The below is the query for AR Transaction Register Summary Report at Line Level
SELECT
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”,
nvl(rctl.quantity_invoiced,rctl.quantity_credited) quantity_invoiced,
rctl.unit_selling_price unit_price,
SUM(nvl(rctl.quantity_invoiced,rctl.quantity_credited) * rctl.unit_selling_price) “Transaction Amount”,
rct.invoice_currency_code
FROM
apps.ra_customer_trx_lines_all rctl,
apps.ra_customer_trx_all rct,
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
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 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.quantity_invoiced,
rctl.unit_selling_price,
rctl.line_number,
rctl.description,
rctl.inventory_item_id,
rctl.quantity_credited
ORDER BY
rct.trx_number,
rctl.line_number
