Sunday, May 10, 2020

Script to Create a BPA (Blanket Purchase Agreement) through Import Price Catalogs Program in Oracle Apps R12













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:

Table Name

Purpose

PO_HEADERS_INTERFACE

Header level data

PO_LINES_INTERFACE

Line data

PO_LINE_LOCATIONS_INTERFACE

Price Breaks

PO_ATTR_VALUES_INTERFACE

Base Descriptors

PO_ATTR_VALUES_TLP_INTERFACE

Base Descriptors with Language specific values

 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;   

1 Responses to “Script to Create a BPA (Blanket Purchase Agreement) through Import Price Catalogs Program in Oracle Apps R12”

wisewitch said...
May 13, 2020 at 3:54 AM

Its a good material. Thank you!!


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.