The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Contents: INSERT_OPERATION *
* UPDATE_OPERATION *
* DELETE_OPERATION *
* *
* Use This is the public layer of the GMD Operation API *
* *
* *
* History *
* Written by Sandra Dulyk, OPM Development *
* 25-NOV-2002 Thomas Daniel Bug# 2679110 *
* Rewrote the procedures to handle the *
* errors properly and also to handle *
* further validations *
* 21-OCT-2003 Shyam S Commented section in update_operation *
* procedure that check if p_value is *
* passes or not *
* 20-FEB-2004 NSRIVAST Bug# 3222090,Removed call to *
* FND_PROFILE.VALUE('AFLOG_ENABLED') *
* Shyam S 09-14-04 Added validations in insert, update and delete *
* that chceks for user access to owner orgn code *
* QZENG 10-DEC-2012 Bug 15967808 Remove trunc for not null *
* effective_start_date and effective_end_date in *
* insert_operation *
***********************************************************************************
*/
--Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
--Forward declaration.
FUNCTION set_debug_flag RETURN VARCHAR2;
insert_operation
Description
This particular procedure is used to insert an operation
Parameters
WHO WHEN WHAT
kkillams 10-MAR-2004 New p_oprn_rsrc_tbl input paramter is added to proceudre to pass the
resource details for activities. Added validation to check if default
status is 400/700 for the context organization then activity should have the
resources w.r.t. bug# 3408799
QZENG 10-DEC-2012 Bug 15967808 Remove trunc for not null effective_start_date and effective_end_date
==================================================================================================== */
PROCEDURE insert_operation (
p_api_version IN NUMBER
,p_init_msg_list IN BOOLEAN
,p_commit IN BOOLEAN
,p_operations IN OUT NOCOPY gmd_operations%ROWTYPE
,p_oprn_actv_tbl IN OUT NOCOPY gmd_operations_pub.gmd_oprn_activities_tbl_type
,x_message_count OUT NOCOPY NUMBER
,x_message_list OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,p_oprn_rsrc_tbl IN gmd_operation_resources_pub.gmd_oprn_resources_tbl_type) IS
v_oprn_id gmd_operations.oprn_id%TYPE;
SELECT GEM5_OPRN_ID_S.NEXTVAL
FROM FND_DUAL;
SAVEPOINT insert_oprn;
,'insert_operation'
,'gmd_operations_pub') THEN
RAISE invalid_version;
/* insert operation */
GMD_OPERATIONS_PVT.insert_operation(p_operations => p_operations,
x_message_count => x_message_count,
x_message_list => x_message_list,
x_return_status => l_retn_status);
/* validate oprn activity info and insert oprn activity */
FOR i in 1.. p_oprn_actv_tbl.count LOOP
p_oprn_actv_tbl(i).oprn_id := p_operations.oprn_id;
GMD_OPERATION_ACTIVITIES_PUB.insert_operation_activity(p_init_msg_list => FALSE,
p_oprn_activity => p_oprn_actv_tbl(i),
p_oprn_rsrc_tbl => my_rsrc_table_type,
x_message_count => x_message_count,
x_message_list => x_message_list,
x_return_status => l_retn_status);
my_rsrc_table_type.delete;
ROLLBACK TO SAVEPOINT insert_oprn;
ROLLBACK TO SAVEPOINT insert_oprn;
ROLLBACK TO SAVEPOINT insert_oprn;
END Insert_Operation;
update_operation
Description
This particular procedure is used to update an operation
Parameters
================================================ */
PROCEDURE update_operation
( p_api_version IN NUMBER
, p_init_msg_list IN BOOLEAN
, p_commit IN BOOLEAN
, p_oprn_id IN gmd_operations.oprn_id%TYPE
, p_oprn_no IN gmd_operations.oprn_no%TYPE
, p_oprn_vers IN gmd_operations.oprn_vers%TYPE
, p_update_table IN gmd_operations_pub.update_tbl_type
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2) IS
v_oprn_id gmd_operations.oprn_id%TYPE;
SELECT oprn_id
FROM gmd_operations
where oprn_no = v_oprn_no
and oprn_vers = v_oprn_vers;
SELECT oprn_id
FROM gmd_operations
where oprn_id = v_oprn_id;
SELECT OWNER_ORGANIZATION_ID
FROM gmd_operations
where oprn_id = v_oprn_id;
SAVEPOINT update_oprn;
,'update_operation'
,'gmd_operations_pub') THEN
RAISE invalid_version;
gmd_debug.put_line('Start of update_operation PUB');
/* Loop thru cols to be updated - verify col and value are present */
FOR i in 1 .. p_update_table.count LOOP
/* Col_to_update and value must be passed, otherwise give error */
IF p_update_table(i).p_col_to_update IS NULL THEN
IF (l_debug = 'Y') THEN
gmd_debug.put_line('col_to_update required');
FND_MESSAGE.SET_TOKEN ('MISSING', 'COL_TO_UPDATE');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'PROCESS_QTY_UOM' THEN
IF (NOT(gmd_api_grp.validate_um(p_update_table(i).p_value))) THEN
IF (l_debug = 'Y') THEN
gmd_debug.put_line('process qty uom invalid');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'MINIMUM_TRANSFER_QTY' THEN
IF p_update_table(i).p_value < 0 THEN
gmd_api_grp.log_message ('GMD_NEGATIVE_FIELDS',
'FIELD', 'MINIMUM_TRANSFER_QTY');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'OPRN_CLASS' THEN
IF gmdopval_pub.check_oprn_class(p_update_table(i).p_value, 'F') <> 0 THEN
gmd_api_grp.log_message('FM_INV_OPRN_CLASS');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'OPRN_DESC' THEN
IF p_update_table(i).p_value IS NULL THEN
FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'DELETE_MARK' THEN
GMDRTVAL_PUB.check_delete_mark ( Pdelete_mark => p_update_table(i).p_value,
x_return_status => l_retn_status);
FND_MESSAGE.SET_NAME('GMA', 'SY_BADDELETEMARK');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'OWNER_ORGANIZATION_ID' THEN
/* Validation : Check if owner_orgn_idis valid */
l_owner_orgn_id := p_update_table(i).p_value;
and Operation is not logically deleted */
IF v_oprn_id IS NOT NULL THEN
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED
(Entity => 'OPERATION',
Entity_id => v_oprn_id,
Update_Column_name => p_update_table(i).p_col_to_update) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_OPRN_NOT_VALID');
GMD_OPERATIONS_PVT.update_operation(p_oprn_id => v_oprn_id
, p_update_table => p_update_table
, x_message_count => x_message_count
, x_message_list => x_message_list
, x_return_status => l_retn_status);
ROLLBACK TO SAVEPOINT update_oprn;
ROLLBACK TO SAVEPOINT update_oprn;
ROLLBACK TO SAVEPOINT update_oprn;
END update_operation;
delete_operation
Description
This particular procedure is used to set delete_mark = 1 for an operation
Parameters
================================================ */
PROCEDURE delete_operation (
p_api_version IN NUMBER
, p_init_msg_list IN BOOLEAN
, p_commit IN BOOLEAN
, p_oprn_id IN gmd_operations.oprn_id%TYPE
, p_oprn_no IN gmd_operations.oprn_no%TYPE
, p_oprn_vers IN gmd_operations.oprn_vers%TYPE
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2 ) IS
v_update_table gmd_operations_pub.update_tbl_type;
SAVEPOINT delete_oprn;
gmd_debug.put_line('START of delete_operation PUB');
/* Call update_operation and set delete mark for given activity to 1*/
v_update_table(1).p_col_to_update := 'DELETE_MARK';
v_update_table(1).p_value := '1';
/* call update with oprn id if that is what is passed */
update_operation(p_api_version => p_api_version
,p_init_msg_list => FALSE
,p_oprn_id => p_oprn_id
,p_oprn_no => p_oprn_no
,p_oprn_vers => p_oprn_vers
,p_update_table => v_update_table
,x_message_count => x_message_count
,x_message_list => x_message_list
,x_return_status => l_retn_status);
gmd_debug.put_line('END of delete_operation PUB');
ROLLBACK TO SAVEPOINT delete_oprn;
ROLLBACK TO SAVEPOINT delete_oprn;
END delete_operation;