[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT application_column_name,flex_value_set_id
FROM fnd_id_flex_segments ffs,
gl_ledgers_public_v gsb
WHERE ffs.application_id = 101
AND ffs.id_flex_code = 'GL#'
AND ffs.id_flex_num = gsb.chart_of_accounts_id
AND gsb.ledger_id = v_sob_id
ORDER BY ffs.segment_num;
PROCEDURE update_err_code(l_rowid VARCHAR2,
l_err_code VARCHAR2,
l_err_reason VARCHAR2);
PROCEDURE update_cleanup(parm_source IN VARCHAR2,
parm_group_id IN NUMBER);
PROCEDURE update_err_rec(v_rec_number IN NUMBER);
-- Cursor for selecting records from
-- fv_be_interface
-- BCPSA-BE Enhancement - Modified the cursor to get the Sub_Type instead of Transaction_Code
CURSOR int IS
SELECT rowid, set_of_books_id, gl_date, record_number,
budget_level_id, budgeting_segments, fund_value, doc_number,
amount, increase_decrease_flag, transaction_type,
sub_type, segment1, segment2, segment3, segment4,
segment5, segment6, segment7, segment8, segment9, segment10,
segment11, segment12, segment13, segment14, segment15,
segment16, segment17, segment18, segment19, segment20,
segment21, segment22, segment23, segment24, segment25,
segment26, segment27, segment28, segment29, segment30,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
source, group_id, corrected_flag, public_law_code, advance_type,
dept_id, main_account, transfer_description, attribute_category,
budget_user_id
FROM fv_be_interface
WHERE source = parm_source
AND group_id = parm_group_id
AND set_of_books_id = parm_ledger_id
AND status IN ('NEW','REJECTED','ACCEPTED')
ORDER BY budget_level_id ;
SELECT cr_account_segment_value,
dr_account_segment_value
FROM fv_be_account_pairs acc,
fv_be_trx_sub_types tst
WHERE acc.be_tt_id = tst.be_tt_id
and tst.sub_type =p_sub_type
AND chart_of_accounts_id = v_coa_id
AND acc.ledger_id = tst.ledger_id
AND acc.ledger_id = p_ledger_id;
UPDATE fv_be_interface_control
SET status = 'IN PROCESS',
date_processed = SYSDATE
WHERE source = parm_source
AND group_id = parm_group_id
AND status IN ('NEW','REJECTED','ACCEPTED');
SELECT count(*)
INTO v_interface_count
FROM fv_be_interface
WHERE source = parm_source
AND group_id = parm_group_id
AND set_of_books_id = parm_ledger_id
AND status IN ('NEW', 'REJECTED','ACCEPTED');
UPDATE fv_be_interface
SET error_code = NULL,
error_reason = NULL
WHERE source = parm_source
AND group_id = parm_group_id
AND set_of_books_id = parm_ledger_id;
segs_array.DELETE;
val_set_id_array.DELETE;
update_err_code(int_rec.rowid,'EM03',
'Invalid Set of Books ID');
update_err_code(int_rec.rowid,'EP06', 'Invalid GL Date');
update_err_code(int_rec.rowid, 'EM29',
'Invalid Budget Level');
update_err_code(int_rec.rowid, 'EU01',
'Invalid Budget User');
update_err_code(int_rec.rowid, 'EU02',
'Insufficient Access for Budget User');
update_err_code(int_rec.rowid, 'EM33',
'Fund Value not defined in Budget Distributions');
update_err_code(int_rec.rowid, 'EM34',
'Cancelled or Expired Treasury Symbol');
update_err_code(int_rec.rowid, 'EM35',
'Invalid Increase / Decrease Flag');
update_err_code(int_rec.rowid, 'EM36',
'Amount must be equal to or greater than zero');
update_err_code(int_rec.rowid, 'EM45',
'Public Law Code should not be more than 7 characters');
update_err_code(int_rec.rowid, 'EM46',
'Invalid Advance Type');
update_err_code(int_rec.rowid, 'EM47',
'Invalid Transfer Dept ID and/or Transfer Main Account');
update_err_code(int_rec.rowid, 'EM28',
'Invalid Transaction Type');
update_err_code(int_rec.rowid, 'EM10',
'Invalid Sub Type');
update_err_code(int_rec.rowid, 'EM10',
'Invalid Sub Type');
update_err_code(int_rec.rowid, 'EM39',
'Previously existing document with same document
number has not been approved');
update_err_code(int_rec.rowid, 'EM40',
'Document Number must be numeric');
update_err_code(int_rec.rowid, 'EM44',
'Fund Value is not the same for document number,
set of books, source and budget level');
update_err_code(int_rec.rowid,'ED01', SUBSTR(l_dff_error_message, 1, 255));
'SELECT '||app_col_name.application_column_name||'_TYPE,'||
app_col_name.application_column_name||
' FROM fv_budget_distribution_dtl
WHERE set_of_books_id = :set_of_books_id
AND budget_level_id = :budget_level_id
AND fund_value = :fund_value ';
SELECT chart_of_accounts_id
INTO v_coa_id
FROM gl_ledgers_public_v
WHERE ledger_id = int_rec.set_of_books_id;
'SELECT '||app_col_rec.application_column_name||'_TYPE
FROM fv_budget_distribution_dtl
WHERE set_of_books_id = :set_of_books_id
AND budget_level_id = :budget_level_id
AND fund_value = :fund_value ';
-- value is null then update the record as error and
-- exit (no need to validate the remaining segments)
IF (l_seg_type = 'E' AND l_seg_value IS NULL)
THEN
update_err_code(int_rec.rowid,'EM31',
'There are more segments required for this budget level');
'SELECT '||app_col_rec.application_column_name||'_TYPE
FROM fv_budget_distribution_dtl
WHERE set_of_books_id = :set_of_books_id
AND budget_level_id = :budget_level_id
AND fund_value = :fund_value ';
SELECT validation_type
into l_validation_type
FROM FND_FLEX_VALUE_SETS
WHERE flex_value_set_id=val_set_id_array(substr(app_col_rec.application_column_name,8,2));
SELECT
application_table_name,
value_column_name
INTO l_application_table_name,l_value_column_name
FROM FND_FLEX_VALIDATION_TABLES
WHERE flex_value_set_id=val_set_id_array(substr(app_col_rec.application_column_name,8,2));
l_table_stmt := ' SELECT 1 FROM '||l_application_table_name ||
' WHERE ' || l_value_column_name ||' = :b_seg_value' ;
SELECT 'x'
INTO v_exists
FROM fnd_flex_values
WHERE flex_value_set_id = val_set_id_array(substr(app_col_rec.application_column_name,8,2))
AND flex_value = segs_array(substr(app_col_rec.application_column_name,8,2))
AND enabled_flag = 'Y';
update_err_code(int_rec.rowid, 'EM37',
'Invalid Segment values');
SELECT concatenated_segment_delimiter
INTO v_delimiter
FROM fnd_id_flex_structures ffs,
gl_ledgers_public_v gsb
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND ffs.id_flex_num = gsb.chart_of_accounts_id
AND gsb.ledger_id = int_rec.set_of_books_id;
FOR cols_rec IN (SELECT application_column_name
FROM fnd_id_flex_segments
WHERE id_flex_code = 'GL#'
AND id_flex_num = v_coa_id
ORDER BY segment_num)
LOOP
v_index := v_index + 1;
'SELECT '||app_col_rec.application_column_name||'_TYPE
FROM fv_budget_distribution_dtl
WHERE set_of_books_id = :set_of_books_id
AND fund_value = :fund_value
AND budget_level_id =
(SELECT MAX(budget_level_id)
FROM fv_budget_distribution_dtl
WHERE fund_value = :fund_value
AND set_of_books_id = :set_of_books_id
AND budget_level_id < :budget_level_id )';
'SELECT '||app_col_rec.application_column_name||
' FROM fv_budget_distribution_dtl
WHERE set_of_books_id = :set_of_books_id
AND fund_value = :fund_value
AND budget_level_id =
(SELECT MAX(budget_level_id)
FROM fv_budget_distribution_dtl
WHERE fund_value = :fund_value
AND set_of_books_id = :set_of_books_id
AND budget_level_id < :budget_level_id )';
update_err_code(int_rec.rowid,'EM43',
'Segments failed cross validation');
update_err_code(int_rec.rowid,'EM41',
'Missing segment value in budgeting segments');
UPDATE fv_be_interface
SET status = 'ACCEPTED',
processed_flag = 'Y'
WHERE rowid = int_rec.rowid ;
SELECT count(*)
INTO v_rej_rec_count
FROM fv_be_interface
WHERE group_id = parm_group_id
AND source = parm_source
AND set_of_books_id = parm_ledger_id
AND status = 'REJECTED';
segs_array.DELETE;
select 'x' into v_source_exists
from fv_lookup_codes
where lookup_type='BE_SOURCE'
AND lookup_code=valid_rec.source;
segment values before inserting');
'SELECT '||app_col_name.application_column_name||'_TYPE,'||
app_col_name.application_column_name||
' FROM fv_budget_distribution_dtl
WHERE set_of_books_id = :set_of_books_id
AND budget_level_id = :budget_level_id
AND fund_value = :fund_value ';
SELECT be_tt_id,
public_law_code_flag,
advance_flag,
transfer_flag
INTO v_tt_id,
g_pub_law_code_flag,
g_advance_flag,
g_transfer_flag
FROM fv_be_transaction_types
WHERE set_of_books_id = valid_rec.set_of_books_id
AND budget_level_id = valid_rec.budget_level_id
AND apprn_transaction_type = valid_rec.transaction_type;
SELECT internal_revision_num, doc_id, revision_num
INTO v_int_rev_num, v_doc_id, v_revision_num
FROM fv_be_trx_hdrs
WHERE set_of_books_id = valid_rec.set_of_books_id
AND budget_level_id = valid_rec.budget_level_id
AND doc_number = valid_rec.doc_number
AND source = valid_rec.source
FOR UPDATE OF doc_total;
-- then update it to IN or RA
-- set the internal rev num to -9999 to distinguish
-- existing hdr from a new hdr and later update it
-- to 0
IF ins_hdr THEN
SELECT fv_be_trx_hdrs_s.nextval
INTO new_doc_id
FROM DUAL;
SELECT treasury_symbol_id
INTO v_ts_id
FROM fv_fund_parameters
WHERE fund_value = valid_rec.fund_value
AND set_of_books_id = valid_rec.set_of_books_id;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INSERTING NEW HEADER RECORD');
-- Select the budget group id for the User
BEGIN
SELECT bu_group_id
INTO l_bu_group_id
FROM fv_budget_user_dtl
WHERE set_of_books_id = valid_rec.set_of_books_id
AND bu_user_id = valid_rec.budget_user_id
AND valid_rec.budget_level_id BETWEEN bu_access_level_from AND bu_access_level_to;
INSERT INTO fv_be_trx_hdrs (
budgeting_segments, budget_level_id,
doc_id, doc_number, doc_status,
doc_total, fund_value, internal_revision_num, revision_num,
set_of_books_id,bu_group_id, source, transaction_date,
treasury_symbol_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
segment1, segment2, segment3, segment4,
segment5, segment6, segment7, segment8, segment9, segment10,
segment11, segment12, segment13, segment14, segment15,
segment16, segment17, segment18, segment19, segment20,
segment21, segment22, segment23, segment24, segment25,
segment26, segment27, segment28, segment29, segment30)
VALUES
(v_bud_segs, valid_rec.budget_level_id,
new_doc_id, valid_rec.doc_number, 'IMPORTING',
0, valid_rec.fund_value, -9999, 0,
valid_rec.set_of_books_id,l_bu_group_id, valid_rec.source, TRUNC(SYSDATE),
v_ts_id, fnd_global.user_id, SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.login_id,
segs_array(1), segs_array(2), segs_array(3),
segs_array(4), segs_array(5), segs_array(6),
segs_array(7), segs_array(8), segs_array(9),
segs_array(10), segs_array(11), segs_array(12),
segs_array(13), segs_array(14), segs_array(15),
segs_array(16), segs_array(17), segs_array(18),
segs_array(19), segs_array(20), segs_array(21),
segs_array(22), segs_array(23), segs_array(24),
segs_array(25), segs_array(26), segs_array(27),
segs_array(28), segs_array(29), segs_array(30));
-- and insert into the record.
v_num_segs := 0;
dummy_array.DELETE;
SELECT concatenated_segment_delimiter
INTO v_delimiter
FROM fnd_id_flex_structures ffs,
gl_ledgers_public_v gsb
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND ffs.id_flex_num = gsb.chart_of_accounts_id
AND gsb.ledger_id = valid_rec.set_of_books_id;
'SELECT '||app_col_rec.application_column_name||'_TYPE
FROM fv_budget_distribution_dtl
WHERE set_of_books_id = :set_of_books_id
AND fund_value = :fund_value
AND budget_level_id =
(SELECT MAX(budget_level_id)
FROM fv_budget_distribution_dtl
WHERE fund_value = :fund_value
AND set_of_books_id = :set_of_books_id
AND budget_level_id < :budget_level_id )';
'SELECT '||app_col_rec.application_column_name||
' FROM fv_budget_distribution_dtl
WHERE set_of_books_id = :set_of_books_id
AND fund_value = :fund_value
AND budget_level_id =
(SELECT MAX(budget_level_id)
FROM fv_budget_distribution_dtl
WHERE fund_value = :fund_value
AND set_of_books_id = :set_of_books_id
AND budget_level_id < :budget_level_id )';
SELECT fbd.segment1, fbd.segment2, fbd.segment3, fbd.segment4,
fbd.segment5, fbd.segment6, fbd.segment7, fbd.segment8,
fbd.segment9, fbd.segment10,fbd.segment11,fbd.segment12,
fbd.segment13,fbd.segment14,fbd.segment15,fbd.segment16,
fbd.segment17,fbd.segment18,fbd.segment19,fbd.segment20,
fbd.segment21,fbd.segment22,fbd.segment23,fbd.segment24,
fbd.segment25,fbd.segment26,fbd.segment27, fbd.segment28,
fbd.segment29, fbd.segment30
INTO v_segment1, v_segment2, v_segment3, v_segment4,
v_segment5, v_segment6, v_segment7, v_segment8,
v_segment9, v_segment10, v_segment11, v_segment12,
v_segment13, v_segment14, v_segment15, v_segment16,
v_segment17, v_segment18, v_segment19, v_segment20,
v_segment21, v_segment22, v_segment23, v_segment24,
v_segment25, v_segment26, v_segment27, v_segment28,
v_segment29, v_segment30
FROM fv_be_trx_hdrs fbh,
fv_be_trx_dtls fbd
WHERE fbh.fund_value = valid_rec.fund_value
AND fbh.set_of_books_id = valid_rec.set_of_books_id
AND fbh.doc_id = fbd.doc_id
AND fbh.set_of_books_id = fbd.set_of_books_id
AND fbd.budgeting_segments = valid_rec.budgeting_segments
AND rownum < 2
AND fbh.budget_level_id =
(SELECT MAX(budget_level_id)
-- FROM fv_budget_distribution_dtl
FROM fv_be_trx_dtls
WHERE fund_value = valid_rec.fund_value
AND set_of_books_id = valid_rec.set_of_books_id
AND budget_level_id < valid_rec.budget_level_id
);
update_err_rec(valid_rec.record_number);
SELECT bu_group_id
INTO l_bu_group_id
FROM fv_budget_user_dtl
WHERE set_of_books_id = valid_rec.set_of_books_id
AND bu_user_id = valid_rec.budget_user_id
AND valid_rec.budget_level_id BETWEEN bu_access_level_from and bu_access_level_to;
INSERT INTO fv_be_trx_hdrs (
budgeting_segments, budget_level_id,
doc_id, doc_number, doc_status,
doc_total, fund_value, internal_revision_num, revision_num,
set_of_books_id, bu_group_id,source, transaction_date,
treasury_symbol_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
segment1, segment2, segment3, segment4, segment5,
segment6, segment7, segment8, segment9, segment10,
segment11, segment12, segment13, segment14, segment15,
segment16, segment17, segment18, segment19, segment20,
segment21, segment22, segment23, segment24, segment25,
segment26, segment27, segment28, segment29, segment30)
VALUES
(valid_rec.budgeting_segments, valid_rec.budget_level_id,
new_doc_id, valid_rec.doc_number, 'IMPORTING',
0, valid_rec.fund_value, -9999, 0,
valid_rec.set_of_books_id, l_bu_group_id,valid_rec.source, TRUNC(SYSDATE),
v_ts_id, fnd_global.user_id, SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.login_id,
v_segment1, v_segment2, v_segment3, v_segment4, v_segment5,
v_segment6, v_segment7, v_segment8, v_segment9, v_segment10,
v_segment11, v_segment12, v_segment13, v_segment14,
v_segment15, v_segment16, v_segment17, v_segment18,
v_segment19, v_segment20, v_segment21, v_segment22,
v_segment23, v_segment24, v_segment25, v_segment26,
v_segment27, v_segment28, v_segment29, v_segment30);
INSERT INTO fv_be_trx_dtls
(
amount,
budgeting_segments,
doc_id,
gl_date,
quarter_num,
gl_transfer_flag,
increase_decrease_flag,
revision_num,
set_of_books_id,
sub_type,
transaction_id,
transaction_status,
transaction_type_id,
source,
group_id,
corrected_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
public_law_code,
advance_type,
dept_id,
main_account,
transfer_description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
(
valid_rec.amount,
v_bud_segs,
v_doc_id,
valid_rec.gl_date,
v_quarter_num,
'N',
valid_rec.increase_decrease_flag,
v_revision_num,
valid_rec.set_of_books_id,
valid_rec.sub_type,
fv_be_trx_dtls_s.nextval,
'IN',
v_tt_id,
valid_rec.source,
valid_rec.group_id,
valid_rec.corrected_flag,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
segs_array(1),
segs_array(2),
segs_array(3),
segs_array(4),
segs_array(5),
segs_array(6),
segs_array(7),
segs_array(8),
segs_array(9),
segs_array(10),
segs_array(11),
segs_array(12),
segs_array(13),
segs_array(14),
segs_array(15),
segs_array(16),
segs_array(17),
segs_array(18),
segs_array(19),
segs_array(20),
segs_array(21),
segs_array(22),
segs_array(23),
segs_array(24),
segs_array(25),
segs_array(26),
segs_array(27),
segs_array(28),
segs_array(29),
segs_array(30),
valid_rec.public_law_code,
valid_rec.advance_type,
valid_rec.dept_id,
valid_rec.main_account,
valid_rec.transfer_description,
valid_rec.attribute_category,
DECODE(validation, 'N', NULL, valid_rec.attribute1),
DECODE(validation, 'N', NULL, valid_rec.attribute2),
DECODE(validation, 'N', NULL, valid_rec.attribute3),
DECODE(validation, 'N', NULL, valid_rec.attribute4),
DECODE(validation, 'N', NULL, valid_rec.attribute5),
DECODE(validation, 'N', NULL, valid_rec.attribute6),
DECODE(validation, 'N', NULL, valid_rec.attribute7),
DECODE(validation, 'N', NULL, valid_rec.attribute8),
DECODE(validation, 'N', NULL, valid_rec.attribute9),
DECODE(validation, 'N', NULL, valid_rec.attribute10),
DECODE(validation, 'N', NULL, valid_rec.attribute11),
DECODE(validation, 'N', NULL, valid_rec.attribute12),
DECODE(validation, 'N', NULL, valid_rec.attribute13),
DECODE(validation, 'N', NULL, valid_rec.attribute14),
DECODE(validation, 'N', NULL, valid_rec.attribute15)
);
SELECT DECODE(valid_rec.increase_decrease_flag,'I',
valid_rec.amount, (-1 * valid_rec.amount))
INTO v_amount FROM DUAL;
UPDATE fv_be_trx_hdrs
SET doc_status = 'IMPORTING',
doc_total = doc_total + v_amount
WHERE doc_id = v_doc_id;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RUNNING UPDATE CLEANUP');
update_cleanup(parm_source,parm_group_id);
PROCEDURE update_err_code(l_rowid VARCHAR2, l_err_code VARCHAR2,
l_err_reason VARCHAR2) IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'update_err_code';
UPDATE fv_be_interface
SET error_code = l_err_code,
error_reason = l_err_reason,
status = 'REJECTED',
processed_flag = 'Y'
WHERE rowid = l_rowid;
END update_err_code;
SELECT 'x'
INTO v_exists
FROM gl_ledgers_public_v
WHERE ledger_id = v_sob_id;
SELECT quarter_num
INTO v_quarter_num
FROM gl_period_statuses
WHERE v_gl_date BETWEEN start_date AND end_date
AND set_of_books_id = v_set_of_books_id
AND closing_status IN ('O','F')
AND adjustment_period_flag = 'N'
AND application_id = 101;
SELECT 'x'
INTO v_exists
FROM fv_budget_levels
WHERE budget_level_id = v_budget_level_id
AND set_of_books_id = v_set_of_books_id;
SELECT COUNT(*)
INTO l_count
FROM fv_budget_user_dtl
WHERE set_of_books_id = p_sob_id
AND bu_user_id = p_bu_user_id;
l_update_flag VARCHAR2(1);
SELECT NVL(bu_update_flag,'N')
INTO l_update_flag
FROM fv_budget_user_dtl
WHERE set_of_books_id = p_sob_id
AND bu_user_id = p_bu_user_id
AND p_budget_level_id BETWEEN bu_access_level_from AND bu_access_level_to;
IF l_update_flag ='N' THEN
v_error_code := -11;
SELECT 'x'
INTO v_exists
FROM fv_budget_distribution_dtl fbd
WHERE fbd.set_of_books_id = v_set_of_books_id
AND fbd.fund_value = v_fund_value
AND fbd.budget_level_id = v_budget_level_id;
SELECT fts.expiration_date, fts.cancellation_date,
fts.treasury_symbol_id
INTO l_expire_date, l_cancel_date, l_tsid
FROM fv_treasury_symbols fts,
fv_budget_distribution_hdr fbh
WHERE fts.treasury_symbol_id = fbh.treasury_symbol_id
AND fts.set_of_books_id = fbh.set_of_books_id
AND fbh.fund_value = v_fund_value
AND fbh.set_of_books_id = v_set_of_books_id;
SELECT public_law_code_flag, advance_flag,
transfer_flag, sub_type_flag
INTO g_pub_law_code_flag, g_advance_flag,
g_transfer_flag,g_sub_type_flag
FROM fv_be_transaction_types
WHERE set_of_books_id = v_set_of_books_id
AND budget_level_id = v_budget_level_id
AND apprn_transaction_type = v_trx_type;
SELECT lookup_code
INTO g_advance_type_code
FROM fv_lookup_codes
WHERE lookup_type = 'ADVANCE_FLAG'
AND description = v_advance_type;
l_update_flag VARCHAR2(1);
SELECT sub_type_flag
INTO l_subtype_flag
FROM fv_be_transaction_types
WHERE set_of_books_id = v_set_of_books_id
AND budget_level_id = v_budget_level_id
AND apprn_transaction_type = v_trx_type;
SELECT 'X'
INTO v_exists
FROM FV_BE_TRANSACTION_TYPES T, FV_BE_TRX_SUB_TYPES S
WHERE T.BE_TT_ID = S.BE_TT_ID
AND T.BUDGET_LEVEL_ID = V_BUDGET_LEVEL_ID
AND T.APPRN_TRANSACTION_TYPE = V_TRX_TYPE
AND S.SUB_TYPE = V_SUB_TYPE
AND t.set_of_books_id = v_set_of_books_id
AND s.ledger_id = v_set_of_books_id;
SELECT doc_status, revision_num, doc_id, fund_value
INTO l_doc_status, l_revision_num, l_doc_id, l_fund_value
FROM fv_be_trx_hdrs
WHERE set_of_books_id = v_set_of_books_id
AND budget_level_id = v_budget_level_id
AND doc_number = v_doc_number
AND source = v_source;
SELECT doc_num_entry, doc_num_type
INTO l_doc_entry, l_doc_type
FROM fv_budget_levels
WHERE set_of_books_id = v_set_of_books_id
AND budget_level_id = v_budget_level_id;
SELECT to_number(v_doc_number)
INTO l_doc_number
FROM DUAL;
'SELECT '||app_col_name.application_column_name||'_TYPE,'||
app_col_name.application_column_name||
' FROM fv_budget_distribution_dtl
WHERE set_of_books_id = :set_of_books_id
AND budget_level_id = :budget_level_id
AND fund_value = :fund_value ';
-- D, then update the current row with the default segment value
lv_stmt := NULL;
'UPDATE fv_be_interface
SET '||app_col_name.application_column_name||
' = '||''''||lv_seg_value||''''||
' WHERE rowid = :rowid ';
SELECT concatenated_segment_delimiter
INTO l_delmtr
FROM fnd_id_flex_structures ffs,
gl_ledgers_public_v gsb
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND ffs.id_flex_num = gsb.chart_of_accounts_id
AND gsb.ledger_id = l_sob_id;
PROCEDURE update_cleanup(parm_source IN VARCHAR2,
parm_group_id IN NUMBER) IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'update_cleanup';
UPDATE fv_be_trx_hdrs fbh
SET revision_num =
(SELECT MAX(revision_num)
FROM fv_be_trx_dtls fbd
WHERE fbh.doc_id = fbd.doc_id)
WHERE fbh.doc_status = 'IMPORTING';
UPDATE fv_be_trx_hdrs
SET doc_status = 'RA'
WHERE doc_status = 'IMPORTING'
AND internal_revision_num <> -9999;
UPDATE fv_be_trx_hdrs
SET doc_status = 'IN',
internal_revision_num = 0
WHERE doc_status = 'IMPORTING'
AND internal_revision_num = -9999;
UPDATE fv_be_interface_control
SET status = 'IMPORTED'
WHERE source = parm_source
AND group_id = parm_group_id;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INSERTING INTO HISTORY TABLE');
INSERT INTO fv_be_interface_history
(record_number, set_of_books_id, source, group_id, error_code,
error_reason, budget_level_id,
budgeting_segments, transaction_type, sub_type,
fund_value, period_name, segment1, segment2, segment3,
segment4, segment5, segment6, segment7, segment8, segment9,
segment10, segment11, segment12, segment13, segment14,
segment15, segment16, segment17, segment18, segment19,
segment20, segment21, segment22, segment23, segment24,
segment25, segment26, segment27, segment28, segment29,
segment30, increase_decrease_flag, amount, doc_number,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14,
attribute15, attribute_category, processed_flag, status,
date_created, created_by, corrected_flag, last_update_date,
last_updated_by, public_law_code, advance_type, dept_id,
main_account, transfer_description,budget_user_id,
gl_date)
SELECT
record_number, set_of_books_id, source, group_id, error_code,
error_reason, budget_level_id,
budgeting_segments, transaction_type, sub_type,
fund_value, period_name, segment1, segment2, segment3,
segment4, segment5, segment6, segment7, segment8, segment9,
segment10, segment11, segment12, segment13, segment14,
segment15, segment16, segment17, segment18, segment19,
segment20, segment21, segment22, segment23, segment24,
segment25, segment26, segment27, segment28, segment29,
segment30, increase_decrease_flag, amount, doc_number,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14,
attribute15, attribute_category, processed_flag, status,
date_created, created_by, corrected_flag, sysdate,
fnd_global.user_id, public_law_code, advance_type,
dept_id, main_account, transfer_description,budget_user_id,
gl_date
FROM fv_be_interface
WHERE source = parm_source
AND group_id = parm_group_id
AND set_of_books_id = parm_ledger_id
AND status = 'ACCEPTED'
AND processed_flag = 'Y';
DELETE FROM fv_be_interface
WHERE source = parm_source
AND group_id = parm_group_id
AND set_of_books_id = parm_ledger_id
AND status = 'ACCEPTED'
AND processed_flag = 'Y';
errbuf := 'When no data found error in update_cleanup';
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'WHEN NO DATA FOUND ERROR IN UPDATE_CLEANUP');
errbuf := 'When others error in update_cleanup.'||SQLERRM;
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'WHEN OTHERS ERROR IN UPDATE_CLEANUP');
END update_cleanup;
PROCEDURE update_err_rec(v_rec_number IN NUMBER) IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'update_err_rec';
UPDATE fv_be_interface
-- SET status = 'REJECTED',
SET status = 'ACCEPTED',
error_code = 'EM42',
error_reason = 'Budgeting Segments do not exist for
previous budget level'
WHERE record_number = v_rec_number;
END update_err_rec;
UPDATE fv_be_interface_control
SET status = 'REJECTED'
WHERE source = parm_source
AND group_id = parm_group_id;