CREATE OR REPLACE FUNCTION "public"."m_production_run" (pinstance_id character varying) RETURNS void VOLATILE AS $dbvis$ DECLARE /************************************************************************* * The contents of this file are subject to the Compiere Public * License 1.1 ("License"); You may not use this file except in * compliance with the License. You may obtain a copy of the License in * the legal folder of your Openbravo installation. * Software distributed under the License is distributed on an * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or * implied. See the License for the specific language governing rights * and limitations under the License. * The Original Code is Compiere ERP & Business Solution * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc. * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, * parts created by ComPiere are Copyright (C) ComPiere, Inc.; * All Rights Reserved. * Contributor(s): Openbravo SL * Contributions are Copyright (C) 2001-2009 Openbravo, S.L. * * Specifically, this derivative work is based upon the following Compiere * file and version. ************************************************************************* * $Id: M_Production_Run.sql,v 1.4 2003/09/05 04:58:06 jjanke Exp $ *** * Title: Production of BOMs * Description: * 1) Creating ProductionLines when IsCreated = 'N' * 2) Posting the Lines (optionally only when fully stocked) ************************************************************************/ -- Logistice v_ResultStr VARCHAR(2000):=''; --OBTG:VARCHAR2-- v_Message VARCHAR(2000):=''; --OBTG:VARCHAR2-- v_Record_ID VARCHAR(32); --OBTG:VARCHAR2-- v_Result NUMERIC:=1; v_is_included NUMERIC:=0; v_MovementDate TIMESTAMP; v_available_period NUMERIC:=0; v_is_ready AD_Org.IsReady%TYPE; v_is_tr_allow AD_OrgType.IsTransactionsAllowed%TYPE; v_isacctle AD_OrgType.IsAcctLegalEntity%TYPE; v_org_bule_id AD_Org.AD_Org_ID%TYPE; -- Parameter --TYPE RECORD IS REFCURSOR; Cur_Parameter RECORD; -- Parameter Variables MustBeStocked CHAR(1) ; v_IsCreated CHAR(1) ; v_Processed VARCHAR(60) ; --OBTG:VARCHAR2-- v_Client_ID VARCHAR(32); --OBTG:VARCHAR2-- v_Org_ID VARCHAR(32); --OBTG:VARCHAR2-- -- v_Line NUMERIC; NextNo VARCHAR(32); --OBTG:VARCHAR2-- CountNo NUMERIC; v_Count NUMERIC; v_Qty NUMERIC; v_QtyAcumulated NUMERIC; v_QtyOrder NUMERIC; p_Warehouse VARCHAR(32); --OBTG:VARCHAR2-- p_User VARCHAR(32); --OBTG:VARCHAR2-- v_Product_Name VARCHAR(2000):=''; --OBTG:VARCHAR2-- -- ProductionPlan CUR_PP RECORD; -- BOM Lines CUR_BOM RECORD; -- Stocked BOMs DECLARE CUR_STOCK CURSOR (Product_ID VARCHAR, UOM VARCHAR, Warehouse_ID VARCHAR, Org_ID VARCHAR) FOR SELECT T.M_PRODUCT_ID, T.C_UOM_ID, T.M_LOCATOR_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.M_PRODUCT_UOM_ID, COALESCE(T.QtyOnHand, 0) AS Qty, T.QtyOrderOnHand AS QtyOrder FROM M_STORAGE_DETAIL T, M_LOCATOR L, M_WAREHOUSE W WHERE T.M_PRODUCT_ID=Product_ID AND T.C_UOM_ID=UOM AND T.M_LOCATOR_ID=L.M_LOCATOR_ID AND COALESCE(T.QtyOnHand, 0)<>0 AND L.M_WAREHOUSE_ID = W.M_WAREHOUSE_ID AND ad_isorgincluded(Org_ID, W.AD_ORG_ID, W.AD_CLIENT_ID) <> -1 ORDER BY( CASE L.M_WAREHOUSE_ID WHEN Warehouse_ID THEN 1 ELSE 2 END ), L.PRIORITYNO, T.M_ATTRIBUTESETINSTANCE_ID, T.M_PRODUCT_UOM_ID; v_storage RECORD; --OBTG:CUR_STOCK-- -- ProductionLines which are non-stocked BOMs (need to be resolved) CUR_PLineBOM RECORD; CUR_BOM_PRODUCT RECORD; -- Posting CUR_PL_Post RECORD; FINISH_PROCESS BOOLEAN:=false; NEXT_PRODUCT BOOLEAN:=false; CUR_STOCK_ISOPEN BOOLEAN:=false; BEGIN -- Update AD_PInstance RAISE NOTICE '%','Updating PInstance - Processing ' || PInstance_ID ; v_ResultStr:='PInstanceNotFound'; PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'Y', NULL, NULL) ; BEGIN --BODY -- Get Parameters v_ResultStr:='ReadingParameters'; FOR Cur_Parameter IN (SELECT i.Record_ID, i.AD_User_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date FROM AD_PInstance i LEFT JOIN AD_PInstance_Para p ON i.AD_PInstance_ID=p.AD_PInstance_ID WHERE i.AD_PInstance_ID=PInstance_ID ORDER BY p.SeqNo ) LOOP v_Record_ID:=Cur_Parameter.Record_ID; p_User:=Cur_Parameter.AD_User_ID; IF(Cur_Parameter.ParameterName='MustBeStocked') THEN MustBeStocked:=Cur_Parameter.P_String; RAISE NOTICE '%',' MustBeStocked=' || MustBeStocked ; ELSE RAISE NOTICE '%','*** Unknown Parameter=' || Cur_Parameter.ParameterName ; END IF; END LOOP; -- Get Parameter RAISE NOTICE '%',' Record_ID=' || v_Record_ID ; -- Processing:1:2:3 Lock :4:5:6:7 -- TODO /** * Get Info + Lock */ v_ResultStr:='ReadingRecord'; SELECT IsCreated, Processed, AD_Client_ID, AD_Org_ID, MovementDate INTO v_IsCreated, v_Processed, v_Client_ID, v_Org_ID, v_MovementDate FROM M_PRODUCTION WHERE M_Production_ID=v_Record_ID FOR UPDATE; /** * No Action */ IF(v_Processed<>'N') THEN v_Message:='@AlreadyPosted@'; RAISE EXCEPTION '%', v_Message; --OBTG:-20000-- FINISH_PROCESS:=true; END IF; /************************************************************************** * Create Lines */ IF(NOT FINISH_PROCESS) THEN /***************************************************************************/ -- Check the header belongs to a organization where transactions are posible and ready to use SELECT AD_Org.IsReady, Ad_OrgType.IsTransactionsAllowed INTO v_is_ready, v_is_tr_allow FROM M_PRODUCTION, AD_Org, AD_OrgType WHERE AD_Org.AD_Org_ID=M_PRODUCTION.AD_Org_ID AND AD_Org.AD_OrgType_ID=AD_OrgType.AD_OrgType_ID AND M_PRODUCTION.M_PRODUCTION_ID=v_Record_ID; IF (v_is_ready='N') THEN RAISE EXCEPTION '%', '@OrgHeaderNotReady@'; --OBTG:-20000-- END IF; IF (v_is_tr_allow='N') THEN RAISE EXCEPTION '%', '@OrgHeaderNotTransAllowed@'; --OBTG:-20000-- END IF; -- Check the lines belong to the same business unit or legal entity as the header SELECT AD_ORG_CHK_DOCUMENTS('M_PRODUCTION', 'M_PRODUCTIONPLAN', v_Record_ID, 'M_PRODUCTION_ID', 'M_PRODUCTION_ID') INTO v_is_included FROM dual; IF (v_is_included=-1) THEN RAISE EXCEPTION '%', '@LinesAndHeaderDifferentLEorBU@'; --OBTG:-20000-- END IF; FOR CUR_PP IN (SELECT M_PRODUCTIONPLAN_ID FROM M_PRODUCTIONPLAN WHERE M_PRODUCTION_ID = v_Record_ID) LOOP SELECT AD_ORG_CHK_DOCUMENTS('M_PRODUCTIONPLAN', 'M_PRODUCTIONLINE', CUR_PP.M_PRODUCTIONPLAN_ID, 'M_PRODUCTIONPLAN_ID', 'M_PRODUCTIONPLAN_ID') INTO v_is_included FROM dual; IF (v_is_included=-1) THEN RAISE EXCEPTION '%', '@LinesAndHeaderDifferentLEorBU@'; --OBTG:-20000-- END IF; END LOOP; -- Check the period control is opened (only if it is legal entity with accounting) -- Gets the BU or LE of the document SELECT AD_GET_DOC_LE_BU('M_PRODUCTION', v_Record_ID, 'M_PRODUCTION_ID', 'LE') INTO v_org_bule_id FROM DUAL; SELECT AD_OrgType.IsAcctLegalEntity INTO v_isacctle FROM AD_OrgType, AD_Org WHERE AD_Org.AD_OrgType_ID = AD_OrgType.AD_OrgType_ID AND AD_Org.AD_Org_ID=v_org_bule_id; IF (v_isacctle='Y') THEN SELECT C_CHK_OPEN_PERIOD(v_Org_ID, v_MovementDate, 'MMP', NULL) INTO v_available_period FROM DUAL; IF (v_available_period<>1) THEN RAISE EXCEPTION '%', '@PeriodNotAvailable@'; --OBTG:-20000-- END IF; END IF; IF(v_IsCreated<>'Y') THEN -- For every Production Plan FOR CUR_PP IN (SELECT M_PRODUCTIONPLAN.*, M_PRODUCT.C_UOM_ID, M_PRODUCT.M_AttributeSetInstance_ID FROM M_PRODUCTIONPLAN, M_PRODUCT WHERE M_PRODUCTIONPLAN.M_Product_ID=M_PRODUCT.M_Product_ID AND M_PRODUCTIONPLAN.M_Production_ID=v_Record_ID ORDER BY M_PRODUCTIONPLAN.Line, M_PRODUCTIONPLAN.M_Product_ID ) LOOP -- Delete prior lines DELETE FROM M_PRODUCTIONLINE WHERE M_ProductionPlan_ID=CUR_PP.M_ProductionPlan_ID; -- Create BOM Line v_ResultStr:='CreatingLine BOM'; v_Line:=10; -- OriginLine SELECT * INTO NextNo FROM Ad_Sequence_Next('M_ProductionLine', CUR_PP.AD_Client_ID) ; INSERT INTO M_PRODUCTIONLINE ( M_ProductionLine_ID, M_ProductionPlan_ID, Line, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, M_Product_ID, MovementQty, M_Locator_ID, Description, C_UOM_ID, M_AttributeSetInstance_ID ) VALUES ( NextNo, CUR_PP.M_ProductionPlan_ID, v_Line, CUR_PP.AD_Client_ID, CUR_PP.AD_Org_ID, 'Y', TO_DATE(NOW()), '0', TO_DATE(NOW()), '0', CUR_PP.M_Product_ID, CUR_PP.ProductionQty, CUR_PP.M_Locator_ID, CUR_PP.Description, CUR_PP.C_UOM_ID, CUR_PP.M_AttributeSetInstance_ID ) ; END LOOP; --Creating BOM Lines DECLARE v_Product_old VARCHAR(32); --OBTG:VARCHAR2-- v_Warehouse_old VARCHAR(32); --OBTG:VARCHAR2-- v_UOM_old VARCHAR(32); --OBTG:VARCHAR2-- v_Warehouse_old_org VARCHAR(32); --OBTG:VARCHAR2-- v_QtyStorage NUMERIC:=0; BEGIN FOR CUR_BOM IN (SELECT pb.*, p.C_UOM_ID, p.IsStocked, pp.M_LOCATOR_ID, pp.AD_Org_ID AS ORG_ID, pp.ProductionQty, pp.M_ProductionPlan_ID, pp.AD_Client_ID AS Client_ID, p.name as pname FROM M_PRODUCT_BOM pb, M_PRODUCT p, M_PRODUCTIONPLAN pp WHERE pb.M_ProductBOM_ID=p.M_Product_ID AND pb.M_Product_ID=pp.M_PRODUCT_ID AND pp.M_PRODUCTION_ID=v_Record_ID ORDER BY pb.M_PRODUCTBOM_ID, pb.Line ) LOOP v_ResultStr:='CreatingLine Products'; IF(CUR_BOM.IsStocked='Y') THEN v_QtyAcumulated:=0; SELECT M_WAREHOUSE_ID INTO p_Warehouse FROM M_LOCATOR WHERE M_LOCATOR_ID=CUR_BOM.M_LOCATOR_ID; IF(NOT CUR_STOCK_ISOPEN) OR (v_Product_old<>CUR_BOM.M_ProductBOM_ID OR v_Warehouse_old<>p_Warehouse OR v_UOM_old<>CUR_BOM.C_UOM_ID) THEN v_Product_old:=CUR_BOM.M_ProductBOM_ID; v_Warehouse_old:=p_Warehouse; v_UOM_old:=CUR_BOM.C_UOM_ID; SELECT ad_org_id INTO v_Warehouse_old_org FROM m_warehouse WHERE m_warehouse_id = v_Warehouse_old; IF(CUR_STOCK_ISOPEN) THEN CLOSE CUR_STOCK; CUR_STOCK_ISOPEN:=false; END IF; OPEN CUR_STOCK(v_Product_old, v_UOM_old, v_Warehouse_old, v_warehouse_old_org); CUR_STOCK_ISOPEN:=true; FETCH CUR_STOCK INTO v_storage; IF NOT FOUND THEN --OBTG:CUR_STOCK-- NEXT_PRODUCT:=true; END IF; v_QtyStorage:=v_storage.Qty; END IF; IF(NOT NEXT_PRODUCT) THEN LOOP v_Qty:=LEAST(v_QtyStorage, CUR_BOM.ProductionQty*CUR_BOM.BOMQty - v_QtyAcumulated) ; v_QtyStorage:=v_QtyStorage - v_Qty; v_QtyAcumulated:=v_QtyAcumulated + v_Qty; v_QtyOrder:=v_storage.QtyOrder*(v_Qty/v_storage.Qty) ; SELECT COALESCE(MAX(Line), 0) + 10 INTO v_Line FROM M_PRODUCTIONLINE WHERE M_ProductionPlan_ID=CUR_BOM.M_ProductionPlan_ID; SELECT * INTO NextNo FROM Ad_Sequence_Next('M_ProductionLine', CUR_BOM.Client_ID) ; INSERT INTO M_PRODUCTIONLINE ( M_ProductionLine_ID, M_ProductionPlan_ID, Line, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, M_Product_ID, C_UOM_ID, MovementQty, M_Locator_ID, M_AttributeSetInstance_ID, M_Product_UOM_ID, QuantityOrder ) VALUES ( NextNo, CUR_BOM.M_ProductionPlan_ID, v_Line, CUR_BOM.Client_ID, CUR_BOM.Org_ID, 'Y', TO_DATE(NOW()), '0', TO_DATE(NOW()), '0', CUR_BOM.M_ProductBOM_ID, CUR_BOM.C_UOM_ID, -v_Qty, v_storage.M_Locator_ID, v_storage.M_AttributeSetInstance_ID, v_storage.M_Product_UOM_ID, -v_QtyOrder ) ; IF(v_QtyAcumulated>=CUR_BOM.ProductionQty*CUR_BOM.BOMQty) THEN EXIT; END IF; v_QtyStorage:=0; v_ResultStr:='FetchingData'; FETCH CUR_STOCK INTO v_storage; EXIT WHEN NOT FOUND ; --OBTG:CUR_STOCK-- v_QtyStorage:=v_storage.Qty; END LOOP; END IF; --NEXT_PRODUCT ELSE SELECT COALESCE(MAX(Line), 0) + 10 INTO v_Line FROM M_PRODUCTIONLINE WHERE M_ProductionPlan_ID=CUR_BOM.M_ProductionPlan_ID; SELECT * INTO NextNo FROM Ad_Sequence_Next('M_ProductionLine', CUR_BOM.Client_ID) ; INSERT INTO M_PRODUCTIONLINE ( M_ProductionLine_ID, M_ProductionPlan_ID, Line, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, M_Product_ID, MovementQty, M_Locator_ID, C_UOM_ID ) VALUES ( NextNo, CUR_BOM.M_ProductionPlan_ID, v_Line, CUR_BOM.Client_ID, CUR_BOM.Org_ID, 'Y', TO_DATE(NOW()), '0', TO_DATE(NOW()), '0', CUR_BOM.M_ProductBOM_ID, -CUR_BOM.ProductionQty*CUR_BOM.BOMQty, CUR_BOM.M_Locator_ID, CUR_BOM.C_UOM_ID ) ; END IF; ---- <> NEXT_PRODUCT:=false; v_Line:=v_Line; IF (v_QtyAcumulated < CUR_BOM.BOMQTY*CUR_BOM.ProductionQty) THEN v_Message := '@NotEnoughStocked@: ' || CUR_BOM.pname || ' ' || v_QtyAcumulated || ' / '|| CUR_BOM.BOMQTY*CUR_BOM.ProductionQty; -- ROLLBACK; RAISE EXCEPTION '%', v_Message; --OBTG:-20000-- END IF; END LOOP; IF(CUR_STOCK_ISOPEN) THEN CLOSE CUR_STOCK; CUR_STOCK_ISOPEN:=false; END IF; END; --END OF DECLARE FOR BOM PRODUCTS INSERTION -- While we have BOMs LOOP -- Are there non-stored BOMs to list details:8 v_ResultStr:='CreatingLine CheckBOM'; SELECT COUNT(*) INTO CountNo FROM M_PRODUCTIONLINE pl, M_PRODUCT p, M_PRODUCTIONPLAN pp WHERE pl.M_Product_ID=p.M_Product_ID AND pl.M_ProductionPlan_ID=pp.M_ProductionPlan_ID AND pp.M_PRODUCTION_ID=v_Record_ID AND pl.Line<>10 -- Origin Line AND p.IsBOM='Y' AND p.IsStocked='N'; -- Nothing to do EXIT WHEN(CountNo=0) ; -- -- Resolve BOMs in ProductLine which are not stocked FOR CUR_PLineBOM IN (SELECT pl.M_ProductionLine_ID, pl.Line, pl.M_Product_ID, pl.MovementQty, pp.M_LOCATOR_ID, pp.AD_Org_ID AS ORG_ID, pp.ProductionQty, pp.M_ProductionPlan_ID, pp.AD_Client_ID AS Client_ID FROM M_PRODUCTIONLINE pl, M_PRODUCT p, M_PRODUCTIONPLAN pp WHERE pl.M_ProductionPlan_ID=pp.M_ProductionPlan_ID AND pp.M_Production_ID=v_Record_ID AND pl.M_Product_ID=p.M_Product_ID AND pl.Line<>10 -- Origin Line AND p.IsBOM='Y' AND p.IsStocked='N' ) LOOP v_ResultStr:='CreatingLineBOM Resolution'; v_Line:=CUR_PLineBOM.Line; -- Resolve BOM Line in product line FOR CUR_BOM_PRODUCT IN (SELECT pb.*, p.C_UOM_ID, p.IsStocked FROM M_PRODUCT_BOM pb, M_PRODUCT p WHERE pb.M_ProductBOM_ID=p.M_Product_ID AND pb.M_Product_ID=CUR_PLineBOM.M_Product_ID ORDER BY pb.M_PRODUCTBOM_ID, pb.Line ) LOOP v_ResultStr:='CreatingLine Products2'; SELECT COALESCE(MAX(Line), 0) + 10 INTO v_Line FROM M_PRODUCTIONLINE WHERE M_ProductionPlan_ID=CUR_PLineBOM.M_ProductionPlan_ID; SELECT * INTO NextNo FROM Ad_Sequence_Next('M_ProductionLine', CUR_PLineBOM.Client_ID) ; INSERT INTO M_PRODUCTIONLINE ( M_ProductionLine_ID, M_ProductionPlan_ID, Line, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, M_Product_ID, MovementQty, M_Locator_ID ) VALUES ( NextNo, CUR_PLineBOM.M_ProductionPlan_ID, v_Line, CUR_PLineBOM.Client_ID, CUR_PLineBOM.Org_ID, 'Y', TO_DATE(NOW()), '0', TO_DATE(NOW()), '0', CUR_BOM_PRODUCT.M_ProductBOM_ID, CUR_PLineBOM.MovementQty*CUR_BOM_PRODUCT.BOMQty, CUR_PLineBOM.M_Locator_ID ) ; END LOOP; -- Delete BOM line DELETE FROM M_PRODUCTIONLINE WHERE M_ProductionLine_ID=CUR_PLineBOM.M_ProductionLine_ID; END LOOP; END LOOP; -- While we have BOMs -- Modifying locator to have sufficient stock -- Indicate that it is Created UPDATE M_PRODUCTION SET IsCreated='Y' WHERE M_Production_ID=v_Record_ID; /************************************************************************** * Post Lines */ ELSE v_ResultStr:='CheckingRestrictions'; SELECT COUNT(*) INTO v_Count FROM M_PRODUCTIONPLAN PP, M_PRODUCTIONLINE PL, M_PRODUCT P WHERE PL.M_PRODUCT_ID=P.M_PRODUCT_ID AND P.M_ATTRIBUTESET_ID IS NOT NULL AND P.M_ATTRIBUTESETINSTANCE_ID IS NULL AND COALESCE(PL.M_ATTRIBUTESETINSTANCE_ID, '0') = '0' AND PL.M_ProductionPlan_ID=pp.M_ProductionPlan_ID AND pp.M_Production_ID=v_Record_ID; IF v_Count<>0 THEN SELECT max(P.name) INTO v_Product_Name FROM M_PRODUCTIONPLAN PP, M_PRODUCTIONLINE PL, M_PRODUCT P WHERE PL.M_PRODUCT_ID=P.M_PRODUCT_ID AND P.M_ATTRIBUTESET_ID IS NOT NULL AND P.M_ATTRIBUTESETINSTANCE_ID IS NULL AND COALESCE(PL.M_ATTRIBUTESETINSTANCE_ID, '0') = '0' AND PL.M_ProductionPlan_ID=pp.M_ProductionPlan_ID AND pp.M_Production_ID=v_Record_ID; v_Message:='@Product@'||' "' ||v_Product_Name ||'" ' || '@ProductWithoutAttributeSet@'; RAISE EXCEPTION '%', v_Message; --OBTG:-20000-- FINISH_PROCESS:=true; END IF; -- All Production Lines IF(NOT FINISH_PROCESS) THEN FOR CUR_PL_Post IN (SELECT pl.M_ProductionLine_ID, pl.AD_Client_ID, pl.AD_Org_ID, p.MovementDate, pl.M_Product_ID, pl.M_AttributeSetInstance_ID, pl.MovementQty, pl.M_Locator_ID, pl.M_Product_UOM_ID, pl.QuantityOrder, pl.C_UOM_ID, pl.LINE FROM M_PRODUCTION p, M_PRODUCTIONLINE pl, M_PRODUCTIONPLAN pp WHERE p.M_Production_ID=pp.M_Production_ID AND pp.M_ProductionPlan_ID=pl.M_ProductionPlan_ID AND pp.M_Production_ID=v_Record_ID ORDER BY pp.Line, pl.Line ) LOOP -- M_ProductionLine_ID, AD_Client_ID, AD_Org_ID, MovementDate, M_Product_ID, MovementQty, M_Locator_ID -- DBMS_OUTPUT.PUT_LINE('ProductionLine=' || pl.M_ProductionLine_ID); -- DBMS_OUTPUT.PUT_LINE(' Qty=' || pl.MovementQty || ', OnHand=' || M_BOM_Qty_OnHand(pl.M_Product_ID, NULL, pl.M_Locator_ID)); -- Check Stock levels for reductions IF(CUR_PL_Post.MovementQty<0 AND MustBeStocked<>'N' AND M_BOM_Qty_OnHand(CUR_PL_Post.M_Product_ID, NULL, CUR_PL_Post.M_Locator_ID) +CUR_PL_Post.MovementQty<0) THEN -- ROLLBACK; SELECT '@NotEnoughStocked@: ' || Name INTO v_Message FROM M_PRODUCT WHERE M_Product_ID=CUR_PL_Post.M_Product_ID; RAISE EXCEPTION '%', v_Message; --OBTG:-20000-- FINISH_PROCESS:=true; END IF; IF(NOT FINISH_PROCESS) THEN -- Create Transaction Entry v_ResultStr:='CreateTransaction'; SELECT * INTO NextNo FROM Ad_Sequence_Next('M_Transaction', CUR_PL_Post.AD_Org_ID) ; INSERT INTO M_TRANSACTION ( M_Transaction_ID, M_ProductionLine_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, MovementType, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, MovementDate, MovementQty, M_Product_UOM_ID, QuantityOrder, C_UOM_ID ) VALUES ( NextNo, CUR_PL_Post.M_ProductionLine_ID, CUR_PL_Post.AD_Client_ID, CUR_PL_Post.AD_Org_ID, 'Y', TO_DATE(NOW()), '0', TO_DATE(NOW()), '0', 'P+', CUR_PL_Post.M_Locator_ID, CUR_PL_Post.M_Product_ID, COALESCE(CUR_PL_Post.M_AttributeSetInstance_ID, '0'), -- not distinguishing between assemby/disassembly CUR_PL_Post.MovementDate, CUR_PL_Post.MovementQty, CUR_PL_Post.M_Product_UOM_ID, CUR_PL_Post.QuantityOrder, CUR_PL_Post.C_UOM_ID ) ; SELECT * INTO v_Result, v_Message FROM M_Check_Stock(CUR_PL_Post.M_Product_ID, v_Client_ID, v_Org_ID) ; IF v_Result=0 THEN v_Message:=v_Message || ' @Inline@ ' || CUR_PL_Post.line; RAISE EXCEPTION '%', v_Message; --OBTG:-20000-- FINISH_PROCESS:=true; END IF; END IF; --FINISH_PROCESS END LOOP; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS) THEN -- Indicate that we are done UPDATE M_PRODUCTION SET Processed='Y', updated=TO_DATE(NOW()), updatedby=p_User WHERE M_Production_ID=v_Record_ID; END IF; --FINISH_PROCESS END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS) THEN -- Only commit when entire job successful -- COMMIT; END IF; --FINISH_PROCESS ---- <> -- Update AD_PInstance RAISE NOTICE '%','Updating PInstance - Finished ' || v_Message ; PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', v_Result, v_Message) ; RETURN; END; --BODY EXCEPTION WHEN OTHERS THEN RAISE NOTICE '%',v_ResultStr ; v_ResultStr:= '@ERROR=' || SQLERRM; RAISE NOTICE '%',v_ResultStr ; -- ROLLBACK; PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr) ; RETURN; END ; $dbvis$ LANGUAGE plpgsql