Oracle Apps Financials period closure monitoring scripts

Oracle Apps Financials period closure monitoring scripts

Accounts Payable:

To find whether any orphan entry exist (this prevents period close)

Select *

from apps.ap_ae_lines_all

Where ae_header_id in(

Select ae_header_id

from apps.ap_ae_headers_all

Where org_id in (select organization_id

from apps.hr_operating_units

Where name like 'XYZ%')

and gl_transfer_flag='N'

And GL_TRANSFER_RUN_ID=-1

And set_of_books_id in (select set_of_books_id

from apps.hr_operating_units

Where name like 'XYZ%')

);

  

Select *

from apps.ap_ae_headers_all

Where org_id in (select organization_id

from apps.hr_operating_units

Where name like 'GEMSAS%')

and gl_transfer_flag='N'

And GL_TRANSFER_RUN_ID=-1

And set_of_books_id in (select organization_id

from apps.hr_operating_units

Where name like 'XYZ%');

If the above 2 queries returns row, run the AP transfer to GL. Re-run the query. If still it returns rows, then check whether the accounting_event_id exists in ap_accounting_events_all. If not, then these are orphan entries and needs to be deleted.

To check whether there are any transactions accounted with error or accounted but not getting transferred to GL (this prevents period close)

select 'AP Accounting Errors(This Needs Your Attention)',

         ouname ,

         count (*),

         null,

         ou ,

         aph.ae_header_id

from

       (select hrou.NAME ouname,

                   apl.AE_LINE_ID,

                   aph.ae_header_id,

                   aph.ORG_ID ou,

                   apl.GL_TRANSFER_ERROR_CODE,

                   apl.ACCOUNTING_ERROR_CODE,

                   aph.GL_TRANSFER_RUN_ID 

       from   apps.ap_ae_headers_all aph,

              apps.AP_AE_LINES_ALL apl,

              apps.hr_operating_units hrou,

              gl.gl_sets_of_books sob

       where   sob.SET_OF_BOOKS_ID = aph.SET_OF_BOOKS_ID

               and sob.SET_OF_BOOKS_ID=hrou.SET_OF_BOOKS_ID

               and aph.AE_HEADER_ID = apl.AE_HEADER_ID

               and aph.ORG_ID=hrou.ORGANIZATION_ID

               and aph.PERIOD_NAME =to_char(sysdate,'MM-MON-YY')

   and (apl.GL_TRANSFER_ERROR_CODE is not null or         apl.ACCOUNTING_ERROR_CODE is not null) )

group by ouname,ou, ae_header_id;

To find any payment batch that is either not confirmed or cancelled (this prevents period close)

select a.CHECKRUN_NAME , 

 a.status ,

 a.org_id,b.NAME

from

 apps.ap_inv_selection_criteria_all a,

 apps.hr_all_organization_units b

where  status not in (‘CANCELED’,’CONFIRMED’,’QUICKCHECK’)

 and a.org_id=b.organization_id

To find whether all future dated payment batches are swept to the next period. 

These does not prevent period close but causes no accounting for these checks for Maturity event. Need data fixes in these cases to update the accounting date to current period and then account those.

Select * from apps.ap_payment_history_all

Where org_id in (select organization_id from apps.hr_operating_units

Where name like ‘GEMSAS%’)

And posted_flag = 'N'

And accounting_event_id is null;

Account Receivable:

To find any unposted items. All these prevents period close

select FCR.REQUEST_ID

,substr(resp.RESPONSIBILITY_NAME ,1,50) RESPONSIBILITY_NAME

,TO_CHAR(fcr.REQUESTED_START_DATE, 'DD-MON-YYYY HH24:MI:SS')

,fcr.REQUEST_DATE

, SUBSTR(USERS.DESCRIPTION,1,30) USER_NAME

,SUBSTR(FCP.USER_CONCURRENT_PROGRAM_NAME,1,55) program_name

FROM

APPS.FND_CONCURRENT_REQUESTS fcr,

APPS.FND_CONCURRENT_PROGRAMS_TL fcp,

fnd_user users,

fnd_responsibility_tl resp

where

fcr.PHASE_CODE =  'C' AND --  COMPLETED ;'P' AND --Pending

fcr.STATUS_CODE = 'C'  AND -- NORMAL ;'I' AND --Incomplete

fcr.RESPONSIBILITY_ID = resp.RESPONSIBILITY_ID AND

FCR.REQUESTED_BY = USERs.USER_ID AND

FCP.USER_CONCURRENT_PROGRAM_NAME  = 'Unposted Items Report from GL Transfer Program'  --'General Ledger Transfer Program'

and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID

AND fcr.REQUEST_DATE > (sysdate - 1)

order by fcr.REQUEST_DATE desc ;

To find whether any receipts are stuck in the ar_interim table. This prevents period close.         

select * from apps.AR_INTERIM_CASH_RECEIPTS_ALL;

select * from apps.AR_INTERIM_CASH_RCPT_LINES_ALL;

Others:

1. Check the ar_interface before closing the periods and ensure that you run auto invoice for all the orgs. This can be triggered through an SRS Engine request set.
2. Before running the postings, ensure that the GL interface errors, if any, are cleared.
3. Ensure that the Future Payment Maturity Date Report is run before the AP chain runs.

General Ledger

Monitor regularly the gl_interface table for any gl unposted records. If anything is stuck with Error message, follow-up with the users to clear them up.