[Home] [Help]
PACKAGE BODY: APPS.CE_UK_VALIDATIONS_DATA_LDR
Source
1 PACKAGE BODY CE_UK_VALIDATIONS_DATA_LDR AS
2 /* $Header: ceukvldb.pls 120.0.12020000.6 2013/02/28 12:34:28 vnetan noship $ */
3
4 -- package exceptions
5 invalid_file_exception EXCEPTION;
6 loader_exception EXCEPTION;
7
8 -- package constants
9 G_ldr_program CONSTANT VARCHAR2(11) := 'CEUKSQLD';
10 G_ctl_file CONSTANT VARCHAR2(11) := 'CEUKVLDL';
11 G_debug_proc VARCHAR2(32);
12
13 /* Procedure to log messages to FND_LOG */
14 PROCEDURE log_msg(
15 p_text IN VARCHAR2,
16 p_log_level IN NUMBER DEFAULT FND_LOG.LEVEL_PROCEDURE
17 )IS
18 BEGIN
19 IF(p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
20 THEN
21 FND_LOG.STRING(
22 p_log_level,
23 'ce.plsql.CE_UK_VALIDATIONS_DATA_LDR.'||G_debug_proc,
24 p_text);
25 END IF;
26 END log_msg;
27
28 /* Procedures to write tags and elements to XML */
29 PROCEDURE put_starttag(p_tag_name IN VARCHAR2)
30 IS
31 BEGIN
32 fnd_file.put_line(fnd_file.output, '<'||p_tag_name||'>');
33 END put_starttag;
34 PROCEDURE put_endtag(p_tag_name IN VARCHAR2)
35 IS
36 BEGIN
37 fnd_file.put_line(fnd_file.output, '</'||p_tag_name||'>');
38 END put_endtag;
39 PROCEDURE put_element(p_tag_name IN VARCHAR2,
40 p_value IN VARCHAR2)
41 IS
42 BEGIN
43 fnd_file.put_line(fnd_file.output,
44 '<'||p_tag_name||'>'||
45 replace(replace(p_value,'&','&'||'amp;'),'<','&'||'lt;')||
46 '</'||p_tag_name||'>');
47 END put_element;
48
49 /* ---------------------------------------------------------------------
50 | PRIVATE PROCEDURE |
51 | Register_Loader_Program |
52 | |
53 | DESCRIPTION |
54 | Create executable and concurrent program definition for loading |
55 | data from text file into the INTERFACE tables. If the |
56 | executable/program already exists, create will be skipped. |
57 | |
58 | HISTORY |
59 | 29-Jan-2013 rtumati Created |
60 --------------------------------------------------------------------- */
61 FUNCTION Register_Loader_Program
62 RETURN VARCHAR2 IS
63 BEGIN
64 G_debug_proc := 'Register_Loader_Program';
65 log_msg('Begin Register_Loader_Program');
66
67 -- Create the SQL*Loader Executable using ctl file.
68 IF NOT FND_PROGRAM.executable_exists(G_ldr_program,'CE')
69 THEN
70 log_msg('Creating SQL*Loader executable-'||G_ctl_file);
71 FND_PROGRAM.executable(
72 executable => G_ctl_file || ': Executable for SQL*loader',
73 application => 'CE',
74 short_name => G_ldr_program,
75 execution_method => 'SQL*Loader',
76 execution_file_name => G_ctl_file);
77
78 ELSE
79 log_msg(G_ldr_program||' Executable exists. Skipping Create');
80 END IF;
81
82 -- Register the concurrent program.
83 IF NOT FND_PROGRAM.program_exists(G_ldr_program,'CE')
84 THEN
85 log_msg('Creating Concurrent Program - '||G_ctl_file);
86 FND_PROGRAM.register(
87 program => G_ctl_file || ': Run SQL*loader',
88 application => 'CE',
89 enabled => 'Y',
90 short_name => G_ldr_program,
91 executable_short_name => G_ldr_program,
92 executable_application => 'CE',
93 use_in_srs => 'N');
94 ELSE
95 log_msg(G_ldr_program||' Concurrent program exists. Skipping Create');
96 END IF;
97
98 log_msg('End Register_Loader_Program');
99 RETURN FND_API.G_ret_sts_success;
100 EXCEPTION
101 WHEN OTHERS THEN
102 log_msg('EXCEPTION: '|| fnd_program.message());
103 log_msg('EXCEPTION: '|| sqlerrm);
104 RETURN FND_API.G_ret_sts_error;
105 END Register_Loader_Program;
106
107 /* ---------------------------------------------------------------------
108 | PUBLIC PROCEDURE |
109 | validate_sub_codes |
110 | |
111 | DESCRIPTION |
112 | This procedure validates substitution sort codes |
113 | |
114 | HISTORY |
115 | 18-Dec-2012 rtumati Created |
116 ----------------------------------------------------------------------*/
117 PROCEDURE validate_sub_codes IS
118 BEGIN
119 G_debug_proc := 'validate_sub_codes';
120 log_msg('begin validate_sub_codes');
121
122 FOR c_sub_codes IN (
123 SELECT INTERFACE_LINE_ID, SOURCE_VALUE1, SOURCE_VALUE2, SOURCE_VALUE3
124 FROM CE_UK_VALIDATIONS_INTERFACE
125 ORDER BY INTERFACE_LINE_ID)
126 LOOP
127 /* Check if ORIG_SORT_CODE is numeric and is of correct length*/
128 IF (NOT regexp_like(c_sub_codes.SOURCE_VALUE1,'^[0-9]*$') OR
129 LENGTH(NVL(c_sub_codes.SOURCE_VALUE1,'0')) <> 6)
130 THEN
131 log_msg('--> ORIG_SORT_CODE is invalid -Rec no:'||c_sub_codes.INTERFACE_LINE_ID);
132 RAISE invalid_file_exception;
133 END IF;
134
135 /* Check if SUBS_SORT_CODE is numeric*/
136 IF (NOT regexp_like(c_sub_codes.SOURCE_VALUE2,'^[0-9]*$') OR
137 LENGTH(NVL(c_sub_codes.SOURCE_VALUE2,'0')) <> 6)
138 THEN
139 log_msg('--> SUBS_SORT_CODE is invalid -Rec no:'||c_sub_codes.INTERFACE_LINE_ID);
140 RAISE invalid_file_exception;
141 END IF;
142
143 /* Ensure file only contains substitute sort codes */
144 IF (c_sub_codes.SOURCE_VALUE3 IS NOT NULL)
145 THEN
146 log_msg('--> Invalid data found -Rec no:'||c_sub_codes.INTERFACE_LINE_ID);
147 RAISE invalid_file_exception;
148 END IF;
149 END LOOP;
150 log_msg('end validate_sub_codes');
151 END validate_sub_codes;
152
153 /* ---------------------------------------------------------------------
154 | PUBLIC PROCEDURE |
155 | transfer_sub_codes |
156 | |
157 | DESCRIPTION |
158 | This procedure Transfer substitute sort codes from interface |
159 | table to actual table CE_UK_VAL_SUBS_CODES |
160 | CALLED BY |
161 | |
162 | HISTORY |
163 | 18-Dec-2012 rtumati Created |
164 --------------------------------------------------------------------- */
165 PROCEDURE transfer_sub_codes IS
166 BEGIN
167 G_debug_proc := 'transfer_sub_codes';
168 log_msg('begin transfer_sub_codes');
169
170 -- Delete data from CE_UK_VAL_SUBS_CODES table.
171 log_msg('Removing existing data from table');
172 DELETE FROM CE_UK_VAL_SUBS_CODES;
173 log_msg(SQL%Rowcount||' record(s) deleted.');
174
175 -- Insert data into CE_UK_VAL_SUBS_CODES table.
176 log_msg('Inserting new data into table');
177 INSERT INTO CE_UK_VAL_SUBS_CODES(
178 SUBSTITUTION_ID,
179 ORIG_SORT_CODE,
180 SUBS_SORT_CODE,
181 REQUEST_ID,
182 CREATED_BY,
183 CREATION_DATE,
184 LAST_UPDATED_BY,
185 LAST_UPDATE_DATE,
186 LAST_UPDATE_LOGIN)
187 SELECT
188 INTERFACE_LINE_ID,
189 SOURCE_VALUE1,
190 SOURCE_VALUE2,
191 FND_GLOBAL.conc_request_id,
192 FND_GLOBAL.user_id,
193 sysdate,
194 FND_GLOBAL.user_id,
195 sysdate,
196 FND_GLOBAL.user_id
197 FROM
198 CE_UK_VALIDATIONS_INTERFACE;
199 log_msg(SQL%Rowcount||' record(s) inserted.');
200
201 log_msg('end transfer_sub_codes');
202
203 END transfer_sub_codes;
204
205 /* ---------------------------------------------------------------------
206 | PUBLIC PROCEDURE |
207 | List_sub_codes |
208 | |
209 | DESCRIPTION |
210 | This procedure lists the substitute sort codes stored in the |
211 | table CE_UK_VAL_SUBS_CODES after succesful load |
212 | |
213 | HISTORY |
214 | 18-Dec-2012 rtumati Created |
215 --------------------------------------------------------------------- */
216 PROCEDURE list_sub_codes IS
217 BEGIN
218 G_debug_proc := 'List_sub_codes';
219 log_msg('begin List_sub_codes');
220
221 put_starttag('subTableData');
222
223 FOR subsCodeRec IN
224 (SELECT
225 SUBSTITUTION_ID,
226 ORIG_SORT_CODE,
227 SUBS_SORT_CODE,
228 LAST_UPDATE_DATE
229 FROM CE_UK_VAL_SUBS_CODES ORDER BY SUBSTITUTION_ID)
230 LOOP
231 log_msg('Listing SUBSTITUTION_ID='||subsCodeRec.SUBSTITUTION_ID);
232
233 put_starttag('subsCodeRec');
234 put_element('fromCode',subsCodeRec.ORIG_SORT_CODE);
235 put_element('toCode',subsCodeRec.SUBS_SORT_CODE);
236 put_element('loadDate',to_char(subsCodeRec.LAST_UPDATE_DATE,
237 FND_PROFILE.value('ICX_DATE_FORMAT_MASK')));
238 put_endtag('subsCodeRec');
239 END LOOP;
240
241 put_endtag('subTableData');
242
243 log_msg('end List_sub_codes');
244 END List_sub_codes;
245
246 /* ---------------------------------------------------------------------
247 | PUBLIC PROCEDURE |
248 | validate_modulus_weights |
249 | |
250 | DESCRIPTION |
251 | Validate modulus weights data being loaded |
252 | |
253 | HISTORY |
254 | 18-Dec-2012 rtumati Created |
255 --------------------------------------------------------------------- */
256 PROCEDURE validate_modulus_weights IS
257 BEGIN
258 G_debug_proc := 'validate_modulus_weights';
259 log_msg('begin validate_modulus_weights');
260
261 FOR c_modulus_weights IN
262 (SELECT
263 INTERFACE_LINE_ID,
264 SOURCE_VALUE1,
265 SOURCE_VALUE2,
266 SOURCE_VALUE3,
267 SOURCE_VALUE4,
268 SOURCE_VALUE5,
269 SOURCE_VALUE6,
270 SOURCE_VALUE7,
271 SOURCE_VALUE8,
272 SOURCE_VALUE9,
273 SOURCE_VALUE10,
274 SOURCE_VALUE11,
275 SOURCE_VALUE12,
276 SOURCE_VALUE13,
277 SOURCE_VALUE14,
278 SOURCE_VALUE15,
279 SOURCE_VALUE16,
280 SOURCE_VALUE17,
281 SOURCE_VALUE18
282 FROM
283 CE_UK_VALIDATIONS_INTERFACE
284 ORDER BY
285 INTERFACE_LINE_ID)
286 LOOP
287 /* Check if FROM_SORT_CODE is numeric and length is ok */
288 IF (NOT regexp_like(c_modulus_weights.SOURCE_VALUE1,'^[0-9]*$')
289 OR LENGTH(nvl(c_modulus_weights.SOURCE_VALUE1,'0')) <> 6)
290 THEN
291 log_msg('--> FROM_SORT_CODE is invalid -Rec no:'||c_modulus_weights.INTERFACE_LINE_ID);
292 RAISE invalid_file_exception;
293 END IF;
294
295 /* Check if TO_SORT_CODE and length is ok */
296 IF (NOT regexp_like(c_modulus_weights.SOURCE_VALUE2,'^[0-9]*$')
297 OR LENGTH(nvl(c_modulus_weights.SOURCE_VALUE2,'0')) <> 6)
298 THEN
299 log_msg('--> TO_SORT_CODE is invalid -Rec no:'||c_modulus_weights.INTERFACE_LINE_ID);
300 RAISE invalid_file_exception;
301 END IF;
302
303 /* Validate CHECK_TYPE */
304 IF(nvl(c_modulus_weights.SOURCE_VALUE3,'X') NOT IN ('MOD10', 'MOD11', 'DBLAL'))
305 THEN
306 log_msg('--> Invalid Check Type -Rec no:'||c_modulus_weights.INTERFACE_LINE_ID);
307 RAISE invalid_file_exception;
308 END IF;
309
310 /* validating the weights range */
311 BEGIN
312 IF (NVL(c_modulus_weights.SOURCE_VALUE4 ,-1000) NOT BETWEEN -999 AND 999
313 OR NVL(c_modulus_weights.SOURCE_VALUE5 ,-1000) NOT BETWEEN -999 AND 999
314 OR NVL(c_modulus_weights.SOURCE_VALUE6 ,-1000) NOT BETWEEN -999 AND 999
315 OR NVL(c_modulus_weights.SOURCE_VALUE7 ,-1000) NOT BETWEEN -999 AND 999
316 OR NVL(c_modulus_weights.SOURCE_VALUE8 ,-1000) NOT BETWEEN -999 AND 999
317 OR NVL(c_modulus_weights.SOURCE_VALUE9 ,-1000) NOT BETWEEN -999 AND 999
318 OR NVL(c_modulus_weights.SOURCE_VALUE10,-1000) NOT BETWEEN -999 AND 999
319 OR NVL(c_modulus_weights.SOURCE_VALUE11,-1000) NOT BETWEEN -999 AND 999
320 OR NVL(c_modulus_weights.SOURCE_VALUE12,-1000) NOT BETWEEN -999 AND 999
321 OR NVL(c_modulus_weights.SOURCE_VALUE13,-1000) NOT BETWEEN -999 AND 999
322 OR NVL(c_modulus_weights.SOURCE_VALUE14,-1000) NOT BETWEEN -999 AND 999
323 OR NVL(c_modulus_weights.SOURCE_VALUE15,-1000) NOT BETWEEN -999 AND 999
324 OR NVL(c_modulus_weights.SOURCE_VALUE16,-1000) NOT BETWEEN -999 AND 999
325 OR NVL(c_modulus_weights.SOURCE_VALUE17,-1000) NOT BETWEEN -999 AND 999)
326 THEN
327 RAISE invalid_file_exception;
328 END IF;
329 EXCEPTION
330 WHEN OTHERS THEN
331 log_msg('--> Invalid Modulus Weights -Rec no:'||c_modulus_weights.INTERFACE_LINE_ID);
332 RAISE invalid_file_exception;
333 END;
334
335 /* validating EXCEPTION_CODE if present */
336 IF( nvl(c_modulus_weights.SOURCE_VALUE18, 0) NOT BETWEEN 0 AND 999)
337 THEN
338 log_msg('--> Invalid Exception Code -Rec no:'||c_modulus_weights.INTERFACE_LINE_ID);
339 RAISE invalid_file_exception;
340 END IF;
341 END LOOP;
342 log_msg('end validate_modulus_weights');
343 END validate_modulus_weights;
344
345
346 /* ---------------------------------------------------------------------
347 | PUBLIC PROCEDURE |
348 | transfer_modulus_weights |
349 | |
350 | DESCRIPTION |
351 | Transfer modulus weights from interface table to actual table |
352 | CE_UK_VAL_MODULUS_WEIGHTS |
353 | |
354 | HISTORY |
355 | 18-Dec-2012 rtumati Created |
356 --------------------------------------------------------------------- */
357 PROCEDURE transfer_modulus_weights IS
358 BEGIN
359 G_debug_proc := 'transfer_modulus_weights';
360 log_msg('begin transfer_modulus_weights');
361
362 --
363 -- Delete data from CE_UK_VAL_MODULUS_WEIGHTS table.
364 --
365 log_msg('Removing existing data from table');
366 DELETE FROM CE_UK_VAL_MODULUS_WEIGHTS;
367 log_msg(SQL%Rowcount||' record(s) deleted.');
368
369 --
370 -- Insert data into CE_UK_VAL_MODULUS_WEIGHTS table.
371 --
372 log_msg('Inserting new data into table');
373 INSERT INTO CE_UK_VAL_MODULUS_WEIGHTS(
374 MODULUS_CHECK_ID,
375 FROM_SORT_CODE ,
376 TO_SORT_CODE,
377 CHECK_TYPE ,
378 WEIGHT_U ,
379 WEIGHT_V ,
380 WEIGHT_W ,
381 WEIGHT_X ,
382 WEIGHT_Y ,
383 WEIGHT_Z ,
384 WEIGHT_A ,
385 WEIGHT_B ,
386 WEIGHT_C ,
387 WEIGHT_D ,
388 WEIGHT_E ,
389 WEIGHT_F ,
390 WEIGHT_G ,
391 WEIGHT_H ,
392 EXCEPTION_CODE,
393 REQUEST_ID,
394 CREATED_BY,
395 CREATION_DATE,
396 LAST_UPDATED_BY,
397 LAST_UPDATE_DATE,
398 LAST_UPDATE_LOGIN)
399 SELECT
400 INTERFACE_LINE_ID,
401 SOURCE_VALUE1,
402 SOURCE_VALUE2 ,
403 SOURCE_VALUE3 ,
404 SOURCE_VALUE4 ,
405 SOURCE_VALUE5 ,
406 SOURCE_VALUE6 ,
407 SOURCE_VALUE7 ,
408 SOURCE_VALUE8 ,
409 SOURCE_VALUE9 ,
410 SOURCE_VALUE10,
411 SOURCE_VALUE11,
412 SOURCE_VALUE12,
413 SOURCE_VALUE13,
414 SOURCE_VALUE14,
415 SOURCE_VALUE15,
416 SOURCE_VALUE16,
417 SOURCE_VALUE17,
418 SOURCE_VALUE18,
419 FND_GLOBAL.conc_request_id,
420 FND_GLOBAL.user_id,
421 sysdate,
422 FND_GLOBAL.user_id,
423 sysdate,
424 FND_GLOBAL.user_id
425 FROM
426 CE_UK_VALIDATIONS_INTERFACE;
427 log_msg(SQL%Rowcount||' record(s) inserted.');
428
429 log_msg('end transfer_modulus_weights');
430 END transfer_modulus_weights;
431
432 /* ---------------------------------------------------------------------
433 | PUBLIC PROCEDURE |
434 | List_modulus_weights |
435 | |
436 | DESCRIPTION |
437 | This procedure lists Modulus Weights Data stored in the table |
438 | CE_UK_VAL_MODULUS_WEIGHTS after succesful load |
439 | |
440 | HISTORY |
441 | 18-Dec-2012 rtumati Created |
442 --------------------------------------------------------------------- */
443 PROCEDURE List_modulus_weights IS
444 BEGIN
445 G_debug_proc := 'List_modulus_weights';
446 log_msg('begin List_modulus_weights');
447
448 put_starttag('modTableData');
449
450 FOR modWeightRec IN
451 (SELECT
452 MODULUS_CHECK_ID,
453 FROM_SORT_CODE,
454 TO_SORT_CODE,
455 CHECK_TYPE,
456 WEIGHT_U, WEIGHT_V, WEIGHT_W, WEIGHT_X,
457 WEIGHT_Y, WEIGHT_Z, WEIGHT_A, WEIGHT_B,
458 WEIGHT_C, WEIGHT_D, WEIGHT_E, WEIGHT_F,
459 WEIGHT_G, WEIGHT_H,
460 EXCEPTION_CODE,
461 LAST_UPDATE_DATE
462 FROM
463 CE_UK_VAL_MODULUS_WEIGHTS ORDER BY MODULUS_CHECK_ID)
464 LOOP
465 log_msg('Listing MODULUS_CHECK_ID='||modWeightRec.MODULUS_CHECK_ID);
466
467 put_starttag('modWeightRec');
468 put_element('fromCode',modWeightRec.FROM_SORT_CODE);
469 put_element('toCode',modWeightRec.TO_SORT_CODE);
470 put_element('checkType',modWeightRec.CHECK_TYPE);
471 put_element('weightU',modWeightRec.WEIGHT_U);
472 put_element('weightV',modWeightRec.WEIGHT_V);
473 put_element('weightW',modWeightRec.WEIGHT_W);
474 put_element('weightX',modWeightRec.WEIGHT_X);
475 put_element('weightY',modWeightRec.WEIGHT_Y);
476 put_element('weightZ',modWeightRec.WEIGHT_Z);
477 put_element('weightA',modWeightRec.WEIGHT_A);
478 put_element('weightB',modWeightRec.WEIGHT_B);
479 put_element('weightC',modWeightRec.WEIGHT_C);
480 put_element('weightD',modWeightRec.WEIGHT_D);
481 put_element('weightE',modWeightRec.WEIGHT_E);
482 put_element('weightF',modWeightRec.WEIGHT_F);
483 put_element('weightG',modWeightRec.WEIGHT_G);
484 put_element('weightH',modWeightRec.WEIGHT_H);
485 put_element('exceptionCode',modWeightRec.EXCEPTION_CODE);
486 put_element('loadDate',to_char(modWeightRec.LAST_UPDATE_DATE,
487 FND_PROFILE.value('ICX_DATE_FORMAT_MASK')));
488 put_endtag('modWeightRec');
489 END LOOP;
490
491 put_endtag('modTableData');
492
493 log_msg('end List_modulus_weights');
494 END List_modulus_weights;
495
496 /* ---------------------------------------------------------------------
497 | PUBLIC PROCEDURE |
498 | Call_Sql_Loader |
499 | |
500 | DESCRIPTION |
501 | This procedure is the main loading program which spwans the |
502 | SQL*Loader program. |
503 | |
504 | HISTORY |
505 | 18-Dec-2012 rtumati Created |
506 --------------------------------------------------------------------- */
507 PROCEDURE Call_Sql_Loader(
508 errbuf OUT NOCOPY VARCHAR2,
509 retcode OUT NOCOPY NUMBER,
510 X_file_type IN VARCHAR2,
511 X_directory_path IN VARCHAR2,
512 X_input_file IN VARCHAR2
513 )IS
514 l_cnt NUMBER;
515 l_data_file VARCHAR2(240);
516
517 l_request_id NUMBER;
518 l_return BOOLEAN;
519 l_phase VARCHAR2(30);
520 l_status VARCHAR2(30);
521 l_dev_phase VARCHAR2(30);
522 l_dev_status VARCHAR2(30);
523 l_message VARCHAR2(1000);
524
525 l_encoding VARCHAR2(20);
526 l_req_data VARCHAR2(20);
527 l_file_type_meaning VARCHAR2(80);
528 BEGIN
529 G_debug_proc := 'Call_Sql_Loader';
530 log_msg('begin Call_Sql_Loader');
531
532 -- using request data to fetch context after sub-request completes
533 -- l_req_data will be NULL when program starts running and after
534 -- PAUSE it will have request_id of child program (SQL*Loader)
535 l_req_data := FND_CONC_GLOBAL.request_data;
536
537
538 IF (l_req_data IS NULL)
539 THEN
540 --
541 -- Program is being run for the first time. Write initial data to output
542 -- and spawn child request to run SQL*loader.
543 --
544 -- Write program parameters to output file
545 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
546 put_starttag('?xml version="1.0" encoding="'||l_encoding||'"?');
547 put_starttag('ukValLoader');
548 put_element('reportType','EXEC');
549 put_element('requestId',FND_GLOBAL.conc_request_id);
550 put_element('requestDate',to_char(SYSDATE,FND_PROFILE.value('ICX_DATE_FORMAT_MASK')));
551 put_element('filePath',X_directory_path);
552 put_element('fileName',X_input_file);
553 put_element('fileTypeCode',X_file_type);
554
555 BEGIN
556 SELECT meaning
557 INTO l_file_type_meaning
558 FROM ce_lookups
559 WHERE lookup_type = 'CE_MODULUS_FILETYPE_UK'
560 AND lookup_code = X_File_Type;
561 EXCEPTION
562 WHEN NO_DATA_FOUND THEN
563 log_msg('Lookup type CE_MODULUS_FILETYPE_UK not defined');
564 l_file_type_meaning := 'UNDEFINED';
565 END;
566 put_element('fileTypeMeaning',l_file_type_meaning);
567
568 -- For modulus weights data the weight column header has to
569 -- be written to output xml as standards do not allow for headers
570 -- of 1 character length in the RTF template.
571 IF x_file_type = 'MOD_WEIGHTS'
572 THEN
573 put_starttag('columnHeaders');
574 put_element('hdrU','U');
575 put_element('hdrV','V');
576 put_element('hdrW','W');
577 put_element('hdrX','X');
578 put_element('hdrY','Y');
579 put_element('hdrZ','Z');
580 put_element('hdrA','A');
581 put_element('hdrB','B');
582 put_element('hdrC','C');
583 put_element('hdrD','D');
584 put_element('hdrE','E');
585 put_element('hdrF','F');
586 put_element('hdrG','G');
587 put_element('hdrH','H');
588 put_endtag('columnHeaders');
589 END IF;
590
591 -- Reformat the input file name
592 -- local_chr(92) = '\' character, local_chr(47) = '/' character
593 IF (INSTR(X_directory_path, FND_GLOBAL.local_chr(92)) <> 0 )
594 THEN
595 l_data_file := X_directory_path || FND_GLOBAL.local_chr(92) || X_input_file;
596 ELSE
597 l_data_file := X_directory_path || FND_GLOBAL.local_chr(47) || X_input_file;
598 END IF;
599
600 --
601 -- Call SQL*Loader to load data into interface table
602 --
603 -- If it doesn't exist, the oader program has to be registered first.
604 IF (Register_Loader_Program <> FND_API.G_ret_sts_success)
605 THEN
606 log_msg('ERROR: Could not register SQL*loader');
607 RAISE loader_exception;
608 ELSE
609 log_msg('Loader program registered successfully.');
610 END IF;
611
612 -- Submit child request to execute SQL*Loader
613 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
614 application => 'CE',
615 program => G_ldr_program,
616 sub_request => TRUE,
617 argument1 => l_data_file,
618 argument2 => fnd_global.local_chr(0));
619
620 log_msg('-->Loading datafile:'||l_data_file||', request_id='||l_request_id);
621 IF nvl(l_request_id,0) = 0
622 THEN
623 log_msg('ERROR: Concurrent request for SQL*Loader not submitted');
624 log_msg(FND_MESSAGE.get);
625 RAISE loader_exception;
626 ELSE
627 -- child request has been submitted. Pause Parent request and
628 -- set request_data context
629 log_msg('-->Submitted Child Request Id:'||l_request_id);
630 FND_CONC_GLOBAL.SET_REQ_GLOBALS(
631 conc_status => 'PAUSED',
632 request_data => FND_NUMBER.NUMBER_TO_CANONICAL(l_request_id));
633 END IF;
634 log_msg('Child submitted. Main program will sleep now');
635 --
636 -- parent program will re-run after child completes. In this case,
637 -- l_req_data is not null
638 --
639 ELSE
640 -- check status of child request.
641 l_request_id := FND_NUMBER.NUMBER_TO_CANONICAL(l_req_data);
642 l_return := FND_CONCURRENT.get_request_status(
643 request_id => l_request_id,
644 appl_shortname => 'CE',
645 program => G_ldr_program,
646 phase => l_phase,
647 status => l_status,
648 dev_phase => l_dev_phase,
649 dev_status => l_dev_status,
650 message => l_message);
651
652 log_msg('l_dev_phase='||l_dev_phase||', l_dev_status='||l_dev_status);
653
654 -- Check if sql*loader has completed successfully and loaded records
655 SELECT count(*)
656 INTO l_cnt
657 FROM CE_UK_VALIDATIONS_INTERFACE;
658
659 IF NOT (l_dev_phase = 'COMPLETE' AND l_dev_status = 'NORMAL')
660 THEN
661 log_msg('Error in SQL*loader: l_message='||l_message);
662 RAISE loader_exception;
663 ELSIF l_cnt = 0
664 THEN
665 log_msg('No records loaded in interface table');
666 RAISE invalid_file_exception;
667 ELSE
668 -- OK to proceed
669 log_msg(l_cnt||' records loaded by SQL*Loader.');
670 END IF;
671
672 -- Check all records from file were loaded into the table
673 -- If file has corruptions/not as per expected format then all the
674 -- records will not be loaded into the table.
675 SELECT MAX(INTERFACE_LINE_ID) - COUNT(*)
676 INTO l_cnt
677 FROM CE_UK_VALIDATIONS_INTERFACE;
678
679 IF l_cnt <> 0
680 THEN
681 log_msg('All records from data file were not loaded.');
682 log_msg('Missing records='||ABS(l_cnt));
683 RAISE invalid_file_exception;
684 END IF;
685
686 -- Check the concurrent program submitted is for Modulus weights
687 -- or Substitute sort Codes
688 IF( X_file_type = 'MOD_WEIGHTS')
689 THEN
690 -- Validate modulus weights data being loaded
691 validate_modulus_weights;
692
693 -- Transfer modulus weights from interface table to actual table
694 -- CE_UK_VAL_MODULUS_WEIGHTS
695 transfer_modulus_weights;
696
697 -- Write data to XML
698 List_modulus_weights;
699
700 ELSIF ( X_file_type = 'SUB_CODES' )
701 THEN
702 -- Validate substitute sort codes data being loaded
703 validate_sub_codes;
704
705 -- Transfer substitute sort codes from interface table to
706 -- actual table CE_UK_VAL_SUBS_CODES
707 transfer_sub_codes;
708
709 -- Write data to XML
710 list_sub_codes;
711 END IF;
712
713 log_msg('Data has been transfered. Committing changes.');
714 COMMIT;
715
716 put_element('loadSuccessful','Y');
717 put_endtag('ukValLoader');
718
719 -- all changes completed successfully. set retcode to 0 (success)
720 retcode := 0;
721 END IF;
722 log_msg('end Call_Sql_Loader');
723
724 EXCEPTION
725 WHEN invalid_file_exception THEN
726 -- file input was not valid. set retcode to 1(warning)
727 retcode := 1;
728 log_msg('EXCEPTION: invalid_file_exception - Invalid data found in file');
729
730 put_element('loadError','INVALID_FILE');
731 put_element('loadSuccessful','N');
732 put_endtag('ukValLoader');
733
734 ROLLBACK;
735 WHEN loader_exception THEN
736 -- loader_exception occured. set retcode to 1(Warning)
737 retcode := 1;
738 log_msg('EXCEPTION: loader_exception - SQL*loader did not complete successfully');
739
740 put_element('loadError','EXCEPTION');
741 put_element('loadSuccessful','N');
742 put_endtag('ukValLoader');
743
744 ROLLBACK;
745 WHEN OTHERS THEN
746 -- other exception occured. set retcode to 2(Error)
747 retcode := 2;
748 log_msg('EXCEPTION: unexpected error');
749 log_msg(sqlerrm);
750
751 put_element('loadError','EXCEPTION');
752 put_element('loadSuccessful','N');
753 put_endtag('ukValLoader');
754
755 ROLLBACK;
756 END Call_Sql_Loader;
757
758 END CE_UK_VALIDATIONS_DATA_LDR;