AR Invoice Customer Tax Information query in Oracle apps


I had a requirement to build a report for AR Invoice Customer Tax Information. We have done the query below

SELECT customer_name,
hl.address1||’, ‘||hl.address2||’, ‘||hl.address3 address, hl.city, hl.country,
(SELECT DISTINCT hcsua.tax_reference tax_reg_num
FROM hz_cust_accounts hz,
ar_customers ac,
hz_parties hp,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsua,
hz_party_sites hps,
hz_locations hl
WHERE hz.cust_account_id = ac.customer_id
AND hz.party_id = hp.party_id
AND hp.party_id = hps.party_id
AND hcas.party_site_id(+) = hps.party_site_id
AND hl.location_id(+) = hps.location_id
AND ac.customer_id = rc.customer_id
AND hcsua.org_id = rcta.org_id
AND hcsua.org_id = hcas.org_id
AND hz.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsua.cust_acct_site_id
AND site_use_code = ‘BILL_TO’
AND hcsua.tax_code IS NOT NULL) tax_reg_num,
(SELECT DISTINCT hcsua.tax_code
FROM hz_cust_accounts hz,
ar_customers ac,
hz_parties hp,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsua,
hz_party_sites hps,
hz_locations hl
WHERE hz.cust_account_id = ac.customer_id
AND hz.party_id = hp.party_id
AND hp.party_id = hps.party_id
AND hcas.party_site_id(+) = hps.party_site_id
AND hl.location_id(+) = hps.location_id
AND ac.customer_id = rc.customer_id
AND hcsua.org_id = rcta.org_id
AND hcsua.org_id = hcas.org_id
AND hz.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsua.cust_acct_site_id
AND site_use_code = ‘BILL_TO’
AND hcsua.tax_code IS NOT NULL) tax_code,
rcta.trx_number, trx_date,
— rsu.cust_acct_site_id, hl.city, hl.state, hl.country, hl.postal_code,
rctla.description description,
rctla.quantity_invoiced,
rctla.unit_selling_price,
rctla.extended_amount line_amt, rct_tax.extended_amount tax_line_amt,
rct_tax.tax_rate, — , hps.Party_id –, (select ) —,
rctla.extended_amount+rct_tax.extended_amount total_price,
le_info.address, le_info.entity_name, le_info.registration_number
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
ar_customers rc,
hz_cust_site_uses_all rsu,
hz_cust_acct_sites_all hcsa,
hz_party_sites hps,
hz_locations hl,
ra_customer_trx_lines_all rct_tax,
(SELECT distinct
po_hr_location.get_formatted_address(hr_loc.location_id) address,
xep.name entity_name,
reg.registration_number ,
xep.legal_entity_id
FROM
xle_entity_profiles xep,
xle_registrations reg, —
hr_operating_units hou,
— hr_all_organization_units hr_ou,
hr_all_organization_units_tl hr_outl,
hr_locations_all hr_loc,
gl_legal_entities_bsvs glev
WHERE
1=1
AND xep.transacting_entity_flag = ‘Y’
AND xep.legal_entity_id = reg.source_id
AND reg.source_table = ‘XLE_ENTITY_PROFILES’
AND reg.identifying_flag = ‘Y’
AND xep.legal_entity_id = hou.default_legal_context_id
AND reg.location_id = hr_loc.location_id
AND xep.legal_entity_id = glev.legal_entity_id
AND hr_outl.organization_id = hou.organization_id) le_info

WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND rc.customer_id = rcta.bill_to_customer_id
AND rcta.customer_trx_id = :p_customer_trx_id
AND rsu.cust_acct_site_id = hcsa.cust_acct_site_id
— AND TRUNC (rcta.trx_date) BETWEEN ’01-DEC-2017′ AND ’31-DEC-2017′
AND rcta.bill_to_site_use_id = rsu.site_use_id
AND hcsa.party_site_id = hps.party_site_id
AND rsu.site_use_code = ‘BILL_TO’
AND hps.location_id = hl.location_id
AND rctla.line_type = ‘LINE’
and le_info.legal_entity_id = rcta.legal_entity_id
AND rct_tax.link_to_cust_trx_line_id = rctla.customer_trx_line_id;

Advertisements

About pacesettergraam

A good and realistic person

Posted on December 17, 2017, in Oracle, oracle apps, Technical and tagged , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: