AP Period is not closing due to following error APP-SQLAP-10304: You cannot close this period because exceptions exist.

Issue: AP Period is not closing due to following error,
------------
APP-SQLAP-10304: You cannot close this period because exceptions exist.

Plese review and resolve all exceptions that are listed in the Period Close Exceptions Report. Choose the Exceptions button to submit this report.

-----------

You would following message pops up when you try to close the AP period in Oracle payables.



Overview (What Oracle checks before it closes any AP Month)
In order for the AP period to be successfully closed, Oracle ensures that followings are met before closing AP period,
1. There should not be any UNACCOUNTED transactions in AP_ACCOUNTING_EVENTS_ALL table

2. There should not be any orphan records

3. All the transactions must be transferred to GL successfully. It checks AP_AE_LINES_ALL table to see if there is anything which have not been transferred to GL. Following query can be used to check that (#4 below addresses this if you see anything).
Oracle does not check if the AP transactions are successfully imported to GL or not. It just checks whether the transactions make it to the GL_INTERFACE table. Oracle would allow you to close AP period even if the transactions reach GL_INTERFACE table but fails due to some issue like GL security rule, CV rule etc. Bottom line is the AP_AE_LINES_ALL.GL_SL_LINK_ID must be pouplated with a valid GL_IMPORT_REFERENCES.GL_SL_LINK_ID. If it finds then Oracle would allow you to close the period.

select e.EVENT_TYPE_CODE
,h.accounting_date
, g.concatenated_segments
, l.ae_line_type_code
, l.currency_code
, l.entered_dr
, l.entered_cr
,l.CREATION_DATE accounting_creation_date
,l.reference5 invoice_number
,e.ORG_ID||'-'||k.name
from ap_accounting_events_all e,
ap_ae_lines_all l,
ap_ae_headers_all h,
gl_code_combinations_kfv g,
hr_operating_units k
where 1 =1
and g.CODE_COMBINATION_ID = L.CODE_COMBINATION_ID
and e.accounting_event_id = h.accounting_event_id
and h.ae_header_id = l.ae_header_id
AND L.GL_SL_LINK_ID IS NULL
AND h.period_name= '&period_name'
and h.GL_TRANSFER_FLAG='E'
and k.organization_id=e.org_id

Reason:
When you see this message you must check followings,
1. Any orphan record exist or not (check following post)
http://oracally.blogspot.com/2010/01/oracle-ap-orphan-record-issue.html

2. Any unaccounted AP transactions exist (check following post)
http://oracally.blogspot.com/2009/11/oracle-payables-month-end-reports.html

3. Any transactions with code_combination_id=-1 in AP_AE_LINES_ALL table
http://oracally.blogspot.com/2010/01/1-issue-apaelinesall-table-contains.html

4. Any stuck transactions in AP_AE_LINES_ALL table which are not able to be transferred to Oracle GL

In this post we will be discussing on #4.
When you see the above message, do the followings,

Run "Payables Transfer to GL" process > view the output file > You should see transactions stuck in the AP_AE_LINES_ALL and in the output file you should see message like "Inactive" or "Disabled" or "Invalid" or "Post"

You would see the output like this (here the exception is "Inactive"),



Exception and their fixes have been listed below,

1. If you see "Disabled" then,
Run a query on table GL_CODE_COMBINATIONS to see if the complete code combination is enabled.
You shuld check GL_CODE_COMBINATIONS.ENABLED_FLAG column for the gl string.

Run a query to check FND_FLEX_VALUES_VL.ENABLED_FLAG column for the account to see if the account is valid.
If the above is disabled then enable them and run the "Payables Transfer to GL" process.
This should transfer the transactions to GL successfully and user should be able to close the AP period.

2. If you see "Invalid" then,
The code combination string used is not valid and would not exist in GL_CODE_COMBINATIONS table or the account used is not valid.
To check code combination string -Check GL_CODE_COMBINATIONS table
To check account -Check FND_FLEX_VALUES for the account value set (check your setup for the value set used for natural account)

Correct the accounting in the invoice > run Payables Accounting process> run "Payables Transfer to GL" process.
This should transfer the transactions to GL successfully and user should be able to close the AP period.

3. If you see "Post" then,
The code combination string used is not allowed for detail posting or the natural account does not have posting allowed
For the GL string check in table -GL_CODE_COMBINATIONS.DETAIL_POSTING_ALLOWED_FLAG
For the natural account check in the front end in APPS in the value set for natural account

Either correct the setup in GL or correct the accounting in the invoice > Run "Payables Accounting" process >
run Payables Accounting process> run "Payables Transfer to GL" process.
This should transfer the transactions to GL successfully and user should be able to close the AP period.

4. If you see "Inactive" like shown in the screenshot above-
This happens if the account used in the GL string / GL string itself has a START_ACTIVE_DATE later than the AP_ACCOUNTING_EVENTS_ALL.ACCOUNTING_DATE for the related transactions. You may use the following queries to see if that is the case,

To check if GL string has later start date than AP Accounting date. If the following query returns any record then you have to correct the GL string start date,

select e.EVENT_TYPE_CODE
,h.accounting_date
, g.concatenated_segments
,g.START_DATE_ACTIVE gl_string_start_date
, l.ae_line_type_code
, l.currency_code
, l.entered_dr
, l.entered_cr
,l.CREATION_DATE accounting_creation_date
,l.reference5 invoice_number
,e.ORG_ID||'-'||k.name
from ap_accounting_events_all e,
ap_ae_lines_all l,
ap_ae_headers_all h,
gl_code_combinations_kfv g,
hr_operating_units k
where 1 =1
and g.CODE_COMBINATION_ID = L.CODE_COMBINATION_ID
and e.accounting_event_id = h.accounting_event_id
and h.ae_header_id = l.ae_header_id
AND L.GL_SL_LINK_ID IS NULL
AND h.period_name= '&period_name'
and h.GL_TRANSFER_FLAG='E'
and k.organization_id=e.org_id
and e.ACCOUNTING_DATE
Similarly find out if natural account start date is later than the AP accounting date.

If you find any such transactions from the above query then the transactions would get stuck in the subledger accounting tables and would not go to GL and the "Payables transfer to GL" would show the output as shown above.

To fix this, update the natural account in the Account flex valueset to have the start date active earlier than the AP_ACCOUNTING_EVENTS_ALL.ACCOUNTING_DATE.

Also check the GL_CODE_COMBINATIONS.START_DATE_ACTIVE value for the GL string. That should be earlier than the AP_ACCOUNTING_EVENTS_ALL.ACCOUNTING_DATE.
After doing that run the "Payables Transfer to GL" process.