[Home] [Help]
PACKAGE BODY: APPS.IEM_CLIENTLAUNCH_PUB
Source
1 PACKAGE BODY IEM_CLIENTLAUNCH_PUB as
2 /* $Header: iempuwqb.pls 120.2.12010000.4 2009/08/31 17:32:44 lkullamb ship $*/
3
4 --
5 --changed
6 -- Purpose: Maintain Launch Email Client
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 4/14/2003 Launched Message Component for INB, ACQ emails
11 -- Liang Xia 4/28/2003 Launching Inbound from node and subnode differently
12 -- Liang Xia 4/29/2003 Fixed bug unable to lauch Transfered message
13 -- Liang Xia 04/06/2005 Fixed GSCC sql.46 ( bug 4256769 )
14 -- Liang Xia 10/26/2005 Fixed bug 4692146
15 -- Lakshmi K 08/13/2009 Changed launchInbound procedure to enable cherry picking
16 -- for 12.1.2 project
17 -- --------- ------ -------------------------------
18 PROCEDURE launchInbound ( p_ieu_media_data IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
19 x_action_type OUT NOCOPY number,
20 x_action_name OUT NOCOPY varchar2,
21 x_action_param OUT NOCOPY varchar2,
22 x_msg_name OUT NOCOPY varchar2,
23 x_msg_param OUT NOCOPY varchar2,
24 x_dialog_style OUT NOCOPY number,
25 x_msg_appl_short_name OUT NOCOPY VARCHAR2
26 ) IS
27
28 l_name varchar2(500);
29 l_value varchar2(1996);
30 l_type varchar2(500);
31
32 logMessage varchar2(2000);
33 l_acct_id number;
34 l_acct_rt_class_id varchar2(30) := '-1';
35 l_resource_id varchar2(30);
36 l_classification_id number;
37 l_launched_node varchar2(100);
38
39 l_return_status VARCHAR2(200) := FND_API.G_RET_STS_SUCCESS;
40 l_msg_count NUMBER := 0;
41 l_msg_data VARCHAR2(2000);
42 l_msg_id number;
43 l_cust_id number;
44 l_sr_number varchar(64);
45 l_contact_id number := -1;
46
47 BEGIN
48
49 -- Standard Start of API savepoint
50 SAVEPOINT launchInbound_pvt;
51
52
53 FND_LOG_REPOSITORY.init(null,null);
54
55 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
56 logMessage := '[ launchInbound begin ]';
57 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHINBOUND', logMessage);
58 end if;
59
60 for i in 1..p_ieu_media_data.count loop
61
62 if p_ieu_media_data(i).PARAM_NAME = 'IEU_PARAM_PK_COL' then
63 l_launched_node := p_ieu_media_data(i).PARAM_VALUE;
64 elsif p_ieu_media_data(i).PARAM_NAME = 'EMAIL_ACCOUNT_ID' then
65 l_acct_id := TO_NUMBER(p_ieu_media_data(i).PARAM_VALUE);
66 elsif p_ieu_media_data(i).PARAM_NAME = 'IEU_PARAM_PK_VALUE' then
67 --Changed for 12.1.2 project for UWQ -cherrypick
68 if l_launched_node = 'MESSAGE_ID' then
69 l_msg_id := p_ieu_media_data(i).PARAM_VALUE;
70 else
74 l_resource_id := p_ieu_media_data(i).PARAM_VALUE;
71 l_acct_rt_class_id := p_ieu_media_data(i).PARAM_VALUE;
72 end if;
73 elsif p_ieu_media_data(i).PARAM_NAME = 'RESOURCE_ID' then
75 end if;
76
77 end loop;
78
79 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
80 logMessage := 'l_acct_id='||l_acct_id||' | l_acct_rt_class_id='||
81 l_acct_rt_class_id||' | l_resource_id='||l_resource_id||
82 ' | l_launched_node=' ||l_launched_node ;
83 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHINBOUND', logMessage);
84 end if;
85
86 if l_launched_node = 'EMAIL_ACCOUNT_ID' then
87 l_classification_id := '-1';
88 elsif l_launched_node = 'ACCOUNT_ROUTE_CLASS_ID' then
89 select route_classification_id into l_classification_id from iem_account_route_class
90 where account_route_class_id = l_acct_rt_class_id;
91 elsif l_launched_node = 'MESSAGE_ID' then
92 select rt_classification_id,customer_id,nvl(contact_id,-1) into l_classification_id,l_cust_id,l_contact_id
93 from iem_rt_proc_emails where message_id = l_msg_id;
94
95 begin
96 select incident_number into l_sr_number from cs_incidents_all_b
97 where incident_id=(select value from iem_encrypted_tags tags,
98 iem_encrypted_tag_dtls dtls where tags.encrypted_id = dtls.encrypted_id
99 and dtls.key = 'IEMNBZTSRVSRID' and tags.message_id= l_msg_id );
100
101 exception when others then
102 l_sr_number := null;
103 end;
104 end if;
105
106 x_action_type := 2; --NULL;
107 x_action_name := 'IEM_MC_LAUNCHER';
108 --Changed for 12.1.2 project
109 if l_launched_node = 'MESSAGE_ID' then
110 x_action_param := 'appShortName=IEM&'||'act=cherrypickpreview&'||'uid='||l_msg_id||'&'||'agentID='||l_resource_id
111 ||'&'||'bigAcctID='||l_acct_id||'&'||'classificationID='||l_classification_id
112 ||'&'||'customerID='||l_cust_id||'&'||'contactID='||l_contact_id||'&'||'serviceRequest='||l_sr_number;
113 else
114 x_action_param := 'appShortName=IEM&'||'act=getwork&'||'agentID='||l_resource_id
115 ||'&'||'accountID='||l_acct_id||'&'||'classificationID='||l_classification_id;
116 end if;
117 x_msg_name := null; --'IEM_NOT_FROM_UWQ_MSG';
118 x_msg_param := NULL;
119 x_dialog_style := IEU_DS_CONSTS_PUB.G_DS_NONE;
120 x_msg_appl_short_name := null;
121
122 /* End of Stub */
123 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
124 logMessage := '[ launchInbound end ]';
125 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHINBOUND', logMessage);
126 end if;
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 ROLLBACK TO launchInbound_pvt;
131 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
132 logMessage := '[NO data found when query iem_account_route_class with l_acct_rt_class_id='||l_acct_rt_class_id||']';
133 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHINBOUND', logMessage);
134 end if;
135
136 WHEN FND_API.G_EXC_ERROR THEN
137 ROLLBACK TO launchInbound_pvt;
138 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
139 logMessage := '[ FND_API.G_EXC_ERROR occur!!! ]';
140 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHINBOUND', logMessage);
141 end if;
142
143 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
144 ROLLBACK TO launchInbound_pvt;
145 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
146 logMessage := '[ FND_API.G_EXC_UNEXPECTED_ERROR occur!!! ]';
147 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHINBOUND', logMessage);
148 end if;
149 WHEN OTHERS THEN
150 ROLLBACK TO launchInbound_pvt;
151 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
152 logMessage := '[Other exception:' ||substr(sqlerrm,1,300)||']';
153 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHINBOUND', logMessage);
154 end if;
155
156 END launchInbound;
157
158
159
160 PROCEDURE launchAcquired( p_ieu_media_data IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
161 x_action_type OUT NOCOPY number,
162 x_action_name OUT NOCOPY varchar2,
163 x_action_param OUT NOCOPY varchar2,
164 x_msg_name OUT NOCOPY varchar2,
165 x_msg_param OUT NOCOPY varchar2,
166 x_dialog_style OUT NOCOPY number,
167 x_msg_appl_short_name OUT NOCOPY VARCHAR2
168 ) IS
169
170 l_name varchar2(500);
171 l_value varchar2(1996);
172 l_type varchar2(500);
173 logMessage varchar2(2000);
174
175 l_acct_id varchar2(30);
176 l_resource_id varchar2(30);
177 l_msg_id varchar2(30);
178
179 l_classification_id iem_route_classifications.route_classification_id%type;
180 l_class_name iem_route_classifications.name%type;
181
182 l_rt_media_item_id IEM_RT_MEDIA_ITEMS.rt_media_item_id%type;
183
184 l_return_status VARCHAR2(200) := FND_API.G_RET_STS_SUCCESS;
185 l_msg_count NUMBER := 0;
186 l_msg_data VARCHAR2(2000);
187
188 BEGIN
189
190 -- Standard Start of API savepoint
191 SAVEPOINT launchInbound_pvt;
192 FND_LOG_REPOSITORY.init(null,null);
196 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHACQUIRED', logMessage);
193
194 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
195 logMessage := '[ launchAcquired begin ]';
197 end if;
198
199 for i in 1..p_ieu_media_data.count loop
200 if p_ieu_media_data(i).PARAM_NAME = 'EMAIL_ACCOUNT_ID' then
201 l_acct_id := p_ieu_media_data(i).PARAM_VALUE;
202 elsif p_ieu_media_data(i).PARAM_NAME = 'IEU_PARAM_PK_VALUE' then
203 l_msg_id := p_ieu_media_data(i).PARAM_VALUE;
204 elsif p_ieu_media_data(i).PARAM_NAME = 'RESOURCE_ID' then
205 l_resource_id := p_ieu_media_data(i).PARAM_VALUE;
206 end if;
207 end loop;
208
209 if( FND_LOG.LEVEL_STATEMENT>= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
210 logMessage := 'l_acct_id='||l_acct_id||' | l_msg_id='||l_msg_id||' | l_resource_id='||l_resource_id;
211 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUBLAUNCHACQUIRED', logMessage);
212 end if;
213
214 select route_classification_id, name
215 into l_classification_id, l_class_name
216 --from iem_route_classifications a, iem_post_mdts b
217 from iem_route_classifications a, iem_rt_proc_emails b
218 where a.route_classification_id = b.rt_classification_id
219 and b.message_id = l_msg_id;
220
221 select rt_media_item_id
222 into l_rt_media_item_id
223 from IEM_RT_MEDIA_ITEMs
224 where message_id = l_msg_id
225 and expire='N';
226
227 if( FND_LOG.LEVEL_STATEMENT>= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
228 logMessage := '[l_class_name='||l_class_name||' | l_rt_media_item_id='||l_rt_media_item_id||']';
229 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUBLAUNCHACQUIRED', logMessage);
230 end if;
231
232 x_action_type := 2;
233 x_action_name := 'IEM_MC_LAUNCHER';
234 x_action_param := 'appShortName=IEM&'||'act=openmsg&'||'agentID='||l_resource_id
235 ||'&'||'classificationID='||l_classification_id||'&'||'classificationName='||l_class_name ||'&'||'key='||l_rt_media_item_id||'&'||'imMsgID='||l_msg_id;
236
237 x_msg_name := NULL;
238 x_msg_param := NULL;
239 x_dialog_style := IEU_DS_CONSTS_PUB.G_DS_NONE;
240 x_msg_appl_short_name := NULL;
241
242 if( FND_LOG.LEVEL_STATEMENT>= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
243 logMessage := '[ launchAcquired End ]';
244 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHACQUIRED', logMessage);
245 end if;
246
247 /* End of Stub */
248
249
250 EXCEPTION
251 WHEN NO_DATA_FOUND THEN
252 ROLLBACK TO launchInbound_pvt;
253 if( FND_LOG.LEVEL_STATEMENT>= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
254 logMessage := '[NO data found when query iem_account_route_class,IEM_RT_MEDIA_ITEMs with l_msg_id='||l_msg_id||']';
255 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHACQUIRED', logMessage);
256 end if;
257
258 WHEN FND_API.G_EXC_ERROR THEN
259 ROLLBACK TO launchInbound_pvt;
260 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
261 logMessage := '[FND_API.G_EXC_ERROR ]';
262 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHACQUIRED', logMessage);
263 end if;
264
265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
266 ROLLBACK TO launchInbound_pvt;
267 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
268 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR]';
269 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHACQUIRED', logMessage);
270 end if;
271
272 WHEN OTHERS THEN
273 ROLLBACK TO launchInbound_pvt;
274 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
275 logMessage := '[Other exception:' ||substr(sqlerrm,1,300)||']';
276 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_CLIENTLAUNCH_PUB.LAUNCHACQUIRED', logMessage);
277 end if;
278 END launchAcquired;
279
280
281 END IEM_CLIENTLAUNCH_PUB;