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;

/