[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 01-Mar-2005 Shishir Joshi Inserting -1 value for set of books when |
| inserting rows into the |
| gl_interface_confrol table per Deborah's |
| recommendation. JI is modified to |
| support intercompany functionality. |
| 15-Apr-2005 Swapna Vellani Added mutl-table Journal Import. |
| 04-Aug-2005 Wynne Chan Bug 4458381 - Public Sector Enhancements |
| 07-Oct-2005 Shishir Joshi Trial Balance chnages. Bug 4630945 |
| 30-Nov-2005 Vinay Kumar Bug4769315 Added filter on application_id|
| 13-Jan-2005 Vinay Kumar Modified the logic to pick JE to transfer|
| and signature of gl_transfer_main |
| Bug 4945075 Acoid creating n1_index and |
| n2_index on GL INTERFACE Table |
| 03-Mar-2006 Vinay Kumar Bug 5041325 Removed the procedure |
| update_gl_sl_link |
| 09-Mar-2006 S. Singhania Bug 5056632. |
| - Modified validate_input_parameters. |
| - Added paramter p_caller to |
| gl_tranfer_main |
| - Modified select_journal_entries. |
| - Modified logic to get group_ids |
| 02-Jun-2006 Vinay Kumar Bug 5254655 Fix for Standalone Transfer |
| to GL |
| 22-Aug-2006 Ejaz Sayyed Bug#5437400 - update gl_transfer_date in |
| set_transfer_status procedure and |
| in select_journal_entries procedure, |
| set trnsfr status code 'S'for combined mod|
| and remove parameter p_ledger_id |
| 22-Aug-2006 V. Swapna Bug 5438564. Comment out the call to |
| validate_accounting_periods to handle |
| a performance issue. |
| 4-Sep-2008 rajose bug#7320079 To pass the je_source_name |
| while spawning data manager. This helps |
| in finding the application from |
| which the data manager has been spawned. |
+===========================================================================*/
-- Constants
C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
| INSERT ROWS INTO THE GL_INTERFACE_CONTROL |
| |
=====================================================================*/
PROCEDURE insert_interface_control(p_ledger_id NUMBER
,p_table_name VARCHAR2) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_interface_control';
trace('insert_interface_control.Begin',C_LEVEL_PROCEDURE,l_Log_module);
INSERT INTO gl_interface_control
(
je_source_name,
status,
interface_run_id,
group_id,
set_of_books_id,
packet_id,
interface_table_name,
processed_table_code
)
VALUES
(
g_je_source_name,
'S',
g_interface_run_id,
g_group_id,
-1,
Decode(g_budgetary_control_flag, 'N', NULL, -3),
p_table_name,
'S' --7512923 Save rows in interface control table
);
trace(SQL%ROWCOUNT|| ' rows inserted into the interface control table' ,C_LEVEL_STATEMENT,l_Log_module);
trace('insert_interface_control.End',C_LEVEL_PROCEDURE,l_Log_module);
trace('Insert into the GL_INTERFACE_CONTROL failed',C_LEVEL_UNEXPECTED,l_Log_module);
END insert_interface_control;
PROCEDURE insert_transfer_log ( p_ledger_id NUMBER) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_transfer_log';
trace('insert_transfer_log.Begin',C_LEVEL_PROCEDURE,l_Log_module);
trace('Inserting a row into the transfer to GL log table.',C_LEVEL_STATEMENT,l_Log_module);
INSERT INTO xla_transfer_logs
(
application_id
,ledger_id
,parent_group_id
,group_id
,transfer_status_code
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_UPDATE_DATE
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,REQUEST_ID
)
VALUES
(
g_application_id
,p_ledger_id
,g_parent_group_id
,g_group_id
,'INCOMPLETE' -- Incomplete
,SYSDATE
,g_user_id
,SYSDATE
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,SYSDATE
,xla_environment_pkg.g_prog_appl_id
,xla_environment_pkg.g_prog_id
,xla_environment_pkg.g_Req_Id
);
trace('insert_transfer_log.End',C_LEVEL_PROCEDURE,l_Log_module);
(p_location => 'xla_transfer_pkg.insert_transfer_log');
END insert_transfer_log;
UPDATE xla_transfer_logs
SET gllezl_request_id = l_gllezl_request_id
WHERE group_id = g_group_id;
SELECT ledger_id
,NAME
,ledger_category_code
FROM xla_ledger_relationships_v xlr
WHERE xlr.primary_ledger_id = p_ledger_id
AND xlr.relationship_enabled_flag = 'Y'
AND EXISTS (SELECT 1
FROM xla_ledger_options xlo
WHERE application_id = p_application_id
AND DECODE(xlr.ledger_category_code
,'ALC',xlr.ledger_id
,xlo.ledger_id) = xlr.ledger_id
AND DECODE(xlr.ledger_category_code
,'SECONDARY',xlo.capture_event_flag
,'N') = 'N'
AND DECODE(xlr.ledger_category_code
,'ALC','Y'
,xlo.enabled_flag) = 'Y')
ORDER BY DECODE(xlr.ledger_category_code,
'PRIMARY',1,
'ALC',2
,3);
SELECT NAME
,ledger_category_code
,enable_budgetary_control_flag
INTO l_ledger_name
,l_ledger_category_code
,g_budgetary_control_flag
FROM gl_ledgers led
WHERE led.ledger_id = p_ledger_id;
trace('Total number of ledgers selected = '|| g_all_ledgers_tab.COUNT,C_LEVEL_PROCEDURE,l_Log_module);
SELECT xlo.transfer_to_gl_mode_code
INTO g_transfer_summary_mode
FROM xla_ledger_options xlo
WHERE xlo.application_id = g_application_id
AND xlo.ledger_id = p_ledger_id;
,p_value_1 => 'The ledger setup is not complete. Please run Update Subledger Accounting Options program for your application '||
'ledger_id = '||p_ledger_id||
' application_id = '|| g_application_id
,p_token_2 => 'LOCATION'
,p_value_2 => 'xla_events_pkg.get_ledger_options');
SELECT access_set_id
INTO l_access_set_id
FROM gl_access_sets aset, gl_ledgers led
WHERE aset.chart_of_accounts_id = led.chart_of_accounts_id
AND led.ledger_id = p_ledger_id
AND aset.access_set_id IN (g_access_set_id, g_sec_access_set_id)
AND ROWNUM = 1;
SELECT implicit_access_set_id
INTO l_access_set_id
FROM gl_ledgers led
WHERE led.ledger_id = p_ledger_id;
SELECT group_id
,gllezl_request_id
BULK COLLECT INTO
g_group_id_tab
,g_gllezl_requests_tab
FROM xla_transfer_logs xtb1
WHERE application_id = g_application_id
AND request_id NOT IN
( SELECT xtb.request_id
FROM xla_transfer_logs xtb
,fnd_concurrent_requests fcr
WHERE xtb.application_id = g_application_id
AND xtb.transfer_status_code = 'INCOMPLETE'
--AND xtb.gllezl_request_id IS NOT NULL
AND xtb.request_id = fcr.request_id
AND fcr.phase_code IN ('R','P','I'));
UPDATE xla_ae_headers
SET group_id = NULL
,gl_transfer_status_code = 'N'
,gl_transfer_date = NULL
,program_update_date = SYSDATE
,program_id = g_program_id
,request_id = g_request_id
WHERE group_id = g_group_id_tab(i);
trace(SQL%ROWCOUNT || ' Headers updated.',C_LEVEL_STATEMENT,l_log_module);
DELETE xla_transfer_logs
WHERE group_id = g_group_id_tab(i);
DELETE xla_transfer_ledgers
WHERE group_id = g_group_id_tab(i);
trace(SQL%ROWCOUNT || 'Rows deleted from the XLA_TRANSFER_LEDGERS',C_LEVEL_STATEMENT,l_log_module);
trace('Calling gl_journal_import_sla_pkg.delete_batches',C_LEVEL_EVENT,l_log_module);
gl_journal_import_sla_pkg.delete_batches
(x_je_source_name => g_je_source_name
,x_group_id => g_primary_ledgers_tab(i).group_id
);
trace('Number of ledgers selected for a period validation = ' ||g_all_ledgers_tab.count,C_LEVEL_STATEMENT,l_log_module);
SELECT actual_flag,budget_flag,encumbrance_flag
INTO l_actual_flag, l_budget_flag, l_encum_flag
FROM (SELECT MAX(DECODE(NVL(ALLOW_ACTUALS_FLAG,'N'),'Y','Y','Z')) actual_flag
,MAX(DECODE(NVL(ALLOW_BUDGETS_FLAG,'N'),'Y','Y','Z')) budget_flag
,MAX(DECODE(NVL(ALLOW_encumbrance_FLAG,'N'),'Y','Y','Z')) encumbrance_flag
FROM xla_event_class_attrs
WHERE application_id = g_application_id
GROUP BY allow_actuals_flag, allow_budgets_flag, allow_encumbrance_flag
ORDER BY actual_flag,budget_flag,encumbrance_flag)
WHERE ROWNUM = 1;
'SELECT DISTINCT aeh.period_name
,aeh.ledger_id
FROM xla_ae_headers aeh
,gl_period_statuses gps
,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
WHERE aeh.application_id = :2 --g_application_id
AND aeh.ledger_id = led.column_value
AND aeh.entity_id = :3 --g_entity_id
AND aeh.gl_transfer_status_code = ''N''
AND aeh.accounting_entry_status_code = ''F''
AND aeh.balance_type_code = ''A''
AND gps.application_id = 101
AND gps.ledger_id = aeh.ledger_id
AND gps.period_name = aeh.period_name
AND NVL(gps.adjustment_period_flag,''N'') = ''N''
AND gps.closing_status IN (''C'',''N'',''P'')';
SELECT DISTINCT gps.period_year
,gbv.budget_name
FROM xla_ae_headers aeh
,gl_period_statuses gps
,gl_budget_period_ranges gbp
,gl_budget_versions gbv
,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
WHERE aeh.application_id = :2
AND aeh.ledger_id = led.column_value
AND aeh.balance_type_code = ''B''
AND aeh.entity_id = :3 --g_entity_id
AND aeh.gl_transfer_status_code = ''N''
AND aeh.accounting_entry_status_code = ''F''
AND gps.application_id = 101
AND gps.ledger_id = aeh.ledger_id
AND gps.period_name = aeh.period_name
AND NVL(gps.adjustment_period_flag,''N'') = ''N''
AND gps.period_year = gbp.period_year
AND aeh.budget_version_id = gbp.budget_version_id
AND gbp.open_flag <> ''O''
AND gbv.budget_version_id = aeh.budget_version_id ';
' SELECT DISTINCT aeh.ledger_id
,gll.latest_encumbrance_year
FROM xla_ae_headers aeh
,gl_period_statuses gps
,gl_ledgers gll
,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
WHERE aeh.application_id = :2 --g_application_id
AND aeh.entity_id = :3 --g_entity_id
AND aeh.ledger_id = led.column_value
AND aeh.balance_type_code = ''E''
AND aeh.ledger_id = gll.ledger_id
AND aeh.gl_transfer_status_code = ''N''
AND aeh.accounting_entry_status_code = ''F''
AND gps.application_id = 101
AND gps.ledger_id = aeh.ledger_id
AND gps.period_name = aeh.period_name
AND gps.period_year > gll.latest_encumbrance_year ';
'SELECT DISTINCT aeh.period_name
,aeh.ledger_id
FROM xla_ae_headers aeh
,gl_period_statuses gps
,xla_transaction_entities xte
,xla_event_types_b xet
,xla_event_class_attrs xec
,xla_ledger_relationships_v xlr
,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
WHERE xte.entity_id = aeh.entity_id
AND aeh.application_id = :2 --g_application_id
AND aeh.ledger_id = led.column_value
AND aeh.accounting_date <= :3 --g_end_date
AND aeh.balance_type_code = ''A''
AND aeh.ledger_id = xlr.ledger_id
AND xte.entity_code = xec.entity_code
AND xte.application_id = xec.application_id
AND xec.application_id = xet.application_id
AND xec.entity_code = xet.entity_code
AND xec.event_class_code = xet.event_class_code
AND xec.event_class_group_code
= NVL(:4,xec.event_class_group_code)
AND xet.event_type_code = aeh.event_type_code
AND xet.application_id = aeh.application_id
AND xet.entity_code = xte.entity_code
AND aeh.gl_transfer_status_code = ''N''
AND aeh.accounting_entry_status_code = ''F''
AND gps.application_id = 101
AND gps.ledger_id = aeh.ledger_id
AND gps.period_name = aeh.period_name
AND NVL(gps.adjustment_period_flag,''N'') = ''N''
AND gps.closing_status IN (''C'',''N'',''P'')'
|| g_transaction_security;
SELECT DISTINCT gps.period_year
,gbv.budget_name
FROM xla_ae_headers aeh
,xla_transaction_entities xte
,xla_event_types_b xet
,xla_event_class_attrs xec
,gl_period_statuses gps
,gl_budget_period_ranges gbp
,gl_budget_versions gbv
,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
WHERE xte.entity_id = aeh.entity_id
AND aeh.application_id = :2
AND aeh.ledger_id = led.column_value
AND aeh.accounting_date <= :3 --g_end_date
AND aeh.balance_type_code = ''B''
AND xte.entity_code = xec.entity_code
AND xte.application_id = xec.application_id
AND xec.application_id = xet.application_id
AND xec.entity_code = xet.entity_code
AND xec.event_class_code = xet.event_class_code
AND xec.event_class_group_code
= NVL(:4,xec.event_class_group_code)
AND xet.event_type_code = aeh.event_type_code
AND xet.application_id = aeh.application_id
AND xet.entity_code = xte.entity_code
AND aeh.gl_transfer_status_code = ''N''
AND aeh.accounting_entry_status_code = ''F''
AND gps.application_id = 101
AND gps.ledger_id = aeh.ledger_id
AND gps.period_name = aeh.period_name
AND NVL(gps.adjustment_period_flag,''N'') = ''N''
AND gps.period_year = gbp.period_year
AND aeh.budget_version_id = gbp.budget_version_id
AND gbp.open_flag <> ''O''
AND gbv.budget_version_id = aeh.budget_version_id '
|| g_transaction_security;
' SELECT DISTINCT aeh.ledger_id
,gll.latest_encumbrance_year
FROM xla_ae_headers aeh
,gl_period_statuses gps
,xla_transaction_entities xte
,xla_event_types_b xet
,xla_event_class_attrs xec
,gl_ledgers gll
,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
WHERE xte.entity_id = aeh.entity_id
AND aeh.application_id = :2 --g_application_id
AND aeh.ledger_id = led.column_value
AND aeh.accounting_date <= :3 --g_end_date
AND aeh.balance_type_code = ''E''
AND aeh.ledger_id = gll.ledger_id
AND xte.entity_code = xec.entity_code
AND xte.application_id = xec.application_id
AND xec.application_id = xet.application_id
AND xec.entity_code = xet.entity_code
AND xec.event_class_code = xet.event_class_code
AND xec.event_class_group_code = NVL(:4,xec.event_class_group_code)
AND xet.event_type_code = aeh.event_type_code
AND xet.application_id = aeh.application_id
AND xet.entity_code = xte.entity_code
AND aeh.gl_transfer_status_code = ''N''
AND aeh.accounting_entry_status_code = ''F''
AND gps.application_id = 101
AND gps.ledger_id = aeh.ledger_id
AND gps.period_name = aeh.period_name
AND gps.period_year > gll.latest_encumbrance_year '
|| g_transaction_security;
THE PROCEDURE selects AND marks THE journal entries
*====================================================================*/
-- removed parameter p_ledger_id
PROCEDURE select_journal_entries IS
l_statement VARCHAR2(4000);
l_log_module := C_DEFAULT_MODULE||'.select_journal_entries';
trace('select_journal_entries.Begin',C_LEVEL_PROCEDURE,l_log_module);
trace('Number of ledgers selected = ' || g_ledger_id_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
UPDATE /*+ index(XLA_AE_HEADERS,XLA_AE_HEADERS_N1) */
xla_ae_headers
SET gl_transfer_date = sysdate,
gl_transfer_status_code = 'S'
WHERE application_id = g_application_id
AND ledger_id = g_ledger_id_tab(i)
AND group_id = g_group_id
AND gl_transfer_status_code = 'N'
AND accounting_entry_status_code = 'F';
trace('Selecting journal entris for the document ' || g_entity_id,C_LEVEL_STATEMENT,l_log_module);
UPDATE xla_ae_headers aeh
SET program_update_date = SYSDATE,
program_id = g_program_id,
request_id = g_request_id,
group_id = g_group_id,
gl_transfer_date = sysdate,
gl_transfer_status_code = 'S'
WHERE application_id = g_application_id
AND ledger_id = g_ledger_id_tab(i)
AND gl_transfer_status_code = 'N'
AND entity_id = g_entity_id
AND accounting_entry_status_code = 'F';
trace('Selecting journal entries for the accounting batch id ' || g_accounting_batch_id,C_LEVEL_STATEMENT,l_log_module);
UPDATE xla_ae_headers aeh
SET program_update_date = SYSDATE,
program_id = g_program_id,
group_id = g_group_id,
gl_transfer_date = SYSDATE,
gl_transfer_status_code = 'S'
WHERE application_id = g_application_id
AND ledger_id = g_ledger_id_tab(i)
AND gl_transfer_status_code = 'N'
AND accounting_batch_id = g_accounting_batch_id
AND accounting_entry_status_code = 'F';
UPDATE xla_ae_headers aeh
SET program_update_date = SYSDATE,
program_id = g_program_id,
request_id = g_request_id,
gl_transfer_date = sysdate,
gl_transfer_status_code = 'S',
group_id = g_group_id
WHERE application_id = g_application_id
AND ledger_id = g_ledger_id_tab(i)
AND entity_id = g_entity_id
AND gl_transfer_status_code = 'N'
AND accounting_entry_status_code = 'F'
;
' UPDATE
(SELECT /*+ leading(aeh,xet,xte) use_hash(xet,xec,xeca) use_nl(xte)
swap_join_inputs(xet) swap_join_inputs(xec) swap_join_inputs(xeca) */
aeh.program_update_date -- added hint per performance change 7259699
,aeh.program_id
,aeh.request_id
,aeh.gl_transfer_date
,aeh.gl_transfer_status_code
,aeh.group_id
FROM xla_ae_headers aeh
,xla_transaction_entities xte
,xla_event_types_b xet
,xla_event_class_attrs xeca
,xla_event_classes_b xec
WHERE xte.entity_id = aeh.entity_id
AND xte.application_id = :1 --g_application_id
AND aeh.application_id = xte.application_id
AND aeh.ledger_id = :2 --g_ledger_id_tab(i)
AND aeh.accounting_date <= :3 --g_end_date
AND xte.entity_code = xec.entity_code
AND xeca.application_id = xec.application_id
AND xeca.event_class_code = xec.event_class_code
AND xeca.entity_code = xec.entity_code
AND xeca.event_class_group_code = Nvl(:4,xeca.event_class_group_code)
AND xec.event_class_code = xet.event_class_code
AND xet.event_type_code = aeh.event_type_code
AND xet.application_id = aeh.application_id
AND xec.application_id = xet.application_id
AND xet.event_class_code = xec.event_class_code
AND aeh.gl_transfer_status_code = ''N''
AND aeh.accounting_entry_status_code = ''F''
'
|| g_transaction_security
|| ' ) SET program_update_date = SYSDATE
,program_id = :5 --g_program_id
,request_id = :6 --g_request_id
,gl_transfer_date = Sysdate
,group_id = :7 --g_group_id
,gl_transfer_status_code = ''S''';
trace('Total journal entries selected = ' || l_je_count,C_LEVEL_STATEMENT,l_log_module);
trace('select_journal_entries.End',C_LEVEL_PROCEDURE,l_log_module);
(p_location => 'xla_transfer_pkg.select_journal_entries');
END select_journal_entries;
SELECT js.je_source_name
,user_je_source_name
INTO g_je_source_name
,g_user_source_name
FROM gl_je_sources js
,xla_subledgers xsl
WHERE xsl.application_id = g_application_id
AND js.je_source_name = xsl.je_source_name;
gl_interface_insert
DESCRIPTION
Inserts ROWS INTO THE GL_ITERFACE TABLE
SCOPE - PRIVATE
EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
ARGUMENTS
NOTES
+===========================================================================*/
PROCEDURE insert_gl_interface IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_gl_interface';
trace('gl_interface_insert.Begin',C_LEVEL_PROCEDURE,l_log_module);
l_statement := 'INSERT INTO '||g_gl_interface_table_name||
'(
status, ledger_id
,user_je_source_name, user_je_category_name
,accounting_date
,currency_code
,date_created, created_by
,actual_flag
,budget_version_id
,encumbrance_type_id
,code_combination_id, stat_amount
,entered_dr
,entered_cr
,accounted_dr
,accounted_cr
,reference1
,reference4
,reference5
,reference10
,reference11
,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, jgzz_recon_ref
,reference_date
,funds_reserved_flag
,reference25
,reference26
,reference27
,reference28
,reference29
,reference30
)
SELECT /*+ ordered index(aeh,xla_ae_headers_n1) use_nl(jc,led,ael,gps) */
''NEW'', aeh.ledger_id
,:1 , jc.user_je_category_name
,DECODE(:2, ''P'' , gps.end_date , aeh.accounting_date)
,DECODE(aeh.balance_type_code , ''E'' , led.currency_code , ael.currency_code)
,SYSDATE, :3
,aeh.balance_type_code
,aeh.budget_version_id
,ael.encumbrance_type_id -- 4458381
,ael.code_combination_id, ael.statistical_amount
,DECODE(aeh.balance_type_code, ''E'', ael.accounted_dr, ael.entered_dr) -- 4458381
,DECODE(aeh.balance_type_code, ''E'', ael.accounted_cr, ael.entered_cr) -- 4458381
,accounted_dr
,accounted_cr
,:4 -- Reference1
,DECODE(reference_date , NULL , NULL
,TO_CHAR(reference_date,''DD-MON-YYYY''))||
DECODE(:5 , ''A'' , TO_CHAR(aeh.accounting_date ,''DD-MON-YYYY'')
,''P'' ,aeh.period_name
,''D'' ,aeh.ae_header_id) --Reference4
,DECODE(:6,''D'',substrb(aeh.description,1,240),null)
,DECODE(DECODE(:7,''D'',''D'',''S'')||ael.gl_transfer_mode_code
,''SS'',null,substrb(ael.description,1,240))
,DECODE(:8||ael.gl_transfer_mode_code,
''AS'',jgzz_recon_ref,
''PS'',jgzz_recon_ref,
aeh.ae_header_id||''-''||ael.ae_line_num) -- Reference11
,aeh.doc_sequence_id
,aeh.doc_sequence_value
,ael.gl_sl_link_table
,ael.gl_sl_link_id
,:9
,ael.ussgl_transaction_code
,aeh.ae_header_id, :10
,aeh.period_name, ael.jgzz_recon_ref
,aeh.reference_date
,decode(led.enable_budgetary_control_flag
,''Y'',
decode(aeh.funds_status_code, ''A'', ''Y'', ''S'', ''Y'', ''P'', ''Y'', NULL)
,''Y'')
,aeh.entity_id
,aeh.event_id
,ael.ae_header_id
,ael.ae_line_num
,ael.accounted_dr
,ael.accounted_cr
FROM xla_ae_headers aeh
,xla_ae_lines ael
,gl_je_categories jc
,gl_period_statuses gps
,gl_ledgers led
WHERE ael.application_id = aeh.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND aeh.group_id = :11
AND aeh.application_id = :12 --4769315
AND aeh.je_category_name = jc.je_category_name
AND gps.application_id = 101
AND gps.ledger_id = aeh.ledger_id
AND led.ledger_id = gps.ledger_id
AND aeh.period_name = gps.period_name
AND aeh.gl_transfer_status_code = ''S''';
trace(SQL%ROWCOUNT || ' rows are inserted into the GL_INTERFACE table',C_LEVEL_STATEMENT,l_log_module);
trace(SQL%ROWCOUNT || ' rows are inserted into the GL_INTERFACE table',C_LEVEL_STATEMENT,l_log_module);
trace('gl_interface_insert.End',C_LEVEL_PROCEDURE,l_log_module);
(p_location => 'xla_transfer_pkg.gl_interface_insert');
END insert_gl_interface;
trace('selecting from gl interface '|| g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
'select status from ' ||g_gl_interface_table_name||
' where user_je_source_name= :1
and group_id = :2
and request_id = :4
and status like ''E%''
and rownum=1 ' into l_gl_status
using g_user_source_name,g_primary_ledgers_tab(i).group_id, g_primary_ledgers_tab(i).gllezl_request_id;
| Updates the transfer to GL status to yes to indicate that journal entries|
| have been transferred successfully. |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| |
| NOTES |
| |
+===========================================================================*/
PROCEDURE set_transfer_status IS
l_log_module VARCHAR2(240);
UPDATE /*+ index(XLA_AE_HEADERS,XLA_AE_HEADERS_N1) */
xla_ae_headers
SET gl_transfer_status_code = 'Y',
gl_transfer_date = sysdate -- bug#5437400
WHERE group_id = g_group_id_tab(i)
AND application_id = g_application_id; --4769315
| delete_transfer_log |
| |
| DESCRIPTION |
| Deletes the transfer to GL log. |
| |
| SCOPE - PRIVATE |
| |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| |
| NOTES |
| |
+===========================================================================*/
PROCEDURE delete_transfer_log IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_transfer_log';
trace('delete_transfer_log.Begin',C_LEVEL_PROCEDURE,l_log_module);
DELETE xla_transfer_logs
WHERE group_id = g_group_id_tab(i);
trace('delete_transfer_log.End',C_LEVEL_PROCEDURE,l_log_module);
(p_location => 'xla_transfer_pkg.delete_transfer_log');
END delete_transfer_log;
| insert_secondary_ledgers |
| |
| DESCRIPTION |
| Keeps track of journal entries transferred for secondary ledgers |
| |
| SCOPE - PRIVATE |
| |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| p_secondary_ledger_id -- Secondary ledger identifier | |
| NOTES |
| |
+===========================================================================*/
PROCEDURE insert_secondary_ledgers ( p_secondary_ledger_id IN NUMBER ) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_secondary_ledgers';
trace('insert_secondary_ledgers.Begin',C_LEVEL_PROCEDURE,l_log_module);
trace('insert_secondary_ledgers.End',C_LEVEL_PROCEDURE,l_log_module);
INSERT INTO xla_transfer_ledgers
(GROUP_ID
,SECONDARY_LEDGER_ID
,PRIMARY_LEDGER_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_UPDATE_DATE
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,REQUEST_ID
)
VALUES
(g_group_id
,p_secondary_ledger_id
,g_primary_ledger_id
,SYSDATE
,g_user_id
,SYSDATE
,xla_environment_pkg.g_usr_id
,xla_environment_pkg.g_login_id
,SYSDATE
,xla_environment_pkg.g_prog_appl_id
,xla_environment_pkg.g_prog_id
,xla_environment_pkg.g_Req_Id
);
(p_location => 'xla_transfer_pkg.insert_secondary_ledgers');
END insert_secondary_ledgers;
| The procedure performs the finishing tasks after inserting journal |
| entries into the GL interface table. |
| |
| |
| SCOPE - PRIVATE |
| |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| p_submit_gl_post Submit GL post |
| |
| NOTES |
| |
+===========================================================================*/
PROCEDURE complete_batch(p_submit_gl_post VARCHAR2) IS
l_req_id NUMBER;
trace('Inserting a row into the XLA_TRANSFER_LEDGERS table.',C_LEVEL_STATEMENT,l_log_module);
insert_secondary_ledgers
(p_secondary_ledger_id => g_primary_ledgers_tab(i).ledger_id
);
delete_transfer_log;
SELECT COUNT(1)
INTO l_cnt
FROM xla_tb_definitions_b xtd
,xla_tb_defn_je_sources xjs
WHERE xtd.definition_code = xjs.definition_code
AND xjs.je_source_name = p_je_source_name
and xtd.ledger_id = p_ledger_id;
g_group_id_tab.DELETE;
g_ledger_id_tab.DELETE;
SELECT gl_journal_import_s.NEXTVAL
INTO g_primary_ledgers_tab(i).interface_run_id
FROM dual;
SELECT gl_journal_import_s.NEXTVAL
,gl_interface_control_s.NEXTVAL
INTO g_primary_ledgers_tab(i).interface_run_id
,g_primary_ledgers_tab(i).group_id
FROM dual;
trace('Ledgers selected for the processing',C_LEVEL_STATEMENT,l_log_module);
trace('Inserting an entry into the audit table',C_LEVEL_STATEMENT,l_log_module);
select_journal_entries;
insert_transfer_log(g_primary_ledgers_tab(i).ledger_id);
insert_gl_interface;
insert_interface_control
(p_ledger_id => g_primary_ledgers_tab(i).ledger_id
,p_table_name => g_gl_interface_table_name
);
delete_transfer_log;
UPDATE xla_ae_headers
SET group_id = NULL
,gl_transfer_status_code = 'N'
,gl_transfer_date = NULL
,program_update_date = SYSDATE
,program_id = g_program_id
,request_id = g_request_id
WHERE group_id = g_primary_ledgers_tab(i).group_id;
delete_transfer_log;