[Home] [Help]
PACKAGE BODY: APPS.IEM_GETMERGEVALUE_PVT
Source
1 package body IEM_GETMERGEVALUE_PVT as
2 /* $Header: iemvmrgb.pls 115.3 2002/12/04 22:52:19 sboorela shipped $*/
3
4 /**********************Global Variable Declaration **********************/
5
6 PROCEDURE IEM_GET_MERGE_VALUES(
7 p_msgid in number,
8 x_merge_vals OUT NOCOPY template_merge_tbl,
9 x_status out NOCOPY varchar2) IS
10
11 l_dblink varchar2(250);
12 l_user varchar2(100);
13 l_domain varchar2(100);
14 l_password varchar2(100);
15 l_account_name varchar2(256);
16 l_source_message_id number;
17 l_subject varchar2(1000);
18 l_sender varchar2(70);
19 l_to_recip varchar2(1000);
20 l_cc_recip varchar2(100);
21 l_fwd_recip varchar2(100);
22 l_frm_str varchar2(100);
23 l_sentdate date;
24 l_received_date date;
25 l_str varchar2(500);
26 l_replyto varchar2(500);
27 l_ret number;
28 l_index number;
29 l_sender_name varchar2(500);
30 l_reply_to varchar2(100);
31 l_msg_size number;
32 l_counter number;
33 BEGIN
34 x_status:='S';
35 select '@'||a.db_link,b.email_user,b.email_password, b.domain,
36 b.account_name,c.source_message_id,c.received_date,b.reply_to_address
37 INTO
38 l_dblink,l_user,l_password,l_domain,
39 l_account_name,l_source_message_id,l_received_date,l_replyto
40 from iem_db_connections a,iem_email_accounts b,iem_post_mdts c
41 where c.message_id=p_msgid
42 and b.email_account_id=c.email_account_id
43 and a.db_server_id=b.db_server_id
44 and a.is_admin='A';
45 l_str:='begin :l_ret:=im_api.authenticate'||l_dblink||'(:a_user,:a_domain,:a_password);end; ';
46 EXECUTE IMMEDIATE l_str using OUT l_ret,l_user,l_domain,l_password;
47 l_str :='begin :l_ret:=im_api.GetMessageHdrs'||l_dblink||'(:a_msg_id,:a_subject,:a_sender,:a_to_recip,:a_cc_recip,:a_frm_str,:a_sent_date,:a_reply_to,:a_msg_size);end;';
48 EXECUTE IMMEDIATE l_str USING OUT l_ret,l_source_message_id,OUT l_subject,
49 OUT l_sender,OUT l_to_recip,OUT l_cc_recip,OUT l_frm_str,OUT l_sentdate,
50 OUT l_reply_to, OUT l_msg_size;
51 l_index:=instr(l_frm_str,'<',1,1);
52
53 IF l_index>0 then
54 l_sender_name:=substr(l_frm_Str,1,l_index-1);
55 l_sender_name:=replace(l_sender_name,'"','');
56 else
57 FND_MESSAGE.Set_Name('IEM','IEM_ADM_AUTO_ACK_CUSTOMER');
58 FND_MSG_PUB.Add;
59 l_sender_name := FND_MSG_PUB.GET(FND_MSG_pub.Count_Msg,FND_API.G_FALSE);
60 end if;
61 x_merge_vals.delete;
62 l_counter:=1;
63 x_merge_vals(l_counter).field_name:='ACK_SENDER_NAME';
64 x_merge_vals(l_counter).field_value:=l_sender_name;
65 l_counter:=l_counter+1;
66 x_merge_vals(l_counter).field_name:='ACK_SUBJECT';
67 x_merge_vals(l_counter).field_value:=l_subject;
68 l_counter:=l_counter+1;
69 x_merge_vals(l_counter).field_name:='ACK_RECEIVED_DATE';
70 x_merge_vals(l_counter).field_value:=l_received_date;
71 l_counter:=l_counter+1;
72 x_merge_vals(l_counter).field_name:='ACK_ACCT_FROM_NAME';
73 x_merge_vals(l_counter).field_value:=l_account_name;
74 l_counter:=l_counter+1;
75 x_merge_vals(l_counter).field_name:='ACK_ACCT_EMAIL_ADDRESS';
76 x_merge_vals(l_counter).field_value:=l_replyto;
77
78 EXCEPTION WHEN OTHERS THEN
79 x_status:='E';
80 END IEM_GET_MERGE_VALUES;
81
82 PROCEDURE IEM_GET_MERGE_VALUE(
83 p_msgid in number,
84 p_merge_key IN varchar2,
85 x_merge_val OUT NOCOPY varchar2,
86 x_status out NOCOPY varchar2) IS
87
88 l_dblink varchar2(250);
89 l_user varchar2(100);
90 l_domain varchar2(100);
91 l_password varchar2(100);
92 l_account_name varchar2(256);
93 l_source_message_id number;
94 l_subject varchar2(1000);
95 l_sender varchar2(70);
96 l_to_recip varchar2(1000);
97 l_cc_recip varchar2(100);
98 l_fwd_recip varchar2(100);
99 l_frm_str varchar2(100);
100 l_sentdate date;
101 l_received_date date;
102 l_str varchar2(500);
103 l_replyto varchar2(500);
104 l_ret number;
105 l_index number;
106 l_sender_name varchar2(500);
107 l_reply_to varchar2(100);
108 l_msg_size number;
109 BEGIN
110 x_status:='S';
111
112 select '@'||a.db_link,b.email_user,b.email_password, b.domain,
113 b.account_name,c.source_message_id,c.received_date,b.reply_to_address
114 INTO
115 l_dblink,l_user,l_password,l_domain,
116 l_account_name,l_source_message_id,l_received_date,l_replyto
117 from iem_db_connections a,iem_email_accounts b,iem_post_mdts c
118 where c.message_id=p_msgid
119 and b.email_account_id=c.email_account_id
120 and a.db_server_id=b.db_server_id
121 and a.is_admin='A';
122 IF upper(p_merge_key)='ACK_SENDER_NAME' THEN
123 l_str:='begin :l_ret:=im_api.authenticate'||l_dblink||'(:a_user,:a_domain,:a_password);end; ';
124 EXECUTE IMMEDIATE l_str using OUT l_ret,l_user,l_domain,l_password;
125 l_str :='begin :l_ret:=im_api.GetMessageHdrs'||l_dblink||'(:a_msg_id,:a_subject,:a_sender,:a_to_recip,:a_cc_recip,:a_frm_str,:a_sent_date,:a_reply_to,:a_msg_size);end;';
126 EXECUTE IMMEDIATE l_str USING OUT l_ret,l_source_message_id,OUT l_subject,
127 OUT l_sender,OUT l_to_recip,OUT l_cc_recip,OUT l_frm_str,OUT l_sentdate,
128 OUT l_reply_to, OUT l_msg_size;
129 l_index:=instr(l_frm_str,'<',1,1);
130
131 IF l_index>0 then
132 l_sender_name:=substr(l_frm_Str,1,l_index-1);
133 l_sender_name:=replace(l_sender_name,'"','');
134 else
135 l_sender_name:='Customer '; -- need to be from profile
136 end if;
137 x_merge_val:=l_sender_name;
138 ELSIF upper(p_merge_key)='ACK_SUBJECT' THEN
139 x_merge_val:=l_subject;
140 ELSIF upper(p_merge_key)='ACK_RECEIVED_DATE' THEN
141 x_merge_val:=l_received_date;
142 ELSIF upper(p_merge_key)='ACK_ACCT_FROM_NAME' THEN
143 x_merge_val:=l_account_name;
144 ELSIF upper(p_merge_key)='ACK_ACCT_EMAIL_ADDRESS' THEN
145 x_merge_val:=l_replyto;
146 END IF;
147 EXCEPTION WHEN OTHERS THEN
148 x_status:='E';
149 END IEM_GET_MERGE_VALUE;
150 END ;