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_PROG RAM_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:
- 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.
- Before running the postings, ensure that the GL interface errors, if any, are cleared.
- 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.
Refer the blog post for: Period Closure Activities For Oracle Apps Implementation
