[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.13.12020000.2 2013/02/09 14:07:32 hvutukur ship $*/
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(round(
135 NVL((POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED,0))
136 *POLL.PRICE_OVERRIDE,(POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0)))
137 ,get_currency_precision(p_po_header_id)))
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' OR x_wc_status = 'CORRECTED') 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', round(NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0),get_currency_precision(p_po_header_id))))
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, Round(RTI.QUANTITY*POLL.PRICE_OVERRIDE,get_currency_precision(poll.po_header_id)))),
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,Round(RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE,get_currency_precision(poll.po_header_id)))),
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 l_wc_lines_corrected NUMBER;
929
930 BEGIN
931
932 --Possible statuses in Interface stage:
933 --DRAFT, PROCESSING, ERROR, CANCEL
934
935
936 --WC in the Interface Tables
937 IF (p_wc_stage = 'INTERFACE') then
938
939 SELECT processing_status_code, transaction_type
940 INTO l_header_processing_status, l_header_transaction_type
941 FROM RCV_HEADERS_INTERFACE
942 WHERE HEADER_INTERFACE_ID = p_wc_id;
943
944 IF(l_header_processing_status = 'DRAFT' and
945 l_header_transaction_type = 'DRAFT') THEN
946
947 x_wc_status := 'DRAFT';
948 x_wc_display_status :=
949 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_DRAFT');
950
951 ELSIF(l_header_processing_status = 'PENDING' and
952 l_header_transaction_type = 'NEW') THEN
953
954 x_wc_status := 'PROCESSING';
955 x_wc_display_status :=
956 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PROCESSING');
957
958 ELSIF(l_header_processing_status = 'RUNNING' and
959 l_header_transaction_type = 'NEW') THEN
960
961 x_wc_status := 'RUNNING';
962 x_wc_display_status :=
963 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_RUNNING');
964
965 ELSIF(l_header_processing_status = 'SUCCESS' and
966 l_header_transaction_type = 'NEW') THEN
967
968 x_wc_status := 'SUCCESS';
969
970
971 ELSIF(l_header_processing_status = 'ERROR' and
972 l_header_transaction_type = 'NEW') THEN
973
974 x_wc_status := 'ERROR';
975 x_wc_display_status :=
976 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_ERROR');
977
978 ELSIF(l_header_processing_status = 'CANCELLED') THEN
979
980 x_wc_status := 'INTERFACE CANCELED';
981 x_wc_display_status := fnd_message_cache.get_string('POS', 'POS_WC_STATUS_CANCELED');
982
983 -- Not handling ERROR and CANCEL yet
984
985 END IF;
986 END IF;
987
988 IF (p_wc_stage = 'SHIPMENT') THEN
989
990
991 l_cancellation_status := GET_WC_CANCELLATION_STATUS(p_wc_id);
992
993 IF (l_cancellation_status = 'NO_CANCELLATION') THEN
994
995 SELECT RSH.APPROVAL_STATUS
996 INTO l_header_approval_status
997 FROM RCV_SHIPMENT_HEADERS RSH
998 WHERE RSH.SHIPMENT_HEADER_ID = p_wc_id;
999
1000 IF(l_header_approval_status is null) THEN
1001
1002 x_wc_status := 'PENDING APPROVAL';
1003 x_wc_display_status :=
1004 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PENDING_APPROVAL');
1005
1006 ELSIF(l_header_approval_status = 'APPROVED') THEN
1007
1008 --Get number of shipment lines
1009 SELECT count(*) into l_wc_total_lines
1010 FROM RCV_SHIPMENT_LINES
1011 WHERE SHIPMENT_HEADER_ID = p_wc_id;
1012
1013 --Get number of the shipment lines that have been processed
1014 SELECT count(*) into l_wc_lines_processed
1015 FROM RCV_TRANSACTIONS
1016 WHERE TRANSACTION_TYPE = 'DELIVER' AND
1017 SHIPMENT_HEADER_ID = p_wc_id;
1018
1019 --Get number of the shipment lines that have been errored out
1020 SELECT count(*) into l_wc_lines_errored
1021 FROM RCV_TRANSACTIONS_INTERFACE RTI,
1022 RCV_SHIPMENT_HEADERS RSH,
1023 RCV_SHIPMENT_LINES RSL
1024 WHERE RTI.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND
1025 RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
1026 RTI.PROCESSING_STATUS_CODE in ('ERROR','COMPLETED') AND
1027 RTI.TRANSACTION_STATUS_CODE = 'ERROR' AND
1028 RSH.SHIPMENT_HEADER_ID = p_wc_id;
1029
1030 -- adding code for bug 9414650 - work confirmation correction ER
1031 -- check for correction on the given work confirmation
1032 SELECT Count(*) INTO l_wc_lines_corrected
1033 FROM rcv_transactions
1034 WHERE transaction_type = 'CORRECT' AND
1035 shipment_header_id = p_wc_id;
1036
1037
1038 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_status',
1039 'Number of correction transactions: '||l_wc_lines_corrected);
1040
1041 -- end of coded added for wc correction ER
1042 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_status',
1043 'Number of Shipment lines: '||l_wc_total_lines);
1044 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_status',
1045 'Number of processed Shipment Lines: '||l_wc_lines_processed);
1046 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_status',
1047 'Number of errored Shipment Lines: '||l_wc_lines_errored);
1048
1049 IF(l_wc_lines_processed = 0 and l_wc_lines_errored = 0) THEN
1050
1051 IF(l_wc_lines_corrected > 0) THEN
1052 x_wc_status := 'CORRECTED';
1053 x_wc_display_status :=
1054 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_CORRECTED');
1055 ELSE
1056 x_wc_status := 'APPROVED';
1057 x_wc_display_status :=
1058 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_APPROVED');
1059 END IF;
1060 /*
1061 -- moving this code block into the ELSE condition - bug 5452504
1062 -- l_wc_lines_processed is equal to the number of distributions,
1063 -- l_wc_total_lines is same as number of shipments, and need not be equal when multiple distributions
1064 ELSIF(l_wc_total_lines = l_wc_lines_processed) THEN
1065 x_wc_status := 'PROCESSED';
1066 x_wc_display_status :=
1067 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PROCESSED');
1068 */
1069 ELSIF(l_wc_lines_errored > 0) THEN
1070 x_wc_status := 'PROCESSING_ERROR';
1071 x_wc_display_status :=
1072 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PROCESS_ERROR');
1073 ELSE
1074
1075 IF(l_wc_lines_corrected > 0) THEN
1076 x_wc_status := 'CORRECTED';
1077 x_wc_display_status :=
1078 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_CORRECTED');
1079 ELSE
1080 x_wc_status := 'PROCESSED';
1081 x_wc_display_status :=
1082 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PROCESSED');
1083 END IF;
1084
1085 END IF;
1086
1087
1088 ELSIF(l_header_approval_status = 'REJECTED') THEN
1089
1090 x_wc_status := 'REJECTED';
1091 x_wc_display_status :=
1092 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_REJECTED');
1093
1094 END IF;
1095
1096 ELSE
1097
1098 IF(l_cancellation_status = 'CANCELED') THEN
1099
1100 x_wc_status := 'SHIPMENT CANCELED';
1101 x_wc_display_status :=
1102 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_CANCELED');
1103
1104 ELSIF(l_cancellation_status = 'PENDING CANCEL') THEN
1105
1106 x_wc_status := 'PENDING CANCEL';
1107 x_wc_display_status :=
1108 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PENDING_CANCEL');
1109
1110 ELSIF(l_cancellation_status = 'PARTIALLY CANCELED') THEN
1111
1112 x_wc_status := 'PARTIALLY CANCELED';
1113 x_wc_display_status :=
1114 fnd_message_cache.get_string('POS', 'POS_WC_STATUS_PARTIALLY_CANCEL');
1115 END IF;
1116
1117 END IF;
1118
1119 END IF;
1120
1121 x_return_status := FND_API.G_RET_STS_SUCCESS;
1122
1123 EXCEPTION
1124
1125 WHEN OTHERS THEN
1126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1127 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_status',
1128 'Unexpected error occurred');
1129
1130 END GET_WC_STATUS;
1131
1132
1133 -----------------------------------------------------------------------------
1134 --API name : GET_WC_CANCELLATION_STATUS
1135 --TYPE : PRIVATE
1136 --Function : To retrieve the cancellation status of a WC
1137 --Parameter :
1138 --IN : p_wc_id IN VARCHAR2 Required
1139 -- corresponds to the column SHIPMENT_NUM in
1140 -- the table RCV_HEADERS_INTERFACE.
1141 --OUT : OUT VARCHAR2 Required
1142 -- returns the cancellation status of the WC
1143 -----------------------------------------------------------------------------
1144
1145 FUNCTION GET_WC_CANCELLATION_STATUS ( p_wc_id NUMBER)
1146 RETURN VARCHAR2 IS
1147
1148 x_total_lines NUMBER := 0;
1149 x_cancelled_lines NUMBER := 0;
1150 x_pending_cancel NUMBER := 0;
1151
1152 BEGIN
1153
1154 /* Get total number of lines */
1155 select count(*)
1156 into x_total_lines
1157 from RCV_SHIPMENT_LINES rsl,
1158 RCV_SHIPMENT_HEADERS rsh
1159 where rsh.shipment_header_id= p_wc_id
1160 and rsh.shipment_header_id = rsl.shipment_header_id;
1161
1162 if (x_total_lines = 0) then
1163 return '';
1164 end if;
1165
1166 /* Get total number of cancelled lines */
1167 select count(*)
1168 into x_cancelled_lines
1169 from RCV_SHIPMENT_LINES rsl,
1170 RCV_SHIPMENT_HEADERS rsh
1171 where rsh.shipment_header_id = p_wc_id
1172 and rsh.shipment_header_id = rsl.shipment_header_id
1173 and rsl.shipment_line_status_code = 'CANCELLED';
1174
1175 /* Get total number of lines pending cancellation */
1176 select count(*)
1177 into x_pending_cancel
1178 from RCV_TRANSACTIONS_INTERFACE rti,
1179 RCV_SHIPMENT_HEADERS rsh
1180 where rti.transaction_type = 'CANCEL'
1181 and rti.shipment_header_id = rsh.shipment_header_id
1182 and rsh.shipment_header_id = p_wc_id;
1183
1184 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_cancellation_status',
1185 'Total Number of lines: '||x_total_lines);
1186 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_cancellation_status',
1187 'Total Number of canceled lines: '||x_cancelled_lines);
1188 LOG(FND_LOG.LEVEL_PROCEDURE,'get_wc_cancellation_status',
1189 'Total Number of lines pending cancelation: '||x_pending_cancel);
1190
1191 if (x_total_lines = x_cancelled_lines) then
1192 return 'CANCELED';
1193 elsif (x_total_lines = x_cancelled_lines + x_pending_cancel) then
1194 return 'PENDING CANCEL';
1195 elsif ((x_total_lines > x_cancelled_lines + x_pending_cancel)
1196 and (x_cancelled_lines + x_pending_cancel > 0)) then
1197 return 'PARTIALLY CANCELED';
1198 else
1199 return 'NO_CANCELLATION';
1200 end if;
1201
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 LOG(FND_LOG.LEVEL_UNEXPECTED,'GET_WC_CANCELLATION_STATUS',
1205 'Unexpected error occurred');
1206
1207 END GET_WC_CANCELLATION_STATUS;
1208
1209
1210 -----------------------------------------------------------------------------
1211 --API name : CANCEL_WC
1212 --TYPE : PUBLIC
1213 --Function : Cancel a WC
1214 --Parameter :
1215 --IN : p_wc_num IN VARCHAR2 Required
1216 -- corresponds to the column SHIPMENT_NUM in
1217 -- the table RCV_HEADERS_INTERFACE.
1218 --IN : p_wc_id IN NUMBER Required
1219 -- corresponds to the column HEADER_INTERFACE_ID in
1220 -- the table RCV_HEADERS_INTERFACE.
1221 -- p_wc_stage IN VARCHAR2 Required
1222 -- indicates if the information in the INTERFACE tables.
1223 -- or the SHIPMENT tables
1224 -- p_po_header_id IN NUMBER Required
1225 -- corresponds to the column PO_HEADER_ID in
1226 -- the table PO_HEADERS_ALL.
1227 --OUT : x_return_status OUT VARCHAR2
1228 -- return status of the procedure.
1229 -- x_return_msg OUT VARCHAR2
1230 -- return message of the procedure.
1231 -----------------------------------------------------------------------------
1232
1233 PROCEDURE CANCEL_WC
1234 (
1235 p_wc_num IN VARCHAR2,
1236 p_wc_id IN NUMBER,
1237 p_wc_status IN VARCHAR2,
1238 p_po_header_id IN NUMBER,
1239 x_return_status OUT NOCOPY VARCHAR2,
1240 x_return_msg OUT NOCOPY VARCHAR2
1241 )
1242
1243 IS
1244
1245 l_api_return_status VARCHAR2(1);
1246 l_api_return_msg VARCHAR2(1000);
1247 l_buyer_id NUMBER;
1248 l_shipment_line_id NUMBER;
1249 NOTIF_ERROR EXCEPTION;
1250 CANCEL_PAY_ITEM_ERROR EXCEPTION;
1251
1252 l_wf_itemtype varchar2(8);
1253 l_wf_itemkey Varchar2(280);
1254
1255 cursor ship_header_cursor(p_header_id number) is
1256 select wf_item_type, wf_item_key
1257 from rcv_shipment_headers
1258 where shipment_header_id = p_header_id;
1259
1260 CURSOR l_wc_pay_item_csr IS
1261 SELECT rsl.shipment_line_id
1262 FROM RCV_SHIPMENT_LINES rsl,
1263 RCV_SHIPMENT_HEADERS rsh
1264 WHERE rsh.SHIPMENT_HEADER_ID = p_wc_id
1265 AND rsh.shipment_header_id = rsl.shipment_header_id;
1266
1267 BEGIN
1268
1269
1270 -- Business rule dictates that only the following WC statuses can
1271 -- be cancelled:
1272 --
1273 -- 1)Processing (interface)
1274 -- 2)Rejected (shipment)
1275 -- 3)Pending Approval (shipment)
1276
1277
1278 -- For 'Processing' WC, RHI.PROCESSING_STATUS_CODE and
1279 -- RTI.PROCESSING_STATUS_CODE will be populated with 'CANCELLED'
1280
1281 -- Should put business logic here to make sure we can cancel the WC
1282
1283 IF(p_wc_status = 'PROCESSING') THEN
1284
1285 UPDATE RCV_TRANSACTIONS_INTERFACE
1286 SET PROCESSING_STATUS_CODE = 'CANCELLED'
1287 WHERE HEADER_INTERFACE_ID = p_wc_id;
1288
1289
1290 UPDATE RCV_HEADERS_INTERFACE
1291 SET PROCESSING_STATUS_CODE = 'CANCELLED'
1292 WHERE HEADER_INTERFACE_ID = p_wc_id;
1293
1294
1295
1296 END IF;
1297
1298
1299 -- For 'Pending Approval' and 'Rejected' WC, a new RTI will be created
1300 -- for every RSL line to be cancelled. The status of the WC will be
1301 -- shown as 'PENDING CANCEL' until the WC is processed by the RTP.
1302 -- At the point, the WC will be shown as 'CANCELLED'
1303
1304 IF(p_wc_status = 'REJECTED' OR p_wc_status = 'PENDING APPROVAL') THEN
1305
1306 OPEN l_wc_pay_item_csr;
1307 LOOP
1308 FETCH l_wc_pay_item_csr INTO l_shipment_line_id;
1309 EXIT WHEN l_wc_pay_item_csr%NOTFOUND;
1310 CANCEL_WC_PAY_ITEM(l_shipment_line_id,
1311 l_api_return_status,
1312 l_api_return_msg);
1313 IF(l_api_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1314 RAISE CANCEL_PAY_ITEM_ERROR;
1315 END IF;
1316 END LOOP;
1317 CLOSE l_wc_pay_item_csr;
1318
1319 --Cancellation Notification to the Buyer
1320
1321 --First get buyer id from PO Header
1322 SELECT POH.AGENT_ID
1323 INTO l_buyer_id
1324 FROM PO_HEADERS_ALL POH
1325 WHERE POH.PO_HEADER_ID = p_po_header_id;
1326
1327 --Debug Information
1328 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','Cancellation Notif parameters:');
1329 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','WC Num:'||p_wc_num);
1330 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','WC ID:'||p_wc_id);
1331 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','WC Status:'||p_wc_status);
1332 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','PO Header ID:'||p_po_header_id);
1333 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','Buyer ID:'||l_buyer_id);
1334 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','User ID:'||fnd_global.user_id);
1335
1336 --Close previous 'requires approval' notif sent to buyer
1337 open ship_header_cursor(p_wc_id);
1338 fetch ship_header_cursor into l_wf_itemtype, l_wf_itemkey;
1339 close ship_header_cursor;
1340
1341 /* Bug 7668094 - Start
1342 Complete Activity Throws Exception if the process is not open.
1343 Hence catched the exception to complete the normal flow.
1344 */
1345 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','l_wf_itemtype:'||l_wf_itemtype);
1346 LOG(FND_LOG.LEVEL_PROCEDURE,'cancel_wc','l_wf_itemkey:'||l_wf_itemkey);
1347 BEGIN
1348 WF_ENGINE.CompleteActivity(l_wf_itemtype, l_wf_itemkey,'WC_APPROVE', 'Cancel');
1349 EXCEPTION WHEN OTHERS THEN
1350 LOG(FND_LOG.LEVEL_UNEXPECTED,'cancel_wc','Could Not Completing activity');
1351 END;
1352 -- Bug 7668094 - Start
1353
1354 POS_ASN_NOTIF.GENERATE_WC_NOTIF
1355 (
1356 p_wc_num => p_wc_num,
1357 p_wc_id => p_wc_id,
1358 p_wc_status => p_wc_status,
1359 p_po_header_id => p_po_header_id,
1360 p_buyer_id => l_buyer_id,
1361 p_user_id => fnd_global.user_id,
1362 x_return_status => l_api_return_status,
1363 x_return_msg => l_api_return_msg
1364 );
1365
1366 IF(l_api_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1367 RAISE NOTIF_ERROR;
1368 END IF;
1369
1370 END IF;
1371
1372 COMMIT;
1373
1374 x_return_status := FND_API.G_RET_STS_SUCCESS;
1375
1376 EXCEPTION
1377
1378 WHEN CANCEL_PAY_ITEM_ERROR THEN
1379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1380 LOG(FND_LOG.LEVEL_UNEXPECTED,'cancel_wc',
1381 'Unexpected error occurred when cancelling pay item');
1382 WHEN NOTIF_ERROR THEN
1383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1384 LOG(FND_LOG.LEVEL_UNEXPECTED,'cancel_wc',
1385 'Unexpected error occurred when sending buyer a cancellation notification');
1386 LOG(FND_LOG.LEVEL_UNEXPECTED,'cancel_wc',
1387 'Error Message: '||l_api_return_msg);
1388
1389 WHEN OTHERS THEN
1390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1391 LOG(FND_LOG.LEVEL_UNEXPECTED,'cancel_wc',
1392 'Unexpected error occurred');
1393
1394
1395 END CANCEL_WC;
1396
1397
1398 -----------------------------------------------------------------------------
1399 --API name : CANCEL_WC_PAY_ITEM
1400 --TYPE : PUBLIC
1401 --Function : Cancel a WC Pay Item
1402 --Parameter :
1403 --IN : p_shipment_line_id IN NUMBER Required
1404 -- corresponds to the column SHIPMENT_LINE_ID in
1405 -- the table RCV_SHIPMENT_LINES.
1406 --OUT : x_return_status OUT VARCHAR2
1407 -- return status of the procedure.
1408 -- x_return_msg OUT VARCHAR2
1409 -- return message of the procedure.
1410 -----------------------------------------------------------------------------
1411
1412 PROCEDURE CANCEL_WC_PAY_ITEM
1413 (
1414 p_shipment_line_id IN NUMBER,
1415 x_return_status OUT nocopy VARCHAR2,
1416 x_return_msg OUT nocopy VARCHAR2
1417 )
1418
1419 IS
1420
1421 l_group_id NUMBER;
1422 l_row_id VARCHAR2(200);
1423 l_interface_transaction_id NUMBER;
1424
1425 l_po_header_id NUMBER;
1426 l_po_release_id NUMBER;
1427 l_po_line_id NUMBER;
1428 l_shipment_header_id NUMBER;
1429 l_po_line_location_id NUMBER;
1430 l_deliver_to_location_id NUMBER;
1431 l_to_organization_id NUMBER;
1432 l_item_id NUMBER;
1433 l_quantity_shipped NUMBER;
1434 l_source_document_code VARCHAR2(25);
1435 l_category_id NUMBER;
1436 l_unit_of_measure VARCHAR2(25);
1437 l_item_description VARCHAR2(240);
1438 l_employee_id NUMBER;
1439 l_destination_type_code VARCHAR2(25);
1440 l_destination_context VARCHAR2(30);
1441 l_subinventory VARCHAR2(10);
1442 l_routing_header_id NUMBER;
1443 l_primary_unit_of_measure VARCHAR2(25);
1444 l_ship_to_location_id NUMBER;
1445 l_vendor_id NUMBER;
1446 l_org_id NUMBER; --for MOAC
1447
1448 --WC parameters
1449 l_matching_basis VARCHAR2(20);
1450 l_amount_shipped NUMBER;
1451 l_requested_amount NUMBER;
1452 l_material_stored_amount NUMBER;
1453
1454 BEGIN
1455
1456 SELECT rcv_interface_groups_s.nextval
1457 INTO l_group_id
1458 FROM dual;
1459
1460
1461 BEGIN
1462 SELECT
1463 RSL.PO_HEADER_ID,
1464 RSL.PO_RELEASE_ID,
1465 RSL.PO_LINE_ID,
1466 RSL.SHIPMENT_HEADER_ID,
1467 RSL.PO_LINE_LOCATION_ID,
1468 RSL.DELIVER_TO_LOCATION_ID,
1469 RSL.TO_ORGANIZATION_ID,
1470 RSL.ITEM_ID,
1471 RSL.QUANTITY_SHIPPED,
1472 RSL.SOURCE_DOCUMENT_CODE,
1473 RSL.CATEGORY_ID,
1474 RSL.UNIT_OF_MEASURE,
1475 RSL.ITEM_DESCRIPTION,
1476 RSL.EMPLOYEE_ID,
1477 RSL.DESTINATION_TYPE_CODE,
1478 RSL.DESTINATION_CONTEXT,
1479 RSL.TO_SUBINVENTORY,
1480 RSL.ROUTING_HEADER_ID,
1481 RSL.PRIMARY_UNIT_OF_MEASURE,
1482 RSL.SHIP_TO_LOCATION_ID,
1483
1484 RSL.AMOUNT_SHIPPED,
1485 RSL.REQUESTED_AMOUNT,
1486 RSL.MATERIAL_STORED_AMOUNT,
1487 POLL.MATCHING_BASIS,
1488 POLL.ORG_ID
1489 INTO
1490 l_po_header_id,
1491 l_po_release_id,
1492 l_po_line_id,
1493 l_shipment_header_id,
1494 l_po_line_location_id,
1495 l_deliver_to_location_id,
1496 l_to_organization_id,
1497 l_item_id,
1498 l_quantity_shipped,
1499 l_source_document_code,
1500 l_category_id,
1501 l_unit_of_measure,
1502 l_item_description,
1503 l_employee_id,
1504 l_destination_type_code,
1505 l_destination_context,
1506 l_subinventory,
1507 l_routing_header_id,
1508 l_primary_unit_of_measure,
1509 l_ship_to_location_id,
1510 l_amount_shipped,
1511 l_requested_amount,
1512 l_material_stored_amount,
1513 l_matching_basis,
1514 l_org_id
1515 FROM
1516 RCV_SHIPMENT_LINES RSL,
1517 PO_LINE_LOCATIONS_ALL POLL
1518 WHERE
1519 RSL.shipment_line_id = p_shipment_line_id AND
1520 RSL.po_line_location_id = POLL.line_location_id;
1521 EXCEPTION
1522 WHEN NO_DATA_FOUND THEN
1523 x_return_msg := 'NO_DATA_FOUND error in CANCEL_WC_PAY_ITEM query,'||
1524 'shipment_line_id =' || p_shipment_line_id;
1525 RAISE;
1526 END;
1527
1528 RCV_ASN_INTERFACE_TRX_INS_PKG.INSERT_ROW(
1529 x_group_id => l_group_id,
1530 x_transaction_type => 'CANCEL',
1531 x_transaction_date => sysdate,
1532 x_processing_status_code => 'PENDING',
1533 x_processing_mode_code => 'BATCH',
1534 x_transaction_status_code => 'PENDING',
1535 x_last_update_date => SYSDATE,
1536 x_last_updated_by => 1,
1537 x_last_update_login => 1,
1538 x_interface_source_code =>'RCV',
1539 x_creation_date => SYSDATE,
1540 x_created_by => 1,
1541 x_auto_transact_code => 'CANCEL',
1542 x_receipt_source_code => 'VENDOR',
1543
1544 -- Parameters whose values should be retrieved from the shipment table
1545 X_po_header_id => l_po_header_id,
1546 X_po_release_id => l_po_release_id,
1547 X_po_line_id => l_po_line_id,
1548 X_shipment_line_id => p_shipment_line_id,
1549 X_shipment_header_id => l_shipment_header_id,
1550 X_po_line_location_id => l_po_line_location_id,
1551 X_deliver_to_location_id => l_deliver_to_location_id,
1552 X_to_organization_id => l_to_organization_id,
1553 X_item_id => null, -- l_item_id,
1554 X_quantity_shipped => l_quantity_shipped,
1555 X_source_document_code => l_source_document_code,
1556 X_category_id => l_category_id,
1557 X_unit_of_measure => l_unit_of_measure,
1558 X_item_description => l_item_description,
1559 X_employee_id => l_employee_id,
1560 X_destination_type_code => l_destination_type_code,
1561 X_destination_context => l_destination_context,
1562 X_subinventory => l_subinventory,
1563 X_routing_header_id => l_routing_header_id,
1564 X_primary_unit_of_measure=> l_primary_unit_of_measure,
1565 X_ship_to_location_id => l_ship_to_location_id,
1566 X_vendor_id => l_vendor_id,
1567
1568 -- Added the following new columns for complex work
1569 P_MATCHING_BASIS => l_matching_basis,
1570 P_AMOUNT_SHIPPED => l_amount_shipped,
1571 P_REQUESTED_AMOUNT => l_requested_amount,
1572 P_MATERIAL_STORED_AMOUNT => l_material_stored_amount,
1573 P_ORG_ID => l_org_id,
1574
1575 -- Rest of the required parameters from API. Pass in null for all of them
1576 X_ROWID => l_row_id,
1577 X_INTERFACE_TRANSACTION_ID => l_interface_transaction_id,
1578 X_REQUEST_ID => null,
1579 X_PROGRAM_APPLICATION_ID => null,
1580 X_PROGRAM_ID => null,
1581 X_PROGRAM_UPDATE_DATE => null,
1582 X_PROCESSING_REQUEST_ID => null,
1583 X_QUANTITY => null,
1584 X_INTERFACE_SOURCE_LINE_ID => null,
1585 X_INV_TRANSACTION_ID => null,
1586 X_ITEM_REVISION => null,
1587 X_UOM_CODE => null,
1588 X_PRIMARY_QUANTITY => null,
1589 X_VENDOR_SITE_ID => null,
1590 X_FROM_ORGANIZATION_ID => null,
1591 X_FROM_SUBINVENTORY => null,
1592 X_INTRANSIT_OWNING_ORG_ID => null,
1593 X_ROUTING_STEP_ID => null,
1594 X_PARENT_TRANSACTION_ID => null,
1595 X_PO_REVISION_NUM => null,
1596 X_PO_UNIT_PRICE => null,
1597 X_CURRENCY_CODE => null,
1598 X_CURRENCY_CONVERSION_TYPE => null,
1599 X_CURRENCY_CONVERSION_RATE => null,
1600 X_CURRENCY_CONVERSION_DATE => null,
1601 X_PO_DISTRIBUTION_ID => null,
1602 X_REQUISITION_LINE_ID => null,
1603 X_REQ_DISTRIBUTION_ID => null,
1604 X_CHARGE_ACCOUNT_ID => null,
1605 X_SUBSTITUTE_UNORDERED_CODE => null,
1606 X_RECEIPT_EXCEPTION_FLAG => null,
1607 X_ACCRUAL_STATUS_CODE => null,
1608 X_INSPECTION_STATUS_CODE => null,
1609 X_INSPECTION_QUALITY_CODE => null,
1610 X_DELIVER_TO_PERSON_ID => null,
1611 X_LOCATION_ID => null,
1612 X_LOCATOR_ID => null,
1613 X_WIP_ENTITY_ID => null,
1614 X_WIP_LINE_ID => null,
1615 X_DEPARTMENT_CODE => null,
1616 X_WIP_REPETITIVE_SCHEDULE_ID => null,
1617 X_WIP_OPERATION_SEQ_NUM => null,
1618 X_WIP_RESOURCE_SEQ_NUM => null,
1619 X_BOM_RESOURCE_ID => null,
1620 X_SHIPMENT_NUM => null,
1621 X_FREIGHT_CARRIER_CODE => null,
1622 X_BILL_OF_LADING => null,
1623 X_PACKING_SLIP => null,
1624 X_SHIPPED_DATE => null,
1625 X_EXPECTED_RECEIPT_DATE => null,
1626 X_ACTUAL_COST => null,
1627 X_TRANSFER_COST => null,
1628 X_TRANSPORTATION_COST => null,
1629 X_TRANSPORTATION_ACCOUNT_ID => null,
1630 X_NUM_OF_CONTAINERS => null,
1631 X_WAYBILL_AIRBILL_NUM => null,
1632 X_VENDOR_ITEM_NUM => null,
1633 X_VENDOR_LOT_NUM => null,
1634 X_RMA_REFERENCE => null,
1635 X_COMMENTS => null,
1636 X_ATTRIBUTE_CATEGORY => null,
1637 X_ATTRIBUTE1 => null,
1638 X_ATTRIBUTE2 => null,
1639 X_ATTRIBUTE3 => null,
1640 X_ATTRIBUTE4 => null,
1641 X_ATTRIBUTE5 => null,
1642 X_ATTRIBUTE6 => null,
1643 X_ATTRIBUTE7 => null,
1644 X_ATTRIBUTE8 => null,
1645 X_ATTRIBUTE9 => null,
1646 X_ATTRIBUTE10 => null,
1647 X_ATTRIBUTE11 => null,
1648 X_ATTRIBUTE12 => null,
1649 X_ATTRIBUTE13 => null,
1650 X_ATTRIBUTE14 => null,
1651 X_ATTRIBUTE15 => null,
1652 X_SHIP_HEAD_ATTRIBUTE_CATEGORY => null,
1653 X_SHIP_HEAD_ATTRIBUTE1 => null,
1654 X_SHIP_HEAD_ATTRIBUTE2 => null,
1655 X_SHIP_HEAD_ATTRIBUTE3 => null,
1656 X_SHIP_HEAD_ATTRIBUTE4 => null,
1657 X_SHIP_HEAD_ATTRIBUTE5 => null,
1658 X_SHIP_HEAD_ATTRIBUTE6 => null,
1659 X_SHIP_HEAD_ATTRIBUTE7 => null,
1660 X_SHIP_HEAD_ATTRIBUTE8 => null,
1661 X_SHIP_HEAD_ATTRIBUTE9 => null,
1662 X_SHIP_HEAD_ATTRIBUTE10 => null,
1663 X_SHIP_HEAD_ATTRIBUTE11 => null,
1664 X_SHIP_HEAD_ATTRIBUTE12 => null,
1665 X_SHIP_HEAD_ATTRIBUTE13 => null,
1666 X_SHIP_HEAD_ATTRIBUTE14 => null,
1667 X_SHIP_HEAD_ATTRIBUTE15 => null,
1668 X_SHIP_LINE_ATTRIBUTE_CATEGORY => null,
1669 X_SHIP_LINE_ATTRIBUTE1 => null,
1670 X_SHIP_LINE_ATTRIBUTE2 => null,
1671 X_SHIP_LINE_ATTRIBUTE3 => null,
1672 X_SHIP_LINE_ATTRIBUTE4 => null,
1673 X_SHIP_LINE_ATTRIBUTE5 => null,
1674 X_SHIP_LINE_ATTRIBUTE6 => null,
1675 X_SHIP_LINE_ATTRIBUTE7 => null,
1676 X_SHIP_LINE_ATTRIBUTE8 => null,
1677 X_SHIP_LINE_ATTRIBUTE9 => null,
1678 X_SHIP_LINE_ATTRIBUTE10 => null,
1679 X_SHIP_LINE_ATTRIBUTE11 => null,
1680 X_SHIP_LINE_ATTRIBUTE12 => null,
1681 X_SHIP_LINE_ATTRIBUTE13 => null,
1682 X_SHIP_LINE_ATTRIBUTE14 => null,
1683 X_SHIP_LINE_ATTRIBUTE15 => null,
1684 X_USSGL_TRANSACTION_CODE => null,
1685 X_GOVERNMENT_CONTEXT => null,
1686 X_REASON_ID => null,
1687 X_SOURCE_DOC_QUANTITY => null,
1688 X_SOURCE_DOC_UNIT_OF_MEASURE => null,
1689 X_MOVEMENT_ID => null,
1690 X_HEADER_INTERFACE_ID => null,
1691 X_VENDOR_CUM_SHIPPED_QTY => null,
1692 X_ITEM_NUM => null,
1693 X_DOCUMENT_NUM => null,
1694 X_DOCUMENT_LINE_NUM => null,
1695 X_TRUCK_NUM => null,
1696 X_SHIP_TO_LOCATION_CODE => null,
1697 X_CONTAINER_NUM => null,
1698 X_SUBSTITUTE_ITEM_NUM => null,
1699 X_NOTICE_UNIT_PRICE => null,
1700 X_ITEM_CATEGORY => null,
1701 X_LOCATION_CODE => null,
1702 X_VENDOR_NAME => null,
1703 X_VENDOR_NUM => null,
1704 X_VENDOR_SITE_CODE => null,
1705 X_FROM_ORGANIZATION_CODE => null,
1706 X_TO_ORGANIZATION_CODE => null,
1707 X_INTRANSIT_OWNING_ORG_CODE => null,
1708 X_ROUTING_CODE => null,
1709 X_ROUTING_STEP => null,
1710 X_RELEASE_NUM => null,
1711 X_DOCUMENT_SHIPMENT_LINE_NUM => null,
1712 X_DOCUMENT_DISTRIBUTION_NUM => null,
1713 X_DELIVER_TO_PERSON_NAME => null,
1714 X_DELIVER_TO_LOCATION_CODE => null,
1715 X_USE_MTL_LOT => null,
1716 X_USE_MTL_SERIAL => null,
1717 X_LOCATOR => null,
1718 X_REASON_NAME => null,
1719 X_VALIDATION_FLAG => null,
1720 X_SUBSTITUTE_ITEM_ID => null,
1721 X_QUANTITY_INVOICED => null,
1722 X_TAX_NAME => null,
1723 X_TAX_AMOUNT => null,
1724 X_REQ_NUM => null,
1725 X_REQ_LINE_NUM => null,
1726 X_REQ_DISTRIBUTION_NUM => null,
1727 X_WIP_ENTITY_NAME => null,
1728 X_WIP_LINE_CODE => null,
1729 X_RESOURCE_CODE => null,
1730 X_SHIPMENT_LINE_STATUS_CODE => null,
1731 X_BARCODE_LABEL => null,
1732 X_COUNTRY_OF_ORIGIN_CODE => null,
1733 X_FROM_LOCATOR_ID => null,
1734 X_QA_COLLECTION_ID => null,
1735 X_OE_ORDER_HEADER_ID => null,
1736 X_OE_ORDER_LINE_ID => null,
1737 X_CUSTOMER_ID => null,
1738 X_CUSTOMER_SITE_ID => null,
1739 X_CUSTOMER_ITEM_NUM => null,
1740 X_CREATE_DEBIT_MEMO_FLAG => null,
1741 X_PUT_AWAY_RULE_ID => null,
1742 X_PUT_AWAY_STRATEGY_ID => null,
1743 X_LPN_ID => null,
1744 X_TRANSFER_LPN_ID => null,
1745 X_COST_GROUP_ID => null,
1746 X_MOBILE_TXN => null,
1747 X_MMTT_TEMP_ID => null,
1748 X_TRANSFER_COST_GROUP_ID => null,
1749 X_SECONDARY_QUANTITY => null,
1750 X_SECONDARY_UNIT_OF_MEASURE => null,
1751 X_SECONDARY_UOM_CODE => null,
1752 X_QC_GRADE => null,
1753 X_OE_ORDER_NUM => null,
1754 X_OE_ORDER_LINE_NUM => null,
1755 X_CUSTOMER_ACCOUNT_NUMBER => null,
1756 X_CUSTOMER_PARTY_NAME => null,
1757 X_SOURCE_TRANSACTION_NUM => null,
1758 X_PARENT_SOURCE_TXN_NUM => null,
1759 X_PARENT_INTERFACE_TXN_ID => null,
1760 X_CUSTOMER_ITEM_ID => null,
1761 X_INTERFACE_AVAIL_QTY => null,
1762 X_INTERFACE_TRANS_QTY => null,
1763 X_FROM_LOCATOR => null,
1764 X_LPN_GROUP_ID => null,
1765 X_ORDER_TRANSACTION_ID => null,
1766 X_LICENSE_PLATE_NUMBER => null,
1767 X_TFR_LICENSE_PLATE_NUMBER => null,
1768 X_AMOUNT => null,
1769 X_JOB_ID => null,
1770 X_PROJECT_ID => null,
1771 X_TASK_ID => null,
1772 X_ASN_ATTACH_ID => null,
1773 X_TIMECARD_ID => null,
1774 X_TIMECARD_OVN => null,
1775 X_INTERFACE_AVAIL_AMT => null,
1776 X_INTERFACE_TRANS_AMT => null);
1777
1778 x_return_status := FND_API.G_RET_STS_SUCCESS;
1779
1780 EXCEPTION
1781 WHEN OTHERS then null;
1782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1783 END;
1784
1785 -----------------------------------------------------------------------------
1786 --API name : DELETE_WC
1787 --TYPE : PUBLIC
1788 --Function : Delete a WC
1789 --Parameter :
1790 --IN : p_wc_id IN NUMBER Required
1791 -- corresponds to the column HEADER_INTERFACE_ID in
1792 -- the table RCV_HEADERS_INTERFACE.
1793 --OUT : x_return_status OUT VARCHAR2
1794 -- return status of the procedure.
1795 -- x_return_msg OUT VARCHAR2
1796 -- return message of the procedure.
1797 -----------------------------------------------------------------------------
1798
1799 PROCEDURE DELETE_WC
1800 (
1801 p_wc_id IN NUMBER,
1802 x_return_status OUT nocopy VARCHAR2,
1803 x_return_msg OUT nocopy VARCHAR2
1804 )
1805
1806 IS
1807
1808 l_header_interface_id NUMBER;
1809 l_wc_attach_id NUMBER;
1810 l_return_status VARCHAR2(1);
1811 l_msg_count NUMBER;
1812 l_msg_data VARCHAR2(2400);
1813
1814 CURSOR l_header_interface_csr
1815 IS
1816 SELECT HEADER_INTERFACE_ID
1817 FROM RCV_HEADERS_INTERFACE
1818 WHERE HEADER_INTERFACE_ID = p_wc_id;
1819
1820
1821 --Before removing the WC from the interface tables,
1822 --the associated attachments will be first removed.
1823
1824 CURSOR l_wc_attach_csr (l_header_intf_id NUMBER)
1825 IS
1826 SELECT DISTINCT RTI.ASN_ATTACH_ID
1827 FROM RCV_TRANSACTIONS_INTERFACE RTI,
1828 FND_ATTACHED_DOCUMENTS FAD
1829 WHERE RTI.HEADER_INTERFACE_ID = L_HEADER_INTF_ID
1830 AND RTI.ASN_ATTACH_ID IS NOT NULL
1831 AND TO_CHAR(RTI.ASN_ATTACH_ID) = FAD.PK1_VALUE
1832 AND FAD.ENTITY_NAME = 'ASN_ATTACH';
1833
1834 BEGIN
1835
1836 OPEN l_header_interface_csr;
1837 LOOP
1838 FETCH l_header_interface_csr INTO l_header_interface_id;
1839 EXIT WHEN l_header_interface_csr%NOTFOUND;
1840
1841 IF(l_header_interface_id is not null) THEN
1842
1843 /* Delete WC attachment if exists. */
1844 OPEN l_wc_attach_csr (l_header_interface_id);
1845 LOOP
1846 FETCH l_wc_attach_csr INTO l_wc_attach_id;
1847 EXIT WHEN l_wc_attach_csr%NOTFOUND;
1848
1849 IF (l_wc_attach_id IS NOT NULL) THEN
1850
1851 RCV_ASN_ATTACHMENT_PKG.DELETE_LINE_ATTACHMENT
1852 (
1853 p_api_version => 1.0,
1854 p_init_msg_list => 'F',
1855 x_return_status => l_return_status,
1856 x_msg_count => l_msg_count,
1857 x_msg_data => l_msg_data,
1858 p_asn_attach_id => l_wc_attach_id
1859 );
1860
1861 END IF;
1862
1863 END LOOP;
1864 CLOSE l_wc_attach_csr;
1865
1866 /* Delete WC line from interface table. */
1867 DELETE FROM RCV_TRANSACTIONS_INTERFACE
1868 WHERE header_interface_id = l_header_interface_id;
1869
1870 END IF;
1871
1872 END LOOP;
1873 CLOSE l_header_interface_csr;
1874
1875 -- Delete records in header interface table.
1876 DELETE FROM RCV_HEADERS_INTERFACE
1877 WHERE header_interface_id = l_header_interface_id;
1878
1879 COMMIT;
1880 x_return_status := FND_API.G_RET_STS_SUCCESS;
1881
1882 EXCEPTION
1883
1884 WHEN OTHERS THEN
1885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1886 LOG(FND_LOG.LEVEL_UNEXPECTED,'delete_wc',
1887 'Unexpected error occurred');
1888
1889 END DELETE_WC;
1890
1891
1892 -----------------------------------------------------------------------------
1893 --API name : DELETE_WC
1894 --TYPE : PUBLIC
1895 --Function : To retrieve future approvers that have not taken action on
1896 -- a WC yet.
1897 --Parameter :
1898 --IN : p_wc_id IN NUMBER Required
1899 -- corresponds to the column HEADER_INTERFACE_ID in
1900 -- the table RCV_HEADERS_INTERFACE.
1901 --OUT : x_approvers OUT PO_TBL_VARCHAR2000
1902 -- return all "future" approvers
1903 -- x_return_status OUT VARCHAR2
1904 -- return status of the procedure.
1905 -- x_return_msg OUT VARCHAR2
1906 -- return message of the procedure.
1907 -----------------------------------------------------------------------------
1908
1909
1910 PROCEDURE GET_WC_APPROVERS
1911 (
1912 p_wc_id IN NUMBER,
1913 x_approvers OUT nocopy PO_TBL_VARCHAR2000,
1914 x_return_status OUT nocopy VARCHAR2,
1915 x_return_msg OUT nocopy VARCHAR2
1916 )
1917
1918 IS
1919
1920 l_approvers ame_util.approversTable2;
1921 l_completeYNO varchar2(100);
1922 l_num_approvers number;
1923
1924 BEGIN
1925
1926 x_approvers := PO_TBL_VARCHAR2000();
1927
1928
1929 ame_api2.getAllApprovers7(
1930 applicationIdIn => 201,
1931 transactionTypeIn => 'WCAPPRV',
1932 transactionIdIn => p_wc_id,
1933 approvalProcessCompleteYNOut => l_completeYNO,
1934 approversOut => l_approvers);
1935
1936 l_num_approvers := l_approvers.count;
1937
1938 --Only retrieves the "future" approvers
1939 IF (l_completeYNO <> 'Y') THEN
1940 FOR l_count IN 1 .. l_num_approvers LOOP
1941
1942 IF(l_approvers(l_count).approval_status is null) THEN
1943
1944 x_approvers.extend;
1945 x_approvers(x_approvers.count) := l_approvers(l_count).display_name;
1946
1947 END IF;
1948
1949 END LOOP;
1950 END IF;
1951
1952 x_return_status := FND_API.G_RET_STS_SUCCESS;
1953
1954 EXCEPTION
1955
1956 WHEN OTHERS THEN
1957
1958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1959 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_approvers',
1960 'Unexpected error occurred');
1961
1962 END GET_WC_APPROVERS;
1963
1964 FUNCTION GET_CURRENCY_PRECISION(p_po_header_id IN NUMBER)
1965 RETURN NUMBER
1966 IS
1967 p_precision NUMBER;
1968 BEGIN
1969 SELECT fc.precision
1970 INTO p_precision
1971 FROM FND_CURRENCIES fc,
1972 PO_HEADERS_ALL poh
1973 WHERE poh.po_header_id=p_po_header_id
1974 AND poh.currency_code= fc.currency_code;
1975
1976 RETURN p_precision;
1977
1978 EXCEPTION
1979 WHEN OTHERS
1980 THEN
1981 p_precision:=0;
1982 RETURN p_precision;
1983 END GET_CURRENCY_PRECISION;
1984
1985 END POS_WC_VIEW_CANCEL_PVT;