[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update_flag VARCHAR2(10);
PROCEDURE Insert_new_transaction(x_amount Number, x_sign_number number);
SELECT fst.rowid,
fst.gl_period,
fst.accomplish_date,
UPPER(fst.sf224_type_code),
fst.record_type,
fst.inter_agency_flag,
fst.obligation_date,
fst.d_r_flag,
fst.column_group,
fst.reported_month,
fst.exception_category,
fst.exception_section,
fst.reported_gl_period,
fst.supplemental_flag,
fst.alc_code,
fst.reference_2,
fst.reference_3,
fst.processed_flag,
fst.update_type,
fst.je_source,
fst.je_category,
fst.txn_category,
fst.sign_number,
fst.amount,
fst.actual_amount,
fst.reclass,
fst.reported_flag,
fst.je_from_sla_flag
FROM fv_sf224_temp fst
WHERE fst.set_of_books_id = c_set_of_books_id
AND fst.sf224_processed_flag = 'Y'
AND fst.alc_code = DECODE (c_alc_code, 'ALL', fst.alc_code, c_alc_code)
AND fst.end_period_date < c_end_date;
TYPE update_type_t IS TABLE OF fv_sf224_temp.update_type%TYPE;
l_update_type update_type_t;
CURSOR select_fv_sf224_map_cur(g_txn_category varchar2) is
SELECT DECODE(g_txn_category, 'C', trx_category_coll,
'P', trx_category_pay,
'I', trx_category_intra,
'I')
FROM fv_sf224_map
WHERE NVL(business_activity_code, 'NULL') = NVL(l_business_activity_code, 'NULL')
AND NVL(gwa_reporter_category_code, 'NULL') = NVL(l_gwa_reporter_category_code, 'NULL');
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'SELECT FROM gl_period_statuses');
SELECT start_date,
end_date,
year_start_date
INTO l_start_date,
l_end_date,
l_yr_start_date
FROM gl_period_statuses glp
WHERE glp.period_name = p_gl_period
AND glp.ledger_id = p_set_of_books_id
AND glp.application_id = 101;
l_location := l_module_name||'select_gl_period_statuses1';
l_update_type,
l_je_source,
l_je_category,
l_txn_category,
l_sign_number,
l_amount,
l_actual_amount,
l_reclass,
l_reported_flag,
l_je_from_sla_flag
LIMIT 10000;
SELECT start_date,
end_date
INTO l_start_date_2,
l_end_date_2
FROM gl_period_statuses glp
WHERE glp.period_name = l_gl_period(i)
AND glp.ledger_id = p_set_of_books_id
AND glp.application_id = 101;
l_location := l_module_name||'select_gl_period_statuses2';
SELECT MAX(supplemental_flag)
INTO l_tmp_supplemental_flag
FROM fv_sf224_audits
WHERE reported_gl_period = p_gl_period
AND set_of_books_id = p_set_of_books_id
AND alc_code = l_alc_code(i);
l_location := l_module_name||'select_fv_sf224_audits';
SELECT 'x'
INTO l_exists
FROM fv_interagency_funds_all
WHERE cash_receipt_id = l_cash_receipt_id;
l_update_type(i) := 'RECEIPT';
l_update_type(i) := NULL;
SELECT 'x'
INTO l_exists
FROM fv_interagency_funds_all
WHERE cash_receipt_id = l_cash_receipt_id;
l_update_type(i) := 'RECEIPT';
l_update_type(i) := NULL;
UPDATE fv_interagency_funds_all
SET processed_flag = 'Y',
period_reported = p_gl_period,
last_updated_by = g_user_id,
last_update_date = g_sysdate,
last_update_login = g_login_id
WHERE DECODE(l_update_type(i),'RECEIPT', cash_receipt_id, invoice_id) =
DECODE(l_update_type(i),'RECEIPT', to_number(l_cash_receipt_id),to_number(l_reference_2(i)));
l_location := l_module_name||'update_fv_interagency_funds_all';
UPDATE fv_refunds_voids_all
SET processed_flag = 'Y',
period_reported = p_gl_period,
last_updated_by = g_user_id,
last_update_date = g_sysdate,
last_update_login = g_login_id
WHERE DECODE(l_update_type(i),'RECEIPT', cash_receipt_id, invoice_id) =
DECODE(l_update_type(i),'RECEIPT', TO_NUMBER(l_cash_receipt_id),to_number(l_reference_2(i)))
AND type = l_type
AND NVL(check_id,0) = DECODE(l_update_type(i),'RECEIPT', NVL(check_id,0), to_number(l_reference_3(i)));
l_location := l_module_name||'update_fv_refunds_voids_all';
SELECT fa.business_activity_code,
fa.gwa_reporter_category_code
INTO l_business_activity_code,
l_gwa_reporter_category_code
FROM fv_alc_business_activity_v fa
WHERE fa.set_of_books_id = p_set_of_books_id
AND fa.agency_location_code = l_alc_code(i)
AND fa.period_name = l_gl_period(i);
l_location := l_module_name||'select_fv_alc_business_activity_v';
/*SELECT DECODE(l_txn_category(i), 'C', trx_category_coll,
'P', trx_category_pay,
'I', trx_category_intra,
'I')
INTO l_include_in_report
FROM fv_sf224_map
WHERE NVL(business_activity_code, 'NULL') = NVL(l_business_activity_code, 'NULL')
AND NVL(gwa_reporter_category_code, 'NULL') = NVL(l_gwa_reporter_category_code, 'NULL');*/
open select_fv_sf224_map_cur(l_txn_category(i));
fetch select_fv_sf224_map_cur into l_include_in_report;
close select_fv_sf224_map_cur;
l_location := l_module_name||'select_fv_sf224_map';
UPDATE fv_sf224_temp fst
SET fst.column_group = l_column_group(i),
fst.exception_category = l_exception_category(i),
fst.reported_month = l_reported_month(i),
fst.reported_flag = DECODE(l_reported_month(i), 'CURRENT', 'Y', 'CURRENT/PRIOR','Y','N'),
fst.exception_section = l_exception_section (i),
fst.reported_gl_period = l_reported_gl_period(i),
fst.supplemental_flag = l_supplemental_flag(i),
fst.txn_category = l_txn_category(i),
fst.sign_number = l_sign_number(i),
fst.amount = l_actual_amount(i) * l_sign_number(i),
fst.last_updated_by = g_user_id,
fst.last_update_date = g_sysdate,
fst.last_update_login = g_login_id,
fst.updated_request_id = g_request_id
WHERE ROWID = l_rowid(i);
l_location := l_module_name||'update_fv_sf224_temp';
PROCEDURE update_audit_info
(
p_set_of_books_id IN NUMBER,
p_alc IN VARCHAR2,
p_end_period_date IN DATE,
p_error_code OUT NOCOPY NUMBER,
p_error_desc OUT NOCOPY VARCHAR2
)
IS
l_module_name VARCHAR2(200) ;
l_module_name := g_module_name || 'Update_audit_info';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'INSERTING INTO THE AUDITS TABLE.');
INSERT INTO fv_sf224_audits
(
batch_id ,
sf224_month_reported,
reported_month,
column_group,
treasury_symbol_id,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
supplemental_flag,
exception_category,
gl_period,
reported_gl_period,
set_of_books_id,
alc_code,
inter_agency_flag,
je_header_id,
je_line_num,
record_type,
je_source,
je_category,
document_number,
je_from_sla_flag
)
SELECT je_batch_id,
TO_CHAR(accomplish_date, 'MM-YYYY'),
reported_month,
column_group,
treasury_symbol_id,
g_user_id,
g_sysdate,
g_sysdate,
g_user_id,
g_login_id,
supplemental_flag,
exception_category,
gl_period,
reported_gl_period,
set_of_books_id,
alc_code,
inter_agency_flag,
je_header_id,
je_line_num,
record_type,
je_source,
je_category,
document_number,
je_from_sla_flag
FROM fv_sf224_temp fst
WHERE ((fst.reported_month in ('CURRENT/PRIOR','CURRENT')
AND fst.reported_flag = 'Y'
AND fst.record_category = 'GLRECORD')
OR (exception_category IN ('INVALID_BA_GWA_SEC_COMBO', 'GWA_REPORTABLE')))
AND fst.set_of_books_id = p_set_of_books_id
AND fst.alc_code = DECODE (p_alc, 'ALL', alc_code, p_alc)
AND fst.end_period_date < TRUNC(p_end_period_date)+1;
l_location := l_module_name||'insert_fv_sf224_temp';
DELETE fv_sf224_temp fst
WHERE fst.reported_month in ('CURRENT/PRIOR','CURRENT')
AND fst.reported_flag = 'Y'
AND fst.record_category = 'GLRECORD'
AND fst.set_of_books_id = p_set_of_books_id
AND fst.alc_code = DECODE (p_alc, 'ALL', alc_code, p_alc)
AND fst.end_period_date < TRUNC(p_end_period_date)+1;
l_location := l_module_name||'delete_fv_sf224_temp';
END update_audit_info;
SELECT printer,
number_of_copies
FROM fnd_concurrent_requests
WHERE request_id = c_request_id ;
update_flag := p_run_mode;
IF (update_flag = 'F') THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling update_audit_info procedure.....');
update_audit_info
(
p_set_of_books_id => p_set_of_books_id,
p_alc => p_alc,
p_end_period_date => l_end_period_date,
p_error_code => p_retcode,
p_error_desc => p_errbuf
);
SELECT COUNT(*)
INTO l_cnt
FROM fv_report_definitions
WHERE set_of_books_id = p_set_of_books_id
AND agency_location_code IS NULL
AND d_r_flag IN ('D','R');
p_error_desc := 'The agency location code needs to be updated '||
'for the Disbursement and Receipt records '||
'in the 224 and Fund Balance with Treasury Form, '||
'before running the 224 Process';
SELECT last_run_date
INTO p_previous_run_date
FROM fv_sf224_run fsr
WHERE fsr.set_of_books_id = p_set_of_books_id;
SELECT min(start_date)
INTO g_lo_date
FROM gl_period_statuses
WHERE ledger_id = sob
AND application_id = 101
AND period_year = to_char(sysdate, 'YYYY');
l_location := l_module_name||'select_fv_sf224_run';
l_insert_required BOOLEAN;
UPDATE fv_sf224_run
SET last_run_date = g_SYSDATE
WHERE set_of_books_id = p_set_of_books_id;
l_insert_required := TRUE;
l_insert_required := FALSE;
l_location := l_module_name||'update_fv_sf224_run';
IF (p_error_code = g_SUCCESS AND l_insert_required) THEN
BEGIN
INSERT INTO fv_sf224_run
(
set_of_books_id,
last_run_date
)
VALUES
(
p_set_of_books_id,
g_SYSDATE
);
l_location := l_module_name||'insert_fv_sf224_run';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from gl_sets_of_books');
SELECT gsob.chart_of_accounts_id,
gsob.currency_code,
gsob.period_set_name
INTO p_chart_of_accounts_id,
p_currency_code,
l_period_set_name
FROM gl_sets_of_books gsob
WHERE set_of_books_id = p_set_of_books_id;
l_location := l_module_name||'select_gl_sets_of_books';
SELECT flex_value_set_id
INTO p_acct_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = p_acct_segment
AND application_id = l_application_id
AND id_flex_code = l_id_flex_code
AND id_flex_num = p_chart_of_accounts_id
AND enabled_flag = 'Y';
l_location := l_module_name||'select_fnd_id_flex_segments';
SELECT sf224_accomplish_date
INTO p_accomplish_attribute
FROM fv_system_parameters;
l_location := l_module_name||'select_fv_system_parameters';
PROCEDURE insert_sf224_batches
(
p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
p_previous_run_date IN fv_sf224_run.last_run_date%TYPE,
p_current_run_date IN fv_sf224_run.last_run_date%TYPE,
p_currency_code IN gl_sets_of_books.currency_code%TYPE,
p_acct_segment IN fnd_id_flex_segments.application_column_name%TYPE,
p_bal_segment IN fnd_id_flex_segments.application_column_name%TYPE,
p_error_code OUT NOCOPY NUMBER,
p_error_desc OUT NOCOPY VARCHAR2
)
IS
l_module_name VARCHAR2(200) ;
l_module_name := g_module_name || 'insert_sf224_batches';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'in insert_sf224_batches procedure, before the insert.');
l_string1 := 'INSERT INTO FV_SF224_TEMP
(
je_batch_id,
fund_code,
fund_description,
sf224_type_code,
name,
set_of_books_id,
posted_date,
amount,
actual_amount,
d_r_flag,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reference_8,
reference_9,
je_line_num,
je_header_id,
gl_period,
default_period_name,
external_reference,
treasury_symbol,
treasury_symbol_id,
record_category,
federal_rpt_id,
sf224_processed_flag,
account,
exception_section,
gl_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
created_request_id,
JE_SOURCE,
JE_CATEGORY,
JE_FROM_SLA_FLAG
)';
l_string3 := ' AND not exists (select batch_id
from fv_sf224_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)';
'SELECT glb.je_batch_id,
ffp.fund_value,
ffp.description,
fts.sf224_type_code,
glb.name,
decode(NVL(glh.je_from_sla_flag, ''N''),''U'', glb.SET_OF_BOOKS_ID_11I, ''N'', gll.ledger_id),
glb.posted_date,
--nvl(gll.Entered_dr,0) - nvl(gll.Entered_cr,0),
--nvl(gll.Entered_dr,0) - nvl(gll.Entered_cr,0),
nvl(gll.accounted_dr,0) - nvl(gll.accounted_cr,0),
nvl(gll.accounted_dr,0) - nvl(gll.accounted_cr,0),
frd.d_r_flag,
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_8)),
LTRIM(RTRIM(gll.reference_9)),
gll.je_line_num,
gll.je_header_id,
gll.period_name,
glb.default_period_name,
glh.external_reference,
fts.treasury_symbol,
ffp.treasury_symbol_id,
''GLRECORD'',
frd.federal_rpt_id,
''N'',
frd.account,
NULL,
gll.effective_date,
:b_user_id,
:b_sysdate,
:b_user_id,
:b_sysdate,
:b_login_id,
:b_request_id,
glh.je_source,
glh.je_category,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_report_definitions frd,
fv_fund_parameters ffp,
fv_treasury_symbols fts
WHERE 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 fts.treasury_symbol_id = ffp.treasury_symbol_id
AND gll.ledger_id = :b_sob
AND frd.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND gcc.'||p_bal_segment||' = ffp.fund_value
--AND glh.currency_code = :b_g_currency_code
AND glh.currency_code <> ''STAT''
AND glh.posted_date >= :posted_from_date
AND glh.posted_date <= :posted_to_date
AND gcc.'||p_acct_segment||' = frd.account
AND frd.d_r_flag in (''D'',''R'')
AND NVL(glh.je_from_sla_flag, ''N'') IN (''N'', ''U'')';
fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for upgraded 11i data ');
l_string2 := ' SELECT glb.je_batch_id,
ffp.fund_value,
ffp.description,
fts.sf224_type_code,
glb.name,
gll.ledger_id,
glb.posted_date,
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
frd.d_r_flag,
NULL, --LTRIM(RTRIM(gll.reference_1))
LTRIM(RTRIM(aid.invoice_id)), --Invoice_id LTRIM(RTRIM(gll.reference_2))
LTRIM(RTRIM(aip.check_id)), --Check_id LTRIM(RTRIM(gll.reference_3)),
NULL, --LTRIM(RTRIM(gll.reference_4)),
NULL, --LTRIM(RTRIM(gll.reference_5)),
NULL, --LTRIM(RTRIM(gll.reference_6)),
LTRIM(RTRIM(aid.distribution_line_number)), -- invoice_distributin_line_number LTRIM(RTRIM(gll.reference_8)),
LTRIM(RTRIM(aip.invoice_payment_id)), -- invoice_payment_id LTRIM(RTRIM(gll.reference_9)),
gll.je_line_num,
gll.je_header_id,
gll.period_name,
glb.default_period_name,
glh.external_reference,
fts.treasury_symbol,
ffp.treasury_symbol_id,
''GLRECORD'',
frd.federal_rpt_id,
''N'',
frd.account,
NULL,
gll.effective_date,
:b_user_id,
:b_sysdate,
:b_user_id,
:b_sysdate,
:b_login_id,
:b_request_id,
glh.je_source,
glh.je_category,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_report_definitions frd,
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 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 fts.treasury_symbol_id = ffp.treasury_symbol_id
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND gll.ledger_id = :b_sob
AND frd.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND gcc.'||p_bal_segment||' = ffp.fund_value
--AND glh.currency_code = :b_g_currency_code
AND glh.currency_code <> ''STAT''
AND glh.posted_date >= :posted_from_date
AND glh.posted_date <= :posted_to_date
AND gcc.'||p_acct_segment||' = frd.account
AND frd.d_r_flag in (''D'',''R'')
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 ';
l_string2 := ' SELECT glb.je_batch_id,
ffp.fund_value,
ffp.description,
fts.sf224_type_code,
glb.name,
gll.ledger_id,
glb.posted_date,
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
frd.d_r_flag,
NULL, --LTRIM(RTRIM(gll.reference_1))
LTRIM(RTRIM(aid.invoice_id)), --Invoice_id LTRIM(RTRIM(gll.reference_2))
LTRIM(RTRIM(aip.check_id)), --Check_id LTRIM(RTRIM(gll.reference_3)),
NULL, --LTRIM(RTRIM(gll.reference_4)),
NULL, --LTRIM(RTRIM(gll.reference_5)),
NULL, --LTRIM(RTRIM(gll.reference_6)),
LTRIM(RTRIM(aid.distribution_line_number)), -- invoice_distributin_line_number LTRIM(RTRIM(gll.reference_8)),
LTRIM(RTRIM(aip.invoice_payment_id)), -- invoice_payment_id LTRIM(RTRIM(gll.reference_9)),
gll.je_line_num,
gll.je_header_id,
gll.period_name,
glb.default_period_name,
glh.external_reference,
fts.treasury_symbol,
ffp.treasury_symbol_id,
''GLRECORD'',
frd.federal_rpt_id,
''N'',
frd.account,
NULL,
gll.effective_date,
:b_user_id,
:b_sysdate,
:b_user_id,
:b_sysdate,
:b_login_id,
:b_request_id,
glh.je_source,
glh.je_category,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_report_definitions frd,
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 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 fts.treasury_symbol_id = ffp.treasury_symbol_id
AND gll.ledger_id = :b_sob
AND frd.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND gcc.'||p_bal_segment||' = ffp.fund_value
--AND glh.currency_code = :b_g_currency_code
AND glh.currency_code <> ''STAT''
AND glh.posted_date >= :posted_from_date
AND glh.posted_date <= :posted_to_date
AND gcc.'||p_acct_segment||' = frd.account
AND frd.d_r_flag in (''D'',''R'')
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'')
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND xdl.application_id = 200 ';
SELECT glb.je_batch_id,
ffp.fund_value,
ffp.description,
fts.sf224_type_code,
glb.name,
gll.ledger_id,
glb.posted_date,
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
frd.d_r_flag,
NULL, --LTRIM(RTRIM(gll.reference_1))
LTRIM(RTRIM(aid.invoice_id)), --Invoice_id LTRIM(RTRIM(gll.reference_2))
LTRIM(RTRIM(aip.check_id)), --Check_id LTRIM(RTRIM(gll.reference_3)),
NULL, --LTRIM(RTRIM(gll.reference_4)),
NULL, --LTRIM(RTRIM(gll.reference_5)),
NULL, --LTRIM(RTRIM(gll.reference_6)),
LTRIM(RTRIM(aid.distribution_line_number)), -- invoice_distributin_line_number LTRIM(RTRIM(gll.reference_8)),
LTRIM(RTRIM(aip.invoice_payment_id)), -- invoice_payment_id LTRIM(RTRIM(gll.reference_9)),
gll.je_line_num,
gll.je_header_id,
gll.period_name,
glb.default_period_name,
glh.external_reference,
fts.treasury_symbol,
ffp.treasury_symbol_id,
''GLRECORD'',
frd.federal_rpt_id,
''N'',
frd.account,
NULL,
gll.effective_date,
:b_user_id,
:b_sysdate,
:b_user_id,
:b_sysdate,
:b_login_id,
:b_request_id,
glh.je_source,
glh.je_category,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_report_definitions frd,
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,
ap_prepay_app_dists apad,
xla_distribution_links xdl
WHERE 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 fts.treasury_symbol_id = ffp.treasury_symbol_id
AND gll.ledger_id = :b_sob
AND frd.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND gcc.'||p_bal_segment||' = ffp.fund_value
AND glh.currency_code <> ''STAT''
AND glh.posted_date >= :posted_from_date
AND glh.posted_date <= :posted_to_date
AND gcc.'||p_acct_segment||' = frd.account
AND frd.d_r_flag in (''D'',''R'')
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_PREPAY'')
--AND apad.prepay_app_distribution_id = aid.invoice_distribution_id
and apad.invoice_distribution_id = aid.invoice_distribution_id
--AND xdl.source_distribution_id_num_1 = apad.prepay_app_distribution_id
AND xdl.source_distribution_id_num_1 = apad.prepay_app_dist_id
AND xdl.application_id = 200 ';
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 glb.je_batch_id,
ffp.fund_value,
ffp.description,
fts.sf224_type_code,
DECODE (xle.event_type_code, ''TREASURY_VOID'', REPLACE (glb.name, ''Budget Execution'', ''VOID''), glb.name),
gll.ledger_id,
glb.posted_date,
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
frd.d_r_flag,
LTRIM(RTRIM(xdl.APPLIED_TO_SOURCE_ID_NUM_1)), -- treasury confirmation id LTRIM(RTRIM(gll.reference_1)),
NULL, --LTRIM(RTRIM(gll.reference_2)),
LTRIM(RTRIM(AIP.check_id)), --Check_id LTRIM(RTRIM(gll.reference_3)),
LTRIM(RTRIM(aid.invoice_id)), --invoice_id LTRIM(RTRIM(gll.reference_4)),
NULL, --LTRIM(RTRIM(gll.reference_5)),
LTRIM(RTRIM(ftc.treasury_doc_date)), --Accomplish date LTRIM(RTRIM(gll.reference_6)),
NULL, --LTRIM(RTRIM(gll.reference_8)),
NULL,--LTRIM(RTRIM(gll.reference_9)),
gll.je_line_num,
gll.je_header_id,
gll.period_name,
glb.default_period_name,
glh.external_reference,
fts.treasury_symbol,
ffp.treasury_symbol_id,
''GLRECORD'',
frd.federal_rpt_id,
''N'',
frd.account,
NULL,
gll.effective_date,
:b_user_id,
:b_sysdate,
:b_user_id,
:b_sysdate,
:b_login_id,
:b_request_id,
glh.je_source,
glh.je_category,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_report_definitions frd,
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,
fv_treasury_confirmations_all ftc,
xla_events xle
WHERE 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 fts.treasury_symbol_id = ffp.treasury_symbol_id
AND gll.ledger_id = :b_sob
AND frd.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND gcc.'||p_bal_segment||' = ffp.fund_value
--AND glh.currency_code = :b_g_currency_code
AND glh.currency_code <> ''STAT''
AND glh.posted_date >= :posted_from_date
AND glh.posted_date <= :posted_to_date
AND gcc.'||p_acct_segment||' = frd.account
AND frd.d_r_flag in (''D'',''R'')
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 xle.event_id = xah.event_id
AND ftc.treasury_confirmation_id = xdl.APPLIED_TO_SOURCE_ID_NUM_1
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 ' ;
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 glb.je_batch_id,
ffp.fund_value,
ffp.description,
fts.sf224_type_code,
glb.name,
gll.ledger_id,
glb.posted_date,
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
frd.d_r_flag,
LTRIM(RTRIM(xte.SOURCE_ID_INT_1)), -- expenditure_item_id LTRIM(RTRIM(gll.reference_1)),
NULL, --LTRIM(RTRIM(gll.reference_2)),
NULL, --LTRIM(RTRIM(gll.reference_3)),
NULL, --LTRIM(RTRIM(gll.reference_4)),
NULL, --LTRIM(RTRIM(gll.reference_5)),
NULL, --LTRIM(RTRIM(gll.reference_6)),
NULL, --LTRIM(RTRIM(gll.reference_8)),
NULL, --LTRIM(RTRIM(gll.reference_9)),
gll.je_line_num,
gll.je_header_id,
gll.period_name,
glb.default_period_name,
glh.external_reference,
fts.treasury_symbol,
ffp.treasury_symbol_id,
''GLRECORD'',
frd.federal_rpt_id,
''N'',
frd.account,
NULL,
gll.effective_date,
:b_user_id,
:b_sysdate,
:b_user_id,
:b_sysdate,
:b_login_id,
:b_request_id,
glh.je_source,
glh.je_category,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_report_definitions frd,
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 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 fts.treasury_symbol_id = ffp.treasury_symbol_id
AND gll.ledger_id = :b_sob
AND frd.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND gcc.'||p_bal_segment||' = ffp.fund_value
--AND glh.currency_code = :b_g_currency_code
AND glh.currency_code <> ''STAT''
AND glh.posted_date >= :posted_from_date
AND glh.posted_date <= :posted_to_date
AND gcc.'||p_acct_segment||' = frd.account
AND frd.d_r_flag in (''D'',''R'')
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 xte.event_id = xet.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 ';
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 glb.je_batch_id,
ffp.fund_value,
ffp.description,
fts.sf224_type_code,
glb.name,
gll.ledger_id,
glb.posted_date,
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
frd.d_r_flag,
NULL, --LTRIM(RTRIM(gll.reference_1)),
LTRIM(RTRIM(arch.cash_receipt_id)), --LTRIM(RTRIM(gll.reference_2)),
NULL, --LTRIM(RTRIM(gll.reference_3)),
NULL, --LTRIM(RTRIM(gll.reference_4)),
LTRIM(RTRIM(arch.CASH_RECEIPT_HISTORY_ID)), ---cash_receipt_hist_id --LTRIM(RTRIM(gll.reference_5)),
NULL, --LTRIM(RTRIM(gll.reference_6)),
NULL, --LTRIM(RTRIM(gll.reference_8)),
NULL, --LTRIM(RTRIM(gll.reference_9)),
gll.je_line_num,
gll.je_header_id,
gll.period_name,
glb.default_period_name,
glh.external_reference,
fts.treasury_symbol,
ffp.treasury_symbol_id,
''GLRECORD'',
frd.federal_rpt_id,
''N'',
frd.account,
NULL,
gll.effective_date,
:b_user_id,
:b_sysdate,
:b_user_id,
:b_sysdate,
:b_login_id,
:b_request_id,
glh.je_source,
glh.je_category,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_report_definitions frd,
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 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 fts.treasury_symbol_id = ffp.treasury_symbol_id
AND gll.ledger_id = :b_sob
AND frd.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND gcc.'||p_bal_segment||' = ffp.fund_value
--AND glh.currency_code = :b_g_currency_code
AND glh.currency_code <> ''STAT''
AND glh.posted_date >= :posted_from_date
AND glh.posted_date <= :posted_to_date
AND gcc.'||p_acct_segment||' = frd.account
AND frd.d_r_flag in (''D'',''R'')
AND glh.je_source=''Receivables''
--AND glh.je_category = ''Misc Receipts''
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.event_id = xet.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 ';
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 glb.je_batch_id,
ffp.fund_value,
ffp.description,
fts.sf224_type_code,
glb.name,
gll.ledger_id,
glb.posted_date,
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
frd.d_r_flag,
NULL, --LTRIM(RTRIM(gll.reference_1)),
LTRIM(RTRIM(arr.cash_receipt_id)), --LTRIM(RTRIM(gll.reference_2)),
NULL, --LTRIM(RTRIM(gll.reference_3)),
NULL, --LTRIM(RTRIM(gll.reference_4)),
LTRIM(RTRIM(arr.receivable_application_id)), ---cash_receipt_hist_id --LTRIM(RTRIM(gll.reference_5)),
NULL, --LTRIM(RTRIM(gll.reference_6)),
NULL, --LTRIM(RTRIM(gll.reference_8)),
NULL, --LTRIM(RTRIM(gll.reference_9)),
gll.je_line_num,
gll.je_header_id,
gll.period_name,
glb.default_period_name,
glh.external_reference,
fts.treasury_symbol,
ffp.treasury_symbol_id,
''GLRECORD'',
frd.federal_rpt_id,
''N'',
frd.account,
NULL,
gll.effective_date,
:b_user_id,
:b_sysdate,
:b_user_id,
:b_sysdate,
:b_login_id,
:b_request_id,
glh.je_source,
glh.je_category,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_report_definitions frd,
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 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 fts.treasury_symbol_id = ffp.treasury_symbol_id
AND gll.ledger_id = :b_sob
AND frd.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND gcc.'||p_bal_segment||' = ffp.fund_value
--AND glh.currency_code = :b_g_currency_code
AND glh.currency_code <> ''STAT''
AND glh.posted_date >= :posted_from_date
AND glh.posted_date <= :posted_to_date
AND gcc.'||p_acct_segment||' = frd.account
AND frd.d_r_flag in (''D'',''R'')
AND glh.je_source=''Receivables''
--AND glh.je_category = ''Misc Receipts''
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.event_id = xet.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 IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_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 ';
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 glb.je_batch_id,
ffp.fund_value,
ffp.description,
fts.sf224_type_code,
glb.name,
gll.ledger_id,
glb.posted_date,
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
nvl(ROUND(xdl.unrounded_accounted_dr,2),0) - nvl(ROUND(xdl.unrounded_accounted_cr,2),0),
frd.d_r_flag,
NULL, --LTRIM(RTRIM(gll.reference_1)),
LTRIM(RTRIM(arm.cash_receipt_id)),
NULL, --LTRIM(RTRIM(gll.reference_3)),
NULL, --LTRIM(RTRIM(gll.reference_4)),
LTRIM(RTRIM(arm.MISC_CASH_DISTRIBUTION_ID)), --cash_receipt_hist_id --LTRIM(RTRIM(gll.reference_5)),
NULL, --LTRIM(RTRIM(gll.reference_6)),
NULL, --LTRIM(RTRIM(gll.reference_8)),
NULL, --LTRIM(RTRIM(gll.reference_9)),
gll.je_line_num,
gll.je_header_id,
gll.period_name,
glb.default_period_name,
glh.external_reference,
fts.treasury_symbol,
ffp.treasury_symbol_id,
''GLRECORD'',
frd.federal_rpt_id,
''N'',
frd.account,
NULL,
gll.effective_date,
:b_user_id,
:b_sysdate,
:b_user_id,
:b_sysdate,
:b_login_id,
:b_request_id,
glh.je_source,
glh.je_category,
glh.je_from_sla_flag
FROM gl_je_batches glb,
gl_je_headers glh,
gl_je_lines gll,
gl_code_combinations gcc,
fv_report_definitions frd,
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 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 fts.treasury_symbol_id = ffp.treasury_symbol_id
AND gll.ledger_id = :b_sob
AND frd.set_of_books_id = :b_sob
AND ffp.set_of_books_id = :b_sob
AND gcc.'||p_bal_segment||' = ffp.fund_value
--AND glh.currency_code = :b_g_currency_code
AND glh.currency_code <> ''STAT''
AND glh.posted_date >= :posted_from_date
AND glh.posted_date <= :posted_to_date
AND gcc.'||p_acct_segment||' = frd.account
AND frd.d_r_flag in (''D'',''R'')
AND glh.je_source=''Receivables''
--AND glh.je_category = ''Misc Receipts''
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.event_id = xet.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 IN (''AR_DISTRIBUTIONS_ALL'', ''MFAR_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 ';
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 ');
END insert_sf224_batches ;
l_select VARCHAR2(2000);
vl_checkrun_name Ap_Inv_Selection_Criteria_All.checkrun_name%TYPE;
l_update_type VARCHAR2(25);
SELECT rowid,
je_batch_id,
fund_code,
sf224_type_code,
name,
amount,
actual_amount,
d_r_flag,
accomplish_date,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reference_8,
reference_9,
je_line_num,
je_header_id,
reported_flag,
exception_exists,
record_category,
gl_period,
exception_category,
exception_section,
reported_month,
column_group,
record_type,
inter_agency_flag,
obligation_date,
treasury_symbol,
treasury_symbol_id,
federal_rpt_id,
txn_category,
je_source,
je_category,
je_from_sla_flag
FROM fv_sf224_temp
WHERE set_of_books_id = c_set_of_books_id
AND sf224_processed_flag = 'N';
SELECT obligation_date,
refund_amount
FROM fv_refunds_voids_all
WHERE cash_receipt_id = c_cash_receipt_id
AND type = 'AP_REFUND'
AND fund_value = c_fund_code;
SELECT COUNT(*)
FROM fv_sf224_temp
WHERE je_batch_id = c_batch_id
AND je_header_id = c_je_header_id
AND DECODE(c_je_from_sla_flag, 'Y', reference_2, DECODE(c_je_category,'Misc Receipts',reference_2, SUBSTR(reference_2,0,INSTR(reference_2,'C')-1)))
= TO_CHAR(c_cash_receipt_id)
AND fund_code = c_fund_code
AND name = 'Refunds_and_Voids'
AND record_category = 'CREATED'
AND record_type = 'Receipt_refund';
SELECT payables_ia_paygroup
INTO p_def_p_ia_paygroup
FROM FV_Operating_units_all
WHERE org_id = p_def_org_id;
SELECT AR_RECLASS_ATTRIBUTE
INTO l_ar_reclass_attribute
FROM fv_system_parameters;
SELECT agency_location_code
INTO p_def_alc_code
FROM Fv_Report_Definitions
WHERE federal_rpt_id = l_federal_rpt_id;
SELECT start_date,
end_date
INTO l_txn_start_date,
l_txn_end_date
FROM gl_period_statuses glp
WHERE glp.period_name = l_gl_period
AND glp.ledger_id = sob
AND glp.application_id = 101;
l_update_type := NULL;
SELECT checkrun_name
INTO l_document_number
FROM ap_checks_all
WHERE check_id = TO_NUMBER(l_reference_3);
SELECT SUBSTR(name,1,50)
INTO l_document_number
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) ;
l_select := 'select pei.'||l_accomplish_attribute||
',pei.org_id from pa_cost_distribution_lines_all pcdl,
pa_expenditure_items_all pei
where pcdl.batch_name =:b_reference_1
and pcdl.expenditure_item_id = pei.expenditure_item_id';
SELECT SUBSTR(name,1,50)
INTO l_document_number
FROM gl_je_headers
WHERE je_header_id = l_je_header_id;
dbms_sql.parse(l_cursor_id, l_select, dbms_sql.v7);
Insert_new_transaction(l_amount, 1);
l_select := 'select pei.'||l_accomplish_attribute||
',pei.org_id from pa_cost_distribution_lines_all pcdl,
pa_expenditure_items_all pei
where pcdl.expenditure_item_id =:b_reference_1
and pcdl.expenditure_item_id = pei.expenditure_item_id';
SELECT SUBSTR(name,1,50)
INTO l_document_number
FROM gl_je_headers
WHERE je_header_id = l_je_header_id;
dbms_sql.parse(l_cursor_id, l_select, dbms_sql.v7);
Insert_new_transaction(l_amount, 1);
SELECT invoice_num
INTO l_document_number
FROM ap_invoices_all
WHERE invoice_id = TO_NUMBER(l_reference_2);
SELECT SUBSTR(name,1,50)
INTO l_document_number
FROM gl_je_headers
WHERE je_header_id = l_je_header_id;
SELECT 'Y', invoice_type_lookup_code
INTO l_exists, g_invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = to_number(l_REFERENCE_2) ;
select 'Y', payment_type_flag, void_date
into l_exists, g_payment_type_flag, g_check_void_date
from ap_checks_all
Where check_id = to_number(l_REFERENCE_3) ;
select 'Y'
into l_exists
from ap_invoice_payments_all
Where invoice_payment_id = to_number(l_REFERENCE_9) ;
SELECT 'Y'
INTO l_exists
FROM Ar_Cash_Receipts_All
WHERE cash_receipt_id = to_number(l_cash_receipt_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 cash_receipt_id = TO_NUMBER(l_cash_receipt_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);
SELECT 'Y'
INTO l_exists
FROM Ar_Cash_Receipt_History_All
WHERE cash_receipt_history_id = TO_NUMBER(l_cash_receipt_hist_id)
AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
SELECT 'Y'
INTO l_exists
FROM Ar_Misc_Cash_Distributions_All
WHERE misc_cash_distribution_id = TO_NUMBER(l_cash_receipt_hist_id)
AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
SELECT receipt_number
INTO l_document_number
FROM ar_cash_receipts_all
WHERE cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
SELECT SUBSTR(name,1,50)
INTO l_document_number
FROM gl_je_headers
WHERE je_header_id = l_je_header_id;
SELECT checkrun_name
INTO l_document_number
FROM ap_checks_all
WHERE check_id = to_number(l_reference_3);
SELECT SUBSTR(name,1,50)
INTO l_document_number
FROM gl_je_headers
WHERE je_header_id = l_je_header_id;
select 'Y'
into l_exists
from AP_CHECKS_ALL
Where check_id = to_number(l_reference_3);
select 'Y'
into l_exists
from AP_INVOICES_ALL
Where invoice_id = to_number(l_reference_4);
SELECT SUBSTR(name,1,50)
INTO l_document_number
FROM gl_je_headers
WHERE je_header_id = l_je_header_id;
SELECT 'Y'
INTO l_exists
FROM Ar_Misc_Cash_Distributions_All
WHERE misc_cash_distribution_id = l_cash_receipt_hist_id
AND cash_receipt_id = l_cash_receipt_id
AND created_from = 'ARP_REVERSE_RECEIPT.REVERSE';
SELECT 'Y'
INTO l_exists
FROM Ar_Cash_Receipt_History_All
WHERE cash_receipt_history_id = l_cash_receipt_hist_id
AND cash_receipt_id = l_cash_receipt_id
AND status='REVERSED';
SELECT DECODE(l_exists,'Y',reversal_date,receipt_date)
INTO l_accomplish_date
FROM ar_cash_receipts_all
WHERE cash_receipt_id = l_cash_receipt_id;
SELECT remit_bank_acct_use_id
INTO vg_bank_acct_id
FROM Ar_Cash_Receipts_All
WHERE cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
SELECT 'x'
INTO l_dummy
FROM fv_interagency_funds_all
WHERE cash_receipt_id = l_cash_receipt_id;
l_update_type := 'RECEIPT';
error_buf := 'Too many rows in interagency select';
sql_stmt1:= 'SELECT ACR.' || l_ar_reclass_attribute ||
' FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.cash_receipt_id = '
|| l_cash_receipt_id;
SELECT 'x'
INTO l_dummy
FROM fv_interagency_funds_all a
WHERE cash_receipt_id = (SELECT cash_receipt_id
FROM ar_cash_receipts b
WHERE b.receipt_number = l_reclass_receipt_number);
l_update_type := 'RECEIPT';
error_buf := 'Too many rows in interagency select';
l_update_type := 'RECEIPT';
insert_new_transaction (l_inv_amount, l_sign_number);
SELECT ce_bank_acct_use_id
INTO vg_bank_acct_id
FROM Ap_Checks_All
WHERE check_id = TO_NUMBER(l_reference_3);
SELECT checkrun_name,treasury_doc_date,org_id
INTO vl_checkrun_name, l_accomplish_date, l_org_id
FROM Fv_Treasury_Confirmations_All
WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
SELECT checkrun_name,org_id
INTO vl_checkrun_name,l_org_id
FROM Fv_Treasury_Confirmations_All
WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
SELECT ce_bank_acct_use_id
INTO vg_bank_acct_id
FROM Ap_Inv_Selection_Criteria_All
WHERE checkrun_name = vl_checkrun_name
AND org_id = l_org_id;
'bank_account_id from Ap_Inv_Selection_Criteria_All table.';
SELECT treasury_doc_date
INTO l_accomplish_date
FROM Fv_Treasury_Confirmations_All
WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
SELECT ce_bank_acct_use_id
INTO vg_bank_acct_id
FROM Ap_Checks_All
WHERE check_id = TO_NUMBER(l_reference_3);
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'
OR apc.payment_method_code = 'CLEAR') ;
error_buf := 'Too many rows in invoice info,dit select';
SELECT ce_bank_acct_use_id
INTO vg_bank_acct_id
FROM Ap_Checks_All
WHERE check_id = TO_NUMBER(l_reference_3);
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 ;
error_buf := 'Too many rows in chargeback 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);
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;
l_update_type := 'VOID_PAYABLE';
error_buf := 'Too many rows in obligation_date select';
error_buf := 'Too many rows in void_date disbursement select';
insert_new_transaction(l_org_amount, 1);
insert_new_transaction(l_amount, 1);
insert_new_transaction(l_amount, 1);
SELECT void_date
INTO l_accomplish_date
FROM ap_checks_all
WHERE check_id = To_number (nvl(l_reference_3,'0')) ;
SELECT invoice_date
INTO l_invoice_date
FROM ap_invoices_all
WHERE Invoice_id = to_number (nvl(l_reference_4,'0')) ;
Insert_New_Transaction(l_org_amount, 1);
SELECT ce_bank_acct_use_id
INTO vg_bank_acct_id
FROM Ap_Checks_All
WHERE check_id = TO_NUMBER(l_reference_3);
l_update_type := 'VOID_PAYABLE';
error_buf := 'Too many rows in obligation_date select';
SELECT max(obligation_date)
INTO l_obligation_date
FROM fv_refunds_voids_all
WHERE set_of_books_id = sob
AND org_id = g_org_id
AND TYPE = 'PAYABLE_REFUND'
AND invoice_id = l_reference_2;
l_update_type := 'PAYABLE_REFUND';
Insert_New_Transaction(l_org_amount,1);
Insert_New_Transaction(l_org_amount,1);
Payables: The Payment Form has been updated to include a Void Payment field.
If the Void Payment field is populated for a payment, then that payment and the cancelled payment it references
will be considered as reclassified transactions and will be included in Section I of the partial 224.
Receivables: A new DFF has been introduced in AR Receipt form. This DFF allows to replace the receipt for any existing
receipts. Both receipts will be treated as reclassified and inludeded in Partial 224. */
IF l_je_source = 'Payables' AND l_je_category = 'Payments' AND g_payment_type_flag = 'M'
AND l_reference_2 IS NOT NULL AND l_reference_3 IS NOT NULL AND l_reference_9 IS NOT NULL THEN
BEGIN
SELECT apc.void_check_number /* replace attribute1 with new field name */
INTO l_void_check_number
FROM ap_checks_all apc
WHERE apc.check_id = to_number(l_reference_3);
SELECT 'Y'
INTO l_voided_reclassified_payment
FROM AP_INVOICE_PAYMENTS_ALL APP
WHERE APP.CHECK_ID = TO_NUMBER(L_REFERENCE_3)
AND APP.invoice_payment_id = TO_NUMBER(L_REFERENCE_9)
AND APP.reversal_inv_pmt_id IS NOT NULL
AND APP.reversal_flag ='Y';
sql_stmt1:= 'SELECT ACR.' || l_ar_reclass_attribute ||
' FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.cash_receipt_id = '||l_cash_receipt_id;
sql_stmt:='SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL ac, Ar_Cash_Receipt_History_All ah
where ac.cash_receipt_id = ah.cash_receipt_id
and ah.cash_receipt_history_id = '|| l_cash_receipt_hist_id ||
' and ah.STATUS= ''REVERSED'' '||
' AND ah.CURRENT_RECORD_FLAG = ''Y'' '||
' and exists (SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL AC2 '||
' WHERE AC2.' || l_ar_reclass_attribute ||
' = ' || '''' || l_document_number || '''' || ' )' ;
sql_stmt:='SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL ac, Ar_Misc_Cash_Distributions_All ah
where ac.cash_receipt_id = ah.cash_receipt_id
and ah.misc_cash_distribution_id = '|| l_cash_receipt_hist_id ||
' and ah.cash_receipt_id = '||l_cash_receipt_id||
' and ah.created_from= ''ARP_REVERSE_RECEIPT.REVERSE'' '||
' and exists (SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL AC2 '||
' WHERE AC2.' || l_ar_reclass_attribute ||
' = ' || '''' || l_document_number || '''' || ' )' ;
SELECT fv.fv_alc_addresses_id ,fv.business_activity_code, fv.gwa_reporter_category_code
INTO l_alc_addresses_id, l_business_activity_code, l_gwa_reporter_category_code
FROM fv_alc_business_activity_v fv
WHERE fv.agency_location_code = l_alc_code
AND fv.PERIOD_NAME = l_gl_period
AND fv.SET_OF_BOOKS_ID = sob;
' for the selected period: '|| l_gl_period);
DELETE fv_sf224_temp fv
WHERE fv.je_line_num = L_JE_LINE_NUM
AND fv.je_header_id = L_JE_HEADER_ID
AND fv.exception_category IN ('DEFAULT_ALC','REPORTED_AS_MANUAL','NO_PA_ACCOMPLISH_DATE');
Insert_New_Transaction(l_org_amount, 1);
Update fv_sf224_temp
set reported_month = l_reported_month,
accomplish_date = l_accomplish_date,
exception_category = l_exception_category,
exception_section = l_exception_section,
column_group = l_column_group,
inter_agency_flag = l_ia_flag,
obligation_date = l_obligation_date,
record_type = l_record_type,
reference_2 = l_reference_2,
reference_3 = l_reference_3,
amount = l_org_amount * l_sign_number,
actual_amount = l_org_amount,
sign_number = l_sign_number,
alc_code = l_alc_code,
reported_flag = DECODE ( l_reportable, 'I', DECODE(l_reported_month, 'CURRENT', 'Y',
'CURRENT/PRIOR','Y','N'),'N'),
EXCEPTION_EXISTS = L_EXCEPTION_EXISTS,
SF224_PROCESSED_FLAG= 'Y',
je_source = l_je_source,
je_category = l_je_category,
document_number = l_document_number,
txn_category = l_txn_category,
reclass = NVL(l_reclass, 'N'),
start_period_date = l_txn_start_date,
end_period_date = l_txn_end_date,
update_type = l_update_type,
last_updated_by = g_user_id,
last_update_date = g_sysdate,
last_update_login = g_login_id,
updated_request_id = g_request_id
where rowid = l_rowid;
update fv_sf224_temp
set reported_flag = 'N',
sf224_Processed_flag = 'Y',
exception_exists = l_exception_exists,
last_updated_by = g_user_id,
last_update_date = g_sysdate,
last_update_login = g_login_id,
updated_request_id = g_request_id
where rowid = l_rowid ;
error_buf := 'fv_sf224_temp table Update failed ';
SELECT mp.trx_category_intra
INTO l_reportable
FROM fv_sf224_map mp
WHERE mp.business_activity_code = p_business_activity_code
AND mp.GWA_REPORTER_CATEGORY_CODE = p_gwa_reporter_category_code;
SELECT mp.trx_category_coll
INTO l_reportable
FROM fv_sf224_map mp
WHERE mp.business_activity_code = p_business_activity_code
AND mp.GWA_REPORTER_CATEGORY_CODE = p_gwa_reporter_category_code;
SELECT mp.trx_category_pay
INTO l_reportable
FROM fv_sf224_map mp
WHERE mp.business_activity_code = p_business_activity_code
AND mp.GWA_REPORTER_CATEGORY_CODE = p_gwa_reporter_category_code;
Insert_New_Transaction(l_amount, 1);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TXN INSERTED');
SELECT ceba.agency_location_code
FROM ce_Bank_Accounts ceba,
ce_bank_acct_uses_all cebu
WHERE cebu.org_id = g_org_id
--AND cebu.org_id = ceba.account_owner_org_id
AND cebu.bank_acct_use_id = c_bank_acct_id
AND ceba.bank_account_id = cebu.bank_account_id;
PROCEDURE insert_new_transaction(x_amount number, x_sign_number number) is
l_module_name VARCHAR2(200) ;
l_module_name := g_module_name || 'Insert_new_transaction';
INSERT INTO fv_sf224_temp
(
je_batch_id,
fund_code,
name,
amount,
actual_amount,
sign_number,
reported_month,
column_group,
record_type,
inter_agency_flag,
obligation_date,
exception_category,
accomplish_date,
treasury_symbol,
treasury_symbol_id,
je_line_num,
reported_flag,
exception_exists,
record_category,
reference_1,
reference_2,
reference_3,
reference_9,
je_header_id,
alc_code,
gl_period,
set_of_books_id,
je_source,
je_category,
document_number,
txn_category,
start_period_date,
end_period_date,
exception_section,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
created_request_id
)
VALUES
(
l_batch_id,
l_billing_agency_fund,
x_name,
x_amount*x_sign_number,
x_amount,
x_sign_number,
l_reported_month,
l_column_group,
l_record_type,
l_ia_flag,
l_obligation_date,
l_exception_category,
l_accomplish_date,
l_treasury_symbol,
l_treasury_symbol_id,
l_je_line_num,
DECODE(l_record_category, 'EXCEPTION', NULL, l_reported_flag),
DECODE(l_record_category, 'EXCEPTION', NULL, l_exception_exists),
l_record_category,
l_reference_1,
l_reference_2,
l_reference_3,
l_reference_9,
l_je_header_id,
l_alc_code,
l_gl_period,
sob,
l_je_source,
l_je_category,
l_document_number,
l_txn_category,
l_txn_start_date,
l_txn_end_date,
l_exception_section,
g_user_id,
g_sysdate,
g_user_id,
g_sysdate,
g_login_id,
g_request_id
);
END Insert_new_transaction;
/* Delete all exceptions */
DELETE fv_sf224_temp fst
WHERE fst.set_of_books_id = p_set_of_books_id
AND fst.exception_exists IS NULL
AND fst.record_category = 'EXCEPTION';
l_location := l_module_name||'delete_fv_sf224_temp';
UPDATE fv_sf224_temp fst
SET fst.sf224_processed_flag = 'N',
last_updated_by = g_user_id,
last_update_date = g_sysdate,
last_update_login = g_login_id,
updated_request_id = g_request_id
WHERE fst.set_of_books_id = p_set_of_books_id
AND fst.exception_exists = 'Y';
l_location := l_module_name||'update_fv_sf224_temp';
SELECT DISTINCT tmp.alc_code
FROM fv_sf224_temp tmp
WHERE tmp.set_of_books_id = sob
AND tmp.alc_code IS NOT NULL;
SELECT 1
INTO l_count
FROM fv_alc_addresses_all
WHERE AGENCY_LOCATION_CODE = alc_code_rec.alc_code
AND set_of_books_id = sob;
SELECT 1
INTO l_count
FROM fv_alc_addresses_all
WHERE AGENCY_LOCATION_CODE = p_alc
AND set_of_books_id = sob;
insert_sf224_batches
(
p_set_of_books_id => p_set_of_books_id,
p_previous_run_date => l_previous_run_date,
p_current_run_date => g_SYSDATE,
p_currency_code => l_currency_code,
p_acct_segment => l_acct_segment,
p_bal_segment => l_bal_segment,
p_error_code => p_retcode,
p_error_desc => p_errbuf
);
SELECT NVL(max(po_distribution_id),0),
NVL(max(parent_invoice_id),0)
INTO l_po_dist_id, l_parent_invoice_id
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id ;
SELECT NVL(gl_encumbered_date,creation_date)
INTO l_obligation_date
FROM po_distributions_all
WHERE po_distribution_id =l_po_dist_id;
SELECT INVOICE_DATE
INTO l_obligation_date
FROM ap_invoices_all
WHERE invoice_id = l_parent_invoice_id;
SELECT INVOICE_DATE
INTO l_obligation_date
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
SELECT start_date
INTO l_gl_start_date
FROM gl_period_statuses
WHERE ledger_id = l_set_of_books_id
AND application_id = 101
AND period_name = p_gl_period_low;
SELECT end_date
INTO l_gl_end_date
FROM gl_period_statuses
WHERE ledger_id = l_set_of_books_id
AND application_id = 101
AND period_name = p_gl_period_high;
INSERT INTO FV_REFUNDS_VOIDS_ALL
( refunds_voids_id,
TYPE ,
invoice_id,
processed_flag,
set_of_books_id,
org_id,
conc_request_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
vendor_id,
vendor_site_id,
invoice_distribution_id,
distribution_line_number,
dist_code_combination_id ,
dist_amount,
check_id,
invoice_payment_id,
refund_amount,
refund_gl_date ,
invoice_gl_date,
invoice_num,
vendor_name ,
vendor_site_code,
check_number,
refund_gl_period,
invoice_amount,
vendor_number,
obligation_date,
obligation_number
)
(
SELECT fv_refunds_voids_s.NEXTVAL,
'PAYABLE_REFUND',
api.invoice_id,
'N',
l_set_of_books_id,
l_org_id,
l_conc_request_id,
SYSDATE,
l_user_id,
l_user_id,
SYSDATE,
l_user_id,
api.vendor_id,
api.vendor_site_id,
apid.invoice_distribution_id,
apid.distribution_line_number ,
apid.dist_code_combination_id,
apid.amount,
apc.check_id,
apip.invoice_payment_id,
apip.amount refund_amount,
apc.check_date ,
api.gl_date,
api.invoice_num,
apc.vendor_name ,
apc.vendor_site_code,
apc.check_number,
apip.period_name,
api.invoice_amount,
(SELECT segment1 FROM po_vendors WHERE vendor_id = apc.vendor_id),
DECODE(apid.po_distribution_id,
NULL,
DECODE(apid.parent_invoice_id,
NULL,
NULL,
(SELECT invoice_date
FROM ap_invoices_all
WHERE invoice_id = apid.parent_invoice_id)),
(SELECT NVL(gl_encumbered_date,creation_date)
FROM po_distributions
WHERE po_distribution_id = apid.po_distribution_id))
obligation_date,
DECODE(apid.po_distribution_id,
NULL,
DECODE(apid.parent_invoice_id,
NULL,
NULL,
(SELECT invoice_num
FROM ap_invoices_all
WHERE invoice_id = apid.parent_invoice_id)),
(SELECT segment1
FROM po_headers
WHERE po_header_id = (SELECT po_header_id
FROM po_distributions
WHERE po_distribution_id = apid.po_distribution_id)))
obligation_number
FROM
ap_checks_all apc,
ap_invoice_payments_all apip,
ap_invoices_all api,
ap_invoice_distributions_all apid
WHERE apip.set_of_books_id = l_set_of_books_id
AND apip.set_of_books_id = api.set_of_books_id
AND api.set_of_books_id = apid.set_of_books_id
AND apc.org_id = l_org_id
AND apc.org_id = apip.org_id
AND apip.org_id = api.org_id
AND api.org_id = apid.org_id
AND apc.check_id = apip.check_id
AND apip.invoice_id = api.invoice_id
AND api.invoice_id = apid.invoice_id
AND api.invoice_type_lookup_code IN ( 'CREDIT','DEBIT')
AND apc.payment_type_flag = 'R'
AND apip.posted_flag = 'Y'
AND reversal_inv_pmt_id IS NULL
AND ap_checks_pkg.get_posting_status(apc.check_id) IN ('Y','P')
AND TRUNC(apc.check_date) BETWEEN TRUNC(l_gl_start_date) AND TRUNC(l_gl_end_date)
AND NOT EXISTS
( SELECT 1 FROM fv_refunds_voids_all
WHERE invoice_payment_id = apip.invoice_payment_id)
/*
Commented for bug 14022463
AND EXISTS (SELECT 1
FROM gl_je_lines gljl,
gl_je_headers gljh,
ap_invoice_distributions_all apid
WHERE gljl.ledger_id = apid.set_of_books_id
AND gljl.status = 'P'
AND gljh.je_header_id = gljl.je_header_id
AND gljh.je_from_sla_flag = 'N'
AND gljl.code_combination_id = apid.DIST_CODE_COMBINATION_ID
AND apid.invoice_id = api.invoice_id
AND gljl.reference_2 = TO_CHAR(api.invoice_id)
UNION
SELECT 1
FROM gl_je_lines gljl,
gl_je_headers gljh,
gl_import_references glir,
xla_ae_lines xal,
xla_distribution_links xdl,
ap_invoice_distributions_all apid
WHERE gljl.ledger_id = apid.set_of_books_id
AND glir.je_batch_id = gljh.je_batch_id
AND glir.je_header_id = gljh.je_header_id
AND glir.je_line_num = gljl.je_line_num
AND gljl.status = 'P'
AND gljh.je_header_id = gljl.je_header_id
AND gljh.je_from_sla_flag = 'Y'
AND gljh.je_source = 'Payables'
AND xal.gl_sl_link_id = glir.gl_sl_link_id
AND xal.gl_sl_link_table = glir.gl_sl_link_table
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_id_num_1 = apid.invoice_distribution_id
AND gljl.code_combination_id = apid.DIST_CODE_COMBINATION_ID
AND apid.invoice_id = api.invoice_id
)*/
);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'No of Rows inserted into the FV table : '
|| SQL%ROWCOUNT ) ;
SELECT printer,
number_of_copies
INTO l_printer_name,
l_no_copies
FROM fnd_concurrent_requests
WHERE request_id = l_conc_request_id ;
'Deleting all the Data from FV table inserted by the current process') ;
DELETE FROM fv_refunds_voids_all
WHERE TYPE = 'PAYABLE_REFUND' AND conc_request_id = l_conc_request_id;
'No of Rows Deleted from FV table : ' || SQL%ROWCOUNT ) ;