PACKAGE BODY: APPS.IGR_PER_INFO_001

Source

1 PACKAGE BODY IGR_PER_INFO_001 AS
2 /* $Header: IGSRTP1B.pls 120.0 2005/06/02 03:31:06 appldev noship $ */
3 /******************************************************************
4 Created By: Benjamin Gu
5 Date Created By: 19-Feb-2002
6 Purpose: Some pl/sql functions used in person summary pages
7 Known limitations,enhancements,remarks:
8 Change History
9 Who        When          What
10 jchin      14-Feb-05     Modified package for IGR pseudo product
11 ******************************************************************/
12 
13 FUNCTION GET_APPL_PROG_UNIT_SETS(x_person_id IN NUMBER,
14 				 x_adm_appl_number IN NUMBER,
15 				 x_course_cd IN VARCHAR2,
16                                  x_seq_number IN NUMBER
17                                  ) RETURN VARCHAR2 IS
18   CURSOR cur_unit_sets IS
19   SELECT eus.TITLE
20     FROM IGS_AD_UNIT_SETS aus,
21          IGS_EN_UNIT_SET eus
22    WHERE aus.UNIT_SET_CD = eus.UNIT_SET_CD
23      AND aus.VERSION_NUMBER = eus.VERSION_NUMBER
24      AND aus.PERSON_ID = x_person_id
25      AND aus.ADMISSION_APPL_NUMBER = x_adm_appl_number
26      AND aus.NOMINATED_COURSE_CD = x_course_cd
27      AND aus.SEQUENCE_NUMBER = x_seq_number;
28 
29   l_title varchar2(100);
30   l_unit_sets varchar2(10000) :='';
31   l_num number :=0;
32 
33 BEGIN
34   OPEN cur_unit_sets;
35   LOOP
36     FETCH cur_unit_sets INTO l_title;
37     EXIT WHEN cur_unit_sets%NOTFOUND;
38 
39     if l_num = 0 then
40       l_unit_sets := l_title;
41     else
42       l_unit_sets := l_unit_sets||' , '||l_title;
43     end if;
44 
45     l_num := l_num + 1;
46   END LOOP;
47   return l_unit_sets;
48 
49 END GET_APPL_PROG_UNIT_SETS;
50 
51 
52 FUNCTION GET_INQ_PROG_UNIT_SETS(x_prog_pref_id IN NUMBER) RETURN VARCHAR2 IS
53   CURSOR cur_unit_sets IS
54 SELECT CODE.DESCRIPTION
55   FROM IGS_AD_I_PRG_PRF_UST PREF,
56        IGS_AD_I_PRG_U_SET USET,
57        IGS_AD_INQ_UNIT_SETS CODE
58  WHERE PREF.INQ_PROG_UNIT_SET_ID = USET.INQ_PROG_UNIT_SET_ID
59    AND USET.INQ_UNIT_SET_CODE_ID = CODE.INQ_UNIT_SET_CODE_ID
60    AND PREF.INQ_PROG_PREF_ID =x_prog_pref_id;
61 
62   l_title varchar2(100);
63   l_unit_sets varchar2(10000) :='';
64   l_num number :=0;
65 
66 BEGIN
67   OPEN cur_unit_sets;
68   LOOP
69     FETCH cur_unit_sets INTO l_title;
70     EXIT WHEN cur_unit_sets%NOTFOUND;
71 
72     if l_num = 0 then
73       l_unit_sets := l_title;
74     else
75       l_unit_sets := l_unit_sets||' <BR> '||l_title;
76     end if;
77 
78     l_num := l_num + 1;
79   END LOOP;
80   return l_unit_sets;
81 END GET_INQ_PROG_UNIT_SETS;
82 
83 
84 FUNCTION GET_TEST_SCORES(x_test_result_id IN NUMBER) RETURN VARCHAR2 IS
85   CURSOR cur_recs IS
86   select SCORES.TEST_SEGMENT||' : '||SCORES.SEGMENT_SCORE SCORE
87     from IGS_AD_TEST_SCORES_V SCORES
88    where SCORES.TEST_RESULTS_ID = x_test_result_id;
89 
90   l_title varchar2(100);
91   l_ret_val varchar2(10000) :='';
92   l_num number :=0;
93 
94 BEGIN
95   OPEN cur_recs;
96   LOOP
97     FETCH cur_recs INTO l_title;
98     EXIT WHEN cur_recs%NOTFOUND;
99 
100     if l_num = 0 then
101       l_ret_val := l_title;
102     else
103       l_ret_val := l_ret_val||' <BR> '||l_title;
104     end if;
105 
106     l_num := l_num + 1;
107   END LOOP;
108   return l_ret_val;
109 
110 END GET_TEST_SCORES;
111 
112 
113 END IGR_PER_INFO_001;