Oracle E-Business Apps Analyzer Diagnostic Scripts

       Financial Analyzers:

Auto Invoice Post-Process Analyzer: Overview and Installation Instructions :

The Auto Invoice Post-Process Validation Report is a Self-Service Diagnostic non-invasive script which reviews the overall status of the Auto Invoice Interface tables, analyzes the validation errors encountered by Auto Invoice program and provides recommendations for resolving the errors.

Auto Accounting Analyzer:

Auto Accounting issues are typically addressed by correcting setup. This analyzer will identify if you have incomplete or incorrect setup for Auto Accounting, and walk you through the navigation telling you what record to query and update to fix the errors.

R12: Payables Create Accounting Analyzer:

Invoice/Payment not getting picked up for Created Accounting? Invoice/Payment accounting with an error? Run the R12 Payables Create accounting Analyzer to find the problem. It checks your ledger and sub-ledger setup for issues. It verifies the transaction and event statuses are correct. It checks for data integrity issues that will prevent accounting.

R12: Payables Period Close Analyzer - Diagnostic to Validate Data Before Period Close :

The PCH works in conjunction with the MGD as the month end. Through the month you detect and fix data issues with the MGD. At the month end the PCH gives you a summary of what other issues may be preventing you closing the Payables Period. In a single run you get aware of transactions pending accounting, unprocessed invoice lines, orphan events and many more typical exceptions that need action ahead of closing the period. The tool indicates exactly what steps you need perform to address each exception type. For convenience this analyzer can be registered as a concurrent process.

R12: EBTax Setup and Data Integrity Analyzer:

This analyzer scans for typical EBTax issues found when processing Payables Invoices (and can be applied to Receivables transactions as well). EBTax issues are very common causes of Invoice Actions (validation, cancel, discard, matching) failing. You can use this tool proactively to screen and correct data problems in EBTax or reactively when having problems performing an Invoice or Transaction action. This analyzer can be used through the month to complement the MGD.

R12: Payables Trial Balance Analyzer - Diagnostic to Validate Data for APTB issues 

The month ends and you need to reconcile. The APTBH’s mission is to help you detect issues that may yield incorrect or inaccurate APTB results. This analyzer will help you identify most (and likely all) transactions that are causing APTB reconciliation issues and provide you the action needed to resolve the problem. The APTBH will also keep you updated on the last patches needed for APTB to work at its best.

R12: Oracle Payments (IBY) Funds Disbursement Analyzer 

Failures in payment processing requests can result in unconfirmed payments which can have an adverse impact on your Period Close. Oracle Payments Funds Disbursement Analyzer minimizes disruptions to your payment process by proactively identifying and addressing any environment (patching) and setup related issues. The Analyzer contains proactive scans to identify stuck payment process requests (PPR's), payments related data anomalies and proposes a Generic Data Fix (GDF) patch to resolve them. The Analyzer can be used as a troubleshooting assistant to address issues related to PPR invoice selection, remittance advice delivery and BI Publisher integration with Oracle Payments .

R12: Internet Expenses Setup Helper - Diagnostic to Validate OIE Setup Data:

This diagnostic will allow you to identify many (possibly all) of the transactions which may cause Internet OIE setup issues with the corrective action suggested to resolve them without requiring you to log a Service Request with support.

R12: Master GDF Diagnostic (MGD) to Validate Data Related to Invoices, Payments, Accounting, Suppliers and EBTax :

The MGD will scan a date range of AP, Payments, Suppliers and eBTax transactions (proactive use) or a single transaction for known data integrity issues. If data integrity problems are found it will recommend the exact GDF needed to fix the data without further confirmation from Support or Development (use of GDFs without confirmation is fully supported). The tool will also provide additional recommendations such as crucial patches and specify which Application flows might be affected by the issues found. Use the tool in date range mode through the month and stay ahead of last.

Order Management Analyzers:

R12: Order Management (ONT) Sales Order Analyzer Diagnostic Script :

This 'Health Check' script can be used at any time to review the data relating to Sales Orders, including : Sales Order Headers and Lines Workflow Issues Drop / Ship Orders Models and Kits Sets Shipping / Delivery Data. Each script used by this report looks for specific symptoms which have been known to cause problems in the past. It can either be run manually in SQL *Plus, or as a concurrent request. The script may be run safely at any time as no data is created, updated, or deleted.

R12: Shipping Execution (WSH) Analyzer Diagnostic Script :

This Self-Service Health-Check script can be used at any time to review the data relating to Shipping including : Trips, Trips Stops, Deliveries and Assignments, Delivery Details and Freight Setup . Each script used by this report looks for specific symptoms which have been known to cause problems in the past. It also lists Open Inventory Periods and WMS / OPM Enabled Warehouses. The script may be run safely at any time as no data is created, updated, or deleted.

Procurement Analyzers:

R12: PO Approval Analyzer Diagnostic Script

The PO Approval Analyzer is a script that you can use proactively to prevent known issues in Purchasing as well as to collect data to troubleshoot approval problems with a single document or group of documents.

R12: IP Item Analyzer Diagnostic Script 

The IP Item Analyzer is a script that you can use proactively to prevent known issues in iProcurement as well as to collect data to troubleshoot items not found in iProcurement search with a single document or group of documents.

R12: EBS Procurement Encumbrance Accounting Analyzer

The Procurement Encumbrance Accounting Analyzer checks for known funds related errors and provides solutions. It is commonly used to diagnose incorrect encumbrance values found for a Purchasing document.

R12: EBS Procurement Accrual Reconciliation Analyzer 

The Procurement Accrual Reconciliation Aanlyzer is a script that can be used when encountering accruals issues on Purchasing documents. The single document check prints all the information needed to troubleshoot the problem with the accrual reconciliation process.

Reference : Oracle Support Note# 1545562.1

Globalization Profile Options

if you are setting up country-specific globalization and you use custom responsibilities, then you may need to know about the profile options you would set up for a non-multi-org product such as Fixed Assets.  For each custom responsibility that uses windows with country-specific or regional features that belongs to a non-multi-org product. you must set the JG: Application, JG: Territory, and JG: Product profile options.
 Note:  You do not need to set these profile options for multi-org products such as Payable and Receivables, which make use of the organization field.
These globalization profile options are:

• JG: Application:

Used to determine which Oracle Applications product the responsibility is associated with.  The list of values for this profile option consists of a complete list of Oracle Applications products.

• JG: Territory:

Used to determine which country the responsibility is associated with.  The list of values for this profile option consists of a list of countries.

• JG: Product:

Used to determine which Global Financials product the responsibility is associated with.  The list of values for this profile option consists of a list of Global Financials products.

You will find additional information about globalization setups in the Oracle® Financials Country-Specific Installation Supplement.

Oracle VirtualBox(VBox)

Virtual Box is a powerful x86 and AMD64/Intel64 virtualization product for enterprise as well as home use. Not only is VirtualBox an extremely feature rich, high performance product for enterprise customers, it is also the only professional solution that is freely available as Open Source Software under the terms of the GNU General Public License (GPL) V2
Presently, VirtualBox runs on Windows, Linux, Macintosh, and Solaris hosts and supports a large number of guest operating systems including but not limited to Windows (NT 4.0, 2000, XP, Server 2003, Vista, Windows 7, Windows 8), DOS/Windows 3.x, Linux (2.4, 2.6 and 3.x), Solaris and OpenSolaris, OS/2, and OpenBSD.

VirtualBox is being actively developed with frequent releases and has an ever growing list of features, supported guest operating systems and platforms it runs on. VirtualBox is a community effort backed by a dedicated company: everyone is encouraged to contribute while Oracle ensures the product always meets professional quality criteria.

Oracle EBS Upgrade Factory

Oracle E-Business Suite (EBS) Upgrade Factory:
Oracle has a commitment to help customers benefit from the latest technology, and has developed a packaged offering to help customers upgrade to Oracle E-Business Suite Release 12 in the cloud, called E-Business Suite (EBS) Upgrade Factory.

Several recent surveys detail why CFOs are increasingly open to moving their enterprise applications into the cloud. For example, a 2012 survey by Financial Executives Research Foundation (FERF) and technology advisory firm Gartner found that 53 percent of CFOs believe that more than half of their enterprise transactions will be delivered through software-as-a-service over the next four years, up from 12 percent today. In addition, almost 70 percent of CFOs surveyed in 2012 by Oracle Corp. said they would consider moving to a cloud-based version of their core enterprise software.


The  Oracle EBS Upgrade Factory was built to be the fastest and lowest-risk way possible to move from a Release 11 on-premise EBS instance to an on-line Release 12 instance. This offering provides a comprehensive, end-to-end process for moving customers into the cloud and upgrading them to the latest release.

The EBS Upgrade Factory includes the following project phases, all bundled into a single package:

• Migration to the Managed Cloud Services Environment
• Technical Upgrade : includes upgrade of the platform, Oracle Application Server, Oracle Database, Oracle Forms and Reports, and Oracle Applications.
• Customizations, Extensions, Modifications, Localizations and Integrations (CEMLI) Upgrade : includes determining technical impact of Oracle E-Business Suite Release 12 on CEMLIs, upgrading CEMLIs to the new technology stack, retrofit of CEMLIs for compatibility and usability on Oracle E-Business Suite Release 12, and assistance in resolution of issues with CEMLI execution.
• Functional Upgrade : includes the basic steps needed to configure and set up the new system, and orientation around new functionality. This is a very lean and cost-effective approach to functional upgrades. Oracle Consulting provides additional support for customers who wish to change their business flows.
•Functional Testing : includes applying Oracle’s expertise to assist customers in updating their Oracle E-Business Suite test scripts to reflect valid navigation paths for Oracle E-Business Suite Release 12.
•Run and Maintain Services :Oracle Managed Cloud Services runs more instances of Oracle Applications and Oracle operating systems than any other provider in the industry

Source: Oracle.com

How to change Valuation Accounts, Cost Variance accounts due to Merger, transformation and dissolutoin of business entities.

What are the options available in Oracle EBS when the material account code, valuation account code etc needs to be changed due to Merger, transformation and dissolutoin of business entities as Standard functionality appears to lock down the valuation accounts on the organization setup form.

This is the intended functionality.The valuation account field defined under costing tab in organization Parameter is not editable and under other accounts . User cannot amend the Material Account, Cost Variance Account and Expense Account through the Organization Parameters form if on-hand or history exists and the Organization is using Average Costing. This is the intended functionality. When using Average Cost method, the Material Valuation Account cannot be changed.

The valuation account field defined under costing tab in organization Parameter is not editable. When needing to change the account information defined for accounts like:- Outside processing. Material overhead. Resource. Expense etc The Accounts cannot be changed once the setup is done and if ANY transactions exist.

As changing the account association will have major repercussions on the distributions that are created for the transactions in the INV Sub-ledger and GL if you changed the accounts after transactions has been made you will not be able to close the accounting period.

When needing to change the account information defined for accounts like:- Outside processing, Material overhead, Resource, Expense, Cost Variance Accounts.
The Accounts cannot be changed once the setup is done and if ANY transactions exist.
As changing the account association will have major repercussions on the distributions that are created for the transactions in the INV Subledger and GL  if you changed the accounts after transactions has been made you will not be able to close the accounting period.

Option1: Create New Organziation standard process:  
1. Standard advice is to create a new Inv Org, with the required valuation accounts ,assign all the items to it that are in the existing org, transfer item quantities to the new org, create a new all POs and sales orders, WIP jobs, etc.

2.When you define the new inventory org, the system will assign all of your organization level valuation accounts to a cost group. If you are not using WMS, this is the only cost group that can be used. Under standard costing your subinventories can have different valuation accounts (and hence different cost groups), but in average costing this is only possible if using WMS . Be sure your client is aware of this restriction if they currently have different accounts for different subinventories.

When WMS is installed or for WMS enabled organization, subinventory valuation accounts get the values from organization level if the costing method is standard costing and does not allow the user to update / modify the accounting segments directly on the subinventory level.

3.The transfer should be planned for ahead of time to close all possible work orders in the old organization and then open new ones in the new average cost organization. Don't try to open a half finished work order in the new org - finish it out in the old.

4.If you have any open sales orders or purchase orders, you will have to update the pick from and deliver to organizations in the SOs and POs. Note that in an average cost org, there will not be any purchase price variances since there are no standards. Invoice price variances will continue to be calculated.

5.Once all activity is transferred, do the month end close process in the old org, and then deactivate it. If you are using Organization Access, be sure to unlink responsibilities from the old org and tie them to the new org when you are ready to commence using it.

6.Be sure that your client is made aware of the need to monitor costing activity in the new org.  Transactions are costed in date/time sequence, and if a transaction is 'stuck' and cannot be costed, no later transactions will be costed until the cork is removed from the bottle.  Transactions should be brought over to the general ledger at least once a week so that uncosted transactions can be caught and resolved early. Having uncosted transactions will prevent them from closing the month.

7. Be sure that if there are any custom program or customization related to that OLD org is pointed to the new Org.

a.There is no on-hand quantity.

- select organization_id, inventory_item_id, primary_quantity 
  from mtl_onhand_quantities_detail
  where organization_id = &orgid and subinventory_code = '&subcode';

b.There are no un-costed transactions.

 - select count(*)  from mtl_material_transactions
   where organization_id = &orgid
   and subinventory_code = '&subcode'
   and costed_flag is not NULL;

  c.There are no pending transactions

-select count(*)
 from mtl_transactions_interface
 where organization_id = &orgid
 and subinventory_code = '&subcode';

- select count(*)
  from mtl_material_transactions_temp
  where organization_id = &orgid
  and subinventory_code = '&subcode';

Option2: Use Copy Inventory Organziation feature and change the Valuation accounts using update script.
1.Unassign the location which is assigned to the eixsting org to copy to a new inventory org, if this is not done prior to running the copy inventory organzation you will not see the location name in the Location name in the maintain Interface form.

Responsibility: US Inventory Super user
Navigation: Setup > Organization > Organization Copy > Maintain Interface

When you define new organizations in the Copy Organization Interface window, new inventory organization records are saved in the Copy Organization Interface table.
The following information describes the required parameters to create new organizations:

• Group Code
• Organization Name
• Organization Code
• Location Name.


1. Enter a Group Code to identify your new organization records.
The Group Code is used by the Copy Inventory Organization concurrent program to identify and group together all the individual organization records that will be processed in a single concurrent request.

2. Enter an Organization Name for the new organization. The Organization Name is the name of the organization that will be created by the Copy Inventory Organization concurrent program.

3. Enter an Organization Code to identify your new organization. The Organization Code is a code that the Copy Inventory Organization concurrent program will use to uniquely identify the new organization.

4. Name an existing location for each organization that you want to create, or define new locations in the XML template. If you are using existing locations, they must not already be assigned to an organization. If you create a new location in the XML template, the location definition must precede the related organization definition.
for more information please go through the copy inventory organzation implementation guide.

Note: Cost type and cost sub-element data in the Inventory Organization parameters. Copy of Process enabled, WMS enabled, and cost sharing organization entities are not supported.
 Copy inventory organization: Execute the Copy Inventory Organization Concurrent Program to create the new inventory organizations that you previously defined.

Model Organization:Choose an inventory organization from the list of values as your model organization. This organization is used to derive all inventory parameters that you wish to copy.

Group code:Choose a Group Code from the list of values. The group is a collection of individual inventory organization records that you wish to process in a single concurrent request.

Assign to Existing Hierarchies:If yes, all new organizations will be assigned to the same hierarchies as the model organization.
If no, the organizations will not be assigned to the model organization’s hierarchies.
You can run the Hierarchy Exceptions Report to list organizations that have not been assigned to hierarchies.

Copy Shipping Networks: If yes, all the model inventory organization’s Shipping Network setup information will be copied to the new organization.
If no, the shipping network setup will not be copied.

Copy items:If yes, all items activated in the model organization will be copied and activated in the new organizations If no, no items will be copied. Item assignment is a one time process and can not be undone. There is a separate concurrent program that can be run to assign items.

Copy BOM:If yes and if Bills of Material have been created in the model organization, all BOMs will be copied to the new organizations. If no, Bills of Material will not be copied.

Copy Rountings: If yes and if routings exist in the model organization, all routings are copied to the new organizations. If no, routings are not copied.

Purge:If yes, all records from the Interface Table will be purged.
If no, the records will not be purged. Records with Fail will not be purged even if you have selected yes.

How to change Inventory  Valuation accounts for an old inventory organization that were are no longer used for inventory.
Please do appropriate backup procedures. Retest the issue in a test instance; verify if the issue was resolved before promoting to Production.

1. For the organization which doesn't use any inventory but has invalid account use the following update sql statement to update the accounts:

update mtl_parameters
set MATERIAL_ACCOUNT = &new_material_account_id,
MATERIAL_OVERHEAD_ACCOUNT = &new_moh_account_id,
OUTSIDE_PROCESSING_ACCOUNT = &new_osp_account_id,
RESOURCE_ACCOUNT = &new_resource_account_id,
OVERHEAD_ACCOUNT = &new_overhead_account_id,
EXPENSE_ACCOUNT = &new_expense_account_id where organization_id = &organization_id;


2. For the cost group accounts for the organization in question, please use the following update sql statements to update the accounts if the accounts are invalid :

update cst_cost_group_accounts
set MATERIAL_ACCOUNT = &new_material_account_id,
MATERIAL_OVERHEAD_ACCOUNT = &new_moh_account_id,
OUTSIDE_PROCESSING_ACCOUNT = &new_osp_account_id,
RESOURCE_ACCOUNT = &new_resource_account_id,
OVERHEAD_ACCOUNT = &new_overhead_account_id,
EXPENSE_ACCOUNT = &new_expense_account_id
where organization_id = &organization_id and cost_group_id = &cost_group_id ;

The cost group ids can be retrieved from the cost group accounts form. Before doing this change make sure that no values exist in GL for the old accounts .

3 If you want the new cost variance account to be used in costing of future transactions, using a simple SQL statement to update mtl_parameters & cst_cost_group_accounts tables should be fine.

update mtl_parameters
set AVERAGE_COST_VAR_ACCOUNT=&your_ccid
where organization_id=&your_org_id;

update cst_cost_group_accounts
set AVERAGE_COST_VAR_ACCOUNT=&your_ccid
where cost_group_id=&your_group_id;

commit;

Reference: support.oracle.com ,#158720.1,#565995.1,#1469836.1,#746326.1,#1056675.1,#1144105.1,# 568078.1.

Oracle Integration Repository (IREP)

Oracle Intergration Repository:
The Oracle Integration Repository is a compilation of information about the service endpoints exposed by the Oracle E-business Suite of applications.

It provides a complete catalog of Oracle E-Business Suite’s business service interfaces. The tool lets users easily discover and deploy the appropriate business interface for integration with any system application.
The Oracle Integration Repository is shipped as part of the E-Business Suite. As the instance is patched, the repository is automatically updated with content appropriate.

The APIs were listed in the user guides. Since the Release 12, they are not anymore but you read instead to refer to the integration Repository (IREP) :

Oracle Intergration Repository, an intergral part of oracle E-business suite is a compliation of information about the numerouse interface endpoints exposed by Oracle Applications.


The full list of public API's and the purpose of each API is available in the intergration repository.
For information on how to access and use Oracle Integration repository see accessing Oracle Intergration Repository User guide.

How to Access IREP:

1. From E-business suite:
Responsibility: Intergration Repository
Note: user should have enough privileges like SYSADMIN

2. From the web: url: http://irep.oracle.com

How to use integration Repository:

There are couple of ways to use the intergration repository:

1. Navigation through the catalog
2. Use of the search page.

Price Hold Expected As It Exceeds Price Tolerance But Invoice Not On Hold.

Issue: Invoice unit price -> (Purchase Order (PO) unit price x % tolerance) but invoice is not going on price hold. As a result the supplier has been overpaid as the invoice passed the approval process.

Invoice Price Tolerance = 5%
PO price = .70 PO qty = 1
INV price = 19.20 INV qty = 1

The current functionality in Accounts Payable is not clearly documented. The current documentation states that PRICE HOLD will be place on invoice if:

INVOICE UNIT PRICE >  (PO UNIT PRICE x (1 + % tolerance))

UNIT_PRICE in AP_INVOICE_DISTRIBUTIONS > PRICE_OVERRIDE in PO_LINE_LOCATIONS (PRICE Hold)
Weighted average price of all distributions on the matched invoice and all price corrections related to the invoice is more than
[purchase order unit price (1 plus% tolerance)]

Unit Price - P.O./Invoice. Unit price of the item from the purchase order line/invoice. Payables compares the unit prices for a purchase order and matched invoice and applies a Price hold to an invoice distribution if the invoice unit price exceeds the purchase order unit price by more than the tolerance level you allow.

Price. The average price of all matched invoices exceeds purchase order price.

System uses average unit price in ap invoices and po unit price to calculate price variance.
Variance = (Inv amt/qty) - (price tolerance * unit price)

If unit price is with lots of decimals. For these cases users can define tolerance with some minimal positive percentage(0.5 to 1).
So system does not place price hold with this tolerance.

Responsibility: Accounts payables super user

Navigation: Setup -  Invoices - Tolerance
Check the PO matching zone what is price%

Scripts

a) select * from PO_headers_all

where segment1 in (301977,272062); ----- PO#

b) select * from po_lines_all

where po_header_id in (1149476,1184290); ---- get the po_header_id from the above script.

c) Select SUM(NVL(PD.quantity_billed,0)), (SUM(NVL(PD.amount_billed,0)))

FROM po_distributions_all PD

WHERE PD.line_location_id in (2704791);

d) Select sum(D.quantity_invoiced), sum(nvl(D.amount,0))

FROM ap_invoice_distributions_all D,

po_distributions_all PD

WHERE D.po_distribution_id+0 = PD.po_distribution_id

AND PD.line_location_id in (3129997);

e) Select SUM(NVL(PD.quantity_billed,0)), (SUM(NVL(PD.amount_billed,0)))

FROM po_distributions_all PD

WHERE PD.line_location_id in (3129997);

Calculate the average unit price = Amount Billed/Quantity Billed.

PO#301977 – unit price is 247.5

AUP= 495/95 = 5.21053

The PO price is being compared to the AVERAGE price of ALL THE INVOICES MATCHED to the PO. So if the average price of all the invoices is greater than the PO price and the tolerance, then the

PRICE hold will come on, even if the current invoice is equal to or less than the PO price.

In both the above cases the AUP is greater than the PO price and the tolerance.

Run the APLISTH script which will provide more details , once you run the aplisth script you can make a note of the columns like invoice_id, Line_Location_ID  so that you can run tbe below script to check further.

SELECT AID.invoice_id, PD.line_location_id SHIP_ID,
DECODE(SUM(nvl(quantity_invoiced,0)),
0, 0,
null,0,
(nvl(sum(decode(nvl(unit_price,0)*nvl(quantity_invoiced,0),0,amount,
nvl(unit_price,0)*nvl(quantity_invoiced,0)
)
),0) /
sum(nvl(quantity_invoiced, 0)))) avg_price
FROM ap_invoice_distributions_all AID,
po_distributions_all PD
WHERE ( AID.parent_invoice_id =
OR AID.invoice_id = )
AND AID.po_distribution_id = PD.po_distribution_id
AND PD.line_location_id =
AND AID.line_type_lookup_code = 'ITEM'
GROUP BY AID.invoice_id, PD.line_location_id;

References:

A documentation Bug 1149642 has been logged to update the current documentation to be clear and concise. In addition, an enhancement Bug 1149668 has been been approved by development to have a setup option so users can have the price hold be per invoice or for all invoices for a PO.

Source: support.oracle.com, AP User guide, etrm guide.