Introduction
This Post is about to AR Transactions Header with GST Query.
SELECT DISTINCT rcta.trx_number,
rcta.ct_reference,
lep.legal_entity_id,
lep.NAME legal_entity,
rcta.trx_date invoice_date,
–Bill to location details
hp.party_name bill_cust_name,
hcs1.LOCATION bill_to_loc,
RTRIM (hrl.address_line_1, ‘,’) Billadd1,
RTRIM (hrl.address_line_2, ‘,’) Billadd2,
RTRIM (hrl.address_line_3, ‘,’) Billadd3,
RTRIM (hrl.loc_information15, ‘,’) Billadd4,
RTRIM (hrl.loc_information16, ‘,’) Billadd5,
–From Organization Details–
DECODE (hl.address1, NULL, NULL, hl.address1 || ‘, ‘) Add1,
DECODE (hl.address2, NULL, NULL, hl.address2 || ‘, ‘) Add2,
DECODE (hl.address3, NULL, NULL, hl.address3 || ‘, ‘) Add3,
DECODE (hl.city, NULL, NULL, hl.city || ‘, ‘) city,
RTRIM (hl.state) state,
DECODE (hl.postal_code,NULL, NULL,hl.postal_code || ‘, ‘) pcode
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctl,
hz_cust_site_uses_all hcs1,
hz_cust_acct_sites_all hcs,
hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hcc,
hz_locations hl,
hr_locations hrl,
jai_tax_lines_all jl,
xle_entity_profiles lep,
org_organization_definitions ood
WHERE 1 = 1
AND rcta.customer_trx_id = rctl.customer_trx_id
AND rcta.trx_number = NVL (:trx_num, rcta.trx_number)
AND rcta.bill_to_customer_id = hcc.cust_account_id
AND hcc.account_number = NVL (:p_cust_no, hcc.account_number)
AND hps.location_id = hl.location_id
AND rcta.ship_to_site_use_id=HCS1.site_use_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcs.party_site_id
AND hcs.cust_acct_site_id = hcs1.cust_acct_site_id
AND hcc.party_id = hp.party_id
AND rctl.customer_trx_line_id = jl.trx_line_id
AND rcta.customer_trx_id = jl.trx_id
AND jl.first_party_primary_reg_num =
NVL (:gst_num, jl.first_party_primary_reg_num)
AND hrl.inventory_organization_id = ood.organization_id
AND hrl.location_id = jl.location_id
AND jl.entity_code = ‘TRANSACTIONS’
AND lep.legal_entity_id(+) = ood.legal_entity
AND lep.NAME = NVL (:entity_name, lep.NAME)
AND jl.organization_id = ood.organization_id
What we expect in the script.
This script helps us to comprehend AR Transactions Header with GST Query. Couple of tables which is being used in the scripts are ra_customer_trx_all rcta,ra_customer_trx_lines_all rctl,hz_cust_site_uses_all hcs1,hz_cust_acct_sites_all hcs,hz_parties hp,hz_party_sites hps,hz_cust_accounts hcc,hz_locations hl,hr_locations hrl,jai_tax_lines_all etc.
Summary
This Post described the script to review detailed information about AR Transactions Header with GST Query in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
