[Home] [Help]
PACKAGE BODY: APPS.BIS_RG_SEND_NOTIFICATIONS_PVT
Source
1 PACKAGE BODY BIS_RG_SEND_NOTIFICATIONS_PVT as
2 /* $Header: BISVRGNB.pls 115.12 2003/10/28 08:02:30 nkishore noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile:~PROD:~PATH:~FILE
5 ----------------------------------------------------------------------------
6 -- PACKAGE: BIS_SCHEDULE_PVT
7 -- --
8 -- DESCRIPTION: Private package to create records in BIS_SCHEDULER
9 --
10 -- --
11 -- MODIFICATIONS --
12 -- Date User Modification
13 -- XX-XXX-XX XXXXXXXX Modifications made, which procedures changed & --
14 -- list bug number, if fixing a bug. --
15 -- --
16 -- 02-25-00 amkulkar Initial creation --
17 -- 05-16-01 mdamle Changed document from VARCHAR to clob --
18 -- 07-03-01 mdamle Scheduling Enhancements --
19 -- 08-13-01 mdamle Fixed Bug#1919709 --
20 -- 09-04-01 mdamle Scheduling Enhancements - Phase II - Multiple --
21 -- Preferences per schedule --
22 -- 07-28-03 nkishore Changed send_notification signature --
23 ----------------------------------------------------------------------------
24
25 -- mdamle 07/03/01 - Scheduling Enhancements
26 gvRoleName varchar2(20) := 'BIS_SCHEDULE_';
27
28 --Email Component include role
29 PROCEDURE SEND_NOTIFICATION
30 (p_user_id IN VARCHAR2
31 ,p_file_id IN VARCHAR2 DEFAULT NULL
32 ,p_schedule_id IN VARCHAR2 DEFAULT NULL
33 ,p_role IN VARCHAR2 DEFAULT NULL
34 ,p_title IN VARCHAR2 DEFAULT NULL
35 )
36 IS
37 l_wf_item_key NUMBER;
38 l_user_name VARCHAR2(32000);
39 l_title VARCHAR2(32000);
40 l_function_name VARCHAR2(32000);
41 l_file_id NUMBER;
42 l_schedule_id NUMBER;
43 l_url VARCHAR2(32000);
44 l_role_name VARCHAR2(30);
45 l_role_exists number;
46
47 -- mdamle 09/04/01 Scheduling Enhancements - Phase II - Multiple Preferences per schedule
48 /*
49 CURSOR c_sched IS
50 SELECT title, function_name, schedule_id
51 FROM bis_scheduler
52 WHERE schedule_id = p_schedule_id;
53 */
54 CURSOR c_sched IS
55 SELECT sp.file_id, sp.title, s.function_name, s.schedule_id
56 FROM bis_scheduler s, bis_schedule_preferences sp
57 WHERE sp.schedule_id = p_schedule_id
58 and sp.file_id = p_file_id
59 and s.schedule_id = sp.schedule_id
60 and sp.user_id = p_user_id
61 and sp.plug_id is null;
62
63 BEGIN
64
65 -- mdamle 07/03/01 - Scheduling Enhancements
66 -- Send to the Role setup for the schedule instead of the user
67 --Email Component check role and then assign it
68
69 SELECT user_name INTO l_user_name
70 FROM fnd_user WHERE user_id = p_user_id;
71
72 IF ( p_role is not null) then
73 l_role_name := p_role;
74 else
75
76 -- mdamle 09/04/01 Scheduling Enhancements - Phase II - Multiple Preferences per schedule
77 l_role_name := gvRoleName || p_file_id;
78 select count(1)
79 into l_role_exists
80 from WF_LOCAL_ROLES
81 where name = l_role_name;
82
83 if l_role_exists = 0 then
84 l_role_name := l_user_name;
85 end if;
86 end if;
87
88
89 IF (p_role is not null) THEN
90 l_file_id := p_file_id;
91 l_title := p_title;
92 ELSE
93 OPEN c_sched;
94 FETCH c_sched INTO l_file_id, l_title, l_function_name, l_schedule_id;
95 CLOSE c_sched;
96 IF (l_title IS NULL) THEN
97 -- mdamle 08/13/01 - Fixed Bug#1919709 --
98 -- l_title := l_function_name;
99 l_title := BIS_REPORT_UTIL_PVT.Get_Report_Title(l_function_name);
100 END IF;
101 END IF;
102
103
104 -- mdamle 05/16/01 - Commenting out the URL notification, retaining the
105 -- one with the Report Body
106 /*
107 SELECT bis_excpt_wf_s.nextval
108 INTO l_wf_item_key FROM dual;
109
110
111 --l_user_name := 'BISTESTER';
112 --Construct the URL to be sent
113 l_url := FND_WEB_CONFIG.TRAIL_SLASH(fnd_profile.value('APPS_WEB_AGENT'));
114 l_url := l_url ||'BIS_SAVE_REPORT.RETRIEVE?file_id='||l_File_id;
115 wf_engine.createprocess(itemtype=>'BISRGNOT'
116 ,itemkey =>l_wf_item_key
117 ,process=> 'BISSENDREPORTURL'
118 );
119 wf_engine.setitemattrtext(
120 itemtype => 'BISRGNOT'
121 ,itemkey => l_wf_item_key
122 ,aname => 'L_REPORTTITLE'
123 ,avalue => l_title
124 );
125 wf_engine.setitemattrtext(
126 itemtype => 'BISRGNOT'
127 ,itemkey => l_wf_item_key
128 ,aname => 'REPORTRECIPIENT'
129 ,avalue => l_user_name
130 );
131 wf_engine.setitemattrtext(
132 itemtype => 'BISRGNOT'
133 ,itemkey => l_wf_item_key
134 ,aname => 'L_REPORTURL'
135 ,avalue => l_url
136 );
137 wf_engine.startprocess
138 (itemtype => 'BISRGNOT'
139 ,itemkey => l_wf_item_key
140 );
141 */
142
143 SELECT bis_excpt_wf_s.nextval
144 INTO l_wf_item_key FROM dual;
145
146
147 wf_engine.createprocess(itemtype=> 'BISRGNOT'
148 ,itemkey=> l_wf_item_key
149 ,process => 'BISSENDHTMLBODY'
150 );
151
152 wf_engine.setitemattrtext(
153 itemtype => 'BISRGNOT'
154 ,itemkey => l_wf_item_key
155 ,aname => 'L_EMAIL_RECIPIENT'
156 -- mdamle 07/03/01 - Scheduling Enhancements
157 -- Send to the Role setup for the schedule instead of the user
158 ,avalue => l_role_name
159 );
160
161 wf_engine.setitemattrtext(
162 itemtype => 'BISRGNOT'
163 ,itemkey => l_wf_item_key
164 ,aname => 'L_FILE_ID'
165 ,avalue => l_file_id
166 );
167
168 wf_engine.setitemattrtext(
169 itemtype => 'BISRGNOT'
170 ,itemkey => l_wf_item_key
171 ,aname => 'L_REPORTTITLE'
172 ,avalue => l_title
173 );
174
175 wf_engine.setitemattrtext(
176 itemtype => 'BISRGNOT'
177 ,itemkey => l_wf_item_key
178 ,aname => '#FROM_ROLE'
179 ,avalue => l_user_name
180 );
181
182
183 wf_engine.startprocess
184 (itemtype => 'BISRGNOT'
185 ,itemkey => l_wf_item_key
186 );
187
188 EXCEPTION
189 WHEN FND_API.G_EXC_ERROR THEN
190 null;
191 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
192 null;
193 WHEN OTHERS THEN
194 null;
195 END;
196
197
198
199 PROCEDURE RETRIEVE_REPORT
200 (document_id IN VARCHAR2
201 ,display_Type IN VARCHAR2 DEFAULT 'TEXT/HTML'
202 ,document IN OUT NOCOPY clob
203 ,document_type IN OUT NOCOPY VARCHAR2
204 )
205 IS
206 l_Document VARCHAR2(32000);
207 l_img_html VARCHAR2(32000);
208 --l_html_pieces UTL_HTTP.HTML_PIECES;
209 l_html_pieces BIS_PMV_UTIL.lob_varchar_pieces;
210 l_count NUMBER;
211 l_length NUMBER := 1;
212
213 BEGIN
214
215 --l_img_html := bis_save_Report.returnURL(document_id);
216 --l_html_pieces := utl_http.request_pieces(l_img_html,32000);
217 -- mdamle 09/04/01 Scheduling Enhancements - Phase II - Multiple Preferences per schedule
218 select count(*)
219 into l_count
220 from fnd_lobs
221 where file_id = document_id;
222
223 if l_count > 0 then
224 l_html_pieces := BIS_PMV_UTIL.readfndlobs(document_id);
225 FOR l_count IN 1..l_html_pieces.COUNT LOOP
226 -- mdamle 05/16/01 - Changing document to clob - no longer a 32K limitation
227 l_document := l_html_pieces(l_count);
228 wf_notification.writetoclob(document, l_document);
229 END LOOP;
230 else
231 l_document := fnd_message.get_string('BIS', 'BIS_REPORT_DATA_PURGED');
232 wf_notification.writetoclob(document, l_document);
233 end if;
234
235 END;
236 END BIS_RG_SEND_NOTIFICATIONS_PVT;