Known Oracle AP monthend issues

Issue:
AP processor are not able to close Oracle AP for a period.

Fact:
Oracle Payables ver 11.5.9

Reason:
Following are the known issues that can stop AP monthend in Oracle applications.
This applies to Oracle application version 11i (Including 11.5.9, 11.5.10)

1. AP Orphan record issue

Check following post:
http://oracally.blogspot.com/2010/01/oracle-ap-orphan-record-issue.html

2. -1 issue: AP_AE_LINES_ALL table contains rows having -1 in CODE_COMBINATION_ID column
COST Center is deactivated
GL security rule

check following post,
http://oracally.blogspot.com/2010/01/1-issue-apaelinesall-table-contains.html

3. INCOMPLETE status in AP_ACCOUNTING_EVENTS_ALL table

Check following post,
http://oracally.blogspot.com/2010/01/incomplete-status-in.html

INCOMPLETE status in AP_ACCOUNTING_EVENTS_ALL table

Symptom:
An error message pops up while closing AP period through "Accounting>Control Payables periods"

Reason:
AP_ACCOUNTING_EVENTS_ALL.EVENT_STATUS_CODE gets updated with status INCOMPLETE when the invoice is updated after being validated. And when the invoice is validated again, the status becomes CREATED.
But due to Oracle bug, sometimes the status remains in INCOMPLETE status even after validating the invoice. Due to which the transaction could not get accounted and hence error pops up while closing AP period.

Fix:
Ask the user to update the invoice again with something and save it again and then validate the invoice. See if that fixes the issue.
If not then following datafix can be used to update the EVENT_STATUS_CODE to CREATED. Then run the Payables Accounting Process to make the transaction accounted.

UPDATE ap_accounting_events_all
SET event_status_code = 'CREATED'
WHERE accounting_event_id = r1.data1
AND event_status_code IN ('INCOMPLETE')
AND org_id = &invoice_or_payment_org_id
AND source_id = &invoice_or_check_id

-1 issue: AP_AE_LINES_ALL table contains rows having -1 in CODE_COMBINATION_ID column

Symptom:
An error message pops up while closing AP period through "Accounting>Control Payables periods"

Reason:
There were some records in AP_AE_LINES_ALL table having -1 in the CODE_COMBINATION_ID column.
There can be two possible reasons (found as of now),
1. Account string becomes invalid because of COST CENTER / ACCOUNT code deactivated
The account string was valid when the related PO was created but when the invoice was matched to the PO the cost center/account code was deactivated due to which the code_combination_id becomes invalid and hence the Payables accounting process puts -1 in the AP_AE_LINES_ALL.CODE_COMBINATION_ID column for the corresponding records.

2. Another reason can be GL Security rule-
There may be some GL Security rule which restricts a perticular account from being used in Oracle payables while those are allowed in Purchasing. Hence the PO could be created with this account but when the invoice was matched to this PO, the Payables Accounting Process found that there is a GL security rule in place in Oracle Payables and hence puts -1 in the AP_AE_LINES_ALL.CODE_COMBINATION_ID column for the corresponding records.

Fix:
For #1- Temporarily activate the COST_CENTER, manually update the accounting lines in "Accounting > Update Accounting Lines" form with the correct account string. This would make the records ACCOUNTED status and then run the "Payables Transfer to GL" process which would transfer the records to GL > Journal Import > Post the journals in GL.
And after this de-activate the cost center.

For #2-
Temporarily end date the GL security rules, manually update the accounting lines in "Accounting > Update Accounting Lines" form with the correct account string. This would make the records ACCOUNTED status and then run the "Payables Transfer to GL" process which would transfer the records to GL > Journal Import > Post the journals in GL.
And after this activate the security rule.

Oracle AP Orphan record issue

Symptom:
--An error message pops up while closing AP period through "Accounting>Control Payables periods"
----Following error showing up in the log file of the Payables Accounting process,
Starting to account all the events created ...ORA-01403: no data found occurred inAP_ACCOUNTING_MAIN_PKG.Create_Accounting_Entry<-AP_ACCOUNTING_ENGINE_PKG.do_accounting<-APXAEREP with parameters (&PARAMETERS) while performing the following operation:&DEBUG_INFO

You get error in the log file similar to the one attached here.



Reason:
There were some Orphan records in AP_ACCOUNTING_EVENTS_ALL table for which there is no corresponding invoice/payment records in AP_INVOICES_ALL / AP_INVOICE_PAYMENTS_ALL table.

Fix:
Delete the orphan records from the AP_ACCOUNTING_EVENTS_ALL table.

Suggested SQL:
Take the back of these records and then use the below delete statement.

DELETE FROM ap_accounting_events_all e
WHERE source_table = 'AP_INVOICES'
AND NOT EXISTS (SELECT i.invoice_id
FROM ap_invoices_all i
WHERE i.invoice_id = e.source_id);

For finding the Orphan records following query can be used,
SELECT b.NAME ou_name
, c.NAME set_of_book_name
, a.source_table
, a.source_id
, a.accounting_date
, a.creation_date
, a.accounting_event_id
FROM ap_accounting_events_all a,
hr_operating_units b,
gl_sets_of_books c
WHERE source_table = 'AP_CHECKS'
AND a.org_id = b.organization_id
AND b.set_of_books_id = c.set_of_books_id
AND NOT EXISTS (SELECT d.check_id
FROM ap_checks_all d
WHERE d.check_id = a.source_id)
UNION all
SELECT b.NAME ou_name
, c.NAME set_of_book_name
, a.source_table
, a.source_id
, a.accounting_date
, a.creation_date
, a.accounting_event_id
FROM ap_accounting_events_all a,
hr_operating_units b,
gl_sets_of_books c
WHERE source_table = 'AP_INVOICES'
AND a.org_id = b.organization_id
AND b.set_of_books_id = c.set_of_books_id
AND NOT EXISTS (SELECT d.invoice_id
FROM ap_invoices_all d
WHERE d.invoice_id = a.source_id);

Opening a POP UP window in OAF

There was a requirement to show a POP UP window which opens a static html file when a perticular OAF page is displayed. To achieve this sort of requirement wherein a window needs to be opened automatically each time visiting a page do the followings,

1. Create your html file and put it in $OA_HTML in OA Application tier.
2. Bounce Apache
3. Turn on the personalization at your user level in Oracle apps through profile option: Personalize Self-Service Defn
4. Go the the page where you want the AUTO POP UP window to be opened > Click "Personalize Page" link at upper right corner > Select the "Complete View" radio button
5. In the Page Layout (as the POP UP window needs to be opened when this page opens) for this page click the "Create Item" link
6. In the create Item window do the followings,
Level: Depending on your requirement (If all the time then Function level otherwise Site level)
Item Style: "Raw Text"
ID: Enter an unique item ID e.g. (XX_WELCOME)
Text:"window.open('http://YOUR_ORACLE_APP_HOST:PORT_NUMBER/OA_HTML/your_html.html','WINDOW_TITLE','ATTRIBUTES');"
In the above string replace the following with your implementation,
YOUR_ORACLE_APP_HOST: This will be your application URL through which you access oracle apps
PORT_NUMBER: Your Application server PORT number
your_html: This is your HTML file name
WINDOW_TITLE: Enter your window title here
ATTRIBUTES: POP UP Window attributes like scrollbars=yes, resizable=yes etc

Put the above in script tag otherwise it would not work. Somehow this site is removing those tags from my post may be due to security reasons.

7. Save your seetings.
8. POP UP window should open now whenever you go the above page. If it does not open then check your POP UP blocker setting and allow your application in the list.

Requisition not getting approved || Requisitoin remains in IN PROCESS || User-Defined Exception||Wf_Engine_Util.Function_Call(PO_REQAPPROVAL_ACTION.VE

Issue:
Requisition is not getting approved. It remains in "IN PROCESS" status.


Symptom:
--Requestion remains in IN PROCESS status when viewed in iProc.
--No error notification is generated.


Facts:
1> Preparer and requestor are different persons.
2> Last approver has approved the requisition and has all the authorization limit. But still the requisition is in IN PROCESS status.


Reason:
Requisition is in error in the REQAPPRV workflow at following node,
Node: VERIFY_APPROVER_AUTHORITY
Process:VERIFY_APPROVER_AUTH_APPROVE
Error message:User-Defined Exception
Error stack: Wf_Engine_Util.Function_Call(PO_REQAPPROVAL_ACTION.VERIFY_AUTHORITY, REQAPPRV, ...


Suggested Fix:
Login to Oracle apps>Workflow Administrator>Status monitor>Retry the workflow for the ITEM TYPE:REQAPPRV and for the item key

Migrating Oracle Form Personalization

In Oracle Apps DB server, do the followings to migrate Oracle Form Personalization between different instances,

Oracle Apps Ver: 11i

Step 1>Run the following command for downloading all the personalization for a perticular form,

$FND_TOP/bin/FNDLOAD "db_userid"/"db_pwd" 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct "filename.ldt" FND_FORM_CUSTOM_RULES form_name="FND_FORM_CUSTOM_RULES.FORM_NAME"

Step 2>Run the following command for uploading all the personalization for the form,

$FND_TOP/bin/FNDLOAD "db_userid"/"db_pwd" 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct "file_name.ldt"