AMRESH KUMARAN ORACLE APPLICATIONS: AR TO XLA

SCRIPTS IS TO DELETE DATA FROM AR TO XLA

/* Formatted on 2015/07/08 15:58 (Formatter Plus v4.8.8) */
/*============================================================================+
$Header: fix_del_trx_r12.sql 120.0 2013/04/05 11:12:11 kyennawa noship $
============================================================================+*/
REM +================================================================================+
REM | |
REM | # Bug No : 9692558 |
REM | |
REM | # RCA Bug : xxxxxxx |
REM | |
REM | # Issue : gsi: fr (org id -38). provide script for deleting a invoice |
REM | |
REM | |
REM | # Symptoms : Need to delete invalid trx. |
REM | Trx is unposted and applications against are also unposted |
REM | |
REM | # Fix Approach : Delete trx related tables. |
REM | |
REM | # Usage : Supply the following when prompted: |
REM | 1) Bug_Number : Bug_Number |
REM | 2) org_id : Organization Id |
REM | 3) Read_Only_Mode : Y - TO show the corruption. |
REM | N - TO show and fix the corruption. |
REM | 4) Cust_Trx_Id : 0 - For all receipts |
REM | or a specific receipt_id. |
REM | |
REM *================================================================================*/
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus &phase=sql \
REM dbdrv: checkfile:~PROD:~PATH:~FILE \
REM dbdrv: &un_ar

SPOOL fix_del_trx_r12.lst
SET serveroutput on size 1000000;
SET linesize 400
SET echo off

DECLARE
l_bug_number NUMBER := &bug_number;
l_org_id NUMBER := ('&org_id');
l_read_only_mode VARCHAR2 (1) := UPPER ('&read_only_mode');
l_trx_id NUMBER := ('&cust_trx_id');
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_count NUMBER;
l_min_ra_id NUMBER;
l_posted_ra_id ar_receivable_applications_all.receivable_application_id%TYPE;

CURSOR cur_trx
IS
SELECT DISTINCT ct.customer_trx_id, ps.CLASS
FROM ra_customer_trx ct, ar_payment_schedules ps
WHERE ct.customer_trx_id = ps.customer_trx_id
AND ps.customer_trx_id = l_trx_id
AND ps.CLASS IN ('INV', 'CM', 'DM')
AND NOT EXISTS (
SELECT 1
FROM ra_cust_trx_line_gl_dist gld
WHERE gld.customer_trx_id = ct.customer_trx_id
AND gld.posting_control_id <> -3
AND gld.account_set_flag = 'N')
AND NOT EXISTS (
SELECT 1
FROM ar_receivable_applications ra
WHERE ra.customer_trx_id = ct.customer_trx_id
AND ra.posting_control_id <> -3)
AND NOT EXISTS (
SELECT 1
FROM ar_receivable_applications ra
WHERE ra.customer_trx_id = ct.customer_trx_id
AND ra.posting_control_id <> -3)
ORDER BY ct.customer_trx_id;

CURSOR ard_cur (p_trx_id IN NUMBER)
IS
SELECT line_id, source_id, source_table
FROM ar_distributions
WHERE source_table = 'RA'
AND source_id IN (
SELECT receivable_application_id
FROM ar_receivable_applications
WHERE customer_trx_id = p_trx_id
UNION
SELECT receivable_application_id
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_trx_id)
UNION
SELECT line_id, source_id, source_table
FROM ar_distributions
WHERE source_table = 'RA'
AND source_table_secondary = 'RA'
AND source_id_secondary IN (SELECT receivable_application_id
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_trx_id);

CURSOR ra_cur (p_trx_id NUMBER)
IS
SELECT *
FROM ar_receivable_applications
WHERE customer_trx_id = p_trx_id AND application_type = 'CM'
UNION
SELECT *
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_trx_id AND application_type = 'CM';

/* Print message on screen */
PROCEDURE DEBUG (s VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line (s);
END DEBUG;

/* Print spaces */
FUNCTION print_spaces (n IN NUMBER)
RETURN VARCHAR2
IS
l_return_string VARCHAR2 (100);
BEGIN
SELECT SUBSTR (' ',
1,
n
)
INTO l_return_string
FROM DUAL;

RETURN (l_return_string);
END print_spaces;

/* PS */
/* Backup table ps*/
PROCEDURE bkp_ps
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table ps_bkp_'
|| l_bug_number
|| ' as
select * from ar_payment_schedules_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_ps;

PROCEDURE insert_into_bkp_ps (p_cm_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into ps_bkp_'
|| l_bug_number
|| '( select * from ar_payment_schedules_all
where customer_trx_id = '
|| p_cm_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* ARD */

/* Backup table ard*/
PROCEDURE bkp_ard
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table ard_bkp_'
|| l_bug_number
|| ' as
select * from ar_distributions_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_ard;

PROCEDURE insert_into_bkp_ard (p_line_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into ard_bkp_'
|| l_bug_number
|| '( select * from ar_distributions_all
where line_id = '
|| p_line_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* RA */
/* Backup table ra*/
PROCEDURE bkp_ra
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table ra_bkp_'
|| l_bug_number
|| ' as
select * from ar_receivable_applications_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_ra;

/* Insert into backup ra*/
PROCEDURE insert_into_bkp_ra (p_ra_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into ra_bkp_'
|| l_bug_number
|| '( select * from ar_receivable_applications_all
where receivable_application_id = '
|| p_ra_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* GLD */
/* Backup table gld*/
PROCEDURE bkp_gld
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table gld_bkp_'
|| l_bug_number
|| ' as
select * from ra_cust_trx_line_gl_dist_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_gld;

PROCEDURE insert_into_bkp_gld (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into gld_bkp_'
|| l_bug_number
|| '( select * from ra_cust_trx_line_gl_dist_all
where customer_trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* SALESREP */
/* Backup table salesrep*/
PROCEDURE bkp_salesreps
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table salesrep_bkp_'
|| l_bug_number
|| ' as
select * from ra_cust_trx_line_salesreps_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_salesreps;

PROCEDURE insert_into_bkp_salesrep (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into salesrep_bkp_'
|| l_bug_number
|| '( select * from ra_cust_trx_line_salesreps_all
where customer_trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* LINES */
/* Backup table lines*/
PROCEDURE bkp_lines
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table lines_bkp_'
|| l_bug_number
|| ' as
select * from ra_customer_trx_lines_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_lines;

PROCEDURE insert_into_bkp_lines (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into lines_bkp_'
|| l_bug_number
|| '( select * from ra_customer_trx_lines_all
where customer_trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* TRX */
/* Backup table cm trx*/
PROCEDURE bkp_trx
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table trx_bkp_'
|| l_bug_number
|| ' as
select * from ra_customer_trx_all
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_trx;

PROCEDURE insert_into_bkp_trx (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into trx_bkp_'
|| l_bug_number
|| '( select * from ra_customer_trx_all
where customer_trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

----------------------------
----------------------------
/* XAH */
/* Backup table xah*/
PROCEDURE bkp_xah
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table xah_bkp_'
|| l_bug_number
|| ' as
select * from xla_ae_headers
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_xah;

PROCEDURE insert_into_bkp_xah (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into xah_bkp_'
|| l_bug_number
|| '( select * from xla_ae_headers xe
WHERE xe.application_id = 222
AND xe.entity_id IN ( SELECT xte.entity_id
FROM XLA.XLA_TRANSACTION_ENTITIES xte
WHERE xte.application_id = 222 AND xte.entity_code = ''TRANSACTIONS''
AND xte.source_id_int_1 = '
|| p_key_id
|| '))';

--debug(l_insert);
EXECUTE IMMEDIATE l_insert;
END;

/* XE */
/* Backup table xe*/
PROCEDURE bkp_xe
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table xe_bkp_'
|| l_bug_number
|| ' as
select * from xla_events
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_xe;

PROCEDURE insert_into_bkp_xe (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into xe_bkp_'
|| l_bug_number
|| '( select * from xla_events xe
WHERE xe.application_id = 222
AND xe.entity_id IN ( SELECT xte.entity_id
FROM XLA.XLA_TRANSACTION_ENTITIES xte
WHERE xte.application_id = 222 AND xte.entity_code = ''TRANSACTIONS''
AND xte.source_id_int_1 = '
|| p_key_id
|| '))';

--debug(l_insert);
EXECUTE IMMEDIATE l_insert;
END;

/* XTE */
/* Backup table cr*/
PROCEDURE bkp_xte
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table xte_bkp_'
|| l_bug_number
|| ' as
select * from XLA_TRANSACTION_ENTITIES
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_xte;

PROCEDURE insert_into_bkp_xte (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into xte_bkp_'
|| l_bug_number
|| '( select * from XLA_TRANSACTION_ENTITIES xte
WHERE xte.application_id = 222
AND xte.entity_code = ''TRANSACTIONS''
AND xte.source_id_int_1 = '
|| p_key_id
|| ')';

--debug(l_insert);
EXECUTE IMMEDIATE l_insert;
END;

-----------------Tax-------------------------

/* ZL */
PROCEDURE bkp_zl
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table zl_bkp_'
|| l_bug_number
|| ' as
select * from zx_lines
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_zl;

PROCEDURE insert_into_bkp_zl (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into zl_bkp_'
|| l_bug_number
|| '( select * from zx_lines zl
where zl.application_id = 222
AND zl.entity_code = ''TRANSACTIONS''
AND zl.event_class_code in (''INVOICE'',''DEBIT_MEMO'',''CREDIT_MEMO'')
AND zl.trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;

/* ZLD */
PROCEDURE bkp_zld
IS
l_create_bk VARCHAR2 (500);
BEGIN
l_create_bk :=
'create table zld_bkp_'
|| l_bug_number
|| ' as
select * from ZX_LINES_DET_FACTORS
where 1 = 2';

EXECUTE IMMEDIATE l_create_bk;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -955
THEN
NULL;
ELSE
RAISE;
END IF;
END bkp_zld;

PROCEDURE insert_into_bkp_zld (p_key_id NUMBER)
IS
l_insert VARCHAR2 (500);
BEGIN
l_insert :=
'insert into zld_bkp_'
|| l_bug_number
|| '( select * from ZX_LINES_DET_FACTORS zld
where zld.application_id = 222
AND zld.entity_code = ''TRANSACTIONS''
AND zld.event_class_code in (''INVOICE'',''DEBIT_MEMO'',''CREDIT_MEMO'')
AND zld.trx_id = '
|| p_key_id
|| ')';

EXECUTE IMMEDIATE l_insert;
END;
BEGIN
mo_global.init ('AR');
mo_global.set_policy_context ('S', l_org_id);

IF NVL (UPPER (l_read_only_mode), 'Y') = 'N'
THEN
bkp_trx;
bkp_lines;
bkp_salesreps;
bkp_gld;
bkp_ra;
bkp_ard;
bkp_ps;
bkp_xte;
bkp_xe;
bkp_xah;
bkp_zl;
bkp_zld;
END IF;

DEBUG ('CUST_TRX_ID TRX_TYPE ');
DEBUG ('=============== =============== ');

FOR c2 IN cur_trx
LOOP
--debug('11');
DEBUG ( c2.customer_trx_id
|| print_spaces (16 - LENGTH (c2.customer_trx_id))
|| c2.CLASS
);

IF NVL (UPPER (l_read_only_mode), 'Y') = 'N'
THEN
/* PS */
insert_into_bkp_ps (c2.customer_trx_id);

DELETE FROM ar_payment_schedules
WHERE customer_trx_id = c2.customer_trx_id;

DEBUG ('');

/* ARD */
FOR ard IN ard_cur (c2.customer_trx_id)
LOOP
insert_into_bkp_ard (ard.line_id);

DELETE FROM ar_distributions
WHERE line_id = ard.line_id;

DEBUG ('');
END LOOP;

/* RA */
FOR ra IN ra_cur (c2.customer_trx_id)
LOOP
insert_into_bkp_ra (ra.receivable_application_id);

DELETE FROM ar_receivable_applications
WHERE receivable_application_id =
ra.receivable_application_id;

DEBUG ('');
END LOOP;

/* GLD */
insert_into_bkp_gld (c2.customer_trx_id);
arp_global.g_allow_datafix := TRUE;

DELETE FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = c2.customer_trx_id;

DEBUG ('');
arp_global.g_allow_datafix := FALSE;
/* SALESREP */
insert_into_bkp_salesrep (c2.customer_trx_id);

DELETE FROM ra_cust_trx_line_salesreps
WHERE customer_trx_id = c2.customer_trx_id;

/* Tax */
insert_into_bkp_zl (c2.customer_trx_id);

DELETE FROM zx_lines zl
WHERE zl.application_id = 222
AND zl.entity_code = 'TRANSACTIONS'
AND zl.event_class_code IN
('INVOICE', 'DEBIT_MEMO', 'CREDIT_MEMO')
AND zl.trx_id = c2.customer_trx_id;

insert_into_bkp_zld (c2.customer_trx_id);

DELETE FROM zx_lines_det_factors zld
WHERE zld.application_id = 222
AND zld.entity_code = 'TRANSACTIONS'
AND zld.event_class_code IN
('INVOICE', 'DEBIT_MEMO', 'CREDIT_MEMO')
AND zld.trx_id = c2.customer_trx_id;

DEBUG ('Deleted Tax info ...');
/* LINES */
insert_into_bkp_lines (c2.customer_trx_id);

DELETE FROM ra_customer_trx_lines
WHERE customer_trx_id = c2.customer_trx_id;

DEBUG ('');
/* TRX */
insert_into_bkp_trx (c2.customer_trx_id);

DELETE FROM ra_customer_trx
WHERE customer_trx_id = c2.customer_trx_id;

DEBUG ('Deleted data for TRX ' || c2.customer_trx_id);
insert_into_bkp_xah (c2.customer_trx_id);

DELETE FROM xla_ae_headers xe
WHERE xe.application_id = 222
AND xe.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = c2.customer_trx_id);

insert_into_bkp_xe (c2.customer_trx_id);

DELETE FROM xla_events xe
WHERE xe.application_id = 222
AND xe.event_status_code <> 'P'
AND xe.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = c2.customer_trx_id);

insert_into_bkp_xte (c2.customer_trx_id);

DELETE FROM xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = c2.customer_trx_id;

DEBUG ('Deleted xla and event info ...');
END IF;

DEBUG ('.............................. ');
END LOOP;

--debug('============================== ');
IF NVL (UPPER (l_read_only_mode), 'Y') = 'N'
THEN
DEBUG ('============================== ');
DEBUG
('Please, review the data and issue ''commit'' to make the changes permanent.'
);
--debug('Run ''invoice_interface_line_bulk.sql'' for reinserting data into interface tables.');
--debug('Then run Autoinvoice Import Program to reimport the CMs in AR.');
END IF;
EXCEPTION
WHEN OTHERS
THEN
arp_global.g_allow_datafix := FALSE;
ROLLBACK;
RAISE;

END;
/

SPOOL off;

AMRESH KUMARAN ORACLE APPLICATIONS

Wednesday, 8 July 2015

AR TO XLA

No comments:

Post a Comment

Blog Archive