Category Archives: Receivables

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 »