[Home] [Help]
PACKAGE BODY: APPS.POS_CANCEL_ASN
Source
1 PACKAGE BODY POS_CANCEL_ASN AS
2 /* $Header: POSASNCB.pls 120.4 2006/08/14 20:27:18 pkapoor noship $*/
3
4 /** Local Procedure Definition **/
5
6 PROCEDURE remove_from_interface (
7 p_shipment_num IN VARCHAR2,
8 p_vendor_id IN NUMBER,
9 p_vendor_site_id IN NUMBER,
10 p_result IN OUT NOCOPY NUMBER,
11 p_error_code IN OUT NOCOPY VARCHAR2 );
12
13 PROCEDURE cancel_invoice (
14 p_invoice_id IN NUMBER );
15
16 PROCEDURE cancel_asn_line (
17 p_shipment_line_id IN NUMBER,
18 p_vendor_id IN NUMBER,
19 p_error_code IN OUT NOCOPY VARCHAR2 );
20
21 PROCEDURE cancel_invoice_new (
22 p_invoice_id IN NUMBER,
23 p_set_of_books_id IN NUMBER,
24 p_gl_date IN DATE,
25 P_period_name IN VARCHAR2 );
26
27 PROCEDURE cancel_invoice_old (
28 p_invoice_id IN NUMBER,
29 p_set_of_books_id IN NUMBER,
30 p_gl_date IN DATE,
31 P_period_name IN VARCHAR2 );
32
33
34 --
35 -- Procedure that cancels the specific shipment.
36 --
37 PROCEDURE cancel_asn (
38 p_shipment_num IN VARCHAR2,
39 p_invoice_num IN VARCHAR2,
40 p_processing_status IN VARCHAR2,
41 p_vendor_id IN NUMBER,
42 p_vendor_site_id IN NUMBER,
43 p_result IN OUT NOCOPY NUMBER,
44 p_error_code IN OUT NOCOPY VARCHAR2 )
45 IS
46
47 l_count NUMBER := 0;
48 l_receipt_count NUMBER := 0; -- Number of receipts created
49 l_shipment_line_id NUMBER := NULL;
50 l_invoice_id NUMBER := NULL;
51 l_org_id NUMBER := NULL;
52
53 CURSOR c_asn_line IS
54 select rsl.shipment_line_id
55 from RCV_SHIPMENT_LINES rsl,
56 RCV_SHIPMENT_HEADERS rsh
57 where rsh.shipment_num = p_shipment_num
58 and rsh.shipment_header_id = rsl.shipment_header_id
59 and rsh.vendor_id = p_vendor_id
60 and rsh.vendor_site_id = p_vendor_site_id;
61
62 CURSOR c_invoice IS
63 select invoice_id, org_id
64 from AP_INVOICES_ALL
65 where invoice_num = p_invoice_num
66 and vendor_id = p_vendor_id
67 and vendor_site_id = p_vendor_site_id;
68
69 BEGIN
70
71 IF (p_processing_status = 'PENDING' or p_processing_status = 'ERROR') THEN
72
73 BEGIN
74
75 POS_ASN_NOTIF.generate_notif ( p_shipment_num,
76 'CANCEL',
77 p_vendor_id,
78 p_vendor_site_id,
79 fnd_global.user_id );
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 p_result := 0;
84 p_error_code := 'POS_CANCEL_NOTIF_ERROR';
85 raise;
86 END;
87
88 remove_from_interface( p_shipment_num,
89 p_vendor_id,
90 p_vendor_site_id,
91 p_result,
92 p_error_code );
93 p_result := 1;
94 return;
95 END IF;
96
97
98 -- Check if shipment already exists in Maintain Shipments view
99 select count(*)
100 into l_count
101 from RCV_SHIPMENT_HEADERS
102 where shipment_num = p_shipment_num
103 and vendor_id = p_vendor_id
104 and vendor_site_id = p_vendor_site_id;
105
106 if (l_count = 0) then
107 p_result := 0;
108 p_error_code := 'POS_ASN_NOT_FOUND';
109 return;
110 END IF;
111
112 -- Check if any receipts have been recorded.
113 select count(*)
114 into l_receipt_count
115 from rcv_shipment_headers
116 where shipment_num = p_shipment_num
117 and vendor_id = p_vendor_id
118 and vendor_site_id = p_vendor_site_id
119 and receipt_num is not null;
120
121 IF (l_receipt_count > 0) THEN
122 p_result := 0;
123 p_error_code := 'POS_ASN_RECEIPT_EXIST';
124 return;
125 END IF;
126
127
128 OPEN c_asn_line;
129 LOOP
130 FETCH c_asn_line INTO l_shipment_line_id;
131 EXIT WHEN c_asn_line%NOTFOUND;
132
133 if get_line_status(l_shipment_line_id) = 'OK' then
134 cancel_asn_line(l_shipment_line_id,
135 p_vendor_id,
136 p_error_code );
137 end if;
138 END LOOP;
139 CLOSE c_asn_line;
140
141 -- Cancel invoice in case of ASBN
142 if (p_invoice_num is not null) then
143 OPEN c_invoice;
144 LOOP
145 FETCH c_invoice INTO l_invoice_id, l_org_id;
146 EXIT WHEN c_invoice%NOTFOUND;
147
148 if (l_invoice_id is not null) then
149 --fnd_client_info.set_org_context(to_char(l_org_id));
150 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
151 cancel_invoice(l_invoice_id);
152 end if;
153
154 END LOOP;
155 CLOSE c_invoice;
156 end if;
157
158 BEGIN
159 POS_ASN_NOTIF.generate_notif( p_shipment_num,
160 'CANCEL',
161 p_vendor_id,
162 p_vendor_site_id,
163 fnd_global.user_id );
164
165 EXCEPTION
166 WHEN OTHERS THEN
167 p_result := 0;
168 p_error_code := 'POS_CANCEL_NOTIF_ERROR';
169 raise;
170 END;
171
172 p_result := 1;
173 commit;
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 p_result := 0;
178 p_error_code := 'POS_ASN_CANCEL_EXCEPTION';
179 raise;
180 END cancel_asn;
181
182
183 --
184 -- Get the processing status of the entire ASN entry
185 --
186 FUNCTION get_processing_status (
187 p_shipment_num VARCHAR2,
188 p_vendor_id NUMBER,
189 p_vendor_site_id NUMBER )
190 return VARCHAR2 IS
191
192 l_asn_status varchar2(25) := NULL;
193 l_processing_status varchar2(25) := NULL;
194 l_transaction_type varchar2(25) := NULL;
195 l_total_count NUMBER := 0;
196 l_pending_count NUMBER := 0;
197 l_success_count NUMBER := 0;
198 l_error_count NUMBER := 0;
199 l_rsl_line_count NUMBER := 0;
200
201 CURSOR c_ASN_status IS
202 select processing_status_code, transaction_type
203 from RCV_HEADERS_INTERFACE
204 where shipment_num = p_shipment_num
205 and vendor_id = p_vendor_id
206 and vendor_site_id = p_vendor_site_id;
207
208 BEGIN
209
210 OPEN c_ASN_status;
211 LOOP
212 FETCH c_ASN_status INTO l_processing_status, l_transaction_type;
213 EXIT WHEN c_ASN_status%NOTFOUND;
214
215 l_total_count := l_total_count + 1;
216
217 -- Status is RUNNING if any header status is running.
218 if (l_processing_status = 'RUNNING') then
219 l_asn_status := 'RUNNING';
220 exit;
221 end if;
222
223 if (l_processing_status = 'PENDING' and l_transaction_type = 'NEW') then
224 l_pending_count := l_pending_count + 1;
225 elsif (l_processing_status = 'ERROR' and l_transaction_type = 'NEW') then
226 l_error_count := l_error_count + 1;
227 elsif (l_processing_status = 'SUCCESS' and l_transaction_type = 'NEW') then
228 l_success_count := l_success_count + 1;
229 end if;
230
231 END LOOP;
232 CLOSE c_ASN_status;
233
234 if (l_asn_status = 'RUNNING' or l_total_count = 0) then
235 return l_asn_status;
236 end if;
237
238 if (l_error_count = l_total_count) then --All are in error
239 l_asn_status := 'ERROR';
240 elsif (l_pending_count + l_error_count = l_total_count) then
241 l_asn_status := 'PENDING';
242 elsif (l_success_count = l_total_count) then
243 BEGIN
244 -- fix for bug 3358908
245 -- for "successful" ASNs make sure that there exists at least one record in rcv_shipment_lines
246 select count(*)
247 into l_rsl_line_count
248 from rcv_shipment_lines
249 where shipment_header_id in
250 (select shipment_header_id from rcv_shipment_headers
251 where shipment_num = p_shipment_num
252 and vendor_id = p_vendor_id and vendor_site_id = p_vendor_site_id);
253
254 if (l_rsl_line_count > 0) then
255 l_asn_status := 'SUCCESS';
256 else
257 l_asn_status := 'ERROR';
258 end if;
259 END;
260 else
261 l_asn_status := 'MULTIPLE';
262 end if;
263
264 return l_asn_status;
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 raise;
269 END get_processing_status;
270
271
272 --
273 -- For unprocessed ASN or ASN in error, the cancellation will
274 -- directly remove the relevant records from interface tables.
275 --
276 PROCEDURE remove_from_interface (
277 p_shipment_num IN VARCHAR2,
278 p_vendor_id IN NUMBER,
279 p_vendor_site_id IN NUMBER,
280 p_result IN OUT NOCOPY NUMBER,
281 p_error_code IN OUT NOCOPY VARCHAR2 )
282 IS
283
284 x_header_interface_id NUMBER;
285
286 CURSOR C_header_interface IS
287 select header_interface_id
288 from rcv_headers_interface
289 where shipment_num = p_shipment_num
290 and vendor_id = p_vendor_id
291 and vendor_site_id = p_vendor_site_id;
292
293 /* FPJ ASN attachment */
294 l_asn_attach_id NUMBER;
295 l_return_status VARCHAR2(1);
296 l_msg_count number;
297 l_msg_data varchar2(2400);
298
299
300 CURSOR C_asn_attach (l_header_intf_id NUMBER) IS
301 select distinct rti.asn_attach_id
302 from rcv_transactions_interface rti,
303 fnd_attached_documents fad
304 where rti.header_interface_id = l_header_intf_id
305 and rti.asn_attach_id is not null
306 and to_char(rti.asn_attach_id) = fad.PK1_value
307 and fad.entity_name = 'ASN_ATTACH';
308
309
310 BEGIN
311
312 -- Delete records in transaction interface table.
313 OPEN C_header_interface;
314 LOOP
315 FETCH c_header_interface INTO x_header_interface_id;
316 EXIT WHEN c_header_interface%NOTFOUND;
317
318 if (x_header_interface_id is not null) then
319
320 /* Delete ASN attachment if exists. */
321 OPEN C_asn_attach (x_header_interface_id);
322 LOOP
323 FETCH C_asn_attach INTO l_asn_attach_id;
324 EXIT WHEN C_asn_attach%NOTFOUND;
325
326 if (l_asn_attach_id is not null) then
327
328 RCV_ASN_ATTACHMENT_PKG.delete_line_attachment (
329 p_api_version => 1.0,
330 p_init_msg_list => 'F',
331 x_return_status => l_return_status,
332 x_msg_count => l_msg_count,
333 x_msg_data => l_msg_data,
334 p_asn_attach_id => l_asn_attach_id );
335
336 end if;
337
338 END LOOP;
339 CLOSE C_asn_attach;
340
341 /* Delete ASN line from interface table. */
342 delete
343 from RCV_TRANSACTIONS_INTERFACE
344 where header_interface_id = x_header_interface_id;
345
346 end if;
347
348 END LOOP;
349 CLOSE C_header_interface;
350
351 -- Delete records in header interface table.
352 delete
353 from RCV_HEADERS_INTERFACE
354 where shipment_num = p_shipment_num
355 and vendor_id = p_vendor_id
356 and vendor_site_id = p_vendor_site_id;
357
358 p_result := 1;
359 commit;
360
361 EXCEPTION
362 WHEN OTHERS THEN
363 raise;
364 END remove_from_interface;
365
366
367 --
368 -- Get the cancellation status of the whole ASN entry.
369 --
370 FUNCTION get_cancellation_status (
371 p_shipment_num VARCHAR2,
372 p_vendor_id NUMBER,
373 p_vendor_site_id NUMBER )
374 RETURN VARCHAR2 IS
375
376 x_total_lines NUMBER := 0;
377 x_cancelled_lines NUMBER := 0;
378 x_pending_cancel NUMBER := 0;
379
380 BEGIN
381
382 /* Get total number of lines */
383 select count(*)
384 into x_total_lines
385 from RCV_SHIPMENT_LINES rsl,
386 RCV_SHIPMENT_HEADERS rsh
387 where rsh.shipment_num = p_shipment_num
388 and rsh.vendor_id = p_vendor_id
389 and rsh.vendor_site_id = p_vendor_site_id
390 and rsh.shipment_header_id = rsl.shipment_header_id;
391
392 if (x_total_lines = 0) then
393 return '';
394 end if;
395
396 /* Get total number of cancelled lines */
397 select count(*)
398 into x_cancelled_lines
399 from RCV_SHIPMENT_LINES rsl,
400 RCV_SHIPMENT_HEADERS rsh
401 where rsh.shipment_num = p_shipment_num
402 and rsh.vendor_id = p_vendor_id
403 and rsh.vendor_site_id = p_vendor_site_id
404 and rsh.shipment_header_id = rsl.shipment_header_id
405 and rsl.shipment_line_status_code = 'CANCELLED';
406
407 /* Get total number of lines pending cancellation */
408 select count(*)
409 into x_pending_cancel
410 from RCV_TRANSACTIONS_INTERFACE rti,
411 RCV_SHIPMENT_HEADERS rsh
412 where rti.transaction_type = 'CANCEL'
413 and rti.shipment_header_id = rsh.shipment_header_id
414 and rsh.shipment_num = p_shipment_num
415 and rsh.vendor_id = p_vendor_id
416 and rsh.vendor_site_id = p_vendor_site_id
417 and rti.processing_status_code ='PENDING'; /*4946276 : Performance fix and */
418 /*we should consider only the lines which are not yet processed*/
419
420 if (x_total_lines = x_cancelled_lines) then
421 return 'CANCELLED';
422 elsif (x_total_lines = x_cancelled_lines + x_pending_cancel) then
423 return 'PENDING_CANCEL';
424 elsif ((x_total_lines > x_cancelled_lines + x_pending_cancel)
425 and (x_cancelled_lines + x_pending_cancel > 0)) then
426 return 'PARTIALLY_CANCELLED';
427 else
428 return '';
429 end if;
430
431 EXCEPTION
432 WHEN OTHERS THEN
433 raise;
434 END get_cancellation_status;
435
436
437 PROCEDURE find_processing_cancellation (
438 p_shipment_num IN VARCHAR2,
439 p_header_id IN NUMBER,
440 p_vendor_id IN NUMBER,
441 p_vendor_site_id IN NUMBER,
442 x_processing_status OUT NOCOPY VARCHAR2,
443 x_processing_dsp OUT NOCOPY VARCHAR2,
444 x_cancellation_status OUT NOCOPY VARCHAR2,
445 x_cancellation_dsp OUT NOCOPY VARCHAR2) is
446
447 l_asn_status varchar2(25) := NULL;
448 l_processing_status varchar2(25) := NULL;
449 l_transaction_type varchar2(25) := NULL;
450 l_rti_error varchar2(1) := NULL;
451 l_total_count NUMBER := 0;
452 l_pending_count NUMBER := 0;
453 l_success_count NUMBER := 0;
454 l_error_count NUMBER := 0;
455 l_rsl_line_count NUMBER := 0;
456 l_rti_line_count NUMBER := 0;
457
458
459 CURSOR c_ASN_status IS
460 select processing_status_code, transaction_type
461 from RCV_HEADERS_INTERFACE
462 where shipment_num = p_shipment_num
463 and header_interface_id = p_header_id
464 and vendor_id = p_vendor_id
465 and vendor_site_id = p_vendor_site_id;
466
467 l_total_lines NUMBER := 0;
468 l_cancelled_lines NUMBER := 0;
469 l_pending_cancel NUMBER := 0;
470
471 BEGIN
472
473 OPEN c_ASN_status;
474 LOOP
475 FETCH c_ASN_status INTO l_processing_status, l_transaction_type;
476 EXIT WHEN c_ASN_status%NOTFOUND;
477
478 l_total_count := l_total_count + 1;
479
480 -- Status is RUNNING if any header status is running.
481 if (l_processing_status = 'RUNNING') then
482 l_asn_status := 'RUNNING';
483 exit;
484 end if;
485
486 if (l_processing_status = 'PENDING' and l_transaction_type = 'NEW') then
487 l_pending_count := l_pending_count + 1;
488 elsif (l_processing_status = 'ERROR' and l_transaction_type = 'NEW') then
489 l_error_count := l_error_count + 1;
490 elsif (l_processing_status = 'SUCCESS' and l_transaction_type = 'NEW') then
491 l_success_count := l_success_count + 1;
492 end if;
493
494 END LOOP;
495 CLOSE c_ASN_status;
496
497
498 if (l_total_count = 0) then
499
500 select count(*)
501 into l_rsl_line_count
502 from rcv_shipment_lines
503 where shipment_header_id = p_header_id;
504
505 if (l_rsl_line_count > 0) then
506 l_asn_status := 'SUCCESS';
507 else
508 l_asn_status := 'ERROR';
509 end if;
510
511 x_processing_status := l_asn_status;
512
513 elsif (l_asn_status = 'RUNNING') then
514
515 x_processing_status := l_asn_status;
516
517 else
518
519 if (l_error_count = l_total_count) then -- check whether all are in error
520 if (l_total_count > 0) then
521 l_asn_status := 'ERROR';
522 else
523 l_asn_status := '';
524 end if;
525 elsif (l_pending_count + l_error_count = l_total_count) then
526 l_asn_status := 'PENDING';
527 elsif (l_success_count = l_total_count) then
528
529 begin
530
531 l_rti_error := 'N';
532
533 select count(*)
534 into l_rti_line_count
535 from rcv_transactions_interface
536 where HEADER_INTERFACE_ID = p_header_id;
537
538 select 'Y' into l_rti_error
539 from dual
540 where exists
541 (select 1 from rcv_transactions_interface
542 where HEADER_INTERFACE_ID = p_header_id
543 and (processing_status_code = 'ERROR' or
544 transaction_status_code = 'ERROR'));
545
546 exception
547 when no_data_found then null;
548 end;
549
550 if (l_rti_line_count > 0) then
551 if (l_rti_error = 'Y') then
552 l_asn_status := 'ERROR';
553 else
554 l_asn_status := 'RUNNING';
555 end if;
556 else
557 l_asn_status := 'SUCCESS';
558 end if;
559
560
561
562 else
563 l_asn_status := 'MULTIPLE';
564 end if;
565
566 x_processing_status := l_asn_status;
567
568 end if; -- end of new else block
569
570
571 if (x_processing_status = 'RUNNING') then
572
573 select fnd_message_cache.get_string('POS', 'POS_RUNNING')
574 into x_processing_dsp
575 from dual;
576
577 elsif (x_processing_status = 'PENDING') then
578
579 select fnd_message_cache.get_string('POS', 'POS_PENDING')
580 into x_processing_dsp
581 from dual;
582
583 elsif (x_processing_status = 'ERROR') then
584
585 select fnd_message_cache.get_string('POS', 'POS_ERROR')
586 into x_processing_dsp
587 from dual;
588
589 elsif (x_processing_status = 'MULTIPLE') then
590
591 select fnd_message_cache.get_string('POS', 'POS_MULTIPLE')
592 into x_processing_dsp
593 from dual;
594
595 else
596
597 x_processing_dsp := '';
598
599 end if;
600
601
602
603 /* Get total number of lines */
604 select count(*)
605 into l_total_lines
606 from RCV_SHIPMENT_LINES rsl,
607 RCV_SHIPMENT_HEADERS rsh
608 where rsh.shipment_num = p_shipment_num
609 and rsh.vendor_id = p_vendor_id
610 and rsh.vendor_site_id = p_vendor_site_id
611 and rsh.shipment_header_id = rsl.shipment_header_id;
612
613 if (l_total_lines = 0) then
614
615 x_cancellation_status := '';
616
617 else -- check in rcv_shipment tables
618
619 /* Get total number of cancelled lines */
620 select count(*)
621 into l_cancelled_lines
622 from RCV_SHIPMENT_LINES rsl,
623 RCV_SHIPMENT_HEADERS rsh
624 where rsh.shipment_num = p_shipment_num
625 and rsh.vendor_id = p_vendor_id
626 and rsh.vendor_site_id = p_vendor_site_id
627 and rsh.shipment_header_id = rsl.shipment_header_id
628 and rsl.shipment_line_status_code = 'CANCELLED';
629
630 /* Get total number of lines pending cancellation */
631 select count(*)
632 into l_pending_cancel
633 from RCV_TRANSACTIONS_INTERFACE rti,
634 RCV_SHIPMENT_HEADERS rsh
635 where rti.transaction_type = 'CANCEL'
636 and rti.shipment_header_id = rsh.shipment_header_id
637 and rsh.shipment_num = p_shipment_num
638 and rsh.vendor_id = p_vendor_id
639 and rsh.vendor_site_id = p_vendor_site_id;
640
641 if (l_total_lines = l_cancelled_lines) then
642 x_cancellation_status := 'CANCELLED';
643 elsif (l_total_lines = l_cancelled_lines + l_pending_cancel) then
644 x_cancellation_status := 'PENDING_CANCEL';
645 elsif ((l_total_lines > l_cancelled_lines + l_pending_cancel)
646 and (l_cancelled_lines + l_pending_cancel > 0)) then
647 x_cancellation_status := 'PARTIALLY_CANCELLED';
648 else
649 x_cancellation_status := '';
650 end if;
651
652 end if; -- whether need to check in rcv_shipment tables
653
654 /*
655 decode(CANCELLATION_STATUS, 'CANCELLED', fnd_message.get_string('POS','POS_CANCELLED'),
656 'PENDING_CANCEL', fnd_message.get_string('POS','POS_PENDING_CANCEL'),
657 'PARTIALLY_CANCELLED', fnd_message.get_string('POS','POS_PARTIALLY_CANCELLED'), '') CANCELLATION_STATUS_DSP
658 */
659
660 if (x_cancellation_status = 'CANCELLED') then
661
662 select fnd_message_cache.get_string('POS', 'POS_CANCELLED')
663 into x_cancellation_dsp
664 from dual;
665
666 elsif (x_cancellation_status = 'PENDING_CANCEL') then
667
668 select fnd_message_cache.get_string('POS', 'POS_ASN_PENDING_CANCEL')
669 into x_cancellation_dsp
670 from dual;
671
672 elsif (x_cancellation_status = 'PARTIALLY_CANCELLED') then
673
674 select fnd_message_cache.get_string('POS', 'POS_PARTIALLY_CANCELLED')
675 into x_cancellation_dsp
676 from dual;
677
678 else
679
680 x_cancellation_dsp := '';
681
682 end if;
683
684 EXCEPTION
685 WHEN OTHERS THEN
686 raise;
687 END find_processing_cancellation;
688
689
690 --abhi: deprecated, replaced by an overloaded funcation
691
692 PROCEDURE find_processing_cancellation (
693 p_shipment_num IN VARCHAR2,
694 p_vendor_id IN NUMBER,
695 p_vendor_site_id IN NUMBER,
696 x_processing_status OUT NOCOPY VARCHAR2,
697 x_processing_dsp OUT NOCOPY VARCHAR2,
698 x_cancellation_status OUT NOCOPY VARCHAR2,
699 x_cancellation_dsp OUT NOCOPY VARCHAR2) is
700
701 l_asn_status varchar2(25) := NULL;
702 l_processing_status varchar2(25) := NULL;
703 l_transaction_type varchar2(25) := NULL;
704 l_total_count NUMBER := 0;
705 l_pending_count NUMBER := 0;
706 l_success_count NUMBER := 0;
707 l_error_count NUMBER := 0;
708 l_rsl_line_count NUMBER := 0;
709
710 CURSOR c_ASN_status IS
711 select processing_status_code, transaction_type
712 from RCV_HEADERS_INTERFACE
713 where shipment_num = p_shipment_num
714 and vendor_id = p_vendor_id
715 and vendor_site_id = p_vendor_site_id;
716
717 l_total_lines NUMBER := 0;
718 l_cancelled_lines NUMBER := 0;
719 l_pending_cancel NUMBER := 0;
720
721 BEGIN
722
723 OPEN c_ASN_status;
724 LOOP
725 FETCH c_ASN_status INTO l_processing_status, l_transaction_type;
726 EXIT WHEN c_ASN_status%NOTFOUND;
727
728 l_total_count := l_total_count + 1;
729
730 -- Status is RUNNING if any header status is running.
731 if (l_processing_status = 'RUNNING') then
732 l_asn_status := 'RUNNING';
733 exit;
734 end if;
735
736 if (l_processing_status = 'PENDING' and l_transaction_type = 'NEW') then
737 l_pending_count := l_pending_count + 1;
738 elsif (l_processing_status = 'ERROR' and l_transaction_type = 'NEW') then
739 l_error_count := l_error_count + 1;
740 elsif (l_processing_status = 'SUCCESS' and l_transaction_type = 'NEW') then
741 l_success_count := l_success_count + 1;
742 end if;
743
744 END LOOP;
745 CLOSE c_ASN_status;
746
747 if (l_asn_status = 'RUNNING' or l_total_count = 0) then
748
749 x_processing_status := l_asn_status;
750
751 else
752
753 if (l_error_count = l_total_count) then -- check whether all are in error
754 if (l_total_count > 0) then
755 l_asn_status := 'ERROR';
756 else
757 l_asn_status := '';
758 end if;
759 elsif (l_pending_count + l_error_count = l_total_count) then
760 l_asn_status := 'PENDING';
761 elsif (l_success_count = l_total_count) then
762
763 BEGIN
764 -- fix for bug 3358908
765 -- for "successful" ASNs make sure that there exists at least one record in rcv_shipment_lines
766 select count(*)
767 into l_rsl_line_count
768 from rcv_shipment_lines
769 where shipment_header_id in
770 (select shipment_header_id from rcv_shipment_headers
771 where shipment_num = p_shipment_num
772 and vendor_id = p_vendor_id and vendor_site_id = p_vendor_site_id);
773
774 if (l_rsl_line_count > 0) then
775 l_asn_status := 'SUCCESS';
776 else
777 l_asn_status := 'ERROR';
778 end if;
779 END;
780
781 else
782 l_asn_status := 'MULTIPLE';
783 end if;
784
785 x_processing_status := l_asn_status;
786
787 end if; -- end of new else block
788
789
790 /*
791 decode(PROCESSING_STATUS, 'RUNNING', fnd_message.get_string('POS','POS_RUNNING'),
792 'PENDING', fnd_message.get_string('POS','POS_PENDING'),
793 'ERROR', fnd_message.get_string('POS','POS_ERROR'),
794 'MULTIPLE', fnd_message.get_string('POS','POS_MULTIPLE'), '') PROCESSING_STATUS_DSP
795 */
796
797 if (x_processing_status = 'RUNNING') then
798
799 select fnd_message_cache.get_string('POS', 'POS_RUNNING')
800 into x_processing_dsp
801 from dual;
802
803 elsif (x_processing_status = 'PENDING') then
804
805 select fnd_message_cache.get_string('POS', 'POS_PENDING')
806 into x_processing_dsp
807 from dual;
808
809 elsif (x_processing_status = 'ERROR') then
810
811 select fnd_message_cache.get_string('POS', 'POS_ERROR')
812 into x_processing_dsp
813 from dual;
814
815 elsif (x_processing_status = 'MULTIPLE') then
816
817 select fnd_message_cache.get_string('POS', 'POS_MULTIPLE')
818 into x_processing_dsp
819 from dual;
820
821 else
822
823 x_processing_dsp := '';
824
825 end if;
826
827
828
829 /* Get total number of lines */
830 select count(*)
831 into l_total_lines
832 from RCV_SHIPMENT_LINES rsl,
833 RCV_SHIPMENT_HEADERS rsh
834 where rsh.shipment_num = p_shipment_num
835 and rsh.vendor_id = p_vendor_id
836 and rsh.vendor_site_id = p_vendor_site_id
837 and rsh.shipment_header_id = rsl.shipment_header_id;
838
839 if (l_total_lines = 0) then
840
841 x_cancellation_status := '';
842
843 else -- check in rcv_shipment tables
844
845 /* Get total number of cancelled lines */
846 select count(*)
847 into l_cancelled_lines
848 from RCV_SHIPMENT_LINES rsl,
849 RCV_SHIPMENT_HEADERS rsh
850 where rsh.shipment_num = p_shipment_num
851 and rsh.vendor_id = p_vendor_id
852 and rsh.vendor_site_id = p_vendor_site_id
853 and rsh.shipment_header_id = rsl.shipment_header_id
854 and rsl.shipment_line_status_code = 'CANCELLED';
855
856 /* Get total number of lines pending cancellation */
857 select count(*)
858 into l_pending_cancel
859 from RCV_TRANSACTIONS_INTERFACE rti,
860 RCV_SHIPMENT_HEADERS rsh
861 where rti.transaction_type = 'CANCEL'
862 and rti.shipment_header_id = rsh.shipment_header_id
863 and rsh.shipment_num = p_shipment_num
864 and rsh.vendor_id = p_vendor_id
865 and rsh.vendor_site_id = p_vendor_site_id;
866
867 if (l_total_lines = l_cancelled_lines) then
868 x_cancellation_status := 'CANCELLED';
869 elsif (l_total_lines = l_cancelled_lines + l_pending_cancel) then
870 x_cancellation_status := 'PENDING_CANCEL';
871 elsif ((l_total_lines > l_cancelled_lines + l_pending_cancel)
872 and (l_cancelled_lines + l_pending_cancel > 0)) then
873 x_cancellation_status := 'PARTIALLY_CANCELLED';
874 else
875 x_cancellation_status := '';
876 end if;
877
878 end if; -- whether need to check in rcv_shipment tables
879
880 /*
881 decode(CANCELLATION_STATUS, 'CANCELLED', fnd_message.get_string('POS','POS_CANCELLED'),
882 'PENDING_CANCEL', fnd_message.get_string('POS','POS_PENDING_CANCEL'),
883 'PARTIALLY_CANCELLED', fnd_message.get_string('POS','POS_PARTIALLY_CANCELLED'), '') CANCELLATION_STATUS_DSP
884 */
885
886 if (x_cancellation_status = 'CANCELLED') then
887
888 select fnd_message_cache.get_string('POS', 'POS_CANCELLED')
889 into x_cancellation_dsp
890 from dual;
891
892 elsif (x_cancellation_status = 'PENDING_CANCEL') then
893
894 select fnd_message_cache.get_string('POS', 'POS_ASN_PENDING_CANCEL')
895 into x_cancellation_dsp
896 from dual;
897
898 elsif (x_cancellation_status = 'PARTIALLY_CANCELLED') then
899
900 select fnd_message_cache.get_string('POS', 'POS_PARTIALLY_CANCELLED')
901 into x_cancellation_dsp
902 from dual;
903
904 else
905
906 x_cancellation_dsp := '';
907
908 end if;
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 raise;
913 END find_processing_cancellation;
914
915
916
917 --
918 -- Get the cancellation status of the single ASN line.
919 --
920 FUNCTION get_line_status (
921 p_shipment_line_id NUMBER)
922 RETURN VARCHAR2 IS
923
924 x_line_status_code VARCHAR2(30) := NULL;
925 x_transaction_type VARCHAR2(25) := NULL;
926
927 CURSOR c_line_status IS
928 Select nvl(shipment_line_status_code, '')
929 From RCV_SHIPMENT_LINES
930 Where shipment_line_id = p_shipment_line_id;
931
932 CURSOR c_transaction_type IS
933 select nvl(transaction_type, '')
934 from RCV_TRANSACTIONS_INTERFACE
935 where shipment_line_id = p_shipment_line_id;
936
937 BEGIN
938
939 OPEN c_line_status;
940 LOOP
941 FETCH c_line_status INTO x_line_status_code;
942 EXIT WHEN c_line_status%NOTFOUND;
943 END LOOP;
944 CLOSE c_line_status;
945
946 if (x_line_status_code = 'CANCELLED') then
947 return 'CANCELLED';
948 end if;
949
950 OPEN c_transaction_type;
951 LOOP
952 FETCH c_transaction_type INTO x_transaction_type;
953 EXIT WHEN c_transaction_type%NOTFOUND;
954 END LOOP;
955 CLOSE c_transaction_type;
956
957 if (x_transaction_type = 'CANCEL') then
958 return 'PENDING_CANCEL';
959 end if;
960
961 return 'OK';
962
963 EXCEPTION
964 WHEN OTHERS THEN
965 raise;
966 END get_line_status;
967
968
969 --
970 -- Cancel the individual ASN line.
971 --
972 PROCEDURE cancel_asn_line (
973 p_shipment_line_id IN NUMBER,
974 p_vendor_id IN NUMBER,
975 p_error_code IN OUT NOCOPY VARCHAR2 )
976 IS
977
978 x_group_id NUMBER;
979 X_po_header_id NUMBER;
980 X_po_release_id NUMBER;
981 X_po_line_id NUMBER;
982 X_shipment_header_id NUMBER;
983 X_po_line_location_id NUMBER;
984 X_deliver_to_location_id NUMBER;
985 X_to_organization_id NUMBER;
986 X_item_id NUMBER;
987 X_quantity_shipped NUMBER;
988 X_source_document_code VARCHAR2(25);
989 X_category_id NUMBER;
990 X_unit_of_measure VARCHAR2(25);
991 X_item_description VARCHAR2(240);
992 X_employee_id NUMBER;
993 X_destination_type_code VARCHAR2(25);
994 X_destination_context VARCHAR2(30);
995 X_subinventory VARCHAR2(10);
996 X_routing_header_id NUMBER;
997 X_primary_unit_of_measure VARCHAR2(25);
998 X_ship_to_location_id NUMBER;
999 X_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE;
1000
1001 BEGIN
1002 SELECT rcv_interface_groups_s.nextval
1003 INTO x_group_id
1004 FROM dual;
1005
1006 select
1007 RSL.PO_HEADER_ID,
1008 RSL.PO_RELEASE_ID,
1009 RSL.PO_LINE_ID,
1010 RSL.SHIPMENT_HEADER_ID,
1011 RSL.PO_LINE_LOCATION_ID,
1012 RSL.DELIVER_TO_LOCATION_ID,
1013 RSL.TO_ORGANIZATION_ID,
1014 RSL.ITEM_ID,
1015 RSL.QUANTITY_SHIPPED,
1016 RSL.SOURCE_DOCUMENT_CODE,
1017 RSL.CATEGORY_ID,
1018 RSL.UNIT_OF_MEASURE,
1019 RSL.ITEM_DESCRIPTION,
1020 RSL.EMPLOYEE_ID,
1021 RSL.DESTINATION_TYPE_CODE,
1022 RSL.DESTINATION_CONTEXT,
1023 RSL.TO_SUBINVENTORY,
1024 RSL.ROUTING_HEADER_ID,
1025 RSL.PRIMARY_UNIT_OF_MEASURE,
1026 RSL.SHIP_TO_LOCATION_ID,
1027 POHA.ORG_ID
1028 into
1029 X_po_header_id,
1030 X_po_release_id,
1031 X_po_line_id,
1032 X_shipment_header_id,
1033 X_po_line_location_id,
1034 X_deliver_to_location_id,
1035 X_to_organization_id,
1036 X_item_id,
1037 X_quantity_shipped,
1038 X_source_document_code,
1039 X_category_id,
1040 X_unit_of_measure,
1041 X_item_description,
1042 X_employee_id,
1043 X_destination_type_code,
1044 X_destination_context,
1045 X_subinventory,
1046 X_routing_header_id,
1047 X_primary_unit_of_measure,
1048 X_ship_to_location_id,
1049 X_operating_unit_id
1050 from
1051 RCV_SHIPMENT_LINES RSL,
1052 PO_HEADERS_ALL POHA
1053 where
1054 rsl.shipment_line_id = p_shipment_line_id
1055 and rsl.po_header_id = poha.po_header_id;
1056
1057 RCV_INSERT_RTI_SV.insert_into_rti(
1058 x_group_id,
1059 'CANCEL',
1060 sysdate,
1061 'PENDING',
1062 'BATCH',
1063 'PENDING',
1064 SYSDATE,
1065 1,
1066 1,
1067 'RCV',
1068 SYSDATE,
1069 1,
1070 'CANCEL',
1071 'VENDOR',
1072 X_po_header_id,
1073 X_po_release_id,
1074 X_po_line_id,
1075 p_shipment_line_id,
1076 X_shipment_header_id,
1077 X_po_line_location_id,
1078 X_deliver_to_location_id,
1079 X_to_organization_id,
1080 X_item_id,
1081 X_quantity_shipped,
1082 X_source_document_code,
1083 X_category_id,
1084 X_unit_of_measure,
1085 X_item_description,
1086 X_employee_id,
1087 X_destination_type_code,
1088 X_destination_context,
1089 X_subinventory,
1090 X_routing_header_id,
1091 X_primary_unit_of_measure,
1092 X_ship_to_location_id,
1093 p_vendor_id,
1094 X_operating_unit_id);
1095
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 p_error_code := 'POS_ASN_CANCEL_EXCEPTION';
1099 raise;
1100 END cancel_asn_line;
1101
1102
1103 --
1104 -- This procedure will be compatible with AP minipack F or above.
1105 --
1106 PROCEDURE cancel_invoice_new (
1107 p_invoice_id IN NUMBER,
1108 p_set_of_books_id IN NUMBER,
1109 p_gl_date IN DATE,
1110 P_period_name IN VARCHAR2 )
1111 IS
1112
1113 plsql_block_old VARCHAR2(1000);
1114 plsql_block_new VARCHAR2(1000);
1115
1116 l_message_name FND_NEW_MESSAGES.message_name%TYPE;
1117 l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
1118 l_base_amount AP_INVOICES.base_amount%TYPE;
1119 l_tax_amount AP_INVOICES.tax_amount%TYPE;
1120 l_temp_cancelled_amount AP_INVOICES.temp_cancelled_amount%TYPE;
1121 l_cancelled_by AP_INVOICES.cancelled_by%TYPE;
1122 l_cancelled_amount AP_INVOICES.cancelled_amount%TYPE;
1123 l_cancelled_date AP_INVOICES.cancelled_date%TYPE;
1124 l_last_update_date AP_INVOICES.last_update_date%TYPE;
1125 l_dummy_amount NUMBER;
1126 l_token VARCHAR2(1000);
1127 l_pay_curr_invoice_amount AP_INVOICES.pay_curr_invoice_amount%TYPE;
1128
1129 BEGIN
1130
1131 /* plsql_block_new has one more binding argument than plsql_block_old. */
1132 plsql_block_old := ' BEGIN if (AP_CANCEL_PKG.Ap_Cancel_Single_Invoice( ' ||
1133 ' :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, ' ||
1134 ' :v10, :v11, :v12, :v13, :v14, :v15, :v16, null, ' ||
1135 ' ''POSASNCB'')) then null; end if; END; ';
1136
1137 plsql_block_new := ' BEGIN if (AP_CANCEL_PKG.Ap_Cancel_Single_Invoice( ' ||
1138 ' :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, ' ||
1139 ' :v10, :v11, :v12, :v13, :v14, ' ||
1140 ' :v15, ''POSASNCB'')) then null; end if; END; ';
1141 BEGIN
1142 /* Cancel the invoice using function with new signature. */
1143 EXECUTE IMMEDIATE plsql_block_new USING
1144 IN p_invoice_id,
1145 IN 1,
1146 IN 1,
1147 IN p_gl_date,
1148 OUT l_message_name,
1149 OUT l_invoice_amount,
1150 OUT l_base_amount,
1151 OUT l_temp_cancelled_amount,
1152 OUT l_cancelled_by,
1153 OUT l_cancelled_amount,
1154 OUT l_cancelled_date,
1155 OUT l_last_update_date,
1156 OUT l_dummy_amount,
1157 OUT l_pay_curr_invoice_amount,
1158 OUT l_token;
1159
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162
1163 /** If the exception is due to the wrong number of arguments in call
1164 to 'AP_CANCEL_SINGLE_INVOICE', we'll try the old signature. **/
1165 IF (SQLCODE = -6550) THEN
1166 BEGIN
1167 EXECUTE IMMEDIATE plsql_block_old USING
1168 IN p_invoice_id,
1169 IN 1,
1170 IN 1,
1171 IN p_set_of_books_id,
1172 IN p_gl_date,
1173 IN p_period_name,
1174 OUT l_message_name,
1175 OUT l_invoice_amount,
1176 OUT l_base_amount,
1177 OUT l_tax_amount,
1178 OUT l_temp_cancelled_amount,
1179 OUT l_cancelled_by,
1180 OUT l_cancelled_amount,
1181 OUT l_cancelled_date,
1182 OUT l_last_update_date,
1183 OUT l_dummy_amount;
1184
1185 EXCEPTION
1186 WHEN OTHERS THEN
1187 raise;
1188 END;
1189
1190 ELSE
1191 raise; -- Raise other types of exception.
1192 END IF;
1193 END;
1194
1195 END cancel_invoice_new;
1196
1197
1198 --
1199 -- This procedure will be compatible with AP minipack A to D.
1200 --
1201 PROCEDURE cancel_invoice_old (
1202 p_invoice_id IN NUMBER,
1203 p_set_of_books_id IN NUMBER,
1204 p_gl_date IN DATE,
1205 P_period_name IN VARCHAR2 )
1206 IS
1207
1208 plsql_block_old VARCHAR2(1000);
1209 plsql_block_new VARCHAR2(1000);
1210
1211 l_message_name FND_NEW_MESSAGES.message_name%TYPE;
1212 l_invoice_amount AP_INVOICES.invoice_amount%TYPE;
1213 l_base_amount AP_INVOICES.base_amount%TYPE;
1214 l_tax_amount AP_INVOICES.tax_amount%TYPE;
1215 l_temp_cancelled_amount AP_INVOICES.temp_cancelled_amount%TYPE;
1216 l_cancelled_by AP_INVOICES.cancelled_by%TYPE;
1217 l_cancelled_amount AP_INVOICES.cancelled_amount%TYPE;
1218 l_cancelled_date AP_INVOICES.cancelled_date%TYPE;
1219 l_last_update_date AP_INVOICES.last_update_date%TYPE;
1220 l_dummy_amount NUMBER;
1221 l_token VARCHAR2(1000);
1222 l_pay_curr_invoice_amount AP_INVOICES.pay_curr_invoice_amount%TYPE;
1223
1224 BEGIN
1225
1226 /* plsql_block_new has one more binding argument than plsql_block_old. */
1227 plsql_block_old := ' BEGIN if (AP_CANCEL_PKG.Ap_Cancel_Single_Invoice( ' ||
1228 ' :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, ' ||
1229 ' :v10, :v11, :v12, :v13, :v14, :v15, :v16, null, ' ||
1230 ' ''POSASNCB'')) then null; end if; END; ';
1231
1232 plsql_block_new := ' BEGIN if (AP_CANCEL_PKG.Ap_Cancel_Single_Invoice( ' ||
1233 ' :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, ' ||
1234 ' :v10, :v11, :v12, :v13, :v14, ' ||
1235 ' :v15, ''POSASNCB'')) then null; end if; END; ';
1236
1237 BEGIN
1238 EXECUTE IMMEDIATE plsql_block_old USING
1239 IN p_invoice_id,
1240 IN 1,
1241 IN 1,
1242 IN p_set_of_books_id,
1243 IN p_gl_date,
1244 IN p_period_name,
1245 OUT l_message_name,
1246 OUT l_invoice_amount,
1247 OUT l_base_amount,
1248 OUT l_tax_amount,
1249 OUT l_temp_cancelled_amount,
1250 OUT l_cancelled_by,
1251 OUT l_cancelled_amount,
1252 OUT l_cancelled_date,
1253 OUT l_last_update_date,
1254 OUT l_dummy_amount;
1255
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258
1259 /** If the exception is due to the wrong number of arguments in call
1260 to 'AP_CANCEL_SINGLE_INVOICE', we'll try the new signature. **/
1261 IF (SQLCODE = -6550) THEN
1262 BEGIN
1263 /* Cancel the invoice using function with new signature. */
1264 EXECUTE IMMEDIATE plsql_block_new USING
1265 IN p_invoice_id,
1266 IN 1,
1267 IN 1,
1268 IN p_gl_date,
1269 OUT l_message_name,
1270 OUT l_invoice_amount,
1271 OUT l_base_amount,
1272 OUT l_temp_cancelled_amount,
1273 OUT l_cancelled_by,
1274 OUT l_cancelled_amount,
1275 OUT l_cancelled_date,
1276 OUT l_last_update_date,
1277 OUT l_dummy_amount,
1278 OUT l_pay_curr_invoice_amount,
1279 OUT l_token;
1280
1281 EXCEPTION
1282 WHEN OTHERS THEN
1283 raise;
1284 END;
1285
1286 ELSE
1287 raise; -- Raise other types of exception.
1288 END IF;
1289 END;
1290
1291 END cancel_invoice_old;
1292
1293
1294
1295 --
1296 -- Call the AP package to cancel the single invoice.
1297 --
1298 PROCEDURE cancel_invoice (
1299 p_invoice_id IN NUMBER )
1300 IS
1301
1302 l_ap_patch_level VARCHAR2(30);
1303 l_set_of_books_id NUMBER;
1304 l_gl_date DATE;
1305 l_period_name gl_period_statuses.period_name%TYPE;
1306
1307 BEGIN
1308
1309 select set_of_books_id, gl_date
1310 into l_set_of_books_id, l_gl_date
1311 from ap_invoices_all
1312 where invoice_id = p_invoice_id;
1313
1314 l_period_name := AP_INVOICES_PKG.GET_PERIOD_NAME(l_gl_date);
1315
1316 /** Get AP's patch level in the enviroment **/
1317 BEGIN
1318 ad_version_util.get_product_patch_level(200, l_ap_patch_level);
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 l_ap_patch_level := null;
1322 END;
1323
1324 /** If AP's patch level is not registered or below AP.F. **/
1325 IF (l_ap_patch_level is null or l_ap_patch_level in ('11i.AP.A',
1326 '11i.AP.B', '11i.AP.C', '11i.AP.D', '11i.AP.E')) THEN
1327
1328 cancel_invoice_old ( P_invoice_id,
1329 l_set_of_books_id,
1330 l_gl_date,
1331 l_period_name );
1332
1333 ELSE -- AP's patch level is F or above
1334 cancel_invoice_new ( P_invoice_id,
1335 l_set_of_books_id,
1336 l_gl_date,
1337 l_period_name );
1338 END IF;
1339
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 raise;
1343 END cancel_invoice;
1344
1345
1346 END POS_CANCEL_ASN;