[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ROWID,reference_number
FROM igs_tr_step_note
WHERE tracking_id = lp_tracking_id
FOR UPDATE OF tracking_id NOWAIT;
igs_tr_step_note_pkg.delete_row(x_rowid => v_tsn_rec.ROWID);
SELECT igs_tr_step.*,ROWID
FROM igs_tr_step
WHERE tracking_id = lp_tracking_id
FOR UPDATE OF tracking_id NOWAIT;
igs_tr_step_pkg.delete_row( x_rowid => v_ts_rec.ROWID);
SELECT ROWID,igs_tr_group_member.*
FROM igs_tr_group_member
WHERE tracking_id = p_tracking_id
FOR UPDATE OF tracking_id NOWAIT;
igs_tr_group_member_pkg.delete_row( x_rowid => v_tgm_rec.ROWID);
SELECT ROWID, reference_number
FROM igs_tr_item_note
WHERE tracking_id = lp_tracking_id
FOR UPDATE OF tracking_id NOWAIT;
igs_tr_item_note_pkg.delete_row( x_rowid => v_tin_rec.ROWID);
SELECT ROWID, igs_tr_item.*
FROM igs_tr_item
WHERE tracking_id = lp_tracking_id
FOR UPDATE OF tracking_id NOWAIT;
igs_tr_item_pkg.delete_row( x_rowid => v_tri_rec.ROWID);
SELECT ROWID
FROM igs_tr_step_grp_lmt
WHERE tracking_id = lp_tracking_id
FOR UPDATE OF tracking_id NOWAIT;
igs_tr_step_grp_lmt_pkg.delete_row( x_rowid => rec_del_grp_lmt.rowid);
IF ( -- Delete tracking_step_note
trkpl_del_tsn(p_tracking_id) = FALSE OR
-- Delete tracking_step
trkpl_del_ts(p_tracking_id) = FALSE OR
-- Deleting step group limit from IGS_TR_STEP_GRP_LMT
trkpl_del_grp_lmt(p_tracking_id) = FALSE OR
-- Delete tracking_group_member
trkpl_del_tgm(p_tracking_id) = FALSE OR
-- Delete tracking_item_note
trkpl_del_tin(p_tracking_id) = FALSE OR
-- Delete tracking_item
trkpl_del_tri(p_tracking_id) = FALSE) THEN
ROLLBACK TO sp_del_tri;
SELECT tracking_id
FROM igs_tr_group_member
WHERE tracking_group_id = cp_tracking_group_id;
SELECT s_tracking_status
FROM igs_tr_item it, igs_tr_status ts
WHERE it.tracking_id = cp_tracking_id
AND it.tracking_status = ts.tracking_status;
1. This program unit is used to insert the default tracking_steps for an item
based on the IGS_TR_TYPE and its associated tracking_type_steps, duplicate
existing IGS_GE_NOTE records and insert IGS_TR_STEP_NOTES
Usage: (e.g. restricted, unrestricted, where to call from)
1. Called from IGSTR007.FMB upon creation of a tracking item.
Known limitations/enhancements/remarks:
-
Change History: (who, when, what: NO CREATION RECORDS HERE!)
Who When What
msrinivi 06 Jul,2001 Added logic to insert the newly added columns, i.e.
step catalog id,step group id and publish indicator
pradhakr 14-Feb-2002 Added code to insert step group limit after the
default of tracking type steps.
*******************************************************************************/
gv_other_detail VARCHAR2(255);
SELECT tracking_type, originator_person_id,start_dt,completion_due_dt, override_offset_clc_ind
FROM igs_tr_item
WHERE tracking_id = cp_tracking_id;
SELECT *
FROM igs_tr_type_step
WHERE tracking_type = cp_tracking_type;
SELECT reference_number, trk_note_type
FROM igs_tr_typ_step_note
WHERE tracking_type = cp_tracking_type
AND tracking_type_step_id = cp_tracking_type_step_id;
SELECT step_group_id,
step_group_limit
FROM igs_tr_tstp_grp_lmt
WHERE tracking_type = cp_tracking_type;
igs_tr_step_pkg.insert_row (
x_rowid => p_rowid,
x_tracking_id => p_tracking_id,
x_tracking_step_id => p_tracking_step_id ,
x_tracking_step_number => p_tracking_step_number,
x_description => p_description,
x_s_tracking_step_type=> p_s_tracking_step_type,
x_completion_dt => p_completion_dt,
x_action_days => p_action_days,
x_step_completion_ind => p_step_completion_ind,
x_by_pass_ind => p_by_pass_ind,
x_recipient_id => p_recipient_id,
x_mode => 'R',
x_step_catalog_cd => p_step_catalog_cd,
x_step_group_id => p_step_group_id,
x_publish_ind => p_publish_ind
);
SELECT s_note_format_type, note_text
FROM igs_ge_note
WHERE reference_number = p_reference_number;
SELECT igs_ge_note_rf_num_s.NEXTVAL INTO v_next_ref_number FROM dual;
igs_ge_note_pkg.insert_row(
x_rowid => p_rowid,
x_reference_number => v_next_ref_number,
x_s_note_format_type => v_s_note_format_type,
x_note_text => v_note_text,
x_mode => 'R'
);
igs_tr_step_note_pkg.insert_row(
x_rowid => p_rowid,
x_tracking_id => p_tracking_id,
x_tracking_step_id => p_tracking_step_id,
x_reference_number => p_reference_number,
x_trk_note_type => p_trk_note_type,
x_mode => 'R'
);
IGS_TR_STEP_GRP_LMT_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_TRACKING_ID => p_tracking_id,
X_STEP_GROUP_ID => p_step_group_id,
X_STEP_GROUP_LIMIT => p_step_group_limit
);
-- If any tracking group limit exists then insert into IGS_TR_STEP_GRP_LMT
FOR rec_tracking_type_step_grplmt IN c_tracking_type_step_grplmt(v_tracking_type) LOOP
trkpl_ins_ts_grp_lmt (p_tracking_id,
rec_tracking_type_step_grplmt.step_group_id,
rec_tracking_type_step_grplmt.step_group_limit);
It will accept the details of the item to be created and insert a
IGS_TR_ITEM record. The tracking item step will be defaulted
when the database table insert trigger fires for the tracking item.
Usage: (e.g. restricted, unrestricted, where to call from)
1. Called from IGSTR007.FMB
Known limitations/enhancements/remarks:
-
Change History: (who, when, what: NO CREATION RECORDS HERE!)
Who When What
msrinivi 06 Jul,2001 Added 3 new columns : completion due dt,
override offset clc ind and publish ind
ssawhney 18 Oct 2002 p_target_days, use NVL with cursor value. RE03 was not passing them
*******************************************************************************/
gv_other_detail VARCHAR2(255);
SELECT 'x'
FROM igs_tr_status
WHERE tracking_status = p_tracking_status;
SELECT *
FROM igs_tr_type
WHERE tracking_type = p_tracking_type;
SELECT 'x'
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT igs_tr_item_tr_id_s.NEXTVAL
FROM dual;
igs_tr_item_pkg.insert_row(
x_rowid => p_rowid,
x_tracking_id => v_nxt_tracking_id,
x_tracking_status => p_tracking_status ,
x_tracking_type => p_tracking_type,
x_source_person_id => p_source_person_id,
x_start_dt => NVL(p_start_dt, SYSDATE),
x_target_days => NVL(p_target_days, v_tracking_type.target_days),
x_sequence_ind => l_sequence_ind,
x_business_days_ind => l_business_days_ind,
x_originator_person_id => p_originator_person_id,
x_s_created_ind => NVL(p_s_created_ind, 'N'),
x_mode => 'R',
x_org_id => igs_ge_gen_003.get_org_id,
x_override_offset_clc_ind => NVL(p_override_offset_clc_ind,'N'),
x_completion_due_dt => NVL(p_completion_due_dt,v_completion_due_dt),
x_publish_ind => p_publish_ind
);
SELECT *
FROM igs_tr_step
WHERE tracking_id = p_tracking_id
ORDER BY tracking_step_number;
SELECT *
FROM igs_Tr_step
WHERE tracking_id = p_tracking_id
AND tracking_step_number > p_tracking_step_number
ORDER BY tracking_step_number;
SELECT *
FROM igs_tr_step
WHERE tracking_id = p_tracking_id
AND tracking_step_number > p_tracking_step_number
ORDER BY tracking_step_number;
SELECT *
FROM igs_tr_step
WHERE tracking_id = p_tracking_id
ORDER BY tracking_step_number;
SELECT *
FROM igs_tr_step
WHERE tracking_id = p_tracking_id
AND tracking_step_number > p_tracking_step_number
ORDER BY tracking_step_number;
SELECT tab.step_group_id, tab.step_group_limit
FROM
( SELECT step_group_id, count(*) step_group_limit
FROM igs_tr_step trst
WHERE trst.tracking_id = p_tracking_id
AND trst.tracking_step_number < p_tracking_step_number
AND trst.step_group_id is not null
AND trst.step_group_id <> p_step_group_id
AND trst.step_completion_ind = 'Y'
AND trst.by_pass_ind = 'N'
GROUP BY trst.step_group_id, trst.step_completion_ind
MINUS
SELECT step_group_id, step_group_limit
FROM igs_tr_step_grp_lmt
WHERE tracking_id = p_tracking_id
) tab ,
igs_tr_step_grp_lmt trg
WHERE tab.step_group_id = trg.step_group_id
AND trg.tracking_id = p_tracking_id
AND tab.step_group_limit < trg.step_group_limit ;
SELECT DISTINCT TO_NUMBER('1')
FROM igs_tr_step trst
WHERE trst.tracking_id = p_tracking_id
AND trst.tracking_step_number < p_tracking_step_number
AND trst.step_group_id IS NULL
AND trst.step_completion_ind = 'N'
AND trst.by_pass_ind = 'N'
UNION
(
SELECT DISTINCT trst.step_group_id
FROM igs_tr_step trst
WHERE trst.tracking_id = p_tracking_id
AND trst.tracking_step_number < p_tracking_step_number
AND trst.step_group_id IS NOT NULL
AND trst.step_completion_ind = 'N'
AND trst.by_pass_ind = 'N'
GROUP BY trst.step_group_id, trst.step_completion_ind
MINUS
SELECT DISTINCT trst.step_group_id
FROM igs_tr_step trst
WHERE trst.tracking_id = p_tracking_id
AND trst.tracking_step_number < p_tracking_step_number
AND trst.step_group_id IS NOT NULL
AND trst.step_completion_ind = 'Y'
GROUP BY trst.step_group_id, trst.step_completion_ind
);
1. This module will update fields of the action days of
a IGS_TR_STEP record.
Usage: (e.g. restricted, unrestricted, where to call from)
1. Called from IGSTR007.FMB
Known limitations/enhancements/remarks:
-
Change History: (who, when, what: NO CREATION RECORDS HERE!)
Who When What
msrinivi 06 Jul,2001 Modified to have the logic for step groups before update. msrinivi
11 Jul,2001 Step update not allowed if item is complete,
Complete item if all the steps are considered complete
Aiyer 24-Apr-2002 This code has been modified by Aiyer for the bug 2309359
In call to function igs_tr_gen_001.trkp_clc_bus_dt the p_business_days
parameter was being passed as 'N'.This was causing a numeric conversion error .
This has been set to NVL(p_action_days,0) in this fix.
ssawhney 4-Nov-2003 Bug 3206700. The cursor to check item completion modified.
Logic also modified to concider BY PASS item as complete.
**************************************************************************************************************/
lv_param_values VARCHAR2(1080);
SELECT *
FROM igs_tr_item
WHERE tracking_id = p_tracking_id;
SELECT s_tracking_status
FROM igs_tr_status
WHERE tracking_status = p_tracking_status;
CURSOR c_update_trst IS
SELECT trst.*, trst.rowid ROW_ID, igs_tr_gen_001.trkp_clc_action_dt ( trst.tracking_id, trst.tracking_step_number,
tri.start_dt, tri.sequence_ind,tri.business_days_ind ) action_dt
FROM igs_tr_step trst,
igs_tr_item tri
WHERE trst.tracking_id = p_tracking_id
AND trst.tracking_id = tri.tracking_id
AND ((NVL(p_tracking_step_id, 0) = 0)
OR
(trst.tracking_step_id = p_tracking_step_id))
AND ((NVL(p_s_tracking_step_type, 'NULL') = 'NULL')
OR
(trst.s_tracking_step_type = p_s_tracking_step_type))
AND ROWNUM = 1
ORDER BY trst.tracking_step_number DESC
FOR UPDATE OF trst.action_days NOWAIT;
SELECT DISTINCT TO_NUMBER('1')
FROM igs_tr_step trst
WHERE trst.tracking_id = p_tracking_id
AND trst.step_group_id IS NULL
AND trst.step_completion_ind = 'N'
AND trst.by_pass_ind = 'N'
UNION
(
SELECT distinct step_group_id
FROM igs_tr_step a
WHERE a.tracking_id = p_tracking_id
AND a.step_group_id IS NOT NULL
-- total no of groups for the tracking item
MINUS
-- subtract groups that are open
SELECT c.step_group_id
FROM (
SELECT tab.step_group_id, tab.count_step
FROM (
SELECT trst.step_group_id step_group_id,
COUNT(*) count_step
FROM igs_tr_step trst
WHERE trst.tracking_id = p_tracking_id
AND trst.step_group_id IS NOT NULL
AND ((trst.step_completion_ind = 'Y' AND trst.by_pass_ind = 'N') OR
(trst.step_completion_ind = 'N' AND trst.by_pass_ind = 'Y'))
GROUP BY trst.step_group_id,
trst.step_completion_ind
) tab,
-- a step is considered complete if either its BY PASSED or its completion ind is Y
igs_tr_step_grp_lmt trg
WHERE tab.step_group_id = trg.step_group_id
AND trg.tracking_id = p_tracking_id
AND tab.count_step >= trg.step_group_limit
-- subtract only if the total steps completed are less than the group limit.
) c
);
SELECT tracking_status
FROM igs_tr_status
WHERE s_tracking_status = 'COMPLETE'
AND default_ind = 'Y';
igs_tr_step_rec c_update_trst%ROWTYPE;
SELECT NVL(trst.completion_dt,igs_tr_gen_001.trkp_clc_action_dt ( trst.tracking_id, trst.tracking_step_number,
tri.start_dt, tri.sequence_ind,tri.business_days_ind )) start_dt
FROM igs_tr_step trst, igs_tr_item tri
WHERE trst.tracking_id = tri.tracking_id
AND trst.tracking_id = p_tracking_id
AND trst.tracking_step_number = (SELECT MAX(b.tracking_step_number)
FROM igs_tr_step b
WHERE b.tracking_id = p_tracking_id
AND b.tracking_step_number < p_tracking_step_number
AND b.by_pass_ind = 'N');
SELECT trst.*, trst.rowid ROW_ID, igs_tr_gen_001.trkp_clc_action_dt ( trst.tracking_id, trst.tracking_step_number,
tri.start_dt, tri.sequence_ind,tri.business_days_ind ) action_dt
FROM igs_tr_step trst,
igs_tr_item tri
WHERE trst.tracking_id = tri.tracking_id
AND trst.tracking_id = p_tracking_id
AND trst.tracking_step_number > p_tracking_step_number
FOR UPDATE OF trst.action_days NOWAIT;
igs_tr_step_pkg.update_row(
x_rowid => l_upd_next_dt_rec.row_id,
x_tracking_id => l_upd_next_dt_rec.tracking_id,
x_tracking_step_id => l_upd_next_dt_rec.tracking_step_id,
x_tracking_step_number => l_upd_next_dt_rec.tracking_step_number,
x_description => l_upd_next_dt_rec.description,
x_s_tracking_step_type => l_upd_next_dt_rec.s_tracking_step_type,
x_completion_dt => l_upd_next_dt_rec.completion_dt,
x_action_days => l_action_days,
x_step_completion_ind => l_upd_next_dt_rec.step_completion_ind,
x_by_pass_ind => l_upd_next_dt_rec.by_pass_ind,
x_recipient_id => l_upd_next_dt_rec.recipient_id,
x_mode => 'R',
x_step_group_id => l_upd_next_dt_rec.step_group_id,
x_step_catalog_cd => l_upd_next_dt_rec.step_catalog_cd,
x_publish_ind => NVL(l_upd_next_dt_rec.publish_ind,'N')
);
p_message_name := 'IGS_TR_CANNOT_UPDATE_STEP';
OPEN c_update_trst;
FETCH c_update_trst INTO igs_tr_step_rec ;
IF (c_update_trst%NOTFOUND) THEN
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
CLOSE c_update_trst;
igs_tr_step_pkg.update_row(
x_rowid => igs_tr_step_rec.row_id,
x_tracking_id => igs_tr_step_rec.tracking_id,
x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
x_description => igs_tr_step_rec.description,
x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
x_completion_dt => igs_tr_step_rec.completion_dt,
x_action_days => igs_tr_step_rec.action_days,
x_step_completion_ind => igs_tr_step_rec.step_completion_ind,
x_by_pass_ind => igs_tr_step_rec.by_pass_ind,
x_recipient_id => igs_tr_step_rec.recipient_id,
x_mode => 'R',
x_step_group_id => igs_tr_step_rec.step_group_id,
x_step_catalog_cd => igs_tr_step_rec.step_catalog_cd,
x_publish_ind => NVL(igs_tr_step_rec.publish_ind,'N')
);
CLOSE c_update_trst;
igs_tr_item_pkg.update_row(
x_mode => 'R',
x_rowid => igs_tr_item_rec.row_id,
x_tracking_id => igs_tr_item_rec.tracking_id,
x_tracking_status => l_trk_sts,
x_tracking_type => igs_tr_item_rec.tracking_type,
x_source_person_id => igs_tr_item_rec.source_person_id,
x_start_dt => igs_tr_item_rec.start_dt,
x_target_days => igs_tr_item_rec.target_days,
x_sequence_ind => igs_tr_item_rec.sequence_ind,
x_business_days_ind => igs_tr_item_rec.business_days_ind,
x_originator_person_id => igs_tr_item_rec.originator_person_id,
x_s_created_ind => igs_tr_item_rec.s_created_ind,
x_override_offset_clc_ind => igs_tr_item_rec.override_offset_clc_ind,
x_completion_due_dt => igs_tr_item_rec.completion_due_dt,
x_publish_ind => igs_tr_item_rec.publish_ind
);
p_message_name := 'IGS_TR_CANNOT_UPDATE_STEP';
1. If that step group being deleted in form IGSTR001 ( block tracking step) is the last one of its
kind for a particular tracking id, the step group id will be deleted from IGS_TR_TSTP_GRP_LMT
else Step Group Limit will be decremented by 1.
2. Any new Step Group ID being created in the form IGSTR001 ( block tracking step) will also be created
in the table IGS_TR_TSTP_GRP_LMT and the Step Group Limit would be defaulted to 1.
Known limitations,enhancements,remarks:
Change History
Who When What
************************************************************************************************************/
--ssawhney view reference changed to table
-- Decrements a step group limit
CURSOR c_decrement_step_grp_lmt IS
SELECT step_group_id
FROM igs_tr_tstp_grp_lmt
WHERE tracking_type = p_tracking_type
MINUS
SELECT step_group_id
FROM igs_tr_type_step tts
WHERE tracking_type = p_tracking_type
AND tts.step_group_id IS NOT NULL
GROUP BY tts.step_group_id;
CURSOR c_insert_step_grp_lmt IS
SELECT step_group_id
FROM igs_tr_type_step tts
WHERE tracking_type = p_tracking_type
AND tts.step_group_id IS NOT NULL
GROUP BY tts.step_group_id
MINUS
SELECT step_group_id
FROM igs_tr_tstp_grp_lmt
WHERE tracking_type = p_tracking_type;
SELECT ROWID
FROM igs_tr_tstp_grp_lmt
WHERE tracking_type = p_tracking_type
AND step_group_id = p_step_group_id;
SELECT step_group_id, COUNT(*) step_group_count
FROM igs_tr_type_step
WHERE tracking_type = p_tracking_type
GROUP BY step_group_id;
SELECT step_group_limit
FROM igs_tr_tstp_grp_lmt
WHERE tracking_type = p_tracking_type
AND step_group_id = p_step_group_id;
igs_tr_tstp_grp_lmt_pkg.delete_row ( X_ROWID => l_rowid );
FOR rec_insert_step_grp_lmt IN c_insert_step_grp_lmt LOOP
igs_tr_tstp_grp_lmt_pkg.insert_row (
X_ROWID => lv_rowid ,
X_TRACKING_TYPE => p_tracking_type ,
X_STEP_GROUP_ID => rec_insert_step_grp_lmt.step_group_id ,
X_STEP_GROUP_LIMIT => 1 ,
X_MODE => 'R'
);
igs_tr_tstp_grp_lmt_pkg.update_row (
X_ROWID => l_rowid ,
X_TRACKING_TYPE => p_tracking_type ,
X_STEP_GROUP_ID => rec_type_step.step_group_id ,
X_STEP_GROUP_LIMIT => rec_type_step.step_group_count ,
X_MODE => 'R'
);
1. If that step group being deleted in form IGSTR007 ( block tracking step) is the last one of its
kind for a particular tracking id, the step group id will be deleted from IGS_TR_STEP_GRP_LMT
2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created
in the table IGS_TR_STEP_GRP_LMT and the Step Group Limit would be defaulted to 1.
3. In case IGS_TR_STEP_GRP_LMT.STEP_GROUP_LIMIT is greater than the count of step_group_id's for tracking_id and step_group_id
combination in IGS_TR_STEP_V view then set it equal to the lower value (i.e count of step_group_id's in the IGS_TR_STEP_V).
Known limitations,enhancements,remarks:
Change History
Who When What
************************************************************************************************************/
-- Fetch all the distinct step group id's for the given tracking id which are there in the table IGS_TR_STEP_GRP_LMT and not in the view IGS_TR_STEP_V
-- IF such records are found then delete them from the table igs_tr_step_grp_lmt
-- ssawhney view reference changed to table
CURSOR c_decrement_step_grp_lmt
IS
SELECT
step_group_id
FROM
igs_tr_step_grp_lmt
WHERE
tracking_id = p_tracking_id
MINUS
SELECT
step_group_id
FROM
igs_tr_step sv
WHERE
tracking_id = p_tracking_id
AND
sv.step_group_id IS NOT NULL
GROUP BY
sv.step_group_id;
CURSOR c_insert_step_grp_lmt
IS
SELECT
step_group_id
FROM
igs_tr_step sv
WHERE
tracking_id = p_tracking_id
AND
sv.step_group_id IS NOT NULL
GROUP BY
sv.step_group_id
MINUS
SELECT
step_group_id
FROM
igs_tr_step_grp_lmt
WHERE
tracking_id = p_tracking_id;
SELECT
ROWID
FROM
igs_tr_step_grp_lmt
WHERE
tracking_id = p_tracking_id
AND
step_group_id = p_step_group_id;
SELECT
step_group_id,
COUNT(step_group_id) step_group_count
FROM
igs_tr_step
WHERE
tracking_id = p_tracking_id
AND
step_group_id IS NOT NULL
GROUP BY
step_group_id;
SELECT
step_group_limit
FROM
igs_tr_step_grp_lmt
WHERE
tracking_id = p_tracking_id
AND
step_group_id = p_step_group_id;
igs_tr_step_grp_lmt_pkg.delete_row ( X_ROWID => lv_rowid );
FOR rec_insert_step_grp_lmt IN c_insert_step_grp_lmt LOOP
igs_tr_step_grp_lmt_pkg.insert_row (
X_ROWID => lv_rowid ,
X_TRACKING_ID => p_tracking_id ,
X_STEP_GROUP_ID => rec_insert_step_grp_lmt.step_group_id ,
X_STEP_GROUP_LIMIT => 1 ,
X_MODE => 'R'
);
igs_tr_step_grp_lmt_pkg.update_row (
X_ROWID => lv_rowid ,
X_TRACKING_ID => p_tracking_id ,
X_STEP_GROUP_ID => rec_item_step.step_group_id ,
X_STEP_GROUP_LIMIT => rec_item_step.step_group_count ,
X_MODE => 'R'
);
SELECT
tracking_id,
tracking_step_number,
step_group_id
FROM
igs_tr_step
WHERE
tracking_id = p_tracking_id
AND step_completion_ind = 'Y';
SELECT
DISTINCT TO_NUMBER('1'),
1
FROM
igs_tr_step trst
WHERE
trst.tracking_id = p_tracking_id
AND
trst.step_group_id IS NULL
AND
trst.step_completion_ind = 'N'
AND
trst.by_pass_ind = 'N'
UNION
(
SELECT tab.step_group_id , tab.count_step
FROM (
SELECT
DISTINCT trst.step_group_id step_group_id,
COUNT(*) count_step
FROM
igs_tr_step trst
WHERE
trst.tracking_id = p_tracking_id
AND
trst.step_group_id IS NOT NULL
AND
(trst.step_completion_ind = 'Y'
OR
trst.by_pass_ind = 'Y')
GROUP BY
trst.step_group_id
-- trst.step_completion_ind
MINUS
SELECT
DISTINCT trgl.step_group_id step_group_id,
step_group_limit count_step
FROM
igs_tr_step_grp_lmt trgl
WHERE
trgl.tracking_id = p_tracking_id
) tab,
igs_tr_step_grp_lmt trg
WHERE tab.step_group_id = trg.step_group_id
AND
trg.tracking_id = p_tracking_id
AND
tab.count_step < trg.step_group_limit
);