[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_corrections varchar2(1);
l_update_type FV_SF1219_TEMP.update_type%TYPE;
SELECT rowid,
batch_id,
fund_code,
name,
posted_date,
gl_period,
amount,
sf1219_type_code,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reference_9,
reported_month,
exception_category,
accomplish_month,
accomplish_date,
obligation_date,
inter_agency_flag,
treasury_symbol,
treasury_symbol_id,
record_type,
lines_exist,
alc_code,
org_id,
update_type,
type,
gl_period_name,
processed_flag,
je_header_id,
je_line_num,
NVL(je_from_sla_flag,'N')
FROM FV_SF1219_TEMP
WHERE record_type not in ('P', 'N')
ORDER BY batch_id;
SELECT obligation_date, refund_amount
FROM fv_refunds_voids_all
WHERE cash_receipt_id = l_cash_receipt_id
AND type = 'AP_REFUND'
AND fund_value = l_fund_code
AND org_id = p_def_org_id;
SELECT name, gl_period, amount, sf1219_type_code,
reference_2, reference_3,
reported_month, accomplish_date,
obligation_date, inter_agency_flag,
record_type, lines_exist, alc_code
FROM fv_sf1219_temp
WHERE name = 'Check for Void';
PROCEDURE insert_batches;
PROCEDURE insert_exceptions(x_amount IN NUMBER);
delete_corrections IN VARCHAR2)
IS
l_module_name VARCHAR2(200) := g_module_name || 'MAIN_1219';
p_delete_corrections := delete_corrections;
' DELETE CORRECTIONS: '||P_DELETE_CORRECTIONS);
SELECT COUNT(*)
INTO v_alc_count
FROM fv_sf1219_definitions_accts
WHERE agency_location_code IS NULL
AND set_of_books_id = p_set_bks_id;
SELECT currency_code
INTO l_currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = p_set_bks_id;
'INSERTING JOURNAL LINES ...');
insert_batches;
IF p_delete_corrections = 'Y'
THEN
DELETE FROM fv_sf1219_temp;
DELETE FROM fv_sf1219_manual_lines;
DELETE FROM fv_sf1219_temp
WHERE record_type <> 'N';
DELETE FROM fv_sf1219_manual_lines
WHERE temp_record_id NOT IN
(SELECT temp_record_id
FROM fv_sf1219_temp);
SELECT chart_of_accounts_id
INTO flex_num
FROM gl_ledgers_public_v
WHERE ledger_id = p_set_bks_id ;
SELECT distinct year_start_date
INTO l_yr_start_date
FROM gl_periods glp,
gl_ledgers_public_v gsob
WHERE gsob.ledger_id = p_set_bks_id
AND gsob.period_set_name = glp.period_set_name
AND gsob.chart_of_accounts_id = flex_num
AND period_name = p_gl_period;
SELECT distinct period_type
INTO period_type
FROM gl_period_statuses
WHERE application_id = '101'
AND ledger_id = p_set_bks_id;
SELECT start_date, end_date, period_year
INTO l_start_date1, l_end_date1, l_period_year
FROM gl_periods glp,
gl_ledgers_public_v gsob
WHERE glp.period_name = p_gl_period
AND glp.period_type = period_type
AND gsob.ledger_id = p_set_bks_id
AND gsob.chart_of_accounts_id = flex_num
AND glp.period_set_name = gsob.period_set_name;
SELECT MAX(glp.end_date)
INTO l_yr_end_date
FROM gl_periods glp, gl_ledgers_public_v gsob
WHERE glp.period_year = l_period_year
AND gsob.ledger_id = p_set_bks_id
AND glp.period_set_name = gsob.period_set_name;
PROCEDURE INSERT_BATCHES IS
l_module_name VARCHAR2(200) := g_module_name || 'INSERT_BATCHES';
l_string1 := 'INSERT INTO fv_sf1219_temp(
temp_record_id,
batch_id,
fund_code,
name,
set_of_books_id,
posted_date,
gl_period,
reported_gl_period,
amount,
sf1219_type_code,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reference_9,
reported_month,
default_period_name,
exception_category,
accomplish_month,
accomplish_date,
obligation_date,
inter_agency_flag,
treasury_symbol,
treasury_symbol_id,
record_type,
lines_exist,
alc_code,
org_id,
group_name,
update_type,
type,
gl_period_name,
processed_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
je_header_id,
je_line_num,
je_from_sla_flag)';
(SELECT ''X''
FROM fv_sf1219_audits fvs
WHERE fvs.batch_id = glb.je_batch_id
AND fvs.je_header_id = gll.je_header_id
AND fvs.je_line_num = gll.je_line_num
AND fvs.record_type <> ''B'')';
l_string2 := 'SELECT
fv_sf1219_temp_s.NEXTVAL,
glb.je_batch_id,
ffp.fund_value,
NVL(glb.name,''Manual''),
--glb.set_of_books_id,
--NULL,
:b_sob,
glb.posted_date,
gll.period_name,
TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
''MANUAL'', -- default value
LTRIM(RTRIM(gll.reference_1)),
LTRIM(RTRIM(gll.reference_2)),
LTRIM(RTRIM(gll.reference_3)),
LTRIM(RTRIM(gll.reference_4)),
LTRIM(RTRIM(gll.reference_5)),
LTRIM(RTRIM(gll.reference_6)),
LTRIM(RTRIM(gll.reference_9)),
NULL, -- reported month used for exceptions
glb.default_period_name,
NULL, -- exception_category updated when exception occurred
NULL, -- accomplish_month derived during the process
:b_end_date1, -- accomplish date
NULL, -- obligation_date derived during the process
NULL, -- ia flag updated during the process
fts.treasury_symbol, -- no fund_value for null value(06/15)
ffp.treasury_symbol_id, -- Added to fix Bug 1575992
''M'', -- Default record type as Manual
''N'', -- Default value for lines exist
fda.agency_location_code,
-1, --glb.org_id,
NULL, -- Group name assigned during the process
NULL, -- update type assigned during the process
NULL, -- type assigned during the process
:b_gl_period, -- gl period for which process is run
''N'', -- default processed flag
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
gll.je_header_id,
gll.je_line_num,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_sf1219_definitions_accts fda,
fv_fund_parameters ffp,
fv_treasury_symbols fts
WHERE gll.effective_date <= :b_end_date1
AND glh.currency_code = :b_currency_code
AND glb.status = ''P''
AND glb.actual_flag = ''A''
AND glb.je_batch_id = glh.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND gll.code_combination_id = gcc.code_combination_id
--AND gll.set_of_books_id = p_set_bks_id
AND gll.ledger_id = :b_sob
AND fda.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND fts.set_of_books_id = :b_sob
AND NVL(glh.je_from_sla_flag, ''N'') IN (''N'', ''U'')
AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
''SEGMENT2'', gcc.segment2,
''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4,
''SEGMENT5'', gcc.segment5,
''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7,
''SEGMENT8'', gcc.segment8,
''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10,
''SEGMENT11'', gcc.segment11,
''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13,
''SEGMENT14'', gcc.segment14,
''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16,
''SEGMENT17'', gcc.segment17,
''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19,
''SEGMENT20'', gcc.segment20,
''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22,
''SEGMENT23'', gcc.segment23,
''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25,
''SEGMENT26'', gcc.segment26,
''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28,
''SEGMENT29'', gcc.segment29,
''SEGMENT30'', gcc.segment30) = ffp.fund_value
and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
gcc.segment30)';
l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
for upgraded 11i data ');
l_string2 := 'SELECT
fv_sf1219_temp_s.NEXTVAL,
glb.je_batch_id,
ffp.fund_value,
glb.name,
:b_sob,
glb.posted_date,
gll.period_name,
TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period
NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
''MANUAL'', -- default value
NULL,
LTRIM(RTRIM(aid.invoice_id)),
LTRIM(RTRIM(aip.check_id)),
NULL,
NULL,
NULL,
LTRIM(RTRIM(aip.invoice_payment_id)),
NULL, -- reported month used for exceptions
glb.default_period_name,
NULL, -- exception_category updated when exception occurred
NULL, -- accomplish_month derived during the process
:b_end_date1, -- accomplish date
NULL, -- obligation_date derived during the process
NULL, -- ia flag updated during the process
fts.treasury_symbol, -- no fund_value for null value(06/15)
ffp.treasury_symbol_id, -- Added to fix Bug 1575992
''M'', -- Default record type as Manual
''N'', -- Default value for lines exist
fda.agency_location_code,
-1, --glb.org_id,
NULL, -- Group name assigned during the process
NULL, -- update type assigned during the process
NULL, -- type assigned during the process
:b_gl_period, -- gl period for which process is run
''N'', -- default processed flag
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
gll.je_header_id,
gll.je_line_num,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_sf1219_definitions_accts fda,
fv_fund_parameters ffp,
fv_treasury_symbols fts,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
gl_import_references glir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl
WHERE gll.effective_date <= :b_end_date1
AND glh.currency_code = :b_currency_code
AND glb.status = ''P''
AND glb.actual_flag = ''A''
AND glb.je_batch_id = glh.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND gll.code_combination_id = gcc.code_combination_id
--AND gll.set_of_books_id = p_set_bks_id
AND gll.ledger_id = :b_sob
AND fda.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND fts.set_of_books_id = :b_sob
AND glh.je_source=''Payables''
AND glh.je_category <> ''Treasury Confirmation''
AND glh.je_from_sla_flag = ''Y''
AND ai.invoice_id = aid.invoice_id
AND aip.invoice_id = ai.invoice_id
AND glir.je_header_id = gll.je_header_id
AND glir.je_line_num = gll.je_line_num
AND xal.gl_sl_link_id = glir.gl_sl_link_id
AND xal.gl_sl_link_table = glir.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type IN ( ''AP_PMT_DIST'')
AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND xdl.application_id = 200
AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
''SEGMENT2'', gcc.segment2,
''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4,
''SEGMENT5'', gcc.segment5,
''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7,
''SEGMENT8'', gcc.segment8,
''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10,
''SEGMENT11'', gcc.segment11,
''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13,
''SEGMENT14'', gcc.segment14,
''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16,
''SEGMENT17'', gcc.segment17,
''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19,
''SEGMENT20'', gcc.segment20,
''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22,
''SEGMENT23'', gcc.segment23,
''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25,
''SEGMENT26'', gcc.segment26,
''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28,
''SEGMENT29'', gcc.segment29,
''SEGMENT30'', gcc.segment30) = ffp.fund_value
and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
gcc.segment30)';
l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
l_string2 := 'SELECT
fv_sf1219_temp_s.NEXTVAL,
glb.je_batch_id,
ffp.fund_value,
glb.name,
:b_sob,
glb.posted_date,
gll.period_name,
TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period
NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
''MANUAL'', -- default value
NULL,
LTRIM(RTRIM(aid.invoice_id)),
LTRIM(RTRIM(aip.check_id)),
NULL,
NULL,
NULL,
LTRIM(RTRIM(aip.invoice_payment_id)),
NULL, -- reported month used for exceptions
glb.default_period_name,
NULL, -- exception_category updated when exception occurred
NULL, -- accomplish_month derived during the process
:b_end_date1, -- accomplish date
NULL, -- obligation_date derived during the process
NULL, -- ia flag updated during the process
fts.treasury_symbol, -- no fund_value for null value(06/15)
ffp.treasury_symbol_id, -- Added to fix Bug 1575992
''M'', -- Default record type as Manual
''N'', -- Default value for lines exist
fda.agency_location_code,
-1, --glb.org_id,
NULL, -- Group name assigned during the process
NULL, -- update type assigned during the process
NULL, -- type assigned during the process
:b_gl_period, -- gl period for which process is run
''N'', -- default processed flag
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
gll.je_header_id,
gll.je_line_num,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_sf1219_definitions_accts fda,
fv_fund_parameters ffp,
fv_treasury_symbols fts,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
ap_invoice_payments_all aip,
gl_import_references glir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl
WHERE gll.effective_date <= :b_end_date1
AND glh.currency_code = :b_currency_code
AND glb.status = ''P''
AND glb.actual_flag = ''A''
AND glb.je_batch_id = glh.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND gll.code_combination_id = gcc.code_combination_id
--AND gll.set_of_books_id = p_set_bks_id
AND gll.ledger_id = :b_sob
AND fda.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND fts.set_of_books_id = :b_sob
AND glh.je_source=''Payables''
AND glh.je_category <> ''Treasury Confirmation''
AND glh.je_from_sla_flag = ''Y''
AND ai.invoice_id = aid.invoice_id
AND aip.invoice_id = ai.invoice_id
AND glir.je_header_id = gll.je_header_id
AND glir.je_line_num = gll.je_line_num
AND xal.gl_sl_link_id = glir.gl_sl_link_id
AND xal.gl_sl_link_table = glir.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type IN (''AP_INV_DIST'',''AP_PREPAY'')
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND xdl.application_id = 200
AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
''SEGMENT2'', gcc.segment2,
''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4,
''SEGMENT5'', gcc.segment5,
''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7,
''SEGMENT8'', gcc.segment8,
''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10,
''SEGMENT11'', gcc.segment11,
''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13,
''SEGMENT14'', gcc.segment14,
''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16,
''SEGMENT17'', gcc.segment17,
''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19,
''SEGMENT20'', gcc.segment20,
''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22,
''SEGMENT23'', gcc.segment23,
''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25,
''SEGMENT26'', gcc.segment26,
''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28,
''SEGMENT29'', gcc.segment29,
''SEGMENT30'', gcc.segment30) = ffp.fund_value
and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
gcc.segment30)';
l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
for je_source is payables and je_category is non treasury');
l_string2 := 'SELECT
fv_sf1219_temp_s.NEXTVAL,
glb.je_batch_id,
ffp.fund_value,
glb.name,
:b_sob,
glb.posted_date,
gll.period_name,
TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) -NVL(gll.entered_cr,0),
''MANUAL'', -- default value
LTRIM(RTRIM(xdl.APPLIED_TO_SOURCE_ID_NUM_1)), --treasury confirmation id
NULL,
LTRIM(RTRIM(AIP.check_id)), --Check_id
LTRIM(RTRIM(aid.invoice_id)), --invoice_id
NULL,
LTRIM(RTRIM(aid.accounting_date)), --Accomplish date
NULL, --invoice_payment_id
NULL, -- reported month used for exceptions
glb.default_period_name,
NULL, -- exception_category updated when exception occurred
NULL, -- accomplish_month derived during the process
:b_end_date1, -- accomplish date
NULL, -- obligation_date derived during the process
NULL, -- ia flag updated during the process
fts.treasury_symbol, -- no fund_value for null value(06/15)
ffp.treasury_symbol_id, -- Added to fix Bug 1575992
''M'', -- Default record type as Manual
''N'', -- Default value for lines exist
fda.agency_location_code,
-1, --glb.org_id,
NULL, -- Group name assigned during the process
NULL, -- update type assigned during the process
NULL, -- type assigned during the process
:b_gl_period, -- gl period for which process is run
''N'', -- default processed flag
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
gll.je_header_id,
gll.je_line_num,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_sf1219_definitions_accts fda,
fv_fund_parameters ffp,
fv_treasury_symbols fts,
ap_invoice_distributions_all aid,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
gl_import_references glir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl
WHERE gll.effective_date <= :b_end_date1
AND glh.currency_code = :b_currency_code
AND glb.status = ''P''
AND glb.actual_flag = ''A''
AND glb.je_batch_id = glh.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND gll.code_combination_id = gcc.code_combination_id
--AND gll.set_of_books_id = p_set_bks_id
AND gll.ledger_id = :b_sob
AND fda.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND fts.set_of_books_id = :b_sob
AND glh.je_category = ''Treasury Confirmation''
AND glh.je_from_sla_flag = ''Y''
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND glir.je_header_id = gll.je_header_id
AND glir.je_line_num = gll.je_line_num
AND xal.gl_sl_link_id = glir.gl_sl_link_id
AND xal.gl_sl_link_table = glir.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xdl.event_id = xah.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type = ''FV_TREASURY_CONFIRMATIONS_ALL''
AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
AND aid.invoice_distribution_id = aphd.invoice_distribution_id
AND xdl.application_id = 8901
AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
''SEGMENT2'', gcc.segment2,
''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4,
''SEGMENT5'', gcc.segment5,
''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7,
''SEGMENT8'', gcc.segment8,
''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10,
''SEGMENT11'', gcc.segment11,
''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13,
''SEGMENT14'', gcc.segment14,
''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16,
''SEGMENT17'', gcc.segment17,
''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19,
''SEGMENT20'', gcc.segment20,
''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22,
''SEGMENT23'', gcc.segment23,
''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25,
''SEGMENT26'', gcc.segment26,
''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28,
''SEGMENT29'', gcc.segment29,
''SEGMENT30'', gcc.segment30) = ffp.fund_value
and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
gcc.segment30)';
l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
for je_source is payables and je_category is treasury confirmation');
l_string2 := 'SELECT
fv_sf1219_temp_s.NEXTVAL,
glb.je_batch_id,
ffp.fund_value,
glb.name,
:b_sob,
glb.posted_date,
gll.period_name,
TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
''MANUAL'', -- default value
LTRIM(RTRIM(xte.SOURCE_ID_INT_1)), -- expenditure_item_id
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL, -- reported month used for exceptions
glb.default_period_name,
NULL, -- exception_category updated when exception occurred
NULL, -- accomplish_month derived during the process
:b_end_date1, -- accomplish date
NULL, -- obligation_date derived during the process
NULL, -- ia flag updated during the process
fts.treasury_symbol, -- no fund_value for null value(06/15)
ffp.treasury_symbol_id, -- Added to fix Bug 1575992
''M'', -- Default record type as Manual
''N'', -- Default value for lines exist
fda.agency_location_code,
-1, --glb.org_id,
NULL, -- Group name assigned during the process
NULL, -- update type assigned during the process
NULL, -- type assigned during the process
:b_gl_period, -- gl period for which process is run
''N'', -- default processed flag
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
gll.je_header_id,
gll.je_line_num,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_sf1219_definitions_accts fda,
fv_fund_parameters ffp,
fv_treasury_symbols fts,
gl_import_references glir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl,
xla_transaction_entities xte
WHERE gll.effective_date <= :b_end_date1
AND glh.currency_code = :b_currency_code
AND glb.status = ''P''
AND glb.actual_flag = ''A''
AND glb.je_batch_id = glh.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND gll.code_combination_id = gcc.code_combination_id
--AND gll.set_of_books_id = p_set_bks_id
AND gll.ledger_id = :b_sob
AND fda.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND fts.set_of_books_id = :b_sob
AND glh.je_source=''Project Accounting''
AND glh.je_category = ''Labor Cost''
AND glh.je_from_sla_flag = ''Y''
AND glir.je_header_id = gll.je_header_id
AND glir.je_line_num = gll.je_line_num
AND xal.gl_sl_link_id = glir.gl_sl_link_id
AND xal.gl_sl_link_table = glir.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xte.entity_id = xet.entity_id
AND xte.entity_code =''EXPENDITURES''
AND xdl.APPLICATION_ID = 275
AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
''SEGMENT2'', gcc.segment2,
''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4,
''SEGMENT5'', gcc.segment5,
''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7,
''SEGMENT8'', gcc.segment8,
''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10,
''SEGMENT11'', gcc.segment11,
''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13,
''SEGMENT14'', gcc.segment14,
''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16,
''SEGMENT17'', gcc.segment17,
''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19,
''SEGMENT20'', gcc.segment20,
''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22,
''SEGMENT23'', gcc.segment23,
''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25,
''SEGMENT26'', gcc.segment26,
''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28,
''SEGMENT29'', gcc.segment29,
''SEGMENT30'', gcc.segment30) = ffp.fund_value
and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
gcc.segment30)';
l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
for je_source is project and je_category is labour_cost');
l_string2 := 'SELECT
fv_sf1219_temp_s.NEXTVAL,
glb.je_batch_id,
ffp.fund_value,
glb.name,
:b_sob,
glb.posted_date,
gll.period_name,
TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
''MANUAL'', -- default value
NULL,
LTRIM(RTRIM(arch.cash_receipt_id)),
NULL,
NULL,
LTRIM(RTRIM(arch.CASH_RECEIPT_HISTORY_ID)),
NULL,
NULL,
NULL, -- reported month used for exceptions
glb.default_period_name,
NULL, -- exception_category updated when exception occurred
NULL, -- accomplish_month derived during the process
:b_end_date1, -- accomplish date
NULL, -- obligation_date derived during the process
NULL, -- ia flag updated during the process
fts.treasury_symbol, -- no fund_value for null value(06/15)
ffp.treasury_symbol_id, -- Added to fix Bug 1575992
''M'', -- Default record type as Manual
''N'', -- Default value for lines exist
fda.agency_location_code,
-1, --glb.org_id,
NULL, -- Group name assigned during the process
NULL, -- update type assigned during the process
NULL, -- type assigned during the process
:b_gl_period, -- gl period for which process is run
''N'', -- default processed flag
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
gll.je_header_id,
gll.je_line_num,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_sf1219_definitions_accts fda,
fv_fund_parameters ffp,
fv_treasury_symbols fts,
ar_distributions_all ard,
ar_cash_receipt_history_all arch,
gl_import_references glir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl,
xla_transaction_entities xte
WHERE gll.effective_date <= :b_end_date1
AND glh.currency_code = :b_currency_code
AND glb.status = ''P''
AND glb.actual_flag = ''A''
AND glb.je_batch_id = glh.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND gll.code_combination_id = gcc.code_combination_id
--AND gll.set_of_books_id = p_set_bks_id
AND gll.ledger_id = :b_sob
AND fda.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND fts.set_of_books_id = :b_sob
AND glh.je_source=''Receivables''
AND glh.je_from_sla_flag = ''Y''
AND glir.je_header_id = gll.je_header_id
AND glir.je_line_num = gll.je_line_num
AND xal.gl_sl_link_id = glir.gl_sl_link_id
AND xal.gl_sl_link_table = glir.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xte.entity_id = xet.entity_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND xdl.source_distribution_id_num_1 = ard.line_id
AND ard.source_table=''CRH''
AND ard.source_id = arch.CASH_RECEIPT_HISTORY_ID
AND xdl.APPLICATION_ID = 222
AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
''SEGMENT2'', gcc.segment2,
''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4,
''SEGMENT5'', gcc.segment5,
''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7,
''SEGMENT8'', gcc.segment8,
''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10,
''SEGMENT11'', gcc.segment11,
''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13,
''SEGMENT14'', gcc.segment14,
''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16,
''SEGMENT17'', gcc.segment17,
''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19,
''SEGMENT20'', gcc.segment20,
''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22,
''SEGMENT23'', gcc.segment23,
''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25,
''SEGMENT26'', gcc.segment26,
''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28,
''SEGMENT29'', gcc.segment29,
''SEGMENT30'', gcc.segment30) = ffp.fund_value
and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
gcc.segment30)';
l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
for je_source is Receivables, based on ar_cash_receipt_history_all');
l_string2 := 'SELECT
fv_sf1219_temp_s.NEXTVAL,
glb.je_batch_id,
ffp.fund_value,
glb.name,
:b_sob,
glb.posted_date,
gll.period_name,
TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
''MANUAL'', -- default value
NULL,
LTRIM(RTRIM(arr.cash_receipt_id)),
NULL,
NULL,
LTRIM(RTRIM(arr.receivable_application_id)),
NULL,
NULL,
NULL, -- reported month used for exceptions
glb.default_period_name,
NULL, -- exception_category updated when exception occurred
NULL, -- accomplish_month derived during the process
:b_end_date1, -- accomplish date
NULL, -- obligation_date derived during the process
NULL, -- ia flag updated during the process
fts.treasury_symbol, -- no fund_value for null value(06/15)
ffp.treasury_symbol_id, -- Added to fix Bug 1575992
''M'', -- Default record type as Manual
''N'', -- Default value for lines exist
fda.agency_location_code,
-1, --glb.org_id,
NULL, -- Group name assigned during the process
NULL, -- update type assigned during the process
NULL, -- type assigned during the process
:b_gl_period, -- gl period for which process is run
''N'', -- default processed flag
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
gll.je_header_id,
gll.je_line_num,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_sf1219_definitions_accts fda,
fv_fund_parameters ffp,
fv_treasury_symbols fts,
ar_distributions_all ard,
AR_RECEIVABLE_APPLICATIONS_ALL arr,
gl_import_references glir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl,
xla_transaction_entities xte
WHERE gll.effective_date <= :b_end_date1
AND glh.currency_code = :b_currency_code
AND glb.status = ''P''
AND glb.actual_flag = ''A''
AND glb.je_batch_id = glh.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND gll.code_combination_id = gcc.code_combination_id
--AND gll.set_of_books_id = p_set_bks_id
AND gll.ledger_id = :b_sob
AND fda.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND fts.set_of_books_id = :b_sob
AND glh.je_source=''Receivables''
AND glh.je_from_sla_flag = ''Y''
AND glir.je_header_id = gll.je_header_id
AND glir.je_line_num = gll.je_line_num
AND xal.gl_sl_link_id = glir.gl_sl_link_id
AND xal.gl_sl_link_table = glir.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xte.entity_id = xet.entity_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND xdl.source_distribution_id_num_1 = ard.line_id
AND ard.source_table=''RA''
AND ard.source_id = arr.receivable_application_id
AND xdl.APPLICATION_ID = 222
AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
''SEGMENT2'', gcc.segment2,
''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4,
''SEGMENT5'', gcc.segment5,
''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7,
''SEGMENT8'', gcc.segment8,
''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10,
''SEGMENT11'', gcc.segment11,
''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13,
''SEGMENT14'', gcc.segment14,
''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16,
''SEGMENT17'', gcc.segment17,
''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19,
''SEGMENT20'', gcc.segment20,
''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22,
''SEGMENT23'', gcc.segment23,
''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25,
''SEGMENT26'', gcc.segment26,
''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28,
''SEGMENT29'', gcc.segment29,
''SEGMENT30'', gcc.segment30) = ffp.fund_value
and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
gcc.segment30)';
l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
for je_source is Receivables , based on AR_RECEIVABLE_APPLICATIONS_ALL ');
l_string2 := 'SELECT
fv_sf1219_temp_s.NEXTVAL,
glb.je_batch_id,
ffp.fund_value,
glb.name,
:b_sob,
glb.posted_date,
gll.period_name,
TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
''MANUAL'', -- default value
NULL,
LTRIM(RTRIM(arm.cash_receipt_id)),
NULL,
NULL,
LTRIM(RTRIM(arm.MISC_CASH_DISTRIBUTION_ID)),--cash_receipt_hist_id
NULL,
NULL,
NULL, -- reported month used for exceptions
glb.default_period_name,
NULL, -- exception_category updated when exception occurred
NULL, -- accomplish_month derived during the process
:b_end_date1, -- accomplish date
NULL, -- obligation_date derived during the process
NULL, -- ia flag updated during the process
fts.treasury_symbol, -- no fund_value for null value(06/15)
ffp.treasury_symbol_id, -- Added to fix Bug 1575992
''M'', -- Default record type as Manual
''N'', -- Default value for lines exist
fda.agency_location_code,
-1, --glb.org_id,
NULL, -- Group name assigned during the process
NULL, -- update type assigned during the process
NULL, -- type assigned during the process
:b_gl_period, -- gl period for which process is run
''N'', -- default processed flag
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
gll.je_header_id,
gll.je_line_num,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_sf1219_definitions_accts fda,
fv_fund_parameters ffp,
fv_treasury_symbols fts,
ar_distributions_all ard,
AR_MISC_CASH_DISTRIBUTIONS_ALL arm,
gl_import_references glir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xet,
xla_distribution_links xdl,
xla_transaction_entities xte
WHERE gll.effective_date <= :b_end_date1
AND glh.currency_code = :b_currency_code
AND glb.status = ''P''
AND glb.actual_flag = ''A''
AND glb.je_batch_id = glh.je_batch_id
AND glh.je_header_id = gll.je_header_id
AND gll.code_combination_id = gcc.code_combination_id
--AND gll.set_of_books_id = p_set_bks_id
AND gll.ledger_id = :b_sob
AND fda.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND fts.set_of_books_id = :b_sob
AND glh.je_source=''Receivables''
AND glh.je_from_sla_flag = ''Y''
AND glir.je_header_id = gll.je_header_id
AND glir.je_line_num = gll.je_line_num
AND xal.gl_sl_link_id = glir.gl_sl_link_id
AND xal.gl_sl_link_table = glir.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xet.event_id = xah.event_id
AND xte.entity_id = xet.entity_id
AND xdl.event_id = xet.event_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
AND xdl.source_distribution_id_num_1 = ard.line_id
AND ard.source_id = arm.MISC_CASH_DISTRIBUTION_ID
AND ard.source_table=''MCD''
AND xdl.APPLICATION_ID = 222
AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
''SEGMENT2'', gcc.segment2,
''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4,
''SEGMENT5'', gcc.segment5,
''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7,
''SEGMENT8'', gcc.segment8,
''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10,
''SEGMENT11'', gcc.segment11,
''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13,
''SEGMENT14'', gcc.segment14,
''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16,
''SEGMENT17'', gcc.segment17,
''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19,
''SEGMENT20'', gcc.segment20,
''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22,
''SEGMENT23'', gcc.segment23,
''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25,
''SEGMENT26'', gcc.segment26,
''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28,
''SEGMENT29'', gcc.segment29,
''SEGMENT30'', gcc.segment30) = ffp.fund_value
and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
gcc.segment30)';
l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
for je_source is Receivables , based on AR_MISC_CASH_DISTRIBUTIONS_ALL ');
SELECT count(*)
INTO no_of_tran
FROM fv_sf1219_temp
WHERE set_of_books_id = p_set_bks_id ;
'2. NUMBER OF ROWS INSERTED: '||no_of_tran);
p_error_msg := SQLERRM || ' -- Error in INSERT_BATCHES procedure.';
END INSERT_BATCHES;
INSERT INTO fv_sf1219_temp (
temp_record_id,
batch_id,
fund_code,
name,
set_of_books_id,
gl_period,
reported_gl_period,
reported_month,
record_type,
lines_exist,
alc_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (
fv_sf1219_temp_s.NEXTVAL,
0,
'NO FUND',
'P Batch',
p_set_bks_id,
p_gl_period,
TO_CHAR(l_start_date1,'MMYYYY'),
TO_CHAR(l_start_date1,'MMYYYY'),
'P',
'N',
p_alc_code,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID);
SELECT count(*)
INTO transaction_count
FROM fv_sf1219_temp
WHERE record_type NOT IN ('P', 'N');
SELECT payables_ia_paygroup
INTO p_def_p_ia_paygroup
FROM FV_Operating_units_all
WHERE org_id = p_def_org_id;
l_update_type,
l_type,
l_gl_period_name,
l_processed_flag,
l_je_header_id,
l_je_line_num,
l_je_from_sla_flag ;
l_module_name,'L_UPDATE_TYPE: '||L_UPDATE_TYPE);
l_update_type := null;
SELECT je_source, je_category
INTO v_je_source, v_je_category
FROM gl_je_headers
WHERE je_header_id = l_je_header_id;
SELECT 'Y'
INTO l_exists
FROM Fv_treasury_confirmations_all
WHERE treasury_confirmation_id = to_number(l_reference_1)
AND org_id = p_def_org_id;
' NO_DATA_FOUND WHEN SELECTING FROM '||
'FV_TREASURY_CONFIRMATIONS_ALL WITH REF_1, HENCE MANUAL');
SELECT 'Y'
INTO l_exists
FROM ap_checks_all
WHERE check_id = to_number(l_REFERENCE_3)
AND org_id = p_def_org_id;
' NO_DATA_FOUND WHEN SELECTING '||
'FROM AP_CHECKS_ALL WITH REF_3, HENCE MANUAL');
SELECT 'Y'
INTO l_exists
FROM ap_invoices_all
WHERE invoice_id = TO_NUMBER(l_reference_2)
AND org_id = p_def_org_id;
' NO_DATA_FOUND WHEN SELECTING' ||
' FROM AP_INVOICES_ALL WITH REF_2, '||
' HENCE MANUAL');
SELECT 'Y'
INTO l_exists
FROM ap_checks_all
WHERE check_id = to_number(l_REFERENCE_3)
AND org_id = p_def_org_id;
' SELECTING FROM AP_CHECKS_ALL '||
' WITH REF_3, HENCE MANUAL');
SELECT 'Y'
INTO l_exists
FROM ap_invoice_payments_all
WHERE invoice_payment_id = to_number(l_REFERENCE_9)
AND org_id = p_def_org_id;
' WHEN SELECTING FROM ' ||
' AP_INVOICE_PAYMENTS_ALL ' ||
' WITH REF_9, HENCE MANUAL');
SELECT 'Y'
INTO l_exists
FROM Ar_Cash_Receipts_All
WHERE cash_receipt_id = to_number(l_cash_receipt_id)
AND org_id = p_def_org_id;
|| 'WHEN SELECTING FROM '||
'AR_CASH_RECEIPTS_ALL, ' ||
' HENCE MANUAL');
SELECT 'Y'
INTO l_exists
FROM Ar_Cash_Receipt_History_All
WHERE cash_receipt_history_id =
to_number(l_cash_receipt_hist_id)
AND org_id = p_def_org_id;
SELECT cash_receipt_history_id
INTO l_temp_cr_hist_id
FROM Ar_Receivable_Applications_All
WHERE receivable_application_id =
TO_NUMBER(l_cash_receipt_hist_id)
AND org_id = p_def_org_id;
SELECT 'Y'
INTO l_exists
FROM Ar_Cash_Receipt_History_All
WHERE cash_receipt_history_id =
TO_NUMBER(l_cash_receipt_hist_id)
AND org_id = p_def_org_id;
' WHEN SELECTING FROM '||
' AR_CASH_RECEIPT_HISTORY_ALL, ' ||
'HENCE MANUAL');
' SELECTING FROM AR_RECEIVABLE' ||
'_APPLICATIONS_ALL, HENCE MANUAL');
SELECT 'Y'
INTO l_exists
FROM Ar_Misc_Cash_Distributions_All
WHERE misc_cash_distribution_id =
TO_NUMBER(l_cash_receipt_hist_id)
AND org_id = p_def_org_id;
' SELECTING FROM AR_MISC_CASH_' ||
'DISTRIBUTIONS_ALL, HENCE MANUAL');
SELECT cba.agency_location_code
INTO l_temp_alc_code
FROM ce_bank_accounts cba,
ar_cash_receipts_all acr,
ce_bank_acct_uses_all cbau
WHERE acr.cash_receipt_id = l_cash_receipt_id --l_reference_2
AND acr.remit_bank_acct_use_id = cbau.bank_acct_use_id
AND cba.bank_account_id = cbau.bank_account_id
AND cbau.org_id = p_def_org_id
AND cba.account_owner_org_id = cbau.org_id
AND cbau.org_id = acr.org_id;
SELECT cba.agency_location_code
INTO l_temp_alc_code
FROM Fv_Treasury_Confirmations_all ftc,
Ap_Inv_Selection_Criteria_all aisc,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
WHERE ftc.treasury_confirmation_id = to_number(l_reference_1)
AND aisc.checkrun_name = ftc.checkrun_name
AND cba.bank_account_id = aisc.bank_account_id
AND cba.bank_account_id = cbau.bank_account_id
AND cbau.org_id = p_def_org_id
AND cba.account_owner_org_id = cbau.org_id
AND cbau.org_id = ftc.org_id
AND ftc.org_id = aisc.org_id;
SELECT cba.agency_location_code
INTO l_temp_alc_code
FROM ap_checks apa,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
WHERE TO_CHAR(apa.check_id) = l_reference_3
AND apa.bank_account_id = cba.bank_account_id
AND apa.ce_bank_acct_use_id = cbau.bank_acct_use_id
AND apa.bank_Account_id = cbau.bank_account_id
AND cbau.org_id = p_def_org_id
AND cba.bank_account_id = cbau.bank_account_id
AND cba.account_owner_org_id = cbau.org_id
AND cbau.org_id = apa.org_id;
SELECT distinct org_id
INTO l_org_id
FROM ap_invoice_payments_all
WHERE invoice_id = to_number(l_reference_2);
SELECT payables_ia_paygroup
INTO l_payables_ia_paygroup
FROM fv_operating_units_all
WHERE org_id = l_org_id;
SELECT api.invoice_id,
api.vendor_id,
api.invoice_amount,
nvl(apc.treasury_pay_date,apc.check_date)
INTO l_invoice_id ,
l_vendor_id,
l_inv_amount,
l_check_date
FROM ap_checks_all apc,
ap_invoices_all api
WHERE api.invoice_id = NVL(l_reference, 0)
AND apc.check_id = to_number(l_reference_3)
AND l_payables_ia_paygroup = api.pay_group_lookup_code
AND apc.payment_method_lookup_code = 'CLEARING';
'info,dit select';
SELECT cba.agency_location_code
INTO l_temp_alc_code
FROM ap_invoice_payments_all aip,
ap_checks_all aca,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
WHERE TO_CHAR(aip.invoice_id) = l_reference_2
AND aca.check_id = l_reference_3
AND aip.set_of_books_id = p_set_bks_id
AND aip.check_id = aca.check_id
AND aca.bank_account_id = cba.bank_account_id
AND aca.ce_bank_acct_use_id = cbau.bank_acct_use_id
AND cba.bank_account_id = cbau.bank_account_id
AND cbau.org_id = p_def_org_id
AND cba.account_owner_org_id = cbau.org_id
AND cbau.org_id = aip.org_id
AND aip.org_id = aca.org_id
AND rownum < 2;
SELECT 'X'
INTO null_var
FROM FV_INTERAGENCY_FUNDS_ALL
WHERE cash_receipt_id = l_cash_receipt_id
and org_id = p_def_org_id;
l_update_type := 'RECEIPT';
'Too many rows in interagnecy select' ||
' for cash receipt '|| to_char(l_cash_receipt_id)||
' for Batch id '|| to_char(l_batch_id);
SELECT 'Y'
INTO l_exists
FROM ar_cash_receipt_history_all
WHERE reversal_cash_receipt_hist_id = l_cash_receipt_hist_id
AND org_id = p_def_org_id;
SELECT DECODE(l_exists,'N',deposit_date,reversal_date)
INTO l_accomplish_date
FROM ar_cash_receipts_all
WHERE cash_receipt_id = l_cash_receipt_id
ANd org_id = p_def_org_id;
l_update_type := 'RECEIPT';
-- Insert new record as record_type 'A'. (changes on 7-Jun-1999)
-- This was being inserted as 'E'
BEGIN
INSERT INTO FV_SF1219_TEMP(
temp_record_id,
batch_id,
fund_code,
name,
set_of_books_id,
posted_date,
gl_period,
reported_gl_period,
amount,
sf1219_type_code,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reported_month,
default_period_name,
exception_category,
accomplish_month,
accomplish_date,
obligation_date,
inter_agency_flag,
treasury_symbol,
treasury_symbol_id,
record_type,
lines_exist,
alc_code,
org_id,
group_name,
update_type,
type,
gl_period_name,
processed_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
je_header_id,
je_line_num)
VALUES(
fv_sf1219_temp_s.nextval,
l_batch_id,
l_fund_code,
l_name,
p_set_bks_id,
l_posted_date,
l_gl_period,
l_reported_gl_period,
l_inv_amount,
l_sf1219_type_code,
l_reference_1,
l_reference_2,
l_reference_3,
l_reference_4,
l_reference_5,
l_reference_6,
l_reported_month,
l_default_period_name,
l_exception_category,
l_accomplish_month,
l_accomplish_date,
l_obligation_date,
l_inter_agency_flag,
l_treasury_symbol,
l_treasury_symbol_id,
'A' ,
'N',
l_alc_code,
-1, --l_org_id,
l_group_name,
l_update_type,
l_type,
l_gl_period_name,
l_processed_flag,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_je_header_id,
l_je_line_num ) ;
SELECT treasury_doc_date
INTO l_accomplish_date
FROM fv_treasury_confirmations_all
WHERE TO_CHAR(treasury_confirmation_id) = l_reference_1
AND org_id = p_def_org_id;
'Too many rows in treasury_doc_date select for ' ||
'treasury confirmation id '||substr(l_reference_6,1,20)||
' for Batch id '|| to_char(l_batch_id) ;
SELECT chargeback_flag,
iaf.billing_agency_fund
INTO l_cb_flag,
l_billing_agency_fund
FROM fv_interagency_funds_all iaf
WHERE iaf.vendor_id = l_vendor_id
AND iaf.invoice_id = l_invoice_id
AND iaf.org_id = p_def_org_id;
insert_exceptions(l_amount);
flag Prelim select';
SELECT nvl(apc.treasury_pay_date,apc.check_date),
apc.void_date
INTO l_accomplish_date,
l_void_date
FROM ap_checks_all apc,
ap_invoices_all api
WHERE api.invoice_id = Nvl(l_reference, 0)
AND apc.check_id = nvl(l_reference_3,0)
AND apc.org_id = p_def_org_id
AND api.org_id = p_def_org_id;
SELECT NVL(MAX(invoice_payment_id),0)
INTO l_inv_pay_id
FROM ap_invoice_payments
WHERE invoice_id = NVL(l_reference, 0)
AND check_id = NVL(l_reference_3,0)
AND invoice_payment_id >l_reference_9;
SELECT obligation_date
INTO l_obligation_date
FROM fv_refunds_voids_all
WHERE type = 'VOID'
AND invoice_id = to_number(l_reference_2)
AND check_id = to_number(l_reference_3)
AND org_id = p_def_org_id;
l_update_type := 'VOID_PAYABLE';
INSERT_EXCEPTIONS(l_org_amount);
' obligation_date select';
' disbursement select';
insert_exceptions(l_amount);
SELECT void_date
INTO l_accomplish_date
FROM ap_checks_all
WHERE TO_CHAR(check_id) = NVL(l_reference_3,'0')
ANd org_id = p_def_org_id;
SELECT invoice_date into l_invoice_date
FROM AP_INVOICES_ALL
WHERE TO_CHAR(invoice_id) = NVL(l_reference_4,'0')
ANd org_id = p_def_org_id;
-- Call procedure to insert exception tranasctions
INSERT_EXCEPTIONS(l_org_amount) ;
UPDATE fv_sf1219_temp
SET record_type = l_record_type,
sf1219_type_code = 'MANUAL',
alc_code = l_alc_code,
accomplish_date = l_accomplish_date
WHERE rowid = l_rowid;
SELECT obligation_date
INTO l_obligation_date
FROM FV_REFUNDS_VOIDS_ALL
WHERE type = 'VOID'
AND TO_CHAR(invoice_id) = l_reference_2
AND TO_CHAR(check_id) = l_reference_3
AND org_id = p_def_org_id;
l_update_type := 'VOID_PAYABLE' ;
-- Call procedure to insert exception tranasctions
INSERT_EXCEPTIONS(l_org_amount) ;
UPDATE fv_sf1219_temp
SET record_type = l_record_type,
sf1219_type_code = 'VOID',
alc_code = l_alc_code
WHERE rowid = l_rowid;
p_error_msg := 'Too many rows in obligation_date select '||
'For JE batch id '||to_char(l_batch_id);
SELECT end_date
INTO l_accomplish_date
FROM gl_periods glp, gl_sets_of_books gsob
WHERE glp.period_name = l_gl_period
AND glp.period_type = period_type
AND gsob.set_of_books_id = p_set_bks_id
AND gsob.chart_of_accounts_id = flex_num
AND glp.period_set_name = gsob.period_set_name ;
INSERT_EXCEPTIONS(x_amount) ;
INSERT_EXCEPTIONS(x_amount) ;
INSERT_EXCEPTIONS(x_amount) ;
UPDATE fv_sf1219_temp
set sf1219_type_code = l_sf1219_type_code,
reported_gl_period = l_reported_gl_period ,
reported_month = l_reported_month,
exception_category = l_exception_category,
accomplish_month = l_accomplish_month,
accomplish_date = l_accomplish_date,
obligation_date = l_obligation_date,
inter_agency_flag = l_inter_agency_flag,
record_type = l_record_type,
alc_code = l_alc_code,
amount = l_org_amount,
reference_2 = l_reference_2,
lines_exist = l_lines_exist,
--org_id = l_org_id,
group_name = l_group_name,
update_type = l_update_type,
type = l_type,
gl_period_name = p_gl_period,
processed_flag = l_processed_flag
WHERE rowid = l_rowid ;
p_error_msg := 'fv_sf1219_temp update failed' ;
DELETE from FV_SF1219_TEMP
WHERE record_type not in ('P')
AND alc_code IS NOT NULL
AND alc_code <> p_alc_code;
UPDATE fv_sf1219_temp fst
SET supplement_number =
(SELECT NVL(MAX(supplement_number), -1) + 1
FROM fv_sf1219_audits fsa
WHERE fst.alc_code = fsa.alc_code
AND gl_period = p_gl_period);
SELECT start_date, end_date
INTO l_start_date2, l_end_date2
FROM GL_PERIODS glp, GL_SETS_OF_BOOKS gsob
WHERE glp.period_name = l_gl_period_name
AND glp.period_type = period_type
AND gsob.set_of_books_id = p_set_bks_id
AND gsob.chart_of_accounts_id = flex_num
AND glp.period_set_name = gsob.period_set_name ;
PROCEDURE INSERT_EXCEPTIONS (x_amount NUMBER) IS
l_module_name VARCHAR2(200) := g_module_name || 'INSERT_EXCEPTIONS';
INSERT INTO fv_sf1219_temp(
temp_record_id,
batch_id,
fund_code,
name,
set_of_books_id,
posted_date,
gl_period,
reported_gl_period,
amount,
sf1219_type_code,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reported_month,
default_period_name,
exception_category,
accomplish_month,
accomplish_date,
obligation_date,
inter_agency_flag,
treasury_symbol,
treasury_symbol_id, --Added to fix Bug. 1575992
record_type,
lines_exist,
alc_code,
org_id,
group_name,
update_type,
type,
gl_period_name,
processed_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
je_header_id,
je_line_num)
VALUES(
fv_sf1219_temp_s.nextval,
l_batch_id,
l_fund_code,
l_name_keep,
p_set_bks_id,
l_posted_date,
l_gl_period,
l_reported_gl_period,
x_amount,
l_sf1219_type_code,
l_reference_1,
l_reference_2,
l_reference_3,
l_reference_4,
l_reference_5,
l_reference_6,
l_reported_month,
l_default_period_name,
l_exception_category,
l_accomplish_month,
l_accomplish_date,
l_obligation_date,
l_inter_agency_flag,
l_treasury_symbol,
l_treasury_symbol_id,
'E' ,
'N',
l_alc_code,
--l_org_id,
-1,
null,
l_update_type,
l_type,
l_gl_period_name,
l_processed_flag,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_je_header_id,
l_je_line_num ) ;
'INSERT_EXCEPTIONS procedure.';
END INSERT_EXCEPTIONS;
SELECT obligation_date into l_obligation_date
FROM fv_refunds_voids_all
WHERE type = 'VOID'
AND TO_CHAR(invoice_id) = l_reference_2
AND TO_CHAR(check_id) = l_reference_3
AND org_id = p_def_org_id;
l_update_type := 'VOID_PAYABLE' ;
INSERT_EXCEPTIONS(l_amount);
INSERT_EXCEPTIONS(l_amount);
INSERT_EXCEPTIONS(l_amount);
UPDATE fv_sf1219_temp
SET sf1219_type_code = l_sf1219_type_code,
reported_month = l_reported_month,
exception_category = l_exception_category,
accomplish_month = to_char(l_accomplish_date,'MMYYYY'),
accomplish_date = l_accomplish_date,
obligation_date = l_obligation_date,
record_type = l_record_type,
inter_agency_flag = l_inter_agency_flag,
group_name = l_group_name,
lines_exist = l_lines_exist,
update_type = l_update_type,
type = l_type,
processed_flag = l_processed_flag
WHERE reference_2 = l_reference_2
AND reference_3 = l_reference_3
AND name <> 'Check for Void'
AND record_type = 'A';
DELETE fv_sf1219_temp
WHERE reference_2 = l_reference_2
AND reference_3 = l_reference_3
AND name = 'Check for Void';
UPDATE fv_sf1219_temp
SET record_type = 'E',
exception_category = 'VOID_MISSING_FRV',
treasury_symbol = 'UNDEFINED'
WHERE reference_2 = l_reference_2
AND reference_3 = l_reference_3
AND name <> 'Check for Void';
p_error_msg := 'Too many rows in obligation_date select' ;
DELETE FROM FV_SF1219_ORG_TEMP;
INSERT into FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, fvt.alc_code,
substr(fvt.group_name,1,3),
sum(fvt.amount * fvr.multiplier)
FROM FV_SF1219_TEMP fvt,
FV_SF1219_REPORT_TEMPLATE fvr
WHERE fvt.alc_code is not null
AND ( fvt.record_type = 'A' OR
(fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
AND substr(fvt.group_name,1,3) = fvr.line_id
GROUP BY fvt.alc_code, substr(fvt.group_name,1,3) ;
INSERT into FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, fvt.alc_code, fvm.line_id, sum(fvt.amount *
DECODE(fvt.record_type, 'N', 1,fvr.multiplier))
FROM FV_SF1219_TEMP fvt,
FV_SF1219_MANUAL_LINES fvm,
FV_SF1219_REPORT_TEMPLATE fvr
WHERE fvt.alc_code IS NOT NULL
AND ((fvt.record_type IN ('M', 'N')
OR (fvt.record_type = 'R' AND fvt.group_name IS NULL))
AND fvt.temp_record_id = fvm.temp_record_id )
AND fvm.line_id = fvr.line_id
GROUP BY fvt.alc_code, fvm.line_id;
INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, fvt.alc_code, '410' line_id, sum(fvt.amount * -1)
FROM FV_SF1219_TEMP fvt
WHERE fvt.alc_code is not null
AND fvt.group_name is not null
AND ( fvt.record_type = 'A' OR
(fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
GROUP BY fvt.alc_code;
SELECT alc_code INTO v_alc_code
FROM fv_sf1219_temp
WHERE record_type = 'P' ;
INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, fvam.alc_code, '100'
line_id, fvam.accountability_balance
FROM FV_SF1219_AUDITS fvam
WHERE fvam.reported_gl_period = (
select to_char(max(
to_date(fvas.reported_gl_period,'MM-YYYY')),'MMYYYY')
from fv_sf1219_audits fvas
where fvas.alc_code = fvam.alc_code
and fvas.record_type = 'B' )
AND fvam.record_type = 'B'
AND fvam.alc_code = DECODE(UPPER(v_alc_code),'ALL',alc_code,
v_alc_code);
INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, alc_code, '290' line_id, sum(amount)
FROM FV_SF1219_ORG_TEMP
WHERE line_id in ('210','211','212','234','236','237','280')
GROUP BY alc_code;
INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, alc_code, '300' line_id, sum(amount)
FROM FV_SF1219_ORG_TEMP
WHERE line_id in ('100','290')
GROUP BY alc_code;
INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, alc_code, '490' line_id, sum(amount)
FROM FV_SF1219_ORG_TEMP
WHERE line_id in ('410','420','434','436','437')
GROUP BY alc_code;
INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, alc_code, '500' line_id,
sum(decode(line_id, '490',amount * -1, amount))
FROM FV_SF1219_ORG_TEMP
WHERE line_id in ('300','490')
GROUP BY alc_code;
INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, alc_code, '800' line_id, sum(amount)
FROM FV_SF1219_ORG_TEMP
WHERE line_id in ('610','620','650','700')
GROUP BY alc_code;
INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
SELECT p_def_org_id, alc_code, '990' line_id, sum(amount)
FROM FV_SF1219_ORG_TEMP
WHERE line_id in ('800','900')
GROUP BY alc_code;
PROCEDURE INSERT_AUDIT_TABLE(v_alc_code VARCHAR2) IS
l_module_name VARCHAR2(200) := g_module_name || 'INSERT_AUDIT_TABLE';
l2_update_type FV_SF1219_TEMP.update_type%TYPE ;
SELECT batch_id,
reference_2,
reference_3,
reported_month,
inter_agency_flag,
update_type,
type,
gl_period_name,
processed_flag
FROM FV_SF1219_TEMP
WHERE (update_type is not null
OR type is not null )
AND alc_code = v_alc_code
ORDER BY batch_id ;
SELECT gl_period
INTO p_gl_period
FROM fv_sf1219_temp
WHERE record_type = 'P'
ORDER BY gl_period;
' INSERT_AUDIT_TABLE procedure while finding GL period.';
SELECT set_of_books_id
INTO p_set_bks_id
FROM fv_sf1219_temp
WHERE rownum = 1;
SELECT chart_of_accounts_id
INTO flex_num
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_bks_id;
SELECT end_date
INTO l_end_date
FROM gl_periods glp,
gl_sets_of_books gsob
WHERE glp.period_name = p_gl_period
AND gsob.set_of_books_id = p_set_bks_id
AND gsob.chart_of_accounts_id = flex_num
AND glp.period_set_name = gsob.period_set_name;
' INSERT_AUDIT_TABLE procedure while ' ||
' finding SoB, CoA and period end date.';
SELECT NVL(MAX(supplement_number),-1) + 1
INTO v_supp_number
FROM fv_sf1219_audits
WHERE alc_code = v_alc_code
AND reported_gl_period = l_reported_period;
INSERT INTO fv_sf1219_audits (
batch_id,
sf1219_type_code,
exception_category,
gl_period,
reported_gl_period,
treasury_symbol_id,
accountability_balance,
org_id,
record_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
je_header_id,
je_line_num,
inter_agency_flag,
alc_code,
supplement_number)
SELECT fvt.batch_id,
fvt.sf1219_type_code,
fvt.exception_category,
fvt.gl_period,
fvt.reported_gl_period,
fvt.treasury_symbol_id,--Added to fix Bug. 1575992
null,
-- l_org_id,
-1,
fvt.record_type,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
fvt.je_header_id,
fvt.je_line_num,
fvt.inter_agency_flag,
alc_code,
v_supp_number --supplement_number
FROM FV_SF1219_TEMP fvt
WHERE fvt.reported_month in ('CURRENT / PRIOR','CURRENT')
AND fvt.alc_code = v_alc_code --is not null
AND ( (fvt.record_type = 'A' OR (fvt.record_type = 'R' AND
fvt.group_name IS NOT NULL))
OR ((fvt.record_type = 'M' OR
(fvt.record_type = 'R' AND fvt.group_name IS NULL))
AND fvt.temp_record_id IN
(SELECT temp_record_id
FROM fv_sf1219_manual_lines)) ) ;
l2_update_type,
l2_type,
l2_gl_period_name,
l2_processed_flag ;
UPDATE fv_interagency_funds_all
SET processed_flag = 'Y',
period_reported = l2_gl_period_name
WHERE decode(l2_update_type, 'RECEIPT',
cash_receipt_id, invoice_id)
= to_number(l2_reference_2)
AND processed_flag = 'N'
AND org_id = p_def_org_id;
UPDATE fv_refunds_voids_all
SET processed_flag = 'Y',
period_reported = l2_gl_period_name
WHERE decode(l2_update_type, 'RECEIPT',
cash_receipt_id, invoice_id)
= to_number(l2_reference_2)
AND type = l2_type
AND nvl(check_id,0) = decode(l2_update_type,'RECEIPT',
nvl(check_id,0),
to_number(l2_reference_3))
AND org_id = p_def_org_id;
p_error_msg := 'fv_Sf1219_temp table update failed ' ;
p_error_msg := SQLERRM || ' -- Error in INSERT_AUDIT_TABLE procedure.';
END INSERT_AUDIT_TABLE;
PROCEDURE INSERT_ACCOUNTABILITY_BALANCE (p_rep_gl_period IN VARCHAR2,
p_cl_balance IN NUMBER,
p_alc_code IN VARCHAR2) IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'INSERT_ACCOUNTABILITY_BALANCE';
UPDATE FV_SF1219_AUDITS
set accountability_balance = p_cl_balance,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where reported_gl_period = p_rep_gl_period
and alc_code = p_alc_code
and record_type = 'B' ;
INSERT into FV_SF1219_AUDITS (
batch_id,
reported_gl_period,
accountability_balance,
alc_code,
record_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_id,
treasury_symbol_id)
values (100, -- some batch id for not null column
p_rep_gl_period, -- gl period in MMYYYY format
p_cl_balance, --amount against line 500 of report
p_alc_code,
'B',
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
-1, --l_org_id,
-2); --This is a dummy value needed for bug# 3537243
'INSERT_ACCOUNTABILITY_BALANCE procedure.';
END INSERT_ACCOUNTABILITY_BALANCE;
SELECT fvr.line_id v_line_id, SUM(DECODE(fvr.line_type,
'A', fvo.amount,
'T', fvo.amount,
'B',fvo.amount,0)) v_amt
FROM fv_sf1219_report_template fvr,
fv_sf1219_org_temp fvo
WHERE fvr.line_id = fvo.line_id
AND fvo.alc_code = v_alc_code
AND line_type <> 'D'
GROUP BY fvr.line_id
UNION
SELECT line_id,0
FROM fv_sf1219_report_template
WHERE line_id NOT IN
(SELECT line_id FROM fv_sf1219_org_temp
WHERE alc_code = v_alc_code)
AND line_type <> 'D'
GROUP BY line_id;
SELECT SUM(grp_amount) group_amount, alc alc_code
FROM (SELECT SUM(decode(fvt.record_type,'N', fvt.amount,
fvt.amount*fvr.multiplier))
grp_amount, fvt.alc_code alc
FROM fv_sf1219_report_template fvr,
fv_sf1219_temp fvt
WHERE substr(fvt.group_name,1,3) = fvr.line_id
AND (fvt.record_type IN ('A') OR
(fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
AND fvt. alc_code = v_alc_code
AND SUBSTR(fvt.group_name,1,3) = '210'
GROUP BY fvt.alc_code, fvr.line_id
UNION
SELECT SUM(decode(fvt.record_type,'N', fvt.amount,
fvt.amount*fvr.multiplier))
grp_amount, fvt.alc_code alc
FROM fv_sf1219_report_template fvr,
fv_sf1219_temp fvt,
fv_sf1219_manual_lines fvm
WHERE fvm.line_id = fvr.line_id
AND fvt.temp_record_id = fvm.temp_record_id
AND (fvt.record_type IN ('M','N') OR
(fvt.record_type = 'R' AND fvt.group_name IS NULL))
AND fvt.alc_code = v_alc_code
AND fvm.line_id = '210'
GROUP BY fvt.alc_code, fvm.line_id)
GROUP BY alc;
SELECT alc alc_code, l_num line_num, acc_mon accomplish_month,
SUM(grp_amt) group_amount
FROM (SELECT fvt.alc_code alc, SUBSTR(fvt.group_name,1,3) l_num,
to_char(to_date(fvt.accomplish_month,'MMYYYY'),'MM/YY') acc_mon,
SUM(decode(fvt.record_type,'N', fvt.amount,
fvt.amount*fvr.multiplier)) grp_amt
FROM fv_sf1219_report_template fvr, fv_sf1219_temp fvt
WHERE substr(fvt.group_name,1,3) = fvr.line_id
AND (fvt.record_type IN ('A') OR
(fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
AND fvt.alc_code = v_alc_code
AND SUBSTR(fvt.group_name,1,3) IN ('211','212','280','420')
GROUP BY fvt.alc_code, SUBSTR(fvt.group_name,1,3), -- fvr.line_id,
to_char(to_date(fvt.accomplish_month,'MMYYYY'),'MM/YY')
UNION
SELECT fvt.alc_code alc, fvm.line_id l_num,
to_char(to_date(fvt.accomplish_month,'MMYYYY'),'MM/YY') acc_mon,
SUM(decode(fvt.record_type,'N', fvt.amount,
fvt.amount*fvr.multiplier)) grp_amt
FROM fv_sf1219_report_template fvr,
fv_sf1219_temp fvt, fv_sf1219_manual_lines fvm
WHERE fvm.line_id = fvr.line_id
AND fvt.temp_record_id = fvm.temp_record_id
AND (fvt.record_type IN ('M','N') OR
(fvt.record_type = 'R' AND fvt.group_name IS NULL))
AND fvt.alc_code = v_alc_code
AND fvm.line_id IN ('211','212','280','420')
GROUP BY fvt.alc_code, fvm.line_id,
to_char(to_date(fvt.accomplish_month,'MMYYYY'),'MM/YY'))
GROUP BY alc, l_num, acc_mon
ORDER BY 2, 3;
SELECT ts treasury_symbol, SUM(c2) col2_amt,
SUM(c3) col3_amt, alc alc_code
FROM (
SELECT fvt.treasury_symbol ts,
SUM(DECODE(fvt.record_type, 'A',
DECODE(SUBSTR(fvt.group_name,4,1),
2, fvt.amount, 0),
'R', DECODE(SUBSTR(fvt.group_name,4,1), 2, fvt.amount, 0))) c2,
SUM(DECODE(fvt.record_type, 'A',
DECODE(SUBSTR(fvt.group_name,4,1), 3, fvt.amount*-1, 0),
'R', DECODE(SUBSTR(fvt.group_name,4,1), 3, fvt.amount*-1, 0))) c3,
fvt.alc_code alc
FROM fv_sf1219_temp fvt
WHERE (fvt.record_type = 'A' OR
(fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
AND fvt.alc_code = v_alc_code
GROUP BY fvt.alc_code, fvt.treasury_symbol
UNION
SELECT fvt.treasury_symbol ts,
SUM(DECODE(fvt.record_type, 'M',
DECODE(fvm.column_no, 2, fvt.amount,0),
'N', DECODE(fvm.column_no, 2, fvt.amount*-1,0),
'R', DECODE(fvm.column_no, 2, fvt.amount,0))) c2,
SUM(DECODE(fvt.record_type, 'M', DECODE(fvm.column_no, 3,
fvt.amount*-1,0),
'N', DECODE(fvm.column_no, 3, fvt.amount,0),
'R', DECODE(fvm.column_no, 3, fvt.amount*-1,0))) c3,
fvt.alc_code alc
FROM fv_sf1219_temp fvt, fv_sf1219_manual_lines fvm
WHERE fvm.temp_record_id = fvt.temp_record_id
AND fvt.alc_code = v_alc_code
AND fvm.line_id = '410'
AND (fvt.record_type IN ('M','N') OR
(fvt.record_type = 'R' AND fvt.group_name IS NULL))
GROUP BY fvt.alc_code, fvt.treasury_symbol)
GROUP BY alc, ts
ORDER BY 1;
SELECT set_of_books_id
INTO p_set_bks_id
FROM fv_sf1219_temp
WHERE rownum = 1;
SELECT chart_of_accounts_id
INTO flex_num
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_bks_id;
SELECT end_date
INTO l_end_date
FROM gl_periods glp,
gl_sets_of_books gsob
WHERE glp.period_name = v_period
AND gsob.set_of_books_id = p_set_bks_id
AND gsob.chart_of_accounts_id = flex_num
AND glp.period_set_name = gsob.period_set_name;
SELECT MAX(supplement_number)
INTO max_supplement_number
FROM fv_sf1219_audits
WHERE alc_code = v_alc_code
AND reported_gl_period = l_reported_period;
v_stmt := 'SELECT ''000.00''||'''||max_supplement_number||'''||'' ''||
'''||v_alc_code||'''||'' ''||'''||
to_char(l_end_date,'MM/DD/YY')||'''||'' ''||'''||
to_char(SYSDATE,'MM/DD/YY')||'''||'' ''||'''||
RPAD(v_do_name,20)||'''||'' ''||'''||RPAD(v_do_tel_num, 14)||
RPAD(' ',10)||''''||
' FROM DUAL';
total_tab.delete;
v_stmt := 'SELECT ''00''||(SUBSTR('||x_line_id||',1,1)||''.''||
SUBSTR('||x_line_id||',2,2))||''0''||'' ''||
replace(replace(to_char('||x_amt||',
decode(sign('||x_amt||'), 1, ''0000000000000.00'', 0,
''0000000000000.00'', ''000000000000.00'')),
''.'',''''),'' '','''')|| RPAD('' '',57)
FROM DUAL';
SELECT replace(replace(to_char(line_210.group_amount,
decode(sign(line_210.group_amount), 1,
'0000000000000.00', 0,
'0000000000000.00',
'000000000000.00')),'.',''),' ','')
INTO l_amt
FROM DUAL;
v_stmt := 'SELECT ''210.''||'''||LPAD(v_rec_count,3,0)||
'''||'' ''||'''||
l_amt||'''||'' ''||'''||line_210.alc_code||''''||
' FROM DUAL';
SELECT replace(replace(to_char(line_211_420.group_amount,
decode(sign(line_211_420.group_amount), 1,
'0000000000000.00', 0,
'0000000000000.00',
'000000000000.00')),'.',''),' ','')
INTO l_amt
FROM DUAL;
v_stmt := 'SELECT '''||line_211_420.line_num||'''||'||'''.'''||'||
'''||LPAD(v_rec_count,3,0)||''''||
'||'' ''||'||''''||l_amt||'''||'||''' '''||'||'''||
line_211_420.alc_code||''''||
'||'' ''||'||''''||line_211_420.accomplish_month||''''||
' FROM DUAL';
SELECT replace(replace(to_char(line_1220.col2_amt,
decode(sign(line_1220.col2_amt), 1,
'0000000000000.00', 0,
'0000000000000.00',
'000000000000.00')),'.',''),' ','')
INTO l_amt
FROM DUAL;
SELECT rpad(nvl(substr(replace(line_1220.treasury_symbol,'-',
''),1,19),' '),19, ' ')
INTO l_ts
FROM DUAL;
v_stmt := 'SELECT ''410.''||'''||LPAD(v_line_count,3,0)||'''
||'||''' '''||
'||'''||l_amt||''''||
'||'' ''||''000000000000000''||'' ''||'||
''''||l_ts||'''||'||''' ''||'''||
LPAD(v_entry_number,3,0)||''''||
'||RPAD('' '',17)'||
' FROM DUAL';
SELECT replace(replace(to_char(line_1220.col3_amt,
decode(sign(line_1220.col3_amt), 1,
'0000000000000.00', 0,
'0000000000000.00',
'000000000000.00')),'.',''),' ','')
INTO l_amt
FROM DUAL;
SELECT rpad(nvl(substr(replace(line_1220.treasury_symbol,'-',''),
1,19),' '),19, ' ')
INTO l_ts
FROM DUAL;
v_stmt := 'SELECT ''410.''||'''||LPAD(v_line_count,3,0)||
'''||'||''' '''||
'||''000000000000000''||'' ''||'||
''''||l_amt||'''||'' '''||
'||'''||l_ts||'''||'||''' ''||'''||
LPAD(v_entry_number,3,0)||''''||
'||RPAD('' '',17)'||
' FROM DUAL';
SELECT replace(replace(to_char(line_1220.col2_amt,
decode(sign(line_1220.col2_amt), 1, '0000000000000.00',
0, '0000000000000.00', '000000000000.00')),
'.',''),' ','')
INTO l_amt
FROM DUAL;
SELECT replace(replace(to_char(line_1220.col3_amt,
decode(sign(line_1220.col3_amt), 1, '0000000000000.00', 0,
'0000000000000.00', '000000000000.00')),'.',''),' ','')
INTO l_amt2
FROM DUAL;
SELECT rpad(nvl(substr(replace(line_1220.treasury_symbol,
'-',''),1,19),' '),19, ' ')
INTO l_ts
FROM DUAL;
v_stmt := 'SELECT ''410.''||'''||LPAD(v_line_count,3,0)||
'''||'||''' '''||
'||'''||l_amt||'''||'' '''||
'||'''||l_amt2||'''||'' '''||
'||'''||l_ts||'''||'||''' ''||'''||
LPAD(v_entry_number,3,0)||''''||
'||RPAD('' '',17)'||
' FROM DUAL';
v_stmt := 'SELECT ''999.999''||'' ''||LPAD('||
v_total_line_count||',8,'' '')'
||'||RPAD('' '',64)'||
' FROM DUAL';
DELETE from fv_sf1219_temp t
WHERE t.alc_code = v_alc_code
AND EXISTS (SELECT 'X'
FROM FV_SF1219_MANUAL_LINES m
WHERE m.temp_record_id = t.temp_record_id
AND t.record_type = 'M');
DELETE FROM fv_sf1219_temp
WHERE alc_code = v_alc_code
AND record_type NOT IN ('P', 'M');
DELETE FROM fv_sf1219_manual_lines m
WHERE NOT EXISTS (SELECT 'X'
FROM fv_sf1219_temp t
WHERE t.temp_record_id = m.temp_record_id
AND t.record_type = 'M');
DELETE FROM fv_sf1219_org_temp;
SELECT ent.source_id_int_1 ,
aeh.event_id,aeh.ae_header_id,ael.ae_line_num
INTO p_reference,l_event_id,l_ae_header_id,l_ae_line_num
FROM xla_transaction_entities ent,
xla_events evt,
xla_ae_headers aeh,
xla_ae_lines ael,
gl_import_references gli
WHERE ent.application_id =p_application_id
AND ent.entity_code = p_entity_code
AND ent.entity_id = evt.entity_id
AND evt.event_id = aeh.event_id
AND aeh.ae_header_id = ael.ae_header_id
AND gli.gl_sl_link_id = ael.gl_sl_link_id
AND gli.je_batch_id = p_batch_id
AND gli.je_header_id = p_je_header_id
AND gli.je_line_num = p_je_line_num
AND ael.application_id = p_application_id;
SELECT applied_to_source_id_num_1
INTO p_appl_reference
FROM xla_distribution_links
WHERE ae_header_id = l_ae_header_id
AND ae_line_num = l_ae_line_num
AND application_id = p_application_id
AND applied_to_application_id = p_application_id ;
SELECT cash_receipt_history_id
INTO l_cash_receipt_hist_id
FROM ar_cash_receipt_history_all
WHERE cash_receipt_id = p_reference
AND event_id = l_event_id;