1 PACKAGE BODY IGS_EN_NSC_PKG AS
2 /* $Header: IGSEN87B.pls 120.7 2006/08/11 10:34:37 smaddali noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_EN_NSC_PKG';
6 g_debug_mode BOOLEAN := FALSE;
7 g_branch_id VARCHAR2(25);
8 g_school_id VARCHAR2(30);
9 g_include_gen VARCHAR2(1);
10 g_matr_profile VARCHAR2(20);
11 g_org_id NUMBER(15);
12 l_doc_inst_params igs_en_doc_instances.doc_inst_params%TYPE;
13 l_ant_grad_date DATE;
14 l_grad_type VARCHAR2(25);
15 l_load_term_cd VARCHAR2(20);
16 l_load_cal_type igs_ca_inst.cal_type%TYPE;
17 l_load_cal_seq igs_ca_inst.sequence_number%TYPE;
18 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
19 l_found BOOLEAN := FALSE;
20 TYPE t_counts IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
21 g_counts t_counts ;
22
23 /*pl/sql table to hold valid programs*/
24 TYPE t_pgms_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
25 t_valid_pgms t_pgms_table;
26 l_valid_pgm_index BINARY_INTEGER := 1;
27
28 /*pl/sql table to hold processed students*/
29 TYPE t_pers_table IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
30 t_valid_pers t_pers_table;
31 l_valid_per_index BINARY_INTEGER := 1;
32
33 --Calendar data
34 CURSOR c_cal_data (l_c_cal_type VARCHAR2,l_c_seq_number NUMBER) IS
35 SELECT start_dt ,
36 end_dt ,
37 alternate_code
38 FROM igs_ca_inst
39 WHERE cal_type = l_c_cal_type
40 AND sequence_number = l_c_seq_number;
41
42 -- Type of student record
43
44 TYPE student_data_type IS RECORD (
45 student_id NUMBER(20) , --Student id for the version
46 ssn VARCHAR2(50) , -- Student SSN
47 f_name VARCHAR2(150) , -- First Name
48 m_name VARCHAR2(80) , -- Middle Initial
49 l_name VARCHAR2(150) , -- Last Name
50 suffix VARCHAR2(50) , -- Name Suffix
51 prev_ssn VARCHAR2(50) , -- Previous SSN
52 prev_l_name VARCHAR2(150) , -- Previous Last Name
53 enr_status VARCHAR2(80) , -- Enrollment Status
54 status_date DATE , -- Status Start Date
55 addr1 VARCHAR2(240), -- Street Line 1
56 addr2 VARCHAR2(240), -- Street Line 2
57 city VARCHAR2(80) , -- City
58 state VARCHAR2(60) , -- State
59 zip VARCHAR2(60) , -- Zip
60 country VARCHAR2(80) , -- Country
61 grad_date DATE , -- Graduation Date
62 birth_date DATE , -- Birth Date
63 term_s_date DATE , -- Term Begin Date
64 term_e_date DATE , -- Term End Date
65 grad_level VARCHAR2(1) , -- Graduate Level Indicator
66 data_block VARCHAR2(1) -- Data Block Indicator
67 );
68
69 /*
70 This procedure generates the error message for the
71 concurrent program and writes it into the log file.
72 */
73 PROCEDURE Generate_message;
74
75 /*
76 Function to determine the current enrollment status of the student.
77 It takes the student id as the input parameter.
78 Source API which returns system enrollment status is:
79 Based on the system status the NSLC status should be derived, using set up table.
80 The set up option names are: ENR_STAT_H, ENR_STAT_F and ENR_STAT_L.
81 Also this function returns the status change date for the W, D, A and G statuses.
82 */
83
84 FUNCTION Get_Enrollment_Status (
85 p_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
86 p_student_id IN igs_pe_person.person_id%TYPE ,
87 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
88 p_load_cal_seq IN igs_ca_inst.sequence_number%TYPE,
89 p_acad_cal_type IN igs_ca_inst.cal_type%TYPE,
90 p_acad_cal_seq IN igs_ca_inst.sequence_number%TYPE,
91 p_prev_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
92 x_credit_points OUT NOCOPY NUMBER,
93 x_status_date OUT NOCOPY DATE,
94 x_grad_level OUT NOCOPY VARCHAR2,
95 x_ant_grad_date OUT NOCOPY DATE,
96 p_student_data IN OUT NOCOPY student_data_type,
97 p_nslc_condition IN varchar2
98 ) RETURN VARCHAR2;
99
100
101
102
103 /*
104 This function is used to determine the status change date
105 Its called only if the enrollment status is changed
106 from F to H or L, or from H to L
107 */
108
109 FUNCTION Get_Status_Change_Date (
110 p_student_id IN igs_pe_person.person_id%TYPE,
111 p_old_status IN VARCHAR2,
112 p_new_status IN VARCHAR2,
113 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
114 p_load_cal_seq IN igs_ca_inst.sequence_number%TYPE,
115 p_acad_cal_type IN igs_ca_inst.cal_type%TYPE,
116 p_acad_cal_seq IN igs_ca_inst.sequence_number%TYPE
117 ) RETURN DATE;
118
119
120
121 /*
122 Create snapshot instance record and save main parameters (header record)
123 */
124 PROCEDURE Create_Header_Record (
125 p_school_code IN VARCHAR2,
126 p_branch_code IN VARCHAR2,
127 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
128 p_load_cal_seq IN igs_ca_inst.sequence_number%TYPE,
129 p_report_flag IN VARCHAR2,
130 p_prev_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
131 x_snapshot_id OUT NOCOPY igs_en_doc_instances.doc_inst_id%TYPE
132 ) ;
133
134
135
136 /*
137 This procedure determines the trailer records values,
138 based on the information from the snapshot.
139 Then it stores the trailer record in the EDS.
140 */
141 PROCEDURE Create_Trailer_Record (
142 p_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE
143 );
144
145
146 /*
147 This procedure takes the record with the student information,
148 instantiated before and stores it in the EDS calling the APIs.
149 */
150
151 PROCEDURE Save_Student_Record (
152 p_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
153 p_student_data IN student_data_type
154 );
155
156
157 /*
158 This procedure deletes all non-numeric characters from SSN
159 */
160 FUNCTION process_ssn (
161 p_ssn VARCHAR2
162 ) RETURN VARCHAR2;
163
164 /*
165 This iprocedure retrieves the data for the particular student.
166 PL/SQL record type is used to store the student information.
167 */
168
169 PROCEDURE Get_Student_Data (
170 p_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
171 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
172 p_load_cal_seq IN igs_ca_inst.sequence_number%TYPE,
173 p_acad_cal_type IN igs_ca_inst.cal_type%TYPE,
174 p_acad_cal_seq IN igs_ca_inst.sequence_number%TYPE,
175 p_prev_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
176 p_student_data IN OUT NOCOPY student_data_type,
177 p_nslc_condition IN varchar2
178 );
179
180
181 /*
182 This function is called from the concurrent request
183 */
184 FUNCTION Create_Snapshot(
185 p_comment IN VARCHAR2, -- Runtime Comments
186 p_school_id IN VARCHAR2, -- School code
187 p_branch_id IN VARCHAR2, -- Branch code
188 p_cal_inst_id IN VARCHAR2, -- Calendar instance concatenated ID
189 p_std_rep_flag IN VARCHAR2, -- Standard report flag
190 p_non_std_rpt_type IN VARCHAR2, --Non standard report type like GRADUATE
191 p_prev_inst_id IN igs_en_doc_instances.doc_inst_id%TYPE, -- Previous snapshot Id (if any)
192 p_dirpath IN VARCHAR2, -- Output directory name
193 p_file_name IN VARCHAR2, -- Output file name
194 p_debug_mode IN VARCHAR2
195 ) RETURN BOOLEAN ;
196
197
198 PROCEDURE Put_Debug_Msg (
199 p_debug_message IN VARCHAR2
200 );
201
202 /******************************************************************************/
203 PROCEDURE Add_To_Cache (
204 p_cache IN OUT NOCOPY VARCHAR2,
205 p_new_id IN NUMBER)
206 IS
207 BEGIN
208 IF p_cache IS NULL THEN
209 p_cache:=',';
210 END IF;
211 IF length(p_cache)<30000 THEN --Maximum is 32767. This is the limit, if more add is called for more entries, nothing is done
212 p_cache:=p_cache||p_new_id||',';
213 END IF;
214 END Add_To_Cache ;
215
216
217 FUNCTION Create_Snapshot(
218 p_comment IN VARCHAR2, -- Runtime Comments
219 p_school_id IN VARCHAR2, -- School code
220 p_branch_id IN VARCHAR2, -- Branch code
221 p_cal_inst_id IN VARCHAR2, -- Calendar instance concatenated ID
222 p_std_rep_flag IN VARCHAR2, -- Standard report flag
223 p_non_std_rpt_type IN VARCHAR2, --Non standard report type like GRADUATE
224 p_prev_inst_id IN igs_en_doc_instances.doc_inst_id%TYPE, -- Previous snapshot Id (if any)
225 p_dirpath IN VARCHAR2, -- Output directory name
226 p_file_name IN VARCHAR2, -- Output file name
227 p_debug_mode IN VARCHAR2
228 ) RETURN BOOLEAN
229 -------------------------------------------------------------------------------------------
230 --Change History:
231 -- Who When What
232 -- svanukur 20-jan-2004 changed the cursor c_validate_person_id_grad to check if
233 -- student does has not already been processed to fix bug 3345173
234 --svanukur 05-MAY-2004 changed the cursor c_persons_for_course to include students whose
235 -- discontinuation date falls within the term even if they do not have unit
236 -- attempts in the term. BUG 3611841
237 --somasekar 13-apr-2005 bug# 4179106 modified to neglect the transfer cancelled programs also
238 --somasekar 07-Jun-2005 BUG# 4415651
239 -------------------------------------------------------------------------------------------
240 IS
241
242 l_api_name CONSTANT VARCHAR2(30) := 'Create_Snapshot';
243 cst_valid CONSTANT VARCHAR2(30) := 'VALID';
244 l_student_data student_data_type;
245 l_load_cal_type igs_ca_inst.cal_type%TYPE;
246 l_acad_cal_seq igs_ca_inst.sequence_number%TYPE;
247 l_snapshot_id igs_en_doc_instances.doc_inst_id%TYPE :=NULL;
248 l_branch_code VARCHAR2(20);
249 l_load_term_s_date DATE;
250 l_load_term_e_date DATE;
251 l_req NUMBER;
252
253 CURSOR c_acad_term IS
254 SELECT sup_cal_type,
255 sup_ci_sequence_number
256 FROM igs_ca_inst_rel
257 WHERE sub_cal_type = l_load_cal_type
258 AND sub_ci_sequence_number = l_load_cal_seq
259 AND sup_cal_type IN
260 (SELECT cal_type
261 FROM igs_ca_type_v
262 WHERE s_cal_cat='ACADEMIC');
263
264
265 CURSOR c_grad_type IS
266 SELECT opt_val
267 FROM igs_en_nsc_options
268 WHERE opt_type = 'DATE_GRAD';
269
270
271 --First get a list of active programs which are attempted by a student
272 --such that they belong to the local institution .
273
274 CURSOR c_course (cp_nslc_condition VARCHAR2 )IS
275 SELECT DISTINCT spa.course_cd, spa.version_number, hp.party_id org_id, ihp.oss_org_unit_cd org_unit_cd
276 FROM igs_en_stdnt_ps_Att spa , igs_ps_ver vers, hz_parties hp, igs_pe_hz_parties ihp,
277 igs_or_status os
278 WHERE vers.course_cd=spa.course_cd
279 AND vers.version_number=spa.version_number
280 AND spa.cal_type = l_acad_cal_type
281 AND (
282 (spa.course_attempt_status NOT IN ('DELETED','UNCONFIRM')
283 AND cp_nslc_condition = '3' )
284 OR (cp_nslc_condition <> '3' AND spa.course_attempt_status = 'COMPLETED')
285 )
286 AND spa.future_dated_trans_flag NOT IN ('Y','C')
287 AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
288 AND ihp.oss_org_unit_cd = vers.responsible_org_unit_cd
289 AND ihp.party_id = hp.party_id
290 AND ihp.institution_cd=g_school_id
291 AND hp.party_id = ihp.party_id AND ihp.inst_org_ind = 'O'
292 AND ihp.ou_org_status = os.org_status AND os.s_org_status='ACTIVE';
293
294
295
296
297 --get a list of persons who attempted the program
298 --added the condition to fetch list of students who have discontinued date within the term BUG 3550778
299 ----IF cp_nslc_condition is 1 then consider only completed program attempts
300 --such that course_rqrmnts_complete_dt falls within the term.
301 --IF cp_nslc_condition is 2 then consider only completed program attempts whose conferral date lies in the term.
302 --IF cp_nslc_condition is 3 then check for all program attempts such that
303 -- either the completion date or discontinuation date falls within the term
304 --or a unit_attempt exists for a given term, or a lapsed date exists within the
305 --term. The inactive and intermit status records are fetched irrespective of
306 --the d.ates
307
308 CURSOR c_persons_for_course (cp_course_cd igs_ps_ver.course_cd%TYPE,cp_course_ver igs_ps_ver.version_number%TYPE, cp_nslc_condition VARCHAR2, l_grd_type VARCHAR2) IS
309 SELECT spa.person_id FROM igs_en_stdnt_ps_att spa
310 WHERE course_cd=cp_course_cd
311 AND version_number=cp_course_ver
312 AND spa.cal_type = l_acad_cal_type
313 AND
314 (
315 (cp_nslc_condition = '3' AND discontinued_dt BETWEEN l_load_term_s_date AND l_load_term_e_date)
316 OR
317 (cp_nslc_condition IN ('1','3') AND l_grd_type = 'CRS_RQMNTS_COMPL_DATE'
318 AND course_rqrmnts_complete_dt BETWEEN l_load_term_s_date AND l_load_term_e_date)
319 OR
320 (cp_nslc_condition = '3' AND lapsed_dt BETWEEN l_load_term_s_date AND l_load_term_e_date)
321
322 OR
323 (cp_nslc_condition = '3' AND course_attempt_status = 'INTERMIT'
324 And (EXISTS ( SELECT spi.person_id,spi.course_cd
325 FROM igs_en_stdnt_ps_intm spi
326 WHERE spi.person_id = spa.person_id
327 AND spi.course_cd = cp_course_cd
328 AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
329 AND(
330 ( spi.start_dt <= l_load_term_e_date AND spi.end_dt >=l_load_term_e_date)
331 OR
332 ( spi.start_dt <= l_load_term_e_date AND spi.end_dt >= SYSDATE AND spi.end_dt >= l_load_term_s_date)
333 )
334 )
335 )
336 )
337 OR
338 (cp_nslc_condition in( '2','3') AND l_grd_type = 'CONFERRAL_DATE' AND
339 (EXISTS (select gr.person_id
340 FROM igs_gr_graduand_all gr,igs_en_spa_awd_aim spaa, igs_gr_stat grs
341 WHERE gr.graduand_status =grs. graduand_status
342 AND grs. s_graduand_status = 'GRADUATED'
343 AND gr.person_id = spaa.person_id AND gr.course_cd = spaa.course_cd
344 AND spa.person_id = spaa.person_id
345 AND spa.course_cd = spaa.course_cd
346 AND gr.award_cd = spaa.award_cd
347 AND spaa.conferral_date BETWEEN l_load_term_s_date AND l_load_term_e_date
348 )
349 )
350 )
351 OR
352 (cp_nslc_condition = '3'
353 AND course_attempt_status IN('ENROLLED','DISCONTIN', 'LAPSED','COMPLETED', 'INACTIVE')
354 AND (EXISTS (SELECT 'X' FROM igs_en_su_attempt b, igs_ca_load_to_teach_v lod
355 WHERE b.person_id = spa.person_id
356 and b.course_cd = spa.course_cd
357 and b.cal_type = lod.teach_cal_type
358 AND b.ci_sequence_number = lod.teach_ci_sequence_number
359 AND lod.load_cal_type = l_load_cal_type AND lod.load_ci_sequence_number = l_load_cal_seq
360 and b.unit_Attempt_Status <> 'UNCONFIRM'
361 )
362 )
363 )
364 );
365
366
367 --Check if the person has already been added by checking if an SSN entry has been made .
368 --The SSN Attribute is mandatory (attrib_id=20)
369 CURSOR c_person_already_in_report(cp_person_id igs_pe_person.person_id%TYPE,cp_snapshot_id igs_en_doc_instances.doc_inst_id%TYPE) IS
370 SELECT cst_valid FROM igs_en_attrib_values
371 WHERE obj_type_id = 1
372 AND obj_id = cp_snapshot_id
373 AND attrib_id = 20
374 AND version = cp_person_id;
375
376 --Checks the res_status and SSN of the person
377 CURSOR c_validate_person_id (cp_person_id igs_pe_person.person_id%type) IS
378 SELECT cst_valid
379 FROM igs_pe_eit perv
380 WHERE perv.person_id=cp_person_id
381 AND information_type = 'PE_STAT_RES_STATUS'
382 AND perv.pei_information1 NOT IN
383 (SELECT opt_val
384 FROM igs_en_nsc_options
385 WHERE opt_type = 'NC_STAT_CD'
386 )
387 AND EXISTS
388 (SELECT 'X'
389 FROM igs_pe_alt_pers_id pit,igs_pe_person_id_typ ppit
390 WHERE cp_person_id=pit.pe_person_id
391 AND pit.person_id_type = ppit.person_id_type
392 AND ppit.s_person_id_type = 'SSN'
393 AND pit.start_dt <= SYSDATE
394 AND NVL(pit.end_dt,SYSDATE) >= SYSDATE
395 );
396
397
398 --if g_branch_id<>'00'
399 --this cursor returns 'Valid' only if there is path from the program org to the branch org with no orgs with alt_ids
400 --the only condition where this cursor return wrong result is if g_branch_id=cp_org_unit_cd and and alt_id is defined.
401 --this is checked before calling the cursor.
402 --The condition after the connect by stops the recursion when the Branch org is reached and prunes branches with alt ids
403 --The where clause filters the final hierarchy to see if te Branch org has been reached.
404 CURSOR c_validate_org_branch (cp_org_unit_cd igs_or_unit_v.org_unit_cd%TYPE) IS
405 SELECT cst_valid
406 FROM igs_or_unit_rel
407 WHERE parent_org_unit_cd=g_branch_id
408 CONNECT BY child_org_unit_cd=PRIOR parent_org_unit_cd
409 AND child_org_unit_cd<>g_branch_id AND igs_en_nsc_pkg.org_alt_check(child_org_unit_cd) IS NULL
410 START WITH child_org_unit_cd=cp_org_unit_cd
411 AND igs_en_nsc_pkg.org_alt_check(cp_org_unit_cd) IS NULL;
412
413
414 --org needs to satisfy either c_validate_org_all or c_validate_org_all_inst if g_branch_id='00'
415 --c_validate_org_all returns 'Valid' only if there is path from the program org to the
416 --one of orgs in the institution (with none of the orgs in the pat having alt_ids)
417 --The condition after the connect by prunes branches with alt ids
418 --The where clause filters the final hierarchy to see if a branch org under the institution has been reached.
419 CURSOR c_validate_org_all (cp_org_unit_cd igs_or_unit_v.org_unit_cd%TYPE) IS
420 SELECT cst_valid
421 FROM igs_or_unit_rel
422 WHERE parent_org_unit_cd IN (SELECT ihp.oss_org_unit_cd org_unit_cd FROM hz_parties hp, igs_pe_hz_parties ihp
423 WHERE hp.party_id = ihp.party_id AND ihp.inst_org_ind = 'O'
424 AND ihp.institution_cd = g_school_id)
425 CONNECT BY child_org_unit_cd=PRIOR parent_org_unit_cd
426 AND igs_en_nsc_pkg.org_alt_check(child_org_unit_cd) IS NULL
427 START WITH child_org_unit_cd=cp_org_unit_cd
428 AND igs_en_nsc_pkg.org_alt_check(cp_org_unit_cd) IS NULL;
429
430 --c_validate_org_all_inst returns valid if org is directly under the institution
431 CURSOR c_validate_org_all_inst (cp_org_unit_cd igs_or_unit_v.org_unit_cd%TYPE) IS
432 SELECT cst_valid FROM hz_parties hp, igs_pe_hz_parties ihp
433 WHERE hp.party_id = ihp.party_id AND ihp.inst_org_ind = 'O'
434 AND ihp.oss_org_unit_cd = cp_org_unit_cd
435 AND ihp.party_id = hp.party_id
436 AND ihp.institution_cd = g_school_id
437 AND igs_en_nsc_pkg.org_alt_check(cp_org_unit_cd) IS NULL;
438
439
440
441 CURSOR c_org_unit_cd(g_branch_id VARCHAR2, l_branch_code VARCHAR2) IS
442 SELECT DISTINCT org_structure_id
443 FROM igs_or_org_alt_ids alt
444 WHERE NVL(alt.end_date, SYSDATE) >= SYSDATE
445 AND NVL(alt.start_date,SYSDATE) <= SYSDATE
446 AND org_alternate_id = g_branch_id
447 AND org_alternate_id_type = l_branch_code
448 AND alt.org_structure_type = 'ORG_UNIT';
449
450 -- Cursor to get the org_structure_id for the passed alternate code
451 CURSOR c_school_code IS
452 SELECT alt.org_structure_id
453 FROM igs_or_org_alt_ids alt, igs_or_institution inst
454 WHERE alt.org_structure_id = inst.institution_cd
455 AND alt.org_structure_type = 'INSTITUTE'
456 AND NVL(alt.end_date, SYSDATE) >= SYSDATE
457 AND inst.local_institution_ind = 'Y'
458 AND alt.org_alternate_id = p_school_id
459 ORDER BY 1;
460
461 l_nslc_condition VARCHAR2(10);
462 l_check VARCHAR2(20);
463 l_succ_orgs VARCHAR2(32000):=NULL;
464 l_fail_orgs VARCHAR2(32000):=NULL;
465 l_per_done BOOLEAN:=FALSE;
466 l_course_cd igs_ps_ver.course_cd%TYPE;
467 l_version_number igs_ps_ver.version_number%TYPE;
468
469 CURSOR c_rowid(p_inst_id IGS_EN_DOC_INSTANCES.doc_inst_id%TYPE) IS
470 SELECT rowid
471 FROM igs_en_doc_instances
472 WHERE doc_inst_id = p_inst_id;
473 l_rowid rowid;
474 BEGIN
475
476 -- Standard Start of API savepoint
477 g_debug_mode := TRUE;
478
479 BEGIN
480 IF (g_debug_mode) THEN
481 Put_Debug_Msg(substr('**************************************************************************************************',1,70));
482 Put_Debug_Msg(substr('*********Starting NSC Snapshot creation proccess '||to_char(sysdate,'DD-MON-YY:MI:SS')||' *********************',1,70));
483 Put_Debug_Msg(substr('**************************************************************************************************',1,70));
484 END IF;
485
486 -- Init term data
487 l_load_cal_type := rtrim(substr(p_cal_inst_id,1,10));
488 l_load_cal_seq := to_number(substr(p_cal_inst_id,101,6));
489
490 OPEN c_acad_term;
491 FETCH c_acad_term INTO l_acad_cal_type,l_acad_cal_seq;
492 CLOSE c_acad_term;
493
494 -- Getting branch code and branch id. The first twenty characters holds the id and the rest of the string holds the code.
495
496 g_branch_id := rtrim(substr(p_branch_id,1,20));
497 l_branch_code := substr(p_branch_id,22,10);
498
499 IF g_branch_id <> '00' THEN
500
501 OPEN c_org_unit_cd(g_branch_id,l_branch_code);
502 FETCH c_org_unit_cd INTO g_branch_id;
503 CLOSE c_org_unit_cd;
504
505 END IF;
506
507 -- Get the school code for the passed alternate code
508 OPEN c_school_code;
509 FETCH c_school_code INTO g_school_id;
510 CLOSE c_school_code;
511
512 OPEN c_cal_data(l_load_cal_type,l_load_cal_seq);
513 FETCH c_cal_data INTO l_load_term_s_date ,
514 l_load_term_e_date ,
515 l_load_term_cd;
516 CLOSE c_cal_data;
517
518 -- Storing the values in Doc instances parameters
519 l_doc_inst_params := l_load_term_cd||TO_CHAR(SYSDATE,'DD/MON/YYYY')||p_dirpath||'/'||p_file_name;
520 g_matr_profile := NVL(FND_PROFILE.VALUE('IGS_PE_MATR_TERM'),'ALL');
521 IF g_matr_profile = 'ALL' THEN
522 g_include_gen := 'Y';
523 ELSE
524 g_include_gen := 'N';
525 END IF;
526
527 g_org_id := NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99);
528
529
530 -- Open student cursor
531 OPEN c_grad_type;
532 FETCH c_grad_type INTO l_grad_type;
533 CLOSE c_grad_type;
534
535 IF (g_debug_mode) THEN
536 Put_Debug_Msg('Input parameters: Load term '||l_load_cal_type ||':'||l_load_cal_seq||' Acad term '|| l_acad_cal_type
537 ||':'||l_acad_cal_seq||' Branch code '|| l_branch_code ||' ID '||g_branch_id ||' Term S date '|| l_load_term_s_date
538 ||' Term E date '||l_load_term_e_date ||' Term code '||l_load_term_cd ||' Include gen programs '||g_include_gen
539 ||' Org Id '||g_org_id ||' Prev snapshot Id '||p_prev_inst_id||' g_school_id '||g_school_id ||
540 ' p_non_std_rpt_type '||p_non_std_rpt_type||' p_std_rep_flag '||p_std_rep_flag) ;
541 Put_Debug_Msg('Creating student loop');
542 END IF;
543
544 --The nslc query behaves in different ways for these 3 conditions
545 --the variable l_nslc_condition is passed to all the relevant cursors.
546 IF l_grad_type= 'CRS_RQMNTS_COMPL_DATE' AND p_non_std_rpt_type = 'G' THEN
547 l_nslc_condition :='1';
548 ELSIF l_grad_type= 'CONFERRAL_DATE' AND p_non_std_rpt_type = 'G' THEN
549 l_nslc_condition :='2';
550 ELSIF p_non_std_rpt_type <> 'G' AND p_std_rep_flag <> 'N' THEN
551 l_nslc_condition :='3';
552 ELSE
553 IF (g_debug_mode) THEN
554 Put_Debug_Msg('NSC condition error');
555 END IF;
556 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_NSC_NO_STUDENTS');
557 FND_MSG_PUB.Add;
558 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
559 RAISE FND_API.G_EXC_ERROR;
560 END IF;
561 l_student_data.student_id:=NULL;
562
563 --Loop through the list of courses satisfying the program attempt validations
564 FOR l_course IN c_course (l_nslc_condition) LOOP
565 l_check:=NULL;
566 --Get the org unit of the program if it satisfies further course validations
567 --course validations was split into c_course and c_get_org_if_valid for performance
568 --(both space and time) reasons
569
570 IF INSTR (l_succ_orgs,','||l_course.org_id||',') <>0 THEN
571 --if in cache of successful orgs
572 l_check:=cst_valid;
573
574 ELSIF INSTR (l_fail_orgs,','||l_course.org_id||',') <>0 THEN
575 --if in cache of failed orgs
576 l_check:='INVALID';
577
578 ELSE
579 --Not found in either cache...Proceed with actual org hierarchy validations
580 IF g_branch_id = '00' THEN
581
582 --check if program org is one of the branches in the institution
583 --If it is, the org is valid and no other org validations are required
584
585 OPEN c_validate_org_all_inst(l_course.org_unit_cd);
586 FETCH c_validate_org_all_inst INTO l_check;
587 CLOSE c_validate_org_all_inst;
588
589 IF NVL(l_check,' ')<>cst_valid THEN
590 --check if there is a path from program org to one of the branches in the institution
591
592 OPEN c_validate_org_all(l_course.org_unit_cd);
593 FETCH c_validate_org_all INTO l_check;
594 CLOSE c_validate_org_all;
595 END IF;
596
597 ELSE
598 --g_branchid<>'00' validate the program against the given Branch Org
599 IF g_branch_id=l_course.org_unit_cd THEN
600 l_check:=cst_valid;
601 ELSE
602 --check if there is a path from program org to the branch org
603 OPEN c_validate_org_branch(l_course.org_unit_cd);
604 FETCH c_validate_org_branch INTO l_check;
605 CLOSE c_validate_org_branch;
606 END IF;
607 END IF;
608
609 --Add the org to the appropriate cache depending
610 -- on whether or not the validation was succesfull
611 --If validation failed, skip to next course.
612 IF NVL(l_check,' ')<>cst_valid THEN
613 --this org failed validation .. skip course
614 Add_To_Cache (l_fail_orgs,l_course.org_id);
615 put_debug_msg('Invalid org unit code :'|| l_course.org_id);
616 l_check:='INVALID';
617 ELSE
618 l_check:=cst_valid;
619 Add_To_Cache (l_succ_orgs,l_course.org_id);
620 END IF;
621 END IF;
622
623 IF l_check=cst_valid THEN
624 -- add program to pl/sql table
625
626 t_valid_pgms(l_valid_pgm_index) := rpad(l_course.course_cd,6,' ')||'-'||l_course.version_number;
627 l_valid_pgm_index := l_valid_pgm_index+1;
628 Put_Debug_Msg('Adding program '||rpad(l_course.course_cd,6,' ')||' with version ' ||l_course.version_number);
629 END IF;
630 END LOOP;
631
632 --for each of the program fetched in teh above cursor fetch the list of students.
633 IF t_valid_pgms.count > 0 THEN
634 FOR i in 1 .. t_valid_pgms.count LOOP
635
636 BEGIN
637 SAVEPOINT SP_PROGRAM;
638 Put_Debug_Msg('t_valid_pgms(i)'||t_valid_pgms(i));
639
640 l_course_cd := rtrim(substr(t_valid_pgms(i),1,6));
641 l_version_number := to_number(rtrim(substr(t_valid_pgms(i),8)));
642 Put_Debug_Msg('Processing program ' || l_course_cd ||' version_number '||l_version_number);
643
644 FOR l_person IN c_persons_for_course (l_course_cd,l_version_number,l_nslc_condition,l_grad_type) LOOP
645 l_per_done:=FALSE;
646 l_check:=NULL;
647 --check if the student has already been processed.
648 IF l_snapshot_id IS NOT NULL THEN
649 IF t_valid_pers.count > 0 THEN
650 FOR i in 1 .. t_valid_pers.count LOOP
651 IF t_valid_pers(i) = l_person.person_id THEN
652 l_per_done := TRUE;
653 exit;
654 END IF;
655 END LOOP;
656 END IF;
657 END IF;
658
659
660 IF l_per_done=FALSE THEN
661
662 --check if the person satisfies person validation
663 l_check:=NULL;
664 OPEN c_validate_person_id(l_person.person_id);
665 FETCH c_validate_person_id INTO l_check ;
666 CLOSE c_validate_person_id ;
667
668
669 --If person has failed...skip
670
671 IF NVL(l_check,' ')<>cst_valid THEN
672 --add to student cache.
673 t_valid_pers(l_valid_per_index) := l_person.person_id;
674 l_valid_per_index := l_valid_per_index +1;
675 l_per_done:=TRUE;
676 END IF;
677 END IF; --l_per_done=FALSE
678
679 IF l_per_done=FALSE THEN
680
681 IF l_student_data.student_id IS NULL THEN
682 --First student is being saved...create header first
683
684 IF (g_debug_mode) THEN
685 Put_Debug_Msg('Creating header record');
686 END IF;
687
688 --Initialise counts for statuses for use in trailer.
689 g_counts(10):=0;g_counts(11):=0;g_counts(12):=0;g_counts(13):=0;
690 g_counts(14):=0;g_counts(15):=0;g_counts(16):=0;g_counts(17):=0;
691
692 --Call to Header creation
693 Create_Header_Record (
694 p_school_code => p_school_id,
695 p_branch_code => l_branch_code,
696 p_load_cal_type => l_load_cal_type,
697 p_load_cal_seq => l_load_cal_seq,
698 p_report_flag => p_std_rep_flag,
699 p_prev_snapshot_id => p_prev_inst_id,
700 x_snapshot_id => l_snapshot_id );
701 IF (g_debug_mode) THEN
702 Put_Debug_Msg('Done. Snapshot id: '||l_snapshot_id);
703 END IF;
704 END IF;
705
706 -- Assigning id to a record
707 IF (g_debug_mode) THEN
708 Put_Debug_Msg('Proccessing student: '||l_person.person_id);
709 END IF;
710
711 l_student_data.student_id := l_person.person_id;
712 -- Get student data
713 Get_Student_Data (
714 p_snapshot_id => l_snapshot_id,
715 p_load_cal_type => l_load_cal_type,
716 p_load_cal_seq => l_load_cal_seq ,
717 p_acad_cal_type => l_acad_cal_type,
718 p_acad_cal_seq => l_acad_cal_seq ,
719 p_prev_snapshot_id => p_prev_inst_id ,
720 p_student_data => l_student_data,
721 p_nslc_condition => l_nslc_condition
722 );
723
724
725 -- Save student data
726 IF (g_debug_mode) THEN
727 Put_Debug_Msg('End Get_Student_Date');
728 END IF;
729
730 IF l_student_data.ssn IS NOT NULL THEN --person details were retrieved successfully
731 --add to student cache.
732 t_valid_pers(l_valid_per_index) := l_person.person_id;
733 l_valid_per_index := l_valid_per_index +1;
734
735 IF l_student_data.enr_status IS NOT NULL THEN
736 l_found := TRUE;
737 Save_Student_Record (p_snapshot_id => l_snapshot_id,
738 p_student_data => l_student_data);
739 IF (g_debug_mode) THEN
740 Put_Debug_Msg('Record saved');
741 END IF;
742 END IF;
743 END IF;
744
745 l_student_data.ssn := NULL;
746 l_student_data.f_name := NULL;
747 l_student_data.m_name := NULL;
748 l_student_data.l_name := NULL;
749 l_student_data.suffix := NULL;
750 l_student_data.prev_ssn := NULL;
751 l_student_data.prev_l_name := NULL;
752 l_student_data.enr_status := NULL;
753 l_student_data.status_date := NULL;
754 l_student_data.addr1 := NULL;
755 l_student_data.addr2 := NULL;
756 l_student_data.city := NULL;
757 l_student_data.state := NULL;
758 l_student_data.zip := NULL;
759 l_student_data.country := NULL;
760 l_student_data.grad_date := NULL;
761 l_student_data.birth_date := NULL;
762 l_student_data.term_s_date := NULL;
763 l_student_data.term_e_date := NULL;
764 l_student_data.grad_level := NULL;
765 l_student_data.data_block := NULL;
766 END IF;--l_per_done=TRUE
767 END LOOP; --person loop
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 Put_Debug_Msg('exception raised, rolling back data');
772 ROLLBACK to SP_PROGRAM;
773 RAISE;
774 END;
775 COMMIT;
776 END LOOP; --program loop
777 END IF; --count;
778 -- Check if there are any records found
779 IF NOT l_found THEN
780
781 IF (g_debug_mode) THEN
782 Put_Debug_Msg('Raising exception NO_STUDENTS_FOUND');
783 END IF;
784
785 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_NSC_NO_STUDENTS');
786 --FND_MSG_PUB.Add;
787 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
788 RAISE FND_API.G_EXC_ERROR;
789
790 END IF;
791
792 -- Create trailer record
793 Create_Trailer_Record (p_snapshot_id => l_snapshot_id) ;
794
795 IF (g_debug_mode) THEN
796 Put_Debug_Msg('Trailer record created');
797 END IF;
798
799 COMMIT;
800 EXCEPTION
801 WHEN OTHERS THEN
802 --delete the records with this snapshot ID
803 begin
804 savepoint delete_snapshot;
805 Put_Debug_Msg('deleting snapshot id ' ||l_snapshot_id);
806 OPEN c_rowid(l_snapshot_id);
807 FETCH c_rowid INTO l_rowid;
808 CLOSE c_rowid;
809
810 -- Deleting the instance record and all attribute values
811 if l_rowid is not null then
812 Put_Debug_Msg('calling delete row for ' ||l_rowid);
813 IGS_EN_DOC_INSTANCES_PKG.Delete_Row(
814 x_rowid => l_rowid
815 );
816
817 end if;
818 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
819 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
820 END IF;
821 COMMIT;
822 Put_Debug_Msg('returning false');
823 RETURN FALSE;
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 ROLLBACK TO DELETE_SNAPSHOT;
828 Put_Debug_Msg('ERROR while deleting snapshot id ' ||l_snapshot_id);
829 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
830 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
831 END IF;
832 RETURN FALSE;
833 END;
834 END;
835 -- Print snapshot
836 Put_Debug_Msg('calling print job');
837 SAVEPOINT Print_Snapshot;
838
839 -- Raising the request to create a text file in the specified format
840 l_req := FND_REQUEST.SUBMIT_REQUEST
841 ('IGS',
842 'IGSENJ09',
843 NULL,
844 NULL,
845 FALSE,
846 p_comment,
847 to_char(l_snapshot_id),
848 p_dirpath,
849 p_file_name );
850
851
852 IF (l_req > 0) THEN
853 IF (g_debug_mode) THEN
854 Put_Debug_Msg('Text file creation request submitted');
855 END IF;
856 ELSE
857 IF (g_debug_mode) THEN
858 Put_Debug_Msg('Error during request submition');
859 END IF;
860 RAISE FND_API.G_EXC_ERROR;
861 END IF;
862
863 COMMIT;
864 RETURN TRUE;
865
866 EXCEPTION
867 WHEN FND_API.G_EXC_ERROR THEN
868 ROLLBACK TO Print_Snapshot;
869 Put_Debug_Msg('G_EXC_ERROR'||sqlerrm);
870 RETURN FALSE;
871
872 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
873 ROLLBACK TO Print_Snapshot;
874 Put_Debug_Msg('UNEXPECTED_ERROR'||sqlerrm);
875 RETURN FALSE;
876
877 WHEN OTHERS THEN
878 ROLLBACK TO Print_Snapshot;
879 Put_Debug_Msg('OTHERS'||sqlerrm);
880 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
881 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
882 END IF;
883
884 RETURN FALSE;
885
886
887 END Create_Snapshot;
888
889 /******************************************************************************/
890 /*
891 This is the main procedure which prints the data into the text file.
892 Its called from the concurent request form or from the Create_Snapshot procedure.
893 */
894
895 PROCEDURE Print_Snapshot_Request (
896 /*************************************************************
897 Change History
898 Who When What
899 (reverse chronological order - newest change first)
900 ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) as a part of bug#4958173.
901 ***************************************************************/
902 errbuf OUT NOCOPY VARCHAR2, -- Request standard error string
903 retcode OUT NOCOPY NUMBER , -- Request standard return status
904 p_comment IN VARCHAR2, -- Runtime comments
905 p_inst_id IN igs_en_doc_instances.doc_inst_id%TYPE, -- Snapshot Id to create a file
906 p_dirpath IN VARCHAR2 , -- Output directory name
907 p_file_name IN VARCHAR2 , -- Output file name
908 p_debug_mode IN VARCHAR2 := FND_API.G_FALSE
909 )
910 IS
911
912 l_api_name CONSTANT VARCHAR2(30) := 'Print_Snapshot_Request';
913 l_Return_Status VARCHAR2(1);
914 l_msg_count NUMBER;
915 l_msg_data VARCHAR2(2000);
916 l_dirpath VARCHAR2(100);
917 l_file_name VARCHAR2(100);
918
919 BEGIN
920 -- Printing file
921 igs_ge_gen_003.set_org_id(NULL);
922 IGS_EN_NSC_FILE_PRNT_PKG.Generate_file(
923 p_api_version => 1.0 ,
924 x_return_status => l_Return_Status,
925 x_msg_count => l_msg_count,
926 x_msg_data => l_msg_data,
927 p_obj_type_id => 1,
928 p_doc_inst_id => p_inst_id,
929 p_dirpath => p_dirpath,
930 p_file_name => p_file_name,
931 p_form_id => 1 ,
932 p_debug_mode => p_debug_mode
933 );
934
935
936 -- Checking the status
937
938 IF (l_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
939 fnd_file.put_line(FND_FILE.LOG,'File Successfully Printed ');
940 --Successfull completion
941 retcode := 0;
942 ELSE
943 -- Error: generating message and returning the error code
944 retcode := 2;
945 END IF;
946
947 EXCEPTION
948
949 WHEN OTHERS THEN
950
951 retcode := 2;
952
953 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
954 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
955 END IF;
956
957 Generate_Message;
958
959 END Print_Snapshot_Request;
960
961
962 /******************************************************************************/
963
964 PROCEDURE Create_Snapshot_Request(
965 /*************************************************************
966 Change History
967 Who When What
968 (reverse chronological order - newest change first)
969 ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) as a part of bug#4958173.
970 ***************************************************************/
971 errbuf OUT NOCOPY VARCHAR2, -- Request standard error string
972 retcode OUT NOCOPY NUMBER , -- Request standard return status
973 p_comment IN VARCHAR2, -- Runtime Comments
974 p_school_id IN VARCHAR2, -- School code
975 p_branch_id IN VARCHAR2, -- Branch code
976 p_cal_inst_id IN VARCHAR2, -- Calendar instance concatenated ID
977 p_std_rep_flag IN VARCHAR2, -- Standard report flag
978 p_dummy IN VARCHAR2, /* Dummy Parameter used for validation of non_std_rpt_type Value Set, it is nowhere used in the procedure. */
979 p_non_std_rpt_typ IN VARCHAR2, -- Non Standard report type like 'GRADUATE'
980 p_prev_inst_id IN igs_en_doc_instances.doc_inst_id%TYPE, -- Previous snapshot Id (if any)
981 p_dirpath IN VARCHAR2, -- Output directory name
982 p_file_name IN VARCHAR2, -- Output file name
983 p_debug_mode IN VARCHAR2 := FND_API.G_FALSE
984 )IS
985 l_api_name CONSTANT VARCHAR2(30) := 'Create_Snapshot_Request';
986 l_return_status VARCHAR2(1);
987 l_msg_count NUMBER;
988 l_msg_data VARCHAR2(2000);
989 p_non_std_rpt_type VARCHAR2(20);
990 BEGIN
991
992 igs_ge_gen_003.set_org_id(NULL);
993 -- If the parameter non standard report type is passed as null
994 -- then it is assigned with a value 'Y'
995 IF p_non_std_rpt_typ IS NULL THEN
996 p_non_std_rpt_type := 'Y';
997 ELSE
998 p_non_std_rpt_type := p_non_std_rpt_typ;
999 END IF;
1000
1001 -- Calling create snapshot procedure
1002 IF Create_Snapshot(
1003 p_comment => p_comment,
1004 p_school_id => p_school_id ,
1005 p_branch_id => p_branch_id ,
1006 p_cal_inst_id => p_cal_inst_id ,
1007 p_std_rep_flag => p_std_rep_flag,
1008 p_non_std_rpt_type => p_non_std_rpt_type,
1009 p_prev_inst_id => p_prev_inst_id,
1010 p_dirpath => p_dirpath ,
1011 p_file_name => p_file_name ,
1012 p_debug_mode => p_debug_mode)
1013 THEN
1014 --Successfull completion
1015 retcode := 0;
1016 ELSE
1017 -- Error: generating message and returning the error code
1018 --Generate_Message;
1019 retcode := 2;
1020 END IF;
1021 EXCEPTION
1022
1023 WHEN OTHERS THEN
1024
1025 retcode := 2;
1026
1027 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1028 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1029 END IF;
1030
1031 Generate_Message;
1032
1033 END Create_Snapshot_Request;
1034
1035 /******************************************************************************/
1036 /*
1037 This is the procedure which deletes the snapshot from the database.
1038 */
1039
1040 PROCEDURE Delete_Snapshot_Request (
1041 /*************************************************************
1042 Change History
1043 Who When What
1044 (reverse chronological order - newest change first)
1045 ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) as a part of bug#4958173.
1046 ***************************************************************/
1047 errbuf OUT NOCOPY VARCHAR2, -- Request standard error string
1048 retcode OUT NOCOPY NUMBER , -- Request standard return status
1049 p_comment IN VARCHAR2, -- Runtime comments
1050 p_inst_id IN igs_en_doc_instances.doc_inst_id%TYPE , -- Snapshot Id to delete
1051 p_debug_mode IN VARCHAR2 := FND_API.G_FALSE
1052 )IS
1053 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Snapshot_Request';
1054 l_return_status CONSTANT VARCHAR2(1) := 'S';
1055 l_msg_count NUMBER;
1056 l_msg_data VARCHAR2(2000);
1057 l_rowid VARCHAR2(255);
1058
1059 CURSOR c_rowid IS
1060 SELECT rowid
1061 FROM igs_en_doc_instances
1062 WHERE doc_inst_id = p_inst_id;
1063
1064 BEGIN
1065
1066 igs_ge_gen_003.set_org_id(NULL);
1067 OPEN c_rowid;
1068 FETCH c_rowid INTO l_rowid;
1069 CLOSE c_rowid;
1070
1071 -- Deleting the instance record and all attribute values
1072
1073 IGS_EN_DOC_INSTANCES_PKG.Delete_Row(
1074 x_rowid => l_rowid
1075 );
1076
1077 -- Checking the status
1078 IF (l_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
1079 --Successfull completion
1080 fnd_file.put_line(FND_FILE.LOG,'File deleted successfully.');
1081 retcode := 0;
1082 ELSE
1083 -- Error: generating message and returning the error code
1084 retcode := 2;
1085 END IF;
1086
1087 EXCEPTION
1088
1089 WHEN OTHERS THEN
1090
1091 retcode := 2;
1092
1093 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1094 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1095 END IF;
1096
1097 Generate_Message;
1098
1099 END Delete_Snapshot_Request;
1100
1101 /******************************************************************************/
1102
1103 PROCEDURE Generate_Message
1104 IS
1105 l_msg_count NUMBER;
1106 l_msg_data VARCHAR2(2000);
1107
1108 BEGIN
1109
1110 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
1111 p_data => l_msg_data );
1112
1113 IF (l_msg_count > 0) THEN
1114 l_msg_data := '';
1115 FOR l_cur IN 1..l_msg_count LOOP
1116 l_msg_data := FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
1117 put_debug_msg(l_msg_data);
1118 fnd_file.put_line (FND_FILE.LOG, l_msg_data);
1119 END LOOP;
1120 ELSE
1121 FND_MESSAGE.SET_NAME('IGS','IGS_EN_NO_ERR_STACK_DATA');
1122 l_msg_data := FND_MESSAGE.GET;
1123 put_debug_msg(l_msg_data);
1124 fnd_file.put_line (FND_FILE.LOG, l_msg_data);
1125 END IF;
1126
1127 END Generate_Message;
1128
1129 /******************************************************************************/
1130
1131 PROCEDURE Check_Result (p_code VARCHAR2)
1132 IS
1133 BEGIN
1134
1135 IF substr(p_code,1,1) <> 'S' THEN
1136 Put_Debug_Msg('Error during EDS insertion: '||p_code);
1137 RAISE FND_API.G_EXC_ERROR;
1138 END IF;
1139
1140 END Check_Result;
1141
1142 /******************************************************************************/
1143
1144 PROCEDURE Create_Header_Record (
1145 p_school_code IN VARCHAR2,
1146 p_branch_code IN VARCHAR2,
1147 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
1148 p_load_cal_seq IN igs_ca_inst.sequence_number%TYPE,
1149 p_report_flag IN VARCHAR2,
1150 p_prev_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
1151 x_snapshot_id OUT NOCOPY igs_en_doc_instances.doc_inst_id%TYPE
1152 ) IS
1153
1154 l_msg_count NUMBER;
1155 l_msg_data VARCHAR2(2000);
1156 l_Return_Status VARCHAR2(1);
1157 l_rowid VARCHAR2(255);
1158 l_inst_id igs_en_doc_instances.doc_inst_id%TYPE;
1159 l_code VARCHAR2(3);
1160 BEGIN
1161
1162 -- Creating instance
1163
1164 IGS_EN_DOC_INSTANCES_PKG.Insert_row (
1165 x_rowid => l_rowid,
1166 x_doc_inst_id => l_inst_id,
1167 x_doc_inst_name => 'NSCL Interface instance' ,
1168 x_doc_inst_params => l_doc_inst_params
1169 );
1170
1171 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1172 RAISE FND_API.G_EXC_ERROR;
1173 END IF;
1174
1175 x_snapshot_id := l_inst_id;
1176
1177 -- Saving values :p_obj_type_id,p_obj_id p_attrib_id p_version p_value x_return_code.
1178 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,l_inst_id,1,0,p_school_code,l_code);
1179 Check_Result(l_code); --Checking the result code
1180
1181 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,l_inst_id,2,0,p_branch_code,l_code);
1182 Check_Result(l_code); --Checking the result code
1183
1184 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,l_inst_id,3,0,p_load_cal_type,l_code);
1185 Check_Result(l_code); --Checking the result code
1186
1187 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,l_inst_id,4,0,p_load_cal_seq,l_code);
1188 Check_Result(l_code); --Checking the result code
1189
1190 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,l_inst_id,5,0,p_report_flag,l_code);
1191 Check_Result(l_code); --Checking the result code
1192
1193 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,l_inst_id,6,0,to_char(sysdate,'YYYYMMDD'),l_code);
1194 Check_Result(l_code); --Checking the result code
1195
1196 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,l_inst_id,7,0,p_prev_snapshot_id,l_code);
1197 Check_Result(l_code); --Checking the result code
1198
1199 END Create_Header_Record;
1200
1201
1202 /******************************************************************************/
1203
1204 PROCEDURE Create_Trailer_Record (
1205 p_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE
1206 )IS
1207 --Statuses mapping
1208 --10,'F',11,'H',12,'L',13,'W',14,'G',15,'A',16,'X',17,'D','?')
1209
1210 l_code VARCHAR2(3);
1211 BEGIN
1212
1213 g_counts(18):=0;
1214 --Making loop through attributes to get counts for the trailer record
1215 FOR l_cur_count IN 10..17 LOOP
1216 --Store value in the EDS
1217 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,l_cur_count,0,g_counts(l_cur_count),l_code);
1218 Check_Result(l_code); --Checking the result code
1219 g_counts(18):=g_counts(18)+g_counts(l_cur_count);
1220 END LOOP;
1221
1222 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,18,0,g_counts(18),l_code);
1223 Check_Result(l_code); --Checking the result code
1224
1225 END Create_Trailer_Record;
1226
1227 /******************************************************************************/
1228
1229
1230
1231
1232 PROCEDURE Save_Student_Record (
1233 p_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
1234 p_student_data IN student_data_type
1235 )IS
1236 l_code VARCHAR2(3);
1237
1238 BEGIN
1239 --Saving value;
1240 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,20,p_student_data.student_id,p_student_data.ssn,l_code);
1241 Check_Result(l_code); --Checking the result code
1242
1243 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,21,p_student_data.student_id,p_student_data.f_name,l_code);
1244 Check_Result(l_code); --Checking the result code
1245
1246 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,22,p_student_data.student_id,p_student_data.m_name,l_code);
1247 Check_Result(l_code); --Checking the result code
1248
1249 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,23,p_student_data.student_id,p_student_data.l_name,l_code);
1250 Check_Result(l_code); --Checking the result code
1251
1252 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,24,p_student_data.student_id,p_student_data.suffix,l_code);
1253 Check_Result(l_code); --Checking the result code
1254
1255 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,25,p_student_data.student_id,p_student_data.prev_ssn,l_code);
1256 Check_Result(l_code); --Checking the result code
1257
1258 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,26,p_student_data.student_id,p_student_data.prev_l_name,l_code);
1259 Check_Result(l_code); --Checking the result code
1260
1261 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,27,p_student_data.student_id,p_student_data.enr_status,l_code);
1262 Check_Result(l_code); --Checking the result code
1263
1264 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,28,p_student_data.student_id,to_char(p_student_data.status_date,'YYYYMMDD'),l_code);
1265 Check_Result(l_code); --Checking the result code
1266
1267 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,29,p_student_data.student_id,p_student_data.addr1,l_code);
1268 Check_Result(l_code); --Checking the result code
1269
1270 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,30,p_student_data.student_id,p_student_data.addr2,l_code);
1271 Check_Result(l_code); --Checking the result code
1272
1273 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,31,p_student_data.student_id,p_student_data.city,l_code);
1274 Check_Result(l_code); --Checking the result code
1275
1276 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,32,p_student_data.student_id,p_student_data.state,l_code);
1277 Check_Result(l_code); --Checking the result code
1278
1279 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,33,p_student_data.student_id,p_student_data.zip,l_code);
1280 Check_Result(l_code); --Checking the result code
1281
1282 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,34,p_student_data.student_id,p_student_data.country,l_code);
1283 Check_Result(l_code); --Checking the result code
1284
1285 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,35,p_student_data.student_id,to_char(p_student_data.grad_date,'YYYYMMDD'),l_code);
1286 Check_Result(l_code); --Checking the result code
1287
1288 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,36,p_student_data.student_id,to_char(p_student_data.birth_date,'YYYYMMDD'),l_code);
1289 Check_Result(l_code); --Checking the result code
1290
1291 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,37,p_student_data.student_id,to_char(p_student_data.term_s_date,'YYYYMMDD'),l_code);
1292 Check_Result(l_code); --Checking the result code
1293
1294 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,38,p_student_data.student_id,to_char(p_student_data.term_e_date,'YYYYMMDD'),l_code);
1295 Check_Result(l_code); --Checking the result code
1296
1297 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,39,p_student_data.student_id,p_student_data.grad_level,l_code);
1298 Check_Result(l_code); --Checking the result code
1299
1300 IGS_EN_GS_ATTRIB_VAL.SET_VALUE(1,p_snapshot_id,40,p_student_data.student_id,p_student_data.data_block,l_code);
1301 Check_Result(l_code); --Checking the result code
1302
1303 END Save_Student_Record;
1304
1305
1306
1307 /******************************************************************************/
1308
1309
1310 FUNCTION process_ssn (
1311 p_ssn VARCHAR2
1312 ) RETURN VARCHAR2
1313 IS
1314 l_new_val VARCHAR2(25);
1315 l_char VARCHAR2(1);
1316 l_len NUMBER;
1317 BEGIN
1318
1319 l_len := NVL(length(p_ssn),0);
1320
1321 FOR i IN 1..l_len LOOP
1322
1323 l_char := substr(p_ssn,i,1);
1324
1325 IF l_char between '0' and '9' THEN
1326 l_new_val := l_new_val||l_char;
1327 END IF;
1328
1329 END LOOP;
1330
1331 RETURN l_new_val;
1332
1333 END process_ssn;
1334
1335 /******************************************************************************/
1336 /* Get previous academic term */
1337
1338 FUNCTION get_prev_acad_term(p_person_id NUMBER)
1339 RETURN DATE AS
1340
1341 -- Cursor to fetch the Units
1342 CURSOR c_units(p_person_id NUMBER) IS
1343 SELECT cal_type,ci_sequence_number
1344 FROM igs_en_su_attempt
1345 WHERE person_id = p_person_id
1346 AND unit_attempt_status IN ('COMPLETED','ENROLLED','DUPLICATE')
1347 AND ci_start_dt <= SYSDATE
1348 ORDER by ci_end_dt DESC;
1349
1350 l_cal_type igs_en_su_attempt.cal_type%TYPE;
1351 l_ci_seq_num igs_en_su_attempt.ci_sequence_number%TYPE;
1352
1353 CURSOR c_prev_acad_term(p_cal_type VARCHAR2, p_ci_sequence_number NUMBER) IS
1354 SELECT load_end_dt
1355 FROM igs_ca_teach_to_load_v
1356 WHERE teach_cal_type = p_cal_type
1357 AND teach_ci_Sequence_number = p_ci_sequence_number
1358 ORDER by load_end_dt DESC;
1359
1360 l_load_end_dt DATE;
1361
1362 BEGIN
1363
1364 /* open the cursor and fetching all the units in descending order of ci_end_dt
1365 which is in status COMPLETED,ENROLLED and DUPLICATE */
1366
1367 OPEN c_units(p_person_id);
1368 FETCH c_units INTO l_cal_type,l_ci_seq_num;
1369 CLOSE c_units;
1370
1371 /* open the cursor and fetch the end date of the load calendar type
1372 of the corresponding teaching periods return from above cursor */
1373
1374 OPEN c_prev_acad_term(l_cal_type,l_ci_seq_num);
1375 FETCH c_prev_acad_term INTO l_load_end_dt;
1376 CLOSE c_prev_acad_term;
1377
1378 RETURN l_load_end_dt;
1379
1380 END get_prev_acad_term;
1381
1382
1383
1384 PROCEDURE Get_Student_Data (
1385 p_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
1386 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
1387 p_load_cal_seq IN igs_ca_inst.sequence_number%TYPE,
1388 p_acad_cal_type IN igs_ca_inst.cal_type%TYPE,
1389 p_acad_cal_seq IN igs_ca_inst.sequence_number%TYPE,
1390 p_prev_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
1391 p_student_data IN OUT NOCOPY student_data_type,
1392 p_nslc_condition IN VARCHAR2
1393 )IS
1394
1395 l_prev_stat VARCHAR2(1);
1396 l_student_id NUMBER;
1397 l_code VARCHAR2(80);
1398 l_comp_period VARCHAR2(30);
1399 l_comp_year NUMBER(4);
1400 l_acad_cal_cd VARCHAR2(80);
1401 l_date DATE;
1402 l_credit_points NUMBER;
1403 l_student_data student_data_type;
1404
1405 -- Cursor to get the SSN
1406 CURSOR c_ssn_num IS
1407 SELECT api_person_id
1408 FROM igs_pe_alt_pers_id
1409 WHERE pe_person_id = p_student_data.student_id
1410 AND person_id_type
1411 IN (SELECT person_id_type
1412 FROM igs_pe_person_id_typ
1413 WHERE s_person_id_type = 'SSN')
1414 AND start_dt <= sysdate
1415 AND end_dt IS NULL;
1416
1417 -- Cursor to get the person details..cursor is changed from igs_pe_person to underlying tables
1418 CURSOR c_name IS
1419 SELECT p.person_last_name surname,
1420 p.person_first_name given_names,
1421 p.person_middle_name middle_name,
1422 p.person_name_suffix suffix,
1423 pp.date_of_birth birth_dt
1424 FROM hz_parties P, hz_person_profiles PP
1425 WHERE p.party_id = p_student_data.student_id
1426 AND p.party_id = pp.party_id
1427 AND pp.content_source_type = 'USER_ENTERED'
1428 AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
1429
1430 -- Cursor to get the previous SSN
1431 CURSOR c_prev_ssn_num IS
1432 SELECT api_person_id
1433 FROM igs_pe_alt_pers_id
1434 WHERE pe_person_id = p_student_data.student_id
1435 AND person_id_type
1436 IN (SELECT person_id_type
1437 FROM igs_pe_person_id_typ
1438 WHERE s_person_id_type = 'SSN')
1439 AND start_dt <= sysdate
1440 AND end_dt IS NOT NULL
1441 AND start_dt <> end_dt
1442 ORDER BY end_dt DESC;
1443
1444
1445 -- Check for previous last name
1446 CURSOR c_prev_last_name IS
1447 SELECT ppa.surname
1448 FROM igs_pe_person_alias ppa,
1449 igs_en_nsc_options eno
1450 WHERE ppa.person_id = p_student_data.student_id
1451 AND ppa.alias_type = eno.opt_val
1452 AND eno.opt_type = 'LAST_NAME'
1453 AND ppa.start_dt <= SYSDATE
1454 AND (ppa.end_dt IS NULL OR ppa.end_dt > SYSDATE )
1455 ORDER BY priority ASC, start_dt DESC;
1456
1457 -- Check for address
1458 CURSOR c_address IS
1459 SELECT addr_line_1 ,
1460 addr_line_2 ,
1461 city ,
1462 decode(country,'US',state,'FO') state,
1463 decode(country,'US',postal_code,' ' ) postal_code,
1464 decode(country,'US','',country)
1465 FROM igs_pe_person_addr
1466 WHERE person_id = p_student_data.student_id
1467 AND addr_type
1468 IN (SELECT opt_val
1469 FROM igs_en_nsc_options
1470 WHERE opt_type = 'ADDR_TYPE')
1471 AND (status = 'A'
1472 AND SYSDATE BETWEEN NVL(start_dt,SYSDATE) AND NVL(end_dt,SYSDATE+1))
1473 ORDER BY start_dt DESC;
1474
1475
1476 CURSOR c_priv_block IS
1477 SELECT 'Y'
1478 FROM igs_pe_priv_level pl,igs_en_nsc_options op
1479 WHERE pl.person_id = p_student_data.student_id
1480 AND to_char(pl.data_group_id)=op.opt_val
1481 AND op.opt_type = 'BLK_IND'
1482 AND pl.start_date <= SYSDATE
1483 AND pl.end_date IS NULL;
1484
1485 -- Cursor returns the most recent compeltion period and year from all program attempts
1486
1487 CURSOR c_program_data IS
1488 SELECT nominated_completion_yr ,
1489 nominated_completion_perd
1490 FROM igs_en_stdnt_ps_att_all
1491 WHERE person_id = p_student_data.student_id
1492 AND cal_type = p_acad_cal_type
1493 AND course_attempt_status IN ('COMPLETED', 'ENROLLED', 'INACTIVE', 'INTERMIT')
1494 ORDER BY NVL(nominated_completion_yr,-1) desc, decode(nominated_completion_perd,'E',1,'S',2,'M',3,0) desc;
1495
1496
1497 BEGIN
1498
1499 IF (g_debug_mode) THEN
1500 Put_Debug_Msg('| Begin Get student data..');
1501 END IF;
1502
1503 -- Get ssn and delete all non numeric characters: c_ssn_num
1504 OPEN c_ssn_num;
1505 FETCH c_ssn_num INTO p_student_data.ssn;
1506 CLOSE c_ssn_num;
1507
1508 IF p_student_data.ssn IS NULL THEN
1509 --SSN not found - don't include into the snapshot
1510 IF (g_debug_mode) THEN
1511 Put_Debug_Msg('| SSN not found - return');
1512 END IF;
1513 RETURN;
1514 END IF;
1515
1516 -- Delete all non numeric characters
1517 p_student_data.ssn := process_ssn (p_student_data.ssn);
1518
1519 IF (g_debug_mode) THEN
1520 Put_Debug_Msg('| SSN processed');
1521 END IF;
1522
1523 -- Get f_name, birth_date, m_name, l_name, suffix: c_name
1524 IF (g_debug_mode) THEN
1525 Put_Debug_Msg('| Processing Name');
1526 END IF;
1527 OPEN c_name;
1528 FETCH c_name INTO p_student_data.l_name,
1529 p_student_data.f_name,
1530 p_student_data.m_name,
1531 p_student_data.suffix,
1532 p_student_data.birth_date;
1533 CLOSE c_name;
1534 IF (g_debug_mode) THEN
1535 IF length(p_student_data.l_name) > 80 then
1536 Put_Debug_Msg('Last name :'|| p_student_data.l_name ||'is too long, data truncated to 80 characters on report ');
1537 end if;
1538
1539 IF length(p_student_data.f_name) > 80 then
1540 Put_Debug_Msg('First name : '||p_student_data.f_name ||'is too long, data truncated to 80 characters on report ');
1541 end if;
1542
1543 END IF;
1544
1545
1546 -- Get prev_ssn and delete all non numeric characters: c_prev_ssn_num
1547 OPEN c_prev_ssn_num;
1548 FETCH c_prev_ssn_num INTO p_student_data.prev_ssn;
1549 CLOSE c_prev_ssn_num;
1550
1551
1552 -- Delete all non numeric characters
1553 p_student_data.prev_ssn := process_ssn (p_student_data.prev_ssn);
1554
1555 IF (g_debug_mode) THEN
1556 Put_Debug_Msg('| Previous SSN processed');
1557 END IF;
1558
1559
1560 -- Get prev_l_name: c_prev_last_name
1561 OPEN c_prev_last_name;
1562 FETCH c_prev_last_name INTO p_student_data.prev_l_name;
1563 CLOSE c_prev_last_name;
1564
1565 IF (g_debug_mode) THEN
1566 Put_Debug_Msg('| Previous last name processed');
1567 IF length(p_student_data.prev_l_name) > 80 then
1568 Put_Debug_Msg('previous Last name :'|| p_student_data.prev_l_name ||'is too long, data truncated to 80 characters on report ');
1569 end if;
1570
1571 END IF;
1572 -- Get enr_status and start date (if any):
1573 IF (g_debug_mode) THEN
1574 Put_Debug_Msg('| Calculating enrollment status..');
1575 END IF;
1576
1577 p_student_data.enr_status := Get_Enrollment_Status (
1578 p_snapshot_id => p_snapshot_id,
1579 p_student_id => p_student_data.student_id,
1580 p_load_cal_type => p_load_cal_type,
1581 p_load_cal_seq => p_load_cal_seq,
1582 p_acad_cal_type => p_acad_cal_type,
1583 p_acad_cal_seq => p_acad_cal_seq,
1584 p_prev_snapshot_id => p_prev_snapshot_id,
1585 x_credit_points => l_credit_points,
1586 x_status_date => p_student_data.status_date,
1587 x_grad_level => p_student_data.grad_level,
1588 x_ant_grad_date => l_ant_grad_date,
1589 p_student_data => l_student_data,
1590 p_nslc_condition =>p_nslc_condition
1591 );
1592
1593 IF (g_debug_mode) THEN
1594 Put_Debug_Msg('| Enrollment status: '||p_student_data.enr_status||' date: '||p_student_data.status_date );
1595 END IF;
1596
1597 -- if falling below full time then derive status change date based on previous snapshot value
1598 IF p_prev_snapshot_id IS NOT NULL AND p_student_data.enr_status IN ('L','H') THEN
1599
1600 --Get previous status if current is H or L and snapshot is provided.
1601 l_prev_stat := IGS_EN_GS_ATTRIB_VAL.GET_VALUE(1,p_prev_snapshot_id,27,p_student_data.student_id);
1602
1603 -- Get status_date (if reqiured)
1604 IF (g_debug_mode) THEN
1605 Put_Debug_Msg('| Previous enrollemt status found: '||l_prev_stat);
1606 END IF;
1607
1608 IF (p_student_data.enr_status = 'L' AND l_prev_stat IN ('F','H') )
1609 OR (p_student_data.enr_status = 'H' AND l_prev_stat ='F') THEN
1610
1611 --Status has been chaged - get the date
1612 IF (g_debug_mode) THEN
1613 Put_Debug_Msg('| Status change date required, calculating..');
1614 END IF;
1615
1616 p_student_data.status_date := Get_Status_Change_Date (
1617 p_student_id => p_student_data.student_id,
1618 p_old_status => l_prev_stat,
1619 p_new_status => p_student_data.enr_status,
1620 p_load_cal_type => p_load_cal_type,
1621 p_load_cal_seq => p_load_cal_seq,
1622 p_acad_cal_type => p_acad_cal_type,
1623 p_acad_cal_seq => p_acad_cal_seq);
1624
1625
1626 IF (g_debug_mode) THEN
1627 Put_Debug_Msg('| Done, date is '||p_student_data.status_date);
1628 END IF;
1629
1630 END IF;
1631
1632 END IF;
1633
1634 -- Get addr1,addr2,city,state,zip,country: c_address
1635 IF (g_debug_mode) THEN
1636 Put_Debug_Msg('Processing address');
1637 END IF;
1638 OPEN c_address;
1639 FETCH c_address INTO p_student_data.addr1 ,
1640 p_student_data.addr2 ,
1641 p_student_data.city ,
1642 p_student_data.state ,
1643 p_student_data.zip ,
1644 p_student_data.country ;
1645 CLOSE c_address;
1646 IF (g_debug_mode) THEN
1647 IF length(p_student_data.addr1) > 100 then
1648 Put_Debug_Msg('Address Line 1 is too long, data truncated to 100 characters on report ');
1649 end if;
1650
1651 IF length(p_student_data.addr2) > 100 then
1652 Put_Debug_Msg('Address Line 2 is too long, data truncated to 100 characters on report ');
1653 end if;
1654 IF length(p_student_data.zip) > 10 then
1655 Put_Debug_Msg('Zip code is too long, data truncated to 10 characters on report ');
1656 end if;
1657
1658 IF length(p_student_data.state) > 2 then
1659 Put_Debug_Msg('State is too long. Please change to 2 character state code and rerun the report');
1660 RAISE FND_API.G_EXC_ERROR;
1661 end if;
1662
1663
1664 END IF;
1665 IF p_student_data.enr_status IN ('F','H','A') THEN
1666
1667 -- Get graduation date
1668
1669 IF (g_debug_mode) THEN
1670 Put_Debug_Msg('| Calculating graduation date..');
1671 END IF;
1672
1673 p_student_data.grad_date := l_ant_grad_date;
1674
1675 IF (g_debug_mode) THEN
1676 Put_Debug_Msg('| Done, Date is: '||p_student_data.grad_date );
1677 END IF;
1678
1679 END IF;
1680
1681 -- Get term_s_date, term_e_date: c_cal_data
1682
1683 OPEN c_cal_data(p_load_cal_type,p_load_cal_seq);
1684 FETCH c_cal_data INTO p_student_data.term_s_date ,
1685 p_student_data.term_e_date ,
1686 l_code;
1687 CLOSE c_cal_data;
1688
1689 -- No need to set grad_level -it's NULL for the current release
1690
1691 p_student_data.grad_level :=p_student_data.grad_level;
1692
1693 -- Get data_block: c_priv_block
1694
1695 OPEN c_priv_block;
1696 FETCH c_priv_block INTO p_student_data.data_block ;
1697
1698 --Check if notfound - then privacy block not requested
1699
1700 IF c_priv_block%NOTFOUND THEN
1701 p_student_data.data_block := 'N';
1702 END IF;
1703
1704 CLOSE c_priv_block;
1705
1706 IF (g_debug_mode) THEN
1707 Put_Debug_Msg('| Get student data exit' );
1708 END IF;
1709
1710 END Get_Student_Data;
1711
1712 /******************************************************************************/
1713
1714 FUNCTION Get_Enrollment_Status (
1715 p_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
1716 p_student_id IN igs_pe_person.person_id%TYPE ,
1717 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
1718 p_load_cal_seq IN igs_ca_inst.sequence_number%TYPE,
1719 p_acad_cal_type IN igs_ca_inst.cal_type%TYPE,
1720 p_acad_cal_seq IN igs_ca_inst.sequence_number%TYPE,
1721 p_prev_snapshot_id IN igs_en_doc_instances.doc_inst_id%TYPE,
1722 x_credit_points OUT NOCOPY NUMBER,
1723 x_status_date OUT NOCOPY DATE,
1724 x_grad_level OUT NOCOPY VARCHAR2,
1725 x_ant_grad_date OUT NOCOPY DATE,
1726 p_student_data IN OUT NOCOPY student_data_type,
1727 p_nslc_condition IN varchar2
1728 ) RETURN VARCHAR2 IS
1729 -------------------------------------------------------------------------------------------
1730 --Change History:
1731 --Who When What
1732 --kkillams 28-04-2003 Modified the c_outcome_dt cursor where clause due to change in
1733 -- pk of student unit attempt w.r.t. bug number 2829262
1734 -- rnirwani 16-Sep-2004 changed cursor cur_intermission_details,c_latest_intrm (Enrp_Get_Sca_Elgbl) to not consider logically deleted records Bug# 3885804
1735 --svanukur 30-dec-2004 BUG 4095287 :Modified the function get_enrollment_status to check for the
1736 -- attendance type only if the student's enrollment status is not 'A', 'G' or 'W'.
1737 -- BUG 4095278 : passed c_stud_courses_rec.course_cd as parameter to the cursor
1738 -- c_outcome_dt, so that the outcome date of the correspondance program is fetched.
1739 --somasekar 13-apr-2005 bug# 4179106 modified to neglect the transfer cancelled programs also
1740 -- smaddali 11-aug-2006 Modified logic for bug#5440823
1741 -------------------------------------------------------------------------------------------
1742 l_A_prev_term_s_dt DATE;
1743 l_A_prev_term_e_dt DATE;
1744 l_A_stat_F BOOLEAN := FALSE;
1745 l_W_stat BOOLEAN := FALSE;
1746 l_A_stat BOOLEAN := FALSE;
1747 l_D_stat BOOLEAN := FALSE;
1748 l_G_stat BOOLEAN := FALSE;
1749 l_s_date DATE;
1750 l_e_date DATE;
1751 l_load_term_cd VARCHAR2(10);
1752 l_W_date DATE;
1753 l_G_date DATE; -- For deceased date
1754 l_A_date DATE;
1755 l_temp_date DATE;
1756 l_A_temp_date DATE;
1757 l_interm_start_dt DATE;
1758 l_tot_points NUMBER := 0;
1759 l_tot_eftsu NUMBER := 0;
1760 l_eftsu_points NUMBER := 0;
1761 l_credit_points NUMBER := 0;
1762 l_stat VARCHAR2(1);
1763 l_enr_status VARCHAR2(5);
1764 l_temp_ant_grad_date DATE;
1765 l_interm_end_dt DATE;
1766 l_study_another_inst VARCHAR2(1);
1767
1768 l_A_prev_stat VARCHAR2(1);
1769 l_grad_date DATE;
1770 l_W_temp_date DATE;
1771 l_lp_temp_date DATE;
1772 l_message_name VARCHAR2(100);
1773 l_attendance_type igs_en_stdnt_ps_att_all.attendance_type%TYPE;
1774 l_code VARCHAR2(3);
1775 l_career_profile VARCHAR2(1);
1776
1777 CURSOR c_enr_status (l_status VARCHAR2) IS
1778 SELECT substr(opt_type,10,1)
1779 FROM igs_en_nsc_options
1780 WHERE opt_val = l_status
1781 AND opt_type IN
1782 ('ENR_STAT_F','ENR_STAT_H','ENR_STAT_L');
1783
1784 /* For deceased date..cursor is changed from igs_pe_person to underlying tables */
1785 CURSOR c_decease_date IS
1786 SELECT NVL(pp.date_of_death,SYSDATE)
1787 FROM igs_pe_hz_parties pd, hz_person_profiles pp
1788 WHERE DECODE(pp.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y')='Y'
1789 AND pp.party_id = p_student_id
1790 AND pp.party_id = pd.party_id (+)
1791 AND pp.content_source_type = 'USER_ENTERED'
1792 AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
1793
1794 /* Intermission */
1795
1796 CURSOR c_interm_date (l_c_course_cd VARCHAR2,p_student_id NUMBER,p_s_date DATE,p_e_date DATE) IS
1797 SELECT spi.start_dt,spi.end_dt,spi.intermission_type
1798 FROM igs_en_stdnt_ps_intm spi,igs_en_intm_types spt
1799 WHERE spi.person_id = p_student_id
1800 AND spi.course_cd = l_c_course_cd
1801 AND spi.intermission_type = spt.intermission_type
1802 AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
1803 AND (
1804 (spt.APPR_REQD_IND = 'Y' AND spi.approved = 'Y')
1805 OR
1806 spt.APPR_REQD_IND = 'N'
1807 )
1808 AND(
1809 ( spi.start_dt <= p_e_date AND spi.end_dt >= p_e_date)
1810 OR
1811 ( spi.start_dt <= p_e_date AND spi.end_dt >= SYSDATE AND spi.end_dt >= p_s_date)
1812 )
1813 ORDER by 2 DESC;
1814
1815 l_interm_type VARCHAR2(25);
1816
1817
1818 /* Cursor to find whether the student is studying at another institution or not */
1819
1820 CURSOR c_interm_date_study (l_intm_type VARCHAR2) IS
1821 SELECT 'x'
1822 FROM igs_en_intm_types
1823 WHERE intermission_type = l_intm_type
1824 AND study_antr_inst_ind = 'Y'
1825 AND APPR_REQD_IND = 'Y';
1826
1827 l_study_at_another_inst VARCHAR2(1);
1828
1829
1830 /* For getting conferral date */
1831
1832 CURSOR c_conferral_dt(p_course_cd VARCHAR,p_s_date DATE,p_e_date DATE) IS
1833 SELECT spaa.conferral_date
1834 FROM igs_gr_graduand_all gr,igs_en_spa_awd_aim spaa
1835 WHERE gr.person_id = p_student_id
1836 AND gr.course_cd = p_course_cd
1837 AND gr. graduand_status IN
1838 ( SELECT graduand_status
1839 FROM igs_gr_stat
1840 WHERE s_graduand_status = 'GRADUATED'
1841 )
1842 AND gr.person_id = spaa.person_id AND gr.course_cd = spaa.course_cd AND gr.award_cd = spaa.award_cd
1843 AND spaa.conferral_date BETWEEN p_s_date AND p_e_date;
1844
1845
1846
1847 /* To check whether all the programs of the student are of type GRADUATE i.e required for Graduate Level Indicator Field */
1848
1849 CURSOR c_grad_lvl(p_course_cd VARCHAR2, p_version_number NUMBER) IS
1850 SELECT 'x'
1851 FROM igs_en_nsc_options
1852 WHERE opt_type='GR_LVL_IND'
1853 AND opt_val IN (SELECT course_type_group_cd FROM igs_ps_type
1854 WHERE course_type = ( SELECT course_type
1855 FROM igs_ps_ver_all
1856 WHERE course_cd = p_course_cd
1857 AND version_number = p_version_number
1858 )
1859 );
1860
1861 c_grad_lvl_rec c_grad_lvl%ROWTYPE;
1862
1863
1864
1865 /* Checking whether the student is enrolled in any correspondence programs */
1866
1867 CURSOR c_corresp_prg(p_course_cd VARCHAR2, p_version_number NUMBER) IS
1868 SELECT 'x'
1869 FROM igs_en_nsc_options
1870 WHERE opt_type='CO_PRG_TG'
1871 AND opt_val IN (SELECT course_type_group_cd FROM igs_ps_type
1872 WHERE course_type = (SELECT course_type
1873 FROM igs_ps_ver_all
1874 WHERE course_cd = p_course_cd
1875 AND version_number=p_version_number
1876 )
1877 );
1878
1879 l_corresp_prg VARCHAR2(1);
1880
1881
1882 /* Checking for assessment items */
1883 -- smaddali modified for performance bug#4914052
1884 CURSOR c_outcome_dt(p_corresp_prg VARCHAR2) IS
1885 SELECT DISTINCT asv.outcome_dt
1886 FROM igs_as_su_atmpt_itm asv, igs_en_su_attempt asav,
1887 igs_en_stdnt_ps_att pr,igs_ps_ver vers,igs_ca_load_to_teach_v lt
1888 WHERE asv.person_id = asav.person_id
1889 AND asv.course_cd = asav.course_cd
1890 AND asv.uoo_id = asav.uoo_id
1891 AND asav.person_id = pr.person_id
1892 AND asav.course_cd = pr.course_cd
1893 AND pr.course_cd = vers.course_cd
1894 AND pr.version_number = vers.version_number
1895 AND asv.person_id = p_student_id
1896 AND asav.course_cd = p_corresp_prg
1897 AND asv.logical_delete_dt IS NULL
1898 AND lt.teach_cal_type = asav.cal_type
1899 AND lt.teach_ci_sequence_number = asav.ci_sequence_number
1900 AND lt.load_cal_type = p_load_cal_type
1901 AND lt.load_ci_sequence_number = p_load_cal_seq
1902 AND pr.cal_type = p_acad_cal_type
1903 AND pr.course_attempt_status IN ('COMPLETED', 'ENROLLED', 'INACTIVE', 'INTERMIT','DISCONTIN','LAPSED')
1904 AND asav.unit_attempt_status IN ('DISCONTIN','DROPPED')
1905 AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
1906 ORDER by 1 DESC;
1907
1908 l_outcome_dt DATE;
1909 c_outcome_dt_rec c_outcome_dt%ROWTYPE;
1910
1911
1912 /* List of all course attempts for the person.
1913 The cursor fetches all course attemmpts if run in standard mode
1914 and only completed program attempts if run in non standard
1915 Also takes into account the career profile of the university*/
1916
1917 CURSOR c_stud_courses(cp_nslc_condition VARCHAR2,cp_career_profile VARCHAR2,cp_grad_type VARCHAR2,p_s_date DATE,p_e_date DATE) IS
1918 SELECT pr.course_cd,
1919 pr.discontinued_dt disc_dt,
1920 pr.course_rqrmnts_complete_dt comp_dt,
1921 pr.version_number vers,
1922 pr.course_attempt_status status,
1923 pr.lapsed_dt lapsed_dt
1924 FROM igs_en_stdnt_ps_att pr,
1925 igs_ps_ver vers
1926 WHERE pr.person_id = p_student_id
1927 AND pr.cal_type = p_acad_cal_type
1928 AND
1929 (
1930 (cp_nslc_condition = '3' AND discontinued_dt BETWEEN p_s_date AND p_e_date)
1931 OR
1932 (cp_nslc_condition IN ('1','3') AND cp_grad_type = 'CRS_RQMNTS_COMPL_DATE'
1933 AND course_rqrmnts_complete_dt BETWEEN p_s_date AND p_e_date)
1934 OR
1935 (cp_nslc_condition = '3' AND lapsed_dt BETWEEN p_s_date AND p_e_date)
1936 OR
1937 (cp_nslc_condition = '3' AND course_attempt_status = 'INTERMIT'
1938 And (EXISTS ( SELECT spi.person_id,spi.course_cd
1939 FROM igs_en_stdnt_ps_intm spi
1940 WHERE spi.person_id = pr.person_id
1941 AND spi.course_cd = pr.course_cd
1942 AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
1943 AND(
1944 ( spi.start_dt <= p_e_date AND spi.end_dt >=p_e_date)
1945 OR
1946 ( spi.start_dt <= p_e_date AND spi.end_dt >= SYSDATE AND spi.end_dt >= p_s_date)
1947 )
1948 )
1949 )
1950 )
1951 OR
1952 (cp_nslc_condition in( '2','3') AND
1953 ( cp_grad_type = 'CONFERRAL_DATE' AND
1954 (EXISTS (select gr.person_id
1955 FROM igs_gr_graduand_all gr,igs_en_spa_awd_aim spaa, igs_gr_stat grs
1956 WHERE gr.person_id = pr.person_id and
1957 gr.graduand_status =grs. graduand_status
1958 AND grs. s_graduand_status = 'GRADUATED'
1959 AND gr.person_id = spaa.person_id AND gr.course_cd = spaa.course_cd
1960 AND pr.person_id = spaa.person_id
1961 AND pr.course_cd = spaa.course_cd
1962 AND gr.award_cd = spaa.award_cd
1963 AND spaa.conferral_date BETWEEN p_s_date AND p_e_date)
1964 )
1965 )
1966 )
1967 OR
1968 (cp_nslc_condition = '3'
1969 AND course_attempt_status IN('ENROLLED','DISCONTIN', 'LAPSED','COMPLETED','INACTIVE')
1970 AND (EXISTS (SELECT 'X' FROM igs_en_su_attempt b, igs_ca_load_to_teach_v lod
1971 WHERE b.person_id = pr.person_id
1972 AND b.course_cd = pr.course_cd
1973 AND b.cal_type = lod.teach_cal_type
1974 AND b.ci_sequence_number = lod.teach_ci_sequence_number
1975 AND lod.load_cal_type = p_load_cal_type
1976 AND lod.load_ci_sequence_number = p_load_cal_seq
1977 and b.unit_attempt_status <> 'UNCONFIRM'
1978 )
1979 )
1980 )
1981 ) AND pr.future_dated_trans_flag NOT IN('Y', 'C')
1982 AND pr.course_cd = vers.course_cd
1983 AND vers.version_number = pr.version_number
1984 AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
1985 AND (
1986 (cp_career_profile = 'Y' AND igs_en_spa_terms_api.get_spat_primary_prg(p_student_id, pr.course_cd,p_load_cal_type,p_load_cal_seq) = 'PRIMARY')
1987 OR
1988 (cp_career_profile = 'N')
1989 );
1990
1991 /* Previous Intermission */
1992 l_interm_prv_start_dt igs_en_stdnt_ps_intm.start_dt%TYPE;
1993 l_interm_prv_end_dt igs_en_stdnt_ps_intm.end_dt%TYPE;
1994
1995 --fetch the latest intermission of a student
1996 CURSOR c_latest_intrm(cp_course_cd VARCHAR2,cp_wthdrn_dt DATE) IS
1997 SELECT sci.start_dt , sci.end_dt
1998 FROM igs_en_stdnt_ps_intm sci,
1999 IGS_EN_INTM_TYPES eit
2000 WHERE sci.person_id = p_student_id
2001 AND sci.course_cd = cp_course_cd
2002 AND sci.end_dt <= cp_wthdrn_dt
2003 AND sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
2004 AND sci.approved = eit.appr_reqd_ind
2005 AND eit.intermission_type = sci.intermission_type
2006 ORDER BY end_dt DESC;
2007
2008 /*fetch unit attempts that were discntinued between teh student's
2009 latest intermission and the program discontinuation/ lapsed date. */
2010
2011 CURSOR c_sua_disc(cp_course_cd VARCHAR2,cp_intrm_end_dt DATE, cp_wthdrn_dt DATE) IS
2012 SELECT uoo_id, cal_type , ci_sequence_number, discontinued_dt ,administrative_unit_status,
2013 unit_attempt_status, no_assessment_ind
2014 FROM igs_En_Su_Attempt
2015 WHERE person_id = p_student_id
2016 AND course_cd = cp_course_cd
2017 AND unit_attempt_status = 'DISCONTIN'
2018 AND discontinued_dt BETWEEN cp_intrm_end_dt AND cp_wthdrn_dt;
2019
2020 l_load_incur VARCHAR2(1);
2021
2022 l_start_date VARCHAR2(20);
2023 l_start_year VARCHAR2(10);
2024 l_non_grad_lvl VARCHAR2(1);
2025 cst_active CONSTANT igs_ca_stat.s_cal_status%TYPE := 'ACTIVE';
2026 cst_load CONSTANT igs_ca_type.s_cal_cat%TYPE := 'LOAD';
2027 l_valid_pgm VARCHAR2(1);
2028
2029 --fetch start dateof the academic calendar for which the job is run
2030 CURSOR cur_strt_date(cp_cal_type igs_ca_inst.cal_type%TYPE, cp_seq_number igs_ca_inst.sequence_number%TYPE) is
2031 SELECT to_char(start_dt,'mm-dd-yyyy')
2032 FROM igs_ca_inst
2033 WHERE cal_type = cp_cal_type
2034 AND sequence_number = cp_seq_number;
2035
2036 -- fetch all acad calendars that start or end in the calendar year of the
2037 -- acad cal for which the job is run .
2038 CURSOR acad_cal(cp_start_year VARCHAR2
2039 ) IS
2040 SELECT ci.cal_type, ci.SEQUENCE_NUMBER
2041 FROM igs_ca_type ct,
2042 igs_ca_inst ci,
2043 igs_ca_stat cs
2044 WHERE ci.cal_type = ct.cal_type
2045 AND ct.s_cal_cat = 'ACADEMIC'
2046 AND ct.closed_ind = 'N'
2047 AND ci.cal_status = cs.cal_status
2048 AND cs.s_cal_status = cst_active
2049 AND (
2050 (substr(to_char(start_dt, 'mm-dd-yyyy') ,7) = cp_start_year)
2051 OR(substr(to_char(end_dt, 'mm-dd-yyyy') ,7) = cp_start_year)
2052 );
2053
2054 --for all acad calendars that are in teh calendar year, get teh subordinate load cals.
2055 CURSOR c_load_cal_instance(
2056 cp_cal_type IGS_CA_INST.cal_type%TYPE,
2057 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
2058 SELECT ci.cal_type,
2059 ci.sequence_number
2060 FROM igs_ca_type ct,
2061 igs_ca_inst ci,
2062 igs_ca_stat cs,
2063 igs_ca_inst_rel cir
2064 WHERE ct.closed_ind = 'N' AND
2065 cs.s_cal_status = cst_active AND
2066 ci.cal_status = cs.cal_status AND
2067 ct.s_cal_cat = cst_load AND
2068 ci.cal_type = ct.cal_type AND
2069 ci.cal_type = cir.sub_cal_type AND
2070 ci.sequence_number = cir.sub_ci_sequence_number AND
2071 cir.sup_cal_type = cp_cal_type AND
2072 cir.sup_ci_sequence_number = cp_sequence_number;
2073
2074 --fetch course that have unit attempts in the term .
2075 -- smaddali modified cursor form perf bug#4914052
2076 CURSOR cur_spa(cp_acad_cal_type igs_ca_inst.cal_type%TYPE,cp_load_cal_type igs_ca_inst.cal_type%TYPE,
2077 cp_load_cal_seq igs_ca_inst.sequence_number%TYPE) IS
2078 SELECT DISTINCT spa.course_cd, spa.version_number
2079 FROM igs_en_su_Attempt sua,igs_en_stdnt_ps_Att spa ,
2080 igs_ca_load_to_teach_v lod
2081 WHERE spa.person_id = p_student_id
2082 AND spa.cal_type = cp_acad_cal_type
2083 AND spa.person_id = sua.person_id
2084 AND spa.course_cd = sua.course_cd
2085 AND sua.cal_type = lod.teach_cal_type
2086 AND sua.ci_sequence_number = lod.teach_ci_sequence_number
2087 AND lod.load_cal_type = cp_load_cal_type
2088 AND lod.load_ci_sequence_number = cp_load_cal_seq;
2089
2090 -- smaddali added these cursors for but#5440823
2091 CURSOR c_other_sua ( cp_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE ) IS
2092 SELECT sua.discontinued_dt
2093 FROM igs_en_su_attempt sua
2094 WHERE sua.person_id = p_student_id
2095 AND sua.course_cd = cp_course_cd
2096 AND sua.unit_attempt_status NOT IN ('DROPPED','DISCONTIN')
2097 AND (sua.cal_type,sua.ci_sequence_number) IN
2098 ( SELECT teach_cal_type,teach_ci_sequence_number FROM igs_ca_load_to_teach_v
2099 WHERE load_cal_type = p_load_cal_type AND load_ci_sequence_number = p_load_cal_seq
2100 );
2101 c_other_sua_rec c_other_sua%ROWTYPE;
2102
2103 CURSOR c_disc_units(cp_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE) IS
2104 SELECT sua.discontinued_dt
2105 FROM igs_En_Su_Attempt sua
2106 WHERE person_id = p_student_id
2107 AND course_cd = cp_course_cd
2108 AND unit_attempt_status IN ( 'DROPPED','DISCONTIN')
2109 AND sua.no_assessment_ind = 'N'
2110 AND (sua.cal_type,sua.ci_sequence_number) IN
2111 ( SELECT teach_cal_type,teach_ci_sequence_number FROM igs_ca_load_to_teach_v
2112 WHERE load_cal_type = p_load_cal_type AND load_ci_sequence_number = p_load_cal_seq )
2113 ORDER BY sua.discontinued_dt DESC ;
2114 c_disc_units_rec c_disc_units%ROWTYPE;
2115 l_inac_temp_date DATE;
2116
2117 BEGIN
2118
2119 l_career_profile := NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N');
2120
2121 -- Check for deceased status first,as this status overrides all statuses
2122 -- If Decease is active
2123 -- fetch date and RETURN;
2124 OPEN c_decease_date;
2125 FETCH c_decease_date INTO l_G_date;
2126 IF c_decease_date%FOUND THEN
2127 -- Person is deceased
2128 IF (g_debug_mode) THEN
2129 Put_Debug_Msg('| Returning D status and date: '||l_G_date);
2130 END IF;
2131 CLOSE c_decease_date;
2132 x_status_date := l_G_date;
2133 g_counts(17):=g_counts(17)+1; --Increment status specific count
2134 RETURN 'D';
2135 END IF;
2136
2137 CLOSE c_decease_date;
2138
2139
2140 -- Get term dates
2141 OPEN c_cal_data(p_load_cal_type,p_load_cal_seq);
2142 FETCH c_cal_data INTO l_s_date ,
2143 l_e_date ,
2144 l_load_term_cd;
2145 CLOSE c_cal_data;
2146
2147
2148 IF (g_debug_mode) THEN
2149 Put_Debug_Msg('| Calculating enrollment status for student: '||p_student_id);
2150 END IF;
2151
2152 -- Loop through all courses for the given acad instance and student.
2153 FOR c_stud_courses_rec IN c_stud_courses(p_nslc_condition,l_career_profile,l_grad_type,l_s_date, l_e_date) LOOP
2154
2155 --check if pragram is valid
2156 l_valid_pgm := 'N';
2157 IF t_valid_pgms.count > 0 THEN
2158 FOR i in 1 .. t_valid_pgms.count LOOP
2159 IF t_valid_pgms(i) = rpad(c_stud_courses_rec.course_cd,6,' ')||'-'||c_stud_courses_rec.vers THEN
2160 l_valid_pgm := 'Y';
2161 EXIT;
2162 END IF;
2163 END LOOP;
2164 END IF;
2165
2166
2167 IF l_valid_pgm = 'Y' THEN
2168
2169 -- Get current EFTSU points for each course and summarize , including research units.
2170 l_eftsu_points := IGS_EN_PRC_LOAD.ENRP_CLC_EFTSU_TOTAL(
2171 p_person_id => p_student_id,
2172 p_course_cd => c_stud_courses_rec.course_cd ,
2173 p_acad_cal_type => p_acad_cal_type ,
2174 p_acad_sequence_number => p_acad_cal_seq,
2175 p_load_cal_type => p_load_cal_type,
2176 p_load_sequence_number => p_load_cal_seq,
2177 p_include_research_ind => 'Y' ,
2178 p_key_course_cd => NULL,
2179 p_key_version_number => NULL,
2180 p_credit_points => l_credit_points );
2181
2182
2183 IF (g_debug_mode) THEN
2184 Put_Debug_Msg('| Calculated points for course: '||c_stud_courses_rec.course_cd ||' eftsu '
2185 ||l_eftsu_points ||' points: '|| l_credit_points||' course stat: '||c_stud_courses_rec.status
2186 ||' compl date: '||c_stud_courses_rec.comp_dt ||' discount date: '|| c_stud_courses_rec.disc_dt);
2187 END IF;
2188
2189 l_tot_eftsu := l_tot_eftsu + NVL(l_eftsu_points,0);
2190 l_tot_points := l_tot_points + NVL(l_credit_points,0);
2191
2192 -- Flags used:
2193 -- l_A_stat_F = Intermission with Full time at another institution
2194 -- l_A_stat = Intermission
2195 IF (NOT l_A_stat_F) THEN
2196 l_interm_start_dt := NULL;
2197 l_interm_end_dt := NULL;
2198 l_interm_type := NULL;
2199 l_A_temp_date := NULL;
2200
2201 -- Step 1. (intermission)
2202 -- Check if an intermission exists which started in the reference term
2203 -- If yes then check that the student is full time at another institute. l_A_stat_F
2204 -- else its a simple intermission l_A_stat.
2205 -- status date = intermission start date.
2206
2207 -- fetch the latest intermission of the student.
2208 OPEN c_interm_date (c_stud_courses_rec.course_cd, p_student_id, l_s_date, l_e_date);
2209 FETCH c_interm_date INTO l_interm_start_dt, l_interm_end_dt, l_interm_type;
2210 CLOSE c_interm_date;
2211
2212 IF (g_debug_mode) THEN
2213 Put_Debug_Msg('| Intermission date calculated current: '||l_interm_start_dt);
2214 END IF;
2215
2216 /* Check if the student is studying in another institution.
2217 If yes then return the enrollment status as 'F' */
2218 IF l_interm_start_dt IS NOT NULL THEN
2219 OPEN c_interm_date_study(l_interm_type);
2220 FETCH c_interm_date_study INTO l_study_another_inst;
2221 IF c_interm_date_study%FOUND THEN
2222 --implies student is studying at another inst hence set the status to 'F'
2223 l_A_stat_F := TRUE;
2224 ELSE
2225 --implies student is on intermission set the status to 'A'
2226 l_A_stat := TRUE;
2227 l_A_temp_date := l_interm_start_dt;
2228 END IF;
2229 CLOSE c_interm_date_study;
2230 END IF;
2231
2232 IF l_A_date IS NULL OR (l_A_temp_date IS NOT NULL AND l_A_temp_date > l_A_date) THEN
2233 l_A_date := l_A_temp_date; --Store the date
2234 END IF;
2235
2236
2237 IF ( NOT l_A_stat AND c_stud_courses_rec.status = 'COMPLETED') THEN
2238
2239 -- Step 2.(Completed)
2240 -- Based up NSLC setup completion date will be
2241 ---- Course completion date
2242 ---- Conferral date.
2243 -- set l_G_Stat
2244
2245 /* Graduated status (G): COMPLETED If Completion date lies within calendar instance dates return Latest completion date */
2246 /* Pickup the graduation date based on the value set in the configuration form */
2247 l_grad_date := NULL;
2248 IF (l_grad_type = 'CRS_RQMNTS_COMPL_DATE' AND
2249 (c_stud_courses_rec.comp_dt BETWEEN l_s_date AND l_e_date)) THEN
2250 l_grad_date := c_stud_courses_rec.comp_dt;
2251 ELSIF (l_grad_type = 'CONFERRAL_DATE') THEN
2252 OPEN c_conferral_dt (c_stud_courses_rec.course_cd,l_s_date,l_e_date);
2253 FETCH c_conferral_dt INTO l_grad_date;
2254 CLOSE c_conferral_dt;
2255 END IF;
2256
2257 IF l_G_date IS NULL OR (l_grad_date IS NOT NULL AND l_G_date < l_grad_date) THEN
2258 IF (g_debug_mode) THEN
2259 Put_Debug_Msg('| Setting completion date');
2260 END IF;
2261 --Store the completion date
2262 l_G_date := l_grad_date;
2263
2264 END IF;
2265
2266 --set the 'G' status
2267 IF l_G_date IS NOT NULL THEN
2268 l_G_stat := TRUE;
2269 END IF;
2270
2271 -- step 3.1 (withdrawn) when status is discontinued.
2272 -- if student has an earlier intermission
2273 ---- If load was not incurred for any unit since the intermission end date
2274 ---- then withdrawn date will be equal to intermission start date.
2275 -- if student has corresspondence programs then equal to their outcome date.
2276 -- if no date set then equal to discontinuation date.
2277
2278 ELSIF (NOT l_A_stat AND NOT l_G_stat AND
2279 (c_stud_courses_rec.status ='DISCONTIN' AND c_stud_courses_rec.disc_dt
2280 BETWEEN l_s_date AND l_e_date))
2281
2282 THEN
2283 l_W_temp_date := NULL;
2284 /*Withdrawn status (W): DISCONTIN Withdrawn date within calendar instance dates
2285 return latest withdrawn date */
2286
2287 /* Checking for correspondence programs and discontinued */
2288 --check if the student had a previous intermission
2289 OPEN c_latest_intrm(c_stud_courses_rec.course_cd,c_stud_courses_rec.disc_dt);
2290 FETCH c_latest_intrm INTO l_interm_prv_start_dt,l_interm_prv_end_dt;
2291 IF c_latest_intrm%FOUND THEN
2292
2293 --student had previous intermission prior to discontinuation; chk for discontinued date
2294 --of units . If it falls between the intermission end date and prgm disc date
2295 l_load_incur := 'N';
2296 FOR c_sua_disc_rec IN c_sua_disc(c_stud_courses_rec.course_cd,l_interm_prv_end_dt,
2297 c_stud_courses_rec.disc_dt) LOOP
2298
2299 --check if even a single unit incurred load; if yes it implies the student returned
2300 --from intermission.
2301 --If no unit incurred load, implies there was no enrollment activity after intermission, hence student
2302 --has not retnd from intermission
2303
2304 IF IGS_EN_PRC_LOAD.ENRP_GET_LOAD_INCUR(p_cal_type =>c_sua_disc_rec.cal_type ,
2305 p_sequence_number => c_sua_disc_rec.ci_sequence_number,
2306 p_discontinued_dt =>c_sua_disc_rec.discontinued_dt,
2307 p_administrative_unit_status => c_sua_disc_rec.administrative_unit_status ,
2308 p_unit_attempt_status => c_sua_disc_rec.unit_attempt_status,
2309 p_no_assessment_ind => c_sua_disc_rec.no_assessment_ind,
2310 p_load_cal_type => p_load_cal_type,
2311 p_load_sequence_number =>p_load_cal_seq,
2312 p_uoo_id => c_sua_disc_rec.uoo_id,
2313 p_include_audit => 'Y') = 'Y' THEN
2314
2315 l_load_incur := 'Y';
2316 EXIT;
2317 END IF;
2318 END LOOP;
2319
2320 IF l_load_incur = 'N' THEN
2321 l_W_temp_date := l_interm_prv_start_dt;
2322 END IF;
2323 END IF;
2324 CLOSE c_latest_intrm;
2325
2326
2327 IF l_W_temp_date IS NULL THEN
2328 ----implies student had no intermission or retnd from intermission hence set the
2329 --'w' date as the discontin date. check for correspondance pgms.
2330 OPEN c_corresp_prg(c_stud_courses_rec.course_cd,c_stud_courses_rec.vers);
2331 FETCH c_corresp_prg INTO l_corresp_prg;
2332 CLOSE c_corresp_prg;
2333
2334 IF l_corresp_prg IS NOT NULL THEN
2335 c_outcome_dt_rec := NULL;
2336 OPEN c_outcome_dt(c_stud_courses_rec.course_cd);
2337 FETCH c_outcome_dt INTO c_outcome_dt_rec ;
2338 CLOSE c_outcome_dt;
2339
2340 IF l_outcome_dt IS NULL OR l_outcome_dt < c_outcome_dt_rec.outcome_dt THEN
2341 l_outcome_dt := nvl(c_outcome_dt_rec.outcome_dt,c_stud_courses_rec.disc_dt);
2342
2343 END IF;
2344
2345
2346 l_W_temp_date := l_outcome_dt;
2347 /* for all other casees return discontinued date */
2348 ELSE
2349 l_W_temp_date := c_stud_courses_rec.disc_dt;
2350 END IF;
2351 END IF;
2352
2353
2354 IF l_W_date IS NULL OR(l_W_temp_date is NOT NULL AND l_W_date < l_W_temp_date ) THEN
2355 IF (g_debug_mode) THEN
2356 Put_Debug_Msg('| Withdrawn date calculated : '||l_W_date);
2357 END IF;
2358 --Store the completion date
2359 l_W_date := l_W_temp_date;
2360 END IF;
2361 --set the W status
2362 IF l_W_date is not null then
2363 l_W_stat := TRUE;
2364 END IF;
2365 l_corresp_prg := NULL;
2366
2367 -- step 3.2 (withdrawn) when status is Lapsed
2368 -- if student has an earlier intermission
2369 ---- If load was not incurred for any unit since the intermission end date
2370 ---- then withdrawn date will be equal to intermission start date.
2371 -- if no date set then equal to Lapsed date.
2372
2373 ELSIF (NOT l_A_stat AND NOT l_G_stat AND
2374 (c_stud_courses_rec.status ='LAPSED' AND c_stud_courses_rec.lapsed_dt BETWEEN l_s_date AND l_e_date)) THEN
2375
2376 l_lp_temp_date := NULL;
2377
2378 --check if the student had a previous intermission
2379 OPEN c_latest_intrm(c_stud_courses_rec.course_cd,c_stud_courses_rec.lapsed_dt);
2380 FETCH c_latest_intrm INTO l_interm_prv_start_dt,l_interm_prv_end_dt;
2381 IF c_latest_intrm%FOUND THEN
2382
2383 --student had previous intermission prior to lapse; chk for discontinued date
2384 --of units . If it falls between the intermission end date and prgm lapse date
2385
2386 l_load_incur := 'N';
2387
2388 FOR c_sua_disc_rec IN c_sua_disc(c_stud_courses_rec.course_cd,l_interm_end_dt,
2389 c_stud_courses_rec.lapsed_dt) LOOP
2390
2391 -- chk if any unit incurred load implying the student has returned from intermission.
2392 IF IGS_EN_PRC_LOAD. ENRP_GET_LOAD_INCUR(p_cal_type =>c_sua_disc_rec.cal_type ,
2393 p_sequence_number => c_sua_disc_rec.ci_sequence_number,
2394 p_discontinued_dt =>c_sua_disc_rec.discontinued_dt,
2395 p_administrative_unit_status => c_sua_disc_rec.administrative_unit_status ,
2396 p_unit_attempt_status => c_sua_disc_rec.unit_attempt_status,
2397 p_no_assessment_ind => c_sua_disc_rec.no_assessment_ind,
2398 p_load_cal_type => p_load_cal_type,
2399 p_load_sequence_number =>p_load_cal_seq,
2400 p_uoo_id => c_sua_disc_rec.uoo_id,
2401 p_include_audit => 'Y') = 'Y' THEN
2402
2403 l_load_incur := 'Y';
2404 EXIT;
2405 END IF;
2406 END LOOP;
2407
2408 IF l_load_incur = 'N' THEN
2409 l_lp_temp_date := l_interm_prv_start_dt;
2410 END IF;
2411 END IF;
2412 CLOSE c_latest_intrm;
2413
2414 --implies student retnd from intermission
2415 IF l_lp_temp_date IS NULL THEN
2416 l_lp_temp_date := c_stud_courses_rec.lapsed_dt;
2417 END IF;
2418
2419
2420
2421 IF l_W_date IS NULL OR l_W_date < l_lp_temp_date THEN
2422 IF (g_debug_mode) THEN
2423 Put_Debug_Msg('| Withdrawn date calculated: '||l_W_date);
2424 END IF;
2425
2426 --Store the completion date
2427 l_W_date := l_lp_temp_date;
2428 END IF;
2429 IF l_W_date is not null then
2430 l_W_stat := TRUE;
2431 END IF;
2432
2433 -- step 3.3 (withdrawn)
2434 -- no enrolled attempts in it.
2435 -- date when the last unit was discontinued
2436
2437 ELSIF (NOT l_A_stat AND NOT l_G_stat ) THEN
2438
2439 l_inac_temp_date := NULL;
2440
2441 -- if there are non audited dropped/discontinued unit attempts in this term
2442 OPEN c_disc_units (c_stud_courses_rec.course_cd);
2443 FETCH c_disc_units INTO c_disc_units_rec;
2444 IF c_disc_units%FOUND THEN
2445 l_inac_temp_date := c_disc_units_rec.discontinued_dt ;
2446 -- if there are no other non dropped unit attempts in this term
2447 OPEN c_other_sua(c_stud_courses_rec.course_cd);
2448 FETCH c_other_sua INTO c_other_sua_rec;
2449 IF c_other_sua%NOTFOUND THEN
2450 IF l_W_date IS NULL OR l_W_date < l_inac_temp_date THEN
2451 -- since all units have been dropped, this program is withdrawn
2452 IF (g_debug_mode) THEN
2453 Put_Debug_Msg('| Inactive Withdrawn date calculated: '||l_inac_temp_date);
2454 END IF;
2455 l_W_date := l_inac_temp_date ;
2456 -- set the withdrawn status
2457 END IF;
2458 IF l_W_date IS NOT NULL THEN
2459 l_W_stat := TRUE;
2460 END IF;
2461 END IF;
2462 CLOSE c_other_sua;
2463 END IF;
2464 CLOSE c_disc_units;
2465
2466
2467 END IF; --end of prgram attempt status conditions
2468
2469 END IF ;/* l_A_stat_F */
2470
2471 /* Anticipated Graduation Date */
2472
2473 l_temp_ant_grad_date := igs_en_gen_015.enrf_drv_cmpl_dt (
2474 p_person_id => p_student_id,
2475 p_course_cd => c_stud_courses_rec.course_cd,
2476 p_achieved_cp => l_credit_points,
2477 p_attendance_type => l_attendance_type,
2478 p_load_cal_type => l_load_cal_type,
2479 p_load_ci_seq_num => l_load_cal_seq,
2480 p_load_ci_alt_code => l_load_term_cd,
2481 p_load_ci_start_dt => l_s_date,
2482 p_load_ci_end_dt => l_e_date,
2483 p_message_name => l_message_name
2484 );
2485
2486
2487 IF l_ant_grad_date IS NULL OR l_ant_grad_date < l_temp_ant_grad_date THEN
2488 l_ant_grad_date := l_temp_ant_grad_date;
2489 END IF;
2490 END IF; -- end of if that checks for valid programs
2491 END LOOP; -- pgms loop
2492
2493 /* Graduate Level Indicator */
2494 -- A student is sonsidered as graduate if all his prpgram attempts in the
2495 --CALENDAR year in which the acad cal for which the job is run are
2496 --graduate programs.
2497
2498 --first fetch the year in which the acad cal falls
2499 open cur_strt_date(p_acad_cal_type,p_acad_cal_seq );
2500 fetch cur_strt_date INTO l_start_date;
2501 close cur_strt_date;
2502
2503 l_start_year := substr(l_start_date,7);
2504 l_non_grad_lvl := 'N';
2505 --fetch all the academic calendars that start or end in the calendar year
2506
2507 FOR acad_cal_rec in acad_cal(l_start_year) loop
2508 --fetch all subordinate load calendars
2509 FOR load_cal_rec in c_load_cal_instance(acad_cal_rec.cal_type,acad_cal_rec.sequence_number) loop
2510 --fetch all progrms from spa_terms for the cal inst and person id
2511 --check if any program is not of type graduate. if found hten return fals for graduate ind.
2512 FOR cur_spa_rec in cur_spa(acad_cal_rec.cal_type,load_cal_rec.cal_type,load_cal_rec.sequence_number) loop
2513 IF l_non_grad_lvl <> 'Y' THEN
2514 OPEN c_grad_lvl(cur_spa_rec.course_cd, cur_spa_rec.version_number);
2515 FETCH c_grad_lvl INTO c_grad_lvl_rec;
2516 IF c_grad_lvl%NOTFOUND THEN
2517 l_non_grad_lvl := 'Y';
2518 END IF;
2519 CLOSE c_grad_lvl;
2520 END IF;
2521 END LOOP;
2522 END LOOP;
2523 END LOOP;
2524
2525 IF l_non_grad_lvl = 'Y' THEN
2526 --implies undergraduate pgms attempted hence not a graduate.
2527 x_grad_level := 'N';
2528 ELSE
2529 x_grad_level := 'Y';
2530 END IF;
2531
2532 x_credit_points := l_tot_points;
2533 x_ant_grad_date := l_ant_grad_date;
2534
2535 IF (g_debug_mode) THEN
2536 Put_Debug_Msg('| Calculating attendance type eftsu: '||l_tot_eftsu);
2537 END IF;
2538
2539 l_enr_status := IGS_EN_PRC_LOAD.ENRP_GET_LOAD_ATT (
2540 p_load_cal_type => p_load_cal_type ,
2541 p_load_figure => l_tot_eftsu
2542 );
2543
2544 IF (g_debug_mode) THEN
2545 Put_Debug_Msg('| Status returned: '||l_enr_status);
2546 END IF;
2547
2548 -- Convert enrollment status according to the set up table: c_enr_status
2549
2550 OPEN c_enr_status (l_enr_status) ;
2551 FETCH c_enr_status INTO l_stat;
2552 CLOSE c_enr_status;
2553
2554 IF (g_debug_mode) THEN
2555 Put_Debug_Msg('| Enrollemt type returned: '||l_stat);
2556 END IF;
2557
2558
2559
2560 IF l_A_stat_F THEN
2561 g_counts(10):=g_counts(10)+1; --Increment status specific count
2562 RETURN 'F';
2563
2564 ELSIF l_A_stat THEN
2565 IF (g_debug_mode) THEN
2566 Put_Debug_Msg('| Returning A status and date: '||l_A_date);
2567 END IF;
2568 x_status_date := l_A_date;
2569 g_counts(15):=g_counts(15)+1; --Increment status specific count
2570 RETURN 'A';
2571
2572 -- Check if one of the statuses is still applicable
2573 ELSIF l_G_stat THEN
2574 IF (g_debug_mode) THEN
2575 Put_Debug_Msg('| Returning G status and date: '||l_G_date);
2576 END IF;
2577 x_status_date := l_G_date;
2578 g_counts(14):=g_counts(14)+1; --Increment status specific count
2579 RETURN 'G';
2580
2581 ELSIF l_W_stat THEN
2582 IF (g_debug_mode) THEN
2583 Put_Debug_Msg('| Returning W status and date: '||l_W_date);
2584 END IF;
2585 x_status_date := l_W_date;
2586 g_counts(13):=g_counts(13)+1; --Increment status specific count
2587 RETURN 'W';
2588 ELSIF l_stat IS NOT NULL THEN
2589
2590 IF l_stat = 'F' THEN
2591 g_counts(10):=g_counts(10)+1; --Increment status specific count
2592 ELSIF l_stat = 'H' THEN
2593 g_counts(11):=g_counts(11)+1; --Increment status specific count
2594 ELSIF l_stat = 'L' THEN
2595 g_counts(12):=g_counts(12)+1; --Increment status specific count
2596 END IF;
2597 RETURN l_stat;
2598
2599 END IF;
2600
2601 RETURN NULL;
2602
2603 END Get_Enrollment_Status;
2604
2605
2606
2607 /******************************************************************************/
2608
2609 FUNCTION Get_Status_Change_Date (
2610 p_student_id IN igs_pe_person.person_id%TYPE,
2611 p_old_status IN VARCHAR2,
2612 p_new_status IN VARCHAR2,
2613 p_load_cal_type IN igs_ca_inst.cal_type%TYPE,
2614 p_load_cal_seq IN igs_ca_inst.sequence_number%TYPE,
2615 p_acad_cal_type IN igs_ca_inst.cal_type%TYPE,
2616 p_acad_cal_seq IN igs_ca_inst.sequence_number%TYPE
2617 ) RETURN DATE
2618 -------------------------------------------------------------------------------------------
2619 --Change History:
2620 --Who When What
2621 --jbegum 25-Jun-2003 BUG#2930935
2622 -- Modified cursor c_unit_cp
2623 --kkillams 02-05-2003 Modified c_hist_units and c_orig_units cursor where clause due
2624 -- to change in pk of the student unit attempt w.r.t. bug number 2829262
2625 --svanukur 09-jan-2005 Modified the logic of the procedure to calculate teh status change date.
2626 -- ckasu 06-MAR-2006 Modified cursor c_drop_units as a prt of bug 5073484 inorder to resolve
2627 -- Sharabale Memory issue.
2628 -- The procedure first determines the current eftsu of all unit attempts of a student excluding unconfirm
2629 --SUA. It then loops through each history record and compares with the previous history record. The change in CP
2630 --and eftsu is determined and the diff is calculated. This diff is then progressively subtracted from the current eftsu
2631 --and the the record for which the difference exceeds the threshold is the record that caused the status change.
2632 -- The history end date of this record is the status change date.
2633 -------------------------------------------------------------------------------------------
2634 IS
2635
2636 l_thresh_eftsu NUMBER;
2637 l_prev_eftsu NUMBER;
2638 l_cur_eftsu NUMBER := 0;
2639 l_loc_eftsu NUMBER;
2640 l_loc_point NUMBER;
2641 l_new_eftsu NUMBER;
2642 l_new_points NUMBER;
2643 l_old_eftsu NUMBER;
2644 l_old_points NUMBER;
2645 l_diff_eftsu NUMBER;
2646 l_diff_points NUMBER;
2647 l_sign NUMBER;
2648 l_status VARCHAR2(20);
2649
2650 l_teach_cal_type igs_en_su_attempt.cal_type%TYPE;
2651 l_ci_seq_num igs_en_su_attempt.ci_sequence_number%TYPE;
2652 l_discont_dt igs_en_su_attempt.discontinued_dt%TYPE;
2653 l_adm_status igs_en_su_attempt.administrative_unit_status%TYPE;
2654 l_unt_atmpt_stat igs_en_su_attempt.unit_attempt_status%TYPE;
2655
2656
2657 CURSOR c_thresh_limit IS
2658 SELECT lower_enr_load_range
2659 FROM igs_en_atd_type_load
2660 WHERE
2661 cal_type = p_load_cal_type
2662 AND attendance_type in (select opt_val from
2663 igs_En_nsc_options where opt_type = 'ENR_STAT_'||p_old_status)
2664 order by lower_enr_load_range;
2665
2666 --List of all courses for the given load term
2667 CURSOR c_all_units IS
2668 SELECT DISTINCT
2669 ut.course_cd ,
2670 pr.version_number cr_ver_number ,
2671 ut.unit_cd ,
2672 ut.version_number unit_ver_number,
2673 ut.cal_type teach_cal_type,
2674 ut.ci_sequence_number teach_seq_number ,
2675 ut.uoo_id uoo_id,
2676 ut.override_enrolled_cp override_enrolled_cp,
2677 ut.override_eftsu override_eftsu
2678 FROM igs_en_su_attempt ut,
2679 igs_en_stdnt_ps_att pr,
2680 igs_ps_ver vers
2681 WHERE (ut.cal_type,ut.ci_sequence_number) IN
2682 (SELECT teach_cal_type,
2683 teach_ci_sequence_number
2684 FROM igs_ca_load_to_teach_v
2685 WHERE load_cal_type = p_load_cal_type
2686 AND load_ci_sequence_number = p_load_cal_seq
2687 )
2688 AND ut.person_id = p_student_id
2689 AND ut.course_cd = pr.course_cd
2690 AND ut.person_id = pr.person_id
2691 AND pr.cal_type = p_acad_cal_type
2692 AND pr.course_attempt_status IN ('COMPLETED', 'ENROLLED', 'INACTIVE', 'INTERMIT','DISCONTIN')
2693 AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
2694 AND pr.course_cd = vers.course_cd
2695 AND vers.version_number = pr.version_number ;
2696 -- AND vers.responsible_org_unit_cd = pr_branch_id;
2697
2698 -- added by ckasu as a prt of bug 5073484.
2699 CURSOR c_get_teach_cal_dtls IS
2700 SELECT teach_cal_type,
2701 teach_ci_sequence_number
2702 FROM igs_ca_load_to_teach_v
2703 WHERE load_cal_type = p_load_cal_type
2704 AND load_ci_sequence_number = p_load_cal_seq;
2705
2706
2707 -- modified by ckasu as a prt of bug 5073484.
2708 -- List of all history units
2709
2710 CURSOR c_drop_units(p_teach_cal_type IGS_CA_INST.cal_type%TYPE,
2711 p_teach_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
2712 SELECT ut.course_cd ,
2713 pr.version_number cr_ver_number ,
2714 ut.unit_cd ,
2715 ut.version_number unit_ver_number,
2716 ut.cal_type teach_cal_type,
2717 ut.ci_sequence_number teach_seq_number ,
2718 ut.override_enrolled_cp ,
2719 ut.override_eftsu ,
2720 ut.hist_end_dt,
2721 ut.no_assessment_ind,
2722 ut.uoo_id,
2723 ut.administrative_unit_status,
2724 ut.unit_attempt_status,
2725 ut.discontinued_dt
2726 FROM igs_en_su_attempt_h ut,
2727 igs_en_stdnt_ps_att pr,
2728 igs_ps_ver vers
2729 WHERE ut.cal_type = p_teach_cal_type
2730 AND ut.ci_sequence_number = p_teach_ci_sequence_number
2731 AND ut.person_id = p_student_id
2732 AND ut.course_cd = pr.course_cd
2733 AND ut.person_id = pr.person_id
2734 AND pr.cal_type = p_acad_cal_type
2735 AND pr.course_attempt_status IN ('COMPLETED', 'ENROLLED', 'INACTIVE', 'INTERMIT','DISCONTIN')
2736 AND (vers.generic_course_ind = g_include_gen OR g_include_gen = 'Y')
2737 AND pr.course_cd = vers.course_cd
2738 AND vers.version_number = pr.version_number
2739 ORDER BY hist_end_dt DESC;
2740
2741
2742 -- This cursor selects next history record from the table (if any) to compare enrollment points.
2743
2744 CURSOR c_hist_units(p_c_course_cd VARCHAR2,
2745 p_c_uoo_id NUMBER,
2746 p_c_hist_end_dt DATE ) IS
2747 SELECT ut.override_eftsu,
2748 ut.override_enrolled_cp ,
2749 ut.cal_type,
2750 ut.ci_sequence_number ,
2751 ut.discontinued_dt,
2752 ut.administrative_unit_status,
2753 ut.unit_attempt_status,
2754 ut.no_assessment_ind,
2755 ut.hist_end_dt
2756 FROM igs_en_su_attempt_h ut
2757 WHERE ut.person_id = p_student_id
2758 AND ut.course_cd = p_c_course_cd
2759 AND ut.uoo_id = p_c_uoo_id
2760 AND ut.hist_end_dt > p_c_hist_end_dt
2761 ORDER BY ut.hist_end_dt;
2762 old_hist_end_dt igs_en_su_attempt_h.hist_end_dt%TYPE;
2763 -- get the next history record whose assessment_ind is not null
2764 CURSOR c_next_ass_ind (p_c_course_cd VARCHAR2,
2765 p_c_uoo_id NUMBER,
2766 p_c_hist_end_dt DATE ) IS
2767 SELECT ut.no_assessment_ind
2768 FROM igs_en_su_attempt_h ut
2769 WHERE ut.person_id = p_student_id
2770 AND ut.course_cd = p_c_course_cd
2771 AND ut.uoo_id = p_c_uoo_id
2772 AND ut.hist_end_dt > p_c_hist_end_dt
2773 AND ut.no_assessment_ind IS NOT NULL
2774 ORDER BY ut.hist_end_dt;
2775 old_next_ass_ind igs_en_su_attempt_all.no_assessment_ind%TYPE;
2776 new_next_ass_ind igs_en_su_attempt_all.no_assessment_ind%TYPE;
2777
2778 -- This cursor selects current record to compare enrollment points.
2779
2780 CURSOR c_orig_units(p_c_course_cd VARCHAR2,
2781 p_c_uoo_id VARCHAR2) IS
2782 SELECT ut.override_eftsu ,
2783 ut.override_enrolled_cp ,
2784 ut.cal_type,
2785 ut.ci_sequence_number ,
2786 ut.discontinued_dt,
2787 ut.administrative_unit_status,
2788 ut.unit_attempt_status,
2789 ut.no_assessment_ind
2790 FROM igs_en_su_attempt ut
2791 WHERE ut.person_id = p_student_id
2792 AND ut.course_cd = p_c_course_cd
2793 AND ut.uoo_id = p_c_uoo_id;
2794 c_sua_rec c_orig_units%ROWTYPE;
2795 --Credit points at the unit level - need to get and compare.
2796
2797 CURSOR c_unit_cp( cp_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE) IS
2798 SELECT NVL(cps.enrolled_credit_points,uv.enrolled_credit_points)
2799 FROM igs_ps_unit_ver uv,
2800 igs_ps_usec_cps cps,
2801 igs_ps_unit_ofr_opt uoo
2802 WHERE uoo.uoo_id = cps.uoo_id(+) AND
2803 uoo.unit_cd = uv.unit_cd AND
2804 uoo.version_number = uv.version_number AND
2805 uoo.uoo_id = cp_uoo_id;
2806
2807 CURSOR c_uv( cp_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE) IS
2808 SELECT uoo.version_number
2809 FROM igs_ps_unit_ofr_opt uoo
2810 WHERE uoo.uoo_id = cp_uoo_id;
2811
2812 l_unit_version NUMBER;
2813 old_load_incur varchar2(1);
2814 new_load_incur varchar2(1);
2815
2816 old_teach_cal_type igs_en_su_Attempt.cal_type%TYPE;
2817 old_teach_seq_number igs_en_su_Attempt.ci_sequence_number%TYPE;
2818 old_discontinued_dt igs_en_su_Attempt.discontinued_dt%TYPE;
2819 old_administrative_unit_status igs_en_su_Attempt.administrative_unit_status%TYPE;
2820 old_unit_attempt_status igs_en_su_Attempt.unit_attempt_status%TYPE;
2821 old_no_assessment_ind igs_en_su_Attempt.no_assessment_ind%TYPE;
2822
2823 BEGIN
2824
2825 -- Get the current status minimum - this is the threshold level.
2826 OPEN c_thresh_limit;
2827 FETCH c_thresh_limit INTO l_thresh_eftsu;
2828 CLOSE c_thresh_limit;
2829
2830 IF (g_debug_mode) THEN
2831 Put_Debug_Msg('| Calculating status change date, threshhold is: '||l_thresh_eftsu);
2832 END IF;
2833
2834
2835 FOR c_all_units_rec IN c_all_units LOOP
2836 -- Get current eftsu and calculate
2837
2838 IF (g_debug_mode) THEN
2839 Put_Debug_Msg('| Calculating EFTSU for unit: course: '||c_all_units_rec.course_cd
2840 ||' c vers '||c_all_units_rec.cr_ver_number
2841 ||' unit '||c_all_units_rec.unit_cd
2842 ||' u vers '||c_all_units_rec.unit_ver_number
2843 ||' teach cal '||c_all_units_rec.teach_cal_type
2844 ||' seq '||c_all_units_rec.teach_seq_number
2845 ||' uoo '||c_all_units_rec.uoo_id
2846 ||' enr points '||c_all_units_rec.override_enrolled_cp
2847 ||' eftsu '||c_all_units_rec.override_eftsu );
2848 END IF;
2849
2850 l_cur_eftsu := l_cur_eftsu +
2851 NVL(IGS_EN_PRC_LOAD.ENRP_CLC_SUA_EFTSU(
2852 p_person_id => p_student_id ,
2853 p_course_cd => c_all_units_rec.course_cd ,
2854 p_crv_version_number => c_all_units_rec.cr_ver_number ,
2855 p_unit_cd => c_all_units_rec.unit_cd ,
2856 p_unit_version_number => c_all_units_rec.unit_ver_number,
2857 p_teach_cal_type => c_all_units_rec.teach_cal_type,
2858 p_teach_sequence_number => c_all_units_rec.teach_seq_number ,
2859 p_uoo_id => c_all_units_rec.uoo_id ,
2860 p_load_cal_type => p_load_cal_type,
2861 p_load_sequence_number => p_load_cal_seq,
2862 p_override_enrolled_cp => c_all_units_rec.override_enrolled_cp ,
2863 p_override_eftsu => c_all_units_rec.override_eftsu ,
2864 p_sca_cp_total => NULL ,
2865 p_key_course_cd => NULL,
2866 p_key_version_number => NULL,
2867 p_credit_points => l_loc_point,
2868 p_include_audit =>'N'),0);
2869
2870 IF (g_debug_mode) THEN
2871 Put_Debug_Msg('| Returned EFTSU: '||l_cur_eftsu);
2872 END IF;
2873
2874 END LOOP;
2875
2876 FOR c_get_teach_cal_dtls_rec IN c_get_teach_cal_dtls LOOP
2877
2878 FOR c_drop_units_rec IN c_drop_units(c_get_teach_cal_dtls_rec.teach_cal_type,c_get_teach_cal_dtls_rec.teach_ci_sequence_number) LOOP
2879
2880 IF (g_debug_mode) THEN
2881 Put_Debug_Msg('| History loop unit found: course'||c_drop_units_rec.course_cd
2882 ||' c vers '||c_drop_units_rec.cr_ver_number
2883 ||' unit '||c_drop_units_rec.unit_cd
2884 ||' u vers '||c_drop_units_rec.unit_ver_number
2885 ||' teach cal '||c_drop_units_rec.teach_cal_type
2886 ||' seq '||c_drop_units_rec.teach_seq_number
2887 ||' uoo id '||c_drop_units_rec.uoo_id
2888 ||' enr points '||c_drop_units_rec.override_enrolled_cp
2889 ||' eftsu '||c_drop_units_rec.override_eftsu);
2890 END IF;
2891
2892 -- Checking the current record
2893 OPEN c_orig_units(c_drop_units_rec.course_cd ,
2894 c_drop_units_rec.uoo_id);
2895
2896 FETCH c_orig_units INTO c_sua_rec;
2897 CLOSE c_orig_units;
2898
2899 --Initalize status
2900 l_status := '';
2901
2902 --Checking if there is a history record available
2903 OPEN c_hist_units(c_drop_units_rec.course_cd ,
2904 c_drop_units_rec.uoo_id,
2905 c_drop_units_rec.hist_end_dt);
2906
2907 FETCH c_hist_units INTO l_old_eftsu,l_old_points,old_teach_cal_type,
2908 old_teach_seq_number ,
2909 old_discontinued_dt,
2910 old_administrative_unit_status,
2911 old_unit_attempt_status,
2912 old_no_assessment_ind,
2913 old_hist_end_dt;
2914
2915 IF (g_debug_mode) THEN
2916 Put_Debug_Msg('| Fetching prev history eftsu: '||l_old_eftsu||' cred points'||l_old_points||' date '||c_drop_units_rec.hist_end_dt);
2917 END IF;
2918
2919 IF c_hist_units%NOTFOUND THEN
2920
2921 -- override with the sua record values
2922 l_old_eftsu := c_sua_rec.override_eftsu ;
2923 l_old_points := c_sua_rec.override_enrolled_cp ;
2924 old_teach_cal_type := c_sua_rec.cal_type ;
2925 old_teach_seq_number := c_sua_rec.ci_sequence_number ;
2926 old_discontinued_dt := c_sua_rec.discontinued_dt ;
2927 old_administrative_unit_status := c_sua_rec.administrative_unit_status ;
2928 old_unit_attempt_status:= c_sua_rec.unit_attempt_status ;
2929 old_no_assessment_ind := c_sua_rec.no_assessment_ind ;
2930
2931
2932
2933 IF (g_debug_mode) THEN
2934 Put_Debug_Msg('| Fetching prev NON history eftsu: '||l_old_eftsu||' cred points'||l_old_points||' status '||old_unit_attempt_status);
2935 END IF;
2936
2937 END IF;
2938
2939 CLOSE c_hist_units;
2940
2941 l_new_eftsu := c_drop_units_rec.override_eftsu;
2942 l_new_points := c_drop_units_rec.override_enrolled_cp;
2943
2944 --IF any of them nulls we need to get amount from unit level to compare
2945
2946 IF l_new_eftsu IS NULL AND l_new_points IS NULL THEN
2947 IF c_drop_units_rec.no_assessment_ind IS NULL THEN
2948 --get the non null ass_id from history . If all history records
2949 --are null then assign the sua value
2950 new_next_ass_ind := NULL;
2951 OPEN c_next_ass_ind(c_drop_units_rec.course_cd ,c_drop_units_rec.uoo_id,c_drop_units_rec.hist_end_dt);
2952 FETCH c_next_ass_ind INTO new_next_ass_ind;
2953 CLOSE c_next_ass_ind;
2954 -- if no other later history records exist with not null value for assessment_ind
2955 -- then use SUA which is the latest value
2956 new_next_ass_ind := NVL(new_next_ass_ind,c_sua_rec.no_assessment_ind);
2957 END IF;
2958
2959 IF NVL(c_drop_units_rec.no_assessment_ind, new_next_ass_ind) = 'Y' OR
2960 c_drop_units_rec.unit_attempt_status = 'DROPPED'THEN
2961 l_new_points := 0;
2962 Put_Debug_Msg('| Assigning 0 to new points: '||l_new_points);
2963 ELSE
2964 OPEN c_unit_cp (c_drop_units_rec.uoo_id);
2965 FETCH c_unit_cp INTO l_new_points;
2966 CLOSE c_unit_cp;
2967 IF (g_debug_mode) THEN
2968 Put_Debug_Msg('| Assigning points from unit level to new points: '||l_new_points);
2969 END IF;
2970 END IF;
2971
2972 END IF;
2973
2974 IF l_old_eftsu IS NULL AND l_old_points IS NULL THEN
2975 IF old_no_assessment_ind IS NULL THEN
2976 --get the non null ass_id from history . If all history records
2977 --are null then assign the sua value
2978 old_next_ass_ind := NULL;
2979 OPEN c_next_ass_ind(c_drop_units_rec.course_cd ,c_drop_units_rec.uoo_id,old_hist_end_dt);
2980 FETCH c_next_ass_ind INTO old_next_ass_ind;
2981 CLOSE c_next_ass_ind;
2982 -- if no other later history records exist with not null value for assessment_ind
2983 -- then use SUA which is the latest value
2984 old_next_ass_ind := NVL(old_next_ass_ind,c_sua_rec.no_assessment_ind);
2985 END IF;
2986 IF NVL(old_no_assessment_ind, old_next_ass_ind) = 'Y' OR
2987 old_unit_attempt_status = 'DROPPED' THEN
2988 l_old_points := 0;
2989 Put_Debug_Msg('| Assigning 0 to old points: '||l_old_points);
2990 ELSE
2991 OPEN c_unit_cp (c_drop_units_rec.uoo_id);
2992 FETCH c_unit_cp INTO l_old_points;
2993 CLOSE c_unit_cp;
2994 IF (g_debug_mode) THEN
2995 Put_Debug_Msg('| Assigning points from unit level to old points: '||l_old_points);
2996 END IF;
2997 END IF;
2998
2999 END IF;
3000
3001 l_diff_eftsu := NVL(l_old_eftsu,0) - NVL(l_new_eftsu,0);
3002 l_diff_points := NVL(l_old_points,0) - NVL(l_new_points,0);
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028 IF l_diff_eftsu = 0 and l_diff_points = 0 then
3029 -- Comparing original value with the current one
3030 --if diff between cp and eftsu is null, check if the units incur load.
3031 --in both the cases, if the current record has a null value for no_Assessment_ind
3032 --or unit_Attempt_Status , pass the value derived above by looping thru history or SUA.
3033
3034
3035 new_load_incur := igs_en_prc_load.enrp_get_load_incur(
3036 c_drop_units_rec.teach_cal_type,
3037 c_drop_units_rec.teach_seq_number ,
3038 c_drop_units_rec.discontinued_dt,
3039 c_drop_units_rec.administrative_unit_status,
3040 nvl(c_drop_units_rec.unit_attempt_status,old_unit_attempt_status),
3041 NVL(c_drop_units_rec.no_assessment_ind, new_next_ass_ind),
3042 p_load_cal_type,
3043 p_load_cal_seq,
3044 c_drop_units_rec.uoo_id
3045 ) ;
3046
3047 old_load_incur := igs_en_prc_load.enrp_get_load_incur(
3048 old_teach_cal_type,
3049 old_teach_seq_number ,
3050 old_discontinued_dt,
3051 old_administrative_unit_status,
3052 old_unit_attempt_status,
3053 NVL(old_no_assessment_ind, old_next_ass_ind),
3054 p_load_cal_type,
3055 p_load_cal_seq,
3056 c_drop_units_rec.uoo_id
3057 ) ;
3058
3059 Put_Debug_Msg('new_load_incur'|| new_load_incur);
3060 Put_Debug_Msg('old_load_incur'|| old_load_incur);
3061
3062 IF old_load_incur = 'Y' and new_load_incur = 'N' THEN
3063 l_diff_eftsu := l_new_eftsu;
3064 l_diff_points := l_new_points;
3065 ELSIF old_load_incur = 'N' and new_load_incur = 'Y' THEN
3066 l_diff_eftsu := 0 - l_new_eftsu;
3067 l_diff_points := 0 - l_new_points;
3068 END if;
3069
3070 END IF;
3071
3072 IF (g_debug_mode) THEN
3073 Put_Debug_Msg('| Amount of changed because of action: eftsu '||l_diff_eftsu||' points '|| l_diff_points );
3074 END IF;
3075
3076 -- Get changes in eftsu and calculate
3077
3078 IF l_diff_eftsu <> 0 OR l_diff_points <> 0 THEN
3079
3080 --Make null values to avoid confusion in the function
3081
3082 IF l_diff_eftsu = 0 THEN
3083 l_diff_eftsu := NULL;
3084 END IF;
3085
3086 IF l_diff_points = 0 THEN
3087 l_diff_points := NULL;
3088 END IF;
3089
3090 IF c_drop_units_rec.unit_ver_number IS NULL THEN
3091 OPEN c_uv(c_drop_units_rec.uoo_id);
3092 FETCH c_uv into l_unit_version;
3093 CLOSE c_uv;
3094 END IF;
3095
3096 --calculate eftsu of the difference and subtract this from the current eftsu
3097 --calculated at the beginning. Progressively subtract the difference
3098 -- and check with the threshold value to determine the date on which the eftsu exceeded the
3099 --threshold
3100 l_loc_eftsu := NVL(IGS_EN_PRC_LOAD.ENRP_CLC_SUA_EFTSU(
3101 p_person_id => p_student_id ,
3102 p_course_cd => c_drop_units_rec.course_cd ,
3103 p_crv_version_number => c_drop_units_rec.cr_ver_number ,
3104 p_unit_cd => c_drop_units_rec.unit_cd ,
3105 p_unit_version_number => nvl(c_drop_units_rec.unit_ver_number,l_unit_version),
3106 p_teach_cal_type => c_drop_units_rec.teach_cal_type,
3107 p_teach_sequence_number => c_drop_units_rec.teach_seq_number ,
3108 p_uoo_id => c_drop_units_rec.uoo_id ,
3109 p_load_cal_type => p_load_cal_type,
3110 p_load_sequence_number => p_load_cal_seq,
3111 p_override_enrolled_cp => l_diff_points ,
3112 p_override_eftsu => l_diff_eftsu ,
3113 p_sca_cp_total => NULL ,
3114 p_key_course_cd => NULL,
3115 p_key_version_number => NULL,
3116 p_credit_points => l_loc_point,
3117 p_include_audit =>'N'),0);
3118
3119 IF (g_debug_mode) THEN
3120 Put_Debug_Msg('| Calculated by API eftsu '||l_loc_eftsu||' points '|| l_loc_point);
3121 END IF;
3122
3123 -- Decrease the total amount of the enrolled credit points for the student by the current unit EFTSU change.
3124 l_cur_eftsu := l_cur_eftsu - l_loc_eftsu;
3125
3126
3127
3128
3129
3130
3131
3132 -- Compare the new amount to the threshold level. If its greater then continue the loop
3133
3134 IF (g_debug_mode) THEN
3135 Put_Debug_Msg('| New eftsu value eftsu '||l_cur_eftsu);
3136 END IF;
3137
3138 IF l_cur_eftsu > l_thresh_eftsu THEN
3139 IF (g_debug_mode) THEN
3140 Put_Debug_Msg('| Threshhold reached, returning date: '||c_drop_units_rec.hist_end_dt);
3141 END IF;
3142 RETURN c_drop_units_rec.hist_end_dt;
3143 END IF;
3144 END IF; -- end of IF l_diff_eftsu <> 0 OR l_diff_points <> 0
3145
3146 END LOOP;
3147
3148 END LOOP;-- end of c_get_teach_cal_dtls_rec FOR LOOP
3149 RETURN NULL;
3150
3151 END Get_Status_Change_Date;
3152
3153
3154
3155 PROCEDURE Put_Debug_Msg (
3156 p_debug_message IN VARCHAR2
3157 ) IS
3158 l_api_name CONSTANT VARCHAR2(30) := 'Put_Debug_Msg';
3159
3160 BEGIN
3161
3162 fnd_file.put_line(FND_FILE.LOG,p_debug_message);
3163 END Put_Debug_Msg;
3164
3165 FUNCTION org_alt_check (p_org_id VARCHAR2)
3166 RETURN VARCHAR2 IS
3167
3168 CURSOR c_alt_id IS
3169 SELECT org_structure_id
3170 FROM igs_or_org_alt_ids alt, igs_or_org_alt_idtyp types
3171 WHERE alt.org_structure_id=p_org_id
3172 AND NVL(alt.end_date, SYSDATE) >= SYSDATE
3173 AND NVL(alt.start_date,SYSDATE) <= SYSDATE
3174 AND alt.org_alternate_id_type = types.org_alternate_id_type
3175 AND types.system_id_type ='NSC_BRANCH';
3176
3177 l_alt_id c_alt_id%ROWTYPE ;
3178
3179 BEGIN
3180 l_alt_id.org_structure_id:=NULL;
3181 OPEN c_alt_id;
3182 FETCH c_alt_id INTO l_alt_id;
3183 CLOSE c_alt_id;
3184 RETURN l_alt_id.org_structure_id;
3185 END org_alt_check ;
3186
3187 END IGS_EN_NSC_PKG;
[Home] [Help]