[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
# insert_row
# SYNOPSIS
# proc insert_row
# DESCRIPTION
# This particular procedure is used to insert the values into
# the ic_xfer_mst table.
#################################################################*/
PROCEDURE insert_row (p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_transfer_id IN NUMBER,
p_transfer_no IN VARCHAR2,
p_transfer_batch IN VARCHAR2,
p_orgn_code IN VARCHAR2,
p_transfer_status IN VARCHAR2,
p_item_id IN NUMBER,
p_lot_id IN NUMBER,
p_lot_status IN VARCHAR2,
p_release_reason_code IN VARCHAR2,
p_receive_reason_code IN VARCHAR2,
p_cancel_reason_code IN VARCHAR2,
p_from_warehouse IN VARCHAR2,
p_from_location IN VARCHAR2,
p_to_warehouse IN VARCHAR2,
p_to_location IN VARCHAR2,
p_release_quantity1 IN NUMBER,
p_release_quantity2 IN NUMBER,
p_release_uom1 IN VARCHAR2,
p_release_uom2 IN VARCHAR2,
p_receive_quantity1 IN NUMBER,
p_receive_quantity2 IN NUMBER,
p_scheduled_release_date IN DATE,
p_actual_release_date IN DATE,
p_scheduled_receive_date IN DATE,
p_actual_receive_date IN DATE,
p_cancel_date IN DATE,
p_delete_mark IN NUMBER,
p_received_by IN NUMBER,
p_released_by IN NUMBER,
p_canceled_by IN NUMBER,
p_text_code IN NUMBER,
p_comments IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_attribute21 IN VARCHAR2,
p_attribute22 IN VARCHAR2,
p_attribute23 IN VARCHAR2,
p_attribute24 IN VARCHAR2,
p_attribute25 IN VARCHAR2,
p_attribute26 IN VARCHAR2,
p_attribute27 IN VARCHAR2,
p_attribute28 IN VARCHAR2,
p_attribute29 IN VARCHAR2,
p_attribute30 IN VARCHAR2,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER,
x_rowid OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_oracle_error OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Local Variables */
l_return_status VARCHAR2(1) := 'S';
SAVEPOINT Insert_Row;
INSERT INTO ic_xfer_mst
(transfer_id,
transfer_no,
transfer_batch,
orgn_code,
transfer_status,
item_id,
lot_id,
lot_status,
release_reason_code,
receive_reason_code,
cancel_reason_code,
from_warehouse,
from_location,
to_warehouse,
to_location,
release_quantity1,
release_quantity2,
release_uom1,
release_uom2,
receive_quantity1,
receive_quantity2,
scheduled_release_date,
actual_release_date,
scheduled_receive_date,
actual_receive_date,
cancel_date,
delete_mark,
received_by,
released_by,
canceled_by,
text_code,
comments,
attribute_category ,
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 ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login)
VALUES
(p_transfer_id ,
p_transfer_no ,
p_transfer_batch ,
p_orgn_code ,
p_transfer_status ,
p_item_id ,
p_lot_id ,
p_lot_status ,
p_release_reason_code ,
p_receive_reason_code ,
p_cancel_reason_code ,
p_from_warehouse ,
p_from_location ,
p_to_warehouse ,
p_to_location ,
p_release_quantity1 ,
p_release_quantity2 ,
p_release_uom1,
p_release_uom2,
p_receive_quantity1,
p_receive_quantity2,
p_scheduled_release_date ,
p_actual_release_date ,
p_scheduled_receive_date ,
p_actual_receive_date ,
p_cancel_date ,
p_delete_mark ,
p_received_by ,
p_released_by ,
p_canceled_by ,
p_text_code ,
p_comments ,
p_attribute_category ,
p_attribute1 ,
p_attribute2 ,
p_attribute3 ,
p_attribute4 ,
p_attribute5 ,
p_attribute6 ,
p_attribute7 ,
p_attribute8 ,
p_attribute9 ,
p_attribute10 ,
p_attribute11 ,
p_attribute12 ,
p_attribute13 ,
p_attribute14 ,
p_attribute15 ,
p_attribute16 ,
p_attribute17 ,
p_attribute18 ,
p_attribute19 ,
p_attribute20 ,
p_attribute21 ,
p_attribute22 ,
p_attribute23 ,
p_attribute24 ,
p_attribute25 ,
p_attribute26 ,
p_attribute27 ,
p_attribute28 ,
p_attribute29 ,
p_attribute30 ,
p_created_by ,
p_creation_date ,
p_last_updated_by ,
p_last_update_date ,
p_last_update_login);
/* Now get the row id of the inserted record */
Check_Primary_Key
(p_orgn_code,
p_transfer_no,
'F',
l_rowid,
l_key_exists);
ROLLBACK TO SAVEPOINT Insert_Row;
ROLLBACK TO SAVEPOINT Insert_Row;
FND_MESSAGE.SET_NAME('GMA', 'SY_DUPKEYINSERT');
ROLLBACK TO SAVEPOINT Insert_Row;
FND_MESSAGE.SET_NAME('GMI', 'IC_NO_RECORD_INSERTED');
ROLLBACK TO SAVEPOINT Insert_Row;
END Insert_Row;
# update_row
# SYNOPSIS
# proc update_row
# DESCRIPTION
# This particular procedure is used to update the values into
# the ic_xfer_mst table.
#################################################################*/
PROCEDURE update_row (p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_transfer_id IN NUMBER,
p_transfer_no IN VARCHAR2,
p_transfer_batch IN VARCHAR2,
p_orgn_code IN VARCHAR2,
p_transfer_status IN VARCHAR2,
p_item_id IN NUMBER,
p_lot_id IN NUMBER,
p_lot_status IN VARCHAR2,
p_release_reason_code IN VARCHAR2,
p_receive_reason_code IN VARCHAR2,
p_cancel_reason_code IN VARCHAR2,
p_from_warehouse IN VARCHAR2,
p_from_location IN VARCHAR2,
p_to_warehouse IN VARCHAR2,
p_to_location IN VARCHAR2,
p_release_quantity1 IN NUMBER,
p_release_quantity2 IN NUMBER,
p_release_uom1 IN VARCHAR2,
p_release_uom2 IN VARCHAR2,
p_receive_quantity1 IN NUMBER,
p_receive_quantity2 IN NUMBER,
p_scheduled_release_date IN DATE,
p_actual_release_date IN DATE,
p_scheduled_receive_date IN DATE,
p_actual_receive_date IN DATE,
p_cancel_date IN DATE,
p_delete_mark IN NUMBER,
p_received_by IN NUMBER,
p_released_by IN NUMBER,
p_canceled_by IN NUMBER,
p_text_code IN NUMBER,
p_comments IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_attribute21 IN VARCHAR2,
p_attribute22 IN VARCHAR2,
p_attribute23 IN VARCHAR2,
p_attribute24 IN VARCHAR2,
p_attribute25 IN VARCHAR2,
p_attribute26 IN VARCHAR2,
p_attribute27 IN VARCHAR2,
p_attribute28 IN VARCHAR2,
p_attribute29 IN VARCHAR2,
p_attribute30 IN VARCHAR2,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_oracle_error OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Local Variables */
l_return_status VARCHAR2(1) := 'S';
SAVEPOINT Update_Row;
UPDATE ic_xfer_mst
SET transfer_status = p_transfer_status,
transfer_batch = p_transfer_batch,
item_id = p_item_id,
lot_id = p_lot_id,
lot_status = p_lot_status,
release_reason_code = p_release_reason_code,
receive_reason_code = p_receive_reason_code,
cancel_reason_code = p_cancel_reason_code,
from_warehouse = p_from_warehouse,
from_location = p_from_location,
to_warehouse = p_to_warehouse,
to_location = p_to_location,
release_quantity1 = p_release_quantity1,
release_quantity2 = p_release_quantity2,
release_uom1 = p_release_uom1,
release_uom2 = p_release_uom2,
receive_quantity1 = p_receive_quantity1,
receive_quantity2 = p_receive_quantity2,
scheduled_release_date = p_scheduled_release_date,
actual_release_date = p_actual_release_date,
scheduled_receive_date = p_scheduled_receive_date,
actual_receive_date = p_actual_receive_date,
cancel_date = p_cancel_date,
delete_mark = p_delete_mark,
received_by = p_received_by,
released_by = p_released_by,
canceled_by = p_canceled_by,
text_code = p_text_code,
comments = p_comments,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20,
attribute21 = p_attribute21,
attribute22 = p_attribute22,
attribute23 = p_attribute23,
attribute24 = p_attribute24,
attribute25 = p_attribute25,
attribute26 = p_attribute26,
attribute27 = p_attribute27,
attribute28 = p_attribute28,
attribute29 = p_attribute29,
attribute30 = p_attribute30,
created_by = p_created_by,
creation_date = p_creation_date,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date,
last_update_login = p_last_update_login
WHERE rowid = p_rowid;
ROLLBACK TO SAVEPOINT Update_Row;
ROLLBACK TO SAVEPOINT Update_Row;
ROLLBACK TO SAVEPOINT Update_Row;
END Update_Row;
p_last_update_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_oracle_error OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
SELECT last_update_date
FROM ic_xfer_mst
WHERE rowid = p_rowid
FOR UPDATE OF last_update_date NOWAIT;
IF LockTransferRcd.last_update_date <> p_last_update_date THEN
RAISE RECORD_CHANGED_ERROR;
SELECT orgn_code
FROM sy_orgn_mst
WHERE orgn_code = p_orgn_code
AND delete_mark = 0;
SELECT rowid
FROM ic_xfer_mst
WHERE transfer_no = p_transfer_no
AND orgn_code = p_orgn_code;