API for Manual allocation of lot number when shipping lot

hello this API use for control lot number when you ship. normally oracle select lot number as FIFO based so if we want to ship lot which based on order number then we need to manual allocation of that lot number

here we suppose manufacturing unit is maxico and shipping unit means supplier is USA

step  1. insert in a table 

CREATE OR REPLACE procedure APPS.inst_lot_int_tab (p_po_header_id in number,p_org_id in number ,p_pack_list in varchar2)
is
   CURSOR get_po_line_det
   IS
      SELECT poh.vendor_id, pol.item_id, pol.attribute13 order_line_id,
             pol.item_id inventory_item_id, pll.*
        FROM po_headers_all poh, po_lines_all pol, po_line_locations_all pll
       WHERE 1 = 1
         AND poh.po_header_id = pol.po_header_id
         AND pol.po_header_id = pll.po_header_id
         AND pol.po_line_id = pll.po_line_id
         AND poh.po_header_id = p_po_header_id
         AND poh.org_id = p_org_id;

   CURSOR cur_lot_det
   IS
             
                SELECT DISTINCT fraa.roll_number, wdd.shipped_quantity
                 FROM ems_packing_list emsh,
                      fil_roll_assgn_attb fraa,
                      wsh_delivery_details wdd,
                      xxuflx_trd_ship_lines_all xtl
                WHERE NVL (fraa.diverted_header_id, fraa.header_id) =
                                                          emsh.order_header_id
                  AND wdd.source_header_id =
                                 NVL (fraa.diverted_header_id, fraa.header_id)
                  AND xtl.receive_flag = 'Y'
                  AND xtl.ship_quantity IS NULL
                  AND xtl.packing_list_number = p_pack_list     --- packing list number of mafacuring site
                  AND xtl.roll_number = fraa.roll_number
                  AND xtl.packing_list_number = emsh.packing_list_number
                  AND xtl.item_code = fraa.item_code
                  AND xtl.roll_number = wdd.lot_number;    
                 
BEGIN
   FOR line_rec IN get_po_line_det
   LOOP
      --
      FOR lot_rec IN cur_lot_det
      LOOP
         --
         DBMS_OUTPUT.put_line ('insert shipment lot rec');

         --
         INSERT INTO mtl_transaction_lots_interface
                     (transaction_interface_id, source_code, product_code,
                      product_transaction_id,
                      last_update_date,
                      last_updated_by,
                      creation_date, created_by,
                      lot_number,
                      transaction_quantity, primary_quantity, process_flag
                     )
              VALUES (mtl_material_transactions_s.NEXTVAL
                                                         --transaction_interface_id
         ,            'PO', 'RCV',                             -- product_code
                      rcv_transactions_interface_s.CURRVAL,
                      SYSDATE                              -- last_update_date
                             ,
                      6055                           -- last_updated_by
                                 ,
                      SYSDATE                                 -- creation_date
                             ,6055                    -- created_by
                                          ,
                      lot_rec.roll_number                         --LOT_NUMBER
                                         ,
                      lot_rec.shipped_quantity
                                              --TRANSACTION_QUANTITY
         ,            lot_rec.shipped_quantity
                                              --primary_quantity,
         ,            1
                     );
      END LOOP;
   --
   END LOOP;
END;
/


step 2. create packing list in usa

CREATE OR REPLACE PROCEDURE APPS.create_packing_list_pk (
      p_packing_list      IN       VARCHAR2,
      x_return_status     OUT      VARCHAR2,
      x_return_msg        OUT      VARCHAR2,
      p_us_packing_list   OUT      VARCHAR2
   )
   IS
      lv_order_number          VARCHAR2 (100);
      lv_str                   VARCHAR2 (100);
      lv_header_id             NUMBER;
      lv_order_number_new      VARCHAR2 (100);
      -- lv_error_message         VARCHAR2 (200);
      lv_packing_list_roll     VARCHAR2 (200);
      lv_commercial_category   VARCHAR2 (200);
      lv_line_number           VARCHAR2 (200);
      lv_width                 VARCHAR2 (200);
      lv_location              VARCHAR2 (200);
      lv_count1                NUMBER;
      lv_old_header_id         NUMBER;
      lv_error_message         VARCHAR2 (200);
      lv_order_header_id       NUMBER;
      lv_mex_pack_qty          NUMBER;
      lv_us_rcvd_qty           NUMBER;

      CURSOR cur_pallet_number (
         p_header_id          IN   NUMBER,
         p_source_header_id   IN   NUMBER
      )
      IS
         SELECT header_id,
                line_id,
                pallet_number,
                SUBSTR (pallet_number, -4) str,
                pallet_weight,
                description,
                pallet_type,
                pallet_roll
         FROM   ems_pallet_number epn
         WHERE  header_id = (select header_id from ems_packing_listwhere packing_list_number=p_packing_list)
         AND    org_id IN (406,82)---= source_org_id
         AND    EXISTS (
                   SELECT 'y'
                   FROM   ems_assign_lot eat
                   WHERE  header_id = epn.header_id
                   AND    line_id = epn.line_id
                   AND    org_id = epn.org_id
                   AND    EXISTS (
                             SELECT 'y'
                             FROM   wsh_delivery_details
                             WHERE  source_header_id = (select order_header_id from ems_packing_list
                              where packing_list_number=p_packing_list)
                             AND    lot_number IS NOT NULL
                             AND    lot_number = eat.lot_sublot_number))
                             ;

      CURSOR cur_lot_number (
         p_header_id          IN   NUMBER,
         p_line_id            IN   NUMBER,
         p_source_header_id   IN   NUMBER
      )
      IS
         SELECT   lot_sublot_number,
                  SUM (shipped_quantity) roll_weight,
                  description,
                  fitment
         FROM     ems_assign_lot eat, wsh_delivery_details wdd
         WHERE    header_id = (select header_id from ems_packing_list where packing_list_number=p_packing_list)
         AND      line_id = p_line_id
         AND      eat.org_id IN (406,82)---= source_org_id
         AND      source_header_id = (select order_header_id from ems_packing_list
                              where packing_list_number=p_packing_list)
         AND      lot_number IS NOT NULL
         AND      lot_number = eat.lot_sublot_number
         GROUP BY lot_sublot_number, description, fitment
         union
          SELECT   lot_sublot_number,
                  SUM (shipped_quantity) roll_weight,
                  description,
                  fitment
         FROM     ems_assign_lot eat, wsh_delivery_details wdd
         WHERE    header_id = (select header_id from ems_packing_list where packing_list_number=p_packing_list)
         AND      line_id = p_line_id
         AND      eat.org_id IN (406,241)---= source_org_id
         AND      source_header_id = (select order_header_id from ems_packing_list where packing_list_number=p_packing_list)
         AND      lot_number IS NOT NULL
         AND      lot_number = eat.lot_sublot_number
         GROUP BY lot_sublot_number, description, fitment;
   BEGIN
      --
      dbms_output.put_line('Inserting Packing List ' || p_packing_list);

      --
      BEGIN
         select  header_id,
                order_number,
                packing_list_number,
                packing_list_roll,
                commercial_category,
                line_number,
                width,
                LOCATION,
                order_header_id
                INTO   
                lv_old_header_id,
                lv_order_number,
                lv_str,
                lv_packing_list_roll,
                lv_commercial_category,
                lv_line_number,
                lv_width,
                lv_location,
                lv_order_header_id 
                from
                ( SELECT header_id,
                order_number,
                SUBSTR (packing_list_number, -4) packing_list_number,
                packing_list_roll,
                commercial_category,
                line_number,
                width,
                LOCATION,
                order_header_id 
                from ems_packing_list
                WHERE  packing_list_number =p_packing_list
                AND org_id IN (406,82)
           union 
                SELECT header_id,
                order_number,
                SUBSTR (packing_list_number, -4) packing_list_number,
                packing_list_roll,
                commercial_category,
                line_number,
                width,
                LOCATION,
                order_header_id
         FROM   ems_packing_list
         WHERE  packing_list_number =p_packing_list
                AND org_id IN (406,241)  )   ;   
                
      EXCEPTION
         WHEN OTHERS
         THEN
            dbms_output.put_line('Invalid Packing List');
            lv_order_number := NULL;
            x_return_status := 'E';
            x_return_msg := 'Invalid packing list ' || p_packing_list;
      END;

      --
      -- Added by Abhishek on 06 May-2014
      BEGIN
     
        SELECT SUM (wdd.shipped_quantity) shipped_quantity
        
         FROM   ems_packing_list emsh,
                ems_pallet_number emsl,
                ems_assign_lot eal,
                fil_roll_assgn_attb fraa,
                wsh_delivery_details wdd,
                oe_order_lines_all ola
         WHERE  emsh.header_id = emsl.header_id
         AND    ola.header_id = wdd.source_header_id
         AND    ola.line_id = wdd.source_line_id
         AND    emsl.line_id = eal.line_id
         AND    NVL (fraa.diverted_header_id, fraa.header_id) =
                                                          emsh.order_header_id
         AND    roll_number = lot_sublot_number
         AND    wdd.source_header_id =
                                 NVL (fraa.diverted_header_id, fraa.header_id)
         --  AND wdd.source_line_id = NVL(fraa.diverted_line_id, fraa.line_id)
         AND    wdd.org_id IN (406,241)--= source_org_id
         AND    wdd.released_status = 'C'
         AND    wdd.lot_number = fraa.roll_number
         AND    packing_list_number = p_packing_list) ;
      --
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_mex_pack_qty := 0;
      END;

      dbms_output.put_line('lv_mex_pack_qty: ' || lv_mex_pack_qty);

      --
      -- Added by Abhishek on 06 May-2014
      BEGIN
         SELECT SUM (quantity_received) rcvd_qty
         INTO   lv_us_rcvd_qty
         FROM   rcv_shipment_headers rcv, rcv_shipment_lines rsl
         WHERE  rcv.attribute15 = p_packing_list
         AND    rcv.shipment_header_id = rsl.shipment_header_id;
      --
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_us_rcvd_qty := 0;
      END;

      dbms_output.put_line('lv_us_rcvd_qty: ' || lv_us_rcvd_qty);

      --
      IF lv_mex_pack_qty = lv_us_rcvd_qty
      THEN
         -- Added by Abhishek on 06 May-2014
         IF lv_order_number IS NOT NULL
         THEN
            --
            dbms_output.put_line('get order number');

            --
            BEGIN
               
          
                    
                      SELECT ooh.header_id,
                      ooh.order_number
              INTO    lv_header_id,
                      lv_order_number_new
               FROM   oe_order_headers_all ooh  
               WHERE  order_number = (select distinct xxth.trd_oe_order_number from XXUFLX_TRD_SHIP_HEADERS_ALL xxth,XXUFLX_TRD_SHIP_LINES_ALL xxtl 
                                              where xxtl.trd_ship_header_id=xxth.TRD_SHIP_HEADER_ID
                                              and xxtl.packing_list_number=p_packing_list);
            
               
            EXCEPTION
               WHEN OTHERS
               THEN
                  dbms_output.put_line('Order Number is not created in USA');
                  lv_order_number_new := NULL;
                  x_return_status := 'E';
                  x_return_msg := 'Order Number is not created in US';
            END;

            IF lv_order_number_new IS NOT NULL
            THEN
               -- check if packing list exists
               SELECT COUNT (*)
               INTO   lv_count1
               FROM   ems_packing_list
               WHERE  packing_list_number = lv_order_number_new || lv_str
               AND    org_id = 241;

               --
               dbms_output.put_line('packing list count ' || lv_count1);
               dbms_output.put_line('New packing list' || lv_order_number_new || lv_str);

               --
               IF lv_count1 = 0
               THEN
                  --
                  BEGIN
                     --
                     INSERT INTO ems_packing_list
                                 (header_id,
                                  order_number,
                                  packing_list_number,
                                  packing_list_roll,
                                  commercial_category,
                                  line_number,
                                  width,
                                  description,
                                  created_by,
                                  creation_date,
                                  last_updated_by,
                                  last_update_date,
                                  order_header_id,
                                  LOCATION,
                                  org_id
                                 )
                     VALUES      (header_seq.NEXTVAL,
                                  lv_order_number_new,
                                  lv_order_number_new || lv_str,
                                  lv_packing_list_roll,
                                  lv_commercial_category,
                                  lv_line_number,
                                  lv_width,
                                  'CLOSED',
                                  6055,
                                  SYSDATE,
                                  6055,
                                  SYSDATE,
                                  lv_header_id,
                                  lv_location,
                                  241
                                 );

                     --
                     dbms_output.put_line('header inserted');
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        lv_error_message := SUBSTR (SQLERRM, 1, 200);
                        dbms_output.put_line
                            (   'Oracle Error when crearting packing list:- '
                             || lv_error_message
                            );
                        x_return_status := 'E';
                        x_return_msg :=
                              'error while inserting packing list header '
                           || SQLERRM;
                  END;

                  IF NVL (x_return_status, 'S') <> 'E'
                  THEN
                     --
                     FOR rec_pallet_number IN
                        cur_pallet_number (lv_old_header_id,
                                           lv_order_header_id
                                          )
                     LOOP
                        --
                        BEGIN
                           --
                           INSERT INTO ems_pallet_number
                                       (header_id,
                                        line_id,
                                        pallet_number,
                                        pallet_roll,
                                        pallet_weight,
                                        description,
                                        created_by,
                                        creation_date,
                                        last_updated_by,
                                        last_update_date,
                                        org_id,
                                        pallet_type
                                       )
                           VALUES      (header_seq.CURRVAL,
                                        line_seq.NEXTVAL,
                                           lv_order_number_new
                                        || rec_pallet_number.str,
                                        rec_pallet_number.pallet_roll,
                                        rec_pallet_number.pallet_weight,
                                        rec_pallet_number.description,
                                        6055,
                                        SYSDATE,
                                        6055,
                                        SYSDATE,
                                        241,
                                        rec_pallet_number.pallet_type
                                       );

                           --
                           dbms_output.put_line('packing list line entered');
                        EXCEPTION
                           WHEN OTHERS
                           THEN
                              lv_error_message := SUBSTR (SQLERRM, 1, 200);
                              dbms_output.put_line
                                 (   'Oracle Error when crearting packing list:- '
                                  || lv_error_message
                                 );
                              x_return_status := 'E';
                              x_return_msg :=
                                    'error while inserting packing list lines '
                                 || SQLERRM;
                        END;

                        IF NVL (x_return_status, 'S') <> 'E'
                        THEN
                           FOR rec_lot_number IN
                              cur_lot_number (rec_pallet_number.header_id,
                                              rec_pallet_number.line_id,
                                              lv_order_header_id
                                             )
                           LOOP
                              BEGIN
                                 INSERT INTO ems_assign_lot
                                             (header_id,
                                              line_id,
                                              assign_lot_id,
                                              lot_sublot_number,
                                              roll_weight,
                                              description,
                                              org_id,
                                              created_by,
                                              creation_date,
                                              last_update_date,
                                              last_updated_by,
                                              fitment
                                             )
                                 VALUES      (header_seq.CURRVAL,
                                              line_seq.CURRVAL,
                                              shipment_seq.NEXTVAL,
                                              rec_lot_number.lot_sublot_number,
                                              rec_lot_number.roll_weight,
                                              rec_lot_number.description,
                                              241,
                                              6055,
                                              SYSDATE,
                                              SYSDATE,
                                              6055,
                                              rec_lot_number.fitment
                                             );
                              EXCEPTION
                                 WHEN OTHERS
                                 THEN
                                    dbms_output.put_line
                                       (   'Oracle Error when crearting packing list Lot Number:- '
                                        || rec_lot_number.lot_sublot_number
                                       );
                                    lv_error_message :=
                                                      SUBSTR (SQLERRM, 1, 200);
                                    dbms_output.put_line
                                       (   'Oracle Error when crearting packing list:- '
                                        || lv_error_message
                                       );
                                    x_return_status := 'E';
                                    x_return_msg :=
                                          'error while inserting packing list lots '
                                       || SQLERRM;
                              END;
                           END LOOP;
                        END IF;
                     END LOOP;
                  END IF;

                  x_return_msg :=
                        'Packing list successfully created. Packing List# is '
                     || lv_order_number_new
                     || lv_str;
               ELSE
                  x_return_status := 'S';
                  x_return_msg := 'Packing list already exists in USA';
               END IF;

               --
               p_us_packing_list := lv_order_number_new || lv_str;
            --
            END IF;
         END IF;
      ELSE
         x_return_status := 'E';
         x_return_msg :=
               'Quantity Mismatch in US Received Quantity ('
            || lv_us_rcvd_qty
            || ') and Mexico Packing List Quantity ('
            || lv_mex_pack_qty
            || ')';
      END IF;

      ---
      IF x_return_status = 'E'
      THEN
         ROLLBACK;
      ELSE
         x_return_status := 'S';
         COMMIT;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         lv_error_message := SUBSTR (SQLERRM, 1, 200);
         dbms_output.put_line('Oracle Error :- ' || lv_error_message);
         x_return_status := 'E';
         x_return_msg := 'Exception while creating packing list ' || SQLERRM;
   END ;

   --shipping
/


now you check new packing list number in ems_packing_list table


3. custom table xxuflex_fil_roll_assgn_attb



CREATE GLOBAL TEMPORARY TABLE APPS.XXUFLEX_FIL_ROLL_ASSGN_ATTB
(
  ROLL_NUMBER            VARCHAR2(64 BYTE),
  HEADER_ID              NUMBER,
  ORG_ID                 NUMBER,
  LINE_ID                NUMBER,
  ITEM_CODE              VARCHAR2(30 BYTE),
  ACTUAL_LENGTH          NUMBER(10),
  OPT_DENS               NUMBER(5,2),
  TRTMNT_IN              VARCHAR2(3 BYTE),
  TRTMNT_OUT             VARCHAR2(3 BYTE),
  CORE_ID                NUMBER(5),
  ROLL_WIDTH             NUMBER(6),
  GROSS_ROLL_WT          NUMBER(6,1),
  NET_ROLL_WT            NUMBER(6,1),
  ROLL_GRADE             VARCHAR2(5 BYTE),
  BATCH_ID               NUMBER(10),
  BATCH_NO               VARCHAR2(32 BYTE),
  LOT_NO                 VARCHAR2(32 BYTE),
  SUBLOT_NO              VARCHAR2(32 BYTE),
  GRADE_REASON           VARCHAR2(30 BYTE),
  NUMBER_OF_JOINTS       NUMBER,
  FORMULA_CLASS          VARCHAR2(8 BYTE),
  LENGTH                 NUMBER(5),
  OUT_DIA                NUMBER(7,2),
  ORDER_NUMBER           NUMBER,
  DIVERTED_ORDER_NUMBER  NUMBER,
  DIVERTED_HEADER_ID     NUMBER,
  DIVERTED_LINE_ID       NUMBER,
  WAREHOUSE              VARCHAR2(4 BYTE),
  LINE_SHIPMENT_NUMBER   VARCHAR2(10 BYTE),
  JUMBO_LOT_NO           VARCHAR2(1000 BYTE),
  JUMBO_SUBLOT_NO        VARCHAR2(1000 BYTE),
  JUMBO_WT               VARCHAR2(1000 BYTE),
  JUMBO_WIDTH            VARCHAR2(1000 BYTE),
  DIVERTED_ORDER_DATE    DATE,
  ROLL_ASSGN_DATE        DATE,
  NET_WT_REMAINING       NUMBER,
  JUMBO_ONHAND_QTY       VARCHAR2(1000 BYTE),
  JUMBO_ONHAND_LENGTH    VARCHAR2(1000 BYTE),
  LOT_ID                 NUMBER,
  ITEM_ID                NUMBER                 NOT NULL,
  CREATED_BY             NUMBER(15),
  CREATION_DATE          DATE,
  LAST_UPDATED_BY        NUMBER(15),
  LAST_UPDATE_DATE       DATE,
  LAST_UPDATE_LOGIN      NUMBER(15),
  CORE_DETAIL            VARCHAR2(100 BYTE),
  CORE_WT                NUMBER(30),
  PRINT_FLAG             CHAR(1 BYTE),
  ROLL_LOCATION          VARCHAR2(50 BYTE),
  PK                     VARCHAR2(1 BYTE),
  CHK_BOX                VARCHAR2(3 BYTE)
)
ON COMMIT PRESERVE ROWS
NOCACHE;




4.CONTROL_LOT

CREATE OR REPLACE PROCEDURE APPS.control_lot_pk (
      p_packing_list_number      IN       VARCHAR,
      p_us_packing_list_number   IN       VARCHAR,
      x_return_message           OUT      VARCHAR2,
      x_return_status            OUT      VARCHAR2
   )
   IS
      CURSOR cur_rec
      IS
         SELECT DISTINCT NVL (fraa.diverted_header_id, fraa.header_id)
                                                                    header_id,
                         NVL (fraa.diverted_line_id, fraa.line_id) line_id
         FROM            ems_packing_list emsh,
                         ems_pallet_number emsl,
                         ems_assign_lot eal,
                         xxuflex_fil_roll_assgn_attb fraa
         WHERE           emsh.header_id = emsl.header_id
         AND             emsl.line_id = eal.line_id
         AND             packing_list_number = p_packing_list_number
--         AND             NVL (fraa.diverted_header_id, fraa.header_id) = emsh.order_header_id
         AND             roll_number = lot_sublot_number
   r;    
       

      CURSOR get_delivery_det (
         p_source_header_id   IN   NUMBER,
         p_source_line_id     IN   NUMBER
      )
      IS
       SELECT wdd.*
         FROM   wsh_delivery_details wdd            --, oe_order_lines_all ola
         WHERE  source_header_id = p_source_header_id
         AND    wdd.source_line_id = get_line_id_pk(p_source_line_id)
         AND    move_order_line_id IS NULL
         AND    released_status IN ('R', 'B')
   
         ;

      --
      l_return_status      VARCHAR2 (10)                          := 'S';
      l_msg_count          NUMBER (15);
      l_msg_data           VARCHAR2 (4000);
      --
      --
      p_new_batch_id       NUMBER;
      l_rule_id            NUMBER;
      l_rule_name          VARCHAR2 (2000);
      l_batch_prefix       VARCHAR2 (2000);
      l_batch_info_rec     wsh_picking_batches_pub.batch_info_rec;
      l_request_id         NUMBER;
      --
      lv_org_id            NUMBER;
      lv_order_number      NUMBER;
      --
      lv_process           VARCHAR2 (100);
      lv_detail_exists     VARCHAR2 (1)                           := 'N';
      lv_order_header_id   NUMBER;
      l_release_batch      VARCHAR2 (1)                           := 'N';
   --
   BEGIN
      --
      dbms_output.put_line ('pick release started ' || p_packing_list_number);

      --
      --
      BEGIN
         SELECT order_header_id
         INTO   lv_order_header_id
         FROM
          (SELECT order_header_id from ems_packing_list
         WHERE  packing_list_number = p_packing_list_number
         AND    org_id IN (406,82)
       ;
         ---= source_org_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_order_header_id := 0;
      END;

      --
      dbms_output.put_line ('Source Header ID ' || lv_order_header_id);
      dbms_output.put_line ('Insert data into fil roll assgn attbt');

      DELETE FROM xxuflex_fil_roll_assgn_attb;

      INSERT INTO xxuflex_fil_roll_assgn_attb
                  (roll_number,
                   header_id,
                   org_id,
                   line_id,
                   item_code,
                   actual_length,
                   opt_dens,
                   trtmnt_in,
                   trtmnt_out,
                   core_id,
                   roll_width,
                   gross_roll_wt,
                   net_roll_wt,
                   roll_grade,
                   batch_id,
                   batch_no,
                   lot_no,
                   sublot_no,
                   grade_reason,
                   number_of_joints,
                   formula_class,
                   LENGTH,
                   out_dia,
                   order_number,
                   item_id,
                   line_shipment_number
                  )
          SELECT distinct roll_number,
                oohus.header_id,
                oohus.org_id,
                oolus.line_id,
                item_code,
                actual_length,
                opt_dens,
                trtmnt_in,
                trtmnt_out,
                core_id,
                roll_width,
                gross_roll_wt,
                net_roll_wt,
                roll_grade,
                NULL batch_id,
                NULL batch_no,
                lot_no,
                sublot_no,
                grade_reason,
                number_of_joints,
                formula_class,
                LENGTH,
                out_dia,
                oohus.order_number,
                oolus.inventory_item_id,
                (oolus.line_number || '.' || oolus.shipment_number
                )
         FROM   fil_roll_assgn_attb fraa,
                oe_order_headers_all oohn,
                oe_order_lines_all ooln,
                oe_order_headers_all oohus,
                oe_order_lines_all oolus,
                XXULX_TRADE_ORDER_LINES xtl
         WHERE  1 = 1
         AND    NVL (fraa.diverted_header_id, fraa.header_id) = oohn.header_id
         and    NVL (fraa.diverted_header_id, fraa.header_id) = xtl.MGF_OE_HEADER_ID
         AND    NVL (fraa.diverted_line_id, fraa.line_id) = ooln.line_id
         AND    oohn.header_id = ooln.header_id
         AND    xtl.mgf_oe_header_id=oohn.header_id
         and    xtl.mgf_oe_order_number=oohn.order_number
         and    xtl.oe_header_id=oohus.HEADER_ID
         and    xtl.org_id=oohus.org_id
         and    xtl.mgf_org_id=oohn.org_id
         AND   oohus.HEADER_ID=oolus.header_id
         AND    fraa.org_id IN (406,82)---= source_org_id
         AND    NVL (fraa.diverted_header_id, fraa.header_id) =lv_order_header_id
         and fraa.roll_number in  (select LOT_SUBLOT_NUMBER from ems_assign_lot where header_id=
          (select HEADER_ID from ems_packing_list where packing_list_number=p_us_packing_list_number))
         union
        SELECT roll_number,
             oohus.header_id,
             oohus.org_id,
             oolus.line_id,
             item_code,
             actual_length,
             opt_dens,
             trtmnt_in,
             trtmnt_out,
             core_id,
             roll_width,
             gross_roll_wt,
             net_roll_wt,
             roll_grade,
             NULL batch_id,
             NULL batch_no,
             lot_no,
             sublot_no,
             grade_reason,
             number_of_joints,
             formula_class,
             LENGTH,
             out_dia,
             oohus.order_number,
             fraa.item_id,
             (oolus.line_number || '.' || oolus.shipment_number)
        FROM fil_roll_assgn_attb  fraa,
             oe_order_headers_all oohm,
             oe_order_lines_all   oolm,
             oe_order_headers_all oohus,
             oe_order_lines_all   oolus,
             XXULX_TRADE_ORDER_LINES xtl
        WHERE  1 = 1
         AND    NVL (fraa.diverted_header_id, fraa.header_id) = oohm.header_id
         and    NVL (fraa.diverted_header_id, fraa.header_id) = xtl.MGF_OE_HEADER_ID
         AND    NVL (fraa.diverted_line_id, fraa.line_id) = oolm.line_id
         AND    oohm.header_id = oolm.header_id
         AND    xtl.mgf_oe_header_id=oohm.header_id
         and    xtl.mgf_oe_order_number=oohm.order_number
         and    xtl.oe_header_id=oohus.HEADER_ID
         and    xtl.org_id=oohus.org_id
         and    xtl.mgf_org_id=oohm.org_id
         AND   oohus.HEADER_ID=oolus.header_id
         AND    fraa.org_id IN (406,241)---= source_org_id
         AND    NVL (fraa.diverted_header_id, fraa.header_id) =lv_order_header_id
         and roll_number in (select LOT_SUBLOT_NUMBER from ems_assign_lot where header_id=
          (select HEADER_ID from ems_packing_list where packing_list_number=p_us_packing_list_number));

      --
      dbms_output.put_line ('Total rolls inserted ' || SQL%ROWCOUNT);
      dbms_output.put_line ('Start pick release');

      FOR l_rec IN cur_rec
      LOOP
         FOR l_get_delivery_det IN get_delivery_det (l_rec.header_id,
                                                     l_rec.line_id
                                                    )
         LOOP
            l_batch_info_rec.backorders_only_flag := 'I';
            l_batch_info_rec.delivery_detail_id := -1;
            l_batch_prefix := NULL;
            --
            lv_process := 'Pick Release';
            dbms_output.put_line (lv_process||'start');
            wsh_picking_batches_pub.create_batch
                                         (p_api_version        => 1.0,
                                          p_init_msg_list      => fnd_api.g_true,
                                          p_commit             => fnd_api.g_false,
                                          x_return_status      => l_return_status,
                                          x_msg_count          => l_msg_count,
                                          x_msg_data           => l_msg_data,
                                          p_rule_id            => l_rule_id,
                                          p_rule_name          => l_rule_name,
                                          p_batch_rec          => l_batch_info_rec,
                                          p_batch_prefix       => l_batch_prefix,
                                          x_batch_id           => p_new_batch_id
                                         );
            lv_detail_exists := 'Y';

            IF l_get_delivery_det.batch_id IS NULL
            THEN
               UPDATE wsh_delivery_details
               SET batch_id = p_new_batch_id
               WHERE  delivery_detail_id =
                                         l_get_delivery_det.delivery_detail_id;

               INSERT INTO xxfil_batch_gt
               VALUES      (p_new_batch_id
                           );

               l_release_batch := 'Y';
            ELSE
               INSERT INTO xxfil_batch_gt
               VALUES      (l_get_delivery_det.batch_id
                           );
            END IF;

            IF lv_detail_exists = 'Y' AND l_release_batch = 'Y'
            THEN
               lv_process := 'Release Batch';
               dbms_output.put_line (lv_process || p_new_batch_id);
               -- Release the batch Created Above
               wsh_picking_batches_pub.release_batch
                                         (p_api_version        => 1.0,
                                          p_init_msg_list      => fnd_api.g_true,
                                          p_commit             => fnd_api.g_false,
                                          x_return_status      => l_return_status,
                                          x_msg_count          => l_msg_count,
                                          x_msg_data           => l_msg_data,
                                          p_batch_id           => p_new_batch_id,
                                          p_batch_name         => NULL,
                                          p_log_level          => 1,
                                          p_release_mode       => 'ONLINE',
                                          -- (ONLINE or CONCURRENT)
                                          x_request_id         => l_request_id
                                         );
               dbms_output.put_line ('Batch Released  Status ' || l_return_status);

               IF l_return_status != 'E'
               THEN
                  NULL;
               ELSE
                  fnd_file.put_line (fnd_file.LOG,
                                     'Message count ' || l_msg_count
                                    );
                  wsh_util_core.get_messages ('Y',
                                              l_msg_data,
                                              l_msg_data,
                                              l_msg_count
                                             );
                  ROLLBACK;
               END IF;
            ELSE
               l_return_status := 'S';
               l_msg_data := NULL;
            END IF;

            IF l_return_status <> 'E'
            THEN
               lv_process := 'Update Lots';
               l_return_status := NULL;
               l_msg_data := NULL;
               dbms_output.put_line (lv_process);

               BEGIN
                  SELECT oeh.org_id,
                         oeh.order_number
                  INTO   lv_org_id,
                         lv_order_number
                  FROM   ems_packing_list ems, oe_order_headers_all oeh
                  WHERE  packing_list_number =p_us_packing_list_number
                  AND    ems.order_number = oeh.order_number;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     lv_org_id := 0;
                     lv_order_number := 0;
               END;

               dbms_output.put_line ('Generate Lots ' || lv_order_number);
              /* control_lot_num (lv_org_id,
                         lv_order_number,
                         p_us_packing_list_number,
                         l_msg_data,
                         l_return_status
                        );*/
            END IF;

            dbms_output.put_line ('After Generate Lots ' || l_return_status || l_msg_data);

            DELETE FROM mtl_reservations
            WHERE       inventory_item_id =
                                          l_get_delivery_det.inventory_item_id
            AND         lot_number IS NULL;
         END LOOP;
      END LOOP;

      x_return_status := l_return_status;
      x_return_message := l_msg_data;
   EXCEPTION
      WHEN OTHERS
      THEN
         x_return_message := lv_process || SQLERRM;
         x_return_status := 'E';
   END ;
/




5.CONTROL_LOT_NUM


CREATE OR REPLACE PROCEDURE APPS.control_lot_num (
      p_org_id                         NUMBER,   ---241
      p_so_number                      NUMBER,   ---311111180605
      p_packing_list_number   IN       VARCHAR2,
      p_move_order_id         in       number,   ---311111180605-002
      x_return_message        OUT      VARCHAR2,
      x_return_status         OUT      VARCHAR2
   )
   AS
      --
      --
      l_error_flag                   VARCHAR2 (5)    := 'N';
      l_return_status                VARCHAR2 (500);
      l_count                        NUMBER          := 0;
      l_proc_msg                     VARCHAR2 (4000);
      l_serial_transaction_temp_id   NUMBER;
      l_insert_alloc                 NUMBER;
      l_user_id                      NUMBER          := fnd_global.user_id;
      ctr                            NUMBER          := 0;
      x_trx_qty                      NUMBER          := 0;
      x_trx_id                       NUMBER          := 0;
      x_trx_id_p                     NUMBER          := 0;

      --
      CURSOR cur_alloc
      IS
        SELECT   inventory_item_id,
                  organization_id,
                  demand_source_line_id,
                  primary_uom_code,
                  trx_qty,
                  lot_number,
                  transaction_temp_id,
                  move_order_line_id,
                  locator_id
         FROM     (SELECT distinct oola.inventory_item_id,
                          oola.ship_from_org_id organization_id,
                          oola.line_id demand_source_line_id,
                          oola.order_quantity_uom primary_uom_code,
                          fraa.net_roll_wt trx_qty,
                          fraa.roll_number lot_number,
                          mmtt.transaction_temp_id,
                          move_order_line_id,
                          moq.locator_id
                   FROM   xxuflex_fil_roll_assgn_attb fraa,
                          oe_order_lines_all oola,
                          oe_order_headers_all ooha,
                          mtl_material_transactions_temp mmtt,
                          mtl_onhand_quantities moq,
                          fil_so_attb fsa
                   --WHERE SUBSTR(FRAA.line_shipment_number,1,INSTR(FRAA.line_shipment_number,'.')-1) = OOLA.line_number
                   --WHERE FRAA.line_id = NVL (OOLA.split_from_line_id, OOLA.line_id)
                   WHERE  fsa.line_number = oola.line_number
                   ------AND NVL (FRAA.diverted_header_id,FRAA.header_id) = OOLA.header_id --FRAA.header_id = OOLA.header_id
                   AND    fraa.header_id = oola.header_id
                   AND    fraa.diverted_header_id IS NULL
                   AND    fraa.roll_number = moq.lot_number
                   -----AND FSA.header_id = NVL (FRAA.diverted_header_id, FRAA.header_id)
                   AND    fsa.header_id = ooha.header_id
                   AND    fsa.roll_width = fraa.roll_width
                   AND    fraa.item_id = oola.inventory_item_id
                   AND    fraa.item_id = moq.inventory_item_id
                   and    mmtt.move_order_line_id=p_move_order_id--(select move_order_line_id from wsh_delivery_details where SOURCE_HEADER_NUMBER= p_so_number)
                   and    fraa.line_id=mmtt.TRX_SOURCE_LINE_ID);
                 
      /*            
                 
                   AND    ooha.order_number = 311111180609
                 
                   AND   NVL (mmtt.transaction_quantity, 0) != 0
--                   AND    moq.organization_id = oola.ship_from_org_id
--                   AND    moq.locator_id = mmtt.locator_id
                 
                   AND    oola.line_id = mmtt.demand_source_line
                   AND    SUBSTR (fraa.line_shipment_number,
                                  1,
                                  INSTR (fraa.line_shipment_number, '.') - 1
                                 ) = oola.line_number
                   AND    ooha.org_id = 241
                   -----------AND NVL (FRAA.diverted_order_number, OOHA.order_number)= p_so_number
                 
                   AND    fraa.roll_number NOT IN (
                             SELECT lot_number
                             FROM   wsh_delivery_details
                             WHERE  org_id = 241
                             AND    lot_number IS NOT NULL
                             AND    source_header_id IN (
                                              SELECT header_id
                                              FROM   oe_order_headers_all
                                              WHERE  order_number =
                                                                   311111180609))
                   AND    EXISTS (
                             SELECT 'Y'
                             FROM   ems_packing_list emsh,
                                    ems_pallet_number emsl,
                                    ems_assign_lot eal
                             WHERE  emsh.header_id = emsl.header_id
                             AND    emsl.line_id = eal.line_id
                             AND    packing_list_number =
                                                        '311111180609-001' --p_packing_list_number
                             AND    emsh.order_header_id = fraa.header_id
                             AND    lot_sublot_number = fraa.roll_number)
                   UNION
                   -----It is for diverted roll----------------------------------------------
                   SELECT oola.inventory_item_id,
                          oola.ship_from_org_id organization_id,
                          oola.line_id demand_source_line_id,
                          oola.order_quantity_uom primary_uom_code,
                          fraa.net_roll_wt trx_qty,
                          fraa.roll_number lot_number,
                          mmtt.transaction_temp_id,
                          move_order_line_id,
                          moq.locator_id
                   FROM   oe_order_lines_all oola,
                          oe_order_headers_all ooha,
                          fil_so_attb fsa,
                          mtl_material_transactions_temp mmtt,
                          xxuflex_fil_roll_assgn_attb fraa,
                          mtl_onhand_quantities moq
                   WHERE  1 = 1
                   AND    oola.header_id = ooha.header_id
                   AND    fsa.header_id = ooha.header_id
                   AND    fsa.line_id = oola.line_id
                   AND    oola.line_id = mmtt.trx_source_line_id
                   --------AND FRAA.diverted_order_number = ooha.order_number
                   AND    fraa.diverted_header_id = ooha.header_id
                   AND    fraa.diverted_line_id = oola.line_id
                   AND    fraa.diverted_header_id IS NOT NULL
-----------------------------------------------
                   AND    fraa.roll_number = moq.lot_number
                   AND    fraa.item_id = moq.inventory_item_id
                   AND             NVL (mmtt.transaction_quantity, 0) != 0
--                   AND    moq.organization_id = oola.ship_from_org_id
--                   AND    moq.locator_id = mmtt.locator_id
-----------------------------------------------
                   AND    ooha.order_number = 311111180609
                   AND    ooha.org_id = 241
                   AND    fraa.roll_number NOT IN (
                             SELECT lot_number
                             FROM   wsh_delivery_details
                             WHERE  org_id = 241
                             AND    lot_number IS NOT NULL
                             AND    source_header_id IN (
                                              SELECT header_id
                                              FROM   oe_order_headers_all
                                              WHERE  order_number =
                                                                  311111180609))
                   AND    EXISTS (
                             SELECT 'Y'
                             FROM   ems_packing_list emsh,
                                    ems_pallet_number emsl,
                                    ems_assign_lot eal
                             WHERE  emsh.header_id = emsl.header_id
                             AND    emsl.line_id = eal.line_id
                             AND    packing_list_number =
                                                       '311111180609-001'
                             AND    emsh.order_header_id =
                                                       fraa.diverted_header_id
                             AND    lot_sublot_number = fraa.roll_number))
         ORDER BY organization_id, transaction_temp_id, inventory_item_id;

      --
     
      */
      CURSOR cur_dealloc
      IS
         SELECT DISTINCT oola.inventory_item_id,
                         oola.ship_from_org_id organization_id,
                         oola.line_id,
                         mmtt.transaction_temp_id
         FROM            xxuflex_fil_roll_assgn_attb fraa,
                         oe_order_lines_all oola,
                         oe_order_headers_all ooha,
                         mtl_material_transactions_temp mmtt
         --WHERE FRAA.line_id = OOLA.line_id
         WHERE           NVL (fraa.diverted_header_id, fraa.header_id) =
                                                                oola.header_id
         --FRAA.header_id = OOLA.header_id
         AND             fraa.item_id = oola.inventory_item_id
         AND             oola.header_id = ooha.header_id
         AND             oola.line_id = mmtt.demand_source_line
         AND             ooha.org_id = p_org_id
         AND   NVL (FRAA.diverted_order_number, OOHA.order_number)=p_so_number
         AND             ooha.order_number = p_so_number
         and    mmtt.move_order_line_id=p_move_order_id--(select move_order_line_id from wsh_delivery_details where SOURCE_HEADER_NUMBER= p_so_number)
          AND             NVL (mmtt.transaction_quantity, 0) != 0
          ;

      CURSOR cur_dealloc_mmtl
      IS
         SELECT DISTINCT mmtt.transaction_temp_id
         FROM            xxuflex_fil_roll_assgn_attb fraa,
                         oe_order_lines_all oola,
                         oe_order_headers_all ooha,
                         mtl_material_transactions_temp mmtt
         --WHERE FRAA.line_id = OOLA.line_id
         WHERE           NVL (fraa.diverted_header_id, fraa.header_id) =
                                                                oola.header_id
         --FRAA.header_id = OOLA.header_id
         AND             fraa.item_id = oola.inventory_item_id
         AND             oola.header_id = ooha.header_id
         AND             oola.line_id = mmtt.demand_source_line
         AND             NVL (transaction_quantity, 0) = 0
         --AND             ooha.org_id = p_org_id
         ---AND   NVL (FRAA.diverted_order_number, OOHA.order_number)= p_so_number
         AND             ooha.order_number = p_so_number;
   --
   BEGIN
      --
      fnd_file.put_line
                   (fnd_file.LOG,
                    '======================================================='
                   );
      fnd_file.put_line (fnd_file.LOG, 'Calling API for De Allocation');

      --
     
      FOR rec_dealloc IN cur_dealloc
      LOOP
     
      dbms_output.put_line('mtl_material_transactions_temp');
         UPDATE mtl_material_transactions_temp mmtt
         SET transaction_quantity = 0,
             primary_quantity = 0
         WHERE  transaction_temp_id = rec_dealloc.transaction_temp_id;
       
         --
         /*
       
         BEGIN
       
         dbms_output.put_line('delete_lot_ser_trx');
            --
            inv_trx_util_pub.delete_lot_ser_trx
                            (p_trx_tmp_id         => rec_dealloc.transaction_temp_id,
                             p_org_id             => rec_dealloc.organization_id,
                             p_item_id            => rec_dealloc.inventory_item_id,
                             p_lotctrl            => 2,
                             p_serctrl            => 1,
                             x_return_status      => l_return_status
                            );
            --
            fnd_file.put_line
                            (fnd_file.LOG,
                                'De Allocation done for Sales Order Line ID: '
                             || rec_dealloc.line_id
                            );
         --
       
       
         EXCEPTION
            --
            WHEN OTHERS
            THEN
            dbms_output.put_line('exception in delete');
               l_error_flag := 'Y';
               fnd_file.put_line
                       (fnd_file.LOG,
                           'Error in De Allocation for Sales Order Line ID: '
                        || rec_dealloc.line_id
                        || ' : '
                        || SQLERRM
                       );
               x_return_message :=
                     'Error in De Allocation for Sales Order Line ID: '
                  || rec_dealloc.line_id
                  || ' : '
                  || SQLERRM;
               EXIT;
         END;
      --
        */
     
      END LOOP;

      --
     
   
      fnd_file.put_line
                    (fnd_file.LOG,
                     '======================================================='
                    );
      fnd_file.put_line (fnd_file.LOG, 'Calling API for Manual Allocation');
      --
      x_trx_id_p := 0;
      ctr := 1;
        dbms_output.put_line('ctr='||ctr);
        dbms_output.put_line('error flag'||l_error_flag);
      IF l_error_flag = 'N'
      THEN
         FOR rec_alloc IN cur_alloc
         LOOP
            IF ctr = 1
            THEN
           
            dbms_output.put_line('ctr = 1');
               x_trx_id := rec_alloc.transaction_temp_id;
               ---X_TRX_ID := REC_ALLOC.transaction_temp_id ;
               ctr := ctr + 1;
               x_trx_qty := 0;
            ELSE
               IF x_trx_id <> rec_alloc.transaction_temp_id
               THEN
               dbms_output.put_line('x_trx_id <> rec_alloc.transaction_temp_id');
                  x_trx_id := rec_alloc.transaction_temp_id;
                  x_trx_qty := 0;
               END IF;
            END IF;
             
----------------------------------------------
--
            BEGIN
               --
             
               dbms_output.put_line('insert_lot_trx');
               l_insert_alloc :=
                  inv_trx_util_pub.insert_lot_trx
                              (p_trx_tmp_id      => rec_alloc.transaction_temp_id,
                               p_user_id         => l_user_id,
                               p_lot_number      => rec_alloc.lot_number,
                               p_trx_qty         => rec_alloc.trx_qty,
                               p_pri_qty         => rec_alloc.trx_qty,
                               x_ser_trx_id      => l_serial_transaction_temp_id,
                               x_proc_msg        => l_proc_msg
                              );
               --
               fnd_file.put_line (fnd_file.LOG,
                                     'Manual Allocation done for Lot Number: '
                                  || rec_alloc.lot_number
                                 );
               --
               dbms_output.put_line('Manual Allocation done for Lot Number: '|| rec_alloc.lot_number);
               l_count := l_count + 1;
               --
               x_trx_qty := x_trx_qty + rec_alloc.trx_qty;
            ----- DBMS_OUTPUT.PUT_LINE('UPDATED .....  '||X_TRX_ID ||'   '|| X_TRX_QTY);
            ---X_TRX_ID := REC_ALLOC.transaction_temp_id ;
            --
            EXCEPTION
               WHEN OTHERS
               THEN
             
               dbms_output.put_line('l_error_flag=Y');
                  l_error_flag := 'Y';
                  fnd_file.put_line (fnd_file.LOG,
                                     'ERROR OTHERS .....  ' || x_trx_id
                                    );
                  fnd_file.put_line
                             (fnd_file.LOG,
                                 'Error in Manual Allocation for Lot Number: '
                              || rec_alloc.lot_number
                              || ' : '
                              || SQLERRM
                             );
                  x_return_message :=
                        'Error in Manual Allocation for Lot Number: '
                     || rec_alloc.lot_number
                     || ' : '
                     || SQLERRM;
                  EXIT;
            END;

            IF l_error_flag = 'N'
            THEN
           
            dbms_output.put_line('UPDATE mtl_material_transactions_temp');
               -----   ADDEDED BY SANDEEP ON 31-OCT-2012 --------------
               UPDATE mtl_material_transactions_temp mmtt
               SET transaction_quantity = x_trx_qty,
                   primary_quantity = x_trx_qty,
                   transfer_to_location = locator_id
               WHERE  transaction_temp_id = x_trx_id;
            -- FND_FILE.put_line(FND_FILE.LOG,'UPDATED .....  '||X_TRX_ID||'  FINAL    '||X_TRX_QTY || ' l_error_flag: '||l_error_flag) ;
            ----COMMIT;
            ELSE
               fnd_file.put_line (fnd_file.LOG,
                                  'NOT UPDATED .....  ' || x_trx_id
                                 );
            ----ROLLBACK;
            END IF;
         --
         END LOOP;
      END IF;

      FOR rec_dealloc_mmtl IN cur_dealloc_mmtl
      LOOP
      dbms_output.put_line('DELETE FROM mtl_material_transactions_temp');
         DELETE FROM mtl_material_transactions_temp
         WHERE       transaction_temp_id =
                                         rec_dealloc_mmtl.transaction_temp_id;
      END LOOP;

      -- /*
      IF l_error_flag = 'N'
      THEN
         x_return_status := 'S';
      ELSE
         x_return_status := 'E';
         ROLLBACK;
      END IF;
       dbms_output.put_line(x_return_status);
     
   ---  */
   EXCEPTION
      WHEN OTHERS
      THEN
      dbms_output.put_line('error');
         fnd_file.put_line (fnd_file.LOG, SQLERRM);
   END ;
/


now you will check your lot number against you order number in mtl_transaction_lots_temp table 
by following query 
you fing move_order_line_id in wsh_dilivery_detail table against order_number

SELECT transaction_temp_id FROM mtl_material_transactions_temp
    WHERE move_order_line_id = 1850473;
    
    select *
from mtl_transaction_lots_temp where transaction_temp_id =53372002;;
    
   
    

  

Comments

Popular posts from this blog

API for create Purchase order using packing_list in oracle apps r12

API for create receipt number after Create PO in oracle apps