[Home] [Help]
PACKAGE BODY: APPS.JTF_FM_INT_REQUEST_PKG
Source
1 PACKAGE BODY JTF_FM_INT_REQUEST_PKG AS
2 /* $Header: jtffmrqb.pls 120.42 2006/10/24 00:30:54 jakaur noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_FM_INT_REQUEST_PKG';
5 g_file_name CONSTANT VARCHAR2(12) := 'bb.pls';
6
7 nodes xmldom.DOMNodeList;
8 one_node xmldom.DOMNode;
9 node_map xmldom.DOMNamedNodeMap;
10 l_no_of_bind NUMBER;
11 l_request_id NUMBER;
12 var BLOB;
13 l_count NUMBER;
14 l_buffer VARCHAR2(32767);
15 l_email_body VARCHAR2(30);
16 l_subject VARCHAR2(2000);
17 l_user_history VARCHAR2(30);
18 l_counter NUMBER;
19 l_bind_object VARCHAR2(2000);
20 l_length NUMBER;
21 l_no_of_chunks NUMBER;
22 l_cursor NUMBER;
23 l_col_cnt NUMBER;
24 l_rec_tab DBMS_SQL.desc_tab;
25 l_parser xmlparser.parser;
26 l_doc xmldom.domdocument;
27 nl xmldom.DOMNodeList;
28 len1 NUMBER;
29 len2 NUMBER;
30 n xmldom.DOMNode;
31 n1 xmldom.DOMNode;
32 e xmldom.DOMElement;
33
34 /*****************************************************************************
35 Forward declaration of private objects starts
36 ******************************************************************************/
37 PROCEDURE clean_stalled_request ( p_request_id IN NUMBER
38 , x_return_status OUT NOCOPY VARCHAR2
39 );
40
41 PROCEDURE get_next_partition ( x_errbuf OUT NOCOPY VARCHAR2
42 , x_retcode OUT NOCOPY VARCHAR2
43 , p_request_id IN NUMBER
44 , x_partition_id OUT NOCOPY NUMBER);
45
46 PROCEDURE lock_partition ( x_errbuf OUT NOCOPY VARCHAR2
47 , x_retcode OUT NOCOPY VARCHAR2
48 , p_request_id IN NUMBER
49 , p_partition_id IN NUMBER
50 );
51
52 PROCEDURE unlock_partition ( x_errbuf OUT NOCOPY VARCHAR2
53 , x_retcode OUT NOCOPY VARCHAR2
54 , p_request_id IN NUMBER
55 );
56 /*****************************************************************************
57 Forward declaration ends
58 ******************************************************************************/
59
60 /*---------------------------------------------------------------------------------*
61 | Procedure Name : UPDATE_CONTACT_PREF |
62 | |
63 | Purpose : Updates jtf_fm_int_request_lines table for "DO NOT" contact |
64 | party IDs. |
65 *---------------------------------------------------------------------------------*/
66 PROCEDURE update_contact_pref ( p_request_id IN NUMBER )
67 IS
68 TYPE l_line_id_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
69 l_line_id l_line_id_table_type ;
70 CURSOR c_parties IS
71 SELECT c.request_line_id
72 FROM hz_contact_preferences b ,
73 jtf_fm_int_request_lines c
74 WHERE b.preference_code = 'DO_NOT'
75 AND b.contact_level_table = 'HZ_PARTIES'
76 AND b.contact_level_table_id = c.party_id
77 AND c.request_id = p_request_id
78 AND NVL(b.preference_start_date, SYSDATE -1) < SYSDATE
79 AND NVL(b.preference_end_date, SYSDATE+1) > SYSDATE ;
80 BEGIN
81 OPEN c_parties;
82 FETCH c_parties BULK COLLECT INTO l_line_id ;
83 CLOSE c_parties;
84
85 FORALL i IN l_line_id.FIRST .. l_line_id.LAST
86 UPDATE jtf_fm_int_request_lines
87 SET contact_preference_flag = 'N',
88 enabled_flag = 'N'
89 WHERE request_line_id = l_line_id(i);
90 END update_contact_pref;
91
92
93 /*---------------------------------------------------------------------------------*
94 | Procedure Name : VALIDATE_EMAIL |
95 | |
96 | Purpose : Updates jtf_fm_int_request_lines table for invalid email |
97 | addresses. |
98 *---------------------------------------------------------------------------------*/
99 PROCEDURE validate_email( p_request_id IN NUMBER )
100 IS
101 TYPE l_line_id_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
102 l_line_id l_line_id_table_type ;
103 CURSOR c_lines IS
104 SELECT c.request_line_id
105 FROM jtf_fm_int_request_lines c
106 WHERE c.request_id = p_request_id
107 AND (c.email_address IS NULL
108 OR INSTR(c.email_address,'@') < 1
109 OR INSTR(c.email_address, ':') > 0)
110 AND c.enabled_flag = 'Y';
111 BEGIN
112 OPEN c_lines;
113 FETCH c_lines BULK COLLECT INTO l_line_id ;
114 CLOSE c_lines;
115
116 FORALL i IN l_line_id.FIRST .. l_line_id.LAST
117 UPDATE jtf_fm_int_request_lines
118 SET enabled_flag = 'N'
119 WHERE request_line_id = l_line_id(i);
120 END validate_email;
121
122 /*---------------------------------------------------------------------------------*
123 | Procedure Name : RAISEBUSINESSEVENT |
124 | |
125 | Purpose : Allows end users to create business rules for blocking email |
126 | addresses. |
127 *---------------------------------------------------------------------------------*/
128 PROCEDURE raiseBusinessEvent( p_request_id IN NUMBER )
129 IS
130 l_parameter_list WF_PARAMETER_LIST_T;
131 l_new_item_key VARCHAR2(30);
132 l_start_time DATE;
133 BEGIN
134 l_start_time := SYSDATE;
135 l_new_item_key := p_request_id || '_'||TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
136 l_parameter_list := WF_PARAMETER_LIST_T();
137
138 wf_event.AddParameterToList( p_name => 'REQUEST_ID',
139 p_value => p_request_id,
140 p_parameterlist => l_parameter_list
141 );
142
143 wf_event.RAISE ( p_event_name => 'oracle.apps.jtf.fm.int.RequestPostProcessing',
144 p_event_key => l_new_item_key,
145 p_parameters => l_parameter_list,
146 p_send_date => l_start_time
147 );
148
149 END raiseBusinessEvent;
150
151 /*---------------------------------------------------------------------------------*
152 | Procedure Name : DISPLAY_ELEMENT |
153 | |
154 | Purpose : Displays the first XML Element for a given node |
155 | |
156 *---------------------------------------------------------------------------------*/
157 PROCEDURE display_element (node IN xmldom.DOMNode )
158 IS
159 one_element xmldom.DOMElement;
160 value_node xmldom.DOMNode;
161 BEGIN
162 one_element := xmldom.makeElement (node);
163 value_node := xmldom.getFirstChild (node);
164 END display_element ;
165
166 /*---------------------------------------------------------------------------------*
167 | Procedure Name : GET_ELEMENT |
168 | |
169 | Purpose : Displays the elements for a given node |
170 | |
171 *---------------------------------------------------------------------------------*/
172 PROCEDURE get_element ( node IN xmldom.DOMNode
173 , value_node_value OUT NOCOPY VARCHAR2)
174 IS
175 one_element xmldom.DOMElement;
176 value_node xmldom.DOMNode;
177 BEGIN
178 one_element := xmldom.makeElement (node);
179 value_node := xmldom.getFirstChild (node);
180 value_node_value := xmldom.getNodeValue (value_node);
181 END get_element ;
182
183 /*---------------------------------------------------------------------------------*
184 | Procedure Name : DISPLAY_ATTRIBUTE |
185 | |
186 | Purpose : Displays attributes for a given node |
187 | |
188 *---------------------------------------------------------------------------------*/
189 PROCEDURE display_attribute ( node_map IN xmldom.DOMNamedNodeMap,
190 attr_index IN PLS_INTEGER )
191 IS
192 one_node xmldom.DOMNode;
193 attrname VARCHAR2(100);
194 attrval VARCHAR2(100);
195 BEGIN
196 one_node := xmldom.item (node_map, attr_index);
197 attrname := xmldom.getNodeName (one_node);
198 attrval := xmldom.getNodeValue (one_node);
199 END display_attribute ;
200
201 /*---------------------------------------------------------------------------------*
202 | Procedure Name : GET_ATTRIBUTE |
203 | |
204 | Purpose : Gets the attribute for a given node map |
205 | |
206 *---------------------------------------------------------------------------------*/
207 PROCEDURE get_attribute ( node_map IN xmldom.DOMNamedNodeMap
208 , attr_index IN PLS_INTEGER
209 , node_item IN VARCHAR2
210 , node_value OUT NOCOPY VARCHAR2
211 ) IS
212 one_node xmldom.DOMNode;
213 attrname VARCHAR2 (100);
214 attrval VARCHAR2 (100);
215 BEGIN
216 one_node := xmldom.item (node_map, attr_index);
217 attrname := xmldom.getNodeName (one_node);
218 attrval := xmldom.getNodeValue (one_node);
219
220 IF attrname = node_item
221 THEN
222 node_value := attrval;
223 END IF;
224 END get_attribute ;
225
226 /*---------------------------------------------------------------------------------*
227 | Procedure Name : GET_ATTRIBUTEC |
228 | |
229 | Purpose : Gets the attribute value for a given node map. |
230 | |
231 *---------------------------------------------------------------------------------*/
232 PROCEDURE get_attributec ( node_map IN xmldom.DOMNamedNodeMap
233 , attr_index IN PLS_INTEGER
234 , node_item IN VARCHAR2
235 , node_value OUT NOCOPY VARCHAR2
236 ) IS
237 one_node xmldom.DOMNode;
238 attrname VARCHAR2 (100);
239 attrval VARCHAR2 (100);
240 BEGIN
241 one_node := xmldom.item (node_map, attr_index);
242 attrname := xmldom.getNodeName (one_node);
243 attrval := xmldom.getNodeValue (one_node);
244
245 IF attrname = node_item
246 THEN
247 node_value := attrval;
248 END IF;
249 END get_attributec ;
250
251 /*---------------------------------------------------------------------------------*
252 | Procedure Name : PROCESS_REQUEST |
253 | |
254 | Purpose : Processes the query and XML for a given request ID. This |
255 | is called by MassRequstMonitor. Main functionalities are: |
256 | - Inserts record into jtf_fm_int_request_header table. |
257 | - Inserts record into jtf_fm_int_request_batches table. |
258 | - Creates partition and inserts records into |
259 | jtff_fm_int_request_lines table. |
260 | |
261 *---------------------------------------------------------------------------------*/
262 PROCEDURE process_request( request_id NUMBER
263 , x_return_status OUT NOCOPY VARCHAR2
264 , x_msg_count OUT NOCOPY NUMBER
265 , x_msg_data OUT NOCOPY VARCHAR2
266 ) IS
267 l_api_name CONSTANT VARCHAR2(30) := 'Process_request';
268 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
269 l_bind CLOB;
270 l_amount NUMBER;
271 l_col_insert VARCHAR2(3000);
272 l_party_first_name_col VARCHAR2(200);
273 l_party_last_name_col VARCHAR2(200);
274 l_email_address_col VARCHAR2(200);
275 l_party_id_col VARCHAR2(200);
276 l_create_part VARCHAR2(1000);
277 l_query_id NUMBER;
278 l_temp_query_id NUMBER;
279 l_batch_size NUMBER;
280 l_clean_request_status VARCHAR2(1);
281 l_header_count NUMBER;
282 l_email_from_address VARCHAR2(100) DEFAULT NULL;
283 l_email_reply_to_address VARCHAR2(100) DEFAULT NULL;
284 l_sender_display_name VARCHAR2(100) DEFAULT NULL;
285 l_header_name JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
286 l_header_value JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
287 l_bind_name JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
288 l_bind_value JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
289 l_select_cols VARCHAR2(3000);
290 l_insert_statement VARCHAR2(32767);
291 l_fnd_query VARCHAR2(32767);
292 l_Add_columns VARCHAR2(32767);
293 l_bind_variable VARCHAR2(2000);
294 l_partition_id NUMBER;
295 l_errbuf VARCHAR2(32767);
296 l_retcode VARCHAR2(1);
297 e_partition_not_found EXCEPTION;
298 e_too_many_bind_vars EXCEPTION;
299 BEGIN
300 -- Standard begin of API savepoint
301 SAVEPOINT process_request;
302
303 --Initialize message list if p_init_msg_list is TRUE.
304 FND_MSG_PUB.initialize;
305
306 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
307 THEN
308 FND_MESSAGE.Set_Name('JTF', 'JTF_FM_API_DEBUG_MESSAGE');
309 FND_MESSAGE.Set_Token('ARG1', l_full_name||': Start');
310 FND_MSG_PUB.ADD;
311 END IF;
312
313 -- Initialize API return status to success
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315
316 SELECT request, hist_req_id
317 INTO l_bind, l_request_id
318 FROM jtf_fm_request_history_all
319 WHERE hist_req_id = request_id;
320
321 l_amount := DBMS_LOB.GETLENGTH(l_bind);
322
323 DBMS_LOB.READ (l_bind, l_amount, 1, l_buffer);
324
325 l_parser := xmlparser.newparser();
326 xmlparser.parseBuffer(l_parser, l_buffer);
327 l_doc := xmlparser.getdocument(l_parser);
328
329 -- get bind var elements
330 nodes := xmldom.getElementsByTagName(l_doc,'bind_var');
331
332 -- loop through elements
333 FOR node_index IN 0 .. xmldom.getLength (nodes) - 1
334 LOOP
335 one_node := xmldom.item (nodes, node_index);
336
337 IF (xmldom.getNodeName(one_node) = 'bind_var' )
338 THEN
339 get_element (one_node, l_bind_variable );
340 node_map := xmldom.getAttributes (one_node);
341 l_bind_value.extend();
342 l_bind_value(node_index + 1) := l_bind_variable;
343
344 FOR attr_index IN 0 .. xmldom.getLength (node_map) - 1
345 LOOP
346 get_attributec (node_map, attr_index,'bind_object',l_bind_object);
347 IF (l_bind_object IS NOT NULL)
348 THEN
349 l_bind_name.extend();
350 l_bind_name(node_index + 1) := l_bind_object ;
351 END IF;
352 END LOOP;
353 END IF;
354 END LOOP;
355
356 nodes := xmldom.getElementsByTagName(l_doc,'file');
357
358 -- loop through elements
359 FOR node_index IN 0 .. xmldom.getLength (nodes) - 1
360 LOOP
361 one_node := xmldom.item (nodes, node_index);
362 -- display_element (one_node);
363 node_map := xmldom.getAttributes (one_node);
364
365 FOR attr_index IN 0 .. xmldom.getLength (node_map) - 1
366 LOOP
367 --display_attribute (node_map, attr_index);
368 get_attribute (node_map, attr_index,'query_id',l_temp_query_id);
369
370 IF l_temp_query_id IS NOT NULL
371 THEN
372 l_query_id := l_temp_query_id;
373 EXIT;
374 END IF;
375 END LOOP;
376 END LOOP;
377
378 nodes := xmldom.getElementsByTagName(l_doc,'ffm_request');
379
380 -- loop through elements
381 FOR node_index IN 0 .. xmldom.getLength (nodes) - 1
382 LOOP
383 one_node := xmldom.item (nodes, node_index);
384
385 -- display_element (one_node);
386 node_map := xmldom.getAttributes (one_node);
387
388 FOR attr_index IN 0 .. xmldom.getLength (node_map) - 1
389 LOOP
390 --display_attribute (node_map, attr_index);
391 get_attribute (node_map, attr_index,'email_body',l_email_body);
392
393 IF l_email_body IS NOT NULL
394 THEN
395 EXIT;
396 END IF;
397 END LOOP;
398 END LOOP;
399
400 FOR node_index IN 0 .. xmldom.getLength (nodes) - 1
401 LOOP
402 one_node := xmldom.item (nodes, node_index);
403
404 -- display_element (one_node);
405 node_map := xmldom.getAttributes (one_node);
406
407 FOR attr_index IN 0 .. xmldom.getLength (node_map) - 1
408 LOOP
409 --display_attribute (node_map, attr_index);
410 get_attribute (node_map, attr_index,'subject',l_subject);
411
412 IF l_subject IS NOT NULL
413 THEN
414 EXIT;
415 END IF;
416 END LOOP;
417 END LOOP;
418
419 FOR node_index IN 0 .. xmldom.getLength (nodes) - 1
420 LOOP
421 one_node := xmldom.item (nodes, node_index);
422
423 -- display_element (one_node);
424 node_map := xmldom.getAttributes (one_node);
425
426 FOR attr_index IN 0 .. xmldom.getLength (node_map) - 1
427 LOOP
428 -- display_attribute (node_map, attr_index);
429 get_attribute (node_map, attr_index,'user_history',l_user_history);
430
431 IF l_user_history IS NOT NULL
432 THEN
433 EXIT;
434 END IF;
435 END LOOP;
436 END LOOP;
437
438 nodes := xmldom.getelementsbytagname(l_doc,'header_name');
439
440 -- loop through elements
441 l_counter := 1;
442
443 FOR node_index IN 0 .. xmldom.getlength (nodes) - 1
444 LOOP
445 one_node := xmldom.item (nodes, node_index);
446
447 --display_element (one_node);
448 l_header_name.extend();
449
450 get_element (one_node, l_header_name(l_counter));
451
452 IF l_header_name(l_counter) IS NOT NULL
453 THEN
454 NULL;
455 END IF;
456
457 node_map := xmldom.getattributes (one_node);
458 l_counter := l_counter +1 ;
459 END LOOP;
460
461 nodes := xmldom.getelementsbytagname(l_doc,'header_value');
462 l_counter := 1;
463
464 FOR node_index IN 0 .. xmldom.getlength (nodes) - 1
465 LOOP
466 one_node := xmldom.item (nodes, node_index);
467 l_header_value.extend();
468 get_element (one_node, l_header_value(l_counter));
469
470 IF l_header_name(l_counter) IS NOT NULL
471 THEN
472 NULL;
473 END IF;
474
475 node_map := xmldom.getattributes (one_node);
476 l_counter := l_counter +1 ;
477 END LOOP;
478
479 SELECT file_data
480 INTO var
481 FROM fnd_lobs
482 WHERE file_id = l_query_id;
483
484 l_buffer := utl_raw.cast_to_varchar2(var);
485
486 l_fnd_query := LTRIM(RTRIM(l_buffer)) ;
487
488 l_cursor := DBMS_SQL.OPEN_CURSOR;
489 DBMS_SQL.PARSE(l_cursor, l_buffer, DBMS_SQL.NATIVE);
490
491 DBMS_SQL.DESCRIBE_COLUMNS(l_cursor, l_col_cnt, l_rec_tab);
492
493 FOR i IN l_col_cnt +1 .. 100
494 LOOP
495 l_rec_tab(i).col_name := '';
496 END LOOP;
497
498 l_count := l_header_value.COUNT();
499
500 IF l_count > 0
501 THEN
502 FOR l_index IN l_header_value.FIRST .. l_header_value.LAST
503 LOOP
504 IF (l_header_name(l_index) = 'email_from_address')
505 THEN
506 l_email_from_address := l_header_value(l_index);
507 ELSIF (l_header_name(l_index) = 'email_reply_to_address')
508 THEN
509 l_email_reply_to_address := l_header_value(l_index);
510 ELSIF (l_header_name(l_index) = 'sender_display_name')
511 THEN
512 l_sender_display_name := l_header_value(l_index);
513 END IF;
514 END LOOP;
515 END IF;
516
517 --Checking records existence before calling the clean_stalled_request procedure.
518
519 SELECT COUNT(ROWID)
520 INTO l_header_count
521 FROM jtf_fm_int_request_header
522 WHERE request_id = l_request_id;
523
524
525 IF ( l_header_count > 0)
526 THEN
527 clean_stalled_request ( p_request_id => l_request_id
528 , x_return_status => l_clean_request_status
529 );
530 END IF;
531
532 INSERT INTO jtf_fm_int_request_header
533 ( request_id
534 , group_id
535 , server_id
536 , submit_dt_tm
537 , processed_dt_tm
538 , priority
539 , source_code_id
540 , source_code
541 , object_id
542 , object_type
543 , outcome_desc
544 , user_id
545 , last_update_date
546 , last_updated_by
547 , creation_date
548 , created_by
549 , last_update_login
550 , object_version_number
551 , request_status
552 , template_id
553 , no_of_parameters
554 , email_format
555 , email_from_address
556 , email_reply_to_address
557 , sender_display_name
558 , user_history
559 , subject
560 , parameter1
561 , parameter2
562 , parameter3
563 , parameter4
564 , parameter5
565 , parameter6
566 , parameter7
567 , parameter8
568 , parameter9
569 , parameter10
570 , parameter11
571 , parameter12
572 , parameter13
573 , parameter14
574 , parameter15
575 , parameter16
576 , parameter17
577 , parameter18
578 , parameter19
579 , parameter20
580 , parameter21
581 , parameter22
582 , parameter23
583 , parameter24
584 , parameter25
585 , parameter26
586 , parameter27
587 , parameter28
588 , parameter29
589 , parameter30
590 , parameter31
591 , parameter32
592 , parameter33
593 , parameter34
594 , parameter35
595 , parameter36
596 , parameter37
597 , parameter38
598 , parameter39
599 , parameter40
600 , parameter41
601 , parameter42
602 , parameter43
603 , parameter44
604 , parameter45
605 , parameter46
606 , parameter47
607 , parameter48
608 , parameter49
609 , parameter50
610 , parameter51
611 , parameter52
612 , parameter53
613 , parameter54
614 , parameter55
615 , parameter56
616 , parameter57
617 , parameter58
618 , parameter59
619 , parameter60
620 , parameter61
621 , parameter62
622 , parameter63
623 , parameter64
624 , parameter65
625 , parameter66
626 , parameter67
627 , parameter68
628 , parameter69
629 , parameter70
630 , parameter71
631 , parameter72
632 , parameter73
633 , parameter74
634 , parameter75
635 , parameter76
636 , parameter77
637 , parameter78
638 , parameter79
639 , parameter80
640 , parameter81
641 , parameter82
642 , parameter83
643 , parameter84
644 , parameter85
645 , parameter86
646 , parameter87
647 , parameter88
648 , parameter89
649 , parameter90
650 , parameter91
651 , parameter92
652 , parameter93
653 , parameter94
654 , parameter95
655 , parameter96
656 , parameter97
657 , parameter98
658 , parameter99
659 , parameter100
660 )
661 SELECT
662 l_request_id
663 , group_id
664 , server_id
665 , submit_dt_tm
666 , processed_dt_tm
667 , priority
668 , source_code_id
669 , source_code
670 , object_id
671 , object_type
672 , NULL
673 , user_id
674 , SYSDATE
675 , last_updated_by
676 , SYSDATE
677 , created_by
678 , last_update_login
679 , 1
680 , 'NEW'
681 , template_id
682 , l_col_cnt
683 , l_email_body
684 , l_email_from_address
685 , l_email_reply_to_address
686 , l_sender_display_name
687 , l_user_history
688 , l_subject
689 , l_rec_tab(1).col_name
690 , l_rec_tab(2).col_name
691 , l_rec_tab(3).col_name
692 , l_rec_tab(4).col_name
693 , l_rec_tab(5).col_name
694 , l_rec_tab(6).col_name
695 , l_rec_tab(7).col_name
696 , l_rec_tab(8).col_name
697 , l_rec_tab(9).col_name
698 , l_rec_tab(10).col_name
699 , l_rec_tab(11).col_name
700 , l_rec_tab(12).col_name
701 , l_rec_tab(13).col_name
702 , l_rec_tab(14).col_name
703 , l_rec_tab(15).col_name
704 , l_rec_tab(16).col_name
705 , l_rec_tab(17).col_name
706 , l_rec_tab(18).col_name
707 , l_rec_tab(19).col_name
708 , l_rec_tab(20).col_name
709 , l_rec_tab(21).col_name
710 , l_rec_tab(22).col_name
711 , l_rec_tab(23).col_name
712 , l_rec_tab(24).col_name
713 , l_rec_tab(25).col_name
714 , l_rec_tab(26).col_name
715 , l_rec_tab(27).col_name
716 , l_rec_tab(28).col_name
717 , l_rec_tab(29).col_name
718 , l_rec_tab(30).col_name
719 , l_rec_tab(31).col_name
720 , l_rec_tab(32).col_name
721 , l_rec_tab(33).col_name
722 , l_rec_tab(34).col_name
723 , l_rec_tab(35).col_name
724 , l_rec_tab(36).col_name
725 , l_rec_tab(37).col_name
726 , l_rec_tab(38).col_name
727 , l_rec_tab(39).col_name
728 , l_rec_tab(40).col_name
729 , l_rec_tab(41).col_name
730 , l_rec_tab(42).col_name
731 , l_rec_tab(43).col_name
732 , l_rec_tab(44).col_name
733 , l_rec_tab(45).col_name
734 , l_rec_tab(46).col_name
735 , l_rec_tab(47).col_name
736 , l_rec_tab(48).col_name
737 , l_rec_tab(49).col_name
738 , l_rec_tab(50).col_name
739 , l_rec_tab(51).col_name
740 , l_rec_tab(52).col_name
741 , l_rec_tab(53).col_name
742 , l_rec_tab(54).col_name
743 , l_rec_tab(55).col_name
744 , l_rec_tab(56).col_name
745 , l_rec_tab(57).col_name
746 , l_rec_tab(58).col_name
747 , l_rec_tab(59).col_name
748 , l_rec_tab(60).col_name
749 , l_rec_tab(61).col_name
750 , l_rec_tab(62).col_name
751 , l_rec_tab(63).col_name
752 , l_rec_tab(64).col_name
753 , l_rec_tab(65).col_name
754 , l_rec_tab(66).col_name
755 , l_rec_tab(67).col_name
756 , l_rec_tab(68).col_name
757 , l_rec_tab(69).col_name
758 , l_rec_tab(70).col_name
759 , l_rec_tab(71).col_name
760 , l_rec_tab(72).col_name
761 , l_rec_tab(73).col_name
762 , l_rec_tab(74).col_name
763 , l_rec_tab(75).col_name
764 , l_rec_tab(76).col_name
765 , l_rec_tab(77).col_name
766 , l_rec_tab(78).col_name
767 , l_rec_tab(79).col_name
768 , l_rec_tab(80).col_name
769 , l_rec_tab(81).col_name
770 , l_rec_tab(82).col_name
771 , l_rec_tab(83).col_name
772 , l_rec_tab(84).col_name
773 , l_rec_tab(85).col_name
774 , l_rec_tab(86).col_name
775 , l_rec_tab(87).col_name
776 , l_rec_tab(88).col_name
777 , l_rec_tab(89).col_name
778 , l_rec_tab(90).col_name
779 , l_rec_tab(91).col_name
780 , l_rec_tab(92).col_name
781 , l_rec_tab(93).col_name
782 , l_rec_tab(94).col_name
783 , l_rec_tab(95).col_name
784 , l_rec_tab(96).col_name
785 , l_rec_tab(97).col_name
786 , l_rec_tab(98).col_name
787 , l_rec_tab(99).col_name
788 , l_rec_tab(100).col_name
789 FROM jtf_fm_request_history_all
790 WHERE hist_req_id = l_request_id;
791
792 l_col_insert := '';
793
794 FOR i IN 1 .. l_col_cnt
795 LOOP
796 IF UPPER(l_rec_tab(i).col_name) = 'CUSTOMER_ID'
797 THEN
798 l_party_id_col := 'col'||i;
799 END IF;
800
801 IF UPPER(l_rec_tab(i).col_name) = 'EMAILADDRESS'
802 THEN
803 l_email_address_col := 'col'||i;
804 END IF;
805
806 IF UPPER(l_rec_tab(i).col_name) = 'FIRST_NAME' THEN
807 l_party_first_name_col := 'col'||i;
808 END IF;
809
810 IF UPPER(l_rec_tab(i).col_name) = 'LAST_NAME' THEN
811 l_party_last_name_col := 'col'||i;
812 END IF;
813
814 l_col_insert := l_col_insert ||',col'||i ;
815 END LOOP;
816
817 l_batch_size := NVL(FND_PROFILE.value('JTF_FM_BATCH_SIZE'),1000);
818
819 --Call to get_next_partition
820 get_next_partition ( x_errbuf => l_errbuf
821 , x_retcode => l_retcode
822 , p_request_id => l_request_id
823 , x_partition_id => l_partition_id);
824
825 IF (l_retcode <> 'S')
826 THEN
827 RAISE e_partition_not_found;
828 END IF;
829
830 --static columns
831 l_insert_statement := 'INSERT INTO jtf_fm_int_request_lines ( request_id, request_line_id, partition_id, batch_no, last_update_date, last_updated_by, creation_date,
832 created_by, last_update_login, object_version_number, email_status, enabled_flag,
833 contact_preference_flag ';
834
835 --values for static columns
836 l_select_cols :='SELECT ' || l_request_id ||', JTF_FM_INT_REQUEST_LINES_s.NEXTVAL, '|| l_partition_id ||',
837 CEIL(ROWNUM/ ' || l_batch_size || ' ),
838 SYSDATE,
839 1,
840 SYSDATE,
841 1,
842 1,
843 1,
844 '||''''||'AVAILABLE'||''''||','||
845 ''''||'Y'||''''||','
846 ||''''||'Y'||''''||',';
847
848 --adding this to the insert statement to avoid update on these rows
849 IF (l_party_id_col IS NOT NULL) THEN
850 l_insert_statement := l_insert_statement || ', party_id';
851 l_select_cols := l_select_cols || 'UserQuery.customer_id, ';
852 END IF;
853
854 --Logic to create the party name based upon the first name and last name.
855 IF (( l_party_first_name_col IS NOT NULL) AND
856 ( l_party_last_name_col IS NULL))
857 THEN
858 l_insert_statement := l_insert_statement || ', party_name';
859 l_select_cols := l_select_cols || 'UserQuery.first_name,';
860 ELSIF (( l_party_first_name_col IS NULL) AND
861 ( l_party_last_name_col IS NOT NULL))
862 THEN
863 l_insert_statement := l_insert_statement || ', party_name';
864 l_select_cols := l_select_cols || 'UserQuery.last_name,';
865 ELSIF (( l_party_first_name_col IS NOT NULL) AND
866 ( l_party_last_name_col IS NOT NULL))
867 THEN
868 l_insert_statement := l_insert_statement || ', party_name';
869 l_select_cols := l_select_cols || 'UserQuery.first_name || '' ''||UserQuery.last_name, ';
870 ELSE
871 l_insert_statement := l_insert_statement;
872 l_select_cols := l_select_cols;
873 END IF;
874
875 IF (l_email_address_col IS NOT NULL) THEN
876 l_insert_statement := l_insert_statement || ', email_address';
877 l_select_cols := l_select_cols || 'UserQuery.emailaddress, ';
878 END IF;
879
880 --creating the l_insert_statement from the static columns, their values and the
881 --query from the user
882 l_insert_statement := l_insert_statement || l_col_insert;
883 l_insert_statement := l_insert_statement || ') ' || l_select_cols || ' UserQuery.* from ('||l_fnd_query ||') UserQuery';
884
885 l_insert_statement := 'BEGIN ' || l_insert_statement || '; END;';
886
887 l_length := LENGTH(l_insert_statement);
888 l_no_of_chunks := CEIL(l_length/255);
889
890 --No need to create partition based upon the request_id
891 --l_create_part := 'ALTER TABLE jtf_fm_int_request_lines ADD PARTITION p_'|| l_request_id || ' VALUES ('||l_request_id || ')';
892 --EXECUTE IMMEDIATE l_create_part;
893
894
895 --Execute immediate requires the bind variables individually mentioned in the USING part.
896 --Dynamic Statements cannot be used if the input count is not known.
897 --Explored using DBMS_SQL but the performance is compromised
898 --We have therefore limited the number of bind variables to 5 and mentioned each case individually
899 IF l_bind_value.COUNT = 0
900 THEN
901 EXECUTE IMMEDIATE l_insert_statement;
902 ELSIF l_bind_value.COUNT = 1 THEN
903 EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1);
904 ELSIF l_bind_value.COUNT = 2 THEN
905 EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1), l_bind_value(2);
906 ELSIF l_bind_value.COUNT = 3 THEN
907 EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1), l_bind_value(2),l_bind_value(3);
908 ELSIF l_bind_value.COUNT = 4 THEN
909 EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1), l_bind_value(2),l_bind_value(3),l_bind_value(4);
910 ELSIF l_bind_value.COUNT = 5 THEN
911 EXECUTE IMMEDIATE l_insert_statement USING l_bind_value(1), l_bind_value(2),l_bind_value(3),l_bind_value(4),l_bind_value(5);
912 ELSE
913 RAISE e_too_many_bind_vars;
914 END IF;
915
916 INSERT INTO jtf_fm_int_request_batches
917 ( request_id,
918 batch_id,
919 request_line_id_start,
920 request_line_id_end,
921 server_instance_id,
922 batch_status ,
923 last_update_date
924 )
925 SELECT
926 l_request_id,
927 d.batch_no,
928 MIN(d.request_line_id),
929 MAX(d.request_line_id),
930 '',
931 'AVAILABLE',
932 SYSDATE
933 FROM jtf_fm_int_request_lines d
934 WHERE d.request_id = l_request_id
935 GROUP BY d.batch_no;
936
937 COMMIT;
938
939 IF (NVL(FND_PROFILE.value('JTF_FM_PRELOAD_TCA_PREF'), 'Y') = 'Y')
940 THEN
941 update_contact_pref(l_request_id );
942 END IF;
943
944 validate_email(l_request_id );
945
946 raiseBusinessEvent(l_request_id);
947
948 UPDATE jtf_fm_int_request_header c
949 SET c.request_status = 'AVAILABLE'
950 WHERE c.request_id = l_request_id ;
951
952 /* -- Moved to get_next_request procedure
953 UPDATE jtf_fm_status_all c
954 SET c.request_status = 'IN_PROCESS'
955 WHERE c.request_id = l_request_id;
956
957 UPDATE jtf_fm_request_history_all c
958 SET c.outcome_code = 'IN_PROCESS'
959 WHERE c.hist_req_id = l_request_id;
960 */
961
962 COMMIT;
963
964 x_return_status := 'S';
965 x_msg_count := 0;
966 x_msg_data := '';
967
968 EXCEPTION
969 WHEN FND_API.G_EXC_ERROR
970 THEN
971 x_return_status := FND_API.g_ret_sts_error ;
972 FND_MSG_PUB.Count_AND_Get ( p_count => x_msg_count,
973 p_data => x_msg_data,
974 p_encoded => FND_API.G_FALSE
975 );
976 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
977 THEN
978 x_return_status := FND_API.g_ret_sts_unexp_error ;
979 FND_MSG_PUB.Count_AND_Get
980 ( p_count => x_msg_count,
981 p_data => x_msg_data,
982 p_encoded => FND_API.G_FALSE
983 );
984 WHEN e_too_many_bind_vars
985 THEN
986 x_return_status := FND_API.g_ret_sts_error ;
987 x_msg_data := 'Error : Only 5 bind variables allowed in query';
988 WHEN e_partition_not_found
989 THEN
990 x_return_status := FND_API.g_ret_sts_error ;
991 x_msg_data := 'Unable to locate partition';
992 WHEN OTHERS
993 THEN
994 x_return_status := FND_API.g_ret_sts_unexp_error ;
995 x_msg_data := SQLERRM;
996 END process_request;
997
998 /*---------------------------------------------------------------------------------*
999 | Procedure Name : UPDATE_LINES_STATUS_BULK |
1000 | |
1001 | Purpose : For a given set of lines id, this procedure updates the |
1002 | jtf_fm_int_request_lines table and sets the email status to |
1003 | the passed email status PL/SQL table. |
1004 | - Updates jtf_fm_int_request_header - for request status |
1005 | - Updates jtf_fm_status_all - for request status |
1006 | - Updates jtf_fm_request_history_all - for outcome code |
1007 | - Updates jtf_fm_email_stat - for email details |
1008 | |
1009 *---------------------------------------------------------------------------------*/
1010 PROCEDURE update_lines_status_bulk ( line_ids IN JTF_VARCHAR2_TABLE_100
1011 , request_id IN NUMBER
1012 , line_status IN JTF_VARCHAR2_TABLE_100
1013 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1014 , x_return_status OUT NOCOPY VARCHAR2
1015 , x_msg_count OUT NOCOPY NUMBER
1016 , x_msg_data OUT NOCOPY VARCHAR2
1017 ) IS
1018 l_request_id NUMBER;
1019 l_complete_flag VARCHAR2(1) := 'N';
1020 l_contact_pref_disabled NUMBER;
1021 l_not_delivered NUMBER;
1022 l_disabled_flag NUMBER;
1023 l_count NUMBER;
1024 CURSOR c_processed IS
1025 SELECT 'Y'
1026 FROM jtf_fm_int_request_header c
1027 WHERE c.request_id = l_request_id
1028 AND c.request_status = 'READYTOLOG' ;
1029 CURSOR c_details IS
1030 SELECT SUM(DECODE(c.contact_preference_flag,'N',1,0)) ,
1031 SUM(DECODE(c.email_status,'NOTDELIVERED',1,0)) ,
1032 SUM(DECODE(c.enabled_flag, 'N',1,0)),
1033 COUNT(1)
1034 FROM jtf_fm_int_request_lines c
1035 WHERE c.request_id = l_request_id ;
1036 BEGIN
1037 l_request_id := request_id;
1038
1039 FORALL i IN LINE_IDS.FIRST .. LINE_IDS.LAST
1040 UPDATE jtf_fm_int_request_lines
1041 SET email_status = line_status(i)
1042 WHERE request_line_id = line_ids(i);
1043
1044 UPDATE jtf_fm_int_request_header c
1045 SET c.request_status = 'READYTOLOG'
1046 WHERE c.request_id = l_request_id
1047 AND NOT EXISTS ( SELECT '1'
1048 FROM jtf_fm_int_request_lines a
1049 WHERE c.request_id = a.request_id
1050 AND a.email_status IN ('AVAILABLE', 'NEW','ASSIGNED')
1051 AND a.enabled_flag = 'Y' );
1052 OPEN c_processed;
1053 FETCH c_processed INTO l_complete_flag;
1054 CLOSE c_processed;
1055
1056 IF l_complete_flag = 'Y'
1057 THEN
1058 OPEN c_details;
1059 FETCH c_details INTO l_contact_pref_disabled ,
1060 l_not_delivered ,
1061 l_disabled_flag ,
1062 l_count ;
1063 CLOSE c_details;
1064
1065 UPDATE jtf_fm_status_all c
1066 SET c.request_status = 'READYTOLOG',
1067 c.LAST_UPDATE_DATE = SYSDATE,
1068 c.jobs_processed = l_count
1069 WHERE c.request_id = l_request_id;
1070
1071 UPDATE jtf_fm_request_history_all
1072 SET outcome_code = 'READYTOLOG',
1073 total_jobs = l_count,
1074 process_dt_tm = SYSDATE,
1075 processed_dt_tm = SYSDATE,
1076 last_update_date = SYSDATE
1077 WHERE hist_req_id = l_request_id;
1078
1079 UPDATE jtf_fm_email_stats
1080 SET total = l_count,
1081 sent = l_count - l_not_delivered ,
1082 malformed = l_disabled_flag - l_contact_pref_disabled ,
1083 bounced = 0,
1084 opened = 0,
1085 unsubscribed = 0,
1086 do_not_contact = l_contact_pref_disabled
1087 WHERE request_id = l_request_id ;
1088
1089 END IF;
1090
1091 IF x_return_status = fnd_api.g_ret_sts_error
1092 THEN
1093 RAISE FND_API.g_exc_error;
1094 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1095 THEN
1096 RAISE FND_API.g_exc_unexpected_error;
1097 END IF;
1098
1099 IF p_commit = FND_API.g_true
1100 THEN
1101 COMMIT WORK;
1102 END IF;
1103
1104 x_return_status := 'S';
1105
1106 FND_MSG_PUB.Count_AND_Get ( p_count => x_msg_count,
1107 p_data => x_msg_data,
1108 p_encoded => FND_API.G_FALSE );
1109
1110 EXCEPTION
1111 WHEN FND_API.G_EXC_ERROR
1112 THEN
1113 x_return_status := FND_API.g_ret_sts_error ;
1114 FND_MSG_PUB.Count_AND_Get ( p_count => x_msg_count,
1115 p_data => x_msg_data,
1116 p_encoded => FND_API.G_FALSE
1117 );
1118 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1119 THEN
1120 x_return_status := FND_API.g_ret_sts_unexp_error ;
1121 FND_MSG_PUB.Count_AND_Get
1122 ( p_count => x_msg_count,
1123 p_data => x_msg_data,
1124 p_encoded => FND_API.G_FALSE
1125 );
1126 WHEN OTHERS
1127 THEN
1128 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
1129
1130 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1131 THEN
1132 NULL;
1133 END IF;
1134
1135 FND_MSG_PUB.Count_AND_Get ( p_count => x_msg_count,
1136 p_data => x_msg_data,
1137 p_encoded => FND_API.G_FALSE
1138 );
1139
1140 END;
1141
1142 /*---------------------------------------------------------------------------------*
1143 | Procedure Name : UPDATE_LINES_STATUS |
1144 | |
1145 | Purpose : For a given set of lines id, this procedure updates the |
1146 | jtf_fm_int_request_lines table and sets the email status to |
1147 | the passed email status PL/SQL table. |
1148 | |
1149 *---------------------------------------------------------------------------------*/
1150 PROCEDURE update_lines_status ( line_ids IN JTF_VARCHAR2_TABLE_100
1151 , request_id IN NUMBER
1152 , line_status IN VARCHAR2
1153 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1154 , x_return_status OUT NOCOPY VARCHAR2
1155 , x_msg_count OUT NOCOPY NUMBER
1156 , x_msg_data OUT NOCOPY VARCHAR2
1157 ) IS
1158 BEGIN
1159 FORALL i IN LINE_IDS.FIRST .. LINE_IDS.LAST
1160 UPDATE jtf_fm_int_request_lines
1161 SET email_status = line_status
1162 WHERE request_line_id = line_ids(i);
1163
1164 IF x_return_status = fnd_api.g_ret_sts_error
1165 THEN
1166 RAISE FND_API.g_exc_error;
1167 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1168 THEN
1169 RAISE FND_API.g_exc_unexpected_error;
1170 END IF;
1171
1172 IF p_commit = FND_API.g_true
1173 THEN
1174 COMMIT WORK;
1175 END IF;
1176
1177 x_return_status := 'S';
1178 FND_MSG_PUB.Count_AND_Get ( p_count => x_msg_count,
1179 p_data => x_msg_data,
1180 p_encoded => FND_API.G_FALSE );
1181
1182 EXCEPTION
1183 WHEN FND_API.G_EXC_ERROR
1184 THEN
1185 x_return_status := FND_API.g_ret_sts_error ;
1186 FND_MSG_PUB.Count_AND_Get ( p_count => x_msg_count,
1187 p_data => x_msg_data,
1188 p_encoded => FND_API.G_FALSE
1189 );
1190
1191 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1192 THEN
1193 x_return_status := FND_API.g_ret_sts_unexp_error ;
1194 FND_MSG_PUB.Count_AND_Get ( p_count => x_msg_count,
1195 p_data => x_msg_data,
1196 p_encoded => FND_API.G_FALSE
1197 );
1198 WHEN OTHERS
1199 THEN
1200 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
1201
1202 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1203 THEN
1204 NULL;
1205 END IF;
1206
1207 FND_MSG_PUB.Count_AND_Get ( p_count => x_msg_count,
1208 p_data => x_msg_data,
1209 p_encoded => FND_API.G_FALSE
1210 );
1211
1212 END;
1213
1214 /*---------------------------------------------------------------------------------*
1215 | Procedure Name : UPDATE_INSTANCE_STATUS |
1216 | |
1217 | Purpose : - Updates jtf_fm_int_request_batches - for batch status |
1218 | - Updates jtf_fm_int_request_header - for request status |
1219 | - Updates jtf_fm_status_all - for request status |
1220 | - Updates jtf_fm_request_history_all - for outcome code |
1221 | - Updates jtf_fm_email_stat - for email details |
1222 | |
1223 *---------------------------------------------------------------------------------*/
1224 PROCEDURE update_instance_status( p_request_id IN NUMBER
1225 , p_server_id IN NUMBER
1226 , p_instance_id IN NUMBER
1227 , p_status IN VARCHAR2
1228 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1229 , x_return_status OUT NOCOPY VARCHAR2
1230 , x_msg_count OUT NOCOPY NUMBER
1231 , x_msg_data OUT NOCOPY VARCHAR2
1232 ) IS
1233 l_request_id NUMBER;
1234 l_complete_flag VARCHAR2(1) := 'N';
1235 l_contact_pref_disabled NUMBER;
1236 l_not_delivered NUMBER;
1237 l_disabled_flag NUMBER;
1238 l_count NUMBER;
1239 CURSOR c_header IS
1240 SELECT 'Y'
1241 FROM jtf_fm_int_request_header c
1242 WHERE c.request_id = l_request_id
1243 AND c.request_status = 'READYTOLOG' ;
1244
1245 CURSOR c_lines IS
1246 SELECT SUM(DECODE(c.CONTACT_PREFERENCE_FLAG,'N',1,0)) ,
1247 SUM(DECODE(c.EMAIL_STATUS,'NOTDELIVERED',1,0)) ,
1248 SUM(DECODE(c.enabled_flag, 'N',1,0)),
1249 COUNT(1)
1250 FROM jtf_fm_int_request_lines c
1251 WHERE c.request_id = l_request_id ;
1252 BEGIN
1253 l_request_id := p_request_id;
1254
1255 UPDATE jtf_fm_int_request_batches
1256 SET batch_status = p_status,
1257 last_update_date = SYSDATE
1258 WHERE request_id = p_request_id;
1259
1260 UPDATE jtf_fm_int_request_header c
1261 SET c.request_status = 'READYTOLOG'
1262 WHERE c.request_id = p_request_id
1263 AND NOT EXISTS ( SELECT '1'
1264 FROM jtf_fm_int_request_batches a
1265 WHERE c.request_id = a.request_id
1266 AND a.batch_status IN ('AVAILABLE', 'NEW','ASSIGNED')
1267 );
1268 OPEN c_header;
1269 FETCH c_header INTO l_complete_flag;
1270 CLOSE c_header;
1271
1272 IF l_complete_flag = 'Y' THEN
1273 OPEN c_lines;
1274 FETCH c_lines INTO l_contact_pref_disabled ,
1275 l_not_delivered ,
1276 l_disabled_flag ,
1277 l_count ;
1278 CLOSE c_lines;
1279
1280 UPDATE jtf_fm_status_all c
1281 SET c.request_status = 'READYTOLOG',
1282 c.LAST_UPDATE_DATE = SYSDATE,
1283 c.jobs_processed = l_count
1284 WHERE c.request_id = l_request_id;
1285
1286 UPDATE jtf_fm_request_history_all
1287 SET outcome_code = 'READYTOLOG',
1288 total_jobs = l_count,
1289 process_dt_tm = SYSDATE,
1290 processed_dt_tm = SYSDATE,
1291 last_update_date = SYSDATE
1292 WHERE hist_req_id = l_request_id;
1293
1294 UPDATE jtf_fm_email_stats
1295 SET total = l_count,
1296 sent = l_count - l_not_delivered ,
1297 malformed = l_disabled_flag - l_contact_pref_disabled ,
1298 bounced = 0,
1299 opened = 0,
1300 unsubscribed = 0,
1301 do_not_contact = l_contact_pref_disabled
1302 WHERE request_id=l_request_id ;
1303
1304 END IF;
1305
1306 IF x_return_status = fnd_api.g_ret_sts_error
1307 THEN
1308 RAISE FND_API.g_exc_error;
1309 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1310 THEN
1311 RAISE FND_API.g_exc_unexpected_error;
1312 END IF;
1313
1314 IF p_commit = FND_API.g_true
1315 THEN
1316 COMMIT WORK;
1317 END IF;
1318
1319 x_return_status := 'S';
1320 x_msg_count := 0;
1321 x_msg_data := '';
1322
1323 FND_MSG_PUB.Count_AND_Get
1324 ( p_count => x_msg_count,
1325 p_data => x_msg_data,
1326 p_encoded => FND_API.G_FALSE );
1327
1328 EXCEPTION
1329 WHEN FND_API.G_EXC_ERROR
1330 THEN
1331 x_return_status := FND_API.g_ret_sts_error ;
1332 FND_MSG_PUB.Count_AND_Get
1333 ( p_count => x_msg_count,
1334 p_data => x_msg_data,
1335 p_encoded => FND_API.G_FALSE
1336 );
1337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1338 THEN
1339 x_return_status := FND_API.g_ret_sts_unexp_error ;
1340 FND_MSG_PUB.Count_AND_Get
1341 ( p_count => x_msg_count,
1342 p_data => x_msg_data,
1343 p_encoded => FND_API.G_FALSE
1344 );
1345 WHEN OTHERS
1346 THEN
1347 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
1348
1349 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1350 THEN
1351 NULL;
1352 END IF;
1353
1354 FND_MSG_PUB.Count_AND_Get
1355 ( p_count => x_msg_count,
1356 p_data => x_msg_data,
1357 p_encoded => FND_API.G_FALSE
1358 );
1359
1360 END;
1361
1362 /*---------------------------------------------------------------------------------*
1363 | Procedure Name : CLEAN_UP_INSTANCE |
1364 | |
1365 | Purpose : - Updates jtf_fm_int_request_batches - for batch status |
1366 | |
1367 *---------------------------------------------------------------------------------*/
1368 PROCEDURE clean_up_instance( p_request_id IN NUMBER
1369 , p_server_id IN NUMBER
1370 , p_instance_id IN NUMBER
1371 , P_commit IN VARCHAR2 := FND_API.G_FALSE
1372 , x_return_status OUT NOCOPY VARCHAR2
1373 , x_msg_count OUT NOCOPY NUMBER
1374 , x_msg_data OUT NOCOPY VARCHAR2) IS
1375 BEGIN
1376 UPDATE jtf_fm_int_request_batches
1377 SET batch_status = 'NEW',
1378 last_update_date = SYSDATE
1379 WHERE request_ID = p_request_id
1380 AND server_instance_id = p_instance_id;
1381
1382 IF x_return_status = fnd_api.g_ret_sts_error
1383 THEN
1384 RAISE FND_API.g_exc_error;
1385 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error
1386 THEN
1387 RAISE FND_API.g_exc_unexpected_error;
1388 END IF;
1389
1390 IF p_commit = FND_API.g_true
1391 THEN
1392 COMMIT WORK;
1393 END IF;
1394
1395 x_return_status := 'S';
1396
1397 FND_MSG_PUB.Count_AND_Get
1398 ( p_count => x_msg_count,
1399 p_data => x_msg_data,
1400 p_encoded => FND_API.G_FALSE );
1401
1402 EXCEPTION
1403 WHEN FND_API.G_EXC_ERROR
1404 THEN
1405 x_return_status := FND_API.g_ret_sts_error ;
1406 FND_MSG_PUB.Count_AND_Get
1407 ( p_count => x_msg_count,
1408 p_data => x_msg_data,
1409 p_encoded => FND_API.G_FALSE
1410 );
1411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1412 THEN
1413 x_return_status := FND_API.g_ret_sts_unexp_error ;
1414 FND_MSG_PUB.Count_AND_Get
1415 ( p_count => x_msg_count,
1416 p_data => x_msg_data,
1417 p_encoded => FND_API.G_FALSE
1418 );
1419 WHEN OTHERS
1420 THEN
1421 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
1422
1423 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1424 THEN
1425 NULL;
1426 END IF;
1427 FND_MSG_PUB.Count_AND_Get
1428 ( p_count => x_msg_count,
1429 p_data => x_msg_data,
1430 p_encoded => FND_API.G_FALSE
1431 );
1432
1433 END;
1434
1435 /*---------------------------------------------------------------------------------*
1436 | Procedure Name : GET_NEXT_REQUEST |
1437 | |
1438 | Purpose : Selects from jtf_fm_int_request_lines , and |
1439 | - Updates jtf_fm_int_request_header - for request status |
1440 | - Updates jtf_fm_status_all - for request status |
1441 | - Updates jtf_fm_request_history_all - for outcome code |
1442 | |
1443 *---------------------------------------------------------------------------------*/
1444 PROCEDURE get_next_request( p_server_id IN NUMBER
1445 , p_instance_id IN NUMBER
1446 , p_request_id OUT NOCOPY NUMBER
1447 , p_template_id OUT NOCOPY NUMBER
1448 , p_no_of_parameters OUT NOCOPY NUMBER
1449 , p_email_format OUT NOCOPY VARCHAR2
1450 , p_email_from_address OUT NOCOPY VARCHAR2
1451 , p_email_reply_to_address OUT NOCOPY VARCHAR2
1452 , p_sender_display_name OUT NOCOPY VARCHAR2
1453 , p_subject OUT NOCOPY VARCHAR2
1454 , p_parameter_table OUT NOCOPY JTF_VARCHAR2_TABLE_100
1455 , x_return_status OUT NOCOPY VARCHAR2
1456 , x_msg_count OUT NOCOPY NUMBER
1457 , x_msg_data OUT NOCOPY VARCHAR2
1458 ) IS
1459 CURSOR c_header IS
1460 SELECT request_id,
1461 template_id,
1462 NO_OF_PARAMETERS,
1463 EMAIL_FORMAT,
1464 EMAIL_FROM_ADDRESS,
1465 EMAIL_REPLY_TO_ADDRESS,
1466 sender_display_name,
1467 subject ,
1468 parameter1,
1469 parameter2,
1470 parameter3,
1471 parameter4,
1472 parameter5,
1473 parameter6,
1474 parameter7,
1475 parameter8,
1476 parameter9,
1477 parameter10,
1478 parameter11,
1479 parameter12,
1480 parameter13,
1481 parameter14,
1482 parameter15,
1483 parameter16,
1484 parameter17,
1485 parameter18,
1486 parameter19,
1487 parameter20,
1488 parameter21,
1489 parameter22,
1490 parameter23,
1491 parameter24,
1492 parameter25,
1493 parameter26,
1494 parameter27,
1495 parameter28,
1496 parameter29,
1497 parameter30,
1498 parameter31,
1499 parameter32,
1500 parameter33,
1501 parameter34,
1502 parameter35,
1503 parameter36,
1504 parameter37,
1505 parameter38,
1506 parameter39,
1507 parameter40,
1508 parameter41,
1509 parameter42,
1510 parameter43,
1511 parameter44,
1512 parameter45,
1513 parameter46,
1514 parameter47,
1515 parameter48,
1516 parameter49,
1517 parameter50,
1518 parameter51,
1519 parameter52,
1520 parameter53,
1521 parameter54,
1522 parameter55,
1523 parameter56,
1524 parameter57,
1525 parameter58,
1526 parameter59,
1527 parameter60,
1528 parameter61,
1529 parameter62,
1530 parameter63,
1531 parameter64,
1532 parameter65,
1533 parameter66,
1534 parameter67,
1535 parameter68,
1536 parameter69,
1537 parameter70,
1538 parameter71,
1539 parameter72,
1540 parameter73,
1541 parameter74,
1542 parameter75,
1543 parameter76,
1544 parameter77,
1545 parameter78,
1546 parameter79,
1547 parameter80,
1548 parameter81,
1549 parameter82,
1550 parameter83,
1551 parameter84,
1552 parameter85,
1553 parameter86,
1554 parameter87,
1555 parameter88,
1556 parameter89,
1557 parameter90,
1558 parameter91,
1559 parameter92,
1560 parameter93,
1561 parameter94,
1562 parameter95,
1563 parameter96,
1564 parameter97,
1565 parameter98,
1566 parameter99,
1567 parameter100
1568 FROM jtf_fm_int_request_header
1569 WHERE request_status IN ('AVAILABLE','INPROGRESS' )
1570 AND server_id = p_server_id
1571 ORDER BY priority, creation_date DESC;
1572 l_header_request_id NUMBER;
1573 BEGIN
1574 p_parameter_table := jtf_varchar2_table_100();
1575
1576 FOR i IN 1 .. 100
1577 LOOP
1578 p_parameter_table.extend();
1579 END LOOP;
1580
1581 OPEN c_header ;
1582 FETCH c_header INTO p_request_id,
1583 p_template_id,
1584 p_no_of_parameters,
1585 p_email_format,
1586 p_email_from_address,
1587 p_email_reply_to_address,
1588 p_sender_display_name,
1589 p_subject ,
1590 p_parameter_table(1),
1591 p_parameter_table(2),
1592 p_parameter_table(3),
1593 p_parameter_table(4),
1594 p_parameter_table(5),
1595 p_parameter_table(6),
1596 p_parameter_table(7),
1597 p_parameter_table(8),
1598 p_parameter_table(9),
1599 p_parameter_table(10),
1600 p_parameter_table(11),
1601 p_parameter_table(12),
1602 p_parameter_table(13),
1603 p_parameter_table(14),
1604 p_parameter_table(15),
1605 p_parameter_table(16),
1606 p_parameter_table(17),
1607 p_parameter_table(18),
1608 p_parameter_table(19),
1609 p_parameter_table(20),
1610 p_parameter_table(21),
1611 p_parameter_table(22),
1612 p_parameter_table(23),
1613 p_parameter_table(24),
1614 p_parameter_table(25),
1615 p_parameter_table(26),
1616 p_parameter_table(27),
1617 p_parameter_table(28),
1618 p_parameter_table(29),
1619 p_parameter_table(30),
1620 p_parameter_table(31),
1621 p_parameter_table(32),
1622 p_parameter_table(33),
1623 p_parameter_table(34),
1624 p_parameter_table(35),
1625 p_parameter_table(36),
1626 p_parameter_table(37),
1627 p_parameter_table(38),
1628 p_parameter_table(39),
1629 p_parameter_table(40),
1630 p_parameter_table(41),
1631 p_parameter_table(42),
1632 p_parameter_table(43),
1633 p_parameter_table(44),
1634 p_parameter_table(45),
1635 p_parameter_table(46),
1636 p_parameter_table(47),
1637 p_parameter_table(48),
1638 p_parameter_table(49),
1639 p_parameter_table(50),
1640 p_parameter_table(51),
1641 p_parameter_table(52),
1642 p_parameter_table(53),
1643 p_parameter_table(54),
1644 p_parameter_table(55),
1645 p_parameter_table(56),
1646 p_parameter_table(57),
1647 p_parameter_table(58),
1648 p_parameter_table(59),
1649 p_parameter_table(60),
1650 p_parameter_table(61),
1651 p_parameter_table(62),
1652 p_parameter_table(63),
1653 p_parameter_table(64),
1654 p_parameter_table(65),
1655 p_parameter_table(66),
1656 p_parameter_table(67),
1657 p_parameter_table(68),
1658 p_parameter_table(69),
1659 p_parameter_table(70),
1660 p_parameter_table(71),
1661 p_parameter_table(72),
1662 p_parameter_table(73),
1663 p_parameter_table(74),
1664 p_parameter_table(75),
1665 p_parameter_table(76),
1666 p_parameter_table(77),
1667 p_parameter_table(78),
1668 p_parameter_table(79),
1669 p_parameter_table(80),
1670 p_parameter_table(81),
1671 p_parameter_table(82),
1672 p_parameter_table(83),
1673 p_parameter_table(84),
1674 p_parameter_table(85),
1675 p_parameter_table(86),
1676 p_parameter_table(87),
1677 p_parameter_table(88),
1678 p_parameter_table(89),
1679 p_parameter_table(90),
1680 p_parameter_table(91),
1681 p_parameter_table(92),
1682 p_parameter_table(93),
1683 p_parameter_table(94),
1684 p_parameter_table(95),
1685 p_parameter_table(96),
1686 p_parameter_table(97),
1687 p_parameter_table(98),
1688 p_parameter_table(99),
1689 p_parameter_table(100) ;
1690 CLOSE c_header;
1691
1692 l_header_request_id := p_request_id;
1693
1694 UPDATE jtf_fm_int_request_header
1695 SET request_status = 'INPROGRESS'
1696 WHERE request_id = p_request_id
1697 AND request_status = 'AVAILABLE';
1698
1699 UPDATE jtf_fm_status_all c
1700 SET c.request_status = 'IN_PROCESS'
1701 WHERE c.request_id = l_header_request_id
1702 AND c.request_status <> 'READYTOLOG'
1703 ;
1704
1705 UPDATE jtf_fm_request_history_all c
1706 SET c.outcome_code = 'IN_PROCESS'
1707 WHERE c.hist_req_id = l_header_request_id
1708 AND c.outcome_code <> 'READYTOLOG'
1709 ;
1710
1711 COMMIT;
1712
1713 x_return_status := 'S';
1714 x_msg_count := 0;
1715 x_msg_data := '';
1716
1717 END get_next_request;
1718
1719 /*---------------------------------------------------------------------------------*
1720 | Procedure Name : GET_NEXT_BATCH |
1721 | |
1722 | Purpose : Selects from jtf_fm_int_request_lines , and |
1723 | - Updates jtf_fm_int_request_batches - for request status |
1724 | |
1725 *---------------------------------------------------------------------------------*/
1726 PROCEDURE get_next_batch ( p_request_id IN NUMBER
1727 , p_server_id IN NUMBER
1728 , p_instance_id IN NUMBER
1729 , P_commit IN VARCHAR2 := FND_API.G_FALSE
1730 , p_line_ids OUT NOCOPY JTF_NUMBER_TABLE
1731 , p_party_id OUT NOCOPY JTF_NUMBER_TABLE
1732 , p_party_name OUT NOCOPY JTF_VARCHAR2_TABLE_200
1733 , p_email_address OUT NOCOPY JTF_VARCHAR2_TABLE_200
1734 , p_COL1 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1735 , p_COL2 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1736 , p_COL3 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1737 , p_COL4 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1738 , p_COL5 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1739 , p_COL6 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1740 , p_COL7 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1741 , p_COL8 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1742 , p_COL9 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1743 , p_COL10 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1744 , p_COL11 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1745 , p_COL12 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1746 , p_COL13 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1747 , p_COL14 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1748 , p_COL15 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1749 , p_COL16 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1750 , p_COL17 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1751 , p_COL18 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1752 , p_COL19 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1753 , p_COL20 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1754 , p_COL21 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1755 , p_COL22 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1756 , p_COL23 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1757 , p_COL24 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1758 , p_COL25 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1759 , p_COL26 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1760 , p_COL27 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1761 , p_COL28 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1762 , p_COL29 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1763 , p_COL30 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1764 , p_COL31 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1765 , p_COL32 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1766 , p_COL33 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1767 , p_COL34 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1768 , p_COL35 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1769 , p_COL36 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1770 , p_COL37 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1771 , p_COL38 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1772 , p_COL39 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1773 , p_COL40 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1774 , p_COL41 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1775 , p_COL42 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1776 , p_COL43 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1777 , p_COL44 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1778 , p_COL45 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1779 , p_COL46 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1780 , p_COL47 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1781 , p_COL48 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1782 , p_COL49 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1783 , p_COL50 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1784 , p_COL51 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1785 , p_COL52 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1786 , p_COL53 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1787 , p_COL54 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1788 , p_COL55 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1789 , p_COL56 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1790 , p_COL57 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1791 , p_COL58 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1792 , p_COL59 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1793 , p_COL60 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1794 , p_COL61 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1795 , p_COL62 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1796 , p_COL63 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1797 , p_COL64 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1798 , p_COL65 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1799 , p_COL66 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1800 , p_COL67 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1801 , p_COL68 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1802 , p_COL69 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1803 , p_COL70 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1804 , p_COL71 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1805 , p_COL72 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1806 , p_COL73 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1807 , p_COL74 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1808 , p_COL75 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1809 , p_COL76 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1810 , p_COL77 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1811 , p_COL78 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1812 , p_COL79 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1813 , p_COL80 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1814 , p_COL81 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1815 , p_COL82 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1816 , p_COL83 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1817 , p_COL84 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1818 , p_COL85 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1819 , p_COL86 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1820 , p_COL87 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1821 , p_COL88 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1822 , p_COL89 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1823 , p_COL90 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1824 , p_COL91 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1825 , p_COL92 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1826 , p_COL93 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1827 , p_COL94 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1828 , p_COL95 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1829 , p_COL96 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1830 , p_COL97 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1831 , p_COL98 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1832 , p_COL99 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1833 , p_COL100 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
1834 , x_no_of_rows OUT NOCOPY NUMBER
1835 , x_return_status OUT NOCOPY VARCHAR2
1836 , x_msg_count OUT NOCOPY NUMBER
1837 , x_msg_data OUT NOCOPY VARCHAR2) IS
1838 CURSOR c_batch IS
1839 SELECT rb.batch_id,
1840 rb.request_line_id_start ,
1841 rb.request_line_id_end
1842 FROM jtf_fm_int_request_batches rb
1843 WHERE rb.request_id = p_request_id
1844 AND rb.batch_status = 'AVAILABLE' ;
1845 l_batch_id NUMBER;
1846 l_request_line_id_start NUMBER;
1847 l_request_line_id_end NUMBER;
1848 l_batch_size NUMBER;
1849 BEGIN
1850 p_line_ids := JTF_NUMBER_TABLE();
1851 p_PARTY_ID := JTF_NUMBER_TABLE();
1852 p_PARTY_NAME := JTF_VARCHAR2_TABLE_200();
1853 p_EMAIL_ADDRESS := JTF_VARCHAR2_TABLE_200();
1854 p_COL1 := JTF_VARCHAR2_TABLE_2000();
1855 p_COL2 := JTF_VARCHAR2_TABLE_2000();
1856 p_COL3 := JTF_VARCHAR2_TABLE_2000();
1857 p_COL4 := JTF_VARCHAR2_TABLE_2000();
1858 p_COL5 := JTF_VARCHAR2_TABLE_2000();
1859 p_COL6 := JTF_VARCHAR2_TABLE_2000();
1860 p_COL7 := JTF_VARCHAR2_TABLE_2000();
1861 p_COL8 := JTF_VARCHAR2_TABLE_2000();
1862 p_COL9 := JTF_VARCHAR2_TABLE_2000();
1863 p_COL10 := JTF_VARCHAR2_TABLE_2000();
1864 p_COL11 := JTF_VARCHAR2_TABLE_2000();
1865 p_COL12 := JTF_VARCHAR2_TABLE_2000();
1866 p_COL13 := JTF_VARCHAR2_TABLE_2000();
1867 p_COL14 := JTF_VARCHAR2_TABLE_2000();
1868 p_COL15 := JTF_VARCHAR2_TABLE_2000();
1869 p_COL16 := JTF_VARCHAR2_TABLE_2000();
1870 p_COL17 := JTF_VARCHAR2_TABLE_2000();
1871 p_COL18 := JTF_VARCHAR2_TABLE_2000();
1872 p_COL19 := JTF_VARCHAR2_TABLE_2000();
1873 p_COL20 := JTF_VARCHAR2_TABLE_2000();
1874 p_COL21 := JTF_VARCHAR2_TABLE_2000();
1875 p_COL22 := JTF_VARCHAR2_TABLE_2000();
1876 p_COL23 := JTF_VARCHAR2_TABLE_2000();
1877 p_COL24 := JTF_VARCHAR2_TABLE_2000();
1878 p_COL25 := JTF_VARCHAR2_TABLE_2000();
1879 p_COL26 := JTF_VARCHAR2_TABLE_2000();
1880 p_COL27 := JTF_VARCHAR2_TABLE_2000();
1881 p_COL28 := JTF_VARCHAR2_TABLE_2000();
1882 p_COL29 := JTF_VARCHAR2_TABLE_2000();
1883 p_COL30 := JTF_VARCHAR2_TABLE_2000();
1884 p_COL31 := JTF_VARCHAR2_TABLE_2000();
1885 p_COL32 := JTF_VARCHAR2_TABLE_2000();
1886 p_COL33 := JTF_VARCHAR2_TABLE_2000();
1887 p_COL34 := JTF_VARCHAR2_TABLE_2000();
1888 p_COL35 := JTF_VARCHAR2_TABLE_2000();
1889 p_COL36 := JTF_VARCHAR2_TABLE_2000();
1890 p_COL37 := JTF_VARCHAR2_TABLE_2000();
1891 p_COL38 := JTF_VARCHAR2_TABLE_2000();
1892 p_COL39 := JTF_VARCHAR2_TABLE_2000();
1893 p_COL40 := JTF_VARCHAR2_TABLE_2000();
1894 p_COL41 := JTF_VARCHAR2_TABLE_2000();
1895 p_COL42 := JTF_VARCHAR2_TABLE_2000();
1896 p_COL43 := JTF_VARCHAR2_TABLE_2000();
1897 p_COL44 := JTF_VARCHAR2_TABLE_2000();
1898 p_COL45 := JTF_VARCHAR2_TABLE_2000();
1899 p_COL46 := JTF_VARCHAR2_TABLE_2000();
1900 p_COL47 := JTF_VARCHAR2_TABLE_2000();
1901 p_COL48 := JTF_VARCHAR2_TABLE_2000();
1902 p_COL49 := JTF_VARCHAR2_TABLE_2000();
1903 p_COL50 := JTF_VARCHAR2_TABLE_2000();
1904 p_COL51 := JTF_VARCHAR2_TABLE_2000();
1905 p_COL52 := JTF_VARCHAR2_TABLE_2000();
1906 p_COL53 := JTF_VARCHAR2_TABLE_2000();
1907 p_COL54 := JTF_VARCHAR2_TABLE_2000();
1908 p_COL55 := JTF_VARCHAR2_TABLE_2000();
1909 p_COL56 := JTF_VARCHAR2_TABLE_2000();
1910 p_COL57 := JTF_VARCHAR2_TABLE_2000();
1911 p_COL58 := JTF_VARCHAR2_TABLE_2000();
1912 p_COL59 := JTF_VARCHAR2_TABLE_2000();
1913 p_COL60 := JTF_VARCHAR2_TABLE_2000();
1914 p_COL61 := JTF_VARCHAR2_TABLE_2000();
1915 p_COL62 := JTF_VARCHAR2_TABLE_2000();
1916 p_COL63 := JTF_VARCHAR2_TABLE_2000();
1917 p_COL64 := JTF_VARCHAR2_TABLE_2000();
1918 p_COL65 := JTF_VARCHAR2_TABLE_2000();
1919 p_COL66 := JTF_VARCHAR2_TABLE_2000();
1920 p_COL67 := JTF_VARCHAR2_TABLE_2000();
1921 p_COL68 := JTF_VARCHAR2_TABLE_2000();
1922 p_COL69 := JTF_VARCHAR2_TABLE_2000();
1923 p_COL70 := JTF_VARCHAR2_TABLE_2000();
1924 p_COL71 := JTF_VARCHAR2_TABLE_2000();
1925 p_COL72 := JTF_VARCHAR2_TABLE_2000();
1926 p_COL73 := JTF_VARCHAR2_TABLE_2000();
1927 p_COL74 := JTF_VARCHAR2_TABLE_2000();
1928 p_COL75 := JTF_VARCHAR2_TABLE_2000();
1929 p_COL76 := JTF_VARCHAR2_TABLE_2000();
1930 p_COL77 := JTF_VARCHAR2_TABLE_2000();
1931 p_COL78 := JTF_VARCHAR2_TABLE_2000();
1932 p_COL79 := JTF_VARCHAR2_TABLE_2000();
1933 p_COL80 := JTF_VARCHAR2_TABLE_2000();
1934 p_COL81 := JTF_VARCHAR2_TABLE_2000();
1935 p_COL82 := JTF_VARCHAR2_TABLE_2000();
1936 p_COL83 := JTF_VARCHAR2_TABLE_2000();
1937 p_COL84 := JTF_VARCHAR2_TABLE_2000();
1938 p_COL85 := JTF_VARCHAR2_TABLE_2000();
1939 p_COL86 := JTF_VARCHAR2_TABLE_2000();
1940 p_COL87 := JTF_VARCHAR2_TABLE_2000();
1941 p_COL88 := JTF_VARCHAR2_TABLE_2000();
1942 p_COL89 := JTF_VARCHAR2_TABLE_2000();
1943 p_COL90 := JTF_VARCHAR2_TABLE_2000();
1944 p_COL91 := JTF_VARCHAR2_TABLE_2000();
1945 p_COL92 := JTF_VARCHAR2_TABLE_2000();
1946 p_COL93 := JTF_VARCHAR2_TABLE_2000();
1947 p_COL94 := JTF_VARCHAR2_TABLE_2000();
1948 p_COL95 := JTF_VARCHAR2_TABLE_2000();
1949 p_COL96 := JTF_VARCHAR2_TABLE_2000();
1950 p_COL97 := JTF_VARCHAR2_TABLE_2000();
1951 p_COL98 := JTF_VARCHAR2_TABLE_2000();
1952 p_COL99 := JTF_VARCHAR2_TABLE_2000();
1953 p_COL100 := JTF_VARCHAR2_TABLE_2000();
1954
1955 l_batch_size := NVL(FND_PROFILE.value('JTF_FM_BATCH_SIZE'),1000);
1956
1957 FOR i IN 1 .. l_batch_size
1958 LOOP
1959 p_line_ids .extend();
1960 p_PARTY_ID.extend();
1961 p_PARTY_NAME.extend();
1962 p_EMAIL_ADDRESS.extend();
1963 p_COL1.extend();
1964 p_COL2.extend();
1965 p_COL3.extend();
1966 p_COL4.extend();
1967 p_COL5.extend();
1968 p_COL6.extend();
1969 p_COL7.extend();
1970 p_COL8.extend();
1971 p_COL9.extend();
1972 p_COL10.extend();
1973 p_COL11.extend();
1974 p_COL12.extend();
1975 p_COL13.extend();
1976 p_COL14.extend();
1977 p_COL15.extend();
1978 p_COL16.extend();
1979 p_COL17.extend();
1980 p_COL18.extend();
1981 p_COL19.extend();
1982 p_COL20.extend();
1983 p_COL21.extend();
1984 p_COL22.extend();
1985 p_COL23.extend();
1986 p_COL24.extend();
1987 p_COL25.extend();
1988 p_COL26.extend();
1989 p_COL27.extend();
1990 p_COL28.extend();
1991 p_COL29.extend();
1992 p_COL30.extend();
1993 p_COL31.extend();
1994 p_COL32.extend();
1995 p_COL33.extend();
1996 p_COL34.extend();
1997 p_COL35.extend();
1998 p_COL36.extend();
1999 p_COL37.extend();
2000 p_COL38.extend();
2001 p_COL39.extend();
2002 p_COL40.extend();
2003 p_COL41.extend();
2004 p_COL42.extend();
2005 p_COL43.extend();
2006 p_COL44.extend();
2007 p_COL45.extend();
2008 p_COL46.extend();
2009 p_COL47.extend();
2010 p_COL48.extend();
2011 p_COL49.extend();
2012 p_COL50.extend();
2013 p_COL51.extend();
2014 p_COL52.extend();
2015 p_COL53.extend();
2016 p_COL54.extend();
2017 p_COL55.extend();
2018 p_COL56.extend();
2019 p_COL57.extend();
2020 p_COL58.extend();
2021 p_COL59.extend();
2022 p_COL60.extend();
2023 p_COL61.extend();
2024 p_COL62.extend();
2025 p_COL63.extend();
2026 p_COL64.extend();
2027 p_COL65.extend();
2028 p_COL66.extend();
2029 p_COL67.extend();
2030 p_COL68.extend();
2031 p_COL69.extend();
2032 p_COL70.extend();
2033 p_COL71.extend();
2034 p_COL72.extend();
2035 p_COL73.extend();
2036 p_COL74.extend();
2037 p_COL75.extend();
2038 p_COL76.extend();
2039 p_COL77.extend();
2040 p_COL78.extend();
2041 p_COL79.extend();
2042 p_COL80.extend();
2043 p_COL81.extend();
2044 p_COL82.extend();
2045 p_COL83.extend();
2046 p_COL84.extend();
2047 p_COL85.extend();
2048 p_COL86.extend();
2049 p_COL87.extend();
2050 p_COL88.extend();
2051 p_COL89.extend();
2052 p_COL90.extend();
2053 p_COL91.extend();
2054 p_COL92.extend();
2055 p_COL93.extend();
2056 p_COL94.extend();
2057 p_COL95.extend();
2058 p_COL96.extend();
2059 p_COL97.extend();
2060 p_COL98.extend();
2061 p_COL99.extend();
2062 p_COL100.extend();
2063 END LOOP;
2064
2065 OPEN c_batch;
2066 FETCH c_batch INTO
2067 l_batch_id ,
2068 l_request_line_id_start ,
2069 l_request_line_id_end;
2070 CLOSE c_batch;
2071
2072 IF l_batch_id IS NOT NULL
2073 THEN
2074 UPDATE jtf_fm_int_request_batches rb
2078 WHERE rb.request_id = p_request_id
2075 SET rb.batch_status = 'INPROGRESS' ,
2076 last_update_date = SYSDATE,
2077 rb.SERVER_INSTANCE_ID = p_instance_id
2079 AND rb.batch_status = 'AVAILABLE'
2080 AND rb.batch_id = l_batch_id;
2081
2082 COMMIT;
2083 END IF;
2084
2085 SELECT
2086 rl.REQUEST_LINE_ID,
2087 rl.PARTY_ID,
2088 rl.PARTY_NAME,
2089 rl.EMAIL_ADDRESS,
2090 rl.COL1,
2091 rl.COL2,
2092 rl.COL3,
2093 rl.COL4,
2094 rl.COL5,
2095 rl.COL6,
2096 rl.COL7,
2097 rl.COL8,
2098 rl.COL9,
2099 rl.COL10,
2100 rl.COL11,
2101 rl.COL12,
2102 rl.COL13,
2103 rl.COL14,
2104 rl.COL15,
2105 rl.COL16,
2106 rl.COL17,
2107 rl.COL18,
2108 rl.COL19,
2109 rl.COL20,
2110 rl.COL21,
2111 rl.COL22,
2112 rl.COL23,
2113 rl.COL24,
2114 rl.COL25,
2115 rl.COL26,
2116 rl.COL27,
2117 rl.COL28,
2118 rl.COL29,
2119 rl.COL30,
2120 rl.COL31,
2121 rl.COL32,
2122 rl.COL33,
2123 rl.COL34,
2124 rl.COL35,
2125 rl.COL36,
2126 rl.COL37,
2127 rl.COL38,
2128 rl.COL39,
2129 rl.COL40,
2130 rl.COL41,
2131 rl.COL42,
2132 rl.COL43,
2133 rl.COL44,
2134 rl.COL45,
2135 rl.COL46,
2136 rl.COL47,
2137 rl.COL48,
2138 rl.COL49,
2139 rl.COL50,
2140 rl.COL51,
2141 rl.COL52,
2142 rl.COL53,
2143 rl.COL54,
2144 rl.COL55,
2145 rl.COL56,
2146 rl.COL57,
2147 rl.COL58,
2148 rl.COL59,
2149 rl.COL60,
2150 rl.COL61,
2151 rl.COL62,
2152 rl.COL63,
2153 rl.COL64,
2154 rl.COL65,
2155 rl.COL66,
2156 rl.COL67,
2157 rl.COL68,
2158 rl.COL69,
2159 rl.COL70,
2160 rl.COL71,
2161 rl.COL72,
2162 rl.COL73,
2163 rl.COL74,
2164 rl.COL75,
2165 rl.COL76,
2166 rl.COL77,
2167 rl.COL78,
2168 rl.COL79,
2169 rl.COL80,
2170 rl.COL81,
2171 rl.COL82,
2172 rl.COL83,
2173 rl.COL84,
2174 rl.COL85,
2175 rl.COL86,
2176 rl.COL87,
2177 rl.COL88,
2178 rl.COL89,
2179 rl.COL90,
2180 rl.COL91,
2181 rl.COL92,
2182 rl.COL93,
2183 rl.COL94,
2184 rl.COL95,
2185 rl.COL96,
2186 rl.COL97,
2187 rl.COL98,
2188 rl.COL99,
2189 rl.COL100
2190 BULK COLLECT INTO
2191 p_line_ids,
2192 p_party_id,
2193 p_party_name,
2194 p_email_address,
2195 p_COL1,
2196 p_COL2,
2197 p_COL3,
2198 p_COL4,
2199 p_COL5,
2200 p_COL6,
2201 p_COL7,
2202 p_COL8,
2203 p_COL9,
2204 p_COL10,
2205 p_COL11,
2206 p_COL12,
2207 p_COL13,
2208 p_COL14,
2209 p_COL15,
2210 p_COL16,
2211 p_COL17,
2212 p_COL18,
2213 p_COL19,
2214 p_COL20,
2215 p_COL21,
2216 p_COL22,
2217 p_COL23,
2218 p_COL24,
2219 p_COL25,
2220 p_COL26,
2221 p_COL27,
2222 p_COL28,
2223 p_COL29,
2224 p_COL30,
2225 p_COL31,
2226 p_COL32,
2227 p_COL33,
2228 p_COL34,
2229 p_COL35,
2230 p_COL36,
2231 p_COL37,
2232 p_COL38,
2233 p_COL39,
2234 p_COL40,
2235 p_COL41,
2236 p_COL42,
2237 p_COL43,
2238 p_COL44,
2239 p_COL45,
2240 p_COL46,
2241 p_COL47,
2242 p_COL48,
2243 p_COL49,
2244 p_COL50,
2245 p_COL51,
2246 p_COL52,
2247 p_COL53,
2248 p_COL54,
2249 p_COL55,
2250 p_COL56,
2251 p_COL57,
2252 p_COL58,
2253 p_COL59,
2254 p_COL60,
2255 p_COL61,
2256 p_COL62,
2257 p_COL63,
2258 p_COL64,
2259 p_COL65,
2260 p_COL66,
2261 p_COL67,
2262 p_COL68,
2263 p_COL69,
2264 p_COL70,
2265 p_COL71,
2266 p_COL72,
2267 p_COL73,
2268 p_COL74,
2269 p_COL75,
2270 p_COL76,
2271 p_COL77,
2272 p_COL78,
2273 p_COL79,
2274 p_COL80,
2275 p_COL81,
2276 p_COL82,
2277 p_COL83,
2278 p_COL84,
2279 p_COL85,
2280 p_COL86,
2281 p_COL87,
2282 p_COL88,
2283 p_COL89,
2284 p_COL90,
2285 p_COL91,
2286 p_COL92,
2287 p_COL93,
2288 p_COL94,
2289 p_COL95,
2290 p_COL96,
2291 p_COL97,
2292 p_COL98,
2293 p_COL99,
2294 p_COL100
2295 FROM jtf_fm_int_request_lines rl
2296 WHERE rl.request_id = p_request_id
2297 AND rl.request_line_id BETWEEN l_request_line_id_start AND l_request_line_id_end ;
2298
2299 x_no_of_rows := SQL%ROWCOUNT;
2300
2301
2302 COMMIT;
2303
2304 x_return_status := 'S';
2305
2306 FND_MSG_PUB.Count_AND_Get
2307 ( p_count => x_msg_count,
2308 p_data => x_msg_data,
2309 p_encoded => FND_API.G_FALSE );
2310
2311 EXCEPTION
2312 WHEN FND_API.G_EXC_ERROR
2313 THEN
2314 x_return_status := FND_API.g_ret_sts_error ;
2315 FND_MSG_PUB.Count_AND_Get
2316 ( p_count => x_msg_count,
2317 p_data => x_msg_data,
2318 p_encoded => FND_API.G_FALSE
2319 );
2320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2321 THEN
2322 x_return_status := FND_API.g_ret_sts_unexp_error ;
2323 FND_MSG_PUB.Count_AND_Get
2324 ( p_count => x_msg_count,
2325 p_data => x_msg_data,
2326 p_encoded => FND_API.G_FALSE
2327 );
2328 WHEN OTHERS
2329 THEN
2330 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
2331 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2332 THEN
2333 NULL;
2334 END IF;
2335 FND_MSG_PUB.Count_AND_Get
2336 ( p_count => x_msg_count,
2337 p_data => x_msg_data,
2338 p_encoded => FND_API.G_FALSE
2339 );
2340 END get_next_batch;
2341
2342
2343 /*---------------------------------------------------------------------------------*
2344 | Procedure Name : MOVE_REQUEST |
2345 | |
2346 | Purpose : Selects from jtf_fm_int_request_lines , and |
2347 | - Inserts into jtf_fm_processed table |
2348 | - Inserts into jtf_fm_content_failures table |
2349 | - Drops partition for jtf_fm_int_request_lines |
2350 | |
2351 *---------------------------------------------------------------------------------*/
2352 PROCEDURE move_request( p_request_id NUMBER
2353 , x_log_interaction OUT NOCOPY VARCHAR2
2354 , x_return_status OUT NOCOPY VARCHAR2
2355 , x_msg_count OUT NOCOPY NUMBER
2356 , x_msg_data OUT NOCOPY VARCHAR2
2357 ) IS
2358 CURSOR c_history IS
2359 SELECT USER_HISTORY
2360 FROM jtf_fm_int_request_header
2361 WHERE request_id = p_request_id ;
2362 l_errbuf VARCHAR2(32767);
2363 l_retcode VARCHAR2(1);
2364 BEGIN
2365 OPEN c_history;
2366 FETCH c_history INTO x_log_interaction;
2367 CLOSE c_history;
2368
2369 INSERT INTO jtf_fm_processed
2370 (
2371 request_id ,
2372 job ,
2373 party_id ,
2374 party_name ,
2375 email_address ,
2376 outcome_code ,
2377 created_by ,
2378 creation_date ,
2379 last_updated_by ,
2380 last_update_date ,
2381 last_update_login ,
2382 email_status ,
2383 col1 ,
2384 col2 ,
2385 col3 ,
2386 col4 ,
2387 col5 ,
2388 col6 ,
2389 col7 ,
2390 col8 ,
2391 col9 ,
2392 col10 ,
2393 col11 ,
2394 col12 ,
2395 col13 ,
2396 col14 ,
2397 col15 ,
2398 col16 ,
2399 col17 ,
2400 col18 ,
2401 col19 ,
2402 col20 ,
2403 col21 ,
2404 col22 ,
2405 col23 ,
2406 col24 ,
2407 col25 ,
2408 col26 ,
2409 col27 ,
2410 col28 ,
2411 col29 ,
2412 col30 ,
2413 col31 ,
2414 col32 ,
2415 col33 ,
2416 col34 ,
2417 col35 ,
2418 col36 ,
2419 col37 ,
2420 col38 ,
2421 col39 ,
2422 col40 ,
2423 col41 ,
2424 col42 ,
2425 col43 ,
2426 col44 ,
2427 col45 ,
2428 col46 ,
2429 col47 ,
2430 col48 ,
2431 col49 ,
2432 col50 ,
2433 col51 ,
2434 col52 ,
2435 col53 ,
2436 col54 ,
2437 col55 ,
2438 col56 ,
2439 col57 ,
2440 col58 ,
2441 col59 ,
2442 col60 ,
2443 col61 ,
2444 col62 ,
2445 col63 ,
2446 col64 ,
2447 col65 ,
2448 col66 ,
2449 col67 ,
2450 col68 ,
2451 col69 ,
2452 col70 ,
2453 col71 ,
2454 col72 ,
2455 col73 ,
2459 col77 ,
2456 col74 ,
2457 col75 ,
2458 col76 ,
2460 col78 ,
2461 col79 ,
2462 col80 ,
2463 col81 ,
2464 col82 ,
2465 col83 ,
2466 col84 ,
2467 col85 ,
2468 col86 ,
2469 col87 ,
2470 col88 ,
2471 col89 ,
2472 col90 ,
2473 col91 ,
2474 col92 ,
2475 col93 ,
2476 col94 ,
2477 col95 ,
2478 col96 ,
2479 col97 ,
2480 col98 ,
2481 col99 ,
2482 col100 ,
2483 partition_id )
2484 SELECT
2485 p_request_id,
2486 rl.request_line_id,
2487 rl.party_id ,
2488 rl.party_name ,
2489 rl.email_address ,
2490 DECODE(enabled_flag, 'N','FAILURE',
2491 DECODE(email_status,'NOTDELIVERED','FAILURE','SUCCESS')),
2492 1,
2493 SYSDATE,
2494 1,
2495 SYSDATE,
2496 1,
2497 DECODE(enabled_flag, 'N','NOT_SENT',
2498 DECODE(email_status,'NOTDELIVERED','NOT_SENT','SENT')),
2499 rl.col1 ,
2500 rl.col2 ,
2501 rl.col3 ,
2502 rl.col4 ,
2503 rl.col5 ,
2504 rl.col6 ,
2505 rl.col7 ,
2506 rl.col8 ,
2507 rl.col9 ,
2508 rl.col10 ,
2509 rl.col11 ,
2510 rl.col12 ,
2511 rl.col13 ,
2512 rl.col14 ,
2513 rl.col15 ,
2514 rl.col16 ,
2515 rl.col17 ,
2516 rl.col18 ,
2517 rl.col19 ,
2518 rl.col20 ,
2519 rl.col21 ,
2520 rl.col22 ,
2521 rl.col23 ,
2522 rl.col24 ,
2523 rl.col25 ,
2524 rl.col26 ,
2525 rl.col27 ,
2526 rl.col28 ,
2527 rl.col29 ,
2528 rl.col30 ,
2529 rl.col31 ,
2530 rl.col32 ,
2531 rl.col33 ,
2532 rl.col34 ,
2533 rl.col35 ,
2534 rl.col36 ,
2535 rl.col37 ,
2536 rl.col38 ,
2537 rl.col39 ,
2538 rl.col40 ,
2539 rl.col41 ,
2540 rl.col42 ,
2541 rl.col43 ,
2542 rl.col44 ,
2543 rl.col45 ,
2544 rl.col46 ,
2545 rl.col47 ,
2546 rl.col48 ,
2547 rl.col49 ,
2548 rl.col50 ,
2549 rl.col51 ,
2550 rl.col52 ,
2551 rl.col53 ,
2552 rl.col54 ,
2553 rl.col55 ,
2554 rl.col56 ,
2555 rl.col57 ,
2556 rl.col58 ,
2557 rl.col59 ,
2558 rl.col60 ,
2559 rl.col61 ,
2560 rl.col62 ,
2561 rl.col63 ,
2562 rl.col64 ,
2563 rl.col65 ,
2564 rl.col66 ,
2565 rl.col67 ,
2566 rl.col68 ,
2567 rl.col69 ,
2568 rl.col70 ,
2569 rl.col71 ,
2570 rl.col72 ,
2571 rl.col73 ,
2572 rl.col74 ,
2573 rl.col75 ,
2574 rl.col76 ,
2575 rl.col77 ,
2576 rl.col78 ,
2577 rl.col79 ,
2578 rl.col80 ,
2579 rl.col81 ,
2580 rl.col82 ,
2581 rl.col83 ,
2582 rl.col84 ,
2583 rl.col85 ,
2584 rl.col86 ,
2585 rl.col87 ,
2586 rl.col88 ,
2587 rl.col89 ,
2588 rl.col90 ,
2589 rl.col91 ,
2590 rl.col92 ,
2591 rl.col93 ,
2592 rl.col94 ,
2593 rl.col95 ,
2594 rl.col96 ,
2595 rl.col97 ,
2596 rl.col98 ,
2597 rl.col99 ,
2598 rl.col100 ,
2599 rl.partition_id
2600 FROM jtf_fm_int_request_lines rl
2601 WHERE rl.request_id = p_request_id;
2602
2603 COMMIT;
2604
2605 INSERT INTO jtf_fm_content_failures
2606 (
2607 request_id,
2608 content_number,
2609 job,
2610 media_type,
2611 address,
2612 failure,
2613 created_by,
2614 creation_date,
2615 last_updated_by,
2616 last_update_date,
2617 last_update_login
2618 )
2619 SELECT
2620 p_request_id,
2621 0,
2622 rl.request_line_id,
2623 'EMAIL',
2624 rl.email_address,
2625 DECODE(rl.enabled_flag, 'N',DECODE (rl.contact_preference_flag,
2626 'N','DO_NOT_CONTACT',
2627 'MALFORMED'),'FAILURE'),
2628 1,
2629 SYSDATE,
2630 1,
2631 SYSDATE,
2632 1
2633 FROM jtf_fm_int_request_lines rl
2634 WHERE rl.request_id = p_request_id
2635 AND (rl.enabled_flag = 'N'
2636 OR rl.email_status = 'NOTDELIVERED' );
2637
2638 COMMIT;
2639
2640 unlock_partition ( x_errbuf => l_errbuf
2641 , x_retcode => l_retcode
2642 , p_request_id => p_request_id
2643 );
2644
2645
2646 x_return_status := 'S';
2647 x_msg_count := 0;
2648 x_msg_data := '';
2649 END move_request;
2650
2651 /*---------------------------------------------------------------------------------*
2652 | Procedure Name : CLEAN_STALLED_REQUEST |
2653 | |
2654 | Purpose : Selects from jtf_fm_int_request_lines , and |
2655 | - Truncates partition for jtf_fm_int_request_lines |
2656 | - Deletes from jtf_fm_int_request_batches |
2657 | - Deletes from jtf_fm_int_request_header |
2658 | |
2659 *---------------------------------------------------------------------------------*/
2660 PROCEDURE clean_stalled_request ( p_request_id IN NUMBER
2661 , x_return_status OUT NOCOPY VARCHAR2
2662 ) IS
2663 l_lines_count NUMBER;
2664 lc_status CONSTANT VARCHAR2(10) := 'AVAILABLE';
2665 l_partition_name VARCHAR2(50);
2666 BEGIN
2667 --Getting the count from lines
2668 SELECT COUNT(ROWID)
2669 INTO l_lines_count
2670 FROM jtf_fm_int_request_lines
2671 WHERE request_id = p_request_id
2672 AND email_status = lc_status
2673 ;
2674
2675 --Check to see if record exist
2676 IF (l_lines_count > 0)
2677 THEN
2678 SELECT partition_name
2679 INTO l_partition_name
2680 FROM jtf_fm_partition_x_request
2681 WHERE request_id = p_request_id;
2682
2683 EXECUTE IMMEDIATE 'ALTER TABLE jtf_fm_int_request_lines TRUNCATE PARTITION '|| l_partition_name;
2684 END IF;
2685
2686 --Deleting from batches table
2687 DELETE
2688 FROM jtf_fm_int_request_batches
2689 WHERE request_id = p_request_id
2690 AND batch_status = lc_status
2691 ;
2692
2693 --Deleting from header table
2694 DELETE
2695 FROM jtf_fm_int_request_header
2696 WHERE request_id = p_request_id
2697 ;
2698
2699 COMMIT;
2700
2701 x_return_status := FND_API.G_RET_STS_SUCCESS;
2702
2703 EXCEPTION
2704 WHEN OTHERS
2705 THEN
2706 x_return_status := FND_API.G_RET_STS_ERROR;
2707 END clean_stalled_request;
2708
2709 PROCEDURE get_next_partition ( x_errbuf OUT NOCOPY VARCHAR2
2710 , x_retcode OUT NOCOPY VARCHAR2
2711 , p_request_id IN NUMBER
2712 , x_partition_id OUT NOCOPY NUMBER) IS
2713 l_partition_id NUMBER;
2714 l_request_id NUMBER;
2715 l_errbuf VARCHAR2(32767);
2716 l_retcode VARCHAR2(1);
2717 e_partition_not_found EXCEPTION;
2718 --Move_Request API
2719 l_log_interaction VARCHAR2(100);
2720 l_return_status VARCHAR2(100);
2721 l_msg_count NUMBER;
2722 l_msg_data VARCHAR2(100);
2723 CURSOR c_partition IS
2724 SELECT partition_id
2725 FROM jtf_fm_partition_x_request
2726 WHERE partition_id = ( SELECT MIN(partition_id)
2727 FROM jtf_fm_partition_x_request
2728 WHERE request_id IS NULL
2729 )
2730 FOR UPDATE;
2731 BEGIN
2732 OPEN c_partition;
2733 FETCH c_partition INTO l_partition_id;
2734 CLOSE c_partition;
2735
2736 IF ( l_partition_id IS NOT NULL)
2737 THEN
2738 lock_partition ( x_errbuf => l_errbuf
2739 , x_retcode => l_retcode
2740 , p_request_id => p_request_id
2741 , p_partition_id => l_partition_id
2742 );
2743 ELSIF ( l_partition_id IS NULL)
2744 THEN
2745 SELECT partition_id, request_id
2746 INTO l_partition_id, l_request_id
2747 FROM ( SELECT part.partition_id, part.request_id
2748 FROM jtf_fm_partition_x_request part, jtf_fm_request_history_all history
2749 WHERE part.request_id IS NOT NULL
2750 AND part.request_id = history.hist_req_id
2751 AND history.outcome_code NOT IN ('IN_PROGRESS', 'PAUSED')
2752 ORDER BY request_id ASC )
2753 WHERE ROWNUM < 2
2754 ;
2755
2756 move_request ( p_request_id => l_request_id
2757 , x_log_interaction => l_log_interaction
2758 , x_return_status => l_return_status
2759 , x_msg_count => l_msg_count
2760 , x_msg_data => l_msg_data
2761 );
2762
2763 lock_partition ( x_errbuf => l_errbuf
2764 , x_retcode => l_retcode
2765 , p_request_id => p_request_id
2766 , p_partition_id => l_partition_id
2767 );
2768 END IF;
2769
2770 IF ( l_retcode = 'S')
2771 THEN
2772 x_partition_id := l_partition_id ;
2773 x_retcode := 'S';
2774 ELSE
2778 EXCEPTION
2775 RAISE e_partition_not_found;
2776 END IF;
2777
2779 WHEN e_partition_not_found
2780 THEN
2781 x_errbuf := SQLERRM;
2782 x_retcode := FND_API.g_ret_sts_unexp_error ;
2783 WHEN OTHERS
2784 THEN
2785 x_errbuf := SQLERRM;
2786 x_retcode := FND_API.g_ret_sts_unexp_error ;
2787 END get_next_partition;
2788
2789 PROCEDURE lock_partition ( x_errbuf OUT NOCOPY VARCHAR2
2790 , x_retcode OUT NOCOPY VARCHAR2
2791 , p_request_id IN NUMBER
2792 , p_partition_id IN NUMBER
2793 ) IS
2794 BEGIN
2795 UPDATE jtf_fm_partition_x_request
2796 SET request_id = p_request_id
2797 WHERE partition_id = p_partition_id
2798 AND request_id IS NULL
2799 ;
2800
2801 COMMIT;
2802
2803 x_retcode := 'S';
2804
2805 EXCEPTION
2806 WHEN OTHERS
2807 THEN
2808 x_errbuf := SQLERRM;
2809 x_retcode := FND_API.g_ret_sts_unexp_error ;
2810 END lock_partition;
2811
2812
2813 PROCEDURE unlock_partition ( x_errbuf OUT NOCOPY VARCHAR2
2814 , x_retcode OUT NOCOPY VARCHAR2
2815 , p_request_id IN NUMBER
2816 ) IS
2817 l_partition_name jtf_fm_partition_x_request.partition_name%TYPE;
2818 BEGIN
2819 UPDATE jtf_fm_partition_x_request
2820 SET request_id = NULL
2821 WHERE request_id = p_request_id
2822 RETURNING partition_name INTO l_partition_name
2823 ;
2824
2825 EXECUTE IMMEDIATE 'ALTER TABLE jtf_fm_int_request_lines TRUNCATE PARTITION '|| l_partition_name;
2826
2827 x_retcode := 'S';
2828
2829 EXCEPTION
2830 WHEN OTHERS
2831 THEN
2832 x_errbuf := SQLERRM;
2833 x_retcode := FND_API.g_ret_sts_unexp_error ;
2834 END unlock_partition;
2835
2836
2837 END JTF_FM_INT_REQUEST_PKG;