REM $Header: xla_mulent_fix.sql 120.0.12010000.3 2010/05/12 08:03:06 nmsubram noship $ REM +==========================================================================+ REM +==========================================================================+ REM | FILENAME | REM | xla_mulent_fix.sql | REM | | REM | DESCRIPTION : This script will fix all the corrupted records identified | REM | by xla_mulent_sel.sql. | REM | This script has to be run only after xla_mulent_sel.sql. | REM | | REM | Execute command line : | REM | sqlplus /@ @xla_mulent_fix.sql | REM | | REM | HISTORY Created by William Green | REM +==========================================================================+ REM dbdrv: none SET SERVEROUTPUT ON SET VERIFY OFF --WHENEVER SQLERROR EXIT FAILURE ROLLBACK; --WHENEVER OSERROR EXIT FAILURE ROLLBACK; DECLARE l_count NUMBER; l_count1 NUMBER; l_count2 NUMBER; l_file_location v$parameter.value%TYPE; l_message VARCHAR2(10000); l_aph_col_str VARCHAR2(10000); l_aph_where_str VARCHAR2(10000); BEGIN XLA_GLOBAL_DATA_FIX_PKG.Open_Log_Out_Files (9651687||'-fix',l_file_location); XLA_GLOBAL_DATA_FIX_PKG.Print(''); ------------------------------- -- Step 1: Taking Backup-- ------------------------------- l_message := 'Running Step 1'; FND_File.Put_Line(fnd_file.log,l_message); begin l_message := 'Creating Backup Tables '; FND_File.Put_Line(fnd_file.log,l_message); -- backup of XLA_TRANSACTION_ENTITIES l_message := 'Creating BACKUP TABLE FOR XLA_TRANSACTION_ENTITIES'; FND_File.Put_Line(fnd_file.log,l_message); l_aph_where_str := 'where (entity_id, application_id) in (select entity_id, application_id from xte_multiple_9651687)'; XLA_GLOBAL_DATA_FIX_PKG.get_cols('XLA_TRANSACTION_ENTITIES', l_aph_col_str); XLA_GLOBAL_DATA_FIX_PKG.Backup_data('xla_transaction_entities_upg', 'XTE_9651687_BKP', l_aph_col_str, l_aph_where_str, 'xla_mulent_fix.sql'); l_message := 'XLA_TRANSACTION_ENTITIES backup table created successfully '; FND_File.Put_Line(fnd_file.log,l_message); -- backup XLA_EVENTS l_message := 'Creating xla_events Backup Table '; FND_File.Put_Line(fnd_file.log,l_message); l_aph_col_str := ' '; l_aph_where_str := ' '; l_aph_where_str := 'where (entity_id, application_id) in (select entity_id, application_id from xte_multiple_9651687)'; XLA_GLOBAL_DATA_FIX_PKG.get_cols('XLA_EVENTS', l_aph_col_str); XLA_GLOBAL_DATA_FIX_PKG.Backup_data('XLA_EVENTS', 'XE_9651687_BKP', l_aph_col_str, l_aph_where_str, 'xla_mulent_fix.sql'); l_message := 'xla_events Backup Table created successfully'; FND_File.Put_Line(fnd_file.log,l_message); --backup of XLA_AE_HEADERS l_message := 'Creating xla_ae_headers Backup Table '; FND_File.Put_Line(fnd_file.log,l_message); l_aph_col_str := ' '; l_aph_where_str := ' '; l_aph_where_str := 'where (entity_id, application_id) in (select entity_id, application_id from xte_multiple_9651687)'; XLA_GLOBAL_DATA_FIX_PKG.get_cols('XLA_AE_HEADERS', l_aph_col_str); XLA_GLOBAL_DATA_FIX_PKG.Backup_data('XLA_AE_HEADERS', 'XAH_9651687_BKP', l_aph_col_str, l_aph_where_str, 'xla_mulent_fix.sql'); l_message := 'xla_ae_headers Backup Table created successfully '; FND_File.Put_Line(fnd_file.log,l_message); --backup of XLA_AE_LINES l_message := 'Creating xla_Ae_lines backup table '; FND_File.Put_Line(fnd_file.log,l_message); l_aph_col_str := ' '; l_aph_where_str := ' '; l_aph_where_str := 'where (AE_HEADER_ID, application_id) in (select ae_header_id, application_id from XLA_AE_HEADERS'|| ' where (entity_id, application_id) in (select entity_id, application_id from xte_multiple_9651687))'; XLA_GLOBAL_DATA_FIX_PKG.get_cols('XLA_AE_LINES', l_aph_col_str); XLA_GLOBAL_DATA_FIX_PKG.Backup_data('XLA_AE_LINES', 'XAL_9651687_BKP', l_aph_col_str, l_aph_where_str, 'xla_mulent_fix.sql'); l_message := 'xla_ae_lines Backup Table created successfully '; FND_File.Put_Line(fnd_file.log,l_message); --backup of XLA_DISTRIBUTION_LINKS l_message := 'Creating xla_distribution Backup Table '; FND_File.Put_Line(fnd_file.log,l_message); l_aph_col_str := ' '; l_aph_where_str := ' '; l_aph_where_str := 'where (AE_HEADER_ID, application_id) in (select ae_header_id, application_id from XLA_AE_HEADERS'|| ' WHERE(entity_id, application_id) in (select entity_id, application_id from xte_multiple_9651687))'; XLA_GLOBAL_DATA_FIX_PKG.get_cols('XLA_DISTRIBUTION_LINKS', l_aph_col_str); XLA_GLOBAL_DATA_FIX_PKG.Backup_data('XLA_DISTRIBUTION_LINKS', 'XDL_9651687_BKP', l_aph_col_str, l_aph_where_str, 'xla_mulent_fix.sql'); l_message := 'xla_distribution_links Backup Tables created successfully'; FND_File.Put_Line(fnd_file.log,l_message); Exception when others then l_message := 'Exception occured When '||l_message||' :: '||SQLERRM; XLA_GLOBAL_DATA_FIX_PKG.print(l_message); FND_File.Put_Line(fnd_file.log,l_message); APP_EXCEPTION.RAISE_EXCEPTION; End; ----------------------------------------------- -- Step 2: Datafix to correct corrupted records ----------------------------------------------- l_message := 'Running Step 2: Datafix to correct the corrupted records'; FND_File.Put_Line(fnd_file.log,l_message); begin FOR j IN ( select bk.entity_id , bk.application_id , bk.ledger_id from xte_multiple_9651687 bk where 1 = 1 and bk.event_count = 0 ) LOOP Update xla_transaction_entities_upg xte set xte.entity_code = 'MANUAL', xte.last_update_login = '-169' where xte.application_id = j.application_id and xte.ledger_id = j.ledger_id and xte.entity_id = j.entity_id ; END LOOP; FOR i IN ( select bk.application_id , bk.ledger_id , bk.source_id_int_1 , bk.entity_code , bk.entity_id , dat.min_entity_id from xte_multiple_9651687 bk , ( select min(bk2.entity_id) min_entity_id , bk2.application_id , bk2.ledger_id , bk2.source_id_int_1 , bk2.entity_code from xte_multiple_9651687 bk2 where 1 = 1 and bk2.event_count >0 group by bk2.application_id , bk2.ledger_id , bk2.source_id_int_1 , bk2.entity_code ) dat where bk.application_id = dat.application_id and bk.ledger_id = dat.ledger_id and NVL(bk.source_id_int_1 , -99) = NVL(dat.source_id_int_1 , -99) and bk.entity_code = dat.entity_code and bk.event_count >0 and bk.entity_id <> dat.min_entity_id order by bk.entity_id ) loop FOR j IN (select xe.event_id from xla_events xe where xe.application_id = i.application_id and xe.entity_id = i.entity_id -- and xe.event_status_code <> 'Z' order by xe.event_number ) LOOP update xla_events xe set xe.entity_id = i.min_entity_id , xe.last_update_login = 9651687 , xe.event_number = NVL( ( select max(xe2.event_number) + 1 from xla_events xe2 where xe2.application_id = i.application_id AND xe2.entity_id in (i.min_entity_id)) , 1 ) where 1 =1 -- and xe.event_status_code <>'Z' and xe.application_id = i.application_id and xe.entity_id = i.entity_id and xe.event_id = j.event_id ; END LOOP; update xla_ae_headers xah set xah.entity_id = i.min_entity_id where xah.application_id = i.application_id and xah.entity_id = i.entity_id; Update xla_transaction_entities_upg xte set xte.entity_code = 'MANUAL', xte.last_update_login = '-169' where xte.application_id = i.application_id and xte.ledger_id = i.ledger_id and xte.entity_id = i.entity_id ; IF ( i.entity_code = 'AP_INVOICES' ) THEN -- Fix to Update the Applied To Entity of XDL of Invoice/Payment's accounting so that -- Trial Balance etc are corrected. UPDATE /*+ bypass_ujvc */ ( select xdl.applied_to_entity_id , xdl.applied_to_entity_code , xdl.applied_to_source_id_num_1 , i.min_entity_id from xla_transaction_entities_upg xte , xla_ae_headers xah , xla_distribution_links xdl where xah.application_id = xte.application_id and xah.entity_id = xte.entity_id and xah.application_id = xdl.application_id and xah.ae_header_id = xdl.ae_header_id and xdl.applied_to_entity_id IS NOT NULL and xdl.applied_to_entity_code = 'AP_INVOICES' and xdl.applied_to_source_id_num_1 = i.source_id_int_1 and xdl.applied_to_entity_id <> i.min_entity_id and xte.application_id = i.application_id and xte.ledger_id = i.ledger_id and (xte.entity_code , NVL(xte.source_id_int_1 , -99) ) IN ( select DISTINCT 'AP_INVOICES' entity_code , invdist.invoice_id source_id_int_1 from ap_invoices_all prepay , ap_invoice_distributions_all prepay_dist, ap_invoice_distributions_all invdist where prepay.invoice_type_lookup_code = 'PREPAYMENT' and prepay.invoice_id = i.source_id_int_1 and prepay.invoice_id = prepay_dist.invoice_id and prepay_dist.invoice_distribution_id = invdist.prepay_distribution_id UNION select DISTINCT 'AP_PAYMENTS' entity_code , invpay.check_id source_id_int_1 from ap_invoice_payments_all invpay where invpay.invoice_id = i.source_id_int_1 UNION select DISTINCT 'AP_INVOICES' entity_code , inv.invoice_id source_id_int_1 from ap_invoices_all inv where inv.invoice_id = i.source_id_int_1 ) ) v SET v.applied_to_entity_id = v.min_entity_id WHERE v.applied_to_source_id_num_1 = i.source_id_int_1 AND v.applied_to_entity_code = i.entity_code AND v.applied_to_entity_id <> v.min_entity_id ; ELSIF (i.entity_code = 'AP_PAYMENTS') THEN UPDATE /*+ bypass_ujvc */ ( select xdl.applied_to_entity_id , xdl.applied_to_entity_code , xdl.applied_to_source_id_num_1 , i.min_entity_id from xla_ae_headers xah , xla_distribution_links xdl where xah.application_id = I.APPLICATION_ID and xah.entity_id = I.MIN_ENTITY_ID and xah.application_id = xdl.application_id and xah.ae_header_id = xdl.ae_header_id and xdl.applied_to_entity_id IS NOT NULL and xdl.applied_to_entity_code = 'AP_PAYMENTS' and xdl.applied_to_source_id_num_1 = I.SOURCE_ID_INT_1 and xdl.applied_to_entity_id <> I.MIN_ENTITY_ID ) v SET v.applied_to_entity_id = v.min_entity_id WHERE v.applied_to_source_id_num_1 = i.source_id_int_1 AND v.applied_to_entity_code = i.entity_code AND v.applied_to_entity_id <> v.min_entity_id ; END IF; end loop; EXECUTE IMMEDIATE 'select count (*) from (select xte.application_id , xte.ledger_id , xte.entity_code , xte.source_id_int_1 from xla_transaction_entities_upg xte , xte_multiple_9651687 bk where xte.application_id =200 and xte.ledger_id = bk.ledger_id and xte.entity_code <> ''MANUAL'' and xte.entity_id = bk.entity_id and xte.application_id = bk.application_id group by xte.application_id , xte.source_id_int_1 , xte.entity_code , xte.ledger_id having count(1) > 1)' into l_count; if l_count = 0 then commit; l_message := 'Commit Executed Successfully'; FND_File.Put_Line(fnd_file.log,l_message); EXECUTE IMMEDIATE 'SELECT count(*) FROM xdl_multiple_9651687 bk WHERE EXISTS ( SELECT 1 FROM xla_ae_lines xal WHERE xal.application_id = 200 AND xal.ae_header_id = bk.ae_header_id AND xal.ae_line_num = bk.ae_line_num AND xal.accounting_class_code = ''LIABILITY'' AND xal.upg_batch_id IS NULL )' into l_count1; if l_count1 > 0 then EXECUTE IMMEDIATE 'select count(*) from xla_tb_definitions_vl where ledger_id IN (SELECT ledger_id from xte_multiple_9651687)' into l_count2; if l_count2 > 0 then begin l_message := 'Please Re-Build Trial balance for the below DEFINITION_CODE AND LEDGER_ID'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); FND_File.Put_Line(fnd_file.log,l_message); XLA_GLOBAL_DATA_FIX_PKG.Print_html_table ('DEFINITION_CODE,NAME,DESCRIPTION,LEDGER_ID', 'XLA_TB_DEFINITIONS_VL', 'where ledger_id IN (SELECT ledger_id from xte_multiple_9651687)', 'xla_mulent_fix.sql'); l_message := 'Steps below to Rebuild Trial Balance :'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'a) Go to Open Account Balances Listing Definitions under Payables Responsibility'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'b) Search for the Trial balance Definition Code(which customer is using for reconciliation)'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'c) Click on Update (Pen Like Icon).'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'd) Click on Apply.'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'This would spawn Open Account Balances Data manager concurrent Request which '|| 'can be seen from the Concurrent Request window.'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); EXCEPTION WHEN OTHERS THEN l_message := 'EXCEPTION :: '||SQLERRM || 'in call to XLA_GLOBAL_DATA_FIX_PKG.Print_html_table '|| 'during printing data from XLA_TB_DEFINITIONS_VL'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); FND_File.Put_Line(fnd_file.log,l_message); end; end if; end if; else ROLLBACK; l_message := 'ROLLBACK Issued.'; XLA_GLOBAL_DATA_FIX_PKG.print(l_message); FND_File.Put_Line(fnd_file.log,l_message); end if; exception when others then l_message := 'Exception when fixing the error '||l_message||' :: '||SQLERRM; XLA_GLOBAL_DATA_FIX_PKG.print(l_message); FND_File.Put_Line(fnd_file.log,l_message); rollback; l_message := 'Rollback executed......'; XLA_GLOBAL_DATA_FIX_PKG.print(l_message); FND_File.Put_Line(fnd_file.log,l_message); APP_EXCEPTION.RAISE_EXCEPTION; end; ------------------------------------------------------------ -- Step 3:Report on Backup tables created-- ------------------------------------------------------------- l_message := 'Running Step 3'; FND_File.Put_Line(fnd_file.log,l_message); l_message := '_______________________________________'|| '_______________________________________'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'Following is the list of backup table containing records affected by this GDF patch

'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'XTE_9651687_BKP for '|| 'XLA_TRANSACTION_ENTITIES

'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'XE_9651687_BKP for '|| 'XLA_EVENTS

'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'XAH_9651687_BKP for '|| 'XLA_AE_HEADERS

'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'XAL_9651687_BKP for '|| 'XLA_AE_LINES

'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'XDL_9651687_BKP for '|| 'XLA_DISTRIBUTION_LINKS

'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := '_______________________________________'|| '_______________________________________'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := 'If corruption is not resolved completly, please log a Service Request with Oracle Support along with the information below.'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); l_message := ' 1. Kindly attach the log file and output file of this script. The file names would be listed in File Information section of this report.

2. Provide us the output for the following queries in EXCEL sheet format only:

a) select * from xte_multiple_9651687;

b) select * from xdl_multiple_9651687;

c) select * from xla_transaction_entities_upg where entity_id in (select entity_id from xte_multiple_9651687);

d) select * from xla_events where entity_id in (select entity_id from xte_multiple_9651687);

e) select * from xla_ae_headers where entity_id in (select entity_id from xte_multiple_9651687);

f) select * from xla_aE_lines where (ae_header_id, application_id) in (select ae_header_id, application_id from xla_ae_headers where entity_id in (select entity_id from xte_multiple_9651687));

g) select * from xla_distribution_links where (ae_header_id, application_id) in (select ae_header_id, application_id from xla_ae_headers where entity_id in (select entity_id from xte_multiple_9651687));'; XLA_GLOBAL_DATA_FIX_PKG.Print(l_message); -------------------------------------------- -- Step 5: Close files-- ---------------------------------------------- XLA_GLOBAL_DATA_FIX_PKG.Print(''); XLA_GLOBAL_DATA_FIX_PKG.Close_Log_Out_Files; dbms_output.put_line('--------------------------------------------------'|| '-----------------------------'); dbms_output.put_line('Please Note the Following information. Kindly open the Out file to proceed further '); dbms_output.put_line(XLA_GLOBAL_DATA_FIX_PKG.G_out_file_info); dbms_output.put_line(''); dbms_output.put_line(XLA_GLOBAL_DATA_FIX_PKG.G_log_file_info); dbms_output.put_line('--------------------------------------------------'|| '-----------------------------'); exception when others then l_message := 'Exception occured :: '||SQLERRM; XLA_GLOBAL_DATA_FIX_PKG.print(l_message); FND_File.Put_Line(fnd_file.log,l_message); dbms_output.put_line(l_message); APP_EXCEPTION.RAISE_EXCEPTION; end; /