[Home] [Help]
PACKAGE BODY: APPS.FND_DELIVERY
Source
1 package body FND_DELIVERY as
2 /* $Header: AFCPDELB.pls 120.8.12020000.2 2013/02/22 21:32:32 ckclark ship $ */
3
4
5
6 --
7 -- PUBLIC FUNCTIONS
8 --
9
10
11 function add_email (subject in varchar2,
12 from_address in varchar2,
13 to_address in varchar2,
14 cc in varchar2 default null,
15 lang in varchar2 default null) return boolean is
16
17 begin
18
19 if (subject is null or from_address is null or to_address is null) then
20 return false;
21 end if;
22
23 return fnd_request.add_delivery_option(type => fnd_delivery.type_email,
24 p_argument1 => subject,
25 p_argument2 => from_address,
26 p_argument3 => to_address,
27 p_argument4 => cc,
28 nls_language => lang);
29
30 end add_email;
31
32
33 function add_ipp_printer (printer_name in varchar2,
34 copies in number default null,
35 orientation in varchar2 default null,
36 username in varchar2 default null,
37 password in varchar2 default null,
38 lang in varchar2 default null) return boolean is
39 printer_id number;
40
41 begin
42
43 select delivery_id
44 into printer_id
45 from fnd_cp_ipp_printers
46 where ipp_printer_name = printer_name;
47
48 return add_ipp_printer(printer_id, copies, orientation, username, password, lang);
49
50 exception
51 when others then
52 return false;
53
54 end add_ipp_printer;
55
56
57 function add_ipp_printer (printer_id in number,
58 copies in number default null,
59 orientation in varchar2 default null,
60 username in varchar2 default null,
61 password in varchar2 default null,
62 lang in varchar2 default null) return boolean is
63
64 cnt number;
65 svc_key varchar2(16) := null;
66
67 begin
68
69 if (printer_id is null) then
70 return false;
71 end if;
72
73 if (orientation is not null and
74 orientation <> fnd_delivery.orientation_portrait and
75 orientation <> fnd_delivery.orientation_landscape) then
76 return false;
77 end if;
78
79
80 select count(*)
81 into cnt
82 from fnd_cp_ipp_printers
83 where delivery_id = printer_id;
84
85 if (cnt = 0) then
86 return false;
87 end if;
88
89 if (username is not null and password is not null) then
90 svc_key := set_temp_credentials(username, password);
91 end if;
92
93
94 return fnd_request.add_delivery_option(type => fnd_delivery.type_ipp_printer,
95 p_argument1 => printer_id,
96 p_argument2 => copies,
97 p_argument3 => orientation,
98 p_argument4 => username,
99 p_argument5 => null,
100 p_argument6 => svc_key,
101 nls_language => lang);
102
103
104 end add_ipp_printer;
105
106
107
108 function add_fax ( server_name in varchar2,
109 fax_number in varchar2,
110 username in varchar2 default null,
111 password in varchar2 default null,
112 lang in varchar2 default null) return boolean is
113
114 server_id number;
115
116 begin
117
118 select delivery_id
119 into server_id
120 from fnd_cp_ipp_printers
121 where ipp_printer_name = server_name;
122
123 return add_fax(server_id, fax_number, username, password, lang);
124
125 exception
126 when others then
127 return false;
128
129 end add_fax;
130
131
132
133 function add_fax ( server_id in number,
134 fax_number in varchar2,
135 username in varchar2 default null,
136 password in varchar2 default null,
137 lang in varchar2 default null) return boolean is
138 cnt number;
139 svc_key varchar2(16) := null;
140
141 begin
142
143 if (server_id is null or fax_number is null) then
144 return false;
145 end if;
146
147 select count(*)
148 into cnt
149 from fnd_cp_ipp_printers
150 where delivery_id = server_id
151 and support_fax = 'Y';
152
153 if (cnt = 0) then
154 return false;
155 end if;
156
157 if (username is not null and password is not null) then
158 svc_key := set_temp_credentials(username, password);
159 end if;
160
161 return fnd_request.add_delivery_option(type => fnd_delivery.type_ipp_fax,
162 p_argument1 => server_id,
163 p_argument2 => fax_number,
164 p_argument3 => username,
165 p_argument4 => null,
166 p_argument5 => svc_key,
167 nls_language => lang);
168
169 end add_fax;
170
171
172
173 function add_ftp ( server in varchar2,
174 username in varchar2,
175 password in varchar2,
176 remote_dir in varchar2,
177 port in varchar2 default null,
178 secure in boolean default FALSE,
179 lang in varchar2 default null) return boolean is
180
181 stype varchar2(1) := fnd_delivery.type_ftp;
182 svc_key varchar2(16) := null;
183
184 begin
185
186 if (secure) then
187 stype := fnd_delivery.type_sftp;
188 if (server is null or username is null or password is null) then
189 return false;
190 end if;
191 else
192 if (server is null or username is null or password is null or remote_dir is null) then
193 return false;
194 end if;
195 end if;
196
197 svc_key := set_temp_credentials(username, password);
198
199 return fnd_request.add_delivery_option(type => stype,
200 p_argument1 => server,
201 p_argument2 => username,
202 p_argument3 => null,
203 p_argument4 => remote_dir,
204 p_argument5 => port,
205 p_argument8 => svc_key,
206 nls_language => lang);
207
208 end add_ftp;
209
210
211
212 function add_webdav ( server in varchar2,
213 remote_dir in varchar2,
214 port in varchar2 default null,
215 username in varchar2 default null,
216 password in varchar2 default null,
217 authtype in varchar2 default null,
218 enctype in varchar2 default null,
219 lang in varchar2 default null) return boolean is
220
221 svc_key varchar2(16) := null;
222 begin
223
224 if (server is null or remote_dir is null) then
225 return false;
226 end if;
227
228 if (username is not null and password is not null) then
229 svc_key := set_temp_credentials(username, password);
230 end if;
231 return fnd_request.add_delivery_option(type => fnd_delivery.type_webdav,
232 p_argument1 => server,
233 p_argument2 => remote_dir,
234 p_argument3 => port,
235 p_argument4 => username,
236 p_argument5 => null,
237 p_argument6 => authtype,
238 p_argument7 => enctype,
239 p_argument8 => svc_key,
240 nls_language => lang);
241
242 end add_webdav;
243
244
245 function add_http ( server in varchar2,
246 remote_dir in varchar2,
247 port in varchar2 default null,
248 username in varchar2 default null,
249 password in varchar2 default null,
250 authtype in varchar2 default null,
251 enctype in varchar2 default null,
252 method in varchar2 default null,
253 lang in varchar2 default null) return boolean is
254
255 svc_key varchar2(16) := null;
256 begin
257
258 if (server is null or remote_dir is null) then
259 return false;
260 end if;
261
262 if (username is not null and password is not null) then
263 svc_key := set_temp_credentials(username, password);
264 end if;
265
266 return fnd_request.add_delivery_option(type => fnd_delivery.type_http,
267 p_argument1 => server,
268 p_argument2 => remote_dir,
269 p_argument3 => port,
270 p_argument4 => username,
271 p_argument5 => null,
272 p_argument6 => authtype,
273 p_argument7 => enctype,
274 p_argument8 => method,
275 p_argument9 => svc_key,
276 nls_language => lang);
277
278 end add_http;
279
280
281
282 function add_custom ( custom_id in number,
283 lang in varchar2 default null) return boolean is
284
285 cnt number;
286
287 begin
288
289 if (custom_id is null) then
290 return false;
291 end if;
292
293 select count(*)
294 into cnt
295 from fnd_cp_delivery_commands
296 where delivery_id = custom_id;
297
298 if (cnt = 0) then
299 return false;
300 end if;
301
302 return fnd_request.add_delivery_option(type => fnd_delivery.type_custom,
303 p_argument1 => custom_id,
304 nls_language => lang);
305
306 end add_custom;
307
308
309 function add_custom ( custom_name in varchar2,
310 lang in varchar2 default null) return boolean is
311
312 custom_id number;
313
314 begin
315
316 select delivery_id
317 into custom_id
318 from fnd_cp_delivery_options
319 where delivery_name = custom_name;
320
321 return add_custom(custom_id, lang);
322
323 exception
324 when others then
325 return false;
326
327 end add_custom;
328
329 function add_burst return boolean is
330
331 begin
332
333 return fnd_request.add_delivery_option(type => fnd_delivery.type_burst);
334
335 end add_burst;
336
337 procedure set_smtp_credentials( username in varchar2,
338 smtp_user in varchar2,
339 smtp_pass in varchar2) is
340
341 begin
342
343 fnd_vault.put(SMTP_SERVICE, username, smtp_user || ':' || smtp_pass);
344
345 end set_smtp_credentials;
346
347
348 procedure get_smtp_credentials( username in varchar2,
349 smtp_user out nocopy varchar2,
350 smtp_pass out nocopy varchar2) is
351
352 unpw varchar2(128);
353
354 begin
355
356 unpw := fnd_vault.get(SMTP_SERVICE, username);
357 if (unpw is null) then
358 smtp_user := null;
359 smtp_pass := null;
360 return;
361 end if;
362
363 smtp_user := substr(unpw, 1, instr(unpw, ':') - 1);
364 smtp_pass := substr(unpw, instr(unpw, ':') + 1);
365
366 end get_smtp_credentials;
367
368 function set_temp_credentials (username in varchar2,
369 password in varchar2) return varchar2 is
370 svc_key varchar2(16);
371
372 begin
373
374 select DELIVERY_SERVICE || to_char(FND_CP_DELIVERY_OPTIONS_S.nextval)
375 into svc_key
376 from dual;
377
378 fnd_vault.put(svc_key, username, password);
379
380 return svc_key;
381
382 end set_temp_credentials;
383
384 function get_temp_credentials (svc_key in varchar2,
385 username in varchar2,
386 delflag in varchar2 default 'Y') return varchar2 is
387
388 password varchar2(128);
389
390 begin
391 password := fnd_vault.get(svc_key, username);
392
393 if (delflag = 'Y') then
394 del_temp_credentials(svc_key, username);
395 end if;
396
397 return password;
398
399 end get_temp_credentials;
400
401 procedure del_temp_credentials (svc_key in varchar2,
402 username in varchar2) is
403
404 begin
405
406 fnd_vault.del(svc_key, username);
407
408 end del_temp_credentials;
409
410
411
412 function has_lob_of_type ( prog_app_name IN varchar2,
413 conc_prog_name IN varchar2,
414 lob_of_type IN varchar2,
415 nls_lang IN varchar2 default null,
416 nls_terry IN varchar2 default null) return boolean is
417
418 iso_lang fnd_languages.iso_language%TYPE := null;
419 iso_terry fnd_languages.iso_territory%TYPE := null;
420 dummy number;
421
422 begin
423
424 if nls_lang is not null then
425 SELECT distinct upper(iso_language)
426 INTO iso_lang
430
427 FROM fnd_languages l
428 WHERE upper(l.nls_language) = upper(nls_lang);
429 end if;
431 if nls_terry is not null then
432 SELECT distinct upper(iso_territory)
433 INTO iso_terry
434 FROM fnd_languages l
435 WHERE upper(l.nls_territory) = upper(nls_terry);
436 end if;
437
438 SELECT count(*)
439 INTO dummy
440 FROM xdo_lobs
441 WHERE lob_code = upper(conc_prog_name)
442 AND application_short_name = upper(prog_app_name)
443 AND lob_type = upper(lob_of_type)
444 AND upper(language) = decode(language, '00', '00', decode(iso_lang, null, upper(language), iso_lang))
445 AND upper(territory) = decode(territory, '00', '00', decode(iso_terry, null, upper(territory), iso_terry));
446
447 if dummy > 0 then
448 return true;
449 else
450 return false;
451 end if;
452
453 exception
454 when others then
455 return false;
456
457 end has_lob_of_type;
458
459 function has_lob_of_type ( reqid IN number,
460 lob_of_type IN varchar2) return boolean is
461
462 prog_app_name fnd_application.application_short_name%TYPE;
463 conc_prog_name fnd_concurrent_programs.concurrent_program_name%TYPE;
464 nls_lang fnd_concurrent_requests.nls_language%TYPE;
465 nls_terry fnd_concurrent_requests.nls_territory%TYPE;
466
467 begin
468
469 SELECT a.application_short_name, p.concurrent_program_name,
470 r.nls_language, r.nls_territory
471 INTO prog_app_name, conc_prog_name,
472 nls_lang, nls_terry
473 FROM fnd_concurrent_requests r, fnd_concurrent_programs p,
474 fnd_application a
475 WHERE r.program_application_id = p.application_id
476 AND r.concurrent_program_id = p.concurrent_program_id
477 AND p.application_id = a.application_id
478 AND r.request_id = reqid;
479
480 return (has_lob_of_type(prog_app_name, conc_prog_name, lob_of_type, nls_lang, nls_terry));
481
482 exception
483 when others then
484 return false;
485
486 end has_lob_of_type;
487
488 function has_delivery_of_type ( reqid IN number,
489 delivery_type IN varchar2) return boolean is
490
491 dummy number;
492
493 begin
494
495 SELECT count(*)
496 INTO dummy
497 FROM fnd_conc_pp_actions pp,
498 fnd_concurrent_requests cr
499 WHERE pp.concurrent_request_id = reqid
500 AND pp.argument1= delivery_type
501 AND pp.concurrent_request_id = cr.request_id
502 AND action_type in (7,8);
503
504 if dummy > 0 then
505 return true;
506 end if;
507
508 SELECT count(*)
509 INTO dummy
510 FROM fnd_run_req_pp_actions rr
511 WHERE rr.parent_request_id = reqid
512 AND rr.argument1= delivery_type
513 AND action_type in (7,8);
514
515 if dummy > 0 then
516 return true;
517 else
518 return false;
519 end if;
520
521 exception
522 when others then
523 return false;
524
525 end has_delivery_of_type;
526
527
528 function post_processing_results ( reqid IN number )
529 return varchar2 is
530
531 i number;
532 len_sum number;
533 len_each number;
534 len_save number;
535 concat_rows varchar2(32767);
536
537 -- Use tokens to create headers...
538 publisher_breaker varchar2(100);
539 plen_breaker number;
540 delivery_breaker varchar2(100);
541 dlen_breaker number;
542 burst_breaker varchar2(100);
543 blen_breaker number;
544 l_newline varchar2(10);
545 no_results varchar2(25);
546 nr_len number;
547
548 cursor c1 is
549 select publisher_return_results, action_type, argument1
550 from FND_CONC_PP_ACTIONS
551 where concurrent_request_id = reqid
552 and action_type >= 6
553 order by action_type, argument1;
554
555 begin
556 i := 0;
557 l_newline := fnd_global.newline;
558
559 -- Get headers
560 fnd_message.set_name('FND', 'FND-PUBLISHER_HEADER');
561 publisher_breaker := fnd_message.get;
562 plen_breaker := lengthb(publisher_breaker);
563 fnd_message.set_name('FND', 'FND-DELIVERY_HEADER');
564 delivery_breaker := fnd_message.get;
565 dlen_breaker := lengthb(delivery_breaker);
566 fnd_message.set_name('FND', 'FND-BURST_HEADER');
567 burst_breaker := fnd_message.get;
568 blen_breaker := lengthb(burst_breaker);
569 fnd_message.set_name('FND', 'FND_DEF_ALTERNATE_TEXT');
570 no_results := fnd_message.get;
571 nr_len := lengthb(no_results);
572
573 len_sum := 0;
574
575 for l_rec in c1 loop
576 if (l_rec.publisher_return_results is null) then
577 if (l_rec.action_type = 6) then
578 concat_rows := concat_rows||l_newline||publisher_breaker||l_newline||no_results||l_newline;
579 len_sum := len_sum + plen_breaker + nr_len + 6;
580 elsif (l_rec.action_type = 7) then
581 concat_rows := concat_rows||l_newline||delivery_breaker||l_newline||no_results||l_newline;
582 len_sum := len_sum + dlen_breaker + nr_len + 6;
583 elsif (l_rec.action_type = 8) then
584 concat_rows := concat_rows||l_newline||burst_breaker||l_newline||no_results||l_newline;
585 len_sum := len_sum + blen_breaker + nr_len + 6;
586 else
587 concat_rows := concat_rows||l_newline||no_results||l_newline;
588 len_sum := len_sum + nr_len + 4;
589 end if;
590 else
591 len_each := lengthb(l_rec.publisher_return_results);
592 if ((len_sum + len_each + plen_breaker) <= 32767) then
593 if (l_rec.action_type = 6) then
594 concat_rows := concat_rows||l_newline||publisher_breaker||l_newline||
595 l_rec.publisher_return_results||l_newline;
596 len_sum := len_sum + plen_breaker + len_each + 6;
597 elsif (l_rec.action_type = 7) then
598 concat_rows := concat_rows||l_newline||delivery_breaker||l_newline||
599 l_rec.publisher_return_results||l_newline;
600 len_sum := len_sum + dlen_breaker + len_each + 6;
601 elsif (l_rec.action_type = 8) then
602 concat_rows := concat_rows||l_newline||burst_breaker||l_newline||
603 l_rec.publisher_return_results||l_newline;
604 len_sum := len_sum + blen_breaker + len_each + 6;
605 else
606 concat_rows := concat_rows||l_newline||
607 l_rec.publisher_return_results||l_newline;
608 len_sum := len_sum + len_each + 4;
609 end if;
610 else
611 len_save := 32767 - (plen_breaker + len_sum + 6);
612 if (len_save > 0) then
613 if (l_rec.action_type = 6) then
614 concat_rows := concat_rows||l_newline||publisher_breaker||l_newline||
615 substr(l_rec.publisher_return_results,1,len_save)||l_newline;
616 len_sum := len_sum + plen_breaker + len_save + 6;
617 elsif (l_rec.action_type = 7) then
618 concat_rows := concat_rows||l_newline||delivery_breaker||l_newline||
619 substr(l_rec.publisher_return_results,1,len_save)||l_newline;
620 len_sum := len_sum + dlen_breaker + len_save + 6;
621 elsif (l_rec.action_type = 8) then
622 concat_rows := concat_rows||l_newline||burst_breaker||l_newline||
623 substr(l_rec.publisher_return_results,1,len_save)||l_newline;
624 len_sum := len_sum + blen_breaker + len_save + 6;
625 else
626 concat_rows := concat_rows||l_newline||
627 substr(l_rec.publisher_return_results,1,len_save)||l_newline;
628 len_sum := len_sum + len_save + 4;
629
630 end if;
631 end if;
632 end if;
633 i := i + 1;
634 end if;
635 end loop;
636
637 return concat_rows;
638
639 exception
640 when others then
641 return concat_rows;
642
643 end post_processing_results;
644
645 end FND_DELIVERY;