, , , , , ,

Code for Item Replenishment Interface

CREATE OR REPLACE PACKAGE A07_REPLENISHMENT_INT AUTHID CURRENT_USER AS
/******************************************************************************
   NAME:       A07_REPLENISH_INTERFACE
   PURPOSE:    Item Replenishment Interface.

   PARAMETERS:
   INPUT:
   OUTPUT:
   RETURNED VALUE:
   CALLED BY:
   CALLS:
   EXAMPLE USE:     NUMBER := A07_REPLENISH_INTERFACE.MyFuncName(Number);
                    A07_REPLENISH_INTERFACE.MyProcName(Number, Varchar2);
   ASSUMPTIONS:+
   LIMITATIONS:
   ALGORITHM:
   NOTES:
******************************************************************************/
— All the Global Variables to be used in this package
      g_create_dt       date   := sysdate;
      g_create_by       number := 10005218;
      g_update_dt       date   := sysdate;
      g_update_by       number := 10005218;
      v_sqlcode         number;
      v_sqlerrm         varchar2(255);
— All Global Variables specific to the package
— All the Functions used in this package
   Function CHK_ORG_EXISTS (org_id mtl_parameters.organization_id%type) return boolean;
   Function CHK_ITEM_EXISTS (item_id mtl_system_items_b.inventory_item_id%type,
                                     v_org_id mtl_system_items_b.organization_id%type) return boolean;
   Function CHK_ITEM_SUBINV (v_item_id number, v_subinv varchar2, v_org_id number) return boolean;
   Function GET_ITEM_PLAN (v_org_id number, v_item_id number, v_subinv varchar2) return number;
   Function GET_ITEM_UOM (v_org_id number, v_item_id number) return varchar2;
— All the Procedures used in this package
   Procedure Validate_Organization;
   Procedure Validate_Item;
   Procedure Validate_Item_SubInv;
   Procedure Validate_Plan_Method;
   Procedure Validate_Uom;
   Procedure Validate ;
   Procedure Populate_Interface;
—   Procedure Run (errcode out number, errbuf out varchar2);
END A07_REPLENISHMENT_INT;
/
CREATE OR REPLACE PACKAGE BODY A07_REPLENISHMENT_INT AS
Function CHK_ORG_EXISTS (org_id mtl_parameters.organization_id%type) return boolean
Is
   cnt number;
Begin
      Select count(*) into cnt
      From hr_all_organization_units
      where organization_id = org_id;
      if cnt <> 0
      then
         return true;
      Else
         return false;
      End if;
End;
Function CHK_ITEM_EXISTS (item_id mtl_system_items_b.inventory_item_id%type,
                                     v_org_id mtl_system_items_b.organization_id%type) return boolean
Is
    cnt     number;
Begin
      Select count(*) into cnt
      From mtl_system_items_b
      Where inventory_item_id = item_id
      And   organization_id = v_org_id;
      if cnt <> 0
      then
         return true;
      else
         return false;
      end if;
End;
Function CHK_ITEM_SUBINV (v_item_id number, v_subinv varchar2, v_org_id number) return boolean
Is
      cnt number;
Begin
      Select count(*) into cnt
      From mtl_item_sub_inventories
      Where inventory_item_id = v_item_id
      and  secondary_inventory = v_subinv
      and organization_id = v_org_id;
      if cnt <> 0
      then
         return true;
      else
         return false;
      end if;
End;
Function GET_ITEM_PLAN (v_org_id number, v_item_id number, v_subinv varchar2) return number
Is
      ipm number;
Begin
      Select inventory_planning_code into ipm
      From mtl_item_sub_inventories
      Where inventory_item_id = v_item_id
      And   organization_id = v_org_id
      And   secondary_inventory = v_subinv;
      Return ipm;
Exception
      When NO_DATA_FOUND then
             return –2;
      When OTHERS then
             return –1;
End;
Function GET_ITEM_UOM (v_org_id number, v_item_id number) return varchar2
is
      uom varchar2(3);
Begin
      Select primary_uom_code into uom
      From mtl_system_items_b
      Where organization_id = v_org_id
      And inventory_item_id = v_item_id;
      Return uom;
Exception
      When NO_DATA_FOUND then
             return NULL;
      When OTHERS then
             return NULL;
End;
Procedure Validate_Organization
As
      Cursor c_org is Select distinct organization_id from A07_replenish_interface;
      v_org  c_org%rowtype;
Begin
      Open c_org;
      Loop
             Fetch c_org into v_org;
             Exit when c_org%NOTFOUND;
             Begin
                  If chk_org_exists(v_org.organization_id)
                  then
                     null;
                  Else
                     Update A07_replenish_interface
                     Set error_flag = –1,
                          error_description = ‘Organization does not exist’
                     Where organization_id = v_org.organization_id;
                End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               where organization_id = v_org.organization_id;
             End;
      End Loop;
      Close c_org;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Organization completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Organization’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_Organization;
Procedure Validate_Item
As
      Cursor c_item is Select inventory_item_id, organization_id from A07_replenish_interface
                               Where error_flag is null;
      v_item c_item%rowtype;
Begin
    Open c_item;
      Loop
             Fetch c_item into v_item;
             Exit when c_item%NOTFOUND;
             Begin
                  If chk_item_exists(v_item.inventory_item_id, v_item.organization_id)
                then
                     null;
                  else
                     Update A07_replenish_interface
                     Set error_flag = –2,
                           error_description = ‘Item does not exist’
                     Where organization_id = v_item.organization_id
                     And   inventory_item_id = v_item.inventory_item_id;
                  End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               Where organization_id = v_item.organization_id
                               And   inventory_item_id = v_item.inventory_item_id;
             End;
      End Loop;
      Close c_item;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Item completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Item’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_Item;
Procedure Validate_Item_SubInv
As
      Cursor c_item is Select inventory_item_id, subinventory_code,organization_id
                               from A07_replenish_interface
                               Where error_flag is null;
      v_item c_item%rowtype;
Begin
    Open c_item;
      Loop
             Fetch c_item into v_item;
             Exit when c_item%NOTFOUND;
             Begin
                  If chk_item_subinv(v_item.inventory_item_id,v_item.subinventory_code,v_item.organization_id)
                then
                     null;
                  else
                     Update A07_replenish_interface
                     Set error_flag = –2,
                           error_description = ‘Item does not exist in suninventory’
                     Where organization_id = v_item.organization_id
                     And   inventory_item_id = v_item.inventory_item_id
                     And   subinventory_code = v_item.subinventory_code;
                  End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               Where organization_id = v_item.organization_id
                               And   inventory_item_id = v_item.inventory_item_id
                             And   subinventory_code = v_item.subinventory_code;
             End;
      End Loop;
      Close c_item;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Item completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Item’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_Item_SubInv;
Procedure Validate_Plan_Method
As
      Cursor c_plan is Select organization_id, inventory_item_id, subinventory_code, count_method
                               from A07_replenish_interface
                               Where error_flag is null;
      v_plan c_plan%rowtype;
      ipm number;
Begin
      Open c_plan;
      Loop
             Fetch c_plan into v_plan;
             Exit when c_plan%NOTFOUND;
             ipm := get_item_plan(v_plan.organization_id, v_plan.inventory_item_id,v_plan.subinventory_code);
             Begin
                  If ipm = 1
                then
                        if v_plan.count_method != 2
                        then
                          Update A07_replenish_interface
                          Set error_flag = –3,
                                error_description = ‘Planning method and Counting Method mismatch’
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id
                          and  subinventory_code = v_plan.subinventory_code;
                        end if;
                  Elsif ipm = 2 then
                        if v_plan.count_method != 3
                        then
                          Update A07_replenish_interface
                          Set error_flag = –3,
                                error_description = ‘Planning method and Counting Method mismatch’
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id
                          and  subinventory_code = v_plan.subinventory_code;
                        end if;
                  Elsif ipm = 6 then
                        if v_plan.count_method != 1
                        then
                          Update A07_replenish_interface
                          Set error_flag = –3,
                                error_description = ‘Planning method and Counting Method mismatch’
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id
                          and  subinventory_code = v_plan.subinventory_code;
                        end if;
                  Else
                          null;
                  End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               Where organization_id = v_plan.organization_id
                             And   inventory_item_id = v_plan.inventory_item_id
                             And   subinventory_code = v_plan.subinventory_code;
             End;
      End Loop;
      Close c_plan;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Plan Method completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Plan Method’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_Plan_Method;
Procedure Validate_UOM
As
      Cursor c_uom is Select distinct inventory_item_id, organization_id
                               from A07_replenish_interface
                               Where error_flag is null;
      v_plan  c_uom%rowtype;
      uom varchar2(3);
Begin
      Open c_uom;
      Loop
             Fetch c_uom into v_plan;
             Exit when c_uom%NOTFOUND;
             uom := get_item_uom(v_plan.organization_id, v_plan.inventory_item_id);
             Begin
                  If uom is NULL
                then
                          Update A07_replenish_interface
                          Set error_flag = –4,
                                error_description = ‘UOM not found’
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id;
                  Else
                          Update A07_replenish_interface
                          Set    UOM_CODE = uom
                          Where organization_id = v_plan.organization_id
                          And   inventory_item_id = v_plan.inventory_item_id;
                  End If;
             Exception
                        When OTHERS then
                               v_sqlcode := sqlcode;
                               v_sqlerrm := sqlerrm;
                               Rollback;
                               update A07_replenish_interface
                               Set error_flag = v_sqlcode,
                                     error_description = v_sqlerrm
                               Where organization_id = v_plan.organization_id
                             And   inventory_item_id = v_plan.inventory_item_id;
             End;
      End Loop;
      Close c_uom;
      Commit;
      fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Plan Method completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Plan Method’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate_UOM;
Procedure Validate
As
Begin
   Update A07_replenish_interface
   Set error_flag = NULL, error_description = NULL;
— Update the organization_ids to the respective organization
   Validate_organization;
— Check if the items exist for the given organization in MTL_SYSTEM_ITEMS_B
   Validate_item;
— Check if the organization costing method and the item costing method are same.
   Validate_Plan_Method;
— Check if the item is existing in the subinventory
   Validate_Item_SubInv;
— To Check if the item cost already exists in the CST_ITEM_COSTS
   Validate_UOM;
   Update A07_replenish_interface
   Set error_flag = 0, error_description = ‘Clean Data’
   Where error_flag is null;
   Commit;
   fnd_file.put_line(FND_FILE.OUTPUT,‘Validate Interface completed successfully’);
Exception
      When OTHERS then
             v_sqlcode := sqlcode;
             v_sqlerrm := sqlerrm;
             Rollback;
             fnd_file.put_line(FND_FILE.LOG,‘In the process : Validate Interface’);
             fnd_file.put_line(FND_FILE.LOG,‘Error Code is ‘ || v_sqlcode);
             fnd_file.put_line(FND_FILE.LOG,‘Error Mesg is ‘ || v_sqlerrm);
End Validate;
Procedure Populate_Interface
As
    CURSOR C1 IS    SELECT * FROM A07_REPLENISH_INTERFACE;
      Cursor c_hdr is Select distinct organization_id, subinventory_code, count_date
                              from A07_replenish_interface
                              where error_flag = 0;
      Cursor c_lines (org_id in number,subinv_code in varchar2) is
                           Select inventory_item_id, organization_id
                           from A07_replenish_interface
                              where error_flag = 0;
      h_id           number;
      l_id           number;
      h_name               varchar2(15);
    V_HDR          C_HDR%ROWTYPE;
Begin
      Open c_hdr;
      Loop <<OUTER>>
             Fetch c_hdr into v_hdr;
             Exit when c_hdr%NOTFOUND;
             Select mtl_replenish_headers_s.nextval into h_id from dual;
             h_name     := ‘SRT’ || h_id ;
    END LOOP;  
        
     
       FOR REC IN C1
             LOOP
             Insert into mtl_replenish_headers_int
             ( REPLENISHMENT_HEADER_ID,REPLENISHMENT_COUNT_NAME,COUNT_DATE, LAST_UPDATE_DATE,CREATION_DATE,
             CREATED_BY ,LAST_UPDATED_BY,ORGANIZATION_ID,SUBINVENTORY_CODE, PROCESS_STATUS,PROCESS_MODE  )
             Values
             (REC.REPLENISHMENT_HEADER_ID,REC.REPLENISHMENT_COUNT_NAME,REC.COUNT_DATE,REC.LAST_UPDATE_DATE,REC.CREATION_DATE,
             REC.CREATED_BY,REC.LAST_UPDATED_BY,REC.ORGANIZATION_ID,REC.SUBINVENTORY_CODE,REC.PROCESS_STATUS,REC.PROCESS_MODE);
             END LOOP;
END;
END A07_REPLENISHMENT_INT;
/by
, , , , , , , , ,

Importing Sales Orders From Excel

This article gives you simple steps to load large sales orders in Order Management using Excel Spreasheet.
We arrived at this solution using some good features available in Oracle Database and Oracle Applications. Here are the steps.
  1. We create order header only (using sales order form) with all the necessary information like customer, order type, price list, sales person, sales credits, warehouse and the other values as deemed necessary.
  2. Now we create line information in Excel worksheet (a template is created). We enter values for the fields like Item, quantity, price list name (if that is different from header), Line Type, Sales Person (different from header), any DFF segments, Shipping Instructions if any, warehouse name so on. We can have as many fields as that can fit in the order lines table. But instead of entering all the values, we use defaulting rules to default a lot of values from the header.
  3. In this worksheet there is a simple marco written that we use to generate another worksheet in the same file for the entered rows.This macro does some basic validations on the information entered in the worksheet and generates the worksheet with this statement for each row.
    1.  insert into custom_lines_load_tbl  (column1,column2,….) values ( field1,field2,……)
  4. Now using these rows with this statement (generated worksheet) we create a simple text file (copy and paste).
  5. As a next step we upload this file as an attachment to the order header that we have created in the step number 1.
  6. Now we run a custom program to create order lines into this order. This program takes order number as a parameter. This program reads the file contents line by line and executes the insert statements. This is the heart of the solution which is to read the contents of the file which is stored as LOB and executing the statements. After all the insert statements are executed successfully, the program calls oe_order_pub API to create order lines.
  7. As you can see the program uses this feature : reading the file which is stored in the database as LOB line by line. Here is the code snippet to read the attached file stored as a file.

PROCEDURE parse_and_load_file (
      ERROR_CODE       OUT      VARCHAR2,
      retcode          OUT      NUMBER,
      p_order_number   IN       NUMBER
   )
   IS
      fil             BLOB;
      pos             INTEGER         := 0;
      amt             BINARY_INTEGER  := 32767;
      buf             RAW (2000);
      l_start         RAW (3000);
      l_end           RAW (3000);
      l_start_pos     INTEGER;
      l_length        INTEGER;
      nth             INTEGER         := 1;
      i               INTEGER         := 2;
      l_text          VARCHAR2 (4000);
      l_end_pos       INTEGER;
      l_plsql_block   VARCHAR2 (4000);
      k               INTEGER;
      l_write_buff    RAW (2000);
      l_text1         VARCHAR2 (4000);
      x_errbuf        VARCHAR2 (4000);
      x_retcode       NUMBER;
   BEGIN
      BEGIN
    begin
    delete custom_lines_upload_tbl
    where order_number=p_order_number
    and line_Id is null; –this is to make sure we do not load duplicate data
    exception when others then
    null;
    end;

SELECT file_data
  INTO fil
  FROM fnd_lobs
 WHERE file_id =
          (SELECT media_id
             FROM fnd_documents_vl
            WHERE document_id IN (
                     SELECT a.document_id
                       FROM fnd_attached_documents a, fnd_documents b
                      WHERE a.document_id = b.document_id
                        AND b.category_id =
                                     (SELECT category_id
                                        FROM fnd_document_categories_tl
                                       WHERE user_name = ‘Custom Lines Upload’)
                        AND entity_name = ‘OE_ORDER_HEADERS’
                        AND pk1_value = (SELECT header_id
                                           FROM oe_order_headers_all
                                          WHERE order_number = p_order_number)))
         FOR UPDATE;
      EXCEPTION
         WHEN OTHERS
         THEN
            debug_log
               (   ‘Order :’
                || p_order_number
                || ‘ does not have any file attached to it to load. Please attach the file and then try running this program’
               );
      END;

      k := DBMS_LOB.ISOPEN (fil);

      — debug_log(‘Checking whether the file is already open’);
      IF k = 1
      THEN
         –debug_log(‘file is already open. Closing it’);
         DBMS_LOB.CLOSE (fil);
      END IF;

      BEGIN
         –debug_log(‘Opening the file’);
         DBMS_LOB.OPEN (fil, DBMS_LOB.lob_readonly);
         k := DBMS_LOB.ISOPEN (fil);
      EXCEPTION
         WHEN OTHERS
         THEN
            debug_log
                    (‘Exception occured in checking whether the file is open’);
      END;

      IF k = 1
      THEN
         LOOP
            l_start := UTL_RAW.cast_to_raw (‘insert’);
            l_end := UTL_RAW.cast_to_raw (‘;’);
            l_start_pos := DBMS_LOB.INSTR (fil, l_start, 1, nth);
            pos := DBMS_LOB.INSTR (fil, l_end, 1, nth);
            buf := ”;
            –debug_log(‘Start position is :’||l_start_pos);
            –debug_log(‘End position is :’||pos);
            l_end_pos := (pos + 1) – (l_start_pos);
            –debug_log(‘Length of the string is :’||l_end_pos);
            –debug_log(‘Reading the file’);
            DBMS_LOB.READ (fil, l_end_pos, l_start_pos, buf);
            l_text := UTL_RAW.cast_to_varchar2 (buf);
            l_plsql_block :=
                  ‘begin’
               || fnd_global.NEWLINE ()
               || l_text
               || fnd_global.NEWLINE ()
               || ‘commit;’
               || fnd_global.NEWLINE ()
               || ‘exception when others then’
               || fnd_global.NEWLINE ()
               ||
                  –’debug_log(”Exception occured in processing the line”);’||fnd_global.NewLine()||
                  ‘NULL;’
               || fnd_global.NEWLINE ()
               || ‘end;’;

            –debug_log(‘———————————————–’);
            –debug_log(l_plsql_block);
            –debug_log(‘———————————————–’);
            BEGIN
            –Now execute the dynamic pl/sql block to insert rows into custom table from the file
               EXECUTE IMMEDIATE l_plsql_block;
            EXCEPTION
               WHEN OTHERS
               THEN
                  –debug_log(‘Exception Occured’);
                  –debug_log(l_plsql_block);
                  –debug_log(‘Exception is :’||substrb(sqlerrm, 1, 55));
                  NULL;
            END;

            nth := nth + 1;
         END LOOP;

         DBMS_LOB.CLOSE (fil);
      END IF;

      COMMIT;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         k := NULL;
         k := DBMS_LOB.ISOPEN (fil);

         IF k = 1
         THEN
            DBMS_LOB.CLOSE (fil);
         END IF;
      WHEN OTHERS
      THEN
         BEGIN
            — debug_log(‘End of File reached in Exception. Closing file’);
            k := NULL;
            k := DBMS_LOB.ISOPEN (fil);

            IF k = 1
            THEN
               DBMS_LOB.CLOSE (fil);
               COMMIT;
            END IF;
         END;

         –Here call the oe_order_pub API to create the lines passing the line table from this custom table.
   END parse_and_load_file;

/by
, , , , , , , , ,

Receiving PO Using Receiving Open Interface (ROI)

The Receiving Transaction Processor processes pending or unprocessed receiving transactions. We can receive the Purchase Order either using the Expected Receipt form or by putting the record into the Receiving Open Interface (ROI). And then if we will submit the receiving transactions processor so the PO will be received.

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.

Interface Tables: –

  • rcv_headers_interface
  • rcv_transactions_interface
  • mtl_transaction_lots_interface

Error Table: –

  • po_interface_errors

Base Tables:

  • rcv_shipment_headers
  • rcv_shipment_lines
  • rcv_transactions
  • mtl_lot_numbers
  • mtl_material_transactions
  • rcv_lot_transactions

R12 – Sample Procedure to Receive PO by inserting records into ROI
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 <>
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;

— Cross Check the Records in the Interface Table

select * from apps.rcv_headers_interface
where created_by = 2083
and group_id = ***

select *
from apps.rcv_transactions_interface
where created_by = 2083
and group_id = ***
select * from apps.mtl_transaction_lots_interface
where created_by = 2083
and lot_number = ***
and product_transaction_id in
(select interface_transaction_id from apps.rcv_transactions_interface
where created_by = 2083 and group_id = ***)

— Check for the Error

select * from po_interface_errors
where batch_id = ***
— Reprocessing the records from the interface if the same errored out there.
UPDATE rcv_headers_interface
SET processing_request_id = NULL,
validation_flag = ‘Y’,
processing_status_code = ‘PENDING’
WHERE GROUP_ID = ***

UPDATE rcv_transactions_interface
SET request_id = NULL,
processing_request_id = NULL,
validation_flag = ‘Y’,
processing_status_code = ‘PENDING’,
transaction_status_code = ‘PENDING’,
processing_mode_code = ‘BATCH’
WHERE interface_transaction_id = ***
AND batch_id = ***

— Verification of the base tables Once the Receiving Transactions Processor is Completed

select * from apps.rcv_shipment_headers
where created_by = 2083

select * from apps.rcv_shipment_lines
where created_by = 2083
and po_header_id = 619
select * from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083

select * from apps.mtl_lot_numbers
where lot_number in (‘A6631684’, ‘A6631685’, ‘A6631686’)
select * from apps.rcv_lot_transactions
where lot_num in (‘A6631684’, ‘A6631685’, ‘A6631686’)
select * from apps.mtl_material_transactions
where created_by = 2083
and rcv_transaction_id in (select transaction_id from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083)

SELECT (SELECT segment1
FROM po_headers_all
WHERE po_header_id = pl.po_header_id
AND org_id = pl.org_id) po_number, pl.po_header_id,
pl.item_id, pl.po_line_id, pl.line_num, pll.shipment_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
FROM
apps.po_lines_all pl,
apps.po_line_locations_all pll,
apps.mtl_parameters mp,
apps.po_distributions_all pda
WHERE 1 = 1
AND pl.po_header_id = 619
AND pl.org_id = 308
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
order by 1, 5, 6

/by
, , , ,

What document can be viewed after running purchasing document open interface with records rejected?

Check the process_code in the po_headers_interface and po_lines_interface, if it is ‘REJECTED’,

select process_code from po_headers_interface;
select process_code from po_lines_interface;

please do the following:
Run the program – Purchasing Interface Errors Report
choose parameter : PO_DOCS_OPEN_INTERFACE

The report will list all the errors you have during importing. You can fix the data, then reset process_code = Null in both interface tables, rerun the Purchasing Document Open Interface.

update po_headers_interface set process_code = null
where process_code = ‘REJECTED’;
update po_lines_interface set process_code = null
where process_code = ‘REJECTED’;

/by
, , ,

Few Important AP Tables

AP_SUPPLIERS:

  • This table replaces the old PO_VENDORS table.
  • It stores information about your supplier level attributes.
  • Each row includes the purchasing, receiving, invoice, tax, classification, and general information.
  • Oracle Purchasing uses this information to determine active suppliers.
  • The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.

AP_SUPPLIER_SITES_ALL:

  • This table replaces the old PO_VENDOR_SITES_ALL table.
  • It stores information about your supplier site level attributes.
  • There is a row for unique combination of supplier address, operating unit and the business relationship that you have with the supplier.
  • The supplier address information is not maintained in this table and is maintained in TCA. The reference to the internal identifier of address in TCA will be stored in AP_SUPPLIER_SITES_ALL.LOCATION_ID, to link the address record in TCA.
  • Each row includes the supplier reference, purchasing, invoice, and general information.

AP_INVOICES_ALL:

  • It contains records for invoices you enter.
  • There is one row for each invoice you enter.
  • An invoice can have one or more invoice distribution lines and can have one or more scheduled payments.  

AP_INVOICE_LINES_ALL:

  • It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
  • An invoice can have one or more invoice lines.
  • An invoice line represents goods (direct or indirect materials), service(s), and/or associated tax/freight/miscellaneous charges invoiced from a supplier.
  • An invoice line should contain all the attributes that are present on the physical or electronic invoice presented by the supplier.

AP_INVOICE_DISTRIBUTIONS_ALL:

  • It holds the distribution information that is manually entered or system-generated.
  • There is one row for each invoice distribution and a distribution must be associated with an invoice.
  • An invoice can have multiple distributions.

AP_INVOICE_PAYMENTS_ALL:

  • It contains records of invoice payments that you made to suppliers.
  • There is one row for each payment you make for each invoice and there is one payment and one invoice for each payment in this table.
  • Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick payment.
  • When you void a payment, your Oracle Payables inserts an additional payment line that is the negative of the original payment line.

AP_PAYMENT_SCHEDULES_ALL:

  • This table stores information about scheduled payment information on invoices.

AP_PAYMENT_HISTORY_ALL:

  • It stores the clearing/unclearing history for payments.
  • It also stores the maturity history for future dated payments.
  • The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable.
  • Any time a payment is cleared or uncleared, a row is inserted into this table for the payment.

AP_BATCHES_ALL:

  • It contains summary information about invoices you enter in batches if you enable the Batch Control Payables option.
  • There is one row for each batch of invoices you enter.
  •  If you enable Batch Control, each invoice must correspond to a record in this table.
  •  Your Oracle Payables application uses this information to group together invoices that one person entered in a batch.

AP_CHECKS_ALL:

  • It stores information about payments issued to suppliers or refunds received from suppliers. 
  • There is one row for each payment you issue to a supplier or refund received from a supplier.
  • Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers.
  • Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Oracle Payables application also stores address information for all payments.

AP_HOLDS_ALL:

  • It contains information about holds that you or your Oracle Payables application place on an invoice.
  • For non-matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice-shipment match.
  • An invoice may have one or more corresponding rows in this table.
  • Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table.

AP_BANK_ACCOUNTS_ALL:

  • It contains information about your bank accounts.
  • There is one row for each bank account you define and each bank account must be affiliated with one bank branch.

AP_BANK_ACCOUNT_USES_ALL:

  • It stores information for the internal and external bank accounts you define in Oracle Payables and Oracle Receivables applications.

AP_CARDS_ALL:

  • It stores information about the corporate credit cards issued to your employees by your corporate credit card providers.

AP_TRIAL_BALANCE:

  • It contains denormalized information about invoices and payments posted to the accrual set of books.
/by