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.


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

Created by : PRASANTH
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;