[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 09/16/02 HAW BUG#:2536589 New procedures: update_opm_trxns, |
| and find_lot_id. |
| These procedures are called from OM file |
| OEXVIIFB.pls in procedure Inventory_Interface |
| Thess procedures will be called if user uses |
| the Bill To functionality from Order Pad |
| 9/29/02 NC Added p_commit parameter for Set_pick_lots inorder|
| to support the Public API allocate_opm_orders. |
| added IF condition to commit only if this commit |
| flag is set. |
| Oct, 2002 HW Added new procedures to support WSH.I - |
| Harmonization project. |
| Two new procedures:Validate_lot_number and |
| line_allocated |
| |
| Nov, 2002 HW BUG#:2654963 Added p_delivery_detail_id to proc |
| line_allocated |
| Nov, 2002 HW BUG#:2654963 Added p_delivery_detail_id to proc. |
| line_allocated. |
| |
| Nov, 2002 HW bug#2677054 - WSH.I project |
| Feb, 2003 PK Bug#2749329 - Commented call to Lock_inventory in |
| set_pick_lots. |
| Apr, 2004 Vipul BUG#3503593 - Added code in Procedure |
| create_transaction_for_rcv to convert the qty into|
| item's uom. |
| Aug 2004 Plowe BUG#3770264 - Added code in Procedures |
| create_transaction_for_rcv, set_pick_lots and |
| create_dflt_lot_from_scratch to |
| retrieve correct lang |
| for retrieval of mtl_sales_orders |
+=========================================================================+
API Name : GMI_Reservation_Util
Type : Private Package Body
Function : This package contains Private Utilities 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_UTIL';
SELECT inventory_item_id,
order_quantity_uom
FROM oe_order_lines_all
WHERE line_id = om_line_id;
GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Error in Select='||SQLCODE||'.');
Select SCHEDULE_SHIP_DATE
From oe_order_lines_all
Where line_id = l_line_id;
/* Fill the ic_tran_pnd record type, and then insert into ic_tran_pnd */
l_ictran_rec.item_id := p_ic_item_mst_rec.item_id;
before call update_pending_transaction qty1='||l_ictran_rec.trans_qty||' '
||l_ictran_rec.trans_um||', qty2='||l_ictran_rec.trans_qty2||' '
||l_ictran_rec.trans_um2||', trans_id='||l_ictran_rec.trans_id);
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_tran_rec => l_ictran_rec
, x_tran_row => l_tran_row
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.' );
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
SELECT oel.sold_to_org_id
, oel.ship_to_org_id
, oel.line_number + (oel.shipment_number / 10)
, oel.org_id
FROM oe_order_lines_all oel
WHERE oel.line_id = oe_line_id;
SELECT user_id,
user_name
FROM fnd_user
WHERE user_id = FND_GLOBAL.USER_ID;
SELECT sy.um_code
FROM mtl_units_of_measure mtl,
sy_uoms_mst sy
WHERE sy.unit_of_measure = mtl.unit_of_measure
AND mtl.uom_code = discrete_uom;
SELECT sy.um_code
FROM mtl_units_of_measure mtl,
sy_uoms_mst sy
WHERE sy.um_code = mtl.unit_of_measure
AND mtl.uom_code = discrete_uom;
SELECT mtl.uom_code
FROM mtl_units_of_measure mtl,
sy_uoms_mst sy
WHERE sy.unit_of_measure = mtl.unit_of_measure
AND sy.um_code = process_uom;
SELECT mtl.uom_code
FROM mtl_units_of_measure mtl,
sy_uoms_mst sy
WHERE sy.um_code = mtl.unit_of_measure
AND sy.um_code = process_uom;
SELECT ship_from_org_id
FROM oe_order_lines_all
WHERE line_id = oe_line_id;
SELECT item_id
, discrete_item_id
, item_no
, whse_item_id
, item_um
, item_um2
, dualum_ind
, alloc_class
, noninv_ind
, deviation_lo
, deviation_hi
, grade_ctl
, inactive_ind
, lot_ctl
, lot_indivisible
, loct_ctl
FROM ic_item_mst
WHERE delete_mark = 0
AND item_no in (SELECT segment1
FROM mtl_system_items
WHERE organization_id = discrete_org_id
AND inventory_item_id = discrete_item_id);
SELECT preferred_grade,
ordered_quantity2 * (-1),
ordered_quantity_uom2,
ship_from_org_id, -- BUG 3538734
inventory_item_id
FROM oe_order_lines_all
WHERE line_id = om_line_id;
select NVL(SUM(ABS(TRANS_QTY)),0)
into x_allocated_qty
from ic_tran_pnd
where line_id = p_query_input.demand_source_line_id
and trans_id <> l_default_tran_rec.trans_id
and (lot_id <> 0 or location <> gmi_reservation_util.g_default_loct)
and doc_type='OMSO'
and delete_mark =0
and completed_ind=0;
select NVL(SUM(ABS(TRANS_QTY)),0)
into x_allocated_qty
from ic_tran_pnd
where line_id = p_query_input.demand_source_line_id
and lot_id = 0
and location = gmi_reservation_util.g_default_loct
and doc_type='OMSO'
and delete_mark =0
and completed_ind=0;
Select count(*)
INTO x_allocated_trans
From ic_tran_pnd
Where line_id = p_query_input.demand_source_line_id
And line_detail_id = p_query_input.attribute4
And delete_mark=0;
select loct_ctl
from ic_whse_mst
where mtl_organization_id = org_id;
SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value)
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT sy.co_code,
sy.orgn_code
FROM sy_orgn_mst sy,
ic_whse_mst wh
WHERE sy.orgn_code = wh.orgn_code
AND wh.whse_code = whse;
/* If new_qty = 0, then delete reservation */
/* If new_qty > 0, then update transaction */
/* if trans_id = 0, then : */
/* Create a new transaction. */
/* */
/* line_id is mandatory in any case. */
/* when trans_id=0, then whse_code is mandatory. */
/* */
/* co_code and orgn_code are null when they are passed by ACCEPT(Pick_Lot) */
/* doc_line and doc_id are null when they are passed by ACCEPT(Pick_Lot) */
/* */
/* Note that each UOM will be in passed in p_ic_tran_rec as AppsUOM (3char). */
/* Need to be converted back to OPMUOM. */
/* */
/* Note2 : If default lot, then the lot_Status has to be null. */
/* */
/* The item_id is the OPM one. */
/* ======================================================================== */
PROCEDURE Set_Pick_Lots
( p_ic_tran_rec IN OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
, p_mo_line_id IN NUMBER
, p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Set_Pick_Lots';
l_need_update_default_lot BOOLEAN;
l_deleted_qty1 NUMBER(19,9);
l_deleted_qty2 NUMBER(19,9);
select loct_ctl
from ic_whse_mst
where mtl_organization_id = org_id;
SELECT sol.ship_from_org_id
, mtl.sales_order_id
, sol.line_id
, sol.schedule_ship_date
, sol.line_number + (sol.shipment_number / 10)
, sol.inventory_item_id
, sol.ship_to_org_id
FROM oe_order_lines_all sol
, oe_order_headers_all soh
, oe_transaction_types_tl tt
, mtl_sales_orders mtl
WHERE mtl.segment1 = to_char(soh.order_number)
AND mtl.segment2 = tt.name
AND mtl.segment3 = fnd_profile.value('ONT_SOURCE_CODE')
-- AND tt.language = userenv('LANG') -- OPM bug 3770264
AND tt.language = (select language_code -- OPM bug 3770264
from fnd_languages
where installed_flag = 'B')
AND tt.transaction_type_id = soh.order_type_id
AND soh.header_id = sol.header_id
AND sol.line_id = so_line_id ;
Select opc.cust_no
From op_cust_mst opc
, sy_orgn_mst som
, ic_whse_mst whse
Where whse.mtl_organization_id = l_organization_id
and whse.orgn_code = som.orgn_code
and som.co_code =opc.co_code
and opc.of_ship_to_site_use_id(+) = l_site_use_id ;
l_need_update_default_lot := TRUE;
(l_op_alot_prm_rec.delete_mark = 1))
THEN
GMI_Reservation_Util.PrintLn('(opm_dbg) allocation - Error missing allocation parms',1);
/* It is not possible to allocate (create/update) if the period between */
/* trans_date and sysdate is greater than the Horizon period. */
/* --------------------------------------------------------------------- */
IF (l_op_alot_prm_rec.alloc_horizon > 0) AND
(p_ic_tran_rec.trans_date > (SYSDATE + l_op_alot_prm_rec.alloc_horizon))
THEN
GMI_Reservation_Util.PrintLn('(opm_dbg) allocation horizon is out - using '|| l_op_alot_prm_rec.alloc_horizon||' days.');
l_need_update_default_lot := FALSE;
/* to update the memory table GMI_Reservation_Util.ic_tran_rec_tbl. */
/* ======================================================================================= */
END IF;
l_need_update_default_lot := FALSE;
/* to update the memory table GMI_Reservation_Util.ic_tran_rec_tbl. */
/* ======================================================================================= */
END IF;
/* At this stage, it is either a delete (allocated transaction) or an update (default lot transaction) : */
/* Make the choice here : */
/* ============================================================================================== */
/* --------------------------------------------------------------------------------------- */
/* Populate local original rec to hold values for comparision */
/* if this is not the default rec copy the original rec to l_original_tran_rec */
/* else this is the default rec copy the default rec to l_original_tran_rec */
/* --------------------------------------------------------------------------------------- */
GMI_reservation_Util.PrintLn('opm_dbg) in Set_Pick_Lots: l_default_tran_rec.trans_id is ' || l_default_tran_rec.trans_id, 'pick_lots.log');
/* Update Transaction (either update qty - if no Change lot, or Delete/Create - if Change lot) */
/* Delete Transaction */
/* Create a new transaction */
/* ============================================================================================== */
GMI_reservation_Util.PrintLn('(opm_dbg)in Set_Pick_Lots: What to do : trans_id='||ll_ic_tran_rec.trans_id||', trans_qty='||ll_ic_tran_rec.trans_qty||', trans_date='||ll_ic_tran_rec.trans_date, 'pick_lots.log');
/* If it is >0, We won't have to update the default lot. NO*/
/* If it is >0, update the default qty = 0 NO*/
/* ======================================================= */
l_delta_qty1 := l_original_tran_rec.trans_qty + ll_ic_tran_rec.trans_qty;
GMI_reservation_Util.PrintLn('(opm_dbg)in Set_Pick_Lots: Update the transaction qty to:' || l_original_tran_rec.trans_qty , 'pick_lots.log');
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_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 Set_Pick_Lots: Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.', 'pick_lots.log');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
/* Need to delete the Original transaction, and then create a new one. */
GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_Lots: Change of Lot...', 'pick_lots.log');
/* trans_id >0, and Qty = 0, then delete the transaction */
/* ========================================================================= */
IF (ll_ic_tran_rec.trans_id > 0 AND ll_ic_tran_rec.trans_qty = 0)
OR (l_change_lot = TRUE)
THEN
GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, Before calling Delete_Reservation', 'pick_lots.log');
GMI_Reservation_PVT.Delete_Reservation(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_validation_flag => l_validation_flag
, p_rsv_rec => l_rsv_rec
, p_serial_number => l_serial_number
);
GMI_Reservation_Util.PrintLn('(opm_dbg) In Set_Pick_Lots, Error returned by Delete_Reservation, Error='||x_return_status, 'pick_lots.log');
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Reservation_PVT.Delete_Reservation');
UPDATE ic_tran_pnd
SET pick_slip_number = l_pick_slip_number
WHERE trans_id = l_temp_tran_row.trans_id;
select *
into l_ic_txn_request_lines
from ic_txn_request_lines
where LINE_ID = p_mo_line_id
for update OF quantity_detailed, secondary_quantity_detailed NOWAIT;
SELECT SUM(ABS(TRANS_QTY)), SUM(ABS(TRANS_QTY2))
INTO l_NEW_ALLOCATED_QTY, l_NEW_ALLOCATED_QTY2
from ic_tran_pnd
where line_id = p_ic_tran_rec.line_id
and staged_ind = 0
and completed_ind = 0
and delete_mark = 0
and lot_id <> 0
and doc_type = 'OMSO'
and line_detail_id in
(Select delivery_detail_id
From wsh_delivery_details
Where move_order_line_id = p_mo_line_id);
SELECT SUM(ABS(TRANS_QTY)), SUM(ABS(TRANS_QTY2))
INTO l_NEW_ALLOCATED_QTY, l_NEW_ALLOCATED_QTY2
from ic_tran_pnd
where line_id = p_ic_tran_rec.line_id
and staged_ind = 0
and completed_ind = 0
and delete_mark = 0
and location <> GMI_Reservation_Util.G_DEFAULT_LOCT
and doc_type = 'OMSO'
and line_detail_id in
(Select delivery_detail_id
From wsh_delivery_details
Where move_order_line_id = p_mo_line_id);
SELECT SUM(ABS(TRANS_QTY)), SUM(ABS(TRANS_QTY2))
INTO l_NEW_ALLOCATED_QTY, l_NEW_ALLOCATED_QTY2
from ic_tran_pnd
where line_id = p_ic_tran_rec.line_id
and staged_ind = 0
and completed_ind = 0
and delete_mark = 0
and doc_type = 'OMSO';
update ic_txn_request_lines
set quantity_detailed = nvl(quantity_delivered,0) + NVL(l_NEW_ALLOCATED_QTY,0)
, secondary_quantity_detailed = nvl(secondary_quantity_delivered,0) + NVL(l_NEW_ALLOCATED_QTY2,0)
where line_id = p_mo_line_id;
Select nvl(sum(trans_qty),0), nvl(sum(trans_qty2),0)
From ic_tran_pnd
Where line_id = l_line_rec.line_id
And doc_type = 'OMSO'
And item_id = p_opm_item_id
And delete_mark = 0
And (lot_id <> 0
OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
Select nvl(sum(trans_qty),0), nvl(sum(trans_qty2),0)
From ic_tran_pnd
Where line_id = l_line_rec.line_id
And doc_type = 'OMSO'
And item_id = p_opm_item_id
And (staged_ind = 1 or completed_ind = 1)
And delete_mark = 0
And (lot_id = 0 AND location = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
Select order_quantity_uom
,NVL(ordered_quantity,0)
,NVL(ordered_quantity2,0)
,ship_from_org_id
,inventory_item_id
From oe_order_lines_all
Where line_id = l_line_rec.line_id;
Select whse_code
, orgn_code
From ic_whse_mst
Where mtl_organization_id = l_organization_id;
Select ic.item_id
, ic.item_um
, ic.item_um2
From ic_item_mst ic
, mtl_system_items mtl
Where mtl.organization_id = p_org_id
and mtl.inventory_item_id = p_inv_Item_id
and mtl.segment1 = ic.item_no;
/* check the whse, if it is changed, simply delete the trans and create a new default */
Open get_whse_code;
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_ic_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 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 balancing the default lot, update pending trans');
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_ic_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
);
FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
/* Fill the ic_tran_pnd record type, and then insert into ic_tran_pnd */
l_original_tran_rec.item_id := p_ic_tran_rec.item_id;
/* Need to update the memory table of Reservation. */
/* ==================================================================================== */
l_rsv_rec.organization_id := p_organization_id;
SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */trans_id
FROM ic_tran_pnd
WHERE lot_id = 0
AND delete_mark = 0
AND doc_type = 'OMSO'
AND item_id = l_item_id
AND line_id = l_line_id;
Select w.orgn_code, co.co_code
From ic_whse_mst w, sy_orgn_mst co
Where w.whse_code = p_whse_code
AND w.orgn_code = co.orgn_code;
Select mtl.sales_order_id
From mtl_sales_orders mtl,
oe_order_lines_all sol,
oe_order_headers_all soh,
oe_transaction_types_tl tt
Where sol.line_id = p_line_id
AND mtl.segment1 = to_char(soh.order_number)
AND mtl.segment2 = tt.name
-- AND tt.language = userenv('LANG') -- OPM bug 3770264
AND tt.language = (select language_code -- OPM bug 3770264
from fnd_languages
where installed_flag = 'B')
AND mtl.segment3 = fnd_profile.value('ONT_SOURCE_CODE')
AND tt.transaction_type_id = soh.order_type_id
AND sol.header_id = soh.header_id;
Select item_um, item_um2
From ic_item_mst
Where item_id = p_item_id;
Select SCHEDULE_SHIP_DATE, line_number+(shipment_number / 10)
From oe_order_lines_all
Where line_id = l_line_id;
Select w.orgn_code
, co.co_code
, w.loct_ctl
From ic_whse_mst w, sy_orgn_mst co
Where w.whse_code = p_whse_code
AND w.orgn_code = co.orgn_code;
Select mtl.sales_order_id
, sol.inventory_item_id
From mtl_sales_orders mtl,
oe_order_lines_all sol,
oe_order_headers_all soh,
oe_transaction_types_tl tt
Where sol.line_id = p_line_id
AND mtl.segment1 = to_char(soh.order_number)
AND mtl.segment2 = tt.name
AND mtl.segment3 = fnd_profile.value('ONT_SOURCE_CODE')
--AND tt.language = userenv('LANG') -- OPM bug 3770264
AND tt.language = (select language_code -- OPM bug 3770264
from fnd_languages
where installed_flag = 'B')
AND tt.transaction_type_id = soh.order_type_id
AND sol.header_id = soh.header_id;
Select item_um
, item_um2
, loct_ctl
From ic_item_mst
Where item_id = p_item_id;
Select transaction_date
, locator_id
, transaction_type
, quantity
, uom_code --BUG#3503593
From rcv_transactions
Where transaction_id = p_transaction_id;
Select qc_grade
From ic_lots_mst
Where lot_id = p_lot_id;
Select location
From ic_loct_mst
Where inventory_location_id = p_locator_id;
Select segment1
From mtl_item_locations
Where inventory_location_id = p_locator_id;
Select lot_status
From ic_loct_inv
Where item_id = p_item_id
and lot_id = p_lot_id
and location = p_location
and whse_code = p_whse_code;
Select SCHEDULE_SHIP_DATE, line_number+(shipment_number / 10)
From oe_order_lines_all
Where line_id = l_line_id;
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
(
p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_tran_rec => l_ictran_rec
,x_tran_row => l_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) Error return by UPDATE_PENDING_TO_COMPLETED,
return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED');
Select inventory_asset_flag
From mtl_system_items
Where organization_id = v_dest_org_id
and inventory_item_id = v_apps_item_id;
Select whse_code
From ic_whse_mst
Where mtl_organization_id = v_dest_org_id;
Select asset_inventory
From mtl_secondary_inventories
Where organization_id = v_dest_org_id
and secondary_inventory_name = l_dest_sub_inv ;
if user has already allocated inv in order pad, here we would update this trans with
the new delivery_detail_id */
Procedure check_OPM_trans_for_so_line
( p_so_line_id IN NUMBER,
p_new_delivery_detail_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_count number := 0;
Select count(*)
INTO l_count
From ic_tran_pnd
Where line_id = p_so_line_id
and doc_type='OMSO'
and delete_mark=0
and completed_ind=0
and staged_ind=0
and line_detail_id is null
and (lot_id<>0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
Update ic_tran_pnd
Set line_detail_id = p_new_delivery_detail_id
Where line_id = p_so_line_id
and doc_type='OMSO'
and delete_mark=0
and completed_ind=0
and staged_ind=0
and line_detail_id is null
and (lot_id<>0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
/* call update reservations for gme */
GML_BATCH_OM_RES_PVT.check_gmeres_for_so_line
( p_so_line_id => p_so_line_id
, p_delivery_detail_id => p_new_delivery_detail_id
, x_return_status => x_return_status
) ;
Select staged_ind
Into l_staged_flag
From ic_tran_pnd
Where trans_id = p_reservation_id;
Select trans_id
From ic_tran_pnd
Where line_id = p_line_id
And doc_type = 'OMSO'
And delete_mark = 0
And completed_ind = 0
And staged_ind = 0
And (lot_id = 0
AND location = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
SELECT iim.item_id, iim.lot_ctl, iim.loct_ctl
FROM ic_item_mst iim,
mtl_system_items msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_mtl_organization_id
AND msi.segment1 = iim.item_no;
Select loct_ctl
Into l_whse_ctl
From ic_whse_mst
Where mtl_organization_id = p_mtl_organization_id;
SELECT trans_id, doc_id
FROM ic_tran_pnd
WHERE line_id = p_old_source_line_id
AND delete_mark = 0
AND doc_type = 'OMSO'
AND trans_qty <> 0
And (lot_id <> 0 -- only real trans
OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
ORDER BY trans_qty desc; /* the smaller qty is at the top, keep in mind it is neg */
SELECT abs(sum(trans_qty)),abs(sum(trans_qty2))
FROM ic_tran_pnd
WHERE line_id = p_old_source_line_id
AND delete_mark = 0
AND completed_ind = 0
AND staged_ind = 0
AND doc_type = 'OMSO'
AND trans_qty <> 0;
Select ic.item_id
, ic.lot_ctl
, ic.loct_ctl
From ic_item_mst ic
, mtl_system_items mtl
Where ic.item_no = mtl.segment1
and mtl.inventory_item_id = l_inventory_item_id
and mtl.organization_id = l_organization_id;
SELECT inventory_item_id, Ship_from_org_id
FROM oe_order_lines_all
WHERE line_id = p_old_source_line_id;
Select loct_ctl
Into l_whse_ctl
From ic_whse_mst
Where mtl_organization_id = l_organization_id;
update ic_tran_pnd
set trans_qty = -1 * l_qty_to_fulfil
, trans_qty2 = -1 * l_qty2_to_fulfil
Where trans_id = l_trans_id;
PROCEDURE update_opm_trxns(
p_trans_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
SELECT IC.trans_id
FROM IC_TRAN_PND IC
WHERE IC.trans_id = p_trans_id
AND IC.DOC_TYPE='OMSO'
AND IC.DELETE_MARK =0
AND IC.COMPLETED_IND =0
AND IC.STAGED_IND = 0
AND ( IC.LOT_ID <> 0 OR
IC.LOCATION <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
SELECT IC.trans_id
FROM IC_TRAN_PND IC
WHERE IC.trans_id = p_trans_id
AND IC.DOC_TYPE='OMSO'
AND IC.DELETE_MARK =0
AND IC.COMPLETED_IND =0
AND IC.STAGED_IND = 0
AND ( IC.LOT_ID = 0 OR
IC.LOCATION = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
gmi_reservation_util.println('Failed to fetch trans_id in update_opm_trxns for lot/location item= '||p_trans_id);
gmi_reservation_util.println('Failed to fetch trans_id in update_opm_trxns for non-lot/non location= '||p_trans_id);
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
(
p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_tran_rec => l_old_transaction_rec
,x_tran_row => l_old_transaction_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) Error return by UPDATE_PENDING_TO_COMPLETED,
return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED');
FND_MESSAGE.Set_Token('WHERE','update_opm_trxns');
END IF; -- of update
gmi_reservation_util.println('Failed to fetch opm trxn in update_opm_trxns for trans_id:'||p_trans_id);
gmi_reservation_util.println('Done upating ic_tran_pnd in update_opm_trxns.');
END update_opm_trxns;
SELECT IC.LOT_ID
FROM IC_TRAN_PND IC
WHERE IC.TRANS_ID = P_TRANS_ID
AND IC.DOC_TYPE='OMSO'
AND IC.DELETE_MARK =0
AND IC.COMPLETED_IND =0
AND IC.STAGED_IND = 0
AND IC.LOT_ID <> 0;
SELECT lot_no
FROM IC_LOTS_MST
WHERE item_id = opm_item_id
AND lot_no = p_lot_number ;
GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Error in Select='||SQLCODE||'.');
SELECT COUNT(1)
FROM IC_TRAN_PND IC
WHERE IC.LINE_ID = p_line_id
AND IC.line_detail_id=p_delivery_detail_id
AND IC.DOC_TYPE='OMSO'
AND IC.DELETE_MARK =0
AND IC.COMPLETED_IND =0
AND ( IC.LOT_ID <> 0 OR
IC.LOCATION <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
SELECT COUNT(1)
FROM IC_TRAN_PND IC
WHERE IC.trans_id = p_line_id
AND IC.line_detail_id=p_delivery_detail_id
AND IC.DOC_TYPE='OMSO'
AND IC.DELETE_MARK =0
AND IC.COMPLETED_IND =0
AND ( IC.LOT_ID = 0 OR
IC.LOCATION = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Error in Select='||SQLCODE||'.');
SELECT COUNT(1)
FROM IC_TRAN_PND IC
WHERE IC.line_detail_id=p_delivery_detail_id
AND IC.DOC_TYPE='OMSO'
AND IC.DELETE_MARK =0
AND IC.COMPLETED_IND =0
AND ( IC.LOT_ID <> 0 OR
IC.LOCATION <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
SELECT COUNT(1)
FROM IC_TRAN_PND IC
WHERE IC.line_detail_id=p_delivery_detail_id
AND IC.DOC_TYPE='OMSO'
AND IC.DELETE_MARK =0
AND IC.COMPLETED_IND =0
AND ( IC.LOT_ID = 0 OR
IC.LOCATION = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
GMI_reservation_Util.PrintLn('(opm_dbg) in Util q: Error in Select='||SQLCODE||'.');
SELECT ic.lot_id
FROM ic_lots_mst ic
WHERE ic.item_id = l_ic_item_mst_rec.item_id
AND ic.lot_no = p_lot_number
AND ic.sublot_no = p_sublot_number;
SELECT ic.lot_id
FROM ic_lots_mst ic
WHERE ic.item_id = l_ic_item_mst_rec.item_id
AND ic.lot_no = p_lot_number
AND ic.sublot_no IS NULL;
GMI_reservation_Util.PrintLn('(opm_dbg) in Util validate_opm_quantities: Error in Select='||SQLCODE||'.');