Difference between R11i and R12i Inventory

Difference between R11i and R12i Inventory

One Major Change in R12i from Inventory prospective is , in R12i Oracle has Merge the OPM Inventory with the Oracle Discreate Inventory. So there are no more different set of tables for OPM inventoty as well as Oracle Discreate Inventory.
This is really awesome feature,with R12.1.1 they have fix lot of bugs related to conversions and it is now in pretty shape.

How to Set MOAC Context in R12i (MOAC - Enabled)

How to Set MOAC Context in R12i (MOAC - Enabled)

In this post I will explain how to set the context to use secured synonym in oracle R12i. Please make a note that in R12i Oracle has removed all the context-based views with Secured Synonym.
All the context-based views like
1. OE_ORDER_HEAREDS
2. OE_ORDER_LINES
3. PO_HEADERS
4. PO_LINES etc has been replaced with Synonym.
Now on if user want to set the context to get the data for particular operating unit they need to use the new apis to set the context
The new API to set the context in R12 is
MO_GLOBAL.Set_Policy_Context.

Note - I like this book and that is my personal opinion.Someone else may or may not like it.

This API has 2 parameters
1. Operating unit
2. Context
Context has 2 values
1. M
2. S
When policy context is set to ‘M’, data from all accessible Operating Units will be returned.
When policy context is set to ‘S’, then only data from the specified Org_Id will be returned.
Example for R12 –

· Try to run
select * from oe_order_headers

· Set the Context
begin
MO_GLOBAL.Set_Policy_Context('S',204);
end;

· Run Again
· select * from oe_order_headers

But for R11i it is still the same
· Set Context
begin
dbms_application_info.set_client_info('204');
end;

· Run Query
· select * from oe_order_headers

Changes in OE_ORDER_PUB in R12i

Changes in OE_ORDER_PUB in R12i

From OM Prospect
OE_ORDER_PUB API has been modified to include the parameters
1. p_org_id - p_org_id is operating_unit_id
2. p_operating_unit - p_operating_unit is Operating Unit Name

User can enter any of these values. Please note that when user pass any of these values system will check if the responsibility from where user are executing these APIs has access Operating Unit or not, if not then API will return Error.
If User don’t pass this values then system will try to derive the values from the Default Operating Unit (Profile option) and if that too not specified then API will return Error.

Query to Check if Closed line stuck with MTL_TRANSACTION_INTERFACE

Query to Check if Closed line stuck with MTL_TRANSACTION_INTERFACE

In this post I post query that shows the query to reterive all the order lines that are CLOSED , but the reservation is not yet RELEASED.(data stuck in mtl_transactions_interface)

SELECT count(*)
FROM oe_order_lines_all l
WHERE l.line_category_code = 'ORDER'
AND NVL(l.shipped_interface_flag,'N') = 'N'
AND l.flow_status_code = 'CLOSED'
AND l.open_flag = 'N'
AND NVL(l.invoice_interface_status_code, 'N') = 'YES'
AND EXISTS
(
SELECT 'MTI records'
FROM mtl_transactions_interface
WHERE source_code = 'ORDER ENTRY'
AND trx_source_line_id = l.line_id
)

Query to Join Delivery Details and MTL Material Table

Query to Join Delivery Details and MTL Material Table

select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id,

wdd.released_status, oe_interfaced_flag, inv_interfaced_flag,
wdd.creation_date, mtl.transaction_id, l.flow_status_code,
l.open_flag, l.cancelled_flag
from wsh_delivery_details wdd,
mtl_material_transactions mtl,
oe_order_lines_all l
where
l.line_id = wdd.source_line_id
AND wdd.delivery_Detail_id = mtl.picking_line_id
AND wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'Y'
and wdd.released_status = 'Y'

Also below is query to Join Order line with MTL_MATERIAL_TRANSACTIONS

select mtl.transaction_id, l.flow_status_code,

l.open_flag, l.cancelled_flag , l.shipped_quantity
from
mtl_material_transactions mtl,
oe_order_lines_all l
where
mtl.trx_source_line_id = l.line_id

How to Define Expense Item

How to Define Expense Item

In this post I will explain how to create an Expense Item.


Go to Inventory responsibility
Navigation > Inventory > Items > Mater Items

In the Item Definition UI enter
1. Item Name
2. Description

• Main Tab - Select UOM.
• Inventory Tab - Don’t Select any Attribute in Inventory tab
• Purchasing Tab – Select
• Purchasable
• Enter Default Buyer Details
• Default Buyer
• Expense Account
• Receiving Tab – Enter
• Receipt Date Control Attribute

How to Create Supplier List and RFQ

How to Create Supplier List and RFQ

This Post is about How to create Supplier List and RFQ in Oracle Purchasing.

In the post I will explain about the Supplier List and how to use Supplier List and Price breaks while creating a RFQ.

Navigate to Supplier List
Supplier Base > Supplier List

 Purchasing Responsibility  -Navigate to RFQ UI

Navigation > RFQ.
Enter details for the RFQ

Operating Unit
RFQ Type
Status
Due Date
Description
Reply Via
Close Date
Item
category
UOM etc

Press the Price Breaks to Enter the Breaks. You need to do that for each Item. Please note that in RFQ Type , I have selected the Catalog RFQ. ( this Post is based on Catalog RFQ)

There are 2 types of RFQ

• Catalog
• Bid

Catalog – Its supports Price Breaks. These can be copy to a Blanket Purchase Agreement.

Bid - Its supports Shipments. These can be copy to a Standard Purchase Order

From the RFQ UI , Press the Supplier button and Select the Supplier List that we have created  

Once you select the Supplier list , system will add all the supplier to supplier List.

Your RFQ is now Ready , you can Print your RFQ.

Once an RFQ is printed, its status changes from Active to Printed. Now suppose you have made some changes after , To Print it again, you need to set status to Active. Also in Supplier UI, you need to check "Include in next Printing" for the supplier that you want to Reprint