[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_CC_NOTIFICATIONS_PKG
Source
1 PACKAGE BODY AP_WEB_CC_NOTIFICATIONS_PKG AS
2 /* $Header: apwcnotb.pls 120.2 2005/10/02 20:11:05 albowicz noship $ */
3
4 align_start constant varchar2(1) := 'S';
5 align_total constant varchar2(1) := 'T';
6 align_right constant varchar2(1) := 'R';
7 table_vertical constant varchar2(1) := 'V';
8 table_horizontal constant varchar2(1) := 'H';
9
10
11 --
12 -- Private Variables
13 --
14 table_direction varchar2(1) := 'L';
15 table_type varchar2(1) := 'V';
16 table_width varchar2(8) := '100%';
17 table_border varchar2(2) := '0';
18 table_cellpadding varchar2(2) := '3';
19 table_cellspacing varchar2(2) := '1';
20 table_bgcolor varchar2(7) := 'white';
21 th_bgcolor varchar2(7) := '#cccc99';
22 th_fontcolor varchar2(7) := '#336699';
23 th_fontface varchar2(80) := 'Arial, Helvetica, Geneva, sans-serif';
24 th_fontsize varchar2(2) := '2';
25 td_bgcolor varchar2(7) := '#f7f7e7';
26 td_fontcolor varchar2(7) := 'black';
27 td_fontface varchar2(80) := 'Arial, Helvetica, Geneva, sans-serif';
28 td_fontsize varchar2(2) := '2';
29
30 --
31 -- Private Functions
32 --
33
34 -- NTF_TABLE
35 -- Generate a "Browser Look and Feel (BLAF)" look a like table.
36 -- ADA compliance is achieved through "scope".
37 --
38 -- IN
39 -- cells - array of table cells
40 -- col - number of columns
41 -- type - Two character code. First determines header position.
42 -- - optional second denotes direction for Bi-Di support.
43 -- - V to generate a vertical table
44 -- - H to generate a horizontal table
45 -- - N to generate a mailer notification header table which
46 -- is a form of vertical
47 -- - *L Left to Right (default)
48 -- - *R Right to Left
49 -- rs - the result html code for the table
50 --
51 -- NOTE
52 -- type - Vertical table is Header always on the first column
53 -- - Horizontal table is Headers always on first row
54 -- - The direction can be omitted to which the default will be
55 -- - Left to Right.
56 --
57 -- cell has the format:
58 -- R40%:content of the cell here
59 -- ^ ^
60 -- | |
61 -- | + -- width specification
62 -- +-- align specification (L-Left, C-Center, R-Right, S-Start E-End)
63 --
64 procedure NTF_Table(cells in wf_notification.tdType,
65 col in pls_integer,
66 type in varchar2, -- 'V'ertical or 'H'orizontal
67 rs in out nocopy varchar2)
68 is
69 i pls_integer;
70 colon pls_integer;
71 modv pls_integer;
72 alignv varchar2(1);
73 l_align varchar2(8);
74 l_width varchar2(3);
75 l_text varchar2(4000);
76 l_type varchar2(1);
77 l_dir varchar2(1);
78 l_dirAttr varchar2(10);
79
80 -- Define a local set and initialize with the default
81 l_table_width varchar2(8) := table_width;
82 l_table_border varchar2(2) := table_border;
83 l_table_cellpadding varchar2(2) := table_cellpadding;
84 l_table_cellspacing varchar2(2) := table_cellspacing;
85 l_table_bgcolor varchar2(7) := table_bgcolor;
86 l_th_bgcolor varchar2(7) := th_bgcolor;
87 l_th_fontcolor varchar2(7) := th_fontcolor;
88 l_th_fontface varchar2(80) := th_fontface;
89 l_th_fontsize varchar2(2) := th_fontsize;
90 l_td_bgcolor varchar2(7) := td_bgcolor;
91 l_td_fontcolor varchar2(7) := td_fontcolor;
92 l_td_fontface varchar2(80) := td_fontface;
93 l_td_fontsize varchar2(2) := td_fontsize;
94
95 begin
96 if length(type) > 1 then
97 l_type := substrb(type, 1, 1);
98 l_dir := substrb(type,2, 1);
99 else
100 l_type := type;
101 l_dir := 'L';
102 end if;
103
104 if l_dir = 'L' then
105 l_dirAttr := NULL;
106 else
107 l_dirAttr := 'dir="RTL"';
108 end if;
109
110 if (l_type = 'N') then
111 -- Notification format. Alter the default colors.
112 l_table_bgcolor := '#FFFFFF';
113 l_th_bgcolor := '#FFFFFF';
114 l_th_fontcolor := '#000000';
115 l_td_bgcolor := '#FFFFFF';
116 l_td_fontcolor := '#000000';
117 l_table_cellpadding := '1';
118 l_table_cellspacing := '1';
119 end if;
120
121 if (cells.COUNT = 0) then
122 rs := null;
123 return;
124 end if;
125 rs := '<table width=100% border=0 cellpadding=0 cellspacing=0 '||l_dirAttr||
126 '><tr><td>';
127 rs := rs||wf_core.newline||'<table sumarry="" width='||l_table_width||
128 ' border='||l_table_border||
129 ' cellpadding='||l_table_cellpadding||
130 ' cellspacing='||l_table_cellspacing||
131 ' bgcolor='||l_table_bgcolor||' '||l_dirAttr||'>';
132
133 -- ### implement as generic log in the future
134 -- if (wf_notification.debug) then
135 -- dbms_output.put_line(to_char(cells.LAST));
136 -- end if;
137
138 for i in 1..cells.LAST loop
139 -- if (wf_notification.debug) then
140 -- dbms_output.put_line(substrb('('||to_char(i)||')='||cells(i),1,254));
141 -- end if;
142 modv := mod(i, col);
143 if (modv = 1) then
144 rs := rs||wf_core.newline||'<tr>';
145 end if;
146
147 alignv := substrb(cells(i), 1, 1);
148 if (alignv = 'R') then
149 l_align := 'RIGHT';
150 elsif (alignv = 'L') then
151 l_align := 'LEFT';
152 elsif (alignv = 'S') then
153 if (l_dir = 'L') then
154 l_align := 'LEFT';
155 else
156 l_align := 'RIGHT';
157 end if;
158 elsif (alignv = 'E') then
159 if (l_dir = 'L') then
160 l_align := 'RIGHT';
161 else
162 l_align := 'LEFT';
163 end if;
164 elsif (alignv = 'T') then
165 l_align := 'RIGHT';
166 else
167 l_align := 'CENTER';
168 end if;
169
170 -- if (wf_notification.debug) then
171 -- dbms_output.put_line('modv = '||to_char(modv));
172 -- end if;
173
174 colon := instrb(cells(i),':');
175 l_width := substrb(cells(i), 2, colon-2);
176 l_text := substrb(cells(i), colon+1); -- what is after the colon
177
178 if ((l_type = 'V' and modv = 1) or (l_type = 'N' and modv = 1)
179 or (l_type = 'H' and i <= col)
180 or (alignv = 'T') ) then
181 if (l_type = 'N') then
182 rs := rs||wf_core.newline||'<td';
183 else
184 -- this is a header
185 rs := rs||wf_core.newline||'<th';
186 end if;
187 if (l_type = 'V') then
188 rs := rs||' scope=row';
189 else
190 rs := rs||' scope=col';
191 end if;
192
193 if (l_width is not null) then
194 rs := rs||' width='||l_width;
195 end if;
196 rs := rs||' align='||l_align||' valign=baseline bgcolor='||
197 l_th_bgcolor||'>';
198 rs := rs||'<font color='||l_th_fontcolor||' face="'||l_th_fontface||'"'
199 ||' size='||l_th_fontsize||'>';
200 rs := rs||l_text||'</font>';
201 if (l_type = 'N') then
202 rs := rs||'</td>';
203 else
204 rs := rs||'</th>';
205 end if;
206 else
207 -- this is regular data
208 rs := rs||wf_core.newline||'<td';
209 if (l_width is not null) then
210 rs := rs||' width='||l_width;
211 end if;
212 rs := rs||' align='||l_align||' valign=baseline bgcolor='||
213 l_td_bgcolor||'>';
214 rs := rs||'<font color='||l_td_fontcolor||' face="'||l_td_fontface||'"'
215 ||' size='||l_td_fontsize||'>';
216 if (l_type = 'N') then
217 rs := rs||'<b>'||l_text||'</b></font></td>';
218 else
219 rs := rs||l_text||'</font></td>';
220 end if;
221 end if;
222 if (modv = 0) then
223 rs := rs||wf_core.newline||'</tr>';
224 end if;
225 end loop;
226 rs := rs||wf_core.newline||'</table>'||wf_core.newline||'</td></tr></table>';
227
228 exception
229 when OTHERS then
230 wf_core.context('Wf_Notification', 'NTF_Table',to_char(col),l_type);
231 raise;
232 end NTF_Table;
233
234
235 FUNCTION formattitle(title IN VARCHAR2) RETURN VARCHAR2 IS
236 BEGIN
237 RETURN '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">'||
238 '<tr><td width="100%" class="OraHeader">' || title ||
239 '</td></tr><tr><td class="OraBGAccentDark"></td></tr></table>';
240 END formattitle;
241
242 FUNCTION CELL_TEXT(cell IN VARCHAR2) RETURN VARCHAR2 IS
243 BEGIN
244 RETURN SUBSTR(cell, INSTR(cell, ':')+1);
245 END CELL_TEXT;
246
247 PROCEDURE GET_NEW_CARD_NOTIFICATION(document_id IN VARCHAR2,
248 display_type IN VARCHAR2,
249 document IN OUT NOCOPY CLOB,
250 document_type IN OUT NOCOPY VARCHAR2) IS
251 colon number;
252 l_request_id number;
253 l_card_program_id number;
254 l_new_count number := 0;
255 l_unassigned_count number := 0;
256 l_inactive_count number := 0;
257 l_active_count number := 0;
258 l_registered_count number := 0;
259
260 buf varchar2(2000);
261 title VARCHAR2(200);
262 cells wf_notification.tdType;
263 cellcnt number;
264 BEGIN
265 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
266 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
267 'ap.pls.AP_WEB_CC_NOTIFICATIONS_PKG.GET_NEW_CARD_NOTIFICATION',
268 'Document = '||display_type||','||document_id);
269 end if;
270
271 title := fnd_message.get_string('SQLAP', 'OIE_CC_INACTIVE_TITLE');
272
273 colon := instr(document_id, ':');
274 l_request_id := to_number(substr(document_id, 1, colon-1));
275 l_card_program_id := to_number(substr(document_id, colon+1));
276
277 select count(*), sum(decode(employee_id, null, 1, 0)), sum(decode(employee_id, null, 0, 1))
278 into l_new_count, l_registered_count, l_active_count
279 from ap_cards_all
280 where request_id = l_request_id;
281
282 select count(*) into l_inactive_count from ap_cards_all c
283 where request_id = l_request_id
284 and employee_id is null
285 and 1 = (select count(*) from ap_card_emp_candidates e
286 where e.card_id = c.card_id);
287
288 l_unassigned_count := l_registered_count - l_inactive_count;
289
290 cellcnt := 0;
291 cells(cellcnt+1) := align_start||':'||FND_MESSAGE.get_string('SQLAP', 'OIE_CC_STATUS');
292 cells(cellcnt+2) := align_right||':'||FND_MESSAGE.get_string('SQLAP', 'OIE_CC_COUNT');
293 cellcnt := cellcnt + 2;
294
295 -- Unassigned Credit Cards
296 cells(cellcnt+1) := align_start||':'||FND_MESSAGE.get_string('SQLAP', 'OIE_CC_UNASSIGNED_CARDS');
297 cells(cellcnt+2) := align_right||':'||to_char(l_unassigned_count);
298 cellcnt := cellcnt + 2;
299
300 -- Inactive Credit Cards
301 cells(cellcnt+1) := align_start||':'||FND_MESSAGE.get_string('SQLAP', 'OIE_CC_INACTIVE_CARDS');
302 cells(cellcnt+2) := align_right||':'||to_char(l_inactive_count);
303 cellcnt := cellcnt + 2;
304
305 -- Credit Cards Activated
306 cells(cellcnt+1) := align_start||':'||FND_MESSAGE.get_string('SQLAP', 'OIE_CC_ACTIVATED_CARDS');
307 cells(cellcnt+2) := align_right||':'||to_char(l_active_count);
308 cellcnt := cellcnt + 2;
309
310 -- New Credit Cards Registered
311 cells(cellcnt+1) := align_total||':'||FND_MESSAGE.get_string('SQLAP', 'OIE_CC_TOTAL_PROMPT');
312 cells(cellcnt+2) := align_right||':'||to_char(l_new_count);
313 cellcnt := cellcnt + 2;
314
315 IF display_type = WF_NOTIFICATION.doc_text THEN
316 buf := title || fnd_global.local_chr(10);
317 for i in 1..cellcnt/2 loop
318 buf := buf || cell_text(cells(i*2-1))||': '||cell_text(cells(i*2))||fnd_global.local_chr(10);
319 end loop;
320 document_type := WF_NOTIFICATION.doc_text;
321 ELSE
322 ntf_table(cells,2,table_horizontal,buf);
323 buf := '<h1>'||formattitle(title)||'</h1>'||buf;
324 document_type := WF_NOTIFICATION.doc_html;
325 END IF;
326 WF_NOTIFICATION.writetoclob(document, buf);
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
331 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
332 'ap.pls.AP_WEB_CC_NOTIFICATIONS_PKG.GET_NEW_CARD_NOTIFICATION',
333 sqlerrm);
334 end if;
335 RAISE;
336 END GET_NEW_CARD_NOTIFICATION;
337
338 PROCEDURE GET_VAL_ERROR_NOTIFICATION(document_id IN VARCHAR2,
339 display_type IN VARCHAR2,
340 document IN OUT NOCOPY CLOB,
341 document_type IN OUT NOCOPY VARCHAR2) IS
342 colon number;
343 l_request_id number;
344 l_card_program_id number;
345
346 buf varchar2(2000);
347 title VARCHAR2(200);
348 cells wf_notification.tdType;
349 cellcnt number;
350
351 cursor cvalidation is
352 select lookup_code, displayed_field
353 from ap_lookup_codes
354 where lookup_type = 'OIE_CC_VALIDATION_ERROR'
355 and lookup_code not in ('INVALID_ALL' );
356 valcnt NUMBER;
357 totcnt NUMBER;
358 BEGIN
359 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
360 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
361 'ap.pls.AP_WEB_CC_NOTIFICATIONS_PKG.GET_VAL_ERROR_NOTIFICATION',
362 'Document = '||display_type||','||document_id);
363 end if;
364
365 title := fnd_message.get_string('SQLAP', 'OIE_CC_INVALID_TITLE');
366
367 colon := instr(document_id, ':');
368 l_request_id := to_number(substr(document_id, 1, colon-1));
369 l_card_program_id := to_number(substr(document_id, colon+1));
370
371
372 cells(1) := align_start||':'||FND_MESSAGE.get_string('SQLAP', 'OIE_CC_VALIDATION_PROMPT');
373 cells(2) := align_right||':'||FND_MESSAGE.get_string('SQLAP', 'OIE_CC_COUNT');
374 cellcnt := 2;
375
376 totcnt := 0;
380 WHERE request_id = l_request_id
377 FOR cvalrec IN cvalidation LOOP
378 SELECT COUNT(*) INTO valcnt
379 FROM AP_CREDIT_CARD_TRXNS_ALL
381 AND validate_code = cvalrec.lookup_code;
382
383 IF valcnt > 0 THEN
384 cells(cellcnt+1) := align_start||':'||cvalrec.displayed_field;
385 cells(cellcnt+2) := align_right||':'||to_char(valcnt);
386 cellcnt := cellcnt + 2;
387
388 totcnt := totcnt + valcnt;
389 END IF;
390 END LOOP;
391
392 cells(cellcnt+1) := align_total||':'||FND_MESSAGE.get_string('SQLAP', 'OIE_CC_TOTAL_PROMPT');
393 cells(cellcnt+2) := align_right||':'||to_char(totcnt);
394 cellcnt := cellcnt + 2;
395
396 IF display_type = WF_NOTIFICATION.doc_text THEN
397 buf := title || fnd_global.local_chr(10);
398 for i in 2..cellcnt/2 loop
399 buf := buf || cell_text(cells(i*2-1))||': '||cell_text(cells(i*2))||fnd_global.local_chr(10);
400 end loop;
401 document_type := WF_NOTIFICATION.doc_text;
402 ELSE
403 ntf_table(cells,2,table_horizontal,buf);
404 buf := '<h1>'||formattitle(title)||'</h1>'||buf;
405 document_type := WF_NOTIFICATION.doc_html;
406 END IF;
407 WF_NOTIFICATION.writetoclob(document, buf);
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
412 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
413 'ap.pls.AP_WEB_CC_NOTIFICATIONS_PKG.GET_VAL_ERROR_NOTIFICATION',
414 sqlerrm);
415 end if;
416 RAISE;
417 END GET_VAL_ERROR_NOTIFICATION;
418
419
420 FUNCTION GET_INACTIVE_COUNT(l_request_id NUMBER) return NUMBER
421 IS
422 l_inactive_count NUMBER;
423 BEGIN
424 select count(*) into l_inactive_count from ap_cards_all c
425 where request_id = l_request_id
426 and employee_id is null
427 and 1 = (select count(*) from ap_card_emp_candidates e
428 where e.card_id = c.card_id);
429 return l_inactive_count;
430
431 EXCEPTION
432 WHEN NO_DATA_FOUND THEN
433 RETURN 0;
434 END;
435 END AP_WEB_CC_NOTIFICATIONS_PKG;