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