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;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
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.
1 Responses to “Script to Create a BPA (Blanket Purchase Agreement) through Import Price Catalogs Program in Oracle Apps R12”
May 13, 2020 at 3:54 AM
Its a good material. Thank you!!
Post a Comment