[Home] [Help]
PACKAGE BODY: APPS.POS_WC_CREATE_UPDATE_PVT
Source
1 PACKAGE BODY POS_WC_CREATE_UPDATE_PVT AS
2 /* $Header: POSVWCCB.pls 120.9.12020000.3 2013/05/22 12:08:10 nchundur ship $*/
3 l_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 PROCEDURE LOG
6 (
7 p_level IN NUMBER,
8 p_api_name IN VARCHAR2,
9 p_msg IN VARCHAR2
10 );
11
12 PROCEDURE GET_PO_ORDERED
13 (
14 p_po_header_id IN NUMBER,
15 x_po_ordered OUT nocopy NUMBER,
16 x_return_status OUT nocopy VARCHAR2,
17 x_return_msg OUT nocopy VARCHAR2
18 );
19
20
21 PROCEDURE GET_PO_APPROVED
22 (
23 p_po_header_id IN NUMBER,
24 x_po_approved OUT nocopy NUMBER,
25 x_return_status OUT nocopy VARCHAR2,
26 x_return_msg OUT nocopy VARCHAR2
27 );
28
29 --Private procedure for logging
30 PROCEDURE LOG
31 (
32 p_level IN NUMBER,
33 p_api_name IN VARCHAR2,
34 p_msg IN VARCHAR2
35 )
36 IS
37 l_module varchar2(2000);
38 BEGIN
39 /* Taken from Package FND_LOG
40 LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
41 LEVEL_ERROR CONSTANT NUMBER := 5;
42 LEVEL_EXCEPTION CONSTANT NUMBER := 4;
43 LEVEL_EVENT CONSTANT NUMBER := 3;
44 LEVEL_PROCEDURE CONSTANT NUMBER := 2;
45 LEVEL_STATEMENT CONSTANT NUMBER := 1;
46 */
47 IF( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
48 IF(l_fnd_debug = 'Y')THEN
49 l_module := 'pos.plsql.pos_wc_create_update_pvt.'||p_api_name;
50 FND_LOG.string( LOG_LEVEL => p_level,
51 MODULE => l_module,
52 MESSAGE => p_msg);
53 END IF;
54 END IF;
55
56 END LOG;
57
58
59
60 -----------------------------------------------------------------------------
61 --API name : DRAFT_EXISTS_FOR_PO
62 -----------------------------------------------------------------------------
63 FUNCTION DRAFT_EXISTS_FOR_PO ( p_po_header_id IN NUMBER)
64 RETURN VARCHAR2
65
66 IS
67
68 l_draft_exists VARCHAR2(1);
69
70 BEGIN
71
72 l_draft_exists := 'N';
73
74 SELECT 'Y' INTO l_draft_exists
75 FROM RCV_HEADERS_INTERFACE RHI,
76 RCV_TRANSACTIONS_INTERFACE RTI
77 WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
78 RTI.PO_HEADER_ID = p_po_header_id AND
79 RHI.processing_status_code = 'DRAFT' AND
80 RHI.transaction_type = 'DRAFT' AND
81 ROWNUM = 1;
82
83 return l_draft_exists;
84
85 EXCEPTION
86
87 WHEN NO_DATA_FOUND THEN
88
89 return 'N';
90
91 WHEN OTHERS THEN
92 LOG(FND_LOG.LEVEL_UNEXPECTED,'draft_exists_for_po',
93 'Unexpected Error');
94
95 END DRAFT_EXISTS_FOR_PO;
96
97
98 -----------------------------------------------------------------------------
99 --API name : GET_PO_INFO
100 --TYPE : PUBLIC
101 --Function : Retrieve information related to a PO such as ordered amount
102 -- and approved amount
103 --Parameter :
104 --IN : p_po_header_id IN NUMBER Required
105 -- corresponds to the columne PO_HEADER_ID in the table
106 -- PO_HEADERS_ALL, and identifies the PO for which the
107 -- information should be retrieved.
108 --
109 --OUT : x_ordered OUT NUMBER
110 -- total ordered amount for the PO
111 -- x_approved OUT NUMBER
112 -- total approved amount for the PO
113 -- x_return_status OUT VARCHAR2
114 -- return status of the procedure
115 -- x_return_msg OUT VARCHAR2
116 -- return message of the procedure
117 -----------------------------------------------------------------------------
118 PROCEDURE GET_PO_INFO
119 (
120 p_po_header_id IN NUMBER,
121 x_ordered OUT nocopy NUMBER,
122 x_approved OUT nocopy NUMBER,
123 x_return_status OUT nocopy VARCHAR2,
124 x_return_msg OUT nocopy VARCHAR2
125 )
126
127 IS
128
129 l_progress NUMBER;
130 l_api_return_status VARCHAR2(1);
131 l_api_return_msg VARCHAR2(1000);
132
133 BEGIN
134
135 l_progress := 0;
136
137 --We are writing these APIs for now. We might be
138 --able to use APIs provided by the Complex Work PO Project later
139 GET_PO_ORDERED
140 (
141 p_po_header_id => p_po_header_id,
142 x_po_ordered => x_ordered,
143 x_return_status => l_api_return_status,
144 x_return_msg => l_api_return_msg
145 );
146
147 IF(l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
148 RAISE FND_API.G_EXC_ERROR;
149 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
151 END IF;
152
153 l_progress := 1;
154
155 GET_PO_APPROVED
156 (
157 p_po_header_id => p_po_header_id,
158 x_po_approved => x_approved,
159 x_return_status => l_api_return_status,
160 x_return_msg => l_api_return_msg
161 );
162
163 IF(l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
164 RAISE FND_API.G_EXC_ERROR;
165 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167 END IF;
168
169 EXCEPTION
170
171 WHEN FND_API.G_EXC_ERROR THEN
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 x_return_msg := l_api_return_msg;
174 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_po_info',
175 'Expected error at stage: '|| l_progress);
176 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_po_ccinfo',
177 'Error: '|| l_api_return_msg);
178
179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181 x_return_msg := 'Unexpected error at stage: '|| l_progress;
182 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_po_info',
183 'Unexpected error at stage: '|| l_progress);
184
185 WHEN OTHERS THEN
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
187 x_return_msg := 'Unexpected error at stage: '|| l_progress;
188 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_po_info',
189 'Unexpected error at stage: '|| l_progress);
190
191 x_return_status := FND_API.G_RET_STS_SUCCESS;
192
193 END;
194
195 -----------------------------------------------------------------------------
196 --API name : GET_PO_APPROVED
197 --TYPE : PRIVATE
198 --Function : Retrieve approved amount of a PO
199 --Parameter :
200 --IN : p_po_header_id IN NUMBER Required
201 -- corresponds to the columne PO_HEADER_ID in the table
202 -- PO_HEADERS_ALL, and identifies the PO for which the
203 -- information should be retrieved.
204 --
205 --OUT : x_approved OUT NUMBER
206 -- total approved amount for the PO
207 -- x_return_status OUT VARCHAR2
208 -- return status of the procedure
209 -- x_return_msg OUT VARCHAR2
210 -- return message of the procedure
211 -----------------------------------------------------------------------------
212 PROCEDURE GET_PO_APPROVED
213 (
214 p_po_header_id IN NUMBER,
215 x_po_approved OUT nocopy NUMBER,
216 x_return_status OUT nocopy VARCHAR2,
217 x_return_msg OUT nocopy VARCHAR2
218 )
219
220 IS
221
222 BEGIN
223
224 SELECT
225 SUM(NVL(RSL.AMOUNT, RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
226 INTO x_po_approved
227 FROM RCV_SHIPMENT_HEADERS RSH,
228 RCV_SHIPMENT_LINES RSL,
229 PO_LINE_LOCATIONS_ALL POLL
230 WHERE RSL.po_header_id = p_po_header_id
231 AND RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
232 AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
233 AND RSH.APPROVAL_STATUS = 'APPROVED'
234 AND RSL.APPROVAL_STATUS in ('APPROVED');
235
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237
238 EXCEPTION
239
240 WHEN OTHERS THEN
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_po_approved',
243 'Unexpected error occurred');
244 END;
245
246
247 -----------------------------------------------------------------------------
248 --API name : GET_PO_ORDERED
249 --TYPE : PRIVATE
250 --Function : Retrieve approved amount of a PO
251 --Parameter :
252 --IN : p_po_header_id IN NUMBER Required
253 -- corresponds to the columne PO_HEADER_ID in the table
254 -- PO_HEADERS_ALL, and identifies the PO for which the
255 -- information should be retrieved.
256 --
257 --OUT : x_ordered OUT NUMBER
258 -- total ordered amount for the PO
259 -- x_return_status OUT VARCHAR2
260 -- return status of the procedure
261 -- x_return_msg OUT VARCHAR2
262 -- return message of the procedure
263 -----------------------------------------------------------------------------
264 PROCEDURE GET_PO_ORDERED
265 (
266 p_po_header_id IN NUMBER,
267 x_po_ordered OUT nocopy NUMBER,
268 x_return_status OUT nocopy VARCHAR2,
269 x_return_msg OUT nocopy VARCHAR2
270 )
271
272 IS
273
274 l_po_ordered NUMBER;
275
276 BEGIN
277
278 --This should be the logic???
279 SELECT SUM(
280 NVL((POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED,0))
281 *POLL.PRICE_OVERRIDE,
282 (POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0))))
283 INTO l_po_ordered
284 FROM PO_LINE_LOCATIONS_ALL POLL
285 WHERE POLL.PO_HEADER_ID = p_po_header_id;
286
287 x_po_ordered := l_po_ordered;
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289
290 EXCEPTION
291
292 WHEN OTHERS THEN
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
294 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_po_ordered',
295 'Unexpected error occurred');
296
297 END GET_PO_ORDERED;
298
299
300 FUNCTION GET_WC_TOTAL_REQUESTED (p_wc_id IN NUMBER,
301 p_wc_stage IN VARCHAR2)
302 RETURN NUMBER
303 IS
304
305 l_intf_request NUMBER;
306 l_ship_request NUMBER;
307
308 BEGIN
309
310 IF(p_wc_stage = 'INTERFACE') THEN
311
312 SELECT SUM(NVL(RTI.AMOUNT, Round(RTI.QUANTITY*POLL.PRICE_OVERRIDE,POS_WC_VIEW_CANCEL_PVT.get_currency_precision(poll.po_header_id))))
313 INTO l_intf_request
314 FROM RCV_TRANSACTIONS_INTERFACE RTI,
315 RCV_HEADERS_INTERFACE RHI,
316 PO_LINE_LOCATIONS_ALL POLL
317 WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID and
318 RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
319 RHI.HEADER_INTERFACE_ID = p_wc_id;
320
321 ELSIF(p_wc_stage = 'SHIPMENT') THEN
322
323 SELECT SUM(NVL(RSL.AMOUNT_SHIPPED, Round(RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE,POS_WC_VIEW_CANCEL_PVT.get_currency_precision(poll.po_header_id))))
324 INTO l_ship_request
325 FROM RCV_SHIPMENT_LINES RSL,
326 RCV_SHIPMENT_HEADERS RSH,
327 PO_LINE_LOCATIONS_ALL POLL
328 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
329 RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
330 RSH.SHIPMENT_HEADER_ID = p_wc_id;
331 END IF;
332
333 IF(l_intf_request is null) THEN
334 l_intf_request := 0;
335 END IF;
336
337 IF(l_ship_request is null) THEN
338 l_ship_request := 0;
339 END IF;
340
341 return l_intf_request + l_ship_request;
342
343
344 EXCEPTION
345
346 WHEN OTHERS THEN
347 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_wc_total_requested',
348 'Unexpected error occurred');
349
350 END GET_WC_TOTAL_REQUESTED;
351
352 -----------------------------------------------------------------------------
353 --API name : GET_PAY_ITEM_PROGRESS
354 --TYPE : PUBLIC
355 --Function : Retrieve the progress on a pay item
356 --Parameter :
357 --IN : p_wc_pay_item_id IN NUMBER Required
358 -- corresponds to the column TRANSACTION_INTERFACE_ID or
359 -- SHIPMENT_LINE_ID in the table RCV_TRANSACTIONS_INTERFACE-- or RCV_SHIPMENT_LINES respectively, depending on the
360 -- p_wc_stage variable. The API is only implemented for
361 -- pay items in 'SHIPMENT' stage at the moment.
362 -- p_wc_stage IN VARCHAR2 Required
363 -- 'INTERFACE' or 'SHIPMENT. Indicates the pay item stage
364 --
365 --OUT : x_progress OUT NUMBER
366 -- calculated progress on pay item
367 -- x_return_status OUT VARCHAR2
368 -- return status of the procedure
369 -- x_return_msg OUT VARCHAR2
370 -- return message of the procedure
371 -----------------------------------------------------------------------------
372
373 PROCEDURE GET_PAY_ITEM_PROGRESS(
374 p_wc_pay_item_id IN NUMBER,
375 p_wc_stage IN VARCHAR2,
376 x_progress OUT NOCOPY NUMBER,
377 x_return_status OUT NOCOPY VARCHAR2,
378 x_return_msg OUT NOCOPY VARCHAR2)
379 IS
380
381 USE_CASE_NOT_SUPPORTED EXCEPTION;
382
383 l_amount_shipped NUMBER;
384 l_quantity_shipped NUMBER;
385 l_amount_ordered NUMBER;
386 l_quantity_ordered NUMBER;
387 l_matching_basis VARCHAR2(20);
388 l_line_location_id NUMBER;
389 l_prev_submitted NUMBER;
390
391
392
393 BEGIN
394
395
396 BEGIN
397 if(p_wc_stage = 'SHIPMENT') then
398
399 SELECT RSL.amount,
400 RSL.quantity_shipped,
401 POLL.amount,
402 POLL.quantity,
403 POLL.matching_basis,
404 POLL.line_location_id
405 INTO l_amount_shipped,
406 l_quantity_shipped,
407 l_amount_ordered,
408 l_quantity_ordered,
409 l_matching_basis,
410 l_line_location_id
411 FROM RCV_SHIPMENT_LINES RSL,
412 PO_LINE_LOCATIONS_ALL POLL
413 WHERE RSL.shipment_line_id = p_wc_pay_item_id and
414 POLL.line_location_id = RSL.po_line_location_id;
415
416 else --currently not supporting 'INTERFACE' stage
417
418 RAISE USE_CASE_NOT_SUPPORTED;
419
420 end if;
421
422 EXCEPTION
423 WHEN NO_DATA_FOUND THEN
424 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_pay_item_progress',
425 'No such pay item found with pay_item_id: '||p_wc_pay_item_id);
426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
427 END;
428
429 BEGIN
430 GET_PAY_ITEM_PREV_SUBMITTED(
431 p_wc_pay_item_id => p_wc_pay_item_id,
432 p_po_pay_item_id => l_line_location_id,
433 p_wc_stage => p_wc_stage,
434 x_prev_submitted => l_prev_submitted);
435
436 EXCEPTION
437 WHEN others THEN
438 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_pay_item_progress',
439 'API GET_PAY_ITEM_PREV_SUBMITTED returns error');
440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
441 END;
442
443 if(l_matching_basis = 'QUANTITY') then
444 x_progress := (l_prev_submitted+l_quantity_shipped)/l_quantity_ordered*100;
445 elsif (l_matching_basis = 'AMOUNT') then
446 x_progress := (l_prev_submitted+l_amount_shipped)/l_amount_ordered*100;
447 end if;
448
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450
451 EXCEPTION
452
453 WHEN USE_CASE_NOT_SUPPORTED THEN
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
455 x_return_msg := 'Shipment Stage: '|| p_wc_stage || ' not supported.';
456
457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459
460 WHEN others THEN
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462
463 END GET_PAY_ITEM_PROGRESS;
464
465 PROCEDURE GET_PAY_ITEM_PREV_SUBMITTED(
466 p_wc_pay_item_id IN NUMBER,
467 p_po_pay_item_id IN NUMBER,
468 p_wc_stage IN VARCHAR2,
469 x_prev_submitted OUT NOCOPY NUMBER)
470
471 IS
472
473 l_request_date DATE;
474 l_wc_prev_submitted_intf NUMBER := 0;
475 l_wc_prev_submitted_ship NUMBER := 0;
476 l_wc_status VARCHAR2(10);
477 l_header_processing_status VARCHAR2(10);
478 l_header_transaction_type VARCHAR2(10);
479 BEGIN
480
481 --Need to retrieve a date as time reference.
482 --INTERFACE: pay items in the interface table
483 --SHIPMENT: pay items in the shipment table
484 --PO: pay items in the PO table
485 l_wc_status := '-1';
486 IF (p_wc_stage = 'INTERFACE') THEN
487
488 SELECT RHI.request_date, RHI.processing_status_code, RHI.transaction_type
489 INTO l_request_date, l_header_processing_status, l_header_transaction_type
490 FROM RCV_HEADERS_INTERFACE RHI,
491 RCV_TRANSACTIONS_INTERFACE RTI
492 WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
493 RTI.INTERFACE_TRANSACTION_ID = p_wc_pay_item_id;
494
495 IF(l_header_processing_status = 'DRAFT' and
496 l_header_transaction_type = 'DRAFT') THEN
497
498 l_wc_status := 'DRAFT';
499 END IF;
500
501
502 ELSIF(p_wc_stage = 'SHIPMENT') THEN
503
504 SELECT RSH.request_date INTO l_request_date
505 FROM RCV_SHIPMENT_HEADERS RSH,
506 RCV_SHIPMENT_LINES RSL
507 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
508 RSL.SHIPMENT_LINE_ID = p_wc_pay_item_id;
509
510 ELSIF(p_wc_stage = 'PO') THEN
511
512 SELECT sysdate INTO l_request_date FROM DUAL;
513
514 END IF;
515 if(l_wc_status = 'DRAFT') THEN
516 l_request_date := sysdate ;
517 END IF;
518 --Use l_request_date as the time reference to search for all pay_items
519 --that have been previously submitted.
520
521
522 --First dig through the Interface table (PROCESSING)
523 BEGIN
524 SELECT sum(NVL(RTI.AMOUNT,RTI.QUANTITY))
525 INTO l_wc_prev_submitted_intf
526 FROM PO_LINE_LOCATIONS_ALL POLL,
527 RCV_TRANSACTIONS_INTERFACE RTI,
528 RCV_HEADERS_INTERFACE RHI
529 WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
530 RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
531 RTI.PROCESSING_STATUS_CODE = 'PENDING' AND
532 RTI.TRANSACTION_STATUS_CODE = 'PENDING' AND
533 RTI.PO_LINE_LOCATION_ID = p_po_pay_item_id AND
534 RHI.REQUEST_DATE < l_request_date;
535
536 EXCEPTION
537 WHEN NO_DATA_FOUND THEN l_wc_prev_submitted_intf := 0;
538 END;
539
540
541 IF(l_wc_prev_submitted_intf is NULL) THEN
542 l_wc_prev_submitted_intf := 0;
543 END IF;
544
545 --Then dig through the Shipment table
546 --(PENDING_APPROVAL, APPROVED, REJECTED, PROCESSED)
547 --NOTE: PROCESSED IS TRACK in RT?????
548 BEGIN
549
550 SELECT sum(NVL(RSL.AMOUNT_SHIPPED, RSL.QUANTITY_SHIPPED))
551 INTO l_wc_prev_submitted_ship
552 FROM PO_LINE_LOCATIONS_ALL POLL,
553 RCV_SHIPMENT_HEADERS RSH,
554 RCV_SHIPMENT_LINES RSL
555 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
556 RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
557 (RSL.APPROVAL_STATUS is NULL OR
558 RSL.APPROVAL_STATUS in ('APPROVED', 'REJECTED', 'PROCESSED')) AND
559 RSL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED' AND
560 RSL.PO_LINE_LOCATION_ID = p_po_pay_item_id AND
561 RSH.REQUEST_DATE < l_request_date;
562 EXCEPTION
563
564 WHEN NO_DATA_FOUND THEN l_wc_prev_submitted_ship := 0;
565
566 END;
567
568 IF(l_wc_prev_submitted_ship is NULL) THEN
569 l_wc_prev_submitted_ship := 0;
570 END IF;
571
572
573 x_prev_submitted := l_wc_prev_submitted_intf+l_wc_prev_submitted_ship;
574
575 EXCEPTION
576
577 WHEN OTHERS THEN
578 LOG(FND_LOG.LEVEL_UNEXPECTED,'get_pay_item_prev_submitted',
579 'Unexpected error occurred');
580
581
582 END GET_PAY_ITEM_PREV_SUBMITTED;
583
584 -----------------------------------------------------------------------------
585 --API name : COMPLETE_WC_APPROVAL_WF_BLOCK
586 --TYPE : PUBLIC
587 --Function : complete the WC approval block of the WCAPPRV workflow
588 --Parameter :
589 --IN : p_wc_header_id IN NUMBER Required
590 -- corresponds to the column SHIPMENT_HEADER_ID in
591 -- the table RCV_SHIPMENT_HEADERS.
592 --OUT : x_return_status OUT VARCHAR2
593 -- return status of the procedure.
594 -- x_return_msg OUT VARCHAR2
595 -- return message of the procedure.
596 -----------------------------------------------------------------------------
597
598
599 PROCEDURE COMPLETE_WC_APPROVAL_WF_BLOCK
600 (
601 p_wc_header_id IN NUMBER,
602 x_return_status OUT nocopy VARCHAR2,
603 x_return_msg OUT nocopy VARCHAR2
604 )
605
606 IS
607
608 l_wf_item_type VARCHAR2(8);
609 l_wf_item_key VARCHAR2(280);
610
611 BEGIN
612
613 SELECT WF_ITEM_KEY, WF_ITEM_TYPE
614 INTO l_wf_item_key,l_wf_item_type
615 FROM RCV_SHIPMENT_HEADERS
616 WHERE SHIPMENT_HEADER_ID = p_wc_header_id;
617
618 WF_ENGINE.CompleteActivity(l_wf_item_type,
619 l_wf_item_key,
620 'NOTIFY_WC_APPROVER_BLOCK',
621 'NULL');
622
623 x_return_status := FND_API.G_RET_STS_SUCCESS;
624
625 EXCEPTION
626
627 WHEN OTHERS THEN
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 LOG(FND_LOG.LEVEL_UNEXPECTED,'complete_wc_approval_wf_block',
630 'Unexpected error occurred');
631
632 END COMPLETE_WC_APPROVAL_WF_BLOCK;
633
634
635 -----------------------------------------------------------------------------
636 --API name : START_APPROVAL_WORKFLOW
637 --TYPE : PUBLIC
638 --Function : Start the approval workflow again after updating a
639 -- : rejected WC
640 --Parameter :
641 --IN : p_wc_header_id IN NUMBER Required
642 -- corresponds to the column SHIPMENT_HEADER_ID in
643 -- the table RCV_SHIPMENT_HEADERS.
644 --OUT : x_return_status OUT VARCHAR2
645 -- return status of the procedure.
646 -- x_return_msg OUT VARCHAR2
647 -- return message of the procedure.
648 -----------------------------------------------------------------------------
649
650
651 PROCEDURE START_APPROVAL_WORKFLOW
652 (
653 p_wc_header_id IN NUMBER,
654 x_return_status OUT nocopy VARCHAR2,
655 x_return_msg OUT nocopy VARCHAR2
656 )
657
658 IS
659
660 WC_APPROVAL_WF_API_FAIL EXCEPTION;
661 l_return_status VARCHAR2(1);
662 l_itemkey varchar2(60);
663 l_seq_for_item_key varchar2(6);
664
665 BEGIN
666
667 /*
668 All the updateable fields should have been updated at this point.
669 */
670
671 --1)Reset the Approval Status and Comment columns of the Header and the Lines
672
673 UPDATE RCV_SHIPMENT_HEADERS
674 SET APPROVAL_STATUS = null, COMMENTS = null
675 WHERE SHIPMENT_HEADER_ID = p_wc_header_id;
676
677 UPDATE RCV_SHIPMENT_LINES
678 SET APPROVAL_STATUS = null, COMMENTS = null
679 WHERE SHIPMENT_HEADER_ID = p_wc_header_id;
680
681
682 --2)Kick off the workflow again
683
684 select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
685 into l_seq_for_item_key
686 from sys.dual;
687
688 l_itemkey := to_char(p_wc_header_id) || '-' ||
689 l_seq_for_item_key;
690
691
692 POS_WCAPPROVE_PVT.START_WF_PROCESS(
693 p_itemtype => 'WCAPPRV',
694 p_itemkey => l_itemkey,
695 p_workflow_process => 'MAIN_WCAPPRV_PROCESS',
696 p_work_confirmation_id => p_wc_header_id,
697 x_return_status => l_return_status);
698
699 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
700 RAISE WC_APPROVAL_WF_API_FAIL;
701 END IF;
702
703 EXCEPTION
704
705 WHEN WC_APPROVAL_WF_API_FAIL THEN
706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707 LOG(FND_LOG.LEVEL_UNEXPECTED,'start_approval_workflow',
708 'pos_wcapprove_pvt.start_wf_process'|| 'returns unexpected error');
709 LOG(FND_LOG.LEVEL_UNEXPECTED,'start_approval_workflow',
710 'WC header Id:'||p_wc_header_id);
711
712 WHEN OTHERS THEN
713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
714 LOG(FND_LOG.LEVEL_UNEXPECTED,'start_approval_workflow',
715 'Unexpected error occurred');
716
717 END START_APPROVAL_WORKFLOW;
718
719 -- code added for work confirmation correction ER - 9414650
720
721 PROCEDURE insert_corrections_into_rti
722 (
723 p_shipment_header_id IN NUMBER,
724 p_line_location_id IN NUMBER,
725 p_group_id IN NUMBER,
726 p_amount_correction IN NUMBER,
727 p_quantity_correction IN NUMBER,
728 p_requested_amount_correction IN NUMBER,
729 p_material_stored_correction IN NUMBER,
730 p_comments IN varchar2)
731 --x_return_status OUT nocopy VARCHAR2,
732 --x_return_msg OUT nocopy VARCHAR2
733
734 IS
735
736 Cursor get_wcr_info(l_shipment_header_id NUMBER, l_line_location_id NUMBER) is
737 SELECT rsl.po_line_location_id,
738 pll.unit_meas_lookup_code,
739 rsl.unit_of_measure unit_of_measure,
740 rsl.unit_of_measure primary_unit_of_measure,
741 rsl.unit_of_measure source_doc_unit_of_measure,
742 NVL(pll.promised_date, pll.need_by_date) promised_date,
743 rsl.to_organization_id ship_to_organization_id,
744 null quantity_ordered,
745 null amount_ordered,
746 NVL(pll.price_override, pl.unit_price) po_unit_price,
747 pll.match_option,
748 rsl.category_id,
749 rsl.item_description,
750 pl.po_line_id,
751 ph.currency_code,
752 ph.rate_type currency_conversion_type,
753 ph.segment1 document_num,
754 null po_distribution_id, --pod.po_distribution_id,
755 rsl.req_distribution_id,
756 rsl.requisition_line_id,
757 rsl.deliver_to_location_id deliver_to_location_id,
758 rsl.deliver_to_location_id location_id,
759 rsl.deliver_to_person_id,
760 null currency_conversion_date, --pod.rate_date currency_conversion_date,
761 null currency_conversion_rate, --pod.rate currency_conversion_rate,
762 rsl.destination_type_code destination_type_code,
763 rsl.destination_type_code destination_context,
764 null charge_account_id, --pod.code_combination_id ,
765 null destination_organization_id, --pod.destination_organization_id,
766 null subinventory, --pod.destination_subinventory ,
767 rsl.ship_to_location_id,
768 rsl.comments,
769 rsl.attribute_category attribute_category,
770 rsl.attribute1 attribute1,
771 rsl.attribute2 attribute2,
772 rsl.attribute3 attribute3,
773 rsl.attribute4 attribute4,
774 rsl.attribute5 attribute5,
775 rsl.attribute6 attribute6,
776 rsl.attribute7 attribute7,
777 rsl.attribute8 attribute8,
778 rsl.attribute9 attribute9,
779 rsl.attribute10 attribute10,
780 rsl.attribute11 attribute11,
781 rsl.attribute12 attribute12,
782 rsl.attribute13 attribute13,
783 rsl.attribute14 attribute14,
784 rsl.attribute15 attribute15,
785 NVL(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code,
786 rsl.shipment_line_id,
787 rsl.item_id,
788 rsl.item_revision,
789 rsh.vendor_id,
790 rsh.shipment_num,
791 rsh.freight_carrier_code,
792 rsh.bill_of_lading,
793 rsh.packing_slip,
794 rsh.shipped_date,
795 rsh.expected_receipt_date,
796 rsh.waybill_airbill_num ,
797 rsh.vendor_site_id,
798 rsl.to_organization_id,
799 rsl.routing_header_id,
800 rsl.vendor_item_num,
801 rsl.vendor_lot_num,
802 rsl.ussgl_transaction_code,
803 rsl.government_context,
804 pll.po_header_id,
805 ph.revision_num po_revision_num,
806 pl.line_num document_line_num,
807 pll.shipment_num document_shipment_line_num,
808 null document_distribution_num , --pod.distribution_num
809 pll.po_release_id,
810 pl.job_id,
811 ph.org_id,
812 rsl.amount_shipped amount,
813 rsl.quantity_shipped quantity,
814 rsl.quantity_shipped source_doc_quantity,
815 rsl.quantity_shipped primary_quantity,
816 rsl.quantity_shipped quantity_shipped,
817 rsl.amount_shipped amount_shipped,
818 rsl.requested_amount requested_amount,
819 rsl.material_stored_amount material_stored_amount,
820 pll.matching_basis
821 FROM
822 --po_distributions_all pod,
823 po_line_locations_all pll,
824 po_lines_all pl,
825 po_headers_all ph,
826 rcv_shipment_lines rsl,
827 rcv_shipment_headers rsh
828 WHERE
829 rsh.shipment_header_id = l_shipment_header_id
830 AND rsl.po_line_location_id = l_line_location_id
831 and rsl.shipment_header_id = rsh.shipment_header_id
832 and rsl.po_header_id = ph.po_header_id
833 --and pod.po_header_id = ph.po_header_id
834 --and pod.line_location_id = pll.line_location_id
835 and rsl.po_line_id = pl.po_line_id
836 and rsl.po_line_location_id = pll.line_location_id
837 and rsh.receipt_source_code = 'VENDOR'
838 and pll.po_line_id = pl.po_line_id
839 AND NVL(pll.approved_flag, 'N') = 'Y'
840 AND NVL(pll.cancel_flag, 'N') = 'N'
841 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT');
842
843 wcr_line_info get_wcr_info%rowtype;
844
845 cursor get_dist_info(l_line_location_id NUMBER) is
846 select pod.po_distribution_id,
847 pod.rate_date currency_conversion_date,
848 pod.rate currency_conversion_rate,
849 pod.code_combination_id charge_account_id,
850 pod.destination_organization_id,
851 pod.destination_subinventory subinventory,
852 pod.distribution_num document_distribution_num,
853 pod.quantity_ordered,
854 pod.amount_ordered,
855 pod.destination_type_code destination_type_code,
856 pod.destination_type_code destination_context
857 from po_distributions_all pod
858 where pod.line_location_id = l_line_location_id;
859
860 l_shipment_header_id NUMBER;
861 l_line_location_id NUMBER;
862 l_progress VARCHAR2(240);
863
864 l_uom_code mtl_units_of_measure.uom_code%type;
865 l_row_id varchar2(40);
866 l_interface_id number;
867 l_group_id number;
868 l_vendor_id number;
869 l_vendor_site_id number;
870 l_ship_to_org_id number;
871 l_ship_to_location_id number;
872 l_header_interface_id number;
873 l_expected_receipt_date date;
874 l_shipment_num varchar2(50);
875 l_receipt_num varchar2(50);
876 l_matching_basis varchar2(35);
877 l_transacted_amount number;
878 l_interface_amount number;
879 l_transacted_quantity number;
880 l_interface_quantity number;
881 l_insert_into_rti boolean := TRUE;
882 l_max_dist NUMBER;
883 l_dist_count NUMBER;
884
885 L_REMAINING_AMOUNT_CORRECTION NUMBER;
886 L_REMAINING_REQ_AMOUNT_CORRECT NUMBER;
887 L_REMAINING_MAT_STORED_CORRECT NUMBER;
888 L_REMAINING_QUANTITY_CORRECT NUMBER;
889
890 l_available_correct_amount NUMBER;
891 l_carry_over_correction_amount NUMBER;
892 l_available_correct_quantity NUMBER;
893 l_carry_over_correct_quantity NUMBER;
894
895 l_transaction_type VARCHAR2(10);
896 l_parent_transaction_id NUMBER;
897
898 l_comments VARCHAR2(100);
899
900 l_req_amount_inserted BOOLEAN := FALSE;
901 l_mat_stored_inserted BOOLEAN := FALSE;
902
903 l_primary_quantity_in NUMBER;
904
905 BEGIN
906
907 l_shipment_header_id := p_shipment_header_id;
908 l_line_location_id := p_line_location_id;
909 l_group_id := p_group_id;
910 l_comments := p_comments;
911
912 l_progress := 'BEFORE opening the cursor';
913 -- opening the work confirmation cursor at specific pay item level
914 open get_wcr_info(l_shipment_header_id, l_line_location_id);
915
916 l_progress := 'after opening the cursor';
917
918 -- looping through the pay items associated with the current work confirmation
919 -- since we are passing line location id as well, we would be ideally getting only one record.
920 loop --{
921
922 l_progress := 'inside the loop';
923
924 l_progress := 'POS_WC_CREATE_UPDATE_PVT.insert_corrections_into_rti:01.';
925
926
927 fetch get_wcr_info into wcr_line_info;
928 exit when get_wcr_info%notfound;
929
930
931 If (wcr_line_info.unit_of_measure is not null) then
932 select muom.uom_code
933 into l_uom_code
934 from mtl_units_of_measure muom
935 WHERE muom.unit_of_measure = wcr_line_info.unit_of_measure;
936
937 end if;
938
939 l_matching_basis:= wcr_line_info.matching_basis;
940
941
942 If (l_matching_basis = 'AMOUNT') then
943
944 l_remaining_amount_correction := p_amount_correction;
945 l_remaining_req_amount_correct := p_requested_amount_correction;
946 l_remaining_mat_stored_correct := p_material_stored_correction;
947
948
949 end if;
950
951 If (l_matching_basis = 'QUANTITY') then
952
953 l_remaining_quantity_correct := p_quantity_correction;
954 end if;
955
956 -- getting the number of distributions associated at the current pay item level
957 SELECT Count(*)
958 INTO l_max_dist
959 FROM po_distributions_all pod
960 where pod.line_location_id = wcr_line_info.po_line_location_id;
961
962 -- the following two attributes take care that the requested amount and material stored values get updated only for the
963 -- first distribution, and for the subsequent distributions they are entered as 0.
964
965 l_req_amount_inserted := FALSE;
966 l_mat_stored_inserted := FALSE;
967
968 l_dist_count := 0;
969
970 -- opening the cursor for fetching distribution level information into the wcr record to be inserted into RTI
971 open get_dist_info(wcr_line_info.po_line_location_id);
972
973 -- looping through the distributions cursor to insert data in RTI
974 loop --{
975
976 l_progress := 'POS_WC_CREATE_UPDATE_PVT.insert_corrections_into_rti:02.';
977 l_progress := 'entered the dist loop';
978
979 fetch get_dist_info into
980 wcr_line_info.po_distribution_id,
981 wcr_line_info.currency_conversion_date,
982 wcr_line_info.currency_conversion_rate,
983 wcr_line_info.charge_account_id,
984 wcr_line_info.destination_organization_id,
985 wcr_line_info.subinventory,
986 wcr_line_info.document_distribution_num,
987 wcr_line_info.quantity_ordered,
988 wcr_line_info.amount_ordered,
989 wcr_line_info.destination_type_code,
990 wcr_line_info.destination_context;
991
992 exit when get_dist_info%notfound or
993 (l_matching_basis = 'AMOUNT' and l_remaining_amount_correction >= 0)
994 or
995 (l_matching_basis = 'QUANTITY' and l_remaining_quantity_correct >= 0);
996
997 l_dist_count := l_dist_count + 1;
998
999
1000 -- set the work confirmation variables for service based lines
1001 If (l_matching_basis = 'AMOUNT') then--{
1002
1003 /* l_transacted_amount = amount which was transacted earlier than the submission of
1004 current work confirmation for this payitem / distribution */
1005 select nvl(sum(amount),0)
1006 into l_transacted_amount
1007 from rcv_transactions
1008 where po_distribution_id= wcr_line_info.po_distribution_id
1009 and destination_type_code = 'RECEIVING';
1010
1011 /* l_interface_amount = amount which is in the interface tables /pending to be approved / rejected before the submission of
1012 current work confirmation for this payitem / distribution */
1013 select nvl(sum(amount),0)
1014 into l_interface_amount
1015 from rcv_transactions_interface
1016 where po_distribution_id= wcr_line_info.po_distribution_id
1017 and processing_status_code='PENDING'
1018 and transaction_status_code = 'PENDING'
1019 and transaction_type = 'RECEIVE';
1020
1021 -- l_available_correct_amount = total amount received against this distribution id
1022 l_available_correct_amount := l_transacted_amount + l_interface_amount;
1023
1024
1025 -- l_carry_over_correction_amount = correction amount left to be entered after doing correction for this
1026 -- distribution id, to be carried over to next distribution
1027 l_carry_over_correction_amount := l_remaining_amount_correction + l_available_correct_amount;
1028
1029 -- check if this is the last distribution for the pay item
1030 IF (l_dist_count >= l_max_dist) THEN
1031
1032 -- last distribution for pay item, insert the l_remaining_amount_correction completely
1033 wcr_line_info.amount := l_remaining_amount_correction;
1034
1035 l_remaining_amount_correction := 0;
1036
1037 IF(l_req_amount_inserted) THEN
1038 wcr_line_info.requested_amount := null;
1039 ELSE
1040 wcr_line_info.requested_amount := l_remaining_req_amount_correct;
1041 END IF;
1042
1043 IF(l_mat_stored_inserted) THEN
1044 wcr_line_info.material_stored_amount := null;
1045 ELSE
1046 wcr_line_info.material_stored_amount := l_remaining_mat_stored_correct;
1047 END IF;
1048
1049 l_req_amount_inserted := TRUE;
1050 l_mat_stored_inserted := TRUE;
1051
1052 l_insert_into_rti := TRUE;
1053
1054 ELSE
1055 -- not the last distribution for the pay item, check if we need to insert or not
1056
1057 IF(l_available_correct_amount > 0) THEN
1058 -- this distribution is not yet completely emptied,
1059 -- so we "need to insert" the correction depending on l_remaining_amount_correction and l_available_correct_amount
1060
1061 IF(l_carry_over_correction_amount > 0) THEN
1062
1063 -- this means that the entire correction amount can be inserted for this distribution
1064 wcr_line_info.amount := l_remaining_amount_correction;
1065
1066 l_remaining_amount_correction := 0;
1067
1068 ELSE
1069
1070 -- this means that the entire correction can not be inserted for this distribution alone
1071 -- need to carry over the remaining correction to the next distribution
1072 -- for this distribution, insert only the "l_available_correct_amount" as permitted
1073
1074 wcr_line_info.amount := 0 - l_available_correct_amount;
1075
1076 -- modify the l_remaining_amount_correction value after insertion
1077
1078 l_remaining_amount_correction := l_remaining_amount_correction + l_available_correct_amount;
1079
1080 END IF;
1081
1082 IF(l_req_amount_inserted) THEN
1083 wcr_line_info.requested_amount := null;
1084 ELSE
1085 wcr_line_info.requested_amount := l_remaining_req_amount_correct;
1086 END IF;
1087
1088 IF(l_mat_stored_inserted) THEN
1089 wcr_line_info.material_stored_amount := null;
1090 ELSE
1091 wcr_line_info.material_stored_amount := l_remaining_mat_stored_correct;
1092 END IF;
1093
1094 l_req_amount_inserted := TRUE;
1095 l_mat_stored_inserted := TRUE;
1096
1097 l_insert_into_rti := TRUE;
1098
1099 ELSE
1100
1101 -- l_available_amount < 0, so "no need to insert"
1102 l_insert_into_rti := FALSE;
1103
1104 END IF;
1105
1106 END IF;
1107
1108 -- set the work confirmation variables for quantity based lines
1109 elsif (l_matching_basis = 'QUANTITY') then --}{
1110
1111 /* l_transacted_quantity = quantity which was transacted earlier than the submission of
1112 current work confirmation for this payitem / distribution */
1113 select nvl(sum(quantity),0)
1114 into l_transacted_quantity
1115 from rcv_transactions
1116 where po_distribution_id= wcr_line_info.po_distribution_id
1117 and destination_type_code = 'RECEIVING';
1118
1119 /* l_interface_quantity = quantity which is in the interface tables /pending to be approved / rejected before the submission of
1120 current work confirmation for this payitem / distribution */
1121 select nvl(sum(quantity),0)
1122 into l_interface_quantity
1123 from rcv_transactions_interface
1124 where po_distribution_id= wcr_line_info.po_distribution_id
1125 and processing_status_code='PENDING'
1126 and transaction_status_code = 'PENDING'
1127 and transaction_type = 'RECEIVE';
1128
1129
1130 -- l_available_correct_quantity = total quantity received against this distribution id
1131 l_available_correct_quantity := l_transacted_quantity + l_interface_quantity;
1132
1133 -- l_carry_over_correction_quantity = correction quantity left to be entered after doing correction for this
1134 -- distribution id, to be carried over to next distribution
1135 l_carry_over_correct_quantity := l_remaining_quantity_correct + l_available_correct_quantity;
1136
1137 -- check if this is the last distribution for the pay item
1138 IF (l_dist_count >= l_max_dist) THEN
1139
1140 -- last distribution for pay item, insert the l_remaining_quantity_correct completely
1141 wcr_line_info.quantity := l_remaining_quantity_correct;
1142 l_remaining_quantity_correct := 0;
1143 l_insert_into_rti := TRUE;
1144
1145 ELSE
1146
1147 -- not the last distribution for the pay item, check if we need to insert or not
1148 IF(l_available_correct_quantity > 0) THEN
1149
1150 -- this distribution is not yet completely emptied,
1151 -- so we "need to insert" the correction depending on l_remaining_quantity_correct and l_available_correct_quantity
1152 IF(l_carry_over_correct_quantity > 0) THEN
1153
1154 -- this means that the entire correction amount can be inserted for this distribution
1155 wcr_line_info.quantity := l_remaining_quantity_correct;
1156 l_remaining_quantity_correct := 0;
1157
1158 ELSE
1159
1160 -- this means that the entire correction can not be inserted for this distribution alone
1161 -- need to carry over the remaining correction to the next distribution
1162 -- for this distribution, insert only the l_available_correct_quantity as permitted
1163
1164 wcr_line_info.quantity := 0 - l_available_correct_quantity;
1165
1166 -- modify the l_remaining_quantity_correct value after insertion
1167 l_remaining_quantity_correct := l_remaining_quantity_correct + l_available_correct_quantity;
1168 END IF;
1169
1170 l_insert_into_rti := TRUE;
1171
1172 ELSE
1173
1174 -- l_available_amount < 0, so "no need to insert"
1175 l_insert_into_rti := FALSE;
1176
1177 END IF;
1178
1179 END IF;
1180
1181 end if; --}
1182
1183
1184 If (l_insert_into_rti) then --{
1185
1186 FOR i IN 1..2 LOOP
1187
1188 select rcv_transactions_interface_s.nextval
1189 into l_interface_id
1190 from dual;
1191
1192 IF (i = 1) THEN
1193
1194 l_transaction_type := 'DELIVER';
1195
1196 ELSIF (i = 2) THEN
1197
1198 l_transaction_type := 'RECEIVE';
1199 wcr_line_info.destination_type_code := 'RECEIVING';
1200 wcr_line_info.destination_context := 'RECEIVING';
1201
1202 END IF;
1203
1204 select transaction_id
1205 INTO l_parent_transaction_id
1206 from rcv_transactions
1207 where shipment_header_id = l_shipment_header_id
1208 AND po_line_location_id = l_line_location_id
1209 AND transaction_type = l_transaction_type
1210 AND po_distribution_id = wcr_line_info.po_distribution_id;
1211
1212 IF(wcr_line_info.matching_basis = 'QUANTITY') THEN
1213
1214 po_uom_s.uom_convert(from_quantity => wcr_line_info.quantity,
1215 from_uom => wcr_line_info.unit_of_measure,
1216 item_id => wcr_line_info.item_id,
1217 to_uom => wcr_line_info.primary_unit_of_measure,
1218 to_quantity => l_primary_quantity_in);
1219
1220 wcr_line_info.primary_quantity := l_primary_quantity_in;
1221
1222 END IF;
1223
1224 l_progress := 'before the actual insert';
1225
1226 rcv_asn_interface_trx_ins_pkg.insert_row
1227 (l_row_id,
1228 l_interface_id,--interface_id
1229 l_group_id, --group_id
1230 sysdate, --last_updated_date
1231 fnd_global.user_id, --last_updated_by,
1232 sysdate, --creation_date,
1233 fnd_global.login_id, --created_by,
1234 fnd_global.login_id, -- last_update_login,
1235 NULL, --request_id,
1236 null, --program_application_id,
1237 null, --program_id,
1238 null, --program_update_date,
1239 'CORRECT', --transaction_type,
1240 sysdate, --transaction_date,
1241 'PENDING', --processing_status_code,
1242 'IMMEDIATE', --processing_mode_code,
1243 null, --processing_request_id,
1244 'PENDING', --.transaction_status_code,
1245 wcr_line_info.category_id,
1246 wcr_line_info.quantity, --quantity
1247 wcr_line_info.unit_of_measure,
1248 'ISP', --.interface_source_code,
1249 NULL, --.interface_source_line_id,
1250 NULL, --.inv_transaction_id,
1251 wcr_line_info.item_id,
1252 wcr_line_info.item_description,
1253 wcr_line_info.item_revision,
1254 l_uom_code, --uom_code,
1255 NULL, --employee_id,
1256 NULL, --auto_transact_code,
1257 l_shipment_header_id, --l_shipment_header_id
1258 wcr_line_info.shipment_line_id,
1259 wcr_line_info.ship_to_location_id,
1260 wcr_line_info.primary_quantity,
1261 wcr_line_info.primary_unit_of_measure,
1262 'VENDOR', --.receipt_source_code,
1263 wcr_line_info.vendor_id,
1264 wcr_line_info.vendor_site_id,
1265 NULL, --from_organization_id,
1266 NULL, --from_subinventory,
1267 wcr_line_info.to_organization_id,
1268 NULL, --.intransit_owning_org_id,
1269 wcr_line_info.routing_header_id,
1270 NULL, --.routing_step_id,
1271 'PO', --source_document_code,
1272 l_parent_transaction_id, --.parent_transaction_id (for correction purpose),
1273 wcr_line_info.po_header_id,
1274 wcr_line_info.po_revision_num,
1275 wcr_line_info.po_release_id,
1276 wcr_line_info.po_line_id,
1277 wcr_line_info.po_line_location_id,
1278 wcr_line_info.po_unit_price,
1279 wcr_line_info.currency_code,
1280 wcr_line_info.currency_conversion_type,
1281 wcr_line_info.currency_conversion_rate,
1282 wcr_line_info.currency_conversion_date,
1283 wcr_line_info.po_distribution_id,
1284 wcr_line_info.requisition_line_id,
1285 wcr_line_info.req_distribution_id,
1286 wcr_line_info.charge_account_id,
1287 NULL, --.substitute_unordered_code,
1288 NULL, --.receipt_exception_flag,
1289 NULL, --.accrual_status_code,
1290 'NOT INSPECTED' ,--.inspection_status_code,
1291 NULL, --.inspection_quality_code,
1292 wcr_line_info.destination_type_code,
1293 wcr_line_info.deliver_to_person_id,
1294 wcr_line_info.location_id,
1295 wcr_line_info.deliver_to_location_id,
1296 NULL, --.subinventory,
1297 NULL, --.locator_id,
1298 NULL, --.wip_entity_id,
1299 NULL, --.wip_line_id,
1300 NULL, --.department_code,
1301 NULL, --.wip_repetitive_schedule_id,
1302 NULL, --.wip_operation_seq_num,
1303 NULL, --.wip_resource_seq_num,
1304 NULL, --.bom_resource_id,
1305 wcr_line_info.shipment_num,
1306 wcr_line_info.freight_carrier_code,
1307 wcr_line_info.bill_of_lading,
1308 wcr_line_info.packing_slip,
1309 wcr_line_info.shipped_date,
1310 wcr_line_info.expected_receipt_date,
1311 NULL, --.actual_cost,
1312 NULL, --.transfer_cost,
1313 NULL, --.transportation_cost,
1314 NULL, --.transportation_account_id,
1315 NULL, --.num_of_containers,
1316 wcr_line_info.waybill_airbill_num,
1317 wcr_line_info.vendor_item_num,
1318 wcr_line_info.vendor_lot_num,
1319 NULL,--.rma_reference,
1320 l_comments,
1321 wcr_line_info.attribute_category,
1322 wcr_line_info.attribute1,
1323 wcr_line_info.attribute2,
1324 wcr_line_info.attribute3,
1325 wcr_line_info.attribute4,
1326 wcr_line_info.attribute5,
1327 wcr_line_info.attribute6,
1328 wcr_line_info.attribute7,
1329 wcr_line_info.attribute8,
1330 wcr_line_info.attribute9,
1331 wcr_line_info.attribute10,
1332 wcr_line_info.attribute11,
1333 wcr_line_info.attribute12,
1334 wcr_line_info.attribute13,
1335 wcr_line_info.attribute14,
1336 wcr_line_info.attribute15,
1337 NULL, --.ship_head_attribute_category,
1338 NULL, --.ship_head_attribute1,
1339 NULL, --.ship_head_attribute2,
1340 NULL, --.ship_head_attribute3,
1341 NULL, --.ship_head_attribute4,
1342 NULL, --.ship_head_attribute5,
1343 NULL, --.ship_head_attribute6,
1344 NULL, --.ship_head_attribute7,
1345 NULL, --.ship_head_attribute8,
1346 NULL, --.ship_head_attribute9,
1347 NULL, --.ship_head_attribute10,
1348 NULL, --.ship_head_attribute11,
1349 NULL, --.ship_head_attribute12,
1350 NULL, --.ship_head_attribute13,
1351 NULL, --.ship_head_attribute14,
1352 NULL, --.ship_head_attribute15,
1353 NULL, --.ship_line_attribute_category,
1354 NULL, --.ship_line_attribute1,
1355 NULL, --.ship_line_attribute2,
1356 NULL, --.ship_line_attribute3,
1357 NULL, --.ship_line_attribute4,
1358 NULL, --.ship_line_attribute5,
1359 NULL, --.ship_line_attribute6,
1360 NULL, --.ship_line_attribute7,
1361 NULL, --.ship_line_attribute8,
1362 NULL, --.ship_line_attribute9,
1363 NULL, --.ship_line_attribute10,
1364 NULL, --.ship_line_attribute11,
1365 NULL, --.ship_line_attribute12,
1366 NULL, --.ship_line_attribute13,
1367 NULL, --.ship_line_attribute14,
1368 NULL, --.ship_line_attribute15,
1369 wcr_line_info.ussgl_transaction_code,
1370 wcr_line_info.government_context,
1371 NULL, --.reason_id,
1372 wcr_line_info.destination_context,
1373 wcr_line_info.source_doc_quantity,
1374 wcr_line_info.source_doc_unit_of_measure,
1375 NULL, --.movement_id,
1376 NULL, --l_header_interface_id, --.header_interface_id,
1377 NULL, --.vendor_cum_shipped_qty,
1378 NULL, --.item_num,
1379 wcr_line_info.document_num,
1380 wcr_line_info.document_line_num,
1381 NULL, --.truck_num,
1382 NULL, --.ship_to_location_code,
1383 NULL, --.container_num,
1384 NULL, --.substitute_item_num,
1385 NULL, --.notice_unit_price,
1386 NULL, --.item_category,
1387 NULL, --.location_code,
1388 NULL, --.vendor_name,
1389 NULL, --.vendor_num,
1390 NULL, --.vendor_site_code,
1391 NULL, --.from_organization_code,
1392 NULL, --.to_organization_code,
1393 NULL, --.intransit_owning_org_code,
1394 NULL, --.routing_code,
1395 NULL, --.routing_step,
1396 NULL, --.release_num,
1397 wcr_line_info.document_shipment_line_num,
1398 wcr_line_info.document_distribution_num,
1399 NULL, --.deliver_to_person_name,
1400 NULL, --.deliver_to_location_code,
1401 NULL, --.use_mtl_lot,
1402 NULL, --.use_mtl_serial,
1403 NULL, --.LOCATOR,
1404 NULL, --.reason_name,
1405 NULL, --.validation_flag,
1406 NULL, --.substitute_item_id,
1407 NULL, --.quantity_shipped,
1408 NULL, --.quantity_invoiced,
1409 NULL, --.tax_name,
1410 NULL, --.tax_amount,
1411 NULL, --.req_num,
1412 NULL, --.req_line_num,
1413 NULL, --.req_distribution_num,
1414 NULL, --.wip_entity_name,
1415 NULL, --.wip_line_code,
1416 NULL, --.resource_code,
1417 NULL, --.shipment_line_status_code,
1418 NULL, --.barcode_label,
1419 NULL, --.country_of_origin_code,
1420 NULL, --.from_locator_id, --WMS Change
1421 NULL, --.qa_collection_id,
1422 NULL, --.oe_order_header_id,
1423 NULL, --.oe_order_line_id,
1424 NULL, --.customer_id,
1425 NULL, --.customer_site_id,
1426 NULL, --.customer_item_num,
1427 NULL, --.create_debit_memo_flag,
1428 NULL, --.put_away_rule_id,
1429 NULL, --.put_away_strategy_id,
1430 NULL, --.lpn_id,
1431 NULL, --.transfer_lpn_id,
1432 NULL, --.cost_group_id,
1433 NULL, --.mobile_txn,
1434 NULL, --.mmtt_temp_id,
1435 NULL, --.transfer_cost_group_id,
1436 NULL, --.secondary_quantity,
1437 NULL, --.secondary_unit_of_measure,
1438 NULL, --.secondary_uom_code,
1439 NULL, --.qc_grade,
1440 NULL, --.oe_order_num,
1441 NULL, --.oe_order_line_num,
1442 NULL, --.customer_account_number,
1443 NULL, --.customer_party_name,
1444 NULL, --.source_transaction_num,
1445 NULL, --.parent_source_transaction_num,
1446 NULL, --.parent_interface_txn_id,
1447 NULL, --.customer_item_id,
1448 NULL, --.interface_available_qty,
1449 NULL, --.interface_transaction_qty,
1450 NULL, --.from_locator,
1451 NULL, --.lpn_group_id,
1452 NULL, --.order_transaction_id,
1453 NULL, --.license_plate_number,
1454 NULL, --.transfer_license_plate_number,
1455 wcr_line_info.amount,
1456 wcr_line_info.job_id,
1457 NULL, --.project_id,
1458 NULL, --.task_id,
1459 NULL, --.asn_attach_id,
1460 NULL, --.timecard_id,
1461 NULL, --.timecard_ovn,
1462 NULL, --.interface_available_amt,
1463 NULL, --.interface_transaction_amt
1464 wcr_line_info.org_id, --<R12 MOAC>
1465 wcr_line_info.matching_basis,
1466 NULL, --wcr_line_info.amount_shipped, --amount_shipped
1467 wcr_line_info.requested_amount,
1468 wcr_line_info.material_stored_amount);
1469
1470 l_progress := 'record inserted';
1471
1472
1473 END LOOP;
1474
1475
1476 END IF; --}
1477
1478 end loop; --}
1479
1480 If get_dist_info%isopen then
1481 Close get_dist_info;
1482 end if;
1483
1484
1485 end loop; --}
1486
1487 If get_wcr_info%isopen then
1488 Close get_wcr_info;
1489 end if;
1490
1491
1492 EXCEPTION
1493
1494 WHEN OTHERS THEN
1495 LOG(FND_LOG.LEVEL_UNEXPECTED,'INSERT CORRECTION DATA IN RTI',
1496 'Unexpected error at stage: '|| l_progress);
1497
1498 END insert_corrections_into_rti;
1499
1500 procedure Launch_RTP_Immediate
1501 (p_group_id IN NUMBER) IS
1502
1503 l_group_id number;
1504 l_result_id NUMBER;
1505 begin
1506
1507 l_group_id := p_group_id;
1508
1509 l_result_id :=
1510 fnd_request.submit_request('PO',
1511 'RVCTP',
1512 null,
1513 null,
1514 false,
1515 'IMMEDIATE',
1516 --'BATCH',
1517 l_group_id,
1518 --fnd_char.local_chr(0),
1519 NULL, -- Modified as part of P1 Bug #: 16208460
1520 NULL,
1521 NULL,
1522 NULL,
1523 NULL,
1524 NULL, NULL,
1525 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1526 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1527 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1528 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1529 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1530
1531 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1532 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1533 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1534 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1535
1536 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1537 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1538 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1539 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1540
1541
1542 COMMIT;
1543
1544 exception
1545 when others then
1546 raise;
1547
1548 END Launch_RTP_Immediate;
1549
1550 PROCEDURE get_wc_history(p_shipment_header_id IN NUMBER,
1551 p_correction_history_tab IN OUT NOCOPY correction_history_tab )
1552 IS
1553
1554 l_shipment_header_id NUMBER := p_shipment_header_id;
1555 TYPE group_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1556 TYPE line_location_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1557 TYPE quantity_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1558 TYPE total_amt_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1559 TYPE requested_amt_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1560 TYPE matstored_amt_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1561 l_groups group_type;
1562 l_line_locations line_location_type;
1563
1564 l_old_quantity quantity_type;
1565 l_old_total_amount total_amt_type;
1566 l_old_requested_amount requested_amt_type;
1567 l_old_matstoredAmount matstored_amt_type;
1568
1569 l_new_quantity quantity_type;
1570 l_new_total_amount total_amt_type;
1571 l_new_requested_amount requested_amt_type;
1572 l_new_matstoredAmount matstored_amt_type;
1573 l_old_quantity1 NUMBER;
1574 l_old_total_amount1 NUMBER;
1575 l_old_requestedAmount1 NUMBER;
1576 l_old_matstoredamount1 NUMBER;
1577 i NUMBER := 0;
1578 j NUMBER := 0;
1579 k NUMBER := 0;
1580
1581 l_old_quantity2 NUMBER;
1582 l_old_total_amount2 NUMBER;
1583 l_old_requestedAmount2 NUMBER;
1584 l_old_matstoredamount2 NUMBER;
1585 l_corrected_quantity NUMBER;
1586 l_corrected_total_amount NUMBER;
1587 l_corrected_requested_amount NUMBER;
1588 l_corrected_matstoredAmount NUMBER;
1589 l_po_header_id NUMBER;
1590 l_po_line_location_id NUMBER;
1591 l_line_location_id NUMBER;
1592 l_group_id NUMBER;
1593
1594 l_last_updated_by NUMBER;
1595 l_creation_date DATE;
1596 l_created_by NUMBER;
1597 l_transaction_type VARCHAR2(10);
1598 l_transaction_date DATE;
1599 l_po_line_id NUMBER;
1600 l_comments VARCHAR2(100);
1601 l_ordered_quantity NUMBER;
1602 l_ordered_amount NUMBER;
1603 l_price NUMBER;
1604 l_line_num NUMBER;
1605 l_description VARCHAR2(100);
1606 l_matching_basis VARCHAR2(10);
1607 l_shipment_num NUMBER;
1608
1609 l_employee_id NUMBER;
1610 l_full_name VARCHAR2(100);
1611 l_old_progress NUMBER;
1612 l_new_progress NUMBER;
1613
1614 l_correction_num NUMBER;
1615
1616 l_correction_history_rec wc_correction_history_rec;
1617 l_correction_history_tab correction_history_tab := correction_history_tab();
1618
1619 CURSOR c1 IS
1620 SELECT DISTINCT po_line_location_id
1621 FROM rcv_transactions rt
1622 WHERE shipment_header_id = l_shipment_header_id
1623 AND transaction_type = 'CORRECT'
1624 ORDER BY po_line_location_id;
1625
1626 CURSOR c2(l_line_location_id NUMBER) IS
1627 SELECT distinct group_id
1628 FROM rcv_transactions rt
1629 WHERE shipment_header_id = l_shipment_header_id
1630 AND transaction_type = 'CORRECT'
1631 AND EXISTS (SELECT '1'
1632 FROM rcv_transactions rt2
1633 WHERE rt2.transaction_type = 'DELIVER'
1634 AND rt2.transaction_id = rt.parent_transaction_id)
1635 AND po_line_location_id = l_line_location_id
1636 ORDER BY group_id ASC;
1637
1638 BEGIN
1639
1640 OPEN c1;
1641
1642 LOOP
1643
1644 i := i + 1;
1645
1646 FETCH c1 INTO l_line_locations(i);
1647 EXIT WHEN c1%NOTFOUND;
1648
1649 END LOOP;
1650
1651 CLOSE c1;
1652
1653 FOR i IN 1 .. l_line_locations.count LOOP
1654 k := 1;
1655 l_line_location_id := l_line_locations(i);
1656
1657 SELECT Sum(rt.quantity),
1658 Sum(rt.amount),
1659 Sum(rt.requested_amount) ,
1660 Sum(rt.material_stored_amount),
1661 po_header_id,
1662 po_line_location_id
1663 INTO l_old_quantity1,
1664 l_old_total_amount1,
1665 l_old_requestedAmount1,
1666 l_old_matstoredamount1,
1667 l_po_header_id,
1668 l_po_line_location_id
1669 FROM rcv_transactions rt
1670 WHERE shipment_header_id = l_shipment_header_id
1671 AND po_line_location_id = l_line_location_id
1672 AND transaction_type = 'DELIVER'
1673 GROUP BY po_header_id,
1674 po_line_location_id;
1675
1676 l_correction_num := 1;
1677 OPEN c2(l_line_location_id);
1678
1679 LOOP
1680
1681 FETCH c2 INTO l_groups(l_correction_num);
1682 EXIT WHEN c2%NOTFOUND;
1683 l_correction_num := l_correction_num + 1;
1684
1685 END LOOP;
1686 l_correction_num := l_correction_num - 1;
1687
1688 CLOSE c2;
1689
1690 FOR j IN 1 .. l_correction_num LOOP
1691
1692 SELECT Sum(Nvl(rt.quantity, 0)),
1693 Sum(Nvl(rt.amount, 0)),
1694 Sum(Nvl(rt.requested_amount, 0)) ,
1695 Sum(Nvl(rt.material_stored_amount, 0)),
1696 Min(rt.last_updated_by),
1697 Min(Nvl(rt.employee_id, -1)),
1698 Min(rt.creation_date),
1699 --Min(rt.created_by),
1700 Min(rt.transaction_type),
1701 Min(rt.transaction_date),
1702 rt.po_header_id,
1703 rt.po_line_id,
1704 Min(rt.comments),
1705 Min(pll.quantity),
1706 Min(pll.amount),
1707 DECODE( pll.matching_basis, 'AMOUNT' , Min(pll.AMOUNT), Min(pll.PRICE_OVERRIDE)),
1708 pl.line_num ,
1709 pll.description ,
1710 pll.matching_basis ,
1711 pll.shipment_num
1712 INTO l_corrected_quantity,
1713 l_corrected_total_amount,
1714 l_corrected_requested_amount,
1715 l_corrected_matstoredAmount,
1716 l_last_updated_by,
1717 l_employee_id,
1718 l_creation_date,
1719 --l_created_by,
1720 l_transaction_type,
1721 l_transaction_date,
1722 l_po_header_id,
1723 l_po_line_id,
1724 l_comments,
1725 l_ordered_quantity,
1726 l_ordered_amount,
1727 l_price,
1728 l_line_num,
1729 l_description,
1730 l_matching_basis,
1731 l_shipment_num
1732 FROM rcv_transactions rt,
1733 po_lines_all pl,
1734 po_line_locations_all pll
1735 WHERE rt.shipment_header_id = l_shipment_header_id
1736 AND rt.transaction_type = 'CORRECT'
1737 AND EXISTS (SELECT '1'
1738 FROM rcv_transactions rt2
1739 WHERE rt2.transaction_type = 'DELIVER'
1740 AND rt2.transaction_id = rt.parent_transaction_id
1741 )
1742 AND rt.po_line_location_id = l_line_location_id
1743 AND rt.group_id = l_groups(j)
1744 AND pll.line_location_id = rt.po_line_location_id
1745 AND pl.po_line_id = rt.po_line_id
1746 AND pl.po_line_id = pll.po_line_id
1747 GROUP BY rt.group_id,
1748 rt.po_header_id,
1749 rt.po_line_id,
1750 rt.po_line_location_id,
1751 pl.line_num,
1752 pll.description,
1753 pll.matching_basis,
1754 pll.shipment_num;
1755
1756 IF (j > 1) THEN
1757
1758 l_old_quantity(k) := l_old_quantity2;
1759 l_old_total_amount(k) := l_old_total_amount2;
1760 l_old_requested_amount(k) :=l_old_requestedAmount2;
1761 l_old_matstoredAmount(k) := l_old_matstoredamount2;
1762
1763 ELSE
1764
1765 l_old_quantity(k) := l_old_quantity1;
1766 l_old_total_amount(k) := l_old_total_amount1;
1767 l_old_requested_amount(k) := l_old_requestedAmount1;
1768 l_old_matstoredAmount(k) := l_old_matstoredamount1;
1769
1770 l_old_quantity2 := l_old_quantity1;
1771 l_old_total_amount2 := l_old_total_amount1;
1772 l_old_requestedAmount2 := l_old_requestedAmount1;
1773 l_old_matstoredamount2 := l_old_matstoredamount1;
1774
1775 END IF;
1776
1777 l_new_quantity(k) := l_old_quantity2 + l_corrected_quantity;
1778 l_new_total_amount(k) := l_old_total_amount2 + l_corrected_total_amount;
1779 l_new_requested_amount(k) := l_old_requestedamount2 + l_corrected_requested_amount;
1780 l_new_matstoredAmount(k) := l_old_matstoredamount2 + l_corrected_matstoredAmount;
1781
1782 l_old_quantity2 := l_new_quantity(k);
1783 l_old_total_amount2 := l_new_total_amount(k);
1784 l_old_requestedAmount2 := l_new_requested_amount(k);
1785 l_old_matstoredamount2 := l_new_matstoredAmount(k);
1786
1787 l_correction_history_rec.old_quantity := l_old_quantity(k);
1788 l_correction_history_rec.new_quantity := l_new_quantity(k);
1789 l_correction_history_rec.old_total_amount := l_old_total_amount(k);
1790 l_correction_history_rec.old_req_amount := l_old_requested_amount(k);
1791 l_correction_history_rec.old_mat_stored := l_old_matstoredAmount(k);
1792 l_correction_history_rec.new_total_amount := l_new_total_amount(k) ;
1793 l_correction_history_rec.new_req_amount := l_new_requested_amount(k);
1794 l_correction_history_rec.new_mat_stored := l_new_matstoredAmount(k);
1795
1796 l_correction_history_rec.correction_date := l_creation_date;
1797
1798
1799 IF(l_last_updated_by >0) THEN
1800
1801 -- bug - 9692573 - fetching the data only for the active employee record
1802 SELECT Nvl(full_name, ' ')
1803 INTO l_full_name
1804 FROM per_employees_current_x
1805 WHERE employee_id = (SELECT employee_id FROM fnd_user WHERE user_id = l_last_updated_by);
1806
1807 ELSE
1808
1809 l_full_name := ' ';
1810
1811 END IF;
1812
1813
1814 l_correction_history_rec.corrected_by := l_full_name;
1815 l_correction_history_rec.shipment_header_id := l_shipment_header_id;
1816 l_correction_history_rec.po_header_id := l_po_header_id;
1817 l_correction_history_rec.po_line_id := l_po_line_id;
1818 l_correction_history_rec.po_line_location_id := l_line_location_id;
1819 l_correction_history_rec.comments := l_comments;
1820 l_correction_history_rec.quantity_ordered := l_ordered_quantity;
1821 l_correction_history_rec.amount_ordered := l_ordered_amount;
1822 l_correction_history_rec.price := l_price;
1823 l_correction_history_rec.group_id := l_groups(j);
1824 l_correction_history_rec.document_line_num := l_line_num;
1825 l_correction_history_rec.pay_item_num := l_shipment_num;
1826 l_correction_history_rec.description := l_description;
1827 l_correction_history_rec.matching_basis := l_matching_basis;
1828
1829
1830 IF(l_matching_basis = 'QUANTITY') THEN
1831
1832 l_old_progress := ( l_old_quantity(k) / l_ordered_quantity )* 100;
1833 l_new_progress := ( l_new_quantity(k) / l_ordered_quantity ) * 100;
1834
1835 l_correction_history_rec.old_req_deliver := l_old_quantity(k);
1836 l_correction_history_rec.new_req_deliver := l_new_quantity(k);
1837
1838 l_correction_history_rec.old_total_amount := l_old_quantity(k) * l_price;
1839 l_correction_history_rec.new_total_amount := l_new_quantity(k) * l_price;
1840
1841 ELSIF (l_matching_basis = 'AMOUNT') THEN
1842
1843 l_old_progress := ( l_old_total_amount(k) / l_ordered_amount )* 100;
1844 l_new_progress := ( l_new_total_amount(k) / l_ordered_amount ) * 100;
1845
1846 l_correction_history_rec.old_req_deliver := l_old_requested_amount(k);
1847 l_correction_history_rec.new_req_deliver := l_new_requested_amount(k);
1848
1849 END IF;
1850
1851 l_correction_history_rec.old_progress := Round(l_old_progress, 2);
1852 l_correction_history_rec.new_progress := Round(l_new_progress, 2);
1853
1854 l_correction_history_tab.EXTEND;
1855 l_correction_history_tab(l_correction_history_tab.COUNT) := l_correction_history_rec;
1856
1857
1858
1859 k := k+1;
1860
1861 END LOOP;
1862
1863 END LOOP;
1864
1865 p_correction_history_tab := l_correction_history_tab;
1866
1867 EXCEPTION
1868
1869 WHEN OTHERS THEN RAISE;
1870
1871 END get_wc_history;
1872
1873 FUNCTION get_wc_correction_history(p_shipment_header_id IN NUMBER)
1874 RETURN correction_history_tab PIPELINED IS
1875
1876 l_shipment_header_id NUMBER := p_shipment_header_id;
1877 l_correction_history_tab correction_history_tab := correction_history_tab();
1878
1879 BEGIN
1880
1881 get_wc_history(l_shipment_header_id, l_correction_history_tab);
1882
1883 FOR i IN l_correction_history_tab.FIRST..l_correction_history_tab.LAST LOOP
1884
1885 PIPE ROW(l_correction_history_tab(i));
1886
1887 END LOOP;
1888
1889 RETURN;
1890
1891 END;
1892
1893 -- end of code added for work confirmation correction ER - 9414650
1894
1895 END POS_WC_CREATE_UPDATE_PVT;