[Home] [Help]
PACKAGE BODY: APPS.POS_WC_VIEW_CANCEL_PVT
Source
1 PACKAGE BODY POS_WC_VIEW_CANCEL_PVT AS
2 /* $Header: POSVWCVB.pls 120.11 2006/09/18 19:29:56 pkapoor noship $*/
3 l_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5
6 FUNCTION GET_WC_CANCELLATION_STATUS ( p_wc_id NUMBER)
7 RETURN VARCHAR2;
8
9 PROCEDURE GET_PO_SUMMARY_INFO
10 (
11 p_wc_header_id IN NUMBER,
12 p_wc_stage IN VARCHAR2,
13 x_po_header_id OUT nocopy NUMBER,
14 x_po_num OUT nocopy VARCHAR2,
15 x_po_currency_code OUT nocopy VARCHAR2,
16 x_po_ordered OUT nocopy NUMBER,
17 x_po_lines_ordered OUT nocopy NUMBER,
18 x_return_status OUT nocopy VARCHAR2,
19 x_return_msg OUT nocopy VARCHAR2
20 );
21
22 PROCEDURE GET_PO_INFO
23 (
24 p_po_header_id IN NUMBER,
25 x_po_ordered OUT nocopy NUMBER,
26 x_po_approved OUT nocopy NUMBER
27 );
28
29
30 PROCEDURE GET_WC_PREV_SUBMITTED
31 (
32 p_po_header_id IN VARCHAR2,
33 p_wc_request_date IN DATE,
34 x_wc_prev_submitted OUT nocopy NUMBER,
35 x_wc_prev_delivered OUT nocopy NUMBER,
36 x_return_status OUT nocopy VARCHAR2,
37 x_return_msg OUT nocopy VARCHAR2
38 );
39
40 PROCEDURE GET_WC_REQUESTED_AND_MATERIAL
41 (
42 p_wc_id IN NUMBER,
43 p_wc_stage IN VARCHAR2,
44 x_wc_requested OUT nocopy NUMBER,
45 x_wc_material OUT nocopy NUMBER,
46 x_wc_delivery OUT nocopy NUMBER,
47 x_return_status OUT nocopy VARCHAR2,
48 x_return_msg OUT nocopy VARCHAR2
49 );
50
51 PROCEDURE CANCEL_WC_PAY_ITEM
52 (
53 p_shipment_line_id IN NUMBER,
54 x_return_status OUT nocopy VARCHAR2,
55 x_return_msg OUT nocopy VARCHAR2
56 );
57
58
59 procedure LOG
60 (
61 p_level in NUMBER,
62 p_api_name in VARCHAR2,
63 p_msg in VARCHAR2
64 );
65
66 procedure LOG
67 (
68 p_level in NUMBER,
69 p_api_name in VARCHAR2,
70 p_msg in VARCHAR2
71 )
72 IS
73 l_module varchar2(2000);
74 BEGIN
75 /* Taken from Package FND_LOG
76 LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
77 LEVEL_ERROR CONSTANT NUMBER := 5;
78 LEVEL_EXCEPTION CONSTANT NUMBER := 4;
79 LEVEL_EVENT CONSTANT NUMBER := 3;
80 LEVEL_PROCEDURE CONSTANT NUMBER := 2;
81 LEVEL_STATEMENT CONSTANT NUMBER := 1;
82 */
83 IF( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
84 IF(l_fnd_debug = 'Y')THEN
85 l_module := 'pos.plsql.pos_wc_view_cancel_pvt.'||p_api_name;
86 FND_LOG.string( LOG_LEVEL => p_level,
87 MODULE => l_module,
88 MESSAGE => p_msg);
89 END IF;
90 END IF;
91 END log;
92
93 -----------------------------------------------------------------------------
94 --API name : GET_PO_HEADER_INFO
95 --TYPE : PUBLIC
96 --Function : Retrieve information related to a PO such as ordered amount
97 -- and approved amount
98 --Parameter :
99 --IN : p_po_header_id IN NUMBER Required
100 -- corresponds to the columne PO_HEADER_ID in the table
101 -- PO_HEADERS_ALL, and identifies the PO for which the
102 -- information should be retrieved.
103 --
104 --OUT : x_ordered OUT NUMBER
105 -- total ordered amount for the PO
106 -- x_approved OUT NUMBER
107 -- total approved amount for the PO
108 -- x_return_status OUT VARCHAR2
109 -- return status of the procedure
110 -- x_return_msg OUT VARCHAR2
111 -- return message of the procedure
112 -----------------------------------------------------------------------------
113 PROCEDURE GET_PO_HEADER_INFO
114 (
115 p_po_header_id IN NUMBER,
116 x_ordered OUT nocopy NUMBER,
117 x_approved OUT nocopy NUMBER,
118 x_pay_item_total OUT nocopy NUMBER,
119 x_return_status OUT nocopy VARCHAR2,
120 x_return_msg OUT nocopy VARCHAR2
121 )
122
123 IS
124
125 BEGIN
126
127 GET_PO_INFO(
128 p_po_header_id => p_po_header_id,
129 x_po_ordered => x_ordered,
130 x_po_approved => x_approved);
131
132 --Sums up all pay items (excluding delivery pay items)
133 --This information should also be obtained from the PO API in the future
134 SELECT SUM(
135 NVL((POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED,0))
136 *POLL.PRICE_OVERRIDE,
137 (POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0))))
138 INTO x_pay_item_total
139 FROM PO_LINE_LOCATIONS_ALL POLL
140 WHERE POLL.PO_HEADER_ID = p_po_header_id AND
141 POLL.PAYMENT_TYPE in ('MILESTONE', 'RATE', 'LUMPSUM');
142
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144
145 EXCEPTION
146
147 WHEN OTHERS THEN
148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
149 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_po_header_info',
150 'Unexpected error when calling PO API');
151
152
153 END GET_PO_HEADER_INFO;
154
155 -----------------------------------------------------------------------------
156 --API name : GET_WC_INFO
157 --TYPE : PUBLIC
158 --Function : Retrieve all calculated WC information
159 --Parameter :
160 --IN : p_wc_id IN NUMBER Required
161 -- corresponds to the column HEADER_INTERFACE_ID for
162 -- table RCV_HEADERS_INTERFACE; or SHIPMENT_HEADER_ID
163 -- for table RCV_SHIPMENT_HEADERS. It identifies the
164 -- WC for which the information should be retrieved.
165 -- p_wc_stage IN VARCHAR2 Required
166 -- specifies the stage of the WC (INTERFACE or SHIPMENT)
167 -- p_po_header_id IN NUMBER Required
168 -- corresponds to the columne PO_HEADER_ID in the table
169 -- PO_HEADERS_ALL, and identifies the PO for which the
170 -- information should be retrieved.
171 -- p_wc_request_date IN DATE Required
172 -- provides date information for time-sensitive info
173 -- p_vendor_id IN NUMBER Required
174 -- vendor id
175 -- p_vendor_site_id IN NUMBER Required
176 -- corresponds to vendor site id
177 --OUT : x_ordered OUT NUMBER
178 -- total ordered amount for the WC
179 -- x_approved OUT NUMBER
180 -- total approved amount for the WC
181 -- x_prev_submitted OUT NUMBER
182 -- all previously submitted amount/quantity for the WC
183 -- x_requested OUT NUMBER
184 -- requested amount for the WC
185 -- x_material_stored OUT NUMBER
186 -- total material stored amount for the WC
187 -- x_total_requested OUT NUMBER
188 -- total requested amount for the WC
189 -- x_wc_status OUT VARCHAR2
190 -- WC internal status
191 -- x_wc_display_status OUT VARCHAR2
192 -- WC display status
193 -- x_po_lines_ordered OUT NUMBER
194 -- total amount of MILESTONE, LUMPSUM, RATE pay items
195 -- excluding DELIVERY pay item
196 -- x_prev_delivered OUT NUMBER
197 -- previously submitted amount/qty of a WC for DELIVERY
198 -- pay items.
199 -- x_wc_delivered OUT NUMBER
200 -- the amount/qty requested of DELIVERY pay items of a WC
201 -- x_return_status OUT VARCHAR2
202 -- return status of the procedure
203 -- x_return_msg OUT VARCHAR2
204 -- return message of the procedure
205 -----------------------------------------------------------------------------
206 PROCEDURE GET_WC_INFO
207 (
208 p_wc_id IN NUMBER,
209 p_wc_stage IN VARCHAR2,
210 p_wc_request_date IN DATE,
211 p_vendor_id IN NUMBER,
212 p_vendor_site_id IN NUMBER,
213 x_po_header_id OUT nocopy NUMBER,
214 x_po_num OUT nocopy VARCHAR2,
215 x_po_currency_code OUT nocopy VARCHAR2,
216 x_ordered OUT nocopy NUMBER,
217 x_approved OUT nocopy NUMBER,
218 x_prev_submitted OUT nocopy NUMBER,
219 x_requested OUT nocopy NUMBER,
220 x_material_stored OUT nocopy NUMBER,
221 x_total_requested OUT nocopy NUMBER,
222 x_wc_status OUT nocopy VARCHAR2,
223 x_wc_display_status OUT nocopy VARCHAR2,
224 x_po_lines_ordered OUT nocopy NUMBER,
225 x_prev_delivered OUT nocopy NUMBER,
226 x_delivery OUT nocopy NUMBER,
227 x_return_status OUT nocopy VARCHAR2,
228 x_return_msg OUT nocopy VARCHAR2
229 )
230
231 IS
232
233 l_progress NUMBER;
234 l_api_return_status VARCHAR2(1);
235 l_api_return_msg VARCHAR2(1000);
236
237 BEGIN
238
239 l_progress := 0;
240
241 GET_WC_STATUS(
242 p_wc_id => p_wc_id,
243 p_wc_stage => p_wc_stage,
244 x_wc_status => x_wc_status,
245 x_wc_display_status => x_wc_display_status,
246 x_return_status => l_api_return_status,
247 x_return_msg => l_api_return_msg);
248
249 IF(l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
250 RAISE FND_API.G_EXC_ERROR;
251 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END IF;
254
255 l_progress := 1;
256
257 GET_PO_SUMMARY_INFO(
258 p_wc_header_id => p_wc_id,
259 p_wc_stage => p_wc_stage,
260 x_po_header_id => x_po_header_id,
261 x_po_num => x_po_num,
262 x_po_currency_code => x_po_currency_code,
263 x_po_ordered => x_ordered,
264 x_po_lines_ordered => x_po_lines_ordered,
265 x_return_status => l_api_return_status,
266 x_return_msg => l_api_return_msg);
267
268 IF(l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
269 RAISE FND_API.G_EXC_ERROR;
270 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
272 END IF;
273
274 l_progress := 2;
275
276 GET_WC_PREV_SUBMITTED (
277 p_po_header_id => x_po_header_id,
278 p_wc_request_date => p_wc_request_date,
279 x_wc_prev_submitted => x_prev_submitted,
280 x_wc_prev_delivered => x_prev_delivered,
281 x_return_status => l_api_return_status,
282 x_return_msg => l_api_return_msg);
283
284 IF(l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
285 RAISE FND_API.G_EXC_ERROR;
286 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
288 END IF;
289
290 l_progress := 3;
291
292 GET_WC_REQUESTED_AND_MATERIAL(
293 p_wc_id => p_wc_id,
294 p_wc_stage => p_wc_stage,
295 x_wc_requested => x_requested,
296 x_wc_material => x_material_stored,
297 x_wc_delivery => x_delivery,
298 x_return_status => l_api_return_status,
299 x_return_msg => l_api_return_msg);
300
301 IF(l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
302 RAISE FND_API.G_EXC_ERROR;
303 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305 END IF;
306
307 l_progress := 4;
308
309 x_total_requested := x_requested +x_material_stored;
310
311 IF(x_wc_status = 'APPROVED' or x_wc_status='PROCESSED') THEN
312 x_approved := x_total_requested;
313 END IF;
314
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316
317 EXCEPTION
318
319 WHEN FND_API.G_EXC_ERROR THEN
320 x_return_status := FND_API.G_RET_STS_ERROR;
321 x_return_msg := l_api_return_msg;
322 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_info',
323 'Expected error at stage: '|| l_progress);
324 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_info',
325 'Error: '|| l_api_return_msg);
326
327 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
329 x_return_msg := 'Unexpected error at stage: '||l_progress||
330 ' '||l_api_return_msg;
331 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_info',
332 'Unexpected error at stage: '|| l_progress);
333
334 WHEN OTHERS THEN
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
336 x_return_msg := 'Unexpected error in get_wc_info at stage: '|| l_progress;
337 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_info',
338 'Unexpected error at stage: '|| l_progress);
339
340 END GET_WC_INFO;
341
342 -----------------------------------------------------------------------------
343 --API name : GET_PO_SUMMARY_INFO
344 --TYPE : PRIVATE
345 --Function : Retrieve PO related info of a WC
346 --Parameter :
347 --IN : p_po_header_id IN NUMBER Required
348 -- corresponds to the columne PO_HEADER_ID in the table
349 -- PO_HEADERS_ALL, and identifies the PO for which the
350 -- information should be retrieved.
351 --OUT : x_po_ordered OUT NUMBER
352 -- PO total of MILESTONE, LUMPSUM, RATE and DELIVERY
353 -- pay items
354 -- x_po_lines_ordered OUT NUMBER
355 -- total of MILESTONE, LUMPSUM, RATE pay items
356 -- excluding DELIVERY pay item
357 -- x_po_num OUT VARCHAR2
358 -- returns POH.segment1
359 -- x_po_currency_code OUT VARCHAR2
360 -- returns POH.currency_code
361 -- x_return_status OUT VARCHAR2
362 -- return status of the procedure
363 -- x_return_msg OUT VARCHAR2
364 -- return message of the procedure
365 -----------------------------------------------------------------------------
366
367
368 PROCEDURE GET_PO_SUMMARY_INFO
369 (
370 p_wc_header_id IN NUMBER,
371 p_wc_stage IN VARCHAR2,
372 x_po_header_id OUT nocopy NUMBER,
373 x_po_num OUT nocopy VARCHAR2,
374 x_po_currency_code OUT nocopy VARCHAR2,
375 x_po_ordered OUT nocopy NUMBER,
376 x_po_lines_ordered OUT nocopy NUMBER,
377 x_return_status OUT nocopy VARCHAR2,
378 x_return_msg OUT nocopy VARCHAR2
379 )
380
381 IS
382
383
384 NO_PO_ID EXCEPTION;
385 l_progress NUMBER;
386 l_po_header_id NUMBER;
387
388 l_po_approved NUMBER;
389
390 BEGIN
391
392 l_progress := 0;
393
394 --Retrieves the PO number and currency
395 IF(p_wc_stage = 'INTERFACE') THEN
396
397 SELECT DISTINCT
398 POH.PO_HEADER_ID,
399 POH.SEGMENT1,
400 POH.CURRENCY_CODE
401 INTO
402 l_po_header_id, x_po_num, x_po_currency_code
403 FROM
404 RCV_TRANSACTIONS_INTERFACE RTI,
405 PO_HEADERS_ALL POH
406 WHERE
407 POH.PO_HEADER_ID = RTI.PO_HEADER_ID AND
408 RTI.HEADER_INTERFACE_ID = p_wc_header_id;
409
410 ELSIF (p_wc_stage = 'SHIPMENT') THEN
411
412 SELECT DISTINCT
413 POH.PO_HEADER_ID,
414 POH.SEGMENT1,
415 POH.CURRENCY_CODE
416 INTO
417 l_po_header_id, x_po_num, x_po_currency_code
418 FROM
419 RCV_SHIPMENT_LINES RSL,
420 PO_HEADERS_ALL POH
421 WHERE
422 POH.PO_HEADER_ID = RSL.PO_HEADER_ID AND
423 RSL.SHIPMENT_HEADER_ID = p_wc_header_id;
424
425 END IF;
426
427
428 IF(l_po_header_id is NULL) THEN
429 RAISE NO_PO_ID;
430 END IF;
431
432 l_progress := 1;
433
434 --Calls PO API to retrieve ordered amount information
435 GET_PO_INFO(
436 p_po_header_id => l_po_header_id,
437 x_po_ordered => x_po_ordered,
438 x_po_approved => l_po_approved);
439
440 l_progress := 2;
441
442 --Sums up all pay items (excluding delivery pay items)
443 --This information should also be obtained from the PO API in the future
444 SELECT SUM(
445 NVL((POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED,0))
446 *POLL.PRICE_OVERRIDE,
447 (POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0))))
448 INTO x_po_lines_ordered
449 FROM PO_LINE_LOCATIONS_ALL POLL
450 WHERE POLL.PO_HEADER_ID = l_po_header_id AND
451 POLL.PAYMENT_TYPE in ('MILESTONE', 'RATE', 'LUMPSUM');
452
453 x_po_header_id := l_po_header_id;
454
455 IF (x_po_ordered is null) THEN x_po_ordered := 0; END IF;
456 IF (x_po_lines_ordered is null) THEN x_po_lines_ordered := 0; END IF;
457
458 LOG(FND_LOG.LEVEL_PROCEDURE,'get_po_summary_info',
459 'x_po_ordered: '|| x_po_ordered);
460 LOG(FND_LOG.LEVEL_PROCEDURE,'get_po_summary_info',
461 'x_po_lines_ordered: '|| x_po_lines_ordered);
462
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464
465
466 EXCEPTION
467
468 WHEN NO_PO_ID THEN
469 x_return_status := FND_API.G_RET_STS_ERROR;
470 x_return_msg := 'Error in get_po_summary_info: No po_header_id';
471
472 WHEN OTHERS THEN
473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
474 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_prev_submitted',
475 'Unexpected error occurred');
476
477 END GET_PO_SUMMARY_INFO;
478
479 -----------------------------------------------------------------------------
480 --API name : GET_PO_INFO
481 --TYPE : PRIVATE
482 --Function : A Wrapper to PO API to retrieve PO numerical information
483 --Parameter :
484 --IN : p_po_header_id IN NUMBER Required
485 -- corresponds to the column PO_HEADER_ID in the table
486 -- PO_HEADERS_ALL, and identifies the PO for which the
487 -- information should be retrieved.
488 --
489 --OUT : x_po_ordered OUT NUMBER
490 -- total ordered amount for the PO
491 -- x_po_approved OUT NUMBER
492 -- total approved amount for the PO
493 -----------------------------------------------------------------------------
494
495 PROCEDURE GET_PO_INFO
496 (
497 p_po_header_id IN NUMBER,
498 x_po_ordered OUT nocopy NUMBER,
499 x_po_approved OUT nocopy NUMBER
500 )
501
502 IS
503
504 l_quantity_total NUMBER;
505 l_amount_total NUMBER;
506 l_quantity_delivered NUMBER;
507 l_amount_delivered NUMBER;
508 l_quantity_received NUMBER;
509 l_amount_received NUMBER;
510 l_quantity_shipped NUMBER;
511 l_amount_shipped NUMBER;
512 l_quantity_billed NUMBER;
513 l_amount_billed NUMBER;
514 l_quantity_financed NUMBER;
515 l_amount_financed NUMBER;
516 l_quantity_recouped NUMBER;
517 l_amount_recouped NUMBER;
518 l_retainage_withheld_amount NUMBER;
519 l_retainage_released_amount NUMBER;
520 l_amt_approved NUMBER := 0;
521 l_org_id NUMBER;
522
523 CURSOR l_amt_approved_csr IS
524 select SUM(DECODE(PLL.matching_basis,
525 'AMOUNT', NVL(PLL.amount_received, 0),
526 'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0)))
527 from PO_LINE_LOCATIONS_ALL PLL
528 where PLL.po_header_id = p_po_header_id
529 and PLL.payment_type in ('MILESTONE', 'RATE', 'LUMPSUM');
530
531 BEGIN
532
533 SELECT org_id
534 INTO l_org_id
535 FROM po_headers_all
536 WHERE po_header_id = p_po_header_id;
537
538 po_moac_utils_pvt.set_org_context(l_org_id);
539
540 --Obviously this API is capable of retrieving other information. But we
541 --we are only concerned with the ordered and approved amount for now.
542
543 PO_DOCUMENT_TOTALS_PVT.get_order_totals(
544 p_doc_type => PO_DOCUMENT_TOTALS_PVT.g_doc_type_PO,
545 p_doc_subtype => PO_DOCUMENT_TOTALS_PVT.g_doc_subtype_STANDARD,
546 p_doc_level => PO_DOCUMENT_TOTALS_PVT.g_doc_level_HEADER,
547 p_doc_level_id => p_po_header_id,
548 x_quantity_total => l_quantity_total,
549 x_amount_total => l_amount_total,
550 x_quantity_delivered => l_quantity_delivered,
551 x_amount_delivered => l_amount_delivered,
552 x_quantity_received => l_quantity_received,
553 x_amount_received => l_amount_received,
554 x_quantity_shipped => l_quantity_shipped,
555 x_amount_shipped => l_amount_shipped,
556 x_quantity_billed => l_quantity_billed,
557 x_amount_billed => l_amount_billed,
558 x_quantity_financed => l_quantity_financed,
559 x_amount_financed => l_amount_financed,
560 x_quantity_recouped => l_quantity_recouped,
561 x_amount_recouped => l_amount_recouped,
562 x_retainage_withheld_amount => l_retainage_withheld_amount,
563 x_retainage_released_amount => l_retainage_released_amount);
564
565 x_po_ordered := l_amount_total;
566
567 --x_po_approved := l_amount_received;
568 OPEN l_amt_approved_csr;
569 LOOP
570 FETCH l_amt_approved_csr INTO l_amt_approved;
571 EXIT WHEN l_amt_approved_csr%NOTFOUND;
572 END LOOP;
573 CLOSE l_amt_approved_csr;
574
575 x_po_approved := NVL(l_amt_approved, 0);
576
577 LOG(FND_LOG.LEVEL_PROCEDURE,'get_po_info',
578 'x_po_ordered: '|| l_amount_total);
579 LOG(FND_LOG.LEVEL_PROCEDURE,'get_po_info',
580 'x_po_approved: '|| l_amount_received);
581
582
583 EXCEPTION
584
585 WHEN OTHERS THEN NULL;
586 LOG(FND_LOG.LEVEL_PROCEDURE,'get_po_summary_info',
587 'Call to PO_DOCUMENT_TOTALS_PVT.get_order_totals not successful');
588 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589
590 END GET_PO_INFO;
591
592 -----------------------------------------------------------------------------
593 --API name : GET_WC_PREV_SUBMITTED
594 --TYPE : PRIVATE
595 --Function : Retrieve Previously submitted amount/qty for a WC
596 --Parameter :
597 --IN : p_po_header_id IN NUMBER Required
598 -- corresponds to the column PO_HEADER_ID in the table
599 -- PO_HEADERS_ALL, and identifies the PO for which the
600 -- information should be retrieved.
601 -- p_wc_request_date IN DATE Required
602 -- uses to establish a time of reference for the
603 -- the defintion of Previously Submitted.
604 --OUT : x_prev_submitted OUT NUMBER
605 -- previously submitted amount of a WC for pay items:
606 -- MILESTONE, LUMPSUM, RATE
607 -- x_prev_delivered OUT NUMBER
608 -- previously submitted amount/qty of a WC for DELIVERY
609 -- pay items.
610 -- x_return_status OUT VARCHAR2
611 -- return status of the procedure
612 -- x_return_msg OUT VARCHAR2
613 -- return message of the procedure
614 -----------------------------------------------------------------------------
615
616 PROCEDURE GET_WC_PREV_SUBMITTED
617 (
618 p_po_header_id IN VARCHAR2,
619 p_wc_request_date IN DATE,
620 x_wc_prev_submitted OUT nocopy NUMBER,
621 x_wc_prev_delivered OUT nocopy NUMBER,
622 x_return_status OUT nocopy VARCHAR2,
623 x_return_msg OUT nocopy VARCHAR2
624 )
625
626 IS
627
628 l_wc_intf_prev_submitted NUMBER;
629 l_wc_ship_prev_submitted NUMBER;
630 l_wc_intf_prev_delivered NUMBER;
631 l_wc_ship_prev_delivered NUMBER;
632
633 BEGIN
634
635 l_wc_intf_prev_submitted := 0;
636 l_wc_ship_prev_submitted := 0;
637 l_wc_intf_prev_delivered := 0;
638 l_wc_ship_prev_delivered := 0;
639
640 --Previously Submitted
641 --Processing, Pending Approval, Approved, Processed and Rejected
642 --Roll up all requested amount/quantity*price of all WCs against the same PO
643
644
645 -------------
646 --Interface
647 -------------
648
649 /*
650 Rolls up all previously submitted pay items that are in INTF tables
651 (excluding delivery pay items)
652 */
653 SELECT SUM( NVL(RTI.AMOUNT, RTI.QUANTITY*POLL.PRICE_OVERRIDE))
654 INTO l_wc_intf_prev_submitted
655 FROM
656 PO_LINE_LOCATIONS_ALL POLL,
657 RCV_TRANSACTIONS_INTERFACE RTI,
658 RCV_HEADERS_INTERFACE RHI
659 WHERE
660 RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
661 RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
662 RTI.PROCESSING_STATUS_CODE = 'PENDING' AND
663 RTI.TRANSACTION_STATUS_CODE = 'PENDING' AND
664 POLL.PAYMENT_TYPE in ('MILESTONE', 'RATE', 'LUMPSUM') AND
665 RTI.PO_HEADER_ID = p_po_header_id AND
666 RHI.REQUEST_DATE < p_wc_request_date;
667
668 IF(l_wc_intf_prev_submitted is NULL) THEN
669 l_wc_intf_prev_submitted := 0;
670 END IF;
671
672 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_prev_submitted',
673 'l_wc_intf_prev_submitted: '|| l_wc_intf_prev_submitted);
674
675 /*
676 Rolls up all previously submitted delivery pay items
677 that are in INTF tables
678 */
679 SELECT SUM( NVL(RTI.AMOUNT, RTI.QUANTITY*POLL.PRICE_OVERRIDE))
680 INTO l_wc_intf_prev_delivered
681 FROM
682 PO_LINE_LOCATIONS_ALL POLL,
683 RCV_TRANSACTIONS_INTERFACE RTI,
684 RCV_HEADERS_INTERFACE RHI
685 WHERE
686 RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
687 RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
688 RTI.PROCESSING_STATUS_CODE = 'PENDING' AND
689 RTI.TRANSACTION_STATUS_CODE = 'PENDING' AND
690 POLL.PAYMENT_TYPE = 'DELIVERY' AND
691 RTI.PO_HEADER_ID = p_po_header_id AND
692 RHI.REQUEST_DATE < p_wc_request_date;
693
694 IF(l_wc_intf_prev_delivered is NULL) THEN
695 l_wc_intf_prev_delivered := 0;
696 END IF;
697
698 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_prev_submitted',
699 'l_wc_intf_prev_delivered: '|| l_wc_intf_prev_delivered);
700
701 -------------
702 --Shipment
703 -------------
704
705 /*
706 Rolls up all previously submitted pay items that are in SHIPMENT tables
707 (excluding delivery pay items)
708 */
709 SELECT SUM(NVL(RSL.AMOUNT_SHIPPED,RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
710 INTO l_wc_ship_prev_submitted
711 FROM
712 PO_LINE_LOCATIONS_ALL POLL,
713 RCV_SHIPMENT_HEADERS RSH,
714 RCV_SHIPMENT_LINES RSL
715 WHERE
716 RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
717 RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
718 (RSL.APPROVAL_STATUS is NULL OR
719 RSL.APPROVAL_STATUS in ('APPROVED', 'REJECTED')) AND
720 RSL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED' AND
721 POLL.PAYMENT_TYPE in ('MILESTONE', 'RATE', 'LUMPSUM') AND
722 RSL.PO_HEADER_ID = p_po_header_id AND
723 RSH.REQUEST_DATE < p_wc_request_date;
724
725 IF(l_wc_ship_prev_submitted is NULL) THEN
726 l_wc_ship_prev_submitted := 0;
727 END IF;
728
729 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_prev_submitted',
730 'l_wc_ship_prev_submitted: '|| l_wc_ship_prev_submitted);
731
732 /*Rolls up all previously submitted delivery pay items
733 that are in SHIPMENT tables
734 */
735 SELECT SUM(NVL(RSL.AMOUNT_SHIPPED,RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
736 INTO l_wc_ship_prev_delivered
737 FROM
738 PO_LINE_LOCATIONS_ALL POLL,
739 RCV_SHIPMENT_HEADERS RSH,
740 RCV_SHIPMENT_LINES RSL
741 WHERE
742 RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
743 RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
744 (RSL.APPROVAL_STATUS is NULL OR
745 RSL.APPROVAL_STATUS in ('APPROVED', 'REJECTED')) AND
746 RSL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED' AND
747 POLL.PAYMENT_TYPE = 'DELIVERY' AND
748 RSL.PO_HEADER_ID = p_po_header_id AND
749 RSH.REQUEST_DATE < p_wc_request_date;
750
751 IF(l_wc_ship_prev_delivered is NULL) THEN
752 l_wc_ship_prev_delivered := 0;
753 END IF;
754
755 x_wc_prev_submitted := l_wc_intf_prev_submitted + l_wc_ship_prev_submitted;
756 x_wc_prev_delivered := l_wc_intf_prev_delivered + l_wc_ship_prev_delivered;
757
758 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_prev_submitted',
759 'x_wc_prev_submitted: '|| x_wc_prev_submitted);
760 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_prev_submitted',
761 'x_wc_prev_delivered: '|| x_wc_prev_delivered);
762
763 x_return_status := FND_API.G_RET_STS_SUCCESS;
764
765 EXCEPTION
766
767 WHEN OTHERS THEN
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
769 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_prev_submitted',
770 'Unexpected error occurred');
771
772 END GET_WC_PREV_SUBMITTED;
773
774
775 -----------------------------------------------------------------------------
776 --API name : GET_WC_REQUESTED_AND_MATERIAL
777 --TYPE : PRIVATE
778 --Function : Retrieve requested amount/qty and material stored amount of a WC
779 --Parameter :
780 --IN : p_wc_id IN NUMBER Required
781 -- corresponds to the column HEADER_INTERFACE_ID in
782 -- the table RCV_HEADERS_INTERFACE
783 -- p_wc_stage IN VARCHAR2 Required
784 -- indicates if the information in the INTERFACE tables
785 -- or the SHIPMENT tables
786 --OUT : x_wc_requested OUT NUMBER
787 -- the amount/qty requested of
788 -- LUMPSUM, MILESTONE, RATE pay items of a WC
789 -- x_wc_delivered OUT NUMBER
790 -- the amount/qty requested of DELIVERY pay items of a WC
791 -- x_wc_material OUT NUMBER
792 -- corresponds to the material stored of a WC
793 -- x_return_status OUT VARCHAR2
794 -- return status of the procedure
795 -- x_return_msg OUT VARCHAR2
796 -- return message of the procedure
797 -----------------------------------------------------------------------------
798
799 PROCEDURE GET_WC_REQUESTED_AND_MATERIAL
800 (
801 p_wc_id IN NUMBER,
802 p_wc_stage IN VARCHAR2,
803 x_wc_requested OUT nocopy NUMBER,
804 x_wc_material OUT nocopy NUMBER,
805 x_wc_delivery OUT nocopy NUMBER,
806 x_return_status OUT nocopy VARCHAR2,
807 x_return_msg OUT nocopy VARCHAR2
808 )
809
810 IS
811
812 l_requested NUMBER;
813 l_material NUMBER;
814 l_delivery NUMBER;
815
816 BEGIN
817
818 IF(p_wc_stage = 'INTERFACE') THEN
819
820 SELECT SUM(NVL(RTI.REQUESTED_AMOUNT, RTI.QUANTITY*POLL.PRICE_OVERRIDE)),
821 SUM(NVL(RTI.MATERIAL_STORED_AMOUNT,0))
822 INTO l_requested, l_material
823 FROM RCV_TRANSACTIONS_INTERFACE RTI,
824 RCV_HEADERS_INTERFACE RHI,
825 PO_LINE_LOCATIONS_ALL POLL
826 WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
827 RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
828 POLL.PAYMENT_TYPE in ('MILESTONE', 'LUMPSUM', 'RATE') AND
829 RHI.HEADER_INTERFACE_ID = p_wc_id;
830
831 SELECT SUM(NVL(RTI.AMOUNT, RTI.QUANTITY*POLL.PRICE_OVERRIDE))
832 INTO l_delivery
833 FROM RCV_TRANSACTIONS_INTERFACE RTI,
834 RCV_HEADERS_INTERFACE RHI,
835 PO_LINE_LOCATIONS_ALL POLL
836 WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
837 RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
838 POLL.PAYMENT_TYPE = 'DELIVERY' AND
839 RHI.HEADER_INTERFACE_ID = p_wc_id;
840
841 ELSIF(p_wc_stage = 'SHIPMENT') THEN
842
843 SELECT SUM(NVL(RSL.REQUESTED_AMOUNT,RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE)),
844 SUM(NVL(RSL.MATERIAL_STORED_AMOUNT,0))
845 INTO l_requested, l_material
846 FROM RCV_SHIPMENT_LINES RSL,
847 RCV_SHIPMENT_HEADERS RSH,
848 PO_LINE_LOCATIONS_ALL POLL
849 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
850 RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
851 POLL.PAYMENT_TYPE in ('MILESTONE', 'LUMPSUM', 'RATE') AND
852 RSH.SHIPMENT_HEADER_ID = p_wc_id;
853
854 SELECT SUM(NVL(RSL.AMOUNT_SHIPPED,RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
855 INTO l_delivery
856 FROM RCV_SHIPMENT_LINES RSL,
857 RCV_SHIPMENT_HEADERS RSH,
858 PO_LINE_LOCATIONS_ALL POLL
859 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
860 RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
861 POLL.PAYMENT_TYPE = 'DELIVERY' AND
862 RSH.SHIPMENT_HEADER_ID = p_wc_id;
863
864 END IF;
865
866 IF(l_requested is NULL) THEN l_requested := 0; END IF;
867 IF(l_material is NULL) THEN l_material := 0; END IF;
868 IF(l_delivery is NULL) THEN l_delivery := 0; END IF;
869
870 x_wc_requested := l_requested;
871 x_wc_material := l_material;
872 x_wc_delivery := l_delivery;
873
874 x_return_status := FND_API.G_RET_STS_SUCCESS;
875
876 EXCEPTION
877
878 WHEN OTHERS THEN
879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
880 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_requested_and_material',
881 'Unexpected error occurred');
882
883 END GET_WC_REQUESTED_AND_MATERIAL;
884
885 -----------------------------------------------------------------------------
886 --API name : GET_WC_STATUS
887 --TYPE : PRIVATE
888 --Function : Retrieve the status of a WC
889 --Parameter :
890 --IN : p_wc_id IN NUMBER Required
891 -- corresponds to the column HEADER_INTERFACE_ID in
892 -- the table RCV_HEADERS_INTERFACE.
893 -- p_wc_stage IN VARCHAR2 Required
894 -- indicates if the information in the INTERFACE tables.
895 -- or the SHIPMENT tables
896 --OUT : x_wc_status OUT VARCHAR2
897 -- corresponds to internal status of a WC.
898 -- x_wc_display_status OUT VARCHAR2
899 -- corresponds to the status of a WC on the UI.
900 -- x_return_status OUT VARCHAR2
901 -- return status of the procedure.
902 -- x_return_msg OUT VARCHAR2
903 -- return message of the procedure.
904 -----------------------------------------------------------------------------
905
906 PROCEDURE GET_WC_STATUS
907 (
908 p_wc_id IN NUMBER,
909 p_wc_stage IN VARCHAR2,
910 x_wc_status OUT nocopy VARCHAR2,
911 x_wc_display_status OUT nocopy VARCHAR2,
912 x_return_status OUT nocopy VARCHAR2,
913 x_return_msg OUT nocopy VARCHAR2
914 )
915
916 IS
917
918 l_rti_line_count NUMBER := 1;
919 l_wc_total_lines NUMBER;
920 l_wc_lines_processed NUMBER;
921 l_wc_lines_errored NUMBER;
922 l_header_processing_status VARCHAR2(10);
923 l_header_transaction_type VARCHAR2(10);
924 l_header_approval_status VARCHAR2(20);
925 l_line_approval_status VARCHAR2(20);
926 l_cancellation_status VARCHAR2(20);
927 l_is_wc_processed VARCHAR2(1);
928
929 BEGIN
930
931 --Possible statuses in Interface stage:
932 --DRAFT, PROCESSING, ERROR, CANCEL
933
934
935 --WC in the Interface Tables
936 IF (p_wc_stage = 'INTERFACE') then
937
938 SELECT processing_status_code, transaction_type
939 INTO l_header_processing_status, l_header_transaction_type
940 FROM RCV_HEADERS_INTERFACE
941 WHERE HEADER_INTERFACE_ID = p_wc_id;
942
943 IF(l_header_processing_status = 'DRAFT' and
944 l_header_transaction_type = 'DRAFT') THEN
945
946 x_wc_status := 'DRAFT';
947 x_wc_display_status :=
948 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_DRAFT');
949
950 ELSIF(l_header_processing_status = 'PENDING' and
951 l_header_transaction_type = 'NEW') THEN
952
953 x_wc_status := 'PROCESSING';
954 x_wc_display_status :=
955 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PROCESSING');
956
957 ELSIF(l_header_processing_status = 'RUNNING' and
958 l_header_transaction_type = 'NEW') THEN
959
960 x_wc_status := 'RUNNING';
961 x_wc_display_status :=
962 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_RUNNING');
963
964 ELSIF(l_header_processing_status = 'SUCCESS' and
965 l_header_transaction_type = 'NEW') THEN
966
967 x_wc_status := 'SUCCESS';
968
969
970 ELSIF(l_header_processing_status = 'ERROR' and
971 l_header_transaction_type = 'NEW') THEN
972
973 x_wc_status := 'ERROR';
974 x_wc_display_status :=
975 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_ERROR');
976
977 ELSIF(l_header_processing_status = 'CANCELLED') THEN
978
979 x_wc_status := 'INTERFACE CANCELED';
980 x_wc_display_status := fnd_message_cache.get_string('POS', 'POS_WC_STATUS_CANCELED');
981
982 -- Not handling ERROR and CANCEL yet
983
984 END IF;
985 END IF;
986
987 IF (p_wc_stage = 'SHIPMENT') THEN
988
989
990 l_cancellation_status := GET_WC_CANCELLATION_STATUS(p_wc_id);
991
992 IF (l_cancellation_status = 'NO_CANCELLATION') THEN
993
994 SELECT RSH.APPROVAL_STATUS
995 INTO l_header_approval_status
996 FROM RCV_SHIPMENT_HEADERS RSH
997 WHERE RSH.SHIPMENT_HEADER_ID = p_wc_id;
998
999 IF(l_header_approval_status is null) THEN
1000
1001 x_wc_status := 'PENDING APPROVAL';
1002 x_wc_display_status :=
1003 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PENDING_APPROVAL');
1004
1005 ELSIF(l_header_approval_status = 'APPROVED') THEN
1006
1007 --Get number of shipment lines
1008 SELECT count(*) into l_wc_total_lines
1009 FROM RCV_SHIPMENT_LINES
1010 WHERE SHIPMENT_HEADER_ID = p_wc_id;
1011
1012 --Get number of the shipment lines that have been processed
1013 SELECT count(*) into l_wc_lines_processed
1014 FROM RCV_TRANSACTIONS
1015 WHERE TRANSACTION_TYPE = 'DELIVER' AND
1016 SHIPMENT_HEADER_ID = p_wc_id;
1017
1018 --Get number of the shipment lines that have been errored out
1019 SELECT count(*) into l_wc_lines_errored
1020 FROM RCV_TRANSACTIONS_INTERFACE RTI,
1021 RCV_SHIPMENT_HEADERS RSH,
1022 RCV_SHIPMENT_LINES RSL
1023 WHERE RTI.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND
1024 RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
1025 RTI.PROCESSING_STATUS_CODE in ('ERROR','COMPLETED') AND
1026 RTI.TRANSACTION_STATUS_CODE = 'ERROR' AND
1027 RSH.SHIPMENT_HEADER_ID = p_wc_id;
1028
1029 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_status',
1030 'Number of Shipment lines: '||l_wc_total_lines);
1031 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_status',
1032 'Number of processed Shipment Lines: '||l_wc_lines_processed);
1033 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_status',
1034 'Number of errored Shipment Lines: '||l_wc_lines_errored);
1035
1036 IF(l_wc_lines_processed = 0 and l_wc_lines_errored = 0) THEN
1037 x_wc_status := 'APPROVED';
1038 x_wc_display_status :=
1039 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_APPROVED');
1040 /*
1041 -- moving this code block into the ELSE condition - bug 5452504
1042 -- l_wc_lines_processed is equal to the number of distributions,
1043 -- l_wc_total_lines is same as number of shipments, and need not be equal when multiple distributions
1044 ELSIF(l_wc_total_lines = l_wc_lines_processed) THEN
1045 x_wc_status := 'PROCESSED';
1046 x_wc_display_status :=
1047 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PROCESSED');
1048 */
1049 ELSIF(l_wc_lines_errored > 0) THEN
1050 x_wc_status := 'PROCESSING_ERROR';
1051 x_wc_display_status :=
1052 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PROCESS_ERROR');
1053 ELSE
1054 x_wc_status := 'PROCESSED';
1055 x_wc_display_status :=
1056 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PROCESSED');
1057 END IF;
1058
1059
1060 ELSIF(l_header_approval_status = 'REJECTED') THEN
1061
1062 x_wc_status := 'REJECTED';
1063 x_wc_display_status :=
1064 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_REJECTED');
1065
1066 END IF;
1067
1068 ELSE
1069
1070 IF(l_cancellation_status = 'CANCELED') THEN
1071
1072 x_wc_status := 'SHIPMENT CANCELED';
1073 x_wc_display_status :=
1074 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_CANCELED');
1075
1076 ELSIF(l_cancellation_status = 'PENDING CANCEL') THEN
1077
1078 x_wc_status := 'PENDING CANCEL';
1079 x_wc_display_status :=
1080 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PENDING_CANCEL');
1081
1082 ELSIF(l_cancellation_status = 'PARTIALLY CANCELED') THEN
1083
1084 x_wc_status := 'PARTIALLY CANCELED';
1085 x_wc_display_status :=
1086 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PARTIALLY_CANCEL');
1087 END IF;
1088
1089 END IF;
1090
1091 END IF;
1092
1093 x_return_status := FND_API.G_RET_STS_SUCCESS;
1094
1095 EXCEPTION
1096
1097 WHEN OTHERS THEN
1098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1099 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_status',
1100 'Unexpected error occurred');
1101
1102 END GET_WC_STATUS;
1103
1104
1105 -----------------------------------------------------------------------------
1106 --API name : GET_WC_CANCELLATION_STATUS
1107 --TYPE : PRIVATE
1108 --Function : To retrieve the cancellation status of a WC
1109 --Parameter :
1110 --IN : p_wc_id IN VARCHAR2 Required
1111 -- corresponds to the column SHIPMENT_NUM in
1112 -- the table RCV_HEADERS_INTERFACE.
1113 --OUT : OUT VARCHAR2 Required
1114 -- returns the cancellation status of the WC
1115 -----------------------------------------------------------------------------
1116
1117 FUNCTION GET_WC_CANCELLATION_STATUS ( p_wc_id NUMBER)
1118 RETURN VARCHAR2 IS
1119
1120 x_total_lines NUMBER := 0;
1121 x_cancelled_lines NUMBER := 0;
1122 x_pending_cancel NUMBER := 0;
1123
1124 BEGIN
1125
1126 /* Get total number of lines */
1127 select count(*)
1128 into x_total_lines
1129 from RCV_SHIPMENT_LINES rsl,
1130 RCV_SHIPMENT_HEADERS rsh
1131 where rsh.shipment_header_id= p_wc_id
1132 and rsh.shipment_header_id = rsl.shipment_header_id;
1133
1134 if (x_total_lines = 0) then
1135 return '';
1136 end if;
1137
1138 /* Get total number of cancelled lines */
1139 select count(*)
1140 into x_cancelled_lines
1141 from RCV_SHIPMENT_LINES rsl,
1142 RCV_SHIPMENT_HEADERS rsh
1143 where rsh.shipment_header_id = p_wc_id
1144 and rsh.shipment_header_id = rsl.shipment_header_id
1145 and rsl.shipment_line_status_code = 'CANCELLED';
1146
1147 /* Get total number of lines pending cancellation */
1148 select count(*)
1149 into x_pending_cancel
1150 from RCV_TRANSACTIONS_INTERFACE rti,
1151 RCV_SHIPMENT_HEADERS rsh
1152 where rti.transaction_type = 'CANCEL'
1153 and rti.shipment_header_id = rsh.shipment_header_id
1154 and rsh.shipment_header_id = p_wc_id;
1155
1156 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_cancellation_status',
1157 'Total Number of lines: '||x_total_lines);
1158 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_cancellation_status',
1159 'Total Number of canceled lines: '||x_cancelled_lines);
1160 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_cancellation_status',
1161 'Total Number of lines pending cancelation: '||x_pending_cancel);
1162
1163 if (x_total_lines = x_cancelled_lines) then
1164 return 'CANCELED';
1165 elsif (x_total_lines = x_cancelled_lines + x_pending_cancel) then
1166 return 'PENDING CANCEL';
1167 elsif ((x_total_lines > x_cancelled_lines + x_pending_cancel)
1168 and (x_cancelled_lines + x_pending_cancel > 0)) then
1169 return 'PARTIALLY CANCELED';
1170 else
1171 return 'NO_CANCELLATION';
1172 end if;
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 LOG(FND_LOG.LEVEL_UNEXPECTED,'GET_WC_CANCELLATION_STATUS',
1177 'Unexpected error occurred');
1178
1179 END GET_WC_CANCELLATION_STATUS;
1180
1181
1182 -----------------------------------------------------------------------------
1183 --API name : CANCEL_WC
1184 --TYPE : PUBLIC
1185 --Function : Cancel a WC
1186 --Parameter :
1187 --IN : p_wc_num IN VARCHAR2 Required
1188 -- corresponds to the column SHIPMENT_NUM in
1189 -- the table RCV_HEADERS_INTERFACE.
1190 --IN : p_wc_id IN NUMBER Required
1191 -- corresponds to the column HEADER_INTERFACE_ID in
1192 -- the table RCV_HEADERS_INTERFACE.
1193 -- p_wc_stage IN VARCHAR2 Required
1194 -- indicates if the information in the INTERFACE tables.
1195 -- or the SHIPMENT tables
1196 -- p_po_header_id IN NUMBER Required
1197 -- corresponds to the column PO_HEADER_ID in
1198 -- the table PO_HEADERS_ALL.
1199 --OUT : x_return_status OUT VARCHAR2
1200 -- return status of the procedure.
1201 -- x_return_msg OUT VARCHAR2
1202 -- return message of the procedure.
1203 -----------------------------------------------------------------------------
1204
1205 PROCEDURE CANCEL_WC
1206 (
1207 p_wc_num IN VARCHAR2,
1208 p_wc_id IN NUMBER,
1209 p_wc_status IN VARCHAR2,
1210 p_po_header_id IN NUMBER,
1211 x_return_status OUT NOCOPY VARCHAR2,
1212 x_return_msg OUT NOCOPY VARCHAR2
1213 )
1214
1215 IS
1216
1217 l_api_return_status VARCHAR2(1);
1218 l_api_return_msg VARCHAR2(1000);
1219 l_buyer_id NUMBER;
1220 l_shipment_line_id NUMBER;
1221 NOTIF_ERROR EXCEPTION;
1222 CANCEL_PAY_ITEM_ERROR EXCEPTION;
1223
1224 l_wf_itemtype varchar2(8);
1225 l_wf_itemkey Varchar2(280);
1226
1227 cursor ship_header_cursor(p_header_id number) is
1228 select wf_item_type, wf_item_key
1229 from rcv_shipment_headers
1230 where shipment_header_id = p_header_id;
1231
1232 CURSOR l_wc_pay_item_csr IS
1233 SELECT rsl.shipment_line_id
1234 FROM RCV_SHIPMENT_LINES rsl,
1235 RCV_SHIPMENT_HEADERS rsh
1236 WHERE rsh.SHIPMENT_HEADER_ID = p_wc_id
1237 AND rsh.shipment_header_id = rsl.shipment_header_id;
1238
1239 BEGIN
1240
1241
1242 -- Business rule dictates that only the following WC statuses can
1243 -- be cancelled:
1244 --
1245 -- 1)Processing (interface)
1246 -- 2)Rejected (shipment)
1247 -- 3)Pending Approval (shipment)
1248
1249
1250 -- For 'Processing' WC, RHI.PROCESSING_STATUS_CODE and
1251 -- RTI.PROCESSING_STATUS_CODE will be populated with 'CANCELLED'
1252
1253 -- Should put business logic here to make sure we can cancel the WC
1254
1255 IF(p_wc_status = 'PROCESSING') THEN
1256
1257 UPDATE RCV_TRANSACTIONS_INTERFACE
1258 SET PROCESSING_STATUS_CODE = 'CANCELLED'
1259 WHERE HEADER_INTERFACE_ID = p_wc_id;
1260
1261
1262 UPDATE RCV_HEADERS_INTERFACE
1263 SET PROCESSING_STATUS_CODE = 'CANCELLED'
1264 WHERE HEADER_INTERFACE_ID = p_wc_id;
1265
1266
1267
1268 END IF;
1269
1270
1271 -- For 'Pending Approval' and 'Rejected' WC, a new RTI will be created
1272 -- for every RSL line to be cancelled. The status of the WC will be
1273 -- shown as 'PENDING CANCEL' until the WC is processed by the RTP.
1274 -- At the point, the WC will be shown as 'CANCELLED'
1275
1276 IF(p_wc_status = 'REJECTED' OR p_wc_status = 'PENDING APPROVAL') THEN
1277
1278 OPEN l_wc_pay_item_csr;
1279 LOOP
1280 FETCH l_wc_pay_item_csr INTO l_shipment_line_id;
1281 EXIT WHEN l_wc_pay_item_csr%NOTFOUND;
1282 CANCEL_WC_PAY_ITEM(l_shipment_line_id,
1283 l_api_return_status,
1284 l_api_return_msg);
1285 IF(l_api_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1286 RAISE CANCEL_PAY_ITEM_ERROR;
1287 END IF;
1288 END LOOP;
1289 CLOSE l_wc_pay_item_csr;
1290
1291 --Cancellation Notification to the Buyer
1292
1293 --First get buyer id from PO Header
1294 SELECT POH.AGENT_ID
1295 INTO l_buyer_id
1296 FROM PO_HEADERS_ALL POH
1297 WHERE POH.PO_HEADER_ID = p_po_header_id;
1298
1299 --Debug Information
1300 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','Cancellation Notif parameters:');
1301 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','WC Num:'||p_wc_num);
1302 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','WC ID:'||p_wc_id);
1303 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','WC Status:'||p_wc_status);
1304 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','PO Header ID:'||p_po_header_id);
1305 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','Buyer ID:'||l_buyer_id);
1306 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','User ID:'||fnd_global.user_id);
1307
1308 --Close previous 'requires approval' notif sent to buyer
1309 open ship_header_cursor(p_wc_id);
1310 fetch ship_header_cursor into l_wf_itemtype, l_wf_itemkey;
1311 close ship_header_cursor;
1312
1313 WF_ENGINE.CompleteActivity(l_wf_itemtype, l_wf_itemkey,
1314 'WC_APPROVE', 'Cancel');
1315
1316 POS_ASN_NOTIF.GENERATE_WC_NOTIF
1317 (
1318 p_wc_num => p_wc_num,
1319 p_wc_id => p_wc_id,
1320 p_wc_status => p_wc_status,
1321 p_po_header_id => p_po_header_id,
1322 p_buyer_id => l_buyer_id,
1323 p_user_id => fnd_global.user_id,
1324 x_return_status => l_api_return_status,
1325 x_return_msg => l_api_return_msg
1326 );
1327
1328 IF(l_api_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1329 RAISE NOTIF_ERROR;
1330 END IF;
1331
1332 END IF;
1333
1334 COMMIT;
1335
1336 x_return_status := FND_API.G_RET_STS_SUCCESS;
1337
1338 EXCEPTION
1339
1340 WHEN CANCEL_PAY_ITEM_ERROR THEN
1341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1342 LOG(FND_LOG.LEVEL_UNEXPECTED,'cancel_wc',
1343 'Unexpected error occurred when cancelling pay item');
1344 WHEN NOTIF_ERROR THEN
1345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1346 LOG(FND_LOG.LEVEL_UNEXPECTED,'cancel_wc',
1347 'Unexpected error occurred when sending buyer a cancellation notification');
1348 LOG(FND_LOG.LEVEL_UNEXPECTED,'cancel_wc',
1349 'Error Message: '||l_api_return_msg);
1350
1351 WHEN OTHERS THEN
1352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1353 LOG(FND_LOG.LEVEL_UNEXPECTED,'cancel_wc',
1354 'Unexpected error occurred');
1355
1356
1357 END CANCEL_WC;
1358
1359
1360 -----------------------------------------------------------------------------
1361 --API name : CANCEL_WC_PAY_ITEM
1362 --TYPE : PUBLIC
1363 --Function : Cancel a WC Pay Item
1364 --Parameter :
1365 --IN : p_shipment_line_id IN NUMBER Required
1366 -- corresponds to the column SHIPMENT_LINE_ID in
1367 -- the table RCV_SHIPMENT_LINES.
1368 --OUT : x_return_status OUT VARCHAR2
1369 -- return status of the procedure.
1370 -- x_return_msg OUT VARCHAR2
1371 -- return message of the procedure.
1372 -----------------------------------------------------------------------------
1373
1374 PROCEDURE CANCEL_WC_PAY_ITEM
1375 (
1376 p_shipment_line_id IN NUMBER,
1377 x_return_status OUT nocopy VARCHAR2,
1378 x_return_msg OUT nocopy VARCHAR2
1379 )
1380
1381 IS
1382
1383 l_group_id NUMBER;
1384 l_row_id VARCHAR2(200);
1385 l_interface_transaction_id NUMBER;
1386
1387 l_po_header_id NUMBER;
1388 l_po_release_id NUMBER;
1389 l_po_line_id NUMBER;
1390 l_shipment_header_id NUMBER;
1391 l_po_line_location_id NUMBER;
1392 l_deliver_to_location_id NUMBER;
1393 l_to_organization_id NUMBER;
1394 l_item_id NUMBER;
1395 l_quantity_shipped NUMBER;
1396 l_source_document_code VARCHAR2(25);
1397 l_category_id NUMBER;
1398 l_unit_of_measure VARCHAR2(25);
1399 l_item_description VARCHAR2(240);
1400 l_employee_id NUMBER;
1401 l_destination_type_code VARCHAR2(25);
1402 l_destination_context VARCHAR2(30);
1403 l_subinventory VARCHAR2(10);
1404 l_routing_header_id NUMBER;
1405 l_primary_unit_of_measure VARCHAR2(25);
1406 l_ship_to_location_id NUMBER;
1407 l_vendor_id NUMBER;
1408 l_org_id NUMBER; --for MOAC
1409
1410 --WC parameters
1411 l_matching_basis VARCHAR2(20);
1412 l_amount_shipped NUMBER;
1413 l_requested_amount NUMBER;
1414 l_material_stored_amount NUMBER;
1415
1416 BEGIN
1417
1418 SELECT rcv_interface_groups_s.nextval
1419 INTO l_group_id
1420 FROM dual;
1421
1422
1423 BEGIN
1424 SELECT
1425 RSL.PO_HEADER_ID,
1426 RSL.PO_RELEASE_ID,
1427 RSL.PO_LINE_ID,
1428 RSL.SHIPMENT_HEADER_ID,
1429 RSL.PO_LINE_LOCATION_ID,
1430 RSL.DELIVER_TO_LOCATION_ID,
1431 RSL.TO_ORGANIZATION_ID,
1432 RSL.ITEM_ID,
1433 RSL.QUANTITY_SHIPPED,
1434 RSL.SOURCE_DOCUMENT_CODE,
1435 RSL.CATEGORY_ID,
1436 RSL.UNIT_OF_MEASURE,
1437 RSL.ITEM_DESCRIPTION,
1438 RSL.EMPLOYEE_ID,
1439 RSL.DESTINATION_TYPE_CODE,
1440 RSL.DESTINATION_CONTEXT,
1441 RSL.TO_SUBINVENTORY,
1442 RSL.ROUTING_HEADER_ID,
1443 RSL.PRIMARY_UNIT_OF_MEASURE,
1444 RSL.SHIP_TO_LOCATION_ID,
1445
1446 RSL.AMOUNT_SHIPPED,
1447 RSL.REQUESTED_AMOUNT,
1448 RSL.MATERIAL_STORED_AMOUNT,
1449 POLL.MATCHING_BASIS,
1450 POLL.ORG_ID
1451 INTO
1452 l_po_header_id,
1453 l_po_release_id,
1454 l_po_line_id,
1455 l_shipment_header_id,
1456 l_po_line_location_id,
1457 l_deliver_to_location_id,
1458 l_to_organization_id,
1459 l_item_id,
1460 l_quantity_shipped,
1461 l_source_document_code,
1462 l_category_id,
1463 l_unit_of_measure,
1464 l_item_description,
1465 l_employee_id,
1466 l_destination_type_code,
1467 l_destination_context,
1468 l_subinventory,
1469 l_routing_header_id,
1470 l_primary_unit_of_measure,
1471 l_ship_to_location_id,
1472 l_amount_shipped,
1473 l_requested_amount,
1474 l_material_stored_amount,
1475 l_matching_basis,
1476 l_org_id
1477 FROM
1478 RCV_SHIPMENT_LINES RSL,
1479 PO_LINE_LOCATIONS_ALL POLL
1480 WHERE
1481 RSL.shipment_line_id = p_shipment_line_id AND
1482 RSL.po_line_location_id = POLL.line_location_id;
1483 EXCEPTION
1484 WHEN NO_DATA_FOUND THEN
1485 x_return_msg := 'NO_DATA_FOUND error in CANCEL_WC_PAY_ITEM query,'||
1486 'shipment_line_id =' || p_shipment_line_id;
1487 RAISE;
1488 END;
1489
1490 RCV_ASN_INTERFACE_TRX_INS_PKG.INSERT_ROW(
1491 x_group_id => l_group_id,
1492 x_transaction_type => 'CANCEL',
1493 x_transaction_date => sysdate,
1494 x_processing_status_code => 'PENDING',
1495 x_processing_mode_code => 'BATCH',
1496 x_transaction_status_code => 'PENDING',
1497 x_last_update_date => SYSDATE,
1498 x_last_updated_by => 1,
1499 x_last_update_login => 1,
1500 x_interface_source_code =>'RCV',
1501 x_creation_date => SYSDATE,
1502 x_created_by => 1,
1503 x_auto_transact_code => 'CANCEL',
1504 x_receipt_source_code => 'VENDOR',
1505
1506 -- Parameters whose values should be retrieved from the shipment table
1507 X_po_header_id => l_po_header_id,
1508 X_po_release_id => l_po_release_id,
1509 X_po_line_id => l_po_line_id,
1510 X_shipment_line_id => p_shipment_line_id,
1511 X_shipment_header_id => l_shipment_header_id,
1512 X_po_line_location_id => l_po_line_location_id,
1513 X_deliver_to_location_id => l_deliver_to_location_id,
1514 X_to_organization_id => l_to_organization_id,
1515 X_item_id => null, -- l_item_id,
1516 X_quantity_shipped => l_quantity_shipped,
1517 X_source_document_code => l_source_document_code,
1518 X_category_id => l_category_id,
1519 X_unit_of_measure => l_unit_of_measure,
1520 X_item_description => l_item_description,
1521 X_employee_id => l_employee_id,
1522 X_destination_type_code => l_destination_type_code,
1523 X_destination_context => l_destination_context,
1524 X_subinventory => l_subinventory,
1525 X_routing_header_id => l_routing_header_id,
1526 X_primary_unit_of_measure=> l_primary_unit_of_measure,
1527 X_ship_to_location_id => l_ship_to_location_id,
1528 X_vendor_id => l_vendor_id,
1529
1530 -- Added the following new columns for complex work
1531 P_MATCHING_BASIS => l_matching_basis,
1532 P_AMOUNT_SHIPPED => l_amount_shipped,
1533 P_REQUESTED_AMOUNT => l_requested_amount,
1534 P_MATERIAL_STORED_AMOUNT => l_material_stored_amount,
1535 P_ORG_ID => l_org_id,
1536
1537 -- Rest of the required parameters from API. Pass in null for all of them
1538 X_ROWID => l_row_id,
1539 X_INTERFACE_TRANSACTION_ID => l_interface_transaction_id,
1540 X_REQUEST_ID => null,
1541 X_PROGRAM_APPLICATION_ID => null,
1542 X_PROGRAM_ID => null,
1543 X_PROGRAM_UPDATE_DATE => null,
1544 X_PROCESSING_REQUEST_ID => null,
1545 X_QUANTITY => null,
1546 X_INTERFACE_SOURCE_LINE_ID => null,
1547 X_INV_TRANSACTION_ID => null,
1548 X_ITEM_REVISION => null,
1549 X_UOM_CODE => null,
1550 X_PRIMARY_QUANTITY => null,
1551 X_VENDOR_SITE_ID => null,
1552 X_FROM_ORGANIZATION_ID => null,
1553 X_FROM_SUBINVENTORY => null,
1554 X_INTRANSIT_OWNING_ORG_ID => null,
1555 X_ROUTING_STEP_ID => null,
1556 X_PARENT_TRANSACTION_ID => null,
1557 X_PO_REVISION_NUM => null,
1558 X_PO_UNIT_PRICE => null,
1559 X_CURRENCY_CODE => null,
1560 X_CURRENCY_CONVERSION_TYPE => null,
1561 X_CURRENCY_CONVERSION_RATE => null,
1562 X_CURRENCY_CONVERSION_DATE => null,
1563 X_PO_DISTRIBUTION_ID => null,
1564 X_REQUISITION_LINE_ID => null,
1565 X_REQ_DISTRIBUTION_ID => null,
1566 X_CHARGE_ACCOUNT_ID => null,
1567 X_SUBSTITUTE_UNORDERED_CODE => null,
1568 X_RECEIPT_EXCEPTION_FLAG => null,
1569 X_ACCRUAL_STATUS_CODE => null,
1570 X_INSPECTION_STATUS_CODE => null,
1571 X_INSPECTION_QUALITY_CODE => null,
1572 X_DELIVER_TO_PERSON_ID => null,
1573 X_LOCATION_ID => null,
1574 X_LOCATOR_ID => null,
1575 X_WIP_ENTITY_ID => null,
1576 X_WIP_LINE_ID => null,
1577 X_DEPARTMENT_CODE => null,
1578 X_WIP_REPETITIVE_SCHEDULE_ID => null,
1579 X_WIP_OPERATION_SEQ_NUM => null,
1580 X_WIP_RESOURCE_SEQ_NUM => null,
1581 X_BOM_RESOURCE_ID => null,
1582 X_SHIPMENT_NUM => null,
1583 X_FREIGHT_CARRIER_CODE => null,
1584 X_BILL_OF_LADING => null,
1585 X_PACKING_SLIP => null,
1586 X_SHIPPED_DATE => null,
1587 X_EXPECTED_RECEIPT_DATE => null,
1588 X_ACTUAL_COST => null,
1589 X_TRANSFER_COST => null,
1590 X_TRANSPORTATION_COST => null,
1591 X_TRANSPORTATION_ACCOUNT_ID => null,
1592 X_NUM_OF_CONTAINERS => null,
1593 X_WAYBILL_AIRBILL_NUM => null,
1594 X_VENDOR_ITEM_NUM => null,
1595 X_VENDOR_LOT_NUM => null,
1596 X_RMA_REFERENCE => null,
1597 X_COMMENTS => null,
1598 X_ATTRIBUTE_CATEGORY => null,
1599 X_ATTRIBUTE1 => null,
1600 X_ATTRIBUTE2 => null,
1601 X_ATTRIBUTE3 => null,
1602 X_ATTRIBUTE4 => null,
1603 X_ATTRIBUTE5 => null,
1604 X_ATTRIBUTE6 => null,
1605 X_ATTRIBUTE7 => null,
1606 X_ATTRIBUTE8 => null,
1607 X_ATTRIBUTE9 => null,
1608 X_ATTRIBUTE10 => null,
1609 X_ATTRIBUTE11 => null,
1610 X_ATTRIBUTE12 => null,
1611 X_ATTRIBUTE13 => null,
1612 X_ATTRIBUTE14 => null,
1613 X_ATTRIBUTE15 => null,
1614 X_SHIP_HEAD_ATTRIBUTE_CATEGORY => null,
1615 X_SHIP_HEAD_ATTRIBUTE1 => null,
1616 X_SHIP_HEAD_ATTRIBUTE2 => null,
1617 X_SHIP_HEAD_ATTRIBUTE3 => null,
1618 X_SHIP_HEAD_ATTRIBUTE4 => null,
1619 X_SHIP_HEAD_ATTRIBUTE5 => null,
1620 X_SHIP_HEAD_ATTRIBUTE6 => null,
1621 X_SHIP_HEAD_ATTRIBUTE7 => null,
1622 X_SHIP_HEAD_ATTRIBUTE8 => null,
1623 X_SHIP_HEAD_ATTRIBUTE9 => null,
1624 X_SHIP_HEAD_ATTRIBUTE10 => null,
1625 X_SHIP_HEAD_ATTRIBUTE11 => null,
1626 X_SHIP_HEAD_ATTRIBUTE12 => null,
1627 X_SHIP_HEAD_ATTRIBUTE13 => null,
1628 X_SHIP_HEAD_ATTRIBUTE14 => null,
1629 X_SHIP_HEAD_ATTRIBUTE15 => null,
1630 X_SHIP_LINE_ATTRIBUTE_CATEGORY => null,
1631 X_SHIP_LINE_ATTRIBUTE1 => null,
1632 X_SHIP_LINE_ATTRIBUTE2 => null,
1633 X_SHIP_LINE_ATTRIBUTE3 => null,
1634 X_SHIP_LINE_ATTRIBUTE4 => null,
1635 X_SHIP_LINE_ATTRIBUTE5 => null,
1636 X_SHIP_LINE_ATTRIBUTE6 => null,
1637 X_SHIP_LINE_ATTRIBUTE7 => null,
1638 X_SHIP_LINE_ATTRIBUTE8 => null,
1639 X_SHIP_LINE_ATTRIBUTE9 => null,
1640 X_SHIP_LINE_ATTRIBUTE10 => null,
1641 X_SHIP_LINE_ATTRIBUTE11 => null,
1642 X_SHIP_LINE_ATTRIBUTE12 => null,
1643 X_SHIP_LINE_ATTRIBUTE13 => null,
1644 X_SHIP_LINE_ATTRIBUTE14 => null,
1645 X_SHIP_LINE_ATTRIBUTE15 => null,
1646 X_USSGL_TRANSACTION_CODE => null,
1647 X_GOVERNMENT_CONTEXT => null,
1648 X_REASON_ID => null,
1649 X_SOURCE_DOC_QUANTITY => null,
1650 X_SOURCE_DOC_UNIT_OF_MEASURE => null,
1651 X_MOVEMENT_ID => null,
1652 X_HEADER_INTERFACE_ID => null,
1653 X_VENDOR_CUM_SHIPPED_QTY => null,
1654 X_ITEM_NUM => null,
1655 X_DOCUMENT_NUM => null,
1656 X_DOCUMENT_LINE_NUM => null,
1657 X_TRUCK_NUM => null,
1658 X_SHIP_TO_LOCATION_CODE => null,
1659 X_CONTAINER_NUM => null,
1660 X_SUBSTITUTE_ITEM_NUM => null,
1661 X_NOTICE_UNIT_PRICE => null,
1662 X_ITEM_CATEGORY => null,
1663 X_LOCATION_CODE => null,
1664 X_VENDOR_NAME => null,
1665 X_VENDOR_NUM => null,
1666 X_VENDOR_SITE_CODE => null,
1667 X_FROM_ORGANIZATION_CODE => null,
1668 X_TO_ORGANIZATION_CODE => null,
1669 X_INTRANSIT_OWNING_ORG_CODE => null,
1670 X_ROUTING_CODE => null,
1671 X_ROUTING_STEP => null,
1672 X_RELEASE_NUM => null,
1673 X_DOCUMENT_SHIPMENT_LINE_NUM => null,
1674 X_DOCUMENT_DISTRIBUTION_NUM => null,
1675 X_DELIVER_TO_PERSON_NAME => null,
1676 X_DELIVER_TO_LOCATION_CODE => null,
1677 X_USE_MTL_LOT => null,
1678 X_USE_MTL_SERIAL => null,
1679 X_LOCATOR => null,
1680 X_REASON_NAME => null,
1681 X_VALIDATION_FLAG => null,
1682 X_SUBSTITUTE_ITEM_ID => null,
1683 X_QUANTITY_INVOICED => null,
1684 X_TAX_NAME => null,
1685 X_TAX_AMOUNT => null,
1686 X_REQ_NUM => null,
1687 X_REQ_LINE_NUM => null,
1688 X_REQ_DISTRIBUTION_NUM => null,
1689 X_WIP_ENTITY_NAME => null,
1690 X_WIP_LINE_CODE => null,
1691 X_RESOURCE_CODE => null,
1692 X_SHIPMENT_LINE_STATUS_CODE => null,
1693 X_BARCODE_LABEL => null,
1694 X_COUNTRY_OF_ORIGIN_CODE => null,
1695 X_FROM_LOCATOR_ID => null,
1696 X_QA_COLLECTION_ID => null,
1697 X_OE_ORDER_HEADER_ID => null,
1698 X_OE_ORDER_LINE_ID => null,
1699 X_CUSTOMER_ID => null,
1700 X_CUSTOMER_SITE_ID => null,
1701 X_CUSTOMER_ITEM_NUM => null,
1702 X_CREATE_DEBIT_MEMO_FLAG => null,
1703 X_PUT_AWAY_RULE_ID => null,
1704 X_PUT_AWAY_STRATEGY_ID => null,
1705 X_LPN_ID => null,
1706 X_TRANSFER_LPN_ID => null,
1707 X_COST_GROUP_ID => null,
1708 X_MOBILE_TXN => null,
1709 X_MMTT_TEMP_ID => null,
1710 X_TRANSFER_COST_GROUP_ID => null,
1711 X_SECONDARY_QUANTITY => null,
1712 X_SECONDARY_UNIT_OF_MEASURE => null,
1713 X_SECONDARY_UOM_CODE => null,
1714 X_QC_GRADE => null,
1715 X_OE_ORDER_NUM => null,
1716 X_OE_ORDER_LINE_NUM => null,
1717 X_CUSTOMER_ACCOUNT_NUMBER => null,
1718 X_CUSTOMER_PARTY_NAME => null,
1719 X_SOURCE_TRANSACTION_NUM => null,
1720 X_PARENT_SOURCE_TXN_NUM => null,
1721 X_PARENT_INTERFACE_TXN_ID => null,
1722 X_CUSTOMER_ITEM_ID => null,
1723 X_INTERFACE_AVAIL_QTY => null,
1724 X_INTERFACE_TRANS_QTY => null,
1725 X_FROM_LOCATOR => null,
1726 X_LPN_GROUP_ID => null,
1727 X_ORDER_TRANSACTION_ID => null,
1728 X_LICENSE_PLATE_NUMBER => null,
1729 X_TFR_LICENSE_PLATE_NUMBER => null,
1730 X_AMOUNT => null,
1731 X_JOB_ID => null,
1732 X_PROJECT_ID => null,
1733 X_TASK_ID => null,
1734 X_ASN_ATTACH_ID => null,
1735 X_TIMECARD_ID => null,
1736 X_TIMECARD_OVN => null,
1737 X_INTERFACE_AVAIL_AMT => null,
1738 X_INTERFACE_TRANS_AMT => null);
1739
1740 x_return_status := FND_API.G_RET_STS_SUCCESS;
1741
1742 EXCEPTION
1743 WHEN OTHERS then null;
1744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1745 END;
1746
1747 -----------------------------------------------------------------------------
1748 --API name : DELETE_WC
1749 --TYPE : PUBLIC
1750 --Function : Delete a WC
1751 --Parameter :
1752 --IN : p_wc_id IN NUMBER Required
1753 -- corresponds to the column HEADER_INTERFACE_ID in
1754 -- the table RCV_HEADERS_INTERFACE.
1755 --OUT : x_return_status OUT VARCHAR2
1756 -- return status of the procedure.
1757 -- x_return_msg OUT VARCHAR2
1758 -- return message of the procedure.
1759 -----------------------------------------------------------------------------
1760
1761 PROCEDURE DELETE_WC
1762 (
1763 p_wc_id IN NUMBER,
1764 x_return_status OUT nocopy VARCHAR2,
1765 x_return_msg OUT nocopy VARCHAR2
1766 )
1767
1768 IS
1769
1770 l_header_interface_id NUMBER;
1771 l_wc_attach_id NUMBER;
1772 l_return_status VARCHAR2(1);
1773 l_msg_count NUMBER;
1774 l_msg_data VARCHAR2(2400);
1775
1776 CURSOR l_header_interface_csr
1777 IS
1778 SELECT HEADER_INTERFACE_ID
1779 FROM RCV_HEADERS_INTERFACE
1780 WHERE HEADER_INTERFACE_ID = p_wc_id;
1781
1782
1783 --Before removing the WC from the interface tables,
1784 --the associated attachments will be first removed.
1785
1786 CURSOR l_wc_attach_csr (l_header_intf_id NUMBER)
1787 IS
1788 SELECT DISTINCT RTI.ASN_ATTACH_ID
1789 FROM RCV_TRANSACTIONS_INTERFACE RTI,
1790 FND_ATTACHED_DOCUMENTS FAD
1791 WHERE RTI.HEADER_INTERFACE_ID = L_HEADER_INTF_ID
1792 AND RTI.ASN_ATTACH_ID IS NOT NULL
1793 AND TO_CHAR(RTI.ASN_ATTACH_ID) = FAD.PK1_VALUE
1794 AND FAD.ENTITY_NAME = 'ASN_ATTACH';
1795
1796 BEGIN
1797
1798 OPEN l_header_interface_csr;
1799 LOOP
1800 FETCH l_header_interface_csr INTO l_header_interface_id;
1801 EXIT WHEN l_header_interface_csr%NOTFOUND;
1802
1803 IF(l_header_interface_id is not null) THEN
1804
1805 /* Delete WC attachment if exists. */
1806 OPEN l_wc_attach_csr (l_header_interface_id);
1807 LOOP
1808 FETCH l_wc_attach_csr INTO l_wc_attach_id;
1809 EXIT WHEN l_wc_attach_csr%NOTFOUND;
1810
1811 IF (l_wc_attach_id IS NOT NULL) THEN
1812
1813 RCV_ASN_ATTACHMENT_PKG.DELETE_LINE_ATTACHMENT
1814 (
1815 p_api_version => 1.0,
1816 p_init_msg_list => 'F',
1817 x_return_status => l_return_status,
1818 x_msg_count => l_msg_count,
1819 x_msg_data => l_msg_data,
1820 p_asn_attach_id => l_wc_attach_id
1821 );
1822
1823 END IF;
1824
1825 END LOOP;
1826 CLOSE l_wc_attach_csr;
1827
1828 /* Delete WC line from interface table. */
1829 DELETE FROM RCV_TRANSACTIONS_INTERFACE
1830 WHERE header_interface_id = l_header_interface_id;
1831
1832 END IF;
1833
1834 END LOOP;
1835 CLOSE l_header_interface_csr;
1836
1837 -- Delete records in header interface table.
1838 DELETE FROM RCV_HEADERS_INTERFACE
1839 WHERE header_interface_id = l_header_interface_id;
1840
1841 COMMIT;
1842 x_return_status := FND_API.G_RET_STS_SUCCESS;
1843
1844 EXCEPTION
1845
1846 WHEN OTHERS THEN
1847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1848 LOG(FND_LOG.LEVEL_UNEXPECTED,'delete_wc',
1849 'Unexpected error occurred');
1850
1851 END DELETE_WC;
1852
1853
1854 -----------------------------------------------------------------------------
1855 --API name : DELETE_WC
1856 --TYPE : PUBLIC
1857 --Function : To retrieve future approvers that have not taken action on
1858 -- a WC yet.
1859 --Parameter :
1860 --IN : p_wc_id IN NUMBER Required
1861 -- corresponds to the column HEADER_INTERFACE_ID in
1862 -- the table RCV_HEADERS_INTERFACE.
1863 --OUT : x_approvers OUT PO_TBL_VARCHAR2000
1864 -- return all "future" approvers
1865 -- x_return_status OUT VARCHAR2
1866 -- return status of the procedure.
1867 -- x_return_msg OUT VARCHAR2
1868 -- return message of the procedure.
1869 -----------------------------------------------------------------------------
1870
1871
1872 PROCEDURE GET_WC_APPROVERS
1873 (
1874 p_wc_id IN NUMBER,
1875 x_approvers OUT nocopy PO_TBL_VARCHAR2000,
1876 x_return_status OUT nocopy VARCHAR2,
1877 x_return_msg OUT nocopy VARCHAR2
1878 )
1879
1880 IS
1881
1882 l_approvers ame_util.approversTable2;
1883 l_completeYNO varchar2(100);
1884 l_num_approvers number;
1885
1886 BEGIN
1887
1888 x_approvers := PO_TBL_VARCHAR2000();
1889
1890
1891 ame_api2.getAllApprovers7(
1892 applicationIdIn => 201,
1893 transactionTypeIn => 'WCAPPRV',
1894 transactionIdIn => p_wc_id,
1895 approvalProcessCompleteYNOut => l_completeYNO,
1896 approversOut => l_approvers);
1897
1898 l_num_approvers := l_approvers.count;
1899
1900 --Only retrieves the "future" approvers
1901 IF (l_completeYNO <> 'Y') THEN
1902 FOR l_count IN 1 .. l_num_approvers LOOP
1903
1904 IF(l_approvers(l_count).approval_status is null) THEN
1905
1906 x_approvers.extend;
1907 x_approvers(x_approvers.count) := l_approvers(l_count).display_name;
1908
1909 END IF;
1910
1911 END LOOP;
1912 END IF;
1913
1914 x_return_status := FND_API.G_RET_STS_SUCCESS;
1915
1916 EXCEPTION
1917
1918 WHEN OTHERS THEN
1919
1920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1921 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_approvers',
1922 'Unexpected error occurred');
1923
1924 END GET_WC_APPROVERS;
1925
1926 END POS_WC_VIEW_CANCEL_PVT;