[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * FROM fv_be_rpr_transactions
WHERE set_of_books_id = p_sob_id
AND approval_id = p_approval_id
AND transaction_status = 'IP'
ORDER BY budget_level_id;
SELECT workflow_flag
INTO l_workflow_flag
FROM fv_budget_options
WHERE set_of_books_id = p_sob_id;
'BEFORE INSERTING RECORD DOC NUMBER '||
l_trx_hdr_rec.doc_number);
insert_hdr_record(l_trx_hdr_rec);
'BEFORE INSERTING DETAIL RECORD');
insert_dtl_record(l_trx_dtl_rec);
UPDATE fv_be_rpr_transactions
SET transaction_status = 'PR'
WHERE transaction_id = l_rpr_rec.transaction_id;
UPDATE fv_be_trx_hdrs
SET doc_status = 'IP'
WHERE doc_id IN (l_from_doc_id, l_to_doc_id);
UPDATE fv_be_trx_dtls
SET transaction_status = 'IP'
WHERE doc_id IN (l_from_doc_id, l_to_doc_id);
UPDATE fv_be_rpr_transactions
SET transaction_status = 'IN'
WHERE set_of_books_id = p_sob_id
AND approval_id = p_approval_id
AND transaction_status = 'IP';
SELECT fv_be_trx_hdrs_s.NEXTVAL
INTO p_trx_hdr_rec.doc_id
FROM dual;
SELECT treasury_symbol_id
INTO p_trx_hdr_rec.treasury_symbol_id
FROM fv_fund_parameters
WHERE fund_value =
DECODE(p_count,1,p_rpr_rec.fund_value_from,2,p_rpr_rec.fund_value_to)
AND set_of_books_id = g_sob_id;
select h.budgeting_segments,
h.segment1,h.segment2,h.segment3,h.segment4,h.segment5,h.segment6,
h.segment7,h.segment8,h.segment9,h.segment10,
h.segment11,h.segment12,h.segment13,h.segment14,h.segment15,h.segment16,
h.segment17,h.segment18,h.segment19,h.segment20,h.segment21,h.segment22,h.segment23,
h.segment24, h.segment25,h.segment26,h.segment27,
h.segment28,h.segment29,h.segment30
into
p_trx_hdr_rec.budgeting_segments,
p_trx_hdr_rec.segment1,
p_trx_hdr_rec.segment2,
p_trx_hdr_rec.segment3,
p_trx_hdr_rec.segment4,
p_trx_hdr_rec.segment5,
p_trx_hdr_rec.segment6,
p_trx_hdr_rec.segment7,
p_trx_hdr_rec.segment8,
p_trx_hdr_rec.segment9,
p_trx_hdr_rec.segment10,
p_trx_hdr_rec.segment11,
p_trx_hdr_rec.segment12,
p_trx_hdr_rec.segment13,
p_trx_hdr_rec.segment14,
p_trx_hdr_rec.segment15,
p_trx_hdr_rec.segment16,
p_trx_hdr_rec.segment17,
p_trx_hdr_rec.segment18,
p_trx_hdr_rec.segment19,
p_trx_hdr_rec.segment20,
p_trx_hdr_rec.segment21,
p_trx_hdr_rec.segment22,
p_trx_hdr_rec.segment23,
p_trx_hdr_rec.segment24,
p_trx_hdr_rec.segment25,
p_trx_hdr_rec.segment26,
p_trx_hdr_rec.segment27,
p_trx_hdr_rec.segment28,
p_trx_hdr_rec.segment29,
p_trx_hdr_rec.segment30
from fv_be_trx_dtls d , fv_be_trx_hdrs h
where d.budgeting_segments = p_rpr_rec.distribution_from
and h.doc_id = d.doc_id
and h.budget_level_id = p_rpr_rec.budget_level_id
and rownum = 1;
p_trx_hdr_rec.last_update_date := g_sysdate;
p_trx_hdr_rec.last_updated_by := g_user_id;
p_trx_hdr_rec.last_update_login := g_login_id;
SELECT fv_be_trx_dtls_s.NEXTVAL
INTO p_trx_dtl_rec.transaction_id
FROM dual;
SELECT quarter_num
INTO p_trx_dtl_rec.quarter_num
FROM gl_period_statuses
WHERE set_of_books_id = g_sob_id
AND application_id = '101'
AND start_date <= p_rpr_rec.gl_date
AND end_date >= p_rpr_rec.gl_date
AND adjustment_period_flag='N';
p_trx_dtl_rec.last_update_date := g_sysdate;
p_trx_dtl_rec.last_updated_by := g_user_id;
p_trx_dtl_rec.last_update_login := g_login_id;
PROCEDURE insert_hdr_record(p_trx_hdr_rec fv_be_trx_hdrs%ROWTYPE) IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'insert_hdr_record';
INSERT INTO fv_be_trx_hdrs
(doc_id ,
doc_number ,
revision_num ,
internal_revision_num ,
treasury_symbol_id ,
fund_value ,
budget_level_id ,
transaction_date ,
doc_status ,
doc_total ,
source ,
budgeting_segments ,
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 ,
approval_id ,
approved_by_user_id ,
distribution_amount ,
old_doc_number ,
set_of_books_id ,
bu_group_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login)
VALUES
(p_trx_hdr_rec.doc_id ,
p_trx_hdr_rec.doc_number ,
p_trx_hdr_rec.revision_num ,
p_trx_hdr_rec.internal_revision_num ,
p_trx_hdr_rec.treasury_symbol_id ,
p_trx_hdr_rec.fund_value ,
p_trx_hdr_rec.budget_level_id ,
TRUNC(p_trx_hdr_rec.transaction_date) ,
p_trx_hdr_rec.doc_status ,
p_trx_hdr_rec.doc_total ,
p_trx_hdr_rec.source ,
p_trx_hdr_rec.budgeting_segments ,
p_trx_hdr_rec.segment1 ,
p_trx_hdr_rec.segment2 ,
p_trx_hdr_rec.segment3 ,
p_trx_hdr_rec.segment4 ,
p_trx_hdr_rec.segment5 ,
p_trx_hdr_rec.segment6 ,
p_trx_hdr_rec.segment7 ,
p_trx_hdr_rec.segment8 ,
p_trx_hdr_rec.segment9 ,
p_trx_hdr_rec.segment10 ,
p_trx_hdr_rec.segment11 ,
p_trx_hdr_rec.segment12 ,
p_trx_hdr_rec.segment13 ,
p_trx_hdr_rec.segment14 ,
p_trx_hdr_rec.segment15 ,
p_trx_hdr_rec.segment16 ,
p_trx_hdr_rec.segment17 ,
p_trx_hdr_rec.segment18 ,
p_trx_hdr_rec.segment19 ,
p_trx_hdr_rec.segment20 ,
p_trx_hdr_rec.segment21 ,
p_trx_hdr_rec.segment22 ,
p_trx_hdr_rec.segment23 ,
p_trx_hdr_rec.segment24 ,
p_trx_hdr_rec.segment25 ,
p_trx_hdr_rec.segment26 ,
p_trx_hdr_rec.segment27 ,
p_trx_hdr_rec.segment28 ,
p_trx_hdr_rec.segment29 ,
p_trx_hdr_rec.segment30 ,
p_trx_hdr_rec.approval_id ,
p_trx_hdr_rec.approved_by_user_id ,
p_trx_hdr_rec.distribution_amount ,
p_trx_hdr_rec.old_doc_number ,
p_trx_hdr_rec.set_of_books_id ,
p_trx_hdr_rec.bu_group_id ,
p_trx_hdr_rec.creation_date ,
p_trx_hdr_rec.created_by ,
p_trx_hdr_rec.last_update_date ,
p_trx_hdr_rec.last_updated_by ,
p_trx_hdr_rec.last_update_login );
g_errbuf := 'Error in insert_hdr_record procedure '||SQLERRM;
END; --procedure insert_hdr_record
PROCEDURE insert_dtl_record (p_trx_dtl_rec fv_be_trx_dtls%ROWTYPE) IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'insert_dtl_record';
INSERT INTO fv_be_trx_dtls
(transaction_id ,
doc_id ,
revision_num ,
transaction_status ,
gl_date ,
quarter_num ,
transaction_type_id ,
budgeting_segments ,
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 ,
public_law_code ,
advance_type ,
dept_id ,
main_account ,
transfer_description ,
sub_type ,
gl_transfer_flag ,
approved_by_user_id ,
posting_process_id ,
set_of_books_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login )
VALUES
(p_trx_dtl_rec.transaction_id ,
p_trx_dtl_rec.doc_id ,
p_trx_dtl_rec.revision_num ,
p_trx_dtl_rec.transaction_status ,
TRUNC(p_trx_dtl_rec.gl_date) ,
p_trx_dtl_rec.quarter_num ,
p_trx_dtl_rec.transaction_type_id ,
p_trx_dtl_rec.budgeting_segments ,
p_trx_dtl_rec.segment1 ,
p_trx_dtl_rec.segment2 ,
p_trx_dtl_rec.segment3 ,
p_trx_dtl_rec.segment4 ,
p_trx_dtl_rec.segment5 ,
p_trx_dtl_rec.segment6 ,
p_trx_dtl_rec.segment7 ,
p_trx_dtl_rec.segment8 ,
p_trx_dtl_rec.segment9 ,
p_trx_dtl_rec.segment10 ,
p_trx_dtl_rec.segment11 ,
p_trx_dtl_rec.segment12 ,
p_trx_dtl_rec.segment13 ,
p_trx_dtl_rec.segment14 ,
p_trx_dtl_rec.segment15 ,
p_trx_dtl_rec.segment16 ,
p_trx_dtl_rec.segment17 ,
p_trx_dtl_rec.segment18 ,
p_trx_dtl_rec.segment19 ,
p_trx_dtl_rec.segment20 ,
p_trx_dtl_rec.segment21 ,
p_trx_dtl_rec.segment22 ,
p_trx_dtl_rec.segment23 ,
p_trx_dtl_rec.segment24,
p_trx_dtl_rec.segment25 ,
p_trx_dtl_rec.segment26 ,
p_trx_dtl_rec.segment27 ,
p_trx_dtl_rec.segment28 ,
p_trx_dtl_rec.segment29 ,
p_trx_dtl_rec.segment30 ,
p_trx_dtl_rec.increase_decrease_flag ,
p_trx_dtl_rec.amount ,
p_trx_dtl_rec.public_law_code ,
p_trx_dtl_rec.advance_type ,
p_trx_dtl_rec.dept_id ,
p_trx_dtl_rec.main_account ,
p_trx_dtl_rec.transfer_description ,
p_trx_dtl_rec.sub_type ,
p_trx_dtl_rec.gl_transfer_flag ,
p_trx_dtl_rec.approved_by_user_id ,
p_trx_dtl_rec.posting_process_id ,
p_trx_dtl_rec.set_of_books_id ,
p_trx_dtl_rec.creation_date ,
p_trx_dtl_rec.created_by ,
p_trx_dtl_rec.last_update_date ,
p_trx_dtl_rec.last_updated_by ,
p_trx_dtl_rec.last_update_login );
g_errbuf := 'Error in insert_dtl_record procedure '||SQLERRM;
END; --procedure insert_dtl_record
UPDATE fv_be_trx_hdrs
SET doc_status = 'IN',
approved_by_user_id = NULL
WHERE doc_id IN (p_from_doc_id, p_to_doc_id);
UPDATE fv_be_trx_dtls
SET transaction_status = 'IN'
WHERE doc_id IN (p_from_doc_id, p_to_doc_id);