[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select flex_value,
summary_flag
from fnd_flex_value_children_v
where flex_value_set_id = p_value_set_id
and parent_flex_value = c_value;
parent.DELETE(i);
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_coa_id
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_coa_id
FROM gl_sets_of_books sob,
ar_system_parameters sysparam
WHERE sob.set_of_books_id = sysparam.set_of_books_id
AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
select substrb(hou.name,1,60)
into l_organization
from hr_organization_units hou
where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
select meaning
into l_organization
from ar_lookups
where lookup_code ='ALL' and lookup_type ='ALL';
SELECT fcav.application_column_name, flex_value_set_id
INTO l_natural_segment_col , l_flex_value_set_id
FROM fnd_segment_attribute_values fcav,
fnd_id_flex_segments fifs
WHERE fcav.application_id = 101
AND fcav.id_flex_code = 'GL#'
AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
AND fcav.attribute_value = 'Y'
AND fcav.segment_attribute_type = 'GL_ACCOUNT'
AND fifs.application_id = fcav.application_id
AND fifs.id_flex_code = fcav.id_flex_code
AND fifs.id_flex_num = fcav.id_flex_num
AND fcav.application_column_name = fifs.application_column_name;
select meaning
into l_receivable_mode_meaning
from fnd_lookups
where lookup_type = 'YES_NO'
and lookup_code = 'Y';
select meaning
into l_receivable_mode_meaning
from fnd_lookups
where lookup_type = 'YES_NO'
and lookup_code = 'N';
select meaning
into l_status_meaning
from ar_lookups
where lookup_type = 'POSTED_STATUS'
and lookup_code = arp_recon_rep.var_tname.g_posting_status;
select trx.invoice_currency_code,
type.name,
adj.posting_control_id,
trx.trx_number,
to_char(pay.due_date,''YYYY-MM-DD'') due_date,
to_char(pay.gl_date,''YYYY-MM-DD'') trx_gl_date,
to_char(adj.gl_date,''YYYY-MM-DD'') adj_gl_date,
adj.adjustment_number,
decode(adj.adjustment_type,''C'', look.meaning,
decode(rec.type, ''FINCHRG'',''Finance'',''Adjustment'')) adj_class,
rec.name activity,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(trx.trx_date,''YYYY-MM-DD'') trx_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
gc.code_combination_id account_code_combination_id,
l_cat.meaning category,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from hz_cust_accounts cust,
hz_parties party,
ra_cust_trx_types type,
gl_code_combinations gc,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' pay,
ar_receivables_trx rec,
'||arp_recon_rep.var_tname.l_ra_customer_trx_all||' trx,
'||arp_recon_rep.var_tname.l_ar_adjustments_all||' adj,
'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups look,
ar_lookups l_cat
where trx.complete_flag = ''Y''
and cust.cust_account_id = trx.bill_to_customer_id
and cust.party_id = party.party_id
and trx.set_of_books_id = arp_recon_rep.get_set_of_books_id()
and trx.cust_trx_type_id = type.cust_trx_type_id
and trx.customer_trx_id = pay.customer_trx_id
and pay.payment_schedule_id = adj.payment_schedule_id
and nvl(adj.status, ''A'') = ''A''
and type.type in (''INV'',''DEP'',''GUAR'',''CM'',''DM'',''CB'')
and nvl(type.org_id,-99) = nvl(trx.org_id,-99)
and look.lookup_type = ''INV/CM''
and look.lookup_code = type.type
and nvl(adj.postable,''Y'') = ''Y''
and adj.receivables_trx_id is not null
and adj.receivables_trx_id <> -15
and adj.receivables_trx_id = rec.receivables_trx_id
and nvl(rec.org_id,-99) = nvl(trx.org_id,-99)
and ard.source_id = adj.adjustment_id
and ard.source_table = ''ADJ''
and gc.code_combination_id = ard.code_combination_id
and gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
and l_cat.lookup_code = (''ADJ_''||ard.source_type)
'||l_adj_org_where||'
'||l_ard_org_where||'
'||l_rec_org_where||'
'||l_trx_org_where||'
'||l_pay_org_where||'
'||l_type_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_where||'
'||l_posting_status_where||'
order by company, category, account,adj_gl_date, adjustment_number' ;
select to_char(sysdate,'YYYY-MM-DD')
into l_report_date
from dual;
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_coa_id
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_coa_id
FROM gl_sets_of_books sob,
ar_system_parameters sysparam
WHERE sob.set_of_books_id = sysparam.set_of_books_id
AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
select substrb(hou.name,1,60)
into l_organization
from hr_organization_units hou
where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
select meaning
into l_organization
from ar_lookups
where lookup_code ='ALL' and lookup_type ='ALL';
SELECT fcav.application_column_name, flex_value_set_id
INTO l_natural_segment_col , l_flex_value_set_id
FROM fnd_segment_attribute_values fcav,
fnd_id_flex_segments fifs
WHERE fcav.application_id = 101
AND fcav.id_flex_code = 'GL#'
AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
AND fcav.attribute_value = 'Y'
AND fcav.segment_attribute_type = 'GL_ACCOUNT'
AND fifs.application_id = fcav.application_id
AND fifs.id_flex_code = fcav.id_flex_code
AND fifs.id_flex_num = fcav.id_flex_num
AND fcav.application_column_name = fifs.application_column_name;
select meaning
into l_receivable_mode_meaning
from fnd_lookups
where lookup_type = 'YES_NO'
and lookup_code = 'Y';
select meaning
into l_receivable_mode_meaning
from fnd_lookups
where lookup_type = 'YES_NO'
and lookup_code = 'N';
select meaning
into l_status_meaning
from ar_lookups
where lookup_type = 'POSTED_STATUS'
and lookup_code = arp_recon_rep.var_tname.g_posting_status;
l_xml_build_sql := '(select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(hd.accounting_date,''YYYY-MM-DD'') app_gl_date,
to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(lk.unrounded_entered_dr,0) entered_debit,
nvl(lk.unrounded_entered_cr,0) entered_credit,
nvl(lk.unrounded_accounted_dr,0) acctd_debit,
nvl(lk.unrounded_accounted_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
/* 7008877 */
ard.line_id,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard,
xla_distribution_links lk,
xla_ae_lines ae,
xla_ae_headers hd,
ar_lookups l_cat
where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
AND nvl(ra.confirmed_flag,''Y'') = ''Y''
AND ra.status in (''UNAPP'',''ACC'',''UNID'',''OTHER ACC'')
AND ps.cash_receipt_id = ra.cash_receipt_id
AND ps.class = ''PMT''
'||l_gl_date_closed_where||'
AND cr.cash_receipt_id = ra.cash_receipt_id
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND ra.set_of_books_id = hd.ledger_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ae.code_combination_id
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND ard.source_id = ra.receivable_application_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND ard.source_table = ''RA''
AND crh.first_posted_record_flag = ''Y''
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND l_cat.lookup_code = ''TRADE_''||decode (ae.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)
AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
AND ae.application_id = 222
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND hd.event_type_code <> ''MANUAL''
AND ra.event_id IS NOT NULL
'||l_ra_org_where||'
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_where||'
'||l_posting_status_where||'
UNION ALL
select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
/* 7008877 */
ard.line_id,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups l_cat
where
ra.status IN (''ACC'', ''OTHER ACC'')
AND ps.cash_receipt_id = ra.cash_receipt_id
AND ps.class = ''PMT''
'||l_gl_date_closed_where||'
AND cr.cash_receipt_id = ra.cash_receipt_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ard.code_combination_id
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND ard.source_id = ra.receivable_application_id
AND ard.source_table = ''RA''
AND crh.first_posted_record_flag = ''Y''
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND nvl(ra.confirmed_flag,''Y'') = ''Y''
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND l_cat.lookup_code = ''TRADE_''||ard.source_type
AND ra.posting_control_id <> - 3
AND ra.event_id IS NULL
'||l_ra_org_where||'
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_ard_where||'
'||l_posting_status_nul_where||'
UNION ALL
select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
/* 7008877 */
ard.line_id,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard,
ar_lookups l_cat
where ra.status IN (''UNAPP'', ''UNID'')
AND EXISTS
(SELECT NULL
FROM xla_distribution_links lk
WHERE lk.source_distribution_id_num_1 = ard.line_id
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
AND ps.class = ''PMT''
'||l_gl_date_closed_where||'
AND crh.batch_id = bat.batch_id(+)
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND ard.source_id = ra.receivable_application_id
AND ps.cash_receipt_id = ra.cash_receipt_id
AND gc.code_combination_id = ard.code_combination_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = ra.cash_receipt_id
AND crh.first_posted_record_flag = ''Y''
AND ard.source_table = ''RA''
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND l_cat.lookup_code = ''TRADE_''||ard.source_type
AND ra.posting_control_id <> - 3
AND nvl(ra.confirmed_flag,''Y'') = ''Y''
AND ra.event_id IS NULL
'||l_ra_org_where||'
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_ard_where||'
'||l_posting_status_nul_where||')';
(select /*+ leading(crh) index(crh AR_CASH_RECEIPT_HISTORY_N2) index(ps AR_PAYMENT_SCHEDULES_U2)*/
cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(hd.accounting_date,''YYYY-MM-DD'') app_gl_date,
to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(lk.unrounded_entered_dr,0) entered_debit,
nvl(lk.unrounded_entered_cr,0) entered_credit,
nvl(lk.unrounded_accounted_dr,0) acctd_debit,
nvl(lk.unrounded_accounted_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
/* 7008877 */
ard.line_id,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
xla_distribution_links lk,
xla_ae_lines ae,
xla_ae_headers hd,
ar_lookups l_cat
where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
--AND ps.cash_receipt_id = ard.cash_receipt_id
AND ps.class = ''PMT''
'||l_gl_date_closed_where||'
AND cr.cash_receipt_id = ps.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND crh.batch_id = bat.batch_id(+)
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ae.code_combination_id
AND crh.cash_receipt_id = ps.cash_receipt_id
AND ard.source_id = crh.cash_receipt_history_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND ard.line_id = lk.source_distribution_id_num_1
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND l_cat.lookup_code = ''TRADE_''||decode (ae.accounting_class_code, ''UNAPP'', ''UNAPP'', ard.source_type)
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND ae.application_id = 222
AND decode (ae.accounting_class_code, ''UNAPP'', ''UNAPP'', ard.source_type) = ''UNAPP''
AND hd.event_type_code <> ''MANUAL''
AND ard.source_table = ''CRH''
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.set_of_books_id = hd.ledger_id
AND crh.event_id IS NOT NULL
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND crh.first_posted_record_flag = ''Y''
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_crh_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_posting_status_crh_where||'
UNION ALL
select /*+ leading(crh) index(crh AR_CASH_RECEIPT_HISTORY_N2) index(ps AR_PAYMENT_SCHEDULES_U2)*/
cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(crh.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
/* 7008877 */
ard.line_id,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups l_cat
where ps.class = ''PMT'' --ps.cash_receipt_id = ard.cash_receipt_id
'||l_gl_date_closed_where||'
AND cr.cash_receipt_id = ps.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ard.code_combination_id
--AND ard.cash_receipt_id = ps.cash_receipt_id
AND ard.source_id = crh.cash_receipt_history_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND ard.source_table = ''CRH''
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND l_cat.lookup_code = ''TRADE_''||ard.source_type
AND crh.event_id IS NULL
AND crh.posting_control_id <> - 3
AND ard.source_type = ''UNAPP''
AND crh.first_posted_record_flag = ''Y''
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_crh_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_posting_status_crh_where||')';
(select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(hd.accounting_date,''YYYY-MM-DD'') app_gl_date,
to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(lk.unrounded_entered_dr,0) entered_debit,
nvl(lk.unrounded_entered_cr,0) entered_credit,
nvl(lk.unrounded_accounted_dr,0) acctd_debit,
nvl(lk.unrounded_accounted_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
/* 7008877 */
ard.line_id,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_receivable_applications ra,
xla_distribution_links lk,
xla_ae_lines ae,
xla_ae_headers hd,
ar_lookups l_cat
where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'',''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
--AND ps.cash_receipt_id = ard.cash_receipt_id
AND ps.class = ''PMT''
'||l_gl_date_closed_where||'
AND cr.cash_receipt_id = ps.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ae.code_combination_id
AND ra.cash_receipt_id = ps.cash_receipt_id
AND ard.source_id = ra.receivable_application_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND ra.set_of_books_id = hd.ledger_id
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND hd.event_type_code <> ''MANUAL''
AND crh.first_posted_record_flag = ''Y''
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND lk.application_id = 222
AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
AND ae.application_id = 222
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND l_cat.lookup_code = ''TRADE_''||decode (ae.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)
AND ard.source_table = ''RA''
AND ra.event_id IS NOT NULL
AND decode (ae.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type) = ''UNAPP''
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_posting_status_where||'
UNION ALL
select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
/* 7008877 */
ard.line_id,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_receivable_applications ra,
ar_lookups l_cat
where ra.status IN (''APP'', ''ACC'', ''ACTIVITY'', ''OTHER ACC'')
--AND ps.cash_receipt_id = ard.cash_receipt_id
AND ps.class = ''PMT''
'||l_gl_date_closed_where||'
AND cr.cash_receipt_id = ps.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ard.code_combination_id
--AND ard.cash_receipt_id = ps.cash_receipt_id
AND ard.source_id = ra.receivable_application_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND l_cat.lookup_code = ''TRADE_''||ard.source_type
AND ard.source_type = ''UNAPP''
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND crh.first_posted_record_flag = ''Y''
AND ard.source_table = ''RA''
AND ra.event_id IS NULL
AND ra.posting_control_id <> - 3
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_posting_status_nul_where||'
UNION ALL
select /*+ leading(ra) index(ra AR_RECEIVABLE_APPLICATIONS_N6) index(ps AR_PAYMENT_SCHEDULES_U2)*/
cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(ps.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
/* 7008877 */
ard.line_id,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_receivable_applications ra,
ar_lookups l_cat
where ra.status IN (''UNAPP'', ''UNID'')
--AND ps.cash_receipt_id = ard.cash_receipt_id
AND ps.class = ''PMT''
'||l_gl_date_closed_where||'
AND cr.cash_receipt_id = ps.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ard.code_combination_id
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
--AND ard.cash_receipt_id = ps.cash_receipt_id
AND ard.source_id = ra.receivable_application_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND EXISTS
(SELECT NULL
FROM xla_distribution_links lk
WHERE lk.source_distribution_id_num_1 = ard.line_id
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND crh.first_posted_record_flag = ''Y''
AND ard.source_table = ''RA''
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND l_cat.lookup_code = ''TRADE_''||ard.source_type
AND ard.source_type = ''UNAPP''
AND ra.event_id IS NULL
AND ra.posting_control_id <> - 3
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_posting_status_nul_where||')';
SELECT value INTO l_nls_numeric_char
FROM v$NLS_PARAMETERS
WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
select to_char(sysdate,'YYYY-MM-DD')
into l_report_date
from dual;
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_coa_id
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_coa_id
FROM gl_sets_of_books sob,
ar_system_parameters sysparam
WHERE sob.set_of_books_id = sysparam.set_of_books_id
AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
select substrb(hou.name,1,60)
into l_organization
from hr_organization_units hou
where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
select meaning
into l_organization
from ar_lookups
where lookup_code ='ALL' and lookup_type ='ALL';
SELECT fcav.application_column_name, flex_value_set_id
INTO l_natural_segment_col , l_flex_value_set_id
FROM fnd_segment_attribute_values fcav,
fnd_id_flex_segments fifs
WHERE fcav.application_id = 101
AND fcav.id_flex_code = 'GL#'
AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
AND fcav.attribute_value = 'Y'
AND fcav.segment_attribute_type = 'GL_ACCOUNT'
AND fifs.application_id = fcav.application_id
AND fifs.id_flex_code = fcav.id_flex_code
AND fifs.id_flex_num = fcav.id_flex_num
AND fcav.application_column_name = fifs.application_column_name;
and not exists (select line_id
from ar_distributions_all ard1, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard1
xla_distribution_links lk1,
xla_ae_lines ae1,
xla_ae_headers hd1
where
ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
and ard1.source_id = ra.receivable_application_id
and decode (ae1.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk1.unrounded_accounted_cr, 0) - nvl (lk1.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard1.source_type) = ''REC''
and ard1.source_table =''RA''
AND ard1.line_id = lk1.source_distribution_id_num_1
AND lk1.application_id = 222
AND lk1.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
AND ae1.application_id = 222
AND lk1.ae_header_id = ae1.ae_header_id
AND lk1.ae_line_num = ae1.ae_line_num
AND lk1.ae_header_id = hd1.ae_header_id
AND ra.set_of_books_id = hd1.ledger_id
AND hd1.event_type_code <> ''MANUAL''
'|| l_ard1_org_where || ')
and decode (ae.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type) in (''REMITTANCE'',''FACTOR'',''UNPAIDREC'')))';
and not exists (select line_id
from ar_distributions_all ard1 ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard1
where ard1.source_id = ra.receivable_application_id
and ard1.source_type = ''REC''
and ard1.source_table =''RA''
'|| l_ard1_org_where || ')
and ard.source_type in (''REMITTANCE'',''FACTOR'',''UNPAIDREC'')))';
select meaning
into l_receivable_mode_meaning
from fnd_lookups
where lookup_type = 'YES_NO'
and lookup_code = 'Y';
select meaning
into l_receivable_mode_meaning
from fnd_lookups
where lookup_type = 'YES_NO'
and lookup_code = 'N';
select meaning
into l_status_meaning
from ar_lookups
where lookup_type = 'POSTED_STATUS'
and lookup_code = arp_recon_rep.var_tname.g_posting_status;
l_xml_build_sql := 'select cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(lk.unrounded_entered_dr,0) entered_debit,
nvl(lk.unrounded_entered_cr,0) entered_credit,
nvl(lk.unrounded_accounted_dr,0) acctd_debit,
nvl(lk.unrounded_accounted_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
ps.trx_number trx_number,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
ard.currency_code currency_code,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
xla_distribution_links lk,
xla_ae_lines ae,
xla_ae_headers hd,
ar_lookups l_cat
where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
AND crh.batch_id = bat.batch_id(+)
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND cr.cash_receipt_id = ra.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND ard.line_id = lk.source_distribution_id_num_1
AND crh.cash_receipt_id = cr.cash_receipt_id
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND ra.set_of_books_id = hd.ledger_id
AND ps.payment_schedule_id = ra.applied_payment_schedule_id
AND gc.code_combination_id = ard.code_combination_id
AND ard.source_id = ra.receivable_application_id
AND ((ra.amount_applied_from IS NULL
and l_cat.lookup_code = (''TRADE_''||decode (ae.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)))
or( ra.amount_applied_from IS NOT NULL
and l_cat.lookup_code = (''CCURR_''||decode (ae.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)))
or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||decode (ae.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type))))
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND lk.application_id = 222
AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
AND ae.application_id = 222
AND nvl(ra.confirmed_flag,''Y'') = ''Y''
AND ra.status = ''APP''
AND crh.first_posted_record_flag = ''Y''
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND ard.source_table = ''RA''
AND hd.event_type_code <> ''MANUAL''
AND ra.event_id IS NOT NULL
AND cr.reversal_date IS NULL
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
'||l_ra_org_where||'
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_where||'
'||l_posting_status_where||'
UNION ALL
select cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
ps.trx_number trx_number,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
ard.currency_code currency_code,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups l_cat
where ra.status IN (''APP'',''ACC'', ''ACTIVITY'', ''OTHER ACC'')
AND cr.cash_receipt_id = ra.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND ps.payment_schedule_id = ra.applied_payment_schedule_id
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ard.code_combination_id
AND ard.source_id = ra.receivable_application_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND ((ra.amount_applied_from IS NULL
and l_cat.lookup_code = (''TRADE_''||ard.source_type))
or( ra.amount_applied_from IS NOT NULL
and l_cat.lookup_code = (''CCURR_''||ard.source_type))
or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||ard.source_type)))
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND ra.posting_control_id <> - 3
AND nvl(ra.confirmed_flag,''Y'') = ''Y''
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND crh.first_posted_record_flag = ''Y''
AND ra.event_id IS NULL
AND ard.source_table = ''RA''
AND ra.status = ''APP''
AND cr.reversal_date IS NULL
'||l_ra_org_where||'
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_ard_where||'
'||l_posting_status_nul_where||'
UNION ALL
select cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
ps.trx_number trx_number,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
ard.currency_code currency_code,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups l_cat
where ra.status IN (''UNAPP'', ''UNID'')
AND cr.cash_receipt_id = ra.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND ps.payment_schedule_id = ra.applied_payment_schedule_id
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ard.code_combination_id
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND ard.source_id = ra.receivable_application_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND ((ra.amount_applied_from IS NULL
and l_cat.lookup_code = (''TRADE_''||ard.source_type))
or( ra.amount_applied_from IS NOT NULL
and l_cat.lookup_code = (''CCURR_''||ard.source_type))
or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||ard.source_type)))
AND EXISTS
(SELECT NULL
FROM xla_distribution_links lk
WHERE lk.source_distribution_id_num_1 = ard.line_id
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND nvl(ra.confirmed_flag,''Y'') = ''Y''
AND cr.reversal_date IS NULL
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND crh.first_posted_record_flag = ''Y''
AND ra.status = ''APP''
AND ra.event_id IS NULL
AND ra.posting_control_id <> - 3
AND ard.source_table = ''RA''
'||l_ra_org_where||'
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_ard_where||'
'||l_posting_status_nul_where;
select cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(hd.accounting_date,''YYYY-MM-DD'') app_gl_date,
to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(lk.unrounded_entered_dr,0) entered_debit,
nvl(lk.unrounded_entered_cr,0) entered_credit,
nvl(lk.unrounded_accounted_dr,0) acctd_debit,
nvl(lk.unrounded_accounted_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
ps.trx_number trx_number,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
ard.currency_code currency_code,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
xla_distribution_links lk,
xla_ae_lines ae,
xla_ae_headers hd,
ar_lookups l_cat
where ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
AND cr.cash_receipt_id = ra.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND ps.payment_schedule_id = ra.applied_payment_schedule_id
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ard.code_combination_id
AND ard.source_id = ra.receivable_application_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND ((ra.amount_applied_from IS NULL
and l_cat.lookup_code = (''TRADE_''||decode (ae.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)))
or( ra.amount_applied_from IS NOT NULL
and l_cat.lookup_code = (''CCURR_''||decode (ae.accounting_class_code,''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type)))
or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||decode (ae.accounting_class_code, ''UNAPP''
, ''UNAPP'', ''EXCHANGE_GAIN_LOSS''
, decode (sign (nvl (lk.unrounded_accounted_cr, 0) - nvl (lk.unrounded_accounted_dr, 0)), - 1
, ''EXCH_LOSS'', ''EXCH_GAIN''), ard.source_type))))
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND ra.set_of_books_id = hd.ledger_id
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND hd.event_type_code <> ''MANUAL''
AND lk.application_id = 222
AND ra.event_id IS NOT NULL
AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
AND ae.application_id = 222
AND nvl(ra.confirmed_flag,''Y'') = ''Y''
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND crh.first_posted_record_flag = ''Y''
AND cr.reversal_date IS NOT NULL
AND ra.status = ''APP''
AND ard.source_table = ''RA''
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
'||l_ra_org_where||'
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_ard_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_where||'
'||l_posting_status_where||'
UNION ALL
select cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
ps.trx_number trx_number,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
ard.currency_code currency_code,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups l_cat
where ra.status IN (''APP'', ''ACC'', ''ACTIVITY'', ''OTHER ACC'')
AND cr.cash_receipt_id = ra.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND ps.payment_schedule_id = ra.applied_payment_schedule_id
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ard.code_combination_id
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND ard.source_id = ra.receivable_application_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND ((ra.amount_applied_from IS NULL
and l_cat.lookup_code = (''TRADE_''||ard.source_type))
or( ra.amount_applied_from IS NOT NULL
and l_cat.lookup_code = (''CCURR_''||ard.source_type))
or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||ard.source_type)))
AND ra.posting_control_id <> - 3
AND nvl(ra.confirmed_flag,''Y'') = ''Y''
AND crh.first_posted_record_flag = ''Y''
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND ard.source_table = ''RA''
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND ra.status = ''APP''
AND cr.reversal_date IS NOT NULL
AND ra.event_id IS NULL
'||l_ra_org_where||'
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_ard_where||'
'||l_posting_status_nul_where||'
UNION ALL
select cr.receipt_number payment_number,
arm.name payment_method,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(crh.gl_date,''YYYY-MM-DD'') payment_gl_date,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(cr.receipt_date,''YYYY-MM-DD'') receipt_date,
cr.currency_code receipt_currency,
ps.trx_number trx_number,
gc.code_combination_id,
bs.name receipt_source,
bat.name batch_name,
l_cat.meaning category,
ard.currency_code currency_code,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr,
ar_receipt_methods arm,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
'||arp_recon_rep.var_tname.l_ar_batches_all||' bat,
ar_batch_sources bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
ar_distributions_all ard, ---'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups l_cat
where ra.status IN (''UNAPP'', ''UNID'')
AND cr.cash_receipt_id = ra.cash_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.batch_id = bat.batch_id(+)
AND ps.payment_schedule_id = ra.applied_payment_schedule_id
AND bat.batch_source_id = bs.batch_source_id(+)
AND bat.org_id = bs.org_id(+)
AND gc.code_combination_id = ard.code_combination_id
AND ard.source_id = ra.receivable_application_id
AND cr.pay_from_customer = cust.cust_account_id(+)
AND cust.party_id = party.party_id(+)
AND ((ra.amount_applied_from IS NULL
and l_cat.lookup_code = (''TRADE_''||ard.source_type))
or( ra.amount_applied_from IS NOT NULL
and l_cat.lookup_code = (''CCURR_''||ard.source_type))
or(ps.class =''BR'' and l_cat.lookup_code = (''BR_''||ard.source_type)))
AND gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
AND ra.posting_control_id <> - 3
AND ra.status = ''APP''
AND cr.reversal_date IS NOT NULL
AND nvl(ra.confirmed_flag,''Y'') = ''Y''
AND nvl(cr.confirmed_flag,''Y'') = ''Y''
AND crh.first_posted_record_flag = ''Y''
AND ard.source_table = ''RA''
AND l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
AND ra.event_id IS NULL
AND EXISTS
(SELECT NULL
FROM xla_distribution_links lk
WHERE lk.source_distribution_id_num_1 = ard.line_id
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
'||l_ra_org_where||'
'||l_ard_org_where||'
'||l_ps_org_where||'
'||l_cr_org_where||'
'||l_crh_org_where||'
'||l_bat_org_where||'
'||l_bs_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_ard_where||'
'||l_posting_status_nul_where;
SELECT value INTO l_nls_numeric_char
FROM v$NLS_PARAMETERS
WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
select to_char(sysdate,'YYYY-MM-DD')
into l_report_date
from dual;
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_coa_id
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_coa_id
FROM gl_sets_of_books sob,
ar_system_parameters sysparam
WHERE sob.set_of_books_id = sysparam.set_of_books_id
AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
select substrb(hou.name,1,60)
into l_organization
from hr_organization_units hou
where hou.organization_id = arp_recon_rep.var_tname.g_reporting_entity_id;
select meaning
into l_organization
from ar_lookups
where lookup_code ='ALL' and lookup_type ='ALL';
SELECT fcav.application_column_name, flex_value_set_id
INTO l_natural_segment_col , l_flex_value_set_id
FROM fnd_segment_attribute_values fcav,
fnd_id_flex_segments fifs
WHERE fcav.application_id = 101
AND fcav.id_flex_code = 'GL#'
AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
AND fcav.attribute_value = 'Y'
AND fcav.segment_attribute_type = 'GL_ACCOUNT'
AND fifs.application_id = fcav.application_id
AND fifs.id_flex_code = fcav.id_flex_code
AND fifs.id_flex_num = fcav.id_flex_num
AND fcav.application_column_name = fifs.application_column_name;
select meaning
into l_receivable_mode_meaning
from fnd_lookups
where lookup_type = 'YES_NO'
and lookup_code = 'Y';
select meaning
into l_receivable_mode_meaning
from fnd_lookups
where lookup_type = 'YES_NO'
and lookup_code = 'N';
select meaning
into l_status_meaning
from ar_lookups
where lookup_type = 'POSTED_STATUS'
and lookup_code = arp_recon_rep.var_tname.g_posting_status;
select substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
ps.trx_number cm_number,
ps1.trx_number trx_number,
nvl(ard.amount_dr,0) entered_debit,
nvl(ard.amount_cr,0) entered_credit,
nvl(ard.acctd_amount_dr,0) acctd_debit,
nvl(ard.acctd_amount_cr,0) acctd_credit,
to_char(ps.trx_date,''YYYY-MM-DD'') cm_date,
to_char(ps1.trx_date,''YYYY-MM-DD'') trx_date,
to_char(ra.gl_date,''YYYY-MM-DD'') app_gl_date,
to_char(ps.gl_date,''YYYY-MM-DD'') cm_gl_date,
to_char(ps1.gl_date,''YYYY-MM-DD'') trx_gl_date,
ps.invoice_currency_code cm_currency_code,
ps1.invoice_currency_code trx_currency_code,
to_char(ps.exchange_date,''YYYY-MM-DD'') cm_exchange_date,
to_char(ps1.exchange_date,''YYYY-MM-DD'') trx_exchange_date,
ps.exchange_rate cm_exchange_rate,
ps1.exchange_rate trx_exchange_rate,
l_cat.meaning category,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''ALL'',gc.code_combination_id) account,
ar_calc_aging.get_value(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_BALANCING'',gc.code_combination_id) company,
ar_calc_aging.get_description(101,''GL#'',arp_recon_rep.get_chart_of_accounts_id(),''GL_ACCOUNT'',gc.code_combination_id) account_desc
from '||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra ,
'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard ,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps ,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps1 ,
gl_code_combinations gc,
hz_cust_accounts cust,
hz_parties party,
ar_lookups l_cat
where nvl(ra.confirmed_flag,''Y'') = ''Y''
and ra.application_type = ''CM''
and ra.status = ''APP''
and ard.source_table = ''RA''
and ard.source_id = ra.receivable_application_id
and ra.payment_schedule_id = ps.payment_schedule_id
and ra.applied_payment_schedule_id = ps1.payment_schedule_id
and cust.cust_account_id = ps.customer_id
and cust.party_id = party.party_id
and gc.code_combination_id = ard.code_combination_id
and gc.chart_of_accounts_id = arp_recon_rep.get_chart_of_accounts_id()
and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
and l_cat.lookup_code = (''CMAPP_''||ard.source_type)
'||l_ard_org_where||'
'||l_ra_org_where||'
'||l_ps_org_where||'
'||l_ps1_org_where||'
'||l_gl_date_where ||'
'||l_co_seg_where ||'
'||l_account_where ||'
'||l_account_seg_where ||'
'||l_source_type_where||'
'||l_posting_status_where||'
order by company, category, account,app_gl_date, cm_number' ;
select to_char(sysdate,'YYYY-MM-DD')
into l_report_date
from dual;
l_insert_stmt DBMS_SQL.VARCHAR2A;
select meaning
into l_organization
from ar_lookups
where lookup_code ='ALL' and lookup_type ='ALL';
SELECT fcav.application_column_name, flex_value_set_id
INTO l_natural_segment_col , l_flex_value_set_id
FROM fnd_segment_attribute_values fcav,
fnd_id_flex_segments fifs
WHERE fcav.application_id = 101
AND fcav.id_flex_code = 'GL#'
AND fcav.id_flex_num = arp_recon_rep.var_tname.g_chart_of_accounts_id
AND fcav.attribute_value = 'Y'
AND fcav.segment_attribute_type = 'GL_ACCOUNT'
AND fifs.application_id = fcav.application_id
AND fifs.id_flex_code = fcav.id_flex_code
AND fifs.id_flex_num = fcav.id_flex_num
AND fcav.application_column_name = fifs.application_column_name;
SELECT sob.name sob_name,
sob.currency_code functional_currency
INTO l_sob_name,
l_functional_currency
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = arp_recon_rep.var_tname.g_reporting_entity_id;
SELECT sob.name sob_name,
sob.currency_code functional_currency
INTO l_sob_name,
l_functional_currency
FROM gl_sets_of_books sob,
ar_system_parameters sysparam
WHERE sob.set_of_books_id = sysparam.set_of_books_id
AND sysparam.org_id = arp_recon_rep.var_tname.g_reporting_entity_id;
select fnd_currency.get_format_mask(l_functional_currency,40)
into l_format
from dual;
SELECT p.start_date, p.end_date
INTO l_gl_date_from , l_gl_date_to
FROM gl_periods p, gl_sets_of_books b
WHERE p.period_set_name = b.period_set_name
AND p.period_type = b.accounted_period_type
AND b.set_of_books_id = arp_recon_rep.var_tname.g_set_of_books_id
AND p.period_name = arp_recon_rep.var_tname.g_period_name;
'SELECT',
'ALL');
'SELECT',
'GL_BALANCING');
insert into ar_gl_recon_gt(code_combination_id,
receivables_dr,receivables_cr,
account_type, account_type_code,account,company)
(select dat.code_combination_id,
sum(nvl(acctd_amount_dr,0)) receivables_debit,
sum(nvl(acctd_amount_cr,0)) receivables_credit,
lookup.description account_type,
gc.account_type account_type_code, ' || l_sel_seg || ' account, ' || l_sel_co_seg || ' company ' ||
' from (
-- Bug 6943555
select decode(sign(sum(nvl(b.acctd_amount_dr,0))- sum(nvl(b.acctd_amount_cr,0))),
+1, (sum(nvl(b.acctd_amount_dr,0))- sum(nvl(b.acctd_amount_cr,0))),
0) acctd_amount_dr,
decode(sign(sum(nvl(b.acctd_amount_dr,0))- sum(nvl(b.acctd_amount_cr,0))),
-1, (sum(nvl(b.acctd_amount_cr,0))- sum(nvl(b.acctd_amount_dr,0))),
0) acctd_amount_cr,
b.code_combination_id
from
(select
DECODE(decode (ae.accounting_class_code, ''RECEIVABLE'', ''REC'', ''FV_REC_DR'', ''REC'', ''FV_REC_CR'', ''REC'', ctlgd.account_class), ''REC'',decode(sign(acctd_amount), -1 ,0, acctd_amount),
''REV'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''TAX'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''ROUND'',decode(sign(acctd_amount), -1,abs(acctd_amount),0),
''UNEARN'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''FREIGHT'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''UNBILL'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),0) acctd_amount_dr,
DECODE(decode (ae.accounting_class_code, ''RECEIVABLE'', ''REC'', ''FV_REC_DR'' , ''REC'', ''FV_REC_CR''
, ''REC'', ctlgd.account_class), ''REC'',decode(sign(acctd_amount), -1 ,abs(acctd_amount),0),
''REV'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''TAX'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''ROUND'',decode(sign(acctd_amount), -1,0,acctd_amount),
''UNEARN'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''FREIGHT'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''UNBILL'',decode(sign(acctd_amount), -1, 0,acctd_amount),0) acctd_amount_cr,
ae.code_combination_id code_combination_id,
ae.ae_header_id ae_header_id,
ae.ae_line_num ae_line_num
from ra_cust_trx_line_gl_dist ctlgd
, xla_distribution_links lk
, xla_ae_headers hd
, xla_ae_lines ae';
l_insert_stmt(l_increment):= l_temp_stmt;
select
DECODE(ctlgd.account_class, ''REC'',decode(sign(acctd_amount), -1 ,0, acctd_amount),
''REV'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''TAX'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''ROUND'',decode(sign(acctd_amount), -1,abs(acctd_amount),0),
''UNEARN'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''FREIGHT'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''UNBILL'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),0) acctd_amount_dr,
DECODE(ctlgd.account_class, ''REC'',decode(sign(acctd_amount), -1 ,abs(acctd_amount),0),
''REV'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''TAX'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''ROUND'',decode(sign(acctd_amount), -1,0,acctd_amount),
''UNEARN'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''FREIGHT'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''UNBILL'',decode(sign(acctd_amount), -1, 0,acctd_amount),0) acctd_amount_cr,
ctlgd.code_combination_id code_combination_id,
NULL ae_header_id,
NULL ae_line_num
from ra_cust_trx_line_gl_dist ctlgd
where ctlgd.gl_date between :gl_date_from and :gl_date_to
/* Bug fix 6631925 */
and ctlgd.account_set_flag = ''N''
AND ctlgd.event_id IS NULL
and ctlgd.posting_control_id <> -3
and ctlgd.set_of_books_id = :reporting_entity_id
'||l_gl_dist_org_where||'
UNION ALL';
l_insert_stmt(l_increment):= l_temp_stmt;
select lk.unrounded_accounted_dr acctd_amount_dr ,
lk.unrounded_accounted_cr acctd_amount_cr ,
ae.code_combination_id code_combination_id,
ae.ae_header_id ae_header_id,
ae.ae_line_num ae_line_num
from ar_distributions_all ard
, xla_distribution_links lk
, xla_ae_lines ae,
ar_cash_receipt_history crh,
ar_cash_receipts cr,
xla_ae_headers hd
where cr.reversal_date IS NULL
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND hd.event_type_code <> ''MANUAL''
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.set_of_books_id = hd.ledger_id
AND crh.event_id IS NOT NULL
AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
and crh.posting_control_id <> -3
and crh.cash_receipt_history_id = ard.source_id
and hd.accounting_date between :gl_date_from and :gl_date_to
and ard.source_table = ''CRH''
'||l_ard_org_where||'
'||l_crh_org_where||'
'||l_cr_org_where||'';
l_insert_stmt(l_increment):= l_temp_stmt;
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
NULL ae_header_id,
NULL ae_line_num
from ar_distributions_all ard,
ar_cash_receipt_history crh,
ar_cash_receipts cr
where cr.reversal_date IS NULL
and crh.cash_receipt_id = cr.cash_receipt_id
and crh.posting_control_id <> -3
AND crh.event_id IS NULL
and crh.cash_receipt_history_id = ard.source_id
and crh.gl_date between :gl_date_from and :gl_date_to
and ard.source_table = ''CRH''
'||l_ard_org_where||'
'||l_crh_org_where||'
'||l_cr_org_where||'
-----------------------------------------------------------------------------------------
/* Bug fix 6432847: select receipts that are reversed*/
UNION ALL';
l_insert_stmt(l_increment):= l_temp_stmt;
select lk.unrounded_accounted_dr acctd_amount_dr ,
lk.unrounded_accounted_cr acctd_amount_cr ,
ae.code_combination_id code_combination_id,
ae.ae_header_id ae_header_id,
ae.ae_line_num ae_line_num
from ar_distributions_all ard
, xla_distribution_links lk
, xla_ae_lines ae,
ar_cash_receipt_history crh,
ar_cash_receipts cr,
xla_ae_headers hd
where cr.reversal_date IS NOT NULL
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND hd.event_type_code <> ''MANUAL''
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.set_of_books_id = hd.ledger_id
AND crh.event_id IS NOT NULL
AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
and crh.posting_control_id <> -3
and crh.cash_receipt_history_id = ard.source_id
and hd.accounting_date between :gl_date_from and :gl_date_to
and ard.source_table = ''CRH''
'||l_ard_org_where||'
'||l_crh_org_where||'
'||l_cr_org_where||'';
l_insert_stmt(l_increment):= l_temp_stmt;
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
NULL ae_header_id,
NULL ae_line_num
from ar_distributions_all ard,
ar_cash_receipt_history crh,
ar_cash_receipts cr
where cr.reversal_date IS NOT NULL
and crh.cash_receipt_id = cr.cash_receipt_id
and crh.posting_control_id <> -3
AND crh.event_id IS NULL
and crh.cash_receipt_history_id = ard.source_id
and crh.gl_date between :gl_date_from and :gl_date_to
and ard.source_table = ''CRH''
'||l_ard_org_where||'
'||l_crh_org_where||'
'||l_cr_org_where||'
-----------------------------------------------------------------------------------------
UNION ALL';
/* Bug fix 6432847: with ra.gl_date condition, select
applications which are not unapplied */
l_increment := l_increment+1;
l_insert_stmt(l_increment):= l_temp_stmt;
select lk.unrounded_accounted_dr acctd_amount_dr ,
lk.unrounded_accounted_cr acctd_amount_cr ,
ae.code_combination_id code_combination_id,
ae.ae_header_id ae_header_id,
ae.ae_line_num ae_line_num
from ar_distributions_all ard
, xla_distribution_links lk
, xla_ae_lines ae
, ar_receivable_applications ra
, xla_ae_headers hd
, ar_cash_receipts cr /* Bug fix 6432847 */
where ard.source_id = ra.receivable_application_id
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND ra.set_of_books_id = hd.ledger_id
AND hd.event_type_code <> ''MANUAL''
AND ra.event_id IS NOT NULL
AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
and cr.reversal_date IS NULL
and ra.cash_receipt_id = cr.cash_receipt_id
AND hd.accounting_date between :gl_date_from and :gl_date_to
and ra.posting_control_id <> -3
and ard.source_table = ''RA''
and ra.application_type = ''CASH''
'||l_ard_org_where||'
'||l_ra_org_where||'
'||l_cr_org_where||'
UNION ALL
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
to_number (NULL) ae_header_id,
to_number (NULL) ae_line_num';
l_insert_stmt(l_increment):= l_temp_stmt;
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
to_number (NULL) ae_header_id,
to_number (NULL) ae_line_num
from ar_distributions_all ard,
ar_receivable_applications ra,
ar_cash_receipts cr /* Bug fix 6432847 */
where cr.reversal_date IS NULL
and ra.cash_receipt_id = cr.cash_receipt_id
AND ra.status IN (''UNAPP'', ''UNID'')
AND ra.event_id IS NULL
AND EXISTS
(
SELECT NULL
FROM xla_distribution_links lk
WHERE lk.source_distribution_id_num_1 = ard.line_id
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
AND ra.gl_date between :gl_date_from and :gl_date_to
and ra.posting_control_id <> -3
and ra.receivable_application_id = ard.source_id
and ard.source_table = ''RA''
and ra.application_type = ''CASH''
'||l_ard_org_where||'
'||l_ra_org_where||'
'||l_cr_org_where||'
--------------------------------------------------------------------------------------------------
UNION ALL';
/* Bug fix 6432847: with ard.gl_date condition, select
applications which are unapplied */
l_increment := l_increment+1;
l_insert_stmt(l_increment):= l_temp_stmt;
select lk.unrounded_accounted_dr acctd_amount_dr ,
lk.unrounded_accounted_cr acctd_amount_cr ,
ae.code_combination_id code_combination_id,
ae.ae_header_id ae_header_id,
ae.ae_line_num ae_line_num
from ar_distributions_all ard
, xla_distribution_links lk
, xla_ae_lines ae
, ar_receivable_applications ra
, xla_ae_headers hd
, ar_cash_receipts cr /* Bug fix 6432847 */
where ard.source_id = ra.receivable_application_id
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND ra.set_of_books_id = hd.ledger_id
AND hd.event_type_code <> ''MANUAL''
AND ra.event_id IS NOT NULL
AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
and cr.reversal_date IS NOT NULL
and ra.cash_receipt_id = cr.cash_receipt_id
AND hd.accounting_date between :gl_date_from and :gl_date_to
and ra.posting_control_id <> -3
and ard.source_table = ''RA''
and ra.application_type = ''CASH''
'||l_ard_org_where||'
'||l_ra_org_where||'
'||l_cr_org_where||'
UNION ALL
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
to_number (NULL) ae_header_id,
to_number (NULL) ae_line_num';
l_insert_stmt(l_increment):= l_temp_stmt;
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
to_number (NULL) ae_header_id,
to_number (NULL) ae_line_num
from ar_distributions_all ard,
ar_receivable_applications ra,
ar_cash_receipts cr /* Bug fix 6432847 */
where cr.reversal_date IS NOT NULL
and ra.cash_receipt_id = cr.cash_receipt_id
AND ra.status IN (''UNAPP'', ''UNID'')
AND ra.event_id IS NULL
AND EXISTS
(
SELECT NULL
FROM xla_distribution_links lk
WHERE lk.source_distribution_id_num_1 = ard.line_id
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
AND ra.gl_date between :gl_date_from and :gl_date_to
and ra.posting_control_id <> -3
and ra.receivable_application_id = ard.source_id
and ard.source_table = ''RA''
and ra.application_type = ''CASH''
'||l_ard_org_where||'
'||l_ra_org_where||'
'||l_cr_org_where||'
--------------------------------------------------------------------------------------------------
/* Bug fix 5679071 : UNAPP records should be displayed based on how it was posted */
UNION ALL';
l_insert_stmt(l_increment):= l_temp_stmt;
select lk.unrounded_accounted_dr acctd_amount_dr ,
lk.unrounded_accounted_cr acctd_amount_cr ,
ae.code_combination_id code_combination_id,
ae.ae_header_id ae_header_id,
ae.ae_line_num ae_line_num
from ar_distributions_all ard
, xla_distribution_links lk
, xla_ae_lines ae
, ar_receivable_applications ra
, xla_ae_headers hd
where ard.source_id = ra.receivable_application_id
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND ra.set_of_books_id = hd.ledger_id
AND hd.event_type_code <> ''MANUAL''
AND ra.event_id IS NOT NULL
AND ae.accounting_class_code IN (''CASH'', ''REMITTANCE'', ''CONFIRMATION''
, ''FV_CASH_CR'', ''FV_CASH_DR'', ''RECEIVABLE''
, ''FREIGHT'', ''TAX'', ''DEFERRED_TAX''
, ''REVENUE'', ''UNEARNED_REVENUE'', ''UNBILL''
, ''EDISC'', ''EDISC_NON_REC_TAX'', ''UNEDISC''
, ''UNEDISC_NON_REC_TAX'', ''ROUNDING'', ''EXCHANGE_GAIN_LOSS''
, ''GAIN'', ''LOSS'', ''UNID''
, ''WRITE_OFF'', ''ACC'', ''BANK_CHG''
, ''CLAIM'', ''SHORT_TERM_DEBT'', ''FACTOR''
, ''FAC_BR'', ''PMT_NET'', ''PREPAY''
, ''REFUND'', ''REM_BR'', ''UNAPP'')
AND hd.accounting_date between :gl_date_from and :gl_date_to
and ra.posting_control_id <> -3
and ard.source_table = ''RA''
and ra.application_type <> ''CASH''
'||l_ard_org_where||'
'||l_ra_org_where||'
UNION ALL
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
to_number (NULL) ae_header_id,
to_number (NULL) ae_line_num';
l_insert_stmt(l_increment):= l_temp_stmt;
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
to_number (NULL) ae_header_id,
to_number (NULL) ae_line_num
from ar_distributions_all ard,
ar_receivable_applications ra
where ra.status IN (''UNAPP'', ''UNID'')
AND ra.event_id IS NULL
AND EXISTS
(
SELECT NULL
FROM xla_distribution_links lk
WHERE lk.source_distribution_id_num_1 = ard.line_id
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'')
AND ra.gl_date between :gl_date_from and :gl_date_to
and ra.posting_control_id <> -3
and ra.receivable_application_id = ard.source_id
and ard.source_table = ''RA''
and ra.application_type <> ''CASH''
'||l_ard_org_where||'
'||l_ra_org_where||'
---------------------------------------------------------------------------------------------
UNION ALL';
l_insert_stmt(l_increment):= l_temp_stmt;
select lk.unrounded_accounted_dr acctd_amount_dr ,
lk.unrounded_accounted_cr acctd_amount_cr ,
ae.code_combination_id code_combination_id,
ae.ae_header_id ae_header_id,
ae.ae_line_num ae_line_num
FROM ar_distributions_all ard
, xla_distribution_links lk
, xla_ae_lines ae
, ar_misc_cash_distributions mcd
, xla_ae_headers hd
where ard.source_id = mcd.misc_cash_distribution_id
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_DISTRIBUTIONS_ALL'')
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND mcd.set_of_books_id = hd.ledger_id
AND hd.event_type_code <> ''MANUAL''
AND mcd.event_id IS NOT NULL
AND ae.accounting_class_code IN (''FV_MISCCASH_CR'', ''FV_MISCCASH_DR'', ''MISC_CASH''
, ''CASH'', ''CONFIRMATION'', ''REMITTANCE''
, ''FACTOR'', ''SHORT_TERM_DEBT'', ''BANK_CHG''
, ''TAX'')
AND hd.accounting_date between :gl_date_from and :gl_date_to
and mcd.posting_control_id <> -3
and ard.source_table = ''MCD''
'||l_mcd_org_where||'
UNION ALL
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
NULL ae_header_id,
NULL ae_line_num
from ar_distributions_all ard
, ar_misc_cash_distributions mcd
where ard.source_id = mcd.misc_cash_distribution_id
AND mcd.event_id IS NULL
and mcd.gl_date between :gl_date_from and :gl_date_to
and mcd.posting_control_id <> -3
and ard.source_table = ''MCD''
'||l_mcd_org_where||'
----------------------------------------------------------------------------------------------
UNION ALL';
l_insert_stmt(l_increment):= l_temp_stmt;
select lk.unrounded_accounted_dr acctd_amount_dr ,
lk.unrounded_accounted_cr acctd_amount_cr ,
ae.code_combination_id code_combination_id,
ae.ae_header_id ae_header_id,
ae.ae_line_num ae_line_num
FROM ar_distributions_all ard
, xla_distribution_links lk
, xla_ae_lines ae
, ar_adjustments adj
, xla_ae_headers hd
where ard.source_id = adj.adjustment_id
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND adj.set_of_books_id = hd.ledger_id
AND hd.event_type_code <> ''MANUAL''
AND adj.event_id IS NOT NULL
AND ae.accounting_class_code IN (''RECEIVABLE'', ''ADJ'', ''CHARGES''
, ''TAX'', ''DEFERRED_TAX'', ''FINCHRG_NON_REC_TAX''
, ''ADJ_NON_REC_TAX'', ''ENDORSEMENT'')
and adj.posting_control_id <> -3
and ard.source_table = ''ADJ''
and hd.accounting_date between :gl_date_from and :gl_date_to
'||l_adj_org_where||'
UNION ALL
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
NULL ae_header_id,
NULL ae_line_num
from ar_distributions_all ard
, ar_adjustments adj
where ard.source_id = adj.adjustment_id
AND adj.gl_date between :gl_date_from and :gl_date_to
AND adj.event_id IS NULL
and adj.posting_control_id <> -3
and ard.source_table = ''ADJ''
'||l_adj_org_where||'
-------------------------------------------------------------------------------------------
UNION ALL';
l_insert_stmt(l_increment):= l_temp_stmt;
select lk.unrounded_accounted_dr acctd_amount_dr ,
lk.unrounded_accounted_cr acctd_amount_cr ,
ae.code_combination_id code_combination_id,
ae.ae_header_id ae_header_id,
ae.ae_line_num ae_line_num
FROM ar_distributions_all ard
, xla_distribution_links lk
, xla_ae_lines ae
, ar_transaction_history th
, ra_customer_trx trx
, xla_ae_headers hd
where hd.accounting_date between :gl_date_from and :gl_date_to
and th.posting_control_id <> -3
and ard.source_table = ''TH''
AND ard.source_id = th.transaction_history_id
AND ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND hd.event_type_code <> ''MANUAL''
AND th.customer_trx_id = trx.customer_trx_id
AND trx.set_of_books_id = hd.ledger_id
AND th.event_id IS NOT NULL
AND ae.accounting_class_code IN (''RECEIVABLE'', ''DEFERRED_TAX'', ''TAX'', ''UNPAID_BR'', ''REM_BR'', ''FAC_BR'')
'||l_ath_org_where||'
UNION ALL
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
NULL ae_header_id,
NULL ae_line_num
FROM ar_distributions_all ard
, ar_transaction_history th
where ard.source_id = th.transaction_history_id
AND th.event_id IS NULL
AND th.gl_date between :gl_date_from and :gl_date_to
and th.posting_control_id <> -3
and ard.source_table = ''TH''
'||l_ath_org_where||'';
l_insert_stmt(l_increment):= l_temp_stmt;
SELECT xal.accounted_dr acctd_amount_dr,
xal.accounted_cr acctd_amount_cr,
xal.code_combination_id code_combination_id,
xal.ae_header_id ae_header_id,
xal.ae_line_num ae_line_num
from xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities_upg xte
where xal.accounting_class_code = ''BALANCE''
and xah.ledger_id = xte.ledger_id
and xah.entity_id = xte.entity_id
and xal.ae_header_id = xah.ae_header_id
and xah.accounting_date between :gl_date_from and :gl_date_to
and xal.application_id = 222
and xah.application_id = 222
and xte.application_id = 222
and xte.ledger_id = :reporting_entity_id ) b
group by b.ae_header_id,b.ae_line_num,b.code_combination_id ) dat,
gl_code_combinations gc,
gl_lookups lookup
where dat.code_combination_id = gc.code_combination_id
and lookup.lookup_code = gc.account_type
and lookup.lookup_type = ''ACCOUNT TYPE'''||
l_co_seg_where||
l_account_where||
l_account_seg_where||'
group by dat.code_combination_id,lookup.description, gc.code_combination_id,gc.account_type, ' || l_sel_seg || ' , ' || l_sel_co_seg || ' )';
l_insert_stmt(l_increment):= l_temp_stmt;
DBMS_SQL.PARSE(l_cursor_id, l_insert_stmt,1,l_insert_stmt.last,FALSE, DBMS_SQL.NATIVE);
update ar_gl_recon_gt argt
set (opening_balance_dr,
opening_balance_cr,
period_activity_dr,
period_activity_cr) = (select nvl(glb.begin_balance_dr,0),
nvl(glb.begin_balance_cr,0),
nvl(glb.period_net_dr,0),
nvl(glb.period_net_cr,0)
from gl_balances glb
where glb.period_name = get_period_name()
and glb.code_combination_id = argt.code_combination_id
and glb.actual_flag = 'A'
and glb.ledger_id = get_set_of_books_id()
and glb.currency_code = get_functional_currency());
update ar_gl_recon_gt argt
set (subledger_not_ar_dr ,
subledger_not_ar_cr ,
subledger_manual_dr ,
subledger_manual_cr ,
subledger_rec_dr,
subledger_rec_cr,
gl_unposted_dr,
gl_unposted_cr) =
(select sum(decode(gjh.je_source,'Manual', 0,
'Receivables', 0,
decode(gjl.status,
'P',gjl.accounted_dr,0))) subledger_not_ar_dr ,
sum(decode(gjh.je_source,'Manual', 0,
'Receivables', 0,
decode(gjl.status,
'P',gjl.accounted_cr,0))) subledger_not_ar_cr,
sum(decode(gjh.je_source, 'Manual',
decode(gjl.status,'P',
gjl.accounted_dr,0),0)) subledger_manual_dr ,
sum(decode(gjh.je_source, 'Manual',
decode(gjl.status,'P',
gjl.accounted_cr,0),0)) subledger_manual_cr,
sum(decode(gjh.je_source, 'Receivables',
decode(gjl.status,'P',
gjl.accounted_dr,0),0)) subledger_receivables_dr ,
sum(decode(gjh.je_source, 'Receivables',
decode(gjl.status,'P',
gjl.accounted_cr,0),0)) subledger_receivables_cr,
sum(decode(gjl.status,'P',0,gjl.accounted_dr)) gl_unposted_dr,
sum(decode(gjl.status,'P',0,gjl.accounted_cr)) gl_unposted_cr
from gl_je_lines gjl,
gl_je_headers gjh
where gjl.code_combination_id = argt.code_combination_id
and gjl.period_name = get_period_name()
and gjl.ledger_id = get_set_of_books_id()
and gjl.je_header_id = gjh.je_header_id
and gjh.actual_flag = 'A'
and gjh.currency_code <> 'STAT'
group by gjl.code_combination_id);
update ar_gl_recon_gt argt
set (gl_interface_dr, gl_interface_cr) =
(select sum(nvl(gif.accounted_dr,0)) gl_interface_dr,
sum(nvl(gif.accounted_cr,0)) gl_interface_cr
from gl_interface gif,
gl_je_sources gjs
where gif.code_combination_id = argt.code_combination_id
and gif.accounting_date between get_gl_date_from() and get_gl_date_to()
and gif.user_je_source_name = gjs.user_je_source_name
and gjs.je_source_name = 'Receivables'
and gif.actual_flag = 'A'
group by gif.code_combination_id);
/*update ar_gl_recon_gt
set account_desc = ar_calc_aging.get_description (101,'GL#',
arp_recon_rep.get_chart_of_accounts_id(),'GL_ACCOUNT',code_combination_id);*/
update ar_gl_recon_gt
set account_desc = substr(get_description(101,'GL#',
arp_recon_rep.get_chart_of_accounts_id(),'GL_ACCOUNT',code_combination_id),1,235);
select code_combination_id,
account_type,
account,
account_desc,
company,
decode(account_type_code,''A'',1,''L'',2,''R'',3,''E'',4) account_type_code,
nvl(opening_balance_dr,0) begin_gl_bal_debit,
nvl(opening_balance_cr,0) begin_gl_bal_credit,
nvl(opening_balance_dr,0)+nvl(period_activity_dr,0) end_gl_bal_debit,
nvl(opening_balance_cr,0)+nvl(period_activity_cr,0) end_gl_bal_credit,
nvl(subledger_not_ar_dr,0) subledger_not_ar_debit,
nvl(subledger_not_ar_cr,0) subledger_not_ar_credit,
nvl(subledger_manual_dr,0) subledger_manual_debit,
nvl(subledger_manual_cr,0) subledger_manual_credit,
nvl(subledger_rec_dr,0) subledger_receivables_debit,
nvl(subledger_rec_cr,0) subledger_receivables_credit,
nvl(gl_unposted_dr,0) gl_unposted_debit,
nvl(gl_unposted_cr,0) gl_unposted_credit,
nvl(gl_interface_dr,0) gl_interface_debit,
nvl(gl_interface_cr,0) gl_interface_credit,
nvl(receivables_dr,0) receivables_debit,
nvl(receivables_cr,0) receivables_credit
from ar_gl_recon_gt
where ''N'' = arp_recon_rep.get_out_of_balance_only()
or nvl(receivables_dr,0)- nvl(subledger_rec_dr,0) <> 0
or nvl(receivables_cr,0)- nvl(subledger_rec_cr,0) <> 0
order by account_type_code,
company,
account';
SELECT value INTO l_nls_numeric_char
FROM v$NLS_PARAMETERS
WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
select to_char(sysdate,'YYYY-MM-DD')
into l_report_date
from dual;