[Home] [Help]
PACKAGE BODY: APPS.IEM_MC_PUB
Source
1 PACKAGE BODY IEM_MC_PUB AS
2 /* $Header: iemmcpb.pls 115.12 2003/12/03 22:12:23 txliu noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEM_MC_PUB';
5
6 PROCEDURE prepareMessageComponent
7 (p_api_version_number IN NUMBER,
8 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
9 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
10 p_action IN VARCHAR2,
11 p_master_account_id IN NUMBER,
12 p_activity_id IN NUMBER,
13 p_to_address_list IN VARCHAR2,
14 p_cc_address_list IN VARCHAR2,
15 p_bcc_address_list IN VARCHAR2,
16 p_subject IN VARCHAR2,
17 p_sr_id IN NUMBER,
18 p_customer_id IN NUMBER,
19 p_contact_id IN NUMBER,
20 p_mes_document_id IN NUMBER,
21 p_mes_category_id IN NUMBER,
22 p_interaction_id IN NUMBER,
23 p_qualifiers IN QualifierRecordList,
24 x_mc_parameters_id OUT NOCOPY NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2
28 ) AS
29 BEGIN
30 IEM_MC_PUB.prepareMessageComponent
31 (p_api_version_number => p_api_version_number,
32 p_init_msg_list => p_init_msg_list,
33 p_commit => p_commit,
34 p_action => p_action,
35 p_master_account_id => p_master_account_id,
36 p_activity_id => p_master_account_id,
37 p_to_address_list => p_to_address_list,
38 p_cc_address_list => p_cc_address_list,
39 p_bcc_address_list => p_bcc_address_list,
40 p_subject => p_subject,
41 p_sr_id => p_sr_id,
42 p_customer_id => p_customer_id,
43 p_contact_id => p_contact_id,
44 p_relationship_id => null,
45 p_mes_document_id => p_mes_document_id,
46 p_mes_category_id => p_mes_category_id,
47 p_interaction_id => p_interaction_id,
48 p_qualifiers => p_qualifiers,
49 x_mc_parameters_id => x_mc_parameters_id,
50 x_return_status => x_return_status,
51 x_msg_count => x_msg_count,
52 x_msg_data => x_msg_data
53 );
54
55 END prepareMessageComponent;
56
57 PROCEDURE prepareMessageComponent
58 (p_api_version_number IN NUMBER,
59 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
60 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
61 p_action IN VARCHAR2,
62 p_master_account_id IN NUMBER,
63 p_activity_id IN NUMBER,
64 p_to_address_list IN VARCHAR2,
65 p_cc_address_list IN VARCHAR2,
66 p_bcc_address_list IN VARCHAR2,
67 p_subject IN VARCHAR2,
68 p_sr_id IN NUMBER,
69 p_customer_id IN NUMBER,
70 p_contact_id IN NUMBER,
71 p_relationship_id IN NUMBER,
72 p_mes_document_id IN NUMBER,
73 p_mes_category_id IN NUMBER,
74 p_interaction_id IN NUMBER,
75 p_qualifiers IN QualifierRecordList,
76 x_mc_parameters_id OUT NOCOPY NUMBER,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2
80 ) AS
81
82 l_action VARCHAR2(20);
83 l_master_account_id NUMBER;
84 l_activity_id NUMBER;
85 l_to_address_list VARCHAR2(1024);
86 l_cc_address_list VARCHAR2(1024);
87 l_bcc_address_list VARCHAR2(1024);
88 l_subject VARCHAR2(1024);
89 l_sr_id NUMBER;
90 l_customer_id NUMBER;
91 l_contact_id NUMBER;
92 l_relationship_id NUMBER;
93 l_mes_document_id NUMBER;
94 l_mes_category_id NUMBER;
95 l_interaction_id NUMBER;
96
97 l_msg_count NUMBER(2);
98 l_msg_data VARCHAR2(2000);
99
100 l_api_name CONSTANT VARCHAR2(30) := 'prepareMessageComponent';
101 l_api_version CONSTANT NUMBER := 1.0;
102
103
104 BEGIN
105
106 SAVEPOINT IEM_MC_PARAMETERS;
107
108
109 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version_number, l_api_name, G_PKG_NAME)
110 then
111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
112 END IF;
113
114 IF fnd_api.to_boolean(p_init_msg_list)
115 then
116 FND_MSG_PUB.initialize;
117 end if;
118
119 x_return_status := fnd_api.g_ret_sts_success;
120 x_mc_parameters_id := 0;
121
122 l_action := p_action;
123 IF (l_action is null)
124 THEN
125 l_action := 'reply';
126 END IF;
127
128 l_master_account_id := p_master_account_id;
129 l_activity_id := p_activity_id;
130 l_to_address_list := p_to_address_list;
131 l_cc_address_list := p_cc_address_list;
132 l_bcc_address_list := p_bcc_address_list;
133 l_subject := p_subject;
134 l_sr_id := p_sr_id;
135 l_customer_id := p_customer_id;
136 l_contact_id := p_contact_id;
137 l_relationship_id := p_relationship_id;
138 l_mes_document_id := p_mes_document_id;
139 l_mes_category_id := p_mes_category_id;
140 l_interaction_id := p_interaction_id;
141
142 select IEM_MC_PARAMETERS_S1.NEXTVAL into x_mc_parameters_id from sys.dual;
143
144 insert into IEM_MC_PARAMETERS
145 (
146 MC_PARAMETER_ID,
147 ACTION,
148 MASTER_ACCOUNT_ID,
149 ACTIVITY_ID,
150 TO_ADDRESS_LIST,
151 CC_ADDRESS_LIST,
152 BCC_ADDRESS_LIST,
153 SUBJECT,
154 SR_ID,
155 CUSTOMER_ID,
156 CONTACT_ID,
157 RELATIONSHIP_ID,
158 MES_DOC_ID,
159 MES_CATEGORY_ID,
160 INTERACTION_ID,
161 DELETE_FLAG_Y_N,
162 CREATED_BY,
163 CREATION_DATE,
164 LAST_UPDATED_BY,
165 LAST_UPDATE_DATE,
166 LAST_UPDATE_LOGIN
167 )
168 values
169 (
170 x_mc_parameters_id,
171 l_action,
172 l_master_account_id,
173 l_activity_id,
174 l_to_address_list,
175 l_cc_address_list,
176 l_bcc_address_list,
177 l_subject,
178 l_sr_id,
179 l_customer_id,
180 l_contact_id,
181 l_relationship_id,
182 l_mes_document_id,
183 l_mes_category_id,
184 l_interaction_id,
185 'N',
186 FND_GLOBAL.USER_ID,
187 SYSDATE,
188 FND_GLOBAL.USER_ID,
189 SYSDATE,
190 FND_GLOBAL.LOGIN_ID
191 );
192
193 IF (p_qualifiers.count > 0)
194 THEN
195 BEGIN
196 FOR i IN p_qualifiers.first .. p_qualifiers.Last
197 LOOP
198 insert into IEM_MC_CUSTOM_PARAMS
199 (
200 MC_PARAMETER_ID,
201 NAME,
202 VALUE
203 )
204 values
205 (
206 x_mc_parameters_id,
207 p_qualifiers(i).QUALIFIER_NAME,
208 p_qualifiers(i).QUALIFIER_VALUE
209 );
210 END LOOP;
211 END;
212 END IF;
213
214 -- Standard check of p_commit
215 IF FND_API.To_Boolean(p_commit) THEN
216 COMMIT WORK;
217 END IF;
218
219 -- Standard call to get message count and if count is 1, get message info.
220 FND_MSG_PUB.Count_And_Get(
221 p_count => x_msg_count,
222 p_data => x_msg_data
223 );
224
225
226 EXCEPTION
227 WHEN FND_API.G_EXC_ERROR THEN
228 ROLLBACK TO IEM_MC_PARAMETERS;
229
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 FND_MSG_PUB.Count_And_Get(
232 p_count => x_msg_count,
233 p_data => x_msg_data
234 );
235
236
237 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
238 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
239 x_msg_data := x_msg_data || ',' || l_msg_data;
240 END LOOP;
241
242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
243 ROLLBACK TO IEM_MC_PARAMETERS;
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
245 FND_MSG_PUB.Count_And_Get
246 (
247 p_count => x_msg_count,
248 p_data => x_msg_data
249 );
250
251 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
252 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
253 x_msg_data := x_msg_data || ',' || l_msg_data;
254 END LOOP;
255
256 WHEN OTHERS THEN
257 ROLLBACK TO IEM_MC_PARAMETERS;
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
259
260 FND_MSG_PUB.Count_And_Get (
261 p_count => x_msg_count,
262 p_data => x_msg_data
263 );
264
265 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
266 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
267 x_msg_data := x_msg_data || ',' || l_msg_data;
268 END LOOP;
269
270 END prepareMessageComponent;
271
272
273 PROCEDURE prepareMessageComponentII
274 (p_api_version_number IN NUMBER,
275 p_init_msg_list IN VARCHAR2,
276 p_commit IN VARCHAR2,
277 p_action IN VARCHAR2,
278 p_master_account_id IN NUMBER,
279 p_activity_id IN NUMBER,
280 p_to_address_list IN VARCHAR2,
281 p_cc_address_list IN VARCHAR2,
282 p_bcc_address_list IN VARCHAR2,
283 p_subject IN VARCHAR2,
284 p_sr_id IN NUMBER,
285 p_customer_id IN NUMBER,
286 p_contact_id IN NUMBER,
287 p_mes_document_id IN NUMBER,
288 p_mes_category_id IN NUMBER,
289 p_interaction_id IN NUMBER,
290 p_qualifiers IN QualifierRecordList,
291 p_message_type IN VARCHAR2,
292 p_encoding IN VARCHAR2,
293 p_character_set IN VARCHAR2,
294 p_relationship_id IN NUMBER,
295 x_mc_parameters_id OUT NOCOPY NUMBER,
296 x_return_status OUT NOCOPY VARCHAR2,
297 x_msg_count OUT NOCOPY NUMBER,
298 x_msg_data OUT NOCOPY VARCHAR2
299 ) AS
300
301 l_created_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
302 l_last_updated_by NUMBER:=NVL(to_number(FND_PROFILE.VALUE('USER_ID')),-1);
303 l_last_update_login NUMBER:= NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
304 l_action VARCHAR2(20);
305
306 l_msg_count NUMBER(2);
307 l_msg_data VARCHAR2(2000);
308
309 l_api_name CONSTANT VARCHAR2(30) := 'prepareMessageComponentII';
310 l_api_version CONSTANT NUMBER := 1.0;
311
312
313 BEGIN
314
315 -- Standard Start of API savepoint
316 SAVEPOINT prepareMessageComponentII_pvt;
317
318 -- Standard call to check for call compatibility.
319 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version_number, l_api_name, G_PKG_NAME)
320 then
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
322 END IF;
323
324 -- Initialize message list if p_init_msg_list is set to TRUE.
325 IF fnd_api.to_boolean(p_init_msg_list)
326 then
327 FND_MSG_PUB.initialize;
328 end if;
329
330 -- Initialize API return status to SUCCESS
331 x_return_status := fnd_api.g_ret_sts_success;
332
333 -----------------------Code------------------------
334 l_action := p_action;
335 IF (l_action is null or l_action = fnd_api.g_miss_char)
336 THEN
337 l_action := 'reply';
338 END IF;
339
340
341 select IEM_MC_PARAMETERS_S1.NEXTVAL into x_mc_parameters_id from sys.dual;
342
343 insert into IEM_MC_PARAMETERS
344 (
345 MC_PARAMETER_ID,
346 ACTION,
347 MASTER_ACCOUNT_ID,
348 ACTIVITY_ID,
349 TO_ADDRESS_LIST,
350 CC_ADDRESS_LIST,
351 BCC_ADDRESS_LIST,
352 SUBJECT,
353 SR_ID,
354 CUSTOMER_ID,
355 CONTACT_ID,
356 MES_DOC_ID,
357 MES_CATEGORY_ID,
358 INTERACTION_ID,
359 DELETE_FLAG_Y_N,
360 CREATED_BY,
361 CREATION_DATE,
362 LAST_UPDATED_BY,
363 LAST_UPDATE_DATE,
364 LAST_UPDATE_LOGIN,
365 ENCODE,
366 CHAR_SET,
367 MSG_TYPE,
368 RELATIONSHIP_ID
369 )
370 values
371 (
372 x_mc_parameters_id,
373 l_action,
374 p_master_account_id,
375 decode(p_activity_id, fnd_api.g_miss_num, null, p_activity_id),
376 p_to_address_list,
377 p_cc_address_list,
378 p_bcc_address_list,
379 p_subject,
380 p_sr_id,
381 p_customer_id,
382 p_contact_id,
383 decode (p_mes_document_id, FND_API.G_MISS_NUM, NULL, p_mes_document_id),
384 decode (p_mes_category_id, FND_API.G_MISS_NUM, NULL, p_mes_category_id),
385 p_interaction_id,
386 'N',
387 l_created_by,
388 SYSDATE,
389 l_last_updated_by,
390 SYSDATE,
391 l_last_update_login,
392 decode(p_encoding, fnd_api.g_miss_char, null, p_encoding),
393 decode(p_character_set, fnd_api.g_miss_char, null, p_character_set),
394 p_message_type, p_relationship_id );
395
396 IF (p_qualifiers.count > 0)
397 THEN
398 BEGIN
399 FOR i IN p_qualifiers.first .. p_qualifiers.Last
400 LOOP
401 insert into IEM_MC_CUSTOM_PARAMS
402 (
403 MC_PARAMETER_ID,
404 NAME,
405 VALUE
406 )
407 values
408 (
409 x_mc_parameters_id,
410 p_qualifiers(i).QUALIFIER_NAME,
411 p_qualifiers(i).QUALIFIER_VALUE
412 );
413 END LOOP;
414 END;
415 END IF;
416
417 --------- end of codes -------------------
418 -- Standard check of p_commit
419 IF FND_API.To_Boolean(p_commit) THEN
420 COMMIT WORK;
421 END IF;
422
423 EXCEPTION
424 WHEN FND_API.G_EXC_ERROR THEN
425 ROLLBACK TO prepareMessageComponentII_pvt;
426
427 x_return_status := FND_API.G_RET_STS_ERROR;
428 FND_MSG_PUB.Count_And_Get(
429 p_count => x_msg_count,
430 p_data => x_msg_data
431 );
432 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
433 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
434 x_msg_data := x_msg_data || ',' || l_msg_data;
435 END LOOP;
436
437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
438 ROLLBACK TO IEM_MC_PARAMETERS;
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
440 FND_MSG_PUB.Count_And_Get
441 (
442 p_count => x_msg_count,
443 p_data => x_msg_data
444 );
445 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
446 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
447 x_msg_data := x_msg_data || ',' || l_msg_data;
448 END LOOP;
449
450 WHEN OTHERS THEN
451 ROLLBACK TO prepareMessageComponentII_pvt;
452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
453
454 FND_MSG_PUB.Count_And_Get (
455 p_count => x_msg_count,
456 p_data => x_msg_data
457 );
458 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
459 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
460 x_msg_data := x_msg_data || ',' || l_msg_data;
461 END LOOP;
462
463 END prepareMessageComponentII;
464
465 END IEM_MC_PUB;