1 PACKAGE BODY QA_SKIPLOT_EVAL_ENGINE AS
2 /* $Header: qaslevab.pls 120.3.12010000.2 2009/11/27 10:01:15 pdube ship $ */
3
4 --
5 -- local function
6 --
7 function insp_required(
8 p_plan_state in qa_skiplot_utility.plan_state_rec)return varchar2 is
9
10 begin
11 --
12 -- p_plan_state is the state before we consider the new coming lot
16 -- rule is finished
13 --
14
15 --
17 --
18 if qa_skiplot_utility.insp_rule_finished(p_plan_state) = fnd_api.g_true then
19 --
20 -- if the next rule does not have freq_numerator as 0, then inspect
21 -- otherwise skip
22 --
23 if qa_skiplot_utility.inspect_zero(p_plan_state) = fnd_api.g_false then
24 return fnd_api.g_true;
25 else
26 return fnd_api.g_false;
27 end if;
28 --
29 -- rule is not finished, but round is finished
30 --
31 elsif qa_skiplot_utility.insp_round_finished(p_plan_state) = fnd_api.g_true then
32 --
33 -- if more rounds and the current frequency numerator is not 0 then inspect
34 -- otherwise skip
35 --
36 if qa_skiplot_utility.more_rounds(p_plan_state) = fnd_api.g_true and
37 p_plan_state.current_freq_num > 0 then
38 return fnd_api.g_true;
39 else
40 return fnd_api.g_false;
41 end if;
42
43 --
44 -- round not finished
45 --
46 else
47 -- if not enough lot accepted, then inspect, otherwise skip
48 if qa_skiplot_utility.enough_lot_accepted(p_plan_state) = fnd_api.g_false then
49 return fnd_api.g_true;
50 else
51 return fnd_api.g_false;
52 end if;
53
54 end if;
55
56 end insp_required;
57
58 --
59 -- local function
60 --
61 procedure fetch_plan_states(
62 p_planList IN qa_skiplot_utility.planList,
63 p_criteria_id IN NUMBER,
64 p_process_id IN NUMBER,
65 p_txn IN NUMBER,
66 p_plan_states OUT nocopy qa_skiplot_utility.planStateTable) IS
67
68 plan_state qa_skiplot_utility.plan_state_rec;
69 plan_id number;
70 i number := null;
71
72 begin
73 i := p_planList.first;
74 while i is not null loop
75 plan_id := p_planList(i).plan_id;
76
77 qa_skiplot_utility.fetch_plan_state (
78 p_plan_id => plan_id,
79 p_criteria_id => p_criteria_id,
80 p_process_id => p_process_id,
81 p_txn => p_txn,
82 p_plan_state => plan_state); -- out parameter
83
84 if plan_state.plan_id is not null then
85 p_plan_states(plan_state.plan_id) := plan_state;
86 end if;
87
88 i := p_planList.next(i);
89 end loop;
90 end fetch_plan_states;
91
92
93 --
94 -- The function returns criteria id
95 -- it also resolve the criteria conflicts
96 -- so that only one criteria id is returned
97 -- if multiple groups of criteria are setup
98 --
99
100
101 FUNCTION GET_RCV_CRITERIA_ID
102 (p_organization_id IN NUMBER,
103 p_vendor_id IN NUMBER,
104 p_vendor_site_id IN NUMBER,
105 p_item_id IN NUMBER,
106 p_item_revision IN VARCHAR2,
107 p_item_category_id IN NUMBER,
108 p_project_id IN NUMBER,
109 p_task_id IN NUMBER,
110 p_manufacturer_id IN NUMBER)
111 RETURN NUMBER IS
112
113 criteriaID NUMBER := -1;
114
115 -- Bug 7270226.FP for bug#7219703
116 -- Getting the categories having criterion defined
117 -- for skipping or sampling for the item.
118 -- pdube Thu Nov 26 03:19:15 PST 2009
119 CURSOR item_categories (
120 x_item_id NUMBER,
121 x_organization_id number,
122 x_item_category_id number) IS
123 SELECT Nvl(micv.category_id,-1) item_category_id
124 FROM MTL_ITEM_CATEGORIES_V MICV,
125 qa_sl_sp_rcv_criteria qssrc,
126 qa_skiplot_association qsa,
127 qa_skiplot_processes qsp
128 WHERE micv.inventory_item_id= x_item_id AND
129 micv.organization_id= x_organization_id AND
130 qssrc.item_category_id = micv.category_id AND
131 qsa.criteria_id = qssrc.criteria_id AND
132 qsa.process_id = qsp.process_id AND
133 micv.category_id <> x_item_category_id
134 ORDER BY qsp.process_code asc;
135
136 CURSOR criteria (
137 x_organization_id number,
138 x_vendor_id number,
139 x_vendor_site_id number,
140 x_item_id number,
141 x_item_revision varchar2,
142 x_item_category_id number,
143 x_project_id number,
144 x_task_id number,
145 x_manufacturer_id number) IS
146
147 -- the SQL resolves the criteria conflict
148 -- by using order by statement.
149
150 --
151 -- removed the rownum = 1 statement from this cursor
152 -- since rownum cound is done before ordering which
153 -- will give wrong criteria
154 -- jezheng
155 -- Thu Oct 18 18:18:29 PDT 2001
156 --
157 --
158 -- Bug 7270226.FP for bug#7219703
159 -- Added the effective date validation clause to handle
160 -- the scenario of multiple applicable scenario with one end dated.
161 -- pdube Thu Nov 26 03:19:15 PST 2009
162 SELECT
163 qsrc.criteria_id criteria_id
164 FROM
165 qa_sl_sp_rcv_criteria qsrc,
166 qa_skiplot_association qsa
167 WHERE
168 qsrc.vendor_id in (-1, x_vendor_id) AND
169 qsrc.vendor_site_id in (-1, x_vendor_site_id) AND
173 qsrc.project_id in (-1, x_project_id) AND
170 qsrc.item_id in (-1, x_item_id)AND
171 qsrc.item_revision in ('-1', x_item_revision) AND
172 qsrc.item_category_id in (-1, x_item_category_id) AND
174 qsrc.task_id in (-1, x_task_id) AND
175 qsrc.manufacturer_id in (-1, x_manufacturer_id) AND
176 qsrc.organization_id = x_organization_id AND
177 qsa.criteria_id = qsrc.criteria_id AND
178 trunc(sysdate) BETWEEN
179 nvl(trunc(qsa.effective_from), trunc(sysdate)) AND
180 nvl(trunc(qsa.effective_to), trunc(sysdate))
181 ORDER BY
182 qsrc.task_id desc, qsrc.project_id desc , qsrc.manufacturer_id desc,
183 qsrc.vendor_site_id desc, qsrc.vendor_id desc, qsrc.item_revision desc,
184 qsrc.item_id desc, qsrc.item_category_id desc, qsrc.last_update_date desc
185 FOR UPDATE NOWAIT;
186
187 begin
188
189 open criteria
190 (p_organization_id,
191 p_vendor_id,
192 p_vendor_site_id,
193 p_item_id,
194 p_item_revision,
195 p_item_category_id,
196 p_project_id,
197 p_task_id,
198 p_manufacturer_id);
199
200 --
201 -- fetch statement will fetch only one criteria_id
202 -- based on the order specified in the cursor
203 -- if more than one applicable.
204 --
205 fetch criteria into criteriaID;
206 close criteria;
207
208 -- Bug 7270226.FP for bug#7219703
209 -- Added this code to check for criteria based on
210 -- categories defined out of "Purchasing" Category Set.
211 -- pdube Thu Nov 26 03:19:15 PST 2009
212 IF (criteriaID = -1) THEN
213 for prec in item_categories(p_item_id,p_organization_id,p_item_category_id) LOOP
214 open criteria
215 (p_organization_id,
216 p_vendor_id,
217 p_vendor_site_id,
218 p_item_id,
219 p_item_revision,
220 prec.item_category_id,
221 p_project_id,
222 p_task_id,
223 p_manufacturer_id);
224 fetch criteria into criteriaID;
225 close criteria;
226 EXIT WHEN(criteriaID <> -1);
227 END LOOP;
228 END IF;
229 -- End of bug 7270226.FP for bug#7219703
230
231 return criteriaID;
232
233 END GET_RCV_CRITERIA_ID;
234
235
236 PROCEDURE EVALUATE_RCV_CRITERIA (
237 p_organization_id IN NUMBER,
238 p_vendor_id IN NUMBER,
239 p_vendor_site_id IN NUMBER,
240 p_item_id IN NUMBER,
241 p_item_revision IN VARCHAR2,
242 p_item_category_id IN NUMBER,
243 p_project_id IN NUMBER,
244 p_task_id IN NUMBER,
245 p_manufacturer_id IN NUMBER,
246 p_lot_qty IN NUMBER,
247 p_primary_uom IN varchar2,
248 p_transaction_uom IN varchar2,
249 p_availablePlans OUT NOCOPY qa_skiplot_utility.planList,
250 p_criteria_id OUT NOCOPY NUMBER,
251 p_process_id OUT NOCOPY NUMBER)IS
252
253 x_availablePlans qa_skiplot_utility.planList;
254 x_criteria_id NUMBER := -1;
255 x_process_id NUMBER := -1;
256
257 primary_lot_qty NUMBER;
258
259 --
260 -- given a criteria_id, we can get multiple process_ids
261 -- from qa_skiplot_association table. Put lot size and
262 -- effective date as restriction, we will get only one
263 -- process_id if any.
264 -- With this process_id, we can get multiple collection
265 -- plan ids from qa_skiplot_process_plans table.
266 -- jezheng
267 -- Tue Oct 30 17:47:20 PST 2001
268 --
269 cursor the_process (x_c_id number, x_lotsize number) is
270 select qsa.process_id
271 from qa_skiplot_association qsa
272 where qsa.criteria_id = x_c_id and
273 trunc(sysdate) between
274 nvl(trunc(qsa.effective_from), trunc(sysdate)) and
275 nvl(trunc(qsa.effective_to), trunc(sysdate)) and
276 x_lotsize between
277 nvl(qsa.lotsize_from, x_lotsize) and
278 nvl(qsa.lotsize_to, x_lotsize);
279
280 cursor avail_plans (x_process_id number) is
281 select qspp.plan_id
282 from qa_skiplot_process_plans qspp
283 where qspp.process_id = x_process_id;
284
285 BEGIN
286 /* p_lot_qty is in transaction_uom. so it is converted to primary_uom*/
287 if ((p_primary_uom is not null) and (p_transaction_uom is not null) and (p_primary_uom <> p_transaction_uom)) then
288 primary_lot_qty := inv_convert.inv_um_convert(
289 item_id => p_item_id,
290 precision => null,
291 from_quantity => p_lot_qty,
292 from_unit => p_transaction_uom,
293 to_unit => p_primary_uom,
294 from_name => null,
295 to_name => null );
296 else
297 primary_lot_qty := p_lot_qty;
298 end if;
299
300 x_criteria_id := get_rcv_criteria_id(
301 p_organization_id => p_organization_id,
302 p_vendor_id => p_vendor_id,
303 p_vendor_site_id => p_vendor_site_id,
304 p_item_id => p_item_id,
305 p_item_revision => p_item_revision,
306 p_item_category_id => p_item_category_id,
307 p_project_id => p_project_id,
308 p_task_id => p_task_id,
309 p_manufacturer_id => p_manufacturer_id);
310
311 open the_process (x_criteria_id, primary_lot_qty);
312 fetch the_process into x_process_id;
313 close the_process;
314
315 for p in avail_plans (x_process_id) loop
316 x_availablePlans(p.plan_id).plan_id := p.plan_id;
317 end loop;
318
319 p_availablePlans := x_availablePlans;
320 p_criteria_id := x_criteria_id;
321 p_process_id := x_process_id;
322
323 END EVALUATE_RCV_CRITERIA;
324
325 PROCEDURE EVALUATE_RULES (
326 p_availablePlans IN qa_skiplot_utility.planList,
327 p_criteria_id IN NUMBER,
328 p_process_id IN NUMBER,
329 p_txn IN NUMBER,
330 p_lot_id OUT NOCOPY NUMBER,
331 p_applicablePlans OUT NOCOPY qa_skiplot_utility.planList)IS
332
333 pID number; -- plan id
334 alternate_plan_id number;
335 plan_states qa_skiplot_utility.planStateTable;
336 i number := null;
337 p_plan_id number; -- process plan id
338 rule_start_lotid number;
339
340 x_lotID number;
341
342 -- Bug 3959767. Skiplot not skipping if we are setting
343 -- qualification to zero and frequency zero out of n.
344 -- Adding the following code including three cursor
345 -- track bug number for changes
346 -- saugupta Mon, 22 Nov 2004 23:59:14 -0800 PDT
347 inspection_stage VARCHAR2(100) := ' ';
348 q_rounds NUMBER;
349 next_rule NUMBER;
350 freq_denom NUMBER;
351 freq_num NUMBER;
352 process_plan_id NUMBER;
353
354 -- Bug 3959767. Fetches the inspection stage
355
356 -- Bug 5197549
357 -- Added the condition for Process Id
358 -- so that the inspection Stage is picked
359 -- Up corresponding to that process
360 -- SHKALYAN 01-May-2006
361
362 CURSOR insp_stage(x_crit_id NUMBER, x_process_id NUMBER) IS
363 SELECT INSP_STAGE
364 FROM qa_skiplot_association
365 WHERE criteria_id = x_crit_id
366 AND process_id = x_process_id;
367
368 -- Bug 3959767 fetches qualification rounds
369 CURSOR qualification_rounds(x_process_id NUMBER) IS
370 SELECT qsppr.rounds
371 FROM qa_skiplot_process_plans qspp,
372 qa_skiplot_process_plan_rules qsppr
373 WHERE qspp.process_id = x_process_id
374 AND qspp.process_plan_id = qsppr.process_plan_id
375 AND qsppr.rule_seq=0;
376
377 -- Bug 3959767 fetches next rule
378 CURSOR nxt_rule (pp_id in number, current_rule in number) IS
379 SELECT MIN(rule_seq)
380 FROM qa_skiplot_process_plan_rules
381 WHERE process_plan_id = pp_id and
382 rule_seq > current_rule;
383
384 --
385 -- Bug 5037121
386 -- New flag to check if the plans state
387 -- have been initialized
388 -- ntungare Wed Feb 15 06:46:23 PST 2006
389 --
390 plan_init_flag BOOLEAN;
391
392 -- Bug 5037121
393 -- New cursor to fetch all the plans associated
394 -- and the criteria Id for the Skip process Id
395 -- ntungare Wed Feb 15 06:46:23 PST 2006
396 --
397 -- Bug 6344791
401 -- bhsankar Wed Aug 22 01:16:48 PDT 2007
398 -- Added a parameter x_crit_id so that the process_plans
399 -- are uniquely identified for a particular
400 -- skiplot process-criteria combination
402 --
403 CURSOR plan_det (x_pid NUMBER, x_crit_id NUMBER) IS
404 SELECT qspp.process_plan_id
405 FROM qa_skiplot_process_plans qspp, qa_skiplot_association qsa
406 WHERE qspp.process_id = qsa.process_id AND
407 qsa.criteria_id = x_crit_id AND
408 qspp.process_id = x_pid;
409
410 BEGIN
411
412 x_lotID := qa_skiplot_utility.get_lot_id;
413 p_lot_id := x_lotID;
414
415 fetch_plan_states(
416 p_planList => p_availablePlans,
417 p_criteria_id => p_criteria_id,
418 p_process_id => p_process_id,
419 p_txn => p_txn,
420 p_plan_states => plan_states); -- out parameter
421
422 i := p_availablePlans.first;
423
424 -- Bug 3959767. Get the inspection stage
425
426 -- Bug 5037121
427 -- Moved the Cursor into the check for date violation
428 -- as in there the Inspectin stage is set to Qualification
429 -- and we need to get the new set stage.
430 -- ntungare Wed Feb 15 06:48:37 PST 2006
431
432 --OPEN insp_stage(p_criteria_id);
433 --FETCH insp_stage INTO inspection_stage;
434 --CLOSE insp_stage;
435
436 --
437 -- loop through each available plan to
438 -- see whether it is applicable
439 -- if yes, write it into applicable plan pl/sql table
440 --
441 while i is not null loop
442
443 pID := p_availablePlans(i).plan_id;
444
445 --
446 -- if state not found for this plan
447 -- or if adjacent date restriction is violated,
448 -- initialize the plan state table and
449 -- the plan must be inspected
450 --
451 --
452 -- Bug 5037121
453 -- If a state is not found then call the update_plan_state
454 -- procedure that will reset the inspection stage to
455 -- qualification.
456 -- If the adjacent date restriction is violated the procedure
457 -- reset_last_receipt_date is called after Update_plan_state
458 -- to update the last receipt date to the sysdate.
459 -- This reset is done for all the Plans associated with
460 -- a skip process
461 -- ntungare Wed Feb 15 06:50:41 PST 2006
462 --
463
464 if not plan_states.exists(pID) THEN
465 qa_skiplot_utility.init_plan_state(
466 p_plan_id=> pID,
467 p_criteria_id => p_criteria_id,
468 p_process_id => p_process_id,
469 p_txn => p_txn,
470 p_lot_id => x_lotID,
471 p_process_plan_id => p_plan_id);
472
473 plan_init_flag := TRUE;
474 elsif
475 qa_skiplot_utility.date_reasonable (
476 p_receipt_date => sysdate,
477 p_check_mode => qa_skiplot_utility.ADJACENT_DATE_CHECK,
478 p_plan_state =>plan_states(pID)) = fnd_api.g_false THEN
479 --
480 -- bug 6344791
481 -- Included a parameter p_criteria_id so as
482 -- to uniquely identify the skiplot plans
483 -- whose statuses needs to be reset to qualification.
484 -- Also modified parameter name from ps.criteria_id to
485 -- p_criteria_id in calls to procedures UPDATE_PLAN_STATE
486 -- and RESET_LAST_RECEIPT_DATE.
487 -- bhsankar Wed Aug 22 01:16:48 PDT 2007
488 --
489 For ps in plan_det (p_process_id, p_criteria_id)
490 LOOP
491 QA_SKIPLOT_UTILITY.UPDATE_PLAN_STATE (
492 p_process_plan_id => ps.process_plan_id,
493 p_criteria_id => p_criteria_id,
494 p_next_rule => 0,
495 p_next_round => 1,
496 p_next_lot => 0,
497 p_lot_accepted => 0,
498 p_txn => QA_SKIPLOT_UTILITY.RCV);
499
500 QA_SKIPLOT_UTILITY.RESET_LAST_RECEIPT_DATE(
501 p_criteria_id => p_criteria_id,
502 p_process_plan_id => ps.process_plan_id);
503 END LOOP;
504
505 plan_init_flag := TRUE;
506 else
507 plan_init_flag := FALSE;
508 end if;
509
510 --
511 -- Bug 5037121
512 -- This processing logic was based on any of the above
513 -- conditions being true. Since the aboce conditions
514 -- have been split so a separate flag has been used.
515 -- ntungare Wed Feb 15 06:56:10 PST 2006
516 --
517 IF plan_init_flag THEN
518
519 -- Bug 3959767 Since plan exists, we need to check if
520 -- process in Qualification stage. If in Qualification
521 -- stage we need to check the rounds as this is where the
522 -- Qualification parameter comes in picture.
523 -- saugupta Tue, 23 Nov 2004 00:00:58 -0800 PDT
524
528 --
525 -- Bug 5037121
526 -- Opening the cursor to fetch the Inspection Stage
527 -- ntungare Wed Feb 15 06:57:25 PST 2006
529
530
531 -- Bug 5197549
532 -- Passing the Process Id too to the
533 -- cursor as that the Inspection Stage
534 -- is read specific to that process
535 -- SHKALYAN 01-May-2006
536
537 OPEN insp_stage(p_criteria_id, p_process_id);
538 FETCH insp_stage INTO inspection_stage;
539 CLOSE insp_stage;
540
541 IF inspection_stage='QUALIFICATION' THEN
542
543 -- Bug 3959767 Get number of qualifying rounds.
544 OPEN qualification_rounds(p_process_id);
545 FETCH qualification_rounds INTO q_rounds;
546 CLOSE qualification_rounds;
547
548 -- Bug 3959767. If qualification round equals zero the process should
549 -- be skipped that is it should directly go to next rule.
550 -- saugupta Tue, 23 Nov 2004 00:01:19 -0800 PDT
551 IF q_rounds =0 THEN
552
553 process_plan_id := QA_SKIPLOT_UTILITY.GET_PROCESS_PLAN_ID(
554 p_plan_id => pID,
555 p_criteria_id => p_criteria_id,
556 p_process_id => p_process_id,
557 p_txn => p_txn);
558
559
560 OPEN nxt_rule(process_plan_id, 0);
561 FETCH nxt_rule INTO next_rule;
562 CLOSE nxt_rule;
563
564 QA_SKIPLOT_UTILITY.CHECK_RULE_FREQUENCY(
565 process_plan_id,
566 next_rule,
567 freq_num,
568 freq_denom);
569 -- Bug 3959767. Checking the frequency of next rule. If this is also set
570 -- to zero then skip and update the plan state/process state.
571 -- saugupta Tue, 23 Nov 2004 00:01:58 -0800 PDT
572 IF freq_num = 0 THEN
573 QA_SKIPLOT_UTILITY.update_plan_state(
574 p_process_plan_id => process_plan_id,
575 p_criteria_id => p_criteria_id,
576 p_next_rule => next_rule,
577 p_txn => p_txn);
578
579
580 ELSE
581 p_applicablePlans(pID).plan_id := pID;
582 END IF;
583 ELSE
584 p_applicablePlans(pID).plan_id := pID;
585 END IF;
586 END IF;
587
588 elsif insp_required(plan_states(pID)) = fnd_api.g_true then
589 --
590 -- plan must be inspected
591 --
592 p_applicablePlans(pID).plan_id := pID;
593
594 --
595 -- if rule_start_lot_id is null, update it with new id
596 -- otherwise leave it as it is
597 --
598 if qa_skiplot_utility.insp_rule_finished(plan_states(pID)) = fnd_api.g_true and
599 (plan_states(pID).last_receipt_date is null or
600 plan_states(pID).rule_start_date is null or
601 plan_states(pID).day_span is null or
602 trunc(plan_states(pID).last_receipt_date) - trunc(plan_states(pID).rule_start_date) <=
603 plan_states(pID).day_span ) then
604
605 rule_start_lotid := x_lotID;
606 else
607 rule_start_lotid := plan_states(pID).rule_start_lot_id;
608 end if;
609
610 --
611 -- update plan state with the latest lot id
612 --
613 qa_skiplot_utility.update_plan_state (
614 p_process_plan_id => plan_states(pID).process_plan_id,
615 p_criteria_id => p_criteria_id,
616 p_last_receipt_lot_id => x_lotID,
617 p_rule_start_lotid => rule_start_lotid,
618 p_txn => p_txn);
619
620 else
621 --
622 -- plan is skipped
623 -- forward the current_lot and
624 -- update the last receipt lot id
625 -- get alternate plan if available
626 --
627 qa_skiplot_utility.update_plan_state (
628 p_process_plan_id => plan_states(pID).process_plan_id,
629 p_criteria_id => p_criteria_id,
630 p_next_lot => plan_states(pID).current_lot + 1,
631 p_last_receipt_lot_id => x_lotID,
632 p_txn => p_txn);
633
634 alternate_plan_id := plan_states(pID).alternate_plan_id;
635
636 if alternate_plan_id is not null then
637 p_applicablePlans(alternate_plan_id).plan_id := alternate_plan_id;
638 p_applicablePlans(alternate_plan_id).alternate_flag := 'Y';
639 end if;
640 end if;
641
642 i := p_availablePlans.next(i);
643 end loop;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 qa_skiplot_utility.insert_error_log (
648 p_module_name => 'QA_SKIPLOT_EVAL_ENGINE.EVALUATE_RULES',
649 p_error_message => 'QA_SKIPLOT_EVAL_RULE_FAILURE',
650 p_comments => SUBSTR (SQLERRM , 1 , 240));
651 fnd_message.set_name ('QA', 'QA_SKIPLOT_EVAL_RULE_FAILURE');
652 APP_EXCEPTION.RAISE_EXCEPTION;
653
654 END EVALUATE_RULES;
655
656 PROCEDURE INSERT_RCV_RESULTS (
657 p_interface_txn_id IN NUMBER,
658 p_manufacturer_id IN NUMBER,
659 p_receipt_qty IN NUMBER,
660 p_criteriaID IN NUMBER,
661 p_insp_status IN VARCHAR2,
662 p_receipt_date IN DATE,
663 p_lotID IN NUMBER DEFAULT NULL,
664 p_source_inspected IN NUMBER,
665 p_process_id IN NUMBER,
666 p_lpn_id IN NUMBER) IS
667
668
669 x_lotID number := null;
670
671 BEGIN
672
673 x_lotID := p_lotID;
674
675 if x_lotID is null then
676 x_lotID := qa_skiplot_utility.get_lot_id;
677 end if;
678
679 insert into qa_skiplot_rcv_results(
680 INSP_LOT_ID,
681 CRITERIA_ID,
682 PROCESS_ID,
683 INTERFACE_TXN_ID,
684 SHIPMENT_LINE_ID,
685 RECEIPT_DATE,
686 MANUFACTURER_ID,
687 LPN_ID,
688 SOURCE_INSPECTED,
689 LOT_QTY,
690 TRANSACTED_QTY,
691 INSPECTION_STATUS,
692 INSPECTION_RESULT,
693 LAST_INSP_DATE,
694 VALID_FLAG,
695 LAST_UPDATE_DATE,
696 LAST_UPDATED_BY,
697 CREATION_DATE,
698 CREATED_BY,
699 LAST_UPDATE_LOGIN)
700 values(
701 x_lotID,
702 p_criteriaID,
703 p_process_id,
704 p_interface_txn_id,
705 null,
706 p_receipt_date,
707 p_manufacturer_id,
708 p_lpn_id,
709 decode(p_source_inspected, 1, 'Y', 'N'),
710 p_receipt_qty,
711 0,
712 p_insp_status,
713 null,
714 null,
715 1,
716 sysdate,
717 fnd_global.user_id,
718 sysdate,
719 fnd_global.user_id,
720 fnd_global.login_id);
721
722 END INSERT_RCV_RESULTS;
723
724 PROCEDURE STORE_LOT_PLANS(
725 p_applicablePlans IN qa_skiplot_utility.planList,
726 p_lotID IN NUMBER,
727 p_insp_status IN VARCHAR2) IS
728
729 plan_id number;
730 alter_flag varchar2(1);
731 i number;
732
733 BEGIN
734
735 i := p_applicablePlans.first;
736 while i is not null loop
737 plan_id := p_applicablePlans(i).plan_id;
738 alter_flag := nvl(p_applicablePlans(i).alternate_flag, 'N');
739
740 insert into qa_skiplot_lot_plans(
741 INSP_LOT_ID,
742 PLAN_ID,
743 ALTERNATE_FLAG,
744 SHIPMENT_LINE_ID,
745 PLAN_INSP_STATUS,
746 PLAN_INSP_RESULT,
747 SAMPLE_SIZE,
748 INSPECTED_QTY,
749 LAST_UPDATE_DATE,
750 LAST_UPDATED_BY,
751 CREATION_DATE,
752 CREATED_BY,
753 LAST_UPDATE_LOGIN)
754 values(
755 p_lotID,
756 plan_id,
757 alter_flag,
758 null,
759 p_insp_status,
760 null,
761 null,
762 0,
763 sysdate,
764 fnd_global.user_id,
765 sysdate,
766 fnd_global.user_id,
767 fnd_global.login_id);
768
769 i := p_applicablePlans.next(i);
770 end loop;
771
772
773 END STORE_LOT_PLANS;
774
775 END QA_SKIPLOT_EVAL_ENGINE;
[Home] [Help]