[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_IMPORT_MAIN_PVT
Source
1 PACKAGE BODY OE_ORDER_IMPORT_MAIN_PVT AS
2 /* $Header: OEXVIMNB.pls 120.14 2011/04/07 13:56:18 vmachett ship $ */
3
4 /* ---------------------------------------------------------------
5 -- Start of Comments
6 -- API name Order Import Main
7 -- Type Private
8 -- Function
9 -- Pre-reqs
10 -- Parameters
11 -- Version Current version = 1.0
12 -- Initial version = 1.0
13 -- Notes
14 --
15 -- End of Comments
16 ------------------------------------------------------------------
17 */
18
19 -- Added this to create new conc. program for FND_STAT
20 PROCEDURE ORDER_IMPORT_STATS_CONC_PGM(
21 errbuf OUT NOCOPY VARCHAR2
22
23 ,retcode OUT NOCOPY NUMBER
24
25 )
26 IS
27 --
28 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
29 --
30 BEGIN
31
32 IF l_debug_level > 0 THEN
33 oe_debug_pub.add( 'BEFORE CALLING PERF STATISTICS API' ) ;
34 END IF;
35
36 FND_STATS.Gather_Table_Stats(ownname => 'ONT',
37 tabname => 'OE_HEADERS_IFACE_ALL');
38 FND_STATS.Gather_Table_Stats(ownname => 'ONT',
39 tabname => 'OE_LINES_IFACE_ALL');
40 FND_STATS.Gather_Table_Stats(ownname => 'ONT',
41 tabname => 'OE_ACTIONS_IFACE_ALL');
42 FND_STATS.Gather_Table_Stats(ownname => 'ONT',
43 tabname => 'OE_PRICE_ADJS_IFACE_ALL');
44 FND_STATS.Gather_Table_Stats(ownname => 'ONT',
45 tabname => 'OE_RESERVTNS_IFACE_ALL');
46 FND_STATS.Gather_Table_Stats(ownname => 'ONT',
47 tabname => 'OE_CREDITS_IFACE_ALL');
48 FND_STATS.Gather_Table_Stats(ownname => 'ONT',
49 tabname => 'OE_LOTSERIALS_IFACE_ALL');
50 FND_STATS.Gather_Table_Stats(ownname => 'ONT',
51 tabname => 'OE_PAYMENTS_IFACE_ALL');
52
53 IF l_debug_level > 0 THEN
54 oe_debug_pub.add( 'AFTER CALLING PERF STATISTICS API' ) ;
55 END IF;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 IF l_debug_level > 0 THEN
60 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
61 END IF;
62 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
63 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order_stats');
64 END IF;
65
66 fnd_file.put_line(FND_FILE.OUTPUT,'Unexpected error: ' || sqlerrm);
67
68 END ORDER_IMPORT_STATS_CONC_PGM;
69
70 /* -----------------------------------------------------------
71 Procedure: Order_Import_Conc_Pgm
72 -----------------------------------------------------------
73 */
74 PROCEDURE ORDER_IMPORT_CONC_PGM(
75 errbuf OUT NOCOPY VARCHAR2
76
77 ,retcode OUT NOCOPY NUMBER
78 ,p_operating_unit IN NUMBER
79 ,p_order_source IN VARCHAR2
80 ,p_orig_sys_document_ref IN VARCHAR2
81 ,p_operation_code IN VARCHAR2
82 ,p_validate_only IN VARCHAR2 DEFAULT 'N'
83 ,p_debug_level IN NUMBER
84 ,p_num_instances IN NUMBER DEFAULT 1
85 ,p_sold_to_org_id IN NUMBER
86 ,p_sold_to_org IN VARCHAR2
87 ,p_change_sequence IN VARCHAR2
88 ,p_perf_param IN VARCHAR2
89 ,p_rtrim_data IN Varchar2
90 -- ,p_request_id IN NUMBER
91 ,p_process_orders_with_null_org IN VARCHAR2
92 ,p_default_org_id IN NUMBER
93 ,p_validate_desc_flex in varchar2 default 'Y' --bug4343612
94 )
95 IS
96 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
97 l_validate_only VARCHAR2(1) := p_validate_only;
98 l_order_source VARCHAR2(240) := p_order_source;
99 l_order_source_id NUMBER;
100 l_orig_sys_document_ref VARCHAR2(50) := p_orig_sys_document_ref;
101 l_sold_to_org_id NUMBER := p_sold_to_org_id;
102 l_sold_to_org VARCHAR2(360) := p_sold_to_org;
103 l_change_sequence VARCHAR2(50) := p_change_sequence;
104 l_orig_sys_line_ref VARCHAR2(50);
105 l_operation_code VARCHAR2(30) := p_operation_code;
106 l_request_id NUMBER;
107 l_org_id NUMBER;
108 -- l_debug_level NUMBER := p_debug_level;
109 l_num_instances NUMBER := p_num_instances;
110 l_count_msgs NUMBER;
111 l_message_text VARCHAR2(2000) := '';
112
113 l_msg_count NUMBER;
114 l_msg_data VARCHAR2(2000) := '';
115 l_return_status VARCHAR2(1) := '';
116
117 l_count_header NUMBER := 0;
118 l_count_header_warning NUMBER := 0;
119 l_count_header_success NUMBER := 0;
120 l_count_header_failure NUMBER := 0;
121
122 l_filename VARCHAR2(100);
123 l_database VARCHAR2(100);
124
125 l_api_name CONSTANT VARCHAR2(30):= 'Order_Import_Main';
126 l_row_count NUMBER;
127 new_request_id NUMBER;
128 x_new_request_id NUMBER;
129 x_errbuf VARCHAR2(2000);
130 x_retcode NUMBER;
131 x_order_source VARCHAR2(240);
132 x_orig_sys_document_ref VARCHAR2(50);
133 x_operation_code VARCHAR2(30);
134 x_validate_only VARCHAR2(1);
135 x_debug_level NUMBER;
136 x_num_instances NUMBER;
137 batch_size NUMBER;
138 l_ord_count NUMBER;
139 batch_size_all NUMBER;
140 batch_last NUMBER;
141 l_mod NUMBER;
142 l_updated_docref VARCHAR2(50);
143
144 l_closed_flag VARCHAR2(1);
145 l_customer_key_profile VARCHAR2(3) := 'N';
146
147 -- For the Parent Wait for child to finish
148 l_req_data VARCHAR2(10);
149 l_req_data_counter NUMBER;
150 G_IMPORT_SHIPMENTS VARCHAR2(3);
151
152 l_rtrim_data Varchar2(1) := p_rtrim_data;
153
154 /* -----------------------------------------------------------
155 Order sources cursor
156 -----------------------------------------------------------
157 */
158 CURSOR l_source_cursor IS
159 SELECT order_source_id
160 FROM oe_order_sources s
161 WHERE (
162 (nvl(l_order_source,' ') = ' ') OR
163 (
164 (nvl(l_order_source,' ') <> ' ') AND
165 (enabled_flag = 'Y') AND
166 (nvl(to_char(order_source_id),' ')=nvl(rtrim(l_order_source),' '))
167 )
168 )
169 ORDER BY order_source_id
170 ;
171
172 /* -----------------------------------------------------------
173 Request Headers cursor
174 -----------------------------------------------------------
175 */
176
177 l_rowid varchar2(100);
178 l_looped_flag varchar2(1) := 'N';
179 l_pnt_request_id number;
180
181
182
183
184 CURSOR l_request_cursor IS
185 SELECT order_source_id
186 , orig_sys_document_ref
187 , sold_to_org_id
188 , sold_to_org
189 , change_sequence
190 , nvl(closed_flag, 'N')
191 ,org_id
192 FROM oe_headers_iface_all
193 WHERE request_id = l_request_id
194 AND decode(p_perf_param, 'Y',
195 nvl(error_flag,'N'), ' ')
196 = decode(p_perf_param, 'Y',
197 'N', ' ')
198 AND decode(l_looped_flag, 'Y',
199 l_rowid, ' ')
200 = decode(l_looped_flag, 'Y',
201 rowidtochar(rowid), ' ')
202 ORDER BY org_id,order_source_id, orig_sys_document_ref, change_sequence
203 ;
204
205
206
207
208
209 /* -----------------------------------------------------------
210 Messages cursor
211 -----------------------------------------------------------
212 */
213 CURSOR l_msg_cursor IS
214 -- Oracle IT bug 01/06/2000 1572080
215 /*
216 SELECT order_source_id
217 , original_sys_document_ref
218 , change_sequence
219 , original_sys_document_line_ref
220 , message_text
221 FROM oe_processing_msgs_vl
222 WHERE request_id = l_request_id
223 ORDER BY order_source_id, original_sys_document_ref, change_sequence
224 ;
225
226
227 */
228 -- changed to
229 SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
230 USE_NL (a b) */
231 a.order_source_id
232 , a.original_sys_document_ref
233 , a.change_sequence
234 , a.original_sys_document_line_ref
235 , a.org_id
236 , b.message_text
237 FROM oe_processing_msgs a, oe_processing_msgs_tl b
238 WHERE a.request_id = l_request_id
239 AND a.transaction_id = b.transaction_id
240 AND b.language = oe_globals.g_lang
241 ORDER BY a.order_source_id, a.original_sys_document_ref, a.change_sequence
242 ;
243
244
245 --
246 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
247 --
248 BEGIN
249
250 --MOAC set policy context for single Org
251 IF p_operating_unit IS NOT NULL THEN
252 MO_GLOBAL.set_policy_context('S',p_operating_unit);
253 END IF;
254
255 fnd_profile.get('ONT_IMP_MULTIPLE_SHIPMENTS', G_IMPORT_SHIPMENTS);
256 G_IMPORT_SHIPMENTS := nvl(G_IMPORT_SHIPMENTS, 'NO');
257 IF l_debug_level > 0 THEN
258 oe_debug_pub.add( 'IMP SHIPMENTS PROFILE = '||G_IMPORT_SHIPMENTS ) ;
259 END IF;
260
261 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
262 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
263 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
264 IF l_debug_level > 0 THEN
265 oe_debug_pub.add( 'DERIVED CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
266 END IF;
267 End If;
268
269
270
271 /* -----------------------------------------------------------
272 Log Output file
273 -----------------------------------------------------------
274 */
275 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Import Concurrent Program');
276 fnd_file.put_line(FND_FILE.OUTPUT, '');
277 fnd_file.put_line(FND_FILE.OUTPUT, 'Concurrent Program Parameters');
278 fnd_file.put_line(FND_FILE.OUTPUT, 'Validate Only: '|| l_validate_only);
279 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Source: '|| l_order_source);
280 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Ref: '|| l_orig_sys_document_ref);
281 fnd_file.put_line(FND_FILE.OUTPUT, 'Sold To Org Id: '|| l_sold_to_org_id);
282 fnd_file.put_line(FND_FILE.OUTPUT, 'Sold To Org: '|| l_sold_to_org);
283 fnd_file.put_line(FND_FILE.OUTPUT, 'Change Sequence: '|| l_change_sequence);
284 fnd_file.put_line(FND_FILE.OUTPUT, 'Performance Parameter: '|| p_perf_param);
285 fnd_file.put_line(FND_FILE.OUTPUT, 'Trim Blanks: '|| p_rtrim_data);
286 fnd_file.put_line(FND_FILE.OUTPUT, 'Operation: '|| l_operation_code);
287 fnd_file.put_line(FND_FILE.OUTPUT, 'Number of Instances: '|| p_num_instances);
288 fnd_file.put_line(FND_FILE.OUTPUT, 'Org Id: '||p_operating_unit);
289 fnd_file.put_line(FND_FILE.OUTPUT, 'Process Orders With Null Org: '||p_process_orders_with_null_org);
290 fnd_file.put_line(FND_FILE.OUTPUT, 'Default Org Id: '||p_default_org_id);
291 fnd_file.put_line(FND_FILE.OUTPUT, '');
292
293 --Check if Ct is atleast on Patchset Level H
294 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL < '110508' And
295 l_order_source = '20'
296 Then
297 fnd_file.put_line(FND_FILE.OUTPUT, 'Cannot Import Order for Order Source XML. This functionality is available only from Pack H onwards');
298 fnd_file.put_line(FND_FILE.OUTPUT, 'End of Order Import Concurrent Program');
299 fnd_file.put_line(FND_FILE.OUTPUT, '');
300 Return;
301 End If;
302
303 --Check if Ct is atleast on Patchset Level H
304 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL < '110508' And
305 G_IMPORT_SHIPMENTS = 'YES'
306 Then
307 fnd_file.put_line(FND_FILE.OUTPUT, 'Cannot Import Multiple Shipments with the same Line Reference. This functionality is available only from Pack H onwards');
308 fnd_file.put_line(FND_FILE.OUTPUT, 'End of Order Import Concurrent Program');
309 fnd_file.put_line(FND_FILE.OUTPUT, '');
310 Return;
311 End If;
312
313 /* -----------------------------------------------------------
314 Setting Debug On
315 -----------------------------------------------------------
316 */
317 -- Removing this initialization as per change in debug package
318 -- OE_DEBUG_PUB.debug_on;
319 -- OE_DEBUG_PUB.SetDebugLevel(5);
320 -- OE_DEBUG_PUB.initialize;
321 -- If l_debug_level Is Null
322 -- Then
323 -- l_debug_level := to_number(nvl(fnd_profile.value('ONT_DEBUG_LEVEL'),'0'));
324 -- End If;
325
326 IF l_debug_level > 0 THEN
327 oe_debug_pub.add( 'AFTER SETTING DEBUG ON' ) ;
328 END IF;
329
330
331 -----------------------------------------------------------
332 -- Setting Debug Mode and File
333 -----------------------------------------------------------
334
335 FND_FILE.Put_Line(FND_FILE.OUTPUT,'Debug Level: '||l_debug_level);
336
337 IF nvl(l_debug_level, 1) > 0 THEN
338 -- fnd_profile.put('OE_DEBUG_LOG_DIRECTORY','/sqlcom/outbound');
339 l_filename := OE_DEBUG_PUB.set_debug_mode ('FILE');
340 FND_FILE.Put_Line(FND_FILE.OUTPUT,'Debug File: ' || l_filename);
341 FND_FILE.Put_Line(FND_FILE.OUTPUT, '');
342 -- Following line moved inside because of the bug 3328608
343 l_filename := OE_DEBUG_PUB.set_debug_mode ('CONC');
344 END IF;
345
346
347
348 /* -----------------------------------------------------------
349 Initialization
350 -----------------------------------------------------------
351 */
352 IF l_debug_level > 0 THEN
353 oe_debug_pub.add( 'BEFORE INITIALIZATION' ) ;
354 END IF;
355 --Changes made for Bug no 5493479 start
356 /*-----------------------------------------------------------
357 Set the Context for Order Import
358 ------------------------------------------------------------
359 */
360
361 if ( OE_ORDER_IMPORT_MAIN_PVT.G_CONTEXT_ID IS NULL) THEN
362 OE_ORDER_IMPORT_MAIN_PVT.G_CONTEXT_ID :=
363 to_number(rtrim(SUBSTRB(SYS_CONTEXT('USERENV','CLIENT_INFO'),1,10),' '));
364 end if;
365 --Changes made for Bug no 5493479 end
366
367 /* -----------------------------------------------------------
368 Get Concurrent Request Id
369 -----------------------------------------------------------
370 */
371 IF l_debug_level > 0 THEN
372 oe_debug_pub.add( 'BEFORE GETTING REQUEST ID' ) ;
373 oe_debug_pub.add( 'PERFORMANCE PARAMETER:' || p_perf_param);
374 END IF;
375
376 --Bug# 12333065 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
377 l_request_id := FND_GLOBAL.CONC_REQUEST_ID; --bug# 12333065
378
379 IF l_debug_level > 0 THEN
380 oe_debug_pub.add( 'REQUEST ID: '|| TO_CHAR ( L_REQUEST_ID ) ) ;
381 END IF;
382 fnd_file.put_line(FND_FILE.OUTPUT, 'Request Id: '|| to_char(l_request_id));
383
384
385 IF l_num_instances > 0 THEN
386 /* -----------------------------------------------------------
387 Sources
388 -----------------------------------------------------------
389 */
390 IF l_debug_level > 0 THEN
391 oe_debug_pub.add( 'INSIDE NUM INSTANCES LOOP' ) ;
392 oe_debug_pub.add( 'BEFORE SOURCES LOOP' ) ;
393 END IF;
394
395 OPEN l_source_cursor;
396 LOOP
397 FETCH l_source_cursor
398 INTO l_order_source_id
399 ;
400 IF l_debug_level > 0 THEN
401 oe_debug_pub.add( 'SOURCE CURSOR VALUE = '||L_ORDER_SOURCE_ID ) ;
402 END IF;
403 EXIT WHEN l_source_cursor%NOTFOUND;
404
405 /* -----------------------------------------------------------
406 Update Concurrent Request Ids
407 -----------------------------------------------------------
408 */
409 /* oe_debug_pub.add('No of orders in inf table');
410 SELECT COUNT(*) INTO l_ord_count
411 FROM oe_headers_iface_all
412 WHERE order_source_id = 1084
413 AND ( nvl(NULL,' ') = ' ' OR
414 (nvl(NULL,' ') <> ' ' AND
415 nvl(NULL,' ') = nvl(orig_sys_document_ref,' ')))
416 AND ( nvl(NULL,' ') = ' ' OR
417 (nvl(NULL,' ') <> ' ' AND
418 nvl(NULL,' ') = nvl(operation_code,' ')))
419 AND request_id IS NULL
420 AND nvl(error_flag,'N') = 'N';
421 oe_debug_pub.add('No of ord in inf table = '||l_ord_count);
422 oe_debug_pub.add('before updating concurrent request id');
423 */
424
425 UPDATE oe_headers_interface
426 SET request_id = l_request_id
427 WHERE order_source_id = l_order_source_id
428 AND ( nvl(l_orig_sys_document_ref,' ') = ' ' OR
429 (nvl(l_orig_sys_document_ref,' ') <> ' ' AND
430 nvl(l_orig_sys_document_ref,' ') = nvl(orig_sys_document_ref,' ')))
431 AND ( nvl(l_operation_code,' ') = ' ' OR
432 (nvl(l_operation_code,' ') <> ' ' AND
433 nvl(l_operation_code,' ') = nvl(operation_code,' ')))
434 AND ( l_sold_to_org_id IS NULL OR
435 (l_sold_to_org_id IS NOT NULL AND
436 nvl(l_sold_to_org_id, FND_API.G_MISS_NUM) = nvl(sold_to_org_id, FND_API.G_MISS_NUM)))
437 AND
438 ( l_sold_to_org IS NULL OR
439 (l_sold_to_org IS NOT NULL AND
440 nvl(l_sold_to_org, FND_API.G_MISS_CHAR) = nvl(sold_to_org, FND_API.G_MISS_CHAR)))
441 AND ( l_change_sequence IS NULL OR
442 (l_change_sequence IS NOT NULL AND
443 nvl(l_change_sequence, FND_API.G_MISS_CHAR) = nvl(change_sequence, FND_API.G_MISS_CHAR)))
444 AND request_id IS NULL
445 AND nvl(error_flag,'N') = 'N';
446
447 --MOAC
448 IF p_process_orders_with_null_org = 'Y' THEN
449 IF
450 (p_operating_unit IS NOT NULL AND
451 p_operating_unit = p_default_org_id)
452 OR
453 (p_operating_unit IS NULL AND
454 p_default_org_id IS NOT NULL)
455 THEN
456
457 UPDATE oe_headers_iface_all
458 SET request_id = l_request_id,
459 org_id = p_default_org_id
460 WHERE order_source_id = l_order_source_id
461 AND ( nvl(l_orig_sys_document_ref,' ') = ' ' OR
462 (nvl(l_orig_sys_document_ref,' ') <> ' ' AND
463 nvl(l_orig_sys_document_ref,' ') = nvl(orig_sys_document_ref,' ')))
464 AND ( nvl(l_operation_code,' ') = ' ' OR
465 (nvl(l_operation_code,' ') <> ' ' AND
466 nvl(l_operation_code,' ') = nvl(operation_code,' ')))
467 AND ( l_sold_to_org_id IS NULL OR
468 (l_sold_to_org_id IS NOT NULL AND
469 nvl(l_sold_to_org_id, FND_API.G_MISS_NUM) = nvl(sold_to_org_id, FND_API.G_MISS_NUM)))
470 AND
471 ( l_sold_to_org IS NULL OR
472 (l_sold_to_org IS NOT NULL AND
473 nvl(l_sold_to_org, FND_API.G_MISS_CHAR) = nvl(sold_to_org, FND_API.G_MISS_CHAR)))
474 AND ( l_change_sequence IS NULL OR
475 (l_change_sequence IS NOT NULL AND
476 nvl(l_change_sequence, FND_API.G_MISS_CHAR) = nvl(change_sequence, FND_API.G_MISS_CHAR)))
477 AND request_id IS NULL
478 AND nvl(error_flag,'N') = 'N'
479 AND org_id IS NULL;
480
481 END IF;
482 END IF;
483
484
485
486 END LOOP; /* Sources cursor */
487 CLOSE l_source_cursor;
488
489 COMMIT;
490
491 SELECT COUNT(*)
492 INTO l_row_count
493 FROM oe_headers_iface_all -- MOAC
494 WHERE request_id = l_request_id;
495 IF l_debug_level > 0 THEN
496 oe_debug_pub.add( 'ROW COUNT = '||L_ROW_COUNT ) ;
497 END IF;
498
499 IF l_row_count = 0 THEN
500 fnd_file.put_line(FND_FILE.OUTPUT,'No orders to process');
501 fnd_file.put_line(FND_FILE.OUTPUT,'Not spawning any child processes');
502 ELSE
503 -- aksingh working
504 l_req_data := fnd_conc_global.request_data;
505 if (l_req_data is not null) then
506 l_req_data_counter := to_number(l_req_data);
507 l_req_data_counter := l_req_data_counter + 1;
508 --errbuf := 'Done!';
509 -- retcode := 0;
510 --return;
511 else
512 l_req_data_counter := 1;
513 end if;
514 IF l_num_instances = 1 THEN
515 batch_size_all := l_row_count;
516 ELSE
517 batch_size_all := FLOOR(l_row_count/l_num_instances);
518 l_mod := MOD(l_row_count,l_num_instances);
519 --batch_last := batch_size_all + MOD(l_row_count,l_num_instances);
520 END IF;
521 IF l_debug_level > 0 THEN
522 oe_debug_pub.add( 'BATCH SIZE = '||BATCH_SIZE_ALL ) ;
523 oe_debug_pub.add( 'MOD = '||L_MOD ) ;
524 END IF;
525
526 IF l_num_instances > l_row_count THEN
527 l_num_instances := l_row_count;
528 END IF;
529
530 IF l_debug_level > 0 THEN
531 oe_debug_pub.add( 'NUM INSTANCES = '||L_NUM_INSTANCES ) ;
532 END IF;
533 FOR loop_counter IN 1..l_num_instances LOOP
534 IF l_debug_level > 0 THEN
535 oe_debug_pub.add( 'INSIDE FOR LOOP FOR SPAWNING CHILD REQ' ) ;
536 END IF;
537 batch_size := batch_size_all;
538 x_num_instances := 0;
539 -- x_new_request_id := new_request_id;
540 --p_validate_desc_flex added for bug4343612
541 new_request_id := FND_REQUEST.SUBMIT_REQUEST('ONT', 'OEOIMP', 'Order Import Child Req' || to_char(l_req_data_counter), NULL, TRUE, p_operating_unit,l_order_source, NULL, NULL,
542 p_validate_only, p_debug_level, 0, NULL, NULL, NULL, p_perf_param,p_rtrim_data,
543 p_process_orders_with_null_org,p_default_org_id,p_validate_desc_flex);
544
545 IF l_debug_level > 0 THEN
546 oe_debug_pub.add( 'CHILD REQUEST ID = '||NEW_REQUEST_ID ) ;
547 END IF;
548 fnd_file.put_line(FND_FILE.OUTPUT, 'Child Request ID: '||new_request_id);
549
550 IF (new_request_id = 0) THEN
551 fnd_file.put_line(FND_FILE.OUTPUT,'Error in submitting child request');
552 errbuf := FND_MESSAGE.GET;
553 retcode := 2;
554 ELSE
555 IF loop_counter <= l_mod THEN
556 batch_size := batch_size_all + 1;
557 END IF;
558 IF l_debug_level > 0 THEN
559 oe_debug_pub.add( 'BEFORE UPDATING REQ ID' ) ;
560 oe_debug_pub.add( 'LOOP COUNT = '||LOOP_COUNTER ) ;
561 oe_debug_pub.add( 'BATCH_SIZE_ALL = '||BATCH_SIZE_ALL ) ;
562 oe_debug_pub.add( 'BATCH_SIZE = '||BATCH_SIZE ) ;
563 END IF;
564
565 IF (p_perf_param = 'Y') THEN
566 -- only update one record
567 UPDATE oe_headers_iface_all
568 SET request_id = new_request_id
569 WHERE request_id = l_request_id
570 AND ROWNUM =1;
571 ELSE
572 -- update batchsize records
573 UPDATE oe_headers_iface_all
574 SET request_id = new_request_id
575 WHERE request_id = l_request_id
576 AND ROWNUM <= batch_size;
577 END IF;
578
579 COMMIT;
580
581 END IF;
582 END LOOP;
583 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
584 request_data => to_char(l_req_data_counter));
585 errbuf := 'Sub-Request ' || to_char(l_req_data_counter) || 'submitted!';
586 retcode := 0;
587 -- fnd_file.put_line(FND_FILE.OUTPUT,'No of orders imported: 0');
588 -- fnd_file.put_line(FND_FILE.OUTPUT,'No of orders failed: 0');
589 fnd_file.put_line(FND_FILE.OUTPUT,'');
590 END IF;
591
592 ELSIF l_num_instances = 0 THEN
593 /* -----------------------------------------------------------
594 Headers
595 -----------------------------------------------------------
596 */
597 --oe_debug_pub.add('before headers loop');
598
599 l_count_header := 0;
600 l_count_header_success := 0;
601 l_count_header_failure := 0;
602
603 <<dist>>
604
605 IF l_debug_level > 0 THEN
606 oe_debug_pub.add( 'after goto') ;
607 END IF;
608
609 -- if profile is set to 'Y' do updates in interface table based on
610 -- customer-inclusive key information
611
612
613 if (l_customer_key_profile = 'Y') then
614
615 IF l_debug_level > 0 THEN
616 oe_debug_pub.add( 'customer key profile was yes') ;
617 END IF;
618
619
620 -- start of customer inclusive request_id updates
621
622 UPDATE oe_lines_iface_all
623 SET request_id = l_request_id
624 WHERE (order_source_id, orig_sys_document_ref, nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR),nvl(org_id,nvl(p_default_org_id,FND_API.G_MISS_NUM))) IN
625 ( SELECT order_source_id, orig_sys_document_ref,
626 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR), nvl(org_id,FND_API.G_MISS_NUM)
627 FROM oe_headers_iface_all
628 WHERE request_id = l_request_id);
629 /*AND decode(p_perf_param, 'Y',
630 nvl(error_flag,'N'), ' ')
631 = decode(p_perf_param, 'Y',
632 'N', ' '); */ -- Bug 5205691
633
634 IF l_debug_level > 0 THEN
635 oe_debug_pub.add('rows updated: ' || sql%rowcount);
636 END IF;
637
638 COMMIT;
639
640 UPDATE oe_price_adjs_iface_all
641 SET request_id = l_request_id
642 WHERE (order_source_id, orig_sys_document_ref,
643 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR),nvl(org_id,nvl(p_default_org_id,FND_API.G_MISS_NUM))) IN
644 ( SELECT order_source_id, orig_sys_document_ref,
645 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR), nvl(org_id,FND_API.G_MISS_NUM)
646 FROM oe_headers_iface_all
647 WHERE request_id = l_request_id) ;
648 /*AND decode(p_perf_param, 'Y',
649 nvl(error_flag,'N'), ' ')
650 = decode(p_perf_param, 'Y',
651 'N', ' '); */ -- Bug 5205691
652
653 COMMIT;
654
655
656 UPDATE oe_payments_iface_all
657 SET request_id = l_request_id
658 WHERE (order_source_id, orig_sys_document_ref,
659 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR),nvl(org_id,nvl(p_default_org_id,FND_API.G_MISS_NUM))) IN
660 ( SELECT order_source_id, orig_sys_document_ref,
661 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR), nvl(org_id,FND_API.G_MISS_NUM)
662 FROM oe_headers_iface_all
663 WHERE request_id = l_request_id) ;
664 /* AND decode(p_perf_param, 'Y',
665 nvl(error_flag,'N'), ' ')
666 = decode(p_perf_param, 'Y',
667 'N', ' '); */ -- Bug 5205691
668
669
670 IF l_debug_level > 0 THEN
671 oe_debug_pub.add('oe payments rows updated: ' || sql%rowcount);
672 END IF;
673
674 COMMIT;
675
676 /* Added for #1433292 */
677 UPDATE oe_price_atts_iface_all
678 SET request_id = l_request_id
679 WHERE (order_source_id, orig_sys_document_ref,
680 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR),nvl(org_id,nvl(p_default_org_id,FND_API.G_MISS_NUM))) IN
681 ( SELECT order_source_id, orig_sys_document_ref,
682 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR), nvl(org_id,FND_API.G_MISS_NUM)
683 FROM oe_headers_iface_all
684 WHERE request_id = l_request_id) ;
685 /* AND decode(p_perf_param, 'Y',
686 nvl(error_flag,'N'), ' ')
687 = decode(p_perf_param, 'Y',
688 'N', ' '); */ -- Bug 5205691
689
690 COMMIT;
691
692
693 UPDATE oe_credits_iface_all
694 SET request_id = l_request_id
695 WHERE (order_source_id, orig_sys_document_ref,
696 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR),nvl(org_id,nvl(p_default_org_id,FND_API.G_MISS_NUM))) IN
697 ( SELECT order_source_id, orig_sys_document_ref,
698 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR), nvl(org_id,FND_API.G_MISS_NUM)
699 FROM oe_headers_iface_all
700 WHERE request_id = l_request_id) ;
701 /*AND decode(p_perf_param, 'Y',
702 nvl(error_flag,'N'), ' ')
703 = decode(p_perf_param, 'Y',
704 'N', ' '); */ -- Bug 5205691
705
706 COMMIT;
707
708 UPDATE oe_lotserials_iface_all
709 SET request_id = l_request_id
710 WHERE (order_source_id, orig_sys_document_ref,
711 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR),nvl(org_id,nvl(p_default_org_id,FND_API.G_MISS_NUM))) IN
712 ( SELECT order_source_id, orig_sys_document_ref,
713 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR), nvl(org_id,FND_API.G_MISS_NUM)
714 FROM oe_headers_iface_all
715 WHERE request_id = l_request_id) ;
716 /*AND decode(p_perf_param, 'Y',
717 nvl(error_flag,'N'), ' ')
718 = decode(p_perf_param, 'Y',
719 'N', ' '); */ -- Bug 5205691
720
721 COMMIT;
722
723 UPDATE oe_reservtns_iface_all
724 SET request_id = l_request_id
725 WHERE (order_source_id, orig_sys_document_ref,
726 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR),nvl(org_id,nvl(p_default_org_id,FND_API.G_MISS_NUM))) IN
727 ( SELECT order_source_id, orig_sys_document_ref,
728 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR), nvl(org_id,FND_API.G_MISS_NUM)
729 FROM oe_headers_iface_all
730 WHERE request_id = l_request_id) ;
731 /* AND decode(p_perf_param, 'Y',
732 nvl(error_flag,'N'), ' ')
733 = decode(p_perf_param, 'Y',
734 'N', ' '); */ -- Bug 5205691
735
736 COMMIT;
737
738 UPDATE oe_actions_iface_all
739 SET request_id = l_request_id
740 WHERE (order_source_id, orig_sys_document_ref,
741 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR),nvl(org_id,nvl(p_default_org_id,FND_API.G_MISS_NUM))) IN
742 ( SELECT order_source_id, orig_sys_document_ref,
743 nvl(sold_to_org_id, FND_API.G_MISS_NUM), nvl(sold_to_org, FND_API.G_MISS_CHAR), nvl(change_sequence, FND_API.G_MISS_CHAR), nvl(org_id,FND_API.G_MISS_NUM)
744 FROM oe_headers_iface_all
745 WHERE request_id = l_request_id) ;
746 /* AND decode(p_perf_param, 'Y',
747 nvl(error_flag,'N'), ' ')
748 = decode(p_perf_param, 'Y',
749 'N', ' '); */ -- Bug 5205691
750
751 COMMIT;
752
753 -- end of customer-inclusive request_id updates
754
755 end if;
756
757
758 OPEN l_request_cursor;
759
760 LOOP
761
762
763 FETCH l_request_cursor
764 INTO l_order_source_id
765 ,l_orig_sys_document_ref
766 , l_sold_to_org_id
767 , l_sold_to_org
768 ,l_change_sequence
769 ,l_closed_flag
770 ,l_org_id
771 ;
772 EXIT WHEN l_request_cursor%NOTFOUND;
773
774 -- If doing throughput-enhanced imports, then set the looped_flag if it has not
775 -- been set already
776 If p_perf_param = 'Y' then
777 If l_looped_flag = 'N' then
778 IF l_debug_level > 0 THEN
779 oe_debug_pub.add('setting looped flag');
780 END IF;
781 l_looped_flag := 'Y';
782 End If;
783 End If;
784
785 IF l_debug_level > 0 THEN
786 oe_debug_pub.add('Performance Parameter: ' || p_perf_param);
787 END IF;
788
789 IF ( p_perf_param = 'Y') THEN
790
791 if l_count_header = 0 then
792
793 begin
794 select parent_request_id
795 into l_pnt_request_id
796 from fnd_concurrent_requests
797 where request_id=l_request_id;
798
799 exception
800 when others then
801 IF l_debug_level > 0 THEN
802 oe_debug_pub.add('Unexpected error: '||sqlerrm);
803 END IF;
804 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
805 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Order_Import_Conc_Pgm');
806 END IF;
807 fnd_file.put_line(FND_FILE.OUTPUT,'Unexpected error: ' || sqlerrm);
808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809
810 end;
811
812 end if;
813
814 IF l_debug_level > 0 THEN
815 oe_debug_pub.add('first parent ' || l_pnt_request_id);
816 END IF;
817 END IF;
818
819 --if customer_key_profile <> 'Y'
820 --update the corresponding child table entries with the header-level
821 --customer/change_seq information to allow old functionality to remain
822 --unchanged
823
824 if (l_customer_key_profile <> 'Y') then
825
826
827 UPDATE oe_lines_iface_all
828 SET request_id = l_request_id,
829 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
830 sold_to_org = nvl(sold_to_org, l_sold_to_org),
831 org_id = l_org_id
832 WHERE order_source_id = l_order_source_id
833 AND orig_sys_document_ref = l_orig_sys_document_ref
834 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
835 AND nvl(org_id,l_org_id) = l_org_id ;
836 /* AND decode(p_perf_param, 'Y',
837 nvl(error_flag,'N'), ' ')
838 = decode(p_perf_param, 'Y',
839 'N', ' '); */ -- Bug 5205691
840
841
842 COMMIT;
843
844 UPDATE oe_price_adjs_iface_all
845 SET request_id = l_request_id,
846 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
847 sold_to_org = nvl(sold_to_org, l_sold_to_org),
848 org_id = l_org_id
849 WHERE order_source_id = l_order_source_id
850 AND orig_sys_document_ref = l_orig_sys_document_ref
851 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
852 AND nvl(org_id,l_org_id) = l_org_id ;
853 /* AND decode(p_perf_param, 'Y',
854 nvl(error_flag,'N'), ' ')
855 = decode(p_perf_param, 'Y',
856 'N', ' '); */ -- Bug 5205691
857
858 COMMIT;
859
860 UPDATE oe_payments_iface_all /* Bug #3419970 */
861 SET request_id = l_request_id,
862 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
863 sold_to_org = nvl(sold_to_org, l_sold_to_org),
864 org_id = l_org_id
865 WHERE order_source_id = l_order_source_id
866 AND orig_sys_document_ref = l_orig_sys_document_ref
867 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
868 AND nvl(org_id,l_org_id) = l_org_id ;
869 /* AND decode(p_perf_param, 'Y',
870 nvl(error_flag,'N'), ' ')
871 = decode(p_perf_param, 'Y',
872 'N', ' '); */ -- Bug 5205691
873
874 COMMIT;
875
876 UPDATE oe_price_atts_iface_all
877 SET request_id = l_request_id,
878 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
879 sold_to_org = nvl(sold_to_org, l_sold_to_org),
880 org_id = l_org_id
881 WHERE order_source_id = l_order_source_id
882 AND orig_sys_document_ref = l_orig_sys_document_ref
883 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
884 AND nvl(org_id,l_org_id) = l_org_id ;
885 /* AND decode(p_perf_param, 'Y',
886 nvl(error_flag,'N'), ' ')
887 = decode(p_perf_param, 'Y',
888 'N', ' '); */ -- Bug 5205691
889
890 COMMIT;
891
892 UPDATE oe_credits_iface_all
893 SET request_id = l_request_id,
894 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
895 sold_to_org = nvl(sold_to_org, l_sold_to_org),
896 org_id = l_org_id
897 WHERE order_source_id = l_order_source_id
898 AND orig_sys_document_ref = l_orig_sys_document_ref
899 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
900 AND nvl(org_id,l_org_id) = l_org_id ;
901 /* AND decode(p_perf_param, 'Y',
902 nvl(error_flag,'N'), ' ')
903 = decode(p_perf_param, 'Y',
904 'N', ' '); */ -- Bug 5205691
905
906 COMMIT;
907
908 UPDATE oe_reservtns_iface_all
909 SET request_id = l_request_id,
910 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
911 sold_to_org = nvl(sold_to_org, l_sold_to_org),
912 org_id = l_org_id
913 WHERE order_source_id = l_order_source_id
914 AND orig_sys_document_ref = l_orig_sys_document_ref
915 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
916 AND nvl(org_id,l_org_id) = l_org_id ;
917 /* AND decode(p_perf_param, 'Y',
918 nvl(error_flag,'N'), ' ')
919 = decode(p_perf_param, 'Y',
920 'N', ' '); */ -- Bug 5205691
921
922 COMMIT;
923
924 UPDATE oe_lotserials_iface_all
925 SET request_id = l_request_id,
926 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
927 sold_to_org = nvl(sold_to_org, l_sold_to_org),
928 org_id = l_org_id
929 WHERE order_source_id = l_order_source_id
930 AND orig_sys_document_ref = l_orig_sys_document_ref
931 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
932 AND nvl(org_id,l_org_id) = l_org_id ;
933 /* AND decode(p_perf_param, 'Y',
934 nvl(error_flag,'N'), ' ')
935 = decode(p_perf_param, 'Y',
936 'N', ' '); */ -- Bug 5205691
937
938 COMMIT;
939
940 UPDATE oe_actions_iface_all
941 SET request_id = l_request_id,
942 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
943 sold_to_org = nvl(sold_to_org, l_sold_to_org),
944 org_id = l_org_id
945 WHERE order_source_id = l_order_source_id
946 AND orig_sys_document_ref = l_orig_sys_document_ref
947 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
948 AND nvl(org_id,l_org_id) = l_org_id ;
949 /* AND decode(p_perf_param, 'Y',
950 nvl(error_flag,'N'), ' ')
951 = decode(p_perf_param, 'Y',
952 'N', ' '); */ -- Bug 5205691
953
954 COMMIT;
955
956 end if;
957
958 -- end of updates to customer information based on profile
959
960
961 l_count_header := l_count_header + 1;
962
963 IF l_debug_level > 0 THEN
964 oe_debug_pub.add( 'ORDER SOURCE ID: '|| TO_CHAR ( L_ORDER_SOURCE_ID ) ) ;
965 oe_debug_pub.add( 'ORIG SYS REFERENCE: '|| L_ORIG_SYS_DOCUMENT_REF ) ;
966 oe_debug_pub.add( 'CHANGE SEQUENCE: ' || L_CHANGE_SEQUENCE ) ;
967 oe_debug_pub.add( 'ORG ID: '||l_org_id);
968 oe_debug_pub.add( 'L_RETURN_STATUS: ' || L_RETURN_STATUS ) ;
969 END IF;
970
971 -- MOAC set the policy context based on the org_id
972 If G_ORG_ID IS NULL THEN
973 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);
974 G_ORG_ID := l_org_id;
975 ELSIF G_ORG_ID IS NOT NULL AND
976 G_ORG_ID <> l_org_id THEN
977 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);
978 G_ORG_ID := l_org_id;
979 END IF;
980
981
982 /* -----------------------------------------------------------
983 Call Import_Order procedure
984 -----------------------------------------------------------
985 */
986 --oe_debug_pub.add('before calling Import_Order procedure');
987
988
989 IF l_closed_flag = 'N' THEN
990 --
991 IF l_debug_level > 0 THEN
992 oe_debug_pub.add( 'BEFORE CALLING IMPORT_ORDER PROCEDURE' ) ;
993 oe_debug_pub.add('rtrim data ='||nvl(l_rtrim_data,'Null'));
994 END IF;
995 OE_ORDER_IMPORT_PVT.Import_Order (
996 p_request_id => l_request_id,
997 p_order_source_id => l_order_source_id,
998 p_orig_sys_document_ref => l_orig_sys_document_ref,
999 p_sold_to_org_id => l_sold_to_org_id,
1000 p_sold_to_org => l_sold_to_org,
1001 p_change_sequence => l_change_sequence,
1002 p_org_id => l_org_id,
1003 p_validate_only => l_validate_only,
1004 p_init_msg_list => l_init_msg_list,
1005 p_rtrim_data => l_rtrim_data,
1006 p_msg_count => l_msg_count,
1007 p_msg_data => l_msg_data,
1008 p_return_status => l_return_status,
1009 p_validate_desc_flex => p_validate_desc_flex --bug4343612
1010 );
1011
1012 ELSE
1013 --
1014 IF l_debug_level > 0 THEN
1015 oe_debug_pub.add( 'BEFORE CALLING IMPORT_ORDER PROCEDURE FOR CLOSED' ) ;
1016 END IF;
1017 OE_CNCL_ORDER_IMPORT_PVT.Import_Order (
1018 p_request_id => l_request_id,
1019 p_order_source_id => l_order_source_id,
1020 p_orig_sys_document_ref => l_orig_sys_document_ref,
1021 p_sold_to_org_id => l_sold_to_org_id,
1022 p_sold_to_org => l_sold_to_org,
1023 p_change_sequence => l_change_sequence,
1024 p_org_id => l_org_id,
1025 p_validate_only => l_validate_only,
1026 p_init_msg_list => l_init_msg_list,
1027 p_msg_count => l_msg_count,
1028 p_msg_data => l_msg_data,
1029 p_return_status => l_return_status
1030 );
1031 --
1032 END IF;
1033
1034 --oe_debug_pub.add('after call Import_Order l_return_status ' || l_return_status);
1035 IF l_return_status = FND_API.G_RET_STS_ERROR OR
1036 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1037 THEN
1038 l_count_header_failure := l_count_header_failure + 1;
1039 ELSE
1040 l_count_header_success := l_count_header_success + 1;
1041 END IF;
1042
1043
1044 IF (p_perf_param = 'Y') THEN
1045
1046 IF l_debug_level > 0 THEN
1047 oe_debug_pub.add('after order_import for docref='|| l_orig_sys_document_ref || ' and request_id ' || l_request_id);
1048 oe_debug_pub.add('parent request_id:' || l_pnt_request_id);
1049 END IF;
1050
1051
1052 begin
1053 loop
1054
1055 select rowidtochar(rowid)
1056 into l_rowid
1057 from oe_headers_iface_all --MOAC
1058 where request_id = l_pnt_request_id
1059 and rownum = 1;
1060
1061 UPDATE oe_headers_iface_all --MOAC
1062 SET request_id = l_request_id
1063 WHERE request_id = l_pnt_request_id
1064 AND nvl(error_flag,'N') = 'N'
1065 AND rowidtochar(rowid) = l_rowid
1066 AND rownum = 1
1067 RETURNING orig_sys_document_ref
1068 INTO l_updated_docref;
1069
1070
1071 if sql%rowcount > 0 then
1072 IF l_debug_level > 0 THEN
1073 oe_debug_pub.add('rowcount:' || sql%rowcount || ' updated docref:' || l_updated_docref);
1074 END IF;
1075 commit;
1076 exit;
1077 end if;
1078
1079 end loop;
1080
1081 close l_request_cursor;
1082 IF l_debug_level > 0 THEN
1083 oe_debug_pub.add('after cursor closed');
1084 END IF;
1085 goto dist;
1086
1087 exception
1088 when no_data_found then
1089 IF l_debug_level > 0 THEN
1090 oe_debug_pub.add('In handled no data found exception');
1091 oe_debug_pub.add('No more records to process');
1092 END IF;
1093
1094 EXIT;
1095 when others then
1096 IF l_debug_level > 0 THEN
1097 oe_debug_pub.add('others exception when attempting update' ||sqlerrm);
1098 END IF;
1099 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1100 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Order_Import_Conc_Pgm');
1101 END IF;
1102 fnd_file.put_line(FND_FILE.OUTPUT,'Unexpected error: ' || sqlerrm);
1103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1104 end;
1105
1106 END IF; -- p_perf_param was 'Y'
1107
1108
1109 END LOOP; /* Request cursor */
1110
1111 CLOSE l_request_cursor;
1112
1113 IF l_debug_level > 0 THEN
1114 oe_debug_pub.add( 'NO. OF ORDERS FOUND: ' || L_COUNT_HEADER ) ;
1115 oe_debug_pub.add( 'NO. OF ORDERS IMPORTED: '|| L_COUNT_HEADER_SUCCESS ) ;
1116 oe_debug_pub.add( 'NO. OF ORDERS FAILED: ' || L_COUNT_HEADER_FAILURE ) ;
1117 END IF;
1118
1119 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders found: ' ||
1120 l_count_header);
1121 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders imported: '||
1122 l_count_header_success);
1123 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders failed: ' ||
1124 l_count_header_failure);
1125 fnd_file.put_line(FND_FILE.OUTPUT,'');
1126
1127
1128 /* SELECT count(*) INTO l_count_msgs
1129 FROM oe_processing_msgs_vl
1130 WHERE request_id = l_request_id;
1131
1132 IF l_count_msgs > 0 THEN
1133 fnd_file.put_line(FND_FILE.OUTPUT,'No. of messages: '||l_count_msgs);
1134 fnd_file.put_line(FND_FILE.OUTPUT,'');
1135 fnd_file.put_line(FND_FILE.OUTPUT,'Source/Order/Seq/Line Message');
1136 */
1137 /* -----------------------------------------------------------
1138 Messages
1139 -----------------------------------------------------------
1140 */
1141 IF l_debug_level > 0 THEN
1142 oe_debug_pub.add( 'BEFORE MESSAGES LOOP' ) ;
1143 END IF;
1144
1145 fnd_file.put_line(FND_FILE.OUTPUT,'');
1146 fnd_file.put_line(FND_FILE.OUTPUT,'Source/Order/Seq/Line Message');
1147 OPEN l_msg_cursor;
1148 LOOP
1149 FETCH l_msg_cursor
1150 INTO l_order_source_id
1151 , l_orig_sys_document_ref
1152 , l_change_sequence
1153 , l_orig_sys_line_ref
1154 , l_org_id --MOAC
1155 , l_message_text;
1156 EXIT WHEN l_msg_cursor%NOTFOUND;
1157
1158 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_order_source_id)
1159 ||'/'||l_orig_sys_document_ref
1160 ||'/'||l_change_sequence
1161 ||'/'||l_org_id
1162 ||'/'||l_orig_sys_line_ref
1163 ||' '||l_message_text);
1164 fnd_file.put_line(FND_FILE.OUTPUT,'');
1165 END LOOP;
1166 -- END IF;
1167
1168 END IF;
1169 /* -----------------------------------------------------------
1170 End of Order_Import_Conc_Pgm
1171 -----------------------------------------------------------
1172 */
1173 IF l_debug_level > 0 THEN
1174 oe_debug_pub.add( 'END OF ORDER IMPORT CONCURRENT PROGRAM' ) ;
1175 END IF;
1176 fnd_file.put_line(FND_FILE.OUTPUT, 'End of Order Import Concurrent Program');
1177 retcode := 0;
1178 --return;
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 IF l_debug_level > 0 THEN
1183 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
1184 END IF;
1185 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1186 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
1187 END IF;
1188
1189 retcode := 2;
1190
1191 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders found: ' ||
1192 l_count_header);
1193 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders imported: '||
1194 l_count_header_success);
1195 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders failed: ' ||
1196 l_count_header_failure);
1197 fnd_file.put_line(FND_FILE.OUTPUT,'');
1198
1199 END ORDER_IMPORT_CONC_PGM;
1200
1201
1202 PROCEDURE ORDER_IMPORT_FORM(
1203 p_request_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
1204 ,p_order_source_id IN NUMBER
1205 ,p_orig_sys_document_ref IN VARCHAR2
1206 ,p_sold_to_org_id IN NUMBER
1207 ,p_sold_to_org IN VARCHAR2
1208 ,p_change_sequence IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
1209 ,p_org_id IN Number
1210 ,p_validate_only IN VARCHAR2 DEFAULT FND_API.G_FALSE
1211 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE
1212 ,p_rtrim_data In Varchar2
1213 ,p_msg_count OUT NOCOPY NUMBER
1214
1215 ,p_msg_data OUT NOCOPY VARCHAR2
1216
1217 ,p_return_status OUT NOCOPY VARCHAR2
1218
1219
1220 ) IS
1221
1222 l_closed_flag VARCHAR2(1) DEFAULT 'N';
1223
1224 --
1225 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1226 --
1227 BEGIN
1228
1229
1230 --MOAC set policy context for single Org
1231 IF p_org_id IS NOT NULL THEN
1232 IF l_debug_level > 0 THEN
1233 oe_debug_pub.add( 'Setting policy context to single-org' ) ;
1234 END IF;
1235 MO_GLOBAL.set_policy_context('S',p_org_id);
1236 END IF;
1237
1238
1239 SELECT closed_flag
1240 INTO l_closed_flag
1241 FROM oe_headers_iface_all
1242 WHERE orig_sys_document_ref = p_orig_sys_document_ref
1243 AND order_source_id = p_order_source_id
1244 AND nvl(sold_to_org_id, -999) = nvl(p_sold_to_org_id, -999)
1245 AND nvl(sold_to_org, ' ') = nvl(p_sold_to_org, ' ')
1246 AND nvl(change_sequence, ' ') = nvl(p_change_sequence, ' ')
1247 AND nvl(org_id,-99) = nvl(p_org_id,-99)
1248 AND nvl(request_id, -999) = nvl(p_request_id, -999);
1249
1250
1251 IF (NVL(l_closed_flag,'N') = 'N') THEN
1252 --
1253 IF l_debug_level > 0 THEN
1254 oe_debug_pub.add( 'BEFORE CALLING IMPORT_ORDER PROCEDURE' ) ;
1255 END IF;
1256
1257
1258 OE_ORDER_IMPORT_PVT.Import_Order (
1259 p_request_id => p_request_id,
1260 p_order_source_id => p_order_source_id,
1261 p_orig_sys_document_ref => p_orig_sys_document_ref,
1262 p_sold_to_org_id => p_sold_to_org_id,
1263 p_sold_to_org => p_sold_to_org,
1264 p_change_sequence => p_change_sequence,
1265 p_org_id => p_org_id,
1266 p_validate_only => p_validate_only,
1267 p_init_msg_list => p_init_msg_list,
1268 p_rtrim_data => p_rtrim_data,
1269 p_msg_count => p_msg_count,
1270 p_msg_data => p_msg_data,
1271 p_return_status => p_return_status
1272 );
1273 --
1274
1275 ELSE
1276 --
1277 IF l_debug_level > 0 THEN
1278 oe_debug_pub.add( 'BEFORE CALLING IMPORT_ORDER PROCEDURE FOR CLOSED' ) ;
1279 END IF;
1280
1281
1282 OE_CNCL_ORDER_IMPORT_PVT.Import_Order (
1283 p_request_id => p_request_id,
1284 p_order_source_id => p_order_source_id,
1285 p_orig_sys_document_ref => p_orig_sys_document_ref,
1286 p_sold_to_org_id => p_sold_to_org_id,
1287 p_sold_to_org => p_sold_to_org,
1288 p_change_sequence => p_change_sequence,
1289 p_org_id => p_org_id,
1290 p_validate_only => p_validate_only,
1291 p_init_msg_list => p_init_msg_list,
1292 p_msg_count => p_msg_count,
1293 p_msg_data => p_msg_data,
1294 p_return_status => p_return_status
1295 );
1296 --
1297 END IF;
1298
1299 END ORDER_IMPORT_FORM;
1300
1301 END OE_ORDER_IMPORT_MAIN_PVT;