[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.4 2006/10/06 12:46:10 pkapoor noship $*/
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, RTI.QUANTITY*POLL.PRICE_OVERRIDE))
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, RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
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
720 END POS_WC_CREATE_UPDATE_PVT;