Oracle Purchasing – Purchase Order FAQ

What is 2-way, 3-way, 4-way matching? How is this set-up?

Answer

2-way matching verifies that Purchase order and invoice information match within your tolerances as follows:

Quantity billed <= Quantity Ordered
Invoice price <= Purchase order price
(<= sign is used because of tolerances)

3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:

Quantity billed <= Quantity received

4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:

Quantity billed <= Quantity accepted.
(Acceptance is done at the time of Inspecting goods).

Whether a PO shipment has 2-way, 3-way or 4-way matching can be setup in the Shipment Details zone of the Enter PO form (character)

Receipt required  Inspection required       Matching

Yes                            Yes                                         4-way

Yes                            No                                           3-way

No                              No                                           2-way

In GUI, Shipments block, alternative region, click on More…this brings up a region with ‘Invoice matching’ column where you can choose 2-way, 3-way or 4-way match.

How do you reflect discount from a supplier on a Purchase orders?
Answer
The legal document is the Purchase order not the quote and therefore the purchase order should reflect agreed upon price.  Secondly if the discount comes after the PO then AP should handle it when invoicing.  If you are using a blanket Purchase order then you can use price breaks.


How do you change the created date on a Purchase Order?
Answer
The 'Created' date on the Purchase order is not an updateable field, due to it being a legal document once approved.


What is the sorting order for requisition lines in autocreation of a Purchase Order?
Answer
Requisition lines first meet search criteria determined by "find" screen, and then are grouped together by item and item category, then by need-by date. After you choose an action, enter any search criteria to narrow the selection of requisition lines.  Oracle Purchasing chooses requisition lines from the requisition pool that meet your search criteria.  All requisition lines for the same item and item category are grouped together, but they are not necessarily in order alphabetically.  Within each grouping of item and item category, requisition lines are ordered by need-by date.


What does the error ‘APP-50022: Oracle Human Resources could not retrieve a value for User Type profile option’ mean?
Answer
The profile HR: User Type needs to be set. Set the profile option ‘HR: User Type’ at the Responsibility level

When do you see the status of a Purchase order as Archived?
Answer
You must approve or print a specific purchase order before you can see it in the history information.


In Oracle Purchasing, where is the automatic numbering for Purchase Order defined and maintained?
Answer
The navigation path for GUI is:
Setup --> Organizations --> Purchasing Options, Numbering alternate region


Oracle Workflow – Account Generator Process

What does the Account Generator process do?
The Account Generator process builds charge, budget, accrual, and variance accounts for each purchase order, release, and requisition distribution based on the distribution’s destination type. It is a synchronous Workflow process.

What are the Pre-requisites to use Account Generator?
Before using the Account Generator you must:
- Define your Accounting flexfield structure for each set of books.
- Define flexfield segment values and validation rules.
- Set up Oracle Workflow.
- Decide whether you want to use the Account Generator processes as seeded in Oracle Purchasing, or you need to customize them to meet your accounting needs

What are the steps to generate the WFSTAT output for Account Generator?

A: 1. Set the following profiles:
- Account Generator: Run in Debug Mode=> Yes
- Account Generator: Purge Runtime Data=> No
PO: Set Debug Workflow On => Yes
2. Make sure that the concurrent program “Purge Workflow Obsolete Runtime Data” is NOT running.
3. Open Purchase Order/Requisitions form, go to the distributions window, enter necessary fields and click on charge account field to start generating the charge account. After the account generator has done building the account, or errors out, do the following from the toolbar (DO NOT exit the form or navigate to any other block or record, otherwise this information would be lost):

Help=> Diagnostics => Examine
Enter in 1st field => parameter
Enter in 2nd field => charge_acc_wf_itemkey

Then tab out. The Item key will appear in the third field.
4. Now save the purchase order/requisition. If you are not able to save, then clear the distribution record and navigate back to the shipment window and save the form. Saving the form is must, because a commit is required to save the workflow information in tables, for generating the wfstat output.
5. If step#3 could not give you an item key, then use the following query to identify the relevant item key:

Select item_key, item_type, begin_date
from wf_items
where item_type = ‘&item_type’
order by begin_date desc;

For PO, use ‘POWFPOAG’ item type in above query, and for requisition, use ‘POWFRQAG’.
6. To generate the WFSTAT output,
Run the sql in $FND_TOP/sql/wfstat.sql with above item_type and item_key. Spool the output.
7. To get the wf debug information, run the following query:

SELECT item_key, item_type, debug_message
FROM po_wf_debug
WHERE item_key = ‘&Item_key’
ORDER BY execution sequence;

How do you determine the WF Item Key value to run wfstatus.sql for Account Generator workflows?
The item key is required in order to run wfstatus.sql for account generator. The wfstatus.sql gives important debug messages, including error messages for processes that are erroring out. This provides the same information that the Workflow Monitor does, if the Workflow Monitor is setup.
Steps to determine the WF Item Key value:
The item key can be found in the Workflow–>Status form in Projects (Querying by item type and dates) or, more generally, via SQL*Plus from the table WF_ITEMS. A sample script for getting the key is below:

Select item_key, begin_date, end_date
from wf_items
where item_type = ‘PAAPINVW’
and begin_date between ‘&low_start’ and ‘&high_start’;

If this does not definitively identify your item you can query on the various items in the Workflow status form to give you more information about the activities associated with them.

How do I save my workflow definition to the database?
There are three options to use when saving your workflow definition to the database:

Option #1

If you have the file open in workflow builder select File -> SaveAs -> Database. Enter the apps username and password and provide the connection string (from the tnsnames.ora in the %ORACLE_HOME%\network\admin directory).
Option #2

The WFLOAD utility is used for loading and extracting workflow definition files to and from the database.

Command Line Syntax:
WFLOAD 0 Y {UPLOAD | DOWNLOAD | UPGRADE | FORCE}
[filepatch [item type]]</APPS pwd>

NOTE: The item type parameter is used only for the DOWNLOAD mode.
Option #3

Run the concurrent program, Workflow Definitions Loader. The system administrator will need to add this concurrent program to a request security group for the responsibility that you want to run this program from.

 

How do I trace errors in my Workflow processes?
A: If a customized workflow process errors you should check the error stack (using the Workflow Monitor and/or wfstatus.sql script), if the procedure in error does NOT begin with WF_.. OR _… then you need to debug your own pl/sql procedures.
If standards in the Workflow Guide for exception handling have not been followed, you can still tell whether the problem is with your code, because Oracle Workflow traps external exceptions and reports these as

Activity Status: ERROR
Activity Result: #EXCEPTION

This is what would show up in the workflow monitor or any status report or form inquiry for the failing activity.

 

How do I customize the Account Generator Workflow process for my company?
A: Customers are expected to customize the Account Generator process. You can do this by editing the existing process or creating your own process, but this MUST be within the seeded ‘Item Type’. You should NOT create a NEW ‘Item Type’ as this would NEVER be run by Account Generator.
If you create a new process, you must ensure it is “Runnable.”
You must then assign the appropriate process to the Accounting Flexfield structure in the Account Generator Process window in Oracle Applications (login as System Administrator, Application -> Flexfields -> Key -> Accounts and query up the required Accounting flexfield structure).

Accounts Payable Trial Balance – Reconciliation with GL

Q:  What formula should I use to balance AP to GL?

A:  Use the following as an example of how to balance. In this example, you are closing your accounting period for April and you have just posted your final invoice and payment batches to your general ledger system.

To reconcile your accounts payable activity for April, make the following calculation:

“Accounts Payable Trial Balance” as of March 31
+   “Posted Invoice Register” for the period between April 1 and April 30
-    “Posted Payment Register” for the period between April 1 and April 30
=   “Accounts Payable Trial Balance” as of April 30

Reconciling AP to GL is accomplished with the use of the following reports.

“Posted Invoice Register”

“Posted Payment Register”

“Accounts Payable Trial Balance” (current and last period)

These reports ensure that your Trial Balance accurately reflects your accounts payable liability by matching the Posted invoices and payments with the AP liability account. You can also compare your AP liability accounts to GL by doing a query of the accounts in GL to identify the account or accounts out of balance. The trial balance total should be the same as your GL liability account.

If not:

Run the GL “Account Analysis” report for the liability account and for the date range in question. Look for transactions with a source other than Payables. This can quickly pinpoint any transactions incorrectly charged to the account. Make sure that you have not made manual

journal entries to your liability account in General Ledger.When you identify the accounts you go back to AP and do a query on the account to find the invoices out of balance.

The last step is to create a journal entry in GL to balance the account or accounts that is out of balance.

Q: How is the as-of-date used in the “Accounts Payable Trial Balance” report?
A: The as-of-date is used to determine which invoices and payments should be included on the report.  Any invoices or payments with an accounting date AFTER the entered as-of-date will not be displayed on this report.

Very Common Oracle Apps DBA Questions

I collection few regular asked question for Oracle Apps DBA to hire Apps DBA for my team, now publishing for reader’s use. These question are even relevant for Oracle Technical experts / developer also.  I would like to request share your questions if you have any.

Q. What is difference between fresh database and vision database install types ?
Fresh Database – Database is installed with Apps but with no data
Vision Database – Database installed with Apps with dummy data

Q. Whatis US directory in $AD_TOP or under various product TOP’s .
US directory is default language directory in Oracle Applications. If you have multiple language Installed in your Applications then you will see other language directories beside US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

Q. Whats main concurrent Manager types.
ICM – Internal Concurrent Manager which manage concurrent Managers
Standard Managers – Which Manage processing of requests.
CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.

Q. Where is Concurrent Manager log file location ?
By default standard location is $APPLCSF/$APPLLOG , in some cases it can go to $FND_TOP/log as well.

. List some ad utilities and their functions?
adadmin, adctrl, adpatch, adlicmgr, admrgpch, adsplice and adworker

Q. List out the modules related to oracle Apps DBA ?

FND – Application Object Library, AU – Application Utilities, AD – Application DBA

Q. How many concurrent mangers can be used ? what is the limit ?
There is no such documented limit on number of concurrent managers, as long as you have enough operating system resource (memory).

Q. How would you check if the tablespace is in backup mode ?
- Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;

Q. What is “Custom TOP” in apps ?
- Custom top is similar to other tops under APPL_TOP but containing custom (client) specific functionality. Apps DBA need to define custom TOP and register all custom code under CUSTOM TOP

Q. if you find that under $FND_TOP/secure directory, there are many .dbc files, how would you know which is the correct one ?
– System picks up dbc file based on profile option value “Applications Database ID“

Q Where is applications start/stop scripts stored ?

Ans: $COMMON_TOP/admin/scripts/$CONTEXT_NAME

How to confirm if Report Server is Up & Running ?

Ans: Report Server is started by executable rwmts60 on concurrent manager Node & this file is under $ORACLE_HOME/bin .execute command on your server like
ps -ef | grep rwmts60
You should get output like
applmgr ……. rwmts60 name=REP60_VISION
where VISION is your Instance name.
Else you can submit a request like “Active Users” with display set to PDF, check output & log file to see if report server can display PDF files

How you will start Discoverer in Oracle Apps 11i ?

Ans: In order to start dicoverer you can use script addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME
or startall.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)

How to check number of forms users at any time ?

Ans: Forms Connections initiate f60webmx connections so you can use
ps -ef | grep f60webmx | wc –l

What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD ?

Ans: 0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.
‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

Q. What are .ldt & .lct files which you see in apps patch or with FNDLOAD ?

Ans: .ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

Q. What is dev60cgi & f60cgi ?

Ans: cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi

Q. How to compile an Oracle Reports file ?

Ans: Utility adrepgen is used to compile Reports. Synatx is given below

adrepgen userid=apps\ source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character