Categories
Inventory Oracle Technical

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.

Categories
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
ORDER BY 1,
2

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

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

Categories
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

Categories
Technical Uncategorized

R12 and IE8

In some cases, I’ve found that Oracle Applications won’t open the forms in IE8.

I’ve been able to fix this by going to IE8’s Internet Options> Local Intranet> Sites> Advanced. Add the site for your application instance to the list of sites and you should be good to go.

Categories
Technical

Oracle Applications on Windows 7

My geek nature meant that I had to try Windows 7 Beta on at least one of my computers. I can’t say I was as down on Vista as most people who leave comments online, but 7 seems to be a great leap forward. I like it. Getting the apps to run on Windows 7 is very similar to getting them to run on Vista:

Internet Explorer and Firefox:

  1. Download and install the latest version of Java (such as Version 6, Update 11). You can get it here.
  2. Copy the file C:\Program Files\Java\jre6\bin\client\jvm.dll to folder C:\Program Files\Oracle\Jinitiator 1.3.1.21\bin\hotspot. (Overwrite the existing jvm.dll.)
  3. In folder C:\Program Files\Oracle\Jinitiator 1.3.1.21\lib, rename “font.properties.zh.5” to “font.properties.zh.6” and “font.properties.zh.5.1” to “font.properties.zh.6.1”
  4. If you use multiple languages, use a text editor (such as Notepad) to open file “C:\Program Files\Oracle\Jinitiator 1.3.1.21\lib\font.properties”. Search for “\u5b8b\u4f53=SIMSUN.TTF” and change it to “\u5b8b\u4f53=SIMSUN.TTC”. This is an optional step

Internet Explorer:

Go to Tools> Internet Options> Advanced. Disable 3rd Party Extensions by unchecking as below:

Click the Compatibility Viewer button to the right of the address bar as below and follow the instructions:

(My thanks to Garry Martin, CTO of Fujitsu’s Microsoft Practice in the UK for this IE8 tip.)

You should be good.

Firefox:

  1. Go to C:\Program Files\Oracle\Jinitiator 1.3.1.21\bin. Find “NPJinit13121.dll” and copy it.
  2. Paste it into C:\Program Files\Mozilla Firefox\plugins\.

Good luck!

Categories
Technical

Oracle Applications on Windows Vista

I bought a new laptop about a year ago and had Vista installed. I showed up at the client site the following Monday and spent the better part of a day trying to get Oracle to come up. Nothing I tried worked. That night, 3 days after the laptop arrived, I “upgraded to a previous version” and installed XP. I never gave a thought to Vista again until a colleague of mine brought his bright shiny new laptop into work a couple of weeks ago and nonchalantly mentioned that Oracle was working just fine with his Vista Ultimate. Like a red rag to a bull…!

Much Googling that evening – yeah I’m competitive!- led me to this solution. I’ve tried it on a couple of different computers and it seems to work well:

You should be able to make it work by following these instructions:

Internet Explorer and Firefox

  1. Download and install the latest version of Java (such as Version 6, Update 11). You can get it here.
  2. Copy the file C:\Program Files\Java\jre6\bin\client\jvm.dll to folder C:\Program Files\Oracle\Jinitiator 1.3.1.21\bin\hotspot. (Overwrite the existing jvm.dll.)
  3. In folder C:\Program Files\Oracle\Jinitiator 1.3.1.21\lib, rename “font.properties.zh.5” to “font.properties.zh.6” and “font.properties.zh.5.1” to “font.properties.zh.6.1”
  4. If you use multiple languages, use a text editor (such as Notepad) to open file “C:\Program Files\Oracle\Jinitiator 1.3.1.21\lib\font.properties”. Search for “\u5b8b\u4f53=SIMSUN.TTF” and change it to “\u5b8b\u4f53=SIMSUN.TTC”. This is an optional step.

This should be all you need to get Oracle running on IE7. However, if IE7 crashes every time Jinitiator tries to open Oracle Forms, you should try disabling third-party browser extensions. In IE7, go to Tools> Internet Options> Advanced and uncheck the box as below:

If you are using IE8, you will also have to click on the Compatibility button to the right of the address bar as below:

(My thanks to Garry Martin, CTO of Fujitsu’s Microsoft Practice in the UK for this IE8 tip.)

Firefox

These additional steps are required if you plan to use Firefox:

  1. Go to C:\Program Files\Oracle\Jinitiator 1.3.1.21\bin. Find NPJinit13121.dll and copy it.
  2. Paste it into C:\Program Files\Mozilla Firefox\plugins\

Good luck!

Categories
Technical

SQL is my Friend

I think I’m a functional consultant with just enough technical knowledge to make himself very, very dangerous!

I’ve always been in awe of my developer friends and always will be. For many years I thought they practised some form of witchcraft. What you don’t understand always seems like magic, doesn’t it?

After several years of looking over the shoulders of some very patient friends – Ravinder Goyal and Sarath Yeturu, I owe you! – I started to pick little pieces of it up. I still don’t know a fraction of what these guys do, but I do know enough to be able to provide value to a client and I think that’s important. Anyone who can keep an SR moving by running scripts, create some table validated DFF’s and do some Forms Personalizations without having to wait for a developer to be available is more valuable to a client than someone who can’t.

SQL is my Friend is a document that I wrote with Pete Stees of Oracle Consulting. It shows how a functional consultant or BSA can pick up the basic SQL skills and how they might be used in an Oracle Applications context.