Query for AR Transactions Header with GST

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
  • January 8, 2018 | 17 views
  • Comments