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