[Home] [Help]
PACKAGE BODY: APPS.QA_SKIPLOT_UTILITY
Source
1 PACKAGE BODY QA_SKIPLOT_UTILITY AS
2 /* $Header: qaslutlb.pls 120.1 2006/02/15 08:43:15 ntungare noship $ */
3
4 --
5 -- local function
6 --
7 function get_first_rule_num(
8 p_process_plan_id in number)return number is
9
10 cursor fr (pp_id in number) is
11 select min(rule_seq)
12 from qa_skiplot_process_plan_rules
13 where process_plan_id = pp_id;
14
15 first_rule number;
16
17 begin
18 open fr (p_process_plan_id);
19 fetch fr into first_rule;
20
21 if fr%notfound or first_rule is null then
22 first_rule := -1;
23 end if;
24 close fr;
25 return first_rule;
26 end get_first_rule_num;
27
28 --
29 -- local function
30 --
31 function get_last_lot_date(
32 p_criteria_id in number,
33 p_receipt_date in date default null)return date is
34
35 prev_date date;
36
37 cursor p (p_criteria_id in number, p_receipt_date in date) is
38 select qsrr.receipt_date
39 from qa_skiplot_rcv_results qsrr
40 where insp_lot_id = (select max(qsrr2.insp_lot_id)
41 from qa_skiplot_rcv_results qsrr2
42 where qsrr2.criteria_id = p_criteria_id and
43 qsrr2.receipt_date < p_receipt_date);
44
45 begin
46
47 open p (p_criteria_id, p_receipt_date);
48 fetch p into prev_date;
49 close p;
50
51 --
52 -- if no previous date, assign current date to it
53 -- which will make date range check automatically true
54 --
55 if p%notfound then
56 prev_date := p_receipt_date;
57 end if;
58
59 return prev_date;
60
61 end get_last_lot_date;
62
63 --
64 --local function
65 --
66 function check_adjacent_date(
67 p_receipt_date in date default null,
68 p_plan_state in plan_state_rec)return varchar2 is
69
70 prev_date date;
71 day_range number;
72
73 begin
74
75 --
76 -- if no date range restriction is setup
77 -- return true
78 --
79 day_range := p_plan_state.adjacent_days;
80 if day_range is null then
81 --
82 -- no adjacent date restriction
83 --
84 return fnd_api.g_true;
85 end if;
86
87 prev_date := p_plan_state.last_receipt_date;
88 if trunc(nvl(p_receipt_date, sysdate)) - trunc(prev_date)
89 <= day_range then
90 return fnd_api.g_true;
91 else
92 return fnd_api.g_false;
93 end if;
94 end check_adjacent_date;
95
96 --
97 -- local function
98 --
99 function check_date_span(
100 p_plan_state in plan_state_rec)return varchar2 is
101
102 x_next_lotid number;
103 x_next_receipt_date date;
104
105 --
106 -- get the lotid and receipt date right after
107 -- the rule start lotid and receipt date
108 --
109 cursor new_start_lot (x_rule_start_lotid number, x_criteria_id number,
110 x_process_id number) is
111 select q1.insp_lot_id, q1.receipt_date
112 from qa_skiplot_rcv_results q1
113 where q1.insp_lot_id = (select min(q2.insp_lot_id)
114 from qa_skiplot_rcv_results q2
115 where q2.insp_lot_id > x_rule_start_lotid and
116 q2.criteria_id = x_criteria_id and
117 q2.process_id = x_process_id);
118
119 begin
120
121
122 if p_plan_state.last_receipt_date is null or
123 p_plan_state.rule_start_date is null or
124 p_plan_state.day_span is null or
125 trunc(p_plan_state.last_receipt_date) - trunc(p_plan_state.rule_start_date)
126 <= p_plan_state.day_span then
127
128
129 return fnd_api.g_true;
130
131 --
132 -- if day span restriction is violated, update
133 -- rule_start_lot_id and rule_start_date with
134 -- the next lot id and receipt date to prepare
135 -- next comparison.
136 --
137 else
138
139 open new_start_lot (p_plan_state.rule_start_lot_id,
140 p_plan_state.criteria_id, p_plan_state.process_id);
141 fetch new_start_lot into x_next_lotid, x_next_receipt_date;
142 close new_start_lot;
143
144 if x_next_lotid is not null and x_next_receipt_date is not null then
145
146 update qa_skiplot_plan_states
147 set rule_start_lot_id = x_next_lotid,
148 rule_start_date = x_next_receipt_date
149 where process_plan_id = p_plan_state.process_plan_id and
150 criteria_id = p_plan_state.criteria_id;
151 --
152 -- this case should not happen
153 --
154 else
155 insert_error_log (
156 p_module_name =>'QA_SKIPLOT_UTILITY.check_date_span',
157 p_error_message => 'Next lotid or next receipt date is null');
158 end if;
159
160 return fnd_api.g_false;
161
162 end if;
163
164 end check_date_span;
165
166
167
168 FUNCTION CHECK_SKIPLOT_AVAILABILITY (
169 p_txn IN NUMBER,
170 p_organization_id IN NUMBER) RETURN VARCHAR2 IS
171
172 -- Check in as Bug 2917141
173 -- Performance standard.
174 -- l_qa_installation VARCHAR2(1) := fnd_api.g_false;
175 -- l_skiplot_control VARCHAR2(1) := fnd_api.g_false;
176 -- l_skiplot_setup VARCHAR2(1) := fnd_api.g_false;
177 -- l_qa_inspection VARCHAR2(1) := fnd_api.g_false;
178
179 l_qa_installation VARCHAR2(1);
180 l_skiplot_control VARCHAR2(1);
181 l_skiplot_setup VARCHAR2(1);
182 l_qa_inspection VARCHAR2(1);
183
184 BEGIN
185
186 /*
187 rkaza: 07/16/2002. Bug 2451734. This caching mechanism doesnt work well
188 for different users getting the same database connection in mobile, also when
189 the user changes the skipping flag in the middle of his work.
190 Temporarily commenting it at the cost of a little bit of performance.
191
192 if skiplot_avail <> fnd_api.g_miss_char then
193 return skiplot_avail;
194 end if;
195 */
196
197 l_qa_installation := QA_INSPECTION_PKG.qa_installation;
198
199 l_skiplot_control := skiplot_control(p_organization_id);
200
201 l_skiplot_setup := skiplot_setup(p_txn, p_organization_id);
202
203 l_qa_inspection := QA_INSPECTION_PKG.qa_inspection;
204
205 if l_qa_installation = fnd_api.g_true and
206 l_skiplot_control = fnd_api.g_true and
207 l_skiplot_setup = fnd_api.g_true and
208 l_qa_inspection = fnd_api.g_true then
209 skiplot_avail := fnd_api.g_true;
210 else
211 skiplot_avail := fnd_api.g_false;
212 end if;
213
214 return skiplot_avail;
215
216 EXCEPTION
217
218 WHEN OTHERS THEN
219
220 insert_error_log (
221 p_module_name =>'QA_SKIPLOT_UTILITY.CHECK_SKIPLOT_AVAILABILITY',
222 p_error_message => 'QA_SKIPLOT_CHECK_AVALIABLITY_ERR',
223 p_comments => SUBSTR (SQLERRM , 1 , 240));
224
225 return fnd_api.g_false;
226
227 END CHECK_SKIPLOT_AVAILABILITY;
228
229 FUNCTION SKIPLOT_CONTROL
230 (p_organization_id IN NUMBER)
231 RETURN VARCHAR2 IS
232
233 cursor sk_ctrl (x_org_id number) is
234 select qa_skipping_insp_flag
235 from mtl_parameters
236 where organization_id = x_org_id;
237
238 sk_flag VARCHAR2(1) := fnd_api.g_false;
239
240 BEGIN
241 --
242 -- open cursor when INV is ready
243 -- return true for now.
244 --
245 open sk_ctrl (p_organization_id);
246 fetch sk_ctrl into sk_flag;
247 close sk_ctrl;
248
249 if sk_flag = 'Y' or sk_flag = 'T' then
250 return fnd_api.g_true;
251 else
252 return fnd_api.g_false;
253 end if;
254
255 END SKIPLOT_CONTROL;
256
257
258 FUNCTION SKIPLOT_SETUP (
259 p_txn IN NUMBER,
260 p_organization_id IN NUMBER)
261 RETURN VARCHAR2 IS
262
263 cursor rcv_criteria (x_org_id number) is
264 select count(*)
265 from qa_skiplot_rcv_criteria_val_v qsrc
266 where qsrc.organization_id = x_org_id and
267 trunc(sysdate) between nvl(trunc(qsrc.effective_from), trunc(sysdate))
268 and nvl(trunc(qsrc.effective_to), trunc(sysdate));
269
270
271 criteria_count number;
272
273 BEGIN
274
275 if p_txn = RCV then
276 open rcv_criteria (p_organization_id);
277 fetch rcv_criteria into criteria_count;
278 close rcv_criteria;
279 if criteria_count > 0 then
280 return fnd_api.g_true;
281 else
282 return fnd_api.g_false;
283 end if;
284 else
285 return fnd_api.g_false;
286 end if;
287
288 END SKIPLOT_SETUP;
289
290 PROCEDURE CHECK_RULE_FREQUENCY (
291 p_process_plan_id IN NUMBER,
292 p_rule_seq IN NUMBER,
293 p_freq_num OUT NOCOPY NUMBER,
294 p_freq_denom OUT NOCOPY NUMBER) IS
295
296 cursor rule (x_pp_id number, x_rule_seq number) is
297 select frequency_num, frequency_denom
298 from qa_skiplot_process_plan_rules
299 where process_plan_id = x_pp_id and
300 rule_seq = x_rule_seq;
301
302 BEGIN
303 open rule (p_process_plan_id, p_rule_seq);
304 fetch rule into p_freq_num, p_freq_denom;
305 close rule;
306
307 END CHECK_RULE_FREQUENCY;
308
309
310 FUNCTION GET_PROCESS_PLAN_ID (
311 p_plan_id IN NUMBER,
312 p_criteria_id IN NUMBER,
313 p_process_id IN NUMBER,
314 p_txn IN NUMBER)RETURN NUMBER IS
315
316 pp_id number;
317 pp refCursorTyp;
318
319 BEGIN
320
321 if p_txn = RCV then
322 open pp for
323 'select qspp.process_plan_id
324 from qa_skiplot_association qsa,
325 qa_skiplot_process_plans qspp
326 where qsa.criteria_id = :1 and
327 qsa.process_id = :2 and
328 qsa.process_id = qspp.process_id and
329 qspp.plan_id = :3'
330 using p_criteria_id, p_process_id, p_plan_id;
331 else
332 --
333 -- wip goes here
334 --
335 return -1;
336 end if;
337
338 fetch pp into pp_id;
339
340 if pp%notfound then
341 pp_id := -1;
342 end if;
343 close pp;
344
345 return pp_id;
346
347 END GET_PROCESS_PLAN_ID ;
348
349
350 PROCEDURE FETCH_PLAN_STATE(
351 p_plan_id IN NUMBER DEFAULT NULL,
352 p_process_plan_id IN NUMBER DEFAULT NULL,
353 p_process_id IN NUMBER DEFAULT NULL,
354 p_criteria_id IN NUMBER,
355 p_txn IN NUMBER DEFAULT NULL,
356 p_plan_state OUT nocopy plan_state_rec) IS
357
358 ps refCursorTyp;
359 pp_id number;
360
361 BEGIN
362
363 pp_id := p_process_plan_id;
364
365 if pp_id is null then
366 pp_id := get_process_plan_id(
367 p_plan_id => p_plan_id,
368 p_criteria_id => p_criteria_id,
369 p_process_id => p_process_id,
370 p_txn => p_txn);
371 end if;
372
373 if pp_id is not null and pp_id <> -1 then
374 open ps for
375 'select
376 qspp.plan_id,
377 qspp.process_plan_id,
378 qsa.process_id,
379 qsp.disqualification_days,
380 qsa.criteria_id,
381 qspp.alternate_plan_id,
382 qsps.current_rule,
383 qsppr.rounds,
384 qsppr.days_span,
385 qsppr.frequency_num,
386 qsppr.frequency_denom,
387 qsps.current_round,
388 qsps.current_lot,
389 qsps.lot_accepted,
390 qsps.rule_start_lot_id,
391 qsps.rule_start_date,
392 qsps.last_receipt_lot_id,
393 qsps.last_receipt_date
394 from qa_skiplot_association qsa,
395 qa_skiplot_processes qsp,
396 qa_skiplot_process_plans qspp,
397 qa_skiplot_process_plan_rules qsppr,
398 qa_skiplot_plan_states qsps
399 where qsa.criteria_id = :1 and
400 qsp.process_id = qsa.process_id and
401 qspp.process_plan_id = :2 and
402 qsppr.process_plan_id = qspp.process_plan_id and
403 qsps.process_plan_id = qspp.process_plan_id and
404 qsps.criteria_id = qsa.criteria_id and
405 qsps.current_rule = qsppr.rule_seq'
406
407 using p_criteria_id, pp_id;
408 fetch ps into p_plan_state;
409 close ps;
410 end if;
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 insert_error_log (
415 p_module_name => 'QA_SKIPLOT_UTILITY.FETCH_PLAN_STATE',
416 p_error_message => 'fail to fetch plan state',
417 p_comments => SUBSTR (SQLERRM , 1 , 240));
418 APP_EXCEPTION.RAISE_EXCEPTION;
419
420 END FETCH_PLAN_STATE;
421
422 PROCEDURE INIT_PLAN_STATES(
423 p_process_id IN NUMBER,
424 p_criteria_id IN NUMBER,
425 p_txn IN NUMBER) IS
426
427 cursor p_plans (x_pid number) is
428 select qspp.process_plan_id
429 from qa_skiplot_process_plans qspp
430 where process_id = x_pid;
431
432 BEGIN
433
434 for pp in p_plans (p_process_id) loop
435 init_plan_state (
436 p_process_plan_id => pp.process_plan_id,
437 p_criteria_id => p_criteria_id,
438 p_txn => p_txn);
439 end loop;
440
441 update_insp_stage(
442 p_txn => p_txn,
443 p_stage => 'QUALIFICATION',
444 p_criteria_id =>p_criteria_id,
445 p_process_id => p_process_id);
446
447
448 END INIT_PLAN_STATES;
449
450 PROCEDURE INIT_PLAN_STATE(
451 p_plan_id IN NUMBER,
452 p_criteria_id IN NUMBER,
453 p_process_id IN NUMBER,
454 p_txn IN NUMBER,
455 p_lot_id IN NUMBER DEFAULT NULL,
456 p_process_plan_id OUT NOCOPY NUMBER) IS
457
458 pp_id number;
459
460 BEGIN
461
462 pp_id := get_process_plan_id
463 (p_plan_id, p_criteria_id, p_process_id,p_txn);
464
465 init_plan_state (
466 p_process_plan_id => pp_id,
467 p_criteria_id => p_criteria_id,
468 p_txn => p_txn,
469 p_lot_id => p_lot_id);
470
471 p_process_plan_id := pp_id;
472
473 END INIT_PLAN_STATE;
474
475 PROCEDURE INIT_PLAN_STATE(
476 p_process_plan_id IN NUMBER,
477 p_criteria_id IN NUMBER,
478 p_txn IN NUMBER,
479 p_lot_id IN NUMBER DEFAULT NULL) IS
480
481 first_rule number;
482 last_date date;
483 last_receipt_lot number;
484 old_rule number;
485
486
487 BEGIN
488 first_rule := get_first_rule_num(p_process_plan_id);
489
490 if p_process_plan_id is null or p_process_plan_id = -1 or
491 p_criteria_id is null or p_criteria_id = -1 then
492
493 --
494 -- insert into error log
495 --
496 insert_error_log (
497 p_module_name =>'QA_SKIPLOT_UTILITY.INIT_PLAN_STATE',
498 p_error_message => 'QA_SKIPLOT_INIT_STATE_FAILURE',
499 p_comments => 'process_plan_id or criteria_id not available');
500
501 fnd_message.set_name ('QA', 'QA_SKIPLOT_INIT_STATE_FAILURE');
502 APP_EXCEPTION.RAISE_EXCEPTION;
503
504 end if;
505
506 delete qa_skiplot_plan_states where
507 process_plan_id = p_process_plan_id and
508 criteria_id = p_criteria_id
509 returning current_rule, last_receipt_date, last_receipt_lot_id into
510 old_rule, last_date, last_receipt_lot;
511
512 --
513 -- in wip p_receipt_date will be null,
514 -- use sysdate
515 --
516 insert into qa_skiplot_plan_states(
517 PROCESS_PLAN_ID,
518 CRITERIA_ID,
519 CURRENT_RULE,
520 CURRENT_ROUND,
521 CURRENT_LOT,
522 LOT_ACCEPTED,
523 RULE_START_LOT_ID,
524 RULE_START_DATE,
525 LAST_RECEIPT_LOT_ID,
526 LAST_RECEIPT_DATE,
527 LAST_UPDATE_DATE,
528 LAST_UPDATED_BY,
529 CREATION_DATE,
530 CREATED_BY,
531 LAST_UPDATE_LOGIN)
532 values(
533 p_process_plan_id,
534 p_criteria_id,
535 first_rule,
536 1,
537 0,
538 0,
539 p_lot_id,
540 decode(p_lot_id, null, null, sysdate),
541 nvl(p_lot_id,last_receipt_lot),
542 nvl(last_date,sysdate),
543 sysdate,
544 fnd_global.user_id,
545 sysdate,
546 fnd_global.user_id,
547 fnd_global.login_id);
548
549 update_state_history (
550 p_process_plan_id => p_process_plan_id,
551 p_criteria_id => p_criteria_id,
552 p_old_rule => old_rule,
553 p_new_rule => first_rule,
554 p_txn => p_txn);
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 insert_error_log (
559 p_module_name => 'QA_SKIPLOT_UTILITY.INIT_PLAN_STATE',
560 p_error_message => 'fail to delete and insert initial plan state',
561 p_comments => SUBSTR (SQLERRM , 1 , 240));
562
563 END INIT_PLAN_STATE;
564
565 PROCEDURE INIT_PLAN_STATES(
566 p_criteria_id IN NUMBER) IS
567
568 cursor plan_states (x_cid number) is
569 select qspp.process_plan_id
570 from qa_skiplot_association qsa,
571 qa_skiplot_process_plans qspp
572 where qsa.criteria_id = x_cid and
573 qsa.process_id = qspp.process_id;
574
575 BEGIN
576 for ps in plan_states (p_criteria_id) loop
577 update_plan_state (
578 p_process_plan_id => ps.process_plan_id,
579 p_criteria_id => p_criteria_id,
580 p_next_rule => 0,
581 p_next_round => 1,
582 p_next_lot => 0,
583 p_lot_accepted => 0);
584 end loop;
585
586 EXCEPTION
587 WHEN OTHERS THEN
588 insert_error_log (
589 p_module_name => 'QA_SKIPLOT_UTILITY.INIT_PLAN_STATES',
590 p_error_message => 'fail to update qa_skiplot_plan_states',
591 p_comments => SUBSTR (SQLERRM , 1 , 240));
592 END INIT_PLAN_STATES;
593
594 --
595 -- Bug 5037121
596 -- Added a new procedure to reset the last receipt date to the
597 -- sysdate, in case the plan state is reset due to the day span
598 -- getting violated
599 -- ntungare Wed Feb 15 07:29:08 PST 2006
600 --
601 PROCEDURE RESET_LAST_RECEIPT_DATE(
602 p_criteria_id IN NUMBER,
603 p_process_plan_id IN NUMBER) IS
604
605 BEGIN
606 UPDATE qa_skiplot_plan_states
607 SET last_receipt_date = SYSDATE,
608 last_update_date = SYSDATE,
609 last_updated_by = fnd_global.user_id,
610 last_update_login = fnd_global.login_id
611 WHERE process_plan_id = p_process_plan_id AND
612 criteria_id = p_criteria_id;
613 END RESET_LAST_RECEIPT_DATE;
614
615
616 PROCEDURE RESET_PLAN_STATES(
617 p_process_id IN NUMBER) IS
618
619 cursor plan_states (x_pid number) is
620 select qspp.process_plan_id,
621 qsa.criteria_id
622 from qa_skiplot_process_plans qspp,
623 qa_skiplot_association qsa
624 where qspp.process_id = x_pid and
625 qspp.process_id = qsa.process_id;
626
627 BEGIN
628
629 for ps in plan_states (p_process_id) loop
630 update_plan_state (
631 p_process_plan_id => ps.process_plan_id,
632 p_criteria_id => ps.criteria_id,
633 p_next_rule => 0,
634 p_next_round => 1,
635 p_next_lot => 0,
636 p_lot_accepted => 0);
637 end loop;
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 insert_error_log (
642 p_module_name => 'QA_SKIPLOT_UTILITY.RESET_PLAN_STATES',
643 p_error_message => 'fail to update qa_skiplot_plan_states',
644 p_comments => SUBSTR (SQLERRM , 1 , 240));
645 END RESET_PLAN_STATES;
646
647 FUNCTION INSP_ROUND_FINISHED(
648 p_plan_state IN plan_state_rec) RETURN VARCHAR2 IS
649
650 current_lot number;
651 total_lots number;
652 current_rule number;
653 total_round number;
654
655 BEGIN
656
657 current_rule := p_plan_state.current_rule;
658 current_lot := p_plan_state.current_lot;
659 total_lots := p_plan_state.current_freq_denom;
660 total_round := p_plan_state.total_round;
661
662 --
663 -- add this if statement to fix bug 2125382
664 -- if qualification lot is 0 or null, mark round as finished
665 -- jezheng
666 -- Thu Nov 29 16:56:22 PST 2001
667 --
668 if current_rule = 0 and (total_round = 0 or total_round is null) then
669 return fnd_api.g_true;
670 end if;
671
672 if current_lot is not null and total_lots is not null and
673 current_lot - total_lots >= 0 then
674 return fnd_api.g_true;
675 end if;
676
677 return fnd_api.g_false;
678
679 END INSP_ROUND_FINISHED;
680
681
682 FUNCTION INSP_RULE_FINISHED(
683 p_plan_state IN plan_state_rec)RETURN VARCHAR2 IS
684
685 c_round number;
686 total_round number;
687 current_rule number;
688
689 BEGIN
690 current_rule := p_plan_state.current_rule;
691 c_round := p_plan_state.current_round;
692 total_round := p_plan_state.total_round;
693
694 --
695 -- added if statement to fix bug 2125382
696 -- when quanlification lots is 0, always mark rule as finished
697 -- jezheng
698 -- Thu Nov 29 16:56:05 PST 2001
699 --
700 if current_rule = 0 and (total_round = 0 or total_round is null) then
701 return fnd_api.g_true;
702 end if;
703
704 if c_round is not null and
705 total_round is not null and
706 c_round >= total_round and
707 insp_round_finished(p_plan_state) = fnd_api.g_true then
708
709 return fnd_api.g_true;
710 end if;
711
712 return fnd_api.g_false;
713
714 END INSP_RULE_FINISHED;
715
716
717 FUNCTION GET_NEXT_INSP_RULE (
718 p_plan_state in plan_state_rec)RETURN NUMBER IS
719
720 next_rule number;
721
722 cursor rule (pp_id in number, current_rule in number)is
723 select min(rule_seq)
724 from qa_skiplot_process_plan_rules
725 where process_plan_id = pp_id and
726 rule_seq > current_rule;
727
728 BEGIN
729 open rule(
730 p_plan_state.process_plan_id,
731 p_plan_state.current_rule);
732
733 fetch rule into next_rule;
734
735 if rule%notfound or next_rule is null then
736 next_rule := -1;
737 end if;
738 return next_rule;
739
740 END GET_NEXT_INSP_RULE;
741
742 FUNCTION MORE_ROUNDS(
743 p_plan_state IN plan_state_rec) RETURN VARCHAR2 IS
744
745 BEGIN
746
747 If (get_next_insp_rule(p_plan_state) = -1) and (p_plan_state.total_round is null) then
748 return fnd_api.g_true;
749 elsif p_plan_state.total_round - p_plan_state.current_round >0 then
750 return fnd_api.g_true;
751 else
752 return fnd_api.g_false;
753 end if;
754
755 END MORE_ROUNDS;
756
757 FUNCTION ENOUGH_LOT_ACCEPTED(
758 p_plan_state IN plan_state_rec) RETURN VARCHAR2 IS
759
760 accepted_num number;
761 required_num number;
762
763 BEGIN
764 accepted_num := p_plan_state.lot_accepted;
765 required_num := p_plan_state.current_freq_num;
766
767 if accepted_num is not null and required_num is not null and
768 accepted_num - required_num >= 0 then
769 return fnd_api.g_true;
770 end if;
771
772
773 return fnd_api.g_false;
774 END ENOUGH_LOT_ACCEPTED;
775
776
777
778 FUNCTION DATE_REASONABLE(
779 p_receipt_date IN DATE DEFAULT NULL,
780 p_check_mode IN NUMBER,
781 p_plan_state plan_state_rec)RETURN VARCHAR2 IS
782
783 c_date DATE;
784 day_span NUMBER;
785
786 BEGIN
787
788 --
789 -- receipt date is checked in two scenarios:
790 -- ADJACENT_DATE_CHECK and DATE_SPAN_CHECK
791 --
792 if p_check_mode = ADJACENT_DATE_CHECK then
793 return check_adjacent_date(p_receipt_date, p_plan_state);
794 elsif p_check_mode = DATE_SPAN_CHECK then
795 return check_date_span (p_plan_state);
796 else
797 -- unknow mode
798 -- treat as no restriction.
799 return fnd_api.g_true;
800 end if;
801 END DATE_REASONABLE;
802
803 PROCEDURE UPDATE_INSP_STAGE (
804 p_txn IN NUMBER,
805 p_stage IN VARCHAR2,
806 p_criteria_id IN NUMBER,
807 p_process_id IN NUMBER)IS
808
809 BEGIN
810
811 --
812 -- removed the if statement as it's really unnecessary.
813 -- Reference bug 2137211
814 -- jezheng
815 -- Wed Mar 17 15:41:02 PST 2004
816 --
817 --if p_txn = RCV then
818 update qa_skiplot_association
819 set insp_stage = p_stage
820 where criteria_id = p_criteria_id and
821 process_id = p_process_id;
822 --end if;
823
824 END UPDATE_INSP_STAGE;
825
826 PROCEDURE UPDATE_PLAN_STATE(
827 p_process_plan_id IN NUMBER,
828 p_criteria_id IN NUMBER,
829 p_next_rule IN NUMBER DEFAULT NULL,
830 p_next_round IN NUMBER DEFAULT NULL,
831 p_next_lot IN NUMBER DEFAULT NULL,
832 p_rule_start_lotid IN NUMBER DEFAULT NULL,
833 p_last_receipt_lot_id IN NUMBER DEFAULT NULL,
834 p_lot_accepted IN NUMBER DEFAULT NULL,
835 p_txn IN NUMBER DEFAULT NULL) IS
836
837 old_plan_state plan_state_rec;
838 x_rule_start_date date;
839
840 BEGIN
841
842
843 fetch_plan_state (
844 p_process_plan_id => p_process_plan_id,
845 p_criteria_id => p_criteria_id,
846 p_txn => p_txn,
847 p_plan_state => old_plan_state);
848
849 if p_next_rule is not null or
850 (p_rule_start_lotid is not null and
851 p_rule_start_lotid <> old_plan_state.rule_start_lot_id) then
852 x_rule_start_date := sysdate;
853 else
854 x_rule_start_date := old_plan_state.rule_start_date;
855 end if;
856
857 update qa_skiplot_plan_states
858 set current_rule = nvl(p_next_rule, current_rule),
859 current_round = nvl(p_next_round, current_round),
860 current_lot = nvl(p_next_lot, current_lot),
861 lot_accepted = nvl(p_lot_accepted, lot_accepted),
862 last_receipt_lot_id = nvl(p_last_receipt_lot_id, last_receipt_lot_id),
863 last_receipt_date = decode(p_last_receipt_lot_id, null, last_receipt_date, sysdate),
864 rule_start_lot_id = nvl(p_rule_start_lotid, rule_start_lot_id),
865 rule_start_date = x_rule_start_date,
866 last_update_date = sysdate,
867 last_updated_by = fnd_global.user_id,
868 last_update_login = fnd_global.login_id
869 where process_plan_id = p_process_plan_id and
870 criteria_id = p_criteria_id;
871
872 if p_next_rule is not null then
873 update_state_history (
874 p_old_plan_state => old_plan_state,
875 p_next_rule => p_next_rule,
876 p_txn => p_txn);
877
878 --
879 -- rule 0 is the system default 1/1 frequency
880 -- if we pass this frequency, it means the inspection stage
881 -- is changing to skipping stage
882 --
883 if p_next_rule > 0 then
884 update_insp_stage(
885 p_txn => p_txn,
886 p_stage => 'SKIPPING',
887 p_criteria_id => p_criteria_id,
888 p_process_id => get_process_id (p_process_plan_id));
889 else -- p_next_rule = 0
890 update_insp_stage(
891 p_txn => p_txn,
892 p_stage => 'QUALIFICATION',
893 p_criteria_id =>p_criteria_id,
894 p_process_id => get_process_id(p_process_plan_id));
895 end if;
896 end if;
897
898 EXCEPTION
899 WHEN OTHERS THEN
900 insert_error_log (
901 p_module_name => 'QA_SKIPLOT_UTILITY.UPDATE_PLAN_STATE',
902 p_error_message => 'QA_SKIPLOT_UPDATE_STATE_FAILURE',
903 p_comments => SUBSTR (SQLERRM , 1 , 240));
904
905 END UPDATE_PLAN_STATE;
906
907
908 -- local function
909 procedure get_process_info (
910 p_process_plan_id in number,
911 p_process_id out NOCOPY number,
912 p_process_code out NOCOPY varchar2,
913 p_process_desc out NOCOPY varchar2) is
914
915 cursor process_info (x_ppid number) is
916 select qsp.process_id, qsp.process_code, qsp.description
917 from qa_skiplot_processes qsp, qa_skiplot_process_plans qspp
918 where qspp.process_plan_id = x_ppid and
919 qspp.process_id = qsp.process_id;
920
921 begin
922 open process_info(p_process_plan_id);
923 fetch process_info into p_process_id, p_process_code, p_process_desc;
924 close process_info;
925 end get_process_info;
926
927
928 -- local function
929 function get_rcv_criteria_str(
930 p_criteria_id in number,
931 p_wf_role_name out NOCOPY varchar2) return varchar2 is
932
933 cursor rcv_criteria (x_criteria_id number) is
934 select vendor_name,
935 vendor_site_code,
936 item,
937 item_revision,
938 category_desc,
939 manufacturer_name,
940 project_number,
941 task_number,
942 wf_role_name
943 from qa_skiplot_rcv_criteria_v
944 where criteria_id = x_criteria_id;
945
946 cursor char_names is
947 select name
948 from qa_chars
949 where char_id in (10, 11, 13, 26, 121, 122, 130)
950 order by char_id;
951
952 x_supplier qa_chars.name%type;
953 x_supplier_site qa_chars.name%type;
954 x_item qa_chars.name%type;
955 x_rev qa_chars.name%type;
956 x_cat qa_chars.name%type;
957 x_project qa_chars.name%type;
958 x_task qa_chars.name%type;
959 x_manufacturer varchar2(50);
960
961 x_criteria_str varchar2(2000) := '';
962 x_vendor_name varchar2(240);
963 x_vendor_site_code varchar2(100);
964 x_item_name varchar2(40);
965 x_item_rev varchar2(30);
966 x_item_cat varchar2(500);
967 x_manufacturer_name varchar2(30);
968 x_project_number varchar2(100);
969 x_task_number varchar2(25);
970
971 begin
972 open rcv_criteria (p_criteria_id);
973 fetch rcv_criteria into
974 x_vendor_name,
975 x_vendor_site_code,
976 x_item_name,
977 x_item_rev,
978 x_item_cat,
979 x_manufacturer_name,
980 x_project_number,
981 x_task_number,
982 p_wf_role_name;
983
984 close rcv_criteria;
985
986 if p_wf_role_name is null then
987 return null;
988 end if;
989
990 --
991 -- fetch the translated name for these collection
992 -- elements
993 --
994 open char_names;
995 fetch char_names into x_item; -- char_id 10
996 fetch char_names into x_cat; -- char_id 11
997 fetch char_names into x_rev; -- char_id 13
998 fetch char_names into x_supplier; -- char_id 26
999 fetch char_names into x_project; -- char_id 121
1000 fetch char_names into x_task; -- char_id 122
1001 fetch char_names into x_supplier_site; -- char_id 130
1002 close char_names;
1003
1004 --
1005 -- manufacturer is not a collection element
1006 -- retrieve the translated name from data dictionary
1007 -- jezheng
1008 -- Tue Oct 30 08:42:46 HKT 2001
1009 --
1010 x_manufacturer := fnd_message.get_string ('QA', 'QA_MANUFACTURER');
1011
1012 if x_vendor_name is not null then
1013 x_criteria_str := x_criteria_str ||
1014 x_supplier || ' = ' || x_vendor_name || '; ';
1015 end if;
1016 if x_vendor_site_code is not null then
1017 x_criteria_str := x_criteria_str ||
1018 x_supplier_site || ' = ' ||x_vendor_site_code || '; ';
1019 end if;
1020 if x_item_name is not null then
1021 x_criteria_str := x_criteria_str ||
1022 x_item || ' = ' || x_item_name || '; ';
1023 end if;
1024 if x_item_rev is not null then
1025 x_criteria_str := x_criteria_str ||
1026 x_rev || ' = ' || x_item_rev || '; ';
1027 end if;
1028 if x_item_cat is not null then
1029 x_criteria_str := x_criteria_str ||
1030 x_cat || ' = ' || x_item_cat || '; ';
1031 end if;
1032 if x_manufacturer_name is not null then
1033 x_criteria_str := x_criteria_str ||
1034 x_manufacturer || ' = ' || x_manufacturer_name || '; ';
1035 end if;
1036 if x_project_number is not null then
1037 x_criteria_str := x_criteria_str ||
1038 x_project || ' = ' || x_project_number || '; ';
1039 end if;
1040 if x_task_number is not null then
1041 x_criteria_str := x_criteria_str ||
1042 x_task || ' = ' || x_task_number || '; ';
1043 end if;
1044
1045 return x_criteria_str;
1046 end get_rcv_criteria_str;
1047
1048 -- local function
1049 function get_coll_plan_name (
1050 p_process_plan_id in number)return varchar2 is
1051
1052 cursor coll_plan (x_process_plan_id number) is
1053 select qp.name
1054 from qa_plans qp, qa_skiplot_process_plans qspp
1055 where qspp.process_plan_id = x_process_plan_id and
1056 qspp.plan_id = qp.plan_id;
1057
1058 x_coll_plan_name varchar2(30);
1059
1060 begin
1061 open coll_plan (p_process_plan_id);
1062 fetch coll_plan into x_coll_plan_name;
1063 close coll_plan;
1064
1065 return x_coll_plan_name;
1066 end get_coll_plan_name;
1067
1068
1069 PROCEDURE LAUNCH_WORKFLOW (
1070 p_process_plan_id IN NUMBER,
1071 p_criteria_id IN NUMBER,
1072 p_old_freq_num IN NUMBER,
1073 p_old_freq_denom IN NUMBER,
1074 p_new_freq_num IN NUMBER,
1075 p_new_freq_denom IN NUMBER,
1076 p_txn IN NUMBER) IS
1077
1078 x_process_id number;
1079 x_process_code varchar2(30);
1080 x_process_desc varchar2(300);
1081 x_plan_name varchar2(30);
1082 x_from_freq_str varchar2(30);
1083 x_to_freq_str varchar2(30);
1084 x_criteria_str varchar2(2000);
1085 x_wf_role_name varchar2(360);
1086 x_wf_itemkey number;
1087
1088 BEGIN
1089 if p_txn is not null and p_txn <> RCV then
1090 return;
1091 end if;
1092
1093 x_criteria_str := get_rcv_criteria_str(p_criteria_id,x_wf_role_name);
1094 if x_wf_role_name is null then
1095 return;
1096 end if;
1097
1098 get_process_info (
1099 p_process_plan_id,
1100 x_process_id, -- out parameter
1101 x_process_code, -- out parameter
1102 x_process_desc); -- out parameter
1103
1104 if x_process_id is null then
1105 return;
1106 end if;
1107
1108 x_plan_name := get_coll_plan_name (p_process_plan_id);
1109
1110 x_from_freq_str := p_old_freq_num || '/' || p_old_freq_denom;
1111 x_to_freq_str := p_new_freq_num || '/' || p_new_freq_denom;
1112
1113 x_wf_itemkey := qa_inspection_wf.raise_frequency_change_event(
1114 p_process_code => x_process_code,
1115 p_description => x_process_desc,
1116 p_inspection_plan => x_plan_name,
1117 p_from_frequency => x_from_freq_str,
1118 p_to_frequency => x_to_freq_str,
1119 p_criteria => x_criteria_str,
1120 p_role_name => x_wf_role_name);
1121
1122 EXCEPTION
1123 WHEN OTHERS THEN
1124 insert_error_log (
1125 p_module_name => 'QA_SKIPLOT_UTILITY.LAUNCH_WORKFLOW',
1126 p_error_message => 'QA_SKIPLOT_WORKFLOW_FAILURE',
1127 p_comments => SUBSTR (SQLERRM , 1 , 240));
1128
1129 END LAUNCH_WORKFLOW;
1130
1131
1132 PROCEDURE UPDATE_STATE_HISTORY(
1133 p_old_plan_state IN plan_state_rec,
1134 p_next_rule IN NUMBER,
1135 p_txn IN NUMBER DEFAULT NULL) IS
1136
1137 new_freq_num number;
1138 new_freq_denom number;
1139
1140 BEGIN
1141
1142 check_rule_frequency (
1143 p_process_plan_id => p_old_plan_state.process_plan_id,
1144 p_rule_seq => p_next_rule,
1145 p_freq_num => new_freq_num, -- out parameter
1146 p_freq_denom => new_freq_denom); -- out parameter
1147
1148 update_state_history (
1149 p_process_plan_id => p_old_plan_state.process_plan_id,
1150 p_criteria_id => p_old_plan_state.criteria_id,
1151 p_old_freq_num => p_old_plan_state.current_freq_num,
1152 p_old_freq_denom => p_old_plan_state.current_freq_denom,
1153 p_new_freq_num => new_freq_num,
1154 p_new_freq_denom => new_freq_denom,
1155 p_txn => p_txn);
1156
1157 END UPDATE_STATE_HISTORY;
1158
1159
1160 PROCEDURE UPDATE_STATE_HISTORY(
1161 p_process_plan_id IN NUMBER,
1162 p_criteria_id IN NUMBER,
1163 p_old_rule IN NUMBER,
1164 p_new_rule IN NUMBER,
1165 p_txn IN NUMBER) IS
1166
1167 old_freq_num number;
1168 old_freq_denom number;
1169 new_freq_num number;
1170 new_freq_denom number;
1171
1172 BEGIN
1173 check_rule_frequency (
1174 p_process_plan_id,
1175 p_old_rule,
1176 old_freq_num, -- out parameter
1177 old_freq_denom); -- out parameter
1178
1179 check_rule_frequency (
1180 p_process_plan_id,
1181 p_new_rule,
1182 new_freq_num, -- out parameter
1183 new_freq_denom); -- out parameter
1184
1185 update_state_history (
1186 p_process_plan_id => p_process_plan_id,
1187 p_criteria_id => p_criteria_id,
1188 p_old_freq_num => old_freq_num,
1189 p_old_freq_denom => old_freq_denom,
1190 p_new_freq_num => new_freq_num,
1191 p_new_freq_denom => new_freq_denom,
1192 p_txn => p_txn);
1193
1194 END UPDATE_STATE_HISTORY;
1195
1196 PROCEDURE UPDATE_STATE_HISTORY(
1197 p_process_plan_id IN NUMBER,
1198 p_criteria_id IN NUMBER,
1199 p_old_freq_num IN NUMBER,
1200 p_old_freq_denom IN NUMBER,
1201 p_new_freq_num IN NUMBER,
1202 p_new_freq_denom IN NUMBER,
1203 p_txn IN NUMBER) IS
1204
1205 BEGIN
1206
1207 insert into qa_skiplot_state_history(
1208 PROCESS_PLAN_ID,
1209 CRITERIA_ID,
1210 CHANGE_DATE,
1211 OLD_FREQ_NUM,
1212 OLD_FREQ_DENOM,
1213 NEW_FREQ_NUM,
1214 NEW_FREQ_DENOM,
1215 LAST_UPDATE_DATE,
1216 LAST_UPDATED_BY,
1217 CREATION_DATE,
1218 CREATED_BY,
1219 LAST_UPDATE_LOGIN)
1220 values(
1221 p_process_plan_id,
1222 p_criteria_id,
1223 sysdate,
1224 p_old_freq_num,
1225 p_old_freq_denom,
1226 p_new_freq_num,
1227 p_new_freq_denom,
1228 sysdate,
1229 fnd_global.user_id,
1230 sysdate,
1231 fnd_global.user_id,
1232 fnd_global.login_id);
1233
1234 launch_workflow (
1235 p_process_plan_id => p_process_plan_id,
1236 p_criteria_id => p_criteria_id,
1237 p_old_freq_num => p_old_freq_num,
1238 p_old_freq_denom => p_old_freq_denom,
1239 p_new_freq_num => p_new_freq_num,
1240 p_new_freq_denom => p_new_freq_denom,
1241 p_txn => p_txn);
1242
1243
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 insert_error_log (
1247 p_module_name => 'QA_SKIPLOT_UTILITY.UPDATE_STATE_HISTORY',
1248 p_error_message => 'QA_SKIPLOT_UPDATE_HISTORY_FAILURE',
1249 p_comments => SUBSTR (SQLERRM , 1 , 240));
1250 END UPDATE_STATE_HISTORY;
1251
1252 PROCEDURE INSERT_ERROR_LOG (
1253 p_module_name IN VARCHAR2,
1254 p_error_message IN VARCHAR2 DEFAULT NULL,
1255 p_comments IN VARCHAR2 DEFAULT NULL) IS
1256
1257 PRAGMA AUTONOMOUS_TRANSACTION;
1258
1259 x_logid number;
1260 cursor id is
1261 select qa_skiplot_log_id_s.nextval
1262 from dual;
1263
1264 cursor c (x_id number)is
1265 select 1 from qa_skiplot_log
1266 where log_id = x_id;
1267
1268 existing_flag number;
1269
1270 BEGIN
1271
1272 open id;
1273 fetch id into x_logid;
1274 close id;
1275
1276 --
1277 -- qa_skiplot_log_id_s is a cycle sequence
1278 -- the purpose is to control log table size
1279 --
1280 open c (x_logid);
1281 fetch c into existing_flag;
1282 close c;
1283
1284 if existing_flag is null then
1285 insert into qa_skiplot_log(
1286 LOG_ID,
1287 MODULE_NAME,
1288 ERROR_MESSAGE,
1289 COMMENTS,
1290 LAST_UPDATE_DATE,
1291 LAST_UPDATED_BY,
1292 CREATION_DATE,
1293 CREATED_BY,
1294 LAST_UPDATE_LOGIN)
1295 values(
1296 x_logid,
1297 p_module_name,
1298 p_error_message,
1299 p_comments,
1300 sysdate,
1301 fnd_global.user_id,
1302 sysdate,
1303 fnd_global.user_id,
1304 fnd_global.login_id);
1305 else
1306 update qa_skiplot_log
1307 set MODULE_NAME = p_module_name,
1308 ERROR_MESSAGE = p_error_message,
1309 COMMENTS = p_comments,
1310 LAST_UPDATE_DATE = sysdate,
1311 LAST_UPDATED_BY = fnd_global.user_id,
1312 CREATION_DATE = sysdate,
1313 CREATED_BY = fnd_global.user_id,
1314 LAST_UPDATE_LOGIN = fnd_global.login_id
1315 where LOG_ID = x_logid;
1316 end if;
1317
1318 COMMIT;
1319
1320 EXCEPTION
1321 WHEN OTHERS THEN
1322 rollback;
1323
1324 END INSERT_ERROR_LOG;
1325
1326 FUNCTION GET_LOT_ID RETURN NUMBER IS
1327
1328 x_lotID number;
1329 cursor id is
1330 select qa_skiplot_lot_id_s.nextval
1331 from dual;
1332
1333 BEGIN
1334 open id;
1335 fetch id into x_lotID;
1336 close id;
1337
1338 return x_lotID;
1339
1340 END GET_LOT_ID;
1341
1342 FUNCTION INSPECT_ZERO (
1343 p_plan_state IN plan_state_rec,
1344 p_txn IN NUMBER DEFAULT NULL) RETURN VARCHAR2 IS
1345
1346 next_rule number;
1347 next_freq_num number;
1348 next_freq_denom number;
1349
1350 BEGIN
1351 next_rule := GET_NEXT_INSP_RULE (p_plan_state);
1352 CHECK_RULE_FREQUENCY(
1353 p_process_plan_id => p_plan_state.process_plan_id,
1354 p_rule_seq => next_rule,
1355 p_freq_num => next_freq_num,
1356 p_freq_denom => next_freq_denom);
1357
1358 if next_freq_num = 0 then
1359
1360 --
1361 -- set inspection stage to Skipping when no
1362 -- inspection is required
1363 -- Reference bug 2940984
1364 -- jezheng
1365 -- Wed Mar 17 14:56:08 PST 2004
1366 --
1367
1368 update_insp_stage(
1369 p_txn => nvl(p_txn, RCV),
1370 p_stage => 'SKIPPING',
1371 p_criteria_id => p_plan_state.criteria_id,
1372 p_process_id => p_plan_state.process_id);
1373
1374 return fnd_api.g_true;
1375 else
1376 return fnd_api.g_false;
1377 end if;
1378 END INSPECT_ZERO;
1379
1380 FUNCTION GET_PROCESS_ID (
1381 p_process_plan_id IN NUMBER) RETURN NUMBER IS
1382
1383 cursor pid (x_ppid number) is
1384 select process_id
1385 from qa_skiplot_process_plans
1386 where process_plan_id = x_ppid;
1387 proc_id number;
1388
1389 BEGIN
1390 open pid (p_process_plan_id);
1391 fetch pid into proc_id;
1392 close pid;
1393 return nvl(proc_id, -1);
1394 END GET_PROCESS_ID;
1395
1396 END QA_SKIPLOT_UTILITY;
1397