[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| - update_row
| - lock_row
|
|
=============================================================================
*/
/* Api start of comments
+============================================================================
| FUNCTION NAME
| insert_row
|
| TYPE
| Private
| USAGE
| Insert_Row will insert a row in gme_lab_batch_lots
|
|
| DESCRIPTION
| Insert_Row will insert a row in gme_lab_batch_lots
|
|
|
| PARAMETERS
| p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
| x_lab_batch_lots IN OUT NOCOPY gme_lab_batch_lots%ROWTYPE
|
| RETURNS
| BOOLEAN
| HISTORY
| 12-MAR-01 Thomas Daniel Created
|
|
|
+=============================================================================
Api end of comments
*/
FUNCTION insert_row (
p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
,x_lab_batch_lots IN OUT NOCOPY gme_lab_batch_lots%ROWTYPE)
RETURN BOOLEAN
IS
BEGIN
x_lab_batch_lots := p_lab_batch_lots;
INSERT INTO gme_lab_batch_lots
(batch_id
,material_detail_id
,item_id, lot_id
,qty, qty2
,uom, uom2
,creation_date, created_by
,last_update_date, last_updated_by
,last_update_login, attribute1
,attribute2, attribute3
,attribute4, attribute5
,attribute6, attribute7
,attribute8, attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute_category)
VALUES (x_lab_batch_lots.batch_id
,x_lab_batch_lots.material_detail_id
,x_lab_batch_lots.item_id, x_lab_batch_lots.lot_id
,x_lab_batch_lots.qty, x_lab_batch_lots.qty2
,x_lab_batch_lots.uom, x_lab_batch_lots.uom2
,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
,gme_common_pvt.g_login_id, x_lab_batch_lots.attribute1
,x_lab_batch_lots.attribute2, x_lab_batch_lots.attribute3
,x_lab_batch_lots.attribute4, x_lab_batch_lots.attribute5
,x_lab_batch_lots.attribute6, x_lab_batch_lots.attribute7
,x_lab_batch_lots.attribute8, x_lab_batch_lots.attribute9
,x_lab_batch_lots.attribute10
,x_lab_batch_lots.attribute11
,x_lab_batch_lots.attribute12
,x_lab_batch_lots.attribute13
,x_lab_batch_lots.attribute14
,x_lab_batch_lots.attribute15
,x_lab_batch_lots.attribute16
,x_lab_batch_lots.attribute17
,x_lab_batch_lots.attribute18
,x_lab_batch_lots.attribute19
,x_lab_batch_lots.attribute20
,x_lab_batch_lots.attribute21
,x_lab_batch_lots.attribute22
,x_lab_batch_lots.attribute23
,x_lab_batch_lots.attribute24
,x_lab_batch_lots.attribute25
,x_lab_batch_lots.attribute26
,x_lab_batch_lots.attribute27
,x_lab_batch_lots.attribute28
,x_lab_batch_lots.attribute29
,x_lab_batch_lots.attribute30
,x_lab_batch_lots.attribute_category);
END insert_row;
SELECT batch_id
,material_detail_id
,item_id, lot_id
,qty, qty2
,uom, uom2
,creation_date, created_by
,last_update_date
,last_updated_by
,last_update_login
,attribute1, attribute2
,attribute3, attribute4
,attribute5, attribute6
,attribute7, attribute8
,attribute9, attribute10
,attribute11, attribute12
,attribute13, attribute14
,attribute15, attribute16
,attribute17, attribute18
,attribute19, attribute20
,attribute21, attribute22
,attribute23, attribute24
,attribute25, attribute26
,attribute27, attribute28
,attribute29, attribute30
,attribute_category
INTO x_lab_batch_lots.batch_id
,x_lab_batch_lots.material_detail_id
,x_lab_batch_lots.item_id, x_lab_batch_lots.lot_id
,x_lab_batch_lots.qty, x_lab_batch_lots.qty2
,x_lab_batch_lots.uom, x_lab_batch_lots.uom2
,x_lab_batch_lots.creation_date, x_lab_batch_lots.created_by
,x_lab_batch_lots.last_update_date
,x_lab_batch_lots.last_updated_by
,x_lab_batch_lots.last_update_login
,x_lab_batch_lots.attribute1, x_lab_batch_lots.attribute2
,x_lab_batch_lots.attribute3, x_lab_batch_lots.attribute4
,x_lab_batch_lots.attribute5, x_lab_batch_lots.attribute6
,x_lab_batch_lots.attribute7, x_lab_batch_lots.attribute8
,x_lab_batch_lots.attribute9, x_lab_batch_lots.attribute10
,x_lab_batch_lots.attribute11, x_lab_batch_lots.attribute12
,x_lab_batch_lots.attribute13, x_lab_batch_lots.attribute14
,x_lab_batch_lots.attribute15, x_lab_batch_lots.attribute16
,x_lab_batch_lots.attribute17, x_lab_batch_lots.attribute18
,x_lab_batch_lots.attribute19, x_lab_batch_lots.attribute20
,x_lab_batch_lots.attribute21, x_lab_batch_lots.attribute22
,x_lab_batch_lots.attribute23, x_lab_batch_lots.attribute24
,x_lab_batch_lots.attribute25, x_lab_batch_lots.attribute26
,x_lab_batch_lots.attribute27, x_lab_batch_lots.attribute28
,x_lab_batch_lots.attribute29, x_lab_batch_lots.attribute30
,x_lab_batch_lots.attribute_category
FROM gme_lab_batch_lots
WHERE batch_id = p_lab_batch_lots.batch_id
AND material_detail_id = p_lab_batch_lots.material_detail_id
AND item_id = p_lab_batch_lots.item_id
AND lot_id = p_lab_batch_lots.lot_id;
| delete_row
|
| TYPE
| Private
| USAGE
| Delete_Row will delete a row in gme_lab_batch_lots
|
|
| DESCRIPTION
| Delete_Row will delete a row in gme_lab_batch_lots
|
|
|
| PARAMETERS
| p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
|
| RETURNS
| BOOLEAN
| HISTORY
| 12-MAR-01 Thomas Daniel Created
| 26-AUG-02 Bharati Satpute 2404126
| Added error message 'GME_RECORD_CHANGED'
|
|
+=============================================================================
Api end of comments
*/
FUNCTION delete_row (p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE)
RETURN BOOLEAN
IS
l_dummy NUMBER (5) := 0;
SELECT 1
INTO l_dummy
FROM gme_lab_batch_lots
WHERE batch_id = p_lab_batch_lots.batch_id
AND material_detail_id = p_lab_batch_lots.material_detail_id
AND item_id = p_lab_batch_lots.item_id
AND lot_id = p_lab_batch_lots.lot_id
FOR UPDATE NOWAIT;
DELETE FROM gme_lab_batch_lots
WHERE batch_id = p_lab_batch_lots.batch_id
AND material_detail_id = p_lab_batch_lots.material_detail_id
AND item_id = p_lab_batch_lots.item_id
AND lot_id = p_lab_batch_lots.lot_id;
END delete_row;
| update_row
|
| TYPE
| Private
| USAGE
| Update_Row will update a row in gme_lab_batch_lots
|
|
| DESCRIPTION
| Update_Row will update a row in gme_lab_batch_lots
|
|
|
| PARAMETERS
| p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
|
| RETURNS
| BOOLEAN
| HISTORY
| 12-MAR-01 Thomas Daniel Created
| 26-AUG-02 Bharati Satpute 2404126
| Added error message 'GME_RECORD_CHANGED'
|
+=============================================================================
Api end of comments
*/
FUNCTION update_row (p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE)
RETURN BOOLEAN
IS
l_dummy NUMBER := 0;
SELECT 1
INTO l_dummy
FROM gme_lab_batch_lots
WHERE batch_id = p_lab_batch_lots.batch_id
AND material_detail_id = p_lab_batch_lots.material_detail_id
AND item_id = p_lab_batch_lots.item_id
AND lot_id = p_lab_batch_lots.lot_id
FOR UPDATE NOWAIT;
UPDATE gme_lab_batch_lots
SET qty = p_lab_batch_lots.qty
,qty2 = p_lab_batch_lots.qty2
,uom = p_lab_batch_lots.uom
,uom2 = p_lab_batch_lots.uom2
,last_update_date = gme_common_pvt.g_timestamp
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_login = gme_common_pvt.g_login_id
,attribute1 = p_lab_batch_lots.attribute1
,attribute2 = p_lab_batch_lots.attribute2
,attribute3 = p_lab_batch_lots.attribute3
,attribute4 = p_lab_batch_lots.attribute4
,attribute5 = p_lab_batch_lots.attribute5
,attribute6 = p_lab_batch_lots.attribute6
,attribute7 = p_lab_batch_lots.attribute7
,attribute8 = p_lab_batch_lots.attribute8
,attribute9 = p_lab_batch_lots.attribute9
,attribute10 = p_lab_batch_lots.attribute10
,attribute11 = p_lab_batch_lots.attribute11
,attribute12 = p_lab_batch_lots.attribute12
,attribute13 = p_lab_batch_lots.attribute13
,attribute14 = p_lab_batch_lots.attribute14
,attribute15 = p_lab_batch_lots.attribute15
,attribute16 = p_lab_batch_lots.attribute16
,attribute17 = p_lab_batch_lots.attribute17
,attribute18 = p_lab_batch_lots.attribute18
,attribute19 = p_lab_batch_lots.attribute19
,attribute20 = p_lab_batch_lots.attribute20
,attribute21 = p_lab_batch_lots.attribute21
,attribute22 = p_lab_batch_lots.attribute22
,attribute23 = p_lab_batch_lots.attribute23
,attribute24 = p_lab_batch_lots.attribute24
,attribute25 = p_lab_batch_lots.attribute25
,attribute26 = p_lab_batch_lots.attribute26
,attribute27 = p_lab_batch_lots.attribute27
,attribute28 = p_lab_batch_lots.attribute28
,attribute29 = p_lab_batch_lots.attribute29
,attribute30 = p_lab_batch_lots.attribute30
,attribute_category = p_lab_batch_lots.attribute_category
WHERE batch_id = p_lab_batch_lots.batch_id
AND material_detail_id = p_lab_batch_lots.material_detail_id
AND item_id = p_lab_batch_lots.item_id
AND lot_id = p_lab_batch_lots.lot_id
AND last_update_date = p_lab_batch_lots.last_update_date;
END update_row;
SELECT 1
INTO l_dummy
FROM gme_lab_batch_lots
WHERE batch_id = p_lab_batch_lots.batch_id
AND material_detail_id = p_lab_batch_lots.material_detail_id
AND item_id = p_lab_batch_lots.item_id
AND lot_id = p_lab_batch_lots.lot_id
FOR UPDATE NOWAIT;
| delete_lab_lots
|
| TYPE
| Private
| USAGE
| Delete_Lab_Lots will delete all the lot allocations.
|
|
| DESCRIPTION
| Delete_Lab_Lots will delete all the lot allocations either for a
| batch or for a material detail or for a combination of material
| detail and lot id.
|
| PARAMETERS
| p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
| x_return_status IN OUT NOCOPY VARCHAR2
|
| HISTORY
| 13-NOV-01 Thomas Daniel Created
|
|
|
+=============================================================================
Api end of comments
*/
PROCEDURE delete_lab_lots (
p_lab_batch_lots IN gme_lab_batch_lots%ROWTYPE
,x_return_status IN OUT NOCOPY VARCHAR2)
IS
TYPE query_ref IS REF CURSOR;
FOR ' SELECT * FROM GME_LAB_BATCH_LOTS
WHERE material_detail_ID =:material_detail_id AND lot_id = :lot_id'
USING p_lab_batch_lots.material_detail_id, p_lab_batch_lots.lot_id;
FOR ' SELECT * FROM GME_LAB_BATCH_LOTS
WHERE material_detail_id =:material_detail_id'
USING p_lab_batch_lots.material_detail_id;
FOR ' SELECT * FROM GME_LAB_BATCH_LOTS
WHERE batch_id =:batch_id'
USING p_lab_batch_lots.batch_id;
IF NOT gme_lab_batch_lots_dbl.delete_row
(p_lab_batch_lots => l_lab_lots) THEN
RAISE fnd_api.g_exc_error;
,'DELETE_LAB_LOTS');
END delete_lab_lots;