Inventory Purchasing Quality

First Article Inspections

This video shows how we created a First Article Inspection process at one of my clients. We designed the process from scratch as, although Oracle says in their Quality User Guide that the module can be used for these inspections, we could never figure out how. Oracle Support couldn’t tell us either.

Everything is built with personalizations and alerts, so we weren’t required to customize any seeded code.

If you would like a detailed setup document so you could build it in your environment, you can download it from here.

You’re welcome to use it or modify it at will. If you do improve on it (and I know that’s possible!), please let me know what you did. Thanks.

Project Manufacturing Purchasing

Project Borrow/Payback – Warning at Receipt

A little Project Manufacturing geekery here. PJM allows you to borrow from one project and pay back  at a later date. There’s a nice dashboard that shows which project has borrowed which items from a lending project and functionality in ASCP that suggests a buy to pay back the borrowed item. But there’s nothing that triggers the stockroom to do the payback transaction when they receive this PO.

This personalization was to provide that functionality.

Details of the personalization are below:

The trigger is:

WHERE :rcv_transaction.item_id =b.inventory_item_id
and :rcv_transaction.project = s.project_number
and b.borrow_project_id = s.project_id
and (b.loan_quantity)-(NVL((p.payback_quantity),0))>0
and b.borrow_transaction_id = p.borrow_transaction_id(+)

The message is:

=select ‘This project, ‘||:rcv_transaction.project||’, owes ‘||to_char((b.loan_quantity)-(NVL((p.payback_quantity),0)))||’ of this item to project ‘||sb.project_number||’.Please perfom a project payback transaction.’
WHERE b.borrow_transaction_id = p.borrow_transaction_id(+)
AND b.borrow_project_id = sa.project_id
AND b.lending_project_id = sb.project_id
and sa.project_number = :rcv_transaction.project
and b.inventory_item_id = :rcv_transaction.item_id

Purchasing Technical

Want to find out if your buyers are overriding Program/Blanket Agreement Pricing?

My current client has negotiated pricing for the vast majority of their purchases. On entering an Oracle PO, the negotiated pricing defaults as soon as the item is entered on the PO. The buyer does, however, have the option of overriding this price.

We were asked to develop a query that the internal auditors could use to review these price changes. We found a column in the po_lines_all called base_unit price, which is populated with the first price that is entered/defaulted for a PO line. The unit_price is the price once the PO is approved. By comparing base_unit_price and unit_price, we were able to provide what the auditors wanted.

Here’s the query:

SELECT poh.segment1 “PO #”,
pol.line_num “Line #”,
pov.vendor_name “Vendor”,
papf.full_name “Buyer”,
msi.segment1 “Item”,
msi.description “Description”,
pol.unit_price “Unit Price”,
pol.base_unit_price “Base Unit Price”,
(pol.unit_price-pol.base_unit_price) “Delta”,
poh_a.segment1 “Blanket Agreement”,
pol.creation_date “Creation Date”,
fu_b.user_name “Created By”,
pol.last_update_date “Last Update Date”,
fu_a.user_name “Last Updated By”
FROM inv.mtl_system_items_b msi,
po.po_headers_all poh,
po.po_lines_all pol,
apps.po_vendors pov,
apps.per_all_people_f papf,
apps.fnd_user fu_a,
apps.fnd_user fu_b,
po.po_headers_all poh_a
WHERE msi.organization_id   = 83
AND msi.inventory_item_id   = pol.item_id
AND pol.base_unit_price    != pol.unit_price
AND pol.base_unit_price    != 0
AND poh.po_header_id        = pol.po_header_id
AND poh.vendor_id           = pov.vendor_id
AND poh.agent_id            = papf.person_id
AND fu_a.user_id            = pol.last_updated_by
AND fu_b.user_id            = pol.created_by
AND pol.from_header_id      = poh_a.po_header_id(+)
AND papf.effective_end_date > sysdate

In our case, 83 is the organization_id of the Operating Unit. You would have to substitute your own value.

Purchasing Technical

Warning for Zero Dollar Purchase Orders

It’s an easy thing to do to forget to enter a line price on a PO and go ahead and approve it.

This personalization pops up a warning to the user if they try to save or approve a PO with a price that differs significantly from the current average cost:

The personalization steps are as below:

Purchasing Technical

Outside Processing Assembly Information on Purchase Order

Oracle’s Outside Processing functionality is really slick – no doubt about it – but it lacks one important element in my opinion. There’s nowhere on the Purchase Order that a buyer can see which assembly the outside processing operation is being performed on. It is possible to have an outside processing item for every assembly that you might send out to a third party, but that gets very cumbersome.

My client sends many different PCBs to a contract manufacturer for rework and we set them up with a single OSP Item for this.

The resulting PO shows the WIP Job number and the Operation Sequence (on the Distribuions page), but not the assembly. There is a field marked “Assembly” on the Distributions window, but I’m told by Support that this is intended for Repetitive Schedules only.

We created a Forms Personalization to get us where we wanted to be.

If a PO is created for Outside Processing:

Our personalization changes the Supplier Item field to an RMA Number field:

Once an RMA Number is entered and the PO is saved, The Note To Supplier field is automatically populated:

This information prints on the hard copy PO, alerting the Vendor to both the process and the assembly on which it is to be performed.

The personalization was straightforward:

The code for the Value in the above screen shot is:

=SELECT ‘RMA ‘||:po_lines.vendor_product_num ||’ for ‘||segment1
FROM mtl_system_items_b
WHERE inventory_item_id =
(SELECT primary_item_id
FROM wip_entities
WHERE wip_entity_id =
(SELECT wip_entity_id
FROM po_distributions_all
WHERE po_header_id = :po_headers.po_header_id
AND rownum        <=1
AND organization_id = :po_headers.ship_to_org_id

Inventory Purchasing

RVTII-060 Errors


This has to be one of the most useless and annoying messages we run into in Oracle Applications! You get it when you try to do a receipt and the Receiving Transaction Processor doesn’t know how to handle it.

I’ve found that it’s possible to troubleshoot the issue without logging an SR. All it takes is a little bit of help from your friendly DBA.

You’ll need access to the System Administrator responsibility to set the following profile options at the user level:

  1. RCV : Debug Mode = Yes
  2. RCV : Processing Mode = Immediate
  3. FND : Debug Log Enabled = Yes
  4. FND : Debug Log Level = Statement
  5. FND : Debug Log Module = po%
  6. TP: INV: Transaction Processing Mode = Online
  7. INV : Debug Trace = Yes
  8. INV: Debug Level = 11
  9. INV : Debug File (including complete path) = /usr/tmp/invdebug.log (can be any file name – make it easy for the DBA to find)
  10. PO: Enable SQL Trace for Receiving Processor = Yes

You should completely log out of the application and log back in again to enable the changes to the FND profile options. Retry the failing transaction. It may appear to be successful, but remember that you have changed the processing mode away from Online. The Receiving Transaction Processor will have run in the background and errored. (You can verify this by going to View> Requests.)

Ask your DBA to retrieve the file for you.

You should be able to open it with a text editor such as Notepad. It will be long, so I suggest you do a search on the  word “error” until you find something that looks useful. See the example below:

[18-MAR-09 07:51:54] INV_INTERCOMPANY_INVOICING: MTL_QP_PRICE.Populate_Temp_Table: Calling QP:Bulk insert routine…
[18-MAR-09 07:51:54] INV_INTERCOMPANY_INVOICING: MTL_QP_PRICE.Populate_Temp_Table: No. of records inserted in QP_PREQ_LINES_TMP=2
[18-MAR-09 07:51:54] INV_INTERCOMPANY_INVOICING: MTL_QP_PRICE.get_transfer_price: Initializing control record…
[18-MAR-09 07:51:54] INV_INTERCOMPANY_INVOICING: MTL_QP_PRICE.get_transfer_price: Calling QP:Price Request routine …
[18-MAR-09 07:51:55] INV_INTERCOMPANY_INVOICING: MTL_QP_PRICE.get_transfer_price: Populating QP results …
[18-MAR-09 07:51:55] INV_INTERCOMPANY_INVOICING: MTL_QP_PRICE.Populate_Results: Status_Code=IPL Status_Text=Item 82-00004419 and uom EA not on pricelist
[18-MAR-09 07:51:55] GET_TRANSFER_PRICE: Error from get_transfer_price_for_item

We can see that, in this instance, the receipt failed because of a price list error. (It is a receipt of an intercompany dropship order and the item was missing from the intercompany price list.) Most likely, your error will be quite different, but you can find it by following the same steps.

Make sure that you revert the profile option settings once your troubleshooting is complete.