[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| - Update_Reservation |
| - Delete_Reservation |
| - Transfer_Reservation |
| - Check_Shipping_Details |
| - Calculate_Prior_Reservations
| |
| HISTORY |
| 21-feb-2000 odaboval Created |
| 7-Nov-2000 odaboval, B1479751 : Added the test and a message. |
| 28-Nov-2000 odaboval, B1504749 : in Query_reservation, swapped columns |
| staged_ind with event_id. |
| 24-AUG-2001 NC Added line_detail_id in the SELECT in Query_Reservation |
| Bug#1675561 |
| 03-OCT-2001 odaboval, local fix for bug 2025611 |
| added procedure Check_Shipping_Details |
| 24-JAN-2002 plowe -- added rounding fix for query_reservation so that |
| reserved quantity rounding for recurring decimals|
| uses GMI: EPSILON for decimal precision rounding |
| in case where primary item UOM is different to |
| ordered quantity UOM. |
| 13-JAN-2003 NC - Added procedure Calculate_prior_reservations. |
| for prior reservations project. Bug#2670928 |
| 23-MAR-2004 P.Raghu Bug#3411704 |
| Modified procedure Update_Reservation such that |
| reserved quantity is calculated if it is equal to |
| FND_API.G_MISS_NUM. |
+========================================================================+
API Name : GMI_Reservation_PVT
Type : Private - Package Body
Function : This package contains Private procedures used to
OPM reservation process.
-
Pre-reqs : N/A
Parameters: Per function
Current Vers : 1.0
*/
/* Global variables */
G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_Reservation_PVT';
SELECT trans_id
, item_id
, line_id
, co_code
, orgn_code
, whse_code
, lot_id
, location
, doc_id
, doc_type
, doc_line
, line_type
, reason_code
, trans_date
, trans_qty
, trans_qty2
, qc_grade
, NULL /* lot no */
, NULL /* sublot no */
, lot_status
, trans_stat
, trans_um
, trans_um2
, staged_ind
, event_id
, text_code
, NULL /* user id */
, NULL /* create_lot_index */
, NULL /* non_inv field */
, line_detail_id
FROM ic_tran_pnd
WHERE doc_type ='OMSO'
AND delete_mark = 0
AND completed_ind = 0
AND line_id = p_query_input.demand_source_line_id
ORDER BY lot_id DESC;
select loct_ctl
from ic_whse_mst
where whse_code = l_allocation_rec.whse_code;
SELECT SUM(ABS(trans_qty))
FROM ic_tran_pnd
WHERE line_id = p_rsv_rec.demand_source_line_id
AND doc_type = 'OMSO'
AND staged_ind = 0
AND completed_ind = 0
AND lot_id <> 0
AND delete_mark = 0;
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c before Create_Default_Lot orgn_code='||l_orgn_code||', trans_id(if not null then UPDATE default_lot)='||l_trans_id);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT c DefaultLot already exist NO Error (Going to update the default lot transaction).');
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Update_Transaction() updating the default record.');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
| Update_Reservation |
| |
| TYPE |
| Global |
| |
| USAGE |
| Update reservation by calling OPM_Allocation manager. |
| |
| DESCRIPTION |
| Update reservation by calling OPM_Allocation manager. |
| |
| PARAMETERS |
| p_init_msg_lst IN VARCHAR2 - Msg init |
| x_return_status OUT VARCHAR2 - Return Status |
| x_msg_count OUT NUMBER - |
| x_msg_data OUT VARCHAR2 - |
| p_validation_flag IN VARCHAR2 - |
| p_original_rsv_rec IN rec_type - |
| p_to_rsv_rec IN rec_type - |
| p_serial_number IN rec_type - |
| x_serial_number OUT rec_type - |
| |
| RETURNS |
| None |
| |
| HISTORY |
| 21-FEB-2000 odaboval Created |
| 23-MAR-2004 P.Raghu Bug#3411704 |
| Reserved quantity is calculated correctly if it|
| is equal to FND_API.G_MISS_NUM. |
| |
+==========================================================================+
Api end of comments
*/
PROCEDURE Update_Reservation
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
, p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
, p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
, p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
, p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
) IS
l_commit VARCHAR2(5) := fnd_api.g_false;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Reservation';
/* GMI_Reservation_Util.Validation_before_Update(
p_mtl_rsv_rec => p_to_rsv_rec
,x_ic_tran_rec => l_ic_tran_rec_out
,x_orgn_code => x_orgn_code
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u : Error Returned by Validation_before_Update');
FND_MESSAGE.SET_NAME('GMI','ERROR_IN_VALIDATION_BEFORE_UPDATE');
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
/* Update the default_lot with Delta*/
/* else*/
/* if NewQty > OldQty*/
/* then*/
/* Update the default_lot with Delta*/
/* else*/
/* Delete the Allocated lot*/
/* and */
/* Update the default_lot with Delta*/
/* endif*/
/* endif*/
/* ======================================================= */
/* Beginning of the process*/
/* =======================================================*/
/* bug 2240221*/
IF p_to_rsv_rec.requirement_date <> FND_API.G_MISS_DATE THEN
l_default_tran_rec.trans_date := p_to_rsv_rec.requirement_date;
GMI_reservation_Util.PrintLn('(opm_dbg)in PVT u: Update PRIM default Lot to:' || l_default_tran_rec.trans_qty );
GMI_reservation_Util.PrintLn('(opm_dbg)in PVT u: Update SECO default Lot to:' || l_default_tran_rec.trans_qty2 );
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_default_tran_rec
,x_tran_row => l_temp_tran_row
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
/* Here we have to update the default_lot, only.*/
l_default_tran_rec.trans_qty := -1 * (ABS(l_default_tran_rec.trans_qty) + ABS(l_new_rsv_to_item_um_qty));
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Update default row trans_qty to '|| l_default_tran_rec.trans_qty);
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_default_tran_rec
,x_tran_row => l_temp_tran_row
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
/* Otherwise call opm delete reservation to remove old allocation*/
/* -------------------------------------------------------------------- */
GMI_reservation_Util.PrintLn('(opm_dbg) in UpdateReserv: The new rsv qty is smaller than the default qty.' );
GMI_reservation_Util.PrintLn('(opm_dbg) in UpdateReserv: We must delete the old reservation.' );
/* Delete the record since it is not the default record*/
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: Deleting transaction record res_id='||p_original_rsv_rec.reservation_id);
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_original_tran_rec
,x_tran_row => l_temp_tran_row
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: ERROR Returned by Delete_Transaction().');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION');
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: After DELETE_PENDING_TRANSACTION.');
/* Transfer the deleted qties to the default_lot + new requested Values.*/
l_default_tran_rec.trans_qty := -1 * (ABS(l_default_tran_rec.trans_qty) + ABS(l_new_rsv_to_item_um_qty));
/* Using the modified copy update the default record by calling the transaction engine*/
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_default_tran_rec
,x_tran_row => l_temp_tran_row
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u: ERROR Returned by Update_Pending_Transaction updating the default record.');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
/* FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');*/
FND_MESSAGE.Set_Token('WHERE', 'Update_Reservation');
END Update_Reservation;
| Delete_Reservation |
| |
| TYPE |
| Global |
| |
| USAGE |
| Delete reservation by calling OPM_Allocation manager. |
| |
| DESCRIPTION |
| Delete reservation by calling OPM_Allocation manager. |
| |
| PARAMETERS |
| x_return_status OUT VARCHAR2 - Return Status |
| x_msg_count OUT NUMBER - |
| x_msg_data OUT VARCHAR2 - |
| p_validation_flag IN VARCHAR2 - |
| p_rsv_rec IN rec_type - |
| p_serial_number IN rec_type - |
| |
| RETURNS |
| None |
| |
| HISTORY |
| 21-FEB-2000 odaboval Created |
| |
+==========================================================================+
Api end of comments
*/
PROCEDURE Delete_Reservation
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
, p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
, p_serial_number IN inv_reservation_global.serial_number_tbl_type
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Reservation';
l_tran_to_delete_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
Select ship_from_org_id
From oe_order_lines_all
Where line_id = l_line_id;
Select whse_code
From ic_whse_mst
Where mtl_organization_id = l_organization_id;
GMI_reservation_Util.PrintLn('(opm_dbg) in proc OPM_Reservation_PVT.OPM_Delete_reservation ');
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: No records found to delete.');
FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: No records found to delete.');
FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
/* If the record is not the default record then just delete the record*/
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: deleting allocation.');
FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
/* Save a copy of the record to be deleted*/
l_tran_to_delete_rec := GMI_Reservation_Util.ic_tran_rec_tbl(l_allocated_lot_index);
/* l_tran_to_delete_rec.non_inv := 0;*/
/* Delete the record since it is not the default record*/
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: Deleting transaction record res_id='||p_rsv_rec.reservation_id||', trans_id='||GMI_Reservation_Util.ic_tran_rec_tbl(l_allocated_lot_index).trans_id );
GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_tran_to_delete_rec
,x_tran_row => l_temp_tran_row
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d After DELETE_PENDING_TRANSACTION.');
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Delete_Transaction().');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION');
FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d: Qties to update: qty=='||l_default_tran_rec.trans_qty||', qty2='||l_default_tran_rec.trans_qty2);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Update_Transaction() updating the default record.');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
/* If the record is the default record then don't delete it just set the quantity to zero*/
l_default_tran_rec.trans_qty := 0 ;
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_default_tran_rec
,x_tran_row => l_temp_tran_row
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Update_Transaction() updating the default record.');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
END Delete_Reservation;
SELECT SUM(nvl(s.onhand_order_qty,0)),
SUM(nvl(s.onhand_order_qty2,0)),
SUM(nvl(s.committedsales_qty,0)),
SUM(nvl(s.committedsales_qty2,0))
FROM ic_summ_inv s
WHERE s.item_id = p_item_id
AND s.whse_code = l_whse_code;
SELECT whse_code ,loct_ctl
FROM IC_WHSE_MST
WHERE mtl_organization_id = l_organization_id ;
SELECT loct_ctl,lot_ctl,noninv_ind
FROM ic_item_mst
WHERE item_id=p_item_id;
Select inventory_item_id
From mtl_system_items_b mtl
, ic_item_mst ic
Where ic.item_id = p_item_id
and mtl.organization_id = p_organization_id
and ic.item_no = mtl.segment1;
SELECT ABS(SUM(nvl(trans_qty,0))),
ABS(SUM(nvl(trans_qty2,0)))
FROM ic_tran_pnd
WHERE item_id = p_item_id
AND whse_code = l_whse_code
AND (lot_id <> 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
AND completed_ind = 0
AND delete_mark = 0
AND trans_qty < 0; -- pending incoming is sorta available, but not real yet
Select sum(nvl(requested_quantity,0)), sum(nvl(requested_quantity2,0))
From wsh_delivery_details
Where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and released_status in ('S', 'Y');
SELECT ABS(SUM(nvl(trans_qty,0))),
ABS(SUM(nvl(trans_qty2,0)))
FROM ic_tran_pnd
WHERE line_id = p_demand_source_line_id
AND line_detail_id = p_delivery_detail_id
AND doc_type='OMSO'
AND completed_ind = 0
AND delete_mark = 0;
SELECT nvl(ship_set_id, 0), source_header_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT NVL(ENFORCE_SHIP_SET_AND_SMC,'N')
FROM WSH_SHIPPING_PARAMETERS
WHERE ORGANIZATION_ID = v_org_id;
SELECT NVL(sum(nvl(requested_quantity,0)),0), NVL(sum(nvl(requested_quantity2,0)),0)
FROM wsh_delivery_details
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND source_code = 'OE'
AND released_status = 'S';
SELECT NVL(ABS(SUM(nvl(trans_qty,0))),0), NVL(ABS(SUM(nvl(trans_qty2,0))),0)
FROM ic_tran_pnd itp
WHERE item_id = p_item_id
AND whse_code = l_whse_code
AND (lot_id <> 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
AND completed_ind = 0
AND delete_mark = 0
AND trans_qty < 0
AND EXISTS(SELECT 1
FROM wsh_delivery_details
WHERE delivery_detail_id = itp.line_detail_id
AND source_code = 'OE'
AND released_status = 'S');
SELECT released_status
FROM wsh_delivery_details
WHERE released_status IN ('Y', 'C')
AND source_line_id = l_so_line_id;
SELECT SUM(nvl(s.onhand_order_qty,0)),
SUM(nvl(s.onhand_order_qty2,0)),
SUM(nvl(s.committedsales_qty,0)),
SUM(nvl(s.committedsales_qty2,0))
FROM ic_summ_inv s
WHERE s.item_id = p_item_id
AND s.whse_code = l_whse_code;
SELECT whse_code
FROM IC_WHSE_MST
WHERE mtl_organization_id = l_organization_id ;
Select grade_ctl
from ic_item_mst
where item_id=p_item_id;
SELECT SUM(nvl(trans_qty,0)),
SUM(nvl(trans_qty2,0))
FROM ic_tran_pnd
WHERE item_id = p_item_id
-- AND line_id = p_demand_source_line_id
AND whse_code = l_whse_code
AND completed_ind = 0
AND delete_mark = 0
-- AND doc_type='OMSO'
AND qc_grade = l_qc_grade
AND line_type = decode(doc_type,'PROD',-1,line_type); --Bug3163165
SELECT SUM(nvl(trans_qty,0)),
SUM(nvl(trans_qty2,0))
FROM ic_tran_pnd
WHERE item_id = p_item_id
-- AND line_id = p_demand_source_line_id
AND whse_code = l_whse_code
AND completed_ind = 0
AND delete_mark = 0
AND line_type = decode(doc_type,'PROD',-1,line_type); --Bug3163165
SELECT SUM(nvl(s.onhand_order_qty,0)),
SUM(nvl(s.onhand_order_qty2,0)),
SUM(nvl(s.committedsales_qty,0)),
SUM(nvl(s.committedsales_qty2,0))
FROM ic_summ_inv s
WHERE s.item_id = p_item_id
AND s.whse_code = l_whse_code
AND s.qc_grade = l_qc_grade
;
SELECT SUM(ABS(nvl(trans_qty,0))),
SUM(ABS(nvl(trans_qty2,0)))
FROM ic_tran_pnd
WHERE line_id = p_demand_source_line_id
AND doc_type='OMSO'
AND completed_ind = 0
AND delete_mark = 0;
SELECT preferred_grade
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT ABS(SUM(nvl(trans_qty,0))),
ABS(SUM(nvl(trans_qty2,0)))
FROM ic_tran_pnd
WHERE line_id = p_demand_source_line_id
AND line_detail_id = p_delivery_detail_id
AND doc_type='OMSO'
AND completed_ind = 0
AND delete_mark = 0;
SELECT whse_code ,loct_ctl
FROM ic_whse_mst
WHERE mtl_organization_id = l_organization_id ;
SELECT loct_ctl,lot_ctl,noninv_ind
FROM ic_item_mst
WHERE item_id=p_item_id;
Select delivery_detail_id, inventory_item_id, organization_id, source_line_id, requested_quantity, requested_quantity2
FROM wsh_delivery_details
WHERE source_header_id = p_source_header_id
AND ship_set_id = p_shipset_id
AND source_code = 'OE'
AND released_status <> 'D';
Select inventory_item_id, Sum(requested_quantity) total_requested
FROM wsh_delivery_details
WHERE source_header_id = p_source_header_id
AND ship_set_id = p_shipset_id
AND source_code = 'OE'
AND released_status <> 'D'
Group by inventory_item_id;
SELECT item_id
FROM ic_item_mst
WHERE delete_mark = 0
AND item_no in (SELECT segment1
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = l_apps_itm_id);
SELECT loct_ctl,lot_ctl,noninv_ind
FROM ic_item_mst
WHERE item_id=l_opm_itm_id;
Select sum(nvl(requested_quantity,0)), sum(nvl(requested_quantity2,0))
From wsh_delivery_details
Where organization_id = p_organization_id
and inventory_item_id = l_apps_itm_id
and released_status in ('S', 'Y');
SELECT ABS(SUM(nvl(trans_qty,0))),
ABS(SUM(nvl(trans_qty2,0)))
FROM ic_tran_pnd
WHERE item_id = l_opm_itm_id
AND whse_code = l_whse_code
AND (lot_id <> 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
AND completed_ind = 0
AND delete_mark = 0
AND trans_qty < 0;
SELECT SUM(nvl(s.onhand_order_qty,0)),
SUM(nvl(s.onhand_order_qty2,0)),
SUM(nvl(s.committedsales_qty,0)),
SUM(nvl(s.committedsales_qty2,0))
FROM ic_summ_inv s
WHERE s.item_id = l_opm_itm_id
AND s.whse_code = l_whse_code;
SELECT NVL(sum(nvl(requested_quantity,0)),0), NVL(sum(nvl(requested_quantity2,0)),0)
FROM wsh_delivery_details
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND source_code = 'OE'
AND released_status = 'S';
SELECT NVL(ABS(SUM(nvl(trans_qty,0))),0), NVL(ABS(SUM(nvl(trans_qty2,0))),0)
FROM ic_tran_pnd itp
WHERE item_id = p_item_id
AND whse_code = l_whse_code
AND (lot_id <> 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
AND completed_ind = 0
AND delete_mark = 0
AND trans_qty < 0
AND EXISTS(SELECT 1
FROM wsh_delivery_details
WHERE delivery_detail_id = itp.line_detail_id
AND source_code = 'OE'
AND released_status = 'S');
gmi_reservation_util.Println('Demand table Updated For Shipset item id ' || l_opm_itm_id );
gmi_reservation_util.println('EXITING Record Updated- RESERVED Shipset id '||p_shipset_id);