Sunday, October 26, 2014

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.


Monday, October 13, 2014

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%

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.  

Friday, October 10, 2014

Oracle E-Business R12.2.4

Oracle E-Business Suite 12.2.4 now available which enables high availability for global operations
1. Online Patching for reduced downtime
  • Functionality leverages Oracle 11g R2 Edition-Based Redefinition features to support online patching
  • Patches can be applied while the production system is fully operational
  • Only a short downtime is required to complete patching process
  • The amount of downtime required by any patch will be consistent.
  • Online Patching cycle includes 5 phases.
  • Application is only offline during the “Cutover” phase
  • Online Patching is used to apply all patches in 12.2.
R12.2 Functional  Highlights:
ERP
– Enhanced Labor Costing with Projects & Payroll
– Project Cost Breakdown Structure
– Contract Lifecycle Mgt for Public Sector (CLM): Electronic Contract File, Concurrent Modifications, more
– Channel Revenue Mgt: Simplified HTML Flows
– Global Enhancements
– Supplier Governance & Oversight with SLM
– Comprehensive Contract Mgt with CLM
– Improve Projects-Based Procurement

SCM
– Order Mgt: Selling Subscriptions
– Order Mgt / WMS: Advanced Catch Weight
– WMS: Mobile Personalization
– MES: Electronic Kanban (E-Kanban)
– MES: Express Transact for Serialized Mfg
– SCM: Contingent Worker Support.
– Outsourced Manufacturing for OPM
– Seller’s Workbench: Margin Analysis, Upsell
– Yard Management

Multi-Unit of Measure Fulfillment:

1. R12 Dual Unit of Measure
  • Enter transactions in primary and secondary UOM
  • Allocate in primary UOM but record fulfillment in both UOM
  • Price iu primary or secondary.
2. R12.X Multi UOM  Fulfillment.
  • Fulfill in primary or secondary UOM
  • Allocate & reserve in multiple UOMs
  • Enhanced inventory tracking, invoicing & shipping in multiple UOMs
New HTML Order Managment UI

- HTML Web UI
- User Configurable
- Modern Look and feel
- Tablet Friendly layout.
-Weblogic server for application server
-simplified User flows

New User Interface Features in Release:
1.New skin
2.Rich table interactions
3.Simplified home page
4.Support for gestures on tablets.

The most obvious user interface change for this release is our new skin for both desktops and tablets. The new skin provides a cleaner look and feel in Oracle Application Framework-based pages, with responsive features that give you more space for controlling the application with your fingers on a tablet while saving space on a desktop. Here is the new look on a tablet and on a desktop.

A streamlined touch-oriented, icon-based global header (the top navigation bar on the page). Users can choose whether to display icons, text links, or both by setting a user preference. This feature is one of the results of the usability testing we did last year, where we got user feedback for fine tuning the new look and feel. 
 
New Table Features :

The new user interface enhancements in tables and hierarchical grids (a special type of table that lets you expand and collapse rows, also known as HGrids).

Our new table enhancements provide a much more interactive experience for tables. Customers have been requesting these enhancements for a long time:

• Reordering columns
• Resizing columns
• Hiding and showing columns
• Detaching tables from the base page (except HGrids)
• Scroll horizontally if the contents of a table exceeds the browser width
• Refresh a table by querying for the latest records
• Reset a table back to its original definition

When reordering, hiding, and resizing columns, the changes are specific to each user and will persist until the user changes them again.
These features are available on:
• Desktop versions of OA Framework-based pages
• iPads running Safari
• Android tablets.

Prerequisites:
You must have the following profile options set in EBS 12.2.4:
• Oracle Applications Look and Feel profile option to "Skyros Look and Feel"
• FND: Enable Rich Table Interactions profile option to "True"

The table button bar contains the following new icons:
1. Detach Table
2. Refresh Data
3. Reset Table Settings
4. Table Settings.

Thursday, October 9, 2014

Oracle E-Bus : The XXXX is not a Valid Responsibility for the current user. Please Contact System Adminstrator.

You have have granted a user access to a new web-based responsibility but the middle-tier application servers have not picked up this change.

How to clear the cache on the middle-tier application server's . You have sys admin access and you have givne Web application respsonsibility to your self  example Internenxt Expenses or Workflow Adminstrator or Function Adminstrator  etc and when you select any form or menu you get a error message  "XXXX is not a valid responsibility for the current user. Please contact your System Administrator". where XXXX is "Internet Expenses ",  “Workflow Administrator or Functional Administrator"









  
You only get this issue with Web-based responsibilities. If I'd assigned "Receivables Manager" then it works without any issues, the reason for this error is that in order to improve performance Oracle caches some information on the web server. In order to "fix" this problem we need to clear the cache by following these steps;
 
Step 1: Log in and select the "Functional Administrator" responsibility

Now this is where we get a delicious taste of irony; this responsibility is web-based so if you are trying to fix a problem that's occurring now and you don't already have this responsibility then I'm afraid you're too late. You'll have to bounce the Apache server (something that will require a DBA). In short; you need to have granted yourself this responsibility BEFORE you run into problems!

Step 2: Select "Core Services"
Step 3: Select "Caching Framework" 
Step 4: Select "Global Configuration" 
This page shows you the currently configured Caching Statistics and Policy. "Clear All Cache" button the right-hand side.




Step 5: Click "Clear All Cache"
log out and login and check the web application and it should display the web application responsibility information.