[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Modified signatures of procedures update_pending_allocations
and CHECK_ALLOC_QTY.
Added procedure VALIDATE_MOVEALLOC_FORMASSMOVE.
======================================================== */
/* ==================================================================
Procedure: update_pending_allocations
Description: This procedure is used for updating the pending
transactions in the ic_tran_pnd table which will
be drawing from the inventory when there is a
mass move immediate or move immediate.
Pending txn are those txns where the delete_mark
is 0 and completed_ind is 0 and the txn is not the
default txn. This procedure updates only those txns
where trans_qty is negative.
For lot controlled items default txn have a lot_id
0 and location as default location.
History Jalaj Srivastava Bug 2024229
1. we are concerned only with lot controlled items
2. we never update OM txns
3. if we cannot move allocations then we error out (done
from the form)
Jalaj Srivastava Bug 2519568
ic_summ_inv is now a view.
Removed all updates to ic_summ_inv from this procedure.
Jalaj Srivastava Bug 3282770
Modified signature to follow api standards.
Added proper error handling.
Reorganized the logic/code.
Allow OMSO allocations to be moved.
================================================================== */
/* Global variables */
G_PKG_NAME CONSTANT VARCHAR2(30):='GMIALLOC';
procedure update_pending_allocations
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,pdoc_id IN NUMBER
,pto_whse_code IN VARCHAR2
,pto_location IN VARCHAR2
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PENDING_ALLOCATIONS' ;
select whse_code, location,lot_id,item_id
from ic_tran_cmp
where doc_type = 'TRNI'
and doc_id = pdoc_id
and line_type = -1
and lot_id > 0;
select trans_id,item_id,lot_id,whse_code,location,qc_grade,trans_qty,
trans_qty2,doc_type,doc_id,line_id
from ic_tran_pnd
where item_id = pcur_get_trans_cmp.item_id
and lot_id = pcur_get_trans_cmp.lot_id
and whse_code = pcur_get_trans_cmp.whse_code
and location = pcur_get_trans_cmp.location
and doc_type NOT IN ('PROD','OPSO','OMSO')
and delete_mark = 0
and completed_ind = 0
and trans_qty < 0
UNION ALL
select trans_id,pnd.item_id,lot_id,whse_code,location,qc_grade,trans_qty,
trans_qty2,doc_type,doc_id,pnd.line_id
from ic_tran_pnd pnd , pm_matl_dtl matl
where pnd.item_id = pcur_get_trans_cmp.item_id
and pnd.lot_id = pcur_get_trans_cmp.lot_id
and pnd.whse_code = pcur_get_trans_cmp.whse_code
and pnd.location = pcur_get_trans_cmp.location
and pnd.doc_type = 'PROD'
and pnd.delete_mark = 0
and pnd.completed_ind = 0
and pnd.trans_qty < 0
and matl.batch_id = pnd.doc_id
and matl.line_id = pnd.line_id
and matl.phantom_id IS NULL
UNION ALL
select trans_id,pnd.item_id,lot_id,whse_code,location,qc_grade,trans_qty,
trans_qty2,doc_type,doc_id,pnd.line_id
from ic_tran_pnd pnd, op_ordr_dtl ordr, op_hold_cds hold
where pnd.item_id = pcur_get_trans_cmp.item_id
and pnd.lot_id = pcur_get_trans_cmp.lot_id
and pnd.whse_code = pcur_get_trans_cmp.whse_code
and pnd.location = pcur_get_trans_cmp.location
and pnd.doc_type = 'OPSO'
and pnd.delete_mark = 0
and pnd.completed_ind = 0
and pnd.trans_qty < 0
and ordr.line_id = pnd.line_id
and ordr.order_id = pnd.doc_id
and ordr.delete_mark = 0
and hold.holdreas_code = ordr.holdreas_code
and hold.invcommit_ind = 0
and hold.delete_mark = 0
and ( (not exists (select 1
from op_cust_itm
where cust_id = ordr.shipcust_id
and item_id = pcur_get_trans_cmp.item_id
and whse_restrictions = 1
and delete_mark = 0
)
)
OR (exists (select 1
from op_cust_itm
where cust_id = ordr.shipcust_id
and item_id = pcur_get_trans_cmp.item_id
and whse_restrictions = 1
and whse_code = pto_whse_code
and delete_mark = 0
)
)
)
UNION ALL
select trans_id,item_id,lot_id,whse_code,location,qc_grade,trans_qty,
trans_qty2,doc_type,doc_id,line_id
from ic_tran_pnd
where item_id = pcur_get_trans_cmp.item_id
and lot_id = pcur_get_trans_cmp.lot_id
and whse_code = pcur_get_trans_cmp.whse_code
and location = pcur_get_trans_cmp.location
and doc_type ='OMSO'
and delete_mark = 0
and completed_ind = 0
and trans_qty < 0
and staged_ind = 0
and whse_code = pto_whse_code;
UPDATE_OP_ORDR_DTL VARCHAR2(1);
SAVEPOINT update_pending_allocations;
select order_id
into orderid
from op_ordr_hdr
where order_id = Cur_get_trans_pnd_rec.doc_id
and delete_mark = 0
FOR UPDATE NOWAIT;
UPDATE_OP_ORDR_DTL := 'Y';
UPDATE_OP_ORDR_DTL := 'N';
OR (UPDATE_OP_ORDR_DTL = 'Y')
)
)
) THEN
UPDATE IC_TRAN_PND
SET WHSE_CODE = pto_whse_code,
LOCATION = pto_location,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_DATE = SYSDATE
WHERE trans_id = Cur_get_trans_pnd_rec.trans_id;
AND (UPDATE_OP_ORDR_DTL = 'Y')
) THEN
UPDATE OP_ORDR_DTL
SET FROM_WHSE = pto_whse_code,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_DATE = SYSDATE
WHERE line_id = Cur_get_trans_pnd_rec.line_id
and order_id = Cur_get_trans_pnd_rec.doc_id
and delete_mark = 0;
UPDATE IC_XFER_MST
SET FROM_WAREHOUSE = pto_whse_code,
FROM_LOCATION = pto_location,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_DATE = SYSDATE
WHERE transfer_id = Cur_get_trans_pnd_rec.doc_id;
ROLLBACK to update_pending_allocations;
ROLLBACK to update_pending_allocations;
ROLLBACK to update_pending_allocations;
END UPDATE_PENDING_ALLOCATIONS;
Pending txn are those txns where the delete_mark
is 0 and completed_ind is 0 and the txn is not the
default txn.
For lot controlled items default txn have a lot_id
0 and location as default location.
This procedure considers only actual onhand qty, move quantity
and the total allocations qty( where trans_qty <0). It
does not look at pending transactions which are going to
add to the inventory.
If move qty <= (onhand qty - total allocations qty) then
returns 0
no need to update allocations
If move qty > (onhand qty - total allocations qty) and
move qty >= total allocations qty
returns 1
allocations need to be updated
If move qty > (onhand qty - total allocations qty) and
move qty < total allocations qty
returns -1
allocations need to be but cannot be updated
History Jalaj Srivastava Bug 2024229
1. we are concerned only with lot controlled items
2. we never update OM txns
3. if we cannot move allocations then we error out (done
from the form)
Jalaj Srivastava Bug 3282770
Modified signature to follow api standards.
Added proper error handling.
Reorganized the logic/code.
If OMSO allocations exist then moving allcoations not allowed
if the move is to a different warehouse.
If any pick confirmed OMSO allocations exist then moving
allcoations not allowed
======================================================================== */
PROCEDURE CHECK_ALLOC_QTY
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,pfrom_whse_code IN VARCHAR2
,pfrom_location IN VARCHAR2
,plot_id IN NUMBER
,pitem_id IN NUMBER
,pmove_qty IN NUMBER
,pto_whse_code IN VARCHAR2
,x_move_allocations OUT NOCOPY VARCHAR2
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'CHECK_ALLOC_QTY' ;
select NVL(sum(nvl(loct_onhand,0)),0)
into onhandqty
from ic_loct_inv
where item_id = pitem_id
and lot_id = plot_id
and whse_code = pfrom_whse_code
and location = pfrom_location;
select nvl(abs(sum(nvl(trans_qty,0))),0)
into tempqty
from ic_tran_pnd
where item_id = pitem_id
and lot_id = plot_id
and whse_code = pfrom_whse_code
and location = pfrom_location
and doc_type NOT IN ('PROD','OPSO')
and delete_mark = 0
and completed_ind = 0
and trans_qty < 0;
select nvl(abs(sum(nvl(trans_qty,0))),0)
into tempqty
from ic_tran_pnd itp, pm_matl_dtl pmd
where itp.item_id = pitem_id
and itp.lot_id = plot_id
and itp.whse_code = pfrom_whse_code
and itp.location = pfrom_location
and itp.doc_type ='PROD'
and itp.delete_mark = 0
and itp.completed_ind = 0
and itp.trans_qty < 0
and pmd.batch_id = itp.doc_id
and pmd.line_id = itp.line_id
and pmd.phantom_id IS NULL;
select nvl(abs(sum(nvl(trans_qty,0))),0)
into tempqty
from ic_tran_pnd itp, op_ordr_dtl ood, op_hold_cds ohc
where itp.item_id = pitem_id
and itp.lot_id = plot_id
and itp.whse_code = pfrom_whse_code
and itp.location = pfrom_location
and itp.doc_type ='OPSO'
and itp.delete_mark = 0
and itp.completed_ind = 0
and itp.trans_qty < 0
and ood.line_id = itp.line_id
and ood.order_id = itp.doc_id
and ood.delete_mark = 0
and ohc.holdreas_code = ood.holdreas_code
and ohc.invcommit_ind = 0
and ohc.delete_mark = 0;
select count(1)
into OMSO_txn_count
from ic_tran_pnd pnd
where pnd.item_id = pitem_id
and pnd.lot_id = plot_id
and pnd.whse_code = pfrom_whse_code
and pnd.location = pfrom_location
and pnd.doc_type ='OMSO'
and pnd.delete_mark = 0
and pnd.completed_ind = 0
and pnd.trans_qty < 0;
select count(1)
into OMSO_pick_confirmed_txn_count
from ic_tran_pnd pnd
where pnd.item_id = pitem_id
and pnd.lot_id = plot_id
and pnd.whse_code = pfrom_whse_code
and pnd.location = pfrom_location
and pnd.doc_type ='OMSO'
and pnd.delete_mark = 0
and pnd.completed_ind = 0
and pnd.trans_qty < 0
and pnd.staged_ind = 1;
Allocations are those txns where the delete_mark
is 0 and completed_ind is 0 and the txn is not the
default txn.
For lot controlled items default txn have a lot_id
0 and location as default location.
This procedure considers only actual onhand qty, move quantity
and the total allocations qty( where trans_qty <0). It
does not look at pending transactions which are going to
add to the inventory.
If move qty <= (onhand qty - total allocations qty) then
returns 0
no need to update allocations
If move qty > (onhand qty - total allocations qty) and
move qty >= total allocations qty
returns 1
allocations need to be updated
If move qty > (onhand qty - total allocations qty) and
move qty < total allocations qty
returns -1
allocations need to be but cannot be updated
Jalaj Srivastava Bug 3282770
Added this procedure.
If OMSO allocations exist then moving allcoations not allowed
if the move is to a different warehouse.
If any pick confirmed OMSO allocations exist then moving
allcoations not allowed
======================================================================== */
PROCEDURE VALIDATE_MOVEALLOC_FORMASSMOVE
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,pfrom_whse_code IN VARCHAR2
,pto_whse_code IN VARCHAR2
,pjournal_id IN NUMBER
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_MOVEALLOC_FORMASSMOVE' ;
SELECT count(1)
INTO OMSO_txn_count
FROM ic_tran_pnd pnd, ic_adjs_jnl jnl
WHERE jnl.journal_id = pjournal_id
AND jnl.line_type = -1
AND jnl.lot_id > 0
AND pnd.item_id = jnl.item_id
AND pnd.lot_id = jnl.lot_id
AND pnd.whse_code = jnl.whse_code
AND pnd.location = jnl.location
AND pnd.doc_type = 'OMSO'
AND pnd.delete_mark = 0
AND pnd.completed_ind = 0
AND pnd.trans_qty < 0;
select count(1)
into OMSO_pick_confirmed_txn_count
FROM ic_tran_pnd pnd, ic_adjs_jnl jnl
WHERE jnl.journal_id = pjournal_id
AND jnl.line_type = -1
AND jnl.lot_id > 0
AND pnd.item_id = jnl.item_id
AND pnd.lot_id = jnl.lot_id
AND pnd.whse_code = jnl.whse_code
AND pnd.location = jnl.location
AND pnd.doc_type = 'OMSO'
AND pnd.delete_mark = 0
AND pnd.completed_ind = 0
AND pnd.trans_qty < 0
AND pnd.staged_ind = 1;