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