[Home] [Help]
PACKAGE BODY: APPS.QA_SKIPLOT_RES_ENGINE
Source
1 PACKAGE BODY QA_SKIPLOT_RES_ENGINE AS
2 /* $Header: qaslresb.pls 120.3 2008/02/08 09:13:27 pdube ship $ */
3
4
5 --
6 -- This procedure is an alternate wrapper for launch_shipment_action() to
7 -- call the RCV API to perform accept or reject. This new procedure enables
8 -- unit wise inspections with LPN and Lot/Serial controls. Thsi procedure
9 -- will be used only if one Inspection Plan is involved. Multiple Inspection
10 -- Plans Inspection will be executed through launch_shipment_action().
11 -- This procedure called from is launch_shipment_action().
12 -- Bug 3096256. For RCV/WMS Merge. kabalakr Thu Aug 28 08:34:59 PDT 2003.
13 --
14
15 PROCEDURE launch_shipment_action_int(
16 p_po_txn_processor_mode IN VARCHAR2,
17 p_po_group_id IN NUMBER,
18 p_collection_id IN NUMBER,
19 p_employee_id IN NUMBER,
20 p_transaction_id IN NUMBER,
21 p_uom IN VARCHAR2,
22 p_transaction_date IN DATE,
23 p_created_by IN NUMBER,
24 p_last_updated_by IN NUMBER,
25 p_last_update_login IN NUMBER) IS
26
27
28 x_return_status VARCHAR2(5);
29 x_msg_count NUMBER;
30 x_msg_data VARCHAR2(240);
31
32
33 CURSOR insp_coll_dtl IS
34 select organization_id, item_id, lpn_id, xfr_lpn_id,
35 lot_number, serial_number, insp_result, sum(insp_qty)
36 from qa_insp_collections_dtl_temp
37 where collection_id = p_collection_id
38 group by organization_id, item_id, lpn_id, xfr_lpn_id,
39 lot_number, serial_number, insp_result;
40
41 CURSOR item_uom_cur(l_item NUMBER, l_org NUMBER) IS
42 select primary_unit_of_measure
43 from mtl_system_items_b
44 where inventory_item_id = l_item
45 and organization_id = l_org;
46
47 l_lpn_id NUMBER;
48 l_xfr_lpn_id NUMBER;
49
50 -- OPM Conv R12 Tracking Bug 4345760
51 -- change variable size for lot num
52
53 l_lot_number qa_results.lot_number%TYPE;
54
55 l_serial_number VARCHAR2(30);
56 l_insp_result VARCHAR2(80);
57 l_insp_qty NUMBER;
58 l_org_id NUMBER;
59 l_item_id NUMBER;
60
61 l_primary_uom VARCHAR2(25);
62 l_primary_qty NUMBER;
63 l_int_txn_id NUMBER;
64 l_ser_txn_id NUMBER;
65
66 -- Added the below cursor and variables for Bug 3225280.
67 -- kabalakr Wed Oct 29 23:19:22 PST 2003.
68
69 CURSOR int_txn (grp_id NUMBER, txn_id NUMBER) IS
70 SELECT max(interface_transaction_id)
71 FROM rcv_transactions_interface
72 WHERE group_id = grp_id
73 AND parent_transaction_id = txn_id;
74
75 l_rti_int_txn_id NUMBER;
76
77 -- Bug 6781108
78 -- Added the following two variables to get the value
79 -- and pass to the RCV API
80 l_rti_sub_code mtl_secondary_inventories.secondary_inventory_name%TYPE :=NULL;
81 l_rti_loc_id NUMBER := NULL;
82 begin
83
84 -- First, post the Inspection details from qa_results onto
85 -- the temp table qa_insp_collections_dtl_temp.
86 -- Here we build the detail temp table for the plan.
87
88 qa_sampling_pkg.post_insp_coll_details(p_collection_id);
89
90
91 -- Fetch the records in qa_insp_collections_dtl_temp for calling the
92 -- RCV API. We have grouped the records in cursor so that it gives the
93 -- consolidated picture.
94
95 OPEN insp_coll_dtl;
96 LOOP
97
98 FETCH insp_coll_dtl INTO l_org_id,
99 l_item_id,
100 l_lpn_id,
101 l_xfr_lpn_id,
102 l_lot_number,
103 l_serial_number,
104 l_insp_result,
105 l_insp_qty;
106
107 EXIT WHEN insp_coll_dtl%NOTFOUND;
108
109 IF l_lpn_id IS NOT NULL THEN
110
111 IF l_xfr_lpn_id IS NULL THEN
112 l_xfr_lpn_id := l_lpn_id;
113 -- Bug 6781108
114 -- Calling this Procedure to get subinv_code and loc_id
115 -- in order to insert into RTI table
116 -- pdube Wed Feb 6 04:53:32 PST 2008
117 QLTDACTB.DEFAULT_LPN_SUB_LOC_INFO(L_LPN_ID,
118 L_XFR_LPN_ID,
119 l_rti_sub_code,
120 l_rti_loc_id);
121 END IF;
122 END IF;
123
124
125 -- First, call the RCV API for the Inspection.
126
127 -- Bug 6781108
128 -- Passing two variables to four parameters p_sub, p_loc_id,
129 -- p_from_subinv and p_from_loc_id as new API
130 -- for receiving needed these parameters
131 -- pdube Wed Feb 6 23:22:10 PST 2008
132 RCV_INSPECTION_GRP.INSERT_INSPECTION
133 (p_api_version => 1.1,
134 p_init_msg_list => NULL,
135 p_commit => 'F',
136 p_validation_level => NULL,
137 p_created_by => p_created_by,
138 p_last_updated_by => p_last_updated_by,
139 p_last_update_login => p_last_update_login,
140 p_employee_id => p_employee_id,
141 p_group_id => p_po_group_id,
142 p_transaction_id => p_transaction_id,
143 p_transaction_type => l_insp_result,
144 p_processing_mode => p_po_txn_processor_mode,
145 p_quantity => l_insp_qty,
146 p_uom => p_uom,
147 p_quality_code => null,
148 p_transaction_date => p_transaction_date,
149 p_comments => null,
150 p_reason_id => null,
151 p_vendor_lot => null,
152 p_lpn_id => l_lpn_id,
153 p_transfer_lpn_id => l_xfr_lpn_id,
154 p_qa_collection_id => p_collection_id,
155 p_return_status => x_return_status,
156 p_msg_count => x_msg_count,
157 p_msg_data => x_msg_data,
158 p_subinventory => L_RTI_SUB_CODE,
159 p_locator_id => L_RTI_LOC_ID,
160 p_from_subinventory => L_RTI_SUB_CODE,
161 p_from_locator_id => L_RTI_LOC_ID);
162
163
164 if x_return_status <> 'S' then
165 qa_skiplot_utility.insert_error_log (
166 p_module_name => 'QA_SKIPLOT_RES_ENGINE.LAUNCH_SHIPMENT_ACTION_INT',
167 p_error_message => 'QA_PO_INSP_ACTION_FAIL',
168 p_comments => x_msg_data);
169 fnd_message.clear;
170 fnd_message.set_name ('QA', 'QA_PO_INSP_ACTION_FAIL');
171 APP_EXCEPTION.RAISE_EXCEPTION;
172 end if;
173
174 -- Bug 6781108
175 -- Commenting the following fix for 3225280 and 3270283
176 -- as already handled above through the INSERT_INSPECTION API
177 -- pdube Wed Feb 6 04:53:32 PST 2008
178 /*-- Bug 3225280. Moved the Lot and serial insertion code after RCV
179 -- insert_inspection API because, we want the interface_transaction_id
180 -- of the ACCEPT and REJECT transactions to be passed to the WMS APIs
181 -- as product_transaction_id.
182 --
183 -- For this, first we need to find the interface_transaction_id of the
184 -- inspection record inserted by RCV API. The logic here is to fetch the
185 -- max(interface_transaction_id) from rti for the parent_transaction_id
186 -- and group_id combination. Since we are implementing this just after
187 -- RCV API call, it will fetch the interface_transaction_id of the
188 -- inspection record just inserted.
189 -- kabalakr. Wed Oct 29 23:19:22 PST 2003.
190 --
191
192 OPEN int_txn(p_po_group_id, p_transaction_id);
193 FETCH int_txn INTO l_rti_int_txn_id;
194 CLOSE int_txn;
195
196 -- Bug 3270283. For LPN inspections, we need to default the receiving
197 -- subinventory and Locator for the transfer LPN, if its a newly
198 -- created one OR, it has a LPN context 'Defined but not used'.
199 -- The new procedure DEFAULT_LPN_SUB_LOC_INFO() takes care of this
200 -- defaulting logic entirely. Hence just call this procedure if its
201 -- a LPN inspection. kabalakr Mon Mar 8 08:01:35 PST 2004.
202
203 IF l_lpn_id IS NOT NULL THEN
204
205 QLTDACTB.DEFAULT_LPN_SUB_LOC_INFO(l_lpn_id,
206 l_xfr_lpn_id,
207 l_rti_int_txn_id);
208
209 END IF; -- If l_lpn_id is not null*/
210 -- End of Bug 6781108
211
212
213 IF l_lot_number IS NOT NULL THEN
214
215 OPEN item_uom_cur(l_item_id, l_org_id);
216 FETCH item_uom_cur INTO l_primary_uom;
217 CLOSE item_uom_cur;
218
219
220 IF (l_primary_uom = p_uom) THEN
221 l_primary_qty := l_insp_qty;
222
223 ELSE
224 l_primary_qty := inv_convert.inv_um_convert
225 (l_item_id,
226 NULL,
227 l_insp_qty,
228 p_uom,
229 l_primary_uom,
230 NULL,
231 NULL);
232
233 END IF;
234
235 l_int_txn_id := NULL;
236
237 -- Now, call the Inventory/WMS API for Lot Insertion.
238 -- Passing NULL value to p_transaction_interface_id to allow the
239 -- API to generate one.
240
241 -- Bug 3225280. Changed the value passed as p_product_transaction_id
242 -- to l_rti_int_txn_id, derived above.
243
244
245 INV_RCV_INTEGRATION_APIS.INSERT_MTLI
246 (p_api_version => 1.0,
247 p_init_msg_lst => NULL,
248 x_return_status => x_return_status,
249 x_msg_count => x_msg_count,
250 x_msg_data => x_msg_data,
251 p_transaction_interface_id => l_int_txn_id,
252 p_transaction_quantity => l_insp_qty,
253 p_primary_quantity => l_primary_qty,
254 p_organization_id => l_org_id,
255 p_inventory_item_id => l_item_id,
256 p_lot_number => l_lot_number,
257 p_expiration_date => NULL,
258 p_status_id => NULL,
259 x_serial_transaction_temp_id => l_ser_txn_id,
260 p_product_code => 'RCV',
261 p_product_transaction_id => l_rti_int_txn_id);
262
263
264 if x_return_status <> 'S' then
265 qa_skiplot_utility.insert_error_log (
266 p_module_name => 'QA_SKIPLOT_RES_ENGINE.LAUNCH_SHIPMENT_ACTION_INT',
267 p_error_message => 'QA_WMS_LOT_INSERT_FAIL',
268 p_comments => x_msg_data);
269 fnd_message.clear;
270 fnd_message.set_name ('QA', 'QA_WMS_LOT_INSERT_FAIL');
271 APP_EXCEPTION.RAISE_EXCEPTION;
272 end if;
273
274
275 END IF;
276
277
278 IF l_serial_number IS NOT NULL THEN
279
280 IF l_lot_number IS NOT NULL THEN
281 l_int_txn_id := l_ser_txn_id;
282
283 ELSE
284 l_int_txn_id := NULL;
285
286 END IF;
287
288 -- Now, call the Inventory/WMS API for Serial Insertion.
289 -- Passing NULL value to p_transaction_interface_id to allow the
290 -- API to generate one.
291
292 -- Bug 3225280. Changed the value passed as p_product_transaction_id
293 -- to l_rti_int_txn_id, derived above.
294
295 INV_RCV_INTEGRATION_APIS.INSERT_MSNI
296 (p_api_version => 1.0,
297 p_init_msg_lst => NULL,
298 x_return_status => x_return_status,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data,
301 p_transaction_interface_id => l_int_txn_id,
302 p_fm_serial_number => l_serial_number,
303 p_to_serial_number => l_serial_number,
304 p_organization_id => l_org_id,
305 p_inventory_item_id => l_item_id,
306 p_status_id => NULL,
307 p_product_code => 'RCV',
308 p_product_transaction_id => l_rti_int_txn_id);
309
310
311 if x_return_status <> 'S' then
312 qa_skiplot_utility.insert_error_log (
313 p_module_name => 'QA_SKIPLOT_RES_ENGINE.LAUNCH_SHIPMENT_ACTION_INT',
314 p_error_message => 'QA_WMS_SER_INSERT_FAIL',
315 p_comments => x_msg_data);
316 fnd_message.clear;
317 fnd_message.set_name ('QA', 'QA_WMS_SER_INSERT_FAIL');
318 APP_EXCEPTION.RAISE_EXCEPTION;
319 end if;
320
321 END IF;
322
323
324 END LOOP;
325
326 END launch_shipment_action_int;
327
328
329 --
330 -- local function
331 --
332 function get_result_column(
333 p_plan_id in number) return varchar2 is
334
335 col_ref qa_skiplot_utility.refCursorTyp;
336 res_col qa_plan_chars.result_column_name%type;
337
338 begin
339 open col_ref for
340 'select result_column_name
341 from qa_plan_chars
342 where plan_id = :1 and
343 char_id = 8'
344 using p_plan_id;
345
346 fetch col_ref into res_col;
347
348 close col_ref;
349
350 return res_col;
351
352 end get_result_column;
353
354
355 --
356 -- local function
357 -- Note: this function will not work for wip since 'quantity'
358 -- is used as context element in wip, not as inspected quantity
359 -- in wip, inspected quantity needs to be passed from UI.
360 --
361 function get_plan_insp_qty (
362 p_collection_id in number,
363 p_plan_id in number) return number is
364
365 cursor qty(x_coll_id number, x_plan_id number) is
366 select sum(quantity)
367 from qa_results
368 where collection_id = x_coll_id and
369 plan_id = x_plan_id;
370
371 quantity number;
372
373 begin
374
375 open qty (p_collection_id, p_plan_id);
376 fetch qty into quantity;
377 close qty;
378
379 return nvl(quantity, 0);
380
381 end get_plan_insp_qty;
382
383
384 --
385 -- local function
386 --
387 function get_plan_result(
388 p_collection_id in number,
389 p_plan_id in number,
390 p_insp_qty out NOCOPY number,
391 p_accepted_qty out NOCOPY number,
392 p_rejected_qty out NOCOPY number) return varchar2 is
393
394 in_str varchar2(3000);
395 result_column qa_plan_chars.result_column_name%type;
396 sql_str varchar2(5000);
397 result varchar2(100);
398 insp_qty number;
399 accept_qty number;
400 reject_qty number;
401
402 begin
403
404 result_column := get_result_column (p_plan_id);
405 in_str :=
406 'select displayed_field ' ||
407 'from po_lookup_codes ' ||
408 'where lookup_type = ''ERT RESULTS ACTION'' and lookup_code = ''REJECT''';
409
410 --
411 -- check whether there is a rejection for the plan
412 -- since we store the inspection result meaning which
413 -- can be in any language, we use in statement to check
414 -- inspection result in all possible language
415 --
416
417 sql_str := 'select sum(quantity) from qa_results ' ||
418 'where collection_id = :1 and plan_id = :2 and '||
419 result_column || ' in (' || in_str || ' )';
420
421
422 execute immediate sql_str into reject_qty
423 using p_collection_id, p_plan_id;
424
425 p_insp_qty := get_plan_insp_qty (p_collection_id, p_plan_id);
426 p_rejected_qty := nvl(reject_qty, 0);
427 p_accepted_qty := p_insp_qty - p_rejected_qty;
428
429 if reject_qty > 0 then
430 return 'REJECT';
431 elsif p_accepted_qty > 0 then
432 return 'ACCEPT';
433 else
434 return null;
435 end if;
436
437 exception
438 when no_data_found then
439 return 'ACCEPT';
440 end get_plan_result;
441
442
443 --
444 -- local function checks whether inspection is pending
445 --
446 function are_all_plans_completed(
447 p_lot_plans lotPlanTable)
448 return varchar2 is
449
450 plan_status varchar2(20);
451 plan_result varchar2(20);
452 i number;
453
454 begin
455
456
457 i := p_lot_plans.first;
458 while i is not null loop
459 plan_result := p_lot_plans(i).plan_insp_status;
460 exit when plan_status = 'PENDING';
461 i := p_lot_plans.next(i);
462 end loop;
463
464 if plan_status = 'PENDING' then
465 return fnd_api.g_false;
466 else
467 return fnd_api.g_true;
468 end if;
469
470 end are_all_plans_completed;
471
472 --
473 -- local function checks whether inspection is rejected
474 --
475 function is_any_plan_rejected(
476 p_lot_plans lotPlanTable)
477 return varchar2 is
478
479 plan_result varchar2(20);
480 i number;
481
482 begin
483
484
485 i := p_lot_plans.first;
486 while i is not null loop
487 plan_result := p_lot_plans(i).plan_insp_result;
488 exit when plan_result = 'REJECT';
489 i := p_lot_plans.next(i);
490 end loop;
491
492
493 if plan_result = 'REJECT' then
494 return fnd_api.g_true;
495 else
496 return fnd_api.g_false;
497 end if;
498
499 end is_any_plan_rejected;
500
501 --
502 -- local function
503 -- in rcv inspection, due to transfering,
504 -- the lot qty may be splited.
505 --
506 function is_lot_qty_finished(
507 p_txn_qty in number,
508 p_shipment_line_id in number) return varchar2 is
509
510 cursor c (x_txn_qty number, x_shl_id number) is
511 select 'FINISHED' from qa_skiplot_rcv_results
512 where shipment_line_id = x_shl_id and
513 lot_qty <= transacted_qty + nvl(x_txn_qty, 0) ;
514
515 conclusion varchar2(20);
516
517 begin
518
519 --
520 -- maybe in wip, no need to check lot qty
521 --
522 if p_shipment_line_id is null then
523 return fnd_api.g_true;
524 end if;
525
526 open c (p_txn_qty, p_shipment_line_id);
527 fetch c into conclusion;
528 close c;
529
530 if conclusion = 'FINISHED' then
531 return fnd_api.g_true;
532 else
533 return fnd_api.g_false;
534 end if;
535 end is_lot_qty_finished;
536
537 --
538 -- local procedure
539 --
540 procedure update_plan_states(
541 p_insp_result in varchar2,
542 p_criteria_id in number,
543 p_lot_id in number default null,
544 p_shipment_line_id in number default null,
545 p_lot_size in number,
546 p_lot_plans in lotPlanTable,
547 p_txn in number,
548 p_prev_txn_type in varchar2 default null,
549 p_reinsp_flag in varchar2 default null) is
550
551 i number;
552 receipt_date date;
553 pid number;
554
555 cursor get_receipt_date (x_insp_lot_id number) is
556 select receipt_date
557 from qa_skiplot_rcv_results
558 where insp_lot_id = x_insp_lot_id;
559
560 cursor get_receipt_date2 (x_shl_id number) is
561 select receipt_date
562 from qa_skiplot_rcv_results
563 where shipment_line_id = x_shl_id;
564
565 --
566 -- given a criteria_id, lotsize and receipt date
567 -- process id should be unique.
568 --
569 cursor get_process_id (x_cid number, x_receipt_date date,
570 x_lotsize number) is
571 select qsa.process_id
572 from qa_skiplot_association qsa
573 where criteria_id = x_cid and
574 trunc(x_receipt_date) between
575 nvl(trunc(qsa.effective_from), trunc(x_receipt_date)) and
576 nvl(trunc(qsa.effective_to), trunc(x_receipt_date)) and
577 x_lotsize between
578 nvl(qsa.lotsize_from, x_lotsize) and
579 nvl(qsa.lotsize_to, x_lotsize);
580
581 begin
582
583 if p_shipment_line_id is not null then
584 open get_receipt_date2 (p_shipment_line_id);
585 fetch get_receipt_date2 into receipt_date;
586 close get_receipt_date2;
587 else
588 open get_receipt_date (p_lot_id);
589 fetch get_receipt_date into receipt_date;
590 close get_receipt_date;
591 end if;
592
593 open get_process_id (p_criteria_id, receipt_date, p_lot_size);
594 fetch get_process_id into pid;
595 close get_process_id;
596
597 --
598 -- if re-inspection changed the result from accept to reject
599 -- or if 1st inspection shows reject, reset the plan states
600 --
601 if p_reinsp_flag = fnd_api.g_true then
602
603 if p_prev_txn_type = 'ACCEPT' and p_insp_result = 'REJECT' then
604 qa_skiplot_utility.init_plan_states(
605 p_process_id => pid,
606 p_criteria_id => p_criteria_id,
607 p_txn => p_txn);
608 end if;
609
610 --
611 -- 1st inspection failed
612 --
613 elsif p_insp_result = 'REJECT' then
614
615 qa_skiplot_utility.init_plan_states(
616 p_process_id => pid,
617 p_criteria_id => p_criteria_id,
618 p_txn => p_txn);
619
620 --
621 -- 1st inspection accepted
622 -- update the plan states one by one if it's not alternate plan
623 --
624 else
625
626 i := p_lot_plans.first;
627 while i is not null loop
628 --
629 -- alternate plan does not have plan state
630 --
631 if p_lot_plans(i).alternate_flag is null or
632 p_lot_plans(i).alternate_flag <> 'Y' then
633 update_plan_state(
634 p_insp_result => p_insp_result,
635 p_criteria_id => p_criteria_id,
636 p_process_id => pid,
637 p_lot_plan => p_lot_plans(i),
638 p_txn =>p_txn);
639 end if;
640 i := p_lot_plans.next(i);
641 end loop;
642 end if;
643
644 end update_plan_states;
645
646
647 --
648 -- procedure set skiplot flag to 'Y'.
649 --
650
651 procedure set_skiplot_flag(
652 p_collection_id in number) is
653
654 begin
655
656 update qa_insp_collections_temp
657 set skiplot_flag = 'Y'
658 where collection_id = p_collection_id;
659
660 end set_skiplot_flag;
661
662 --
663 -- local function
664 -- check the previous transaction type.
665 -- This check is needed to decide whether the current
666 -- inspection is re-inspection.
667 --
668 procedure check_txn_type (
669 p_rcv_txn_id in number,
670 p_txn_type out NOCOPY varchar2,
671 p_reinsp_flag out NOCOPY varchar2) is
672
673 cursor txn_type (x_txn_id in number) is
674 select transaction_type
675 from rcv_transactions
676 where transaction_id = x_txn_id;
677
678 begin
679
680 open txn_type (p_rcv_txn_id);
681 fetch txn_type into p_txn_type;
682 close txn_type;
683
684 if p_txn_type in ( 'ACCEPT', 'REJECT') then
685 p_reinsp_flag := fnd_api.g_true;
686 else
687 p_reinsp_flag := fnd_api.g_false;
688
689 end if;
690
691 end check_txn_type;
692
693 --
694 -- insert the inspecion result to po rcv tables.
695 --
696
697 PROCEDURE PROCESS_SKIPLOT_RESULT (
698 p_collection_id IN NUMBER,
699 p_insp_lot_id IN NUMBER DEFAULT NULL,
700 p_shipment_line_id IN NUMBER DEFAULT NULL,
701 p_inspected_qty IN NUMBER DEFAULT NULL,
702 p_total_txn_qty IN NUMBER DEFAULT NULL,
703 p_rcv_txn_id IN NUMBER DEFAULT NULL,
704 p_lot_result OUT NOCOPY VARCHAR2) IS
705
706 lot_result VARCHAR2(30);
707 lot_plans lotPlanTable;
708 criteria_id NUMBER;
709 txn NUMBER;
710 prev_txn_type varchar2(30);
711 reinsp_flag varchar2(10);
712
713 BEGIN
714
715 --
716 -- set skip lot flag in qa_insp_collections_temp table.
717 --
718 set_skiplot_flag (p_collection_id);
719
720
721 --
722 -- check previous txn type
723 --
724 if p_rcv_txn_id is not null then
725 check_txn_type (p_rcv_txn_id,prev_txn_type, reinsp_flag);
726 end if;
727
728 --
729 -- update lot plans table with inspection
730 -- status and results and return back the
731 -- plan list
732 --
733 update_lot_plans(
734 p_collection_id => p_collection_id,
735 p_insp_lot_id => p_insp_lot_id,
736 p_shipment_line_id => p_shipment_line_id,
737 p_rcv_txn_id => p_rcv_txn_id,
738 p_inspected_qty => p_inspected_qty,
739 p_prev_txn_type => prev_txn_type,
740 p_reinsp_flag => reinsp_flag);
741
742 --
743 -- update lot result table with the lot
744 -- inspection result
745 --
746
747 update_skiplot_result(
748 p_collection_id => p_collection_id,
749 p_insp_lot_id => p_insp_lot_id,
750 p_shipment_line_id => p_shipment_line_id,
751 p_total_txn_qty => p_total_txn_qty,
752 p_prev_txn_type => prev_txn_type,
753 p_reinsp_flag => reinsp_flag,
754 p_criteria_id => criteria_id, -- out parameter
755 p_result => lot_result, -- out parameter
756 p_lot_plans => lot_plans); -- out parameter
757
758
759 --
760 -- update plan state table for each plan
761 --
762 if p_shipment_line_id is null then
763 txn := QA_SKIPLOT_UTILITY.WIP;
764 else
765 txn := QA_SKIPLOT_UTILITY.RCV;
766 end if;
767
768 update_plan_states(
769 p_insp_result => lot_result,
770 p_criteria_id => criteria_id,
771 p_shipment_line_id => p_shipment_line_id,
772 p_lot_id => p_insp_lot_id,
773 p_lot_size => p_total_txn_qty,
774 p_lot_plans => lot_plans,
775 p_txn =>txn,
776 p_prev_txn_type => prev_txn_type,
777 p_reinsp_flag => reinsp_flag);
778
779 p_lot_result := lot_result;
780
781
782 EXCEPTION
783 WHEN OTHERS THEN
784 qa_skiplot_utility.insert_error_log (
785 p_module_name => 'QA_SKIPLOT_RES_ENGINE.PROCESS_SKIPLOT_RESULT',
786 p_error_message => 'QA_SKIPLOT_PROCESS_RES_ERROR',
787 p_comments => SUBSTR (SQLERRM , 1 , 240));
788
789 fnd_message.clear;
790 fnd_message.set_name ('QA', 'QA_SKIPLOT_PROCESS_RES_ERROR');
791 APP_EXCEPTION.RAISE_EXCEPTION;
792
793 END PROCESS_SKIPLOT_RESULT;
794
795 PROCEDURE PROCESS_SKIPLOT_RESULT (
796 p_collection_id IN NUMBER,
797 p_lpn_id IN NUMBER,
798 p_inspected_qty IN NUMBER,
799 p_total_txn_qty IN NUMBER,
800 p_lot_result OUT NOCOPY VARCHAR2) IS
801
802
803 cursor shls (x_lpn_id number) is
804 select shipment_line_id
805 from qa_skiplot_rcv_results
806 where lpn_id = x_lpn_id;
807
808 shlid number;
809 lot_result varchar2(30);
810
811 BEGIN
812
813 for id in shls (p_lpn_id) loop
814 process_skiplot_result (
815 p_collection_id => p_collection_id,
816 p_shipment_line_id => id.shipment_line_id,
817 p_inspected_qty => p_inspected_qty,
818 p_total_txn_qty => p_total_txn_qty,
819 p_lot_result => lot_result);
820 end loop;
821 p_lot_result := lot_result;
822
823 END;
824
825 --
826 -- added p_receipt_num, p_rma_id and p_int_ship_id as parameters
827 -- to fix bug 2374625.
828 -- Changed static cursor to ref cursor
829 -- Also tuned sql query based on po number for better performance
830 -- jezheng
831 -- Tue May 21 18:24:03 PDT 2002
832 --
833 PROCEDURE MSCA_PROCESS_SKIPLOT_RESULT ( p_collection_id IN NUMBER,
834 p_po_num IN VARCHAR2,
835 p_receipt_num IN VARCHAR2,
836 p_rma_id IN NUMBER,
837 p_int_ship_id IN NUMBER,
838 p_item IN VARCHAR2,
839 p_revision IN VARCHAR2,
840 p_org_id IN NUMBER,
841 p_inspected_qty IN NUMBER,
842 p_total_txn_qty IN NUMBER,
843 x_lot_result OUT NOCOPY VARCHAR2) IS
844 l_item_id NUMBER;
845 l_lot_result VARCHAR2(30);
846 l_shl_id NUMBER;
847 shls_query VARCHAR2(10000);
848
849 TYPE shls_cur_type is REF CURSOR;
850 shls_cur shls_cur_type;
851
852 BEGIN
853
854
855 l_item_id := QA_FLEX_UTIL.get_item_id(p_org_id, p_item);
856
857 --
858 -- RMA Inspection
859 --
860 if p_rma_id is not null and p_rma_id > 0 then
861 shls_query :=
862 ' select distinct rs.shipment_line_id ' ||
863 ' from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh ' ||
864 ' where rsh.receipt_source_code = ''CUSTOMER'' ' ||
865 ' and rs.oe_order_header_id = :1 ' ||
866 ' and rs.to_organization_id = :2 ' ||
867 ' and rs.item_id = :3 ' ||
868 ' and (rs.item_revision = :4 OR ' ||
869 ' (rs.item_revision is null and :4 is null )) ' ||
870 ' and rs.rcv_transaction_id = rt.transaction_id ' ||
871 ' and rsh.shipment_header_id = rs.shipment_header_id ' ||
872 ' and rt.inspection_status_code = ''NOT INSPECTED'' ' ||
873 ' and rs.supply_type_code = ''RECEIVING'' ' ||
874 ' and rt.transaction_type <> ''UNORDERED'' ' ||
875 ' and rt.routing_header_id = 2 ';
876
877 open shls_cur for shls_query
878 using p_rma_id, p_org_id, l_item_id, p_revision, p_revision;
879
880 --
881 -- Intransit Shipment Inspection
882 --
883 elsif p_int_ship_id is not null and p_int_ship_id > 0 then
884 shls_query :=
885 ' select distinct rs.shipment_line_id ' ||
886 ' from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh ' ||
887 ' where rsh.receipt_source_code <> ''VENDOR'' ' ||
888 ' and rs.shipment_header_id = :1 ' ||
889 ' and rs.to_organization_id = :2 ' ||
890 ' and rs.item_id = :3 ' ||
891 ' and (rs.item_revision = :4 OR ' ||
892 ' (rs.item_revision is null and :4 is null)) ' ||
893 ' and rs.rcv_transaction_id = rt.transaction_id ' ||
894 ' and rsh.shipment_header_id = rs.shipment_header_id ' ||
895 ' and rt.inspection_status_code = ''NOT INSPECTED'' ' ||
896 ' and rs.supply_type_code = ''RECEIVING'' ' ||
897 ' and rt.transaction_type <> ''UNORDERED'' ' ||
898 ' and rt.routing_header_id = 2) ';
899
900 open shls_cur for shls_query
901 using p_int_ship_id, p_org_id, l_item_id, p_revision, p_revision;
902
903 --
904 -- Inspection based on receipt number
905 --
906 elsif p_receipt_num is not null then
907 shls_query :=
908 ' select distinct rsl.shipment_line_id ' ||
909 ' from rcv_supply rs, ' ||
910 ' rcv_transactions rt, ' ||
911 ' rcv_shipment_headers rsh, ' ||
912 ' rcv_shipment_lines rsl ' ||
913 ' where rsh.receipt_num = :1 ' ||
914 ' and rsh.shipment_header_id = rs.shipment_header_id ' ||
915 ' and rs.supply_type_code = ''RECEIVING'' ' ||
916 ' and rs.rcv_transaction_id = rt.transaction_id ' ||
917 ' and rt.inspection_status_code = ''NOT INSPECTED'' ' ||
918 ' and rt.transaction_type <> ''UNORDERED'' ' ||
919 ' and rt.routing_header_id = 2 ' ||
920 ' and rsh.shipment_header_id = rsl.shipment_header_id ';
921
922 open shls_cur for shls_query
923 using p_receipt_num;
924
925 --
926 -- Default: inspection based on PO number
927 --
928 else
929 shls_query :=
930 ' SELECT distinct rs.shipment_line_id ' ||
931 ' FROM rcv_supply rs, rcv_transactions rt, po_headers ph ' ||
932 ' WHERE rs.rcv_transaction_id = rt.transaction_id ' ||
933 ' AND rs.po_header_id = ph.po_header_id ' ||
934 ' AND ph.segment1 = :1 ' ||
935 ' AND rs.to_organization_id = :2 ' ||
936 ' AND rs.item_id = :3 ' ||
937 ' AND (rs.item_revision = :4 OR ' ||
938 ' (rs.item_revision is null AND :4 is null)) ' ||
939 ' AND rt.inspection_status_code = ''NOT INSPECTED'' ';
940
941 open shls_cur for shls_query
942 using p_po_num, p_org_id, l_item_id, p_revision, p_revision;
943
944 end if;
945
946 loop
947 fetch shls_cur into l_shl_id;
948
949 exit when shls_cur%notfound;
950
951 process_skiplot_result (
952 p_collection_id => p_collection_id,
953 p_shipment_line_id =>l_shl_id,
954 p_inspected_qty => p_inspected_qty,
955 p_total_txn_qty => p_total_txn_qty,
956 p_lot_result => l_lot_result);
957 end loop;
958
959 x_lot_result := l_lot_result;
960
961 END MSCA_PROCESS_SKIPLOT_RESULT;
962
963 --
964 -- local function
965 -- get previous inspection history information
966 --
967 procedure get_prev_insp_info (
968 p_rcv_txn_id in number,
969 p_plan_id in number,
970 p_insp_qty out NOCOPY number,
971 p_acpt_qty out NOCOPY number,
972 p_rej_qty out NOCOPY number) is
973
974 cursor get_coll_id (x_txn_id in number) is
975 select qa_collection_id
976 from rcv_transactions
977 where transaction_id = x_txn_id;
978
979 coll_id number;
980 result varchar2(100);
981
982 begin
983
984 open get_coll_id (p_rcv_txn_id);
985 fetch get_coll_id into coll_id;
986 close get_coll_id;
987
988 result:= get_plan_result (
989 coll_id,
990 p_plan_id,
991 p_insp_qty, -- out parameter
992 p_acpt_qty, -- out parameter
993 p_rej_qty); -- out parameter
994
995 end get_prev_insp_info;
996
997
998 PROCEDURE UPDATE_LOT_PLANS(
999 p_collection_id IN NUMBER,
1000 p_insp_lot_id IN NUMBER,
1001 p_rcv_txn_id IN NUMBER,
1002 p_shipment_line_id IN NUMBER,
1003 p_inspected_qty IN NUMBER DEFAULT NULL,
1004 p_prev_txn_type IN VARCHAR2 DEFAULT NULL,
1005 p_reinsp_flag IN VARCHAR2 DEFAULT NULL) IS
1006
1007 cursor lotPlans (x_insp_lot_id number) is
1008 select *
1009 from qa_skiplot_lot_plans
1010 where insp_lot_id = x_insp_lot_id;
1011
1012 cursor lotPlans2 (x_shl_id number) is
1013 select *
1014 from qa_skiplot_lot_plans
1015 where shipment_line_id = x_shl_id;
1016
1017 res_col varchar2(30);
1018 result varchar2(100);
1019 insp_qty number;
1020 plan_status varchar2(20);
1021 sql_str varchar2(3000);
1022 acpt_qty number;
1023 rej_qty number;
1024 prev_insp_qty number;
1025 prev_acpt_qty number;
1026 prev_rej_qty number;
1027
1028 BEGIN
1029
1030 --
1031 -- loop through all the plans for the lot
1032 --
1033
1034 --
1035 -- in PO receiving scenario, shipment_line_id is provided
1036 -- then use shipment_line_id
1037 -- in other scenario where shipment_line_id does not make sense
1038 -- use insp_lot_id
1039 --
1040 if p_shipment_line_id is not null then
1041 for lp in lotPlans2(p_shipment_line_id) loop
1042
1043 --
1044 -- get plan result and accept and reject quantities
1045 --
1046 result := get_plan_result (
1047 p_collection_id => p_collection_id,
1048 p_plan_id => lp.plan_id,
1049 p_insp_qty => insp_qty,
1050 p_accepted_qty => acpt_qty,
1051 p_rejected_qty => rej_qty);
1052
1053 --
1054 -- re-inspection
1055 --
1056 if p_reinsp_flag = fnd_api.g_true then
1057 --
1058 -- when only part of the lot qty is re-inspected
1059 -- we need to get the historical inspection info.
1060 -- this typically happens when transfering txn is done
1061 -- before inspection
1062 --
1063 if lp.inspected_qty > insp_qty then
1064 get_prev_insp_info (
1065 p_rcv_txn_id,
1066 lp.plan_id,
1067 prev_insp_qty, -- out parameter
1068 prev_acpt_qty, -- out parameter
1069 prev_rej_qty); -- out parameter
1070
1071 acpt_qty := nvl(lp.accepted_qty, 0) - prev_acpt_qty + acpt_qty;
1072 rej_qty := nvl(lp.rejected_qty, 0) - prev_rej_qty + rej_qty;
1073 if rej_qty > 0 then
1074 result := 'REJECT';
1075 else
1076 result := 'ACCEPT';
1077 end if;
1078
1079 --
1080 -- when total quantity is re-inspected, simply take the
1081 -- new result, acpt_qty and rej_qty
1082 --
1083 else
1084 null;
1085 end if;
1086
1087 --
1088 -- not re-inspection
1089 --
1090 else
1091 --
1092 -- if transfering transaction splits a shipment line
1093 -- into multiple lines. As long as one line
1094 -- is rejected, the shipment line is rejected.
1095 -- i.e. the current result will not override the previous
1096 -- 'reject' decision
1097 --
1098 if lp.plan_insp_result = 'REJECT' then
1099 result := 'REJECT';
1100 end if;
1101
1102 insp_qty := nvl(lp.inspected_qty, 0) + insp_qty;
1103 acpt_qty := nvl(lp.accepted_qty, 0) + acpt_qty;
1104 rej_qty := nvl(lp.rejected_qty, 0) + rej_qty;
1105
1106 end if;
1107
1108 if result is not null then
1109
1110 --
1111 -- update lot plans table
1112 --
1113 sql_str := 'update qa_skiplot_lot_plans set ' ||
1114 'plan_insp_status = ''INSPECTED'', ' ||
1115 'plan_insp_result = :1, ' ||
1116 'inspected_qty = :2 ,' ||
1117 'accepted_qty = :3, ' ||
1118 'rejected_qty = :4, ' ||
1119 'collection_id = :5 ' ||
1120 'where shipment_line_id = :6 and ' ||
1121 'plan_id = :7 ';
1122
1123 execute immediate sql_str
1124 using result, insp_qty, acpt_qty, rej_qty, p_collection_id,
1125 p_shipment_line_id, lp.plan_id;
1126
1127 end if;
1128
1129 end loop;
1130 else -- p_insp_lot_id must be provided
1131 for lp in lotPlans(p_insp_lot_id) loop
1132
1133 --
1134 -- get plan result and accept and reject quantities
1135 --
1136 result := get_plan_result (
1137 p_collection_id => p_collection_id,
1138 p_plan_id => lp.plan_id,
1139 p_insp_qty => insp_qty,
1140 p_accepted_qty => acpt_qty,
1141 p_rejected_qty => rej_qty);
1142
1143 --
1144 -- re-inspection
1145 --
1146 if p_reinsp_flag = fnd_api.g_true then
1147 --
1148 -- Take the latest result, insp_qty, acpt_qty and rej_qty
1149 --
1150 null;
1151 --
1152 -- not re-inspection
1153 --
1154 else
1155 --
1156 -- if transfering transaction splits a shipment line
1157 -- into multiple lines. As long as one line
1158 -- is rejected, the shipment line is rejected.
1159 -- i.e. the current result will not override the previous
1160 -- 'reject' decision
1161 --
1162 if lp.plan_insp_result = 'REJECT' then
1163 result := 'REJECT';
1164 end if;
1165
1166 insp_qty := nvl(lp.inspected_qty, 0) + insp_qty;
1167 acpt_qty := nvl(lp.accepted_qty, 0) + acpt_qty;
1168 rej_qty := nvl(lp.rejected_qty, 0) + rej_qty;
1169
1170 end if;
1171
1172 if result is not null then
1173 --
1174 -- update lot plans table
1175 -- if plan was rejected before, keep the result
1176 --
1177 sql_str := 'update qa_skiplot_lot_plans set ' ||
1178 'plan_insp_status = ''INSPECTED'', ' ||
1179 'plan_insp_result = :1, ' ||
1180 'inspected_qty = :2 ,' ||
1181 'accepted_qty = :3, ' ||
1182 'rejected_qty = :4, ' ||
1183 'collection_id = :5 ' ||
1184 'where insp_lot_id = :6 and ' ||
1185 'plan_id = :7 ';
1186
1187 execute immediate sql_str
1188 using result, insp_qty, acpt_qty, rej_qty, p_collection_id,
1189 p_insp_lot_id, lp.plan_id;
1190
1191 end if;
1192 end loop;
1193 end if;
1194
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197 qa_skiplot_utility.insert_error_log (
1198 p_module_name => 'QA_SKIPLOT_RES_ENGINE.UPDATE_LOT_PLANS',
1199 p_error_message => 'QA_SKIPLOT_RES_UPDATE_PLANS_ERR',
1200 p_comments => SUBSTR (SQLERRM , 1 , 240));
1201 fnd_message.clear;
1202 fnd_message.set_name ('QA', 'QA_SKIPLOT_RES_UPDATE_PLANS_ERR');
1203 APP_EXCEPTION.RAISE_EXCEPTION;
1204
1205 END UPDATE_LOT_PLANS;
1206
1207 PROCEDURE UPDATE_SKIPLOT_RESULT(
1208 p_collection_id IN NUMBER,
1209 p_insp_lot_id IN NUMBER DEFAULT NULL,
1210 p_shipment_line_id IN NUMBER DEFAULT NULL,
1211 p_total_txn_qty IN NUMBER DEFAULT NULL,
1212 p_prev_txn_type IN VARCHAR2 DEFAULT NULL,
1213 p_reinsp_flag IN VARCHAR2 DEFAULT NULL,
1214 p_criteria_id OUT NOCOPY NUMBER,
1215 p_lot_plans OUT NOCOPY lotPlanTable,
1216 p_result OUT NOCOPY VARCHAR2) IS
1217
1218 c_id NUMBER := null;
1219
1220 -- values: 'INCOMPLETE', 'REJECT', 'ACCEPT'
1221 lot_result VARCHAR2(20);
1222
1223 -- values: 'PENDING', 'INSPECTED'
1224 lot_status VARCHAR2(20);
1225 lot_plans lotPlanTable;
1226
1227 cursor lotPlans (x_insp_lot_id number) is
1228 select *
1229 from qa_skiplot_lot_plans
1230 where insp_lot_id = x_insp_lot_id;
1231
1232 cursor lotPlans2 (x_shl_id number) is
1233 select *
1234 from qa_skiplot_lot_plans
1235 where shipment_line_id = x_shl_id;
1236
1237 cursor sampling_result (x_coll_id number) is
1238 select sampling_flag, lot_result
1239 from qa_insp_collections_temp
1240 where collection_id = x_coll_id;
1241
1242 sampling_flag varchar2(1);
1243
1244 BEGIN
1245
1246 --
1247 -- fetch the lot plans into pl/sql table
1248 --
1249
1250 --
1251 -- in receiving inspection shipment_line_id will be provided
1252 -- while in other inspections, insp_lot_id is used because
1253 -- shipment_line_id does not make sense
1254 --
1255 if p_shipment_line_id is not null then
1256 for lp in lotPlans2 (p_shipment_line_id) loop
1257 lot_plans (lp.plan_id) := lp;
1258 end loop;
1259 else -- insp_lot_id must be provided
1260 for lp in lotPlans (p_insp_lot_id) loop
1261 lot_plans (lp.plan_id) := lp;
1262 end loop;
1263 end if;
1264
1265 p_lot_plans := lot_plans;
1266
1267 open sampling_result (p_collection_id);
1268 fetch sampling_result into sampling_flag, lot_result;
1269 close sampling_result;
1270
1271 --
1272 -- if not sampling, the lot result need to be calculated.
1273 --
1274 if sampling_flag is null or sampling_flag <> 'Y' then
1275
1276 --
1277 -- if any plan is rejected, reject the lot
1278 --
1279 if is_any_plan_rejected(lot_plans) = fnd_api.g_true then
1280 lot_result := 'REJECT';
1281 lot_status := 'INSPECTED';
1282 --
1283 -- if some plans are incomplete, lot result can not be decided.
1284 -- this usually will not happen since we catch them at UI part
1285 --
1286 elsif are_all_plans_completed (lot_plans) = fnd_api.g_false then
1287 lot_result := 'INCOMPLETE';
1288 lot_status := 'PENDING';
1289 --
1290 -- only happens in receiving inspection when transafering
1291 -- occurs before inspection
1292 --
1293 elsif is_lot_qty_finished (p_total_txn_qty, p_shipment_line_id)
1294 = fnd_api.g_false then
1295 lot_result := 'ACCEPT';
1296 lot_status := 'PENDING';
1297 --
1298 -- if no rejection, all plans finished and lot qty finished,
1299 -- accept the lot
1300 --
1301 else
1302 lot_result := 'ACCEPT';
1303 lot_status := 'INSPECTED';
1304 end if;
1305 else
1306 lot_status := 'INSPECTED';
1307 end if;
1308
1309 --
1310 -- If there are incomplete inspection plans and no rejection found,
1311 -- the lot result can not be decided.
1312 -- Do not update skiplot result table.
1313 --
1314
1315 if lot_result <> 'INCOMPLETE' then
1316
1317 --
1318 -- if original inspection result is rejected, keep it.
1319 -- current transacted qty needs to be added to original
1320 -- transacted_qty
1321 --
1322
1323 --
1324 -- if shipment_line_id is provided, use it,
1325 -- otherwise use insp_lot_id
1326 --
1327 -- valid_flag = 3 indicate result is entered, but invalid
1328 -- after the parent txn goes through fine, the result will
1329 -- be set to valid_flag = 4 indicating valid
1330 --
1331
1332 if p_shipment_line_id is not null then
1333 --
1334 --
1335 update qa_skiplot_rcv_results
1336 set inspection_status = lot_status,
1337 inspection_result = decode(p_reinsp_flag, fnd_api.g_true, lot_result,
1338 decode (inspection_result, 'REJECT', 'REJECT', lot_result)),
1339 transacted_qty = decode (p_reinsp_flag, fnd_api.g_true, transacted_qty,
1340 (nvl(transacted_qty, 0) + nvl(p_total_txn_qty, 0))),
1341 last_insp_date = sysdate,
1342 valid_flag = 2,
1343 last_update_date = sysdate,
1344 last_updated_by = fnd_global.user_id,
1345 last_update_login = fnd_global.login_id
1346 where shipment_line_id = p_shipment_line_id
1347 returning criteria_id, inspection_result into c_id, lot_result;
1348 else
1349 update qa_skiplot_rcv_results
1350 set inspection_status = lot_status,
1351 inspection_result = decode(p_reinsp_flag, fnd_api.g_true, lot_result,
1352 decode (inspection_result, 'REJECT', 'REJECT', lot_result)),
1353 transacted_qty = decode(p_reinsp_flag, fnd_api.g_true, transacted_qty,
1354 (nvl(transacted_qty, 0) + nvl(p_total_txn_qty, 0))),
1355 last_insp_date = sysdate,
1356 valid_flag = 2,
1357 last_update_date = sysdate,
1358 last_updated_by = fnd_global.user_id,
1359 last_update_login = fnd_global.login_id
1360 where insp_lot_id = p_insp_lot_id
1361 returning criteria_id, inspection_result into c_id, lot_result;
1362 end if;
1363 end if;
1364
1365 p_criteria_id := nvl(c_id, -1);
1366 p_result := lot_result;
1367
1368
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371 qa_skiplot_utility.insert_error_log (
1372 p_module_name => 'QA_SKIPLOT_RES_ENGINE.UPDATE_SKIPLOT_RESULT',
1373 p_error_message => 'QA_SKIPLOT_RES_UPDATE_RESULT_ERR',
1374 p_comments => SUBSTR (SQLERRM , 1 , 240));
1375 fnd_message.clear;
1376 fnd_message.set_name ('QA', 'QA_SKIPLOT_RES_UPDATE_RESULT_ERR');
1377 APP_EXCEPTION.RAISE_EXCEPTION;
1378
1379 END UPDATE_SKIPLOT_RESULT;
1380
1381
1382 PROCEDURE UPDATE_PLAN_STATE(
1383 p_insp_result IN VARCHAR2,
1384 p_criteria_id IN NUMBER,
1385 p_process_id IN NUMBER,
1386 p_lot_plan IN lot_plan_rec,
1387 p_txn IN NUMBER,
1388 p_prev_txn_type IN VARCHAR2 DEFAULT NULL,
1389 p_reinsp_flag IN VARCHAR2 DEFAULT NULL) IS
1390
1391 plan_state qa_skiplot_utility.plan_state_rec;
1392 forward_lot number;
1393 pp_id number;
1394 next_rule number;
1395
1396 BEGIN
1397
1398 qa_skiplot_utility.fetch_plan_state(
1399 p_plan_id => p_lot_plan.plan_id,
1400 p_criteria_id => p_criteria_id,
1401 p_process_id => p_process_id,
1402 p_txn => p_txn,
1403 p_plan_state => plan_state);
1404
1405 --
1406 -- re-inspection
1407 --
1408 if p_reinsp_flag = fnd_api.g_true then
1409 if p_prev_txn_type = 'ACCEPT' and p_insp_result = 'REJECT' then
1410 --
1411 -- reset plan states if re-inspection failed
1412 --
1413 qa_skiplot_utility.init_plan_state(
1414 p_plan_id => p_lot_plan.plan_id,
1415 p_criteria_id => p_criteria_id,
1416 p_process_id => plan_state.process_id,
1417 p_lot_id => p_lot_plan.insp_lot_id,
1418 p_txn => p_txn,
1419 p_process_plan_id => pp_id); -- out parameter
1420
1421 end if;
1422 --
1423 -- 1st inspection
1424 --
1425 elsif p_insp_result = 'REJECT' then
1426 --
1427 -- if lot is rejected, reset every inspection plan state
1428 --
1429 qa_skiplot_utility.init_plan_state(
1430 p_plan_id => p_lot_plan.plan_id,
1431 p_criteria_id => p_criteria_id,
1432 p_process_id => plan_state.process_id,
1433 p_lot_id => p_lot_plan.insp_lot_id,
1434 p_txn => p_txn,
1435 p_process_plan_id => pp_id); -- out parameter
1436
1437 --
1438 -- if round is not finished, update the round parameters
1439 --
1440 elsif qa_skiplot_utility.insp_round_finished(plan_state)
1441 = fnd_api.g_false then
1442
1443 if qa_skiplot_utility.enough_lot_accepted (plan_state)
1444 = fnd_api.g_false then
1445 forward_lot := 1;
1446 --
1447 -- if this is over inspection, do not forward the
1448 -- current lot pointer
1449 --
1450 else
1451 forward_lot := 0;
1452 end if;
1453
1454 qa_skiplot_utility.update_plan_state(
1455 p_process_plan_id => plan_state.process_plan_id,
1456 p_criteria_id => plan_state.criteria_id,
1457 p_next_lot => plan_state.current_lot + forward_lot,
1458 p_lot_accepted => plan_state.lot_accepted + 1,
1459 p_txn => p_txn);
1460
1461
1462 --
1463 -- if this round is done and there are more rounds or
1464 -- if this rule is done and there are no more rules or
1465 -- if date span exceeded then
1466 -- start a new round for this rule
1467 --
1468 elsif qa_skiplot_utility.more_rounds(plan_state)
1469 = fnd_api.g_true OR
1470 qa_skiplot_utility.get_next_insp_rule(plan_state) = -1 OR
1471 qa_skiplot_utility.date_reasonable (
1472 p_check_mode => qa_skiplot_utility.DATE_SPAN_CHECK,
1473 p_plan_state => plan_state) = fnd_api.g_false then
1474
1475 qa_skiplot_utility.update_plan_state(
1476 p_process_plan_id => plan_state.process_plan_id,
1477 p_criteria_id => plan_state.criteria_id,
1478 p_next_round => plan_state.current_round + 1,
1479 p_next_lot => 1,
1480 p_lot_accepted => 1,
1481 p_txn => p_txn);
1482
1483
1484 --
1485 -- if this rule is finished and there are more rules and
1486 -- date span is reasonable
1487 -- then go to next rule
1488 --
1489 else
1490 next_rule := qa_skiplot_utility.get_next_insp_rule(plan_state);
1491
1492 qa_skiplot_utility.update_plan_state(
1493 p_process_plan_id => plan_state.process_plan_id,
1494 p_criteria_id => plan_state.criteria_id,
1495 p_next_rule => next_rule,
1496 p_next_round => 1,
1497 p_next_lot => 1,
1498 p_lot_accepted => 1,
1499 p_txn => p_txn);
1500
1501 end if;
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 qa_skiplot_utility.insert_error_log (
1505 p_module_name => 'QA_SKIPLOT_RES_ENGINE.UPDATE_PLAN_STATE',
1506 p_error_message => 'QA_SKIPLOT_RES_UPDATE_STATE_ERR',
1507 p_comments => SUBSTR (SQLERRM , 1 , 240));
1508 fnd_message.clear;
1509 fnd_message.set_name ('QA', 'QA_SKIPLOT_RES_UPDATE_STATE_ERR');
1510 APP_EXCEPTION.RAISE_EXCEPTION;
1511
1512 END UPDATE_PLAN_STATE;
1513
1514 FUNCTION GET_SKIPLOT_FLAG (
1515 p_collection_id IN NUMBER) RETURN VARCHAR2 IS
1516
1517 cursor skiplot_flag (x_coll_id number) is
1518 select skiplot_flag
1519 from qa_insp_collections_temp
1520 where collection_id = x_coll_id;
1521
1522 x_skiplot_flag varchar2(1) := null;
1523
1524 BEGIN
1525 open skiplot_flag (p_collection_id);
1526 fetch skiplot_flag into x_skiplot_flag;
1527 close skiplot_flag;
1528
1529 if x_skiplot_flag = 'Y' then
1530 return fnd_api.g_true;
1531 elsif x_skiplot_flag = 'N' then
1532 return fnd_api.g_false;
1533 else
1534 return null;
1535 end if;
1536
1537 END GET_SKIPLOT_FLAG;
1538
1539 PROCEDURE SET_SKIPLOT_FLAG(
1540 p_collection_id IN NUMBER,
1541 p_skiplot_flag IN VARCHAR2) IS
1542
1543 BEGIN
1544
1545 update qa_insp_collections_temp
1546 set skiplot_flag = decode(p_skiplot_flag, 'T', 'Y','Y', 'Y', 'N')
1547 where collection_id = p_collection_id;
1548
1549 END SET_SKIPLOT_FLAG;
1550
1551 --
1552 -- local function
1553 --
1554 function get_rejected_qty(
1555 p_collection_id in number,
1556 p_lot_qty in number) return number is
1557
1558 cursor rej_qty(x_coll_id number) is
1559 select sum(rejected_qty)
1560 from qa_skiplot_lot_plans
1561 where collection_id = x_coll_id;
1562
1563 x_rej_qty number;
1564
1565 begin
1566 open rej_qty (p_collection_id);
1567 fetch rej_qty into x_rej_qty;
1568 close rej_qty;
1569
1570 if x_rej_qty is null then
1571 return 0;
1572 elsif x_rej_qty > p_lot_qty then
1573 return p_lot_qty;
1574 else
1575 return x_rej_qty;
1576 end if;
1577 end get_rejected_qty;
1578
1579
1580 PROCEDURE LAUNCH_SHIPMENT_ACTION (
1581 p_po_txn_processor_mode IN VARCHAR2,
1582 p_po_group_id IN NUMBER,
1583 p_collection_id IN NUMBER,
1584 p_employee_id IN NUMBER,
1585 p_transaction_id IN NUMBER,
1586 p_uom IN VARCHAR2,
1587 p_lotsize IN NUMBER,
1588 p_transaction_date IN DATE,
1589 p_created_by IN NUMBER,
1590 p_last_updated_by IN NUMBER,
1591 p_last_update_login IN NUMBER) IS
1592
1593 x_rejected_qty number;
1594 x_accepted_qty number;
1595
1596 x_return_status varchar2(5);
1597 x_msg_count number;
1598 x_msg_data varchar2(2400);
1599
1600 -- kmqa
1601 CURSOR plan_count_cur IS
1602 select count(*) AS insp_plans
1603 from qa_insp_plans_temp
1604 where collection_id = p_collection_id;
1605
1606 l_plan_count NUMBER;
1607
1608
1609 BEGIN
1610
1611 OPEN plan_count_cur;
1612 FETCH plan_count_cur INTO l_plan_count;
1613 CLOSE plan_count_cur;
1614
1615 -- If the Receiving Inspection involves only one Inspection Collection
1616 -- Plan, call the new procedure. This procedure supports unit wise
1617 -- inspection at lpn, lot and Serial levels.
1618
1619 IF (l_plan_count = 1) THEN
1620
1621 launch_shipment_action_int
1622 (p_po_txn_processor_mode => p_po_txn_processor_mode,
1623 p_po_group_id => p_po_group_id,
1624 p_collection_id => p_collection_id,
1625 p_employee_id => p_employee_id,
1626 p_transaction_id => p_transaction_id,
1627 p_uom => p_uom,
1628 p_transaction_date => p_transaction_date,
1629 p_created_by => p_created_by,
1630 p_last_updated_by => p_last_updated_by,
1631 p_last_update_login => p_last_update_login);
1632
1633 -- No Need to continue as the Inspections are completed.
1634 -- Return from the procedure.
1635 return;
1636
1637 END IF;
1638
1639
1640 x_rejected_qty := get_rejected_qty(p_collection_id,p_lotsize);
1641 x_accepted_qty := p_lotsize - x_rejected_qty;
1642
1643 --
1644 -- launch rejection action for rejected quantity
1645 --
1646
1647 --
1648 -- modified p_commit values from 'T' to 'F' to fix
1649 -- bug 2056343. If p_commit = 'T', PO will commit the
1650 -- work and the skiplot temp table will be cleared
1651 -- and the skiplot flag will not be available
1652 -- any more.
1653 -- this procedure is called in post-forms-commit
1654 -- the transaction will be committed anyway when the
1655 -- forms is committed. So no need to commit here.
1656 -- jezheng
1657 -- Mon Nov 12 14:12:44 PST 2001
1658 --
1659
1660 -- Modified the API call for RCV/EMS merge.
1661 -- p_api_version changed to 1.1. Also added p_lpn_id and
1662 -- p_transfer_lpn_id. Passed as NULL.
1663 -- kabalakr Thu Aug 28 08:34:59 PDT 2003.
1664
1665 if x_rejected_qty > 0 then
1666 RCV_INSPECTION_GRP.INSERT_INSPECTION(
1667 p_api_version => 1.1,
1668 p_init_msg_list => NULL,
1669 p_commit => 'F',
1670 p_validation_level => NULL,
1671 p_created_by => p_created_by,
1672 p_last_updated_by => p_last_updated_by,
1673 p_last_update_login => p_last_update_login,
1674 p_employee_id => p_employee_id,
1675 p_group_id => p_po_group_id,
1676 p_transaction_id => p_transaction_id,
1677 p_transaction_type => 'REJECT',
1678 p_processing_mode => p_po_txn_processor_mode,
1679 p_quantity => x_rejected_qty,
1680 p_uom => p_uom,
1681 p_quality_code => null,
1682 p_transaction_date => p_transaction_date,
1683 p_comments => null,
1684 p_reason_id => null,
1685 p_vendor_lot => null,
1686 p_lpn_id => null,
1687 p_transfer_lpn_id => null,
1688 p_qa_collection_id => p_collection_id,
1689 p_return_status => x_return_status,
1690 p_msg_count => x_msg_count,
1691 p_msg_data => x_msg_data);
1692 end if;
1693
1694 if x_return_status <> 'S' then
1695 qa_skiplot_utility.insert_error_log (
1696 p_module_name => 'QA_SKIPLOT_RES_ENGINE.LAUNCH_SHIPMENT_ACTION',
1697 p_error_message => 'QA_SKIPLOT_REJECTION_ACTION_ERROR',
1698 p_comments => x_msg_data);
1699 fnd_message.clear;
1700 fnd_message.set_name ('QA', 'QA_SKIPLOT_LAUNCH_ACTION_ERROR');
1701 APP_EXCEPTION.RAISE_EXCEPTION;
1702 end if;
1703
1704 --
1705 -- launch acceptance action for accepted qty
1706 --
1707 if x_accepted_qty > 0 then
1708 RCV_INSPECTION_GRP.INSERT_INSPECTION(
1709 p_api_version => 1.1,
1710 p_init_msg_list => NULL,
1711 p_commit => 'F',
1712 p_validation_level => NULL,
1713 p_created_by => p_created_by,
1714 p_last_updated_by => p_last_updated_by,
1715 p_last_update_login => p_last_update_login,
1716 p_employee_id => p_employee_id,
1717 p_group_id => p_po_group_id,
1718 p_transaction_id => p_transaction_id,
1719 p_transaction_type => 'ACCEPT',
1720 p_processing_mode => p_po_txn_processor_mode,
1721 p_quantity => x_accepted_qty,
1722 p_uom => p_uom,
1723 p_quality_code => null,
1724 p_transaction_date => p_transaction_date,
1725 p_comments => null,
1726 p_reason_id => null,
1727 p_vendor_lot => null,
1728 p_lpn_id => null,
1729 p_transfer_lpn_id => null,
1730 p_qa_collection_id => p_collection_id,
1731 p_return_status => x_return_status,
1732 p_msg_count => x_msg_count,
1733 p_msg_data => x_msg_data);
1734 end if;
1735 if x_return_status <> 'S' then
1736 qa_skiplot_utility.insert_error_log (
1737 p_module_name => 'QA_SKIPLOT_RES_ENGINE.LAUNCH_SHIPMENT_ACTION',
1738 p_error_message => 'QA_SKIPLOT_ACCEPTANCE_ACTION_ERROR',
1739 p_comments => x_msg_data);
1740 fnd_message.clear;
1741 fnd_message.set_name ('QA', 'QA_SKIPLOT_LAUNCH_ACTION_ERROR');
1742 APP_EXCEPTION.RAISE_EXCEPTION;
1743 end if;
1744
1745
1746 END LAUNCH_SHIPMENT_ACTION;
1747
1748 PROCEDURE CALCULATE_QUANT_RESULT (
1749 p_collection_id IN NUMBER,
1750 p_lotqty IN NUMBER,
1751 p_rej_qty OUT NOCOPY NUMBER,
1752 p_acc_qty OUT NOCOPY NUMBER) IS
1753
1754
1755 BEGIN
1756
1757 p_rej_qty := get_rejected_qty (
1758 p_collection_id,
1759 p_lotqty);
1760
1761 p_acc_qty := p_lotqty - p_rej_qty;
1762
1763 END CALCULATE_QUANT_RESULT;
1764
1765 /*
1766 anagarwa Wed Apr 10 12:48:10 PDT 2002
1767 Qa MSCA: Following method should actually be placed in qltutlfb.pls
1768 But due to GSCC error for qltutlfb.pls, I'm putting it here. The related bug
1769 is 2312644.
1770 Whenever this is moved back to qltutlfb.pls, the java file
1771 $QA_TOP/java/util/ContextElementTable.java should be changed
1772 */
1773
1774 FUNCTION get_asl_status(p_org_id NUMBER,
1775 p_po_num VARCHAR2,
1776 p_item_id NUMBER) RETURN VARCHAR2 IS
1777
1778 -- Bug 4958740. SQL Repository Fix SQL ID: 15008408
1779 CURSOR c(c_org_id NUMBER, c_po_num VARCHAR2, c_item_id NUMBER) IS
1780 SELECT past.status
1781 FROM po_approved_supplier_list pasl,
1782 po_asl_statuses past,
1783 po_headers ph
1784 WHERE ph.segment1 = c_po_num AND
1785 ph.vendor_id = pasl.vendor_id(+) AND
1786 ph.vendor_site_id = pasl.vendor_site_id(+) AND
1787 pasl.using_organization_id = c_org_id AND
1788 pasl.item_id = c_item_id AND
1789 pasl.asl_status_id = past.status_id(+);
1790
1791 /*
1792 select asl_status_dsp
1793 from po_asl_suppliers_v pasv,
1794 po_headers ph
1795 where ph.segment1 = c_po_num
1796 and ph.vendor_id = pasv.vendor_id(+)
1797 and ph.vendor_site_id = pasv.vendor_site_id(+)
1798 and pasv.using_organization_id = c_org_id
1799 and pasv.item_id = c_item_id;
1800 */
1801
1802 x_asl_status VARCHAR2(100);
1803
1804 BEGIN
1805
1806 IF ((p_org_id IS NULL) or (p_po_num is null)) THEN
1807 RETURN NULL;
1808 END IF;
1809
1810 OPEN c(p_org_id, p_po_num, p_item_id);
1811 FETCH c INTO x_asl_status;
1812 CLOSE c;
1813
1814 RETURN x_asl_status;
1815
1816 END get_asl_status;
1817
1818
1819
1820 END QA_SKIPLOT_RES_ENGINE;
1821