1 PACKAGE igs_tr_gen_002 AUTHID CURRENT_USER AS
2 /* $Header: IGSTR02S.pls 115.18 2002/11/29 04:18:37 nsidana ship $ */
3
4 /*******************************************************************************
5 Created by : Oracle IDC
6 Date created:
7
8 Purpose:
9 1. Used for deletion of tracking step notes, deletion of
10 tracking steps, for tracking items, deletion of tracking group members for
11 IGS_TR_ITEMS, deletion of tracking item notes for IGS_TR_ITEMS and deletion of
12 IGS_TR_ITEMS
13
14 Usage: (e.g. restricted, unrestricted, where to call from)
15 1. Called from IGSTR007.fmb
16
17 Known limitations/enhancements/remarks:
18 -
19
20 Change History: (who, when, what: NO CREATION RECORDS HERE!)
21 Who When What
22 *******************************************************************************/
23 FUNCTION trkp_del_tri(
24 p_tracking_id IN NUMBER ,
25 p_message_name OUT NOCOPY VARCHAR2 )
26 RETURN BOOLEAN;
27
28 /*******************************************************************************
29 Created by : Oracle IDC
30 Date created:
31
32 Purpose:
33 1. This function returns the status of a tracking group
34
35 Usage: (e.g. restricted, unrestricted, where to call from)
36 1. Called from IGSTR007.FMB
37
38 Known limitations/enhancements/remarks:
39 -
40
41 Change History: (who, when, what: NO CREATION RECORDS HERE!)
42 Who When What
43 *******************************************************************************/
44 FUNCTION trkp_get_group_sts(
45 p_tracking_group_id IN NUMBER )
46 RETURN VARCHAR2;
47 PRAGMA RESTRICT_REFERENCES (trkp_get_group_sts, wnds);
48
49 /*******************************************************************************
50 Created by : Oracle IDC
51 Date created:
52
53 Purpose:
54 1. This function returns the status of a tracking item
55
56 Usage: (e.g. restricted, unrestricted, where to call from)
57 1. Called from IGSTR007.FMB
58
59 Known limitations/enhancements/remarks:
60 -
61
62 Change History: (who, when, what: NO CREATION RECORDS HERE!)
63 Who When What
64 *******************************************************************************/
65 FUNCTION trkp_get_item_status(
66 p_tracking_id IN NUMBER )
67 RETURN VARCHAR2;
68 PRAGMA RESTRICT_REFERENCES (trkp_get_item_status, wnds,wnps);
69
70
71 /*******************************************************************************
72 Created by : Oracle IDC
73 Date created:
74
75 Purpose:
76 1. This program unit is used to insert the default tracking_steps for an item
77 based on the IGS_TR_TYPE and its associated tracking_type_steps, duplicate
78 existing IGS_GE_NOTE records and insert IGS_TR_STEP_NOTES
79
80 Usage: (e.g. restricted, unrestricted, where to call from)
81 1. Called from IGSTR007.FMB upon creation of a tracking item.
82
83 Known limitations/enhancements/remarks:
84 -
85
86 Change History: (who, when, what: NO CREATION RECORDS HERE!)
87 Who When What
88 msrinivi 06 Jul,2001 Added logic to insert the newly added columns, i.e.
89 step catalog id,step group id and publish indicator
90 *******************************************************************************/
91 PROCEDURE trkp_ins_dflt_trst(
92 p_tracking_id IN NUMBER ,
93 p_message_name OUT NOCOPY VARCHAR2 );
94
95 /*******************************************************************************
96 Created by : Oracle IDC
97 Date created:
98
99 Purpose:
100 1. This procedure will be used by batch processing to create a tracking item
101 It will accept the details of the item to be created and insert a
102 IGS_TR_ITEM record. The tracking item step will be defaulted
103 when the database table insert trigger fires for the tracking item.
104
105 Usage: (e.g. restricted, unrestricted, where to call from)
106 1. Called from IGSTR007.FMB
107
108 Known limitations/enhancements/remarks:
109 -
110
111 Change History: (who, when, what: NO CREATION RECORDS HERE!)
112 Who When What
113 msrinivi 06 Jul,2001 Added 3 new columns : completion due dt,
114 override offset clc ind and publish ind
115 *******************************************************************************/
116 PROCEDURE trkp_ins_trk_item(
117 p_tracking_status IN VARCHAR2 ,
118 p_tracking_type IN VARCHAR2 ,
119 p_source_person_id IN NUMBER ,
120 p_start_dt IN DATE ,
121 p_target_days IN NUMBER ,
122 p_sequence_ind IN VARCHAR2 ,
123 p_business_days_ind IN VARCHAR2 ,
124 p_originator_person_id IN NUMBER ,
125 p_s_created_ind IN VARCHAR2 DEFAULT 'N',
126 p_tracking_id OUT NOCOPY NUMBER ,
127 p_message_name OUT NOCOPY VARCHAR2,
128 p_override_offset_clc_ind IN VARCHAR2 DEFAULT 'N',
129 p_completion_due_dt IN DATE DEFAULT NULL,
130 p_publish_ind IN VARCHAR2 DEFAULT 'N'
131 );
132
133 /*******************************************************************************
134 Created by : Oracle IDC
135 Date created:
136
137 Purpose:
138 1. This module will update fields of the action days of
139 a IGS_TR_STEP record.
140 Usage: (e.g. restricted, unrestricted, where to call from)
141 1. Called from IGSTR007.FMB
142
143 Known limitations/enhancements/remarks:
144 -
145
146 Change History: (who, when, what: NO CREATION RECORDS HERE!)
147 Who When What
148 msrinivi 06 Jul,2001 Modified to have the logic for step groups before update.
149 *******************************************************************************/
150 FUNCTION trkp_upd_trst(
151 p_tracking_id IN NUMBER ,
152 p_tracking_step_id IN NUMBER ,
153 p_s_tracking_step_type IN VARCHAR2 ,
154 p_action_dt IN DATE ,
155 p_completion_dt IN DATE ,
156 p_step_completion_ind IN VARCHAR2,
157 p_by_pass_ind IN VARCHAR2,
158 p_recipient_id IN NUMBER ,
159 p_message_name OUT NOCOPY VARCHAR2)
160 RETURN BOOLEAN;
161
162
163 /***********************************************************************************************************
164
165 Created By: pradhakr
166 Date Created By: 11-Feb-2002
167 Purpose: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content
168 of IGS_TR_TYPE_STEP_V.
169 1. If that step group being deleted in form IGSTR001 ( block tracking step) is the last one of its
170 kind for a particular tracking id, the step group id will be deleted from IGS_TR_TSTP_GRP_LMT
171 else Step Group Limit will be decremented by 1.
172 2. Any new Step Group ID being created in the form IGSTR001 ( block tracking step) will also be created
173 in the table IGS_TR_TSTP_GRP_LMT and the Step Group Limit would be defaulted to 1.
174
175 Known limitations,enhancements,remarks:
176 Change History
177 Who When What
178 ************************************************************************************************************/
179
180 PROCEDURE sync_trk_type_grplmt (
181 p_tracking_type IGS_TR_TYPE_STEP_V.tracking_type%TYPE,
182 p_execute VARCHAR2 DEFAULT 'N'
183 );
184
185
186 /***********************************************************************************************************
187
188 Created By: Arun Iyer
189
190 Date Created By: 13-Feb-2002
191
192 Purpose: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content
193 of IGS_TR_STEP_V.
194 1. If that step group being deleted in form IGSTR007 ( block tracking step) is the last one of its
195 kind for a particular tracking id, the step group id will be deleted from IGS_TR_STEP_GRP_LMT
196
197 2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created
198 in the table IGS_TR_STEP_GRP_LMT and the Step Group Limit would be defaulted to 1.
199
200 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
201 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).
202
203
204 Known limitations,enhancements,remarks:
205
206 Change History
207
208 Who When What
209 ************************************************************************************************************/
210
211 PROCEDURE sync_trk_item_grplmt ( p_tracking_id IGS_TR_STEP_V.tracking_id %TYPE,
212 p_execute VARCHAR2 DEFAULT 'N'
213 ) ;
214
215
216
217 /***********************************************************************************************************
218
219 Created By: Arun Iyer
220
221 Date Created By: 13-Feb-2002
222
223 Purpose: This procedure would be called from the post forms commit trigger of the form IGSTR007 (Tracking Items)
224 when the user checks the sequential flag (value = Y' ) for the current tracking id.
225 1. This checks whether all the previous steps have been completed or not. In case they have not been completed
226 it suitable raises an error.
227 2. If in the form the tracking status has been set to COMPLETE then this procedure validate whether
228 the tracking status changed to COMPLETE is correct or not.
229 In case it is incorrect then a suitable error message is returned back to the calling form
230
231 Known limitations,enhancements,remarks:
232
233 Change History
234
235 Who When What
236 ************************************************************************************************************/
237
238 FUNCTION validate_completion_status ( p_tracking_id IN IGS_TR_STEP_V.TRACKING_ID%TYPE,
239 p_tracking_status IN IGS_TR_ITEM_V.TRACKING_STATUS%TYPE,
240 p_sequence_ind IN IGS_TR_ITEM_V.SEQUENCE_IND%TYPE,
241 p_message_name OUT NOCOPY VARCHAR2
242 ) RETURN BOOLEAN;
243
244
245
246
247 /***********************************************************************************************************
248
249 Created By:
250 Date Created By:
251 Purpose:
252 Known limitations,enhancements,remarks:
253 Change History
254 Who When What
255 pradhakr 14-Feb-2002 Added a parameter step_group_id in the function.
256 ************************************************************************************************************/
257 FUNCTION trkp_prev_step_cmplt (
258 p_tracking_id igs_tr_step.tracking_id%TYPE,
259 p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
260 p_step_group_id igs_tr_step.step_group_id%TYPE,
261 p_message_name OUT NOCOPY VARCHAR2
262 ) RETURN BOOLEAN ;
263
264
265 END igs_tr_gen_002;