1 PACKAGE BODY OE_BULK_ORDER_PVT AS
2 /* $Header: OEBVORDB.pls 120.2.12010000.5 2009/01/08 15:42:07 smanian ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_BULK_ORDER_PVT';
5
6
7 ---------------------------------------------------------------
8 -- LOCAL PROCEDURES
9 ---------------------------------------------------------------
10
11 PROCEDURE Delete_Error_Records(p_batch_id NUMBER,
12 p_adjustments_exist IN VARCHAR2,
13 p_process_tax IN VARCHAR2,
14 p_process_configurations IN VARCHAR2)
15 IS
16 l_return_status VARCHAR2(1);
17 l_msg_count NUMBER;
18 l_msg_data VARCHAR2(2000);
19 --
20 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
21 --
22 BEGIN
23
24 IF G_ERROR_REC.order_source_id.COUNT > 0 THEN
25
26 for i in 1..G_ERROR_REC.order_source_id.COUNT loop
27
28 IF l_debug_level > 0 THEN
29 oe_debug_pub.add( 'ORDER SOURCE :'||G_ERROR_REC.ORDER_SOURCE_ID ( I ) ) ;
30 oe_debug_pub.add( 'ORIG SYS REF :'||G_ERROR_REC.ORIG_SYS_DOCUMENT_REF ( I ) ) ;
31 oe_debug_pub.add( 'Header_Id :'||G_ERROR_REC.HEADER_ID(I));
32 END IF;
33
34 end loop;
35
36 -- Delete Holds Records
37 FORALL i IN 1..G_ERROR_REC.order_source_id.COUNT
38 DELETE from OE_ORDER_HOLDS
39 WHERE header_id = G_ERROR_REC.header_id(i);
40
41 -- Delete Sales Credits Records
42 FORALL i IN 1..G_ERROR_REC.order_source_id.COUNT
43 DELETE from OE_SALES_CREDITS
44 WHERE header_id = G_ERROR_REC.header_id(i);
45
46 -- Delete MTL Sales Order Records
47 INV_SalesOrder.Delete_MTL_Sales_Orders_Bulk
48 (p_api_version_number => 1.0
49 ,p_error_rec => G_ERROR_REC
50 ,x_return_status => l_return_status
51 ,x_message_count => l_msg_count
52 ,x_message_data => l_msg_data
53 );
54
55 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
56 IF l_debug_level > 0 THEN
57 oe_debug_pub.add( 'ERROR IN DELETE_MTL_SALES_ORDERS_BULK :' ||L_RETURN_STATUS ) ;
58 END IF;
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61
62 -- Delete line records
63 FORALL i IN 1..G_ERROR_REC.order_source_id.COUNT
64 DELETE from OE_ORDER_LINES
65 WHERE header_id = G_ERROR_REC.header_id(i);
66
67 -- Delete Header Records
68 FORALL i IN 1..G_ERROR_REC.order_source_id.COUNT
69 DELETE from OE_ORDER_HEADERS
70 WHERE header_id = G_ERROR_REC.header_id(i);
71
72 IF G_DBI_INSTALLED = 'Y' THEN
73
74 -- Delete from DBI log tables
75 FORALL i IN 1..G_ERROR_REC.order_source_id.COUNT
76 DELETE from ONT_DBI_CHANGE_LOG
77 WHERE header_id = G_ERROR_REC.header_id(i);
78
79 END IF;
80
81 -- Delete Adjustment Records (added for bugfix 4180619)
82 IF p_adjustments_exist = 'Y' OR p_process_tax = 'Y' THEN
83 FORALL i IN 1..G_ERROR_REC.order_source_id.COUNT
84 DELETE from OE_PRICE_ADJUSTMENTS
85 WHERE header_id = G_ERROR_REC.header_id(i);
86 END IF;
87
88 -- Delete CZ configuration revisions
89 IF (p_process_configurations = 'Y') AND
90 nvl(G_CONFIGURATOR_USED, 'N') = 'Y'
91 THEN
92 OE_BULK_CONFIG_UTIL.Delete_Configurations
93 ( p_error_rec => G_ERROR_REC
94 ,x_return_status => l_return_status
95 );
96
97 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
98 IF l_debug_level > 0 THEN
99 oe_debug_pub.add( 'ERROR IN OE_BULK_CONFIG_UTIL.Delete_Configurations :'
100 ||L_RETURN_STATUS ) ;
101 END IF;
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;
104 END IF;
105
106
107
108 END IF;
109
110 IF l_debug_level > 0 THEN
111 oe_debug_pub.add( 'Exiting Delete_Error_Records :');
112 END IF;
113
114 EXCEPTION
115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 WHEN OTHERS THEN
118 OE_BULK_MSG_PUB.Add_Exc_Msg
119 ( G_PKG_NAME
120 , 'Delete_Error_Records'
121 );
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END Delete_Error_Records;
124
125 -- This procedure marks the headers interface records as errored for
126 -- any invalid headers or lines that have been inserted.
127 PROCEDURE Process_Invalid_Records
128 IS
129 --
130 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
131 --
132 BEGIN
133
134 --
135 -- This IF ensures that subsequent calls to process invalid records
136 -- does not look at error records already updated on the
137 -- interface tables by prior calls.
138 --
139 -- For e.g. process invalid records is called after headers
140 -- processing first so it marks error for all invalid headers.
141 -- Next, it is called after lines processing so it should update
142 -- interface tables only for invalid lines.
143 --
144 IF (G_ERROR_REC.order_source_id.COUNT > G_ERROR_COUNT) THEN
145
146 IF l_debug_level > 0 THEN
147 oe_debug_pub.add( 'COUNT :'||G_ERROR_REC.ORDER_SOURCE_ID.COUNT ) ;
148 END IF;
149 IF l_debug_level > 0 THEN
150 oe_debug_pub.add( 'ORIG SYS REF :'||G_ERROR_REC.ORIG_SYS_DOCUMENT_REF ( 1 ) ) ;
151 END IF;
152 IF l_debug_level > 0 THEN
153 oe_debug_pub.add('Header_Id :'||G_ERROR_REC.HEADER_ID(1));
154 oe_debug_pub.add('G_ERROR_COUNT :'||G_ERROR_COUNT);
155 oe_debug_pub.add('G_REQUEST_ID :'||G_REQUEST_ID);
156 oe_debug_pub.add('G_ORDER_SOURCE :'||G_ERROR_REC.ORDER_SOURCE_ID(1));
157 END IF;
158 FORALL i IN (G_error_count+1)..G_ERROR_REC.order_source_id.COUNT
159 UPDATE OE_HEADERS_IFACE_ALL
160 SET ERROR_FLAG = 'Y'
161 WHERE REQUEST_ID = G_REQUEST_ID
162 AND ORDER_SOURCE_ID = G_ERROR_REC.order_source_id(i)
163 AND ORIG_SYS_DOCUMENT_REF = G_ERROR_REC.orig_sys_document_ref(i);
164
165 G_ERROR_COUNT := G_ERROR_REC.order_source_id.COUNT;
166
167 END IF;
168 IF l_debug_level > 0 THEN
169 oe_debug_pub.add( 'Exiting Process_Invalid_Records ');
170 END IF;
171
172 EXCEPTION
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 WHEN OTHERS THEN
176 OE_BULK_MSG_PUB.Add_Exc_Msg
177 ( G_PKG_NAME
178 , 'Process_Invalid_Records'
179 );
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END Process_Invalid_Records;
182
183 --
184 -- Update_DBI_Log
185 -- Bulk enabled version of OEXUDBIB.pls. Is used to insert records into DBI
186 -- log tables about lines created during bulk import
187 --
188 PROCEDURE Update_DBI_Log
189 ( p_line_rec IN OE_WSH_BULK_GRP.Line_Rec_Type
190 , x_return_status OUT NOCOPY VARCHAR2
191
192 )
193 IS
194 l_header_id NUMBER;
195 l_line_id NUMBER;
196 l_line_count NUMBER;
197 l_set_of_books_rec oe_order_cache.Set_Of_Books_Rec_Type;
198 l_set_of_books_id NUMBER;
199 l_currency_code VARCHAR2(15);
200 l_last_update_date DATE;
201 --
202 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
203 --
204 BEGIN
205
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207
208 l_set_of_books_rec := OE_ORDER_CACHE.LOAD_SET_OF_BOOKS;
209 l_set_of_books_id := l_set_of_books_rec.set_of_books_id;
210 l_currency_code := l_set_of_books_rec.currency_code;
211
212 IF (l_set_of_books_id IS NULL) OR (l_currency_code IS NULL) THEN
213 IF l_debug_level > 0 THEN
214 oe_debug_pub.add( 'SET OF BOOKS OR CURRENCY IS NULL' ) ;
215 END IF;
216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217 END IF;
218
219 FOR I IN 1..p_line_rec.line_id.count LOOP
220
221 IF p_line_rec.booked_flag(I) = 'Y' THEN
222
223 INSERT INTO ONT_DBI_CHANGE_LOG
224 ( HEADER_ID
225 ,LINE_ID
226 ,SET_OF_BOOKS_ID
227 ,CURRENCY_CODE
228 ,LAST_UPDATE_DATE
229 )
230 VALUES
231 ( p_line_rec.header_id(I)
232 ,p_line_rec.line_id(I)
233 ,l_set_of_books_id
234 ,l_currency_code
235 ,sysdate
236 );
237
238 END IF;
239
240 END LOOP;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
245 THEN
246 OE_BULK_MSG_PUB.Add_Exc_Msg
247 (G_PKG_NAME
248 ,'Update_DBI_log');
249 END IF;
250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
251 END Update_DBI_Log;
252
253
254 PROCEDURE Process_Headers
255 ( p_batch_id IN NUMBER
256 , p_validate_only IN VARCHAR2 DEFAULT 'N'
257 , p_validate_desc_flex IN VARCHAR2 DEFAULT 'Y'
258 , p_defaulting_mode IN VARCHAR2 DEFAULT 'Y'
259 , p_process_configurations IN VARCHAR2 DEFAULT 'N'
260 , p_validate_configurations IN VARCHAR2 DEFAULT 'Y'
261 , p_schedule_configurations IN VARCHAR2 DEFAULT 'N'
262 )
263 IS
264 l_start_time NUMBER;
265 l_end_time NUMBER;
266 l_header_scredit_rec OE_BULK_ORDER_PVT.Scredit_Rec_Type;
267 --
268 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
269 --
270 BEGIN
271
272
273 ----------------------------------------------------------------
274 -- Load Headers from interface table.
275 ----------------------------------------------------------------
276
277 -- Bug 5640601 =>
278 -- Selecting hsecs from v$times is changed to execute only when debug
279 -- is enabled, as hsec is used for logging only when debug is enabled.
280 IF l_debug_level > 0 Then
281 SELECT hsecs INTO l_start_time from v$timer;
282 end if;
283
284 OE_Bulk_Header_Util.Load_Headers
285 (p_batch_id => p_batch_id
286 ,p_header_rec => G_HEADER_REC);
287
288 -- Bug 5640601 =>
289 -- Selecting hsecs from v$times is changed to execute only when debug
290 -- is enabled, as hsec is used for logging only when debug is enabled.
291 IF l_debug_level > 0 Then
292 SELECT hsecs INTO l_end_time from v$timer;
293 end if;
294
295 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Load_Headers is (sec) '||((l_end_time-l_start_time)/100));
296
297 IF G_HEADER_REC.HEADER_ID.COUNT = 0 THEN
298 IF l_debug_level > 0 THEN
299 oe_debug_pub.add( 'NO ORDERS IN THIS BATCH , EXIT!' ) ;
300 END IF;
301 RETURN;
302 END IF;
303
304
305 ----------------------------------------------------------------
306 -- Process Headers
307 ----------------------------------------------------------------
308 -- Bug 5640601 =>
309 -- Selecting hsecs from v$times is changed to execute only when debug
310 -- is enabled, as hsec is used for logging only when debug is enabled.
311 IF l_debug_level > 0 Then
312 SELECT hsecs INTO l_start_time from v$timer;
313 end if;
314
315 OE_Bulk_Process_Header.Entity
316 (p_header_rec => G_HEADER_REC
317 ,x_header_scredit_rec => l_header_scredit_rec
318 ,p_defaulting_mode => p_defaulting_mode
319 ,p_process_configurations => p_process_configurations
320 ,p_validate_configurations => p_validate_configurations
321 ,p_schedule_configurations => p_schedule_configurations
322 ,p_validate_desc_flex => p_validate_desc_flex
323 );
324
325 -- Bug 5640601 =>
326 -- Selecting hsecs from v$times is changed to execute only when debug
327 -- is enabled, as hsec is used for logging only when debug is enabled.
328 IF l_debug_level > 0 Then
329 SELECT hsecs INTO l_end_time from v$timer;
330 end if;
331
332
333 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Headers Entity validation is (sec) '||((l_end_time-l_start_time)/100));
334
335
336 -------------------------------------------------------------------
337 -- Insert Messages into DB from above processing call
338 -------------------------------------------------------------------
339
340 IF l_debug_level > 0 THEN
341 oe_debug_pub.add( 'SAVING MESSAGES '||OE_BULK_MSG_PUB.G_MSG_TBL.MESSAGE.COUNT ) ;
342 END IF;
343
344 -- Bug 5640601 =>
345 -- Selecting hsecs from v$times is changed to execute only when debug
346 -- is enabled, as hsec is used for logging only when debug is enabled.
347 IF l_debug_level > 0 Then
348 SELECT hsecs INTO l_start_time from v$timer;
349 end if;
350
351 OE_Bulk_Msg_PUB.Save_Messages(G_REQUEST_ID);
352
353 -- Bug 5640601 =>
354 -- Selecting hsecs from v$times is changed to execute only when debug
355 -- is enabled, as hsec is used for logging only when debug is enabled.
356 IF l_debug_level > 0 Then
357 SELECT hsecs INTO l_end_time from v$timer;
358 end if;
359
360 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Save Messages is (sec) '||((l_end_time-l_start_time)/100));
361
362
363 ------------------------------------------------------------
364 -- Update Headers Interface table for invalid headers.
365 ------------------------------------------------------------
366 -- Bug 5640601 =>
367 -- Selecting hsecs from v$times is changed to execute only when debug
368 -- is enabled, as hsec is used for logging only when debug is enabled.
369 IF l_debug_level > 0 Then
370 SELECT hsecs INTO l_start_time from v$timer;
371 end if;
372 Process_Invalid_Records;
373
374 -- Bug 5640601 =>
375 -- Selecting hsecs from v$times is changed to execute only when debug
376 -- is enabled, as hsec is used for logging only when debug is enabled.
377 IF l_debug_level > 0 Then
378 SELECT hsecs INTO l_end_time from v$timer;
379 end if;
380
381 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Process_Invalid_Records is (sec) '||((l_end_time-l_start_time)/100));
382
383 IF p_validate_only = 'Y' THEN
384 RETURN;
385 END IF;
386
387 -------------------------------------------------------------------
388 -- Create Header Sales Credits
389 -------------------------------------------------------------------
390
391 -- Bug 5640601 =>
392 -- Selecting hsecs from v$times is changed to execute only when debug
393 -- is enabled, as hsec is used for logging only when debug is enabled.
394 IF l_debug_level > 0 Then
395 SELECT hsecs INTO l_start_time from v$timer;
396 end if;
397
398 OE_Bulk_Header_Util.Create_Header_Scredits(l_header_scredit_rec);
399
400 -- Bug 5640601 =>
401 -- Selecting hsecs from v$times is changed to execute only when debug
402 -- is enabled, as hsec is used for logging only when debug is enabled.
403 IF l_debug_level > 0 Then
404 SELECT hsecs INTO l_end_time from v$timer;
405 end if;
406
407 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Create Sales Credits is (sec) '
408 ||((l_end_time-l_start_time)/100));
409
410 EXCEPTION
411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 WHEN OTHERS THEN
414 OE_BULK_MSG_PUB.Add_Exc_Msg
415 ( G_PKG_NAME
416 , 'Process_Headers'
417 );
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419 END Process_Headers;
420
421 PROCEDURE Process_Lines
422 ( p_batch_id IN NUMBER
423 , p_validate_only IN VARCHAR2 DEFAULT 'N'
424 , p_validate_desc_flex IN VARCHAR2 DEFAULT 'Y'
425 , p_defaulting_mode IN VARCHAR2 DEFAULT 'Y'
426 , p_process_configurations IN VARCHAR2 DEFAULT 'N'
427 , p_validate_configurations IN VARCHAR2 DEFAULT 'Y'
428 , p_schedule_configurations IN VARCHAR2 DEFAULT 'N'
429 , p_process_tax IN VARCHAR2 DEFAULT 'N'
430 )
431 IS
432 -- l_line_rec LINE_REC_TYPE;
433 l_start_time NUMBER;
434 l_end_time NUMBER;
435 l_line_scredit_rec OE_BULK_ORDER_PVT.Scredit_Rec_Type;
436 --
437 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
438 --
439 BEGIN
440
441 -- Bulk Load Lines
442
443 -- Bug 5640601 =>
444 -- Selecting hsecs from v$times is changed to execute only when debug
445 -- is enabled, as hsec is used for logging only when debug is enabled.
446 IF l_debug_level > 0 Then
447 SELECT hsecs INTO l_start_time from v$timer;
448 end if;
449 oe_debug_pub.add( 'Process Tax :'|| p_process_tax,1);
450
451 OE_Bulk_Line_Util.Load_Lines
452 (p_batch_id => p_batch_id
453 ,p_process_configurations => p_process_configurations
454 ,p_line_rec => G_LINE_REC);
455 IF l_debug_level > 0 THEN
456 oe_debug_pub.add( 'AFTER LOADING LINES THE COUNT IS'||G_LINE_REC.LINE_ID.COUNT ) ;
457
458 -- Bug 5640601 =>
459 -- Selecting hsecs from v$times is changed to execute only when debug
460 -- is enabled, as hsec is used for logging only when debug is enabled.
461 SELECT hsecs INTO l_end_time from v$timer;
462 END IF;
463
464 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Load_Lines is (sec) '||((l_end_time-l_start_time)/100));
465
466 IF G_LINE_REC.LINE_ID.COUNT = 0 THEN
467 IF l_debug_level > 0 THEN
468 oe_debug_pub.add( 'NO LINES IN THIS BATCH , EXIT!' ) ;
469 END IF;
470 RETURN;
471 END IF;
472
473 -- Pre-Process Lines
474 -- Bug 5640601 =>
475 -- Selecting hsecs from v$times is changed to execute only when debug
476 -- is enabled, as hsec is used for logging only when debug is enabled.
477 IF l_debug_level > 0 Then
478 SELECT hsecs INTO l_start_time from v$timer;
479 end if;
480
481
482 OE_Bulk_Process_Line.Entity
483 (p_line_rec => G_LINE_REC
484 ,p_header_rec => G_HEADER_REC
485 ,x_line_scredit_rec => l_line_scredit_rec
486 ,p_defaulting_mode => p_defaulting_mode
487 ,p_process_configurations => p_process_configurations
488 ,p_validate_configurations => p_validate_configurations
489 ,p_schedule_configurations => p_schedule_configurations
490 ,p_validate_only => p_validate_only
491 ,p_validate_desc_flex => p_validate_desc_flex
492 ,p_process_tax => p_process_tax
493 );
494
495 -- Bug 5640601 =>
496 -- Selecting hsecs from v$times is changed to execute only when debug
497 -- is enabled, as hsec is used for logging only when debug is enabled.
498 IF l_debug_level > 0 Then
499 SELECT hsecs INTO l_end_time from v$timer;
500 end if;
501
502 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Entity Validation is (sec) '||((l_end_time-l_start_time)/100));
503
504
505 -- Insert Messages
506 IF l_debug_level > 0 THEN
507 oe_debug_pub.add( 'SAVING MESSAGES '||OE_BULK_MSG_PUB.G_MSG_TBL.MESSAGE.COUNT ) ;
508 END IF;
509 OE_Bulk_Msg_PUB.Save_Messages(G_REQUEST_ID);
510
511 -- Update Headers Interface for Invalid Lines
512 -- Bug 5640601 =>
513 -- Selecting hsecs from v$times is changed to execute only when debug
514 -- is enabled, as hsec is used for logging only when debug is enabled.
515 IF l_debug_level > 0 Then
516 SELECT hsecs INTO l_start_time from v$timer;
517 end if;
518
519 Process_Invalid_Records;
520
521 -- Bug 5640601 =>
522 -- Selecting hsecs from v$times is changed to execute only when debug
523 -- is enabled, as hsec is used for logging only when debug is enabled.
524 IF l_debug_level > 0 Then
525 SELECT hsecs INTO l_end_time from v$timer;
526 end if;
527
528 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Process_Invalid Records for lines is (sec) '||((l_end_time-l_start_time)/100));
529
530 -------------------------------------------------------------------
531 -- Create Line Sales Credits
532 -------------------------------------------------------------------
533
534 -- Bug 5640601 =>
535 -- Selecting hsecs from v$times is changed to execute only when debug
536 -- is enabled, as hsec is used for logging only when debug is enabled.
537 IF l_debug_level > 0 Then
538 SELECT hsecs INTO l_start_time from v$timer;
539 end if;
540
541 OE_Bulk_Line_Util.Create_Line_Scredits(l_line_scredit_rec);
542
543 -- Bug 5640601 =>
544 -- Selecting hsecs from v$times is changed to execute only when debug
545 -- is enabled, as hsec is used for logging only when debug is enabled.
546 IF l_debug_level > 0 Then
547 SELECT hsecs INTO l_end_time from v$timer;
548 end if;
549
550 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Create Line Sales Credits is (sec) '
551 ||((l_end_time-l_start_time)/100));
552
553 EXCEPTION
554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
556 WHEN OTHERS THEN
557 OE_BULK_MSG_PUB.Add_Exc_Msg
558 ( G_PKG_NAME
559 , 'Process_Lines'
560 );
561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
562 END Process_Lines;
563
564
565 PROCEDURE Post_Process
566 ( p_batch_id IN NUMBER
567 , p_adjustments_exist IN VARCHAR2 DEFAULT 'Y'
568 , p_process_tax IN VARCHAR2 DEFAULT 'N'
569 , p_process_configurations IN VARCHAR2 DEFAULT 'N'
570 , p_validate_configurations IN VARCHAR2 DEFAULT 'Y'
571 , p_schedule_configurations IN VARCHAR2 DEFAULT 'N'
572 )
573 IS
574 l_start_time NUMBER;
575 l_end_time NUMBER;
576 l_return_status VARCHAR2(30);
577 l_msg_count NUMBER;
578 l_msg_data VARCHAR2(2000);
579 l_adjustments_exist VARCHAR2(1); --pibadj
580 l_credit_check_method VARCHAR2(3):='OLD';
581 -- Added for HVOP Tax project
582 l_tax_calculated BOOLEAN;
583 --
584 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
585 --
586 BEGIN
587
588 l_adjustments_exist := p_adjustments_exist; --pibadj
589
590 IF G_HEADER_REC.HEADER_ID.COUNT = 0 THEN
591 IF l_debug_level > 0 THEN
592 oe_debug_pub.add( 'NO ORDERS IN THIS BATCH , EXIT!' ) ;
593 oe_debug_pub.add( ' Process Tax :'|| p_process_tax, 1);
594 END IF;
595 RETURN;
596 END IF;
597
598
599 -------------------------------------------------------------------
600 -- Create Entries in MTL_SALES_ORDERS
601 -------------------------------------------------------------------
602
603 -- Bug 5640601 =>
604 -- Selecting hsecs from v$times is changed to execute only when debug
605 -- is enabled, as hsec is used for logging only when debug is enabled.
606 IF l_debug_level > 0 Then
607 SELECT hsecs INTO l_start_time from v$timer;
608 end if;
609
610 INV_SalesOrder.Create_MTL_Sales_Orders_Bulk
611 (p_api_version_number => 1.0
612 ,p_header_rec => G_HEADER_REC
613 ,x_return_status => l_return_status
614 ,x_message_count => l_msg_count
615 ,x_message_data => l_msg_data
616 );
617
618 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
619 IF l_debug_level > 0 THEN
620 oe_debug_pub.add( 'ERROR IN CREATE_MTL_SALES_ORDERS_BULK :' ||L_RETURN_STATUS ) ;
621 END IF;
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 -- Bug 5640601 =>
626 -- Selecting hsecs from v$times is changed to execute only when debug
627 -- is enabled, as hsec is used for logging only when debug is enabled.
628 IF l_debug_level > 0 Then
629 SELECT hsecs INTO l_end_time from v$timer;
630 end if;
631
632
633 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in MTL_SALES_ORDERS creates is (sec) '
634 ||((l_end_time-l_start_time)/100));
635
636
637 -------------------------------------------------------------------
638 -- Auto-Scheduling
639 -------------------------------------------------------------------
640
641 IF G_SCH_COUNT > 0 THEN
642
643 -- Bug 5640601 =>
644 -- Selecting hsecs from v$times is changed to execute only when debug
645 -- is enabled, as hsec is used for logging only when debug is enabled.
646 IF l_debug_level > 0 Then
647 SELECT hsecs INTO l_start_time from v$timer;
648 end if;
649
650 OE_Bulk_Schedule_Util.Schedule_Orders
651 (p_line_rec => OE_BULK_ORDER_PVT.G_LINE_REC
652 ,p_header_rec => OE_BULK_ORDER_PVT.G_HEADER_REC
653 ,x_return_status => l_return_status
654 );
655
656 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
658 END IF;
659
660 -- Bug 5640601 =>
661 -- Selecting hsecs from v$times is changed to execute only when debug
662 -- is enabled, as hsec is used for logging only when debug is enabled.
663 IF l_debug_level > 0 Then
664 SELECT hsecs INTO l_end_time from v$timer;
665 end if;
666
667 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Scheduling is (sec) '
668 ||((l_end_time-l_start_time)/100));
669
670 END IF;
671
672 -- added for HVOP TAX project
673 --IF p_process_tax = 'Y' THEN bug7685103
674 OE_Bulk_Tax_Util.Get_Default_Tax_Code;
675 --END IF;
676
677
678 --PIB
679 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' Then
680 -- Bug 5640601 =>
681 -- Selecting hsecs from v$times is changed to execute only when debug
682 -- is enabled, as hsec is used for logging only when debug is enabled.
683 IF l_debug_level > 0 Then
684 SELECT hsecs INTO l_start_time from v$timer;
685 end if;
686
687 IF G_PRICING_NEEDED = 'Y' AND QP_UTIL_PUB.Hvop_Pricing_Setup = 'Y'
688 AND NOT G_CATCHWEIGHT THEN --bug 3798477
689 IF l_debug_level > 0 THEN
690 oe_debug_pub.add('before calling OE_BULK_PRICEORDER_PVT.Price_Orders');
691 END IF;
692 OE_BULK_PRICEORDER_PVT.Price_Orders
693 (p_header_rec => OE_BULK_ORDER_PVT.G_HEADER_REC
694 ,p_line_rec => OE_BULK_ORDER_PVT.G_LINE_REC
695 ,p_adjustments_exist => p_adjustments_exist --pibadj
696 ,x_return_status => l_return_status
697 );
698
699 IF l_debug_level > 0 THEN
700 oe_debug_pub.add('after OE_BULK_PRICEORDER_PVT.Price_Orders : return status : '||l_return_status);
701 END IF;
702
703 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
705 END IF;
706
707 G_PRICING_NEEDED := 'N';
708 l_adjustments_exist := 'N'; --pibadj
709 --new credit check should be used since new bulkhvop pricing got activated
710 l_credit_check_method := 'NEW';
711 END IF;
712 -- Bug 5640601 =>
713 -- Selecting hsecs from v$times is changed to execute only when debug
714 -- is enabled, as hsec is used for logging only when debug is enabled.
715 IF l_debug_level > 0 Then
716 SELECT hsecs INTO l_end_time from v$timer;
717 end if;
718
719
720 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in HVOP Pricing is (sec) '
721 ||((l_end_time-l_start_time)/100));
722
723 END IF;
724 --PIB
725
726
727 -- added for HVOP TAX project
728 IF p_process_tax = 'Y' AND
729 G_PRICING_NEEDED = 'N' THEN
730 select hsecs into l_start_time from v$timer;
731
732 OE_Bulk_Tax_Util.Calculate_Tax(p_post_insert => FALSE);
733 select hsecs into l_end_time from v$timer;
734
735 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Tax is (sec) '
736 ||((l_end_time-l_start_time)/100));
737
738 IF G_ERROR_COUNT < G_ERROR_REC.order_source_id.COUNT THEN
739 Process_Invalid_Records;
740 END IF;
741 l_tax_calculated := TRUE;
742 ELSE
743 l_tax_calculated := FALSE;
744 END IF;
745
746
747 -------------------------------------------------------------------
748 -- Insert header and line records into DB, should be done after
749 -- scheduling as updates by scheduling are done directly on globals!
750 -------------------------------------------------------------------
751
752 -- Bug 5640601 =>
753 -- Selecting hsecs from v$times is changed to execute only when debug
754 -- is enabled, as hsec is used for logging only when debug is enabled.
755 IF l_debug_level > 0 Then
756 SELECT hsecs INTO l_start_time from v$timer;
757 end if;
758
759 OE_Bulk_Header_Util.Insert_Headers
760 ( p_header_rec => G_HEADER_REC
761 ,p_batch_id => p_batch_id
762 );
763
764 -- Bug 5640601 =>
765 -- Selecting hsecs from v$times is changed to execute only when debug
766 -- is enabled, as hsec is used for logging only when debug is enabled.
767 IF l_debug_level > 0 Then
768 SELECT hsecs INTO l_end_time from v$timer;
769 end if;
770
771 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Insert_headers is (sec) '||((l_end_time-l_start_time)/100));
772
773 -- Bug 5640601 =>
774 -- Selecting hsecs from v$times is changed to execute only when debug
775 -- is enabled, as hsec is used for logging only when debug is enabled.
776 IF l_debug_level > 0 Then
777 SELECT hsecs INTO l_start_time from v$timer;
778 end if;
779
780 OE_Bulk_Line_Util.Insert_Lines
781 ( p_line_rec => G_LINE_REC
782 );
783
784 -- Bug 5640601 =>
785 -- Selecting hsecs from v$times is changed to execute only when debug
786 -- is enabled, as hsec is used for logging only when debug is enabled.
787 IF l_debug_level > 0 Then
788 SELECT hsecs INTO l_end_time from v$timer;
789 end if;
790
791 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Insert_Lines is (sec) '||((l_end_time-l_start_time)/100));
792
793
794 -------------------------------------------------------------------
795 -- Create Holds
796 -------------------------------------------------------------------
797
798 -- Bug 5640601 =>
799 -- Selecting hsecs from v$times is changed to execute only when debug
800 -- is enabled, as hsec is used for logging only when debug is enabled.
801 IF l_debug_level > 0 Then
802 SELECT hsecs INTO l_start_time from v$timer;
803 end if;
804
805 OE_Bulk_Holds_Pvt.Create_Holds;
806
807 -- Bug 5640601 =>
808 -- Selecting hsecs from v$times is changed to execute only when debug
809 -- is enabled, as hsec is used for logging only when debug is enabled.
810 IF l_debug_level > 0 Then
811 SELECT hsecs INTO l_end_time from v$timer;
812 end if;
813
814 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Evaluating Holds is (sec) '
815 ||((l_end_time-l_start_time)/100));
816
817
818 -------------------------------------------------------------------
819 -- Update DBI log tables
820 -------------------------------------------------------------------
821
822 IF G_DBI_INSTALLED = 'Y' THEN
823
824 -- Bug 5640601 =>
825 -- Selecting hsecs from v$times is changed to execute only when debug
826 -- is enabled, as hsec is used for logging only when debug is enabled.
827 IF l_debug_level > 0 Then
828 SELECT hsecs INTO l_start_time from v$timer;
829 end if;
830
831 Update_DBI_Log(p_line_rec => G_LINE_REC
832 ,x_return_status => l_return_status
833 );
834
835 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
838 RAISE FND_API.G_EXC_ERROR;
839 END IF;
840
841 -- Bug 5640601 =>
842 -- Selecting hsecs from v$times is changed to execute only when debug
843 -- is enabled, as hsec is used for logging only when debug is enabled.
844 IF l_debug_level > 0 Then
845 SELECT hsecs INTO l_end_time from v$timer;
846 end if;
847
848 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Update DBI Logs is (sec) '
849 ||((l_end_time-l_start_time)/100));
850
851 END IF;
852
853
854 -------------------------------------------------------------------
855 -- Deleting Error Records from Headers and Lines
856 -------------------------------------------------------------------
857
858 /* Moving it to post pricing */
859 -- Delete_Error_Records(p_batch_id);
860
861
862 -------------------------------------------------------------------
863 -- Pricing Steps
864 -- 1. Create Price Adjustments from Interface Tables
865 -- 2. Price the Order
866 -- Open Issue: What about scheduling/holds evaluation on free good lines?
867 -------------------------------------------------------------------
868
869 IF l_adjustments_exist = 'Y' THEN --pibadj
870
871 -- Bug 5640601 =>
872 -- Selecting hsecs from v$times is changed to execute only when debug
873 -- is enabled, as hsec is used for logging only when debug is enabled.
874 IF l_debug_level > 0 Then
875 SELECT hsecs INTO l_start_time from v$timer;
876 end if;
877
878 OE_Bulk_Price_Pvt.Insert_Adjustments(p_batch_id, l_return_status);
879
880 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
882 END IF;
883
884 -- Bug 5640601 =>
885 -- Selecting hsecs from v$times is changed to execute only when debug
886 -- is enabled, as hsec is used for logging only when debug is enabled.
887 IF l_debug_level > 0 Then
888 SELECT hsecs INTO l_end_time from v$timer;
889 end if;
890
891 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Inserting Price Adjs is (sec) '
892 ||((l_end_time-l_start_time)/100));
893
894 END IF;
895
896 IF G_PRICING_NEEDED = 'Y' OR (G_CC_REQUIRED = 'Y' and l_credit_check_method = 'OLD')
897 OR (p_process_tax = 'Y' and NOT l_tax_calculated)
898 THEN
899
900 -- Bug 5640601 =>
901 -- Selecting hsecs from v$times is changed to execute only when debug
902 -- is enabled, as hsec is used for logging only when debug is enabled.
903 IF l_debug_level > 0 Then
904 SELECT hsecs INTO l_start_time from v$timer;
905 end if;
906
907 OE_Bulk_Price_Pvt.Price_Orders
908 (p_header_rec => G_HEADER_REC
909 ,x_return_status => l_return_status
910 ,p_process_tax => p_process_tax
911 );
912
913 G_CATCHWEIGHT := FALSE; --bug 3798477
914
915 -- Added for bugfix 4180619
916 IF G_ERROR_COUNT < G_ERROR_REC.order_source_id.COUNT THEN
917 Process_Invalid_Records;
918 END IF;
919 -- End
920
921 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
923 END IF;
924
925 -- Bug 5640601 =>
926 -- Selecting hsecs from v$times is changed to execute only when debug
927 -- is enabled, as hsec is used for logging only when debug is enabled.
928 IF l_debug_level > 0 Then
929 SELECT hsecs INTO l_end_time from v$timer;
930 end if;
931
932 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Pricing is (sec) '
933 ||((l_end_time-l_start_time)/100));
934
935 ELSIF (G_CC_REQUIRED = 'Y' and l_credit_check_method = 'NEW') THEN --bug 4558078
936 OE_BULK_PRICEORDER_PVT.credit_check(OE_BULK_ORDER_PVT.G_HEADER_REC);
937 END IF;
938 --PIB }
939
940 -------------------------------------------------------------------
941 -- Deleting Error Records from Headers and Lines
942 -------------------------------------------------------------------
943
944 -- Moved here for bugfix 4180619
945 Delete_Error_Records(p_batch_id,
946 p_adjustments_exist,
947 p_process_tax,
948 p_process_configurations);
949
950 -------------------------------------------------------------------
951 -- Process Acknowledgments
952 -------------------------------------------------------------------
953
954 IF G_ACK_NEEDED = 'Y' THEN
955
956 -- Bug 5640601 =>
957 -- Selecting hsecs from v$times is changed to execute only when debug
958 -- is enabled, as hsec is used for logging only when debug is enabled.
959 IF l_debug_level > 0 Then
960 SELECT hsecs INTO l_start_time from v$timer;
961 end if;
962
963 OE_Bulk_Ack_Pvt.Process_Acknowledgments(p_batch_id, l_return_status);
964
965 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
966 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
967 END IF;
968
969 -- Bug 5640601 =>
970 -- Selecting hsecs from v$times is changed to execute only when debug
971 -- is enabled, as hsec is used for logging only when debug is enabled.
972 IF l_debug_level > 0 Then
973 SELECT hsecs INTO l_end_time from v$timer;
974 end if;
975
976 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Acknowledgments is (sec) '
977 ||((l_end_time-l_start_time)/100));
978
979 END IF;
980
981
982 -------------------------------------------------------------------
983 -- Start Header and Line Workflows
984 -------------------------------------------------------------------
985
986 -- Bug 5640601 =>
987 -- Selecting hsecs from v$times is changed to execute only when debug
988 -- is enabled, as hsec is used for logging only when debug is enabled.
989 IF l_debug_level > 0 Then
990 SELECT hsecs INTO l_start_time from v$timer;
991 end if;
992
993 OE_Bulk_WF_Util.Start_Flows(p_header_rec => G_HEADER_REC
994 ,p_line_rec => G_LINE_REC
995 ,x_return_status => l_return_status
996 );
997
998 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1000 END IF;
1001
1002 -- Bug 5640601 =>
1003 -- Selecting hsecs from v$times is changed to execute only when debug
1004 -- is enabled, as hsec is used for logging only when debug is enabled.
1005 IF l_debug_level > 0 Then
1006 SELECT hsecs INTO l_end_time from v$timer;
1007 end if;
1008
1009 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Starting Workflows is (sec) '
1010 ||((l_end_time-l_start_time)/100));
1011
1012
1013 -------------------------------------------------------------------
1014 -- OM-WSH-HVOP bulk shipping call.
1015 -------------------------------------------------------------------
1016
1017
1018 -- Bug 5640601 =>
1019 -- Selecting hsecs from v$times is changed to execute only when debug
1020 -- is enabled, as hsec is used for logging only when debug is enabled.
1021 IF l_debug_level > 0 Then
1022 SELECT hsecs INTO l_start_time from v$timer;
1023 end if;
1024
1025 IF G_LINE_REC.shipping_eligible_flag.COUNT > 0 THEN
1026
1027 IF l_debug_level > 0 THEN
1028 oe_debug_pub.add('eligible lines exist,call WSH', 5);
1029 END IF;
1030
1031 OE_Shipping_Integration_Pvt.OM_To_WSH_Interface
1032 ( p_line_rec => G_LINE_REC
1033 ,p_header_rec => G_HEADER_REC
1034 ,x_return_status => l_return_status);
1035
1036 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1038 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1039 RAISE FND_API.G_EXC_ERROR;
1040 END IF;
1041
1042 END IF;
1043
1044 -- Bug 5640601 =>
1045 -- Selecting hsecs from v$times is changed to execute only when debug
1046 -- is enabled, as hsec is used for logging only when debug is enabled.
1047 IF l_debug_level > 0 Then
1048 SELECT hsecs INTO l_end_time from v$timer;
1049 end if;
1050
1051 FND_FILE.PUT_LINE
1052 (FND_FILE.LOG,'Time spent in OM to WSH Interface is (sec) '
1053 ||((l_end_time-l_start_time)/100));
1054
1055
1056 EXCEPTION
1057 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1059 WHEN OTHERS THEN
1060 OE_BULK_MSG_PUB.Add_Exc_Msg
1061 ( G_PKG_NAME
1062 , 'Post_Process'
1063 );
1064 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1065 END Post_Process;
1066
1067
1068 ---------------------------------------------------------------
1069 -- PUBLIC PROCEDURES
1070 ---------------------------------------------------------------
1071
1072 PROCEDURE Process_Batch
1073 ( p_batch_id IN NUMBER
1074 , p_validate_only IN VARCHAR2 DEFAULT 'N'
1075 , p_validate_desc_flex IN VARCHAR2 DEFAULT 'Y'
1076 , p_defaulting_mode IN VARCHAR2 DEFAULT 'N'
1077 , p_process_configurations IN VARCHAR2 DEFAULT 'N'
1078 , p_validate_configurations IN VARCHAR2 DEFAULT 'Y'
1079 , p_schedule_configurations IN VARCHAR2 DEFAULT 'N'
1080 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1081 , p_process_tax IN VARCHAR2 DEFAULT 'N'
1082 , x_msg_count OUT NOCOPY NUMBER
1083
1084 , x_msg_data OUT NOCOPY VARCHAR2
1085
1086 , x_return_status OUT NOCOPY VARCHAR
1087
1088 )
1089 IS
1090 l_adjustments_exist VARCHAR2(1);
1091 l_start_time NUMBER;
1092 l_end_time NUMBER;
1093 --
1094 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1095 --
1096 BEGIN
1097 oe_debug_pub.add( ' In Process Batch : p_process_configurations :'|| p_process_configurations );
1098 oe_debug_pub.add( ' Process Tax :'|| p_process_tax ,1);
1099 -- Initialize Return Status
1100
1101 x_return_status := FND_API.G_RET_STS_SUCCESS;
1102
1103 -- Initialize message list
1104
1105 IF FND_API.to_Boolean(p_init_msg_list) THEN
1106 OE_BULK_MSG_PUB.initialize;
1107 END IF;
1108
1109 -- Establish SAVEPOINT
1110
1111 SAVEPOINT Process_Batch;
1112
1113 -- Initialize Global Error Record
1114
1115 G_ERROR_REC.order_source_id := OE_WSH_BULK_GRP.T_NUM();
1116 G_ERROR_REC.orig_sys_document_ref := OE_WSH_BULK_GRP.T_V50();
1117 G_ERROR_REC.header_id := OE_WSH_BULK_GRP.T_NUM();
1118 G_ERROR_COUNT := 0;
1119
1120 -- Populate parameter if adjustments exist for this batch
1121
1122 BEGIN
1123
1124 SELECT 'Y'
1125 INTO l_adjustments_exist
1126 FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
1127 WHERE h.batch_id = p_batch_id
1128 AND a.order_source_id = h.order_source_id
1129 AND a.orig_sys_document_ref = h.orig_sys_document_ref
1130 AND rownum = 1;
1131
1132 EXCEPTION
1133 WHEN NO_DATA_FOUND THEN
1134 l_adjustments_exist := 'N';
1135 END;
1136
1137 -- Initialize Batch Global Parameters
1138
1139 G_PRICING_NEEDED := 'N';
1140 G_ACK_NEEDED := 'N';
1141 G_SCH_COUNT := 0;
1142 -- Initialize Credit Checking Globals
1143 G_REALTIME_CC_REQUIRED := 'N';
1144 G_CC_REQUIRED := 'N';
1145
1146 -- Initialize Batch Global Records
1147
1148 OE_Bulk_Holds_PVT.Initialize_Holds_Tbl;
1149
1150
1151 -------------------------------------------------------------------
1152 -- I. Pre-processing - includes:
1153 -- a. Order Import Pre-Processing Steps from OEXVIMSB.pls 115.51
1154 -- b. Validations to ensure that orders meet pre-req criteria
1155 -- for BULK processing
1156 -------------------------------------------------------------------
1157
1158 -- Bug 5640601 =>
1159 -- Selecting hsecs from v$times is changed to execute only when debug
1160 -- is enabled, as hsec is used for logging only when debug is enabled.
1161 IF l_debug_level > 0 Then
1162 SELECT hsecs INTO l_start_time from v$timer;
1163 end if;
1164 OE_BULK_VALIDATE.Pre_Process(p_batch_id);
1165 -- Bug 5640601 =>
1166 -- Selecting hsecs from v$times is changed to execute only when debug
1167 -- is enabled, as hsec is used for logging only when debug is enabled.
1168 IF l_debug_level > 0 Then
1169 SELECT hsecs INTO l_end_time from v$timer;
1170 end if;
1171 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Pre_Process is (sec) '
1172 ||((l_end_time-l_start_time)/100));
1173
1174
1175 -------------------------------------------------------------------
1176 -- II. Attribute Validations on the Interface Tables
1177 -------------------------------------------------------------------
1178
1179 -- Bug 5640601 =>
1180 -- Selecting hsecs from v$times is changed to execute only when debug
1181 -- is enabled, as hsec is used for logging only when debug is enabled.
1182 IF l_debug_level > 0 Then
1183 SELECT hsecs INTO l_start_time from v$timer;
1184 end if;
1185 OE_BULK_VALIDATE.Attributes(p_batch_id, l_adjustments_exist);
1186 -- Bug 5640601 =>
1187 -- Selecting hsecs from v$times is changed to execute only when debug
1188 -- is enabled, as hsec is used for logging only when debug is enabled.
1189 IF l_debug_level > 0 Then
1190 SELECT hsecs INTO l_end_time from v$timer;
1191 end if;
1192 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Attribute Validation is (sec) '
1193 ||((l_end_time-l_start_time)/100));
1194
1195
1196 -------------------------------------------------------------------
1197 -- III. Value to ID conversions
1198 -------------------------------------------------------------------
1199
1200 -- Bug 5640601 =>
1201 -- Selecting hsecs from v$times is changed to execute only when debug
1202 -- is enabled, as hsec is used for logging only when debug is enabled.
1203 IF l_debug_level > 0 Then
1204 SELECT hsecs INTO l_start_time from v$timer;
1205 end if;
1206
1207 -- Value to ID for Header Attributes
1208
1209 OE_Bulk_Value_To_Id.Headers(p_batch_id);
1210
1211 -- Value to ID for Line Attributes
1212
1213 OE_Bulk_Value_To_Id.Lines(p_batch_id);
1214
1215 -- Value to ID for Adjustment Attributes
1216
1217 IF l_adjustments_exist = 'Y' THEN
1218 OE_Bulk_Value_To_Id.Adjustments(p_batch_id);
1219 END IF;
1220
1221 -- Process Error Messages from Value to ID conversions
1222
1223 OE_Bulk_Value_To_Id.INSERT_ERROR_MESSAGES(p_batch_id);
1224
1225 -- Bug 5640601 =>
1226 -- Selecting hsecs from v$times is changed to execute only when debug
1227 -- is enabled, as hsec is used for logging only when debug is enabled.
1228 IF l_debug_level > 0 Then
1229 SELECT hsecs INTO l_end_time from v$timer;
1230 end if;
1231 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Value To ID is (sec) '
1232 ||((l_end_time-l_start_time)/100));
1233
1234
1235 -------------------------------------------------------------------
1236 -- Mark Error Status on Interface Tables
1237 -------------------------------------------------------------------
1238
1239 OE_BULK_VALIDATE.MARK_INTERFACE_ERROR(p_batch_id);
1240 Process_Invalid_Records;
1241
1242 -------------------------------------------------------------------
1243 -- Pre_process Configurations
1244 -------------------------------------------------------------------
1245
1246
1247 IF l_debug_level > 0 THEN
1248 oe_debug_pub.add( 'p_process_configurations = '|| p_process_configurations ) ;
1249 END IF;
1250
1251 IF (p_process_configurations = 'Y') THEN
1252
1253 IF l_debug_level > 0 THEN
1254 oe_debug_pub.add( 'CALLING OE_BULK_CONFIG_UTIL.Pre_Process ' ) ;
1255 END IF;
1256
1257 SELECT hsecs INTO l_start_time from v$timer;
1258
1259 OE_BULK_CONFIG_UTIL.Pre_Process
1260 (p_batch_id => p_batch_id,
1261 p_validate_only => p_validate_only,
1262 p_use_configurator => G_CONFIGURATOR_USED,
1263 p_validate_configurations => p_validate_configurations);
1264
1265 SELECT hsecs INTO l_end_time from v$timer;
1266 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in CONFIG Pre_Process is (sec) '
1267 ||((l_end_time-l_start_time)/100));
1268
1269 END IF;
1270
1271
1272 -------------------------------------------------------------------
1273 -- Mark Error Status on Interface Tables
1274 -------------------------------------------------------------------
1275
1276 OE_BULK_VALIDATE.MARK_INTERFACE_ERROR(p_batch_id);
1277
1278
1279 Process_Invalid_Records;
1280
1281
1282 -------------------------------------------------------------------
1283 -- IV. Process Order Headers in this Batch
1284 -------------------------------------------------------------------
1285
1286 Process_Headers(p_batch_id => p_batch_id
1287 ,p_validate_only => p_validate_only
1288 ,p_validate_desc_flex => p_validate_desc_flex
1289 ,p_defaulting_mode => p_defaulting_mode
1290 ,p_process_configurations => p_process_configurations
1291 ,p_validate_configurations => p_validate_configurations
1292 ,p_schedule_configurations => p_schedule_configurations);
1293
1294
1295 -------------------------------------------------------------------
1296 -- V. Process Order Lines in this Batch
1297 -------------------------------------------------------------------
1298
1299 Process_Lines(p_batch_id => p_batch_id
1300 ,p_validate_only => p_validate_only
1301 ,p_validate_desc_flex => p_validate_desc_flex
1302 ,p_defaulting_mode => p_defaulting_mode
1303 ,p_process_configurations => p_process_configurations
1304 ,p_validate_configurations => p_validate_configurations
1305 ,p_schedule_configurations => p_schedule_configurations
1306 ,p_process_tax => p_process_tax);
1307
1308
1309
1310 -------------------------------------------------------------------
1311 -- If validation only mode, no further processing is needed.
1312 -------------------------------------------------------------------
1313 IF p_validate_only = 'Y' THEN
1314 RETURN;
1315 END IF;
1316
1317
1318 -------------------------------------------------------------------
1319 -- VII. Post-Processing: This includes all processing steps that
1320 -- need to be done after order header and line records are validated.
1321 -- Creation of Default Sales Credits/Entries in MTL_SALES_ORDERS
1322 -- Creation of Included Items
1323 -- Holds Evaluation
1324 -- Auto-Scheduling
1325 -- Creation of Price Adjustments, Pricing Engine Calls
1326 -- Starting Workflows
1327 -------------------------------------------------------------------
1328
1329 Post_Process(p_batch_id => p_batch_id
1330 ,p_adjustments_exist => l_adjustments_exist
1331 ,p_process_tax => p_process_tax
1332 ,p_process_configurations => p_process_configurations
1333 ,p_validate_configurations => p_validate_configurations
1334 ,p_schedule_configurations => p_schedule_configurations);
1335
1336
1337 -- Get message count and data
1338
1339 OE_BULK_MSG_PUB.Count_And_Get
1340 ( p_count => x_msg_count
1341 , p_data => x_msg_data
1342 );
1343
1344
1345 EXCEPTION
1346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1347 IF l_debug_level > 0 THEN
1348 oe_debug_pub.add( 'UNEXP ERROR IN PROCESS_BATCH' ) ;
1349 END IF;
1350 ROLLBACK TO SAVEPOINT Process_Batch;
1351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1352 -- Get message count and data
1353 OE_BULK_MSG_PUB.Count_And_Get
1354 ( p_count => x_msg_count
1355 , p_data => x_msg_data
1356 );
1357 WHEN OTHERS THEN
1358 IF l_debug_level > 0 THEN
1359 oe_debug_pub.add( 'OTHERS ERROR IN PROCESS_BATCH' ) ;
1360 END IF;
1361 IF l_debug_level > 0 THEN
1362 oe_debug_pub.add( 'SQL ERROR :'||SQLERRM ) ;
1363 END IF;
1364 ROLLBACK TO SAVEPOINT Process_Batch;
1365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1366 OE_BULK_MSG_PUB.Add_Exc_Msg
1367 ( G_PKG_NAME
1368 , 'Process_Batch'
1369 );
1370 -- Get message count and data
1371 OE_BULK_MSG_PUB.Count_And_Get
1372 ( p_count => x_msg_count
1373 , p_data => x_msg_data
1374 );
1375 END Process_Batch;
1376
1377 FUNCTION GET_FLEX_ENABLED_FLAG(p_flex_name VARCHAR2)
1378 RETURN VARCHAR2
1379 IS
1380 l_count NUMBER;
1381 --
1382 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1383 --
1384 BEGIN
1385 SELECT count(*)
1386 INTO l_count
1387 FROM fnd_descr_flex_column_usages
1388 WHERE APPLICATION_ID = 660
1389 AND DESCRIPTIVE_FLEXFIELD_NAME = p_flex_name
1390 AND ENABLED_FLAG = 'Y'
1391 AND ROWNUM = 1;
1392
1393 IF l_count = 1 THEN
1394 RETURN 'Y';
1395 ELSE
1396 RETURN 'N';
1397 END IF;
1398
1399 EXCEPTION
1400 WHEN OTHERS THEN
1401 RETURN 'N';
1402 END GET_FLEX_ENABLED_FLAG;
1403
1404 PROCEDURE mark_header_error(p_header_index IN NUMBER,
1405 p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE)
1406 IS
1407 error_count NUMBER := OE_Bulk_Order_Pvt.G_ERROR_REC.header_id.COUNT;
1408 --
1409 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1410 --
1411 BEGIN
1412
1413 IF l_debug_level > 0 THEN
1414 oe_debug_pub.add( 'ENTERING OE_BULK_ORDER_PVT.MARK_HEADER_ERROR' ) ;
1415 oe_debug_pub.add('The error count is '|| error_count);
1416 END IF;
1417
1418 error_count := error_count + 1;
1419
1420 OE_Bulk_Order_Pvt.G_ERROR_REC.order_source_id.EXTEND(1);
1421 OE_Bulk_Order_Pvt.G_ERROR_REC.order_source_id(error_count)
1422 := p_header_rec.order_source_id(p_header_index);
1423
1424 OE_Bulk_Order_Pvt.G_ERROR_REC.orig_sys_document_ref.EXTEND(1);
1425 OE_Bulk_Order_Pvt.G_ERROR_REC.orig_sys_document_ref(error_count)
1426 := p_header_rec.orig_sys_document_ref(p_header_index);
1427
1428 OE_Bulk_Order_Pvt.G_ERROR_REC.header_id.EXTEND(1);
1429 OE_Bulk_Order_Pvt.G_ERROR_REC.header_id(error_count)
1430 := p_header_rec.header_id(p_header_index);
1431
1432 IF l_debug_level > 0 THEN
1433 oe_debug_pub.add( 'EXITING OE_BULK_ORDER_PVT.MARK_HEADER_ERROR' ) ;
1434 END IF;
1435
1436 EXCEPTION
1437 WHEN OTHERS THEN
1438 IF l_debug_level > 0 THEN
1439 oe_debug_pub.add( 'in others exception ' || SQLERRM ) ;
1440 END IF;
1441 IF OE_BULK_MSG_PUB.check_msg_level(OE_BULK_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1442 THEN
1443 OE_BULK_MSG_PUB.add_exc_msg
1444 (G_PKG_NAME
1445 ,'Mark_Header_Error'
1446 );
1447 END IF;
1448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1449 END Mark_Header_Error;
1450
1451
1452 END OE_BULK_ORDER_PVT;
[Home] [Help]