/*API for Un-Accounting AR*/
CREATE OR replace PROCEDURE Xx_unaccounting_gl (org_id IN NUMBER,gl_date IN DATE,
cm_trx_id IN NUMBER,
event_id IN NUMBER)
AS
l_user_id NUMBER := 9999;
l_responsibility_id NUMBER := 99999;
l_org_id NUMBER := org_id;
l_gl_date DATE := gl_date; -- to_date(p_gl_date,'DD-MM-YYYY');
l_customer_trx_id NUMBER := cm_trx_id;
l_event_id NUMBER := event_id;
l_read_only_mode VARCHAR2(1) := 'N';
l_bug_number NUMBER := 8568656;
p_api_version NUMBER;
p_event_id NUMBER;
p_init_msg_list VARCHAR2(300);
p_application_id INTEGER;
p_reversal_method VARCHAR2(300);
p_gl_date DATE;
p_post_to_gl_flag VARCHAR2(300);
x_return_status VARCHAR2(300);
x_msg_count NUMBER;
x_msg_data VARCHAR2(4000);
x_rev_ae_header_id INTEGER;
x_rev_event_id INTEGER;
x_rev_entity_id INTEGER;
x_new_event_id INTEGER;
x_new_entity_id INTEGER;
CURSOR c_transactions IS
SELECT DISTINCT ct.customer_trx_id,
gld.event_id,
gld.gl_date,
xah.gl_transfer_status_code
FROM ra_customer_trx_all ct,
ra_cust_trx_line_gl_dist_all gld,
xla_events xe,
xla_ae_headers xah
WHERE ct.customer_trx_id = l_customer_trx_id
AND ct.customer_trx_id = gld.customer_trx_id
AND gld.event_id = xe.event_id
AND gld.account_set_flag = 'N'
AND xe.application_id = 222
AND xah.event_id = xe.event_id
AND xe.event_id = l_event_id
AND xah.application_id = 222;
PROCEDURE Backup_table_gldist
IS
l_create_bk_gld VARCHAR2(500);
BEGIN
l_create_bk_gld := 'create table temp_gld_bk_'
||l_bug_number
||' as select * from ra_cust_trx_line_gl_dist_all where 1=2';
EXECUTE IMMEDIATE l_create_bk_gld;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
NULL;
ELSE
RAISE;
END IF;
END backup_table_gldist;
PROCEDURE Backup_table_xla
IS
l_create_bk_xla VARCHAR2(500);
BEGIN
l_create_bk_xla := 'create table temp_xla_bk_'
||l_bug_number
||' as select * from xla_events where 1=2';
EXECUTE IMMEDIATE l_create_bk_xla;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
NULL;
ELSE
RAISE;
END IF;
END backup_table_xla;
PROCEDURE Insert_into_backup_gldist(p_trx_id NUMBER)
IS
l_insert_gldist VARCHAR2(500);
BEGIN
l_insert_gldist := 'insert into temp_gld_bk_'
||l_bug_number
|| '( select * from ra_cust_trx_line_gl_dist_all where customer_trx_id = '
||p_trx_id
||')';
EXECUTE IMMEDIATE l_insert_gldist;
END;
PROCEDURE Insert_into_backup_xla(p_ev_id NUMBER)
IS
l_insert_xla VARCHAR2(500);
BEGIN
l_insert_xla := 'insert into temp_xla_bk_'
||l_bug_number
|| '( select * from xla_events where event_id = '
||p_ev_id
||')';
EXECUTE IMMEDIATE l_insert_xla;
END insert_into_backup_xla;
PROCEDURE Debug(s VARCHAR2)
IS
BEGIN
dbms_output.Put_line(s);
END;
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;
BEGIN
fnd_global.Apps_initialize(l_user_id, l_responsibility_id, 222);
mo_global.Init('AR');
mo_global.Set_policy_context('S', l_org_id);
IF Nvl(Upper(l_read_only_mode), 'Y') = 'N' THEN
backup_table_gldist;
backup_table_xla;
END IF;
Debug('CTX_ID Event_Id ');
Debug('-------------- ------------------');
FOR rec IN c_transactions LOOP
Debug(rec.customer_trx_id
||' '
||rec.event_id);
IF Nvl(Upper(l_read_only_mode), 'Y') = 'N' THEN
Insert_into_backup_gldist(rec.customer_trx_id);
Insert_into_backup_xla(rec.event_id);
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_application_id := 222;
p_event_id := rec.event_id; -- event that needs to be reversed.
p_reversal_method := 'SIDE';
p_post_to_gl_flag := 'N';
/* p_gl_date := trunc(sysdate); */
p_gl_date := l_gl_date;
x_return_status := NULL;
dbms_output.Put_line('event_id -'
||p_event_id);
IF Nvl(rec.gl_transfer_status_code, 'N') = 'N' THEN
xla_datafixes_pub.Delete_journal_entries (p_api_version,
p_init_msg_list
,
p_application_id, p_event_id, x_return_status, x_msg_count,
x_msg_data);
ELSE
xla_datafixes_pub.Reverse_journal_entries (p_api_version,
p_init_msg_list,
p_application_id, p_event_id, p_reversal_method, p_gl_date,
p_post_to_gl_flag,
x_return_status, x_msg_count, x_msg_data, x_rev_ae_header_id,
x_rev_event_id
,
x_rev_entity_id, x_new_event_id, x_new_entity_id);
END IF;
dbms_output.Put_line('status -'
||x_return_status);
dbms_output.Put_line('x_rev_ae_header_id -'
||x_rev_ae_header_id);
dbms_output.Put_line('x_rev_event_id -'
||x_rev_event_id); -- this is E2
dbms_output.Put_line('x_new_event_id -'
||x_new_event_id); -- this is E3
dbms_output.Put_line('x_new_entity_id -'
||x_new_entity_id);
dbms_output.Put_line('x_msg_data -'
||x_msg_data);
IF x_return_status = 'S' THEN
Debug('Updating the AR Tables for customer_trx_id '
||rec.customer_trx_id);
arp_global.g_allow_datafix := TRUE;
UPDATE ra_cust_trx_line_gl_dist_all
SET posting_control_id = -3,
gl_posted_date = NULL,
gl_date = l_gl_date
WHERE customer_trx_id = rec.customer_trx_id
AND account_set_flag = 'N'
AND event_id = rec.event_id
AND posting_control_id <> -3;
UPDATE xla_events
SET event_date = l_gl_date
WHERE event_id = rec.event_id;
arp_global.g_allow_datafix := FALSE;
Debug('Updated the AR Tables for customer_trx_id '
||rec.customer_trx_id);
END IF;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
