1 PACKAGE BODY IGS_PS_WF_EVENT_HOOK_PKG AS
2 /* $Header: IGSPS79B.pls 120.2 2006/01/31 01:52:02 sommukhe noship $ */
3 procedure wf_get_shadow_values( p_uoo_id IN NUMBER,
4 p_unit_section_occurrence_id IN NUMBER,
5 p_type IN VARCHAR2,
6 p_old_values IN OUT NOCOPY IGS_PS_WF_EVENT_HOOK_PKG.OCCUROLD,
7 p_new_values IN OUT NOCOPY IGS_PS_WF_EVENT_HOOK_PKG.OCCURNEW,
8 p_unit_dtls IN OUT NOCOPY IGS_PS_WF_EVENT_HOOK_PKG.UNITDTLS)
9 IS
10 /**********************************************************
11 Created By :
12
13 Date Created By :
14
15 Purpose :
16
17 Know limitations, enhancements or remarks
18
19 Change History
20
21 Who When What
22 sommukhe 24-Jan-2006 Bug #4926548,replaced igs_pe_person with hz_parties and hz_person_profiles for inline cursors p_students and p_instructors
23 sarakshi 12-Jan-2005 Bug#4926548, modified the select for the ref cursor p_unit_dtls such ath tit uses the base tables rather than the view IGS_PS_UNIT_OFR_OPT_V
24 ***************************************************************/
25
26 BEGIN
27 IF p_type = 'CNCL' THEN
28 RETURN;
29 END IF;
30 IF p_unit_dtls%ISOPEN THEN
31 CLOSE p_unit_dtls;
32 END IF;
33 IF p_new_values%ISOPEN THEN
34 CLOSE p_new_values;
35 END IF;
36 IF p_old_values%ISOPEN THEN
37 CLOSE p_old_values;
38 END IF;
39 OPEN p_unit_dtls FOR SELECT us.unit_cd,
40 uv.title,
41 ca.start_dt cal_start_dt,
42 ca.end_dt cal_end_dt,
43 us.location_cd,
44 loc.description location_description,
45 uc.unit_mode unit_mode
46 FROM igs_ps_unit_ofr_opt_all us, igs_ca_inst_all ca, igs_ps_unit_ver_all uv, igs_as_unit_class_all uc, igs_ad_location_all loc
47 WHERE us.unit_cd=uv.unit_cd
48 AND us.version_number=uv.version_number
49 AND us.cal_type=ca.cal_type
50 AND us.ci_sequence_number=ca.sequence_number
51 AND us.unit_class=uc.unit_class
52 AND us.location_cd=loc.location_cd
53 AND us.uoo_id = p_uoo_id;
54 OPEN p_new_values FOR SELECT Monday,
55 Tuesday,
56 wednesday,
57 thursday,
58 friday,
59 Saturday,
60 sunday,
61 start_time,
62 end_time,
63 building_code,
64 room_code
65 FROM IGS_PS_USEC_OCCURS
66 WHERE notify_status='TRIGGER'
67 AND unit_section_occurrence_id = p_unit_section_occurrence_id;
68 OPEN p_old_values FOR SELECT unit_section_occurrence_id,
69 Monday,
70 Tuesday,
71 wednesday,
72 thursday,
73 friday,
74 Saturday,
75 sunday,
76 start_time,
77 end_time,
78 building_code,
79 room_code
80 FROM IGS_PS_SH_USEC_OCCURS
81 WHERE unit_section_occurrence_id = p_unit_section_occurrence_id;
82 END wf_get_shadow_values;
83
84 procedure wf_event_audience (p_uoo_id IN NUMBER,
85 p_unit_section_occurrence_id IN NUMBER,
86 p_type IN VARCHAR2,
87 p_students IN OUT NOCOPY IGS_PS_WF_EVENT_HOOK_PKG.StudentDetails,
88 p_instructors IN OUT NOCOPY IGS_PS_WF_EVENT_HOOK_PKG.InstructorDetails)
89 IS
90 CURSOR c_notify IS SELECT a.inst_notify_ind,
91 b.instructor_id
92 FROM IGS_PS_USEC_OCCURS a,
93 IGS_PS_USO_INSTRCTRS_V b
94 WHERE a.unit_section_occurrence_id = p_unit_section_occurrence_id
95 AND a.unit_section_occurrence_id=b.unit_section_occurrence_id;
96
97 l_inst_notify_ind IGS_PS_USEC_OCCURS.inst_notify_ind%TYPE;
98 l_instructor_id IGS_PS_USO_INSTRCTRS_V.instructor_id%TYPE;
99 BEGIN
100
101 IF p_type='MOD' THEN
102 IF p_students%ISOPEN THEN
103 CLOSE p_students;
104 ELSE
105 OPEN p_students FOR SELECT a.person_id,
106 a.unit_attempt_status,
107 p.party_number person_number,
108 p.email_address email_addr,
109 pp.person_name full_name
110 FROM igs_en_su_attempt a,hz_parties p,hz_person_profiles pp
111 WHERE a.UOO_ID=p_uoo_id
112 AND a.UNIT_ATTEMPT_STATUS IN ('ENROLLED','WAITLISTED')
113 AND a.person_id=p.party_id
114 AND p.party_id = pp.party_id AND
115 SYSDATE BETWEEN PP.EFFECTIVE_START_DATE AND
116 NVL(PP.EFFECTIVE_END_DATE,SYSDATE);
117 END IF;
118 END IF;
119
120 IF p_type='CNCL' THEN
121 IF p_students%ISOPEN THEN
122 CLOSE p_students;
123 ELSE
124 OPEN p_students FOR SELECT a.person_id,
125 a.unit_attempt_status,
126 p.party_number person_number,
127 p.email_address email_addr,
128 pp.person_name full_name
129 FROM igs_en_su_attempt a,hz_parties p,hz_person_profiles pp
130 WHERE a.uoo_id=p_uoo_id
131 AND a.unit_attempt_status IN ('DROPPED','DISCONTIN')
132 AND a.person_id=p.party_id
133 AND p.party_id = pp.party_id AND
134 SYSDATE BETWEEN PP.EFFECTIVE_START_DATE AND
135 NVL(PP.EFFECTIVE_END_DATE,SYSDATE);
136 END IF;
137 END IF;
138 OPEN c_notify;
139 FETCH c_notify INTO l_inst_notify_ind, l_instructor_id;
140 IF l_inst_notify_ind='Y' THEN
141 LOOP
142 IF p_instructors%ISOPEN THEN
143 close p_instructors;
144 ELSE
145 OPEN p_instructors FOR SELECT P.PARTY_NUMBER PERSON_NUMBER,P.EMAIL_ADDRESS EMAIL_ADDR,PP.PERSON_NAME FULL_NAME
146 FROM HZ_PARTIES P,HZ_PERSON_PROFILES PP
147 WHERE P.PARTY_ID = l_instructor_id
148 AND P.PARTY_ID = PP.PARTY_ID AND
149 SYSDATE BETWEEN PP.EFFECTIVE_START_DATE AND
150 NVL(PP.EFFECTIVE_END_DATE,SYSDATE);
151 END IF;
152 END LOOP;
153 END IF;
154 CLOSE c_notify;
155 END wf_event_audience;
156 END IGS_PS_WF_EVENT_HOOK_PKG;
[Home] [Help]