[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cat.user_je_category_name,
src.user_je_source_name,
usr.user_conversion_type
INTO JG_JOURNAL_ALLOCATIONS_PKG.G_user_je_category_name,
JG_JOURNAL_ALLOCATIONS_PKG.G_user_je_source_name,
JG_JOURNAL_ALLOCATIONS_PKG.G_translated_user
FROM GL_JE_SOURCES src,
GL_JE_CATEGORIES cat,
GL_DAILY_CONVERSION_TYPES usr
WHERE src.je_source_name = JG_JOURNAL_ALLOCATIONS_PKG.G_destn_journal_source AND
cat.je_category_name = JG_JOURNAL_ALLOCATIONS_PKG.G_destn_journal_category AND
usr.conversion_type = 'User';
SELECT name
INTO JG_JOURNAL_ALLOCATIONS_PKG.G_set_of_books_name -- for report displaying purposes
FROM gl_sets_of_books
WHERE set_of_books_id = JG_JOURNAL_ALLOCATIONS_PKG.G_set_of_books_id;
JG_JOURNAL_ALLOCATIONS_PKG.G_acct_flex_segmt_arr.DELETE;
JG_CREATE_JOURNALS_PKG.alloc_lines_arr.DELETE;
JG_JOURNAL_ALLOCATIONS_PKG.G_zero_fill_arr.DELETE;
| selected rule set prior to looping through each source journal |
| line. Here is a list of the checks performed: |
| 1) Checks that account ranges within separate cost center ranges|
| do not overlap. This could lead to multiple allocations |
| of the same source journal line. |
| 2) Checks that at least one allocation rule line exists for |
| each account range |
| 3) If allocation lines exist, it checks they add up to 100% if |
| partial allocation has not been set for the rule |
| 4) If allocation lines exist, it checks they do not add up to |
| greater than 100% if partial allocation has been set for the |
| rule |
| 5) Checks that there is an offset account defined at the account|
| range level if the total number of offsets at the rule line |
| level does not equal the total number of rule lines. |
| 6) Informs whether or not there is at least one offset account |
| defined at the account range level |
| CALLED BY |
| JG_JOURNAL_ALLOCATIONS_PKG.main |
| RETURNS |
| TRUE if valid rule set, FALSE otherwise. Error |
| Message Code returned if FALSE. |
--------------------------------------------------------------------- */
FUNCTION valid_rule_set(p_err_msg_code IN OUT NOCOPY VARCHAR2
,p_acct_rnge_offset IN OUT NOCOPY BOOLEAN) RETURN BOOLEAN IS
CURSOR c_rule_set IS
SELECT rs.partial_allocation partial_allocation
, ccr.cc_range_low cc_range_low
, ccr.cc_range_high cc_range_high
, acr.account_range_low account_range_low
, acr.account_range_high account_range_high
, acr.offset_account acc_range_offset_acct
, acr.account_range_id account_range_id
, SUM(rl.allocation_percent) total_percent
, COUNT(*) total_num_of_lines
, SUM(DECODE(rl.offset_account, NULL, 0, 1)) total_num_of_offsets
FROM jg_zz_ta_rule_lines rl
, jg_zz_ta_account_ranges acr
, jg_zz_ta_cc_ranges ccr
, jg_zz_ta_rule_sets rs
WHERE rs.rule_set_id = ccr.rule_set_id
AND ccr.cc_range_id = acr.cc_range_id
AND acr.account_range_id = rl.account_range_id (+)
AND rs.rule_set_id = JG_JOURNAL_ALLOCATIONS_PKG.G_rule_set_id
GROUP BY rs.partial_allocation
, ccr.cc_range_low
, ccr.cc_range_high
, acr.account_range_low
, acr.account_range_high
, acr.offset_account
, acr.account_range_id;
SELECT MAX(group_id)
INTO l_group_id
FROM gl_interface_groups_v
WHERE set_of_books_id = JG_JOURNAL_ALLOCATIONS_PKG.G_destn_set_of_books_id
AND user_je_source_name = JG_JOURNAL_ALLOCATIONS_PKG.G_destn_journal_source;
GL_INTERFACE_CONTROL_PKG.Insert_Row(--JG_JOURNAL_ALLOCATIONS_PKG.G_destn_set_of_books_id, Removed, ledger Arch. changes in package
l_interface_run_id,
JG_JOURNAL_ALLOCATIONS_PKG.G_destn_journal_source,
JG_JOURNAL_ALLOCATIONS_PKG.G_destn_set_of_books_id,
l_group_id,
NULL);
APPS_DDL.apps_ddl('UPDATE gl_je_lines l '||
'SET l.description = (SELECT h.description '||
'FROM gl_je_headers h '||
'WHERE h.je_header_id = l.je_header_id) '||
'WHERE l.je_header_id IN (SELECT h.je_header_id '||
'FROM gl_je_headers h '||
', gl_je_batches b '||
'WHERE b.je_batch_id = h.je_batch_id '||
'AND b.name LIKE '''||TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_Request_Id)||'%'''||
' AND b.default_period_name = '''||JG_JOURNAL_ALLOCATIONS_PKG.G_destn_period_name||
''' AND b.ledger_id = '||TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_destn_set_of_books_id)||')');
JG_UTILITY_PKG.debug('Error in Update statement after journal import run');
| In addition, the allocations will be inserted into the |
| GL_Interface table ready for Journal Import (if running in |
| non-validation mode), a record will be kept of those fiscal |
| lines that have been allocated and it will run Journal Import |
| if the user chose to do so. |
--------------------------------------------------------------------------------*/
PROCEDURE main(errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2,
p_set_of_books_id IN NUMBER,
p_chart_of_accounts_id IN NUMBER,
p_functional_currency IN VARCHAR2,
p_period_set_name IN VARCHAR2,
p_rule_set_id IN NUMBER,
p_period_name IN VARCHAR2,
p_currency_code IN VARCHAR2,
p_amount_type IN VARCHAR2,
p_balance_type IN VARCHAR2,
p_balance_type_id IN NUMBER,
p_balance_segment_value IN VARCHAR2,
p_destn_set_of_books_id IN NUMBER,
p_destn_period_name IN VARCHAR2,
p_destn_journal_source IN VARCHAR2,
p_destn_journal_category IN VARCHAR2,
p_destn_segment_method IN VARCHAR2,
p_destn_cost_center_grouping IN VARCHAR2,
p_error_handling IN VARCHAR2,
p_validate_only IN VARCHAR2,
p_run_journal_import IN VARCHAR2,
p_destn_summary_level IN VARCHAR2,
p_import_desc_flexfields IN VARCHAR2,
p_post_errors_to_suspense IN VARCHAR2,
p_debug_flag IN VARCHAR2) IS
l_err_msg_code VARCHAR2(50);