1 PACKAGE BODY QA_SAMPLING_PKG AS
2 /* $Header: qasamplb.pls 120.4 2008/02/08 09:14:48 pdube ship $ */
3
4
5 -- This procedure inserts the Details for the quantity left in the
6 -- lot, not inspected, onto the temp table - qa_insp_collections_dtl_temp.
7 -- Called from launch_shipment_action_int().
8 -- Bug 3096256. For RCV/WMS Merge. kabalakr Thu Aug 28 08:34:59 PDT 2003.
9
10 PROCEDURE post_lot_qty_not_insp(p_collection_id IN NUMBER,
11 p_quantity IN NUMBER,
12 p_result IN VARCHAR2) IS
13
14 CURSOR dtl_info IS
15 select organization_id, item_id, lpn_id
16 from qa_insp_collections_dtl_temp
17 where collection_id = p_collection_id
18 and rownum = 1;
19
20 l_lpn_id NUMBER;
21 l_org_id NUMBER;
22 l_item_id NUMBER;
23
24 BEGIN
25
26 -- We assume that these information are common for all inspection
27 -- records entered in qa_results.
28
29 OPEN dtl_info;
30 FETCH dtl_info INTO l_org_id, l_item_id, l_lpn_id;
31 CLOSE dtl_info;
32
33 -- Insert a record onto qa_insp_collections_dtl_temp for the Quantity
34 -- not inspected in the Lot.
35
36 -- Bug 3229571. Passing Lot_number and serial_number as 'DEFERRED'. The Lot
37 -- and serial numbers associated with the lot qty not inspected, will be derived
38 -- and attached to the inspection txn later. kabalakr.
39
40 insert into qa_insp_collections_dtl_temp
41 (collection_id,
42 occurrence,
43 organization_id,
44 item_id,
45 lpn_id,
46 xfr_lpn_id,
47 lot_number,
48 serial_number,
49 insp_result,
50 insp_qty
51 )
52 values
53 (p_collection_id,
54 NULL,
55 l_org_id,
56 l_item_id,
57 l_lpn_id,
58 l_lpn_id,
59 'DEFERRED',
60 'DEFERRED',
61 p_result,
62 p_quantity
63 );
64
65 END post_lot_qty_not_insp;
66
67
68
69 -- This procedure updates the records in qa_insp_collections_dtl_temp
70 -- with insp_result = 'REJECT', when the lot_result is 'REJECT'.
71 -- Called from launch_shipment_action_int().
72 -- Bug 3096256. For RCV/WMS Merge. kabalakr Thu Aug 28 08:34:59 PDT 2003.
73
74 PROCEDURE upd_insp_coll_dtl_result(p_collection_id IN NUMBER) IS
75
76 l_sql_string VARCHAR2(200);
77
78 BEGIN
79
80 l_sql_string := 'UPDATE qa_insp_collections_dtl_temp '||
81 ' SET insp_result = ''REJECT'''||
82 ' WHERE collection_id = :1';
83
84 EXECUTE IMMEDIATE l_sql_string USING p_collection_id;
85
86 END upd_insp_coll_dtl_result;
87
88
89 -- Bug 3229571. This procedure populates the qa_rcv_lot_ser_temp temp
90 -- table with the lot and serial info from supply tables.
91 -- Called from launch_shipment_action_int ().
92
93 PROCEDURE populate_lot_serial_temp(p_transaction_id IN NUMBER) IS
94
95 -- OPM Conv R12 Tracking Bug 4345760
96 -- change variable size for lot num
97
98 l_lot_num qa_results.lot_number%TYPE;
99
100
101 l_serial_num VARCHAR2(30);
102 l_quantity NUMBER;
103
104 CURSOR lot_ser_supply IS
105 select rls.lot_num, rss.serial_num, decode(rss.serial_num, NULL, rls.quantity, 1)
106 from rcv_lots_supply rls, rcv_serials_supply rss
107 where rls.transaction_id = rss.transaction_id (+)
108 and rls.transaction_id = p_transaction_id
109 and rls.lot_num = rss.lot_num (+)
110 UNION
111 select rls.lot_num, rss.serial_num, decode(rss.serial_num, NULL, rls.quantity, 1)
112 from rcv_lots_supply rls, rcv_serials_supply rss
113 where rls.transaction_id (+) = rss.transaction_id
114 and rss.transaction_id = p_transaction_id
115 and rls.lot_num (+) = rss.lot_num;
116
117
118 BEGIN
119
120 OPEN lot_ser_supply;
121 LOOP
122
123 FETCH lot_ser_supply INTO l_lot_num,
124 l_serial_num,
125 l_quantity;
126
127 EXIT WHEN lot_ser_supply%NOTFOUND;
128
129 -- The value for valid_flag is passes as 1 initally to indicate that the
130 -- record is valid.
131
132 insert into qa_rcv_lot_ser_temp
133 (rcv_txn_id,
134 lot_num,
135 serial_num,
136 quantity,
137 valid_flag
138 )
139 values
140 (p_transaction_id,
141 l_lot_num,
142 l_serial_num,
143 l_quantity,
144 1
145 );
146
147 END LOOP;
148
149 CLOSE lot_ser_supply;
150
151 END populate_lot_serial_temp;
152
153
154 -- Bug 3229571. This procedure updates the lot serial qty in the temp
155 -- table to reflect the inspected quantity.
156 -- Called from launch_shipment_action_int ().
157
158 PROCEDURE modify_lot_serial_temp(p_transaction_id IN NUMBER,
159 p_collection_id IN NUMBER) IS
160
161
162 -- OPM Conv R12 Tracking Bug 4345760
163 -- change variable size for lot num
164
165 l_lot_number qa_results.lot_number%TYPE;
166
167
168 l_serial_number VARCHAR2(30);
169 l_insp_qty NUMBER;
170 l_temp_qty NUMBER;
171 l_qty_rem NUMBER;
172
173 CURSOR lot_ser_dtl IS
174 select quantity
175 from qa_rcv_lot_ser_temp
176 where rcv_txn_id = p_transaction_id
177 and nvl(lot_num, '@@') = nvl(l_lot_number, '@@')
178 and nvl(serial_num, '@@') = nvl(l_serial_number, '@@');
179
180 CURSOR insp_dtl IS
181 select lot_number, serial_number, insp_qty
182 from qa_insp_collections_dtl_temp
183 where collection_id = p_collection_id;
184
185
186 BEGIN
187
188 OPEN insp_dtl;
189 LOOP
190
191 -- Fetch the inspected lt and serial info.
192
193 FETCH insp_dtl INTO l_lot_number,
194 l_serial_number,
195 l_insp_qty;
196
197 EXIT WHEN insp_dtl%NOTFOUND;
198
199 -- Exit if the lot and serial are having NULL values.
200
201 IF (l_lot_number IS NULL) AND (l_serial_number IS NULL) THEN
202
203 EXIT;
204 END IF;
205
206 -- Fetch the qty toatal qty for the line in the temp table.
207 OPEN lot_ser_dtl;
208 FETCH lot_ser_dtl INTO l_temp_qty;
209
210 IF (lot_ser_dtl%NOTFOUND) THEN
211 CLOSE lot_ser_dtl;
212 EXIT;
213 END IF;
214
215 CLOSE lot_ser_dtl;
216
217 -- calculate the qty remaining to be inspected. This basically is the
218 -- lot qty left.
219
220 l_qty_rem := l_temp_qty - l_insp_qty;
221
222
223 -- If the qty remaining is zero, update the valid_flag to 2. This indicates
224 -- that the record is not to be used for further inspections.
225 -- If the qty is not zero, update, the remaining qty onto the temp table.
226
227 IF (l_qty_rem = 0) THEN
228
229 update qa_rcv_lot_ser_temp
230 set valid_flag = 2
231 where rcv_txn_id = p_transaction_id
232 and nvl(lot_num, '@@') = nvl(l_lot_number, '@@')
233 and nvl(serial_num, '@@') = nvl(l_serial_number, '@@');
234
235 ELSE
236
237 update qa_rcv_lot_ser_temp
238 set quantity = l_qty_rem
239 where rcv_txn_id = p_transaction_id
240 and nvl(lot_num, '@@') = nvl(l_lot_number, '@@')
241 and nvl(serial_num, '@@') = nvl(l_serial_number, '@@');
242
243 END IF;
244
245 END LOOP;
246
247 END modify_lot_serial_temp;
248
249
250
251
252 -- Bug 3229571. This procedure takes care of inserting the lot and serial
253 -- info onto the corresponding interface tables for the qty left in the lot
254 -- after sampling inspection.
255 -- Called from launch_shipment_action_int (). kabalakr.
256
257 PROCEDURE insert_lot_serial_txn(p_parent_txn_id NUMBER,
258 p_transaction_id NUMBER,
259 p_item_id NUMBER,
260 p_org_id NUMBER,
261 p_uom VARCHAR2) IS
262
263
264 x_return_status VARCHAR2(5);
265 x_msg_count NUMBER;
266 x_msg_data VARCHAR2(240);
267
268 -- OPM Conv R12 Tracking Bug 4345760
269 -- change variable size for lot num
270
271 l_lot_number qa_results.lot_number%TYPE;
272
273
274 l_serial_number VARCHAR2(30);
275 l_qty_rem NUMBER;
276
277 l_primary_uom VARCHAR2(25);
278 l_primary_qty NUMBER;
279 l_int_txn_id NUMBER;
280 l_ser_txn_id NUMBER;
281 l_rti_txn_id NUMBER;
282
283
284 CURSOR item_uom_cur IS
285 select primary_unit_of_measure
286 from mtl_system_items_b
287 where inventory_item_id = p_item_id
288 and organization_id = p_org_id;
289
290
291 CURSOR lot_ser_cur IS
292 select lot_num, serial_num, quantity
293 from qa_rcv_lot_ser_temp
294 where rcv_txn_id = p_parent_txn_id
295 and valid_flag = 1;
296
297
298 BEGIN
299
300 -- Assigning rcv_transaction_id of the inspection record to a local variable.
301 -- The parent transaction id value is stored in p_parent_txn_id to retrieve
302 -- the lot and serial info from the qa_rcv_lot_ser_temp temp table.
303
304 l_rti_txn_id := p_transaction_id;
305
306
307 OPEN lot_ser_cur;
308 LOOP
309
310 FETCH lot_ser_cur INTO l_lot_number,
311 l_serial_number,
312 l_qty_rem;
313
314 EXIT WHEN lot_ser_cur%NOTFOUND;
315
316
317 IF l_lot_number IS NOT NULL THEN
318
319 -- First, fetch the primary quantity.
320
321 OPEN item_uom_cur;
322 FETCH item_uom_cur INTO l_primary_uom;
323 CLOSE item_uom_cur;
324
325 IF (l_primary_uom = p_uom) THEN
326 l_primary_qty := l_qty_rem;
327
328 ELSE
329 l_primary_qty := inv_convert.inv_um_convert
330 (p_item_id,
331 NULL,
332 l_qty_rem,
333 p_uom,
334 l_primary_uom,
335 NULL,
336 NULL);
337
338 END IF;
339
340 l_int_txn_id := NULL;
341 l_ser_txn_id := NULL;
342
343 -- Now, call the Inventory/WMS API for Lot Insertion.
344 -- Passing NULL value to p_transaction_interface_id to allow the
345 -- API to generate one.
346
347 INV_RCV_INTEGRATION_APIS.INSERT_MTLI
348 (p_api_version => 1.0,
349 p_init_msg_lst => NULL,
350 x_return_status => x_return_status,
351 x_msg_count => x_msg_count,
352 x_msg_data => x_msg_data,
353 p_transaction_interface_id => l_int_txn_id,
354 p_transaction_quantity => l_qty_rem,
355 p_primary_quantity => l_primary_qty,
356 p_organization_id => p_org_id,
357 p_inventory_item_id => p_item_id,
358 p_lot_number => l_lot_number,
359 p_expiration_date => NULL,
360 p_status_id => NULL,
361 x_serial_transaction_temp_id => l_ser_txn_id,
362 p_product_code => 'RCV',
363 p_product_transaction_id => l_rti_txn_id);
364
365 if x_return_status <> 'S' then
366 qa_skiplot_utility.insert_error_log (
367 p_module_name => 'QA_SAMPLING_PKG.LAUNCH_SHIPMENT_ACTION_INT',
368 p_error_message => 'QA_WMS_LOT_INSERT_FAIL',
369 p_comments => x_msg_data);
370 fnd_message.clear;
371 fnd_message.set_name ('QA', 'QA_WMS_LOT_INSERT_FAIL');
372 APP_EXCEPTION.RAISE_EXCEPTION;
373 end if;
374
375
376 END IF;
377
378
379 IF (l_serial_number IS NOT NULL) THEN
380
381 IF (l_lot_number IS NOT NULL) THEN
382 l_int_txn_id := l_ser_txn_id;
383
384 ELSE
385 l_int_txn_id := NULL;
386
387 END IF;
388
389 -- Now, call the Inventory/WMS API for Serial Insertion.
390 -- Passing NULL value to p_transaction_interface_id to allow the
391 -- API to generate one.
392
393 INV_RCV_INTEGRATION_APIS.INSERT_MSNI
394 (p_api_version => 1.0,
395 p_init_msg_lst => NULL,
396 x_return_status => x_return_status,
397 x_msg_count => x_msg_count,
398 x_msg_data => x_msg_data,
399 p_transaction_interface_id => l_int_txn_id,
400 p_fm_serial_number => l_serial_number,
401 p_to_serial_number => l_serial_number,
402 p_organization_id => p_org_id,
403 p_inventory_item_id => p_item_id,
404 p_status_id => NULL,
405 p_product_code => 'RCV',
406 p_product_transaction_id => l_rti_txn_id);
407
408 if x_return_status <> 'S' then
409 qa_skiplot_utility.insert_error_log (
410 p_module_name => 'QA_SAMPLING_PKG.LAUNCH_SHIPMENT_ACTION_INT',
411 p_error_message => 'QA_WMS_SER_INSERT_FAIL',
412 p_comments => x_msg_data);
413 fnd_message.clear;
414 fnd_message.set_name ('QA', 'QA_WMS_SER_INSERT_FAIL');
415 APP_EXCEPTION.RAISE_EXCEPTION;
416 end if;
417
418 END IF;
419
420 END LOOP;
421
422 END insert_lot_serial_txn;
423
424
425
426 -- This procedure is an alternate wrapper for launch_shipment_action() to
427 -- call the RCV API to perform accept or reject. This new procedure enables
428 -- unit wise inspections with LPN and Lot/Serial controls. This procedure
429 -- will be used only if one Inspection Plan is involved. Multiple Inspection
430 -- Plans Inspection will be executed through launch_shipment_action().
431 -- This procedure is called from launch_shipment_action().
432 -- Bug 3096256. For RCV/WMS Merge. kabalakr Thu Aug 28 08:34:59 PDT 2003.
433 --
434
435 PROCEDURE launch_shipment_action_int(
436 p_po_txn_processor_mode IN VARCHAR2,
437 p_po_group_id IN NUMBER,
438 p_collection_id IN NUMBER,
439 p_employee_id IN NUMBER,
440 p_transaction_id IN NUMBER,
441 p_uom IN VARCHAR2,
442 p_transaction_date IN DATE,
443 p_created_by IN NUMBER,
444 p_last_updated_by IN NUMBER,
445 p_last_update_login IN NUMBER,
446 p_lot_size IN NUMBER,
447 p_lot_result IN VARCHAR2) IS
448
449 l_lot_qty_not_insp NUMBER;
450 l_lot_qty_insp NUMBER;
451
452 x_return_status VARCHAR2(5);
453 x_msg_count NUMBER;
454 x_msg_data VARCHAR2(240);
455
456 CURSOR lot_info_cur IS
457 select lot_size, lot_result, total_rejected_qty
458 from qa_insp_collections_temp
459 where collection_id = p_collection_id;
460
461 CURSOR lot_insp_cur IS
462 select sum(insp_qty) AS total_insp_qty
463 from qa_insp_collections_dtl_temp
464 where collection_id = p_collection_id;
465
466 CURSOR insp_coll_dtl IS
467 select organization_id, item_id, lpn_id, xfr_lpn_id,
468 lot_number, serial_number, insp_result, sum(insp_qty)
469 from qa_insp_collections_dtl_temp
470 where collection_id = p_collection_id
471 group by organization_id, item_id, lpn_id, xfr_lpn_id,
472 lot_number, serial_number, insp_result;
473
474 CURSOR item_uom_cur(l_item NUMBER, l_org NUMBER) IS
475 select primary_unit_of_measure
476 from mtl_system_items_b
477 where inventory_item_id = l_item
478 and organization_id = l_org;
479
480
481 l_lpn_id NUMBER;
482 l_xfr_lpn_id NUMBER;
483
484 -- OPM Conv R12 Tracking Bug 4345760
485 -- change variable size for lot num
486
487 l_lot_number qa_results.lot_number%TYPE;
488
489
490 l_serial_number VARCHAR2(30);
491 l_insp_result VARCHAR2(80);
492 l_insp_qty NUMBER;
493 l_org_id NUMBER;
494 l_item_id NUMBER;
495
496 l_primary_uom VARCHAR2(25);
497 l_primary_qty NUMBER;
498 l_int_txn_id NUMBER;
499 l_ser_txn_id NUMBER;
500
501 -- Added the below cursor and variables for Bug 3225280.
502 -- kabalakr Wed Oct 29 23:19:22 PST 2003.
503
504 CURSOR int_txn (grp_id NUMBER, txn_id NUMBER) IS
505 SELECT max(interface_transaction_id)
506 FROM rcv_transactions_interface
507 WHERE group_id = grp_id
508 AND parent_transaction_id = txn_id;
509
510 l_rti_int_txn_id NUMBER;
511
512 -- Bug 6781108
513 -- Added the following two variables to get the value
514 -- and pass to the RCV API
515 l_rti_sub_code mtl_secondary_inventories.secondary_inventory_name%TYPE :=NULL;
516 l_rti_loc_id NUMBER := NULL;
517 BEGIN
518 -- We dont need to check the sampling flag here because
519 -- launch_shipment_action() calls this only for sampling scenario.
520
521 -- First, post the Inspection details from qa_results onto
522 -- the temp table qa_insp_collections_dtl_temp.
523 -- Here we build the detail temp table for the plan.
524
525 post_insp_coll_details(p_collection_id);
526
527 -- Bug 3229571. Once the Inspection details are posted, post the lot
528 -- and serial info for the receiving txn onto qa_rcv_lot_ser_temp.
529
530 populate_lot_serial_temp(p_transaction_id);
531
532 -- Bug 3229571. Synch the qa_rcv_lot_ser_temp with the lot and serial
533 -- numbers already used in Inspection.
534
535 modify_lot_serial_temp(p_transaction_id,
536 p_collection_id);
537
538 -- Fetch the total quantity inspected, in the results record.
539 OPEN lot_insp_cur;
540 FETCH lot_insp_cur INTO l_lot_qty_insp;
541 CLOSE lot_insp_cur;
542
543 -- Get the total lot quantity, not inspected. This needs to be inserted
544 -- onto qa_insp_collections_dtl_temp. Call the post_lot_qty_not_insp()
545 -- for this.
546
547 l_lot_qty_not_insp := p_lot_size - l_lot_qty_insp;
548
549 -- Bug 3258383. Post the lot quantity not inpsected, only if its
550 -- greater than Zero. Added the IF condition below to attain the same.
551 -- kabalakr Thu Mar 4 03:36:22 PST 2004.
552
553 IF (l_lot_qty_not_insp > 0) THEN
554
555 post_lot_qty_not_insp(p_collection_id,
556 l_lot_qty_not_insp,
557 p_lot_result);
558
559 END IF;
560
561
562 -- If the lot_result is 'REJECT, then update the insp_result of
563 -- qa_insp_collections_dtl_temp with 'REJECT' for the collection_id.
564
565 IF p_lot_result = 'REJECT' THEN
566 upd_insp_coll_dtl_result(p_collection_id);
567
568 END IF;
569
570
571 -- Now, fetch the records in qa_insp_collections_dtl_temp for calling the
572 -- RCV API. We have grouped the records in cursor so that it gives the
573 -- consolidated picture.
574
575 OPEN insp_coll_dtl;
576 LOOP
577
578 FETCH insp_coll_dtl INTO l_org_id,
579 l_item_id,
580 l_lpn_id,
581 l_xfr_lpn_id,
582 l_lot_number,
583 l_serial_number,
584 l_insp_result,
585 l_insp_qty;
586
587 EXIT WHEN insp_coll_dtl%NOTFOUND;
588
589 IF l_lpn_id IS NOT NULL THEN
590
591 IF l_xfr_lpn_id IS NULL THEN
592 l_xfr_lpn_id := l_lpn_id;
593 -- Bug 6781108
594 -- Calling this Procedure to get subinv_code and loc_id
595 -- in order to insert into RTI table
596 -- pdube Wed Feb 6 04:53:32 PST 2008
597 QLTDACTB.DEFAULT_LPN_SUB_LOC_INFO(L_LPN_ID,
598 L_XFR_LPN_ID,
599 l_rti_sub_code,
600 l_rti_loc_id);
601
602 END IF;
603 END IF;
604
605 -- First, call the RCV API for the Inspection.
606
607 -- Bug 6781108
608 -- Passing two variables to four parameters p_sub, p_loc_id,
609 -- p_from_subinv and p_from_loc_id as new API
610 -- for receiving needed these parameters
611 -- pdube Wed Feb 6 23:22:10 PST 2008
612 RCV_INSPECTION_GRP.INSERT_INSPECTION
613 (p_api_version => 1.1,
614 p_init_msg_list => NULL,
615 p_commit => 'F',
616 p_validation_level => NULL,
617 p_created_by => p_created_by,
618 p_last_updated_by => p_last_updated_by,
619 p_last_update_login => p_last_update_login,
620 p_employee_id => p_employee_id,
621 p_group_id => p_po_group_id,
622 p_transaction_id => p_transaction_id,
623 p_transaction_type => l_insp_result,
624 p_processing_mode => p_po_txn_processor_mode,
625 p_quantity => l_insp_qty,
626 p_uom => p_uom,
627 p_quality_code => null,
628 p_transaction_date => p_transaction_date,
629 p_comments => null,
630 p_reason_id => null,
631 p_vendor_lot => null,
632 p_lpn_id => l_lpn_id,
633 p_transfer_lpn_id => l_xfr_lpn_id,
634 p_qa_collection_id => p_collection_id,
635 p_return_status => x_return_status,
636 p_msg_count => x_msg_count,
637 p_msg_data => x_msg_data,
638 p_subinventory => L_RTI_SUB_CODE,
639 p_locator_id => L_RTI_LOC_ID,
640 p_from_subinventory => L_RTI_SUB_CODE,
641 p_from_locator_id => L_RTI_LOC_ID);
642
643 -- Bug 6781108
644 -- Commenting the following fix for 3225280 and 3270283
645 -- as already handled above through the INSERT_INSPECTION API
646 -- pdube Wed Feb 6 04:53:32 PST 2008
647 /*-- Bug 3225280. Moved the Lot and serial insertion code after RCV
648 -- insert_inspection API because, we want the interface_transaction_id
649 -- of the ACCEPT and REJECT transactions to be passed to the WMS APIs
650 -- as product_transaction_id.
651 --
652 -- For this, first we need to find the interface_transaction_id of the
653 -- inspection record inserted by RCV API. The logic here is to fetch the
654 -- max(interface_transaction_id) from rti for the parent_transaction_id
655 -- and group_id combination. Since we are implementing this just after
656 -- RCV API call, it will fetch the interface_transaction_id of the
657 -- inspection record just inserted.
658 -- kabalakr. Wed Oct 29 23:19:22 PST 2003.
659 --
660
661 OPEN int_txn(p_po_group_id, p_transaction_id);
662 FETCH int_txn INTO l_rti_int_txn_id;
663 CLOSE int_txn;
664
665 -- Bug 3270283. For LPN inspections, we need to default the receiving
666 -- subinventory and Locator for the transfer LPN, if its a newly
667 -- created one OR, it has a LPN context 'Defined but not used'.
668 -- The new procedure DEFAULT_LPN_SUB_LOC_INFO() takes care of this
669 -- defaulting logic entirely. Hence just call this procedure if its
670 -- a LPN inspection. kabalakr Mon Mar 8 08:01:35 PST 2004.
671
672 IF l_lpn_id IS NOT NULL THEN
673
674 QLTDACTB.DEFAULT_LPN_SUB_LOC_INFO(l_lpn_id,
675 l_xfr_lpn_id,
676 l_rti_int_txn_id);
677
678 END IF; -- If l_lpn_id is not null*/
679 -- End bug 6781108
680
681
682 -- Bug 3229571. If the Lot_number or Serial_number is 'DEFERRED', do not
683 -- process now. It means we are posting inspection txn for the qty left
684 -- in the lot which is not inspected. We need to derive the lot and serials
685 -- that needs to be attached to this txn.
686
687 IF ((l_lot_number IS NOT NULL) AND (l_lot_number <> 'DEFERRED')) THEN
688
689 OPEN item_uom_cur(l_item_id, l_org_id);
690 FETCH item_uom_cur INTO l_primary_uom;
691 CLOSE item_uom_cur;
692
693 IF (l_primary_uom = p_uom) THEN
694 l_primary_qty := l_insp_qty;
695
696 ELSE
697 l_primary_qty := inv_convert.inv_um_convert
698 (l_item_id,
699 NULL,
700 l_insp_qty,
701 p_uom,
702 l_primary_uom,
703 NULL,
704 NULL);
705
706 END IF;
707
708 l_int_txn_id := NULL;
709
710 -- Now, call the Inventory/WMS API for Lot Insertion.
711 -- Passing NULL value to p_transaction_interface_id to allow the
712 -- API to generate one. Bug 3096256.
713
714 -- Bug 3225280. Changed the value passed as p_product_transaction_id
715 -- to l_rti_int_txn_id, derived above.
716
717 INV_RCV_INTEGRATION_APIS.INSERT_MTLI
718 (p_api_version => 1.0,
719 p_init_msg_lst => NULL,
720 x_return_status => x_return_status,
721 x_msg_count => x_msg_count,
722 x_msg_data => x_msg_data,
723 p_transaction_interface_id => l_int_txn_id,
724 p_transaction_quantity => l_insp_qty,
725 p_primary_quantity => l_primary_qty,
726 p_organization_id => l_org_id,
727 p_inventory_item_id => l_item_id,
728 p_lot_number => l_lot_number,
729 p_expiration_date => NULL,
730 p_status_id => NULL,
731 x_serial_transaction_temp_id => l_ser_txn_id,
732 p_product_code => 'RCV',
733 p_product_transaction_id => l_rti_int_txn_id);
734
735 if x_return_status <> 'S' then
736 qa_skiplot_utility.insert_error_log (
737 p_module_name => 'QA_SAMPLING_PKG.LAUNCH_SHIPMENT_ACTION_INT',
738 p_error_message => 'QA_WMS_LOT_INSERT_FAIL',
739 p_comments => x_msg_data);
740 fnd_message.clear;
741 fnd_message.set_name ('QA', 'QA_WMS_LOT_INSERT_FAIL');
742 APP_EXCEPTION.RAISE_EXCEPTION;
743 end if;
744
745
746 END IF;
747
748
749 IF ((l_serial_number IS NOT NULL) AND (l_serial_number <> 'DEFERRED')) THEN
750
751 IF ((l_lot_number IS NOT NULL) AND (l_lot_number <> 'DEFERRED')) THEN
752 l_int_txn_id := l_ser_txn_id;
753
754 ELSE
755 l_int_txn_id := NULL;
756
757 END IF;
758
759 -- Now, call the Inventory/WMS API for Serial Insertion.
760 -- Passing NULL value to p_transaction_interface_id to allow the
761 -- API to generate one. Bug 3096256.
762
763 -- Bug 3225280. Changed the value passed as p_product_transaction_id
764 -- to l_rti_int_txn_id, derived above.
765
766 INV_RCV_INTEGRATION_APIS.INSERT_MSNI
767 (p_api_version => 1.0,
768 p_init_msg_lst => NULL,
769 x_return_status => x_return_status,
770 x_msg_count => x_msg_count,
771 x_msg_data => x_msg_data,
772 p_transaction_interface_id => l_int_txn_id,
773 p_fm_serial_number => l_serial_number,
774 p_to_serial_number => l_serial_number,
775 p_organization_id => l_org_id,
776 p_inventory_item_id => l_item_id,
777 p_status_id => NULL,
778 p_product_code => 'RCV',
779 p_product_transaction_id => l_rti_int_txn_id);
780
781 if x_return_status <> 'S' then
782 qa_skiplot_utility.insert_error_log (
783 p_module_name => 'QA_SAMPLING_PKG.LAUNCH_SHIPMENT_ACTION_INT',
784 p_error_message => 'QA_WMS_SER_INSERT_FAIL',
785 p_comments => x_msg_data);
786 fnd_message.clear;
787 fnd_message.set_name ('QA', 'QA_WMS_SER_INSERT_FAIL');
788 APP_EXCEPTION.RAISE_EXCEPTION;
789 end if;
790
791 END IF;
792
793 -- Bug 3229571. Call the procedure insert_lot_serial_txn for posting the lot
794 -- and serial info for the qty not inspected. We have already assigned a value
795 -- of 'DEFERRED' to identify this transaction.
796
797 IF ((l_lot_number = 'DEFERRED') AND (l_serial_number = 'DEFERRED')) THEN
798
799 insert_lot_serial_txn(p_transaction_id,
800 l_rti_int_txn_id,
801 l_item_id,
802 l_org_id,
803 p_uom);
804
805 END IF;
806
807
808 END LOOP;
809
810 END launch_shipment_action_int;
811
812
813
814 -- following procedure is written to avoid code duplication.
815 -- qa_insp_plans_temp is being updated 3 times and this procedure
816 -- consolidates the code.
817 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
818 procedure update_qa_insp_plans_temp(p_sample_size number,
819 p_c_num number,
820 p_rej_num number,
821 p_aql number,
822 p_coll_id number,
823 p_plan_id number)
824 is
825
826 begin
827 update qa_insp_plans_temp
828 set sample_size = p_sample_size,
829 c_number = p_c_num,
830 rejection_number = p_rej_num,
831 aql = p_aql
832 where collection_id = p_coll_id
833 and plan_id = p_plan_id;
834
835 end ; -- update_qa_insp_plans_temp
836
837 --
838 -- local function
839 --
840 function get_softcoded_column(
841 p_plan_id in number) return varchar2
842 is
843
844 cursor res_cur is
845 select result_column_name
846 from qa_plan_chars
847 where plan_id = p_plan_id
848 and char_id = 8; --8 is Inspection Result
849
850 res_col varchar2(20);
851
852 begin
853 --this function returns the softcoded column in qa_results
854 --where Inspection Result is stored for the given plan_id
855 open res_cur;
856
857 fetch res_cur into res_col;
858
859 close res_cur;
860
861 return res_col;
862
863 end;--end function
864
865 --
866 -- local function
867 --
868 function get_rcv_criteria_str(
869 p_criteria_id in number,
870 p_wf_role_name out NOCOPY varchar2) return varchar2 is
871
872 cursor rcv_criteria (x_criteria_id number) is
873 select vendor_name,
874 vendor_site_code,
875 item,
876 item_revision,
877 category_desc,
878 project_number,
879 task_number,
880 wf_role_name
881 from qa_sampling_rcv_criteria_v
882 where criteria_id = x_criteria_id;
883
884 cursor char_names is
885 select name
886 from qa_chars
887 where char_id in (10, 11, 13, 26, 121, 122, 130)
888 order by char_id;
889
890
891 x_supplier qa_chars.name%type;
892 x_supplier_site qa_chars.name%type;
893 x_item qa_chars.name%type;
894 x_rev qa_chars.name%type;
895 x_cat qa_chars.name%type;
896 x_project qa_chars.name%type;
897 x_task qa_chars.name%type;
898
899 x_criteria_str varchar2(2000) := '';
900 x_vendor_name varchar2(240);
901 x_vendor_site_code varchar2(100);
902 x_item_name varchar2(40);
903 x_item_rev varchar2(30);
904 x_item_cat varchar2(500);
905
906 x_project_number varchar2(100);
907 x_task_number varchar2(25);
908
909 begin
910 --this function is used while calling reduced sampling workflow
911 --concatenate the context criteria values as a string
912 --this string passed to OB's workflow function
913 open rcv_criteria (p_criteria_id);
914 fetch rcv_criteria into
915 x_vendor_name,
916 x_vendor_site_code,
917 x_item_name,
918 x_item_rev,
919 x_item_cat,
920 x_project_number,
921 x_task_number,
922 p_wf_role_name;
923
924 close rcv_criteria;
925
926 if p_wf_role_name is null then
927 return null;
928 end if;
929
930 -- While construction x_criteria_str, it is imperative that no
931 -- hardcoding is used. Strings like 'Supplier' need to be removed and
932 -- replaced by variables. This is mandatory requirement to keep the
933 -- text translatable to other languages.
934 -- This can be achieved by using the cursor as follows
935 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
936 open char_names;
937 fetch char_names into x_item; -- char_id 10
938 fetch char_names into x_cat; -- char_id 11
939 fetch char_names into x_rev; -- char_id 13
940 fetch char_names into x_supplier; -- char_id 26
941 fetch char_names into x_project; -- char_id 121
942 fetch char_names into x_task; -- char_id 122
943 fetch char_names into x_supplier_site; -- char_id 130
944 close char_names;
945 if x_vendor_name is not null then
946 x_criteria_str := x_criteria_str ||
947 x_supplier || ' = ' || x_vendor_name || '; ';
948 end if;
949 if x_vendor_site_code is not null then
950 x_criteria_str := x_criteria_str ||
951 x_supplier_site || ' = ' ||x_vendor_site_code || '; ';
952 end if;
953 if x_item_name is not null then
954 x_criteria_str := x_criteria_str ||
955 x_item || ' = ' || x_item_name || '; ';
956 end if;
957 if x_item_rev is not null then
958 x_criteria_str := x_criteria_str ||
959 x_rev || ' = ' || x_item_rev || '; ';
960 end if;
961 if x_item_cat is not null then
962 x_criteria_str := x_criteria_str ||
963 x_cat || ' = ' || x_item_cat || '; ';
964 end if;
965 if x_project_number is not null then
966 x_criteria_str := x_criteria_str ||
967 x_project || ' = ' || x_project_number || '; ';
968 end if;
969 if x_task_number is not null then
970 x_criteria_str := x_criteria_str ||
971 x_task || ' = ' || x_task_number || '; ';
972 end if;
973
974 return x_criteria_str;
975 end; --end function
976
977
978 --public procedures/functions below
979
980 --the purpose of this function is to evaluate to see if there is
981 --applicable sampling plan for each collection plan and context values
982 --if sampling plan is not found, put -1 for sampling plan id
983 --for multiple collection plans case, if one collection plan has sampling,
984 --then the whole record uses sampling, and the sampling flag set to Y
985 --
986
987 --
988 -- removed parameter default values. default values should only be
989 -- put in spec per new coding standard
990 -- jezheng
991 -- Wed Nov 27 15:13:11 PST 2002
992 --
993 procedure eval_rcv_sampling_plan (
994 p_collection_id IN NUMBER,
995 p_organization_id IN number,
996 p_lot_size in number,
997 p_item_id in number ,
998 p_item_category_id in number ,
999 p_item_revision in varchar2 ,
1000 p_vendor_id in number ,
1001 p_vendor_site_id in number ,
1002 p_project_id in number ,
1003 p_task_id in number ,
1004 p_sampling_flag out NOCOPY varchar2
1005 )
1006 IS
1007 l_sampling_plan_id number := -1;
1008 l_criteria_id number := -1;
1009 l_sampling_flag varchar2(1) := 'N';
1010 out_sample_size number := p_lot_size;
1011
1012 cursor plan_cur
1013 is
1014 select plan_id
1015 from qa_insp_plans_temp qipt
1016 where collection_id = p_collection_id;
1017
1018 --
1019 -- removed the rownum = 1 statement from this cursor
1020 -- since rownum cound is done before ordering which
1021 -- will give wrong criteria
1022 -- jezheng
1023 -- Thu Oct 18 18:18:29 PDT 2001
1024 --
1025 cursor criteria_cur (
1026 x_organization_id IN number,
1027 x_item_id in number,
1028 x_item_category_id in number,
1029 x_item_revision in varchar2,
1030 x_vendor_id in number,
1031 x_vendor_site_id in number,
1032 x_project_id in number,
1033 x_task_id in number,
1034 x_collection_plan_id in number)
1035 is
1036 select
1037 sampling_plan_id, criteria_id
1038 from
1039 qa_sampling_rcv_criteria_val_v qsrc
1040 where
1041 qsrc.vendor_id in (-1, x_vendor_id) AND
1042 qsrc.vendor_site_id in (-1, x_vendor_site_id) AND
1043 qsrc.item_id in (-1, x_item_id)AND
1044 qsrc.item_revision in ('-1', x_item_revision) AND
1045 qsrc.item_category_id in (-1, x_item_category_id) AND
1046 qsrc.project_id in (-1, x_project_id) AND
1047 qsrc.task_id in (-1, x_task_id) AND
1048 qsrc.collection_plan_id in (-1, x_collection_plan_id) AND
1049 qsrc.organization_id = x_organization_id AND
1050 trunc(sysdate) BETWEEN
1051 nvl(trunc(qsrc.effective_from), trunc(sysdate)) AND
1052 nvl(trunc(qsrc.effective_to), trunc(sysdate))
1053 ORDER BY
1054 task_id desc, project_id desc ,
1055 vendor_site_id desc, vendor_id desc, item_revision desc,
1056 item_id desc, item_category_id desc, collection_plan_id desc,
1057 last_update_date desc;
1058
1059
1060 BEGIN
1061
1062 p_sampling_flag := 'N'; --initialize this to N
1063 for plan_rec in plan_cur
1064 loop
1065 open criteria_cur(
1066 x_organization_id =>p_organization_id,
1067 x_item_id => nvl(p_item_id, -1),
1068 x_item_category_id => nvl(p_item_category_id, -1),
1069 x_item_revision => nvl(p_item_revision, '-1'),
1070 x_vendor_id => nvl(p_vendor_id, -1),
1071 x_vendor_site_id => nvl(p_vendor_site_id, -1),
1072 x_project_id => nvl(p_project_id, -1),
1073 x_task_id => nvl(p_task_id, -1),
1074 x_collection_plan_id => plan_rec.plan_id);
1075 fetch criteria_cur into l_sampling_plan_id, l_criteria_id;
1076 close criteria_cur;
1077
1078 if (l_sampling_plan_id <> -1) --means sampling plan present
1079 then
1080 update qa_insp_plans_temp
1081 set sampling_plan_id = l_sampling_plan_id,
1082 sampling_criteria_id = l_criteria_id
1083 where collection_id = p_collection_id
1084 and plan_id = plan_rec.plan_id;
1085
1086 l_sampling_flag := 'Y'; --even if one sampling found
1087 --also update the temp table flag
1088 update qa_insp_collections_temp
1089 set sampling_flag = 'Y'
1090 where collection_id = p_collection_id;
1091
1092 p_sampling_flag := 'Y'; --set out parameter
1093 else
1094 update qa_insp_plans_temp
1095 set sampling_plan_id = -1,
1096 sampling_criteria_id = -1
1097 where collection_id = p_collection_id
1098 and plan_id = plan_rec.plan_id;
1099 --should NOT reset l_sampling_flag here
1100 end if;
1101
1102 set_sample_size(l_sampling_plan_id,
1103 p_collection_id,
1104 plan_rec.plan_id,
1105 p_lot_size,
1106 out_sample_size);
1107
1108 -- need to reset l_sampling_plan_id to -1
1109 l_sampling_plan_id := -1;
1110
1111 end loop;
1112
1113 END; --end procedure
1114
1115 --
1116 --This checks the QA_SAMPLING_CUSTOM_RULES table based on sampling plan id
1117 --and lot size and return the custom sample size if found. Otherwise, it
1118 --checks QA_SAMPLING_PLANS table and gets the standard code, c number
1119 --and AQL and checks the QA_SAMPLING_STD_RULES table and return the std.
1120 --sample size
1121 --Note: Some alterations to the table columns were being made
1122 --Please refer to detail design for more information
1123 --
1124 procedure set_sample_size(
1125 p_sampling_plan_id in number,
1126 p_collection_id in number,
1127 p_collection_plan_id in number,
1128 p_lot_size in number,
1129 p_sample_size out NOCOPY number)
1130 is
1131 l_sample_size number := p_lot_size;
1132 l_c_num number := 0;
1133 l_rej_num number := 1;
1134 l_aql number := null;
1135 l_std_code number := null;
1136 l_insp_level varchar2(5) := null;
1137 l_lot_size_code varchar2(1) := null;
1138
1139 cursor sampling_plan_cur
1140 is
1141 select sampling_std_code, AQL, insp_level_code
1142 from QA_SAMPLING_PLANS qsp
1143 where sampling_plan_id = p_sampling_plan_id;
1144
1145 cursor custom_sample_cur
1146 is
1147 select sample_size
1148 from QA_SAMPLING_CUSTOM_RULES qscr
1149 where qscr.sampling_plan_id = p_sampling_plan_id
1150 and p_lot_size between qscr.min_lot_size
1151 and nvl(qscr.max_lot_size,p_lot_size);
1152 --max lot size could be null. this is the reason for nvl
1153
1154 cursor std_sample_cur(x_std_code in number,
1155 x_aql in number,
1156 x_table_seq in number,
1157 x_lot_code in varchar2)
1158 is
1159 select sample_size, c_number, rejection_number
1160 from qa_sampling_std_rules qssr
1161 where qssr.sampling_std_code = x_std_code
1162 and qssr.aql = x_aql
1163 and qssr.table_seq = x_table_seq
1164 and qssr.lot_size_code = x_lot_code;
1165
1166 cursor lot_code_cur(x_insp_level in varchar2)
1167 is
1168 select lot_size_code
1169 from qa_sampling_insp_level qsil
1170 where qsil.insp_level_code = x_insp_level
1171 and p_lot_size between qsil.min_lot_size
1172 and nvl(qsil.max_lot_size, p_lot_size);
1173 begin
1174 if (p_sampling_plan_id = -1 OR p_sampling_plan_id is null)
1175 then
1176 l_sample_size := p_lot_size;
1177 l_c_num := 0;
1178 l_rej_num := 1;
1179 l_aql := null;
1180
1181 -- replace update stmt by calling proc update_qa_insp_plans_temp
1182 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
1183 update_qa_insp_plans_temp(l_sample_size, l_c_num, l_rej_num,
1184 l_aql, p_collection_id,
1185 p_collection_plan_id);
1186
1187 /*
1188 update qa_insp_plans_temp
1189 set sample_size = l_sample_size,
1190 c_number = l_c_num,
1191 rejection_number = l_rej_num,
1192 aql = l_aql
1193 where collection_id = p_collection_id
1194 and plan_id = p_collection_plan_id;
1195
1196 */
1197 p_sample_size := l_sample_size;--set out parameter
1198 else --p_sampling_plan_id is not -1, we can open cursor
1199 open sampling_plan_cur;
1200 fetch sampling_plan_cur into l_std_code, l_aql, l_insp_level;
1201 close sampling_plan_cur;
1202
1203 if (l_std_code = qa_sampling_pkg.custom_sampling_plan)
1204 then
1205 open custom_sample_cur;
1206 fetch custom_sample_cur into l_sample_size;
1207 close custom_sample_cur;
1208 l_c_num := 0;
1209 l_rej_num := 0;
1210 --
1211 -- bug 6122194
1212 -- If sample size is larger than lot size, use lot size
1213 -- bhsankar Tue Jul 3 03:40:41 PDT 2007
1214 --
1215 l_sample_size := least(l_sample_size, p_lot_size);
1216
1217 -- replace update stmt by calling proc update_qa_insp_plans_temp
1218 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
1219 update_qa_insp_plans_temp(l_sample_size, l_c_num,
1220 l_rej_num, null, p_collection_id,
1221 p_collection_plan_id);
1222 /*
1223 update qa_insp_plans_temp
1224 set sample_size = l_sample_size,
1225 c_number = l_c_num,
1226 rejection_number = l_rej_num,
1227 aql = null -- no aql for custom sampling
1228 where collection_id = p_collection_id
1229 and plan_id = p_collection_plan_id;
1230 */
1231
1232 p_sample_size := l_sample_size;--set out param
1233 else --use standard sampling plan
1234 open lot_code_cur(l_insp_level);
1235 fetch lot_code_cur into l_lot_size_code;
1236 close lot_code_cur;
1237
1238 open std_sample_cur(l_std_code, l_aql, 1,
1239 l_lot_size_code);
1240 fetch std_sample_cur
1241 into l_sample_size,
1242 l_c_num,
1243 l_rej_num;
1244 close std_sample_cur;
1245
1246 --
1247 -- if sample size is larger than lot size, use lot size
1248 -- jezheng
1249 -- reference bug 2331892
1250 --
1251 l_sample_size := least(l_sample_size, p_lot_size);
1252
1253 -- replace update stmt by calling proc update_qa_insp_plans_temp
1254 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
1255 update_qa_insp_plans_temp(l_sample_size, l_c_num,
1256 l_rej_num, l_aql, p_collection_id,
1257 p_collection_plan_id);
1258 /*
1259 update qa_insp_plans_temp
1260 set sample_size = l_sample_size,
1261 c_number = l_c_num,
1262 rejection_number = l_rej_num,
1263 aql = l_aql
1264 where collection_id = p_collection_id
1265 and plan_id = p_collection_plan_id;
1266 */
1267
1268 p_sample_size := l_sample_size; --set out param
1269 end if; -- end inner if
1270
1271 end if;
1272
1273 end; -- end procedure
1274
1275 --
1276 --This procedure calculates the collection plan result based on
1277 --the sample inspection result and the sampling plan setup
1278 --If reduced inspection, then workflow is launched to notify
1279 --about this reduced inspection event
1280 -- launch_workflow is a wrapper to call OB's api
1281 --
1282 --
1283 -- Bug 6129041
1284 -- Added an IN parameter p_item_id which defaults to null.
1285 -- The item id is required to correctly calculate the rejected quantity
1286 -- in case of LPN Inspection.
1287 -- skolluku Wed Jul 11 03:37:20 PDT 2007
1288 --
1289 procedure get_plan_result(
1290 p_collection_id in number,
1291 p_coll_plan_id in number,
1292 out_plan_insp_result out NOCOPY varchar2,
1293 p_item_id in number default null)
1294 is
1295 l_sampling_flag varchar2(1) := 'N';
1296 in_str varchar2(3000);
1297
1298 -- anagarwa Fri Mar 29 11:56:16 PST 2002
1299 -- result_column needs to be same length as being selected from
1300 -- po_lookup_codes otherwise it was causing an unhandled exception
1301 -- on the click of OK button in RCV TXN form if sampling was involved.
1302
1303 -- result_column varchar2(10);
1304 result_column po_lookup_codes.displayed_field%type;
1305
1306 sql_str varchar2(5000);
1307 result varchar2(20);
1308 reject_qty number;
1309 l_c_num number := 0;
1310 l_rej_num number := 1;
1311 l_criteria_id number;
1312 l_sampling_plan_id number;
1313 l_sampling_std_code qa_sampling_plans.sampling_std_code%type;
1314 out_wf_item_key number;
1315
1316 cursor sampling_flag_cur
1317 is
1318 select sampling_flag
1319 from qa_insp_collections_temp
1320 where collection_id = p_collection_id;
1321
1322 cursor rej_cur
1323 is
1324 select c_number, rejection_number,
1325 sampling_plan_id, sampling_criteria_id
1326 from qa_insp_plans_temp
1327 where collection_id = p_collection_id
1328 and plan_id = p_coll_plan_id;
1329
1330 cursor sampling_std_code_cur (x_sampling_plan_id number)
1331 is
1332 select sampling_std_code
1333 from qa_sampling_plans
1334 where sampling_plan_id = x_sampling_plan_id;
1335
1336 begin
1337 open sampling_flag_cur;
1338 fetch sampling_flag_cur into l_sampling_flag;
1339 close sampling_flag_cur;
1340
1341 if (l_sampling_flag <> 'Y')
1342 then
1343 out_plan_insp_result := null;
1344 return; --terminate procedure and return
1345 end if;
1346 -- proceed beyond this point means sampling_flag is Y
1347 result_column := get_softcoded_column (p_coll_plan_id);
1348
1349 -- select in_str from po_lookupcodes instead of fnd_lookup_values as
1350 -- displayed_field in po_lookup_codes is traslatable string
1351 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
1352
1353 in_str :=
1354 'select displayed_field ' ||
1355 ' from po_lookup_codes ' ||
1356 ' where lookup_type = ''ERT RESULTS ACTION''' ||
1357 ' and lookup_code = ''REJECT''';
1358 /*
1359 in_str :=
1360 'select meaning ' ||
1361 'from fnd_lookup_values lv ' ||
1362 'where view_application_id = 201 and ' ||
1363 -- following line commented out based upon code review feedback
1364 -- anagarwa Thu Oct 25 10:52:48 PDT 2001
1365 -- 'security_group_id = fnd_global.lookup_security_group ' ||
1366 '(lv.lookup_type,lv.view_application_id) and ' ||
1367 'lookup_type = ''ERT RESULTS ACTION'' and lookup_code = ''REJECT''';
1368
1369 */
1370 --
1371 -- check whether there is a rejection for the plan
1372 -- since we store the inspection result meaning which
1373 -- can be in any language, we use in statement to check
1374 -- inspection result in all possible language
1375 --
1376 /*
1377 sql_str := 'select sum(quantity) from qa_results where exists ' ||
1378 '(select ' || result_column || ' from qa_results ' ||
1379 'where collection_id = :1 and plan_id = :2 and '||
1380 result_column || ' in (' || in_str || ' ))';
1381 */
1382
1383 -- anagarwa Wed Jan 9 15:44:08 PST 2002
1384 -- Bug 2170122 was being caused by faulty sql above which needs to
1385 -- be replaced by the one below. Using 'where exists' and not
1386 -- restricting by plan_id and collection_id results in multiple
1387 -- rows being returned which are then summed up as rejected qty.
1388 -- This in turn causes the whole lot to be rejected inspite of high
1389 -- AQL. Following sql computes correct rejected qty by restricting the
1390 -- qa_results row by plan_id and collection_id.
1391
1392 --
1393 -- Bug 6129041
1394 -- Commented the below code and replaced with the code which
1395 -- considers item_id to build the sql_str and then execute using p_item_id
1396 -- The null check is included because this procedure is also called from
1397 -- QLTRES.pld which does not pass item_id. Hence if p_item_id is null, the
1398 -- procedure executes as before.
1399 -- skolluku Wed Jul 11 03:37:20 PDT 2007
1400 --
1401 /*
1402 sql_str := 'select sum(quantity) from qa_results ' ||
1403 'where collection_id = :1 and plan_id = :2 and ' ||
1404 result_column || ' in (' || in_str || ' )';
1405
1406 execute immediate sql_str into reject_qty
1407 using p_collection_id, p_coll_plan_id;
1408 */
1409 sql_str := 'select sum(quantity) from qa_results ' ||
1410 'where collection_id = :1 and plan_id = :2 and ';
1411 if p_item_id is not null then
1412 sql_str := sql_str || ' item_id = :3 and ';
1413 end if;
1414 sql_str := sql_str || result_column || ' in (' || in_str || ' )';
1415
1416 if p_item_id is null then
1417 execute immediate sql_str into reject_qty
1418 using p_collection_id, p_coll_plan_id;
1419 else
1420 execute immediate sql_str into reject_qty
1421 using p_collection_id, p_coll_plan_id, p_item_id;
1422 end if;
1423
1424 reject_qty := nvl(reject_qty, 0);
1425
1426 --set the total qty rejected for this collection plan
1427 update qa_insp_plans_temp
1428 set plan_rejected_qty = reject_qty
1429 where collection_id = p_collection_id
1430 and plan_id = p_coll_plan_id;
1431
1432 open rej_cur;
1433 fetch rej_cur into l_c_num, l_rej_num,
1434 l_sampling_plan_id, l_criteria_id;
1435 close rej_cur;
1436
1437 if reject_qty <= l_c_num
1438 then
1439 result := 'ACCEPT';
1440 elsif reject_qty >= l_rej_num
1441 then
1442 result := 'REJECT';
1443 else
1444 --check for reduced sampling
1445 open sampling_std_code_cur(l_sampling_plan_id);
1446 fetch sampling_std_code_cur into l_sampling_std_code;
1447 close sampling_std_code_cur;
1448
1449 if (l_sampling_std_code = REDUCED_SAMPLING_PLAN)
1450 then
1451 result := 'ACCEPT';
1452 launch_workflow(l_criteria_id, p_coll_plan_id,
1453 out_wf_item_key);
1454 else
1455 result := 'FUZZY';
1456 end if;
1457 end if;
1458
1459 update qa_insp_plans_temp
1460 set plan_insp_result = result
1461 where collection_id = p_collection_id
1462 and plan_id = p_coll_plan_id;
1463
1464 out_plan_insp_result := result;
1465 exception
1466 when no_data_found then
1467 out_plan_insp_result := 'ACCEPT';
1468 return;
1469
1470 null;
1471 end;
1472
1473 --
1474 --This procedure is used to compute the lot inspection result
1475 --based on the results of the collection plans
1476 --
1477 procedure get_lot_result(
1478 p_collection_id in number,
1479 lot_insp_result out NOCOPY varchar2)
1480 is
1481 l_sampling_flag varchar2(1) := null;
1482 result varchar2(20);
1483 l_rejected number := 0;
1484 total_number_rej number := 0;
1485
1486 cursor sampling_flag_cur
1487 is
1488 select sampling_flag
1489 from qa_insp_collections_temp
1490 where collection_id = p_collection_id;
1491
1492 cursor plan_result_cur
1493 is
1494 select count(*) AS rejected_plans
1495 from qa_insp_plans_temp
1496 where collection_id = p_collection_id
1497 and plan_insp_result = 'REJECT';
1498
1499 cursor total_rej_cur
1500 is
1501 select sum(plan_rejected_qty) AS total_rej_qty
1502 from qa_insp_plans_temp
1503 where collection_id = p_collection_id;
1504
1505 begin
1506 open sampling_flag_cur;
1507 fetch sampling_flag_cur into l_sampling_flag;
1508 close sampling_flag_cur;
1509
1510 if (l_sampling_flag = 'Y')
1511 then
1512 open plan_result_cur;
1513 fetch plan_result_cur into l_rejected;
1514 close plan_result_cur;
1515
1516 if (l_rejected > 0)
1517 then
1518 result := 'REJECT';
1519 else
1520 result := 'ACCEPT';
1521 end if;--end inner if
1522
1523 update qa_insp_collections_temp
1524 set lot_result = result
1525 where collection_id = p_collection_id;
1526 end if;
1527 lot_insp_result := result; --set the out variable
1528
1529 --get the total rejection for this lot and update the temptable
1530 open total_rej_cur;
1531 fetch total_rej_cur into total_number_rej;
1532 close total_rej_cur;
1533
1534 update qa_insp_collections_temp
1535 set total_rejected_qty = total_number_rej
1536 where collection_id = p_collection_id;
1537
1538 null;
1539 end;
1540
1541 --
1542 --This procedure is a wrapper to call the RCV API to perform accept or reject
1543 --From the qa_insp_collections_temp table find out the lotsize, lotresult
1544 --based on that call the RCV API to perform the action
1545 --this procedure called from qainspb.pls (the qainspb.pls has a wrapper)
1546 --the qainspb.pls wrapper is called from client side QLTRES.pld
1547 --
1548 procedure launch_shipment_action(
1549 p_po_txn_processor_mode IN VARCHAR2,
1550 p_po_group_id IN NUMBER,
1551 p_collection_id IN NUMBER,
1552 p_employee_id IN NUMBER,
1553 p_transaction_id IN NUMBER,
1554 p_uom IN VARCHAR2,
1555 p_transaction_date IN DATE,
1556 p_created_by IN NUMBER,
1557 p_last_updated_by IN NUMBER,
1558 p_last_update_login IN NUMBER)
1559 IS
1560
1561 l_lot_size number;
1562 -- following 2 variables added to call PO's api twice, once for
1563 -- accepted quantity and once for rejected quantity
1564 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
1565 l_accepted_qty number;
1566 l_rejected_qty number;
1567
1568 l_lot_result varchar2(20);
1569
1570 x_return_status varchar2(5);
1571 x_msg_count number;
1572 x_msg_data varchar2(240);
1573
1574 cursor lot_info_cur
1575 is
1576 select lot_size, lot_result,total_rejected_qty
1577 from qa_insp_collections_temp
1578 where collection_id = p_collection_id;
1579
1580 -- Added the below cursor and variable for RCV/WMS Merge.
1581 -- Bug 3096256. kabalakr Fri Aug 29 09:06:28 PDT 2003.
1582
1583 cursor plan_count_cur
1584 is
1585 select count(*) AS insp_plans
1586 from qa_insp_plans_temp
1587 where collection_id = p_collection_id;
1588
1589 l_plan_count NUMBER;
1590
1591 --BUG 4741324
1592 --Added a new variable to get a count of the LPN IDs
1593 --from qa_results for a particular Collection Id
1594 --ntungare Mon Nov 21 20:44:54 PST 2005
1595 l_license_plate_no_id number;
1596
1597 begin
1598 --we dont need to check the sampling flag here
1599 --becos qa_inspection_pkg calls this only for sampling scenario
1600 open lot_info_cur;
1601 fetch lot_info_cur into l_lot_size, l_lot_result, l_rejected_qty;
1602 close lot_info_cur;
1603
1604 -- Changes fro RCV/WMS Merge. If the Receiving Inspection involves
1605 -- only one Inspection Collection Plan, call the new procedure.
1606 -- This procedure supports unit wise inspection at lpn, lot and
1607 -- Serial levels.
1608 -- Bug 3096256. kabalakr Fri Aug 29 09:06:28 PDT 2003
1609
1610 -- Check the no of plan involved from qa_insp_plans_temp.
1611 open plan_count_cur;
1612 fetch plan_count_cur into l_plan_count;
1613 close plan_count_cur;
1614
1615 --
1616 -- Bug 4732741:
1617 -- The following select stmt is based on qa_results which is similar
1618 -- to the way LPN_ID's are fetched and processed in the procedure
1619 -- launch_shipment_action_int. In that procedure we fetch the
1620 -- lpn_id's from qa_results and post them in to temp table
1621 -- qa_insp_collections_dtl_temp and then process them. So based
1622 -- this cursor to fetch count of lpn_id from qa_results for the particular
1623 -- collection_id and if count is 0 stop calling the procedure
1624 -- launch_shipment_action_int.
1625 -- ntungare Mon Nov 14 21:11:30 PST 2005
1626 --
1627 SELECT Count(lpn_id) INTO l_license_plate_no_id
1628 FROM qa_results
1629 WHERE collection_id = p_collection_id;
1630
1631 -- If the Plan count is 1, call the new procedure and return.
1632 -- IF (l_plan_count = 1) THEN
1633
1634 --
1635 -- Bug 4732741
1636 -- If the Plan count is 1 and only when lpn_id count is non zero, call the
1637 -- new procedure. This new procedure has been originally written to
1638 -- support LPN transactions in desktop. If the extra condition based
1639 -- on lpn_id is not used then for all type of sampling inspection this
1640 -- code is called which results in bug.
1641 -- ntungare Mon Nov 14 21:15:35 PST 2005
1642 --
1643 IF ((l_plan_count = 1) AND (l_license_plate_no_id <>0)) THEN
1644 launch_shipment_action_int
1645 (p_po_txn_processor_mode => p_po_txn_processor_mode,
1646 p_po_group_id => p_po_group_id,
1647 p_collection_id => p_collection_id,
1648 p_employee_id => p_employee_id,
1649 p_transaction_id => p_transaction_id,
1650 p_uom => p_uom,
1651 p_transaction_date => p_transaction_date,
1652 p_created_by => p_created_by,
1653 p_last_updated_by => p_last_updated_by,
1654 p_last_update_login => p_last_update_login,
1655 p_lot_size => l_lot_size,
1656 p_lot_result => l_lot_result);
1657
1658 -- No Need to continue as the Inspections are completed.
1659 -- Return from the procedure.
1660 return;
1661
1662 END IF;
1663
1664
1665 -- calculate accepted qty
1666 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
1667 l_accepted_qty := l_lot_size - l_rejected_qty;
1668
1669 --call po api to launch accept/reject shipment action.
1670 --parameter p_transaction_type takes the result viz.Accept or Reject
1671 --parameter p_quantity is lot_size in the case of sampling
1672
1673 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
1674 -- if lot_result is ACCEPT then call API for accepted qty first and
1675 -- then look for Rejected_qty. If that's >0 then call the same API with
1676 -- rejected qty and result = 'REJECT'
1677
1678 --
1679 -- modified p_commit values from 'T' to 'F' to fix
1680 -- bug 2056343. If p_commit = 'T', PO will commit the
1681 -- work and the skiplot/sampling temp table will be cleared
1682 -- and the sampling flag will not be available any more.
1683 -- This procedure is called in post-forms-commit.
1684 -- The transaction will be committed anyway when the
1685 -- forms is committed. So we should not commit here.
1686 -- jezheng
1687 -- Mon Nov 12 14:12:44 PST 2001
1688 --
1689
1690 -- Modified the API call for RCV/EMS merge.
1691 -- p_api_version changed to 1.1. Also added p_lpn_id and
1692 -- p_transfer_lpn_id. Passed as NULL.
1693 -- kabalakr Thu Aug 28 08:34:59 PDT 2003.
1694
1695 IF l_lot_result = 'ACCEPT' THEN
1696 RCV_INSPECTION_GRP.INSERT_INSPECTION(
1697 p_api_version => 1.1,
1698 p_init_msg_list => NULL,
1699 p_commit => 'F',
1700 p_validation_level => NULL,
1701 p_created_by => p_created_by,
1702 p_last_updated_by => p_last_updated_by,
1703 p_last_update_login => p_last_update_login,
1704 p_employee_id => p_employee_id,
1705 p_group_id => p_po_group_id,
1706 p_transaction_id => p_transaction_id,
1707 p_transaction_type => l_lot_result,
1708 p_processing_mode => p_po_txn_processor_mode,
1709 p_quantity => l_accepted_qty,
1710 p_uom => p_uom,
1711 p_quality_code => null,
1712 p_transaction_date => p_transaction_date,
1713 p_comments => null,
1714 p_reason_id => null,
1715 p_vendor_lot => null,
1716 p_lpn_id => null,
1717 p_transfer_lpn_id => null,
1718 p_qa_collection_id => p_collection_id,
1719 p_return_status => x_return_status,
1720 p_msg_count => x_msg_count,
1721 p_msg_data => x_msg_data);
1722
1723 -- call this api again with rejected qty.
1724
1725 IF (l_rejected_qty >0) THEN
1726 RCV_INSPECTION_GRP.INSERT_INSPECTION(
1727 p_api_version => 1.1,
1728 p_init_msg_list => NULL,
1729 p_commit => 'F',
1730 p_validation_level => NULL,
1731 p_created_by => p_created_by,
1732 p_last_updated_by => p_last_updated_by,
1733 p_last_update_login => p_last_update_login,
1734 p_employee_id => p_employee_id,
1735 p_group_id => p_po_group_id,
1736 p_transaction_id => p_transaction_id,
1737 p_transaction_type => 'REJECT',
1738 p_processing_mode => p_po_txn_processor_mode,
1739 p_quantity => l_rejected_qty,
1740 p_uom => p_uom,
1741 p_quality_code => null,
1742 p_transaction_date => p_transaction_date,
1743 p_comments => null,
1744 p_reason_id => null,
1745 p_vendor_lot => null,
1746 p_lpn_id => null,
1747 p_transfer_lpn_id => null,
1748 p_qa_collection_id => p_collection_id,
1749 p_return_status => x_return_status,
1750 p_msg_count => x_msg_count,
1751 p_msg_data => x_msg_data);
1752 END IF;
1753
1754 -- anagarwa Thu Oct 25 11:08:47 PDT 2001
1755 -- if REJECT then call the API for lot_size
1756 ELSE
1757 RCV_INSPECTION_GRP.INSERT_INSPECTION(
1758 p_api_version => 1.1,
1759 p_init_msg_list => NULL,
1760 p_commit => 'F',
1761 p_validation_level => NULL,
1762 p_created_by => p_created_by,
1763 p_last_updated_by => p_last_updated_by,
1764 p_last_update_login => p_last_update_login,
1765 p_employee_id => p_employee_id,
1766 p_group_id => p_po_group_id,
1767 p_transaction_id => p_transaction_id,
1768 p_transaction_type => l_lot_result,
1769 p_processing_mode => p_po_txn_processor_mode,
1770 p_quantity => l_lot_size,
1771 p_uom => p_uom,
1772 p_quality_code => null,
1773 p_transaction_date => p_transaction_date,
1774 p_comments => null,
1775 p_reason_id => null,
1776 p_vendor_lot => null,
1777 p_lpn_id => null,
1778 p_transfer_lpn_id => null,
1779 p_qa_collection_id => p_collection_id,
1780 p_return_status => x_return_status,
1781 p_msg_count => x_msg_count,
1782 p_msg_data => x_msg_data);
1783
1784 END IF;
1785
1786 null;
1787 end; --end procedure
1788
1789 function is_sampling( p_collection_id in number ) return varchar2
1790 is
1791 l_sampling_flag varchar2(1) := 'N';
1792
1793 cursor sampling_flag_cur
1794 is
1795 select sampling_flag
1796 from qa_insp_collections_temp
1797 where collection_id = p_collection_id;
1798
1799 begin
1800 open sampling_flag_cur;
1801 fetch sampling_flag_cur into l_sampling_flag;
1802 close sampling_flag_cur;
1803
1804 return l_sampling_flag;
1805 null;
1806 end;
1807
1808 procedure launch_workflow(
1809 p_criteria_id IN NUMBER,
1810 p_coll_plan_id IN NUMBER,
1811 p_wf_item_key OUT NOCOPY NUMBER)
1812 is
1813 x_plan_name varchar2(30);
1814 x_criteria_str varchar2(200);
1815 x_wf_role_name varchar2(360);
1816 x_wf_itemkey number;
1817
1818 cursor plan_name_cur is
1819 select name
1820 from qa_plans
1821 where plan_id = p_coll_plan_id;
1822
1823 begin
1824
1825 -- the below call to get_rcv_criteria_str is a local call to
1826 -- function in this same package
1827 -- similar function is available for skiplot package
1828 x_criteria_str := get_rcv_criteria_str(p_criteria_id,x_wf_role_name);
1829 if (x_wf_role_name is null) then
1830 return;
1831 end if;
1832
1833 open plan_name_cur;
1834 fetch plan_name_cur into x_plan_name;
1835 close plan_name_cur;
1836
1837 x_wf_itemkey := qa_inspection_wf.raise_reduced_inspection_event (
1838 p_lot_information => x_criteria_str,
1839 p_inspection_date => SYSDATE,
1840 p_plan_name => x_plan_name,
1841 p_role_name => x_wf_role_name);
1842
1843 null;
1844 end; --end launch_workflow procedure
1845
1846 PROCEDURE parse_list(x_result IN VARCHAR2,
1847 x_array OUT NOCOPY PlanArray) IS
1848
1849 value VARCHAR2(2000) := '';
1850 c VARCHAR2(10);
1851 separator CONSTANT VARCHAR2(1) := ',';
1852 arr_index INTEGER := 1;
1853 p INTEGER := 1;
1854 n INTEGER := length(x_result);
1855
1856 BEGIN
1857 --
1858 -- Loop until a single ',' is found or x_result is exhausted.
1859 --
1860 WHILE p <= n LOOP
1861 c := substr(x_result, p, 1);
1862 p := p + 1;
1863 IF (c = separator) THEN
1864 x_array(arr_index) := value;
1865 arr_index := arr_index + 1;
1866 value := '';
1867 ELSE
1868 value := value || c;
1869 END IF;
1870
1871 END LOOP;
1872 x_array(arr_index) := value;
1873 END parse_list;
1874
1875
1876 --
1877 -- The procedure is typically used in WMS mobile inspection scenario
1878 -- It takes a list of plan IDs as in parameter and get plan inpsection
1879 -- result for each one. Then calculate lot result based on plan
1880 -- result.
1881 --
1882 --
1883 -- Bug 6129041
1884 -- Added 2 IN params p_org_id and p_item which default to null
1885 -- These params will be used to calculate the item_id
1886 -- skolluku Wed Jul 11 03:37:20 PDT 2007
1887 --
1888 procedure calculate_lot_result(p_collection_id IN NUMBER,
1889 p_plan_ids IN VARCHAR2,
1890 x_lot_result OUT NOCOPY VARCHAR2,
1891 x_rej_qty OUT NOCOPY NUMBER,
1892 x_acc_qty OUT NOCOPY NUMBER,
1893 p_org_id IN NUMBER DEFAULT NULL,
1894 p_item IN VARCHAR2 DEFAULT NULL )
1895 IS
1896
1897 l_plan_id_array PlanArray;
1898 l_plan_id NUMBER;
1899 l_lot_size NUMBER;
1900 l_plan_insp_result VARCHAR2(100);
1901 l_lot_insp_result VARCHAR2(100);
1902 --
1903 -- Bug 6129041
1904 -- local variable hold item_id which will be calculated later.
1905 -- skolluku Wed Jul 11 03:37:20 PDT 2007
1906 --
1907 l_item_id NUMBER;
1908
1909 CURSOR lot_rej_cur IS
1910 SELECT total_rejected_qty, lot_size
1911 FROM qa_insp_collections_temp
1912 WHERE collection_id = p_collection_id;
1913
1914 BEGIN
1915
1916 -- p_plan_ids is a comma separated list of plan_id
1917 -- parse p_plan_ids to get child plan ids in an array
1918 IF p_plan_ids IS NOT NULL THEN
1919 parse_list(p_plan_ids, l_plan_id_array);
1920 END IF;
1921
1922 --
1923 -- Bug 6129041
1924 -- Calculate item_id if both P-org_id and p_item have been passed.
1925 -- Else assign null value to l_item_id.
1926 -- skolluku Wed Jul 11 03:37:20 PDT 2007
1927 --
1928 IF p_org_id IS NOT NULL AND p_item IS NOT NULL THEN
1929 l_item_id := qa_flex_util.get_item_id (p_org_id, p_item);
1930 ELSE
1931 l_item_id := null;
1932 END IF;
1933
1934 -- for all plans do following
1935 FOR i IN 1..l_plan_id_array.COUNT LOOP
1936 l_plan_id := l_plan_id_array(i);
1937 -- get plan result.
1938 --
1939 -- Bug 5948234
1940 -- Pass l_item_id to the procedure get_plan_results
1941 -- skolluku Wed May 23 05:10:48 PDT 2007
1942 --
1943 get_plan_result(p_collection_id, l_plan_id, l_plan_insp_result,l_item_id);
1944 END LOOP; -- for loop ends here
1945
1946 -- get lot result
1947 get_lot_result(p_collection_id, l_lot_insp_result);
1948
1949 -- get total rejected qty and lot_size from temp table
1950 OPEN lot_rej_cur;
1951 FETCH lot_rej_cur INTO x_rej_qty, l_lot_size;
1952 CLOSE lot_rej_cur;
1953
1954 IF l_lot_insp_result = 'ACCEPT' THEN
1955 x_acc_qty := l_lot_size - nvl(x_rej_qty,0);
1956 ELSIF l_lot_insp_result = 'REJECT' THEN
1957 x_acc_qty := 0;
1958 x_rej_qty := l_lot_size;
1959 ELSE
1960 x_acc_qty := 0;
1961 x_rej_qty := 0;
1962 END IF;
1963
1964 x_lot_result := l_lot_insp_result;
1965
1966 END calculate_lot_result;
1967
1968
1969 --
1970 -- The procedure is typically used in WMS LPN based inspection scenario.
1971 -- This procedure takes plan ID list as in parameter and call init_collection
1972 -- for each plan. Then calls overloased procedure eval_rcv_sampling_plan to
1973 -- check whether sampling should be used in this scenario. Sampling_flag is
1974 -- returned
1975 --
1976 procedure eval_rcv_sampling_plan (
1977 p_collection_id IN NUMBER,
1978 p_plan_id_list IN VARCHAR2,
1979 p_org_id IN NUMBER,
1980 p_lot_size IN NUMBER,
1981 p_lpn_id IN NUMBER,
1982 p_item IN VARCHAR2,
1983 p_item_id IN NUMBER,
1984 p_item_cat IN VARCHAR2,
1985 p_item_category_id IN NUMBER,
1986 p_item_rev IN VARCHAR2,
1987 p_vendor IN VARCHAR2,
1988 p_vendor_id IN NUMBER,
1989 p_vendor_site IN VARCHAR2,
1990 p_vendor_site_id IN NUMBER,
1991 p_project_id IN NUMBER,
1992 p_task_id IN NUMBER,
1993 x_sampling_flag OUT NOCOPY VARCHAR2
1994 )
1995 IS
1996
1997 l_plan_id_array PlanArray;
1998 l_plan_id NUMBER;
1999 l_project_id NUMBER;
2000 l_task_id NUMBER;
2001 l_vendor_id NUMBER;
2002 l_item_id NUMBER;
2003 l_category_id NUMBER;
2004 l_vendor_site_id NUMBER;
2005 l_category_val VARCHAR2(1000);
2006 l_item VARCHAR2(2000);
2007
2008 BEGIN
2009 -- p_plan_ids is a comma separated list of plan_id
2010 -- parse p_plan_ids to get child plan ids in an array
2011 IF p_plan_id_list IS NOT NULL THEN
2012 parse_list(p_plan_id_list, l_plan_id_array);
2013 END IF;
2014
2015 -- for all plans do following
2016 FOR i IN 1..l_plan_id_array.COUNT LOOP
2017 l_plan_id := l_plan_id_array(i);
2018 qa_inspection_pkg.init_collection(p_collection_id, p_lot_size,
2019 l_plan_id, null);
2020 END LOOP;
2021
2022 l_item_id := p_item_id;
2023 l_item := p_item;
2024 l_vendor_id := p_vendor_id;
2025 l_category_id := p_item_category_id;
2026 l_category_val := p_item_cat;
2027 l_vendor_site_id := p_vendor_site_id;
2028
2029 IF ((p_item_id IS NULL OR p_item_id<1) AND
2030 p_item IS NOT NULL) THEN
2031 l_item_id := qa_flex_util.get_item_id (p_org_id, p_item);
2032 END IF;
2033
2034 IF (p_item IS NULL AND p_item_id IS NOT NULL) THEN
2035 l_item := qa_flex_util.item(p_org_id, l_item_id);
2036 END IF;
2037
2038 IF l_vendor_id IS NULL OR l_vendor_id < 1 THEN
2039 l_vendor_id := qa_plan_element_api.get_supplier_id(p_vendor);
2040 END IF;
2041
2042 -- anagarwa Wed Apr 3 14:34:49 PST 2002
2043 -- MSCA change: LPN is null if the txn_no =1022
2044 IF p_lpn_id IS NULL OR p_lpn_id = -1 THEN
2045 l_project_id := -1;
2046 l_task_id := -1;
2047 ELSE
2048 -- txn is 1021
2049 l_project_id := qa_flex_util.get_project_id_from_lpn(p_org_id,
2050 p_lpn_id);
2051 l_task_id := qa_flex_util.get_task_id_from_lpn(p_org_id , p_lpn_id);
2052 END IF;
2053
2054 IF p_item_category_id IS NULL OR p_item_category_id < 1 THEN
2055
2056 qa_flex_util.get_item_category_val (p_org_id, l_item, l_item_id,
2057 l_category_val, l_category_id);
2058 END IF;
2059
2060 IF p_vendor_site_id IS NULL OR p_vendor_site_id < 1 THEN
2061 l_vendor_site_id := qa_flex_util.get_vendor_site_id(p_vendor_site);
2062 END IF;
2063
2064
2065
2066 eval_rcv_sampling_plan (
2067 p_collection_id => p_collection_id,
2068 p_organization_id => p_org_id,
2069 p_lot_size => p_lot_size,
2070 p_item_id => l_item_id,
2071 p_item_category_id => l_category_id,
2072 p_item_revision => p_item_rev,
2073 p_vendor_id => l_vendor_id,
2074 p_vendor_site_id => l_vendor_site_id,
2075 p_project_id => l_project_id,
2076 p_task_id => l_task_id,
2077 p_sampling_flag => x_sampling_flag);
2078
2079 END eval_rcv_sampling_plan;
2080
2081
2082 --
2083 -- Bug 3096256. Added the following procedures for RCV/WMS Merge.
2084 -- This procedure inserts the detailed Inspection results onto
2085 -- qa_insp_collections_dtl_temp. This enables unit wise inspection
2086 -- with LPN and at Lot/Serial levels.
2087 -- Called from launch_shipment_action_int() of QA_SAMPLING_PKG and
2088 -- QA_SKIPLOT_RES_ENGINE.
2089 -- kabalakr Fri Aug 29 09:06:28 PDT 2003.
2090 --
2091
2092 PROCEDURE post_insp_coll_details(p_collection_id IN NUMBER) IS
2093
2094
2095 TYPE insp_cur IS REF CURSOR;
2096 insp_acc insp_cur;
2097 insp_rej insp_cur;
2098
2099 l_result_column po_lookup_codes.displayed_field%type;
2100
2101 l_occurrence NUMBER;
2102 l_org_id NUMBER;
2103 l_item_id NUMBER;
2104 l_lpn_id NUMBER;
2105 l_xfr_lpn_id NUMBER;
2106
2107 -- OPM Conv R12 Tracking Bug 4345760
2108 -- change variable size for lot num
2109
2110 l_lot_num qa_results.lot_number%TYPE;
2111
2112 l_serial_num VARCHAR2(25);
2113 l_qty NUMBER;
2114
2115 in_str_reject VARCHAR2(240);
2116 in_str_accept VARCHAR2(240);
2117 l_sql_rej VARCHAR2(1000);
2118 l_sql_acc VARCHAR2(1000);
2119 l_plan_id NUMBER;
2120
2121 CURSOR plan_cur IS
2122 select plan_id
2123 from qa_insp_plans_temp
2124 where collection_id = p_collection_id;
2125
2126
2127 BEGIN
2128
2129 OPEN plan_cur;
2130 FETCH plan_cur INTO l_plan_id;
2131 CLOSE plan_cur;
2132
2133 -- Need to fetch the result column of the Inspection Result
2134 -- Collection element.
2135
2136 l_result_column := get_softcoded_column(l_plan_id);
2137
2138 -- Construct the SQL for fetching REJECT and ACCEPT from
2139 -- the results entered in Inspection Plan in qa_results.
2140
2141 in_str_reject :=
2142 ' select displayed_field' ||
2143 ' from po_lookup_codes' ||
2144 ' where lookup_type = ''ERT RESULTS ACTION''' ||
2145 ' and lookup_code = ''REJECT''';
2146
2147
2148 in_str_accept :=
2149 ' select displayed_field' ||
2150 ' from po_lookup_codes ' ||
2151 ' where lookup_type = ''ERT RESULTS ACTION''' ||
2152 ' and lookup_code = ''ACCEPT''';
2153
2154
2155 l_sql_rej := 'select occurrence, organization_id, item_id, lpn_id, xfr_lpn_id,'||
2156 ' lot_number, serial_number, quantity from qa_results' ||
2157 ' where collection_id = :1 and plan_id = :2 and ' ||
2158 l_result_column || ' in (' || in_str_reject || ' )';
2159
2160 l_sql_acc := 'select occurrence, organization_id, item_id, lpn_id, xfr_lpn_id,'||
2161 ' lot_number, serial_number, quantity from qa_results' ||
2162 ' where collection_id = :1 and plan_id = :2 and ' ||
2163 l_result_column || ' in (' || in_str_accept || ' )';
2164
2165
2166 -- Get the required info from the results entered for the plan for 'ACCEPT'
2167 -- and insert the same onto qa_insp_collections_dtl_temp.
2168
2169 OPEN insp_acc FOR l_sql_acc USING p_collection_id, l_plan_id;
2170 LOOP
2171
2172 FETCH insp_acc INTO l_occurrence,
2173 l_org_id,
2174 l_item_id,
2175 l_lpn_id,
2176 l_xfr_lpn_id,
2177 l_lot_num,
2178 l_serial_num,
2179 l_qty;
2180
2181 EXIT WHEN insp_acc%NOTFOUND;
2182
2183 insert into qa_insp_collections_dtl_temp
2184 (collection_id,
2185 occurrence,
2186 organization_id,
2187 item_id,
2188 lpn_id,
2189 xfr_lpn_id,
2190 lot_number,
2191 serial_number,
2192 insp_result,
2193 insp_qty
2194 )
2195 values
2196 (p_collection_id,
2197 l_occurrence,
2198 l_org_id,
2199 l_item_id,
2200 l_lpn_id,
2201 l_xfr_lpn_id,
2202 l_lot_num,
2203 l_serial_num,
2204 'ACCEPT',
2205 l_qty
2206 );
2207
2208 END LOOP;
2209
2210 CLOSE insp_acc;
2211
2212
2213 -- Get the required info from the results entered for the plan for 'REJECT'
2214 -- and insert the same onto qa_insp_collections_dtl_temp.
2215
2216 OPEN insp_rej FOR l_sql_rej USING p_collection_id, l_plan_id;
2217 LOOP
2218
2219 FETCH insp_rej INTO l_occurrence,
2220 l_org_id,
2221 l_item_id,
2222 l_lpn_id,
2223 l_xfr_lpn_id,
2224 l_lot_num,
2225 l_serial_num,
2226 l_qty;
2227
2228 EXIT WHEN insp_rej%NOTFOUND;
2229
2230 insert into qa_insp_collections_dtl_temp
2231 (collection_id,
2232 occurrence,
2233 organization_id,
2234 item_id,
2235 lpn_id,
2236 xfr_lpn_id,
2237 lot_number,
2238 serial_number,
2239 insp_result,
2240 insp_qty
2241 )
2242 values
2243 (p_collection_id,
2244 l_occurrence,
2245 l_org_id,
2246 l_item_id,
2247 l_lpn_id,
2248 l_xfr_lpn_id,
2249 l_lot_num,
2250 l_serial_num,
2251 'REJECT',
2252 l_qty
2253 );
2254
2255 END LOOP;
2256
2257 CLOSE insp_rej;
2258
2259 END post_insp_coll_details;
2260
2261
2262
2263 END; -- End QA_SAMPLING_PKG
2264
[Home] [Help]