Purchase Orders created on previous day

Purchase Orders created on previous day:-
-------------------------------------------------

You can use the following query to get all the Purchasing Orders created on previous day in any Instance.

You can filter-out based on category set or Inventory Organization etc.
I have commented out category_set_id and Organization ID conditions in the where clause.
You can modify as per your requirement.

Note: - This kind of information would be required for the Organization (Management) for the tracking the PO's created on daily bases.

Note:- There are some values I am getting from attribute columns. This values would vary in your Instance. These values depends on the DFF setup in your Instance.

SELECT mcb.segment1 "GMA"
, mcb.segment2 "Group"
, mcb.segment3 "Dept"
, mcb.segment4 "Cat"
, poh.segment1 "PO"
, fu.user_name "PO Created by(User_id)"
, fu.description "PO Created by(Name)"
, vs.vendor_site_code
, vn.vendor_name
, hou1.name
, hrl1.location_code "Ship To"
, poh.authorization_status "PO status"
, msi.segment1 "SKU"
, msi.description "Item Description"
, pll.quantity
, pll.quantity_received
, pll.quantity_cancelled
, fu1.user_name "Cancelled by"
, pll.cancel_reason
, poh.creation_date
, pll.promised_date
, pll.need_by_date
,(SELECT rsh.attribute8
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) "Appointment Number"
,(SELECT rsh.attribute9
FROM rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=poh.po_header_id
AND rsl.po_line_id=pol.po_line_id
AND ROWNUM=1) "Appointment Date"
,pol.unit_price "Unit Cost"
,(pol.unit_price*pol.quantity) "Ext Cost"
FROM
po_headers_all poh
, po_lines_all pol
, po_line_locations_all pll
, mtl_system_items_b msi
, inv.mtl_item_categories mic
, inv.mtl_categories_b mcb
, fnd_user fu
, po_vendors vn
, po_vendor_sites_all vs
, hr_locations_all_tl hrl1
, hr_organization_units hou1
,fnd_user fu1
WHERE TRUNC(poh.creation_date)=TRUNC(SYSDATE-1)
AND poh.type_lookup_code='STANDARD'
--AND poh.attribute2 LIKE 'M%'
AND poh.po_header_id=pol.po_header_id
AND pol.po_line_id=pll.po_line_id
AND pol.po_header_id=pll.po_header_id
AND pol.item_id=msi.inventory_item_id
AND msi.organization_id = mic.organization_id
AND msi.inventory_item_id = mic.inventory_item_id
-- AND mic.category_set_id = 1
AND mcb.category_id = mic.category_id
-- AND msi.organization_id IN (Your Organization ID's)
AND poh.created_by=fu.user_id
AND poh.vendor_id=vn.vendor_id
AND vs.vendor_id = poh.vendor_id
AND vs.vendor_site_id = poh.vendor_site_id
AND hrl1.location_id(+) = poh.ship_to_location_id
AND hrl1.LANGUAGE(+) = USERENV ('LANG')
AND hrl1.location_id=hou1.location_id
AND fu1.user_id(+)=pll.cancelled_by

Hard Reservartion for the Stanard Sales Order Lines against Internal Sales Order

Hard Reservation for the Standard Sales order Line against Internal Sales order:-
----------------------------------------------------------------------------------------------

Note:- You need to have the ASCP Module Installed for the following script to work.

Note:- ASCP decide whether it have to create the PO or Internal sales order
based on the Sourcing Rules defined for a Item in the Inventory Organization.

When any sales Order created for any particular item for any Quantity and if that
quantity is not available then ASCP would created the PO (Purchase order to get the
Material required for third party or could create the Internal requisition which would
again creates the Internal sales order) and It has to reserve the quantity which
the Purchase Order or Internal sales order has.

Here, we are considering if ASCP has decided to create the Internal Sales Order.

You can use the following script.

This document is designed by Dhanunjaya Sadhu.
He is one is dedicated resource in ASCP module.

Many thanks to Dhanunjaya Sadhu

/************************************************************
Hard Reservation for Standard Sales Order line against Internal Sales Order


Created by : Dhanunjaya Sadhu
Creation Date :

Primarily applicable for oracle ASCP implemented projects
Can be customized based on the requirement change.

For others, the input values can be passed directly without CURSOR.

************************************************************/
PROCEDURE create_iso_hard_reservation
(p_line_id IN NUMBER,
p_delivery_date IN DATE,
p_ship_from_org_id IN NUMBER)

IS
p_rsv inv_reservation_global.mtl_reservation_rec_type;
p_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
x_rsv_id NUMBER;
x_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_status VARCHAR2(1);
x_qty NUMBER;
l_inventory_item_id number;
l_source_header_id number;
l_reservation_qty number;
l_error_message VARCHAR2(300);
l_order_number NUMBER;


CURSOR C_ISO_RESV(c_line_id)
----- Cursor to select ISO pegged to the standard sales order line
IS
SELECT
DISTINCT ool.HEADER_ID,
ool.LINE_ID,
mfp.ALLOCATED_QUANTITY
FROM OE_ORDER_LINES_ALL ool,
OE_ORDER_HEADERS_ALL ooh ,
MSC_DEMANDS md,
MSC_SUPPLIES ms,
MSC_FULL_PEGGING mfp,
MSC_PLANS mp
WHERE mp.PLAN_ID=mfp.PLAN_ID
AND mp.plan_id=ms.plan_id
AND mp.plan_id=md.plan_id
AND ool.HEADER_ID=ooh.HEADER_ID
AND ooh.ORIG_SYS_DOCUMENT_REF = ms.ORDER_NUMBER
AND ool.SOURCE_DOCUMENT_LINE_ID=ms.PO_LINE_ID
AND ms.TRANSACTION_ID=mfp.TRANSACTION_ID
AND md.DEMAND_ID=mfp.DEMAND_ID
AND md.SALES_ORDER_LINE_ID=c_line_id;

BEGIN

--- initialize required input params to call reservation api--

--- get the item id--
SELECT DISTINCT INVENTORY_ITEM_ID
INTO l_inventory_item_id
FROM mtl_system_items
WHERE segment1=( SELECT ordered_item
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=p_line_id);


--- get the sales_order_id from mtl_sales_orders table
---which should be passed as one of the input params

SELECT SALES_ORDER_ID,segment1
INTO l_source_header_id,l_order_number
FROM mtl_sales_orders
WHERE SEGMENT1=(SELECT TO_CHAR(oh.order_number)
FROM OE_ORDER_HEADERS_ALL oh,OE_ORDER_LINES_ALL ol
WHERE oh.header_id=ol.header_id
AND ol.line_id=p_line_id);


FOR C_REC IN C_ISO_RESV(p_line_id)

----------- sending the sales order line to the cursor variable

LOOP


----- Initialize al required inputs to perform HARD RESERVATION------

-----fnd_global.APPS_Initialize(2572700,20559,300);
-----p_user_id, p_resp_id, p_resp_appl_id

p_rsv.reservation_id := NULL;
---- cannot know
---- will be generated once reservation is successful
p_rsv.requirement_date := p_delivery_date;
p_rsv.organization_id := p_ship_from_org_id; ---------------------------mtl_parameters.organization id
p_rsv.inventory_item_id := l_inventory_item_id;
---------------------------mtl_system_items.Inventory_item_id
p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe;
p_rsv.demand_source_name := NULL;
------------------------------------------also u can save it as 'SALES ORDER' ;
p_rsv.demand_source_header_id :=l_source_header_id;-----------------------------mtl_sales_orders.sales_order_id for ordernumber
p_rsv.demand_source_line_id := p_line_id;
------------------------------------ oe_order_lines.line_id
p_rsv.primary_uom_code := 'EA';
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_code := 'EA';
p_rsv.reservation_uom_id := NULL;
p_rsv.reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.primary_reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.autodetail_group_id := NULL;
p_rsv.external_source_code := NULL;
p_rsv.external_source_line_id := NULL;
p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_internal_ord;
p_rsv.supply_source_header_id :=C_REC.HEADER_ID;
-------------------------------Header id of Internal sales order
p_rsv.supply_source_line_id :=C_REC.LINE_ID;
---------------------------------Line id of Internal sales order
p_rsv.supply_source_name := NULL;
p_rsv.supply_source_line_detail := NULL;
p_rsv.revision := NULL;
p_rsv.subinventory_code := NULL;
--------------------------subinventory code can be mentioned based on your customization.
p_rsv.subinventory_id := NULL;
p_rsv.locator_id := NULL;
--------------------------17930; -- A10-L2-B09
p_rsv.lot_number :=NULL;
---------------------------'200801225083 ';
p_rsv.lot_number_id := NULL;
p_rsv.pick_slip_number := NULL;
p_rsv.lpn_id := NULL;
p_rsv.attribute_category := NULL;
p_rsv.attribute1 := NULL;
p_rsv.attribute2 := NULL;
p_rsv.attribute3 := NULL;
p_rsv.attribute4 := NULL;
p_rsv.attribute5 := NULL;
p_rsv.attribute6 := NULL;
p_rsv.attribute7 := NULL;
p_rsv.attribute8 := NULL;
p_rsv.attribute9 := NULL;
p_rsv.attribute10 := NULL;
p_rsv.attribute11 := NULL;
p_rsv.attribute12 := NULL;
p_rsv.attribute13 := NULL;
p_rsv.attribute14 := NULL;
p_rsv.attribute15 := NULL;
p_rsv.ship_ready_flag := NULL;
p_rsv.demand_source_delivery := NULL;

--- Validating whether the pegging is done WITH AN ISO---
---- FInally performing Hard Reservation ---------

inv_reservation_pub.create_reservation
(
p_api_version_number => 1.0
, x_return_status => x_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_rsv
, p_serial_number => p_dummy_sn
, x_serial_number => x_dummy_sn
, x_quantity_reserved => x_qty
, x_reservation_id => x_rsv_id
);


IF x_status='S' THEN
--- HARD RESERVATION IS DONE SUCCESFULLY -----
COMMIT;

dbms_output.put_line('reservation succesful');
dbms_output.put_line('reservation id: || x_rsv_id);

ELSE

------ getting error msgs from fnd_msg_pub------

if(nvl(x_msg_count,0) = 0) then
dbms_output.put_line('no message return');
else
for I in 1..x_msg_count LOOP
l_error_message := fnd_msg_pub.get(I, 'F');
end LOOP;
end if;

--- HARD RESERVATION API fails ---

dbms_output.put_line('Reservation API Error Message: '||l_error_message);

END IF;


EXCEPTION
WHEN OTHERS THEN --- In case of any pl/sql error
l_error_message := SQLERRM;
dbms_output.put_line('Plsql Error Message: '||l_error_message);

END create_iso_hard_reservation;

Hope you find the above document helpful in your work.

Hard Reservation for Standard Sales Order line against Purchase Order

Hard Reservation for Standard Sales Order line against Purchase Order:-
-------------------------------------------------------------------------------------

Note:- You need to have the ASCP Module Installed for the following script to work.

Note:- ASCP decide whether it have to create the PO or Internal sales order
based on the Sourcing Rules defined for a Item in the Inventory Organization.

When any sales Order created for any particular item for any Quantity and if that
quantity is not available then ASCP would created the PO (Purchase order to get the
Material required for third party or could create the Internal requisition which would
again creates the Internal sales order) and It has to reserve the quantity which
the Purchase Order or Internal sales order has.

Here, we are considering if ASCP has decided to create the Purchase Order.

For that you can use the following script.

This document is designed by Dhanunjaya Sadhu. He is one is dedicated resource in ASCP module.

Many thanks to Dhanunjaya Sadhu

/************************************************************
Hard Reservation for Standard Sales Order line against Purchase Order

Created by : Dhanunjaya Sadhu
Creation Date :

Primarily applicable for oracle ASCP implemented projects
Can be customized based on the requirement change.

For others, the input values can be passed directly without CURSOR.

************************************************************/
PROCEDURE create_po_hard_reservation
(p_line_id IN NUMBER,
p_delivery_date IN DATE,
p_ship_from_org_id IN NUMBER)
IS
p_rsv inv_reservation_global.mtl_reservation_rec_type;
p_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
x_rsv_id NUMBER;
x_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_status VARCHAR2(1);
x_qty NUMBER;
l_inventory_item_id number;
l_source_header_id number;
l_reservation_qty number;
l_error_message VARCHAR2(300);
l_order_number NUMBER;

CURSOR C_PO_RESV(c_line_id) ----- Cursor to select Purchas Orders pegged to the standard sales order line
IS

SELECT
DISTINCT pla.PO_HEADER_ID,
pda.PO_DISTRIBUTION_ID,
mfp.ALLOCATED_QUANTITY
FROM PO_LINES_ALL pla,
PO_HEADERS_ALL pha,
PO_DISTRIBUTIONS_ALL pda,
MSC_DEMANDS md,
MSC_SUPPLIES ms,
MSC_FULL_PEGGING mfp,
MSC_PLANS mp
WHERE mp.PLAN_ID=mfp.PLAN_ID
AND mp.plan_id=ms.plan_id
AND mp.plan_id=md.plan_id
AND pla.PO_HEADER_ID=pha.PO_HEADER_ID
AND pla.PO_HEADER_ID=pda.PO_HEADER_ID
AND pla.PO_LINE_ID = pda.PO_LINE_ID
AND pla.PO_HEADER_ID=ms.DISPOSITION_ID
AND pla.PO_LINE_ID= ms.PO_LINE_ID
AND ms.TRANSACTION_ID=mfp.TRANSACTION_ID
AND md.DEMAND_ID=mfp.DEMAND_ID
AND md.SALES_ORDER_LINE_ID=c_line_id;

BEGIN

--- initialize required input params to call reservation api--

--- get the item id--

SELECT DISTINCT INVENTORY_ITEM_ID
INTO l_inventory_item_id
FROM mtl_system_items
WHERE segment1=( SELECT ordered_item
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=p_line_id);


--- get the sales_order_id from mtl_sales_orders
--- table which will be passed as one of the input params-

SELECT SALES_ORDER_ID,segment1
INTO l_source_header_id,l_order_number
FROM mtl_sales_orders
WHERE SEGMENT1=(SELECT TO_CHAR(oh.order_number)
FROM OE_ORDER_HEADERS_ALL oh,OE_ORDER_LINES_ALL ol
WHERE oh.header_id=ol.header_id
AND ol.line_id=p_line_id);

FOR C_REC IN C_PO_RESV(p_line_id)

-------------- sending the sales order line to the cursor variable

LOOP


----- Initialize al required inputs to perform HARD RESERVATION------

--fnd_global.APPS_Initialize(2572700,20559,300);
--p_user_id, p_resp_id, p_resp_appl_id

p_rsv.reservation_id := NULL; -- cannot know

------------will be generated once reservation is successful

p_rsv.requirement_date := p_delivery_date;
p_rsv.organization_id := p_ship_from_org_id;

---------------------------mtl_parameters.organization id

p_rsv.inventory_item_id := l_inventory_item_id;

---------------------------mtl_system_items.Inventory_item_id

p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe;
p_rsv.demand_source_name := NULL;-------'SALES ORDER';
p_rsv.demand_source_header_id :=l_source_header_id;

--------------------mtl_sales_orders.sales_order_id for order number

p_rsv.demand_source_line_id := p_line_id;

-------------------- oe_order_lines.line_id
p_rsv.primary_uom_code := 'EA';
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_code := 'EA';
p_rsv.reservation_uom_id := NULL;
p_rsv.reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.primary_reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.autodetail_group_id := NULL;
p_rsv.external_source_code := NULL;
p_rsv.external_source_line_id := NULL;
p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_po;
p_rsv.supply_source_header_id :=C_REC.PO_HEADER_ID;

------------------------Header id of PO

p_rsv.supply_source_line_id :=C_REC.PO_DISTRIBUTION_ID;

------------------------Distribution id of PO

p_rsv.supply_source_name := NULL;
p_rsv.supply_source_line_detail := NULL;
p_rsv.revision := NULL;
p_rsv.subinventory_code := NULL;

-------------subinventory code can be mentioned

p_rsv.subinventory_id := NULL;
p_rsv.locator_id := NULL;--17930; -- A10-L2-B09
p_rsv.lot_number :=NULL;--'200801225083 ';
p_rsv.lot_number_id := NULL;
p_rsv.pick_slip_number := NULL;
p_rsv.lpn_id := NULL;
p_rsv.attribute_category := NULL;
p_rsv.attribute1 := NULL;
p_rsv.attribute2 := NULL;
p_rsv.attribute3 := NULL;
p_rsv.attribute4 := NULL;
p_rsv.attribute5 := NULL;
p_rsv.attribute6 := NULL;
p_rsv.attribute7 := NULL;
p_rsv.attribute8 := NULL;
p_rsv.attribute9 := NULL;
p_rsv.attribute10 := NULL;
p_rsv.attribute11 := NULL;
p_rsv.attribute12 := NULL;
p_rsv.attribute13 := NULL;
p_rsv.attribute14 := NULL;
p_rsv.attribute15 := NULL;
p_rsv.ship_ready_flag := NULL;
p_rsv.demand_source_delivery := NULL;


------------------------------ CASE II-------------------------------------------

--- Validating whether the pegging is done WITH AN EXTERNAL PURCHASE ORDER---
---- FInally performing Hard Reservation ---------


inv_reservation_pub.create_reservation
(
p_api_version_number => 1.0
, x_return_status => x_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_rsv
, p_serial_number => p_dummy_sn
, x_serial_number => x_dummy_sn
, x_quantity_reserved => x_qty
, x_reservation_id => x_rsv_id
);


IF x_status='S' THEN --- HARD RESERVATION IS DONE SUCCESFULLY ---
COMMIT;

dbms_output.put_line('reservation succesful');
dbms_output.put_line('reservation id: || x_rsv_id);

ELSE

if(nvl(x_msg_count,0) = 0) then
dbms_output.put_line('no message return');
else
for I in 1..x_msg_count LOOP
l_error_message := fnd_msg_pub.get(I, 'F');
end LOOP;
end if;

--- HARD RESERVATION API fails ---

dbms_output.put_line('Reservation API Error Message: '||l_error_message);

END IF;


EXCEPTION
WHEN OTHERS THEN ------------------ In case of any pl/sql error
l_error_message := SQLERRM;
dbms_output.put_line('Plsql Error Message: '||l_error_message);

END create_po_hard_reservation;

Hope you find this document helpful in your work.

PO Requisition associated with PO Orders

PO Requisition associated with PO Orders:-
--------------------------------------------------

We Using following URL, we can find out the all the requisitions (Purchasing Requisitions) which are associated with what all Purchasing orders.

They are 2 types of requisitions.

1) Purchasing Requisition

2) Internal Requisition.

Note:- Only Purchase Requisitions will be converted to the Purchase orders.

SELECT prha.segment1 "Requisition Number",
prha.type_lookup_code "Requisition Type",
pha.segment1 "Purchase Order Number",
pha.type_lookup_code "Purchase Order Type"
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id

Internal Requisitions has not converted to Internal Sales Order

Internal Requisitions has not converted to Internal Sales Order:-
-------------------------------------------------------------------------------

Requisitions (PO Module) would be 2 types.

1) Purchasing Requisitions (Handled by the PO Module)

2) Internal Requisitions (Handled by the OM Module).

We create the Purchase Orders for the Purchasing Requisitions and Internal Sales Orders will be created for the Internal Requisitions.

Note:- We create Internal Sales Order to transfer the material between the Inventories with-in the Organization.

We can use the following Query to find out all the Internal requisitions are created but not converted to the Internal Sales Orders.

SELECT rqha.segment1 "Requisition Number"
, rqla.line_num "Line Number"
, rqla.requisition_header_id "Requisition Header ID"
, rqla.requisition_line_id "Requisition Line ID"
, rqla.item_id "Inventory item ID"
, rqla.unit_meas_lookup_code "Unit Of Measure"
, rqla.unit_price "Unit Price"
, rqla.quantity "Quantity"
, rqla.quantity_cancelled "Quantity Cancelled"
, rqla.quantity_delivered "Quantity Delivered"
, rqla.cancel_flag "Cancelled"
, rqla.source_type_code "Source Type"
, rqla.source_organization_id "Source Organization ID"
, rqla.destination_organization_id "Destination Organization ID"
, rqha.transferred_to_oe_flag "Transferred to OE Flag"
FROM po_requisition_lines_all rqla
, po_requisition_headers_all rqha
WHERE rqla.requisition_header_id = rqha.requisition_header_id
AND rqla.source_type_code = 'INVENTORY'
AND rqla.source_organization_id IS NOT NULL
AND NOT EXISTS ( SELECT 'existing internal order'
FROM oe_order_lines_all line
WHERE line.source_document_line_id =
rqla.requisition_line_id
AND line.source_document_type_id = 10)
ORDER BY rqha.requisition_header_id
, rqla.line_num

Cancel Requisitions

Cancel Requisitions:-
------------------------

You can get all the Canceled Requisitions in the PO Module using the following Query.

SELECT
prha.requisition_header_id "requisition_header_id"
,prha.segment1 "Requisition Number"
,prha.preparer_id "preparer_id"
,TRUNC(prha.creation_date) "creation_date"
,prha.description "description"
,prha.note_to_authorizer "note_to_authorizer"
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code='CANCEL'
AND pah.object_type_code='REQUISITION'
AND pah.object_id=prha.requisition_header_id

SQL and PL/SQL Online

SQL and PL/SQL Online:-
---------------------------------------

You can use the following Oracle URL for practicing SQL and PL/SQL:

http://apex.appshosting.com/pls/apex/

Note:- You need to register for this (which is free) Once you get your Username, Password and Workplace then you can login from anywhere to practice the SQL and PL/SQL.