[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT batch_id, batch_status
FROM gme_batch_header
WHERE organization_id = v_organization_id AND batch_no = v_batch_no;
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;
This procedure is used to validate all parameters passed to insert activity API
Parameters
x_return_status reflects return status of the API
=============================================================================================*/
PROCEDURE validate_activity_param (
p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
p_step_id IN NUMBER,
p_validate_flexfield IN VARCHAR2
DEFAULT fnd_api.g_false,
p_action IN VARCHAR2,
x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
x_step_status OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'validate_activity_param';
SELECT 1
FROM fm_actv_mst
WHERE activity = v_activity AND delete_mark = 0;
SELECT *
FROM gme_batch_steps
WHERE batchstep_id = v_step_id;
SELECT automatic_step_calculation
FROM gme_batch_header
WHERE batch_id = v_batch_id;
SELECT batch_type
FROM gme_batch_header b, gme_batch_steps s
WHERE b.batch_id = s.batch_id AND batchstep_id = v_step_id;
IF (p_action = 'INSERT')
THEN
-- check FPO
OPEN cur_check_fpo (p_step_id);
insert_batchstep_activity
Description
This procedure is used to insert activity for a batch step
Parameters
p_batchstep_id OR (p_org_code,p_batch_no,step_no) to uniquely identify a step
p_batchstep_activity_rec GME_BATCH_STEP_ACTIVITIES%ROWTYPE - details of the activity
p_batchstep_resource_tbl gme_create_step_pvt.resources_tab - details of the rsrc
x_batchstep_activity_rec GME_BATCH_STEP_ACTIVITIES%ROWTYPE - returns the newly inserted row
x_return_status reflects return status of the API
=============================================================================================*/
PROCEDURE insert_batchstep_activity (
p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
p_batchstep_resource_tbl IN gme_create_step_pvt.resources_tab,
p_org_code IN VARCHAR2 := NULL,
p_batch_no IN VARCHAR2 := NULL,
p_batchstep_no IN NUMBER := NULL,
p_ignore_qty_below_cap IN VARCHAR2
DEFAULT fnd_api.g_false,
p_validate_flexfield IN VARCHAR2
DEFAULT fnd_api.g_false,
x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'insert_batchstep_activity';
SELECT batch_id
FROM gme_batch_steps
WHERE batchstep_id = v_step_id;
p_action => 'INSERT',
x_batchstep_activity_rec => l_batchstep_activity_rec,
x_step_status => l_step_status,
x_return_status => x_return_status
);
l_batchstep_activity_rec.delete_mark := 0;
p_action => 'INSERT',
x_batchstep_resource_rec => l_batchstep_resource_rec,
x_step_status => l_step_status,
x_return_status => x_return_status
);
|| 'before call to GME_BATCH_STEP_ACTIVITIES_DBL.insert_row batch_id='
|| l_batchstep_activity_rec.batch_id
|| ' and step_id ='
|| l_batchstep_activity_rec.batchstep_id
);
IF NOT (gme_batch_step_activities_dbl.insert_row (l_batchstep_activity_rec,
l_batchstep_activity_out_rec
)
)
THEN
RAISE fnd_api.g_exc_error;
|| 'after call to GME_BATCH_STEP_ACTIVITIES_DBL.insert_row activity_id='
|| l_batchstep_activity_out_rec.batchstep_activity_id
);
gme_batchstep_rsrc_pvt.insert_batchstep_rsrc (p_batchstep_resource_rec => l_batchstep_resource_rec,
x_batchstep_resource_rec => l_batchstep_resource_out_rec,
x_return_status => x_return_status
);
END insert_batchstep_activity;
update_batchstep_activity
Description
This procedure is used to update an activity
Parameters
p_batchstep_activity_id OR
(p_org_code,p_batch_no,step_no,activity) to uniquely identify an activity
p_batchstep_activity_rec GME_BATCH_STEP_ACTIVITIES%ROWTYPE - details of the activity
x_batchstep_activity_rec returns the newly updated row for further processing
x_return_status reflects return status of the API
History
=============================================================================================*/
PROCEDURE update_batchstep_activity (
p_batchstep_activity_rec IN gme_batch_step_activities%ROWTYPE,
p_org_code IN VARCHAR2 := NULL,
p_batch_no IN VARCHAR2 := NULL,
p_batchstep_no IN NUMBER := NULL,
p_validate_flexfield IN VARCHAR2
DEFAULT fnd_api.g_false,
x_batchstep_activity_rec OUT NOCOPY gme_batch_step_activities%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'update_batchstep_activity';
l_field_updated BOOLEAN := FALSE;
l_update_resources BOOLEAN := FALSE;
l_last_update_date DATE;
SELECT *
FROM gme_batch_step_resources
WHERE batchstep_activity_id = v_act_id;
SELECT a.batch_id, a.batchstep_id, step_status, activity
FROM gme_batch_step_activities a, gme_batch_steps s
WHERE batchstep_activity_id = v_act_id
AND a.batchstep_id = s.batchstep_id;
SELECT *
FROM gme_batch_steps
WHERE batchstep_id = v_step_id;
SELECT *
FROM gme_batch_header
WHERE batch_id = v_batch_id;
SELECT batch_type
FROM gme_batch_header b, gme_batch_step_activities a
WHERE b.batch_id = a.batch_id AND batchstep_activity_id = v_actv_id;
SELECT last_update_date
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_activity_id
FOR UPDATE OF last_update_date NOWAIT;
FETCH cur_lock_actv INTO l_last_update_date;
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_update_resources := TRUE;
l_field_updated := TRUE;
l_update_resources := TRUE;
l_field_updated := TRUE;
IF NOT (l_field_updated)
THEN
gme_common_pvt.log_message ('GME_NO_CHANGE_TO_UPD');
gme_debug.put_line ('before update of activity');
IF l_update_resources
THEN
OPEN cur_get_batch (l_batch_id);
IF l_batch_hdr.update_inventory_ind = 'Y'
THEN
gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_hdr,
x_rsc_row_count => l_rsrc_trans_count,
x_return_status => x_return_status
);
gme_update_step_qty_pvt.update_resources (p_batch_hdr_rec => l_batch_hdr,
p_batch_step_rec => l_step_dtl,
p_batchstep_activities_rec => l_batchstep_activity_rec,
x_return_status => x_return_status
);
|| 'after call to GME_UPDATE_STEP_QTY_PVT.update_resources return status is '
|| x_return_status
);
IF NOT (gme_batch_step_activities_dbl.update_row (l_batchstep_activity_rec
)
)
THEN
RAISE fnd_api.g_exc_error;
END update_batchstep_activity;
delete_batchstep_activity
Description
This procedure is used to delete an activity and all it's children
Parameters
p_batchstep_activity_id OR
(p_org_code,p_batch_no,step_no,activity) to uniquely identify an activity
x_return_status reflects return status of the API
=============================================================================================*/
PROCEDURE delete_batchstep_activity (
p_batchstep_activity_id IN NUMBER := NULL,
p_org_code IN VARCHAR2 := NULL,
p_batch_no IN VARCHAR2 := NULL,
p_batchstep_no IN NUMBER := NULL,
p_activity IN VARCHAR2 := NULL,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_batchstep_activity';
l_last_update_date DATE;
SELECT *
FROM gme_batch_header
WHERE batch_id = v_batch_id;
SELECT step_status, batch_status, a.batch_id, s.batchstep_id,
batchstep_activity_id, activity
FROM gme_batch_steps s,
gme_batch_step_activities a,
gme_batch_header b
WHERE batchstep_activity_id = v_batchstep_act_id
AND b.batch_id = a.batch_id
AND s.batchstep_id = a.batchstep_id;
SELECT COUNT (1)
FROM gme_batch_step_activities
WHERE batchstep_id = v_step_id;
SELECT batch_type
FROM gme_batch_header b, gme_batch_step_activities a
WHERE b.batch_id = a.batch_id AND batchstep_activity_id = v_actv_id;
SELECT last_update_date
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_activity_id
FOR UPDATE OF last_update_date NOWAIT;
FETCH cur_lock_actv INTO l_last_update_date;
IF l_batch_hdr.update_inventory_ind = 'Y'
THEN
gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_hdr,
x_rsc_row_count => l_rsrc_trans_count,
x_return_status => x_return_status
);
gme_delete_batch_step_pvt.delete_activity (p_batch_step_activities_rec => l_batchstep_activity_rec,
x_return_status => x_return_status
);
gme_debug.put_line ( 'after call to gme_delete_batch_step_pvt.delete_activity return status is '
|| x_return_status
);
END delete_batchstep_activity;