R12 Generate Intended BOM

set serveroutput on size unlimited DECLARE err_msg VARCHAR2(5000); error_code NUMBER; grp_id NUMBER; session_id NUMBER; CURSOR c_get_items_for_BOM IS SELECT inventory_item_id, organization_id FROM mtl_system_items msi WHERE 1=1 AND msi.segment1 in ('ITEM_A','ITEM_B') ---- PUT YOUR ITEMS HERE AND organization_id =2; BEGIN SELECT bom_explosion_temp_s.NEXTVAL INTO grp_id FROM Dual ; SELECT bom_explosion_temp_session_s.NEXTVAL INTO session_id FROM Dual ; FOR r_get_items_for_BOM IN c_get_items_for_BOM LOOP bompexpl.exploder_userexit ( 0 --verify_flag IN NUMBER DEFAULT 0, ,r_get_items_for_BOM.organization_id -- org_id IN NUMBER, ,1 -- order_by IN NUMBER DEFAULT 1, ,grp_id -- grp_id unique value to identify current explosion -- use value from sequence bom_explosion_temp_s ,session_id -- session_id -- IN NUMBER DEFAULT 0, it is unique value to identify current session ,use value from bom_explosion_temp_session_s ,10 -- levels_to_explode IN NUMBER DEFAULT 1, ,1 -- bom_or_eng IN NUMBER DEFAULT 1, ,1 -- impl_flag IN NUMBER DEFAULT 1, ,2 -- plan_factor_flag IN NUMBER DEFAULT 2, 2 means NO ,2 -- explode_option IN NUMBER DEFAULT 2,explode_option 1 - All, 2 - Current,3 - Current and future ,2 -- module IN NUMBER DEFAULT 2, ,0 -- cst_type_id IN NUMBER DEFAULT 0, ,2 -- std_comp_flag IN NUMBER DEFAULT 0, ,1 -- expl_qty IN NUMBER DEFAULT 1, ,r_get_items_for_BOM.inventory_item_id --1681441 -- item_id IN NUMBER, ,'' -- alt_desg IN VARCHAR2 DEFAULT '', ,'' -- comp_code IN VARCHAR2 DEFAULT '', ,to_char(sysdate,'dd-mon-yy HH24:MI') -- rev_date IN VARCHAR2, ,'' --unit_number IN VARCHAR2 DEFAULT '', ,0 --release_option IN NUMBER DEFAULT 0, ,err_msg -- err_msg OUT NOCOPY VARCHAR2, ,error_code -- error_code OUT NOCOPY NUMBER ); IF err_msg is not null or error_code is not null then dbms_output.put_line(err_msg); dbms_output.put_line(error_code); END IF; END LOOP; dbms_output.put_line('Use grp_id = '||grp_id ' to query the table BOM_EXPLOSION_TEMP in this session only.'); dbms_output.put_line('session_id = '||session_id); END;

Query to get Customer Information in R12

Below SQL query gets the Customer information from TCA (Trading Community Architecture)
tables.


SELECT cust.party_name customer_name, cust_acct.account_number, cust_uses.site_use_code, cust_loc.address1, cust_loc.address2, cust_loc.address3, cust_loc.address4, cust_loc.city, cust_loc.postal_code, bank.party_name bank_name, bank_prof.home_country, branch.party_name branch_name, branch_prof.bank_or_branch_number branch_number, account.bank_account_num, account.bank_account_name FROM hz_parties bank, hz_relationships rel, hz_parties branch, hz_organization_profiles bank_prof, hz_organization_profiles branch_prof, iby_ext_bank_accounts account, iby_account_owners acc_owner, iby_external_payers_all ext_payer, iby_pmt_instr_uses_all acc_instr, hz_parties cust, hz_cust_accounts cust_acct, hz_cust_acct_sites_all cust_site, hz_cust_site_uses_all cust_uses, hz_locations cust_loc WHERE 1 = 1 AND bank.party_id = rel.object_id AND bank.party_type = rel.object_type AND rel.object_table_name = 'HZ_PARTIES' AND rel.relationship_code = 'BRANCH_OF' AND rel.subject_id = branch.party_id AND rel.subject_type = branch.party_type AND rel.subject_table_name = 'HZ_PARTIES' AND bank.party_id = bank_prof.party_id AND branch.party_id = branch_prof.party_id AND bank.party_id = account.bank_id AND branch.party_id = account.branch_id AND account.ext_bank_account_id = acc_owner.ext_bank_account_id AND acc_owner.account_owner_party_id = cust.party_id AND account.ext_bank_account_id = acc_instr.instrument_id AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id AND ext_payer.cust_account_id = cust_acct.cust_account_id AND cust_acct.cust_account_id = cust_site.cust_account_id AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id AND cust_uses.site_use_id = ext_payer.acct_site_use_id AND cust_uses.location = cust_loc.location_id AND cust.party_id = cust_acct.party_id;