[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Contents INSERT RESOURCE *
* UPDATE RESOURCE *
* DELETE RESOURCE *
* *
* Use This is the private layer of the GME Batch Step *
* Resources. *
* *
* History *
* K.Y.Hunt *
* Reworked for Inventory Convergence. 02-APR-2005 *
* Pawan Kumar 10-Oct-2005 Bug-4175041 *
* Added the interdependency validation for the resource count *
* and resource usage in update_batchstep_resource procedure *
G. Muratore 15-APR-2009 Bug 8335046
Update the last_update_date for locking issues at the step level.
PROCEDURE: update_batchstep_rsrc
G. Muratore 16-MAY-2012 Bug 14037157
Initialize variables properly so that UPDATE action works correctly.
PROCEDURE: validate_rsrc_param
*****************************************************************
*/
/* Global variables */
g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_BATCHSTEP_RSRC_PVT';
SELECT bh.organization_id, bh.batch_id, bh.batch_type
FROM gme_batch_header bh, mtl_parameters mp
WHERE mp.organization_code = v_organization_code
AND mp.organization_id = bh.organization_id
AND bh.batch_no = v_batch_no
AND batch_type = 0;
SELECT batchstep_id, step_status
FROM gme_batch_steps
WHERE batch_id = v_batch_id AND batchstep_no = v_batchstep_no;
SELECT batchstep_activity_id
FROM gme_batch_step_activities
WHERE batchstep_id = v_step_id
AND batch_id = v_batch_id
AND activity = v_activity;
SELECT batchstep_resource_id
FROM gme_batch_step_resources
WHERE batchstep_activity_id = v_activity_id
AND resources = v_resource;
Initialize variables properly so that UPDATE action works correctly.
=============================================================================================*/
PROCEDURE validate_rsrc_param (
p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
,p_activity_id IN NUMBER
,p_ignore_qty_below_cap IN VARCHAR2
DEFAULT fnd_api.g_false
,p_validate_flexfield IN VARCHAR2
DEFAULT fnd_api.g_false
,p_action IN VARCHAR2
,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
,x_step_status OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'validate_rsrc_param';
l_field_updated BOOLEAN := FALSE;
SELECT a.step_status, a.batch_id, a.step_qty_um
FROM gme_batch_steps a, gme_batch_step_activities b
WHERE b.batchstep_activity_id = v_act_id
AND a.batch_id = b.batch_id
AND a.batchstep_id = b.batchstep_id;
SELECT DECODE (v_status
,1, plan_activity_factor
,actual_activity_factor)
,plan_start_date, plan_cmplt_date, actual_start_date
,actual_cmplt_date
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_activity_id;
SELECT plan_start_date, plan_cmplt_date
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_activity_id;
SELECT automatic_step_calculation
FROM gme_batch_header
WHERE batch_id = v_batch_id;
SELECT 1
FROM cm_cmpt_mst
WHERE cost_cmpntcls_id = v_cost_cmpntcls_id;
SELECT 1
FROM cm_alys_mst
WHERE cost_analysis_code = v_cost_analysis_code;
IF p_action = 'INSERT' THEN
/* Validations for Insert processing */
--check analysis code
IF g_debug <= gme_debug.g_log_procedure THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ' validate cost analysis code '
|| p_batchstep_resource_rec.cost_analysis_code);
/* Additional Validations for action INSERT */
IF l_batchstep_resource_rec.offset_interval IS NULL THEN
l_batchstep_resource_rec.offset_interval := 0;
ELSIF p_action = 'UPDATE' THEN
/* ============================ */
--NULL;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
/* When the actual resource count is null in the database and we are trying to update actual resource usage without the
actual resource count, then user will be given error message that actual resource count is required.*/
ELSE
IF (l_batchstep_resource_rec.actual_rsrc_count IS NULL AND p_batchstep_resource_rec.actual_rsrc_usage IS NOT NULL) THEN
gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'actual_rsrc_count');
l_field_updated := TRUE;
l_field_updated := TRUE;
/* When the actual resource count is null in the database and we are trying to update actual resource usage without the
actual resource count, then user will be given error message that actual resource count is required.*/
ELSE
IF (l_batchstep_resource_rec.actual_rsrc_count IS NULL AND p_batchstep_resource_rec.actual_rsrc_usage IS NOT NULL) THEN
gme_common_pvt.log_message('GME_FIELD_VALUE_REQUIRED','FIELD_NAME', 'actual_rsrc_count');
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
l_field_updated := TRUE;
insert_batchstep_rsrc
Description
Procedure is used to insert rsrc for an activity
Parameters
p_batchstep_resource_rec Input Row from GME_BATCH_STEP_RESOURCES
x_batchstep_resource_rec Output Row from GME_BATCH_STEP_RESOURCES
x_return_status reflects return status of the API
=============================================================================================*/
PROCEDURE insert_batchstep_rsrc (
p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'insert_batchstep_rsrc';
SELECT max_step_capacity
FROM gme_batch_steps
WHERE batchstep_id = v_batchstep_id;
SELECT capacity_constraint
FROM cr_rsrc_mst
WHERE resources = v_resources;
rsrc_insert_failure EXCEPTION;
IF l_batch_header.update_inventory_ind = 'Y' THEN
gme_trans_engine_util.load_rsrc_trans
(p_batch_row => l_batch_header
,x_rsc_row_count => l_rsrc_trans_count
,x_return_status => l_return_status);
,p_action_code => 'INSERT'
,p_check_prim_rsrc => TRUE
,x_step_resources_rec => l_batchstep_resource_out_rec
,x_return_status => l_return_status);
gme_debug.put_line ('after insert ' || l_return_status);
RAISE rsrc_insert_failure;
UPDATE gme_batch_steps
SET max_step_capacity = l_batchstep_resource_rec.max_capacity
,last_update_date = gme_common_pvt.g_timestamp
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_login = gme_common_pvt.g_login_id
WHERE batchstep_id = l_batchstep_resource_rec.batchstep_id
AND batch_id = l_batchstep_resource_rec.batch_id;
WHEN validation_failure OR rsrc_not_found OR rsrc_insert_failure OR error_condition THEN
x_return_status := fnd_api.g_ret_sts_error;
END insert_batchstep_rsrc;
update_batchstep_rsrc
Description
Procedure to update resource for an activity
Parameters
p_batchstep_resource_rec Input Row from GME_BATCH_STEP_RESOURCES
x_batchstep_resource_rec Output Row from GME_BATCH_STEP_RESOURCES
x_return_status reflects return status of the API
History
Inventory Convergence Project - March 2005
G. Muratore 15-APR-2009 Bug 8335046
Update the last_update_date for locking issues at the step level.
=============================================================================================*/
PROCEDURE update_batchstep_rsrc (
p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_batchstep_rsrc';
l_field_updated BOOLEAN := FALSE;
l_update_capacity NUMBER;
SELECT a.batchstep_id, a.step_status, b.batchstep_activity_id
,a.batch_id, b.resources
FROM gme_batch_steps a, gme_batch_step_resources b
WHERE b.batchstep_resource_id = v_resource_id
AND a.batch_id = b.batch_id
AND a.batchstep_id = b.batchstep_id;
SELECT max_step_capacity
FROM gme_batch_steps;
SELECT capacity_constraint
FROM cr_rsrc_dtl
WHERE resources = v_resources AND orgn_code = v_orgn_code;
SELECT capacity_constraint
FROM cr_rsrc_mst
WHERE resources = v_resources;
SELECT 1
FROM gme_batch_header a, gme_batch_step_resources b
WHERE a.batch_id = b.batch_id
AND b.batchstep_resource_id = v_rsrc_id
AND a.batch_type = 10;
rsrc_update_failure EXCEPTION;
IF l_batch_header.update_inventory_ind = 'Y' THEN
gme_trans_engine_util.load_rsrc_trans
(p_batch_row => l_batch_header
,x_rsc_row_count => l_rsrc_trans_count
,x_return_status => l_return_status);
,p_action_code => 'UPDATE'
,p_check_prim_rsrc => TRUE
,x_step_resources_rec => l_batchstep_resource_out_rec
,x_return_status => l_return_status);
RAISE rsrc_update_failure;
l_update_capacity := 0;
l_update_capacity := 1;
UPDATE gme_batch_steps
SET max_step_capacity = DECODE(l_update_capacity, 1, l_batchstep_resource_out_rec.max_capacity, max_step_capacity),
last_update_date = l_batchstep_resource_out_rec.last_update_date
WHERE batchstep_id = l_batchstep_id AND batch_id = l_batch_id;
WHEN rsrc_not_valid OR rsrc_update_failure OR input_param_missing THEN
x_return_status := fnd_api.g_ret_sts_error;
END update_batchstep_rsrc;
delete_batchstep_rsrc
Description
Procedure to delete batchstep resource
Parameters
p_batchstep_resource_rec batchstep resource row targetted for deletion
x_return_status reflects return status of the API
=============================================================================================*/
PROCEDURE delete_batchstep_rsrc (
p_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_batchstep_rsrc';
IF l_batch_header.update_inventory_ind = 'Y' THEN
gme_trans_engine_util.load_rsrc_trans
(p_batch_row => l_batch_header
,x_rsc_row_count => l_rsrc_trans_count
,x_return_status => l_return_status);
,p_action_code => 'DELETE'
,p_check_prim_rsrc => TRUE
,x_step_resources_rec => l_batchstep_resource_rec
,x_return_status => l_return_status);
gme_debug.put_line ( 'delete batchsetp resource returns '
|| l_return_status);
END delete_batchstep_rsrc;
SELECT plan_start_date, plan_cmplt_date, actual_start_date
,actual_cmplt_date
FROM gme_batch_step_activities
WHERE batchstep_activity_id = p_batchstep_activity_id;
SELECT 1
FROM gem_lookups
WHERE lookup_type = p_lookup_type AND lookup_code = p_lookup_code;
Move input attribute values into the output record structure prior to validation or update
processing.
If p_validate_flexfield is TRUE, just move the value. The validation processing will
do the rest.
If flexfield validation is FALSE, then interpret the input values according to these rules
NULL means update value not supplied so retain the original (old) value
G_MISS_??? means update with a NULL value
NOT NULL update with the supplied value
Parameters
p_new_batchstep_resource_rec input record with values to be applied as updates
p_old_batchstep_resource_rec original record retrieved from the database
p_validate_flexfield indicates whethere validation required or not
x_batchstep_resource_rec Consolidation of the inputs above
x_return_status Return status
=============================================================================================*/
PROCEDURE consolidate_flexfields (
p_new_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
,p_old_batchstep_resource_rec IN gme_batch_step_resources%ROWTYPE
,p_validate_flexfield IN VARCHAR2
DEFAULT fnd_api.g_false
,x_batchstep_resource_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'consolidate_flexfields';
/* On this basis, retain the new update values */
l_batchstep_resource_rec.attribute_category :=
p_new_batchstep_resource_rec.attribute_category;