[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. |
| 12-Aug-2009 rajose bug#8691650 Phase 2 |
| 01-Sep-2010 VGOPISET 10047096 Perf changes in the UPDATE of |
| XLA_AE_HEADERS in Select_Journal_Entries |
| 26-NOV-2010 Narayanan M.S. Bug#10124492 Headers with gl_transfer_sta|
| tus_code as 'NT' will not be reset to 'N'|
| or 'Y' |
| Bug#9839301 Modified procedure |
| get_ledger_options to use table |
| gl_access_set_assignments instead of |
| gl_access_sets to derive the access_set_id|
| 11-Mar-2011 Narayanan M.S. Bug 11855000 Funds check Enhancement |
| 20-May-2011 VGOPISET Bug12542507 changes for failure of this |
| GL Transfer post 12.2 edition changes |
| 28-SEP-2011 Narayanan M.S. Bug 12965313. Performance fix for GL. |
| Passing value 'TRUE' for the paramter |
| 'create_n3_index' when creating temporary|
| interface table. |
+===========================================================================*/
-- Constants
C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
This function inserts data in gl_bc_packets and on success
returns a positive packet_id.
*/----------------------------------------------------------------------
FUNCTION reserve_funds
(p_group_id number,
p_application_id number)
RETURN NUMBER IS
CURSOR c_get_arrival_seq IS
SELECT gl_bc_packet_arrival_order_s.nextval
FROM dual;
SELECT gl_bc_packets_s.NEXTVAL
FROM dual;
SELECT enable_budgetary_control_flag, ledger_category_code
INTO g_budgetary_control_flag, l_ledger_category
FROM gl_ledgers led
WHERE led.ledger_id = g_ledger_id_tab(i);
insert into gl_bc_packets
(PACKET_ID
,APPLICATION_ID
,LEDGER_ID
,JE_SOURCE_NAME
,JE_CATEGORY_NAME
,CODE_COMBINATION_ID
,ACTUAL_FLAG
,PERIOD_NAME
,PERIOD_YEAR
,PERIOD_NUM
,QUARTER_NUM
,CURRENCY_CODE
,STATUS_CODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,ENCUMBRANCE_TYPE_ID
,BUDGET_VERSION_ID
,ENTERED_DR
,ENTERED_CR
,ACCOUNTED_DR
,ACCOUNTED_CR
,EVENT_ID
,AE_HEADER_ID
,AE_LINE_NUM
,SESSION_ID
,SERIAL_ID
,BC_DATE
,group_id
,entity_id
,effect_on_funds_code
)
SELECT
l_packet_id
, p_application_id
, xah.ledger_id
, xs.je_source_name
, xah.je_category_name
, xal.code_combination_id
, xah.balance_type_code
, xah.period_name
, gps.period_year
, gps.period_num
, gps.quarter_num
, xal.currency_code
, 'C'
, sysdate
, xla_environment_pkg.g_usr_id
, xal.encumbrance_type_id
, xah.budget_version_id
, xal.entered_dr
, xal.entered_cr
, xal.accounted_dr
, xal.accounted_cr
, xah.event_id
, xal.ae_header_id
, xal.ae_line_num
, ses.sid
, ses.serial#
, xah.accounting_date
, xah.group_id
, xah.entity_id
, Case
When xal.accounted_dr is not null Then 'D'
Else 'I'
END
FROM xla_ae_headers xah
, xla_ae_lines xal
, xla_subledgers xs
, gl_period_statuses gps
, v$session ses
WHERE xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xs.application_id = xah.application_id
AND gps.application_id = 101
AND gps.ledger_id = xah.ledger_id
AND gps.period_name = xah.period_name
AND ses.audsid = userenv('SESSIONID')
and xah.group_id = p_group_id
and xah.gl_transfer_status_code = 'S'
and xah.application_id = p_application_id
and xah.ledger_id = g_ledger_id_tab(i)
and xah.funds_status_code is null
and xah.balance_type_code in ('A', 'E')
and exists
(select 1 from xla_events xe
where xe.entity_id = xah.entity_id
and xe.application_id = xah.application_id
and xe.budgetary_control_flag = 'Y')
;
trace(p_msg => 'Num of rows inserted: '|| l_bc_count,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
--Insert data into gl_bc_packet_arrival_order
IF (NVL(l_bc_count,0) > 0) THEN
insert into gl_bc_packet_arrival_order
(
packet_id,
ledger_id,
arrival_seq,
affect_funds_flag,
last_update_date,
last_updated_by
)
values
(
l_packet_id,
g_ledger_id_tab(i),
l_arrival_seq,
'Y', -- FC mode will be 'C' for funds check, 'R' for reserve. For reserve, affect_funds_flag should be set to 'Y'
sysdate,
g_user_id
)
;
trace(p_msg => 'Num of rows inserted into gl_bc_packet_arrival_order table: '|| SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
-- update xla_ae_headers as funds reserved
g_reserve_flag := true;
update xla_ae_headers xah
set xah.funds_status_code = 'S'
where xah.ledger_id = g_ledger_id_tab(i)
and xah.application_id = p_application_id
and xah.group_id = p_group_id
and xah.gl_transfer_status_code = 'S'
and xah.funds_status_code is null
and xah.balance_type_code in ('A', 'E')
and exists
(
select 1 from gl_bc_packets glc
where glc.group_id = xah.group_id
and glc.ae_header_id = xah.ae_header_id
and glc.packet_id = l_packet_id
and glc.event_id = xah.event_id
and glc.application_id = xah.application_id
)
;
trace('No of headers updated as funds reserved = ' || SQL%ROWCOUNT
,C_LEVEL_STATEMENT
,l_log_module);
--update the funding budget version_id
UPDATE gl_bc_packets bp
SET bp.funding_budget_version_id =
(SELECT DECODE(pk.actual_flag, 'B', pk.budget_version_id, bo.funding_budget_version_id)
FROM gl_budget_assignments ba,
gl_budgets b ,
gl_budget_versions bv ,
gl_period_statuses ps ,
gl_bc_packets pk ,
gl_budorg_bc_options bo
WHERE ba.ledger_id(+) = g_ledger_id_tab(i)
AND ba.currency_code(+) = DECODE(PK.currency_code, 'STAT', 'STAT', pk.currency_code)
AND ba.code_combination_id (+) = PK.code_combination_id
AND bo.range_id(+) = ba.range_id
AND bo.funding_budget_version_id = bv.budget_version_id
AND bv.budget_name = b.budget_name
AND ((b.budget_type = 'payment'
AND pk.actual_flag IN ('P', 'F')
)
OR (b.budget_type = 'standard'
AND pk.actual_flag NOT IN ('P', 'F')
)
)
AND ps.application_id = 101
AND ps.ledger_id = g_ledger_id_tab(i)
AND ps.period_name = pk.period_name
AND ps.start_date >=
(SELECT p1.start_date
FROM gl_period_statuses p1
WHERE p1.period_name = b.first_valid_period_name
AND p1.application_id = ps.application_id
AND p1.ledger_id = ps.ledger_id
)
AND ps.end_date <=
(SELECT p2.end_date
FROM gl_period_statuses p2
WHERE p2.period_name = b.last_valid_period_name
AND p2.application_id = ps.application_id
AND p2.ledger_id = ps.ledger_id
)
AND pk.rowid = bp.rowid
)
WHERE bp.packet_id = l_packet_id
AND bp.template_id IS NULL
AND bp.funding_budget_version_id IS NULL
;
trace('No of rows updated in GL_BC_PACKETS with funding_budget_version_id = ' || SQL%ROWCOUNT
,C_LEVEL_STATEMENT
,l_log_module);
| 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' --8429053, 8691650 Commented
--decode(g_gl_interface_table_name, 'GL_INTERFACE',NULL,'S') --8429053
);
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 access_set_id
INTO l_access_set_id
FROM gl_access_set_assignments gasa
WHERE gasa.ledger_id = p_ledger_id
AND gasa.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 DECODE(g_application_id,707, ' AND NVL(xte.security_id_int_1,'|| g_security_id_int_1||') = ' ||g_security_id_int_1,
' AND xte.security_id_int_1 = '|| g_security_id_int_1)
into g_transaction_security from dual ;
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'));
-- Delete rows from gl_bc_packets and update gl_interface control with packet_id as -1
--
IF g_application_id = 200 THEN
FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
UPDATE xla_Ae_headers xah
set funds_status_code = null
where group_id = g_group_id_tab(i)
and funds_status_code = 'S'
and exists
(
select 1 from gl_bc_packets glc
where glc.group_id = xah.group_id
and glc.ae_header_id = xah.ae_header_id
and glc.event_id = xah.event_id
and glc.application_id = xah.application_id
and glc.status_code = 'C'
)
;
trace('No of headers updated as unreserved in recover_batch = '||SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
DELETE gl_bc_packet_arrival_order
where packet_id in
(select packet_id from gl_bc_packets
WHERE group_id = g_group_id_tab(i)
and status_code = 'C')
;
trace(SQL%ROWCOUNT || 'Rows deleted from the gl_bc_packet_arrival_order in recover_batch',C_LEVEL_STATEMENT,l_log_module);
DELETE gl_bc_packets
WHERE group_id = g_group_id_tab(i)
and status_code = 'C'
;
trace(SQL%ROWCOUNT || 'Rows deleted from the gl_bc_packets in recover_batch',C_LEVEL_STATEMENT,l_log_module);
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)
--Added for 10124492
AND gl_transfer_status_code <> 'NT';
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);
/* bug#8691650 use the g_group_id_tab to delete batches which needs to be recovered */
IF l_first_time_recover THEN
FOR i IN g_group_id_tab.FIRST .. g_group_id_tab.LAST
LOOP
IF (C_LEVEL_EVENT >= g_log_level) THEN
trace('First time recover 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_group_id_tab(i)
);
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(xah, XLA_AE_HEADERS_N1) */
xla_ae_headers xah
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'
-- added Bug#8691650
AND EXISTS
(
SELECT 1 FROM xla_ae_lines xal
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
)
AND EXISTS
(
-- added hint for perf bug#10047096
SELECT /*+ no_unnest */ 1 FROM xla_events xle
WHERE xah.event_id = xle.event_id
AND xah.application_id = xle.application_id
AND xle.event_status_code = 'P'
AND xle.process_status_code = 'P'
);
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'
-- added Bug#8691650
AND EXISTS
(
SELECT 1 FROM xla_ae_lines xal
WHERE aeh.ae_header_id = xal.ae_header_id
AND aeh.application_id = xal.application_id
)
AND EXISTS
(
SELECT 1 FROM xla_events xle
WHERE aeh.event_id = xle.event_id
AND aeh.application_id = xle.application_id
AND xle.event_status_code = 'P'
AND xle.process_status_code = 'P'
);
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'
-- added Bug#8691650
AND EXISTS
(
SELECT 1 FROM xla_ae_lines xal
WHERE aeh.ae_header_id = xal.ae_header_id
AND aeh.application_id = xal.application_id
)
AND EXISTS
(
SELECT 1 FROM xla_events xle
WHERE aeh.event_id = xle.event_id
AND aeh.application_id = xle.application_id
AND xle.event_status_code = 'P'
AND xle.process_status_code = 'P'
);
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'
-- added Bug#8691650
AND EXISTS
(
SELECT 1 FROM xla_ae_lines xal
WHERE aeh.ae_header_id = xal.ae_header_id
AND aeh.application_id = xal.application_id
)
AND EXISTS
(
SELECT 1 FROM xla_events xle
WHERE aeh.event_id = xle.event_id
AND aeh.application_id = xle.application_id
AND xle.event_status_code = 'P'
AND xle.process_status_code = 'P'
);
-- added extra conditions to make the below update and
-- gl_interface insert query in SYNC.
-- For bug12542507 commented the below hint
-- /*+ leading(aeh,xet,xte) use_hash(xet,xec,xeca) use_nl(xte)
-- swap_join_inputs(xet) swap_join_inputs(xec) swap_join_inputs(xeca) */
l_statement :=
' UPDATE
(SELECT /*+ leading(aeh) use_nl(xte) */ -- added for bug12542507
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 -- commented for bug12542507 and made it as IN clause
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.je_category_name in (select je_category_name from gl_je_categories) --8417930
AND EXISTS(select 1 from gl_period_statuses glp
where glp.application_id = 101
and glp.ledger_id = aeh.ledger_id
and glp.period_name = aeh.period_name) --8417930
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 */ --commented for bug12542507 and made it as IN clause as below
AND (aeh.application_id , aeh.event_type_code) IN ( SELECT xet.application_id , xet.event_type_code
FROM xla_event_types_b xet
,xla_event_class_attrs xeca
,xla_event_classes_b xec
WHERE 1 = 1
AND xeca.event_class_group_code = Nvl(:4,xeca.event_class_group_code)
AND xeca.application_id = :5
AND xeca.application_id = xec.application_id
AND xeca.event_class_code = xec.event_class_code
AND xeca.entity_code = xec.entity_code
AND xec.event_class_code = xet.event_class_code
AND xec.application_id = xet.application_id
)
AND aeh.gl_transfer_status_code = ''N''
AND aeh.accounting_entry_status_code = ''F''
AND EXISTS
(
SELECT 1 FROM xla_ae_lines xal
WHERE aeh.ae_header_id = xal.ae_header_id
AND aeh.application_id = xal.application_id
)
AND EXISTS
(
SELECT 1 FROM xla_events xle
WHERE aeh.event_id = xle.event_id
AND aeh.application_id = xle.application_id
AND xle.event_status_code = ''P''
AND xle.process_status_code = ''P''
)
'
|| g_transaction_security
|| ' ) SET program_update_date = SYSDATE
,program_id = :6 --g_program_id
,request_id = :7 --g_request_id
,gl_transfer_date = Sysdate
,group_id = :8 --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
,decode(js.import_using_key_flag,'Y',js.je_source_key
,js.user_je_source_name)
,js.import_using_key_flag
INTO g_je_source_name
,g_user_source_name
,g_import_key_flag
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 , decode(:2,''Y'',jc.je_category_key
,jc.user_je_category_name)
,DECODE(:3, ''P'' , gps.end_date , aeh.accounting_date)
,DECODE(aeh.balance_type_code , ''E'' , led.currency_code , ael.currency_code)
,SYSDATE, :4
,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
,:5 -- Reference1
,DECODE(reference_date , NULL , NULL
,TO_CHAR(reference_date,''DD-MON-YYYY''))||
DECODE(:6 , ''A'' , TO_CHAR(aeh.accounting_date ,''DD-MON-YYYY'')
,''P'' ,aeh.period_name
,''D'' ,aeh.ae_header_id
,''E'' ,TO_CHAR(aeh.accounting_date ,''DD-MON-YYYY'') -- added E/F lookup code for bug8681466
,''F'' ,aeh.period_name) --Reference4
,DECODE(:7,''D'',substrb(aeh.description,1,240),null)
,DECODE(DECODE(:8,''D'',''D'',''E'',''D'',''F'',''D'',''S'')||ael.gl_transfer_mode_code --added bug 8846459 to show line description
,''SS'',null,substrb(ael.description,1,240))
,DECODE(:9||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
,:10
,ael.ussgl_transaction_code
,aeh.ae_header_id, :11
,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 = :12
AND aeh.application_id = :13 --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''';
Following columns have been removed from the above Insert and Select
currency_conversion_date
,user_currency_conversion_type
,currency_conversion_rate
*/
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace('l_statement = ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
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(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 status <> ''PROCESSED'' AND status NOT LIKE ''W%''
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;
delete from gl_interface
where user_je_source_name = g_user_source_name
and group_id = g_primary_ledgers_tab(i).group_id;
trace(SQL%ROWCOUNT || 'Rows deleted from GL_INTERFACE Table',C_LEVEL_ERROR,l_log_module);
| 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
--Added for 10124492
AND gl_transfer_status_code <> 'NT';
| 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;
Need to update gl_bc_packets here because when GL posting happens,
data from gl_bc_packets gets flushed. Hence we update the
gl_bc_packets as funds reserved and then call keep_batches
where gl posting happens if customer has provided
post_to_gl parameter as Yes.
*/
IF g_application_id = 200 then
If (g_reserve_flag) then
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace('Updating gl_bc_packets ',C_LEVEL_STATEMENT,l_log_module);
UPDATE gl_bc_packets
SET status_code = 'A'
, result_code = 'P01'
WHERE group_id = g_group_id_tab(i)
AND application_id = g_application_id
and status_code = 'C';
trace('No of rows updated as Reserved in GL_BC_PACKETS ='||SQL%ROWCOUNT,C_LEVEL_PROCEDURE,l_log_module);
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 FROM gl_interface
WHERE user_je_source_name = g_user_source_name
AND group_id = g_group_id_tab(i);
--When get_gllezl_status returns false, data that are newly inserted
--into GL_INTERFACE table are not deleted. Added below code to
--delete those data in GL_INTERFACE.
If g_gl_interface_table_name = 'GL_INTERFACE' Then
--bug#8691650 delete for all the group ids in a loop
FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
delete from gl_interface
where user_je_source_name = g_user_source_name
and group_id = g_group_id_tab(i);
delete from gl_interface
where user_je_source_name = g_user_source_name
and group_id = g_primary_ledgers_tab(i).group_id
and request_id = g_request_id;
trace(SQL%ROWCOUNT || 'Rows deleted from GL_INTERFACE Table',C_LEVEL_ERROR,l_log_module);
Need to update gl_bc_packets here because Journal import is disabled
and funds in gl bc packets needs to be marked as reserved.
*/
IF g_application_id = 200 then
If (g_reserve_flag) then
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace('Updating gl_bc_packets ',C_LEVEL_STATEMENT,l_log_module);
UPDATE gl_bc_packets
SET status_code = 'A'
WHERE group_id = g_group_id_tab(i)
AND application_id = g_application_id
and status_code = 'C';
trace('No of rows updated as Reserved in GL_BC_PACKETS ='||SQL%ROWCOUNT,C_LEVEL_PROCEDURE,l_log_module);
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;