Friday, July 3, 2020

API to update Blanket Purchase Agreement in Oracle Apps R12 (PO_PDOI_GRP.CATALOG_UPLOAD)

This is an API used by Oracle internal programs to update the catalog using “iProcurement Catalog Administration”.

  1. In this case, whenever a change was made using this API, it resides in draft tables and a change summary notification is sent to buyer of the BPA.
  2. The buyer can view the changes made from notification and approve or reject the notification.
  3. If approved, the changes will reflect in PO_HEADERS_ALL, PO_LINES_ALL etc tables and new revision is created.
  4. If rejected, the changes in draft tables will be stamped has "REJECTED"
  5. Additionally “PO_PDOI_GRP” is the same package the standard concurrent program “Import Price Catalogs” programs calls
  6. In order to this API to work, the BPA should be given access to CATALOG_ADMIN for update
  7. This API takes inputs from standard BPA Open Interface tables like PO_HEADERS_INTERFACE , PO_LINES_INTERFACE etc


Tested Instance: R12


SET DEFINE OFF; SET SERVEROUTPUT ON; DECLARE ln_batch_id NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSDATE,'HHMISSSS')); PROCEDURE submit_catalog_change (piv_approval_status IN VARCHAR2, pin_batch_id IN NUMBER, pin_org_id IN NUMBER, pin_intf_file_id IN NUMBER ) IS ln_processed_lines_count NUMBER; ln_rejected_lines_count NUMBER; lv_err_tolerance_exceeded VARCHAR2(2000); lv_return_status VARCHAR2(1); lv_error_message VARCHAR2(2000); lv_msg VARCHAR2(2000); ln_total_processed_lines NUMBER; ln_total_failed_lines NUMBER; lv_cutom_status VARCHAR2(200); lv_custom_error_message VARCHAR2(2000); BEGIN dbms_output.put_line('ln_batch_id: '||ln_batch_id); FOR intf_rec IN ( SELECT * FROM po_headers_interface WHERE batch_id = pin_batch_id AND org_id = pin_org_id ) LOOP dbms_output.put_line('Processing Interface_HEADER_ID :' ||intf_rec.interface_header_id ||' with Interface Code = ' ||intf_rec.interface_source_code ); ln_processed_lines_count := NULL; ln_rejected_lines_count := NULL; lv_err_tolerance_exceeded := NULL; PO_PDOI_GRP.CATALOG_UPLOAD (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_validation_level => fnd_api.g_valid_level_full, p_commit => fnd_api.g_true, x_return_status => lv_return_status, x_error_message => lv_error_message, p_gather_intf_tbl_stat => fnd_api.g_false, p_selected_batch_id => pin_batch_id, p_batch_size => po_pdoi_constants.g_def_batch_size, p_buyer_id => NULL, p_document_type => 'BLANKET', p_document_subtype => NULL, p_create_items => 'N', p_create_sourcing_rules_flag => 'N', p_rel_gen_method => NULL, p_sourcing_level => NULL, p_sourcing_inv_org_id => NULL, p_approved_status => NVL(piv_approval_status,'INITIATE APPROVAL'), p_process_code => po_pdoi_constants.g_process_code_pending, p_interface_header_id => intf_rec.interface_header_id, p_org_id => pin_org_id, p_ga_flag => 'Y', p_submit_dft_flag => 'Y', p_role => 'CAT_ADMIN', p_catalog_to_expire => NULL, p_err_lines_tolerance => 1000, x_processed_lines_count => ln_processed_lines_count, x_rejected_lines_count => ln_rejected_lines_count, x_err_tolerance_exceeded => lv_err_tolerance_exceeded ); IF (lv_return_status <> FND_API.G_RET_STS_SUCCESS) THEN FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP lv_msg := FND_MSG_PUB.get( p_msg_index => i, p_encoded => FND_API.G_FALSE ); dbms_output.put_line( 'The API call failed with error '||lv_msg); lv_custom_error_message := lv_custom_error_message||lv_msg||';'; END LOOP; lv_cutom_status := 'ERROR'; lv_custom_error_message := lv_custom_error_message ||'Processed: '||ln_processed_lines_count ||'. Error:'||ln_rejected_lines_count; ELSE lv_cutom_status := 'PROCESSED'; lv_custom_error_message := NULL; dbms_output.put_line( 'The API call ended with SUCESSS status'); END IF; dbms_output.put_line('processed_lines_count := '||ln_processed_lines_count); dbms_output.put_line('rejected_lines_count := '||ln_rejected_lines_count); dbms_output.put_line('err_tolerance_exceeded := '||lv_err_tolerance_exceeded); END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Others exception. Error: ' || SQLERRM); END submit_catalog_change; BEGIN -- Initialize enviroment mo_global.init('PO'); mo_global.set_policy_context('S',14); fnd_global.apps_initialize(1112,51234,201); -- Create Header Data Insert into PO.PO_HEADERS_INTERFACE (interface_header_id, batch_id, interface_source_code, po_header_id, document_num, vendor_id, vendor_site_id, org_id, agent_id, process_code, action, creation_date) VALUES ( po_headers_interface_s.NEXTVAL, --- interface_header_id, ln_batch_id, --- batch_id, po_pdoi_constants.g_call_mod_catalog_upload, 93063, -- po header id '40010129', -- bpa number 22, -- vendor id 30, -- vendor site id 84, -- org_id 108,-- agent id 'PENDING', --- process_code, 'UPDATE', ---action, SYSDATE ); -- Create Line 1 INSERT INTO PO.PO_LINES_INTERFACE (interface_line_id, interface_header_id, action, po_line_id, po_header_id, document_num, line_num, line_type, item, item_description, vendor_product_num, 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, po_headers_interface_s.CURRVAL, 'UPDATE', --- action, 282349, 93063, '40010129', 9, ---line_num, 'Goods', --- line_type, NULL, --- item, 'test item 9', -- item_description 'vendor_item',-- Vendor product Num 3129,-- catagory_id 'EA', --uom_code, 30, -- unit_price, NULL, --- ship_to_organization_id, NULL, --- ship_to_location_id, SYSDATE, --- creation_date, 'N' ); COMMIT; submit_catalog_change(piv_approval_status => 'INITIATE APPROVAL' , pin_batch_id => ln_batch_id, pin_org_id => 84, pin_intf_file_id => NULL ); END;

0 Responses to “API to update Blanket Purchase Agreement in Oracle Apps R12 (PO_PDOI_GRP.CATALOG_UPLOAD)”

Post a Comment


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.