[Home] [Help]
PACKAGE BODY: APPS.OE_SALES_CAN_UTIL
Source
1 PACKAGE BODY OE_SALES_CAN_UTIL AS
2 /* $Header: OEXUCANB.pls 120.17.12020000.6 2013/04/09 08:01:15 spothula ship $ */
3
4 -- Start of Comments
5 -- API name OE_SALES_CAN_UTIL
6 -- Type Private
7 -- Function
8 --
9 -- Pre-reqs
10 --
11 -- Parameters
12 --
13 -- Version Current version = 1.0
14 -- Initial version = 1.0
15 --
16 -- Notes
17 --
18 -- End of Comments
19
20 --g_ord_lvl_can boolean := FALSE; Commented for bug# 2922468
21 g_par_ord_lvl_can boolean := FALSE; -- Introduced this variable to fix bug 2230777
22 g_ser_cascade boolean := FALSE;
23
24 Procedure PerformLineCancellation(P_line_tbl IN OE_ORDER_PUB.LINE_TBL_TYPE,
25 p_line_old_tbl IN OE_ORDER_PUB.LINE_TBL_TYPE,
26 x_return_status OUT NOCOPY VARCHAR2);
27
28
29 Procedure UpdateLine
30 (p_line_id In Number
31 ,p_ordered_quantity In Number
32 ,p_change_reason In Varchar2
33 ,p_change_comments In Varchar2
34 ,x_return_status out nocopy Varchar2
35
36 ,x_msg_count out nocopy Number
37
38 ,x_msg_data out nocopy Varchar2)
39
40 Is
41 l_api_name VARCHAR2(30) := 'UPDATELINE';
42 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
43 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
44 l_control_rec OE_GLOBALS.Control_Rec_Type;
45 l_return_status VARCHAR2(1);
46 l_line_rec OE_Order_PUB.Line_Rec_Type;
47
48 --
49 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
50 --
51 Begin
52
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54
55 IF l_debug_level > 0 THEN
56 oe_debug_pub.add( 'ENTERING OE_SALES_CAN_UTIL.UPDATE_LINE' ) ;
57 END IF;
58
59 -- Set control flags.
60
61 l_control_rec.controlled_operation := TRUE;
62 l_control_rec.change_attributes := TRUE;
63 l_control_rec.validate_entity := TRUE;
64 l_control_rec.write_to_DB := TRUE;
65 l_control_rec.default_attributes := FALSE;
66 l_control_rec.process := TRUE;
67
68 -- Instruct API to retain its caches
69
70 l_control_rec.clear_api_cache := FALSE;
71 l_control_rec.clear_api_requests := FALSE;
72
73 -- Read line from cache
74
75 IF l_debug_level > 0 THEN
76 oe_debug_pub.add( 'BEFORE CALLING LOCK ROW' ) ;
77 END IF;
78
79 /* Fix for Bug 1763178. Passing line record instead of table. */
80
81 OE_Line_Util.lock_Row
82 ( p_line_id => p_line_id,
83 p_x_line_rec => l_line_rec
84 ,x_return_status => l_return_status
85 );
86
87 IF l_debug_level > 0 THEN
88 oe_debug_pub.add( 'AFTER CALLING LOCK ROW' ) ;
89 END IF;
90
91 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
92 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
94 RAISE FND_API.G_EXC_ERROR;
95 END IF;
96
97 l_line_tbl(1) := l_line_rec;
98 l_line_tbl(1).db_flag := FND_API.G_TRUE;
99
100 l_old_line_tbl(1) := l_line_tbl(1);
101
102 l_line_tbl(1).ordered_quantity := p_ordered_quantity;
103
104 /*lchen fix bug 1879607. Passing input parameters instead of line record*/
105
106 l_line_tbl(1).change_reason := p_change_reason;
107 l_line_tbl(1).change_comments := p_change_comments;
108
109 -- Set Operation.
110 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
111
112 -- Populate line table
113
114 -- Call OE_Order_PVT.Process_order
115 g_ser_cascade := TRUE;
116
117 IF l_debug_level > 0 THEN
118 oe_debug_pub.add( 'BEFORE CALLING PROCESS ORDER' ) ;
119 END IF;
120
121 oe_order_pvt.Lines
122 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
123 , p_control_rec => l_control_rec
124 , p_x_line_tbl => l_line_tbl
125 , p_x_old_line_tbl => l_old_line_tbl
126 , x_return_status => l_return_status
127 );
128
129 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
132 RAISE FND_API.G_EXC_ERROR;
133 END IF;
134
135 IF l_debug_level > 0 THEN
136 oe_debug_pub.add( 'AFTER CALLING PROCESS ORDER' ) ;
137 END IF;
138
139 /* jolin start comment out nocopy for notification project
140
141 -- API to call notify_oc and ack and to process delayed requests
142 OE_Order_PVT.Process_Requests_And_Notify
143 ( p_process_requests => FALSE
144 , p_notify => TRUE
145 , x_return_status => l_return_status
146 , p_line_tbl => l_line_tbl
147 , p_old_line_tbl => l_old_line_tbl
148 );
149
150 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
151 oe_debug_pub.ADD('Update Line Process Order return UNEXP_ERROR');
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
154 oe_debug_pub.ADD('Update Line Process Order return RET_STS_ERROR');
155 RAISE FND_API.G_EXC_ERROR;
156 END IF;
157 jolin end */
158
159 g_ser_cascade := FALSE;
160
161 IF l_debug_level > 0 THEN
162 oe_debug_pub.add( 'EXITING OE_SALES_CAN_UTIL.UPDATE_LINE' ) ;
163 END IF;
164
165 EXCEPTION
166 WHEN FND_API.G_EXC_ERROR THEN
167 g_ser_cascade := FALSE;
168 x_return_status := FND_API.G_RET_STS_ERROR;
169 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
170 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
171 oe_msg_pub.Add_Exc_Msg
172 (G_PKG_NAME
173 ,l_api_name);
174 END IF;
175 g_ser_cascade := FALSE;
176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
177 WHEN OTHERS THEN
178 g_ser_cascade := FALSE;
179 IF l_debug_level > 0 THEN
180 oe_debug_pub.add( 'UNEXPECTED ERROR IN ' || G_PKG_NAME || ':' || L_API_NAME ) ;
181 END IF;
182 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
183 oe_msg_pub.Add_Exc_Msg
184 (G_PKG_NAME
185 ,l_api_name);
186 END IF;
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
188 END UpdateLine;
189
190 PROCEDURE perform_line_change
191 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
192 , p_old_line_rec IN OE_Order_PUB.Line_Rec_Type := OE_Order_PUB.G_MISS_LINE_REC
193 , x_return_status OUT NOCOPY VARCHAR2
194
195 )
196 IS
197 l_api_name VARCHAR2(30):= 'Perform_line_change';
198 l_line_id NUMBER;
199 l_line_ind NUMBER;
200 l_return_status VARCHAR2(30);
201 l_result VARCHAR2(30);
202 --l_pending_rec OE_CHG_ORDER_PVT.G_Pending_Request_REC_Type;
203 l_msg_count NUMBER;
204 l_msg_data VARCHAR2(240);
205 l_entity_id NUMBER;
206 l_entity_code VARCHAR2(1) := 'O';
207 l_line_process_name VARCHAR2(30);
208 l_hold_release_rec OE_HOLD_SOURCES_PVT.HOLD_RELEASE_REC;
209 l_cancelled_qty NUMBER;
210 lhisttypecode VARCHAR2(240);
211 l_fulfill VARCHAR2(30);
212 l_order_tbl OE_HOLDS_PVT.order_tbl_type;
213 l_hold_id OE_HOLD_DEFINITIONS.HOLD_ID%TYPE;
214 l_release_reason_code OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE;
215 l_release_comment OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE;
216
217 -- INVCONV
218 l_cancelled_qty2 NUMBER; -- INVCONV
219
220 -- This is moved to pre write to cover all quantity changes
221 po_result BOOLEAN;
222 po_supply_id NUMBER;
223 po_header_id NUMBER; --Bug 5335066
224
225 l_is_ota_line BOOLEAN;
226 l_order_quantity_uom VARCHAR2(3);
227 l_mtl_supply_quantity NUMBER; -- 6710187
228
229 -- We do not need this cursor since we started storing REQUISITION_LINE_ID in
230 -- source_document_line_id.
231 /*cursor poreq is
232 SELECT PRL.REQUISITION_LINE_ID
233 FROM PO_REQUISITION_LINES PRL,
234 PO_REQUISITION_HEADERS PRH
235 WHERE PRH.SEGMENT1 = p_line_rec.source_document_id
236 AND PRL.LINE_NUM = p_line_rec.source_document_line_id
237 AND PRL.REQUISITION_HEADER_ID =PRH.REQUISITION_HEADER_ID;*/
238
239 l_require_reason BOOLEAN;
240
241 --
242 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
243 --
244 BEGIN
245
246 -- bug2743789 if cancellation is defined at entered level and audit parameter
247 -- defined at booked orders only, then cancel history record would be inserted
248 -- without reason code.
249
250 IF l_debug_level > 0 THEN
251 OE_DEBUG_PUB.add('Entering perform_line_change() with reason required flag : '||OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG,1);
252 END IF;
253
254 l_order_quantity_uom := p_line_rec.order_quantity_uom; --bug 5702849
255 oe_debug_pub.add('5702849: Value of l_order_quantity_uom :'||l_order_quantity_uom);
256
257 IF (p_line_rec.operation <> oe_globals.g_opr_create AND
258 -- p_line_rec.split_action_code <> 'SPLIT') THEN -- Bug 8841055
259 Nvl(p_line_rec.split_action_code,Fnd_Api.G_Miss_Char) <> 'SPLIT') THEN
260
261 IF l_debug_level > 0 THEN
262 oe_debug_pub.add('Entering oe_sales_can_util.perform_line_change' ) ;
263 END IF;
264
265 x_return_status := FND_API.G_RET_STS_SUCCESS;
266
267 IF (nvl(p_line_rec.ordered_quantity, 0) < nvl(p_old_line_rec.ordered_quantity, 0)) THEN
268 l_cancelled_qty := nvl(p_old_line_rec.ordered_quantity, 0) - nvl(p_line_rec.ordered_quantity, 0);
269 --13939745 start
270 --IF (oe_sales_can_util.G_REQUIRE_REASON) THEN
271 IF ((oe_sales_can_util.G_REQUIRE_REASON) and (nvl(oe_globals.g_pricing_recursion, 'N') = 'N')) THEN
272 --13939745 end
273 lhisttypecode := OE_GLOBALS.G_CAN_HIST_TYPE_CODE;
274 ELSE
275 lhisttypecode := 'QUANTITY UPDATE';
276 END IF;
277 ELSIF (nvl(p_line_rec.ordered_quantity, 0) > nvl(p_old_line_rec.ordered_quantity, 0)) THEN
278 lhisttypecode := 'QUANTITY UPDATE';
279 END IF;
280
281 -- INVCONV
282 IF (nvl(p_line_rec.ordered_quantity2, 0) < nvl(p_old_line_rec.ordered_quantity2, 0)) THEN
283 l_cancelled_qty2 := nvl(p_old_line_rec.ordered_quantity2, 0) - nvl(p_line_rec.ordered_quantity2, 0);
284 IF (oe_sales_can_util.G_REQUIRE_REASON) THEN
285 lhisttypecode := OE_GLOBALS.G_CAN_HIST_TYPE_CODE;
286 ELSE
287 lhisttypecode := 'QUANTITY UPDATE';
288 END IF;
289 ELSIF (nvl(p_line_rec.ordered_quantity2, 0) > nvl(p_old_line_rec.ordered_quantity2, 0)) THEN
290 lhisttypecode := 'QUANTITY UPDATE';
291 END IF;
292
293
294 -- Update Service Lines
295 /*
296 ** Fix bug # 2126033
297 ** update service not called when order is being cancelled.
298 if NOT(g_ord_lvl_can) then
299 */
300
301 /*
302 ** Fix Bug # 2157850
303 ** Store the g_require_reason global before calling update
304 ** service and restore after that.
305 */
306 l_require_reason := oe_sales_can_util.g_require_reason;
307
308 IF l_debug_level > 0 THEN
309 oe_debug_pub.add('Before calling update_service()',1) ;
310 END IF;
311
312 update_service(p_line_rec, p_old_line_rec, x_return_status);
313
314 IF l_debug_level > 0 THEN
315 oe_debug_pub.add('After calling update_service , return status : '||x_return_status,1) ;
316 END IF;
317
318 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
319 if x_return_status = FND_API.G_RET_STS_ERROR then
320 raise FND_API.G_EXC_ERROR;
321 else
322 raise FND_API.G_EXC_UNEXPECTED_ERROR;
323 end if;
324 end if;
325
326 oe_sales_can_util.g_require_reason := l_require_reason ;
327
328 -- end if; -- Not Order Level Cancellation.
329 -- This code is moved to pre write to cover all quantity changes
330 -- call PO if its Internal requistion
331
332
333 /* 7576948: IR ISO Change Management project Start */
334 /* -- This code is commented for IR ISO project, as from this project onwards
335 -- there will not be any descrepancy between the OM and Purchasing for
336 -- partial internal sales order line cancellation, createed as part of
337 -- system split after partial shipping against the original internal sales
338 -- order line. From this project, onwards, if an internal sales order line
339 -- is cancelled, it will log a OE_GLOBALS.G_UPDATE_REQUISITION delayed
340 -- request, whose execution will call the Puchasing API
341 -- PO_RCO_Validation_GRP.Update_ReqCancel_from_SO(). It will be this
342 -- purchasing API responsibility to update the MTL_Supply table for
343 -- quantity change in internal sales order line.
344
345 -- For details on IR ISO CMS project, please refer to FOL >
346 -- OM Development > OM GM > 12.1.1 > TDD > IR_ISO_CMS_TDD.doc
347
348
349
350 IF p_line_rec.source_document_type_id = 10 THEN
351 IF (nvl(p_line_rec.ordered_quantity,0) < nvl(p_old_line_rec.ordered_quantity,0)) THEN
352 IF l_debug_level > 0 THEN
353 oe_debug_pub.add('Qty reduced on internal order',1);
354 END IF;
355
356 po_supply_id := p_line_rec.source_document_line_id;
357 po_header_id := p_line_rec.source_document_id; --Bug 5335066
358 --l_order_quantity_uom := p_line_rec.order_quantity_uom; --Bug 5335066 [Moved the initialization outside the condition.]
359
360 IF po_supply_id IS NOT NULL THEN
361
362 IF l_debug_level > 0 THEN
363 oe_debug_pub.add( 'SUPPLY ID EXISTS , BEFORE CALLING PO_SUPPLY.PO_REQ_SUPPLY ( ) ' ) ;
364 END IF;
365 -- INVCONV no requirement to change this
366
367 IF p_line_rec.split_from_line_id IS NOT NULL THEN -- 6710187 start
368
369 BEGIN
370 SELECT quantity
371 INTO l_mtl_supply_quantity
372 FROM mtl_supply
373 WHERE req_line_id = p_line_rec.source_document_line_id
374 AND supply_type_code = 'REQ';
375
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378 NULL;
379 WHEN TOO_MANY_ROWS THEN
380 fnd_message.set_name('ONT','OE_CAN_UPDATE_SUPPLY');
381 oe_msg_pub.add;
382 raise FND_API.G_EXC_ERROR;
383 END;
384
385 IF p_old_line_rec.ordered_quantity <> l_mtl_supply_quantity THEN
386
387 fnd_message.set_name('ONT','OE_CAN_UPDATE_SUPPLY');
388 oe_msg_pub.add;
389 raise FND_API.G_EXC_ERROR;
390 END IF;
391
392 END IF; -- 6710187 end
393
394 po_result := po_supply.po_req_supply( --Bug 5335066
395 p_docid => po_header_id,
396 p_lineid => po_supply_id,
397 p_shipid => NULL,
398 p_action => 'Update_Req_Line_Qty',
399 p_recreate_flag => FALSE,
400 p_qty => p_line_rec.ordered_quantity,
401 p_receipt_date => null,
402 p_reservation_action=>'UPDATE_SO_QUANTITY',
403 p_ordered_uom => l_order_quantity_uom);
404 --p_reservation_action added for bug4277603
405
406 IF l_debug_level > 0 THEN
407 oe_debug_pub.add('After calling po_supply.po_req_supply()',1);
408 END IF;
409
410 END IF;
411
412 IF po_supply_id IS NULL THEN
413 fnd_message.set_name('ONT','OE_CAN_NO_SUPPLY_ID');
414 oe_msg_pub.add;
415 -- raise FND_API.G_EXC_ERROR;
416 END IF;
417 END IF;
418 END IF; -- For cancellation
419
420 */
421 /* IR ISO Change Management project End */
422
423
424 IF l_debug_level > 0 THEN
425 OE_DEBUG_PUB.add('Audit Trail Reason Required Flag : '||OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG,1);
426 oe_debug_pub.add('Line change reason : '||p_line_rec.change_reason,1);
427 END IF;
428
429 -- #2743789 changes
430 IF oe_sales_can_util.G_REQUIRE_REASON THEN
431
432 IF l_debug_level > 0 THEN
433 oe_debug_pub.add('Change reason required',1);
434 END IF;
435
436 --Commented out hre following block of code for bug #3665150
437 /* IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN -- reinstated for 2653505
438
439 IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG <> 'N' THEN
440 IF p_line_rec.change_reason IS NULL OR
441 p_line_rec.change_reason = FND_API.G_MISS_CHAR THEN
442 IF l_debug_level > 0 THEN
443 oe_debug_pub.add('Cancellation Reason is Not Provided',1);
444 END IF;
445 fnd_message.set_name('ONT','OE_CAN_REASON_NOT');
446 oe_msg_pub.add;
447 raise FND_API.G_EXC_ERROR;
448 END IF;
449 END IF;
450
451 ELSE.*/
452 IF p_line_rec.change_reason IS NULL OR
453 p_line_rec.change_reason = FND_API.G_MISS_CHAR THEN
454 IF l_debug_level > 0 THEN
455 oe_debug_pub.add('Cancellation Reason is Not Provided',1);
456 END IF;
457 fnd_message.set_name('ONT','OE_CAN_REASON_NOT');
458 oe_msg_pub.add;
459 raise FND_API.G_EXC_ERROR;
460 END IF;
461 -- END IF;
462 END IF;
463
464 IF p_line_rec.change_reason IS NOT NULL AND p_line_rec.change_reason <> FND_API.G_MISS_CHAR THEN
465
466 IF l_debug_level > 0 THEN
467 oe_debug_pub.add('Before calling record line history',5);
468 END IF;
469
470 --11.5.10 Versioning/Audit Trail updates
471 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
472 OE_Versioning_Util.Capture_Audit_Info(p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
473 p_entity_id => p_line_rec.line_id,
474 p_hist_type_code => lhisttypecode);
475 --log delayed request
476 OE_Delayed_Requests_Pvt.Log_Request(p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
477 p_entity_id => p_line_rec.header_id,
478 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
479 p_requesting_entity_id => p_line_rec.line_id,
480 p_request_type => OE_GLOBALS.G_VERSION_AUDIT,
481 x_return_status => l_return_status);
482 OE_GLOBALS.G_AUDIT_HISTORY_RQD_FLAG := 'N';
483 ELSE
484 OE_CHG_ORDER_PVT.RecordLineHist
485 ( p_line_id => p_line_rec.line_id
486 , p_line_rec => p_line_rec
487 , p_hist_type_code => lhisttypecode
488 , p_reason_code => p_line_rec.change_reason
489 , p_comments => p_line_rec.change_comments
490 , p_wf_activity_code => 'OEOL_CANCEL'
491 , p_wf_result_code => 'COMPLETE'
492 , x_return_status => l_return_status);
493
494 END IF;
495 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
496 IF l_return_status = FND_API.G_RET_STS_ERROR then
497 RAISE FND_API.G_EXC_ERROR;
498 ELSE
499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
500 END IF;
501 END IF;
502
503 IF l_debug_level > 0 THEN
504 oe_debug_pub.add('After calling record line history',5);
505 END IF;
506
507 END IF;
508
509 -- Call OTA API for training lines that are cancelled.
510
511 --l_order_quantity_uom := p_line_rec.order_quantity_uom; Moved up before the call to po_supply.po_req_supply
512 l_is_ota_line := OE_OTA_UTIL.Is_OTA_Line(l_order_quantity_uom);
513
514 If (l_is_ota_line) THEN
515 IF l_debug_level > 0 THEN
516 oe_debug_pub.add( 'JPN: IT IS A OTA LINE' , 1 ) ;
517 END IF;
518
519 IF p_line_rec.ordered_quantity = 0 THEN
520 IF l_debug_level > 0 THEN
521 oe_debug_pub.add( 'JPN: BEFORE CALLING NOTIFY_OTA' , 1 ) ;
522 END IF;
523
524 oe_ota_util.Notify_OTA(
525 p_line_id => p_line_rec.line_id,
526 p_org_id => p_line_rec.org_id,
527 p_order_quantity_uom => l_order_quantity_uom,
528 p_daemon_type => 'C',
529 x_return_status => l_return_status);
530
531 IF l_debug_level > 0 THEN
532 oe_debug_pub.add( 'JPN: AFTER CALLING NOTIFY_OTA' , 1 ) ;
533 END IF;
534
535 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
536 if l_return_status = FND_API.G_RET_STS_ERROR then
537 RAISE FND_API.G_EXC_ERROR;
538 else
539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
540 end if;
541 end if;
542 end if;
543
544 IF l_debug_level > 0 THEN
545 oe_debug_pub.add( 'JPN: NOT A OTA LINE' , 1 ) ;
546 END IF;
547 end if;
548
549 -- Call to Remove from fullfillment sets
550 IF (oe_sales_can_util.G_REQUIRE_REASON) THEN
551 If p_line_rec.ordered_quantity = 0 then
552
553 -- Remove from fulfillment sets
554
555 --oe_set_util.Remove_from_fulfillment(p_line_id => p_line_rec.line_id);
556 -- added the following IF condition to fix bug 2230777
557 -- using OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can for bug# 2922468
558 if NOT(OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can) and NOT(g_par_ord_lvl_can)then
559 IF l_debug_level > 0 THEN
560 oe_debug_pub.add( 'BEFORE CALLING OE_LINE_FULLFILL.CANCEL_LINE ( ) ' ) ;
561 END IF;
562
563 oe_line_fullfill.cancel_line(p_line_id => p_line_rec.line_id,
564 x_return_status => l_return_status);
565
566 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
567 if l_return_status = FND_API.G_RET_STS_ERROR then
568 raise FND_API.G_EXC_ERROR;
569 else
570 raise FND_API.G_EXC_UNEXPECTED_ERROR;
571 end if;
572 end if;
573 end if;
574
575 oe_set_util.Remove_from_fulfillment(p_line_id => p_line_rec.line_id);
576
577 End if;
578
579 /*
580 ** Recommented for clarity. WF activity is progressed in OEXULINB.pls
581
582 Abort Wf activities
583 OE_STANDARD_WF.Get_LineProcessName(OE_GLOBALS.G_WFI_LIN, p_line_rec.line_id,
584 l_line_process_name);
585
586 If p_line_rec.ordered_quantity = 0 then
587 wf_engine.abortprocess(OE_Globals.G_WFI_LIN
588 ,to_char(p_line_rec.line_id)
589 ,l_line_process_name);
590 OE_DEBUG_PUB.ADD('Calling Wf Handle Error ');
591 Using Retry option to run close activity
592 OE_GLOBALS.G_RECURSION_MODE := 'Y';
593 wf_engine.handleerror(OE_Globals.G_WFI_LIN
594 ,to_char(p_line_rec.line_id)
595 ,'CLOSE_LINE',
596 'RETRY','CANCEL');
597 OE_DEBUG_PUB.ADD('After Calling Wf Handle Error ');
598 NULL;
599 OE_GLOBALS.G_RECURSION_MODE := 'N';
600 end if;
601 */
602
603 -- Release Holds
604
605 IF p_line_rec.ordered_quantity = 0 THEN
606
607 l_order_tbl(1).line_id := p_line_rec.line_id;
608 l_release_reason_code := 'OM_CANCEL';
609
610 IF l_debug_level > 0 THEN
611 oe_debug_pub.add( 'BEFORE CALLING RELEASE HOLDS' ) ;
612 END IF;
613
614 OE_Holds_pub.release_holds(
615 p_order_tbl => l_order_tbl,
616 p_release_reason_code => l_release_reason_code,
617 p_release_comment => l_release_comment,
618 p_hold_id => Null,
619 x_return_status => l_return_status,
620 x_msg_count => l_msg_count,
621 x_msg_data => l_msg_data);
622
623 IF l_debug_level > 0 THEN
624 oe_debug_pub.add( 'AFTER CALLING RELEASE HOLDS :' || L_RETURN_STATUS ) ;
625 END IF;
626
627 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
628 if l_return_status = FND_API.G_RET_STS_ERROR then
629 raise FND_API.G_EXC_ERROR;
630 else
631 raise FND_API.G_EXC_UNEXPECTED_ERROR;
632 end if;
633 end if;
634 End if; -- Rlease holds
635
636 end if;
637 END IF;
638
639 --oe_sales_can_util.g_require_reason := FALSE;
640
641 -- If the ordered quantity is Zero then a delayed request is logged
642
643 IF p_line_rec.ordered_quantity = 0 THEN
644
645 oe_delayed_requests_pvt.log_request(
646 p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
647 p_entity_id => p_line_rec.header_id,
648 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL,
649 p_requesting_entity_id => p_line_rec.header_id,
650 p_request_type => OE_GLOBALS.G_DELETE_CHARGES,
651 x_return_status => l_return_status);
652
653 End if;
654
655 IF l_debug_level > 0 THEN
656 oe_debug_pub.add( 'EXITING OE_SALES_CAN_UTIL.PERFORM_LINE_CHANGE' ) ;
657 END IF;
658
659 EXCEPTION
660 WHEN FND_API.G_EXC_ERROR THEN
661 x_return_status := FND_API.G_RET_STS_ERROR;
662 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
663 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
664 oe_msg_pub.Add_Exc_Msg
665 (G_PKG_NAME
666 ,l_api_name);
667 END IF;
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
669 WHEN OTHERS THEN
670 IF l_debug_level > 0 THEN
671 oe_debug_pub.add( 'UNEXPECTED ERROR IN ' || G_PKG_NAME || ':' || 'PERFORM_LINE_CHANGE' ) ;
672 END IF;
673 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
674 oe_msg_pub.Add_Exc_Msg
675 (G_PKG_NAME
676 ,l_api_name);
677 END IF;
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
679 END Perform_Line_change;
680
681 -- Comment Label for procedure added as part of Inline Documentation Drive.
682 ---------------------------------------------------------------------------------
683 -- Procedure Name : Check_Constraints
684 -- Input Params : p_x_line_rec : Current Line Record.
685 -- p_old_line_rec : Old Line Record.
686 -- Output Params : x_return_status : Return Status from the procedure.
687 -- : p_x_line_rec : Current Line Record.
688 -- Description : This procedure does various checks during Line Cancellation
689 -- flow or during reducing the ordered_quantity on sales order
690 -- line. Checks are like, Updating quantity on Service Lines
691 -- not allowed, or setting qty to zero on Booked order not
692 -- allowed, and processing of Cancelled Quantities and Flags
693 -- if the flow is a cancellation flow.
694 -- This procedure is called from OE_HEADER_UTIL(OEXUHDRB.pls)
695 -- and OE_LINE_UTIL(OEXULINB.pls), for checking constraints
696 -- on cancellation and calculating Cancelled Quantities
697 -- for Cancellation flows, or for other flows where secondary
698 -- Cancelled quantities need to be populated.
699 ---------------------------------------------------------------------------------
700
701 PROCEDURE check_constraints
702 ( p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
703 , p_old_line_rec IN OE_Order_PUB.Line_Rec_Type := OE_Order_PUB.G_MISS_LINE_REC
704 , x_return_status OUT NOCOPY varchar2
705
706 )
707 IS
708 l_return_status VARCHAR2(30) :=FND_API.G_RET_STS_SUCCESS;
709 --l_line_Rec oe_order_pub.line_rec_type := p_line_Rec;
710 x_result NUMBER := 0 ;
711 x_msg_count NUMBER;
712 x_msg_data VARCHAR2(255);
713 l_api_name VARCHAR2(30):= 'Check_Constraints';
714
715 l_item_rec OE_ORDER_CACHE.item_rec_type; -- INVCONV
716 --
717 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
718 --
719 BEGIN
720 -- x_line_Rec := p_line_Rec;
721 IF (p_x_line_rec.operation <> oe_globals.g_opr_create AND
722 nvl(p_x_line_rec.split_action_code,'X') <> 'SPLIT') THEN
723 -- OE_SALES_CAN_UTIL.G_REQUIRE_REASON := FALSE;
724
725 IF l_debug_level > 0 THEN
726 oe_debug_pub.add( 'ENTERING OE_SALES_CAN_UTIL.CHECK_CONSTRAINTS' ) ;
727 END IF;
728 -- initialize API return status to success
729 l_return_status := FND_API.G_RET_STS_SUCCESS;
730
731 IF l_debug_level > 0 THEN
732 oe_debug_pub.add( 'CHECKING CONSTRAINTS FOR LINE ID '|| TO_CHAR ( P_X_LINE_REC.LINE_ID ) ) ;
733 END IF;
734
735 if ( p_old_line_rec.cancelled_flag = 'Y') then
736 fnd_message.set_name('ONT', 'OE_CANCEL_NOTHING');
737 oe_msg_pub.add;
738 RAISE FND_API.G_EXC_ERROR ;
739 end if;
740
741 -- Check if line is booked and and is not a cancellations and orderquantity is
742 -- zero
743 -- This is to fix the order level status problem. If the call is made
744 -- from order level it will treated as cancellation always
745 -- using OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can for bug# 2922468
746 IF OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can THEN
747 OE_SALES_CAN_UTIL.G_REQUIRE_REASON := TRUE;
748 END IF;
749
750 IF NOT OE_SALES_CAN_UTIL.G_REQUIRE_REASON THEN
751
752 IF (nvl(p_x_line_rec.booked_flag,'N') = 'Y' AND
753 nvl(p_x_line_rec.ordered_quantity,0) = 0 ) THEN
754
755 fnd_message.set_name('ONT', 'OE_QTY_ZERO_NOT_ALLOWED');
756 oe_msg_pub.add;
757 RAISE FND_API.G_EXC_ERROR ;
758 END IF;
759 END IF;
760
761 -- Check Service here. We allow only full cancellation of service
762 -- if service alone is updated.
763
764 if (p_x_line_rec.ITEM_TYPE_CODE = 'SERVICE') AND
765 /* --sol_ord_er #16014165, Update qty check not required for Subscription Service */
766 NVL(p_x_line_rec.subscription_enable_flag,'N')='N' AND
767 NOT (g_ser_cascade) AND
768 (p_x_line_rec.Ordered_Quantity <> 0) then
769
770 if (p_x_line_rec.Ordered_Quantity <>
771 p_old_line_rec.Ordered_Quantity) THEN
772 fnd_message.set_name('ONT', 'OE_CAN_SERV_AMT_NOT_ALLOWED');
773 oe_msg_pub.add;
774 RAISE FND_API.G_EXC_ERROR;
775 end if;
776 end if;
777
778 -- Set Ordered level cancellation to true or false
779 -- using OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can for bug# 2922468
780 IF OE_SALES_CAN_UTIL.G_REQUIRE_REASON AND OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can THEN
781
782 oe_sales_can_util.g_order_cancel := TRUE;
783 ELSE
784 IF oe_sales_can_util.g_order_cancel THEN
785 oe_sales_can_util.g_order_cancel := FALSE;
786 END IF;
787 END IF;
788
789 -- Compute Cancelled Quantity
790 -- Compute Cancelled Quantity2
791 -- invconv
792
793 -- Bug 7679175 Start
794 -- Moved this condition here, since Cancelled_Quantity2 should
795 -- only be calculated when Cancelled_Quantity is calculated.
796
797 IF OE_SALES_CAN_UTIL.G_REQUIRE_REASON THEN
798
799
800 IF l_debug_level > 0 THEN
801 oe_debug_pub.add( 'IN CHANGE REASON REQUIRED' ) ;
802 END IF;
803
804 IF p_x_line_rec.change_reason IS NULL THEN
805 /*fnd_message.set_name('ONT','OE_CAN_REASON_NOT');
806 oe_msg_pub.add;*/
807 NULL;
808 END IF;
809
810 -- Bug 7679175 End
811
812 IF oe_line_util.dual_uom_control -- INVCONV Process_Characteristics
813 (p_x_line_rec.inventory_item_id,p_X_line_rec.ship_from_org_id,l_item_rec) THEN
814 IF l_item_rec.tracking_quantity_ind = 'PS' THEN -- INVCONV
815 if l_debug_level > 0 then
816 oe_debug_pub.add(' Get dual uom - tracking in P and S ');
817 end if;
818
819
820 IF l_debug_level > 0 THEN
821 oe_debug_pub.add( 'BEFORE CALLING CAL_CANCELLED_QTY2' ) ;
822 END IF;
823
824 p_x_line_rec.cancelled_quantity2 :=oe_sales_can_util.Cal_cancelled_qty2(
825 p_x_line_rec,
826 p_old_line_rec);
827
828 IF l_debug_level > 0 THEN
829 oe_debug_pub.add( 'CANCELLED QUANTITY2 IS '||P_X_LINE_REC.CANCELLED_QUANTITY2 ) ;
830 END IF;
831
832 END IF; -- IF l_item_rec.tracking_quantity_ind = 'PS'
833
834 END IF; -- IF oe_line_util.dual_uom_control
835
836 -- Bug 7679175 Start
837 -- Moving this condition to before calculating Cancelled_Quantity2, since it should
838 -- only be calculated when Cancelled_Quantity is calculated.
839 /*
840 IF OE_SALES_CAN_UTIL.G_REQUIRE_REASON THEN
841
842
843 IF l_debug_level > 0 THEN
844 oe_debug_pub.add( 'IN CHANGE REASON REQUIRED' ) ;
845 END IF;
846
847 IF p_x_line_rec.change_reason IS NULL THEN
848 fnd_message.set_name('ONT','OE_CAN_REASON_NOT');
849 oe_msg_pub.add;
850 NULL;
851 END IF;
852 */
853 -- Bug 7679175 End
854
855
856 IF l_debug_level > 0 THEN
857 oe_debug_pub.add( 'BEFORE CALLING CAL_CANCELLED_QTY' ) ;
858 END IF;
859
860 p_x_line_rec.cancelled_quantity :=oe_sales_can_util.Cal_cancelled_qty(
861 p_x_line_rec,
862 p_old_line_rec);
863
864 IF l_debug_level > 0 THEN
865 oe_debug_pub.add( 'CANCELLED QUANTITY IS '||P_X_LINE_REC.CANCELLED_QUANTITY ) ;
866 END IF;
867
868
869
870
871 --Set cancelled flag and open flag if complete cancellation
872 IF p_x_line_rec.ordered_quantity = 0 THEN
873 /* NC - Added for OPM 04/23/01 Bug#1749562 */
874
875 IF(p_x_line_rec.ordered_quantity2 IS NOT NULL AND
876 p_x_line_rec.ordered_quantity2 <> 0 ) THEN
877
878 p_x_line_rec.ordered_quantity2 := 0;
879
880 END IF;
881 /* End of OPM changes. */
882
883 -- Reverted the changes to take care of P1 issues
884
885 p_x_line_rec.cancelled_flag := 'Y';
886
887 /*
888 ** Fix Bug # 2238002:
889 ** Following columns are updated when WF Handle error is called.
890 p_x_line_rec.open_flag := 'N';
891 p_x_line_rec.flow_status_code := 'CANCELLED';
892 */
893
894 END IF;
895
896
897
898
899
900 ELSIF p_x_line_rec.ordered_quantity > p_old_line_rec.ordered_quantity THEN
901
902 -- bug 3542477, must calculate cancelled quantity in case user
903 -- reverts their selection
904 IF l_debug_level > 0 THEN
905 oe_debug_pub.add( 'CHECKING ORIGINAL CANCELLED_QUANTITY' ) ;
906 END IF;
907
908 p_x_line_rec.cancelled_quantity :=oe_sales_can_util.Cal_cancelled_qty(
909 p_x_line_rec,
910 p_old_line_rec);
911 -- INVCONV
912
913
914 IF oe_line_util.dual_uom_control -- INVCONV Process_Characteristics
915 (p_x_line_rec.inventory_item_id,p_X_line_rec.ship_from_org_id,l_item_rec) THEN
916 IF l_item_rec.tracking_quantity_ind = 'PS' THEN -- INVCONV
917 if l_debug_level > 0 then
918 oe_debug_pub.add(' Get dual uom - tracking in P and S ');
919 end if;
920
921
922 IF l_debug_level > 0 THEN
923 oe_debug_pub.add( 'BEFORE CALLING CAL_CANCELLED_QTY2' ) ;
924 END IF;
925
926 p_x_line_rec.cancelled_quantity2 :=oe_sales_can_util.Cal_cancelled_qty2(
927 p_x_line_rec,
928 p_old_line_rec);
929
930 IF l_debug_level > 0 THEN
931 oe_debug_pub.add( 'CANCELLED QUANTITY2 IS '||P_X_LINE_REC.CANCELLED_QUANTITY2 ) ;
932 END IF;
933
934 END IF; -- IF l_item_rec.tracking_quantity_ind = 'PS'
935
936 END IF; -- IF oe_line_util.dual_uom_control
937
938
939
940
941
942 END IF;
943
944 IF l_debug_level > 0 THEN
945 oe_debug_pub.add( 'EXITING OE_SALES_CAN_UTIL.CHECK_CONSTRAINTS' ) ;
946 END IF;
947
948 END IF;
949
950 EXCEPTION
951 WHEN FND_API.G_EXC_ERROR THEN
952 x_return_status := FND_API.G_RET_STS_ERROR;
953 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
954 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
955 oe_msg_pub.Add_Exc_Msg
956 (G_PKG_NAME
957 ,l_api_name);
958 END IF;
959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
960 WHEN OTHERS THEN
961 IF l_debug_level > 0 THEN
962 oe_debug_pub.add( 'UNEXPECTED ERROR IN ' || G_PKG_NAME || ':' || L_API_NAME ) ;
963 END IF;
964 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
965 oe_msg_pub.Add_Exc_Msg
966 (G_PKG_NAME
967 ,l_api_name);
968 END IF;
969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
970
971 END check_constraints;
972
973 PROCEDURE update_service
974 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
975 , p_old_line_rec IN OE_Order_PUB.Line_Rec_Type := OE_Order_PUB.G_MISS_LINE_REC
976 , x_return_status OUT NOCOPY VARCHAR2
977
978 )
979 IS
980 l_api_name VARCHAR2(30):= 'Update_Service';
981 l_line_id NUMBER;
982 l_ordered_quantity NUMBER;
983 l_service_quantity NUMBER;
984 l_return_status VARCHAR2(30);
985 x_msg_count NUMBER;
986 x_msg_data VARCHAR2(250);
987
988 /* lchen add l_change_reason, l_change_comments to fix bug 1879607 */
989 l_change_reason VARCHAR2(30);
990 l_change_comments VARCHAR2(2000);
991
992 CURSOR get_service IS
993 SELECT LINE_ID, ORDERED_QUANTITY
994 FROM oe_order_lines
995 WHERE service_reference_line_id = p_line_rec.line_id
996 AND service_reference_type_code = 'ORDER' -- these two conditions added for bug 2946327
997 AND nvl(cancelled_flag, 'N') <> 'Y';
998
999 --
1000 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1001 --
1002 BEGIN
1003
1004 IF l_debug_level > 0 THEN
1005 oe_debug_pub.add( 'ENTERING OE_SALES_CAN_UTIL.UPDATE_SERVICE' ) ;
1006 END IF;
1007
1008 for Serrec in get_service loop
1009 l_line_id := serrec.line_id;
1010 l_service_quantity := serrec.ordered_quantity;
1011 l_ordered_quantity := p_line_rec.ordered_quantity;
1012
1013 /* lchen add l_change_reason, l_change_comments to fix bug 1879607 */
1014 l_change_reason := p_line_rec.change_reason;
1015 l_change_comments := p_line_rec.change_comments;
1016
1017 IF l_debug_level > 0 THEN
1018 oe_debug_pub.add( 'BEFORE CALLING UPDATELINE FOR LINE ID: '|| L_LINE_ID ) ;
1019 END IF;
1020
1021 /* Call the UpdateLine only if ordered_quantity is greater than zero */
1022 IF( l_service_quantity <> 0 )then
1023 Updateline(l_line_id,
1024 l_ordered_quantity,
1025 l_change_reason,
1026 l_change_comments,
1027 l_return_status,
1028 x_msg_count,
1029 x_msg_data);
1030 END IF;
1031
1032 IF l_debug_level > 0 THEN
1033 oe_debug_pub.add( 'AFTER CALLING UPDATELINE , RETURN STATUS: '|| L_RETURN_STATUS ) ;
1034 END IF;
1035
1036 end loop;
1037
1038 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1039 if l_return_status = FND_API.G_RET_STS_ERROR then
1040 raise FND_API.G_EXC_ERROR;
1041 else
1042 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1043 end if;
1044 end if;
1045
1046 IF l_debug_level > 0 THEN
1047 oe_debug_pub.add( 'EXITING OE_SALES_CAN_UTIL.UPDATE_SERVICE' ) ;
1048 END IF;
1049
1050 EXCEPTION
1051 WHEN FND_API.G_EXC_ERROR THEN
1052 x_return_status := FND_API.G_RET_STS_ERROR;
1053 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1054 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1055 oe_msg_pub.Add_Exc_Msg
1056 (G_PKG_NAME
1057 ,l_api_name);
1058 END IF;
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060 WHEN OTHERS THEN
1061 IF l_debug_level > 0 THEN
1062 oe_debug_pub.add( 'UNEXPECTED ERROR IN ' || G_PKG_NAME || ':' || L_API_NAME ) ;
1063 END IF;
1064 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1065 oe_msg_pub.Add_Exc_Msg
1066 (G_PKG_NAME
1067 ,l_api_name);
1068 END IF;
1069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1070 end update_service;
1071
1072 FUNCTION Cal_Cancelled_Qty
1073 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
1074 , p_old_line_rec IN OE_Order_PUB.Line_Rec_Type :=
1075 OE_Order_PUB.G_MISS_LINE_REC
1076 )Return Number
1077 IS
1078 l_ordered_quantity number;
1079 l_old_ord_quantity number;
1080 l_old_can_quantity number;
1081 l_new_can_quantity number;
1082 --
1083 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1084 --
1085 begin
1086 IF NOT OE_GLOBALS.Equal(p_line_rec.ordered_quantity,p_old_line_rec.ordered_quantity)
1087 THEN
1088 --IF (nvl(p_line_rec.line_category_code,' ') <> 'RETURN') THEN
1089
1090 /* Fix bug # 2136529: Get the old quantities from the database */
1091 select nvl(ordered_quantity, 0)
1092 , nvl(cancelled_quantity, 0)
1093 into l_old_ord_quantity
1094 , l_old_can_quantity
1095 from oe_order_lines
1096 where line_id = p_line_rec.line_id;
1097
1098 IF l_debug_level > 0 THEN
1099 oe_debug_pub.add( 'NEW ORDERED QUANTITY IS: '||P_LINE_REC.ORDERED_QUANTITY ) ;
1100 END IF;
1101 IF l_debug_level > 0 THEN
1102 oe_debug_pub.add( 'OLD ORDERED QUANTITY IS: '||L_OLD_ORD_QUANTITY ) ;
1103 END IF;
1104 IF l_debug_level > 0 THEN
1105 oe_debug_pub.add( 'OLD CANCELLED QUANTITY IS: '||L_OLD_CAN_QUANTITY ) ;
1106 END IF;
1107
1108 IF (l_old_ord_quantity > p_line_rec.ordered_quantity)
1109 and nvl(oe_globals.g_pricing_recursion, 'N') = 'N' THEN
1110 G_CANCELLATION_ACTION := 'C'; -- 12695580
1111 l_new_can_quantity := l_old_ord_quantity - p_line_rec.ordered_quantity
1112 + l_old_can_quantity;
1113 ELSE
1114 l_new_can_quantity := l_old_can_quantity;
1115 END IF;
1116
1117 /* Bug 12695580
1118 ** G_CANCELLATION_ACTION will be set to 'CR' from oe_config_pvt.handle_updates
1119 */
1120 IF G_CANCELLATION_ACTION = 'CR'
1121 AND nvl(oe_globals.g_pricing_recursion, 'N') = 'N' THEN
1122 l_new_can_quantity := l_old_ord_quantity - p_line_rec.ordered_quantity
1123 + l_old_can_quantity;
1124 END IF;
1125 -- End : 12695580
1126
1127 IF l_debug_level > 0 THEN
1128 oe_debug_pub.add( 'NEW CANCELLED QUANTITY IS: '||L_NEW_CAN_QUANTITY ) ;
1129 END IF;
1130
1131 RETURN l_new_can_quantity;
1132
1133 END IF;
1134 /*
1135 ** Fix # 3147694 Start
1136 ** Following will be true only if the user is cancelling the
1137 ** order right after quantity on the lines was updated to 0.
1138 ** Need to send the Cancelled Qty as Zero in such instances.
1139 */
1140 IF p_line_rec.ordered_quantity = 0 AND
1141 p_old_line_rec.ordered_quantity = 0 AND
1142 OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can THEN
1143
1144 RETURN 0;
1145
1146 IF l_debug_level > 0 THEN
1147 oe_debug_pub.add('Line Ord Qty already 0, Returning 0 as Cancelled Qty');
1148 END IF;
1149 END IF;
1150
1151 /* Following commented code has been replaced as fix for bug # 2136529
1152
1153 IF (p_old_line_rec.ordered_quantity > p_line_rec.ordered_quantity) THEN
1154 l_ordered_quantity :=
1155 (p_old_line_rec.ordered_quantity - p_line_rec.ordered_quantity);
1156
1157 IF p_old_line_rec.cancelled_quantity = FND_API.G_MISS_NUM OR
1158 p_old_line_rec.cancelled_quantity is NULL THEN
1159 oe_debug_pub.ADD('rajeevcancell');
1160 RETURN l_ordered_quantity;
1161 ELSE
1162 oe_debug_pub.ADD('rajeevcancel2');
1163 RETURN (l_ordered_quantity +
1164 p_old_line_rec.cancelled_quantity) ;
1165 END IF;
1166 ELSE
1167 oe_debug_pub.ADD('rajeevcancel3');
1168 RETURN p_line_rec.cancelled_quantity;
1169 END IF;
1170 END IF;
1171 ELSE
1172 IF (p_old_line_rec.ordered_quantity < p_line_rec.ordered_quantity) THEN
1173 l_ordered_quantity :=
1174 (p_old_line_rec.ordered_quantity - p_line_rec.ordered_quantity);
1175
1176 IF p_old_line_rec.cancelled_quantity = FND_API.G_MISS_NUM OR
1177 p_old_line_rec.cancelled_quantity is NULL THEN
1178 RETURN l_ordered_quantity;
1179 ELSE
1180 RETURN (l_ordered_quantity +
1181 p_old_line_rec.cancelled_quantity) ;
1182 END IF;
1183 ELSE
1184 RETURN p_line_rec.cancelled_quantity;
1185 END IF;
1186 oe_debug_pub.ADD('rajeevcancelsecond');
1187
1188 END IF;
1189 END IF;
1190 RETURN p_line_rec.cancelled_quantity;
1191 */
1192
1193 --Bug# 4009268
1194 IF OE_GLOBALS.Equal(p_line_rec.ordered_quantity,p_old_line_rec.ordered_quantity) THEN
1195 oe_debug_pub.add('New ordered_quantity and Old ordered_quantity are equal ');
1196 RETURN nvl(p_line_rec.cancelled_quantity,0);
1197 END IF;
1198
1199 IF l_debug_level > 0 THEN
1200 oe_debug_pub.add('Cal_Cancelled_qty - Returning 0 as Cancelled Qty at end ');
1201 END IF;
1202
1203 return 0; -- INVCONV
1204
1205 end Cal_Cancelled_qty;
1206
1207 PROCEDURE check_constraints
1208 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
1209 , p_old_header_rec IN OE_Order_PUB.header_Rec_Type:=
1210 OE_Order_PUB.G_MISS_header_REC
1211 , x_return_status OUT NOCOPY VARCHAR2
1212
1213 ) IS
1214 l_api_name CONSTANT VARCHAR2(30) := 'Check_Constraints';
1215 l_return_status varchar2(30) :=FND_API.G_RET_STS_SUCCESS;
1216 x_result NUMBER := 0 ;
1217 l_constrain_all_flag varchar2(255);
1218 l_constraint_id NUMBER;
1219 l_resolving_wf_activity_name varchar2(255);
1220 l_resolving_wf_item_type varchar2(255);
1221 l_exclude_flag varchar2(255);
1222 l_resolving_responsibility_id number;
1223 x_msg_count number;
1224 x_msg_data varchar2(255);
1225 l_on_operation_action varchar2(255);
1226 l_resp_id number := nvl(fnd_global.resp_id,-1);
1227 --
1228 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1229 --
1230 BEGIN
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add( 'IN CHECK CONSTRAINS FOR CANCELLATION' ) ;
1233 END IF;
1234 -- initialize API return status to success
1235 x_return_status := FND_API.G_RET_STS_SUCCESS;
1236 -- Prepare security record
1237
1238 EXCEPTION
1239 WHEN FND_API.G_EXC_ERROR THEN
1240 x_return_status := FND_API.G_RET_STS_ERROR;
1241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1242 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1243 oe_msg_pub.Add_Exc_Msg
1244 (G_PKG_NAME
1245 ,l_api_name);
1246 END IF;
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1248 WHEN OTHERS THEN
1249 IF l_debug_level > 0 THEN
1250 oe_debug_pub.add( 'UNEXPECTED ERROR IN ' || G_PKG_NAME || ':' || L_API_NAME ) ;
1251 END IF;
1252 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1253 oe_msg_pub.Add_Exc_Msg
1254 (G_PKG_NAME
1255 ,l_api_name);
1256 END IF;
1257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1258 end check_constraints;
1259
1260 PROCEDURE perform_cancel_order
1261 ( p_header_rec IN OE_Order_PUB.header_Rec_Type
1262 , p_old_header_rec IN OE_Order_PUB.header_Rec_Type := OE_Order_PUB.G_MISS_header_REC
1263 , x_return_status OUT NOCOPY VARCHAR2
1264
1265 )IS
1266 l_line_tbl OE_Order_PUB.line_tbl_type;
1267 l_line_old_tbl OE_Order_PUB.line_tbl_type;
1268 l_line_rec OE_Order_PUB.line_rec_type;
1269 l_msg_count NUMBER;
1270 l_msg_data VARCHAR2(240);
1271 l_entity_id NUMBER;
1272 l_result VARCHAR2(30);
1273 l_entity_code VARCHAR2(1) := 'O';
1274 l_line_process_name VARCHAR2(30);
1275 l_hold_release_rec oe_hold_sources_pvt.hold_release_rec;
1276 l_return_status VARCHAR2(30);
1277 l_result_out VARCHAR2(30);
1278 l_order_tbl OE_HOLDS_PVT.order_tbl_type;
1279 l_hold_id OE_HOLD_DEFINITIONS.HOLD_ID%TYPE;
1280 l_release_reason_code OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE;
1281 l_release_comment OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE;
1282
1283 l_service_parent_exists VARCHAR2(1) := 'N';
1284 l_x_line_old_tbl OE_Order_PUB.line_tbl_type;
1285 i NUMBER := 0;
1286 l_prg_line_count NUMBER;
1287
1288 --
1289 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1290 --
1291 Begin
1292
1293 IF l_debug_level > 0 THEN
1294 OE_DEBUG_PUB.ADD('Entering oe_sales_can_util.perform_cancel_order',5);
1295 OE_DEBUG_PUB.ADD('perform cancel order header id : '||p_header_rec.header_id,5);
1296 OE_DEBUG_PUB.add('Reason Required Flag : '||OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG,1);
1297 OE_DEBUG_PUB.add('Reason provided is : '||p_header_rec.change_reason,1);
1298 END IF;
1299
1300 --g_ord_lvl_can := TRUE; Commented for bug# 2922468
1301
1302 IF (oe_sales_can_util.G_REQUIRE_REASON AND OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG <> 'N') THEN
1303 IF l_debug_level > 0 THEN
1304 oe_debug_pub.add( 'CHANGE REASON IS REQUIRED' ) ;
1305 END IF;
1306 IF p_header_rec.change_reason IS NULL THEN
1307 fnd_message.set_name('ONT','OE_CAN_REASON_NOT');
1308 oe_msg_pub.add;
1309 raise FND_API.G_EXC_ERROR;
1310 END IF;
1311 END IF;
1312
1313 oe_line_util.Query_Rows( p_header_id => p_header_rec.header_id
1314 , x_line_tbl => l_line_old_tbl
1315 );
1316
1317 --l_line_tbl := l_line_old_tbl;
1318
1319 -- If the header is cancelled then the header level charges are deleted
1320 IF nvl(p_header_rec.cancelled_flag,'N') = 'Y' THEN
1321 OE_Header_Adj_Util.Delete_Header_Charges( p_header_id => p_header_rec.header_id );
1322 END IF;
1323
1324 For j in 1 .. l_line_old_tbl.count Loop
1325
1326 /* Fix for bug # 2104209 */
1327 IF nvl(l_line_old_tbl(j).cancelled_flag, 'N') <> 'Y' THEN
1328
1329 IF nvl(l_line_old_tbl(j).top_model_line_id, l_line_old_tbl(j).line_id) = l_line_old_tbl(j).line_id OR
1330 nvl(l_line_old_tbl(j).model_remnant_flag, 'N') = 'Y' THEN
1331
1332 IF l_debug_level > 0 THEN
1333 oe_debug_pub.add( 'LINE SELECTED FOR CANCELLATION , ID: '|| L_LINE_OLD_TBL ( J ) .LINE_ID , 1 ) ;
1334 END IF;
1335
1336 /* Fix for bug # 2126033 */
1337 IF l_line_old_tbl(j).item_type_code = OE_Globals.G_ITEM_SERVICE AND
1338 l_line_old_tbl(j).service_reference_type_code = 'ORDER' THEN
1339
1340 begin
1341 select 'Y'
1342 into l_service_parent_exists
1343 from oe_order_lines
1344 where header_id = l_line_old_tbl(j).header_id
1345 and line_id = l_line_old_tbl(j).service_reference_line_id;
1346 exception
1347 when no_data_found then
1348 l_service_parent_exists := 'N';
1349 when others then
1350 null;
1351 end;
1352
1353 IF l_service_parent_exists = 'Y' THEN
1354 IF l_debug_level > 0 THEN
1355 oe_debug_pub.add( 'SKIP , PARENT OF THIS SERVICE LINE EXISTS IN THE SAME ORDER' ) ;
1356 END IF;
1357 goto end_loop;
1358 END IF;
1359 END IF;
1360
1361 /* Fix for bug # 2387919 */
1362 begin
1363 select count(*)
1364 into l_prg_line_count
1365 from oe_price_adjustments opa1,
1366 oe_price_adjustments opa2,
1367 oe_price_adj_assocs opaa
1368 where opa1.list_line_type_code = 'PRG'
1369 and opa1.price_adjustment_id = opaa.price_adjustment_id
1370 and opa2.price_adjustment_id = opaa.rltd_price_adj_id
1371 and opa2.line_id = l_line_old_tbl(j).line_id;
1372 end;
1373
1374 IF l_prg_line_count > 0 THEN
1375 IF l_debug_level > 0 THEN
1376 oe_debug_pub.add( 'SKIP , THIS IS A PROMOTIONAL LINE' ) ;
1377 END IF;
1378 goto end_loop;
1379 END IF;
1380
1381 IF l_debug_level > 0 THEN
1382 oe_debug_pub.add( 'ADDING FOR CANCELLATION , LINE ID: '||L_LINE_OLD_TBL ( J ) .LINE_ID ) ;
1383 END IF;
1384
1385 i := i + 1;
1386
1387 l_line_tbl(i) := l_line_old_tbl(j);
1388 l_x_line_old_tbl(i) := l_line_old_tbl(j);
1389 l_line_tbl(i).db_flag := FND_API.G_TRUE;
1390 --l_line_tbl(i).cancelled_quantity := l_line_tbl(i).ordered_quantity;
1391 l_line_tbl(i).ordered_quantity :=0;
1392 l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
1393 l_line_tbl(i).change_reason :=p_header_rec.change_reason;
1394 l_line_tbl(i).change_comments :=p_header_Rec.change_comments;
1395 /*l_line_tbl(i).cancelled_flag := 'Y';
1396 l_line_tbl(i).flow_status_code := 'CANCELLED';
1397 l_line_tbl(i).open_flag := 'N'; */
1398 ELSE
1399 IF l_debug_level > 0 THEN
1400 oe_debug_pub.add( 'SKIPPING LINE ID: '||L_LINE_OLD_TBL ( J ) .LINE_ID||'-'|| L_LINE_OLD_TBL ( J ) .ITEM_TYPE_CODE ) ;
1401 END IF;
1402 END IF;
1403 ELSE
1404 IF l_debug_level > 0 THEN
1405 oe_debug_pub.add( 'SKIPPING CANCELLED LINE ID: '||L_LINE_OLD_TBL ( J ) .LINE_ID||'-'|| L_LINE_OLD_TBL ( J ) .ITEM_TYPE_CODE ) ;
1406 END IF;
1407 END IF; -- line not cancelled?
1408 <<end_loop>>
1409 null;
1410 End Loop;
1411
1412 IF l_line_tbl.count = 0 THEN
1413 oe_sales_can_util.g_order_cancel := TRUE;
1414 END IF;
1415
1416 -- Call PeformLinecancellation to cancel each line
1417 IF l_line_tbl.count > 0 THEN
1418 PerformLinecancellation(l_line_tbl,
1419 l_x_line_old_tbl,
1420 l_return_status);
1421 END IF;
1422
1423 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1424 IF l_debug_level > 0 THEN
1425 oe_debug_pub.add( 'UPDATE LINE PROCESS ORDER RETURN UNEXP_ERROR' ) ;
1426 END IF;
1427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1428 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1429 IF l_debug_level > 0 THEN
1430 oe_debug_pub.add( 'UPDATE LINE PROCESS ORDER RETURN RET_STS_ERROR' ) ;
1431 END IF;
1432 RAISE FND_API.G_EXC_ERROR;
1433 END IF;
1434
1435
1436 -- Need to process payment refund if there is any prepayment.
1437 IF OE_PREPAYMENT_UTIL.IS_MULTIPLE_PAYMENTS_ENABLED THEN
1438 IF l_debug_level > 0 THEN
1439 oe_debug_pub.ADD('Calling Payment Refund for multiple payments.');
1440 END IF;
1441
1442 OE_Prepayment_PVT.Process_Payment_Refund
1443 ( p_header_rec => p_header_rec
1444 , x_msg_count => l_msg_count
1445 , x_msg_data => l_msg_data
1446 , x_return_status => x_return_status
1447 );
1448 IF l_debug_level > 0 THEN
1449 oe_debug_pub.add('return status after calling process_payment_refund is: '||x_return_status, 3);
1450 END IF;
1451
1452 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1453 RAISE FND_API.G_EXC_ERROR;
1454 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1456 END IF;
1457 ELSIF NVL(p_header_rec.payment_type_code,'NULL') = 'CREDIT_CARD'
1458 AND OE_PrePayment_UTIL.is_prepaid_order(p_header_rec) = 'Y'
1459 THEN
1460 oe_debug_pub.ADD('Calling Payment Refund.');
1461 OE_PrePayment_PVT.Process_PrePayment_Order
1462 ( p_header_rec => p_header_rec
1463 , p_calling_action => NULL
1464 , p_delayed_request => FND_API.G_FALSE
1465 , x_msg_count => l_msg_count
1466 , x_msg_data => l_msg_data
1467 , x_return_status => x_return_status
1468 );
1469 IF l_debug_level > 0 THEN
1470 oe_debug_pub.add('return status after calling process_prepayment_order is: '||x_return_status, 3);
1471 END IF;
1472
1473 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1474 RAISE FND_API.G_EXC_ERROR;
1475 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1476 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1477 END IF;
1478 END IF;
1479
1480 -- Release Holds
1481
1482 IF l_debug_level > 0 THEN
1483 oe_debug_pub.add( 'CALLING RELEASE HOLD API RELEASE HOLD' ) ;
1484 END IF;
1485
1486 l_order_tbl(1).header_id := p_header_rec.header_id;
1487 l_release_reason_code := 'OM_CANCEL';
1488
1489 OE_Holds_pub.release_holds(
1490 p_order_tbl => l_order_tbl,
1491 p_release_reason_code => l_release_reason_code,
1492 p_release_comment => l_release_comment,
1493 p_hold_id => Null,
1494 x_return_status => l_return_status,
1495 x_msg_count => l_msg_count,
1496 x_msg_data => l_msg_data
1497 );
1498
1499 IF l_debug_level > 0 THEN
1500 oe_debug_pub.add( 'THE HOLD RELEASED WITH STATUS :'|| L_RETURN_STATUS ) ;
1501 END IF;
1502
1503 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1504 if l_return_status = FND_API.G_RET_STS_ERROR then
1505 raise FND_API.G_EXC_ERROR;
1506 else
1507 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1508 end if;
1509 end if;
1510
1511 /*
1512 ** Fix for 1967295:
1513 ** As VOID has been de-supported by iPayment, commenting
1514 ** following code which was used to VOID existing Trxns.
1515 **
1516 -- If Order has Credit Card approval, then Void the Authorization
1517 IF p_header_rec.credit_card_approval_code is NOT NULL THEN
1518
1519 -- Call Payment Request to Void any existing Authorizations
1520 oe_debug_pub.ADD('Before calling Payment Request');
1521
1522 OE_Verify_Payment_PUB.Payment_Request
1523 ( p_header_rec => p_header_rec
1524 , p_trxn_type => 'VOIDAUTHONLY'
1525 , p_msg_count => l_msg_count
1526 , p_msg_data => l_msg_data
1527 , p_result_out => l_result_out
1528 , p_return_status => l_return_status
1529 );
1530 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1531 RAISE FND_API.G_EXC_ERROR;
1532 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1534 END IF;
1535 END IF; -- IF Credit Card Approval Code is NOT NULL
1536 */
1537
1538
1539
1540 -- Abort the Wf and close the Header
1541 --OE_STANDARD_WF.Get_HdrProcessName(OE_GLOBALS.G_WFI_HDR, p_header_rec.header_id,
1542 --l_line_process_name);
1543
1544 -- Moved this post line process
1545 /*IF oe_sales_can_util.g_order_cancel THEN
1546 wf_engine.handleerror(OE_Globals.G_WFI_HDR
1547 ,to_char(p_header_rec.header_id)
1548 ,'CLOSE_HEADER',
1549 'SKIP','CANCEL');
1550 END IF; */
1551
1552 --g_ord_lvl_can := FALSE; Commented for bug# 2922468
1553
1554 IF l_debug_level > 0 THEN
1555 oe_debug_pub.add( 'EXITING OE_SALES_CAN_UTIL.PERFORM_CANCEL_ORDER' ) ;
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN FND_API.G_EXC_ERROR THEN
1560 --g_ord_lvl_can := FALSE; Commented for bug# 2922468
1561 x_return_status := FND_API.G_RET_STS_ERROR;
1562 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1563 --g_ord_lvl_can := FALSE; Commented for bug# 2922468
1564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1565 WHEN OTHERS THEN
1566 --g_ord_lvl_can := FALSE; Commented for bug# 2922468
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1569 oe_msg_pub.Add_Exc_Msg
1570 (G_PKG_NAME
1571 ,'Peform_Cancel_order');
1572 END IF;
1573
1574 End Perform_Cancel_order;
1575
1576 Procedure PerformLineCancellation(
1577 p_line_tbl IN OE_ORDER_PUB.LINE_TBL_TYPE
1578 , p_line_old_tbl IN OE_ORDER_PUB.LINE_TBL_TYPE
1579 , x_return_status OUT NOCOPY VARCHAR2)
1580
1581 IS
1582 l_control_rec OE_GLOBALS.Control_Rec_Type;
1583 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
1584 l_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
1585 l_api_name CONSTANT VARCHAR2(30) := 'PerformLineCancellation';
1586 x_msg_count NUMBER;
1587 x_msg_data VARCHAR2(30);
1588
1589 --
1590 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1591 --
1592 BEGIN
1593
1594 IF l_debug_level > 0 THEN
1595 oe_debug_pub.add( 'ENTERING OE_SALES_CAN_UTIL.PERFORMLINECANCELLATION' ) ;
1596 END IF;
1597
1598 l_control_rec.controlled_operation := TRUE;
1599 l_control_rec.change_attributes := TRUE;
1600 l_control_rec.validate_entity := TRUE;
1601 l_control_rec.write_to_DB := TRUE;
1602
1603 l_control_rec.default_attributes := FALSE;
1604 l_control_rec.process := TRUE;
1605 l_control_rec.process_partial := TRUE;
1606
1607 -- Instruct API to retain its caches
1608
1609 l_control_rec.clear_api_cache := FALSE;
1610 l_control_rec.clear_api_requests := FALSE;
1611
1612 l_line_tbl := p_line_tbl;
1613 l_old_line_tbl := p_line_old_tbl;
1614
1615 IF l_debug_level > 0 THEN
1616 oe_debug_pub.add( 'BEFORE CALLING PROCESS ORDER' ) ;
1617 END IF;
1618
1619 oe_order_pvt.Lines
1620 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
1621 , p_control_rec => l_control_rec
1622 , p_x_line_tbl => l_line_tbl
1623 , p_x_old_line_tbl => l_old_line_tbl
1624 , x_return_status => x_return_status
1625 );
1626
1627 IF l_debug_level > 0 THEN
1628 oe_debug_pub.add( 'AFTER CALLING PROCESS ORDER' ) ;
1629 END IF;
1630
1631 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1633 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1634 RAISE FND_API.G_EXC_ERROR;
1635 END IF;
1636
1637 -- Api to call OC and ACK and process delayed requests
1638 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL < '110508' THEN -- reinstated for 2653505
1639 OE_Order_PVT.Process_Requests_And_Notify
1640 ( p_process_requests => FALSE
1641 , p_notify => TRUE
1642 , x_return_status => x_return_status
1643 , p_line_tbl => l_line_tbl
1644 , p_old_line_tbl => l_old_line_tbl
1645 );
1646
1647 FOR I IN 1..l_line_tbl.COUNT LOOP
1648 if l_line_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
1649 x_return_status := FND_API.G_RET_STS_ERROR;
1650 elsif l_line_tbl(I).return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1652 end if;
1653 end loop;
1654 END IF; -- 2653505
1655
1656 IF l_debug_level > 0 THEN
1657 oe_debug_pub.add( 'EXITING OE_SALES_CAN_UTIL.PERFORMLINECANCELLATION' ) ;
1658 END IF;
1659
1660 EXCEPTION
1661 WHEN FND_API.G_EXC_ERROR THEN
1662 x_return_status := FND_API.G_RET_STS_ERROR;
1663 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1665 WHEN OTHERS THEN
1666 IF oe_msg_pub.Check_MSg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1667 oe_msg_pub.Add_Exc_Msg
1668 (G_PKG_NAME
1669 ,'PeformLineCancellation');
1670 END IF;
1671 x_return_status := FND_API.G_RET_STS_ERROR;
1672
1673 END PerformLineCancellation;
1674
1675 FUNCTION Query_Rows
1676 ( p_line_id IN NUMBER :=
1677 FND_API.G_MISS_NUM
1678 , p_header_id IN NUMBER :=
1679 FND_API.G_MISS_NUM
1680 ) RETURN OE_Order_PUB.Line_Tbl_Type
1681 IS
1682 l_line_rec OE_Order_PUB.Line_Rec_Type;
1683 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
1684
1685 --
1686 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1687 --
1688 BEGIN
1689
1690 IF l_debug_level > 0 THEN
1691 oe_debug_pub.add( 'ENTERING OE_LINE_UTIL.QUERY_ROWS' , 1 ) ;
1692 END IF;
1693
1694
1695 -- Return fetched table
1696 IF l_debug_level > 0 THEN
1697 oe_debug_pub.add( 'EXITING OE_LINE_UTIL.QUERY_ROWS' , 1 ) ;
1698 END IF;
1699
1700 RETURN l_line_tbl;
1701
1702 EXCEPTION
1703
1704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1705
1706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1707
1708 WHEN OTHERS THEN
1709
1710 IF oe_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1711 THEN
1712 oe_msg_pub.Add_Exc_Msg
1713 ( G_PKG_NAME
1714 , 'Query_Rows'
1715 );
1716 END IF;
1717
1718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1719
1720 END Query_Rows;
1721
1722 -- bug 4887806
1723 -- The Logic of creating this api was to see that while the
1724 -- remaining lines of an order are being cancelled, if there
1725 -- are any fulfillment sets which should not be awaiting for
1726 -- any other lines to reach fulfillment due to result of cancellation
1727 -- then those sets should be processed for fulfillment logic.
1728
1729 PROCEDURE Call_Process_Fulfillment(p_header_id IN NUMBER)
1730 IS
1731 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1732 l_result_out VARCHAR2(240);
1733 l_return_status VARCHAR2(30);
1734 l_activity_result VARCHAR2(30) := 'NO_RESULT';
1735 l_activity_status VARCHAR2(8);
1736 l_msg_data VARCHAR2(2000);
1737 l_item_key VARCHAR2(240);
1738 l_msg_count NUMBER;
1739 l_line_id NUMBER;
1740 l_activity_id NUMBER;
1741 l_lines_in_set NUMBER;
1742 l_lines_awaiting NUMBER;
1743
1744 -- Find all the non-closed fulfillment sets
1745 Cursor C_FulfillmentSets IS
1746 SELECT DISTINCT OS.SET_ID
1747 FROM oe_order_lines ol, oe_sets OS, oe_line_sets ols
1748 WHERE ol.header_id = p_header_id
1749 and OS.HEADER_ID = ol.header_id
1750 and ol.line_id = ols.line_id
1751 and ols.set_id = OS.set_id
1752 and OS.SET_TYPE = 'FULFILLMENT_SET'
1753 and OS.SET_STATUS in ('A','T')
1754 and ol.cancelled_flag <> 'Y'
1755 and ol.open_flag = 'Y';
1756
1757 BEGIN
1758 IF l_debug_level > 0 THEN
1759 oe_debug_pub.add('Entering Oe_sales_can_util.Call_Process_Fulfillment API',2);
1760 END IF;
1761
1762 SAVEPOINT Process_Set;
1763
1764 FOR C_Set in C_FulfillmentSets
1765 LOOP
1766
1767 -- Count the lines in this set
1768 SELECT count(1) INTO l_lines_in_set
1769 FROM oe_order_lines ol, oe_line_sets ols
1770 WHERE ol.header_id = p_header_id
1771 AND ols.set_id = C_Set.set_id
1772 AND ol.line_id = ols.line_id
1773 AND ol.cancelled_flag <> 'Y'
1774 AND ol.open_flag = 'Y';
1775
1776 IF l_debug_level > 0 THEN
1777 oe_debug_pub.add('Fulfillment Set_id='||C_Set.set_id||',No. of lines='||l_lines_in_set,2);
1778 END IF;
1779
1780 -- Count the lines that are awaiting fulfillment for this set
1781 IF l_lines_in_set > 0 THEN
1782 SELECT count(1) INTO l_lines_awaiting
1783 FROM oe_order_lines ol, oe_line_sets ols,
1784 wf_item_activity_statuses WIAS,
1785 wf_process_activities WPA
1786 WHERE ol.header_id = p_header_id
1787 AND ols.set_id = C_Set.set_id
1788 AND ols.line_id = ol.line_id
1789 AND WPA.activity_name = 'FULFILL_LINE'
1790 AND WIAS.item_type = 'OEOL'
1791 AND WIAS.item_key = to_char(ol.line_id)
1792 AND WIAS.activity_status = 'NOTIFIED'
1793 AND WIAS.Process_Activity = WPA.instance_id;
1794
1795 IF l_debug_level > 0 THEN
1796 oe_debug_pub.add('No. of lines in Fulfill:NOTIFIED ='||l_lines_awaiting,2);
1797 END IF;
1798
1799 IF l_lines_awaiting = l_lines_in_set THEN
1800 -- This is to pick up 1 line in the set in Fulfill:Notified
1801 SELECT WIAS.Process_Activity, ol.line_id
1802 INTO l_activity_id, l_line_id
1803 FROM oe_order_lines ol, oe_line_sets ols,
1804 wf_item_activity_statuses WIAS,
1805 wf_process_activities WPA
1806 WHERE ol.header_id = p_header_id
1807 AND ols.set_id = C_Set.set_id
1808 AND ols.line_id = ol.line_id
1809 AND WPA.activity_name = 'FULFILL_LINE'
1810 AND WIAS.item_type = 'OEOL'
1811 AND WIAS.item_key = to_char(ol.line_id)
1812 AND WIAS.activity_status = 'NOTIFIED'
1813 AND WIAS.Process_Activity = WPA.instance_id
1814 AND ROWNUM = 1;
1815
1816 IF l_debug_level > 0 THEN
1817 oe_debug_pub.add('All lines in fulfill:notified, hence calling OE_Line_Fullfill.Process_Fulfillment for line_id='||l_line_id||' , p_activity_id='||l_activity_id,2);
1818 END IF;
1819
1820 OE_Line_Fullfill.Process_Fulfillment
1821 ( p_api_version_number => 1.0
1822 , p_line_id => l_line_id
1823 , p_activity_id => l_activity_id
1824 , x_result_out => l_result_out
1825 , x_return_status => l_return_status
1826 , x_msg_count => l_msg_count
1827 , x_msg_data => l_msg_data);
1828
1829 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1830 IF l_debug_level > 0 THEN
1831 oe_debug_pub.add('Exception from Process_Fulfillment:'||l_result_out,2);
1832 END IF;
1833 OE_MSG_PUB.Add_Text(p_message_text => l_msg_data);
1834 OE_MSG_PUB.Save_API_Messages;
1835
1836 ROLLBACK TO Process_Set;
1837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1838
1839 ELSE -- fulfillment of all related lines was successful
1840 l_item_key := to_char(l_line_id);
1841 Oe_line_fullfill.Get_Activity_Result(
1842 p_item_type => OE_GLOBALS.G_WFI_LIN
1843 ,p_item_key => l_item_key
1844 ,p_activity_name => 'FULFILL_LINE'
1845 ,x_return_status => l_return_status
1846 ,x_activity_result => l_activity_result
1847 ,x_activity_status_code => l_activity_status
1848 ,x_activity_id => l_activity_id );
1849
1850 IF l_debug_level > 0 THEN
1851 oe_debug_pub.add('After Get_Activity_Result, ret status:'||l_return_status||' ,Fulfill:'||l_activity_status,3);
1852 END IF;
1853
1854 IF l_return_status = FND_API.G_RET_STS_SUCCESS AND
1855 l_activity_status = 'NOTIFIED' THEN
1856 IF l_debug_level > 0 THEN
1857 oe_debug_pub.add('Calling wf_engine.CompleteActivityInternalName for lineID='||l_item_key,2);
1858 END IF;
1859
1860 Wf_Engine.CompleteActivityInternalName('OEOL', l_item_key, 'FULFILL_LINE', '#NULL',TRUE ); --15870313
1861
1862 IF l_debug_level > 0 THEN
1863 oe_debug_pub.add('After calling wf_engine.CompleteActivityInternalName for lineID='||l_item_key,2);
1864 END IF;
1865 END IF;
1866 END IF;
1867 END IF; -- l_lines_awaiting = l_lines_in_set check
1868 END IF; -- l_lines_in_set > 0 check
1869 -- re-initializing the variables to NULL
1870 l_lines_awaiting := NULL;
1871 l_lines_in_set := NULL;
1872 END LOOP; -- Non closed fulfillment sets
1873
1874 IF l_debug_level > 0 THEN
1875 oe_debug_pub.add('Exiting from Oe_sales_can_util.Call_Process_Fulfillment API',2);
1876 END IF;
1877 EXCEPTION
1878 WHEN Others THEN
1879 IF l_debug_level > 0 THEN
1880 oe_debug_pub.add('OTHERS EXCEPTION in Oe_sales_can_util.Call_Process_Fulfillment API',2);
1881 END IF;
1882 END Call_Process_Fulfillment;
1883
1884 PROCEDURE Cancel_Remaining_Order
1885 ( p_header_Rec IN OE_Order_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC,
1886 p_header_id IN NUMBER := FND_API.G_MISS_NUM
1887 , x_return_status OUT NOCOPY VARCHAR2
1888
1889 )
1890 IS
1891 l_line_tbl OE_Order_PUB.line_tbl_type;
1892 l_line_old_tbl OE_Order_PUB.line_tbl_type;
1893 l_x_line_old_tbl OE_Order_PUB.line_tbl_type;
1894 l_line_rec OE_Order_PUB.line_rec_type;
1895 l_header_rec OE_Order_PUB.header_rec_type;
1896 l_msg_count NUMBER;
1897 l_msg_data VARCHAR2(240);
1898 l_entity_id NUMBER;
1899 l_result VARCHAR2(30);
1900 l_entity_code VARCHAR2(1) := 'O';
1901 l_line_process_name VARCHAR2(30);
1902 l_hold_release_rec oe_hold_sources_pvt.hold_release_rec;
1903 l_return_status VARCHAR2(30);
1904 l_header_id NUMBER;
1905 J NUMBER := 0;
1906 lfullfillqty NUMBER;
1907
1908 l_service_parent_exists VARCHAR2(1) := 'N';
1909 l_prg_line_count NUMBER;
1910
1911 --
1912 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1913 --
1914 Begin
1915
1916 IF l_debug_level > 0 THEN
1917 oe_debug_pub.add( 'ENTERING OE_SALES_CAN_UTIL.CANCEL_REMAINING_ORDER' ) ;
1918 END IF;
1919
1920 x_return_status := FND_API.G_RET_STS_SUCCESS;
1921
1922 g_par_ord_lvl_can := TRUE; --Fix for bug 2230777
1923
1924 l_line_tbl := OE_ORDER_PUB.G_MISS_LINE_TBL;
1925
1926 IF (p_header_rec.header_id = FND_API.G_MISS_NUM OR
1927 p_header_rec.header_id = NULL) THEN
1928 l_header_id := p_header_id;
1929 ELSE
1930 l_header_id := p_header_rec.header_id;
1931 END IF;
1932
1933 IF l_debug_level > 0 THEN
1934 oe_debug_pub.add( 'CANCEL REMAINING ORDER HEADER ID: '||L_HEADER_ID ) ;
1935 END IF;
1936
1937 oe_line_util.Query_Rows( p_header_id => l_header_id
1938 , x_line_tbl => l_line_old_tbl
1939 );
1940
1941 oe_header_util.Query_Row( p_header_id => l_header_id
1942 , x_header_rec => l_header_rec
1943 );
1944
1945 --l_line_tbl := l_line_old_tbl;
1946
1947 For I in 1 .. l_line_old_tbl.count Loop
1948
1949 /*IF l_line_old_tbl(i).line_category_code = 'RETURN' THEN
1950 lfullfillqty := NULL;
1951 ELSE
1952 lfullfillqty := l_line_old_tbl(i).shipped_quantity;
1953 END IF;*/
1954
1955 IF Nvl(l_line_old_tbl(i).shipped_quantity,0) = 0 AND
1956 Nvl(l_line_old_tbl(i).cancelled_flag,'N') <> 'Y' AND
1957 Nvl(l_line_old_tbl(i).ordered_quantity,0) <> 0 AND
1958 Nvl(l_line_old_tbl(i).open_flag,'N') <> 'N' THEN
1959
1960 /* Following IF condition added to fix 2112435 */
1961 IF nvl(l_line_old_tbl(i).top_model_line_id, l_line_old_tbl(i).line_id) = l_line_old_tbl(i).line_id OR
1962 nvl(l_line_old_tbl(i).model_remnant_flag, 'N') = 'Y' THEN
1963
1964 IF l_debug_level > 0 THEN
1965 oe_debug_pub.add( 'LINE SELECTED FOR CANCELLATION , ID: '|| L_LINE_OLD_TBL ( I ) .LINE_ID , 1 ) ;
1966 END IF;
1967
1968 /* Fix for bug # 2126033 */
1969 IF l_line_old_tbl(i).item_type_code = OE_Globals.G_ITEM_SERVICE AND
1970 l_line_old_tbl(i).service_reference_type_code = 'ORDER' THEN
1971
1972 begin
1973 select 'Y'
1974 into l_service_parent_exists
1975 from oe_order_lines
1976 where header_id = l_line_old_tbl(i).header_id
1977 and line_id = l_line_old_tbl(i).service_reference_line_id;
1978 exception
1979 when no_data_found then
1980 l_service_parent_exists := 'N';
1981 when others then
1982 null;
1983 end;
1984
1985 IF l_service_parent_exists = 'Y' THEN
1986 IF l_debug_level > 0 THEN
1987 oe_debug_pub.add( 'SKIP , PARENT OF THIS SERVICE LINE EXISTS IN THE SAME ORDER' ) ;
1988 END IF;
1989 goto end_loop;
1990 END IF;
1991 END IF;
1992
1993 /* Fix for bug # 2387919 */
1994 begin
1995 select count(*)
1996 into l_prg_line_count
1997 from oe_price_adjustments opa1,
1998 oe_price_adjustments opa2,
1999 oe_price_adj_assocs opaa,
2000 oe_order_lines_all ol --bug 4156493
2001 where opa1.list_line_type_code = 'PRG'
2002 and opa1.price_adjustment_id = opaa.price_adjustment_id
2003 and opa2.price_adjustment_id = opaa.rltd_price_adj_id
2004 and opa2.line_id = l_line_old_tbl(i).line_id
2005 --bug 4156493
2006 and opa1.line_id = ol.line_id
2007 and ol.shipped_quantity is null
2008 AND Nvl(ol.open_flag,'N') = 'Y'; -- bug 13739573
2009 end;
2010
2011 IF l_prg_line_count > 0 THEN
2012 IF l_debug_level > 0 THEN
2013 oe_debug_pub.add( 'SKIP , THIS IS A PROMOTIONAL GOODS LINE' ) ;
2014 END IF;
2015 goto end_loop;
2016 END IF;
2017
2018 IF l_debug_level > 0 THEN
2019 oe_debug_pub.add( 'ADDING FOR CANCELLATION , LINE ID: '|| L_LINE_OLD_TBL ( I ) .LINE_ID ) ;
2020 END IF;
2021
2022 J := J + 1;
2023
2024 l_line_tbl(J) := l_line_old_tbl(I);
2025 l_x_line_old_tbl(J) := l_line_old_tbl(I);
2026 l_line_tbl(J).db_flag := FND_API.G_TRUE;
2027 --l_line_tbl(J).cancelled_quantity := l_line_tbl(J).ordered_quantity;
2028 l_line_tbl(j).ordered_quantity :=0;
2029 l_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
2030 l_line_tbl(j).change_reason :=p_header_rec.change_reason;
2031 l_line_tbl(j).change_comments :=p_header_Rec.change_comments;
2032 /*
2033 ** Fix bug # 2660104:
2034 ** Following column will be updated in Check_Constraints()
2035 ** procedure if line reaches the cancellation point.
2036 l_line_tbl(j).cancelled_flag := 'Y';
2037 */
2038 /*
2039 ** Fix Bug # 2238002:
2040 ** Following columns are updated when WF Handle error is called.
2041 */
2042 --l_line_tbl(j).flow_status_code := 'CANCELLED';
2043 --l_line_tbl(j).open_flag := 'N';
2044 ELSE
2045 IF l_debug_level > 0 THEN
2046 oe_debug_pub.add( 'SKIPPING LINE ID: '||L_LINE_OLD_TBL ( I ) .LINE_ID||'-'|| L_LINE_OLD_TBL ( I ) .ITEM_TYPE_CODE ) ;
2047 END IF;
2048 END IF;
2049 ELSE
2050 IF l_debug_level > 0 THEN
2051 oe_debug_pub.add( 'SKIPPING ALREADY SHIPPED OR CLOSED/CANCELLED LINE ID: '||L_LINE_OLD_TBL ( I ) .LINE_ID ) ;
2052 END IF;
2053 END If;
2054 <<end_loop>>
2055 null;
2056 End Loop;
2057
2058 IF l_debug_level > 0 THEN
2059 oe_debug_pub.add( 'CANCEL REMAINING ORDER - LINE COUNT '|| L_LINE_TBL.COUNT ) ;
2060 END IF;
2061
2062 IF l_line_tbl.count = 0 THEN
2063 FND_MESSAGE.SET_NAME('ONT','OE_NO_ELIGIBLE_LINES');
2064 FND_MESSAGE.SET_TOKEN('ORDER', L_header_rec.Order_Number);
2065 OE_MSG_PUB.ADD;
2066 RAISE FND_API.G_EXC_ERROR;
2067 END IF;
2068
2069 IF l_debug_level > 0 THEN
2070 oe_debug_pub.add( 'BEFORE CALLING PERFORM LINE CANCELLATION' ) ;
2071 END IF;
2072
2073 -- Call PeformLinecancellation to cancel each line
2074 PerformLinecancellation(l_line_tbl,
2075 l_x_line_old_tbl,
2076 x_return_status => l_return_status);
2077
2078 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2079 IF l_debug_level > 0 THEN
2080 oe_debug_pub.add( 'PERFORM LINE CANCELLATION UNEXP_ERROR' ) ;
2081 END IF;
2082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2083 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2084 IF l_debug_level > 0 THEN
2085 oe_debug_pub.add( 'PERFORM LINE CANCELLATION RET_STS_ERROR' ) ;
2086 END IF;
2087 RAISE FND_API.G_EXC_ERROR;
2088 END IF;
2089
2090 g_par_ord_lvl_can := FALSE;
2091
2092 --added for bug 4567339
2093 OE_Order_PVT.Process_Requests_And_Notify
2094 ( p_process_requests => TRUE
2095 , p_notify => TRUE
2096 , x_return_status => l_return_status
2097 , p_line_tbl => l_line_tbl
2098 , p_old_line_tbl => l_x_line_old_tbl
2099 );
2100 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2101 IF l_debug_level > 0 THEN
2102 oe_debug_pub.add( 'Process_requests_and_notify UNEXP_ERROR' ) ;
2103 END IF;
2104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2105 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2106 IF l_debug_level > 0 THEN
2107 oe_debug_pub.add( 'Process_requests_and_notify RET_STS_ERROR' ) ;
2108 END IF;
2109 RAISE FND_API.G_EXC_ERROR;
2110 END IF;
2111 --end bug 4567339
2112
2113 -- bug 4887806
2114 OE_SALES_CAN_UTIL.Call_Process_Fulfillment(p_header_id => p_header_id);
2115
2116 IF l_debug_level > 0 THEN
2117 oe_debug_pub.add( 'EXITING OE_SALES_CAN_UTIL.CANCEL_REMAINING_ORDER' ) ;
2118 END IF;
2119
2120 EXCEPTION
2121 WHEN FND_API.G_EXC_ERROR THEN
2122 g_par_ord_lvl_can := FALSE;
2123 x_return_status := FND_API.G_RET_STS_ERROR;
2124 WHEN OTHERS THEN
2125 g_par_ord_lvl_can := FALSE;
2126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2127 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2128 THEN
2129 OE_MSG_PUB.Add_Exc_Msg
2130 ( G_PKG_NAME ,
2131 'Cancel Remaining Order'
2132 );
2133 END IF;
2134 End Cancel_Remaining_Order;
2135
2136 PROCEDURE Cancel_Wf
2137 (
2138 x_return_status OUT NOCOPY varchar2
2139 , x_request_rec IN OUT NOCOPY OE_Order_PUB.Request_Rec_Type
2140 )
2141 IS
2142 l_Ordered_Quantity number ;
2143 Cursor C1 IS
2144 select ordered_quantity from
2145 oe_order_lines_all
2146 where line_id = x_request_rec.entity_id;
2147
2148 Cursor C2 IS
2149 select ordered_quantity from
2150 oe_order_lines_all
2151 where header_id = x_request_rec.entity_id
2152 and ordered_quantity <> 0;
2153 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2154
2155 BEGIN
2156 if l_debug_level > 0 then
2157 OE_DEBUG_PUB.ADD('Enter Cancel Workflow API - OEXUCANB ');
2158 OE_DEBUG_PUB.ADD('Entity:' || x_request_rec.param1 );
2159 OE_DEBUG_PUB.ADD('Entity id:' || x_request_rec.entity_id );
2160 end if;
2161
2162 IF x_request_rec.param1 = OE_GLOBALS.G_ENTITY_HEADER
2163 THEN
2164
2165 if l_debug_level > 0 then
2166 OE_DEBUG_PUB.ADD('Before select ');
2167 end if;
2168 OPEN C2;
2169 FETCH C2 INTO l_ordered_quantity;
2170 CLOSE C2;
2171 IF l_ordered_quantity IS NULL THEN
2172 if l_debug_level > 0 then
2173 OE_DEBUG_PUB.ADD('Before cancelling header flow ');
2174 end if;
2175
2176 wf_engine.handleerror(OE_Globals.G_WFI_HDR
2177 ,to_char(x_request_rec.entity_id)
2178 ,'CLOSE_HEADER',
2179 'RETRY','CANCEL');
2180
2181 END IF; -- Ordered quantity
2182
2183 if l_debug_level > 0 then
2184 OE_DEBUG_PUB.ADD('after cancelling header flow ');
2185 end if;
2186
2187 ELSIF x_request_rec.param1 = OE_GLOBALS.G_ENTITY_LINE
2188 THEN
2189
2190 if l_debug_level > 0 then
2191 OE_DEBUG_PUB.ADD('before select Cursoer C1 ');
2192 end if;
2193 OPEN C1;
2194 FETCH C1 INTO l_ordered_quantity;
2195 CLOSE C1;
2196 IF l_ordered_quantity = 0 THEN
2197 if l_debug_level > 0 then
2198 OE_DEBUG_PUB.ADD('Before cancelling line flow ');
2199 end if;
2200 Update oe_order_lines_all
2201 set cancelled_flag = 'Y' where
2202 line_id = x_request_rec.entity_id;
2203
2204 wf_engine.handleerror(OE_Globals.G_WFI_LIN
2205 ,to_char(x_request_rec.entity_id)
2206 ,'CLOSE_LINE',
2207 'RETRY','CANCEL');
2208 End if; -- Ordered quantity
2209
2210 if l_debug_level > 0 then
2211 OE_DEBUG_PUB.ADD('after cancelling line flow ');
2212 end if;
2213
2214 END IF;
2215
2216 EXCEPTION
2217
2218 WHEN FND_API.G_EXC_ERROR THEN
2219
2220 x_return_status := FND_API.G_RET_STS_ERROR;
2221
2222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2223
2224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2225
2226 WHEN OTHERS THEN
2227
2228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2229
2230 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2231 THEN
2232 OE_MSG_PUB.Add_Exc_Msg
2233 ( G_PKG_NAME
2234 , 'CANCEL_WF' );
2235 END IF;
2236
2237 END CANCEL_WF ;
2238
2239
2240 -- INVCONV OPM Inventory convergence
2241 FUNCTION Cal_Cancelled_Qty2
2242 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
2243 , p_old_line_rec IN OE_Order_PUB.Line_Rec_Type :=
2244 OE_Order_PUB.G_MISS_LINE_REC
2245 )Return Number
2246 IS
2247 l_ordered_quantity2 number;
2248 l_old_ord_quantity2 number;
2249 l_old_can_quantity2 number;
2250 l_new_can_quantity2 number;
2251 --
2252 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2253 --
2254 begin
2255
2256 IF l_debug_level > 0 THEN
2257 oe_debug_pub.add( 'entering Cal_Cancelled_Qty2') ;
2258 END IF;
2259 IF NOT OE_GLOBALS.Equal(p_line_rec.ordered_quantity2,p_old_line_rec.ordered_quantity2)
2260 THEN
2261 IF l_debug_level > 0 THEN
2262 oe_debug_pub.add( 'in Cal_Cancelled_Qty2 1 ') ;
2263 END IF;
2264
2265 --IF (nvl(p_line_rec.line_category_code,' ') <> 'RETURN') THEN
2266
2267 /* Fix bug # 2136529: Get the old quantities from the database */
2268 select nvl(ordered_quantity2, 0)
2269 , nvl(cancelled_quantity2, 0)
2270 into l_old_ord_quantity2
2271 , l_old_can_quantity2
2272 from oe_order_lines
2273 where line_id = p_line_rec.line_id;
2274
2275 IF l_debug_level > 0 THEN
2276 oe_debug_pub.add( 'NEW ORDERED QUANTITY2 IS: '||P_LINE_REC.ORDERED_QUANTITY2 ) ;
2277 END IF;
2278 IF l_debug_level > 0 THEN
2279 oe_debug_pub.add( 'OLD ORDERED QUANTITY2 IS: '||L_OLD_ORD_QUANTITY2 ) ;
2280 END IF;
2281 IF l_debug_level > 0 THEN
2282 oe_debug_pub.add( 'OLD CANCELLED QUANTITY2 IS: '||L_OLD_CAN_QUANTITY2 ) ;
2283 END IF;
2284
2285 IF (l_old_ord_quantity2 > p_line_rec.ordered_quantity2)
2286 and nvl(oe_globals.g_pricing_recursion, 'N') = 'N' THEN
2287 l_new_can_quantity2 := l_old_ord_quantity2 - p_line_rec.ordered_quantity2
2288 + l_old_can_quantity2;
2289 ELSE
2290 l_new_can_quantity2 := l_old_can_quantity2;
2291 END IF;
2292
2293 IF l_debug_level > 0 THEN
2294 oe_debug_pub.add( 'NEW CANCELLED QUANTITY2 IS: '||L_NEW_CAN_QUANTITY2 ) ;
2295 END IF;
2296
2297 RETURN l_new_can_quantity2;
2298
2299 END IF;
2300 /*
2301 ** Fix # 3147694 Start
2302 ** Following will be true only if the user is cancelling the
2303 ** order right after quantity on the lines was updated to 0.
2304 ** Need to send the Cancelled Qty as Zero in such instances.
2305 */
2306 IF l_debug_level > 0 THEN
2307 oe_debug_pub.add( 'in Cal_Cancelled_Qty2 2 ') ;
2308 END IF;
2309
2310 IF p_line_rec.ordered_quantity2 = 0 AND
2311 p_old_line_rec.ordered_quantity2 = 0 AND
2312 OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can THEN
2313
2314 RETURN 0;
2315
2316 IF l_debug_level > 0 THEN
2317 oe_debug_pub.add('Line Ord Qty2 already 0, Returning 0 as Cancelled Qty2');
2318 END IF;
2319 END IF;
2320
2321 IF l_debug_level > 0 THEN
2322 oe_debug_pub.add( 'in Cal_Cancelled_Qty2 3 ') ;
2323 END IF;
2324
2325 /* Following commented code has been replaced as fix for bug # 2136529
2326
2327 IF (p_old_line_rec.ordered_quantity > p_line_rec.ordered_quantity) THEN
2328 l_ordered_quantity :=
2329 (p_old_line_rec.ordered_quantity - p_line_rec.ordered_quantity);
2330
2331 IF p_old_line_rec.cancelled_quantity = FND_API.G_MISS_NUM OR
2332 p_old_line_rec.cancelled_quantity is NULL THEN
2333 oe_debug_pub.ADD('rajeevcancell');
2334 RETURN l_ordered_quantity;
2335 ELSE
2336 oe_debug_pub.ADD('rajeevcancel2');
2337 RETURN (l_ordered_quantity +
2338 p_old_line_rec.cancelled_quantity) ;
2339 END IF;
2340 ELSE
2341 oe_debug_pub.ADD('rajeevcancel3');
2342 RETURN p_line_rec.cancelled_quantity;
2343 END IF;
2344 END IF;
2345 ELSE
2346 IF (p_old_line_rec.ordered_quantity < p_line_rec.ordered_quantity) THEN
2347 l_ordered_quantity :=
2348 (p_old_line_rec.ordered_quantity - p_line_rec.ordered_quantity);
2349
2350 IF p_old_line_rec.cancelled_quantity = FND_API.G_MISS_NUM OR
2351 p_old_line_rec.cancelled_quantity is NULL THEN
2352 RETURN l_ordered_quantity;
2353 ELSE
2354 RETURN (l_ordered_quantity +
2355 p_old_line_rec.cancelled_quantity) ;
2356 END IF;
2357 ELSE
2358 RETURN p_line_rec.cancelled_quantity;
2359 END IF;
2360 oe_debug_pub.ADD('rajeevcancelsecond');
2361
2362 END IF;
2363 END IF;
2364 RETURN p_line_rec.cancelled_quantity;
2365 */
2366
2367 --Bug# 4009268
2368 IF OE_GLOBALS.Equal(p_line_rec.ordered_quantity2,p_old_line_rec.ordered_quantity2) THEN
2369 oe_debug_pub.add('New ordered_quantity2 and Old ordered_quantity2 are equal ');
2370 RETURN p_line_rec.cancelled_quantity2;
2371 END IF;
2372
2373 IF l_debug_level > 0 THEN
2374 oe_debug_pub.add( 'in Cal_Cancelled_Qty2 4 ') ;
2375 END IF;
2376 RETURN 0;
2377
2378 end Cal_Cancelled_qty2;
2379
2380
2381 END OE_SALES_CAN_UTIL;