1 PACKAGE BODY OE_ORDER_BOOK_UTIL AS
2 /* $Header: OEXUBOKB.pls 120.9.12010000.2 2008/09/25 12:17:33 msundara ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_ORDER_BOOK_UTIL';
6
7
8 /* LOCAL PROCEDURES */
9
10 -- LOCAL function: BookingIsDeferred
11 -- Called from Complete_Book_Eligible
12 -- Returns TRUE if booking has been deferred for this itemkey
13 -- and also populates a message to inform the user that booking
14 -- has been deferred.
15 ---------------------------------------------------------------
16 FUNCTION BookingIsDeferred
17 (p_itemkey IN VARCHAR2
18 )
19 RETURN BOOLEAN
20 IS
21 l_book_deferred VARCHAR2(1);
22 CURSOR book_deferred IS
23 SELECT 'Y'
24 FROM WF_ITEM_ACTIVITY_STATUSES WIAS
25 , WF_PROCESS_ACTIVITIES WPA
26 WHERE WIAS.item_type = 'OEOH'
27 AND WIAS.item_key = p_itemkey
28 AND WIAS.activity_status = 'DEFERRED'
29 AND WPA.activity_name = 'BOOK_DEFER'
30 AND WPA.instance_id = WIAS.process_activity;
31 --
32 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
33 --
34 BEGIN
35 IF l_debug_level > 0 THEN
36 oe_debug_pub.add( 'CHECK IF BOOKING IS DEFERRED' ) ;
37 END IF;
38
39 OPEN book_deferred;
40 FETCH book_deferred INTO l_book_deferred;
41
42 IF (book_deferred%FOUND) THEN
43 IF l_debug_level > 0 THEN
44 oe_debug_pub.add( 'BOOKING IS DEFERRED' ) ;
45 END IF;
46 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_BOOK_DEFERRED');
47 OE_MSG_PUB.ADD;
48 CLOSE book_deferred;
49 RETURN TRUE;
50 END IF;
51
52 CLOSE book_deferred;
53 RETURN FALSE;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 if (book_deferred%isopen) then
58 close book_deferred;
59 end if;
60 RAISE;
61
62 END BookingIsDeferred;
63
64 ---------------------------------------------------------------
65 PROCEDURE Pricing_Book_Event
66 (p_x_line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type
67 ,p_header_id IN NUMBER
68 ,x_return_status OUT NOCOPY VARCHAR2
69
70 )
71 IS
72 l_price_control_rec QP_PREQ_GRP.control_record_type;
73 l_request_rec oe_order_pub.request_rec_type;
74 --
75 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
76 --
77 BEGIN
78 -- Oe_Debug_pub.Add('Before Book_Pricing_Event');
79 l_Price_Control_Rec.pricing_event := 'BOOK';
80 l_Price_Control_Rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
81 l_Price_Control_Rec.Simulation_Flag := 'N';
82
83 oe_order_adj_pvt.Price_line(
84 X_Return_Status => x_Return_Status
85 ,p_Header_id => p_header_id
86 ,p_Request_Type_code=> 'ONT'
87 ,p_Control_rec => l_Price_Control_Rec
88 ,p_write_to_db => TRUE
89 ,p_request_rec => l_request_rec
90 ,x_line_Tbl => p_x_Line_Tbl
91 );
92 -- Oe_Debug_pub.Add('After Book_Pricing_Event');
93
94 END Pricing_Book_Event;
95
96
97 ---------------------------------------------------------------
98 PROCEDURE Update_Booked_Flag
99 (p_header_id IN NUMBER
100 ,x_validate_cfg OUT NOCOPY BOOLEAN
101
102 ,x_freeze_inc_items OUT NOCOPY BOOLEAN
103
104 ,x_return_status OUT NOCOPY VARCHAR2
105
106 )
107 IS
108 l_index NUMBER := 1;
109 l_new_index NUMBER := 1;
110 l_loop_index NUMBER := 1; -- jolin
111 l_notify_index NUMBER; -- jolin
112 l_line_id NUMBER;
113 l_header_rec OE_ORDER_PUB.Header_Rec_Type;
114 l_old_header_rec OE_ORDER_PUB.Header_Rec_Type;
115 l_line_tbl OE_ORDER_PUB.Line_TBL_Type;
116 l_old_line_tbl OE_ORDER_PUB.Line_TBL_Type;
117 l_msg_count NUMBER;
118 l_active_phase_count NUMBER;
119 l_msg_data VARCHAR2(2000);
120 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
121 l_tax_calculation_event_code varchar2(30) := NULL;
122 l_notify BOOLEAN := FALSE;
123 l_in_loop_index NUMBER := 1;
124 l_line_adj_tbl OE_ORDER_PUB.LINE_ADJ_TBL_Type;
125 l_populate_adj BOOLEAN := FALSE;
126 l_global_index NUMBER;
127 l_line_adj_index NUMBER;
128
129 CURSOR Query_Lines IS
130 SELECT line_id
131 , booked_flag
132 , sold_to_org_id
133 , invoice_to_org_id
134 , ship_to_org_id
135 , tax_exempt_flag
136 , inventory_item_id
137 , order_quantity_uom
138 , ordered_quantity
139 , line_category_code
140 , item_type_code
141 , price_list_id
142 , unit_list_price
143 , unit_selling_price
144 , payment_term_id
145 , ship_from_org_id
146 , request_date
147 , line_type_id
148 , tax_date
149 , tax_code
150 , service_duration
151 , reference_line_id
152 , cancelled_flag
153 , orig_sys_document_ref
154 , orig_sys_line_ref
155 , source_document_id
156 , source_document_line_id
157 , service_coterminate_flag
158 , service_reference_type_code
159 , service_start_date
160 , service_end_date
161 , service_period
162 , header_id /* renga */
163 , org_id
164 , return_context
165 , reference_customer_trx_line_id /* end renga */
166 , order_firmed_date /* Key Transaction Dates */
167 FROM OE_ORDER_LINES
168 WHERE HEADER_ID = p_header_id;
169
170 --
171 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
172 --
173 BEGIN
174
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176 x_validate_cfg := FALSE;
177 x_freeze_inc_items := FALSE;
178
179 -- NOTE: Process order is called twice, once to update the booked flag
180 -- on the order header and the second time to update the booked flag
181 -- on all lines on the order. This cannot be done in one call as the
182 -- process order does not process lines(or any line level validation)
183 -- if the header validation fails. In order to give user complete
184 -- feedback i.e. validation errors for lines also if any, process order
185 -- is called for lines separately even if header update returns with
186 -- a status of ERROR.
187
188
189 -- Set up the header record
190
191 OE_Header_Util.Query_Row
192 (p_header_id => p_header_id
193 ,x_header_rec => l_old_header_rec
194 );
195 l_header_rec := l_old_header_rec;
196 l_header_rec.booked_flag := 'Y';
197 l_header_rec.booked_date := sysdate;
198 l_header_rec.flow_status_code := 'BOOKED';
199 l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
200 l_header_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
201 l_header_rec.last_update_date := SYSDATE;
202 l_header_rec.lock_control := l_header_rec.lock_control + 1;
203
204 -- bug 1406890
205 -- renga: change for tax calculation event enhancement
206 BEGIN
207
208 IF l_header_rec.order_type_id is not null THEN
209 SELECT TAX_CALCULATION_EVENT_CODE
210 into l_tax_calculation_event_code
211 from oe_transaction_types_all
212 where transaction_type_id = l_header_rec.order_type_id;
213 END IF;
214
215 EXCEPTION
216 when no_data_found then
217 l_tax_calculation_event_code := NULL;
218 when others then
219 IF l_debug_level > 0 THEN
220 oe_debug_pub.add( 'REN: FAILED WHILE TRYING TO QUERY UP TAX_CALCUALTION_EVENT FOR ORDER_TYPE_ID IN UPDATE_BOOKED_FLAG' ) ;
221 END IF;
222 RAISE;
223 END;
224 -- renga: end of change for tax calculation event enhancement
225
226
227 -- Header booking validation
228
229 OE_MSG_PUB.set_msg_context(
230 p_entity_code => 'HEADER'
231 ,p_entity_id => l_header_rec.header_id
232 ,p_header_id => l_header_rec.header_id
233 ,p_line_id => null
234 ,p_order_source_id => l_header_rec.order_source_id
235 ,p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref
236 ,p_orig_sys_document_line_ref => null
237 ,p_change_sequence => l_header_rec.change_sequence
238 ,p_source_document_type_id => l_header_rec.source_document_type_id
239 ,p_source_document_id => l_header_rec.source_document_id
240 ,p_source_document_line_id => null );
241
242 OE_Validate_Header.Check_Book_Reqd_Attributes
243 (p_header_rec => l_header_rec
244 ,x_return_status => l_return_status
245 );
246
247 OE_MSG_PUB.reset_msg_context('HEADER');
248
249 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
250 x_return_status := FND_API.G_RET_STS_ERROR;
251 -- if unexpected error, then do NOT validate lines.
252 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254 RETURN;
255 END IF;
256
257 -- Check whether we need to call the Pricing at BOOKING
258 SELECT count(*)
259 INTO l_active_phase_count
260 FROM QP_EVENT_PHASES
261 WHERE pricing_event_code = 'BOOK'
262 AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
263 trunc(nvl(end_date_active, sysdate));
264
265 -- Check if the ASO and EC are installed products
266 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
267 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
268 END IF;
269
270 IF OE_GLOBALS.G_EC_INSTALLED IS NULL THEN
271 OE_GLOBALS.G_EC_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(175);
272 END IF;
273
274 -- We need to call the Query_Rows only if Pricing is called and ASO/EC are
275 -- Installed products.
276
277 IF ( (NVL(l_active_phase_count,0) > 0) OR
278 (OE_GLOBALS.G_ASO_INSTALLED = 'Y') OR
279 (OE_GLOBALS.G_EC_INSTALLED = 'Y' ) OR
280 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y') )
281 THEN
282 -- Set up the lines table of records
283
284 OE_Line_Util.Query_Rows
285 (p_header_id => p_header_id
286 ,x_line_tbl => l_old_line_tbl
287 );
288 ELSE
289 l_index := 1;
290
291 OPEN Query_Lines;
292 LOOP
293
294 FETCH Query_Lines INTO
295 l_old_line_tbl(l_index).line_id
296 , l_old_line_tbl(l_index).booked_flag
297 , l_old_line_tbl(l_index).sold_to_org_id
298 , l_old_line_tbl(l_index).invoice_to_org_id
299 , l_old_line_tbl(l_index).ship_to_org_id
300 , l_old_line_tbl(l_index).tax_exempt_flag
301 , l_old_line_tbl(l_index).inventory_item_id
302 , l_old_line_tbl(l_index).order_quantity_uom
303 , l_old_line_tbl(l_index).ordered_quantity
304 , l_old_line_tbl(l_index).line_category_code
305 , l_old_line_tbl(l_index).item_type_code
306 , l_old_line_tbl(l_index).price_list_id
307 , l_old_line_tbl(l_index).unit_list_price
308 , l_old_line_tbl(l_index).unit_selling_price
309 , l_old_line_tbl(l_index).payment_term_id
310 , l_old_line_tbl(l_index).ship_from_org_id
311 , l_old_line_tbl(l_index).request_date
312 , l_old_line_tbl(l_index).line_type_id
313 , l_old_line_tbl(l_index).tax_date
314 , l_old_line_tbl(l_index).tax_code
315 , l_old_line_tbl(l_index).service_duration
316 , l_old_line_tbl(l_index).reference_line_id
317 , l_old_line_tbl(l_index).cancelled_flag
318 , l_old_line_tbl(l_index).orig_sys_document_ref
319 , l_old_line_tbl(l_index).orig_sys_line_ref
320 , l_old_line_tbl(l_index).source_document_id
321 , l_old_line_tbl(l_index).source_document_line_id
322 , l_old_line_tbl(l_index).service_coterminate_flag
323 , l_old_line_tbl(l_index).service_reference_type_code
324 , l_old_line_tbl(l_index).service_start_date
325 , l_old_line_tbl(l_index).service_end_date
326 , l_old_line_tbl(l_index).service_period
327 , l_old_line_tbl(l_index).header_id /* renga */
328 , l_old_line_tbl(l_index).org_id
329 , l_old_line_tbl(l_index).return_context
330 , l_old_line_tbl(l_index).reference_customer_trx_line_id
331 /* end renga */
332 , l_old_line_tbl(l_index).order_firmed_date; /*key transaction dates */
333
334 EXIT WHEN (Query_Lines%NOTFOUND);
335
336 l_index := l_index + 1;
337
338 END LOOP;
339
340 CLOSE Query_Lines;
341
342 END IF;
343
344
345 -- (1) Lines Booking Validation
346 l_line_tbl := l_old_line_tbl;
347 l_index := l_line_tbl.FIRST;
348
349 WHILE l_index IS NOT NULL LOOP
350
351 -- for non-cancelled lines, do the booking validation and set
352 -- the booked_flag and flow_status on the new line records
353 IF nvl(l_line_tbl(l_index).cancelled_flag,'N') = 'N' THEN
354
355 IF l_line_tbl(l_index).item_type_code = 'STANDARD' THEN
356 NULL;
357 ELSIF l_line_tbl(l_index).item_type_code = 'MODEL' THEN
358 x_validate_cfg := TRUE;
359 x_freeze_inc_items := TRUE;
360 ELSIF l_line_tbl(l_index).item_type_code in ('CLASS','KIT') THEN
361 x_freeze_inc_items := TRUE;
362 END IF;
363
364 l_return_status := FND_API.G_RET_STS_SUCCESS;
365 l_line_tbl(l_index).operation := OE_GLOBALS.G_OPR_UPDATE;
366 l_line_tbl(l_index).booked_flag := 'Y';
367 l_line_tbl(l_index).flow_status_code := 'BOOKED';
368 l_line_tbl(l_index).last_updated_by := FND_GLOBAL.USER_ID;
369 l_line_tbl(l_index).last_update_login := FND_GLOBAL.LOGIN_ID;
370 l_line_tbl(l_index).last_update_date := SYSDATE;
371 l_line_tbl(l_index).lock_control := l_line_tbl(l_index).lock_control + 1;
372
373 OE_MSG_PUB.set_msg_context(
374 p_entity_code => 'LINE'
375 ,p_entity_id => l_line_tbl(l_index).line_id
376 ,p_header_id => p_header_id
377 ,p_line_id => l_line_tbl(l_index).line_id
378 ,p_order_source_id => l_line_tbl(l_index).order_source_id
379 ,p_orig_sys_document_ref => l_line_tbl(l_index).orig_sys_document_ref
380 ,p_orig_sys_document_line_ref => l_line_tbl(l_index).orig_sys_line_ref
381 ,p_orig_sys_shipment_ref => l_line_tbl(l_index).orig_sys_shipment_ref
382 ,p_change_sequence => l_line_tbl(l_index).change_sequence
383 ,p_source_document_type_id => l_line_tbl(l_index).source_document_type_id
384 ,p_source_document_id => l_line_tbl(l_index).source_document_id
385 ,p_source_document_line_id => l_line_tbl(l_index).source_document_line_id );
386
387 OE_Validate_Line.Check_Book_Reqd_Attributes
388 (p_line_rec => l_line_tbl(l_index)
389 ,p_old_line_rec => l_old_line_tbl(l_index)
390 ,x_return_status => l_return_status
391 );
392
393 OE_MSG_PUB.reset_msg_context('LINE');
394
395 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
396 x_return_status := FND_API.G_RET_STS_ERROR;
397 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
399 RETURN;
400 END IF;
401
402 -- bug 1406890
403 -- Renga - log a delayed request for calculating tax for
404 -- each of the order lines
405
406 IF l_tax_calculation_event_code = 'BOOKING' THEN
407
408 IF l_line_tbl(l_index).item_type_code not in ('INCLUDED', 'CONFIG') THEN
409
410 OE_delayed_requests_Pvt.log_request(
411 p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
412 p_entity_id => l_line_tbl(l_index).line_id,
413 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_LINE,
414 p_requesting_entity_id => l_line_tbl(l_index).line_id,
415 p_request_type => OE_GLOBALS.g_tax_line,
416 x_return_status => l_return_status);
417
418 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
419 x_return_status := FND_API.G_RET_STS_ERROR;
420 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 RETURN;
423 END IF;
424
425 END IF; -- if item_type_code not in INCLUDED or CONFIG
426
427 END IF; -- if tax_calculation_event is booking
428
429
430 -- end of bug 1406890
431
432
433 -- delete the cancelled lines from the lines table, these do
434 -- not need to be sent to notify_oc
435 ELSE
436 l_old_line_tbl.DELETE(l_index);
437 l_line_tbl.DELETE(l_index);
438 END IF;
439
440 l_index := l_line_tbl.NEXT(l_index);
441
442 END LOOP;
443
444 -- Return error if there were validation errors for the header/line
445 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448
449
450 -- Update the booked_flag and flow_status on the tables
451 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
452
453 UPDATE oe_order_headers_all
454 SET booked_flag = 'Y'
455 , booked_date = sysdate
456 , flow_status_code = 'BOOKED'
457 , last_updated_by = FND_GLOBAL.USER_ID
458 , last_update_login = FND_GLOBAL.LOGIN_ID
459 , last_update_date = SYSDATE
460 , lock_control = lock_control + 1
461 WHERE header_id = p_header_id;
462
463 -- aksingh performance
464 -- As the update is on headers table, it is time to update
465 -- cache also!
466 OE_Order_Cache.Set_Order_Header(l_header_rec);
467
468 -- Clear cached results for constraints as order being booked
469 -- may change results of some validation packages
470 OE_PC_Constraints_Admin_PVT.Clear_Cached_Results;
471
472 UPDATE oe_order_lines_all
473 SET booked_flag = 'Y'
474 , flow_status_code = 'BOOKED'
475 , last_updated_by = FND_GLOBAL.USER_ID
476 , last_update_login = FND_GLOBAL.LOGIN_ID
477 , last_update_date = SYSDATE
478 , lock_control = lock_control + 1
479 WHERE header_id = p_header_id
480 AND nvl(cancelled_flag,'N') <> 'Y'; -- nvl added for bug 4486781
481
482 END IF;
483
484
485
486 -- Evaluate the BOOK event for pricing
487 -- Call Pricing only if there are active phases in BOOK event.
488 /*
489 IF l_active_phase_count > 0 THEN
490 Pricing_Book_Event(p_x_line_tbl => l_line_tbl
491 , p_header_id => p_header_id
492 , x_return_status => l_return_status
493 );
494 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
495 RAISE FND_API.G_EXC_ERROR;
496 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498 END IF;
499 END IF;
500 */
501
502 -- (4) API to call notify_oc and ack with l_header_rec and l_old_header_rec
503 -- and l_line_tbl and l_old_line_tbl
504 -- bug 1406890
505 -- renga - change for tax calculation event enhancement
506
507 l_notify := FALSE;
508
509 IF ( (OE_GLOBALS.G_ASO_INSTALLED = 'Y') OR
510 (OE_GLOBALS.G_EC_INSTALLED = 'Y' ) OR
511 (NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') = 'Y') )
512 THEN
513 l_notify := TRUE;
514
515 /* jolin start */
516 -- AND we need to update the global picture before calling process_requests_and_notify
517 -- if we are using the new notification method
518
519 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
520
521 -- Need to call Update_Global_Picture to update globals on direct changes
522 -- made in the two update stmts to the headers and lines tables.
523 -- First make call for the header, then loop through all the lines
524
525 -- bug 2821129
526 oe_debug_pub.add('in OEXUBOKB header will do query');
527 oe_debug_pub.add('p_header_id is '|| p_header_id);
528 oe_debug_pub.add('l_header_rec.header_id is '|| l_header_rec.header_id);
529 OE_HEADER_ADJ_UTIL.Query_Rows
530 (p_header_id => p_header_id
531 ,x_Header_adj_Tbl => OE_ORDER_UTIL.g_header_adj_tbl);
532
533
534 -- loop to populate the operation in the global table
535 l_in_loop_index := OE_ORDER_UTIL.g_header_adj_tbl.FIRST;
536
537 while l_in_loop_index is not null loop
538 OE_ORDER_UTIL.g_header_adj_tbl(l_in_loop_index).operation := OE_GLOBALS.G_OPR_CREATE;
539 l_in_loop_index := OE_ORDER_UTIL.g_header_adj_tbl.NEXT(l_in_loop_index);
540 END LOOP;
541
542 oe_debug_pub.add('in OEXUBOKB header adj tbl count is '|| OE_ORDER_UTIL.g_header_adj_tbl.count);
543
544 IF l_debug_level > 0 THEN
545 oe_debug_pub.add( 'BEFORE ENTERING UPDATE_GLOBAL_PICTURE IN BOOKING' ) ;
546 END IF;
547 -- call notification framework to get header index position
548 IF l_debug_level > 0 THEN
549 oe_debug_pub.add( 'BEFORE UPDATE , HEADER VALUE' || OE_ORDER_UTIL.G_HEADER_REC.HEADER_ID ) ;
550 END IF;
551 IF l_debug_level > 0 THEN
552 oe_debug_pub.add( 'BEFORE UPDATE , OLD HEADER VALUE' || OE_ORDER_UTIL.G_OLD_HEADER_REC.HEADER_ID ) ;
553 END IF;
554 OE_ORDER_UTIL.Update_Global_Picture
555 (p_Upd_New_Rec_If_Exists =>FALSE
556 , p_header_rec => l_header_rec
557 , p_old_header_rec => l_old_header_rec
558 , p_header_id => l_header_rec.header_id
559 , x_index => l_notify_index
560 , x_return_status => l_return_status);
561
562 IF l_debug_level > 0 THEN
563 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FOR HDR IS: ' || L_RETURN_STATUS ) ;
564 END IF;
565 IF l_debug_level > 0 THEN
566 oe_debug_pub.add( 'HDR INDEX IS: ' || L_NOTIFY_INDEX , 1 ) ;
567 END IF;
568
569 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
570 RAISE FND_API.G_EXC_ERROR;
571 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
573 END IF;
574
575 IF l_notify_index is not null then
576 -- modify Global Picture
577 IF l_debug_level > 0 THEN
578 oe_debug_pub.add( 'JPN: OLD GLOBAL HDR REC BOOKED_FLAG IS:' || OE_ORDER_UTIL.G_OLD_HEADER_REC.BOOKED_FLAG , 1 ) ;
579 END IF;
580 OE_ORDER_UTIL.g_old_header_rec := l_old_header_rec;
581 OE_ORDER_UTIL.g_header_rec := OE_ORDER_UTIL.g_old_header_rec;
582 OE_ORDER_UTIL.g_old_header_rec.booked_flag := 'N';
583 OE_ORDER_UTIL.g_old_header_rec.booked_date := NULL;
584 OE_ORDER_UTIL.g_header_rec.booked_flag := l_header_rec.booked_flag;
585 OE_ORDER_UTIL.g_header_rec.booked_date:= l_header_rec.booked_date;
586 OE_ORDER_UTIL.g_header_rec.flow_status_code:=l_header_rec.flow_status_code;
587 OE_ORDER_UTIL.g_header_rec.last_updated_by:=l_header_rec.last_updated_by;
588 OE_ORDER_UTIL.g_header_rec.last_update_login:=l_header_rec.last_update_login;
589 OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
590 OE_ORDER_UTIL.g_header_rec.lock_control:= l_header_rec.lock_control;
591 OE_ORDER_UTIL.g_header_rec.operation:= l_header_rec.operation;
592
593 IF l_debug_level > 0 THEN
594 oe_debug_pub.add( 'JYOTHI:GLOBAL HDR REC BOOKED_FLAG IS: ' || OE_ORDER_UTIL.G_HEADER_REC.BOOKED_FLAG , 1 ) ;
595 END IF;
596 IF l_debug_level > 0 THEN
597 oe_debug_pub.add( 'GLOBAL HDR BOOKED_DATE IS: ' || OE_ORDER_UTIL.G_HEADER_REC.BOOKED_DATE , 1 ) ;
598 END IF;
599 IF l_debug_level > 0 THEN
600 oe_debug_pub.add( 'GLOBAL HDR FLOW_STATUS_CODE IS: ' || OE_ORDER_UTIL.G_HEADER_REC.FLOW_STATUS_CODE , 1 ) ;
601 END IF;
602
603 END IF ; /* global entity index null check */
604
605 -- update lines global picture
606 -- loop over l_line_tbl using loop_index
607
608 l_loop_index := l_line_tbl.FIRST;
609
610 <<outer>>
611
612 WHILE l_loop_index IS NOT NULL LOOP
613 IF l_debug_level > 0 THEN
614 oe_debug_pub.add( 'JFC: IN BOOKING LINES , L_LOOP_INDEX= '||L_LOOP_INDEX , 1 ) ;
615 END IF;
616 -- call notification framework to get this line's index position
617 OE_ORDER_UTIL.Update_Global_Picture
618 (p_Upd_New_Rec_If_Exists =>FALSE
619 , p_line_rec => l_line_tbl(l_loop_index)
620 , p_line_id => l_line_tbl(l_loop_index).line_id
621 , x_index => l_notify_index
622 , x_return_status => l_return_status);
623
624 IF l_debug_level > 0 THEN
625 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS IN OE_ORDER_BOOK_UTIL FOR LINE_ID '||L_LINE_TBL ( L_LOOP_INDEX ) .LINE_ID ||' IS: ' || L_RETURN_STATUS , 1 ) ;
626 END IF;
627 IF l_debug_level > 0 THEN
628 oe_debug_pub.add( 'UPDATE_GLOBAL INDEX IN OE_ORDER_BOOK_UTIL FOR LINE_ID '||L_LINE_TBL ( L_LOOP_INDEX ) .LINE_ID ||' IS: ' || L_NOTIFY_INDEX , 1 ) ;
629 END IF;
630
631 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
632 RAISE FND_API.G_EXC_ERROR;
633 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635 END IF;
636
637 IF l_notify_index is not null then
638 -- modify Global Picture
639 OE_ORDER_UTIL.g_old_line_tbl(l_notify_index):= l_old_line_tbl(l_loop_index);
640 OE_ORDER_UTIL.g_line_tbl(l_notify_index):= OE_ORDER_UTIL.g_old_line_tbl(l_notify_index);
641 OE_ORDER_UTIL.g_old_line_tbl(l_notify_index).booked_flag:='N';
642
643 OE_ORDER_UTIL.g_line_tbl(l_notify_index).line_id:= l_line_tbl(l_loop_index).line_id;
644 OE_ORDER_UTIL.g_line_tbl(l_notify_index).booked_flag:= l_line_tbl(l_loop_index).booked_flag;
645 OE_ORDER_UTIL.g_line_tbl(l_notify_index).flow_status_code:=l_line_tbl(l_loop_index).flow_status_code;
646 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by:= l_line_tbl(l_loop_index).last_updated_by;
647 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login:=l_line_tbl(l_loop_index).last_update_login;
648 OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date:=l_line_tbl(l_loop_index).last_update_date;
649 OE_ORDER_UTIL.g_line_tbl(l_notify_index).lock_control:= l_line_tbl(l_loop_index).lock_control;
650 OE_ORDER_UTIL.g_line_tbl(l_notify_index).operation:=l_line_tbl(l_loop_index).operation;
651
652 IF l_debug_level > 0 THEN
653 oe_debug_pub.add( 'IN BOOKING , AFTER UPDATE LINE GLOBAL PICTURE' ) ;
654 END IF;
655 IF l_debug_level > 0 THEN
656 oe_debug_pub.add( 'GLOBAL LINE BOOKED_FLAG IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .BOOKED_FLAG , 1 ) ;
657 END IF;
658 IF l_debug_level > 0 THEN
659 oe_debug_pub.add( 'GLOBAL LINE FLOW_STATUS_CODE IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .FLOW_STATUS_CODE , 1 ) ;
660 END IF;
661 IF l_debug_level > 0 THEN
662 oe_debug_pub.add( 'GLOBAL OLD LINE BOOKED_FLAG IS: ' || OE_ORDER_UTIL.G_OLD_LINE_TBL ( L_NOTIFY_INDEX ) .BOOKED_FLAG , 1 ) ;
663 END IF;
664 IF l_debug_level > 0 THEN
665 oe_debug_pub.add( 'GLOBAL LINE OPERATION IS: ' || OE_ORDER_UTIL.G_LINE_TBL ( L_NOTIFY_INDEX ) .OPERATION , 1 ) ;
666 END IF;
667 END IF ; /* global entity index null check */
668
669 -- bug 2821129
670
671 oe_debug_pub.add('p_header_id is '|| p_header_id);
672 oe_debug_pub.add('line_id is '|| l_line_tbl(l_loop_index).line_id);
673
674 oe_debug_pub.add('in OEXUBOKB start g line adj tbl count is '|| OE_ORDER_UTIL.g_line_adj_tbl.count);
675
676 OE_LINE_ADJ_UTIL.Query_Rows
677 (
678 p_line_id => l_line_tbl(l_loop_index).line_id
679 ,p_header_id => p_header_id
680 ,x_Line_Adj_Tbl => l_line_adj_tbl);
681
682
683 oe_debug_pub.add('in OEXUBOKB line adj tbl count from query row is '|| l_line_adj_tbl.count);
684
685
686 l_in_loop_index := l_line_adj_tbl.FIRST;
687
688 While l_in_loop_index is not NULL LOOP
689
690 OE_ORDER_UTIL.Update_Global_Picture
691 (p_Upd_New_Rec_If_Exists =>FALSE
692 , p_line_adj_id => l_line_adj_tbl(l_in_loop_index).price_adjustment_id
693 , x_index => l_notify_index
694 , x_return_status => l_return_status);
695
696 OE_ORDER_UTIL.g_line_adj_tbl(l_notify_index) := l_line_adj_tbl(l_in_loop_index);
697 OE_ORDER_UTIL.g_line_adj_tbl(l_notify_index).operation := OE_GLOBALS.G_OPR_CREATE;
698 oe_debug_pub.add('in OEXUBOKB after insert into global table, line_id is ' || OE_ORDER_UTIL.g_line_adj_tbl(l_notify_index).line_id);
699 oe_debug_pub.add('in OEXUBOKB after insert into global table, operation is ' || OE_ORDER_UTIL.g_line_adj_tbl(l_notify_index).operation);
700
701 l_in_loop_index := l_line_adj_tbl.NEXT(l_in_loop_index);
702
703 END LOOP;
704
705 oe_debug_pub.add('in OEXUBOKB at end g line adj tbl count is '|| OE_ORDER_UTIL.g_line_adj_tbl.count);
706
707 l_loop_index := l_line_tbl.NEXT(l_loop_index);
708
709 END LOOP outer; -- over each line to update global picture
710
711 -- notification framework end
712 -- no need to call process_requests_and_notify in new framework
713
714 ELSE -- ASO or EC are installed and we are using the old framework
715
716 -- Process request is set to FALSE here but this api will
717 -- be again called with process_requests = TRUE at the end
718 -- of the main Book_Order procedure. This will take care of
719 -- executing the tax requests that may have been logged in
720 -- this procedure.
721 OE_Order_PVT.Process_Requests_And_Notify
722 ( p_process_requests => FALSE
723 , p_notify => l_notify
724 , x_return_status => l_return_status
725 , p_header_rec => l_header_rec
726 , p_old_header_rec => l_old_header_rec
727 , p_line_tbl => l_line_tbl
728 , p_old_line_tbl => l_old_line_tbl
729 );
730
731 -- renga -end change for tax calculation event enhancement
732
733 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
734 RAISE FND_API.G_EXC_ERROR;
735 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 END IF;
738
739 END IF; /* code set is pack H or higher */
740 /* jolin end*/
741
742 END IF; -- ASO or EC are installed
743
744 /* Call the pricing event in the end after all the processing */
745
746 -- Evaluate the BOOK event for pricing
747 -- Call Pricing only if there are active phases in BOOK event.
748
749 IF l_active_phase_count > 0 THEN
750 Pricing_Book_Event(p_x_line_tbl => l_line_tbl
751 , p_header_id => p_header_id
752 , x_return_status => l_return_status
753 );
754 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
755 RAISE FND_API.G_EXC_ERROR;
756 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758 END IF;
759 END IF;
760
761 IF OE_FEATURES_PVT.Is_Margin_Avail THEN
762 --Evaluate margin hold;
763 Oe_Margin_Pvt.Margin_Hold(p_header_id);
764 END IF;
765
766 EXCEPTION
767 WHEN FND_API.G_EXC_ERROR THEN
768 x_return_status := FND_API.G_RET_STS_ERROR;
769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771 WHEN OTHERS THEN
772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773 IF FND_MSG_PUB.Check_Msg_Level
774 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
775 THEN
776 OE_MSG_PUB.Add_Exc_Msg
777 ( G_PKG_NAME
778 , 'Update_Booked_Flag'
779 );
780 END IF;
781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
782 END Update_Booked_Flag;
783
784 PROCEDURE Verify_Payment_AT_Booking
785 ( p_header_id IN NUMBER
786 , x_return_status OUT NOCOPY VARCHAR2
787
788 )
789 IS
790 l_msg_count NUMBER;
791 l_msg_data VARCHAR2(2000);
792 --
793 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
794 --
795 BEGIN
796
797 -- If Payment Verification Fails then the Order is
798 -- Automatically Put on a Credit Checking Hold or
799 -- a Credit Card Processing Hold.
800 IF l_debug_level > 0 THEN
801 oe_debug_pub.add( 'BEFORE CALLING VERIFY PAYMENT' ) ;
802 END IF;
803 --
804 OE_Verify_Payment_PUB.Verify_Payment
805 ( p_header_id => p_header_id
806 , p_calling_action => 'BOOKING'
807 , p_msg_count => l_msg_count
808 , p_msg_data => l_msg_data
809 , p_return_status => x_return_status
810 );
811
812 EXCEPTION
813 WHEN OTHERS THEN
814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
815 IF FND_MSG_PUB.Check_Msg_Level
816 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
817 THEN
818 OE_MSG_PUB.Add_Exc_Msg
819 ( G_PKG_NAME
820 , 'Verify_Payment_AT_Booking'
821 );
822 END IF;
823 END Verify_Payment_AT_Booking;
824
825 PROCEDURE Validate_Sales_Credits
826 ( p_header_id IN NUMBER
827 , x_return_status OUT NOCOPY VARCHAR2
828
829 )
830 IS
831 l_return_status VARCHAR2(30);
832 h_return_status VARCHAR2(30);
833 --
834 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
835 --
836 --FP bug 3872166
837 CURSOR line_ids(p_header_id IN NUMBER) IS
838 SELECT line_id
839 FROM oe_order_lines_all
840 WHERE header_id = p_header_id;
841 BEGIN
842 IF l_debug_level > 0 THEN
843 oe_debug_pub.add('Entering Oe_Order_Book_Util.Validate_sales_Credits' ) ;
844 END IF;
845
846 h_return_status := FND_API.G_RET_STS_SUCCESS;
847 -- Validate Header Sales Credits
848 OE_Validate_Header_Scredit.Validate_HSC_TOTAL_FOR_BK
849 ( p_header_id => p_header_id
850 , x_return_status => x_return_status );
851
852 IF l_debug_level > 0 THEN
853 oe_debug_pub.add( 'RETURN STATUS AFTER HSC:'||X_RETURN_STATUS ) ;
854 END IF;
855
856 -- FP bug 4697708
857 -- h_return_status := x_return_status;
858 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
859 h_return_status := x_return_status;
860 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
861 RETURN;
862 END IF;
863
864
865 l_return_status := FND_API.G_RET_STS_SUCCESS;
866
867 -- commented out for FP 4697708
868 /*
869 -- IF added for nocopy analysis
870 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
871 x_return_status := FND_API.G_RET_STS_ERROR;
872 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
874 RETURN ;
875 ELSE
876 x_return_status := FND_API.G_RET_STS_SUCCESS;
877 END IF;
878 */
879
880 --FP bug 3872166 start
881 FOR l_line_id IN line_ids(p_header_id) LOOP
882 OE_Validate_Line_Scredit.Validate_LSC_QUOTA_TOTAL
883 ( x_return_status => x_return_status
884 ,p_line_id => l_line_id.line_id);
885
886 IF l_debug_level > 0 THEN
887 oe_debug_pub.add( 'RETURN STATUS AFTER LSC FOR LINE '||l_line_id.line_id|| 'IS '||X_RETURN_STATUS ) ;
888 END IF;
889
890 -- FP bug 4697708
891 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
892 l_return_status := x_return_status;
893 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
894 RETURN;
895 END IF;
896 END LOOP;
897 --FP bug 3872166 end
898
899 IF l_debug_level > 0 THEN
900 oe_debug_pub.add( 'RETURN STATUS AFTER LSC:'||L_RETURN_STATUS ) ;
901 END IF;
902
903 -- FP bug 4697708
904 /*
905 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
906 l_return_status := x_return_status;
907 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
908 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
909 RETURN;
910 ELSE -- nocopy analysis
911 l_return_status := FND_API.G_RET_STS_SUCCESS;
912 END IF;
913 */
914
915 IF h_return_status = FND_API.G_RET_STS_ERROR OR l_return_status = FND_API.G_RET_STS_ERROR THEN
916 x_return_status := FND_API.G_RET_STS_ERROR;
917 ELSIF h_return_status = FND_API.G_RET_STS_UNEXP_ERROR OR l_return_status
918 = FND_API.G_RET_STS_UNEXP_ERROR THEN
919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
920 ELSE
921 x_return_status := FND_API.G_RET_STS_SUCCESS;
922 END IF;
923
924 IF l_debug_level > 0 THEN
925 oe_debug_pub.add('Exiting Oe_Order_Book_Util.Validate_sales_Credits: '||x_return_status ) ;
926 END IF;
927
928 EXCEPTION
929 WHEN OTHERS THEN
930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
931 IF FND_MSG_PUB.Check_Msg_Level
932 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
933 THEN
934 OE_MSG_PUB.Add_Exc_Msg
935 ( G_PKG_NAME
936 , 'Validate_Sales_Credits'
937 );
938 END IF;
939 END Validate_Sales_Credits;
940
941
942 /* PUBLIC PROCEDURES */
943
944 ---------------------------------------------------------------
945 PROCEDURE Check_Booking_Holds
946 (p_header_id IN NUMBER
947 ,x_return_status OUT NOCOPY VARCHAR2
948
949 )
950 IS
951 l_check_holds_result VARCHAR2(30);
952 l_msg_count NUMBER;
953 l_msg_data VARCHAR2(2000);
954 l_dummy VARCHAR2(30);
955 p_hold_rec OE_HOLDS_PUB.any_line_hold_rec;
956
957 --
958 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
959 --
960 BEGIN
961
962 IF l_debug_level > 0 THEN
963 oe_debug_pub.add( 'ENTER OE_ORDER_BOOK_UTIL.CHECK_BOOKING_HOLDS' , 1 ) ;
964 END IF;
965
966 -- Check if there are any generic or Booking holds
967
968 -- Fix bug
969 -- Removed p_entity_id parameter
970 OE_HOLDS_PUB.Check_Holds
971 (p_api_version => 1.0
972 ,p_header_id => p_header_id
973 ,p_wf_item => 'OEOH'
974 ,p_wf_activity => 'BOOK_ORDER'
975 ,x_result_out => l_check_holds_result
976 ,x_return_status => x_return_status
977 ,x_msg_count => l_msg_count
978 ,x_msg_data => l_msg_data
979 );
980
981
982 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS AND
983 l_check_holds_result = FND_API.G_TRUE )
984 THEN
985 FND_MESSAGE.SET_NAME('ONT','OE_BOOKING_HOLD_EXISTS');
986 OE_MSG_PUB.ADD;
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 /* Changes for bug#2673236:Begin */
989 ELSIF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
990 l_check_holds_result = FND_API.G_FALSE )
991 THEN
992 IF nvl(fnd_profile.value('ONT_PREVENT_BOOKING'),'N')='Y' THEN
993 IF l_debug_level > 0 THEN
994 oe_debug_pub.add( 'Profile ont_prevent_booking is set' , 1 ) ;
995 END IF;
996
997 BEGIN
998 SELECT
999 'EXISTS' INTO l_dummy
1000 FROM oe_order_lines_all
1001 WHERE header_id = p_header_id and
1002 ROWNUM = 1;
1003
1004 IF sql%found THEN
1005 p_hold_rec.header_id := p_header_id;
1006 p_hold_rec.wf_item_type := 'OEOH';
1007 p_hold_rec.wf_activity_name := 'BOOK_ORDER';
1008
1009 IF l_debug_level > 0 THEN
1010 oe_debug_pub.add( 'Before calling Check_Any_Line_Hold' , 1 ) ;
1011 END IF;
1012
1013 OE_HOLDS_PUB.Check_Any_Line_Hold
1014 (x_hold_rec => p_hold_rec
1015 ,x_return_status => x_return_status
1016 ,x_msg_count => l_msg_count
1017 ,x_msg_data => l_msg_data
1018 );
1019
1020
1021 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS AND
1022 p_hold_rec.x_result_out = FND_API.G_TRUE )
1023 THEN
1024 FND_MESSAGE.SET_NAME('ONT','OE_BOOKING_HOLD_EXISTS');
1025 OE_MSG_PUB.ADD;
1026 x_return_status := FND_API.G_RET_STS_ERROR;
1027 END IF;
1028 END IF;
1029 EXCEPTION
1030 WHEN NO_DATA_FOUND THEN
1031 null;
1032 END;
1033 END IF;
1034 END IF;
1035 /* Changes for bug#2673236:End */
1036 IF l_debug_level > 0 THEN
1037 oe_debug_pub.add( 'EXIT OE_ORDER_BOOK_UTIL.CHECK_BOOKING_HOLDS' , 1 ) ;
1038 END IF;
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043 IF FND_MSG_PUB.Check_Msg_Level
1044 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1045 THEN
1046 OE_MSG_PUB.Add_Exc_Msg
1047 ( G_PKG_NAME
1048 , 'Check_Booking_Holds'
1049 );
1050 END IF;
1051 END Check_Booking_Holds;
1052
1053 ---------------------------------------------------------------
1054 PROCEDURE BOOK_ORDER
1055 (p_api_version_number IN NUMBER
1056 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1057 ,p_header_id IN NUMBER
1058 ,x_return_status OUT NOCOPY VARCHAR2
1059
1060 ,x_msg_count OUT NOCOPY NUMBER
1061
1062 ,x_msg_data OUT NOCOPY VARCHAR2
1063
1064 )
1065 IS
1066 l_api_name CONSTANT VARCHAR2(30) := 'BOOK_ORDER';
1067 l_api_version_number CONSTANT NUMBER := 1.0;
1068 l_return_status VARCHAR2(1);
1069 l_validate_cfg BOOLEAN;
1070 l_freeze_inc_items BOOLEAN;
1071 --
1072 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1073 --
1074
1075 l_msg_count NUMBER;
1076 l_msg_data VARCHAR2(2000);
1077 l_count NUMBER;
1078
1079 l_qa_return_status VARCHAR2(1);
1080
1081
1082 BEGIN
1083 IF l_debug_level > 0 THEN
1084 oe_debug_pub.add( 'ENTER OE_ORDER_BOOK.BOOK_ORDER' ) ;
1085 END IF;
1086
1087 SAVEPOINT BOOK_ORDER;
1088
1089 -- Initialize API return status to success
1090 x_return_status := FND_API.G_RET_STS_SUCCESS;
1091
1092 -- Standard call to check for call compatibility
1093
1094 IF NOT FND_API.Compatible_API_Call
1095 ( l_api_version_number
1096 , p_api_version_number
1097 , l_api_name
1098 , G_PKG_NAME
1099 )
1100 THEN
1101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1102 END IF;
1103
1104 -- Initialize message list.
1105
1106 IF FND_API.to_Boolean(p_init_msg_list) THEN
1107 OE_MSG_PUB.initialize;
1108 END IF;
1109
1110
1111 -- Validate if revenue sales credits on the header and on each line
1112 -- add upto 100%. There is no check for error after this as all the
1113 -- order validation feedback should be given together. The check
1114 -- for error is therefore, after the next call which would validate
1115 -- the header and the lines
1116
1117 Validate_Sales_Credits
1118 ( p_header_id => p_header_id
1119 , x_return_status => l_return_status
1120 );
1121
1122 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1123 OE_Header_Scredit_Util.Redefault_Sales_Group(p_header_id=>p_header_id,
1124 p_date=> nvl(OE_ORDER_UTIL.g_header_rec.booked_date,SYSDATE));
1125 END IF;
1126
1127 -- if unexpected error, then go to exception handler. If there
1128 -- were validation failures, then it is expected error therefore
1129 -- go to order and line validation and then raise error.
1130 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1132 END IF;
1133
1134 IF l_debug_level > 0 THEN
1135 oe_debug_pub.add( 'RETURN STATUS AFTER SALES CREDITS:'||L_RETURN_STATUS ) ;
1136 END IF;
1137
1138 -- Call process order to update the booked_flag on header and
1139 -- on all the order lines. This will also check for all the fields
1140 -- that are required on the order and the lines at booking
1141
1142 Update_Booked_Flag(p_header_id => p_header_id
1143 , x_validate_cfg => l_validate_cfg
1144 , x_freeze_inc_items => l_freeze_inc_items
1145 , x_return_status => x_return_status
1146 );
1147
1148 IF l_debug_level > 0 THEN
1149 oe_debug_pub.add( 'RETURN STATUS AFTER UPDATE BOOKED:'||X_RETURN_STATUS ) ;
1150 END IF;
1151
1152 -- if failure during validate sales credits OR during order and
1153 -- line validation, raise error
1154 IF (x_return_status = FND_API.G_RET_STS_ERROR OR
1155 l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1156 RAISE FND_API.G_EXC_ERROR;
1157 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1159 END IF;
1160
1161
1162 -- Validate configurations, if any, in this order
1163 IF (l_validate_cfg) THEN
1164
1165 l_return_status := OE_Config_Util.Validate_Cfgs_In_Order
1166 (p_header_id => p_header_id);
1167 IF l_debug_level > 0 THEN
1168 oe_debug_pub.add( 'RETURN STATUS AFTER VALIDATE CFGS:'||L_RETURN_STATUS ) ;
1169 END IF;
1170
1171 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1172 RAISE FND_API.G_EXC_ERROR;
1173 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175 END IF;
1176
1177 END IF;
1178
1179
1180 -- Freeze included items, if any, for this order
1181 IF (l_freeze_inc_items
1182 AND FND_PROFILE.VALUE('ONT_INCLUDED_ITEM_FREEZE_METHOD') =
1183 OE_GLOBALS.G_IIFM_BOOKING )
1184 THEN
1185
1186 l_return_status := OE_Config_Util.Freeze_Inc_Items_For_Order
1187 (p_header_id => p_header_id);
1188
1189 IF l_debug_level > 0 THEN
1190 oe_debug_pub.add( 'RETURN STATUS AFTER FREEZE INC:'||L_RETURN_STATUS ) ;
1191 END IF;
1192
1193 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1194 RAISE FND_API.G_EXC_ERROR;
1195 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1197 END IF;
1198
1199 END IF;
1200
1201
1202 -- Execute any delayed requests and flow starts
1203 -- Will also take care of executing other delayed requests like
1204 -- tax which may have been logged in update_booked_flag and
1205 -- other procedures called by book_order.
1206 -- NOTE: This should be executed before verify payment call
1207 -- as credit check needs to look at the updated tax amounts.
1208
1209 OE_Order_PVT.Process_Requests_And_Notify
1210 ( p_process_requests => TRUE
1211 , p_notify => FALSE
1212 , x_return_status => l_return_status
1213 );
1214
1215 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1216 RAISE FND_API.G_EXC_ERROR;
1217 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1219 END IF;
1220
1221
1222 -- ABH
1223 -- run QA for current order
1224 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' THEN
1225
1226
1227 /****************************************************
1228 l_header_id_char := TO_CHAR(p_header_id);
1229 --This procedure would return status of FND_API.G_RET_STS_SUCCESS only if the order passed QA check
1230 QA_Order(
1231 p_api_version_number => 1.0,
1232 p_init_msg_list => 'T',
1233 p_header_id_list => l_header_id_char,
1234 p_header_count => l_count,
1235 x_error_count => l_error_count,
1236 x_return_status => l_return_status,
1237 x_msg_count => l_msg_count,
1238 x_msg_data => l_msg_data
1239 );
1240
1241 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1242 RAISE FND_API.G_EXC_ERROR;
1243 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1245 END IF;
1246 ****************************************************/
1247
1248 --Check for licensing
1249 IF OE_Contracts_util.check_license() <> 'Y' THEN
1250 IF l_debug_level > 0 THEN
1251 oe_debug_pub.add('Contractual option not licensed, hence not performing article QA ', 3);
1252 END IF;
1253 ELSE
1254
1255
1256 OE_CONTRACTS_UTIL.qa_articles (
1257 p_api_version => 1.0,
1258 p_doc_type => OE_CONTRACTS_UTIL.G_SO_DOC_TYPE,
1259 p_doc_id => p_header_id,
1260 x_qa_return_status => l_qa_return_status,
1261 x_return_status => l_return_status,
1262 x_msg_count => l_msg_count,
1263 x_msg_data => l_msg_data);
1264
1265
1266 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1267
1268 IF l_qa_return_status = FND_API.G_RET_STS_ERROR THEN
1269 RAISE FND_API.G_EXC_ERROR;
1270 ELSIF l_qa_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1272 END IF;
1273
1274 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1275 RAISE FND_API.G_EXC_ERROR;
1276 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1278 END IF;
1279
1280
1281 END IF;
1282
1283
1284 END IF;
1285 -- ABH
1286
1287 -- From Order Import we will make a call to Verfify Payment, only after committing the data.
1288 -- The call to Verify Payment from Booking code is suppressed for Order Import flow.
1289 -- This change is only for Verify Payment call that is triggered as part of Booking.
1290 IF NOT OE_GLOBALS.G_ORDER_IMPORT_CALL THEN -- Bug 7367433
1291
1292 -- Payment Verification is done at the end of booking
1293 -- because it is an expensive operation.
1294
1295 --R12 CVV2
1296 IF nvl(OE_GLOBALS.G_PAYMENT_PROCESSED, 'N') <> 'Y' THEN
1297 Verify_Payment_AT_Booking
1298 (p_header_id => p_header_id
1299 , x_return_status => l_return_status
1300 );
1301 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1302 RAISE FND_API.G_EXC_ERROR;
1303 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1305 END IF;
1306 END IF;
1307 ELSE
1308 IF nvl(OE_GLOBALS.G_PAYMENT_PROCESSED, 'N') <> 'Y' THEN
1309 OE_GLOBALS.G_PAYMENT_PROCESSED := 'O';
1310 END IF;
1311 END IF; -- Bug 7367433
1312
1313 OE_MSG_PUB.Count_And_Get
1314 ( p_count => x_msg_count
1315 , p_data => x_msg_data
1316 );
1317
1318 IF l_debug_level > 0 THEN
1319 oe_debug_pub.add( 'EXIT OE_ORDER_BOOK.BOOK_ORDER' , 1 ) ;
1320 END IF;
1321
1322 EXCEPTION
1323 -- Bug 2285308: Clear Delayed Requests when there is an error.
1324 -- Pricing/Inc Item Explosion API calls can log delayed requests but
1325 -- execution of requests is only towards the end, just before Verify_Payments.
1326 -- If there is an error in between logging and execution of requests,
1327 -- requests were not cleared from the cache earlier resulting
1328 -- in bugs like 2285308.
1329 WHEN FND_API.G_EXC_ERROR THEN
1330 IF l_debug_level > 0 THEN
1331 oe_debug_pub.add( 'EXP. ERROR IN OE_ORDER_BOOK.BOOK_ORDER' , 1 ) ;
1332 END IF;
1333 x_return_status := FND_API.G_RET_STS_ERROR;
1334 OE_Delayed_Requests_PVT.Clear_Request(l_return_status);
1335 OE_MSG_PUB.Count_And_Get
1336 ( p_count => x_msg_count
1337 , p_data => x_msg_data
1338 );
1339 ROLLBACK TO BOOK_ORDER;
1340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1341 IF l_debug_level > 0 THEN
1342 oe_debug_pub.add( 'UNEXP. ERROR IN OE_ORDER_BOOK.BOOK_ORDER' , 1 ) ;
1343 END IF;
1344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1345 OE_Delayed_Requests_PVT.Clear_Request(l_return_status);
1346 OE_MSG_PUB.Count_And_Get
1347 ( p_count => x_msg_count
1348 , p_data => x_msg_data
1349 );
1350 ROLLBACK TO BOOK_ORDER;
1351 WHEN OTHERS THEN
1352 IF l_debug_level > 0 THEN
1353 oe_debug_pub.add( 'OTHERS ERROR IN OE_ORDER_BOOK.BOOK_ORDER' , 1 ) ;
1354 END IF;
1355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1356 OE_Delayed_Requests_PVT.Clear_Request(l_return_status);
1357 IF FND_MSG_PUB.Check_Msg_Level
1358 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1359 THEN
1360 OE_MSG_PUB.Add_Exc_Msg
1361 ( G_PKG_NAME
1362 , l_api_name
1363 );
1364 END IF;
1365 OE_MSG_PUB.Count_And_Get
1366 ( p_count => x_msg_count
1367 , p_data => x_msg_data
1368 );
1369 ROLLBACK TO BOOK_ORDER;
1370 END BOOK_ORDER;
1371
1372
1373 -- Complete_Book_Eligible
1374 -- Checks if the order is eligible for booking and if not, populates
1375 -- an error message and returns an expected error status.
1376 -- If it is eligible, it progresses the order workflow to complete
1377 -- the booking process.
1378 -- If booking has been deferred, then it informs the caller by
1379 -- adding a message to the stack.
1380 ---------------------------------------------------------------------
1381 PROCEDURE Complete_Book_Eligible
1382 (p_api_version_number IN NUMBER
1383 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1384 , p_header_id IN NUMBER
1385 , x_return_status OUT NOCOPY VARCHAR2
1386
1387 , x_msg_count OUT NOCOPY NUMBER
1388
1389 , x_msg_data OUT NOCOPY VARCHAR2
1390
1391 )
1392 IS
1393 l_api_name CONSTANT VARCHAR2(30) := 'COMPLETE_BOOK_ELIGIBLE';
1394 l_api_version_number CONSTANT NUMBER := 1.0;
1395 l_itemkey VARCHAR2(30);
1396 l_booked_flag VARCHAR2(1);
1397 l_book_eligible VARCHAR2(1);
1398 l_booking_errored_flag VARCHAR2(1);
1399 l_order_source_id NUMBER;
1400 l_orig_sys_document_ref VARCHAR2(50);
1401 l_change_sequence VARCHAR2(50);
1402 l_source_document_type_id NUMBER;
1403 l_source_document_id NUMBER;
1404 CURSOR book_eligible IS
1405 SELECT 'Y'
1406 FROM WF_ITEM_ACTIVITY_STATUSES WIAS
1407 , WF_PROCESS_ACTIVITIES WPA
1408 WHERE WIAS.item_type = 'OEOH'
1409 AND WIAS.item_key = l_itemkey
1410 AND WIAS.activity_status = 'NOTIFIED'
1411 AND WPA.activity_name = 'BOOK_ELIGIBLE'
1412 AND WPA.instance_id = WIAS.process_activity;
1413 --For bug 3493374
1414 CURSOR booking_errored IS
1415 SELECT 'Y'
1416 FROM WF_ITEM_ACTIVITY_STATUSES WIAS
1417 , WF_PROCESS_ACTIVITIES WPA
1418 WHERE WIAS.item_type = 'OEOH'
1419 AND WIAS.item_key = l_itemkey
1420 AND WIAS.activity_status = 'ERROR'
1421 AND WPA.activity_name = 'BOOK_ORDER'
1422 AND WPA.instance_id = WIAS.process_activity;
1423 --
1424 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1425 --
1426 BEGIN
1427 IF l_debug_level > 0 THEN
1428 oe_debug_pub.add( 'ENTER OE_ORDER_BOOK.COMPLETE_BOOK_ELIGIBLE' , 1 ) ;
1429 END IF;
1430
1431 -- Initialize API return status to success
1432 x_return_status := FND_API.G_RET_STS_SUCCESS;
1433
1434 -- Standard call to check for call compatibility
1435
1436 IF NOT FND_API.Compatible_API_Call
1437 ( l_api_version_number
1438 , p_api_version_number
1439 , l_api_name
1440 , G_PKG_NAME
1441 )
1442 THEN
1443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1444 END IF;
1445
1446 -- Initialize message list.
1447
1448 IF FND_API.to_Boolean(p_init_msg_list) THEN
1449 OE_MSG_PUB.initialize;
1450 END IF;
1451
1452 SELECT order_source_id, orig_sys_document_ref, change_sequence, source_document_type_id, source_document_id
1453 INTO l_order_source_id, l_orig_sys_document_ref, l_change_sequence, l_source_document_type_id, l_source_document_id
1454 FROM OE_ORDER_HEADERS_ALL
1455 WHERE HEADER_ID = p_header_id;
1456
1457 OE_MSG_PUB.set_msg_context(
1458 p_entity_code => 'HEADER'
1459 ,p_entity_id => p_header_id
1460 ,p_header_id => p_header_id
1461 ,p_line_id => null
1462 ,p_order_source_id => l_order_source_id
1463 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1464 ,p_orig_sys_document_line_ref => null
1465 ,p_change_sequence => l_change_sequence
1466 ,p_source_document_type_id => l_source_document_type_id
1467 ,p_source_document_id => l_source_document_id
1468 ,p_source_document_line_id => null );
1469
1470 l_itemkey := to_char(p_header_id);
1471
1472 -- Check if for this header, the BOOK_ELIGIBLE activity is in a
1473 -- notified state
1474
1475 IF l_debug_level > 0 THEN
1476 oe_debug_pub.add( 'OPEN BOOK_ELIGIBLE' ) ;
1477 END IF;
1478 OPEN book_eligible;
1479 FETCH book_eligible INTO l_book_eligible;
1480
1481 IF (book_eligible%NOTFOUND) THEN
1482 IF l_debug_level > 0 THEN
1483 oe_debug_pub.add( 'BOOKING NOT ELIGIBLE' ) ;
1484 END IF;
1485
1486 -- Booking could be a high cost activity and could have been deferred
1487 -- due to a prior request. If the activity is in a deferred status,
1488 -- then inform the user.
1489
1490 IF BookingIsDeferred(l_itemkey)
1491 THEN
1492
1493 OE_MSG_PUB.Count_And_Get
1494 ( p_count => x_msg_count
1495 , p_data => x_msg_data
1496 );
1497 CLOSE book_eligible;
1498 RETURN;
1499 --For bug 3493374.Booking errored out
1500 ELSE
1501 OPEN booking_errored;
1502 FETCH booking_errored INTO l_booking_errored_flag;
1503 IF (booking_errored%FOUND)
1504 THEN
1505 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_BOOK_ERRORED');
1506 OE_MSG_PUB.ADD;
1507 RAISE FND_API.G_EXC_ERROR;
1508 -- Else the order is NOT eligible for booking: raise an error.
1509 ELSE
1510 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_NOT_BOOK_ELIGIBLE');
1511 OE_MSG_PUB.ADD;
1512 RAISE FND_API.G_EXC_ERROR;
1513 END IF;
1514 CLOSE booking_errored;
1515 END IF;
1516 END IF;
1517
1518 CLOSE book_eligible;
1519 IF l_debug_level > 0 THEN
1520 oe_debug_pub.add( 'CLOSE BOOK_ELIGIBLE' ) ;
1521 END IF;
1522
1523
1524 -- Lock the order: header, lines , sales credits and price adjustments
1525 -- This will prevent another user from working on the same order
1526 -- and needs to be done before calling the wf_engine as the workflow
1527 -- engine will hang if another user is trying to book the same order
1528
1529 OE_ORDER_UTIL.Lock_Order_Object
1530 (p_header_id => p_header_id
1531 ,x_return_status => x_return_status
1532 );
1533 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1534 RAISE FND_API.G_EXC_ERROR;
1535 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1537 END IF;
1538
1539 -- Call WF_ENGINE to complete the BOOK_ELIGIBLE activity and proceed
1540 -- to the next activity in the order workflow
1541
1542 WF_ENGINE.CompleteActivityInternalName
1543 ( itemtype => 'OEOH'
1544 , itemkey => l_itemkey
1545 , activity => 'BOOK_ELIGIBLE'
1546 , result => NULL
1547 );
1548
1549
1550 IF l_debug_level > 0 THEN
1551 oe_debug_pub.add( 'AFTER CALLING WF_ENGINE' ) ;
1552 END IF;
1553
1554 -- if order was booked, the flag on the order header would have
1555 -- been updated.
1556
1557 SELECT booked_flag
1558 INTO l_booked_flag
1559 FROM OE_ORDER_HEADERS_ALL
1560 WHERE HEADER_ID = p_header_id;
1561
1562 OE_MSG_PUB.set_msg_context(
1563 p_entity_code => 'HEADER'
1564 ,p_entity_id => p_header_id
1565 ,p_header_id => p_header_id
1566 ,p_line_id => null
1567 ,p_order_source_id => l_order_source_id
1568 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1569 ,p_orig_sys_document_line_ref => null
1570 ,p_change_sequence => l_change_sequence
1571 ,p_source_document_type_id => l_source_document_type_id
1572 ,p_source_document_id => l_source_document_id
1573 ,p_source_document_line_id => null );
1574
1575 -- if order has been booked, inform the user
1576
1577 IF l_booked_flag = 'Y' THEN
1578
1579 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_BOOKED');
1580 OE_MSG_PUB.ADD;
1581
1582 -- if order has NOT been booked, then check if booking has been deferred
1583
1584 ELSE
1585
1586 -- if order is neither booked nor booking has been deferred,
1587 -- then raise an expected error: booking might have failed.
1588 -- Error messages would have been populated in the
1589 -- BOOK_ORDER activity
1590 IF NOT BookingIsDeferred(l_itemkey)
1591 THEN
1592
1593 -- Bug 2437258 - raise error only if order is back at
1594 -- book eligible status. As booking workflows should be
1595 -- defined to transition to book eligible for errors.
1596 -- If it is NOT at book eligible, there was probably a
1597 -- customization between book eligible and book order
1598 -- (e.g. WF approval notification) due to which order
1599 -- did not even reach book order activity.
1600 IF l_debug_level > 0 THEN
1601 oe_debug_pub.add( 'AGAIN OPEN BOOK_ELIGIBLE' ) ;
1602 END IF;
1603 OPEN book_eligible;
1604 FETCH book_eligible INTO l_book_eligible;
1605 IF (book_eligible%FOUND) THEN
1606 RAISE FND_API.G_EXC_ERROR;
1607 END IF;
1608 CLOSE book_eligible;
1609
1610 END IF;
1611
1612 END IF;
1613
1614 OE_MSG_PUB.Count_And_Get
1615 ( p_count => x_msg_count
1616 , p_data => x_msg_data
1617 );
1618
1619 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
1620
1621 IF l_debug_level > 0 THEN
1622 oe_debug_pub.add( 'EXIT OE_ORDER_BOOK.COMPLETE_BOOK_ELIGIBLE' , 1 ) ;
1623 END IF;
1624
1625 EXCEPTION
1626 WHEN FND_API.G_EXC_ERROR THEN
1627 x_return_status := FND_API.G_RET_STS_ERROR;
1628 IF (book_eligible%ISOPEN) THEN
1629 CLOSE book_eligible;
1630 END IF;
1631 OE_MSG_PUB.Count_And_Get
1632 ( p_count => x_msg_count
1633 , p_data => x_msg_data
1634 );
1635 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
1636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1638 IF (book_eligible%ISOPEN) THEN
1639 CLOSE book_eligible;
1640 END IF;
1641 OE_MSG_PUB.Count_And_Get
1642 ( p_count => x_msg_count
1643 , p_data => x_msg_data
1644 );
1645 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
1646 WHEN OTHERS THEN
1647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1648 IF (book_eligible%ISOPEN) THEN
1649 CLOSE book_eligible;
1650 END IF;
1651 IF OE_MSG_PUB.Check_Msg_Level
1652 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1653 THEN
1654 OE_MSG_PUB.Add_Exc_Msg
1655 ( G_PKG_NAME
1656 , l_api_name
1657 );
1658 END IF;
1659 OE_MSG_PUB.Count_And_Get
1660 ( p_count => x_msg_count
1661 , p_data => x_msg_data
1662 );
1663 OE_MSG_PUB.Reset_Msg_Context(p_entity_code => 'HEADER');
1664
1665 END Complete_Book_Eligible;
1666
1667
1668 -- PROCEDURE Book_Multiple_Orders
1669 -- This procedure accepts a list of header IDs in a string separated
1670 -- by commas (e.g. 1234,3567.8945) and the number of orders
1671 -- (p_header_count) to be booked.
1672 -- And it progresses each order through the booking activity in its
1673 -- workflow by calling complete_book_eligible.
1674 -- The return status is SUCCESS only if all orders are processed
1675 -- successfully.
1676 -- The number of orders that are processed with errors can be retrieved
1677 -- from the OUT variable, x_error_count.
1678 -- Called from the form package OE_ORDER_CONTROL.Book_Order_Button
1679 -- for booking multi_selected orders.
1680 ---------------------------------------------------------------------
1681 PROCEDURE Book_Multiple_Orders
1682 (p_api_version_number IN NUMBER
1683 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1684 ,p_header_id_list IN OE_GLOBALS.Selected_Record_Tbl
1685 ,p_header_count IN NUMBER
1686 ,x_error_count OUT NOCOPY NUMBER
1687
1688 ,x_return_status OUT NOCOPY VARCHAR2
1689
1690 ,x_msg_count OUT NOCOPY NUMBER
1691
1692 ,x_msg_data OUT NOCOPY VARCHAR2
1693
1694 )
1695 IS
1696 l_api_name CONSTANT VARCHAR2(30) := 'BOOK_MULTIPLE_ORDERS';
1697 l_api_version_number CONSTANT NUMBER := 1.0;
1698 l_header_id NUMBER;
1699 l_Transaction_Phase_Code VARCHAR2(30);
1700 l_msg_count NUMBER;
1701 l_msg_data VARCHAR2(2000);
1702 I NUMBER;
1703 initial NUMBER;
1704 nextpos NUMBER;
1705 l_return_status VARCHAR2(30);
1706 l_orgid number;
1707 --
1708 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1709 --
1710 BEGIN
1711 IF l_debug_level > 0 THEN
1712 oe_debug_pub.add( 'ENTER OE_ORDER_BOOK.BOOK_MULTIPLE_ORDERS' , 1 ) ;
1713 END IF;
1714
1715 -- Initialize API return status to success
1716 x_return_status := FND_API.G_RET_STS_SUCCESS;
1717 x_error_count := 0;
1718
1719 -- Standard call to check for call compatibility
1720
1721 IF NOT FND_API.Compatible_API_Call
1722 ( l_api_version_number
1723 , p_api_version_number
1724 , l_api_name
1725 , G_PKG_NAME
1726 )
1727 THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1729 END IF;
1730
1731 -- Initialize message list.
1732
1733 IF FND_API.to_Boolean(p_init_msg_list) THEN
1734 OE_MSG_PUB.initialize;
1735 END IF;
1736 initial := 1;
1737 FOR I IN 1..p_header_count LOOP
1738
1739 IF l_debug_level > 0 THEN
1740 oe_debug_pub.add( 'Enter Loop headercount'|| p_header_count , 1 ) ;
1741 END IF;
1742 /* changes for MOAC, not required since the input is the table
1743 IF I = p_header_count THEN
1744 nextpos := length(p_header_id_list)+1.0;
1745 ELSE
1746 nextpos := INSTR(p_header_id_list,',',initial,1);
1747 END IF;
1748 */
1749
1750 l_header_id := p_header_id_list(i).id1;
1751 IF l_orgid is null OR p_header_id_list(i).org_id <> l_orgid THEN
1752 l_orgid := p_header_id_list(i).Org_Id;
1753
1754 Mo_Global.Set_Policy_Context (p_access_mode => 'S',
1755 p_org_id => p_header_id_list(i).Org_Id);
1756
1757 END IF;
1758
1759 IF l_debug_level > 0 THEN
1760 oe_debug_pub.add( 'HEADER ID: '||L_HEADER_ID , 1 ) ;
1761 END IF;
1762 --For CC Project
1763 SELECT h.Transaction_Phase_Code
1764 INTO l_Transaction_Phase_Code
1765 FROM oe_order_headers_all h
1766 WHERE l_header_id=h.header_id;
1767 IF (l_Transaction_Phase_Code='N' ) THEN
1768 OE_Order_Wf_Util.Complete_eligible_and_Book(
1769 p_api_version_number => 1.0
1770 , p_init_msg_list => FND_API.G_FALSE
1771 , p_header_id => l_header_id
1772 , x_return_status => l_return_status
1773 , x_msg_count => l_msg_count
1774 , x_msg_data => l_msg_data );
1775 ELSE
1776 OE_Order_Book_Util.Complete_Book_Eligible (
1777 p_api_version_number => 1.0
1778 , p_init_msg_list => FND_API.G_FALSE
1779 , p_header_id => l_header_id
1780 , x_return_status => l_return_status
1781 , x_msg_count => l_msg_count
1782 , x_msg_data => l_msg_data);
1783 END IF ;
1784
1785 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1786 x_error_count := x_error_count + 1.0;
1787 x_return_status := FND_API.G_RET_STS_ERROR;
1788 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1789 x_error_count := x_error_count + 1.0;
1790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1791 END IF;
1792
1793 initial := nextpos + 1.0;
1794 END LOOP;
1795
1796 IF l_debug_level > 0 THEN
1797 oe_debug_pub.add( 'EXIT OE_ORDER_BOOK.BOOK_MULTIPLE_ORDERS' , 1 ) ;
1798 END IF;
1799
1800 EXCEPTION
1801 WHEN FND_API.G_EXC_ERROR THEN
1802 x_return_status := FND_API.G_RET_STS_ERROR;
1803 OE_MSG_PUB.Count_And_Get
1804 ( p_count => x_msg_count
1805 , p_data => x_msg_data
1806 );
1807 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1809 OE_MSG_PUB.Count_And_Get
1810 ( p_count => x_msg_count
1811 , p_data => x_msg_data
1812 );
1813 WHEN OTHERS THEN
1814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1815 IF OE_MSG_PUB.Check_Msg_Level
1816 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1817 THEN
1818 OE_MSG_PUB.Add_Exc_Msg
1819 ( G_PKG_NAME
1820 , l_api_name
1821 );
1822 END IF;
1823 OE_MSG_PUB.Count_And_Get
1824 ( p_count => x_msg_count
1825 , p_data => x_msg_data
1826 );
1827
1828 END Book_Multiple_Orders;
1829
1830
1831
1832 /******************************************************************************
1833 --ABH
1834 -- This procedure accepts a list of header IDs in a string separated
1835 -- by commas (e.g. 1234,3567,8945) and the number of orders
1836 -- (p_header_count) to be QA'd.
1837 -- In case of a single order, we have just one header id in the string.
1838 -- The return status is SUCCESS only if all orders are processed
1839 -- successfully.
1840 -- The number of orders that are processed with errors (i.e. QA returned error) can be retrieved
1841 -- from the OUT variable, x_error_count.
1842 -- Called from the form package OE_ORDER_CONTROL.Book_Order_Button
1843 -- for QA'ing multi_selected orders or just a single order.
1844 PROCEDURE QA_Order
1845 (p_api_version_number IN NUMBER
1846 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1847 ,p_header_id_list IN OUT NOCOPY VARCHAR2
1848 ,p_header_count IN NUMBER
1849 ,x_error_count OUT NOCOPY NUMBER
1850 ,x_return_status OUT NOCOPY VARCHAR2
1851 ,x_msg_count OUT NOCOPY NUMBER
1852 ,x_msg_data OUT NOCOPY VARCHAR2
1853 ) IS
1854
1855 l_api_name CONSTANT VARCHAR2(30) := 'QA_ORDER';
1856 l_api_version_number CONSTANT NUMBER := 1.0;
1857 l_header_id NUMBER;
1858 l_msg_count NUMBER;
1859 l_msg_data VARCHAR2(2000);
1860 I NUMBER;
1861 initial NUMBER;
1862 nextpos NUMBER;
1863 l_return_status VARCHAR2(30);
1864
1865 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1866
1867 TYPE header_id_tbl_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
1868 l_header_id_tbl header_id_tbl_type;
1869 TYPE delete_header_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1870 l_delete_header_id_tbl delete_header_id_tbl_type;
1871 J NUMBER;
1872 l_qa_return_status VARCHAR2(1);
1873 l_record_ids VARCHAR2(32000);
1874
1875 BEGIN
1876 IF l_debug_level > 0 THEN
1877 oe_debug_pub.add('ENTER OE_ORDER_BOOK.QA_ORDER' , 1 ) ;
1878 oe_debug_pub.add('p_header_count: ' || p_header_count, 1);
1879 END IF;
1880
1881 -- Initialize API return status to success
1882 x_return_status := FND_API.G_RET_STS_SUCCESS;
1883 x_error_count := 0;
1884
1885 --Check for licensing
1886 IF OE_Contracts_util.check_license() <> 'Y' THEN
1887 IF l_debug_level > 0 THEN
1888 oe_debug_pub.add('Contractual option not licensed, hence not performing article QA ', 3);
1889 END IF;
1890 RETURN;
1891 END IF;
1892
1893
1894
1895 -- Standard call to check for call compatibility
1896
1897 IF NOT FND_API.Compatible_API_Call
1898 ( l_api_version_number
1899 , p_api_version_number
1900 , l_api_name
1901 , G_PKG_NAME
1902 )
1903 THEN
1904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1905 END IF;
1906
1907 -- Initialize message list.
1908
1909 IF FND_API.to_Boolean(p_init_msg_list) THEN
1910 OE_MSG_PUB.initialize;
1911 END IF;
1912
1913 --initialize PL/SQL tables
1914 l_header_id_tbl.DELETE;
1915 l_delete_header_id_tbl.DELETE;
1916
1917
1918
1919 --convert the header id's separated by commas in p_header_id_list into a PL/SQL table l_header_id_tbl
1920 initial := 1;
1921 FOR I IN 1..p_header_count LOOP
1922
1923 IF I = p_header_count THEN
1924 nextpos := length(p_header_id_list)+1.0;
1925 ELSE
1926 nextpos := INSTR(p_header_id_list,',',initial,1);
1927 END IF;
1928
1929 l_header_id := to_number(substr(p_header_id_list,initial, nextpos-initial));
1930 IF l_debug_level > 0 THEN
1931 oe_debug_pub.add( 'adding to PL/SQL table HEADER ID: '||L_HEADER_ID , 1 ) ;
1932 END IF;
1933
1934 --transfer into PL/SQL table
1935 l_header_id_tbl(I) := l_header_id;
1936
1937 initial := nextpos + 1.0;
1938 END LOOP;
1939
1940 IF l_debug_level > 0 THEN
1941 oe_debug_pub.add('l_header_id_tbl.COUNT: ' || l_header_id_tbl.COUNT);
1942 END IF;
1943
1944
1945 --now run QA on header id's contained in PL/SQL table l_header_id_tbl
1946 J := 1;
1947 FOR I IN 1..p_header_count LOOP
1948 IF l_debug_level > 0 THEN
1949 oe_debug_pub.add('Calling OE_CONTRACTS_UTIL.qa_articles for l_header_id_tbl('||I||'): '||l_header_id_tbl(I));
1950 END IF;
1951
1952 OE_CONTRACTS_UTIL.qa_articles (
1953 p_api_version => 1.0,
1954 p_doc_type => OE_CONTRACTS_UTIL.G_SO_DOC_TYPE,
1955 p_doc_id => TO_NUMBER(l_header_id_tbl(I)),
1956 x_qa_return_status => l_qa_return_status,
1957 x_return_status => l_return_status,
1958 x_msg_count => l_msg_count,
1959 x_msg_data => l_msg_data);
1960
1961
1962
1963
1964 IF l_debug_level > 0 THEN
1965 oe_debug_pub.add('l_return_status for l_header_id_tbl('||I||'): '||l_header_id_tbl(I)||' is '|| l_return_status);
1966 oe_debug_pub.add('l_qa_return_status for l_header_id_tbl('||I||'): '||l_header_id_tbl(I)||' is '|| l_qa_return_status);
1967 END IF;
1968
1969 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1970 IF l_qa_return_status = FND_API.G_RET_STS_ERROR THEN
1971 l_delete_header_id_tbl(J) := I; --keep track of which header id to delete later
1972 J := J + 1;
1973 x_error_count := x_error_count + 1.0;
1974 x_return_status := FND_API.G_RET_STS_ERROR;
1975
1976 ELSIF l_qa_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1977 l_delete_header_id_tbl(J) := I; --keep track of which header id to delete later
1978 J := J + 1;
1979 x_error_count := x_error_count + 1.0;
1980 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1981
1982 END IF;
1983
1984 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1985 RAISE FND_API.G_EXC_ERROR;
1986 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1988 END IF;
1989
1990
1991 END LOOP;
1992
1993
1994 IF l_debug_level > 0 THEN
1995 oe_debug_pub.add('l_delete_header_id_tbl.COUNT: ' || l_delete_header_id_tbl.COUNT);
1996 oe_debug_pub.add('l_header_id_tbl.COUNT before deleting: ' || l_header_id_tbl.COUNT);
1997 END IF;
1998
1999 --delete the header id's that failed QA from the PL/SQL table l_header_id_tbl
2000 FOR J IN 1..l_delete_header_id_tbl.COUNT LOOP
2001 l_header_id_tbl.DELETE(l_delete_header_id_tbl(J));
2002
2003 IF l_debug_level > 0 THEN
2004 oe_debug_pub.add('Deleted element with index ' || J || ' from l_header_id_tbl');
2005 END IF;
2006 END LOOP;
2007
2008 IF l_debug_level > 0 THEN
2009 oe_debug_pub.add('l_header_id_tbl.COUNT after deleting: ' || l_header_id_tbl.COUNT);
2010 END IF;
2011
2012
2013
2014 -- finally transfer the remaining QA passed header ids in PL/SQL table l_header_id_tbl back to comma separated
2015 -- form in p_header_id_list
2016 l_record_ids := NULL; --initialize
2017 I := l_header_id_tbl.FIRST;
2018 WHILE I IS NOT NULL LOOP
2019
2020 IF (I = l_header_id_tbl.LAST OR l_header_id_tbl.COUNT = 1) THEN
2021 l_record_ids := l_record_ids || l_header_id_tbl(I);
2022 ELSE
2023 l_record_ids := l_record_ids || l_header_id_tbl(I) || ',';
2024 END IF;
2025
2026 I := l_header_id_tbl.NEXT(I);
2027
2028 END LOOP;
2029
2030 p_header_id_list := l_record_ids;
2031
2032 IF l_debug_level > 0 THEN
2033 oe_debug_pub.add('Length of string l_record_ids: ' || LENGTH(l_record_ids));
2034 oe_debug_pub.add('Length of string p_header_id_list: ' || LENGTH(p_header_id_list));
2035 END IF;
2036
2037
2038
2039 IF l_debug_level > 0 THEN
2040 oe_debug_pub.add( 'EXIT OE_ORDER_BOOK.QA_ORDER' , 1 ) ;
2041 END IF;
2042
2043 EXCEPTION
2044 WHEN FND_API.G_EXC_ERROR THEN
2045 x_return_status := FND_API.G_RET_STS_ERROR;
2046 OE_MSG_PUB.Count_And_Get
2047 ( p_count => x_msg_count
2048 , p_data => x_msg_data
2049 );
2050 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2051 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2052 OE_MSG_PUB.Count_And_Get
2053 ( p_count => x_msg_count
2054 , p_data => x_msg_data
2055 );
2056 WHEN OTHERS THEN
2057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2058 IF OE_MSG_PUB.Check_Msg_Level
2059 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2060 THEN
2061 OE_MSG_PUB.Add_Exc_Msg
2062 ( G_PKG_NAME
2063 , l_api_name
2064 );
2065 END IF;
2066 OE_MSG_PUB.Count_And_Get
2067 ( p_count => x_msg_count
2068 , p_data => x_msg_data
2069 );
2070
2071
2072 END QA_Order;
2073 --ABH
2074 ******************************************************************************/
2075
2076
2077 END OE_ORDER_BOOK_UTIL;
[Home] [Help]