Query to get Unearned Revenue Error details
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 to_char (rct.trx_date, 'MON-RRRR') = :p_curr_period
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
By
Eswaramoorthi M
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 to_char (rct.trx_date, 'MON-RRRR') = :p_curr_period
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
By
Eswaramoorthi M
No comments:
Post a Comment