Query: Find scheduled or on hold concurrent requests

Query: Find scheduled or on hold concurrent requests

I've noticed a number of requests for finding scheduled concurrent requests via a single query. But there are various combinations of phase_code, status_code, hold_flag, requested_start_date, etc on the fnd_concurrent_requests table and others that determine the "real" Phase and Status as displayed in the View Requests form.
No worries, here's how we find the Scheduled Requests:

select request_id
from   fnd_concurrent_requests
where  status_code in ('Q','I')
and    requested_start_date > SYSDATE
and    hold_flag = 'N';

How about an all in one query? Note: this may not be complete, so any comments welcome! Also we limit to those requests submitted in the last hour.

select fcr.request_id
,      decode(fcr.phase_code
             ,'P',decode(fcr.hold_flag
                        ,'Y','Inactive'
                        ,fl_p.meaning
                        )
             ,fl_p.meaning
             ) phase
,      decode(fcr.phase_code
             ,'P',decode(fcr.hold_flag
                        ,'Y','On Hold'
                        ,decode(sign(fcr.requested_start_date - sysdate)
                               ,1,'Scheduled'
                               ,fl_s.meaning)
                  )
             ,fl_s.meaning
             ) status
from   fnd_concurrent_requests fcr
,      fnd_lookups fl_p
,      fnd_lookups fl_s
where  1=1
and    fcr.phase_code = fl_p.lookup_code
and    fl_p.lookup_type = 'CP_PHASE_CODE'
and    fcr.status_code = fl_s.lookup_code
and    fl_s.lookup_type = 'CP_STATUS_CODE'
and    fcr.request_date > sysdate - 60/1440
order by fcr.request_id desc;

Oracle Order to Cash Queries

Oracle Order to Cash Queries

Query to Join OM and requisition Interface table for Back 2 back Order

select l.line_id, l.flow_status_code , l.open_flag,pr.interface_source_code,pr.interface_source_line_id,pr.note_to_buyer,
pr.note_to_receiver
from
oe_order_lines_all l,
po_requisitions_interface_all pr
where l.line_id = pr.interface_source_line_id
and pr.interface_source_code='CTO'

Query to Join OM and Purchase Order tables for Back 2 Back Order
select ph.segment1,a. supply_source_line_id, a.supply_source_header_id
from
mtl_reservations a,
oe_order_lines_all l,
po_headers_all ph
where demand_source_line_id = &Enter_Order_lineID
and l.line_id = a.demand_source_line_id
and a.supply_source_header_id = ph.po_header_id

Query to Join OM and PO Requisition table for Back 2 Back Order
select ph.segment1,a. supply_source_line_id, a.supply_source_header_id
from
mtl_reservations a,
oe_order_lines_all l,
po_requisition_headers_all pqh
where demand_source_line_id = &Enter_Order_lineID
and l.line_id = a.demand_source_line_id
and a.supply_source_header_id = pqh.requisition_header_id

Query to Join OM , WSH and AR table
SELECT h.order_number,l.line_id,l.ordered_quantity,l.shipped_quantity,l.invoiced_quantity,
wdd.delivery_detail_id,wnd.delivery_id,wdd.shipped_quantity,a.org_id,
a.creation_date ,a.trx_number,b.quantity_ordered , b.quantity_invoiced ,b.interface_line_attribute1,b.interface_line_attribute3,
b.interface_line_attribute6,interface_line_attribute12
from
ra_customer_trx_all a,
ra_customer_trx_lines_all b,
oe_order_headers_all h,
oe_order_lines_all l,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where a.customer_trx_id = b.customer_trx_id
and a.interface_header_context = 'ORDER ENTRY'
and b.interface_line_attribute1 = to_char(h.order_number)
and h.header_id = l.header_id
and to_char(l.line_id) = b.interface_line_attribute6
and l.line_id = wdd.source_line_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and to_char(wnd.delivery_id) = b.interface_line_attribute3

Mapping Between AR and OM (Transaction Flex field)
(RAL) - RA_CUSTOMER_TRX_LINES_ALL

RAL.INTERFACE_LINE_ATTRIBUTE1 Order_Num
RAL.INTERFACE_LINE_ATTRIBUTE2 Order_Type
RAL.INTERFACE_LINE_ATTRIBUTE3 Delivery ID
RAL.INTERFACE_LINE_ATTRIBUTE4 WayBill
RAL.INTERFACE_LINE_ATTRIBUTE6 Line_ID
RAL.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading
RAL.INTERFACE_LINE_ATTRIBUTE10 WH_ID RAL.INTERFACE_LINE_ATTRIBUTE11 PA_ID

Oracle 10g BULK Binding for better Performance.

Oracle 10g BULK Binding for better Performance.

Performance is always a very important key in the design and development of code , irrespective of the language , and it is very important when we have database operations.
Oracle in last few releases of database like 9i and 10g came up with New Built in features to improve the performances , like

* RETURNING CLAUSE
* BULK BINDING

and of course design always plays very crucial role for performance.

RETURNING CLAUSE

By thumb rule , we can improve performance by minimizing explicit calls to database.If we have requirement to get the information about the row that are impacted by DML operations (INSERT, UPDATE, DELETE) , we can do SELECT statement after DML operations , but in that case we need to run a additional SELECT Clause.RETURNING is a feature which helps us to avoid the SELECT clause after the DML operations.
We can include RETURNING clause in DML statements , it returns column values from the affected row in pl/sql variable, thus eliminate the need for additional SELECT statement to retrieve the data and finally fewer network trip, less server resources.
Below are examples about how to use RETURNING CLAUSE.

-------------------
create or replace
PROCEDURE update_item_price(p_header_id NUMBER) IS
type itemdet_type is RECORD
(
ordered_item order_test.ordered_item%TYPE,
unit_selling_price order_test.unit_selling_price%TYPE,
line_id order_test.line_id%TYPE
);
recITEMDET itemdet_type;
BEGIN
--
UPDATE order_test
SET unit_selling_price = unit_selling_price+100
WHERE header_id = p_header_id
RETURNING ordered_item,unit_selling_price, line_id INTO recITEMDET;

dbms_output.put_line('Ordered Item - 'recITEMDET.ordered_item' 'recITEMDET.unit_selling_price
' 'recITEMDET.line_id);
INSERT into order_test (ordered_item,unit_selling_price, line_id, header_id)
values ('ABCD',189,9090,1)
RETURNING ordered_item,unit_selling_price, line_id INTO recITEMDET;

dbms_output.put_line('Ordered Item - 'recITEMDET.ordered_item' 'recITEMDET.unit_selling_price
' 'recITEMDET.line_id);

DELETE from order_test
where header_id = 119226
RETURNING ordered_item,unit_selling_price, line_id into recITEMDET;

dbms_output.put_line('Ordered Item - 'recITEMDET.ordered_item' 'recITEMDET.unit_selling_price
' 'recITEMDET.line_id);
END;
-- End of Example 1 ---

When we talk about oracle database , our code is combination of PL/SQL and SQL. Oracle server uses two engines to run PL/SQL blocks , subprograms , packages etc.

* PL/SQL engine to run the procedural statements but passes the SQL statements to SQL engine.
* SQL engine executes the sql statements and if required returns data to PL/SQL engine.

thus in execution of pl/sql code our code results in switch between these two engines, and if we have SQL statement in LOOP like structure switching between these two engines results in performance penalty for excessive amount of SQL processing.This makes more sense when we have a SQL statement in a loop that uses indexed collections element values (e.g index-by tables, nexted tables , varrays).

We can improve the performance to great extends by minimizing the number of switches between these 2 engines.Oracle has introduced the concept of Bulk Binding to reduce the switching between these engines.

Bulk binding passes the entire collection of values back and forth between the two engines in single context switch rather than switching between the engines for each collection values in an iteration of a loop.

Syntax for BULK operations are
FORALL index low..high
sql_statement

..bulk collection INTO collection_name

Please note down that although FORALL statement contains an iteration scheme, it is not a FOR LOOP.Looping is not required at all when using Bulk Binding.
FORALL instruct pl/sql engine to bulk bind the collection before passing it to SQL engine, and BULK COLLECTION instruct SQL engine to bulk bind the collection before returning it to PL/SQL engine.

we can improve performance with bulk binding in DML as well as SELECT statment as shown in examples below.

declare
type line_rec_type is RECORD
(line_id NUMBER,
ordered_item varchar2(200),
header_id NUMBER,
attribute1 varchar2(100));
type line_type is table of line_rec_type
index by pls_integer;
i pls_integer:=1;
l_att varchar2(100);
l_line_id number;
l_linetbl line_type;
l_linetbl_l line_type;

type line_type_t is table of integer
index by pls_integer;
j pls_integer:=1;
l_lin_tbl line_type_t;


type line_type_t2 is table of oe_order_lines_all.attribute2%TYPE
index by pls_integer;
j pls_integer:=1;
l_lin_tbl2 line_type_t2;

begin
dbms_output.put_line('Test');

for line in (select attribute10, line_id , ordered_item, header_id
from oe_order_lines_all
where creation_date between sysdate-10 and sysdate)
loop
l_linetbl(i).line_id:=line.line_id;
l_linetbl(i).header_id:=line.header_id;
l_linetbl(i).ordered_item:=line.ordered_item;
l_lin_tbl(i):=line.line_id;
i:=i+1;
end loop;
dbms_output.put_line('Total count in table 'l_lin_tbl.COUNT);

-- Below statement will call the Update Statement ONLY Once for complete Collection.
forall i in l_lin_tbl.FIRST..l_lin_tbl.LAST
save exceptions
update oe_order_lines_all
set attribute1=l_lin_tbl(i)
where line_id = l_lin_tbl(i);
--Common Error
--DML ststement without BULK In-BIND canot be used inside FORALL
--implementation restriction;cannot reference fields of BULK In_BIND table of records

--In below statement we are passing complete collection to pl/sql table in Single statement and thus avoiding the Cursor.
SELECT line_id, ordered_item, header_id, attribute1 BULK COLLECT INTO l_linetbl_l
FROM oe_order_lines_all
WHERE creation_date between sysdate-10 and sysdate;

FOR i in 1..l_linetbl_l.count LOOP
dbms_output.put_line(' Line ID = 'l_linetbl_l(i).line_id' Ordered Item = 'l_linetbl_l(i).ordered_item' Attribute1 ='l_linetbl_l(i).attribute1);
END LOOP;

--Returning
forall i in l_lin_tbl.FIRST..l_lin_tbl.LAST
UPDATE oe_order_lines_all
SET ATTRIBUTE2 = l_lin_tbl(i)
WHERE line_id = l_lin_tbl(i)
RETURNING line_id BULK COLLECT into l_lin_tbl2;

FOR i in 1..l_lin_tbl2.count LOOP
dbms_output.put_line(' Attribute2 ='l_lin_tbl2(i));
END LOOP;

END;
-- End of Example 2 ---

R12 Multi-Org Access Control MOAC

R12 Multi-Org Access Control

Release 12 came with a new feature of accessing the multiple Operating Units with the single responsibility. In R12 they call it Multi-Org Access Control (MOAC).

Multi-Org Access Control (MOAC) enables companies that have implemented a Shared Services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications responsibility.

Features of MOACAccess multiple operating units within a single application responsibility
Perform tasks for and across multiple operating units:
1. Set-up controls, Negotiate sales agreements
2. Enter quotes, orders and returns
3. Schedule orders, Apply and Release holds
4. Run reports and concurrent programs
5. Setup Transaction Type, apply and release holds

This increases the productivity of Shared Service Centers as users no longer have to switch application responsibilities when processing transactions for multiple operating units at a time.

Ability to view data from multiple operating units from a single responsibility, gives users more information. This enables them to make better decisions. For example when performing scheduling actions, users can now look at orders across multiple operating units and make more informed decisions on inventory allocation.

Setup required for MOAC
At a high level we need to set-up security profiles that allow access to multiple Operating Units. We also need to set the following MO profile options, in order to enable Multi-Org Access Control :
MO: Security Profile
MO: Default Operating Unit.

Note that If you do not set these profiles the application will behave as it does now.

There are almost 13 profile options in OM that has been converted to System parameters.
(For details send me an email)

To support Multi-Org Access Control the Operating Unit has been has added as a hidden folder field in the following forms:

All of the Sales Order Form Windows like Sales Order window, Order Organizer Find window (All tabs), Order Summary, Quick Sales Order window, Quick Sales Order Organizer, Quick Order Summary, Quote window, Quote Organizer, Quote Summary, Find Customer window

All of the Sales Agreement Form Windows, these are –
The Sales Agreement window, Sales Agreement Organizer, Sales Agreement Summary

Other Form Windows including –
The Scheduling Organizer window, Pricing and Availability window, Order Import Corrections window, Open Interface Tracking window, Retro-bill Organizer window, Retro-bill Requests tab

Since operating unit is a hidden fileld we can made it visible (using folder tools) in both the Order Organizer Find Window and the Summary Window.

In the Find Window, if you leave the Operating Unit field blank and do not specify criteria that are Operating Unit sensitive (such as Order Type or Ship To Location etc) you can search for transactions across all the Operating Units that you have access to via your MO: Security Profile.

You can also restrict your search to a single Operating Unit by picking one from the LOV or by specifying a query criteria that is Operating Unit sensitive (such as the Order Type).

Benefits1. Improve accessibility
2. Increase information for decision making
3. Reduce costs

Setup Required for MOAC
You can create global security profiles that enable users to work on organizations in multiple business groups.
You do this by setting up a global hierarchy, which can contain organizations from any business group on your database, and associating it with a global security profile. This enables you to create a security hierarchy that gives users access to organizations across business groups.

Enter and Schedule Orders Across OU's
A new Operating field has been added to all the forms that allow you to view and manage Sales Agreements, Quotes, Orders and Returns across multiple Operating Units. With R12 you can:
- Choose an Operating Unit when entering a transaction
- Query transactions across multiple Operating Units
- Perform various actions on transactions from multiple Operating Units

How to do setupGo to Responsibility > Human Resource
Navigaton > Security > Global Profile.

Create global security profiles that enable users to work on organizations in multiple business groups.

Set up the global organization hierarchy.
Setup top Organization
Define the organizations from any business group in your database, and associating it with a global security profile. This enables you to create a security hierarchy that gives users access to organizations across business groups.
Run Concurrent Request "Security List Maintenance"

For More Info - Please conatct em or you can go to www.oracle.com or metalink.oracle.com

Oracle Apps Inventory Queries

Oracle Apps Inventory Queries

--Reservation Qty for SKU

select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and mtr.inventory_item_id =1
and ooh.order_number = 316

--Reservation Qty for Order
select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and ooh.order_number = 316


--Order to HZ (Customer Location)
select ooh.ship_to_org_id,ooh.sold_to_org_id ,hp.party_name,hca.party_id,
hca.account_number,hcsu.site_use_code,hcsu.location,hcsu.primary_flag,
hcsu.bill_to_site_use_id,--hpsu.site_use_type,hps.party_site_number,hps.party_site_id,
hps.location_id,hpsu.primary_per_type
,hl.address1,hl.address2,hl.address3,hl.address4,hl.city,hl.state,hl.postal_code,hl.county
FROM
oe_order_headers_all ooh,
hz_cust_accounts_all hca,
hz_parties hp,hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hl
where
ooh.sold_to_org_id = hca.cust_account_id
and hca.party_id = hp.party_id
and hca.party_id = hps.party_id
and hca.cust_account_id = hcas.cust_account_id
and hps.party_site_id = hcas.party_site_id
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hl.location_id = hps.location_id
and ooh.order_number = 351
order by hps.party_site_id

11i & 12i Queries for Internal Requisition and Internal Sales Order

11i & 12i Queries for Internal Requisition and Internal Sales Order

1. Getting data from oe lines iface all based on PO requisition number (Before Order Import)select * from oe_lines_iface_all
where order_source_id = 10 --order_source_id for 'Internal'
and orig_sys_document_ref =
(select to_char(requisition_header_id)
from po_requisition_headers_all prh
where prh.segment1 = '&requisition_number');

2. Getting data from order header/lines based on PO requistion number (after Order Import) SELECT oeh.order_number, oeh.header_id, oel.line_id, oel.line_number
FROM
oe_order_lines_all oel,
oe_order_headers_all oeh,
po_requisition_headers_all porh,
po_requisition_lines_all porl
WHERE oeh.header_id = oel.header_id
AND oel.source_document_id = porh.requisition_header_id
AND oel.source_document_line_id = porl.requisition_line_id
AND porh.requisition_header_id = porl.requisition_header_id
AND oel.order_source_id = 10 --order_source_id for 'Internal'
AND oel.orig_sys_document_ref = '&requisition_number'
AND oel.org_id = porh.org_id
ORDER BY oeh.header_id, oel.line_id;


3. The following sql can be used to confirm the Location defined in Oracle Purchasing is tied to a Customer in Order Management. The script shows that last ten locations created in order of creation date.

SELECT rtrim(hl.location_code) location_code, hl.location_id, ood.organization_code, pla.organization_id, hl.ship_to_site_flag, hl.receiving_site_flag, pla.customer_id, rtrim(rc.customer_name) customer_name
FROM hr_locations_all hl,
org_organization_definitions ood,
po_location_associations_all pla,
ra_customers rc WHERE pla.location_id = hl.location_id
AND rc.customer_id(+) = pla.customer_id
AND ood.organization_id(+) = pla.organization_id
AND rownum < order_source_id =" posp.order_source_id" org_id =" posp.org_id" requisition_header_id =" ool.source_document_id" requisition_line_id =" ool.source_document_line_id" requisition_header_id =" porl.requisition_header_id" requisition_line_id =" pord.requisition_line_id" requisition_line_id =" rcv.requisition_line_id" distribution_id =" rcv.req_distribution_id" shipment_header_id =" rsh.shipment_header_id" org_id =" posp.org_id" header_id =" ooh.header_id"> 0
AND ool.orig_sys_line_ref not like '%OE_ORDER_LINES_ALL%'
AND ool.source_document_line_id is not null

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

RFQ to PO Receipt Cycle

RFQ to PO Receipt Cycle

This Post is about Orace Apps RFQ to Receipt Creation.

In this Post I will explain the Cycle from RFQ to PO Receipt.

Once we Submit the “Request to Print the RFQ” for a supplier, print Count for that supplier will Incremented. As shown below I have printed for all suppliers , so all supplier print count incremented by 1.

As shown Below  in Oracle Apps UIs

1. 
   
   RFQ # 308,
2. 
   
   Supplier Info from Supplier List and
3. 
   
   Price Breaks.

Print RFQ for all the Suppliers by means of Concurrent Program available in Oracle Apps

 
Once we Print the RFQ , Status of RFQ become Printed , and also Print count will Increment.Since we get response from the Office Supplier , Inc Site – OFFICESUPPLIER , Responded field populated for It.

  

 From the RFQ , Select Tools > Copy Doc .It will Create Quotations as shown below.

1. 
   
   Enter the Supplier Name for whom you want to create Quote.
2. 
   
   Press OK and it will Create Quotation.

1. 
   
    Query for Quotation # 502.
2. 
   
   Create Purchase Order Agreement from Quotation by selected Tools > Copy Doc
3. 
   
   Press Ok and it will Create Purchase Order Agreement. 

 Query for PO Agreement and Approve it

 Once Oracle Purchase Agreement is Approved , create the releases for Blanket PO Agreement .In this PO Agreement Release we have item Test001 , BUT Item Test001 is restricted to be ordered from supplier that are in “Approval Supplier list”, and as our Supplier is not part of any Approve Supplier list , system will throw Error.

 For my test , I just remove the Item Test001 and Approve the Oracle Purchase Order Release and finally did the receipt against PO.

RFQ to PO Receipt Cycle

This Post is about Orace Apps RFQ to Receipt Creation.                                         In this Post I will explain the Cycle from RFQ to PO Receipt.

Once we Submit the “Request to Print the RFQ” for a supplier, print Count for that supplier will Incremented. As shown below I have printed for all suppliers , so all supplier print count incremented by 1.

As shown Below  in Oracle Apps UIs

1. 
   
   RFQ # 308,
2. 
   
   Supplier Info from Supplier List and
3. 
   
   Price Breaks.

Print RFQ for all the Suppliers by means of Concurrent Program available in Oracle Apps

 
Once we Print the RFQ , Status of RFQ become Printed , and also Print count will Increment.Since we get response from the Office Supplier , Inc Site – OFFICESUPPLIER , Responded field populated for It.

  

 From the RFQ , Select Tools > Copy Doc .It will Create Quotations as shown below.

1. 
   
   Enter the Supplier Name for whom you want to create Quote.
2. 
   
   Press OK and it will Create Quotation.

1. 
   
    Query for Quotation # 502.
2. 
   
   Create Purchase Order Agreement from Quotation by selected Tools > Copy Doc
3. 
   
   Press Ok and it will Create Purchase Order Agreement. 

 Query for PO Agreement and Approve it

 Once Oracle Purchase Agreement is Approved , create the releases for Blanket PO Agreement .In this PO Agreement Release we have item Test001 , BUT Item Test001 is restricted to be ordered from supplier that are in “Approval Supplier list”, and as our Supplier is not part of any Approve Supplier list , system will throw Error.

 For my test , I just remove the Item Test001 and Approve the Oracle Purchase Order Release and finally did the receipt against PO.

Adding a New Line is Order with OE_ORDER_PUB(API)

Adding a New Line is Order with OE_ORDER_PUB

Below is code to add New Line in an existing Sales Order with OE_ORDER_PUB.

create or replace
package body xxorderprocess as

function xxcreateOrder
(
p_order_type_id NUMBER,
p_sold_to_org_id NUMBER,
p_ship_to_org_id NUMBER,
p_price_list_id NUMBER,
p_transactional_curr_code VARCHAR2,
p_flow_status_code VARCHAR2,
p_cust_po_number VARCHAR2,
p_order_source_id NUMBER,
p_inventory_item_id NUMBER,
p_ordered_quantity NUMBER,
p_tax_code VARCHAR2,
p_code VARCHAR2,
p_header_id NUMBER,
p_line_id NUMBER

)
return VARCHAR2 is
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_xxstatus VARCHAR2(1000);

/*****************PARAMETERS****************************************************/
l_debug_level number := 1; -- OM DEBUG LEVEL (MAX 5)
l_org number := 204; -- OPERATING UNIT
l_user number := 1318; -- USER
l_resp number := 21623; -- RESPONSIBLILTY
l_appl number := 660; -- ORDER MANAGEMENT
/***INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
/***OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
-- book API vars

b_return_status VARCHAR2(200);
b_msg_count NUMBER;
b_msg_data VARCHAR2(2000);
BEGIN
dbms_application_info.set_client_info(l_org);
--MO_GLOBAL.set_policy_context('S',l_org);
/*****************INITIALIZE DEBUG INFO*************************************/
if (l_debug_level > 0) then
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
end if;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize(l_user, l_resp, l_appl); -- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
/***********POPULATE REQUIRED ATTRIBUTES **********************************/
dbms_output.put_line('Start'||'--'||p_code||'--'||substr(p_code,1));


IF p_code = 'AL' THEN -- add Create Shippable Line
dbms_output.put_line('Add New Ship Line');
---Create 1 Line
l_line_tbl(1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(1).inventory_item_id := p_inventory_item_id;--149 ;
l_line_tbl(1).ordered_quantity := p_ordered_quantity;--1;
l_line_tbl(1).ship_to_org_id := p_ship_to_org_id;--1024 ;
l_line_tbl(1).tax_code := p_tax_code;--'Location' ;
l_line_tbl(1).header_id := p_header_id;
END IF;


/*****************CALLTO PROCESS ORDER API*********************************/
dbms_output.put_line('Calling API');
oe_order_pub.Process_Order( p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
--OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

/*****************CHECK RETURN STATUS***********************************/
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Return status is success ');
dbms_output.put_line('debug level '||l_debug_level);
if (l_debug_level > 0) then
dbms_output.put_line('success');
end if;
commit;
l_xxstatus :='S';
else
dbms_output.put_line('Return status failure ');
if (l_debug_level > 0) then
dbms_output.put_line('failure');
end if;
rollback;
l_xxstatus :='F';
end if;

/*****************DISPLAY RETURN STATUS FLAGS******************************/
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: ' ||l_return_status);
DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: ' ||l_msg_data);
DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: ' ||l_msg_count);
DBMS_OUTPUT.PUT_LINE('header.order_number IS: ' ||to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE('header.return_status IS: '|| l_header_rec_out.return_status);
DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: '|| l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE('header.header_id IS: '|| l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: '|| l_header_rec_out.order_source_id);
DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: '|| l_header_rec_out.flow_status_code);
end if;
l_xxstatus := l_xxstatus||' '||l_header_rec_out.booked_flag||' '||l_header_rec_out.header_id||' '||l_header_rec_out.flow_status_code;
/*****************DISPLAY ERROR MSGS*************************************/
if (l_debug_level > 0) then
FOR i IN 1 .. l_msg_count LOOP
Oe_Msg_Pub.get(
p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => l_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE('message is: ' ||l_data);
DBMS_OUTPUT.PUT_LINE('message index is: ' ||l_msg_index);
END LOOP;
end if;
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('Debug = ' ||OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE('Debug Level = ' ||to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE('Debug File = ' ||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);
DBMS_OUTPUT.PUT_LINE('****************************************************');
end if;
return l_xxstatus;

EXCEPTION
WHEN OTHERS THEN
l_xxstatus:= l_xxstatus||' '||sqlerrm;
return l_xxstatus;
end xxcreateOrder;


end xxorderprocess;

create or replace
package xxorderprocess as

function xxcreateOrder
(
p_order_type_id NUMBER,
p_sold_to_org_id NUMBER,
p_ship_to_org_id NUMBER,
p_price_list_id NUMBER,
p_transactional_curr_code VARCHAR2,
p_flow_status_code VARCHAR2,
p_cust_po_number VARCHAR2,
p_order_source_id NUMBER,
p_inventory_item_id NUMBER,
p_ordered_quantity NUMBER,
p_tax_code VARCHAR2,
p_code VARCHAR2,
p_header_id NUMBER,
p_line_id NUMBER

)return VARCHAR2;


end xxorderprocess;

I am calling above Package from following PL/SQL block

------------------------
declare
l_status VARCHAR2(1000);
p_header_id NUMBER;
p_line_id NUMBER:=197953;
p_code VARCHAR2(10):='AL';
BEGIN
l_status:= xxorderprocess.xxcreateOrder(1437,1005,1024,1000,'USD','ENTERED',
'PO-9090',0,149,100,'Location',
p_code,p_header_id,p_line_id);
dbms_output.put_line('l_status ='||l_status);
end;

What is ship_to_org_id and ship_from_org_id in Oracle Order Management.

What is ship_to_org_id and ship_from_org_id in Oracle Order Management.

Thsi Post is for Oracle Apps (Order Management).

ship_from_org_id column in Oracle Order Management application means warehouse from where you ship the goods to customer.Please keep in mind ship_from_org_id should be an Inventory organization (Because you want to ship the goods and if you don't define your warehouse as Inventory org then how you will able to ship the from from there ) , Technically , if you define and organization and not mark it as Inventory Org , then in Order Management Waregouse column ( lines level) , your organization will not appear , till you mark it as Inventory org and assign items to it.

Ship_to_org_id is the location where you actually want to ship the good , this is possibly be your customer's place and it can be or can't be an Inventory org.

Important table and view to look into for this are
1.SHIP_TO_ORG_ID
hz_parties
hz_cust_accounts
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL

2.SHIP_FROM_ORG_ID
org_organization_definitions
HR_ORGANIZATION_INFORMATION

Queries to Drive price List /Qualifiers/Modifiers/Conext/Segments

Queries to Drive price List /Qualifiers/Modifiers/Conext/Segments

 Below queries are for Oracle Applications Order Management and Advance Pricing Modules.

 

SELECT  l.list_line_id,q.qualifier_grouping_no,
      q.qualifier_id, q.qualifier_context, q.qualifier_attr_value,
      q.comparison_operator_code,q.qualifier_precedence,q.qual_attr_value_from_number,
      q.qualifier_attribute,q.end_date_active,l.end_date_active,h.end_date_active
    FROM
      qp_list_headers_all h,
      qp_list_lines l,
      qp_qualifiers q
    where h.list_header_id = l.list_header_id
    and h.list_header_id = q.list_header_id
    and h.list_header_id = &list_id -- Price List Header ID or Modifier header ID
    and NVL(h.end_date_active,sysdate) >= sysdate
    and NVL(l.end_date_active,sysdate) >= sysdate
    and NVL(q.end_date_active,sysdate) >= sysdate;

 

 

select q.qualifier_id,q.qualifier_context,q.qualifier_attribute,qualifier_attr_value ,
ct.prc_context_id, qs.segment_code
from qp_qualifiers q , qp_prc_contexts_b ct , qp_segments_b qs
where q.list_header_id = &ListHeaderID   --PriceList Header ID
and ct.prc_context_type ='QUALIFIER'
and q.qualifier_context = ct.prc_context_code
and qs.prc_context_id =  ct.prc_context_id
and qs.segment_mapping_column = q.qualifier_attribute

Some questions Third Party Payments on R12 Oracle Payables

Some questions Third Party Payments on R12 Oracle Payables

Recently One of reader ask me few questions on 3rd Party Payments feature Offered in Oracle Payable in R12.

1.Can payment batches be processed for the remit to suppliers .

Answer - Yes

2.If the invoices are being bought through ap invoice interface, will the relationship still need to be setup even if we bring in the remit to supplier and supplier site data on the interface.

Answer - Yes , for Third Party , we have to define the relationship in supplier setup. By just populating into the Interface table with "remit to " values will not work.
For complete info on 3rd Party payment in R12 Oracle Payable refer 3rd Party Payments

How to Create/Pick/Ship Confim Sales Order (Youtube Video).

Click this link

How to Create/Pick/Ship Confim Sales Order (Youtube Video). 

 

 

Commands to Open Oracle Report6i and Form6i in UNIX envirnoment

Commands to Open Oracle Report6i and Form6i in UNIX envirnoment

These days working on some 11510 projects and I had hard time to look for the commands to open Oracle reports6i/forms6i in UNIX.

Listing these commands for reference.

To Open/Run Oracle Reports6i in Unix use

rwbld60 To Open

rwrun60 To Run

To Open/Run Oracle Forms6i in Unix use

f60desm To Open

f60genm To generate and compile.