8/9/2019 Report Query

1/2

SELECT rct.trx_number, rct.customer_trx_id, rct.trx_date, bhp.party_name bill_to_cust_name, shp.party_name ship_to_cust_name, raa.address1 remit_address_line1, raa.address2 remit_address_line2, raa.address3 remit_address_line3, raa.city remit_city, raa.state remit_state, raa.postal_code remit_postal_code, bhca.account_number customer_number, rt.name terms_name, bhl.address1 bill_to_address1, bhl.address2 bill_to_address2, bhl.address3 bill_to_address3, bhl.city bill_to_city, bhl.state bill_to_state, bhl.postal_code bill_to_postal_code, bhl.country bill_to_country, shl.address1 ship_to_address1, shl.address2 ship_to_address2, shl.address3 ship_to_address3, shl.city ship_to_city,

shl.state ship_to_state, shl.postal_code ship_to_postal_code, shl.country ship_to_country, rsa.salesrep_id salesperson_id, rsa.name salespersonFROM ra_customer_trx_all rct, ra_addresses_all raa, hz_parties bhp, hz_parties shp, hz_cust_accounts_all bhca, hz_cust_accounts_all shca, hz_cust_site_uses_all bhcsu, hz_cust_site_uses_all shcsu,

hz_cust_acct_sites_all bhcas, hz_cust_acct_sites_all shcas, hz_party_sites bhps, hz_party_sites shps, hz_locations bhl, hz_locations shl, ra_terms rt, ra_salesreps_all rsaWHERE rct.remit_to_address_id = raa.address_idAND rct.bill_to_customer_id = bhca.cust_account_idAND rct.ship_to_customer_id = shca.cust_account_idAND bhp.party_id = bhps.party_idAND shp.party_id = shps.party_id

AND rct.bill_to_site_use_id = bhcsu.site_use_idAND bhcsu.cust_acct_site_id = bhcas.cust_acct_site_idAND bhcas.party_site_id = bhps.party_site_idAND bhps.location_id = bhl.location_idAND rct.ship_to_site_use_id = shcsu.site_use_idAND shcsu.cust_acct_site_id = shcas.cust_acct_site_idAND shcas.party_site_id = shps.party_site_idAND shps.location_id = shl.location_idAND rct.term_id = rt.term_idAND rct.primary_salesrep_id = rsa.salesrep_id

8/9/2019 Report Query

2/2

AND rct.org_id = 204AND bhp.party_name LIKE NVL(:cust_name||'%','%')AND rct.trx_number = '10046900';

-----

SELECT msib.inventory_item_id product_id, msib.segment1 product_name, rctl.description product_description, rctl.uom_code uom, rctl.quantity_invoiced quantity, rctl.unit_selling_price net_price, rctl.extended_amount gross_price, --- rctl.quantity_invoiced*rctl.unit_selling_price gross_price, rctl.sales_order sales_order_number, rctl.sales_order_line so_line_number, rctl.sales_order_date so_ship_date, ood.organization_name warehouse_nameFROM ra_customer_trx_all rct, ra_customer_trx_lines_all rctl, mtl_system_items_b msib, org_organization_definitions oodWHERE rct.customer_trx_id = rctl.customer_trx_idAND rctl.warehouse_id = ood.organization_id

AND rctl.inventory_item_id = msib.inventory_item_idAND rctl.line_type = 'LINE'AND rct.trx_number = '10046900'AND msib.organization_id = 204;

---

SELECT SUM(extended_amount) total_freightFROM ra_customer_trx_all rct, ra_customer_trx_lines_all rctlWHERE rct.customer_trx_id = rctl.customer_trx_idAND rctl.line_type = 'FREIGHT'AND rct.trx_number = '10046900';

---

SELECT SUM(extended_amount) total_taxFROM ra_customer_trx_all rct, ra_customer_trx_lines_all rctlWHERE rct.customer_trx_id = rctl.customer_trx_idAND rctl.line_type = 'TAX'AND rct.trx_number = '10046900';