[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_CLOSE_UTIL
Source
1 PACKAGE BODY OE_ORDER_CLOSE_UTIL AS
2 /* $Header: OEXUCLOB.pls 120.15.12020000.3 2013/01/21 06:09:19 skurella ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_ORDER_CLOSE_UTIL';
6
7 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
8
9 DELETE_ADJUSTMENTS purges the the unapplied adjustments from oe table
10
11 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
12
13 PROCEDURE DELETE_ADJUSTMENTS (
14 p_header_id IN NUMBER DEFAULT NULL
15 ,p_line_id IN NUMBER DEFAULT NULL
16 )
17 IS
18 l_adjustment_id NUMBER;
19
20 --bug4099565
21 -- Adding the condition retrobill_request_id IS NULL in both the cursors to prevent the deletion of manual modifier record corresponding to the latest price in the set up in the case of retrobill lines.
22
23 CURSOR c_adjustment_header IS
24 SELECT price_adjustment_id
25 FROM oe_price_adjustments
26 WHERE header_id = p_header_id
27 AND list_line_type_code<>'TAX'
28 AND automatic_flag = 'N'
29 AND applied_flag = 'N'
30 AND retrobill_request_id IS NULL; --bug4099565
31
32 /* Added a condition to avoid deleting unapplied Price Break Child line adjustments
33 from oe_price_adjustments for bug 2516895 */
34 CURSOR c_adjustment_line IS
35 SELECT price_adjustment_id
36 FROM oe_price_adjustments adj
37 WHERE line_id = p_line_id
38 AND list_line_type_code<>'TAX'
39 AND automatic_flag = 'N'
40 AND applied_flag = 'N'
41 AND retrobill_request_id IS NULL --bug4099565
42 AND not exists( select 1 from oe_price_adj_assocs
43 where rltd_price_adj_id = adj.price_adjustment_id);
44
45 --
46 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
47 --
48 BEGIN
49
50 IF l_debug_level > 0 THEN
51 oe_debug_pub.add('ENTERING DELETE_ADJUSTMENTS');
52 END IF;
53
54 IF p_header_id IS NOT NULL THEN
55 OPEN c_adjustment_header;
56 LOOP
57 BEGIN
58 FETCH c_adjustment_header INTO l_adjustment_id;
59 EXIT WHEN c_adjustment_header%NOTFOUND;
60 --
61 DELETE FROM oe_price_adj_assocs
62 WHERE price_adjustment_id = l_adjustment_id;
63 --
64 DELETE FROM oe_price_adj_attribs
65 WHERE price_adjustment_id = l_adjustment_id;
66 --
67 DELETE FROM oe_price_adjustments
68 WHERE price_adjustment_id = l_adjustment_id;
69 EXCEPTION
70 WHEN OTHERS THEN
71 IF l_debug_level > 0 THEN
72 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
73 END IF;
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75 END;
76 END LOOP;
77 --
78 CLOSE c_adjustment_header;
79 ELSE
80 IF p_line_id IS NOT NULL THEN
81 OPEN c_adjustment_line;
82 LOOP
83 BEGIN
84 FETCH c_adjustment_line INTO l_adjustment_id;
85 EXIT WHEN c_adjustment_line%NOTFOUND;
86 --
87 DELETE FROM oe_price_adj_assocs
88 WHERE price_adjustment_id = l_adjustment_id;
89 --
90 DELETE FROM oe_price_adj_attribs
91 WHERE price_adjustment_id = l_adjustment_id;
92 --
93 DELETE FROM oe_price_adjustments
94 WHERE price_adjustment_id = l_adjustment_id;
95 EXCEPTION
96 WHEN OTHERS THEN
97 IF l_debug_level > 0 THEN
98 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
99 END IF;
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END;
102 --
103 END LOOP;
104 CLOSE c_adjustment_line;
105 --
106 ELSE
107 IF l_debug_level > 0 THEN
108 oe_debug_pub.add( 'ERROR: BOTH PARAMETERS ARE NULL IN DELETE_ADJUSTMENTS ' , 1 ) ;
109 END IF;
110 RETURN;
111 END IF;
112 END IF;
113
114 IF l_debug_level > 0 THEN
115 oe_debug_pub.add('EXITING DELETE_ADJUSTMENTS');
116 END IF;
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 IF l_debug_level > 0 THEN
121 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
122 END IF;
123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
124 END DELETE_ADJUSTMENTS;
125
126 PROCEDURE CLOSE_ORDER
127 (p_api_version_number IN NUMBER
128 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
129 ,p_header_id IN NUMBER
130 ,x_return_status OUT NOCOPY VARCHAR2
131
132 ,x_msg_count OUT NOCOPY NUMBER
133
134 ,x_msg_data OUT NOCOPY VARCHAR2
135
136 )
137 IS
138 l_header_rec OE_ORDER_PUB.Header_Rec_Type;
139 l_old_header_rec OE_ORDER_PUB.Header_Rec_Type;
140 l_msg_count NUMBER;
141 l_msg_data VARCHAR2(2000);
142 l_check_hold_result VARCHAR2(30);
143 on_hold_error EXCEPTION;
144 l_notify_index NUMBER; -- jolin
145 l_itemkey_sso NUMBER; -- GENESIS
146
147 --
148 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
149 --
150 BEGIN
151
152 SAVEPOINT CLOSE_ORDER;
153
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 OE_MSG_PUB.set_msg_context(
157 p_entity_code => 'HEADER'
158 ,p_entity_id => p_header_id
159 ,p_header_id => p_header_id);
160
161 -- Lock and query the old header record
162
163 OE_Header_Util.Lock_Row
164 (p_header_id => p_header_id
165 ,p_x_header_rec => l_old_header_rec
166 ,x_return_status => x_return_status
167 );
168 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
169 RAISE FND_API.G_EXC_ERROR;
170 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172 END IF;
173
174 OE_MSG_PUB.update_msg_context(
175 p_entity_code => 'HEADER'
176 ,p_entity_id => l_old_header_rec.header_id
177 ,p_header_id => l_old_header_rec.header_id
178 ,p_line_id => null
179 ,p_orig_sys_document_ref => l_old_header_rec.orig_sys_document_ref
180 ,p_orig_sys_document_line_ref => null
181 ,p_change_sequence => l_old_header_rec.change_sequence
182 ,p_source_document_id => l_old_header_rec.source_document_id
183 ,p_source_document_line_id => null
184 ,p_order_source_id => l_old_header_rec.order_source_id
185 ,p_source_document_type_id => l_old_header_rec.source_document_type_id);
186
187
188 -- check for generic or holds specific to CLOSE_HEADER activity
189 IF nvl(l_old_header_rec.cancelled_flag,'N') = 'N' THEN
190
191 OE_Holds_PUB.Check_Holds
192 (p_api_version => 1.0
193 ,p_header_id => p_header_id
194 ,p_wf_item => 'OEOH'
195 ,p_wf_activity => 'CLOSE_ORDER'
196 ,p_chk_act_hold_only => 'Y'
197 ,x_result_out => l_check_hold_result
198 ,x_return_status => x_return_status
199 ,x_msg_count => l_msg_count
200 ,x_msg_data => l_msg_data
201 );
202
203 END IF;
204
205 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS AND
206 l_check_hold_result = FND_API.G_TRUE )
207 THEN
208 FND_MESSAGE.SET_NAME('ONT','OE_CLOSE_ORDER_HOLD_EXISTS');
209 OE_MSG_PUB.ADD;
210 RAISE ON_HOLD_ERROR;
211 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
212 RAISE FND_API.G_EXC_ERROR;
213 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
215 END IF;
216
217
218 -- Set the open flag and flow status on the header
219
220 l_header_rec := l_old_header_rec;
221 l_header_rec.open_flag := 'N';
222 IF l_header_rec.cancelled_flag = 'Y' THEN
223 l_header_rec.flow_status_code := 'CANCELLED';
224 ELSE
225 l_header_rec.flow_status_code := 'CLOSED';
226 END IF;
227 l_header_rec.last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID); -- 3169637;
228 l_header_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
229 l_header_rec.last_update_date := SYSDATE;
230 l_header_rec.lock_control := l_header_rec.lock_control + 1;
231
232 UPDATE oe_order_headers
233 SET open_flag = l_header_rec.open_flag
234 , flow_status_code = l_header_rec.flow_status_code
235 , last_updated_by = l_header_rec.last_updated_by
236 , last_update_login = l_header_rec.last_update_login
237 , last_update_date = l_header_rec.last_update_date
238 , lock_control = l_header_rec.lock_control
239 WHERE header_id = p_header_id;
240
241 -- Added for bug 5988559
242 IF SQL%NOTFOUND THEN
243
244 IF l_debug_level > 0 THEN
245 oe_debug_pub.add( 'ORG CONTEXT is not properly set for'||p_header_id , 1) ;
246 END IF;
247 RAISE FND_API.G_EXC_ERROR;
248
249 END IF;
250
251 DELETE_ADJUSTMENTS(p_header_id => p_header_id);
252
253 -- jolin start
254 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
255
256 -- call notification framework to get header index position
257 OE_ORDER_UTIL.Update_Global_Picture
258 (p_Upd_New_Rec_If_Exists =>FALSE
259 , p_header_rec => l_header_rec
260 , p_old_header_rec => l_old_header_rec
261 , p_header_id => l_header_rec.header_id
262 , x_index => l_notify_index
263 , x_return_status => x_return_status);
264
265 IF l_debug_level > 0 THEN
266 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FOR HDR IS: ' || X_RETURN_STATUS ) ;
267 END IF;
268 IF l_debug_level > 0 THEN
269 oe_debug_pub.add( 'HDR INDEX IS: ' || L_NOTIFY_INDEX , 1 ) ;
270 END IF;
271
272 IF l_notify_index is not null then
273 -- modify Global Picture
274
275 OE_ORDER_UTIL.g_header_rec.open_flag:= l_header_rec.open_flag;
276 OE_ORDER_UTIL.g_header_rec.flow_status_code:= l_header_rec.flow_status_code;
277 OE_ORDER_UTIL.g_header_rec.last_updated_by:=l_header_rec.last_updated_by;
278 OE_ORDER_UTIL.g_header_rec.last_update_login:=l_header_rec.last_update_login;
279 OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
280 OE_ORDER_UTIL.g_header_rec.lock_control:= l_header_rec.lock_control;
281
282 IF l_debug_level > 0 THEN
283 oe_debug_pub.add( 'GLOBAL HDR OPEN_FLAG IS: ' || OE_ORDER_UTIL.G_HEADER_REC.OPEN_FLAG , 1 ) ;
284 END IF;
285 IF l_debug_level > 0 THEN
286 oe_debug_pub.add( 'GLOBAL HDR FLOW_STATUS_CODE IS: ' || OE_ORDER_UTIL.G_HEADER_REC.FLOW_STATUS_CODE , 1 ) ;
287 END IF;
288
289 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
290 RAISE FND_API.G_EXC_ERROR;
291 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
293 END IF;
294
295 -- Process requests is TRUE so still need to call it, but don't need to notify
296 IF l_debug_level > 0 THEN
297 oe_debug_pub.add( 'OEXUCLOB: BEFORE CALLING PROCESS_REQUESTS_AND_NOTIFY' ) ;
298 END IF;
299 OE_Order_PVT.Process_Requests_And_Notify
300 ( p_process_requests => TRUE
301 , p_notify => FALSE
302 , p_process_ack => FALSE
303 , x_return_status => x_return_status
304 , p_header_rec => l_header_rec
305 , p_old_header_rec => l_old_header_rec
306 );
307 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
308 RAISE FND_API.G_EXC_ERROR;
309 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
311 END IF;
312 END IF ; /* global entity index null check */
313
314 ELSE /* in pre-pack H code */
315
316 -- Need to both notify and process requests in old framework
317 IF l_debug_level > 0 THEN
318 oe_debug_pub.add( 'OEXUCLOB: BEFORE CALLING PROCESS_REQUESTS_AND_NOTIFY' ) ;
319 END IF;
320 OE_Order_PVT.Process_Requests_And_Notify
321 ( p_process_requests => TRUE
322 , p_notify => TRUE
323 , p_process_ack => FALSE
324 , x_return_status => x_return_status
325 , p_header_rec => l_header_rec
326 , p_old_header_rec => l_old_header_rec
327 );
328 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
329 RAISE FND_API.G_EXC_ERROR;
330 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333 END IF; /* code set is pack H or higher */
334 -- jolin end
335
336 -- Added following code to raise OIP status update event for Order Header during closure.
337 -- Call to the following procedure added for the bug 13930149
338 oe_debug_pub.add('RAISING OIP STATUS CHANGE BUSINESS EVENT FOR ORDER HEADER CLOSURE');
339 OE_ORDER_UTIL.RAISE_BUSINESS_EVENT(p_header_id => l_header_rec.header_id,
340 p_status => l_header_rec.flow_status_code);
341
342 /********************GENESIS********************************
343 * Some statuses are not going through process order and *
344 * the update_flow_status is getting called directly. So *
345 * we need to call synch_header_line for 28 *
346 ***********************************************************/
347 IF l_debug_level > 0 THEN
348 oe_debug_pub.add( ' GENESIS : -CLOSE ORDER- header rec order source'||l_header_rec.order_source_id);
349 END IF;
350 IF (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN
351 -- 8516700: Start (O2C25)
352 IF Oe_Genesis_Util.Status_Needs_Sync(l_header_rec.flow_status_code) THEN
353 -- 8516700: End (O2C25)
354
355 select OE_XML_MESSAGE_SEQ_S.nextval
356 into l_itemkey_sso
357 from dual;
358 IF l_debug_level > 0 THEN
359 oe_debug_pub.add( ' GENESIS CLOSE : CLOSE ORDER - l_itemkey_sso'||l_itemkey_sso);
360 END IF;
361 OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE( p_header_rec => l_header_rec
362 ,p_line_rec => null
363 ,p_hdr_req_id => l_itemkey_sso
364 ,p_lin_req_id => null
365 ,p_change_type => 'LINE_STATUS');
366 END IF; -- status_needs_sync
367 END IF; -- source_aia_enabled
368 -- GENESIS --
369
370 -- aksingh performance
371 -- As the update is on headers table, it is time to update
372 -- cache also!
373 OE_Order_Cache.Set_Order_Header(l_header_rec);
374
375 -- Bug 1755817: clear the cached constraint results for header entity
376 -- when order header is updated.
377 OE_PC_Constraints_Admin_Pvt.Clear_Cached_Results
378 (p_validation_entity_id => OE_PC_GLOBALS.G_ENTITY_HEADER);
379
380 OE_MSG_PUB.reset_msg_context('HEADER');
381
382 EXCEPTION
383 WHEN ON_HOLD_ERROR THEN
384 x_return_status := 'H';
385 OE_MSG_PUB.reset_msg_context('HEADER');
386 ROLLBACK TO CLOSE_ORDER;
387 WHEN FND_API.G_EXC_ERROR THEN
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 OE_MSG_PUB.reset_msg_context('HEADER');
390 ROLLBACK TO CLOSE_ORDER;
391 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 OE_MSG_PUB.reset_msg_context('HEADER');
394 ROLLBACK TO CLOSE_ORDER;
395 WHEN OTHERS THEN
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 IF FND_MSG_PUB.Check_Msg_Level
398 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
399 THEN
400 OE_MSG_PUB.Add_Exc_Msg
401 ( G_PKG_NAME
402 , 'Close_Order'
403 );
404 END IF;
405 OE_MSG_PUB.reset_msg_context('HEADER');
406 ROLLBACK TO CLOSE_ORDER;
407 END CLOSE_ORDER;
408
409 PROCEDURE CLOSE_LINE
410 (p_api_version_number IN NUMBER
411 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
412 ,p_line_id IN NUMBER
413 ,x_return_status OUT NOCOPY VARCHAR2
414 ,x_msg_count OUT NOCOPY NUMBER
415 ,x_msg_data OUT NOCOPY VARCHAR2
416 )
417 IS
418 l_line_tbl OE_ORDER_PUB.Line_TBL_Type;
419 l_old_line_tbl OE_ORDER_PUB.Line_TBL_Type;
420 l_msg_count NUMBER;
421 l_msg_data VARCHAR2(2000);
422 l_check_hold_result VARCHAR2(30);
423 on_hold_error EXCEPTION;
424 l_notify_index NUMBER; -- jolin
425 -- GENESIS --
426 l_itemkey_sso NUMBER;
427 l_header_rec OE_Order_PUB.Header_Rec_Type;
428 l_return_status_gen VARCHAR2(30);
429 -- GENESIS --
430 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
431 l_inventory_item_id oe_order_lines_all.inventory_item_id%TYPE;
432 l_org_id oe_order_lines_all.org_id%TYPE;
433 /* Customer Acceptance */
434 l_pending_acceptance VARCHAR2(1) := 'N';
435 l_line_rec OE_ORDER_PUB.Line_rec_type;
436 --
437 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
438 --
439 BEGIN
440 IF l_debug_level > 0 THEN
441 oe_debug_pub.add( 'ENTER OE_ORDER_CLOSE_UTIL.CLOSE_LINE' , 1 ) ;
442 END IF;
443
444 SAVEPOINT CLOSE_LINE;
445
446 x_return_status := FND_API.G_RET_STS_SUCCESS;
447
448 OE_MSG_PUB.set_msg_context(
449 p_entity_code => 'LINE'
450 ,p_entity_id => p_line_id
451 ,p_line_id => p_line_id);
452
453
454 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
455 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
456 END IF;
457
458 IF OE_GLOBALS.G_EC_INSTALLED IS NULL THEN
459 OE_GLOBALS.G_EC_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(175);
460 END IF;
461
462
463 -- Check whether EC/ASO products are installed. Call lock_row only if we
464 -- need to call Process_Requests_And_Notify to notify OC or to process
465 -- acknowledgements.
466
467 IF ( (OE_GLOBALS.G_EC_INSTALLED <> 'Y') AND
468 (OE_GLOBALS.G_ASO_INSTALLED <> 'Y') AND
469 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'N') )
470 THEN
471 SELECT cancelled_flag,
472 lock_control,
473 line_id,
474 header_id,
475 order_source_id,
476 orig_sys_document_ref,
477 orig_sys_line_ref,
478 orig_sys_shipment_ref,
479 change_sequence,
480 source_document_type_id,
481 source_document_id,
482 source_document_line_id
483 INTO l_old_line_tbl(1).cancelled_flag,
484 l_old_line_tbl(1).lock_control,
485 l_old_line_tbl(1).line_id,
486 l_old_line_tbl(1).header_id,
487 l_old_line_tbl(1).order_source_id,
488 l_old_line_tbl(1).orig_sys_document_ref,
489 l_old_line_tbl(1).orig_sys_line_ref,
490 l_old_line_tbl(1).orig_sys_shipment_ref,
491 l_old_line_tbl(1).change_sequence,
492 l_old_line_tbl(1).source_document_type_id,
493 l_old_line_tbl(1).source_document_id,
494 l_old_line_tbl(1).source_document_line_id
495 FROM oe_order_lines_all
496 WHERE line_id = p_line_id
497 FOR UPDATE NOWAIT;
498
499 OE_MSG_PUB.update_msg_context
500 ( p_entity_code => 'LINE'
501 ,p_entity_id => l_old_line_tbl(1).line_id
502 ,p_header_id => l_old_line_tbl(1).header_id
503 ,p_line_id => l_old_line_tbl(1).line_id
504 ,p_orig_sys_document_ref => l_old_line_tbl(1).orig_sys_document_ref
505 ,p_orig_sys_document_line_ref => l_old_line_tbl(1).orig_sys_line_ref
506 ,p_orig_sys_shipment_ref => l_old_line_tbl(1).orig_sys_shipment_ref
507 ,p_change_sequence => l_old_line_tbl(1).change_sequence
508 ,p_source_document_id => l_old_line_tbl(1).source_document_id
509 ,p_source_document_line_id => l_old_line_tbl(1).source_document_line_id
510 ,p_order_source_id => l_old_line_tbl(1).order_source_id
511 ,p_source_document_type_id => l_old_line_tbl(1).source_document_type_id);
512
513 ELSE
514 -- Lock and query the old line record
515
516 OE_Line_Util.Lock_Rows
517 (p_line_id => p_line_id
518 ,x_line_tbl => l_old_line_tbl
519 ,x_return_status => x_return_status
520 );
521 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
522 RAISE FND_API.G_EXC_ERROR;
523 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 END IF;
526
527 OE_MSG_PUB.update_msg_context
528 ( p_entity_code => 'LINE'
529 ,p_entity_id => l_old_line_tbl(1).line_id
530 ,p_header_id => l_old_line_tbl(1).header_id
531 ,p_line_id => l_old_line_tbl(1).line_id
532 ,p_orig_sys_document_ref => l_old_line_tbl(1).orig_sys_document_ref
533 ,p_orig_sys_document_line_ref => l_old_line_tbl(1).orig_sys_line_ref
534 ,p_orig_sys_shipment_ref => l_old_line_tbl(1).orig_sys_shipment_ref
535 ,p_change_sequence => l_old_line_tbl(1).change_sequence
536 ,p_source_document_id => l_old_line_tbl(1).source_document_id
537 ,p_source_document_line_id => l_old_line_tbl(1).source_document_line_id
538 ,p_order_source_id => l_old_line_tbl(1).order_source_id
539 ,p_source_document_type_id => l_old_line_tbl(1).source_document_type_id);
540
541 END IF;
542
543
544
545 --- Deep
546
547 -- check for generic or holds specific to CLOSE_LINE activity
548
549 IF nvl(l_old_line_tbl(1).cancelled_flag,'N') = 'N' THEN
550 OE_Holds_PUB.Check_Holds
551 (p_api_version => 1.0
552 ,p_line_id => p_line_id
553 ,p_wf_item => 'OEOL'
554 ,p_wf_activity => 'CLOSE_LINE'
555 ,p_chk_act_hold_only => 'Y'
556 ,x_result_out => l_check_hold_result
557 ,x_return_status => x_return_status
558 ,x_msg_count => l_msg_count
559 ,x_msg_data => l_msg_data
560 );
561 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS AND
562 l_check_hold_result = FND_API.G_TRUE )
563 THEN
564 FND_MESSAGE.SET_NAME('ONT','OE_CLOSE_LINE_HOLD_EXISTS');
565 OE_MSG_PUB.ADD;
566 RAISE ON_HOLD_ERROR;
567 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
568 RAISE FND_API.G_EXC_ERROR;
569 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
571 END IF;
572 END IF;
573
574 --Customer Acceptance
575 IF nvl(OE_SYS_PARAMETERS.VALUE('ENABLE_FULFILLMENT_ACCEPTANCE'), 'N') = 'Y' THEN
576
577 OE_Line_Util.Query_Row(p_line_id => p_line_id,x_line_rec => l_line_rec);
578
579 -- need to check if the line is accepted when re-tried from progress order action
580 IF ((l_line_rec.flow_status_code='POST-BILLING_ACCEPTANCE' OR OE_ACCEPTANCE_UTIL.Post_billing_acceptance_on (l_line_rec))
581 AND OE_ACCEPTANCE_UTIL.Acceptance_Status(l_line_rec) = 'NOT_ACCEPTED')
582 AND nvl(l_line_rec.cancelled_flag,'N') = 'N' THEN
583
584 -- added following for bug# 5232503
585 -- If it is a child line then check if the parent is accepted.
586 -- Do not wait for acceptance if parent is already accepted. -- This check is added to make sure that child line won't get stuck
587 -- if the system parameter is changed from yes to no to yes again.
588 IF ((l_line_rec.top_model_line_id is not null
589 AND l_line_rec.line_id <> l_line_rec.top_model_line_id
590 AND OE_ACCEPTANCE_UTIL.Acceptance_Status(l_line_rec.top_model_line_id) = 'ACCEPTED')
591 OR
592 (l_line_rec.item_type_code = 'SERVICE'
593 AND l_line_rec.service_reference_type_code='ORDER'
594 AND l_line_rec.service_reference_line_id IS NOT NULL
595 AND OE_ACCEPTANCE_UTIL.Acceptance_Status(l_line_rec.service_reference_line_id) = 'ACCEPTED')) THEN
596 IF l_debug_level > 0 THEN
597 oe_debug_pub.add('acceptance not required. item_type:'||l_line_rec.item_type_code);
598 END IF;
599 ELSE
600 l_pending_acceptance:= 'Y';
601 END IF;
602 END IF;
603 END IF;
604
605 --Customer Acceptance
606
607 --
608 -- Bug # 4454055
609 -- Close_Line is changed to call the costing API to move COGS account from
610 -- deferred to actual account for the order lines that need to notify costing
611 --
612
613 IF oe_cogs_grp.is_revenue_event_line(p_line_id) = 'Y' AND l_pending_acceptance = 'N' THEN
614
615 SELECT inventory_item_id, org_id
616 INTO l_inventory_item_id, l_org_id
617 FROM oe_order_lines_all
618 WHERE line_id = p_line_id;
619
620
621 cst_revenuecogsmatch_grp.receive_closelineevent (
622 p_api_version => 1.0,
623 p_init_msg_list => FND_API.G_FALSE,
624 p_commit => FND_API.G_FALSE,
625 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
626 x_return_status => l_return_status,
627 x_msg_count => x_msg_count,
628 x_msg_data => x_msg_data,
629 p_revenue_event_line_id => p_line_id,
630 p_event_date => SYSDATE,
631 p_ou_id => l_org_id,
632 p_inventory_item_id => l_inventory_item_id);
633
634 END IF;
635
636 -- Check Return status and error handling
637 -- Costing will raise an error if required parameters are not passed
638 -- Also if there is any unexpected error, close line workflow activity would be in
639 -- Incomplete status and will rerun automatically, we will mark the flow_status_code
640 -- as 'NOTIFY_COSTING_ERROR';
641
642 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
643
644 UPDATE oe_order_lines_all
645 SET flow_status_code='NOTIFY_COSTING_ERROR'
646 WHERE line_id = p_line_id;
647 x_return_status := l_return_status;
648 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
649 return;
650 END IF;
651
652
653 -- Check whether we need to call Process_Requests_And_Notify.
654
655 IF ( (OE_GLOBALS.G_RECURSION_MODE = 'Y') AND
656 (OE_GLOBALS.G_EC_INSTALLED <> 'Y') AND
657 (OE_GLOBALS.G_ASO_INSTALLED <> 'Y') AND
658 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'N') )
659 THEN
660 --Customer Acceptance
661 -- Modified for bug 16037930
662
663 IF l_pending_acceptance = 'Y' THEN
664
665 l_line_tbl(1) := l_old_line_tbl(1);
666 l_line_tbl(1).flow_status_code := 'POST-BILLING_ACCEPTANCE';
667 l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);
668 l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
669 l_line_tbl(1).last_update_date := SYSDATE;
670 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
671
672
673 ELSE
674
675 l_line_tbl(1) := l_old_line_tbl(1);
676 l_line_tbl(1).open_flag := 'N';
677 l_line_tbl(1).calculate_price_flag := 'N';
678 IF l_line_tbl(1).cancelled_flag = 'Y' THEN
679 l_line_tbl(1).flow_status_code := 'CANCELLED';
680 ELSE
681 l_line_tbl(1).flow_status_code := 'CLOSED';
682 END IF;
683 l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);
684 l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
685 l_line_tbl(1).last_update_date := SYSDATE;
686 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
687
688 END IF;
689
690 UPDATE oe_order_lines
691 SET open_flag = l_line_tbl(1).open_flag
692 , calculate_price_flag = l_line_tbl(1).calculate_price_flag
693 , flow_status_code = l_line_tbl(1).flow_status_code
694 , last_updated_by = l_line_tbl(1).last_updated_by
695 , last_update_login = l_line_tbl(1).last_update_login
696 , last_update_date = l_line_tbl(1).last_update_date
697 , lock_control = l_line_tbl(1).lock_control
698 WHERE line_id = p_line_id;
699
700 -- Added for bug 5988559
701 IF SQL%NOTFOUND THEN
702
703 IF l_debug_level > 0 THEN
704 oe_debug_pub.add( 'ORG CONTEXT is not properly set for'||p_line_id , 1) ;
705 END IF;
706 RAISE FND_API.G_EXC_ERROR;
707
708 END IF;
709
710
711
712
713 ELSE
714 --Customer Acceptance Start
715 IF l_pending_acceptance= 'Y' THEN
716
717 l_line_tbl(1) := l_old_line_tbl(1);
718 l_line_tbl(1).flow_status_code := 'POST-BILLING_ACCEPTANCE';
719 l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);
720 l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
721 l_line_tbl(1).last_update_date := SYSDATE;
722 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
723 ELSE
724 --Customer Acceptance End
725 l_line_tbl(1) := l_old_line_tbl(1);
726 l_line_tbl(1).open_flag := 'N';
727 l_line_tbl(1).calculate_price_flag := 'N';
728 IF l_line_tbl(1).cancelled_flag = 'Y' THEN
729 l_line_tbl(1).flow_status_code := 'CANCELLED';
730 ELSE
731 l_line_tbl(1).flow_status_code := 'CLOSED';
732 END IF;
733 l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);
734 l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
735 l_line_tbl(1).last_update_date := SYSDATE;
736 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
737 END IF;
738
739 UPDATE oe_order_lines
740 SET open_flag = l_line_tbl(1).open_flag
741 , calculate_price_flag = l_line_tbl(1).calculate_price_flag
742 , flow_status_code = l_line_tbl(1).flow_status_code
743 , last_updated_by = l_line_tbl(1).last_updated_by
744 , last_update_login = l_line_tbl(1).last_update_login
745 , last_update_date = l_line_tbl(1).last_update_date
746 , lock_control = l_line_tbl(1).lock_control
747 WHERE line_id = p_line_id;
748
749 -- Added for bug 5988559
750 IF SQL%NOTFOUND THEN
751
752 IF l_debug_level > 0 THEN
753 oe_debug_pub.add( 'ORG CONTEXT is not properly set for'||p_line_id , 1) ;
754 END IF;
755 RAISE FND_API.G_EXC_ERROR;
756
757 END IF;
758
759
760 -- jolin start
761 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
762
763 -- call notification framework to get this line's index position
764 OE_ORDER_UTIL.Update_Global_Picture
765 (p_Upd_New_Rec_If_Exists =>FALSE
766 , p_line_rec => l_line_tbl(1)
767 , p_old_line_rec => l_old_line_tbl(1)
768 , p_line_id => l_line_tbl(1).line_id
769 , x_index => l_notify_index
770 , x_return_status => x_return_status);
771
772 IF l_debug_level > 0 THEN
773 oe_debug_pub.add( 'UPDATE_GLOBAL RET_STATUS FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || X_RETURN_STATUS , 1 ) ;
774 END IF;
775 IF l_debug_level > 0 THEN
776 oe_debug_pub.add( 'UPDATE_GLOBAL INDEX FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || L_NOTIFY_INDEX , 1 ) ;
777 END IF;
778
779 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
780 RAISE FND_API.G_EXC_ERROR;
781 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783 END IF;
784
785 IF l_notify_index is not null then
786 -- modify Global Picture
787 OE_ORDER_UTIL.g_line_tbl(l_notify_index).open_flag:= l_line_tbl(1).open_flag;
788 OE_ORDER_UTIL.g_line_tbl(l_notify_index).calculate_price_flag:= l_line_tbl(1).calculate_price_flag;
789 OE_ORDER_UTIL.g_line_tbl(l_notify_index).flow_status_code:=l_line_tbl(1).flow_status_code;
790 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by:= l_line_tbl(1).last_updated_by;
791 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login:=l_line_tbl(1).last_update_login;
792 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date:=l_line_tbl(1).last_update_date;
793 OE_ORDER_UTIL.g_line_tbl(l_notify_index).lock_control:= l_line_tbl(1).lock_control;
794
795 IF l_debug_level > 0 THEN
796 oe_debug_pub.add( 'GLOBAL LINE OPEN_FLAG IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .OPEN_FLAG , 1 ) ;
797 END IF;
798 IF l_debug_level > 0 THEN
799 oe_debug_pub.add( 'GLOBAL LINE CALCULATE_PRICE_FLAG IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .CALCULATE_PRICE_FLAG , 1 ) ;
800 END IF;
801 IF l_debug_level > 0 THEN
802 oe_debug_pub.add( 'GLOBAL LINE FLOW_STATUS_CODE IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .FLOW_STATUS_CODE , 1 ) ;
803 END IF;
804
805
806 -- Process requests is TRUE, but don't need to notify
807 IF l_debug_level > 0 THEN
808 oe_debug_pub.add( 'OEXPVPMB: BEFORE CALLING PROCESS_REQUESTS_AND_NOTIFY' ) ;
809 END IF;
810 OE_Order_PVT.Process_Requests_And_Notify
811 ( p_process_requests => TRUE
812 , p_notify => FALSE
813 , p_process_ack => FALSE
814 , x_return_status => x_return_status
815 , p_line_tbl => l_line_tbl
816 , p_old_line_tbl => l_old_line_tbl
817 );
818 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
819 RAISE FND_API.G_EXC_ERROR;
820 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
822 END IF;
823 END IF ; /* global entity index null check */
824
825 ELSE /* in pre-pack H code */
826
827 -- Need to both notify and process requests in old framework
828 IF l_debug_level > 0 THEN
829 oe_debug_pub.add( 'OEXPVPMB: BEFORE CALLING PROCESS_REQUESTS_AND_NOTIFY' ) ;
830 END IF;
831 OE_Order_PVT.Process_Requests_And_Notify
832 ( p_process_requests => TRUE
833 , p_notify => TRUE
834 , p_process_ack => FALSE
835 , x_return_status => x_return_status
836 , p_line_tbl => l_line_tbl
837 , p_old_line_tbl => l_old_line_tbl
838 );
839 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
840 RAISE FND_API.G_EXC_ERROR;
841 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
843 END IF;
844 END IF; /* code set is pack H or higher */
845 -- jolin end
846
847 END IF; -- we should notify after we update
848
849 IF l_pending_acceptance = 'Y' THEN
850 x_return_status := 'C';
851 END IF;
852
853 --Customer Acceptance
854 IF l_pending_acceptance = 'N' THEN
855 DELETE_ADJUSTMENTS(p_line_id => p_line_id);
856 OE_MSG_PUB.Reset_Msg_Context('LINE');
857 END IF;
858
859 --- Adding the following code to raise OIP status change event during order line closure
860 -- change done for bug 13930149
861 oe_debug_pub.add('RAISING OIP STATUS CHANGE BUSINESS EVENT FOR ORDER LINE CLOSURE');
862 OE_ORDER_UTIL.RAISE_BUSINESS_EVENT(p_header_id => l_old_line_tbl(1).header_id,
863 p_line_id => p_line_id,
864 p_status => l_line_tbl(1).flow_status_code);
865 -- done with changes for bug 13930149
866
867 /********************GENESIS********************************
868 * Some statuses are not going through process order and *
869 * the update_flow_status is getting called directly. So *
870 * we need to call synch_header_line for 28 *
871 ***********************************************************/
872 /* 10065583 Moved the If condition up */
873
874 IF (OE_GENESIS_UTIL.source_aia_enabled(l_old_line_tbl(1).order_source_id)) THEN
875 IF l_debug_level > 0 THEN
876 oe_debug_pub.add( ' GENESIS : CLOSE LINE - header rec order source'||p_line_id);
877 oe_debug_pub.add( ' GENESIS : CLOSE LINE - line rec order source'||l_line_tbl(1).order_source_id);
878 oe_debug_pub.add( ' GENESIS : CLOSE LINE - old line rec order source'||l_old_line_tbl(1).order_source_id);
879 oe_debug_pub.add( ' GENESIS : CLOSE LINE - old line rec order source'||l_line_tbl(1).order_source_id);
880 oe_debug_pub.add( ' GENESIS : CLOSE LINE - new line rec flow status'||l_line_tbl(1).flow_status_code);
881 oe_debug_pub.add( ' GENESIS : CLOSE LINE - old line rec flow status'||l_old_line_tbl(1).flow_status_code);
882 END IF;
883 -- 8516700 (O2C2.5): Start
884 IF Oe_Genesis_Util.Status_Needs_Sync(l_line_tbl(1).flow_status_code) THEN
885 -- 8516700 (O2C2.5): End
886 OE_Header_UTIL.Query_Row
887 (p_header_id => l_old_line_tbl(1).header_id
888 ,x_header_rec => l_header_rec
889 );
890
891 select OE_XML_MESSAGE_SEQ_S.nextval
892 into l_itemkey_sso
893 from dual;
894 IF l_debug_level > 0 THEN
895 oe_debug_pub.add( ' GENESIS : CLOSE LINE - l_itemkey_sso'||l_itemkey_sso);
896 END IF;
897
898 OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec => l_line_tbl(1),
899 p_change_type => 'LINE_STATUS',
900 p_req_id => l_itemkey_sso,
901 X_RETURN_STATUS => L_RETURN_STATUS_GEN);
902
903 IF l_debug_level > 0 THEN
904 oe_debug_pub.add( ' GENESIS :CLOSE LINE: RETURN STATUS'||l_return_status_gen);
905 END IF;
906
907 IF l_return_status_gen = FND_API.G_RET_STS_SUCCESS THEN
908 OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE( p_header_rec => l_header_rec
909 ,p_line_rec => l_line_tbl(1) -- Bug 8442372
910 ,p_hdr_req_id => l_itemkey_sso
911 ,p_lin_req_id => l_itemkey_sso
912 ,p_change_type => 'LINE_STATUS');
913 END IF;
914
915 END IF; -- status_needs_sync
916 END IF; -- source_aia_enabled
917 -- GENESIS
918
919 EXCEPTION
920 WHEN ON_HOLD_ERROR THEN
921 x_return_status := 'H';
922 OE_MSG_PUB.Reset_Msg_Context('LINE');
923 ROLLBACK TO CLOSE_LINE;
924 WHEN FND_API.G_EXC_ERROR THEN
925 x_return_status := FND_API.G_RET_STS_ERROR;
926 OE_MSG_PUB.Reset_Msg_Context('LINE');
927 ROLLBACK TO CLOSE_LINE;
928 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
930 OE_MSG_PUB.Reset_Msg_Context('LINE');
931 ROLLBACK TO CLOSE_LINE;
932 WHEN OTHERS THEN
933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934 IF FND_MSG_PUB.Check_Msg_Level
935 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
936 THEN
937 OE_MSG_PUB.Add_Exc_Msg
938 ( G_PKG_NAME
939 , 'Close_Line'
940 );
941 END IF;
942 OE_MSG_PUB.Reset_Msg_Context('LINE');
943 ROLLBACK TO CLOSE_LINE;
944 END CLOSE_LINE;
945
946 END OE_ORDER_CLOSE_UTIL;