1 package body IEM_SENDMAIL_PVT as
2 /* $Header: iemvsomb.pls 120.4 2007/11/07 20:26:58 kgscott noship $*/
3
4 PROCEDURE IEM_CHK_TEMPLATE(
5 p_template_id in number,
6 x_status OUT NOCOPY varchar2) IS
7 l_file_id number;
8 cursor c1 is select lookup_code,meaning
9 from fnd_lookups
10 where enabled_flag = 'Y'
11 AND NVL(start_date_active, SYSDATE) <= SYSDATE
12 AND NVL(end_date_active,SYSDATE) >= SYSDATE
13 AND lookup_type ='IEM_MERGE_FIELDS'
14 ANd lookup_code like 'ACK%';
15 v_rawbuf RAW(2400);
16 v_done BOOLEAN := false;
17 v_flag INTEGER;
18 v_bfile bfile;
19 v_amt INTEGER;
20 v_data BLOB;
21 v_offset INTEGER := 1;
22 l_buff varchar2(2400);
23 c_buflen CONSTANT INTEGER := 2400;
24 l_index number;
25 l_string1 varchar2(100);
26 l_string2 varchar2(100);
27 l_ffind number:=0;
28 l_sfind number:=0;
29 l_ffind1 number:=0;
30 l_sfind1 number:=0;
31 l_occur number:=1;
32 l_match number;
33 TYPE t_mergeTbl is table of varchar2(100)
34 INDEX BY BINARY_INTEGER;
35 l_mergetbl t_mergeTbl;
36 l_mindex number;
37 l_tval varchar2(10):='CHR';
38 l_val1 varchar2(10):='((?';
39 l_val2 varchar2(10):='?))';
40 BEGIN
41 x_status:='S';
42 l_val1:=l_tval||'(171)';
43
44 l_val2:=l_tval||'(187)';
45 SELECT fl.file_id
46 INTO l_file_id
47 FROM jtf_amv_items_tl b ,jtf_amv_attachments a ,fnd_lobs fl
48 WHERE b.item_id = a.attachment_used_by_id
49 and a.attachment_used_by='ITEM'
50 AND a.file_id = fl.file_id
51 AND b.item_id=p_template_id
52 AND b.language=USERENV('LANG')
53 and rownum=1;
54 -- Store all the merge fields present in the templates
55 select file_data into v_data
56 from fnd_lobs
57 where file_id=l_file_id;
58 l_occur:=1;
59 l_mergetbl.delete; --To store all the merge fields
60 l_mindex:=1; --Index to store all the merge
61 LOOP
62 v_amt := c_buflen;
63 dbms_lob.read(v_data, v_amt, v_offset, v_rawbuf);
64 v_offset := v_offset + v_amt;
65 l_buff:=utl_raw.cast_to_VARCHAR2(v_rawbuf);
66 l_string1:=l_val1;
67 l_string2:=l_val2;
68 /*
69 execute immediate 'select '||l_val1||' from dual ' into l_string1;
70 execute immediate 'select '||l_val2||' from dual ' into l_string2;
71 */
72 LOOP
73 l_ffind:=instr(l_buff,'((?',1,l_occur);
74 l_sfind:=instr(l_buff,'?))',1,l_occur);
75 l_ffind1:=instr(l_buff,'((*',1,l_occur);
76 l_sfind1:=instr(l_buff,'*))',1,l_occur);
77 IF (l_ffind>0) and (l_sfind>0) THEN
78 l_mergetbl(l_mindex):=substr(l_buff,l_ffind+1,l_sfind-l_ffind-1);
79 l_mindex:=l_mindex+1;
80 l_occur:=l_occur+1;
81 END IF;
82
83 IF (l_ffind1>0) and (l_sfind1>0) THEN
84 l_mergetbl(l_mindex):=substr(l_buff,l_ffind1+3,l_sfind1-l_ffind1-3);
85 l_mindex:=l_mindex+1;
86 l_occur:=l_occur+1;
87 END IF;
88 EXIT WHEN (l_ffind=0 OR l_sfind=0)AND (l_ffind1=0 OR l_sfind1=0);
89 END LOOP;
90 EXIT WHEN v_amt < c_buflen;
91 END LOOP;
92 --Check all the merge field with the standard merge fields.
93 IF l_mergetbl.count>0 THEN -- Template Contain Merge Fields
94 FOR i in l_mergetbl.FIRST..l_mergetbl.LAST LOOP
95 l_match:=0;
96 FOR v1 in c1 LOOP
97 if trim(l_mergetbl(i))=trim(v1.lookup_code) THEN
98 l_match:=l_match+1;
99 end if;
100 END LOOP;
101 EXIT when l_match=0;
102 END LOOP;
103 IF l_match=0 THEN
104 x_status:='E';
105 END IF;
106 ELSE -- Template Contain No merge Fields
107 x_status:='S';
108 END IF;
109 EXCEPTION WHEN OTHERS THEN
110 x_status:='E';
111 END IEM_CHK_TEMPLATE;
112 PROCEDURE IEM_SENDMAIL(
113 p_user in varchar2,
114 p_domain in varchar2,
115 p_password in varchar2,
116 p_replyto in varchar2,
117 p_file_id in number,
118 p_subject in varchar2,
119 p_tostr in varchar2,
120 p_fromstr in varchar2,
121 p_encrypt_tbl in email_encrypt_tbl,
122 x_status OUT NOCOPY varchar2,
123 x_return_text OUT NOCOPY varchar2
124 )
125
126 IS
127
128 begin
129 x_status:='S';
130 end IEM_SENDMAIL ;
131 end;
[Home] [Help]