Receiving & Return with Receiving Open Interface (ROI)

Records needs to be inserted into rcv_transactions_interface with processing_status_code and transaction_status_code as ‘PENDING’ and transaction_type of ‘RECEIVE’. and also inserted into rcv_shipment_headers which creates the shipment header.

Oracle using Receiving Transaction Processor processes receiving transactions with PENDING or UNPROCESSED status. We could receive Purchase Order either using Expected Receipt form, or by insert record into Receiving Open Interface (ROI). Then we submitted concurrent Receiving Transactions Processor, so PO would be received.

We also could return transactions. This could be done with insert data to ROI, and also submitted Receiving Transactions Processor.

When the records needs to be inserted into ROI interface, they would be inserted on table RCV_TRANSACTIONS_INTERFACE.  And the record were :

  • value “PENDING” on column PROCESSING_STATUS_CODE and TRANSACTION_STATUS_CODE.
  • value “RECEIVE” on column TRANSACTION_TYPE.

And the record also inserted into table RCV_SHIPMENT_HEADERS, which creates the shipment header.

/*Interface Tables*/
RCV_HEADERS_INTERFACE

RCV_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE

/*Error Tables for checked
* There were Standard reports for interface status checking :
* Receiving Interface Errors Report
* Receiving Exception Report
*/
PO_INTERFACE_ERRORS

/*Base Tables*/
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
RCV_TRANSACTIONS
MTL_LOT_NUMBERS
MTL_MATERIAL_TRANSACTIONS
RCV_LOT_TRANSACTIONS

There was a plsql query for Receiving Open Interface using, based on PO Document.

DECLARE
   x_user_id             NUMBER;
   x_resp_id             NUMBER;
   x_appl_id             NUMBER;
   x_po_header_id        NUMBER;
   x_vendor_id           NUMBER;
   x_segment1            VARCHAR2 (20);
   x_org_id              NUMBER;
   x_line_num            NUMBER;
   l_chr_lot_number      VARCHAR2 (50);
   l_chr_return_status   VARCHAR2 (2000);
   l_num_msg_count       NUMBER;
   l_chr_msg_data        VARCHAR2 (50);
   v_count               NUMBER;
BEGIN
   DBMS_OUTPUT.put_line ('RCV Sample Insert Script Starts');
   DBMS_OUTPUT.put_line ('**************************************');

   SELECT po_header_id, vendor_id, segment1, org_id
     INTO x_po_header_id, x_vendor_id, x_segment1, x_org_id
     FROM po_headers_all
    WHERE segment1 = '380087'-- Enter The Po Number which needs to be received
      AND org_id = 308                                     -- Enter the org_id
      AND approved_flag = 'Y'
      AND NVL (cancel_flag, 'N') = 'N';

   SELECT DISTINCT u.user_id, TO_CHAR (a.responsibility_id) responsibility_id,
                   b.application_id
              INTO x_user_id, x_resp_id,
                   x_appl_id
              FROM apps.fnd_user_resp_groups_direct a,
                   apps.fnd_responsibility_vl b,
                   apps.fnd_user u,
                   apps.fnd_application fa
             WHERE a.user_id = u.user_id
               AND a.responsibility_id = b.responsibility_id
               AND a.responsibility_application_id = b.application_id
               AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date,
                                                         SYSDATE + 1
                                                        )
               AND fa.application_id(+) = b.application_id
               AND UPPER (u.user_name) = 'A42485'       -- Enter the User_name
               AND b.responsibility_name = 'Inventory';
                                              -- Enter The Responsibility Name

   DBMS_OUTPUT.put_line ('Inserting the Record into Rcv_headers_interface');
   DBMS_OUTPUT.put_line ('*********************************************');

   INSERT INTO rcv_headers_interface
               (header_interface_id, GROUP_ID, processing_status_code,
                receipt_source_code, transaction_type, last_update_date,
                last_updated_by, last_update_login, creation_date, created_by,
                vendor_id, expected_receipt_date, validation_flag)
      SELECT rcv_headers_interface_s.NEXTVAL, rcv_interface_groups_s.NEXTVAL,
             'PENDING', 'VENDOR', 'NEW', SYSDATE, x_user_id, 0, SYSDATE,
             x_user_id, x_vendor_id, SYSDATE, 'Y'
        FROM DUAL;

   DECLARE
      CURSOR po_line
      IS
         SELECT pl.org_id, pl.po_header_id, pl.item_id, pl.po_line_id,
                pl.line_num, pll.quantity, pl.unit_meas_lookup_code,
                mp.organization_code, pll.line_location_id, pll.closed_code,
                pll.quantity_received, pll.cancel_flag, pll.shipment_num,
                pda.destination_type_code, pda.deliver_to_person_id,
                pda.deliver_to_location_id, pda.destination_subinventory,
                pda.destination_organization_id
           FROM po_lines_all pl,
                po_line_locations_all pll,
                mtl_parameters mp,
                apps.po_distributions_all pda
          WHERE pl.po_header_id = x_po_header_id
            AND pl.po_line_id = pll.po_line_id
            AND pll.line_location_id = pda.line_location_id
            AND pll.ship_to_organization_id = mp.organization_id;
   BEGIN
      FOR rec_det IN po_line
      LOOP
         IF     rec_det.closed_code IN ('APPROVED', 'OPEN')
            AND rec_det.quantity_received <> 0
         THEN
            DBMS_OUTPUT.put_line
                      ('Inserting the Record into Rcv_Transactions_Interface');
            DBMS_OUTPUT.put_line
                             ('*********************************************');

            INSERT INTO rcv_transactions_interface
                        (interface_transaction_id, GROUP_ID,
                         last_update_date, last_updated_by, creation_date,
                         created_by, last_update_login, transaction_type,
                         transaction_date, processing_status_code,
                         processing_mode_code, transaction_status_code,
                         po_header_id, po_line_id, item_id, quantity,
                         unit_of_measure, po_line_location_id,
                         auto_transact_code, receipt_source_code,
                         to_organization_code, source_document_code,
                         document_num, destination_type_code,
                         deliver_to_person_id, deliver_to_location_id,
                         subinventory, header_interface_id, validation_flag)
               SELECT rcv_transactions_interface_s.NEXTVAL,
                      rcv_interface_groups_s.CURRVAL, SYSDATE, x_user_id,
                      SYSDATE, x_user_id, 0, 'RECEIVE', SYSDATE, 'PENDING',
                      'BATCH', 'PENDING', rec_det.po_header_id,
                      rec_det.po_line_id, rec_det.item_id, rec_det.quantity,
                      rec_det.unit_meas_lookup_code,
                      rec_det.line_location_id, 'DELIVER', 'VENDOR',
                      rec_det.organization_code, 'PO', x_segment1,
                      rec_det.destination_type_code,
                      rec_det.deliver_to_person_id,
                      rec_det.deliver_to_location_id,
                      rec_det.destination_subinventory,
                      rcv_headers_interface_s.CURRVAL, 'Y'
                 FROM DUAL;

            DBMS_OUTPUT.put_line (   'PO line:'
                                  || rec_det.line_num
                                  || ' Shipment: '
                                  || rec_det.shipment_num
                                  || ' has been inserted into ROI.'
                                 );

            SELECT COUNT (*)
              INTO v_count
              FROM mtl_system_items
             WHERE inventory_item_id = rec_det.item_id
               AND lot_control_code = 2    -- 2 - full_control, 1 - no control
               AND organization_id = rec_det.destination_organization_id;

            IF v_count > 0
            THEN
               DBMS_OUTPUT.put_line ('The Ordered Item is Lot Controlled');
               DBMS_OUTPUT.put_line
                       ('Generate the Lot Number for the Lot Controlled Item');

               BEGIN
                  -- initialization required for R12
                  mo_global.set_policy_context ('S', rec_det.org_id);
                  mo_global.init ('INV');
                  -- Initialization for Organization_id
                  inv_globals.set_org_id (rec_det.destination_organization_id);
                  -- initialize environment
                  fnd_global.apps_initialize (user_id           => x_user_id,
                                              resp_id           => x_resp_id,
                                              resp_appl_id      => x_appl_id
                                             );
                  DBMS_OUTPUT.put_line('Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers');
                  DBMS_OUTPUT.put_line('*********************************************');
                  l_chr_lot_number :=
                     inv_lot_api_pub.auto_gen_lot
                             (p_org_id                 => rec_det.destination_organization_id,
                              p_inventory_item_id      => rec_det.item_id,
                              p_parent_lot_number      => NULL,
                              p_subinventory_code      => NULL,
                              p_locator_id             => NULL,
                              p_api_version            => 1.0,
                              p_init_msg_list          => 'F',
                              p_commit                 => 'T',
                              p_validation_level       => 100,
                              x_return_status          => l_chr_return_status,
                              x_msg_count              => l_num_msg_count,
                              x_msg_data               => l_chr_msg_data
                             );

                  IF l_chr_return_status = 'S'
                  THEN
                     COMMIT;
                  ELSE
                     ROLLBACK;
                  END IF;

                  DBMS_OUTPUT.put_line(   'Lot Number Created for the item is => '|| l_chr_lot_number);
               END;

               DBMS_OUTPUT.put_line('Inserting the Record into mtl_transaction_lots_interface ');
               DBMS_OUTPUT.put_line('*********************************************');

               INSERT INTO mtl_transaction_lots_interface
                           (transaction_interface_id, last_update_date,
                            last_updated_by, creation_date, created_by,
                            last_update_login, lot_number,
                            transaction_quantity, primary_quantity,
                            serial_transaction_temp_id, product_code,
                            product_transaction_id)
                  (SELECT mtl_material_transactions_s.NEXTVAL,
                                                    --transaction_interface_id
                                                              SYSDATE,
                                                            --last_update_date
                          x_user_id,                         --last_updated_by
                                    SYSDATE,                   --creation_date
                                            x_user_id,            --created_by
                                                      -1,  --last_update_login
                                                         l_chr_lot_number,
                                                                  --lot_number
                          rec_det.quantity,             --transaction_quantity
                                           rec_det.quantity,
                                                            --primary_quantity
                                                            NULL,
                                                  --serial_transaction_temp_id
                                                                 'RCV',
                                                                --product_code
                          rcv_transactions_interface_s.CURRVAL
                                                      --product_transaction_id
                     FROM DUAL);
            ELSE
               DBMS_OUTPUT.put_line ('The Ordered Item is Not Lot Controlled');
               DBMS_OUTPUT.put_line ('********************************************');
            END IF;
         ELSE
            DBMS_OUTPUT.put_line ('PO line '|| rec_det.line_num|| '-'|| rec_det.shipment_num|| ' is either closed, cancelled, received.');
            DBMS_OUTPUT.put_line ('*********************************************');
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('RCV Sample Insert Script Ends');
      DBMS_OUTPUT.put_line ('*****************************************');
   END;

   COMMIT;
END;

Source : Jyoti Blog

Edi Yanto Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s