[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| COMMENT : Creates,updates or deletes an opm reservation in ic_tran_pnd
| table table with information specified in p_tran_rec.
|
| Notes :
| -- The passed qties are positive
| -- if trans_id > 0, then the action code would be either update
| or delete.
|
| -- if trans_id = 0, then ureate a new transaction.
|
| -- line_id is mandatory in any case.
|
| -- Values for action_code are 'INSERT','UPDATE' and 'DELETE'
|
| -- Note that each UOM will be in passed in p_tran_rec as
| AppsUOM (3char). Need to be converted back to OPMUOM.
| -- Update is for quantities only.
|
****************************************************************************
| ======================================================================== */
PROCEDURE Allocate_OPM_Orders(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_tran_rec IN IC_TRAN_REC_TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 ) IS
-- Standard constants to be used to check for call compatibility.
l_api_version CONSTANT NUMBER := 1.0;
l_delete_mark NUMBER;
SELECT flow_status_code
,inventory_item_id
,ship_from_org_id
,schedule_ship_date
,order_quantity_uom
,ordered_quantity_uom2
,ordered_quantity
,ordered_quantity2
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT reason_code
FROM sy_reas_cds
WHERE reason_code = p_reas_code
AND delete_mark <> 1;
SELECT delivery_detail_id,move_order_line_id
FROM wsh_delivery_details
WHERE source_line_id = p_line_id;
SELECT source_line_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_line_detail_id;
SELECT *
FROM IC_WHSE_MST
WHERE mtl_organization_id = p_organization_id;
SELECT loct_onhand
,loct_onhand2
,lot_status
,delete_mark
FROM ic_loct_inv
WHERE item_id = p_item_id
AND lot_id = NVL(p_lot_id ,0)
AND whse_code = p_whse_code
AND location = NVL(p_location,l_default_location);
SELECT loct_onhand
,loct_onhand2
,lot_status
,delete_mark
FROM ic_loct_inv
WHERE item_id = p_item_id
AND lot_id = NVL(p_lot_id ,0)
AND whse_code = p_whse_code;
SELECT nettable_ind
,order_proc_ind
,rejected_ind
FROM ic_lots_sts
WHERE lot_status = p_lot_status;
SELECT delete_mark
FROM ic_loct_mst
WHERE location = p_location
AND whse_code = p_whse_code;
SELECT NVL(ABS(SUM(trans_qty)),0)
, NVL(ABS(SUM(trans_qty2)),0)
FROM IC_TRAN_PND a
WHERE a.lot_id = l_ic_lots_mst_rec.lot_id
AND a.item_id = l_ic_item_mst_rec.item_id
AND a.location = NVL(l_tran_rec.location ,l_default_location)
AND a.whse_code = l_ic_whse_mst_rec.whse_code
AND a.trans_id <> NVL(l_tran_rec.trans_id,0)
AND a.delete_mark = 0
AND a.completed_ind = 0
AND a.trans_qty < 0;
SELECT NVL(ABS(SUM(trans_qty)),0)
,NVL(ABS(SUM(trans_qty2)),0)
FROM IC_TRAN_PND
WHERE line_id = p_line_id
AND line_detail_id = p_line_detail_id
AND doc_type = 'OMSO'
AND delete_mark = 0;
SELECT NVL(ABS(SUM(trans_qty)),0)
,NVL(ABS(SUM(trans_qty2)),0)
FROM IC_TRAN_PND
WHERE line_id = p_line_id
AND doc_type = 'OMSO'
AND delete_mark = 0;
SELECT delete_mark,completed_ind,staged_ind
FROM ic_tran_pnd
WHERE trans_id = p_trans_id;
SELECT requested_quantity,requested_quantity2
FROM wsh_delivery_details
WHERE delivery_detail_id = p_line_detail_id;
SELECT move_order_line_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_line_detail_id;
IF( UPPER(NVL(l_tran_rec.action_code, 'N')) not in ('INSERT', 'UPDATE', 'DELETE'))
THEN
PrintMsg('ERROR - Validation failed on action_code Only ');
IF( UPPER(NVL(l_tran_rec.action_code,'N')) IN ('DELETE','UPDATE'))
THEN
IF( NVL(l_tran_rec.trans_id,0) = 0 )
THEN
PrintMsg('ERROR - Validation failed for trans_id');
PrintMsg('ERROR - Trans_id not required for INSERT');
IF( UPPER(NVL(l_tran_rec.action_code,'N')) IN ('DELETE','UPDATE') AND
NVL(l_tran_rec.trans_id,0) <> 0 )
THEN
OPEN validate_trans_id_cur(l_tran_rec.trans_id);
FETCH validate_trans_id_cur INTO l_delete_mark,l_completed_ind,l_staged_ind;
ELSIF (l_delete_mark = 1)
THEN
CLOSE validate_trans_id_cur;
PrintMsg('ERROR - Invalid Trans_id Transaction is already deleted : '||l_tran_rec.trans_id);
FND_MESSAGE.SET_NAME('GML','GMI_API_TRANS_DELETED');
END IF; /* action code 'DELETE' OR 'UPDATE' and trans_id <>0) */
SELECT count(*) into l_count
FROM wsh_delivery_details
WHERE source_line_id = l_tran_rec.line_id;
/* Is it Deleted? */
IF( l_ic_lots_mst_rec.delete_mark = 1)
THEN
PrintMsg(' Lot is Deleted , Lot_no '||l_tran_rec.lot_no||
'lot_id : '||l_tran_rec.lot_id);
FND_MESSAGE.Set_Name('GMI','GMI_API_DELETED_LOT');
/* if the action_code is Insert or Update and Item is dual controlled then
this should be > 0. If not supplied will be defaulted for dual1 and dual2. */
IF( UPPER(NVL(l_tran_rec.action_code, 'N')) in('INSERT', 'UPDATE')
AND l_ic_item_mst_rec.dualum_ind > 0 )
THEN
GMICUOM.icuomcv( pitem_id => l_ic_item_mst_rec.item_id,
plot_id => l_ic_lots_mst_rec.lot_id,
pcur_qty => l_tmp_qty,
pcur_uom => l_ic_item_mst_rec.item_um,
pnew_uom => l_ic_item_mst_rec.item_um2,
onew_qty => l_tmp_qty2);
/* For action code Insert or Update trans_qty must be > 0) */
IF (UPPER(NVL(l_tran_rec.action_code, 'N'))in ('INSERT', 'UPDATE'))
THEN
IF( NVL(l_tran_rec.trans_qty,0) <= 0 )
THEN
PrintMsg('ERROR - trans_qty Has to be > 0 For actions INSERT and Update ');
IF (l_tran_rec.action_code = 'DELETE')
THEN
l_pick_lots_rec.trans_qty := 0;
SELECT *
FROM ic_lots_mst
WHERE
lot_no = p_lot_no
AND ( sublot_no = p_sublot_no OR
sublot_no is NULL)
AND item_id = p_item_id;
SELECT *
FROM ic_lots_mst
WHERE
lot_id = p_lot_id
AND item_id = p_item_id;
SELECT ic.*
FROM ic_item_mst_b ic
, mtl_system_items mtl
WHERE delete_mark = 0
AND ic.item_no = mtl.segment1
AND mtl.organization_id = discrete_org_id
AND mtl.inventory_item_id = discrete_item_id;
SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value) location, USERENV('SESSIONID') sessionid
FROM v$parameter
WHERE name = 'utl_file_dir';