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