po report query

SELECT

  O.NAME "OPERATING_UNIT_NAME",

  PH.SEGMENT1 "PO_REV_NUM",

  PH.PO_HEADER_ID, 

  PH.TYPE_LOOKUP_CODE "TYPE",

  PH.CURRENCY_CODE,

  PH.AUTHORIZATION_STATUS "STATUS",

  PV.VENDOR_SITE_CODE,

  V.VENDOR_NAME "SUPPLIER_NAME",

  LOC1.LOCATION_CODE "SHIP_TO_LOC",

  LOC2.LOCATION_CODE "BILL_TO_LOC",

  (PL1.QUANTITY*PL1.UNIT_PRICE) "TOTAL",

--LINES INFORMATION 

  PL1.LINE_NUM,

  PL1.PURCHASE_BASIS "TYPE",

  I.SEGMENT1 "ITEM",

  PL1.ITEM_REVISION,

  C.SEGMENT1||C.SEGMENT1 "CATEGORY",

  PL1.ITEM_DESCRIPTION, 

  PL1.QUANTITY,

  PL.UNIT_MEAS_LOOKUP_CODE,

  PL1.UNIT_PRICE, 

  PL.NEED_BY_DATE,

  PL.SHIPMENT_TYPE,

  PL.SHIPMENT_NUM,

  M.ORGANIZATION_CODE "SHP_ORG_CODE",

  LOC1.LOCATION_CODE "SHIPMENT_LOC",

  PL.UNIT_MEAS_LOOKUP_CODE "SHP_UOM",

  PL.QUANTITY "SHP_QUANTITY",

  (PL.QUANTITY*PL.PRICE_OVERRIDE) "SHP_AMOUNT", 

  D.DISTRIBUTION_NUM,

  D.DESTINATION_TYPE_CODE, 

  GL.CONCATENATED_SEGMENTS,

  D.QUANTITY_ORDERED "DIS_QUANTITY",

  P.FULL_NAME "REQUESTER NAME",

  I.INVENTORY_ITEM_ID,

  C.CATEGORY_ID

FROM

  --PO_HEADERS

  HR_ORGANIZATION_UNITS O,

  po_headers_all PH,

  PO_VENDORS V,

  PO_VENDOR_SITES_ALL PV,

  HR_LOCATIONS_ALL_TL LOC1,

  HR_LOCATIONS_ALL_TL LOC2,

  --PO_LINES

  po_line_locations_all PL,

  PO_LINES_ALL PL1,

  MTL_SYSTEM_ITEMS_B I,

  MTL_CATEGORIES_B C,

  MTL_PARAMETERS M,

  PO_DISTRIBUTIONS_ALL D,

  GL_CODE_COMBINATIONS_KFV GL,

  PER_ALL_PEOPLE_F P

WHERE PH.VENDOR_SITE_ID=PV.VENDOR_SITE_ID 

  AND PH.SHIP_TO_LOCATION_ID=PV.SHIP_TO_LOCATION_ID 

  AND PH.VENDOR_ID=V.VENDOR_ID

  AND LOC1.LOCATION_ID=PH.SHIP_TO_LOCATION_ID

  AND LOC2.LOCATION_ID=PV.BILL_TO_LOCATION_ID 

  AND PH.PO_HEADER_ID=PL1.PO_HEADER_ID

    --LINES 

  AND PL1.ITEM_ID=I.INVENTORY_ITEM_ID

  AND PL.PO_HEADER_ID=PL1.PO_HEADER_ID

  AND PL.PO_LINE_ID=PL1.PO_LINE_ID

  AND PL1.CATEGORY_ID=C.CATEGORY_ID

  --ORG_CODE

  AND M.ORGANIZATION_ID=PL.SHIP_TO_ORGANIZATION_ID

  --AND O.ORGANIZATION_ID=M.ORGANIZATION_ID

  --AND C.ORGANIZATION_ID=M.ORGANIZATION_ID

  AND O.ORGANIZATION_ID=PH.ORG_ID

  --DISTRIBTION

  AND D.PO_HEADER_ID=PL.PO_HEADER_ID

  AND D.PO_LINE_ID=PL.PO_LINE_ID

  AND D.LINE_LOCATION_ID=PL.LINE_LOCATION_ID

  AND GL.CODE_COMBINATION_ID=D.CODE_COMBINATION_ID

  AND D.DELIVER_TO_PERSON_ID=P.PERSON_ID

  AND PH.SEGMENT1 like 'PO_NUM%'

  AND O.NAME LIKE 'Vision%';

Query to find the Flexfields and valuesets

  
     SELECT       A.ID_FLEX_STRUCTURE_CODE,
                   B.ID_FLEX_CODE,
                   E.LANGUAGE,
                   D.FLEX_VALUE,
                   E.DESCRIPTION,
                   E.FLEX_VALUE_MEANING,
                   B.FLEX_VALUE_SET_ID,
                   B.APPLICATION_COLUMN_NAME,
                   B.SEGMENT_NAME,
                   C.FLEX_VALUE_SET_NAME,
                   D.FLEX_VALUE_ID                
        FROM     APPS.fnd_id_flex_structures A,
                 APPS.fnd_id_flex_segments B,
                 APPS.fnd_flex_value_sets C,
                 APPS.fnd_flex_values D,     
                 APPS.fnd_flex_values_tl E      
        WHERE  A.ID_FLEX_NUM=B.ID_FLEX_NUM
         AND   B.FLEX_VALUE_SET_ID=C.FLEX_VALUE_SET_ID
         AND   C.FLEX_VALUE_SET_ID=D.FLEX_VALUE_SET_ID
         AND   D.FLEX_VALUE_ID=E.FLEX_VALUE_ID
         AND   A.APPLICATION_ID IN (101,201)
         AND   E.LANGUAGE='US'
         ORDER BY A.ID_FLEX_STRUCTURE_CODE

multi- org securiy query

select psp.SECURITY_PROFILE_NAME,
       psp.SECURITY_PROFILE_ID,
       hou.NAME,
       hou.ORGANIZATION_ID
  from PER_SECURITY_PROFILES psp,
       PER_SECURITY_ORGANIZATIONS pso,
       HR_OPERATING_UNITS hou
 where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
   and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

gl_drill_down_to_ap_invoice_liablity_account

select distinct
         AIA.invoice_id,
--         AIA.ORG_ID,
         aia.DOC_SEQUENCE_VALUE,
         aia.invoice_num,
--         aia.gl_date,
         aia.INVOICE_AMOUNT,
         jl.ACCOUNTED_CR,
         jl.ACCOUNTED_DR,
--         jl.JE_LINE_NUM,
         jb.JE_BATCH_ID ,
         jl.JE_HEADER_ID,
          Gcc.SEGMENT1
         || '.'
         || Gcc.SEGMENT2
         || '.'
         || Gcc.SEGMENT3   --  -1545500    3835344   adv
         || '.'
         || Gcc.SEGMENT4
         || '.'
         || Gcc.SEGMENT5
         || '.'
         || Gcc.SEGMENT6
         || '.'
         || Gcc.SEGMENT7
            "CODE_COMBINATION"
            from gl_je_headers jh,gl_je_lines jl,gl_code_combinations Gcc,
            gl_import_references GIr,gl_je_batches jb ,ap_invoices_all AIA
where  jl.je_header_id = jh.je_header_id
and AIA.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
and AIA.INVOICE_ID = gir.REFERENCE_2
and aia.DOC_SEQUENCE_VALUE = gir.SUBLEDGER_DOC_SEQUENCE_VALUE
AND jl.code_combination_id = Gcc.code_combination_id
AND jh.je_header_id = GIr.je_header_id
AND jl.je_line_num = GIr.je_line_num
AND jl.je_header_id = GIr.je_header_id
AND jb.je_batch_id = jh.je_batch_id
and gir.JE_BATCH_ID = jb.JE_BATCH_ID
AND JH.JE_SOURCE = 'Payables'
AND JH.je_category = 'Purchase Invoices'
--and JH.PERIOD_NAME = 'APR-11'
AND GCC.SEGMENT1 = '41'
AND GCC.SEGMENT3 = '373100'
--and jh.je_header_id =2404071
and jl.SET_OF_BOOKS_ID = 5

PO_RECEIPT_WITH_TAX

select *
from ja_in_receipt_tax_lines jtl, rcv_shipment_lines rcl,
         rcv_shipment_headers rch
where rch.receipt_num like '200604211'
and jtl.tax_type in ('Service')
and rcl.shipment_line_id = jtl.shipment_line_id
and rcl.shipment_header_id = rch.shipment_header_id

-----------------------------------------------------------------------


SELECT /*+INDEX (RSH RCV_SHIPMENT_HEADERS_U1) INDEX (GCCK GL_CODE_COMBINATIONS_U1)*/
       rsh.receipt_num, rt.transaction_type, NULL account_type,
       gcck.concatenated_segments, rrsl.accounted_cr, rrsl.accounted_dr,
       rrsl.currency_code, ood.organization_code organization_code,
       rrsl.accounting_date gl_date
  FROM rcv_receiving_sub_ledger rrsl,
       rcv_transactions rt,
       rcv_shipment_headers rsh,
       gl_code_combinations_kfv gcck,
       org_organization_definitions ood
 WHERE rrsl.rcv_transaction_id = rt.transaction_id(+)
   AND rt.shipment_header_id = rsh.shipment_header_id(+)
   AND rrsl.code_combination_id = gcck.code_combination_id(+)
   AND rsh.organization_id = ood.organization_id(+)
   AND rsh.receipt_num in ('200500915','200502972','200600332','200703754','200805644','200900262')
UNION
SELECT jirjb.receipt_num, jirjb.acct_nature, jirjb.acct_type,
       gcck.concatenated_segments, jirjb.entered_cr, jirjb.entered_dr,
       jirjb.currency_code, ood.organization_code organization_code,
       jirjb.transaction_date gl_date
  FROM ja_in_rcv_journals_b jirjb,
       gl_code_combinations_kfv gcck,
       org_organization_definitions ood
 WHERE jirjb.code_combination_id = gcck.code_combination_id(+)
   AND jirjb.organization_code = ood.organization_code(+)
   AND jirjb.receipt_num in ('200500915','200502972','200600332','200703754','200805644','200900262')

select * from JAI_RCV_JOURNAL_ENTRIES   ---->  This table is R12

ja_in_rcv_journals_b    ----- >   This is 11i table

invoice_validatad or never_validated

exec


SELECT

invoice_num, approval_status_lookup_code,ORG_IDFROM ap_invoices_vWHERE invoice_num in ('RECEIPT/426014245/2730024', MO_GLOBAL.INIT('SQLAP');

ITEM_LAST_PO_QRY

SELECT   p.unit_price,
         P.LIST_PRICE_PER_UNIT,
         (SELECT   PRIMARY_UNIT_OF_MEASURE
            FROM   mtl_system_items_b
           WHERE       inventory_item_id = p.item_id
                   AND SUBSTR (SEGMENT1, 1, 1) = :P_ITEM_CODE
                   AND organization_id = 1206)
            UOM,     
         b.item_id,
         (SELECT   segment1
            FROM   mtl_system_items_b
           WHERE       inventory_item_id = p.item_id
                   AND SUBSTR (SEGMENT1, 1, 1) = :P_ITEM_CODE
                   AND organization_id = 1206)
            item_code,
           b.po_date
  FROM   po_lines_all p,
         (  SELECT   item_id, MAX (creation_date) po_date
              FROM   po_lines_All
             WHERE   item_id IN
                           (SELECT   inventory_item_id
                              FROM   mtl_system_items_b msi
                             WHERE   SUBSTR (MSI.SEGMENT1, 1, 2) = :P_ITEM_CODE
                                     AND organization_id = 1206)
                     AND TRUNC (Creation_date) <= '31-MAR-2011'
          GROUP BY   item_id) b
 WHERE   p.item_id = b.item_id AND p.creation_date = b.po_date
 order by b.item_id

XX_XX_BATCH_CREATION

CREATE OR REPLACE PROCEDURE APPS.XX_XX_BATCH_CREATION (
   ERRBUFF             OUT VARCHAR2,
   RETCODE             OUT NUMBER,
   P_BATCH_NUMBER   IN     VARCHAR2,
   P_WHSE_CODE      IN     VARCHAR2,
   P_SHIFT_CODE     IN     NUMBER,
   P_DATE           IN     VARCHAR2
)
AS
   P_API_VERSION               NUMBER DEFAULT 1 ;
   P_VALIDATION_LEVEL          NUMBER DEFAULT GME_API_PUB.MAX_ERRORS ;
   P_INIT_MSG_LIST             BOOLEAN DEFAULT FALSE ;
   P_BATCH_TYPE                NUMBER DEFAULT 0 ;
   X_BATCH_HEADER              GME_BATCH_HEADER%ROWTYPE;
   X_MESSAGE_COUNT             NUMBER;
   X_MESSAGE_LIST              VARCHAR2 (1000);
   X_RETURN_STATUS             VARCHAR2 (1);
   L_BATCH_HEADER              GME_BATCH_HEADER%ROWTYPE;
   L_MSG_INDEX_OUT             NUMBER;
   XX_EXCEPTION_MATERIAL_TBL   GME_API_PUB.UNALLOCATED_MATERIALS_TAB;
   -- DECLARING TMS VARIABLES
   V_ROUTE_CODE                VARCHAR2 (100);
   V_START_SHIFT_CODE          NUMBER;
   V_TM_ITEM_CODE              VARCHAR2 (30);
   V_TRIP_START_DATE           DATE;
   V_TM_ITEM_ID                NUMBER;
   V_UNLOAD_QUANTITY           NUMBER;
   V_ORGN_CODE                 VARCHAR2 (30);
   V_WHSE_CODE                 VARCHAR2 (30);
   V_ITEM_CODE                 VARCHAR2 (100);
   V_LOT_NO                    NUMBER;
   -- TMS VARIABLES DECLARATION ENDED
   -- DECLARING VARIABLE FOR UPDATE WITH THE QUANTITY
   L_MATERIAL_DETAIL           GME_MATERIAL_DETAILS%ROWTYPE;
   X_MATERIAL_DETAIL           GME_MATERIAL_DETAILS%ROWTYPE;
   L_VALUE_TAB                 GME_API_PUB.FIELD_VALUES_TAB;
   L_INDEX                     NUMBER;
   L_BATCH_ID                  GME_BATCH_HEADER.BATCH_ID%TYPE;
   L_MATERIAL_DETAIL_ID        GME_MATERIAL_DETAILS.MATERIAL_DETAIL_ID%TYPE;
   V_PLANT_CODE                VARCHAR2 (10);
   V_ITEM_ID                   NUMBER;
   V_NUM                       NUMBER := 2;
   V_SCALE_TYPE                NUMBER;
   V_COST_ALLOC                NUMBER;
   V_CONTRIBUTE_YIELD_IND      VARCHAR2 (10) := NULL;
   V_ITEM_UOMS                 VARCHAR2 (10);
   V_MATERIAL_DETAIL_ID        NUMBER;
   L_TRAN_ROW                  GME_INVENTORY_TXNS_GTMP%ROWTYPE;
   LX_TRAN_ROW                 GME_INVENTORY_TXNS_GTMP%ROWTYPE;
   LX_MATERIAL_DETAIL          GME_MATERIAL_DETAILS%ROWTYPE;
   LX_DEF_TRAN_ROW             GME_INVENTORY_TXNS_GTMP%ROWTYPE;
   L_TRANSACTIONS              GME_API_PUB.INV_TRANS_REC_TAB;
   L_TRANSACTION               IC_TRAN_PND%ROWTYPE;
   X_MSG_COUNT                 NUMBER;
   X_MSG_DATA                  VARCHAR2 (1000);
   V_CO_CODE                   VARCHAR2 (100);
   V_WHSE_NAME                 VARCHAR2 (1000);
   V_ACTUAL_DATE               DATE;
   P_RECEIPE_NO                VARCHAR2 (100);
   P_RECEIPE_ID                NUMBER;
   P_RECEIPE_RULE              NUMBER;
   P_RECEIPE_VERSION           NUMBER;
   REJECTION_FLAG              NUMBER := 0;
   -- ENDED
   --- DECLARE CURSOR TO GET THE ITEM AND THE QUANTITY DETAILS FOR WHICH THE PRODUCTION BATCH NEEDS TO BE CREATED
   CURSOR C_GET_TOTAL_QUANTITY
   IS
        SELECT                                       --ROUTE_CODE, ROUTE_NAME,
              START_SHIFT_CODE,
                 TM_ITEM_CODE,
                 TTH.ATTRIBUTE5 RL_SECTION,       -- TTH.ATTRIBUTE9 REASON_CD,
                 TRUNC (TRIP_START_DATE) TRIP_START_DATE,
                 TM_ITEM_ID,
                 SUM (NVL (TTL.NO_OF_TRIPS, 1) * UNLOAD_QUANTITY)
                    TOTAL_QUANTITY,
                 ORGN_CODE,
                 WHSE_CODE,
                 (SELECT   ITEM_ID
                    FROM   IC_ITEM_MST
                   WHERE   ITEM_NO = TM_ITEM_CODE)
                    ITEM_ID
          FROM   TM_TRIP_HEADERS TTH,
                 TM_ROUTES TR,
                 TM_TRIP_LINES TTL,
                 TM_LOCATIONS TL,
                 XX_OPM_TM DOT
         WHERE       TTH.ROUTE_ID = TR.ROUTE_ID
                 AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
                 AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
                 AND DOT.LOC_CODE = TL.TM_LOCATION_ID
                 AND START_SHIFT_CODE = P_SHIFT_CODE
                 AND TO_CHAR (TRUNC (TRIP_START_DATE), 'YYYYMMDD') = P_DATE
                 AND ORGN_TYPE = 'M'
                 AND WHSE_CODE = P_WHSE_CODE
      GROUP BY                                                   --ROUTE_CODE,
                 --ROUTE_NAME,
                 START_SHIFT_CODE,
                 TM_ITEM_CODE,
                 TRUNC (TRIP_START_DATE),
                 TM_ITEM_ID,
                 ORGN_CODE,
                 WHSE_CODE,
                 -- TTH.ATTRIBUTE9,
                 TTH.ATTRIBUTE5;
   -- CURSOR TO SUM THE ITEM QUANTITY FOR THE BATCH ALLOCATION
   CURSOR C_INSERT_LINE_ALLOCATION (
      P_ITEM_CODE                 VARCHAR2
   )
   IS
        SELECT   SUM (QTY) QTY, WHSE_1, REASON_CD
          FROM   (  SELECT   NVL ( (SELECT   WHSE_CODE
                                      FROM   XX_OPM_TM DOT
                                     WHERE   LOC_CODE = DEST_SUB_INVENTORY),
                                  WHSE_CODE)
                                WHSE_1,
                             TTH.ATTRIBUTE9 REASON_CD,
                             SUM (NVL (TTL.NO_OF_TRIPS, 1) * UNLOAD_QUANTITY) QTY
                      FROM   TM_TRIP_HEADERS TTH,
                             TM_ROUTES TR,
                             TM_TRIP_LINES TTL,
                             TM_LOCATIONS TL,
                             XX_OPM_TM DOT
                     WHERE       TTH.ROUTE_ID = TR.ROUTE_ID
                             AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
                             AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
                             AND DOT.LOC_CODE = TL.TM_LOCATION_ID
                             AND START_SHIFT_CODE = P_SHIFT_CODE
                             AND TO_CHAR (TRUNC (TRIP_START_DATE), 'YYYYMMDD') =
                                   P_DATE
                             AND ORGN_TYPE = 'M'
                             AND WHSE_CODE = P_WHSE_CODE
                             AND TM_ITEM_CODE = P_ITEM_CODE
                  GROUP BY   ROUTE_CODE,
                             ROUTE_NAME,
                             START_SHIFT_CODE,
                             TM_ITEM_CODE,
                             TRIP_START_DATE,
                             TM_ITEM_ID,
                             ORGN_CODE,
                             WHSE_CODE,
                             SOURCE_SUB_INVENTORY,
                             TTH.ATTRIBUTE9,
                             TTH.ATTRIBUTE5,
                             DEST_SUB_INVENTORY)
      GROUP BY   WHSE_1, REASON_CD;
-- END OF DECLARATION
BEGIN
   -- GET THE DETAILS OF PLANT CODE,WAREHOUSE CODE AND ACTUAL DATE FROM TRUCK M SYSTEM
   BEGIN
      SELECT   DISTINCT
               DOT.ORGN_CODE,
               DOT.WHSE_CODE,
               TO_DATE (
                     TRUNC (TRIP_START_DATE)
                  || ' '
                  || TO_CHAR (SHIFT_START / 3600),
                  'dd-mon-yy hh24:mi'
               )
                  TRIP_START_DATE
        INTO   V_PLANT_CODE, V_WHSE_CODE, V_ACTUAL_DATE
        FROM   TM_TRIP_HEADERS TTH,
               TM_ROUTES TR,
               TM_TRIP_LINES TTL,
               TM_LOCATIONS TL,
               XX_OPM_TM DOT,
               IC_WHSE_MST WHS,
               MR_SHDY_DTL C
       WHERE       TTH.ROUTE_ID = TR.ROUTE_ID
               AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
               AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
               AND DOT.LOC_CODE = TL.TM_LOCATION_ID
               AND START_SHIFT_CODE = P_SHIFT_CODE
               AND DOT.WHSE_CODE = WHS.WHSE_CODE
               AND WHS.ATTRIBUTE1 = C.SHOPDAY_NO
               AND START_SHIFT_CODE = P_SHIFT_CODE
               AND C.SHIFT_NO = P_SHIFT_CODE
               AND TO_CHAR ( (TRIP_START_DATE), 'YYYYMMDD') = P_DATE
               AND ORGN_TYPE = 'M'
               AND DOT.WHSE_CODE = P_WHSE_CODE;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'error while fetching values from TMS =' || SQLERRM
         );
   END;
   -- TO GET THE DETAIL OF THE COMPANY CODE BASED ON THE PLANT CODE FETCHED ABOVE
   BEGIN
      SELECT   CO_CODE
        INTO   V_CO_CODE
        FROM   SY_ORGN_MST
       WHERE   ORGN_CODE = V_PLANT_CODE;
   EXCEPTION
      WHEN OTHERS
      THEN
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'error while fetching values from CO_CODE' || SQLERRM
         );
   END;
   -- TO GET THE NAME OF THE WAREHOUSE FROM THE WAREHOUSE CODE
   BEGIN
      SELECT   WHSE_NAME
        INTO   V_WHSE_NAME
        FROM   IC_WHSE_MST
       WHERE   WHSE_CODE = V_WHSE_CODE;
   EXCEPTION
      WHEN OTHERS
      THEN
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'error while fetching values from WHSE_NAME' || SQLERRM
         );
   END;

   ---------------------FETCH RECEIPE INFORMATION ----------------------------
   BEGIN
      SELECT   RECIPE_ID, RECIPE_NO, RECIPE_VERSION
        INTO   P_RECEIPE_ID, P_RECEIPE_NO, P_RECEIPE_VERSION
        FROM   GMD_RECIPES
       WHERE   RECIPE_VERSION = (SELECT   MAX (RECIPE_VERSION)
                                   FROM   GMD_RECIPES
                                  WHERE   ATTRIBUTE1 = P_WHSE_CODE)
               AND ATTRIBUTE1 = P_WHSE_CODE;
      FND_FILE.PUT_LINE (
         FND_FILE.LOG,
         'receipe id and recipe no ' || P_RECEIPE_ID || ' ' || P_RECEIPE_NO
      );
   EXCEPTION
      WHEN OTHERS
      THEN
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'receipe id and recipe no not fetched' || SQLERRM
         );
   END;

   ---------------------------------------------------------------------
   ------------------FETCHING RECIPE VALIDATION RULE --------------------------------
   BEGIN
      SELECT   RECIPE_VALIDITY_RULE_ID
        INTO   P_RECEIPE_RULE
        FROM   GMD_RECIPE_VALIDITY_RULES
       WHERE   RECIPE_ID = P_RECEIPE_ID;

      FND_FILE.PUT_LINE (FND_FILE.LOG,
                         'receipe validity rule ' || P_RECEIPE_RULE);
   EXCEPTION
      WHEN OTHERS
      THEN
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'receipe validity rule not fetched due to ' || SQLERRM
         );
   END;

   -- CREATION OF BATCH
   ----------------------------------------------------------------------------------

   L_BATCH_HEADER.PLANT_CODE := V_PLANT_CODE;
   L_BATCH_HEADER.BATCH_TYPE := P_BATCH_TYPE;
   L_BATCH_HEADER.BATCH_NO :=
      V_WHSE_NAME || '/' || P_DATE || '/' || P_SHIFT_CODE || '10';
   L_BATCH_HEADER.PLAN_START_DATE := SYSDATE;
   L_BATCH_HEADER.PLAN_CMPLT_DATE := SYSDATE;
   L_BATCH_HEADER.UPDATE_INVENTORY_IND := 'Y';
   L_BATCH_HEADER.RECIPE_VALIDITY_RULE_ID := P_RECEIPE_RULE;
   L_BATCH_HEADER.WIP_WHSE_CODE := V_WHSE_CODE;
   L_BATCH_HEADER.ACTUAL_START_DATE := V_ACTUAL_DATE;
   FND_GLOBAL.APPS_INITIALIZE (
      USER_ID        => FND_PROFILE.VALUE ('USER_ID'),
      RESP_ID        => FND_PROFILE.VALUE ('RESP_ID'),
      RESP_APPL_ID   => FND_PROFILE.VALUE ('RESP_APPL_ID')
   );
   GME_API_PUB.CREATE_BATCH (
      P_API_VERSION            => 1,
      P_VALIDATION_LEVEL       => P_VALIDATION_LEVEL,
      P_INIT_MSG_LIST          => FALSE,
      P_COMMIT                 => FALSE,
      X_MESSAGE_COUNT          => X_MESSAGE_COUNT,
      X_MESSAGE_LIST           => X_MESSAGE_LIST,
      X_RETURN_STATUS          => X_RETURN_STATUS,
      P_BATCH_HEADER           => L_BATCH_HEADER,
      X_BATCH_HEADER           => X_BATCH_HEADER,
      P_BATCH_SIZE             => NULL,
      P_BATCH_SIZE_UOM         => NULL,
      P_CREATION_MODE          => 'RECIPE',
      P_RECIPE_ID              => NULL,
      P_RECIPE_NO              => P_RECEIPE_NO,
      P_RECIPE_VERSION         => P_RECEIPE_VERSION,
      P_PRODUCT_NO             => NULL,
      P_PRODUCT_ID             => NULL,
      P_IGNORE_QTY_BELOW_CAP   => TRUE,
      P_IGNORE_SHORTAGES       => TRUE,
      P_USE_SHOP_CAL           => 0,
      P_CONTIGUITY_OVERRIDE    => 0,
      X_UNALLOCATED_MATERIAL   => XX_EXCEPTION_MATERIAL_TBL
   );
   GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);

   ----------------WRITE THE DETAILS OF THE BATCH IN THE LOG FILE ---------------------------
   FND_FILE.PUT_LINE (FND_FILE.LOG, 'create_batch =' || X_MESSAGE_LIST);
   FND_FILE.PUT_LINE (FND_FILE.LOG, 'x_return_status =' || X_RETURN_STATUS);
   FND_FILE.PUT_LINE (
      FND_FILE.LOG,
      'create_batch x_batch_header.batch_id= '
      || TO_CHAR (X_BATCH_HEADER.BATCH_ID)
   );
   FND_FILE.PUT_LINE (
      FND_FILE.LOG,
      SUBSTR ('x_batch_header.plant_code =' || X_BATCH_HEADER.PLANT_CODE,
              1,
              255)
   );
   FND_FILE.PUT_LINE (
      FND_FILE.LOG,
      SUBSTR ('x_batch_header.batch_no =' || X_BATCH_HEADER.BATCH_NO, 1, 255)
   );
   COMMIT;
   -- THE BELOW CODE WILL STORE THE ITEM ID OF REJECTION ITEM IN V_ITEM_ID
   SELECT   ITEM_ID
     INTO   V_ITEM_ID
     FROM   FM_MATL_DTL
    WHERE   FORMULA_ID IN (SELECT   FORMULA_ID
                             FROM   GMD_RECIPES
                            WHERE   RECIPE_NO LIKE 'M-H-2 TOP%')
            AND LINE_TYPE = 1;
   L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;

   -- THE BELOW CODE WILL FIND THE SCALE TYPE ,COST ALLOCATION,CONTRIBUTE TOWARDS YIELD INDICATOR AND UOM ASSOCIATED
   -- WITH THE REJECTION ITEM CODE.SAME SET OF VALUES WILL BE USED FOR ANY NEW MATERIAL LINE INSERTION
   BEGIN
      SELECT   SCALE_TYPE,
               COST_ALLOC,
               CONTRIBUTE_YIELD_IND,
               ITEM_UM
        INTO   V_SCALE_TYPE,
               V_COST_ALLOC,
               V_CONTRIBUTE_YIELD_IND,
               V_ITEM_UOMS
        FROM   GME_MATERIAL_DETAILS
       WHERE       BATCH_ID = L_BATCH_ID
               AND LINE_TYPE = 1
               AND ITEM_ID = V_ITEM_ID;
   EXCEPTION
      WHEN OTHERS
      THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG,
                            'ERRROR WHILE FETCHING MATERIAL DETAILS');
   END;

   -- START THE UPDATE OF THE BATCH MATERIAL LINES BASED ON THE ITEM AND QUANTITY RETURNED FROM THE TMS QUERY
   FOR I IN C_GET_TOTAL_QUANTITY
   LOOP
      IF I.ITEM_ID = V_ITEM_ID -- I.E. IF REJECTION ITEM IS ALSO A PART OF THE TMS QUERY
      THEN
         REJECTION_FLAG := 1;
         L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
         -- IF REJECTION ITEM IS A PART OF THE TMS QUERY THEN FETCH THE MATERIAL DETAIL ID OF THE REJECTION ITEM FROM THE
         -- NEWLY CREATED BATCH TO UPDATE ITS QUANTITY
         BEGIN
            SELECT   MATERIAL_DETAIL_ID
              INTO   L_MATERIAL_DETAIL_ID
              FROM   GME_MATERIAL_DETAILS
             WHERE       BATCH_ID = L_BATCH_ID
                     AND LINE_TYPE = 1
                     AND ITEM_ID = V_ITEM_ID;
         END;
         V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
         L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
         L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
         L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
         L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
         L_INDEX := 1;
         L_VALUE_TAB (L_INDEX).P_VALUE := 'PLAN_QTY';
         GME_API_PUB.UPDATE_MATERIAL_LINE (
            P_API_VERSION        => 1,
            P_VALIDATION_LEVEL   => P_VALIDATION_LEVEL,
            P_INIT_MSG_LIST      => FALSE,
            P_COMMIT             => FALSE,
            X_MESSAGE_COUNT      => X_MESSAGE_COUNT,
            X_MESSAGE_LIST       => X_MESSAGE_LIST,
            X_RETURN_STATUS      => X_RETURN_STATUS,
            P_MATERIAL_DETAIL    => L_MATERIAL_DETAIL,
            P_VALUES_TAB         => L_VALUE_TAB,
            P_SCALE_PHANTOM      => FALSE,
            X_MATERIAL_DETAIL    => X_MATERIAL_DETAIL
         );
         -- TO DISPLAY THE RESULT OF THE ABOVE LINE UPDATE
         GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
         FND_FILE.PUT_LINE (FND_FILE.LOG,
                            'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
               'x_message_list =
'
            || LENGTH (X_MESSAGE_LIST)
            || SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
         );
         FND_FILE.PUT_LINE (FND_FILE.LOG,
                            'x_message_list =' || X_MESSAGE_LIST);
         FND_FILE.PUT_LINE (FND_FILE.LOG,
                            'x_return_status =' || X_RETURN_STATUS);
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'x_batch_header.batch_id= '
            || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
         );
         COMMIT;
         -------------------------INSERT THE ATTRIBUTE3(RL SECTION) VALUE -----------------------------------
         V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
         L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
         L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
         L_MATERIAL_DETAIL.ATTRIBUTE3 := I.RL_SECTION;
         L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
         L_INDEX := 1;
         L_VALUE_TAB (L_INDEX).P_VALUE := 'ATTRIBUTE3';
         GME_API_PUB.UPDATE_MATERIAL_LINE (
            P_API_VERSION        => 1,
            P_VALIDATION_LEVEL   => P_VALIDATION_LEVEL,
            P_INIT_MSG_LIST      => FALSE,
            P_COMMIT             => FALSE,
            X_MESSAGE_COUNT      => X_MESSAGE_COUNT,
            X_MESSAGE_LIST       => X_MESSAGE_LIST,
            X_RETURN_STATUS      => X_RETURN_STATUS,
            P_MATERIAL_DETAIL    => L_MATERIAL_DETAIL,
            P_VALUES_TAB         => L_VALUE_TAB,
            P_SCALE_PHANTOM      => FALSE,
            X_MATERIAL_DETAIL    => X_MATERIAL_DETAIL
         );
         -- TO DISPLAY THE RESULT OF RL SECTION UPDATE
         GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
         FND_FILE.PUT_LINE (FND_FILE.LOG,
                            'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
               'x_message_list =
'
            || LENGTH (X_MESSAGE_LIST)
            || SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
         );
         FND_FILE.PUT_LINE (FND_FILE.LOG,
                            'x_message_list =' || X_MESSAGE_LIST);
         FND_FILE.PUT_LINE (FND_FILE.LOG,
                            'x_return_status =' || X_RETURN_STATUS);
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'x_batch_header.batch_id= '
            || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
         );
         COMMIT;
         -- TO FETCH THE ITEM CODE AND THE CURRENT LOT NUMBER BASED ON THE ITEM ID
         BEGIN
            SELECT   ITEM_NO, LOT_SUFFIX
              INTO   V_ITEM_CODE, V_LOT_NO
              FROM   IC_ITEM_MST
             WHERE   ITEM_ID = I.ITEM_ID;
         EXCEPTION
            WHEN OTHERS
            THEN
               FND_FILE.PUT_LINE (FND_FILE.LOG, 'UNABLE TO FIND ITEM');
         END;

         ---------------INSERT INTO LINE ALLOCATION FOR THE REJECTION ITEM
         FOR J IN C_INSERT_LINE_ALLOCATION (V_ITEM_CODE)
         LOOP
            -- /////////////////////////////////////////////////////////////
            -- SET VALUES
            -- /////////////////////////////////////////////////////////////
            L_TRAN_ROW.DOC_ID := L_BATCH_ID;
            L_TRAN_ROW.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
            L_TRAN_ROW.WHSE_CODE := J.WHSE_1;
            L_TRAN_ROW.TRANS_QTY := J.QTY;
            L_TRAN_ROW.TRANS_DATE := V_ACTUAL_DATE;
            L_TRAN_ROW.REASON_CODE := J.REASON_CD;
            -- /////////////////////////////////////////////////////////////

            GME_API_PUB.INSERT_LINE_ALLOCATION (
               P_API_VERSION        => GME_API_PUB.API_VERSION,
               P_VALIDATION_LEVEL   => GME_API_PUB.MAX_ERRORS,
               P_INIT_MSG_LIST      => TRUE,
               P_COMMIT             => TRUE,
               P_TRAN_ROW           => L_TRAN_ROW,
               P_LOT_NO             => V_LOT_NO,
               P_CREATE_LOT         => TRUE,
               X_MATERIAL_DETAIL    => LX_MATERIAL_DETAIL,
               X_TRAN_ROW           => LX_TRAN_ROW,
               X_DEF_TRAN_ROW       => LX_DEF_TRAN_ROW,
               X_MESSAGE_COUNT      => X_MESSAGE_COUNT,
               X_MESSAGE_LIST       => X_MESSAGE_LIST,
               X_RETURN_STATUS      => X_RETURN_STATUS
            );
            -- /////////////////////////////////////////////////////////////
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
               'x_message_count for line allocation ='
               || TO_CHAR (X_MESSAGE_COUNT)
            );
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
                  'x_message_list =
'
               || LENGTH (X_MESSAGE_LIST)
               || SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
            );
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
               'x_message_list for line allocation =' || X_MESSAGE_LIST
            );
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
               'x_return_status for line allocation =' || X_RETURN_STATUS
            );
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
               'x_batch_header.batch_id for line allocation = '
               || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
            );
            V_LOT_NO := V_LOT_NO + 1;
         END LOOP;
         COMMIT;
      -----------------INSERT END
      -- IF THE ITEMS RETURNED FROM THE TRUCKING MODULE QUERY ARE NOT REJECTION ITEM INSERT THEM IN THE NEWLY CREATED BATCH
      ELSE
         L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;

         BEGIN
            SELECT   MAX (MATERIAL_DETAIL_ID)
              INTO   L_MATERIAL_DETAIL_ID
              FROM   GME_MATERIAL_DETAILS
             WHERE   BATCH_ID = L_BATCH_ID AND LINE_TYPE = 1;
         END;
         FND_FILE.PUT_LINE (FND_FILE.LOG,
                            'mateiral detail id' || L_MATERIAL_DETAIL_ID);
         V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
         L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
         L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
         L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
         L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
         L_MATERIAL_DETAIL.LINE_NO := V_NUM;
         L_MATERIAL_DETAIL.ITEM_UM := V_ITEM_UOMS;
         L_MATERIAL_DETAIL.LINE_TYPE := 1;
         L_MATERIAL_DETAIL.SCALE_TYPE := V_SCALE_TYPE;
         L_MATERIAL_DETAIL.COST_ALLOC := 0;
         L_MATERIAL_DETAIL.CONTRIBUTE_STEP_QTY_IND := 'Y';
         L_MATERIAL_DETAIL.ALLOC_IND := 1;
         L_MATERIAL_DETAIL.ORIGINAL_QTY := 0.000000;
         L_MATERIAL_DETAIL.ATTRIBUTE3 := I.RL_SECTION;
         GME_API_PUB.INSERT_MATERIAL_LINE (
            P_API_VERSION        => 1,
            P_VALIDATION_LEVEL   => P_VALIDATION_LEVEL,
            P_INIT_MSG_LIST      => FALSE,
            P_COMMIT             => FALSE,
            X_MESSAGE_COUNT      => X_MESSAGE_COUNT,
            X_MESSAGE_LIST       => X_MESSAGE_LIST,
            X_RETURN_STATUS      => X_RETURN_STATUS,
            P_MATERIAL_DETAIL    => L_MATERIAL_DETAIL,
            P_BATCHSTEP_NO       => NULL,
            X_MATERIAL_DETAIL    => X_MATERIAL_DETAIL
         );
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'insert_material_line x_message_list =' || X_MESSAGE_LIST
         );
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'insert_material_line x_return_status =' || X_RETURN_STATUS
         );
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'insert_material_line x_batch_header.batch_id= '
            || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
         );
         COMMIT;
         L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
         L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID + 1;
         L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
         L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
         L_MATERIAL_DETAIL.LINE_NO := V_NUM;
         L_MATERIAL_DETAIL.ITEM_UM := V_ITEM_UOMS;
         L_MATERIAL_DETAIL.LINE_TYPE := 1;
         L_MATERIAL_DETAIL.SCALE_TYPE := V_SCALE_TYPE;
         L_MATERIAL_DETAIL.COST_ALLOC := 0;
         L_MATERIAL_DETAIL.CONTRIBUTE_STEP_QTY_IND := 'Y';
         L_MATERIAL_DETAIL.ALLOC_IND := 1;
         L_MATERIAL_DETAIL.ORIGINAL_QTY := 0.000000;
         V_MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID + 1;
         L_TRANSACTIONS (1).ITEM_ID := I.ITEM_ID;
         L_TRANSACTIONS (1).LINE_ID := V_MATERIAL_DETAIL_ID;
         L_TRANSACTIONS (1).CO_CODE := V_CO_CODE;
         L_TRANSACTIONS (1).WHSE_CODE := V_WHSE_CODE;
         L_TRANSACTIONS (1).LOT_ID := 0;
         L_TRANSACTIONS (1).LOCATION := 'NONE';
         L_TRANSACTIONS (1).DOC_ID := L_BATCH_ID;
         L_TRANSACTIONS (1).DOC_TYPE := 'PROD';
         L_TRANSACTIONS (1).LINE_TYPE := 1;
         L_TRANSACTIONS (1).TRANS_QTY := 0;
         L_TRANSACTIONS (1).TRANS_UM := V_ITEM_UOMS;
         L_TRANSACTIONS (1).TRANS_DATE := V_ACTUAL_DATE;
         L_TRANSACTIONS (1).ORGN_CODE := V_PLANT_CODE;
         GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION (
            P_API_VERSION        => 1,
            P_INIT_MSG_LIST      => FND_API.G_TRUE,
            P_COMMIT             => FND_API.G_TRUE,
            P_VALIDATION_LEVEL   => P_VALIDATION_LEVEL,
            P_TRAN_REC           => L_TRANSACTIONS (1),
            X_TRAN_ROW           => L_TRANSACTION,
            X_RETURN_STATUS      => X_RETURN_STATUS,
            X_MSG_COUNT          => X_MSG_COUNT,
            X_MSG_DATA           => X_MSG_DATA
         );
         COMMIT;
         DBMS_OUTPUT.PUT_LINE (X_RETURN_STATUS);
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
               'Return status of create pending transaction'
            || X_RETURN_STATUS
            || ' '
            || X_MSG_COUNT
         );
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'Create pending transaction else'
            || SUBSTR (X_MSG_DATA, LENGTH (X_MSG_DATA) - 20, 20)
         );
         FND_FILE.PUT_LINE (
            FND_FILE.LOG,
            'ASASDASDASDAS' || X_MATERIAL_DETAIL.MATERIAL_DETAIL_ID
         );
         COMMIT;
         -- TO FETCH THE ITEM CODE AND THE CURRENT LOT NUMBER BASED ON THE ITEM ID
         BEGIN
            SELECT   ITEM_NO, LOT_SUFFIX
              INTO   V_ITEM_CODE, V_LOT_NO
              FROM   IC_ITEM_MST
             WHERE   ITEM_ID = I.ITEM_ID;
         EXCEPTION
            WHEN OTHERS
            THEN
               FND_FILE.PUT_LINE (FND_FILE.LOG, 'UNABLE TO FIND ITEM');
         END;
         -- TO DO LINE ALLOCATION OF THE ITEMS SELECTED ABOVE
         ---------------INSERT INTO LINE ALLOCATION
         FOR J IN C_INSERT_LINE_ALLOCATION (V_ITEM_CODE)
         LOOP
            -- /////////////////////////////////////////////////////////////
            -- SET VALUES
            -- /////////////////////////////////////////////////////////////
            L_TRAN_ROW.DOC_ID := L_BATCH_ID;
            L_TRAN_ROW.MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID;
            L_TRAN_ROW.WHSE_CODE := J.WHSE_1;
            L_TRAN_ROW.TRANS_QTY := J.QTY;
            L_TRAN_ROW.TRANS_DATE := V_ACTUAL_DATE;
            L_TRAN_ROW.REASON_CODE := J.REASON_CD;
            -- /////////////////////////////////////////////////////////////
            -- /////////////////////////////////////////////////////////////
            -- CALL API METHOD
            -- /////////////////////////////////////////////////////////////
            GME_API_PUB.INSERT_LINE_ALLOCATION (
               P_API_VERSION        => GME_API_PUB.API_VERSION,
               P_VALIDATION_LEVEL   => GME_API_PUB.MAX_ERRORS,
               P_INIT_MSG_LIST      => TRUE,
               P_COMMIT             => TRUE,
               P_TRAN_ROW           => L_TRAN_ROW,
               P_LOT_NO             => V_LOT_NO,
               P_CREATE_LOT         => TRUE,
               X_MATERIAL_DETAIL    => LX_MATERIAL_DETAIL,
               X_TRAN_ROW           => LX_TRAN_ROW,
               X_DEF_TRAN_ROW       => LX_DEF_TRAN_ROW,
               X_MESSAGE_COUNT      => X_MESSAGE_COUNT,
               X_MESSAGE_LIST       => X_MESSAGE_LIST,
               X_RETURN_STATUS      => X_RETURN_STATUS
            );
            -- /////////////////////////////////////////////////////////////
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
               'x_message_count for line allocation ='
               || TO_CHAR (X_MESSAGE_COUNT)
            );
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
                  'x_message_list =
'
               || LENGTH (X_MESSAGE_LIST)
               || SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
            );
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
               'x_message_list for line allocation =' || X_MESSAGE_LIST
            );
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
               'x_return_status for line allocation =' || X_RETURN_STATUS
            );
            FND_FILE.PUT_LINE (
               FND_FILE.LOG,
               'x_batch_header.batch_id for line allocation = '
               || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
            );
            V_LOT_NO := V_LOT_NO + 1;
         END LOOP;
         -----------------------------END OF LINE ALLOCATION
         -- END OF CREATE
         V_NUM := V_NUM + 1;
      END IF;
   END LOOP;
   V_NUM := 0;

   --END IF ;
   --------------------------------------------RELEASE THE BATCH -------------------------------------------------------------
   L_BATCH_HEADER.PLANT_CODE := V_PLANT_CODE;                         --'DBP';
   L_BATCH_HEADER.BATCH_TYPE := P_BATCH_TYPE;
   L_BATCH_HEADER.BATCH_NO :=
      V_WHSE_NAME || '/' || P_DATE || '/' || P_SHIFT_CODE || '10';

   L_BATCH_HEADER.PLAN_START_DATE := SYSDATE;
   L_BATCH_HEADER.PLAN_CMPLT_DATE := SYSDATE;
   L_BATCH_HEADER.UPDATE_INVENTORY_IND := 'Y';
   L_BATCH_HEADER.RECIPE_VALIDITY_RULE_ID := P_RECEIPE_RULE;
   L_BATCH_HEADER.WIP_WHSE_CODE := V_WHSE_CODE;
   L_BATCH_HEADER.ACTUAL_START_DATE := V_ACTUAL_DATE;
   ---------------------------------CALL THE API----------------------------------------------------
   GME_API_PUB.RELEASE_BATCH (
      P_API_VERSION            => 1,
      P_VALIDATION_LEVEL       => P_VALIDATION_LEVEL,
      P_INIT_MSG_LIST          => FALSE,
      P_COMMIT                 => FALSE,
      X_MESSAGE_COUNT          => X_MESSAGE_COUNT,
      X_MESSAGE_LIST           => X_MESSAGE_LIST,
      X_RETURN_STATUS          => X_RETURN_STATUS,
      P_BATCH_HEADER           => L_BATCH_HEADER,
      X_BATCH_HEADER           => X_BATCH_HEADER,
      P_IGNORE_SHORTAGES       => TRUE,
      X_UNALLOCATED_MATERIAL   => XX_EXCEPTION_MATERIAL_TBL
   );

   COMMIT;
   FND_FILE.PUT_LINE (FND_FILE.LOG, 'RELEASE BATCH' || X_RETURN_STATUS);
   GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
   FND_FILE.PUT_LINE (FND_FILE.LOG,
                      'RELEASE BATCH =' || TO_CHAR (X_MESSAGE_COUNT));
   ------------------------------------------------------------------------------------------------
   -- IF THE REJECTION ITEM IS NOT A PART OF THE TMS QUERY THEN MAKE IT 0
   IF REJECTION_FLAG = 0
   THEN
      L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
      BEGIN
         SELECT   MATERIAL_DETAIL_ID
           INTO   L_MATERIAL_DETAIL_ID
           FROM   GME_MATERIAL_DETAILS
          WHERE       BATCH_ID = L_BATCH_ID
                  AND LINE_TYPE = 1
                  AND ITEM_ID = V_ITEM_ID;
      END;
      V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
      L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
      L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
      L_MATERIAL_DETAIL.WIP_PLAN_QTY := 0;
      L_MATERIAL_DETAIL.ITEM_ID := V_ITEM_ID;
      L_INDEX := 1;
      L_VALUE_TAB (L_INDEX).P_VALUE := 'WIP_PLAN_QTY';
      GME_API_PUB.UPDATE_MATERIAL_LINE (
         P_API_VERSION        => 1,
         P_VALIDATION_LEVEL   => P_VALIDATION_LEVEL,
         P_INIT_MSG_LIST      => FALSE,
         P_COMMIT             => FALSE,
         X_MESSAGE_COUNT      => X_MESSAGE_COUNT,
         X_MESSAGE_LIST       => X_MESSAGE_LIST,
         X_RETURN_STATUS      => X_RETURN_STATUS,
         P_MATERIAL_DETAIL    => L_MATERIAL_DETAIL,
         P_VALUES_TAB         => L_VALUE_TAB,
         P_SCALE_PHANTOM      => FALSE,
         X_MATERIAL_DETAIL    => X_MATERIAL_DETAIL
      );
      GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
      FND_FILE.PUT_LINE (FND_FILE.LOG,
                         'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
      FND_FILE.PUT_LINE (
         FND_FILE.LOG,
            'x_message_list =
'
         || LENGTH (X_MESSAGE_LIST)
         || SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
      );
      FND_FILE.PUT_LINE (FND_FILE.LOG, 'x_message_list =' || X_MESSAGE_LIST);
      FND_FILE.PUT_LINE (FND_FILE.LOG,
                         'x_return_status =' || X_RETURN_STATUS);
      FND_FILE.PUT_LINE (
         FND_FILE.LOG,
         'x_batch_header.batch_id= ' || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
      );
      COMMIT;
   END IF;
END;
/