1 PACKAGE BODY IGS_RE_GEN_003 AS
2 /* $Header: IGSRE03B.pls 120.0 2005/06/01 21:35:49 appldev noship $ */
3 -- modified procedures resp_ins_tex_tri and resp_ins_tpm_tri to
4 --add 3 new fields in the tbh calls to IGS_TR_ITEM and IGS_TR_STEP
5 -- for tracking dld nov 2001 release bug#1837257
6
7 PROCEDURE RESP_INS_MIL_HIST(
8 p_person_id IN NUMBER ,
9 p_ca_sequence_number IN NUMBER ,
10 p_sequence_number IN NUMBER ,
11 p_old_milestone_type IN VARCHAR2 ,
12 p_new_milestone_type IN VARCHAR2 ,
13 p_old_milestone_status IN VARCHAR2 ,
14 p_new_milestone_status IN VARCHAR2 ,
15 p_old_due_dt IN DATE ,
16 p_new_due_dt IN DATE ,
17 p_old_description IN VARCHAR2 ,
18 p_new_description IN VARCHAR2 ,
19 p_old_actual_reached_dt IN DATE ,
20 p_new_actual_reached_dt IN DATE ,
21 p_old_preced_sequence_number IN NUMBER ,
22 p_new_preced_sequence_number IN NUMBER ,
23 p_old_ovrd_ntfctn_immnnt_days IN NUMBER ,
24 p_new_ovrd_ntfctn_immnnt_days IN NUMBER ,
25 p_old_ovrd_ntfctn_rmndr_days IN NUMBER ,
26 p_new_ovrd_ntfctn_rmndr_days IN NUMBER ,
27 p_old_re_reminder_days IN NUMBER ,
28 p_new_re_reminder_days IN NUMBER ,
29 p_old_comments IN VARCHAR2 ,
30 p_new_comments IN VARCHAR2 ,
31 p_old_update_who IN NUMBER ,
32 p_new_update_who IN NUMBER ,
33 p_old_update_on IN DATE ,
34 p_new_update_on IN DATE )
35 AS
36 gv_other_detail VARCHAR2(255);
37 lv_rowid VARCHAR2(25);
38 v_org_id IGS_PR_MILESTONE_HST.ORG_ID%TYPE := IGS_GE_GEN_003.Get_Org_Id;
39
40 BEGIN -- resp_ins_mil_hist
41 -- Insert IGS_PR_MILESTONE history(IGS_PR_MILESTONE_HST)
42 DECLARE
43 v_mil_rec IGS_PR_MILESTONE_HST%ROWTYPE;
44 v_create_history BOOLEAN := FALSE;
45 BEGIN
46 -- If any of the old values (p_old_<column_name>) are
47 -- different from the associated new values (p_new_<column_name>)
48 -- (with the exception of the last_update_date and last_updated_by columns)
49 -- then create a IGS_PR_MILESTONE_HST history record with the old values
50 -- (p_old_<column_name>). Do not set the last_updated_by and last_update_date
51 -- columns when creating the history record.
52 IF p_new_milestone_type <> p_old_milestone_type THEN
53 v_mil_rec.milestone_type := p_old_milestone_type;
54 v_create_history := TRUE;
55 END IF;
56 IF p_new_milestone_status <> p_old_milestone_status THEN
57 v_mil_rec.milestone_status := p_old_milestone_status;
58 v_create_history := TRUE;
59 END IF;
60 IF p_new_due_dt <> p_old_due_dt THEN
61 v_mil_rec.due_dt := p_old_due_dt;
62 v_create_history := TRUE;
63 END IF;
64 IF NVL(p_new_description,'NULL') <> NVL(p_old_description,'NULL') THEN
65 v_mil_rec.description := p_old_description;
66 v_create_history := TRUE;
67 END IF;
68 IF NVL(p_new_actual_reached_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) <>
69 NVL(p_old_actual_reached_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
70 v_mil_rec.actual_reached_dt := p_old_actual_reached_dt;
71 v_create_history := TRUE;
72 END IF;
73 IF NVL(p_new_preced_sequence_number,0) <>
74 NVL(p_old_preced_sequence_number,0) THEN
75 v_mil_rec.preced_sequence_number := p_old_preced_sequence_number;
76 v_create_history := TRUE;
77 END IF;
78 IF NVL(p_new_ovrd_ntfctn_immnnt_days,0) <>
79 NVL(p_old_ovrd_ntfctn_immnnt_days,0) THEN
80 v_mil_rec.ovrd_ntfctn_imminent_days := p_old_ovrd_ntfctn_immnnt_days;
81 v_create_history := TRUE;
82 END IF;
83 IF NVL(p_new_ovrd_ntfctn_rmndr_days,0) <>
84 NVL(p_old_ovrd_ntfctn_rmndr_days,0) THEN
85 v_mil_rec.ovrd_ntfctn_reminder_days := p_old_ovrd_ntfctn_rmndr_days;
86 v_create_history := TRUE;
87 END IF;
88 IF NVL(p_new_re_reminder_days,0) <>
89 NVL(p_old_re_reminder_days,0) THEN
90 v_mil_rec.ovrd_ntfctn_re_reminder_days := p_old_re_reminder_days;
91 v_create_history := TRUE;
92 END IF;
93 IF NVL(p_new_comments,'NULL') <> NVL(p_old_comments,'NULL') THEN
94 v_mil_rec.comments := p_old_comments;
95 v_create_history := TRUE;
96 END IF;
97 IF v_create_history THEN
98 v_mil_rec.person_id := p_person_id;
99 v_mil_rec.ca_sequence_number := p_ca_sequence_number;
100 v_mil_rec.sequence_number := p_sequence_number;
101 v_mil_rec.hist_start_dt := p_old_update_on;
102 v_mil_rec.hist_end_dt := p_new_update_on;
103 v_mil_rec.hist_who := p_old_update_who;
104 IGS_PR_MILESTONE_HST_PKG.INSERT_ROW(
105 X_ROWID => LV_ROWID,
106 X_person_id => v_mil_rec.person_id,
107 X_ca_sequence_number => v_mil_rec.ca_sequence_number,
108 X_sequence_number => v_mil_rec.sequence_number,
109 X_hist_start_dt => v_mil_rec.hist_start_dt,
110 X_hist_end_dt => v_mil_rec.hist_end_dt,
111 X_hist_who => v_mil_rec.hist_who,
112 X_milestone_type => v_mil_rec.milestone_type,
113 X_milestone_status => v_mil_rec.milestone_status,
114 X_due_dt => v_mil_rec.due_dt,
115 X_description => v_mil_rec.description,
116 X_actual_reached_dt => v_mil_rec.actual_reached_dt,
117 X_preced_sequence_number=> v_mil_rec.preced_sequence_number,
118 X_ovrd_ntfctn_imminent_days => v_mil_rec.ovrd_ntfctn_imminent_days,
119 X_ovrd_ntfctn_reminder_days => v_mil_rec.ovrd_ntfctn_reminder_days,
120 X_ovrd_ntfctn_re_reminder_days => v_mil_rec.ovrd_ntfctn_re_reminder_days,
121 X_comments => v_mil_rec.comments,
122 X_Org_Id => v_org_id,
123 X_MODE => 'R');
124 END IF;
125 END;
126 EXCEPTION
127 WHEN OTHERS THEN
128 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
129 IGS_GE_MSG_STACK.ADD;
130 app_Exception.Raise_Exception;
131 END resp_ins_mil_hist;
132
133
134 PROCEDURE RESP_INS_TEX_HIST(
135 P_PERSON_ID IN NUMBER ,
136 P_CA_SEQUENCE_NUMBER IN NUMBER ,
137 P_THE_SEQUENCE_NUMBER IN NUMBER ,
138 P_CREATION_DT IN DATE ,
139 P_OLD_SUBMISSION_DT IN DATE ,
140 P_NEW_SUBMISSION_DT IN DATE ,
141 P_OLD_THESIS_EXAM_TYPE IN VARCHAR2 ,
142 P_NEW_THESIS_EXAM_TYPE IN VARCHAR2 ,
143 p_old_thesis_panel_type IN VARCHAR2 ,
144 p_new_thesis_panel_type IN VARCHAR2 ,
145 p_old_thesis_result_cd IN VARCHAR2 ,
146 p_new_thesis_result_cd IN VARCHAR2 ,
147 p_old_tracking_id IN NUMBER ,
148 p_new_tracking_id NUMBER ,
149 p_old_update_who IN NUMBER ,
150 p_new_update_who IN NUMBER ,
151 p_old_update_on IN DATE ,
152 p_new_update_on IN DATE )
153 AS
154 gv_other_detail VARCHAR2(255);
155 LV_ROWID VARCHAR2(25);
156 v_org_id IGS_PR_MILESTONE_HST.ORG_ID%TYPE := IGS_GE_GEN_003.Get_Org_Id;
157 BEGIN -- resp_ins_tex_hist
158 -- Insert IGS_RE_THESIS_EXAM history (IGS_RE_THS_EXAM_HIST)
159 DECLARE
160 v_teh_rec IGS_RE_THS_EXAM_HIST%ROWTYPE;
161 v_create_history BOOLEAN := FALSE;
162 v_hist_start_dt IGS_RE_THESIS_EXAM.last_update_date%TYPE;
163 v_hist_end_dt IGS_RE_THESIS_EXAM.last_update_date%TYPE;
164 v_hist_who IGS_RE_THESIS_EXAM.last_updated_by%TYPE;
165 BEGIN
166 -- If any of the old values (p_old_<column_name>) are different from the
167 -- associated new values (p_new_<column_name>) (with the exception the
168 -- last_updated_by and last_update_date columns) then create an IGS_RE_THS_EXAM_HIST history
169 -- record with the old values (p_old_<column_name>). Do not set the
170 -- last_updated_by and last_update_date columns when creating the history record.
171 IF NVL(p_new_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
172 NVL(p_old_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
173 v_teh_rec.submission_dt := p_old_submission_dt;
174 v_create_history := TRUE;
175 END IF;
176 IF p_new_thesis_exam_type <> p_old_thesis_exam_type THEN
177 v_teh_rec.thesis_exam_type := p_old_thesis_exam_type;
178 v_create_history := TRUE;
179 END IF;
180 IF p_new_thesis_panel_type <> p_old_thesis_panel_type THEN
181 v_teh_rec.thesis_panel_type := p_old_thesis_panel_type;
182 v_create_history := TRUE;
183 END IF;
184 IF NVL(p_new_tracking_id, -1) <> NVL(p_old_tracking_id, -1) THEN
185 v_teh_rec.tracking_id := p_old_tracking_id;
186 v_create_history := TRUE;
187 END IF;
188 IF NVL(p_new_thesis_result_cd, 'NULL') <>
189 NVL(p_old_thesis_result_cd,'NULL') THEN
190 v_teh_rec.thesis_result_cd := NVL(p_old_thesis_result_cd,' ');
191 v_create_history := TRUE;
192 END IF;
193 -- create a history record if any column has changed
194 IF v_create_history = TRUE THEN
195 v_teh_rec.person_id := p_person_id;
196 v_teh_rec.ca_sequence_number := p_ca_sequence_number;
197 v_teh_rec.the_sequence_number := p_the_sequence_number;
198 v_teh_rec.creation_dt := p_creation_dt;
199 v_teh_rec.hist_start_dt := p_old_update_on;
200 v_teh_rec.hist_end_dt := NVL(p_new_update_on,SYSDATE);
201 v_teh_rec.hist_who := p_old_update_who;
202 IGS_RE_THS_EXAM_HIST_PKG.INSERT_ROW(
203 X_ROWID => LV_ROWID,
204 X_person_id => v_teh_rec.person_id,
205 X_ca_sequence_number => v_teh_rec.ca_sequence_number,
206 X_the_sequence_number => v_teh_rec.the_sequence_number,
207 X_creation_dt => v_teh_rec.creation_dt,
208 X_hist_start_dt => v_teh_rec.hist_start_dt,
209 X_hist_end_dt => v_teh_rec.hist_end_dt,
210 X_hist_who => v_teh_rec.hist_who,
211 X_submission_dt => v_teh_rec.submission_dt,
212 X_thesis_exam_type => v_teh_rec.thesis_exam_type,
213 X_thesis_panel_type => v_teh_rec.thesis_panel_type,
214 X_tracking_id => v_teh_rec.tracking_id,
215 X_thesis_result_cd => v_teh_rec.thesis_result_cd,
216 X_Org_Id => v_org_id,
217 X_MODE => 'R');
218 END IF;
219 END;
220 EXCEPTION
221 WHEN OTHERS THEN
222 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
223 IGS_GE_MSG_STACK.ADD;
224 App_Exception.Raise_Exception;
225 END resp_ins_tex_hist;
226
227 FUNCTION RESP_INS_TEX_TRI(
228 p_person_id IN NUMBER ,
229 p_ca_sequence_number IN NUMBER ,
230 p_the_sequence_number IN NUMBER ,
231 p_creation_dt IN DATE ,
232 p_thesis_panel_type IN VARCHAR2 ,
233 p_message_name OUT NOCOPY VARCHAR2 )
234 --add 3 new fields in the tbh calls to IGS_TR_ITEM and IGS_TR_STEP
235 --for tracking dld nov 2001 release bug#1837257
236 /*-----------------------------------------------------------------------
237 who when what
238 svanukur 07-sep-2004 modified cursor c_pe for performance bug 3866423
239 --------------------------------------------------------------------------*/
240 RETURN NUMBER AS
241 gv_other_detail VARCHAR2(255);
242 BEGIN -- resp_ins_tex_tri
243 -- Insert tracking item for a thesis_examination_record.
244 -- The routine returns the tracking ID of the item created.
245 -- IGS_GE_NOTE: A commit is not done from this routine as it is done as part of the
246 -- calling routine
247 DECLARE
248 cst_s_tracking_type CONSTANT
249 IGS_TR_TYPE.s_tracking_type%TYPE := 'RES_TEX';
250 cst_stst_principal CONSTANT
251 IGS_TR_STEP.s_tracking_step_type%TYPE := 'RES_TEX_PR';
252 cst_stst_student CONSTANT
253 IGS_TR_STEP.s_tracking_step_type%TYPE := 'RES_TEX_ST';
254 cst_stst_originator CONSTANT
255 IGS_TR_STEP.s_tracking_step_type%TYPE := 'RES_TEX_OR';
256 cst_stst_chair CONSTANT
257 IGS_TR_STEP.s_tracking_step_type%TYPE := 'RES_TEX_CH';
258 cst_active CONSTANT IGS_TR_STATUS.s_tracking_status%TYPE := 'ACTIVE';
259 v_message_name VARCHAR2(30);
260 v_originator_id IGS_RE_THS_PNL_MBR.person_id%TYPE;
261 v_recipient_id IGS_TR_STEP.recipient_id%TYPE;
262 v_tts_recipient_id IGS_TR_TYPE_STEP.recipient_id%TYPE;
263 v_principal_id IGS_PE_PERSON.person_id%TYPE;
264 v_chair_id IGS_PE_PERSON.person_id%TYPE;
265 v_tracking_id IGS_TR_ITEM.tracking_id%TYPE;
266 v_target_days NUMBER;
267 v_tracking_type IGS_TR_TYPE.tracking_type%TYPE;
268 v_tracking_status IGS_TR_STATUS.tracking_status%TYPE;
269 v_person_id IGS_RE_SPRVSR.person_id%TYPE;
270 v_start_dt IGS_TR_ITEM.start_dt%TYPE;
271 v_business_days_ind IGS_TR_ITEM.business_days_ind%TYPE;
272 v_action_dt IGS_TR_STEP_V.action_dt%TYPE;
273 v_current_person_id IGS_PE_PERSON.person_id%TYPE;
274
275 CURSOR c_pe IS
276 SELECT PERSON_PARTY_ID
277 FROM fnd_user
278 WHERE user_id = fnd_global.user_id;
279 CURSOR c_rst_rsup IS
280 SELECT rsup.person_id
281 FROM IGS_RE_SPRVSR rsup,
282 IGS_RE_SPRVSR_TYPE rst
283 WHERE rsup.ca_person_id = p_person_id AND
284 rsup.ca_sequence_number = p_ca_sequence_number AND
285 rst.research_supervisor_type = rsup.research_supervisor_type AND
286 rst.principal_supervisor_ind = 'Y'
287 ORDER BY rsup.supervision_percentage DESC;
288 CURSOR c_tpt (
289 cp_thesis_panel_type IGS_RE_THS_PNL_TYPE.thesis_panel_type%TYPE) IS
290 SELECT tpt.tracking_type
291 FROM IGS_RE_THS_PNL_TYPE tpt
292 WHERE tpt.thesis_panel_type = cp_thesis_panel_type;
293 CURSOR c_ts IS
294 SELECT ts.tracking_status
295 FROM IGS_TR_STATUS ts
296 WHERE ts.s_tracking_status = cst_active AND
297 ts.closed_ind = 'N'
298 ORDER BY ts.tracking_status ASC;
299 CURSOR c_tri (
300 cp_tracking_id IGS_TR_ITEM.tracking_id%TYPE) IS
301 SELECT tri.start_dt,
302 tri.business_days_ind
303 FROM IGS_TR_ITEM tri
304 WHERE tri.tracking_id = cp_tracking_id;
305 CURSOR c_tsdv (
306 cp_tracking_id IGS_TR_ITEM.tracking_id%TYPE) IS
307 SELECT MAX(tsdv.action_dt)
308 FROM IGS_TR_STEP_V tsdv
309 WHERE tsdv.tracking_id = cp_tracking_id;
310 CURSOR c_trs (
311 cp_tracking_id IGS_TR_ITEM.tracking_id%TYPE) IS
312 SELECT trs.tracking_step_id,
313 trs.s_tracking_step_type
314 FROM IGS_TR_STEP trs
315 WHERE trs.tracking_id = cp_tracking_id;
316 CURSOR c_tts (
317 cp_tracking_type IGS_TR_TYPE_STEP.tracking_type%TYPE,
318 cp_tracking_type_step_id IGS_TR_TYPE_STEP.tracking_type_step_id%TYPE) IS
319 SELECT tts.recipient_id
320 FROM IGS_TR_TYPE_STEP tts
321 WHERE tracking_type = cp_tracking_type AND
322 tracking_type_step_id = cp_tracking_type_step_id;
323 CURSOR c_tpm (
324 cp_person_id IGS_RE_THS_PNL_MBR.ca_person_id%TYPE,
325 cp_ca_sequence_number IGS_RE_THS_PNL_MBR.ca_sequence_number%TYPE,
326 cp_the_sequence_number IGS_RE_THS_PNL_MBR.the_sequence_number%TYPE,
327 cp_creation_dt IGS_RE_THS_PNL_MBR.creation_dt%TYPE) IS
328 SELECT tpm.person_id
329 FROM IGS_RE_THS_PNL_MBR tpm,
330 IGS_RE_THS_PNL_MR_TP tpmt
331 WHERE tpm.ca_person_id = cp_person_id AND
332 tpm.ca_sequence_number = cp_ca_sequence_number AND
333 tpm.the_sequence_number = cp_the_sequence_number AND
334 tpm.creation_dt = cp_creation_dt AND
335 tpm.confirmed_dt IS NOT NULL AND
336 tpmt.panel_member_type = tpm.panel_member_type AND
337 tpmt.panel_chair_ind = 'Y';
338 CURSOR CUR_IGS_TR_STEP ( cp_tracking_id IGS_TR_STEP.tracking_id%TYPE,
339 cp_tracking_STEP_id IGS_TR_STEP.tracking_STEP_id%TYPE) IS
340 SELECT rowid , IGS_TR_STEP.*
341 FROM IGS_TR_STEP
342 WHERE tracking_id = CP_tracking_id AND
343 tracking_step_id = CP_tracking_step_id;
344 CURSOR CUR_IGS_TR_ITEM ( cp_tracking_id IGS_TR_ITEM.tracking_id%TYPE)IS
345 SELECT rowid , IGS_TR_ITEM.*
346 FROM IGS_TR_ITEM
347 WHERE tracking_id = CP_tracking_id;
348
349 BEGIN
350 -- Set the default message number and issue a savepoint
351 p_message_name := NULL;
352 SAVEPOINT s_before_insert;
353 -- Retrieve the principal supervisor ID
354 OPEN c_rst_rsup;
355 FETCH c_rst_rsup INTO v_person_id;
356 IF c_rst_rsup%NOTFOUND THEN
357 v_principal_id := NULL;
358 ELSE
359 v_principal_id := v_person_id;
360 END IF;
361 CLOSE c_rst_rsup;
362 -- Retrieve the ID of the IGS_PS_UNIT chair
363 OPEN c_tpm (p_person_id, p_ca_sequence_number,
364 p_the_sequence_number, p_creation_dt);
365 FETCH c_tpm INTO v_person_id;
366 IF c_tpm%NOTFOUND THEN
367 v_chair_id := NULL;
368 ELSE
369 v_chair_id := v_person_id;
370 END IF;
371 CLOSE c_tpm;
372 -- Set the originator id.
373
374 OPEN c_pe;
375 FETCH c_pe INTO v_current_person_id;
376
377 IF c_pe%FOUND THEN
378 CLOSE c_pe;
379 v_originator_id := v_current_person_id;
380 ELSE
381 CLOSE c_pe;
382 IF v_principal_id IS NOT NULL THEN
383 v_originator_id := v_principal_id;
384 ELSE
385 v_originator_id := p_person_id;
386 END IF;
387 END IF;
388 -- Determine the tracking type from the system type.
389 OPEN c_tpt (p_thesis_panel_type);
390 FETCH c_tpt INTO v_tracking_type;
391 IF c_tpt%NOTFOUND THEN
392 CLOSE c_tpt;
393 RETURN NULL;
394 ELSIF v_tracking_type IS NULL THEN
395 CLOSE c_tpt;
396 p_message_name := 'IGS_RE_CANT_LOC_TRK_TYPE_THES';
397 RETURN NULL;
398 END IF;
399 CLOSE c_tpt;
400 -- Determine the active tracking status.
401 OPEN c_ts;
402 FETCH c_ts INTO v_tracking_status;
403 IF c_ts%NOTFOUND THEN
404 CLOSE c_ts;
405 p_message_name := 'IGS_RE_CANT_FIND_TRK_STATUS';
406 RETURN NULL;
407 END IF;
408 CLOSE c_ts;
409 -- Call routine to insert a tracking item of the appropriate type.
410 IGS_TR_GEN_002.trkp_ins_trk_item (
411 v_tracking_status,
412 v_tracking_type,
413 p_person_id,
414 SYSDATE, -- tracking start date
415 NULL, -- target days
416 NULL, -- sequence indicator
417 NULL, -- business days
418 v_originator_id, -- originator
419 'Y', -- s_created_ind
420 v_tracking_id, -- OUT NOCOPY
421 v_message_name);
422 IF v_message_name IS NOT NULL THEN
423 ROLLBACK TO s_before_insert;
424 p_message_name := v_message_name;
425 RETURN NULL;
426 END IF;
427 -- Update the recipient IDs for the tracking steps.
428 FOR v_trs_rec IN c_trs (v_tracking_id)
429 LOOP
430 IF v_trs_rec.s_tracking_step_type = cst_stst_principal THEN
431 v_recipient_id := NVL(v_principal_id,v_originator_id);
432 ELSIF v_trs_rec.s_tracking_step_type = cst_stst_student THEN
433 v_recipient_id := p_person_id;
434 ELSIF v_trs_rec.s_tracking_step_type = cst_stst_originator THEN
435 v_recipient_id := v_originator_id;
436 ELSIF v_trs_rec.s_tracking_step_type = cst_stst_chair THEN
437 v_recipient_id := v_chair_id;
438 ELSE
439 v_recipient_id := NULL;
440 END IF;
441 IF v_recipient_id IS NULL THEN
442 OPEN c_tts( v_tracking_type,
443 v_trs_rec.tracking_step_id);
444 FETCH c_tts INTO v_tts_recipient_id;
445 IF c_tts%FOUND THEN
446 v_recipient_id := v_tts_recipient_id;
447 END IF;
448 CLOSE c_tts;
449 END IF;
450 IF v_recipient_id IS NOT NULL THEN
451 -- Call routine to update the current step.
452 IF NOT IGS_TR_GEN_002.trkp_upd_trst (
453 v_tracking_id,
454 v_trs_rec.tracking_step_id, -- tracking step ID
455 v_trs_rec.s_tracking_step_type, -- system tracking step type
456 NULL, -- action date
457 NULL, -- completion date
458 NULL, -- step completion indicator
459 NULL, -- by pass indicator
460 v_recipient_id, -- recipient_id
461 v_message_name) THEN
462 ROLLBACK TO s_before_insert;
463 p_message_name := v_message_name;
464 RETURN NULL;
465 END IF;
466 ELSE
467 -- Clear the recipient ID (IGS_GE_NOTE: no locking check as the record has been
468 -- created by this routine.
469 BEGIN
470 FOR IGS_TR_STEP_REC IN CUR_IGS_TR_STEP(V_TRACKING_ID, v_trs_rec.tracking_step_id)
471 LOOP
472 IGS_TR_STEP_PKG.UPDATE_ROW(
473 X_ROWID => IGS_TR_STEP_REC.ROWID,
474 X_TRACKING_ID => IGS_TR_STEP_REC.TRACKING_ID,
475 X_TRACKING_STEP_ID => IGS_TR_STEP_REC.TRACKING_STEP_ID,
476 X_TRACKING_STEP_NUMBER => IGS_TR_STEP_REC.TRACKING_STEP_NUMBER,
477 X_DESCRIPTION => IGS_TR_STEP_REC.DESCRIPTION,
478 X_S_TRACKING_STEP_TYPE => IGS_TR_STEP_REC.S_TRACKING_STEP_TYPE ,
479 X_COMPLETION_DT => IGS_TR_STEP_REC.COMPLETION_DT,
480 X_ACTION_DAYS => IGS_TR_STEP_REC.ACTION_DAYS,
481 X_STEP_COMPLETION_IND => IGS_TR_STEP_REC.STEP_COMPLETION_IND ,
482 X_BY_PASS_IND => IGS_TR_STEP_REC.BY_PASS_IND ,
483 X_RECIPIENT_ID => NULL,
484 --add 3 new fields in the tbh call
485 -- for tracking dld nov 2001 release bug#1837257
486 X_STEP_GROUP_ID => IGS_TR_STEP_REC.STEP_GROUP_ID,
487 X_PUBLISH_IND => IGS_TR_STEP_REC.PUBLISH_IND,
488 X_STEP_CATALOG_CD => IGS_TR_STEP_REC.STEP_CATALOG_CD,
489 X_MODE => 'R');
490 END LOOP;
491 END;
492 END IF;
493 END LOOP;
494 -- Update the target days of the item. This is done in two separate queries
495 -- as the view does quite a bit of processing and this is considered to be
496 -- the most efficient approach.
497 OPEN c_tri (
498 v_tracking_id);
499 FETCH c_tri INTO v_start_dt,
500 v_business_days_ind;
501 CLOSE c_tri;
502 OPEN c_tsdv (
503 v_tracking_id);
504 FETCH c_tsdv INTO v_action_dt;
505 CLOSE c_tsdv;
506 v_target_days := IGS_TR_GEN_001.trkp_clc_days_ovrdue (
507 v_start_dt,
508 v_action_dt,
509 v_business_days_ind);
510 BEGIN
511 FOR IGS_TR_ITEM_REC IN CUR_IGS_TR_ITEM (V_TRACKING_ID)
512 LOOP
513 IGS_TR_ITEM_REC.TARGET_DAYS := v_target_days;
514 IGS_TR_ITEM_PKG.UPDATE_ROW(
515 X_ROWID => IGS_TR_ITEM_REC.ROWID,
516 X_TRACKING_ID => IGS_TR_ITEM_REC.TRACKING_ID,
517 X_TRACKING_STATUS => IGS_TR_ITEM_REC.TRACKING_STATUS,
518 X_TRACKING_TYPE => IGS_TR_ITEM_REC.TRACKING_TYPE,
519 X_SOURCE_PERSON_ID => IGS_TR_ITEM_REC.SOURCE_PERSON_ID,
520 X_START_DT => IGS_TR_ITEM_REC.START_DT,
521 X_TARGET_DAYS => IGS_TR_ITEM_REC.TARGET_DAYS,
522 X_SEQUENCE_IND => IGS_TR_ITEM_REC.SEQUENCE_IND,
523 X_BUSINESS_DAYS_IND => IGS_TR_ITEM_REC.BUSINESS_DAYS_IND,
524 X_ORIGINATOR_PERSON_ID =>IGS_TR_ITEM_REC.ORIGINATOR_PERSON_ID ,
525 X_S_CREATED_IND => IGS_TR_ITEM_REC.S_CREATED_IND,
526 --add 3 new fields in the tbh call
527 -- for tracking dld nov 2001 release bug#1837257
528 X_COMPLETION_DUE_DT => IGS_TR_ITEM_REC.COMPLETION_DUE_DT,
529 X_OVERRIDE_OFFSET_CLC_IND => IGS_TR_ITEM_REC.OVERRIDE_OFFSET_CLC_IND ,
530 X_PUBLISH_IND => IGS_TR_ITEM_REC.PUBLISH_IND,
531 X_MODE => 'R');
532 END LOOP;
533 END;
534 RETURN v_tracking_id;
535 EXCEPTION
536 WHEN OTHERS THEN
537 IF c_pe%ISOPEN THEN
538 CLOSE c_pe;
539 END IF;
540 IF c_rst_rsup%ISOPEN THEN
541 CLOSE c_rst_rsup;
542 END IF;
543 IF c_tpt%ISOPEN THEN
544 CLOSE c_tpt;
545 END IF;
546 IF c_tpm%ISOPEN THEN
547 CLOSE c_tpm;
548 END IF;
549 IF c_ts%ISOPEN THEN
550 CLOSE c_ts;
551 END IF;
552 IF c_tri%ISOPEN THEN
553 CLOSE c_tri;
554 END IF;
555 IF c_tsdv%ISOPEN THEN
556 CLOSE c_tsdv;
557 END IF;
558 IF c_tts%ISOPEN THEN
559 CLOSE c_tts;
560 END IF;
561 RAISE;
562 END;
563 EXCEPTION
564 WHEN OTHERS THEN
565 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
566 IGS_GE_MSG_STACK.ADD;
567 App_Exception.Raise_Exception;
568 END resp_ins_tex_tri;
569
570
571 PROCEDURE RESP_INS_THE_HIST(
572 p_person_id IN NUMBER ,
573 p_ca_sequence_number IN NUMBER ,
574 p_sequence_number IN NUMBER ,
575 p_old_title IN VARCHAR2 ,
576 p_new_title IN VARCHAR2 ,
577 p_old_final_title_ind IN VARCHAR2 ,
578 p_new_final_title_ind IN VARCHAR2 ,
579 p_old_short_title IN VARCHAR2 ,
580 p_new_short_title IN VARCHAR2 ,
581 p_old_abbreviated_title IN VARCHAR2 ,
582 p_new_abbreviated_title IN VARCHAR2 ,
583 p_old_thesis_result_cd IN VARCHAR2 ,
584 p_new_thesis_result_cd IN VARCHAR2 ,
585 p_old_expected_submission_dt IN DATE ,
586 p_new_expected_submission_dt IN DATE ,
587 p_old_library_lodgement_dt IN DATE ,
588 p_new_library_lodgement_dt IN DATE ,
589 p_old_library_catalogue_number IN VARCHAR2 ,
590 p_new_library_catalogue_number IN VARCHAR2 ,
591 p_old_embargo_expiry_dt IN DATE ,
592 p_new_embargo_expiry_dt IN DATE ,
593 p_old_thesis_format IN VARCHAR2 ,
594 p_new_thesis_format IN VARCHAR2 ,
595 p_old_logical_delete_dt IN DATE ,
596 p_new_logical_delete_dt IN DATE ,
597 p_old_embargo_details IN VARCHAR2 ,
598 p_new_embargo_details IN VARCHAR2 ,
599 p_old_thesis_topic IN VARCHAR2 ,
600 p_new_thesis_topic IN VARCHAR2 ,
601 p_old_citation IN VARCHAR2 ,
602 p_new_citation IN VARCHAR2 ,
603 p_old_comments IN VARCHAR2 ,
604 p_new_comments IN VARCHAR2 ,
605 p_old_update_who IN NUMBER ,
606 p_new_update_who IN NUMBER ,
607 p_old_update_on IN DATE ,
608 p_new_update_on IN DATE )
609 AS
610 gv_other_detail VARCHAR2(255);
611 LV_ROWID VARCHAR2(25);
612 v_org_id IGS_PR_MILESTONE_HST.ORG_ID%TYPE := IGS_GE_GEN_003.Get_Org_Id;
613
614 BEGIN -- resp_ins_the_hist
615 -- Insert IGS_RE_THESIS history (IGS_RE_THESIS_HIST)
616 DECLARE
617 v_th_rec IGS_RE_THESIS_HIST%ROWTYPE;
618 v_create_history BOOLEAN := FALSE;
619 v_hist_start_dt IGS_RE_THESIS.last_update_date%TYPE;
620 v_hist_end_dt IGS_RE_THESIS.last_update_date%TYPE;
621 v_hist_who IGS_RE_THESIS.last_updated_by%TYPE;
622 BEGIN
623 -- If any of the old values (p_old_<column_name>) are different from the
624 -- associated new values (p_new_<column_name>) (with the exception the
625 -- last_updated_by and last_update_date columns) then create an IGS_RE_THESIS_HIST history
626 -- record with the old values (p_old_<column_name>). Do not set the
627 -- last_updated_by and last_update_date columns when creating the history record.
628 IF p_new_title <> p_old_title THEN
629 v_th_rec.title := p_old_title;
630 v_create_history := TRUE;
631 END IF;
632 IF p_new_final_title_ind <> p_old_final_title_ind THEN
633 v_th_rec.final_title_ind := p_old_final_title_ind;
634 v_create_history := TRUE;
635 END IF;
636 IF NVL(p_new_short_title, 'NULL') <> NVL(p_old_short_title, 'NULL') THEN
637 v_th_rec.short_title := p_old_short_title;
638 v_create_history := TRUE;
639 END IF;
640 IF NVL(p_new_abbreviated_title, 'NULL') <>
641 NVL(p_old_abbreviated_title, 'NULL') THEN
642 v_th_rec.abbreviated_title := p_old_abbreviated_title;
643 v_create_history := TRUE;
644 END IF;
645 IF NVL(p_new_thesis_result_cd, 'NULL') <>
646 NVL(p_old_thesis_result_cd, 'NULL') THEN
647 v_th_rec.thesis_result_cd := NVL(p_old_thesis_result_cd,' ');
648 v_create_history := TRUE;
649 END IF;
650 IF NVL(p_new_expected_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
651 NVL(p_old_expected_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
652 v_th_rec.expected_submission_dt := p_old_expected_submission_dt;
653 v_create_history := TRUE;
654 END IF;
655 IF NVL(p_new_library_lodgement_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
656 NVL(p_old_library_lodgement_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
657 v_th_rec.date_of_library_lodgement := p_old_library_lodgement_dt;
658 v_create_history := TRUE;
659 END IF;
660 IF NVL(p_new_library_catalogue_number, 'NULL') <>
661 NVL(p_old_library_catalogue_number, 'NULL') THEN
662 v_th_rec.library_catalogue_number := p_old_library_catalogue_number;
663 v_create_history := TRUE;
664 END IF;
665 IF NVL(p_new_embargo_expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
666 NVL(p_old_embargo_expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
667 v_th_rec.embargo_expiry_dt := p_old_embargo_expiry_dt;
668 v_create_history := TRUE;
669 END IF;
670 IF NVL(p_new_thesis_format, 'NULL') <> NVL(p_old_thesis_format, 'NULL') THEN
671 v_th_rec.thesis_format := p_old_thesis_format;
672 v_create_history := TRUE;
673 END IF;
674 IF NVL(p_new_logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
675 NVL(p_old_logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
676 v_th_rec.logical_delete_dt := p_old_logical_delete_dt;
677 v_create_history := TRUE;
678 END IF;
679 IF NVL(p_new_embargo_details, 'NULL') <>
680 NVL(p_old_embargo_details, 'NULL') THEN
681 v_th_rec.embargo_details := p_old_embargo_details;
682 v_create_history := TRUE;
683 END IF;
684 IF NVL(p_new_thesis_topic, 'NULL') <> NVL(p_old_thesis_topic, 'NULL') THEN
685 v_th_rec.thesis_topic := p_old_thesis_topic;
686 v_create_history := TRUE;
687 END IF;
688 IF NVL(p_new_citation, 'NULL') <> NVL(p_old_citation, 'NULL') THEN
689 v_th_rec.citation := p_old_citation;
690 v_create_history := TRUE;
691 END IF;
692 IF NVL(p_new_comments, 'NULL') <> NVL(p_old_comments, 'NULL') THEN
693 v_th_rec.comments := p_old_comments;
694 v_create_history := TRUE;
695 END IF;
696 IF v_create_history = TRUE THEN
697 v_th_rec.person_id := p_person_id;
698 v_th_rec.ca_sequence_number := p_ca_sequence_number;
699 v_th_rec.sequence_number := p_sequence_number;
700 v_th_rec.hist_start_dt := p_old_update_on;
701 v_th_rec.hist_end_dt := NVL(p_new_update_on,SYSDATE);
702 v_th_rec.hist_who := p_old_update_who;
703 IGS_RE_THESIS_HIST_PKG.INSERT_ROW(
704 X_ROWID => LV_ROWID,
705 X_person_id => v_th_rec.person_id,
706 X_ca_sequence_number => v_th_rec.ca_sequence_number,
707 X_sequence_number => v_th_rec.sequence_number,
708 X_hist_start_dt => v_th_rec.hist_start_dt,
709 X_hist_end_dt => v_th_rec.hist_end_dt,
710 X_hist_who => v_th_rec.hist_who,
711 X_title => v_th_rec.title,
712 X_final_title_ind => v_th_rec.final_title_ind,
713 X_short_title => v_th_rec.short_title,
714 X_abbreviated_title => v_th_rec.abbreviated_title,
715 X_thesis_result_cd => v_th_rec.thesis_result_cd,
716 X_expected_submission_dt => v_th_rec.expected_submission_dt,
717 X_date_of_library_lodgement => v_th_rec.date_of_library_lodgement,
718 X_library_catalogue_number => v_th_rec.library_catalogue_number,
719 X_embargo_expiry_dt => v_th_rec.embargo_expiry_dt,
720 X_thesis_format => v_th_rec.thesis_format,
721 X_logical_delete_dt => v_th_rec.logical_delete_dt,
722 X_embargo_details => v_th_rec.embargo_details,
723 X_thesis_topic => v_th_rec.thesis_topic,
724 X_citation => v_th_rec.citation,
725 X_comments => v_th_rec.comments,
726 X_Org_Id => v_org_id,
727 X_MODE => 'R');
728 END IF;
729 END;
730 EXCEPTION
731 WHEN OTHERS THEN
732 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
733 IGS_GE_MSG_STACK.ADD;
734 App_Exception.Raise_Exception;
735 END resp_ins_the_hist;
736
737
738 PROCEDURE RESP_INS_TPM_HIST(
739 p_ca_person_id IN NUMBER ,
740 p_ca_sequence_number IN NUMBER ,
741 p_the_sequence_number IN NUMBER ,
742 p_creation_dt IN DATE ,
743 p_person_id IN NUMBER ,
744 p_old_panel_member_type IN VARCHAR2 ,
745 p_new_panel_member_type IN VARCHAR2 ,
746 p_old_confirmed_dt IN DATE ,
747 p_new_confirmed_dt IN DATE ,
748 p_old_declined_dt IN DATE ,
749 p_new_declined_dt IN DATE ,
750 p_old_anonymity_ind IN VARCHAR2 ,
751 p_new_anonymity_ind IN VARCHAR2 ,
752 p_old_thesis_result_cd IN VARCHAR2 ,
753 p_new_thesis_result_cd IN VARCHAR2 ,
754 p_old_paid_dt IN DATE ,
755 p_new_paid_dt IN DATE ,
756 p_old_tracking_id IN NUMBER ,
757 p_new_tracking_id IN NUMBER ,
758 p_old_recommendation_summary IN VARCHAR2 ,
759 p_new_recommendation_summary IN VARCHAR2 ,
760 p_old_update_who IN NUMBER ,
761 p_new_update_who IN NUMBER ,
762 p_old_update_on IN DATE ,
763 p_new_update_on IN DATE )
764 AS
765 gv_other_detail VARCHAR2(255);
766 LV_ROWID VARCHAR2(25);
767 v_org_id IGS_PR_MILESTONE_HST.ORG_ID%TYPE := IGS_GE_GEN_003.Get_Org_Id;
768 BEGIN -- resp_ins_tpm_hist
769 -- Insert IGS_RE_THS_PNL_MBR history (IGS_RE_THS_PNL_MR_HS)
770 DECLARE
771 v_tpmh_rec IGS_RE_THS_PNL_MR_HS%ROWTYPE;
772 v_create_history BOOLEAN := FALSE;
773 v_hist_start_dt IGS_RE_THS_PNL_MBR.last_update_date%TYPE;
774 v_hist_end_dt IGS_RE_THS_PNL_MBR.last_update_date%TYPE;
775 v_hist_who IGS_RE_THS_PNL_MBR.last_updated_by%TYPE;
776 BEGIN
777 -- If any of the old values (p_old_<column_name>) are different from the
778 -- associated new values (p_new_<column_name>) (with the exception the
779 -- last_updated_by and last_update_date columns) then create an IGS_RE_THS_EXAM_HIST history
780 -- record with the old values (p_old_<column_name>). Do not set the
781 -- last_updated_by and last_update_date columns when creating the history record.
782 IF p_new_panel_member_type <> p_old_panel_member_type THEN
783 v_tpmh_rec.panel_member_type := p_old_panel_member_type;
784 v_create_history := TRUE;
785 END IF;
786 IF NVL(p_new_confirmed_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
787 NVL(p_old_confirmed_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
788 v_tpmh_rec.confirmed_dt := p_old_confirmed_dt;
789 v_create_history := TRUE;
790 END IF;
791 IF NVL(p_new_declined_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
792 NVL(p_old_declined_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
793 v_tpmh_rec.declined_dt := p_old_declined_dt;
794 v_create_history := TRUE;
795 END IF;
796 IF NVL(p_new_anonymity_ind, 'NULL') <> NVL(p_old_anonymity_ind, 'NULL') THEN
797 v_tpmh_rec.anonymity_ind := p_old_anonymity_ind;
798 v_create_history := TRUE;
799 END IF;
800 IF NVL(p_new_thesis_result_cd, 'NULL') <>
801 NVL(p_old_thesis_result_cd, 'NULL') THEN
802 v_tpmh_rec.thesis_result_cd := NVL(p_old_thesis_result_cd, ' ');
803 v_create_history := TRUE;
804 END IF;
805 IF NVL(p_new_paid_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
806 NVL(p_old_paid_dt, IGS_GE_DATE.IGSDATE('1900/01/01' )) THEN
807 v_tpmh_rec.paid_dt := p_old_paid_dt;
808 v_create_history := TRUE;
809 END IF;
810 IF NVL(p_new_tracking_id, -1) <> NVL(p_old_tracking_id, -1) THEN
811 v_tpmh_rec.tracking_id := p_old_tracking_id;
812 v_create_history := TRUE;
813 END IF;
814 IF NVL(p_new_recommendation_summary, 'NULL') <>
815 NVL(p_old_recommendation_summary, 'NULL') THEN
816 v_tpmh_rec.recommendation_summary := p_old_recommendation_summary;
817 v_create_history := TRUE;
818 END IF;
819 -- create a history record if any column has changed
820 IF v_create_history = TRUE THEN
821 v_tpmh_rec.ca_person_id := p_ca_person_id;
822 v_tpmh_rec.ca_sequence_number := p_ca_sequence_number;
823 v_tpmh_rec.the_sequence_number := p_the_sequence_number;
824 v_tpmh_rec.creation_dt := p_creation_dt;
825 v_tpmh_rec.person_id := p_person_id;
826 v_tpmh_rec.hist_start_dt := p_old_update_on;
827 v_tpmh_rec.hist_end_dt := NVL(p_new_update_on,SYSDATE);
828 v_tpmh_rec.hist_who := p_old_update_who;
829 IGS_RE_THS_PNL_MR_HS_PKG.INSERT_ROW(
830 X_ROWID => LV_ROWID,
831 X_ca_person_id => v_tpmh_rec.ca_person_id,
832 X_ca_sequence_number => v_tpmh_rec.ca_sequence_number,
833 X_the_sequence_number => v_tpmh_rec.the_sequence_number,
834 X_creation_dt => v_tpmh_rec.creation_dt,
835 X_person_id => v_tpmh_rec.person_id,
836 X_hist_start_dt => v_tpmh_rec.hist_start_dt,
837 X_hist_end_dt => v_tpmh_rec.hist_end_dt,
838 X_hist_who => v_tpmh_rec.hist_who,
839 X_panel_member_type => v_tpmh_rec.panel_member_type,
840 X_confirmed_dt => v_tpmh_rec.confirmed_dt,
841 X_declined_dt => v_tpmh_rec.declined_dt,
842 X_anonymity_ind => v_tpmh_rec.anonymity_ind,
843 X_thesis_result_cd => v_tpmh_rec.thesis_result_cd,
844 X_paid_dt => v_tpmh_rec.paid_dt,
845 X_tracking_id => v_tpmh_rec.tracking_id,
846 X_recommendation_summary => v_tpmh_rec.recommendation_summary,
847 X_Org_Id => v_org_id,
848 X_MODE => 'R');
849
850 END IF;
851 END;
852 EXCEPTION
853 WHEN OTHERS THEN
854 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
855 IGS_GE_MSG_STACK.ADD;
856 App_Exception.Raise_Exception;
857 END resp_ins_tpm_hist;
858
859
860 FUNCTION RESP_INS_TPM_TRI(
861 p_ca_person_id IN NUMBER ,
862 p_ca_sequence_number IN NUMBER ,
863 p_person_id IN NUMBER ,
864 p_panel_member_type IN VARCHAR2 ,
865 p_message_name OUT NOCOPY VARCHAR2 )
866 --add 3 new fields in the tbh calls to IGS_TR_ITEM and IGS_TR_STEP
867 -- for tracking dld nov 2001 release bug#1837257
868 /*-----------------------------------------------------------------------
869 who when what
870 svanukur 07-sep-2004 modified cursor c_pe for performance bug 3866423
871 --------------------------------------------------------------------------*/
872 RETURN NUMBER AS
873 gv_other_detail VARCHAR2(255);
874 BEGIN -- resp_ins_tpm_tri
875 -- Insert tracking item for a IGS_RE_THS_PNL_MBR.
876 -- The routine returns the tracking ID of the item created.
877 -- IGS_GE_NOTE: A commit is not done from this routine as it is done as part of
878 -- the calling routine.
879 DECLARE
880 cst_s_tracking_type CONSTANT VARCHAR2(10) := 'RES_TPM';
881 cst_stst_person CONSTANT VARCHAR2(10) := 'RES_TPM_PE';
882 cst_stst_originator CONSTANT VARCHAR2(10) := 'RES_TPM_OR';
883 v_message_name VARCHAR2(30);
884 v_current_person_id IGS_PE_PERSON.person_id%TYPE;
885 v_originator_id IGS_RE_THS_PNL_MBR.person_id%TYPE;
886 v_recipient_id IGS_TR_STEP.recipient_id%TYPE;
887 v_tts_recipient_id IGS_TR_TYPE_STEP.recipient_id%TYPE;
888 v_tracking_id IGS_TR_ITEM.tracking_id%TYPE;
889 v_target_days NUMBER;
890 v_tracking_type IGS_TR_TYPE.tracking_type%TYPE;
891 v_tracking_status IGS_TR_STATUS.tracking_status%TYPE;
892 v_person_id IGS_RE_SPRVSR.person_id%TYPE;
893 v_start_dt IGS_TR_ITEM.start_dt%TYPE;
894 v_business_days_ind IGS_TR_ITEM.business_days_ind%TYPE;
895 v_action_dt IGS_TR_STEP_V.action_dt%TYPE;
896 CURSOR c_pe IS
897 SELECT PERSON_PARTY_ID
898 FROM fnd_user
899 WHERE user_id = fnd_global.user_id;
900 CURSOR c_rst_rsup IS
901 SELECT rsup.person_id
902 FROM IGS_RE_SPRVSR rsup,
903 IGS_RE_SPRVSR_TYPE rst
904 WHERE rsup.ca_person_id = p_ca_person_id AND
905 rsup.ca_sequence_number = p_ca_sequence_number AND
906 rst.research_supervisor_type = rsup.research_supervisor_type AND
907 rst.principal_supervisor_ind = 'Y'
908 ORDER BY rsup.supervision_percentage DESC;
909 CURSOR c_tmpt (
910 cp_panel_member_type IGS_RE_THS_PNL_MR_TP.panel_member_type%TYPE) IS
911 SELECT tmpt.tracking_type
912 FROM IGS_RE_THS_PNL_MR_TP tmpt
913 WHERE tmpt.panel_member_type = cp_panel_member_type;
914 CURSOR c_ts IS
915 SELECT ts.tracking_status
916 FROM IGS_TR_STATUS ts
917 WHERE ts.s_tracking_status = 'ACTIVE' AND
918 ts.closed_ind = 'N'
919 ORDER BY ts.tracking_status ASC;
920 CURSOR c_tri (
921 cp_tracking_id IGS_TR_ITEM.tracking_id%TYPE) IS
922 SELECT tri.start_dt,
923 tri.business_days_ind
924 FROM IGS_TR_ITEM tri
925 WHERE tri.tracking_id = cp_tracking_id;
926 CURSOR c_tsdv (
927 cp_tracking_id IGS_TR_ITEM.tracking_id%TYPE) IS
928 SELECT MAX(tsdv.action_dt)
929 FROM IGS_TR_STEP_V tsdv
930 WHERE tsdv.tracking_id = cp_tracking_id;
931 CURSOR c_trs (
932 cp_tracking_id IGS_TR_ITEM.tracking_id%TYPE) IS
933 SELECT trs.tracking_step_id,
934 trs.s_tracking_step_type
935 FROM IGS_TR_STEP trs
936 WHERE trs.tracking_id = cp_tracking_id;
937 CURSOR c_tts (
938 cp_tracking_type IGS_TR_TYPE_STEP.tracking_type%TYPE,
939 cp_tracking_type_step_id IGS_TR_TYPE_STEP.tracking_type_step_id%TYPE) IS
940 SELECT tts.recipient_id
941 FROM IGS_TR_TYPE_STEP tts
942 WHERE tracking_type = cp_tracking_type AND
943 tracking_type_step_id = cp_tracking_type_step_id;
944 CURSOR CUR_IGS_TR_STEP ( cp_tracking_id IGS_TR_STEP.tracking_id%TYPE,
945 cp_tracking_STEP_id IGS_TR_STEP.tracking_STEP_id%TYPE) IS
946 SELECT rowid , IGS_TR_STEP.*
947 FROM IGS_TR_STEP
948 WHERE tracking_id = CP_tracking_id AND
949 tracking_step_id = CP_tracking_step_id;
950 CURSOR CUR_IGS_TR_ITEM ( cp_tracking_id IGS_TR_ITEM.tracking_id%TYPE)IS
951 SELECT rowid , IGS_TR_ITEM.*
952 FROM IGS_TR_ITEM
953 WHERE tracking_id = CP_tracking_id;
954
955
956
957 BEGIN
958 p_message_name := NULL;
959 SAVEPOINT s_before_update;
960 -- Get the originator IGS_PE_PERSON ID, being the principal supervisor with the
961 -- greatest supervising load. If multiple principal supervisors exist, then
962 -- use the first.
963
964 OPEN c_pe;
965 FETCH c_pe INTO v_current_person_id;
966 IF c_pe%FOUND THEN
967 CLOSE c_pe;
968 v_originator_id := v_current_person_id;
969 ELSE
970 CLOSE c_pe;
971 OPEN c_rst_rsup;
972 FETCH c_rst_rsup INTO v_person_id;
973 IF c_rst_rsup%NOTFOUND THEN
974 CLOSE c_rst_rsup;
975 v_originator_id := p_ca_person_id;
976 ELSE
977 CLOSE c_rst_rsup;
978 v_originator_id := v_person_id;
979 END IF;
980 END IF;
981 -- Determine the tracking type from the system type.
982 OPEN c_tmpt (p_panel_member_type);
983 FETCH c_tmpt INTO v_tracking_type;
984 IF c_tmpt%NOTFOUND THEN
985 CLOSE c_tmpt;
986 RETURN NULL;
987 ELSIF v_tracking_type IS NULL THEN
988 CLOSE c_tmpt;
989 p_message_name := 'IGS_RE_CANT_LOC_TRK_TYPE_MEM';
990 RETURN NULL;
991 END IF;
992 CLOSE c_tmpt;
993 -- Determine the active tracking status.
994 OPEN c_ts;
995 FETCH c_ts INTO v_tracking_status;
996 IF c_ts%NOTFOUND THEN
997 CLOSE c_ts;
998 p_message_name := 'IGS_RE_CANT_FIND_TRK_STATUS';
999 RETURN NULL;
1000 END IF;
1001 CLOSE c_ts;
1002 -- Call routine to insert a tracking item of the appropriate type.
1003 IGS_TR_GEN_002.trkp_ins_trk_item (
1004 v_tracking_status,
1005 v_tracking_type,
1006 p_person_id,
1007 SYSDATE, -- tracking start date
1008 NULL, -- target days
1009 NULL, -- sequence indicator
1010 NULL, -- business days
1011 v_originator_id, -- originator
1012 'Y', -- s_created_ind
1013 v_tracking_id, -- OUT NOCOPY
1014 v_message_name);
1015 IF v_message_name IS NOT NULL THEN
1016 ROLLBACK TO s_before_update;
1017 p_message_name := v_message_name;
1018 RETURN NULL;
1019 END IF;
1020 FOR v_trs_rec IN c_trs (v_tracking_id)
1021 LOOP
1022 IF v_trs_rec.s_tracking_step_type = cst_stst_person THEN
1023 v_recipient_id := p_person_id;
1024 ELSIF v_trs_rec.s_tracking_step_type = cst_stst_originator THEN
1025 v_recipient_id := v_originator_id;
1026 ELSE
1027 v_recipient_id := NULL;
1028 END IF;
1029 IF v_recipient_id IS NULL THEN
1030 OPEN c_tts( v_tracking_type,
1031 v_trs_rec.tracking_step_id);
1032 FETCH c_tts INTO v_tts_recipient_id;
1033 IF c_tts%FOUND THEN
1034 v_recipient_id := v_tts_recipient_id;
1035 END IF;
1036 CLOSE c_tts;
1037 END IF;
1038 IF v_recipient_id IS NOT NULL THEN
1039 -- Update the recipient ID to the panel member for steps linked to the
1040 -- appropriate s_tracking_step_type.
1041 IF NOT IGS_TR_GEN_002.trkp_upd_trst (
1042 v_tracking_id,
1043 v_trs_rec.tracking_step_id, -- tracking step ID
1044 v_trs_rec.s_tracking_step_type, -- system tracking step type
1045 NULL, -- action date
1046 NULL, -- completion date
1047 NULL, -- step completion indicator
1048 NULL, -- by pass indicator
1049 v_recipient_id, -- recipient_id
1050 v_message_name) THEN
1051 ROLLBACK TO s_before_update;
1052 p_message_name := v_message_name;
1053 RETURN NULL;
1054 END IF;
1055 ELSE
1056 -- Clear the recipient ID.
1057 BEGIN
1058 FOR IGS_TR_STEP_REC IN CUR_IGS_TR_STEP (v_tracking_id, v_trs_rec.tracking_step_id)
1059 LOOP
1060
1061 IGS_TR_STEP_PKG.UPDATE_ROW(
1062 X_ROWID => IGS_TR_STEP_REC.ROWID,
1063 X_TRACKING_ID => IGS_TR_STEP_REC.TRACKING_ID,
1064 X_TRACKING_STEP_ID => IGS_TR_STEP_REC.TRACKING_STEP_ID,
1065 X_TRACKING_STEP_NUMBER => IGS_TR_STEP_REC.TRACKING_STEP_NUMBER,
1066 X_DESCRIPTION => IGS_TR_STEP_REC.DESCRIPTION,
1067 X_S_TRACKING_STEP_TYPE => IGS_TR_STEP_REC.S_TRACKING_STEP_TYPE ,
1068 X_COMPLETION_DT => IGS_TR_STEP_REC.COMPLETION_DT ,
1069 X_ACTION_DAYS => IGS_TR_STEP_REC.ACTION_DAYS,
1070 X_STEP_COMPLETION_IND => IGS_TR_STEP_REC.STEP_COMPLETION_IND ,
1071 X_BY_PASS_IND => IGS_TR_STEP_REC.BY_PASS_IND ,
1072 X_RECIPIENT_ID => NULL,
1073 --add 3 new fields in the tbh call
1074 -- for tracking dld nov 2001 release bug#1837257
1075 X_STEP_GROUP_ID => IGS_TR_STEP_REC.STEP_GROUP_ID,
1076 X_PUBLISH_IND => IGS_TR_STEP_REC.PUBLISH_IND,
1077 X_STEP_CATALOG_CD => IGS_TR_STEP_REC.STEP_CATALOG_CD,
1078 X_MODE => 'R');
1079 END LOOP;
1080 END;
1081 END IF;
1082 END LOOP;
1083 -- Update the target days of the item. This is done in two separate queries
1084 -- as the view does quite a bit of processing and this is considered to be
1085 -- the most efficient approach.
1086 OPEN c_tri (
1087 v_tracking_id);
1088 FETCH c_tri INTO v_start_dt,
1089 v_business_days_ind;
1090 CLOSE c_tri;
1091 OPEN c_tsdv (
1092 v_tracking_id);
1093 FETCH c_tsdv INTO v_action_dt;
1094 CLOSE c_tsdv;
1095 v_target_days := IGS_TR_GEN_001.trkp_clc_days_ovrdue (
1096 v_start_dt,
1097 v_action_dt,
1098 v_business_days_ind);
1099 BEGIN
1100 FOR IGS_TR_ITEM_REC IN CUR_IGS_TR_ITEM (V_TRACKING_ID)
1101 LOOP
1102 IGS_TR_ITEM_REC.TARGET_DAYS := v_target_days;
1103 IGS_TR_ITEM_PKG.UPDATE_ROW(
1104 X_ROWID => IGS_TR_ITEM_REC.ROWID,
1105 X_TRACKING_ID => IGS_TR_ITEM_REC.TRACKING_ID,
1106 X_TRACKING_STATUS => IGS_TR_ITEM_REC.TRACKING_STATUS,
1107 X_TRACKING_TYPE => IGS_TR_ITEM_REC.TRACKING_TYPE,
1108 X_SOURCE_PERSON_ID => IGS_TR_ITEM_REC.SOURCE_PERSON_ID,
1109 X_START_DT => IGS_TR_ITEM_REC.START_DT,
1110 X_TARGET_DAYS => IGS_TR_ITEM_REC.TARGET_DAYS,
1111 X_SEQUENCE_IND => IGS_TR_ITEM_REC.SEQUENCE_IND,
1112 X_BUSINESS_DAYS_IND => IGS_TR_ITEM_REC.BUSINESS_DAYS_IND,
1113 X_ORIGINATOR_PERSON_ID =>IGS_TR_ITEM_REC.ORIGINATOR_PERSON_ID ,
1114 X_S_CREATED_IND => IGS_TR_ITEM_REC.S_CREATED_IND,
1115 --add 3 new fields in the tbh call
1116 -- for tracking dld nov 2001 release bug#1837257
1117 X_COMPLETION_DUE_DT => IGS_TR_ITEM_REC.COMPLETION_DUE_DT,
1118 X_OVERRIDE_OFFSET_CLC_IND => IGS_TR_ITEM_REC.OVERRIDE_OFFSET_CLC_IND ,
1119 X_PUBLISH_IND => IGS_TR_ITEM_REC.PUBLISH_IND,
1120 X_MODE => 'R');
1121 END LOOP;
1122 END;
1123 RETURN v_tracking_id;
1124 EXCEPTION
1125 WHEN OTHERS THEN
1126 IF c_pe%ISOPEN THEN
1127 CLOSE c_pe;
1128 END IF;
1129 IF c_rst_rsup%ISOPEN THEN
1130 CLOSE c_rst_rsup;
1131 END IF;
1132 IF c_tmpt%ISOPEN THEN
1133 CLOSE c_tmpt;
1134 END IF;
1135 IF c_ts%ISOPEN THEN
1136 CLOSE c_ts;
1137 END IF;
1138 IF c_tri%ISOPEN THEN
1139 CLOSE c_tri;
1140 END IF;
1141 IF c_tsdv%ISOPEN THEN
1142 CLOSE c_tsdv;
1143 END IF;
1144 IF c_tts%ISOPEN THEN
1145 CLOSE c_tts;
1146 END IF;
1147 RAISE;
1148 END;
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1152 IGS_GE_MSG_STACK.ADD;
1153 App_Exception.Raise_Exception;
1154 END resp_ins_tpm_tri;
1155
1156
1157 END IGS_RE_GEN_003 ;
[Home] [Help]