--- a/src-db/database/model/functions/M_PRODUCTION_RUN.xml Wed Jul 18 03:32:22 2012 +0000
+++ b/src-db/database/model/functions/M_PRODUCTION_RUN.xml Fri Aug 24 09:28:08 2012 +0200
@@ -42,43 +42,31 @@
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 REF CURSOR;
Cur_Parameter RECORD;
- -- Parameter Variables
- MustBeStocked CHAR(1) ;
- v_IsCreated CHAR(1) ;
- v_Processed VARCHAR2(60) ;
- v_Client_ID VARCHAR2(32);
- v_Org_ID VARCHAR2(32);
- --
- v_Line NUMBER;
- NextNo VARCHAR2(32);
- CountNo NUMBER;
- v_Count NUMBER;
- v_Qty NUMBER;
- v_QtyAcumulated NUMBER;
- v_QtyOrder NUMBER;
- p_Warehouse VARCHAR2(32);
- p_User VARCHAR2(32);
- v_Product_Name VARCHAR2(2000):='';
- -- ProductionPlan
CUR_PP RECORD;
- -- BOM Lines
- CUR_BOM RECORD;
- -- Stocked BOMs
- CURSOR CUR_STOCK (Product_ID VARCHAR, UOM VARCHAR, Warehouse_ID VARCHAR, Org_ID VARCHAR) IS
- 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 LEFT OUTER JOIN
- M_ATTRIBUTESETINSTANCE A
- ON T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
+ CUR_PLineBOM RECORD;
+ CUR_BOM_PRODUCT RECORD;
+ CUR_PL_Post RECORD;
+ -- Parameter Variables
+ MustBeStocked CHAR(1) ;
+ v_IsCreated CHAR(1) ;
+ v_Processed VARCHAR2(60) ;
+ v_Client_ID VARCHAR2(32);
+ v_Org_ID VARCHAR2(32);
+ --
+ v_Line NUMBER;
+ v_Count NUMBER;
+ p_User VARCHAR2(32);
+ v_Product_Name VARCHAR2(2000):='';
+ -- Stocked BOMs
+ CURSOR CUR_STOCK (Product_ID VARCHAR, UOM VARCHAR, Warehouse_ID VARCHAR, Org_ID VARCHAR) IS
+ SELECT T.M_PRODUCT_ID, T.M_LOCATOR_ID, T.M_ATTRIBUTESETINSTANCE_ID,
+ T.C_UOM_ID, T.M_PRODUCT_UOM_ID,
+ COALESCE(T.QtyOnHand, 0) AS Qty, T.QtyOrderOnHand AS QtyOrder
+ FROM M_STORAGE_DETAIL T
+ LEFT OUTER JOIN M_ATTRIBUTESETINSTANCE A ON T.M_ATTRIBUTESETINSTANCE_ID = A.M_ATTRIBUTESETINSTANCE_ID,
M_LOCATOR L, M_WAREHOUSE W
WHERE T.M_PRODUCT_ID=Product_ID
AND T.C_UOM_ID=UOM
@@ -89,530 +77,404 @@
ORDER BY(
CASE L.M_WAREHOUSE_ID WHEN Warehouse_ID THEN 1 ELSE 2
END
- ),
- L.PRIORITYNO,
- A.CREATED, T.CREATED,
- T.M_PRODUCT_UOM_ID;
- v_storage CUR_STOCK%ROWTYPE;
- -- 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;
+ ), L.PRIORITYNO, A.CREATED, T.CREATED, T.M_PRODUCT_UOM_ID;
+ v_storage CUR_STOCK%ROWTYPE;
+ NEXT_PRODUCT BOOLEAN:=false;
+ CUR_STOCK_ISOPEN BOOLEAN:=false;
+BEGIN
+ -- Update AD_PInstance
+ DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID) ;
+ v_ResultStr:='PInstanceNotFound';
+ 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;
+ DBMS_OUTPUT.PUT_LINE(' MustBeStocked=' || MustBeStocked) ;
+ ELSE
+ DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || Cur_Parameter.ParameterName) ;
+ END IF;
+ END LOOP; -- Get Parameter
+ DBMS_OUTPUT.PUT_LINE(' 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_APPLICATION_ERROR(-20000, v_Message);
+ END IF;
+ /**************************************************************************
+ * Create Lines
+ */
+ /***************************************************************************/
+ -- 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_APPLICATION_ERROR(-20000, '@OrgHeaderNotReady@');
+ END IF;
+ IF (v_is_tr_allow='N') THEN
+ RAISE_APPLICATION_ERROR(-20000, '@OrgHeaderNotTransAllowed@');
+ 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_APPLICATION_ERROR(-20000, '@LinesAndHeaderDifferentLEorBU@');
+ 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_APPLICATION_ERROR(-20000, '@LinesAndHeaderDifferentLEorBU@');
+ 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_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 = AD_GET_DOC_LE_BU('M_PRODUCTION', v_Record_ID, 'M_PRODUCTION_ID', 'LE');
+
+ 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_APPLICATION_ERROR(-20000, '@PeriodNotAvailable@');
+ 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,
+ (CASE WHEN M_PRODUCT.ATTRSETVALUETYPE='D' THEN M_PRODUCT.M_AttributeSetInstance_ID ELSE NULL END) AS 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
+ IF (CUR_PP.M_Locator_ID IS NULL) THEN
+ RAISE_APPLICATION_ERROR(-20000, '@ProductionPlanLocatorNeeded@');
+ END IF;
+ -- 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
+ 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 (
+ get_uuid(), CUR_PP.M_ProductionPlan_ID, v_Line, CUR_PP.AD_Client_ID,
+ CUR_PP.AD_Org_ID, 'Y', now(), p_User,
+ now(), p_User, 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 VARCHAR2(32);
+ v_Warehouse_old VARCHAR2(32);
+ v_UOM_old VARCHAR2(32);
+ v_Warehouse_old_org VARCHAR2(32);
+ v_Qty NUMBER;
+ v_QtyStorage NUMBER:=0;
+ v_QtyAcumulated NUMBER;
+ v_QtyOrder NUMBER;
+ CUR_BOM RECORD;
BEGIN
- -- Update AD_PInstance
- DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID) ;
- v_ResultStr:='PInstanceNotFound';
- 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;
- DBMS_OUTPUT.PUT_LINE(' MustBeStocked=' || MustBeStocked) ;
- ELSE
- DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || Cur_Parameter.ParameterName) ;
- END IF;
- END LOOP; -- Get Parameter
- DBMS_OUTPUT.PUT_LINE(' 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_APPLICATION_ERROR(-20000, v_Message);
- 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_APPLICATION_ERROR(-20000, '@OrgHeaderNotReady@');
- END IF;
- IF (v_is_tr_allow='N') THEN
- RAISE_APPLICATION_ERROR(-20000, '@OrgHeaderNotTransAllowed@');
- 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_APPLICATION_ERROR(-20000, '@LinesAndHeaderDifferentLEorBU@');
- 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_APPLICATION_ERROR(-20000, '@LinesAndHeaderDifferentLEorBU@');
- 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_APPLICATION_ERROR(-20000, '@PeriodNotAvailable@');
- 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,
- (CASE WHEN M_PRODUCT.ATTRSETVALUETYPE='D' THEN M_PRODUCT.M_AttributeSetInstance_ID ELSE NULL END) AS 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
- IF (CUR_PP.M_Locator_ID IS NULL) THEN
- RAISE_APPLICATION_ERROR(-20000, '@ProductionPlanLocatorNeeded@');
- END IF;
- -- 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
- Ad_Sequence_Next('M_ProductionLine', CUR_PP.AD_Client_ID, NextNo) ;
- 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', now(), p_User,
- now(), p_User, 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 VARCHAR2(32);
- v_Warehouse_old VARCHAR2(32);
- v_UOM_old VARCHAR2(32);
- v_Warehouse_old_org VARCHAR2(32);
- v_QtyStorage NUMBER:=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
- AND pb.isactive='Y'
- 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 CUR_STOCK%NOTFOUND THEN
- 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;
- Ad_Sequence_Next('M_ProductionLine', CUR_BOM.Client_ID, NextNo) ;
- 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', now(), p_User,
- now(), p_User, CUR_BOM.M_ProductBOM_ID, CUR_BOM.C_UOM_ID,
- -v_Qty, v_storage.M_Locator_ID,
- (CASE WHEN (SELECT ATTRSETVALUETYPE FROM M_PRODUCT WHERE M_PRODUCT_ID=CUR_BOM.M_ProductBOM_ID)='F' THEN NULL ELSE v_storage.M_AttributeSetInstance_ID END),
- 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 CUR_STOCK%NOTFOUND;
- 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;
- Ad_Sequence_Next('M_ProductionLine', CUR_BOM.Client_ID, NextNo) ;
- 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', now(), p_User,
- now(), p_User, 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>>
- NEXT_PRODUCT:=false;
- v_Line:=v_Line;
- IF (v_QtyAcumulated < CUR_BOM.BOMQTY*CUR_BOM.ProductionQty) AND MustBeStocked='Y' THEN
- v_Message := '@NotEnoughStocked@: ' || CUR_BOM.pname || ' ' || v_QtyAcumulated || ' / '|| CUR_BOM.BOMQTY*CUR_BOM.ProductionQty;
- ROLLBACK;
- RAISE_APPLICATION_ERROR(-20000, v_Message);
- END IF;
- IF (CUR_BOM.ProductionQty <= 0) THEN
- v_Message := '@NegativeBOMProductionQty@';
- ROLLBACK;
- RAISE_APPLICATION_ERROR(-20000, v_Message);
- END IF;
- END LOOP;
- IF(CUR_STOCK_ISOPEN) THEN
+ 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,
+ l.m_warehouse_id
+ FROM M_PRODUCT_BOM pb
+ JOIN M_PRODUCT p ON pb.M_ProductBOM_ID=p.M_Product_ID
+ JOIN M_PRODUCTIONPLAN pp ON pb.M_Product_ID=pp.M_PRODUCT_ID
+ JOIN M_LOCATOR l ON pp.m_locator_id = l.m_locator_id
+ WHERE pp.M_PRODUCTION_ID=v_Record_ID
+ AND pb.isactive='Y'
+ ORDER BY pb.M_PRODUCTBOM_ID, pb.Line
+ ) LOOP
+ v_ResultStr:='CreatingLine Products';
+ IF (CUR_BOM.IsStocked = 'Y') THEN
+ v_QtyAcumulated:=0;
+ IF ((NOT CUR_STOCK_ISOPEN)
+ OR (v_Product_old<>CUR_BOM.M_ProductBOM_ID OR v_Warehouse_old<>cur_bom.m_warehouse_id OR v_UOM_old<>CUR_BOM.C_UOM_ID)) THEN
+ v_Product_old := CUR_BOM.M_ProductBOM_ID;
+ v_Warehouse_old := cur_bom.m_warehouse_id;
+ 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;
- 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'
- )
+ 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 (CUR_STOCK%NOTFOUND) THEN
+ NEXT_PRODUCT:=true;
+ END IF;
+ v_QtyStorage:=v_storage.Qty;
+ END IF;
+ IF (NOT NEXT_PRODUCT) THEN
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
- AND pb.isactive='Y'
- 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;
- Ad_Sequence_Next('M_ProductionLine', CUR_PLineBOM.Client_ID, NextNo) ;
- 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_PLineBOM.M_ProductionPlan_ID, v_Line, CUR_PLineBOM.Client_ID,
- CUR_PLineBOM.Org_ID, 'Y', now(), p_User,
- now(), p_User, CUR_BOM_PRODUCT.M_ProductBOM_ID, CUR_PLineBOM.MovementQty*CUR_BOM_PRODUCT.BOMQty,
- CUR_PLineBOM.M_Locator_ID, CUR_BOM_PRODUCT.C_UOM_ID
- )
- ;
- END LOOP;
- -- Delete BOM line
- DELETE
+ 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_ProductionLine_ID=CUR_PLineBOM.M_ProductionLine_ID;
+ WHERE M_ProductionPlan_ID=CUR_BOM.M_ProductionPlan_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 (
+ get_uuid(), CUR_BOM.M_ProductionPlan_ID, v_Line, CUR_BOM.Client_ID,
+ CUR_BOM.Org_ID, 'Y', now(), p_User,
+ now(), p_User, CUR_BOM.M_ProductBOM_ID, CUR_BOM.C_UOM_ID,
+ -v_Qty, v_storage.M_Locator_ID,
+ (CASE WHEN (SELECT ATTRSETVALUETYPE FROM M_PRODUCT WHERE M_PRODUCT_ID=CUR_BOM.M_ProductBOM_ID)='F' THEN NULL ELSE v_storage.M_AttributeSetInstance_ID END),
+ 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 CUR_STOCK%NOTFOUND;
+ v_QtyStorage:=v_storage.Qty;
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',Updated=now(),UpdatedBy=p_User WHERE M_Production_ID=v_Record_ID;
- /**************************************************************************
- * Post Lines
- */
+ END IF; --NEXT_PRODUCT
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.ATTRSETVALUETYPE IS NULL OR P.ATTRSETVALUETYPE <> 'F')
- AND (SELECT ISONEATTRSETVALREQUIRED FROM M_ATTRIBUTESET WHERE M_ATTRIBUTESET_ID = P.M_ATTRIBUTESET_ID) = 'Y'
- 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.ATTRSETVALUETYPE IS NULL OR P.ATTRSETVALUETYPE <> 'F')
- AND (SELECT ISONEATTRSETVALREQUIRED FROM M_ATTRIBUTESET WHERE M_ATTRIBUTESET_ID = P.M_ATTRIBUTESET_ID) = 'Y'
- 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_APPLICATION_ERROR(-20000, v_Message);
- 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_APPLICATION_ERROR(-20000, v_Message);
- FINISH_PROCESS:=true;
- END IF;
- IF(NOT FINISH_PROCESS) THEN
- -- Create Transaction Entry
- v_ResultStr:='CreateTransaction';
- Ad_Sequence_Next('M_Transaction', CUR_PL_Post.AD_Org_ID, NextNo) ;
- 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', now(), p_User, now(),
- p_User, '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
- )
- ;
- M_Check_Stock(CUR_PL_Post.M_Product_ID, v_Client_ID, v_Org_ID, v_Result, v_Message) ;
- IF v_Result=0 THEN
- v_Message:=v_Message || ' @Inline@ ' || CUR_PL_Post.line;
- RAISE_APPLICATION_ERROR(-20000, v_Message);
- 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=now(),
- updatedby=p_User
- WHERE M_Production_ID=v_Record_ID;
- END IF; --FINISH_PROCESS
+ SELECT COALESCE(MAX(Line), 0) + 10
+ INTO v_Line
+ FROM M_PRODUCTIONLINE
+ WHERE M_ProductionPlan_ID=CUR_BOM.M_ProductionPlan_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 (
+ get_uuid(), CUR_BOM.M_ProductionPlan_ID, v_Line, CUR_BOM.Client_ID,
+ CUR_BOM.Org_ID, 'Y', now(), p_User,
+ now(), p_User, CUR_BOM.M_ProductBOM_ID, -CUR_BOM.ProductionQty*CUR_BOM.BOMQty,
+ CUR_BOM.M_Locator_ID, CUR_BOM.C_UOM_ID
+ );
END IF;
- END IF;--FINISH_PROCESS
- IF(NOT FINISH_PROCESS) THEN
- -- Only commit when entire job successful
- COMMIT;
- END IF; --FINISH_PROCESS
- --<<FINISH_PROCESS>>
- -- Update AD_PInstance
- DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message) ;
- AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', v_Result, v_Message) ;
- RETURN;
- END; --BODY
+ --<<NEXT_PRODUCT>>
+ NEXT_PRODUCT:=false;
+ v_Line:=v_Line;
+ IF (v_QtyAcumulated < CUR_BOM.BOMQTY*CUR_BOM.ProductionQty) AND MustBeStocked='Y' THEN
+ v_Message := '@NotEnoughStocked@: ' || CUR_BOM.pname || ' ' || v_QtyAcumulated || ' / '|| CUR_BOM.BOMQTY*CUR_BOM.ProductionQty;
+ RAISE_APPLICATION_ERROR(-20000, v_Message);
+ END IF;
+ IF (CUR_BOM.ProductionQty <= 0) THEN
+ v_Message := '@NegativeBOMProductionQty@';
+ RAISE_APPLICATION_ERROR(-20000, v_Message);
+ 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 v_count
+ 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(v_count=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
+ AND pb.isactive='Y'
+ 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;
+ 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 (
+ get_uuid(), CUR_PLineBOM.M_ProductionPlan_ID, v_Line, CUR_PLineBOM.Client_ID,
+ CUR_PLineBOM.Org_ID, 'Y', now(), p_User,
+ now(), p_User, CUR_BOM_PRODUCT.M_ProductBOM_ID, CUR_PLineBOM.MovementQty*CUR_BOM_PRODUCT.BOMQty,
+ CUR_PLineBOM.M_Locator_ID, CUR_BOM_PRODUCT.C_UOM_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',Updated=now(),UpdatedBy=p_User 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.ATTRSETVALUETYPE IS NULL OR P.ATTRSETVALUETYPE <> 'F')
+ AND (SELECT ISONEATTRSETVALREQUIRED FROM M_ATTRIBUTESET WHERE M_ATTRIBUTESET_ID = P.M_ATTRIBUTESET_ID) = 'Y'
+ 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.ATTRSETVALUETYPE IS NULL OR P.ATTRSETVALUETYPE <> 'F')
+ AND (SELECT ISONEATTRSETVALREQUIRED FROM M_ATTRIBUTESET WHERE M_ATTRIBUTESET_ID = P.M_ATTRIBUTESET_ID) = 'Y'
+ 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_APPLICATION_ERROR(-20000, v_Message);
+ END IF;
+ -- All Production Lines
+ 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
+ SELECT '@NotEnoughStocked@: ' || Name INTO v_Message
+ FROM M_PRODUCT
+ WHERE M_Product_ID=CUR_PL_Post.M_Product_ID;
+ RAISE_APPLICATION_ERROR(-20000, v_Message);
+ END IF;
+ -- Create Transaction Entry
+ v_ResultStr:='CreateTransaction';
+ 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 (
+ get_uuid(), CUR_PL_Post.M_ProductionLine_ID, CUR_PL_Post.AD_Client_ID, CUR_PL_Post.AD_Org_ID,
+ 'Y', now(), p_User, now(),
+ p_User, '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
+ );
+ M_Check_Stock(CUR_PL_Post.M_Product_ID, v_Client_ID, v_Org_ID, v_Result, v_Message) ;
+ IF (v_Result = 0) THEN
+ v_Message:=v_Message || ' @Inline@ ' || CUR_PL_Post.line;
+ RAISE_APPLICATION_ERROR(-20000, v_Message);
+ END IF;
+ END LOOP;
+ -- Indicate that we are done
+ UPDATE M_PRODUCTION
+ SET Processed='Y',
+ updated=now(),
+ updatedby=p_User
+ WHERE M_Production_ID=v_Record_ID;
+ END IF;
+ --<<FINISH_PROCESS>>
+ -- Update AD_PInstance
+ DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message) ;
+ AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', v_Result, v_Message) ;
+ RETURN;
+END; --BODY
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;