1 PACKAGE BODY igs_as_prod_doc AS
2 /* $Header: IGSAS49B.pls 120.1 2005/09/19 01:34:27 appldev ship $ */
3
4
5
6 FUNCTION get_hold_status (
7 p_person_id NUMBER
8 ) RETURN VARCHAR2 AS
9 /*************************************************************
10 Created By :Imran Jeddy
11 Date Created on : 16-Feb-2004
12 Purpose : This process is called by the BulkOrderVO to see if the student has a hold placed on him/her.
13 Know limitations, enhancements or remarks
14 CHANGE HISTORY
15 Who When What
16 (reverse chronological order - newest change first)
17 ***************************************************************/
18 -- Cursor to get the institution setup for documents.
19 --
20 CURSOR c_setup IS
21 SELECT 'X'
22 FROM igs_as_docproc_stup
23 WHERE trans_request_if_hold_ind = 'N';
24 --
25 -- Cursor to get the holds information for the student which has effect of blocking his transcript
26 --
27 CURSOR c_hold IS
28 SELECT 'X'
29 FROM igs_pe_persenc_effct
30 WHERE person_id = p_person_id
31 AND s_encmb_effect_type IN ('TRANS_BLK','RVK_SRVC','SUS_SRVC','RESULT_BLK')
32 AND NVL (expiry_dt, SYSDATE) >= SYSDATE
33 AND pee_start_dt < SYSDATE
34 ORDER BY pee_start_dt DESC;
35 --
36 l_var VARCHAR2(1);
37 --
38 BEGIN
39
40 -- Setup allows a Transcript to be processed even if there is a hold.
41 OPEN c_setup;
42 FETCH c_setup INTO l_var;
43 IF c_setup%FOUND THEN
44 CLOSE c_setup;
45 RETURN 'Y';
46 END IF;
47 CLOSE c_setup;
48
49 --Check if there is a hold on the student
50 OPEN c_hold ;
51 FETCH c_hold INTO l_var;
52 IF c_hold%FOUND THEN
53 CLOSE c_hold;
54 RETURN 'N';
55 END IF;
56 CLOSE c_hold;
57 RETURN 'Y';
58 END get_hold_status;
59
60
61
62
63
64
65 PROCEDURE asp_chk_doc_rdns (
66 p_item_number IN NUMBER,
67 p_document_ready OUT NOCOPY VARCHAR2,
68 p_error_mesg OUT NOCOPY VARCHAR2
69 ) AS
70 /*************************************************************
71 Created By :Nalin Kumar
72 Date Created on : 21-Aug-2002
73 Purpose : This process is called by the report producing the documents to check the readiness for production of a document.
74 Know limitations, enhancements or remarks
75 CHANGE HISTORY
76 Who When What
77 anilk 22-Aug-2003 Fixed GSCC - Date conversion must use date format mask
78 (reverse chronological order - newest change first)
79 ***************************************************************/
80 -- Constant declaration.
81 cst_completed CONSTANT VARCHAR2(20) := 'COMPLETED';
82 cst_enrolled CONSTANT VARCHAR2(20) := 'ENROLLED';
83 cst_final CONSTANT VARCHAR2(20) := 'FINAL';
84 cst_in_progress CONSTANT VARCHAR2(20) := 'IN PROGRESS';
85 cst_trans_blk CONSTANT VARCHAR2(20) := 'TRANS_BLK';
86 cst_trans_blk1 CONSTANT VARCHAR2(20) := 'RVK_SRVC';
87 cst_trans_blk2 CONSTANT VARCHAR2(20) := 'SUS_SRVC';
88 cst_trans_blk3 CONSTANT VARCHAR2(20) := 'RESULT_BLK';
89
90 -- This cursor will select the information related to the given Item number.
91 CURSOR cur_ord_itm_int IS
92 SELECT missing_acad_record_data_ind,
93 hold_release_of_final_grades,
94 fgrade_cal_type,
95 fgrade_seq_num,
96 person_id,
97 hold_for_grade_chg,
98 creation_date,
99 hold_degree_expected,
100 programs_on_file
101 FROM igs_as_ord_itm_int
102 WHERE item_number = p_item_number;
103 rec_cur_ord_itm_int cur_ord_itm_int%ROWTYPE;
104
105 -- To determine if there exists a Transcript Hold for the student.
106 CURSOR cur_hold_dlv_ind IS
107 SELECT trans_request_if_hold_ind, hold_deliv_ind
108 FROM igs_as_docproc_stup;
109 rec_cur_hold_dlv_ind cur_hold_dlv_ind%ROWTYPE;
110
111 -- To determine if the student has a pending transcript hold.
112 --ijeddy, bug 3410409.
113 CURSOR cur_hold_eft (cp_person_id igs_as_chn_grd_req.person_id%TYPE,
114 cp_creation_date igs_as_ord_itm_int.creation_date%TYPE) IS
115 SELECT 'X'
116 FROM igs_pe_persenc_effct
117 WHERE person_id = cp_person_id AND
118 s_encmb_effect_type IN (cst_trans_blk,cst_trans_blk1,cst_trans_blk2,cst_trans_blk3) AND
119 expiry_dt IS NULL AND
120 TRUNC(pen_start_dt) <= TRUNC(cp_creation_date);
121 rec_cur_hold_eft cur_hold_eft%ROWTYPE;
122
123 -- Cursor to get the student unit attempts.
124 CURSOR cur_en_su_att (cp_person_id igs_en_su_attempt.person_id%TYPE,
125 cp_cal_type igs_en_su_attempt.cal_type%TYPE,
126 cp_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE) IS
127 -- anilk, 22-Apr-2003, Bug# 2829262
128 SELECT course_cd, unit_cd, uoo_id
129 FROM igs_en_su_attempt
130 WHERE person_id = cp_person_id AND
131 unit_attempt_status IN (cst_completed,cst_enrolled) AND
132 cal_type = cp_cal_type AND
133 ci_sequence_number = cp_ci_sequence_number ;
134 rec_cur_en_su_att cur_en_su_att%ROWTYPE;
135
136 -- Cursor to check the finalised outcome for the student unit attempts.
137 CURSOR cur_as_suaoa(cp_person_id igs_en_su_attempt.person_id%TYPE,
138 cp_cal_type igs_en_su_attempt.cal_type%TYPE,
139 cp_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE,
140 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
141 cp_unit_cd igs_en_su_attempt.unit_cd%TYPE,
142 -- anilk, 22-Apr-2003, Bug# 2829262
143 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE ) IS
144 SELECT finalised_outcome_ind
145 FROM igs_as_suaoa_v
146 WHERE person_id = cp_person_id AND
147 course_cd = cp_course_cd AND
148 -- anilk, 22-Apr-2003, Bug# 2829262
149 uoo_id = cp_uoo_id AND
150 grading_period_cd = cst_final AND
151 finalised_outcome_ind = 'Y';
152 rec_cur_as_suaoa cur_as_suaoa%ROWTYPE;
153
154 -- Cursor to check if the user has requested to 'Hold for Grade Change'.
155 CURSOR cur_chn_grd_req (cp_person_id igs_as_chn_grd_req.person_id%TYPE,
156 cp_creation_date igs_as_ord_itm_int.creation_date%TYPE)IS
157 SELECT 'X'
158 FROM igs_as_chn_grd_req
159 WHERE person_id = cp_person_id AND
160 current_status = cst_in_progress AND
161 request_date < cp_creation_date;
162 rec_cur_chn_grd_req cur_chn_grd_req%ROWTYPE;
163
164 -- For Program centric Mode- Check if the program attempt status is 'COMPLETED' (for all the programs if program is not selected).
165 CURSOR cur_ps_att (cp_person_id igs_as_chn_grd_req.person_id%TYPE,
166 cp_creation_date igs_as_ord_itm_int.creation_date%TYPE,
167 cp_course_cd igs_en_sca_v.course_cd%TYPE)IS
168 SELECT 'X'
169 FROM igs_en_stdnt_ps_att_all
170 WHERE person_id = cp_person_id AND
171 course_cd = NVL(cp_course_cd, course_cd) AND
172 TRUNC(course_rqrmnts_complete_dt) BETWEEN TRUNC(igs_ge_date.igsdate(cp_creation_date)) AND TRUNC(SYSDATE);
173 rec_cur_ps_att cur_ps_att%ROWTYPE;
174
175 -- For Career centric Mode- Check if the program attempt status is 'COMPLETED' (under all the career if career is not selected).
176 CURSOR cur_en_sca (cp_person_id igs_as_chn_grd_req.person_id%TYPE,
177 cp_creation_date igs_as_ord_itm_int.creation_date%TYPE,
178 cp_course_type igs_en_sca_v.course_type%TYPE)IS
179 SELECT 'X'
180 FROM igs_en_sca_v
181 WHERE person_id = cp_person_id AND
182 course_type = NVL(cp_course_type, course_type) AND
183 TRUNC(course_rqrmnts_complete_dt) BETWEEN TRUNC(igs_ge_date.igsdate(cp_creation_date)) AND TRUNC(SYSDATE);
184 rec_cur_en_sca cur_en_sca%ROWTYPE;
185
186 l_su_att_count NUMBER(10);
187 BEGIN
188
189 OPEN cur_ord_itm_int;
190 FETCH cur_ord_itm_int INTO rec_cur_ord_itm_int;
191 CLOSE cur_ord_itm_int;
192 --
193 -- 1. Check if the "Academic History is missing.
194 --
195 IF NVL(rec_cur_ord_itm_int.missing_acad_record_data_ind,'N') = 'Y' THEN
196 p_document_ready := 'N';
197 p_error_mesg := 'IGS_AS_MSNG_ACAD_HIST';
198 RETURN;
199 END IF;
200
201 --
202 -- 2. Determine if there exists a Transcript Hold for the student.
203 --
204 OPEN cur_hold_dlv_ind;
205 FETCH cur_hold_dlv_ind INTO rec_cur_hold_dlv_ind;
206 CLOSE cur_hold_dlv_ind;
207 --IJEDDY
208 IF rec_cur_hold_dlv_ind.hold_deliv_ind = 'Y' OR
209 rec_cur_hold_dlv_ind.trans_request_if_hold_ind = 'Y' THEN
210 -- Determine if the student has a pending transcript hold.
211 OPEN cur_hold_eft(rec_cur_ord_itm_int.person_id,
212 rec_cur_ord_itm_int.creation_date);
213 FETCH cur_hold_eft INTO rec_cur_hold_eft;
214 IF cur_hold_eft%FOUND THEN
215 CLOSE cur_hold_eft;
216 p_document_ready := 'N';
217 p_error_mesg := 'IGS_AS_TRNS_BLK_EXISTS';
218 RETURN;
219 END IF;
220 CLOSE cur_hold_eft;
221 END IF;
222
223 --
224 -- 3. Determine if the document request specifies to "Hold for Final Grades" in a selected teaching period.
225 --
226 IF NVL(rec_cur_ord_itm_int.hold_release_of_final_grades,'N') = 'Y' THEN
227 l_su_att_count := 0;
228 p_error_mesg := NULL;
229 FOR rec_cur_en_su_att IN cur_en_su_att(rec_cur_ord_itm_int.person_id,
230 rec_cur_ord_itm_int.fgrade_cal_type,
231 rec_cur_ord_itm_int.fgrade_seq_num) LOOP
232 l_su_att_count := l_su_att_count + 1;
233 OPEN cur_as_suaoa(rec_cur_ord_itm_int.person_id,
234 rec_cur_ord_itm_int.fgrade_cal_type,
235 rec_cur_ord_itm_int.fgrade_seq_num,
236 rec_cur_en_su_att.course_cd,
237 rec_cur_en_su_att.unit_cd,
238 -- anilk, 22-Apr-2003, Bug# 2829262
239 rec_cur_en_su_att.uoo_id );
240 FETCH cur_as_suaoa INTO rec_cur_as_suaoa;
241 IF cur_as_suaoa%NOTFOUND THEN
242 CLOSE cur_as_suaoa;
243 p_error_mesg := 'IGS_AS_GRD_NOT_FINAL';
244 EXIT;
245 END IF;
246 CLOSE cur_as_suaoa;
247 END LOOP;
248 IF (l_su_att_count = 0 OR p_error_mesg IS NOT NULL) THEN
249 p_document_ready := 'N';
250 p_error_mesg := 'IGS_AS_GRD_NOT_FINAL';
251 RETURN;
252 END IF;
253 END IF;
254
255 --
256 -- 4. Determine if the document request specifies to "Hold for Grade Change"
257 --
258 IF NVL(rec_cur_ord_itm_int.hold_for_grade_chg,'N') = 'Y' THEN
259 OPEN cur_chn_grd_req(rec_cur_ord_itm_int.person_id,
260 rec_cur_ord_itm_int.creation_date);
261 FETCH cur_chn_grd_req INTO rec_cur_chn_grd_req;
262 IF cur_chn_grd_req%FOUND THEN
263 CLOSE cur_chn_grd_req;
264 p_document_ready := 'N';
265 p_error_mesg := 'IGS_AS_GRD_CHG_HLD';
266 RETURN;
267 END IF;
268 CLOSE cur_chn_grd_req;
269 END IF;
270
271 --
272 -- 5. Determine if the document request specified to "Hold for Degree Award".
273 --
274 IF NVL(rec_cur_ord_itm_int.hold_degree_expected,'N') = 'Y' THEN
275 IF rec_cur_ord_itm_int.programs_on_file = 'ALL' THEN
276 IF FND_PROFILE.VALUE('CAREER_MODEL_ENABLED') <> 'Y' THEN
277 OPEN cur_ps_att(rec_cur_ord_itm_int.person_id,
278 rec_cur_ord_itm_int.creation_date,
279 NULL /* To check for all Program Attempts*/);
280 FETCH cur_ps_att INTO rec_cur_ps_att;
281 IF cur_ps_att%NOTFOUND THEN
282 CLOSE cur_ps_att;
283 p_document_ready := 'N';
284 p_error_mesg := 'IGS_AS_DEG_AWD_HLD';
285 RETURN;
286 END IF;
287 CLOSE cur_ps_att;
288 ELSIF FND_PROFILE.VALUE('CAREER_MODEL_ENABLED') = 'Y' THEN
289 OPEN cur_en_sca(rec_cur_ord_itm_int.person_id,
290 rec_cur_ord_itm_int.creation_date,
291 NULL /* To check for all Careers*/);
292 FETCH cur_en_sca INTO rec_cur_en_sca;
293 IF cur_en_sca%NOTFOUND THEN
294 CLOSE cur_en_sca;
295 p_document_ready := 'N';
296 p_error_mesg := 'IGS_AS_DEG_AWD_HLD';
297 RETURN;
298 END IF;
299 CLOSE cur_en_sca;
300 END IF;
301 ELSE
302 IF FND_PROFILE.VALUE('CAREER_MODEL_ENABLED') <> 'Y' THEN
303 OPEN cur_ps_att(rec_cur_ord_itm_int.person_id,
304 rec_cur_ord_itm_int.creation_date,
305 rec_cur_ord_itm_int.programs_on_file);
306 FETCH cur_ps_att INTO rec_cur_ps_att;
307 IF cur_ps_att%NOTFOUND THEN
308 CLOSE cur_ps_att;
309 p_document_ready := 'N';
310 p_error_mesg := 'IGS_AS_DEG_AWD_HLD';
311 RETURN;
312 END IF;
313 CLOSE cur_ps_att;
314 ELSIF FND_PROFILE.VALUE('CAREER_MODEL_ENABLED') = 'Y' THEN
315 OPEN cur_en_sca(rec_cur_ord_itm_int.person_id,
316 rec_cur_ord_itm_int.creation_date,
317 rec_cur_ord_itm_int.programs_on_file);
318 FETCH cur_en_sca INTO rec_cur_en_sca;
319 IF cur_en_sca%NOTFOUND THEN
320 CLOSE cur_en_sca;
321 p_document_ready := 'N';
322 p_error_mesg := 'IGS_AS_DEG_AWD_HLD';
323 RETURN;
324 END IF;
325 CLOSE cur_en_sca;
326 END IF;
327 END IF;
328 END IF;
329
330 --
331 -- If control reaches here means the document can be produced.
332 --
333 p_document_ready := 'Y';
334 p_error_mesg := NULL;
335
336 EXCEPTION
337 WHEN OTHERS THEN
338 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
339 FND_MESSAGE.SET_TOKEN('NAME','igs_as_prod_doc.asp_chk_doc_rdns');
340 IGS_GE_MSG_STACK.ADD;
341 APP_EXCEPTION.RAISE_EXCEPTION;
342 END asp_chk_doc_rdns;
343
344 PROCEDURE notify_miss_acad_rec_prod (
345 p_person_id IN VARCHAR2,
346 p_order_number IN VARCHAR2,
347 p_item_number IN VARCHAR2,
348 p_document_type IN VARCHAR2,
349 p_recipient_name IN VARCHAR2,
350 p_receiving_inst_name IN VARCHAR2,
351 p_delivery_method IN VARCHAR2,
352 p_fulfillment_date_time IN VARCHAR2
353 ) AS
354 /*
355 || Created By : [email protected]
356 || Created On : 29-OCT-2002
357 || Purpose : To send notification to the student informing him about the
358 || manual production and delivery of Missing Academic Records document
359 || Known limitations, enhancements or remarks :
360 || Change History :
361 || Who When What
362 || (reverse chronological order - newest change first)
363 */
364 --
365 -- Cursor to get the user_name corresponding to the person_id
366 --
367 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
368 SELECT user_name
369 FROM fnd_user
370 WHERE person_party_id = cp_person_id;
371 --
372 -- Get a unique sequence number for use in raising the event
373 --
374 CURSOR c_seq_num IS
375 SELECT igs_as_wf_beas008_s.NEXTVAL
376 FROM dual;
377 --
378 l_event_t wf_event_t;
379 l_parameter_list_t wf_parameter_list_t;
383 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSAS008';
380 l_itemKey VARCHAR2(100);
381 ln_seq_val NUMBER;
382 l_role_name VARCHAR2(320);
384 l_user_name fnd_user.user_name%TYPE;
385 --
386 BEGIN
387 --
388 OPEN c_seq_num;
389 FETCH c_seq_num INTO ln_seq_val;
390 CLOSE c_seq_num;
391 --
392 -- initialize the wf_event_t object
393 --
394 wf_event_t.Initialize (l_event_t);
395 -- Create the adhoc role
396 l_role_name := 'IGSAS008' || ln_seq_val;
397 --
398 -- Create adhoc role
399 --
400 Wf_Directory.CreateAdHocRole (
401 role_name => l_role_name,
402 role_display_name => l_role_display_name
403 );
404 --
405 -- Add the user name to the adhoc role
406 --
407 OPEN c_user_name (p_person_id);
408 FETCH c_user_name INTO l_user_name;
409 CLOSE c_user_name;
410 --
411 IF (l_user_name IS NOT NULL) THEN
412 Wf_Directory.AddUsersToAdHocRole (
413 role_name => l_role_name,
414 role_users => l_user_name
415 );
416 END IF;
417 --
418 -- Add the parameters to the parameter list
419 --
420 wf_event.AddParameterToList ( p_name => 'IA_PERSON_ID', p_value => p_person_id, p_parameterlist => l_parameter_list_t);
421 wf_event.AddParameterToList ( p_name => 'IA_ORDER_NUMBER', p_value => p_order_number, p_parameterlist => l_parameter_list_t);
422 wf_event.AddParameterToList ( p_name => 'IA_DOCUMENT_NUMBER', p_value => p_item_number, p_parameterlist => l_parameter_list_t);
423 wf_event.AddParameterToList ( p_name => 'IA_DOCUMENT_TYPE', p_value => p_document_type, p_parameterlist => l_parameter_list_t);
424 wf_event.AddParameterToList ( p_name => 'IA_RECIPIENT_NAME', p_value => p_recipient_name, p_parameterlist => l_parameter_list_t);
425 wf_event.AddParameterToList ( p_name => 'IA_RECEIVING_INST_NAME', p_value => p_receiving_inst_name, p_parameterlist => l_parameter_list_t);
426 wf_event.AddParameterToList ( p_name => 'IA_DELIVERY_METHOD', p_value => p_delivery_method, p_parameterlist => l_parameter_list_t);
427 wf_event.AddParameterToList ( p_name => 'IA_FULFILLMENT_DATE_TIME', p_value => p_fulfillment_date_time, p_parameterlist => l_parameter_list_t);
428 wf_event.AddParameterToList ( p_name => 'IA_ADHOCROLE', p_value => l_role_name, p_parameterlist => l_parameter_list_t);
429 --
430 -- Raise the Event
431 --
432 wf_event.raise (
433 p_event_name => 'oracle.apps.igs.as.orddoc.notstu',
434 p_event_key => 'AS008' || ln_seq_val,
435 p_parameters => l_parameter_list_t
436 );
437 --
438 -- Delete the Parameter list after the event is raised
439 --
440 l_parameter_list_t.delete;
441 --
442 END notify_miss_acad_rec_prod;
443
444
445 PROCEDURE wf_launch_as004 (
446 p_user IN VARCHAR2,
447 p_date_produced IN VARCHAR2,
448 p_doc_type IN VARCHAR2
449 ) AS
450 /******************************************************************
451 Created By : Sameer Manglm
452 Date Created By : 22-AUG-2002
453 Purpose : This procedure will be used for launching
454 the workflow process.
455 remarks : pass p_date_produced in form canonical date
456 format like YYYY/MM/DD HH24:MI:SS
457 and p_doc_type as meaning of lookup_code
458 Change History
459 Who When What
460 ******************************************************************/
461
462 lv_item_type VARCHAR2(100) :='IGSAS004' ;
463 ln_seq_val NUMBER;
464
465 -- Gets a unique sequence number
466 CURSOR
467 c_seq_num
468 IS
469 SELECT
470 igs_as_wf_beas004_s.NEXTVAL
471 FROM
472 dual;
473 BEGIN
474
475 -- Get the sequence value
476 OPEN c_seq_num;
477 FETCH c_seq_num INTO ln_seq_val ;
478 CLOSE c_seq_num ;
479
480 -- Create the process
481 Wf_Engine.createprocess( ItemType => lv_item_type,
482 ItemKey => 'AS004'||ln_seq_val,
483 process => 'P_AS004'
484 );
485 -- Attach the item attribute IA_USER_ROLE
486 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
487 ItemKey => 'AS004'||ln_seq_val,
488 aname => 'IA_USERROLE',
489 avalue => p_user
490 );
491
492 -- Attach the item attribute IA_USER_NAME
493 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
494 ItemKey => 'AS004'||ln_seq_val,
495 aname => 'IA_USERNAME',
496 avalue => p_user
497 );
498
499 -- Pass the work flow event key
500 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
501 ItemKey => 'AS004'||ln_seq_val,
502 aname => 'IA_EVENT_KEY',
503 avalue => 'BEAS004'||ln_seq_val
507 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
504 );
505
506 -- Pass the date of production
508 ItemKey => 'AS004'||ln_seq_val,
509 aname => 'EA_DATE_PRODUCED',
510 avalue => p_date_produced
511 );
512 -- Pass the date of production
513 Wf_Engine.SetItemAttrText( ItemType => lv_item_type,
514 ItemKey => 'AS004'||ln_seq_val,
515 aname => 'EA_DOC_TYPE',
516 avalue => p_doc_type
517 );
518
519 -- Start the doc type
520 Wf_Engine.StartProcess ( ItemType => lv_item_type,
521 ItemKey => 'AS004'||ln_seq_val
522 );
523
524 -- Handle the exception using WF_CORE.Context
525 EXCEPTION
526
527 WHEN OTHERS THEN
528 Wf_Core.Context('IGS_AS_PROD_DOC', 'WF_LAUNCH_AS004', lv_item_type, 'AS004'||ln_seq_val,'P_AS004');
529 RAISE;
530
531 END wf_launch_as004;
532
533 PROCEDURE wf_set_role (
534 itemtype IN VARCHAR2,
535 itemkey IN VARCHAR2,
536 actid IN NUMBER,
537 funcmode IN VARCHAR2,
538 resultout OUT NOCOPY VARCHAR2
539 ) AS
540 /******************************************************************
541 Created By : Sameer Manglm
542 Date Created By : 22-AUG-2002
543 Purpose : This workflow procedure is a wrapper procedure,
544 which will be called from the workflow builder
545 IGSAS005. This would set the adhoc role for the
546 notification
547 Remarks :
548 Change History
549 Who When What
550 ******************************************************************/
551 --
552 -- Cursor to get details from Order Item Interface table
553 --
554 CURSOR c_person_id (cp_date_produced IN VARCHAR2) IS
555 SELECT aoii.order_number order_number,
556 aoii.item_number item_number,
557 aoii.person_id person_id
558 FROM igs_as_ord_itm_int aoii
559 WHERE fnd_date.date_to_canonical(aoii.date_produced) = cp_date_produced
560 AND aoii.item_status = 'PROCESSED';
561 --
562 -- Cursor to get the oracle applications user_name corresponding to the person_id
563 --
564 CURSOR c_user_name (cp_person_id igs_as_ord_itm_int.person_id%TYPE) IS
565 SELECT user_name
566 FROM fnd_user
567 WHERE person_party_id = cp_person_id;
568 --
569 -- Cursor to check if the user_name being added to the Adhoc Role is already duplicated
570 --
571 CURSOR c_dup_user (cp_user_name VARCHAR2,
572 cp_role_name VARCHAR2) IS
573 SELECT count(1)
574 FROM WF_LOCAL_USER_ROLES
575 WHERE USER_NAME = cp_user_name
576 AND ROLE_NAME = cp_role_name
577 AND ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
578 AND ROLE_ORIG_SYSTEM_ID = 0;
579 --
580 -- Cursor to check if the user_name being added to the Adhoc Role is already duplicated
581 --
582 CURSOR cur_order_details (cp_order_number IN NUMBER) IS
583 SELECT request_type
584 FROM igs_as_order_hdr
585 WHERE order_number = cp_order_number;
586 --
587 l_date_prod VARCHAR2(30);
588 l_doc_type VARCHAR2(30);
589 l_role_name VARCHAR2(320);
590 l_role_display_name VARCHAR2(320) := 'Adhoc Role for IGSAS005';
591 l_person_id c_person_id%ROWTYPE;
592 l_user_name fnd_user.user_name%TYPE;
593 l_dup_user NUMBER := 0;
594 l_send_notification BOOLEAN := TRUE;
595 rec_order_details cur_order_details%ROWTYPE;
596 --
597 BEGIN
598 --
599 IF (funcmode = 'RUN') THEN
600
601 -- create the adhoc role
602 l_role_name := 'IGS'||substr(itemkey,6);
603 Wf_Directory.CreateAdHocRole (role_name => l_role_name,
604 role_display_name => l_role_display_name
605 );
606
607 l_date_prod := Wf_Engine.GetItemAttrText(itemtype,itemkey,'EA_DATE_PRODUCED');
608 l_doc_type := Wf_Engine.GetItemAttrText(itemtype,itemkey,'EA_DOC_TYPE');
609
610 -- set the item attribute of the workflow with date produced
611 Wf_Engine.SetItemAttrText( ItemType => itemtype,
612 ItemKey => itemkey,
613 aname => 'IA_DATE_PROD',
614 avalue => l_date_prod
615 );
616 -- set the item attribute of the workflow with doc type
617 Wf_Engine.SetItemAttrText( ItemType => itemtype,
618 ItemKey => itemkey,
619 aname => 'IA_DOC_TYPE',
620 avalue => l_doc_type
621 );
622 --
623 -- fetch student for whom the record has been processed and add the user name to the adhoc role
624 --
625 OPEN c_person_id (l_date_prod);
626 LOOP
627 FETCH c_person_id INTO l_person_id;
628 EXIT WHEN c_person_id%NOTFOUND;
629 l_send_notification := TRUE;
630 OPEN cur_order_details (l_person_id.order_number);
631 FETCH cur_order_details INTO rec_order_details;
632 CLOSE cur_order_details;
633 IF (rec_order_details.request_type = 'B') THEN
634 --
635 -- Add the user to the Role only when the User Hook returns a TRUE value
636 --
637 l_send_notification := igs_as_user_hook.notify_bulk_doc_production (
638 p_order_number => l_person_id.order_number,
639 p_item_number => l_person_id.item_number,
640 p_person_id => l_person_id.person_id
641 );
642 END IF;
643 IF (l_send_notification) THEN
644 OPEN c_user_name (l_person_id.person_id);
645 FETCH c_user_name INTO l_user_name;
646 CLOSE c_user_name;
647 --
648 -- Add this user name to the adhoc role if it is not null and unique
649 --
650 OPEN c_dup_user(l_user_name,l_role_name);
651 FETCH c_dup_user INTO l_dup_user;
652 CLOSE c_dup_user;
653 IF l_user_name IS NOT NULL AND l_dup_user = 0 THEN
654 Wf_Directory.AddUsersToAdHocRole (
655 role_name => l_role_name,
656 role_users => l_user_name
657 );
658 END IF;
659 END IF;
660 END LOOP;
661 CLOSE c_person_id;
662 -- now set this role to the workflow
663 Wf_Engine.SetItemAttrText( ItemType => itemtype,
664 ItemKey => itemkey,
665 aname => 'IA_ROLE',
666 avalue => l_role_name
667 );
668 Resultout:= 'COMPLETE:';
669 RETURN;
670 END IF;
671
672 END wf_set_role;
673
674 PROCEDURE asp_update_order_doc (
675 p_item_number IN NUMBER,
676 p_test_mode IN VARCHAR2
677 ) AS
678 /******************************************************************
679 Created By : Sameer Manglm
680 Date Created By : 22-AUG-2002
681 Purpose : Update Order Docs interface table with the item status
682 (IGS_AS_ORD_ITM_INT.ITEM_STATUS) to PROCESSED and
683 the line item status (IGS_AS_DOC_DETAILS.ITEM_STATUS)
684 to PROCESSED. Also if all the items are processed,
685 update the Order status (IGS_AS_ORDER_HDR.ORDER_STATUS)
686 to COMPLETED.
687 Remarks :
688 Change History
689 Who When What
690 ******************************************************************/
691 PRAGMA AUTONOMOUS_TRANSACTION;
692 --
693 -- Cursor to check the existence of a record in Order Item Interface Table
694 --
695 CURSOR c_ord_itm_int (cp_item_number igs_as_ord_itm_int.item_number%TYPE) IS
696 SELECT 'x'
697 FROM igs_as_ord_itm_int aoii
698 WHERE aoii.item_number = cp_item_number;
699 --
700 -- Cursor to fetch the records from Order Item Interface Table.
701 -- Discontinue automatic posting of Completion for documents having Missing Academic Records
702 --
703 CURSOR c_doc_details (cp_item_number igs_as_doc_details.item_number%TYPE) IS
704 SELECT dd.rowid, dd.*
705 FROM igs_as_doc_details dd
706 WHERE dd.item_number = cp_item_number
707 AND NVL (missing_acad_record_data_ind, 'N') = 'N';
708 --
709 -- Cursor to fetch order number from IGS_AS_ORDER_HDR and update order status
710 -- to COMPLETED if all the line items are PROCESSED
711 --
712 CURSOR c_order_hdr IS
713 SELECT oh.rowid, oh.*
714 FROM igs_as_order_hdr oh
715 WHERE oh.order_status = 'INPROCESS'
716 AND NOT EXISTS (SELECT 'x'
717 FROM igs_as_doc_details dd
718 WHERE dd.order_number = oh.order_number
719 AND dd.item_status <> 'PROCESSED');
720 --
721 l_return_status VARCHAR2(1);
722 l_msg_data VARCHAR2(30);
723 l_msg_count NUMBER;
724 --
725 BEGIN
726 --
727 -- Update igs_as_ord_itm_int with item_status = PROCESSED and date_produced to sysdate
728 -- Discontinue automatic posting of Completion for documents having Missing Academic Records
729 --
730 FOR rec_ord_itm_int IN c_ord_itm_int (p_item_number)
731 LOOP
732 UPDATE igs_as_ord_itm_int
733 SET item_status = 'PROCESSED',
734 date_produced = SYSDATE
735 WHERE item_number = p_item_number
736 AND NVL (missing_acad_record_data_ind, 'N') = 'N';
737 END LOOP;
738 --
739 -- Update IGS_AS_DOC_DETAILS.ITEM_STATUS and date_produced to sysdate
740 --
741 FOR rec_doc_details IN c_doc_details (p_item_number)
742 LOOP
743 igs_as_doc_details_pkg.update_row (
744 x_rowid => rec_doc_details.rowid,
745 x_order_number => rec_doc_details.order_number,
746 x_document_type => rec_doc_details.document_type,
750 x_date_produced => SYSDATE,
747 x_document_sub_type => rec_doc_details.document_sub_type,
748 x_item_number => rec_doc_details.item_number,
749 x_item_status => 'PROCESSED',
751 x_incl_curr_course => rec_doc_details.incl_curr_course,
752 x_num_of_copies => rec_doc_details.num_of_copies,
753 x_comments => rec_doc_details.comments,
754 x_recip_pers_name => rec_doc_details.recip_pers_name,
755 x_recip_inst_name => rec_doc_details.recip_inst_name,
756 x_recip_addr_line_1 => rec_doc_details.recip_addr_line_1,
757 x_recip_addr_line_2 => rec_doc_details.recip_addr_line_2,
758 x_recip_addr_line_3 => rec_doc_details.recip_addr_line_3,
759 x_recip_addr_line_4 => rec_doc_details.recip_addr_line_4,
760 x_recip_city => rec_doc_details.recip_city,
761 x_recip_postal_code => rec_doc_details.recip_postal_code,
762 x_recip_state => rec_doc_details.recip_state,
763 x_recip_province => rec_doc_details.recip_province,
764 x_recip_county => rec_doc_details.recip_county,
765 x_recip_country => rec_doc_details.recip_country,
766 x_recip_fax_area_code => rec_doc_details.recip_fax_area_code,
767 x_recip_fax_country_code => rec_doc_details.recip_fax_country_code,
768 x_recip_fax_number => rec_doc_details.recip_fax_number,
769 x_delivery_method_type => rec_doc_details.delivery_method_type,
770 x_programs_on_file => rec_doc_details.programs_on_file,
771 x_missing_acad_record_data_ind => rec_doc_details.missing_acad_record_data_ind,
772 x_missing_academic_record_data => rec_doc_details.missing_academic_record_data,
773 x_send_transcript_immediately => rec_doc_details.send_transcript_immediately,
774 x_hold_release_of_final_grades => rec_doc_details.hold_release_of_final_grades,
775 x_fgrade_cal_type => rec_doc_details.fgrade_cal_type,
776 x_fgrade_seq_num => rec_doc_details.fgrade_seq_num,
777 x_hold_degree_expected => rec_doc_details.hold_degree_expected,
778 x_deghold_cal_type => rec_doc_details.deghold_cal_type,
779 x_deghold_seq_num => rec_doc_details.deghold_seq_num,
780 x_hold_for_grade_chg => rec_doc_details.hold_for_grade_chg,
781 x_special_instr => rec_doc_details.special_instr,
782 x_express_mail_type => rec_doc_details.express_mail_type,
783 x_express_mail_track_num => rec_doc_details.express_mail_track_num,
784 x_ge_certification => rec_doc_details.ge_certification,
785 x_external_comments => rec_doc_details.external_comments,
786 x_internal_comments => rec_doc_details.internal_comments,
787 x_dup_requested => rec_doc_details.dup_requested,
788 x_dup_req_date => rec_doc_details.dup_req_date,
789 x_dup_sent_date => rec_doc_details.dup_sent_date,
790 x_enr_term_cal_type => rec_doc_details.enr_term_cal_type,
791 x_enr_ci_sequence_number => rec_doc_details.enr_ci_sequence_number,
792 x_incl_attempted_hours => rec_doc_details.incl_attempted_hours,
793 x_incl_class_rank => rec_doc_details.incl_class_rank,
794 x_incl_progresssion_status => rec_doc_details.incl_progresssion_status,
795 x_incl_class_standing => rec_doc_details.incl_class_standing,
796 x_incl_cum_hours_earned => rec_doc_details.incl_cum_hours_earned,
797 x_incl_gpa => rec_doc_details.incl_gpa,
798 x_incl_date_of_graduation => rec_doc_details.incl_date_of_graduation,
799 x_incl_degree_dates => rec_doc_details.incl_degree_dates,
800 x_incl_degree_earned => rec_doc_details.incl_degree_earned,
801 x_incl_date_of_entry => rec_doc_details.incl_date_of_entry,
802 x_incl_drop_withdrawal_dates => rec_doc_details.incl_drop_withdrawal_dates,
803 x_incl_hrs_for_curr_term => rec_doc_details.incl_hrs_earned_for_curr_term,
804 x_incl_majors => rec_doc_details.incl_majors,
805 x_incl_last_date_of_enrollment => rec_doc_details.incl_last_date_of_enrollment,
806 x_incl_professional_licensure => rec_doc_details.incl_professional_licensure,
807 x_incl_college_affiliation => rec_doc_details.incl_college_affiliation,
808 x_incl_instruction_dates => rec_doc_details.incl_instruction_dates,
809 x_incl_usec_dates => rec_doc_details.incl_usec_dates,
810 x_incl_program_attempt => rec_doc_details.incl_program_attempt,
811 x_incl_attendence_type => rec_doc_details.incl_attendence_type,
812 x_incl_last_term_enrolled => rec_doc_details.incl_last_term_enrolled,
813 x_incl_ssn => rec_doc_details.incl_ssn,
814 x_incl_date_of_birth => rec_doc_details.incl_date_of_birth,
815 x_incl_disciplin_standing => rec_doc_details.incl_disciplin_standing,
816 x_incl_no_future_term => rec_doc_details.incl_no_future_term,
817 x_incl_acurat_till_copmp_dt => rec_doc_details.incl_acurat_till_copmp_dt,
818 x_incl_cant_rel_without_sign => rec_doc_details.incl_cant_rel_without_sign,
819 x_mode => 'R',
820 x_return_status => l_return_status,
821 x_msg_data => l_msg_data,
822 x_msg_count => l_msg_count,
823 x_doc_fee_per_copy => rec_doc_details.doc_fee_per_copy,
824 x_delivery_fee => rec_doc_details.delivery_fee,
825 x_recip_email => rec_doc_details.recip_email,
826 x_overridden_doc_delivery_fee => rec_doc_details.overridden_doc_delivery_fee,
827 x_overridden_document_fee => rec_doc_details.overridden_document_fee,
828 x_fee_overridden_by => rec_doc_details.fee_overridden_by,
829 x_fee_overridden_date => rec_doc_details.fee_overridden_date,
830 x_incl_department => rec_doc_details.incl_department,
831 x_incl_field_of_stdy => rec_doc_details.incl_field_of_stdy,
832 x_incl_attend_mode => rec_doc_details.incl_attend_mode,
833 x_incl_yop_acad_prd => rec_doc_details.incl_yop_acad_prd,
834 x_incl_intrmsn_st_end => rec_doc_details.incl_intrmsn_st_end,
835 x_incl_hnrs_lvl => rec_doc_details.incl_hnrs_lvl,
836 x_incl_awards => rec_doc_details.incl_awards,
837 x_incl_award_aim => rec_doc_details.incl_award_aim,
838 x_incl_acad_sessions => rec_doc_details.incl_acad_sessions,
839 x_incl_st_end_acad_ses => rec_doc_details.incl_st_end_acad_ses,
840 x_incl_hesa_num => rec_doc_details.incl_hesa_num,
841 x_incl_location => rec_doc_details.incl_location,
842 x_incl_program_type => rec_doc_details.incl_program_type,
843 x_incl_program_name => rec_doc_details.incl_program_name,
844 x_incl_prog_atmpt_stat => rec_doc_details.incl_prog_atmpt_stat,
845 x_incl_prog_atmpt_end => rec_doc_details.incl_prog_atmpt_end,
846 x_incl_prog_atmpt_strt => rec_doc_details.incl_prog_atmpt_strt,
847 x_incl_req_cmplete => rec_doc_details.incl_req_cmplete,
848 x_incl_expected_compl_dt => rec_doc_details.incl_expected_compl_dt,
849 x_incl_conferral_dt => rec_doc_details.incl_conferral_dt,
850 x_incl_thesis_title => rec_doc_details.incl_thesis_title,
851 x_incl_program_code => rec_doc_details.incl_program_code,
852 x_incl_program_ver => rec_doc_details.incl_program_ver,
853 x_incl_stud_no => rec_doc_details.incl_stud_no,
854 x_incl_surname => rec_doc_details.incl_surname,
855 x_incl_fore_name => rec_doc_details.incl_fore_name,
856 x_incl_prev_names => rec_doc_details.incl_prev_names,
857 x_incl_initials => rec_doc_details.incl_initials,
858 x_doc_purpose_code => rec_doc_details.doc_purpose_code,
859 x_plan_id => rec_doc_details.plan_id,
860 x_produced_by => rec_doc_details.produced_by,
861 x_person_id => rec_doc_details.person_id
862 );
863 END LOOP;
864 --
865 -- Fetch order number from IGS_AS_ORDER_HDR and update order status
866 -- to COMPLETED if all the line items are PROCESSED
867 --
868 FOR rec_order_hdr IN c_order_hdr
869 LOOP
870 igs_as_order_hdr_pkg.update_row(
871 x_msg_count => l_msg_count,
872 x_msg_data => l_msg_data,
873 x_return_status => l_return_status,
874 x_rowid => rec_order_hdr.rowid,
875 x_order_number => rec_order_hdr.order_number,
876 x_order_status => 'COMPLETED',
877 x_date_completed => SYSDATE,
878 x_person_id => rec_order_hdr.person_id,
879 x_addr_line_1 => rec_order_hdr.addr_line_1,
880 x_addr_line_2 => rec_order_hdr.addr_line_2,
881 x_addr_line_3 => rec_order_hdr.addr_line_3,
882 x_addr_line_4 => rec_order_hdr.addr_line_4,
883 x_city => rec_order_hdr.city,
884 x_state => rec_order_hdr.state,
885 x_province => rec_order_hdr.province,
886 x_county => rec_order_hdr.county,
887 x_country => rec_order_hdr.country,
888 x_postal_code => rec_order_hdr.postal_code,
889 x_email_address => rec_order_hdr.email_address,
890 x_phone_country_code => rec_order_hdr.phone_country_code,
891 x_phone_area_code => rec_order_hdr.phone_area_code,
892 x_phone_number => rec_order_hdr.phone_number,
893 x_phone_extension => rec_order_hdr.phone_extension,
894 x_fax_country_code => rec_order_hdr.fax_country_code,
895 x_fax_area_code => rec_order_hdr.fax_area_code,
896 x_fax_number => rec_order_hdr.fax_number,
897 x_delivery_fee => rec_order_hdr.delivery_fee,
898 x_order_fee => rec_order_hdr.order_fee,
899 x_request_type => rec_order_hdr.request_type,
900 x_submit_method => rec_order_hdr.submit_method,
901 x_invoice_id => rec_order_hdr.invoice_id,
902 x_mode => 'R',
903 x_order_description => rec_order_hdr.order_description,
904 x_order_placed_by => rec_order_hdr.order_placed_by
905 );
906 END LOOP;
907 --
908 -- Commit if the job is not run in Test Mode;
909 --
910 IF (p_test_mode = 'N') THEN
911 COMMIT;
912 END IF;
913 --
914 END asp_update_order_doc;
915
916 FUNCTION get_doc_fee(
917 p_order_number igs_as_doc_details.order_number%TYPE
918 ) RETURN NUMBER
919 IS
920 /*************************************************************
921 Created By :Sandeep Waghmare
922 Date Created on : 14-Sep-2005
923 Purpose : This process is called by the DocSummaryDtlsVO to display the Document Fees for the particular student.
924 Know limitations, enhancements or remarks
925 CHANGE HISTORY
926 Who When What
927 (reverse chronological order - newest change first)
928 ***************************************************************/
929 l_order_fee number;
930 BEGIN
931 SELECT NVL (SUM (NVL (dtl.overridden_document_fee, dtl.doc_fee_per_copy)), 0)
932 INTO l_order_fee
933 FROM igs_as_doc_details dtl
934 WHERE order_number = p_order_number;
935 RETURN l_order_fee;
936 END get_doc_fee;
937
938 FUNCTION get_del_fee(
939 p_order_number igs_as_doc_details.order_number%TYPE
940 ) RETURN NUMBER
941 IS
942 /*************************************************************
943 Created By :Sandeep Waghmare
944 Date Created on : 14-Sep-2005
945 Purpose : This process is called by the DocSummaryDtlsVO to display the Document Delivery Fees for the particular student.
946 Know limitations, enhancements or remarks
947 CHANGE HISTORY
948 Who When What
949 (reverse chronological order - newest change first)
950 ***************************************************************/
951 l_order_del_fee NUMBER;
952 BEGIN
953 SELECT NVL (SUM (NVL (dtl.overridden_doc_delivery_fee, dtl.delivery_fee)), 0)
954 INTO l_order_del_fee
955 FROM igs_as_doc_details dtl
956 WHERE order_number = p_order_number;
957
958 RETURN l_order_del_fee;
959 END get_del_fee;
960
961
962 END igs_as_prod_doc;
[Home] [Help]