[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.13 2006/09/13 09:03:10 dmitra 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 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
377
378 IF l_debug_level > 0 THEN
379 oe_debug_pub.add( 'REQUEST ID: '|| TO_CHAR ( L_REQUEST_ID ) ) ;
380 END IF;
381 fnd_file.put_line(FND_FILE.OUTPUT, 'Request Id: '|| to_char(l_request_id));
382
383
384 IF l_num_instances > 0 THEN
385 /* -----------------------------------------------------------
386 Sources
387 -----------------------------------------------------------
388 */
389 IF l_debug_level > 0 THEN
390 oe_debug_pub.add( 'INSIDE NUM INSTANCES LOOP' ) ;
391 oe_debug_pub.add( 'BEFORE SOURCES LOOP' ) ;
392 END IF;
393
394 OPEN l_source_cursor;
395 LOOP
396 FETCH l_source_cursor
397 INTO l_order_source_id
398 ;
399 IF l_debug_level > 0 THEN
400 oe_debug_pub.add( 'SOURCE CURSOR VALUE = '||L_ORDER_SOURCE_ID ) ;
401 END IF;
402 EXIT WHEN l_source_cursor%NOTFOUND;
403
404 /* -----------------------------------------------------------
405 Update Concurrent Request Ids
406 -----------------------------------------------------------
407 */
408 /* oe_debug_pub.add('No of orders in inf table');
409 SELECT COUNT(*) INTO l_ord_count
410 FROM oe_headers_iface_all
411 WHERE order_source_id = 1084
412 AND ( nvl(NULL,' ') = ' ' OR
413 (nvl(NULL,' ') <> ' ' AND
414 nvl(NULL,' ') = nvl(orig_sys_document_ref,' ')))
415 AND ( nvl(NULL,' ') = ' ' OR
416 (nvl(NULL,' ') <> ' ' AND
417 nvl(NULL,' ') = nvl(operation_code,' ')))
418 AND request_id IS NULL
419 AND nvl(error_flag,'N') = 'N';
420 oe_debug_pub.add('No of ord in inf table = '||l_ord_count);
421 oe_debug_pub.add('before updating concurrent request id');
422 */
423
424 UPDATE oe_headers_interface
425 SET request_id = l_request_id
426 WHERE order_source_id = l_order_source_id
427 AND ( nvl(l_orig_sys_document_ref,' ') = ' ' OR
428 (nvl(l_orig_sys_document_ref,' ') <> ' ' AND
429 nvl(l_orig_sys_document_ref,' ') = nvl(orig_sys_document_ref,' ')))
430 AND ( nvl(l_operation_code,' ') = ' ' OR
431 (nvl(l_operation_code,' ') <> ' ' AND
432 nvl(l_operation_code,' ') = nvl(operation_code,' ')))
433 AND ( l_sold_to_org_id IS NULL OR
434 (l_sold_to_org_id IS NOT NULL AND
435 nvl(l_sold_to_org_id, FND_API.G_MISS_NUM) = nvl(sold_to_org_id, FND_API.G_MISS_NUM)))
436 AND
437 ( l_sold_to_org IS NULL OR
438 (l_sold_to_org IS NOT NULL AND
439 nvl(l_sold_to_org, FND_API.G_MISS_CHAR) = nvl(sold_to_org, FND_API.G_MISS_CHAR)))
440 AND ( l_change_sequence IS NULL OR
441 (l_change_sequence IS NOT NULL AND
442 nvl(l_change_sequence, FND_API.G_MISS_CHAR) = nvl(change_sequence, FND_API.G_MISS_CHAR)))
443 AND request_id IS NULL
444 AND nvl(error_flag,'N') = 'N';
445
446 --MOAC
447 IF p_process_orders_with_null_org = 'Y' THEN
448 IF
449 (p_operating_unit IS NOT NULL AND
450 p_operating_unit = p_default_org_id)
451 OR
452 (p_operating_unit IS NULL AND
453 p_default_org_id IS NOT NULL)
454 THEN
455
456 UPDATE oe_headers_iface_all
457 SET request_id = l_request_id,
458 org_id = p_default_org_id
459 WHERE order_source_id = l_order_source_id
460 AND ( nvl(l_orig_sys_document_ref,' ') = ' ' OR
461 (nvl(l_orig_sys_document_ref,' ') <> ' ' AND
462 nvl(l_orig_sys_document_ref,' ') = nvl(orig_sys_document_ref,' ')))
463 AND ( nvl(l_operation_code,' ') = ' ' OR
464 (nvl(l_operation_code,' ') <> ' ' AND
465 nvl(l_operation_code,' ') = nvl(operation_code,' ')))
466 AND ( l_sold_to_org_id IS NULL OR
467 (l_sold_to_org_id IS NOT NULL AND
468 nvl(l_sold_to_org_id, FND_API.G_MISS_NUM) = nvl(sold_to_org_id, FND_API.G_MISS_NUM)))
469 AND
470 ( l_sold_to_org IS NULL OR
471 (l_sold_to_org IS NOT NULL AND
472 nvl(l_sold_to_org, FND_API.G_MISS_CHAR) = nvl(sold_to_org, FND_API.G_MISS_CHAR)))
473 AND ( l_change_sequence IS NULL OR
474 (l_change_sequence IS NOT NULL AND
475 nvl(l_change_sequence, FND_API.G_MISS_CHAR) = nvl(change_sequence, FND_API.G_MISS_CHAR)))
476 AND request_id IS NULL
477 AND nvl(error_flag,'N') = 'N'
478 AND org_id IS NULL;
479
480 END IF;
481 END IF;
482
483
484
485 END LOOP; /* Sources cursor */
486 CLOSE l_source_cursor;
487
488 COMMIT;
489
490 SELECT COUNT(*)
491 INTO l_row_count
492 FROM oe_headers_iface_all -- MOAC
493 WHERE request_id = l_request_id;
494 IF l_debug_level > 0 THEN
495 oe_debug_pub.add( 'ROW COUNT = '||L_ROW_COUNT ) ;
496 END IF;
497
498 IF l_row_count = 0 THEN
499 fnd_file.put_line(FND_FILE.OUTPUT,'No orders to process');
500 fnd_file.put_line(FND_FILE.OUTPUT,'Not spawning any child processes');
501 ELSE
502 -- aksingh working
503 l_req_data := fnd_conc_global.request_data;
504 if (l_req_data is not null) then
505 l_req_data_counter := to_number(l_req_data);
506 l_req_data_counter := l_req_data_counter + 1;
507 --errbuf := 'Done!';
508 -- retcode := 0;
509 --return;
510 else
511 l_req_data_counter := 1;
512 end if;
513 IF l_num_instances = 1 THEN
514 batch_size_all := l_row_count;
515 ELSE
516 batch_size_all := FLOOR(l_row_count/l_num_instances);
517 l_mod := MOD(l_row_count,l_num_instances);
518 --batch_last := batch_size_all + MOD(l_row_count,l_num_instances);
519 END IF;
520 IF l_debug_level > 0 THEN
521 oe_debug_pub.add( 'BATCH SIZE = '||BATCH_SIZE_ALL ) ;
522 oe_debug_pub.add( 'MOD = '||L_MOD ) ;
523 END IF;
524
525 IF l_num_instances > l_row_count THEN
526 l_num_instances := l_row_count;
527 END IF;
528
529 IF l_debug_level > 0 THEN
530 oe_debug_pub.add( 'NUM INSTANCES = '||L_NUM_INSTANCES ) ;
531 END IF;
532 FOR loop_counter IN 1..l_num_instances LOOP
533 IF l_debug_level > 0 THEN
534 oe_debug_pub.add( 'INSIDE FOR LOOP FOR SPAWNING CHILD REQ' ) ;
535 END IF;
536 batch_size := batch_size_all;
537 x_num_instances := 0;
538 -- x_new_request_id := new_request_id;
539 --p_validate_desc_flex added for bug4343612
540 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,
541 p_validate_only, p_debug_level, 0, NULL, NULL, NULL, p_perf_param,p_rtrim_data,
542 p_process_orders_with_null_org,p_default_org_id,p_validate_desc_flex);
543
544 IF l_debug_level > 0 THEN
545 oe_debug_pub.add( 'CHILD REQUEST ID = '||NEW_REQUEST_ID ) ;
546 END IF;
547 fnd_file.put_line(FND_FILE.OUTPUT, 'Child Request ID: '||new_request_id);
548
549 IF (new_request_id = 0) THEN
550 fnd_file.put_line(FND_FILE.OUTPUT,'Error in submitting child request');
551 errbuf := FND_MESSAGE.GET;
552 retcode := 2;
553 ELSE
554 IF loop_counter <= l_mod THEN
555 batch_size := batch_size_all + 1;
556 END IF;
557 IF l_debug_level > 0 THEN
558 oe_debug_pub.add( 'BEFORE UPDATING REQ ID' ) ;
559 oe_debug_pub.add( 'LOOP COUNT = '||LOOP_COUNTER ) ;
560 oe_debug_pub.add( 'BATCH_SIZE_ALL = '||BATCH_SIZE_ALL ) ;
561 oe_debug_pub.add( 'BATCH_SIZE = '||BATCH_SIZE ) ;
562 END IF;
563
564 IF (p_perf_param = 'Y') THEN
565 -- only update one record
566 UPDATE oe_headers_iface_all
567 SET request_id = new_request_id
568 WHERE request_id = l_request_id
569 AND ROWNUM =1;
570 ELSE
571 -- update batchsize records
572 UPDATE oe_headers_iface_all
573 SET request_id = new_request_id
574 WHERE request_id = l_request_id
575 AND ROWNUM <= batch_size;
576 END IF;
577
578 COMMIT;
579
580 END IF;
581 END LOOP;
582 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
583 request_data => to_char(l_req_data_counter));
584 errbuf := 'Sub-Request ' || to_char(l_req_data_counter) || 'submitted!';
585 retcode := 0;
586 -- fnd_file.put_line(FND_FILE.OUTPUT,'No of orders imported: 0');
587 -- fnd_file.put_line(FND_FILE.OUTPUT,'No of orders failed: 0');
588 fnd_file.put_line(FND_FILE.OUTPUT,'');
589 END IF;
590
591 ELSIF l_num_instances = 0 THEN
592 /* -----------------------------------------------------------
593 Headers
594 -----------------------------------------------------------
595 */
596 --oe_debug_pub.add('before headers loop');
597
598 l_count_header := 0;
599 l_count_header_success := 0;
600 l_count_header_failure := 0;
601
602 <<dist>>
603
604 IF l_debug_level > 0 THEN
605 oe_debug_pub.add( 'after goto') ;
606 END IF;
607
608 -- if profile is set to 'Y' do updates in interface table based on
609 -- customer-inclusive key information
610
611
612 if (l_customer_key_profile = 'Y') then
613
614 IF l_debug_level > 0 THEN
615 oe_debug_pub.add( 'customer key profile was yes') ;
616 END IF;
617
618
619 -- start of customer inclusive request_id updates
620
621 UPDATE oe_lines_iface_all
622 SET request_id = l_request_id
623 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
624 ( SELECT order_source_id, orig_sys_document_ref,
625 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)
626 FROM oe_headers_iface_all
627 WHERE request_id = l_request_id);
628 /*AND decode(p_perf_param, 'Y',
629 nvl(error_flag,'N'), ' ')
630 = decode(p_perf_param, 'Y',
631 'N', ' '); */ -- Bug 5205691
632
633 IF l_debug_level > 0 THEN
634 oe_debug_pub.add('rows updated: ' || sql%rowcount);
635 END IF;
636
637 COMMIT;
638
639 UPDATE oe_price_adjs_iface_all
640 SET request_id = l_request_id
641 WHERE (order_source_id, orig_sys_document_ref,
642 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
643 ( SELECT order_source_id, orig_sys_document_ref,
644 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)
645 FROM oe_headers_iface_all
646 WHERE request_id = l_request_id) ;
647 /*AND decode(p_perf_param, 'Y',
648 nvl(error_flag,'N'), ' ')
649 = decode(p_perf_param, 'Y',
650 'N', ' '); */ -- Bug 5205691
651
652 COMMIT;
653
654
655 UPDATE oe_payments_iface_all
656 SET request_id = l_request_id
657 WHERE (order_source_id, orig_sys_document_ref,
658 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
659 ( SELECT order_source_id, orig_sys_document_ref,
660 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)
661 FROM oe_headers_iface_all
662 WHERE request_id = l_request_id) ;
663 /* AND decode(p_perf_param, 'Y',
664 nvl(error_flag,'N'), ' ')
665 = decode(p_perf_param, 'Y',
666 'N', ' '); */ -- Bug 5205691
667
668
669 IF l_debug_level > 0 THEN
670 oe_debug_pub.add('oe payments rows updated: ' || sql%rowcount);
671 END IF;
672
673 COMMIT;
674
675 /* Added for #1433292 */
676 UPDATE oe_price_atts_iface_all
677 SET request_id = l_request_id
678 WHERE (order_source_id, orig_sys_document_ref,
679 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
680 ( SELECT order_source_id, orig_sys_document_ref,
681 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)
682 FROM oe_headers_iface_all
683 WHERE request_id = l_request_id) ;
684 /* AND decode(p_perf_param, 'Y',
685 nvl(error_flag,'N'), ' ')
686 = decode(p_perf_param, 'Y',
687 'N', ' '); */ -- Bug 5205691
688
689 COMMIT;
690
691
692 UPDATE oe_credits_iface_all
693 SET request_id = l_request_id
694 WHERE (order_source_id, orig_sys_document_ref,
695 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
696 ( SELECT order_source_id, orig_sys_document_ref,
697 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)
698 FROM oe_headers_iface_all
699 WHERE request_id = l_request_id) ;
700 /*AND decode(p_perf_param, 'Y',
701 nvl(error_flag,'N'), ' ')
702 = decode(p_perf_param, 'Y',
703 'N', ' '); */ -- Bug 5205691
704
705 COMMIT;
706
707 UPDATE oe_lotserials_iface_all
708 SET request_id = l_request_id
709 WHERE (order_source_id, orig_sys_document_ref,
710 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
711 ( SELECT order_source_id, orig_sys_document_ref,
712 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)
713 FROM oe_headers_iface_all
714 WHERE request_id = l_request_id) ;
715 /*AND decode(p_perf_param, 'Y',
716 nvl(error_flag,'N'), ' ')
717 = decode(p_perf_param, 'Y',
718 'N', ' '); */ -- Bug 5205691
719
720 COMMIT;
721
722 UPDATE oe_reservtns_iface_all
723 SET request_id = l_request_id
724 WHERE (order_source_id, orig_sys_document_ref,
725 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
726 ( SELECT order_source_id, orig_sys_document_ref,
727 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)
728 FROM oe_headers_iface_all
729 WHERE request_id = l_request_id) ;
730 /* AND decode(p_perf_param, 'Y',
731 nvl(error_flag,'N'), ' ')
732 = decode(p_perf_param, 'Y',
733 'N', ' '); */ -- Bug 5205691
734
735 COMMIT;
736
737 UPDATE oe_actions_iface_all
738 SET request_id = l_request_id
739 WHERE (order_source_id, orig_sys_document_ref,
740 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
741 ( SELECT order_source_id, orig_sys_document_ref,
742 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)
743 FROM oe_headers_iface_all
744 WHERE request_id = l_request_id) ;
745 /* AND decode(p_perf_param, 'Y',
746 nvl(error_flag,'N'), ' ')
747 = decode(p_perf_param, 'Y',
748 'N', ' '); */ -- Bug 5205691
749
750 COMMIT;
751
752 -- end of customer-inclusive request_id updates
753
754 end if;
755
756
757 OPEN l_request_cursor;
758
759 LOOP
760
761
762 FETCH l_request_cursor
763 INTO l_order_source_id
764 ,l_orig_sys_document_ref
765 , l_sold_to_org_id
766 , l_sold_to_org
767 ,l_change_sequence
768 ,l_closed_flag
769 ,l_org_id
770 ;
771 EXIT WHEN l_request_cursor%NOTFOUND;
772
773 -- If doing throughput-enhanced imports, then set the looped_flag if it has not
774 -- been set already
775 If p_perf_param = 'Y' then
776 If l_looped_flag = 'N' then
777 IF l_debug_level > 0 THEN
778 oe_debug_pub.add('setting looped flag');
779 END IF;
780 l_looped_flag := 'Y';
781 End If;
782 End If;
783
784 IF l_debug_level > 0 THEN
785 oe_debug_pub.add('Performance Parameter: ' || p_perf_param);
786 END IF;
787
788 IF ( p_perf_param = 'Y') THEN
789
790 if l_count_header = 0 then
791
792 begin
793 select parent_request_id
794 into l_pnt_request_id
795 from fnd_concurrent_requests
796 where request_id=l_request_id;
797
798 exception
799 when others then
800 IF l_debug_level > 0 THEN
801 oe_debug_pub.add('Unexpected error: '||sqlerrm);
802 END IF;
803 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
804 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Order_Import_Conc_Pgm');
805 END IF;
806 fnd_file.put_line(FND_FILE.OUTPUT,'Unexpected error: ' || sqlerrm);
807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
808
809 end;
810
811 end if;
812
813 IF l_debug_level > 0 THEN
814 oe_debug_pub.add('first parent ' || l_pnt_request_id);
815 END IF;
816 END IF;
817
818 --if customer_key_profile <> 'Y'
819 --update the corresponding child table entries with the header-level
820 --customer/change_seq information to allow old functionality to remain
821 --unchanged
822
823 if (l_customer_key_profile <> 'Y') then
824
825
826 UPDATE oe_lines_iface_all
827 SET request_id = l_request_id,
828 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
829 sold_to_org = nvl(sold_to_org, l_sold_to_org),
830 org_id = l_org_id
831 WHERE order_source_id = l_order_source_id
832 AND orig_sys_document_ref = l_orig_sys_document_ref
833 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
834 AND nvl(org_id,l_org_id) = l_org_id ;
835 /* AND decode(p_perf_param, 'Y',
836 nvl(error_flag,'N'), ' ')
837 = decode(p_perf_param, 'Y',
838 'N', ' '); */ -- Bug 5205691
839
840
841 COMMIT;
842
843 UPDATE oe_price_adjs_iface_all
844 SET request_id = l_request_id,
845 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
846 sold_to_org = nvl(sold_to_org, l_sold_to_org),
847 org_id = l_org_id
848 WHERE order_source_id = l_order_source_id
849 AND orig_sys_document_ref = l_orig_sys_document_ref
850 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
851 AND nvl(org_id,l_org_id) = l_org_id ;
852 /* AND decode(p_perf_param, 'Y',
853 nvl(error_flag,'N'), ' ')
854 = decode(p_perf_param, 'Y',
855 'N', ' '); */ -- Bug 5205691
856
857 COMMIT;
858
859 UPDATE oe_payments_iface_all /* Bug #3419970 */
860 SET request_id = l_request_id,
861 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
862 sold_to_org = nvl(sold_to_org, l_sold_to_org),
863 org_id = l_org_id
864 WHERE order_source_id = l_order_source_id
865 AND orig_sys_document_ref = l_orig_sys_document_ref
866 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
867 AND nvl(org_id,l_org_id) = l_org_id ;
868 /* AND decode(p_perf_param, 'Y',
869 nvl(error_flag,'N'), ' ')
870 = decode(p_perf_param, 'Y',
871 'N', ' '); */ -- Bug 5205691
872
873 COMMIT;
874
875 UPDATE oe_price_atts_iface_all
876 SET request_id = l_request_id,
877 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
878 sold_to_org = nvl(sold_to_org, l_sold_to_org),
879 org_id = l_org_id
880 WHERE order_source_id = l_order_source_id
881 AND orig_sys_document_ref = l_orig_sys_document_ref
882 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
883 AND nvl(org_id,l_org_id) = l_org_id ;
884 /* AND decode(p_perf_param, 'Y',
885 nvl(error_flag,'N'), ' ')
886 = decode(p_perf_param, 'Y',
887 'N', ' '); */ -- Bug 5205691
888
889 COMMIT;
890
891 UPDATE oe_credits_iface_all
892 SET request_id = l_request_id,
893 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
894 sold_to_org = nvl(sold_to_org, l_sold_to_org),
895 org_id = l_org_id
896 WHERE order_source_id = l_order_source_id
897 AND orig_sys_document_ref = l_orig_sys_document_ref
898 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
899 AND nvl(org_id,l_org_id) = l_org_id ;
900 /* AND decode(p_perf_param, 'Y',
901 nvl(error_flag,'N'), ' ')
902 = decode(p_perf_param, 'Y',
903 'N', ' '); */ -- Bug 5205691
904
905 COMMIT;
906
907 UPDATE oe_reservtns_iface_all
908 SET request_id = l_request_id,
909 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
910 sold_to_org = nvl(sold_to_org, l_sold_to_org),
911 org_id = l_org_id
912 WHERE order_source_id = l_order_source_id
913 AND orig_sys_document_ref = l_orig_sys_document_ref
914 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
915 AND nvl(org_id,l_org_id) = l_org_id ;
916 /* AND decode(p_perf_param, 'Y',
917 nvl(error_flag,'N'), ' ')
918 = decode(p_perf_param, 'Y',
919 'N', ' '); */ -- Bug 5205691
920
921 COMMIT;
922
923 UPDATE oe_lotserials_iface_all
924 SET request_id = l_request_id,
925 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
926 sold_to_org = nvl(sold_to_org, l_sold_to_org),
927 org_id = l_org_id
928 WHERE order_source_id = l_order_source_id
929 AND orig_sys_document_ref = l_orig_sys_document_ref
930 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
931 AND nvl(org_id,l_org_id) = l_org_id ;
932 /* AND decode(p_perf_param, 'Y',
933 nvl(error_flag,'N'), ' ')
934 = decode(p_perf_param, 'Y',
935 'N', ' '); */ -- Bug 5205691
936
937 COMMIT;
938
939 UPDATE oe_actions_iface_all
940 SET request_id = l_request_id,
941 sold_to_org_id = nvl(sold_to_org_id, l_sold_to_org_id),
942 sold_to_org = nvl(sold_to_org, l_sold_to_org),
943 org_id = l_org_id
944 WHERE order_source_id = l_order_source_id
945 AND orig_sys_document_ref = l_orig_sys_document_ref
946 AND nvl(change_sequence, ' ') = nvl(l_change_sequence, ' ')
947 AND nvl(org_id,l_org_id) = l_org_id ;
948 /* AND decode(p_perf_param, 'Y',
949 nvl(error_flag,'N'), ' ')
950 = decode(p_perf_param, 'Y',
951 'N', ' '); */ -- Bug 5205691
952
953 COMMIT;
954
955 end if;
956
957 -- end of updates to customer information based on profile
958
959
960 l_count_header := l_count_header + 1;
961
962 IF l_debug_level > 0 THEN
963 oe_debug_pub.add( 'ORDER SOURCE ID: '|| TO_CHAR ( L_ORDER_SOURCE_ID ) ) ;
964 oe_debug_pub.add( 'ORIG SYS REFERENCE: '|| L_ORIG_SYS_DOCUMENT_REF ) ;
965 oe_debug_pub.add( 'CHANGE SEQUENCE: ' || L_CHANGE_SEQUENCE ) ;
966 oe_debug_pub.add( 'ORG ID: '||l_org_id);
967 oe_debug_pub.add( 'L_RETURN_STATUS: ' || L_RETURN_STATUS ) ;
968 END IF;
969
970 -- MOAC set the policy context based on the org_id
971 If G_ORG_ID IS NULL THEN
972 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);
973 G_ORG_ID := l_org_id;
974 ELSIF G_ORG_ID IS NOT NULL AND
975 G_ORG_ID <> l_org_id THEN
976 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);
977 G_ORG_ID := l_org_id;
978 END IF;
979
980
981 /* -----------------------------------------------------------
982 Call Import_Order procedure
983 -----------------------------------------------------------
984 */
985 --oe_debug_pub.add('before calling Import_Order procedure');
986
987
988 IF l_closed_flag = 'N' THEN
989 --
990 IF l_debug_level > 0 THEN
991 oe_debug_pub.add( 'BEFORE CALLING IMPORT_ORDER PROCEDURE' ) ;
992 oe_debug_pub.add('rtrim data ='||nvl(l_rtrim_data,'Null'));
993 END IF;
994 OE_ORDER_IMPORT_PVT.Import_Order (
995 p_request_id => l_request_id,
996 p_order_source_id => l_order_source_id,
997 p_orig_sys_document_ref => l_orig_sys_document_ref,
998 p_sold_to_org_id => l_sold_to_org_id,
999 p_sold_to_org => l_sold_to_org,
1000 p_change_sequence => l_change_sequence,
1001 p_org_id => l_org_id,
1002 p_validate_only => l_validate_only,
1003 p_init_msg_list => l_init_msg_list,
1004 p_rtrim_data => l_rtrim_data,
1005 p_msg_count => l_msg_count,
1006 p_msg_data => l_msg_data,
1007 p_return_status => l_return_status,
1008 p_validate_desc_flex => p_validate_desc_flex --bug4343612
1009 );
1010
1011 ELSE
1012 --
1013 IF l_debug_level > 0 THEN
1014 oe_debug_pub.add( 'BEFORE CALLING IMPORT_ORDER PROCEDURE FOR CLOSED' ) ;
1015 END IF;
1016 OE_CNCL_ORDER_IMPORT_PVT.Import_Order (
1017 p_request_id => l_request_id,
1018 p_order_source_id => l_order_source_id,
1019 p_orig_sys_document_ref => l_orig_sys_document_ref,
1020 p_sold_to_org_id => l_sold_to_org_id,
1021 p_sold_to_org => l_sold_to_org,
1022 p_change_sequence => l_change_sequence,
1023 p_org_id => l_org_id,
1024 p_validate_only => l_validate_only,
1025 p_init_msg_list => l_init_msg_list,
1026 p_msg_count => l_msg_count,
1027 p_msg_data => l_msg_data,
1028 p_return_status => l_return_status
1029 );
1030 --
1031 END IF;
1032
1033 --oe_debug_pub.add('after call Import_Order l_return_status ' || l_return_status);
1034 IF l_return_status = FND_API.G_RET_STS_ERROR OR
1035 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1036 THEN
1037 l_count_header_failure := l_count_header_failure + 1;
1038 ELSE
1039 l_count_header_success := l_count_header_success + 1;
1040 END IF;
1041
1042
1043 IF (p_perf_param = 'Y') THEN
1044
1045 IF l_debug_level > 0 THEN
1046 oe_debug_pub.add('after order_import for docref='|| l_orig_sys_document_ref || ' and request_id ' || l_request_id);
1047 oe_debug_pub.add('parent request_id:' || l_pnt_request_id);
1048 END IF;
1049
1050
1051 begin
1052 loop
1053
1054 select rowidtochar(rowid)
1055 into l_rowid
1056 from oe_headers_iface_all --MOAC
1057 where request_id = l_pnt_request_id
1058 and rownum = 1;
1059
1060 UPDATE oe_headers_iface_all --MOAC
1061 SET request_id = l_request_id
1062 WHERE request_id = l_pnt_request_id
1063 AND nvl(error_flag,'N') = 'N'
1064 AND rowidtochar(rowid) = l_rowid
1065 AND rownum = 1
1066 RETURNING orig_sys_document_ref
1067 INTO l_updated_docref;
1068
1069
1070 if sql%rowcount > 0 then
1071 IF l_debug_level > 0 THEN
1072 oe_debug_pub.add('rowcount:' || sql%rowcount || ' updated docref:' || l_updated_docref);
1073 END IF;
1074 commit;
1075 exit;
1076 end if;
1077
1078 end loop;
1079
1080 close l_request_cursor;
1081 IF l_debug_level > 0 THEN
1082 oe_debug_pub.add('after cursor closed');
1083 END IF;
1084 goto dist;
1085
1086 exception
1087 when no_data_found then
1088 IF l_debug_level > 0 THEN
1089 oe_debug_pub.add('In handled no data found exception');
1090 oe_debug_pub.add('No more records to process');
1091 END IF;
1092
1093 EXIT;
1094 when others then
1095 IF l_debug_level > 0 THEN
1096 oe_debug_pub.add('others exception when attempting update' ||sqlerrm);
1097 END IF;
1098 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1099 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Order_Import_Conc_Pgm');
1100 END IF;
1101 fnd_file.put_line(FND_FILE.OUTPUT,'Unexpected error: ' || sqlerrm);
1102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1103 end;
1104
1105 END IF; -- p_perf_param was 'Y'
1106
1107
1108 END LOOP; /* Request cursor */
1109
1110 CLOSE l_request_cursor;
1111
1112 IF l_debug_level > 0 THEN
1113 oe_debug_pub.add( 'NO. OF ORDERS FOUND: ' || L_COUNT_HEADER ) ;
1114 oe_debug_pub.add( 'NO. OF ORDERS IMPORTED: '|| L_COUNT_HEADER_SUCCESS ) ;
1115 oe_debug_pub.add( 'NO. OF ORDERS FAILED: ' || L_COUNT_HEADER_FAILURE ) ;
1116 END IF;
1117
1118 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders found: ' ||
1119 l_count_header);
1120 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders imported: '||
1121 l_count_header_success);
1122 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders failed: ' ||
1123 l_count_header_failure);
1124 fnd_file.put_line(FND_FILE.OUTPUT,'');
1125
1126
1127 /* SELECT count(*) INTO l_count_msgs
1128 FROM oe_processing_msgs_vl
1129 WHERE request_id = l_request_id;
1130
1131 IF l_count_msgs > 0 THEN
1132 fnd_file.put_line(FND_FILE.OUTPUT,'No. of messages: '||l_count_msgs);
1133 fnd_file.put_line(FND_FILE.OUTPUT,'');
1134 fnd_file.put_line(FND_FILE.OUTPUT,'Source/Order/Seq/Line Message');
1135 */
1136 /* -----------------------------------------------------------
1137 Messages
1138 -----------------------------------------------------------
1139 */
1140 IF l_debug_level > 0 THEN
1141 oe_debug_pub.add( 'BEFORE MESSAGES LOOP' ) ;
1142 END IF;
1143
1144 fnd_file.put_line(FND_FILE.OUTPUT,'');
1145 fnd_file.put_line(FND_FILE.OUTPUT,'Source/Order/Seq/Line Message');
1146 OPEN l_msg_cursor;
1147 LOOP
1148 FETCH l_msg_cursor
1149 INTO l_order_source_id
1150 , l_orig_sys_document_ref
1151 , l_change_sequence
1152 , l_orig_sys_line_ref
1153 , l_org_id --MOAC
1154 , l_message_text;
1155 EXIT WHEN l_msg_cursor%NOTFOUND;
1156
1157 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_order_source_id)
1158 ||'/'||l_orig_sys_document_ref
1159 ||'/'||l_change_sequence
1160 ||'/'||l_org_id
1161 ||'/'||l_orig_sys_line_ref
1162 ||' '||l_message_text);
1163 fnd_file.put_line(FND_FILE.OUTPUT,'');
1164 END LOOP;
1165 -- END IF;
1166
1167 END IF;
1168 /* -----------------------------------------------------------
1169 End of Order_Import_Conc_Pgm
1170 -----------------------------------------------------------
1171 */
1172 IF l_debug_level > 0 THEN
1173 oe_debug_pub.add( 'END OF ORDER IMPORT CONCURRENT PROGRAM' ) ;
1174 END IF;
1175 fnd_file.put_line(FND_FILE.OUTPUT, 'End of Order Import Concurrent Program');
1176 retcode := 0;
1177 --return;
1178
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 IF l_debug_level > 0 THEN
1182 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
1183 END IF;
1184 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1185 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Import_Order');
1186 END IF;
1187
1188 retcode := 2;
1189
1190 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders found: ' ||
1191 l_count_header);
1192 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders imported: '||
1193 l_count_header_success);
1194 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders failed: ' ||
1195 l_count_header_failure);
1196 fnd_file.put_line(FND_FILE.OUTPUT,'');
1197
1198 END ORDER_IMPORT_CONC_PGM;
1199
1200
1201 PROCEDURE ORDER_IMPORT_FORM(
1202 p_request_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
1203 ,p_order_source_id IN NUMBER
1204 ,p_orig_sys_document_ref IN VARCHAR2
1205 ,p_sold_to_org_id IN NUMBER
1206 ,p_sold_to_org IN VARCHAR2
1207 ,p_change_sequence IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
1208 ,p_org_id IN Number
1209 ,p_validate_only IN VARCHAR2 DEFAULT FND_API.G_FALSE
1210 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE
1211 ,p_rtrim_data In Varchar2
1212 ,p_msg_count OUT NOCOPY NUMBER
1213
1214 ,p_msg_data OUT NOCOPY VARCHAR2
1215
1216 ,p_return_status OUT NOCOPY VARCHAR2
1217
1218
1219 ) IS
1220
1221 l_closed_flag VARCHAR2(1) DEFAULT 'N';
1222
1223 --
1224 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1225 --
1226 BEGIN
1227
1228
1229 --MOAC set policy context for single Org
1230 IF p_org_id IS NOT NULL THEN
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add( 'Setting policy context to single-org' ) ;
1233 END IF;
1234 MO_GLOBAL.set_policy_context('S',p_org_id);
1235 END IF;
1236
1237
1238 SELECT closed_flag
1239 INTO l_closed_flag
1240 FROM oe_headers_iface_all
1241 WHERE orig_sys_document_ref = p_orig_sys_document_ref
1242 AND order_source_id = p_order_source_id
1243 AND nvl(sold_to_org_id, -999) = nvl(p_sold_to_org_id, -999)
1244 AND nvl(sold_to_org, ' ') = nvl(p_sold_to_org, ' ')
1245 AND nvl(change_sequence, ' ') = nvl(p_change_sequence, ' ')
1246 AND nvl(org_id,-99) = nvl(p_org_id,-99)
1247 AND nvl(request_id, -999) = nvl(p_request_id, -999);
1248
1249
1250 IF (NVL(l_closed_flag,'N') = 'N') THEN
1251 --
1252 IF l_debug_level > 0 THEN
1253 oe_debug_pub.add( 'BEFORE CALLING IMPORT_ORDER PROCEDURE' ) ;
1254 END IF;
1255
1256
1257 OE_ORDER_IMPORT_PVT.Import_Order (
1258 p_request_id => p_request_id,
1259 p_order_source_id => p_order_source_id,
1260 p_orig_sys_document_ref => p_orig_sys_document_ref,
1261 p_sold_to_org_id => p_sold_to_org_id,
1262 p_sold_to_org => p_sold_to_org,
1263 p_change_sequence => p_change_sequence,
1264 p_org_id => p_org_id,
1265 p_validate_only => p_validate_only,
1266 p_init_msg_list => p_init_msg_list,
1267 p_rtrim_data => p_rtrim_data,
1268 p_msg_count => p_msg_count,
1269 p_msg_data => p_msg_data,
1270 p_return_status => p_return_status
1271 );
1272 --
1273
1274 ELSE
1275 --
1276 IF l_debug_level > 0 THEN
1277 oe_debug_pub.add( 'BEFORE CALLING IMPORT_ORDER PROCEDURE FOR CLOSED' ) ;
1278 END IF;
1279
1280
1281 OE_CNCL_ORDER_IMPORT_PVT.Import_Order (
1282 p_request_id => p_request_id,
1283 p_order_source_id => p_order_source_id,
1284 p_orig_sys_document_ref => p_orig_sys_document_ref,
1285 p_sold_to_org_id => p_sold_to_org_id,
1286 p_sold_to_org => p_sold_to_org,
1287 p_change_sequence => p_change_sequence,
1288 p_org_id => p_org_id,
1289 p_validate_only => p_validate_only,
1290 p_init_msg_list => p_init_msg_list,
1291 p_msg_count => p_msg_count,
1292 p_msg_data => p_msg_data,
1293 p_return_status => p_return_status
1294 );
1295 --
1296 END IF;
1297
1298 END ORDER_IMPORT_FORM;
1299
1300 END OE_ORDER_IMPORT_MAIN_PVT;