[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_all 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_all type,
gl_code_combinations gc,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' pay,
ar_receivables_trx_all 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_all 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 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_all bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups l_cat
where 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 nvl(cr.confirmed_flag,''Y'') = ''Y''
and cr.receipt_method_id = arm.receipt_method_id
and crh.cash_receipt_id = cr.cash_receipt_id
and crh.first_posted_record_flag = ''Y''
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 cr.pay_from_customer = cust.cust_account_id(+)
and cust.party_id = party.party_id(+)
and l_cat.lookup_type = ''ARRGTA_FUNCTION_MAPPING''
and l_cat.lookup_code = ''TRADE_''||ard.source_type
'||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
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(ard.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_all bs,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups l_cat
where ard.source_type = ''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 nvl(cr.confirmed_flag,''Y'') = ''Y''
and cr.receipt_method_id = arm.receipt_method_id
and crh.cash_receipt_id = cr.cash_receipt_id
and crh.first_posted_record_flag = ''Y''
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_table in(''CRH'', ''RA'')
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 l_cat.lookup_code = ''TRADE_''||ard.source_type
'||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_posting_status_ard_where;
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_all 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 '||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;
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_all bs,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_payment_schedules_all||' ps,
'||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_lookups l_cat
where nvl(ra.confirmed_flag,''Y'') = ''Y''
and ra.status = ''APP''
and cr.cash_receipt_id = ra.cash_receipt_id
and nvl(cr.confirmed_flag,''Y'') = ''Y''
and cr.receipt_method_id = arm.receipt_method_id
and crh.cash_receipt_id = cr.cash_receipt_id
and crh.first_posted_record_flag = ''Y''
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 ard.source_table = ''RA''
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)))
'||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;
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_all 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;
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_all 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;
insert into ar_gl_recon_gt(code_combination_id,
receivables_dr,receivables_cr,
account_type, account_type_code)
(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
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(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(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,
gl_dist.code_combination_id code_combination_id,
gl_dist.ae_header_id ae_header_id,
gl_dist.ae_line_num ae_line_num
from '||arp_recon_rep.var_tname.l_ra_cust_trx_gl_dist_all||' gl_dist
where gl_dist.gl_date between :gl_date_from and :gl_date_to
/* Bug fix 6631925 */
and gl_dist.account_set_flag = ''N''
and gl_dist.posting_control_id <> -3
'||l_gl_dist_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,
ard.ae_header_id ae_header_id,
ard.ae_line_num ae_line_num
from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
'||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr /* Bug fix 6432847 */
where crh.gl_date between :gl_date_from and :gl_date_to
and crh.posting_control_id <> -3
and crh.cash_receipt_history_id = ard.source_id
/* Bug 6432847 : select receipts that are not reversed */
and cr.cash_receipt_id = crh.cash_receipt_id
and cr.reversal_date IS NULL
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
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
ard.ae_header_id ae_header_id,
ard.ae_line_num ae_line_num
from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
'||arp_recon_rep.var_tname.l_ar_cash_receipt_history_all||' crh,
'||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' 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.cash_receipt_history_id = ard.source_id
and ard.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 */
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
ard.ae_header_id ae_header_id,
ard.ae_line_num ae_line_num
from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr /* Bug fix 6432847 */
where ra.gl_date between :gl_date_from and :gl_date_to
and cr.cash_receipt_id = ra.cash_receipt_id
and cr.reversal_date IS NULL
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 */
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
ard.ae_header_id ae_header_id,
ard.ae_line_num ae_line_num
from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra,
'||arp_recon_rep.var_tname.l_ar_cash_receipts_all||' cr /* Bug fix 6432847 */
where ard.gl_date between :gl_date_from and :gl_date_to
and cr.reversal_date IS NOT NULL
and ra.cash_receipt_id = cr.cash_receipt_id
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
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
ard.ae_header_id ae_header_id,
ard.ae_line_num ae_line_num
from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
'||arp_recon_rep.var_tname.l_ar_receivable_apps_all||' ra
where ard.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
select ard.acctd_amount_dr acctd_amount_dr ,
ard.acctd_amount_cr acctd_amount_cr ,
ard.code_combination_id code_combination_id,
ard.ae_header_id ae_header_id,
ard.ae_line_num ae_line_num
from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
'||arp_recon_rep.var_tname.l_ar_misc_cash_dists_all||' mcd
where mcd.gl_date between :gl_date_from and :gl_date_to
and mcd.posting_control_id <> -3
and mcd.misc_cash_distribution_id = ard.source_id
and ard.source_table = ''MCD''
'||l_ard_org_where||'
'||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,
ard.ae_header_id ae_header_id,
ard.ae_line_num ae_line_num
from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
'||arp_recon_rep.var_tname.l_ar_adjustments_all||' adj
where adj.gl_date between :gl_date_from and :gl_date_to
and adj.posting_control_id <> -3
and adj.adjustment_id = ard.source_id
and ard.source_table = ''ADJ''
'||l_ard_org_where||'
'||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,
ard.ae_header_id ae_header_id,
ard.ae_line_num ae_line_num
from '||arp_recon_rep.var_tname.l_ar_distributions_all||' ard,
ar_transaction_history_all ath
where ath.gl_date between :gl_date_from and :gl_date_to
and ath.posting_control_id <> -3
and ath.transaction_history_id = ard.source_id
and ard.source_table = ''TH''
'||l_ard_org_where||'
'||l_ath_org_where||'
/* 6964153 */
UNION ALL
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.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)'
USING
l_gl_date_from, l_gl_date_to,
l_gl_date_from, l_gl_date_to,
l_gl_date_from, l_gl_date_to,
l_gl_date_from, l_gl_date_to,
l_gl_date_from, l_gl_date_to,
l_gl_date_from, l_gl_date_to,
l_gl_date_from, l_gl_date_to,
l_gl_date_from, l_gl_date_to,
l_gl_date_from, l_gl_date_to,
l_gl_date_from, l_gl_date_to, p_reporting_entity_id ;
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 = ar_calc_aging.get_value(101,'GL#',
arp_recon_rep.get_chart_of_accounts_id(),'ALL',code_combination_id),
company = ar_calc_aging.get_value(101,'GL#',
arp_recon_rep.get_chart_of_accounts_id(),'GL_BALANCING',code_combination_id),
account_desc = ar_calc_aging.get_description(101,'GL#',
arp_recon_rep.get_chart_of_accounts_id(),'GL_ACCOUNT',code_combination_id);
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,
ar_calc_aging.get_value(101,''GL#'',
arp_recon_rep.get_chart_of_accounts_id(),
''GL_ACCOUNT'',code_combination_id)';
select to_char(sysdate,'YYYY-MM-DD')
into l_report_date
from dual;