SOX Compliance – Booking Sales Orders

In the past, I’ve removed the ability to book a sales order from the basic order entry responsibility and then created a Supervisor responsibility for review/booking purposes.

My client has a business process where sales orders are peer-reviewed prior to booking. A user can book any sales order except for one that they have entered.

We set this up in Oracle with some forms personalizations as follows:




The system recognizes the creator of a sales order and disables the “Book” button while they are in that order.

There is a “back door” that you have to watch out for; you can book a sales order from the Actions button on the Order Organizer:


Oracle recommends disabling it as below:





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 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

Auto-hiding the Receipt Header

If you enter header information on all of your receipts, having the header pop up when you enter the Receipts form makes sense. If you don’t – and I’ve found this to be the case for the vast majority of my clients – then the pop-up is an annoyance. This is a personalization we built to hide it automatically.

Item Shortage Checks & Project Manufacturing

We were wondering why we only got shortage warnings some of the time when we were doing PO receipts. A Service Request and many trace files later, Support let us know that were were experiencing intended functionality. The shortage checking functionality considers total on-hand and does not consider projects at all. So, if you have none on hand in your project’s inventory and have a shortage on a job for that project, you will not receive a shortage message during PO receipt if inventory exists in another project.

We logged an enhancement request, but we know how they go…

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.

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:

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

Standard Cost Items in an Average Cost Organization

My client had two conflicting needs, to cost the majority of their items at average actual, but to track around 100 items at a fixed, standard cost even though they were bought at market price through the year. Our original solution was to create two inventory organizations, one standard cost and one average cost. The standard costed items were always received into the standard cost organization and then transferred into the average cost organization.

Although this worked from a costing perspective, the transactional overhead was high. Each transfer involved an internal requisition and an internal sales order. Because all of the items were serialized, much manual input was required.

We were looking for a way to collapse the standard cost organization into the average cost one so that we could eliminate the transactional load. Tom Concialdi, Senior Sales Consultant at Oracle and all-around Manufacturing Guru, pointed us toward the Transaction Cost Extension. This is a stub program that is called whenever the Cost Manager runs. If there is code in the Transaction Cost Extension, it will be executed at that time.

We chose to build a Quotation in Purchasing to hold the standard cost information for our 100 items. We created a dummy Supplier for the purpose and then listed all of the items in the quote with their fixed cost. When the Transaction Cost Extension is called, it is coded to go and look to see if the subject item of the uncosted transaction is on our quote. If it is, the value from the quote is returned and the transaction is costed at this value rather than the original cost that was entered or derived.

We looked at different places to store the fixed cost information; creating a new Cost Type, creating a Lookup etc. We chose the Quotation form because Purchasing maintained the information and were familiar with the form already, and because it contained effectivity dates.

The coding was relatively simple and it works beautifully. All transactions are reset to the fixed, standard cost at the time that they are costed. Because we can be assured that all transactions will be at the same cost, we have no need for the second organization and its associated overhead.

Un-commoning a BOM

Oracle Support tells us (in Note 878976.1) that the system does not support this functionality, although an enhancement request has been logged.

We were in a situation where we had no choice but to un-common some BOMs. We found this script to be effective:

UPDATE bom_structures_b
SET common_organization_id     = NULL ,
common_assembly_item_id      = NULL ,
common_bill_sequence_id      = bill_sequence_id ,
source_bill_sequence_id      = bill_sequence_id
WHERE source_bill_sequence_id != bill_sequence_id

This script as written will un-common all BOMs. Obviously you can limit it to specific BOMs if you want to be more targeted.