1 PACKAGE BODY igs_pr_prog_out_wf_pkg AS
2 /* $Header: IGSPR36B.pls 120.0 2005/07/05 12:02:40 appldev noship $ */
3
4
5
6 PROCEDURE find_prog_out_notification(
7 itemtype IN VARCHAR2,
8 itemkey IN VARCHAR2,
9 actid IN NUMBER,
10 funcmode IN VARCHAR2,
11 resultout IN OUT NOCOPY VARCHAR2
12 ) AS
13 /******************************************************************
14 Created By : Deepankar Dey
15 Date Created By : 30-Sept-2002
16 Purpose : This procedure catches the event and it decides which notification
17 is to be send .
18 Change History
19 Who When What
20 ******************************************************************/
21
22
23
24 l_event_name VARCHAR2(2000);
25 l_show_cause_expiry_dt VARCHAR2(100);
26 l_appeal_expiry_dt VARCHAR2(100);
27 l_person_id NUMBER;
28
29
30
31 -- cursor to get the user_name corresponding to the person_id
32
33 CURSOR c_user_name (cp_person_id igs_pe_person.person_id%TYPE) IS
34 SELECT user_name
35 FROM fnd_user
36 WHERE person_party_id = cp_person_id;
37
38 CURSOR c_student_info (cp_person_id igs_pe_person.person_id%TYPE) IS
39 SELECT person_pre_name_adjunct,party_name
40 FROM hz_parties
41 WHERE party_id = cp_person_id ;
42
43 CURSOR c_program_title (cp_course_cd igs_ps_ver.course_cd%TYPE) IS
44 SELECT title
45 FROM igs_ps_ver
46 WHERE course_cd = cp_course_cd ;
47
48 l_user_name fnd_user.user_name%TYPE;
49 l_person_pre_name_adjunct hz_parties.person_pre_name_adjunct%TYPE;
50 l_party_name hz_parties.party_name%TYPE;
51 l_title igs_ps_ver.title%TYPE ;
52 l_course_cd igs_ps_ver.course_cd%TYPE ;
53
54
55 BEGIN
56
57
58 l_event_name := Wf_Engine.GetItemAttrText(itemtype,itemkey,'IA_EVENT_NAME');
59 l_show_cause_expiry_dt := Wf_Engine.GetItemAttrText(itemtype,itemkey,'IA_SHOW_CAUSE_EXPIRY_DT');
60 l_appeal_expiry_dt := Wf_Engine.GetItemAttrText(itemtype,itemkey,'IA_APPEAL_EXPIRY_DT');
61 l_person_id := Wf_Engine.GetItemAttrText(itemtype,itemkey,'IA_PERSON_ID');
62 l_course_cd := Wf_Engine.GetItemAttrText(itemtype,itemkey,'IA_COURSE_CD');
63
64
65 OPEN c_user_name(l_person_id);
66 FETCH c_user_name INTO l_user_name;
67 CLOSE c_user_name ;
68
69 OPEN c_student_info (l_person_id);
70 FETCH c_student_info INTO l_person_pre_name_adjunct,l_party_name;
71 CLOSE c_student_info;
72
73 OPEN c_program_title (l_course_cd);
74 FETCH c_program_title INTO l_title;
75 CLOSE c_program_title;
76
77 IF (l_event_name = 'oracle.apps.igs.pr.approve_otcm') THEN
78
79 IF ((l_show_cause_expiry_dt IS NOT NULL) AND (l_appeal_expiry_dt IS NOT NULL )) THEN
80
81
82 -- This part of the package would be customized as per the customer requirement .
83 NULL;
84
85 -- This is to route flow to the correcponding Notification Activity ,
86 -- which has the Message in the Message Body, which will show Show Cause Expiry Date and Appeal Expiry Date
87
88 resultout := 'COMPLETE:DEFAULT';
89
90 ELSIF ((l_show_cause_expiry_dt IS NOT NULL) AND (l_appeal_expiry_dt IS NULL )) THEN
91
92
93
94 -- This is to route flow to the correcponding Notification Activity ,
95 -- which has the Message in the Message Body, which will show only Show Cause Expiry Date
96
97 resultout := 'COMPLETE:TWO';
98
99
100
101 -- Setting the values for the User Name
102
103 Wf_Engine.SetItemAttrText( ItemType => itemtype,
104 ItemKey => itemkey,
105 aname => 'IA_USER_NAME',
106 avalue => l_user_name
107 );
108
109 -- Setting the values for the Person Name
110
111 Wf_Engine.SetItemAttrText( ItemType => itemtype,
112 ItemKey => itemkey,
113 aname => 'IA_PERSON_NAME',
114 avalue => l_party_name
115 );
116
117
118 -- Setting the values for the Person Pre Name Adjunct
119
120 Wf_Engine.SetItemAttrText( ItemType => itemtype,
121 ItemKey => itemkey,
122 aname => 'IA_PERSON_PREFIX',
123 avalue => l_person_pre_name_adjunct
124 );
125
126 -- Setting the values for the Person Pre Name Adjunct
127
128 Wf_Engine.SetItemAttrText( ItemType => itemtype,
129 ItemKey => itemkey,
130 aname => 'IA_PROGRAM_TITLE',
131 avalue => l_title
132 );
133
134
135
136 ELSIF ((l_show_cause_expiry_dt IS NULL) AND (l_appeal_expiry_dt IS NOT NULL )) THEN
137
138
139 -- This part of the package would be customized as per the customer requirement .
140 NULL;
141
142 -- This is to route flow to the correcponding Notification Activity ,
143 -- which has the Message in the Message Body, which will show only Appeal Expiry Date
144
145 resultout := 'COMPLETE:DEFAULT';
146
147 ELSIF ((l_show_cause_expiry_dt IS NULL) AND (l_appeal_expiry_dt IS NOT NULL )) THEN
148
149 -- This part of the package would be customized as per the customer requirement .
150 NULL;
151
152 -- This is to route flow to the correcponding Notification Activity ,
153 -- which has the Message in the Message Body, which will show message without Show Cause Expiry Date and Appeal Expiry Date
154
155 resultout := 'COMPLETE:DEFAULT';
156
157 END IF ;
158
159 ELSIF (l_event_name = 'oracle.apps.igs.pr.apply_positive_otcm') THEN
160
161
162 -- This part of the package would be customized as per the customer requirement .
163 NULL;
164
165 -- This is to route flow to the correcponding Notification Activity ,
166 -- correcponding Notification Activity , which has the corresponding Message in the Message Body
167
168 resultout := 'COMPLETE:DEFAULT';
169
170 ELSIF (l_event_name = 'oracle.apps.igs.pr.apply_otcm') THEN
171
172 -- This part of the package would be customized as per the customer requirement .
173 NULL;
174
175 -- This is to route flow to the correcponding Notification Activity ,
176 -- correcponding Notification Activity , which has the corresponding Message in the Message Body
177
178 resultout := 'COMPLETE:DEFAULT';
179
180 ELSIF (l_event_name = 'oracle.apps.igs.pr.showcause_uph_dsm') THEN
181
182 -- This part of the package would be customized as per the customer requirement .
183 NULL;
184
185 -- This is to route flow to the correcponding Notification Activity ,
186 -- correcponding Notification Activity , which has the corresponding Message in the Message Body
187
188 resultout := 'COMPLETE:DEFAULT';
189
190 ELSIF (l_event_name = 'oracle.apps.igs.pr.appeal_uph_dsm') THEN
191
192
193 -- This part of the package would be customized as per the customer requirement .
194 NULL;
195
196 -- This is to route flow to the correcponding Notification Activity ,
197 -- correcponding Notification Activity , which has the corresponding Message in the Message Body
198
199 resultout := 'COMPLETE:DEFAULT';
200
201 ELSIF (l_event_name = 'oracle.apps.igs.pr.remove_waive_cancel_otcm') THEN
202
203 -- This part of the package would be customized as per the customer requirement .
204 NULL;
205
206 -- This is to route flow to the correcponding Notification Activity ,
207 -- correcponding Notification Activity , which has the corresponding Message in the Message Body
208
209 resultout := 'COMPLETE:DEFAULT';
210 END IF;
211
212
213 END find_prog_out_notification;
214
215
216 END igs_pr_prog_out_wf_pkg;