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.
This should transfer the transactions to GL successfully and user should be able to close the AP period.

Designing a Check using XML Publisher

This post is in-process, I would be putting the video for this soon. Please revisit this blog in couple of days. Thanks for your patience.

Oracle applications R12 has payables check printing using Oracle XML Publisher. But in 11i the check printing program uses Oracle report writer for check.

Here I would be explaining how to design a check using XML publisher. This is a live version of check that organizations use to pay suppliers.
I have developed a complete solution for this including,

Check design (.rtf file) using XML desktop component plugin to MS Word
--Program to generate the XML data source
--All the necessary Oracle applications registration including registration using XML Publisher Administrator responsibility

For the complete solution including source code and other documentation, please contact me through this blog via posting a comment and I would provide the same on need by basis.

Thanks,

XML Publisher Tutorial-

In this post I would explain how to design report using XML publisher. I have created two videos for this explaining the steps to create report using XML publisher.

Video 1- This explains followings,

• PL/SQL program to generate the XML data source
• Concurrent program registration in Oracle apps
• Validating the XML data generated
• Registering XML Data definition (linking the concurrent program)

Video 2-

• Template creation (.rtf file)
• Validating the template
• Template registration in Template manager in XML Publisher Administrator responsibility
• Linking XML definition with Template
• Finally Running the concurrent request to generate report output in PDF format
  

PL/SQL Procedure-
You can compile the procedure at below location in APPS schema in Oracle Apps to try this tutorial.

Click here for the code

Oracle XML Publisher -Designing and development of supplier check

I have shown here the complete design and development of Supplier check using Oracle XML publisher. I have the complete kit for this which includes,

--Concurrent program LDT file
--Check template (.rtf)
--DB package
--Configuration of report in XML Publisher Administrator responsibility

This would definitely reduce development time if you are planning to build this on your own.

How to clear web cache in Oracle applications

Whenever there is anything added/changed to any OA component (PG, RG etc) / BC4J components (VO / EO / AMetc), Controller classes, the Apache server cache needs to be cleared so that the changes can be seen.

There are two ways to clear the web cache in Oracle Apache,

1. Bounce Apache (You can refer the following article in this blog to do that)
http://oracally.blogspot.com/2010/01/startingstopping-apache-in-oracle.html

2. Web cache can be cleared without bouncing Apache. To do that, do the following,

Get "Functional Administrator" responsibility>Click on "Functional Administrator" responsibility
Following screen would come up.




Click on "Core Services" tab> Click "Caching Framework" menu > Click "Global Configurations"> Following screen would come up.




Click on "Clear all Cache" button which would prompt you to confirm> Click yes to clear all cache.


Note: Clearing cache would impact performance because server needs to cache any page opened after this.

Object Version Number in Oracle Applications

Oracle database tables usually contain a column known as Object Version number (OVN).

Here is its use .

This column is used to control the update patterns of a tuple. (row in the database table ).

Once the row is inserted in the table the OVN is 1.

When there is an update on the same record then it checks the value of OVN.

If the OVN is same then it commits the changes to the database and increments the value of OVN to the next number .

Some other sources when trying to update the same row finds the change in OVN and the system rejects it advises to requery the record .

This feature is absolutely helpful when there are multiple updates and only 1 update is allowed at a time .

Even the APIs use this as a parameter: p_object version_number.

Object Version Number in Oracle Applications

Oracle Applications tables usually contain a column known as Object Version number (OVN).

Here is its use .

This column is used to track the changes of a tuple. (row in the database table )

R12: Opening Payables accounting period in Oracle R12

Oracle payables accounting period needs to be opened before any AP invoice could be entered in that period.
The date that you enter in the GL period field in the Invoice entry screen needs to be in an opened AP period.

If not opened / "future open", then you would see the below popup error (App-sqlap-10006).




Do the following to open/"future open" the AP period in Oracle payables module,

Login to oracle>Payables super user responsibility>Setup > Calender > Accounting > Accounting periods
You would see the below screen.




Open the required period by selecting "Open"/"Future" in the Period status field and save the change.

Now, invoice can be entered in the open/future period.

How to find employee's organization structure

Oralce has provided CONNECT BY claused in SQL query which can be used to find data in a hierarchical structure.

To find Organization structure for an employee in PER_ALL_PEOPLE_F table following query can be used,

SELECT DISTINCT
B.FULL_NAME EMP_NAME
,DECODE(B.PERSON_TYPE_ID,6,'EMPLOYEE','TEMP') EMP_TYPE
,C.FULL_NAME SUPERVISOR_NAME
,LEVEL
FROM PER_ALL_ASSIGNMENTS_F A, PER_ALL_PEOPLE_F B, PER_ALL_PEOPLE_F C
WHERE A.PERSON_ID=B.PERSON_ID
AND SYSDATE BETWEEN B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN C.EFFECTIVE_START_DATE AND C.EFFECTIVE_END_DATE
AND B.PERSON_TYPE_ID IN (6,103)
AND A.SUPERVISOR_ID = C.PERSON_ID
START WITH A.PERSON_id = &starting_person_id
CONNECT BY PRIOR A.PERSON_id = SUPERVISOR_id
ORDER BY LEVEL,SUPERVISOR_NAME

This would return organizational for the input employee identified by &starting_person_id. In other words, it would give the employee and supervisor relationship of the tree of which the starting node is &starting_person_id.

LEVEL: This is a oracle pseudo column for the CONNECT BY queries.

Preliminary payment register report going into error

Issue:
Preliminary Payment Register Report is not getting printed (Number Of Copies=0)

Facts:
Oracle Applications ver 11.5.9

User tries to print the Preliminary payment register report through the standard payment processing funcationality wherein user selects the check bok "Print Preliminary Register" during the payment batch processing using the following navigation,

Login to Oracle>AP payment responsibility>Payments>
Entry>Payment Batches>click on actions button for invoice batch,check the ' Print Preliminary Register' check box and give printer name

Reason:
Preliminary payment register defaulting to zero copies using the profile option 'Concurrent:Report Copies'

Workarounds:

Option 1: The payment user should have to manually run the preliminary and Final register program separately manually giving the no of prints they require. This will require a change in the process and needs to be communicated to the payment users.

Option 2: Enable the profile option to make the number of prints to 1 for all programs only for the payment users or for the payment responsibility. But this is not recommended, as all the other programs which have no print as the option will end in warning.

LTF
Work with Oracle for Patch#6188470

Oracle EBS 11i: Define Organization structure / Oraganization Hierarchy

Defining Organization structure is the first step in any ERP implementation and is the most critical part of any implementation. You have to carefully study and understand the companys' reporting requirements, management hierarchy, business geographies, legal requirements, data security etc while finalizing it's organization structure.

First let me define the key components of Organization structure in Oracle ERP perspective. To better explain these, lets assume assume Amazon.com is implementing Oracle financials for it's US business.

1. Set of Books
Set of book is the top level of Organization hierarchy. This defines 3 key components of a ledger. So any transaction in a ledger automatically inherits followings from set of books,

a. Chart of Accounts
b. Currency
c. Calender

To set this up for Amazon.com US business in Oracle ERP, the values for above would be,

a. Chart of Accounts: To determine how many segments would be used in Chart of Accounts depends on several factors like financial reporting requirement, financial reporting to management, what level of reporting is required etc.
Typical Oracle ERP implementation varies from 5-8 segments. Following minimum level of segments should be there in any implementation,

Company (Balancing segment)
Cost center / Department
Natural Account

Generally every Organization require above segments as part of their Chart of accounts to track financial transactions, for financical reporting purpose etc.

Other segments like followings vary upon type of businesses,

Project
Product

For Amazon.com US business lets assume we have 7 segment chart of accounts with 2 segments for future usages.

b. Currency: This is the functional currency of the country in which you are doing business. For Amazon.com US business, this would be USD.

c. Calender: This is the financial calender that company wants to follow. Most of the US companies have financial calender as JAN to DEC but it is not mendatory to have JAN to DEC. Some companies like CISCO has financial period from AUG to JUL. So, based on company's requirement and business practice setup Calender.

Basically these three components make up a Set of book in Oracle. If anyone changes then you have to create a new Set of book. For our e.g., if Amazon.com starts business in UK then a new set of book needs to be created for UK currency (Generally Chart of accounts and calender remains same for any company across different countries).

Set of books is not for outside reporting. This is the Oracle way of handling financial transactions (GL Journal entries) and securing data within Oracle ERP. Set of books is defined in Oracle General Ledger module. A set of book can have multiple Legal entities / Operating units.

2. Legal Entity

Legal entity is the next level in Oraganization hierarchy and this is what is visible to outside world. For our e.g., if Amazon.com US business has been registered with AMAZON.COM LLC then the legal entity name for Amazon.com's US business would be "AMAZON.COM LLC". Note that the name is case sensitive and must match with the company registered name. All legal / financial reporting for a company happens by Legal entity name. Any financial deals (like Purchase orders, Sales Orders, Employement etc) in a company happens by Legal entity name.

Oracle classifies an Organization as Legal entity by "GRE/Legal entity". Organization is defined in Oracle Purchasing Module. A legal entity can have 1 and only 1 set of books but can have 1 or many operating units.

3. Operating Unit

Next level after Legal Entity is Operating unit. This is Oracle way of securing financial data and is nothing to do with Outside world. All subledger transactions (POs, Payable Invoices, Receivable Invoices etc.) are secured by Operating units in Oracle EBS 11i. Oracle classifies an Organization as Operating unit by "Operating Unit" type. Organization is defined in Oracle Purchasing Module. An Operating unit can have 1 and only 1 Legal entity and Set of book. Until Oracle EBS 11i, subledger data is secured by assigning Operating unit to each subledger responsibilities via profile option "MO:Operating Unit".

So, these are the key components an Organization structure in Oracle perpective.

Let's see how we can define Organzation structure for our e.g. AMAZON.COM which recently acquired ZAPPOS.COM.

A> Set Of Books
To decide how many SOBs are required for AMAZON.COM's US business, lets answer following questions which would help in determing if we need multiple SOBs,

Does AMAZON.COM and ZAPPOS.COM share the same 3Cs of SOB (Calender, Currency, COA)?
I think yes. It is because we are implmenting for US where functional currency is USD. Calender would be same as AMAZON.COM acquired ZAPPOS.COM so they would share the same AMAZON.COM calender. We can keep same COA as of AMAZON.COM
So, from above analysis we do not need a new SOB for our implementation / merger.

B> Legal Entity
To decide on number of Legal entities, we need to find out how many companies are registered under AMAZON.COM
In this case as AMAZON.COM aquired ZAPPOS.COM, we definitely would have to define two legal entities if AMAZON.COM wants to continue with ZAPPOS.COM as a registered company. If AMAZON.COM decides that both would be only 1 company going forward and there would be no ZAPPOS.COM then only 1 legal entity would be enough. But generally with any acuiqisition company do not close the aquired company immediately for several reasons like brand, reporting requirements, legal reasons etc.

So, here in our e.g., we will have minimum two legal entities. If AMAZON.COM has another registered company under it's parent company then that also needs to be defined. So, the logic behind this is how many companies are registered legally for which you are obliged for financial reporting.

Both the legal entities can have the same SOB that we define in the previous step.

In real life there can be more complex hierarchy depending upon legal requirement, the way companies are registered etc.

C> Operating unit:

For our e.g. we would definitely need minimum of two operating units because we have two legal entities for which we have to do financial reporting. 1 for AMAZON.COM and 1 for ZAPPOS.COM
However we can have more than 2 operating units as well. Think about a scenario where if you want to logically divide AMAZON.COM/ZAPPOS.COM business into east coast and west coast (region wise) and want to secure data by region. Although all are part of same legal entities and share the same SOB but you want to divide by region. In that case you can achieve that by having two operating units each for AMAZON.COM and ZAPPOS.COM
I just mentioned an example. In real world however company does that at country level not on region level within a country.

So, here we saw what is an Organization structure and how we define Organization hierarchy.

Oracle Workflow Monitoring-

There are some known issues in Workflow notification mailer (11i including 11.5.10) due to which the mailer goes down and all the notification email gets stuck in the WF_NOTIFICATIONS table.

To resolve that the workflow notification mailer needs a bounce by the DBAs.

But the question is how to know when it goes down.

So, to achieve that, a simple concurrent program can be designed based on the below SQL query to identify if the notification mailer is down.
The concurrent program can be scheduled to run every half an hour. Email functionality can be implemented in the concurrent program using an email program as mentioned here,

Google "SendMail.Send". You would find bunch of such programs.



SELECT COUNT (1)
FROM wf_notifications wf
WHERE (SYSDATE - wf.begin_date) * 24 * 60 > = p_time_in_min
AND NVL (mail_status, 'NOTMAIL') = 'MAIL'
AND status = 'OPEN';

Change request is getting stuck in the workflow

Symptom: Requestor gets an error notification email with the following subject
"Approval workflow database error occurs while approving Purchase Requisition" and change request gets stuck in the workflow at the below workflow node.

Reason:
Item type: POREQCHA
Activity: "VERIFY_APPROVER_AUTHORITY"
WF Error message: User-Defined Exception
Error stack: Wf_Engine_Util.Function_Call(PO_REQAPPROVAL_ACTION.VERIFY_AUTHORITY, POREQCHA,

Workaround: Retry the WF fixes the issue and workflow moves to the next node successfully.

LTF: There is a Oracle patch for this. Work with Oracle to get the patch.

User gets error while trying to approve the change request WF notification

Issue: User gets following error while approving the change request WF notification,
Error ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "APPS.WF_NOTIFICATION", line 5361 ORA-06512: at line 5

Facts: Oracle apps 11.5.9

Symptom: 1> User gets above error notification in the screen when taking action (when approve button is clicked) on the WF notification
2> After clicking the requisition no longer in the queue.

Facts: 1> It is a PO change request WF approval notification.
2> Requisition has undergone lots of changes. PO Action history has 65+ records for this requisition.
4> Although the error is displayed while approving the notificaiton, but all the updates in the requisition and the PO happens successfully.
PO_REQUISITION_HEADERS_ALL, PO_CHANGE_REQUESTS, PO_HEADERS, PO_LINES_ALL, PO_DISTRIBUTIONS_ALL tables get updated successfully.

Reason: Looks like this is a known Oracle bug. Similar issue can be found in Oracle metalink note id# 579729.1
The WF notification goes into error if the message has more than 32K characters in it.

Steps to Reproduce:
Use this requisition in non prod instance> create a change request and submit for approval>Login as the approver and try to approve it
> When approve button is clicked the above error is displayed in the screen.

Impact: User can ignore this message as this is displayed due to the above Oracle bug.
All the updates happen successfully. PO is updated with the change request detail successfully.

Suggested fix: Open an Oracle TAR to get a fix from Oracle.

How to change Oracle applications user password programmatically

Generally Oracle applications user password is reset/changed through the System administrator> Define user form.
But sometimes developers/testers need to do a quick test using some user's credentials in dev/test instance and in these situations, you lose time if you follow the above procedure just for resetting the user password.

To address this pain, developers can use fnd_user_pkg.changepassword API to change the password of an Oracle application user.

This returns boolean value and has two input parameters,

1. User name
2. Password

Following PL/SQL block can be used to reset password to welcome@123,

begin
if fnd_user_pkg.changepassword('USER_NAME','welcome@123') then
null;

end if;
end;

How to create Workflow roles in Oracle workflow

Following API can be used to create a oracle workflow role,

wf_directory.createrole

WF_DIRECTORY package has many APIs to carry out different workflow user/role related actions such as,


There are other APIs too in this package wf_directory such as,

createuser - for creating workflow user
createAdHocRole - to create adHoc role programmatically
This is specially useful when there is a requirement to send to an external email address like xyz@gmail.com. In such scenario, create a adHocRole and assign this email address and then use the role to send the workflow notification emails.

Images not opening in Markview 170 application

Issue:
User is not able to open image in Markview 170 application. While opening the image attached image pops up.
error reads:
"Markview was unable to open the configuration file. Please contact technical support."

Facts: Following browser version was used to open the image,
IE8
Firefox 3.5
Chrome 4.0
Markview version: v5.10

Reason:
This is a browser version issue. Markview 170 application v5.10 is not supported on IE8, FIREFOX 3.5, Chrome 4.0. v6x onwards are supported in these version.

Fix:
Ask the user to open in supported browser version.
Check the following answer in 170 support site to see the compatible browser version,
http://support.170systems.com/cgi-bin/170systems.cfg/php/enduser/std_adp.php?p_faqid=2284