AR TO TCA Tracking

 SELECT *

FROM   ra_customer_trx_all rct
WHERE  rct.trx_number = Nvl(:P_TRX_NUM, '263')
==============================================================================================================================================================================

SELECT *
FROM   hz_cust_accounts hca
WHERE  hca.cust_account_id IN (SELECT rct.bill_to_customer_id
                               FROM   ra_customer_trx_all rct
                               WHERE  rct.trx_number = Nvl(:P_TRX_NUM, '263'))
==============================================================================================================================================================================

SELECT *
FROM   hz_parties hp
WHERE  hp.party_id IN (SELECT hca.party_id
                       FROM   hz_cust_accounts hca
                       WHERE  hca.cust_account_id IN
                              (SELECT rct.bill_to_customer_id
                               FROM   ra_customer_trx_all rct
                               WHERE
                              rct.trx_number = Nvl(:P_TRX_NUM, '263')))
==============================================================================================================================================================================

SELECT *
FROM   hz_party_sites HPS
WHERE  hps.party_id IN (SELECT hca.party_id
                        FROM   hz_cust_accounts hca
                        WHERE  hca.cust_account_id IN
                               (SELECT rct.bill_to_customer_id
                                FROM   ra_customer_trx_all rct
                                WHERE
                               rct.trx_number = Nvl(:P_TRX_NUM, '263')))
==============================================================================================================================================================================

SELECT *
FROM   hz_locations hl
WHERE  hl.location_id IN (SELECT hps.location_id
                          FROM   hz_party_sites HPS
                          WHERE  hps.party_id IN (SELECT hca.party_id
                                                  FROM   hz_cust_accounts hca
                                                  WHERE  hca.cust_account_id IN
                                 (SELECT rct.bill_to_customer_id
                                  FROM   ra_customer_trx_all rct
                                  WHERE
                                 rct.trx_number = Nvl(:P_TRX_NUM, '263'))))
==============================================================================================================================================================================

SELECT *
FROM   hz_cust_acct_sites_all hcasa
WHERE  hcasa.cust_account_id IN (SELECT hca.cust_account_id
                                 FROM   hz_cust_accounts hca
                                 WHERE  hca.cust_account_id IN
                                        (SELECT rct.bill_to_customer_id
                                         FROM   ra_customer_trx_all rct
                                         WHERE  rct.trx_number =
                                                Nvl(:P_TRX_NUM, '263')
                                        ))
==============================================================================================================================================================================

SELECT *
FROM   hz_cust_site_uses_all HCSU
WHERE  hcsu.cust_acct_site_id IN (SELECT hcasa.cust_acct_site_id
                                  FROM   hz_cust_acct_sites_all hcasa
                                  WHERE  hcasa.cust_account_id IN
                                         (SELECT hca.cust_account_id
                                          FROM   hz_cust_accounts hca
                                          WHERE  hca.cust_account_id IN
                                                 (SELECT rct.bill_to_customer_id
                                                  FROM   ra_customer_trx_all rct
                                                  WHERE  rct.trx_number =
                                                         Nvl(:P_TRX_NUM, '263')
                                                 )))
==============================================================================================================================================================================

SELECT *
FROM   hz_customer_profiles_f hcp
WHERE  hcp.cust_account_id IN (SELECT hca.cust_account_id
                               FROM   hz_cust_accounts hca
                               WHERE  hca.cust_account_id IN
                                      (SELECT rct.bill_to_customer_id
                                       FROM   ra_customer_trx_all rct
                                       WHERE  rct.trx_number =
                                              Nvl(:P_TRX_NUM, '263')))
==============================================================================================================================================================================