/*Query to get Oracle Receivables Details
==========================================*/
SELECT rcta.trx_number "INVOICE NUMBER",
rctta.name "TRANSACTION TYPE",
rcta.trx_date "INVOICE DATE",
apsa.due_date "DUE DATE",
apsa.invoice_currency_code "CURRENCY",
apsa.amount_due_original "AMOUNT",
apsa.amount_due_remaining "AMOUNT DUE",
hp_b.party_name "BILL TO CUSTOMER NAME",
hcsua_b.site_use_id "BILL TO SITE USE ID",
hl_b.address1,
hl_b.address2,
hl_b.address3,
hl_b.address4,
hl_b.city,
hl_b.state,
hl_b.postal_code,
hp_s.party_name "SHIP TO CUSTOMER NAME",
hcsua_s.site_use_id "SHIP TO SITE USE ID",
hl_s.address1,
hl_s.address2,
hl_s.address3,
hl_s.address4,
hl_s.city,
hl_s.state,
hl_s.postal_code
FROM RA_TERMS RT,
RA_CUSTOMER_TRX_ALL RCTA,
--RA_CUSTOMER_TRX_LINES_ALL RCTLA,
RA_CUST_TRX_TYPES_ALL RCTTA,
HZ_CUST_ACCOUNTS_ALL HCAA,
HZ_CUST_ACCT_SITES_ALL HCASA_S,
HZ_CUST_SITE_USES_ALL HCSUA_S,
HZ_PARTIES HP_S,
HZ_PARTY_SITES HPS_S,
HZ_LOCATIONS HL_S,
HZ_CUST_ACCT_SITES_ALL HCASA_B,
HZ_CUST_SITE_USES_ALL HCSUA_B,
HZ_PARTIES HP_B,
HZ_PARTY_SITES HPS_B,
HZ_LOCATIONS HL_B,
AR_PAYMENT_SCHEDULES_ALL APSA,
HR_LEGAL_ENTITIES HLE
WHERE --RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID
RCTA.CUST_TRX_TYPE_ID = RCTTA.CUST_TRX_TYPE_ID
AND RCTA.BILL_TO_CUSTOMER_ID = HCAA.CUST_ACCOUNT_ID
AND RCTA.ORG_ID = RCTTA.ORG_ID
AND APSA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND APSA.CUST_TRX_TYPE_ID = RCTTA.CUST_TRX_TYPE_ID
AND RCTA.TERM_ID = RT.TERM_ID(+)
AND HCAA.PARTY_ID = HP_S.PARTY_ID
AND HCAA.PARTY_ID = HP_B.PARTY_ID
--SHIP TO ADDRESS LINKS
AND RCTA.SHIP_TO_SITE_USE_ID = HCSUA_S.SITE_USE_ID
AND HCSUA_S.CUST_ACCT_SITE_ID = HCASA_S.CUST_ACCT_SITE_ID
AND HCASA_S.PARTY_SITE_ID = HPS_S.PARTY_SITE_ID
AND HPS_S.PARTY_ID = HP_S.PARTY_ID
AND HPS_S.LOCATION_ID = HL_S.LOCATION_ID
--------BILL TO ADDRESS LINKS
AND RCTA.BILL_TO_SITE_USE_ID = HCSUA_B.SITE_USE_ID
AND HCSUA_B.CUST_ACCT_SITE_ID = HCASA_B.CUST_ACCT_SITE_ID
AND HCASA_B.PARTY_SITE_ID = HPS_B.PARTY_SITE_ID
AND HPS_B.PARTY_ID = HP_B.PARTY_ID
AND HPS_B.LOCATION_ID = HL_B.LOCATION_ID
AND HLE.ORGANIZATION_ID = RCTA.LEGAL_ENTITY_ID
AND RCTA.TRX_NUMBER ='12149'
