1 PACKAGE BODY qa_web_txn_api AS
2 /* $Header: qlttxnwb.plb 120.8.12020000.2 2013/03/28 05:29:10 ntungare ship $ */
3
4 g_module_name CONSTANT VARCHAR2(60):= 'qa.plsql.qa_web_txn_api';
5
6 -- bug 16263093
7 -- Added a global collection to cache the security predicate
8 --
9 TYPE plan_fun_privs_cache_tab_typ IS TABLE OF VARCHAR2(2000) INDEX BY LONG;
10 g_plan_fun_privs_cache_tab plan_fun_privs_cache_tab_typ;
11
12
13 FUNCTION evaluate_triggers (p_context_table in qa_ss_const.ctx_table,
14 p_txn_number IN NUMBER,
15 p_org_id IN NUMBER,
16 p_optimize_flag IN NUMBER,
17 p_mandatory_flag IN NUMBER DEFAULT NULL,
18 p_background_flag IN NUMBER DEFAULT NULL,
19 p_plans_table IN OUT NOCOPY qa_ss_const.num_table)
20 RETURN BOOLEAN IS
21
22 -- The p_optimize_flag parameter is an optimization hint. A value 2 for
23 -- p_optimize_flag means optimize. As as soon as one plan apply we should
24 -- immediately return true. On the other hand, if the p_optimize_flag
25 -- passed is 1 then go ahead and find all the applicable plans
26 -- (populate the out table and then return.
27
28 CURSOR trigger_cur IS
29 SELECT qpt.plan_transaction_id,
30 qpt.plan_id,
31 qc.char_id,
32 qc.dependent_char_id,
33 qc.datatype,
34 qpct.operator,
35 qpct.low_value,
36 qpct.high_value
37 FROM qa_plan_collection_triggers qpct,
38 qa_plan_transactions qpt,
39 qa_plans_val_v qp,
40 qa_chars qc,
41 qa_txn_collection_triggers qtct
42 WHERE qpt.plan_id = qp.plan_id
43 AND qpct.plan_transaction_id(+) = qpt.plan_transaction_id
44 AND qpct.collection_trigger_id = qtct.collection_trigger_id(+)
45 AND qpct.collection_trigger_id = qc.char_id(+)
46 AND qpt.transaction_number = p_txn_number
47 AND qtct.transaction_number(+) = p_txn_number
48 AND qp.organization_id = p_org_id
49 AND qpt.enabled_flag = 1
50 AND qpt.mandatory_collection_flag =
51 NVL(p_mandatory_flag, qpt.mandatory_collection_flag)
52 AND qpt.background_collection_flag =
53 NVL(p_background_flag, qpt.background_collection_flag)
54 ORDER BY qpt.plan_transaction_id;
55
56 type coll_trigg_type IS TABLE OF trigger_cur%ROWTYPE
57 INDEX BY BINARY_INTEGER;
58 coll_trigg_tab coll_trigg_type;
59
60 plan_is_applicable BOOLEAN;
61 counter INTEGER;
62 i INTEGER := 1;
63 l_rowcount INTEGER;
64 l_datatype NUMBER;
65 l_operator NUMBER;
66 l_low_char VARCHAR2(150);
67 l_high_char VARCHAR2(150);
68 l_low_number NUMBER;
69 l_high_number NUMBER;
70 l_low_date DATE;
71 l_high_date DATE;
72 l_value_char VARCHAR2(150);
73 l_value_number NUMBER;
74 l_value_date DATE;
75 l_plan_id NUMBER;
76 l_old_plan_id NUMBER;
77 l_plan_txn_id NUMBER ;
78 l_old_plan_txn_id NUMBER ;
79 l_char_id NUMBER;
80 l_dep_char_id NUMBER;
81 pid_count NUMBER := 0;
82 atleast_one BOOLEAN;
83
84 BEGIN
85
86 atleast_one := FALSE;
87 counter := 1;
88
89 FOR ct_rec IN trigger_cur LOOP
90 coll_trigg_tab(counter) := ct_rec;
91 counter := counter + 1;
92 END LOOP;
93
94 l_rowcount := coll_trigg_tab.count;
95
96 IF (l_rowcount < 1) THEN -- no plans apply
97 RETURN FALSE;
98 END IF;
99
100 l_plan_txn_id := coll_trigg_tab(1).plan_transaction_id;
101
102 -- The variable i has been initialized to 1
103
104 WHILE ( i <= l_rowcount) LOOP
105
106 l_old_plan_txn_id := l_plan_txn_id;
107 plan_is_applicable := TRUE; -- start with this assumption
108
109 WHILE (l_plan_txn_id = l_old_plan_txn_id) AND (i <= l_rowcount) LOOP
110
111 IF (plan_is_applicable = TRUE) THEN
112
113 l_operator := coll_trigg_tab(i).Operator;
114 l_datatype := coll_trigg_tab(i).Datatype;
115 l_char_id := coll_trigg_tab(i).char_id;
116
117 IF (l_operator is NULL) AND (l_datatype is NULL) THEN
118 null; -- null collection trigger. Plan applies
119 ELSE
120 -- watch out for exceptions while accessing
121 -- p_context_table below
122 IF (qltcompb.compare(p_context_table(l_char_id),
123 l_operator, coll_trigg_tab(i).low_value,
124 coll_trigg_tab(i).high_value, l_datatype)) THEN
125 plan_is_applicable := TRUE;
126 ELSE
127 plan_is_applicable := FALSE;
128 END IF; --end qltcompb
129 END IF; -- end l_operator and l_datatype null
130
131 END IF; -- end Check plan applicable is true
132
133 i := i+1;
134
135 IF (i <= l_rowcount) THEN
136 l_plan_txn_id := coll_trigg_tab(i).plan_transaction_id;
137 END IF;
138
139 END LOOP; -- end inner while loop
140
141 IF (plan_is_applicable = TRUE) THEN
142 atleast_one := TRUE;
143 -- if p_optimize_flag is 2, stop here itself and return True
144 IF (p_optimize_flag = 2) THEN
145 RETURN TRUE;
146 END IF;
147 -- if p_optimize_flag is not 2, then keep continuing
148 pid_count := pid_count + 1;
149 -- at very beginning pid_count is ZERO
150 p_plans_table(pid_count) := coll_trigg_tab(i-1).plan_id;
151 END IF;
152
153 END LOOP; -- end outer while loop
154
155 RETURN atleast_one;
156
157 END evaluate_triggers;
158
159
160 FUNCTION check_plan_for_applicability (
161 p_context_table IN qa_ss_const.ctx_table,
162 p_txn_number IN NUMBER,
163 p_org_id IN NUMBER,
164 p_plan_id IN NUMBER)
165 RETURN VARCHAR2 IS
166
167 CURSOR coll_trigg_cur IS
168 SELECT qpt.plan_transaction_id,
169 qpt.plan_id,
170 qc.char_id,
171 qc.dependent_char_id,
172 qc.datatype,
173 qpct.operator,
174 qpct.low_value,
175 qpct.high_value
176 FROM qa_plan_collection_triggers qpct,
177 qa_plan_transactions qpt,
178 qa_plans qp,
179 qa_chars qc,
180 qa_txn_collection_triggers qtct
181 WHERE qp.plan_id = p_plan_id
182 and qpt.plan_id = qp.plan_id
183 and qpct.plan_transaction_id(+) = qpt.plan_transaction_id
184 and qpct.collection_trigger_id = qtct.collection_trigger_id(+)
185 and qpct.collection_trigger_id = qc.char_id(+)
186 and qpt.transaction_number = p_txn_number
187 and qtct.transaction_number(+) = p_txn_number
188 and qp.organization_id = p_org_id
189 and qpt.enabled_flag = 1
190 ORDER BY qpt.plan_transaction_id;
191
192 --
193 -- Bug 2891093
194 -- 'qa_txn_collection_triggers qtct' is used in FROM clause
195 -- which is not required at all.
196 -- Removed this to fix the SQL Repository issue
197 --
198 -- rkunchal Mon Apr 7 21:58:22 PDT 2003
199 --
200 CURSOR coll_trigg_cur_for_asset IS
201 SELECT qpt.plan_transaction_id,
202 qpt.plan_id,
203 qc.char_id,
204 qc.dependent_char_id,
205 qc.datatype,
206 qpct.operator,
207 qpct.low_value,
208 qpct.high_value
209 FROM qa_plan_collection_triggers qpct,
210 qa_plan_transactions qpt,
211 qa_plans qp,
212 qa_chars qc
213 WHERE qp.plan_id = p_plan_id
214 and qpt.plan_id = qp.plan_id
215 and qpct.plan_transaction_id(+) = qpt.plan_transaction_id
216 and qpct.collection_trigger_id = qc.char_id(+)
217 and qpt.transaction_number in (31, 32, 33)
218 and qp.organization_id = p_org_id
219 and qpt.enabled_flag = 1
220 ORDER BY qpt.plan_transaction_id;
221
222 type coll_trigg_type IS TABLE OF coll_trigg_cur%ROWTYPE
223 INDEX BY BINARY_INTEGER;
224 coll_trigg_tab coll_trigg_type;
225
226 plan_is_applicable BOOLEAN;
227 atleast_one BOOLEAN;
228 counter INTEGER;
229 l_rowcount INTEGER;
230 i INTEGER := 1;
231
232 pid_count NUMBER := 0;
233
234 l_datatype NUMBER;
235 l_operator NUMBER;
236 l_low_number NUMBER;
237 l_high_number NUMBER;
238 l_value_number NUMBER;
239 l_plan_id NUMBER;
240 l_old_plan_id NUMBER;
241 l_plan_txn_id NUMBER ;
242 l_old_plan_txn_id NUMBER ;
243 l_char_id NUMBER;
244 l_dep_char_id NUMBER;
245
246 l_low_char VARCHAR2(150);
247 l_high_char VARCHAR2(150);
248 l_value_char VARCHAR2(150);
249
250 l_low_date DATE;
251 l_high_date DATE;
252 l_value_date DATE;
253
254
255 BEGIN
256 atleast_one := FALSE;
257 counter := 1;
258
259 if p_txn_number = 32 then
260 FOR ct_rec in coll_trigg_cur_for_asset LOOP
261 coll_trigg_tab(counter) := ct_rec;
262 counter := counter + 1;
263 END LOOP;
264 else
265 FOR ct_rec in coll_trigg_cur LOOP
266 coll_trigg_tab(counter) := ct_rec;
267 counter := counter + 1;
268 END LOOP;
269 end if;
270
271 l_rowcount := coll_trigg_tab.count;
272
273 IF (l_rowcount < 1) THEN
274 RETURN 'N'; -- no plans applicable
275 END IF;
276
277 l_plan_txn_id := coll_trigg_tab(1).plan_transaction_id;
278
279 -- The variable i has been initialized to 1
280
281 WHILE ( i <= l_rowcount) LOOP
282 l_old_plan_txn_id := l_plan_txn_id;
283 plan_is_applicable := TRUE; -- start with this assumption
284
285 WHILE (l_plan_txn_id = l_old_plan_txn_id) AND (i <= l_rowcount) LOOP
286 IF (plan_is_applicable = TRUE) THEN
287 l_operator := coll_trigg_tab(i).Operator;
288 l_datatype := coll_trigg_tab(i).Datatype;
289 l_char_id := coll_trigg_tab(i).char_id;
290
291 IF (l_operator is NULL) AND (l_datatype is NULL) THEN
292 null;
293 -- null collection trigger. Plan applies
294 ELSE
295 -- WATCH OUT FOR EXCEPTIONS while
296 -- accessing Ctx table below
297 IF (qltcompb.compare( p_context_table(l_char_id),
298 l_operator, coll_trigg_tab(i).Low_value,
299 coll_trigg_tab(i).High_Value,l_datatype)) THEN
300 plan_is_applicable := TRUE;
301 ELSE
302 plan_is_applicable := FALSE;
303 END IF; --end qltcompb
304 END IF; -- end l_operator and l_datatype null
305 END IF; -- end Check plan applicable is true
306
307 i := i+1;
308 IF (i <= l_rowcount) THEN
309 l_plan_txn_id := coll_trigg_tab(i).plan_transaction_id;
310 END IF;
311 END LOOP; -- end inner while loop
312 IF (plan_is_applicable = TRUE) THEN
313 RETURN 'Y';
314 END IF;
315
316 -- if flag is not 2, then keep continuing
317
318 END LOOP; -- end outer while loop
319
320 RETURN 'N';
321
322 END check_plan_for_applicability;
323
324
325 FUNCTION plan_applicable_for_txn ( p_plan_id IN NUMBER,
326 p_txn_number IN NUMBER default null)
327 RETURN BOOLEAN IS
328
329 CURSOR txn_plans IS
330 SELECT qpt.plan_id
331 FROM qa_plan_transactions qpt
332 WHERE qpt.plan_id = p_plan_id
333 AND qpt.transaction_number = p_txn_number;
334
335 CURSOR txn_plans_for_asset IS
336 SELECT qpt.plan_id
337 FROM qa_plan_transactions qpt
338 WHERE qpt.plan_id = p_plan_id
339 AND qpt.transaction_number in (31, 32, 33);
340
341 result BOOLEAN;
342 dummy NUMBER;
343
344 BEGIN
345
346 -- This procedure quickly determines if a colleciton plan
347 -- applies at all to a transaction wihtout taking into
348 -- complexity of collection triggers.
349
350 if (p_txn_number = 32) then
351
352 OPEN txn_plans_for_asset;
353 FETCH txn_plans_for_asset INTO dummy;
354 result := txn_plans_for_asset%FOUND;
355 CLOSE txn_plans_for_asset;
356
357 else
358
359 OPEN txn_plans;
360 FETCH txn_plans INTO dummy;
361 result := txn_plans%FOUND;
362 CLOSE txn_plans;
363
364 end if;
365
366 RETURN result;
367
368 END plan_applicable_for_txn;
369
370
371 FUNCTION plan_applies ( p_plan_id IN NUMBER,
372 p_txn_number IN NUMBER, -- DEFAULT NULL
373 p_org_id IN NUMBER, -- DEFAULT NULL
374 pk1 IN VARCHAR2, -- DEFAULT NULL
375 pk2 IN VARCHAR2, -- DEFAULT NULL
376 pk3 IN VARCHAR2, -- DEFAULT NULL
377 pk4 IN VARCHAR2, -- DEFAULT NULL
378 pk5 IN VARCHAR2, -- DEFAULT NULL
379 pk6 IN VARCHAR2, -- DEFAULT NULL
380 pk7 IN VARCHAR2, -- DEFAULT NULL
381 pk8 IN VARCHAR2, -- DEFAULT NULL
382 pk9 IN VARCHAR2, -- DEFAULT NULL
383 pk10 IN VARCHAR2, -- DEFAULT NULL
384 p_txn_name IN VARCHAR2) -- DEFAULT NULL)
385 RETURN VARCHAR2 IS
386
387 l_context_table qa_ss_const.ctx_table;
388
389 BEGIN
390 -- This function is called to figure out if a particular plan applies
391 -- to the transaction and the associated context. For EAM this is
392 -- called before rendering the lsit of plans page.
393
394 -- bug 3189850. rkaza. 01/30/2003.
395 -- This check is not needed when coming from asset txn because
396 -- now only plans that are associated with the transaction come here
397 -- for context check. Modified the VO to have this check in the VO
398 -- itself, for perf reasons.
399 -- Also please note that the same change need to be made for other
400 -- EAM txns too. But list of plans VO for other EAM txns now belongs
401 -- to EAM code (they have duplicated it in 11i10). So needs a fix from
402 -- their side. Temporarily leaving it as it is.
403 if p_txn_number <> 32 then
404 IF NOT plan_applicable_for_txn(p_plan_id, p_txn_number) THEN
405 RETURN 'N';
406 END IF;
407 end if;
408
409 -- IF (p_txn_number = 31) THEN
410
411 l_context_table(qa_ss_const.asset_group) := pk1;
412 l_context_table(qa_ss_const.asset_number) := pk2;
413 l_context_table(qa_ss_const.asset_activity) := pk3;
414 l_context_table(qa_ss_const.work_order) := pk4;
415 l_context_table(qa_ss_const.maintenance_op_seq) := pk5;
416 l_context_table(qa_ss_const.asset_instance_number) := pk6; --dgupta: R12 EAM Integration. Bug 4345492
417
418 -- END IF;
419
420 RETURN check_plan_for_applicability(l_context_table, p_txn_number,
421 p_org_id, p_plan_id);
422
423 END plan_applies;
424
425
426 FUNCTION get_mandatory_optional_info (p_plan_id NUMBER, p_txn_number IN NUMBER)
427 RETURN VARCHAR2 IS
428
429 l_plan_type VARCHAR2(240) DEFAULT 'N/A';
430
431 CURSOR c IS
432 SELECT decode(mandatory_collection_flag, 1, 'Mandatory', 2, 'Optional')
433 FROM qa_plan_transactions
434 WHERE plan_id = p_plan_id
435 AND transaction_number = p_txn_number
436 AND enabled_flag = 1;
437
438 BEGIN
439
440 -- This function determines given a plan and transactions number
441 -- whether this plan is mandatory or optional.
442
443 IF background_plan(p_plan_id, p_txn_number) = 'Y' THEN
444 l_plan_type := 'Background';
445 ELSE
446 OPEN c;
447 FETCH c INTO l_plan_type;
448 CLOSE c;
449 END IF;
450
451 RETURN l_plan_type;
452
453 END get_mandatory_optional_info;
454
455
456 FUNCTION background_plan ( p_plan_id IN NUMBER, p_txn_number IN NUMBER)
457 RETURN VARCHAR2 IS
458
459 l_result VARCHAR2(1) DEFAULT 'N';
460
461 CURSOR c IS
462 SELECT 'Y'
463 FROM qa_plan_transactions
464 WHERE plan_id = p_plan_id
465 AND transaction_number = p_txn_number
466 AND background_collection_flag = 1;
467
468 BEGIN
469
470 -- This function is called to figure out if a particular plan is
471 -- background plan or not
472
473 OPEN c;
474 FETCH c INTO l_result;
475 CLOSE c;
476
477 RETURN l_result;
478
479 END background_plan;
480
481
482 FUNCTION get_user_name
483 RETURN VARCHAR2 IS
484
485 l_user_id NUMBER;
486 l_user_name VARCHAR2(30);
487 l_customer_id NUMBER;
488
489 CURSOR c (p_user_id NUMBER) IS
490 SELECT NVL(customer_id, -1)
491 FROM fnd_user
492 WHERE user_id = p_user_id;
493
494 BEGIN
495
496 l_user_id := fnd_global.user_id;
497
498 OPEN c(l_user_id);
499 FETCH c INTO l_customer_id;
500 CLOSE c;
501
502 l_user_name := 'HZ_PARTY:'||to_char(l_customer_id);
503
504 RETURN l_user_name;
505
506 END get_user_name;
507
508 --
509 -- Bug 16263093
510 -- Made the function deterministic to improve performance
511 --
512 FUNCTION allowed_for_plan ( p_function_name IN VARCHAR2, p_plan_id IN NUMBER)
513 RETURN VARCHAR2 DETERMINISTIC IS
514
515 l_result VARCHAR2(1) DEFAULT 'F';
516 l_profile_value NUMBER DEFAULT NULL;
517 l_user_name VARCHAR2(30);
518 dummy NUMBER;
519
520 CURSOR c IS
521 SELECT PLAN_RELATIONSHIP_ID
522 FROM QA_PC_PLAN_RELATIONSHIP
523 WHERE CHILD_PLAN_ID = p_plan_id
524 AND DATA_ENTRY_MODE = 4;
525
526 BEGIN
527
528 -- Bug 3412523 ksoh Fri Jan 30 11:38:19 PST 2004
529 -- should return false for Update/Entry/Delete of a
530 -- plan that has history relationship with another parent plan(s)
531 -- regardless of the security profile.
532 IF (p_function_name = 'QA_RESULTS_ENTER' OR
533 p_function_name = 'QA_RESULTS_UPDATE' OR
534 p_function_name = 'QA_RESULTS_DELETE') THEN
535 OPEN c;
536 FETCH c INTO dummy;
537 IF c%FOUND THEN
538 CLOSE c;
539 RETURN 'F';
540 END IF;
541 CLOSE c;
542 END IF;
543
544 l_profile_value := fnd_profile.value('QA_SECURITY_USED');
545
546 -- 2 is no, 1 is yes
547 IF (l_profile_value = 2) OR (l_profile_value IS NULL) THEN
548 l_result := 'T';
549 ELSE
550 --
551 -- Bug 16263093
552 -- Check if the security predicate is already cached
553 -- If yes, then retrieve from cache else cache it
554 --
555 IF g_plan_fun_privs_cache_tab.exists(p_plan_id||p_function_name) THEN
556 l_result := g_plan_fun_privs_cache_tab(p_plan_id||p_function_name);
557 ELSE
558 l_user_name := get_user_name;
559
560 -- Bug 4465241
561 -- ATG Mandatory Fix: Deprecated API
562 -- removing p_user_name
563 -- saugupta Mon, 27 Jun 2005 06:21:00 -0700 PDT
564 l_result := fnd_data_security.check_function
565 (p_api_version => 1.0,
566 p_function => p_function_name,
567 p_object_name => 'QA_PLANS',
568 p_instance_pk1_value => p_plan_id,
569 p_instance_pk2_value => NULL,
570 p_instance_pk3_value => NULL,
571 p_instance_pk4_value => NULL,
572 p_instance_pk5_value => NULL);
573 -- p_user_name => l_user_name);
574
575 g_plan_fun_privs_cache_tab(p_plan_id||p_function_name) := l_result;
576 END IF;
577 END IF;
578
579 RETURN l_result;
580
581 END allowed_for_plan;
582
583
584 FUNCTION quality_plans_applicable (p_txn_number IN NUMBER,
585 p_organization_id IN NUMBER, -- DEFAULT NULL
586 pk1 IN VARCHAR2, -- DEFAULT NULL
587 pk2 IN VARCHAR2, -- DEFAULT NULL
588 pk3 IN VARCHAR2, -- DEFAULT NULL
589 pk4 IN VARCHAR2, -- DEFAULT NULL
590 pk5 IN VARCHAR2, -- DEFAULT NULL
591 pk6 IN VARCHAR2, -- DEFAULT NULL
592 pk7 IN VARCHAR2, -- DEFAULT NULL
593 pk8 IN VARCHAR2, -- DEFAULT NULL
594 pk9 IN VARCHAR2, -- DEFAULT NULL
595 pk10 IN VARCHAR2, -- DEFAULT NULL
596 p_txn_name IN VARCHAR2) -- DEFAULT NULL)
597 RETURN VARCHAR2 IS
598
599 l_context_table qa_ss_const.ctx_table;
600 l_plans_table qa_ss_const.num_table;
601 result BOOLEAN;
602 return_value VARCHAR2(1) DEFAULT 'N';
603
604 BEGIN
605
606 -- This function will be called from parent page to determine
607 -- if they will show Quality Button on their page or not.
608 -- They will pass the context information through pk variables.
609 -- Note that these will contain different values for different
610 -- transactions.
611
612
613 -- IF (p_txn_number = qa_ss_const.eam_work_order_completion_txn) THEN
614
615 -- The following are the context elements for work order completions.
616 -- Ordered By char id
617 --
618 -- pk1 -> asset group
619 -- pk2 -> asset number
620 -- pk3 -> asset activity
621 -- pk4 -> work order number
622 -- pk5 -> step
623
624 l_context_table(qa_ss_const.asset_group) := pk1;
625 l_context_table(qa_ss_const.asset_number) := pk2;
626 l_context_table(qa_ss_const.asset_activity) := pk3;
627 l_context_table(qa_ss_const.work_order) := pk4;
628 l_context_table(qa_ss_const.maintenance_op_seq) := pk5;
629 l_context_table(qa_ss_const.asset_instance_number) := pk6; --dgupta: R12 EAM Integration. Bug 4345492
630
631
632
633 --dbms_output.put_line(p_txn_number || ' ' || p_organization_id);
634 --dbms_output.put_line(l_context_table(qa_ss_const.asset_group));
635 --dbms_output.put_line(l_context_table(qa_ss_const.asset_number));
636 --dbms_output.put_line(l_context_table(qa_ss_const.asset_activity));
637 --dbms_output.put_line(l_context_table(qa_ss_const.work_order));
638 --dbms_output.put_line(l_context_table(qa_ss_const.step));
639
640 result := evaluate_triggers (
641 p_context_table => l_context_table,
642 p_txn_number => p_txn_number,
643 p_org_id => p_organization_id,
644 p_optimize_flag => 2,
645 p_plans_table => l_plans_table);
646
647
648 IF result = TRUE THEN
649 return_value := 'Y';
650 END IF;
651
652 -- END IF;
653
654 RETURN return_value;
655
656 END quality_plans_applicable;
657
658
659 FUNCTION quality_mandatory_plans_remain (p_txn_number IN NUMBER,
660 p_organization_id IN NUMBER, -- DEFAULT NULL
661 pk1 IN VARCHAR2, -- DEFAULT NULL
662 pk2 IN VARCHAR2, -- DEFAULT NULL
663 pk3 IN VARCHAR2, -- DEFAULT NULL
664 pk4 IN VARCHAR2, -- DEFAULT NULL
665 pk5 IN VARCHAR2, -- DEFAULT NULL
666 pk6 IN VARCHAR2, -- DEFAULT NULL
667 pk7 IN VARCHAR2, -- DEFAULT NULL
668 pk8 IN VARCHAR2, -- DEFAULT NULL
669 pk9 IN VARCHAR2, -- DEFAULT NULL
670 pk10 IN VARCHAR2, -- DEFAULT NULL
671 p_txn_name IN VARCHAR2, -- DEFAULT NULL
672 p_list_of_plans IN VARCHAR2,-- DEFAULT NULL
673 p_collection_id IN NUMBER, -- DEFAULT NULL,
674 p_wip_entity_id IN NUMBER) -- DEFAULT NULL
675 RETURN VARCHAR2 IS
676
677 l_module constant varchar2(200) := g_module_name||'.quality_mandatory_plans_remain';
678 l_log boolean := ((FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) and
679 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module));
680 l_plog boolean := l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
681 l_slog boolean := l_plog and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
682 l_context_table qa_ss_const.ctx_table;
683 l_plans_table qa_ss_const.num_table;
684 result BOOLEAN;
685 return_value VARCHAR2(1) DEFAULT 'N';
686 i NUMBER;
687 l_plan_token VARCHAR2(30);
688 l_results_entered VARCHAR2(1);
689 l_wip_entity_id NUMBER := p_wip_entity_id;
690 BEGIN
691 --dgupta: Start R12 EAM Integration. Bug 4345492
692 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
693 'Start of ' || l_module || '(' || 'p_txn_number=' || p_txn_number
694 || ',p_organization_id='|| p_organization_id|| ',pk1='|| pk1|| ',pk2='|| pk2
695 || ',pk3='|| pk3|| ', pk4='|| pk4|| ', pk5='|| pk5 || ', pk6='|| pk6|| ',p_txn_name='|| p_txn_name
696 || ',p_list_of_plans='|| p_list_of_plans || ',p_collection_id='|| p_collection_id
697 || ',p_wip_entity_id='|| p_wip_entity_id|| ')');
698 end if;
699 l_context_table(qa_ss_const.asset_group) := pk1;
700 l_context_table(qa_ss_const.asset_number) := pk2;
701 l_context_table(qa_ss_const.asset_activity) := pk3;
702 l_context_table(qa_ss_const.work_order) := pk4;
703 l_context_table(qa_ss_const.maintenance_op_seq) := pk5;
704 l_context_table(qa_ss_const.asset_instance_number) := pk6;
705 --dgupta: End R12 EAM Integration. Bug 4345492
706
707 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
708 'Calling evaluate_triggers');
709 end if;
710 result := evaluate_triggers (
711 p_context_table => l_context_table,
712 p_txn_number => p_txn_number,
713 p_org_id => p_organization_id,
714 p_optimize_flag => 1,
715 p_mandatory_flag => 1,
716 p_background_flag => 2,
717 p_plans_table => l_plans_table);
718 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
719 'evaluate_triggers returned. No. of mandatory plans: '|| l_plans_table.count);
720 end if;
721
722 i := l_plans_table.FIRST;
723 if (p_txn_number = 31 or p_txn_number = 33) then
724 if (l_wip_entity_id is null and l_plans_table.count > 0) then
725 select wip_entity_id into l_wip_entity_id
726 from wip_entities where wip_entity_name = pk4
727 and organization_id = p_organization_id;
728 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
729 'l_wip_entity_id: '|| l_wip_entity_id);
730 end if;
731 end if;
732 elsif (i > 0) AND ( length(p_list_of_plans) = 2) THEN
733 RETURN 'Y';
734 end if;
735 WHILE (i <> l_plans_table.LAST +1) LOOP
736 l_results_entered := 'N';
737 begin
738 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
739 'l_plans_table('||i||'): ' || l_plans_table(i));
740 end if;
741 --
742 -- Bug 7685429
743 -- Added condition to check for transaction_number in case of
744 -- operation/work order completion.
745 -- skolluku
746 --
747 if (p_txn_number = 31) then --maintenance work order completion
748 select 'Y' into l_results_entered
749 from dual where exists (
750 select collection_id from QA_RESULTS
751 where organization_id = p_organization_id
752 and work_order_id =l_wip_entity_id
753 and (maintenance_op_seq is null or transaction_number = 31)
754 and plan_id = l_plans_table(i)
755 and (status is null or status=2 or --results be either enabled or belong to p_collection_id
756 (p_collection_id is not null and collection_id = p_collection_id))
757 );
758 elsif (p_txn_number = 33) then --maintenance op completion
759 select 'Y' into l_results_entered from dual
760 where exists (
761 select collection_id from QA_RESULTS
762 where organization_id = p_organization_id
763 and work_order_id =l_wip_entity_id
764 and maintenance_op_seq = pk5
765 and transaction_number = 33
766 and plan_id = l_plans_table(i)
767 and (status is null or status=2 or --results be either enabled or belong to p_collection_id
768 (p_collection_id is not null and collection_id = p_collection_id))
769 );
770 else --all other txns
771 l_plan_token := '@' || l_plans_table(i) || '@';
772 if (instr(p_list_of_plans, l_plan_token) <> 0) then
773 l_results_entered := 'Y';
774 end if;
775 end if;
776 if (l_results_entered is null) then
777 l_results_entered := 'N';
778 end if;
779 if (l_slog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
780 'l_results_entered('||i||'): ' || l_results_entered);
781 end if;
782 exception
783 when no_data_found then
784 RETURN 'Y';
785 end;
786 IF (l_results_entered = 'N') THEN
787 RETURN 'Y';
788 END IF;
789 i := l_plans_table.NEXT(i);
790 END LOOP;
791 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
792 'Exiting ' || l_module
793 || '. No mandatory qa plans remain. Return value: '|| return_value );
794 end if;
795 RETURN return_value;
796 END quality_mandatory_plans_remain;
797
798
799 PROCEDURE quality_post_commit_processing (p_collection_id IN NUMBER,
800 p_return_status OUT NOCOPY VARCHAR2,
801 x_msg_count OUT NOCOPY NUMBER,
802 x_msg_data OUT NOCOPY VARCHAR2) IS
803 BEGIN
804 -- This procedure shoots off the background actions after
805 -- parent transactions have committed.
806
807 qa_results_pub.enable_and_fire_action (
808 p_api_version => 1.0,
809 p_commit => FND_API.G_TRUE,
810 p_collection_id => p_collection_id,
811 x_return_status => p_return_status,
812 x_msg_count => x_msg_count,
813 x_msg_data => x_msg_data);
814
815 END quality_post_commit_processing;
816
817
818
819 PROCEDURE post_background_results(
820 p_txn_number IN NUMBER,
821 p_org_id IN NUMBER,
822 p_context_values IN VARCHAR2,
823 p_collection_id IN NUMBER) IS
824
825 l_plan_id NUMBER;
826 elements qa_txn_grp.ElementsArray;
827
828 Cursor c1(txn_no number, org_id number, col_id number) is
829 SELECT DISTINCT qpt.plan_id
830 FROM qa_plan_transactions qpt, qa_plans qp
831 WHERE qpt.transaction_number = txn_no
832 AND qpt.plan_id = qp.plan_id
833 AND qp.organization_id = org_id
834 AND trunc(sysdate) between
835 nvl(trunc(qp.effective_from), trunc(sysdate)) and
836 nvl(trunc(qp.effective_to), trunc(sysdate))
837 AND qpt.enabled_flag = 1
838 AND qpt.background_collection_flag = 1
839 AND NOT EXISTS
840 (SELECT 1
841 FROM qa_results qr
842 WHERE qr.plan_id = qpt.plan_id
843 AND qr.collection_id = col_id);
844
845 BEGIN
846 --bug 4995406
847 --Checking if the Txn is an EAM transaction
848 --ntungare Wed Feb 22 06:57:05 PST 2006
849 If p_txn_number in (qa_ss_const.eam_checkin_txn,
850 qa_ss_const.eam_checkout_txn,
851 qa_ss_const.eam_operation_txn,
852 qa_ss_const.eam_work_order_txn) THEN
853 -- CAll the procedure to process the result
854 -- Collection for Background Plans for EAM Txn
855 -- ntungare Wed Feb 22 07:48:02 PST 2006
856 qa_txn_grp.eam_post_background_results(p_txn_number => p_txn_number,
857 p_org_id => p_org_id,
858 p_context_values => p_context_values,
859 p_collection_id => p_collection_id);
860 ELSE
861 elements := qa_txn_grp.result_to_array(p_context_values);
862 OPEN c1(p_txn_number, p_org_id, p_collection_id);
863 LOOP
864 FETCH c1 INTO l_plan_id;
865 EXIT WHEN c1%NOTFOUND;
866 qa_txn_grp.insert_results(l_plan_id, p_org_id, p_collection_id, elements);
867 END LOOP;
868 CLOSE c1;
869 END IF;
870 END post_background_results;
871
872
873 --
874 -- Tracking Bug 4343758. Fwk Integration.
875 -- Currently there is no simple metamodel to look up
876 -- which transactions are enabled for Workbench.
877 -- So, we do a hard check here. When there is
878 -- datamodel available, this can be changed to
879 -- select from the db.
880 --
881 -- Return fnd_api.g_true if p_txn is enabled for OAF
882 -- transaction integration; else fnd_api.g_false.
883 -- bso Fri May 20 14:01:25 PDT 2005
884 --
885 --
886 FUNCTION is_workbench_txn(p_txn IN NUMBER)
887 RETURN VARCHAR2 IS
888
889 BEGIN
890
891 IF p_txn IN (
892 qa_ss_const.wip_move_txn,
893 qa_ss_const.wip_completion_txn,
894 qa_ss_const.flow_work_order_less_txn,
895 qa_ss_const.flow_line_op_txn,
896 qa_ss_const.osfm_move_txn) THEN
897 RETURN fnd_api.g_true;
898 END IF;
899
900 RETURN fnd_api.g_false;
901 END is_workbench_txn;
902
903 -- Bug 4343758. Oa Fwk Integration Project.
904 -- New API used to get information on mandatory
905 -- result entry.
906 -- srhariha. Mon May 2 00:33:26 PDT 2005.
907
908 FUNCTION get_result_entered(p_plan_id IN NUMBER,
909 p_collection_id IN NUMBER)
910 RETURN VARCHAR2 IS
911
912 CURSOR c1(x_plan_id NUMBER, x_collection_id NUMBER, x_status NUMBER) IS
913 SELECT occurrence
914 FROM QA_RESULTS
915 WHERE plan_id = x_plan_id
916 AND collection_id = x_collection_id
917 AND status = x_status;
918
919 l_occurrence NUMBER;
920
921 BEGIN
922
923
924 OPEN C1(p_plan_id,p_collection_id,1);
925 FETCH C1 INTO l_occurrence;
926 CLOSE C1;
927
928 IF (l_occurrence IS NULL) THEN
929 RETURN 'N';
930 END IF;
931
932 RETURN 'Y';
933
934 END get_result_entered;
935
936 -- Bug 4519559. Oa Fwk Integration Project. UT bug fix.
937 -- Return fnd_api.g_true if p_txn is a mobile txn
938 -- else return fnd_api.g_false
939 -- srhariha. Tue Aug 2 01:37:53 PDT 2005
940
941 -- Bug 4519558.OA Framework Integration project. UT bug fix.
942 -- Incorporating Bryan's code review comments. Moved the
943 -- method to qa_mqa_mwa_api package.
944 -- srhariha. Mon Aug 22 02:50:35 PDT 2005.
945
946 /*
947 FUNCTION is_mobile_txn(p_txn IN NUMBER)
948 RETURN VARCHAR2 IS
949
950 BEGIN
951
952 IF p_txn IN (qa_ss_const.mob_move_txn,
953 qa_ss_const.mob_scrap_reject_txn,
954 qa_ss_const.mob_return_txn,
955 qa_ss_const.mob_completion_txn,
956 qa_ss_const.mob_wo_less_txn,
957 qa_ss_const.mob_flow_txn,
958 qa_ss_const.mob_material_txn,
959 qa_ss_const.mob_move_and_complete_txn,
960 qa_ss_const.mob_return_and_move_txn,
961 qa_ss_const.mob_ser_move_txn,
962 qa_ss_const.mob_ser_scrap_rej_txn,
963 qa_ss_const.mob_ser_return_txn,
964 qa_ss_const.mob_ser_completion_txn,
965 qa_ss_const.mob_ser_material_txn,
966 qa_ss_const.mob_ser_move_and_comp_txn,
967 qa_ss_const.mob_ser_return_and_move_txn,
968 qa_ss_const.mob_lpn_inspection_txn,
969 qa_ss_const.mob_recv_inspection_txn,
970 qa_ss_const.wms_lpn_based_txn) THEN
971 RETURN fnd_api.g_true;
972 END IF;
973
974 RETURN fnd_api.g_false;
975
976 END is_mobile_txn;
977 */
978
979 END qa_web_txn_api;
[Home] [Help]