[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT quantity,
quantity_detailed,
line_status
FROM IC_TXN_REQUEST_LINES
WHERE ship_set_id = p_ship_set_id
AND LINE_STATUS = 7;
SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
FROM ic_tran_pnd
WHERE line_id = p_line_id
AND ( lot_id > 0
OR location <> p_location )
-- AND item_id = p_item_id -- REMOVED for bug 3403418
AND doc_type = 'OMSO'
AND staged_ind = 0
AND completed_ind = 0
AND delete_mark = 0
AND line_detail_id in
(Select delivery_detail_id
From wsh_delivery_details
Where move_order_line_id = l_mo_line_rec.line_id
and released_status in ('R','S'));
SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
FROM ic_tran_pnd
WHERE line_id = p_line_id
-- AND item_id = p_item_id -- REMOVED for bug 3403418
AND doc_type = 'OMSO'
AND staged_ind = 0
AND completed_ind = 0
AND delete_mark = 0;
SELECT count(*)
FROM ic_tran_pnd
WHERE line_id = p_line_id
-- AND item_id = p_item_id -- REMOVED for bug 3403418
AND doc_type = 'OMSO'
AND staged_ind = 0
AND completed_ind = 0
AND delete_mark = 0;
SELECT tran.trans_id,
ABS(tran.trans_qty) trans_qty,
ABS(tran.trans_qty2) trans_qty2,
tran.qc_grade,
tran.location,
lots.lot_no,
lots.lot_id,
lots.sublot_no,
loct.INVENTORY_LOCATION_ID locator_id
FROM IC_TRAN_PND tran,
IC_LOTS_MST lots,
IC_LOCT_MST loct
WHERE lots.lot_id = tran.lot_id
AND lots.item_id = tran.item_id
AND lots.delete_mark = 0
AND tran.line_id = p_line_id
AND ( tran.lot_id > 0
OR tran.location <> p_location )
-- AND tran.item_id = p_item_id -- REMOVED for bug 3403418
AND tran.doc_type = 'OMSO'
AND tran.staged_ind = 0
AND tran.completed_ind = 0
AND tran.delete_mark = 0
AND loct.delete_mark(+) = 0
AND loct.whse_code (+) = tran.whse_code
AND loct.location (+) = tran.location
AND tran.line_detail_id = l_delivery_detail_id;
SELECT whse_code,loct_ctl
FROM ic_whse_mst
WHERE mtl_organization_id = l_organization_id;
SELECT delivery_detail_id
, source_header_id
, source_line_id
, requested_quantity
, requested_quantity2
FROM wsh_delivery_details
WHERE move_order_line_id = l_mo_line_rec.line_id
AND move_order_line_id IS NOT NULL
AND released_status = 'S';
Select loct_onhand
,nvl(loct_onhand2,0)
From ic_loct_inv inv
Where inv.item_id = p_item_id
AND inv.whse_code = p_whse_code
AND inv.lot_id = p_lot_id
AND inv.location = p_location;
Select nvl(sum(trans_qty),0)
,nvl(sum(nvl(trans_qty2,0)),0)
From ic_tran_pnd pnd
Where pnd.completed_ind =0
AND pnd.delete_mark = 0
AND pnd.staged_ind = 1
AND pnd.doc_type= 'OMSO'
AND pnd.item_id = p_item_id
AND pnd.whse_code = p_whse_code
AND pnd.lot_id = p_lot_id
AND pnd.location = p_location;
SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
FROM ic_tran_pnd
WHERE line_id = l_mo_line_rec.txn_source_line_id
AND line_detail_id = l_delivery_detail_id
AND delete_mark = 0;
Select count(*)
From wsh_delivery_details
Where move_order_line_id = l_mo_line_rec.line_id
And source_line_id = l_mo_line_rec.txn_source_line_id
And released_status = 'S';
Select status_ctl
From ic_item_mst
Where item_id=p_item_id;
SELECT SUM(ABS(nvl(trans_qty,0))),SUM(ABS(nvl(trans_qty2,0)))
FROM ic_tran_pnd trans
, ic_loct_inv lots
, ic_lots_sts sts
WHERE trans.line_id = p_line_id
AND trans.lot_id > 0
-- AND trans.item_id = p_item_id -- REMOVED for bug 3403418
AND trans.doc_type = 'OMSO'
AND trans.staged_ind = 0
AND trans.completed_ind = 0
AND trans.delete_mark = 0
AND lots.item_id = trans.item_id
AND lots.whse_code = trans.whse_code
AND lots.lot_id = trans.lot_id
AND lots.location = trans.location
AND lots.lot_status = sts.lot_status (+)
AND NVL(sts.shipping_ind,1) = 1
-- AND NVL(sts.order_proc_ind,1)=1 PK Bug 3470116
AND NVL(sts.rejected_ind,0) = 0
AND trans.line_detail_id in
(Select delivery_detail_id
From wsh_delivery_details
Where move_order_line_id = p_mo_line_id
and released_status in ('R','S'))
;
Select order_proc_ind
, shipping_ind
From ic_lots_sts sts
, ic_loct_inv inv
Where inv.item_id = p_item_id
AND inv.whse_code = p_whse_code
AND inv.lot_id = p_lot_id
AND inv.location = p_location
AND inv.lot_status = sts.lot_status
;
SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
FROM ic_tran_pnd tran
, ic_loct_inv inv -- get status
, ic_lots_sts sts -- status check
WHERE tran.line_id = l_mo_line_rec.txn_source_line_id
AND tran.line_detail_id = l_delivery_detail_id
AND tran.delete_mark = 0
AND inv.item_id = tran.item_id
AND inv.whse_code = tran.whse_code
AND inv.lot_id = tran.lot_id
AND inv.location = tran.location
AND inv.lot_status = sts.lot_status (+)
AND NVL(sts.shipping_ind,1) = 1
-- AND NVL(sts.order_proc_ind,1)=1 PK Bug 3470116
AND NVL(sts.rejected_ind,0) = 0
;
SELECT trans_id
, trans_qty
, trans_qty2
FROM ic_tran_pnd
WHERE line_id = p_line_id
-- AND item_id = p_item_id -- REMOVED for bug 3403418
AND doc_type = 'OMSO'
AND staged_ind = 1
AND completed_ind = 0
AND delete_mark = 0
AND line_detail_id = p_line_detail_id;
SELECT object_name
FROM user_objects
WHERE object_name = 'WSH_USA_INV_PVT'
AND object_type = 'PACKAGE BODY';
, user_id => l_mo_line_rec.last_updated_by
);
/* Update Move Order Line record */
GMI_Reservation_Util.PrintLn('Now, update the MO row');
GMI_MOVE_ORDER_LINE_UTIL.Update_ROW(p_mo_line_rec => l_mo_line_rec);
/*SELECT a.delivery_detail_id, a.oe_header_id, a.oe_line_id
, b.order_quantity_uom
INTO l_delivery_detail_id
, l_source_header_id
, l_source_line_id
, l_order_quantity_uom
FROM oe_order_lines_all b
, wsh_inv_delivery_details_v a
WHERE a.move_order_line_id = l_mo_line_rec.line_id
AND a.move_order_line_id IS NOT NULL
AND a.oe_line_id = b.line_id
AND a.released_status = 'S';*/
GMI_Reservation_Util.PrintLn('Before Calling the Update_Shipping_Attributes trans_id='||l_mo_line_txn_rec.trans_id);
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
GMI_Reservation_Util.PrintLn('Return Status from [1] Update_Shipping_Attributes Call '||l_return_status);
/* Now Update staged_ind in transaction record from 0 to 1 */
/* HAM Will Have to be More Selective */
/* On Update i.e lot_id, qc_grade, locator_id, */
/* NC added delivery_detail_id and staged_ind in the where clause */
/* -- BUG#1675561*/
UPDATE ic_tran_pnd -- NOT NEEDED
SET staged_ind =1
--line_detail_id = l_shipping_attr(1).delivery_detail_id
WHERE trans_id = l_mo_line_txn_rec.trans_id AND
staged_ind <> 1 and delete_mark <> 1;
UPDATE ic_tran_pnd
SET pick_slip_number = l_pick_slip_number
WHERE trans_id = l_mo_line_txn_rec.trans_id;
GMI_MOVE_ORDER_LINE_UTIL.Update_ROW(p_mo_line_rec => l_mo_line_rec);
GMI_MOVE_ORDER_LINE_UTIL.Update_ROW(p_mo_line_rec => l_mo_line_rec);
GMI_Reservation_Util.PrintLn('backordering Update_Shipping_Attributes delivery_detail_id= '
||l_delivery_detail_id);
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
GMI_Reservation_Util.PrintLn('Return Status from [2] Update_Shipping_Attributes Call '||l_return_status);
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
GMI_Reservation_Util.PrintLn('Return Status from [3] Update_Shipping_Attributes Call '||l_return_status);
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
GMI_Reservation_Util.PrintLn('Return Status from [4] Update_Shipping_Attributes Call '||l_return_status);
UPDATE ic_tran_pnd
SET pick_slip_number = NULL
WHERE trans_id = d_trans_id;
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
GMI_Reservation_Util.PrintLn('Return Status from [5] Update_Shipping_Attributes Call '||l_return_status);
Select count(*)
INTO l_count
From wsh_delivery_details
Where move_order_line_id = l_mo_line_rec.line_id
And source_line_id = l_mo_line_rec.txn_source_line_id
And released_status = 'S';
GMI_Reservation_Util.PrintLn('Now, update the MO row');
GMI_MOVE_ORDER_LINE_UTIL.Update_ROW(p_mo_line_rec => l_mo_line_rec);
SELECT txn_source_line_id
FROM ic_txn_request_lines
WHERE line_id = p_line_id;
SELECT source_line_id
FROM wsh_delivery_details
WHERE move_order_line_id = p_line_id;
SELECT inventory_item_id,
organization_id,
requested_quantity_uom,
requested_quantity_uom2,
ship_tolerance_above
FROM wsh_delivery_details
WHERE source_line_id = x_source_line_id
AND source_code = 'OE'
AND container_flag = 'N'
AND rownum = 1;
SELECT NVL(SUM(requested_quantity), 0) net_requested_qty,
NVL(SUM(NVL(picked_quantity, requested_quantity)), 0) net_staged_qty,
NVL(SUM(NVL(requested_quantity2,0)), 0) net_requested_qty2,
NVL(SUM(NVL(picked_quantity2, requested_quantity2)), 0) net_staged_qty2
FROM wsh_delivery_details
WHERE source_line_id = x_source_line_id
AND source_code = 'OE'
AND container_flag = 'N'
AND released_status IN ('X', 'Y', 'C');
SELECT WSH_WV_UTILS.CONVERT_UOM(order_quantity_uom,
x_primary_uom,
ordered_quantity,
x_item_id) quantity ,
order_quantity_uom
FROM oe_order_lines_all
WHERE line_id = x_source_line_id;
SELECT ordered_quantity,
order_quantity_uom,
ordered_quantity2,
ordered_quantity_uom2
FROM oe_order_lines_all
WHERE line_id = x_source_line_id;
SELECT ship_tolerance_below,
ship_tolerance_above,
line_set_id,
ordered_quantity,
shipped_quantity,
ordered_quantity2,
shipped_quantity2,
top_model_line_id,
ato_line_id
INTO l_ship_tolerance_below,
l_ship_tolerance_above,
l_line_set_id,
l_ordered_quantity,
l_shipped_quantity,
l_ordered_quantity2,
l_shipped_quantity2,
l_top_model_line_id,
l_ato_line_id
FROM OE_ORDER_LINES_ALL
WHERE line_id = p_line_id;
SELECT line_set_id
INTO l_line_set_id
FROM OE_ORDER_LINES_ALL
WHERE line_id = l_top_model_line_id;
SELECT SUM(ordered_quantity)
, SUM(shipped_quantity)
, SUM(shipping_quantity)
, SUM(nvl(ordered_quantity2,0))
, SUM(nvl(shipped_quantity2,0))
, SUM(nvl(shipping_quantity2,0))
INTO l_ordered_quantity
, l_shipped_quantity
, l_shipping_quantity
, l_ordered_quantity2
, l_shipped_quantity2
, l_shipping_quantity2
FROM oe_order_lines_all
WHERE line_set_id = l_line_set_id;
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => p_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_dflt_nonctl_tran_rec
,x_tran_row => x_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 loct_onhand , loct_onhand2
FROM ic_loct_inv
WHERE whse_code = p_whse_code
AND item_id = p_item_id
AND lot_id = 0
AND location = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT
AND delete_mark = 0;
SELECT NVL(sum(trans_qty),0), NVL(sum(trans_qty2),0)
FROM ic_tran_pnd
WHERE item_id = p_item_id
AND whse_code = p_whse_code
AND doc_type = 'OMSO'
AND staged_ind = 1
AND delete_mark = 0
AND completed_ind = 0;
SELECT sum(requested_quantity), sum(requested_quantity2)
FROM wsh_delivery_details
WHERE released_status = 'B'
AND source_line_id = p_mo_line_rec.txn_source_line_id;
Select noninv_ind
from ic_item_mst
where item_id = p_item_id;
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => p_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_dflt_nonctl_tran_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);
UPDATE ic_tran_pnd
SET pick_slip_number = l_pick_slip_number
WHERE trans_id = l_tran_row.trans_id;
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => p_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_dflt_nonctl_tran_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_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => p_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_dflt_nonctl_tran_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');
GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => p_commit
,p_validation_level => l_validation_level
,p_tran_rec => l_dflt_nonctl_tran_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');
UPDATE ic_tran_pnd
SET pick_slip_number = l_pick_slip_number
WHERE trans_id = l_tran_row.trans_id;
SELECT trans_id, trans_qty, trans_qty2
FROM IC_TRAN_PND tran,
IC_LOTS_MST lots,
IC_LOCT_MST loct
WHERE lots.lot_id = tran.lot_id
AND lots.item_id = tran.item_id
AND lots.delete_mark = 0
AND tran.line_id = p_line_id
AND (tran.lot_id > 0 OR tran.location <> p_default_location )
AND tran.doc_type = 'OMSO'
AND tran.staged_ind = 0
AND tran.completed_ind = 0
AND tran.delete_mark = 0
AND loct.delete_mark(+) = 0
AND loct.whse_code (+) = tran.whse_code
AND loct.location (+) = tran.location
AND tran.line_detail_id = p_delivery_detail_id
FOR UPDATE OF trans_qty, trans_qty2 NOWAIT;
update ic_tran_pnd
set trans_qty = trunc (trans_qty, l_TRUNCATE_TO_LENGTH),
trans_qty2= trunc (trans_qty2, l_TRUNCATE_TO_LENGTH)
where current of lot_loct_ctl_trans;