-- Transaction balances with transaction types having open receivables set to ‘N’ and
-- post to gl set to ‘Y’ would reflect in GL Account balance, but not in Aging, since the
-- balances are not maintained in payment shcedules for these transactions
-- To identify the amount difference caused by these kind of transactions, please, use the
-- below query
select sum(acctd_amt_dr-acctd_amt_cr) gl_no_aging
from (
select c.code_combination_id,
DECODE(gld.account_class,'REC',decode(sign(gld.acctd_amount), -1 ,0, gld.acctd_amount),
'REV',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
'TAX',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
'ROUND',decode(sign(gld.acctd_amount), -1,abs(gld.acctd_amount),0),
'UNEARN',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
'FREIGHT',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
'UNBILL',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),0) acctd_amt_dr,
DECODE(gld.account_class, 'REC',decode(sign(gld.acctd_amount), -1 ,abs(gld.acctd_amount),0),
'REV',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
'TAX',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
'ROUND',decode(sign(gld.acctd_amount), -1,0,gld.acctd_amount),
'UNEARN',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
'FREIGHT',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
'UNBILL',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),0) acctd_amt_cr
from ra_cust_trx_line_gl_dist_all gld
,ra_customer_trx_all ct
,ra_cust_trx_types_all ctt
,xla_distribution_links lk
,xla_ae_lines ae
,gl_code_combinations c
where ct.cust_trx_type_id = ctt.cust_trx_type_id
and ct.customer_trx_id = gld.customer_trx_id
and gld.account_class = 'REC'
and gld.account_set_flag = 'N'
and gld.latest_rec_flag = 'Y'
and ctt.accounting_affect_flag = 'N'
and ctt.post_to_gl = 'Y'
and ct.org_id = ctt.org_id
and ct.org_id = gld.org_id
and ct.set_of_books_id = &ledger_id
and gld.gl_date <= to_date('&as_of_date','DD-MON-YYYY')
and gld.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1(+)
and lk.source_distribution_type(+) = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and lk.application_id(+) = 222
and ae.application_id(+) = 222
and lk.ae_header_id = ae.ae_header_id(+)
and lk.ae_line_num = ae.ae_line_num(+)
and decode(lk.accounting_line_code, '', 'Y',
'CM_EXCH_GAIN_LOSS', 'N',
'AUTO_GEN_GAIN_LOSS', 'N', 'Y') = 'Y'
and decode(ae.ledger_id,'',decode(gld.posting_control_id,-3,-999999,gld.code_combination_id),gld.set_of_books_id,ae.code_combination_id,-999999)= c.code_combination_id
and c.code_combination_id = &code_combination_id
);
-- post to gl set to ‘Y’ would reflect in GL Account balance, but not in Aging, since the
-- balances are not maintained in payment shcedules for these transactions
-- To identify the amount difference caused by these kind of transactions, please, use the
-- below query
select sum(acctd_amt_dr-acctd_amt_cr) gl_no_aging
from (
select c.code_combination_id,
DECODE(gld.account_class,'REC',decode(sign(gld.acctd_amount), -1 ,0, gld.acctd_amount),
'REV',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
'TAX',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
'ROUND',decode(sign(gld.acctd_amount), -1,abs(gld.acctd_amount),0),
'UNEARN',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
'FREIGHT',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
'UNBILL',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),0) acctd_amt_dr,
DECODE(gld.account_class, 'REC',decode(sign(gld.acctd_amount), -1 ,abs(gld.acctd_amount),0),
'REV',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
'TAX',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
'ROUND',decode(sign(gld.acctd_amount), -1,0,gld.acctd_amount),
'UNEARN',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
'FREIGHT',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
'UNBILL',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),0) acctd_amt_cr
from ra_cust_trx_line_gl_dist_all gld
,ra_customer_trx_all ct
,ra_cust_trx_types_all ctt
,xla_distribution_links lk
,xla_ae_lines ae
,gl_code_combinations c
where ct.cust_trx_type_id = ctt.cust_trx_type_id
and ct.customer_trx_id = gld.customer_trx_id
and gld.account_class = 'REC'
and gld.account_set_flag = 'N'
and gld.latest_rec_flag = 'Y'
and ctt.accounting_affect_flag = 'N'
and ctt.post_to_gl = 'Y'
and ct.org_id = ctt.org_id
and ct.org_id = gld.org_id
and ct.set_of_books_id = &ledger_id
and gld.gl_date <= to_date('&as_of_date','DD-MON-YYYY')
and gld.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1(+)
and lk.source_distribution_type(+) = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and lk.application_id(+) = 222
and ae.application_id(+) = 222
and lk.ae_header_id = ae.ae_header_id(+)
and lk.ae_line_num = ae.ae_line_num(+)
and decode(lk.accounting_line_code, '', 'Y',
'CM_EXCH_GAIN_LOSS', 'N',
'AUTO_GEN_GAIN_LOSS', 'N', 'Y') = 'Y'
and decode(ae.ledger_id,'',decode(gld.posting_control_id,-3,-999999,gld.code_combination_id),gld.set_of_books_id,ae.code_combination_id,-999999)= c.code_combination_id
and c.code_combination_id = &code_combination_id
);
No comments:
Post a Comment