[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_sequence_number IN NUMBER,
p_property_id IN VARCHAR2,
p_label_code IN VARCHAR2,
p_number_value IN NUMBER,
p_alpha_value IN VARCHAR2,
p_date_value IN DATE,
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
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
SAVEPOINT Insert_Row;
INSERT INTO gr_inv_item_properties
(organization_id,
inventory_item_id,
sequence_number,
property_id,
label_code,
number_value,
alpha_value,
date_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(p_organization_id,
p_inventory_item_id,
p_sequence_number,
p_property_id,
p_label_code,
p_number_value,
p_alpha_value,
p_date_value,
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_organization_id,
p_inventory_item_id,
p_label_code,
p_property_id,
'F',
l_rowid,
l_key_exists);
ROLLBACK TO SAVEPOINT Insert_Row;
ROLLBACK TO SAVEPOINT Insert_Row;
ROLLBACK TO SAVEPOINT Insert_Row;
'GR_NO_RECORD_INSERTED');
ROLLBACK TO SAVEPOINT Insert_Row;
END Insert_Row;
PROCEDURE Update_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_sequence_number IN NUMBER,
p_property_id IN VARCHAR2,
p_label_code IN VARCHAR2,
p_number_value IN NUMBER,
p_alpha_value IN VARCHAR2,
p_date_value IN DATE,
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
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
SAVEPOINT Update_Row;
select count(*) into l_count from gr_inv_item_properties where rowid = p_rowid;
UPDATE gr_inv_item_properties
SET organization_id = p_organization_id,
inventory_item_id = p_inventory_item_id,
sequence_number = p_sequence_number,
property_id = p_property_id,
label_code = p_label_code,
number_value = p_number_value,
alpha_value = p_alpha_value,
date_value = p_date_value,
-- Bug 4510201 Start
--created_by = p_created_by,
--creation_date = p_creation_date,
-- Bug 4510201 End
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;
'GR_NO_RECORD_INSERTED');
ROLLBACK TO SAVEPOINT Update_Row;
END Update_Row;
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
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
SELECT *
FROM gr_inv_item_properties
WHERE rowid = p_rowid
FOR UPDATE NOWAIT;
PROCEDURE Delete_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_sequence_number IN NUMBER,
p_property_id IN VARCHAR2,
p_label_code IN VARCHAR2,
p_number_value IN NUMBER,
p_alpha_value IN VARCHAR2,
p_date_value IN DATE,
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
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
SAVEPOINT Delete_Row;
DELETE FROM gr_inv_item_properties
WHERE rowid = p_rowid;
ROLLBACK TO SAVEPOINT Delete_Row;
ROLLBACK TO SAVEPOINT Delete_Row;
ROLLBACK TO SAVEPOINT Delete_Row;
END Delete_Row;
PROCEDURE Delete_Rows
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_delete_option IN VARCHAR2,
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_label_code IN VARCHAR2,
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';
NULL_DELETE_OPTION_ERROR EXCEPTION;
SAVEPOINT Delete_Rows;
** p delete option has one of three values
** 'I' - Delete all rows for the specified item.
** 'L' - Delete all rows for the specified label.
** 'B' - Delete all rows using the item and label
** combination.
*/
IF p_delete_option = 'I' THEN
IF p_organization_id IS NULL AND p_inventory_item_id IS NULL THEN
l_msg_token := 'Organization ID' || ' Item Inventory Id';
RAISE Null_Delete_Option_Error;
DELETE FROM gr_inv_item_properties
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
ELSIF p_delete_option = 'L' THEN
IF p_label_code IS NULL THEN
l_msg_token := 'Label Code';
RAISE Null_Delete_Option_Error;
DELETE FROM gr_inv_item_properties
WHERE label_code = p_label_code;
ELSIF p_delete_option = 'B' THEN
IF (p_organization_id IS NULL AND p_inventory_item_id IS NULL) OR
(p_label_code IS NULL) THEN
l_msg_token := 'Organization ID, Item or Label Code';
RAISE Null_Delete_Option_Error;
DELETE FROM gr_inv_item_properties
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND label_code = p_label_code;
WHEN Null_Delete_Option_Error THEN
x_return_status := 'E';
ROLLBACK TO SAVEPOINT Delete_Rows;
END Delete_Rows;
SELECT organization_id, inventory_item_id
FROM mtl_system_items_b
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT ip.rowid
FROM gr_inv_item_properties ip
WHERE ip.organization_id = p_organization_id
AND ip.inventory_item_id = p_inventory_item_id
AND ip.label_code = p_label_code
AND ip.property_id = p_property_id;