[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_SS_DOC_REQUEST
Source
1 PACKAGE BODY Igs_As_Ss_Doc_Request AS
2 /* $Header: IGSAS45B.pls 120.6 2006/07/26 07:41:20 ijeddy ship $ */
3 --
4 --
5 --
6 FUNCTION check_waivers (p_person_id NUMBER)
7 RETURN VARCHAR2
8 AS
9 CURSOR c_ftci
10 IS
11 SELECT ftci.fee_cal_type, ftci.fee_ci_sequence_number
12 FROM igs_fi_f_typ_ca_inst ftci,
13 igs_ca_da_inst_v daiv,
14 igs_ca_da_inst_v daiv1,
15 igs_fi_fee_type ft,
16 igs_fi_fee_str_stat stat
17 WHERE ftci.fee_type = ft.fee_type
18 AND ft.s_fee_type = 'DOCUMENT'
19 AND ftci.fee_type_ci_status = stat.fee_structure_status
20 AND stat.s_fee_structure_status = 'ACTIVE'
21 AND NVL (ft.closed_ind, 'N') = 'N'
22 AND ( daiv.dt_alias = ftci.start_dt_alias
23 AND daiv.sequence_number = ftci.start_dai_sequence_number
24 AND daiv.cal_type = ftci.fee_cal_type
25 AND daiv.ci_sequence_number = ftci.fee_ci_sequence_number
26 )
27 AND ( daiv1.dt_alias = ftci.end_dt_alias
28 AND daiv1.sequence_number = ftci.end_dai_sequence_number
29 AND daiv1.cal_type = ftci.fee_cal_type
30 AND daiv1.ci_sequence_number = ftci.fee_ci_sequence_number
31 )
32 AND SYSDATE BETWEEN daiv.alias_val
33 AND NVL (daiv1.alias_val, SYSDATE)
34 ORDER BY daiv.alias_val DESC;
35
36 v_ftci_rec c_ftci%ROWTYPE;
37
38 CURSOR c1 (
39 l_person_id igs_fi_wav_std_pgms.person_id%TYPE,
40 l_fee_cal_type igs_fi_wav_std_pgms.fee_cal_type%TYPE,
41 l_fee_ci_sequence_number igs_fi_wav_std_pgms.fee_ci_sequence_number%TYPE
42 )
43 IS
44 SELECT 'X'
45 FROM igs_fi_wav_std_pgms
46 WHERE person_id = l_person_id
47 AND fee_cal_type = l_fee_cal_type
48 AND fee_ci_sequence_number = l_fee_ci_sequence_number
49 AND assignment_status_code = 'ACTIVE';
50
51 temp VARCHAR2 (1);
52 BEGIN
53 OPEN c_ftci;
54 FETCH c_ftci INTO v_ftci_rec;
55 IF (c_ftci%NOTFOUND)
56 THEN
57 CLOSE c_ftci;
58 RETURN 'NOSETUP';
59 END IF;
60 CLOSE c_ftci;
61 OPEN c1 (
62 p_person_id,
63 v_ftci_rec.fee_cal_type,
64 v_ftci_rec.fee_ci_sequence_number);
65 FETCH c1 INTO temp;
66 IF (c1%FOUND)
67 THEN
68 CLOSE c1;
69 RETURN 'TRUE';
70 ELSE
71 CLOSE c1;
72 RETURN 'FALSE';
73 END IF;
74 END;
75
76 PROCEDURE get_summary_display_message (
77 p_person_id NUMBER,
78 p_hold_message OUT NOCOPY VARCHAR2,
79 p_hint_message OUT NOCOPY VARCHAR2,
80 p_request_allowed OUT NOCOPY VARCHAR2,
81 p_transcript_allowed OUT NOCOPY VARCHAR2,
82 p_encert_allowed OUT NOCOPY VARCHAR2,
83 p_lifetimefee_allowed OUT NOCOPY VARCHAR2
84 ) AS
85 lvcmessage VARCHAR2 (2000);
86 --
87 -- Cursor to get the institution setup for documents.
88 --
89 CURSOR c_setup IS
90 SELECT lifetime_trans_fee_ind,
91 provide_transcript_ind,
92 trans_request_if_hold_ind,
93 all_acad_hist_in_one_doc_ind,
94 hold_deliv_ind,
95 allow_enroll_cert_ind
96 FROM igs_as_docproc_stup;
97 --
98 cur_setup c_setup%ROWTYPE;
99 --
100 -- Cursor to get the holds information for the student which has effect of blocking his transcript
101 --
102 CURSOR c_hold IS
103 SELECT encumbrance_type,
104 pee_start_dt
105 FROM igs_pe_persenc_effct
106 WHERE person_id = p_person_id
107 AND s_encmb_effect_type IN ('TRANS_BLK','RVK_SRVC','SUS_SRVC','RESULT_BLK')
108 AND NVL (expiry_dt, SYSDATE) >= SYSDATE
109 AND pee_start_dt < SYSDATE
110 ORDER BY pee_start_dt DESC;
111 --
112 cur_hold c_hold%ROWTYPE;
113 --
114 -- Cursor to select the the info whether the student has paid the life time fee or not
115 --
116 CURSOR c_lifetime_fee IS
117 SELECT lifetime_fee_paid
118 FROM igs_as_doc_fee_pmnt
119 WHERE person_id = p_person_id
120 AND document_type = 'TRANSCRIPT'
121 AND fee_paid_type = 'LIFETIME';
122 --
123 cur_lifetime_fee c_lifetime_fee%ROWTYPE;
124 lvcholdexists VARCHAR2 (1) DEFAULT 'N';
125 --
126 BEGIN
127 --
128 -- Initialize the request allowed to 'Y'
129 --
130 p_request_allowed := 'Y';
131 p_transcript_allowed := 'Y';
132 p_encert_allowed := 'Y';
133 p_lifetimefee_allowed := 'Y';
134 --
135 -- Initailize Hold Message to Null
136 --
137 p_hold_message := NULL;
138 --
139 -- Open Cursor to determine whether the lifetime fee is paid or not
140 --
141 OPEN c_setup;
142 FETCH c_setup INTO cur_setup;
143 CLOSE c_setup;
144 OPEN c_hold;
145 FETCH c_hold INTO cur_hold;
146 IF c_hold%FOUND THEN
147 lvcholdexists := 'Y';
148 END IF;
149 CLOSE c_hold;
150 --
151 IF lvcholdexists = 'Y' THEN
152 fnd_message.set_name ('IGS', 'IGS_SS_AS_HOLD_EXISTS');
153 fnd_message.set_token ('HOLD_TYPE', cur_hold.encumbrance_type);
154 fnd_message.set_token ('START_DATE', cur_hold.pee_start_dt);
155 p_hold_message := fnd_message.get;
156 END IF;
157 --
158 -- Check for Lifetime Fee
159 --
160 IF cur_setup.lifetime_trans_fee_ind = 'Y' THEN
161 OPEN c_lifetime_fee;
162 FETCH c_lifetime_fee INTO cur_lifetime_fee;
163 IF c_lifetime_fee%FOUND THEN
164 p_lifetimefee_allowed := 'N';
165 ELSE
166 p_lifetimefee_allowed := 'Y';
167 END IF;
168 CLOSE c_lifetime_fee;
169 ELSE
170 p_lifetimefee_allowed := 'N';
171 END IF;
172 IF cur_setup.provide_transcript_ind = 'Y' THEN
173 IF lvcholdexists = 'Y' THEN
174 IF cur_setup.trans_request_if_hold_ind = 'Y'
175 AND cur_setup.hold_deliv_ind = 'N' THEN
176 IF cur_setup.allow_enroll_cert_ind = 'Y' THEN
177 fnd_message.set_name ('IGS', 'IGS_SS_AS_ENCERT_TILL_HOLD');
178 p_hint_message := fnd_message.get;
179 p_request_allowed := 'Y';
180 p_transcript_allowed := 'N';
181 p_encert_allowed := 'Y';
182 RETURN;
183 ELSE
184 fnd_message.set_name ('IGS', 'IGS_SS_AS_NODOC_TILL_HOLD');
185 p_hint_message := fnd_message.get;
186 p_request_allowed := 'N';
187 p_transcript_allowed := 'N';
188 p_encert_allowed := 'N';
189 RETURN;
190 END IF;
191 --
192 -- kdande; 27-May-2002; Bug# 2375407
193 -- Added the following code to display the messages properly and meaningfully.
194 --
195 ELSIF cur_setup.trans_request_if_hold_ind = 'N'
196 AND cur_setup.hold_deliv_ind = 'Y' THEN
197 IF cur_setup.allow_enroll_cert_ind = 'Y' THEN
198 fnd_message.set_name ('IGS', 'IGS_SS_AS_ENC_DLV_TILL_HOLD');
199 p_hint_message := fnd_message.get;
200 p_request_allowed := 'Y';
201 p_transcript_allowed := 'Y';
202 p_encert_allowed := 'Y';
203 RETURN;
204 ELSE
205 fnd_message.set_name ('IGS', 'IGS_SS_AS_TRN_DLV_TILL_HOLD');
206 p_hint_message := fnd_message.get;
207 p_request_allowed := 'Y';
208 p_transcript_allowed := 'Y';
209 p_encert_allowed := 'N';
210 RETURN;
211 END IF;
212 END IF;
213 IF cur_setup.hold_deliv_ind = 'Y' THEN
214 fnd_message.set_name ('IGS', 'IGS_SS_AS_HOLD_DEIV');
215 p_hint_message := fnd_message.get;
216 END IF;
217 END IF;
218 IF cur_setup.allow_enroll_cert_ind = 'Y' THEN
219 fnd_message.set_name ('IGS', 'IGS_SS_AS_ORDER_BOTH_DOCS');
220 p_hint_message := fnd_message.get;
221 ELSE
222 fnd_message.set_name ('IGS', 'IGS_SS_AS_ORDER_ONLY_TRANS');
223 p_hint_message := fnd_message.get;
224 p_transcript_allowed := 'Y';
225 p_encert_allowed := 'N';
226 END IF;
227 ELSE
228 IF cur_setup.allow_enroll_cert_ind = 'Y' THEN
229 fnd_message.set_name ('IGS', 'IGS_SS_AS_ORDER_ONLY_ENCERT');
230 p_hint_message := fnd_message.get;
231 p_transcript_allowed := 'N';
232 p_encert_allowed := 'Y';
233 ELSE
234 fnd_message.set_name ('IGS', 'IGS_SS_AS_ORDER_NO_DOC');
235 p_hint_message := fnd_message.get;
236 --
237 -- Neither of Transcript and Enrollment Certification are allowed.
238 -- Hence do not allow request.
239 --
240 p_request_allowed := 'N';
241 p_transcript_allowed := 'N';
242 p_encert_allowed := 'N';
243 END IF;
244 END IF;
245 END get_summary_display_message;
246 --
247 --
248 --
249 FUNCTION get_item_details_for_order (p_order_number NUMBER)
250 RETURN VARCHAR2 AS
251 --
252 -- Bug 2677640 added RECIP_PERS_NAME to query
253 --
254 CURSOR cur_item IS
255 SELECT order_number,
256 lkup.meaning doc_type,
257 item_number,
258 item_status,
259 recip_inst_name,
260 recip_pers_name
261 FROM igs_as_doc_details dtls,
262 igs_lookups_view lkup
263 WHERE dtls.document_sub_type = lkup.lookup_code
264 AND lkup.lookup_type = 'IGS_AS_DOCUMENT_SUB_TYPE'
265 AND order_number = p_order_number
266 ORDER BY item_number; --msrinivi, for bug #2318474
267 lvdocdetails VARCHAR2 (2000);
268 BEGIN
269 FOR cur_dtl IN cur_item LOOP
270 -- Bug 2677640 Display Recipient name when entered else show Organisation name
271 IF cur_dtl.recip_pers_name IS NULL THEN
272 lvdocdetails := lvdocdetails
273 || 'Document No: '
274 || TO_CHAR (cur_item%ROWCOUNT)
275 || '<BR>'
276 || 'Recipient: '
277 || cur_dtl.recip_inst_name
278 || '<BR>'
279 || 'Item Type: '
280 || cur_dtl.doc_type
281 || '<BR>'
282 || '<BR>'
283 || fnd_global.newline ();
284 ELSE
285 lvdocdetails := lvdocdetails
286 || 'Document No: '
287 || TO_CHAR (cur_item%ROWCOUNT)
288 || '<BR>'
289 || 'Recipient: '
290 || cur_dtl.recip_pers_name
291 || '<BR>'
292 || 'Item Type: '
293 || cur_dtl.doc_type
294 || '<BR>'
295 || '<BR>'
296 || fnd_global.newline ();
297 END IF;
298 END LOOP;
299 IF RTRIM (LTRIM (lvdocdetails)) IS NULL THEN
300 RETURN fnd_message.get_string ('IGS', 'IGS_SS_AS_NO_ITEM');
301 ELSE
302 RETURN lvdocdetails;
303 END IF;
304 END get_item_details_for_order;
305
306 FUNCTION get_order_details_include_addr (p_item_number NUMBER)
307 RETURN VARCHAR2 AS
308 -- Bug 2677640 added RECIP_PERS_NAME to query
309 CURSOR cur_item IS
310 SELECT order_number,
311 lkup.meaning doc_type,
312 item_number,
313 item_status,
314 recip_inst_name,
315 recip_pers_name,
316 recip_addr_line_1
317 || ' '
318 || recip_addr_line_2
319 || ' '
320 || recip_addr_line_3
321 || ' '
322 || recip_addr_line_4
323 || ','
324 || recip_city
325 || ' '
326 || recip_state
327 || ', '
328 || recip_country addr
329 FROM igs_as_doc_details dtls,
330 igs_lookups_view lkup
331 WHERE dtls.document_sub_type = lkup.lookup_code
332 AND lkup.lookup_type = 'IGS_AS_DOCUMENT_SUB_TYPE'
333 AND item_number = p_item_number
334 ORDER BY item_number; --msrinivi, for bug #2318474
335 lvdocdetails VARCHAR2 (2000);
336 BEGIN
337 FOR cur_dtl IN cur_item LOOP
338 -- Bug 2677640 Display Recipient name when entered else show Organisation name
339 IF cur_dtl.recip_pers_name IS NULL THEN
340 lvdocdetails := lvdocdetails
341 || 'Document No: '
342 || TO_CHAR (cur_item%ROWCOUNT)
343 || '<BR>'
344 || 'Recipient: '
345 || cur_dtl.recip_inst_name
346 || '<BR>'
347 || 'Recipient Address: '
348 || cur_dtl.addr
349 || '<BR>'
350 || 'Item Type: '
351 || cur_dtl.doc_type
352 || '<BR>'
353 || '<BR>'
354 || fnd_global.newline ();
355 ELSE
356 lvdocdetails := lvdocdetails
357 || 'Document No: '
358 || TO_CHAR (cur_item%ROWCOUNT)
359 || '<BR>'
360 || 'Recipient: '
361 || cur_dtl.recip_pers_name
362 || '<BR>'
363 || 'Recipient Address: '
364 || cur_dtl.addr
365 || '<BR>'
366 || 'Item Type: '
367 || cur_dtl.doc_type
368 || '<BR>'
369 || '<BR>'
370 || fnd_global.newline ();
371 END IF;
372 END LOOP;
373 IF RTRIM (LTRIM (lvdocdetails)) IS NULL THEN
374 RETURN fnd_message.get_string ('IGS', 'IGS_SS_AS_NO_ITEM');
375 ELSE
376 RETURN lvdocdetails;
377 END IF;
378 END get_order_details_include_addr;
379 --
380 --
381 --
382 FUNCTION get_transcript_fee (
383 p_person_id IN NUMBER,
384 p_document_type IN VARCHAR2,
385 p_number_of_copies IN NUMBER,
386 p_include_delivery_fee IN VARCHAR2,
387 p_delivery_method_type IN VARCHAR2,
388 p_item_number IN NUMBER
389 )
390 RETURN NUMBER IS
391 --
392 -- Cursor that gets the document fee setup details
393 -- for the given document type.
394 --
395 CURSOR cur_doc_setup (cp_document_type IN VARCHAR2, l_start_at IN NUMBER) IS
396 SELECT dfs.lower_range lower_range,
397 dfs.upper_range upper_range,
398 dfs.payment_type payment_type,
399 dfs.amount amount
400 FROM igs_as_doc_fee_stup dfs
401 WHERE dfs.document_type = cp_document_type
402 AND l_start_at < upper_range
403 ORDER BY dfs.lower_range;
404 --
405 -- Cursor that gets the document delivery setup details
406 -- for the given document delivery method type.
407 --
408 CURSOR cur_delivery_fee_setup (cp_delivery_method_type IN VARCHAR2) IS
409 SELECT dlfs.amount amount
410 FROM igs_as_doc_dlvy_fee dlfs
411 WHERE dlfs.delivery_method_type = cp_delivery_method_type;
412 --
413 -- Cursor that checks if a person has paid life time fee or not
414 --
415 CURSOR cur_life_time_fee_paid (cp_person_id IN NUMBER) IS
416 SELECT NVL (ltfp.lifetime_fee_paid, 'N') life_time_fee_paid
417 FROM igs_as_doc_fee_pmnt ltfp
418 WHERE ltfp.document_type = 'TRANSCRIPT'
419 AND fee_paid_type = 'LIFETIME'
420 AND ltfp.person_id = cp_person_id;
421 --
422 CURSOR cur_num_stu_ords (cp_item_number IN NUMBER) IS
423 SELECT NVL (SUM (doc.num_of_copies), 0)
424 FROM igs_as_doc_details doc,
425 igs_as_order_hdr hdr
426 WHERE doc.person_id = p_person_id
427 AND doc.plan_id IS NULL
428 AND doc.item_number < cp_item_number
429 AND doc.document_type =
430 DECODE (
431 p_document_type,
432 'ENCERT', 'ENCERT',
433 'OFFICIAL', 'TRANSCRIPT',
434 'UNOFFICIAL', 'TRANSCRIPT',
435 'TRANSCRIPT')
436 AND hdr.order_number = doc.order_number
437 AND NVL (hdr.request_type, 'W') <> 'B';
438 --
439 CURSOR cur_ttl_itm_chrgd_und_free IS
440 SELECT NVL (SUM (num_of_copies), 0)
441 FROM igs_as_doc_details
442 WHERE person_id = p_person_id
443 AND item_number <> NVL (p_item_number, -1)
444 AND plan_id IN (SELECT plan_id
445 FROM igs_as_servic_plan
446 WHERE plan_type IN (SELECT meaning
447 FROM igs_lookups_view
448 WHERE lookup_type = 'TRANSCRIPT_SERVICE_PLAN_TYPE'
449 AND lookup_code = 'FREE_TRANSCRIPT'));
450 --
451 CURSOR c_num_free_cops IS
452 SELECT NVL (quantity_limit, 0)
453 FROM igs_as_servic_plan
454 WHERE plan_type IN (SELECT meaning
455 FROM igs_lookups_view
456 WHERE lookup_type = 'TRANSCRIPT_SERVICE_PLAN_TYPE'
457 AND lookup_code = 'FREE_TRANSCRIPT');
458 --
459 -- Local Variables
460 --
461 l_life_time_fee_paid VARCHAR2 (1);
462 rec_cur_doc_setup cur_doc_setup%ROWTYPE;
463 l_document_fee NUMBER (20, 2) DEFAULT 0;
464 l_delivery_fee igs_as_doc_dlvy_fee.amount%TYPE;
465 l_number_of_copies NUMBER;
466 l_copies_in_slab NUMBER;
467 l_num_stu_ords NUMBER := 0;
468 l_start_at NUMBER := 0;
469 l_temp_var NUMBER := 0;
470 l_diff_in_range NUMBER := 0;
471 l_doc_type igs_as_doc_details.document_type%TYPE;
472 l_ttl_itm_chrgd_und_free NUMBER;
473 l_num_free_cops NUMBER;
474 l_diff_free_ofr_and_avail NUMBER;
475 BEGIN
476 SELECT DECODE (
477 p_document_type,
478 'ENCERT', 'ENCERT',
479 'OFFICIAL', 'TRANSCRIPT',
480 'UNOFFICIAL', 'TRANSCRIPT',
481 'TRANSCRIPT')
482 INTO l_doc_type
483 FROM dual;
484 --
485 -- Get prev num of copies placed by the student
486 --
487 OPEN cur_num_stu_ords (NVL (p_item_number, 999999999999999));
488 FETCH cur_num_stu_ords INTO l_num_stu_ords;
489 CLOSE cur_num_stu_ords;
490 --
491 -- Check if the person has paid life time transcript fee.
492 --
493 OPEN cur_life_time_fee_paid (p_person_id);
494 FETCH cur_life_time_fee_paid INTO l_life_time_fee_paid;
495 CLOSE cur_life_time_fee_paid;
496 --
497 -- Get the delivery fee for the document.
498 --
499 OPEN cur_delivery_fee_setup (p_delivery_method_type);
500 FETCH cur_delivery_fee_setup INTO l_delivery_fee;
501 CLOSE cur_delivery_fee_setup;
502 -- If an item were given free transcripts
503 -- and part of the same item was charged since
504 -- num_cop_ordered > num_avail under free
505 OPEN cur_ttl_itm_chrgd_und_free;
506 FETCH cur_ttl_itm_chrgd_und_free INTO l_ttl_itm_chrgd_und_free;
507 CLOSE cur_ttl_itm_chrgd_und_free;
508 --
509 OPEN c_num_free_cops;
510 FETCH c_num_free_cops INTO l_num_free_cops;
511 CLOSE c_num_free_cops;
512 --
513 l_diff_free_ofr_and_avail := NVL (l_ttl_itm_chrgd_und_free, 0) - NVL (l_num_free_cops, 0);
514 l_start_at := NVL (l_num_stu_ords, 0);
515 --
516 IF NVL (l_diff_free_ofr_and_avail, 0) >= 0
517 AND l_doc_type = 'TRANSCRIPT' THEN
518 l_start_at := l_start_at + NVL (l_diff_free_ofr_and_avail, 0);
519 END IF;
520 --
521 l_number_of_copies := NVL (p_number_of_copies, 0);
522 --
523 -- Calculate the Document Fee only if the Life Time Transcript is not paid.
524 --
525 IF (l_doc_type = 'TRANSCRIPT'
526 AND (NVL (l_life_time_fee_paid, 'N') = 'N')
527 )
528 OR l_doc_type = 'ENCERT' THEN
529 --
530 -- Loop through the document setup to compute the fee for the transcripts.
531 --
532 FOR rec_cur_doc_setup IN cur_doc_setup (p_document_type, l_start_at) LOOP
533 l_temp_var := l_temp_var + 1;
534 IF l_temp_var > 100 THEN
535 l_document_fee := 10000;
536 END IF;
537 EXIT WHEN l_number_of_copies = 0
538 OR l_temp_var > 100;
539 --
540 l_copies_in_slab := 0;
541 l_diff_in_range := (rec_cur_doc_setup.upper_range - rec_cur_doc_setup.lower_range) + 1;
542 --
543 IF cur_doc_setup%ROWCOUNT = 1 THEN
544 IF l_number_of_copies > (rec_cur_doc_setup.upper_range - l_start_at) THEN
545 l_copies_in_slab := rec_cur_doc_setup.upper_range - l_start_at;
546 ELSE
547 l_copies_in_slab := l_number_of_copies;
548 END IF;
549 --
550 IF (rec_cur_doc_setup.payment_type IN ('O', 'F')) THEN
551 l_document_fee := l_document_fee + rec_cur_doc_setup.amount;
552 EXIT;
553 ELSE
554 l_document_fee := l_document_fee
555 + (l_copies_in_slab * rec_cur_doc_setup.amount);
556 END IF;
557 ELSE
558 IF l_diff_in_range > l_number_of_copies THEN
559 l_copies_in_slab := l_number_of_copies;
560 ELSE
561 l_copies_in_slab := l_diff_in_range;
562 END IF;
563 --
564 IF (rec_cur_doc_setup.payment_type IN ('O', 'F')) THEN
565 l_document_fee := l_document_fee + rec_cur_doc_setup.amount;
566 EXIT;
567 ELSE
568 l_document_fee := l_document_fee
569 + (l_copies_in_slab * rec_cur_doc_setup.amount);
570 END IF;
571 END IF;
572 l_number_of_copies := l_number_of_copies - l_copies_in_slab;
573 END LOOP;
574 END IF;
575 --
576 -- Return the sum of Document Fee and Delivery Fee for the document.
577 --
578 IF (p_include_delivery_fee = 'Y') THEN
579 RETURN (NVL (l_document_fee, 0) + NVL (l_delivery_fee, 0));
580 ELSE
581 RETURN (NVL (l_document_fee, 0));
582 END IF;
583 END get_transcript_fee;
584 --
585 --
586 --
587 FUNCTION enrp_get_career_dates (p_person_id IN NUMBER, p_course_type IN VARCHAR2)
588 RETURN VARCHAR2 AS
589 CURSOR c_comm_dt (
590 cp_person_id igs_pe_person.person_id%TYPE,
591 cp_course_type igs_ps_type.course_type%TYPE
592 ) IS
593 SELECT spa.commencement_dt
594 FROM igs_en_stdnt_ps_att_all spa,
595 igs_ps_ver_all pv
596 WHERE spa.person_id = cp_person_id
597 AND pv.course_type = cp_course_type
598 AND spa.course_cd = pv.course_cd
599 AND spa.version_number = pv.version_number
600 AND spa.course_attempt_status <> 'UNCONFIRM'
601 AND spa.commencement_dt IS NOT NULL
602 ORDER BY spa.commencement_dt ASC;
603 CURSOR c_cmpl_dt (
604 cp_person_id igs_pe_person.person_id%TYPE,
605 cp_course_type igs_ps_type.course_type%TYPE
606 ) IS
607 SELECT spa.course_rqrmnts_complete_dt
608 FROM igs_en_stdnt_ps_att_all spa,
609 igs_ps_ver_all pv
610 WHERE spa.person_id = cp_person_id
611 AND pv.course_type = cp_course_type
612 AND spa.course_cd = pv.course_cd
613 AND spa.version_number = pv.version_number
614 AND spa.course_attempt_status IN ('ENROLLED', 'COMPLETED')
615 ORDER BY spa.course_rqrmnts_complete_dt DESC;
616 v_commencement_dt igs_en_stdnt_ps_att_all.commencement_dt%TYPE;
617 v_course_rqrmnts_complete_dt igs_en_stdnt_ps_att_all.course_rqrmnts_complete_dt%TYPE;
618 BEGIN
619 /* This function will return the earliest commencement date and the latest
620 course requirements completion date of the particular student in a particular
621 career passed as parameter
622 1. if there are no commencement dates available, for the programs which are not 'UNCOMFIRMED',
623 within the specified career, then this function will return null
624 2. If more than one program with the specified career has commencement date
625 then the least/earliest will be picked up
626 3. If the COURSE_RQRMNTS_COMPLETE_DT is not availble for any of the
627 'ENROLLED' and 'COMPLETED' programs, then requirements completion date will not be returned
628 4. If all the 'ENROLLED' and 'COMPLETED' programs have a requirments completion date
629 the latest of the dates will be picked up
630 5. If the Commencement date is found and Completiont date is not
631 then the commencement date alone will be returned
632 6. If the Commencement date and Completiont date are found then both will be returned
633 7. A freak case is also handled where the commencement date is not available
634 but the completion date is found then completion date is being returned
635 */
636 OPEN c_comm_dt (p_person_id, p_course_type);
637 FETCH c_comm_dt INTO v_commencement_dt;
638 IF c_comm_dt%NOTFOUND THEN
639 CLOSE c_comm_dt;
640 RETURN NULL;
641 END IF;
642 CLOSE c_comm_dt;
643 OPEN c_cmpl_dt (p_person_id, p_course_type);
644 FETCH c_cmpl_dt INTO v_course_rqrmnts_complete_dt;
645 IF c_cmpl_dt%NOTFOUND THEN
646 CLOSE c_cmpl_dt;
647 IF v_commencement_dt IS NOT NULL THEN
648 RETURN (TO_CHAR (v_commencement_dt) || ' - ');
649 ELSE
650 RETURN NULL;
651 END IF;
652 END IF;
653 CLOSE c_cmpl_dt;
654 IF v_commencement_dt IS NULL
655 AND v_course_rqrmnts_complete_dt IS NULL THEN
656 RETURN NULL;
657 ELSIF v_commencement_dt IS NOT NULL
658 AND v_course_rqrmnts_complete_dt IS NULL THEN
659 RETURN (TO_CHAR (v_commencement_dt) || ' - ');
660 ELSIF v_commencement_dt IS NULL
661 AND v_course_rqrmnts_complete_dt IS NOT NULL THEN
662 -- this case should ideally not occur just added in case
663 RETURN ('NULL TO '|| TO_CHAR (v_course_rqrmnts_complete_dt));
664 ELSIF v_commencement_dt IS NOT NULL
665 AND v_course_rqrmnts_complete_dt IS NOT NULL THEN
666 RETURN (TO_CHAR (v_commencement_dt) || ' - ' || TO_CHAR (v_course_rqrmnts_complete_dt));
667 END IF;
668 END enrp_get_career_dates;
669 --
670 --
671 --
672 PROCEDURE create_invoice (
673 p_order_number IN NUMBER,
674 p_payment_type IN VARCHAR2,
675 p_invoice_id OUT NOCOPY NUMBER,
676 p_return_status OUT NOCOPY VARCHAR2,
677 p_msg_count OUT NOCOPY NUMBER,
678 p_msg_data OUT NOCOPY VARCHAR2,
679 p_waiver_amount OUT NOCOPY NUMBER
680 ) AS
681 ------------------------------------------------------------------------------
682 -- CHANGE HISTORY:
683 -- WHO WHEN WHAT
684 -- UUDAYAPR 09-MAR-2004 BUG#3478599.Added a new cursor c_igs_fi_invln to check whether a record already exists
685 -- for the given invoice id with the error flag set to y .
686 -- vvutukur 27-Nov-2002 Enh#2584986.GL Interface Build. Removed the references to igs_fi_cur. Instead defaulted
687 -- the currency with the one that is set up in System Options Form. The same has been
688 -- used for the creation of the charge record.Passed exchange_rate always 1.
689 -- vvutukur 19-Sep-2002 Enh#2564643.Removed references to subaccount_id from 1)cursor c_ftci and
690 -- 2)call to IGS_FI_SS_CHARGES_API_PVT.create_charge.
691 -- kdande 28-Jun-2002 Bug# 2434054. Changed the cursor c_ftci to consider
692 -- only the Active Fee Type Calendar Instances.
693 -- smadathi 24-Jun-2002 Bug 2404720. CURSOR c_ftci select statement modified
694 -- to include DESCRIPTION column. The call to
695 -- igs_fi_ss_charges_api_pvt.create_charge was modified
696 -- to pass this description value to the formal parameter
697 -- p_invoice_desc.
698 -- vvutukur 13-may-2002 Bug#2426560.Modified cursor c_ftci to select active
699 -- fee type of system fee type as Document.ie., included
700 -- closed_ind also in where clause.
701 -- msrinivi 12-Aug-2002 Bug 2490258 - Added exception block to create_invoice
702 -- and raised error when order not found, sysdate
703 -- not in the ftci start end date aliases
704 -- swaghmar 22-Aug-2005 Bug 4506599
705 ------------------------------------------------------------------------------
706 CURSOR c_ord (cp_order_number igs_as_order_hdr.order_number%TYPE) IS
707 SELECT (ord.delivery_fee + ord.order_fee) total_amount,
708 ord.*,
709 ord.ROWID row_id
710 FROM igs_as_order_hdr ord
711 WHERE order_number = cp_order_number;
712
713 CURSOR c_inv(cp_inv igs_as_order_hdr.invoice_id%TYPE) IS
714 SELECT invoice_amount_due
715 FROM igs_fi_inv_int_all
716 WHERE invoice_id = cp_inv;
717
718
719 v_ord_rec c_ord%ROWTYPE;
720
721 CURSOR c_ftci IS
722 SELECT ftci.fee_cal_type,
723 ftci.fee_ci_sequence_number,
724 ftci.fee_type,
725 ft.description description
726 FROM igs_fi_f_typ_ca_inst ftci,
727 igs_ca_da_inst_v daiv,
728 igs_ca_da_inst_v daiv1,
729 igs_fi_fee_type ft,
730 igs_fi_fee_str_stat stat
731 WHERE ftci.fee_type = ft.fee_type
732 AND ft.s_fee_type = 'DOCUMENT'
733 AND ftci.fee_type_ci_status = stat.fee_structure_status
734 AND stat.s_fee_structure_status = 'ACTIVE'
735 AND NVL (ft.closed_ind, 'N') = 'N'
736 AND (daiv.dt_alias = ftci.start_dt_alias
737 AND daiv.sequence_number = ftci.start_dai_sequence_number
738 AND daiv.cal_type = ftci.fee_cal_type
739 AND daiv.ci_sequence_number = ftci.fee_ci_sequence_number
740 )
741 AND (daiv1.dt_alias = ftci.end_dt_alias
742 AND daiv1.sequence_number = ftci.end_dai_sequence_number
743 AND daiv1.cal_type = ftci.fee_cal_type
744 AND daiv1.ci_sequence_number = ftci.fee_ci_sequence_number
745 )
746 AND SYSDATE BETWEEN daiv.alias_val AND NVL (daiv1.alias_val, SYSDATE)
747 ORDER BY daiv.alias_val DESC;
748 v_ftci_rec c_ftci%ROWTYPE;
749 l_v_currency igs_fi_control_all.currency_cd%TYPE;
750 l_v_curr_desc fnd_currencies_tl.NAME%TYPE;
751 l_v_message_name fnd_new_messages.message_name%TYPE;
752 --CURSOR ADDED FOR CHECKING WHETHER A RECORD ALREADY EXISTS FOR THE INVOICE ID GIVEN.
753 CURSOR c_igs_fi_invln(cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
754 SELECT '1'
755 FROM igs_fi_invln_int_all
756 WHERE invoice_id = cp_invoice_id
757 AND NVL(error_account,'N') = 'Y'
758 AND ROWNUM < 2;
759 rec_c_igs_fi_invln c_igs_fi_invln%ROWTYPE;
760 p_waiver_amount_ord NUMBER;
761 BEGIN
762 OPEN c_ord (p_order_number);
763 FETCH c_ord INTO v_ord_rec;
764 IF c_ord%NOTFOUND THEN
765 CLOSE c_ord;
766 p_return_status := fnd_api.g_ret_sts_error;
767 fnd_message.set_name ('IGS', 'IGS_SS_AS_NO_SUCH_ORD');
768 fnd_msg_pub.ADD;
769 RAISE fnd_api.g_exc_error;
770 END IF;
771 CLOSE c_ord;
772 IF v_ord_rec.invoice_id IS NOT NULL THEN
773 -- check if invoice is generated with error account 'Y'
774 OPEN c_igs_fi_invln(cp_invoice_id => v_ord_rec.invoice_id);
775 FETCH c_igs_fi_invln INTO rec_c_igs_fi_invln;
776 IF c_igs_fi_invln%FOUND THEN
777 CLOSE c_igs_fi_invln;
778 p_return_status := fnd_api.g_ret_sts_error;
779 fnd_message.set_name ('IGS', 'IGS_FI_SRC_TXN_ACC_INV');
780 fnd_msg_pub.ADD;
781 RAISE fnd_api.g_exc_error;
782 END IF;
783 CLOSE c_igs_fi_invln;
784
785 p_return_status := fnd_api.g_ret_sts_success;
786 p_invoice_id := v_ord_rec.invoice_id;
787 p_waiver_amount := '0.0';
788 ELSE
789 OPEN c_ftci;
790 FETCH c_ftci INTO v_ftci_rec;
791 IF c_ftci%NOTFOUND THEN
792 CLOSE c_ftci;
793 p_return_status := fnd_api.g_ret_sts_error;
794 fnd_message.set_name ('IGS', 'IGS_SS_AS_FI_CAL_NOT_SET');
795 fnd_msg_pub.ADD;
796 RAISE fnd_api.g_exc_error;
797 END IF;
798 CLOSE c_ftci;
799 --Capture the default currency that is set up in System Options Form.
800 igs_fi_gen_gl.finp_get_cur (
801 p_v_currency_cd => l_v_currency,
802 p_v_curr_desc => l_v_curr_desc,
803 p_v_message_name => l_v_message_name
804 );
805 IF l_v_message_name IS NOT NULL THEN
806 fnd_message.set_name ('IGS', l_v_message_name);
807 fnd_msg_pub.ADD;
808 RAISE fnd_api.g_exc_error;
809 END IF;
810 igs_fi_ss_charges_api_pvt.create_charge (
811 p_api_version => 2.0,
812 p_init_msg_list => fnd_api.g_false,
813 p_commit => fnd_api.g_false,
814 p_validation_level => fnd_api.g_valid_level_full,
815 p_person_id => v_ord_rec.person_id,
816 p_fee_type => v_ftci_rec.fee_type,
817 p_fee_cat => NULL,
818 p_fee_cal_type => v_ftci_rec.fee_cal_type,
819 p_fee_ci_sequence_number => v_ftci_rec.fee_ci_sequence_number,
820 p_course_cd => NULL,
821 p_attendance_type => NULL,
822 p_attendance_mode => NULL,
823 p_invoice_amount => v_ord_rec.total_amount,
824 p_invoice_creation_date => SYSDATE,
825 p_invoice_desc => v_ftci_rec.description,
826 p_transaction_type => 'DOCUMENT',
827 p_currency_cd => l_v_currency,
828 p_exchange_rate => 1,
829 p_effective_date => NULL,
830 p_waiver_flag => NULL,
831 p_waiver_reason => NULL,
832 p_source_transaction_id => NULL,
833 p_invoice_id => p_invoice_id,
834 x_return_status => p_return_status,
835 x_msg_count => p_msg_count,
836 x_msg_data => p_msg_data,
837 x_waiver_amount => p_waiver_amount
838 );
839 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
840 RETURN;
841 END IF;
842 --
843 -- update the Order header table with the Invoice id returned by the create_charge api
844 --
845 FOR order_hdr_rec IN c_ord (p_order_number) LOOP
846 igs_as_order_hdr_pkg.update_row (
847 x_mode => 'R',
848 x_rowid => order_hdr_rec.row_id,
849 x_order_number => order_hdr_rec.order_number,
850 x_order_status => order_hdr_rec.order_status,
851 x_date_completed => order_hdr_rec.date_completed,
852 x_person_id => order_hdr_rec.person_id,
853 x_addr_line_1 => order_hdr_rec.addr_line_1,
854 x_addr_line_2 => order_hdr_rec.addr_line_2,
855 x_addr_line_3 => order_hdr_rec.addr_line_3,
856 x_addr_line_4 => order_hdr_rec.addr_line_4,
857 x_city => order_hdr_rec.city,
858 x_state => order_hdr_rec.state,
859 x_province => order_hdr_rec.province,
860 x_county => order_hdr_rec.county,
861 x_country => order_hdr_rec.country,
862 x_postal_code => order_hdr_rec.postal_code,
863 x_email_address => order_hdr_rec.email_address,
864 x_phone_country_code => order_hdr_rec.phone_country_code,
865 x_phone_area_code => order_hdr_rec.phone_area_code,
866 x_phone_number => order_hdr_rec.phone_number,
867 x_phone_extension => order_hdr_rec.phone_extension,
868 x_fax_country_code => order_hdr_rec.fax_country_code,
869 x_fax_area_code => order_hdr_rec.fax_area_code,
870 x_fax_number => order_hdr_rec.fax_number,
871 x_delivery_fee => order_hdr_rec.delivery_fee,
872 x_order_fee => order_hdr_rec.order_fee,
873 x_request_type => order_hdr_rec.request_type,
874 x_submit_method => order_hdr_rec.submit_method,
875 x_invoice_id => p_invoice_id, -- this is the value that is being updated
876 x_return_status => p_return_status,
877 x_msg_data => p_msg_data,
878 x_msg_count => p_msg_count,
879 x_order_placed_by => order_hdr_rec.order_placed_by,
880 x_order_description => order_hdr_rec.order_description,
881 p_init_msg_list => FND_API.G_FALSE
882 );
883 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
884 RETURN;
885 END IF;
886 END LOOP;
887 END IF;
888 -- 22 Aug 05 swaghmar Modified for Bug 4506599
889 OPEN c_inv(v_ord_rec.invoice_id);
890 FETCH c_inv INTO p_waiver_amount_ord;
891 CLOSE c_inv;
892 IF ((p_payment_type = 'BILL_ME_LATER') OR (p_waiver_amount_ord = 0)) THEN
893 igs_as_documents_api.update_document_details (
894 p_order_number => p_order_number,
895 p_item_number => NULL,
896 p_init_msg_list => fnd_api.g_false,
897 p_return_status => p_return_status,
898 p_msg_count => p_msg_count,
899 p_msg_data => p_msg_data
900 );
901 END IF;
902
903 RETURN;
904 EXCEPTION
905 WHEN fnd_api.g_exc_error THEN
906 p_return_status := fnd_api.g_ret_sts_error;
907 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
908 RETURN;
909 WHEN fnd_api.g_exc_unexpected_error THEN
910 p_return_status := fnd_api.g_ret_sts_unexp_error;
911 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
912 RETURN;
913 WHEN OTHERS THEN
914 p_return_status := fnd_api.g_ret_sts_unexp_error;
915 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
916 fnd_message.set_token ('NAME', 'CREATE_INVOICE_ID : ' || SQLERRM);
917 fnd_msg_pub.ADD;
918 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
919 RETURN;
920 END create_invoice;
921 --
922 --
923 --
924 FUNCTION show_bill_me_later (p_person_id IN NUMBER)
925 RETURN VARCHAR2 IS
926 --
927 -- Cursor to find out if the institution allows Bill Me Later option
928 -- for Order Payment made by the student. This check is not for the
929 -- administrator
930 --
931 CURSOR cur_allow_bill_me_later IS
932 SELECT NVL (bill_me_later_ind, 'N') bill_me_later_ind
933 FROM igs_as_docproc_stup;
934 --
935 -- Cursor to find out if the student is a student is a current or
936 -- former student
937 --
938 CURSOR cur_student_type (cp_person_id IN NUMBER) IS
939 SELECT 'Y' current_student
940 FROM igs_pe_person_types typ,
941 igs_pe_typ_instances_all inst
942 WHERE inst.person_id = cp_person_id
943 AND inst.person_type_code = typ.person_type_code
944 AND typ.system_type = 'STUDENT'
945 AND inst.start_date <= SYSDATE
946 AND NVL (inst.end_date, SYSDATE) >= SYSDATE;
947 --
948 -- Local variables
949 --
950 rec_cur_student_type cur_student_type%ROWTYPE;
951 rec_cur_allow_bill_me_later cur_allow_bill_me_later%ROWTYPE;
952 allow_bill_me_later VARCHAR2 (1);
953 BEGIN
954 --
955 -- Check if Bill Me Later is allowed for the student or not
956 --
957 OPEN cur_allow_bill_me_later;
958 FETCH cur_allow_bill_me_later INTO rec_cur_allow_bill_me_later;
959 CLOSE cur_allow_bill_me_later;
960 --
961 IF (rec_cur_allow_bill_me_later.bill_me_later_ind = 'Y') THEN
962 allow_bill_me_later := 'Y';
963 ELSE
964 allow_bill_me_later := 'N';
965 END IF;
966 --
967 -- Check for student type only if the institution is allowing Bill Me Later
968 --
969 IF (allow_bill_me_later = 'Y') THEN
970 --
971 -- Check if the Student is current student or former student
972 --
973 OPEN cur_student_type (p_person_id);
974 FETCH cur_student_type INTO rec_cur_student_type;
975 IF (cur_student_type%FOUND) THEN
976 allow_bill_me_later := 'Y';
977 ELSE
978 allow_bill_me_later := 'N';
979 END IF;
980 CLOSE cur_student_type;
981 END IF;
982 --
983 RETURN (allow_bill_me_later);
984 END show_bill_me_later;
985 --
986 --
987 --
988 PROCEDURE update_order_fee (
989 p_order_number NUMBER,
990 p_item_number NUMBER,
991 p_old_sub_doc_type VARCHAR2,
992 p_old_deliv_type VARCHAR2,
993 p_old_num_copies VARCHAR2,
994 p_new_sub_doc_type VARCHAR2,
995 p_new_deliv_type VARCHAR2,
996 p_new_num_copies VARCHAR2,
997 p_return_status OUT NOCOPY VARCHAR2,
998 p_msg_data OUT NOCOPY VARCHAR2,
999 p_msg_count OUT NOCOPY NUMBER
1000 ) AS
1001 CURSOR c_order IS
1002 SELECT ROWID row_id,
1003 hdr.*
1004 FROM igs_as_order_hdr hdr
1005 WHERE order_number = p_order_number;
1006 cur_order c_order%ROWTYPE;
1007 CURSOR c_doc IS
1008 SELECT SUM (NVL (overridden_document_fee, doc_fee_per_copy)),
1009 SUM (NVL (overridden_doc_delivery_fee, delivery_fee))
1010 FROM igs_as_doc_details
1011 WHERE order_number = p_order_number;
1012 --
1013 -- Variables
1014 --
1015 lndocfeepercopy igs_as_doc_details.doc_fee_per_copy%TYPE;
1016 lndeliveryfee igs_as_doc_details.delivery_fee%TYPE;
1017 l_new_doc_fee igs_as_doc_details.doc_fee_per_copy%TYPE;
1018 l_dummy_plan_id igs_as_doc_details.plan_id%TYPE;
1019 --
1020 BEGIN
1021 --
1022 OPEN c_order;
1023 FETCH c_order INTO cur_order;
1024 CLOSE c_order;
1025 --
1026 OPEN c_doc;
1027 FETCH c_doc INTO lndocfeepercopy,
1028 lndeliveryfee;
1029 CLOSE c_doc;
1030 --
1031 -- Once all the information are available make a call to the update row of the order
1032 -- header table.
1033 --
1034 igs_as_order_hdr_pkg.update_row (
1035 x_msg_count => p_msg_count,
1036 x_msg_data => p_msg_data,
1037 x_return_status => p_return_status,
1038 x_rowid => cur_order.row_id,
1039 x_order_number => cur_order.order_number,
1040 x_order_status => cur_order.order_status,
1041 x_date_completed => cur_order.date_completed,
1042 x_person_id => cur_order.person_id,
1043 x_addr_line_1 => cur_order.addr_line_1,
1044 x_addr_line_2 => cur_order.addr_line_2,
1045 x_addr_line_3 => cur_order.addr_line_3,
1046 x_addr_line_4 => cur_order.addr_line_4,
1047 x_city => cur_order.city,
1048 x_state => cur_order.state,
1049 x_province => cur_order.province,
1050 x_county => cur_order.county,
1051 x_country => cur_order.country,
1052 x_postal_code => cur_order.postal_code,
1053 x_email_address => cur_order.email_address,
1054 x_phone_country_code => cur_order.phone_country_code,
1055 x_phone_area_code => cur_order.phone_area_code,
1056 x_phone_number => cur_order.phone_number,
1057 x_phone_extension => cur_order.phone_extension,
1058 x_fax_country_code => cur_order.fax_country_code,
1059 x_fax_area_code => cur_order.fax_area_code,
1060 x_fax_number => cur_order.fax_number,
1061 --X_LIFE_TIME_FEE_PAID => cur_order.LIFE_TIME_FEE_PAID,
1062 x_delivery_fee => NVL (lndeliveryfee, 0),
1063 x_order_fee => NVL (lndocfeepercopy, 0),
1064 x_request_type => cur_order.request_type,
1065 x_submit_method => cur_order.submit_method,
1066 x_invoice_id => cur_order.invoice_id,
1067 x_order_placed_by => cur_order.order_placed_by,
1068 x_order_description => cur_order.order_description
1069 );
1070 END update_order_fee;
1071 --
1072 --
1073 --
1074
1075 --swaghmar bug# 4377816
1076 FUNCTION inst_is_edi_partner (
1077 p_inst_code VARCHAR2
1078 ) RETURN VARCHAR2 AS
1079 --
1080 CURSOR c_edi IS
1081 SELECT edi_transaction_handling,
1082 edi_id_number,
1083 edi_payment_method,
1084 edi_payment_format,
1085 edi_remittance_method,
1086 edi_remittance_instruction,
1087 edi_tp_header_id,
1088 edi_ece_tp_location_code
1089 FROM hz_contact_points cont,
1090 igs_pe_hz_parties ipz,
1091 hz_parties org
1092 WHERE cont.owner_table_name = 'HZ_PARTIES'
1093 AND cont.owner_table_id = org.party_id
1094 AND ipz.party_id = org.party_id
1095 AND ipz.oss_org_unit_cd = p_inst_code;
1096 cur_edi c_edi%ROWTYPE;
1097 lvcedipartner VARCHAR2 (1) DEFAULT 'N';
1098 BEGIN
1099 /* The fuction will be enabled when it is decided that EDI related info will be available to OSS.
1100 This function is just a place holder and currently always returns N meaning
1101 Institution is not an EDI partner.
1102 FOR CUR_EDI IN C_EDI LOOP
1103 IF CUR_EDI.EDI_ID_NUMBER IS NOT NULL THEN
1104 lvcEDIPartner := 'Y';
1105 EXIT;
1106 END IF;
1107 END LOOP;
1108 */
1109 RETURN lvcedipartner;
1110 END;
1111 --
1112 --
1113 --
1114 FUNCTION is_all_progs_allowed
1115 RETURN VARCHAR2 AS
1116 --
1117 CURSOR c_all_prog IS
1118 SELECT all_acad_hist_in_one_doc_ind
1119 FROM igs_as_docproc_stup;
1120 --
1121 l_all_prg VARCHAR2 (1);
1122 BEGIN
1123 OPEN c_all_prog;
1124 FETCH c_all_prog INTO l_all_prg;
1125 CLOSE c_all_prog;
1126 --
1127 RETURN l_all_prg;
1128 END is_all_progs_allowed;
1129
1130 PROCEDURE create_as_application (
1131 p_credit_id IN igs_fi_applications.credit_id%TYPE,
1132 p_invoice_id IN igs_fi_applications.invoice_id%TYPE,
1133 p_amount_apply IN igs_fi_applications.amount_applied%TYPE,
1134 p_appl_type IN igs_fi_applications.application_type%TYPE,
1135 p_appl_hierarchy_id IN igs_fi_applications.appl_hierarchy_id%TYPE,
1136 p_validation IN VARCHAR2,
1137 p_application_id OUT NOCOPY igs_fi_applications.application_id%TYPE,
1138 p_err_msg OUT NOCOPY fnd_new_messages.message_name%TYPE,
1139 p_status OUT NOCOPY VARCHAR2
1140 ) AS
1141 /*----------------------------------------------------------------------------
1142 || Created By :
1143 || Created On :
1144 || Purpose :
1145 || Known limitations, enhancements or remarks :
1146 || Change History :
1147 || Who When What
1148 || (reverse chronological order - newest change first)
1149 || vvutukur 18-Nov-2002 Enh#2584986.Modified the call to create_application to pass sysdate to the
1150 || new parameter p_d_gl_date.
1151 ----------------------------------------------------------------------------*/
1152 lb_status BOOLEAN := TRUE;
1153 l_dr_gl_ccid igs_fi_cr_activities.dr_gl_ccid%TYPE;
1154 l_cr_gl_ccid igs_fi_cr_activities.cr_gl_ccid%TYPE;
1155 l_dr_account_cd igs_fi_cr_activities.dr_account_cd%TYPE;
1156 l_cr_account_cd igs_fi_cr_activities.cr_account_cd%TYPE;
1157 l_unapp_amount igs_fi_credits_all.unapplied_amount%TYPE;
1158 l_inv_amt_due igs_fi_inv_int_all.invoice_amount_due%TYPE;
1159 BEGIN
1160 igs_fi_gen_007.create_application (
1161 p_application_id => p_application_id,
1162 p_credit_id => p_credit_id,
1163 p_invoice_id => p_invoice_id,
1164 p_amount_apply => p_amount_apply,
1165 p_appl_type => p_appl_type,
1166 p_appl_hierarchy_id => p_appl_hierarchy_id,
1167 p_validation => p_validation,
1168 p_dr_gl_ccid => l_dr_gl_ccid,
1169 p_cr_gl_ccid => l_cr_gl_ccid,
1170 p_dr_account_cd => l_dr_account_cd,
1171 p_cr_account_cd => l_cr_account_cd,
1172 p_unapp_amount => l_unapp_amount,
1173 p_inv_amt_due => l_inv_amt_due,
1174 p_err_msg => p_err_msg,
1175 p_status => lb_status,
1176 p_d_gl_date => TRUNC (SYSDATE)
1177 );
1178 IF (lb_status) THEN
1179 p_status := 'TRUE';
1180 ELSE
1181 p_status := 'FALSE';
1182 END IF;
1183 END create_as_application;
1184 --
1185 --
1186 --
1187 FUNCTION get_prg_st_end_dts (
1188 p_person_id NUMBER,
1189 p_course_cd VARCHAR2
1190 ) RETURN VARCHAR2 AS
1191 CURSOR c_sua_cmnt IS
1192 SELECT load_description
1193 FROM igs_en_su_attempt,
1194 igs_ca_teach_to_load_v
1195 WHERE person_id = p_person_id
1196 AND course_cd = p_course_cd
1197 AND unit_attempt_status IN ('ENROLLED', 'COMPLETED', 'DUPLICATE', 'DISCONTIN')
1198 AND teach_cal_type = cal_type
1199 AND teach_ci_sequence_number = ci_sequence_number
1200 ORDER BY load_start_dt ASC;
1201 --
1202 CURSOR c_sua_end IS
1203 SELECT load_description
1204 FROM igs_en_su_attempt,
1205 igs_ca_teach_to_load_v
1206 WHERE person_id = p_person_id
1207 AND course_cd = p_course_cd
1208 AND unit_attempt_status IN ('ENROLLED', 'COMPLETED', 'DUPLICATE', 'DISCONTIN')
1209 AND teach_cal_type = cal_type
1210 AND teach_ci_sequence_number = ci_sequence_number
1211 ORDER BY load_start_dt DESC;
1212 --
1213 CURSOR c_conferral_dt (p_person_id NUMBER, p_course_cd VARCHAR) IS
1214 SELECT spaa.conferral_date
1215 FROM igs_en_spa_awd_aim spaa,
1216 igs_gr_graduand_all gr
1217 WHERE spaa.person_id = p_person_id
1218 AND spaa.course_cd = p_course_cd
1219 AND gr.person_id = spaa.person_id
1220 AND gr.course_cd = spaa.course_cd
1221 AND gr.award_cd = spaa.award_cd
1222 AND EXISTS (
1223 SELECT 'X'
1224 FROM igs_gr_stat grst
1225 WHERE grst.graduand_status = gr.graduand_status
1226 AND grst.s_graduand_status = 'GRADUATED');
1227 --
1228 l_return_string VARCHAR2 (2000);
1229 BEGIN
1230 FOR c_sua_cmnt_rec IN c_sua_cmnt LOOP
1231 l_return_string := c_sua_cmnt_rec.load_description;
1232 EXIT;
1233 END LOOP;
1234 --
1235 FOR c_conferral_dt_rec IN c_conferral_dt (p_person_id, p_course_cd) LOOP
1236 l_return_string := l_return_string || ' ' || RTRIM (LTRIM (fnd_message.get_string ('IGS', 'IGS_GE_GRAD_DATE'))) || ' ' || c_conferral_dt_rec.conferral_date;
1237 EXIT;
1238 END LOOP;
1239 --
1240 FOR c_sua_end_rec IN c_sua_end LOOP
1241 l_return_string := l_return_string || ' ' || RTRIM (LTRIM (fnd_message.get_string ('IGS', 'IGS_GE_UNTIL'))) || ' ' || c_sua_end_rec.load_description;
1242 EXIT;
1243 END LOOP;
1244 --
1245 RETURN l_return_string;
1246 END get_prg_st_end_dts;
1247 --
1248 -- Added by msrinivi acc to bug 2318474 to delete items along with order
1249 --
1250 PROCEDURE delete_order_and_items (
1251 p_order_number IN igs_as_order_hdr.order_number%TYPE,
1252 p_msg_count OUT NOCOPY NUMBER,
1253 p_msg_data OUT NOCOPY VARCHAR2,
1254 p_return_status OUT NOCOPY VARCHAR2
1255 ) AS
1256 --
1257 CURSOR c_items IS
1258 SELECT ROWID
1259 FROM igs_as_doc_details
1260 WHERE order_number = p_order_number;
1261 --
1262 CURSOR c_order IS
1263 SELECT ROWID
1264 FROM igs_as_order_hdr
1265 WHERE order_number = p_order_number;
1266 BEGIN
1267 -- Delete items first
1268 FOR c_items_rec IN c_items LOOP
1269 EXIT WHEN c_items%NOTFOUND;
1270 igs_as_doc_details_pkg.delete_row (
1271 x_rowid => c_items_rec.ROWID,
1272 x_msg_count => p_msg_count,
1273 x_msg_data => p_msg_data,
1274 x_return_status => p_return_status
1275 );
1276 END LOOP;
1277 --
1278 -- Delete order now
1279 --
1280 IF p_return_status IS NULL
1281 OR p_return_status = fnd_api.g_ret_sts_success THEN
1282 FOR c_order_rec IN c_order LOOP
1283 EXIT WHEN c_order%NOTFOUND;
1284 igs_as_order_hdr_pkg.delete_row (
1285 x_rowid => c_order_rec.ROWID,
1286 x_msg_count => p_msg_count,
1287 x_msg_data => p_msg_data,
1288 x_return_status => p_return_status
1289 );
1290 END LOOP;
1291 END IF;
1292 --
1293 -- Delete all the interface items for this order
1294 --
1295 DELETE igs_as_ord_itm_int
1296 WHERE order_number = p_order_number;
1297 END delete_order_and_items;
1298 --
1299 --
1300 --
1301 PROCEDURE get_doc_and_delivery_fee (
1302 p_person_id IN NUMBER,
1303 p_document_type IN VARCHAR2,
1304 p_document_sub_type IN VARCHAR2,
1305 p_number_of_copies IN NUMBER,
1306 p_delivery_method_type IN VARCHAR2,
1307 p_document_fee OUT NOCOPY NUMBER,
1308 p_delivery_fee OUT NOCOPY NUMBER,
1309 p_program_on_file IN VARCHAR2,
1310 p_plan_id IN OUT NOCOPY NUMBER,
1311 p_item_number IN NUMBER
1312 ) AS
1313 lnchrgcopy NUMBER := p_number_of_copies;
1314 lnfree VARCHAR2 (1) := 'N';
1315 lndocfee NUMBER := 0;
1316 lncopies_availded NUMBER;
1317 lnyearspassed NUMBER;
1318 lnmonthspassed NUMBER;
1319 lninperd NUMBER;
1320 l_delivery_fee igs_as_doc_dlvy_fee.amount%TYPE;
1321 l_free_plan_id igs_as_servic_plan.plan_id%TYPE;
1322 --
1323 -- Cursor to get all the plans subscribed by the student.
1324 -- Every time a user subscribes to a plan, a record is created in the doc_fee_pmnt table.
1325 --
1326 CURSOR cur_plans_subs (cp_plan_id NUMBER) IS
1327 SELECT subs.person_id,
1328 subs.fee_paid_date,
1329 subs.plan_id,
1330 subs.plan_discon_from,
1331 NVL (subs.num_of_copies, 0) num_of_copies,
1332 subs.cal_type,
1333 subs.ci_sequence_number,
1334 subs.prev_paid_plan,
1335 subs.program_on_file,
1336 pln.plan_type,
1337 pln.unlimited_ind,
1338 pln.quantity_limit,
1339 pln.period_of_plan,
1340 pln.total_periods_covered
1341 FROM igs_as_doc_fee_pmnt subs,
1342 igs_as_servic_plan pln
1343 WHERE subs.plan_id = pln.plan_id
1344 AND subs.person_id = p_person_id
1345 AND subs.plan_id = cp_plan_id
1346 AND NVL (plan_discon_from, SYSDATE + 1) > SYSDATE;
1347 --
1348 fee_pmnt_rec cur_plans_subs%ROWTYPE;
1349 -- Cursor to know whether the free plan is allowed by the institute or not...
1350 CURSOR cur_free_plan IS
1351 SELECT plan_id,
1352 plan_type,
1353 unlimited_ind,
1354 quantity_limit,
1355 period_of_plan,
1356 total_periods_covered
1357 FROM igs_as_servic_plan pl,
1358 igs_lookups_view lk
1359 WHERE pl.plan_type = lk.meaning
1360 AND lk.lookup_type = 'TRANSCRIPT_SERVICE_PLAN_TYPE'
1361 AND lk.lookup_code = 'FREE_TRANSCRIPT'
1362 AND NVL (pl.closed_ind, 'N') = 'N';
1363 -- Cursor to get the delivery Fee:
1364 CURSOR cur_delivery_fee_setup (cp_delivery_method_type IN VARCHAR2) IS
1365 SELECT dlfs.amount amount
1366 FROM igs_as_doc_dlvy_fee dlfs
1367 WHERE dlfs.delivery_method_type = cp_delivery_method_type;
1368 --
1369 CURSOR cur_free_plan_id IS
1370 SELECT plan_id
1371 FROM igs_as_servic_plan pl,
1372 igs_lookups_view lk
1373 WHERE pl.plan_type = lk.meaning
1374 AND lk.lookup_type = 'TRANSCRIPT_SERVICE_PLAN_TYPE'
1375 AND lk.lookup_code = 'FREE_TRANSCRIPT'
1376 AND NVL (pl.closed_ind, 'N') = 'N';
1377 BEGIN
1378 -- First get the delivery fee since it has nothing to do with document type and transcript plan.
1379 OPEN cur_delivery_fee_setup (p_delivery_method_type);
1380 FETCH cur_delivery_fee_setup INTO l_delivery_fee;
1381 CLOSE cur_delivery_fee_setup;
1382 --
1383 p_delivery_fee := NVL (l_delivery_fee, 0);
1384 --Free plan_id
1385 OPEN cur_free_plan_id;
1386 FETCH cur_free_plan_id INTO l_free_plan_id;
1387 CLOSE cur_free_plan_id;
1388 -- Service plan concept is applicable only for transcript and not for Enrollment certification:
1389 IF p_document_type <> 'ENCERT' THEN
1390 IF p_plan_id IS NULL
1391 OR p_plan_id = l_free_plan_id THEN --No plan or free plan
1392 --See if the student has subscribed to any plan
1393 FOR free_plan_rec IN cur_free_plan LOOP
1394 --- See if the person has already availed this :
1395 SELECT SUM (NVL (num_of_copies, 0))
1396 INTO lncopies_availded
1397 FROM igs_as_doc_fee_pmnt
1398 WHERE person_id = p_person_id
1399 AND plan_id = free_plan_rec.plan_id
1400 AND program_on_file IN ('ALL', p_program_on_file);
1401 -- See if the number of copies availed so far still alows the free transcript.
1402 lncopies_availded := NVL (lncopies_availded, 0);
1403 IF p_plan_id = l_free_plan_id THEN
1404 lncopies_availded := 0;
1405 END IF;
1406 IF (free_plan_rec.quantity_limit - lncopies_availded) >= p_number_of_copies THEN
1407 lnfree := 'Y';
1408 p_plan_id := free_plan_rec.plan_id;
1409 ELSE
1410 lnfree := 'N';
1411 IF (free_plan_rec.quantity_limit - lncopies_availded) > 0 THEN
1412 lnchrgcopy := p_number_of_copies - (free_plan_rec.quantity_limit - lncopies_availded);
1413 p_plan_id := free_plan_rec.plan_id;
1414 ELSE
1415 lnchrgcopy := p_number_of_copies;
1416 END IF;
1417 END IF;
1418 END LOOP;
1419 ELSIF p_plan_id IS NOT NULL THEN
1420 --See if the student has subscribed to any service plan:
1421 FOR plan_subs IN cur_plans_subs (p_plan_id) LOOP
1422 IF plan_subs.plan_type = 'Lifetime Transcript' THEN
1423 -- Since Lifetime type is unlimited hence do nothing extra.
1424 p_plan_id := plan_subs.plan_id;
1425 lnfree := 'Y';
1426 lnchrgcopy := 0;
1427 EXIT;
1428 ELSE
1429 ----See whether the student has already ordered some Xcript against the plan.
1430 IF plan_subs.period_of_plan = 'YEARS' THEN
1431 --Get the number of years lapsed between date fee was paid and sysdate
1432 SELECT NVL ((MONTHS_BETWEEN (SYSDATE, plan_subs.fee_paid_date) / 12), 0)
1433 INTO lnyearspassed
1434 FROM DUAL;
1435 IF lnyearspassed >= 0 THEN -- Plan is still Valid
1436 -- See if the student has already availed all the allowed number of copies.
1437 IF (plan_subs.quantity_limit - plan_subs.num_of_copies) > 0 THEN
1438 lnfree := 'Y';
1439 p_plan_id := plan_subs.plan_id;
1440 lnchrgcopy := 0;
1441 EXIT;
1442 ELSE
1443 lnfree := 'N';
1444 lnchrgcopy := p_number_of_copies - (plan_subs.quantity_limit - plan_subs.num_of_copies);
1445 p_plan_id := plan_subs.plan_id;
1446 EXIT;
1447 END IF;
1448 END IF;
1449 ---See if the Period = 'MONTHS'
1450 ELSIF plan_subs.period_of_plan = 'MONTHS' THEN
1451 --Get the number of Months lapsed between date fee was paid and sysdate
1452 SELECT NVL (MONTHS_BETWEEN (plan_subs.fee_paid_date, SYSDATE), 0)
1453 INTO lnyearspassed
1454 FROM DUAL;
1455 IF lnyearspassed >= 0 THEN -- Plan is still Valid
1456 -- See if the student has already availed all the allowed number of copies.
1457 lncopies_availded := plan_subs.quantity_limit - plan_subs.num_of_copies;
1458
1459 IF lncopies_availded - p_number_of_copies >= 0 THEN
1460 lnfree := 'Y';
1461 p_plan_id := plan_subs.plan_id;
1462 lnchrgcopy := 0;
1463 EXIT;
1464 ELSIF lncopies_availded > 0 THEN
1465 lnchrgcopy := p_number_of_copies - lncopies_availded;
1466 lnfree := 'N';
1467 p_plan_id := plan_subs.plan_id;
1468 EXIT;
1469 ELSE
1470 lnchrgcopy := p_number_of_copies;
1471 lnfree := 'N';
1472 p_plan_id := NULL;
1473 EXIT;
1474 END IF;
1475 END IF;
1476 ELSIF (plan_subs.period_of_plan = 'ACADEMIC'
1477 OR plan_subs.period_of_plan = 'TERM'
1478 ) THEN
1479 --Verify if the SYSDATE is between start and End date of the Academic calendar.
1480 BEGIN
1481 SELECT 1
1482 INTO lninperd
1483 FROM igs_ca_inst ci
1484 WHERE SYSDATE BETWEEN ci.start_dt AND ci.end_dt
1485 AND cal_type = plan_subs.cal_type
1486 AND sequence_number = plan_subs.ci_sequence_number;
1487 EXCEPTION
1488 WHEN NO_DATA_FOUND THEN
1489 lninperd := 0;
1490 END;
1491 IF lninperd = 1 THEN -- Plan is still Valid
1492 -- See if the student has already availed all the allowed number of copies.
1493 lncopies_availded := plan_subs.quantity_limit - plan_subs.num_of_copies;
1494 IF lncopies_availded - p_number_of_copies >= 0 THEN
1495 lnfree := 'Y';
1496 p_plan_id := plan_subs.plan_id;
1497 lnchrgcopy := 0;
1498 EXIT;
1499 ELSIF lncopies_availded > 0 THEN
1500 lnchrgcopy := p_number_of_copies - lncopies_availded;
1501 lnfree := 'N';
1502 p_plan_id := plan_subs.plan_id;
1503 EXIT;
1504 ELSE
1505 lnchrgcopy := p_number_of_copies;
1506 lnfree := 'N';
1507 p_plan_id := NULL;
1508 EXIT;
1509 END IF;
1510 END IF;
1511 END IF;
1512 END IF;
1513 END LOOP;
1514 END IF;
1515 END IF; -- End if For Document Type;
1516 -- See if thestudent has to pay:
1517 IF lnfree = 'Y' THEN -- No user will get the document free...
1518 p_document_fee := 0;
1519 RETURN;
1520 ELSE -- Make a call to the existing function to calculate the fee:
1521 lndocfee := igs_as_ss_doc_request.get_transcript_fee (
1522 p_person_id => p_person_id,
1523 p_document_type => p_document_sub_type,
1524 p_number_of_copies => lnchrgcopy,
1525 p_include_delivery_fee => 'N',
1526 p_delivery_method_type => p_delivery_method_type,
1527 p_item_number => p_item_number
1528 );
1529 p_document_fee := NVL (lndocfee, 0);
1530 END IF;
1531 END get_doc_and_delivery_fee;
1532
1533 PROCEDURE pay_lifetime_fees (
1534 p_person_id IN NUMBER,
1535 p_order_number IN NUMBER,
1536 p_return_status OUT NOCOPY VARCHAR2,
1537 p_msg_data OUT NOCOPY VARCHAR2,
1538 p_msg_count OUT NOCOPY NUMBER
1539 ) AS
1540 -- To select the delivery method type:
1541 CURSOR c_deliv_type IS
1542 SELECT delivery_method_type
1543 FROM igs_as_doc_dlvy_typ
1544 WHERE s_delivery_method_type = 'MANUAL'
1545 AND closed_ind = 'Y'
1546 AND delivery_method_type = 'NONE';
1547 ldeliv_type igs_as_doc_dlvy_typ.delivery_method_type%TYPE;
1548 -- To get the document fee setup details
1549 -- for the given document type.
1550 CURSOR cur_doc_setup IS
1551 SELECT dfs.amount amount
1552 FROM igs_as_doc_fee_stup dfs
1553 WHERE dfs.document_type = 'LIFE_TIME_TRANS';
1554 -- Variables
1555 l_item_number igs_as_doc_details.item_number%TYPE;
1556 l_doc_fee igs_as_order_hdr.order_fee%TYPE;
1557 l_delivery_fee igs_as_order_hdr.delivery_fee%TYPE;
1558 l_itm_row_id VARCHAR2 (25);
1559 l_fmnt_row_id VARCHAR2 (25);
1560 l_lifetime_fee igs_as_doc_fee_stup.amount%TYPE;
1561 l_default_country VARCHAR2 (80) := fnd_profile.VALUE ('OSS_COUNTRY_CODE');
1562 l_return_status VARCHAR2 (30);
1563 l_msg_data VARCHAR2 (1000);
1564 l_msg_count NUMBER;
1565 BEGIN
1566 OPEN c_deliv_type;
1567 FETCH c_deliv_type INTO ldeliv_type;
1568 CLOSE c_deliv_type;
1569 --
1570 SELECT igs_as_doc_details_s.NEXTVAL
1571 INTO l_item_number
1572 FROM DUAL;
1573 --
1574 OPEN cur_doc_setup;
1575 FETCH cur_doc_setup INTO l_lifetime_fee;
1576 CLOSE cur_doc_setup;
1577 -- Inserting an Item
1578 igs_as_doc_details_pkg.insert_row (
1579 x_rowid => l_itm_row_id,
1580 x_order_number => p_order_number,
1581 x_document_type => 'TRANSCRIPT',
1582 x_document_sub_type => 'LIFE_TIME_TRANS',
1583 x_item_number => l_item_number,
1584 x_item_status => 'INCOMPLETE',
1585 x_date_produced => NULL,
1586 x_incl_curr_course => NULL,
1587 x_num_of_copies => 1,
1588 x_comments => NULL,
1589 x_recip_pers_name => NULL,
1590 x_recip_inst_name => NULL,
1591 x_recip_addr_line_1 => 'N/A',
1592 x_recip_addr_line_2 => NULL,
1593 x_recip_addr_line_3 => NULL,
1594 x_recip_addr_line_4 => NULL,
1595 x_recip_city => NULL,
1596 x_recip_postal_code => NULL,
1597 x_recip_state => NULL,
1598 x_recip_province => NULL,
1599 x_recip_county => NULL,
1600 x_recip_country => l_default_country,
1601 x_recip_fax_area_code => NULL,
1602 x_recip_fax_country_code => NULL,
1603 x_recip_fax_number => NULL,
1604 x_delivery_method_type => ldeliv_type,
1605 x_programs_on_file => NULL,
1606 x_missing_acad_record_data_ind => NULL,
1607 x_missing_academic_record_data => NULL,
1608 x_send_transcript_immediately => NULL,
1609 x_hold_release_of_final_grades => NULL,
1610 x_fgrade_cal_type => NULL,
1611 x_fgrade_seq_num => NULL,
1612 x_hold_degree_expected => NULL,
1613 x_deghold_cal_type => NULL,
1614 x_deghold_seq_num => NULL,
1615 x_hold_for_grade_chg => NULL,
1616 x_special_instr => NULL,
1617 x_express_mail_type => NULL,
1618 x_express_mail_track_num => NULL,
1619 x_ge_certification => NULL,
1620 x_external_comments => NULL,
1621 x_internal_comments => NULL,
1622 x_dup_requested => NULL,
1623 x_dup_req_date => NULL,
1624 x_dup_sent_date => NULL,
1625 x_enr_term_cal_type => NULL,
1626 x_enr_ci_sequence_number => NULL,
1627 x_incl_attempted_hours => NULL,
1628 x_incl_class_rank => NULL,
1629 x_incl_progresssion_status => NULL,
1630 x_incl_class_standing => NULL,
1631 x_incl_cum_hours_earned => NULL,
1632 x_incl_gpa => NULL,
1633 x_incl_date_of_graduation => NULL,
1634 x_incl_degree_dates => NULL,
1635 x_incl_degree_earned => NULL,
1636 x_incl_date_of_entry => NULL,
1637 x_incl_drop_withdrawal_dates => NULL,
1638 x_incl_hrs_for_curr_term => NULL,
1639 x_incl_majors => NULL,
1640 x_incl_last_date_of_enrollment => NULL,
1641 x_incl_professional_licensure => NULL,
1642 x_incl_college_affiliation => NULL,
1643 x_incl_instruction_dates => NULL,
1644 x_incl_usec_dates => NULL,
1645 x_incl_program_attempt => NULL,
1646 x_incl_attendence_type => NULL,
1647 x_incl_last_term_enrolled => NULL,
1648 x_incl_ssn => NULL,
1649 x_incl_date_of_birth => NULL,
1650 x_incl_disciplin_standing => NULL,
1651 x_incl_no_future_term => NULL,
1652 x_incl_acurat_till_copmp_dt => NULL,
1653 x_incl_cant_rel_without_sign => NULL,
1654 x_return_status => l_return_status,
1655 x_msg_data => l_msg_data,
1656 x_msg_count => l_msg_count,
1657 x_doc_fee_per_copy => l_lifetime_fee,
1658 x_delivery_fee => 0,
1659 x_recip_email => NULL,
1660 x_overridden_doc_delivery_fee => NULL,
1661 x_overridden_document_fee => NULL,
1662 x_fee_overridden_by => NULL,
1663 x_fee_overridden_date => NULL,
1664 x_incl_department => NULL,
1665 x_incl_field_of_stdy => NULL,
1666 x_incl_attend_mode => NULL,
1667 x_incl_yop_acad_prd => NULL,
1668 x_incl_intrmsn_st_end => NULL,
1669 x_incl_hnrs_lvl => NULL,
1670 x_incl_awards => NULL,
1671 x_incl_award_aim => NULL,
1672 x_incl_acad_sessions => NULL,
1673 x_incl_st_end_acad_ses => NULL,
1674 x_incl_hesa_num => NULL,
1675 x_incl_location => NULL,
1676 x_incl_program_type => NULL,
1677 x_incl_program_name => NULL,
1678 x_incl_prog_atmpt_stat => NULL,
1679 x_incl_prog_atmpt_end => NULL,
1680 x_incl_prog_atmpt_strt => NULL,
1681 x_incl_req_cmplete => NULL,
1682 x_incl_expected_compl_dt => NULL,
1683 x_incl_conferral_dt => NULL,
1684 x_incl_thesis_title => NULL,
1685 x_incl_program_code => NULL,
1686 x_incl_program_ver => NULL,
1687 x_incl_stud_no => NULL,
1688 x_incl_surname => NULL,
1689 x_incl_fore_name => NULL,
1690 x_incl_prev_names => NULL,
1691 x_incl_initials => NULL,
1692 x_doc_purpose_code => NULL,
1693 x_plan_id => NULL
1694 );
1695 -- Setting out params
1696 p_return_status := l_return_status;
1697 p_msg_data := l_msg_data;
1698 p_msg_count := l_msg_count;
1699 IF NVL (p_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success THEN
1700 RETURN;
1701 END IF;
1702 -- Inserting record into fee payment, so that transcript items dos fees are calc to Zero
1703 /*IGS_AS_DOC_FEE_PMNT_PKG.INSERT_ROW(
1704 X_ROWID => l_fmnt_row_Id,
1705 X_PERSON_ID => p_person_id,
1706 X_DOCUMENT_TYPE => 'TRANSCRIPT',
1707 X_FEE_PAID_TYPE => 'LIFETIME',
1708 X_FEE_PAID_DATE => NULL,
1709 X_LIFETIME_FEE_PAID => 'N',
1710 X_FEE_AMOUNT => l_lifetime_fee,
1711 X_FEE_RECORDED_DATE => SYSDATE,
1712 X_FEE_RECORDED_BY => p_person_id
1713 );*/
1714 -- Setting out params
1715 p_return_status := l_return_status;
1716 p_msg_data := l_msg_data;
1717 p_msg_count := l_msg_count;
1718 EXCEPTION
1719 WHEN fnd_api.g_exc_error THEN
1720 p_return_status := fnd_api.g_ret_sts_error;
1721 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
1722 WHEN fnd_api.g_exc_unexpected_error THEN
1723 p_return_status := fnd_api.g_ret_sts_unexp_error;
1724 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
1725 WHEN OTHERS THEN
1726 p_return_status := fnd_api.g_ret_sts_unexp_error;
1727 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1728 fnd_message.set_token ('NAME', 'Pay_Lifetime_Fees: ' || SQLERRM);
1729 fnd_msg_pub.ADD;
1730 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
1731 END pay_lifetime_fees;
1732
1733 PROCEDURE recalc_after_lft_paid (
1734 p_person_id IN NUMBER,
1735 p_order_number IN NUMBER,
1736 p_return_status OUT NOCOPY VARCHAR2,
1737 p_msg_data OUT NOCOPY VARCHAR2,
1738 p_msg_count OUT NOCOPY NUMBER
1739 ) AS
1740 -- To select all orders for a person
1741 CURSOR c_order IS
1742 SELECT ord.ROWID row_id,
1743 ord.*
1744 FROM igs_as_order_hdr ord
1745 WHERE person_id = p_person_id
1746 AND order_status = 'INCOMPLETE';
1747 --
1748 c_order_rec c_order%ROWTYPE;
1749 -- To select all items for an order
1750 CURSOR c_order_item (cp_order_number IN NUMBER, cp_item_number IN NUMBER) IS
1751 SELECT dtl.ROWID row_id,
1752 dtl.*
1753 FROM igs_as_doc_details dtl
1754 WHERE order_number = cp_order_number
1755 AND document_type = 'TRANSCRIPT'
1756 AND document_sub_type <> 'LIFE_TIME_TRANS'
1757 AND item_status = 'INCOMPLETE';
1758 -- Get rowid for doc_fee_pmnt table
1759 -- to update the lft flag
1760 CURSOR c_doc_fee_pmnt_record IS
1761 SELECT a.*,
1762 a.ROWID
1763 FROM igs_as_doc_fee_pmnt a
1764 WHERE person_id = p_person_id;
1765 c_doc_fee_pmnt_rec c_doc_fee_pmnt_record%ROWTYPE;
1766 c_order_item_rec c_order_item%ROWTYPE;
1767 -- Variables
1768 l_order_number igs_as_order_hdr.order_number%TYPE;
1769 l_item_number igs_as_doc_details.item_number%TYPE;
1770 l_doc_fee igs_as_order_hdr.order_fee%TYPE;
1771 l_delivery_fee igs_as_order_hdr.delivery_fee%TYPE;
1772 l_itm_row_id VARCHAR2 (25);
1773 l_fmnt_row_id VARCHAR2 (25);
1774 l_lifetime_fee igs_as_doc_fee_stup.amount%TYPE;
1775 l_default_country VARCHAR2 (80) := fnd_profile.VALUE ('OSS_COUNTRY_CODE');
1776 l_return_status VARCHAR2 (30);
1777 l_msg_data VARCHAR2 (1000);
1778 l_msg_count NUMBER;
1779 BEGIN
1780 OPEN c_doc_fee_pmnt_record;
1781 FETCH c_doc_fee_pmnt_record INTO c_doc_fee_pmnt_rec;
1782 -- Update record into fee payment, so that transcript items dos fees are calc to Zero
1783 /*IGS_AS_DOC_FEE_PMNT_PKG.UPDATE_ROW(
1784 X_ROWID => c_doc_fee_pmnt_rec.rowid,
1785 X_PERSON_ID => p_person_id,
1786 X_DOCUMENT_TYPE => c_doc_fee_pmnt_rec.document_type,
1787 X_FEE_PAID_TYPE => c_doc_fee_pmnt_rec.fee_paid_type,
1788 X_FEE_PAID_DATE => SYSDATE,
1789 X_LIFETIME_FEE_PAID => 'Y',
1790 X_FEE_AMOUNT => c_doc_fee_pmnt_rec.fee_amount,
1791 X_FEE_RECORDED_DATE => c_doc_fee_pmnt_rec.fee_recorded_date,
1792 X_FEE_RECORDED_BY => c_doc_fee_pmnt_rec.fee_recorded_by
1793 );*/
1794 NULL;
1795 CLOSE c_doc_fee_pmnt_record;
1796 --
1797 OPEN c_order;
1798 LOOP
1799 FETCH c_order INTO c_order_rec;
1800 EXIT WHEN c_order%NOTFOUND;
1801 l_order_number := c_order_rec.order_number;
1802 OPEN c_order_item (c_order_rec.order_number, l_item_number);
1803 LOOP
1804 FETCH c_order_item INTO c_order_item_rec;
1805 EXIT WHEN c_order_item%NOTFOUND;
1806 -- Update all items for the current order with doc_fee = 0
1807 igs_as_doc_details_pkg.update_row (
1808 x_rowid => c_order_item_rec.row_id,
1809 x_order_number => c_order_item_rec.order_number,
1810 x_document_type => c_order_item_rec.document_type,
1811 x_document_sub_type => c_order_item_rec.document_sub_type,
1812 x_item_number => c_order_item_rec.item_number,
1813 x_item_status => c_order_item_rec.item_status,
1814 x_date_produced => c_order_item_rec.date_produced,
1815 x_incl_curr_course => c_order_item_rec.incl_curr_course,
1816 x_num_of_copies => c_order_item_rec.num_of_copies,
1817 x_comments => c_order_item_rec.comments,
1818 x_recip_pers_name => c_order_item_rec.recip_pers_name,
1819 x_recip_inst_name => c_order_item_rec.recip_inst_name,
1820 x_recip_addr_line_1 => c_order_item_rec.recip_addr_line_1,
1821 x_recip_addr_line_2 => c_order_item_rec.recip_addr_line_2,
1822 x_recip_addr_line_3 => c_order_item_rec.recip_addr_line_3,
1823 x_recip_addr_line_4 => c_order_item_rec.recip_addr_line_4,
1824 x_recip_city => c_order_item_rec.recip_city,
1825 x_recip_postal_code => c_order_item_rec.recip_postal_code,
1826 x_recip_state => c_order_item_rec.recip_state,
1827 x_recip_province => c_order_item_rec.recip_province,
1828 x_recip_county => c_order_item_rec.recip_county,
1829 x_recip_country => c_order_item_rec.recip_country,
1830 x_recip_fax_area_code => c_order_item_rec.recip_fax_area_code,
1831 x_recip_fax_country_code => c_order_item_rec.recip_fax_country_code,
1832 x_recip_fax_number => c_order_item_rec.recip_fax_number,
1833 x_delivery_method_type => c_order_item_rec.delivery_method_type,
1834 x_programs_on_file => c_order_item_rec.programs_on_file,
1835 x_missing_acad_record_data_ind => c_order_item_rec.missing_acad_record_data_ind,
1836 x_missing_academic_record_data => c_order_item_rec.missing_academic_record_data,
1837 x_send_transcript_immediately => c_order_item_rec.send_transcript_immediately,
1838 x_hold_release_of_final_grades => c_order_item_rec.hold_release_of_final_grades,
1839 x_fgrade_cal_type => c_order_item_rec.fgrade_cal_type,
1840 x_fgrade_seq_num => c_order_item_rec.fgrade_seq_num,
1841 x_hold_degree_expected => c_order_item_rec.hold_degree_expected,
1842 x_deghold_cal_type => c_order_item_rec.deghold_cal_type,
1843 x_deghold_seq_num => c_order_item_rec.deghold_seq_num,
1844 x_hold_for_grade_chg => c_order_item_rec.hold_for_grade_chg,
1845 x_special_instr => c_order_item_rec.special_instr,
1846 x_express_mail_type => c_order_item_rec.express_mail_type,
1847 x_express_mail_track_num => c_order_item_rec.express_mail_track_num,
1848 x_ge_certification => c_order_item_rec.ge_certification,
1849 x_external_comments => c_order_item_rec.external_comments,
1850 x_internal_comments => c_order_item_rec.internal_comments,
1851 x_dup_requested => c_order_item_rec.dup_requested,
1852 x_dup_req_date => c_order_item_rec.dup_req_date,
1853 x_dup_sent_date => c_order_item_rec.dup_sent_date,
1854 x_enr_term_cal_type => c_order_item_rec.enr_term_cal_type,
1855 x_enr_ci_sequence_number => c_order_item_rec.enr_ci_sequence_number,
1856 x_incl_attempted_hours => c_order_item_rec.incl_attempted_hours,
1857 x_incl_class_rank => c_order_item_rec.incl_class_rank,
1858 x_incl_progresssion_status => c_order_item_rec.incl_progresssion_status,
1859 x_incl_class_standing => c_order_item_rec.incl_class_standing,
1860 x_incl_cum_hours_earned => c_order_item_rec.incl_cum_hours_earned,
1861 x_incl_gpa => c_order_item_rec.incl_gpa,
1862 x_incl_date_of_graduation => c_order_item_rec.incl_date_of_graduation,
1863 x_incl_degree_dates => c_order_item_rec.incl_degree_dates,
1864 x_incl_degree_earned => c_order_item_rec.incl_degree_earned,
1865 x_incl_date_of_entry => c_order_item_rec.incl_date_of_entry,
1866 x_incl_drop_withdrawal_dates => c_order_item_rec.incl_drop_withdrawal_dates,
1867 x_incl_hrs_for_curr_term => c_order_item_rec.incl_hrs_earned_for_curr_term,
1868 x_incl_majors => c_order_item_rec.incl_majors,
1869 x_incl_last_date_of_enrollment => c_order_item_rec.incl_last_date_of_enrollment,
1870 x_incl_professional_licensure => c_order_item_rec.incl_professional_licensure,
1871 x_incl_college_affiliation => c_order_item_rec.incl_college_affiliation,
1872 x_incl_instruction_dates => c_order_item_rec.incl_instruction_dates,
1873 x_incl_usec_dates => c_order_item_rec.incl_usec_dates,
1874 x_incl_program_attempt => c_order_item_rec.incl_program_attempt,
1875 x_incl_attendence_type => c_order_item_rec.incl_attendence_type,
1876 x_incl_last_term_enrolled => c_order_item_rec.incl_last_term_enrolled,
1877 x_incl_ssn => c_order_item_rec.incl_ssn,
1878 x_incl_date_of_birth => c_order_item_rec.incl_date_of_birth,
1879 x_incl_disciplin_standing => c_order_item_rec.incl_disciplin_standing,
1880 x_incl_no_future_term => c_order_item_rec.incl_no_future_term,
1881 x_incl_acurat_till_copmp_dt => c_order_item_rec.incl_acurat_till_copmp_dt,
1882 x_incl_cant_rel_without_sign => c_order_item_rec.incl_cant_rel_without_sign,
1883 x_mode => 'R',
1884 x_return_status => l_return_status,
1885 x_msg_data => l_msg_data,
1886 x_msg_count => l_msg_count,
1887 x_doc_fee_per_copy => 0,
1888 x_delivery_fee => c_order_item_rec.delivery_fee,
1889 x_recip_email => c_order_item_rec.recip_email,
1890 x_overridden_doc_delivery_fee => c_order_item_rec.overridden_doc_delivery_fee,
1891 x_overridden_document_fee => c_order_item_rec.overridden_document_fee,
1892 x_fee_overridden_by => c_order_item_rec.fee_overridden_by,
1893 x_fee_overridden_date => c_order_item_rec.fee_overridden_date,
1894 x_incl_department => c_order_item_rec.incl_department,
1895 x_incl_field_of_stdy => c_order_item_rec.incl_field_of_stdy,
1896 x_incl_attend_mode => c_order_item_rec.incl_attend_mode,
1897 x_incl_yop_acad_prd => c_order_item_rec.incl_yop_acad_prd,
1898 x_incl_intrmsn_st_end => c_order_item_rec.incl_intrmsn_st_end,
1899 x_incl_hnrs_lvl => c_order_item_rec.incl_hnrs_lvl,
1900 x_incl_awards => c_order_item_rec.incl_awards,
1901 x_incl_award_aim => c_order_item_rec.incl_award_aim,
1902 x_incl_acad_sessions => c_order_item_rec.incl_acad_sessions,
1903 x_incl_st_end_acad_ses => c_order_item_rec.incl_st_end_acad_ses,
1904 x_incl_hesa_num => c_order_item_rec.incl_hesa_num,
1905 x_incl_location => c_order_item_rec.incl_location,
1906 x_incl_program_type => c_order_item_rec.incl_program_type,
1907 x_incl_program_name => c_order_item_rec.incl_program_name,
1908 x_incl_prog_atmpt_stat => c_order_item_rec.incl_prog_atmpt_stat,
1909 x_incl_prog_atmpt_end => c_order_item_rec.incl_prog_atmpt_end,
1910 x_incl_prog_atmpt_strt => c_order_item_rec.incl_prog_atmpt_strt,
1911 x_incl_req_cmplete => c_order_item_rec.incl_req_cmplete,
1912 x_incl_expected_compl_dt => c_order_item_rec.incl_expected_compl_dt,
1913 x_incl_conferral_dt => c_order_item_rec.incl_conferral_dt,
1914 x_incl_thesis_title => c_order_item_rec.incl_thesis_title,
1915 x_incl_program_code => c_order_item_rec.incl_program_code,
1916 x_incl_program_ver => c_order_item_rec.incl_program_ver,
1917 x_incl_stud_no => c_order_item_rec.incl_stud_no,
1918 x_incl_surname => c_order_item_rec.incl_surname,
1919 x_incl_fore_name => c_order_item_rec.incl_fore_name,
1920 x_incl_prev_names => c_order_item_rec.incl_prev_names,
1921 x_incl_initials => c_order_item_rec.incl_initials,
1922 x_doc_purpose_code => c_order_item_rec.doc_purpose_code,
1923 x_plan_id => c_order_item_rec.plan_id,
1924 x_produced_by => c_order_item_rec.produced_by,
1925 x_person_id => c_order_item_rec.person_id
1926 );
1927 -- Setting out params
1928 p_return_status := l_return_status;
1929 p_msg_data := l_msg_data;
1930 p_msg_count := l_msg_count;
1931 IF NVL (p_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success THEN
1932 RETURN;
1933 END IF;
1934 END LOOP;
1935 CLOSE c_order_item;
1936 SELECT NVL (SUM (doc_fee_per_copy), 0),
1937 NVL (SUM (delivery_fee), 0)
1938 INTO l_doc_fee,
1939 l_delivery_fee
1940 FROM igs_as_doc_details
1941 WHERE order_number = l_order_number;
1942 -- Update the order for the above items
1943 igs_as_order_hdr_pkg.update_row (
1944 x_msg_count => l_msg_count,
1945 x_msg_data => l_msg_data,
1946 x_return_status => l_return_status,
1947 x_rowid => c_order_rec.row_id,
1948 x_order_number => c_order_rec.order_number,
1949 x_order_status => c_order_rec.order_status,
1950 x_date_completed => c_order_rec.date_completed,
1951 x_person_id => c_order_rec.person_id,
1952 x_addr_line_1 => c_order_rec.addr_line_1,
1953 x_addr_line_2 => c_order_rec.addr_line_2,
1954 x_addr_line_3 => c_order_rec.addr_line_3,
1955 x_addr_line_4 => c_order_rec.addr_line_4,
1956 x_city => c_order_rec.city,
1957 x_state => c_order_rec.state,
1958 x_province => c_order_rec.province,
1959 x_county => c_order_rec.county,
1960 x_country => c_order_rec.country,
1961 x_postal_code => c_order_rec.postal_code,
1962 x_email_address => c_order_rec.email_address,
1963 x_phone_country_code => c_order_rec.phone_country_code,
1964 x_phone_area_code => c_order_rec.phone_area_code,
1965 x_phone_number => c_order_rec.phone_number,
1966 x_phone_extension => c_order_rec.phone_extension,
1967 x_fax_country_code => c_order_rec.fax_country_code,
1968 x_fax_area_code => c_order_rec.fax_area_code,
1969 x_fax_number => c_order_rec.fax_number,
1970 x_delivery_fee => l_delivery_fee,
1971 x_order_fee => l_doc_fee,
1972 x_request_type => c_order_rec.request_type,
1973 x_submit_method => c_order_rec.submit_method,
1974 x_invoice_id => c_order_rec.invoice_id,
1975 x_mode => 'R',
1976 x_order_placed_by => c_order_rec.order_placed_by,
1977 x_order_description => c_order_rec.order_description
1978 );
1979 IF NVL (p_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success THEN
1980 RETURN;
1981 END IF;
1982 -- Setting out params
1983 p_return_status := l_return_status;
1984 p_msg_data := l_msg_data;
1985 p_msg_count := l_msg_count;
1986 END LOOP;
1987 CLOSE c_order;
1988 --Initialize API return status to success.
1989 p_return_status := fnd_api.g_ret_sts_success;
1990 --Standard call to get message count and if count is 1, get message info.
1991 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
1992 EXCEPTION
1993 WHEN fnd_api.g_exc_error THEN
1994 p_return_status := fnd_api.g_ret_sts_error;
1995 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
1996 WHEN fnd_api.g_exc_unexpected_error THEN
1997 p_return_status := fnd_api.g_ret_sts_unexp_error;
1998 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
1999 WHEN OTHERS THEN
2000 p_return_status := fnd_api.g_ret_sts_unexp_error;
2001 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2002 fnd_message.set_token ('NAME', 'update_document_details: ' || SQLERRM);
2003 fnd_msg_pub.ADD;
2004 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
2005 END recalc_after_lft_paid;
2006
2007 PROCEDURE get_as_current_term (
2008 p_cal_type OUT NOCOPY VARCHAR2,
2009 p_sequence_number OUT NOCOPY NUMBER,
2010 p_description OUT NOCOPY VARCHAR2
2011 ) AS
2012 BEGIN
2013 -- Procedure to select the current term calendar based on the current date
2014 EXECUTE IMMEDIATE 'SELECT cal_type, sequence_number, description
2015 FROM
2016 (SELECT
2017 ci.cal_type,
2018 ci.sequence_number,
2019 ci.description,
2020 NVL(
2021 ( SELECT MIN(daiv.alias_val)
2022 FROM igs_ca_da_inst_v daiv,
2023 igs_en_cal_conf secc
2024 WHERE secc.s_control_num = 1 AND
2025 daiv.cal_type = ci.cal_type AND
2026 daiv.ci_sequence_number= ci.sequence_number AND
2027 daiv.dt_alias = secc.load_effect_dt_alias),
2028 ci.start_dt) load_effect_dt
2029 FROM igs_ca_inst_all ci,
2030 igs_ca_type cat,
2031 igs_ca_stat cs
2032 WHERE
2033 ci.end_dt >= TRUNC(SYSDATE) AND
2034 cat.cal_type = ci.cal_type AND
2035 cat.s_cal_cat = ''LOAD'' AND
2036 cs.cal_status = ci.cal_status AND
2037 cs.s_cal_status = ''ACTIVE'' AND
2038 ci.ss_displayed = ''Y'' AND
2039 cat.closed_ind = ''N''
2040 ORDER BY load_effect_dt DESC ) dates
2041 WHERE load_effect_dt <= SYSDATE
2042 AND ROWNUM = 1 '
2043 INTO p_cal_type,
2044 p_sequence_number,
2045 p_description;
2046 EXCEPTION
2047 WHEN NO_DATA_FOUND THEN
2048 p_cal_type := NULL;
2049 p_sequence_number := NULL;
2050 p_description := NULL;
2051 END get_as_current_term;
2052
2053 PROCEDURE get_as_next_term (
2054 p_cal_type OUT NOCOPY VARCHAR2,
2055 p_sequence_number OUT NOCOPY NUMBER,
2056 p_description OUT NOCOPY VARCHAR2
2057 ) AS
2058 BEGIN
2059 -- Procedure to select the next term calendar based on the current date
2060 EXECUTE IMMEDIATE 'select cal_type, sequence_number,description
2061 from
2062 (
2063 SELECT
2064 ci.cal_type,
2065 ci.sequence_number,
2066 ci.description,
2067 ci.end_dt ,
2068 NVL(
2069 (
2070 SELECT MIN(daiv.alias_val)
2071 FROM igs_ca_da_inst_v daiv,
2072 igs_en_cal_conf secc
2073 WHERE secc.s_control_num = 1 AND
2074 daiv.cal_type = ci.cal_type AND
2075 daiv.ci_sequence_number= ci.sequence_number AND
2076 daiv.dt_alias = secc.load_effect_dt_alias
2077 ),
2078 ci.start_dt
2079 ) load_effect_dt
2080 FROM igs_ca_inst_all ci,
2081 igs_ca_type cat,
2082 igs_ca_stat cs
2083 WHERE
2084 ci.end_dt >= TRUNC(SYSDATE) AND
2085 cat.cal_type = ci.cal_type AND
2086 cat.s_cal_cat = ''LOAD'' AND
2087 cs.cal_status = ci.cal_status AND
2088 cs.s_cal_status = ''ACTIVE''
2089 ORDER BY load_effect_dt ASC
2090 ) dates
2091 where load_effect_dt > SYSDATE and rownum=1 '
2092 INTO p_cal_type,
2093 p_sequence_number,
2094 p_description;
2095 END get_as_next_term;
2096
2097 PROCEDURE get_as_previous_term (
2098 p_cal_type OUT NOCOPY VARCHAR2,
2099 p_sequence_number OUT NOCOPY NUMBER,
2100 p_description OUT NOCOPY VARCHAR2
2101 ) AS
2102 BEGIN
2103 -- Procedure to select the next term calendar based on the current date
2104 EXECUTE IMMEDIATE 'select cal_type, sequence_number,description
2105 from
2106 (
2107 SELECT
2108 ci.cal_type,
2109 ci.sequence_number,
2110 ci.description,
2111 ci.end_dt ,
2112 NVL(
2113 (
2114 SELECT MIN(daiv.alias_val)
2115 FROM igs_ca_da_inst_v daiv,
2116 igs_en_cal_conf secc
2117 WHERE secc.s_control_num = 1 AND
2118 daiv.cal_type = ci.cal_type AND
2119 daiv.ci_sequence_number= ci.sequence_number AND
2120 daiv.dt_alias = secc.load_effect_dt_alias
2121 ),
2122 ci.start_dt
2123 ) load_effect_dt
2124 FROM igs_ca_inst_all ci,
2125 igs_ca_type cat,
2126 igs_ca_stat cs
2127 WHERE
2128 ci.end_dt < TRUNC(SYSDATE) AND
2129 cat.cal_type = ci.cal_type AND
2130 cat.s_cal_cat = ''LOAD'' AND
2131 cs.cal_status = ci.cal_status AND
2132 cs.s_cal_status = ''ACTIVE''
2133 ORDER BY ci.end_dt DESC
2134 ) dates
2135 where load_effect_dt <= SYSDATE and rownum=1'
2136 INTO p_cal_type,
2137 p_sequence_number,
2138 p_description;
2139 END get_as_previous_term;
2140
2141 PROCEDURE re_calc_doc_fees (
2142 p_person_id IN NUMBER,
2143 p_plan_id IN NUMBER,
2144 p_subs_unsubs IN VARCHAR2, -- Possible values 'U' and 'S'
2145 p_admin_person_id IN NUMBER, -- The person Id of the admin
2146 p_orders_recalc OUT NOCOPY VARCHAR2 -- To return comma seperated List of Order numbers that got recalculated.
2147 ) IS
2148 -- Cursor to get all the items with plan ID as passed in parameter.
2149 CURSOR cur_itm_dtls_unsc IS
2150 SELECT l.item_number,
2151 l.order_number,
2152 l.document_type,
2153 l.num_of_copies,
2154 l.delivery_method_type,
2155 l.programs_on_file
2156 FROM igs_as_doc_details l,
2157 igs_as_order_hdr h
2158 WHERE h.person_id = p_person_id
2159 AND l.item_status = 'INCOMPLETE'
2160 AND l.order_number = h.order_number
2161 AND l.plan_id = p_plan_id;
2162 -- Cursor to get all the items which are incomplete and have plan_id = NULL;
2163 CURSOR cur_itm_dtls_subs IS
2164 SELECT l.item_number,
2165 l.order_number,
2166 l.document_type,
2167 l.num_of_copies,
2168 l.delivery_method_type,
2169 l.programs_on_file
2170 FROM igs_as_doc_details l,
2171 igs_as_order_hdr h
2172 WHERE h.person_id = p_person_id
2173 AND l.item_status = 'INCOMPLETE'
2174 AND l.order_number = h.order_number
2175 AND l.plan_id IS NULL;
2176 -- Cursor to get the order details so that it can be updated..
2177 CURSOR cur_order (cp_order_number NUMBER) IS
2178 SELECT o.ROWID row_id,
2179 o.*
2180 FROM igs_as_order_hdr o
2181 WHERE order_number = cp_order_number;
2182 order_rec cur_order%ROWTYPE;
2183 -- Cursor for updating the item
2184 CURSOR cur_itm_dtls_upd (cp_item_number NUMBER) IS
2185 SELECT l.ROWID AS row_id,
2186 l.*
2187 FROM igs_as_doc_details l
2188 WHERE l.item_number = cp_item_number;
2189 rec_dtls cur_itm_dtls_upd%ROWTYPE;
2190 -- Cursor to get the plan Details:
2191 CURSOR cur_plan IS
2192 SELECT plan_id,
2193 plan_type,
2194 unlimited_ind,
2195 quantity_limit
2196 FROM igs_as_servic_plan
2197 WHERE plan_id = p_plan_id;
2198 plan_rec cur_plan%ROWTYPE;
2199 -- Cursor to update the doc_fee_pmnt
2200 CURSOR cur_doc_fee_pmnt IS
2201 SELECT f.ROWID row_id,
2202 f.*
2203 FROM igs_as_doc_fee_pmnt f
2204 WHERE person_id = p_person_id
2205 AND plan_id = p_plan_id;
2206 rec_doc_fee_pmnt cur_doc_fee_pmnt%ROWTYPE;
2207 prev_order_number NUMBER;
2208 next_order_number NUMBER;
2209 lndoc_fee NUMBER;
2210 ln_order_fee NUMBER := 0;
2211 ln_msg_count NUMBER;
2212 lv_msg_data VARCHAR2 (100);
2213 lv_return_status VARCHAR2 (30);
2214 lnplan_limit NUMBER;
2215 lnitmused NUMBER := 0;
2216 ln_plan_id NUMBER;
2217 ldeliv_fee NUMBER;
2218 -- Create a local Procedure to update order:
2219 PROCEDURE update_order_for_recalc (p_order_number NUMBER, p_order_fee NUMBER) IS
2220 BEGIN
2221 OPEN cur_order (p_order_number);
2222 FETCH cur_order INTO order_rec;
2223 CLOSE cur_order;
2224 -- Make a Call to the update row fro igs_as_order_hdr
2225 igs_as_order_hdr_pkg.update_row (
2226 x_msg_count => ln_msg_count,
2227 x_msg_data => lv_msg_data,
2228 x_return_status => lv_return_status,
2229 x_rowid => order_rec.row_id,
2230 x_order_number => order_rec.order_number,
2231 x_order_status => order_rec.order_status,
2232 x_date_completed => order_rec.date_completed,
2233 x_person_id => order_rec.person_id,
2234 x_addr_line_1 => order_rec.addr_line_1,
2235 x_addr_line_2 => order_rec.addr_line_2,
2236 x_addr_line_3 => order_rec.addr_line_3,
2237 x_addr_line_4 => order_rec.addr_line_4,
2238 x_city => order_rec.city,
2239 x_state => order_rec.state,
2240 x_province => order_rec.province,
2241 x_county => order_rec.county,
2242 x_country => order_rec.country,
2243 x_postal_code => order_rec.postal_code,
2244 x_email_address => order_rec.email_address,
2245 x_phone_country_code => order_rec.phone_country_code,
2246 x_phone_area_code => order_rec.phone_area_code,
2247 x_phone_number => order_rec.phone_number,
2248 x_phone_extension => order_rec.phone_extension,
2249 x_fax_country_code => order_rec.fax_country_code,
2250 x_fax_area_code => order_rec.fax_area_code,
2251 x_fax_number => order_rec.fax_number,
2252 x_delivery_fee => order_rec.delivery_fee,
2253 x_order_fee => p_order_fee,
2254 x_request_type => order_rec.request_type,
2255 x_submit_method => order_rec.submit_method,
2256 x_invoice_id => order_rec.invoice_id,
2257 x_mode => 'R',
2258 x_order_description => order_rec.order_description,
2259 x_order_placed_by => order_rec.order_placed_by
2260 );
2261 END update_order_for_recalc;
2262 -- Create a local Procedure to update order:
2263 PROCEDURE update_itm_for_recalc (p_item_number NUMBER, p_order_fee NUMBER, p_plan_id NUMBER) IS
2264 BEGIN
2265 OPEN cur_itm_dtls_upd (p_item_number);
2266 FETCH cur_itm_dtls_upd INTO rec_dtls;
2267 CLOSE cur_itm_dtls_upd;
2268 -- Make a Call to the update row fro igs_as_doc_details
2269 igs_as_doc_details_pkg.update_row (
2270 x_rowid => rec_dtls.row_id,
2271 x_order_number => rec_dtls.order_number,
2272 x_document_type => rec_dtls.document_type,
2273 x_document_sub_type => rec_dtls.document_sub_type,
2274 x_item_number => rec_dtls.item_number,
2275 x_item_status => rec_dtls.item_status,
2276 x_date_produced => rec_dtls.date_produced,
2277 x_incl_curr_course => rec_dtls.incl_curr_course,
2278 x_num_of_copies => rec_dtls.num_of_copies,
2279 x_comments => rec_dtls.comments,
2280 x_recip_pers_name => rec_dtls.recip_pers_name,
2281 x_recip_inst_name => rec_dtls.recip_inst_name,
2282 x_recip_addr_line_1 => rec_dtls.recip_addr_line_1,
2283 x_recip_addr_line_2 => rec_dtls.recip_addr_line_2,
2284 x_recip_addr_line_3 => rec_dtls.recip_addr_line_3,
2285 x_recip_addr_line_4 => rec_dtls.recip_addr_line_4,
2286 x_recip_city => rec_dtls.recip_city,
2287 x_recip_postal_code => rec_dtls.recip_postal_code,
2288 x_recip_state => rec_dtls.recip_state,
2289 x_recip_province => rec_dtls.recip_province,
2290 x_recip_county => rec_dtls.recip_county,
2291 x_recip_country => rec_dtls.recip_country,
2292 x_recip_fax_area_code => rec_dtls.recip_fax_area_code,
2293 x_recip_fax_country_code => rec_dtls.recip_fax_country_code,
2294 x_recip_fax_number => rec_dtls.recip_fax_number,
2295 x_delivery_method_type => rec_dtls.delivery_method_type,
2296 x_programs_on_file => rec_dtls.programs_on_file,
2297 x_missing_acad_record_data_ind => rec_dtls.missing_acad_record_data_ind,
2298 x_missing_academic_record_data => rec_dtls.missing_academic_record_data,
2299 x_send_transcript_immediately => rec_dtls.send_transcript_immediately,
2300 x_hold_release_of_final_grades => rec_dtls.hold_release_of_final_grades,
2301 x_fgrade_cal_type => rec_dtls.fgrade_cal_type,
2302 x_fgrade_seq_num => rec_dtls.fgrade_seq_num,
2303 x_hold_degree_expected => rec_dtls.hold_degree_expected,
2304 x_deghold_cal_type => rec_dtls.deghold_cal_type,
2305 x_deghold_seq_num => rec_dtls.deghold_seq_num,
2306 x_hold_for_grade_chg => rec_dtls.hold_for_grade_chg,
2307 x_special_instr => rec_dtls.special_instr,
2308 x_express_mail_type => rec_dtls.express_mail_type,
2309 x_express_mail_track_num => rec_dtls.express_mail_track_num,
2310 x_ge_certification => rec_dtls.ge_certification,
2311 x_external_comments => rec_dtls.external_comments,
2312 x_internal_comments => rec_dtls.internal_comments,
2313 x_dup_requested => rec_dtls.dup_requested,
2314 x_dup_req_date => rec_dtls.dup_req_date,
2315 x_dup_sent_date => rec_dtls.dup_sent_date,
2316 x_enr_term_cal_type => rec_dtls.enr_term_cal_type,
2317 x_enr_ci_sequence_number => rec_dtls.enr_ci_sequence_number,
2318 x_incl_attempted_hours => rec_dtls.incl_attempted_hours,
2319 x_incl_class_rank => rec_dtls.incl_class_rank,
2320 x_incl_progresssion_status => rec_dtls.incl_progresssion_status,
2321 x_incl_class_standing => rec_dtls.incl_class_standing,
2322 x_incl_cum_hours_earned => rec_dtls.incl_cum_hours_earned,
2323 x_incl_gpa => rec_dtls.incl_gpa,
2324 x_incl_date_of_graduation => rec_dtls.incl_date_of_graduation,
2325 x_incl_degree_dates => rec_dtls.incl_degree_dates,
2326 x_incl_degree_earned => rec_dtls.incl_degree_earned,
2327 x_incl_date_of_entry => rec_dtls.incl_date_of_entry,
2328 x_incl_drop_withdrawal_dates => rec_dtls.incl_drop_withdrawal_dates,
2329 x_incl_hrs_for_curr_term => rec_dtls.incl_hrs_earned_for_curr_term,
2330 x_incl_majors => rec_dtls.incl_majors,
2331 x_incl_last_date_of_enrollment => rec_dtls.incl_last_date_of_enrollment,
2332 x_incl_professional_licensure => rec_dtls.incl_professional_licensure,
2333 x_incl_college_affiliation => rec_dtls.incl_college_affiliation,
2334 x_incl_instruction_dates => rec_dtls.incl_instruction_dates,
2335 x_incl_usec_dates => rec_dtls.incl_usec_dates,
2336 x_incl_program_attempt => rec_dtls.incl_program_attempt,
2337 x_incl_attendence_type => rec_dtls.incl_attendence_type,
2338 x_incl_last_term_enrolled => rec_dtls.incl_last_term_enrolled,
2339 x_incl_ssn => rec_dtls.incl_ssn,
2340 x_incl_date_of_birth => rec_dtls.incl_date_of_birth,
2341 x_incl_disciplin_standing => rec_dtls.incl_disciplin_standing,
2342 x_incl_no_future_term => rec_dtls.incl_no_future_term,
2343 x_incl_acurat_till_copmp_dt => rec_dtls.incl_acurat_till_copmp_dt,
2344 x_incl_cant_rel_without_sign => rec_dtls.incl_cant_rel_without_sign,
2345 x_mode => 'R',
2346 x_return_status => lv_return_status,
2347 x_msg_data => lv_msg_data,
2348 x_msg_count => ln_msg_count,
2349 x_doc_fee_per_copy => p_order_fee,
2350 x_delivery_fee => rec_dtls.delivery_fee,
2351 x_recip_email => rec_dtls.recip_email,
2352 x_overridden_doc_delivery_fee => rec_dtls.overridden_doc_delivery_fee,
2353 x_overridden_document_fee => rec_dtls.overridden_document_fee,
2354 x_fee_overridden_by => rec_dtls.fee_overridden_by,
2355 x_fee_overridden_date => rec_dtls.fee_overridden_date,
2356 x_incl_department => rec_dtls.incl_department,
2357 x_incl_field_of_stdy => rec_dtls.incl_field_of_stdy,
2358 x_incl_attend_mode => rec_dtls.incl_attend_mode,
2359 x_incl_yop_acad_prd => rec_dtls.incl_yop_acad_prd,
2360 x_incl_intrmsn_st_end => rec_dtls.incl_intrmsn_st_end,
2361 x_incl_hnrs_lvl => rec_dtls.incl_hnrs_lvl,
2362 x_incl_awards => rec_dtls.incl_awards,
2363 x_incl_award_aim => rec_dtls.incl_award_aim,
2364 x_incl_acad_sessions => rec_dtls.incl_acad_sessions,
2365 x_incl_st_end_acad_ses => rec_dtls.incl_st_end_acad_ses,
2366 x_incl_hesa_num => rec_dtls.incl_hesa_num,
2367 x_incl_location => rec_dtls.incl_location,
2368 x_incl_program_type => rec_dtls.incl_program_type,
2369 x_incl_program_name => rec_dtls.incl_program_name,
2370 x_incl_prog_atmpt_stat => rec_dtls.incl_prog_atmpt_stat,
2371 x_incl_prog_atmpt_end => rec_dtls.incl_prog_atmpt_end,
2372 x_incl_prog_atmpt_strt => rec_dtls.incl_prog_atmpt_strt,
2373 x_incl_req_cmplete => rec_dtls.incl_req_cmplete,
2374 x_incl_expected_compl_dt => rec_dtls.incl_expected_compl_dt,
2375 x_incl_conferral_dt => rec_dtls.incl_conferral_dt,
2376 x_incl_thesis_title => rec_dtls.incl_thesis_title,
2377 x_incl_program_code => rec_dtls.incl_program_code,
2378 x_incl_program_ver => rec_dtls.incl_program_ver,
2379 x_incl_stud_no => rec_dtls.incl_stud_no,
2380 x_incl_surname => rec_dtls.incl_surname,
2381 x_incl_fore_name => rec_dtls.incl_fore_name,
2382 x_incl_prev_names => rec_dtls.incl_prev_names,
2383 x_incl_initials => rec_dtls.incl_initials,
2384 x_doc_purpose_code => rec_dtls.doc_purpose_code,
2385 x_plan_id => p_plan_id,
2386 x_produced_by => rec_dtls.produced_by,
2387 x_person_id => rec_dtls.person_id
2388 );
2389 END update_itm_for_recalc;
2390 BEGIN -- Begin Of Main Procedure
2391 ln_plan_id := p_plan_id;
2392 IF p_subs_unsubs = 'U' THEN
2393 -- Update the IGS_AS_DOC_FEE_PMNT table with end date as SYSDATE.
2394 OPEN cur_doc_fee_pmnt;
2395 FETCH cur_doc_fee_pmnt INTO rec_doc_fee_pmnt;
2396 CLOSE cur_doc_fee_pmnt;
2397 BEGIN
2398 igs_as_doc_fee_pmnt_pkg.update_row (
2399 x_rowid => rec_doc_fee_pmnt.row_id,
2400 x_person_id => rec_doc_fee_pmnt.person_id,
2401 x_fee_paid_date => rec_doc_fee_pmnt.fee_paid_date,
2402 x_fee_amount => rec_doc_fee_pmnt.fee_amount,
2403 x_fee_recorded_date => rec_doc_fee_pmnt.fee_recorded_date,
2404 x_fee_recorded_by => rec_doc_fee_pmnt.fee_recorded_by,
2405 x_mode => 'R',
2406 x_plan_id => rec_doc_fee_pmnt.plan_id,
2407 x_invoice_id => rec_doc_fee_pmnt.invoice_id,
2408 x_plan_discon_from => SYSDATE,
2409 x_plan_discon_by => p_admin_person_id,
2410 x_num_of_copies => rec_doc_fee_pmnt.num_of_copies,
2411 x_prev_paid_plan => rec_doc_fee_pmnt.prev_paid_plan,
2412 x_cal_type => rec_doc_fee_pmnt.cal_type,
2413 x_ci_sequence_number => rec_doc_fee_pmnt.ci_sequence_number,
2414 x_program_on_file => rec_doc_fee_pmnt.program_on_file,
2415 x_return_status => lv_return_status,
2416 x_msg_data => lv_msg_data,
2417 x_msg_count => ln_msg_count
2418 );
2419 END;
2420 -- Update all the items with the proper fee.
2421 FOR uns_itm IN cur_itm_dtls_unsc LOOP
2422 prev_order_number := uns_itm.order_number;
2423 p_orders_recalc := p_orders_recalc || uns_itm.order_number || ',';
2424 --Get the Fee that this document will require if plan were not subscribed by the user.
2425 lndoc_fee := igs_as_ss_doc_request.get_transcript_fee (
2426 p_person_id => p_person_id,
2427 p_document_type => uns_itm.document_type,
2428 p_number_of_copies => uns_itm.num_of_copies,
2429 p_include_delivery_fee => 'N',
2430 p_delivery_method_type => uns_itm.delivery_method_type,
2431 p_item_number => uns_itm.item_number
2432 );
2433 -- Upadete the Item Details table with Newly calculated Fee and Plan ID with NULL:
2434 update_itm_for_recalc (uns_itm.item_number, uns_itm.order_number, NULL);
2435 -- After Looping through all the Items of the order update the order
2436 IF NVL (next_order_number, prev_order_number) <> prev_order_number THEN
2437 update_order_for_recalc (prev_order_number, ln_order_fee);
2438 ln_order_fee := lndoc_fee;
2439 END IF;
2440 next_order_number := uns_itm.order_number;
2441 ln_order_fee := ln_order_fee + lndoc_fee;
2442 END LOOP; -- Upd Doc Details.
2443 --- UPdate the order for the last Order or if the Order had only one item.
2444 -- Remove the trailing Comma
2445 p_orders_recalc := SUBSTR (p_orders_recalc, 1, LENGTH (p_orders_recalc) - 1);
2446 update_order_for_recalc (prev_order_number, ln_order_fee);
2447 ELSIF p_subs_unsubs = 'S' THEN
2448 OPEN cur_plan;
2449 FETCH cur_plan INTO plan_rec;
2450 CLOSE cur_plan;
2451 -- Hope not more than 100 items are in status 'INCOMPLETE'
2452 lnplan_limit := NVL (plan_rec.quantity_limit, 100);
2453 FOR subs_itm IN cur_itm_dtls_subs LOOP
2454 lnitmused := lnitmused + subs_itm.num_of_copies;
2455 EXIT WHEN lnitmused > lnplan_limit;
2456 prev_order_number := subs_itm.order_number;
2457 p_orders_recalc := p_orders_recalc || subs_itm.order_number || ',';
2458 --Get the Fee that this document will require if this plan is subscribed by the user.
2459 get_doc_and_delivery_fee (
2460 p_person_id => p_person_id,
2461 p_document_type => subs_itm.document_type,
2462 p_document_sub_type => NULL,
2463 p_number_of_copies => subs_itm.num_of_copies,
2464 p_delivery_method_type => subs_itm.delivery_method_type,
2465 p_program_on_file => subs_itm.programs_on_file,
2466 p_plan_id => ln_plan_id,
2467 p_document_fee => lndoc_fee,
2468 p_delivery_fee => ldeliv_fee,
2469 p_item_number => subs_itm.item_number
2470 );
2471 -- Upadete the Item Details table with Newly calculated Fee and Plan ID with NULL:
2472 update_itm_for_recalc (subs_itm.item_number, subs_itm.order_number, ln_plan_id);
2473 -- After Looping through all the Items of the order update the order
2474 IF NVL (next_order_number, prev_order_number) <> prev_order_number THEN
2475 update_order_for_recalc (prev_order_number, ln_order_fee);
2476 ln_order_fee := lndoc_fee;
2477 END IF;
2478 next_order_number := subs_itm.order_number;
2479 ln_order_fee := ln_order_fee + lndoc_fee;
2480 END LOOP; -- Upd Doc Details for Subscribe.
2481 -- Update the order for the last Order or if the Order had only one item.
2482 -- Remove the trailing Comma
2483 p_orders_recalc := SUBSTR (p_orders_recalc, 1, LENGTH (p_orders_recalc) - 1);
2484 update_order_for_recalc (prev_order_number, ln_order_fee);
2485 END IF;
2486 END re_calc_doc_fees; -- End Procedure
2487
2488 PROCEDURE create_trns_plan_invoice_id (
2489 p_person_id IN NUMBER,
2490 p_fee_amount IN NUMBER,
2491 p_invoice_id OUT NOCOPY NUMBER,
2492 p_return_status OUT NOCOPY VARCHAR2,
2493 p_msg_count OUT NOCOPY NUMBER,
2494 p_msg_data OUT NOCOPY VARCHAR2,
2495 p_waiver_amount OUT NOCOPY NUMBER
2496 ) AS
2497 /*----------------------------------------------------------------------------
2498 || Created By :
2499 || Created On :
2500 || Purpose :
2501 || Known limitations, enhancements or remarks :
2502 || Change History :
2503 || Who When What
2504 || (reverse chronological order - newest change first)
2505 || vvutukur 27-Nov-2002 Enh#2584986.GL Interface Build. Removed the references to igs_fi_cur. Instead defaulted
2506 || the currency with the one that is set up in System Options Form. The same has been
2507 || used for the creation of the charge record.
2508 || swaghmar 22-Aug-2005 Bug 4506599 Modified create_trns_plan_invoice_id ()
2509 ----------------------------------------------------------------------------*/
2510 --ijeddy modified the query in this cursor for bug 3229087.
2511 CURSOR c_ftci IS
2512 SELECT ftci.fee_cal_type,
2513 ftci.fee_ci_sequence_number,
2514 ftci.fee_type,
2515 ft.description description
2516 FROM igs_fi_f_typ_ca_inst ftci,
2517 igs_ca_da_inst_v daiv,
2518 igs_ca_da_inst_v daiv1,
2519 igs_fi_fee_type ft,
2520 igs_fi_fee_str_stat stat
2521 WHERE ftci.fee_type = ft.fee_type
2522 AND ft.s_fee_type = 'DOCUMENT'
2523 AND ftci.fee_type_ci_status = stat.fee_structure_status
2524 AND stat.s_fee_structure_status = 'ACTIVE'
2525 AND NVL (ft.closed_ind, 'N') = 'N'
2526 AND (daiv.dt_alias = ftci.start_dt_alias
2527 AND daiv.sequence_number = ftci.start_dai_sequence_number
2528 AND daiv.cal_type = ftci.fee_cal_type
2529 AND daiv.ci_sequence_number = ftci.fee_ci_sequence_number
2530 )
2531 AND (daiv1.dt_alias = ftci.end_dt_alias
2532 AND daiv1.sequence_number = ftci.end_dai_sequence_number
2533 AND daiv1.cal_type = ftci.fee_cal_type
2534 AND daiv1.ci_sequence_number = ftci.fee_ci_sequence_number
2535 )
2536 AND SYSDATE BETWEEN daiv.alias_val AND NVL (daiv1.alias_val, SYSDATE)
2537 ORDER BY daiv.alias_val DESC;
2538 v_ftci_rec c_ftci%ROWTYPE;
2539 l_v_currency igs_fi_control_all.currency_cd%TYPE;
2540 l_v_curr_desc fnd_currencies_tl.NAME%TYPE;
2541 l_v_message_name fnd_new_messages.message_name%TYPE;
2542 BEGIN
2543 fnd_msg_pub.initialize;
2544 p_msg_count := 0;
2545 OPEN c_ftci;
2546 FETCH c_ftci INTO v_ftci_rec;
2547 IF c_ftci%NOTFOUND THEN
2548 CLOSE c_ftci;
2549 p_return_status := fnd_api.g_ret_sts_error;
2550 fnd_message.set_name ('IGS', 'IGS_FI_FEE_ENCUMB_FEECAT_CAL');
2551 fnd_msg_pub.ADD;
2552 RAISE fnd_api.g_exc_error;
2553 END IF;
2554 CLOSE c_ftci;
2555 --Capture the default currency that is set up in System Options Form.
2556 igs_fi_gen_gl.finp_get_cur (
2557 p_v_currency_cd => l_v_currency,
2558 p_v_curr_desc => l_v_curr_desc,
2559 p_v_message_name => l_v_message_name
2560 );
2561 IF l_v_message_name IS NOT NULL THEN
2562 fnd_message.set_name ('IGS', l_v_message_name);
2563 fnd_msg_pub.ADD;
2564 RAISE fnd_api.g_exc_error;
2565 END IF;
2566 igs_fi_ss_charges_api_pvt.create_charge (
2567 p_api_version => 2.0,
2568 p_init_msg_list => fnd_api.g_false,
2569 p_commit => fnd_api.g_false,
2570 p_validation_level => fnd_api.g_valid_level_full,
2571 p_person_id => p_person_id,
2572 p_fee_type => v_ftci_rec.fee_type,
2573 p_fee_cat => NULL,
2574 p_fee_cal_type => v_ftci_rec.fee_cal_type,
2575 p_fee_ci_sequence_number => v_ftci_rec.fee_ci_sequence_number,
2576 p_course_cd => NULL,
2577 p_attendance_type => NULL,
2578 p_attendance_mode => NULL,
2579 p_invoice_amount => p_fee_amount,
2580 p_invoice_creation_date => SYSDATE,
2581 p_invoice_desc => v_ftci_rec.description,
2582 p_transaction_type => 'DOCUMENT',
2583 p_currency_cd => l_v_currency,
2584 p_exchange_rate => 1,
2585 p_effective_date => NULL,
2586 p_waiver_flag => NULL,
2587 p_waiver_reason => NULL,
2588 p_source_transaction_id => NULL,
2589 p_invoice_id => p_invoice_id,
2590 x_return_status => p_return_status,
2591 x_msg_count => p_msg_count,
2592 x_msg_data => p_msg_data,
2593 x_waiver_amount => p_waiver_amount
2594 );
2595 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
2596 RETURN;
2597 END IF;
2598 -- COMMIT;
2599 EXCEPTION
2600 WHEN fnd_api.g_exc_error THEN
2601 p_return_status := fnd_api.g_ret_sts_error;
2602 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
2603 RETURN;
2604 WHEN fnd_api.g_exc_unexpected_error THEN
2605 p_return_status := fnd_api.g_ret_sts_unexp_error;
2606 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
2607 RETURN;
2608 WHEN OTHERS THEN
2609 p_return_status := fnd_api.g_ret_sts_unexp_error;
2610 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2611 fnd_message.set_token ('NAME', 'CREATE_INVOICE_ID : ' || SQLERRM);
2612 fnd_msg_pub.ADD;
2613 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
2614 RETURN;
2615 END create_trns_plan_invoice_id;
2616
2617 PROCEDURE delete_bulk_item (
2618 p_item_number IN NUMBER,
2619 p_msg_count OUT NOCOPY NUMBER,
2620 p_msg_data OUT NOCOPY VARCHAR2,
2621 p_return_status OUT NOCOPY VARCHAR2
2622 ) AS
2623 -- Delete allowed only if the item is
2624 -- not in completed state
2625 CURSOR c_chk_del_allwed IS
2626 SELECT COUNT (*)
2627 FROM igs_as_doc_details
2628 WHERE item_number = p_item_number
2629 AND item_status = 'PROCESSED';
2630 CURSOR c_items IS
2631 SELECT ROWID
2632 FROM igs_as_doc_details
2633 WHERE item_number = p_item_number;
2634 -- Record types
2635 c_items_rec c_items%ROWTYPE;
2636 l_count NUMBER;
2637 l_rowid igs_as_order_hdr_v.row_id%TYPE;
2638 BEGIN
2639 fnd_msg_pub.initialize;
2640 OPEN c_chk_del_allwed;
2641 FETCH c_chk_del_allwed INTO l_count;
2642 CLOSE c_chk_del_allwed;
2643 IF l_count > 0 THEN
2644 fnd_message.set_name ('IGS', 'IGS_SS_AS_CNT_DEL_BLK_ITM');
2645 fnd_msg_pub.ADD;
2646 RAISE fnd_api.g_exc_error;
2647 END IF;
2648 -- Delete all the items in this order
2649 FOR c_items_rec IN c_items LOOP
2650 igs_as_doc_details_pkg.delete_row (
2651 x_rowid => c_items_rec.ROWID,
2652 x_return_status => p_return_status,
2653 x_msg_data => p_msg_data,
2654 x_msg_count => p_msg_count
2655 );
2656 IF NVL (p_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success THEN
2657 RETURN;
2658 END IF;
2659 END LOOP;
2660 --Delete all the interface items for this order
2661 DELETE igs_as_ord_itm_int
2662 WHERE item_number = p_item_number;
2663 -- Initialize API return status to success.
2664 p_return_status := fnd_api.g_ret_sts_success;
2665 -- Standard call to get message count and if count is 1, get message info
2666 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
2667 EXCEPTION
2668 WHEN fnd_api.g_exc_error THEN
2669 p_return_status := fnd_api.g_ret_sts_error;
2670 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
2671 RETURN;
2672 WHEN fnd_api.g_exc_unexpected_error THEN
2673 p_return_status := fnd_api.g_ret_sts_unexp_error;
2674 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
2675 RETURN;
2676 WHEN OTHERS THEN
2677 p_return_status := fnd_api.g_ret_sts_unexp_error;
2678 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2679 fnd_message.set_token ('NAME', 'Insert_Row : ' || SQLERRM);
2680 fnd_msg_pub.ADD;
2681 fnd_msg_pub.count_and_get (p_count => p_msg_count, p_data => p_msg_data);
2682 RETURN;
2683 END delete_bulk_item;
2684
2685 PROCEDURE place_bulk_order (
2686 p_person_ids IN VARCHAR2,
2687 p_program_cds IN VARCHAR2,
2688 p_prog_vers IN VARCHAR2,
2689 p_printer_name IN VARCHAR2,
2690 p_schedule_date IN DATE,
2691 p_action_type IN VARCHAR2, -- Whether create doc only or create doc and produce docs also
2692 p_trans_type IN igs_as_doc_details.document_type%TYPE,
2693 p_deliv_meth IN igs_as_doc_details.delivery_method_type%TYPE,
2694 p_incl_ind IN VARCHAR2,
2695 p_num_copies IN NUMBER,
2696 p_admin_person_id IN hz_parties.party_id%TYPE,
2697 p_order_desc IN igs_as_order_hdr.order_description%TYPE,
2698 p_purpose IN igs_as_doc_details.doc_purpose_code%TYPE,
2699 p_effbuff OUT NOCOPY VARCHAR2,
2700 p_status OUT NOCOPY VARCHAR2
2701 ) AS
2702 l_req_id NUMBER;
2703 BEGIN
2704 --Call bulk order job
2705 bulk_order_job (
2706 errbuf => p_effbuff,
2707 retcode => p_status,
2708 p_person_ids => p_person_ids,
2709 p_program_cds => p_program_cds,
2710 p_prog_vers => p_prog_vers,
2711 p_printer_name => p_printer_name,
2712 p_schedule_date => p_schedule_date,
2713 p_action_type => p_action_type,
2714 p_trans_type => p_trans_type,
2715 p_deliv_meth => p_deliv_meth,
2716 p_incl_ind => p_incl_ind,
2717 p_num_copies => p_num_copies,
2718 p_admin_person_id => p_admin_person_id,
2719 p_order_desc => p_order_desc,
2720 p_purpose => p_purpose
2721 );
2722 IF p_action_type = 'PRODUCE_DOCS'
2723 AND p_effbuff = 0 THEN
2724 p_status := fnd_api.g_ret_sts_error;
2725 p_effbuff := fnd_message.get;
2726 ELSIF p_status = 1 THEN
2727 p_status := fnd_api.g_ret_sts_error;
2728 ELSE
2729 p_status := fnd_api.g_ret_sts_success;
2730 END IF;
2731 END place_bulk_order;
2732
2733 PROCEDURE submit_print_request (
2734 p_errbuf OUT NOCOPY VARCHAR2,
2735 p_retcode OUT NOCOPY VARCHAR2,
2736 p_order_number IN igs_as_order_hdr.order_number%TYPE,
2737 p_item_numbers IN igs_as_doc_details.item_number%TYPE,
2738 p_printer_name IN VARCHAR2,
2739 p_schedule_date IN DATE
2740 ) AS
2741 CURSOR cur_document_type (cp_order_number IN igs_as_order_hdr.order_number%TYPE) IS
2742 SELECT document_type
2743 FROM igs_as_doc_details
2744 WHERE order_number = cp_order_number;
2745 rec_document_type cur_document_type%ROWTYPE;
2746 l_msg_count NUMBER (10);
2747 l_msg_data VARCHAR2 (2000);
2748 l_return_status VARCHAR2 (2);
2749 l_rowid VARCHAR2 (30) := NULL;
2750 l_order_number igs_as_doc_details.order_number%TYPE;
2751 l_item_number igs_as_doc_details.item_number%TYPE;
2752 l_programs_on_file igs_as_doc_details.programs_on_file%TYPE;
2753 l_printer_options_ret BOOLEAN;
2754 l_req_id NUMBER;
2755 l_oss_country_cd VARCHAR2 (10) := fnd_profile.VALUE ('OSS_COUNTRY_CODE');
2756 l_rep_name VARCHAR2 (10);
2757 l_message VARCHAR2 (2000);
2758 BEGIN
2759 l_printer_options_ret :=
2760 fnd_request.set_print_options (
2761 printer => p_printer_name,
2762 -- style => 'PORTRAIT' ,
2763 copies => 1,
2764 save_output => TRUE,
2765 print_together => 'N'
2766 );
2767 OPEN cur_document_type (p_order_number);
2768 FETCH cur_document_type INTO rec_document_type;
2769 CLOSE cur_document_type;
2770 IF (rec_document_type.document_type = 'TRANSCRIPT') THEN
2771 IF l_oss_country_cd = 'US' THEN
2772 l_rep_name := 'IGSASP26';
2773 ELSE
2774 l_rep_name := 'IGSASP27';
2775 END IF;
2776 END IF;
2777 --This report now needs to take the order number as parameter
2778 l_req_id := fnd_request.submit_request (
2779 application => 'IGS',
2780 program => l_rep_name,
2781 description => NULL,
2782 start_time => NVL (p_schedule_date, SYSDATE),
2783 sub_request => FALSE,
2784 argument1 => 'N',
2785 argument2 => '',
2786 argument3 => '',
2787 argument4 => '',
2788 argument5 => NULL,
2789 argument6 => '',
2790 argument7 => '',
2791 argument8 => '',
2792 argument9 => p_order_number,
2793 argument10 => p_item_numbers
2794 );
2795 p_retcode := TO_CHAR (l_req_id);
2796 IF l_req_id = 0 THEN
2797 p_errbuf := fnd_message.get;
2798 END IF;
2799 COMMIT; --Since the job will not be saved till commit is done
2800 END submit_print_request;
2801
2802 PROCEDURE produce_docs_ss (
2803 p_item_numbers IN VARCHAR2,
2804 p_printer_name IN VARCHAR2,
2805 p_schedule_date IN DATE,
2806 p_ret_status OUT NOCOPY VARCHAR2,
2807 p_effbuff OUT NOCOPY VARCHAR2,
2808 p_req_ids OUT NOCOPY VARCHAR2
2809 ) AS
2810 l_req_id NUMBER (20);
2811 l_item_numbers VARCHAR2 (2000);
2812 l_oss_country_cd VARCHAR2 (10);
2813 l_rep_name VARCHAR2 (10);
2814 l_printer_options_ret BOOLEAN;
2815 TYPE rec IS REF CURSOR;
2816 v rec;
2817 v1 rec;
2818 encert VARCHAR2 (2000)
2819 := 'SELECT 1 FROM igs_as_ord_itm_int WHERE document_type = ''ENCERT'' AND item_number IN ('
2820 || p_item_numbers
2821 || ')';
2822 trns VARCHAR2 (2000)
2823 := 'SELECT 1 FROM igs_as_ord_itm_int WHERE document_type = ''TRANSCRIPT'' AND item_number IN ('
2824 || p_item_numbers
2825 || ')';
2826 CURSOR c_doc (cp_doc_type igs_as_ord_itm_int.document_type%TYPE, cp_item_numbers VARCHAR2) IS
2827 SELECT 1
2828 FROM igs_as_ord_itm_int
2829 WHERE document_type = cp_doc_type
2830 AND item_number IN (cp_item_numbers);
2831 l_encert NUMBER (1) := 0;
2832 l_trns NUMBER (1) := 0;
2833 BEGIN
2834 l_oss_country_cd := fnd_profile.VALUE ('OSS_COUNTRY_CODE');
2835 -- verify the type of doc
2836 OPEN v FOR encert;
2837 LOOP
2838 FETCH v INTO l_encert;
2839 EXIT WHEN v%NOTFOUND;
2840 END LOOP;
2841 OPEN v1 FOR trns;
2842 LOOP
2843 FETCH v1 INTO l_trns;
2844 EXIT WHEN v1%NOTFOUND;
2845 END LOOP;
2846 IF l_trns = 1 THEN
2847 IF l_oss_country_cd = 'US' THEN
2848 l_rep_name := 'IGSASP26';
2849 ELSE
2850 l_rep_name := 'IGSASP27';
2851 END IF;
2852 l_printer_options_ret := fnd_request.set_print_options (
2853 printer => p_printer_name,
2854 -- style => 'PORTRAIT' ,
2855 copies => 1,
2856 save_output => TRUE,
2857 print_together => 'N'
2858 );
2859 l_req_id := fnd_request.submit_request (
2860 application => 'IGS',
2861 program => l_rep_name,
2862 description => NULL,
2863 start_time => NVL (p_schedule_date, SYSDATE),
2864 sub_request => FALSE,
2865 argument1 => 'N',
2866 argument2 => '',
2867 argument3 => NULL,
2868 argument4 => '',
2869 argument5 => NULL,
2870 argument6 => '',
2871 argument7 => '',
2872 argument8 => '',
2873 argument9 => NULL, --Order Number
2874 argument10 => p_item_numbers --Concatr string of item number
2875 );
2876 IF l_req_id = 0 THEN
2877 p_ret_status := fnd_api.g_ret_sts_error;
2878 p_effbuff := fnd_message.get;
2879 ELSIF l_req_id IS NULL THEN
2880 p_ret_status := fnd_api.g_ret_sts_unexp_error;
2881 p_effbuff := fnd_message.get;
2882 ELSE
2883 p_ret_status := fnd_api.g_ret_sts_success;
2884 p_req_ids := l_req_id;
2885 END IF;
2886 END IF;
2887 IF l_encert = 1 THEN
2888 IF l_oss_country_cd = 'US' THEN
2889 l_rep_name := 'IGSASP28';
2890 ELSE
2891 l_rep_name := 'IGSASP29';
2892 END IF;
2893 l_printer_options_ret := fnd_request.set_print_options (
2894 printer => p_printer_name,
2895 -- style => 'PORTRAIT' ,
2896 copies => 1,
2897 save_output => TRUE,
2898 print_together => 'N'
2899 );
2900 l_req_id := fnd_request.submit_request (
2901 application => 'IGS',
2902 program => l_rep_name,
2903 description => NULL,
2904 start_time => NVL (p_schedule_date, SYSDATE),
2905 sub_request => FALSE,
2906 argument1 => 'N',
2907 argument2 => '',
2908 argument3 => p_item_numbers,
2909 argument4 => '',
2910 argument5 => NULL,
2911 argument6 => '',
2912 argument7 => ''
2913 );
2914 IF l_req_id = 0 THEN
2915 p_ret_status := fnd_api.g_ret_sts_error;
2916 p_effbuff := fnd_message.get;
2917 ELSIF l_req_id IS NULL THEN
2918 p_ret_status := fnd_api.g_ret_sts_unexp_error;
2919 p_effbuff := fnd_message.get;
2920 ELSE
2921 p_ret_status := fnd_api.g_ret_sts_success;
2922 IF p_req_ids IS NULL THEN
2923 p_req_ids := l_req_id;
2924 ELSE
2925 p_req_ids := p_req_ids || ', ' || l_req_id;
2926 END IF;
2927 END IF;
2928 END IF;
2929 COMMIT;
2930 END produce_docs_ss;
2931
2932 PROCEDURE bulk_order_job (
2933 errbuf OUT NOCOPY VARCHAR2,
2934 retcode OUT NOCOPY NUMBER,
2935 p_person_ids IN VARCHAR2,
2936 p_program_cds IN VARCHAR2,
2937 p_prog_vers IN VARCHAR2,
2938 p_printer_name IN VARCHAR2,
2939 p_schedule_date IN DATE,
2940 p_action_type IN VARCHAR2, -- Whether create doc only or create doc and produce docs also
2941 p_trans_type IN igs_as_doc_details.document_type%TYPE,
2942 p_deliv_meth IN igs_as_doc_details.delivery_method_type%TYPE,
2943 p_incl_ind IN VARCHAR2,
2944 p_num_copies IN NUMBER,
2945 p_admin_person_id IN hz_parties.party_id%TYPE,
2946 p_order_desc IN igs_as_order_hdr.order_description%TYPE,
2947 p_purpose IN igs_as_doc_details.doc_purpose_code%TYPE
2948 ) AS
2949 l_person_id VARCHAR2 (15);
2950 l_prog_cd VARCHAR2 (10);
2951 l_prog_ver NUMBER (10);
2952 l_seperator VARCHAR2 (1) := '*';
2953 l_person_ids VARCHAR2 (2000);
2954 l_program_cds VARCHAR2 (2000);
2955 l_prog_vers VARCHAR2 (2000);
2956 l_msg_count NUMBER (10);
2957 l_msg_data VARCHAR2 (2000);
2958 l_return_status VARCHAR2 (2);
2959 l_rowid VARCHAR2 (30) := NULL;
2960 l_order_number igs_as_doc_details.order_number%TYPE;
2961 l_item_number igs_as_doc_details.item_number%TYPE;
2962 l_programs_on_file igs_as_doc_details.programs_on_file%TYPE;
2963 l_printer_options_ret BOOLEAN;
2964 l_req_id NUMBER;
2965 l_oss_country_cd VARCHAR2 (10);
2966 l_rep_name VARCHAR2 (10);
2967 l_message VARCHAR2 (2000);
2968
2969 CURSOR c_prsn_info (p_person_id hz_parties.party_id%TYPE) IS
2970 SELECT party_name,
2971 address1,
2972 address2,
2973 address3,
2974 address4,
2975 city,
2976 state,
2977 province,
2978 postal_code,
2979 county,
2980 country,
2981 email_address,
2982 party_number
2983 FROM hz_parties
2984 WHERE party_id = p_person_id;
2985 -- kdande; 12-Jan-2004; Bug# 3220696
2986 CURSOR c_lkup_meaning IS
2987 SELECT meaning
2988 FROM fnd_lookup_values
2989 WHERE lookup_type = 'PE_MIL_ASS_STATUS'
2990 AND lookup_code = 'NA'
2991 AND LANGUAGE = USERENV ('LANG')
2992 AND view_application_id = 8405
2993 AND security_group_id = 0;
2994 c_prsn_info_rec c_prsn_info%ROWTYPE;
2995 l_one_item_created BOOLEAN := FALSE;
2996 l_na_meaning igs_lookups_view.meaning%TYPE;
2997 BEGIN
2998
2999 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
3000
3001 l_oss_country_cd := fnd_profile.VALUE ('OSS_COUNTRY_CODE');
3002 l_person_ids := p_person_ids;
3003 l_program_cds := p_program_cds;
3004 l_prog_vers := p_prog_vers;
3005 -- Admin Person address information for order header table
3006 OPEN c_prsn_info (p_admin_person_id);
3007 FETCH c_prsn_info INTO c_prsn_info_rec;
3008 CLOSE c_prsn_info;
3009 --Get meaning of word NA
3010 OPEN c_lkup_meaning;
3011 FETCH c_lkup_meaning INTO l_na_meaning;
3012 CLOSE c_lkup_meaning;
3013 --Admin can create order if there is no primary address associated with admin.
3014 --Only addr1 and country will have NA
3015 --Create Order Here
3016 errbuf := '0';
3017 --ijeddy, Bug 3129712, put the nvl to country instead of county.
3018 igs_as_order_hdr_pkg.insert_row (
3019 x_msg_count => l_msg_count,
3020 x_msg_data => l_msg_data,
3021 x_return_status => l_return_status,
3022 x_rowid => l_rowid,
3023 x_order_number => l_order_number,
3024 x_order_status => 'INPROCESS',
3025 x_date_completed => NULL,
3026 x_person_id => p_admin_person_id,
3027 x_addr_line_1 => NVL (c_prsn_info_rec.address1, l_na_meaning),
3028 x_addr_line_2 => c_prsn_info_rec.address2,
3029 x_addr_line_3 => c_prsn_info_rec.address3,
3030 x_addr_line_4 => c_prsn_info_rec.address4,
3031 x_city => c_prsn_info_rec.city,
3032 x_state => c_prsn_info_rec.state,
3033 x_province => c_prsn_info_rec.province,
3034 x_county => c_prsn_info_rec.county,
3035 x_country => NVL (c_prsn_info_rec.country, l_na_meaning),
3036 x_postal_code => c_prsn_info_rec.postal_code,
3037 x_email_address => c_prsn_info_rec.email_address,
3038 x_phone_country_code => NULL,
3039 x_phone_area_code => NULL,
3040 x_phone_number => NULL,
3041 x_phone_extension => NULL,
3042 x_fax_country_code => NULL,
3043 x_fax_area_code => NULL,
3044 x_fax_number => NULL,
3045 x_delivery_fee => 0,
3046 x_order_fee => 0,
3047 x_request_type => 'B',
3048 x_submit_method => NULL,
3049 x_invoice_id => NULL,
3050 x_mode => 'R',
3051 x_order_description => p_order_desc,
3052 x_order_placed_by => p_admin_person_id
3053 );
3054 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3055 fnd_message.set_name ('IGS', l_msg_data);
3056 fnd_msg_pub.ADD;
3057 --Log the error and exit since if order is not created, no item can be created
3058 fnd_file.put_line (fnd_file.LOG, '------------------------------------------------------------------------');
3059 fnd_file.put_line (fnd_file.LOG, l_msg_data);
3060 fnd_file.put_line (fnd_file.LOG, '------------------------------------------------------------------------');
3061 RAISE fnd_api.g_exc_error;
3062 END IF;
3063 -- This is the main loop which loops through the persons for whom the items are to be created ---------
3064 l_one_item_created := FALSE;
3065 WHILE LENGTH (l_person_ids) > 2 LOOP
3066 l_person_id := SUBSTR (l_person_ids, 0, INSTR (l_person_ids, l_seperator, 1) - 1); -- First Person Id
3067 l_prog_cd := SUBSTR (l_program_cds, 0, INSTR (l_program_cds, l_seperator, 1) - 1); -- First Prog Cd
3068 l_prog_ver := TO_NUMBER (SUBSTR (l_prog_vers, 0, INSTR (l_prog_vers, l_seperator, 1) - 1)); -- First Prog ver
3069 l_rowid := NULL; -- Nullify old row id
3070 l_item_number := NULL; -- Nullify old item number
3071 --Create Order Item here with items in inprocess status
3072 OPEN c_prsn_info (l_person_id);
3073 FETCH c_prsn_info INTO c_prsn_info_rec;
3074 CLOSE c_prsn_info;
3075 IF p_incl_ind = 'ALL' THEN
3076 l_programs_on_file := 'ALL';
3077 ELSE
3078 l_programs_on_file := l_prog_cd;
3079 END IF;
3080 igs_as_doc_details_pkg.insert_row (
3081 x_rowid => l_rowid,
3082 x_order_number => l_order_number,
3083 x_document_type => 'TRANSCRIPT',
3084 x_document_sub_type => p_trans_type,
3085 x_item_number => l_item_number,
3086 x_item_status => 'INPROCESS',
3087 x_date_produced => NULL,
3088 x_incl_curr_course => NULL,
3089 x_num_of_copies => p_num_copies,
3090 x_comments => NULL,
3091 x_recip_pers_name => c_prsn_info_rec.party_name,
3092 x_recip_inst_name => c_prsn_info_rec.party_number,
3093 x_recip_addr_line_1 => NVL (c_prsn_info_rec.address1, l_na_meaning),
3094 x_recip_addr_line_2 => c_prsn_info_rec.address2,
3095 x_recip_addr_line_3 => c_prsn_info_rec.address3,
3096 x_recip_addr_line_4 => c_prsn_info_rec.address4,
3097 x_recip_city => c_prsn_info_rec.city,
3098 x_recip_postal_code => c_prsn_info_rec.postal_code,
3099 x_recip_state => c_prsn_info_rec.state,
3100 x_recip_province => c_prsn_info_rec.province,
3101 x_recip_county => c_prsn_info_rec.county,
3102 x_recip_country => NVL (c_prsn_info_rec.country, l_na_meaning),
3103 x_recip_fax_area_code => NULL,
3104 x_recip_fax_country_code => NULL,
3105 x_recip_fax_number => NULL,
3106 x_delivery_method_type => p_deliv_meth,
3107 x_programs_on_file => l_programs_on_file,
3108 x_missing_acad_record_data_ind => NULL,
3109 x_missing_academic_record_data => NULL,
3110 x_send_transcript_immediately => NULL,
3111 x_hold_release_of_final_grades => NULL,
3112 x_fgrade_cal_type => NULL,
3113 x_fgrade_seq_num => NULL,
3114 x_hold_degree_expected => NULL,
3115 x_deghold_cal_type => NULL,
3116 x_deghold_seq_num => NULL,
3117 x_hold_for_grade_chg => NULL,
3118 x_special_instr => NULL,
3119 x_express_mail_type => NULL,
3120 x_express_mail_track_num => NULL,
3121 x_ge_certification => NULL,
3122 x_external_comments => NULL,
3123 x_internal_comments => NULL,
3124 x_dup_requested => NULL,
3125 x_dup_req_date => NULL,
3126 x_dup_sent_date => NULL,
3127 x_enr_term_cal_type => NULL,
3128 x_enr_ci_sequence_number => NULL,
3129 x_incl_attempted_hours => NULL,
3130 x_incl_class_rank => NULL,
3131 x_incl_progresssion_status => NULL,
3132 x_incl_class_standing => NULL,
3133 x_incl_cum_hours_earned => NULL,
3134 x_incl_gpa => NULL,
3135 x_incl_date_of_graduation => NULL,
3136 x_incl_degree_dates => NULL,
3137 x_incl_degree_earned => NULL,
3138 x_incl_date_of_entry => NULL,
3139 x_incl_drop_withdrawal_dates => NULL,
3140 x_incl_hrs_for_curr_term => NULL,
3141 x_incl_majors => NULL,
3142 x_incl_last_date_of_enrollment => NULL,
3143 x_incl_professional_licensure => NULL,
3144 x_incl_college_affiliation => NULL,
3145 x_incl_instruction_dates => NULL,
3146 x_incl_usec_dates => NULL,
3147 x_incl_program_attempt => NULL,
3148 x_incl_attendence_type => NULL,
3149 x_incl_last_term_enrolled => NULL,
3150 x_incl_ssn => NULL,
3151 x_incl_date_of_birth => NULL,
3152 x_incl_disciplin_standing => NULL,
3153 x_incl_no_future_term => NULL,
3154 x_incl_acurat_till_copmp_dt => NULL,
3155 x_incl_cant_rel_without_sign => NULL,
3156 x_mode => 'R',
3157 x_return_status => l_return_status,
3158 x_msg_data => l_msg_data,
3159 x_msg_count => l_msg_count,
3160 x_doc_fee_per_copy => 0,
3161 x_delivery_fee => 0,
3162 x_recip_email => c_prsn_info_rec.email_address,
3163 x_overridden_doc_delivery_fee => NULL,
3164 x_overridden_document_fee => NULL,
3165 x_fee_overridden_by => NULL,
3166 x_fee_overridden_date => NULL,
3167 x_incl_department => NULL,
3168 x_incl_field_of_stdy => NULL,
3169 x_incl_attend_mode => NULL,
3170 x_incl_yop_acad_prd => NULL,
3171 x_incl_intrmsn_st_end => NULL,
3172 x_incl_hnrs_lvl => NULL,
3173 x_incl_awards => NULL,
3174 x_incl_award_aim => NULL,
3175 x_incl_acad_sessions => NULL,
3176 x_incl_st_end_acad_ses => NULL,
3177 x_incl_hesa_num => NULL,
3178 x_incl_location => NULL,
3179 x_incl_program_type => NULL,
3180 x_incl_program_name => NULL,
3181 x_incl_prog_atmpt_stat => NULL,
3182 x_incl_prog_atmpt_end => NULL,
3183 x_incl_prog_atmpt_strt => NULL,
3184 x_incl_req_cmplete => NULL,
3185 x_incl_expected_compl_dt => NULL,
3186 x_incl_conferral_dt => NULL,
3187 x_incl_thesis_title => NULL,
3188 x_incl_program_code => NULL,
3189 x_incl_program_ver => NULL,
3190 x_incl_stud_no => NULL,
3191 x_incl_surname => NULL,
3192 x_incl_fore_name => NULL,
3193 x_incl_prev_names => NULL,
3194 x_incl_initials => NULL,
3195 x_doc_purpose_code => p_purpose,
3196 x_plan_id => NULL,
3197 x_produced_by => NULL,
3198 x_person_id => l_person_id
3199 );
3200 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3201 fnd_file.put_line (fnd_file.LOG, '------------------------------------------------------------------------');
3202 fnd_file.put_line (fnd_file.LOG, l_msg_data);
3203 fnd_file.put_line (fnd_file.LOG, '------------------------------------------------------------------------');
3204 RAISE fnd_api.g_exc_error;
3205 ELSIF l_one_item_created = FALSE
3206 AND l_return_status = fnd_api.g_ret_sts_success THEN
3207 l_one_item_created := TRUE;
3208 END IF;
3209 --If docs are to be produced, create order Interface table item here so that report can pick it up
3210 INSERT INTO igs_as_ord_itm_int
3211 (order_number, person_id, document_type, document_sub_type, item_number, item_status, date_produced,
3212 num_of_copies, programs_on_file, comments, recip_pers_name, recip_inst_name, recip_addr_line_1,
3213 recip_addr_line_2, recip_addr_line_3, recip_addr_line_4, recip_city,
3214 recip_postal_code, recip_state, recip_province, recip_county,
3215 recip_country, recip_fax_area_code, recip_fax_country_code, recip_fax_number, delivery_method_type,
3216 dup_requested, dup_req_date, dup_sent_date, fgrade_cal_type, fgrade_seq_num, deghold_cal_type,
3217 deghold_seq_num, hold_for_grade_chg, hold_degree_expected, hold_release_of_final_grades,
3218 incl_curr_course, missing_acad_record_data_ind, missing_academic_record_data,
3219 send_transcript_immediately, special_instr, express_mail_type, express_mail_track_num,
3220 ge_certification, external_comments, internal_comments, enr_term_cal_type, enr_ci_sequence_number,
3221 incl_attempted_hours, incl_class_rank, incl_progresssion_status, incl_class_standing,
3222 incl_cum_hours_earned, incl_gpa, incl_date_of_graduation, incl_degree_dates, incl_degree_earned,
3223 incl_date_of_entry, incl_drop_withdrawal_dates, incl_hrs_earned_for_curr_term, incl_majors,
3224 incl_last_date_of_enrollment, incl_professional_licensure, incl_college_affiliation,
3225 incl_instruction_dates, incl_usec_dates, incl_program_attempt, incl_attendence_type,
3226 incl_last_term_enrolled, incl_ssn, incl_date_of_birth, incl_disciplin_standing, incl_no_future_term,
3227 incl_acurat_till_copmp_dt, incl_cant_rel_without_sign, creation_date, created_by, last_update_date,
3228 last_updated_by, last_update_login, request_id, program_id, program_application_id,
3229 program_update_date, recip_email)
3230 VALUES (l_order_number, l_person_id, 'TRANSCRIPT', p_trans_type, l_item_number, 'INPROCESS', NULL,
3231 p_num_copies, l_prog_cd, NULL, c_prsn_info_rec.party_name, NULL, c_prsn_info_rec.address1,
3232 c_prsn_info_rec.address2, c_prsn_info_rec.address3, c_prsn_info_rec.address4, c_prsn_info_rec.city,
3233 c_prsn_info_rec.postal_code, c_prsn_info_rec.state, c_prsn_info_rec.province, c_prsn_info_rec.county,
3234 c_prsn_info_rec.country, NULL, NULL, NULL, p_deliv_meth,
3235 NULL, NULL, NULL, NULL, NULL, NULL,
3236 NULL, NULL, NULL, NULL,
3237 NULL, NULL, NULL,
3238 NULL, NULL, NULL, NULL,
3239 NULL, NULL, NULL, NULL, NULL,
3240 NULL, NULL, NULL, NULL,
3241 NULL, NULL, NULL, NULL, NULL,
3242 NULL, NULL, NULL, NULL,
3243 NULL, NULL, NULL,
3244 NULL, NULL, NULL, NULL,
3245 NULL, NULL, NULL, NULL, NULL,
3246 NULL, NULL, SYSDATE, fnd_global.user_id, SYSDATE,
3247 fnd_global.user_id, fnd_global.user_id, NULL, NULL, NULL,
3248 NULL, c_prsn_info_rec.email_address);
3249 --End of creation of order and int item
3250 -- Change the concat string to substr for the bext loop--------
3251 l_person_ids := SUBSTR (l_person_ids, INSTR (l_person_ids, l_seperator, 1) + 1); -- Person Id string reduced
3252 l_program_cds := SUBSTR (l_program_cds, INSTR (l_program_cds, l_seperator, 1) + 1); -- Prog Cd string reduced
3253 END LOOP;
3254 IF NOT l_one_item_created THEN
3255 ROLLBACK;
3256 END IF;
3257 --Submit job printing report here if docs are also to be produced
3258 IF l_one_item_created
3259 AND p_action_type = 'PRODUCE_DOCS' THEN
3260 COMMIT; -- Reqd since the report will need the rec in the database
3261 l_printer_options_ret := fnd_request.set_print_options (
3262 printer => p_printer_name,
3263 -- style => 'PORTRAIT' ,
3264 copies => p_num_copies,
3265 save_output => TRUE,
3266 print_together => 'N'
3267 );
3268 IF l_oss_country_cd = 'US' THEN
3269 l_rep_name := 'IGSASP26';
3270 ELSE
3271 l_rep_name := 'IGSASP27';
3272 END IF;
3273 -- This report now needs to take the order number as parameter
3274 l_req_id := fnd_request.submit_request (
3275 application => 'IGS',
3276 program => l_rep_name,
3277 description => NULL,
3278 start_time => NVL (p_schedule_date, SYSDATE),
3279 sub_request => FALSE,
3280 argument1 => 'N',
3281 argument2 => '',
3282 argument3 => NULL,
3283 argument4 => '',
3284 argument5 => p_deliv_meth,
3285 argument6 => '',
3286 argument7 => '',
3287 argument8 => '',
3288 argument9 => l_order_number, --Order Number
3289 argument10 => NULL -- Concatr string of item number
3290 );
3291 IF NVL (l_req_id, 0) = 0 THEN
3292 fnd_message.set_name ('IGS', 'IGS_JOB_FAILED');
3293 fnd_file.put_line (fnd_file.LOG, '------------------------------------------------------------------------');
3294 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
3295 fnd_file.put_line (fnd_file.LOG, '------------------------------------------------------------------------');
3296 END IF;
3297 END IF;
3298 -- End of the main loop which loops through the persons for whom the order is to be created ---------
3299 errbuf := NVL (l_req_id, 0);
3300 COMMIT;
3301 -- Initialize API return status to success.
3302 l_return_status := fnd_api.g_ret_sts_success;
3303 -- Standard call to get message count and if count is 1, get message
3304 -- info.
3305 fnd_msg_pub.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
3306 EXCEPTION
3307 WHEN fnd_api.g_exc_error THEN
3308 ROLLBACK;
3309 l_return_status := fnd_api.g_ret_sts_error;
3310 fnd_msg_pub.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
3311 errbuf := l_msg_data;
3312 retcode := 1; --l_RETURN_STATUS;
3313 RETURN;
3314 WHEN fnd_api.g_exc_unexpected_error THEN
3315 ROLLBACK;
3316 l_return_status := fnd_api.g_ret_sts_unexp_error;
3317 fnd_msg_pub.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
3318 errbuf := l_msg_data;
3319 retcode := 1; --l_RETURN_STATUS;
3320 RETURN;
3321 WHEN OTHERS THEN
3322 ROLLBACK;
3323 l_return_status := fnd_api.g_ret_sts_unexp_error;
3324 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
3325 fnd_message.set_token ('NAME', 'Insert_Row : ' || SQLERRM);
3326 fnd_msg_pub.ADD;
3327 fnd_msg_pub.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
3328 errbuf := l_msg_data;
3329 retcode := 2; --l_RETURN_STATUS;
3330 RETURN;
3331 END bulk_order_job;
3332
3333 FUNCTION get_latest_yop (p_person_id hz_parties.party_id%TYPE, p_course_cd igs_ps_ver.course_cd%TYPE)
3334 RETURN VARCHAR2 IS
3335 CURSOR c_susa IS
3336 SELECT susa.unit_set_cd
3337 FROM igs_as_su_setatmpt susa
3338 WHERE susa.person_id = p_person_id
3339 AND susa.course_cd = p_course_cd
3340 ORDER BY susa.selection_dt DESC;
3341 l_yop VARCHAR2 (10);
3342 c_susa_rec c_susa%ROWTYPE;
3343 BEGIN
3344 OPEN c_susa;
3345 FETCH c_susa INTO c_susa_rec;
3346 l_yop := c_susa_rec.unit_set_cd;
3347 CLOSE c_susa;
3348 RETURN l_yop;
3349 END get_latest_yop;
3350
3351 FUNCTION is_order_del_alwd (p_order_number NUMBER)
3352 RETURN VARCHAR2 IS
3353 CURSOR c_chk_itm_count IS
3354 SELECT COUNT (*)
3355 FROM igs_as_doc_details
3356 WHERE order_number = p_order_number
3357 AND item_status = 'PROCESSED';
3358 l_ret_value VARCHAR2 (1);
3359 l_count NUMBER (10);
3360 BEGIN
3361 OPEN c_chk_itm_count;
3362 FETCH c_chk_itm_count INTO l_count;
3363 CLOSE c_chk_itm_count;
3364 IF NVL (l_count, 0) > 0 THEN
3365 RETURN 'N';
3366 ELSE
3367 RETURN 'Y';
3368 END IF;
3369 END is_order_del_alwd;
3370 END Igs_As_Ss_Doc_Request;