Account Receivable Invoice: Well, it most probably is the single most important thing for your client as it is where they will get the money from. So obviously this becomes one of the most if not the most important part of O2C cycle. In most cases, Account Receivable invoices will be generated to charge a customer for the sales order you delivered. Following query will give you the Account Receivable Invoice Query Details:
Account Receivable Invoice Query
SELECT trx.trx_number trx_number,
trx.trx_date trx_date,
trx.created_by created_by,
b_bill_party.party_name Customer,
a_bill_loc.postal_code postal_code,
trx.invoice_currency_code inv_Currency,
t.name term_name,
trx.purchase_order PO_number,
b_bill.account_number bill_to_custnum,
b_bill_party.party_name bill_to_custname,
a_bill_loc.address1 bill_to_address1,
a_bill_loc.address2 bill_to_address2,
a_bill_loc.address3 bill_to_address3,
a_bill_loc.address4 bill_to_address4,
a_bill_loc.city bill_to_city,
a_bill_loc.state bill_to_state,
a_bill_loc.province bill_to_province,
a_bill_loc.postal_code bill_to_postal_code,
a_bill_loc.country bill_to_country,
b_ship_party.party_name ship_to_custname,
u_ship.party_site_id ship_to_sitenum,
a_ship_loc.address1 ship_to_address1,
a_ship_loc.address2 ship_to_address2,
a_ship_loc.address3 ship_to_address3,
a_ship_loc.address4 ship_to_address4,
a_ship_loc.city ship_to_city,
a_ship_loc.postal_code ship_to_postal_code,
a_ship_loc.country ship_to_country,
a_ship_loc.state ship_to_state,
a_ship_loc.province ship_to_province,
a_remit_loc.address1 remit_to_address1,
a_remit_loc.address2 remit_to_address2,
a_remit_loc.address3 remit_to_address3,
a_remit_loc.address4 remit_to_address4,
a_remit_loc.city remit_to_city,
a_remit_loc.state remit_to_state,
a_remit_loc.postal_code remit_to_postal_code,
a_remit_loc.country remit_to_country ,
trx.created_by primary_salesrep_name,
(SELECT max(cp.raw_phone_number)
FROM hz_contact_points cp,
hz_cust_account_roles acct_role
WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id
and acct_role.relationship_id = cp.relationship_id
and cp.owner_table_name = ‘HZ_PARTIES’
and cp.contact_point_type=’PHONE’
and cp.phone_line_type=’MOBILE’
and cp.status =’A’
and cp.primary_flag = ‘Y’
) contact_mobile_ph_number,
(SELECT max(cp.email_address)
FROM hz_contact_points cp,
hz_cust_account_roles acct_role
WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id
and acct_role.relationship_id = cp.relationship_id
and cp.owner_table_name = ‘HZ_PARTIES’
and cp.contact_point_type=’EMAIL’
and cp.status =’A’
and cp.primary_flag = ‘Y’
) contact_email,
NVL((SELECT SUM(c.extended_amount)
FROM ra_customer_trx_lines_all c
WHERE c.customer_trx_id = trx.customer_trx_id
and c.line_type=’LINE’
),0) line_amount,
NVL((SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all lines
WHERE lines.customer_trx_id = trx.customer_trx_id
AND lines.line_type =’TAX’
),0) tax_amount,
NVL((SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all lines
WHERE lines.customer_trx_id = trx.customer_trx_id
AND lines.line_type =’FREIGHT’
),0) freight_amount,
NVL((SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all lines
WHERE lines.customer_trx_id = trx.customer_trx_id
),0) total_amount,
trx.trx_class
FROM
ra_terms t,
ra_cust_trx_types_all types,
ra_customer_trx_all trx,
hz_cust_accounts b_bill,
hz_parties b_bill_party,
hz_cust_acct_sites_all a_bill,
hz_party_sites a_bill_ps,
hz_locations a_bill_loc,
hz_parties b_ship_party,
hz_party_sites a_ship_ps,
hz_locations a_ship_loc,
ar_remit_to_locs_all a_remit,
hz_locations a_remit_loc,
hz_cust_site_uses_all u_bill,
hz_party_site_uses u_ship
WHERE trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id
AND trx.term_id = t.term_id(+)
AND trx.bill_to_customer_id = b_bill.cust_account_id
ANd b_bill.party_id = b_bill_party.party_id
AND trx.ship_to_party_id = b_ship_party.party_id(+)
AND trx.bill_to_site_use_id = u_bill.site_use_id
AND trx.ship_to_party_site_use_id = u_ship.party_site_use_id(+)
AND u_bill.cust_acct_site_id = a_bill.cust_acct_site_id(+)
AND a_bill.party_site_id = a_bill_ps.party_site_id(+)
AND a_bill_loc.location_id(+) = a_bill_ps.location_id
AND u_ship.party_site_id = a_ship_ps.party_site_id(+)
AND a_ship_loc.location_id(+) = a_ship_ps.location_id
AND trx.remit_to_address_seq_id = a_remit.address_loc_seq_id(+)
AND a_remit.location_id = a_remit_loc.location_id(+)
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Related Posts:
Query To Get The Shipment Details In Oracle Fusion
Sales Order Query in Oracle Fusion
Accounting Reconciliation In Oracle Fusion Finance
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
