Oracle Apps Knowledge Sharing

In this post, I have a given a script to create a BPA using "Import Price Catalogs Program".

Here we have given only basic header and line level data. Depends on your need, please modify the script.

 Interface Tables:

 Instance Tested: R12.2.4

Script:

SET DEFINE OFF;
SET SERVEROUTPUT ON;
DECLARE 

  ln_batch_id   NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSDATE,'HHMISSSS'));
  ln_standard_req NUMBER;
  
  PROCEDURE submit_catalog_import(pin_default_buyer_id IN NUMBER,
                                  piv_approval_status  IN VARCHAR2,
                                  pin_batch_id         IN NUMBER,
                                  pin_org_id           IN NUMBER,
                                  pin_intf_file_id     IN NUMBER,
                                  pon_request_id       OUT NUMBER
                                 ) IS
  lv_request_id  NUMBER;
  lv_result      BOOLEAN;
  lv_phase1      VARCHAR2(100);
  lv_status1     VARCHAR2(100);
  lv_dev_phase1  VARCHAR2(100);
  lv_dev_status1 VARCHAR2(100);
  lv_message1    VARCHAR2(100);
  
  lv_custom_status              VARCHAR2(200);
  lv_custom_error_message      VARCHAR2(2000);
   position_                   NUMBER;
  BEGIN
    position_ := 500;
  
    fnd_request.set_org_id(NVL(pin_org_id,FND_GLOBAL.ORG_ID));
    dbms_output.put_line  ('Org ID being processed in SUBMIT_CATALOG_IMPORT:'
                           || NVL(pin_org_id,FND_GLOBAL.ORG_ID)
                          );
    dbms_output.put_line('Batch id: '||pin_batch_id);
  
    -- Submit Import Price Catalogs:
    lv_request_id := fnd_request.submit_request
                     (application => 'PO',
                      program     => 'POXPDOI',
                      description => NULL,
                      start_time  => NULL,
                      sub_request => NULL,
                      argument1   => pin_default_buyer_id, --Default Buyer
                      argument2   => 'Blanket',  --Document Type
                      argument3   => NULL, --Document SubType
                      argument4   => 'N', --Create or Update Items
                      argument5   => 'N', --Create Sourcing Rules
                      argument6   => piv_approval_status, --Approval Status
                      argument7   => NULL, --Release Generation Method
                      argument8   => pin_batch_id, --Batch Id
                      argument9   => pin_org_id,--Operating Unit
                      argument10  => 'Y', --Global Agreement
                      argument11  => 'Y', --Enable Sourcing Level
                      argument12  => NULL,--Sourcing Level
                      argument13  => NULL,--Inv Org Enable
                      argument14  => NULL,--Inventory Organization
                      argument15  => 'Y', --p_group_lines
                      argument16  => 'N', --p_clm_flag
                      argument17  => 5000,--p_batch_size
                      argument18  => 'N' --p_gather_stats
                      );
    COMMIT;
    position_ := 510;
    IF lv_request_id = 0
    THEN
      dbms_output.put_line(' Failed to submit Process POXPDOI.'
                          || fnd_message.get);
      lv_custom_status := 'ERROR';
      lv_custom_error_message := 'Failed to submit Process POXPDOI. ' 
                                 || fnd_message.get;
    ELSE
      lv_result := fnd_concurrent.wait_for_request
                   (lv_request_id
                    ,1
                    ,0
                    ,lv_phase1
                    ,lv_status1
                    ,lv_dev_phase1
                    ,lv_dev_status1
                    ,lv_message1
                    );
    END IF;
    position_ := 520;
    IF NOT lv_result
    THEN
      dbms_output.put_line('No Status returned for the request Id: ' 
                          || lv_request_id
                          );
    ELSIF lv_dev_status1 = 'NORMAL'
    THEN
      lv_custom_status :='PROCESSED';
      lv_custom_error_message := NULL;   
      dbms_output.put_line('The Req-Id of POXPDOI Process is: ' 
                           || lv_request_id
                          );
      dbms_output.put_line('Submit Catalog Program having for org id ' 
                           || pin_org_id 
                           || ' completed successfully with status '
                           || lv_dev_status1
                           );  
    ELSE       
      lv_custom_status :='ERROR';
      lv_custom_error_message := lv_custom_error_message || 
                                 'Submit Catalog Program FAILED for org id ' 
                                 || pin_org_id 
                                 || '. Error: '||lv_message1;   
      dbms_output.put_line('The Req-Id of POXPDOI Process is: ' 
                          || lv_request_id
                          );
      dbms_output.put_line('Submit Catalog Program having for org id ' 
                           || pin_org_id 
                           || ' completed successfully with status '
                           || lv_dev_status1
                           );  
    END IF;    
    
    pon_request_id := lv_request_id;
    
    UPDATE po_headers_all
       SET cat_admin_auth_enabled_flag = 'Y'
     WHERE type_lookup_code = 'BLANKET'
       AND request_id = lv_request_id;
    COMMIT;    
               
  EXCEPTION
  WHEN OTHERS THEN
    pon_request_id := NVL(lv_request_id
                         ,0);
    dbms_output.put_line('Others exception. Error: ' ||
                         SQLERRM);
  END submit_catalog_import;
  
BEGIN  

  -- Set context
  mo_global.init('PO');
  mo_global.set_policy_context('S',84);
  fnd_global.apps_initialize(1178,50739,201);

  -- Create Header Data 
   Insert into PO.PO_HEADERS_INTERFACE
       (interface_header_id, 
        batch_id,    
        interface_source_code,
        effective_date,
        vendor_id,
        vendor_site_id,
        org_id,
        agent_id,
        process_code,
        action, 
        po_header_id,  
        creation_date,
        acceptance_required_flag,
        expiration_date)
     VALUES 
     ( po_headers_interface_s.NEXTVAL,  --- interface_header_id, 
        ln_batch_id,                    --- batch_id,     
         NULL,
         TO_DATE('14-APR-2020','DD-MON-RRRR'),
         22,                            -- vendor id 
         30,                            -- vendor site id 
         84,                            -- org_id 
         108,                           -- agent id 
        'PENDING',                      -- process_code,
        'ORIGINAL',                     -- action
        NULL,
        SYSDATE,
        'N',
        SYSDATE + 365   
        );  
        
   -- Create Line 1 
   Insert into PO.PO_LINES_INTERFACE
           (INTERFACE_LINE_ID,
            INTERFACE_HEADER_ID,
            ACTION,     
            LINE_NUM,          
            LINE_TYPE,        
            ITEM,   
            item_description,
            category_id,            
            UOM_CODE,  
            UNIT_PRICE,        
            SHIP_TO_ORGANIZATION_ID,         
            SHIP_TO_LOCATION_ID,                    
            CREATION_DATE,      
            LINE_LOC_POPULATED_FLAG)
         Values
           (po_lines_interface_s.nextval,    --- interface_line_id,
            po_headers_interface_s.currval,  --- interface_header_id,
           'ADD',                            --- action,     
            1,                               --- line_num,          
            'Goods',                         --- line_type,      
            NULL,                            --- item,        
            'test item 1',                   --- item_description
            3129,                            --- catagory_id
            'EA',                            --- uom_code,   
            10,                              --- unit_price,       
            NULL,                            --- ship_to_organization_id,          
            NULL,                            --- ship_to_location_id,
            SYSDATE,                         --- creation_date,        
            'N'); 

       COMMIT;       
            
    submit_catalog_import(pin_default_buyer_id => '108',
                          piv_approval_status  => 'APPROVED',
                          pin_batch_id         => ln_batch_id,
                          pin_org_id           => 84,
                          pin_intf_file_id     => NULL,
                          pon_request_id       => ln_standard_req
                         );       

END;