Query To Get User Password in Oracle E-Business Suite Version 12

SELECT usr.user_name,
      APPS.get_pwd.decrypt
          ((SELECT (SELECT APPS.get_pwd.decrypt
                              (APPS.fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (APPS.fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (APPS.fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';

Master Data Template - Oracle EBS

You can check this site

It is very useful template for upload master data that related to modul in Oracle E-Business Suite

Fix Negative Ledger ID

R12: How To Avoid and Fix Corruption in Data Transfer from SLA - Negative Ledger_ID, Not Reached GL, Duplicate in GL (Doc ID 883557.1)
=========================================================================

Applies to:
Oracle Receivables - Version 12.0.0 to 12.0.6 [Release 12.0]
Oracle Purchasing - Version 12.0.0 and later
Oracle Cost Management - Version 12.0.0 and later
Oracle Payables - Version 12.0.0 to 12.0.6 [Release 12.0]
Oracle General Ledger - Version 12.0 and later
Information in this document applies to any platform.
Goal

Release 12 introduced SLA - an architectural layer between subledger and GL. Hence, typically, once data has been accounted properly, data transfer happens from subledger to GL through SLA.

During the transfer, some data corruption issues have been reported on the SLA to GL bridge. These issues may be categorized into below categories :

1. Data with Negative Ledger_id in GL tables.
2. Data Marked as transferred in SLA but not reached GL.
3. Data Marked as Not transferred in SLA, but reached GL.
4. Multiple posting issues.
5. In GL Deleted from SLA (It is NOT applicable for 11i data)
6. Journal Import failing with EP01 error while running Transfer to Journal Entries Program
7. 11i accounted transferred to GL in R12 missing gl sl link id.

The problems can be visible in GL (for example missing journals) but also in subledgers (for example Accrual Balance mismatch between Accrual Reconciliation Report and GL).



Fix

A) To identify and confirm if any such data corruption exists and which category you fall into, please run the SLA GL diagnostics script (see NOTE 878891.1).

This diagnostics can be obtained after applying the following patches. 

for 12.0.x versions: Patch 8513940:R12.XLA.A
for 12.1.x versions: Patch 8765953:R12.XLA.B 

Navigation:

Log into Oracle Diagnostics tools
Select link against Subledger Accounting
Select XLA GL Diagnostics under GL_DIAGNOSTICS link

GL diagnostics has to be run for all the ledgers associated to a primary ledger. The below query would help to identify the ledgers for which the diagnostics has to be run:

SELECT gled.ledger_id as LEDGER_ID 
FROM gl_ledger_relationships glr, gl_ledgers gled 
WHERE glr.primary_ledger_id = &p_ledger_id -- Put primary ledger id here
AND glr.application_id = 101 
AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC') 
AND glr.relationship_type_code = 'SUBLEDGER') 
OR (glr.target_ledger_category_code IN ('PRIMARY') 
AND glr.relationship_type_code = 'NONE')) 
AND glr.target_ledger_id = gled.ledger_id 
AND Nvl(gled.complete_flag,'Y') = 'Y' 
GROUP BY gled.ledger_id;

The diagnostic will prompt for following parameters:

Ledger name (One ledger at a time)
Responsibility name (subledger responsibility)
Start Date and End Date
Source (Journal Entry Source Name)
Application_Id


To get the Application id and corresponding je_source_name, run the query below :

select application_id "Application id" ,je_source_name "Je Source" 
from xla_subledgers;

Please ensure the following before the script is run

Program 'Gather Schema Statistics' is run to avoid any performance issues.
The script is run for the Primary Ledger only.





B) Check that you have installed the following XLA patch to fix the Root Cause:

for 12.0.x versions: Patch 8691650:R12.XLA.A
Fixed file versions:
xlaaptrn.pkb - 120.61.12000000.25
This can be replaced with advantage by patch 9742747:R12.XLA.A: Create Accounting Performance: Consolidated Patch (May 2010)
for 12.1.1 versions: Patch 8691650:R12.XLA.B (fix included in R12.1.2)
Fixed file versions: 

$XLA_TOP
patch/115/sql/xlapurge.pkb - 120.0.12010000.2
patch/115/sql/xlapurge.pkh - 120.0.12010000.2
patch/115/sql/xlaaptrn.pkb - 120.63.12010000.17
patch/115/import/US/xla120cp.ldt - 120.131.12010000.19

Important: Please refer to Document 833986.1. That note has details of patch applications of dependent subledger applications, like Payables (AP), Receivables (AR), Costing (CST/BOM), Projects(PA), Assets (FA) and Globalization (JG). Please apply the necessary patches depending upon which all modules of Oracle Applications you use.


C) In case the diagnostic output identifies any data corruption, please raise a Service Request with Oracle Support, providing the output of the above diagnostics, so that we can provide the Datafix action plan.

*** Customers are requested not to apply any fix without recommendation by Oracle Support. Each fix has a packaged series of steps which can be specific to every customer environment. Applying the fix without executing other related steps may cause damage to existing data. ***

-----------------------------

1.Data with Negative Ledger_id in GL tables.

1.1) Confirm that the recommended patches/file versions are installed.

Run steps 1.2 to 1.3 by providing only the primary ledger_id for which GL diagnostic returned corruption.

1.2) Apply Negative_ledger_id_dfix.txt

1.3) Apply IN_SLA_NOT_IN_GL_dfix.txt

1.4) Run Transfer to GL. It may be possible that the fix IN_SLA_NOT_IN_GL_dfix.txt did not return any rows. Because of this, step 1.4 would not transfer any new data either. In this case, it could be inferred that a corresponding line already exists in GL with a positive ledger id. This can also be verified using the query provided in step 1.7

1.4.1)While running Transfer to Journal Entries Program if journal Imports throws EP01 error, Please go to Step 6 and follow the steps and then resume back to this point and continue with below steps.

1.5) Rebuild Trial balance if subledger is Payables using Note 553484.1

1.6) Run GL diagnostic and log a bug if it still shows corruption.

1.7) After deleting negative ledger id data, to find out GL batches in which this data has been reposted with positive ledger data ,run below query. Note that there is no duplication, negative ledger id batches were deleted and reposted to GL with correct positive ledger id batches.

Select distinct je_batch_id, name 
from gl_je_batches where je_batch_id in 
(select je_batch_id from gl_import_references 
where (gl_sl_link_id, gl_sl_link_table) in 
(select gl_sl_link_id,gl_sl_link_table 
from gl_invalid_jrnls_&P_BUG_NUMBER));



----------------------------

2. Data marked as transferred in SLA but not reached GL.



2.1) Confirm that the recommended patches/file versions are installed.
Run step 2.2 by providing only the Primary ledger_id



2.2) Apply IN_SLA_NOT_IN_GL_dfix.txt


2.3) Run Transfer to GL. 



2.3.1)While running Transfer to Journal Entries Program if journal Imports throws EPO1 error, Please go to Step 6 a and follow the steps and then resume back to this point and continue with below steps

2.4) Rebuild Trial balance if subledger is Payables using Note 553484.1

2.5) Run GL Diagnostic again and log a bug if GL diagnostic still shows corruption.


2.6) To find the GL Batch for which data was originally stuck in SLA tables and is now posted after application of datafix and running Transfer to GL, run below query



select je_batch_id,name from gl_je_batches 
where group_id in 
(select distinct group_id from xla_ae_headers 
where (ae_header_id, ledger_id, entity_id) in 
(select ae_header_id, ledger_id, entity_id 
from xla_not_in_gl_&P_BUG_NUMBER));

-----------------------------

3. Data marked as not transferred in SLA but reached GL.

For cases where only the gl transfer flag is incorrectly set in SLA tables to untransferred but data has reached GL, proceed with below steps.

3.1) Confirm that the recommended patches/file versions are installed before application of the following datafix.

Run step 3.2 only for Primary Ledger_id

3.2) Apply IN_GL_NOT_IN_SLA_dfix.txt

3.3) Rebuild Trial balance if subledger is Payables using Note 553484.1

3.4) Run GL Diagnostics and Log a bug if GL diagnostics still shows corruption.


-----------------------------

4. Multiple Posting Issues



For cases where data from SLA tables has been posted multiple times in GL, follow below steps.

4.1)Confirm that the recommended patches/file versions are installed before application of datafix;

4.2) Apply Dual_posting_dfix.txt 

Run this datafix by providing Primary_ledger_id only. After running this datafix, please open the file provided in the P_File_name parameter and reverse all batches manually displayed under the following prompts

"Reverse following batches in GL" and "Reverse following batches in GL for the secondary ledgers created at Journal Level"

Batches are combinations of duplicate data and correct data (which was posted only once). Datafix asks to reverse all batches in which duplication has been posted and transfer to GL is run to retransfer data from SLA that was reversed in GL as part of datafix.

Do not proceed to 4.3 unless all the batches are reversed.

4.3) Apply IN_SLA_NOT_IN_GL_dfix.txt

4.4) Run Transfer to GL.

4.4.1)While running Transfer to Journal Entries Program if journal Imports throws EPO1 error, Please go to Step 6 and follow the steps and then resume back to this point and continue with below steps.

4.5) Rebuild Trial balance if subledger is Payables using Note 553484.1

4.6) Run GL Diagnostics and log a bug if GL diagnostics still shows corruption.

4.7) All duplicate batches would be reversed. To find out new batches in GL in which data has been reposted, run below query:

SELECT DISTINCT gjb.je_batch_id,
gjb.name ,
gjh.ledger_id
FROM gl_je_batches gjb ,
gl_je_headers gjh
WHERE gjb.je_batch_id IN
(SELECT je_batch_id FROM gl_import_references
WHERE (gl_sl_link_id,gl_sl_link_table) IN
(SELECT gl_sl_link_id,gl_sl_link_table 
FROM xla_dual_&P_BUG_NUMBER) )
AND gjb.je_batch_id=gjh.je_batch_id
AND nvl(gjh.accrual_rev_je_header_id, 0) = 0;



---------------------------

5. In GL Deleted from SLA (It is NOT applicable for 11i data)
For Cases where Data deleted from SLA tables, but exists in GL Tables.
5.1) Confirm that the recommended patches/file versions are installed before application of the following datafix.
5.2)Apply generic_deleted_from_sla_Part1.sql script.
Run this datafix by providing below parameters

p_file_Name ->Output File Name, some name in order to identify customer which file is generated.
p_ledger id ->Primary ledger for which corruption is observed
p_application id ->Application ID (e.g 200-> Payables, 222-> Receivables etc)
p_je_source_name ->"Payables" or "Receivables" etc
If user is not aware then use below query
select application_id "Application id" ,je_source_name "Je Source"
from xla_subledgers;
p_start date/p_end date ->Date range within which accounting/gl date of corrupted data lies

After running this datafix, please open the file provided in the P_File_name parameter and reverse all batches manually displayed under the prompts - "Reverse following batches in GL" and "Reverse following batches in GL for the secondary ledgers created at Journal Level"

NOTE: To be able to reverse, one has to open the relevant periods and reverse the batches in the same periods because re-transfer the data to GL will be done in the same period.
If the user disagrees to open the period, then we may have to provide a datafix to sweep the transactions to an open period. The users will have to reverse these batches to this new period only (Please accept this option only in the worst case scenario).

5.3) After reversing the batches, Please run the script generic_deleted_from_sla_Part2.sql .
5.4) Run Transfer Journal Entries to GL to re-transfer the data back to GL.
5.4.1) While running Transfer to Journal Entries Program if Journal Imports throws EP01 error, Please refer Step 6 and then resume back to this point and continue with below action points. 5.5) Run GL Diagnostics and log a bug if GL diagnostics still shows corruption.

NOTE: The above fix will impact only data which ever transferred from SLA, if the data deleted is from other sources (eg Manuals, Other Sub ledgers) the above fix doesn't have any impact.

------------------------------

6. Journal Import failing with EP01 error while running Transfer to Journal Entries Program

6.1)Are the users willing to open the relevant periods listed in Journal Import with EP01 error.If yes, please Open period and run transfer to GL.If you ran Transfer to GL as part of data fix mentioned in this note, navigate back to original data fix and resume from where you left.
6.2)If users are not willing to open the period ,then run sweep data fix point .This would move the Accounting Date of data in SLA tables to that of latest open period.Note that users are recommended to open period over this option of running sweep because this would modify dates in SLA/GL but in sub ledger still old accounting date would be stamped.So users need to take care of this during reconciliation or followup with respective sub ledgers


NOTE: While running the data fix, please ensure proper parameter values are passed, when it comes to Dates, it should be in proper formats.
Supply the following when prompted: 
new_period_name : New Period Name for which customer needs to sweep(It should be in proper format)
ledger_id : Primary Ledger Id
application_id : Application ID (e.g 200-> Payables, 222-> Receivables etc)

To get the Application id and corresponding je_source_name, run the query below :

select application_id "Application id" ,je_source_name "Je Source" 
from xla_subledgers;

6.3) GL transfer is not picking data and ledger is secondary ledger and customer has disabled secondary ledger meaning relationship_enabled_flag in gl ledger is 'N'.Because of this GL Transfer doesnt pick secondary ledger data.Customer need following datafix in this case.

UPDATE XLA_AE_HEADERS XAH
SET
XAH.GL_TRANSFER_STATUS_CODE = 'NT'
WHERE
XAH.APPLICATION_ID = :p_application_id
AND XAH.LEDGER_ID = :p_ledger_id -- ledger id of secondary ledger which is disabled
AND XAH.GL_TRANSFER_STATUS_CODE = 'N'
AND XAH.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
AND XAH.ACCOUNTING_DATE BETWEEN TO_DATE(':p_start_date','DD-MM-YYYY') AND TO_DATE(':p_end_date','DD-MM-YYYY')
AND EXISTS (SELECT 1
FROM XLA_EVENTS XE
WHERE XE.APPLICATION_ID = :p_application_id
AND XE.EVENT_ID = XAH.EVENT_ID
AND XE.EVENT_ID = XAH.EVENT_ID
AND XE.EVENT_STATUS_CODE = 'P'
AND XE.PROCESS_STATUS_CODE = 'P');

Note : The same datafix can be used for primary book too, where you have old data not getting picked up. In that case you will have to use primary ledger id and date range of old periods as ledger_id and accounting date range respecitvely. 

Important note : After updating the records to NT such records would not go to GL and customers would need to take care of those records while doing the reconciliation from their side.

------------------------

7. 11i accounted transferred to GL in R12 missing gl sl link id.
7.1)For users who have patch 8691650 or equivalent/higher version of xlaaptrn.pkb,follow gdf 13783741:R12.XLA.A/ 13783741:R12.XLA.B
7.2)For customers who do not have patch 8691650 or have lower version please apply datafix.Point to 11i missing old datafix here.(File Name: 11iMissingGLSLLINKID_R12.txt)



If while applying any of the datafixes, the following error is occurs:
- ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
add the below line after the 'begin' statement of CALL GL API TO DELETE THE BATCHES:
dbms_output.enable(null);

AR transaction is inputted using functional currency USD but converted to IDR

Check this site

Download fix_trx_acctd_amt_func_ccy_posted_p_bki.sql from the SR.

1) This script will undo / reverse the wrong entries passed to XLA and GL and correct the data in AR:
Run the script with parameter:

&enter_user_id = ==> get the user_id for user ADMIN

select user_id from fnd_user
where user_name='ADMIN';

&responsibility_id = 50558

org_id = 82
gl_start_date = 01-JAN-2013
gl_end_date = 31-JAN-2013
gl_date = gl date in a current open period - eg 12-FEB-2013

customer_trx_id = 271844

read_only_mode= N

commit;

2) Run the 'Transfer Journal Entries to GL' concurrent program with the following parameters to get the undo accounting for the reversal transferred to GL
.
Ledger : enter your ledger
Process Category : Manual
End Date : ==> enter the gl date you passed in (1) for GL date eg. 12-FEB-2013

3) Run online accounting for the invoice 010/PS-SAI/I/13 to get it re-accounted with the correct values into the current open period passed as gl date during (1).

Code in sql:

/*============================================================================+

$Header$

============================================================================+*/

REM +================================================================================+

REM | # Bug No : 9650290                                                            |

REM |                                                                               |

REM | # RCA Bug :                                                                   |

REM |                                                                               |

REM | # Issue : The transactions got wrongly posted to GL.                          |

REM |                                                                               |

REM | # Symptoms :  a) Checked out the data for the distributions and found that    |

REM |               theacctd_amount is not equal to amount and the data is posted   |

REM |               to                                                              |

REM |               GL.                                                             |

REM |   fix_trx_acctd_amt_func_ccy_posted_p_bki.sql                                 |

REM | # Fix Approach :  a) Undo accounting has to be                                |

REM |                   done.b) The acctd_amount has to be corrected                |

REM |                   .c) The data has to be reposted to GL.                      |

REM |                                                                               |

REM | # Usage : Customer                                                            |

REM |                                                                               |

REM | # Category : Transactions                                                     |

REM | # based on fix_acctd_amt_func_ccy_posted_p.sql but with update on paymentschedule                                                                              |

REM *================================================================================*/

set serveroutput on size 1000000;

set verify off

set lines 500

set echo off

spool fix_acctd_amt_func_ccy_posted.out

declare

l_user_id           NUMBER := &enter_user_id;

l_responsibility_id NUMBER := &responsibility_id;

l_org_id      number := &org_id; 

l_gl_start_date    date   := to_date('&gl_start_date','DD-MM-YYYY');

l_gl_end_date    date   := to_date('&gl_end_date','DD-MM-YYYY');

l_gl_date       DATE := TO_DATE('&gl_date','DD-MON-YYYY');

l_customer_trx_id  number := &customer_trx_id;

l_read_only_mode   varchar2(1) := '&read_only_mode'||'';

l_bug_number     number := 9650290 ;

 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 gld.customer_trx_id customer_trx_id , gld.gl_Date,

gld.event_id event_id ,xah.gl_transfer_status_code 

from ra_cust_trx_line_gl_dist  gld,ra_customer_trx  ct, gl_ledgers gl,

xla_events xe , xla_ae_headers xah 

where gld.customer_trx_id = ct.customer_trx_id

and gld.customer_trx_id = decode(l_customer_Trx_id,0,gld.customer_trx_id,l_customer_trx_id)

and gld.gl_date between l_gl_start_date and l_gl_end_date

and ct.set_of_books_id = gl.ledger_id

and ct.invoice_currency_code = gl.currency_code

and ct.exchange_rate is  not  null

and gld.account_set_flag = 'N'

and gld.event_id = xe.event_id 

and gld.posting_control_id <> -3

and gld.amount <> gld.acctd_amount

and xe.event_id = xah.event_id

and xe.application_id = xah.application_id 

and xe.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(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 set posting_control_id  = -3 , gl_posted_date = null

      ,acctd_amount = amount ,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 ar_receivable_applications set posting_control_id  = -3 , gl_posted_date = null,

      gl_date = l_gl_date

where customer_trx_id = rec.customer_trx_id 

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 

and event_status_code = 'U' ;

      update ar_receivable_applications set acctd_amount_applied_to = amount_applied,

      acctd_amount_applied_from = amount_applied ,gl_date = l_gl_date 

      where applied_customer_trx_id = rec.customer_trx_id 

      and posting_control_id = -3

      and application_type = 'CM';

      update ar_receivable_applications set acctd_amount_applied_to = amount_applied,

      acctd_amount_applied_from = amount_applied ,gl_Date = l_gl_date 

      where customer_trx_id = rec.customer_trx_id 

      and posting_control_id = -3

      and application_type = 'CM';

update ra_customer_trx set exchange_rate_type = null , exchange_date = null,

exchange_rate = null where customer_trx_id = rec.customer_trx_id ; 

update ar_payment_schedules set exchange_rate_type = null , exchange_date = null,

exchange_rate = null ,gl_date = l_gl_date ,

                acctd_amount_due_remaining=amount_due_remaining

                 where customer_trx_id = rec.customer_trx_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;

EXCEPTION

When others then

rollback; 

raise;

end;

/