TheAppsFactory

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:

(SELECT COUNT(*) FROM
PJM_BORROW_TRANSACTIONS b,
PJM_BORROW_PAYBACKS p,
PJM_SEIBAN_NUMBERS s
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(+)
)>0

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.’
FROM PJM_BORROW_TRANSACTIONS b,
PJM_BORROW_PAYBACKS p,
PJM_SEIBAN_NUMBERS sa,
PJM_SEIBAN_NUMBERS sb
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

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.

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…

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:

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

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:

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

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.

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.

This is new functionality in R12 that we’ve been using at my current client. We wanted to create a “Putaway” locator in the stockroom that we could use for WIP job completions. Manufacturing would complete into this locator, then the Stockroom folks could transfer it from there to a more permanent storage.

We found that this worked well, but WIP Pick Release was allocating material from Putaway rather than the permanent storage location, so we wanted to make the Putaway locator non-reservable.

Using the new Material Status Definition form, we created an NRL status as below.

We then created the Putaway locator and assigned it this status.

Should work fine, right? Almost. There’s a “secret” profile option that has to be set before it all comes together:

I’m honestly not sure why this profile option exists. I can’t imagine a situation where a user would create additional material statuses and then not use them. I also can’t understand why Oracle would ship the application with this profile option set to null, and why null is interpreted as No. But that’s how it is…

I’ve been working with Support, Development and Product Management for the last few weeks to clarify some of the functionality around Project Manufacturing and how it integrates with other modules.

My client is using Advanced Supply Chain Planning, Work in Process and Inventory in a Project Manufacturing environment. We’ve seen that, by setting the Reservation Level to Planning Group in the ASCP options, we can use excess supply from other projects within the planning group for a particular project. When we release the job created from the Planner Workbench and pick release the components, the Component Pick Release function releases these materials from other projects within the planning group. Our issue comes when we try to transact the resulting Move Order and move the components into the staging subinventory. The Move Order Transaction fails because a project transfer is required during the move.

Strangely, if we navigate to the View/Update Allocations screen and transact from there, the move order processes successfully and the project transfer is effected.

Support told us that the error message is intentional and that our ability to process the transaction from the Allocations screen is a mistake. They intend to prevent this with a patch in the near future.

Project Manufacturing Development recommends that on-hand balances are reviewed for project ownership prior to running pick release and that project transfers are done were necessary before pick release is run. This is difficult for the client, as some WIP jobs include 200-300 components and a manual review would slow down the process considerably. They intend to continue to use the “backdoor” approach and avoid applying th patch that will stop them.

We understand that more than one customer has logged an enhancement request so that this functionality is available.