[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_CHAR(SYSDATE, '-HH24:MI:SS')
INTO l_date
FROM DUAL;
SELECT decode(instr(value,','),0,value,
SUBSTR (value,1,instr(value,',') - 1))
INTO l_file_location
FROM v$parameter
WHERE name = 'utl_file_dir';
' as select * from XLA_EVENTS where rownum<1 ';
' add datafix_update_date date default sysdate';
' as select * from XLA_AE_HEADERS where rownum<1 ';
' add datafix_update_date date default sysdate';
' as select * from XLA_AE_LINES where rownum<1 ';
' add datafix_update_date date default sysdate';
' as select * from XLA_DISTRIBUTION_LINKS where rownum<1 ';
' add datafix_update_date date default sysdate';
' as select * from XLA_TRANSACTION_ENTITIES where rownum<1 ';
' add datafix_update_date date default sysdate';
stmt_str := 'select column_name from '|| l_all_tab_columns ||
' where table_name=:1 and column_name<>''DATAFIX_UPDATE_DATE''';
stmt_str := 'select column_name from '|| l_all_tab_columns ||
' where table_name=:1 and owner =:2 and column_name<>''DATAFIX_UPDATE_DATE''';
sql_liab_stat := 'select count(*) from '|| l_tables ||
' where table_name='||''''||'HEADERS_'||P_Bug_number||'''';
sql_liab_stat := 'insert into events_'||P_Bug_Number||'('||col_str5||') '||
' select '||col_str5||' from xla_events '||
' where event_id in '||
' (select event_id from '||l_driver_table||
' Where process_flag=''Y'')';
sql_liab_stat := 'insert into headers_'||P_Bug_Number||'('||col_str1||') '||
' select '||col_str1||' from xla_ae_headers '||
' where event_id in '||
' (select event_id from '||l_driver_table||
' Where process_flag=''Y'')';
sql_liab_stat := 'insert into lines_'||P_Bug_Number||'('||col_str2||') '||
' select '||col_str2||' from xla_ae_lines '||
' where ae_header_id in '||
' (select xah.ae_header_id '||
' from headers_'||P_Bug_Number||' xah, '||
l_driver_table||' dr '||
' where dr.event_id = xah.event_id '||
' and dr.process_flag = ''Y'' '||
' ) ';
sql_liab_stat := 'insert into distrib_links_'||P_Bug_Number||'('||col_str3||') '||
' select '||col_str3||' from xla_distribution_links '||
' where ae_header_id in '||
' (select xah.ae_header_id '||
' from headers_'||P_Bug_Number||' xah, '||
l_driver_table||' dr '||
' where dr.event_id = xah.event_id '||
' and dr.process_flag = ''Y'' '||
' ) ';
(p_select_list in VARCHAR2,
p_table_in in VARCHAR2,
p_where_in in VARCHAR2,
P_calling_sequence in VARCHAR2,
p_print_in_output IN BOOLEAN /* GSI Bug 9490277 */) IS
l_calling_sequence varchar2(500);
select_list1 varchar2(2000):=P_SELECT_LIST;
dot_loc := INSTR(select_list1,',');
col_list := col_list || ', ' || select_list1;
colname (col_count) := select_list1;
col_list := col_list || ', ' || SUBSTR (select_list1, 1, dot_loc-1);
colname (col_count) := SUBSTR (select_list1, 1, dot_loc-1);
select_list1:=SUBSTR (select_list1, dot_loc+1);
SELECT data_type,DATA_LENGTH
INTO coltype (col_count) ,collen(col_count)
FROM all_tab_columns
WHERE owner = owner_nm
AND table_name = table_nm
AND column_name=colname (col_count);
'SELECT ' || col_list ||
' FROM ' || p_table_in || ' ' || where_clause,
1);
l_message := 'SELECT ' || col_list ||
' FROM ' || p_table_in || ' ' || where_clause||'';
table. It also takes in as input SELECT LIST which determine
the list of columns which will be backed up. The additional
WHERE caluse can also be passed in as input. */
Procedure Backup_data
(p_source_table in VARCHAR2,
p_destination_table in VARCHAR2,
p_select_list in VARCHAR2,
p_where_clause in VARCHAR2,
P_calling_sequence in VARCHAR2) is
l_calling_sequence varchar2(4000);
sql_stmt := 'select count(*) from '|| l_tables ||
' where table_name='||''''||p_destination_table||'''';
' as select '||p_select_list||' from '||
p_source_table||' where rownum<1 ';
' add datafix_update_date date default sysdate';
sql_stmt := 'SELECT column_name,
data_type|| decode(data_type, ''DATE'', '''', ''(''||data_length||''''||nvl2(data_precision, '',''||data_precision||'')'', '')'')) column_type
FROM all_tab_columns
WHERE table_name IN ('''||p_source_table||''')
MINUS
SELECT column_name,
data_type|| decode(data_type, ''DATE'', '''', ''(''||data_length||''''||nvl2(data_precision, '',''||data_precision||'')'', '')'')) column_type
FROM all_tab_columns
WHERE table_name IN ('''||p_destination_table||''')';
sql_stmt := 'insert into '||p_destination_table||
'('||P_SELECT_LIST||') '||' select '||P_SELECT_LIST||
' from '||P_SOURCE_TABLE||' '||P_WHERE_CLAUSE;
SELECT fu.user_id
INTO l_user_id
FROM fnd_user fu
WHERE fu.user_name = p_user_name;
SELECT fr.responsibility_id
INTO l_resp_id
FROM fnd_responsibility_tl fr
WHERE fr.responsibility_name = p_resp_name
AND rownum = 1;
p_delete_event IN VARCHAR2,
p_commit_flag IN VARCHAR2,
p_calling_sequence IN VARCHAR2) IS
l_event_status_code XLA_EVENTS.EVENT_STATUS_CODE%TYPE;
l_log_msg := 'p_delete_event '||p_delete_event ;
SELECT 1
INTO l_dummy
FROM dual
WHERE EXISTS
(SELECT 1
FROM xla_events xe
WHERE xe.application_id = 200
AND xe.event_id = p_event_id);
SELECT xe.event_status_code,
xe.entity_id
INTO l_event_status_code,
l_entity_id
FROM xla_events xe
WHERE xe.application_id = 200
AND xe.event_id = p_event_id;
SELECT xah.gl_transfer_status_code
INTO l_gl_transfer_status_code
FROM xla_ae_headers xah
WHERE xah.application_id = 200
AND xah.gl_transfer_status_code = 'Y'
AND xah.event_id = p_event_id
AND rownum = 1;
While calling delete entity, if l_result is 0 then entity deleted
else entity not deleted as there are events associated to the entity
*/
BEGIN
l_debug_info := ' Fetching security context..';
SELECT security_id_int_1,
legal_entity_id,
ledger_id,
entity_code,
source_id_int_1,
transaction_number,
application_id
INTO l_event_security_context.security_id_int_1,
l_event_source_info.legal_entity_id,
l_event_source_info.ledger_id,
l_event_source_info.entity_type_code,
l_event_source_info.source_id_int_1,
l_event_source_info.transaction_number,
l_event_source_info.application_id
FROM xla_transaction_entities_upg xte
WHERE xte.application_id = 200
AND xte.entity_id = l_entity_id;
SELECT 'Y'
INTO l_journals_exist
FROM dual
WHERE EXISTS
(SELECT 'JOURNALS EXISTS'
FROM xla_ae_headers
WHERE application_id =200
AND event_id = p_event_id);
xla_datafixes_pub.delete_journal_entries
(p_api_version => l_api_Version,
p_init_msg_list => l_InIt_msg_List,
p_application_id => l_Application_Id,
p_event_id => p_event_id,
x_return_status => l_Return_Status,
x_msg_count => x_msg_Count,
x_msg_data => x_msg_Data);
l_error_log := 'Error in xla_DataFixes_Pub.delete_journal_entries:'|| x_msg_Data;
IF (p_delete_event = 'Y' and l_Return_Status = 'S' ) THEN
BEGIN
l_debug_info := ' Calling delete event..';
AP_XLA_EVENTS_PKG.delete_event
( p_event_source_info => l_event_source_info,
p_event_id => p_event_id,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_calling_sequence
);
SELECT 1
INTO l_dummy
FROM dual
WHERE EXISTS
(SELECT 1
FROM xla_transaction_entities_upg xte
WHERE xte.application_id = 200
AND xte.entity_id = l_entity_id);
l_debug_info := 'Calling delete entity..';
l_result := xla_events_pub_pkg.delete_entity (
p_source_info => l_event_source_info,
p_valuation_method => NULL,
p_security_context => l_event_security_context );
p_update_process_flag IN VARCHAR2,
P_calc_undo_date IN VARCHAR2,
P_commit_flag IN VARCHAR2 DEFAULT 'N',
p_calling_sequence IN VARCHAR2) IS
l_sql_stmt LONG;
l_update_process_flag VARCHAR2(1);
SELECT 1
INTO l_dummy
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'EVENT_ID';
SELECT 'Y'
INTO l_check_process_flag
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'PROCESS_FLAG';
'must get updated';
l_update_process_flag := p_update_process_flag;
l_update_process_flag := 'Y';
SELECT 'Y'
INTO l_check_proposed_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'PROPOSED_UNDO_DATE';
' SELECT DISTINCT '||
' xah.event_id, '||
' xah.event_type_code, '||
' xah.ae_header_id, '||
' xah.accounting_date, '||
' DECODE(xte.entity_code, '||
' ''AP_INVOICES'', ''Invoice'', '||
' ''AP_PAYMENTS'', ''Payments''), '||
' xte.source_id_int_1, '||
' xte.transaction_number, '||
' xte.security_id_int_1, '||
' glps.period_name, '||
' DECODE(glps.closing_status, '||
' ''C'', ''Closed'', '||
' ''N'', ''Never Opened'', '||
' ''Not-Open''), '||
' gl.name '||
' FROM xla_events xe, '||
' xla_ae_headers xah, '||
l_driver_table||' dr, '||
' xla_transaction_entities_upg xte, '||
' gl_period_statuses glps, '||
' gl_ledgers gl '||
' WHERE xe.application_id = 200 '||
' AND xah.application_id =200 '||
' AND xte.application_id =200 '||
' AND xe.event_id = dr.event_id '||
' AND xe.entity_id = xte.entity_id '||
' AND xe.event_id = xah.event_id '||
' AND xah.entity_id = xte.entity_id '||
' AND xe.event_status_code = ''P'' '||
' AND xah.accounting_entry_status_code = ''F'' '||
' AND xah.event_type_code <> ''MANUAL'' '||
' AND (glps.application_id = 101 OR '||
' (glps.application_id = 200 AND '||
' xah.ledger_id = xte.ledger_id)) '||
' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
' AND glps.set_of_books_id = xah.ledger_id '||
' AND glps.closing_status NOT IN (''O'',''F'') '||
' AND dr.proposed_undo_date IS NOT NULL '||
' AND dr.process_flag = ''Y'' '|| /*Bug 9727543*/
' AND dr.proposed_undo_date BETWEEN glps.start_date AND glps.end_date '||
' AND xah.ledger_id = gl.ledger_id ';
IF (l_update_process_flag = 'Y' AND period_close_list.event_id_l.COUNT > 0) THEN
l_debug_info := 'The column process_flag has been found, proceeding to update';
' UPDATE '||l_driver_table||
' SET process_flag = ''E'' '|| /*Bug 9727543*/
' WHERE event_id = '||period_close_list.event_id_l(i);
l_debug_info := 'Proceeding to update the process flag for the event '||
period_close_list.event_id_l(i);
' SELECT DISTINCT '||
' xah.event_id, '||
' xah.event_type_code, '||
' xah.ae_header_id, '||
' xah.accounting_date, '||
' DECODE(xte.entity_code, '||
' ''AP_INVOICES'', ''Invoice'', '||
' ''AP_PAYMENTS'', ''Payments''), '||
' xte.source_id_int_1, '||
' xte.transaction_number, '||
' xte.security_id_int_1, '||
' glps.period_name, '||
' DECODE(glps.closing_status, '||
' ''C'', ''Closed'', '||
' ''N'', ''Never Opened'', '||
' ''Not-Open''), '||
' gl.name '||
' FROM xla_events xe, '||
' xla_ae_headers xah, '||
l_driver_table||' dr, '||
' xla_transaction_entities_upg xte, '||
' gl_period_statuses glps, '||
' gl_ledgers gl '||
' WHERE xe.application_id = 200 '||
' AND xah.application_id =200 '||
' AND xte.application_id =200 '||
' AND xe.event_id = dr.event_id '||
' AND dr.process_flag = ''Y'' '|| /*Bug 9727543*/
' AND xe.entity_id = xte.entity_id '||
' AND xe.event_id = xah.event_id '||
' AND xah.entity_id = xte.entity_id '||
' AND xe.event_status_code = ''P'' '||
' AND xah.accounting_entry_status_code = ''F'' '||
' AND xah.event_type_code <> ''MANUAL'' '||
' AND (glps.application_id = 101 OR '||
' (glps.application_id = 200 AND '||
' xah.ledger_id = xte.ledger_id)) '||
' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
' AND glps.set_of_books_id = xah.ledger_id '||
' AND glps.closing_status NOT IN (''O'',''F'') '||
' AND xah.ledger_id = gl.ledger_id '||
' AND '||l_date_string||' BETWEEN glps.start_date AND glps.end_date ';
' (SELECT 1 '||
' FROM gl_period_statuses glpse, '||
' xla_ae_headers xahe '||
' WHERE xahe.application_id = 200 '||
' AND (glpse.application_id = 101 OR '||
' (glpse.application_id = 200 AND '||
' xahe.ledger_id = xte.ledger_id)) '||
' AND xahe.event_id = xe.event_id '||
' AND nvl(glpse.adjustment_period_flag, ''N'') = ''N'' '||
' AND glpse.set_of_books_id = xahe.ledger_id '||
' AND glpse.closing_status NOT IN (''O'',''F'') '||
' AND xe.event_date BETWEEN glpse.start_date AND glpse.end_date) ';
IF (l_update_process_flag = 'Y' AND period_close_list.event_id_l.COUNT > 0) THEN
l_debug_info := 'The column process_flag has been found, proceeding to update';
' UPDATE '||l_driver_table||
' SET process_flag = ''E'' '|| /*Bug 9727543*/
' WHERE event_id = '||period_close_list.event_id_l(i);
l_debug_info := 'Proceeding to update the process flag for the event '||
period_close_list.event_id_l(i);
SELECT 1
INTO l_dummy
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'CALCULATED_UNDO_DATE';
' UPDATE '||l_driver_table ||' dr '||
' SET (dr.calculated_undo_date, '||
' dr.calculated_undo_period) = '||
' (SELECT dr.proposed_undo_date, glps.period_name '||
' FROM xla_events xe, '||
' gl_period_statuses glps, '||
' xla_transaction_entities_upg xte '||
' WHERE xe.application_id = 200 '||
' AND glps.application_id = 200 '||
' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
' AND glps.set_of_books_id = xte.ledger_id '||
' AND xte.application_id =200 '||
' AND xte.entity_id = xe.entity_id '||
' AND dr.event_id = xe.event_id '||
' AND dr.proposed_undo_date BETWEEN glps.start_date '||
' AND glps.end_date) '||
' WHERE 1=1 ';
' (SELECT 1 '||
' FROM gl_period_statuses glps, '||
' xla_transaction_entities_upg xte, '||
' xla_ae_headers xah '||
' WHERE (glps.application_id = 101 OR '||
' (glps.application_id = 200 AND '||
' xah.ledger_id = xte.ledger_id)) '||
' AND dr.event_id = xah.event_id '||
' AND xah.application_id =200 '||
' AND xte.application_id =200 '||
' AND xah.entity_id = xte.entity_id '||
' AND xah.ledger_id = glps.set_of_books_id '||
' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
' AND dr.proposed_undo_date BETWEEN glps.start_date '||
' AND glps.end_date '||
' AND glps.closing_status NOT IN (''O'',''F'')) ';
' UPDATE '||l_driver_table ||' dr '||
' SET (dr.calculated_undo_date, '||
' dr.calculated_undo_period) = '||
' (SELECT xe.event_date, glps.period_name '||
' FROM xla_events xe, '||
' gl_period_statuses glps, '||
' xla_transaction_entities_upg xte '||
' WHERE xe.application_id = 200 '||
' AND glps.application_id = 200 '||
' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
' AND glps.set_of_books_id = xte.ledger_id '||
' AND xte.application_id =200 '||
' AND xte.entity_id = xe.entity_id '||
' AND dr.event_id = xe.event_id '||
' AND xe.event_date BETWEEN glps.start_date '||
' AND glps.end_date) '||
' WHERE 1=1 ';
' (SELECT 1 '||
' FROM gl_period_statuses glps, '||
' xla_transaction_entities_upg xte, '||
' xla_ae_headers xah '||
' WHERE (glps.application_id = 101 OR '||
' (glps.application_id = 200 AND '||
' xah.ledger_id = xte.ledger_id)) '||
' AND dr.event_id = xah.event_id '||
' AND xah.application_id =200 '||
' AND xte.application_id =200 '||
' AND xah.entity_id = xte.entity_id '||
' AND xah.ledger_id = glps.set_of_books_id '||
' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
' AND xah.accounting_date BETWEEN glps.start_date '||
' AND glps.end_date '||
' AND glps.closing_status NOT IN (''O'',''F'')) ';
' UPDATE '||l_driver_table ||' dr '||
' SET (dr.calculated_undo_date, '||
' dr.calculated_undo_period) = '||
' (SELECT trunc(sysdate), glps.period_name '||
' FROM xla_events xe, '||
' gl_period_statuses glps, '||
' xla_transaction_entities_upg xte '||
' WHERE xe.application_id = 200 '||
' AND glps.application_id = 200 '||
' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
' AND glps.set_of_books_id = xte.ledger_id '||
' AND xte.application_id =200 '||
' AND xte.entity_id = xe.entity_id '||
' AND dr.event_id = xe.event_id '||
' AND trunc(sysdate) BETWEEN glps.start_date '||
' AND glps.end_date) '||
' WHERE 1=1 ';
' (SELECT 1 '||
' FROM gl_period_statuses glps, '||
' xla_transaction_entities_upg xte, '||
' xla_ae_headers xah '||
' WHERE (glps.application_id = 101 OR '||
' (glps.application_id = 200 AND '||
' xah.ledger_id = xte.ledger_id)) '||
' AND dr.event_id = xah.event_id '||
' AND xah.application_id =200 '||
' AND xte.application_id =200 '||
' AND xah.entity_id = xte.entity_id '||
' AND xah.ledger_id = glps.set_of_books_id '||
' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
' AND trunc(sysdate) BETWEEN glps.start_date '||
' AND glps.end_date '||
' AND glps.closing_status NOT IN (''O'',''F'')) ';
p_update_process_flag IN VARCHAR2,
p_commit_flag IN VARCHAR2 DEFAULT 'N',
p_calling_sequence IN VARCHAR2) IS
l_sql_stmt LONG;
' SELECT DISTINCT '||
' xah.event_id, '||
' xah.event_type_code, '||
' xah.ae_header_id, '||
' xah.accounting_date, '||
' xal.ae_line_num, '||
' xal.accounting_class_code, '||
' DECODE(xte.entity_code, '||
' ''AP_INVOICES'', ''Invoice'', '||
' ''AP_PAYMENTS'', ''Payments''), '||
' xte.source_id_int_1, '||
' xte.transaction_number, '||
' xte.security_id_int_1, '||
' glcc.code_combination_id, '||
' glcc.padded_concatenated_segments, '||
' glcc.enabled_flag, '||
' glcc.end_date_active, '||
' gl.name '||
' FROM xla_events xe, '||
' xla_ae_headers xah, '||
' xla_ae_lines xal, '||
l_driver_table||' dr, '||
' xla_transaction_entities_upg xte, '||
' gl_code_combinations_kfv glcc, '||
' gl_ledgers gl '||
' WHERE xe.application_id = 200 '||
' AND xah.application_id = 200 '||
' AND xal.application_id = 200 '||
' AND xte.application_id =200 '||
' AND xe.event_id = dr.event_id '||
' and DR.PROCESS_FLAG = ''Y'' '|| /*Bug 9727543*/
' AND xe.entity_id = xte.entity_id '||
' AND xe.event_id = xah.event_id '||
' AND xah.entity_id = xte.entity_id '||
' AND xe.event_status_code = ''P'' '||
' AND xah.accounting_entry_status_code = ''F'' '||
' AND xah.event_type_code <> ''MANUAL'' '||
' AND xah.ae_header_id = xal.ae_header_id '||
' AND xal.code_combination_id = glcc.code_combination_id '||
' AND (glcc.enabled_flag = ''N'' OR glcc.end_date_active IS NOT NULL) '||
' AND xah.ledger_id = gl.ledger_id ';
SELECT 'Y'
INTO l_check_process_flag
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'PROCESS_FLAG';
IF (l_check_process_flag = 'Y' AND p_update_process_flag = 'Y') THEN
l_debug_info := 'The column process_flag has been found, proceeding to update';
' UPDATE '||l_driver_table||
' SET process_flag = ''E'' '||
' WHERE event_id = '||invalid_ccid_list.event_id_l(i);
l_debug_info := 'Proceeding to update the process flag for the event '||
invalid_ccid_list.event_id_l(i);
SELECT /*LEADING(ASP, XTE)*/ DISTINCT xe.event_id,
security_id_int_1 cur_org_id,
decode(xte.entity_code, 'AP_PAYMENTS', 'CHECKS', 'AP_INVOICES', 'INVOICES') check_or_invoice,
xe.event_date gl_date,
MAX(DECODE(NVL(gl_transfer_status_code,'N'), 'Y', 'Y', 'N')) gl_transfer_status_code /*bug 13911650*/
FROM xla_transaction_entities_upg xte,
xla_events xe,
xla_ae_headers xah,
ap_system_parameters_all asp
WHERE xte.entity_id = xe.entity_id
AND xe.application_id = 200
AND xte.entity_code = p_Source_Table
AND nvl(source_id_int_1,-99) = p_Check_Or_Invoice_Id --nvl added by bug 7655892
AND xe.event_status_code = 'P'
AND xe.process_status_code = 'P'
AND xah.event_id = xe.event_id
AND nvl(xe.event_id, xe.event_id) = nvl(p_Event_Id, xe.event_id) --nvl added to both sides in 7655892
AND xah.application_id = 200
AND xte.application_id = 200
/* AND xah.ledger_id = xte.ledger_id Bug 13900488 */
AND xte.ledger_id = asp.set_of_books_id --extra join condition added by 7655892
AND xte.security_id_int_1 = asp.org_id
AND nvl(xe.budgetary_control_flag, 'N') = 'N'
GROUP BY xe.event_id,
security_id_int_1,
decode(xte.entity_code, 'AP_PAYMENTS', 'CHECKS', 'AP_INVOICES', 'INVOICES'),
xe.event_date; /* bug 13911650, 7627438*/
SELECT DISTINCT gps.Period_Name
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 200
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND p_Date BETWEEN Trunc(gps.Start_Date)
AND Trunc(gps.End_Date)
AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
AND gps.closing_Status in ('O', 'F')
INTERSECT
SELECT DISTINCT gps.Period_Name
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 101
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND p_Date BETWEEN Trunc(gps.Start_Date)
AND Trunc(gps.End_Date)
AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
AND gps.closing_Status in ('O', 'F');
SELECT Period_Name, End_Date
FROM (
SELECT DISTINCT gps.Period_Name, trunc(gps.End_Date) End_date
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 200
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
AND gps.closing_Status in ('O', 'F')
INTERSECT
SELECT DISTINCT gps.Period_Name, trunc(gps.End_Date) End_date
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 101
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
AND gps.closing_Status in ('O', 'F')
order by end_date
)
WHERE rownum < 2;
SELECT DISTINCT gps.Period_Name
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 200
AND p_Gl_Date between trunc(gps.start_date) and trunc(gps.end_date)
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
AND gps.closing_Status in ('O', 'F')
INTERSECT
SELECT DISTINCT gps.Period_Name
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 101
AND p_Gl_Date between trunc(gps.start_date) and trunc(gps.end_date)
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
AND gps.closing_Status in ('O', 'F');
ins_AP_undo_event_log_stmt VARCHAR2(200) := 'INSERT INTO AP_undo_event_log('
||'EVENT_ID,E2,E3,STATUS,INVOICE_ID,CHECK_ID, BUG_ID) '||
'VALUES(:1, :2, :3, :4, :5, :6, :7)';
log_table_exists_stmt VARCHAR2(200) := 'select count(*) '||
'from '||l_table_name ||
' where table_name = ''AP_UNDO_EVENT_LOG'' ';
Debug_Info := 'xla_DataFixes_Pub.delete_journal_entries';
l_log_msg := 'Calling xla_datafixes_pub.delete_journal_entries';
xla_datafixes_pub.delete_journal_entries
(p_api_version => l_aPi_Version,
p_init_msg_list => l_InIt_msg_List,
p_application_id => l_Application_Id,
p_event_id => Events_to_Process_tab(i).Event_Id,
x_return_status => l_Return_Status,
x_msg_count => x_msg_Count,
x_msg_data => x_msg_Data);
l_log_msg := 'Undo_Accounting : Error in xla_DataFixes_Pub.delete_journal_entries
:'|| x_msg_Data;
SELECT event_status_code
INTO l_event_status_code
FROM xla_events xe
WHERE xe.application_id = 200
AND xe.event_id = Events_to_Process_tab(i).Event_Id;
UPDATE ap_Payment_History_All aph
SET Accounting_Date = l_gl_Date,
Posted_Flag = 'N',
Last_Updated_By = fnd_Global.User_Id
WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
AND Check_Id = p_Source_Id;
UPDATE ap_Invoice_Payments_All aip
SET Accounting_Date = l_gl_Date,
Posted_Flag = 'N',
Accrual_Posted_Flag = 'N',
Last_Updated_By = fnd_Global.User_Id,
Period_Name = l_Period_Name
WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
AND Check_Id = p_Source_Id;
UPDATE ap_Invoice_distributions_All aid
SET Accounting_Date = l_gl_Date,
Posted_Flag = 'N',
Accrual_Posted_Flag = 'N',
Last_Updated_By = fnd_Global.User_Id,
Period_Name = l_Period_Name
WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
AND line_type_lookup_code = 'AWT' ;
UPDATE xla_Events
SET Event_Date = l_gl_Date
WHERE Event_Id = Events_to_Process_tab(i).Event_Id;
DELETE
FROM ap_Payment_Hist_dIsts
WHERE Payment_History_Id IN
(SELECT Payment_History_Id
FROM ap_Payment_History_All
WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
AND Check_Id = l_Source_Id);
l_log_msg :='Updated Transaction tables for Payments';
UPDATE ap_Invoice_Distributions_All Aid
SET Accounting_Date = l_gl_Date,
Posted_Flag = 'N',
Accrual_Posted_Flag = 'N',
Last_Updated_By = fnd_Global.User_Id,
Period_Name =l_Period_Name
WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
AND Invoice_Id = l_Source_Id;
UPDATE ap_self_assessed_tax_dist_all asatd
SET Accounting_Date = l_gl_Date,
Posted_Flag = 'N',
Accrual_Posted_Flag = 'N',
Last_Updated_By = fnd_Global.User_Id,
Period_Name = l_Period_Name
WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
AND Invoice_Id = l_Source_Id;
UPDATE xla_Events
SET Event_Date = l_gl_Date
WHERE Event_Id = Events_to_Process_tab(i).Event_Id;
UPDATE ap_prepay_history_all aph
SET Accounting_Date = l_gl_Date,
Posted_Flag = 'N',
Last_Updated_By = fnd_Global.User_Id
WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
AND Invoice_Id = l_Source_Id;
DELETE
FROM ap_prepay_app_dists
WHERE PREPAY_HISTORY_ID IN
(SELECT PREPAY_HISTORY_ID
FROM ap_prepay_history_all
WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
AND transaction_type = 'PREPAYMENT APPLICATION ADJ' --7502473
AND Invoice_Id = l_Source_Id);
l_log_msg :='Updated Transaction tables for Invoice';
p_update_process_flag => 'Y',
p_calc_undo_date => 'Y',
p_commit_flag => 'Y',
p_calling_sequence => l_calling_sequence);
p_update_process_flag => 'Y',
p_commit_flag => 'Y',
p_calling_sequence => l_calling_sequence);
SELECT 1
INTO l_dummy
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'REVERSAL_EVENT_ID';
l_sql_stmt := ' SELECT DISTINCT '||
' xte.entity_code, '||
' xte.source_id_int_1, '||
' xe.event_id, '||
' xte.security_id_int_1, '||
' dr.calculated_undo_date, '||
' dr.calculated_undo_period, '||
' xe.budgetary_control_flag '|| --Bug 10072990
' FROM xla_transaction_entities_upg xte, '||
' xla_events xe, '||
l_driver_table||' dr '||
' WHERE xte.application_id = 200 '||
' AND xe.application_id = 200 '||
' AND dr.event_id = xe.event_id '||
' AND xe.entity_id = xte.entity_id '||
' AND dr.process_flag = ''Y'' '||
' AND xe.event_status_code = ''P'' '||
' AND xe.event_type_code <> ''MANUAL'' '||
' ORDER BY decode(nvl(xe.budgetary_control_flag,''N''),''N'',1,2) ' ||
' , xte.security_id_int_1 ' ; --Bug 10072990
' UPDATE '||l_driver_table||
' SET reversal_event_id = :b1, '||
' new_event_id = :b2, '||
' return_status = :b3 '||
' WHERE event_id = :b4 ';
' SELECT v1.kind, '||
' v1.accounting_class_code, '||
' v1.event_type_code, '||
' v1.event_id, '||
' v1.event_date, '||
' v1.ae_header_id, '||
' v1.balance_type_code, '||
' v1.source_id_int_1, '||
' v1.transaction_number, '||
' v1.entity_code, '||
' v1.ae_line_num, '||
' v1.padded_concatenated_segments, '||
' v1.entered_dr, '||
' v1.entered_cr, '||
' v1.accounted_dr, '||
' v1.accounted_cr, '||
' v1.description, '||
' v1.name '||
' FROM '||
' ( '||
' SELECT ''OLD'' KIND, '||
' xal.accounting_class_code, '||
' xah.event_type_code, '||
' xah.event_id, '||
' xe.event_date, '||
' xah.ae_header_id, '||
' xah.balance_type_code, '||
' xah.accounting_date, '||
' xte.source_id_int_1, '||
' xte.transaction_number, '||
' xte.entity_code, '||
' xal.ae_line_num, '||
' gcc.padded_concatenated_segments, '||
' xal.entered_dr, '||
' xal.entered_cr, '||
' xal.accounted_dr, '||
' xal.accounted_cr, '||
' xal.description, '||
' gl.name '||
' FROM xla_events xe, '||
' xla_ae_headers xah, '||
' xla_ae_lines xal, '||
' xla_transaction_entities_upg xte, '||
' gl_code_combinations_kfv gcc, '||
' gl_ledgers gl '||
' WHERE xe.application_id = 200 '||
' AND xah.application_id = 200 '||
' AND xal.application_id = 200 '||
' AND xte.application_id = 200 '||
' AND xe.event_id = xah.event_id '||
' AND xe.entity_id = xte.entity_id '||
' AND xah.ae_header_id = xal.ae_header_id '||
' AND xah.ledger_id = gl.ledger_id '||
' AND xal.code_combination_id = gcc.code_combination_id '||
' AND xah.event_id IN '||
' (SELECT DISTINCT dr.new_event_id '||
' FROM '||l_driver_table||' dr, '||
' xla_events xe '||
' WHERE xe.application_id = 200 '||
' AND dr.event_id = xe.event_id '||
' AND xe.event_status_code <> ''MANUAL'' '||
' AND dr.process_flag = ''Y'' '||
' AND xe.event_status_code <> ''P'' '||
' ) '||
' UNION ALL '||
' SELECT ''REVERSAL'' KIND, '||
' xal.accounting_class_code, '||
' xah.event_type_code, '||
' xah.event_id, '||
' xe.event_date, '||
' xah.ae_header_id, '||
' xah.balance_type_code, '||
' xah.accounting_date, '||
' xte.source_id_int_1, '||
' xte.transaction_number, '||
' xte.entity_code, '||
' xal.ae_line_num, '||
' gcc.padded_concatenated_segments, '||
' xal.entered_dr, '||
' xal.entered_cr, '||
' xal.accounted_dr, '||
' xal.accounted_cr, '||
' xal.description, '||
' gl.name '||
' FROM xla_events xe, '||
' xla_ae_headers xah, '||
' xla_ae_lines xal, '||
' xla_transaction_entities_upg xte, '||
' gl_code_combinations_kfv gcc, '||
' gl_ledgers gl '||
' WHERE xe.application_id = 200 '||
' AND xah.application_id = 200 '||
' AND xal.application_id = 200 '||
' AND xte.application_id = 200 '||
' AND xe.event_id = xah.event_id '||
' AND xah.ledger_id = gl.ledger_id '||
' AND xe.entity_id = xte.entity_id '||
' AND xah.ae_header_id = xal.ae_header_id '||
' AND xe.event_type_code = ''MANUAL'' '||
' AND xal.code_combination_id = gcc.code_combination_id '||
' AND xe.event_id IN '||
' (SELECT DISTINCT dr.reversal_event_id '||
' FROM '||l_driver_table||' dr, '||
' xla_events xe '||
' WHERE xe.application_id = 200 '||
' AND dr.event_id = xe.event_id '||
' AND xe.event_status_code <> ''MANUAL'' '||
' AND dr.process_flag = ''Y'' '||
' AND xe.event_status_code <> ''P'' '||
' ) '||
' ) v1 '||
' ORDER BY v1.entity_code, '||
' v1.source_id_int_1, '||
' v1.KIND, '||
' v1.event_type_code, '||
' v1.ae_header_id, '||
' v1.balance_type_code, '||
' v1.ae_line_num ';
l_sql_stmt := ' SELECT DISTINCT '||
' xte.entity_code, '||
' xte.source_id_int_1, '||
' xte.transaction_number, '||
' xe.event_id, '||
' dr.calculated_undo_date, '||
' dr.calculated_undo_period, '||
' decode(dr.return_status, '||
' ''XLA_ERROR'', ''XLA Undo API Error'', '||
' ''XLA_EXCEPTION'', ''XLA Undo API throws Exception'', '||
' ''XLA_NO_WORK'', ''XLA API did not Work'', '||
' ''AP_PAYMENT_ERROR'', ''Exception while updating AP Payments'', '||
' ''AP_INVOICE_ERROR'', ''Exception while updating AP Invoices'', '||
' ''UNEXPECTED_EXCEPTION'', ''Unexpected Exception Occurred'', '||
' ''SUCCESS'', ''Success'', '||
' ''Relevant Error Not Found '' '||
' ) '||
' FROM xla_transaction_entities_upg xte, '||
' xla_events xe, '||
l_driver_table||' dr '||
' WHERE xte.application_id = 200 '||
' AND xe.application_id = 200 '||
' AND dr.event_id = xe.event_id '||
' AND xe.entity_id = xte.entity_id '||
' AND dr.process_flag = ''Y'' '||
' AND xe.event_status_code = ''P'' '||
' AND xe.event_type_code <> ''MANUAL'' ';
'UPDATE '||l_driver_table||' dr '||
' SET dr.process_flag = ''D'' '||
' WHERE dr.process_flag = ''Y'' '||
' AND (dr.event_id IN '||
' (SELECT xe.event_id '||
' FROM xla_events xe, '||
l_driver_table||' dr1 '||
' WHERE xe.application_id = 200 '||
' AND xe.event_status_code <> ''P'' '||
' AND xe.event_id = dr1.event_id) ' ||
' OR NOT EXISTS ' ||
' (SELECT 1 ' ||
' FROM xla_events xe ' ||
' WHERE xe.event_id = dr.event_id ' ||
' AND xe.application_id =200 ))';
SELECT XE.Event_ID,
XE.Event_Type_Code,
XE.Event_Date,
XE.Event_Number,
XE.Event_Status_Code,
XTE.Entity_Code,
XTE.Source_ID_Int_1
FROM xla_events XE,
xla_transaction_entities_upg XTE
WHERE XE.application_id = 200
AND XTE.application_id = 200
AND XE.entity_id = XTE.entity_id
AND XE.event_id = p_event_id;
SELECT AIP.Invoice_ID,
AIP.Invoice_Payment_ID,
AIP.Amount,
AIP.Discount_Taken,
AIP.Payment_Base_Amount,
AIP.Invoice_Base_Amount,
AIP.Exchange_Rate_Type,
AIP.Exchange_Date,
AIP.Exchange_Rate,
NVL(AIP.Reversal_Flag,'N'),
AIP.Reversal_Inv_Pmt_ID
FROM Ap_Invoice_Payments_All AIP
WHERE AIP.invoice_id = P_Invoice_id
AND AIP.accounting_event_id = nvl(P_Event_id, AIP.accounting_event_id)
AND AIP.check_id = nvl(P_Check_id, AIP.check_id)
AND nvl(AIP.reversal_flag, 'N') <> 'Y';
SELECT APH.Prepay_History_ID,
APH.Prepay_Invoice_ID,
APH.Invoice_ID,
APH.Invoice_Line_Number,
APH.Transaction_Type,
APH.Accounting_Date,
APH.Invoice_Adjustment_Event_ID,
APH.Related_Prepay_App_Event_ID
FROM AP_Prepay_History_All APH
WHERE APH.Invoice_ID = P_Invoice_ID
AND APH.accounting_event_id = P_event_id
ORDER BY transaction_type;
SELECT AID.Invoice_ID,
AID.Invoice_Distribution_ID Invoice_Distribution_ID,
AID.Line_Type_Lookup_Code,
AID.Amount,
AID.Base_Amount,
AID.Accounting_Event_ID,
AID.Prepay_Distribution_ID,
AID.Prepay_Tax_Diff_Amount,
AID.Parent_Reversal_ID
FROM AP_Invoice_Distributions_All AID
WHERE Invoice_ID = P_Invoice_ID
AND Line_Type_Lookup_Code = 'PREPAY'
AND Accounting_Event_ID = P_event_id
ORDER BY abs(AID.amount) DESC;
SELECT aphd.payment_hist_dist_id,
aphd.accounting_event_id,
aphd.pay_dist_lookup_code,
aphd.invoice_distribution_id,
aphd.amount,
aphd.payment_history_id,
aphd.invoice_payment_id,
aphd.bank_curr_amount,
aphd.cleared_base_amount,
aphd.historical_flag,
aphd.invoice_dist_amount,
aphd.invoice_dist_base_amount,
aphd.invoice_adjustment_event_id,
aphd.matured_base_amount,
aphd.paid_base_amount,
aphd.rounding_amt,
aphd.reversal_flag,
aphd.reversed_pay_hist_dist_id,
aphd.created_by,
aphd.creation_date,
aphd.last_update_date,
aphd.last_updated_by,
aphd.last_update_login,
aphd.program_application_id,
aphd.program_id,
aphd.program_login_id,
aphd.program_update_date,
aphd.request_id,
aphd.awt_related_id,
aphd.release_inv_dist_derived_from,
aphd.pa_addition_flag,
aphd.amount_variance,
aphd.invoice_base_amt_variance,
aphd.quantity_variance,
aphd.invoice_base_qty_variance,
DECODE(asp.automatic_offsets_flag,
'Y',DECODE(asp.liability_post_lookup_code,
'ACCOUNT_SEGMENT_VALUE', aid.dist_code_combination_id,
asp.rounding_error_ccid),
asp.rounding_error_ccid
) write_off_code_combination
FROM ap_payment_hist_dists aphd,
ap_payment_history_all aph,
ap_system_parameters_all asp,
ap_invoice_distributions_all aid
WHERE aphd.accounting_event_id = p_event_id
AND aph.payment_history_id = aphd.payment_history_id
AND aph.org_id = asp.org_id
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND aphd.payment_hist_dist_id > p_max_pay_dist_id;
SELECT apad.prepay_app_dist_id,
apad.prepay_dist_lookup_code,
apad.invoice_distribution_id,
apad.prepay_app_distribution_id,
apad.accounting_event_id,
apad.prepay_history_id,
apad.prepay_exchange_date,
apad.prepay_pay_exchange_date,
apad.prepay_clr_exchange_date,
apad.prepay_exchange_rate,
apad.prepay_pay_exchange_rate,
apad.prepay_clr_exchange_rate,
apad.prepay_exchange_rate_type,
apad.prepay_pay_exchange_rate_type,
apad.prepay_clr_exchange_rate_type,
apad.reversed_prepay_app_dist_id,
apad.amount,
apad.base_amt_at_prepay_xrate,
apad.base_amt_at_prepay_pay_xrate,
apad.base_amount,
apad.base_amt_at_prepay_clr_xrate,
apad.rounding_amt,
apad.round_amt_at_prepay_xrate,
apad.round_amt_at_prepay_pay_xrate,
apad.round_amt_at_prepay_clr_xrate,
apad.last_updated_by,
apad.last_update_date,
apad.last_update_login,
apad.created_by,
apad.creation_date,
apad.program_application_id,
apad.program_id,
apad.program_update_date,
apad.request_id,
apad.awt_related_id,
apad.release_inv_dist_derived_from,
apad.pa_addition_flag,
apad.bc_event_id,
apad.amount_variance,
apad.invoice_base_amt_variance,
apad.quantity_variance,
apad.invoice_base_qty_variance,
DECODE(asp.automatic_offsets_flag,
'Y',DECODE(asp.liability_post_lookup_code,
'ACCOUNT_SEGMENT_VALUE', aid.dist_code_combination_id,
asp.rounding_error_ccid),
asp.rounding_error_ccid
) write_off_code_combination
FROM ap_prepay_app_dists apad,
ap_prepay_history_all apph,
ap_system_parameters_all asp,
ap_invoice_distributions_all aid
WHERE apad.accounting_event_id = P_Event_Id
AND apad.prepay_history_id = apph.prepay_history_id
AND apph.org_id = asp.org_id
AND apad.invoice_distribution_id = aid.invoice_distribution_id
AND apad.prepay_app_dist_id > P_Max_Prepay_Dist_id;
SELECT 1
INTO l_dummy
FROM ap_invoices_all
WHERE invoice_id = l_invoice_id;
SELECT org_id
INTO l_org_id
FROM ap_invoices_all
WHERE invoice_id = l_invoice_id;
SELECT ap_invoices_utility_pkg.Get_Approval_Status
(ai.invoice_id,
ai.invoice_amount,
ai.payment_status_flag,
ai.invoice_type_lookup_code)
INTO l_validation_status
FROM ap_invoices_all ai
WHERE ai.invoice_id = l_invoice_id;
SELECT count(*)
INTO l_count_hist_pay
FROM ap_invoice_payments_all aip,
xla_ae_headers xah,
xla_transaction_entities_upg xte,
xla_ae_lines xal
WHERE xah.application_id = 200
AND aip.check_id = nvl(xte.source_id_int_1, -99)
AND aip.set_of_books_id = xte.ledger_id
AND xte.entity_code = 'AP_PAYMENTS'
AND aip.invoice_id = l_invoice_id
AND xah.upg_batch_id IS NOT NULL
AND xah.upg_batch_id <> -9999
AND xte.entity_id = xah.entity_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.accounting_class_code = 'LIABILITY'
AND rownum = 1;
SELECT count(*)
INTO l_cnt_unacc_inv_evnts
FROM xla_events xe,
xla_transaction_entities_upg xte,
ap_invoices_all ai
WHERE xe.application_id = 200
AND xte.application_id = 200
AND xe.entity_id = xte.entity_id
AND xte.entity_code = 'AP_INVOICES'
AND xe.event_status_code NOT IN ('P', 'N', 'Z')
AND nvl(xte.source_id_int_1, -99) = ai.invoice_id
AND xte.ledger_id = ai.set_of_books_id
AND ai.invoice_id = l_invoice_id
AND rownum = 1;
SELECT count(*)
INTO l_cnt_unacc_pay_evnts
FROM xla_events xe,
xla_transaction_entities_upg xte,
ap_invoice_payments_all aip
WHERE xe.application_id = 200
AND xte.application_id = 200
AND xte.entity_code = 'AP_PAYMENTS'
AND xe.entity_id = xte.entity_id
AND aip.invoice_id = l_invoice_id
AND nvl(xte.source_id_int_1, -99) = aip.check_id
AND xte.ledger_id = aip.set_of_books_id
AND xe.event_status_code NOT IN ('P', 'N', 'Z')
AND rownum = 1;
SELECT count(*)
INTO l_cnt_untrx_inv_evnts
FROM xla_events xe,
xla_ae_headers xah,
xla_transaction_entities_upg xte,
ap_invoices_all ai
WHERE xe.application_id = 200
AND xte.application_id = 200
AND xah.application_id = 200
AND xe.entity_id = xte.entity_id
AND xte.entity_code = 'AP_INVOICES'
AND xe.event_status_code = 'P'
AND xah.event_id = xe.event_id
AND xah.accounting_entry_status_code = 'F'
AND xah.gl_transfer_status_code <> 'Y'
AND nvl(xte.source_id_int_1, -99) = ai.invoice_id
AND xte.ledger_id = ai.set_of_books_id
AND ai.invoice_id = l_invoice_id
AND rownum = 1;
SELECT count(*)
INTO l_cnt_untrx_pay_evnts
FROM xla_events xe,
xla_ae_headers xah,
xla_transaction_entities_upg xte,
ap_invoice_payments_all aip
WHERE xe.application_id = 200
AND xte.application_id = 200
AND xte.entity_code = 'AP_PAYMENTS'
AND xe.entity_id = xte.entity_id
AND aip.invoice_id = l_invoice_id
AND nvl(xte.source_id_int_1, -99) = aip.check_id
AND xte.ledger_id = aip.set_of_books_id
AND xe.event_status_code = 'P'
AND xah.event_id = xe.event_id
AND xah.accounting_entry_status_code = 'F'
AND xah.gl_transfer_status_code <> 'Y'
AND rownum = 1;
SELECT max(xe.event_id)
INTO l_max_event_id
FROM xla_events xe
WHERE xe.application_id = 200
AND xe.event_status_code = 'P'
AND xe.event_id IN
(SELECT aph.accounting_event_id
FROM ap_payment_history_all aph,
ap_invoice_payments_all aip
WHERE aip.check_id = aph.check_id
AND aip.invoice_id = l_invoice_id
AND aph.rev_pmt_hist_id IS NULL
AND aph.transaction_type IN ('PAYMENT CREATED',
'REFUND RECORDED',
'PAYMENT ADJUSTED',
'MANUAL PAYMENT ADJUSTED',
'PAYMENT CLEARING',
'PAYMENT CLEARING ADJUSTED'
)
AND NOT EXISTS
(SELECT 1
FROM ap_payment_history_all aph_rev
WHERE aph_rev.check_id = aph.check_id
AND nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
= nvl(aph.related_event_id, aph.accounting_event_id)
AND aph_rev.rev_pmt_hist_id IS NOT NULL
)
UNION
SELECT aid.accounting_event_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_invoice_id
AND aid.line_type_lookup_code = 'PREPAY'
AND nvl(aid.reversal_flag, 'N') <> 'Y')
AND EXISTS
(SELECT 1
FROM xla_ae_headers xah,
xla_ae_lines xal
WHERE xah.application_id = 200
AND xal.application_id = 200
AND xah.event_id = xe.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.accounting_class_code = 'LIABILITY');
SELECT xe.event_type_code
INTO p_op_event_type
FROM xla_events xe
WHERE xe.application_id = 200
AND xe.event_id = l_max_event_id;
SELECT max(aphd.payment_hist_dist_id)
INTO l_max_pay_dist_id
FROM ap_payment_hist_dists aphd
WHERE aphd.accounting_event_id = l_max_event_id;
SELECT max(apad.prepay_app_dist_id)
INTO l_max_prepay_dist_id
FROM ap_prepay_app_dists apad
WHERE apad.accounting_event_id = l_max_event_id;
SELECT xte.source_id_int_1
INTO l_check_id
FROM xla_transaction_entities_upg xte,
xla_events xe
WHERE xe.application_id = 200
AND xte.application_id = 200
AND xe.entity_id = xte.entity_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xe.event_id = l_max_event_id;
SELECT period_name
Into l_period_name
FROM gl_period_statuses GLPS,
ap_system_parameters_all SP
WHERE application_id = 200
AND sp.org_id = P_Org_Id
AND GLPS.set_of_books_id = SP.set_of_books_id
AND trunc(P_Date) BETWEEN start_date AND end_date
AND closing_status in ('O', 'F')
AND NVL(adjustment_period_flag, 'N') = 'N';
SELECT Start_Date
Into l_start_date
FROM (
SELECT DISTINCT gps.Period_Name, trunc(gps.Start_Date) Start_date
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 200
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
AND gps.closing_Status in ('O', 'F')
INTERSECT
SELECT DISTINCT gps.Period_Name, trunc(gps.start_Date) start_date
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 101
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND Nvl(Asp.Org_Id,- 99) = Nvl(P_Org_Id,- 99)
AND gps.closing_Status in ('O', 'F')
order by Start_date
)
WHERE rownum < 2;
Public api to delete prepay appl/payment cascade adjustment events
INPUT :
p_source_type - 'AP_INVOICES' -- for prepay appl
'AP_PAYMENTS' -- for payment
p_source_id - invoice_id -- when p_source_type is 'AP_INVOICES'
check_id -- when p_source_type is 'AP_PAYMENTS'
p_related_event_id - related event id of cascade adjustment.
This is added to handle single event only
for ex: in case when single event id undone..etc
NOTES :
1. org context needs to be set prior to call the api
2. commit is to be handled by the calling api
*/
FUNCTION delete_cascade_adjustments
(p_source_type IN VARCHAR2,
p_source_id IN NUMBER,
p_related_event_id IN NUMBER DEFAULT NULL)
RETURN BOOLEAN IS
l_procedure_name CONSTANT VARCHAR2(30) := 'delete_cascade_adjustments()';
SELECT DISTINCT aph_adj.accounting_event_id
FROM ap_payment_history_all aph
, xla_events xe
, ap_payment_history_all aph_adj
, xla_events xe_adj
WHERE aph.check_id = p_source_id
AND aph.accounting_event_id = NVL(p_related_event_id
, aph.accounting_event_id)
AND aph.transaction_type IN('PAYMENT CREATED',
'PAYMENT MATURITY',
'PAYMENT CLEARING',
'REFUND RECORDED')
AND aph.posted_flag <> 'Y'
AND xe.event_id = aph.accounting_event_id
AND xe.event_status_code <> 'P'
AND aph_adj.check_id = aph.check_id
AND aph_adj.related_event_id <> aph_adj.accounting_event_id
AND aph_adj.related_event_id = aph.accounting_event_id
AND aph_adj.transaction_type IN('PAYMENT ADJUSTED',
'PAYMENT MATURITY ADJUSTED',
'PAYMENT CLEARING ADJUSTED',
'REFUND ADJUSTED')
AND aph_adj.posted_flag <> 'Y'
AND xe_adj.event_id = aph_adj.accounting_event_id
AND xe_adj.event_status_code <> 'P';
SELECT DISTINCT apph_adj.accounting_event_id
FROM ap_invoices_all ai
, ap_prepay_history_all apph
, xla_events xe
, ap_prepay_history_all apph_adj
, xla_events xe_adj
WHERE 1=1
AND ai.invoice_id = p_source_id
AND ap_invoices_utility_pkg.get_approval_status
(ai.invoice_id,
ai.invoice_amount,
ai.payment_status_flag,
ai.invoice_type_lookup_code)
IN ('NEEDS REAPPROVAL',
'NEVER APPROVED',
'UNAPPROVED')
AND apph.invoice_id = ai.invoice_id
AND apph.accounting_event_id = NVL(p_related_event_id,
apph.accounting_event_id)
AND apph.transaction_type IN('PREPAYMENT APPLIED')
AND apph.posted_flag <> 'Y'
AND NOT EXISTS
(
SELECT 'encumbered'
FROM ap_invoice_distributions_all aid
WHERE aid.accounting_event_id = apph.accounting_event_id
AND NVL(aid.encumbered_flag, 'N') = 'Y' --bug11880177,bug12407622
)
AND xe.event_id = apph.accounting_event_id
AND xe.event_status_code <> 'P'
AND apph_adj.invoice_id = apph.invoice_id
AND apph_adj.related_prepay_app_event_id <> apph_adj.accounting_event_id
AND apph_adj.related_prepay_app_event_id = apph.accounting_event_id
AND apph_adj.transaction_type IN('PREPAYMENT APPLICATION ADJ')
AND apph_adj.posted_flag <> 'Y'
AND xe_adj.event_id = apph_adj.accounting_event_id
AND xe_adj.event_status_code <> 'P';
SELECT security_id_int_1,
legal_entity_id,
ledger_id,
entity_code,
source_id_int_1,
transaction_number,
application_id
INTO l_event_security_context.security_id_int_1,
l_event_source_info.legal_entity_id,
l_event_source_info.ledger_id,
l_event_source_info.entity_type_code,
l_event_source_info.source_id_int_1,
l_event_source_info.transaction_number,
l_event_source_info.application_id
FROM xla_transaction_entities_upg xte
WHERE NVL(xte.source_id_int_1, -99) = p_source_id
AND xte.entity_code = p_source_type
AND xte.application_id = 200;
/* delete PREPAY APPL cascade adjustments */
FOR i IN prepay_appl_cascade_adj_cur
LOOP
AP_XLA_EVENTS_PKG.delete_event
( p_event_source_info => l_event_source_info,
p_event_id => i.accounting_event_id,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_procedure_name
);
DELETE ap_prepay_app_dists apad
WHERE apad.accounting_event_id = i.accounting_event_id
AND NOT EXISTS
(
SELECT 1
FROM xla_events xe
WHERE xe.event_id = apad.accounting_event_id
AND xe.application_id = 200
);
DELETE ap_prepay_history_all apph
WHERE apph.accounting_event_id = i.accounting_event_id
AND NOT EXISTS
(
SELECT 1
FROM xla_events xe
WHERE xe.event_id = apph.accounting_event_id
AND xe.application_id = 200
);
/* delete PAYMENT cascade adjustments */
FOR i IN payment_cascade_adj_cur
LOOP
AP_XLA_EVENTS_PKG.delete_event
( p_event_source_info => l_event_source_info,
p_event_id => i.accounting_event_id,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_procedure_name
);
DELETE ap_payment_hist_dists aphd
WHERE aphd.accounting_event_id = i.accounting_event_id
AND NOT EXISTS
(
SELECT 1
FROM xla_events xe
WHERE xe.event_id = aphd.accounting_event_id
AND xe.application_id = 200
);
DELETE ap_payment_history_all aph
WHERE aph.accounting_event_id = i.accounting_event_id
AND NOT EXISTS
(
SELECT 1
FROM xla_events xe
WHERE xe.event_id = aph.accounting_event_id
AND xe.application_id = 200
);
END delete_cascade_adjustments;
SELECT aid.invoice_distribution_id,
aid.invoice_line_number,
aid.accounting_event_id,
aid.bc_event_id,
aid.match_status_flag
FROM ap_invoice_distributions_all aid,
xla_events xe
WHERE xe.application_id = 200
AND aid.accounting_event_id = xe.event_id
AND aid.invoice_id = P_Invoice_ID
AND xe.event_status_code IN ('I','U')
AND xe.event_type_code IN ('PREPAYMENT APPLIED','PREPAYMENT UNAPPLIED')
AND aid.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
AND aid.prepay_distribution_id IS NOT NULL
AND NVL(encumbered_flag, 'N') <> 'Y'
AND aid.posted_flag <> 'Y'
AND NOT EXISTS
(SELECT 1
FROM xla_events xe_bc,
financials_system_params_all fsp
WHERE fsp.org_id = aid.org_id
AND fsp.purch_encumbrance_flag = 'Y'
AND xe_bc.application_id = 200
AND xe_bc.budgetary_control_flag = 'Y'
AND xe_bc.event_id = aid.bc_event_id
AND xe_bc.event_status_code = 'P'
)
ORDER BY aid.invoice_id,
xe.event_type_code ;
SELECT 'Y'
INTO l_check_invoice_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'INVOICE_ID';
SELECT 'Y'
INTO l_check_ret_stat_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'RETURN_STATUS';
SELECT 'Y'
INTO l_check_process_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'PROCESS_FLAG';
l_sql_stmt := ' UPDATE '||l_driver_table||
' SET process_flag = ''Y''';
SELECT 'Y'
INTO l_check_event_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'EVENT_ID';
l_sql_stmt := 'UPDATE '||l_driver_table||' dr '||
' SET invoice_id = (SELECT DISTINCT invoice_id '||
' FROM ap_invoice_distributions_all '||
' WHERE accounting_event_id = nvl(dr.event_id,-99)) ';
l_sql_stmt := ' SELECT DISTINCT dr.invoice_id, dr.process_flag '||
' FROM '||l_driver_table||' dr '||
' WHERE dr.process_flag NOT IN (''N'',''E'') '|| --bug10019343
' AND EXISTS '||
' (SELECT ''Prepay Invoice'''||
' FROM ap_invoice_distributions_all '||
' WHERE invoice_id = dr.invoice_id '||
' AND line_type_lookup_code = ''PREPAY'')';
SELECT NVL(org_id,-99)
INTO l_org_id
FROM ap_invoices_all
WHERE invoice_id = repop_invoices_list.source_id_l(i);
UPDATE ap_invoice_distributions_all aid
SET aid.accounting_event_id = NULL,
aid.bc_event_id = NULL,
aid.posted_flag = DECODE(aid.posted_flag, 'S', 'N', aid.posted_flag),
aid.match_status_flag = 'S'
WHERE aid.invoice_distribution_id = l_inv_dist_id_tab(j);
-- bug10056653, updated the delete to exclude the PREPAY APP
-- DISTS pertaining to ADJ events
--
FORALL j IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
DELETE FROM ap_prepay_app_dists apad
WHERE (apad.accounting_event_id = l_acct_event_id_tab(j) OR
apad.bc_event_id = l_bc_event_id_tab(j) OR
apad.prepay_app_distribution_id = l_inv_dist_id_tab(j))
AND NOT EXISTS
(SELECT 1
FROM ap_prepay_history_all apph
WHERE apph.prepay_history_id = apad.prepay_history_id
AND apph.transaction_type = 'PREPAYMENT APPLICATION ADJ');
-- bug10056653, updated the delete to exclude the PREPAY
-- HISTORY pertaining to ADJ events, and also ensure that
-- there are no posted or encumbered Invoice dists under
-- the prepay History
--
FORALL j IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
DELETE FROM ap_prepay_history_all apph
WHERE apph.invoice_id = repop_invoices_list.source_id_l(i)
AND apph.transaction_type <> 'PREPAYMENT APPLICATION ADJ'
AND apph.invoice_line_number = l_inv_line_no_tab(j)
AND NOT EXISTS
(SELECT 1
FROM ap_prepay_app_dists apad,
ap_invoice_distributions_all aid
WHERE apad.prepay_history_id = apph.prepay_history_id
AND apad.prepay_app_distribution_id = aid.invoice_distribution_id
AND (aid.posted_flag = 'Y' OR
aid.encumbered_flag = 'Y')
)
AND (apph.accounting_event_id = l_acct_event_id_tab(j) OR
apph.bc_event_id = l_bc_event_id_tab(j) OR
(apph.accounting_event_id IS NULL AND
apph.bc_event_id IS NULL)
);
UPDATE ap_invoice_distributions_all aid
SET aid.accounting_event_id = l_acct_event_id_tab(j),
aid.bc_event_id = l_bc_event_id_tab(j),
aid.match_status_flag = l_match_status_flg_tab(j)
WHERE aid.invoice_distribution_id = l_inv_dist_id_tab(j);
UPDATE ap_prepay_app_dists apad
SET apad.accounting_event_id = l_acct_event_id_tab(j),
apad.bc_event_id = l_bc_event_id_tab(j)
WHERE apad.prepay_app_distribution_id = l_inv_dist_id_tab(j)
AND NOT EXISTS
(SELECT 1
FROM ap_prepay_history_all apph
WHERE apph.prepay_history_id = apad.prepay_history_id
AND apph.transaction_type = 'PREPAYMENT APPLICATION ADJ');
UPDATE ap_prepay_history_all apph
SET apph.accounting_event_id = l_acct_event_id_tab(j),
apph.bc_event_id = l_bc_event_id_tab(j)
WHERE apph.transaction_type <> 'PREPAYMENT APPLICATION ADJ'
AND EXISTS
(SELECT 1
FROM ap_prepay_app_dists apad
WHERE apad.prepay_history_id = apph.prepay_history_id
AND apad.prepay_app_distribution_id = l_inv_dist_id_tab(j));
l_sql_stmt := ' UPDATE '||l_driver_table||' dr '||
' SET return_status = '||SQLERRM||' , '||
' process_flag = ''E'''||
' WHERE dr.invoice_id = repop_invoices_list.source_id_l(i)';
' SELECT distinct dr.invoice_id, '||
' ai.invoice_num, '||
' ai.invoice_type_lookup_code, '||
' ai.invoice_amount, '||
' av.vendor_name, '||
' avs.vendor_site_code, '||
' ai.org_id, '||
' aps.set_of_books_id, '||
' dr.process_flag, '||
' NVL(dr.return_status,''Data Processed'') '||
' FROM '||l_driver_table||' dr, '||
' ap_invoices_all ai, '||
' ap_suppliers av, '||
' ap_supplier_sites_all avs, '||
' ap_system_parameters_all aps '||
' WHERE ai.invoice_id = dr.invoice_id'||
' AND ai.org_id = aps.org_id'||
' AND ai.vendor_id = av.vendor_id'||
' AND ai.vendor_site_id = avs.vendor_site_id'||
' AND dr.process_flag NOT IN (''N'',''E'') '|| --bug10019343
' ORDER BY dr.invoice_id';
' SELECT distinct dr.invoice_id, '||
' ai.invoice_num, '||
' ai.invoice_type_lookup_code, '||
' ai.invoice_amount, '||
' av.vendor_name, '||
' avs.vendor_site_code, '||
' ai.org_id, '||
' aps.set_of_books_id, '||
' dr.process_flag, '||
' NVL(dr.return_status,''Data Processed'') '||
' FROM '||l_driver_table||' dr, '||
' ap_invoices_all ai, '||
' ap_suppliers av, '||
' ap_supplier_sites_all avs, '||
' ap_system_parameters_all aps '||
' WHERE ai.invoice_id = dr.invoice_id'||
' AND ai.org_id = aps.org_id'||
' AND ai.vendor_id = av.vendor_id'||
' AND ai.vendor_site_id = avs.vendor_site_id'||
' AND dr.process_flag = ''E'''||
' ORDER BY dr.invoice_id';
SELECT 'Y'
INTO l_check_invoice_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'INVOICE_ID';
SELECT 'Y'
INTO l_check_ret_stat_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'DEL_CAS_RT_STS'; --bug10056653, changed column name to CAPS
SELECT 'Y'
INTO l_check_process_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'PROCESS_FLAG';
l_sql_stmt := ' UPDATE '||l_driver_table||
' SET process_flag = ''Y''';
SELECT 'Y'
INTO l_check_event_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'EVENT_ID';
l_sql_stmt := 'UPDATE '||l_driver_table||' dr '||
' SET invoice_id = (SELECT DISTINCT invoice_id '||
' FROM ap_invoice_distributions_all '||
' WHERE accounting_event_id = nvl(dr.event_id,-99))';
SELECT 'Y'
INTO l_check_canc_dt_col
FROM sys.all_tab_columns
WHERE table_name = l_driver_table
AND column_name = 'CANCELLED_DATE';
l_sql_stmt := 'UPDATE '||l_driver_table||' dr '||
' SET cancelled_date = (SELECT cancelled_date '||
' FROM ap_invoices_all '||
' WHERE invoice_id = dr.invoice_id)';
l_sql_stmt := ' SELECT DISTINCT dr.invoice_id, dr.event_id, dr.cancelled_date, dr.process_flag '||
' FROM '||l_driver_table||' dr '||
' WHERE dr.process_flag NOT IN (''N'',''E'') ';
l_sql_stmt := ' UPDATE ap_invoices_all '||
' SET force_revalidation_flag = ''Y'','||
' cancelled_date = NULL'||
' WHERE invoice_id = '||del_cascade_inv_list.source_id_l(i)||
' AND force_revalidation_flag != ''Y''';
IF delete_cascade_adjustments
('AP_INVOICES',
del_cascade_inv_list.source_id_l(i),
del_cascade_inv_list.event_id_l(i)
) THEN
l_debug_info := 'After calling delete_cascade_adjustments..';
l_sql_stmt := ' UPDATE '||l_driver_table||' dr '||
' SET del_cas_rt_sts = '||''''||l_debug_info||''''|| /*bug:12764043*/
' WHERE dr.invoice_id ='|| del_cascade_inv_list.source_id_l(i); /*bug:11660129*/
l_sql_stmt := ' UPDATE ap_invoices_all '||
' SET force_revalidation_flag = ''N'','||
' cancelled_date = NVL('||''''||del_cascade_inv_list.cancelled_date_l(i)||''''||',NULL)'||
' WHERE invoice_id = '||del_cascade_inv_list.source_id_l(i);
'cascade events, if any, are deleted ';
' SELECT distinct dr.invoice_id, '||
' dr.event_id, '||
' ai.invoice_num, '||
' dr.process_flag, '||
' NVL(dr.del_cas_rt_sts,''Data Processed'') '||
' FROM '||l_driver_table||' dr, '||
' ap_invoices_all ai'||
' WHERE ai.invoice_id = dr.invoice_id'||
' AND dr.process_flag NOT IN (''N'',''E'') '||
' ORDER BY dr.invoice_id';
SELECT /*LEADING(ASP, XTE)*/
DISTINCT xe.event_id
, xe.event_type_code --bug12833171
, security_id_int_1 cur_org_id
, xe.event_date gl_date
, nvl(xe.budgetary_control_flag, 'N') budgetary_control_flag /*Bug 12975723*/
, MAX(DECODE(NVL(gl_transfer_status_code,'N'), 'Y', 'Y', 'N')) gl_transfer_status_code /* bug 13911650*/
FROM xla_transaction_entities_upg xte
, xla_events xe
, xla_ae_headers xah
, ap_system_parameters_all asp
WHERE xte.entity_id = xe.entity_id
AND xe.application_id = 200
AND xte.entity_code = 'AP_INVOICES'
AND NVL( source_id_int_1, - 99 ) = p_invoice_id
AND xe.event_status_code = 'P'
AND xe.process_status_code = 'P'
AND xah.event_id = xe.event_id
AND xe.event_id = NVL( p_event_id, xe.event_id )
AND xah.application_id = 200
AND xte.application_id = 200
/* AND xah.ledger_id = xte.ledger_id Bug 13900488 */
AND xte.ledger_id = asp.set_of_books_id
AND xte.security_id_int_1 = asp.org_id
GROUP BY xe.event_id
, xe.event_type_code --bug12833171
, security_id_int_1
, xe.event_date
, nvl(xe.budgetary_control_flag, 'N') /* bug 13911650, 12975723*/
ORDER BY decode(nvl(xe.budgetary_control_flag,'N'),'N',1,2);
SELECT DISTINCT gps.period_name
FROM gl_period_statuses gps
, ap_system_parameters_all asp
WHERE gps.application_id = 200
AND gps.set_of_books_id = asp.set_of_books_id
AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
AND p_date BETWEEN TRUNC( gps.start_date ) AND TRUNC( gps.end_date )
AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
AND gps.closing_status IN( 'O', 'F' )
INTERSECT
SELECT DISTINCT gps.period_name
FROM gl_period_statuses gps
, ap_system_parameters_all asp
WHERE gps.application_id = 101
AND gps.set_of_books_id = asp.set_of_books_id
AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
AND p_date BETWEEN TRUNC( gps.start_date ) AND TRUNC( gps.end_date )
AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
AND gps.closing_status IN( 'O', 'F' );
SELECT period_name
, end_date
FROM
( SELECT DISTINCT gps.period_name
, TRUNC( gps.end_date ) end_date
FROM gl_period_statuses gps
, ap_system_parameters_all asp
WHERE gps.application_id = 200
AND gps.set_of_books_id = asp.set_of_books_id
AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
AND gps.closing_status IN( 'O', 'F' )
INTERSECT
SELECT DISTINCT gps.period_name
, TRUNC( gps.end_date ) end_date
FROM gl_period_statuses gps
, ap_system_parameters_all asp
WHERE gps.application_id = 101
AND gps.set_of_books_id = asp.set_of_books_id
AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
AND gps.closing_status IN( 'O', 'F' )
ORDER BY end_date
)
WHERE rownum < 2;
SELECT DISTINCT gps.period_name
FROM gl_period_statuses gps
, ap_system_parameters_all asp
WHERE gps.application_id = 200
AND p_gl_date BETWEEN TRUNC( gps.start_date ) AND TRUNC( gps.end_date )
AND gps.set_of_books_id = asp.set_of_books_id
AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
AND gps.closing_status IN( 'O', 'F' )
INTERSECT
SELECT DISTINCT gps.period_name
FROM gl_period_statuses gps
, ap_system_parameters_all asp
WHERE gps.application_id = 101
AND p_gl_date BETWEEN TRUNC( gps.start_date ) AND TRUNC( gps.end_date )
AND gps.set_of_books_id = asp.set_of_books_id
AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
AND gps.closing_status IN( 'O', 'F' );
ins_ap_undo_event_log_stmt VARCHAR2(200) := 'INSERT INTO AP_undo_event_log('
||'EVENT_ID,E2,E3,STATUS,INVOICE_ID,CHECK_ID, BUG_ID) '
||'VALUES(:1, :2, :3, :4, :5, :6, :7)';
log_table_exists_stmt VARCHAR2(200) := 'select count(*) from '||l_table_name
||' where table_name = ''AP_UNDO_EVENT_LOG'' ';
SELECT COUNT( 1 )
INTO l_rel_act_acct_event_cnt
FROM ap_invoice_distributions_all aid
, xla_events xe
WHERE aid.invoice_id = p_source_id
AND aid.bc_event_id = Events_to_Process_tab( i ).event_id
AND xe.event_id = aid.accounting_event_id
AND xe.event_status_code = 'P'
AND xe.application_id = 200;
Debug_Info := 'xla_DataFixes_Pub.delete_journal_entries';
l_log_msg := 'Calling xla_datafixes_pub.delete_journal_entries';
xla_datafixes_pub.delete_journal_entries
( p_api_version => l_aPi_Version
, p_init_msg_list => l_InIt_msg_List
, p_application_id => l_Application_Id
, p_event_id => Events_to_Process_tab( i ).Event_Id
, x_return_status => l_Return_Status
, x_msg_count => x_msg_Count
, x_msg_data => x_msg_Data );
l_log_msg := 'Undo_Accounting : Error in xla_DataFixes_Pub.delete_journal_entries:'|| x_msg_Data;
SELECT event_status_code
INTO l_event_status_code
FROM xla_events xe
WHERE xe.application_id = 200
AND xe.event_id = Events_to_Process_tab( i ).Event_Id;
UPDATE ap_Invoice_Distributions_All Aid
SET Accounting_Date = l_gl_Date
, Posted_Flag = 'N'
, Accrual_Posted_Flag = 'N'
, Last_Updated_By = fnd_Global.User_Id
, Period_Name = l_Period_Name
WHERE Accounting_Event_Id = Events_to_Process_tab( i ).Event_Id
AND Invoice_Id = l_Source_Id;
UPDATE ap_self_assessed_tax_dist_all asatd
SET Accounting_Date = l_gl_Date
, Posted_Flag = 'N'
, Accrual_Posted_Flag = 'N'
, Last_Updated_By = fnd_Global.User_Id
, Period_Name = l_Period_Name
WHERE Accounting_Event_Id = Events_to_Process_tab( i ).Event_Id
AND Invoice_Id = l_Source_Id;
UPDATE xla_Events
SET Event_Date = l_gl_Date
WHERE Event_Id = Events_to_Process_tab( i ).Event_Id
AND application_id = 200;
UPDATE ap_prepay_history_all aph
SET Accounting_Date = l_gl_Date
, Posted_Flag = 'N'
, Last_Updated_By = fnd_Global.User_Id
WHERE Accounting_Event_Id = Events_to_Process_tab( i ).Event_Id
AND Invoice_Id = l_Source_Id;
DELETE FROM ap_prepay_app_dists
WHERE PREPAY_HISTORY_ID IN
(SELECT PREPAY_HISTORY_ID
FROM ap_prepay_history_all
WHERE Accounting_Event_Id = Events_to_Process_tab( i ).Event_Id
AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
AND Invoice_Id = l_Source_Id);
l_log_msg := 'Updated Transaction tables for Invoice';
DELETE gl_bc_packets
WHERE event_id = events_to_process_tab(i).event_id;
UPDATE gms_award_distributions
SET fc_status = 'N'
, last_updated_by = fnd_Global.User_Id
WHERE invoice_distribution_id IN
(SELECT aid.invoice_distribution_id
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = events_to_process_tab(i).event_id
AND aid.invoice_id = p_source_id)
AND fc_status = 'A';
UPDATE ap_invoice_distributions_all aid
SET encumbered_flag = 'N'
, bc_event_id = NULL
, match_status_flag = 'T'
, last_updated_by = fnd_Global.User_Id
WHERE aid.bc_event_id = events_to_process_tab(i).event_id
AND aid.invoice_id = p_source_id;
UPDATE ap_self_assessed_tax_dist_all aid
SET encumbered_flag = 'N'
, bc_event_id = NULL
, match_status_flag = 'T'
, last_updated_by = fnd_Global.User_Id
WHERE aid.bc_event_id = events_to_process_tab(i).event_id
AND aid.invoice_id = p_source_id;
UPDATE ap_prepay_history_all apph
SET bc_event_id = NULL
, last_updated_by = fnd_Global.User_Id
WHERE apph.bc_event_id = events_to_process_tab(i).event_id
AND apph.invoice_id = p_source_id;
UPDATE ap_prepay_app_dists
SET bc_event_id = NULL
, last_updated_by = fnd_Global.User_Id
WHERE bc_event_id = events_to_process_tab(i).event_id
AND prepay_history_id IN
(SELECT apph.prepay_history_id
FROM ap_prepay_history_all apph
WHERE apph.invoice_id = p_source_id);
UPDATE ap_invoices_all
SET cancelled_date = NULL
, last_updated_by = fnd_Global.User_Id
WHERE invoice_id = p_source_id
AND cancelled_date IS NOT NULL
AND events_to_process_tab(i).event_type_code IN
('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
'DEBIT MEMO CANCELLED', 'CREDIT MEMO CANCELLED');
ap_accounting_events_pkg.update_invoice_events_status(p_source_id ,l_calling_sequence );
l_log_msg := 'Updated Transaction tables for Invoice';
SELECT NVL((SUM(AID.Amount)), 0) --bug12764043, removed ABS
INTO l_total_prepay_amt
FROM ap_invoice_distributions_all aid
WHERE aid.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
AND aid.prepay_distribution_id IS NOT NULL
AND aid.accounting_event_id = P_Event_ID;
SELECT NVL((sum(nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0))), 0) --bug12764043, removed ABS
into l_total_prepay_acct
FROM xla_ae_lines xal,
xla_ae_headers xah
WHERE xal.application_id = 200
AND xah.application_id = 200
AND xah.balance_type_code = 'A' --bug12654609
AND xah.ae_header_id = xal.ae_header_id
AND xal.accounting_class_code = 'PREPAID_EXPENSE'
AND xah.event_id = P_Event_ID
AND xah.ledger_id = P_Ledger_ID;
SELECT NVL((sum(nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0))), 0) --bug12764043, removed ABS
into l_total_prepay_acct
FROM xla_ae_lines xal,
xla_ae_headers xah
WHERE xal.application_id = 200
AND xah.application_id = 200
AND xah.balance_type_code = 'A' --bug12654609
AND xah.ae_header_id = xal.ae_header_id
AND xal.accounting_class_code = 'PREPAID_EXPENSE'
AND xah.event_id IN
(SELECT /*+ push_subq */
apph.accounting_event_id
FROM ap_prepay_history_all apph
WHERE apph.related_prepay_app_event_id = p_event_id
AND apph.transaction_type = 'PREPAYMENT APPLICATION ADJ')
AND xah.ledger_id = P_Ledger_ID;
UPDATE po_headers_all
SET vendor_contact_id = new_vendor_contact_id
WHERE vendor_contact_id = old_vendor_contact_id;
UPDATE po_headers_archive_all
SET vendor_contact_id = new_vendor_contact_id
WHERE vendor_contact_id = old_vendor_contact_id;
UPDATE po_rfq_vendors
SET vendor_contact_id = new_vendor_contact_id
WHERE vendor_contact_id = old_vendor_contact_id;
UPDATE po_vendor_list_entries
SET vendor_contact_id = new_vendor_contact_id
WHERE vendor_contact_id = old_vendor_contact_id;
UPDATE po_requisition_lines_all
SET vendor_contact_id = new_vendor_contact_id
WHERE vendor_contact_id = old_vendor_contact_id;
UPDATE po_reqexpress_lines_all
SET suggested_vendor_contact_id = new_vendor_contact_id
WHERE suggested_vendor_contact_id = old_vendor_contact_id;
USING (SELECT DISTINCT poh.po_header_id,
tmp.new_vendor_contact_id,
tmp.current_vendor_contact_id
FROM '|| p_driver_table || ' tmp,'||
'po_headers_all poh
WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
and tmp.process_flag = ''Y''
) a
on (poh.po_header_id = a.po_header_id)
WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
USING (SELECT DISTINCT poh.po_header_id,
poh.revision_num,
tmp.new_vendor_contact_id,
tmp.current_vendor_contact_id
FROM '|| p_driver_table || ' tmp,'||
'po_headers_archive_all poh
WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
and tmp.process_flag = ''Y''
) a
on (poh.po_header_id = a.po_header_id and poh.revision_num = a.revision_num)
WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
USING (SELECT DISTINCT poh.po_header_id,
poh.sequence_num,
tmp.new_vendor_contact_id,
tmp.current_vendor_contact_id
FROM '|| p_driver_table || ' tmp,'||
'po_rfq_vendors poh
WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
AND tmp.process_flag = ''Y''
) a
on (poh.po_header_id = a.po_header_id and poh.sequence_num = a.sequence_num)
WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
USING (SELECT DISTINCT poh.vendor_list_entry_id,
tmp.new_vendor_contact_id,
tmp.current_vendor_contact_id
FROM '|| p_driver_table || ' tmp,'||
'po_vendor_list_entries poh
WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
AND tmp.process_flag = ''Y''
) a
on (poh.vendor_list_entry_id = a.vendor_list_entry_id)
WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
USING (SELECT DISTINCT poh.requisition_line_id,
tmp.new_vendor_contact_id,
tmp.current_vendor_contact_id
FROM '|| p_driver_table || ' tmp,'||
'po_requisition_lines_all poh
WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
AND tmp.process_flag = ''Y''
) a
on (poh.requisition_line_id = a.requisition_line_id)
WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
USING (SELECT distinct
poh.rowid row_id, --bug13863902
poh.express_name,
poh.sequence_num,
tmp.new_vendor_contact_id,
tmp.current_vendor_contact_id
FROM '|| p_driver_table || ' tmp,'||
'po_reqexpress_lines_all poh
WHERE poh.suggested_vendor_contact_id = tmp.current_vendor_contact_id
and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
AND tmp.process_flag = ''Y''
) a
on (poh.express_name = a.express_name and poh.sequence_num = a.sequence_num and poh.rowid = a.row_id)
WHEN MATCHED THEN UPDATE SET poh.suggested_vendor_contact_id = a.new_vendor_contact_id';