SELECT
customer,
account_number,
invoice_no,
line_no,
accounting_rule_name,
functional_amount
FROM
(
SELECT
replace(hp.party_name,’,’,’ ‘) customer,
hca.account_number,
rcl.customer_trx_line_id,
aps.creation_date creation_date,
rct.trx_date invoice_date,
rct.invoicing_rule_id invoice_rule,
rct.trx_number invoice_no,
rcl.line_number line_no,
replace(rcl.line_type,’,’,’ ‘) line_type,
replace(rcl.description,’,’,’ ‘) description,
rcl.quantity_invoiced qty,
rcl.unit_selling_price price,
ra.name accounting_rule_name,
rcl.accounting_rule_duration duration,
TO_CHAR(rcl.rule_start_date,’DD-MON-YYYY’) start1,
rcl.extended_amount amount,
round(rcl.extended_amount * nvl(aps.exchange_rate,1),2) functional_amount,
replace(rctd.account_class,’,’,’ ‘) class
FROM
hz_parties hp,
ra_rules ra,
hz_cust_accounts hca,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rcl,
ar_payment_schedules_all aps,
ra_cust_trx_line_gl_dist_all rctd
WHERE
hp.party_id = hca.party_id
AND ra.rule_id = rcl.accounting_rule_id
AND hca.cust_account_id = rct.bill_to_customer_id
AND aps.customer_trx_id = rct.customer_trx_id
AND rct.customer_trx_id = rcl.customer_trx_id
AND rct.org_id =:p_org_id
AND rct.invoicing_rule_id =-2
AND rcl.line_type = ‘LINE’
AND rctd.account_class = ‘UNEARN’
AND rctd.customer_trx_line_id = rcl.customer_trx_line_id
AND rcl.extended_amount <> 0
AND rcl.rule_start_date > rct.trx_date
AND TRUNC(rct.trx_date) BETWEEN (:P_FROM_DATE) AND (:P_TO_DATE)
GROUP BY
hp.party_name,
hca.account_number,
aps.creation_date,
rct.trx_date,
rct.trx_number,
rct.invoicing_rule_id,
rcl.line_number,
rcl.line_type,
rcl.description,
rcl.quantity_invoiced,
rcl.unit_selling_price,
ra.name,
rcl.accounting_rule_duration,
rcl.rule_start_date,
rcl.extended_amount,
nvl(aps.exchange_rate,1),
rctd.account_class,
rcl.customer_trx_line_id
) a
ORDER BY
a.creation_date,
a.invoice_no,
a.line_no;
Recent Posts