[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Procedure relieve_pending_lots modified to delete the pending lots if transacting qty >= */
/* pending lot qty */
/* Namit Singhi Bug#5689035. Added procedure get_pnd_prod_lot_qty */
/*************************************************************************************************/
PROCEDURE get_pending_lot
(p_material_detail_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_pending_product_lot_tbl OUT NOCOPY gme_common_pvt.pending_lots_tab) IS
CURSOR cur_get_lots (v_mtl_dtl_id NUMBER) IS
SELECT *
FROM gme_pending_product_lots
WHERE material_detail_id = v_mtl_dtl_id
ORDER BY sequence asc, lot_number asc;
SELECT quantity, secondary_quantity
FROM gme_pending_product_lots
WHERE pending_product_lot_id = v_pending_lot_id;
error_delete_row EXCEPTION;
/* Bug#5186388 if transacting qty is greater than pending lot qty then delete the lot
rather than updating to zero */
l_pending_product_lots_rec.pending_product_lot_id := p_pending_lot_id;
delete_pending_product_lot( p_pending_product_lots_rec => l_pending_product_lots_rec
,x_return_status => l_return_status
);
RAISE error_delete_row;
/*UPDATE gme_pending_product_lots
SET quantity = 0,
last_updated_by = gme_common_pvt.g_user_ident,
last_update_date = gme_common_pvt.g_timestamp,
last_update_login = gme_common_pvt.g_login_id
WHERE pending_product_lot_id = p_pending_lot_id;
UPDATE gme_pending_product_lots
SET secondary_quantity = 0
WHERE pending_product_lot_id = p_pending_lot_id;
UPDATE gme_pending_product_lots
SET quantity = quantity - p_quantity,
last_updated_by = gme_common_pvt.g_user_ident,
last_update_date = gme_common_pvt.g_timestamp,
last_update_login = gme_common_pvt.g_login_id
WHERE pending_product_lot_id = p_pending_lot_id;
UPDATE gme_pending_product_lots
SET secondary_quantity = secondary_quantity - p_secondary_quantity
WHERE pending_product_lot_id = p_pending_lot_id;
WHEN ERROR_DELETE_ROW THEN
x_return_status := l_return_status;
SELECT msi.shelf_life_code, msi.shelf_life_days
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id;
error_insert_row EXCEPTION;
IF NOT gme_pending_product_lots_dbl.insert_row
(p_pending_product_lots_rec => p_pending_product_lots_rec
,x_pending_product_lots_rec => l_pp_lot_rec) THEN
RAISE error_insert_row;
WHEN error_insert_row THEN
gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
PROCEDURE update_pending_product_lot
(p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2) IS
error_update_row EXCEPTION;
l_api_name CONSTANT VARCHAR2 (30) := 'update_pending_product_lot';
IF NOT gme_pending_product_lots_dbl.update_row
(p_pending_product_lots_rec => p_pending_product_lots_rec) THEN
RAISE error_update_row;
WHEN error_update_row OR error_fetch_row THEN
-- error message set in fetch routine
x_return_status := fnd_api.g_ret_sts_unexp_error;
END update_pending_product_lot;
PROCEDURE delete_pending_product_lot
(p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2) IS
error_delete_row EXCEPTION;
l_api_name CONSTANT VARCHAR2 (30) := 'delete_pending_product_lot';
IF NOT gme_pending_product_lots_dbl.delete_row
(p_pending_product_lots_rec => p_pending_product_lots_rec) THEN
RAISE error_delete_row;
WHEN error_delete_row THEN
gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
END delete_pending_product_lot;
PROCEDURE delete_pending_product_lot
(p_material_detail_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR c_get_pending_lots IS
SELECT pending_product_lot_id
FROM gme_pending_product_lots
WHERE material_detail_id = p_material_detail_id;
l_api_name CONSTANT VARCHAR2 (30) := 'delete_pending_product_lot';
error_delete_row EXCEPTION;
IF NOT gme_pending_product_lots_dbl.delete_row
(p_pending_product_lots_rec => l_pending_product_lots_rec) THEN
CLOSE c_get_pending_lots;
RAISE error_delete_row;
WHEN error_delete_row THEN
gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
END delete_pending_product_lot;
IF p_batch_header_rec.update_inventory_ind = 'N' THEN
IF p_material_detail_rec.line_type <> gme_common_pvt.g_line_type_ing THEN
gme_common_pvt.log_message('GME_NO_LOT_CREATE');
SELECT max(sequence)
FROM gme_pending_product_lots
WHERE material_detail_id = v_dtl_id;
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
*/
IF g_debug <= gme_debug.g_log_procedure THEN
gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
PROCEDURE validate_material_for_update
(p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_material_detail_rec IN gme_material_details%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'validate_material_for_update';
END validate_material_for_update;
PROCEDURE validate_record_for_update
(p_material_detail_rec IN gme_material_details%ROWTYPE
,p_db_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'validate_record_for_update';
x_pending_product_lots_rec.last_update_date := l_db_pending_product_lots_rec.last_update_date;
x_pending_product_lots_rec.last_update_login := l_db_pending_product_lots_rec.last_update_login;
x_pending_product_lots_rec.last_updated_by := l_db_pending_product_lots_rec.last_updated_by;
END validate_record_for_update;
PROCEDURE validate_material_for_delete
(p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_material_detail_rec IN gme_material_details%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'validate_material_for_delete';
END validate_material_for_delete;
PROCEDURE validate_record_for_delete
(p_material_detail_rec IN gme_material_details%ROWTYPE
,p_db_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
,p_pending_product_lots_rec IN gme_pending_product_lots%ROWTYPE
,x_pending_product_lots_rec OUT NOCOPY gme_pending_product_lots%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'validate_record_for_delete';
END validate_record_for_delete;
SELECT primary_uom_code, secondary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = v_item_id
AND organization_id = v_org_id;
SELECT count( 1 )
FROM mtl_lot_numbers
WHERE inventory_item_id = v_item_id
AND organization_id = v_org_id
AND lot_number = v_lot_no;
SELECT 1
FROM gme_pending_product_lots
WHERE material_detail_id = v_matl_dtl_id
AND sequence = v_sequ;
SELECT count(1)
FROM mtl_transaction_reasons
WHERE reason_id = v_reason_id
AND NVL (disable_date, SYSDATE + 1) > SYSDATE;
SELECT 1
FROM gme_pending_product_lots
WHERE batch_id = v_batch_id
AND material_detail_id = v_matl_dtl_id
AND quantity <> 0
AND rownum = 1;