[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gsa.batchstep_id
,DECODE (gsa.sequence_dependent_ind, 1, 1, 2) seq_dep_order
,gsa.offset_interval, gsa.activity
,gsa.batchstep_activity_id
,NVL (gsa.sequence_dependent_ind, -1)
sequence_dependent_ind
FROM gme_batch_step_activities gsa
WHERE gsa.batch_id = v_batch_id
ORDER BY 1, 2, 3, 4, 5;
/* select all of the activities for the batch into the table check on the way
if there was any numbering done already. */
LOOP
FETCH get_activities
INTO act_tab (act_cnt);
UPDATE gme_batch_step_activities
SET sequence_dependent_ind = act_tab (i).seq_dep_ind
,last_update_date = SYSDATE
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_login = gme_common_pvt.g_login_id
WHERE batchstep_activity_id =
act_tab (i).batchstep_activity_id;
UPDATE gme_batch_step_activities
SET sequence_dependent_ind = act_tab (i).seq_dep_ind
,last_update_date = SYSDATE
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_login =
gme_common_pvt.g_login_id
----- Punit Kumar
WHERE batchstep_activity_id =
act_tab (i).batchstep_activity_id;
/* select only pending or wip batches */
CURSOR get_batches
IS
SELECT gbh.batch_id
FROM gme_batch_header gbh
WHERE gbh.batch_status IN (1, 2);
/* this query will select all the resources that need to have the setup applied
the step resource must be defined at the plant and be scheduled to an
instance There must be an activity defined in the step as sequence dependent.
All of the resources in the step will have the setup applied, hence there is
no join through activity. we just need to confirm that sequences dependencies
will be applied in the step. The sequence type will be found for operation
specific or global, hence there will be two outer joins */
l_api_name CONSTANT VARCHAR2 (30) := 'set_sequence_dependent_id';
SELECT gsr.batchstep_resource_id, 1 sds_type, gst.seq_dep_id
FROM gme_batch_header gbh
,gmd_recipe_validity_rules gvr
,gme_batch_steps gbs
,gme_batch_step_activities gsa
,gme_batch_step_resources gsr
,cr_rsrc_dtl crd
,ic_item_mst iim
,gmp_sequence_types gst
WHERE gbh.batch_id = v_batch_id
AND gbh.recipe_validity_rule_id = gvr.recipe_validity_rule_id
AND gbh.batch_id = gbs.batch_id
AND gbs.batchstep_id = gsr.batchstep_id
AND gvr.item_id = iim.item_id
AND iim.seq_category_id IS NOT NULL
AND gbs.batchstep_id = gsa.batchstep_id
AND gsa.sequence_dependent_ind = 1
AND gbh.organization_id = crd.organization_id
AND gsr.resources = crd.resources
AND crd.schedule_ind = 2
AND iim.seq_category_id = gst.category_id
AND gbs.oprn_id = gst.oprn_id
UNION ALL
SELECT gsr2.batchstep_resource_id, 2 sds_type, gst2.seq_dep_id
FROM gme_batch_header gbh2
,gmd_recipe_validity_rules gvr2
,gme_batch_steps gbs2
,gme_batch_step_activities gsa2
,gme_batch_step_resources gsr2
,cr_rsrc_dtl crd2
,gmp_sequence_types gst2
,ic_item_mst iim2
WHERE gbh2.batch_id = v_batch_id
AND gbh2.recipe_validity_rule_id = gvr2.recipe_validity_rule_id
AND gbh2.batch_id = gbs2.batch_id
AND gbs2.batchstep_id = gsr2.batchstep_id
AND gvr2.item_id = iim2.item_id
AND iim2.seq_category_id IS NOT NULL
AND gbs2.batchstep_id = gsa2.batchstep_id
AND gsa2.sequence_dependent_ind = 1
AND gbh2.organization_id = crd2.organization_id
AND gsr2.resources = crd2.resources
AND crd2.schedule_ind = 2
AND -1 = gst2.oprn_id
AND iim2.seq_category_id = gst2.category_id
ORDER BY 1, 2;
do not update the row and go one to the next row */
IF (v_sds_type = 1)
OR (v_sds_type = 2 AND last_res <> v_batchstep_resource_id) THEN
last_res := v_batchstep_resource_id;
UPDATE gme_batch_step_resources
SET sequence_dependent_id = v_seq_dep_id
,last_update_date = SYSDATE
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_login = gme_common_pvt.g_login_id --- Punit
WHERE batchstep_resource_id = v_batchstep_resource_id;
SELECT gbh.batch_id
FROM gme_batch_header gbh
WHERE gbh.batch_status IN (1, 2)
AND gbh.batch_id IN (
SELECT gbh1.batch_id
FROM gme_batch_header gbh1
,gme_batch_step_activities gsa
WHERE gbh.batch_status IN (1, 2)
AND gbh.batch_id = gsa.batch_id
AND gsa.sequence_dependent_ind = 1);
SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id = v_batch_id;
SELECT 1
FROM gme_batch_step_charges
WHERE batch_id = v_batch_id
AND batchstep_id = v_batchstep_id
AND ROWNUM = 1;
UPDATE gme_batch_step_charges
SET plan_start_date = NULL
,plan_cmplt_date = NULL
,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 --- Punit
WHERE batch_id = p_batch_id AND batchstep_id = p_batchstep_id;
UPDATE gme_batch_step_charges
SET plan_start_date = NULL
,plan_cmplt_date = NULL
,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 --- Punit
WHERE batch_id = p_batch_id
AND batchstep_id = l_gme_batchstep_ids_tab (i);
SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id = v_batch_id;
SELECT 1
FROM gme_batch_step_charges
WHERE batch_id = v_batch_id
AND batchstep_id = v_batchstep_id
AND ROWNUM = 1;
IF NOT (gme_batch_step_charge_dbl.delete_row
(p_batch_step_charges_in => l_batch_step_charges_in) ) THEN
RAISE clear_chg_error;
IF NOT (gme_batch_step_charge_dbl.delete_row
(p_batch_step_charges_in => l_batch_step_charges_in) ) THEN
RAISE clear_chg_error;
SELECT MIN (a.sequence_dependent_ind)
FROM gme_batch_step_activities a, gme_batch_step_resources r
WHERE r.resources = v_resources
AND r.batchstep_activity_id = a.batchstep_activity_id
AND a.batchstep_id = v_batchstep_id
AND r.batchstep_id = v_batchstep_id
AND r.scale_type = 2;
error_charge_insert EXCEPTION;
IF (gme_batch_step_charge_dbl.insert_row
(p_batch_step_charges_in => l_batchstep_charges_in
,x_batch_step_charges => l_batchstep_charges_in) ) THEN
IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
gme_debug.put_line
('sucessfully inserted into batchstep charges');
RAISE error_charge_insert;
IF (gme_batch_step_charge_dbl.insert_row
(p_batch_step_charges_in => l_batchstep_charges_in
,x_batch_step_charges => l_batchstep_charges_in) ) THEN
IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
gme_debug.put_line ('sucessfully inserted into batchstep charges');
RAISE error_charge_insert;
WHEN error_charge_insert OR error_act_seq_num_calc THEN
x_return_status := fnd_api.g_ret_sts_error;