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