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