[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_CANCEL_PVT
Source
1 PACKAGE BODY PO_Document_Cancel_PVT AS
2 /* $Header: POXVDCAB.pls 120.6.12020000.6 2013/05/28 11:58:21 srpantha ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
8
9 -- <Shared Proc FPJ START>
10
11 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
12 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
13
14 -- <Shared Proc FPJ END>
15
16
17 --<Bug 14254141 :Cancel Refactoring Project Starts>
18 --------------------------------------------------------------------------------
19 --Start of Comments
20 --Name: isDropShipWithUpdateableSO
21
22 --Function:
23 -- Checks if there are any PO/release drop shipments with updatable sales Orders
24
25 --Parameters:
26 --IN:
27 -- p_entity_level
28 -- p_entity_id
29 -- p_document_type
30
31 --RETURNS :
32 -- TRUE if there are any PO/release drop shipments with updatable sales Order
33 -- Lines
34 -- FALSE if there are no PO/release drop shipments with updatable sales Order
35 -- Lines
36
37 --End of Comments
38 --------------------------------------------------------------------------------
39
40 FUNCTION isDropShipWithUpdateableSO(
41 p_api_version IN NUMBER,
42 p_init_msg_list IN VARCHAR2,
43 p_entity_level IN VARCHAR2,
44 p_entity_id IN NUMBER,
45 p_document_type IN VARCHAR2)
46 RETURN BOOLEAN
47 IS
48
49 d_api_version CONSTANT NUMBER := 1.0;
50 d_api_name CONSTANT VARCHAR2(30) := 'isDropShipWithUpdateableSO.';
51 d_module CONSTANT VARCHAR2(100) := g_module_prefix || d_api_name;
52
53 l_progress VARCHAR2(3) := '000' ;
54 l_so_updatable_flag VARCHAR2(1);
55 l_return_status VARCHAR2(30);
56 l_msg_data VARCHAR2(30);
57 l_msg_count NUMBER;
58 l_on_hold VARCHAR2(30);
59 l_order_line_status NUMBER;
60
61 CURSOR l_drop_ship_csr IS
62 SELECT line_location_id
63 FROM po_line_locations
64 WHERE NVL(drop_ship_flag, 'N') = 'Y'
65 AND ((p_entity_level = c_entity_level_HEADER
66 AND p_document_type <> c_doc_type_RELEASE
67 AND po_header_id = p_entity_id)
68 OR
69 (p_entity_level = c_entity_level_HEADER
70 AND p_document_type = c_doc_type_RELEASE
71 AND po_release_id = p_entity_id)
72 OR
73 (p_entity_level = c_entity_level_LINE
74 AND po_line_id = p_entity_id)
75 OR
76 (p_entity_level = c_entity_level_SHIPMENT
77 AND line_location_id = p_entity_id)
78 );
79
80 l_drop_ship_row l_drop_ship_csr%ROWTYPE;
81
82 BEGIN
83 -- Start standard API initialization
84 IF FND_API.to_boolean(p_init_msg_list) THEN
85 FND_MSG_PUB.initialize;
86 END IF;
87
88 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
89 d_api_name, g_pkg_name) THEN
90 RAISE FND_API.g_exc_unexpected_error;
91 END IF;
92
93 IF g_debug_stmt THEN
94 PO_DEBUG.debug_begin(d_module);
95 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
96 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
97 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
98 END IF;
99
100 l_progress := '001' ;
101
102 FOR l_drop_ship_row IN l_drop_ship_csr LOOP
103
104 OE_DROP_SHIP_GRP.Get_Order_Line_Status(
105 p_api_version => 1.0,
106 p_po_header_id => NULL,
107 p_po_release_id => NULL,
108 p_po_line_id => NULL,
109 p_po_line_location_id => l_drop_ship_row.line_location_id,
110 p_mode => 0,
111 x_updatable_flag => l_so_updatable_flag,
112 x_on_hold => l_on_hold,
113 x_order_line_status => l_order_line_status,
114 x_return_status => l_return_status,
115 x_msg_data => l_msg_data,
116 x_msg_count => l_msg_count);
117
118 IF g_debug_stmt THEN
119 PO_DEBUG.debug_var(d_module, l_progress, 'line_location_id', l_drop_ship_row.line_location_id);
120 PO_DEBUG.debug_var(d_module, l_progress, 'l_so_updatable_flag', l_so_updatable_flag);
121 PO_DEBUG.debug_var(d_module, l_progress, 'l_order_line_status', l_order_line_status);
122 PO_DEBUG.debug_var(d_module, l_progress, 'l_return_status', l_return_status);
123 END IF;
124
125 IF (l_return_status IS NULL) THEN
126 l_return_status := FND_API.g_ret_sts_success;
127 END IF;
128
129 IF (l_return_status = FND_API.g_ret_sts_error) THEN
130 RAISE FND_API.g_exc_error;
131 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
132 RAISE FND_API.g_exc_unexpected_error;
133 END IF;
134
135 IF l_so_updatable_flag = 'Y' THEN
136 IF g_debug_stmt THEN
137 PO_DEBUG.debug_stmt(d_module, l_progress, 'Exiting from Loop');
138 END IF;
139
140 EXIT;
141 END IF;
142
143 END LOOP;
144
145 IF l_so_updatable_flag = 'Y' THEN
146 RETURN TRUE;
147 ELSE
148 RETURN FALSE;
149 END IF;
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 RETURN FALSE;
154
155 END isDropShipWithUpdateableSO;
156
157
158 --------------------------------------------------------------------------------
159 --Start of Comments
160 --Name: isPartialRcvBilled
161
162 --Function:
163 -- Checks if the entity(PO Headers/Line/Shipment) is either received or billed
164
165 --Parameters:
166 --IN:
167 -- p_entity_level
168 -- p_entity_id
169 -- p_document_type
170
171 --RETURNS :
172 -- TRUE if the document(Header/Line/shipment) is either Received or billed
173 -- FALSE if the document(Header/Line/shipment) is neither Received nor billed
174
175 --End of Comments
176 --------------------------------------------------------------------------------
177
178 FUNCTION isPartialRcvBilled(
179 p_api_version IN NUMBER,
180 p_init_msg_list IN VARCHAR2,
181 p_entity_level IN VARCHAR2,
182 p_document_type IN VARCHAR2,
183 p_entity_id IN NUMBER)
184 RETURN BOOLEAN
185
186 IS
187
188 d_api_version CONSTANT NUMBER := 1.0;
189 d_api_name CONSTANT VARCHAR2(30) := 'isPartialRcvBilled.';
190 d_module CONSTANT VARCHAR2(100) := g_module_prefix || d_api_name;
191
192 l_progress VARCHAR2(8) := '000' ;
193 l_partial_rcv_bld NUMBER;
194 l_return_status BOOLEAN;
195
196 BEGIN
197 -- Start standard API initialization
198 IF FND_API.to_boolean(p_init_msg_list) THEN
199 FND_MSG_PUB.initialize;
200 END IF;
201
202 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
203 d_api_name, g_pkg_name) THEN
204 RAISE FND_API.g_exc_unexpected_error;
205 END IF;
206 l_partial_rcv_bld := 0;
207 l_return_status := FALSE;
208
209 IF g_debug_stmt THEN
210 PO_DEBUG.debug_begin(d_module);
211 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
212 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
213 END IF;
214
215 BEGIN
216 l_progress := '001' ;
217
218 IF (p_entity_level = c_entity_level_HEADER) THEN
219 --Canceling Header
220 l_progress := '002';
221 SELECT 1
222 INTO l_partial_rcv_bld
223 FROM DUAL
224 WHERE EXISTS (SELECT 1
225 FROM po_line_locations
226 WHERE ( (p_document_type <>c_doc_type_RELEASE
227 AND po_header_id = p_entity_id)
228 OR(p_document_type = c_doc_type_RELEASE
229 AND po_release_id = p_entity_id)
230 )
231 AND NVL(cancel_flag, 'N') <> 'Y'
232 AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
233 AND (quantity_received > 0
234 OR quantity_billed > 0 ));
235
236 ELSIF (p_entity_level = c_entity_level_LINE) THEN
237 --Canceling Line
238 l_progress := '003' ;
239 SELECT 1
240 INTO l_partial_rcv_bld
241 FROM DUAL
242 WHERE EXISTS (SELECT 1
243 FROM po_line_locations
244 WHERE po_line_id = p_entity_id
245 AND NVL(cancel_flag, 'N') <> 'Y'
246 AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
247 AND(quantity_received > 0
248 OR quantity_billed > 0 ));
249
250 ELSE
251 --Canceling Shipment
252 l_progress := '004';
253 SELECT 1
254 INTO l_partial_rcv_bld
255 FROM DUAL
256 WHERE EXISTS(SELECT 1
257 FROM po_line_locations
258 WHERE line_location_id = p_entity_id
259 AND NVL(cancel_flag, 'N') <> 'Y'
260 AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
261 AND ( quantity_received > 0 OR quantity_billed > 0));
262
263 END IF;
264
265 IF g_debug_stmt THEN
266 PO_DEBUG.debug_var(d_module, l_progress, 'l_partial_rcv_bld', l_partial_rcv_bld);
267 END IF;
268
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 l_partial_rcv_bld := 0 ;
272 IF g_debug_stmt THEN
273 PO_DEBUG.debug_stmt(d_module, l_progress, 'Set l_partial_rcv_bld = 0');
274 END IF ;
275 WHEN OTHERS THEN
276 IF g_debug_stmt THEN
277 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
278 PO_DEBUG.debug_stmt(d_module, l_progress, 'Exception in isPartialRcvBilled check');
279 END IF ;
280 END ;
281
282 IF l_partial_rcv_bld > 0 THEN
283 l_return_status := TRUE;
284 END IF;
285
286 IF g_debug_stmt THEN
287 PO_DEBUG.debug_var(d_module, l_progress, 'l_return_status', l_return_status);
288 END IF;
289
290 RETURN l_return_status;
291
292 END isPartialRcvBilled;
293
294
295 --------------------------------------------------------------------------------
296 --Start of Comments
297 --Name: init_recreate_demand_flag
298
299 --Function:
300 -- Initialize the recreate demad flag for each entity being cancelled
301 -- Logic: The flag value is calculated based on the following conditions:
302 -- 1.Cancel Requistion Option value in Purchasing Options
303 -- If the Purchasing Option Value is Always and user i/p value for Cancel
304 -- reqs flag is No then update the cancel Reqs falg to Y.
305 -- If the Purchasing Option Value is Never and user i/p value for Cancel
306 -- reqs flag is Yes then update the cancel Reqs falg to N.
307 -- Otherwise, Consider User i/p value of Cancel Reqs falg.
308 --
309 -- 2.CTO Order
310 -- If the entity (Header/Line/Shipment) being cancelled belong to a CTO
311 -- order, then do not recreate demand.
312 --
313 -- 3.Complex PO /CLM Po with partially Received/Billed shipments associated.
314 -- If the entity (Header/Line/Shipment) being cancelled belong to a CLM /
315 -- Complex order and the entity is partially rcvd/billed, then do not
316 -- recreate demand
317 --
318 -- 4.Drop Shipment with an updatable Sales Order Lines check
319 -- If any shipemnt associated with currecnt entity being cancelled is a
320 -- Drop Shipment with an updatable SO, then impose recreate demand.
321 --
322 --
323 --Parameters:
324 --IN:
325 -- p_cancel_reqs_flag
326 -- p_entity_dtl
327
328 --IN OUT:
329
330 -- OUT:
331 -- x_return_status
332 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
333 -- FND_API.G_RET_STS_ERROR if procedure fails
334 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
335
336 -- x_msg_data
337
338 --End of Comments
339 --------------------------------------------------------------------------------
340
341
342
343 PROCEDURE init_recreate_demand_flag(
344 p_api_version IN NUMBER,
345 p_init_msg_list IN VARCHAR2,
346 p_cancel_reqs_flag IN OUT NOCOPY VARCHAR2,
347 p_entity_dtl IN OUT NOCOPY po_document_action_pvt.entity_dtl_rec_type_tbl,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_msg_data OUT NOCOPY VARCHAR2)
350 IS
351
352 d_api_version CONSTANT NUMBER := 1.0;
353 d_api_name CONSTANT VARCHAR2(30) := 'init_recreate_demand_flag.';
354 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
355
356
357 l_progress VARCHAR2(3) := '000' ;
358
359 BEGIN
360 -- Start standard API initialization
361 IF FND_API.to_boolean(p_init_msg_list) THEN
362 FND_MSG_PUB.initialize;
363 END IF;
364
365 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
366 d_api_name, g_pkg_name) THEN
367 RAISE FND_API.g_exc_unexpected_error;
368 END IF;
369
370
371 IF g_debug_stmt THEN
372 PO_DEBUG.debug_begin(d_module);
373 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reqs_flag', p_cancel_reqs_flag);
374 END IF;
375
376 x_return_status := FND_API.G_RET_STS_SUCCESS;
377 x_msg_data := NULL;
378 l_progress := '001';
379
380 -- for each entity id in the entity record table
381 FOR i IN 1..p_entity_dtl.Count LOOP
382
383 IF g_debug_stmt THEN
384 PO_DEBUG.debug_var(d_module, l_progress, ' i', i);
385 PO_DEBUG.debug_var(d_module, l_progress,'entity recreate_demand_flag', p_entity_dtl(i).recreate_demand_flag);
386
387 END IF;
388
389 IF p_entity_dtl(i).process_entity_flag = 'Y' THEN
390
391 IF p_cancel_reqs_flag ='Y' THEN
392 p_entity_dtl(i).recreate_demand_flag := 'N';
393 ELSE
394 p_entity_dtl(i).recreate_demand_flag := 'Y';
395 END IF;
396
397
398 IF p_entity_dtl(i).recreate_demand_flag = 'Y' THEN
399
400 l_progress := '006';
401
402 IF is_document_cto_order(
403 p_entity_dtl(i).doc_id,
404 p_entity_dtl(i).document_type)
405 THEN
406 l_progress := '007';
407 p_entity_dtl(i).recreate_demand_flag := 'N';
408
409 ELSE
410 l_progress := '008';
411 -- If the document is s Complex PO/CLM PO and is partially rcvd/billed,
412 -- then do not recreate demand
413
414 IF p_entity_dtl(i).document_type <> c_doc_type_RELEASE
415 AND
416 PO_COMPLEX_WORK_PVT.is_complex_work_po(
417 p_entity_dtl(i).doc_id)
418 OR (po_clm_intg_grp.is_clm_po(
419 p_po_header_id => p_entity_dtl(i).doc_id) = 'Y')
420 THEN
421 l_progress := '009';
422 IF isPartialRcvBilled(
423 p_api_version => 1.0,
424 p_init_msg_list => FND_API.G_FALSE,
425 p_entity_level=> p_entity_dtl(i).entity_level,
426 p_document_type=> p_entity_dtl(i).document_type,
427 p_entity_id=>p_entity_dtl(i).entity_id)
428 THEN
429 l_progress := '010';
430 p_entity_dtl(i).recreate_demand_flag := 'N';
431 END IF;
432
433 END IF; --if is_complex_work_po
434 END IF; -- if is_document_cto_order
435
436 ELSE
437 l_progress := '011';
438
439 IF NOT is_document_cto_order(
440 p_doc_id =>p_entity_dtl(i).doc_id,
441 p_doc_type =>p_entity_dtl(i).document_type)
442 THEN
443
444 l_progress := '012';
445 -- If any shipemnt associated with currecnt entity being cancelled
446 -- is a Drop Shipment with an updatable SO, then impose recreate demand.
447 IF isDropShipWithUpdateableSO(
448 p_api_version => 1.0,
449 p_init_msg_list => FND_API.G_FALSE,
450 p_entity_level=>p_entity_dtl(i).entity_level,
451 p_entity_id=>p_entity_dtl(i).entity_id,
452 p_document_type=>p_entity_dtl(i).document_type)
453 THEN
454
455 l_progress := '013';
456 p_entity_dtl(i).recreate_demand_flag := 'Y';
457
458 END IF; -- if isDropShipWithUpdateableSO
459
460 END IF; -- is_document_cto_order
461 END IF; -- if recreate_demand_flag ='Y'
462 END IF; --if process_entity_flag='Y'
463
464 IF g_debug_stmt THEN
465 PO_DEBUG.debug_var(
466 d_module,
467 l_progress,
468 'entity recreate_demand_flag',
469 p_entity_dtl(i).recreate_demand_flag);
470 END IF;
471
472 END LOOP;
473
474 EXCEPTION
475 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
476 x_msg_data := FND_MSG_PUB.GET(
477 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
478 P_ENCODED => 'F');
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
480 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
481
482 WHEN FND_API.G_EXC_ERROR THEN
483 x_msg_data := FND_MSG_PUB.GET(
484 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
485 P_ENCODED => 'F');
486 x_return_status := FND_API.G_RET_STS_ERROR;
487 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
488
489 WHEN OTHERS THEN
490 IF (G_DEBUG_UNEXP) THEN
491 FND_LOG.STRING(
492 FND_LOG.LEVEL_UNEXPECTED,
493 d_module || '.OTHERS_EXCEPTION',
494 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
495 END IF;
496 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
497
498 x_msg_data := FND_MSG_PUB.GET(
499 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
500 P_ENCODED => 'F');
501 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
502
503 END init_recreate_demand_flag;
504
505 --------------------------------------------------------------------------------
506 --Start of Comments
507 --Name: denormPlannedPOQty
508
509 --Function:
510 -- Updates the Qty Recvd and Qty billed of schedule releases to Planned Po
511 -- shipemnts/Distributions.This way we can treat the cancellation as a regular
512 -- standard PO case.
513 --
514 --
515 --Parameters:
516 --IN:
517 -- p_cancel_reqs_flag
518 -- p_key
519
520 --IN OUT:
521
522 -- OUT:
523 -- x_return_status
524 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
525 -- FND_API.G_RET_STS_ERROR if procedure fails
526 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
527
528
529
530 --End of Comments
531 --------------------------------------------------------------------------------
532
533 PROCEDURE denormPlannedPOQty(
534 p_api_version IN NUMBER,
535 p_init_msg_list IN VARCHAR2,
536 entity_dtl_tbl IN po_document_action_pvt.entity_dtl_rec_type_tbl,
537 p_key IN po_session_gt.key%TYPE,
538 x_return_status OUT NOCOPY VARCHAR2)
539 IS
540
541 d_api_version CONSTANT NUMBER := 1.0;
542 d_api_name CONSTANT VARCHAR2(30) := 'denormPlannedPOQty.';
543 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
544 l_progress VARCHAR2(3) := '000' ;
545
546 BEGIN
547 -- Start standard API initialization
548 IF FND_API.to_boolean(p_init_msg_list) THEN
549 FND_MSG_PUB.initialize;
550 END IF;
551
552 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
553 d_api_name, g_pkg_name) THEN
554 RAISE FND_API.g_exc_unexpected_error;
555 END IF;
556
557
558 IF g_debug_stmt THEN
559 PO_DEBUG.debug_begin(d_module);
560 PO_DEBUG.debug_var(d_module, l_progress, 'p_key', p_key);
561 END IF;
562
563 l_progress := '001' ;
564
565 UPDATE po_line_locations POLL
566 SET (POLL.quantity_billed, POLL.quantity_received) =
567 (SELECT
568 SUM( NVL(RELS.quantity_billed, 0) ),
569 SUM( NVL(RELS.quantity_received, 0) )
570 FROM
571 PO_LINE_LOCATIONS RELS
572 WHERE
573 RELS.source_shipment_id = POLL.line_location_id )
574 WHERE POLL.shipment_type = 'PLANNED'
575 AND POLL.line_location_id IN
576 (SELECT num1
577 FROM po_session_gt
578 WHERE char3 = c_entity_level_SHIPMENT
579 AND char2 = c_doc_subtype_PLANNED
580 UNION
581 SELECT line_location_id
582 FROM po_line_locations,
583 po_session_gt
584 WHERE num1 = po_line_id
585 AND char3 = c_entity_level_LINE
586 AND char2 = c_doc_subtype_PLANNED
587 AND NVL(cancel_flag, 'N') = 'N'
588 AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
589 UNION
590 SELECT line_location_id
591 FROM po_line_locations,
592 po_session_gt
593 WHERE num1 = po_header_id
594 AND char3 = c_entity_level_HEADER
595 AND char2 = c_doc_subtype_PLANNED
596 AND NVL(cancel_flag, 'N') = 'N'
597 AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
598 );
599
600 IF g_debug_stmt THEN
601 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated the PO line locations table', SQL%ROWCOUNT);
602 END IF;
603
604 l_progress := '002' ;
605
606 UPDATE PO_DISTRIBUTIONS_ALL POD
607 SET (POD.quantity_billed,POD.quantity_delivered) =
608 ( SELECT SUM( NVL(RELD.quantity_billed, 0) ),
609 SUM( NVL(RELD.quantity_delivered, 0) )
610 FROM PO_DISTRIBUTIONS RELD
611 WHERE RELD.source_distribution_id = POD.po_distribution_id )
612 WHERE POD.line_location_id IN
613 ( SELECT num1
614 FROM po_session_gt
615 WHERE char3= c_entity_level_SHIPMENT
616 AND char2 = c_doc_subtype_PLANNED
617 AND char5 = 'Y'
618 UNION
619 SELECT line_location_id
620 FROM po_line_locations,
621 po_session_gt
622 WHERE num1 = po_line_id
623 AND shipment_type = 'PLANNED'
624 AND char3 = c_entity_level_LINE
625 AND char2 = c_doc_subtype_PLANNED
626 AND NVL(cancel_flag, 'N') = 'N'
627 AND char5 = 'Y'
628 AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
629 UNION
630 SELECT line_location_id
631 FROM po_line_locations,
632 po_session_gt
633 WHERE num1 = po_header_id
634 AND shipment_type = 'PLANNED'
635 AND char3 = c_entity_level_HEADER
636 AND char2 = c_doc_subtype_PLANNED
637 AND NVL(cancel_flag, 'N') = 'N'
638 AND char5 = 'Y'
639 AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED') ;
640
641 IF g_debug_stmt THEN
642 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated the PO Distributions table', SQL%ROWCOUNT);
643 END IF;
644
645 EXCEPTION
646 WHEN FND_API.g_exc_error THEN
647 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
648 x_return_status := FND_API.g_ret_sts_error;
649 WHEN FND_API.g_exc_unexpected_error THEN
650 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
651 x_return_status := FND_API.g_ret_sts_unexp_error;
652 WHEN OTHERS THEN
653 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
654 x_return_status := FND_API.g_ret_sts_unexp_error;
655 END denormPlannedPOQty;
656
657 --------------------------------------------------------------------------------
658 --Start of Comments
659 --Name: update_dist_cancel
660
661 --Function:
662 --
663 -- Updates the Cancel details on PO Distribution if the entity being canceled is
664 -- PO/Release Header, PO Line, PO/Release Shipment
665
666 -- Updates the following columns :
667 -- Before Funds Control call
668 -- -------------------------
669 -- quantity_cancelled
670 -- amount_cancelled
671 --
672 -- Afer Funds Control Call
673 -- -------------------------
674 -- gl_cancelled_date
675 -- req_distribution_id
676 -- last_update_date
677 -- last_updated_by
678 -- last_update_login
679 --
680 --Parameters:
681 --IN:
682 -- p_fc_level
683 -- p_action_date
684 -- p_entity_level
685 -- p_entity_id
686 -- p_document_type
687 -- p_recreate_demand
688 -- p_user_id
689 -- p_login_id
690
691
692 --IN OUT:
693
694 -- OUT:
695 -- x_return_status
696 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
697 -- FND_API.G_RET_STS_ERROR if procedure fails
698 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
699
700
701
702 --End of Comments
703 --------------------------------------------------------------------------------
704
705 PROCEDURE update_dist_cancel(
706 p_api_version IN NUMBER,
707 p_init_msg_list IN VARCHAR2,
708 p_fc_level IN VARCHAR2,
709 p_action_date IN DATE,
710 p_entity_level IN VARCHAR2,
711 p_entity_id IN NUMBER,
712 p_document_type IN VARCHAR2,
713 p_recreate_demand IN VARCHAR2,
714 p_user_id IN po_lines.last_updated_by%TYPE,
715 p_login_id IN po_lines.last_update_login%TYPE,
716 x_return_status OUT NOCOPY VARCHAR2)
717 IS
718
719 d_api_name CONSTANT VARCHAR2(30) := 'update_dist_cancel';
720 d_api_version CONSTANT NUMBER := 1.0;
721 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
722
723 l_progress VARCHAR2(3) := '000' ;
724
725 BEGIN
726 -- Start standard API initialization
727 IF FND_API.to_boolean(p_init_msg_list) THEN
728 FND_MSG_PUB.initialize;
729 END IF;
730
731 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
732 d_api_name, g_pkg_name) THEN
733 RAISE FND_API.g_exc_unexpected_error;
734 END IF;
735
736
737 IF g_debug_stmt THEN
738 PO_DEBUG.debug_begin(d_module);
739 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
740 PO_DEBUG.debug_var(d_module, l_progress, 'p_fc_level', p_fc_level);
741 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
742 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
743 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
744 PO_DEBUG.debug_var(d_module, l_progress, 'p_recreate_demand', p_recreate_demand);
745 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
746 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
747 END IF;
748
749 IF p_fc_level = c_before_FC THEN
750
751 l_progress := '001' ;
752
753 UPDATE
754 PO_DISTRIBUTIONS_ALL POD
755 SET
756 pod.quantity_cancelled = pod.quantity_ordered-greatest(
757 NVL(quantity_delivered,0),
758 NVL(quantity_financed,0),
759 NVL(quantity_billed,0)),
760 pod.amount_cancelled = pod.amount_ordered-greatest(
761 NVL(amount_delivered,0),
762 NVL(amount_financed,0),
763 NVL(amount_billed,0)),
764 pod.last_update_date = SYSDATE ,
765 pod.last_updated_by = p_user_id ,
766 pod.last_update_login = p_login_id
767 WHERE pod.line_location_id IN
768 ( SELECT line_location_id
769 FROM po_line_locations
770 WHERE line_location_id = p_entity_id
771 AND p_entity_level = c_entity_level_SHIPMENT
772 UNION ALL
773 SELECT line_location_id
774 FROM po_line_locations
775 WHERE po_line_id = p_entity_id
776 AND p_entity_level = c_entity_level_LINE
777 UNION ALL
778 SELECT line_location_id
779 FROM po_line_locations
780 WHERE po_header_id = p_entity_id
781 AND p_document_type <> c_doc_type_RELEASE
782 AND p_entity_level = c_entity_level_HEADER
783 UNION ALL
784 SELECT line_location_id
785 FROM po_line_locations
786 WHERE po_release_id = p_entity_id
787 AND p_document_type = c_doc_type_RELEASE
788 AND p_entity_level = c_entity_level_HEADER);
789
790
791
792
793 ELSE
794
795 l_progress := '002' ;
796
797 UPDATE PO_DISTRIBUTIONS_ALL POD
798 SET pod.gl_cancelled_date = p_action_date,
799 pod.req_distribution_id = Decode(
800 p_recreate_demand,
801 'Y',
802 DECODE(
803 greatest(
804 NVL(pod.quantity_delivered, 0),
805 NVL(pod.quantity_billed, 0)),
806 0,
807 NULL,
808 pod.req_distribution_id),
809 pod.req_distribution_id),
810 pod.last_update_date = SYSDATE ,
811 pod.last_updated_by = p_user_id,
812 pod.last_update_login = p_login_id
813 WHERE pod.line_location_id IN
814 ( SELECT line_location_id
815 FROM po_line_locations
816 WHERE line_location_id = p_entity_id
817 AND p_entity_level = c_entity_level_SHIPMENT
818 UNION ALL
819 SELECT line_location_id
820 FROM po_line_locations
821 WHERE po_line_id = p_entity_id
822 AND p_entity_level = c_entity_level_LINE
823 UNION ALL
824 SELECT line_location_id
825 FROM po_line_locations
826 WHERE po_header_id = p_entity_id
827 AND p_document_type <> c_doc_type_RELEASE
828 AND p_entity_level = c_entity_level_HEADER
829 UNION ALL
830 SELECT line_location_id
831 FROM po_line_locations
832 WHERE po_release_id = p_entity_id
833 AND p_document_type = c_doc_type_RELEASE
834 AND p_entity_level = c_entity_level_HEADER);
835
836
837 END IF;
838
839 IF g_debug_stmt THEN
840 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Distributions table', SQL%ROWCOUNT);
841 END IF;
842
843 EXCEPTION
844
845 WHEN FND_API.g_exc_error THEN
846 x_return_status := FND_API.g_ret_sts_error;
847 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
848 WHEN FND_API.g_exc_unexpected_error THEN
849 x_return_status := FND_API.g_ret_sts_unexp_error;
850 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
851 WHEN OTHERS THEN
852 x_return_status := FND_API.g_ret_sts_unexp_error;
853 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
854 END update_dist_cancel;
855
856
857 --------------------------------------------------------------------------------
858 --Start of Comments
859 --Name: update_ship_cancel
860
861 --Function:
862 --
863 -- Updates the Cancel details on PO Shipments if the entity being canceled is
864 -- PO/Release Header, PO Line, PO/Release Shipment.
865
866 -- Updates the following columns :
867 -- Before Funds Control call
868 -- -------------------------
869 -- cancel_flag ='I'
870 -- cancel_date
871 -- cancel_reason
872 -- cancelled_by
873 -- last_update_date
874 -- last_updated_by
875 -- last_update_login
876 -- quantity_cancelled - sum(Distributions cancelled quantity)
877 -- amount_cancelled - sum(Distributions cancelled amount)
878 --
879 -- Afer Funds Control Call
880 -- -------------------------
881 -- cancel_flag ='Y'
882
883 --
884 --Parameters:
885 --IN:
886 -- p_fc_level
887 -- p_action_date
888 -- p_entity_level
889 -- p_entity_id
890 -- p_action_date
891 -- p_document_type
892 -- p_recreate_demand
893 -- p_user_id
894 -- p_login_id
895
896
897 --IN OUT:
898
899 -- OUT:
900 -- x_return_status
901 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
902 -- FND_API.G_RET_STS_ERROR if procedure fails
903 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
904
905
906
907 --End of Comments
908 --------------------------------------------------------------------------------
909
910 PROCEDURE update_ship_cancel(
911 p_api_version IN NUMBER,
912 p_init_msg_list IN VARCHAR2,
913 p_fc_level IN VARCHAR2,
914 p_cancel_reason IN VARCHAR2,
915 p_entity_level IN VARCHAR2,
916 p_entity_id IN NUMBER,
917 p_action_date IN DATE,
918 p_document_type IN VARCHAR2,
919 p_user_id IN po_lines.last_updated_by%TYPE,
920 p_login_id IN po_lines.last_update_login%TYPE,
921 x_return_status IN OUT NOCOPY VARCHAR2)
922 IS
923
924 d_api_name CONSTANT VARCHAR2(30) := 'update_ship_cancel';
925 d_api_version CONSTANT NUMBER := 1.0;
926 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
927
928 l_progress VARCHAR2(3) := '000' ;
929 l_emp_id NUMBER := FND_GLOBAL.employee_id;
930 l_request_id PO_HEADERS.request_id%TYPE := fnd_global.conc_request_id;
931
932 BEGIN
933 -- Start standard API initialization
934 IF FND_API.to_boolean(p_init_msg_list) THEN
935 FND_MSG_PUB.initialize;
936 END IF;
937
938 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
939 d_api_name, g_pkg_name) THEN
940 RAISE FND_API.g_exc_unexpected_error;
941 END IF;
942
943
944
945 IF g_debug_stmt THEN
946 PO_DEBUG.debug_begin(d_module);
947 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reason', p_cancel_reason);
948 PO_DEBUG.debug_var(d_module, l_progress, 'p_fc_level', p_fc_level);
949 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
950 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
951 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
952 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
953 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
954 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
955
956 END IF;
957
958
959
960 IF p_fc_level = c_before_FC THEN
961
962 l_progress := '001' ;
963
964 UPDATE po_line_locations POLL
965 SET POLL.cancel_flag = 'I' ,
966 --Bug 16575765: CANCEL_DATE is always stamped(SHIPMENT) as sysdate.
967 --POLL.cancel_date = p_action_date ,
968 POLL.cancel_date = SYSDATE ,
969 POLL.cancel_reason = p_cancel_reason,
970 POLL.cancelled_by = l_emp_id ,
971 POLL.last_update_date = SYSDATE ,
972 POLL.last_updated_by = p_user_id ,
973 POLL.last_update_login = p_login_id ,
974 POLL.request_id = DECODE(l_request_id,
975 NULL,
976 request_id,
977 -1,
978 request_id,
979 l_request_id) ,
980 POLL.quantity_cancelled = (SELECT SUM(NVL(POD.quantity_cancelled,0))
981 FROM PO_DISTRIBUTIONS_ALL POD
982 WHERE POD.line_location_id=POLL.line_location_id),
983 POLL.amount_cancelled = (SELECT SUM(NVL(POD.amount_cancelled, 0))
984 FROM PO_DISTRIBUTIONS_ALL POD
985 WHERE POD.line_location_id=POLL.line_location_id)
986 WHERE NVL(poll.cancel_flag, 'N') = 'N'
987 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
988 AND POLL.line_location_id IN
989 ( SELECT line_location_id
990 FROM po_line_locations
991 WHERE line_location_id = p_entity_id
992 AND p_entity_level = c_entity_level_SHIPMENT
993 UNION ALL
994 SELECT line_location_id
995 FROM po_line_locations
996 WHERE po_line_id = p_entity_id
997 AND p_entity_level = c_entity_level_LINE
998 UNION ALL
999 SELECT line_location_id
1000 FROM po_line_locations
1001 WHERE po_header_id = p_entity_id
1002 AND p_document_type <> c_doc_type_RELEASE
1003 AND p_entity_level = c_entity_level_HEADER
1004 UNION ALL
1005 SELECT line_location_id
1006 FROM po_line_locations
1007 WHERE po_release_id = p_entity_id
1008 AND p_document_type = c_doc_type_RELEASE
1009 AND p_entity_level = c_entity_level_HEADER);
1010
1011
1012 ELSE
1013
1014 l_progress := '002' ;
1015
1016 UPDATE po_line_locations POLL
1017 SET POLL.cancel_flag = 'Y'
1018 WHERE NVL(poll.cancel_flag, 'N') = 'I'
1019 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1020 AND POLL.line_location_id IN
1021 ( SELECT line_location_id
1022 FROM po_line_locations
1023 WHERE line_location_id = p_entity_id
1024 AND p_entity_level = c_entity_level_SHIPMENT
1025 UNION ALL
1026 SELECT line_location_id
1027 FROM po_line_locations
1028 WHERE po_line_id = p_entity_id
1029 AND p_entity_level = c_entity_level_LINE
1030 UNION ALL
1031 SELECT line_location_id
1032 FROM po_line_locations
1033 WHERE po_header_id = p_entity_id
1034 AND p_document_type <> c_doc_type_RELEASE
1035 AND p_entity_level = c_entity_level_HEADER
1036 UNION ALL
1037 SELECT line_location_id
1038 FROM po_line_locations
1039 WHERE po_release_id = p_entity_id
1040 AND p_document_type = c_doc_type_RELEASE
1041 AND p_entity_level = c_entity_level_HEADER);
1042
1043
1044 END IF;
1045
1046
1047 IF g_debug_stmt THEN
1048 PO_DEBUG.debug_var(
1049 d_module,
1050 l_progress,
1051 'Rows Updated in PO LineLocations table',
1052 SQL%ROWCOUNT);
1053 END IF;
1054
1055
1056 EXCEPTION
1057 WHEN FND_API.g_exc_error THEN
1058 x_return_status := FND_API.g_ret_sts_error;
1059 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1060 WHEN FND_API.g_exc_unexpected_error THEN
1061 x_return_status := FND_API.g_ret_sts_unexp_error;
1062 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1063 WHEN OTHERS THEN
1064 x_return_status := FND_API.g_ret_sts_unexp_error;
1065 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1066
1067 END update_ship_cancel;
1068
1069
1070
1071 --------------------------------------------------------------------------------
1072 --Start of Comments
1073 --Name: update_line_qty_price_amt
1074
1075 --Function:
1076 --
1077 -- Updates the Quantity, Price and Amount on the line when its shipment
1078 -- is canceled
1079 --
1080 --Parameters:
1081 -- IN:
1082 -- p_ship_id
1083
1084 -- IN OUT:
1085
1086 -- OUT:
1087 -- x_return_status
1088 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1089 -- FND_API.G_RET_STS_ERROR if procedure fails
1090 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1091
1092 --End of Comments
1093 --------------------------------------------------------------------------------
1094
1095 PROCEDURE update_line_qty_price_amt(
1096 p_api_version IN NUMBER,
1097 p_init_msg_list IN VARCHAR2,
1098 p_ship_id IN NUMBER,
1099 x_return_status OUT NOCOPY VARCHAR2)
1100 IS
1101
1102 d_api_version CONSTANT NUMBER := 1.0;
1103 d_api_name CONSTANT VARCHAR2(30) := 'update_line_qty_price_amt';
1104 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
1105
1106 l_progress VARCHAR2(3) := '000' ;
1107 l_emp_id NUMBER := FND_GLOBAL.employee_id;
1108
1109 BEGIN
1110 -- Start standard API initialization
1111 IF FND_API.to_boolean(p_init_msg_list) THEN
1112 FND_MSG_PUB.initialize;
1113 END IF;
1114
1115 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
1116 d_api_name, g_pkg_name) THEN
1117 RAISE FND_API.g_exc_unexpected_error;
1118 END IF;
1119
1120
1121 IF g_debug_stmt THEN
1122 PO_DEBUG.debug_begin(d_module);
1123 PO_DEBUG.debug_var(d_module, l_progress, 'p_ship_id', p_ship_id);
1124 END IF;
1125
1126 l_progress := '001' ;
1127
1128 UPDATE po_lines pol
1129 SET pol.quantity =(SELECT SUM(
1130 NVL(poll.quantity,0)
1131 - NVL(poll.quantity_cancelled, 0))
1132 FROM po_line_locations POLL
1133 WHERE poll.po_line_id = pol.po_line_id
1134 AND poll.shipment_type IN ('STANDARD','PLANNED'))
1135
1136 WHERE pol.po_line_id=(SELECT po_line_id
1137 FROM po_line_locations
1138 WHERE line_location_id = p_ship_id)
1139 AND pol.order_type_lookup_code IN ('QUANTITY','AMOUNT')
1140 AND NOT EXISTS (SELECT
1141 'PO Line has Qty Milestone Pay Items'
1142 FROM po_line_locations poll2
1143 WHERE poll2.po_line_id=pol.po_line_id
1144 AND poll2.payment_type IS NOT NULL);
1145
1146 IF g_debug_stmt THEN
1147 PO_DEBUG.debug_var(d_module, l_progress, 'Quanity Updated in PO Lines table', SQL%ROWCOUNT);
1148 END IF;
1149
1150
1151 l_progress := '002' ;
1152
1153 -- For Qty-based lines, only do the update of line price for the
1154 -- Complex Work Qty Milestone case.
1155 -- Cancellation of normal PO shipment does not change Line Price
1156
1157 UPDATE po_lines pol
1158 SET pol.unit_price =
1159 (SELECT SUM(poll.price_override)
1160 FROM po_line_locations POLL
1161 WHERE poll.po_line_id = pol.po_line_id
1162 AND poll.shipment_type = 'STANDARD'
1163 AND nvl(poll.cancel_flag, 'N') = 'N'
1164 AND nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
1165
1166 WHERE pol.po_line_id=(SELECT po_line_id
1167 FROM po_line_locations
1168 WHERE line_location_id= p_ship_id)
1169 AND pol.order_type_lookup_code IN ('QUANTITY', 'AMOUNT')
1170 AND EXISTS
1171 (SELECT 'PO Line has open Qty Milestone Pay Items'
1172 FROM po_line_locations poll2
1173 WHERE poll2.po_line_id = pol.po_line_id
1174 AND poll2.payment_type IS NOT NULL
1175 AND nvl(poll2.cancel_flag, 'N') = 'N'
1176 AND nvl(poll2.closed_code, 'OPEN') <> 'FINALLY CLOSED');
1177
1178
1179
1180 IF g_debug_stmt THEN
1181 PO_DEBUG.debug_var(d_module, l_progress, 'Price Updated in PO Lines table', SQL%ROWCOUNT);
1182 END IF;
1183
1184
1185 l_progress := '003' ;
1186
1187 -- For Services lines, we can have a mix
1188 -- of Quantity and Amount based line locations (pay item case).
1189 -- The logic below will also work for the non-mixed case, as
1190 -- exists with non-Complex Work shipments
1191
1192 UPDATE po_lines pol
1193 SET pol.amount= (SELECT SUM(DECODE(
1194 POLL.amount,
1195 NULL,
1196 --Quantity or Amount Line Locations
1197 ((NVL(poll.quantity,0) - NVL(poll.quantity_cancelled,0))
1198 * POLL.price_override),
1199 -- Fixed Price or Rate Line Locations
1200 (NVL(poll.amount, 0) - NVL(poll.amount_cancelled,0))
1201 ))
1202 FROM po_line_locations POLL
1203 WHERE poll.po_line_id = pol.po_line_id
1204 AND poll.shipment_type IN ('STANDARD','PLANNED'))
1205
1206 WHERE pol.po_line_id =(SELECT po_line_id
1207 FROM po_line_locations
1208 WHERE line_location_id= p_ship_id)
1209 AND pol.order_type_lookup_code in ('FIXED PRICE', 'RATE');
1210
1211
1212 IF g_debug_stmt THEN
1213 PO_DEBUG.debug_var(d_module, l_progress, 'Amount Updated in PO Lines table', SQL%ROWCOUNT);
1214 END IF;
1215
1216
1217
1218 EXCEPTION
1219 WHEN FND_API.g_exc_error THEN
1220 x_return_status := FND_API.g_ret_sts_error;
1221 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1222 WHEN FND_API.g_exc_unexpected_error THEN
1223 x_return_status := FND_API.g_ret_sts_unexp_error;
1224 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1225 WHEN OTHERS THEN
1226 x_return_status := FND_API.g_ret_sts_unexp_error;
1227 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1228
1229 END update_line_qty_price_amt;
1230
1231
1232 --------------------------------------------------------------------------------
1233 --Start of Comments
1234 --Name: update_line_cancel
1235
1236 --Function:
1237 --
1238 -- Updates the Cancel details on PO Lines if the entity being canceled is
1239 -- PO/PA Header, PO/PA Line
1240
1241 -- Updates the following columns :
1242 -- Before Funds Control call
1243 -- -------------------------
1244 -- cancel_flag ='I'
1245 -- cancel_date
1246 -- cancel_reason
1247 -- cancelled_by
1248 -- last_update_date
1249 -- last_updated_by
1250 -- last_update_login
1251 -- quantity - sum(Shipments Open Quanity i.e. Quantity-Quantity Cancelled)
1252 -- amount - sum(Shipments Open Quanity i.e. Amount-Amount Cancelled)
1253 --
1254 -- Afer Funds Control Call
1255 -- -------------------------
1256 -- cancel_flag ='Y'
1257
1258 --Parameters:
1259 -- IN:
1260 -- p_fc_level
1261 -- p_cancel_reason
1262 -- p_entity_level
1263 -- p_entity_id
1264 -- p_action_date
1265 -- p_user_id
1266 -- p_login_id
1267 --
1268 -- IN OUT:
1269 --
1270 -- OUT:
1271 -- x_return_status
1272 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1273 -- FND_API.G_RET_STS_ERROR if procedure fails
1274 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1275
1276 --End of Comments
1277 --------------------------------------------------------------------------------
1278
1279
1280 PROCEDURE update_line_cancel(
1281 p_api_version IN NUMBER,
1282 p_init_msg_list IN VARCHAR2,
1283 p_fc_level IN VARCHAR2,
1284 p_cancel_reason IN VARCHAR2,
1285 p_entity_level IN VARCHAR2,
1286 p_entity_id IN NUMBER,
1287 p_action_date IN DATE,
1288 p_user_id IN po_lines.last_updated_by%TYPE,
1289 p_login_id IN po_lines.last_update_login%TYPE,
1290 p_note_to_vendor IN VARCHAR2,
1291 x_return_status OUT NOCOPY VARCHAR2)
1292 IS
1293
1294 d_api_name CONSTANT VARCHAR2(30) := 'update_line_cancel';
1295 d_api_version CONSTANT NUMBER := 1.0;
1296 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
1297
1298 l_progress VARCHAR2(3) := '000' ;
1299 l_emp_id NUMBER := FND_GLOBAL.employee_id;
1300 l_request_id PO_HEADERS.request_id%TYPE := fnd_global.conc_request_id;
1301
1302 BEGIN
1303 -- Start standard API initialization
1304 IF FND_API.to_boolean(p_init_msg_list) THEN
1305 FND_MSG_PUB.initialize;
1306 END IF;
1307
1308 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
1309 d_api_name, g_pkg_name) THEN
1310 RAISE FND_API.g_exc_unexpected_error;
1311 END IF;
1312
1313
1314 IF g_debug_stmt THEN
1315 PO_DEBUG.debug_begin(d_module);
1316 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reason', p_cancel_reason);
1317 PO_DEBUG.debug_var(d_module, l_progress, 'p_fc_level', p_fc_level);
1318 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
1319 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
1320 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
1321 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
1322 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
1323 PO_DEBUG.debug_var(d_module, l_progress, 'p_note_to_vendor', p_note_to_vendor);
1324
1325 END IF;
1326
1327
1328
1329
1330 IF p_fc_level = c_before_FC THEN
1331
1332 l_progress := '001' ;
1333
1334 UPDATE po_lines pol
1335 SET pol.cancel_flag = 'I',
1336 --Bug 16575765: CANCEL_DATE is always stamped(LINE) as sysdate.
1337 --pol.cancel_date = p_action_date,
1338 pol.cancel_date = SYSDATE,
1339 pol.cancel_reason = p_cancel_reason,
1340 pol.cancelled_by = l_emp_id,
1341 pol.last_update_date = sysdate,
1342 pol.last_updated_by = p_user_id,
1343 pol.last_update_login = p_login_id,
1344 pol.note_to_vendor =p_note_to_vendor ,
1345 pol.request_id = DECODE(l_request_id,
1346 NULL,
1347 request_id,
1348 -1,
1349 request_id,
1350 l_request_id) ,
1351
1352 pol.quantity =
1353 DECODE(pol.quantity,
1354 NULL,
1355 pol.quantity,
1356 (SELECT SUM(NVL(poll.quantity,0) -NVL(poll.quantity_cancelled,0))
1357 FROM po_line_locations POLL
1358 WHERE poll.po_line_id = pol.po_line_id
1359 AND poll.shipment_type IN('STANDARD','PLANNED'))
1360 ),
1361 pol.amount =
1362 DECODE(pol.amount,
1363 NULL,
1364 pol.amount,
1365 (SELECT SUM(DECODE(POLL.amount,
1366 NULL,
1367 ((NVL(poll.quantity,0) -NVL(poll.quantity_cancelled,0))
1368 * POLL.price_override),
1369 (NVL(poll.amount, 0) -NVL(poll.amount_cancelled,0))
1370 )
1371 )
1372 FROM po_line_locations POLL
1373 WHERE poll.po_line_id = pol.po_line_id
1374 AND poll.shipment_type IN ('STANDARD','PLANNED')) )
1375 WHERE nvl(pol.cancel_flag,'N') = 'N'
1376 AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
1377 AND pol.po_line_id IN (
1378 SELECT po_line_id
1379 FROM po_lines
1380 WHERE po_line_id= p_entity_id
1381 AND p_entity_level=c_entity_level_LINE
1382 UNION ALL
1383 SELECT po_line_id
1384 FROM po_lines
1385 WHERE po_header_id= p_entity_id
1386 AND p_entity_level=c_entity_level_HEADER);
1387
1388 ELSE
1389 l_progress := '002' ;
1390
1391 UPDATE po_lines POl
1392 SET pol.cancel_flag = 'Y'
1393 WHERE nvl(pol.cancel_flag,'N') = 'I'
1394 AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1395 AND pol.po_line_id IN (
1396 SELECT po_line_id
1397 FROM po_lines
1398 WHERE po_line_id= p_entity_id
1399 AND p_entity_level=c_entity_level_LINE
1400 UNION ALL
1401 SELECT po_line_id
1402 FROM po_lines
1403 WHERE po_header_id= p_entity_id
1404 AND p_entity_level=c_entity_level_HEADER);
1405
1406
1407 END IF;
1408
1409
1410 IF g_debug_stmt THEN
1411 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Lines table', SQL%ROWCOUNT);
1412 END IF;
1413
1414
1415 EXCEPTION
1416 WHEN FND_API.g_exc_error THEN
1417 x_return_status := FND_API.g_ret_sts_error;
1418 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1419 WHEN FND_API.g_exc_unexpected_error THEN
1420 x_return_status := FND_API.g_ret_sts_unexp_error;
1421 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1422 WHEN OTHERS THEN
1423 x_return_status := FND_API.g_ret_sts_unexp_error;
1424 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1425
1426 END update_line_cancel;
1427
1428 --------------------------------------------------------------------------------
1429 --Start of Comments
1430 --Name: update_po_header_cancel
1431
1432 --Function:
1433 --
1434 -- Updates the Cancel details on PO Headers if the entity being canceled is
1435 -- PO/PA Header
1436 -- Updates the following columns :
1437 --
1438 -- Before Funds Control call
1439 -- -------------------------
1440 -- cancel_flag ='I'
1441 -- last_update_date
1442 -- last_updated_by
1443 -- last_update_login
1444 -- acceptance_required_flag
1445 --
1446 -- Afer Funds Control Call
1447 -- -------------------------
1448 -- cancel_flag ='Y'
1449 -- closed_code = 'CLOSED'
1450 -- closed_date = sysdate
1451
1452 --Parameters:
1453 -- IN:
1454 -- p_fc_level
1455 -- p_entity_id
1456 -- p_user_id
1457 -- p_login_id
1458 -- p_action_date
1459 --
1460 -- IN OUT:
1461 --
1462 -- OUT:
1463 -- x_return_status
1464 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1465 -- FND_API.G_RET_STS_ERROR if procedure fails
1466 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1467
1468 --End of Comments
1469 --------------------------------------------------------------------------------
1470
1471
1472 PROCEDURE update_po_header_cancel(
1473 p_api_version IN NUMBER,
1474 p_init_msg_list IN VARCHAR2,
1475 p_fc_level IN VARCHAR2,
1476 p_entity_id IN NUMBER,
1477 p_action_date IN DATE,
1478 p_user_id IN po_lines.last_updated_by%TYPE,
1479 p_login_id IN po_lines.last_update_login%TYPE,
1480 p_note_to_vendor IN VARCHAR2,
1481 x_return_status OUT NOCOPY VARCHAR2)
1482 IS
1483
1484 d_api_name CONSTANT VARCHAR2(30) := 'update_po_header_cancel';
1485 d_api_version CONSTANT NUMBER := 1.0;
1486 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
1487
1488 l_progress VARCHAR2(3) := '000' ;
1489 l_emp_id NUMBER := FND_GLOBAL.employee_id;
1490 l_request_id PO_HEADERS.request_id%TYPE := fnd_global.conc_request_id;
1491
1492 BEGIN
1493 -- Start standard API initialization
1494 IF FND_API.to_boolean(p_init_msg_list) THEN
1495 FND_MSG_PUB.initialize;
1496 END IF;
1497
1498 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
1499 d_api_name, g_pkg_name) THEN
1500 RAISE FND_API.g_exc_unexpected_error;
1501 END IF;
1502
1503
1504 IF g_debug_stmt THEN
1505 PO_DEBUG.debug_begin(d_module);
1506 PO_DEBUG.debug_var(d_module, l_progress, 'p_fc_level', p_fc_level);
1507 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
1508 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
1509 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
1510 PO_DEBUG.debug_var(d_module, l_progress, 'p_note_to_vendor', p_note_to_vendor);
1511 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
1512
1513 END IF;
1514
1515
1516 IF p_fc_level = c_before_FC THEN
1517
1518 l_progress := '001' ;
1519
1520 UPDATE po_headers poh
1521 SET poh.cancel_flag = 'I' ,
1522 poh.last_update_date = SYSDATE ,
1523 poh.note_to_vendor =p_note_to_vendor ,
1524 poh.last_updated_by = p_user_id,
1525 poh.last_update_login = p_login_id,
1526 poh.acceptance_required_flag =NULL,
1527 poh.request_id = DECODE(l_request_id,
1528 NULL,
1529 request_id,
1530 -1,
1531 request_id,
1532 l_request_id)
1533
1534 WHERE poh.po_header_id= p_entity_id;
1535
1536
1537 ELSE
1538 l_progress := '002' ;
1539
1540
1541 UPDATE po_headers poh
1542 SET poh.cancel_flag = 'Y' ,
1543 poh.closed_code = 'CLOSED',
1544 --Bug 16575765: CLOSED_DATE is always stamped(HEADER) as sysdate.
1545 --poh.closed_date = p_action_date
1546 poh.closed_date = sysdate
1547 WHERE NVL(poh.cancel_flag, 'N') = 'I'
1548 AND poh.po_header_id= p_entity_id;
1549
1550 END IF;
1551
1552
1553 IF g_debug_stmt THEN
1554 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Headers table', SQL%ROWCOUNT);
1555 END IF;
1556
1557
1558 EXCEPTION
1559 WHEN FND_API.g_exc_error THEN
1560 x_return_status := FND_API.g_ret_sts_error;
1561 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1562 WHEN FND_API.g_exc_unexpected_error THEN
1563 x_return_status := FND_API.g_ret_sts_unexp_error;
1564 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1565 WHEN OTHERS THEN
1566 x_return_status := FND_API.g_ret_sts_unexp_error;
1567 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1568
1569 END update_po_header_cancel;
1570
1571
1572 --------------------------------------------------------------------------------
1573 --Start of Comments
1574 --Name: update_rel_header_cancel
1575
1576 --Function:
1577 --
1578 -- Updates the Cancel details on PO Releases if the entity being canceled is
1579 -- Release Header
1580 -- Updates the following columns :
1581 --
1582 -- Before Funds Control call
1583 -- -------------------------
1584 -- cancel_flag ='I'
1585 -- cancel_reason
1586 -- cancelled_by
1587 -- cancel_date
1588 -- last_update_date
1589 -- last_updated_by
1590 -- last_update_login
1591 --
1592 --
1593 -- Afer Funds Control(After Funds Control routine was successful in
1594 -- unencumbering the PO) Call
1595 -- -------------------------
1596 -- cancel_flag ='Y'
1597
1598 --Parameters:
1599 -- IN:
1600 -- p_fc_level
1601 -- p_entity_id
1602 -- p_cancel_reason
1603 -- p_user_id
1604 -- p_login_id
1605 -- p_action_date
1606 --
1607 -- IN OUT:
1608 --
1609 -- OUT:
1610 -- x_return_status
1611 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1612 -- FND_API.G_RET_STS_ERROR if procedure fails
1613 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1614
1615 --End of Comments
1616 -------------------------------------------------------------------------------
1617 PROCEDURE update_rel_header_cancel(
1618 p_api_version IN NUMBER,
1619 p_init_msg_list IN VARCHAR2,
1620 p_entity_id IN NUMBER,
1621 p_fc_level IN VARCHAR2,
1622 p_action_date IN DATE,
1623 p_cancel_reason IN VARCHAR2,
1624 p_note_to_vendor IN VARCHAR2,
1625 p_user_id IN po_lines.last_updated_by%TYPE,
1626 p_login_id IN po_lines.last_update_login%TYPE,
1627 x_return_status OUT NOCOPY VARCHAR2)
1628 IS
1629
1630 d_api_name CONSTANT VARCHAR2(30) := 'update_rel_header_cancel';
1631 d_api_version CONSTANT NUMBER := 1.0;
1632 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
1633
1634 l_progress VARCHAR2(3) := '000' ;
1635 l_emp_id NUMBER := FND_GLOBAL.employee_id;
1636 l_request_id PO_HEADERS.request_id%TYPE := fnd_global.conc_request_id;
1637
1638 BEGIN
1639 -- Start standard API initialization
1640 IF FND_API.to_boolean(p_init_msg_list) THEN
1641 FND_MSG_PUB.initialize;
1642 END IF;
1643
1644 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
1645 d_api_name, g_pkg_name) THEN
1646 RAISE FND_API.g_exc_unexpected_error;
1647 END IF;
1648
1649
1650 IF g_debug_stmt THEN
1651 PO_DEBUG.debug_begin(d_module);
1652 PO_DEBUG.debug_var(d_module, l_progress, 'p_fc_level', p_fc_level);
1653 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reason', p_cancel_reason);
1654 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
1655 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
1656 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
1657 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
1658 PO_DEBUG.debug_var(d_module, l_progress, 'p_note_to_vendor', p_note_to_vendor);
1659
1660 END IF;
1661
1662
1663
1664 IF p_fc_level = c_before_FC THEN
1665
1666 l_progress := '001' ;
1667
1668 UPDATE po_releases por
1669 SET por.cancel_flag = 'I' ,
1670 por.cancel_reason = p_cancel_reason,
1671 por.cancelled_by = l_emp_id ,
1672 --Bug 16575765: CANCEL_DATE is always stamped(RELEASE) as sysdate.
1673 --por.cancel_date = p_action_date ,
1674 por.cancel_date = sysdate,
1675 por.note_to_vendor = p_note_to_vendor,
1676 por.last_update_date = SYSDATE ,
1677 por.last_updated_by = p_user_id ,
1678 por.last_update_login = p_login_id,
1679 por.request_id = DECODE(l_request_id,
1680 NULL,
1681 request_id,
1682 -1,
1683 request_id,
1684 l_request_id)
1685
1686 WHERE por.po_release_id = p_entity_id;
1687
1688 ELSE
1689
1690 l_progress := '002' ;
1691
1692 UPDATE po_releases por
1693 SET por.cancel_flag = 'Y'
1694 WHERE NVL(por.cancel_flag, 'N') = 'I'
1695 AND por.po_release_id = p_entity_id;
1696
1697 END IF;
1698
1699
1700 IF g_debug_stmt THEN
1701 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in Release Headers table', SQL%ROWCOUNT);
1702 END IF;
1703
1704
1705 EXCEPTION
1706
1707 WHEN FND_API.g_exc_error THEN
1708 x_return_status := FND_API.g_ret_sts_error;
1709 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1710 WHEN FND_API.g_exc_unexpected_error THEN
1711 x_return_status := FND_API.g_ret_sts_unexp_error;
1712 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1713 WHEN OTHERS THEN
1714 x_return_status := FND_API.g_ret_sts_unexp_error;
1715 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
1716
1717
1718 END update_rel_header_cancel;
1719
1720 --------------------------------------------------------------------------------
1721 --Start of Comments
1722 --Name: update_document
1723
1724 --Function:
1725 --
1726 -- Updates the Cancel details on PO /Release Header/Line/Shipemnts/Distributions
1727 -- based on the entity being canceled.
1728 --
1729 -- 1. If the PO Header is Canceled i.e. entity_level=HEADER and document_type=PO
1730 -- then updates PO Headers/Lines/Shipments/Distributions
1731 --
1732 -- 2. If the Release Header is Canceled i.e. entity_level=HEADER and
1733 -- document_type =RELEASE then updates Release Header/Shipments/Distributions
1734 --
1735 -- 3. If the Blanket Header is Canceled i.e. entity_level=HEADER and
1736 -- document_type =PA nd docuemnt_subtype=BLANKET then updates PA Header/Lines
1737 --
1738 -- 4. If the Contract Header is Canceled i.e. entity_level=HEADER and
1739 -- document_type =PA nd docuemnt_subtype=CONTRACT then updates PA Header
1740 --
1741 -- 5. If the PO Line is Canceled i.e. entity_level=LINE and document_type =PO
1742 -- then updates PO Lines/Shipments/Distributions
1743 --
1744 -- 6. If the PA Line is Canceled i.e. entity_level=LINE and document_type =PO
1745 -- then updates PA Line
1746 --
1747 -- 7. If the PO/Release Shipment is Canceled i.e. entity_level=LINE and
1748 -- document_type =PO then updates PO/Release Shipment/Distributions
1749 --
1750 --Parameters:
1751 -- IN:
1752 -- p_entity_level
1753 -- p_action_date
1754 -- p_entity_id
1755 -- p_document_type
1756 -- p_doc_subtype
1757 -- p_cancel_reason
1758 -- p_fc_level
1759 -- p_user_id
1760 -- p_login_id
1761 --
1762 -- IN OUT:
1763 --
1764 -- OUT:
1765 -- x_msg_data
1766 -- x_return_status
1767 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1768 -- FND_API.G_RET_STS_ERROR if procedure fails
1769 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1770
1771 --End of Comments
1772 --------------------------------------------------------------------------------
1773
1774 PROCEDURE update_document(
1775 p_api_version IN NUMBER,
1776 p_init_msg_list IN VARCHAR2,
1777 p_entity_level IN VARCHAR2,
1778 p_action_date IN DATE,
1779 p_entity_id IN NUMBER,
1780 p_document_type IN VARCHAR2,
1781 p_doc_subtype IN VARCHAR2,
1782 p_cancel_reason IN VARCHAR2,
1783 p_fc_level IN VARCHAR2,
1784 p_recreate_demand IN VARCHAR2,
1785 p_note_to_vendor IN VARCHAR2,
1786 p_user_id IN po_lines.last_updated_by%TYPE,
1787 p_login_id IN po_lines.last_update_login%TYPE,
1788 x_return_status OUT NOCOPY VARCHAR2,
1789 x_msg_data OUT NOCOPY VARCHAR2)
1790
1791 IS
1792
1793 d_api_name CONSTANT VARCHAR2(30) := 'update_document';
1794 d_api_version CONSTANT NUMBER := 1.0;
1795 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
1796
1797 l_progress VARCHAR2(3) := '000' ;
1798
1799 BEGIN
1800 -- Start standard API initialization
1801 IF FND_API.to_boolean(p_init_msg_list) THEN
1802 FND_MSG_PUB.initialize;
1803 END IF;
1804
1805 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
1806 d_api_name, g_pkg_name) THEN
1807 RAISE FND_API.g_exc_unexpected_error;
1808 END IF;
1809
1810
1811 IF g_debug_stmt THEN
1812 PO_DEBUG.debug_begin(d_module);
1813 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
1814 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
1815 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
1816 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
1817 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
1818 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reason', p_cancel_reason);
1819 PO_DEBUG.debug_var(d_module, l_progress, 'p_recreate_demand', p_recreate_demand);
1820 PO_DEBUG.debug_var(d_module, l_progress, 'p_fc_level', p_fc_level);
1821 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
1822 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id );
1823 PO_DEBUG.debug_var(d_module, l_progress, 'p_note_to_vendor', p_note_to_vendor );
1824 END IF;
1825
1826 l_progress := '002' ;
1827 x_return_status := FND_API.g_ret_sts_success;
1828 x_msg_data := NULL;
1829
1830 -- This block is applicable to level: Header/Line/Shipment
1831 -- If the Entity being Canceld belongs to a
1832 -- Standard/Planned PO or Release, the corresponding distributions
1833 -- and shipments have to be canceled
1834 -- So cancelling the distributions and shipments in all cases if the
1835 -- document type is not 'Purchase Agreement(PA)'
1836
1837 IF p_document_type <> c_doc_type_PA THEN
1838 l_progress := '003' ;
1839
1840 -- Update Cancel details on Distribution
1841 update_dist_cancel(
1842 p_api_version=> 1.0,
1843 p_init_msg_list=>FND_API.G_FALSE,
1844 p_fc_level => p_fc_level,
1845 p_action_date => p_action_date,
1846 p_entity_level => p_entity_level,
1847 p_entity_id => p_entity_id,
1848 p_document_type=>p_document_type,
1849 p_recreate_demand =>p_recreate_demand,
1850 p_user_id => p_user_id,
1851 p_login_id => p_login_id,
1852 x_return_status => x_return_status);
1853
1854 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1855 RAISE FND_API.g_exc_error;
1856 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1857 RAISE FND_API.g_exc_unexpected_error;
1858 END IF;
1859
1860 l_progress := '004';
1861
1862 -- Update Cancel details on Shipment
1863 update_ship_cancel(
1864 p_api_version=> 1.0,
1865 p_init_msg_list=>FND_API.G_FALSE,
1866 p_fc_level => p_fc_level,
1867 p_cancel_reason => p_cancel_reason,
1868 p_entity_level => p_entity_level,
1869 p_entity_id => p_entity_id,
1870 p_action_date => p_action_date,
1871 p_document_type=>p_document_type,
1872 p_user_id => p_user_id,
1873 p_login_id => p_login_id,
1874 x_return_status => x_return_status);
1875
1876 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1877 RAISE FND_API.g_exc_error;
1878 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1879 RAISE FND_API.g_exc_unexpected_error;
1880 END IF;
1881
1882
1883 END IF; --IF p_document_type <> c_doc_type_PA
1884
1885 l_progress := '005';
1886
1887 -- This block is applicable to level: Header/Line/Shipment
1888 -- If the Entity being Canceld belongs to a
1889 -- Standard/Planned PO or Blanket Agreement, the line details need to
1890 -- be updated
1891 -- If the PO Shipment is canceled, update(rollup) the qty/amount on line
1892 -- If the Line/Header is canceled, cancel the corresponding line itself
1893 -- So Updating the line details in case if the
1894 -- document type is not 'Release' and subtype is not 'Contract'
1895
1896
1897 IF(NOT(p_document_type = c_doc_type_RELEASE
1898 OR p_doc_subtype = c_doc_subtype_CONTRACT)) THEN
1899
1900 --If the Shipment is canceled
1901 IF p_entity_level = c_entity_level_SHIPMENT THEN
1902
1903 l_progress := '006';
1904
1905 -- Update(rollup) the qty/amount on line
1906 update_line_qty_price_amt(
1907 p_api_version=> 1.0,
1908 p_init_msg_list=>FND_API.G_FALSE,
1909 p_ship_id=>p_entity_id,
1910 x_return_status=>x_return_status);
1911
1912 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1913 RAISE FND_API.g_exc_error;
1914 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1915 RAISE FND_API.g_exc_unexpected_error;
1916 END IF;
1917
1918 ELSE
1919 -- If the Line/Header is canceled,
1920 l_progress := '007';
1921
1922 --cancel the corresponding line
1923 update_line_cancel(
1924 p_api_version=> 1.0,
1925 p_init_msg_list=>FND_API.G_FALSE,
1926 p_fc_level => p_fc_level,
1927 p_cancel_reason =>p_cancel_reason,
1928 p_entity_level => p_entity_level,
1929 p_entity_id => p_entity_id,
1930 p_action_date => p_action_date,
1931 p_user_id => p_user_id,
1932 p_login_id => p_login_id,
1933 p_note_to_vendor =>p_note_to_vendor,
1934 x_return_status => x_return_status);
1935
1936 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1937 RAISE FND_API.g_exc_error;
1938 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1939 RAISE FND_API.g_exc_unexpected_error;
1940 END IF;
1941
1942 END IF; --IF p_entity_level = = c_entity_level_SHIPMENT
1943 END IF; -- IF (NOT(p_document_type = c_doc_type_RELEASE OR p_doc_subtype = c_doc_subtype_CONTRACT))
1944
1945
1946 l_progress := '008';
1947
1948 -- This block is applicable to level: Header
1949 -- Canceling the document Header
1950
1951 IF p_entity_level = c_entity_level_HEADER THEN
1952
1953 -- If the Document is a Release Header
1954 IF p_document_type = c_doc_type_RELEASE THEN
1955
1956 l_progress := '009';
1957 update_rel_header_cancel(
1958 p_api_version=> 1.0,
1959 p_init_msg_list=>FND_API.G_FALSE,
1960 p_fc_level => p_fc_level,
1961 p_entity_id => p_entity_id,
1962 p_action_date => p_action_date,
1963 p_cancel_reason =>p_cancel_reason,
1964 p_user_id =>p_user_id,
1965 p_login_id =>p_login_id,
1966 p_note_to_vendor =>p_note_to_vendor,
1967 x_return_status =>x_return_status);
1968
1969 ELSE
1970 l_progress := '010';
1971 -- If the Document is a PO/PA Header
1972 update_po_header_cancel(
1973 p_api_version=> 1.0,
1974 p_init_msg_list=>FND_API.G_FALSE,
1975 p_fc_level => p_fc_level,
1976 p_entity_id => p_entity_id,
1977 p_action_date => p_action_date,
1978 p_user_id =>p_user_id,
1979 p_login_id =>p_login_id,
1980 p_note_to_vendor =>p_note_to_vendor,
1981 x_return_status =>x_return_status);
1982
1983 END IF; -- IF p_document_type = c_doc_type_RELEASE
1984
1985 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1986 RAISE FND_API.g_exc_error;
1987 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1988 RAISE FND_API.g_exc_unexpected_error;
1989 END IF;
1990
1991 END IF; -- IF p_entity_level = c_entity_level_HEADER
1992
1993
1994
1995 EXCEPTION
1996 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1997 x_msg_data := FND_MSG_PUB.GET(
1998 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
1999 P_ENCODED => 'F');
2000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2001 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2002
2003 WHEN FND_API.G_EXC_ERROR THEN
2004 x_msg_data := FND_MSG_PUB.GET(
2005 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2006 P_ENCODED => 'F');
2007 x_return_status := FND_API.G_RET_STS_ERROR;
2008 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2009
2010 WHEN OTHERS THEN
2011 IF (G_DEBUG_UNEXP) THEN
2012 FND_LOG.STRING(
2013 FND_LOG.LEVEL_UNEXPECTED,
2014 d_module || '.OTHERS_EXCEPTION',
2015 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
2016 END IF;
2017
2018 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2019 x_msg_data := FND_MSG_PUB.GET(
2020 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2021 P_ENCODED => 'F');
2022 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2023
2024 END update_document;
2025
2026 --------------------------------------------------------------------------------
2027 -- Start of Comments
2028 -- Name: cancel_supply
2029 -- Function:
2030 -- Updates MTL_SUPPLY when the gievn entity is cancelled to remove the
2031 -- corresponding PO Supply
2032 -- For this , the common routin po_suply.po_req_supply is calle with appropriate
2033 -- action.
2034 --
2035 --Parameters:
2036 --IN:
2037 -- p_entity_level
2038 -- p_entity_id
2039 -- p_doc_id
2040 -- p_document_type
2041 -- p_doc_subtype
2042 -- p_recreate_flag
2043 --
2044 --IN OUT :
2045 --OUT :
2046 -- x_msg_data
2047 -- x_return_status
2048 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
2049 -- FND_API.G_RET_STS_ERROR if cancel action fails
2050 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2051 --
2052 --End of Comments
2053 --------------------------------------------------------------------------------
2054 PROCEDURE cancel_supply(
2055 p_api_version IN NUMBER,
2056 p_init_msg_list IN VARCHAR2,
2057 p_entity_level IN VARCHAR2,
2058 p_entity_id IN NUMBER,
2059 p_doc_id IN NUMBER,
2060 p_document_type IN VARCHAR2,
2061 p_doc_subtype IN VARCHAR2,
2062 p_recreate_flag IN VARCHAR2,
2063 x_return_status IN OUT NOCOPY VARCHAR2,
2064 x_msg_data IN OUT NOCOPY VARCHAR2)
2065
2066 IS
2067
2068 d_api_name CONSTANT VARCHAR2(30) := 'cancel_supply';
2069 d_api_version CONSTANT NUMBER := 1.0;
2070 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
2071
2072 l_progress VARCHAR2(3) := '000' ;
2073
2074
2075 l_line_id NUMBER := 0;
2076 l_ship_id NUMBER := 0;
2077 l_action VARCHAR2(30) := NULL;
2078 l_status BOOLEAN := FALSE;
2079 l_recreate_flag BOOLEAN;
2080
2081
2082 BEGIN
2083 -- Start standard API initialization
2084 IF FND_API.to_boolean(p_init_msg_list) THEN
2085 FND_MSG_PUB.initialize;
2086 END IF;
2087
2088 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
2089 d_api_name, g_pkg_name) THEN
2090 RAISE FND_API.g_exc_unexpected_error;
2091 END IF;
2092
2093
2094 IF g_debug_stmt THEN
2095 PO_DEBUG.debug_begin(d_module);
2096 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
2097 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
2098 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
2099 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
2100 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
2101 PO_DEBUG.debug_var(d_module, l_progress, 'p_recreate_flag', p_recreate_flag);
2102 END IF;
2103
2104
2105 x_return_status := FND_API.g_ret_sts_success;
2106 x_msg_data := NULL;
2107 l_progress := '001' ;
2108
2109 -- If the requiistion line is canceled, cancel req line supply
2110 -- This will be called on cancelling backing req
2111 IF p_document_type = c_doc_type_REQUISITION THEN
2112 l_line_id := p_entity_id;
2113 l_action := 'Remove_Req_Line_Supply';
2114
2115 ELSE
2116 -- If SPO shipment is canceled, the action will be Cancel_PO_Shipment
2117 -- If Schedule Release shipment is canceled, the action will be
2118 -- Cancel_Planned_Shipment
2119 -- If Blanket Release shipment is canceled, the action will be
2120 -- Cancel_Blanket_Shipment
2121 IF p_entity_level = c_entity_level_SHIPMENT THEN
2122 l_ship_id := p_entity_id;
2123
2124 IF p_document_type <> c_doc_type_RELEASE THEN
2125 l_action := 'Cancel_PO_Shipment';
2126 ELSE -- if doc is realse
2127 IF p_doc_subtype = c_doc_subtype_PLANNED THEN
2128 l_action := 'Cancel_Planned_Shipment';
2129 ELSE
2130 l_action := 'Cancel_Blanket_Shipment';
2131 END IF;
2132 END IF;
2133
2134 -- If SPO line is canceled, the action will be Cancel_PO_Line
2135 ELSIF p_entity_level = c_entity_level_LINE THEN
2136
2137 l_line_id := p_entity_id;
2138 l_action := 'Cancel_PO_Line';
2139
2140 -- If SPO Header is canceled, the action will be Cancel_PO_Supply
2141 -- If Schedule Release is canceled, the action will be
2142 -- Cancel_Planned_Release
2143 -- If Blanket Release is canceled, the action will be
2144 -- Cancel_Blanket_Release
2145 ELSE --header level
2146
2147 IF p_document_type <> c_doc_type_RELEASE THEN
2148 l_action := 'Cancel_PO_Supply';
2149 ELSE -- if doc is realse
2150
2151 IF p_doc_subtype = c_doc_subtype_PLANNED THEN
2152 l_action := 'Cancel_Planned_Release';
2153 ELSE
2154 l_action := 'Cancel_Blanket_Release';
2155 END IF;
2156
2157 END IF;
2158
2159 END IF; -- if l_entity_rec_tbl(i).entity_level = c_entity_level_SHIPMENT
2160 END IF;
2161
2162 IF g_debug_stmt THEN
2163 PO_DEBUG.debug_var(d_module, l_progress, 'l_action', l_action);
2164 PO_DEBUG.debug_var(d_module, l_progress, 'l_line_id', l_line_id);
2165 PO_DEBUG.debug_var(d_module, l_progress, 'l_ship_id', l_ship_id);
2166 END IF ;
2167
2168 l_progress := '002' ;
2169 IF p_recreate_flag = 'Y' THEN
2170 l_recreate_flag := TRUE;
2171 ELSE
2172 l_recreate_flag := FALSE;
2173 END IF;
2174
2175 IF g_debug_stmt THEN
2176 PO_DEBUG.debug_var(d_module, l_progress, 'l_recreate_flag', l_recreate_flag);
2177 END IF;
2178
2179
2180 l_progress := '003' ;
2181 -- For the entity, call the po_req_supply function to cancel mtl supply
2182 -- with appropriate action.
2183 l_status := po_supply.po_req_supply(
2184 p_docid => p_doc_id,
2185 p_lineid =>l_line_id,
2186 p_shipid =>l_ship_id,
2187 p_action =>l_action,
2188 p_recreate_flag =>l_recreate_flag,
2189 p_qty =>0,
2190 p_receipt_date =>SYSDATE,
2191 p_reservation_action=>NULL,
2192 p_ordered_uom =>NULL);
2193
2194 IF g_debug_stmt THEN
2195 PO_DEBUG.debug_var(d_module, l_progress, 'l_status', l_status);
2196 END IF;
2197
2198
2199
2200 EXCEPTION
2201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2202 x_msg_data := FND_MSG_PUB.GET(
2203 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2204 P_ENCODED => 'F');
2205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2206 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2207
2208 WHEN FND_API.G_EXC_ERROR THEN
2209 x_msg_data := FND_MSG_PUB.GET(
2210 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2211 P_ENCODED => 'F');
2212 x_return_status := FND_API.G_RET_STS_ERROR;
2213 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2214
2215 WHEN OTHERS THEN
2216 IF (G_DEBUG_UNEXP) THEN
2217 FND_LOG.STRING(
2218 FND_LOG.LEVEL_UNEXPECTED,
2219 d_module || '.OTHERS_EXCEPTION',
2220 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
2221 END IF;
2222
2223 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2224 x_msg_data := FND_MSG_PUB.GET(
2225 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2226 P_ENCODED => 'F');
2227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2228
2229
2230 END cancel_supply;
2231
2232
2233 --------------------------------------------------------------------------------
2234 -- Start of Comments
2235 -- Name: cancel_tax_lines
2236 -- Function:
2237 -- Cancels tax lines after corresponding PO shipment has been cancelled
2238 -- For this , the common routine PO_TAX_INTERFACE_PVT.cancel_tax_lines is
2239 -- called.
2240 --
2241 --Parameters:
2242 --IN:
2243 -- p_entity_level
2244 -- p_entity_id
2245 -- p_doc_id
2246 -- p_document_type
2247 -- p_doc_subtype
2248 --
2249 --IN OUT :
2250 --OUT :
2251 -- x_msg_data
2252 -- x_return_status
2253 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
2254 -- FND_API.G_RET_STS_ERROR if cancel action fails
2255 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2256 --
2257 --End of Comments
2258 --------------------------------------------------------------------------------
2259
2260 PROCEDURE cancel_tax_lines(
2261 p_api_version IN NUMBER,
2262 p_init_msg_list IN VARCHAR2,
2263 p_entity_level IN VARCHAR2,
2264 p_entity_id IN NUMBER,
2265 p_doc_id IN NUMBER,
2266 p_document_type IN VARCHAR2,
2267 p_doc_subtype IN VARCHAR2,
2268 x_return_status IN OUT NOCOPY VARCHAR2,
2269 x_msg_data IN OUT NOCOPY VARCHAR2)
2270
2271 IS
2272
2273 d_api_name CONSTANT VARCHAR2(30) := 'cancel_tax_lines';
2274 d_api_version CONSTANT NUMBER := 1.0;
2275 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
2276
2277 l_progress VARCHAR2(3) := '000' ;
2278
2279 l_msg_count NUMBER := 0;
2280 l_line_id NUMBER := 0;
2281 l_ship_id NUMBER := 0;
2282
2283
2284 BEGIN
2285 -- Start standard API initialization
2286 IF FND_API.to_boolean(p_init_msg_list) THEN
2287 FND_MSG_PUB.initialize;
2288 END IF;
2289
2290 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
2291 d_api_name, g_pkg_name) THEN
2292 RAISE FND_API.g_exc_unexpected_error;
2293 END IF;
2294
2295 IF g_debug_stmt THEN
2296 PO_DEBUG.debug_begin(d_module);
2297 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
2298 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
2299 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
2300 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
2301 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
2302 END IF;
2303
2304 IF p_entity_level = c_entity_level_SHIPMENT THEN
2305 l_ship_id := p_entity_id;
2306
2307 ELSIF p_entity_level = c_entity_level_LINE THEN
2308 l_line_id := p_entity_id;
2309
2310 END IF; -- if l_entity_rec_tbl(i).entity_level = c_entity_level_SHIPMENT
2311
2312
2313 IF g_debug_stmt THEN
2314 PO_DEBUG.debug_var(d_module, l_progress, 'l_line_id', l_line_id);
2315 PO_DEBUG.debug_var(d_module, l_progress, 'l_ship_id', l_ship_id);
2316 END IF ;
2317
2318
2319 PO_TAX_INTERFACE_PVT.cancel_tax_lines(
2320 p_document_type => p_document_type,
2321 p_document_id => p_doc_id,
2322 p_line_id => l_line_id,
2323 p_shipment_id => l_ship_id,
2324 x_return_status => x_return_status,
2325 x_msg_count => l_msg_count,
2326 x_msg_data => x_msg_data);
2327
2328
2329 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2330 RAISE FND_API.g_exc_error;
2331 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2332 RAISE FND_API.g_exc_unexpected_error;
2333 END IF;
2334
2335 EXCEPTION
2336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2337 x_msg_data := FND_MSG_PUB.GET(
2338 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2339 P_ENCODED => 'F');
2340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2341 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2342 IF (g_debug_unexp) THEN
2343 FND_LOG.string(FND_LOG.level_unexpected,d_module ||'---at '||l_progress || 'UnExpected Error', SQLERRM);
2344 END IF;
2345
2346
2347 WHEN FND_API.G_EXC_ERROR THEN
2348 x_msg_data := FND_MSG_PUB.GET(
2349 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2350 P_ENCODED => 'F');
2351 x_return_status := FND_API.G_RET_STS_ERROR;
2352 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2353 IF (g_debug_unexp) THEN
2354 FND_LOG.string(FND_LOG.level_unexpected,d_module ||'---at '||l_progress || 'UnExpected Error', SQLERRM);
2355 END IF;
2356
2357
2358 WHEN OTHERS THEN
2359 IF (G_DEBUG_UNEXP) THEN
2360 FND_LOG.STRING(
2361 FND_LOG.LEVEL_UNEXPECTED,
2362 d_module || '.OTHERS_EXCEPTION',
2363 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
2364 END IF;
2365 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2366 x_msg_data := FND_MSG_PUB.GET(
2367 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2368 P_ENCODED => 'F');
2369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2370
2371 END cancel_tax_lines;
2372
2373 --------------------------------------------------------------------------------
2374 -- Start of Comments
2375 -- Name: cancel_pending_change_request
2376 -- Function:
2377 -- When the PO/Po Shipment is Cancelled, then there is no need for any pending
2378 -- Change Request on the PO /PO Shipment to be approved by Buyer.
2379 -- If the underlying req is also canceled When the PO/PO Shipmnet is Canceled ,
2380 -- then there is no need for the requester change request to be approved by
2381 -- manager any more. We should immediately close the requester change request.
2382 -- If the request change request is a cancel request, we should immediately
2383 -- accept it, and if it is a change request, we should reject it.
2384 --
2385 --Parameters:
2386 --IN:
2387 -- p_entity_level
2388 -- p_entity_id
2389 -- p_doc_id
2390 -- p_document_type
2391 -- p_doc_subtype
2392 -- p_source
2393 -- p_req_line_id_tbl
2394 --
2395 --IN OUT :
2396 --OUT :
2397 -- x_msg_data
2398 -- x_return_status
2399 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
2400 -- FND_API.G_RET_STS_ERROR if cancel action fails
2401 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2402 --
2403 --End of Comments
2404 --------------------------------------------------------------------------------
2405
2406 PROCEDURE cancel_pending_change_request(
2407 p_api_version IN NUMBER,
2408 p_init_msg_list IN VARCHAR2,
2409 p_entity_level IN VARCHAR2,
2410 p_entity_id IN NUMBER,
2411 p_doc_id IN NUMBER,
2412 p_document_type IN VARCHAR2,
2413 p_doc_subtype IN VARCHAR2,
2414 p_source IN VARCHAR2,
2415 p_req_line_id_tbl IN PO_ReqChangeRequestWF_PVT.ReqLineID_tbl_type,
2416 x_return_status IN OUT NOCOPY VARCHAR2,
2417 x_msg_data IN OUT NOCOPY VARCHAR2)
2418
2419 IS
2420
2421 d_api_name CONSTANT VARCHAR2(30) := 'cancel_pending_change_request';
2422 d_api_version CONSTANT NUMBER := 1.0;
2423 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
2424
2425 l_progress VARCHAR2(8) := '000' ;
2426 l_msg_count NUMBER ;
2427 l_line_id NUMBER ;
2428 l_ship_id NUMBER ;
2429
2430
2431 BEGIN
2432 -- Start standard API initialization
2433 IF FND_API.to_boolean(p_init_msg_list) THEN
2434 FND_MSG_PUB.initialize;
2435 END IF;
2436
2437 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
2438 d_api_name, g_pkg_name) THEN
2439 RAISE FND_API.g_exc_unexpected_error;
2440 END IF;
2441
2442
2443
2444 IF g_debug_stmt THEN
2445 PO_DEBUG.debug_begin(d_module);
2446 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
2447 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
2448 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
2449 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
2450 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
2451 PO_DEBUG.debug_var(d_module, l_progress, 'p_source', p_source);
2452 END IF;
2453
2454 l_msg_count := 0;
2455 l_line_id := 0;
2456 l_ship_id := 0;
2457
2458
2459
2460 IF p_entity_level = c_entity_level_SHIPMENT THEN
2461 l_ship_id := p_entity_id;
2462
2463 ELSIF p_entity_level = c_entity_level_LINE THEN
2464 l_line_id := p_entity_id;
2465
2466 END IF; -- if l_entity_rec_tbl(i).entity_level = c_entity_level_SHIPMENT
2467
2468 l_progress := '002';
2469
2470 IF g_debug_stmt THEN
2471 PO_DEBUG.debug_var(d_module, l_progress, 'l_line_id', l_line_id);
2472 PO_DEBUG.debug_var(d_module, l_progress, 'l_ship_id', l_ship_id);
2473 END IF ;
2474
2475 IF p_req_line_id_tbl IS NOT NULL AND p_req_line_id_tbl.Count>0 THEN
2476
2477 l_progress := '003';
2478
2479 -- When there is a pending requester change request going on,
2480 -- if the PO get canceled, which cause the underlying req calceled
2481 -- also, there is no need for the requester change request to be
2482 -- approved by manager any more. We should immediately close the
2483 -- requester change request. If the request change request is
2484 -- a cancel request, we should immediately accept it, and if it is
2485 -- a change request, we should reject it. */
2486 PO_ReqChangeRequestWF_PVT.process_cancelled_req_lines (
2487 p_api_version => 1.0,
2488 p_init_msg_list => FND_API.G_FALSE,
2489 p_commit => FND_API.G_FALSE,
2490 x_return_status => x_return_status,
2491 x_msg_count => l_msg_count,
2492 x_msg_data => x_msg_data,
2493 p_canceledReqLineIDs_tbl => p_req_line_id_tbl );
2494
2495
2496 ELSIF (p_document_type = c_doc_type_RELEASE) THEN
2497 l_progress := '004';
2498
2499 IF p_entity_level = c_entity_level_HEADER THEN
2500 PO_Document_Control_PVT.rel_stop_wf_process(
2501 p_api_version => 1.0,
2502 p_init_msg_list => FND_API.G_FALSE,
2503 x_return_status => x_return_status,
2504 p_doc_type => p_document_type,
2505 p_doc_subtype => p_doc_subtype,
2506 p_doc_id => p_doc_id );
2507
2508 END IF;
2509
2510 -- abort supplier initiated changes and auto accept any
2511 -- pending cancellation for shipments if any
2512 IF nvl(p_source,'NULL') IN(c_HTML_CONTROL_ACTION,c_FORM_CONTROL_ACTION)
2513 THEN
2514
2515 l_progress := '005';
2516 PO_CHG_REQUEST_PVT.cancel_change_request(
2517 p_api_version => 1.0,
2518 p_init_msg_list => FND_API.G_FALSE,
2519 x_return_status => x_return_status,
2520 p_po_header_id => NULL,
2521 p_po_release_id => p_doc_id,
2522 p_po_line_id => NULL,
2523 p_po_line_location_id => l_ship_id );
2524 END IF;
2525 ELSE
2526
2527 IF p_entity_level = c_entity_level_HEADER THEN
2528 l_progress := '006';
2529 PO_Document_Control_PVT.po_stop_wf_process(
2530 p_api_version => 1.0,
2531 p_init_msg_list => FND_API.G_FALSE,
2532 x_return_status => x_return_status,
2533 p_doc_type => p_document_type,
2534 p_doc_subtype => p_doc_subtype,
2535 p_doc_id => p_doc_id );
2536 END IF;
2537
2538 -- abort supplier initiated changes and auto accept any
2539 -- pending cancellation for shipments if any
2540
2541 IF nvl(p_source,'NULL') IN(c_HTML_CONTROL_ACTION,c_FORM_CONTROL_ACTION)
2542 THEN
2543
2544 l_progress := '007';
2545
2546 PO_CHG_REQUEST_PVT.cancel_change_request(
2547 p_api_version => 1.0,
2548 p_init_msg_list => FND_API.G_FALSE,
2549 x_return_status => x_return_status,
2550 p_po_header_id => p_doc_id,
2551 p_po_release_id => NULL,
2552 p_po_line_id => l_line_id,
2553 p_po_line_location_id => l_ship_id );
2554
2555 END IF;
2556
2557 END IF; --<if p_doc_type RELEASE>
2558
2559 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2560 RAISE FND_API.g_exc_error;
2561 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2562 RAISE FND_API.g_exc_unexpected_error;
2563 END IF;
2564
2565
2566
2567 EXCEPTION
2568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2569 x_msg_data := FND_MSG_PUB.GET(
2570 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2571 P_ENCODED => 'F');
2572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2573 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2574
2575 WHEN FND_API.G_EXC_ERROR THEN
2576 x_msg_data := FND_MSG_PUB.GET(
2577 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2578 P_ENCODED => 'F');
2579 x_return_status := FND_API.G_RET_STS_ERROR;
2580 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2581
2582 WHEN OTHERS THEN
2583 IF (G_DEBUG_UNEXP) THEN
2584 FND_LOG.STRING(
2585 FND_LOG.LEVEL_UNEXPECTED,
2586 d_module || '.OTHERS_EXCEPTION',
2587 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
2588 END IF;
2589 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2590 x_msg_data := FND_MSG_PUB.GET(
2591 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
2592 P_ENCODED => 'F');
2593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2594
2595 END cancel_pending_change_request;
2596
2597
2598
2599 --------------------------------------------------------------------------------
2600 -- Start of Comments
2601 -- Name: fetch_req_lines
2602 -- Function:
2603 -- Fetches all requisition line IDs linked to the document at
2604 -- p_entity_level, specified by p_entity_id.
2605 --
2606 --Parameters:
2607 --IN:
2608 -- p_entity_level
2609 -- p_entity_id
2610 -- p_document_type
2611 -- p_doc_subtype
2612 -- p_fc_level
2613 --
2614 --IN OUT :
2615 --OUT :
2616 -- x_msg_data
2617 -- x_req_line_id_tbl
2618 -- A PL/SQL table of requisition line IDs
2619 -- x_return_status
2620 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
2621 -- FND_API.G_RET_STS_ERROR if cancel action fails
2622 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2623 --
2624 --End of Comments
2625 --------------------------------------------------------------------------------
2626
2627 PROCEDURE fetch_req_lines (
2628 p_api_version IN NUMBER,
2629 p_init_msg_list IN VARCHAR2,
2630 p_entity_id IN NUMBER,
2631 p_entity_level IN VARCHAR2,
2632 p_document_type IN VARCHAR2,
2633 p_doc_subtype IN VARCHAR2,
2634 p_fc_level IN VARCHAR2,
2635 x_return_status OUT NOCOPY VARCHAR2,
2636 x_req_line_id_tbl OUT NOCOPY PO_ReqChangeRequestWF_PVT.ReqLineID_tbl_type)
2637 IS
2638
2639 d_api_name CONSTANT VARCHAR2(30) := 'fetch_req_lines';
2640 d_api_version CONSTANT NUMBER := 1.0;
2641 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
2642
2643 l_progress VARCHAR2(3) := '000' ;
2644
2645 CURSOR l_before_fc_csr IS
2646 SELECT DISTINCT(prd.requisition_line_id)
2647 FROM po_req_distributions_all prd,
2648 po_line_locations poll ,
2649 po_distributions_all pod
2650 WHERE pod.line_location_id = poll.line_location_id
2651 AND pod.req_distribution_id = prd.distribution_id
2652 AND NVL(poll.cancel_flag, 'N') = 'I'
2653 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2654 AND poll.shipment_type IN(
2655 'STANDARD',
2656 'PLANNED' ,
2657 'BLANKET' ,
2658 'PREPAYMENT')
2659 AND ((p_entity_level = c_entity_level_HEADER
2660 AND p_document_type <> c_doc_type_RELEASE
2661 AND pod.po_header_id = p_entity_id)
2662 OR(p_entity_level = c_entity_level_HEADER
2663 AND p_document_type = c_doc_type_RELEASE
2664 AND pod.po_release_id = p_entity_id)
2665 OR(p_entity_level = c_entity_level_LINE
2666 AND pod.po_line_id = p_entity_id)
2667 OR(p_entity_level = c_entity_level_SHIPMENT
2668 AND pod.line_location_id = p_entity_id));
2669
2670
2671 CURSOR l_after_fc_csr IS
2672 SELECT DISTINCT(prl.requisition_line_id)
2673 FROM po_requisition_lines_all prl,
2674 po_line_locations poll
2675 WHERE prl.line_location_id= (-1 * poll.line_location_id)
2676 AND NVL(poll.cancel_flag, 'N') = 'Y'
2677 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2678 AND poll.shipment_type IN(
2679 'STANDARD',
2680 'PLANNED' ,
2681 'BLANKET' ,
2682 'PREPAYMENT')
2683 AND ((p_entity_level = c_entity_level_HEADER
2684 AND p_document_type <> c_doc_type_RELEASE
2685 AND poll.po_header_id = p_entity_id)
2686 OR(p_entity_level = c_entity_level_HEADER
2687 AND p_document_type = c_doc_type_RELEASE
2688 AND poll.po_release_id = p_entity_id)
2689 OR(p_entity_level = c_entity_level_LINE
2690 AND poll.po_line_id = p_entity_id)
2691 OR(p_entity_level = c_entity_level_SHIPMENT
2692 AND poll.line_location_id = p_entity_id));
2693
2694
2695
2696
2697 BEGIN
2698 -- Start standard API initialization
2699 IF FND_API.to_boolean(p_init_msg_list) THEN
2700 FND_MSG_PUB.initialize;
2701 END IF;
2702
2703 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
2704 d_api_name, g_pkg_name) THEN
2705 RAISE FND_API.g_exc_unexpected_error;
2706 END IF;
2707
2708
2709 IF g_debug_stmt THEN
2710 PO_DEBUG.debug_begin(d_module);
2711 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
2712 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
2713 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
2714 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
2715 PO_DEBUG.debug_var(d_module, l_progress, 'p_fc_level', p_fc_level);
2716 END IF;
2717
2718
2719 IF p_fc_level = c_before_FC THEN
2720 l_progress := '001';
2721
2722 OPEN l_before_fc_csr;
2723 FETCH l_before_fc_csr BULK COLLECT
2724 INTO x_req_line_id_tbl;
2725
2726 IF (l_before_fc_csr%NOTFOUND) THEN
2727 NULL;
2728 END IF;
2729 CLOSE l_before_fc_csr;
2730
2731
2732 ELSE
2733 l_progress := '002';
2734
2735 OPEN l_after_fc_csr;
2736 FETCH l_after_fc_csr BULK COLLECT
2737 INTO x_req_line_id_tbl;
2738
2739 IF (l_after_fc_csr%NOTFOUND) THEN
2740 NULL;
2741 END IF;
2742 CLOSE l_after_fc_csr;
2743
2744 END IF;
2745
2746
2747 IF g_debug_stmt THEN
2748 PO_DEBUG.debug_var(
2749 d_module,
2750 l_progress,
2751 'x_req_line_id_tbl count',
2752 x_req_line_id_tbl.count);
2753 END IF;
2754
2755 EXCEPTION
2756 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2758 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2759 IF(G_DEBUG_UNEXP) THEN
2760 FND_LOG.STRING(
2761 FND_LOG.LEVEL_UNEXPECTED,
2762 d_module || '.UNEXPECTED',
2763 'ERROR: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE|| ' SQL ERRM IS '||SQLERRM);
2764 END IF;
2765
2766
2767 WHEN FND_API.G_EXC_ERROR THEN
2768 x_return_status := FND_API.G_RET_STS_ERROR;
2769 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2770 IF (G_DEBUG_UNEXP) THEN
2771 FND_LOG.STRING(
2772 FND_LOG.LEVEL_UNEXPECTED,
2773 d_module || '.ERROR ERROR',
2774 'ERROR: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE|| ' SQL ERRM IS '||SQLERRM);
2775 END IF;
2776
2777 WHEN OTHERS THEN
2778 IF (G_DEBUG_UNEXP) THEN
2779 FND_LOG.STRING(
2780 FND_LOG.LEVEL_UNEXPECTED,
2781 d_module || '.OTHERS_EXCEPTION',
2782 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE|| ' SQL ERRM IS '||SQLERRM);
2783 END IF;
2784 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2786
2787 END fetch_req_lines;
2788
2789 --------------------------------------------------------------------------------
2790 --Start of Comments
2791 --Name: calc_uom_conv
2792 --Function:
2793 -- Calculate UOM Conversion between Req Line UOM and its corresponding
2794 -- PO Line UOM
2795 --
2796 --Parameters:
2797 --IN:
2798 -- p_req_line_id
2799 -- p_fc_level
2800 --
2801 --IN OUT :
2802 --OUT :
2803 --RETURNS
2804 -- x_uom_conv:
2805 -- UOM Conversion between Req Line UOM and corresponding PO Line UOM
2806 --
2807 --End of Comments
2808 --------------------------------------------------------------------------------
2809 PROCEDURE calc_uom_conv(
2810 p_api_version IN NUMBER,
2811 p_init_msg_list IN VARCHAR2,
2812 p_fc_level IN VARCHAR2,
2813 p_req_line_id IN NUMBER,
2814 x_uom_conv OUT NOCOPY NUMBER)
2815 IS
2816
2817 d_api_name CONSTANT VARCHAR2(30) := 'calc_uom_conv';
2818 d_api_version CONSTANT NUMBER := 1.0;
2819 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
2820
2821 l_progress VARCHAR2(3) := '000' ;
2822
2823
2824
2825 BEGIN
2826 -- Start standard API initialization
2827 IF FND_API.to_boolean(p_init_msg_list) THEN
2828 FND_MSG_PUB.initialize;
2829 END IF;
2830
2831 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
2832 d_api_name, g_pkg_name) THEN
2833 RAISE FND_API.g_exc_unexpected_error;
2834 END IF;
2835
2836
2837 IF g_debug_stmt THEN
2838 PO_DEBUG.debug_begin(d_module);
2839 PO_DEBUG.debug_var(d_module, l_progress, 'p_req_line_id', p_req_line_id);
2840 PO_DEBUG.debug_var(d_module, l_progress, 'p_fc_level', p_fc_level);
2841 END IF;
2842
2843 x_uom_conv := 1;
2844
2845 --<<Bug#16515136 START>>
2846 IF p_fc_level = c_before_FC THEN
2847 SELECT PO_UOM_S.PO_UOM_CONVERT_P(
2848 POL.UNIT_MEAS_LOOKUP_CODE,
2849 PORL.UNIT_MEAS_LOOKUP_CODE,
2850 PORL.ITEM_ID)
2851 INTO x_uom_conv
2852 FROM PO_REQUISITION_LINES_ALL PORL,
2853 po_line_locations POLL ,
2854 po_lines POL
2855 WHERE PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
2856 AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2857 AND PORL.REQUISITION_LINE_ID = p_req_line_id ;
2858
2859 ELSE
2860 SELECT PO_UOM_S.PO_UOM_CONVERT_P(
2861 POL.UNIT_MEAS_LOOKUP_CODE,
2862 PORL.UNIT_MEAS_LOOKUP_CODE,
2863 PORL.ITEM_ID)
2864 INTO x_uom_conv
2865 FROM PO_REQUISITION_LINES_ALL PORL,
2866 po_line_locations POLL ,
2867 po_lines POL
2868 WHERE (-1)*PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
2869 AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2870 and porl.requisition_line_id = p_req_line_id ;
2871 END IF;
2872 --<<Bug#16515136 END>>
2873
2874 IF g_debug_stmt THEN
2875 PO_DEBUG.debug_var(d_module, l_progress, 'x_uom_conv', x_uom_conv);
2876 END IF;
2877
2878 EXCEPTION
2879 WHEN No_Data_Found THEN
2880 x_uom_conv := 1;
2881 IF g_debug_stmt THEN
2882 PO_DEBUG.debug_stmt(d_module, l_progress,'Set l_uom_conv = 0');
2883 END IF ;
2884 WHEN OTHERS THEN
2885 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2886 IF g_debug_stmt THEN
2887 PO_DEBUG.debug_stmt(d_module, l_progress,'Exception in calc_uom_conv check');
2888 END IF ;
2889
2890 END calc_uom_conv;
2891
2892 --------------------------------------------------------------------------------
2893 --Start of Comments
2894 --Name: calc_qty_canceled
2895 --Function:
2896 -- Calculates the quantity canceled to be updated on requisition line
2897 --
2898 --Parameters:
2899 --IN:
2900 -- p_req_line_id
2901 -- p_uom_conv
2902 --
2903 --IN OUT :
2904 --OUT :
2905 --RETURNS
2906 -- x_qty_cancelled
2907 --
2908 --End of Comments
2909 --------------------------------------------------------------------------------
2910 PROCEDURE calc_qty_canceled(
2911 p_api_version IN NUMBER,
2912 p_init_msg_list IN VARCHAR2,
2913 p_req_line_id IN NUMBER,
2914 p_uom_conv IN NUMBER,
2915 x_qty_cancelled OUT NOCOPY NUMBER )
2916 IS
2917
2918 d_api_name CONSTANT VARCHAR2(30) := 'calc_qty_canceled';
2919 d_api_version CONSTANT NUMBER := 1.0;
2920 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
2921
2922 l_progress VARCHAR2(3) := '000' ;
2923
2924
2925 BEGIN
2926 -- Start standard API initialization
2927 IF FND_API.to_boolean(p_init_msg_list) THEN
2928 FND_MSG_PUB.initialize;
2929 END IF;
2930
2931 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
2932 d_api_name, g_pkg_name) THEN
2933 RAISE FND_API.g_exc_unexpected_error;
2934 END IF;
2935
2936
2937 IF g_debug_stmt THEN
2938 PO_DEBUG.debug_begin(d_module);
2939 PO_DEBUG.debug_var(d_module, l_progress, 'p_req_line_id', p_req_line_id);
2940 PO_DEBUG.debug_var(d_module, l_progress, 'p_uom_conv', p_uom_conv);
2941 END IF;
2942
2943 SELECT DECODE(
2944 SIGN(SUM(PORD.req_line_quantity
2945 - p_uom_conv *greatest(NVL(POD.quantity_delivered, 0),
2946 NVL(POD.quantity_billed, 0)
2947 )
2948 )
2949 ),
2950 -1,
2951 0,
2952 SUM(PORD.req_line_quantity
2953 - p_uom_conv *greatest(NVL(POD.quantity_delivered, 0),
2954 NVL(POD.quantity_billed, 0)
2955 )
2956 )
2957 )
2958 INTO x_qty_cancelled
2959 FROM PO_DISTRIBUTIONS_ALL POd ,
2960 po_req_distributions_all pord,
2961 po_line_locations POLL
2962 WHERE pord.requisition_line_id = p_req_line_id
2963 AND pord.distribution_id = pod.req_distribution_id
2964 AND POLL.line_location_id = POD.line_location_id
2965 AND POLL.shipment_type IN(
2966 'STANDARD',
2967 'PLANNED' ,
2968 'BLANKET');
2969
2970 IF g_debug_stmt THEN
2971 PO_DEBUG.debug_var(d_module, l_progress, 'x_qty_cancelled', x_qty_cancelled);
2972 END IF;
2973
2974 EXCEPTION
2975 WHEN No_Data_Found THEN
2976 x_qty_cancelled := 0;
2977 IF g_debug_stmt THEN
2978 PO_DEBUG.debug_stmt(d_module, l_progress, 'Set p_qty_cancelled = 0');
2979 END IF ;
2980 WHEN OTHERS THEN
2981 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
2982 IF g_debug_stmt THEN
2983 PO_DEBUG.debug_stmt(d_module, l_progress, 'Exception in calc_qty_canceled check');
2984 END IF ;
2985
2986 END calc_qty_canceled;
2987
2988 --------------------------------------------------------------------------------
2989 -- Start of Comments
2990 -- Name: update_req_details_after_fc
2991 -- Function:
2992 -- Updates the cancel details(ex: cancel_flag ='Y') on backing requisition lines
2993 -- of the document being canceled after the funds control call
2994 -- (i.e. unencumbering the Document).
2995 -- If p_reacreate_demand_flag ='N' then it just updates::
2996 -- the Cancel_flag to 'Y'of the backing Req Line
2997 -- Quantity on PRD and PRL to be the cancelled quantity on POD
2998 -- and Sum(qty on PRD) resp.
2999 -- If p_reacreate_demand_flag ='Y' then it :
3000 -- Updates the OLD PRD Quantity to be remaining qty i.e. Original PRD
3001 -- Qty-Qty canceled on POD
3002 -- Updates the OLD PRL Quantity to be sum (Quantity on OLD PRD)
3003 -- Updates line_location_id to be null on new PRL.
3004 --
3005 --Parameters:
3006 --IN:
3007 -- p_req_line_id
3008 -- p_recreate_demand_flag
3009 -- p_user_id
3010 -- p_login_id
3011 --
3012 --IN OUT :
3013 --OUT :
3014 -- x_msg_data
3015 -- x_return_status
3016 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
3017 -- FND_API.G_RET_STS_ERROR if cancel action fails
3018 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3019 --
3020 --End of Comments
3021 --------------------------------------------------------------------------------
3022 PROCEDURE update_req_details_after_fc(
3023 p_api_version IN NUMBER,
3024 p_init_msg_list IN VARCHAR2,
3025 p_req_line_id IN NUMBER,
3026 p_recreate_demand_flag IN VARCHAR2,
3027 p_user_id IN po_lines.last_updated_by%TYPE,
3028 p_login_id IN po_lines.last_update_login%TYPE,
3029 p_is_new_req_line IN VARCHAR2,
3030 x_msg_data OUT NOCOPY VARCHAR2,
3031 x_return_status OUT NOCOPY VARCHAR2)
3032 IS
3033
3034 d_api_name CONSTANT VARCHAR2(30) := 'update_req_details_after_fc';
3035 d_api_version CONSTANT NUMBER := 1.0;
3036 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
3037
3038 l_progress VARCHAR2(3);
3039 l_qty_cancelled NUMBER;
3040 l_parent_req_line_id NUMBER;
3041 l_uom_conv NUMBER ;
3042 l_auth_status VARCHAR2(30);
3043
3044
3045 BEGIN
3046 -- Start standard API initialization
3047 IF FND_API.to_boolean(p_init_msg_list) THEN
3048 FND_MSG_PUB.initialize;
3049 END IF;
3050
3051 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
3052 d_api_name, g_pkg_name) THEN
3053 RAISE FND_API.g_exc_unexpected_error;
3054 END IF;
3055
3056
3057 l_progress := '000' ;
3058
3059 IF g_debug_stmt THEN
3060 PO_DEBUG.debug_begin(d_module);
3061 PO_DEBUG.debug_var(d_module, l_progress, 'p_req_line_id', p_req_line_id);
3062 PO_DEBUG.debug_var(d_module, l_progress, 'p_recreate_demand_flag', p_recreate_demand_flag);
3063 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
3064 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
3065 PO_DEBUG.debug_var(d_module, l_progress, 'p_is_new_req_line', p_is_new_req_line);
3066
3067 END IF;
3068
3069
3070 x_return_status := FND_API.g_ret_sts_success;
3071 x_msg_data := NULL;
3072 l_auth_status := NULL;
3073 l_qty_cancelled := 0;
3074 l_uom_conv := 0;
3075
3076 l_progress := '001' ;
3077
3078 -- If recreate demand is OFF, then the req line has to be canceled
3079 IF p_recreate_demand_flag = 'N' THEN
3080
3081 -- Get the UOM conversion between req line and its corresponding PO Line
3082 calc_uom_conv (
3083 p_api_version=> 1.0,
3084 p_init_msg_list=>FND_API.G_FALSE,
3085 p_fc_level=>c_after_FC,
3086 p_req_line_id=>p_req_line_id,
3087 x_uom_conv=>l_uom_conv);
3088
3089 IF g_debug_stmt THEN
3090 PO_DEBUG.debug_var(d_module, l_progress, 'l_uom_conv', l_uom_conv);
3091 END IF;
3092
3093 l_progress := '002';
3094 -- Calculate the quantity canceled to be updated on req line.
3095 calc_qty_canceled(
3096 p_api_version=> 1.0,
3097 p_init_msg_list=>FND_API.G_FALSE,
3098 p_req_line_id=>p_req_line_id,
3099 p_uom_conv=>l_uom_conv,
3100 x_qty_cancelled=>l_qty_cancelled);
3101
3102 IF g_debug_stmt THEN
3103 PO_DEBUG.debug_var(d_module, l_progress, 'l_qty_cancelled', l_qty_cancelled);
3104 END IF;
3105
3106 -- Update the req distribution quantity to be the quantity canceled on Po Distribution
3107 UPDATE PO_REQ_DISTRIBUTIONS PORD
3108 SET PORD.req_line_quantity =
3109 (SELECT DECODE(SIGN(PORD.req_line_quantity
3110 - SUM(l_uom_conv * greatest(NVL(POD.quantity_delivered, 0),
3111 NVL(POD.quantity_billed, 0)
3112 )
3113 )
3114 ),
3115 -1,
3116 PORD.req_line_quantity,
3117 0,
3118 PORD.req_line_quantity,
3119 SUM(l_uom_conv * (greatest(NVL(POD.quantity_delivered, 0),
3120 NVL(POD.quantity_billed, 0)) )) )
3121
3122 FROM po_line_locations POLL,
3123 PO_DISTRIBUTIONS_ALL POD
3124 WHERE POD.req_distribution_id = PORD.distribution_id
3125 AND POLL.line_location_id = POD.line_location_id
3126 AND POLL.shipment_type IN ('STANDARD',
3127 'PLANNED' ,
3128 'BLANKET') )
3129 WHERE PORD.requisition_line_id = p_req_line_id ;
3130
3131
3132 IF g_debug_stmt THEN
3133 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO_REQ_DISTRIBUTIONS table', SQL%ROWCOUNT);
3134 END IF;
3135
3136 l_progress := '003' ;
3137
3138 -- Set the cancel_flag to Y and set back the line_location id to positive
3139 UPDATE PO_REQUISITION_LINES_ALL PORL
3140 SET PORL.cancel_flag = 'Y' ,
3141 PORL.contractor_status = NULL ,
3142 PORL.cancel_date = SYSDATE ,
3143 PORL.line_location_id = -1 * PORL.line_location_id,
3144 PORL.quantity_cancelled = l_qty_cancelled
3145 WHERE PORL.requisition_line_id = p_req_line_id;
3146
3147 IF g_debug_stmt THEN
3148 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO_REQUISITION_LINES_ALL table', SQL%ROWCOUNT);
3149 END IF;
3150
3151
3152 ELSE --p_recreate_demand_flag ='Y'
3153
3154
3155 IF p_is_new_req_line ='Y' THEN
3156 -- When Recreate_Demand is ON and new Req line is created
3157 l_progress := '004' ;
3158 calc_uom_conv (
3159 p_api_version=> 1.0,
3160 p_init_msg_list=>FND_API.G_FALSE,
3161 p_fc_level=>c_after_FC,
3162 p_req_line_id=>p_req_line_id,
3163 x_uom_conv=>l_uom_conv);
3164
3165
3166 -- The control will come here if the recreate demand is ON and the qty
3167 -- canceled on po distribution is greater than 0 .
3168 -- In that case, a new Req Line/distribution will be craeted with the qty
3169 -- being canceled.
3170 -- So Just Updating Old Req line and Distribution quantity with the
3171 -- (original qty-qty canceled).
3172 -- Note: The p_req_line_id now will be the new req line id created
3173
3174 l_progress := '005';
3175
3176 UPDATE PO_REQ_DISTRIBUTIONS_ALL PORD
3177 SET PORD.req_line_quantity = (SELECT Least(PORD.req_line_quantity ,
3178 sum(l_uom_conv *
3179 Greatest(NVL(POD.quantity_delivered, 0),
3180 NVL(POD.quantity_billed, 0))
3181 )
3182 )
3183 FROM po_line_locations POLL,
3184 PO_DISTRIBUTIONS_ALL POD
3185 WHERE POD.req_distribution_id = PORD.distribution_id
3186 AND POLL.line_location_id = POD.line_location_id
3187 AND POLL.shipment_type IN ('STANDARD' ,
3188 'PLANNED' ,
3189 'BLANKET') ),
3190
3191 PORD.last_update_date = SYSDATE ,
3192 PORD.last_updated_by = p_user_id,
3193 PORD.last_update_login = p_login_id
3194 WHERE PORD.distribution_id IN (
3195 SELECT PORD1.distribution_id
3196 FROM PO_REQUISITION_LINES_ALL PORL_NEW,
3197 PO_REQUISITION_LINES_ALL PORL_OLD,
3198 PO_REQ_DISTRIBUTIONS_ALL PORD1 ,
3199 PO_DISTRIBUTIONS POD
3200 WHERE PORL_NEW.requisition_line_id = p_req_line_id
3201 AND PORL_OLD.requisition_line_id = (-1) * PORL_NEW.parent_req_line_id
3202 AND PORD1.requisition_line_id = PORL_OLD.requisition_line_id
3203 AND POD.req_distribution_id = PORD1.distribution_id
3204 AND NVL(POD.quantity_cancelled, 0) > 0 );
3205
3206 IF g_debug_stmt THEN
3207 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO_REQ_DISTRIBUTIONS_ALL table', SQL%ROWCOUNT);
3208 END IF;
3209
3210 l_progress := '006' ;
3211 SELECT SUM(PORD.req_line_quantity)
3212 INTO l_qty_cancelled
3213 FROM PO_REQ_DISTRIBUTIONS_ALL PORD
3214 WHERE PORD.distribution_id IN (
3215 SELECT PORD1.distribution_id
3216 FROM PO_REQUISITION_LINES_ALL PORL_NEW,
3217 PO_REQUISITION_LINES_ALL PORL_OLD,
3218 PO_REQ_DISTRIBUTIONS_ALL PORD1
3219 WHERE PORL_NEW.requisition_line_id= p_req_line_id
3220 AND PORL_OLD.requisition_line_id= (-1) * PORL_NEW.parent_req_line_id
3221 AND PORD1.requisition_line_id = PORL_OLD.requisition_line_id );
3222
3223 IF g_debug_stmt THEN
3224 PO_DEBUG.debug_var(d_module, l_progress, 'l_qty_cancelled', l_qty_cancelled);
3225 END IF;
3226
3227 l_progress := '007' ;
3228
3229 -- Updating the Old Req Line
3230 UPDATE PO_REQUISITION_LINES_ALL PORL
3231 SET(
3232 PORL.QUANTITY ,
3233 PORL.LAST_UPDATE_DATE,
3234 PORL.LAST_UPDATED_BY ,
3235 PORL.LAST_UPDATE_LOGIN )
3236 = (SELECT DECODE(porl.order_type_lookup_code,
3237 'RATE', NULL,
3238 'FIXED PRICE', NULL,
3239 l_qty_cancelled),
3240 SYSDATE ,
3241 p_user_id ,
3242 p_login_id
3243 FROM po_line_locations POLL,
3244 po_lines POL
3245 WHERE PORL.line_location_id = POLL.line_location_id
3246 AND POLL.po_line_id = POL.po_line_id)
3247 WHERE PORL.requisition_line_id = (
3248 SELECT ((-1) * PORL_NEW.parent_req_line_id)
3249 FROM PO_REQUISITION_LINES_ALL PORL_NEW
3250 WHERE PORL_NEW.requisition_line_id = p_req_line_id );
3251
3252 IF g_debug_stmt THEN
3253 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO_REQUISITION_LINES_ALL table', SQL%ROWCOUNT);
3254 END IF;
3255
3256 l_progress := '008' ;
3257
3258 SELECT PORL_OLD.PARENT_REQ_LINE_ID,
3259 PRH.AUTHORIZATION_STATUS
3260 INTO l_parent_req_line_id,
3261 l_auth_status
3262 FROM PO_REQUISITION_LINES_ALL PORL_OLD,
3263 PO_REQUISITION_LINES_ALL PORL_NEW,
3264 PO_REQUISITION_HEADERS_ALL PRH
3265 WHERE PORL_OLD.requisition_line_id = (-1) * PORL_NEW.parent_req_line_id
3266 AND PRH.requisition_header_id = PORL_NEW.requisition_header_id
3267 AND PORL_NEW.requisition_line_id = p_req_line_id;
3268
3269
3270 IF g_debug_stmt THEN
3271 PO_DEBUG.debug_var(d_module,l_progress,'l_auth_status',l_auth_status);
3272 PO_DEBUG.debug_var(d_module,l_progress,'l_parent_req_line_id',l_parent_req_line_id);
3273 END IF;
3274
3275 -- Need to calculate Reqs in POOL flag :
3276 -- It should be Y only if Req Header is in Approved status
3277
3278 l_progress := '009' ;
3279 -- Updating the new line to free it up,
3280 UPDATE PO_REQUISITION_LINES_ALL PORL
3281 SET PORL.LINE_LOCATION_ID = NULL,
3282 PORL.PARENT_REQ_LINE_ID = DECODE(l_parent_req_line_id,
3283 -9999,
3284 NULL,
3285 l_parent_req_line_id),
3286 LAST_UPDATE_DATE = SYSDATE ,
3287 LAST_UPDATED_BY = p_user_id ,
3288 LAST_UPDATE_LOGIN = p_login_id ,
3289 PORL.REQS_IN_POOL_FLAG = DECODE(l_auth_status,
3290 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
3291 'Y',
3292 'N')
3293
3294 WHERE PORL.requisition_line_id = p_req_line_id;
3295
3296 IF g_debug_stmt THEN
3297 PO_DEBUG.debug_var(d_module,
3298 l_progress,
3299 'Rows Updated in PO_REQUISITION_LINES_ALL table',
3300 SQL%ROWCOUNT);
3301 END IF;
3302
3303
3304 ELSE
3305 -- When Recreate_Demand is ON and no new Req line is created
3306 l_progress := '010' ;
3307
3308 SELECT PRH.AUTHORIZATION_STATUS
3309 INTO l_auth_status
3310 FROM PO_REQUISITION_LINES_ALL PORL,
3311 PO_REQUISITION_HEADERS_ALL PRH
3312 WHERE PRH.requisition_header_id = PORL.requisition_header_id
3313 AND PORL.requisition_line_id = p_req_line_id;
3314
3315
3316 IF g_debug_stmt THEN
3317 PO_DEBUG.debug_var(d_module,l_progress,'l_auth_status',l_auth_status);
3318 END IF;
3319
3320 -- Need to calculate Reqs in POOL flag :
3321 -- It should be Y only if Req Header is in Approved status
3322
3323 l_progress := '009' ;
3324 -- Updating the new line to free it up,
3325 UPDATE PO_REQUISITION_LINES_ALL PORL
3326 SET PORL.LINE_LOCATION_ID = NULL,
3327 LAST_UPDATE_DATE = SYSDATE ,
3328 LAST_UPDATED_BY = p_user_id ,
3329 LAST_UPDATE_LOGIN = p_login_id ,
3330 PORL.REQS_IN_POOL_FLAG = DECODE(l_auth_status,
3331 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
3332 'Y',
3333 'N')
3334
3335 WHERE PORL.requisition_line_id = p_req_line_id;
3336
3337 IF g_debug_stmt THEN
3338 PO_DEBUG.debug_var(d_module,
3339 l_progress,
3340 'Rows Updated in PO_REQUISITION_LINES_ALL table',
3341 SQL%ROWCOUNT);
3342 END IF;
3343
3344 END IF; -- If p_is_new_req_line ='Y'
3345
3346 END IF;
3347
3348 EXCEPTION
3349 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3350 x_msg_data := FND_MSG_PUB.GET(
3351 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
3352 P_ENCODED => 'F');
3353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3354 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
3355
3356 WHEN FND_API.G_EXC_ERROR THEN
3357 x_msg_data := FND_MSG_PUB.GET(
3358 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
3359 P_ENCODED => 'F');
3360 x_return_status := FND_API.G_RET_STS_ERROR;
3361 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
3362
3363 WHEN OTHERS THEN
3364 IF (G_DEBUG_UNEXP) THEN
3365 FND_LOG.STRING(
3366 FND_LOG.LEVEL_UNEXPECTED,
3367 d_module || '.OTHERS_EXCEPTION',
3368 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
3369 END IF;
3370
3371 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
3372 x_msg_data := FND_MSG_PUB.GET(
3373 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
3374 P_ENCODED => 'F');
3375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3376
3377
3378 END update_req_details_after_fc;
3379
3380
3381 --------------------------------------------------------------------------------
3382 --Start of Comments
3383 --Name: is_qty_del_bill_zero
3384 --Function:
3385 -- Finds he PO Distributions corresponding to each of the Req Distributions
3386 -- of the i/p req line and
3387 -- Returns true if the Qty delivered/Billed is greater than 0 on any of those
3388 -- PO distributions Otherwise returns False.
3389 --
3390 --Parameters:
3391 --IN:
3392 -- p_req_line_id
3393 --IN OUT :
3394 --OUT :
3395 -- RETURNS
3396 -- True
3397 -- If Qty Billed/Delivered is greater than 0 on corresponding PO distribution.
3398 -- False
3399 -- If both Qty Billed/Delivered are 0 on corresponding PO distribution.
3400 --
3401 --End of Comments
3402 --------------------------------------------------------------------------------
3403
3404 FUNCTION is_qty_del_bill_zero(
3405 p_api_version IN NUMBER,
3406 p_init_msg_list IN VARCHAR2,
3407 p_req_line_id IN NUMBER)
3408 RETURN BOOLEAN
3409 IS
3410
3411 d_api_name CONSTANT VARCHAR2(30) := 'is_qty_del_bill_zero';
3412 d_api_version CONSTANT NUMBER := 1.0;
3413 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
3414
3415 l_progress VARCHAR2(3) := '000' ;
3416 l_is_qty_zero BOOLEAN := FALSE;
3417 l_qzero NUMBER := 0;
3418
3419 BEGIN
3420 -- Start standard API initialization
3421 IF FND_API.to_boolean(p_init_msg_list) THEN
3422 FND_MSG_PUB.initialize;
3423 END IF;
3424
3425 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
3426 d_api_name, g_pkg_name) THEN
3427 RAISE FND_API.g_exc_unexpected_error;
3428 END IF;
3429
3430
3431 IF g_debug_stmt THEN
3432 PO_DEBUG.debug_begin(d_module);
3433 PO_DEBUG.debug_var(d_module, l_progress, 'p_req_line_id', p_req_line_id);
3434 END IF;
3435
3436
3437 BEGIN
3438 SELECT SUM(greatest(pod.quantity_delivered,
3439 pod.quantity_billed))
3440 INTO l_qzero
3441 FROM PO_DISTRIBUTIONS_ALL POD,
3442 PO_REQ_DISTRIBUTIONS_ALL PORD
3443 WHERE POD.req_distribution_id = PORD.distribution_id
3444 AND PORD.requisition_line_id = p_req_line_id;
3445
3446 EXCEPTION
3447
3448 WHEN No_Data_Found THEN
3449 l_qzero := 0;
3450 IF g_debug_stmt THEN
3451 PO_DEBUG.debug_stmt(d_module, l_progress, 'Set l_qzero = 0');
3452 END IF ;
3453 WHEN OTHERS THEN
3454 IF g_debug_stmt THEN
3455 PO_DEBUG.debug_stmt(d_module, l_progress, 'Exception in is_qty_del_bill_zero check');
3456 END IF ;
3457 RAISE FND_API.g_exc_unexpected_error;
3458 END;
3459
3460 IF g_debug_stmt THEN
3461 PO_DEBUG.debug_var(d_module, l_progress, 'l_qzero', l_qzero);
3462 END IF;
3463
3464 IF l_qzero= 0 THEN
3465 l_is_qty_zero := TRUE;
3466 END IF;
3467
3468 RETURN l_is_qty_zero;
3469
3470 END is_qty_del_bill_zero;
3471
3472 --------------------------------------------------------------------------------
3473 --Start of Comments
3474 --Name: create_req_line
3475 --Function:
3476 -- Creates a new requisition line, setting its quantity to be input qty
3477 -- Insert a new req line with:
3478 -- Line number = Max(current line number on req)
3479 -- requisition_line_id = PO_REQUISITION_LINE_S.NEXTVAL
3480 -- quantity = p_line_quantity.
3481 -- line_location_id= -poll.line_location_id (This is so that no
3482 -- other module uses this req line until funds
3483 -- checker is executed successfully. Then we
3484 -- change line_location_id to Null.)
3485 -- Copy the notes corresponding to the old line.
3486 -- This routine is called only if recreate_demand_flag is ON
3487 --
3488 --Parameters:
3489 --IN:
3490 -- p_old_line_id
3491 -- p_line_quantity
3492 -- p_user_id
3493 -- p_login_id
3494 --
3495 --IN OUT :
3496 --OUT :
3497 -- x_new_line_id
3498 -- Returns the requisition_line_id of the newly created req line
3499 -- x_msg_data
3500 -- x_return_status
3501 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
3502 -- FND_API.G_RET_STS_ERROR if cancel action fails
3503 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3504 --
3505 --End of Comments
3506 --------------------------------------------------------------------------------
3507
3508 PROCEDURE create_req_line(
3509 p_api_version IN NUMBER,
3510 p_init_msg_list IN VARCHAR2,
3511 p_old_line_id IN NUMBER,
3512 p_line_quantity IN NUMBER,
3513 p_user_id IN po_lines.last_updated_by%TYPE,
3514 p_login_id IN po_lines.last_update_login%TYPE,
3515 x_new_line_id OUT NOCOPY NUMBER,
3516 x_return_status OUT NOCOPY VARCHAR2,
3517 x_msg_data OUT NOCOPY VARCHAR2)
3518
3519 IS
3520 d_api_name CONSTANT VARCHAR2(30) := 'create_req_line';
3521 d_api_version CONSTANT NUMBER := 1.0;
3522 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
3523
3524 l_next_line_num NUMBER;
3525 l_progress VARCHAR2(3) := '000' ;
3526
3527 BEGIN
3528 -- Start standard API initialization
3529 IF FND_API.to_boolean(p_init_msg_list) THEN
3530 FND_MSG_PUB.initialize;
3531 END IF;
3532
3533 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
3534 d_api_name, g_pkg_name) THEN
3535 RAISE FND_API.g_exc_unexpected_error;
3536 END IF;
3537
3538
3539 IF g_debug_stmt THEN
3540 PO_DEBUG.debug_begin(d_module);
3541 PO_DEBUG.debug_var(d_module, l_progress, 'p_old_line_id', p_old_line_id);
3542 PO_DEBUG.debug_var(d_module, l_progress, 'p_line_quantity', p_line_quantity);
3543 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
3544 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
3545 END IF;
3546
3547 l_progress := '001' ;
3548 x_msg_data := NULL;
3549 x_return_status := FND_API.G_RET_STS_SUCCESS;
3550
3551 SELECT NVL(MAX(porl.line_num), 0) + 1
3552 INTO l_next_line_num
3553 FROM po_requisition_lines_all porl
3554 WHERE porl.requisition_header_id
3555 =(SELECT requisition_header_id
3556 FROM po_requisition_lines_all
3557 WHERE requisition_line_id = p_old_line_id);
3558
3559 l_progress := '002' ;
3560
3561 SELECT PO_REQUISITION_LINES_S.NEXTVAL
3562 INTO x_new_line_id
3563 FROM SYS.DUAL;
3564
3565 IF g_debug_stmt THEN
3566 PO_DEBUG.debug_var(d_module, l_progress, 'x_new_line_id', x_new_line_id);
3567 PO_DEBUG.debug_var(d_module, l_progress, 'l_next_line_num', l_next_line_num);
3568 END IF;
3569
3570 INSERT INTO PO_REQUISITION_LINES_ALL(
3571 REQUISITION_LINE_ID ,
3572 REQUISITION_HEADER_ID ,
3573 LINE_NUM ,
3574 LINE_TYPE_ID ,
3575 CATEGORY_ID ,
3576 ITEM_DESCRIPTION ,
3577 UNIT_MEAS_LOOKUP_CODE ,
3578 UNIT_PRICE ,
3579 QUANTITY ,
3580 DELIVER_TO_LOCATION_ID ,
3581 TO_PERSON_ID ,
3582 LAST_UPDATE_DATE ,
3583 LAST_UPDATED_BY ,
3584 SOURCE_TYPE_CODE ,
3585 LAST_UPDATE_LOGIN ,
3586 CREATION_DATE ,
3587 CREATED_BY ,
3588 ITEM_ID ,
3589 ITEM_REVISION ,
3590 QUANTITY_DELIVERED ,
3591 SUGGESTED_BUYER_ID ,
3592 RFQ_REQUIRED_FLAG ,
3593 NEED_BY_DATE ,
3594 LINE_LOCATION_ID ,
3595 MODIFIED_BY_AGENT_FLAG ,
3596 PARENT_REQ_LINE_ID ,
3597 JUSTIFICATION ,
3598 NOTE_TO_AGENT ,
3599 NOTE_TO_RECEIVER ,
3600 PURCHASING_AGENT_ID ,
3601 BLANKET_PO_HEADER_ID ,
3602 BLANKET_PO_LINE_NUM ,
3603 SUGGESTED_VENDOR_NAME ,
3604 SUGGESTED_VENDOR_LOCATION ,
3605 SUGGESTED_VENDOR_CONTACT ,
3606 SUGGESTED_VENDOR_PHONE ,
3607 SUGGESTED_VENDOR_PRODUCT_CODE,
3608 UN_NUMBER_ID ,
3609 HAZARD_CLASS_ID ,
3610 MUST_USE_SUGG_VENDOR_FLAG ,
3611 REFERENCE_NUM ,
3612 ON_RFQ_FLAG ,
3613 URGENT_FLAG ,
3614 CANCEL_FLAG ,
3615 SOURCE_ORGANIZATION_ID ,
3616 SOURCE_SUBINVENTORY ,
3617 DESTINATION_TYPE_CODE ,
3618 DESTINATION_ORGANIZATION_ID ,
3619 DESTINATION_SUBINVENTORY ,
3620 QUANTITY_CANCELLED ,
3621 CANCEL_DATE ,
3622 CANCEL_REASON ,
3623 CLOSED_CODE ,
3624 AGENT_RETURN_NOTE ,
3625 CHANGED_AFTER_RESEARCH_FLAG ,
3626 VENDOR_ID ,
3627 VENDOR_SITE_ID ,
3628 VENDOR_CONTACT_ID ,
3629 RESEARCH_AGENT_ID ,
3630 ON_LINE_FLAG ,
3631 WIP_ENTITY_ID ,
3632 WIP_LINE_ID ,
3633 WIP_REPETITIVE_SCHEDULE_ID ,
3634 WIP_OPERATION_SEQ_NUM ,
3635 WIP_RESOURCE_SEQ_NUM ,
3636 BOM_RESOURCE_ID ,
3637 ATTRIBUTE_CATEGORY ,
3638 DESTINATION_CONTEXT ,
3639 INVENTORY_SOURCE_CONTEXT ,
3640 VENDOR_SOURCE_CONTEXT ,
3641 ATTRIBUTE1 ,
3642 ATTRIBUTE2 ,
3643 ATTRIBUTE3 ,
3644 ATTRIBUTE4 ,
3645 ATTRIBUTE5 ,
3646 ATTRIBUTE6 ,
3647 ATTRIBUTE7 ,
3648 ATTRIBUTE8 ,
3649 ATTRIBUTE9 ,
3650 ATTRIBUTE10 ,
3651 ATTRIBUTE11 ,
3652 ATTRIBUTE12 ,
3653 ATTRIBUTE13 ,
3654 ATTRIBUTE14 ,
3655 ATTRIBUTE15 ,
3656 CURRENCY_CODE ,
3657 CURRENCY_UNIT_PRICE ,
3658 DOCUMENT_TYPE_CODE ,
3659 RATE ,
3660 RATE_DATE ,
3661 RATE_TYPE ,
3662 TAX_CODE_ID ,
3663 TAX_USER_OVERRIDE_FLAG ,
3664 TAX_STATUS_INDICATOR ,
3665 ORG_ID ,
3666 ORDER_TYPE_LOOKUP_CODE ,
3667 PURCHASE_BASIS ,
3668 MATCHING_BASIS ,
3669 BASE_UNIT_PRICE ,
3670 DROP_SHIP_FLAG ,
3671 CATALOG_TYPE ,
3672 CATALOG_SOURCE
3673 )
3674 SELECT
3675 x_new_line_id ,
3676 PORL.REQUISITION_HEADER_ID ,
3677 l_next_line_num ,
3678 PORL.LINE_TYPE_ID ,
3679 PORL.CATEGORY_ID ,
3680 PORL.ITEM_DESCRIPTION ,
3681 PORL.UNIT_MEAS_LOOKUP_CODE ,
3682 PORL.unit_price ,
3683 p_line_quantity ,
3684 PORL.DELIVER_TO_LOCATION_ID ,
3685 PORL.TO_PERSON_ID ,
3686 SYSDATE ,
3687 p_user_id ,
3688 PORL.SOURCE_TYPE_CODE ,
3689 p_login_id ,
3690 PORL.CREATION_DATE ,
3691 p_user_id ,
3692 PORL.ITEM_ID ,
3693 PORL.ITEM_REVISION ,
3694 0 ,
3695 PORL.SUGGESTED_BUYER_ID ,
3696 PORL.RFQ_REQUIRED_FLAG ,
3697 PORL.NEED_BY_DATE ,
3698 (-1 * PORL.LINE_LOCATION_ID) ,
3699 PORL.MODIFIED_BY_AGENT_FLAG ,
3700 (-1 * PORL.REQUISITION_LINE_ID) ,
3701 PORL.JUSTIFICATION ,
3702 PORL.NOTE_TO_AGENT ,
3703 PORL.NOTE_TO_RECEIVER ,
3704 PORL.PURCHASING_AGENT_ID ,
3705 PORL.BLANKET_PO_HEADER_ID ,
3706 PORL.BLANKET_PO_LINE_NUM ,
3707 PORL.SUGGESTED_VENDOR_NAME ,
3708 PORL.SUGGESTED_VENDOR_LOCATION ,
3709 PORL.SUGGESTED_VENDOR_CONTACT ,
3710 PORL.SUGGESTED_VENDOR_PHONE ,
3711 PORL.SUGGESTED_VENDOR_PRODUCT_CODE,
3712 PORL.UN_NUMBER_ID ,
3713 PORL.HAZARD_CLASS_ID ,
3714 PORL.MUST_USE_SUGG_VENDOR_FLAG ,
3715 PORL.REFERENCE_NUM ,
3716 PORL.ON_RFQ_FLAG ,
3717 PORL.URGENT_FLAG ,
3718 PORL.CANCEL_FLAG ,
3719 PORL.SOURCE_ORGANIZATION_ID ,
3720 PORL.SOURCE_SUBINVENTORY ,
3721 PORL.DESTINATION_TYPE_CODE ,
3722 PORL.DESTINATION_ORGANIZATION_ID ,
3723 PORL.DESTINATION_SUBINVENTORY ,
3724 PORL.QUANTITY_CANCELLED ,
3725 PORL.CANCEL_DATE ,
3726 PORL.CANCEL_REASON ,
3727 PORL.CLOSED_CODE ,
3728 PORL.AGENT_RETURN_NOTE ,
3729 PORL.CHANGED_AFTER_RESEARCH_FLAG ,
3730 PORL.VENDOR_ID ,
3731 PORL.VENDOR_SITE_ID ,
3732 PORL.VENDOR_CONTACT_ID ,
3733 PORL.RESEARCH_AGENT_ID ,
3734 PORL.ON_LINE_FLAG ,
3735 PORL.WIP_ENTITY_ID ,
3736 PORL.WIP_LINE_ID ,
3737 PORL.WIP_REPETITIVE_SCHEDULE_ID ,
3738 PORL.WIP_OPERATION_SEQ_NUM ,
3739 PORL.WIP_RESOURCE_SEQ_NUM ,
3740 PORL.BOM_RESOURCE_ID ,
3741 PORL.ATTRIBUTE_CATEGORY ,
3742 PORL.DESTINATION_CONTEXT ,
3743 PORL.INVENTORY_SOURCE_CONTEXT ,
3744 PORL.VENDOR_SOURCE_CONTEXT ,
3745 PORL.ATTRIBUTE1 ,
3746 PORL.ATTRIBUTE2 ,
3747 PORL.ATTRIBUTE3 ,
3748 PORL.ATTRIBUTE4 ,
3749 PORL.ATTRIBUTE5 ,
3750 PORL.ATTRIBUTE6 ,
3751 PORL.ATTRIBUTE7 ,
3752 PORL.ATTRIBUTE8 ,
3753 PORL.ATTRIBUTE9 ,
3754 PORL.ATTRIBUTE10 ,
3755 PORL.ATTRIBUTE11 ,
3756 PORL.ATTRIBUTE12 ,
3757 PORL.ATTRIBUTE13 ,
3758 PORL.ATTRIBUTE14 ,
3759 PORL.ATTRIBUTE15 ,
3760 PORL.CURRENCY_CODE ,
3761 PORL.CURRENCY_UNIT_PRICE ,
3762 PORL.DOCUMENT_TYPE_CODE ,
3763 PORL.RATE ,
3764 PORL.RATE_DATE ,
3765 PORL.RATE_TYPE ,
3766 PORL.TAX_CODE_ID ,
3767 PORL.TAX_USER_OVERRIDE_FLAG ,
3768 PORL.TAX_STATUS_INDICATOR ,
3769 PORL.ORG_ID ,
3770 PORL.ORDER_TYPE_LOOKUP_CODE ,
3771 PORL.PURCHASE_BASIS ,
3772 PORL.MATCHING_BASIS ,
3773 PORL.BASE_UNIT_PRICE ,
3774 PORL.DROP_SHIP_FLAG ,
3775 PORL.CATALOG_TYPE ,
3776 PORL.CATALOG_SOURCE
3777 FROM
3778 PO_REQUISITION_LINES_ALL PORL,
3779 po_line_locations POLL ,
3780 po_lines POL
3781 WHERE
3782 PORL.REQUISITION_LINE_ID = p_old_line_id
3783 AND PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
3784 AND POLL.PO_LINE_ID = POL.PO_LINE_ID;
3785
3786 l_progress := '003' ;
3787
3788 PO_NOTES_SV.COPY_NOTES (
3789 X_orig_id=>p_old_line_id,
3790 X_orig_column=> 'REQUISITION_LINE_ID',
3791 X_orig_table=> 'PO_REQUISITION_LINES',
3792 X_add_on_title=>NULL,
3793 X_new_id=> x_new_line_id,
3794 X_new_column=> 'REQUISITION_LINE_ID',
3795 X_new_table=> 'PO_REQUISITION_LINES',
3796 X_last_updated_by=>p_user_id,
3797 X_last_update_login=> p_login_id);
3798
3799
3800 EXCEPTION
3801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3802 x_msg_data := FND_MSG_PUB.GET(
3803 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
3804 P_ENCODED => 'F');
3805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3806 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
3807 WHEN FND_API.G_EXC_ERROR THEN
3808 x_msg_data := FND_MSG_PUB.GET(
3809 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
3810 P_ENCODED => 'F');
3811 x_return_status := FND_API.G_RET_STS_ERROR;
3812 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
3813 WHEN OTHERS THEN
3814 IF (G_DEBUG_UNEXP) THEN
3815 FND_LOG.STRING(
3816 FND_LOG.LEVEL_UNEXPECTED,
3817 d_module || '.OTHERS_EXCEPTION',
3818 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
3819 END IF;
3820 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
3821 x_msg_data := FND_MSG_PUB.GET(
3822 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
3823 P_ENCODED => 'F');
3824 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3825
3826 END create_req_line;
3827
3828 --------------------------------------------------------------------------------
3829 --Start of Comments
3830 --Name: create_req_dist
3831 --Function:
3832 -- Create requisition distributions to take the quantity
3833 -- that was ordered but not billed/delivered for the given
3834 -- req line.
3835 -- Insert into PO_REQ_DISTRIBUTIONS.
3836 -- ID = PO_REQ_DISTRIBUTIONS_S.NEXTVAL,
3837 -- requisition_line_id = new_line_id,
3838 -- quantity = quantity_cancelled from associated po_distribution.
3839 -- source_req_dist_id = PORD.distribution_id
3840
3841 -- This routine is called only if recreate_demand_flag is ON
3842
3843 --
3844 --Parameters:
3845 --IN:
3846 -- p_req_line_id
3847 -- p_user_id
3848 -- p_login_id
3849 --
3850 --
3851 --IN OUT :
3852 --OUT :
3853 -- x_msg_data
3854 -- x_return_status
3855 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
3856 -- FND_API.G_RET_STS_ERROR if cancel action fails
3857 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3858 --
3859 --End of Comments
3860 --------------------------------------------------------------------------------
3861
3862 PROCEDURE create_req_dist(
3863 p_api_version IN NUMBER,
3864 p_init_msg_list IN VARCHAR2,
3865 p_req_line_id IN NUMBER,
3866 p_user_id IN po_lines.last_updated_by%TYPE,
3867 p_login_id IN po_lines.last_update_login%TYPE,
3868 x_return_status OUT NOCOPY VARCHAR2,
3869 x_msg_data OUT NOCOPY VARCHAR2)
3870
3871 IS
3872
3873 d_api_name CONSTANT VARCHAR2(30) := 'create_req_dist';
3874 d_api_version CONSTANT NUMBER := 1.0;
3875 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
3876 l_progress VARCHAR2(3) := '000' ;
3877 l_uom_conv NUMBER := 0;
3878
3879 BEGIN
3880 -- Start standard API initialization
3881 IF FND_API.to_boolean(p_init_msg_list) THEN
3882 FND_MSG_PUB.initialize;
3883 END IF;
3884
3885 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
3886 d_api_name, g_pkg_name) THEN
3887 RAISE FND_API.g_exc_unexpected_error;
3888 END IF;
3889
3890
3891
3892 IF g_debug_stmt THEN
3893 PO_DEBUG.debug_begin(d_module);
3894 PO_DEBUG.debug_var(d_module, l_progress, 'p_req_line_id', p_req_line_id);
3895 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
3896 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
3897 END IF;
3898
3899 l_progress := '001' ;
3900 x_msg_data := NULL;
3901 x_return_status := FND_API.G_RET_STS_SUCCESS;
3902
3903 calc_uom_conv (
3904 p_api_version=> 1.0,
3905 p_init_msg_list=>FND_API.G_FALSE,
3906 p_req_line_id=>p_req_line_id,
3907 p_fc_level=>c_after_FC,
3908 x_uom_conv=>l_uom_conv);
3909
3910 IF g_debug_stmt THEN
3911 PO_DEBUG.debug_var(d_module, l_progress, 'l_uom_conv', l_uom_conv);
3912 END IF;
3913
3914 l_progress := '002' ;
3915
3916 INSERT INTO PO_REQ_DISTRIBUTIONS_ALL (
3917 distribution_id ,
3918 last_update_date ,
3919 last_updated_by ,
3920 requisition_line_id ,
3921 set_of_books_id ,
3922 code_combination_id ,
3923 req_line_quantity ,
3924 last_update_login ,
3925 creation_date ,
3926 created_by ,
3927 distribution_num ,
3928 gl_encumbered_date ,
3929 gl_encumbered_period_name ,
3930 gl_cancelled_date ,
3931 failed_funds_lookup_code ,
3932 source_req_distribution_id ,
3933 ATTRIBUTE1 ,
3934 ATTRIBUTE2 ,
3935 ATTRIBUTE3 ,
3936 ATTRIBUTE4 ,
3937 ATTRIBUTE5 ,
3938 ATTRIBUTE6 ,
3939 ATTRIBUTE7 ,
3940 ATTRIBUTE8 ,
3941 ATTRIBUTE9 ,
3942 ATTRIBUTE10 ,
3943 ATTRIBUTE11 ,
3944 ATTRIBUTE12 ,
3945 ATTRIBUTE13 ,
3946 ATTRIBUTE14 ,
3947 ATTRIBUTE15 ,
3948 ATTRIBUTE_CATEGORY ,
3949 ACCRUAL_ACCOUNT_ID ,
3950 BUDGET_ACCOUNT_ID ,
3951 VARIANCE_ACCOUNT_ID ,
3952 government_context ,
3953 project_id ,
3954 task_id ,
3955 expenditure_type ,
3956 project_accounting_context ,
3957 expenditure_organization_id,
3958 project_related_flag ,
3959 expenditure_item_date ,
3960 RECOVERY_RATE ,
3961 TAX_RECOVERY_OVERRIDE_FLAG ,
3962 NONRECOVERABLE_TAX ,
3963 ORG_ID ,
3964 prevent_encumbrance_flag
3965 )
3966 SELECT
3967 PO_REQ_DISTRIBUTIONS_S.NEXTVAL,
3968 SYSDATE ,
3969 p_user_id ,
3970 p_req_line_id ,
3971 PORD.set_of_books_id ,
3972 PORD.code_combination_id ,
3973 (PORD.req_line_quantity
3974 - l_uom_conv * greatest(NVL(POD.quantity_delivered, 0),
3975 NVL(POD.quantity_billed, 0))
3976 ) ,
3977 p_login_id ,
3978 SYSDATE ,
3979 p_user_id ,
3980 rownum ,
3981 PORD.gl_encumbered_date ,
3982 PORD.gl_encumbered_period_name ,
3983 PORD.gl_cancelled_date ,
3984 PORD.failed_funds_lookup_code ,
3985 PORD.distribution_id ,
3986 PORD.ATTRIBUTE1 ,
3987 PORD.ATTRIBUTE2 ,
3988 PORD.ATTRIBUTE3 ,
3989 PORD.ATTRIBUTE4 ,
3990 PORD.ATTRIBUTE5 ,
3991 PORD.ATTRIBUTE6 ,
3992 PORD.ATTRIBUTE7 ,
3993 PORD.ATTRIBUTE8 ,
3994 PORD.ATTRIBUTE9 ,
3995 PORD.ATTRIBUTE10 ,
3996 PORD.ATTRIBUTE11 ,
3997 PORD.ATTRIBUTE12 ,
3998 PORD.ATTRIBUTE13 ,
3999 PORD.ATTRIBUTE14 ,
4000 PORD.ATTRIBUTE15 ,
4001 PORD.ATTRIBUTE_CATEGORY ,
4002 PORD.ACCRUAL_ACCOUNT_ID ,
4003 PORD.BUDGET_ACCOUNT_ID ,
4004 PORD.VARIANCE_ACCOUNT_ID ,
4005 PORD.government_context ,
4006 PORD.project_id ,
4007 PORD.task_id ,
4008 PORD.expenditure_type ,
4009 PORD.project_accounting_context ,
4010 PORD.expenditure_organization_id ,
4011 PORD.project_related_flag ,
4012 PORD.expenditure_item_date ,
4013 PORD.RECOVERY_RATE ,
4014 PORD.TAX_RECOVERY_OVERRIDE_FLAG ,
4015 (DECODE(
4016 SIGN(PORD.req_line_quantity
4017 - l_uom_conv * greatest(NVL(POD.quantity_delivered, 0),
4018 NVL(POD.quantity_billed, 0)
4019 )
4020 ),
4021 -1,
4022 0,
4023 (PORD.req_line_quantity
4024 - l_uom_conv * greatest(NVL(POD.quantity_delivered, 0),
4025 NVL(POD.quantity_billed, 0)
4026 )
4027 )
4028 ) / PORD.req_line_quantity) * PORD.nonrecoverable_tax,
4029 PORL_NEW.ORG_ID ,
4030 NVL(DECODE( POD.prevent_encumbrance_flag,
4031 'Y',
4032 'Y',
4033 DECODE(
4034 PORL_NEW.org_id,
4035 BLANKET.org_id,
4036 BLANKET.encumbrance_required_flag,
4037 NULL )
4038 ),
4039 'N')
4040
4041 FROM
4042 PO_REQ_DISTRIBUTIONS_ALL PORD ,
4043 PO_REQUISITION_LINES_ALL PORL_OLD,
4044 PO_REQUISITION_LINES_ALL PORL_NEW,
4045 PO_DISTRIBUTIONS_ALL POD ,
4046 PO_LINE_LOCATIONS PLL ,
4047 po_headers BLANKET
4048 WHERE
4049 PORL_NEW.requisition_line_id = p_req_line_id
4050 AND PORL_OLD.requisition_line_id = (-1) * PORL_NEW.parent_req_line_id
4051 AND PORD.requisition_line_id = PORL_OLD.requisition_line_id
4052 AND POD.req_distribution_id = PORD.distribution_id
4053 AND POD.line_location_id = PLL.line_location_id
4054 AND PLL.shipment_type IN (
4055 'STANDARD',
4056 'PLANNED' ,
4057 'BLANKET')
4058 AND NVL(POD.quantity_cancelled, 0) >= 0
4059 AND PORL_NEW.blanket_po_header_id = BLANKET.po_header_id(+)
4060 AND (PORD.req_line_quantity
4061 - l_uom_conv * Greatest(NVL(POD.quantity_delivered, 0),
4062 NVL(POD.quantity_billed, 0))
4063 > 0) ;
4064
4065 EXCEPTION
4066 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4067 x_msg_data := FND_MSG_PUB.GET(
4068 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
4069 P_ENCODED => 'F');
4070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4071 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
4072 WHEN FND_API.G_EXC_ERROR THEN
4073 x_msg_data := FND_MSG_PUB.GET(
4074 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
4075 P_ENCODED => 'F');
4076 x_return_status := FND_API.G_RET_STS_ERROR;
4077 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
4078 WHEN OTHERS THEN
4079 IF (G_DEBUG_UNEXP) THEN
4080 FND_LOG.STRING(
4081 FND_LOG.LEVEL_UNEXPECTED,
4082 d_module || '.OTHERS_EXCEPTION',
4083 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
4084 END IF;
4085
4086 x_msg_data := FND_MSG_PUB.GET(
4087 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
4088 P_ENCODED => 'F');
4089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4090 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
4091
4092 END create_req_dist;
4093
4094 --------------------------------------------------------------------------------
4095 --Start of Comments
4096 --Name: update_requisitions
4097 --Function:
4098 -- Updates the cancel details on backing requisition lines.
4099 -- If recreate_demand is OFF, have to cancel the req lines. If before
4100 -- Funds Checker call, change cancel_flag to 'I', if after, change flag
4101 -- If recreate_demand is ON then :
4102 -- If nothing has been billed/delivered:
4103 -- Mark the distributions belonging to the req line.
4104 -- Set the quantity on each req distribution to be that on the
4105 -- PO distribution.
4106 -- Set the quantity on the req line to be the sum of the
4107 -- quantities on the req distributions. Set the price to be
4108 -- the price_override from the PO shipment times the calculated
4109 -- foreign currency exchange rate (1.0 for base currency POs).
4110 -- Set the unit of measure and unit class to be the same as
4111 -- those on the PO line.
4112 -- Also note that if the fc_mode is c_before_FC, we have
4113 -- to make sure that no one uses the 'freed-up' req line.
4114 -- This is done by temporarily setting the porl.line_loc_id to
4115 -- the negative of the corresponding poll.line_location_id.
4116 -- When this routine is later called with fc_mode=c_after_FC,
4117 -- this is updated to be Null, and hence freed-up.
4118 -- Otherwise(if billed/delivered >0):
4119 -- Create a new req line. Set the quantity to be that which has not
4120 -- been billed/delivered. Set the price, UOM, UOM class as above.
4121 -- Adjust the old req line. Set the quantity to be that which HAS
4122 -- been billed/delivered. Set the price, UOM, UOM class as above.
4123 -- Create new req distributions. Adjust the quantities to be that
4124 -- which has NOT been billed/delivered.
4125 -- Adjust the old req distributions. Set the quantity to be that
4126 -- which HAS been billed/delivered. Set the price, UOM,
4127 -- UOM class as above.
4128 -- Same note for fc_mode applies: On the newly created req lines,
4129 -- line_location_id is set to negative of poll.line_location_id
4130 -- if fc_mode=c_before_FC. When fc_mode=c_after_FC, this column
4131 -- will be corrected to be Null.
4132 --
4133 --Parameters:
4134 --IN:
4135 -- p_entity_level
4136 -- p_action_date
4137 -- p_entity_id
4138 -- p_document_type
4139 -- p_doc_subtype
4140 -- p_cancel_reason
4141 -- p_fc_level
4142 -- p_recreate_demand
4143 -- p_req_enc_flag
4144 -- P_user_id
4145 -- p_login_id
4146 --
4147 --
4148 --IN OUT :
4149 --OUT :
4150 -- x_req_line_id_tbl
4151 -- x_msg_data
4152 -- x_return_status
4153 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
4154 -- FND_API.G_RET_STS_ERROR if cancel action fails
4155 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4156 --
4157 --End of Comments
4158 --------------------------------------------------------------------------------
4159
4160 PROCEDURE update_requisitions(
4161 p_api_version IN NUMBER,
4162 p_init_msg_list IN VARCHAR2,
4163 p_entity_level IN VARCHAR2,
4164 p_action_date IN DATE,
4165 p_entity_id IN NUMBER,
4166 p_document_type IN VARCHAR2,
4167 p_doc_subtype IN VARCHAR2,
4168 p_cancel_reason IN VARCHAR2,
4169 p_fc_level IN VARCHAR2,
4170 p_recreate_demand IN VARCHAR2,
4171 p_req_enc_flag IN VARCHAR2,
4172 p_user_id IN po_lines.last_updated_by%TYPE,
4173 p_login_id IN po_lines.last_update_login%TYPE,
4174 x_is_new_line IN OUT NOCOPY VARCHAR2,
4175 x_return_status OUT NOCOPY VARCHAR2,
4176 x_msg_data OUT NOCOPY VARCHAR2,
4177 x_req_line_id_tbl OUT NOCOPY PO_ReqChangeRequestWF_PVT.ReqLineID_tbl_type)
4178 IS
4179
4180 d_api_name CONSTANT VARCHAR2(30) := 'update_requisitions';
4181 d_api_version CONSTANT NUMBER := 1.0;
4182 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
4183 l_progress VARCHAR2(8) := '000' ;
4184 l_qty_cancelled NUMBER := 0;
4185 l_uom_conv NUMBER := 0;
4186 l_quantity NUMBER := 0;
4187 l_msg_count NUMBER := 0;
4188 l_status BOOLEAN;
4189 l_new_req_line_id NUMBER;
4190
4191
4192 BEGIN
4193 -- Start standard API initialization
4194 IF FND_API.to_boolean(p_init_msg_list) THEN
4195 FND_MSG_PUB.initialize;
4196 END IF;
4197
4198 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
4199 d_api_name, g_pkg_name) THEN
4200 RAISE FND_API.g_exc_unexpected_error;
4201 END IF;
4202
4203
4204 IF g_debug_stmt THEN
4205 PO_DEBUG.debug_begin(d_module);
4206 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
4207 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
4208 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
4209 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
4210 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
4211 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reason', p_cancel_reason);
4212 PO_DEBUG.debug_var(d_module, l_progress, 'p_fc_level', p_fc_level);
4213 PO_DEBUG.debug_var(d_module, l_progress, 'p_recreate_demand', p_recreate_demand);
4214 PO_DEBUG.debug_var(d_module, l_progress, 'p_req_enc_flag', p_req_enc_flag);
4215 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
4216 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
4217 PO_DEBUG.debug_var(d_module, l_progress, 'x_is_new_line', x_is_new_line);
4218
4219 END IF;
4220
4221 l_progress := '002' ;
4222
4223 -- get_linked_req_lines to the entity being canceled
4224 fetch_req_lines(
4225 p_api_version=> 1.0,
4226 p_init_msg_list=>FND_API.G_FALSE,
4227 p_entity_id => p_entity_id,
4228 p_entity_level =>p_entity_level,
4229 p_document_type =>p_document_type,
4230 p_doc_subtype =>p_doc_subtype,
4231 p_fc_level => p_fc_level,
4232 x_return_status =>x_return_status,
4233 x_req_line_id_tbl =>x_req_line_id_tbl);
4234
4235
4236 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4237 RAISE FND_API.g_exc_error;
4238 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4239 RAISE FND_API.g_exc_unexpected_error;
4240 END IF;
4241
4242
4243 IF g_debug_stmt THEN
4244 PO_DEBUG.debug_var(d_module, l_progress, 'x_req_line_id_tbl.count', x_req_line_id_tbl.count);
4245 END IF;
4246
4247 l_progress := '003' ;
4248
4249
4250 FOR i IN 1..x_req_line_id_tbl.count
4251 LOOP
4252
4253 -- If recraete_demand is OFF, We have to cancel the req lines.
4254 -- If p_fc_level=c_before_FC, then change cancel_flag to 'I',
4255 -- else change flag to 'Y'
4256 IF p_recreate_demand = 'N' THEN
4257
4258 IF p_fc_level = c_before_FC THEN
4259
4260 x_is_new_line :='N';
4261 -- Update Cancel_flag to 'I' for the req line and line_location_id
4262 -- to -line_location_id, so that it will not be fetched else where
4263 -- till this cancel action completes
4264 UPDATE PO_REQUISITION_LINES_ALL PORL
4265 SET PORL.cancel_flag = 'I' ,
4266 PORL.LAST_UPDATE_DATE = SYSDATE ,
4267 PORL.LAST_UPDATED_BY = p_user_id ,
4268 PORL.LAST_UPDATE_LOGIN = p_login_id,
4269 PORL.line_location_id = -1 * PORL.line_location_id
4270 WHERE PORL.requisition_line_id = x_req_line_id_tbl(i);
4271
4272 IF g_debug_stmt THEN
4273 PO_DEBUG.debug_var(d_module, l_progress, 'update_req lines before_fc', SQL%ROWCOUNT);
4274 END IF;
4275
4276 ELSE
4277
4278 l_progress := '004' ;
4279
4280 -- Update Cancel_flag to 'Y',quantity for the req line and its distribution
4281 update_req_details_after_fc(
4282 p_api_version=> 1.0,
4283 p_init_msg_list=>FND_API.G_FALSE,
4284 p_req_line_id=>x_req_line_id_tbl(i),
4285 p_recreate_demand_flag=>p_recreate_demand,
4286 p_user_id=>p_user_id,
4287 p_login_id=>p_login_id,
4288 p_is_new_req_line=>x_is_new_line,
4289 x_msg_data =>x_msg_data,
4290 x_return_status =>x_return_status);
4291
4292
4293 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4294 RAISE FND_API.g_exc_error;
4295 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4296 RAISE FND_API.g_exc_unexpected_error;
4297 END IF;
4298
4299 l_progress := '005' ;
4300
4301 -- Remove the Req Line Supply from mtl supply table
4302 cancel_supply(
4303 p_api_version=> 1.0,
4304 p_init_msg_list=>FND_API.G_FALSE,
4305 p_entity_level =>NULL,
4306 p_entity_id =>x_req_line_id_tbl(i),
4307 p_doc_id =>x_req_line_id_tbl(i),
4308 p_document_type =>c_doc_type_REQUISITION,
4309 p_doc_subtype =>NULL,
4310 p_recreate_flag =>'N',
4311 x_return_status =>x_return_status,
4312 x_msg_data =>x_msg_data);
4313
4314 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4315 RAISE FND_API.g_exc_error;
4316 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4317 RAISE FND_API.g_exc_unexpected_error;
4318 END IF;
4319
4320
4321 END IF; --p_fc_level
4322
4323 ELSE -- If recreate_demand is ON then
4324
4325 IF p_fc_level = c_before_FC THEN
4326
4327 l_progress := '005' ;
4328
4329 -- if nothing has been billed/delivered ,then we don't need
4330 -- to create a new req line; we just need to update the old one,
4331 -- since all ordered is cancelled
4332 IF is_qty_del_bill_zero(
4333 p_api_version=> 1.0,
4334 p_init_msg_list=> FND_API.G_FALSE,
4335 p_req_line_id=>x_req_line_id_tbl(i)) THEN
4336
4337 x_is_new_line :='N';
4338
4339 --The below sql will only consider the req distributions which are sourced to a blanket agreement
4340
4341 UPDATE PO_REQ_DISTRIBUTIONS_ALL PORD
4342 SET PORD.last_update_date = SYSDATE ,
4343 PORD.last_updated_by = p_user_id ,
4344 PORD.last_update_login = p_login_id,
4345 PORD.prevent_encumbrance_flag =
4346 (SELECT NVL(DECODE(
4347 POD.prevent_encumbrance_flag,
4348 'Y',
4349 'Y',
4350 DECODE(PORL.org_id,
4351 BLANKET.org_id,
4352 BLANKET.encumbrance_required_flag,
4353 NULL)),
4354 'N')
4355 FROM PO_REQUISITION_LINES_ALL PORL,
4356 PO_DISTRIBUTIONS_ALL POD ,
4357 po_line_locations PLL ,
4358 po_headers BLANKET
4359 WHERE PORL.requisition_line_id = PORD.requisition_line_id
4360 AND POD.req_distribution_id = PORD.distribution_id
4361 AND POD.line_location_id = PLL.line_location_id
4362 AND PLL.shipment_type IN (
4363 'STANDARD',
4364 'PLANNED' ,
4365 'BLANKET')
4366 AND PORL.blanket_po_header_id = BLANKET.po_header_id)
4367 WHERE PORD.requisition_line_id= x_req_line_id_tbl(i)
4368 AND EXISTS (SELECT 1
4369 FROM po_requisition_lines_all porl
4370 WHERE porl.requisition_line_id = PORD.requisition_line_id
4371 AND porl.blanket_po_header_id IS NOT NULL);
4372
4373 IF g_debug_stmt THEN
4374 PO_DEBUG.debug_var(d_module, l_progress, 'update_req Dsitributions before_fc', SQL%ROWCOUNT);
4375 END IF;
4376
4377
4378 l_progress := '006' ;
4379
4380 -- The below sql is to update the prevent encumbrance flag for all the req distributions
4381 -- whose autocreated Standard PO/Release are backed by encumbered GBPA
4382
4383 -- The prevent encumbrance flag is updated to 'Y' when the main document is reserved
4384 -- and is backed by an encumbered BPA
4385 -- The prevent encumbrance flag to 'N' on all the req distributions
4386 -- which were processed during Reserve action
4387
4388 UPDATE PO_REQ_DISTRIBUTIONS_ALL PORD
4389 SET PORD.last_update_date = SYSDATE,
4390 PORD.last_updated_by = p_user_id,
4391 PORD.last_update_login = p_login_id,
4392 PORD.prevent_encumbrance_flag =
4393 DECODE(
4394 p_req_enc_flag,
4395 'Y',
4396 'N',
4397 pord.prevent_encumbrance_flag)
4398 WHERE PORD.requisition_line_id = x_req_line_id_tbl(i)
4399 AND EXISTS (SELECT 1
4400 FROM po_requisition_lines_all porl
4401 WHERE porl.requisition_line_id = PORD.requisition_line_id
4402 AND porl.blanket_po_header_id IS NULL)
4403 AND EXISTS (SELECT 1
4404 FROM po_distributions_all poda,
4405 po_lines pol,
4406 po_distributions_all pod
4407 WHERE poda.po_header_id = pol.from_header_id
4408 AND pol.po_line_id = pod.po_line_id
4409 AND pod.req_distribution_id = pord.distribution_id
4410 AND pod.distribution_type = c_doc_subtype_STANDARD
4411 UNION
4412 SELECT 1
4413 FROM po_distributions_all poda,
4414 po_distributions_all pod
4415 WHERE pod.req_distribution_id = pord.distribution_id
4416 AND pod.distribution_type = c_doc_subtype_BLANKET
4417 AND pod.po_header_id = poda.po_header_id
4418 AND poda.distribution_type = 'AGREEMENT');
4419
4420 --Update the requisition line. Get the quantity from the
4421 -- requisition distributions first.
4422
4423
4424 SELECT SUM(PORD.REQ_LINE_QUANTITY)
4425 INTO l_quantity
4426 FROM PO_REQ_DISTRIBUTIONS_ALL PORD
4427 WHERE PORD.REQUISITION_LINE_ID = x_req_line_id_tbl(i);
4428
4429 IF g_debug_stmt THEN
4430 PO_DEBUG.debug_var(d_module, l_progress, 'l_quantity', l_quantity);
4431 END IF;
4432
4433 l_progress := '007';
4434
4435 UPDATE PO_REQUISITION_LINES_ALL PORL
4436 SET(
4437 PORL.LINE_LOCATION_ID,
4438 PORL.QUANTITY ,
4439 PORL.LAST_UPDATE_DATE,
4440 PORL.LAST_UPDATED_BY ,
4441 PORL.LAST_UPDATE_LOGIN) =
4442 (
4443 SELECT
4444 (-1 * PORL.LINE_LOCATION_ID),
4445 DECODE(porl.order_type_lookup_code,
4446 'RATE', NULL,
4447 'FIXED PRICE',NULL,
4448 l_quantity),
4449 SYSDATE ,
4450 p_user_id ,
4451 p_login_id
4452 FROM po_line_locations POLL,
4453 po_lines POL
4454 WHERE PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
4455 AND POLL.PO_LINE_ID = POL.PO_LINE_ID
4456 )
4457 WHERE PORL.REQUISITION_LINE_ID = x_req_line_id_tbl(i);
4458
4459 IF g_debug_stmt THEN
4460 PO_DEBUG.debug_var(d_module, l_progress, 'update_req Lines before_fc', SQL%ROWCOUNT);
4461 END IF;
4462
4463 ELSE
4464
4465 -- if something has been billed/delivered, we need to create a new
4466 -- req line for freed up (=cancelled) orders and update the
4467 -- quantities on oldreq lines and distributions.
4468
4469 l_progress := '008';
4470
4471 x_is_new_line :='Y';
4472
4473 -- Get the UOM conversion between req line and its corresponding PO Line
4474 calc_uom_conv (
4475 p_api_version=> 1.0,
4476 p_init_msg_list=>FND_API.G_FALSE,
4477 p_fc_level=>c_before_FC,
4478 p_req_line_id=>x_req_line_id_tbl(i),
4479 x_uom_conv=>l_uom_conv);
4480
4481
4482 IF g_debug_stmt THEN
4483 PO_DEBUG.debug_var(d_module, l_progress, 'l_uom_conv', l_uom_conv);
4484 END IF;
4485
4486 l_progress := '009';
4487
4488 -- Calculate the quantity left over quanity to be updated on new req line.
4489 calc_qty_canceled(
4490 p_api_version=> 1.0,
4491 p_init_msg_list=>FND_API.G_FALSE,
4492 p_req_line_id=>x_req_line_id_tbl(i),
4493 p_uom_conv=>l_uom_conv,
4494 x_qty_cancelled=>l_qty_cancelled);
4495
4496 IF g_debug_stmt THEN
4497 PO_DEBUG.debug_var(d_module, l_progress, 'l_qty_cancelled', l_qty_cancelled);
4498 END IF;
4499
4500 IF l_qty_cancelled <> 0 THEN
4501 -- If the quantity delivered/billed on the PO is less than the quantity
4502 -- that was ordered on the Req ,then create new req line and distribution
4503 create_req_line(
4504 p_api_version=> 1.0,
4505 p_init_msg_list=>FND_API.G_FALSE,
4506 p_old_line_id =>x_req_line_id_tbl(i),
4507 p_line_quantity =>l_qty_cancelled,
4508 p_user_id =>p_user_id,
4509 p_login_id =>p_login_id,
4510 x_new_line_id =>l_new_req_line_id,
4511 x_return_status =>x_return_status,
4512 x_msg_data =>x_msg_data);
4513
4514 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4515 RAISE FND_API.g_exc_error;
4516 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4517 RAISE FND_API.g_exc_unexpected_error;
4518 END IF;
4519
4520
4521 create_req_dist(
4522 p_api_version=> 1.0,
4523 p_init_msg_list=>FND_API.G_FALSE,
4524 p_req_line_id=>l_new_req_line_id,
4525 p_user_id =>p_user_id,
4526 p_login_id =>p_login_id,
4527 x_return_status =>x_return_status,
4528 x_msg_data =>x_msg_data);
4529
4530 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4531 RAISE FND_API.g_exc_error;
4532 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4533 RAISE FND_API.g_exc_unexpected_error;
4534 END IF;
4535
4536 ELSE
4537 -- If l_qty_cancelled =0 (i.e., the quantity delivered/billed on the PO is
4538 -- greater than or equal to the quantity that was ordered on the Req)
4539 -- then we are setting the cancel_flag of the Req line to 'Y',
4540 -- so that the encumbrance code avoids picking it up.
4541 -- Note that the "after_fc" cursor in fetch_req_lines does not
4542 -- fetch these Req lines, as their line_location_id had never
4543 -- been negated.
4544
4545 UPDATE PO_REQUISITION_LINES_ALL PORL
4546 SET PORL.cancel_flag = 'Y',--doubt
4547 PORL.contractor_status = NULL ,
4548 PORL.cancel_date = SYSDATE ,
4549 PORL.last_update_date = SYSDATE ,
4550 PORL.last_updated_by = p_user_id,
4551 PORL.last_update_login = p_login_id
4552 WHERE PORL.requisition_line_id = x_req_line_id_tbl(i);
4553
4554 END IF ; -- l_qty_cancelled<>0
4555
4556
4557 UPDATE PO_REQ_DISTRIBUTIONS_ALL PORD
4558 SET PORD.last_update_date = SYSDATE ,
4559 PORD.last_updated_by = p_user_id,
4560 PORD.last_update_login = p_login_id
4561 WHERE PORD.requisition_line_id = x_req_line_id_tbl(i);
4562
4563
4564 END IF; -- if is_qty_del_bill_zero
4565
4566 ELSE --p_fc_level=c_after_FC
4567
4568 -- Now we have 2 situations after funds checker:
4569 -- 1. No new req lines are created, so all we have to do is null out
4570 -- PORL.line_location_id (= free up the req line).
4571 -- 2. A new req line with new distribution associated with it must
4572 -- have been created.So we need to Null out the line_location_id
4573 -- of this new req. line.
4574 -- Call supply to create a req supply for the new lines.
4575
4576 -- Note that The req_line_id available now is the new req line created.
4577 -- in this situation the old req line (reflecting amount already
4578 -- received/billed and hence noncancellable) has
4579 -- been updated during pre_fundschecker and we don't even get it here.
4580
4581 update_req_details_after_fc(
4582 p_api_version=> 1.0,
4583 p_init_msg_list=>FND_API.G_FALSE,
4584 p_req_line_id=>x_req_line_id_tbl(i),
4585 p_recreate_demand_flag=>p_recreate_demand,
4586 p_user_id=>p_user_id,
4587 p_login_id=>p_login_id,
4588 p_is_new_req_line=>x_is_new_line,
4589 x_return_status =>x_return_status,
4590 x_msg_data =>x_msg_data);
4591
4592 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4593 RAISE FND_API.g_exc_error;
4594 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4595 RAISE FND_API.g_exc_unexpected_error;
4596 END IF;
4597
4598 -- Create req supply for the new lines
4599 l_status := po_supply.po_req_supply(
4600 p_docid => 0,
4601 p_lineid =>x_req_line_id_tbl(i),
4602 p_shipid =>0,
4603 p_action =>'Create_Req_Line_Supply',
4604 p_recreate_flag =>TRUE,
4605 p_qty =>0,
4606 p_receipt_date =>SYSDATE,
4607 p_reservation_action=>NULL,
4608 p_ordered_uom =>NULL);
4609
4610
4611 END IF; -- if p_fc_level=c_before_FC
4612 END IF; ---p_recreate_demand = 'N'
4613
4614 END LOOP;
4615
4616 EXCEPTION
4617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4618 x_msg_data := FND_MSG_PUB.GET(
4619 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
4620 P_ENCODED => 'F');
4621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4622 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
4623 IF (g_debug_unexp) THEN
4624 FND_LOG.STRING(
4625 FND_LOG.LEVEL_UNEXPECTED,
4626 d_module || '.Error',
4627 'Error: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
4628 END IF;
4629
4630
4631 WHEN FND_API.G_EXC_ERROR THEN
4632 x_msg_data := FND_MSG_PUB.GET(
4633 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
4634 P_ENCODED => 'F');
4635 x_return_status := FND_API.G_RET_STS_ERROR;
4636 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
4637 IF (g_debug_unexp) THEN
4638 FND_LOG.STRING(
4639 FND_LOG.LEVEL_UNEXPECTED,
4640 d_module || '.UnExpected',
4641 'EXCEPTION: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
4642 END IF;
4643
4644 WHEN OTHERS THEN
4645 IF (g_debug_unexp) THEN
4646 FND_LOG.STRING(
4647 FND_LOG.LEVEL_UNEXPECTED,
4648 d_module || '.OTHERS',
4649 'EXCEPTION: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
4650 END IF;
4651 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
4652 x_msg_data := FND_MSG_PUB.GET(
4653 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
4654 P_ENCODED => 'F');
4655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4656
4657
4658
4659 END update_requisitions;
4660
4661 --------------------------------------------------------------------------------
4662 --Start of Comments
4663 --Name: update_req_po_after_fc
4664 --Function:
4665 -- Updates the cancel details(ex: cancel_flag ='Y')on document being canceled
4666 -- and backing requisition lines after the funds control call
4667 -- (i.e. unencumbering the Document).
4668
4669 --Parameters:
4670 --IN:
4671 -- p_entity_level
4672 -- p_action_date
4673 -- p_entity_id
4674 -- p_doc_id
4675 -- p_document_type
4676 -- p_doc_subtype
4677 -- p_cancel_reason
4678 -- p_recreate_demand
4679 -- p_req_enc_flag
4680 -- p_user_id
4681 -- p_login_id
4682 --
4683 --
4684 --IN OUT :
4685 --OUT :
4686 -- x_msg_data
4687 -- x_return_status
4688 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
4689 -- FND_API.G_RET_STS_ERROR if cancel action fails
4690 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4691 --
4692 --End of Comments
4693 --------------------------------------------------------------------------------
4694
4695 PROCEDURE update_req_po_after_fc(
4696 p_api_version IN NUMBER,
4697 p_init_msg_list IN VARCHAR2,
4698 p_entity_level IN VARCHAR2,
4699 p_action_date IN DATE,
4700 p_entity_id IN NUMBER,
4701 p_doc_id IN NUMBER,
4702 p_document_type IN VARCHAR2,
4703 p_doc_subtype IN VARCHAR2,
4704 p_cancel_reason IN VARCHAR2,
4705 p_recreate_flag IN VARCHAR2,
4706 p_note_to_vendor IN VARCHAR2,
4707 p_req_enc_flag IN VARCHAR2,
4708 p_source IN VARCHAR2,
4709 p_user_id IN po_lines.last_updated_by%TYPE,
4710 p_login_id IN po_lines.last_update_login%TYPE,
4711 x_is_new_line IN OUT NOCOPY VARCHAR2,
4712 x_return_status IN OUT NOCOPY VARCHAR2,
4713 x_msg_data IN OUT NOCOPY VARCHAR2)
4714 IS
4715
4716 d_api_name CONSTANT VARCHAR2(30) := 'update_req_po_after_fc';
4717 d_api_version CONSTANT NUMBER := 1.0;
4718 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
4719
4720 l_progress VARCHAR2(3) := '000' ;
4721 l_fc_level CONSTANT VARCHAR2(10) := c_after_FC;
4722 l_req_line_id_tbl PO_ReqChangeRequestWF_PVT.ReqLineID_tbl_type;
4723
4724
4725 BEGIN
4726 -- Start standard API initialization
4727 IF FND_API.to_boolean(p_init_msg_list) THEN
4728 FND_MSG_PUB.initialize;
4729 END IF;
4730
4731 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
4732 d_api_name, g_pkg_name) THEN
4733 RAISE FND_API.g_exc_unexpected_error;
4734 END IF;
4735
4736
4737 x_return_status := FND_API.G_RET_STS_SUCCESS;
4738 x_msg_data := NULL;
4739
4740 IF g_debug_stmt THEN
4741 PO_DEBUG.debug_begin(d_module);
4742 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
4743 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
4744 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
4745 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
4746 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
4747 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
4748 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reason', p_cancel_reason);
4749 PO_DEBUG.debug_var(d_module, l_progress, 'p_recreate_flag', p_recreate_flag);
4750 PO_DEBUG.debug_var(d_module, l_progress, 'p_req_enc_flag', p_req_enc_flag);
4751 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
4752 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
4753 PO_DEBUG.debug_var(d_module, l_progress, 'p_note_to_vendor', p_note_to_vendor);
4754 PO_DEBUG.debug_var(d_module, l_progress, 'x_is_new_line', x_is_new_line);
4755 PO_DEBUG.debug_var(d_module, l_progress, 'p_source',p_source);
4756
4757 END IF;
4758
4759
4760 -- Update the cancel details on document after funds control call (unencumbering the document)
4761 update_document(
4762 p_api_version=> 1.0,
4763 p_init_msg_list=>FND_API.G_FALSE,
4764 p_entity_level =>p_entity_level,
4765 p_action_date =>p_action_date,
4766 p_entity_id =>p_entity_id,
4767 p_document_type =>p_document_type,
4768 p_doc_subtype =>p_doc_subtype,
4769 p_cancel_reason =>p_cancel_reason,
4770 p_recreate_demand =>p_recreate_flag,
4771 p_fc_level =>l_fc_level,
4772 p_note_to_vendor =>p_note_to_vendor,
4773 p_user_id =>p_user_id,
4774 p_login_id => p_login_id,
4775 x_return_status =>x_return_status,
4776 x_msg_data =>x_msg_data);
4777
4778
4779
4780 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4781 RAISE FND_API.g_exc_error;
4782 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4783 RAISE FND_API.g_exc_unexpected_error;
4784 END IF;
4785
4786 l_progress := '001';
4787 -- Update the backing requisitions accordingly after funds control call
4788 -- (unencumbering the document)
4789 update_requisitions(
4790 p_api_version=> 1.0,
4791 p_init_msg_list=>FND_API.G_FALSE,
4792 p_entity_level =>p_entity_level,
4793 p_action_date =>p_action_date,
4794 p_entity_id =>p_entity_id,
4795 p_document_type =>p_document_type,
4796 p_doc_subtype =>p_doc_subtype,
4797 p_cancel_reason =>p_cancel_reason,
4798 p_recreate_demand =>p_recreate_flag,
4799 p_req_enc_flag =>p_req_enc_flag,
4800 p_fc_level =>l_fc_level,
4801 p_user_id =>p_user_id,
4802 p_login_id =>p_login_id,
4803 x_is_new_line =>x_is_new_line,
4804 x_return_status =>x_return_status,
4805 x_msg_data =>x_msg_data,
4806 x_req_line_id_tbl =>l_req_line_id_tbl);
4807
4808 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4809 RAISE FND_API.g_exc_error;
4810 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4811 RAISE FND_API.g_exc_unexpected_error;
4812 END IF;
4813
4814 l_progress := '002';
4815
4816 -- Remove the supply for the entity being canceled
4817 cancel_supply(
4818 p_api_version=> 1.0,
4819 p_init_msg_list=>FND_API.G_FALSE,
4820 p_entity_level =>p_entity_level,
4821 p_entity_id =>p_entity_id,
4822 p_doc_id =>p_doc_id,
4823 p_document_type =>p_document_type,
4824 p_doc_subtype =>p_doc_subtype,
4825 p_recreate_flag =>p_recreate_flag,
4826 x_return_status =>x_return_status,
4827 x_msg_data =>x_msg_data);
4828
4829 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4830 RAISE FND_API.g_exc_error;
4831 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4832 RAISE FND_API.g_exc_unexpected_error;
4833 END IF;
4834
4835
4836 l_progress := '003';
4837
4838 -- Cancel corresponding tax lines
4839 cancel_tax_lines(
4840 p_api_version=> 1.0,
4841 p_init_msg_list=>FND_API.G_FALSE,
4842 p_entity_level =>p_entity_level,
4843 p_entity_id =>p_entity_id,
4844 p_doc_id =>p_doc_id,
4845 p_document_type =>p_document_type,
4846 p_doc_subtype =>p_doc_subtype,
4847 x_return_status =>x_return_status,
4848 x_msg_data =>x_msg_data);
4849
4850
4851 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4852 RAISE FND_API.g_exc_error;
4853 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4854 RAISE FND_API.g_exc_unexpected_error;
4855 END IF;
4856
4857 l_progress := '004';
4858
4859 -- As document is being canceled, cancel any pending Change Request on the PO /PO Shipment and
4860 -- also any pending requester change request for the underlying req
4861 cancel_pending_change_request(
4862 p_api_version=> 1.0,
4863 p_init_msg_list=>FND_API.G_FALSE,
4864 p_entity_level =>p_entity_level,
4865 p_entity_id =>p_entity_id,
4866 p_doc_id =>p_doc_id,
4867 p_document_type =>p_document_type,
4868 p_doc_subtype =>p_doc_subtype,
4869 p_source =>p_source,
4870 p_req_line_id_tbl =>l_req_line_id_tbl,
4871 x_return_status =>x_return_status,
4872 x_msg_data =>x_msg_data);
4873
4874
4875 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4876 RAISE FND_API.g_exc_error;
4877 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4878 RAISE FND_API.g_exc_unexpected_error;
4879 END IF;
4880
4881 EXCEPTION
4882 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4883 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
4884 P_ENCODED => 'F');
4885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4886 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
4887
4888 WHEN FND_API.G_EXC_ERROR THEN
4889 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
4890 P_ENCODED => 'F');
4891 x_return_status := FND_API.G_RET_STS_ERROR;
4892 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
4893
4894
4895 WHEN OTHERS THEN
4896 IF (G_DEBUG_UNEXP) THEN
4897 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
4898 END IF;
4899 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
4900 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
4901 P_ENCODED => 'F');
4902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4903
4904
4905
4906 END update_req_po_after_fc;
4907
4908 --------------------------------------------------------------------------------
4909 --Start of Comments
4910 --Name: update_req_po_before_fc
4911 --Function:
4912 -- Updates the cancel details(ex: cancel_flag ='I')on document being canceled
4913 -- and backing requisition lines before the funds control call
4914 -- (i.e. unencumbering the Document).
4915
4916 --Parameters:
4917 --IN:
4918 -- p_entity_level
4919 -- p_action_date
4920 -- p_entity_id
4921 -- p_document_type
4922 -- p_doc_subtype
4923 -- p_cancel_reason
4924 -- p_recreate_demand
4925 -- p_req_enc_flag
4926 -- p_user_id
4927 -- p_login_id
4928 --
4929 --
4930 --IN OUT :
4931 --OUT :
4932 -- x_msg_data
4933 -- x_return_status
4934 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
4935 -- FND_API.G_RET_STS_ERROR if cancel action fails
4936 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4937 --
4938 --End of Comments
4939 --------------------------------------------------------------------------------
4940
4941 PROCEDURE update_req_po_before_fc(
4942 p_api_version IN NUMBER,
4943 p_init_msg_list IN VARCHAR2,
4944 p_entity_level IN VARCHAR2,
4945 p_action_date IN DATE,
4946 p_entity_id IN NUMBER,
4947 p_document_type IN VARCHAR2,
4948 p_doc_subtype IN VARCHAR2,
4949 p_cancel_reason IN VARCHAR2,
4950 p_recreate_demand IN VARCHAR2,
4951 p_req_enc_flag IN VARCHAR2,
4952 p_note_to_vendor IN VARCHAR2,
4953 p_user_id IN po_lines.last_updated_by%TYPE,
4954 p_login_id IN po_lines.last_update_login%TYPE,
4955 x_is_new_line IN OUT NOCOPY VARCHAR2,
4956 x_return_status IN OUT NOCOPY VARCHAR2,
4957 x_msg_data IN OUT NOCOPY VARCHAR2)
4958
4959 IS
4960
4961 d_api_name CONSTANT VARCHAR2(30) := 'update_req_po_before_fc';
4962 d_api_version CONSTANT NUMBER := 1.0;
4963 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
4964
4965 l_progress VARCHAR2(3) := '000' ;
4966 l_fc_level VARCHAR2(10) := c_before_FC ;
4967 l_req_line_id_tbl PO_ReqChangeRequestWF_PVT.ReqLineID_tbl_type;
4968
4969
4970 BEGIN
4971 -- Start standard API initialization
4972 IF FND_API.to_boolean(p_init_msg_list) THEN
4973 FND_MSG_PUB.initialize;
4974 END IF;
4975
4976 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
4977 d_api_name, g_pkg_name) THEN
4978 RAISE FND_API.g_exc_unexpected_error;
4979 END IF;
4980
4981
4982 x_return_status := FND_API.G_RET_STS_SUCCESS;
4983 x_msg_data := NULL;
4984
4985 IF g_debug_stmt THEN
4986 PO_DEBUG.debug_begin(d_module);
4987 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
4988 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
4989 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
4990 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
4991 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
4992 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reason', p_cancel_reason);
4993 PO_DEBUG.debug_var(d_module, l_progress, 'p_recreate_demand', p_recreate_demand);
4994 PO_DEBUG.debug_var(d_module, l_progress, 'p_req_enc_flag', p_req_enc_flag);
4995 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
4996 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
4997 PO_DEBUG.debug_var(d_module, l_progress, 'p_note_to_vendor', p_note_to_vendor);
4998 PO_DEBUG.debug_var(d_module, l_progress, 'x_is_new_line', x_is_new_line);
4999 END IF;
5000
5001 l_progress := '002' ;
5002
5003 -- Update the cancel details on document before funds control call (unencumbering the document)
5004 update_document(
5005 p_api_version=> 1.0,
5006 p_init_msg_list=>FND_API.G_FALSE,
5007 p_entity_level =>p_entity_level,
5008 p_action_date =>p_action_date,
5009 p_entity_id =>p_entity_id,
5010 p_document_type =>p_document_type,
5011 p_doc_subtype =>p_doc_subtype,
5012 p_cancel_reason =>p_cancel_reason,
5013 p_recreate_demand=>p_recreate_demand,
5014 p_fc_level =>l_fc_level,
5015 p_note_to_vendor =>p_note_to_vendor,
5016 p_user_id =>p_user_id,
5017 p_login_id => p_login_id,
5018 x_return_status =>x_return_status,
5019 x_msg_data =>x_msg_data);
5020
5021 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5022 RAISE FND_API.g_exc_error;
5023 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5024 RAISE FND_API.g_exc_unexpected_error;
5025 END IF;
5026
5027 l_progress := '003' ;
5028
5029 -- Update the backing requisitions accordingly before funds control call (unencumbering the document)
5030 update_requisitions(
5031 p_api_version=> 1.0,
5032 p_init_msg_list=>FND_API.G_FALSE,
5033 p_entity_level =>p_entity_level,
5034 p_action_date =>p_action_date,
5035 p_entity_id =>p_entity_id,
5036 p_document_type =>p_document_type,
5037 p_doc_subtype =>p_doc_subtype,
5038 p_cancel_reason =>p_cancel_reason,
5039 p_recreate_demand =>p_recreate_demand,
5040 p_req_enc_flag=>p_req_enc_flag,
5041 p_fc_level =>l_fc_level,
5042 p_user_id =>p_user_id,
5043 p_login_id =>p_login_id,
5044 x_is_new_line=>x_is_new_line,
5045 x_return_status =>x_return_status,
5046 x_msg_data =>x_msg_data,
5047 x_req_line_id_tbl =>l_req_line_id_tbl);
5048
5049 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5050 RAISE FND_API.g_exc_error;
5051 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5052 RAISE FND_API.g_exc_unexpected_error;
5053 END IF;
5054
5055 EXCEPTION
5056 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5057 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5058 P_ENCODED => 'F');
5059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5060 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5061
5062 WHEN FND_API.G_EXC_ERROR THEN
5063 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5064 P_ENCODED => 'F');
5065 x_return_status := FND_API.G_RET_STS_ERROR;
5066 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5067
5068 WHEN OTHERS THEN
5069 IF (G_DEBUG_UNEXP) THEN
5070 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
5071 END IF;
5072
5073 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5074 P_ENCODED => 'F');
5075 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5076 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5077
5078
5079 END update_req_po_before_fc;
5080
5081 --------------------------------------------------------------------------------
5082 --Start of Comments
5083 --Name: approve_entity
5084 --Function:
5085 -- Approves the entity ebing canceled
5086 -- If the Document Header is Canceled, The entire docuemnt is approved,
5087 -- so calling PO_DOCUMENT_ACTION_PVT.do_approve
5088 -- If the Document Line is cancled, then just the line is approved i.e all its
5089 -- shipments are updated to be Approved.
5090 -- If the Document Shipment is cancled, then just the Shipment is approved
5091
5092
5093 --Parameters:
5094 --IN:
5095 -- p_entity_level
5096 -- p_entity_id
5097 -- p_document_type
5098 -- p_doc_subtype
5099 -- p_reason
5100 --
5101 --
5102 --IN OUT :
5103 --OUT :
5104 -- x_msg_data
5105 -- x_return_status
5106 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
5107 -- FND_API.G_RET_STS_ERROR if cancel action fails
5108 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
5109 --
5110 --End of Comments
5111 --------------------------------------------------------------------------------
5112 PROCEDURE approve_entity(
5113 p_api_version IN NUMBER,
5114 p_init_msg_list IN VARCHAR2,
5115 p_entity_level IN VARCHAR2,
5116 p_entity_id IN NUMBER,
5117 p_document_type IN VARCHAR2,
5118 p_doc_subtype IN VARCHAR2,
5119 p_reason IN VARCHAR2,
5120 x_return_status OUT NOCOPY VARCHAR2,
5121 x_msg_data OUT NOCOPY VARCHAR2)
5122 IS
5123
5124 d_api_name CONSTANT VARCHAR2(30) := 'approve_entity';
5125 d_api_version CONSTANT NUMBER := 1.0;
5126 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
5127
5128 l_progress VARCHAR2(8) := '000' ;
5129 l_user_id po_lines.last_updated_by%TYPE := -1;
5130 l_login_id po_lines.last_update_login%TYPE := -1;
5131
5132
5133
5134 BEGIN
5135 -- Start standard API initialization
5136 IF FND_API.to_boolean(p_init_msg_list) THEN
5137 FND_MSG_PUB.initialize;
5138 END IF;
5139
5140 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
5141 d_api_name, g_pkg_name) THEN
5142 RAISE FND_API.g_exc_unexpected_error;
5143 END IF;
5144
5145
5146 x_return_status := FND_API.G_RET_STS_SUCCESS;
5147 x_msg_data := NULL;
5148
5149 IF g_debug_stmt THEN
5150 PO_DEBUG.debug_begin(d_module);
5151 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
5152 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
5153 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
5154 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
5155 PO_DEBUG.debug_var(d_module, l_progress, 'p_reason', p_reason);
5156 END IF;
5157
5158 --Get User ID and Login ID
5159 l_user_id := FND_GLOBAL.USER_ID;
5160 IF (FND_GLOBAL.CONC_LOGIN_ID >= 0) THEN
5161 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
5162 ELSE
5163 l_login_id := FND_GLOBAL.LOGIN_ID;
5164 END IF;
5165
5166 IF g_debug_stmt THEN
5167 PO_DEBUG.debug_var(d_module, l_progress, 'l_user_id', l_user_id);
5168 PO_DEBUG.debug_var(d_module, l_progress, 'l_login_id', l_login_id);
5169 END IF;
5170
5171
5172
5173 -- If the docuemnt Header is Canceled then call do_approve
5174 IF p_entity_level = c_entity_level_HEADER THEN
5175
5176 l_progress := '001' ;
5177
5178 PO_DOCUMENT_ACTION_UTIL.update_doc_auth_status(
5179 p_document_id => p_entity_id,
5180 p_document_type => p_document_type,
5181 p_document_subtype => p_doc_subtype,
5182 p_new_status => PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
5183 p_user_id => l_user_id,
5184 p_login_id => l_login_id,
5185 x_return_status => x_return_status
5186 );
5187
5188
5189 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5190 RAISE FND_API.g_exc_error;
5191 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5192 RAISE FND_API.g_exc_unexpected_error;
5193 END IF;
5194
5195 ELSE
5196 -- If the document Line/Shipment is Canceled then approve the corresponding shipemnts
5197 l_progress := '002' ;
5198
5199 UPDATE po_line_locations
5200 SET approved_flag = 'Y',
5201 approved_date = SYSDATE
5202 WHERE ( (line_location_id = p_entity_id
5203 AND p_entity_level = c_entity_level_SHIPMENT)
5204 OR(po_line_id = p_entity_id
5205 AND p_entity_level = c_entity_level_LINE));
5206
5207 IF g_debug_stmt THEN
5208 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated the PO line locations table', SQL%ROWCOUNT);
5209 END IF;
5210
5211 END IF;
5212
5213 EXCEPTION
5214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5215 x_msg_data := FND_MSG_PUB.GET(
5216 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5217 P_ENCODED => 'F');
5218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5219 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5220
5221
5222 WHEN FND_API.G_EXC_ERROR THEN
5223 x_msg_data := FND_MSG_PUB.GET(
5224 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5225 P_ENCODED => 'F');
5226 x_return_status := FND_API.G_RET_STS_ERROR;
5227 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5228
5229 WHEN OTHERS THEN
5230 IF (g_debug_unexp) THEN
5231 FND_LOG.STRING(
5232 FND_LOG.LEVEL_UNEXPECTED,
5233 d_module || '.OTHERS',
5234 'EXCEPTION: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
5235 END IF;
5236 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5237 x_msg_data := FND_MSG_PUB.GET(
5238 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5239 P_ENCODED => 'F');
5240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5241
5242
5243
5244 END approve_entity;
5245
5246 --------------------------------------------------------------------------------
5247 --Start of Comments
5248 --Name: update_po_rev_archive
5249 --Function:
5250 -- 1. Set the entity being canceled to Approved Status.
5251 -- i. If the entity being canceled is in "Approved" status then
5252 -- The entity's authorization status will not be touched
5253 -- In that case,If the Cancel action is at Line/Shipment Level,
5254 -- the header level authorization status will also remain approved
5255 -- <<Bug#16445788>>
5256 -- ii. If the entity being canceled is in "Requires Reapproval"/"Rejected"
5257 -- status then
5258 -- * Set the entity to approved status.
5259 -- * For lower level entities, just approve that record alone
5260 -- 2. Archives the Document if the cancel is at Header Level.
5261 -- 3. Checks and Updates the document revision if necessary
5262
5263 --Parameters:
5264 --IN:
5265 -- p_entity_level
5266 -- p_entity_id
5267 -- p_doc_id
5268 -- p_document_type
5269 -- p_doc_subtype
5270 -- p_action_date
5271 -- p_reason
5272 -- p_user_id
5273 -- p_login_id
5274 -- p_caller
5275 --
5276 --IN OUT :
5277 --OUT :
5278 -- x_msg_data
5279 -- x_return_status
5280 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
5281 -- FND_API.G_RET_STS_ERROR if cancel action fails
5282 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
5283 --
5284 --End of Comments
5285 --------------------------------------------------------------------------------
5286 PROCEDURE update_po_rev_archive(
5287 p_api_version IN NUMBER,
5288 p_init_msg_list IN VARCHAR2,
5289 p_entity_level IN VARCHAR2,
5290 p_entity_id IN NUMBER,
5291 p_doc_id IN NUMBER,
5292 p_document_type IN VARCHAR2,
5293 p_doc_subtype IN VARCHAR2,
5294 p_action_date IN DATE,
5295 p_reason IN VARCHAR2,
5296 p_user_id IN po_lines.last_updated_by%TYPE,
5297 p_login_id IN po_lines.last_update_login%TYPE,
5298 p_caller IN VARCHAR2,
5299 x_return_status IN OUT NOCOPY VARCHAR2,
5300 x_msg_data IN OUT NOCOPY VARCHAR2)
5301
5302 IS
5303
5304 d_api_name CONSTANT VARCHAR2(30) := 'update_po_rev_archive';
5305 d_api_version CONSTANT NUMBER := 1.0;
5306 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
5307
5308 l_progress VARCHAR2(3) := '000' ;
5309 l_orig_revision_num NUMBER :=-1;
5310 l_revision_num NUMBER :=-1;
5311 l_msg_count NUMBER := 0;
5312 l_count NUMBER;
5313 l_employee_id NUMBER;
5314 l_action_date DATE;
5315 l_sequence_num NUMBER := 0;
5316 l_auth_status VARCHAR2(25);
5317 l_head_auth_status VARCHAR2(25);
5318
5319
5320 BEGIN
5321 -- Start standard API initialization
5322 IF FND_API.to_boolean(p_init_msg_list) THEN
5323 FND_MSG_PUB.initialize;
5324 END IF;
5325
5326 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
5327 d_api_name, g_pkg_name) THEN
5328 RAISE FND_API.g_exc_unexpected_error;
5329 END IF;
5330
5331
5332 x_return_status := FND_API.G_RET_STS_SUCCESS;
5333 x_msg_data := NULL;
5334
5335 IF g_debug_stmt THEN
5336 PO_DEBUG.debug_begin(d_module);
5337 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
5338 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
5339 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
5340 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
5341 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
5342 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
5343 PO_DEBUG.debug_var(d_module, l_progress, 'p_reason', p_reason);
5344 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
5345 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
5346 PO_DEBUG.debug_var(d_module, l_progress, 'p_caller', p_caller);
5347 END IF;
5348
5349
5350
5351 l_progress := '001';
5352
5353 -- Step 1: Identify the Authorization Status of teh entity being canceled
5354 -- Fetch the Docuemnt header Current Revision and Authorization status
5355 IF p_document_type = c_doc_type_RELEASE THEN
5356 SELECT revision_num,
5357 authorization_status
5358 INTO l_orig_revision_num,
5359 l_head_auth_status
5360 FROM po_releases
5361 WHERE PO_RELEASE_ID = p_doc_id;
5362
5363 ELSE
5364 SELECT revision_num,
5365 authorization_status
5366 INTO l_orig_revision_num,
5367 l_head_auth_status
5368 FROM po_headers
5369 WHERE PO_HEADER_ID = p_doc_id;
5370
5371 END IF;
5372
5373 l_progress := '002';
5374
5375 -- If the entity bing canceled is Document Shipment/Line,
5376 -- Get its Authorization Status(Approved/Not Approved)
5377 IF p_entity_level <> c_entity_level_HEADER THEN
5378
5379 SELECT COUNT(1)
5380 INTO l_count
5381 FROM po_line_locations
5382 WHERE NVL(approved_flag, 'N') = 'R'
5383 AND ( (line_location_id = p_entity_id
5384 AND p_entity_level = c_entity_level_LINE
5385 AND p_document_type <> c_doc_type_PA)
5386 OR(line_location_id = p_entity_id
5387 AND p_entity_level = c_entity_level_SHIPMENT)) ;
5388
5389 IF l_count> 0 THEN
5390 l_auth_status := PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL;
5391 ELSE
5392 l_auth_status := PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED;
5393 END IF;
5394
5395 ELSE
5396 l_auth_status := l_head_auth_status;
5397 END IF;
5398
5399
5400 IF g_debug_stmt THEN
5401 PO_DEBUG.debug_var(d_module, l_progress, 'l_orig_revision_num', l_orig_revision_num);
5402 PO_DEBUG.debug_var(d_module, l_progress, 'l_head_auth_status', l_head_auth_status);
5403 PO_DEBUG.debug_var(d_module, l_progress, 'l_auth_status', l_auth_status);
5404 END IF;
5405
5406 l_progress := '003';
5407
5408 -- Step 1: Checks and Updates the document revision if necessary
5409
5410 l_revision_num := l_orig_revision_num;
5411
5412 -- Find the latest revision number of the entity
5413
5414 PO_DOCUMENT_REVISION_GRP.Check_New_Revision (
5415 p_api_version => 1.0,
5416 p_doc_type => p_document_type,
5417 p_doc_subtype => p_doc_subtype,
5418 p_doc_id => p_doc_id,
5419 p_table_name => 'ALL',
5420 x_return_status => x_return_status,
5421 x_doc_revision_num => l_revision_num,
5422 x_message => x_msg_data);
5423
5424 IF g_debug_stmt THEN
5425 PO_DEBUG.debug_var(d_module, l_progress, 'l_revision_num', l_revision_num);
5426 PO_DEBUG.debug_var(d_module, l_progress, 'l_orig_revision_num', l_orig_revision_num);
5427 END IF;
5428
5429
5430 l_progress := '004';
5431
5432
5433 -- Step 2: Set the entity being canceled to Approved Status
5434 --If the entity being canceled is in "Requires Reapproval"/"Rejected" status then
5435 -- Set the entity to approved status.
5436 IF l_auth_status <> PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED THEN
5437 approve_entity(
5438 p_api_version=> 1.0,
5439 p_init_msg_list=>FND_API.G_FALSE,
5440 p_entity_level => p_entity_level,
5441 p_entity_id => p_entity_id,
5442 p_document_type => p_document_type,
5443 p_doc_subtype => p_doc_subtype,
5444 p_reason => p_reason,
5445 x_return_status => x_return_status,
5446 x_msg_data => x_msg_data);
5447
5448 END IF;
5449
5450 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5451 RAISE FND_API.g_exc_error;
5452 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5453 RAISE FND_API.g_exc_unexpected_error;
5454 END IF;
5455
5456
5457
5458 l_progress := '005';
5459 -- <<Bug#16445788>>
5460 IF p_document_type = c_doc_type_RELEASE THEN
5461
5462 UPDATE PO_RELEASES_ALL
5463 SET REVISION_NUM = l_revision_num,
5464 REVISED_DATE = DECODE (
5465 revision_num,
5466 l_revision_num,
5467 REVISED_DATE,
5468 SYSDATE),
5469 LAST_UPDATE_DATE = SYSDATE ,
5470 LAST_UPDATED_BY = p_user_id ,
5471 LAST_UPDATE_LOGIN = p_login_id
5472 WHERE PO_RELEASE_ID = p_doc_id;
5473
5474 IF g_debug_stmt THEN
5475 PO_DEBUG.debug_var(d_module, l_progress, 'Rows updated in PO Releases', SQL%ROWCOUNT);
5476 END IF;
5477
5478 ELSE
5479 UPDATE po_headers
5480 SET REVISION_NUM = l_revision_num,
5481 REVISED_DATE = DECODE(
5482 revision_num,
5483 l_revision_num,
5484 REVISED_DATE,
5485 SYSDATE),
5486 LAST_UPDATE_DATE = SYSDATE ,
5487 LAST_UPDATED_BY = p_user_id ,
5488 LAST_UPDATE_LOGIN = p_login_id
5489 WHERE PO_HEADER_ID = p_doc_id;
5490
5491 IF g_debug_stmt THEN
5492 PO_DEBUG.debug_var(d_module, l_progress, 'Rows updated in PO Headers', SQL%ROWCOUNT);
5493 END IF;
5494
5495 END IF; --if l_document_type = c_doc_type_RELEASE
5496
5497 -- <<Bug#16445788: Archives the Document if the cancel is at Header Level>>
5498 IF p_entity_level = c_entity_level_HEADER THEN
5499
5500 l_progress := '006';
5501
5502 -- archive the PO revision and update the action history table
5503 -- if the Document Header is Canceled
5504
5505 PO_DOCUMENT_ARCHIVE_GRP.Archive_PO(
5506 p_api_version => 1.0,
5507 p_document_id => p_doc_id,
5508 p_document_type => p_document_type,
5509 p_document_subtype => p_doc_subtype,
5510 p_process => 'APPROVE',
5511 x_return_status => x_return_status,
5512 x_msg_count => l_msg_count,
5513 x_msg_data => x_msg_data);
5514
5515 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5516 RAISE FND_API.g_exc_error;
5517 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5518 RAISE FND_API.g_exc_unexpected_error;
5519 END IF;
5520
5521 l_progress := '007';
5522
5523
5524 SELECT MAX(sequence_num)
5525 INTO l_sequence_num
5526 FROM PO_ACTION_HISTORY
5527 WHERE object_type_code= p_document_type --'PO'
5528 AND object_sub_type_code = p_doc_subtype --'STANDARD'
5529 AND object_id = p_doc_id;
5530
5531 IF g_debug_stmt THEN
5532 PO_DEBUG.debug_var(d_module, l_progress, 'l_sequence_num', l_sequence_num);
5533 END IF;
5534
5535 BEGIN
5536
5537 SELECT HR.employee_id
5538 INTO l_employee_id
5539 FROM FND_USER FND,
5540 HR_EMPLOYEES_CURRENT_V HR
5541 WHERE FND.user_id = p_user_id
5542 AND FND.employee_id = HR.employee_id ;
5543
5544 EXCEPTION
5545 WHEN No_Data_Found THEN
5546 l_employee_id := NULL;
5547 WHEN OTHERS THEN
5548 RAISE;
5549 END;
5550
5551 IF g_debug_stmt THEN
5552 PO_DEBUG.debug_var(d_module, l_progress, 'l_employee_id', l_employee_id);
5553 END IF;
5554
5555 l_progress := '008';
5556
5557 po_forward_sv1.insert_action_history(
5558 x_object_id=> p_doc_id,
5559 x_object_type_code =>p_document_type,
5560 x_object_sub_type_code =>p_doc_subtype,
5561 x_sequence_num =>l_sequence_num+1,
5562 x_action_code =>'CANCEL',
5563 x_action_date =>p_action_date,
5564 x_employee_id =>l_employee_id,
5565 x_approval_path_id =>NULL,
5566 x_note =>p_reason,
5567 x_object_revision_num =>l_revision_num,
5568 x_offline_code =>NULL,
5569 x_request_id =>NULL,
5570 x_program_application_id =>NULL,
5571 x_program_id =>NULL,
5572 x_program_date =>SYSDATE,
5573 x_user_id =>p_user_id,
5574 x_login_id =>p_login_id) ;
5575
5576 END IF; -- IF p_entity_level = c_entity_level_HEADER
5577
5578 EXCEPTION
5579 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5580 x_msg_data := FND_MSG_PUB.GET(
5581 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5582 P_ENCODED => 'F');
5583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5584 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5585
5586 WHEN FND_API.G_EXC_ERROR THEN
5587 x_msg_data := FND_MSG_PUB.GET(
5588 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5589 P_ENCODED => 'F');
5590 x_return_status := FND_API.G_RET_STS_ERROR;
5591 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5592 WHEN OTHERS THEN
5593 IF (g_debug_unexp) THEN
5594 FND_LOG.STRING(
5595 FND_LOG.LEVEL_UNEXPECTED,
5596 d_module || '.OTHERS',
5597 'EXCEPTION: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
5598 END IF;
5599 x_msg_data := FND_MSG_PUB.GET(
5600 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5601 P_ENCODED => 'F');
5602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5603 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5604
5605 END update_po_rev_archive;
5606
5607 --------------------------------------------------------------------------------
5608 --Start of Comments
5609 --Name: update_closed_code
5610 --Function:
5611 -- Update the Status of Shipments, based on the Receiving and Invoicing
5612 -- Closure Point Quantities, and Rollup if necessary
5613 -- For example, if all of a shipment has been received, then the shipment
5614 -- is closed for receiving
5615 -- Closure Points and Tolerance Levels are set in po_line_locations (for
5616 -- tolerances) and in po_system_parameters (for closed codes) respectively.
5617 -- This method wraps PO_DOCUMENT_ACTION_PVT.auto_close_update_state. .
5618 --
5619 --
5620 --Parameters:
5621 --IN:
5622 -- p_entity_level
5623 -- p_entity_id
5624 -- p_doc_id
5625 -- p_document_type
5626 -- p_doc_subtype
5627 -- p_user_id
5628 -- p_login_id
5629 --
5630 --IN OUT :
5631 --OUT :
5632 -- x_msg_data
5633 -- x_return_status
5634 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
5635 -- FND_API.G_RET_STS_ERROR if cancel action fails
5636 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
5637 --
5638 --End of Comments
5639 --------------------------------------------------------------------------------
5640 PROCEDURE update_closed_code(
5641 p_api_version IN NUMBER,
5642 p_init_msg_list IN VARCHAR2,
5643 p_entity_level IN VARCHAR2,
5644 p_entity_id IN NUMBER,
5645 p_doc_id IN NUMBER,
5646 p_document_type IN VARCHAR2,
5647 p_doc_subtype IN VARCHAR2,
5648 p_user_id IN po_lines.last_updated_by%TYPE,
5649 p_login_id IN po_lines.last_update_login%TYPE,
5650 x_return_status OUT NOCOPY VARCHAR2,
5651 x_msg_data OUT NOCOPY VARCHAR2)
5652
5653 IS
5654
5655 d_api_name CONSTANT VARCHAR2(30) := 'update_closed_code';
5656 d_api_version CONSTANT NUMBER := 1.0;
5657 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
5658 l_progress VARCHAR2(8) := '000' ;
5659 l_line_id NUMBER;
5660 l_ship_id NUMBER;
5661 l_return_code VARCHAR2(30);
5662
5663
5664 BEGIN
5665 -- Start standard API initialization
5666 IF FND_API.to_boolean(p_init_msg_list) THEN
5667 FND_MSG_PUB.initialize;
5668 END IF;
5669
5670 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
5671 d_api_name, g_pkg_name) THEN
5672 RAISE FND_API.g_exc_unexpected_error;
5673 END IF;
5674
5675
5676 x_return_status := FND_API.G_RET_STS_SUCCESS;
5677 x_msg_data := NULL;
5678
5679 IF g_debug_stmt THEN
5680 PO_DEBUG.debug_begin(d_module);
5681 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_level', p_entity_level);
5682 PO_DEBUG.debug_var(d_module, l_progress, 'p_entity_id', p_entity_id);
5683 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
5684 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
5685 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
5686 END IF;
5687
5688 l_progress := '001' ;
5689
5690 IF p_entity_level = c_entity_level_SHIPMENT THEN
5691 l_ship_id := p_entity_id;
5692 ELSIF p_entity_level = c_entity_level_LINE THEN
5693 l_line_id := p_entity_id;
5694 END IF;
5695
5696
5697 IF g_debug_stmt THEN
5698 PO_DEBUG.debug_var(d_module, l_progress, 'l_line_id', l_line_id);
5699 PO_DEBUG.debug_var(d_module, l_progress, 'l_ship_id', l_ship_id);
5700 END IF;
5701
5702 l_progress := '002' ;
5703
5704 PO_DOCUMENT_ACTION_PVT.auto_update_close_state(
5705 p_document_id => p_doc_id,
5706 p_document_type => p_document_type,
5707 p_document_subtype => p_doc_subtype,
5708 p_line_id => l_line_id,
5709 p_shipment_id => l_ship_id,
5710 p_calling_mode => 'PO',
5711 p_called_from_conc => FALSE,
5712 x_return_status => x_return_status,
5713 x_return_code => l_return_code,
5714 x_exception_msg => x_msg_data );
5715
5716 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5717 RAISE FND_API.g_exc_error;
5718 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5719 RAISE FND_API.g_exc_unexpected_error;
5720 END IF;
5721
5722
5723
5724 EXCEPTION
5725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5726 x_msg_data := FND_MSG_PUB.GET(
5727 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5728 P_ENCODED => 'F');
5729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5730
5731 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5732
5733 WHEN FND_API.G_EXC_ERROR THEN
5734 x_msg_data := FND_MSG_PUB.GET(
5735 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5736 P_ENCODED => 'F');
5737 x_return_status := FND_API.G_RET_STS_ERROR;
5738 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5739
5740
5741 WHEN OTHERS THEN
5742 IF (g_debug_unexp) THEN
5743 FND_LOG.STRING(
5744 FND_LOG.LEVEL_UNEXPECTED,
5745 d_module || '.OTHERS',
5746 'EXCEPTION: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
5747 END IF;
5748 x_msg_data := FND_MSG_PUB.GET(
5749 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
5750 P_ENCODED => 'F');
5751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5752 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5753 END update_closed_code;
5754
5755 --------------------------------------------------------------------------------
5756 --Start of Comments
5757 --Name: process_cancel_action
5758
5759 --Function:
5760 -- Performs the cancellation of document
5761 -- Update the cancel flag and other relevant coulmns on the document
5762 -- and the backing requisition
5763
5764 --Parameters:
5765 --IN:
5766 -- p_da_call_rec
5767 -- p_key
5768 -- p_user_id
5769 -- p_login_id
5770
5771 --IN OUT :
5772 --OUT :
5773 -- x_msg_data
5774 -- x_return_status -
5775 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
5776 -- FND_API.G_RET_STS_ERROR if cancel action fails
5777 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
5778 --
5779 --End of Comments
5780 --------------------------------------------------------------------------------
5781 PROCEDURE process_cancel_action(
5782 p_api_version IN NUMBER,
5783 p_init_msg_list IN VARCHAR2,
5784 p_da_call_rec IN OUT NOCOPY po_document_action_pvt.DOC_ACTION_CALL_TBL_REC_TYPE,
5785 p_key IN po_session_gt.key%TYPE,
5786 p_user_id IN po_lines.last_updated_by%TYPE,
5787 p_login_id IN po_lines.last_update_login%TYPE,
5788 p_po_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE,
5789 p_req_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.req_encumbrance_flag%TYPE,
5790 x_return_status OUT NOCOPY VARCHAR2,
5791 x_msg_data OUT NOCOPY VARCHAR2)
5792
5793 IS
5794
5795 d_api_name CONSTANT VARCHAR2(30) := 'process_cancel_action';
5796 d_api_version CONSTANT NUMBER := 1.0;
5797 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
5798 l_progress VARCHAR2(8) := '000' ;
5799 l_cancel_reason VARCHAR2(240); --Bug 15836292
5800 l_entity_rec_tbl po_document_action_pvt.entity_dtl_rec_type_tbl;
5801 l_doc_id NUMBER;
5802 l_enc_req_flag VARCHAR2(1);
5803 l_is_new_req_line VARCHAR2(1);
5804 l_po_return_code VARCHAR2(25);
5805 l_online_report_id NUMBER;
5806
5807 BEGIN
5808 -- Start standard API initialization
5809 IF FND_API.to_boolean(p_init_msg_list) THEN
5810 FND_MSG_PUB.initialize;
5811 END IF;
5812
5813 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
5814 d_api_name, g_pkg_name) THEN
5815 RAISE FND_API.g_exc_unexpected_error;
5816 END IF;
5817
5818
5819 x_return_status := FND_API.G_RET_STS_SUCCESS;
5820 x_msg_data := NULL;
5821
5822 IF g_debug_stmt THEN
5823 PO_DEBUG.debug_begin(d_module);
5824 PO_DEBUG.debug_var(d_module, l_progress, 'p_key', p_key);
5825 PO_DEBUG.debug_var(d_module, l_progress, 'p_user_id', p_user_id);
5826 PO_DEBUG.debug_var(d_module, l_progress, 'p_login_id', p_login_id);
5827 PO_DEBUG.debug_var(d_module, l_progress, 'p_po_enc_flag', p_po_enc_flag);
5828 PO_DEBUG.debug_var(d_module, l_progress, 'p_req_enc_flag', p_req_enc_flag);
5829 END IF;
5830
5831 l_progress := '002' ;
5832
5833 l_entity_rec_tbl := p_da_call_rec.entity_dtl_record_tbl;
5834 l_cancel_reason := p_da_call_rec.reason;
5835 l_enc_req_flag :='N';
5836
5837 l_doc_id := -1;
5838
5839
5840 IF g_debug_stmt THEN
5841 PO_DEBUG.debug_var(d_module, l_progress, 'l_cancel_reason', l_cancel_reason);
5842 END IF;
5843
5844 -- for each entity id in the entity record table
5845 FOR i IN 1..l_entity_rec_tbl.Count
5846 LOOP
5847 SAVEPOINT process_cancel_action_SP;
5848
5849 BEGIN
5850
5851 IF l_entity_rec_tbl(i).process_entity_flag = 'Y' THEN
5852
5853 IF g_debug_stmt THEN
5854 PO_DEBUG.debug_var(d_module, l_progress, 'i', i);
5855 PO_DEBUG.debug_var(d_module, l_progress, 'entity_level', l_entity_rec_tbl(i).entity_level);
5856 PO_DEBUG.debug_var(d_module, l_progress, 'entity_id', l_entity_rec_tbl(i).entity_id);
5857 PO_DEBUG.debug_var(d_module, l_progress, 'entity_action_date', l_entity_rec_tbl(i).entity_action_date);
5858 PO_DEBUG.debug_var(d_module, l_progress, 'document_type', l_entity_rec_tbl(i).document_type);
5859 PO_DEBUG.debug_var(d_module, l_progress, 'document_subtype', l_entity_rec_tbl(i).document_subtype);
5860 PO_DEBUG.debug_var(d_module, l_progress, 'recreate_demand_flag', l_entity_rec_tbl(i).recreate_demand_flag);
5861 END IF;
5862
5863 l_progress := '003' ;
5864
5865 l_doc_id := l_entity_rec_tbl(i).doc_id;
5866
5867 --Update the Cancel Flag('I') and other relevant columns of the
5868 -- document and backing req
5869 --If recreate_demand is ON, create the new distributions and lines
5870 update_req_po_before_fc(
5871 p_api_version=> 1.0,
5872 p_init_msg_list=>FND_API.G_FALSE,
5873 p_entity_level =>l_entity_rec_tbl(i).entity_level,
5874 p_action_date =>l_entity_rec_tbl(i).entity_action_date,
5875 p_entity_id =>l_entity_rec_tbl(i).entity_id,
5876 p_document_type =>l_entity_rec_tbl(i).document_type,
5877 p_doc_subtype =>l_entity_rec_tbl(i).document_subtype,
5878 p_cancel_reason =>l_cancel_reason,
5879 p_recreate_demand => l_entity_rec_tbl(i).recreate_demand_flag,
5880 p_note_to_vendor =>p_da_call_rec.note_to_vendor,
5881 p_req_enc_flag =>p_req_enc_flag,
5882 x_is_new_line=>l_is_new_req_line,
5883 p_user_id =>p_user_id,
5884 p_login_id => p_login_id,
5885 x_return_status =>x_return_status,
5886 x_msg_data =>x_msg_data);
5887
5888 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5889 RAISE FND_API.g_exc_error;
5890 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5891 RAISE FND_API.g_exc_unexpected_error;
5892 END IF;
5893
5894 IF g_debug_stmt THEN
5895 PO_DEBUG.debug_var(d_module, l_progress, 'l_is_new_req_line', l_is_new_req_line);
5896 END IF;
5897
5898
5899 l_progress := '004' ;
5900
5901 --Call to encumbrance code is controlled by the "l_enc_req_flag" value
5902 -- If the Value is 'Y' then call PO_DOCUMENT_FUNDS_PVT.do_cancel,
5903 -- otherwise do not call
5904
5905 --The value of "l_enc_req_flag" is set to 'Y' only when
5906 -- 1) Document is a blanket purchase agreement,
5907 -- and both PO and REQ enc are turned on,
5908 -- and encumbrance_required_flag is 'Y', OR
5909 -- 2) Document is not a Purchase Agreement, and PO enc is turned on
5910 IF (l_entity_rec_tbl(i).document_type = c_doc_type_PA
5911 AND l_entity_rec_tbl(i).document_subtype=c_doc_subtype_BLANKET
5912 AND p_po_enc_flag='Y'
5913 AND p_req_enc_flag='Y'
5914 AND l_entity_rec_tbl(i).entity_level=c_entity_level_HEADER)
5915 THEN
5916 BEGIN
5917
5918 SELECT Nvl(encumbrance_required_flag,'N')
5919 INTO l_enc_req_flag
5920 FROM po_headers
5921 WHERE po_header_id=l_entity_rec_tbl(i).entity_id;
5922
5923 EXCEPTION
5924 WHEN No_Data_Found THEN
5925 l_enc_req_flag:='N';
5926
5927 WHEN OTHERS THEN
5928 RAISE FND_API.g_exc_unexpected_error;
5929 END;
5930 --Document is not a Purchase Agreement, and PO enc is turned on
5931 -- Bug 15983778: Instead of po encumbrance flag req enc flag was checked.
5932 ELSIF(l_entity_rec_tbl(i).document_type <> c_doc_type_PA
5933 AND p_po_enc_flag='Y') THEN
5934 l_enc_req_flag :='Y' ;
5935 END IF;
5936
5937 IF g_debug_stmt THEN
5938 PO_DEBUG.debug_var(d_module, l_progress, 'l_enc_req_flag', l_enc_req_flag);
5939 END IF;
5940
5941
5942 l_progress := '005' ;
5943
5944 IF l_enc_req_flag ='Y' THEN
5945
5946 -- bug 14766811
5947 -- Not able to cancel an encumbered PO at shipment level as entity level passed to
5948 -- encumbrance API is LINE_LOCATION for shipment whereas PO_CORE_S.GET_DOCUMENT_IDS
5949 -- expect the value to be SHIPMENT. Thus changing the value before calling encumbrance API.
5950 IF(l_entity_rec_tbl(i).entity_level = PO_Document_Cancel_PVT.c_entity_level_SHIPMENT) THEN
5951 l_entity_rec_tbl(i).entity_level := PO_CORE_S.g_doc_level_SHIPMENT;
5952 END IF;
5953
5954 -- Unencumber the entity
5955 PO_DOCUMENT_FUNDS_PVT.do_cancel(
5956 x_return_status => x_return_status,
5957 p_doc_type => l_entity_rec_tbl(i).document_type,
5958 p_doc_subtype => l_entity_rec_tbl(i).document_subtype,
5959 p_doc_level => l_entity_rec_tbl(i).entity_level,
5960 p_doc_level_id => l_entity_rec_tbl(i).entity_id,
5961 p_use_enc_gt_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO,
5962 p_override_funds => PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE,
5963 p_use_gl_date => p_da_call_rec.use_gl_date,
5964 p_override_date => l_entity_rec_tbl(i).entity_action_date,
5965 x_po_return_code => l_po_return_code,
5966 x_online_report_id => l_online_report_id);
5967
5968
5969 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5970 RAISE FND_API.g_exc_error;
5971 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5972 RAISE FND_API.g_exc_unexpected_error;
5973 END IF;
5974
5975 -- bug 14766811
5976 -- Resetting the value of entity level back to original as this is used by other flows.
5977 IF(l_entity_rec_tbl(i).entity_level = PO_CORE_S.g_doc_level_SHIPMENT) THEN
5978 l_entity_rec_tbl(i).entity_level := PO_Document_Cancel_PVT.c_entity_level_SHIPMENT;
5979 END IF;
5980
5981 END IF;
5982 l_progress := '006' ;
5983
5984 -- After Funds Control routine was successful in unencumbering the PO,
5985 -- update po entity cancel_flags at all levels to 'Y',
5986 -- and if new req lines were created, to free them up by setting their
5987 -- line_location_id column to Null.
5988
5989 update_req_po_after_fc(
5990 p_api_version=> 1.0,
5991 p_init_msg_list=>FND_API.G_FALSE,
5992 p_entity_level =>l_entity_rec_tbl(i).entity_level,
5993 p_action_date =>l_entity_rec_tbl(i).entity_action_date,
5994 p_entity_id =>l_entity_rec_tbl(i).entity_id,
5995 p_doc_id =>l_entity_rec_tbl(i).doc_id,
5996 p_document_type =>l_entity_rec_tbl(i).document_type,
5997 p_doc_subtype =>l_entity_rec_tbl(i).document_subtype,
5998 p_cancel_reason =>l_cancel_reason,
5999 p_recreate_flag =>l_entity_rec_tbl(i).recreate_demand_flag,
6000 p_req_enc_flag =>p_req_enc_flag,
6001 p_note_to_vendor =>p_da_call_rec.note_to_vendor,
6002 p_source =>p_da_call_rec.caller,
6003 x_is_new_line=>l_is_new_req_line,
6004 p_user_id =>p_user_id,
6005 p_login_id => p_login_id,
6006 x_return_status =>x_return_status,
6007 x_msg_data =>x_msg_data);
6008
6009
6010 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6011 RAISE FND_API.g_exc_error;
6012 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6013 RAISE FND_API.g_exc_unexpected_error;
6014 END IF;
6015
6016 l_progress := '007' ;
6017
6018 -- Calling update closed code only once for a document header id
6019 -- and not individual entity id
6020 -- So Checking if this is the last entity or the next entity belongs
6021 -- to a different doc_id.If So,then making a call to update closed code.
6022 -- This is based on the assumption/fact that the records in
6023 -- l_entity_rec_tbl are sorted based on doc_id.This soring is done
6024 -- while inserting the records back into entity record table from
6025 -- po_Seesion_gt in the procedure
6026 -- po_control_action_validations.mark_errored_record(..)
6027 IF i=l_entity_rec_tbl.Count OR l_doc_id <> l_entity_rec_tbl(i+1).doc_id THEN
6028
6029 -- After successful update of the relavant columns of the document,
6030 -- update the closed code on the document
6031 update_closed_code(
6032 p_api_version=> 1.0,
6033 p_init_msg_list=>FND_API.G_FALSE,
6034 p_entity_level =>c_entity_level_HEADER,
6035 p_entity_id =>l_entity_rec_tbl(i).doc_id,
6036 p_doc_id => l_entity_rec_tbl(i).doc_id,
6037 p_document_type =>l_entity_rec_tbl(i).document_type,
6038 p_doc_subtype =>l_entity_rec_tbl(i).document_subtype,
6039 p_user_id =>p_user_id,
6040 p_login_id => p_login_id,
6041 x_return_status =>x_return_status,
6042 x_msg_data =>x_msg_data);
6043
6044 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6045 RAISE FND_API.g_exc_error;
6046 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6047 RAISE FND_API.g_exc_unexpected_error;
6048 END IF;
6049
6050 END IF;
6051
6052 l_progress := '008';
6053
6054
6055 -- Routine to update the PO revision, and archive the PO
6056 -- Update the po action history table
6057 update_po_rev_archive(
6058 p_api_version=> 1.0,
6059 p_init_msg_list=>FND_API.G_FALSE,
6060 p_entity_level =>l_entity_rec_tbl(i).entity_level,
6061 p_entity_id =>l_entity_rec_tbl(i).entity_id,
6062 p_doc_id => l_entity_rec_tbl(i).doc_id,
6063 p_document_type =>l_entity_rec_tbl(i).document_type,
6064 p_doc_subtype =>l_entity_rec_tbl(i).document_subtype,
6065 p_reason =>l_cancel_reason,
6066 p_action_date =>l_entity_rec_tbl(i).entity_action_date,
6067 p_user_id =>p_user_id,
6068 p_login_id =>p_login_id,
6069 p_caller =>p_da_call_rec.caller,
6070 x_return_status => x_return_status,
6071 x_msg_data =>x_msg_data);
6072
6073
6074
6075 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6076 RAISE FND_API.g_exc_error;
6077 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6078 RAISE FND_API.g_exc_unexpected_error;
6079 END IF;
6080
6081
6082 END IF; --if process_entity_flag='Y' \
6083
6084 EXCEPTION
6085 WHEN FND_API.g_exc_error THEN
6086 ROLLBACK TO process_cancel_action_SP;
6087 x_return_status := FND_API.g_ret_sts_error;
6088 IF (g_debug_unexp) THEN
6089 FND_LOG.STRING(
6090 FND_LOG.LEVEL_UNEXPECTED,
6091 d_module || '.Error',
6092 'Error: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
6093 END IF;
6094 WHEN FND_API.g_exc_unexpected_error THEN
6095 ROLLBACK TO process_cancel_action_SP;
6096 x_return_status := FND_API.g_ret_sts_unexp_error;
6097 IF (g_debug_unexp) THEN
6098 FND_LOG.STRING(
6099 FND_LOG.LEVEL_UNEXPECTED,
6100 d_module || '.UnExpected',
6101 'EXCEPTION: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
6102 END IF;
6103
6104 WHEN OTHERS THEN
6105 ROLLBACK TO process_cancel_action_SP;
6106 x_return_status := FND_API.g_ret_sts_unexp_error;
6107 IF (g_debug_unexp) THEN
6108 FND_LOG.STRING(
6109 FND_LOG.LEVEL_UNEXPECTED,
6110 d_module || '.OTHERS',
6111 'EXCEPTION: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
6112 END IF;
6113 END;
6114
6115 l_progress := '002' ;
6116 END LOOP;
6117
6118
6119 EXCEPTION
6120 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6121 x_msg_data := FND_MSG_PUB.GET(
6122 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6123 P_ENCODED => 'F');
6124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6125 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
6126 WHEN FND_API.G_EXC_ERROR THEN
6127 x_msg_data := FND_MSG_PUB.GET(
6128 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6129 P_ENCODED => 'F');
6130 x_return_status := FND_API.G_RET_STS_ERROR;
6131 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
6132 WHEN OTHERS THEN
6133 IF (g_debug_unexp) THEN
6134 FND_LOG.STRING(
6135 FND_LOG.LEVEL_UNEXPECTED,
6136 d_module || '.OTHERS',
6137 'EXCEPTION: LOCATION IS '|| l_progress || ' SQL CODE IS '||SQLCODE);
6138 END IF;
6139 x_msg_data := FND_MSG_PUB.GET(
6140 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6141 P_ENCODED => 'F');
6142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6143 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
6144
6145 END process_cancel_action;
6146
6147 --------------------------------------------------------------------------------
6148 --Start of Comments
6149 --Name: insrtEntityRecInSessiongt
6150
6151 --Function:
6152 -- Inserts the entity record in po session gt
6153 --
6154 -- Column Mapping is as :
6155 -- num1 => entity_id
6156 -- char1 => document_type
6157 -- char2 => document_subtype
6158 -- char3 => entity_level
6159 -- char4 => doc_id
6160 -- char5 => process_entity_flag
6161
6162 --Parameters:
6163 --IN:
6164 -- p_entity_rec_tbl
6165
6166 --IN OUT :
6167 --OUT :
6168 -- x_key
6169 -- x_msg_data
6170 -- x_return_status
6171 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
6172 -- FND_API.G_RET_STS_ERROR if cancel action fails
6173 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
6174 --
6175 --End of Comments
6176 --------------------------------------------------------------------------------
6177 PROCEDURE insrtEntityRecInSessiongt(
6178 p_entity_rec_tbl IN po_document_action_pvt.entity_dtl_rec_type_tbl,
6179 p_api_version IN NUMBER,
6180 p_init_msg_list IN VARCHAR2,
6181 x_key OUT NOCOPY po_session_gt.key%TYPE,
6182 x_return_status OUT NOCOPY VARCHAR2,
6183 x_msg_data OUT NOCOPY VARCHAR2)
6184
6185 IS
6186
6187 d_api_name CONSTANT VARCHAR2(30) := 'insrtEntityRecInSessiongt';
6188 d_api_version CONSTANT NUMBER := 1.0;
6189 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
6190 l_progress VARCHAR2(3) := '000' ;
6191
6192 BEGIN
6193 -- Start standard API initialization
6194 IF FND_API.to_boolean(p_init_msg_list) THEN
6195 FND_MSG_PUB.initialize;
6196 END IF;
6197
6198 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
6199 d_api_name, g_pkg_name) THEN
6200 RAISE FND_API.g_exc_unexpected_error;
6201 END IF;
6202
6203 x_return_status := FND_API.G_RET_STS_SUCCESS;
6204 x_msg_data := NULL;
6205
6206 IF g_debug_stmt THEN
6207 PO_DEBUG.debug_begin(d_module);
6208 END IF;
6209
6210 l_progress := '001';
6211
6212 x_key := po_core_s.get_session_gt_nextval;
6213
6214 IF g_debug_stmt THEN
6215 PO_DEBUG.debug_var(d_module, l_progress, 'x_key', x_key);
6216 END IF;
6217
6218 -- for each entity
6219 FOR i IN 1.. p_entity_rec_tbl.Count
6220 LOOP
6221
6222
6223 INSERT INTO po_session_gt(
6224 KEY ,
6225 num1 ,
6226 char1,
6227 char2,
6228 char3,
6229 char4,
6230 char5)
6231 VALUES(
6232 x_key ,
6233 P_entity_rec_tbl(i).entity_id ,
6234 P_entity_rec_tbl(i).document_type ,
6235 P_entity_rec_tbl(i).document_subtype,
6236 P_entity_rec_tbl(i).entity_level ,
6237 P_entity_rec_tbl(i).doc_id ,
6238 'Y');
6239
6240
6241 END LOOP;
6242 EXCEPTION
6243 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6244 x_msg_data := FND_MSG_PUB.GET(
6245 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6246 P_ENCODED => 'F');
6247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6248 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
6249
6250 WHEN FND_API.G_EXC_ERROR THEN
6251 x_msg_data := FND_MSG_PUB.GET(
6252 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6253 P_ENCODED => 'F');
6254 x_return_status := FND_API.G_RET_STS_ERROR;
6255 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
6256
6257 WHEN OTHERS THEN
6258 IF (G_DEBUG_UNEXP) THEN
6259 FND_LOG.STRING(
6260 FND_LOG.LEVEL_UNEXPECTED,
6261 d_module || '.OTHERS_EXCEPTION',
6262 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
6263 END IF;
6264 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
6265 x_msg_data := FND_MSG_PUB.GET(
6266 P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6267 P_ENCODED => 'F');
6268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6269
6270 END insrtEntityRecInSessiongt;
6271
6272
6273 --------------------------------------------------------------------------------
6274 --Start of Comments
6275 --Name: cancel_document
6276
6277 --Function:
6278
6279 -- Modifies: All cancel columns and who columns for this document at the entity
6280 -- level of cancellation.
6281 -- Effects: Cancels the document at the header, line, or shipment level
6282 -- depending upon the document ID parameters after performing validations.
6283 -- Validations include state checks and cancel submission checks. If
6284 -- p_cbc_enabled is 'Y', then the CBC accounting date is updated to be
6285 -- p_action_date. If p_cancel_reqs_flag is 'Y', then backing requisitions
6286 -- will also be cancelled if allowable. Otherwise, they will be recreated.
6287 -- Encumbrance is recalculated for cancelled entities if enabled. If the
6288 -- cancel action is successful, the document's cancel and who columns will be
6289 -- updated at the specified entity level. Otherwise, the document will remain
6290 -- unchanged. All changes will be committed upon success if p_commit is
6291 -- FND_API.G_TRUE.
6292
6293
6294 --Parameters:
6295 --IN:
6296 -- p_da_call_rec
6297 -- p_api_version
6298 -- p_init_msg_list
6299
6300 --IN OUT :
6301 --OUT :
6302 -- x_msg_data
6303 -- x_return_status
6304 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
6305 -- FND_API.G_RET_STS_ERROR if cancel action fails
6306 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
6307 --
6308 --End of Comments
6309 --------------------------------------------------------------------------------
6310 PROCEDURE cancel_document(
6311 p_da_call_rec IN OUT NOCOPY po_document_action_pvt.DOC_ACTION_CALL_TBL_REC_TYPE,
6312 p_api_version IN NUMBER,
6313 p_init_msg_list IN VARCHAR2,
6314 x_return_status OUT NOCOPY VARCHAR2,
6315 x_msg_data OUT NOCOPY VARCHAR2,
6316 x_return_code OUT NOCOPY VARCHAR2
6317 )
6318 IS
6319
6320 d_api_name CONSTANT VARCHAR2(30) := 'cancel_document';
6321 d_api_version CONSTANT NUMBER := 1.0;
6322 d_module CONSTANT VARCHAR2(100) := g_module_prefix|| d_api_name;
6323
6324 l_progress VARCHAR2(3) := '000' ;
6325 l_key po_session_gt.key%TYPE;
6326 l_user_id po_lines.last_updated_by%TYPE := -1;
6327 l_login_id po_lines.last_update_login%TYPE := -1;
6328 l_po_encumbrance_flag FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE;
6329 l_req_encumbrance_flag FINANCIALS_SYSTEM_PARAMETERS.req_encumbrance_flag%TYPE;
6330
6331 BEGIN
6332
6333 -- Start standard API initialization
6334 IF FND_API.to_boolean(p_init_msg_list) THEN
6335 FND_MSG_PUB.initialize;
6336 END IF;
6337
6338 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
6339 d_api_name, g_pkg_name) THEN
6340 RAISE FND_API.g_exc_unexpected_error;
6341 END IF;
6342
6343 SAVEPOINT cancel_document_PVT;
6344
6345 x_return_status := FND_API.G_RET_STS_SUCCESS;
6346 x_msg_data := NULL ;
6347
6348 IF g_debug_stmt THEN
6349 PO_DEBUG.debug_begin(d_module);
6350 PO_DEBUG.debug_var(d_module,
6351 l_progress,
6352 'online_report_id',
6353 p_da_call_rec.online_report_id);
6354 END IF;
6355
6356 l_progress := '001';
6357
6358 -- Insert the entity record into po_session_gt
6359 insrtEntityRecInSessiongt(
6360 p_api_version=> 1.0,
6361 p_init_msg_list=>FND_API.G_FALSE,
6362 p_entity_rec_tbl=>p_da_call_rec.entity_dtl_record_tbl,
6363 x_key =>l_key,
6364 x_return_status =>x_return_status,
6365 x_msg_data =>x_msg_data);
6366
6367 IF g_debug_stmt THEN
6368 PO_DEBUG.debug_var(d_module, l_progress, 'l_key', l_key);
6369 END IF;
6370
6371 --Get User ID and Login ID
6372 l_user_id := FND_GLOBAL.USER_ID;
6373 IF (FND_GLOBAL.CONC_LOGIN_ID >= 0) THEN
6374 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
6375 ELSE
6376 l_login_id := FND_GLOBAL.LOGIN_ID;
6377 END IF;
6378
6379 --Query encumbrance flags from FINANCIALS_SYSTEM_PARAMETERS
6380 --to validate action_date with encumbrance if necessary
6381 SELECT NVL(fsp.purch_encumbrance_flag, 'N'),
6382 NVL(fsp.req_encumbrance_flag, 'N')
6383 INTO l_po_encumbrance_flag,
6384 l_req_encumbrance_flag
6385 FROM financials_system_parameters fsp;
6386
6387
6388 IF g_debug_stmt THEN
6389 PO_DEBUG.debug_var(d_module, l_progress, 'l_po_encumbrance_flag',l_po_encumbrance_flag);
6390 PO_DEBUG.debug_var(d_module, l_progress, 'l_req_encumbrance_flag',l_req_encumbrance_flag);
6391 PO_DEBUG.debug_var(d_module, l_progress, 'l_login_id', l_login_id);
6392 PO_DEBUG.debug_var(d_module, l_progress, 'l_user_id', l_user_id);
6393 PO_DEBUG.debug_var(d_module, l_progress, 'x_return_status', x_return_status);
6394 PO_DEBUG.debug_var(d_module, l_progress, 'x_msg_data', x_msg_data);
6395 END IF;
6396
6397 --Do all the validations related to Cancel Action
6398 po_control_action_validations.validate_cancel_action(
6399 p_da_call_rec =>p_da_call_rec,
6400 p_key =>l_key,
6401 p_user_id => l_user_id,
6402 p_login_id =>l_login_id,
6403 p_po_enc_flag=>l_po_encumbrance_flag,
6404 p_req_enc_flag=>l_req_encumbrance_flag,
6405 x_return_status =>x_return_status,
6406 x_msg_data =>x_msg_data,
6407 x_return_code=>x_return_code);
6408
6409 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6410 RAISE FND_API.g_exc_error;
6411 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6412 RAISE FND_API.g_exc_unexpected_error;
6413 END IF;
6414
6415 IF g_debug_stmt THEN
6416 PO_DEBUG.debug_var(d_module, l_progress, 'online_report_id',
6417 p_da_call_rec.online_report_id);
6418 END IF;
6419
6420 l_progress := '002';
6421
6422 --Initialize the flag recreate demad based on the value of cancel_reqs_flag,
6423 --If PO is a complex PO, the existence of any valid shipment
6424 --Based on Drop shipments with Updateable Sales Order Lines check
6425 init_recreate_demand_flag(
6426 p_api_version=> 1.0,
6427 p_init_msg_list=>FND_API.G_FALSE,
6428 p_cancel_reqs_flag => p_da_call_rec.cancel_reqs_flag,
6429 p_entity_dtl => p_da_call_rec.entity_dtl_record_tbl,
6430 x_return_status => x_return_status,
6431 x_msg_data =>x_msg_data);
6432
6433 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6434 RAISE FND_API.g_exc_error;
6435 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6436 RAISE FND_API.g_exc_unexpected_error;
6437 END IF;
6438
6439 l_progress := '003';
6440
6441 -- Upadte the QTY Delivered/Recvd and Qty billed of schedule releases to
6442 -- Planned Po shipemnts/Distributions.So that the Planned Pos
6443 -- cancellation can be treated as a regular SPO
6444 denormPlannedPoQty(
6445 p_api_version=> 1.0,
6446 p_init_msg_list=>FND_API.G_FALSE,
6447 entity_dtl_tbl => p_da_call_rec.entity_dtl_record_tbl,
6448 p_key => l_key,
6449 x_return_status => x_return_status);
6450
6451 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6452 RAISE FND_API.g_exc_error;
6453 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6454 RAISE FND_API.g_exc_unexpected_error;
6455 END IF;
6456
6457 l_progress := '004';
6458
6459
6460
6461 -- Perform the cancellation of document
6462 -- Update the cancel flag and other relevant coulmns on the document
6463 -- and the backing requisition
6464 process_cancel_action(
6465 p_api_version=> 1.0,
6466 p_init_msg_list=>FND_API.G_FALSE,
6467 p_da_call_rec =>p_da_call_rec,
6468 p_key =>l_key,
6469 p_user_id => l_user_id,
6470 p_login_id =>l_login_id,
6471 p_po_enc_flag=>l_po_encumbrance_flag,
6472 p_req_enc_flag=>l_req_encumbrance_flag,
6473 x_return_status =>x_return_status,
6474 x_msg_data =>x_msg_data);
6475
6476 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6477 RAISE FND_API.g_exc_error;
6478 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6479 RAISE FND_API.g_exc_unexpected_error;
6480 END IF;
6481
6482 IF g_debug_stmt THEN
6483 PO_DEBUG.debug_var(d_module,l_progress,'x_return_code',x_return_code);
6484 END IF;
6485
6486 -- Check if the process_entity flag is "N' for any of the entities,
6487 -- then show a standard message as
6488 -- Cancel action has failed for some douments,
6489 -- Please run the error report to see the errors.
6490
6491 EXCEPTION
6492 WHEN FND_API.g_exc_error THEN
6493 x_return_status := FND_API.g_ret_sts_error;
6494 FND_MSG_PUB.add_exc_msg(g_pkg_name, d_api_name||':'||l_progress);
6495 ROLLBACK TO cancel_document_PVT;
6496 IF (g_debug_unexp) THEN
6497 FND_LOG.STRING(
6498 FND_LOG.LEVEL_UNEXPECTED,
6499 d_module || '.ERROR',
6500 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
6501 END IF;
6502 WHEN FND_API.g_exc_unexpected_error THEN
6503 x_return_status := FND_API.g_ret_sts_unexp_error;
6504 FND_MSG_PUB.add_exc_msg(g_pkg_name, d_api_name||':'||l_progress);
6505 ROLLBACK TO cancel_document_PVT;
6506
6507 IF (g_debug_unexp) THEN
6508 FND_LOG.STRING(
6509 FND_LOG.LEVEL_UNEXPECTED,
6510 d_module || '.UNEXPECTED ERROR',
6511 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
6512 END IF;
6513
6514
6515 WHEN OTHERS THEN
6516 x_return_status := FND_API.g_ret_sts_unexp_error;
6517 FND_MSG_PUB.add_exc_msg(g_pkg_name, d_api_name||':'||l_progress);
6518 ROLLBACK TO cancel_document_PVT;
6519
6520 IF (g_debug_unexp) THEN
6521 FND_LOG.STRING(
6522 FND_LOG.LEVEL_UNEXPECTED,
6523 d_module || '.OTHERS EXCEPTION',
6524 'EXCEPTION: LOCATION IS ' || l_progress || ' SQL CODE IS '||SQLCODE);
6525 END IF;
6526
6527 END cancel_document;
6528
6529 /**
6530 * Function : is_document_cto_order
6531 * Description : Determines if the document is a CTO order.
6532 * Parameters : p_doc_id - id of the document.
6533 * p_doc_type - type of document, either 'PO' or 'RELEASE'
6534 * Returns : TRUE if document is a CTO PO. FALSE otherwise.
6535 * Notes : See Bug 4571297
6536 * : Added No_data_found execption as part of Cancel Refactoring Project
6537 */
6538 FUNCTION is_document_cto_order (
6539 p_doc_id IN PO_HEADERS.po_header_id%TYPE,
6540 p_doc_type IN PO_DOCUMENT_TYPES_ALL_B.document_type_code%TYPE )
6541
6542 RETURN BOOLEAN
6543 IS
6544
6545 l_cto_order NUMBER;
6546 d_api_name CONSTANT VARCHAR2(30) := 'is_document_cto_order.';
6547 d_module CONSTANT VARCHAR2(100) := g_module_prefix || d_api_name;
6548
6549 l_progress VARCHAR2(8) := '000' ;
6550
6551 BEGIN
6552 BEGIN
6553 SELECT COUNT(*)
6554 INTO l_cto_order
6555 FROM po_requisition_headers_all PRH,
6556 po_requisition_lines_all PRL,
6557 po_line_locations POLL
6558 WHERE PRH.interface_source_code = 'CTO'
6559 AND PRH.requisition_header_id = PRL.requisition_header_id
6560 AND PRL.line_location_id = POLL.line_location_id
6561 AND ( ((p_doc_type = 'PO') AND (POLL.po_header_id = p_doc_id)) OR
6562 ((p_doc_type = 'RELEASE') AND (POLL.po_release_id = p_doc_id)));
6563
6564 EXCEPTION
6565
6566 WHEN No_Data_Found THEN
6567 l_cto_order := 0;
6568 IF g_debug_stmt THEN
6569 PO_DEBUG.debug_stmt(d_module, l_progress, 'Set l_cto_order = 0');
6570 END IF ;
6571 WHEN OTHERS THEN
6572 IF g_debug_stmt THEN
6573 PO_DEBUG.debug_stmt(d_module, l_progress, 'Exception in is_document_cto_order check');
6574 END IF ;
6575 RAISE FND_API.g_exc_unexpected_error;
6576 END;
6577
6578 IF (l_cto_order > 0) THEN
6579 RETURN (TRUE);
6580 ELSE
6581 RETURN (FALSE);
6582 END IF;
6583
6584 EXCEPTION
6585 WHEN OTHERS THEN
6586 RAISE FND_API.g_exc_unexpected_error;
6587 END is_document_cto_order;
6588 --<Bug 14254141 :Cancel Refactoring Project Ends>
6589
6590 /**
6591 * Public Procedure: val_cancel_backing_reqs
6592 * Requires: API message list has been initialized if p_init_msg_list is false.
6593 * PO and REQ encumbrance should be on.
6594 * Modifies: API message list
6595 * Effects: Ensures that the document has shipments that are not cancelled or
6596 * finally closed, and that they are all fully encumbered. Appends to API
6597 * message list on error.
6598 * Returns:
6599 * x_return_status - FND_API.G_RET_STS_SUCCESS if validation succeeds
6600 * FND_API.G_RET_STS_ERROR if validation fails
6601 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
6602 */
6603 PROCEDURE val_cancel_backing_reqs
6604 (p_api_version IN NUMBER,
6605 p_init_msg_list IN VARCHAR2,
6606 x_return_status OUT NOCOPY VARCHAR2,
6607 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
6608 p_doc_id IN NUMBER)
6609 IS
6610
6611 l_api_name CONSTANT VARCHAR2(30) := 'val_cancel_backing_reqs';
6612 l_api_version CONSTANT NUMBER := 1.0;
6613
6614 BEGIN
6615 --<Bug 4571297> Only perform validation if document is not CTO order
6616 IF NOT PO_Document_Cancel_PVT.is_document_cto_order
6617 (p_doc_type => p_doc_type,
6618 p_doc_id => p_doc_id)
6619 THEN
6620 -- Start standard API initialization
6621 IF FND_API.to_boolean(p_init_msg_list) THEN
6622 FND_MSG_PUB.initialize;
6623 END IF;
6624 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
6625 l_api_name, g_pkg_name)
6626 THEN
6627 RAISE FND_API.g_exc_unexpected_error;
6628 END IF;
6629 x_return_status := FND_API.g_ret_sts_success;
6630 -- End standard API initialization
6631
6632 IF (g_fnd_debug = 'Y') THEN
6633 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
6634 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
6635 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
6636 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
6637 END IF;
6638 END IF;
6639
6640 IF NOT PO_Document_Control_PVT.has_shipments
6641 (p_api_version => 1.0,
6642 p_init_msg_list => FND_API.G_FALSE,
6643 x_return_status => x_return_status,
6644 p_doc_type => p_doc_type,
6645 p_doc_id => p_doc_id)
6646 THEN
6647 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
6648 RAISE FND_API.g_exc_unexpected_error;
6649 END IF;
6650
6651 -- Document does not have any valid shipments.
6652 RAISE FND_API.g_exc_error;
6653 END IF; --<if has_shipments ...>
6654
6655
6656 IF PO_Document_Control_PVT.has_unencumbered_shipments
6657 (p_api_version => 1.0,
6658 p_init_msg_list => FND_API.G_FALSE,
6659 x_return_status => x_return_status,
6660 p_doc_type => p_doc_type,
6661 p_doc_id => p_doc_id)
6662 THEN
6663 -- Document has valid, unencumbered shipments. Cannot cancel reqs
6664 -- because encumbrance is assumed to be on.
6665 RAISE FND_API.g_exc_error;
6666 END IF; --<if has_unencumbered_shipments ...>
6667
6668 -- Check that an error did not occur in previous procedure call
6669 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
6670 RAISE FND_API.g_exc_unexpected_error;
6671 END IF;
6672
6673 END IF;
6674
6675 EXCEPTION
6676 WHEN FND_API.g_exc_error THEN
6677 x_return_status := FND_API.g_ret_sts_error;
6678 FND_MESSAGE.set_name('PO','PO_CANCEL_REQ_DISALLOWED');
6679 IF (g_fnd_debug = 'Y') THEN
6680 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
6681 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name
6682 || '.cancel_req_disallowed', FALSE);
6683 END IF;
6684 END IF;
6685 FND_MSG_PUB.add;
6686 WHEN FND_API.g_exc_unexpected_error THEN
6687 x_return_status := FND_API.g_ret_sts_unexp_error;
6688 WHEN OTHERS THEN
6689 x_return_status := FND_API.g_ret_sts_unexp_error;
6690 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
6691 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
6692 IF (g_fnd_debug = 'Y') THEN
6693 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
6694 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
6695 l_api_name || '.others_exception', 'Exception');
6696 END IF;
6697 END IF;
6698 END IF;
6699 END val_cancel_backing_reqs;
6700
6701
6702 --------------------------------------------------------------------------------
6703 --Start of Comments
6704 --Name: calculate_qty_cancel
6705
6706 --Function:
6707 --
6708 -- Updates the Quanity/Amount Cancelled columns of Po lines/Shipments
6709 -- and Distributions
6710 -- The routine will be called from "Finally Close action"
6711 --
6712 --Parameters:
6713 --IN:
6714 -- p_action_date
6715 -- p_doc_header_id
6716 -- p_line_id
6717 -- p_line_location_id
6718 -- p_document_type
6719 -- p_doc_subtype
6720
6721
6722
6723 --IN OUT:
6724
6725 -- OUT:
6726 -- x_return_status
6727 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
6728 -- FND_API.G_RET_STS_ERROR if procedure fails
6729 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
6730
6731
6732
6733 --End of Comments
6734 --------------------------------------------------------------------------------
6735
6736 PROCEDURE calculate_qty_cancel(
6737 p_api_version IN NUMBER,
6738 p_init_msg_list IN VARCHAR2,
6739 p_doc_header_id IN NUMBER,
6740 p_line_id IN NUMBER,
6741 p_line_location_id IN NUMBER,
6742 p_document_type IN VARCHAR2,
6743 p_doc_subtype IN VARCHAR2,
6744 p_action_date IN DATE,
6745 x_return_status OUT NOCOPY VARCHAR2)
6746 IS
6747
6748 d_api_name CONSTANT VARCHAR2(30) := 'calculate_qty_cancel';
6749 d_api_version CONSTANT NUMBER := 1.0;
6750 d_module CONSTANT VARCHAR2(100) := g_module_prefix||d_api_name;
6751
6752 l_progress VARCHAR2(3) := '000' ;
6753 l_line_loc_tbl po_tbl_NUMBER;
6754 l_user_id po_lines.last_updated_by%TYPE := -1;
6755 l_login_id po_lines.last_update_login%TYPE := -1;
6756
6757
6758
6759 CURSOR l_ship_cursor(
6760 p_line_location_id NUMBER,
6761 p_line_id NUMBER,
6762 p_doc_header_id NUMBER,
6763 p_document_type VARCHAR2)
6764 IS
6765 SELECT line_location_id
6766 FROM po_line_locations
6767 WHERE line_location_id = p_line_location_id
6768 AND p_line_location_id IS NOT NULL
6769 UNION ALL
6770 SELECT line_location_id
6771 FROM po_line_locations
6772 WHERE po_line_id = p_line_id
6773 AND p_line_location_id IS NULL
6774 AND p_line_id IS NOT NULL
6775
6776 UNION ALL
6777 SELECT line_location_id
6778 FROM po_line_locations
6779 WHERE po_header_id = p_doc_header_id
6780 AND p_line_location_id IS NULL
6781 AND p_line_id IS NULL
6782 AND p_doc_header_id IS NOT NULL
6783 AND p_document_type <> c_doc_type_RELEASE
6784
6785 UNION ALL
6786 SELECT line_location_id
6787 FROM po_line_locations
6788 WHERE po_release_id = p_doc_header_id
6789 AND p_line_location_id IS NULL
6790 AND p_line_id IS NULL
6791 AND p_doc_header_id IS NOT NULL
6792 AND p_document_type = c_doc_type_RELEASE;
6793
6794
6795
6796 BEGIN
6797 -- Start standard API initialization
6798 IF FND_API.to_boolean(p_init_msg_list) THEN
6799 FND_MSG_PUB.initialize;
6800 END IF;
6801
6802 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
6803 d_api_name, g_pkg_name) THEN
6804 RAISE FND_API.g_exc_unexpected_error;
6805 END IF;
6806
6807
6808 IF g_debug_stmt THEN
6809 PO_DEBUG.debug_begin(d_module);
6810 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_header_id', p_doc_header_id);
6811 PO_DEBUG.debug_var(d_module, l_progress, 'p_line_id', p_line_id);
6812 PO_DEBUG.debug_var(d_module, l_progress, 'p_line_location_id', p_line_location_id);
6813 PO_DEBUG.debug_var(d_module, l_progress, 'p_document_type', p_document_type);
6814 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
6815 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
6816
6817 END IF;
6818
6819 x_return_status :=FND_API.G_RET_STS_SUCCESS;
6820
6821 l_progress := '001' ;
6822
6823
6824 --Get User ID and Login ID
6825 l_user_id := FND_GLOBAL.USER_ID;
6826 IF (FND_GLOBAL.CONC_LOGIN_ID >= 0) THEN
6827 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
6828 ELSE
6829 l_login_id := FND_GLOBAL.LOGIN_ID;
6830 END IF;
6831
6832 IF g_debug_stmt THEN
6833 PO_DEBUG.debug_var(d_module, l_progress, 'l_user_id', l_user_id);
6834 PO_DEBUG.debug_var(d_module, l_progress, 'l_login_id', l_login_id);
6835 END IF;
6836
6837 OPEN l_ship_cursor(p_line_location_id,
6838 p_line_id,
6839 p_doc_header_id,
6840 p_document_type);
6841
6842 FETCH l_ship_cursor BULK COLLECT INTO
6843 l_line_loc_tbl;
6844
6845 CLOSE l_ship_cursor;
6846
6847
6848 IF g_debug_stmt THEN
6849 PO_DEBUG.debug_var(d_module, l_progress, 'l_line_loc_tbl.count', l_line_loc_tbl.Count);
6850 END IF;
6851
6852 l_progress := '002' ;
6853
6854 FOR i IN 1..l_line_loc_tbl.Count LOOP
6855
6856 IF g_debug_stmt THEN
6857 PO_DEBUG.debug_var(d_module, l_progress, 'l_line_loc_tbl('||i||')', l_line_loc_tbl(i));
6858 END IF;
6859
6860 l_progress := '003' ;
6861
6862 UPDATE
6863 PO_DISTRIBUTIONS_ALL POD
6864 SET
6865 pod.quantity_cancelled = pod.quantity_ordered-greatest(
6866 NVL(quantity_delivered,0),
6867 NVL(quantity_financed,0),
6868 NVL(quantity_billed,0)),
6869 pod.amount_cancelled = pod.amount_ordered-greatest(
6870 NVL(amount_delivered,0),
6871 NVL(amount_financed,0),
6872 NVL(amount_billed,0)),
6873 pod.last_update_date = p_action_date,
6874 pod.last_updated_by = l_user_id,
6875 pod.last_update_login = l_login_id
6876 WHERE pod.line_location_id =l_line_loc_tbl(i);
6877
6878 IF g_debug_stmt THEN
6879 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Distributions table', SQL%ROWCOUNT);
6880 END IF;
6881
6882 l_progress := '004' ;
6883
6884 UPDATE po_line_locations POLL
6885 SET POLL.last_update_date = p_action_date,
6886 POLL.last_updated_by = l_user_id,
6887 POLL.last_update_login = l_login_id,
6888 POLL.quantity_cancelled = (SELECT SUM(NVL(POD.quantity_cancelled,0))
6889 FROM PO_DISTRIBUTIONS_ALL POD
6890 WHERE POD.line_location_id=POLL.line_location_id),
6891 POLL.amount_cancelled = (SELECT SUM(NVL(POD.amount_cancelled, 0))
6892 FROM PO_DISTRIBUTIONS_ALL POD
6893 WHERE POD.line_location_id=POLL.line_location_id)
6894 WHERE poll.line_location_id=l_line_loc_tbl(i);
6895
6896 IF g_debug_stmt THEN
6897 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Shipments table', SQL%ROWCOUNT);
6898 END IF;
6899
6900 l_progress := '005' ;
6901
6902 UPDATE po_lines pol
6903 SET pol.last_update_date = p_action_date,
6904 pol.last_updated_by = l_user_id,
6905 pol.last_update_login = l_login_id,
6906 pol.quantity =
6907 DECODE(pol.quantity,
6908 NULL,
6909 pol.quantity,
6910 (SELECT SUM(NVL(poll.quantity,0) -NVL(poll.quantity_cancelled,0))
6911 FROM po_line_locations POLL
6912 WHERE poll.po_line_id = pol.po_line_id
6913 AND poll.shipment_type IN('STANDARD','PLANNED'))
6914 ),
6915 pol.amount =
6916 DECODE(pol.amount,
6917 NULL,
6918 pol.amount,
6919 (SELECT SUM(DECODE(POLL.amount,
6920 NULL,
6921 ((NVL(poll.quantity,0) -NVL(poll.quantity_cancelled,0))
6922 * POLL.price_override),
6923 (NVL(poll.amount, 0) -NVL(poll.amount_cancelled,0))
6924 )
6925 )
6926 FROM po_line_locations POLL
6927 WHERE poll.po_line_id = pol.po_line_id
6928 AND poll.shipment_type IN ('STANDARD','PLANNED')) )
6929
6930 WHERE pol.po_line_id IN(SELECT DISTINCT po_line_id
6931 FROM po_line_locations
6932 WHERE line_location_id=l_line_loc_tbl(i)
6933 AND shipment_type IN ('STANDARD','PLANNED'));
6934
6935 IF g_debug_stmt THEN
6936 PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Lines table', SQL%ROWCOUNT);
6937 END IF;
6938
6939
6940 END LOOP;
6941
6942 EXCEPTION
6943
6944 WHEN FND_API.g_exc_error THEN
6945 x_return_status := FND_API.g_ret_sts_error;
6946 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
6947 WHEN FND_API.g_exc_unexpected_error THEN
6948 x_return_status := FND_API.g_ret_sts_unexp_error;
6949 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
6950 WHEN OTHERS THEN
6951 x_return_status := FND_API.g_ret_sts_unexp_error;
6952 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
6953
6954 END calculate_qty_cancel;
6955
6956
6957 END PO_Document_Cancel_PVT;