Friday, July 3, 2020

Script to re-compile all invalid custom objects in APPS schema (DBMS_DDL.ALTER_COMPILE & DBMS_UTILITY.COMPILE_SCHEMA)









In this post, we have given a custom package while has below logic

  1. Compiles all invalid custom objects (objects starting with XX%). This will be handy when every time a custom code deployment happens. This ensure all dependent objects are compiled and verified.
  2. Compiles all objects in a schema

API used (invalid Objects only): DBMS_DDL.ALTER_COMPILE

API used (Complete Schema): DBMS_UTILITY.COMPILE_SCHEMA

Package:

CREATE OR REPLACE PACKAGE xxsh_dba_utilities AUTHID CURRENT_USER AS PROCEDURE compile_invalid_apps_objects; PROCEDURE compile_schema (piv_schema_name IN VARCHAR2); END xxsh_dba_utilities; / SHOW ERRORS; CREATE OR REPLACE PACKAGE BODY xxsh_dba_utilities AS ------------------------------------------------------------------- -- PROCEDURE compile_invalid_apps_objects -------------------------------------------------------------------- PROCEDURE compile_invalid_apps_objects AS CURSOR cur_invalid_objects IS SELECT object_name, object_type, owner FROM dba_objects WHERE owner = 'APPS' AND status = 'INVALID' AND object_name like 'XX%' AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'VIEW', 'MATERIALIZED VIEW', 'SYNONYM' ); lv_status VARCHAR2(200); BEGIN FOR i IN cur_invalid_objects LOOP BEGIN dbms_output.put_line('Compiling Object named :'||i.object_name); IF i.object_type IN ('VIEW','MATERIALIZED VIEW','SYNONYM') THEN EXECUTE IMMEDIATE 'ALTER '||i.object_type ||' '||i.object_name||' COMPILE'; ELSE dbms_ddl.alter_compile(i.object_type,NULL,i.object_name); END IF; lv_status := NULL; SELECT status INTO lv_status FROM dba_objects WHERE object_name = i.object_name AND object_type = i.object_type; dbms_output.put_line ('Post Compilation Object Status :'||lv_status); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Unhandled Exception:'||SQLERRM); END; END LOOP; END compile_invalid_apps_objects; ------------------------------------------------------------------- -- PROCEDURE compile_schema -------------------------------------------------------------------- PROCEDURE compile_schema (piv_schema_name IN VARCHAR2) AS ln_check NUMBER; BEGIN SELECT COUNT(1) INTO ln_check FROM dba_users WHERE username = piv_schema_name; IF ln_check > 0 THEN dbms_utility.compile_schema(piv_schema_name); dbms_output.put_line('Schema named ' ||piv_schema_name ||' compiled successfully' ); ELSE dbms_output.put_line('Error: Schema named ' ||piv_schema_name ||' does not exists' ); END IF; END compile_schema; END xxsh_dba_utilities; / SHOW ERRORS;

Calling Script:

SET SERVEROUTPUT ON; BEGIN xxsh_dba_utilities.compile_invalid_apps_objects; END;

Friday, July 3, 2020 by Team search · 0

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

API: PO_PDOI_GRP.CATALOG_UPLOAD

Tested Instance: R12

Script:

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;

by Team search · 0

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.