m_production_run

-- Filter INSERT INTO M_PRODUCTIONLINE

CREATE OR REPLACE FUNCTION PUBLIC.m_production_run(pinstance_id character varying)
 RETURNS void
 LANGUAGE plpgsql
 SET search_path TO '$user', 'public'
AS $function$ 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 SLU
  * Contributions are Copyright (C) 2001-2017 Openbravo, S.L.U.
  *
  * 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;
  -- Parameter
  --TYPE RECORD IS REFCURSOR;
    Cur_Parameter RECORD;
    CUR_ProductionPlan RECORD;
    CUR_PP RECORD;
    CUR_PLineBOM RECORD;
    CUR_BOM_PRODUCT RECORD;
    CUR_PL_Post 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;
  v_Count NUMERIC;
  p_User VARCHAR(32); --OBTG:VARCHAR2--
  v_NegStockLocator VARCHAR(32); --OBTG:VARCHAR2--
  v_NegStockWarehosue VARCHAR(32); --OBTG:VARCHAR2--
  v_Product_Name m_product.name%TYPE;
  -- Stocked BOMs
  -- Proposed stock from given warehouse is priorized.
  DECLARE CUR_STOCK CURSOR (v_ad_pinstance_id VARCHAR)  FOR
      SELECT sd.m_product_id, sd.m_locator_id, sd.m_attributesetinstance_id,
          sd.c_uom_id, sd.m_product_uom_id, u.stdprecision,
          ms.quantity AS qty, ms.qtyorder AS qtyorder,
          ms.priority, invs.overissue
      FROM m_stock_proposed ms
            LEFT JOIN m_storage_detail sd ON ms.m_storage_detail_id = sd.m_storage_detail_id
            LEFT JOIN m_product_uom pu ON sd.m_product_uom_id = pu.m_product_uom_id
            LEFT JOIN c_uom u ON pu.c_uom_id = u.c_uom_id
            LEFT JOIN m_locator l ON sd.m_locator_id = l.m_locator_id
            JOIN m_inventorystatus invs ON invs.m_inventorystatus_id = l.m_inventorystatus_id
      WHERE ms.ad_pinstance_id = v_ad_pinstance_id
      ORDER BY ms.priority;
  v_storage RECORD; --OBTG:CUR_STOCK--
  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_ClientInfo ci JOIN AD_PInstance i ON ci.ad_client_id=i.ad_client_id
            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--
  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 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_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 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,
            (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 EXCEPTION '%', '@ProductionPlanLocatorNeeded@'; --OBTG:-20000--
      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', TO_DATE(NOW()), p_User,
          TO_DATE(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 VARCHAR(32); --OBTG:VARCHAR2--
      v_Warehouse_old VARCHAR(32); --OBTG:VARCHAR2--
      v_UOM_old VARCHAR(32); --OBTG:VARCHAR2--
      v_Qty NUMERIC;
      v_QtyStorage NUMERIC:=0;
      v_QtyAcumulated NUMERIC;
      v_QtyOrder NUMERIC;
      v_QtyOrderRate NUMERIC;
      v_ProductionLineCount NUMERIC;
      v_pinstance_id VARCHAR(32); --OBTG:VARCHAR2--
      CUR_BOM RECORD;
    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,
              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
            NEXT_PRODUCT:=FALSE;
            v_product_old := cur_bom.m_productbom_id;
            v_warehouse_old := cur_bom.m_warehouse_id;
            v_uom_old := cur_bom.c_uom_id;
            IF (CUR_STOCK_ISOPEN) THEN
              CLOSE CUR_STOCK;
              CUR_STOCK_ISOPEN:=FALSE;
            END IF;

            -- Call M_GET_STOCK
            DECLARE
              v_pinstance_result AD_PInstance.result%TYPE;
              v_pinstance_msg AD_PInstance.errormsg%TYPE;
            BEGIN
              v_pinstance_id := get_uuid();
             SELECT * INTO  v_pinstance_result, v_pinstance_msg FROM M_GET_STOCK_PARAM(v_pinstance_id, v_record_id, NULL, v_Product_old, NULL, NULL, v_Warehouse_old, cur_bom.org_id, NULL, p_user, v_client_id, NULL, v_UOM_old, NULL, NULL, NULL, 10, '137', NULL, 'N', 'Y', NULL, NULL);
              -- Check result
              IF (v_pinstance_result = 0) THEN
                -- Error on m_get_stock
                RAISE EXCEPTION '%', v_pinstance_msg; --OBTG:-20000--
              END IF;
            END; -- End Call M_GET_STOCK

            OPEN CUR_STOCK(v_pinstance_id);
            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
              IF (v_storage.OVERISSUE = 'Y' AND v_NegStockLocator IS NULL) THEN
                v_NegStockLocator:= v_storage.m_locator_id;
              END IF;

              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 := ROUND(v_storage.QtyOrder*(v_Qty/v_storage.Qty), v_storage.stdprecision);
              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, 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', TO_DATE(NOW()), p_User,
                  TO_DATE(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;
              IF ( NOT FOUND ) THEN --OBTG:CUR_STOCK--
                CLOSE CUR_STOCK;
                CUR_STOCK_ISOPEN:=FALSE;
                EXIT;
              END IF;
              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;
          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', TO_DATE(NOW()), p_User,
              TO_DATE(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;
        v_Line:=v_Line;
        IF (v_QtyAcumulated < CUR_BOM.BOMQTY*CUR_BOM.ProductionQty) THEN
          IF (MustBeStocked='Y') THEN
            v_Message := '@NotEnoughStocked@: ' || CUR_BOM.pname || ' ' || v_QtyAcumulated || ' / '|| CUR_BOM.BOMQTY*CUR_BOM.ProductionQty;
            RAISE EXCEPTION '%', v_Message; --OBTG:-20000--
          ELSE
            IF (v_NegStockLocator IS NULL) THEN
              -- Get Locator with negative Stock that belongs to the same Warehosue
              SELECT m_warehouse_id
              INTO v_NegStockWarehosue
              FROM m_locator
             WHERE m_locator_id = (SELECT m_locator_id
                                   FROM M_PRODUCTIONPLAN
                                   WHERE M_ProductionPlan_ID=CUR_BOM.M_ProductionPlan_ID);

              SELECT MIN(l.m_locator_id)
              INTO v_NegStockLocator
              FROM m_locator l
              JOIN m_inventorystatus invs ON l.m_inventorystatus_id = invs.m_inventorystatus_id
              WHERE l.m_warehouse_id = v_NegStockWarehosue
              AND invs.overissue = 'Y'
              AND l.isactive= 'Y'
              AND priorityno = (SELECT MIN(priorityno)
                                FROM m_locator l
                                JOIN m_inventorystatus invs ON l.m_inventorystatus_id = invs.m_inventorystatus_id
                                WHERE l.m_warehouse_id = v_NegStockWarehosue
                                AND invs.overissue = 'Y'
                                AND l.isactive='Y');
            END IF;

            -- If there is a Storage Bin that allows negative stock and MustBeStocked is disabled and there is not enough stock,
            -- pending quantity will be created for the same locator as production plan product, keeping a negative stock
            IF (v_NegStockLocator IS NOT NULL) THEN

              -- Check if exists a production line for this product with the same locator
              SELECT COUNT(M_ProductionLine_ID)
              INTO v_ProductionLineCount
              FROM M_PRODUCTIONLINE
              WHERE M_ProductionPlan_ID=CUR_BOM.M_ProductionPlan_ID
              AND M_Product_ID=CUR_BOM.M_ProductBOM_ID
              AND M_Locator_ID=v_NegStockLocator;

              -- If exists update it
              IF (v_ProductionLineCount <> 0) THEN

                SELECT QuantityOrder / CASE WHEN (MovementQty <> 0) THEN MovementQty ELSE 1 END
                INTO v_QtyOrderRate
                FROM M_PRODUCTIONLINE
                WHERE M_ProductionPlan_ID=CUR_BOM.M_ProductionPlan_ID
                AND M_Product_ID=CUR_BOM.M_ProductBOM_ID
                AND M_Locator_ID=v_NegStockLocator;

                UPDATE M_PRODUCTIONLINE
                SET MovementQty = MovementQty - (CUR_BOM.BOMQTY*CUR_BOM.ProductionQty - v_QtyAcumulated),
                QuantityOrder = v_QtyOrderRate * (MovementQty - (CUR_BOM.BOMQTY*CUR_BOM.ProductionQty - v_QtyAcumulated))
                WHERE M_ProductionPlan_ID=CUR_BOM.M_ProductionPlan_ID
                AND M_Product_ID=CUR_BOM.M_ProductBOM_ID
                AND M_Locator_ID=v_NegStockLocator;

              -- If not exists, insert a new production line
              ELSE

                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, C_UOM_ID,
                  MovementQty, M_Locator_ID
              ) VALUES (
                  get_uuid(), CUR_BOM.M_ProductionPlan_ID, v_Line, CUR_BOM.Client_ID,
                  CUR_BOM.Org_ID, 'Y', TO_DATE(NOW()), p_User,
                  TO_DATE(NOW()), p_User, CUR_BOM.M_ProductBOM_ID, CUR_BOM.C_UOM_ID,
                  - (CUR_BOM.BOMQTY*CUR_BOM.ProductionQty - v_QtyAcumulated), v_NegStockLocator
              );

              END IF;
            END IF;
          END IF;
        END IF;
        IF (CUR_BOM.ProductionQty <= 0) THEN
          v_Message := '@NegativeBOMProductionQty@';
          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 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', TO_DATE(NOW()), p_User,
              TO_DATE(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=TO_DATE(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 EXCEPTION '%', v_Message; --OBTG:-20000--
    END IF;

    SELECT COUNT(M_ProductionPlan_ID)
    INTO v_count
    FROM M_ProductionPlan pp
    WHERE pp.M_Production_ID = v_Record_ID
    AND NOT EXISTS (SELECT 1 FROM M_ProductionLine WHERE M_ProductionPlan_ID = pp.M_ProductionPlan_ID AND MovementQty > 0);

    v_Message := NULL;
    IF(v_count <> 0) THEN
      FOR CUR_ProductionPlan IN (
    SELECT DISTINCT(pp.line)
    FROM M_ProductionPlan pp
    WHERE pp.M_Production_ID = v_Record_ID
    AND NOT EXISTS (SELECT 1 FROM M_ProductionLine WHERE M_ProductionPlan_ID = pp.M_ProductionPlan_ID AND MovementQty > 0)
    ORDER BY Line
        )
      LOOP
        IF v_Message IS NULL THEN
          v_Message := CUR_ProductionPlan.line;
        ELSE
      v_Message := v_Message || ', ' || CUR_ProductionPlan.line;
    END IF;
      END LOOP;
      v_Message := '@ProducedProductWithNegativeQty@' || ' @ReferProductionPlanLines@' || ': ' || v_Message;
      RAISE EXCEPTION '%', v_Message; --OBTG:-20000--
    END IF;

    SELECT COUNT(M_ProductionPlan_ID)
    INTO v_count
    FROM M_ProductionPlan pp
    WHERE pp.M_Production_ID = v_Record_ID
    AND (SELECT COUNT(M_ProductionLine_ID) FROM M_ProductionLine WHERE M_ProductionPlan_ID = pp.M_ProductionPlan_ID AND movementQty > 0) > 1;

    IF (v_count <> 0) THEN
      FOR CUR_ProductionPlan IN (
    SELECT DISTINCT(pp.line)
    FROM M_ProductionPlan pp
    WHERE pp.M_Production_ID = v_Record_ID
    AND (SELECT COUNT(M_ProductionLine_ID) FROM M_ProductionLine WHERE M_ProductionPlan_ID = pp.M_ProductionPlan_ID AND movementQty > 0) > 1
    ORDER BY Line
      )
      LOOP
        IF v_Message IS NULL THEN
          v_Message := CUR_ProductionPlan.line;
        ELSE
      v_Message := v_Message || ', ' || CUR_ProductionPlan.line;
    END IF;
      END LOOP;
      v_Message:= '@ConsumedProductWithPostiveQty@' || ' @ReferProductionPlanLines@' || ': ' || v_Message;
      RAISE EXCEPTION '%', v_Message; --OBTG:-20000--
    END IF;

    DECLARE
        v_Message_ProductionRun VARCHAR(2000);
        v_Message_ProductLine VARCHAR(2000);
        BEGIN
          FOR CUR_ProductionPlan IN
            (SELECT pp.Line, pp.M_PRODUCTIONPLAN_ID
            FROM M_PRODUCTION p, M_PRODUCTIONPLAN pp
            WHERE p.M_Production_ID=pp.M_Production_ID
            AND pp.M_Production_ID=v_Record_ID
            ORDER BY pp.Line)
          LOOP
            FOR CUR_PL_Post IN
              (SELECT pl.Line
              FROM M_PRODUCTIONLINE pl, M_PRODUCT pr
              WHERE Cur_ProductionPlan.M_ProductionPlan_ID=pl.M_ProductionPlan_ID
              AND pl.M_PRODUCT_ID=pr.M_PRODUCT_ID
              AND pr.ISACTIVE='N'
              ORDER BY pl.Line)
            LOOP
              v_Message_ProductLine := COALESCE(v_Message_ProductLine, '') || CUR_PL_Post.line || ', ';
            END LOOP;
            IF (v_Message_ProductLine IS NOT NULL) THEN
              v_Message_ProductionRun := COALESCE(v_Message_ProductionRun, '') || '@ProductionRunLine@' || Cur_ProductionPlan.line || ' @ProductLine@' || v_Message_ProductLine;
              v_Message_ProductLine := NULL;
            END IF;
          END LOOP;
          IF (v_Message_ProductionRun IS NOT NULL) THEN
            RAISE EXCEPTION '%', v_Message_ProductionRun||'@InActiveProducts@'; --OBTG:-20000--
          END IF;
        END;

    SELECT COUNT(*) INTO v_count
    FROM dual
    WHERE EXISTS (
        SELECT 1
        FROM m_productionplan pp
            JOIN m_productionline pl ON pp.m_productionplan_id = pl.m_productionplan_id
            JOIN m_product p ON pl.m_product_id = p.m_product_id
        WHERE pp.m_production_id = v_record_id
          AND p.isgeneric = 'Y');
    IF (v_count > 0) THEN
      SELECT MAX(p.name) INTO v_product_name
      FROM m_productionplan pp
          JOIN m_productionline pl ON pp.m_productionplan_id = pl.m_productionplan_id
          JOIN m_product p ON pl.m_product_id = p.m_product_id
      WHERE pp.m_production_id = v_record_id
        AND p.isgeneric = 'Y';
      RAISE EXCEPTION '%', '@CannotUseGenericProduct@ ' || v_product_Name; --OBTG:-20000--
    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, M_PRODUCT pro
        WHERE p.M_Production_ID=pp.M_Production_ID
          AND pp.M_ProductionPlan_ID=pl.M_ProductionPlan_ID
          AND pl.M_PRODUCT_ID = pro.M_PRODUCT_ID
          AND pro.ISSTOCKED = 'Y'
          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 EXCEPTION '%', v_Message; --OBTG:-20000--
      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', TO_DATE(NOW()), p_User, TO_DATE(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
      );
      SELECT * INTO  v_Result, v_Message FROM M_Check_Stock(CUR_PL_Post.M_Product_ID, v_Client_ID, v_Org_ID, CUR_PL_Post.M_Locator_ID) ;
      IF (v_Result = 0) THEN
        v_Message:=v_Message || ' @Inline@ ' || CUR_PL_Post.line;
        RAISE EXCEPTION '%', v_Message; --OBTG:-20000--
      END IF;
    END LOOP;
    -- 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>>
  --  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 ; $function$
;