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.