CREATE OR REPLACE PROCEDURE APPS.xx_dempo_batch_plant (
   errbuff        OUT      VARCHAR2,
   retcode        OUT      NUMBER,
  -- p_batch_number   IN       VARCHAR2
                                     --   ,p_PLANT_CODE IN VARCHAR2
                                       -- ,p_WHSE_CODE IN VARCHAR2
                                       -- ,P_RECEIPE_CODE IN VARCHAR2
--,
   p_whse_code   IN       VARCHAR2,
   p_shift_code   IN       NUMBER,
   --p_batch_number   IN       VARCHAR2,
   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;
--p_orgn_code VARCHAR2(4) :=p_WHSE_CODE;--DEFAULT 'DC1';
   x_batch_header              gme_batch_header%ROWTYPE;
   Y_Batch_header              gme_batch_header%ROWTYPE;
   Y_BATCH_ID   NUMBER;
-- x_unallocated_material gme_api_pub.unallocated_materials_tab;
   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_whse_code1                VARCHAR2 (30);
   v_item_code                 VARCHAR2 (100);
   v_lot_no                    NUMBER;
   v_lot_no1                    NUMBER;
-- 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;
   bat_no                      VARCHAR2 (1000);
   ln_material_detail_id_product   NUMBER;
-- ENDED
-----
   ln_batch_id                 NUMBER;
   ln_material_detail_id       NUMBER;
   x_item_id                   NUMBER;
   
   
   
   ----
   
   XX_RECIPE_ID     NUMBER;
   XX_RECIPE_NO  VARCHAR2(100);
   XX_RULE_ID   NUMBER;
   XX_RECIPE_VERSION NUMBER;
   XX_FLAG    NUMBER:=0;

-------
--- DECLARE CURSOR
   CURSOR c_get_total_quantity
   IS
      SELECT                                         --route_code,route_name,
               d_dot.whse_code src_whse, d_dot.whse_code dst_whse,
               start_shift_code, (SELECT item_id
                                    FROM ic_item_mst
                                   WHERE item_no = tm_item_code) item_id,
               trip_start_date, tm_item_id, SUM (unload_quantity) qty,
               dot.orgn_code, tm_item_code
          --, whse_code
      FROM     tm_trip_headers tth,
               tm_routes tr,
               tm_trip_lines ttl,
               tm_locations tl,
               dempo_opm_tm dot,
               tm_locations d_tl,
               dempo_opm_tm d_dot
         WHERE                                  --tth.tm_transaction_id=220381
--AND
               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 tr.dest_sub_inventory = d_tl.tm_location_id
           AND d_dot.loc_code =
                           d_tl.tm_location_id
                                              --- Destination  should be Plant
           AND start_shift_code = p_shift_code                             --1
           --AND TO_DATE(TRUNC(trip_start_date),'MM-DD-YYYY' )='01/07/2010' --p_date
           AND TO_CHAR (TRUNC (trip_start_date), 'YYYYMMDD') = p_date--'20100107'
           AND d_dot.whse_code = p_whse_code
           AND d_dot.orgn_type = 'P'
           AND activity_id LIKE 'BUN.FEED%'
           AND ttl.attribute9 IS NULL                -- ie reason code is null
--'20060131'--:p_TRIP_START_DATE
      GROUP BY trip_start_date                      --, route_code,route_name,
                              ,
               d_dot.whse_code,
               start_shift_code,
               tm_item_code,
               tm_item_id,
               dot.orgn_code,
               dot.whse_code;
               
               
               
               
               -----insert line allocation -----------------
               CURSOR C_LINE_ALL(P_ITEM_CODE    VARCHAR2) IS 
                     SELECT                                         --route_code,route_name,
               d_dot.whse_code src_whse, d_dot.whse_code dst_whse,
               start_shift_code, (SELECT item_id
                                    FROM ic_item_mst
                                   WHERE item_no = tm_item_code) item_id,
               trip_start_date, tm_item_id, SUM (unload_quantity) qty,
               dot.orgn_code, tm_item_code
          --, whse_code
      FROM     tm_trip_headers tth,
               tm_routes tr,
               tm_trip_lines ttl,
               tm_locations tl,
               dempo_opm_tm dot,
               tm_locations d_tl,
               dempo_opm_tm d_dot
         WHERE                                  --tth.tm_transaction_id=220381
--AND
               tth.route_id = tr.route_id
           AND tm_item_code= P_ITEM_CODE       
           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 tr.dest_sub_inventory = d_tl.tm_location_id
           AND d_dot.loc_code =
                           d_tl.tm_location_id
                                              --- Destination  should be Plant
           AND start_shift_code = p_shift_code                             --1
           --AND TO_DATE(TRUNC(trip_start_date),'MM-DD-YYYY' )='01/07/2010' --p_date
           AND TO_CHAR (TRUNC (trip_start_date), 'YYYYMMDD') =p_date --'20100107'
           AND d_dot.whse_code = p_whse_code
           AND d_dot.orgn_type = 'P'
           AND activity_id LIKE 'BUN.FEED%'
           AND ttl.attribute9 IS NULL                -- ie reason code is null
--'20060131'--:p_TRIP_START_DATE
      GROUP BY trip_start_date                      --, route_code,route_name,
                              ,
               d_dot.whse_code,
               start_shift_code,
               tm_item_code,
               tm_item_id,
               dot.orgn_code,
               dot.whse_code;
               
               
               
               
               ---------------------------------------------
BEGIN
-- GET THE DETAILS FROM TRUCK M System
   BEGIN
      SELECT DISTINCT d_dot.whse_code src_whse, d_dot.whse_code dst_whse,
                      trip_start_date, dot.orgn_code
                 INTO v_whse_code, v_whse_code1,
                      v_actual_date, v_plant_code
                 FROM tm_trip_headers tth,
                      tm_routes tr,
                      tm_trip_lines ttl,
                      tm_locations tl,
                      dempo_opm_tm dot,
                      tm_locations d_tl,
                      dempo_opm_tm d_dot
                WHERE                           --tth.tm_transaction_id=220381
--AND
                      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 tr.dest_sub_inventory = d_tl.tm_location_id
                  AND d_dot.loc_code =
                           d_tl.tm_location_id
                                              --- Destination  should be Plant
                  AND start_shift_code = p_shift_code                      --1
                  AND TO_CHAR (TRUNC (trip_start_date), 'YYYYMMDD') =p_date
                                                                    --'20100107'
                  AND d_dot.whse_code = p_whse_code
                  AND d_dot.orgn_type = 'P'
                  AND activity_id LIKE 'BUN.FEED%'
                  AND ttl.attribute9 IS NULL AND   dot.orgn_code='DBP'    -- ie reason code is null
--'20060131'--:p_TRIP_START_DATE
      GROUP BY        trip_start_date               --, route_code,route_name,
                                     ,
                      d_dot.whse_code,
                      start_shift_code,
                      tm_item_code,
                      tm_item_id,
                      dot.orgn_code,
                      dot.whse_code;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'error while fetching values from TMS ='
                            || SQLERRM
                           );
   END;

   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;

   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;
   
   
  
   
   
   
   
   

   

   FOR i IN c_get_total_quantity
   LOOP
   XX_FLAG    :=0;
   
     -----------------------FETCHING RECIPE INFORMATIONS----------------------------------------------
   
   BEGIN 
   
   SELECT RECIPE_NO INTO XX_RECIPE_NO FROM TMS_REC 
    WHERE 
        START_SHIFT_CODE= p_shift_code  
    AND TO_CHAR (TRUNC (TRIP_START_DATE), 'YYYYMMDD') = p_date
    AND SRC_WHSE= p_whse_code
    AND TM_ITEM_ID=I.TM_ITEM_ID
    AND QTY=I.QTY;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'error while fetching values  ITEM_ID' || SQLERRM
                           );
    XX_FLAG    :=1;
   END;
   
   --IF XX_FLAG    =1 THEN 
   BEGIN 
    SELECT     RECIPE_ID,RECIPE_VERSION INTO XX_RECIPE_ID,XX_RECIPE_VERSION FROM  gmd_recipes
        WHERE RECIPE_NO=XX_RECIPE_NO
        AND RECIPE_VERSION=(SELECT MAX(RECIPE_VERSION) FROM GMD_RECIPES WHERE RECIPE_NO=XX_RECIPE_NO) ;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'error while fetching values  ITEM_ID' || SQLERRM
                           );
   
   
   END;
   
   BEGIN 
   SELECT RECIPE_VALIDITY_RULE_ID INTO XX_RULE_ID FROM GMD_RECIPE_VALIDITY_RULES  WHERE RECIPE_ID=XX_RECIPE_ID;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'error while fetching values  ITEM_ID' || SQLERRM
                           );
   
   END;
   

   
   BEGIN
      SELECT item_id,item_um
        INTO v_item_id,v_item_uoms    
        FROM fm_matl_dtl
       WHERE formula_id IN (SELECT formula_id
                              FROM gmd_recipes
                             WHERE recipe_id = 31) -- 31 IS THE id for phantom
         AND line_type = -1;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'error while fetching values  ITEM_ID' || SQLERRM
                           );
   END;
   
   
   
 
   
   
   
   
   
   
   
   
   
   
   
      l_batch_header.plant_code := v_plant_code;                     --'DBP';
      l_batch_header.batch_type := p_batch_type;
      l_batch_header.batch_no :=  p_whse_code||'/'||'FEED'||'/'||i.trip_start_date||'/'||p_shift_code||'/'|| i.tm_item_code;


     -- BDW/FEED/01-JAN-10/1/ROM0004
               -- p_batch_number || '/' || i.tm_item_code || '/' || i.trip_start_date;
                           --p_batch_number;                  --'TXIS_TEST5';
      l_batch_header.plan_start_date := SYSDATE;
      l_batch_header.plan_cmplt_date := SYSDATE;
                                  --TO_DATE(SYSDATE,'DD-MON-YYYY HH25:MI:SS');
--l_batch_header.due_date :=TO_DATE(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
      l_batch_header.update_inventory_ind := 'Y';
      l_batch_header.recipe_validity_rule_id := XX_RULE_ID;
      l_batch_header.wip_whse_code := v_whse_code;                   --'DC1';
      l_batch_header.actual_start_date := v_actual_date;
      fnd_global.apps_initialize
                           (user_id           => fnd_profile.VALUE ('USER_ID')
                                                                              --1449
      ,
                            resp_id           => fnd_profile.VALUE ('RESP_ID')
                                                                              --23326
      ,
                            resp_appl_id      => fnd_profile.VALUE
                                                               ('RESP_APPL_ID')
                           --553
                           );
      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                 => XX_RECIPE_NO,
                           p_recipe_version            => 2,
                           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);
      DBMS_OUTPUT.put_line ('x_message_count =' || TO_CHAR (x_message_count));
      DBMS_OUTPUT.put_line (   'x_message_list =
'
                            || LENGTH (x_message_list)
                            || SUBSTR (x_message_list,
                                       LENGTH (x_message_list) - 20,
                                       20
                                      )
                           );
      DBMS_OUTPUT.put_line ('x_message_list =' || x_message_list);
      DBMS_OUTPUT.put_line ('x_return_status =' || x_return_status);
      DBMS_OUTPUT.put_line (   'x_batch_header.batch_id= '
                            || TO_CHAR (x_batch_header.batch_id)
                           );
      DBMS_OUTPUT.put_line (SUBSTR (   'x_batch_header.plant_code ='
                                    || x_batch_header.plant_code,
                                    1,
                                    255
                                   )
                           );
      DBMS_OUTPUT.put_line (SUBSTR (   'x_batch_header.batch_no ='
                                    || x_batch_header.batch_no,
                                    1,
                                    255
                                   )
                           );
----------------WRITE IN THE LOG FILE ---------------------------
      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_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
                                )
                        );
      ------------------create phantom ------------------------------------
      COMMIT;
      ln_batch_id := x_batch_header.batch_id;
      Y_BATCH_ID:=x_batch_header.batch_id;

      BEGIN
         SELECT material_detail_id
           INTO ln_material_detail_id
           FROM gme_material_details
          WHERE batch_id = ln_batch_id AND line_type = -1;
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG,
                               'batch not found' || x_batch_header.batch_id
                              );
      END;

      l_material_detail.batch_id := x_batch_header.batch_id;
      l_material_detail.material_detail_id := ln_material_detail_id;
      gme_api_pub.create_phantom
                          (p_api_version               => 1,
                           p_validation_level          => p_validation_level,
                           p_init_msg_list             => TRUE,
                           p_commit                    => TRUE,
                           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_batch_no                  => p_whse_code||'/'||'FEED'||'/'||i.trip_start_date||'/'||p_shift_code||'/'|| i.tm_item_code
                                                          || '(P)',
                           x_material_detail           => x_material_detail,
                           p_validity_rule_id          => 126,
                           p_ignore_shortages          => FALSE,
                           p_use_shop_cal              => NULL,
                           p_contiguity_override       => 1,
                           x_unallocated_material      => xx_exception_material_tbl
                          );
      fnd_file.put_line (fnd_file.LOG,
                         'return status of phantom' || x_return_status
                        );
      fnd_file.put_line (fnd_file.LOG, 'Error message list' || x_message_list);
      COMMIT;

      BEGIN
         SELECT item_id
           INTO x_item_id
           FROM ic_item_mst
          WHERE item_no = i.tm_item_code;
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG,
                                  'error while fetching values  ITEM_ID2'
                               || SQLERRM
                              );
      END;

      ------------------end of creation of phantom -------------------

      ----------------------UPDATE phantom item to 0-----------------
      BEGIN
         SELECT batch_id
           INTO ln_batch_id
           FROM gme_batch_header
          WHERE batch_no =
                     p_whse_code||'/'||'FEED'||'/'||i.trip_start_date||'/'||p_shift_code||'/'|| i.tm_item_code
                                                          || '(P)';

         BEGIN
            SELECT material_detail_id
              INTO ln_material_detail_id
              FROM gme_material_details
             WHERE batch_id = ln_batch_id AND line_type = -1;
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                  'batch not found' || x_batch_header.batch_id
                                 );
         END;
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG,
                                  'error while fetching values  batch id'
                               || SQLERRM
                              );
      END;
      
      BEGIN 
      SELECT material_detail_id
              INTO ln_material_detail_id_product
              FROM gme_material_details
             WHERE batch_id = ln_batch_id AND line_type = 1;
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                  'batch not found ' || x_batch_header.batch_id
                                 );
         END;
      
      
      

      IF x_item_id = v_item_id
      THEN
      ------------------------UPDATE THE ING-------------------
         --V_MATERIAL_DETAIL_ID:=l_material_detail_id;
         l_material_detail.batch_id := ln_batch_id;
         l_material_detail.material_detail_id := ln_material_detail_id;
         l_material_detail.plan_qty := i.qty;
        -- l_material_detail.item_id := v_item_id;
         --l_material_detail.LINE_NO:=1;
         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
                                   );
         fnd_file.put_line (fnd_file.LOG, 'inside the comparison');
         fnd_file.put_line (fnd_file.LOG,
                            'inside the comparison' || x_return_status
                           );
         gme_debug.display_messages (x_message_count);
         fnd_file.put_line (fnd_file.LOG,
                            'x_message_count =' || 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;
         
         -------------------UPDATING the quantity for product------------------------------------------
         ------------------------UPDATE THE PRODUCT-------------------
         --V_MATERIAL_DETAIL_ID:=l_material_detail_id;
         l_material_detail.batch_id := ln_batch_id;
         l_material_detail.material_detail_id := ln_material_detail_id_product;
         l_material_detail.plan_qty := i.qty;
        -- l_material_detail.item_id := v_item_id;
         --l_material_detail.LINE_NO:=1;
         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
                                   );
         fnd_file.put_line (fnd_file.LOG, 'inside the comparison');
         fnd_file.put_line (fnd_file.LOG,
                            'inside the comparison' || x_return_status
                           );
         gme_debug.display_messages (x_message_count);
         fnd_file.put_line (fnd_file.LOG,
                            'x_message_count =' || 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----------------------------------------------------------------------- 
        
        
        
        ---------------fetching lot number for feed of phantom ------------------------
        
        
        BEGIN 
                SELECT LOT_SUFFIX-1 INTO V_LOT_NO1 FROM  IC_ITEM_MST WHERE ITEM_ID=226;
                      -- fnd_file.put_line (fnd_file.LOG, 'lot number for item is ' || V_ITEM_CODE||' '||V_LOT_NO||'  '||x_item_id||'  '||ln_material_detail_id+1);   
                EXCEPTION 
                    WHEN OTHERS THEN 
                        FND_FILE.PUT_LINE(FND_FILE.LOG,'UNABLE TO FIND ITEM'||x_item_id||sqlerrm);
                END ;
                   FND_FILE.PUT_LINE(FND_FILE.LOG,'lot no'||V_LOT_NO||','||x_item_id);
        
        
        
        
        
        
        
        
        ------------------------------------------end-------------------------------------
          BEGIN 
                SELECT LOT_SUFFIX INTO V_LOT_NO FROM  IC_ITEM_MST WHERE ITEM_ID=x_item_id;
                      -- fnd_file.put_line (fnd_file.LOG, 'lot number for item is ' || V_ITEM_CODE||' '||V_LOT_NO||'  '||x_item_id||'  '||ln_material_detail_id+1);   
                EXCEPTION 
                    WHEN OTHERS THEN 
                        FND_FILE.PUT_LINE(FND_FILE.LOG,'UNABLE TO FIND ITEM'||x_item_id||sqlerrm);
                END ;
                   FND_FILE.PUT_LINE(FND_FILE.LOG,'lot no'||V_LOT_NO||','||x_item_id);
      
      FOR J IN C_LINE_ALL(I.tm_item_code)
      LOOP
                -- /////////////////////////////////////////////////////////////
                -- Set Values
                -- /////////////////////////////////////////////////////////////
                V_LOT_NO:=V_LOT_NO-1;
                L_TRAN_ROW.doc_id := lN_batch_id;
                L_TRAN_ROW.material_detail_id := ln_material_detail_id;
                L_TRAN_ROW.whse_code := J.dst_whse;
                --L_TRAN_ROW.LOCATION := P_LOCATION;
                --L_TRAN_ROW.lot_id := P_LOT_ID;
                L_TRAN_ROW.trans_qty := J.QTY;
                --L_TRAN_ROW.trans_qty2 := P_QTY2;
                --L_TRAN_ROW.completed_ind := 1;
                L_TRAN_ROW.trans_date := SYSDATE;
                --L_TRAN_ROW.reason_code := P_REASON_CODE;
                -- /////////////////////////////////////////////////////////////
            
                -- /////////////////////////////////////////////////////////////
                -- 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_sublot_no          IN              VARCHAR2 DEFAULT NULL,
                p_create_lot         =>TRUE                ,
            --    p_ignore_shortage    IN              BOOLEAN DEFAULT FALSE ,
                --p_scale_phantom      IN              BOOLEAN DEFAULT FALSE ,
                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_list = alloc' || x_message_list);
      fnd_file.put_line (fnd_file.LOG, 'x_return_status = alloc' || x_return_status);
      V_LOT_NO:=V_LOT_NO+1;
      
      
      
      -------------------------INSERT THE SAME LINE ALLOCATION FOR THE PRODUCT------------------------------
       -- /////////////////////////////////////////////////////////////
                -- Set Values
                -- /////////////////////////////////////////////////////////////
                V_LOT_NO:=V_LOT_NO1;
                L_TRAN_ROW.doc_id := lN_batch_id;
                L_TRAN_ROW.material_detail_id := ln_material_detail_id_product;
                L_TRAN_ROW.whse_code := J.dst_whse;
                --L_TRAN_ROW.LOCATION := P_LOCATION;
                --L_TRAN_ROW.lot_id := P_LOT_ID;
                L_TRAN_ROW.trans_qty := J.QTY;
                --L_TRAN_ROW.trans_qty2 := P_QTY2;
                --L_TRAN_ROW.completed_ind := 1;
                L_TRAN_ROW.trans_date := SYSDATE;
                --L_TRAN_ROW.reason_code := P_REASON_CODE;
                -- /////////////////////////////////////////////////////////////
            
                -- /////////////////////////////////////////////////////////////
                -- 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_NO1,
            --    p_sublot_no          IN              VARCHAR2 DEFAULT NULL,
                p_create_lot         =>TRUE                ,
            --    p_ignore_shortage    IN              BOOLEAN DEFAULT FALSE ,
                --p_scale_phantom      IN              BOOLEAN DEFAULT FALSE ,
                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_list = alloc' || x_message_list);
      fnd_file.put_line (fnd_file.LOG, 'x_return_status = alloc' || x_return_status);
      V_LOT_NO1:=V_LOT_NO1+1;
      
      
      
      -------------------------------------END----------------------------------------------------------------
      END LOOP;
                 
                 

COMMIT;
         
         
         
         
         
    ELSE 
        ---------------------------------FOR NOT MACHING WE NEED TO UPDATE THE QTY TO ZERO-------------------------
        
        
        --V_MATERIAL_DETAIL_ID:=l_material_detail_id;
         l_material_detail.batch_id := ln_batch_id;
         l_material_detail.material_detail_id := ln_material_detail_id;
         l_material_detail.plan_qty := 0;
         --l_material_detail.item_id := v_item_id;
         
         fnd_file.put_line (fnd_file.LOG, 'ERROR DEBUG'||'BATCH_ID-->'||ln_batch_id||'DETAIL-->'||ln_material_detail_id||
         ' '||v_item_id);
         
         --l_material_detail.LINE_NO:=1;
         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                => TRUE,
                                    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
                                   );
         fnd_file.put_line (fnd_file.LOG, 'inside the comparison');
         fnd_file.put_line (fnd_file.LOG,
                            'inside the comparison OF ZERO' || x_return_status
                           );
         gme_debug.display_messages (x_message_count);
         fnd_file.put_line (fnd_file.LOG,
                            'x_message_count =' || 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;
        
        
        
        
        
        
        -----------------------------
        
        select max(material_detail_id) into ln_material_detail_id from gme_material_Details;
        
        
        --------------------INSERT MATERIAL LINE -------------------------------------------
        l_material_detail.batch_id := ln_batch_id;
      l_material_detail.material_detail_id := ln_material_detail_id+1;
--l_material_detail.ORIGINAL_QTY:= 3;
      l_material_detail.plan_qty := I.qty;
      l_material_detail.ITEM_ID:=x_item_id;
      l_material_detail.LINE_NO:=2;
      l_material_detail.ITEM_UM:= v_item_uoms;
      l_material_detail.line_type := -1;
      l_material_detail.SCALE_type := 1 ;
       l_material_detail.COST_ALLOC:=0;
       l_material_detail.CONTRIBUTE_STEP_QTY_IND:='Y';
       l_material_detail.ALLOC_IND:=0;
       l_material_detail.SCRAP_FACTOR:=0;
       l_material_detail.ORIGINAL_QTY:=0.000000;
       l_material_detail.PHANTOM_TYPE:=0;
        
         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, '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;
        
        
        
        ---------------------------------------------------------------------------------------------
        
        
        
        ------------------------------------------create pending transactions--------------------
        
        l_transactions(1).item_id:=x_item_id;
                    l_transactions(1).line_id:=ln_material_detail_id+1;
                    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:=ln_batch_id;
                    l_transactions(1).DOC_TYPE:='PROD';
                    --l_transactions(1).DOC_LINE:=3;
                    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:=SYSDATE;
                    l_transactions(1).orgn_code:=v_plant_code;
                    --l_transactions(1).LOT_sTATUS:='APPR';
                    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   
                 );   
                 
                 ---------------fetching lot number for feed of phantom ------------------------
        
        
        BEGIN 
                SELECT LOT_SUFFIX-1 INTO V_LOT_NO1 FROM  IC_ITEM_MST WHERE ITEM_ID=226;
                      -- fnd_file.put_line (fnd_file.LOG, 'lot number for item is ' || V_ITEM_CODE||' '||V_LOT_NO||'  '||x_item_id||'  '||ln_material_detail_id+1);   
                EXCEPTION 
                    WHEN OTHERS THEN 
                        FND_FILE.PUT_LINE(FND_FILE.LOG,'UNABLE TO FIND ITEM'||x_item_id||sqlerrm);
                END ;
                   FND_FILE.PUT_LINE(FND_FILE.LOG,'lot no'||V_LOT_NO||','||x_item_id);
        
        
        
        
        
        
        
        
        ------------------------------------------end-------------------------------------
                       
      BEGIN 
                SELECT LOT_SUFFIX INTO V_LOT_NO FROM  IC_ITEM_MST WHERE ITEM_ID=x_item_id;
                      -- fnd_file.put_line (fnd_file.LOG, 'lot number for item is ' || V_ITEM_CODE||' '||V_LOT_NO||'  '||x_item_id||'  '||ln_material_detail_id+1);   
                EXCEPTION 
                    WHEN OTHERS THEN 
                        FND_FILE.PUT_LINE(FND_FILE.LOG,'UNABLE TO FIND ITEM'||x_item_id||sqlerrm);
                END ;
                   FND_FILE.PUT_LINE(FND_FILE.LOG,'lot no'||V_LOT_NO||','||x_item_id);
      
      FOR J IN C_LINE_ALL(I.tm_item_code)
      LOOP
         -- /////////////////////////////////////////////////////////////
                -- Set Values
                -- /////////////////////////////////////////////////////////////
                V_LOT_NO:=V_LOT_NO-1;
                L_TRAN_ROW.doc_id := lN_batch_id;
                L_TRAN_ROW.material_detail_id := ln_material_detail_id+1;
                L_TRAN_ROW.whse_code := J.dst_whse;
                --L_TRAN_ROW.LOCATION := P_LOCATION;
                --L_TRAN_ROW.lot_id := P_LOT_ID;
                L_TRAN_ROW.trans_qty := J.QTY;
                --L_TRAN_ROW.trans_qty2 := P_QTY2;
                --L_TRAN_ROW.completed_ind := 1;
                L_TRAN_ROW.trans_date := SYSDATE;
                --L_TRAN_ROW.reason_code := P_REASON_CODE;
                -- /////////////////////////////////////////////////////////////
            
                -- /////////////////////////////////////////////////////////////
                -- 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_sublot_no          IN              VARCHAR2 DEFAULT NULL,
                p_create_lot         =>TRUE                ,
            --    p_ignore_shortage    IN              BOOLEAN DEFAULT FALSE ,
                --p_scale_phantom      IN              BOOLEAN DEFAULT FALSE ,
                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_list = alloc' || x_message_list);
      fnd_file.put_line (fnd_file.LOG, 'x_return_status = alloc' || x_return_status);
      V_LOT_NO:=V_LOT_NO+1;
      
       -------------------------INSERT THE SAME LINE ALLOCATION FOR THE PRODUCT------------------------------
       -- /////////////////////////////////////////////////////////////
                -- Set Values
                -- /////////////////////////////////////////////////////////////
                V_LOT_NO:=V_LOT_NO1;
                L_TRAN_ROW.doc_id := lN_batch_id;
                L_TRAN_ROW.material_detail_id := ln_material_detail_id_product;
                L_TRAN_ROW.whse_code := J.dst_whse;
                --L_TRAN_ROW.LOCATION := P_LOCATION;
                --L_TRAN_ROW.lot_id := P_LOT_ID;
                L_TRAN_ROW.trans_qty := J.QTY;
                --L_TRAN_ROW.trans_qty2 := P_QTY2;
                --L_TRAN_ROW.completed_ind := 1;
                L_TRAN_ROW.trans_date := SYSDATE;
                --L_TRAN_ROW.reason_code := P_REASON_CODE;
                -- /////////////////////////////////////////////////////////////
            
                -- /////////////////////////////////////////////////////////////
                -- 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_NO1,
            --    p_sublot_no          IN              VARCHAR2 DEFAULT NULL,
                p_create_lot         =>TRUE                ,
            --    p_ignore_shortage    IN              BOOLEAN DEFAULT FALSE ,
                --p_scale_phantom      IN              BOOLEAN DEFAULT FALSE ,
                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_list = alloc' || x_message_list);
      fnd_file.put_line (fnd_file.LOG, 'x_return_status = alloc' || x_return_status);
      V_LOT_NO1:=V_LOT_NO1+1;
     ---------------------------------------------------------------------------END--------------------------------------------------------
      
      
      END LOOP;
                 
                 

COMMIT;
        
        
        -------------------------------------------end-----------------------------------------------
     -----------------------------
     ------------------------UPDATE THE PRODUCT-------------------
         --V_MATERIAL_DETAIL_ID:=l_material_detail_id;
         l_material_detail.batch_id := ln_batch_id;
         l_material_detail.material_detail_id := ln_material_detail_id_product;
         l_material_detail.plan_qty := i.qty;
        -- l_material_detail.item_id := v_item_id;
         --l_material_detail.LINE_NO:=1;
         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
                                   );
         fnd_file.put_line (fnd_file.LOG, 'inside the comparison');
         fnd_file.put_line (fnd_file.LOG,
                            'inside the comparison' || x_return_status
                           );
         gme_debug.display_messages (x_message_count);
         fnd_file.put_line (fnd_file.LOG,
                            'x_message_count =' || 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;

      COMMIT;
      
      
      --------------------------YIELD CALCULATE---------------------------------
      Y_Batch_header.BATCH_ID:=Y_BATCH_ID;
      
      GME_API_PUB.theoretical_yield_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      =>Y_Batch_header,
                                    p_scale_factor      =>100);
      
            COMMIT;
        
fnd_file.put_line (fnd_file.LOG, 'inside YIELD');
         fnd_file.put_line (fnd_file.LOG,
                            'inside the YIELD' || x_return_status
                           );
         gme_debug.display_messages (x_message_count);
         fnd_file.put_line (fnd_file.LOG,
                            'x_message_count YIELD =' || TO_CHAR (x_message_count)
                           );
         fnd_file.put_line (fnd_file.LOG,
                               'x_message_list YIELD =
    '
                            || LENGTH (x_message_list)
                            || SUBSTR (x_message_list,
                                       LENGTH (x_message_list) - 20,
                                       20
                                      )
                           );
         
--ELSE 
--fnd_file.put_line (fnd_file.LOG,'DATA DOES NOT EXISTS IN THE TMS FORM');
         
      --   END IF ;      
      
      ----------------------------------END----------------------------------------

   END LOOP;
   
-- IF x_return_status = 'S'  THEN
commit;
--- UPDATE THE BATCH WITH THE QUANTITY

------------------END OF UPDATE--------------------------------
END;
/