AP does not balance to GL

 

Doc ID: AG0967

Subject: PH payment done in May but still appeared as unrecorded transaction of reconciling

Type: Problem

Creation Date: 11-Aug-08

Last Revision Date: 02-Sep-08

Status: Open

Owner: Kevin

 

Symptom

PH FIN said that there are unrecorded payments that appeared as reconciling when in fact payment was done in the month of May 2008. (Refer to AG0967_01 AG0967_02)

 

Cause

 

 

Solutions

1. [11-Aug-08] Kevin checked the problem and found that so many records were in gl_interface table. And the status of some of these records is ‘EC12’.(Refer to AG0967_03)

2. [12-Aug-08] Kevin found Note:344367.1 about that AP does not balance to GL in Metalink.

How to check this kind of problem:

First verify that there is no data in the GL Interface that has yet to be processed.

    If the following query returns any rows this data must be imported and posted into

    the GL before following through with the rest of the action plan.

  

        select x.group_id

            , x.batch_name

            , x.gl_transfer_run_id

            , g.status

            , USER_JE_SOURCE_NAME

            , sum(g.accounted_dr)

            , sum(g.accounted_cr)

        from XLA_GL_TRANSFER_BATCHES_ALL x

            , GL_INTERFACE g

        where x.group_id = g.group_id

        and x.application_id = 200

        and g.gl_sl_link_table = 'APECL'

        group by x.group_id

        , x.batch_name

        , x.gl_transfer_run_id

        , g.status

        , USER_JE_SOURCE_NAME

   (Refer to AG0967_04)

3. [13-Aug-08] Kevin found this kind of ‘EC12’ problem maybe is Bug 4585089  of Oracle EBS.

4. [14-Aug-08] Kevin asked Ren to help to apply Patch 4569370  in a test environment first. (AG0967_05)

5. [15-Aug-08] Kevin checked this problem and run ’Journal Import’ in GL on TEST Server. But the error ‘EC12’ still existed. (Refer to AG0967_06)

6. [18-Aug-08] Kevin was still checking this problem.

7. [21-Aug-08] Kevin found account [210.212.5901002000.0000.0000.0000.000.0000] in accounting of payment [Document Num:2972]. And Kevin confirmed with PH FIN that the account [210.212.5901002000.0000.0000.0000.000.0000] should not appear here. (Refer to AG0967_07)

8. [21-Aug-08] Kevin wanted to create SR for this problem but didn’t succeed because the status of support identifier was ‘NO VALID CSI FOUND’.

9. [25-Aug-08] Kevin found payment [2971] and [2972] those were not balance.

10. [25-Aug-08] Kevin ran the SQL script on TEST Server(ERPDB).(SQL script:AG0967_08)

11. [25-Aug-08] Opened period ‘May-08’ in AP module. (Refer to AG0967_09)

12. [25-Aug-08] Then ran the ‘Payables Transfer to General Ledger’. (Refer to AG0967_10)

13. [25-Aug-08] Then ran the ‘Journal Import’. (Refer to AG0967_11)

14. [25-Aug-08] Inquired journal. (Refer to AG0967_12)

15. [26-Aug-08] Kevin asked PH FIN to check this problem on TEST Server(ERPDB) carefully. (Refer to AG0967_13)

16. [26-Aug-08] PH FIN confirmed that this problem was solved on TEST Server(ERPDB). (Refer to AG0967_14)

17. [26-Aug-08] Awaiting for PH FIN confirms entry AG0968 that is ok. We need to confirm that AG0967 and AG0968 are all ok before we apply the solution on PROD Server, because entry AG0967 is the same kind of problem as AG0968, and they have the same one solution.

18．[02-Sep-08] Kevin applied the solution on TEST Server again, and found there were journal entry is unbalanced.

     There were Batch Name[34985 Payables 5231237: A 371476], Batch Name[36288 Payables 5231237: A 396269]

19. [03-Sep-08] PH FIN Myles checked them and found some abnormal entries. (Refer to AG0967_15 AG0967_15A)

20. [03-Sep-08] Kevin found what caused these abnormal entries. The cause needed to be checked. (Refer to AG0967_16)

21. [04-Sep-08] PH FIN Myles confirmed these entries were abnormal. (Refer to AG0967_17 AG0967_17A)

22. [05-Sep-08] Ren restored ERPAPP backup dated 2008-08-30 on TEST server for my testing. (Refer to AG0967_18)

23. [05-Sep-08] Kevin ran the SQL script to correct the abnormal data. (Refer to AG0967_19)

24. [05-Sep-08] Ran 'Payables Transfer to General Ledger' & 'Journal Import'. (Refer to Slide #8&#9 in AG0967_20)

25. [05-Sep-08] This problem was solved on TEST Server(ERPDB). Awaiting for end user’s confirmation.

 

 

 

 

 

Doc ID: AG0968

Subject: Voided transactions still showed in bank recon items

Type: Problem

Creation Date: 11-Aug-08

Last Revision Date: 02-Sep-08

Status: Open

Owner: Kevin

 

 

Symptom

PH FIN said that there were several “voided transactions” made which should not appear as reconciling but the system read or included those in downloading of bank recon items. (Refer to AG0968_01 AG0968_02)

 

 

Cause

 

 

Solutions

1. [11-Aug-08] Kevin checked the problem and found that so many records were in gl_interface table. And the status of some of these records is ‘EC12’.(Refer to AG0968_03)

2. [12-Aug-08] Kevin found Note:344367.1 about that AP does not balance to GL in Metalink.

How to check this kind of problem:

First verify that there is no data in the GL Interface that has yet to be processed.

    If the following query returns any rows this data must be imported and posted into

    the GL before following through with the rest of the action plan.

  

        select x.group_id

            , x.batch_name

            , x.gl_transfer_run_id

            , g.status

            , USER_JE_SOURCE_NAME

            , sum(g.accounted_dr)

            , sum(g.accounted_cr)

        from XLA_GL_TRANSFER_BATCHES_ALL x

            , GL_INTERFACE g

        where x.group_id = g.group_id

        and x.application_id = 200

        and g.gl_sl_link_table = 'APECL'

        group by x.group_id

        , x.batch_name

        , x.gl_transfer_run_id

        , g.status

        , USER_JE_SOURCE_NAME

   (Refer to AG0968_04)

3. [13-Aug-08] Kevin found this kind of ‘EC12’ problem maybe caused by Bug 4585089  of Oracle EBS.

4. [14-Aug-08] Kevin asked Ren to help to apply Patch 4569370  in a test environment first. (AG0968_05)

5. [15-Aug-08] Kevin checked this problem and run ’Journal Import’ in GL on TEST Server. But the error ‘EC12’ still existed. (Refer to AG0968_06)

6. [18-Aug-08] Kevin was still checking this problem.

7. [21-Aug-08] Kevin found account [210.212.5901002000.0000.0000.0000.000.0000] in accounting of payment [Document Num:2972]. And Kevin confirmed with PH FIN that the account [210.212.5901002000.0000.0000.0000.000.0000] should not appear here. (Refer to AG0968_07)

8. [21-Aug-08] Kevin wanted to create SR for this problem but didn’t succeed because the status of support identifier was ‘NO VALID CSI FOUND’.

9. [25-Aug-08] Kevin found payment [2971] and [2972] those were not balance.

10. [25-Aug-08] Kevin ran the SQL script on TEST Server(ERPDB).(SQL script:AG0968_08)

11. [25-Aug-08] Opened period ‘May-08’ in AP module. (Refer to AG0968_09)

12. [25-Aug-08] Then ran the ‘Payables Transfer to General Ledger’. (Refer to AG0968_10)

13. [25-Aug-08] Then ran the ‘Journal Import’. (Refer to AG0968_11)

14. [25-Aug-08] Inquired journal. (Refer to AG0968_12)

15. [26-Aug-08] Kevin asked PH FIN to check this problem on TEST Server(ERPDB) carefully. (Refer to AG0968_13)

16. [26-Aug-08] PH FIN said that they didn’t see the solution on TEST Server(ERPDB). (Refer to AG0968_14)

17. [27-Aug-08] Kevin told them how to check this problem. (Refer to AG0968_15 AG0968_15A)

18. [28-Aug-08] PH FIN said that there were payments done in 2007, and there payments were reversed in 2008. (Refer to AG0968_16)

19. [28-Aug-08] Awaiting for the end confirmation of PH FIN.

20．[02-Sep-08] Kevin applied the solution on TEST Server again, and found there were journal entry is unbalanced.

     There were Batch Name[34985 Payables 5231237: A 371476], Batch Name[36288 Payables 5231237: A 396269]

21. [03-Sep-08] PH FIN Myles checked them and found some abnormal entries. (Refer to AG0968_17 AG0968_17A)

22. [03-Sep-08] Kevin found what caused these abnormal entries. The cause needed to be checked. (Refer to AG0968_18)

23. [04-Sep-08] PH FIN Myles confirmed these entries were abnormal. (Refer to AG0968_19 AG0968_19A)

24. [05-Sep-08] Ren restored ERPAPP backup dated 2008-08-30 on TEST server for my testing. (Refer to AG0968_20)

25. [05-Sep-08] Kevin ran the SQL script to correct the abnormal data. (Refer to AG0968_21)

26. [05-Sep-08] Ran 'Payables Transfer to General Ledger' & 'Journal Import'. (Refer to Slide #8&#9 in AG0968_22)

27. [05-Sep-08] This problem was solved on TEST Server(ERPDB). Awaiting for end user’s confirmation.

 

 

 

 

 

 

----------------------------------------FINAL SOLUTION--------------------------------------------

--1. delete 91 records in gl_interface
--23 records 398785异常情况
select * from gl_interface
where gl_sl_link_id in (select gl_sl_link_id from ap_ae_lines_all
where ae_header_id=161956) or gl_sl_link_id=398785

       

        select * from ap_ae_lines_all
where ae_header_id=161956

 

        select * from ap_ae_lines_all
where gl_sl_link_id=398785

 

        delete gl_interface
where gl_sl_link_id in (select gl_sl_link_id from ap_ae_lines_all
where ae_header_id=161956) or gl_sl_link_id=398785

        --22 records
select * from gl_interface
where gl_sl_link_id in (select gl_sl_link_id from ap_ae_lines_all
where ae_header_id=171194)

 

        delete gl_interface
where gl_sl_link_id in (select gl_sl_link_id from ap_ae_lines_all
where ae_header_id=171194)

        --23 records
select * from gl_interface
where gl_sl_link_id in (select gl_sl_link_id from ap_ae_lines_all
where ae_header_id=171195)

 

        delete gl_interface
where gl_sl_link_id in (select gl_sl_link_id from ap_ae_lines_all
where ae_header_id=171195)

        --23 records
select * from gl_interface
where gl_sl_link_id in (select gl_sl_link_id from ap_ae_lines_all
where ae_header_id=171196)

 

        delete gl_interface
where gl_sl_link_id in (select gl_sl_link_id from ap_ae_lines_all
where ae_header_id=171196)

--2. update data (payment Document Num 2971&2972)
select * from ap_ae_lines_all
where ae_header_id=161956

     

        update ap_ae_lines_all
set entered_cr=199267.56
where ae_header_id=161956 and ae_line_id=428596

 

        select * from ap_ae_lines_all
where ae_header_id=171194

 

        update ap_ae_lines_all
set entered_dr=199267.56
where ae_header_id=171194 and ae_line_id=458482

 

        select * from ap_ae_lines_all
where ae_header_id=171195

 

        update ap_ae_lines_all
set entered_dr=10815.75
where ae_header_id=171195 and ae_line_id=458504

 

        select * from ap_ae_lines_all
where ae_header_id=171196

 

        update ap_ae_lines_all
set entered_cr=10815.75
where ae_header_id=171196 and ae_line_id=458527

 

--3. update status to do 'Payables Transfer to General Ledger' again
select * from ap_ae_headers_all
where ae_header_id in (161956,171194,171195,171196)

 

        update ap_ae_headers_all
set gl_transfer_flag='N',
      gl_transfer_run_id='-1'
where ae_header_id in (161956,171194,171195,171196)

 

--4. run 'Payables Transfer to General Ledger' & 'Journal Import'