[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| prepare_journal_select |
| DESCRIPTION |
| Prepare the journal select dynamic SQL |
| CALLED BY |
| allocate |
--------------------------------------------------------------------- */
PROCEDURE prepare_journal_select IS
l_fiscal_journal_qry VARCHAR2(10000) := NULL;
JG_UTILITY_PKG.log('> JG_ALLOCATE_JOURNALS_PKG.prepare_journal_select');
l_fiscal_journal_qry := JG_ALLOCATE_JOURNALS_PKG.get_dynamic_select_string;
JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.prepare_journal_select');
END prepare_journal_select;
SELECT ccr.cc_range_id,
acr.account_range_id,
acr.offset_account,
ccr.cc_range_low,
ccr.cc_range_high
FROM jg_zz_ta_account_ranges acr,
jg_zz_ta_cc_ranges ccr
WHERE NVL(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cost_center, ccr.cc_range_low)
BETWEEN ccr.cc_range_low AND ccr.cc_range_high
AND ccr.rule_set_id = JG_JOURNAL_ALLOCATIONS_PKG.G_rule_set_id
AND ccr.cc_range_id = acr.cc_range_id (+)
AND JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.account_number
BETWEEN acr.account_range_low (+) AND acr.account_range_high (+)
ORDER BY acr.offset_account;
| Get_Dynamic_Select_String |
| DESCRIPTION |
| Substitutes in variable strings into overall SELECT string |
| CALLED BY |
| Create_Journal_Allocations |
| RETURNS |
| SELECT string |
--------------------------------------------------------------------- */
FUNCTION get_dynamic_select_string RETURN VARCHAR2 IS
l_sob_where VARCHAR2(200) := NULL;
JG_UTILITY_PKG.log( '> JG_ALLOCATE_JOURNALS_PKG.get_dynamic_select_string');
l_inline_view_clause := '(SELECT ccr.cc_range_id '||
', ccr.cc_range_low '||
', ccr.cc_range_high '||
', ccr.description '||
', acr.account_range_id '||
', acr.account_range_low '||
', acr.account_range_high '||
', acr.offset_account '||
'FROM jg_zz_ta_account_ranges acr '||
', jg_zz_ta_cc_ranges ccr '||
'WHERE ccr.cc_range_id = acr.cc_range_id '||
'AND ccr.rule_set_id = '||TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_rule_set_id)||') ranges, ';
JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.get_dynamic_select_string');
RETURN 'SELECT '||l_hint_clause||' jlv.je_batch_id '||
', jlv.je_batch_name '||
', jlv.je_header_id '||
', jlv.je_header_name '||
', jlv.currency_code '||
', jlv.currency_conversion_type '||
', jlv.currency_conversion_date '||
', jlv.currency_conversion_rate '||
', jlv.encumbrance_type_id '||
', jlv.budget_version_id '||
JG_JOURNAL_ALLOCATIONS_PKG.G_cc_seg_num_string||
JG_JOURNAL_ALLOCATIONS_PKG.G_acc_seg_num_string||
', jlv.segment1 '||
', jlv.segment2 '||
', jlv.segment3 '||
', jlv.segment4 '||
', jlv.segment5 '||
', jlv.segment6 '||
', jlv.segment7 '||
', jlv.segment8 '||
', jlv.segment9 '||
', jlv.segment10 '||
', jlv.segment11 '||
', jlv.segment12 '||
', jlv.segment13 '||
', jlv.segment14 '||
', jlv.segment15 '||
', jlv.segment16 '||
', jlv.segment17 '||
', jlv.segment18 '||
', jlv.segment19 '||
', jlv.segment20 '||
', jlv.segment21 '||
', jlv.segment22 '||
', jlv.segment23 '||
', jlv.segment24 '||
', jlv.segment25 '||
', jlv.segment26 '||
', jlv.segment27 '||
', jlv.segment28 '||
', jlv.segment29 '||
', jlv.segment30 '||
', jlv.je_line_num '||
', jlv.accounted_cr '||
', jlv.accounted_dr '||
', jlv.entered_cr '||
', jlv.entered_dr '||
', jlv.stat_amount '||
', jlv.subledger_doc_sequence_id '||
', jlv.subledger_doc_sequence_value '||
', jlv.attribute1 '||
', jlv.attribute2 '||
', jlv.attribute3 '||
', jlv.attribute4 '||
', jlv.attribute5 '||
', jlv.attribute6 '||
', jlv.attribute7 '||
', jlv.attribute8 '||
', jlv.attribute9 '||
', jlv.attribute10 '||
', jlv.attribute11 '||
', jlv.attribute12 '||
', jlv.attribute13 '||
', jlv.attribute14 '||
', jlv.attribute15 '||
', jlv.attribute16 '||
', jlv.attribute17 '||
', jlv.attribute18 '||
', jlv.attribute19 '||
', jlv.attribute20 '||
', jlv.context '||
', jlv.context2 '||
', jlv.context3 '||
', jlv.invoice_date '||
', jlv.tax_code '||
', jlv.invoice_identifier '||
', jlv.invoice_amount '||
', jlv.ussgl_transaction_code '||
', jlv.jgzz_recon_ref '||
', jlv.code_combination_id '||
', jlv.row_id '||
', jlv.effective_date '||
', jlv.external_reference '||
', jlv.je_doc_sequence_name '||
', jlv.je_doc_sequence_value '||
', jlv.alloc_row_id '||
', jlv.period_name '||
l_non_view_columns||
'FROM '||l_inline_view_clause||
l_rule_sets_clause||
' jg_zz_ta_je_lines_v jlv '||
'WHERE '||l_cc_range_where ||
l_acct_range_where ||
l_rule_set_where ||
l_account_type_where ||
l_sob_where ||
l_period_name_where ||
l_currency_code_where ||
l_bal_seg_where ||
l_bal_type_where ||
l_budenc_where ||
l_allocate_where ||
l_order_clause;
END get_dynamic_select_string;
SELECT je_header_id
INTO l_header_id
FROM gl_je_lines
WHERE rowid = CHARTOROWID(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.row_id)
FOR UPDATE OF je_header_id NOWAIT;
-- may have been null because the account_range_id was null (see dynamic select)
-- and we need to return a specific message as to why the journal line failed
--
IF (NOT JG_ALLOCATE_JOURNALS_PKG.get_cc_acc_range_ids) THEN
-- return false, but no error message to be displayed, just continue processing
p_err_msg_code := NULL;
| Insert the journal line into the allocated lines table to show |
| that the line has been successfully allocated |
| ELSIF in unallocation mode THEN |
| Delete journal line from allocated lines table to show that the |
| line has been unallocated |
| END IF |
| END LOOP |
| IF in allocation mode THEN |
| IF last journal line processed had an offset account defined at the |
| account range level THEN |
| Add offset allocation line to array |
| Insert allocation line in GL_INTERFACE |
| END IF |
| Write Details of Allocated Lines to Output File from array |
| END IF |
| |
| CALLED BY |
| JG_JOURNAL_ALLOCATION_PKG.main |
--------------------------------------------------------------------------------*/
PROCEDURE allocate IS
l_dummy_int INTEGER;
JG_ALLOCATE_JOURNALS_PKG.prepare_journal_select;
JG_UTILITY_PKG.debug( 'Execute Journal Select');
JG_ZZ_TA_ALLOCATED_LINES_PKG.insert_row(
x_rowid,
JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_batch_id,
JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_header_id,
JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_line_num,
SYSDATE,
JG_JOURNAL_ALLOCATIONS_PKG.G_user_id,
JG_JOURNAL_ALLOCATIONS_PKG.G_user_id,
SYSDATE,
JG_JOURNAL_ALLOCATIONS_PKG.G_login_id,
JG_JOURNAL_ALLOCATIONS_PKG.G_request_id,
JG_JOURNAL_ALLOCATIONS_PKG.G_progr_appl_id,
JG_JOURNAL_ALLOCATIONS_PKG.G_conc_progr_id,
SYSDATE);
JG_UTILITY_PKG.log('> JG_ZZ_TA_ALLOCATED_LINES_PKG.delete_row');
JG_ZZ_TA_ALLOCATED_LINES_PKG.delete_row(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.alloc_row_id);
JG_UTILITY_PKG.log('< JG_ZZ_TA_ALLOCATED_LINES_PKG.delete_row');