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