[Home] [Help]
PACKAGE BODY: APPS.QA_SKIPLOT_RCV_GRP
Source
1 PACKAGE BODY QA_SKIPLOT_RCV_GRP AS
2 /* $Header: qaslrcvb.pls 120.0.12000000.2 2007/07/05 11:35:26 bhsankar ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):= 'QA_SKIPLOT_RCV_GRP';
5
6 PROCEDURE CHECK_AVAILABILITY
7 (p_api_version IN NUMBER, -- 1.0
8 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
9 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
10 p_validation_level IN VARCHAR2 DEFAULT FND_API.G_VALID_LEVEL_FULL,
11 p_organization_id IN NUMBER,
12 x_qa_availability OUT NOCOPY VARCHAR2, -- return fnd_api.g_true/false
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2) IS
16
17
18 l_api_version CONSTANT NUMBER := 1.0;
19 l_api_name CONSTANT VARCHAR2(30):= 'CHECK_AVAILABILITY';
20 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
21 l_msg_count NUMBER;
22 l_msg_data VARCHAR2(240);
23
24 BEGIN
25
26 --
27 -- standard start of API savepoint
28 --
29 SAVEPOINT check_availability_pub;
30
31 --
32 -- standard call to check for call compatibility.
33 --
34 if not fnd_api.compatible_api_call (
35 l_api_version,
36 p_api_version,
37 l_api_name,
38 g_pkg_name) then
39 raise fnd_api.g_exc_unexpected_error;
40 end if;
41
42 --
43 -- initialize message list if p_init_msg_list is set to TRUE.
44 --
45 if fnd_api.to_boolean(p_init_msg_list) then
46 fnd_msg_pub.initialize;
47 end if;
48
49
50 --
51 -- Initialize API return status to success
52 --
53 x_return_status := fnd_api.g_ret_sts_success;
54
55 x_qa_availability := qa_skiplot_utility.check_skiplot_availability(
56 qa_skiplot_utility.RCV,
57 p_organization_id);
58
59 qa_skiplot_utility.insert_error_log (
60 p_module_name => 'QA_SKIPLOT_RCV_GRP.CHECK_AVAILABILITY',
61 p_error_message => 'no error ',
62 p_comments => 'availability=' || x_qa_availability);
63
64
65
66 EXCEPTION
67
68 WHEN FND_API.G_EXC_ERROR THEN
69 x_return_status := fnd_api.g_ret_sts_error;
70
71 qa_skiplot_utility.insert_error_log (
72 p_module_name => 'QA_SKIPLOT_RCV_GRP.CHECK_AVAILABILITY',
73 p_error_message => 'FND_API.G_EXC_ERROR ',
74 p_comments => SUBSTR (SQLERRM , 1 , 240));
75
76 --
77 -- get message count and data
78 --
79 fnd_msg_pub.count_and_get (
80 p_count => x_msg_count,
81 p_data => x_msg_data);
82
83 x_qa_availability := fnd_api.g_false;
84
85 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
86 x_return_status := fnd_api.g_ret_sts_unexp_error ;
87
88 qa_skiplot_utility.insert_error_log (
89 p_module_name => 'QA_SKIPLOT_RCV_GRP.CHECK_AVAILABILITY',
90 p_error_message => 'FND_API.G_EXC_UNEXPECTED_ERROR',
91 p_comments => SUBSTR (SQLERRM , 1 , 240));
92
93 --
94 -- get message count and data
95 --
96 fnd_msg_pub.count_and_get (
97 p_count => x_msg_count,
98 p_data => x_msg_data);
99
100 x_qa_availability := fnd_api.g_false;
101
102 WHEN OTHERS THEN
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
104
105 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
106 fnd_msg_pub.add_exc_msg (g_pkg_name , l_api_name);
107 end if;
108
109 qa_skiplot_utility.insert_error_log (
110 p_module_name => 'QA_SKIPLOT_RCV_GRP.CHECK_AVAILABILITY',
111 p_error_message => 'OTHERS exception',
112 p_comments => SUBSTR (SQLERRM , 1 , 240));
113
114 --
115 -- get message count and data
116 --
117 fnd_msg_pub.count_and_get (
118 p_count => x_msg_count,
119 p_data => x_msg_data);
120
121 x_qa_availability := fnd_api.g_false;
122
123 END CHECK_AVAILABILITY;
124
125 --
126 -- local function
127 --
128 procedure get_project_task (
129 p_po_distribution_id in number,
130 p_po_line_location_id in number,
131 p_project_id out NOCOPY number,
132 p_task_id out NOCOPY number) is
133
134 --
135 -- select from po_distributions_all so that
136 -- current organization need not to be considered
137 --
138
139 --
140 -- in PO receipt form, when a po line location has
141 -- only one distribution, the po_distribution_id is
142 -- populated, otherwise it is null
143 -- when the po_distribution_id is provided, we use
144 -- it to derive project and task ids, otherwise we
145 -- use po_line_location_id
146 -- one po_line_location_id may contain multiple po
147 -- distributions, thus multiple project/task pair
148 -- skiplot engine only consider the situtaion when
149 -- one line location has only one distribution case
150 -- reference P1 2141280
151 -- jezheng
152 -- Fri Dec 14 11:28:49 PST 2001
153 --
154
155 cursor project_task (x_distribution_id number) is
156 select project_id, task_id
157 from po_distributions_all
158 where po_distribution_id = x_distribution_id;
159
160 cursor project_task2 (x_line_location_id number) is
161 select project_id, task_id
162 from po_distributions_all
163 where line_location_id = x_line_location_id;
164
165 counter number := 0;
166
167 begin
168
169 if p_po_distribution_id is not null then
170 open project_task (p_po_distribution_id);
171 fetch project_task into p_project_id, p_task_id;
172 close project_task;
173
174 elsif p_po_line_location_id is not null then
175 for pt in project_task2 (p_po_line_location_id) loop
176 p_project_id := pt.project_id;
177 p_task_id := pt.task_id;
178 counter := counter + 1;
179 end loop;
180
181
182 --
183 -- one po line location may have multiple po distributions
184 -- each of them may have one project/task pair
185 -- skiplot does not support this case
186 -- reference p1 2141280
187 --
188 if counter = 0 or counter = 1 then
189 p_project_id := null;
190 p_task_id := null;
191 end if;
192 end if;
193
194 end get_project_task;
195
196 PROCEDURE EVALUATE_LOT
197 (p_api_version IN NUMBER, -- 1.0
198 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
199 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
200 p_validation_level IN VARCHAR2 DEFAULT FND_API.G_VALID_LEVEL_FULL,
201 p_interface_txn_id IN NUMBER,
202 p_organization_id IN NUMBER,
203 p_vendor_id IN NUMBER,
204 p_vendor_site_id IN NUMBER,
205 p_item_id IN NUMBER,
206 p_item_revision IN VARCHAR2,
207 p_item_category_id IN NUMBER,
208 p_project_id IN NUMBER,
209 p_task_id IN NUMBER,
210 p_manufacturer_id IN NUMBER,
211 p_source_inspected IN NUMBER,
212 p_receipt_qty IN NUMBER,
213 p_receipt_date IN DATE,
214 p_primary_uom IN varchar2 DEFAULT null,
215 p_transaction_uom IN varchar2 DEFAULT null,
216 p_po_header_id IN NUMBER DEFAULT null,
217 p_po_line_id IN NUMBER DEFAULT null,
218 p_po_line_location_id IN NUMBER DEFAULT null,
219 p_po_distribution_id IN NUMBER DEFAULT null,
220 p_lpn_id IN NUMBER DEFAULT null,
221 p_wms_flag IN VARCHAR2 DEFAULT 'N',
222 x_evaluation_result OUT NOCOPY VARCHAR2, -- returns INSPECTor STANDARD
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2) IS
226
227 PRAGMA AUTONOMOUS_TRANSACTION;
228
229 l_api_version CONSTANT NUMBER := 1.0;
230 l_api_name CONSTANT VARCHAR2(30):= 'EVALUATE_LOT';
231 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
232 l_msg_count NUMBER;
233 l_msg_data VARCHAR2(500);
234
235 SOURCE_INSPECTED CONSTANT NUMBER := 1;
236 applicablePlans qa_skiplot_utility.planList;
237 availablePlans qa_skiplot_utility.planList;
238 lotID NUMBER;
239 criteriaID NUMBER;
240 processID NUMBER;
241 insp_status VARCHAR2(10);
242 project_id number;
243 task_id number;
244
245 BEGIN
246
247 --
248 -- standard start of API savepoint
249 --
250 SAVEPOINT check_availability_pub;
251
252 --
253 -- standard call to check for call compatibility.
254 --
255 if not fnd_api.compatible_api_call (
256 l_api_version,
257 p_api_version,
258 l_api_name,
259 g_pkg_name) then
260 raise fnd_api.g_exc_unexpected_error;
261 end if;
262
263 --
264 -- initialize message list if p_init_msg_list is set to TRUE.
265 --
266 if fnd_api.to_boolean(p_init_msg_list) then
267 fnd_msg_pub.initialize;
268 end if;
269
270
271 --
272 -- Initialize API return status to success
273 --
274 x_return_status := fnd_api.g_ret_sts_success;
275
276
277 --
278 -- if skip lot not available, use normal inspection
279 --
280 if qa_skiplot_utility.check_skiplot_availability
281 (qa_skiplot_utility.RCV, p_organization_id ) = fnd_api.g_false then
282 x_evaluation_result := 'INSPECT';
283
284 --
285 -- if source inspected, skip the lot
286 --
287 elsif p_source_inspected = SOURCE_INSPECTED then
288 --
289 -- lot is already source inspected
290 -- skip it and do not write it into database
291 --
292
293 x_evaluation_result := 'STANDARD';
294
295 --
296 -- evaluate criteria needed
297 --
298 else
299
300 project_id := p_project_id;
301 task_id := p_task_id;
302
303 if project_id is null and task_id is null then
304 get_project_task (
305 p_po_distribution_id,
306 p_po_line_location_id,
307 project_id, -- out parameter
308 task_id); -- out parameter
309 end if;
310
311 --
312 -- get the availablePlans from skip lot setup
313 --
314 qa_skiplot_eval_engine.evaluate_rcv_criteria (
315 p_organization_id => p_organization_id,
316 p_vendor_id => p_vendor_id,
317 p_vendor_site_id => p_vendor_site_id,
318 p_item_id => p_item_id,
319 p_item_revision => p_item_revision,
320 p_item_category_id => p_item_category_id,
321 p_project_id => project_id,
322 p_task_id => task_id,
323 p_manufacturer_id => p_manufacturer_id,
324 p_lot_qty => p_receipt_qty,
325 p_primary_uom => p_primary_uom,
326 p_transaction_uom => p_transaction_uom,
327 p_availablePlans => availablePlans, -- out parameter
328 p_criteria_ID => criteriaID, -- out parameter
329 p_process_id => processID); -- out parameter
330
331 --
332 -- if skip lot is not setup for this lot
333 -- use normal inspection.
334 --
335
336 if availablePlans.count = 0 then
337 x_evaluation_result := 'INSPECT';
338 else
339 --
340 -- evaluate the available plans to get the
341 -- applicable plans
342 --
343 qa_skiplot_eval_engine.evaluate_rules (
344 p_availablePlans => availablePlans,
345 p_criteria_id => criteriaID,
346 p_process_id => processID,
347 p_txn => qa_skiplot_utility.RCV,
348 p_lot_id => lotID, -- out parameter
349 p_applicablePlans => applicablePlans); -- out parameter
350
351 --
352 -- lot is skipped
353 --
354 if applicablePlans.count = 0 then
355 x_evaluation_result := 'STANDARD';
356 insp_status := 'SKIPPED';
357 else
358 x_evaluation_result := 'INSPECT';
359 insp_status := 'PENDING'; -- inspection pending
360
361 --
362 -- store the lot/plan pairs if inspection required.
363 --
364 qa_skiplot_eval_engine.store_lot_plans(
365 p_applicablePlans => applicablePlans,
366 p_lotID => lotID,
367 p_insp_status => insp_status);
368
369 end if;
370
371 --
372 -- insert into skip lot results table
373 -- no matter insepction or skipping
374 -- as long as skip lot inspection is
375 -- applied
376 --
377 qa_skiplot_eval_engine.insert_rcv_results (
378 p_interface_txn_id => p_interface_txn_id,
379 p_manufacturer_id => p_manufacturer_id,
380 p_receipt_qty => p_receipt_qty,
381 p_criteriaID => criteriaID,
382 p_insp_status => insp_status,
383 p_receipt_date => sysdate,
384 p_lotID => lotID,
385 p_source_inspected => p_source_inspected,
386 p_process_id => processID,
387 p_lpn_id => p_lpn_id);
388 end if;
389 end if;
390
391 COMMIT;
392
393 EXCEPTION
394
395 WHEN FND_API.G_EXC_ERROR THEN
396 x_return_status := fnd_api.g_ret_sts_error;
397
398 --
399 -- get message count and data
400 --
401 fnd_msg_pub.count_and_get (
402 p_count => x_msg_count,
403 p_data => x_msg_data);
404
405 qa_skiplot_utility.insert_error_log (
406 p_module_name => 'QA_SKIPLOT_RCV_GRP.EVALUATE_LOT',
407 p_error_message => 'FND_API.G_EXC_ERROR',
408 p_comments => SUBSTR (SQLERRM , 1 , 240));
409
410 x_evaluation_result := 'INSPECT';
411
412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
413 x_return_status := fnd_api.g_ret_sts_unexp_error ;
414
415 --
416 -- get message count and data
417 --
418 fnd_msg_pub.count_and_get (
419 p_count => x_msg_count,
420 p_data => x_msg_data);
421
422 qa_skiplot_utility.insert_error_log (
423 p_module_name => 'QA_SKIPLOT_RCV_GRP.EVALUATE_LOT',
424 p_error_message => 'FND_API.G_EXC_UNEXPECTED_ERROR',
425 p_comments => SUBSTR (SQLERRM , 1 , 240));
426
427 x_evaluation_result := 'INSPECT';
428
429 WHEN OTHERS THEN
430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
431
432 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
433 fnd_msg_pub.add_exc_msg (g_pkg_name , l_api_name);
434 end if;
435
436 qa_skiplot_utility.insert_error_log (
437 p_module_name => 'QA_SKIPLOT_RCV_GRP.EVALUATE_LOT',
438 p_error_message => 'OTHERS exception ',
439 p_comments => SUBSTR (SQLERRM , 1 , 240));
440
441 --
442 -- get message count and data
443 --
444 fnd_msg_pub.count_and_get (
445 p_count => x_msg_count,
446 p_data => x_msg_data);
447
448 x_evaluation_result := 'INSPECT';
449
450
451 END EVALUATE_LOT;
452
453 PROCEDURE MATCH_SHIPMENT
454 (p_api_version IN NUMBER,
455 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
456 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
457 p_validation_level IN VARCHAR2 DEFAULT FND_API.G_VALID_LEVEL_FULL,
458 p_interface_txn_id IN NUMBER,
459 p_shipment_header_id IN NUMBER,
460 p_shipment_line_id IN NUMBER,
461 x_return_status OUT NOCOPY VARCHAR2,
462 x_msg_count OUT NOCOPY NUMBER,
463 x_msg_data OUT NOCOPY VARCHAR2) IS
464
465 l_api_version CONSTANT NUMBER := 1.0;
466 l_api_name CONSTANT VARCHAR2(30):= 'MATCH_SHIPMENT';
467 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
468 l_msg_count NUMBER;
469 l_msg_data VARCHAR2(2400);
470
471 lotID NUMBER := null;
472
473 BEGIN
474
475 --
476 -- standard start of API savepoint
477 --
478 SAVEPOINT check_availability_pub;
479
480 --
481 -- standard call to check for call compatibility.
482 --
483 if not fnd_api.compatible_api_call (
484 l_api_version,
485 p_api_version,
486 l_api_name,
487 g_pkg_name) then
488 raise fnd_api.g_exc_unexpected_error;
489 end if;
490
491 --
492 -- initialize message list if p_init_msg_list is set to TRUE.
493 --
494 if fnd_api.to_boolean(p_init_msg_list) then
495 fnd_msg_pub.initialize;
496 end if;
497
498
499 --
500 -- Initialize API return status to success
501 --
502 x_return_status := fnd_api.g_ret_sts_success;
503
504
505 --
506 -- update lot plans table and results table
507 -- with shipment line id
508 --
509
510 if p_interface_txn_id is not null then
511 update qa_skiplot_rcv_results
512 set shipment_line_id = p_shipment_line_id,
513 valid_flag = 2
514 where interface_txn_id = p_interface_txn_id
515 returning insp_lot_id into lotID;
516 end if;
517
518 if lotID is not null then
519 update qa_skiplot_lot_plans
520 set shipment_line_id = p_shipment_line_id
521 where insp_lot_id = lotID;
522 end if;
523
524 EXCEPTION
525
526 WHEN FND_API.G_EXC_ERROR THEN
527 x_return_status := fnd_api.g_ret_sts_error;
528
529 qa_skiplot_utility.insert_error_log (
530 p_module_name => 'QA_SKIPLOT_RCV_GRP.MATCH_SHIPMENT',
531 p_error_message => 'FND_API.G_EXC_ERROR ',
532 p_comments => SUBSTR (SQLERRM , 1 , 240));
533
534 --
535 -- get message count and data
536 --
537 fnd_msg_pub.count_and_get (
538 p_count => x_msg_count,
539 p_data => x_msg_data);
540
541 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
542 x_return_status := fnd_api.g_ret_sts_unexp_error ;
543
544 qa_skiplot_utility.insert_error_log (
545 p_module_name => 'QA_SKIPLOT_RCV_GRP.MATCH_SHIPMENT',
546 p_error_message => 'FND_API.G_EXC_UNEXPECTED_ERROR ',
547 p_comments => SUBSTR (SQLERRM , 1 , 240));
548
549 --
550 -- get message count and data
551 --
552 fnd_msg_pub.count_and_get (
553 p_count => x_msg_count,
554 p_data => x_msg_data);
555
556 WHEN OTHERS THEN
557 x_return_status := fnd_api.g_ret_sts_unexp_error ;
558
559 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
560 fnd_msg_pub.add_exc_msg (g_pkg_name , l_api_name);
561 end if;
562
563 qa_skiplot_utility.insert_error_log (
564 p_module_name => 'QA_SKIPLOT_RCV_GRP.MATCH_SHIPMENT',
565 p_error_message => 'OTHERS exception ',
566 p_comments => SUBSTR (SQLERRM , 1 , 240));
567
568 --
569 -- get message count and data
570 --
571 fnd_msg_pub.count_and_get (
572 p_count => x_msg_count,
573 p_data => x_msg_data);
574
575 END MATCH_SHIPMENT;
576
577 PROCEDURE IS_QA_RESULT_PRESENT
578 (p_api_version IN NUMBER, -- 1.0
579 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
580 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
581 p_validation_level IN VARCHAR2 DEFAULT FND_API.G_VALID_LEVEL_FULL,
582 p_collection_id IN NUMBER,
583 x_result_present OUT NOCOPY VARCHAR2,
584 x_return_status OUT NOCOPY VARCHAR2,
585 x_msg_count OUT NOCOPY NUMBER,
586 x_msg_data OUT NOCOPY VARCHAR2) IS
587
588 l_api_version CONSTANT NUMBER := 1.0;
589 l_api_name CONSTANT VARCHAR2(30):= 'IS_QA_RESULT_PRESENT';
590 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
591 l_msg_count NUMBER;
592 l_msg_data VARCHAR2(2400);
593
594 --uses qa_pc_results_relationship to ensure we don't count orphan child rows
595 CURSOR C1 IS
596 SELECT count(qr.occurrence)
597 FROM qa_results qr
598 WHERE qr.collection_id = p_collection_id
599 AND qr.occurrence NOT IN (SELECT qprr.child_occurrence
600 FROM qa_pc_results_relationship qprr);
601 l_res_count NUMBER := 0;
602 BEGIN
603
604 --
605 -- savepoint unnecessary since this is a read-only procedure
606 --
607
608 --
609 -- standard call to check for call compatibility.
610 --
611 if not fnd_api.compatible_api_call (
612 l_api_version,
613 p_api_version,
614 l_api_name,
615 g_pkg_name) then
616 raise fnd_api.g_exc_unexpected_error;
617 end if;
618
619 --
620 -- initialize message list if p_init_msg_list is set to TRUE.
621 --
622 if fnd_api.to_boolean(p_init_msg_list) then
623 fnd_msg_pub.initialize;
624 end if;
625
626 --
627 -- Initialize API return status to success and
628 -- data found result to false
629 --
630 x_return_status := fnd_api.g_ret_sts_success;
631 x_result_present := fnd_api.g_false;
632
633 --
634 -- do a lookup on qa_results to check for the collection_id
635 -- in a non-child plan
636 --
637 OPEN C1;
638 FETCH C1 INTO l_res_count;
639 IF l_res_count > 0 THEN
640 x_result_present := fnd_api.g_true;
641 END IF;
642 CLOSE C1;
643
644 EXCEPTION
645
646 WHEN FND_API.G_EXC_ERROR THEN
647 x_return_status := fnd_api.g_ret_sts_error;
648
649 qa_skiplot_utility.insert_error_log (
650 p_module_name => 'QA_SKIPLOT_RCV_GRP.IS_QA_RESULT_PRESENT',
651 p_error_message => 'FND_API.G_EXC_ERROR ',
652 p_comments => SUBSTR (SQLERRM , 1 , 240));
653
654 --
655 -- get message count and data
656 --
657 fnd_msg_pub.count_and_get (
658 p_count => x_msg_count,
659 p_data => x_msg_data);
660
661 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
662 x_return_status := fnd_api.g_ret_sts_unexp_error ;
663
664 qa_skiplot_utility.insert_error_log (
665 p_module_name => 'QA_SKIPLOT_RCV_GRP.IS_QA_RESULT_PRESENT',
666 p_error_message => 'FND_API.G_EXC_UNEXPECTED_ERROR ',
667 p_comments => SUBSTR (SQLERRM , 1 , 240));
668
669 --
670 -- get message count and data
671 --
672 fnd_msg_pub.count_and_get (
673 p_count => x_msg_count,
674 p_data => x_msg_data);
675
676 WHEN OTHERS THEN
677 x_return_status := fnd_api.g_ret_sts_unexp_error ;
678
679 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
680 fnd_msg_pub.add_exc_msg (g_pkg_name , l_api_name);
681 end if;
682
683 qa_skiplot_utility.insert_error_log (
684 p_module_name => 'QA_SKIPLOT_RCV_GRP.IS_QA_RESULT_PRESENT',
685 p_error_message => 'OTHERS exception ',
686 p_comments => SUBSTR (SQLERRM , 1 , 240));
687
688 --
689 -- get message count and data
690 --
691 fnd_msg_pub.count_and_get (
692 p_count => x_msg_count,
693 p_data => x_msg_data);
694
695 END IS_QA_RESULT_PRESENT;
696
697 --
698 -- bug 6064562
699 -- This procedure is used by PO's RCV integration to
700 -- check if a receiving transaction lot was skipped.
701 -- This API is being introduced since, AP Invoicing
702 -- creates an hold for skipped records for PO's created
703 -- with 4 Way Match with Receipt. This happens because
704 -- AP calls Receiving API to get the quantity details
705 -- from rcv_transactions but rcv_transactions does
706 -- not maintain details of Skipped lots.
707 -- returns x_skip_status = {fnd_api.g_true | fnd_api.g_false}
708 -- bhsankar Thu Jul 5 04:09:04 PDT 2007
709 --
710 PROCEDURE IS_LOT_SKIPPED(p_api_version IN NUMBER, -- 1.0
711 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
712 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
713 p_validation_level IN VARCHAR2 DEFAULT FND_API.G_VALID_LEVEL_FULL,
714 p_transaction_id IN NUMBER,
715 x_skip_status OUT NOCOPY VARCHAR2,
716 x_return_status OUT NOCOPY VARCHAR2,
717 x_msg_count OUT NOCOPY NUMBER,
718 x_msg_data OUT NOCOPY VARCHAR2) IS
719
720
721 l_api_version CONSTANT NUMBER := 1.0;
722 l_api_name CONSTANT VARCHAR2(30):= 'IS_LOT_SKIPPED';
723 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
724 l_msg_count NUMBER;
725 l_msg_data VARCHAR2(2400);
726
727 CURSOR C1 IS
728 SELECT count(qa.inspection_status)
729 FROM qa_skiplot_rcv_results qa, rcv_transactions rt
730 WHERE rt.interface_transaction_id = qa.interface_txn_id
731 AND rt.shipment_line_id = qa.shipment_line_id
732 AND qa.inspection_status = 'SKIPPED'
733 AND rt.transaction_id = p_transaction_id;
734
735 l_skip_count number;
736 BEGIN
737
738 --
739 -- savepoint unnecessary since this is a read-only procedure
740 --
741
742 --
743 -- standard call to check for call compatibility.
744 --
745 if not fnd_api.compatible_api_call (
746 l_api_version,
747 p_api_version,
748 l_api_name,
749 g_pkg_name) then
750 raise fnd_api.g_exc_unexpected_error;
751 end if;
752
753 --
754 -- initialize message list if p_init_msg_list is set to TRUE.
755 --
756 if fnd_api.to_boolean(p_init_msg_list) then
757 fnd_msg_pub.initialize;
758 end if;
759
760 --
761 -- Initialize API return status to success and
762 -- data found result to false
763 --
764 x_return_status := fnd_api.g_ret_sts_success;
765 x_skip_status := fnd_api.g_false;
766
767 --
768 -- do a lookup in qa_skiplot_rcv_results and check
769 -- if the lot pertaining to the rcv_transaction_id
770 -- was skipped.
771 --
772 OPEN C1;
773 FETCH C1 INTO l_skip_count;
774 IF l_skip_count > 0 THEN
775 x_skip_status := fnd_api.g_true;
776 END IF;
777 CLOSE C1;
778
779 EXCEPTION
780
781 WHEN FND_API.G_EXC_ERROR THEN
782 x_return_status := fnd_api.g_ret_sts_error;
783
784 qa_skiplot_utility.insert_error_log (
785 p_module_name => 'QA_SKIPLOT_RCV_GRP.IS_LOT_SKIPPED',
786 p_error_message => 'FND_API.G_EXC_ERROR ',
787 p_comments => SUBSTR (SQLERRM , 1 , 240));
788
789 --
790 -- get message count and data
791 --
792 fnd_msg_pub.count_and_get (
793 p_count => x_msg_count,
794 p_data => x_msg_data);
795
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
797 x_return_status := fnd_api.g_ret_sts_unexp_error ;
798
799 qa_skiplot_utility.insert_error_log (
800 p_module_name => 'QA_SKIPLOT_RCV_GRP.IS_LOT_SKIPPED',
801 p_error_message => 'FND_API.G_EXC_UNEXPECTED_ERROR ',
802 p_comments => SUBSTR (SQLERRM , 1 , 240));
803
804 --
805 -- get message count and data
806 --
807 fnd_msg_pub.count_and_get (
808 p_count => x_msg_count,
809 p_data => x_msg_data);
810
811 WHEN OTHERS THEN
812 x_return_status := fnd_api.g_ret_sts_unexp_error ;
813
814 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
815 fnd_msg_pub.add_exc_msg (g_pkg_name , l_api_name);
816 end if;
817
818 qa_skiplot_utility.insert_error_log (
819 p_module_name => 'QA_SKIPLOT_RCV_GRP.IS_LOT_SKIPPED',
820 p_error_message => 'OTHERS exception ',
821 p_comments => SUBSTR (SQLERRM , 1 , 240));
822
823 --
824 -- get message count and data
825 --
826 fnd_msg_pub.count_and_get (
827 p_count => x_msg_count,
828 p_data => x_msg_data);
829
830
831 END IS_LOT_SKIPPED;
832
833 END QA_SKIPLOT_RCV_GRP;