[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_headers_selected NUMBER := 0; -- No. of headers selected
statement := 'select ' || g_lines_sequence_name ||
'.NEXTVAL from dual';
PROCEDURE validate_periods(p_selection_type IN VARCHAR2,
p_sob_list IN t_sob_list,
p_program_name IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE ) IS
l_periods VARCHAR2(30);
SELECT gps.period_name, gps.start_date, gps.end_date, gps.closing_status
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = c_sob_id
AND Nvl(gps.adjustment_period_flag,'N') = 'N'
AND gps.end_date >= c_start_date
AND gps.start_date <= c_end_date
AND gps.closing_status NOT IN ('O','F')
ORDER BY gps.start_date;
IF p_selection_type = 1 THEN
-- Get the start date of the first open or future open
-- period and end date of the last open period.
BEGIN
SELECT min(start_date), max(end_date)
INTO l_open_start_date, l_open_end_date
FROM gl_period_statuses
WHERE application_id = 101
AND set_of_books_id = p_sob_list(i).sob_id
AND Nvl(adjustment_period_flag,'N') = 'N'
AND closing_status IN ( 'O','F');
' SELECT COUNT(*)
FROM dual
WHERE EXISTS (
SELECT ''x''
FROM ' || g_headers_table ||
' WHERE accounting_date BETWEEN :b_begin_date AND :b_end_date
AND set_of_books_id = :sob_id
AND gl_transfer_flag = ''N'')';
END IF; -- Selection Type
l_statement := ' SELECT MIN(accounting_date), MAX(accounting_date)
FROM ' || g_headers_table ||
' WHERE gl_transfer_run_id = :b_transfer_run_id ';
PROCEDURE select_acct_headers( p_selection_type NUMBER,
p_set_of_books_id NUMBER,
p_source_id NUMBER DEFAULT NULL,
p_source_table VARCHAR2 DEFAULT NULL,
p_transfer_run_id NUMBER,
p_request_id NUMBER,
p_ae_category t_ae_category,
p_start_date DATE,
p_end_date DATE,
p_legal_entity_id NUMBER,
p_cost_group_id NUMBER,
p_cost_type_id NUMBER,
p_validate_account VARCHAR2 ) IS
statement VARCHAR2(4000) ;
l_log_module := C_DEFAULT_MODULE||'.select_acct_headers';
(p_msg => 'BEGIN of procedure SELECT_ACCT_HEADERS'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
IF Nvl(p_selection_type,1) = 1 THEN -- for batch transfer
IF p_legal_entity_id IS NOT NULL THEN
-- Manufacturing Transfer
l_where := ' AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
AND aeh.legal_entity_id = :b_legal_entity_id
AND aeh.cost_group_id = :b_cost_group_id
AND aeh.cost_type_id = :b_cost_type_id ';
' AND NOT EXISTS ( SELECT ''x'' FROM ' || g_lines_table || ' ael
WHERE ael.ae_header_id = aeh.ae_header_id
AND ael.accounting_error_code IS NOT NULL ) ';
( SELECT ''x''
FROM ' || g_events_table || ' ace
WHERE aeh.accounting_event_id = ace.accounting_event_id
AND ace.event_status_code = ''ACCOUNTED WITH ERROR'' ) ';
( SELECT ''x''
FROM ' || g_events_table || ' ace
WHERE aeh.accounting_event_id = ace.accounting_event_id
AND ace.event_status_code = ''ACCOUNTED'' ) ';
( SELECT ''x''
FROM ' || g_events_table || ' ace
WHERE aeh.accounting_event_id = ace.accounting_event_id
AND ace.event_status_code = ''ACCOUNTED''
AND ace.source_id = :b_source_id
AND ace.source_table = :b_source_table) ';
( SELECT ''x''
FROM ' || g_events_table || ' ace
WHERE aeh.accounting_event_id = ace.accounting_event_id
AND ace.event_status_code = ''ACCOUNTED WITH ERROR''
AND ace.source_id = :b_source_id
AND ace.source_table = :b_source_table) ';
xla_message('XLA_GLT_SELECTING_HEADERS',
'','',
'','',
'','',
l_log_module,
C_LEVEL_STATEMENT);
statement := ' UPDATE ' || g_headers_table || ' aeh
SET program_update_date = Sysdate,
program_id = :b_program_id,
request_id = :b_request_id,
gl_transfer_run_id = :b_transfer_run_id,
gl_transfer_error_code = NULL,
gl_transfer_flag = ''Y''
WHERE gl_transfer_run_id = -1
AND gl_transfer_flag IN ( ''N'',''E'')
AND aeh.accounting_error_code IS NULL ' || l_where;
IF Nvl(p_selection_type,1) = 1 THEN -- for batch transfer
IF p_legal_entity_id IS NOT NULL THEN
-- Manufacturing Transfer
dbms_sql.bind_variable(cid,':b_legal_entity_id', p_legal_entity_id);
g_headers_selected := rows_processed;
Nvl(g_control_info(g_periods_cnt).rec_transferred,0) + g_headers_selected;
xla_message('XLA_GLT_SELECTED_HEADERS','COUNT',rows_processed,'','','','',
l_log_module,
C_LEVEL_STATEMENT);
IF Nvl(p_selection_type,1) = 1 THEN -- for batch transfer
IF p_legal_entity_id IS NULL THEN
-- Bug2708663. Removed the extra Exists Condition.
statement := ' SELECT COUNT(aeh.gl_transfer_run_id)
FROM ' || g_headers_table || ' aeh
WHERE gl_transfer_run_id = -1 ' || l_where_error;
(p_msg => 'BEGIN of procedure SELECT_ACCT_HEADERS'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
END select_acct_headers;
PROCEDURE validate_acct_lines( p_selection_type NUMBER,
p_set_of_books_id NUMBER,
p_coa_id NUMBER,
p_transfer_run_id NUMBER,
p_start_date DATE,
p_end_date DATE ) IS
statement VARCHAR2(4000) ;
'UPDATE ' || g_lines_table || ' ael
SET ael.gl_transfer_error_code =
( SELECT Decode(gcc.detail_budgeting_allowed_flag, ''N'', ''POST'',
Decode(gcc.summary_flag, ''Y'', ''POST'',
Decode(template_id, NULL,
Decode(enabled_flag, ''N'', ''DISABLED'',
Decode(nvl(gcc.code_combination_id,-1) , -1, ''INVALID'',
Decode(Sign(gcc.start_date_active - aeh.accounting_date), 1, ''INACTIVE'',
Decode(Sign(aeh.accounting_date - gcc.end_date_active), 1, ''INACTIVE'',
NULL)))),
''POST''))) FROM ' || g_headers_table || ' aeh, gl_code_combinations gcc
WHERE aeh.ae_header_id = ael.ae_header_id
AND gcc.code_combination_id = ael.code_combination_id
AND gcc.chart_of_accounts_id = :b_coa_id )
WHERE ael.ae_header_id IN ( SELECT ae_header_id FROM ' || g_headers_table || ' aeh
WHERE aeh.gl_transfer_run_id = :b_transfer_run_id
AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date) ';
'UPDATE ' || g_lines_table || ' ael
SET ael.gl_transfer_error_code =
( SELECT Decode(gcc.detail_posting_allowed_flag, ''N'', ''POST'',
Decode(gcc.summary_flag, ''Y'', ''POST'',
Decode(template_id, NULL,
Decode(enabled_flag, ''N'', ''DISABLED'',
Decode(nvl(gcc.code_combination_id,-1) , -1, ''INVALID'',
Decode(Sign(gcc.start_date_active - aeh.accounting_date), 1, ''INACTIVE'',
Decode(Sign(aeh.accounting_date - gcc.end_date_active), 1, ''INACTIVE'',
NULL)))),
''POST''))) FROM ' || g_headers_table || ' aeh, gl_code_combinations gcc
WHERE aeh.ae_header_id = ael.ae_header_id
AND gcc.code_combination_id = ael.code_combination_id
AND gcc.chart_of_accounts_id = :b_coa_id )
WHERE ael.ae_header_id IN ( SELECT ae_header_id FROM ' || g_headers_table || ' aeh
WHERE aeh.gl_transfer_run_id = :b_transfer_run_id
AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date) ';
xla_message('XLA_GLT_LINES_UPDATED','COUNT',rows_processed,'','','','',
l_log_module,
C_LEVEL_STATEMENT);
PROCEDURE validate_acct_headers ( p_selection_type NUMBER,
p_set_of_books_id NUMBER,
p_transfer_run_id NUMBER,
p_start_date DATE,
p_end_date DATE ) IS
cid NUMBER;
statement := ' UPDATE ' || g_headers_table || ' aeh
SET aeh.gl_transfer_run_id = -1,
aeh.gl_transfer_flag = ''E''
WHERE aeh.gl_transfer_run_id = :b_transfer_run_id
AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
AND EXISTS ( SELECT ''x'' FROM ' || g_lines_table || ' ael
WHERE ael.ae_header_id = aeh.ae_header_id
AND ael.gl_transfer_error_code IS NOT NULL )';
g_headers_selected := g_headers_selected - l_invalid_headers;
IF (g_headers_selected > 0) THEN
g_proceed := 'Y';
+ (g_headers_selected);
xla_message('XLA_GLT_HEADERS_TRANSFERRED','COUNT',g_headers_selected ,'','','','',
l_log_module,
C_LEVEL_STATEMENT);
xla_message('XLA_GLT_UPDATE_ENC_LINKID','','','','','','',
l_log_module,
C_LEVEL_STATEMENT);
statement := 'UPDATE ' || g_encumbrance_table ||
' SET program_update_date = Sysdate,
program_id = :b_program_id,
request_id = :b_request_id,
gl_sl_link_id = ' || g_enc_sequence_name || '.NEXTVAL
WHERE ae_header_id IN ( SELECT ae_header_id FROM ' || g_headers_table
|| ' WHERE gl_transfer_run_id = :b_transfer_run_id
AND accounting_date BETWEEN :b_start_date AND :b_end_date )';
xla_message('XLA_GLT_UPDATE_ENC_LINES','COUNT',rows_processed,'','','','',
l_log_module,
C_LEVEL_STATEMENT);
xla_message('XLA_GLT_INSERTING_ENC_LINES','','','','','','',
l_log_module,
C_LEVEL_STATEMENT);
statement := 'INSERT INTO gl_interface(
status, set_of_books_id,
user_je_source_name, user_je_category_name,
accounting_date, currency_code,
date_created, created_by,
actual_flag, encumbrance_type_id,
code_combination_id, stat_amount,
entered_dr, entered_cr,
accounted_dr, accounted_cr,
reference1, reference2,
reference7, reference8,
reference5, reference10,
reference21, reference22,
reference23, reference24,
reference25, reference26,
reference27, reference28,
reference29, reference30,
subledger_doc_sequence_id,
subledger_doc_sequence_value,
gl_sl_link_table, gl_sl_link_id,
je_header_id, group_id
)
SELECT
''NEW'', aeh.set_of_books_id,
:b_source_name, jc.user_je_category_name,
aeh.accounting_date, :b_base_currency_code,
Sysdate, :b_user_id,
''E'', ael.encumbrance_type_id,
ael.code_combination_id, stat_amount,
accounted_dr, accounted_cr,
accounted_dr, accounted_cr,
:b_batch_name, :b_batch_desc,
aeh.gl_reversal_flag,
Decode(Nvl(aeh.gl_reversal_flag,''N''), ''Y'',
Decode(Nvl(:b_average_balances_flag,''N''),
''Y'',to_char(:b_reversal_date),:b_next_period),NULL),
:b_je_desc, :b_je_line_desc,
ael.reference1, ael.reference2,
ael.reference3, ael.reference4,
ael.reference5, ael.reference6,
ael.reference7, ael.reference8,
ael.reference9, ael.reference10,
ael.subledger_doc_sequence_id,
ael.subledger_doc_sequence_value,
:b_link_table, ael.gl_sl_link_id,
-1, :b_group_id
FROM '|| g_headers_table ||' aeh, '|| g_encumbrance_table ||
' ael, gl_je_categories jc
WHERE ael.ae_header_id = aeh.ae_header_id
AND aeh.set_of_books_id = :b_set_of_books_id
AND aeh.gl_transfer_run_id = :b_transfer_run_id
AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
AND jc.je_category_name = aeh.ae_category';
xla_message('XLA_GLT_INSERTED_ENC_LINES','COUNT',rows_processed,'','','','',
l_log_module,
C_LEVEL_STATEMENT);
PROCEDURE gl_insert_summary( p_request_id NUMBER,
p_source_name VARCHAR2,
p_transfer_run_id NUMBER,
p_period_name VARCHAR2,
p_start_date DATE,
p_end_date DATE,
p_next_period VARCHAR2,
p_reversal_date DATE,
p_average_balances_flag VARCHAR2,
p_gl_transfer_mode VARCHAR2,
p_group_id NUMBER,
p_batch_desc VARCHAR2,
p_je_desc VARCHAR2,
p_je_line_desc VARCHAR2) IS
statement_summary VARCHAR2(10000) ;
l_select_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
l_insert_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
l_log_module := C_DEFAULT_MODULE||'.gl_insert_summary';
(p_msg => 'BEGIN of procedure GL_INSERT_SUMMARY'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_select_actual_flag := '''A'',';
l_insert_actual_flag := '';
l_select_actual_flag := 'NVL(ael.actual_flag,''A''), aeh.budget_version_id,';
l_insert_actual_flag := 'budget_version_id,';
statement_summary := 'INSERT INTO gl_interface(
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
'|| l_insert_actual_flag ||'
code_combination_id,
stat_amount,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference1,
reference2,
reference5,
reference10,
reference7,
reference8,
reference21,
gl_sl_link_id,
gl_sl_link_table,
request_id,
ussgl_transaction_code,
je_header_id,
group_id
)
SELECT /*+ ORDERED */
jc.je_category_name,
aeh.set_of_books_id,
:b_source_name,
jc.user_je_category_name,
aeh.accounting_date ,
ael.currency_code,
Sysdate,
:b_user_id,
'|| l_select_actual_flag ||'
ael.code_combination_id,
SUM(stat_amount),
SUM(entered_dr),
SUM(entered_cr),
SUM(accounted_dr),
SUM(accounted_cr),
:b_batch_name,
:b_batch_desc,
:b_je_desc,
:b_je_line_desc,
aeh.gl_reversal_flag,
Decode(Nvl(aeh.gl_reversal_flag,''N''), ''Y'',
Decode(Nvl(:b_average_balances_flag,''N''),
''Y'',to_char(:b_reversal_date),:b_next_period),NULL),
To_char(:b_transfer_run_id),
xla_gl_transfer_pkg.get_linkid(:b_program_name),
:b_link_table,
:b_request_id,
ael.ussgl_transaction_code,
:b_transfer_run_id,
:b_group_id '
|| l_from ||
l_where ||
' GROUP BY aeh.set_of_books_id, aeh.ae_category,jc.je_category_name,
jc.user_je_category_name, aeh.accounting_date,
aeh.gl_reversal_flag, ael.currency_code,
ael.code_combination_id,ael.ussgl_transaction_code,
Decode(Sign(ael.entered_dr), 1,''dr'', -1, ''dr'',
0,Decode(Sign(ael.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'')
'|| l_group_by_actual_flag ;
statement_summary := 'INSERT INTO gl_interface(
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
'|| l_insert_actual_flag ||'
encumbrance_type_id,
code_combination_id,
stat_amount,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference1,
reference2,
reference5,
reference10,
reference7,
reference8,
reference21,
gl_sl_link_id,
gl_sl_link_table,
request_id,
ussgl_transaction_code,
je_header_id,
group_id,
period_name
)
SELECT /*+ ORDERED */
jc.je_category_name,
aeh.set_of_books_id,
:b_source_name,
jc.user_je_category_name,
:b_end_date,
ael.currency_code,
Sysdate,
:b_user_id,
'|| l_select_actual_flag ||'
NULL,
ael.code_combination_id,
SUM(stat_amount),
SUM(entered_dr),
SUM(entered_cr),
SUM(accounted_dr),
SUM(accounted_cr),
:b_batch_name,
:b_batch_desc,
:b_je_desc,
:b_je_line_desc,
aeh.gl_reversal_flag,
Decode(Nvl(aeh.gl_reversal_flag,''N''), ''Y'',
Decode(Nvl(:b_average_balances_flag,''N''),
''Y'',To_char(:b_reversal_date),:b_next_period),NULL),
To_char(:b_transfer_run_id),
xla_gl_transfer_pkg.get_linkid(:b_program_name), :b_link_table,
:b_request_id,
ael.ussgl_transaction_code,
:b_transfer_run_id,
:b_group_id,
:b_period_name '
|| l_from ||
l_where ||
' GROUP BY aeh.set_of_books_id, aeh.ae_category, jc.je_category_name,jc.user_je_category_name,
aeh.period_name, aeh.gl_reversal_flag, ael.currency_code,
ael.code_combination_id, ael.ussgl_transaction_code,
Decode(Sign(ael.entered_dr), 1,''dr'', -1, ''dr'',
0,Decode(Sign(ael.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'')
'|| l_group_by_actual_flag;
xla_message('XLA_GLT_GL_INSERT','COUNT','(summary) ' || rows_processed,'','','','',
l_log_module,
C_LEVEL_STATEMENT);
(p_msg => 'END of procedure GL_INSERT_SUMMARY'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
END gl_insert_summary ;
PROCEDURE gl_insert_detail( p_request_id NUMBER,
p_source_name VARCHAR2,
p_transfer_run_id NUMBER,
p_period_name VARCHAR2,
p_start_date DATE,
p_end_date DATE,
p_next_period VARCHAR2,
p_reversal_date DATE,
p_average_balances_flag VARCHAR2,
p_gl_transfer_mode VARCHAR2,
p_group_id NUMBER,
p_batch_desc VARCHAR2,
p_je_desc VARCHAR2,
p_je_line_desc VARCHAR2) IS
statement_detail VARCHAR2(10000) ;
l_select_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
l_insert_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
l_log_module := C_DEFAULT_MODULE||'.gl_insert_detail';
(p_msg => 'BEGIN of procedure GL_INSERT_DETAIL'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_select_actual_flag := '''A'',';
l_insert_actual_flag := '';
l_select_actual_flag := 'NVL(ael.actual_flag,''A''), aeh.budget_version_id,';
l_insert_actual_flag := 'budget_version_id,';
statement_detail := 'INSERT INTO gl_interface(
status, set_of_books_id,
user_je_source_name, user_je_category_name,
accounting_date, currency_code,
date_created, created_by,
actual_flag,
'|| l_insert_actual_flag ||'
code_combination_id, stat_amount,
entered_dr, entered_cr,
accounted_dr, accounted_cr,
reference1, reference2,
reference3, reference5,
reference7, reference8,
reference10,
reference21, reference22,
reference23, reference24,
reference25, reference26,
reference27, reference28,
reference29, reference30,
subledger_doc_sequence_id,
subledger_doc_sequence_value,
gl_sl_link_table,
gl_sl_link_id, request_id,
ussgl_transaction_code,
je_header_id, group_id,
period_name
)
SELECT /*+ ORDERED */
''NEW'', aeh.set_of_books_id,
:b_source_name, jc.user_je_category_name,
aeh.accounting_date, ael.currency_code,
Sysdate, :b_user_id,
'|| l_select_actual_flag ||'
ael.code_combination_id, stat_amount,
entered_dr, entered_cr,
accounted_dr, accounted_cr,
:b_batch_name , :b_batch_desc,
NULL, :b_je_desc,
aeh.gl_reversal_flag,
Decode(Nvl(aeh.gl_reversal_flag,''N''),
''Y'',Decode(Nvl(:b_average_balances_flag,''N''),
''Y'',To_char(:b_reversal_date),:b_next_period),NULL),
Nvl(ael.description, :b_je_line_desc),
Nvl(ael.reference1,:b_transfer_run_id),
ael.reference2,
ael.reference3, ael.reference4,
ael.reference5, ael.reference6,
ael.reference7, ael.reference8,
ael.reference9, ael.reference10,
ael.subledger_doc_sequence_id,
ael.subledger_doc_sequence_value,
:b_link_table,
ael.gl_sl_link_id, :b_request_id,
ael.ussgl_transaction_code,
:b_transfer_run_id, :b_group_id,
aeh.period_name
' || l_from
|| l_where;
xla_message('XLA_GLT_GL_INSERT','COUNT','(Detail) ' || rows_processed,'','','','',
l_log_module,
C_LEVEL_STATEMENT);
(p_msg => 'END of procedure GL_INSERT_DETAIL'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
END gl_insert_detail;
PROCEDURE update_linkid_summary( p_request_id NUMBER,
p_gl_transfer_mode VARCHAR2,
p_transfer_run_id NUMBER,
p_start_date DATE,
p_end_date DATE
) IS
statement VARCHAR2(2000) ;
l_log_module := C_DEFAULT_MODULE||'.update_linkid_summary';
(p_msg => 'BEGIN of procedure update_linkid_summary'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
xla_message('XLA_GLT_UPDATE_SUM_LINKID','','','','','','',
l_log_module,
C_LEVEL_STATEMENT);
'UPDATE ' || g_lines_table || ' ael
SET program_update_date = Sysdate,
program_id = :b_program_id,
request_id = :b_request_id,
gl_sl_link_id =
(
SELECT gi.gl_sl_link_id
FROM gl_interface gi, ' || g_headers_table || ' aeh
WHERE gi.request_id = :b_request_id
AND gi.je_header_id = :b_transfer_run_id
AND aeh.gl_transfer_run_id = :b_transfer_run_id
AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
AND Decode(Nvl(aeh.cross_currency_flag,''N''), ''Y'', ''Cross Currency'',
aeh.ae_category) = gi.status
AND Nvl(aeh.gl_reversal_flag,''N'') = nvl(gi.reference7,''N'')
AND gi.gl_sl_link_table = :b_actual_table_alias '
|| l_and || '
AND aeh.set_of_books_id = gi.set_of_books_id '
|| l_budget_version ||'
AND ael.code_combination_id = gi.code_combination_id
AND ael.currency_code = gi.currency_code
AND aeh.ae_header_id = ael.ae_header_id
AND Decode(Sign(gi.entered_dr), 1,''dr'', -1, ''dr'', 0,
Decode(Sign(gi.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'') =
Decode(Sign(ael.entered_dr), 1,''dr'', -1, ''dr'', 0,
Decode(Sign(ael.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'')
)
WHERE ael.ae_header_id IN ( SELECT ae_header_id
FROM ' || g_headers_table ||
' WHERE gl_transfer_run_id = :b_transfer_run_id
AND accounting_date BETWEEN :b_start_date AND :b_end_date )';
(p_msg => 'END of procedure update_linkid_summary'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
END update_linkid_summary ;
PROCEDURE update_linkid_detail( p_transfer_run_id NUMBER,
p_request_id NUMBER,
p_start_date DATE,
p_end_date DATE) IS
statement VARCHAR2(2000) ;
l_log_module := C_DEFAULT_MODULE||'.update_linkid_detail';
(p_msg => 'BEGIN of procedure UPDATE_LINKID_DETAIL'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
statement := 'UPDATE ' || g_lines_table || ' ael ' ||
' SET program_update_date = Sysdate,
program_id = :b_program_id,
request_id = :b_request_id,
gl_sl_link_id = ' || g_lines_sequence_name || '.NEXTVAL
WHERE ae_header_id in
( SELECT ae_header_id
FROM ' || g_headers_table ||
' WHERE gl_transfer_run_id = :b_transfer_run_id
AND accounting_date BETWEEN :b_start_date AND :b_end_date )' ;
(p_msg => 'END of procedure UPDATE_LINKID_DETAIL'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
END update_linkid_detail;
PROCEDURE check_input_param(p_selection_type NUMBER,
p_start_date DATE,
p_end_date DATE,
p_gl_transfer_mode VARCHAR2,
p_source_doc_id NUMBER,
p_source_document_table VARCHAR2) IS
l_log_module VARCHAR2(255);
IF p_selection_type = 1 THEN
-- Date validation
IF p_start_date IS NOT NULL THEN
IF p_start_date > p_end_date THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
xla_message('XLA_GLT_INVALID_DATE_RANGE','','','','','','',
l_log_module,
C_LEVEL_EXCEPTION);
ELSIF p_selection_type = 2 THEN
IF (p_source_doc_id IS NULL) OR (p_source_document_table IS NULL) THEN
IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
xla_message('','Source document Id and Source document table should be NULL for document Transfer','','','','','',
l_log_module,
C_LEVEL_EXCEPTION);
xla_message('XLA_GLT_INVALID_SELECTION_TYPE','','','','','','',
l_log_module,
C_LEVEL_EXCEPTION);
SELECT COUNT(*)
INTO l_budget_entries
FROM dual
WHERE EXISTS ( SELECT 'x'
FROM gl_interface
WHERE user_je_source_name = p_user_source_name
AND group_id = p_group_id
AND set_of_books_id = p_set_of_books_id );
SELECT enable_budgetary_control_flag
INTO l_budget_control_flag
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
FOR select_line_type_rec IN ( SELECT Line_Type_Code
FROM xla_je_line_types
WHERE application_id = g_application_id
AND summary_flag = 'D' )
LOOP
IF g_line_type IS NULL THEN
g_line_type := '''' || select_line_type_rec.Line_Type_Code || ''',';
g_line_type := g_line_type || '''' || select_line_type_rec.Line_Type_Code || ''',';
| p_selection_type Transfer Type 1-Batch , 2- Doc. Level Transfer |
| p_fc_force_flag Force flag for the funds checker. |
| |
| NOTES |
| |
+===========================================================================*/
PROCEDURE xla_gl_transfer(p_application_id NUMBER,
p_user_id NUMBER,
p_org_id NUMBER,
p_request_id NUMBER,
p_program_name VARCHAR2,
p_selection_type NUMBER DEFAULT 1,
p_sob_list t_sob_list,
p_batch_name VARCHAR2,
p_source_doc_id NUMBER DEFAULT NULL,
p_source_document_table VARCHAR2 DEFAULT NULL,
p_start_date DATE,
p_end_date DATE,
p_journal_category t_ae_category,
p_validate_account VARCHAR2,
p_gl_transfer_mode VARCHAR2,
p_submit_journal_import VARCHAR2,
p_summary_journal_entry VARCHAR2,
p_process_days NUMBER ,
p_batch_desc VARCHAR2 DEFAULT NULL,
p_je_desc VARCHAR2 DEFAULT NULL,
p_je_line_desc VARCHAR2 DEFAULT NULL,
p_fc_force_flag BOOLEAN DEFAULT TRUE,
p_debug_flag VARCHAR2
) IS
l_start_date DATE;
SELECT gp1.period_name, gp1.start_date, gp1.end_date,
gp2.period_name, gp2.start_date
FROM gl_period_statuses gp1,
gl_period_statuses gp2
WHERE gp1.application_id = 101
AND gp1.set_of_books_id = c_sob_id
AND gp1.end_date >= Nvl(c_start_date,gp1.end_date-1)
AND gp1.start_date <= c_end_date
AND gp1.closing_status = DECODE( g_entry_type,'A', DECODE( gp1.closing_status, 'O', 'O', 'F', 'F','Z'),
'B', gp1.closing_status )
AND nvl(gp1.adjustment_period_flag,'N') = 'N'
AND gp2.application_id(+) = 101
AND gp2.set_of_books_id(+) = c_sob_id
AND gp2.start_date(+) = gp1.end_date+1
AND nvl(gp2.adjustment_period_flag,'N') = 'N'
ORDER BY gp1.start_date;
xla_message('' , 'p_selection_type = ' || p_selection_type,'','','','','',
l_log_module,
C_LEVEL_PROCEDURE);
check_input_param(p_selection_type,
p_start_date,
p_end_date,
p_gl_transfer_mode,
p_source_doc_id,
p_source_document_table
);
SELECT je_source_name, account_validation_flag, period_status_table_name,
pre_commit_api_name, application_id, NVL(entry_type,'A')
INTO l_source_name, l_acct_validation_flag, g_periods_table,
l_pre_commit_api, l_application_id, g_entry_type
FROM xla_gl_transfer_programs
WHERE program_name = p_program_name;
SELECT user_je_source_name
INTO l_user_source_name
FROM gl_je_sources js
WHERE je_source_name = l_source_name;
IF p_selection_type = 1 THEN -- this is only for SRS, Doc Transfer will call it later.
IF(g_entry_type = 'A') THEN
validate_periods(p_selection_type,
p_sob_list,
p_program_name,
p_start_date,
p_end_date
);
SELECT chart_of_accounts_id
INTO l_coa_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_set_of_books_id;
SELECT xla_gl_transfer_runid_s.NEXTVAL
INTO l_transfer_run_id
FROM dual;
SELECT gl_interface_control_s.NEXTVAL, gl_journal_import_s.NEXTVAL
INTO l_group_id, l_interface_run_id
FROM dual;
xla_message('XLA_GLT_INSERT_XTB','','','','','','',
l_log_module,
C_LEVEL_STATEMENT);
INSERT INTO xla_gl_transfer_batches_all
( gl_transfer_run_id,
request_id ,
application_id ,
user_id ,
selection_type ,
set_of_books_id ,
batch_name,
source_id ,
source_table ,
transfer_from_date,
transfer_to_date,
ae_category ,
gl_transfer_mode ,
submit_journal_import ,
summary_journal_entry ,
process_days ,
gl_transfer_date,
group_id,
interface_run_id,
org_id,
legal_entity_id,
cost_group_id,
cost_type_id,
transfer_status
)
VALUES
( l_transfer_run_id,
p_request_id,
p_application_id,
p_user_id,
p_selection_type ,
p_sob_list(i).sob_id ,
g_batch_name ,
p_source_doc_id ,
p_source_document_table ,
p_start_date ,
p_end_date ,
p_journal_category(1),
p_gl_transfer_mode ,
NVL(p_submit_journal_import,'Y') ,
NVL(p_summary_journal_entry,'N') ,
p_process_days ,
Sysdate,
l_group_id,
l_interface_run_id,
p_org_id,
p_sob_list(i).legal_entity_id,
p_sob_list(i).cost_group_id,
p_sob_list(i).cost_type_id,
'P'
);
IF p_selection_type = 1 THEN
-- If processing more than one period then break the date range into
-- multiple peirods.
OPEN c_getPeriods(p_sob_list(i).sob_id,
p_start_date,
p_end_date
);
select_acct_headers( p_selection_type,
l_set_of_books_id,
p_source_doc_id,
p_source_document_table,
l_transfer_run_id,
p_request_id,
p_journal_category,
l_start_date,
l_end_date,
p_sob_list(i).legal_entity_id,
p_sob_list(i).cost_group_id,
p_sob_list(i).cost_type_id,
p_validate_account);
validate_acct_lines( p_selection_type,
l_set_of_books_id,
l_coa_id,
l_transfer_run_id,
l_start_date,
l_end_date);
validate_acct_headers( p_selection_type,
l_set_of_books_id,
l_transfer_run_id,
l_start_date,
l_end_date);
xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
'',l_log_module,
C_LEVEL_STATEMENT);
update_linkid_detail( l_transfer_run_id,
p_request_id,
l_start_date,
l_end_date);
gl_insert_detail( p_request_id,
l_user_source_name,
l_transfer_run_id,
l_period_name,
l_start_date,
l_end_date,
l_next_period,
l_reversal_date,
p_sob_list(i).average_balances_flag,
p_gl_transfer_mode,
l_group_id,
p_batch_desc,
p_je_desc,
p_je_line_desc);
gl_insert_summary( p_request_id,
l_user_source_name,
l_transfer_run_id,
l_period_name,
l_start_date,
l_end_date,
l_next_period,
l_reversal_date,
p_sob_list(i).average_balances_flag,
p_gl_transfer_mode,
l_group_id,
p_batch_desc,
p_je_desc,
p_je_line_desc);
xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
'',l_log_module,
C_LEVEL_STATEMENT);
update_linkid_summary( p_request_id,
p_gl_transfer_mode,
l_transfer_run_id,
l_start_date,
l_end_date);
xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
'',l_log_module,
C_LEVEL_STATEMENT);
update_linkid_detail( l_transfer_run_id,
p_request_id,
l_start_date,
l_end_date);
gl_insert_detail( p_request_id,
l_user_source_name,
l_transfer_run_id,
l_period_name,
l_start_date,
l_end_date,
l_next_period,
l_reversal_date,
p_sob_list(i).average_balances_flag,
p_gl_transfer_mode,
l_group_id,
p_batch_desc,
p_je_desc,
p_je_line_desc);
p_selection_type,
p_batch_name,
l_start_date,
l_end_date,
p_gl_transfer_mode,
p_process_days,
p_debug_flag
);
IF ( p_selection_type = 1 AND
l_end_date < Least(p_end_date,l_period_end_date)) THEN
l_start_date := l_end_date+1;
select_acct_headers( p_selection_type,
l_set_of_books_id,
p_source_doc_id,
p_source_document_table,
l_transfer_run_id,
p_request_id,
p_journal_category,
l_start_date,
l_end_date,
p_sob_list(i).legal_entity_id,
p_sob_list(i).cost_group_id,
p_sob_list(i).cost_type_id,
p_validate_account
);
validate_periods(p_selection_type,
p_sob_list,
p_program_name,
l_start_date,
l_end_date
);
validate_acct_lines( p_selection_type,
l_set_of_books_id,
l_coa_id,
l_transfer_run_id,
l_period_start_date,
l_period_end_date);
validate_acct_headers( p_selection_type,
l_set_of_books_id,
l_transfer_run_id,
l_period_start_date,
l_period_end_date);
xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
'',l_log_module,
C_LEVEL_STATEMENT);
update_linkid_detail( l_transfer_run_id,
p_request_id,
l_period_start_date,
l_period_end_date);
gl_insert_detail( p_request_id,
l_user_source_name,
l_transfer_run_id,
l_period_name,
l_period_start_date,
l_period_end_date,
l_next_period,
l_reversal_date,
p_sob_list(i).average_balances_flag,
p_gl_transfer_mode,
l_group_id,
p_batch_desc,
p_je_desc,
p_je_line_desc);
gl_insert_summary( p_request_id,
l_user_source_name,
l_transfer_run_id,
l_period_name,
l_period_start_date,
l_period_end_date,
l_next_period,
l_reversal_date,
p_sob_list(i).average_balances_flag,
p_gl_transfer_mode,
l_group_id,
p_batch_desc,
p_je_desc,
p_je_line_desc);
xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
'',l_log_module,
C_LEVEL_STATEMENT);
update_linkid_summary( p_request_id,
p_gl_transfer_mode,
l_transfer_run_id,
l_period_start_date,
l_period_end_date);
xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
'',l_log_module,
C_LEVEL_STATEMENT);
update_linkid_detail( l_transfer_run_id,
p_request_id,
l_start_date,
l_end_date);
gl_insert_detail( p_request_id,
l_user_source_name,
l_transfer_run_id,
l_period_name,
l_period_start_date,
l_period_end_date,
l_next_period,
l_reversal_date,
p_sob_list(i).average_balances_flag,
p_gl_transfer_mode,
l_group_id,
p_batch_desc,
p_je_desc,
p_je_line_desc);
p_selection_type,
p_batch_name,
l_period_start_date,
l_period_end_date,
p_gl_transfer_mode,
p_process_days,
p_debug_flag
);
END IF; -- Selection Type
xla_message('XLA_GLT_INSERT_GIC','STATUS','S','','','','',l_log_module,
C_LEVEL_STATEMENT);
INSERT INTO gl_interface_control
( JE_SOURCE_NAME,
STATUS,
INTERFACE_RUN_ID,
GROUP_ID,
SET_OF_BOOKS_ID,
PACKET_ID
)
VALUES
(
l_source_name,
'S',
l_interface_run_id,
l_group_id,
l_set_of_books_id,
''
);
UPDATE xla_gl_transfer_batches_all
SET gllezl_request_id = l_submittedreqid,
transfer_status = Decode(g_sob_rows_created,0,'N','C'),
packet_id = l_packet_id
WHERE gl_transfer_run_id = l_transfer_run_id;