[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_CLOSE_UTIL
Source
1 PACKAGE BODY OE_ORDER_CLOSE_UTIL AS
2 /* $Header: OEXUCLOB.pls 120.11 2007/12/24 04:46:25 snimmaga 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 /********************GENESIS********************************
337 * Some statuses are not going through process order and *
338 * the update_flow_status is getting called directly. So *
339 * we need to call synch_header_line for 28 *
340 ***********************************************************/
341 IF l_debug_level > 0 THEN
342 oe_debug_pub.add( ' GENESIS : -CLOSE ORDER- header rec order source'||l_header_rec.order_source_id);
343 END IF;
344 IF (OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN
345
346 select OE_XML_MESSAGE_SEQ_S.nextval
347 into l_itemkey_sso
348 from dual;
349 IF l_debug_level > 0 THEN
350 oe_debug_pub.add( ' GENESIS CLOSE : CLOSE ORDER - l_itemkey_sso'||l_itemkey_sso);
351 END IF;
352 OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE( p_header_rec => l_header_rec
353 ,p_line_rec => null
354 ,p_hdr_req_id => l_itemkey_sso
355 ,p_lin_req_id => null
356 ,p_change_type => 'LINE_STATUS');
357 END IF;
358 -- GENESIS --
359
360 -- aksingh performance
361 -- As the update is on headers table, it is time to update
362 -- cache also!
363 OE_Order_Cache.Set_Order_Header(l_header_rec);
364
365 -- Bug 1755817: clear the cached constraint results for header entity
366 -- when order header is updated.
367 OE_PC_Constraints_Admin_Pvt.Clear_Cached_Results
368 (p_validation_entity_id => OE_PC_GLOBALS.G_ENTITY_HEADER);
369
370 OE_MSG_PUB.reset_msg_context('HEADER');
371
372 EXCEPTION
373 WHEN ON_HOLD_ERROR THEN
374 x_return_status := 'H';
375 OE_MSG_PUB.reset_msg_context('HEADER');
376 ROLLBACK TO CLOSE_ORDER;
377 WHEN FND_API.G_EXC_ERROR THEN
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 OE_MSG_PUB.reset_msg_context('HEADER');
380 ROLLBACK TO CLOSE_ORDER;
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 OE_MSG_PUB.reset_msg_context('HEADER');
384 ROLLBACK TO CLOSE_ORDER;
385 WHEN OTHERS THEN
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 IF FND_MSG_PUB.Check_Msg_Level
388 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
389 THEN
390 OE_MSG_PUB.Add_Exc_Msg
391 ( G_PKG_NAME
392 , 'Close_Order'
393 );
394 END IF;
395 OE_MSG_PUB.reset_msg_context('HEADER');
396 ROLLBACK TO CLOSE_ORDER;
397 END CLOSE_ORDER;
398
399 PROCEDURE CLOSE_LINE
400 (p_api_version_number IN NUMBER
401 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
402 ,p_line_id IN NUMBER
403 ,x_return_status OUT NOCOPY VARCHAR2
404 ,x_msg_count OUT NOCOPY NUMBER
405 ,x_msg_data OUT NOCOPY VARCHAR2
406 )
407 IS
408 l_line_tbl OE_ORDER_PUB.Line_TBL_Type;
409 l_old_line_tbl OE_ORDER_PUB.Line_TBL_Type;
410 l_msg_count NUMBER;
411 l_msg_data VARCHAR2(2000);
412 l_check_hold_result VARCHAR2(30);
413 on_hold_error EXCEPTION;
414 l_notify_index NUMBER; -- jolin
415 -- GENESIS --
416 l_itemkey_sso NUMBER;
417 l_header_rec OE_Order_PUB.Header_Rec_Type;
418 l_return_status_gen VARCHAR2(30);
419 -- GENESIS --
420 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
421 l_inventory_item_id oe_order_lines_all.inventory_item_id%TYPE;
422 l_org_id oe_order_lines_all.org_id%TYPE;
423 /* Customer Acceptance */
424 l_pending_acceptance VARCHAR2(1) := 'N';
425 l_line_rec OE_ORDER_PUB.Line_rec_type;
426 --
427 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
428 --
429 BEGIN
430 IF l_debug_level > 0 THEN
431 oe_debug_pub.add( 'ENTER OE_ORDER_CLOSE_UTIL.CLOSE_LINE' , 1 ) ;
432 END IF;
433
434 SAVEPOINT CLOSE_LINE;
435
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 OE_MSG_PUB.set_msg_context(
439 p_entity_code => 'LINE'
440 ,p_entity_id => p_line_id
441 ,p_line_id => p_line_id);
442
443
444 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
445 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
446 END IF;
447
448 IF OE_GLOBALS.G_EC_INSTALLED IS NULL THEN
449 OE_GLOBALS.G_EC_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(175);
450 END IF;
451
452
453 -- Check whether EC/ASO products are installed. Call lock_row only if we
454 -- need to call Process_Requests_And_Notify to notify OC or to process
455 -- acknowledgements.
456
457 IF ( (OE_GLOBALS.G_EC_INSTALLED <> 'Y') AND
458 (OE_GLOBALS.G_ASO_INSTALLED <> 'Y') AND
459 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'N') )
460 THEN
461 SELECT cancelled_flag,
462 lock_control,
463 line_id,
464 header_id,
465 order_source_id,
466 orig_sys_document_ref,
467 orig_sys_line_ref,
468 orig_sys_shipment_ref,
469 change_sequence,
470 source_document_type_id,
471 source_document_id,
472 source_document_line_id
473 INTO l_old_line_tbl(1).cancelled_flag,
474 l_old_line_tbl(1).lock_control,
475 l_old_line_tbl(1).line_id,
476 l_old_line_tbl(1).header_id,
477 l_old_line_tbl(1).order_source_id,
478 l_old_line_tbl(1).orig_sys_document_ref,
479 l_old_line_tbl(1).orig_sys_line_ref,
480 l_old_line_tbl(1).orig_sys_shipment_ref,
481 l_old_line_tbl(1).change_sequence,
482 l_old_line_tbl(1).source_document_type_id,
483 l_old_line_tbl(1).source_document_id,
484 l_old_line_tbl(1).source_document_line_id
485 FROM oe_order_lines_all
486 WHERE line_id = p_line_id
487 FOR UPDATE NOWAIT;
488
489 OE_MSG_PUB.update_msg_context
490 ( p_entity_code => 'LINE'
491 ,p_entity_id => l_old_line_tbl(1).line_id
492 ,p_header_id => l_old_line_tbl(1).header_id
493 ,p_line_id => l_old_line_tbl(1).line_id
494 ,p_orig_sys_document_ref => l_old_line_tbl(1).orig_sys_document_ref
495 ,p_orig_sys_document_line_ref => l_old_line_tbl(1).orig_sys_line_ref
496 ,p_orig_sys_shipment_ref => l_old_line_tbl(1).orig_sys_shipment_ref
497 ,p_change_sequence => l_old_line_tbl(1).change_sequence
498 ,p_source_document_id => l_old_line_tbl(1).source_document_id
499 ,p_source_document_line_id => l_old_line_tbl(1).source_document_line_id
500 ,p_order_source_id => l_old_line_tbl(1).order_source_id
501 ,p_source_document_type_id => l_old_line_tbl(1).source_document_type_id);
502
503 ELSE
504 -- Lock and query the old line record
505
506 OE_Line_Util.Lock_Rows
507 (p_line_id => p_line_id
508 ,x_line_tbl => l_old_line_tbl
509 ,x_return_status => x_return_status
510 );
511 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
512 RAISE FND_API.G_EXC_ERROR;
513 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515 END IF;
516
517 OE_MSG_PUB.update_msg_context
518 ( p_entity_code => 'LINE'
519 ,p_entity_id => l_old_line_tbl(1).line_id
520 ,p_header_id => l_old_line_tbl(1).header_id
521 ,p_line_id => l_old_line_tbl(1).line_id
522 ,p_orig_sys_document_ref => l_old_line_tbl(1).orig_sys_document_ref
523 ,p_orig_sys_document_line_ref => l_old_line_tbl(1).orig_sys_line_ref
524 ,p_orig_sys_shipment_ref => l_old_line_tbl(1).orig_sys_shipment_ref
525 ,p_change_sequence => l_old_line_tbl(1).change_sequence
526 ,p_source_document_id => l_old_line_tbl(1).source_document_id
527 ,p_source_document_line_id => l_old_line_tbl(1).source_document_line_id
528 ,p_order_source_id => l_old_line_tbl(1).order_source_id
529 ,p_source_document_type_id => l_old_line_tbl(1).source_document_type_id);
530
531 END IF;
532
533
534
535 --- Deep
536
537 -- check for generic or holds specific to CLOSE_LINE activity
538
539 IF nvl(l_old_line_tbl(1).cancelled_flag,'N') = 'N' THEN
540 OE_Holds_PUB.Check_Holds
541 (p_api_version => 1.0
542 ,p_line_id => p_line_id
543 ,p_wf_item => 'OEOL'
544 ,p_wf_activity => 'CLOSE_LINE'
545 ,p_chk_act_hold_only => 'Y'
546 ,x_result_out => l_check_hold_result
547 ,x_return_status => x_return_status
548 ,x_msg_count => l_msg_count
549 ,x_msg_data => l_msg_data
550 );
551 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS AND
552 l_check_hold_result = FND_API.G_TRUE )
553 THEN
554 FND_MESSAGE.SET_NAME('ONT','OE_CLOSE_LINE_HOLD_EXISTS');
555 OE_MSG_PUB.ADD;
556 RAISE ON_HOLD_ERROR;
557 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
558 RAISE FND_API.G_EXC_ERROR;
559 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
561 END IF;
562 END IF;
563
564 --Customer Acceptance
565 IF nvl(OE_SYS_PARAMETERS.VALUE('ENABLE_FULFILLMENT_ACCEPTANCE'), 'N') = 'Y' THEN
566
567 OE_Line_Util.Query_Row(p_line_id => p_line_id,x_line_rec => l_line_rec);
568
569 -- need to check if the line is accepted when re-tried from progress order action
570 IF ((l_line_rec.flow_status_code='POST-BILLING_ACCEPTANCE' OR OE_ACCEPTANCE_UTIL.Post_billing_acceptance_on (l_line_rec))
571 AND OE_ACCEPTANCE_UTIL.Acceptance_Status(l_line_rec) = 'NOT_ACCEPTED')
572 AND nvl(l_line_rec.cancelled_flag,'N') = 'N' THEN
573
574 -- added following for bug# 5232503
575 -- If it is a child line then check if the parent is accepted.
576 -- Do not wait for acceptance if parent is already accepted. -- This check is added to make sure that child line won't get stuck
577 -- if the system parameter is changed from yes to no to yes again.
578 IF ((l_line_rec.top_model_line_id is not null
579 AND l_line_rec.line_id <> l_line_rec.top_model_line_id
580 AND OE_ACCEPTANCE_UTIL.Acceptance_Status(l_line_rec.top_model_line_id) = 'ACCEPTED')
581 OR
582 (l_line_rec.item_type_code = 'SERVICE'
583 AND l_line_rec.service_reference_type_code='ORDER'
584 AND l_line_rec.service_reference_line_id IS NOT NULL
585 AND OE_ACCEPTANCE_UTIL.Acceptance_Status(l_line_rec.service_reference_line_id) = 'ACCEPTED')) THEN
586 IF l_debug_level > 0 THEN
587 oe_debug_pub.add('acceptance not required. item_type:'||l_line_rec.item_type_code);
588 END IF;
589 ELSE
590 l_pending_acceptance:= 'Y';
591 END IF;
592 END IF;
593 END IF;
594
595 --Customer Acceptance
596
597 --
598 -- Bug # 4454055
599 -- Close_Line is changed to call the costing API to move COGS account from
600 -- deferred to actual account for the order lines that need to notify costing
601 --
602
603 IF oe_cogs_grp.is_revenue_event_line(p_line_id) = 'Y' AND l_pending_acceptance = 'N' THEN
604
605 SELECT inventory_item_id, org_id
606 INTO l_inventory_item_id, l_org_id
607 FROM oe_order_lines_all
608 WHERE line_id = p_line_id;
609
610
611 cst_revenuecogsmatch_grp.receive_closelineevent (
612 p_api_version => 1.0,
613 p_init_msg_list => FND_API.G_FALSE,
614 p_commit => FND_API.G_FALSE,
615 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
616 x_return_status => l_return_status,
617 x_msg_count => x_msg_count,
618 x_msg_data => x_msg_data,
619 p_revenue_event_line_id => p_line_id,
620 p_event_date => SYSDATE,
621 p_ou_id => l_org_id,
622 p_inventory_item_id => l_inventory_item_id);
623
624 END IF;
625
626 -- Check Return status and error handling
627 -- Costing will raise an error if required parameters are not passed
628 -- Also if there is any unexpected error, close line workflow activity would be in
629 -- Incomplete status and will rerun automatically, we will mark the flow_status_code
630 -- as 'NOTIFY_COSTING_ERROR';
631
632 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
633
634 UPDATE oe_order_lines_all
635 SET flow_status_code='NOTIFY_COSTING_ERROR'
636 WHERE line_id = p_line_id;
637 x_return_status := l_return_status;
638 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 return;
640 END IF;
641
642
643 -- Check whether we need to call Process_Requests_And_Notify.
644
645 IF ( (OE_GLOBALS.G_RECURSION_MODE = 'Y') AND
646 (OE_GLOBALS.G_EC_INSTALLED <> 'Y') AND
647 (OE_GLOBALS.G_ASO_INSTALLED <> 'Y') AND
648 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'N') )
649 THEN
650 --Customer Acceptance
651 IF l_pending_acceptance = 'Y' THEN
652
653 UPDATE oe_order_lines
654 SET flow_status_code = 'POST-BILLING_ACCEPTANCE'
655 , last_updated_by = NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID)
656 , last_update_login = FND_GLOBAL.LOGIN_ID
657 , last_update_date = SYSDATE
658 , lock_control = l_old_line_tbl(1).lock_control + 1
659 WHERE line_id = p_line_id;
660
661 -- Added for bug 5988559
662 IF SQL%NOTFOUND THEN
663
664 IF l_debug_level > 0 THEN
665 oe_debug_pub.add( 'ORG CONTEXT is not properly set for'||p_line_id , 1) ;
666 END IF;
667 RAISE FND_API.G_EXC_ERROR;
668
669 END IF;
670 ELSE
671 --Customer Acceptance
672 UPDATE oe_order_lines
673 SET open_flag = 'N'
674 , calculate_price_flag = 'N'
675 , flow_status_code = DECODE(l_old_line_tbl(1).cancelled_flag,'Y',
676 'CANCELLED','CLOSED')
677 , last_updated_by = NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID) -- 3169637
678 , last_update_login = FND_GLOBAL.LOGIN_ID
679 , last_update_date = SYSDATE
680 , lock_control = l_old_line_tbl(1).lock_control + 1
681 WHERE line_id = p_line_id;
682
683 -- Added for bug 5988559
684 IF SQL%NOTFOUND THEN
685
686 IF l_debug_level > 0 THEN
687 oe_debug_pub.add( 'ORG CONTEXT is not properly set for'||p_line_id , 1) ;
688 END IF;
689 RAISE FND_API.G_EXC_ERROR;
690
691 END IF;
692
693
694 END IF;
695
696 ELSE
697 --Customer Acceptance Start
698 IF l_pending_acceptance= 'Y' THEN
699
700 l_line_tbl(1) := l_old_line_tbl(1);
701 l_line_tbl(1).flow_status_code := 'POST-BILLING_ACCEPTANCE';
702 l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);
703 l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
704 l_line_tbl(1).last_update_date := SYSDATE;
705 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
706 ELSE
707 --Customer Acceptance End
708 l_line_tbl(1) := l_old_line_tbl(1);
709 l_line_tbl(1).open_flag := 'N';
710 l_line_tbl(1).calculate_price_flag := 'N';
711 IF l_line_tbl(1).cancelled_flag = 'Y' THEN
712 l_line_tbl(1).flow_status_code := 'CANCELLED';
713 ELSE
714 l_line_tbl(1).flow_status_code := 'CLOSED';
715 END IF;
716 l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);
717 l_line_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
718 l_line_tbl(1).last_update_date := SYSDATE;
719 l_line_tbl(1).lock_control := l_line_tbl(1).lock_control + 1;
720 END IF;
721
722 UPDATE oe_order_lines
723 SET open_flag = l_line_tbl(1).open_flag
724 , calculate_price_flag = l_line_tbl(1).calculate_price_flag
725 , flow_status_code = l_line_tbl(1).flow_status_code
726 , last_updated_by = l_line_tbl(1).last_updated_by
727 , last_update_login = l_line_tbl(1).last_update_login
728 , last_update_date = l_line_tbl(1).last_update_date
729 , lock_control = l_line_tbl(1).lock_control
730 WHERE line_id = p_line_id;
731
732 -- Added for bug 5988559
733 IF SQL%NOTFOUND THEN
734
735 IF l_debug_level > 0 THEN
736 oe_debug_pub.add( 'ORG CONTEXT is not properly set for'||p_line_id , 1) ;
737 END IF;
738 RAISE FND_API.G_EXC_ERROR;
739
740 END IF;
741
742
743 -- jolin start
744 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
745
746 -- call notification framework to get this line's index position
747 OE_ORDER_UTIL.Update_Global_Picture
748 (p_Upd_New_Rec_If_Exists =>FALSE
749 , p_line_rec => l_line_tbl(1)
750 , p_old_line_rec => l_old_line_tbl(1)
751 , p_line_id => l_line_tbl(1).line_id
752 , x_index => l_notify_index
753 , x_return_status => x_return_status);
754
755 IF l_debug_level > 0 THEN
756 oe_debug_pub.add( 'UPDATE_GLOBAL RET_STATUS FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || X_RETURN_STATUS , 1 ) ;
757 END IF;
758 IF l_debug_level > 0 THEN
759 oe_debug_pub.add( 'UPDATE_GLOBAL INDEX FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || L_NOTIFY_INDEX , 1 ) ;
760 END IF;
761
762 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
763 RAISE FND_API.G_EXC_ERROR;
764 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767
768 IF l_notify_index is not null then
769 -- modify Global Picture
770 OE_ORDER_UTIL.g_line_tbl(l_notify_index).open_flag:= l_line_tbl(1).open_flag;
771 OE_ORDER_UTIL.g_line_tbl(l_notify_index).calculate_price_flag:= l_line_tbl(1).calculate_price_flag;
772 OE_ORDER_UTIL.g_line_tbl(l_notify_index).flow_status_code:=l_line_tbl(1).flow_status_code;
773 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by:= l_line_tbl(1).last_updated_by;
774 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login:=l_line_tbl(1).last_update_login;
775 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date:=l_line_tbl(1).last_update_date;
776 OE_ORDER_UTIL.g_line_tbl(l_notify_index).lock_control:= l_line_tbl(1).lock_control;
777
778 IF l_debug_level > 0 THEN
779 oe_debug_pub.add( 'GLOBAL LINE OPEN_FLAG IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .OPEN_FLAG , 1 ) ;
780 END IF;
781 IF l_debug_level > 0 THEN
782 oe_debug_pub.add( 'GLOBAL LINE CALCULATE_PRICE_FLAG IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .CALCULATE_PRICE_FLAG , 1 ) ;
783 END IF;
784 IF l_debug_level > 0 THEN
785 oe_debug_pub.add( 'GLOBAL LINE FLOW_STATUS_CODE IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .FLOW_STATUS_CODE , 1 ) ;
786 END IF;
787
788
789 -- Process requests is TRUE, but don't need to notify
790 IF l_debug_level > 0 THEN
791 oe_debug_pub.add( 'OEXPVPMB: BEFORE CALLING PROCESS_REQUESTS_AND_NOTIFY' ) ;
792 END IF;
793 OE_Order_PVT.Process_Requests_And_Notify
794 ( p_process_requests => TRUE
795 , p_notify => FALSE
796 , p_process_ack => FALSE
797 , x_return_status => x_return_status
798 , p_line_tbl => l_line_tbl
799 , p_old_line_tbl => l_old_line_tbl
800 );
801 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
802 RAISE FND_API.G_EXC_ERROR;
803 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
805 END IF;
806 END IF ; /* global entity index null check */
807
808 ELSE /* in pre-pack H code */
809
810 -- Need to both notify and process requests in old framework
811 IF l_debug_level > 0 THEN
812 oe_debug_pub.add( 'OEXPVPMB: BEFORE CALLING PROCESS_REQUESTS_AND_NOTIFY' ) ;
813 END IF;
814 OE_Order_PVT.Process_Requests_And_Notify
815 ( p_process_requests => TRUE
816 , p_notify => TRUE
817 , p_process_ack => FALSE
818 , x_return_status => x_return_status
819 , p_line_tbl => l_line_tbl
820 , p_old_line_tbl => l_old_line_tbl
821 );
822 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
823 RAISE FND_API.G_EXC_ERROR;
824 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
825 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
826 END IF;
827 END IF; /* code set is pack H or higher */
828 -- jolin end
829
830 END IF; -- we should notify after we update
831
832 IF l_pending_acceptance = 'Y' THEN
833 x_return_status := 'C';
834 END IF;
835
836 --Customer Acceptance
837 IF l_pending_acceptance = 'N' THEN
838 DELETE_ADJUSTMENTS(p_line_id => p_line_id);
839 OE_MSG_PUB.Reset_Msg_Context('LINE');
840 END IF;
841
842 /********************GENESIS********************************
843 * Some statuses are not going through process order and *
844 * the update_flow_status is getting called directly. So *
845 * we need to call synch_header_line for 28 *
846 ***********************************************************/
847 IF l_debug_level > 0 THEN
848 oe_debug_pub.add( ' GENESIS : CLOSE LINE - header rec order source'||p_line_id);
849 oe_debug_pub.add( ' GENESIS : CLOSE LINE - line rec order source'||l_line_tbl(1).order_source_id);
850 oe_debug_pub.add( ' GENESIS : CLOSE LINE - old line rec order source'||l_old_line_tbl(1).order_source_id);
851 oe_debug_pub.add( ' GENESIS : CLOSE LINE - old line rec order source'||l_line_tbl(1).order_source_id);
852 oe_debug_pub.add( ' GENESIS : CLOSE LINE - new line rec flow status'||l_line_tbl(1).flow_status_code);
853 oe_debug_pub.add( ' GENESIS : CLOSE LINE - old line rec flow status'||l_old_line_tbl(1).flow_status_code);
854 END IF;
855 IF (OE_GENESIS_UTIL.source_aia_enabled(l_old_line_tbl(1).order_source_id)) THEN
856 /*
857 oe_line_util.query_row(
858 p_line_id => p_line_id
859 ,x_line_rec => l_line_tbl(1)
860 );
861 */
862 OE_Header_UTIL.Query_Row
863 (p_header_id => l_old_line_tbl(1).header_id
864 ,x_header_rec => l_header_rec
865 );
866
867 select OE_XML_MESSAGE_SEQ_S.nextval
868 into l_itemkey_sso
869 from dual;
870 IF l_debug_level > 0 THEN
871 oe_debug_pub.add( ' GENESIS : CLOSE LINE - l_itemkey_sso'||l_itemkey_sso);
872 END IF;
873
874 OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec => l_line_tbl(1),
875 p_change_type => 'LINE_STATUS',
876 p_req_id => l_itemkey_sso,
877 X_RETURN_STATUS => L_RETURN_STATUS_GEN);
878
879 IF l_debug_level > 0 THEN
880 oe_debug_pub.add( ' GENESIS :CLOSE LINE: RETURN STATUS'||l_return_status_gen);
881 END IF;
882 IF l_return_status_gen = FND_API.G_RET_STS_SUCCESS THEN
883 OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE( p_header_rec => l_header_rec
884 ,p_line_rec => null
885 ,p_hdr_req_id => l_itemkey_sso
886 ,p_lin_req_id => l_itemkey_sso
887 ,p_change_type => 'LINE_STATUS');
888 END IF;
889 END IF;
890 -- GENESIS
891
892 EXCEPTION
893 WHEN ON_HOLD_ERROR THEN
894 x_return_status := 'H';
895 OE_MSG_PUB.Reset_Msg_Context('LINE');
896 ROLLBACK TO CLOSE_LINE;
897 WHEN FND_API.G_EXC_ERROR THEN
898 x_return_status := FND_API.G_RET_STS_ERROR;
899 OE_MSG_PUB.Reset_Msg_Context('LINE');
900 ROLLBACK TO CLOSE_LINE;
901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
903 OE_MSG_PUB.Reset_Msg_Context('LINE');
904 ROLLBACK TO CLOSE_LINE;
905 WHEN OTHERS THEN
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
907 IF FND_MSG_PUB.Check_Msg_Level
908 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
909 THEN
910 OE_MSG_PUB.Add_Exc_Msg
911 ( G_PKG_NAME
912 , 'Close_Line'
913 );
914 END IF;
915 OE_MSG_PUB.Reset_Msg_Context('LINE');
916 ROLLBACK TO CLOSE_LINE;
917 END CLOSE_LINE;
918
919 END OE_ORDER_CLOSE_UTIL;