The below is the query for AR Transaction Register details at Distribution Level
SELECT RCTL.CUSTOMER_TRX_line_ID,
hou.name ou_name,
c.account_number “Customer Number”,
(
SELECT
party_name
FROM
apps.hz_parties
WHERE
party_id = c.party_id
) customer_name,
d.party_site_number “PARTY_SITE_NUMBER”,
rct.trx_number “Invoice Number”,
rctl.line_number,
rctl.inventory_item_id,
rctl.description,
rct.trx_date “Invoice Date”,
rtt.name “Activity Class”,
rct.creation_date “Invoice Creation Date”,
gld.amount “Transaction Amount”,
rct.invoice_currency_code,
gld.gl_date,
cc.concatenated_segments,
gld.account_class
FROM
apps.ra_customer_trx_lines_all rctl,
apps.ra_customer_trx_all rct,
apps.ra_cust_trx_line_gl_dist_all gld,
apps.hz_party_sites d,
apps.hz_cust_accounts c,
apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_site_uses_all hcsu,
apps.ra_cust_trx_types_all rtt,
apps.hr_operating_units hou,
apps.gl_code_combinations_kfv cc
WHERE
rct.cust_trx_type_id = rtt.cust_trx_type_id
AND hou.organization_id = rct.org_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND d.party_id = c.party_id
AND c.cust_account_id = rct.bill_to_customer_id
AND d.party_site_id (+) = hcas.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_id = rct.bill_to_site_use_id
and GLD.customer_trx_id=RCTL.CUSTOMER_TRX_ID
and GLD.customer_trx_line_id=RCTL.CUSTOMER_TRX_line_ID
and cc.code_combination_id=gld.code_combination_id
and gld.gl_date is not null
and rct.org_id = :org_id
AND rctl.line_type = ‘LINE’
GROUP BY
hou.name,
rct.invoice_currency_code,
d.party_site_number,
c.party_id,
c.account_number,
rct.trx_number,
rct.trx_date,
rtt.name,
rct.creation_date,
rctl.line_number,
rctl.description,
rctl.inventory_item_id,
gld.gl_date,
gld.amount,
cc.concatenated_segments,
gld.account_class,
RCTL.CUSTOMER_TRX_line_ID
ORDER BY
rct.trx_number,
rctl.line_number
