Category Archives: Scripts

Query to list Active Customers and their Sites Information

Here is the query to list Active Customers and their Sites Information /********************************************************** *PURPOSE: To list customers and their sites information * *AUTHOR: Shailender Thallam * **********************************************************/ SELECT ———————- –Customer Information ———————- hp.party_id, hp.party_name "CUSTOMER_NAME", hca.cust_account_id, hca.account_number, hcas.org_id, ————————— –Customer Site Information ————————— hcas.cust_acct_site_id, hps.party_site_number, hcsu.site_use_code, ———————– –Customer Site Address ———————– hl.address1, hl.address2, hl.address3, hl.address4,… Read More »

Join condition between RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL

Here is the query to find link of Sales Orders with their corresponding Invoices /************************************************************************** *PURPOSE: To find link of Sales Orders with their corresponding Invoices * *AUTHOR: Shailender Thallam * **************************************************************************/ SELECT ooh.order_number, ool.line_number, ool.ordered_item, ool.ordered_quantity * ool.unit_selling_price, rcta.trx_number invoice_number, rcta.trx_date, rctl.line_number, rctl.unit_selling_price, ooh.org_id FROM oe_order_headers_all ooh, oe_order_lines_all ool, ra_customer_trx_all rcta, ra_customer_trx_lines_all rctl WHERE… Read More »

Script to Update Customer Locations – HZ_LOCATION_V2PUB.UPDATE_LOCATION

We can use standard API HZ_LOCATION_V2PUB.UPDATE_LOCATION to update the existing customer address stored in the HZ_LOCATIONS table. /******************************************************************************** *PURPOSE: To Customer Locations information from back-end * *AUTHOR: Shailender Thallam * *********************************************************************************/ SET SERVEROUTPUT ON;   DECLARE — –Cursor to fetch location details — cursor cur_loc IS SELECT location_id, object_version_number, country, state, province FROM hz_locations WHERE… Read More »

API to find out Code Combination ID for given Segments

Here is a usage of standard API “fnd_flex_ext.get_combination_id” to find code_combination_id for given segment values. Returns TRUE if combination is valid, or FALSE and sets error message on server using FND_MESSAGE if invalid. Note: This API return Code Combination ID but it will NOT create a Code Combination ID if it is not existing. 1… Read More »