[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Function : This package contains public procedures used to create, modify, and delete operation resources
Pre-reqs : N/A
Parameters: Per function
Current Vers : 1.0
Previous Vers : 1.0
Initial Vers : 1.0
Notes
*/
/* Global Cursors */
CURSOR check_oprn_line_id(V_oprn_line_id NUMBER) IS
SELECT 1
FROM gmd_operation_activities
WHERE oprn_line_id = v_oprn_line_id;
SELECT 1
FROM mtl_units_of_measure
WHERE uom_Code = v_uom
AND (disable_date IS NULL or disable_date > SYSDATE); /* pku */
SELECT 1
FROM cm_cmpt_mst
WHERE COST_CMPNTCLS_id = v_cost_cmpntcls_id
AND delete_mark = 0;
SELECT 1
FROM cm_alys_mst
WHERE cost_analysis_code = v_cost_analysis_code
AND delete_mark = 0;
SELECT 1
FROM gmd_operation_resources
WHERE oprn_line_id = p_oprn_line_id
AND resources = p_resources;
SELECT COUNT(1)
FROM gmd_operation_resources
WHERE oprn_line_id = V_oprn_line_id
AND prim_rsrc_ind = 1;
SELECT COUNT(1)
FROM gmd_operation_resources
WHERE oprn_line_id = V_oprn_line_id
AND (V_resources IS NULL OR resources <> V_resources)
AND prim_rsrc_ind = 1;
insert_operation_resources
Description
This particular procedure is used to insert an
operation resources Parameters
================================================ */
PROCEDURE insert_operation_resources
( p_api_version IN NUMBER
, p_init_msg_list IN BOOLEAN
, p_commit IN BOOLEAN
, p_oprn_line_id IN gmd_operation_activities.oprn_line_id%TYPE
, p_oprn_rsrc_tbl IN gmd_operation_resources_pub.gmd_oprn_resources_tbl_type
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2) IS
v_resources gmd_operation_resources.resources%TYPE;
SELECT oprn_id
FROM gmd_operation_activities
WHERE oprn_line_id = V_oprn_line_id;
SELECT process_qty_uom
FROM gmd_operations a, gmd_operation_activities b
WHERE a.oprn_id = b.oprn_id
AND b.oprn_line_id = V_oprn_line_id;
SELECT cr.cost_cmpntcls_id
FROM cr_rsrc_mst cr, cm_cmpt_mst cm
WHERE cr.cost_cmpntcls_id = cm.cost_cmpntcls_id
AND cr.resources = V_resources
AND cr.delete_mark = 0
AND cm.delete_mark = 0
AND cm.usage_ind = 3;
SELECT cost_analysis_code
FROM fm_actv_mst a, gmd_operation_activities o
WHERE o.activity = a.activity
AND o.oprn_line_id = V_oprn_line_id;
SELECT std_usage_uom
FROM cr_rsrc_mst
WHERE resources = V_resources;
SAVEPOINT insert_oprn_rsrc;
,'insert_operation_resources'
,'gmd_operation_resources_pub') THEN
RAISE invalid_version;
gmd_debug.put_line('In insert_operation_resources public.');
/* Validation: Chcek if this users performing update has access to this
operation owner orgn code */
IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'OPERATION'
,Entity_id => l_oprn_id) THEN
RAISE ins_oprn_rsrc_err;
GMD_OPERATION_RESOURCES_PVT.insert_operation_resources(p_oprn_line_id => p_oprn_line_id,
p_oprn_rsrc_tbl => l_oprn_rsrc_tbl,
x_message_count => x_message_count,
x_message_list => x_message_list,
x_return_status => l_return_status);
gmd_debug.put_line('End of insert_operation_resource PUB');
ROLLBACK TO SAVEPOINT insert_oprn_rsrc;
ROLLBACK TO SAVEPOINT insert_oprn_rsrc;
ROLLBACK TO SAVEPOINT insert_oprn_rsrc;
END insert_operation_resources;
update_operation_resources
Description
This particular procedure is used to update operation resources
Parameters
================================================ */
PROCEDURE update_operation_resources
( p_api_version IN NUMBER
, p_init_msg_list IN BOOLEAN
, p_commit IN BOOLEAN
, p_oprn_line_id IN gmd_operation_resources.oprn_line_id%TYPE
, p_resources IN gmd_operation_resources.resources%TYPE
, p_update_table IN gmd_operation_resources_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_operation_activities
WHERE oprn_line_id = p_oprn_line_id;
SAVEPOINT update_oprn_rsrc;
,'update_operation_resources'
,'gmd_operation_resources_pub') THEN
RAISE invalid_version;
gmd_debug.put_line('Start of update_operation_activity 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
FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
FND_MESSAGE.SET_TOKEN ('MISSING', 'P_COL_TO_UPDATE');
ELSIF 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) = 'COST_ANALYSIS_CODE' THEN
OPEN check_cost_analysis_code(p_update_table(i).p_value);
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'RESOURCE_USAGE' THEN
IF p_update_table(i).p_value < 0 THEN
gmd_api_grp.log_message('FM_RESUSGERR');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'PROCESS_QTY' THEN
IF p_update_table(i).p_value < 0 THEN
gmd_api_grp.log_message('FM_PROCQTYERR');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'resource_usage_uom' THEN
OPEN check_uom(p_update_table(i).p_value);
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'COST_CMPNTCLS_ID' THEN
OPEN check_cost_cmpntcls_id(p_update_table(i).p_value);
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'PRIM_RSRC_IND' THEN
IF p_update_table(i).p_value NOT IN (0, 1, 2) THEN
gmd_api_grp.log_message ('FM_RSRCINDERR');
ELSIF p_update_table(i).p_value = 1 THEN
/* This implies that we are setting this current resource as primary */
/* so let us check if their are any other primaries already existing */
OPEN check_one_prim_rsrc (p_oprn_line_id);
ELSIF p_update_table(i).p_value IN (0,2) THEN
/* This implies that we are setting this current resource as secondary or auxillary */
/* so let us check if their exists atleast one primary resource */
OPEN check_atleast_one (p_oprn_line_id, p_resources);
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'RESOURCE_COUNT' THEN
IF p_update_table(i).p_value < 0 THEN
gmd_api_grp.log_message ('FM_RESCOUNTERR');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'OFFSET_INTERVAL' THEN
IF p_update_table(i).p_value < 0 THEN
gmd_api_grp.log_message('GMD_INVALID_OFFSET');
ELSIF UPPER(p_update_table(i).p_col_to_update) = 'SCALE_TYPE' THEN
IF p_update_table(i).p_value NOT IN (0, 1, 2) THEN
gmd_api_grp.log_message ('FM_SCALETYPERR');
/* Validation: Chcek if this users performing update has access to this
operation owner orgn code */
IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'OPERATION'
,Entity_id => v_oprn_id) THEN
RAISE upd_oprn_rsrc_err;
and Operation is not logically deleted */
IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'OPERATION',
Entity_id => v_oprn_id ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_OPRN_NOT_VALID');
GMD_OPERATION_RESOURCES_PVT.update_operation_resources(p_oprn_line_id => p_oprn_line_id
, p_resources => p_resources
, p_update_table => p_update_table
, x_message_count => x_message_count
, x_message_list => x_message_list
, x_return_status => l_return_status);
gmd_debug.put_line('END of update_operation_resource PUB');
ROLLBACK TO SAVEPOINT update_oprn_rsrc;
ROLLBACK TO SAVEPOINT update_oprn_rsrc;
ROLLBACK TO SAVEPOINT update_oprn_rsrc;
END update_operation_resources;
delete_operation_resources
Description
This particular procedure is used to delete operation resources
Parameters
================================================ */
PROCEDURE delete_operation_resources
( p_api_version IN NUMBER
, p_init_msg_list IN BOOLEAN
, p_commit IN BOOLEAN
, p_oprn_line_id IN gmd_operation_resources.oprn_line_id%TYPE
, p_resources IN gmd_operation_resources.resources%TYPE
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2) IS
v_update_table gmd_operation_resources_pub.update_tbl_type;
SELECT 1
FROM gmd_operation_activities
WHERE oprn_line_id = p_oprn_line_id;
SELECT 1
FROM gmd_operation_resources
WHERE oprn_line_id = p_oprn_line_id
AND resources = p_resources;
SELECT oprn_id
FROM gmd_operation_activities
WHERE oprn_line_id = p_oprn_line_id;
SAVEPOINT delete_oprn_rsrc;
,'delete_operation_resources'
,'gmd_operation_resources_pub') THEN
RAISE invalid_version;
gmd_debug.put_line('START of delete_operation_resources PUB');
/* Validation: Chcek if this users performing update has access to this
operation owner orgn code */
IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'OPERATION'
,Entity_id => v_oprn_id) THEN
RAISE del_oprn_rsrc_err;
/* Call PVT delete_operation_resources */
IF (l_debug = 'Y') THEN
gmd_debug.put_line('before call to delete_operation_resource PVT');
gmd_operation_resources_pvt.delete_operation_resource(p_oprn_line_id => p_oprn_line_id
,p_resources => p_resources
, x_message_count => x_message_count
, x_message_list => x_message_list
, x_return_status => l_return_status);
gmd_debug.put_line('END of delete_operation_resources PUB');
ROLLBACK TO SAVEPOINT delete_oprn_rsrc;
ROLLBACK TO SAVEPOINT delete_oprn_rsrc;
ROLLBACK TO SAVEPOINT delete_oprn_rsrc;
END delete_operation_resources;