[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_flag in varchar2,
p_sort_by_criteria in number,
x_quantity_modified out nocopy number ) is
-- define constants for api version and api name
c_api_version_number constant number := 1.0;
mydebug('p_delete_flag = '|| p_delete_flag ,c_api_name,1);
if upper(nvl(p_delete_flag,'N')) = 'N' then
-- codereview.su.06. swap action types for sorting criteria
if p_mtl_maintain_rsv_rec.action = c_action_demand then
if p_sort_by_criteria is null then
l_sort_by_criteria := inv_reservation_global.g_query_supply_rcpt_date_asc;
elsif upper(nvl(p_delete_flag,'N')) = 'Y' then
-- if delete flag is 'y' sort by criteria will be same as what user has passed.
l_sort_by_criteria := p_sort_by_criteria;
if upper(nvl(p_delete_flag,'N')) = 'Y' then
-- check if there are any reservation records with staged_flag as 'y'
-- if so, throw error
if (g_debug= c_debug_enabled) then
mydebug ('Inside delete flag is Y' ,c_api_name,1);
mydebug ('Deleting reservations, Delete flag: Y' ,c_api_name,1);
inv_reservation_pvt.delete_reservation(
p_api_version_number => 1.0 ,
p_init_msg_lst => fnd_api.g_false ,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rsv_rec => l_original_rsv_rec ,
p_original_serial_number => l_original_serial_number,
p_validation_flag => fnd_api.g_true);
mydebug ('After calling delete: ' || x_return_status ,c_api_name,1);
l_fnd_log_message := 'error while calling delete_reservation api :';
l_fnd_log_message := 'error while calling delete_reservation api :';
l_fnd_log_message := 'calling delete_reservation api was successful:';
elsif upper(nvl(p_delete_flag,'N')) = 'N' then
-- check for expected quantity value
-- dbms_output.put_line('delete flag is no');
-- call update reservation api
-- dbms_output.put_line('calling update reservation api');
mydebug('Update reservation', c_api_name,9);
mydebug('Update qty' || l_primary_need_reduced_qty, c_api_name,9);
inv_reservation_pvt.update_reservation
(
p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_original_rsv_rec => l_original_rsv_rec,
p_to_rsv_rec => l_to_rsv_rec,
p_original_serial_number => l_original_serial_number,
p_to_serial_number => l_to_serial_number,
p_validation_flag => fnd_api.g_true ,
p_check_availability => fnd_api.g_false );
-- check if delete reservation has raised any errors, if so raise exception
if x_return_status = fnd_api.g_ret_sts_error then
l_fnd_log_message := 'error while calling update reservation api 02:';
l_fnd_log_message := 'error while calling update reservation api 02:';
l_fnd_log_message := 'calling update reservation api was successful -02:';
-- call delete reservation
IF g_debug= c_debug_enabled then
mydebug('Call delete reservation for reservation id' || l_mtl_reservation_tbl(i).reservation_id, c_api_name,9);
-- dbms_output.put_line('calling delete reservation api');
inv_reservation_pvt.delete_reservation
(
p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_rsv_rec => l_original_rsv_rec,
p_original_serial_number => l_original_serial_number,
p_validation_flag => fnd_api.g_true );
-- check if delete reservation has raised any errors, if so raise exception
if x_return_status = fnd_api.g_ret_sts_error then
l_fnd_log_message := 'error while calling delete reservation api 02:';
l_fnd_log_message := 'error while calling delete reservation api 02:';
l_fnd_log_message := 'calling delete reservation api was successful -02:';
end if; -- p_delete_flag = y
select decode (h.source_document_type_id, 10,
inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe )
into l_demand_source_type_id
from oe_order_headers_all h, oe_order_lines_all l
where h.header_id = l.header_id
and l.line_id = p_demand_source_line_id;
PROCEDURE UPDATE_RES
(p_supply_source_header_id IN NUMBER
,p_supply_source_line_id IN NUMBER
,p_supply_source_type_id IN NUMBER
,p_primary_uom_code IN VARCHAR2 DEFAULT NULL
,p_primary_reservation_quantity IN NUMBER
,p_reservation_id IN NUMBER
,p_project_id IN NUMBER
,p_task_id IN NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
-- Define Constants for API version and API name
C_api_version_number CONSTANT NUMBER := 1.0;
C_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RES';
inv_reservation_pub.update_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_rsv
, p_to_rsv_rec => l_rsv_new
, p_original_serial_number => g_dummy_sn_tbl -- no serial contorl
, p_to_serial_number => g_dummy_sn_tbl -- no serial control
, p_validation_flag => fnd_api.g_true
);
l_Fnd_Log_message := 'Calling update_reservation API was successful ';
l_Fnd_Log_message := 'Error while calling update_reservation API ';
PROCEDURE DELETE_RES
(p_supply_source_header_id IN NUMBER DEFAULT NULL
,p_supply_source_line_id IN NUMBER DEFAULT NULL
,p_supply_source_type_id IN NUMBER DEFAULT NULL
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
-- Define Constants for API version and API name
C_api_version_number CONSTANT NUMBER := 1.0;
C_api_name CONSTANT VARCHAR2(30) := 'DELETE_RES';
inv_reservation_pub.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => l_rsv
, p_serial_number => g_dummy_sn_tbl
);
l_Fnd_Log_message := 'Calling delete_reservation API was successful ';
l_Fnd_Log_message := 'Error while calling delete_reservation API ';
SELECT item_id
, unit_meas_lookup_code
INTO l_item_id
, l_po_uom
FROM po_lines_all
WHERE po_line_id= p_po_line_id;
SELECT unit_of_measure
INTO l_primary_meas
FROM mtl_units_of_measure
WHERE uom_code = p_primary_uom;
SELECT unit_of_measure
INTO l_res_meas
FROM mtl_units_of_measure
WHERE uom_code = p_res_uom;
SELECT requisition_header_id
, requisition_line_id
, interface_source_line_id
, need_by_date
, item_id
, destination_organization_id
, uom_code
, quantity
, project_id
, task_id
, source_type_code
FROM po_requisitions_interface
WHERE requisition_header_id = p_header_id
AND interface_source_code = 'CTO'
UNION
select prl.requisition_header_id
, prl.requisition_line_id
, interface_source_line_id
, need_by_date
, item_id
, destination_organization_id
, uom_code
, quantity
, project_id
, task_id
, source_type_code
from po_requisition_lines_all prl, po_requisition_headers_all prh , po_req_distributions_all prd, mtl_units_of_measure muom
where prh.requisition_header_id = p_header_id
and prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID
and prd.requisition_line_id = prl.requisition_line_id
and nvl(prl.modified_by_agent_flag,'N') <> 'Y'
AND muom.unit_of_measure= unit_meas_lookup_code
and not exists (select null
FROM po_requisitions_interface pri
where pri.requisition_header_id = prh.requisition_header_id
and pri.interface_source_code = 'CTO');
SELECT header_id
, ordered_quantity
, order_quantity_uom
FROM oe_order_lines_all
WHERE line_id = v_demand_line_id
AND cancelled_flag = 'N';
SELECT sum(nvl(primary_reservation_quantity,0)) sum_pri_res_qty, primary_uom_code
FROM mtl_reservations
WHERE demand_source_header_id = v_demand_header_id
AND demand_source_line_id = v_demand_line_id
AND demand_source_type_id in (inv_reservation_global.g_source_type_oe,
inv_reservation_global.g_source_type_internal_ord)
GROUP BY primary_uom_code;
SELECT 'Exists'
FROM mtl_reservations
WHERE supply_source_header_id = v_requisition_header_id
AND supply_source_line_id = v_requisition_line_id
AND Supply_source_type_id=inv_reservation_global.g_source_type_req;
SELECT pll.po_header_id
,pll.po_line_id
,pll.line_location_id
,pll.ship_to_organization_id
,pl.item_id
FROM po_line_locations_all pll
,po_lines_all pl
WHERE pll.po_header_id = v_po_header_id
AND pl.po_line_id = pll.po_line_id;
SELECT b.requisition_line_id req_line_id
, Nvl(b.project_id,-99) project_id
, Nvl(b.task_id, -99) task_id
-- , sum(a.quantity_ordered) quantity_ordered
FROM po_distributions_all a
, po_req_distributions_all b
WHERE a.line_location_id = v_po_shipment_id
AND a.req_distribution_id = b.distribution_id
AND a.distribution_type <> 'AGREEMENT' --
GROUP BY b.requisition_line_id
, b.project_id
, b.task_id;
SELECT COUNT(min(po_distribution_id)) count
FROM po_distributions_all pd
WHERE pd.line_location_id = v_po_shipment_id
GROUP BY project_id, task_id;
SELECT Nvl(project_id, -99) project_id, Nvl(task_id, -99) task_id
FROM po_distributions_all
WHERE line_location_id = v_po_shipment_id
GROUP BY project_id,task_id;
SELECT reservation_uom_code
, primary_uom_code
, sum(nvl(primary_reservation_quantity,0)) primary_reservation_quantity
FROM mtl_reservations
WHERE supply_source_header_id = v_po_header_id
AND supply_source_line_id = v_po_line_location_id
AND supply_source_type_id = inv_reservation_global.g_source_type_po
AND Nvl(project_id,-99) = nvl(v_project_id, -99)
AND Nvl(task_id,-99) = nvl(v_task_id, -99)
GROUP BY reservation_uom_code
, primary_uom_code;
SELECT pll.po_header_id
, pll.po_line_id
, pll.line_location_id
, pll.ship_to_organization_id --?? is it correct org_id ??
, pl.item_id
FROM po_line_locations_all pll
,po_lines_all pl
WHERE pll.po_release_id = v_po_header_id
AND pll.po_line_id = pl.po_line_id;
SELECT requisition_line_id, source_type_code
FROM po_requisition_lines_all --
WHERE requisition_header_id = v_po_req_header_id;
SELECT po_header_id
FROM po_lines_all --
WHERE po_line_id = v_po_line_id;
SELECT pll.po_header_id
, pll.po_line_id
, pll.line_location_id
, pll.ship_to_organization_id
, pl.item_id
, pll.quantity
FROM po_line_locations_all pll, po_lines_all pl --
WHERE pl.po_line_id = v_po_line_id
AND pl.po_line_id = pll.po_line_id;
SELECT po_header_id
FROM po_line_locations_all --
WHERE line_location_id = p_line_location_id;
SELECT pll.po_header_id
, pll.po_line_id
, pll.line_location_id
, pll.ship_to_organization_id
, pl.item_id
, pll.quantity
FROM po_line_locations_all pll, po_lines_all pl--
WHERE pll.line_location_id = v_po_line_location_id AND
pl.po_line_id = pll.po_line_id;
SELECT po_header_id
FROM po_releases_all --
WHERE po_release_id = v_po_header_id;
SELECT po_header_id
, po_line_id
, line_location_id
, req_distribution_id
, quantity_ordered
FROM po_distributions_all --
WHERE po_release_id = v_po_header_id;
SELECT source_document_code, organization_id FROM rcv_transactions
WHERE transaction_id = v_transaction_id;
SELECT decode(a.source_document_code,'PO'
, decode(b.asn_line_flag, 'Y', 'ASN', 'PO'), a.source_document_code) supply_type
, a.po_header_id, a.po_line_id
, a.po_line_location_id
, a.po_distribution_id
, d.uom_code primary_unit_of_measure
, a.primary_quantity
, a.requisition_line_id
, a.req_distribution_id
, a.shipment_line_id
, a.shipment_header_id
, a.subinventory
, a.locator_id
, a.organization_id
, a.lpn_id
, b.item_revision
, b.item_id
, b.to_organization_id
, c.project_id
, c.task_id
FROM rcv_transactions a
, rcv_shipment_lines b
, po_distributions_all c
, mtl_units_of_measure d
WHERE transaction_id = v_transaction_id
AND a.shipment_line_id = b.shipment_line_id
AND c.po_distribution_id = a.po_distribution_id
AND c.po_header_id = a.po_header_id
AND c.po_line_id = a.po_line_id
AND c.line_location_id = a.po_line_location_id
AND d.unit_of_measure = a.primary_unit_of_measure;
SELECT lot_num
, primary_quantity
FROM rcv_lot_transactions
WHERE shipment_line_id = v_shipment_line_id;
SELECT lot_num
, primary_quantity
FROM rcv_lot_transactions
WHERE shipment_line_id = v_shipment_line_id
AND transaction_id = v_transaction_id;
SELECT a.po_header_id
, a.po_line_id
, a.po_line_location_id
, a.po_distribution_id
, d.uom_code primary_unit_of_measure
, a.primary_quantity
, a.shipment_line_id
, a.subinventory
, a.locator_id
, a.organization_id
, a.lpn_id
, b.item_revision
, b.item_id
, b.to_organization_id
, c.project_id
, c.task_id
FROM rcv_transactions a
, rcv_shipment_lines b
, po_distributions_all c
, mtl_units_of_measure d
WHERE a.transaction_id = v_transaction_id
AND a.source_document_code = 'PO'
AND b.shipment_line_id = a.shipment_line_id
AND b.asn_line_flag = 'Y'
AND c.po_distribution_id = a.po_distribution_id
AND c.po_header_id = a.po_header_id
AND c.po_line_id = a.po_line_id
AND c.line_location_id = a.po_line_location_id
AND d.unit_of_measure = a.primary_unit_of_measure;
SELECT a.requisition_line_id
, e.uom_code primary_unit_of_measure
, a.primary_quantity
, a.shipment_line_id
, a.subinventory
, a.locator_id
, a.organization_id
, a.lpn_id
, b.item_revision
, b.item_id
, b.to_organization_id
, c.project_id
, c.task_id
, d.requisition_header_id
FROM rcv_transactions a
, rcv_shipment_lines b
, po_req_distributions_all c
, po_requisition_lines_all d
, mtl_units_of_measure e
WHERE a.transaction_id = v_transaction_id
AND a.source_document_code = 'REQ'
AND b.shipment_line_id = a.shipment_line_id
AND c.distribution_id = a.req_distribution_id
AND c.requisition_line_id = a.requisition_line_id
AND d.requisition_line_id = c.requisition_line_id
AND e.unit_of_measure = a.primary_unit_of_measure;
l_delete_flag VARCHAR2(1);
SELECT POSTPROCESSING_LEAD_TIME
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = v_item_id
AND ORGANIZATION_ID = v_org_id;
l_Fnd_Log_Message := 'calling update_res API ';
update_res
(p_supply_source_header_id => l_rsv_rec.supply_source_header_id
,p_supply_source_line_id => l_rsv_rec.supply_source_line_id
,p_supply_source_type_id => inv_reservation_global.g_source_type_req
,p_primary_reservation_quantity => l_qty_avail_to_reserve
,p_project_id => get_req_line_po_shipment_rec.project_id
,p_task_id => get_req_line_po_shipment_rec.task_id
,p_reservation_id => l_rsv_rec.reservation_id
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status);
-- delete all the reservations for this shipment
-- log message
IF g_debug= C_Debug_Enabled THEN
l_Fnd_Log_Message := 'Organization is wms org. calling delete_res API with IN parameters';
-- Call reduce reservations instead of delete
-- reservations
-- Call the reduce reservations API by setting the
-- delete_flag to yes. delete all reservations for that
-- supply line.
-- calling reduce_reservation API
l_delete_flag := 'Y';
, p_delete_flag => l_delete_flag
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);
/***** Call reduce reservations instead of delete reservations
DELETE_RES
(p_supply_source_header_id => p_header_id
,p_supply_source_line_id => get_po_shipment_rec.line_location_id
,p_supply_source_type_id => inv_reservation_global.g_source_type_po
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status);
, p_delete_flag => 'N'
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);
SELECT max(mir.revision)
INTO l_item_revision
FROM mtl_system_items msi
, mtl_item_revisions mir
WHERE msi.inventory_item_id = get_rcv_transaction_rec.item_id
AND msi.organization_id = get_rcv_transaction_rec.organization_id
AND msi.revision_qty_control_code = 2
AND mir.organization_id = msi.organization_id
AND msi.inventory_item_id = msi.inventory_item_id
AND mir.effectivity_date in
(SELECT MAX(mir2.effectivity_date)
FROM mtl_item_revisions mir2
WHERE mir2.organization_id = get_rcv_transaction_rec.organization_id
AND mir2.inventory_item_id = get_rcv_transaction_rec.item_id
AND mir2.effectivity_date <= SYSDATE
AND mir2.implementation_date is not NULL);
item revision to inventory so that supply gets updated accordingly.
*/
IF g_debug= C_Debug_Enabled THEN
l_Fnd_Log_Message := 'get_rcv_transaction_rec.item_revision is NOT NULL ';
SELECT msi.revision_qty_control_code
INTO l_revision_control_code
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = get_rcv_transaction_rec.item_id
AND msi.organization_id = get_rcv_transaction_rec.organization_id;
select nvl(lot_control_code,1)
into l_lot_control_code
from mtl_system_items
where organization_id = get_rcv_transaction_rec.to_organization_id
and inventory_item_id = get_rcv_transaction_rec.item_id ;
SELECT max(mir.revision)
INTO l_item_revision
FROM mtl_system_items msi
, mtl_item_revisions mir
WHERE msi.inventory_item_id = get_rcv_transaction_asn_rec.item_id
AND msi.organization_id = get_rcv_transaction_asn_rec.organization_id
AND msi.revision_qty_control_code = 2
AND mir.organization_id = msi.organization_id
AND msi.inventory_item_id = msi.inventory_item_id
AND mir.effectivity_date in
(SELECT MAX(mir2.effectivity_date)
FROM mtl_item_revisions mir2
WHERE mir2.organization_id = get_rcv_transaction_asn_rec.organization_id
AND mir2.inventory_item_id = get_rcv_transaction_asn_rec.item_id
AND mir2.effectivity_date <= SYSDATE
AND mir2.implementation_date is not NULL);
item revision to inventory so that supply gets updated accordingly.
*/
BEGIN
SELECT msi.revision_qty_control_code
INTO l_revision_control_code
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = get_rcv_transaction_asn_rec.item_id
AND msi.organization_id = get_rcv_transaction_asn_rec.organization_id;
select nvl(lot_control_code,1)
into l_lot_control_code
from mtl_system_items
where organization_id = get_rcv_transaction_rec.to_organization_id
and inventory_item_id = get_rcv_transaction_rec.item_id ;
SELECT max(mir.revision)
INTO l_item_revision
FROM mtl_system_items msi
, mtl_item_revisions mir
WHERE msi.inventory_item_id = get_rcv_txn_int_req_rec.item_id
AND msi.organization_id = get_rcv_txn_int_req_rec.organization_id
AND msi.revision_qty_control_code = 2
AND mir.organization_id = msi.organization_id
AND msi.inventory_item_id = msi.inventory_item_id
AND mir.effectivity_date in
(SELECT MAX(mir2.effectivity_date)
FROM mtl_item_revisions mir2
WHERE mir2.organization_id = get_rcv_txn_int_req_rec.organization_id
AND mir2.inventory_item_id = get_rcv_txn_int_req_rec.item_id
AND mir2.effectivity_date <= SYSDATE
AND mir2.implementation_date is not NULL);
item revision to inventory so that supply gets updated accordingly.
*/
--Bug 5147013: Changed the cursor name to get the
-- item and org from the correct one.
BEGIN
SELECT msi.revision_qty_control_code
INTO l_revision_control_code
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = get_rcv_txn_int_req_rec.item_id
AND msi.organization_id = get_rcv_txn_int_req_rec.organization_id;
select nvl(lot_control_code,1)
into l_lot_control_code
from mtl_system_items
where organization_id = get_rcv_transaction_rec.to_organization_id
and inventory_item_id = get_rcv_transaction_rec.item_id ;
update_res
(p_supply_source_header_id => l_rsv_rec.supply_source_header_id
,p_supply_source_line_id => l_rsv_rec.supply_source_line_id
,p_supply_source_type_id => inv_reservation_global.g_source_type_req
,p_primary_reservation_quantity => l_qty_avail_to_reserve
,p_project_id => get_req_line_po_shipment_rec.project_id
,p_task_id => get_req_line_po_shipment_rec.task_id
,p_reservation_id => l_rsv_rec.reservation_id
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status);
-- delete all the reservations for this shipment
-- log message
-- Commenting out the delete reservation call. Call
-- reduce reservations instead
-- DELETE_RES
-- (p_supply_source_header_id => get_po_shipment_rel_rec.po_header_id
-- ,p_supply_source_line_id => get_po_shipment_rel_rec.line_location_id
--,p_supply_source_type_id => inv_reservation_global.g_source_type_po
-- ,x_msg_count => x_msg_count
-- ,x_msg_data => x_msg_data
-- ,x_return_status => l_return_status);
-- Call reduce reservations instead of delete
-- reservations
-- Call the reduce reservations API by setting the
-- delete_flag to yes. delete all reservations for that
-- supply line.
-- calling reduce_reservation API
l_delete_flag := 'Y';
, p_delete_flag => l_delete_flag
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);
, p_Delete_Flag => 'N'
, p_Sort_By_Criteria => NULL
, x_Quantity_Modified => l_quantity_modified);
DELETE_RES
(p_supply_source_header_id => p_header_id
,p_supply_source_line_id => get_req_hdr_lines_rec.requisition_line_id
,p_supply_source_type_id => l_supply_source_type_id
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status);
--delete the reservation on the req
--
IF g_debug= C_Debug_Enabled THEN
mydebug ('Remove req line supply. req line:'|| p_line_id ,c_api_name,9);
SELECT source_type_code INTO l_source_type_code FROM
po_requisition_lines_all WHERE requisition_line_id = p_line_id;
DELETE_RES
(p_supply_source_line_id => p_line_id
,p_supply_source_type_id => l_supply_source_type_id
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status);
-- If Reservation Exists, then delete them and never transfer back to requistion
-- since when PO is cancelled, associated req got
-- cancelled too.
IF g_debug= C_Debug_Enabled THEN
mydebug ('Cancel PO supply. Supply header: '|| p_header_id ,c_api_name,9);
l_delete_flag := 'Y';
, p_delete_flag => l_delete_flag
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);
-- DELETE_RES (p_supply_source_header_id => l_po_header_id
-- ,p_supply_source_line_id => get_distr_rec.line_location_id
-- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
-- ,x_msg_count => x_msg_count
-- ,x_msg_data => x_msg_data
-- ,x_return_status => l_return_status);
l_delete_flag := 'Y';
, p_delete_flag => l_delete_flag
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);
-- DELETE_RES (p_supply_source_header_id => l_po_header_id
-- ,p_supply_source_line_id => get_distr_rec.line_location_id
-- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po--
-- ,x_msg_count => x_msg_count
---- ,x_msg_data => x_msg_data
-- ,x_return_status => l_return_status);
l_delete_flag := 'Y';
, p_delete_flag => l_delete_flag
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);
--delete the reservation
-- OPEN get_distr_for_po_release(p_header_id);
--reservation should be deleted
OPEN get_po_shipment(p_header_id);
--DELETE_RES (p_supply_source_header_id => p_header_id
-- ,p_supply_source_line_id => get_po_shipment_rec.line_location_id
-- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
-- ,x_msg_count => x_msg_count
-- ,x_msg_data => x_msg_data
-- ,x_return_status => l_return_status);
l_delete_flag := 'Y';
, p_delete_flag => l_delete_flag
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);
-- DELETE_RES (p_supply_source_header_id => l_po_header_id
-- ,p_supply_source_line_id => get_distr_rec.line_location_id
-- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
-- ,x_msg_count => x_msg_count
-- ,x_msg_data => x_msg_data
-- ,x_return_status => l_return_status);
l_delete_flag := 'Y';
, p_delete_flag => l_delete_flag
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);
ELSIF upper(p_action) = 'UPDATE_SO_QUANTITY' THEN
--reduce the reservation on the req by the ordered quantity
IF g_debug= C_Debug_Enabled THEN
mydebug ('Inside update so qty. req line:'|| p_line_id ,c_api_name,9);
SELECT Nvl(SUM(primary_reservation_quantity),0) INTO
l_primary_res_qty FROM mtl_reservations
WHERE supply_source_type_id =
inv_reservation_global.g_source_type_internal_req AND
supply_source_header_id = p_header_id AND
supply_source_line_id = p_line_id;
SELECT destination_organization_id, item_id, unit_meas_lookup_code, quantity INTO
l_organization_id,
l_inventory_item_id,
l_req_unit_meas, l_req_qty FROM
po_requisition_lines_all WHERE requisition_line_id = p_line_id;
-- delete all reservations for that req line
-- calling reduce_reservation API
l_delete_flag := 'Y';
, p_delete_flag => l_delete_flag
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);
, p_delete_flag => 'N'
, p_sort_by_criteria => l_sort_by_criteria
, x_quantity_modified => l_quantity_modified);