[Home] [Help]
PACKAGE BODY: APPS.AMS_FULFILL_PVT
Source
1 PACKAGE BODY AMS_FULFILL_PVT as
2 /* $Header: amsvffmb.pls 120.5 2006/05/31 12:53:08 kbasavar ship $ */
3
4 --
5 -- NAME
6 -- AMS_FULFILL_PVT
7 --
8 -- HISTORY
9 -- 11/12/1999 ptendulk CREATED
10 -- 02/21/2000 ptendulk Modified the interaction with fulfillment
11 -- Interaction
12 -- 03/08/2000 ptendulk Modified after changes from fulfillment team
13 -- 05/08/2000 ptendulk Modified , Get the cover letter from Schedules
14 -- table instead of campaign table
15 -- 05/22/2000 ptendulk Modified AMS_FULFILL procedure, Get the partyid
16 -- from party id column instead of customer id column
17 -- in ams_list_entries table
18 -- 11-Dec-2000 ptendulk Added additional parameter in GetMasterInfo
19 -- Ref Bug # 1529231
20 -- 01-Feb-2001 ptendulk Added additional where clause in ams_fulfill
21 -- Refer Bug # 1618348
22 -- 21-Mar-2001 soagrawa Modified Get_Master_Info and Get_Deliverable_Info
23 -- to access activity type info from schedule
24 -- 13-Jun-2001 ptendulk Modified ams_fulfill to write source code in interaction
25 -- Modified Create_master_doc to use from and reply to.
26 -- 25-Jun-2001 ptendulk Added additional apis to Send_Test_Email and Attach
27 -- query.
28 -- 10-Jul-2001 ptendulk 1. Modified the get_deliverable_info as for eBlast
29 -- attachments will not be created as deliverables but
30 -- will be created in deliverables details page.
31 -- 2. Clean up the code
32 -- 06-Sep-2001 soagrawa Modified cursor c_csch_det in Get_Deliverable_Info
33 -- 06-Sep-2001 soagrawa Modified Send_Test_email to include the extended header
34 -- 18-jan-2002 soagrawa Modified Get_Master_Info to fix
35 -- bug# 2186980
36 -- 29-apr-2002 soagrawa Modified for new schedule eblast
37 -- 30-may-2002 soagrawa Modified call to submit batch request to pass it profile_id
38 -- 15-jul-2002 soagrawa Modified ams_fulfill to pass source codes to the FFM API.
39 -- 14-aug-2002 soagrawa Modified ams_fulfill for bug# 2490929 requestor id issues
40 -- 22-aug-2003 soagrawa Modified ams_fulfill for bug# 3111735 extended header issues
41 -- 28-aug-2003 soagrawa Modified ams_fulfill for bug# 3119662
42 -- 30-sep-2003 soagrawa Modified ams_fulill for integration with JTO 11.5.10
43 -- 28-jan-2005 spendem Fix for bug # 4145845. Added to_char function to the schedule_id
44 -- 29-May-2006 kbasavar Modified ams_fulfill for delivery_mode fix
45
46 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_FULFILL_PVT';
47 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvffmb.pls';
48 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
49 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
50 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
51
52 -- Debug mode
53 --g_debug boolean := FALSE;
54 --g_debug boolean := TRUE;
55
56 ----------------------------------------------------------------------------------------
57 ----------------------------------------------------------------------------------------
58 ----------------------------------------------------------------------------------------
59 ---------------------------------- FulFillment Process----------------------------------
60 ----------------------------------------------------------------------------------------
61 ----------------------------------------------------------------------------------------
62 ----------------------------------------------------------------------------------------
63
64 /*************************** PRIVATE ROUTINES *********************************/
65
66
67
68 -- Start of Comments
69 --
70 -- NAME
71 -- FulFill_OC
72 --
73 -- PURPOSE
74 -- This procedure will call the Order Capture API to fulfill
75 -- the hard collaterals and the kits associated with these
76 -- Collaterals
77 --
78 -- NOTES
79 -- This api will be used for physical collaterals fulfillment
80 -- Requires integration with OC and OMO is not currently supporting
81 -- it.
82 --
83 -- HISTORY
84 -- 02/22/2000 ptendulk Created
85 -- End of Comments
86
87 PROCEDURE FulFill_OC(p_api_version IN NUMBER,
88 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
89
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER ,
92 x_msg_data OUT NOCOPY VARCHAR2,
93
94 p_deliv_id IN NUMBER ,
95 p_list_header_id IN NUMBER )
96 IS
97 l_api_name CONSTANT VARCHAR2(30) := 'Fulfill_Oc';
98 l_api_version CONSTANT NUMBER := 1.0;
99 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
100 l_return_status VARCHAR2(1);
101
102 -- Declare the oc variables
103 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
104 l_control_rec ASO_ORDER_INT.control_rec_type;
105 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
106 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
107
108 l_kit_index NUMBER := 2 ;
109
110 x_order_header_rec ASO_ORDER_INT.Order_Header_rec_type;
111 x_order_line_tbl ASO_ORDER_INT.Order_Line_tbl_type;
112
113
114 CURSOR c_del_det IS
115 SELECT inventory_item_id, inventory_item_org_id,
116 pricelist_header_id , nvl(qp.currency_code,'USD') currency_code
117 FROM ams_deliverables_vl del,qp_list_headers_vl qp
118 WHERE del.deliverable_id = p_deliv_id
119 AND del.pricelist_header_id = qp.list_header_id ;
120
121 l_del_rec c_del_det%ROWTYPE ;
122
123 CURSOR c_kit_det IS
124 SELECT inventory_item_id, inventory_item_org_id,
125 pricelist_header_id , nvl(qp.currency_code,'USD') currency_code
126 FROM ams_deliverables_vl del,qp_list_headers_vl qp
127 WHERE del.deliverable_id = p_deliv_id
128 AND del.pricelist_header_id = qp.list_header_id ;
129
130 l_kit_rec c_del_det%ROWTYPE ;
131
132 CURSOR c_list_ent_det IS
133 SELECT list_entry_source_system_id party_id,
134 fax,
135 email_address
136 FROM ams_list_entries
137 WHERE list_header_id = p_list_header_id ;
138 l_list_rec c_list_ent_det%ROWTYPE ;
139
140 BEGIN
141 --
142 -- Standard Start of API savepoint
143 --
144 SAVEPOINT FulFill_OC_PT;
145
146 --
147 -- Debug Message
148 --
149 IF (AMS_DEBUG_HIGH_ON) THEN
150 AMS_Utility_PVT.debug_message(l_full_name||': start');
151 END IF;
152 --
153 -- Initialize message list IF p_init_msg_list is set to TRUE.
154 --
155 IF FND_API.to_Boolean( p_init_msg_list ) THEN
156 FND_MSG_PUB.initialize;
157 END IF;
158
159 --
160 -- Standard call to check for call compatibility.
161 --
162 IF NOT FND_API.Compatible_API_Call ( l_api_version,
163 p_api_version,
164 l_api_name,
165 G_PKG_NAME)
166 THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169
170 --
171 -- Initialize API return status to success
172 --
173 x_return_status := FND_API.G_RET_STS_SUCCESS;
174
175
176 --
177 -- API body
178 --
179 OPEN c_del_det;
180 FETCH c_del_det INTO l_del_rec ;
181 CLOSE c_del_det;
182
183 l_qte_header_rec.quote_source_code := 'ASO' ;
184 l_qte_header_rec.currency_code := l_del_rec.currency_code ;
185 l_qte_header_rec.price_list_id := l_del_rec.pricelist_header_id ;
186 l_qte_header_rec.employee_person_id := 41942 ;
187
188 l_control_rec.book_flag := FND_API.G_TRUE ;
189 l_control_rec.calculate_price := FND_API.G_FALSE ;
190 l_control_rec.server_id := 3 ;
191 -- l_qte_header_rec.order_type_id := 1000 ;
192
193 OPEN c_list_ent_det ;
194 LOOP
195 FETCH c_list_ent_det INTO l_list_rec ;
196 EXIT WHEN c_list_ent_det%NOTFOUND ;
197
198 l_qte_header_rec.party_id := l_list_rec.party_id ;
199
200
201 l_qte_line_tbl(1).inventory_item_id := l_del_rec.inventory_item_id ;
202 l_qte_line_tbl(1).organization_id := l_del_rec.inventory_item_org_id;
203 l_qte_line_tbl(1).quantity := 1 ;
204 l_qte_line_tbl(1).uom_code := 'Ea' ; ---***
205 l_qte_line_tbl(1).price_list_id := l_del_rec.pricelist_header_id ;
206 l_qte_line_tbl(1).line_category_code := 'ORDER' ;
207
208 l_qte_line_tbl(1).ffm_media_type := 'EMAIL' ;
209 l_qte_line_tbl(1).ffm_media_id := '[email protected]' ;
210 l_qte_line_tbl(1).ffm_content_type := 'COLLATERAL' ;
211
212
213 l_ln_shipment_tbl(1).qte_line_index := 1 ;
214 l_ln_shipment_tbl(1).quantity := 1 ;
215
216 OPEN c_kit_det ;
217 LOOP
218 FETCH c_kit_det INTO l_kit_rec ;
219 EXIT WHEN c_kit_det%NOTFOUND ;
220 l_qte_line_tbl(l_kit_index).inventory_item_id := l_del_rec.inventory_item_id ;
221 l_qte_line_tbl(l_kit_index).organization_id := l_del_rec.inventory_item_org_id;
222 l_qte_line_tbl(l_kit_index).quantity := 1 ;
223 l_qte_line_tbl(l_kit_index).uom_code := 'ABS' ; ---***
224 l_qte_line_tbl(l_kit_index).price_list_id := l_del_rec.pricelist_header_id ;
225 l_qte_line_tbl(l_kit_index).line_category_code := 'ORDER' ;
226
227 -- l_qte_line_tbl(l_kit_index).ffm_media_type := 'EMAIL' ;
228 -- l_qte_line_tbl(l_kit_index).ffm_media_id := '[email protected]' ;
229 -- l_qte_line_tbl(l_kit_index).ffm_content_type := 'COLLATERAL' ;
230
231 l_ln_shipment_tbl(l_kit_index).qte_line_index := l_kit_index ;
232 l_ln_shipment_tbl(l_kit_index).quantity := 1 ;
233 l_kit_index := l_kit_index + 1 ;
234 END LOOP ;
235 CLOSE c_kit_det ;
236
237 IF (AMS_DEBUG_HIGH_ON) THEN
238 AMS_UTILITY_PVT.Debug_message('Before calling create order');
239 END IF;
240
241 ASO_ORDER_INT.Create_order(
242 p_api_version_number => 1.0,
243 p_init_msg_list => p_init_msg_list,
244
245 p_qte_rec => l_qte_header_rec,
246
247 p_qte_line_tbl => l_qte_line_tbl,
248
249 p_line_shipment_tbl => l_ln_shipment_tbl,
250
251 P_control_rec => l_control_rec,
252 x_order_header_rec => x_order_header_rec,
253 x_order_line_tbl => x_order_line_tbl,
254
255 x_return_status => l_return_status,
256 x_msg_count => x_msg_count,
257 x_msg_data => x_msg_data
258 );
259
260 --
261 -- If any errors happen abort API.
262 --
263 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
264 RAISE FND_API.G_EXC_ERROR;
265 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
267 END IF;
268
269 IF (AMS_DEBUG_HIGH_ON) THEN
270 AMS_UTILITY_PVT.Debug_message('order header is '|| x_order_header_rec.order_header_id);
271 END IF;
272
273 IF (AMS_DEBUG_HIGH_ON) THEN
274 AMS_UTILITY_PVT.Debug_message('order line is ' || x_order_line_tbl(1).order_line_id);
275 END IF;
276
277 END LOOP ;
278 --
279 -- set OUT value
280 --
281 x_return_status := l_return_status ;
282 --
283 -- END of API body.
284 --
285
286 --
287 -- Standard call to get message count AND IF count is 1, get message info.
288 --
289 FND_MSG_PUB.Count_AND_Get
290 ( p_count => x_msg_count,
291 p_data => x_msg_data,
292 p_encoded => FND_API.G_FALSE
293 );
294
295 IF (AMS_DEBUG_HIGH_ON) THEN
296 AMS_Utility_PVT.debug_message(l_full_name ||': end');
297 END IF;
298
299
300 EXCEPTION
301 WHEN FND_API.G_EXC_ERROR THEN
302
303 ROLLBACK TO FulFill_OC_PT;
304 x_return_status := FND_API.G_RET_STS_ERROR ;
305
306 FND_MSG_PUB.Count_AND_Get
307 ( p_count => x_msg_count,
308 p_data => x_msg_data,
309 p_encoded => FND_API.G_FALSE
310 );
311 ams_utility_pvt.display_messages;
312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
313
314 ROLLBACK TO FulFill_OC_PT;
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
316
317 FND_MSG_PUB.Count_AND_Get
318 ( p_count => x_msg_count,
319 p_data => x_msg_data,
320 p_encoded => FND_API.G_FALSE
321 );
322 ams_utility_pvt.display_messages;
323
324 WHEN OTHERS THEN
325
326 ROLLBACK TO FulFill_OC_PT;
327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
328
329 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
330 THEN
331 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
332 END IF;
333
334 FND_MSG_PUB.Count_AND_Get
335 ( p_count => x_msg_count,
336 p_data => x_msg_data,
337 p_encoded => FND_API.G_FALSE
338 );
339
340 ams_utility_pvt.display_messages;
341
342 END FulFill_OC ;
343
344
345
346
347
348
349 -- Start of Comments
350 --
351 -- NAME
352 -- AMS_FULFILL
353 --
354 -- PURPOSE
355 -- This procedure is use to Fulfill the Marketing Collaterals.
356 -- It also creates the Coverletter.
357 -- It calls Get_Master_Info to get the XML for the Cover letter and
358 -- then it calls Get_Deliverables_Info to get XML for Deliverables
359 -- Procedure internally calls Fulfillment API to kick off
360 -- fulfillment engine
361 --
362 -- NOTES
363 --
364 --
365 -- HISTORY
366 -- 11/12/1999 ptendulk created
367 -- 03/08/2000 ptendulk Modified after changes from fulfillment team
368 -- 05/09/2000 ptendulk Modified 1.Get the subject from the schedules table
369 -- 2. get the user id from FND_GLOBALS
370 -- 05/22/2000 ptendulk Modified AMS_FULFILL procedure, Get the partyid
371 -- from party id column instead of customer id column
372 -- in ams_list_entries table
373 -- 11-Dec-2000 ptendulk Added the extra paramter in spec of get content XML
374 -- Ref Bug # 1529231
375 -- 13-Jun-2001 ptendulk Added schedule source code,objectid to the api to
376 -- write to interaction.
377 -- 30-may-2002 soagrawa Modified call to submit batch request to pass it profile_id
378 -- Procedure now takes profile_id as a parameter
379 -- 15-jul-2002 soagrawa Modified call to submit batch request to pass it source code
380 -- and source code id of the schedule. Added cursors for that.
381 -- 14-aug-2002 soagrawa Fixed user id and resource id related bug# 2490929
382 -- 08-may-2003 anchaudh Added extended header
383 -- 22-aug-2003 soagrawa Fixed bug# 3111735 in extended header.
384 -- 28-aug-2003 soagrawa Fixed bug# 3119662
385 -- 30-sep-2003 soagrawa Modified for integration with JTO 11.5.10
386 -- 28-jan-2005 spendem Fix for bug # 4145845. Added to_char function to the schedule_id
387 -- 29-dec-2005 kbasavar Added references to email_format for delivery_mode fix
388 -- End of Comments
389 PROCEDURE AMS_FULFILL
390 (p_api_version IN NUMBER,
391 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
392 p_commit IN VARCHAR2 := FND_API.G_False,
393
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER ,
396 x_msg_data OUT NOCOPY VARCHAR2,
397
398 x_request_history_id OUT NOCOPY NUMBER,
399 p_schedule_id IN NUMBER,
400
401 p_profile_id IN NUMBER := fnd_profile.VALUE('AMF_DEFAULT_MAIL_PROFILE'),
402 p_user_id IN NUMBER := FND_GLOBAL.user_id )
403 IS
404
405 l_api_name CONSTANT VARCHAR2(30) := 'AMS_FulFill';
406 l_api_version CONSTANT NUMBER := 1.0;
407 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
408
409
410 l_return_status VARCHAR2(1);
411
412 CURSOR c_csch_det IS
413 SELECT sender_display_name,mail_subject,source_code, start_Date_time,mail_sender_name,reply_to_mail, owner_user_id, activity_id, printer_address, delivery_mode
414 FROM ams_campaign_schedules_b
415 WHERE schedule_id = p_schedule_id ;
416
417 -- following cursor added by soagrawa on 15-jul-2002
418 CURSOR c_csch_source_code(l_id IN VARCHAR2) IS
419 SELECT source_code_id
420 FROM ams_source_codes
421 WHERE source_code = l_id
422 AND active_flag = 'Y';
423
424 -- following cursor added by soagrawa on 14-jul-2002 for bug# 2490929
425 CURSOR c_resource IS
426 SELECT resource_id
427 FROM ams_jtf_rs_emp_v
428 WHERE user_id = p_user_id;
429
430 -- following cursor added by soagrawa on 20-sep-2003 for 11.5.10 JTO integration
431 CURSOR c_cover_letter_det IS
432 SELECT content_item_id, citem_version_id
433 FROM ibc_associations
434 WHERE association_type_code = 'AMS_CSCH'
435 AND associated_object_val1 = to_char(p_schedule_id); -- fix for bug # 4145845
436
437 --anchaudh added for R12
438 CURSOR c_bypass_flag IS
439 SELECT APPLY_SUPPRESSION_FLAG
440 FROM ams_list_headers_all
441 WHERE arc_list_used_by = 'CSCH'
442 AND list_used_by_id = p_schedule_id;
443
444 l_subject VARCHAR2(1000) ;
445 l_source_code VARCHAR2(30);
446 l_start_Date DATE;
447 l_template_id NUMBER;
448 l_bind_values JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
449 l_source_code_id NUMBER;
450 l_resource_id NUMBER;
451 l_sender VARCHAR2(120) ;
452 l_reply_to VARCHAR2(120) ;
453 l_sender_display_name VARCHAR2(120) ;
454
455 --anchaudh added for R12
456 l_bypass_flag VARCHAR2(30);
457
458 -- soagrawa added these definitions for integrating with 1159 1-to-1 FFM
459 -- 05-dec-2002
460 l_order_header_rec JTF_Fulfillment_PUB.ORDER_HEADER_REC_TYPE;
461 l_order_line_tbl JTF_Fulfillment_PUB.ORDER_LINE_TBL_TYPE;
462 l_fulfill_electronic_rec JTF_FM_OCM_REQUEST_GRP.FULFILL_ELECTRONIC_REC_TYPE;
463 y_order_header_rec ASO_ORDER_INT.ORDER_HEADER_REC_TYPE;
464 l_request_type VARCHAR2(32) := 'E';
465 l_extended_header VARCHAR2(32767) ;
466 -- soagrawa added the following variable on 28-aug-2003 to fix bug# 3119662
467 l_user_id NUMBER := p_user_id;
468 l_csch_owner_user_id NUMBER ;
469
470 -- soagrawa 30-sep-2003 added for integrating with 11.5.10 JTO
471 l_template_ver_id NUMBER;
472 l_media_types VARCHAR2(30) := 'E';
473 l_activity_id NUMBER;
474 l_fulfilment VARCHAR2(30);
475
476 l_printer JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
477 l_printer_address VARCHAR2(255);
478
479 l_delivery_mode VARCHAR2(30);
480 BEGIN
481 --
482 -- Standard Start of API savepoint
483 --
484 SAVEPOINT Create_FULFILL_PVT;
485
486 --
487 -- Debug Message
488 --
489 IF (AMS_DEBUG_HIGH_ON) THEN
490 AMS_Utility_PVT.debug_message(l_full_name||': start');
491 END IF;
492
493 --
494 -- Initialize message list IF p_init_msg_list is set to TRUE.
495 --
496 IF FND_API.to_Boolean( p_init_msg_list ) THEN
497 FND_MSG_PUB.initialize;
498 END IF;
499
500 --
501 -- Standard call to check for call compatibility.
502 --
503 IF NOT FND_API.Compatible_API_Call ( l_api_version,
504 p_api_version,
505 l_api_name,
506 G_PKG_NAME)
507 THEN
508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
509 END IF;
510
511 --
512 -- Initialize API return status to success
513 --
514 x_return_status := FND_API.G_RET_STS_SUCCESS;
515
516 --
517 -- API body
518 --
519
520 l_fulfilment := FND_PROFILE.Value('AMS_FULFILL_ENABLE_FLAG');
521 IF( l_fulfilment <> 'N' )
522 THEN
523 -- get all needed data of the schedule
524 OPEN c_csch_det;
525 FETCH c_csch_det INTO l_sender_display_name,l_subject,l_source_code, l_start_date,l_sender,l_reply_to,l_csch_owner_user_id, l_activity_id, l_printer_address, l_delivery_mode ;
526 CLOSE c_csch_det ;
527
528 -- get associated cover letter info soagrawa 30-sep-2003 for JTO integration 11.5.10
529 OPEN c_cover_letter_det;
530 FETCH c_cover_letter_det INTO l_template_id, l_template_ver_id;
531 CLOSE c_cover_letter_det ;
532
533 IF l_template_id IS null
534 THEN
535 AMS_Utility_PVT.Error_Message('AMS_CSCH_NO_COVER_LETTER');
536 RAISE FND_API.g_exc_error;
537 END IF;
538
539 -- soagrawa 30-sep-2003 added for 11.5.10
540 IF l_activity_id = 10
541 THEN
542 l_media_types := 'F'; -- fax
543 ELSIF l_activity_id = 20
544 THEN
545 l_media_types := 'E'; -- email
546 ELSIF l_activity_id = 480
547 THEN
548 l_media_types := 'P'; -- print
549 END IF;
550
551 -- soagrawa added 15-jul-2002
552 -- get all needed data of the schedule
553 OPEN c_csch_source_code(l_source_code);
554 FETCH c_csch_source_code INTO l_source_code_id;
555 CLOSE c_csch_source_code ;
556
557 -- soagrawa added 14-aug-2002 for bug# 2490929
558 OPEN c_resource;
559 FETCH c_resource INTO l_resource_id;
560 CLOSE c_resource;
561
562 -- anchaudh added for R12
563 OPEN c_bypass_flag;
564 FETCH c_bypass_flag INTO l_bypass_flag;
565 CLOSE c_bypass_flag;
566
567 -- bind values
568 l_bind_values(1) := TO_CHAR(p_schedule_id);
569
570 -- set the values for record type l_fulfill_electronic_rec before calling the 1-to-1 api
571
572 -- soagrawa added the following IF part on 28-aug-2003 to fix bug# 3119662
573 IF l_user_id IS NULL
574 OR l_user_id = -1
575 THEN
576 l_user_id := Ams_Utility_pvt.get_user_id(l_csch_owner_user_id);
577 END IF;
578
579 l_fulfill_electronic_rec.template_id := l_template_id;
580 l_fulfill_electronic_rec.version_id := l_template_ver_id;
581 l_fulfill_electronic_rec.object_type := 'AMS_CSCH'; --'CSCH'; modified for 11.5.10
582 l_fulfill_electronic_rec.object_id := p_schedule_id;
583 l_fulfill_electronic_rec.source_code := l_source_code;
584 l_fulfill_electronic_rec.source_code_id := l_source_code_id;
585 --l_fulfill_electronic_rec.requestor_type := l_resource_id;
586 -- soagrawa modified on 28-aug-2003 to fix bug# 3119662
587 l_fulfill_electronic_rec.requestor_id := l_user_id; --l_resource_id;
588 --l_fulfill_electronic_rec.requestor_id := p_user_id; --l_resource_id;
589 -- l_fulfill_electronic_rec.server_group := server_group;
590 l_fulfill_electronic_rec.schedule_date := l_start_date;
591 l_fulfill_electronic_rec.media_types := l_media_types; -- added for 11.5.10
592 --l_fulfill_electronic_rec.archive := 'N'; -- thts the default
593 l_fulfill_electronic_rec.log_user_ih := 'Y';
594 l_fulfill_electronic_rec.request_type := 'E';
595 --l_fulfill_electronic_rec.profile_id := p_profile_id;
596 --l_fulfill_electronic_rec.order_id := order_id;
597 --l_fulfill_electronic_rec.collateral_id := collateral_id;
598 l_fulfill_electronic_rec.subject := l_subject;
599 --l_fulfill_electronic_rec.party_id := party_id;
600 --l_fulfill_electronic_rec.email := email;
601 --l_fulfill_electronic_rec.fax := fax;
602 l_fulfill_electronic_rec.bind_values := l_bind_values;
603 l_fulfill_electronic_rec.bind_names(1) := 'schedule_id';
604 --l_fulfill_electronic_rec.email_text := email_text;
605 --l_fulfill_electronic_rec.content_name := content_name;
606 --l_fulfill_electronic_rec.content_type := content_type;
607 l_fulfill_electronic_rec.email_format := nvl(l_delivery_mode, 'BOTH');
608
609 -- anchaudh added for R12
610 if(l_bypass_flag = 'N') then
611 l_fulfill_electronic_rec.stop_list_bypass := 'B';
612 end if;
613
614 -- soagrawa 22-aug-2003 added the following if clause for bug# 3111735
615 IF l_activity_id = 20
616 THEN
617 IF l_sender IS NOT NULL
618 AND l_reply_to IS NOT null
619 THEN
620 --start: added by anchaudh on 08-may-2003.
621 l_extended_header := '<extended_header>
622 <header_name>email_from_address</header_name>
623 <header_value>' ||l_sender|| '</header_value>
624 <header_name>email_reply_to_address</header_name>
625 <header_value>' ||l_reply_to|| '</header_value>
626 <header_name>sender_display_name</header_name>
627 <header_value>' ||l_sender_display_name|| '</header_value>
628 </extended_header>';
629
630 l_fulfill_electronic_rec.extended_header := l_extended_header;
631
632 --end: added by anchaudh on 08-may-2003.
633 END IF; -- ends soagrawa IF 22-aug-2003 bug# 3111735
634 END IF;
635
636 IF l_activity_id = 480
637 THEN
638 l_printer(1) := l_printer_address;
639 l_fulfill_electronic_rec.printer := l_printer;
640 END IF;
641
642 -- soagrawa modified for 11.5.10 JTO integration
643 --JTF_FM_OCM_REQUEST_GRP.create_fulfillment
644 JTF_FM_OCM_REND_REQ.create_fulfillment_rendition
645 (
646 p_init_msg_list => p_init_msg_list,
647 p_api_version => l_api_version,
648 p_commit => p_commit,
649 p_order_header_rec => l_order_header_rec,
650 p_order_line_tbl => l_order_line_tbl,
651 p_fulfill_electronic_rec => l_fulfill_electronic_rec,
652 p_request_type => l_request_type,
653 x_return_status => l_return_status,
654 x_msg_count => x_msg_count,
655 x_msg_data => x_msg_data,
656 x_order_header_rec => y_order_header_rec,
657 x_request_history_id => x_request_history_id
658 );
659
660 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
661 RAISE FND_API.G_EXC_ERROR;
662 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
664 END IF;
665
666 IF (AMS_DEBUG_HIGH_ON) THEN
667 AMS_Utility_PVT.debug_message('Return Status: '||l_return_status||x_request_history_id);
668 END IF;
669
670 x_return_status := l_return_status;
671
672 END IF; -- if fulfillment is enabled
673
674 --
675 -- END of API body.
676 --
677
678 --
679 -- Standard check of p_commit.
680 --
681 IF FND_API.To_Boolean ( p_commit )
682 THEN
683 COMMIT WORK;
684 END IF;
685
686 --
687 -- Standard call to get message count AND IF count is 1, get message info.
688 --
689 FND_MSG_PUB.Count_AND_Get
690 ( p_count => x_msg_count,
691 p_data => x_msg_data,
692 p_encoded => FND_API.G_FALSE
693 );
694
695 IF (AMS_DEBUG_HIGH_ON) THEN
696 AMS_Utility_PVT.debug_message(l_full_name ||': end');
697 END IF;
698
699
700 EXCEPTION
701 WHEN FND_API.G_EXC_ERROR THEN
702
703 ROLLBACK TO Create_FULFILL_PVT;
704 x_return_status := FND_API.G_RET_STS_ERROR ;
705
706 FND_MSG_PUB.Count_AND_Get
707 ( p_count => x_msg_count,
708 p_data => x_msg_data,
709 p_encoded => FND_API.G_FALSE
710 );
711 ams_utility_pvt.display_messages;
712 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
713
714 ROLLBACK TO Create_FULFILL_PVT;
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
716
717 FND_MSG_PUB.Count_AND_Get
718 ( p_count => x_msg_count,
719 p_data => x_msg_data,
720 p_encoded => FND_API.G_FALSE
721 );
722 ams_utility_pvt.display_messages;
723
724 WHEN OTHERS THEN
725
726 ROLLBACK TO Create_FULFILL_PVT;
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
728
729 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
730 THEN
731 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
732 END IF;
733
734 FND_MSG_PUB.Count_AND_Get
735 ( p_count => x_msg_count,
736 p_data => x_msg_data,
737 p_encoded => FND_API.G_FALSE
738 );
739 ams_utility_pvt.display_messages;
740 END AMS_FULFILL ;
741
742
743 -- Start of Comments
744 --
745 -- NAME
746 -- AMS_EXEC_SCHEDULE
747 --
748 -- PURPOSE
749 -- This procedure is wrapper on ams_fulfill
750 -- It will be called from schedules to execute the list.
751 -- The procedure first updates the list with the schedule details,
752 -- it executes the list , and then updates the list sent out date .
753 --
754 -- NOTES
755 --
756 --
757 -- HISTORY
758 -- 10/27/2000 ptendulk created
759 -- End of Comments
760 PROCEDURE AMS_EXEC_SCHEDULE
761 (p_api_version IN NUMBER,
762 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
763 p_commit IN VARCHAR2 := FND_API.G_False,
764
765 x_return_status OUT NOCOPY VARCHAR2,
766 x_msg_count OUT NOCOPY NUMBER ,
767 x_msg_data OUT NOCOPY VARCHAR2,
768
769 p_list_header_id IN NUMBER,
770 p_schedule_id IN NUMBER,
771 p_exec_flag IN VARCHAR2)
772 IS
773
774 CURSOR c_list_details IS
775 SELECT object_version_number
776 FROM ams_list_headers_all
777 WHERE list_header_id = p_list_header_id ;
778
779
780 l_list_rec AMS_LISTHEADER_PVT.list_header_rec_type;
781 l_api_name CONSTANT VARCHAR2(30) := 'AMS_EXEC_SCHEDULE';
782 l_api_version CONSTANT NUMBER := 1.0;
783 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
784
785 l_return_status VARCHAR2(1);
786
787 l_request_history_id NUMBER;
788
789 BEGIN
790 --
791 -- Standard Start of API savepoint
792 --
793 SAVEPOINT AMS_EXEC_SCHEDULE;
794
795 --
796 -- Debug Message
797 --
798 IF (AMS_DEBUG_HIGH_ON) THEN
799 AMS_Utility_PVT.debug_message(l_full_name||': start');
800 END IF;
801
802 --
803 -- Initialize message list IF p_init_msg_list is set to TRUE.
804 --
805 IF FND_API.to_Boolean( p_init_msg_list ) THEN
806 FND_MSG_PUB.initialize;
807 END IF;
808
809 --
810 -- Standard call to check for call compatibility.
811 --
812 IF NOT FND_API.Compatible_API_Call ( l_api_version,
813 p_api_version,
814 l_api_name,
815 G_PKG_NAME)
816 THEN
817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
818 END IF;
819
820 --
821 -- Initialize API return status to success
822 --
823 x_return_status := FND_API.G_RET_STS_SUCCESS;
824
825 -- Update the list header with the Schedule details.
826 AMS_LISTHEADER_PVT.Init_ListHeader_rec(x_listheader_rec => l_list_rec);
827 l_list_rec.list_header_id := p_list_header_id ;
828
829 OPEN c_list_details ;
830 FETCH c_list_details INTO l_list_rec.object_version_number ;
831 CLOSE c_list_details ;
832 l_list_rec.arc_list_used_by := 'CSCH' ; -- Campaign Schedule
833 l_list_rec.list_used_by_id := p_schedule_id ; -- Campaign Schedule
834
835 AMS_LISTHEADER_PVT.Update_ListHeader
836 ( p_api_version => p_api_version,
837 p_init_msg_list => FND_API.G_FALSE,
838 p_commit => FND_API.G_FALSE,
839 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
840
841 x_return_status => x_return_status,
842 x_msg_count => x_msg_count,
843 x_msg_data => x_msg_data ,
844
845 p_listheader_rec => l_list_rec
846 );
847
848 -- If any errors happen abort API.
849 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
850 RAISE FND_API.G_EXC_ERROR;
851 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
853 END IF;
854
855 --
856 -- Execute the list if required
857 --
858 IF p_exec_flag = 'Y' THEN
859 -- Call the fulfillment APi
860 AMS_FULFILL
861 (p_api_version => p_api_version ,
862 p_init_msg_list => p_init_msg_list,
863 p_commit => p_commit,
864
865 x_return_status => x_return_status,
866 x_msg_count => x_msg_count,
867 x_msg_data => x_msg_data,
868
869 x_request_history_id => l_request_history_id,
870 p_schedule_id => p_schedule_id ) ;
871
872
873 -- If any errors happen abort API.
874 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
875 RAISE FND_API.G_EXC_ERROR;
876 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
878 END IF;
879
880 --
881 -- Update the list sent out date with sysdate if success
882 --
883 AMS_LISTHEADER_PVT.Init_ListHeader_rec(x_listheader_rec => l_list_rec);
884 l_list_rec.list_header_id := p_list_header_id ;
885
886 OPEN c_list_details ;
887 FETCH c_list_details INTO l_list_rec.object_version_number ;
888 CLOSE c_list_details ;
889 l_list_rec.sent_out_date := sysdate ;
890
891 AMS_LISTHEADER_PVT.Update_ListHeader
892 ( p_api_version => p_api_version,
893 p_init_msg_list => FND_API.G_FALSE,
894 p_commit => FND_API.G_FALSE,
895 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
896
897 x_return_status => x_return_status,
898 x_msg_count => x_msg_count,
899 x_msg_data => x_msg_data ,
900
901 p_listheader_rec => l_list_rec
902 );
903
904 -- If any errors happen abort API.
905 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
906 RAISE FND_API.G_EXC_ERROR;
907 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 END IF;
910
911 END IF ;
912
913 --
914 -- Standard check of p_commit.
915 --
916 IF FND_API.To_Boolean ( p_commit )
917 THEN
918 COMMIT WORK;
919 END IF;
920
921 --
922 -- Standard call to get message count AND IF count is 1, get message info.
923 --
924 FND_MSG_PUB.Count_AND_Get
925 ( p_count => x_msg_count,
926 p_data => x_msg_data,
927 p_encoded => FND_API.G_FALSE
928 );
929
930 IF (AMS_DEBUG_HIGH_ON) THEN
931 AMS_Utility_PVT.debug_message(l_full_name ||': end');
932 END IF;
933
934
935
936 EXCEPTION
937 WHEN FND_API.G_EXC_ERROR THEN
938
939 ROLLBACK TO AMS_EXEC_SCHEDULE;
940 x_return_status := FND_API.G_RET_STS_ERROR ;
941
942 FND_MSG_PUB.Count_AND_Get
943 ( p_count => x_msg_count,
944 p_data => x_msg_data,
945 p_encoded => FND_API.G_FALSE
946 );
947
948 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
949
950 ROLLBACK TO AMS_EXEC_SCHEDULE;
951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
952
953 FND_MSG_PUB.Count_AND_Get
954 ( p_count => x_msg_count,
955 p_data => x_msg_data,
956 p_encoded => FND_API.G_FALSE
957 );
958
959 WHEN OTHERS THEN
960
961 ROLLBACK TO AMS_EXEC_SCHEDULE;
962 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
963
964 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
965 THEN
966 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
967 END IF;
968
969 FND_MSG_PUB.Count_AND_Get
970 ( p_count => x_msg_count,
971 p_data => x_msg_data,
972 p_encoded => FND_API.G_FALSE
973 );
974 END AMS_EXEC_SCHEDULE ;
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990 -- Start of Comments
991 --
992 -- NAME
993 -- Get_Master_Info
994 --
995 -- PURPOSE
996 -- This procedure is to create XML string for the Master document
997 --
998 --
999 -- NOTES
1000 --
1001 --
1002 -- HISTORY
1003 -- 11/12/1999 ptendulk created
1004 -- 05/08/2000 ptendulk Modified , 1.Commented document_type
1005 -- in Get content XML api 2. Get the cover letter
1006 -- from Schedules instead of campaign table
1007 -- 11-Dec-2000 ptendulk Added extra parameter x_extended_header into the
1008 -- spec of the get_master_info
1009 -- 22-Mar-2001 soagrawa Modified cursor c_camp_det
1010 -- to access activity type info from schedule
1011 -- 23-Mar-2001 soagrawa Modified cursor c_sch_det, commented c_camp_det
1012 -- 30-May-2001 soagrawa Modified call to submit_batch_request
1013 -- Now passing parameter p_per_user_history as true
1014 -- to write to interaction history
1015 -- Bug# 1717384
1016 -- 18-jan-2002 soagrawa Fixed bug# 2186980
1017 -- 29-apr-2002 soagrawa Removed as now using new FFM
1018 -- End of Comments
1019 /*
1020 PROCEDURE Get_Master_Info
1021 (p_api_version IN NUMBER,
1022 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
1023
1024 x_return_status OUT NOCOPY VARCHAR2,
1025 x_msg_count OUT NOCOPY NUMBER,
1026 x_msg_data OUT NOCOPY VARCHAR2,
1027
1028 p_list_header_id IN NUMBER,
1029 p_schedule_id IN NUMBER,
1030 p_request_id IN NUMBER ,
1031 x_content_xml OUT NOCOPY VARCHAR2,
1032 x_extended_header OUT NOCOPY VARCHAR2)
1033 IS
1034
1035 l_api_name CONSTANT VARCHAR2(30) := 'Get_Master_Info';
1036 l_api_version CONSTANT NUMBER := 1.0;
1037 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1038
1039 l_return_status VARCHAR2(1);
1040
1041 CURSOR c_csch_det IS
1042 SELECT csch.campaign_id,csch.cover_letter_id cover_letter,item.item_name name,
1043 csch.mail_sender_name, csch.reply_to_mail replyto_mail_id, csch.from_fax_no,
1044 csch.activity_type_code, csch.activity_id
1045 FROM ams_campaign_schedules_b csch,jtf_amv_items_vl item
1046 WHERE schedule_id = p_schedule_id
1047 AND csch.cover_letter_id = item.item_id ;
1048 l_camp_id NUMBER;
1049
1050 l_content_type VARCHAR2(30) ;
1051 l_user_notes VARCHAR2(240) ;
1052 l_content_id NUMBER ;
1053 --modified by soagrawa on 18-jan-2002, bug# 2186980
1054 l_content_nm VARCHAR2(240); --(50) ;
1055 l_media_type VARCHAR2(30) ;
1056 l_content_xml VARCHAR2(4000) ;
1057
1058 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
1059 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
1060 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
1061 l_cnt NUMBER := 0 ;
1062
1063 l_extended_header VARCHAR2(2000);
1064 l_sender VARCHAR2(120);
1065 l_reply_to VARCHAR2(240);
1066 l_from_fax VARCHAR2(25);
1067
1068 -- added by soagrawa on 23Mar2001
1069 l_media_type_code VARCHAR2(30) ;
1070 l_media_id NUMBER ;
1071
1072
1073 BEGIN
1074 --
1075 -- Debug Message
1076 --
1077 IF (AMS_DEBUG_HIGH_ON) THEN
1078 AMS_Utility_PVT.debug_message(l_full_name||': start');
1079 END IF;
1080
1081 --
1082 -- Initialize message list IF p_init_msg_list is set to TRUE.
1083 --
1084 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1085 FND_MSG_PUB.initialize;
1086 END IF;
1087
1088 --
1089 -- Standard call to check for call compatibility.
1090 --
1091 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1092 p_api_version,
1093 l_api_name,
1094 G_PKG_NAME)
1095 THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 END IF;
1098
1099 --
1100 -- Initialize API return status to success
1101 --
1102 l_return_status := FND_API.G_RET_STS_SUCCESS;
1103
1104 --
1105 -- API body
1106 --
1107 OPEN c_csch_det;
1108 FETCH c_csch_det INTO l_camp_id,l_content_id,l_content_nm,l_sender,l_reply_to,l_from_fax,
1109 l_media_type_code,l_media_id ;
1110 CLOSE c_csch_det;
1111
1112 IF (AMS_DEBUG_HIGH_ON) THEN
1113 AMS_Utility_PVT.debug_message('Cover Letter : '|| l_content_id);
1114 END IF;
1115
1116 IF l_content_id IS NULL THEN
1117 --
1118 -- Send Collaterals without Coverletter : Confirm
1119 --
1120 x_return_status := FND_API.G_RET_STS_SUCCESS;
1121 RETURN ;
1122 END IF;
1123
1124 IF (l_media_type_code = 'DIRECT_MARKETING') AND
1125 (l_media_id = 10 OR l_media_id = 20 )
1126 THEN
1127 -- Create Master Document XML
1128 l_content_type := 'QUERY'; -- For the Master Document
1129
1130 IF l_media_id = 10 THEN
1131 l_media_type := 'FAX' ;
1132 x_extended_header := null ;
1133 ELSE
1134 l_media_type := 'EMAIL' ;
1135 -- Following code is added by PTENDULK on 11-Dec-2000
1136 -- Ref Bug# 1529231 . Create the extended header to support the
1137 -- reply to functionality
1138 -- Note : Fulfillment currently doesn't support having different Sunder name ,
1139 -- and Reply to . So currently using the reply to functionality .
1140 IF l_sender IS NOT NULL THEN
1141 l_extended_header := '<extended_header media_type="EMAIL">';
1142 l_extended_header := l_extended_header||'<header_name>';
1143 l_extended_header := l_extended_header||'From';
1144 l_extended_header := l_extended_header||'</header_name>';
1145 l_extended_header := l_extended_header||'<header_value>'||l_sender||'</header_value>';
1146 l_extended_header := l_extended_header||'</extended_header>';
1147 ELSE
1148 l_extended_header := NULL ;
1149 END IF ;
1150
1151 -- following code is added by ptendulk on 13-Jun-2001
1152 -- to support sender as well as the reply to email address.
1153 IF l_reply_to IS NOT NULL THEN
1154 l_extended_header := l_extended_header||'<extended_header media_type="EMAIL">';
1155 l_extended_header := l_extended_header||'<header_name>';
1156 l_extended_header := l_extended_header||'email_reply_to_address';
1157 l_extended_header := l_extended_header||'</header_name>';
1158 l_extended_header := l_extended_header||'<header_value>'||l_reply_to||'</header_value>';
1159 l_extended_header := l_extended_header||'</extended_header>';
1160 END IF ;
1161
1162 x_extended_header := l_extended_header ;
1163 END IF ;
1164
1165 l_bind_var(1) := 'id' ;
1166 l_bind_var_type(1) := 'NUMBER' ;
1167 l_bind_val(1) := p_list_header_id ;
1168
1169 -- No need to Pass Bind Variables to the Following API as the
1170 -- bind Variable defined for the query of Master Doc is Party_id
1171 -- So the FF Engine will create Master Document in Submit Batch Process
1172 -- Also no need to pass Fax no / Email as well ,as these details will
1173 -- pass that in Submit Batch Process API
1174 JTF_FM_REQUEST_GRP.Get_Content_XML
1175 ( p_api_version => l_api_version,
1176 x_return_status => l_return_status,
1177 x_msg_count => x_msg_count,
1178 x_msg_data => x_msg_data,
1179 p_content_id => l_content_id,
1180 p_content_nm => l_content_nm,
1181 --p_document_type => 'WORD',
1182 p_media_type => l_media_type,
1183 p_user_note => l_user_notes,
1184 p_content_type => l_content_type,
1185 p_bind_var => l_bind_var,
1186 p_bind_val => l_bind_val,
1187 p_bind_var_type => l_bind_var_type,
1188 p_request_id => p_request_id,
1189 x_content_xml => l_content_xml);
1190 --
1191 -- If any errors happen abort API.
1192 --
1193 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1194 x_return_status := l_return_status ;
1195 RETURN ;
1196 END IF;
1197
1198 ELSE
1199 IF (AMS_DEBUG_HIGH_ON) THEN
1200 AMS_Utility_PVT.debug_message('Can only Fulfill Email or Fax');
1201 END IF;
1202 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1203 THEN -- MMSG
1204 FND_MESSAGE.Set_Name('AMS', 'AMS_FFM_INVALID_MEDIA');
1205 FND_MSG_PUB.Add;
1206 END IF;
1207 RAISE FND_API.G_EXC_ERROR;
1208 x_return_status := FND_API.G_RET_STS_ERROR ;
1209 RETURN;
1210 END IF;
1211
1212 --
1213 -- set OUT value
1214 --
1215 x_content_xml := l_content_xml ;
1216 x_return_status := l_return_status ;
1217
1218 --
1219 -- END of API body.
1220 --
1221
1222 --
1223 -- Standard call to get message count AND IF count is 1, get message info.
1224 --
1225 FND_MSG_PUB.Count_AND_Get
1226 ( p_count => x_msg_count,
1227 p_data => x_msg_data,
1228 p_encoded => FND_API.G_FALSE
1229 );
1230
1231 IF (AMS_DEBUG_HIGH_ON) THEN
1232 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1233 END IF;
1234
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1238
1239 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1240 THEN
1241 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1242 END IF;
1243
1244 FND_MSG_PUB.Count_AND_Get
1245 ( p_count => x_msg_count,
1246 p_data => x_msg_data,
1247 p_encoded => FND_API.G_FALSE
1248 );
1249 END Get_Master_Info;
1250 */
1251
1252
1253 -- Start of Comments
1254 --
1255 -- NAME
1256 -- Get_Kit_Info
1257 --
1258 -- PURPOSE
1259 -- This procedure is to create XML string for the kits (if any
1260 -- present for Deliverable
1261 --
1262 -- NOTES
1263 -- This api is absoleted now , as for eBlast the attachments are
1264 -- are directly created for schedule and not coming as deliverables.
1265 --
1266 -- HISTORY
1267 -- 11/12/1999 ptendulk created
1268 -- 05/08/2000 ptendulk Modified , Commented document_type
1269 -- in Get conttent XML api
1270 -- 29-apr-2002 soagrawa Removed as now using new FFM
1271 --
1272 -- End of Comments
1273 /*
1274 PROCEDURE Get_Kit_Info
1275 (p_api_version IN NUMBER,
1276 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
1277
1278 x_return_status OUT NOCOPY VARCHAR2,
1279 x_msg_count OUT NOCOPY NUMBER,
1280 x_msg_data OUT NOCOPY VARCHAR2,
1281
1282 p_deliv_id IN NUMBER,
1283 p_request_id IN NUMBER ,
1284 p_media_type IN VARCHAR2,
1285 p_user_notes IN VARCHAR2,
1286 x_content_xml OUT NOCOPY VARCHAR2)
1287 IS
1288 l_api_name CONSTANT VARCHAR2(30) := 'Get_Kit_Info' ;
1289 l_api_version CONSTANT NUMBER := 1.0;
1290 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1291
1292
1293 CURSOR c_kit_det IS
1294 SELECT deli.jtf_amv_item_id ,
1295 deli.deliverable_name,
1296 deli.kit_flag
1297 FROM ams_deliverables_vl deli,ams_deliv_kit_items kit
1298 WHERE kit.deliverable_kit_id = p_deliv_id
1299 AND kit.deliverable_kit_part_id = deli.deliverable_id
1300 AND deli.can_fulfill_electronic_flag = 'Y' ;
1301
1302
1303 l_content_id NUMBER;
1304 l_content_nm VARCHAR2(240) ;
1305 l_content_type VARCHAR2(30) := 'COLLATERAL';
1306 l_kit_flag VARCHAR2(1);
1307
1308 l_content_xml VARCHAR2(4000);
1309 l_final_content VARCHAR2(4000);
1310
1311 BEGIN
1312 --
1313 -- Debug Message
1314 --
1315 IF (AMS_DEBUG_HIGH_ON) THEN
1316 AMS_Utility_PVT.debug_message(l_full_name||': start');
1317 END IF;
1318
1319 --
1320 -- Initialize message list IF p_init_msg_list is set to TRUE.
1321 --
1322 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1323 FND_MSG_PUB.initialize;
1324 END IF;
1325
1326 --
1327 -- Standard call to check for call compatibility.
1328 --
1329 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1330 p_api_version,
1331 l_api_name,
1332 G_PKG_NAME)
1333 THEN
1334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1335 END IF;
1336
1337 --
1338 -- Initialize API return status to success
1339 --
1340 x_return_status := FND_API.G_RET_STS_SUCCESS;
1341
1342 --
1343 -- API body
1344 --
1345 OPEN c_kit_det ;
1346 LOOP
1347 FETCH c_kit_det INTO l_content_id ,l_content_nm,l_kit_flag ;
1348 EXIT WHEN c_kit_det%NOTFOUND;
1349 JTF_FM_REQUEST_GRP.Get_Content_XML
1350 ( p_api_version => l_api_version,
1351 x_return_status => x_return_status,
1352 x_msg_count => x_msg_count,
1353 x_msg_data => x_msg_data,
1354 p_content_id => l_content_id,
1355 p_content_nm => l_content_nm,
1356 -- p_document_type => 'WORD',
1357 p_media_type => p_media_type,
1358 p_user_note => p_user_notes,
1359 p_content_type => l_content_type,
1360 p_request_id => p_request_id,
1361 x_content_xml => l_content_xml);
1362
1363 --
1364 -- If any errors happen abort API.
1365 --
1366 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1367 RETURN ;
1368 END IF;
1369
1370 l_final_content := l_final_content || l_content_xml ;
1371
1372 END LOOP;
1373 CLOSE c_kit_det ;
1374
1375 --
1376 -- set OUT value
1377 --
1378 x_content_xml := l_content_xml ;
1379
1380 --
1381 -- END of API body.
1382 --
1383
1384 --
1385 -- Standard call to get message count AND IF count is 1, get message info.
1386 --
1387 FND_MSG_PUB.Count_AND_Get
1388 ( p_count => x_msg_count,
1389 p_data => x_msg_data,
1390 p_encoded => FND_API.G_FALSE
1391 );
1392
1393 IF (AMS_DEBUG_HIGH_ON) THEN
1394 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1395 END IF;
1396
1397
1398 EXCEPTION
1399 WHEN OTHERS THEN
1400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1401
1402 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1403 THEN
1404 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1405 END IF;
1406
1407 FND_MSG_PUB.Count_AND_Get
1408 ( p_count => x_msg_count,
1409 p_data => x_msg_data,
1410 p_encoded => FND_API.G_FALSE
1411 );
1412
1413 END Get_Kit_Info;
1414 */
1415
1416
1417
1418 -- Start of Comments
1419 --
1420 -- NAME
1421 -- Get_Deliverable_Info
1422 --
1423 -- PURPOSE
1424 -- This procedure is to create XML string for the Deliverables
1425 -- If the Deliverable is a kit it will call the Get_Kit_Info
1426 -- procedure to get the XML of the kit items
1427 --
1428 -- NOTES
1429 -- Date: 21Feb2000 : If the Collateral is Hard Collateral or if any
1430 -- of the kits of the collateral is hard collateral then Create order
1431 -- for all the collaterals and kits and Order capture will fulfill
1432 -- Them, but if all the collaterals are soft then Oracle Marketing
1433 -- will interact with Fulfillment directly and fulfill the collaterals
1434 --
1435 -- HISTORY
1436 -- 11/12/1999 ptendulk created
1437 -- 02/21/2000 ptendulk Modified the Order Capture Interaction.
1438 -- 05/08/2000 ptendulk Modified , Commented document_type
1439 -- in Get conttent XML api
1440 -- 21-Mar-2001 soagrawa Modified cursor c_ff_type
1441 -- to access activity type info from schedule
1442 -- 10-Jul-2001 ptendulk 1. modified as the attachments will be attached in
1443 -- the eBlast screen directly unlike prior releases
1444 -- where the deliverables attached to the schedules
1445 -- will go in as deliverable.
1446 -- 06-Sep-2001 soagrawa Modified cursor c_csch_det - changed attachment_used_by to
1447 -- 'AMS_'|| sys_Arc_qual
1448 -- 29-apr-2002 soagrawa Removed as now using new FFM
1449 -- End of Comments
1450
1451 /*
1452 PROCEDURE Get_Deliverable_Info
1453 (p_api_version IN NUMBER,
1454 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
1455
1456 x_return_status OUT NOCOPY VARCHAR2,
1457 x_msg_count OUT NOCOPY NUMBER ,
1458 x_msg_data OUT NOCOPY VARCHAR2,
1459
1460 p_act_id IN NUMBER ,
1461 p_arc_act IN VARCHAR2 ,
1462 p_list_header_id IN NUMBER := NULL,
1463 p_request_id IN NUMBER ,
1464 p_email_address IN VARCHAR2 := NULL,
1465
1466 x_content_xml OUT NOCOPY VARCHAR2)
1467 IS
1468 l_api_name CONSTANT VARCHAR2(30) := 'Get_Deliverable_Info';
1469 l_api_version CONSTANT NUMBER := 1.0;
1470 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1471 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1472
1473
1474 l_media_type VARCHAR2(30);
1475 l_media_type_code VARCHAR2(30);
1476 l_user_notes VARCHAR2(4000);
1477 l_media_id NUMBER ;
1478
1479
1480 --CURSOR c_csch_det IS
1481 -- SELECT deli.jtf_amv_item_id,deli.kit_flag,deli.deliverable_name,
1482 -- deli.can_fulfill_electronic_flag,
1483 -- csch.activity_type_code, csch.activity_id
1484 -- FROM ams_campaign_schedules_b csch, ams_deliverables_vl deli, ams_object_associations assoc
1485 -- WHERE csch.schedule_id = p_act_id
1486 -- AND assoc.master_object_type = 'CSCH'
1487 -- AND assoc.master_object_id= p_act_id
1488 -- AND assoc.using_object_type = 'DELV'
1489 -- AND deli.deliverable_id = assoc.using_object_id;
1490
1491 CURSOR c_csch_det IS
1492 SELECT jtf.file_id,jtf.file_name,
1493 ams.activity_type_code, ams.activity_id
1494 FROM ams_campaign_schedules_b ams,jtf_amv_attachments jtf
1495 -- modified by soagrawa, ptendulk on 06-Sep-2001
1496 -- WHERE jtf.attachment_used_by = p_arc_act
1497 WHERE jtf.attachment_used_by = 'AMS_'||p_arc_act
1498 AND jtf.attachment_used_by_id = ams.schedule_id
1499 AND jtf.can_fulfill_electronic_flag = 'Y'
1500 AND ams.schedule_id = p_act_id
1501 AND jtf.attachment_type = 'FILE' ;
1502
1503 --l_deli_id NUMBER;
1504 --l_kit_flag VARCHAR2(1);
1505 --l_deli_name VARCHAR2(240);
1506
1507
1508 --CURSOR c_kit_det IS
1509 -- SELECT deli.can_fulfill_electronic_flag
1510 -- FROM ams_deliverables_vl deli,ams_deliv_kit_items kit
1511 -- WHERE kit.deliverable_kit_id = l_deli_id
1512 -- AND kit.deliverable_kit_part_id = deli.deliverable_id;
1513
1514
1515 l_content_type VARCHAR2(30) := 'ATTACHMENT';
1516 l_content_id NUMBER ;
1517 -- modified by soagrawa on 18-jan-2002 bug# 2186980
1518 l_content_nm VARCHAR2(240);--(50) ;
1519 l_content_xml VARCHAR2(4000) := '';
1520 l_final_content VARCHAR2(4000) := '';
1521 -- l_content VARCHAR2(4000);
1522 --l_elect_fulfill_flg VARCHAR2(1);
1523 --l_kit_fulfill_flg VARCHAR2(1);
1524
1525 l_interface VARCHAR2(30);
1526
1527 BEGIN
1528 --
1529 -- Debug Message
1530 --
1531 IF (AMS_DEBUG_HIGH_ON) THEN
1532 AMS_Utility_PVT.debug_message(l_full_name||': start');
1533 END IF;
1534
1535
1536 -- Savepoint
1537 SAVEPOINT Get_Deliverable_Info_SP ;
1538 --
1539 -- Initialize message list IF p_init_msg_list is set to TRUE.
1540 --
1541 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1542 FND_MSG_PUB.initialize;
1543 END IF;
1544
1545 --
1546 -- Standard call to check for call compatibility.
1547 --
1548 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1549 p_api_version,
1550 l_api_name,
1551 G_PKG_NAME)
1552 THEN
1553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1554 END IF;
1555
1556 --
1557 -- Initialize API return status to success
1558 --
1559 x_return_status := FND_API.G_RET_STS_SUCCESS;
1560
1561 --
1562 -- API body
1563 --
1564 OPEN c_csch_det;
1565 LOOP
1566 FETCH c_csch_det INTO l_content_id, l_content_nm,l_media_type_code,l_media_id ;
1567 EXIT WHEN c_csch_det%NOTFOUND ;
1568
1569 IF l_media_id = 10 THEN
1570 l_media_type := 'FAX' ;
1571 ELSE
1572 l_media_type := 'EMAIL' ;
1573 END IF ;
1574 JTF_FM_REQUEST_GRP.Get_Content_XML
1575 ( p_api_version => l_api_version,
1576 x_return_status => l_return_status,
1577 x_msg_count => x_msg_count,
1578 x_msg_data => x_msg_data,
1579 p_content_id => l_content_id,
1580 p_content_nm => l_content_nm,
1581 p_media_type => l_media_type,
1582 p_email => p_email_address,
1583 p_user_note => l_user_notes,
1584 p_content_type => l_content_type,
1585 p_request_id => p_request_id,
1586 x_content_xml => l_content_xml);
1587
1588 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1589 CLOSE c_csch_det ;
1590 RAISE FND_API.G_EXC_ERROR;
1591 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1592 CLOSE c_csch_det ;
1593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1594 END IF;
1595 l_final_content := l_final_content || l_content_xml ;
1596
1597 END LOOP;
1598 CLOSE c_csch_det;
1599
1600 x_content_xml := l_final_content;
1601
1602 x_return_status := l_return_status ;
1603 */
1604 /* Following code is commented by ptendulk as eBlast will
1605 not use collaterals to send attachments
1606 IF p_arc_act = 'CSCH' THEN
1607 IF (AMS_DEBUG_HIGH_ON) THEN
1608 AMS_UTILITY_PVT.debug_message('Arc Act : '||p_arc_act);
1609 END IF;
1610 OPEN c_csch_det;
1611 -- Following code modified by soagrawa on 23Mar2001
1612 -- Added loop to process each deliverable
1613 LOOP
1614 FETCH c_csch_det INTO l_deli_id,l_kit_flag,l_deli_name,l_elect_fulfill_flg,
1615 l_media_type_code,l_media_id;
1616 EXIT WHEN c_csch_det%NOTFOUND;
1617
1618 IF (AMS_DEBUG_HIGH_ON) THEN
1619 AMS_UTILITY_PVT.debug_message('in c_csch_det loop');
1620 END IF;
1621
1622 IF l_elect_fulfill_flg = 'N' THEN
1623 l_interface := 'OC' ;
1624 ELSE
1625 OPEN c_kit_det ;
1626 LOOP
1627 FETCH c_kit_det INTO l_kit_fulfill_flg ;
1628 EXIT WHEN c_kit_det%NOTFOUND ;
1629 IF (AMS_DEBUG_HIGH_ON) THEN
1630 AMS_UTILITY_PVT.debug_message('l_kit_fulfill_flg: '||l_kit_fulfill_flg);
1631 END IF;
1632 IF l_kit_fulfill_flg = 'N' THEN
1633 l_elect_fulfill_flg := 'N' ;
1634 END IF ;
1635 END LOOP ;
1636 CLOSE c_kit_det ;
1637
1638 IF l_elect_fulfill_flg = 'N' THEN
1639 l_interface := 'OC';
1640 ELSE
1641 l_interface := 'FFM';
1642 END IF;
1643 END IF ;
1644
1645 IF l_interface = 'OC' THEN
1646 IF (AMS_DEBUG_HIGH_ON) THEN
1647 AMS_UTILITY_PVT.debug_message('oc request');
1648 END IF;
1649
1650 -- Call the oc api
1651 FulFill_OC(p_api_version => p_api_version,
1652 p_init_msg_list => p_init_msg_list,
1653
1654 x_return_status => l_return_status,
1655 x_msg_count => x_msg_count,
1656 x_msg_data => x_msg_data,
1657
1658 p_deliv_id => l_deli_id ,
1659 p_list_header_id => p_list_header_id);
1660 ELSIF l_interface = 'FFM' THEN
1661 IF (AMS_DEBUG_HIGH_ON) THEN
1662 AMS_UTILITY_PVT.debug_message('FFM request');
1663 END IF;
1664 IF (l_media_type_code <> 'DIRECT_MARKETING') OR
1665 (l_media_id <> 10 AND l_media_id <> 20 )
1666 THEN
1667 IF (AMS_DEBUG_HIGH_ON) THEN
1668 AMS_Utility_PVT.debug_message('Error Msg : Can FulFill only Email or Fax Requests');
1669 END IF;
1670 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1671 THEN -- MMSG
1672 FND_MESSAGE.Set_Name('AMS', 'AMS_FFM_INVALID_MEDIA');
1673 FND_MSG_PUB.Add;
1674 END IF;
1675 RAISE FND_API.G_EXC_ERROR;
1676 x_return_status := FND_API.G_RET_STS_ERROR ;
1677 RETURN;
1678 ELSE
1679 IF l_media_id = 10 THEN
1680 l_media_type := 'FAX' ;
1681 ELSE
1682 l_media_type := 'EMAIL' ;
1683 END IF ;
1684 END IF;
1685
1686 -- Create Deliverable XML
1687 l_content_type := 'COLLATERAL'; -- For the Deliverable
1688 l_content_id := l_deli_id ;
1689 l_content_nm := l_deli_name ;
1690
1691 IF (AMS_DEBUG_HIGH_ON) THEN
1692 AMS_UTILITY_PVT.debug_message('Media Type :'||l_media_type);
1693 END IF;
1694
1695 IF (AMS_DEBUG_HIGH_ON) THEN
1696 AMS_UTILITY_PVT.debug_message('Content Id :'||l_content_id);
1697 END IF;
1698 -- No need to pass Fax no / Email as well ,as these details will
1699 -- pass that in Submit Batch Process API
1700 JTF_FM_REQUEST_GRP.Get_Content_XML
1701 ( p_api_version => l_api_version,
1702 x_return_status => l_return_status,
1703 x_msg_count => x_msg_count,
1704 x_msg_data => x_msg_data,
1705 p_content_id => l_content_id,
1706 p_content_nm => l_content_nm,
1707 -- p_document_type => 'WORD',
1708 p_media_type => l_media_type,
1709 p_user_note => l_user_notes,
1710 p_content_type => l_content_type,
1711 p_request_id => p_request_id,
1712 x_content_xml => l_final_content);
1713
1714 IF (AMS_DEBUG_HIGH_ON) THEN
1715 AMS_UTILITY_PVT.debug_message('after get_content_xml : '||l_final_content);
1716 END IF;
1717
1718 -- Modified by soagrawa on 23Mar2001
1719 -- If any errors, raise exceptions.
1720 -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1721 -- x_return_status := l_return_status ;
1722 -- RETURN ;
1723 -- END IF;
1724
1725 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1726 RAISE FND_API.G_EXC_ERROR;
1727 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1729 END IF;
1730
1731 --
1732 -- Now , If the Deliverable is a kit, Fulfill Kit Items as well
1733 --
1734 IF (AMS_DEBUG_HIGH_ON) THEN
1735 AMS_UTILITY_PVT.debug_message('l_kit_flag : '||l_kit_flag);
1736 END IF;
1737
1738 IF l_kit_flag = 'Y' THEN
1739 Get_Kit_Info
1740 (p_api_version => l_api_version,
1741 p_init_msg_list => p_init_msg_list,
1742
1743 x_return_status => l_return_status,
1744 x_msg_count => x_msg_count ,
1745 x_msg_data => x_msg_data ,
1746
1747 p_deliv_id => l_deli_id ,
1748 p_request_id => p_request_id,
1749 p_media_type => l_media_type,
1750 p_user_notes => l_user_notes,
1751 x_content_xml => l_content_xml) ;
1752 -- Modified by soagrawa on 23Mar2001
1753 -- If any errors happen abort API.
1754 -- IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1755 -- x_return_status := l_return_status ;
1756 -- RETURN ;
1757 -- END IF;
1758
1759 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1760 RAISE FND_API.G_EXC_ERROR;
1761 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1763 END IF;
1764
1765
1766 l_content := l_content || l_content_xml;
1767
1768 END IF;
1769 END IF;
1770 END LOOP;-- fetching from c_csch_det
1771 CLOSE c_csch_det ;
1772 END IF; -- if p_arc_act = 'CSCH'
1773 --
1774 -- set OUT value
1775 --
1776 -- x_content_xml := l_final_content ;
1777 x_content_xml := l_final_content||l_content;
1778 IF (AMS_DEBUG_HIGH_ON) THEN
1779 AMS_UTILITY_PVT.debug_message('x_content_xml: '||x_content_xml);
1780 END IF;
1781 x_return_status := l_return_status ;
1782
1783 */
1784 /*
1785 --
1786 -- END of API body.
1787 --
1788
1789 --
1790 -- Standard call to get message count AND IF count is 1, get message info.
1791 --
1792 FND_MSG_PUB.Count_AND_Get
1793 ( p_count => x_msg_count,
1794 p_data => x_msg_data,
1795 p_encoded => FND_API.G_FALSE
1796 );
1797
1798 IF (AMS_DEBUG_HIGH_ON) THEN
1799 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1800 END IF;
1801
1802
1803 EXCEPTION
1804 WHEN FND_API.G_EXC_ERROR THEN
1805 IF c_csch_det%ISOPEN THEN
1806 CLOSE c_csch_det ;
1807 END IF ;
1808 ROLLBACK TO Get_Deliverable_Info_SP;
1809 x_return_status := FND_API.G_RET_STS_ERROR ;
1810
1811 FND_MSG_PUB.Count_AND_Get
1812 ( p_count => x_msg_count,
1813 p_data => x_msg_data,
1814 p_encoded => FND_API.G_FALSE
1815 );
1816 ams_utility_pvt.display_messages;
1817
1818 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1819 IF c_csch_det%ISOPEN THEN
1820 CLOSE c_csch_det ;
1821 END IF ;
1822 ROLLBACK TO Get_Deliverable_Info_SP;
1823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1824
1825 FND_MSG_PUB.Count_AND_Get
1826 ( p_count => x_msg_count,
1827 p_data => x_msg_data,
1828 p_encoded => FND_API.G_FALSE
1829 );
1830 ams_utility_pvt.display_messages;
1831
1832 WHEN OTHERS THEN
1833 IF c_csch_det%ISOPEN THEN
1834 CLOSE c_csch_det ;
1835 END IF ;
1836 ROLLBACK TO Get_Deliverable_Info_SP;
1837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1838
1839 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1840 THEN
1841 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1842 END IF;
1843
1844 FND_MSG_PUB.Count_AND_Get
1845 ( p_count => x_msg_count,
1846 p_data => x_msg_data,
1847 p_encoded => FND_API.G_FALSE
1848 );
1849
1850 END Get_Deliverable_Info;
1851
1852 */
1853
1854 -- Start of Comments
1855 --
1856 -- NAME
1857 -- AMS_FULFILL
1858 --
1859 -- PURPOSE
1860 -- This procedure is use to Fulfill the Marketing Collaterals.
1861 -- It also creates the Coverletter.
1862 -- It calls Get_Master_Info to get the XML for the Cover letter and
1863 -- then it calls Get_Deliverables_Info to get XML for Deliverables
1864 -- Procedure internally calls Fulfillment API to kick off
1865 -- fulfillment engine
1866 --
1867 -- NOTES
1868 --
1869 --
1870 -- HISTORY
1871 -- 11/12/1999 ptendulk created
1872 -- 03/08/2000 ptendulk Modified after changes from fulfillment team
1873 -- 05/09/2000 ptendulk Modified 1.Get the subject from the schedules table
1874 -- 2. get the user id from FND_GLOBALS
1875 -- 05/22/2000 ptendulk Modified AMS_FULFILL procedure, Get the partyid
1876 -- from party id column instead of customer id column
1877 -- in ams_list_entries table
1878 -- 11-Dec-2000 ptendulk Added the extra paramter in spec of get content XML
1879 -- Ref Bug # 1529231
1880 -- 13-Jun-2001 ptendulk Added schedule source code,objectid to the api to
1881 -- write to interaction.
1882 -- 29-apr-2002 soagrawa Replaced this with ams_fulfill that uses new FFM
1883 -- End of Comments
1884 /*
1885 PROCEDURE AMS_FULFILL
1886 (p_api_version IN NUMBER,
1887 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
1888 p_commit IN VARCHAR2 := FND_API.G_False,
1889
1890 x_return_status OUT NOCOPY VARCHAR2,
1891 x_msg_count OUT NOCOPY NUMBER ,
1892 x_msg_data OUT NOCOPY VARCHAR2,
1893
1894 p_list_header_id IN NUMBER,
1895 p_schedule_id IN NUMBER,
1896 p_user_id IN NUMBER := FND_GLOBAL.user_id )
1897 IS
1898
1899 l_api_name CONSTANT VARCHAR2(30) := 'AMS_FulFill';
1900 l_api_version CONSTANT NUMBER := 1.0;
1901 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1902
1903
1904 l_return_status VARCHAR2(1);
1905
1906
1907 -- following code uncommented by soagrawa on 06-sep-2001
1908 -- to change submit_batch_request to submit_mass_request
1909 CURSOR c_list_hdr_det IS
1910 SELECT list_used_by_id,
1911 arc_list_used_by
1912 FROM ams_list_headers_all
1913 WHERE list_header_id = p_list_header_id ;
1914 l_list_hdr_rec c_list_hdr_det%ROWTYPE ;
1915
1916 -- Following code is added by ptendulk on 8th Mar
1917 -- This one has to be changed after fulfillment
1918 -- resolves their bug for having party name mandatory
1919
1920 -- ===============================================================================
1921 -- Following code is modified by ptendulk on May22-00 to get the party id from
1922 -- party id column instead of customer id column in ams_list_entries
1923 -- ===============================================================================
1924 CURSOR c_list_ent_det IS
1925 SELECT party_id,
1926 fax,
1927 email_address,
1928 first_name
1929 FROM ams_list_entries
1930 WHERE list_header_id = p_list_header_id
1931 -- Following code is added by ptendulk on 01-Feb-2001
1932 -- Refer Bug # 1618348
1933 AND enabled_flag = 'Y' ;
1934 -- End of code added by ptendulk on 01-Feb-2001
1935
1936 -- ==============================================================================
1937 -- Following code is added by ptendulk on 05/08/2000
1938 -- Get the subject of the mail from campaign schedules
1939 -- ==============================================================================
1940
1941 CURSOR c_subject IS
1942 SELECT mail_subject,source_code
1943 FROM ams_campaign_schedules_b
1944 WHERE schedule_id = p_schedule_id ;
1945 l_subject VARCHAR2(1000) ;
1946 l_source_code VARCHAR2(30);
1947
1948 l_party_id JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE ;
1949 l_party_name JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1950 l_fax JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
1951 l_email JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
1952
1953
1954 l_request_id NUMBER ;
1955 l_content_xml VARCHAR2(4000); -- Temp. Variable to get XML Data
1956 l_final_content VARCHAR2(4000); -- Final XML String
1957
1958 l_user_id NUMBER ;
1959 l_server_id NUMBER ;
1960 --
1961 l_cnt NUMBER := 0 ;
1962 l_extended_header VARCHAR2(2000);
1963
1964 -- added by soagrawa on 06-sep-2001
1965 -- to use submit_mass_request instead of submit_batch_request
1966 G_QUERY_ID CONSTANT NUMBER := 300 ;
1967 l_mass_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
1968 l_mass_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
1969 l_mass_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
1970
1971
1972
1973 BEGIN
1974 --
1975 -- Standard Start of API savepoint
1976 --
1977 SAVEPOINT Create_FULFILL_PVT;
1978
1979 --
1980 -- Debug Message
1981 --
1982 IF (AMS_DEBUG_HIGH_ON) THEN
1983 AMS_Utility_PVT.debug_message(l_full_name||': start');
1984 END IF;
1985
1986 --
1987 -- Initialize message list IF p_init_msg_list is set to TRUE.
1988 --
1989 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1990 FND_MSG_PUB.initialize;
1991 END IF;
1992
1993 --
1994 -- Standard call to check for call compatibility.
1995 --
1996 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1997 p_api_version,
1998 l_api_name,
1999 G_PKG_NAME)
2000 THEN
2001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2002 END IF;
2003
2004 --
2005 -- Initialize API return status to success
2006 --
2007 x_return_status := FND_API.G_RET_STS_SUCCESS;
2008
2009 --
2010 -- API body
2011 --
2012
2013 -- uncommented by soagrawa on 06-sep-2001
2014 -- to change submit_batch_request to submit_mass_request
2015 OPEN c_list_hdr_det ;
2016 FETCH c_list_hdr_det INTO l_list_hdr_rec;
2017 CLOSE c_list_hdr_det ;
2018
2019 JTF_FM_REQUEST_GRP.STart_Request
2020 ( p_api_version => l_api_version,
2021 x_return_status => l_return_status,
2022 x_msg_count => x_msg_count,
2023 x_msg_data => x_msg_data,
2024 x_request_id => l_request_id
2025 );
2026
2027 IF (AMS_DEBUG_HIGH_ON) THEN
2028 AMS_Utility_PVT.debug_message('Request_ID: '||to_char(l_request_id));
2029 END IF;
2030
2031 --
2032 -- Create a Master Document First. It will be at the Campaign Level
2033 -- Take the Overriding Coverletter in Campaigns table as Content id
2034 --
2035 Get_Master_Info
2036 (p_api_version => l_api_version,
2037 p_init_msg_list => p_init_msg_list,
2038
2039 x_return_status => l_return_status,
2040 x_msg_count => x_msg_count ,
2041 x_msg_data => x_msg_data ,
2042
2043 p_list_header_id => p_list_header_id,
2044 p_schedule_id => p_schedule_id,
2045 p_request_id => l_request_id,
2046 x_content_xml => l_final_content,
2047 x_extended_header => l_extended_header);
2048
2049
2050 -- If any errors happen abort API.
2051 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2052 RAISE FND_API.G_EXC_ERROR;
2053 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2055 END IF;
2056
2057 -- display_string(l_content_xml1);
2058 IF (AMS_DEBUG_HIGH_ON) THEN
2059 AMS_Utility_PVT.debug_message('Return Status: '||l_return_status);
2060 END IF;
2061 --
2062 -- Create XML for Deliverable now. It will be at the Campaign Level
2063 -- Take the Overriding Coverletter in Campaigns table as Content id
2064 --
2065
2066 Get_Deliverable_Info
2067 (p_api_version => l_api_version,
2068 p_init_msg_list => p_init_msg_list,
2069
2070 x_return_status => l_return_status,
2071 x_msg_count => x_msg_count ,
2072 x_msg_data => x_msg_data ,
2073
2074 p_act_id => p_schedule_id,
2075 p_arc_act => 'CSCH',
2076 p_list_header_id => p_list_header_id,
2077 p_request_id => l_request_id,
2078
2079 x_content_xml => l_content_xml);
2080 -- If any errors happen abort API.
2081
2082 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2083 RAISE FND_API.G_EXC_ERROR;
2084 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2085 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2086 END IF;
2087
2088 l_final_content := l_final_content||l_content_xml ;
2089
2090 -- display_string(l_content_xml1);
2091 IF (AMS_DEBUG_HIGH_ON) THEN
2092 AMS_Utility_PVT.debug_message('Request ID: '||l_request_id);
2093 END IF;
2094
2095 IF (AMS_DEBUG_HIGH_ON) THEN
2096 AMS_Utility_PVT.debug_message('Return Status: '||l_return_status);
2097 END IF;
2098
2099 --
2100 -- call the Batch Process to Submit the Process
2101 --
2102 OPEN c_list_ent_det ;
2103 LOOP
2104 FETCH c_list_ent_det INTO l_party_id(l_cnt +1),l_fax(l_cnt +1),l_email(l_cnt +1),l_party_name(l_cnt + 1) ;
2105 EXIT WHEN c_list_ent_det%NOTFOUND ;
2106 l_cnt := l_cnt + 1 ;
2107 END LOOP ;
2108 CLOSE c_list_ent_det ;
2109
2110 -- ==============================================================================
2111 -- Following code is added by ptendulk on 05/08/2000
2112 -- Get the subject of the mail from campaign schedules
2113 -- ==============================================================================
2114
2115 OPEN c_subject;
2116 FETCH c_subject INTO l_subject,l_source_code ;
2117 CLOSE c_subject ;
2118
2119 l_user_id := p_user_id ;
2120 IF (AMS_DEBUG_HIGH_ON) THEN
2121 AMS_Utility_PVT.Debug_Message('User '||l_user_id||' Subject: '||l_subject );
2122 END IF;
2123
2124 IF (AMS_DEBUG_HIGH_ON) THEN
2125 AMS_Utility_PVT.debug_message('Return Status: '||l_return_status);
2126 END IF;
2127
2128
2129 -- commented out by soagrawa on 06-sep-2001
2130 -- using submit_mass_request instead of submit_batch_request
2131
2132 -- added by soagrawa on 30-May-2001
2133 -- changes made in order to write to interaction history
2134 -- added value for parameter p_per_user_history as true
2135 */
2136 /*JTF_FM_REQUEST_GRP.Submit_Batch_Request
2137 (p_api_version => l_api_version,
2138 p_commit => p_commit,
2139 x_return_status => l_return_status,
2140 x_msg_count => x_msg_count,
2141 x_msg_data => x_msg_data,
2142 p_subject => l_subject,
2143 p_user_id => l_user_id,
2144 p_source_code => l_source_code,
2145 p_object_type => 'CSCH',
2146 p_object_id => p_schedule_id,
2147 p_party_id => l_party_id,
2148 p_party_name => l_party_name,
2149 p_email => l_email,
2150 p_fax => l_fax,
2151 p_printer => l_fax,
2152 p_file_path => l_fax,
2153 -- p_server_id => l_server_id,
2154 p_content_xml => l_final_content,
2155 p_extended_header => l_extended_header,
2156 p_list_type => 'ADDRESS',
2157 p_request_id => l_request_id,
2158 p_per_user_history => FND_API.G_True
2159 );
2160 */
2161 /*
2162 l_mass_bind_var(1) := 'LIST_ID' ;
2163 l_mass_bind_var_type(1) := 'NUMBER' ;
2164 l_mass_bind_val(1) := p_list_header_id ;
2165
2166 JTF_FM_REQUEST_GRP.Submit_Mass_Request
2167 (p_api_version => l_api_version,
2168 p_commit => p_commit,
2169 x_return_status => l_return_status,
2170 x_msg_count => x_msg_count,
2171 x_msg_data => x_msg_data,
2172 p_subject => l_subject,
2173 p_user_id => l_user_id,
2174 p_source_code => l_source_code,
2175 p_object_type => 'CSCH',
2176 p_object_id => l_list_hdr_rec.list_used_by_id,
2177 p_list_type => 'BATCH_QUERY',
2178 p_server_id => l_server_id,
2179 p_extended_header => l_extended_header,
2180 p_content_xml => l_final_content,
2181 p_request_id => l_request_id,
2182 p_per_user_history => FND_API.G_True,
2183 p_mass_query_id => G_QUERY_ID,
2184 p_mass_bind_var => l_mass_bind_var,
2185 p_mass_bind_val => l_mass_bind_val,
2186 p_mass_bind_var_type=> l_mass_bind_var_type);
2187
2188
2189
2190 IF (AMS_DEBUG_HIGH_ON) THEN
2191 AMS_Utility_PVT.debug_message('Request ID: '||l_request_id);
2192 END IF;
2193
2194 IF (AMS_DEBUG_HIGH_ON) THEN
2195 AMS_Utility_PVT.debug_message('Return Status: '||l_return_status);
2196 END IF;
2197
2198 -- display_message(l_request_id);
2199
2200 --
2201 -- set OUT value
2202 --
2203 x_return_status := l_return_status;
2204
2205 --
2206 -- END of API body.
2207 --
2208
2209 --
2210 -- Standard check of p_commit.
2211 --
2212 IF FND_API.To_Boolean ( p_commit )
2213 THEN
2214 COMMIT WORK;
2215 END IF;
2216
2217 --
2218 -- Standard call to get message count AND IF count is 1, get message info.
2219 --
2220 FND_MSG_PUB.Count_AND_Get
2221 ( p_count => x_msg_count,
2222 p_data => x_msg_data,
2223 p_encoded => FND_API.G_FALSE
2224 );
2225
2226 IF (AMS_DEBUG_HIGH_ON) THEN
2227 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2228 END IF;
2229
2230
2231 EXCEPTION
2232 WHEN FND_API.G_EXC_ERROR THEN
2233
2234 ROLLBACK TO Create_FULFILL_PVT;
2235 x_return_status := FND_API.G_RET_STS_ERROR ;
2236
2237 FND_MSG_PUB.Count_AND_Get
2238 ( p_count => x_msg_count,
2239 p_data => x_msg_data,
2240 p_encoded => FND_API.G_FALSE
2241 );
2242 ams_utility_pvt.display_messages;
2243 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2244
2245 ROLLBACK TO Create_FULFILL_PVT;
2246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2247
2248 FND_MSG_PUB.Count_AND_Get
2249 ( p_count => x_msg_count,
2250 p_data => x_msg_data,
2251 p_encoded => FND_API.G_FALSE
2252 );
2253 ams_utility_pvt.display_messages;
2254
2255 WHEN OTHERS THEN
2256
2257 ROLLBACK TO Create_FULFILL_PVT;
2258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2259
2260 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2261 THEN
2262 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2263 END IF;
2264
2265 FND_MSG_PUB.Count_AND_Get
2266 ( p_count => x_msg_count,
2267 p_data => x_msg_data,
2268 p_encoded => FND_API.G_FALSE
2269 );
2270 ams_utility_pvt.display_messages;
2271 END AMS_FULFILL ;
2272
2273 */
2274
2275
2276 -- Start of Comments
2277 --
2278 -- NAME
2279 -- Send_Test_Mail
2280 --
2281 -- PURPOSE
2282 -- This procedure is used to Send the test mail to the test user
2283 --
2284 -- NOTES
2285 --
2286 --
2287 -- HISTORY
2288 -- 22-Jun-2001 ptendulk created
2289 -- 06-Sep-2001 soagrawa Modified code to now send the extended header as well
2290 -- i.e. the sender and reply to addresses
2291 -- 29-apr-2002 soagrawa Removed as now using new FFM
2292 --
2293 -- End of Comments
2294 /*
2295 PROCEDURE Send_Test_Email
2296 (p_api_version IN NUMBER,
2297 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
2298 p_commit IN VARCHAR2 := FND_API.G_False,
2299
2300 x_return_status OUT NOCOPY VARCHAR2,
2301 x_msg_count OUT NOCOPY NUMBER ,
2302 x_msg_data OUT NOCOPY VARCHAR2,
2303
2304 p_email_address IN VARCHAR2,
2305 p_schedule_id IN NUMBER)
2306 IS
2307
2308 l_api_name CONSTANT VARCHAR2(30) := 'Send_Test_Email';
2309 l_api_version CONSTANT NUMBER := 1.0;
2310 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2311
2312 l_return_status VARCHAR2(1);
2313 l_content_xml VARCHAR2(4000); -- Temp. Variable to get XML Data
2314 l_attach_xml VARCHAR2(4000);
2315 l_extended_header VARCHAR2(2000);
2316 l_request_id NUMBER ;
2317
2318 -- added by soagrawa on 06-sep-2001
2319 -- for extended header purposes
2320 l_sender VARCHAR2(120);
2321 l_reply_to VARCHAR2(240);
2322 l_from_fax VARCHAR2(25);
2323 l_media_type_code VARCHAR2(30) ;
2324
2325 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
2326 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
2327 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
2328
2329 -- Following cursor will select dummy entry from ams_list_entries to
2330 -- send the test mail.
2331 CURSOR c_list_entry IS
2332 SELECT party_id, list_header_id,first_name
2333 FROM ams_list_entries
2334 WHERE party_id IS NOT NULL
2335 AND rownum < 2 ;
2336
2337 l_list_header_id NUMBER ;
2338 l_party_id NUMBER ;
2339 l_party_name VARCHAR2(30);
2340
2341 CURSOR c_csch_det IS
2342 SELECT csch.campaign_id, csch.cover_letter_id cover_letter,item.item_name name,
2343 csch.mail_sender_name, csch.reply_to_mail ,
2344 csch.mail_subject
2345 FROM ams_campaign_schedules_b csch,jtf_amv_items_vl item
2346 WHERE schedule_id = p_schedule_id
2347 AND csch.cover_letter_id = item.item_id ;
2348 l_schedule_rec c_csch_det%ROWTYPE;
2349
2350
2351 BEGIN
2352
2353 SAVEPOINT Send_Test_Email;
2354
2355 IF (AMS_DEBUG_HIGH_ON) THEN
2356 AMS_Utility_PVT.debug_message(l_full_name||': start');
2357 END IF;
2358
2359 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2360 FND_MSG_PUB.initialize;
2361 END IF;
2362
2363 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2364 p_api_version,
2365 l_api_name,
2366 G_PKG_NAME)
2367 THEN
2368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2369 END IF;
2370
2371 x_return_status := FND_API.G_RET_STS_SUCCESS;
2372
2373 OPEN c_list_entry ;
2374 FETCH c_list_entry INTO l_party_id, l_list_header_id, l_party_name;
2375 CLOSE c_list_entry;
2376
2377 OPEN c_csch_det;
2378 FETCH c_csch_det INTO l_schedule_rec;
2379 CLOSE c_csch_det ;
2380
2381 -- Start the Api
2382 -- Get the xml for cover letter
2383 JTF_FM_REQUEST_GRP.STart_Request
2384 ( p_api_version => l_api_version,
2385 x_return_status => l_return_status,
2386 x_msg_count => x_msg_count,
2387 x_msg_data => x_msg_data,
2388 x_request_id => l_request_id
2389 );
2390
2391 --
2392 -- following segment added by soagrawa on 06-sep-2001
2393 -- to pass correct reply to and sender email addresses in the email
2394 --
2395
2396 l_sender := l_schedule_rec.mail_sender_name ;
2397 l_reply_to := l_schedule_rec.reply_to_mail ;
2398
2399 IF l_sender IS NOT NULL THEN
2400 l_extended_header := '<extended_header media_type="EMAIL">';
2401 l_extended_header := l_extended_header||'<header_name>';
2402 l_extended_header := l_extended_header||'From';
2403 l_extended_header := l_extended_header||'</header_name>';
2404 l_extended_header := l_extended_header||'<header_value>'||l_sender||'</header_value>';
2405 l_extended_header := l_extended_header||'</extended_header>';
2406 ELSE
2407 l_extended_header := NULL ;
2408 END IF ;
2409
2410 IF l_reply_to IS NOT NULL THEN
2411 l_extended_header := l_extended_header||'<extended_header media_type="EMAIL">';
2412 l_extended_header := l_extended_header||'<header_name>';
2413 l_extended_header := l_extended_header||'email_reply_to_address';
2414 l_extended_header := l_extended_header||'</header_name>';
2415 l_extended_header := l_extended_header||'<header_value>'||l_reply_to||'</header_value>';
2416 l_extended_header := l_extended_header||'</extended_header>';
2417 END IF ;
2418
2419 --
2420 -- end soagrawa 06-sep-2001
2421 --
2422
2423 l_bind_var(1) := 'id' ;
2424 l_bind_var_type(1) := 'NUMBER' ;
2425 l_bind_val(1) := l_list_header_id ;
2426 IF (AMS_DEBUG_HIGH_ON) THEN
2427 AMS_Utility_PVT.Debug_Message('Party Id : '||l_party_id || 'List : '||l_list_header_id);
2428 END IF;
2429
2430 JTF_FM_REQUEST_GRP.Get_Content_XML
2431 (
2432 p_api_version => l_api_version,
2433 p_commit => p_commit,
2434 x_return_status => x_return_status,
2435 x_msg_count => x_msg_count,
2436 x_msg_data => x_msg_data,
2437 p_content_id => l_schedule_rec.cover_letter,
2438 p_content_nm => l_schedule_rec.name,
2439 p_media_type => 'EMAIL',
2440 p_email => p_email_address,
2441 p_content_type => 'QUERY',
2442 p_bind_var => l_bind_var,
2443 p_bind_val => l_bind_val,
2444 p_bind_var_type => l_bind_var_type,
2445 p_request_id => l_request_id,
2446 x_content_xml => l_content_xml ) ;
2447
2448 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2449 RAISE FND_API.G_EXC_ERROR;
2450 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2452 END IF;
2453
2454 Get_Deliverable_Info(
2455 p_api_version => l_api_version,
2456 x_return_status => x_return_status,
2457 x_msg_count => x_msg_count,
2458 x_msg_data => x_msg_data,
2459 p_act_id => p_schedule_id,
2460 p_arc_act => 'CSCH',
2461 p_request_id => l_request_id,
2462 p_email_address => p_email_address,
2463
2464 x_content_xml => l_attach_xml);
2465
2466 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2467 RAISE FND_API.G_EXC_ERROR;
2468 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2470 END IF;
2471
2472 IF (AMS_DEBUG_HIGH_ON) THEN
2473 AMS_Utility_PVT.Debug_Message('Success Flag : '||x_return_status );
2474 END IF;
2475 l_content_xml := l_content_xml || nvl(l_attach_xml,'') ;
2476 JTF_FM_REQUEST_GRP.Submit_Request
2477 (p_api_version => l_api_version,
2478 p_commit => p_commit,
2479 x_return_status => l_return_status,
2480 x_msg_count => x_msg_count,
2481 x_msg_data => x_msg_data,
2482 p_subject => l_schedule_rec.mail_subject,
2483 p_party_id => l_party_id,
2484 p_party_name => l_party_name,
2485 p_user_id => FND_GLOBAL.user_id,
2486 p_extended_header => l_extended_header,
2487 p_content_xml => l_content_xml,
2488 p_request_id => l_request_id
2489 ) ;
2490 IF (AMS_DEBUG_HIGH_ON) THEN
2491 AMS_Utility_PVT.Debug_Message('Success Flag : '||x_return_status );
2492 END IF;
2493
2494 --
2495 -- Standard check of p_commit.
2496 --
2497 IF FND_API.To_Boolean ( p_commit )
2498 THEN
2499 COMMIT WORK;
2500 END IF;
2501
2502 --
2503 -- Standard call to get message count AND IF count is 1, get message info.
2504 --
2505 FND_MSG_PUB.Count_AND_Get
2506 ( p_count => x_msg_count,
2507 p_data => x_msg_data,
2508 p_encoded => FND_API.G_FALSE
2509 );
2510
2511 IF (AMS_DEBUG_HIGH_ON) THEN
2512 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2513 END IF;
2514
2515
2516
2517 EXCEPTION
2518 WHEN FND_API.G_EXC_ERROR THEN
2519
2520 ROLLBACK TO Send_Test_Email;
2521 x_return_status := FND_API.G_RET_STS_ERROR ;
2522
2523 FND_MSG_PUB.Count_AND_Get
2524 ( p_count => x_msg_count,
2525 p_data => x_msg_data,
2526 p_encoded => FND_API.G_FALSE
2527 );
2528
2529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2530
2531 ROLLBACK TO Send_Test_Email;
2532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2533
2534 FND_MSG_PUB.Count_AND_Get
2535 ( p_count => x_msg_count,
2536 p_data => x_msg_data,
2537 p_encoded => FND_API.G_FALSE
2538 );
2539
2540 WHEN OTHERS THEN
2541
2542 ROLLBACK TO Send_Test_Email;
2543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2544
2545 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2546 THEN
2547 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2548 END IF;
2549
2550 FND_MSG_PUB.Count_AND_Get
2551 ( p_count => x_msg_count,
2552 p_data => x_msg_data,
2553 p_encoded => FND_API.G_FALSE
2554 );
2555
2556 END Send_Test_Email ;
2557 */
2558 -- Start of Comments
2559 --
2560 -- NAME
2561 -- Send_Test_Mail
2562 --
2563 -- PURPOSE
2564 -- This procedure is link the Cover letter with the query
2565 --
2566 -- NOTES
2567 -- This api is currently inserting into jtf_amv_query
2568 -- once the fulfillment team delivers the api , the
2569 -- insert statement will be replaced with the api call
2570 --
2571 -- HISTORY
2572 -- 26-Jun-2001 ptendulk created
2573 -- 29-apr-2002 soagrawa Removed as now using new FFM
2574 --
2575 -- End of Comments
2576 /*
2577 PROCEDURE Attach_Query
2578 (p_api_version IN NUMBER,
2579 p_init_msg_list IN VARCHAR2 := FND_API.G_False,
2580 p_commit IN VARCHAR2 := FND_API.G_False,
2581
2582 x_return_status OUT NOCOPY VARCHAR2,
2583 x_msg_count OUT NOCOPY NUMBER ,
2584 x_msg_data OUT NOCOPY VARCHAR2,
2585
2586 p_query_id IN NUMBER,
2587 p_item_id IN NUMBER
2588 )
2589 IS
2590
2591 l_api_name CONSTANT VARCHAR2(30) := 'Attach_Query';
2592 l_api_version CONSTANT NUMBER := 1.0;
2593 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2594
2595 l_return_status VARCHAR2(1);
2596
2597 CURSOR c_query_det IS
2598 SELECT 1
2599 FROM dual
2600 WHERE EXISTS (SELECT 1 FROM JTF_FM_QUERY_MES
2601 WHERE query_id = p_query_id
2602 AND mes_doc_id = p_item_id ) ;
2603 l_dummy NUMBER ;
2604
2605 BEGIN
2606
2607 SAVEPOINT Attach_Query;
2608
2609 IF (AMS_DEBUG_HIGH_ON) THEN
2610 AMS_Utility_PVT.debug_message(l_full_name||': start');
2611 END IF;
2612
2613 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2614 FND_MSG_PUB.initialize;
2615 END IF;
2616
2617 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2618 p_api_version,
2619 l_api_name,
2620 G_PKG_NAME)
2621 THEN
2622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2623 END IF;
2624
2625 x_return_status := FND_API.G_RET_STS_SUCCESS;
2626
2627 OPEN c_query_det;
2628 FETCH c_query_det INTO l_dummy ;
2629 CLOSE c_query_det;
2630
2631 IF l_dummy IS NOT NULL THEN
2632 RETURN ;
2633 END IF;
2634
2635 INSERT INTO jtf_fm_query_mes
2636 (mes_doc_id, query_id,last_update_date,last_updated_by,creation_date,created_by)
2637 VALUES(p_item_id, p_query_id,SYSDATE,FND_GLOBAL.user_id,SYSDATE,FND_GLOBAL.user_id) ;
2638
2639 --
2640 -- Standard check of p_commit.
2641 --
2642 IF FND_API.To_Boolean ( p_commit )
2643 THEN
2644 COMMIT WORK;
2645 END IF;
2646
2647 --
2648 -- Standard call to get message count AND IF count is 1, get message info.
2649 --
2650 FND_MSG_PUB.Count_AND_Get
2651 ( p_count => x_msg_count,
2652 p_data => x_msg_data,
2653 p_encoded => FND_API.G_FALSE
2654 );
2655
2656 IF (AMS_DEBUG_HIGH_ON) THEN
2657 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2658 END IF;
2659
2660 EXCEPTION
2661 WHEN FND_API.G_EXC_ERROR THEN
2662
2663 ROLLBACK TO Attach_Query;
2664 x_return_status := FND_API.G_RET_STS_ERROR ;
2665
2666 FND_MSG_PUB.Count_AND_Get
2667 ( p_count => x_msg_count,
2668 p_data => x_msg_data,
2669 p_encoded => FND_API.G_FALSE
2670 );
2671
2672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2673
2674 ROLLBACK TO Attach_Query;
2675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2676
2677 FND_MSG_PUB.Count_AND_Get
2678 ( p_count => x_msg_count,
2679 p_data => x_msg_data,
2680 p_encoded => FND_API.G_FALSE
2681 );
2682
2683 WHEN OTHERS THEN
2684
2685 ROLLBACK TO Attach_Query;
2686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2687
2688 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2689 THEN
2690 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2691 END IF;
2692
2693 FND_MSG_PUB.Count_AND_Get
2694 ( p_count => x_msg_count,
2695 p_data => x_msg_data,
2696 p_encoded => FND_API.G_FALSE
2697 );
2698
2699
2700 END Attach_Query ;
2701 */
2702
2703
2704 END AMS_FULFILL_PVT ;