eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_TR_GEN_002 dependencies on IGS_TR_STEP

Line 57: FROM igs_tr_step_note

53: DECLARE
54:
55: CURSOR c_del_tsn IS
56: SELECT ROWID,reference_number
57: FROM igs_tr_step_note
58: WHERE tracking_id = lp_tracking_id
59: FOR UPDATE OF tracking_id NOWAIT;
60:
61: BEGIN

Line 65: igs_tr_step_note_pkg.delete_row(x_rowid => v_tsn_rec.ROWID);

61: BEGIN
62:
63: FOR v_tsn_rec IN c_del_tsn LOOP
64:
65: igs_tr_step_note_pkg.delete_row(x_rowid => v_tsn_rec.ROWID);
66: IF igs_ge_gen_001.genp_del_note( v_tsn_rec.reference_number, v_message_name) = FALSE THEN
67: p_message_name := v_message_name;
68: EXIT;
69: END IF;

Line 112: SELECT igs_tr_step.*,ROWID

108:
109: DECLARE
110:
111: CURSOR c_del_ts IS
112: SELECT igs_tr_step.*,ROWID
113: FROM igs_tr_step
114: WHERE tracking_id = lp_tracking_id
115: FOR UPDATE OF tracking_id NOWAIT;
116:

Line 113: FROM igs_tr_step

109: DECLARE
110:
111: CURSOR c_del_ts IS
112: SELECT igs_tr_step.*,ROWID
113: FROM igs_tr_step
114: WHERE tracking_id = lp_tracking_id
115: FOR UPDATE OF tracking_id NOWAIT;
116:
117: BEGIN

Line 120: igs_tr_step_pkg.delete_row( x_rowid => v_ts_rec.ROWID);

116:
117: BEGIN
118:
119: FOR v_ts_rec IN c_del_ts LOOP
120: igs_tr_step_pkg.delete_row( x_rowid => v_ts_rec.ROWID);
121: END LOOP;
122: RETURN TRUE;
123:
124: END;

Line 128: p_message_name := 'IGS_TR_STEP_RECORD_LOCKED';

124: END;
125:
126: EXCEPTION
127: WHEN e_resource_busy THEN
128: p_message_name := 'IGS_TR_STEP_RECORD_LOCKED';
129: RETURN FALSE;
130:
131: WHEN OTHERS THEN
132: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');

Line 284: -- 6. Delete step group limit from IGS_TR_STEP_GRP_LMT

280: END trkpl_del_tri;
281:
282:
283: ------------------------------------------------------------------------------
284: -- 6. Delete step group limit from IGS_TR_STEP_GRP_LMT
285: -- Function added by pradhakr as part of CCR for Tracking in Applicant
286: -- Self Service Part 3.
287: ------------------------------------------------------------------------------
288: FUNCTION trkpl_del_grp_lmt(

Line 289: lp_tracking_id igs_tr_step_grp_lmt.tracking_id%TYPE )

285: -- Function added by pradhakr as part of CCR for Tracking in Applicant
286: -- Self Service Part 3.
287: ------------------------------------------------------------------------------
288: FUNCTION trkpl_del_grp_lmt(
289: lp_tracking_id igs_tr_step_grp_lmt.tracking_id%TYPE )
290: RETURN BOOLEAN AS
291: lv_param_values VARCHAR2(1080);
292: BEGIN
293:

Line 298: FROM igs_tr_step_grp_lmt

294: DECLARE
295:
296: CURSOR c_del_grp_lmt IS
297: SELECT ROWID
298: FROM igs_tr_step_grp_lmt
299: WHERE tracking_id = lp_tracking_id
300: FOR UPDATE OF tracking_id NOWAIT;
301:
302: BEGIN

Line 305: igs_tr_step_grp_lmt_pkg.delete_row( x_rowid => rec_del_grp_lmt.rowid);

301:
302: BEGIN
303:
304: FOR rec_del_grp_lmt IN c_del_grp_lmt LOOP
305: igs_tr_step_grp_lmt_pkg.delete_row( x_rowid => rec_del_grp_lmt.rowid);
306: END LOOP;
307:
308: RETURN TRUE;
309: END;

Line 338: -- Deleting step group limit from IGS_TR_STEP_GRP_LMT

334: IF ( -- Delete tracking_step_note
335: trkpl_del_tsn(p_tracking_id) = FALSE OR
336: -- Delete tracking_step
337: trkpl_del_ts(p_tracking_id) = FALSE OR
338: -- Deleting step group limit from IGS_TR_STEP_GRP_LMT
339: trkpl_del_grp_lmt(p_tracking_id) = FALSE OR
340: -- Delete tracking_group_member
341: trkpl_del_tgm(p_tracking_id) = FALSE OR
342: -- Delete tracking_item_note

Line 513: existing IGS_GE_NOTE records and insert IGS_TR_STEP_NOTES

509:
510: Purpose:
511: 1. This program unit is used to insert the default tracking_steps for an item
512: based on the IGS_TR_TYPE and its associated tracking_type_steps, duplicate
513: existing IGS_GE_NOTE records and insert IGS_TR_STEP_NOTES
514:
515: Usage: (e.g. restricted, unrestricted, where to call from)
516: 1. Called from IGSTR007.FMB upon creation of a tracking item.
517:

Line 578: -- This procedure insert a IGS_TR_STEP record

574: FROM igs_tr_tstp_grp_lmt
575: WHERE tracking_type = cp_tracking_type;
576:
577:
578: -- This procedure insert a IGS_TR_STEP record
579: PROCEDURE trkpl_ins_ts_rec (
580: p_tracking_id igs_tr_step.tracking_id%TYPE,
581: p_tracking_step_id igs_tr_step.tracking_step_id%TYPE,
582: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,

Line 580: p_tracking_id igs_tr_step.tracking_id%TYPE,

576:
577:
578: -- This procedure insert a IGS_TR_STEP record
579: PROCEDURE trkpl_ins_ts_rec (
580: p_tracking_id igs_tr_step.tracking_id%TYPE,
581: p_tracking_step_id igs_tr_step.tracking_step_id%TYPE,
582: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
583: p_description igs_tr_step.description%TYPE,
584: p_completion_dt igs_tr_step.completion_dt%TYPE,

Line 581: p_tracking_step_id igs_tr_step.tracking_step_id%TYPE,

577:
578: -- This procedure insert a IGS_TR_STEP record
579: PROCEDURE trkpl_ins_ts_rec (
580: p_tracking_id igs_tr_step.tracking_id%TYPE,
581: p_tracking_step_id igs_tr_step.tracking_step_id%TYPE,
582: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
583: p_description igs_tr_step.description%TYPE,
584: p_completion_dt igs_tr_step.completion_dt%TYPE,
585: p_action_days igs_tr_step.action_days%TYPE,

Line 582: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,

578: -- This procedure insert a IGS_TR_STEP record
579: PROCEDURE trkpl_ins_ts_rec (
580: p_tracking_id igs_tr_step.tracking_id%TYPE,
581: p_tracking_step_id igs_tr_step.tracking_step_id%TYPE,
582: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
583: p_description igs_tr_step.description%TYPE,
584: p_completion_dt igs_tr_step.completion_dt%TYPE,
585: p_action_days igs_tr_step.action_days%TYPE,
586: p_step_completion_ind igs_tr_step.step_completion_ind%TYPE,

Line 583: p_description igs_tr_step.description%TYPE,

579: PROCEDURE trkpl_ins_ts_rec (
580: p_tracking_id igs_tr_step.tracking_id%TYPE,
581: p_tracking_step_id igs_tr_step.tracking_step_id%TYPE,
582: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
583: p_description igs_tr_step.description%TYPE,
584: p_completion_dt igs_tr_step.completion_dt%TYPE,
585: p_action_days igs_tr_step.action_days%TYPE,
586: p_step_completion_ind igs_tr_step.step_completion_ind%TYPE,
587: p_by_pass_ind igs_tr_step.by_pass_ind%TYPE,

Line 584: p_completion_dt igs_tr_step.completion_dt%TYPE,

580: p_tracking_id igs_tr_step.tracking_id%TYPE,
581: p_tracking_step_id igs_tr_step.tracking_step_id%TYPE,
582: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
583: p_description igs_tr_step.description%TYPE,
584: p_completion_dt igs_tr_step.completion_dt%TYPE,
585: p_action_days igs_tr_step.action_days%TYPE,
586: p_step_completion_ind igs_tr_step.step_completion_ind%TYPE,
587: p_by_pass_ind igs_tr_step.by_pass_ind%TYPE,
588: p_recipient_id igs_tr_step.recipient_id%TYPE,

Line 585: p_action_days igs_tr_step.action_days%TYPE,

581: p_tracking_step_id igs_tr_step.tracking_step_id%TYPE,
582: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
583: p_description igs_tr_step.description%TYPE,
584: p_completion_dt igs_tr_step.completion_dt%TYPE,
585: p_action_days igs_tr_step.action_days%TYPE,
586: p_step_completion_ind igs_tr_step.step_completion_ind%TYPE,
587: p_by_pass_ind igs_tr_step.by_pass_ind%TYPE,
588: p_recipient_id igs_tr_step.recipient_id%TYPE,
589: p_s_tracking_step_type igs_tr_type_step.s_tracking_step_type%TYPE,

Line 586: p_step_completion_ind igs_tr_step.step_completion_ind%TYPE,

582: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
583: p_description igs_tr_step.description%TYPE,
584: p_completion_dt igs_tr_step.completion_dt%TYPE,
585: p_action_days igs_tr_step.action_days%TYPE,
586: p_step_completion_ind igs_tr_step.step_completion_ind%TYPE,
587: p_by_pass_ind igs_tr_step.by_pass_ind%TYPE,
588: p_recipient_id igs_tr_step.recipient_id%TYPE,
589: p_s_tracking_step_type igs_tr_type_step.s_tracking_step_type%TYPE,
590: p_step_catalog_cd igs_tr_type_step.step_catalog_cd%TYPE,

Line 587: p_by_pass_ind igs_tr_step.by_pass_ind%TYPE,

583: p_description igs_tr_step.description%TYPE,
584: p_completion_dt igs_tr_step.completion_dt%TYPE,
585: p_action_days igs_tr_step.action_days%TYPE,
586: p_step_completion_ind igs_tr_step.step_completion_ind%TYPE,
587: p_by_pass_ind igs_tr_step.by_pass_ind%TYPE,
588: p_recipient_id igs_tr_step.recipient_id%TYPE,
589: p_s_tracking_step_type igs_tr_type_step.s_tracking_step_type%TYPE,
590: p_step_catalog_cd igs_tr_type_step.step_catalog_cd%TYPE,
591: p_step_group_id igs_tr_type_step.step_group_id%TYPE,

Line 588: p_recipient_id igs_tr_step.recipient_id%TYPE,

584: p_completion_dt igs_tr_step.completion_dt%TYPE,
585: p_action_days igs_tr_step.action_days%TYPE,
586: p_step_completion_ind igs_tr_step.step_completion_ind%TYPE,
587: p_by_pass_ind igs_tr_step.by_pass_ind%TYPE,
588: p_recipient_id igs_tr_step.recipient_id%TYPE,
589: p_s_tracking_step_type igs_tr_type_step.s_tracking_step_type%TYPE,
590: p_step_catalog_cd igs_tr_type_step.step_catalog_cd%TYPE,
591: p_step_group_id igs_tr_type_step.step_group_id%TYPE,
592: p_publish_ind igs_tr_type_step.publish_ind%TYPE)

Line 601: igs_tr_step_pkg.insert_row (

597: p_rowid VARCHAR2(25);
598:
599: BEGIN
600:
601: igs_tr_step_pkg.insert_row (
602: x_rowid => p_rowid,
603: x_tracking_id => p_tracking_id,
604: x_tracking_step_id => p_tracking_step_id ,
605: x_tracking_step_number => p_tracking_step_number,

Line 628: IF l_message_name = 'IGS_TR_STEP_CTLG_CLOSED' THEN

624: l_message_name VARCHAR2(30);
625: l_app VARCHAR2(50);
626: BEGIN
627: FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
628: IF l_message_name = 'IGS_TR_STEP_CTLG_CLOSED' THEN
629: fnd_message.set_name('IGS',l_message_name);
630: igs_ge_msg_stack.ADD;
631: app_exception.raise_exception;
632: ELSE

Line 708: -- Insert IGS_TR_STEP_NOTE record

704: app_exception.raise_exception;
705:
706: END trkpl_dup_note_rec;
707:
708: -- Insert IGS_TR_STEP_NOTE record
709: PROCEDURE trkpl_ins_tsn_rec(
710: p_tracking_id igs_tr_step_note.tracking_id%TYPE,
711: p_tracking_step_id igs_tr_step_note.tracking_step_id%TYPE,
712: p_reference_number igs_tr_step_note.reference_number%TYPE,

Line 710: p_tracking_id igs_tr_step_note.tracking_id%TYPE,

706: END trkpl_dup_note_rec;
707:
708: -- Insert IGS_TR_STEP_NOTE record
709: PROCEDURE trkpl_ins_tsn_rec(
710: p_tracking_id igs_tr_step_note.tracking_id%TYPE,
711: p_tracking_step_id igs_tr_step_note.tracking_step_id%TYPE,
712: p_reference_number igs_tr_step_note.reference_number%TYPE,
713: p_trk_note_type igs_tr_step_note.trk_note_type%TYPE)
714: AS

Line 711: p_tracking_step_id igs_tr_step_note.tracking_step_id%TYPE,

707:
708: -- Insert IGS_TR_STEP_NOTE record
709: PROCEDURE trkpl_ins_tsn_rec(
710: p_tracking_id igs_tr_step_note.tracking_id%TYPE,
711: p_tracking_step_id igs_tr_step_note.tracking_step_id%TYPE,
712: p_reference_number igs_tr_step_note.reference_number%TYPE,
713: p_trk_note_type igs_tr_step_note.trk_note_type%TYPE)
714: AS
715:

Line 712: p_reference_number igs_tr_step_note.reference_number%TYPE,

708: -- Insert IGS_TR_STEP_NOTE record
709: PROCEDURE trkpl_ins_tsn_rec(
710: p_tracking_id igs_tr_step_note.tracking_id%TYPE,
711: p_tracking_step_id igs_tr_step_note.tracking_step_id%TYPE,
712: p_reference_number igs_tr_step_note.reference_number%TYPE,
713: p_trk_note_type igs_tr_step_note.trk_note_type%TYPE)
714: AS
715:
716: lv_param_values VARCHAR2(1080);

Line 713: p_trk_note_type igs_tr_step_note.trk_note_type%TYPE)

709: PROCEDURE trkpl_ins_tsn_rec(
710: p_tracking_id igs_tr_step_note.tracking_id%TYPE,
711: p_tracking_step_id igs_tr_step_note.tracking_step_id%TYPE,
712: p_reference_number igs_tr_step_note.reference_number%TYPE,
713: p_trk_note_type igs_tr_step_note.trk_note_type%TYPE)
714: AS
715:
716: lv_param_values VARCHAR2(1080);
717: p_rowid VARCHAR2(25);

Line 721: igs_tr_step_note_pkg.insert_row(

717: p_rowid VARCHAR2(25);
718:
719: BEGIN
720:
721: igs_tr_step_note_pkg.insert_row(
722: x_rowid => p_rowid,
723: x_tracking_id => p_tracking_id,
724: x_tracking_step_id => p_tracking_step_id,
725: x_reference_number => p_reference_number,

Line 750: p_tracking_id IN igs_tr_step_grp_lmt.tracking_id%TYPE,

746:
747: -- API to default the tracking steps for item based on the Tracking Type
748: -- Added by pradhakr
749: PROCEDURE trkpl_ins_ts_grp_lmt (
750: p_tracking_id IN igs_tr_step_grp_lmt.tracking_id%TYPE,
751: p_step_group_id IN igs_tr_step_grp_lmt.step_group_id%TYPE,
752: p_step_group_limit IN igs_tr_step_grp_lmt.step_group_limit%TYPE
753: ) AS
754:

Line 751: p_step_group_id IN igs_tr_step_grp_lmt.step_group_id%TYPE,

747: -- API to default the tracking steps for item based on the Tracking Type
748: -- Added by pradhakr
749: PROCEDURE trkpl_ins_ts_grp_lmt (
750: p_tracking_id IN igs_tr_step_grp_lmt.tracking_id%TYPE,
751: p_step_group_id IN igs_tr_step_grp_lmt.step_group_id%TYPE,
752: p_step_group_limit IN igs_tr_step_grp_lmt.step_group_limit%TYPE
753: ) AS
754:
755: lv_param_values VARCHAR2(1080);

Line 752: p_step_group_limit IN igs_tr_step_grp_lmt.step_group_limit%TYPE

748: -- Added by pradhakr
749: PROCEDURE trkpl_ins_ts_grp_lmt (
750: p_tracking_id IN igs_tr_step_grp_lmt.tracking_id%TYPE,
751: p_step_group_id IN igs_tr_step_grp_lmt.step_group_id%TYPE,
752: p_step_group_limit IN igs_tr_step_grp_lmt.step_group_limit%TYPE
753: ) AS
754:
755: lv_param_values VARCHAR2(1080);
756: l_rowid VARCHAR2(25);

Line 760: IGS_TR_STEP_GRP_LMT_PKG.INSERT_ROW (

756: l_rowid VARCHAR2(25);
757:
758: BEGIN
759:
760: IGS_TR_STEP_GRP_LMT_PKG.INSERT_ROW (
761: X_ROWID => l_rowid,
762: X_TRACKING_ID => p_tracking_id,
763: X_STEP_GROUP_ID => p_step_group_id,
764: X_STEP_GROUP_LIMIT => p_step_group_limit

Line 815: -- For each record found, create a new IGS_TR_STEP record

811: IF (NVL(v_override_offset_clc_ind,'N') = 'Y') THEN
812: v_tts_rec.action_days := v_completion_due_dt - v_start_dt;
813: END IF;
814:
815: -- For each record found, create a new IGS_TR_STEP record
816:
817: trkpl_ins_ts_rec (
818: p_tracking_id,
819: v_tts_rec.tracking_type_step_id,

Line 839: -- then create a new IGS_TR_STEP_NOTE

835:
836: FOR v_ttsn_rec IN c_tracking_type_step_note ( v_tts_rec.tracking_type, v_tts_rec.tracking_type_step_id) LOOP
837:
838: trkpl_dup_note_rec(v_ttsn_rec.reference_number, v_new_reference_number);
839: -- then create a new IGS_TR_STEP_NOTE
840:
841: trkpl_ins_tsn_rec(
842: p_tracking_id,
843: v_tts_rec.tracking_type_step_id,

Line 852: -- If any tracking group limit exists then insert into IGS_TR_STEP_GRP_LMT

848: END LOOP;
849:
850: END LOOP;
851:
852: -- If any tracking group limit exists then insert into IGS_TR_STEP_GRP_LMT
853: FOR rec_tracking_type_step_grplmt IN c_tracking_type_step_grplmt(v_tracking_type) LOOP
854: trkpl_ins_ts_grp_lmt (p_tracking_id,
855: rec_tracking_type_step_grplmt.step_group_id,
856: rec_tracking_type_step_grplmt.step_group_limit);

Line 1111: p_tracking_id igs_tr_step.tracking_id%TYPE,

1107:
1108: END trkp_ins_trk_item;
1109:
1110: FUNCTION trkp_next_step_cmplt (
1111: p_tracking_id igs_tr_step.tracking_id%TYPE,
1112: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1113: p_step_group_id igs_tr_step.step_group_id%TYPE,
1114: p_message_name OUT NOCOPY VARCHAR2
1115: ) RETURN BOOLEAN IS

Line 1112: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,

1108: END trkp_ins_trk_item;
1109:
1110: FUNCTION trkp_next_step_cmplt (
1111: p_tracking_id igs_tr_step.tracking_id%TYPE,
1112: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1113: p_step_group_id igs_tr_step.step_group_id%TYPE,
1114: p_message_name OUT NOCOPY VARCHAR2
1115: ) RETURN BOOLEAN IS
1116: ------------------------------------------------------------------

Line 1113: p_step_group_id igs_tr_step.step_group_id%TYPE,

1109:
1110: FUNCTION trkp_next_step_cmplt (
1111: p_tracking_id igs_tr_step.tracking_id%TYPE,
1112: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1113: p_step_group_id igs_tr_step.step_group_id%TYPE,
1114: p_message_name OUT NOCOPY VARCHAR2
1115: ) RETURN BOOLEAN IS
1116: ------------------------------------------------------------------
1117: --Created by : Apex, Oracle IDC (ssanyal.in)

Line 1146: FROM igs_tr_step

1142:
1143: -- ssawhney, table reference included instead of view
1144: CURSOR c_trst_all IS
1145: SELECT *
1146: FROM igs_tr_step
1147: WHERE tracking_id = p_tracking_id
1148: ORDER BY tracking_step_number;
1149:
1150: CURSOR c_trst_later IS

Line 1152: FROM igs_Tr_step

1148: ORDER BY tracking_step_number;
1149:
1150: CURSOR c_trst_later IS
1151: SELECT *
1152: FROM igs_Tr_step
1153: WHERE tracking_id = p_tracking_id
1154: AND tracking_step_number > p_tracking_step_number
1155: ORDER BY tracking_step_number;
1156:

Line 1234: p_tracking_id igs_tr_step.tracking_id%TYPE,

1230: app_exception.raise_exception;
1231: END trkp_next_step_cmplt;
1232:
1233: FUNCTION trkp_next_grp_step_cmplt (
1234: p_tracking_id igs_tr_step.tracking_id%TYPE,
1235: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1236: p_step_group_id igs_tr_step.step_group_id%TYPE,
1237: p_message_name OUT NOCOPY VARCHAR2
1238: ) RETURN BOOLEAN IS

Line 1235: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,

1231: END trkp_next_step_cmplt;
1232:
1233: FUNCTION trkp_next_grp_step_cmplt (
1234: p_tracking_id igs_tr_step.tracking_id%TYPE,
1235: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1236: p_step_group_id igs_tr_step.step_group_id%TYPE,
1237: p_message_name OUT NOCOPY VARCHAR2
1238: ) RETURN BOOLEAN IS
1239: ------------------------------------------------------------------

Line 1236: p_step_group_id igs_tr_step.step_group_id%TYPE,

1232:
1233: FUNCTION trkp_next_grp_step_cmplt (
1234: p_tracking_id igs_tr_step.tracking_id%TYPE,
1235: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1236: p_step_group_id igs_tr_step.step_group_id%TYPE,
1237: p_message_name OUT NOCOPY VARCHAR2
1238: ) RETURN BOOLEAN IS
1239: ------------------------------------------------------------------
1240: --Created by : Apex, Oracle IDC (ssanyal.in)

Line 1267: FROM igs_tr_step

1263:
1264: --ssawhney, view reference changed to table.
1265: CURSOR c_trst_later IS
1266: SELECT *
1267: FROM igs_tr_step
1268: WHERE tracking_id = p_tracking_id
1269: AND tracking_step_number > p_tracking_step_number
1270: ORDER BY tracking_step_number;
1271:

Line 1274: FROM igs_tr_step

1270: ORDER BY tracking_step_number;
1271:
1272: CURSOR c_trst_all IS
1273: SELECT *
1274: FROM igs_tr_step
1275: WHERE tracking_id = p_tracking_id
1276: ORDER BY tracking_step_number;
1277:
1278: CURSOR c_trst_next(p_tracking_step_number NUMBER) IS

Line 1280: FROM igs_tr_step

1276: ORDER BY tracking_step_number;
1277:
1278: CURSOR c_trst_next(p_tracking_step_number NUMBER) IS
1279: SELECT *
1280: FROM igs_tr_step
1281: WHERE tracking_id = p_tracking_id
1282: AND tracking_step_number > p_tracking_step_number
1283: ORDER BY tracking_step_number;
1284:

Line 1358: p_tracking_id IN igs_tr_step.tracking_id%TYPE,

1354: END trkp_next_grp_step_cmplt;
1355:
1356:
1357: FUNCTION trkp_check_step_group_limit (
1358: p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1359: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1360: p_step_group_id IN igs_tr_step.step_group_id%TYPE,
1361: p_message_name OUT NOCOPY VARCHAR2
1362: ) RETURN BOOLEAN IS

Line 1359: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,

1355:
1356:
1357: FUNCTION trkp_check_step_group_limit (
1358: p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1359: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1360: p_step_group_id IN igs_tr_step.step_group_id%TYPE,
1361: p_message_name OUT NOCOPY VARCHAR2
1362: ) RETURN BOOLEAN IS
1363: ------------------------------------------------------------------

Line 1360: p_step_group_id IN igs_tr_step.step_group_id%TYPE,

1356:
1357: FUNCTION trkp_check_step_group_limit (
1358: p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1359: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1360: p_step_group_id IN igs_tr_step.step_group_id%TYPE,
1361: p_message_name OUT NOCOPY VARCHAR2
1362: ) RETURN BOOLEAN IS
1363: ------------------------------------------------------------------
1364: --Created by : pradhakr, Oracle IDC

Line 1380: -- comparing the result with the one in the table IGS_TR_STEP_GRP_LMT

1376: lv_param_values VARCHAR2(1080);
1377:
1378: -- Cursor to get all step group ids and the number of completed steps prior to the
1379: -- passed step group id (p_step_group_id) and tracking step number (p_ tracking_step_number) and
1380: -- comparing the result with the one in the table IGS_TR_STEP_GRP_LMT
1381:
1382: CURSOR c_grp_limit IS
1383: SELECT tab.step_group_id, tab.step_group_limit
1384: FROM

Line 1386: FROM igs_tr_step trst

1382: CURSOR c_grp_limit IS
1383: SELECT tab.step_group_id, tab.step_group_limit
1384: FROM
1385: ( SELECT step_group_id, count(*) step_group_limit
1386: FROM igs_tr_step trst
1387: WHERE trst.tracking_id = p_tracking_id
1388: AND trst.tracking_step_number < p_tracking_step_number
1389: AND trst.step_group_id is not null
1390: AND trst.step_group_id <> p_step_group_id

Line 1396: FROM igs_tr_step_grp_lmt

1392: AND trst.by_pass_ind = 'N'
1393: GROUP BY trst.step_group_id, trst.step_completion_ind
1394: MINUS
1395: SELECT step_group_id, step_group_limit
1396: FROM igs_tr_step_grp_lmt
1397: WHERE tracking_id = p_tracking_id
1398: ) tab ,
1399: igs_tr_step_grp_lmt trg
1400: WHERE tab.step_group_id = trg.step_group_id

Line 1399: igs_tr_step_grp_lmt trg

1395: SELECT step_group_id, step_group_limit
1396: FROM igs_tr_step_grp_lmt
1397: WHERE tracking_id = p_tracking_id
1398: ) tab ,
1399: igs_tr_step_grp_lmt trg
1400: WHERE tab.step_group_id = trg.step_group_id
1401: AND trg.tracking_id = p_tracking_id
1402: AND tab.step_group_limit < trg.step_group_limit ;
1403:

Line 1412: p_message_name := 'IGS_TR_STEP_GRPLMT_VIOLATE';

1408: OPEN c_grp_limit;
1409: FETCH c_grp_limit INTO l_grp_limit;
1410: -- If record exists, it means that some of the step group limit has not been met for the step group id.
1411: IF c_grp_limit%FOUND THEN
1412: p_message_name := 'IGS_TR_STEP_GRPLMT_VIOLATE';
1413: RETURN FALSE;
1414: ELSE
1415: RETURN TRUE;
1416: END IF;

Line 1434: p_tracking_id igs_tr_step.tracking_id%TYPE,

1430: END trkp_check_step_group_limit;
1431:
1432:
1433: FUNCTION trkp_prev_step_cmplt (
1434: p_tracking_id igs_tr_step.tracking_id%TYPE,
1435: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1436: p_step_group_id igs_tr_step.step_group_id%TYPE,
1437: p_message_name OUT NOCOPY VARCHAR2
1438: ) RETURN BOOLEAN IS

Line 1435: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,

1431:
1432:
1433: FUNCTION trkp_prev_step_cmplt (
1434: p_tracking_id igs_tr_step.tracking_id%TYPE,
1435: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1436: p_step_group_id igs_tr_step.step_group_id%TYPE,
1437: p_message_name OUT NOCOPY VARCHAR2
1438: ) RETURN BOOLEAN IS
1439: ------------------------------------------------------------------

Line 1436: p_step_group_id igs_tr_step.step_group_id%TYPE,

1432:
1433: FUNCTION trkp_prev_step_cmplt (
1434: p_tracking_id igs_tr_step.tracking_id%TYPE,
1435: p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1436: p_step_group_id igs_tr_step.step_group_id%TYPE,
1437: p_message_name OUT NOCOPY VARCHAR2
1438: ) RETURN BOOLEAN IS
1439: ------------------------------------------------------------------
1440: --Created by : Apex, Oracle IDC (ssanyal.in)

Line 1457: FROM igs_tr_step trst

1453:
1454:
1455: CURSOR c_earlier_step_trst IS
1456: SELECT DISTINCT TO_NUMBER('1')
1457: FROM igs_tr_step trst
1458: WHERE trst.tracking_id = p_tracking_id
1459: AND trst.tracking_step_number < p_tracking_step_number
1460: AND trst.step_group_id IS NULL
1461: AND trst.step_completion_ind = 'N'

Line 1466: FROM igs_tr_step trst

1462: AND trst.by_pass_ind = 'N'
1463: UNION
1464: (
1465: SELECT DISTINCT trst.step_group_id
1466: FROM igs_tr_step trst
1467: WHERE trst.tracking_id = p_tracking_id
1468: AND trst.tracking_step_number < p_tracking_step_number
1469: AND trst.step_group_id IS NOT NULL
1470: AND trst.step_completion_ind = 'N'

Line 1475: FROM igs_tr_step trst

1471: AND trst.by_pass_ind = 'N'
1472: GROUP BY trst.step_group_id, trst.step_completion_ind
1473: MINUS
1474: SELECT DISTINCT trst.step_group_id
1475: FROM igs_tr_step trst
1476: WHERE trst.tracking_id = p_tracking_id
1477: AND trst.tracking_step_number < p_tracking_step_number
1478: AND trst.step_group_id IS NOT NULL
1479: AND trst.step_completion_ind = 'Y'

Line 1538: a IGS_TR_STEP record.

1534: Date created:
1535:
1536: Purpose:
1537: 1. This module will update fields of the action days of
1538: a IGS_TR_STEP record.
1539: Usage: (e.g. restricted, unrestricted, where to call from)
1540: 1. Called from IGSTR007.FMB
1541:
1542: Known limitations/enhancements/remarks:

Line 1569: -- a IGS_TR_STEP record.

1565: BEGIN
1566:
1567: -- trkp_upd_trst
1568: -- This module will update fields of the action days of
1569: -- a IGS_TR_STEP record.
1570:
1571: DECLARE
1572:
1573: e_resource_busy_exception EXCEPTION;

Line 1577: v_tracking_step_id igs_tr_step.tracking_step_id%TYPE;

1573: e_resource_busy_exception EXCEPTION;
1574:
1575: PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
1576:
1577: v_tracking_step_id igs_tr_step.tracking_step_id%TYPE;
1578: v_action_days NUMBER;
1579: v_completion_dt igs_tr_step.completion_dt%TYPE;
1580: v_step_completion_ind igs_tr_step.step_completion_ind%TYPE;
1581: v_by_pass_ind igs_tr_step.by_pass_ind%TYPE;

Line 1579: v_completion_dt igs_tr_step.completion_dt%TYPE;

1575: PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
1576:
1577: v_tracking_step_id igs_tr_step.tracking_step_id%TYPE;
1578: v_action_days NUMBER;
1579: v_completion_dt igs_tr_step.completion_dt%TYPE;
1580: v_step_completion_ind igs_tr_step.step_completion_ind%TYPE;
1581: v_by_pass_ind igs_tr_step.by_pass_ind%TYPE;
1582: v_recipient_id igs_tr_step.recipient_id%TYPE;
1583: v_start_dt igs_tr_item.start_dt%TYPE;

Line 1580: v_step_completion_ind igs_tr_step.step_completion_ind%TYPE;

1576:
1577: v_tracking_step_id igs_tr_step.tracking_step_id%TYPE;
1578: v_action_days NUMBER;
1579: v_completion_dt igs_tr_step.completion_dt%TYPE;
1580: v_step_completion_ind igs_tr_step.step_completion_ind%TYPE;
1581: v_by_pass_ind igs_tr_step.by_pass_ind%TYPE;
1582: v_recipient_id igs_tr_step.recipient_id%TYPE;
1583: v_start_dt igs_tr_item.start_dt%TYPE;
1584: v_business_days_ind igs_tr_item.business_days_ind%TYPE;

Line 1581: v_by_pass_ind igs_tr_step.by_pass_ind%TYPE;

1577: v_tracking_step_id igs_tr_step.tracking_step_id%TYPE;
1578: v_action_days NUMBER;
1579: v_completion_dt igs_tr_step.completion_dt%TYPE;
1580: v_step_completion_ind igs_tr_step.step_completion_ind%TYPE;
1581: v_by_pass_ind igs_tr_step.by_pass_ind%TYPE;
1582: v_recipient_id igs_tr_step.recipient_id%TYPE;
1583: v_start_dt igs_tr_item.start_dt%TYPE;
1584: v_business_days_ind igs_tr_item.business_days_ind%TYPE;
1585: v_sequence_ind igs_tr_item.sequence_ind%TYPE;

Line 1582: v_recipient_id igs_tr_step.recipient_id%TYPE;

1578: v_action_days NUMBER;
1579: v_completion_dt igs_tr_step.completion_dt%TYPE;
1580: v_step_completion_ind igs_tr_step.step_completion_ind%TYPE;
1581: v_by_pass_ind igs_tr_step.by_pass_ind%TYPE;
1582: v_recipient_id igs_tr_step.recipient_id%TYPE;
1583: v_start_dt igs_tr_item.start_dt%TYPE;
1584: v_business_days_ind igs_tr_item.business_days_ind%TYPE;
1585: v_sequence_ind igs_tr_item.sequence_ind%TYPE;
1586: v_step_completion_ind_temp igs_tr_step.step_completion_ind%TYPE;

Line 1586: v_step_completion_ind_temp igs_tr_step.step_completion_ind%TYPE;

1582: v_recipient_id igs_tr_step.recipient_id%TYPE;
1583: v_start_dt igs_tr_item.start_dt%TYPE;
1584: v_business_days_ind igs_tr_item.business_days_ind%TYPE;
1585: v_sequence_ind igs_tr_item.sequence_ind%TYPE;
1586: v_step_completion_ind_temp igs_tr_step.step_completion_ind%TYPE;
1587: v_action_dt igs_tr_step_v.action_dt%TYPE;
1588: v_message_name VARCHAR2(30);
1589: v_rowid VARCHAR2(25);
1590: v_step_group_id igs_tr_step.step_group_id%TYPE;

Line 1587: v_action_dt igs_tr_step_v.action_dt%TYPE;

1583: v_start_dt igs_tr_item.start_dt%TYPE;
1584: v_business_days_ind igs_tr_item.business_days_ind%TYPE;
1585: v_sequence_ind igs_tr_item.sequence_ind%TYPE;
1586: v_step_completion_ind_temp igs_tr_step.step_completion_ind%TYPE;
1587: v_action_dt igs_tr_step_v.action_dt%TYPE;
1588: v_message_name VARCHAR2(30);
1589: v_rowid VARCHAR2(25);
1590: v_step_group_id igs_tr_step.step_group_id%TYPE;
1591: v_tracking_step_number igs_tr_step.tracking_step_number%TYPE;

Line 1590: v_step_group_id igs_tr_step.step_group_id%TYPE;

1586: v_step_completion_ind_temp igs_tr_step.step_completion_ind%TYPE;
1587: v_action_dt igs_tr_step_v.action_dt%TYPE;
1588: v_message_name VARCHAR2(30);
1589: v_rowid VARCHAR2(25);
1590: v_step_group_id igs_tr_step.step_group_id%TYPE;
1591: v_tracking_step_number igs_tr_step.tracking_step_number%TYPE;
1592: l_field_mod VARCHAR2(10) := NULL;
1593: l_end_dt DATE;
1594: l_action_days igs_tr_step.action_days%TYPE;

Line 1591: v_tracking_step_number igs_tr_step.tracking_step_number%TYPE;

1587: v_action_dt igs_tr_step_v.action_dt%TYPE;
1588: v_message_name VARCHAR2(30);
1589: v_rowid VARCHAR2(25);
1590: v_step_group_id igs_tr_step.step_group_id%TYPE;
1591: v_tracking_step_number igs_tr_step.tracking_step_number%TYPE;
1592: l_field_mod VARCHAR2(10) := NULL;
1593: l_end_dt DATE;
1594: l_action_days igs_tr_step.action_days%TYPE;
1595: l_action_dt DATE;

Line 1594: l_action_days igs_tr_step.action_days%TYPE;

1590: v_step_group_id igs_tr_step.step_group_id%TYPE;
1591: v_tracking_step_number igs_tr_step.tracking_step_number%TYPE;
1592: l_field_mod VARCHAR2(10) := NULL;
1593: l_end_dt DATE;
1594: l_action_days igs_tr_step.action_days%TYPE;
1595: l_action_dt DATE;
1596:
1597: TYPE rec_step_group_id IS RECORD ( step_group_id NUMBER);
1598: TYPE tab_step_group_id IS TABLE OF

Line 1617: FROM igs_tr_step trst,

1613: --ssawhney cant change view reference..using view logic in select.
1614: CURSOR c_update_trst IS
1615: SELECT trst.*, trst.rowid ROW_ID, igs_tr_gen_001.trkp_clc_action_dt ( trst.tracking_id, trst.tracking_step_number,
1616: tri.start_dt, tri.sequence_ind,tri.business_days_ind ) action_dt
1617: FROM igs_tr_step trst,
1618: igs_tr_item tri
1619: WHERE trst.tracking_id = p_tracking_id
1620: AND trst.tracking_id = tri.tracking_id
1621: AND ((NVL(p_tracking_step_id, 0) = 0)

Line 1640: FROM igs_tr_step trst

1636:
1637: --ssawhney view reference changed to table.
1638: CURSOR c_check_item_cmpltn IS
1639: SELECT DISTINCT TO_NUMBER('1')
1640: FROM igs_tr_step trst
1641: WHERE trst.tracking_id = p_tracking_id
1642: AND trst.step_group_id IS NULL
1643: AND trst.step_completion_ind = 'N'
1644: AND trst.by_pass_ind = 'N'

Line 1648: FROM igs_tr_step a

1644: AND trst.by_pass_ind = 'N'
1645: UNION
1646: (
1647: SELECT distinct step_group_id
1648: FROM igs_tr_step a
1649: WHERE a.tracking_id = p_tracking_id
1650: AND a.step_group_id IS NOT NULL
1651: -- total no of groups for the tracking item
1652: MINUS

Line 1660: FROM igs_tr_step trst

1656: SELECT tab.step_group_id, tab.count_step
1657: FROM (
1658: SELECT trst.step_group_id step_group_id,
1659: COUNT(*) count_step
1660: FROM igs_tr_step trst
1661: WHERE trst.tracking_id = p_tracking_id
1662: AND trst.step_group_id IS NOT NULL
1663: AND ((trst.step_completion_ind = 'Y' AND trst.by_pass_ind = 'N') OR
1664: (trst.step_completion_ind = 'N' AND trst.by_pass_ind = 'Y'))

Line 1669: igs_tr_step_grp_lmt trg

1665: GROUP BY trst.step_group_id,
1666: trst.step_completion_ind
1667: ) tab,
1668: -- a step is considered complete if either its BY PASSED or its completion ind is Y
1669: igs_tr_step_grp_lmt trg
1670: WHERE tab.step_group_id = trg.step_group_id
1671: AND trg.tracking_id = p_tracking_id
1672: AND tab.count_step >= trg.step_group_limit
1673: -- subtract only if the total steps completed are less than the group limit.

Line 1683: igs_tr_step_rec c_update_trst%ROWTYPE;

1679: FROM igs_tr_status
1680: WHERE s_tracking_status = 'COMPLETE'
1681: AND default_ind = 'Y';
1682:
1683: igs_tr_step_rec c_update_trst%ROWTYPE;
1684: igs_tr_item_rec c_tri_all%ROWTYPE;
1685: l_sys_trk_sts igs_tr_status.s_tracking_status%TYPE;
1686: l_trk_sts igs_tr_status.tracking_status%TYPE;
1687: l_item_cmpl c_check_item_cmpltn%ROWTYPE;

Line 1692: p_tracking_id IN igs_tr_step.tracking_id%TYPE,

1688:
1689: -- validitate action / completion date and get action days
1690: FUNCTION process_date (
1691: p_date_type IN VARCHAR2,
1692: p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1693: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1694: p_end_dt IN OUT NOCOPY DATE,
1695: p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,
1696: p_message_name OUT NOCOPY VARCHAR2

Line 1693: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,

1689: -- validitate action / completion date and get action days
1690: FUNCTION process_date (
1691: p_date_type IN VARCHAR2,
1692: p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1693: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1694: p_end_dt IN OUT NOCOPY DATE,
1695: p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,
1696: p_message_name OUT NOCOPY VARCHAR2
1697: )RETURN BOOLEAN IS

Line 1695: p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,

1691: p_date_type IN VARCHAR2,
1692: p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1693: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1694: p_end_dt IN OUT NOCOPY DATE,
1695: p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,
1696: p_message_name OUT NOCOPY VARCHAR2
1697: )RETURN BOOLEAN IS
1698: --ssawhney view reference changed to table in the second part..first part still referes to the function for getting the dates.
1699:

Line 1703: FROM igs_tr_step trst, igs_tr_item tri

1699:
1700: CURSOR c_start_dt IS
1701: SELECT NVL(trst.completion_dt,igs_tr_gen_001.trkp_clc_action_dt ( trst.tracking_id, trst.tracking_step_number,
1702: tri.start_dt, tri.sequence_ind,tri.business_days_ind )) start_dt
1703: FROM igs_tr_step trst, igs_tr_item tri
1704: WHERE trst.tracking_id = tri.tracking_id
1705: AND trst.tracking_id = p_tracking_id
1706: AND trst.tracking_step_number = (SELECT MAX(b.tracking_step_number)
1707: FROM igs_tr_step b

Line 1707: FROM igs_tr_step b

1703: FROM igs_tr_step trst, igs_tr_item tri
1704: WHERE trst.tracking_id = tri.tracking_id
1705: AND trst.tracking_id = p_tracking_id
1706: AND trst.tracking_step_number = (SELECT MAX(b.tracking_step_number)
1707: FROM igs_tr_step b
1708: WHERE b.tracking_id = p_tracking_id
1709: AND b.tracking_step_number < p_tracking_step_number
1710: AND b.by_pass_ind = 'N');
1711:

Line 1716: FROM igs_tr_step trst,

1712: --ssawhney view reference changed to table and introduced fucntions.
1713: CURSOR c_upd_next_dt IS
1714: SELECT trst.*, trst.rowid ROW_ID, igs_tr_gen_001.trkp_clc_action_dt ( trst.tracking_id, trst.tracking_step_number,
1715: tri.start_dt, tri.sequence_ind,tri.business_days_ind ) action_dt
1716: FROM igs_tr_step trst,
1717: igs_tr_item tri
1718: WHERE trst.tracking_id = tri.tracking_id
1719: AND trst.tracking_id = p_tracking_id
1720: AND trst.tracking_step_number > p_tracking_step_number

Line 1725: l_action_days igs_tr_step.action_days%TYPE;

1721: FOR UPDATE OF trst.action_days NOWAIT;
1722:
1723: l_start_dt DATE;
1724: l_p_action_dt DATE;
1725: l_action_days igs_tr_step.action_days%TYPE;
1726: l_next_start_dt_diff NUMBER;
1727: l_next_start_dt DATE;
1728:
1729: FUNCTION validate_date (

Line 1731: p_tracking_id IN igs_tr_step.tracking_id%TYPE,

1727: l_next_start_dt DATE;
1728:
1729: FUNCTION validate_date (
1730: p_date_type IN VARCHAR2,
1731: p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1732: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1733: p_start_dt IN DATE,
1734: p_end_dt IN OUT NOCOPY DATE,
1735: p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,

Line 1732: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,

1728:
1729: FUNCTION validate_date (
1730: p_date_type IN VARCHAR2,
1731: p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1732: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1733: p_start_dt IN DATE,
1734: p_end_dt IN OUT NOCOPY DATE,
1735: p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,
1736: p_message_name OUT NOCOPY VARCHAR2

Line 1735: p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,

1731: p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1732: p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1733: p_start_dt IN DATE,
1734: p_end_dt IN OUT NOCOPY DATE,
1735: p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,
1736: p_message_name OUT NOCOPY VARCHAR2
1737: )RETURN BOOLEAN IS
1738:
1739: l_clc_end_dt DATE;

Line 1812: l_next_start_dt_diff := TRUNC(p_end_dt) - TRUNC(igs_tr_step_rec.action_dt);

1808: RETURN FALSE;
1809: END IF;
1810:
1811: IF p_date_type = 'A' THEN
1812: l_next_start_dt_diff := TRUNC(p_end_dt) - TRUNC(igs_tr_step_rec.action_dt);
1813: l_next_start_dt := TRUNC(p_end_dt);
1814: ELSIF p_date_type = 'C' THEN
1815: l_next_start_dt_diff := TRUNC(p_end_dt) - TRUNC(igs_tr_step_rec.completion_dt);
1816: l_next_start_dt := TRUNC(p_end_dt);

Line 1815: l_next_start_dt_diff := TRUNC(p_end_dt) - TRUNC(igs_tr_step_rec.completion_dt);

1811: IF p_date_type = 'A' THEN
1812: l_next_start_dt_diff := TRUNC(p_end_dt) - TRUNC(igs_tr_step_rec.action_dt);
1813: l_next_start_dt := TRUNC(p_end_dt);
1814: ELSIF p_date_type = 'C' THEN
1815: l_next_start_dt_diff := TRUNC(p_end_dt) - TRUNC(igs_tr_step_rec.completion_dt);
1816: l_next_start_dt := TRUNC(p_end_dt);
1817: END IF;
1818:
1819: IF igs_tr_item_rec.sequence_ind = 'Y' THEN

Line 1842: igs_tr_step_pkg.update_row(

1838: IF p_date_type = 'C' THEN
1839: l_action_days := l_upd_next_dt_rec.action_days;
1840: END IF;
1841:
1842: igs_tr_step_pkg.update_row(
1843: x_rowid => l_upd_next_dt_rec.row_id,
1844: x_tracking_id => l_upd_next_dt_rec.tracking_id,
1845: x_tracking_step_id => l_upd_next_dt_rec.tracking_step_id,
1846: x_tracking_step_number => l_upd_next_dt_rec.tracking_step_number,

Line 1900: p_message_name := 'IGS_TR_STEP_TYPE_ID_NOT_NULL';

1896: p_message_name := NULL;
1897:
1898: -- Validate that p_tracking_step_id and p_s_tracking_step_type cannot both be null.
1899: IF ((p_tracking_step_id IS NULL) AND (p_s_tracking_step_type IS NULL)) THEN
1900: p_message_name := 'IGS_TR_STEP_TYPE_ID_NOT_NULL';
1901: RETURN FALSE;
1902: END IF;
1903:
1904: -- Manu Validate that the step being updated does not

Line 1934: FETCH c_update_trst INTO igs_tr_step_rec ;

1930: -- multiple steps having the same s_tracking_step_type exists,
1931: -- since we are only interested in the first one.
1932:
1933: OPEN c_update_trst;
1934: FETCH c_update_trst INTO igs_tr_step_rec ;
1935:
1936: IF (c_update_trst%NOTFOUND) THEN
1937: CLOSE c_update_trst;
1938: p_message_name := 'IGS_TR_INVALID_STEP_ID';

Line 1943: p_step_completion_ind <> igs_tr_step_rec.step_completion_ind) THEN

1939: RETURN FALSE;
1940: END IF;
1941:
1942: IF (p_step_completion_ind IS NOT NULL AND
1943: p_step_completion_ind <> igs_tr_step_rec.step_completion_ind) THEN
1944: l_field_mod := l_field_mod || 'S';
1945: END IF;
1946:
1947: IF (p_by_pass_ind IS NOT NULL AND

Line 1948: p_by_pass_ind <> igs_tr_step_rec.by_pass_ind) THEN

1944: l_field_mod := l_field_mod || 'S';
1945: END IF;
1946:
1947: IF (p_by_pass_ind IS NOT NULL AND
1948: p_by_pass_ind <> igs_tr_step_rec.by_pass_ind) THEN
1949: l_field_mod := l_field_mod || 'B';
1950: END IF;
1951:
1952: IF (igs_tr_item_rec.override_offset_clc_ind = 'N' AND

Line 1954: p_action_dt <> igs_tr_step_rec.action_dt AND

1950: END IF;
1951:
1952: IF (igs_tr_item_rec.override_offset_clc_ind = 'N' AND
1953: p_action_dt IS NOT NULL AND
1954: p_action_dt <> igs_tr_step_rec.action_dt AND
1955: igs_tr_step_rec.step_completion_ind = 'N' AND
1956: igs_tr_step_rec.by_pass_ind = 'N') THEN
1957: IF (igs_tr_item_rec.sequence_ind = 'Y' AND
1958: NVL(INSTR(l_field_mod,'S'),0) = 0 AND

Line 1955: igs_tr_step_rec.step_completion_ind = 'N' AND

1951:
1952: IF (igs_tr_item_rec.override_offset_clc_ind = 'N' AND
1953: p_action_dt IS NOT NULL AND
1954: p_action_dt <> igs_tr_step_rec.action_dt AND
1955: igs_tr_step_rec.step_completion_ind = 'N' AND
1956: igs_tr_step_rec.by_pass_ind = 'N') THEN
1957: IF (igs_tr_item_rec.sequence_ind = 'Y' AND
1958: NVL(INSTR(l_field_mod,'S'),0) = 0 AND
1959: NVL(INSTR(l_field_mod,'B'),0) = 0) OR

Line 1956: igs_tr_step_rec.by_pass_ind = 'N') THEN

1952: IF (igs_tr_item_rec.override_offset_clc_ind = 'N' AND
1953: p_action_dt IS NOT NULL AND
1954: p_action_dt <> igs_tr_step_rec.action_dt AND
1955: igs_tr_step_rec.step_completion_ind = 'N' AND
1956: igs_tr_step_rec.by_pass_ind = 'N') THEN
1957: IF (igs_tr_item_rec.sequence_ind = 'Y' AND
1958: NVL(INSTR(l_field_mod,'S'),0) = 0 AND
1959: NVL(INSTR(l_field_mod,'B'),0) = 0) OR
1960: (igs_tr_item_rec.sequence_ind = 'N') THEN

Line 1968: igs_tr_step_rec.step_completion_ind = 'Y' AND

1964:
1965: IF (NVL(INSTR(l_field_mod,'S'),0) = 0 AND
1966: NVL(INSTR(l_field_mod,'B'),0) = 0) THEN
1967: IF (p_completion_dt IS NOT NULL AND
1968: igs_tr_step_rec.step_completion_ind = 'Y' AND
1969: p_completion_dt <> igs_tr_step_rec.completion_dt) THEN
1970: l_field_mod := l_field_mod || 'C';
1971: END IF;
1972: END IF;

Line 1969: p_completion_dt <> igs_tr_step_rec.completion_dt) THEN

1965: IF (NVL(INSTR(l_field_mod,'S'),0) = 0 AND
1966: NVL(INSTR(l_field_mod,'B'),0) = 0) THEN
1967: IF (p_completion_dt IS NOT NULL AND
1968: igs_tr_step_rec.step_completion_ind = 'Y' AND
1969: p_completion_dt <> igs_tr_step_rec.completion_dt) THEN
1970: l_field_mod := l_field_mod || 'C';
1971: END IF;
1972: END IF;
1973:

Line 1976: igs_tr_step_rec.step_completion_ind = 'N' AND

1972: END IF;
1973:
1974: IF (NVL(INSTR(l_field_mod,'S'),0) = 0 AND
1975: NVL(INSTR(l_field_mod,'B'),0) = 0 AND
1976: igs_tr_step_rec.step_completion_ind = 'N' AND
1977: igs_tr_step_rec.by_pass_ind = 'N') OR
1978: NVL(INSTR(l_field_mod,'S'),0) > 0 OR
1979: NVL(INSTR(l_field_mod,'B'),0) > 0 THEN
1980: IF (p_recipient_id IS NOT NULL) THEN

Line 1977: igs_tr_step_rec.by_pass_ind = 'N') OR

1973:
1974: IF (NVL(INSTR(l_field_mod,'S'),0) = 0 AND
1975: NVL(INSTR(l_field_mod,'B'),0) = 0 AND
1976: igs_tr_step_rec.step_completion_ind = 'N' AND
1977: igs_tr_step_rec.by_pass_ind = 'N') OR
1978: NVL(INSTR(l_field_mod,'S'),0) > 0 OR
1979: NVL(INSTR(l_field_mod,'B'),0) > 0 THEN
1980: IF (p_recipient_id IS NOT NULL) THEN
1981: IF (igs_tr_step_rec.recipient_id IS NOT NULL AND

Line 1981: IF (igs_tr_step_rec.recipient_id IS NOT NULL AND

1977: igs_tr_step_rec.by_pass_ind = 'N') OR
1978: NVL(INSTR(l_field_mod,'S'),0) > 0 OR
1979: NVL(INSTR(l_field_mod,'B'),0) > 0 THEN
1980: IF (p_recipient_id IS NOT NULL) THEN
1981: IF (igs_tr_step_rec.recipient_id IS NOT NULL AND
1982: p_recipient_id <> igs_tr_step_rec.recipient_id ) OR
1983: (igs_tr_step_rec.recipient_id IS NULL) THEN
1984: l_field_mod := l_field_mod || 'R';
1985: END IF;

Line 1982: p_recipient_id <> igs_tr_step_rec.recipient_id ) OR

1978: NVL(INSTR(l_field_mod,'S'),0) > 0 OR
1979: NVL(INSTR(l_field_mod,'B'),0) > 0 THEN
1980: IF (p_recipient_id IS NOT NULL) THEN
1981: IF (igs_tr_step_rec.recipient_id IS NOT NULL AND
1982: p_recipient_id <> igs_tr_step_rec.recipient_id ) OR
1983: (igs_tr_step_rec.recipient_id IS NULL) THEN
1984: l_field_mod := l_field_mod || 'R';
1985: END IF;
1986: END IF;

Line 1983: (igs_tr_step_rec.recipient_id IS NULL) THEN

1979: NVL(INSTR(l_field_mod,'B'),0) > 0 THEN
1980: IF (p_recipient_id IS NOT NULL) THEN
1981: IF (igs_tr_step_rec.recipient_id IS NOT NULL AND
1982: p_recipient_id <> igs_tr_step_rec.recipient_id ) OR
1983: (igs_tr_step_rec.recipient_id IS NULL) THEN
1984: l_field_mod := l_field_mod || 'R';
1985: END IF;
1986: END IF;
1987: END IF;

Line 2003: igs_tr_step_rec.tracking_id,

1999: IF NVL(INSTR(l_field_mod,'S'),0) > 0 THEN
2000: IF p_step_completion_ind = 'Y' THEN
2001: -- Validate new value, on error return false
2002: IF trkp_prev_step_cmplt (
2003: igs_tr_step_rec.tracking_id,
2004: igs_tr_step_rec.tracking_step_number,
2005: igs_tr_step_rec.step_group_id,
2006: p_message_name) THEN
2007:

Line 2004: igs_tr_step_rec.tracking_step_number,

2000: IF p_step_completion_ind = 'Y' THEN
2001: -- Validate new value, on error return false
2002: IF trkp_prev_step_cmplt (
2003: igs_tr_step_rec.tracking_id,
2004: igs_tr_step_rec.tracking_step_number,
2005: igs_tr_step_rec.step_group_id,
2006: p_message_name) THEN
2007:
2008: igs_tr_step_rec.step_completion_ind := 'Y';

Line 2005: igs_tr_step_rec.step_group_id,

2001: -- Validate new value, on error return false
2002: IF trkp_prev_step_cmplt (
2003: igs_tr_step_rec.tracking_id,
2004: igs_tr_step_rec.tracking_step_number,
2005: igs_tr_step_rec.step_group_id,
2006: p_message_name) THEN
2007:
2008: igs_tr_step_rec.step_completion_ind := 'Y';
2009: igs_tr_step_rec.by_pass_ind := 'N';

Line 2008: igs_tr_step_rec.step_completion_ind := 'Y';

2004: igs_tr_step_rec.tracking_step_number,
2005: igs_tr_step_rec.step_group_id,
2006: p_message_name) THEN
2007:
2008: igs_tr_step_rec.step_completion_ind := 'Y';
2009: igs_tr_step_rec.by_pass_ind := 'N';
2010:
2011: l_end_dt := NVL(p_completion_dt,SYSDATE);
2012: IF NOT (process_date (

Line 2009: igs_tr_step_rec.by_pass_ind := 'N';

2005: igs_tr_step_rec.step_group_id,
2006: p_message_name) THEN
2007:
2008: igs_tr_step_rec.step_completion_ind := 'Y';
2009: igs_tr_step_rec.by_pass_ind := 'N';
2010:
2011: l_end_dt := NVL(p_completion_dt,SYSDATE);
2012: IF NOT (process_date (
2013: 'C',

Line 2014: igs_tr_step_rec.tracking_id,

2010:
2011: l_end_dt := NVL(p_completion_dt,SYSDATE);
2012: IF NOT (process_date (
2013: 'C',
2014: igs_tr_step_rec.tracking_id,
2015: igs_tr_step_rec.tracking_step_number,
2016: l_end_dt,
2017: l_action_days,
2018: p_message_name)) THEN

Line 2015: igs_tr_step_rec.tracking_step_number,

2011: l_end_dt := NVL(p_completion_dt,SYSDATE);
2012: IF NOT (process_date (
2013: 'C',
2014: igs_tr_step_rec.tracking_id,
2015: igs_tr_step_rec.tracking_step_number,
2016: l_end_dt,
2017: l_action_days,
2018: p_message_name)) THEN
2019: CLOSE c_update_trst;

Line 2022: igs_tr_step_rec.completion_dt := l_end_dt;

2018: p_message_name)) THEN
2019: CLOSE c_update_trst;
2020: RETURN FALSE;
2021: END IF;
2022: igs_tr_step_rec.completion_dt := l_end_dt;
2023:
2024: ELSE
2025: CLOSE c_update_trst;
2026: RETURN FALSE;

Line 2032: igs_tr_step_rec.tracking_id,

2028:
2029: ELSIF p_step_completion_ind = 'N' THEN
2030: -- Validate new value, on error return false
2031: IF NOT (trkp_next_step_cmplt (
2032: igs_tr_step_rec.tracking_id,
2033: igs_tr_step_rec.tracking_step_number,
2034: igs_tr_step_rec.step_group_id,
2035: p_message_name)) THEN
2036:

Line 2033: igs_tr_step_rec.tracking_step_number,

2029: ELSIF p_step_completion_ind = 'N' THEN
2030: -- Validate new value, on error return false
2031: IF NOT (trkp_next_step_cmplt (
2032: igs_tr_step_rec.tracking_id,
2033: igs_tr_step_rec.tracking_step_number,
2034: igs_tr_step_rec.step_group_id,
2035: p_message_name)) THEN
2036:
2037: IF NVL(INSTR(l_field_mod,'C'),0) > 0 AND

Line 2034: igs_tr_step_rec.step_group_id,

2030: -- Validate new value, on error return false
2031: IF NOT (trkp_next_step_cmplt (
2032: igs_tr_step_rec.tracking_id,
2033: igs_tr_step_rec.tracking_step_number,
2034: igs_tr_step_rec.step_group_id,
2035: p_message_name)) THEN
2036:
2037: IF NVL(INSTR(l_field_mod,'C'),0) > 0 AND
2038: p_completion_dt IS NOT NULL THEN

Line 2044: igs_tr_step_rec.step_completion_ind := 'N';

2040: CLOSE c_update_trst;
2041: RETURN FALSE;
2042: END IF;
2043:
2044: igs_tr_step_rec.step_completion_ind := 'N';
2045: igs_tr_step_rec.completion_dt := NULL;
2046: IF NVL(INSTR(l_field_mod,'A'),0) = 0 THEN
2047: l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);
2048: l_field_mod := l_field_mod || 'A';

Line 2045: igs_tr_step_rec.completion_dt := NULL;

2041: RETURN FALSE;
2042: END IF;
2043:
2044: igs_tr_step_rec.step_completion_ind := 'N';
2045: igs_tr_step_rec.completion_dt := NULL;
2046: IF NVL(INSTR(l_field_mod,'A'),0) = 0 THEN
2047: l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);
2048: l_field_mod := l_field_mod || 'A';
2049: END IF;

Line 2047: l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);

2043:
2044: igs_tr_step_rec.step_completion_ind := 'N';
2045: igs_tr_step_rec.completion_dt := NULL;
2046: IF NVL(INSTR(l_field_mod,'A'),0) = 0 THEN
2047: l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);
2048: l_field_mod := l_field_mod || 'A';
2049: END IF;
2050:
2051: ELSE

Line 2063: igs_tr_step_rec.tracking_id,

2059: IF NVL(INSTR(l_field_mod,'B'),0) > 0 THEN
2060: IF p_by_pass_ind = 'Y' THEN
2061: -- Validate new value, on error return false
2062: IF NOT (trkp_next_grp_step_cmplt (
2063: igs_tr_step_rec.tracking_id,
2064: igs_tr_step_rec.tracking_step_number,
2065: igs_tr_step_rec.step_group_id,
2066: p_message_name)) THEN
2067:

Line 2064: igs_tr_step_rec.tracking_step_number,

2060: IF p_by_pass_ind = 'Y' THEN
2061: -- Validate new value, on error return false
2062: IF NOT (trkp_next_grp_step_cmplt (
2063: igs_tr_step_rec.tracking_id,
2064: igs_tr_step_rec.tracking_step_number,
2065: igs_tr_step_rec.step_group_id,
2066: p_message_name)) THEN
2067:
2068: IF NVL(INSTR(l_field_mod,'C'),0) > 0 AND

Line 2065: igs_tr_step_rec.step_group_id,

2061: -- Validate new value, on error return false
2062: IF NOT (trkp_next_grp_step_cmplt (
2063: igs_tr_step_rec.tracking_id,
2064: igs_tr_step_rec.tracking_step_number,
2065: igs_tr_step_rec.step_group_id,
2066: p_message_name)) THEN
2067:
2068: IF NVL(INSTR(l_field_mod,'C'),0) > 0 AND
2069: p_completion_dt IS NOT NULL THEN

Line 2075: igs_tr_step_rec.step_completion_ind := 'N';

2071: CLOSE c_update_trst;
2072: RETURN FALSE;
2073: END IF;
2074:
2075: igs_tr_step_rec.step_completion_ind := 'N';
2076: igs_tr_step_rec.by_pass_ind := 'Y';
2077: igs_tr_step_rec.completion_dt := NULL;
2078: IF NVL(INSTR(l_field_mod,'A'),0) = 0 THEN
2079: l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);

Line 2076: igs_tr_step_rec.by_pass_ind := 'Y';

2072: RETURN FALSE;
2073: END IF;
2074:
2075: igs_tr_step_rec.step_completion_ind := 'N';
2076: igs_tr_step_rec.by_pass_ind := 'Y';
2077: igs_tr_step_rec.completion_dt := NULL;
2078: IF NVL(INSTR(l_field_mod,'A'),0) = 0 THEN
2079: l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);
2080: l_field_mod := l_field_mod || 'A';

Line 2077: igs_tr_step_rec.completion_dt := NULL;

2073: END IF;
2074:
2075: igs_tr_step_rec.step_completion_ind := 'N';
2076: igs_tr_step_rec.by_pass_ind := 'Y';
2077: igs_tr_step_rec.completion_dt := NULL;
2078: IF NVL(INSTR(l_field_mod,'A'),0) = 0 THEN
2079: l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);
2080: l_field_mod := l_field_mod || 'A';
2081: END IF;

Line 2079: l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);

2075: igs_tr_step_rec.step_completion_ind := 'N';
2076: igs_tr_step_rec.by_pass_ind := 'Y';
2077: igs_tr_step_rec.completion_dt := NULL;
2078: IF NVL(INSTR(l_field_mod,'A'),0) = 0 THEN
2079: l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);
2080: l_field_mod := l_field_mod || 'A';
2081: END IF;
2082:
2083: ELSE

Line 2091: igs_tr_step_rec.tracking_id,

2087:
2088: ELSIF p_by_pass_ind = 'N' THEN
2089: -- Validate new value, on error return false
2090: IF NOT (trkp_next_step_cmplt (
2091: igs_tr_step_rec.tracking_id,
2092: igs_tr_step_rec.tracking_step_number,
2093: igs_tr_step_rec.step_group_id,
2094: p_message_name)) THEN
2095: IF NVL(INSTR(l_field_mod,'C'),0) > 0 THEN

Line 2092: igs_tr_step_rec.tracking_step_number,

2088: ELSIF p_by_pass_ind = 'N' THEN
2089: -- Validate new value, on error return false
2090: IF NOT (trkp_next_step_cmplt (
2091: igs_tr_step_rec.tracking_id,
2092: igs_tr_step_rec.tracking_step_number,
2093: igs_tr_step_rec.step_group_id,
2094: p_message_name)) THEN
2095: IF NVL(INSTR(l_field_mod,'C'),0) > 0 THEN
2096: p_message_name := 'IGS_TR_CANNOT_SET_CMP_DATE';

Line 2093: igs_tr_step_rec.step_group_id,

2089: -- Validate new value, on error return false
2090: IF NOT (trkp_next_step_cmplt (
2091: igs_tr_step_rec.tracking_id,
2092: igs_tr_step_rec.tracking_step_number,
2093: igs_tr_step_rec.step_group_id,
2094: p_message_name)) THEN
2095: IF NVL(INSTR(l_field_mod,'C'),0) > 0 THEN
2096: p_message_name := 'IGS_TR_CANNOT_SET_CMP_DATE';
2097: CLOSE c_update_trst;

Line 2101: igs_tr_step_rec.by_pass_ind := 'N';

2097: CLOSE c_update_trst;
2098: RETURN FALSE;
2099: END IF;
2100:
2101: igs_tr_step_rec.by_pass_ind := 'N';
2102:
2103: ELSE
2104: CLOSE c_update_trst;
2105: RETURN FALSE;

Line 2116: igs_tr_step_rec.tracking_id,

2112: -- Validate new value, on error return false
2113: l_end_dt := NVL(p_action_dt,l_action_dt);
2114: IF NOT (process_date (
2115: 'A',
2116: igs_tr_step_rec.tracking_id,
2117: igs_tr_step_rec.tracking_step_number,
2118: l_end_dt,
2119: l_action_days,
2120: p_message_name)) THEN

Line 2117: igs_tr_step_rec.tracking_step_number,

2113: l_end_dt := NVL(p_action_dt,l_action_dt);
2114: IF NOT (process_date (
2115: 'A',
2116: igs_tr_step_rec.tracking_id,
2117: igs_tr_step_rec.tracking_step_number,
2118: l_end_dt,
2119: l_action_days,
2120: p_message_name)) THEN
2121: CLOSE c_update_trst;

Line 2125: igs_tr_step_rec.action_dt := l_end_dt;

2121: CLOSE c_update_trst;
2122: RETURN FALSE;
2123: END IF;
2124:
2125: igs_tr_step_rec.action_dt := l_end_dt;
2126: igs_tr_step_rec.action_days := l_action_days;
2127:
2128: END IF;
2129:

Line 2126: igs_tr_step_rec.action_days := l_action_days;

2122: RETURN FALSE;
2123: END IF;
2124:
2125: igs_tr_step_rec.action_dt := l_end_dt;
2126: igs_tr_step_rec.action_days := l_action_days;
2127:
2128: END IF;
2129:
2130: IF NVL(INSTR(l_field_mod,'C'),0) > 0 THEN

Line 2136: igs_tr_step_rec.tracking_id,

2132:
2133: l_end_dt := NVL(p_completion_dt,SYSDATE);
2134: IF NOT (process_date (
2135: 'C',
2136: igs_tr_step_rec.tracking_id,
2137: igs_tr_step_rec.tracking_step_number,
2138: l_end_dt,
2139: l_action_days,
2140: p_message_name)) THEN

Line 2137: igs_tr_step_rec.tracking_step_number,

2133: l_end_dt := NVL(p_completion_dt,SYSDATE);
2134: IF NOT (process_date (
2135: 'C',
2136: igs_tr_step_rec.tracking_id,
2137: igs_tr_step_rec.tracking_step_number,
2138: l_end_dt,
2139: l_action_days,
2140: p_message_name)) THEN
2141: CLOSE c_update_trst;

Line 2144: igs_tr_step_rec.completion_dt := l_end_dt;

2140: p_message_name)) THEN
2141: CLOSE c_update_trst;
2142: RETURN FALSE;
2143: END IF;
2144: igs_tr_step_rec.completion_dt := l_end_dt;
2145: END IF;
2146:
2147: IF NVL(INSTR(l_field_mod,'R'),0) > 0 THEN
2148: -- Validate new value, on error return false

Line 2154: igs_tr_step_rec.recipient_id := p_recipient_id;

2150: IF NOT (igs_co_val_oc.genp_val_prsn_id(p_recipient_id, p_message_name)) THEN
2151: CLOSE c_update_trst;
2152: RETURN FALSE;
2153: ELSE
2154: igs_tr_step_rec.recipient_id := p_recipient_id;
2155: END IF;
2156:
2157: END IF;
2158:

Line 2165: igs_tr_step_rec.step_completion_ind := 'Y';

2161: IF NVL(INSTR(l_field_mod,'S'),0) > 0 THEN
2162: IF p_step_completion_ind = 'Y' THEN
2163: -- Validate new value, on error return false
2164:
2165: igs_tr_step_rec.step_completion_ind := 'Y';
2166: igs_tr_step_rec.by_pass_ind := 'N';
2167:
2168: l_end_dt := NVL(p_completion_dt,SYSDATE);
2169: IF NOT (process_date (

Line 2166: igs_tr_step_rec.by_pass_ind := 'N';

2162: IF p_step_completion_ind = 'Y' THEN
2163: -- Validate new value, on error return false
2164:
2165: igs_tr_step_rec.step_completion_ind := 'Y';
2166: igs_tr_step_rec.by_pass_ind := 'N';
2167:
2168: l_end_dt := NVL(p_completion_dt,SYSDATE);
2169: IF NOT (process_date (
2170: 'C',

Line 2171: igs_tr_step_rec.tracking_id,

2167:
2168: l_end_dt := NVL(p_completion_dt,SYSDATE);
2169: IF NOT (process_date (
2170: 'C',
2171: igs_tr_step_rec.tracking_id,
2172: igs_tr_step_rec.tracking_step_number,
2173: l_end_dt,
2174: l_action_days,
2175: p_message_name)) THEN

Line 2172: igs_tr_step_rec.tracking_step_number,

2168: l_end_dt := NVL(p_completion_dt,SYSDATE);
2169: IF NOT (process_date (
2170: 'C',
2171: igs_tr_step_rec.tracking_id,
2172: igs_tr_step_rec.tracking_step_number,
2173: l_end_dt,
2174: l_action_days,
2175: p_message_name)) THEN
2176: CLOSE c_update_trst;

Line 2179: igs_tr_step_rec.completion_dt := l_end_dt;

2175: p_message_name)) THEN
2176: CLOSE c_update_trst;
2177: RETURN FALSE;
2178: END IF;
2179: igs_tr_step_rec.completion_dt := l_end_dt;
2180:
2181: ELSIF p_step_completion_ind = 'N' THEN
2182: -- Validate new value, on error return false
2183:

Line 2191: igs_tr_step_rec.step_completion_ind := 'N';

2187: CLOSE c_update_trst;
2188: RETURN FALSE;
2189: END IF;
2190:
2191: igs_tr_step_rec.step_completion_ind := 'N';
2192: igs_tr_step_rec.completion_dt := NULL;
2193:
2194: END IF;
2195:

Line 2192: igs_tr_step_rec.completion_dt := NULL;

2188: RETURN FALSE;
2189: END IF;
2190:
2191: igs_tr_step_rec.step_completion_ind := 'N';
2192: igs_tr_step_rec.completion_dt := NULL;
2193:
2194: END IF;
2195:
2196: END IF;

Line 2209: igs_tr_step_rec.step_completion_ind := 'N';

2205: CLOSE c_update_trst;
2206: RETURN FALSE;
2207: END IF;
2208:
2209: igs_tr_step_rec.step_completion_ind := 'N';
2210: igs_tr_step_rec.by_pass_ind := 'Y';
2211: igs_tr_step_rec.completion_dt := NULL;
2212:
2213: ELSIF p_by_pass_ind = 'N' THEN

Line 2210: igs_tr_step_rec.by_pass_ind := 'Y';

2206: RETURN FALSE;
2207: END IF;
2208:
2209: igs_tr_step_rec.step_completion_ind := 'N';
2210: igs_tr_step_rec.by_pass_ind := 'Y';
2211: igs_tr_step_rec.completion_dt := NULL;
2212:
2213: ELSIF p_by_pass_ind = 'N' THEN
2214: -- Validate new value, on error return false

Line 2211: igs_tr_step_rec.completion_dt := NULL;

2207: END IF;
2208:
2209: igs_tr_step_rec.step_completion_ind := 'N';
2210: igs_tr_step_rec.by_pass_ind := 'Y';
2211: igs_tr_step_rec.completion_dt := NULL;
2212:
2213: ELSIF p_by_pass_ind = 'N' THEN
2214: -- Validate new value, on error return false
2215:

Line 2222: igs_tr_step_rec.by_pass_ind := 'N';

2218: CLOSE c_update_trst;
2219: RETURN FALSE;
2220: END IF;
2221:
2222: igs_tr_step_rec.by_pass_ind := 'N';
2223:
2224: END IF;
2225:
2226: END IF;

Line 2235: igs_tr_step_rec.tracking_id,

2231: IF p_action_dt IS NOT NULL THEN
2232: l_end_dt := p_action_dt;
2233: IF NOT (process_date (
2234: 'A',
2235: igs_tr_step_rec.tracking_id,
2236: igs_tr_step_rec.tracking_step_number,
2237: l_end_dt,
2238: l_action_days,
2239: p_message_name)) THEN

Line 2236: igs_tr_step_rec.tracking_step_number,

2232: l_end_dt := p_action_dt;
2233: IF NOT (process_date (
2234: 'A',
2235: igs_tr_step_rec.tracking_id,
2236: igs_tr_step_rec.tracking_step_number,
2237: l_end_dt,
2238: l_action_days,
2239: p_message_name)) THEN
2240: CLOSE c_update_trst;

Line 2243: igs_tr_step_rec.action_dt := l_end_dt;

2239: p_message_name)) THEN
2240: CLOSE c_update_trst;
2241: RETURN FALSE;
2242: END IF;
2243: igs_tr_step_rec.action_dt := l_end_dt;
2244: igs_tr_step_rec.action_days := l_action_days;
2245: END IF;
2246: END IF;
2247:

Line 2244: igs_tr_step_rec.action_days := l_action_days;

2240: CLOSE c_update_trst;
2241: RETURN FALSE;
2242: END IF;
2243: igs_tr_step_rec.action_dt := l_end_dt;
2244: igs_tr_step_rec.action_days := l_action_days;
2245: END IF;
2246: END IF;
2247:
2248: IF NVL(INSTR(l_field_mod,'C'),0) > 0 THEN

Line 2254: igs_tr_step_rec.tracking_id,

2250:
2251: l_end_dt := NVL(p_completion_dt,SYSDATE);
2252: IF NOT (process_date (
2253: 'C',
2254: igs_tr_step_rec.tracking_id,
2255: igs_tr_step_rec.tracking_step_number,
2256: l_end_dt,
2257: l_action_days,
2258: p_message_name)) THEN

Line 2255: igs_tr_step_rec.tracking_step_number,

2251: l_end_dt := NVL(p_completion_dt,SYSDATE);
2252: IF NOT (process_date (
2253: 'C',
2254: igs_tr_step_rec.tracking_id,
2255: igs_tr_step_rec.tracking_step_number,
2256: l_end_dt,
2257: l_action_days,
2258: p_message_name)) THEN
2259: CLOSE c_update_trst;

Line 2262: igs_tr_step_rec.completion_dt := l_end_dt;

2258: p_message_name)) THEN
2259: CLOSE c_update_trst;
2260: RETURN FALSE;
2261: END IF;
2262: igs_tr_step_rec.completion_dt := l_end_dt;
2263: END IF;
2264:
2265: IF NVL(INSTR(l_field_mod,'R'),0) > 0 THEN
2266: -- Validate new value, on error return false

Line 2272: igs_tr_step_rec.recipient_id := p_recipient_id;

2268: IF NOT (igs_co_val_oc.genp_val_prsn_id(p_recipient_id, p_message_name)) THEN
2269: CLOSE c_update_trst;
2270: RETURN FALSE;
2271: ELSE
2272: igs_tr_step_rec.recipient_id := p_recipient_id;
2273: END IF;
2274: END IF;
2275:
2276: END IF; -- End of sequential validation

Line 2280: igs_tr_step_pkg.update_row(

2276: END IF; -- End of sequential validation
2277:
2278: -- Update the record with NOWAIT option
2279:
2280: igs_tr_step_pkg.update_row(
2281: x_rowid => igs_tr_step_rec.row_id,
2282: x_tracking_id => igs_tr_step_rec.tracking_id,
2283: x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,

Line 2281: x_rowid => igs_tr_step_rec.row_id,

2277:
2278: -- Update the record with NOWAIT option
2279:
2280: igs_tr_step_pkg.update_row(
2281: x_rowid => igs_tr_step_rec.row_id,
2282: x_tracking_id => igs_tr_step_rec.tracking_id,
2283: x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
2285: x_description => igs_tr_step_rec.description,

Line 2282: x_tracking_id => igs_tr_step_rec.tracking_id,

2278: -- Update the record with NOWAIT option
2279:
2280: igs_tr_step_pkg.update_row(
2281: x_rowid => igs_tr_step_rec.row_id,
2282: x_tracking_id => igs_tr_step_rec.tracking_id,
2283: x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
2285: x_description => igs_tr_step_rec.description,
2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,

Line 2283: x_tracking_step_id => igs_tr_step_rec.tracking_step_id,

2279:
2280: igs_tr_step_pkg.update_row(
2281: x_rowid => igs_tr_step_rec.row_id,
2282: x_tracking_id => igs_tr_step_rec.tracking_id,
2283: x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
2285: x_description => igs_tr_step_rec.description,
2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
2287: x_completion_dt => igs_tr_step_rec.completion_dt,

Line 2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,

2280: igs_tr_step_pkg.update_row(
2281: x_rowid => igs_tr_step_rec.row_id,
2282: x_tracking_id => igs_tr_step_rec.tracking_id,
2283: x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
2285: x_description => igs_tr_step_rec.description,
2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
2287: x_completion_dt => igs_tr_step_rec.completion_dt,
2288: x_action_days => igs_tr_step_rec.action_days,

Line 2285: x_description => igs_tr_step_rec.description,

2281: x_rowid => igs_tr_step_rec.row_id,
2282: x_tracking_id => igs_tr_step_rec.tracking_id,
2283: x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
2285: x_description => igs_tr_step_rec.description,
2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
2287: x_completion_dt => igs_tr_step_rec.completion_dt,
2288: x_action_days => igs_tr_step_rec.action_days,
2289: x_step_completion_ind => igs_tr_step_rec.step_completion_ind,

Line 2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,

2282: x_tracking_id => igs_tr_step_rec.tracking_id,
2283: x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
2285: x_description => igs_tr_step_rec.description,
2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
2287: x_completion_dt => igs_tr_step_rec.completion_dt,
2288: x_action_days => igs_tr_step_rec.action_days,
2289: x_step_completion_ind => igs_tr_step_rec.step_completion_ind,
2290: x_by_pass_ind => igs_tr_step_rec.by_pass_ind,

Line 2287: x_completion_dt => igs_tr_step_rec.completion_dt,

2283: x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
2285: x_description => igs_tr_step_rec.description,
2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
2287: x_completion_dt => igs_tr_step_rec.completion_dt,
2288: x_action_days => igs_tr_step_rec.action_days,
2289: x_step_completion_ind => igs_tr_step_rec.step_completion_ind,
2290: x_by_pass_ind => igs_tr_step_rec.by_pass_ind,
2291: x_recipient_id => igs_tr_step_rec.recipient_id,

Line 2288: x_action_days => igs_tr_step_rec.action_days,

2284: x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
2285: x_description => igs_tr_step_rec.description,
2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
2287: x_completion_dt => igs_tr_step_rec.completion_dt,
2288: x_action_days => igs_tr_step_rec.action_days,
2289: x_step_completion_ind => igs_tr_step_rec.step_completion_ind,
2290: x_by_pass_ind => igs_tr_step_rec.by_pass_ind,
2291: x_recipient_id => igs_tr_step_rec.recipient_id,
2292: x_mode => 'R',

Line 2289: x_step_completion_ind => igs_tr_step_rec.step_completion_ind,

2285: x_description => igs_tr_step_rec.description,
2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
2287: x_completion_dt => igs_tr_step_rec.completion_dt,
2288: x_action_days => igs_tr_step_rec.action_days,
2289: x_step_completion_ind => igs_tr_step_rec.step_completion_ind,
2290: x_by_pass_ind => igs_tr_step_rec.by_pass_ind,
2291: x_recipient_id => igs_tr_step_rec.recipient_id,
2292: x_mode => 'R',
2293: x_step_group_id => igs_tr_step_rec.step_group_id,

Line 2290: x_by_pass_ind => igs_tr_step_rec.by_pass_ind,

2286: x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
2287: x_completion_dt => igs_tr_step_rec.completion_dt,
2288: x_action_days => igs_tr_step_rec.action_days,
2289: x_step_completion_ind => igs_tr_step_rec.step_completion_ind,
2290: x_by_pass_ind => igs_tr_step_rec.by_pass_ind,
2291: x_recipient_id => igs_tr_step_rec.recipient_id,
2292: x_mode => 'R',
2293: x_step_group_id => igs_tr_step_rec.step_group_id,
2294: x_step_catalog_cd => igs_tr_step_rec.step_catalog_cd,

Line 2291: x_recipient_id => igs_tr_step_rec.recipient_id,

2287: x_completion_dt => igs_tr_step_rec.completion_dt,
2288: x_action_days => igs_tr_step_rec.action_days,
2289: x_step_completion_ind => igs_tr_step_rec.step_completion_ind,
2290: x_by_pass_ind => igs_tr_step_rec.by_pass_ind,
2291: x_recipient_id => igs_tr_step_rec.recipient_id,
2292: x_mode => 'R',
2293: x_step_group_id => igs_tr_step_rec.step_group_id,
2294: x_step_catalog_cd => igs_tr_step_rec.step_catalog_cd,
2295: x_publish_ind => NVL(igs_tr_step_rec.publish_ind,'N')

Line 2293: x_step_group_id => igs_tr_step_rec.step_group_id,

2289: x_step_completion_ind => igs_tr_step_rec.step_completion_ind,
2290: x_by_pass_ind => igs_tr_step_rec.by_pass_ind,
2291: x_recipient_id => igs_tr_step_rec.recipient_id,
2292: x_mode => 'R',
2293: x_step_group_id => igs_tr_step_rec.step_group_id,
2294: x_step_catalog_cd => igs_tr_step_rec.step_catalog_cd,
2295: x_publish_ind => NVL(igs_tr_step_rec.publish_ind,'N')
2296: );
2297:

Line 2294: x_step_catalog_cd => igs_tr_step_rec.step_catalog_cd,

2290: x_by_pass_ind => igs_tr_step_rec.by_pass_ind,
2291: x_recipient_id => igs_tr_step_rec.recipient_id,
2292: x_mode => 'R',
2293: x_step_group_id => igs_tr_step_rec.step_group_id,
2294: x_step_catalog_cd => igs_tr_step_rec.step_catalog_cd,
2295: x_publish_ind => NVL(igs_tr_step_rec.publish_ind,'N')
2296: );
2297:
2298: CLOSE c_update_trst;

Line 2295: x_publish_ind => NVL(igs_tr_step_rec.publish_ind,'N')

2291: x_recipient_id => igs_tr_step_rec.recipient_id,
2292: x_mode => 'R',
2293: x_step_group_id => igs_tr_step_rec.step_group_id,
2294: x_step_catalog_cd => igs_tr_step_rec.step_catalog_cd,
2295: x_publish_ind => NVL(igs_tr_step_rec.publish_ind,'N')
2296: );
2297:
2298: CLOSE c_update_trst;
2299:

Line 2372: Purpose: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content

2368: /***********************************************************************************************************
2369:
2370: Created By: pradhakr
2371: Date Created By: 11-Feb-2002
2372: Purpose: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content
2373: of IGS_TR_TYPE_STEP_V.
2374: 1. If that step group being deleted in form IGSTR001 ( block tracking step) is the last one of its
2375: kind for a particular tracking id, the step group id will be deleted from IGS_TR_TSTP_GRP_LMT
2376: else Step Group Limit will be decremented by 1.

Line 2506: PROCEDURE sync_trk_item_grplmt ( p_tracking_id IGS_TR_STEP_V.tracking_id %TYPE,

2502: App_Exception.Raise_Exception;
2503:
2504: END sync_trk_type_grplmt;
2505:
2506: PROCEDURE sync_trk_item_grplmt ( p_tracking_id IGS_TR_STEP_V.tracking_id %TYPE,
2507: p_execute VARCHAR2
2508: )
2509: IS
2510:

Line 2518: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content

2514:
2515: Date Created By: 11-Feb-2002
2516:
2517: Purpose: This procedure would be called from the post_forms commit trigger of the Form IGSTR007 (Tracking Items ).
2518: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content
2519: of IGS_TR_STEP_V.
2520: 1. If that step group being deleted in form IGSTR007 ( block tracking step) is the last one of its
2521: kind for a particular tracking id, the step group id will be deleted from IGS_TR_STEP_GRP_LMT
2522:

Line 2519: of IGS_TR_STEP_V.

2515: Date Created By: 11-Feb-2002
2516:
2517: Purpose: This procedure would be called from the post_forms commit trigger of the Form IGSTR007 (Tracking Items ).
2518: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content
2519: of IGS_TR_STEP_V.
2520: 1. If that step group being deleted in form IGSTR007 ( block tracking step) is the last one of its
2521: kind for a particular tracking id, the step group id will be deleted from IGS_TR_STEP_GRP_LMT
2522:
2523: 2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created

Line 2521: kind for a particular tracking id, the step group id will be deleted from IGS_TR_STEP_GRP_LMT

2517: Purpose: This procedure would be called from the post_forms commit trigger of the Form IGSTR007 (Tracking Items ).
2518: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content
2519: of IGS_TR_STEP_V.
2520: 1. If that step group being deleted in form IGSTR007 ( block tracking step) is the last one of its
2521: kind for a particular tracking id, the step group id will be deleted from IGS_TR_STEP_GRP_LMT
2522:
2523: 2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created
2524: in the table IGS_TR_STEP_GRP_LMT and the Step Group Limit would be defaulted to 1.
2525:

Line 2524: in the table IGS_TR_STEP_GRP_LMT and the Step Group Limit would be defaulted to 1.

2520: 1. If that step group being deleted in form IGSTR007 ( block tracking step) is the last one of its
2521: kind for a particular tracking id, the step group id will be deleted from IGS_TR_STEP_GRP_LMT
2522:
2523: 2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created
2524: in the table IGS_TR_STEP_GRP_LMT and the Step Group Limit would be defaulted to 1.
2525:
2526: 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
2527: 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).
2528:

Line 2526: 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

2522:
2523: 2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created
2524: in the table IGS_TR_STEP_GRP_LMT and the Step Group Limit would be defaulted to 1.
2525:
2526: 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
2527: 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).
2528:
2529:
2530: Known limitations,enhancements,remarks:

Line 2527: 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).

2523: 2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created
2524: in the table IGS_TR_STEP_GRP_LMT and the Step Group Limit would be defaulted to 1.
2525:
2526: 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
2527: 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).
2528:
2529:
2530: Known limitations,enhancements,remarks:
2531:

Line 2539: -- 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

2535: ************************************************************************************************************/
2536:
2537:
2538:
2539: -- 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
2540: -- IF such records are found then delete them from the table igs_tr_step_grp_lmt
2541: -- ssawhney view reference changed to table
2542: CURSOR c_decrement_step_grp_lmt
2543: IS

Line 2540: -- IF such records are found then delete them from the table igs_tr_step_grp_lmt

2536:
2537:
2538:
2539: -- 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
2540: -- IF such records are found then delete them from the table igs_tr_step_grp_lmt
2541: -- ssawhney view reference changed to table
2542: CURSOR c_decrement_step_grp_lmt
2543: IS
2544: SELECT

Line 2547: igs_tr_step_grp_lmt

2543: IS
2544: SELECT
2545: step_group_id
2546: FROM
2547: igs_tr_step_grp_lmt
2548: WHERE
2549: tracking_id = p_tracking_id
2550: MINUS
2551: SELECT

Line 2554: igs_tr_step sv

2550: MINUS
2551: SELECT
2552: step_group_id
2553: FROM
2554: igs_tr_step sv
2555: WHERE
2556: tracking_id = p_tracking_id
2557: AND
2558: sv.step_group_id IS NOT NULL

Line 2563: -- Fetch all the distinct step group id's for the given tracking id which are there in IGS_TR_STEP and not in the table IGS_TR_STEP_GRP_LMT

2559: GROUP BY
2560: sv.step_group_id;
2561:
2562:
2563: -- Fetch all the distinct step group id's for the given tracking id which are there in IGS_TR_STEP and not in the table IGS_TR_STEP_GRP_LMT
2564: -- IF such records are found then insert them from the table igs_tr_step_grp_lmt with a default group_limit of 1.
2565: -- ssawhney view reference changed to table
2566: CURSOR c_insert_step_grp_lmt
2567: IS

Line 2564: -- IF such records are found then insert them from the table igs_tr_step_grp_lmt with a default group_limit of 1.

2560: sv.step_group_id;
2561:
2562:
2563: -- Fetch all the distinct step group id's for the given tracking id which are there in IGS_TR_STEP and not in the table IGS_TR_STEP_GRP_LMT
2564: -- IF such records are found then insert them from the table igs_tr_step_grp_lmt with a default group_limit of 1.
2565: -- ssawhney view reference changed to table
2566: CURSOR c_insert_step_grp_lmt
2567: IS
2568: SELECT

Line 2571: igs_tr_step sv

2567: IS
2568: SELECT
2569: step_group_id
2570: FROM
2571: igs_tr_step sv
2572: WHERE
2573: tracking_id = p_tracking_id
2574: AND
2575: sv.step_group_id IS NOT NULL

Line 2582: igs_tr_step_grp_lmt

2578: MINUS
2579: SELECT
2580: step_group_id
2581: FROM
2582: igs_tr_step_grp_lmt
2583: WHERE
2584: tracking_id = p_tracking_id;
2585:
2586:

Line 2587: -- Cursor to fetch the Rowid from the igs_tr_step_grp_lmt table for a tracking_id, step_group_id combination

2583: WHERE
2584: tracking_id = p_tracking_id;
2585:
2586:
2587: -- Cursor to fetch the Rowid from the igs_tr_step_grp_lmt table for a tracking_id, step_group_id combination
2588:
2589: CURSOR c_rowid ( p_tracking_id IGS_TR_STEP_GRP_LMT.TRACKING_ID%TYPE ,
2590: p_step_group_id IGS_TR_STEP_GRP_LMT.STEP_GROUP_ID%TYPE
2591: )

Line 2589: CURSOR c_rowid ( p_tracking_id IGS_TR_STEP_GRP_LMT.TRACKING_ID%TYPE ,

2585:
2586:
2587: -- Cursor to fetch the Rowid from the igs_tr_step_grp_lmt table for a tracking_id, step_group_id combination
2588:
2589: CURSOR c_rowid ( p_tracking_id IGS_TR_STEP_GRP_LMT.TRACKING_ID%TYPE ,
2590: p_step_group_id IGS_TR_STEP_GRP_LMT.STEP_GROUP_ID%TYPE
2591: )
2592: IS
2593: SELECT

Line 2590: p_step_group_id IGS_TR_STEP_GRP_LMT.STEP_GROUP_ID%TYPE

2586:
2587: -- Cursor to fetch the Rowid from the igs_tr_step_grp_lmt table for a tracking_id, step_group_id combination
2588:
2589: CURSOR c_rowid ( p_tracking_id IGS_TR_STEP_GRP_LMT.TRACKING_ID%TYPE ,
2590: p_step_group_id IGS_TR_STEP_GRP_LMT.STEP_GROUP_ID%TYPE
2591: )
2592: IS
2593: SELECT
2594: ROWID

Line 2596: igs_tr_step_grp_lmt

2592: IS
2593: SELECT
2594: ROWID
2595: FROM
2596: igs_tr_step_grp_lmt
2597: WHERE
2598: tracking_id = p_tracking_id
2599: AND
2600: step_group_id = p_step_group_id;

Line 2605: CURSOR c_item_step ( p_tracking_id IGS_TR_STEP_V.TRACKING_ID%TYPE )

2601:
2602:
2603: -- Cursor to get the count of step_group_id's for a tracking item step
2604: -- ssawhney view reference changed to table
2605: CURSOR c_item_step ( p_tracking_id IGS_TR_STEP_V.TRACKING_ID%TYPE )
2606: IS
2607: SELECT
2608: step_group_id,
2609: COUNT(step_group_id) step_group_count

Line 2611: igs_tr_step

2607: SELECT
2608: step_group_id,
2609: COUNT(step_group_id) step_group_count
2610: FROM
2611: igs_tr_step
2612: WHERE
2613: tracking_id = p_tracking_id
2614: AND
2615: step_group_id IS NOT NULL

Line 2620: -- Cursor to get the step group limit from igs_tr_step_grp_lmt table for a combination of tracking_id and step_group_id

2616: GROUP BY
2617: step_group_id;
2618:
2619:
2620: -- Cursor to get the step group limit from igs_tr_step_grp_lmt table for a combination of tracking_id and step_group_id
2621:
2622: CURSOR c_grp_lmt (p_tracking_id igs_tr_step_grp_lmt.tracking_id%TYPE,
2623: p_step_group_id igs_tr_step_grp_lmt.step_group_id%TYPE)
2624: IS

Line 2622: CURSOR c_grp_lmt (p_tracking_id igs_tr_step_grp_lmt.tracking_id%TYPE,

2618:
2619:
2620: -- Cursor to get the step group limit from igs_tr_step_grp_lmt table for a combination of tracking_id and step_group_id
2621:
2622: CURSOR c_grp_lmt (p_tracking_id igs_tr_step_grp_lmt.tracking_id%TYPE,
2623: p_step_group_id igs_tr_step_grp_lmt.step_group_id%TYPE)
2624: IS
2625: SELECT
2626: step_group_limit

Line 2623: p_step_group_id igs_tr_step_grp_lmt.step_group_id%TYPE)

2619:
2620: -- Cursor to get the step group limit from igs_tr_step_grp_lmt table for a combination of tracking_id and step_group_id
2621:
2622: CURSOR c_grp_lmt (p_tracking_id igs_tr_step_grp_lmt.tracking_id%TYPE,
2623: p_step_group_id igs_tr_step_grp_lmt.step_group_id%TYPE)
2624: IS
2625: SELECT
2626: step_group_limit
2627: FROM

Line 2628: igs_tr_step_grp_lmt

2624: IS
2625: SELECT
2626: step_group_limit
2627: FROM
2628: igs_tr_step_grp_lmt
2629: WHERE
2630: tracking_id = p_tracking_id
2631: AND
2632: step_group_id = p_step_group_id;

Line 2636: ln_step_group_limit igs_tr_step_grp_lmt.step_group_limit%TYPE;

2632: step_group_id = p_step_group_id;
2633:
2634:
2635: lv_rowid VARCHAR2(25);
2636: ln_step_group_limit igs_tr_step_grp_lmt.step_group_limit%TYPE;
2637: ln_grp_lmt igs_tr_step_grp_lmt.step_group_limit%TYPE;
2638:
2639: BEGIN
2640:

Line 2637: ln_grp_lmt igs_tr_step_grp_lmt.step_group_limit%TYPE;

2633:
2634:
2635: lv_rowid VARCHAR2(25);
2636: ln_step_group_limit igs_tr_step_grp_lmt.step_group_limit%TYPE;
2637: ln_grp_lmt igs_tr_step_grp_lmt.step_group_limit%TYPE;
2638:
2639: BEGIN
2640:
2641: /************************************ Validation 1 ***********************************************************/

Line 2650: -- Fetch all the distinct step group id's for the given tracking id which are there in the table IGS_TR_STEP_GRP_LMT

2646: END IF;
2647:
2648:
2649: /************************************ Validation 2 ***********************************************************/
2650: -- Fetch all the distinct step group id's for the given tracking id which are there in the table IGS_TR_STEP_GRP_LMT
2651: -- and not in the view IGS_TR_STEP_V.
2652: -- IF such records are found then delete them from the table igs_tr_step_grp_lmt
2653:
2654: FOR rec_decrement_step_grp_lmt IN c_decrement_step_grp_lmt LOOP

Line 2651: -- and not in the view IGS_TR_STEP_V.

2647:
2648:
2649: /************************************ Validation 2 ***********************************************************/
2650: -- Fetch all the distinct step group id's for the given tracking id which are there in the table IGS_TR_STEP_GRP_LMT
2651: -- and not in the view IGS_TR_STEP_V.
2652: -- IF such records are found then delete them from the table igs_tr_step_grp_lmt
2653:
2654: FOR rec_decrement_step_grp_lmt IN c_decrement_step_grp_lmt LOOP
2655: OPEN c_rowid(p_tracking_id, rec_decrement_step_grp_lmt.step_group_id );

Line 2652: -- IF such records are found then delete them from the table igs_tr_step_grp_lmt

2648:
2649: /************************************ Validation 2 ***********************************************************/
2650: -- Fetch all the distinct step group id's for the given tracking id which are there in the table IGS_TR_STEP_GRP_LMT
2651: -- and not in the view IGS_TR_STEP_V.
2652: -- IF such records are found then delete them from the table igs_tr_step_grp_lmt
2653:
2654: FOR rec_decrement_step_grp_lmt IN c_decrement_step_grp_lmt LOOP
2655: OPEN c_rowid(p_tracking_id, rec_decrement_step_grp_lmt.step_group_id );
2656: FETCH c_rowid INTO lv_rowid;

Line 2658: igs_tr_step_grp_lmt_pkg.delete_row ( X_ROWID => lv_rowid );

2654: FOR rec_decrement_step_grp_lmt IN c_decrement_step_grp_lmt LOOP
2655: OPEN c_rowid(p_tracking_id, rec_decrement_step_grp_lmt.step_group_id );
2656: FETCH c_rowid INTO lv_rowid;
2657: CLOSE c_rowid;
2658: igs_tr_step_grp_lmt_pkg.delete_row ( X_ROWID => lv_rowid );
2659: END LOOP;
2660:
2661:
2662:

Line 2664: -- Insert records into IGS_TR_STEP_GRP_LMT when tracking_id and step_group_id combination exists in IGS_TR_STEP_V

2660:
2661:
2662:
2663: /************************************* Validation 3 ***********************************************************/
2664: -- Insert records into IGS_TR_STEP_GRP_LMT when tracking_id and step_group_id combination exists in IGS_TR_STEP_V
2665: -- but not in IGS_TR_STEP_GRP_LMT.
2666: -- Default the value of Step_group_limit to 1.
2667:
2668: FOR rec_insert_step_grp_lmt IN c_insert_step_grp_lmt LOOP

Line 2665: -- but not in IGS_TR_STEP_GRP_LMT.

2661:
2662:
2663: /************************************* Validation 3 ***********************************************************/
2664: -- Insert records into IGS_TR_STEP_GRP_LMT when tracking_id and step_group_id combination exists in IGS_TR_STEP_V
2665: -- but not in IGS_TR_STEP_GRP_LMT.
2666: -- Default the value of Step_group_limit to 1.
2667:
2668: FOR rec_insert_step_grp_lmt IN c_insert_step_grp_lmt LOOP
2669: igs_tr_step_grp_lmt_pkg.insert_row (

Line 2669: igs_tr_step_grp_lmt_pkg.insert_row (

2665: -- but not in IGS_TR_STEP_GRP_LMT.
2666: -- Default the value of Step_group_limit to 1.
2667:
2668: FOR rec_insert_step_grp_lmt IN c_insert_step_grp_lmt LOOP
2669: igs_tr_step_grp_lmt_pkg.insert_row (
2670: X_ROWID => lv_rowid ,
2671: X_TRACKING_ID => p_tracking_id ,
2672: X_STEP_GROUP_ID => rec_insert_step_grp_lmt.step_group_id ,
2673: X_STEP_GROUP_LIMIT => 1 ,

Line 2680: -- Check the step group limit for a combination of Tracking_Id and Step_group_id in the table IGS_TR_STEP_GRP_LMT

2676: END LOOP;
2677:
2678:
2679: /*************************************** Validation 4 ***********************************************************/
2680: -- Check the step group limit for a combination of Tracking_Id and Step_group_id in the table IGS_TR_STEP_GRP_LMT
2681: -- and the view IGS_TR_STEP_V.
2682: -- 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
2683: -- combination in IGS_TR_STEP_V view then set it equal to the count of step_group_id's in the IGS_TR_STEP_V.
2684:

Line 2681: -- and the view IGS_TR_STEP_V.

2677:
2678:
2679: /*************************************** Validation 4 ***********************************************************/
2680: -- Check the step group limit for a combination of Tracking_Id and Step_group_id in the table IGS_TR_STEP_GRP_LMT
2681: -- and the view IGS_TR_STEP_V.
2682: -- 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
2683: -- combination in IGS_TR_STEP_V view then set it equal to the count of step_group_id's in the IGS_TR_STEP_V.
2684:
2685: FOR rec_item_step IN c_item_step (p_tracking_id)

Line 2682: -- 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

2678:
2679: /*************************************** Validation 4 ***********************************************************/
2680: -- Check the step group limit for a combination of Tracking_Id and Step_group_id in the table IGS_TR_STEP_GRP_LMT
2681: -- and the view IGS_TR_STEP_V.
2682: -- 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
2683: -- combination in IGS_TR_STEP_V view then set it equal to the count of step_group_id's in the IGS_TR_STEP_V.
2684:
2685: FOR rec_item_step IN c_item_step (p_tracking_id)
2686: LOOP

Line 2683: -- combination in IGS_TR_STEP_V view then set it equal to the count of step_group_id's in the IGS_TR_STEP_V.

2679: /*************************************** Validation 4 ***********************************************************/
2680: -- Check the step group limit for a combination of Tracking_Id and Step_group_id in the table IGS_TR_STEP_GRP_LMT
2681: -- and the view IGS_TR_STEP_V.
2682: -- 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
2683: -- combination in IGS_TR_STEP_V view then set it equal to the count of step_group_id's in the IGS_TR_STEP_V.
2684:
2685: FOR rec_item_step IN c_item_step (p_tracking_id)
2686: LOOP
2687: OPEN c_grp_lmt (p_tracking_id, rec_item_step.step_group_id);

Line 2696: -- then set the step group limit equal to the count of step group id's in the igs)tr)step_v view for a step group id and tracking_id combination.

2692: FETCH c_rowid INTO lv_rowid;
2693: CLOSE c_rowid;
2694:
2695: -- Check whether Step Group limit is greater than the count of Tracking item steps for a step group id and tracking_id combination
2696: -- then set the step group limit equal to the count of step group id's in the igs)tr)step_v view for a step group id and tracking_id combination.
2697:
2698: IF ln_grp_lmt > rec_item_step.step_group_count THEN
2699: igs_tr_step_grp_lmt_pkg.update_row (
2700: X_ROWID => lv_rowid ,

Line 2699: igs_tr_step_grp_lmt_pkg.update_row (

2695: -- Check whether Step Group limit is greater than the count of Tracking item steps for a step group id and tracking_id combination
2696: -- then set the step group limit equal to the count of step group id's in the igs)tr)step_v view for a step group id and tracking_id combination.
2697:
2698: IF ln_grp_lmt > rec_item_step.step_group_count THEN
2699: igs_tr_step_grp_lmt_pkg.update_row (
2700: X_ROWID => lv_rowid ,
2701: X_TRACKING_ID => p_tracking_id ,
2702: X_STEP_GROUP_ID => rec_item_step.step_group_id ,
2703: X_STEP_GROUP_LIMIT => rec_item_step.step_group_count ,

Line 2719: FUNCTION validate_completion_status ( p_tracking_id IN IGS_TR_STEP_V.TRACKING_ID%TYPE,

2715:
2716: END sync_trk_item_grplmt;
2717:
2718:
2719: FUNCTION validate_completion_status ( p_tracking_id IN IGS_TR_STEP_V.TRACKING_ID%TYPE,
2720: p_tracking_status IN IGS_TR_ITEM_V.TRACKING_STATUS%TYPE,
2721: p_sequence_ind IN IGS_TR_ITEM_V.SEQUENCE_IND%TYPE,
2722: p_message_name OUT NOCOPY VARCHAR2
2723: )

Line 2756: igs_tr_step

2752: tracking_id,
2753: tracking_step_number,
2754: step_group_id
2755: FROM
2756: igs_tr_step
2757: WHERE
2758: tracking_id = p_tracking_id
2759: AND step_completion_ind = 'Y';
2760:

Line 2769: igs_tr_step trst

2765: SELECT
2766: DISTINCT TO_NUMBER('1'),
2767: 1
2768: FROM
2769: igs_tr_step trst
2770: WHERE
2771: trst.tracking_id = p_tracking_id
2772: AND
2773: trst.step_group_id IS NULL

Line 2786: igs_tr_step trst

2782: SELECT
2783: DISTINCT trst.step_group_id step_group_id,
2784: COUNT(*) count_step
2785: FROM
2786: igs_tr_step trst
2787: WHERE
2788: trst.tracking_id = p_tracking_id
2789: AND
2790: trst.step_group_id IS NOT NULL

Line 2803: igs_tr_step_grp_lmt trgl

2799: SELECT
2800: DISTINCT trgl.step_group_id step_group_id,
2801: step_group_limit count_step
2802: FROM
2803: igs_tr_step_grp_lmt trgl
2804: WHERE
2805: trgl.tracking_id = p_tracking_id
2806: ) tab,
2807: igs_tr_step_grp_lmt trg

Line 2807: igs_tr_step_grp_lmt trg

2803: igs_tr_step_grp_lmt trgl
2804: WHERE
2805: trgl.tracking_id = p_tracking_id
2806: ) tab,
2807: igs_tr_step_grp_lmt trg
2808: WHERE tab.step_group_id = trg.step_group_id
2809: AND
2810: trg.tracking_id = p_tracking_id
2811: AND