PO OPENINTERFACE

INTERFACE TABLE:
----------------
    PO_HEADERS_INTERFACE
    PO_LINES_INTERFACE
    PO_DISTRIBUTIONS_INTERFACE

         RCV_HEADERS_INTERFACE
         RCV_TRANSACTIONS_INTERFACE

BASE TABLE:
-----------

    PO_HEADERS_ALL
    PO_LINES_ALL
    PO_LINE_LOCATIONS_ALL

CONCURRENT PROGRAM NAME:
====================================

    IMPORT STANDARD PURCHASE ORDER




STAGGING TABLE:
---------------

HEADER TABLE:
-------------

create table T_PO_HEAD(
        INTERFACE_HEADER_ID     NUMBER,    
        ORG_ID                  NUMBER,
        DOCUMENT_TYPE_CODE      VARCHAR2(25),
        SHIP_TO_LOCATION_ID     NUMBER,
        BILL_TO_LOCATION_ID     NUMBER,
        AGENT_NAME              VARCHAR2(240),
        APPROVAL_STATUS         VARCHAR2(25),
        CURRENCY_CODE           VARCHAR2(15),
        PROCESSING_ID           NUMBER,
        BATCH_ID             NUMBER,
        ACTION                VARCHAR2(25),
        VENDOR_ID            NUMBER);


LINE TABLE:
-----------


create table T_PO_LINE(
                INTERFACE_HEADER_ID     NUMBER,
        INTERFACE_LINE_ID     NUMBER,
        LINE_NUM         NUMBER,
        LINE_TYPE          VARCHAR2(25),
        ITEM               VARCHAR2(1000),
        CATEGORY            VARCHAR2(2000),
        ITEM_DESCRIPTION        VARCHAR2(240),
        UOM_CODE               VARCHAR2(3),
        QUANTITY         NUMBER,
        UNIT_PRICE         NUMBER,
        PROCESSING_ID        NUMBER,
        ACTION              VARCHAR2(25));


DATA FILE FOR HEADER:
---------------------
810001,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810002,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810003,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810004,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810005,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810006,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810007,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810008,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810009,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810010,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810011,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810012,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810013,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810014,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810015,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810016,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810017,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810018,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810019,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600
810020,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600

CONTROL FILE FOR HEADER:
------------------------


LOAD DATA 
INFILE '/oracle2/VIS1211/apps/apps_st/appl/po/12.0.0/bin/T_PO.txt' 
INSERT INTO TABLE T_PO_HEAD
FIELDS TERMINATED BY "," 
(
    INTERFACE_HEADER_ID,    
    ORG_ID,                   
    DOCUMENT_TYPE_CODE,       
    SHIP_TO_LOCATION_ID, 
    BILL_TO_LOCATION_ID,      
    AGENT_NAME,              
    APPROVAL_STATUS,        
    CURRENCY_CODE,
        PROCESSING_ID,
        BATCH_ID,
        ACTION,
        VENDOR_ID                              
)         


DATA FILE FOR LINE:
-------------------

810001,55001,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
810002,55002,1,Goods,ISO002,MISC.MISC,ISO002,Ea,8,8,25,ORIGINAL
810003,55003,1,Goods,ISO002,MISC.MISC,ISO002,Ea,9,9,25,ORIGINAL
810004,55004,1,Goods,ISO002,MISC.MISC,ISO002,Ea,10,10,25,ORIGINAL
810005,55005,1,Goods,ISO002,MISC.MISC,ISO002,Ea,5,5,25,ORIGINAL
810006,55006,1,Goods,ISO002,MISC.MISC,ISO002,Ea,11,10,25,ORIGINAL
810007,55007,1,Goods,ISO002,MISC.MISC,ISO002,Ea,12,10,25,ORIGINAL
810008,55008,1,Goods,ISO002,MISC.MISC,ISO002,Ea,13,10,25,ORIGINAL
810009,55009,1,Goods,ISO002,MISC.MISC,ISO002,Ea,14,10,25,ORIGINAL
810010,55010,1,Goods,ISO002,MISC.MISC,ISO002,Ea,15,10,25,ORIGINAL
810011,55011,1,Goods,ISO002,MISC.MISC,ISO002,Ea,16,10,25,ORIGINAL
810012,55012,1,Goods,ISO002,MISC.MISC,ISO002,Ea,17,10,25,ORIGINAL
810013,55013,1,Goods,ISO002,MISC.MISC,ISO002,Ea,18,10,25,ORIGINAL
810014,55014,1,Goods,ISO002,MISC.MISC,ISO002,Ea,19,10,25,ORIGINAL
810015,55015,1,Goods,ISO002,MISC.MISC,ISO002,Ea,6,10,25,ORIGINAL
810016,55016,1,Goods,ISO002,MISC.MISC,ISO002,Ea,12,10,25,ORIGINAL
810017,55017,1,Goods,ISO002,MISC.MISC,ISO002,Ea,6,6,25,ORIGINAL
810018,55018,1,Goods,ISO002,MISC.MISC,ISO002,Ea,9,15,25,ORIGINAL
810019,55019,1,Goods,ISO002,MISC.MISC,ISO002,Ea,8,12,25,ORIGINAL
810020,55020,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,12,25,ORIGINAL


CONTROL FILE FOR LINE:
----------------------

LOAD DATA 
INFILE '/oracle2/VIS1211/apps/apps_st/appl/po/12.0.0/bin/T_PL.txt' 
INSERT INTO TABLE T_PO_LINE
FIELDS TERMINATED BY ","
(
    INTERFACE_HEADER_ID,
    INTERFACE_LINE_ID,
    LINE_NUM,
    LINE_TYPE, 
    ITEM,      
    CATEGORY,   
    ITEM_DESCRIPTION,   
    UOM_CODE,          
    QUANTITY,
    UNIT_PRICE,
        PROCESSING_ID,
        ACTION    
)


After Finishing this move the Header and Line file to gftp.

Run the Header and Line Control File in SQL Loader.




PROCEDURE PROGRAM:
------------------


CREATE OR REPLACE PROCEDURE pur_proc (errbuf out varchar2,retcode out varchar2) is
  
   CURSOR c1 IS SELECT * FROM T_PO_HEAD;
   CURSOR c2 IS SELECT * FROM T_PO_LINE;

BEGIN
  FOR i IN c1 LOOP
   INSERT INTO po_headers_interface
                 (INTERFACE_HEADER_ID,    
                  ORG_ID,                   
                  DOCUMENT_TYPE_CODE,       
                  SHIP_TO_LOCATION_ID, 
                  BILL_TO_LOCATION_ID,      
                  AGENT_NAME,              
                  APPROVAL_STATUS,        
                  CURRENCY_CODE,
                  PROCESSING_ID,
                  BATCH_ID,
                  ACTION,
                  VENDOR_ID)
           VALUES (i.INTERFACE_HEADER_ID,    
                    i.ORG_ID,                   
                    i.DOCUMENT_TYPE_CODE,       
                    i.SHIP_TO_LOCATION_ID, 
                    i.BILL_TO_LOCATION_ID,      
                    i.AGENT_NAME,              
                    i.APPROVAL_STATUS,        
                    i.CURRENCY_CODE,
                    i.PROCESSING_ID,
                    i.BATCH_ID,
                    i.ACTION,
                    i.VENDOR_ID);               
   END LOOP;

 FOR j IN c2 LOOP
  INSERT INTO po_lines_interface
                  (INTERFACE_HEADER_ID,
                    INTERFACE_LINE_ID,
                    LINE_NUM,
                    LINE_TYPE, 
                    ITEM,      
                    CATEGORY,   
                    ITEM_DESCRIPTION,   
                    UOM_CODE,          
                    QUANTITY,
                    UNIT_PRICE,
                    PROCESSING_ID,
                    ACTION )
                
           VALUES (j.INTERFACE_HEADER_ID,
                    j.INTERFACE_LINE_ID,
                    j.LINE_NUM,
                    j.LINE_TYPE, 
                    j.ITEM,      
                    j.CATEGORY,   
                    j.ITEM_DESCRIPTION,   
                    j.UOM_CODE,          
                    j.QUANTITY,
                    j.UNIT_PRICE,
                    j.PROCESSING_ID,
                    j.ACTION );
                
   END LOOP;
   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      fnd_file.put_line (fnd_file.LOG, SQLERRM);
     
END;


Run the Procedure using PL/SQL Stored Preocedure.


Finally Run the Concurrent Program.




QUERY:
------

select * from T_PO_HEAD;

select * from T_PO_LINE;


select * from po_headers_interface

select * from po_lines_interface


select * from po_interface_errors

select  * from po_interface_errors where request_id=5823116

select * from po_headers_all where request_id=5823572;

select * from po_lines_all where request_id=5823678;


PROCEDURE USING VALIDATION
--------------------------


CREATE OR REPLACE PROCEDURE pur_proc_validate (errbuf out varchar2,retcode out varchar2) is
  
   CURSOR c1 IS SELECT * FROM T_PO_HEAD;
   CURSOR c2 IS SELECT * FROM T_PO_LINE;
  
   ven_name varchar2(15);
   org_name varchar2(20);
   s_item   varchar2(20);
   l_item_id varchar2(20);
  
  
BEGIN
  FOR i IN c1 LOOP
 
BEGIN

      SELECT vendor_name into ven_name FROM po_vendors where vendor_id=i.vendor_id;
      dbms_output.put_line('Vendor_name'|| ven_name);
      EXCEPTION
      WHEN OTHERS THEN
         dbms_output.put_line('Vendor_Id is Invalid');
      END;

    
    
     INSERT INTO po_headers_interface
                 (INTERFACE_HEADER_ID,    
                  ORG_ID,                   
                  DOCUMENT_TYPE_CODE,       
                  SHIP_TO_LOCATION_ID, 
                  BILL_TO_LOCATION_ID,      
                  AGENT_NAME,              
                  APPROVAL_STATUS,        
                  CURRENCY_CODE,
                  PROCESSING_ID,
                  BATCH_ID,
                  ACTION,
                  VENDOR_ID)
           VALUES  (i.INTERFACE_HEADER_ID,    
                    i.ORG_ID,                   
                    i.DOCUMENT_TYPE_CODE,       
                    i.SHIP_TO_LOCATION_ID, 
                    i.BILL_TO_LOCATION_ID,      
                    i.AGENT_NAME,              
                    i.APPROVAL_STATUS,        
                    i.CURRENCY_CODE,
                    i.PROCESSING_ID,
                    i.BATCH_ID,
                    i.ACTION,
                    i.VENDOR_ID);               
   END LOOP;

 FOR j IN c2 LOOP

 BEGIN

  SELECT segment1 INTO s_item FROM mtl_system_items_b WHERE segment1 = j.item;
  dbms_output.put_line('segment1'|| s_item);
  EXCEPTION
  WHEN OTHERS THEN
         dbms_output.put_line('Item is Invalid');
  END;
 
 
 
  INSERT INTO po_lines_interface
                    (INTERFACE_HEADER_ID,
                    INTERFACE_LINE_ID,
                    LINE_NUM,
                    LINE_TYPE, 
                    ITEM,      
                    CATEGORY,   
                    ITEM_DESCRIPTION,   
                    UOM_CODE,          
                    QUANTITY,
                    UNIT_PRICE,
                    PROCESSING_ID,
                    ACTION )
                
           VALUES (j.INTERFACE_HEADER_ID,
                    j.INTERFACE_LINE_ID,
                    j.LINE_NUM,
                    j.LINE_TYPE, 
                    j.ITEM,      
                    j.CATEGORY,   
                    j.ITEM_DESCRIPTION,   
                    j.UOM_CODE,          
                    j.QUANTITY,
                    j.UNIT_PRICE,
                    j.PROCESSING_ID,
                    j.ACTION );
                
   END LOOP;
   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      fnd_file.put_line (fnd_file.LOG, SQLERRM);     
END;



   Auther Validation
----------------------------

CREATE OR REPLACE PROCEDURE XX_PO_VALID
IS
   CURSOR data_load
   IS
      SELECT *
        FROM xx_header3;

   CURSOR data_load1
   IS
      SELECT *
        FROM xx_lines3;
 
   l_vendor_id   NUMBER (10);
   l_item        VARCHAR2 (100);
   l_item_id     number(6);
   l_org_id      number(6);
   l_flag        VARCHAR2 (4)DEFAULT 'A';
   l_msg         VARCHAR2 (200);
BEGIN
   DELETE FROM po_headers_interface;

   DELETE FROM po_lines_interface;

   COMMIT;

   FOR i IN data_load
   LOOP
      BEGIN
         SELECT vendor_id
           INTO l_vendor_id
           FROM po_vendors
          WHERE vendor_name = i.vendor_name;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_flag := 'E';
            l_vendor_id := 0;
            l_msg := 'Vendor id is Not in System';
      END;
     
      IF l_flag != 'E'
      THEN
        
         INSERT INTO po_headers_interface
                     (interface_header_id, batch_id, action, org_id,
                      document_type_code, currency_code, agent_id,
                      vendor_name, vendor_site_id,
                      ship_to_location_id, approval_status,
                      freight_carrier, fob, freight_terms
                     )
              VALUES (i.interface_header_id, i.batch_id, i.action, i.org_id,
                      i.document_type_code, i.currency_code, i.agent_id,
                      i.vendor_name, i.vendor_site_id,
                      i.ship_to_location_id, i.approval_status,
                      i.freight_carrier, i.fob, i.freight_terms
                     );
      END IF;
   END LOOP;
--   DBMS_OUTPUT.put_line ('interface_header_id' || i.interface_header_id);
--         DBMS_OUTPUT.put_line ('batch_id' || i.batch_id);

   FOR i1 IN data_load1
   LOOP
      l_flag := 'A';

      BEGIN
         SELECT segment1
           INTO l_item
           FROM mtl_system_items_b
          WHERE segment1 = i1.item
          AND organization_id = fnd_profile.value ('org_id');
      EXCEPTION
         WHEN OTHERS
         THEN
            l_flag := 'E';
            l_vendor_id := 0;
            l_msg := 'Item is not Valid Item';
      END;
       BEGIN
         SELECT inventory_item_id
           INTO l_item_id
           FROM mtl_system_items_b
          WHERE segment1 = i1.item
          AND organization_id = fnd_profile.value ('org_id');
      EXCEPTION
         WHEN OTHERS
         THEN
            l_flag := 'E';
            l_item_id := 0;
            l_msg := 'Item is not Valid Item';
      END;

      IF l_flag != 'E'
      THEN
         INSERT INTO po_lines_interface
                     (interface_header_id, interface_line_id,
                      line_num, shipment_num, line_type_id, item,
                      item_description,uom_code,
                      quantity, unit_price, ship_to_organization_id,
                      ship_to_location_id
                     )
              VALUES (i1.interface_header_id, i1.interface_line_id,
                      i1.line_num, i1.shipment_num, i1.line_type_id, i1.item,
                      i1.item_description,  i1.uom_code,
                      i1.quantity, i1.unit_price, i1.ship_to_organization_id,
                      i1.ship_to_location_id
                     );
      END IF;
   END LOOP;
--    DBMS_OUTPUT.put_line ('interface_header_id' || i1.interface_header_id
--                              );
--         DBMS_OUTPUT.put_line ('uom_code,' || i1.uom_code);

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      --FND_FILE.PUT_LINE(FND_FILE.LOG,'Records not loaded into the interface table');
      fnd_file.put_line (fnd_file.LOG, SQLERRM);
END xx_po_valid;


EXECUTE XX_PO_VALID;