1 PACKAGE BODY QA_SEQUENCE_API AS
2 /* $Header: qltseqb.pls 120.13.12020000.3 2013/01/07 13:48:06 ntungare ship $ */
3
4 -- Bug 5368983. Intoroducing debug logging in this API
5 g_module_name CONSTANT VARCHAR2(60):= 'qa.plsql.qa_sequence_api';
6
7 g_curr_plan_id NUMBER;
8 g_curr_parent_plan_id NUMBER;
9
10 --
11 -- bug 5228667
12 -- New Variable to detect if the ERES Txn
13 -- has been resubmitted
14 -- ntungare Thu Aug 17 03:26:40 PDT 2006
15 --
16 g_eres_resubmit_flg BOOLEAN := FALSE;
17
18 --
19 -- bug 5955808
20 -- New variable to indicate a Mobile Transaction
21 -- ntungare Mon Jul 23 11:31:09 PDT 2007
22 --
23 g_mobile BOOLEAN := FALSE;
24
25 g_curr_plan_seq_char_ids ID_TABLE;
26 g_parent_plan_seq_char_ids ID_TABLE;
27 g_parent_plan_seq_nos ID_TABLE;
28
29 --Bug 5114865
30 -- Global array to hold the Seq Type elements
31 -- ntungare Sun Apr 9 23:40:46 PDT 2006
32 g_true_seq_gen_recids ID_TABLE;
33
34 ParentChild_Tab QA_PARENT_CHILD_PKG.ParentChildTabTyp;
35
36 -- Bug 3160651. Code change start. rponnusa Thu Sep 25 02:24:28 PDT 2003
37
38 Type MessageRecord IS RECORD (
39 plan_name VARCHAR2(40),
40 element_prompt VARCHAR2(40),
41 sequence_value VARCHAR2(40));
42
43 TYPE MessageArray IS TABLE OF MessageRecord INDEX BY BINARY_INTEGER;
44 TYPE PROMPT_TABLE IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;
45
46 g_message_array MessageArray;
47 g_prompt_tab PROMPT_TABLE;
48
49 PROCEDURE get_all_char_prompt_info(p_plan_id NUMBER);
50
51 PROCEDURE populate_message_array(p_char_id NUMBER,
52 p_seq_value VARCHAR2,
53 p_plan_name VARCHAR2);
54
55 -- Bug 3160651. Code change end. rponnusa Thu Sep 25 02:24:28 PDT 2003
56
57
58 FUNCTION get_all_record_info(p_collection_id NUMBER,
59 p_txn_header_id NUMBER) RETURN NUMBER;
60
61 -- Bug 5335509. SHKALYAN 15-Jun-2006
62 -- commenting this declaration out since this function
63 -- needs to be called from external programs so, moved to specs.
64 /*
65 FUNCTION get_sequence_default_value RETURN VARCHAR2;
66 */
67
68 FUNCTION get_eres_profile RETURN BOOLEAN;
69
70 --
71 -- bug 5228667
72 -- Added the parameters plan id and occurrence as
73 -- they would help in uniquely identifying a record
74 -- in the qa_seq_audit_history table. This is important
75 -- in case of a SR Txn wherein an EQR can be done while
76 -- updating an SR wherein the same Collection id is reused
77 -- This will ensure that the resubmission is not confused
78 -- with any audit recs already existing for a collection id
79 -- ntungare Thu Aug 17 03:31:39 PDT 2006
80 --
81 FUNCTION eres_resubmit(p_txn_header_id NUMBER,
82 p_collection_id NUMBER,
83 p_plan_id NUMBER DEFAULT NULL,
84 p_occurrence NUMBER DEFAULT NULL) RETURN BOOLEAN;
85
86 PROCEDURE find_update_seq_in_collection(p_cur_record_indicator NUMBER,
87 p_seq_position NUMBER,
88 p_action NUMBER,
89 p_seq_value IN OUT NOCOPY VARCHAR2);
90
91 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
92 -- new procedure added
93
94 -- Bug 5368983
95 -- Added a new flag. Provided a default value so it
96 -- doesnt not impact existing calls
97 -- saugupta Wed, 30 Aug 2006 06:03:10 -0700 PDT
98 PROCEDURE gen_seq_for_currec_commit(p_plan_id NUMBER,
99 p_collection_id NUMBER,
100 p_occurrence NUMBER,
101 p_parent_plan_id NUMBER,
102 p_parent_collection_id NUMBER,
103 p_parent_occurrence NUMBER,
104 p_oa_txnint BOOLEAN DEFAULT NULL);
105
106 -- Bug 5368983
107 -- Added a new flag. Provided a default value so it
108 -- doesnt not impact existing calls
109 -- saugupta Wed, 30 Aug 2006 05:18:01 -0700 PDT
110 PROCEDURE generate_seq_for_currec(p_plan_id NUMBER,
111 p_collection_id NUMBER,
112 p_occurrence NUMBER,
113 p_parent_plan_id NUMBER,
114 p_parent_collection_id NUMBER,
115 p_parent_occurrence NUMBER,
116 p_oa_txnint BOOLEAN DEFAULT NULL);
117
118
119 PROCEDURE get_seq_value_for_pc_comb(p_plan_id NUMBER,
120 p_collection_id NUMBER,
121 p_occurrence NUMBER,
122 p_cur_rec_indicator NUMBER,
123 p_parent_plan_id NUMBER,
124 p_parent_collection_id NUMBER,
125 p_parent_occurrence NUMBER,
126 p_parent_rec_indicator NUMBER);
127
128 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
129 -- Renamed procedure generate_seq_for_pc_nocommit to
130 -- generate_seq_for_pc
131
132 -- Bug 5368983
133 -- Added a new flag. Provided a default value so it
134 -- doesnt not impact existing calls
135 -- saugupta Wed, 30 Aug 2006 05:18:01 -0700 PDT
136
137 PROCEDURE generate_seq_for_pc(p_plan_id NUMBER,
138 p_collection_id NUMBER,
139 p_txn_header_id NUMBER,
140 p_oa_txnint BOOLEAN DEFAULT NULL);
141
142 PROCEDURE get_plan_seq_ele_setup(p_plan_id NUMBER,
143 p_parent_plan_id NUMBER);
144
145 PROCEDURE update_record(p_total_rec_count NUMBER);
146
147 PROCEDURE get_all_rec_info_for_audit(p_plan_ids DBMS_SQL.number_table,
148 p_collection_ids DBMS_SQL.number_table,
149 p_occurrences DBMS_SQL.number_table);
150
151 PROCEDURE audit_sequence_for_allchild(p_plan_id NUMBER,
152 p_collection_id NUMBER,
153 p_occurrence NUMBER);
154
155 PROCEDURE audit_sequence_for_currec(p_plan_id NUMBER,
156 p_collection_id NUMBER,
157 p_occurrence NUMBER,
158 p_parent_plan_id NUMBER,
159 p_parent_collection_id NUMBER,
160 p_parent_occurrence NUMBER);
161
162 PROCEDURE audit_seq_for_eres(p_char_id NUMBER,
163 p_seq_value VARCHAR2,
164 p_cur_rec_indicator NUMBER);
165
166 -- Bug 5368983
167 -- Added a new method for sequence auditing for OA Txn Integ
168 -- saugupta Wed, 30 Aug 2006 05:22:51 -0700 PDT
169 --
170 -- Bug 5955808
171 -- Added a new parameter to take in the Module name
172 -- to be set at the time of auditing the sequences
173 -- ntungare Thu Jul 26 02:55:50 PDT 2007
174 --
175 PROCEDURE audit_seq_for_txnint(p_char_id NUMBER,
176 p_seq_value VARCHAR2,
177 p_cur_rec_indicator NUMBER,
178 p_module VARCHAR2 DEFAULT 'OATXNINT');
179 -- Get_Nextval function derives the next sequence number and updates
180 -- qa_chars with the next sequence number and commits it.
181 -- kabalakr
182
183
184 FUNCTION GET_NEXTVAL(p_char_id NUMBER) RETURN NUMBER IS
185
186 PRAGMA AUTONOMOUS_TRANSACTION;
187
188 -- l_seq_incr NUMBER := 0;
189 l_curr_val NUMBER := 0;
190 -- l_next_val NUMBER := 0;
191
192 --
193 -- Bug 5118745. Locking issue as reported by customer P1 5060289.
194 -- In order to keep sequence generation as efficient as possible
195 -- this procedure is reduced from 2 SQLs with a FOR UPDATE lock
196 -- to one atomic SQL of equivalent semantics. Locking is no longer
197 -- required because we reduced 2 SQLs with 1 preventing database
198 -- activities from happening in between.
199 -- bso Tue Mar 28 12:35:36 PST 2006
200 --
201 -- CURSOR c IS SELECT sequence_nextval, sequence_increment
202 -- FROM qa_chars
203 -- WHERE char_id = p_char_id FOR UPDATE;
204 --
205
206 BEGIN
207
208 -- select the next sequnce from qa_chars.
209 --
210 -- OPEN c;
211 -- FETCH c INTO l_curr_val, l_seq_incr;
212 -- IF (c%NOTFOUND) THEN
213 -- NULL;
214 -- END IF;
215 -- CLOSE c;
216 --
217 -- increment the sequence.
218 -- l_next_val := l_curr_val+l_seq_incr;
219 --
220 -- update qa_chars with new sequence value.
221 -- UPDATE qa_chars
222 -- SET sequence_nextval = l_next_val
223 -- WHERE char_id = p_char_id;
224
225 -- Basically, we increase the sequence_nextval column
226 -- by sequence_increment and return the sum in one shot.
227 -- bso
228 --
229 -- Bug 5233797. The previous RETURNING clause was
230 -- RETURNING sequence_nextval + sequence_increment
231 -- This is incorrect as sequence_nextval would have
232 -- taken the new incremented value. The semantics
233 -- of this procedure is to return the old value before
234 -- increment. So change + to -
235 -- bso Thu May 18 13:36:06 PDT 2006
236 --
237
238 UPDATE qa_chars
239 SET sequence_nextval = sequence_nextval + sequence_increment
240 WHERE char_id = p_char_id
241 RETURNING sequence_nextval - sequence_increment
242 INTO l_curr_val;
243
244 COMMIT;
245
246 RETURN l_curr_val;
247
248 END GET_NEXTVAL;
249
250
251
252 -- Get_Next_Seq function gets the next Sequence Number using
253 -- get_nextval function and prototypes the Sequence String into the
254 -- format that user views. It also takes care of the zero padding
255 -- logic.
256 -- kabalakr
257
258 FUNCTION GET_NEXT_SEQ(p_char_id NUMBER, p_commit BOOLEAN) RETURN VARCHAR2 IS
259
260 l_next_seq VARCHAR2(100);
261
262 l_seq_pref VARCHAR2(100);
263 l_seq_suf VARCHAR2(100);
264 l_seq_sep VARCHAR2(1);
265 l_seq_len NUMBER := 0;
266 l_zero_pad NUMBER := 0;
267
268 l_next_val VARCHAR2(100);
269 l_zero VARCHAR2(100);
270 l_len NUMBER := 0;
271
272 i NUMBER;
273
274 CURSOR c IS SELECT sequence_prefix, sequence_suffix, sequence_separator,
275 sequence_length, sequence_zero_pad
276 FROM qa_chars
277 WHERE char_id = p_char_id;
278 BEGIN
279
280 -- Gapless Sequence Proj Start.
281 -- rponnusa Wed Jul 30 04:52:45 PDT 2003
282
283 -- get the next sequence.
284 IF NVL(p_commit,TRUE) = FALSE THEN
285 l_next_val := to_char(get_nextval_nocommit(p_char_id));
286 ELSE
287 l_next_val := to_char(GET_NEXTVAL(p_char_id));
288 END IF;
289
290 -- Gapless Sequence Proj End
291
292 -- Get all the sequence data.
293 OPEN c;
294 FETCH c INTO l_seq_pref, l_seq_suf, l_seq_sep, l_seq_len, l_zero_pad;
295
296 /*
297 -- Gapless Sequence Proj
298 -- comment out this code as it does not needed.
299
300 IF (c%NOTFOUND) THEN
301 NULL;
302 END IF;
303 */
304
305 CLOSE c;
306
307 -- If zero_pad the sequence if the flag is set.
308 IF (l_zero_pad = 1) THEN
309 l_len := LENGTH(l_next_val);
310 l_len := l_seq_len - l_len ;
311
312 FOR i in 1..l_len LOOP
313 l_zero := l_zero || '0' ;
314 END LOOP;
315
316 l_next_val := l_zero || l_next_val ;
317
318 END IF;
319
320 -- Make the sequence here.
321 l_next_seq := l_seq_pref || l_seq_sep || l_next_val || l_seq_sep || l_seq_suf ;
322 RETURN l_next_seq;
323
324 END GET_NEXT_SEQ;
325
326 -- Max_Sequence is the Server Side constant. Its the maximum
327 -- number of sequences that can be defined.
328 -- kabalakr
329
330 FUNCTION MAX_SEQUENCE RETURN NUMBER IS
331
332 l_max_seq_num NUMBER := 15;
333
334 BEGIN
335
336 RETURN l_max_seq_num;
337
338 END MAX_SEQUENCE;
339
340 -- Bug 2548710. Added following procedure
341 -- rponnusa Mon Nov 18 03:49:15 PST 2002
342
343 PROCEDURE FILL_SEQ_TABLE (p_char_id IN NUMBER,
344 p_count IN NUMBER,
345 x_seq_table OUT NOCOPY QLTTRAWB.CHAR50_TABLE ) IS
346
347 -- This procedure is called from qlttrawb.plb
348 -- For sequence datatype, we need to generate distinct sequence value for each record.
349 -- First generate p_count distinct seq values, bundle it in seq table
350 -- and send it back to calling procedure.
351 -- p_count is the no. of records processed by the worker.
352 --
353
354 BEGIN
355 -- we need to initialize the collection before actually use it.
356 x_seq_table := QLTTRAWB.CHAR50_TABLE ();
357
358 -- create the number of elements required for the collection.
359 x_seq_table.EXTEND(p_count);
360
361 FOR I IN 1..p_count LOOP
362 x_seq_table(I) := QA_SEQUENCE_API.GET_NEXT_SEQ(p_char_id);
363 END LOOP;
364
365 END FILL_SEQ_TABLE;
366
367 -- Gapless Sequence Proj Start.
368 -- rponnusa Wed Jul 30 04:52:45 PDT 200
369
370 FUNCTION get_nextval_nocommit(p_char_id NUMBER) RETURN NUMBER IS
371
372 -- This is function is same as fn GET_NEXTVAL except that
373 -- autonomous txn is not used here.
374
375 l_curr_val NUMBER := 0;
376
377 BEGIN
378
379 --
380 -- Many cleanup done. See comments in get_nextval.
381 -- bso Tue Mar 28 13:26:01 PST 2006
382 --
383 -- Bug 5233797. The previous RETURNING clause was
384 -- RETURNING sequence_nextval + sequence_increment
385 -- This is incorrect as sequence_nextval would have
386 -- taken the new incremented value. The semantics
387 -- of this procedure is to return the old value before
388 -- increment. So change + to -
389 -- bso Thu May 18 13:36:06 PDT 2006
390 --
391
392 UPDATE qa_chars
393 SET sequence_nextval = sequence_nextval + sequence_increment
394 WHERE char_id = p_char_id
395 RETURNING sequence_nextval - sequence_increment
396 INTO l_curr_val;
397
398 RETURN l_curr_val;
399 END get_nextval_nocommit;
400
401 -- bug 14134055
402 -- New procedure to ensure that the child_txn_header_id in the
403 -- qa_pc_results_realtionship table is synced up with the qa_results
404 -- table so that there is no error in sequence generation.
405 --
406 PROCEDURE sync_child_txn_hdr_id(p_txn_header_id IN NUMBER) IS
407 CURSOR results_cur IS
408 SELECT plan_id,
409 collection_id,
410 occurrence,
411 txn_header_id
412 FROM qa_results
413 WHERE txn_header_id = p_txn_header_id;
414
415 TYPE results_tab_typ IS TABLE OF results_cur%rowtype INDEX BY binary_integer;
416 results_tab results_tab_typ ;
417 BEGIN
418 OPEN results_cur;
419 FETCH results_cur bulk collect INTO results_tab;
420 CLOSE results_cur;
421
422 FOR cntr IN 1..results_tab.COUNT LOOP
423 UPDATE qa_pc_results_relationship
424 SET child_txn_header_id = results_tab(cntr).txn_header_id
425 WHERE child_plan_id = results_tab(cntr).plan_id
426 AND child_collection_id = results_tab(cntr).collection_id
427 AND child_occurrence = results_tab(cntr).occurrence;
428 END LOOP;
429 END sync_child_txn_hdr_id;
430
431 PROCEDURE generate_seq_for_Txn(p_collection_id NUMBER,
432 p_return_status OUT NOCOPY VARCHAR2) IS
433
434 -- In eRecords scanario following things are done.
435
436 -- 1. Sequence will always be generated before the parent txn commited
437 -- in post-forms-commit for the FORM
438 -- 2. While generating sequence, audit information captured with
439 -- source code = 'EDR' and audit_type = 'CREATED' in qa_seq_audit_history table.
440 -- 3. When user Rejects the entry , update qa_seq_audit_history table
441 -- for audit_type = 'REJECTED' , source_id with eRes ID.
442 -- 4. When user Accepts the txn, delete audit information from
443 -- qa_seq_audit_history table
444 -- 5. In case erecords are resubmitted, checking is done qa_seq_audit_history
445 -- for the collection_id,occurrence. If record found, dont call sequence api.
446 -- 6. QA_RESULT_GRP.enable is called when parent txn commited(after eRes).
447 -- Api checks for eRes txn. If eRes then seq. api will not be called since
448 -- seq. values generated already.
449
450
451 -- this cursor finds out number of top level plans for which
452 -- results been entered.
453
454 CURSOR all_plan_cur IS
455 SELECT distinct qr.plan_id
456 FROM qa_results qr
457 WHERE qr.collection_id = p_collection_id AND
458 NOT EXISTS (SELECT 1 FROM qa_pc_results_relationship qprr
459 WHERE qprr.child_plan_id = qr.plan_id AND
460 qprr.child_collection_id = qr.collection_id AND
461 qprr.child_occurrence = qr.occurrence);
462
463 -- use this cursor to make other user wait to get the lock on
464 -- known char_id =1. This is to avoid concurrency problem. For
465 -- Ex. user1, user2 simultanously trying to generate seq value.
466 -- assume user1 got the lock on this table and process it. During
467 -- this time, user2 will wait to acquire the lock (without that
468 -- he cannt proceed). Once user1 completes the task and lock
469 -- is released which can be obtained by user2.
470
471 l_char_id NUMBER;
472 l_row_count NUMBER;
473
474 --
475 -- Bug 5118745. Locking issue as reported by customer P1 5060289.
476 -- This cursor is not required. It is conservatively used to give
477 -- a nice-to-have feature for sequential numbering per collection
478 -- during concurrent EQR.
479 -- bso Tue Mar 28 12:19:04 PST 2006
480 --
481 -- CURSOR c IS
482 -- SELECT char_id
483 -- FROM qa_chars WHERE char_id = 1
484 -- FOR UPDATE;
485
486 BEGIN
487 -- Bug 5118745. Locking issue as reported by customer P1 5060289.
488 -- IF NOT get_eres_profile() THEN
489 -- -- unless user gets the lock on this table he can't get past this cursor.
490 -- -- lock the row only in non eres case.
491 -- OPEN c;
492 -- FETCH c INTO l_char_id;
493 -- CLOSE c;
494 -- END IF;
495
496 -- Initialize return status to success
497 p_return_status := FND_API.G_RET_STS_SUCCESS;
498
499 --
500 -- Bug 5228667
501 -- Commented out this check because although
502 -- in case when an ERES is resubmitted the sequence
503 -- is not to be regenerated, yet the sequence array
504 -- needs to be populated with the values from the
505 -- Audit Hist table where the generated seq would reside
506 -- and this should be used while updating qa_results
507 -- Without this, in case of a resubmission, the sequence
508 -- value won't be copied to QA_RESULTS. This processing
509 -- has been moved to the proc generate_seq_for_pc
510 -- ntungare Thu Aug 17 03:40:02 PDT 2006
511 --
512 /*
513 IF get_eres_profile() AND eres_resubmit(null,p_collection_id) THEN
514
515 -- eRes is enabled and same records are resubmitted.
516 -- dont do any processing.
517 RETURN;
518 END IF;
519 */
520
521 -- initialize fnd message table, this is used by self-service api
522 fnd_msg_pub.initialize;
523
524 -- Pack all the sequence values in the data collection into plsql table
525 l_row_count := get_all_record_info(p_collection_id,null);
526
527 IF l_row_count = 0 THEN
528 RETURN;
529 END IF;
530
531 FOR plan_rec IN all_plan_cur LOOP
532
533 -- Generate seq value or copy seq value from parent to child rec.
534 -- this should be done for all the parent rec and its all child,
535 -- grandchild.. records
536
537 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
538 -- IF condition
539 generate_seq_for_pc(plan_rec.plan_id,
540 p_collection_id,
541 null);
542
543 END LOOP;
544
545 -- Now use bulk update to update all seq. values into qa_results for all the
546 -- records in the data collection
547
548 update_record(l_row_count);
549
550 EXCEPTION
551 WHEN OTHERS THEN
552 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
553 fnd_message.set_name('QA', 'QA_SEQ_GENERATION_ERROR');
554 fnd_msg_pub.add();
555
556 END generate_seq_for_txn;
557
558
559 PROCEDURE generate_seq_for_DDE(p_txn_header_id NUMBER,
560 p_plan_id NUMBER,
561 p_return_status OUT NOCOPY VARCHAR2) IS
562
563 -- In direct data entry mode (eqr/uqr) txn_header_id is unique between saves.
564 -- The generation of seq. should happen for each save. Collection ID
565 -- cannot be used in this case, since it is same between saves.
566 -- This procedure is the entry point for seq. api called from FORM
567
568 -- use this cursor to make other user wait to get the lock on
569 -- known row char_id =1. This is to avoid concurrency problem. For
570 -- Ex. user1, user2 simultanously trying to generate seq value.
571 -- assume user1 got the lock on this table and process it. During
572 -- this time, user2 will wait to acquire the lock (without that
573 -- he cannt proceed). Once user1 completes the task and lock
574 -- is released which can be obtained by user2.
575
576 l_char_id NUMBER;
577 l_row_count NUMBER;
578
579 --
580 -- Bug 5118745. Locking issue as reported by customer P1 5060289.
581 -- This cursor is not required. It is conservatively used to give
582 -- a nice-to-have feature for sequential numbering per collection
583 -- during concurrent EQR.
584 -- bso Tue Mar 28 12:19:04 PST 2006
585 --
586 -- CURSOR c IS
587 -- SELECT char_id
588 -- FROM qa_chars WHERE char_id = 1
589 -- FOR UPDATE;
590
591 BEGIN
592 -- Bug 5118745. Locking issue as reported by customer P1 5060289.
593 -- IF NOT get_eres_profile() THEN
594 -- -- unless user gets the lock on this table he can't get past this cursor.
595 -- -- lock the row only in non eres case.
596 -- OPEN c;
597 -- FETCH c INTO l_char_id;
598 -- CLOSE c;
599 -- END IF;
600
601 -- Initialize return status to success
602 p_return_status := FND_API.G_RET_STS_SUCCESS;
603
604 IF get_eres_profile() AND eres_resubmit(p_txn_header_id,null) THEN
605
606 -- eRes is enabled and same records are resubmitted.
607 -- dont do any processing.
608 RETURN;
609 END IF;
610
611 -- Pack all the sequence values in the data collection into plsql table
612 l_row_count := get_all_record_info(null, p_txn_header_id);
613
614 IF l_row_count = 0 THEN
615 RETURN;
616 END IF;
617
618 -- Generate seq value or copy seq value from parent to child rec.
619 -- this should be done for all the parent rec and its all child,
620 -- grandchild.. records
621
622 -- bug 14134055
623 -- Call the API to sync up the txn header ids in
624 -- qa_pc_results_relationship table
625 --
626 sync_child_txn_hdr_id(p_txn_header_id);
627
628 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
629 -- IF condition
630 generate_seq_for_pc(p_plan_id,
631 null,
632 p_txn_header_id);
633
634 -- Now use bulk update to update all seq. values into qa_results for all the
635 -- records in the data collection
636
637 update_record(l_row_count);
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 -- Bug 3160651. If any error happens, should return error
642
643 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644
645 END generate_seq_for_DDE;
646
647 FUNCTION get_all_record_info(p_collection_id NUMBER,
648 p_txn_header_id NUMBER) RETURN NUMBER IS
649
650 -- Pack all the parent,child,grand child record into the plsql table
651 -- for the given coll id or txn header id.
652
653 CURSOR coll_cur IS
654 SELECT occurrence,collection_id,plan_id,txn_header_id,
655 sequence1,sequence2,sequence3,
656 sequence4,sequence5,sequence6,
657 sequence7,sequence8,sequence9,
658 sequence10,sequence11,sequence12,
659 sequence13,sequence14,sequence15
660 FROM qa_results
661 WHERE collection_id = p_collection_id;
662
663 CURSOR txn_cur IS
664 SELECT occurrence,collection_id,plan_id,txn_header_id,
665 sequence1,sequence2,sequence3,
666 sequence4,sequence5,sequence6,
667 sequence7,sequence8,sequence9,
668 sequence10,sequence11,sequence12,
669 sequence13,sequence14,sequence15
670 FROM qa_results
671 WHERE txn_header_id = p_txn_header_id;
672
673 l_row_count NUMBER := 0;
674
675 BEGIN
676 IF p_collection_id IS NOT NULL THEN
677
678 OPEN coll_cur;
679
680 -- bulk fetch all the records into corresponding plsql tables
681 -- this is called from transaction mode
682
683 FETCH coll_cur BULK COLLECT INTO
684 QLTTRAWB.g_occurrence_tab, QLTTRAWB.g_collection_id_tab,
685 QLTTRAWB.g_plan_id_tab, QLTTRAWB.g_txn_header_id_tab,
686 QLTTRAWB.g_seq_tab1, QLTTRAWB.g_seq_tab2, QLTTRAWB.g_seq_tab3,
687 QLTTRAWB.g_seq_tab4, QLTTRAWB.g_seq_tab5, QLTTRAWB.g_seq_tab6,
688 QLTTRAWB.g_seq_tab7, QLTTRAWB.g_seq_tab8, QLTTRAWB.g_seq_tab9,
689 QLTTRAWB.g_seq_tab10,QLTTRAWB.g_seq_tab11, QLTTRAWB.g_seq_tab12,
690 QLTTRAWB.g_seq_tab13,QLTTRAWB.g_seq_tab14, QLTTRAWB.g_seq_tab15;
691
692 l_row_count := coll_cur%ROWCOUNT;
693 CLOSE coll_cur;
694
695 ELSIF p_txn_header_id IS NOT NULL THEN
696 OPEN txn_cur;
697 -- bulk fetch all the records into corresponding plsql tables
698 -- this is called from direct data entry mode
699
700 FETCH txn_cur BULK COLLECT INTO
701 QLTTRAWB.g_occurrence_tab, QLTTRAWB.g_collection_id_tab,
702 QLTTRAWB.g_plan_id_tab, QLTTRAWB.g_txn_header_id_tab,
703 QLTTRAWB.g_seq_tab1, QLTTRAWB.g_seq_tab2, QLTTRAWB.g_seq_tab3,
704 QLTTRAWB.g_seq_tab4, QLTTRAWB.g_seq_tab5, QLTTRAWB.g_seq_tab6,
705 QLTTRAWB.g_seq_tab7, QLTTRAWB.g_seq_tab8, QLTTRAWB.g_seq_tab9,
706 QLTTRAWB.g_seq_tab10,QLTTRAWB.g_seq_tab11, QLTTRAWB.g_seq_tab12,
707 QLTTRAWB.g_seq_tab13,QLTTRAWB.g_seq_tab14, QLTTRAWB.g_seq_tab15;
708
709 l_row_count := txn_cur%ROWCOUNT;
710 CLOSE txn_cur;
711
712 END IF;
713
714 RETURN l_row_count;
715
716 END get_all_record_info;
717
718 -- Bug 5368983
719 -- Added a new flag. Provided a default value so it
720 -- does not impact existing calls
721 -- saugupta Wed, 30 Aug 2006 05:18:01 -0700 PDT
722 PROCEDURE generate_seq_for_pc(p_plan_id NUMBER,
723 p_collection_id NUMBER,
724 p_txn_header_id NUMBER,
725 p_oa_txnint BOOLEAN DEFAULT NULL) IS
726
727 -- Generate sequence for the parent record and all its child,grand children
728 -- records.
729 --
730 -- bug 7552689
731 -- Sequences are randomly generated if there are multiple records
732 -- entered in the child plan. Hence, added child_occurrence as well
733 -- to ensure proper ordering.
734
735 CURSOR txn_cur IS
736 SELECT 0 child_plan_id,
737 0 child_collection_id,
738 0 child_occurrence,
739 plan_id parent_plan_id,
740 collection_id parent_collection_id,
741 occurrence parent_occurrence,
742 0 levels
743 FROM qa_results qr
744 WHERE qr.plan_id = p_plan_id AND
745 qr.collection_id = p_collection_id
746 UNION ALL
747 SELECT child_plan_id,
748 child_collection_id,
749 child_occurrence,
750 parent_plan_id,
751 parent_collection_id,
752 parent_occurrence,
753 level levels
754 FROM qa_pc_results_relationship r
755 START WITH r.parent_plan_id = p_plan_id AND
756 r.parent_collection_id = p_collection_id
757 CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence
758 ORDER BY levels, parent_occurrence, child_occurrence;
759 --
760 -- Bug 5118745. Locking issue as reported by customer P1 5060289.
761 -- For better user experience, augmented order by with occurrence
762 -- so that user does not see random ordering of sequences when
763 -- entering multiple records in one collection. No change to
764 -- performance as the number of new records per collection is
765 -- limited.
766 -- bso Tue Mar 28 12:59:39 PST 2006
767 --
768
769 -- Bug 3283794. Modified dde_cur to select from
770 -- qa_pc_results_relationship_v. rponnusa Thu Dec 18 21:28:55 PST 2003
771
772 /* CURSOR dde_cur IS
773 SELECT 0 child_plan_id,
774 0 child_collection_id,
775 0 child_occurrence,
776 plan_id parent_plan_id,
777 collection_id parent_collection_id,
778 occurrence parent_occurrence,
779 0 levels
780 FROM qa_results qr
781 WHERE qr.plan_id = p_plan_id AND
782 qr.txn_header_id = p_txn_header_id
783 UNION ALL
784 SELECT child_plan_id,
785 child_collection_id,
786 child_occurrence,
787 parent_plan_id,
788 parent_collection_id,
789 parent_occurrence,
790 level levels
791 FROM qa_pc_results_relationship_v r
792 WHERE r.child_txn_header_id = p_txn_header_id
793 START WITH r.parent_plan_id = p_plan_id
794 CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence
795 ORDER BY levels; */
796
797 -- Bug 3578477. Above commented CURSOR is not compatible with Oracle 8i
798 -- To make it compatible removed the call to view qa_pc_results_relationship_v
799 -- and replaced it with qa_pc_results_relationship table and changed the
800 -- where condition of second select query.
801 -- saugupta Tue, 20 Apr 2004 04:09:30 -0700 PDT
802
803 --
804 -- bug 7552689
805 -- Sequences are randomly generated if there are multiple records
806 -- entered in the child plan. Hence, added child_occurrence as well
807 -- to ensure proper ordering.
808 --
809 CURSOR dde_cur IS
810 -- bug 6450787
811 -- Commenting the following cursor due to
812 -- performance reasons.
813 -- bhsankar Thu Oct 4 04:19:54 PDT 2007
814 /* SELECT 0 child_plan_id,
815 0 child_collection_id,
816 0 child_occurrence,
817 plan_id parent_plan_id,
818 collection_id parent_collection_id,
819 occurrence parent_occurrence,
820 0 levels
821 FROM qa_results qr
822 WHERE qr.plan_id = p_plan_id AND
823 qr.txn_header_id = p_txn_header_id
824 UNION ALL
825 SELECT child_plan_id,
826 child_collection_id,
827 child_occurrence,
828 parent_plan_id,
829 parent_collection_id,
830 parent_occurrence,
831 level levels
832 FROM qa_pc_results_relationship r
833 WHERE p_txn_header_id =
834 (SELECT qr.txn_header_id
835 FROM qa_results qr
836 WHERE qr.plan_id = r.child_plan_id and
837 qr.collection_id = r.child_collection_id and
838 qr.occurrence = r.child_occurrence)
839 START WITH r.parent_plan_id = p_plan_id
840 CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence
841 ORDER BY levels, parent_occurrence; */
842
843 -- bug 6450787
844 -- Modified the cursor to use child_txn_header_id
845 -- to improve the performance.
846 -- bhsankar Thu Oct 4 04:19:54 PDT 2007
847 --
848 --
849 -- bug 7015532
850 -- modified the cursor definition to correct the
851 -- hierarchy initiation criteria. Commented out the
852 -- plan id since in case of a P-C-G scenario the Txn
853 -- Header Id generated for a new Grand Child would not
854 -- be stamped against the topmost parent record.
855 -- ntungare
856 --
857 SELECT 0 child_plan_id,
858 0 child_collection_id,
859 0 child_occurrence,
860 plan_id parent_plan_id,
861 collection_id parent_collection_id,
862 occurrence parent_occurrence,
863 0 levels
864 FROM qa_results qr
865 WHERE qr.plan_id = p_plan_id AND
866 qr.txn_header_id = p_txn_header_id
867 UNION ALL
868 --
869 -- bug 16037863
870 -- With the fix done for bug 14134055, the child txn header id in
871 -- qa_pc_results_relationhsip table will be in sync with that in
872 -- qa_results. So we can use the original form of the SQL that starts
873 -- from a parent plan id and gets all the rows in the hierarchy and
874 -- then picks the one with the specific txn_header_id
875 --
876 SELECT child_plan_id,
877 child_collection_id,
878 child_occurrence,
879 parent_plan_id,
880 parent_collection_id,
881 parent_occurrence,
882 levels
883 FROM (
884 SELECT child_plan_id,
885 child_collection_id,
886 child_occurrence,
887 parent_plan_id,
888 parent_collection_id,
889 parent_occurrence,
890 level levels,
891 child_txn_header_id
892 FROM qa_pc_results_relationship r
893 START WITH r.parent_plan_id = p_plan_id
894 CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence)
895 WHERE child_txn_header_id = p_txn_header_id
896 ORDER BY levels, parent_occurrence, child_occurrence;
897
898
899 --
900 -- Bug 5118745. Locking issue as reported by customer P1 5060289.
901 -- For better user experience, augmented order by with occurrence
902 -- so that user does not see random ordering of sequences when
903 -- entering multiple records in one collection. No change to
904 -- performance as the number of new records per collection is
905 -- limited.
906 -- bso Tue Mar 28 12:59:39 PST 2006
907 --
908
909
910 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
911 l_eres_enabled BOOLEAN;
912
913 BEGIN
914 -- Bug 3128040. Get the profile value
915 l_eres_enabled := get_eres_profile();
916
917 IF p_txn_header_id IS NOT NULL THEN
918
919 -- for Direct data entry
920 FOR rec IN dde_cur LOOP
921 IF rec.levels = 0 THEN
922 -- This is the top most parent record.
923
924 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
925 -- Added eRes condition since we need intermediate commit before final eRes commit
926
927 IF l_eres_enabled THEN
928 gen_seq_for_currec_commit(rec.parent_plan_id,
929 rec.parent_collection_id,
930 rec.parent_occurrence,
931 null,null,null);
932
933 ELSE
934 generate_seq_for_currec(rec.parent_plan_id,
935 rec.parent_collection_id,
936 rec.parent_occurrence,
937 null,null,null);
938 END IF;
939
940 ELSE
941 -- This is for any child or grandchild ... record
942 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
943 -- Added eRes condition
944
945 IF l_eres_enabled THEN
946 gen_seq_for_currec_commit(rec.child_plan_id,
947 rec.child_collection_id,
948 rec.child_occurrence,
949 rec.parent_plan_id,
950 rec.parent_collection_id,
951 rec.parent_occurrence);
952
953 ELSE
954 generate_seq_for_currec(rec.child_plan_id,
955 rec.child_collection_id,
956 rec.child_occurrence,
957 rec.parent_plan_id,
958 rec.parent_collection_id,
959 rec.parent_occurrence);
960 END IF;
961 END IF;
962
963 END LOOP;
964 ELSE
965 -- for txn data entry
966 FOR rec IN txn_cur LOOP
967 IF rec.levels = 0 THEN
968 -- This is the top most parent record.
969
970 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
971 -- Added eRes condition
972 -- Bug 5368983. Added OA Txn Integartion condition.
973 IF ( l_eres_enabled OR p_oa_txnint ) THEN
974 --
975 -- bug 5228667
976 -- Check if the Txn is a resubmission of the
977 -- ERES. If it is then the Resubmission flag
978 -- is set to TRUE
979 -- ntungare Thu Aug 17 03:40:02 PDT 2006
980 --
981 If eres_resubmit(null,
982 rec.parent_collection_id,
983 rec.parent_plan_id,
984 rec.parent_occurrence) THEN
985
986 g_eres_resubmit_flg := TRUE;
987 END IF;
988 gen_seq_for_currec_commit(rec.parent_plan_id,
989 rec.parent_collection_id,
990 rec.parent_occurrence,
991 null,null,null,p_oa_txnint );
992
993 ELSE
994 generate_seq_for_currec(rec.parent_plan_id,
995 rec.parent_collection_id,
996 rec.parent_occurrence,
997 null,null,null);
998 END IF;
999
1000
1001 ELSE
1002 -- This is for any child or grandchild ... record
1003
1004 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
1005 -- Added eRes condition
1006
1007 -- Bug 5368983. Added OA Txn Integartion condition.
1008 IF ( l_eres_enabled OR p_oa_txnint ) THEN
1009
1010 --
1011 -- bug 5228667
1012 -- Check if the Txn is a resubmission of the
1013 -- ERES. If it is then the Resubmission flag
1014 -- is set to TRUE
1015 -- ntungare Thu Aug 17 03:40:02 PDT 2006
1016 --
1017 If eres_resubmit(null,
1018 rec.child_collection_id,
1019 rec.child_plan_id,
1020 rec.child_occurrence) THEN
1021
1022 g_eres_resubmit_flg := TRUE;
1023 END IF;
1024 gen_seq_for_currec_commit(rec.child_plan_id,
1025 rec.child_collection_id,
1026 rec.child_occurrence,
1027 rec.parent_plan_id,
1028 rec.parent_collection_id,
1029 rec.parent_occurrence,
1030 p_oa_txnint);
1031
1032 ELSE
1033 generate_seq_for_currec(rec.child_plan_id,
1034 rec.child_collection_id,
1035 rec.child_occurrence,
1036 rec.parent_plan_id,
1037 rec.parent_collection_id,
1038 rec.parent_occurrence);
1039 END IF;
1040 END IF;
1041
1042 --
1043 -- Bug 5228667
1044 -- Resetting the Eres resubmisson.
1045 -- Check Flag
1046 -- ntungare Thu Aug 17 03:40:02 PDT 2006
1047 --
1048 g_eres_resubmit_flg := FALSE;
1049
1050 END LOOP;
1051 END IF;
1052
1053 END generate_seq_for_pc;
1054
1055 -- Bug 5368983
1056 -- Added a new flag. Provided a default value so it
1057 -- does not impact existing calls
1058 -- saugupta Wed, 30 Aug 2006 06:06:20 -0700 PDT
1059 PROCEDURE gen_seq_for_currec_commit(p_plan_id NUMBER,
1060 p_collection_id NUMBER,
1061 p_occurrence NUMBER,
1062 p_parent_plan_id NUMBER,
1063 p_parent_collection_id NUMBER,
1064 p_parent_occurrence NUMBER,
1065 p_oa_txnint BOOLEAN DEFAULT NULL) IS
1066 PRAGMA AUTONOMOUS_TRANSACTION;
1067
1068 -- Bug 3128040 rponnusa Thu Sep 4 05:52:20 PDT 2003
1069
1070 -- In the ERES flow, we certainly don't want to commit the qa_results
1071 -- before the final commit. So the autonomous commit should be used
1072
1073 -- 1. user tries to save record in the form
1074 -- 2. sequences generated (HERE each generation is itself an
1075 -- autonomous txn:lock table, gen-sequence, add-to-audit, commit.)
1076 -- 3. qa_results table updated
1077 -- 4. eSignature obtained
1078 -- 5. commit
1079
1080 -- step 2 is accomblished in this procedure.
1081
1082 l_char_id NUMBER;
1083
1084 --
1085 -- Bug 5118745. Locking issue as reported by customer P1 5060289.
1086 -- This cursor is not required. It is conservatively used to give
1087 -- a nice-to-have feature for sequential numbering per collection
1088 -- during concurrent EQR.
1089 -- bso Tue Mar 28 12:19:04 PST 2006
1090 --
1091 -- CURSOR c IS
1092 -- SELECT char_id
1093 -- FROM qa_chars WHERE char_id = 1
1094 -- FOR UPDATE;
1095
1096 BEGIN
1097 -- unless user gets the lock on this table he can't get past this cursor.
1098 -- for eRes, lock the row within autonomous block. Reason is, the time between
1099 -- seq. generation and eres commit is too long, but we cannot have lock for so long
1100 -- since other users will wait. Here lock is relaased once block data committed
1101 -- thus avoids potential problem.
1102
1103 -- Bug 5118745. Locking issue as reported by customer P1 5060289.
1104 -- OPEN c;
1105 -- FETCH c INTO l_char_id;
1106 -- CLOSE c;
1107
1108 -- Bug 5368983
1109 -- Added a new flag to the modified proc signature
1110 -- saugupta Wed, 30 Aug 2006 06:07:52 -0700 PDT
1111 generate_seq_for_currec(p_plan_id,
1112 p_collection_id,
1113 p_occurrence,
1114 p_parent_plan_id,
1115 p_parent_collection_id,
1116 p_parent_occurrence,
1117 p_oa_txnint);
1118
1119 -- following commit statement will coming only the data in
1120 -- qa_seq_audit_history, qa_chars table. We should not commit
1121 -- records in qa_results;
1122
1123 COMMIT;
1124
1125 END gen_seq_for_currec_commit;
1126
1127 --
1128 -- bug 5228667
1129 -- New function to get the sequence from the
1130 -- Seq Audit Hist Table. In case an ERES Txn
1131 -- is resubmitted, then the sequence should not
1132 -- be regenerated as it would already be present
1133 -- in the Audit Hist table. In this case this
1134 -- function would be used to fetch the val from
1135 -- the Audit table.
1136 -- ntungare Thu Aug 17 03:59:48 PDT 2006
1137 --
1138 FUNCTION get_eres_seq(p_plan_id NUMBER,
1139 p_collection_id NUMBER,
1140 p_occurrence NUMBER,
1141 p_char_id NUMBER) RETURN VARCHAR2 AS
1142
1143 CURSOR cur_seq_val IS
1144 SELECT sequence_value
1145 FROM qa_seq_audit_history
1146 WHERE plan_id = p_plan_id
1147 AND collection_id = p_collection_id
1148 AND occurrence = p_occurrence
1149 AND char_id = p_char_id;
1150
1151 seq_val VARCHAR2(2000);
1152 BEGIN
1153 open cur_seq_val;
1154 fetch cur_seq_val into seq_val;
1155 close cur_seq_val;
1156
1157 RETURN seq_val;
1158 END get_eres_seq;
1159
1160 -- Bug 5368983
1161 -- Added a new flag. Provided a default value
1162 -- so it does not impact exeisting calls
1163 -- saugupta Wed, 30 Aug 2006 05:18:01 -0700 PDT
1164
1165 PROCEDURE generate_seq_for_currec(p_plan_id NUMBER,
1166 p_collection_id NUMBER,
1167 p_occurrence NUMBER,
1168 p_parent_plan_id NUMBER,
1169 p_parent_collection_id NUMBER,
1170 p_parent_occurrence NUMBER,
1171 p_oa_txnint BOOLEAN default NULL) IS
1172
1173 l_parent_rec_indicator NUMBER := -1;
1174 l_cur_rec_indicator NUMBER := -1;
1175 l_char_id NUMBER;
1176 l_seq_value VARCHAR2(40);
1177 l_seq_default_value VARCHAR2(40);
1178 l_eres_enabled BOOLEAN;
1179
1180 -- Bug 3160651 following cursor defined
1181 l_plan_name VARCHAR2(40);
1182
1183 CURSOR plan_cur(l_plan_id NUMBER) IS
1184 SELECT name FROM qa_plans
1185 WHERE plan_id = l_plan_id;
1186
1187 --Bug 5114865
1188 l_childUpdate_retval VARCHAR2(10);
1189
1190 parentChildCntr PLS_INTEGER := 1;
1191
1192 BEGIN
1193
1194 FOR i IN 1..QLTTRAWB.g_occurrence_tab.count LOOP
1195
1196 -- Find out the position of parent and/or child record in the plsql table
1197 -- by looping through the G_OCCURRENCE and store it in rec. indicator.
1198
1199 IF (p_parent_plan_id IS NOT NULL AND
1200 QLTTRAWB.g_occurrence_tab(i) = p_parent_occurrence) THEN
1201
1202 l_parent_rec_indicator := i;
1203
1204 ELSIF QLTTRAWB.g_occurrence_tab(i) = p_occurrence THEN -- for child record
1205 l_cur_rec_indicator := i;
1206 END IF;
1207
1208 END LOOP;
1209
1210 get_seq_value_for_pc_comb(p_plan_id,
1211 p_collection_id,
1212 p_occurrence,
1213 l_cur_rec_indicator,
1214 p_parent_plan_id,
1215 p_parent_collection_id,
1216 p_parent_occurrence,
1217 l_parent_rec_indicator);
1218
1219 -- Bug 3160651. rponnusa Thu Sep 25 02:24:28 PDT 2003
1220 OPEN plan_cur(p_plan_id);
1221 FETCH plan_cur INTO l_plan_name;
1222 CLOSE plan_cur;
1223
1224 -- get all the seq. element prompt info. for the plan
1225 get_all_char_prompt_info(p_plan_id);
1226
1227
1228 -- Generate new seq. value for parent rec. sequence elements or for child rec.
1229 -- sequence element which doesnt have any seq copy relation with parent rec.
1230
1231 -- In the QLTTRAWB.g_seq_tab1..15 if any value is 'Automatic' means that new seq. number
1232 -- needs to be generated.
1233
1234 -- For normal datacollection, generated only the sequence values for all the elements in
1235 -- the plan. But in eRes enabled txn, generated and capture the audit information at the
1236 -- same time.
1237
1238 l_seq_default_value := get_sequence_default_value();
1239 l_eres_enabled := get_eres_profile();
1240
1241 IF NVL(QLTTRAWB.g_seq_tab1(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1242 l_char_id := g_curr_plan_seq_char_ids(1);
1243
1244 --
1245 -- Bug 5228667
1246 -- in case the ERES request has been resubmitted in that
1247 -- case the seq value would already be generated and
1248 -- present in the qa_seq_audit_hist table. So it need not
1249 -- be generated again. The value just needs to be read
1250 -- from this table and populated in the array.
1251 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1252 --
1253 --l_seq_value := get_next_seq(l_char_id,FALSE);
1254 If g_eres_resubmit_flg = TRUE
1255 THEN l_seq_value := get_eres_seq(p_plan_id,
1256 p_collection_id,
1257 p_occurrence,
1258 l_char_id);
1259 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1260 End if;
1261
1262 QLTTRAWB.g_seq_tab1(l_cur_rec_indicator) := l_seq_value;
1263
1264 --
1265 -- Bug 5228667
1266 -- if ERES Txn is being resubmitted then the Seq
1267 -- Auditing need not be done as it would have been
1268 -- done earlier.
1269 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1270 --
1271 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1272 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1273 -- Bug 5368983
1274 -- Added the call if the we are coming here from OA Txn Integ code
1275 -- added same call for all the sequence elements below from 1..15
1276 -- saugupta Wed, 30 Aug 2006 05:26:00 -0700 PDT
1277 ELSIF p_oa_txnint THEN
1278 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1279 --
1280 -- bug 5955808
1281 -- Added for auditing the sequence generation in mobile application
1282 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1283 --
1284 ELSIF g_mobile THEN
1285 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1286 END IF;
1287
1288 -- Bug 3160651.
1289 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1290
1291 END IF;
1292
1293
1294 IF NVL(QLTTRAWB.g_seq_tab2(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1295 l_char_id := g_curr_plan_seq_char_ids(2);
1296
1297 --
1298 -- Bug 5228667
1299 -- in case the ERES request has been resubmitted in that
1300 -- case the seq value would already be generated and
1301 -- present in the qa_seq_audit_hist table. So it need not
1302 -- be generated again. The value just needs to be read
1303 -- from this table and populated in the array.
1304 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1305 --
1306 --l_seq_value := get_next_seq(l_char_id,FALSE);
1307 If g_eres_resubmit_flg = TRUE
1308 THEN l_seq_value := get_eres_seq(p_plan_id,
1309 p_collection_id,
1310 p_occurrence,
1311 l_char_id);
1312 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1313 End if;
1314
1315 QLTTRAWB.g_seq_tab2(l_cur_rec_indicator) := l_seq_value;
1316
1317 --
1318 -- Bug 5228667
1319 -- if ERES Txn is being resubmitted then the Seq
1320 -- Auditing need not be done as it would have been
1321 -- done earlier.
1322 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1323 --
1324 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1325 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1326 ELSIF p_oa_txnint THEN
1327 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1328 --
1329 -- bug 5955808
1330 -- Added for auditing the sequence generation in mobile application
1331 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1332 --
1333 ELSIF g_mobile THEN
1334 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1335 END IF;
1336
1337 -- Bug 3160651.
1338 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1339 END IF;
1340
1341 IF NVL(QLTTRAWB.g_seq_tab3(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1342 l_char_id := g_curr_plan_seq_char_ids(3);
1343
1344 --
1345 -- Bug 5228667
1346 -- in case the ERES request has been resubmitted in that
1347 -- case the seq value would already be generated and
1348 -- present in the qa_seq_audit_hist table. So it need not
1349 -- be generated again. The value just needs to be read
1350 -- from this table and populated in the array.
1351 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1352 --
1353 --l_seq_value := get_next_seq(l_char_id,FALSE);
1354 If g_eres_resubmit_flg = TRUE
1355 THEN l_seq_value := get_eres_seq(p_plan_id,
1356 p_collection_id,
1357 p_occurrence,
1358 l_char_id);
1359 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1360 End if;
1361
1362 QLTTRAWB.g_seq_tab3(l_cur_rec_indicator) := l_seq_value;
1363
1364 --
1365 -- Bug 5228667
1366 -- if ERES Txn is being resubmitted then the Seq
1367 -- Auditing need not be done as it would have been
1368 -- done earlier.
1369 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1370 --
1371 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1372 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1373 ELSIF p_oa_txnint THEN
1374 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1375 --
1376 -- bug 5955808
1377 -- Added for auditing the sequence generation in mobile application
1378 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1379 --
1380 ELSIF g_mobile THEN
1381 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1382 END IF;
1383
1384 -- Bug 3160651.
1385 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1386
1387 END IF;
1388
1389 IF NVL(QLTTRAWB.g_seq_tab4(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1390 l_char_id := g_curr_plan_seq_char_ids(4);
1391
1392 --
1393 -- Bug 5228667
1394 -- in case the ERES request has been resubmitted in that
1395 -- case the seq value would already be generated and
1396 -- present in the qa_seq_audit_hist table. So it need not
1397 -- be generated again. The value just needs to be read
1398 -- from this table and populated in the array.
1399 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1400 --
1401 --l_seq_value := get_next_seq(l_char_id,FALSE);
1402 If g_eres_resubmit_flg = TRUE
1403 THEN l_seq_value := get_eres_seq(p_plan_id,
1404 p_collection_id,
1405 p_occurrence,
1406 l_char_id);
1407 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1408 End if;
1409
1410 QLTTRAWB.g_seq_tab4(l_cur_rec_indicator) := l_seq_value;
1411
1412 --
1413 -- Bug 5228667
1414 -- if ERES Txn is being resubmitted then the Seq
1415 -- Auditing need not be done as it would have been
1416 -- done earlier.
1417 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1418 --
1419 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1420 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1421 ELSIF p_oa_txnint THEN
1422 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1423 --
1424 -- bug 5955808
1425 -- Added for auditing the sequence generation in mobile application
1426 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1427 --
1428 ELSIF g_mobile THEN
1429 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1430 END IF;
1431
1432 -- Bug 3160651.
1433 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1434
1435 END IF;
1436
1437 IF NVL(QLTTRAWB.g_seq_tab5(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1438 l_char_id := g_curr_plan_seq_char_ids(5);
1439
1440 --
1441 -- Bug 5228667
1442 -- in case the ERES request has been resubmitted in that
1443 -- case the seq value would already be generated and
1444 -- present in the qa_seq_audit_hist table. So it need not
1445 -- be generated again. The value just needs to be read
1446 -- from this table and populated in the array.
1447 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1448 --
1449 --l_seq_value := get_next_seq(l_char_id,FALSE);
1450 If g_eres_resubmit_flg = TRUE
1451 THEN l_seq_value := get_eres_seq(p_plan_id,
1452 p_collection_id,
1453 p_occurrence,
1454 l_char_id);
1455 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1456 End if;
1457
1458 QLTTRAWB.g_seq_tab5(l_cur_rec_indicator) := l_seq_value;
1459
1460 --
1461 -- Bug 5228667
1462 -- if ERES Txn is being resubmitted then the Seq
1463 -- Auditing need not be done as it would have been
1464 -- done earlier.
1465 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1466 --
1467 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1468 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1469 ELSIF p_oa_txnint THEN
1470 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1471 --
1472 -- bug 5955808
1473 -- Added for auditing the sequence generation in mobile application
1474 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1475 --
1476 ELSIF g_mobile THEN
1477 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1478 END IF;
1479
1480 -- Bug 3160651.
1481 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1482
1483 END IF;
1484
1485 IF NVL(QLTTRAWB.g_seq_tab6(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1486 l_char_id := g_curr_plan_seq_char_ids(6);
1487
1488 --
1489 -- Bug 5228667
1490 -- in case the ERES request has been resubmitted in that
1491 -- case the seq value would already be generated and
1492 -- present in the qa_seq_audit_hist table. So it need not
1493 -- be generated again. The value just needs to be read
1494 -- from this table and populated in the array.
1495 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1496 --
1497 --l_seq_value := get_next_seq(l_char_id,FALSE);
1498 If g_eres_resubmit_flg = TRUE
1499 THEN l_seq_value := get_eres_seq(p_plan_id,
1500 p_collection_id,
1501 p_occurrence,
1502 l_char_id);
1503 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1504 End if;
1505
1506 QLTTRAWB.g_seq_tab6(l_cur_rec_indicator) := l_seq_value;
1507
1508 --
1509 -- Bug 5228667
1510 -- if ERES Txn is being resubmitted then the Seq
1511 -- Auditing need not be done as it would have been
1512 -- done earlier.
1513 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1514 --
1515 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1516 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1517 ELSIF p_oa_txnint THEN
1518 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1519 --
1520 -- bug 5955808
1521 -- Added for auditing the sequence generation in mobile application
1522 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1523 --
1524 ELSIF g_mobile THEN
1525 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1526 END IF;
1527
1528 -- Bug 3160651.
1529 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1530
1531 END IF;
1532
1533 IF NVL(QLTTRAWB.g_seq_tab7(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1534 l_char_id := g_curr_plan_seq_char_ids(7);
1535
1536 --
1537 -- Bug 5228667
1538 -- in case the ERES request has been resubmitted in that
1539 -- case the seq value would already be generated and
1540 -- present in the qa_seq_audit_hist table. So it need not
1541 -- be generated again. The value just needs to be read
1542 -- from this table and populated in the array.
1543 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1544 --
1545 --l_seq_value := get_next_seq(l_char_id,FALSE);
1546 If g_eres_resubmit_flg = TRUE
1547 THEN l_seq_value := get_eres_seq(p_plan_id,
1548 p_collection_id,
1549 p_occurrence,
1550 l_char_id);
1551 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1552 End if;
1553
1554 QLTTRAWB.g_seq_tab7(l_cur_rec_indicator) := l_seq_value;
1555
1556 --
1557 -- Bug 5228667
1558 -- if ERES Txn is being resubmitted then the Seq
1559 -- Auditing need not be done as it would have been
1560 -- done earlier.
1561 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1562 --
1563 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1564 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1565 ELSIF p_oa_txnint THEN
1566 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1567 --
1568 -- bug 5955808
1569 -- Added for auditing the sequence generation in mobile application
1570 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1571 --
1572 ELSIF g_mobile THEN
1573 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1574 END IF;
1575
1576 -- Bug 3160651.
1577 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1578
1579 END IF;
1580
1581 IF NVL(QLTTRAWB.g_seq_tab8(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1582 l_char_id := g_curr_plan_seq_char_ids(8);
1583
1584 --
1585 -- Bug 5228667
1586 -- in case the ERES request has been resubmitted in that
1587 -- case the seq value would already be generated and
1588 -- present in the qa_seq_audit_hist table. So it need not
1589 -- be generated again. The value just needs to be read
1590 -- from this table and populated in the array.
1591 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1592 --
1593 --l_seq_value := get_next_seq(l_char_id,FALSE);
1594 If g_eres_resubmit_flg = TRUE
1595 THEN l_seq_value := get_eres_seq(p_plan_id,
1596 p_collection_id,
1597 p_occurrence,
1598 l_char_id);
1599 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1600 End if;
1601
1602 QLTTRAWB.g_seq_tab8(l_cur_rec_indicator) := l_seq_value;
1603
1604 --
1605 -- Bug 5228667
1606 -- if ERES Txn is being resubmitted then the Seq
1607 -- Auditing need not be done as it would have been
1608 -- done earlier.
1609 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1610 --
1611 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1612 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1613 ELSIF p_oa_txnint THEN
1614 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1615 --
1616 -- bug 5955808
1617 -- Added for auditing the sequence generation in mobile application
1618 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1619 --
1620 ELSIF g_mobile THEN
1621 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1622 END IF;
1623
1624 -- Bug 3160651.
1625 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1626
1627 END IF;
1628
1629 IF NVL(QLTTRAWB.g_seq_tab9(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1630 l_char_id := g_curr_plan_seq_char_ids(9);
1631
1632 --
1633 -- Bug 5228667
1634 -- in case the ERES request has been resubmitted in that
1635 -- case the seq value would already be generated and
1636 -- present in the qa_seq_audit_hist table. So it need not
1637 -- be generated again. The value just needs to be read
1638 -- from this table and populated in the array.
1639 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1640 --
1641 --l_seq_value := get_next_seq(l_char_id,FALSE);
1642 If g_eres_resubmit_flg = TRUE
1643 THEN l_seq_value := get_eres_seq(p_plan_id,
1644 p_collection_id,
1645 p_occurrence,
1646 l_char_id);
1647 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1648 End if;
1649
1650 QLTTRAWB.g_seq_tab9(l_cur_rec_indicator) := l_seq_value;
1651
1652 --
1653 -- Bug 5228667
1654 -- if ERES Txn is being resubmitted then the Seq
1655 -- Auditing need not be done as it would have been
1656 -- done earlier.
1657 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1658 --
1659 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1660 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1661 ELSIF p_oa_txnint THEN
1662 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1663 --
1664 -- bug 5955808
1665 -- Added for auditing the sequence generation in mobile application
1666 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1667 --
1668 ELSIF g_mobile THEN
1669 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1670 END IF;
1671
1672 -- Bug 3160651.
1673 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1674
1675 END IF;
1676
1677 IF NVL(QLTTRAWB.g_seq_tab10(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1678 l_char_id := g_curr_plan_seq_char_ids(10);
1679
1680 --
1681 -- Bug 5228667
1682 -- in case the ERES request has been resubmitted in that
1683 -- case the seq value would already be generated and
1684 -- present in the qa_seq_audit_hist table. So it need not
1685 -- be generated again. The value just needs to be read
1686 -- from this table and populated in the array.
1687 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1688 --
1689 --l_seq_value := get_next_seq(l_char_id,FALSE);
1690 If g_eres_resubmit_flg = TRUE
1691 THEN l_seq_value := get_eres_seq(p_plan_id,
1692 p_collection_id,
1693 p_occurrence,
1694 l_char_id);
1695 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1696 End if;
1697
1698 QLTTRAWB.g_seq_tab10(l_cur_rec_indicator) := l_seq_value;
1699
1700 --
1701 -- Bug 5228667
1702 -- if ERES Txn is being resubmitted then the Seq
1703 -- Auditing need not be done as it would have been
1704 -- done earlier.
1705 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1706 --
1707 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1708 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1709 ELSIF p_oa_txnint THEN
1710 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1711 --
1712 -- bug 5955808
1713 -- Added for auditing the sequence generation in mobile application
1714 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1715 --
1716 ELSIF g_mobile THEN
1717 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1718 END IF;
1719
1720 -- Bug 3160651.
1721 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1722
1723 END IF;
1724
1725
1726 IF NVL(QLTTRAWB.g_seq_tab11(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1727 l_char_id := g_curr_plan_seq_char_ids(11);
1728
1729 --
1730 -- Bug 5228667
1731 -- in case the ERES request has been resubmitted in that
1732 -- case the seq value would already be generated and
1733 -- present in the qa_seq_audit_hist table. So it need not
1734 -- be generated again. The value just needs to be read
1735 -- from this table and populated in the array.
1736 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1737 --
1738 --l_seq_value := get_next_seq(l_char_id,FALSE);
1739 If g_eres_resubmit_flg = TRUE
1740 THEN l_seq_value := get_eres_seq(p_plan_id,
1741 p_collection_id,
1742 p_occurrence,
1743 l_char_id);
1744 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1745 End if;
1746
1747 QLTTRAWB.g_seq_tab11(l_cur_rec_indicator) := l_seq_value;
1748
1749 --
1750 -- Bug 5228667
1751 -- if ERES Txn is being resubmitted then the Seq
1752 -- Auditing need not be done as it would have been
1753 -- done earlier.
1754 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1755 --
1756 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1757 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1758 ELSIF p_oa_txnint THEN
1759 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1760 --
1761 -- bug 5955808
1762 -- Added for auditing the sequence generation in mobile application
1763 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1764 --
1765 ELSIF g_mobile THEN
1766 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1767 END IF;
1768
1769 -- Bug 3160651.
1770 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1771
1772 END IF;
1773
1774 IF NVL(QLTTRAWB.g_seq_tab12(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1775 l_char_id := g_curr_plan_seq_char_ids(12);
1776
1777 --
1778 -- Bug 5228667
1779 -- in case the ERES request has been resubmitted in that
1780 -- case the seq value would already be generated and
1781 -- present in the qa_seq_audit_hist table. So it need not
1782 -- be generated again. The value just needs to be read
1783 -- from this table and populated in the array.
1784 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1785 --
1786 --l_seq_value := get_next_seq(l_char_id,FALSE);
1787 If g_eres_resubmit_flg = TRUE
1788 THEN l_seq_value := get_eres_seq(p_plan_id,
1789 p_collection_id,
1790 p_occurrence,
1791 l_char_id);
1792 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1793 End if;
1794
1795 QLTTRAWB.g_seq_tab12(l_cur_rec_indicator) := l_seq_value;
1796
1797 --
1798 -- Bug 5228667
1799 -- if ERES Txn is being resubmitted then the Seq
1800 -- Auditing need not be done as it would have been
1801 -- done earlier.
1802 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1803 --
1804 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1805 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1806 ELSIF p_oa_txnint THEN
1807 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1808 --
1809 -- bug 5955808
1810 -- Added for auditing the sequence generation in mobile application
1811 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1812 --
1813 ELSIF g_mobile THEN
1814 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1815 END IF;
1816
1817 -- Bug 3160651.
1818 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1819
1820 END IF;
1821
1822 IF NVL(QLTTRAWB.g_seq_tab13(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1823 l_char_id := g_curr_plan_seq_char_ids(13);
1824
1825 --
1826 -- Bug 5228667
1827 -- in case the ERES request has been resubmitted in that
1828 -- case the seq value would already be generated and
1829 -- present in the qa_seq_audit_hist table. So it need not
1830 -- be generated again. The value just needs to be read
1831 -- from this table and populated in the array.
1832 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1833 --
1834 --l_seq_value := get_next_seq(l_char_id,FALSE);
1835 If g_eres_resubmit_flg = TRUE
1836 THEN l_seq_value := get_eres_seq(p_plan_id,
1837 p_collection_id,
1838 p_occurrence,
1839 l_char_id);
1840 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1841 End if;
1842
1843 QLTTRAWB.g_seq_tab13(l_cur_rec_indicator) := l_seq_value;
1844
1845 --
1846 -- Bug 5228667
1847 -- if ERES Txn is being resubmitted then the Seq
1848 -- Auditing need not be done as it would have been
1849 -- done earlier.
1850 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1851 --
1852 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1853 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1854 ELSIF p_oa_txnint THEN
1855 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1856 --
1857 -- bug 5955808
1858 -- Added for auditing the sequence generation in mobile application
1859 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1860 --
1861 ELSIF g_mobile THEN
1862 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1863 END IF;
1864
1865 -- Bug 3160651.
1866 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1867
1868 END IF;
1869
1870 IF NVL(QLTTRAWB.g_seq_tab14(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1871 l_char_id := g_curr_plan_seq_char_ids(14);
1872
1873 --
1874 -- Bug 5228667
1875 -- in case the ERES request has been resubmitted in that
1876 -- case the seq value would already be generated and
1877 -- present in the qa_seq_audit_hist table. So it need not
1878 -- be generated again. The value just needs to be read
1879 -- from this table and populated in the array.
1880 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1881 --
1882 --l_seq_value := get_next_seq(l_char_id,FALSE);
1883 If g_eres_resubmit_flg = TRUE
1884 THEN l_seq_value := get_eres_seq(p_plan_id,
1885 p_collection_id,
1886 p_occurrence,
1887 l_char_id);
1888 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1889 End if;
1890
1891 QLTTRAWB.g_seq_tab14(l_cur_rec_indicator) := l_seq_value;
1892
1893 --
1894 -- Bug 5228667
1895 -- if ERES Txn is being resubmitted then the Seq
1896 -- Auditing need not be done as it would have been
1897 -- done earlier.
1898 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1899 --
1900 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1901 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1902 ELSIF p_oa_txnint THEN
1903 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1904 --
1905 -- bug 5955808
1906 -- Added for auditing the sequence generation in mobile application
1907 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1908 --
1909 ELSIF g_mobile THEN
1910 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1911 END IF;
1912
1913 -- Bug 3160651.
1914 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1915
1916 END IF;
1917
1918 IF NVL(QLTTRAWB.g_seq_tab15(l_cur_rec_indicator),' ') = l_seq_default_value THEN
1919 l_char_id := g_curr_plan_seq_char_ids(15);
1920
1921 --
1922 -- Bug 5228667
1923 -- in case the ERES request has been resubmitted in that
1924 -- case the seq value would already be generated and
1925 -- present in the qa_seq_audit_hist table. So it need not
1926 -- be generated again. The value just needs to be read
1927 -- from this table and populated in the array.
1928 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1929 --
1930 --l_seq_value := get_next_seq(l_char_id,FALSE);
1931 If g_eres_resubmit_flg = TRUE
1932 THEN l_seq_value := get_eres_seq(p_plan_id,
1933 p_collection_id,
1934 p_occurrence,
1935 l_char_id);
1936 ELSE l_seq_value := get_next_seq(l_char_id,FALSE);
1937 End if;
1938
1939 QLTTRAWB.g_seq_tab15(l_cur_rec_indicator) := l_seq_value;
1940
1941 --
1942 -- Bug 5228667
1943 -- if ERES Txn is being resubmitted then the Seq
1944 -- Auditing need not be done as it would have been
1945 -- done earlier.
1946 -- ntungare Thu Aug 17 04:06:01 PDT 2006
1947 --
1948 IF (l_eres_enabled AND g_eres_resubmit_flg = FALSE) THEN
1949 audit_seq_for_eres(l_char_id,l_seq_value,l_cur_rec_indicator);
1950 ELSIF p_oa_txnint THEN
1951 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator);
1952 --
1953 -- bug 5955808
1954 -- Added for auditing the sequence generation in mobile application
1955 -- ntungare Thu Jul 26 02:55:50 PDT 2007
1956 --
1957 ELSIF g_mobile THEN
1958 audit_seq_for_txnint(l_char_id,l_seq_value,l_cur_rec_indicator, 'MOBILE');
1959 END IF;
1960
1961 -- Bug 3160651.
1962 populate_message_array(l_char_id,l_seq_value,l_plan_name);
1963
1964 END IF;
1965
1966 --
1967 -- Bug 5114865
1968 -- Collect the indexes for the Seq Type elements
1969 -- to be read from the the QLTTRAWB.g_seq_tabX array
1970 -- ntungare Sun Apr 9 23:40:46 PDT 2006
1971 If g_curr_plan_seq_char_ids.COUNT <> 0
1972 THEN
1973 g_true_seq_gen_recids(NVL(g_true_seq_gen_recids.LAST,0)+1) := l_cur_rec_indicator;
1974 END IF;
1975
1976 --
1977 -- Bug 5114865
1978 -- Gather the data for the PC relationships
1979 -- ntungare Sun Apr 9 23:40:46 PDT 2006
1980 IF p_parent_plan_id IS NOT NULL THEN
1981 parentChildCntr := NVL(ParentChild_Tab.LAST,0)+1;
1982 ParentChild_Tab(parentChildCntr).parent_plan_id := p_parent_plan_id;
1983 ParentChild_Tab(parentChildCntr).parent_collection_id := p_parent_collection_id;
1984 ParentChild_Tab(parentChildCntr).parent_occurrence := p_parent_occurrence;
1985 ParentChild_Tab(parentChildCntr).child_plan_id := p_plan_id;
1986 ParentChild_Tab(parentChildCntr).child_collection_id := p_collection_id;
1987 ParentChild_Tab(parentChildCntr).child_occurrence := p_occurrence;
1988
1989 END IF;
1990
1991 END generate_seq_for_currec;
1992
1993 PROCEDURE get_seq_value_for_pc_comb(p_plan_id NUMBER,
1994 p_collection_id NUMBER,
1995 p_occurrence NUMBER,
1996 p_cur_rec_indicator NUMBER,
1997 p_parent_plan_id NUMBER,
1998 p_parent_collection_id NUMBER,
1999 p_parent_occurrence NUMBER,
2000 p_parent_rec_indicator NUMBER) IS
2001
2002 -- First pack all the char_id of the seq. element in the plan into x_seq_char_ids
2003 -- If it is top level parent rec, then dont do anything.
2004 -- If it is child rec and copy relations exist then copy parent seq. value to child
2005 -- seq element in the QLTTRAWB.g_seq_tab1..15 table for the child rec.
2006
2007 l_seq_value VARCHAR2(30);
2008 i NUMBER;
2009
2010 BEGIN
2011 IF ((g_curr_plan_id = p_plan_id AND p_parent_plan_id IS NULL) OR
2012 (g_curr_plan_id = p_plan_id AND
2013 g_curr_parent_plan_id = p_parent_plan_id)) THEN
2014
2015 -- Current plan(if not parent-child plan) or Parent-Child setup
2016 -- information for the current plan was already stored. Do nothing
2017 NULL;
2018 ELSE
2019 -- get the plan setup or parent-child setup for the current plan
2020
2021 get_plan_seq_ele_setup(p_plan_id,
2022 p_parent_plan_id);
2023
2024 g_curr_plan_id := p_plan_id;
2025 g_curr_parent_plan_id := p_parent_plan_id;
2026
2027 END IF;
2028
2029 --
2030 -- bug 7015532
2031 -- Added a check to ensure that the P-C processing is done only
2032 -- if the parent Id is not null and also the parent record
2033 -- indicator is not equal to -1
2034 -- ntungare
2035 --
2036 IF (p_parent_plan_id IS NOT NULL AND
2037 p_parent_rec_indicator <> -1) THEN -- parent child combination exist
2038
2039 -- loop thro' all the seq element in the child plan, check any copy relation
2040 -- exist with its parent plan. If exist then copy parent seq. element value
2041 -- to child seq. element
2042 i := g_curr_plan_seq_char_ids.FIRST;
2043
2044 WHILE i IS NOT NULL LOOP
2045
2046 IF g_parent_plan_seq_char_ids.EXISTS(i) THEN
2047 -- copy relation exists between parent,child seq. elements
2048 find_update_seq_in_collection(p_parent_rec_indicator,
2049 g_parent_plan_seq_nos(i),
2050 1,
2051 l_seq_value);
2052
2053 find_update_seq_in_collection(p_cur_rec_indicator,
2054 i,
2055 2,
2056 l_seq_value);
2057
2058 END IF;
2059 i := g_curr_plan_seq_char_ids.NEXT(i); -- get the subscript of next element
2060 END LOOP;
2061 END IF;
2062
2063 END get_seq_value_for_pc_comb;
2064
2065 PROCEDURE get_plan_seq_ele_setup(p_plan_id NUMBER,
2066 p_parent_plan_id NUMBER) IS
2067 CURSOR plan_cur IS
2068 SELECT qc.char_id,substr(qpc.result_column_name,9,10) position
2069 FROM qa_plan_chars qpc,
2070 qa_chars qc
2071 WHERE qpc.plan_id = p_plan_id AND
2072 qpc.char_id = qc.char_id AND
2073 qpc.enabled_flag = 1 AND
2074 qc.datatype = 5;
2075
2076 -- Bug 5114865
2077 -- Modified the Cursor to fetch only the Seq Type elements
2078 -- Relationships as the Seq-Char relationships would be
2079 -- handled in the Update_record procedure using the
2080 -- Update_Child proc
2081 -- ntungare Sun Apr 9 23:40:46 PDT 2006
2082 CURSOR pc_cur IS
2083 SELECT parent_char_id, substr(parent_database_column,9,10) parent_seq_position,
2084 child_char_id, substr(child_database_column,9,10) child_seq_position
2085 FROM qa_pc_result_columns_v
2086 WHERE parent_plan_id = p_parent_plan_id AND
2087 child_plan_id = p_plan_id AND
2088 element_relationship_type = 1 AND
2089 parent_dataType = 5 AND
2090 child_dataType = 5;
2091
2092 -- EXISTS (SELECT 1
2093 -- FROM qa_chars
2094 -- WHERE char_id in (parent_char_id,child_char_id) AND
2095 -- datatype = 5);
2096 BEGIN
2097 -- reset all the collections used
2098
2099 g_curr_plan_seq_char_ids.DELETE;
2100 g_parent_plan_seq_char_ids.DELETE;
2101 g_parent_plan_seq_nos.DELETE;
2102
2103 FOR plan_rec IN plan_cur LOOP
2104 -- store the seq char_ID in collection index by the sequence position (1 to 15)
2105
2106 g_curr_plan_seq_char_ids(plan_rec.position) := plan_rec.char_id;
2107 END LOOP;
2108
2109 IF p_parent_plan_id IS NOT NULL THEN -- parent child combination exist
2110
2111 FOR child_rec IN pc_cur LOOP
2112
2113 -- store parent seq. element char_ID and its corresponding parent
2114 -- seq. position (between 1 to 15) in the collection. Both the collections
2115 -- are indexed by child seq. position
2116
2117 -- Ex. if seq8 in parent plan copied to seq15 in child plan. Store seq15 char_ID
2118 -- in collection g_curr_plan_seq_char_ids at index 15 (this is done in plan_cur).
2119 -- similarly char_ID of seq8 stored in g_parent_plan_seq_char_ids at index 15
2120 -- and parent seq position ( 8 in this case) stored in g_parent_plan_seq_nos at index 15
2121
2122
2123 g_parent_plan_seq_char_ids(child_rec.child_seq_position) := child_rec.parent_char_id;
2124 g_parent_plan_seq_nos(child_rec.child_seq_position) := child_rec.parent_seq_position;
2125 END LOOP;
2126 END IF;
2127 END get_plan_seq_ele_setup;
2128
2129
2130 PROCEDURE find_update_seq_in_collection(p_cur_record_indicator NUMBER,
2131 p_seq_position NUMBER,
2132 p_action NUMBER,
2133 p_seq_value IN OUT NOCOPY VARCHAR2) IS
2134 /*
2135 p_action:
2136 Holds value 1 Meaning get the seq value from parent record in plsql table
2137 Holds value 2 Meaning copy the seq value into child record in plsql table
2138
2139 p_cur_record_indicator:
2140 Indicates the record postion in the plsql table that should be used to
2141 get or update the seq values from parent or child record respectively
2142
2143 p_seq_position:
2144 Holds values from 1 to 15 to identify the result column name in qa_results
2145
2146 p_seq_value inout variable:
2147 When p_action = 1 then parent seq value return back to calling procedure
2148 When p_action = 2 then parent seq value passed in
2149 */
2150
2151
2152 BEGIN
2153
2154 IF p_seq_position = 1 THEN
2155 IF p_action = 1 THEN
2156 p_seq_value := QLTTRAWB.g_seq_tab1(p_cur_record_indicator);
2157 ELSE
2158 QLTTRAWB.g_seq_tab1(p_cur_record_indicator):= p_seq_value;
2159 END IF;
2160
2161
2162 ELSIF p_seq_position = 2 THEN
2163 IF p_action = 1 THEN
2164 p_seq_value := QLTTRAWB.g_seq_tab2(p_cur_record_indicator);
2165 ELSE
2166 QLTTRAWB.g_seq_tab2(p_cur_record_indicator):= p_seq_value;
2167 END IF;
2168
2169 ELSIF p_seq_position = 3 THEN
2170 IF p_action = 1 THEN
2171 p_seq_value := QLTTRAWB.g_seq_tab3(p_cur_record_indicator);
2172 ELSE
2173 QLTTRAWB.g_seq_tab3(p_cur_record_indicator):= p_seq_value;
2174 END IF;
2175
2176 ELSIF p_seq_position = 4 THEN
2177 IF p_action = 1 THEN
2178 p_seq_value := QLTTRAWB.g_seq_tab4(p_cur_record_indicator);
2179 ELSE
2180 QLTTRAWB.g_seq_tab4( p_cur_record_indicator):= p_seq_value;
2181 END IF;
2182
2183 ELSIF p_seq_position = 5 THEN
2184 IF p_action = 1 THEN
2185 p_seq_value := QLTTRAWB.g_seq_tab5(p_cur_record_indicator);
2186 ELSE
2187 QLTTRAWB.g_seq_tab5(p_cur_record_indicator):= p_seq_value;
2188 END IF;
2189
2190 ELSIF p_seq_position = 6 THEN
2191 IF p_action = 1 THEN
2192 p_seq_value := QLTTRAWB.g_seq_tab6(p_cur_record_indicator);
2193 ELSE
2194 QLTTRAWB.g_seq_tab6(p_cur_record_indicator):= p_seq_value;
2195 END IF;
2196
2197 ELSIF p_seq_position = 7 THEN
2198 IF p_action = 1 THEN
2199 p_seq_value := QLTTRAWB.g_seq_tab7(p_cur_record_indicator);
2200 ELSE
2201 QLTTRAWB.g_seq_tab7(p_cur_record_indicator):= p_seq_value;
2202 END IF;
2203
2204 ELSIF p_seq_position = 8 THEN
2205 IF p_action = 1 THEN
2206 p_seq_value := QLTTRAWB.g_seq_tab8(p_cur_record_indicator);
2207 ELSE
2208 QLTTRAWB.g_seq_tab8(p_cur_record_indicator):= p_seq_value;
2209 END IF;
2210
2211 ELSIF p_seq_position = 9 THEN
2212 IF p_action = 1 THEN
2213 p_seq_value := QLTTRAWB.g_seq_tab9(p_cur_record_indicator);
2214 ELSE
2215 QLTTRAWB.g_seq_tab9(p_cur_record_indicator):= p_seq_value;
2216 END IF;
2217
2218 ELSIF p_seq_position = 10 THEN
2219 IF p_action = 1 THEN
2220 p_seq_value := QLTTRAWB.g_seq_tab10(p_cur_record_indicator);
2221 ELSE
2222 QLTTRAWB.g_seq_tab10(p_cur_record_indicator):= p_seq_value;
2223 END IF;
2224
2225 ELSIF p_seq_position = 11 THEN
2226 IF p_action = 1 THEN
2227 p_seq_value := QLTTRAWB.g_seq_tab11(p_cur_record_indicator);
2228 ELSE
2229 QLTTRAWB.g_seq_tab11(p_cur_record_indicator):= p_seq_value;
2230 END IF;
2231
2232 ELSIF p_seq_position = 12 THEN
2233 IF p_action = 1 THEN
2234 p_seq_value := QLTTRAWB.g_seq_tab12(p_cur_record_indicator);
2235 ELSE
2236 QLTTRAWB.g_seq_tab12(p_cur_record_indicator):= p_seq_value;
2237 END IF;
2238
2239 ELSIF p_seq_position = 13 THEN
2240 IF p_action = 1 THEN
2241 p_seq_value := QLTTRAWB.g_seq_tab13(p_cur_record_indicator);
2242 ELSE
2243 QLTTRAWB.g_seq_tab13(p_cur_record_indicator):= p_seq_value;
2244 END IF;
2245
2246
2247 ELSIF p_seq_position = 14 THEN
2248 IF p_action = 1 THEN
2249 p_seq_value := QLTTRAWB.g_seq_tab14(p_cur_record_indicator);
2250 ELSE
2251 QLTTRAWB.g_seq_tab14(p_cur_record_indicator):= p_seq_value;
2252 END IF;
2253
2254 ELSIF p_seq_position = 15 THEN
2255 IF p_action = 1 THEN
2256 p_seq_value := QLTTRAWB.g_seq_tab15(p_cur_record_indicator);
2257 ELSE
2258 QLTTRAWB.g_seq_tab15(p_cur_record_indicator):= p_seq_value;
2259 END IF;
2260 END IF;
2261
2262 END find_update_seq_in_collection;
2263
2264 -- Bug 5114865
2265 -- New procedure to reset the Sequence
2266 -- Global Arrays to NULL
2267 -- nutngare Thu Mar 16 08:33:38 PST 2006
2268 --
2269 PROCEDURE reset_sequence_global_arrays AS
2270 BEGIN
2271 g_true_seq_gen_recids.delete;
2272 ParentChild_Tab.delete;
2273 QLTTRAWB.g_seq_tab1:= NULL;
2274 QLTTRAWB.g_seq_tab2:= NULL;
2275 QLTTRAWB.g_seq_tab3:= NULL;
2276 QLTTRAWB.g_seq_tab4:= NULL;
2277 QLTTRAWB.g_seq_tab5:= NULL;
2278 QLTTRAWB.g_seq_tab6:= NULL;
2279 QLTTRAWB.g_seq_tab7:= NULL;
2280 QLTTRAWB.g_seq_tab8:= NULL;
2281 QLTTRAWB.g_seq_tab9:= NULL;
2282 QLTTRAWB.g_seq_tab10:= NULL;
2283 QLTTRAWB.g_seq_tab11:= NULL;
2284 QLTTRAWB.g_seq_tab12:= NULL;
2285 QLTTRAWB.g_seq_tab13:= NULL;
2286 QLTTRAWB.g_seq_tab14:= NULL;
2287 QLTTRAWB.g_seq_tab15:= NULL;
2288 QLTTRAWB.g_plan_id_tab:= NULL;
2289 QLTTRAWB.g_collection_id_tab:= NULL;
2290 QLTTRAWB.g_occurrence_tab:= NULL;
2291 QLTTRAWB.g_txn_header_id_tab:= NULL;
2292 END reset_sequence_global_arrays;
2293
2294 PROCEDURE update_record(p_total_rec_count NUMBER) IS
2295
2296 l_childUpdate_retval varchar2(10);
2297 BEGIN
2298
2299 -- Bug 5114865
2300 -- Commented out the update below as the Udpate is
2301 -- now split between the seq Type elements
2302 -- and the seq->Char elem relations
2303 -- ntungare Sun Apr 9 23:43:06 PDT 2006
2304
2305 -- Bulk update done once for all the records in collection
2306
2307 -- FORALL k IN 1..p_total_rec_count
2308 -- UPDATE qa_results
2309 -- SET sequence1 = QLTTRAWB.g_seq_tab1(k),
2310 -- sequence2 = QLTTRAWB.g_seq_tab2(k),
2311 -- sequence3 = QLTTRAWB.g_seq_tab3(k),
2312 -- sequence4 = QLTTRAWB.g_seq_tab4(k),
2313 -- sequence5 = QLTTRAWB.g_seq_tab5(k),
2314 -- sequence6 = QLTTRAWB.g_seq_tab6(k),
2315 -- sequence7 = QLTTRAWB.g_seq_tab7(k),
2316 -- sequence8 = QLTTRAWB.g_seq_tab8(k),
2317 -- sequence9 = QLTTRAWB.g_seq_tab9(k),
2318 -- sequence10 = QLTTRAWB.g_seq_tab10(k),
2319 -- sequence11 = QLTTRAWB.g_seq_tab11(k),
2320 -- sequence12 = QLTTRAWB.g_seq_tab12(k),
2321 -- sequence13 = QLTTRAWB.g_seq_tab13(k),
2322 -- sequence14 = QLTTRAWB.g_seq_tab14(k),
2323 -- sequence15 = QLTTRAWB.g_seq_tab15(k)
2324 -- WHERE plan_id = QLTTRAWB.g_plan_id_tab(k) AND
2325 -- collection_id = QLTTRAWB.g_collection_id_tab(k) AND
2326 -- occurrence = QLTTRAWB.g_occurrence_tab(k);
2327
2328 --
2329 -- Bug 5114865
2330 -- Updating the data for the Seq Type elements
2331 -- nutngare Sun Apr 9 23:43:06 PDT 2006
2332 --
2333 FOR k in 1..g_true_seq_gen_recids.COUNT
2334 LOOP
2335 UPDATE qa_results
2336 SET sequence1 = QLTTRAWB.g_seq_tab1(g_true_seq_gen_recids(k)),
2337 sequence2 = QLTTRAWB.g_seq_tab2(g_true_seq_gen_recids(k)),
2338 sequence3 = QLTTRAWB.g_seq_tab3(g_true_seq_gen_recids(k)),
2339 sequence4 = QLTTRAWB.g_seq_tab4(g_true_seq_gen_recids(k)),
2340 sequence5 = QLTTRAWB.g_seq_tab5(g_true_seq_gen_recids(k)),
2341 sequence6 = QLTTRAWB.g_seq_tab6(g_true_seq_gen_recids(k)),
2342 sequence7 = QLTTRAWB.g_seq_tab7(g_true_seq_gen_recids(k)),
2343 sequence8 = QLTTRAWB.g_seq_tab8(g_true_seq_gen_recids(k)),
2344 sequence9 = QLTTRAWB.g_seq_tab9(g_true_seq_gen_recids(k)),
2345 sequence10 = QLTTRAWB.g_seq_tab10(g_true_seq_gen_recids(k)),
2346 sequence11 = QLTTRAWB.g_seq_tab11(g_true_seq_gen_recids(k)),
2347 sequence12 = QLTTRAWB.g_seq_tab12(g_true_seq_gen_recids(k)),
2348 sequence13 = QLTTRAWB.g_seq_tab13(g_true_seq_gen_recids(k)),
2349 sequence14 = QLTTRAWB.g_seq_tab14(g_true_seq_gen_recids(k)),
2350 sequence15 = QLTTRAWB.g_seq_tab15(g_true_seq_gen_recids(k))
2351 WHERE plan_id = QLTTRAWB.g_plan_id_tab(g_true_seq_gen_recids(k)) AND
2352 collection_id = QLTTRAWB.g_collection_id_tab(g_true_seq_gen_recids(k)) AND
2353 occurrence = QLTTRAWB.g_occurrence_tab(g_true_seq_gen_recids(k));
2354 END LOOP;
2355
2356 -- Bug 5114865
2357 -- Updating the data for the Seq->Char elem relations
2358 -- ntungare Sun Apr 9 23:43:06 PDT 2006
2359 If ParentChild_Tab.COUNT <> 0 THEN
2360 l_childUpdate_retval := QA_PARENT_CHILD_PKG.update_sequence_child
2361 (p_ParentChild_Tab => ParentChild_Tab);
2362 End If;
2363
2364 -- Bug 5114865
2365 -- Resetting the global Arrays
2366 -- ntungare Sun Apr 9 23:43:06 PDT 2006
2367 reset_sequence_global_arrays;
2368
2369 END update_record;
2370
2371 FUNCTION get_sequence_default_value RETURN VARCHAR2 IS
2372 BEGIN
2373
2374 fnd_message.set_name('QA','QA_SEQ_DEFAULT');
2375 RETURN fnd_message.get;
2376
2377 END get_sequence_default_value;
2378
2379
2380 FUNCTION get_eres_profile RETURN BOOLEAN IS
2381
2382 l_eres_profile VARCHAR2(3);
2383 BEGIN
2384 l_eres_profile := FND_PROFILE.VALUE('EDR_ERES_ENABLED');
2385
2386 IF l_eres_profile = 'Y' THEN
2387 RETURN TRUE;
2388 ELSE
2389 RETURN FALSE;
2390 END IF;
2391
2392 END get_eres_profile;
2393
2394 --
2395 -- bug 5228667
2396 -- Added the parameters plan id and occurrence
2397 -- ntungare Thu Aug 17 05:06:15 PDT 2006
2398 --
2399 FUNCTION eres_resubmit(p_txn_header_id NUMBER,
2400 p_collection_id NUMBER,
2401 p_plan_id NUMBER DEFAULT NULL,
2402 p_occurrence NUMBER DEFAULT NULL)
2403 RETURN BOOLEAN IS
2404
2405 -- This procedure return true if any audit record found
2406 -- for the collection identified by txn_header_id or
2407 -- collection_id. This scanario happens when eRes resubmits
2408 -- the same record again. There is no need to generate seq again
2409 -- since it was generated already.
2410
2411 l_count NUMBER;
2412
2413 CURSOR dde_cursor IS
2414 SELECT 1 FROM qa_seq_audit_history
2415 WHERE txn_header_id = p_txn_header_id
2416 and (p_plan_id IS NULL OR plan_id = p_plan_id)
2417 and (p_occurrence IS NULL OR occurrence = p_occurrence);
2418
2419 CURSOR txn_cursor IS
2420 SELECT 1 FROM qa_seq_audit_history
2421 WHERE collection_id = p_collection_id
2422 and (p_plan_id IS NULL OR plan_id = p_plan_id)
2423 and (p_occurrence IS NULL OR occurrence = p_occurrence);
2424 BEGIN
2425 IF p_txn_header_id IS NOT NULL THEN
2426
2427 OPEN dde_cursor;
2428 FETCH dde_cursor INTO l_count;
2429 IF dde_cursor%NOTFOUND THEN
2430 l_count := 0;
2431 END IF;
2432 CLOSE dde_cursor;
2433
2434 ELSIF p_collection_id IS NOT NULL THEN
2435
2436 OPEN txn_cursor;
2437 FETCH txn_cursor INTO l_count;
2438 IF txn_cursor%NOTFOUND THEN
2439 l_count := 0;
2440 END IF;
2441 CLOSE txn_cursor;
2442 END IF;
2443
2444 IF l_count > 0 THEN
2445 -- means that records are resubmitted. No need to
2446 -- generate seq again.
2447 RETURN TRUE;
2448 ELSE
2449 RETURN FALSE;
2450 END IF;
2451
2452 END eres_resubmit;
2453
2454 PROCEDURE audit_sequence_values(p_plan_id NUMBER,
2455 p_collection_id NUMBER,
2456 p_occurrence NUMBER,
2457 p_enabled_flag VARCHAR2) IS
2458
2459 l_child_plan_ids DBMS_SQL.number_table;
2460 l_child_collection_ids DBMS_SQL.number_table;
2461 l_child_occurrences DBMS_SQL.number_table;
2462
2463 l_status VARCHAR2(1);
2464 BEGIN
2465 -- capture audit only if enabled child records are deleted.
2466 IF p_enabled_flag <> 'T' THEN
2467 RETURN;
2468 END IF;
2469
2470 -- given the parent record information, find out all the child, grand child
2471 -- plan ID, coll ID and occurrence
2472
2473 l_status := QA_PARENT_CHILD_PKG.get_descendants(p_plan_id,
2474 p_collection_id,
2475 p_occurrence,
2476 l_child_plan_ids,
2477 l_child_collection_ids,
2478 l_child_occurrences);
2479
2480
2481 IF (l_status = 'T') THEN
2482
2483 -- Important thing is, here auditing is not done for the parent record.
2484 -- This will be done by the client side code
2485
2486 audit_sequence_values(l_child_plan_ids,
2487 l_child_collection_ids,
2488 l_child_occurrences,
2489 p_plan_id,
2490 p_collection_id,
2491 p_occurrence);
2492
2493 END IF;
2494
2495 END audit_sequence_values;
2496
2497
2498 PROCEDURE audit_sequence_values(p_plan_ids DBMS_SQL.number_table,
2499 p_collection_ids DBMS_SQL.number_table,
2500 p_occurrences DBMS_SQL.number_table,
2501 p_parent_plan_id NUMBER,
2502 p_parent_collection_id NUMBER,
2503 p_parent_occurrence NUMBER) IS
2504
2505 -- This is overloaded procedure. This will be called when
2506 -- all the child, grandchild records (that will be deleted along
2507 -- with parent record ) occurrence, plan ID, coll ID are known.
2508 -- called from qa_parent_child_pkg
2509
2510 BEGIN
2511
2512 -- get all the child, grand child record into collection
2513 get_all_rec_info_for_audit(p_plan_ids,
2514 p_collection_ids,
2515 p_occurrences);
2516
2517 audit_sequence_for_allchild(p_parent_plan_id,
2518 p_parent_collection_id,
2519 p_parent_occurrence);
2520
2521 -- Bug 5114865
2522 -- Resetting the global Arrays
2523 -- nutngare Thu Mar 16 08:32:48 PST 2006
2524 reset_sequence_global_arrays;
2525
2526 EXCEPTION
2527 WHEN OTHERS THEN
2528 Raise;
2529 END audit_sequence_values;
2530
2531 PROCEDURE get_all_rec_info_for_audit(p_plan_ids DBMS_SQL.number_table,
2532 p_collection_ids DBMS_SQL.number_table,
2533 p_occurrences DBMS_SQL.number_table) IS
2534
2535 -- This procedure is similar to create_recordgroup
2536 -- pack all the record information into collection
2537
2538 l_count NUMBER := 0;
2539 l_row_count NUMBER;
2540 i NUMBER;
2541
2542 BEGIN
2543 l_row_count := p_occurrences.COUNT;
2544
2545 -- initialize all collection objects.
2546 QLTTRAWB.init_seq_table(l_row_count);
2547
2548 i := p_occurrences.FIRST;
2549 WHILE i IS NOT NULL LOOP
2550
2551 l_count := l_count + 1;
2552
2553 SELECT occurrence,collection_id,plan_id,txn_header_id,
2554 sequence1,sequence2,sequence3,
2555 sequence4,sequence5,sequence6,
2556 sequence7,sequence8,sequence9,
2557 sequence10,sequence11,sequence12,
2558 sequence13,sequence14,sequence15
2559 INTO
2560 QLTTRAWB.g_occurrence_tab(l_count), QLTTRAWB.g_collection_id_tab(l_count),
2561 QLTTRAWB.g_plan_id_tab(l_count), QLTTRAWB.g_txn_header_id_tab(l_count),
2562 QLTTRAWB.g_seq_tab1(l_count), QLTTRAWB.g_seq_tab2(l_count), QLTTRAWB.g_seq_tab3(l_count),
2563 QLTTRAWB.g_seq_tab4(l_count), QLTTRAWB.g_seq_tab5(l_count), QLTTRAWB.g_seq_tab6(l_count),
2564 QLTTRAWB.g_seq_tab7(l_count), QLTTRAWB.g_seq_tab8(l_count), QLTTRAWB.g_seq_tab9(l_count),
2565 QLTTRAWB.g_seq_tab10(l_count), QLTTRAWB.g_seq_tab11(l_count), QLTTRAWB.g_seq_tab12(l_count),
2566 QLTTRAWB.g_seq_tab13(l_count), QLTTRAWB.g_seq_tab14(l_count), QLTTRAWB.g_seq_tab15(l_count)
2567 FROM qa_results
2568 WHERE plan_id = p_plan_ids(i) AND
2569 collection_id = p_collection_ids(i) AND
2570 occurrence = p_occurrences(i);
2571 i := p_occurrences.NEXT(i);
2572
2573 END LOOP;
2574
2575 END get_all_rec_info_for_audit;
2576
2577 PROCEDURE audit_sequence_for_allchild(p_plan_id NUMBER,
2578 p_collection_id NUMBER,
2579 p_occurrence NUMBER) IS
2580
2581 -- Following cursor will not fetch parent record
2582 -- we are auditing only the child, grand child records.
2583
2584 CURSOR enabled_child_cur IS
2585 SELECT child_plan_id, child_collection_id, child_occurrence,
2586 parent_plan_id, parent_collection_id, parent_occurrence,
2587 level
2588 FROM qa_pc_results_relationship r
2589 WHERE EXISTS (
2590 SELECT 1
2591 FROM qa_results qr
2592 WHERE qr.plan_id = r.child_plan_id AND
2593 qr.collection_id = r.child_collection_id AND
2594 qr.occurrence = r.child_occurrence AND
2595 (qr.status IS NULL or qr.status=2) )
2596 START WITH r.parent_plan_id = p_plan_id AND
2597 r.parent_collection_id = p_collection_id AND
2598 r.parent_occurrence = p_occurrence
2599 CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence
2600 ORDER BY level;
2601
2602 BEGIN
2603
2604 FOR child_rec IN enabled_child_cur LOOP
2605
2606 -- audit one record at a time
2607 audit_sequence_for_currec(child_rec.child_plan_id,
2608 child_rec.child_collection_id,
2609 child_rec.child_occurrence,
2610 child_rec.parent_plan_id,
2611 child_rec.parent_collection_id,
2612 child_rec.parent_occurrence);
2613
2614 END LOOP;
2615
2616 END audit_sequence_for_allchild;
2617
2618 PROCEDURE audit_sequence_for_currec(p_plan_id NUMBER,
2619 p_collection_id NUMBER,
2620 p_occurrence NUMBER,
2621 p_parent_plan_id NUMBER,
2622 p_parent_collection_id NUMBER,
2623 p_parent_occurrence NUMBER) IS
2624
2625 -- This procedure will be called for normal deletion of parent records
2626 -- from FORM.
2627
2628 i NUMBER;
2629 l_cur_rec_indicator NUMBER;
2630 l_seq_value VARCHAR2(40);
2631 l_seq_default_value VARCHAR2(40);
2632 l_user_id NUMBER;
2633 l_login_id NUMBER;
2634 l_date DATE;
2635
2636 BEGIN
2637
2638 l_user_id := fnd_global.user_id;
2639 l_login_id := fnd_global.login_id;
2640 l_date := SYSDATE;
2641
2642 FOR j IN 1..QLTTRAWB.g_occurrence_tab.count LOOP
2643
2644 IF QLTTRAWB.g_occurrence_tab(j) = p_occurrence THEN
2645 l_cur_rec_indicator := j;
2646 END IF;
2647 END LOOP;
2648
2649 get_plan_seq_ele_setup(p_plan_id,p_parent_plan_id);
2650
2651 i := g_curr_plan_seq_char_ids.FIRST;
2652
2653 l_seq_default_value := get_sequence_default_value();
2654
2655 WHILE i IS NOT NULL LOOP
2656
2657 IF NOT g_parent_plan_seq_char_ids.EXISTS(i) THEN -- No copy releation exist
2658
2659 -- get sequence element value
2660 find_update_seq_in_collection(l_cur_rec_indicator,
2661 i,
2662 1,
2663 l_seq_value);
2664 -- capture audit only if seq. element contains pre generated
2665 -- sequence value
2666 IF (l_seq_value IS NOT NULL) AND (l_seq_value <> l_seq_default_value ) THEN
2667 sequence_audit_log(
2668 p_plan_id => p_plan_id,
2669 p_collection_id => p_collection_id,
2670 p_occurrence => p_occurrence,
2671 p_char_id => g_curr_plan_seq_char_ids(i),
2672 p_txn_header_id => QLTTRAWB.g_txn_header_id_tab(l_cur_rec_indicator),
2673 p_sequence_value => l_seq_value,
2674 p_user_id => l_user_id,
2675 p_source_code => 'EQR',
2676 p_source_id => NULL,
2677 p_audit_type => 'DELETED',
2678 p_audit_date => l_date,
2679 p_last_update_date => l_date,
2680 p_last_updated_by => l_user_id,
2681 p_creation_date => l_date,
2682 p_created_by => l_user_id,
2683 p_last_update_login => l_login_id);
2684 END IF;
2685 END IF;
2686
2687 i := g_curr_plan_seq_char_ids.NEXT(i); -- get the next subscript
2688
2689 END LOOP;
2690 END audit_sequence_for_currec;
2691
2692 PROCEDURE audit_seq_for_eres(p_char_id NUMBER,
2693 p_seq_value VARCHAR2,
2694 p_cur_rec_indicator NUMBER) IS
2695 l_user_id NUMBER;
2696 l_login_id NUMBER;
2697 l_date DATE;
2698 BEGIN
2699
2700 l_user_id := fnd_global.user_id;
2701 l_login_id := fnd_global.login_id;
2702 l_date := SYSDATE;
2703
2704 sequence_audit_log(
2705 p_plan_id => QLTTRAWB.g_plan_id_tab(p_cur_rec_indicator),
2706 p_collection_id => QLTTRAWB.g_collection_id_tab(p_cur_rec_indicator),
2707 p_occurrence => QLTTRAWB.g_occurrence_tab(p_cur_rec_indicator),
2708 p_char_id => p_char_id,
2709 p_txn_header_id => QLTTRAWB.g_txn_header_id_tab(p_cur_rec_indicator),
2710 p_sequence_value => p_seq_value,
2711 p_user_id => l_user_id,
2712 p_source_code => 'EDR', -- ERES
2713 p_source_id => NULL, -- eRes ID not generated at this time
2714 p_audit_type => 'CREATED',
2715 p_audit_date => l_date,
2716 p_last_update_date => l_date,
2717 p_last_updated_by => l_user_id,
2718 p_creation_date => l_date,
2719 p_created_by => l_user_id,
2720 p_last_update_login => l_login_id);
2721
2722 END audit_seq_for_eres;
2723
2724 PROCEDURE sequence_audit_log(p_plan_id NUMBER,
2725 p_collection_id NUMBER,
2726 p_occurrence NUMBER,
2727 p_char_id NUMBER,
2728 p_txn_header_id NUMBER,
2729 p_sequence_value VARCHAR2,
2730 p_user_id NUMBER,
2731 p_source_code VARCHAR2,
2732 p_source_id NUMBER,
2733 p_audit_type VARCHAR2,
2734 p_audit_date DATE,
2735 p_last_update_date DATE,
2736 p_last_updated_by NUMBER,
2737 p_creation_date DATE,
2738 p_created_by NUMBER,
2739 p_last_update_login NUMBER) IS
2740
2741 l_rowid VARCHAR2(18) := NULL;
2742
2743 BEGIN
2744 QA_SEQ_AUDIT_PKG.insert_row(
2745 P_Rowid => l_rowid,
2746 P_Plan_Id => p_plan_id,
2747 P_Collection_Id => p_collection_id,
2748 P_Occurrence => p_occurrence,
2749 P_Char_Id => p_char_id,
2750 P_Txn_Header_Id => p_txn_header_id,
2751 P_Sequence_Value => p_sequence_value,
2752 P_User_Id => p_user_id,
2753 P_Source_Code => p_source_code,
2754 P_Source_Id => p_source_id,
2755 P_Audit_Type => p_audit_type,
2756 P_Audit_Date => p_audit_date,
2757 P_Last_Update_Date => p_last_update_date,
2758 P_Last_Updated_By => p_last_updated_by,
2759 P_Creation_Date => p_creation_date,
2760 P_Created_By => p_created_by,
2761 P_Last_Update_Login => p_last_update_login);
2762
2763 END sequence_audit_log;
2764
2765 PROCEDURE delete_auditinfo_for_Txn(p_collection_id NUMBER) IS
2766
2767 -- This procedure is called by eRecords in TXN Mode.
2768 -- For a txn if eRecords are enabled, sequence are generated before
2769 -- eRecord information shown to the user. At sequence generation, we
2770 -- are capturing audit info. for each sequence value generated.
2771 -- If eRecord is accepted then we need to delete the audit information
2772 -- that got captured at generation. If eRecords rejected by user then
2773 -- leave the audit info. as it was.
2774 BEGIN
2775
2776 DELETE FROM qa_seq_audit_history
2777 WHERE collection_id = p_collection_id;
2778 EXCEPTION
2779 WHEN OTHERS THEN raise;
2780
2781 END delete_auditinfo_for_Txn;
2782
2783 PROCEDURE delete_auditinfo_for_DDE(p_txn_header_id NUMBER) IS
2784
2785 -- This procedure is called by eRecords in DDE scanario and is
2786 -- similar to delete_audit_for_Txn(see this proc. for details).
2787
2788 BEGIN
2789
2790 DELETE FROM qa_seq_audit_history
2791 WHERE txn_header_id = p_txn_header_id;
2792 EXCEPTION
2793 WHEN OTHERS THEN raise;
2794
2795 END delete_auditinfo_for_DDE;
2796
2797 -- Gapless Sequence Proj End
2798
2799 --Bug 3160651. Code change Start. rponnusa Thu Sep 25 02:24:28 PDT 2003
2800
2801 PROCEDURE generate_seq_for_DDE(p_txn_header_id NUMBER,
2802 p_plan_id NUMBER,
2803 p_return_status OUT NOCOPY VARCHAR2,
2804 x_message OUT NOCOPY VARCHAR2) IS
2805
2806 -- This is overloaded procedure. Called from self service EQR.
2807 -- Once the seq. numbers are generated, acknowledgement needs to
2808 -- shown with the newly generated seq. values,plan name,element prompt
2809 -- to the user. For this message string needs to be returned back to
2810 -- caller. The x_message string will take the format
2811 -- <plan_name> <char_prompt>=<value>@<plan_name> <char_prompt>=<value>@...
2812
2813 i NUMBER;
2814 l_separator VARCHAR2(1) := ':';
2815 l_delimiter VARCHAR2(1) := '@';
2816
2817 BEGIN
2818
2819 -- flush off any unnecessary values.
2820 g_message_array.DELETE;
2821
2822 generate_seq_for_DDE(p_txn_header_id,
2823 p_plan_id,
2824 p_return_status);
2825
2826 i := g_message_array.FIRST;
2827 WHILE i <= g_message_array.LAST LOOP
2828
2829 -- loop thro' the array and formulate the msg. in required order.
2830 x_message := x_message || l_delimiter ||
2831 g_message_array(i).plan_name || l_separator ||
2832 g_message_array(i).element_prompt || '=' ||
2833 g_message_array(i).sequence_value;
2834
2835 i := g_message_array.NEXT(i);
2836 END LOOP;
2837
2838 x_message := substr(x_message,2); -- remove the starting '@' char.
2839
2840 END generate_seq_for_DDE;
2841
2842 PROCEDURE get_all_char_prompt_info(p_plan_id NUMBER) IS
2843
2844 -- Pack all the sequence element 'prompt' names for the plan
2845 -- into global plsql table.
2846
2847 CURSOR prompt_cur IS
2848 SELECT qpc.prompt,qpc.char_id
2849 FROM qa_plan_chars qpc,
2850 qa_chars qc
2851 WHERE qpc.plan_id = p_plan_id
2852 AND qpc.char_id = qc.char_id
2853 AND qc.datatype = 5;
2854
2855 BEGIN
2856 -- delete all the existing values from the collection.
2857 g_prompt_tab.DELETE;
2858
2859 FOR i IN prompt_cur LOOP
2860 g_prompt_tab(i.char_id) := i.prompt;
2861 END LOOP;
2862 END get_all_char_prompt_info;
2863
2864 PROCEDURE populate_message_array(p_char_id NUMBER,
2865 p_seq_value VARCHAR2,
2866 p_plan_name VARCHAR2) IS
2867 l_message_index NUMBER;
2868 BEGIN
2869 l_message_index := g_message_array.count;
2870
2871 g_message_array(l_message_index).plan_name := p_plan_name;
2872 g_message_array(l_message_index).sequence_value := p_seq_value;
2873 g_message_array(l_message_index).element_prompt := g_prompt_tab(p_char_id);
2874
2875 END populate_message_array;
2876
2877 --Bug 3160651. Code change end. rponnusa Thu Sep 25 02:24:28 PDT 2003
2878
2879 -- Bug 5368983. Generating Sequence Number for OA Txn Integration Flows.
2880 -- saugupta Fri, 01 Sep 2006 02:24:09 -0700 PDT
2881 PROCEDURE generate_seq_for_txninteg(p_collection_id IN NUMBER,
2882 p_return_status OUT nocopy VARCHAR2,
2883 x_message OUT nocopy VARCHAR2) IS
2884
2885 CURSOR all_plan_cur IS
2886 SELECT DISTINCT qr.plan_id
2887 FROM qa_results qr
2888 WHERE qr.collection_id = p_collection_id
2889 AND NOT EXISTS
2890 (SELECT 1
2891 FROM qa_pc_results_relationship qprr
2892 WHERE qprr.child_plan_id = qr.plan_id
2893 AND qprr.child_collection_id = qr.collection_id
2894 AND qprr.child_occurrence = qr.occurrence);
2895
2896 i NUMBER;
2897 l_row_count NUMBER;
2898 l_separator VARCHAR2(1) := ':';
2899 l_delimiter VARCHAR2(1) := '@';
2900 l_module constant VARCHAR2(200) := g_module_name || '.generate_seq_for_txninteg';
2901
2902 BEGIN
2903
2904 IF(fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2905 fnd_log.string(fnd_log.level_procedure, l_module,
2906 'Generating sequence for collection_id = ' || p_collection_id);
2907 END IF;
2908
2909 -- Initialize return status to success
2910 p_return_status := fnd_api.g_ret_sts_success;
2911 -- initialize fnd message table, this is used by self-service api
2912 fnd_msg_pub.initialize;
2913
2914 -- flush off any unnecessary values from sequence message array
2915 g_message_array.DELETE;
2916
2917 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2918 fnd_log.string(fnd_log.level_statement, l_module, 'Calling get_all_record_info ');
2919 END IF;
2920
2921 -- Pack all the sequence values in the data collection into plsql table
2922 l_row_count := get_all_record_info(p_collection_id, NULL);
2923
2924 IF l_row_count = 0 THEN
2925 RETURN;
2926 END IF;
2927
2928 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2929 fnd_log.string(fnd_log.level_statement, l_module, 'Looping through all the plans');
2930 END IF;
2931
2932 FOR plan_rec IN all_plan_cur
2933 LOOP
2934
2935 -- Generate seq value or copy seq value from parent to child rec.
2936 -- this should be done for all the parent rec and its all child,
2937 -- grandchild.. records
2938
2939 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2940 fnd_log.string(fnd_log.level_statement, l_module,
2941 'Calling generate_seq_for_pc for plan_id = ' || plan_rec.plan_id);
2942 END IF;
2943
2944 generate_seq_for_pc(plan_rec.plan_id, p_collection_id, NULL, TRUE);
2945
2946 END LOOP;
2947
2948 -- loop thro' the array and formulate the msg. in required order.
2949 i := g_message_array.FIRST;
2950 WHILE i <= g_message_array.LAST
2951 LOOP
2952 x_message := x_message || l_delimiter || g_message_array(i).plan_name
2953 || l_separator || g_message_array(i).element_prompt
2954 || '=' || g_message_array(i).sequence_value;
2955 i := g_message_array.NEXT(i);
2956 END LOOP;
2957
2958 -- remove the starting '@' char.
2959 x_message := SUBSTR(x_message, 2);
2960
2961
2962 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2963 fnd_log.string(fnd_log.level_statement, l_module,
2964 'Sequence message for Applicable Plans Page ' || x_message);
2965 END IF;
2966
2967 -- Now use bulk update to update all seq. values into qa_results for all the
2968 -- records in the data collection
2969 update_record(l_row_count);
2970
2971 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2972 fnd_log.string(fnd_log.level_statement, l_module,
2973 'Called update_record with row count ' || l_row_count);
2974 END IF;
2975
2976 -- reflect the changes in inline region, so commit
2977 -- saugupta Wed, 05 Dec 2007 03:58:03 -0800 PDT
2978 commit;
2979
2980 EXCEPTION
2981 WHEN others THEN
2982 p_return_status := fnd_api.g_ret_sts_unexp_error;
2983 fnd_message.set_name('QA', 'QA_SEQ_GENERATION_ERROR');
2984 fnd_msg_pub.ADD();
2985
2986 END generate_seq_for_txninteg;
2987
2988 -- Bug 5368983. Added for auditing sequence in OA Txn integ flows.
2989 -- saugupta Fri, 01 Sep 2006 02:33:22 -0700 PDT
2990 --
2991 -- Bug 5955808
2992 -- Added a new parameter to take in the Module name
2993 -- to be set at the time of auditing the sequences
2994 -- ntungare Thu Jul 26 02:55:50 PDT 2007
2995 --
2996 PROCEDURE audit_seq_for_txnint(p_char_id NUMBER,
2997 p_seq_value VARCHAR2,
2998 p_cur_rec_indicator NUMBER,
2999 p_module VARCHAR2 DEFAULT 'OATXNINT') IS
3000
3001 l_user_id NUMBER;
3002 l_login_id NUMBER;
3003 l_date DATE;
3004 l_module VARCHAR2(2000);
3005
3006 BEGIN
3007 If p_module = 'MOBILE' THEN
3008 l_module := g_module_name || '.audit_seq_for_mobile';
3009 Else
3010 l_module := g_module_name || '.audit_seq_for_txnint';
3011 End If;
3012
3013 l_user_id := fnd_global.user_id;
3014 l_login_id := fnd_global.login_id;
3015 l_date := sysdate;
3016
3017 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3018 fnd_log.string(fnd_log.level_statement, l_module,
3019 'Auditing for char_id = ' || p_char_id
3020 || ' Seq val = ' || p_seq_value);
3021 END IF;
3022
3023 sequence_audit_log(
3024 p_plan_id => qlttrawb.g_plan_id_tab(p_cur_rec_indicator),
3025 p_collection_id => qlttrawb.g_collection_id_tab(p_cur_rec_indicator),
3026 p_occurrence => qlttrawb.g_occurrence_tab(p_cur_rec_indicator),
3027 p_char_id => p_char_id,
3028 p_txn_header_id => qlttrawb.g_txn_header_id_tab(p_cur_rec_indicator),
3029 p_sequence_value => p_seq_value,
3030 p_user_id => l_user_id,
3031 p_source_code => p_module, -- OA Transaction Integration /Mobile
3032 p_source_id => NULL, -- Parent transaction not committed till this time
3033 p_audit_type => 'CREATED',
3034 p_audit_date => l_date,
3035 p_last_update_date => l_date,
3036 p_last_updated_by => l_user_id,
3037 p_creation_date => l_date,
3038 p_created_by => l_user_id,
3039 p_last_update_login => l_login_id);
3040
3041 END audit_seq_for_txnint;
3042
3043 -- Bug 5368983. Code changes end
3044
3045 --
3046 -- Bug 5955808
3047 -- New procedure to generate sequences in Mobile Txn
3048 -- This has an additonal message parameter that would
3049 -- return the generated message string to the calling
3050 -- Java program for displaying on the Mobile message
3051 -- page
3052 -- ntungare Mon Jul 23 11:10:18 PDT 2007
3053 --
3054 PROCEDURE generate_seq_for_txn(p_collection_id NUMBER,
3055 p_return_status OUT NOCOPY VARCHAR2,
3056 x_message OUT NOCOPY VARCHAR2) IS
3057
3058 -- This is overloaded procedure. Called from Mobile.
3059 -- Once the seq. numbers are generated, acknowledgement needs to
3060 -- shown with the newly generated seq. values,plan name,element prompt
3061 -- to the user. For this message string needs to be returned back to
3062 -- caller. The x_message string will take the format
3063 -- (cntr)<plan_name> [<char_prompt> (<value>,<value>) <char_prompt> (<value>,<value>)], (cntr)<plan_name> ..
3064 -- eg. (1)NT_P1 [NT_SEQ1(100,110) ,NT_SEQ2(11,12) ], (2)NT_P2 [NT_SEQ1(120) ,NT_SEQ2(13) ]
3065
3066 i VARCHAR2(200);
3067 j BINARY_INTEGER;
3068
3069 mesg_ctr NUMBER := 1;
3070 plan_ctr NUMBER := 1;
3071
3072 prev_plan VARCHAR2(200);
3073
3074 Type seq_tab_typ is table of varchar2(2000) index by varchar2(200);
3075 seq_tab seq_tab_typ;
3076
3077 BEGIN
3078 g_mobile := TRUE;
3079
3080 -- flush off any unnecessary values.
3081 g_message_array.DELETE;
3082
3083 generate_seq_for_txn(p_collection_id,
3084 p_return_status);
3085
3086 -- Formatting of the message string
3087 x_message := ' ';
3088 If g_message_array.count <> 0 Then
3089 j := g_message_array.FIRST;
3090
3091 seq_tab(g_message_array(j).element_prompt) := g_message_array(j).sequence_value;
3092 prev_plan := g_message_array(j).plan_name;
3093 x_message := '(' || plan_ctr || ')' || prev_plan || ' [';
3094
3095 j := g_message_array.next(j);
3096
3097 -- Looping through all the plans in the message array
3098 WHILE j <= g_message_array.LAST LOOP
3099 If (g_message_array(j).plan_name = prev_plan)
3100 then
3101 -- Cumulating the sequences over the elements
3102 If seq_tab.exists(g_message_array(j).element_prompt) THEN
3103 seq_tab(g_message_array(j).element_prompt) := seq_tab(g_message_array(j).element_prompt) || ',' ||
3104 g_message_array(j).sequence_value;
3105 ELSE
3106 seq_tab(g_message_array(j).element_prompt) := g_message_array(j).sequence_value;
3107 END If;
3108 else
3109 If seq_tab.count <>0 then
3110 i := seq_tab.first;
3111 while i <= seq_tab.last
3112 loop
3113 -- Building message string
3114 x_message := x_message || i || '(' || seq_tab(i) || ') ,';
3115 i := seq_tab.next(i);
3116 end loop;
3117 x_message := rtrim(x_message,',');
3118 seq_tab.delete;
3119 end If;
3120 prev_plan := g_message_array(j).plan_name;
3121 plan_ctr := plan_ctr +1;
3122 seq_tab(g_message_array(j).element_prompt) := g_message_array(j).sequence_value;
3123 x_message := x_message || '], (' || plan_ctr || ')' || prev_plan || ' [';
3124 end If;
3125 j := g_message_array.next(j);
3126 end Loop;
3127
3128 If seq_tab.count <>0 then
3129 i := seq_tab.first;
3130 While i <= seq_tab.last
3131 Loop
3132 x_message := x_message || i || '(' || seq_tab(i) || ') ,';
3133 i := seq_tab.next(i);
3134 End Loop;
3135
3136 x_message := rtrim(x_message,',') || ']';
3137 end If;
3138 x_message := 'Following Sequences were generated:' || x_message;
3139 end If;
3140 END generate_seq_for_txn;
3141
3142
3143 END QA_SEQUENCE_API;
[Home] [Help]