1 PACKAGE BODY OE_BULK_ORDER_IMPORT_PVT AS
2 /* $Header: OEBVIMNB.pls 120.21 2011/04/07 13:57:28 vmachett ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OE_BULK_ORDER_IMPORT_PVt';
6
7
8 -----------------------------------------------------------------
9 -- DATA TYPES (RECORD/TABLE TYPES)
10 -----------------------------------------------------------------
11
12 TYPE number_arr IS TABLE OF number;
13 TYPE char30_arr IS TABLE OF varchar2(30);
14 TYPE char50_arr IS TABLE OF varchar2(50);
15
16 TYPE Order_Rec_Type IS RECORD
17 ( order_source_id number_arr := number_arr()
18 , orig_sys_document_ref char50_arr := char50_arr()
19 , num_lines number_arr := number_arr()
20 , request_id number_arr := number_arr()
21 , batch_id number_arr := number_arr()
22 , org_id number_arr := number_arr()
23
24 );
25
26 TYPE Instance_Rec_Type IS RECORD
27 ( request_id NUMBER
28 , total_lines NUMBER := 0
29 );
30 TYPE Instance_Tbl_Type IS TABLE OF Instance_Rec_Type
31 INDEX BY BINARY_INTEGER;
32
33 TYPE Batch_Rec_Type IS RECORD
34 ( batch_id NUMBER
35 , total_lines NUMBER
36 , org_id NUMBER -- For MOAC
37 );
38 TYPE Batch_Tbl_Type IS TABLE OF Batch_Rec_Type
39 INDEX BY BINARY_INTEGER;
40
41
42 ---------------------------------------------------------------------
43 -- LOCAL PROCEDURES/FUNCTIONS
44 ---------------------------------------------------------------------
45
46 PROCEDURE Initialize_Request
47 ( p_request_id IN NUMBER
48 , p_validate_desc_flex IN VARCHAR2
49 , p_rtrim_data IN VARCHAR2
50 , x_return_status OUT NOCOPY VARCHAR2
51 )IS
52 l_start_time NUMBER;
53 l_end_time NUMBER;
54 BEGIN
55
56 x_return_status := FND_API.G_RET_STS_SUCCESS;
57
58 ------------------------------------------------------------
59 -- Initialize Request level globals/variables, these will be
60 -- used in the batch processing APIs later.
61 -- These do not vary with the batch being processed
62 -- and are common for a request.
63 -- It will also include profile options that do not change
64 -- for a given request_id
65 ------------------------------------------------------------
66
67 -- Set the globals in OE_BULK_ORDER_PVT using system parameters
68
69 -- Initialize Request ID Global
70
71 OE_BULK_ORDER_PVT.G_REQUEST_ID := p_request_id;
72
73 -- This ensures that the WF selector functions do not test context.
74
75 OE_STANDARD_WF.RESET_APPS_CONTEXT_OFF;
76
77 -- Initialize Global Flex Status
78
79 IF p_validate_desc_flex = 'Y' THEN
80
81 OE_BULK_ORDER_PVT.G_OE_HEADER_ATTRIBUTES :=
82 OE_BULK_ORDER_PVT.GET_FLEX_ENABLED_FLAG('OE_HEADER_ATTRIBUTES');
83 OE_BULK_ORDER_PVT.G_OE_HEADER_GLOBAL_ATTRIBUTE :=
84 OE_BULK_ORDER_PVT.GET_FLEX_ENABLED_FLAG('OE_HEADER_GLOBAL_ATTRIBUTE');
85 OE_BULK_ORDER_PVT.G_OE_HEADER_TP_ATTRIBUTES :=
86 OE_BULK_ORDER_PVT.GET_FLEX_ENABLED_FLAG('OE_HEADER_TP_ATTRIBUTES');
87
88 OE_BULK_ORDER_PVT.G_OE_LINE_ATTRIBUTES :=
89 OE_BULK_ORDER_PVT.GET_FLEX_ENABLED_FLAG('OE_LINE_ATTRIBUTES');
90 OE_BULK_ORDER_PVT.G_OE_LINE_INDUSTRY_ATTRIBUTE :=
91 OE_BULK_ORDER_PVT.GET_FLEX_ENABLED_FLAG('OE_LINE_INDUSTRY_ATTRIBUTE');
92 OE_BULK_ORDER_PVT.G_OE_LINE_TP_ATTRIBUTES :=
93 OE_BULK_ORDER_PVT.GET_FLEX_ENABLED_FLAG('OE_LINE_TP_ATTRIBUTES');
94
95 END IF;
96
97 -- Initialize OE_GLOBALS.g_org_id
98 --OE_GLOBALS.SET_CONTEXT; removed as part of MOAC
99
100 IF OE_GLOBALS.G_EC_INSTALLED IS NULL THEN
101 OE_GLOBALS.G_EC_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(175);
102 END IF;
103
104 ----------------------------------------------------------
105 -- Set the Global to RTRIM interface data
106 ----------------------------------------------------------
107 G_RTRIM_IFACE_DATA := p_rtrim_data;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 FND_FILE.PUT_LINE(FND_FILE.LOG,'Initialize_Request Error :'||substr(sqlerrm,1,200));
112 OE_BULK_MSG_PUB.Add_Exc_Msg
113 ( G_PKG_NAME
114 , 'Initialize_Request'
115 );
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
117 END Initialize_Request;
118
119 PROCEDURE Initialize_Batch
120 ( x_return_status OUT NOCOPY VARCHAR2
121 )IS
122 l_start_time NUMBER;
123 l_end_time NUMBER;
124 --
125 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
126 --
127 BEGIN
128
129 x_return_status := FND_API.G_RET_STS_SUCCESS;
130
131 ------------------------------------------------------------
132 -- Initialize Org Level globals/variables
133 -- used in the batch processing APIs later.
134 -- These do not vary for an org_id being processed
135 -- It will also include system parameters that only change
136 -- during the OU change.
137 ------------------------------------------------------------
138
139 OE_BULK_ORDER_PVT.G_SCHEDULE_LINE_ON_HOLD := OE_Sys_Parameters.value('ONT_SCHEDULE_LINE_ON_HOLD');
140
141 OE_BULK_ORDER_PVT.G_RESERVATION_TIME_FENCE := OE_Sys_Parameters.value('ONT_RESERVATION_TIME_FENCE');
142
143 OE_BULK_ORDER_PVT.G_ITEM_ORG := OE_Sys_Parameters.value('MASTER_ORGANIZATION_ID');
144
145 OE_BULK_ORDER_PVT.G_SOB_ID := OE_Sys_Parameters.value('SET_OF_BOOKS_ID');
146
147 OE_BULK_ORDER_PVT.G_CUST_RELATIONS := OE_Sys_Parameters.value('CUSTOMER_RELATIONSHIPS_FLAG');
148
149 OE_BULK_ORDER_PVT.G_CUST_RELATIONS := OE_Sys_Parameters.value('CUSTOMER_RELATIONSHIPS_FLAG');
150
151
152 ----------------------------------------------------------
153 -- Load Hold Sources into globals
154 ----------------------------------------------------------
155 -- Bug 5640601 =>
156 -- Selecting hsecs from v$times is changed to execute only when debug
157 -- is enabled, as hsec is used for logging only when debug is enabled.
158 IF l_debug_level > 0 THEN
159 SELECT hsecs INTO l_start_time from v$timer;
160 END IF;
161
162 -- Load the Hold Sources Globals
163 OE_Bulk_Holds_PVT.Load_Hold_Sources;
164
165 -- Bug 5640601 =>
166 -- Selecting hsecs from v$times is changed to execute only when debug
167 -- is enabled, as hsec is used for logging only when debug is enabled.
168 IF l_debug_level > 0 THEN
169 SELECT hsecs INTO l_end_time from v$timer;
170 END IF;
171
172 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Load Hold Sources is (sec) '
173 ||((l_end_time-l_start_time)/100));
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 FND_FILE.PUT_LINE(FND_FILE.LOG,'Initialize_Batch Error :'||substr(sqlerrm,1,200));
178 OE_BULK_MSG_PUB.Add_Exc_Msg
179 ( G_PKG_NAME
180 , 'Initialize_Batch'
181 );
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183 END Initialize_Batch;
184
185
186 PROCEDURE Post_Process
187 ( p_batch_id IN NUMBER
188 , p_validate_only IN VARCHAR2
189 , x_return_status OUT NOCOPY VARCHAR
190 )IS
191 BEGIN
192
193 x_return_status := FND_API.G_RET_STS_SUCCESS;
194
195 -- No need to delete records if it is validation only mode
196
197 if p_validate_only = 'Y' then
198 return;
199 end if;
200
201 -- Delete all successfully processed records from interface
202 -- tables
203
204 delete from oe_actions_interface
205 where (order_source_id, orig_sys_document_ref) IN
206 (select order_source_id, orig_sys_document_ref
207 from oe_headers_iface_all
208 where batch_id = p_batch_id
209 and nvl(error_flag,'N') = 'N');
210
211 --ER 9060917
212 If NVL (Fnd_Profile.Value('ONT_HVOP_DROP_INVALID_LINES'), 'N')='Y' then
213
214 DELETE FROM oe_price_adjs_interface
215 WHERE(order_source_id, orig_sys_document_ref) IN
216 (SELECT order_source_id,
217 orig_sys_document_ref
218 FROM oe_headers_iface_all a
219 WHERE batch_id = p_batch_id
220 AND nvl(error_flag,'N') = 'N'
221 AND EXISTS
222 (SELECT 1
223 FROM oe_lines_iface_all b
224 WHERE b.orig_sys_document_ref = a.orig_sys_document_ref
225 AND b.order_source_id = a.order_source_id
226 AND batch_id = p_batch_id
227 AND nvl(error_flag, 'N') = 'N'));
228
229 DELETE FROM oe_price_adjs_interface
230 WHERE(order_source_id, orig_sys_line_ref, orig_sys_document_ref) IN
231 (SELECT a.order_source_id,
232 a.orig_sys_line_ref,
233 a.orig_sys_document_ref
234 FROM oe_lines_iface_all a
235 WHERE nvl(a.error_flag, 'N') = 'N'
236 AND(a.order_source_id, a.orig_sys_document_ref) IN
237 (SELECT order_source_id,
238 orig_sys_document_ref
239 FROM oe_headers_iface_all b
240 WHERE nvl(a.error_flag, 'N') = 'N'
241 AND batch_id = p_batch_id));
242
243
244 UPDATE oe_headers_iface_all a
245 SET operation_code = 'UPDATE'
246 WHERE a.batch_id = p_batch_id
247 AND nvl(error_flag,'N') = 'N'
248 AND(order_source_id, orig_sys_document_ref) IN
249 (SELECT b.order_source_id,
250 b.orig_sys_document_ref
251 FROM oe_lines_iface_all b
252 WHERE a.orig_sys_document_ref = b.orig_sys_document_ref
253 AND a.order_source_id = b.order_source_id
254 AND nvl(error_flag, 'N') = 'Y'
255 AND EXISTS
256 (SELECT 'X'
257 FROM oe_lines_iface_all c
258 WHERE c.orig_sys_document_ref = a.orig_sys_document_ref
259 AND c.order_source_id = a.order_source_id
260 AND nvl(error_flag, 'N') = 'N'));
261
262
263 DELETE FROM oe_lines_iface_all
264 WHERE(order_source_id, orig_sys_document_ref) IN
265 (SELECT order_source_id,orig_sys_document_ref
266 FROM oe_headers_iface_all
267 WHERE batch_id = p_batch_id
268 AND nvl(error_flag, 'N') = 'N')
269 AND nvl(error_flag, 'N') = 'N';
270
271 DELETE FROM oe_headers_iface_all a
272 WHERE batch_id = p_batch_id
273 AND nvl(error_flag, 'N') = 'N'
274 AND NOT EXISTS
275 (SELECT 1
276 FROM oe_lines_iface_all b
277 WHERE b.orig_sys_document_ref = a.orig_sys_document_ref
278 AND b.order_source_id = a.order_source_id
279 AND nvl(error_flag, 'N') = 'Y');
280
281
282 --ER 9060917
283 else
284 delete from oe_price_adjs_interface
285 where (order_source_id, orig_sys_document_ref) IN
286 (select order_source_id, orig_sys_document_ref
287 from oe_headers_iface_all
288 where batch_id = p_batch_id
289 and nvl(error_flag,'N') = 'N');
290
291 delete from oe_lines_interface
292 where (order_source_id, orig_sys_document_ref) IN
293 (select order_source_id, orig_sys_document_ref
294 from oe_headers_iface_all
295 where batch_id = p_batch_id
296 and nvl(error_flag,'N') = 'N');
297
298 delete from oe_headers_iface_all
299 where batch_id = p_batch_id
300 and nvl(error_flag,'N') = 'N';
301 end if;
302
303 -- commit after every batch is processed
304 COMMIT;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 OE_BULK_MSG_PUB.Add_Exc_Msg
309 ( G_PKG_NAME
310 , 'Post_Process'
311 );
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 END Post_Process;
314
315
316 ---------------------------------------------------------------------
317 -- PUBLIC PROCEDURES/FUNCTIONS
318 ---------------------------------------------------------------------
319
320 -----------------------------------------------------------
321 -- Procedure: Order_Import_Conc_Pgm
322 -----------------------------------------------------------
323 PROCEDURE ORDER_IMPORT_CONC_PGM(
324 errbuf OUT NOCOPY VARCHAR2
325 ,retcode OUT NOCOPY NUMBER
326 ,p_operating_unit IN NUMBER
327 ,p_order_source_id IN NUMBER
328 ,p_orig_sys_document_ref IN VARCHAR2
329 ,p_validate_only IN VARCHAR2 DEFAULT 'N'
330 ,p_validate_desc_flex IN VARCHAR2 DEFAULT 'Y'
331 ,p_defaulting_mode IN VARCHAR2 DEFAULT 'N'
332 ,p_debug_level IN NUMBER DEFAULT NULL
333 ,p_num_instances IN NUMBER DEFAULT 1
334 ,p_batch_size IN NUMBER DEFAULT 10000
335 ,p_rtrim_data IN VARCHAR2 DEFAULT 'N'
336 ,p_process_recs_with_no_org IN VARCHAR2 DEFAULT 'Y'
337 ,p_process_tax IN VARCHAR2 DEFAULT 'N'
338 , p_process_configurations IN VARCHAR2 DEFAULT 'N'
339 , p_dummy IN VARCHAR2 DEFAULT NULL
340 , p_validate_configurations IN VARCHAR2 DEFAULT 'Y'
341 , p_schedule_configurations IN VARCHAR2 DEFAULT 'N'
342 )
343 IS
344 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
345 l_change_sequence VARCHAR2(50);
346 l_order_source_id NUMBER;
347 l_orig_sys_document_ref VARCHAR2(50);
348 l_orig_sys_line_ref VARCHAR2(50);
349 l_request_id NUMBER;
350 l_new_request_id NUMBER;
351 b_org_id NUMBER;
352 l_count_msgs NUMBER;
353 l_count NUMBER;
354 l_message_text VARCHAR2(2000) := '';
355
356 l_msg_count NUMBER;
357 l_msg_data VARCHAR2(2000) := '';
358 l_return_status VARCHAR2(1) := '';
359
360 l_count_batch NUMBER := 0;
361 l_count_batch_warning NUMBER := 0;
362 l_count_batch_success NUMBER := 0;
363 l_count_batch_failure NUMBER := 0;
364
365 l_filename VARCHAR2(100);
366 l_database VARCHAR2(100);
367
368 l_api_name CONSTANT VARCHAR2(30):= 'Order_Import_Main';
369 l_row_count NUMBER;
370 l_num_instances NUMBER := p_num_instances;
371
372 l_closed_flag VARCHAR2(1);
373
374 -- For the Parent Wait for child to finish
375 l_req_data VARCHAR2(10);
376 l_req_data_counter NUMBER;
377
378 l_default_org_id NUMBER;
379
380 -----------------------------------------------------------
381 -- Batches cursor
382 -----------------------------------------------------------
383 CURSOR l_batch_cursor IS
384 SELECT DISTINCT batch_id,org_id
385 FROM oe_headers_iface_all
386 WHERE request_id = l_request_id;
387
388 --ER 9060917
389 ------------------------------------------------------------
390 -- Paritially processed orders
391 ------------------------------------------------------------
392
393 CURSOR l_partial_orders IS
394 SELECT orig_sys_document_ref
395 FROM oe_headers_iface_all a
396 WHERE request_id = l_request_id
397 AND nvl(error_flag, 'N') = 'N'
398 AND EXISTS
399 (SELECT orig_sys_line_ref
400 FROM oe_lines_iface_all b
401 WHERE b.orig_sys_document_ref = a.orig_sys_document_ref
402 AND b.order_source_id = a.order_source_id
403 AND nvl(error_flag, 'N') = 'Y')
404 ORDER BY a.order_source_id, a.orig_sys_document_ref ;
405
406 --End of ER 9060917
407
408 ------------------------------------------------------------
409 -- Messages cursor
410 ------------------------------------------------------------
411 CURSOR l_msg_cursor IS
412 -- Oracle IT bug 01/06/2000 1572080
413 SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
414 USE_NL (a b) */
415 a.order_source_id
416 , a.original_sys_document_ref
417 , a.change_sequence
418 , a.original_sys_document_line_ref
419 , nvl(a.message_text, b.message_text)
420 FROM oe_processing_msgs a, oe_processing_msgs_tl b
421 WHERE a.request_id = l_request_id
422 AND a.transaction_id = b.transaction_id (+)
423 AND b.language (+) = oe_globals.g_lang
424 ORDER BY a.order_source_id, a.original_sys_document_ref, a.change_sequence;
425
426 l_order_rec Order_Rec_Type;
427 l_instance_tbl Instance_Tbl_Type;
428 l_batch_tbl Batch_Tbl_Type;
429
430 -----------------------------------------------------------
431 -- Lines per order cursor
432 -----------------------------------------------------------
433 -- Outer join to lines interface table so that the cursor also
434 -- selects headers with no order lines.
435 -- Headers with no order lines will be counted as 1 line
436 -- (num_lines = 1) for assigning batches.
437
438 CURSOR c_lines_per_order IS
439 SELECT /* MOAC_SQL_CHANGE */ h.order_source_id
440 , h.orig_sys_document_ref
441 , count(*) num_lines
442 , NULL request_id
443 , NULL batch_id
444 , h.org_id
445 FROM oe_headers_interface h, oe_lines_iface_all l, --bug 4685432
446 oe_sys_parameters_all sys
447 WHERE h.order_source_id = nvl(p_order_source_id,h.order_source_id)
448 AND h.orig_sys_document_ref = nvl(p_orig_sys_document_ref,h.orig_sys_document_ref)
449 AND h.org_id = l.org_id
450 AND sys.org_id(+) = h.org_id --bug 4685432, 5209313
451 AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
452 AND nvl(sys.parameter_value,'N') = 'N'
453 -- This phase of BULK supports only CREATION of complete orders
454 AND nvl(h.operation_code,'CREATE') IN ('INSERT','CREATE')
455 AND h.request_id IS NULL
456 AND h.order_source_id <> 20
457 AND nvl(h.error_flag,'N') = 'N'
458 AND nvl(h.ineligible_for_hvop, 'N')='N'
459 AND nvl(h.ready_flag,'Y') = 'Y'
460 AND nvl(h.rejected_flag,'N') = 'N'
461 AND nvl(h.force_apply_flag,'Y') = 'Y'
462 AND h.orig_sys_document_ref = l.orig_sys_document_ref(+)
463 AND h.order_source_id = l.order_source_id(+)
464 AND h.org_id = l.org_id(+)
465 AND nvl(h.change_sequence, FND_API.G_MISS_CHAR)
466 = nvl(l.change_sequence(+), FND_API.G_MISS_CHAR)
467 AND nvl(l.error_flag(+),'N') = 'N'
468 AND nvl(l.rejected_flag(+),'N') = 'N'
469 AND nvl (h.payment_type_code, ' ') <> 'CREDIT_CARD'
470 AND nvl(h.order_source_id,0) <> 10
471 AND h.customer_preference_set_code IS NULL
472 AND h.return_reason_code IS NULL
473 AND nvl(h.closed_flag ,'N') = 'N'
474 AND nvl(l.source_type_code,'INTERNAL') = 'INTERNAL'
475 AND l.arrival_set_name IS NULL
476 AND l.ship_set_name IS NULL
477 AND l.commitment_id IS NULL
478 AND l.return_reason_code IS NULL
479 AND l.override_atp_date_code IS NULL
480 AND h.org_id = nvl(p_operating_unit,h.org_id)
481 -- Do not process orders with manual sales credits, manual
482 -- pricing attributes or with action requests other than booking
483 AND NOT EXISTS (SELECT 'Y'
484 FROM oe_credits_iface_all sc
485 WHERE sc.order_source_id = h.order_source_id
486 AND sc.orig_sys_document_ref
487 = h.orig_sys_document_ref
488 AND sc.org_id = h.org_id)
489 AND NOT EXISTS (SELECT 'Y'
490 FROM oe_price_atts_iface_all pa
491 WHERE pa.order_source_id = h.order_source_id
492 AND pa.orig_sys_document_ref
493 = h.orig_sys_document_ref
494 AND h.org_id = pa.org_id)
495 AND NOT EXISTS (SELECT 'Y'
496 FROM oe_actions_iface_all a
497 WHERE a.order_source_id = h.order_source_id
498 AND a.orig_sys_document_ref
499 = h.orig_sys_document_ref
500 AND operation_code <> 'BOOK_ORDER'
501 AND h.org_id = a.org_id)
502 GROUP BY h.org_id, h.order_source_id, h.orig_sys_document_ref, h.change_sequence
503 ORDER BY h.org_id, h.order_source_id, h.orig_sys_document_ref ;
504
505 ----------------------------------------------------------
506 -- second lines cursor to get the orders with null org
507 ----------------------------------------------------------
508 CURSOR c_lines_per_order_2 IS
509 SELECT /* MOAC_SQL_CHANGE */ order_source_id,
510 orig_sys_document_ref,
511 num_lines,
512 request_id,
513 batch_id,
514 org_id
515 FROM (
516 SELECT h.order_source_id
517 , h.orig_sys_document_ref
518 , count(*) num_lines
519 , NULL request_id
520 , NULL batch_id
521 , h.org_id org_id
522 FROM oe_headers_interface h, oe_lines_iface_all l, --bug 4685432
523 oe_sys_parameters_all sys
524 WHERE h.order_source_id = nvl(p_order_source_id,h.order_source_id)
525 AND h.orig_sys_document_ref = nvl(p_orig_sys_document_ref,h.orig_sys_document_ref)
526 AND sys.org_id(+) = h.org_id --bug 4685432, 5209313
527 AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
528 AND nvl(sys.parameter_value,'N') = 'N'
529 -- This phase of BULK supports only CREATION of complete orders
530 AND nvl(h.operation_code,'CREATE') IN ('INSERT','CREATE')
531 AND h.request_id IS NULL
532 AND h.order_source_id <> 20
533 AND nvl(h.error_flag,'N') = 'N'
534 AND nvl(h.ready_flag,'Y') = 'Y'
535 AND nvl(h.rejected_flag,'N') = 'N'
536 AND nvl(h.force_apply_flag,'Y') = 'Y'
537 AND h.orig_sys_document_ref = l.orig_sys_document_ref(+)
538 AND h.order_source_id = l.order_source_id(+)
539 AND h.org_id IS NOT NULL
540 AND h.org_id = l.org_id(+)
541 AND nvl(h.change_sequence, FND_API.G_MISS_CHAR)
542 = nvl(l.change_sequence(+), FND_API.G_MISS_CHAR)
543 AND nvl(l.error_flag(+),'N') = 'N'
544 AND nvl(l.rejected_flag(+),'N') = 'N'
545 AND h.org_id = nvl(p_operating_unit,h.org_id)
546 -- Do not process orders with manual sales credits, manual
547 -- pricing attributes or with action requests other than booking
548 AND NOT EXISTS (SELECT 'Y'
549 FROM oe_credits_iface_all sc
550 WHERE sc.order_source_id = h.order_source_id
551 AND sc.orig_sys_document_ref
552 = h.orig_sys_document_ref
553 AND h.org_id = sc.org_id)
554 AND NOT EXISTS (SELECT 'Y'
555 FROM oe_price_atts_iface_all pa
556 WHERE pa.order_source_id = h.order_source_id
557 AND pa.orig_sys_document_ref
558 = h.orig_sys_document_ref
559 AND h.org_id = pa.org_id)
560 AND NOT EXISTS (SELECT 'Y'
561 FROM oe_actions_iface_all a
562 WHERE a.order_source_id = h.order_source_id
563 AND a.orig_sys_document_ref
564 = h.orig_sys_document_ref
565 AND h.org_id = a.org_id
566 AND operation_code <> 'BOOK_ORDER')
567 GROUP BY h.org_id, h.order_source_id, h.orig_sys_document_ref, h.change_sequence
568 UNION
569 SELECT h.order_source_id
570 , h.orig_sys_document_ref
571 , count(*) num_lines
572 , NULL request_id
573 , NULL batch_id
574 , l_default_org_id org_id
575 FROM oe_headers_iface_all h, oe_lines_iface_all l, --bug 4685432
576 oe_sys_parameters_all sys
577 WHERE h.order_source_id = nvl(p_order_source_id,h.order_source_id)
578 AND h.orig_sys_document_ref = nvl(p_orig_sys_document_ref,h.orig_sys_document_ref)
579 AND nvl(sys.org_id,l_default_org_id) = l_default_org_id --bug 4685432, 5209313
580 AND sys.org_id(+) = h.org_id
581 AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
582 AND nvl(sys.parameter_value,'N') = 'N'
583 -- This phase of BULK supports only CREATION of complete orders
584 AND nvl(h.operation_code,'CREATE') IN ('INSERT','CREATE')
585 AND h.request_id IS NULL
586 AND h.order_source_id <> 20
587 AND nvl(h.error_flag,'N') = 'N'
588 AND nvl(h.ready_flag,'Y') = 'Y'
589 AND nvl(h.rejected_flag,'N') = 'N'
590 AND nvl(h.force_apply_flag,'Y') = 'Y'
591 AND h.orig_sys_document_ref = l.orig_sys_document_ref(+)
592 AND h.order_source_id = l.order_source_id(+)
593 AND nvl(h.org_id, l_default_org_id) = nvl(l.org_id(+), l_default_org_id)
594 AND nvl(h.change_sequence, FND_API.G_MISS_CHAR)
595 = nvl(l.change_sequence(+), FND_API.G_MISS_CHAR)
596 AND nvl(l.error_flag(+),'N') = 'N'
597 AND nvl(l.rejected_flag(+),'N') = 'N'
598 AND (h.org_id is NULL AND
599 l_default_org_id = nvl(p_operating_unit,l_default_org_id))
600 -- Do not process orders with manual sales credits, manual
601 -- pricing attributes or with action requests other than booking
602 AND NOT EXISTS (SELECT 'Y'
603 FROM oe_credits_iface_all sc
604 WHERE sc.order_source_id = h.order_source_id
605 AND sc.orig_sys_document_ref
606 = h.orig_sys_document_ref
607 AND NVL(h.org_id, l_default_org_id) =
608 NVL(sc.org_id, l_default_org_id))
609 AND NOT EXISTS (SELECT 'Y'
610 FROM oe_price_atts_iface_all pa
611 WHERE pa.order_source_id = h.order_source_id
612 AND pa.orig_sys_document_ref
613 = h.orig_sys_document_ref
614 AND NVL(h.org_id, l_default_org_id) =
615 NVL(pa.org_id, l_default_org_id))
616 AND NOT EXISTS (SELECT 'Y'
617 FROM oe_actions_iface_all a
618 WHERE a.order_source_id = h.order_source_id
619 AND a.orig_sys_document_ref
620 = h.orig_sys_document_ref
621 AND NVL(h.org_id, l_default_org_id) =
622 NVL(a.org_id, l_default_org_id)
623 AND operation_code <> 'BOOK_ORDER')
624 GROUP BY h.org_id, h.order_source_id, h.orig_sys_document_ref, h.change_sequence)
625 ORDER BY org_id, order_source_id, orig_sys_document_ref ;
626
627 -- Start bug 4685432
628 -------------------------------------
629 -- Get Customer Acceptance enabled OU
630 -------------------------------------
631 CURSOR ca_enabled_cur IS
632 SELECT count(org_id), org_id
633 FROM oe_sys_parameters_syn
634 WHERE parameter_code = 'ENABLE_FULFILLMENT_ACCEPTANCE'
635 AND nvl(parameter_value,'N') = 'Y'
636 AND ((p_operating_unit IS NULL) OR (org_id = p_operating_unit))
637 GROUP BY org_id
638 ORDER BY org_id ;
639 -- End bug 4685432
640
641 -----------------------------------------------------------
642 -- Headers Cursor
643 -----------------------------------------------------------
644 CURSOR c_headers(p_request_id NUMBER) IS
645 SELECT order_source_id, orig_sys_document_ref,org_id,request_id
646 FROM oe_headers_iface_all
647 WHERE request_id = p_request_id
648 ORDER BY order_source_id, orig_sys_document_ref, change_sequence;
649
650 minimum number;
651 l_order_count number;
652 l_index number;
653 l_instance_index number;
654 l_batch_index number;
655
656 I number;
657 l_batch_id number;
658 l_num_lines number;
659 l_batch_found BOOLEAN;
660 l_max_batches NUMBER := 1000;
661 v_end number;
662 v_start number;
663 l_exists varchar2(1);
664 l_start_total_time number;
665 l_end_total_time number;
666 l_batch_orders number;
667 l_entered_orders number;
668 l_booked_orders number;
669 l_error_orders number;
670 l_oper_unit_name varchar2(240) := NULL; --bug 4685432
671 BEGIN
672
673 -----------------------------------------------------------
674 -- Log Output file
675 -----------------------------------------------------------
676
677 fnd_file.put_line(FND_FILE.OUTPUT, '');
678 fnd_file.put_line(FND_FILE.OUTPUT, 'BULK Order Import Concurrent Program');
679 fnd_file.put_line(FND_FILE.OUTPUT, '');
680 fnd_file.put_line(FND_FILE.OUTPUT, 'Concurrent Program Parameters');
681 fnd_file.put_line(FND_FILE.OUTPUT, 'Validate Only: '|| p_validate_only);
682 fnd_file.put_line(FND_FILE.OUTPUT, 'Validate Desc Flex: '|| p_validate_desc_flex);
683 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Source: '|| p_order_source_id);
684 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Ref: '|| p_orig_sys_document_ref);
685 fnd_file.put_line(FND_FILE.OUTPUT, 'Number of Instances: '|| p_num_instances);
686 fnd_file.put_line(FND_FILE.OUTPUT, 'Defaulting Mode: '|| p_defaulting_mode);
687 fnd_file.put_line(FND_FILE.OUTPUT, 'Process Recs With No Org: '|| p_process_recs_with_no_org );
688 fnd_file.put_line(FND_FILE.OUTPUT, '');
689 fnd_file.put_line(FND_FILE.OUTPUT,'Process Configurations '||P_PROCESS_CONFIGURATIONS);
690 fnd_file.put_line(FND_FILE.OUTPUT, ' Process Tax ' || p_process_tax );
691
692
693 -----------------------------------------------------------
694 -- Setting Debug Mode and File
695 -----------------------------------------------------------
696
697 FND_FILE.Put_Line(FND_FILE.OUTPUT,'Debug Level: '||nvl(p_debug_level,0));
698
699 IF nvl(p_debug_level, 0) > 0 THEN
700 FND_PROFILE.PUT('ONT_DEBUG_LEVEL',p_debug_level);
701 --moved this stmt in if loop for bug 3747791
702 l_filename := oe_debug_pub.set_debug_mode ('CONC');
703 END IF;
704
705 -- l_filename := oe_debug_pub.set_debug_mode ('CONC');
706
707 -----------------------------------------------------------
708 -- Get Concurrent Request Id
709 -----------------------------------------------------------
710
711 --Bug# 12333065 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
712 l_request_id := FND_GLOBAL.CONC_REQUEST_ID; --Bug# 12333065
713
714 fnd_file.put_line(FND_FILE.OUTPUT, 'Request Id: '|| to_char(l_request_id));
715
716
717 If p_operating_unit IS NOT NULL Then
718 MO_GLOBAL.set_policy_context('S',p_operating_unit);
719 End If;
720 l_default_org_id := MO_UTILS.get_default_org_id;
721
722 If p_debug_level > 0 Then
723 oe_debug_pub.add('Default Org Id '||l_default_org_id);
724 End If;
725
726 fnd_file.put_line(FND_FILE.OUTPUT, 'Org Id: '|| to_char(p_operating_unit));
727 fnd_file.put_line(FND_FILE.OUTPUT, '');
728
729 l_req_data := fnd_conc_global.request_data;
730 if (l_req_data is not null) then
731 l_req_data_counter := to_number(l_req_data);
732 l_req_data_counter := l_req_data_counter + 1;
733 else
734 l_req_data_counter := 1;
735 end if;
736
737 --Start bug 4685432
738 ---------------------------------------------------------------
739 -- Populate Customer Acceptance enabled informations
740 -- If Customer Acceptance is enabled then HVOP is not supported
741 ---------------------------------------------------------------
742 FOR ca_enabled_rec IN ca_enabled_cur LOOP
743 BEGIN
744 SELECT name
745 INTO l_oper_unit_name
746 FROM hr_operating_units
747 WHERE organization_id = ca_enabled_rec.org_id ;
748
749 FND_MESSAGE.SET_NAME('ONT','ONT_BULK_NOT_SUPP_ACCEPTANCE');
750 FND_MESSAGE.SET_TOKEN('OU','''' || l_oper_unit_name || '''');
751 fnd_file.put_line(FND_FILE.OUTPUT, FND_MESSAGE.GET);
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 NULL ;
756 END ;
757 END LOOP;
758
759 if p_debug_level >0 then
760 oe_debug_pub.add('process recs with no org '||p_process_recs_with_no_org);
761 oe_debug_pub.add('G_CONFIG_EFFECT_DATE'||OE_BULK_ORDER_PVT.G_CONFIG_EFFECT_DATE);
762 end if;
763 --End bug 4685432
764
765 -----------------------------------------------------------
766
767 -----------------------------------------------------------
768 -- If number of instances > 0, submit order import child requests
769 -- and assign request_id/batch_id to all orders in this BULK import run.
770 -----------------------------------------------------------
771 IF l_num_instances > 0 THEN
772
773 --------------------------------------------------------------
774 -- IMPORTANT: This check is necessary as an EXIT criteria.
775 -- Parent requests are automatically re-submitted by the concurrent
776 -- manager until there are no more child requests submitted.
777 -- Without this exit criteria, an infinite number of child
778 -- requests will be spawned.
779 --------------------------------------------------------------
780
781 BEGIN
782
783 If p_process_recs_with_no_org = 'Y' Then
784
785 oe_debug_pub.add('p_process_recs_with_no_org =Y',1);
786
787 SELECT count(orig_sys_document_ref)
788 INTO l_row_count
789 FROM(
790 SELECT /* MOAC_SQL_CHANGE */
791 h.orig_sys_document_ref orig_sys_document_ref
792 FROM oe_headers_interface h, oe_order_sources os, --bug 4685432
793 oe_sys_parameters_all sys
794 WHERE request_id IS NULL
795 AND sys.org_id(+) = h.org_id --bug 4685432, 5209313
796 AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
797 AND nvl(sys.parameter_value,'N') = 'N'
798 AND os.order_source_id = nvl(p_order_source_id,os.order_source_id)
799 AND os.order_source_id <> 20
800 AND h.order_source_id = os.order_source_id
801 AND orig_sys_document_ref = nvl(p_orig_sys_document_ref,orig_sys_document_ref)
802 AND nvl(operation_code,'CREATE') IN ('INSERT','CREATE')
803 AND nvl(error_flag,'N') = 'N'
804 AND nvl(ready_flag,'Y') = 'Y'
805 AND nvl(rejected_flag,'N') = 'N'
806 AND nvl(force_apply_flag,'Y') = 'Y'
807 AND nvl(Ineligible_for_hvop, 'N')='N'
808 AND nvl (h.payment_type_code, ' ') <> 'CREDIT_CARD'
809 AND nvl(h.order_source_id,0) <> 10
810 AND h.customer_preference_set_code IS NULL
811 AND h.return_reason_code IS NULL
812 AND nvl(h.closed_flag ,'N') = 'N'
813 AND h.org_id = nvl(p_operating_unit,h.org_id)
814 AND NOT EXISTS
815 ( SELECT orig_sys_line_ref
816 FROM oe_lines_iface_all l
817 WHERE h.orig_sys_document_ref = l.orig_sys_document_ref
818 AND h.order_source_id = l.order_source_id
819 AND (nvl(l.source_type_code,'INTERNAL') = 'EXTERNAL'
820 OR l.arrival_set_name IS NOT NULL
821 OR l.ship_set_name IS NOT NULL
822 OR l.commitment_id IS NOT NULL
823 OR l.return_reason_code IS NOT NULL
824 OR l.override_atp_date_code IS NOT NULL OR
825 (l.item_type_code IN ('MODEL', 'CLASS', 'OPTION') AND
826 l.top_model_line_ref is not null AND
827 p_process_configurations = 'N' )))
828
829 AND NOT EXISTS (SELECT 'Y'
830 FROM oe_credits_iface_all sc
831 WHERE sc.order_source_id = h.order_source_id
832 AND sc.orig_sys_document_ref
833 = h.orig_sys_document_ref
834 AND h.org_id = sc.org_id)
835 AND NOT EXISTS (SELECT 'Y'
836 FROM oe_price_atts_iface_all pa
837 WHERE pa.order_source_id = h.order_source_id
838 AND pa.orig_sys_document_ref
839 = h.orig_sys_document_ref
840 AND h.org_id = pa.org_id)
841 AND NOT EXISTS (SELECT 'Y'
842 FROM oe_actions_iface_all a
843 WHERE a.order_source_id = h.order_source_id
844 AND a.orig_sys_document_ref
845 = h.orig_sys_document_ref
846 AND h.org_id = a.org_id
847 AND operation_code <> 'BOOK_ORDER')
848 UNION
849 SELECT h.orig_sys_document_ref orig_sys_document_ref
850 FROM oe_headers_iface_all h, oe_order_sources os, --bug 4685432
851 oe_sys_parameters_all sys
852 WHERE request_id IS NULL
853 AND nvl(sys.org_id,l_default_org_id) = l_default_org_id --bug 4685432, 5209313
854 AND sys.org_id(+) = h.org_id
855 AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
856 AND nvl(sys.parameter_value,'N') = 'N'
857 AND os.order_source_id = nvl(p_order_source_id,os.order_source_id)
858 AND os.order_source_id <> 20
859 AND h.order_source_id = os.order_source_id
860 AND orig_sys_document_ref = nvl(p_orig_sys_document_ref,orig_sys_document_ref)
861 AND nvl(operation_code,'CREATE') IN ('INSERT','CREATE')
862 AND nvl(error_flag,'N') = 'N'
863 AND nvl(ready_flag,'Y') = 'Y'
864 AND nvl(rejected_flag,'N') = 'N'
865 AND nvl(force_apply_flag,'Y') = 'Y'
866 AND (h.org_id is NULL AND
867 l_default_org_id = nvl(p_operating_unit,l_default_org_id))
868 AND NOT EXISTS (SELECT 'Y'
869 FROM oe_credits_iface_all sc
870 WHERE sc.order_source_id = h.order_source_id
871 AND sc.orig_sys_document_ref
872 = h.orig_sys_document_ref
873 AND NVL(h.org_id, l_default_org_id) =
874 NVL(sc.org_id, l_default_org_id))
875 AND NOT EXISTS (SELECT 'Y'
876 FROM oe_price_atts_iface_all pa
877 WHERE pa.order_source_id = h.order_source_id
878 AND pa.orig_sys_document_ref
879 = h.orig_sys_document_ref
880 AND NVL(h.org_id, l_default_org_id) =
881 NVL(pa.org_id, l_default_org_id))
882 AND NOT EXISTS (SELECT 'Y'
883 FROM oe_actions_iface_all a
884 WHERE a.order_source_id = h.order_source_id
885 AND a.orig_sys_document_ref
886 = h.orig_sys_document_ref
887 AND NVL(h.org_id, l_default_org_id) =
888 NVL(a.org_id, l_default_org_id)
889 AND operation_code <> 'BOOK_ORDER'));
890 if p_debug_level > 0 then
891 oe_debug_pub.add('the number of orders to process is '||l_row_count);
892 end if;
893 Else
894
895 -- No need to select NULL org_id records.
896 oe_debug_pub.add('p_process_recs_with_no_org =N',1);
897 SELECT /* MOAC_SQL_CHANGE */ count(*)
898 INTO l_row_count
899 FROM oe_headers_interface h, oe_order_sources os, --bug 4685432
900 oe_sys_parameters_all sys
901 WHERE request_id IS NULL
902 AND sys.org_id(+) = h.org_id --bug 4685432, 5209313
903 AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
904 AND nvl(sys.parameter_value,'N') = 'N'
905 AND os.order_source_id = nvl(p_order_source_id,os.order_source_id)
906 AND os.order_source_id <> 20
907 AND h.order_source_id = os.order_source_id
908 AND orig_sys_document_ref = nvl(p_orig_sys_document_ref,orig_sys_document_ref)
909 AND nvl(operation_code,'CREATE') IN ('INSERT','CREATE')
910 AND nvl(error_flag,'N') = 'N'
911 AND nvl(ready_flag,'Y') = 'Y'
912 AND nvl(rejected_flag,'N') = 'N'
913 AND nvl(force_apply_flag,'Y') = 'Y'
914 AND h.org_id = nvl(p_operating_unit,h.org_id)
915 AND NOT EXISTS (SELECT 'Y'
916 FROM oe_credits_iface_all sc
917 WHERE sc.order_source_id = h.order_source_id
918 AND sc.orig_sys_document_ref
919 = h.orig_sys_document_ref
920 AND sc.org_id = h.org_id)
921 AND NOT EXISTS (SELECT 'Y'
922 FROM oe_price_atts_iface_all pa
923 WHERE pa.order_source_id = h.order_source_id
924 AND pa.orig_sys_document_ref
925 = h.orig_sys_document_ref
926 AND pa.org_id = h.org_id )
927 AND NOT EXISTS (SELECT 'Y'
928 FROM oe_actions_iface_all a
929 WHERE a.order_source_id = h.order_source_id
930 AND a.orig_sys_document_ref
931 = h.orig_sys_document_ref
932 AND a.org_id = h.org_id
933 AND operation_code <> 'BOOK_ORDER');
934 End If;
935 IF l_row_count = 0 THEN
936 oe_debug_pub.add('No Data found in the IFCAE table');
937 RAISE NO_DATA_FOUND;
938 END IF;
939
940 EXCEPTION
941 WHEN NO_DATA_FOUND THEN
942 fnd_file.put_line(FND_FILE.OUTPUT,'No more orders to process');
943 fnd_file.put_line(FND_FILE.OUTPUT,'Not spawning any child processes');
944 RETURN;
945
946 WHEN OTHERS THEN
947 fnd_file.put_line(FND_FILE.OUTPUT,'SOME OTHER ERROR IN SQL STATEMENT');
948 RETURN;
949 END;
950
951
952
953
954
955 If p_debug_level > 0 Then
956 oe_debug_pub.add('Remaining Header Row Count is '||l_row_count);
957 end if;
958 IF l_num_instances > l_row_count THEN
959 l_num_instances := l_row_count;
960 END IF;
961
962 -----------------------------------------------------------
963 -- Populate l_instance_tbl for l_num_instances
964 -----------------------------------------------------------
965
966 v_start := DBMS_UTILITY.GET_TIME;
967 FOR I IN 1..l_num_instances LOOP
968
969 -- Generate and populate request_id for each instance
970 l_new_request_id := FND_REQUEST.SUBMIT_REQUEST('ONT', 'OEHVIMP',
971 'High Volume Order Import Child Req' || to_char(l_req_data_counter)
972 , NULL, TRUE, p_operating_unit,p_order_source_id,
973 p_orig_sys_document_ref, p_validate_only
974 , p_validate_desc_flex, p_defaulting_mode , p_debug_level
975 , 0,
976 p_batch_size,p_rtrim_data,p_process_recs_with_no_org,p_process_tax,
977 p_process_configurations, p_dummy, p_validate_configurations, p_schedule_configurations);
978 fnd_file.put_line(FND_FILE.OUTPUT, 'Child Request ID: '||l_new_request_id);
979 IF (l_new_request_id = 0) THEN
980 fnd_file.put_line(FND_FILE.OUTPUT,'Error in submitting child request');
981 errbuf := FND_MESSAGE.GET;
982 retcode := 2;
983 RETURN;
984 END IF;
985
986 l_instance_tbl(I).request_id := l_new_request_id; -- I*1000;
987
988 END LOOP;
989
990 v_end := DBMS_UTILITY.GET_TIME;
991 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time for submit REQUESTs :'||to_char((v_end-v_start)/100));
992
993 -----------------------------------------------------------
994 -- BULK Populate Orders Table - l_order_rec
995 -----------------------------------------------------------
996 If p_debug_level > 0 Then
997 oe_debug_pub.add('p_operating_unit = '||p_operating_unit);
998 oe_debug_pub.add('p_process_recs_with_no_org = '||p_process_recs_with_no_org);
999 oe_debug_pub.add('l_default_org_id = '||l_default_org_id);
1000 end if;
1001 If p_process_recs_with_no_org = 'N' Then
1002 v_start := DBMS_UTILITY.GET_TIME;
1003 OPEN c_lines_per_order;
1004 FETCH c_lines_per_order BULK COLLECT
1005 INTO l_order_rec.order_source_id
1006 , l_order_rec.orig_sys_document_ref
1007 , l_order_rec.num_lines
1008 , l_order_rec.request_id
1009 , l_order_rec.batch_id
1010 , l_order_rec.org_id
1011 ;
1012 CLOSE c_lines_per_order;
1013 v_end := DBMS_UTILITY.GET_TIME;
1014 FND_FILE.PUT_LINE(FND_FILE.LOG,'TIME FOR BULK COLLECT:'||TO_CHAR ( ( V_END-V_START ) /100 ) ) ;
1015 Elsif p_process_recs_with_no_org = 'Y' Then
1016 v_start := DBMS_UTILITY.GET_TIME;
1017 If p_debug_level > 0 Then
1018 oe_debug_pub.add('here1');
1019 End If;
1020 OPEN c_lines_per_order_2;
1021 FETCH c_lines_per_order_2 BULK COLLECT
1022 INTO l_order_rec.order_source_id
1023 , l_order_rec.orig_sys_document_ref
1024 , l_order_rec.num_lines
1025 , l_order_rec.request_id
1026 , l_order_rec.batch_id
1027 , l_order_rec.org_id
1028 ;
1029 CLOSE c_lines_per_order_2;
1030 v_end := DBMS_UTILITY.GET_TIME;
1031 FND_FILE.PUT_LINE(FND_FILE.LOG,'TIME FOR BULK COLLECT:'||TO_CHAR ( ( V_END-V_START ) /100 ) ) ;
1032 End If;
1033 -----------------------------------------------------------
1034 -- Assign request_id (instance) AND batch_id to the orders
1035 -----------------------------------------------------------
1036
1037 l_order_count := l_order_rec.orig_sys_document_ref.count;
1038 If p_debug_level > 0 Then
1039 oe_debug_pub.add('Order Count = '||l_order_count);
1040 End If;
1041 minimum := 0;
1042 v_start := DBMS_UTILITY.GET_TIME;
1043 l_index := 1;
1044
1045 <<BEGINNING_OF_LOOP>>
1046 WHILE l_index <= l_order_count LOOP
1047
1048 l_num_lines := l_order_rec.num_lines(l_index);
1049
1050
1051 --------------------------------------------------------------
1052 -- Print error in the output if order size > batch size
1053 --------------------------------------------------------------
1054
1055 IF l_num_lines > p_batch_size THEN
1056
1057
1058 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_order_rec.order_source_id(l_index))
1059 ||'/'||l_order_rec.orig_sys_document_ref(l_index));
1060 fnd_file.put_line(FND_FILE.OUTPUT,'This order cannot be processed in this batch.'||
1061 ' Order Size is > '||to_char(p_batch_size));
1062
1063
1064 -- Assign parent import program's request id. Without this, infinite
1065 -- number of child requests are spawned as this order will continue
1066 -- to exist with no request id assigned.
1067 l_order_rec.request_id(l_index) := l_request_id;
1068
1069 update oe_headers_iface_all
1070 set error_flag='Y' --- did not update ineligible_for_hvop as per the TDD
1071 where order_source_id = l_order_rec.order_source_id(l_index)
1072 and orig_sys_document_ref = l_order_rec.orig_sys_document_ref(l_index)
1073 and nvl(org_id,-99) = nvl(l_order_rec.org_id(l_index),-99);
1074
1075 l_index := l_index + 1;
1076
1077 GOTO BEGINNING_OF_LOOP;
1078
1079 END IF;
1080
1081 --------------------------------------------------------------
1082 -- I. Identify the instance with the least number of total lines
1083 --------------------------------------------------------------
1084
1085 FOR I IN 1..l_num_instances LOOP
1086 IF l_instance_tbl(I).total_lines <= minimum THEN
1087 minimum := l_instance_tbl(I).total_lines;
1088 l_instance_index := I;
1089 END IF;
1090 END LOOP;
1091
1092 -- Assign request_id for this instance to the order
1093 l_order_rec.request_id(l_index)
1094 := l_instance_tbl(l_instance_index).request_id;
1095
1096 -- Update total number of lines for this instance/request.
1097 l_instance_tbl(l_instance_index).total_lines
1098 := minimum + l_num_lines;
1099 minimum := minimum + l_num_lines;
1100
1101 --------------------------------------------------------------
1102 -- II. Identify the batch within this request where this order
1103 -- can be accommodated.
1104 --------------------------------------------------------------
1105
1106 l_batch_index := l_instance_index * l_max_batches;
1107
1108 -- Search if a batch exists that can accommodate this order
1109 -- and total number of lines (incl. this order) will be
1110 -- <= p_batch_size
1111 l_batch_found := FALSE;
1112
1113 -- Added logic to check if the org_id on the batch is same as the one on
1114 -- l_order_rec. This is to make sure that within a batch, there are records
1115 -- for only one org_id.
1116
1117 WHILE l_batch_tbl.EXISTS(l_batch_index) LOOP
1118
1119 IF l_batch_tbl(l_batch_index).total_lines + l_num_lines <= p_batch_size
1120 AND l_order_rec.org_id(l_index) = l_batch_tbl(l_batch_index).org_id
1121 THEN
1122 -- If batch exists, assign batch number to this order
1123 If p_debug_level > 0 Then
1124 oe_debug_pub.add(' Found the empty batch'||l_batch_tbl(l_batch_index).batch_id);
1125 END IF;
1126 l_batch_tbl(l_batch_index).total_lines :=
1127 l_batch_tbl(l_batch_index).total_lines + l_num_lines;
1128 l_order_rec.batch_id(l_index) := l_batch_tbl(l_batch_index).batch_id;
1129 l_batch_found := TRUE;
1130 EXIT;
1131 END IF;
1132 l_batch_index := l_batch_index + 1;
1133 END LOOP;
1134
1135 -- If batch does not exist, create a new batch.
1136 -- Assign new batch to this order and update number of batches
1137 -- for this instance.
1138
1139 IF NOT l_batch_found THEN
1140 -- Generate a new batch_id
1141 If p_debug_level > 0 Then
1142 oe_debug_pub.add('Did not find empty batch so creating a new one');
1143 oe_debug_pub.add('Org_id for this batch is '||l_order_rec.org_id(l_index));
1144 end if;
1145 SELECT oe_batch_id_s.nextval
1146 INTO l_batch_id FROM DUAL;
1147 l_batch_tbl(l_batch_index).batch_id := l_batch_id;
1148 l_order_rec.batch_id(l_index) := l_batch_id;
1149 l_batch_tbl(l_batch_index).total_lines := l_num_lines;
1150 l_batch_tbl(l_batch_index).org_id := l_order_rec.org_id(l_index);
1151 END IF;
1152
1153 l_index := l_index + 1;
1154
1155 END LOOP; -- End of loop over Orders table - l_order_rec
1156 v_end := DBMS_UTILITY.GET_TIME;
1157 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time to ASSIGN IDs :'||to_char((v_end-v_start)/100));
1158
1159
1160 --------------------------------------------------------------
1161 -- BULK UPDATE Request_ID and Batch_ID on headers iface table
1162 --------------------------------------------------------------
1163
1164 v_start := DBMS_UTILITY.GET_TIME;
1165 FORALL l_index IN 1..l_order_count
1166 UPDATE oe_headers_iface_all
1167 SET request_id = l_order_rec.request_id(l_index)
1168 , batch_id = l_order_rec.batch_id(l_index)
1169 , org_id = l_order_rec.org_id(l_index) -- added for MOAC
1170 WHERE order_source_id = l_order_rec.order_source_id(l_index)
1171 AND orig_sys_document_ref
1172 = l_order_rec.orig_sys_document_ref(l_index)
1173 and nvl(org_id,l_default_org_id) = l_order_rec.org_id(l_index);
1174
1175 -- Sequential assignment of header_ids/line_ids
1176 -- Improves performance in parallel threads by reducing contention
1177 -- on OE tables and WF tables as WF itemkeys are also constructed
1178 -- using these id values.
1179
1180 --------------------------------------------------------------
1181 -- BULK UPDATE org_id all iface tables if processing NULL records
1182 --------------------------------------------------------------
1183 IF p_process_recs_with_no_org = 'Y' Then
1184
1185 -- Update on lines iface is done as a part of request_id update.
1186
1187 -- Update on actions iface all
1188 FORALL l_index IN 1..l_order_count
1189 UPDATE oe_actions_iface_all
1190 SET org_id = l_order_rec.org_id(l_index) -- added for MOAC
1191 WHERE order_source_id = l_order_rec.order_source_id(l_index)
1192 AND orig_sys_document_ref
1193 = l_order_rec.orig_sys_document_ref(l_index)
1194 AND org_id IS NULL
1195 AND l_default_org_id = nvl(p_operating_unit,l_default_org_id);
1196
1197 -- Update on oe_price_adjs_interface
1198 FORALL l_index IN 1..l_order_count
1199 UPDATE OE_PRICE_ADJS_IFACE_ALL
1200 SET org_id = l_order_rec.org_id(l_index) -- added for MOAC
1201 WHERE order_source_id = l_order_rec.order_source_id(l_index)
1202 AND orig_sys_document_ref
1203 = l_order_rec.orig_sys_document_ref(l_index)
1204 AND org_id IS NULL
1205 AND l_default_org_id = nvl(p_operating_unit,l_default_org_id);
1206
1207 END IF;
1208
1209 FOR l_index IN 1..l_num_instances LOOP
1210
1211 -- Bug 3045608
1212 -- High volume import assumes that global headers table
1213 -- populated by oe_bulk_process_header.load_headers will have
1214 -- header records sorted in the ascending order for BOTH header_id
1215 -- and for (order_source_id,orig_sys_ref) combination.
1216 -- So order by order_source_id, orig_sys_ref when assigning
1217 -- header_id from the sequence. If it is not ordered thus, header_ids
1218 -- will be in random order in the global table and workflows/pricing
1219 -- for orders may be skipped.
1220 FOR c IN c_headers(l_instance_tbl(l_index).request_id) LOOP
1221
1222 If p_debug_level > 0 Then
1223 oe_debug_pub.add('Updating Line_Ids for'||c.org_id);
1224 oe_debug_pub.add('Updating Line_Ids for'||c.orig_sys_document_ref);
1225 End If;
1226 UPDATE oe_headers_iface_all
1227 SET header_id = oe_order_headers_s.nextval
1228 WHERE order_source_id = c.order_source_id
1229 AND orig_sys_document_ref = c.orig_sys_document_ref
1230 AND nvl(org_id,-99) = nvl(c.org_id,-99)
1231 AND request_id = c.request_id; -- Changed for MOAC
1232
1233 UPDATE oe_lines_iface_all
1234 SET line_id = oe_order_lines_s.nextval,
1235 request_id = l_instance_tbl(l_index).request_id,
1236 org_id = c.org_id
1237 WHERE order_source_id = c.order_source_id
1238 AND orig_sys_document_ref = c.orig_sys_document_ref
1239 AND nvl(org_id, l_default_org_id) = nvl(c.org_id,l_default_org_id); -- changed for MOAC
1240
1241 END LOOP;
1242
1243 END LOOP;
1244
1245 v_end := DBMS_UTILITY.GET_TIME;
1246 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time to BULK UPDATE:'||to_char((v_end-v_start)/100));
1247
1248 COMMIT;
1249
1250 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1251 request_data => to_char(l_req_data_counter));
1252 errbuf := 'Sub-Request ' || to_char(l_req_data_counter) || 'submitted!';
1253 retcode := 0;
1254
1255 -----------------------------------------------------------
1256 -- If number of instances = 0, this is a child request.
1257 -- For each batch in this request, run BULK order import.
1258 -----------------------------------------------------------
1259 ELSIF l_num_instances = 0 THEN
1260
1261 /* Commenting out as the request ID assignment on these tables can
1262 take a long time. In particular, when there are multiple child requests
1263 working on these updates - the performance could be poor due to disk
1264 contention on these tables.
1265 BULK Processing APIs should use the global request ID (G_REQUEST_ID)
1266 to identify current child request ID. Do not use request_id on the
1267 interface tables.
1268
1269 UPDATE oe_lines_interface
1270 SET request_id = l_request_id
1271 WHERE (order_source_id, orig_sys_document_ref) IN
1272 ( SELECT order_source_id, orig_sys_document_ref
1273 FROM oe_headers_iface_all
1274 WHERE request_id = l_request_id);
1275
1276 COMMIT;
1277
1278 UPDATE oe_price_adjs_interface
1279 SET request_id = l_request_id
1280 WHERE (order_source_id, orig_sys_document_ref) IN
1281 ( SELECT order_source_id, orig_sys_document_ref
1282 FROM oe_headers_iface_all
1283 WHERE request_id = l_request_id);
1284
1285 COMMIT;
1286
1287 UPDATE oe_actions_interface
1288 SET request_id = l_request_id
1289 WHERE (order_source_id, orig_sys_document_ref) IN
1290 ( SELECT order_source_id, orig_sys_document_ref
1291 FROM oe_headers_iface_all
1292 WHERE request_id = l_request_id);
1293
1294 COMMIT;
1295 */
1296
1297 -- Bug 5640601 =>
1298 -- Selecting hsecs from v$times is changed to execute only when debug
1299 -- is enabled, as hsec is used for logging only when debug is enabled.
1300 IF p_debug_level > 0 THEN
1301 SELECT hsecs INTO l_start_total_time from v$timer;
1302 END IF;
1303
1304 Initialize_Request
1305 ( p_request_id => l_request_id
1306 , p_validate_desc_flex => p_validate_desc_flex
1307 , p_rtrim_data => p_rtrim_data -- 3390458
1308 , x_return_status => l_return_status
1309 );
1310
1311 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1312 retcode := 2;
1313 errbuf := 'Please check the log file for error messages';
1314 RETURN;
1315 END IF;
1316
1317 OPEN l_batch_cursor;
1318 LOOP
1319 FETCH l_batch_cursor INTO l_batch_id,b_org_id;
1320 EXIT WHEN l_batch_cursor%NOTFOUND;
1321 If p_debug_level > 0 Then
1322 oe_debug_pub.add(' Inside the Batch cursor for org '||b_org_id);
1323 oe_debug_pub.add(' Inside the Batch cursor for batch '||l_batch_id);
1324 oe_debug_pub.add(' The G_ORG_ID is '||G_ORG_ID);
1325 end if;
1326
1327 l_entered_orders := G_ENTERED_ORDERS;
1328 l_booked_orders := G_BOOKED_ORDERS;
1329 l_error_orders := G_ERROR_ORDERS;
1330
1331 IF G_ORG_ID IS NULL OR
1332 (G_ORG_ID IS NOT NULL AND
1333 G_ORG_ID <> b_org_id) Then
1334 If p_debug_level > 0 Then
1335 oe_debug_pub.add(' Setting the policy context for '||b_org_id);
1336 end if;
1337 MO_GLOBAL.SET_POLICY_CONTEXT('S',b_org_id);
1338 G_ORG_ID := b_org_id;
1339
1340 -- Set all globals that are derived based on OU
1341 Initialize_Batch
1342 ( x_return_status => l_return_status
1343 );
1344
1345 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1346 retcode := 2;
1347 errbuf := 'Please check the log file for error messages';
1348 RETURN;
1349 END IF;
1350 End if;
1351
1352 -----------------------------------------------------------
1353 --Customer Acceptance
1354 --If Customer Acceptance is enabled then HVOP is not supported
1355 -----------------------------------------------------------
1356 --IF OE_SYS_PARAMETERS.VALUE('ENABLE_FULFILLMENT_ACCEPTANCE',G_ORG_ID) = 'Y'
1357 --THEN
1358 -- FND_MESSAGE.SET_NAME('ONT','OE_BULK_NOT_SUPP_ACCEPTANCE');
1359 -- fnd_file.put_line(FND_FILE.OUTPUT, FND_MESSAGE.GET);
1360 -- retcode := 2;
1361 -- RETURN;
1362 -- END IF;
1363
1364 -----------------------------------------------------------
1365 -- Call Process_Batch procedure
1366 -----------------------------------------------------------
1367 If p_debug_level > 0 Then
1368 oe_debug_pub.add(' Calling Process Batch with process Tax :'|| p_process_tax, 1);
1369 end if;
1370
1371 OE_BULK_ORDER_PVT.PROCESS_BATCH (
1372 p_batch_id => l_batch_id,
1373 p_validate_only => p_validate_only,
1374 p_validate_desc_flex => p_validate_desc_flex,
1375 p_defaulting_mode => p_defaulting_mode,
1376 p_process_configurations => p_process_configurations,
1377 p_validate_configurations => p_validate_configurations,
1378 p_schedule_configurations => p_schedule_configurations,
1379 p_init_msg_list => l_init_msg_list,
1380 p_process_tax => p_process_tax,
1381 x_msg_count => l_msg_count,
1382 x_msg_data => l_msg_data,
1383 x_return_status => l_return_status
1384 );
1385
1386 -- Save messages logged during the processing of this batch
1387
1388 OE_BULK_MSG_PUB.Save_Messages(OE_Bulk_Order_PVT.G_REQUEST_ID);
1389
1390 -- Save messages from non-bulk enabled API calls
1391 OE_MSG_PUB.Save_Messages(OE_Bulk_Order_PVT.G_REQUEST_ID);
1392
1393
1394 -- Process_Batch will only return unexp error or success result
1395 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1396
1397 UPDATE oe_headers_iface_all
1398 SET error_flag = 'Y'
1399 WHERE batch_id = l_batch_id;
1400
1401 -- All orders in this batch would have failed as it is an unexp error.
1402 -- Therefore, re-set entered/booked count to values before this
1403 -- batch was processed. And update error count with number of orders
1404 -- assigned to this batch.
1405 G_ENTERED_ORDERS := l_entered_orders;
1406 G_BOOKED_ORDERS := l_booked_orders;
1407
1408 SELECT count(*)
1409 INTO l_batch_orders
1410 FROM oe_headers_iface_all
1411 WHERE batch_id = l_batch_id;
1412
1413 G_ERROR_ORDERS := l_error_orders + l_batch_orders;
1414
1415 l_count_batch_failure := l_count_batch_failure + 1;
1416 fnd_file.put_line(FND_FILE.LOG,'Batch id: '|| to_char(l_batch_id)||
1417 ' Status : Unexpected Error (all orders failed to import)'
1418 );
1419
1420 ELSE
1421 l_count_batch_success := l_count_batch_success + 1;
1422 -- Call order import post processing routine.
1423 -- This will delete all records from interface tables that were
1424 -- successfully imported.
1425 Post_Process(
1426 p_batch_id => l_batch_id,
1427 p_validate_only => p_validate_only,
1428 x_return_status => l_return_status
1429 );
1430 fnd_file.put_line(FND_FILE.LOG,'Batch ID: '|| to_char(l_batch_id)||
1431 ' Status : Processed (orders could have failures due to validation errors)'
1432 );
1433 END IF;
1434
1435 l_count_batch := l_count_batch + 1;
1436 OE_BULK_MSG_PUB.Save_Messages(l_request_id);
1437
1438 -- Save messages from non-bulk enabled API calls
1439 OE_MSG_PUB.Save_Messages(l_request_id);
1440
1441
1442
1443 END LOOP; -- Batch cursor
1444 CLOSE l_batch_cursor;
1445
1446 -- Bug 5640601 =>
1447 -- Selecting hsecs from v$times is changed to execute only when debug
1448 -- is enabled, as hsec is used for logging only when debug is enabled.
1449 IF p_debug_level > 0 THEN
1450 SELECT hsecs INTO l_end_total_time from v$timer;
1451 END IF;
1452
1453 FND_FILE.PUT_LINE(FND_FILE.LOG,'Total time is (sec) '
1454 ||((l_end_total_time-l_start_total_time)/100));
1455
1456 fnd_file.put_line(FND_FILE.OUTPUT,'No. of batches found: ' ||
1457 l_count_batch);
1458 fnd_file.put_line(FND_FILE.OUTPUT,'');
1459
1460
1461 fnd_file.put_line(FND_FILE.OUTPUT,'No. of Orders Processed across All Batches :'||
1462 to_char(G_BOOKED_ORDERS + G_ENTERED_ORDERS + G_ERROR_ORDERS));
1463 fnd_file.put_line(FND_FILE.OUTPUT,'No. of Booked Orders: '||
1464 to_char(G_BOOKED_ORDERS));
1465 fnd_file.put_line(FND_FILE.OUTPUT,'No. of Entered Orders: ' ||
1466 to_char(G_ENTERED_ORDERS));
1467 fnd_file.put_line(FND_FILE.OUTPUT,'No. of Orders Failed: ' ||
1468 to_char(G_ERROR_ORDERS));
1469
1470 -- ER 9060917
1471 IF NVL (Fnd_Profile.Value('ONT_HVOP_DROP_INVALID_LINES'), 'N')='Y' then
1472 fnd_file.put_line(FND_FILE.OUTPUT,'');
1473 fnd_file.put_line(FND_FILE.OUTPUT,'Following orders are processed partially as profile OM: Allow HVOP to drop invalid lines is set');
1474
1475 OPEN l_partial_orders;
1476 LOOP
1477 FETCH l_partial_orders
1478 INTO l_orig_sys_document_ref;
1479 EXIT when l_partial_orders%NOTFOUND;
1480
1481 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_orig_sys_document_ref));
1482
1483 END LOOP;
1484 CLOSE l_partial_orders;
1485 END IF;
1486 -- End of ER 9060917
1487 -----------------------------------------------------------
1488 -- Messages
1489 -----------------------------------------------------------
1490
1491 fnd_file.put_line(FND_FILE.OUTPUT,'');
1492 fnd_file.put_line(FND_FILE.OUTPUT,'Source/Order/Seq/Line Message');
1493 OPEN l_msg_cursor;
1494 LOOP
1495 FETCH l_msg_cursor
1496 INTO l_order_source_id
1497 , l_orig_sys_document_ref
1498 , l_change_sequence
1499 , l_orig_sys_line_ref
1500 , l_message_text;
1501 EXIT WHEN l_msg_cursor%NOTFOUND;
1502
1503 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_order_source_id)
1504 ||'/'||l_orig_sys_document_ref
1505 ||'/'||l_change_sequence
1506 ||'/'||l_orig_sys_line_ref
1507 ||' '||l_message_text);
1508 fnd_file.put_line(FND_FILE.OUTPUT,'');
1509 END LOOP;
1510
1511 END IF; -- End if for l_num_instances
1512
1513 -----------------------------------------------------------
1514 -- End of Order_Import_Conc_Pgm
1515 -----------------------------------------------------------
1516 fnd_file.put_line(FND_FILE.OUTPUT, 'End of BULK Order Import Concurrent Program');
1517
1518 EXCEPTION
1519 WHEN OTHERS THEN
1520 retcode := 2;
1521 fnd_file.put_line(FND_FILE.OUTPUT,'Unexpected error '||substr(sqlerrm,1,200));
1522 fnd_file.put_line(FND_FILE.OUTPUT,'no. of batches imported: '||
1523 l_count_batch_success);
1524 fnd_file.put_line(FND_FILE.OUTPUT,'no. of batches failed: ' ||
1525 l_count_batch_failure);
1526 fnd_file.put_line(FND_FILE.OUTPUT,'');
1527 END ORDER_IMPORT_CONC_PGM;
1528
1529
1530 END OE_BULK_ORDER_IMPORT_PVT;
[Home] [Help]