1 PACKAGE BODY GCS_LEX_MAP_API_PKG as
2 /* $Header: gcslmapb.pls 120.1 2005/10/30 05:19:00 appldev noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7
8 -- Used to store the constant 'gcs_rv_' which is used as the return value
9 -- variable in functions that are dynamically created in here.
10 g_ret_val CONSTANT VARCHAR2(10) := 'gcs_rv_';
11
12 -- Used to store the constant 'gcs_drv_' which is used as a local variable
13 -- to hold the derivation number in the materialized function.
14 g_deriv_num CONSTANT VARCHAR2(10) := 'gcs_drv_';
15
16 -- Name of this API package.
17 g_api CONSTANT VARCHAR2(30) := 'gcs.plsql.GCS_LEX_MAP_API_PKG';
18
19 -- Action types for writing module information to the log file. Used for
20 -- the procedure log_file_module_write.
21 g_module_enter CONSTANT VARCHAR2(1) := 'E';
22 g_module_success CONSTANT VARCHAR2(1) := 'S';
23 g_module_failure CONSTANT VARCHAR2(1) := 'F';
24
25 -- various error codes
26 -- Error Descriptions:
27 -- EX01 - Lookup table derivation returned more than one row
28 -- EX02 - Lookup table derivation returned no rows
29 -- EX03 - Type mismatch occurred (type conversion error)
30 -- EX04 - Value set validation failed
31 -- EX05 - Lookup table validation failed
32
33 -- EX99 - Unexpected error occurred.
34
35 g_error_lookup_tmr CONSTANT VARCHAR2(4) := 'EX01';
36 g_error_lookup_ndf CONSTANT VARCHAR2(4) := 'EX02';
37 g_error_type_mismatch CONSTANT VARCHAR2(4) := 'EX03';
38 g_error_vsv_failed CONSTANT VARCHAR2(4) := 'EX04';
39 g_error_lutv_failed CONSTANT VARCHAR2(4) := 'EX05';
40
41 g_error_unexpected CONSTANT VARCHAR2(4) := 'EX99';
42
43
44 --
45 -- PRIVATE EXCEPTIONS
46 --
47
48 GCS_LEX_INVALID_RULE_SET EXCEPTION;
49 GCS_LEX_FILTER_ERROR EXCEPTION;
50 GCS_LEX_FILTER_COLUMN_NOT_RO EXCEPTION;
51 GCS_LEX_SET_NO_STAGE EXCEPTION;
52 GCS_LEX_STAGE_NO_RULE EXCEPTION;
53 GCS_LEX_INVALID_LIST_CODE EXCEPTION;
54 GCS_LEX_PARAM_LIST_FAILED EXCEPTION;
55 GCS_LEX_CREATE_COND_FAILED EXCEPTION;
56 GCS_LEX_DERIVATION_FAILED EXCEPTION;
57 GCS_LEX_DEF_COND_NOT_LAST EXCEPTION;
58 GCS_LEX_RULE_NO_DERIVATION EXCEPTION;
59 GCS_LEX_READ_ONLY_COLUMN_RULE EXCEPTION;
60 GCS_LEX_DISABLED EXCEPTION;
61 GCS_LEX_UNEXPECTED_ERROR EXCEPTION;
62 GCS_LEX_INIT_FAILED EXCEPTION;
63 GCS_LEX_FUNC_FAILURE EXCEPTION;
64 GCS_LEX_INVALID_VALID_CODE EXCEPTION;
65 GCS_LEX_INVALID_VALUE_SET EXCEPTION;
66 GCS_LEX_INVALID_VALUE_SET_ID EXCEPTION;
67 GCS_LEX_INVALID_TV_VALUE_SET EXCEPTION;
68 GCS_LEX_FAIL_VS_VALIDATION EXCEPTION;
69 GCS_LEX_FAIL_LUT_VALIDATION EXCEPTION;
70 GCS_LEX_NO_FILTER_COLUMN_NAME EXCEPTION;
71 GCS_LEX_NO_FILTER_VALUE EXCEPTION;
72 GCS_LEX_STAGE_FAILED EXCEPTION;
73 GCS_LEX_VALIDATION_FAILED EXCEPTION;
74 GCS_LEX_NO_ERROR_COLUMN EXCEPTION;
75 GCS_LEX_MULT_ERROR_COLUMNS EXCEPTION;
76 GCS_LEX_INVALID_FILTER_COLUMN EXCEPTION;
77 GCS_LEX_TABLE_CHECK_FAILED EXCEPTION;
78 GCS_LEX_VALID_CHECK_FAILED EXCEPTION;
79 GCS_LEX_LUT_NO_LOOKUP_CODE EXCEPTION;
80 GCS_LEX_VDATION_LUT_NOT_META EXCEPTION;
81 GCS_LEX_ERROR_COLUMN_NOT_SET EXCEPTION;
82 GCS_LEX_ERROR_COL_WRITE EXCEPTION;
83 GCS_LEX_NUM_ROWS_CHANGED EXCEPTION;
84 GCS_LEX_NO_VALIDATION_LUT EXCEPTION;
85 GCS_LEX_RULE_NO_FUNC EXCEPTION;
86 GCS_LEX_APPLSYS_NOT_FOUND EXCEPTION;
87 GCS_LEX_FUNC_FAILED EXCEPTION;
88 GCS_LEX_FUNC_NOT_REGISTERED EXCEPTION;
89
90 GCS_LEX_VRS_NO_ROWS EXCEPTION;
91 GCS_LEX_VRS_RULE_FAILED EXCEPTION;
92
93 --
94 -- PRIVATE PROCEDURES/FUNCTIONS
95 --
96
97 --
98 -- Procedure
99 -- QSort_Error_Table
100 -- Purpose
101 -- Quick sorts the error table
102 -- Arguments
103 -- Low Bottom index
104 -- High Top index
105 -- Example
106 -- GCS_LEX_MAP_PKG.QSort_Error_Table(1, 5);
107 -- Notes
108 --
109 PROCEDURE QSort_Error_Table( Low NUMBER,
110 High NUMBER) IS
111 low_counter NUMBER;
112 high_counter NUMBER;
113
114 -- for swapping
115 temp_row error_record_type;
116
117 -- for storing pivot information
118 pivot_row error_record_type;
119 BEGIN
120 low_counter := low;
121 high_counter := high;
122 IF low >= high THEN
123 return;
124 END IF;
125
126 pivot_row := error_table(trunc((low+high)/2));
127
128 -- Get the pivot from the center of the array to the front
129 error_table(trunc((low+high)/2)) := error_table(low);
130 error_table(low) := pivot_row;
131
132 LOOP
133 WHILE low_counter < high AND
134 ((error_table(low_counter).rule_id < pivot_row.rule_id) OR
135 (error_table(low_counter).rule_id = pivot_row.rule_id AND
136 error_table(low_counter).deriv_num < pivot_row.deriv_num) OR
137 (error_table(low_counter).rule_id = pivot_row.rule_id AND
138 error_table(low_counter).deriv_num = pivot_row.deriv_num AND
139 error_table(low_counter).error_code < pivot_row.error_code) OR
140 (error_table(low_counter).rule_id = pivot_row.rule_id AND
141 error_table(low_counter).deriv_num = pivot_row.deriv_num AND
142 error_table(low_counter).error_code = pivot_row.error_code AND
143 error_table(low_counter).row_id <= pivot_row.row_id)) LOOP
144 low_counter := low_counter + 1;
145 END LOOP;
146 WHILE high_counter > low AND
147 ((error_table(high_counter).rule_id > pivot_row.rule_id) OR
148 (error_table(high_counter).rule_id = pivot_row.rule_id AND
149 error_table(high_counter).deriv_num > pivot_row.deriv_num) OR
150 (error_table(high_counter).rule_id = pivot_row.rule_id AND
151 error_table(high_counter).deriv_num = pivot_row.deriv_num AND
152 error_table(high_counter).error_code > pivot_row.error_code) OR
153 (error_table(high_counter).rule_id = pivot_row.rule_id AND
154 error_table(high_counter).deriv_num = pivot_row.deriv_num AND
155 error_table(high_counter).error_code = pivot_row.error_code AND
156 error_table(high_counter).row_id > pivot_row.row_id)) LOOP
157 high_counter := high_counter - 1;
158 END LOOP;
159 EXIT WHEN low_counter >= high_counter;
160
161 -- swap the high and low.
162 temp_row := error_table(low_counter);
163 error_table(low_counter) := error_table(high_counter);
164 error_table(high_counter) := temp_row;
165 END LOOP;
166
167 -- Put the pivot row into the correct place.
168 error_table(low) := error_table(high_counter);
169 error_table(high_counter) := pivot_row;
170
171 -- Quick Sort the two sub-arrays
172 qsort_error_table(low, high_counter-1);
173 qsort_error_table(high_counter+1, high);
174 EXCEPTION
175 WHEN OTHERS THEN
176 null;
177 END QSort_Error_Table;
178
179 --
180 -- Procedure
181 -- Write_Header_Output
182 -- Purpose
183 -- Writes header information for the output execution report.
184 -- Arguments
185 -- Idt_Name The transformer to use in this API call.
186 -- Staging_Table Staging table where the data is stored.
187 -- Filter_Text The filter criteria.
188 -- Example
189 -- GCS_LEX_MAP_PKG.Write_Header_Output;
190 -- Notes
191 --
192 PROCEDURE Write_Header_Output( Idt_Name VARCHAR2,
193 Staging_Table VARCHAR2,
194 Filter_Text VARCHAR2) IS
195 BEGIN
196 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_HEADER');
197 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
198 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
199
200 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_IDT');
201 FND_MESSAGE.set_token('IDT_NAME', idt_name);
202 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
203
204 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_STG_TBL');
205 FND_MESSAGE.set_token('STG_TBL', staging_table);
206 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
207
208 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_FILTER');
209 FND_MESSAGE.set_token('FILTER_TEXT', filter_text);
210 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
211
212 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_REQ_ID');
213 FND_MESSAGE.set_token('REQ_ID', fnd_global.conc_request_id);
214 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
215 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
216
217 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_SEPARATOR');
218 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
219 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
220 END Write_Header_Output;
221
222
223 --
224 -- Procedure
225 -- Write_Tail_Output
226 -- Purpose
227 -- Writes tail information for the output execution report.
228 -- Arguments
229 --
230 -- Example
231 -- GCS_LEX_MAP_PKG.Write_Tail_Output;
232 -- Notes
233 --
234 PROCEDURE Write_Tail_Output IS
235 BEGIN
236 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
237 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
238 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
239 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
240
241 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_TAIL');
242 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
243
244 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_TAIL_SEPARATOR');
245 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
246
247 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_EX01_LEGEND');
248 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
249
250 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_EX02_LEGEND');
251 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
252
253 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_EX03_LEGEND');
254 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
255
256 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_EX04_LEGEND');
257 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
258
259 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_EX05_LEGEND');
260 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
261
262 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_EX99_LEGEND');
263 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
264 END Write_Tail_Output;
265
266
267 --
268 -- Procedure
269 -- Write_Header_Log
270 -- Purpose
271 -- Writes header information for the log file.
272 -- Arguments
273 -- Idt_Name The transformer to use in this API call.
274 -- Staging_Table Staging table where the data is stored.
275 -- Filter_Text The filter criteria.
276 -- Example
277 -- GCS_LEX_MAP_PKG.Write_Header_Log(idt,'GL_INTERFACE','group_id=''11''');
278 -- Notes
279 --
280 PROCEDURE Write_Header_Log( Idt_Name VARCHAR2,
281 Staging_Table VARCHAR2,
282 Filter_Text VARCHAR2) IS
283 BEGIN
284 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XL_IDT');
285 FND_MESSAGE.set_token('IDT_NAME', idt_name);
286 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.get);
287
288 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XL_STG_TBL');
289 FND_MESSAGE.set_token('STG_TBL', staging_table);
290 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.get);
291
292 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XL_FILTER');
293 FND_MESSAGE.set_token('FILTER_TEXT', filter_text);
294 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.get);
295 END Write_Header_Log;
296
297 --
298 -- Procedure
299 -- Log_File_Module_Write
300 -- Purpose
301 -- Adds a line to the log file saying that we have entered, exited with
302 -- success, or exited with failure from a module of our API. However, it
303 -- strips out the API name that always comes at the front, since that is
304 -- implied by the log file you are reading.
305 -- Arguments
306 -- Module Name of the Module.
307 -- Action_Type Entered, Exited Successfully, or Exited with Failure.
308 -- Example
309 -- GCS_LEX_MAP_PKG.Log_File_Module_Write('GCS_LEX_MAP_PKG.apply_map', 'E');
310 -- Notes
311 --
312 PROCEDURE Log_File_Module_Write( Module VARCHAR2,
313 Action_Type VARCHAR2)
314 IS
315 enter_exit_text VARCHAR2(10);
316 BEGIN
317 IF action_type = g_module_enter THEN
318 FND_FILE.NEW_LINE(FND_FILE.LOG);
319 enter_exit_text := '>>';
320 ELSIF action_type = g_module_success THEN
321 enter_exit_text := '<<';
322 ELSE
323 enter_exit_text := '<x';
324 END IF;
325
326 FND_FILE.PUT_LINE(
327 FND_FILE.LOG,
328 enter_exit_text || ' ' ||
329 SUBSTR(module, LENGTH(g_api)+2, LENGTH(module)-LENGTH(g_api)-1) ||
330 '() ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
331
332 IF action_type <> g_module_enter THEN
333 FND_FILE.NEW_LINE(FND_FILE.LOG);
334 END IF;
335
336 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT THEN
337 FND_LOG.string(FND_LOG.LEVEL_EVENT, module,
338 enter_exit_text || ' ' ||
339 SUBSTR(module, LENGTH(g_api)+2, LENGTH(module)-LENGTH(g_api)-1) ||
340 '() ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
341 END IF;
342 END Log_File_Module_Write;
343
344 --
345 -- Procedure
346 -- Write_To_Log
347 -- Purpose
348 -- Adds a message to both the log file and log repository, if appropriate.
349 -- It uses the module passed in for the log repository. It will write to
350 -- the log file if the File_Write parameter is set to 'Y'. The assumption
351 -- is that the message has already been built by the fnd_message API.
352 -- Arguments
353 -- Module Name of the Module that failed.
354 -- File_Write 'Y' if writing to a log file.
355 -- Example
356 -- GCS_LEX_MAP_PKG.Write_To_Log('GCS_LEX_DERIV_FAIL');
357 -- Notes
358 --
359 PROCEDURE Write_To_Log( Module VARCHAR2,
360 File_Write VARCHAR2)
361 IS
362 encoded_message VARCHAR2(32767);
363 BEGIN
364 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED THEN
365 FND_LOG.message(FND_LOG.LEVEL_UNEXPECTED, module);
366 END IF;
367 IF File_Write = 'Y' THEN
368 encoded_message := FND_MESSAGE.get_encoded;
369 FND_MESSAGE.set_encoded(encoded_message);
370 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.get);
371 FND_MESSAGE.set_encoded(encoded_message);
372 END IF;
373 FND_MSG_PUB.add;
374 END Write_To_Log;
375
376 --
377 -- Procedure
378 -- Add_Deriv_Proc_Failed_Msg
379 -- Purpose
380 -- Adds a message that the derivation given has failed during processing.
381 -- The exact message to show is given by the message name. The message must
382 -- have four tokens: IDT_NAME for transformer name, STAGE_NUM for stage
383 -- number, COL_NAME for column of the rule, and DERIV_NUM for derivation
384 -- sequence number. There is one exception, and that is when the deriv_num
385 -- passed into this procedure is -1. In that case, there should not be a
386 -- DERIV_NUM token.
387 -- Arguments
388 -- Rule_Id ID of the rule that failed.
389 -- Deriv_Num Sequence number of the derivation that failed.
390 -- Message_Name Name of the message to be added.
391 -- Module Name of the Module that failed.
392 -- File_Write 'Y' if writing to a log file.
393 -- Example
394 -- GCS_LEX_MAP_PKG.Add_Deriv_Proc_Failed_Msg(123, 3, 'GCS_IDT_DERIV_FAIL');
395 -- Notes
396 --
397 PROCEDURE Add_Deriv_Proc_Failed_Msg( Rule_Id NUMBER,
398 Deriv_Num NUMBER,
399 Message_Name VARCHAR2,
400 Module VARCHAR2,
401 File_Write VARCHAR2)
402 IS
403 idt_name VARCHAR2(100);
404 stage_num NUMBER;
405 col_name VARCHAR2(100);
406 BEGIN
407 SELECT rst.name,
408 rstg.stage_number,
409 mc.column_name
410 INTO idt_name,
411 stage_num,
412 col_name
413 FROM gcs_lex_map_rule_sets rst,
414 gcs_lex_map_rule_stages rstg,
415 gcs_lex_map_rules r,
416 gcs_lex_map_columns mc
417 WHERE rst.rule_set_id = rstg.rule_set_id
418 AND rstg.rule_stage_id = r.rule_stage_id
419 AND r.target_column_id = mc.column_id
420 AND r.rule_id = add_deriv_proc_failed_msg.rule_id;
421
422 FND_MESSAGE.set_name('GCS', message_name);
423 FND_MESSAGE.set_token('IDT_NAME', idt_name);
424 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
425 FND_MESSAGE.set_token('COL_NAME', col_name);
426 IF deriv_num <> -1 THEN
427 FND_MESSAGE.set_token('DERIV_NUM', deriv_num);
428 END IF;
429 Write_To_Log(module, file_write);
430 END Add_Deriv_Proc_Failed_Msg;
431
432 --
433 -- Procedure
434 -- Add_Deriv_Failed_Msg
435 -- Purpose
436 -- Adds a message that the derivation given has failed during processing.
437 -- The exact message to show is given by the message name. The message must
438 -- have four tokens: IDT_NAME for transformer name, STAGE_NUM for stage
439 -- number, COL_NAME for column of the rule, and DERIV_NUM for derivation
440 -- sequence number.
441 -- Arguments
442 -- Derivation_Id ID of the derivation that failed.
443 -- Message_Name Name of the message to be added.
444 -- Module Name of the Module that failed.
445 -- File_Write 'Y' if writing to a log file.
446 -- Example
447 -- GCS_LEX_MAP_PKG.Add_Deriv_Failed_Msg(12345, 'GCS_IDT_ERROR', 'ABC_PKG');
448 -- Notes
449 --
450 PROCEDURE Add_Deriv_Failed_Msg( Derivation_Id NUMBER,
451 Message_Name VARCHAR2,
452 Module VARCHAR2,
453 File_Write VARCHAR2)
454 IS
455 idt_name VARCHAR2(100);
456 stage_num NUMBER;
457 col_name VARCHAR2(100);
458 deriv_num NUMBER;
459 BEGIN
460 SELECT rst.name,
461 rstg.stage_number,
462 mc.column_name,
463 d.derivation_sequence
464 INTO idt_name,
465 stage_num,
466 col_name,
467 deriv_num
468 FROM gcs_lex_map_rule_sets rst,
469 gcs_lex_map_rule_stages rstg,
470 gcs_lex_map_rules r,
471 gcs_lex_map_derivations d,
472 gcs_lex_map_columns mc
473 WHERE rst.rule_set_id = rstg.rule_set_id
474 AND rstg.rule_stage_id = r.rule_stage_id
475 AND r.rule_id = d.rule_id
476 AND r.target_column_id = mc.column_id
477 AND d.derivation_id = add_deriv_failed_msg.derivation_id;
478
479 FND_MESSAGE.set_name('GCS', message_name);
480 FND_MESSAGE.set_token('IDT_NAME', idt_name);
481 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
482 FND_MESSAGE.set_token('COL_NAME', col_name);
483 FND_MESSAGE.set_token('DERIV_NUM', deriv_num);
484 Write_To_Log(module, file_write);
485 END Add_Deriv_Failed_Msg;
486
487 --
488 -- Procedure
489 -- Add_PLSQL_Deriv_Failed_Msg
490 -- Purpose
491 -- Adds a message that the derivation given has failed during processing.
492 -- The exact message to show is given by the message name. The message must
493 -- have five tokens: IDT_NAME for transformer name, STAGE_NUM for stage
494 -- number, COL_NAME for column of the rule, DERIV_NUM for derivation
495 -- sequence number, and FUNC_NAME for the PL/SQL function name.
496 -- Arguments
497 -- Derivation_Id ID of the derivation that failed.
498 -- Message_Name Name of the message to be added.
499 -- Module Name of the Module that failed.
500 -- File_Write 'Y' if writing to a log file.
501 -- Example
502 -- GCS_LEX_MAP_PKG.Add_PLSQL_Deriv_Failed_Msg(1, 'GCS_IDT_ERR', 'ABC_PKG');
503 -- Notes
504 --
505 PROCEDURE Add_PLSQL_Deriv_Failed_Msg( Derivation_Id NUMBER,
506 Message_Name VARCHAR2,
507 Module VARCHAR2,
508 File_Write VARCHAR2)
509 IS
510 idt_name VARCHAR2(100);
511 stage_num NUMBER;
512 col_name VARCHAR2(100);
513 deriv_num NUMBER;
514 func_name VARCHAR2(100);
515 BEGIN
516 SELECT rst.name,
517 rstg.stage_number,
518 mc.column_name,
519 d.derivation_sequence,
520 d.function_name
521 INTO idt_name,
522 stage_num,
523 col_name,
524 deriv_num,
525 func_name
526 FROM gcs_lex_map_rule_sets rst,
527 gcs_lex_map_rule_stages rstg,
528 gcs_lex_map_rules r,
529 gcs_lex_map_derivations d,
530 gcs_lex_map_columns mc
531 WHERE rst.rule_set_id = rstg.rule_set_id
532 AND rstg.rule_stage_id = r.rule_stage_id
533 AND r.rule_id = d.rule_id
534 AND r.target_column_id = mc.column_id
535 AND d.derivation_id = add_plsql_deriv_failed_msg.derivation_id;
536
537 FND_MESSAGE.set_name('GCS', message_name);
538 FND_MESSAGE.set_token('IDT_NAME', idt_name);
539 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
540 FND_MESSAGE.set_token('COL_NAME', col_name);
541 FND_MESSAGE.set_token('DERIV_NUM', deriv_num);
542 FND_MESSAGE.set_token('FUNC_NAME', func_name);
543 Write_To_Log(module, file_write);
544 END Add_PLSQL_Deriv_Failed_Msg;
545
546 --
547 -- Procedure
548 -- Add_Rule_Failed_Msg
549 -- Purpose
550 -- Adds a message that the rule given failed during processing. The message
551 -- itself is determined by the message name passed in. It must be a message
552 -- for 'GCS', and must have three tokens: IDT_NAME for the transformer
553 -- name, STAGE_NUM for the stage number within that transformer, and
554 -- COL_NAME for the column of that rule.
555 -- Arguments
556 -- Rule_Id ID of the rule that failed.
557 -- Message_Name Name of the message to be added.
558 -- Module Name of the Module that failed.
559 -- File_Write 'Y' if writing to a log file.
560 -- Example
561 -- GCS_LEX_MAP_PKG.Add_Rule_Failed_Msg(11111, 'GCS_IDT_RULE_FAILED');
562 -- Notes
563 --
564 PROCEDURE Add_Rule_Failed_Msg(Rule_Id NUMBER,
565 Message_Name VARCHAR2,
566 Module VARCHAR2,
567 File_Write VARCHAR2)
568 IS
569 idt_name VARCHAR2(100);
570 stage_num NUMBER;
571 col_name VARCHAR2(100);
572 BEGIN
573 SELECT rst.name,
574 rstg.stage_number,
575 mc.column_name
576 INTO idt_name,
577 stage_num,
578 col_name
579 FROM gcs_lex_map_rule_sets rst,
580 gcs_lex_map_rule_stages rstg,
581 gcs_lex_map_rules r,
582 gcs_lex_map_columns mc
583 WHERE rst.rule_set_id = rstg.rule_set_id
584 AND rstg.rule_stage_id = r.rule_stage_id
585 AND r.target_column_id = mc.column_id
586 AND r.rule_id = add_rule_failed_msg.rule_id;
587
588 FND_MESSAGE.set_name('GCS', message_name);
589 FND_MESSAGE.set_token('IDT_NAME', idt_name);
590 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
591 FND_MESSAGE.set_token('COL_NAME', col_name);
592 Write_To_Log(module, file_write);
593 END Add_Rule_Failed_Msg;
594
595 --
596 -- Procedure
597 -- Add_VRS_Rule_Failed_Msg
598 -- Purpose
599 -- Adds a message that the rule given failed during validation. The message
600 -- itself is determined by the message name passed in. It must be a message
601 -- for 'GCS', and must have one token: RULE_NAME for the rule name.
602 -- Arguments
603 -- Rule_Name Name of the rule that failed.
604 -- Message_Name Name of the message to be added.
605 -- Module Name of the Module that failed.
606 -- File_Write 'Y' if writing to a log file.
607 -- Example
608 -- GCS_LEX_MAP_PKG.Add_VRS_Rule_Failed_Msg('name', 'GCS_IDT_RULE_FAILED');
609 -- Notes
610 --
611 PROCEDURE Add_VRS_Rule_Failed_Msg( Rule_Name VARCHAR2,
612 Message_Name VARCHAR2,
613 Module VARCHAR2,
614 File_Write VARCHAR2)
615 IS
616 BEGIN
617 FND_MESSAGE.set_name('GCS', message_name);
618 FND_MESSAGE.set_token('RULE_NAME', rule_name);
619 Write_To_Log(module, file_write);
620 END Add_VRS_Rule_Failed_Msg;
621
622 --
623 -- Procedure
624 -- Add_Stage_Failed_Msg
625 -- Purpose
626 -- Adds a message that the stage failed during processing. The message
627 -- itself is determined by the message name passed in. It must be a message
628 -- for 'GCS', and must have two tokens: IDT_NAME for the transformer name
629 -- and STAGE_NUM for the stage number within that transformer.
630 -- Arguments
631 -- Rule_Stage_Id ID of the stage that failed.
632 -- Message_Name Name of the message to be added.
633 -- Module Name of the Module that failed.
634 -- File_Write 'Y' if writing to a log file.
635 -- Example
636 -- GCS_LEX_MAP_PKG.Add_Stage_Failed_Msg(111, 'GCS_IDT_RULE_FAILED');
637 -- Notes
638 --
639 PROCEDURE Add_Stage_Failed_Msg( Rule_Stage_Id NUMBER,
640 Message_Name VARCHAR2,
641 Module VARCHAR2,
642 File_Write VARCHAR2)
643 IS
644 idt_name VARCHAR2(100);
645 stage_num NUMBER;
646 BEGIN
647 SELECT rst.name,
648 rstg.stage_number
649 INTO idt_name,
650 stage_num
651 FROM gcs_lex_map_rule_sets rst,
652 gcs_lex_map_rule_stages rstg
653 WHERE rst.rule_set_id = rstg.rule_set_id
654 AND rstg.rule_stage_id = add_stage_failed_msg.rule_stage_id;
655
656 FND_MESSAGE.set_name('GCS', message_name);
657 FND_MESSAGE.set_token('IDT_NAME', idt_name);
658 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
659 Write_To_Log(module, file_write);
660 END Add_Stage_Failed_Msg;
661
662 --
663 -- Procedure
664 -- Add_Rows_Changed_Msg
665 -- Purpose
666 -- Adds a message that the stage given processed a different number of rows
667 -- than the previous stage. The message itself is determined by the message
668 -- message name passed in. It must be a message for 'GCS', and must have
669 -- four tokens: IDT_NAME for the transformer name, STAGE_NUM for the stage
670 -- number, PREV_ROWS for the previous number of rows, and CURR_ROWS for the
671 -- rows affected by the current stage.
672 -- Arguments
673 -- Rule_Stage_Id ID of the stage that failed.
674 -- Current_Rows Number of rows just processed.
675 -- Previous_Rows Number of rows previously processed.
676 -- Message_Name Name of the message to be added.
677 -- Module Name of the Module that failed.
678 -- File_Write 'Y' if writing to a log file.
679 -- Example
680 -- GCS_LEX_MAP_PKG.Add_Rows_Changed_Msg(...);
681 -- Notes
682 --
683 PROCEDURE Add_Rows_Changed_Msg( Rule_Stage_Id NUMBER,
684 Current_Rows NUMBER,
685 Previous_Rows NUMBER,
686 Message_Name VARCHAR2,
687 Module VARCHAR2,
688 File_Write VARCHAR2)
689 IS
690 idt_name VARCHAR2(100);
691 stage_num NUMBER;
692 BEGIN
693 SELECT rst.name,
694 rstg.stage_number
695 INTO idt_name,
696 stage_num
697 FROM gcs_lex_map_rule_sets rst,
698 gcs_lex_map_rule_stages rstg
699 WHERE rst.rule_set_id = rstg.rule_set_id
700 AND rstg.rule_stage_id = add_rows_changed_msg.rule_stage_id;
701
702 FND_MESSAGE.set_name('GCS', message_name);
703 FND_MESSAGE.set_token('IDT_NAME', idt_name);
704 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
705 FND_MESSAGE.set_token('CURR_ROWS', current_rows);
706 FND_MESSAGE.set_token('PREV_ROWS', previous_rows);
707 Write_To_Log(module, file_write);
708 END Add_Rows_Changed_Msg;
709
710 --
711 -- Procedure
712 -- Add_IDT_Failed_Msg
713 -- Purpose
714 -- Adds a message that the IDT given failed during processing. The message
715 -- itself is determined by the message name passed in. It must be a message
716 -- for 'GCS', and must have one token: IDT_NAME for the transformer name.
717 -- Arguments
718 -- Rule_Set_Id ID of the IDT that failed
719 -- Message_Name Name of the message to be added.
720 -- Module Name of the Module that failed.
721 -- File_Write 'Y' if writing to a log file.
722 -- Example
723 -- GCS_LEX_MAP_PKG.Add_Stage_Failed_Msg(111, 'GCS_IDT_RULE_FAILED');
724 -- Notes
725 --
726 PROCEDURE Add_IDT_Failed_Msg( Rule_Set_Id NUMBER,
727 Message_Name VARCHAR2,
728 Module VARCHAR2,
729 File_Write VARCHAR2)
730 IS
731 idt_name VARCHAR2(100);
732 BEGIN
733 SELECT rst.name
734 INTO idt_name
735 FROM gcs_lex_map_rule_sets rst
736 WHERE rst.rule_set_id = add_idt_failed_msg.rule_set_id;
737
738 FND_MESSAGE.set_name('GCS', message_name);
739 FND_MESSAGE.set_token('IDT_NAME', idt_name);
740 Write_To_Log(module, file_write);
741 END Add_IDT_Failed_Msg;
742
743 --
744 -- Procedure
745 -- Add_Structure_Failed_Msg
746 -- Purpose
747 -- Adds a message that the structure for the IDT given failed during
748 -- processing. The message itself is determined by the message name passed
749 -- in. It must be a message for 'GCS', and must have one token: STRUCT_NAME
750 -- for the structure name.
751 -- Arguments
752 -- Rule_Set_Id ID of the IDT for which the structure failed
753 -- Message_Name Name of the message to be added.
754 -- Module Name of the Module that failed.
755 -- File_Write 'Y' if writing to a log file.
756 -- Example
757 -- GCS_LEX_MAP_PKG.Add_Stage_Failed_Msg(111, 'GCS_IDT_RULE_FAILED');
758 -- Notes
759 --
760 PROCEDURE Add_Structure_Failed_Msg( Rule_Set_Id NUMBER,
761 Message_Name VARCHAR2,
762 Module VARCHAR2,
763 File_Write VARCHAR2)
764 IS
765 struct_name VARCHAR2(100);
766 BEGIN
767 SELECT ms.structure_name
768 INTO struct_name
769 FROM gcs_lex_map_rule_sets rst,
770 gcs_lex_map_structs ms
771 WHERE rst.structure_id = ms.structure_id
772 AND rst.rule_set_id = add_structure_failed_msg.rule_set_id;
773
774 FND_MESSAGE.set_name('GCS', message_name);
775 FND_MESSAGE.set_token('STRUCT_NAME', struct_name);
776 Write_To_Log(module, file_write);
777 END Add_Structure_Failed_Msg;
778
779 --
780 -- Procedure
781 -- Add_Value_Set_Failed_Msg
782 -- Purpose
783 -- Adds a message that the value set for the given rule failed. The message
784 -- itself is determined by the message name passed in. It must be a message
785 -- for 'GCS', and must have four tokens: VS_NAME for the value set name,
786 -- COL_NAME for the rule column, STAGE_NUM for the stage number, and
787 -- IDT_NAME for the transformer name.
788 -- Arguments
789 -- Rule_Id ID of the rule for which the value set failed.
790 -- Message_Name Name of the message to be added.
791 -- Module Name of the Module that failed.
792 -- File_Write 'Y' if writing to a log file.
793 -- Example
794 -- GCS_LEX_MAP_PKG.Add_Value_Set_Failed_Msg(111, 'GCS_IDT_RULE_FAILED');
795 -- Notes
796 --
797 PROCEDURE Add_Value_Set_Failed_Msg( Rule_Id NUMBER,
798 Message_Name VARCHAR2,
799 Module VARCHAR2,
800 File_Write VARCHAR2)
801 IS
802 idt_name VARCHAR2(100);
803 stage_num NUMBER;
804 col_name VARCHAR2(100);
805 vs_name VARCHAR2(100);
806 BEGIN
807 SELECT rst.name,
808 rstg.stage_number,
809 mc.column_name,
810 ffvs.flex_value_set_name
811 INTO idt_name,
812 stage_num,
813 col_name,
814 vs_name
815 FROM gcs_lex_map_rule_sets rst,
816 gcs_lex_map_rule_stages rstg,
817 gcs_lex_map_rules r,
818 gcs_lex_map_columns mc,
819 fnd_flex_value_sets ffvs
820 WHERE rst.rule_set_id = rstg.rule_set_id
821 AND rstg.rule_stage_id = r.rule_stage_id
822 AND r.target_column_id = mc.column_id
823 AND r.value_set_id = ffvs.flex_value_set_id
824 AND r.rule_id = add_value_set_failed_msg.rule_id;
825
826 FND_MESSAGE.set_name('GCS', message_name);
827 FND_MESSAGE.set_token('IDT_NAME', idt_name);
828 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
829 FND_MESSAGE.set_token('COL_NAME', col_name);
830 FND_MESSAGE.set_token('VS_NAME', vs_name);
831 Write_To_Log(module, file_write);
832 END Add_Value_Set_Failed_Msg;
833
834 --
835 -- Procedure
836 -- Add_Rule_LUT_Failed_Msg
837 -- Purpose
838 -- Adds a message that the lookup table for the given rule failed. The
839 -- message itself is determined by the message name passed in. It must be a
840 -- message for 'GCS', and must have five tokens: LUT_NAME for the lookup
841 -- table name, COL_NAME for the rule column, STAGE_NUM for the stage
842 -- number, and IDT_NAME for the transformer name.
843 -- Arguments
844 -- Rule_Id ID of the rule for which the lookup table failed.
845 -- Message_Name Name of the message to be added.
846 -- Module Name of the Module that failed.
847 -- File_Write 'Y' if writing to a log file.
848 -- Example
849 -- GCS_LEX_MAP_PKG.Add_Rule_LUT_Failed_Msg(111, 'GCS_IDT_RULE_FAILED');
850 -- Notes
851 --
852 PROCEDURE Add_Rule_LUT_Failed_Msg( Rule_Id NUMBER,
853 Message_Name VARCHAR2,
854 Module VARCHAR2,
855 File_Write VARCHAR2)
856 IS
857 idt_name VARCHAR2(100);
858 stage_num NUMBER;
859 col_name VARCHAR2(100);
860 lut_name VARCHAR2(100);
861 BEGIN
862 SELECT rst.name,
863 rstg.stage_number,
864 mc.column_name,
865 lutms.structure_name
866 INTO idt_name,
867 stage_num,
868 col_name,
869 lut_name
870 FROM gcs_lex_map_rule_sets rst,
871 gcs_lex_map_rule_stages rstg,
872 gcs_lex_map_rules r,
873 gcs_lex_map_columns mc,
874 gcs_lex_map_structs lutms
875 WHERE rst.rule_set_id = rstg.rule_set_id
876 AND rstg.rule_stage_id = r.rule_stage_id
877 AND r.target_column_id = mc.column_id
878 AND r.lookup_table_id = lutms.structure_id
879 AND r.rule_id = add_rule_lut_failed_msg.rule_id;
880
881 FND_MESSAGE.set_name('GCS', message_name);
882 FND_MESSAGE.set_token('IDT_NAME', idt_name);
883 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
884 FND_MESSAGE.set_token('COL_NAME', col_name);
885 FND_MESSAGE.set_token('LUT_NAME', lut_name);
886 Write_To_Log(module, file_write);
887 END Add_Rule_LUT_Failed_Msg;
888
889 --
890 -- Procedure
891 -- Add_Deriv_LUT_Failed_Msg
892 -- Purpose
893 -- Adds a message that the lookup table for the given derivation failed.
894 -- The message itself is determined by the message name passed in. It must
895 -- be a message for 'GCS', and must have five tokens: LUT_NAME for the
896 -- lookup table name, DERIV_NUM for the derivation number, COL_NAME for the
897 -- rule column, STAGE_NUM for the stage number, and IDT_NAME for the
898 -- transformer name.
899 -- Arguments
900 -- Derivation_Id ID of the rule for which the lookup table failed.
901 -- Message_Name Name of the message to be added.
902 -- Module Name of the Module that failed.
903 -- File_Write 'Y' if writing to a log file.
904 -- Example
905 -- GCS_LEX_MAP_PKG.Add_Deriv_LUT_Failed_Msg(111, 'GCS_IDT_RULE_FAILED');
906 -- Notes
907 --
908 PROCEDURE Add_Deriv_LUT_Failed_Msg( Derivation_Id NUMBER,
909 Message_Name VARCHAR2,
910 Module VARCHAR2,
911 File_Write VARCHAR2)
912 IS
913 idt_name VARCHAR2(100);
914 stage_num NUMBER;
915 col_name VARCHAR2(100);
916 deriv_num NUMBER;
917 lut_name VARCHAR2(100);
918 BEGIN
919 SELECT rst.name,
920 rstg.stage_number,
921 mc.column_name,
922 d.derivation_sequence,
923 lutms.structure_name
924 INTO idt_name,
925 stage_num,
926 col_name,
927 deriv_num,
928 lut_name
929 FROM gcs_lex_map_rule_sets rst,
930 gcs_lex_map_rule_stages rstg,
931 gcs_lex_map_rules r,
932 gcs_lex_map_derivations d,
933 gcs_lex_map_columns mc,
934 gcs_lex_map_columns lutmc,
935 gcs_lex_map_structs lutms
936 WHERE rst.rule_set_id = rstg.rule_set_id
937 AND rstg.rule_stage_id = r.rule_stage_id
938 AND r.rule_id = d.rule_id
939 AND r.target_column_id = mc.column_id
940 AND d.lookup_result_column_id = lutmc.column_id
941 AND lutmc.structure_id = lutms.structure_id
942 AND d.derivation_id = add_deriv_lut_failed_msg.derivation_id;
943
944 FND_MESSAGE.set_name('GCS', message_name);
945 FND_MESSAGE.set_token('IDT_NAME', idt_name);
946 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
947 FND_MESSAGE.set_token('COL_NAME', col_name);
948 FND_MESSAGE.set_token('DERIV_NUM', deriv_num);
949 FND_MESSAGE.set_token('LUT_NAME', lut_name);
950 Write_To_Log(module, file_write);
951 END Add_Deriv_LUT_Failed_Msg;
952
953 --
954 -- Procedure
955 -- Add_ID_Value_Failed_Msg
956 -- Purpose
957 -- Adds a message that something failed during processing. The message
958 -- itself is determined by the message name passed in. It must be a
959 -- message for 'GCS', and must have one token: IDNUM for the id.
960 -- Arguments
961 -- Id_Value ID value that failed.
962 -- Message_Name Name of the message to be added.
963 -- Module Name of the Module that failed.
964 -- File_Write 'Y' if writing to a log file.
965 -- Example
966 -- GCS_LEX_MAP_PKG.Add_ID_Value_Failed_Msg(111, 'GCS_IDT_RULE_FAILED');
967 -- Notes
968 --
969 PROCEDURE Add_ID_Value_Failed_Msg( Id_Value NUMBER,
970 Message_Name VARCHAR2,
971 Module VARCHAR2,
972 File_Write VARCHAR2)
973 IS
974 BEGIN
975 FND_MESSAGE.set_name('GCS', message_name);
976 FND_MESSAGE.set_token('IDNUM', id_value);
977 Write_To_Log(module, file_write);
978 END Add_ID_Value_Failed_Msg;
979
980 --
981 -- Procedure
982 -- Add_Code_Value_Failed_Msg
983 -- Purpose
984 -- Adds a message that a code value is invalid. The message iself is
985 -- determined by the message name passed in. It must be a message for
986 -- 'GCS', and must have one token: CODE_VAL for the code value.
987 -- Arguments
988 -- Code_Value Code value that failed.
989 -- Message_Name Name of the message to be added.
990 -- Module Name of the Module that failed.
991 -- File_Write 'Y' if writing to a log file.
992 -- Example
993 -- GCS_LEX_MAP_PKG.Add_Code_Value_Failed_Msg('Z', 'GCS_IDT_RULE_FAILED');
994 -- Notes
995 --
996 PROCEDURE Add_Code_Value_Failed_Msg( Code_Value VARCHAR2,
997 Message_Name VARCHAR2,
998 Module VARCHAR2,
999 File_Write VARCHAR2)
1000 IS
1001 BEGIN
1002 FND_MESSAGE.set_name('GCS', message_name);
1003 FND_MESSAGE.set_token('CODE_VAL', code_value);
1004 Write_To_Log(module, file_write);
1005 END Add_Code_Value_Failed_Msg;
1006
1007 --
1008 -- Procedure
1009 -- Add_Column_Failed_Msg
1010 -- Purpose
1011 -- Adds a message that a column is invalid. The message iself is determined
1012 -- by the message name passed in. It must be a message for 'GCS', and must
1013 -- have one token: COL_NAME for the column name.
1014 -- Arguments
1015 -- Column_Name Name of the column that failed.
1016 -- Message_Name Name of the message to be added.
1017 -- Module Name of the Module that failed.
1018 -- File_Write 'Y' if writing to a log file.
1019 -- Example
1020 -- GCS_LEX_MAP_PKG.Add_Column_Failed_Msg('SEGMENT1','GCS_IDT_RULE_FAILED');
1021 -- Notes
1022 --
1023 PROCEDURE Add_Column_Failed_Msg( Column_Name VARCHAR2,
1024 Message_Name VARCHAR2,
1025 Module VARCHAR2,
1026 File_Write VARCHAR2)
1027 IS
1028 BEGIN
1029 FND_MESSAGE.set_name('GCS', message_name);
1030 FND_MESSAGE.set_token('COL_NAME', column_name);
1031 Write_To_Log(module, file_write);
1032 END Add_Column_Failed_Msg;
1033
1034 --
1035 -- Procedure
1036 -- Add_Simple_Failed_Msg
1037 -- Purpose
1038 -- Adds a message that something failed during processing. The message
1039 -- itself is determined by the message name passed in. It must be a
1040 -- message for 'GCS', and must have no tokens.
1041 -- Arguments
1042 -- Message_Name Name of the message to be added.
1043 -- Module Name of the Module that failed.
1044 -- File_Write 'Y' if writing to a log file.
1045 -- Example
1046 -- GCS_LEX_MAP_PKG.Add_Simple_Failed_Msg('GCS_IDT_RULE_FAILED', mymodule);
1047 -- Notes
1048 --
1049 PROCEDURE Add_Simple_Failed_Msg( Message_Name VARCHAR2,
1050 Module VARCHAR2,
1051 File_Write VARCHAR2)
1052 IS
1053 BEGIN
1054 FND_MESSAGE.set_name('GCS', message_name);
1055 Write_To_Log(module, file_write);
1056 END Add_Simple_Failed_Msg;
1057
1058 --
1059 -- Procedure
1060 -- Add_Staging_Table_Failed_Msg
1061 -- Purpose
1062 -- Adds a message that something failed for the staging table. The message
1063 -- itself is determined by the message name passed in. It must be a message
1064 -- for 'GCS', and must have one token: STG_TBL for the table name.
1065 -- Arguments
1066 -- Table_Name Name of the table that failed.
1067 -- Message_Name Name of the message to be added.
1068 -- Module Name of the Module that failed.
1069 -- File_Write 'Y' if writing to a log file.
1070 -- Example
1071 -- GCS_LEX_MAP_PKG.Add_Staging_Table_Failed_Msg('gl_interface',
1072 -- 'GCS_IDT_RULE_FAILED');
1073 -- Notes
1074 --
1075 PROCEDURE Add_Staging_Table_Failed_Msg( Table_Name VARCHAR2,
1076 Message_Name VARCHAR2,
1077 Module VARCHAR2,
1078 File_Write VARCHAR2)
1079 IS
1080 BEGIN
1081 FND_MESSAGE.set_name('GCS', message_name);
1082 FND_MESSAGE.set_token('STG_TBL', table_name);
1083 Write_To_Log(module, file_write);
1084 END Add_Staging_Table_Failed_Msg;
1085
1086 --
1087 -- Procedure
1088 -- Add_Error_Code_Failed_Msg
1089 -- Purpose
1090 -- Adds a message that something failed while working with the error code
1091 -- column of the staging table. The message itself is determined by the
1092 -- message name passed in. It must be a message for 'GCS', and must have
1093 -- two tokens: STG_TBL for the table name and COL_NAME for the error code
1094 -- column name.
1095 -- Arguments
1096 -- Rule_Set_Id ID of the IDT for which the failure occurred.
1097 -- Staging_Table Name of the table that failed.
1098 -- Message_Name Name of the message to be added.
1099 -- Module Name of the Module that failed.
1100 -- File_Write 'Y' if writing to a log file.
1101 -- Example
1102 -- GCS_LEX_MAP_PKG.Add_Error_Code_Failed_Msg(111, 'gl_interface',
1103 -- 'GCS_IDT_RULE_FAILED');
1104 -- Notes
1105 --
1106 PROCEDURE Add_Error_Code_Failed_Msg( Rule_Set_Id NUMBER,
1107 Staging_Table VARCHAR2,
1108 Message_Name VARCHAR2,
1109 Module VARCHAR2,
1110 File_Write VARCHAR2)
1111 IS
1112 error_code_col_name VARCHAR2(100);
1113 BEGIN
1114 SELECT mc.column_name
1115 INTO error_code_col_name
1116 FROM gcs_lex_map_rule_sets rst,
1117 gcs_lex_map_columns mc
1118 WHERE rst.structure_id = mc.structure_id
1119 AND mc.error_code_column_flag = 'Y'
1120 AND rst.rule_set_id = add_error_code_failed_msg.rule_set_id;
1121
1122 FND_MESSAGE.set_name('GCS', message_name);
1123 FND_MESSAGE.set_token('STG_TBL', staging_table);
1124 FND_MESSAGE.set_token('COL_NAME', error_code_col_name);
1125 Write_To_Log(module, file_write);
1126 END Add_Error_Code_Failed_Msg;
1127
1128 --
1129 -- Function
1130 -- Create_AD
1131 -- Purpose
1132 -- Creates a function using ad_ddl. Returns 'TRUE' if there was an error.
1133 -- Returns 'FALSE' if there was no error.
1134 -- Arguments
1135 -- Func_Body Body of the function.
1136 -- Func_Name Name of the function.
1137 -- My_Appl APPLSYS schema, required for ad_ddl.
1138 -- Example
1139 -- GCS_LEX_MAP_PKG.Create_AD('create ...', 'foo', 'APPLSYS');
1140 -- Notes
1141 --
1142 FUNCTION Create_AD( Func_Body VARCHAR2,
1143 Func_Name VARCHAR2,
1144 My_Appl VARCHAR2)
1145 RETURN VARCHAR2 IS
1146 PRAGMA AUTONOMOUS_TRANSACTION;
1147
1148 err VARCHAR2(2000);
1149 curr_index NUMBER;
1150 lines NUMBER;
1151 body_len NUMBER;
1152 BEGIN
1153 curr_index := 1;
1154 lines := 0;
1155 body_len := LENGTH(func_body);
1156 WHILE curr_index <= body_len LOOP
1157 lines := lines + 1;
1158 ad_ddl.build_statement(substr(func_body, curr_index, 200), lines);
1159 curr_index := curr_index + 200;
1160 END LOOP;
1161
1162 ad_ddl.create_plsql_object(my_appl, 'FND', func_name, 1,lines,'FALSE',err);
1163 return err;
1164 END;
1165
1166 --
1167 -- Function
1168 -- Initial_Rule_Set_Check
1169 -- Purpose
1170 -- Checks that the rule set exists, is enabled, has 1 or more stages,
1171 -- and each stage has one or more rules. If one of these checks does
1172 -- not pass, raises the appropriate exception. Returns the IDT name.
1173 -- Arguments
1174 -- Rule_Set_Id ID of the Rule Set that should be checked.
1175 -- Usage Transformation or Validation
1176 -- File_Write 'Y' if writing to a log file.
1177 -- Example
1178 -- rsname := GCS_LEX_MAP_PKG.Initial_Rule_Set_Check(111);
1179 -- Notes
1180 --
1181 FUNCTION Initial_Rule_Set_Check( Rule_Set_Id NUMBER,
1182 Usage VARCHAR2,
1183 File_Write VARCHAR2)
1184 RETURN VARCHAR2 IS
1185 CURSOR stage_check IS
1186 SELECT stg.rule_stage_id
1187 FROM gcs_lex_map_rule_stages stg
1188 WHERE stg.rule_set_id = initial_rule_set_check.rule_set_id;
1189
1190 CURSOR rule_set_check IS
1191 SELECT name, enabled_flag
1192 FROM gcs_lex_map_rule_sets rsts
1193 WHERE rsts.rule_set_id = initial_rule_set_check.rule_set_id;
1194
1195 CURSOR rule_check(p_stage_id NUMBER) IS
1196 SELECT r.rule_id
1197 FROM gcs_lex_map_rules r
1198 WHERE r.rule_stage_id = p_stage_id;
1199
1200 CURSOR error_column_check IS
1201 SELECT mc.column_name, mc.write_flag
1202 FROM gcs_lex_map_rule_sets rst,
1203 gcs_lex_map_columns mc
1204 WHERE rst.structure_id = mc.structure_id
1205 AND mc.error_code_column_flag = 'Y'
1206 AND rst.rule_set_id = initial_rule_set_check.rule_set_id;
1207
1208 CURSOR func_reg_check_c IS
1209 SELECT d.derivation_id
1210 FROM gcs_lex_map_derivations d,
1211 gcs_lex_map_rules r,
1212 gcs_lex_map_rule_stages stg
1213 WHERE d.rule_id = r.rule_id
1214 AND r.rule_stage_id = stg.rule_stage_id
1215 AND stg.rule_set_id = initial_rule_set_check.rule_set_id
1216 AND d.derivation_type_code = 'PLS'
1217 AND NOT EXISTS
1218 (SELECT 1
1219 FROM gcs_lex_map_plsql_funcs f
1220 WHERE UPPER(d.function_name) = f.function_name);
1221
1222 idt_name VARCHAR2(30);
1223 rule_set_enabled VARCHAR2(1);
1224 num_stages NUMBER := 0;
1225 stage_id NUMBER;
1226 derivation_id NUMBER;
1227 dummy NUMBER;
1228
1229 col_name VARCHAR2(30);
1230 error_column_write VARCHAR2(1);
1231
1232 module VARCHAR2(60);
1233 BEGIN
1234 module := g_api || '.Initial_Rule_Set_Check';
1235 IF file_write = 'Y' THEN
1236 log_file_module_write(module, g_module_enter);
1237 END IF;
1238
1239 -- Check the rule set to make sure it exists and is enabled
1240 OPEN rule_set_check;
1241 FETCH rule_set_check INTO idt_name, rule_set_enabled;
1242
1243 -- Check that the rule set ID exists in the table
1244 IF (rule_set_check%NOTFOUND) THEN
1245 CLOSE rule_set_check;
1246 raise gcs_lex_invalid_rule_set;
1247 END IF;
1248 CLOSE rule_set_check;
1249
1250 -- Check that the rule set is enabled
1251 IF (rule_set_enabled <> 'Y') THEN
1252 raise gcs_lex_disabled;
1253 END IF;
1254
1255 -- Check that there are one or more stages in this rule set, and that
1256 -- each stage has one or more rules associated with it.
1257 FOR stage IN stage_check LOOP
1258 stage_id := stage.rule_stage_id;
1259 OPEN rule_check(stage_id);
1260 FETCH rule_check INTO dummy;
1261 IF (rule_check%NOTFOUND) THEN
1262 CLOSE rule_check;
1263 raise gcs_lex_stage_no_rule;
1264 END IF;
1265 CLOSE rule_check;
1266 num_stages := num_stages + 1;
1267 END LOOP;
1268
1269 -- now check that there were one or more stages
1270 IF num_stages = 0 THEN
1271 raise gcs_lex_set_no_stage;
1272 END IF;
1273
1274 -- now check that there is one and only one error code column
1275 OPEN error_column_check;
1276 FETCH error_column_check INTO col_name, error_column_write;
1277 IF error_column_check%NOTFOUND THEN
1278 CLOSE error_column_check;
1279 raise gcs_lex_no_error_column;
1280 END IF;
1281 IF error_column_write = 'Y' THEN
1282 CLOSE error_column_check;
1283 raise gcs_lex_error_col_write;
1284 END IF;
1285 FETCH error_column_check INTO col_name, error_column_write;
1286 IF error_column_check%FOUND THEN
1287 CLOSE error_column_check;
1288 raise gcs_lex_mult_error_columns;
1289 END IF;
1290 CLOSE error_column_check;
1291
1292 OPEN func_reg_check_c;
1293 FETCH func_reg_check_c INTO derivation_id;
1294 IF func_reg_check_c%FOUND THEN
1295 CLOSE func_reg_check_c;
1296 raise gcs_lex_func_not_registered;
1297 END IF;
1298 CLOSE func_reg_check_c;
1299
1300 IF file_write = 'Y' THEN
1301 log_file_module_write(module, g_module_success);
1302 END IF;
1303
1304 return idt_name;
1305 EXCEPTION
1306 WHEN gcs_lex_func_not_registered THEN
1307 add_plsql_deriv_failed_msg(derivation_id, 'GCS_IDT_FUNC_NOT_REGISTERED',
1308 module, file_write);
1309 IF file_write = 'Y' THEN
1310 log_file_module_write(module, g_module_failure);
1311 END IF;
1312 raise gcs_lex_init_failed;
1313 WHEN gcs_lex_invalid_rule_set THEN
1314 add_id_value_failed_msg(rule_set_id, 'GCS_IDT_INVALID_RULE_SET',
1315 module, file_write);
1316 IF file_write = 'Y' THEN
1317 log_file_module_write(module, g_module_failure);
1318 END IF;
1319 raise gcs_lex_init_failed;
1320 WHEN gcs_lex_disabled THEN
1321 IF file_write = 'Y' THEN
1322 log_file_module_write(module, g_module_failure);
1323 END IF;
1324 raise;
1325 WHEN gcs_lex_set_no_stage THEN
1326 IF usage = 'TRANSFORMATION' THEN
1327 add_idt_failed_msg(rule_set_id, 'GCS_IDT_SET_NO_STAGE',
1328 module, file_write);
1329 ELSE
1330 add_idt_failed_msg(rule_set_id, 'GCS_IDT_VRS_SET_NO_RULE',
1331 module, file_write);
1332 END IF;
1333 IF file_write = 'Y' THEN
1334 log_file_module_write(module, g_module_failure);
1335 END IF;
1336 raise gcs_lex_init_failed;
1337 WHEN gcs_lex_stage_no_rule THEN
1338 add_stage_failed_msg(stage_id, 'GCS_IDT_STAGE_NO_RULE',
1339 module, file_write);
1340 IF file_write = 'Y' THEN
1341 log_file_module_write(module, g_module_failure);
1342 END IF;
1343 raise gcs_lex_init_failed;
1344 WHEN gcs_lex_no_error_column THEN
1345 add_structure_failed_msg(rule_set_id, 'GCS_IDT_NO_ERROR_COLUMN',
1346 module, file_write);
1347 IF file_write = 'Y' THEN
1348 log_file_module_write(module, g_module_failure);
1349 END IF;
1350 raise gcs_lex_init_failed;
1351 WHEN gcs_lex_mult_error_columns THEN
1352 add_structure_failed_msg(rule_set_id, 'GCS_IDT_MULT_ERROR_COLUMNS',
1353 module, file_write);
1354 IF file_write = 'Y' THEN
1355 log_file_module_write(module, g_module_failure);
1356 END IF;
1357 raise gcs_lex_init_failed;
1358 WHEN gcs_lex_error_col_write THEN
1359 add_column_failed_msg(col_name, 'GCS_IDT_ERROR_COL_WRITE',
1360 module, file_write);
1361 IF file_write = 'Y' THEN
1362 log_file_module_write(module, g_module_failure);
1363 END IF;
1364 raise gcs_lex_init_failed;
1365 WHEN OTHERS THEN
1366 add_id_value_failed_msg(rule_set_id, 'GCS_IDT_UNEXPECTED_INIT_ERROR',
1367 module, file_write);
1368 IF file_write = 'Y' THEN
1369 log_file_module_write(module, g_module_failure);
1370 END IF;
1371 raise gcs_lex_init_failed;
1372 END Initial_Rule_Set_Check;
1373
1374 --
1375 -- Procedure
1376 -- Staging_Table_Check
1377 -- Purpose
1378 -- Checks that the table has all the columns specified in the meta data
1379 -- repository.
1380 -- Arguments
1381 -- Table_Name Table to check.
1382 -- Rule_Set_Id ID of the rule set to check against.
1383 -- Example
1384 -- GCS_LEX_MAP_PKG.Table_Check('gl_interface', 111);
1385 -- Notes
1386 --
1387 PROCEDURE Staging_Table_Check(Table_Name VARCHAR2,
1388 Rule_Set_Id NUMBER)
1389 IS
1390 CURSOR all_columns IS
1391 SELECT mc.column_name
1392 FROM gcs_lex_map_columns mc,
1393 gcs_lex_map_rule_sets rst
1394 WHERE rst.structure_id = mc.structure_id
1395 AND rst.rule_set_id = staging_table_check.rule_set_id;
1396
1397 check_text VARCHAR2(32767);
1398
1399 TYPE check_cursor IS REF CURSOR;
1400 check_cv check_cursor;
1401
1402 num_cols NUMBER := 0;
1403
1404 module VARCHAR2(60);
1405 BEGIN
1406 module := g_api || '.Staging_Table_Check';
1407 log_file_module_write(module, g_module_enter);
1408
1409 check_text := 'SELECT ';
1410 FOR column IN all_columns LOOP
1411 IF num_cols > 0 THEN
1412 check_text := check_text || ',';
1413 END IF;
1414 check_text := check_text || column.column_name;
1415 num_cols := num_cols + 1;
1416 END LOOP;
1417 check_text := check_text || ' FROM ' || table_name;
1418
1419 OPEN check_cv FOR check_text;
1420 CLOSE check_cv;
1421
1422 log_file_module_write(module, g_module_success);
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 add_staging_table_failed_msg(table_name, 'GCS_IDT_STG_TABLE_NOT_META',
1426 module, 'Y');
1427 log_file_module_write(module, g_module_failure);
1428 raise gcs_lex_table_check_failed;
1429 END Staging_Table_Check;
1430
1431 --
1432 -- Procedure
1433 -- Get_Filter_Text
1434 -- Purpose
1435 -- Puts all the query criteria together to create filter text.
1436 -- Arguments
1437 -- Rule_Set_Id The IDT being used for the transformation.
1438 -- Usage Transformation or Validation.
1439 -- Filter_Column_Name1 First filter column name.
1440 -- Filter_Column_Value1 Value to match the filter column against.
1441 -- ...
1442 -- ...
1443 -- Example
1444 -- GCS_LEX_MAP_PKG.Table_Check('GROUP_ID', '123', null, null, ...);
1445 -- Notes
1446 --
1447 FUNCTION Get_Filter_Text( rule_set_id NUMBER,
1448 usage VARCHAR2,
1449 filter_column_name1 VARCHAR2,
1450 filter_column_value1 VARCHAR2,
1451 filter_column_name2 VARCHAR2,
1452 filter_column_value2 VARCHAR2,
1453 filter_column_name3 VARCHAR2,
1454 filter_column_value3 VARCHAR2,
1455 filter_column_name4 VARCHAR2,
1456 filter_column_value4 VARCHAR2,
1457 filter_column_name5 VARCHAR2,
1458 filter_column_value5 VARCHAR2,
1459 filter_column_name6 VARCHAR2,
1460 filter_column_value6 VARCHAR2,
1461 filter_column_name7 VARCHAR2,
1462 filter_column_value7 VARCHAR2,
1463 filter_column_name8 VARCHAR2,
1464 filter_column_value8 VARCHAR2,
1465 filter_column_name9 VARCHAR2,
1466 filter_column_value9 VARCHAR2,
1467 filter_column_name10 VARCHAR2,
1468 filter_column_value10 VARCHAR2)
1469 RETURN VARCHAR2 IS
1470 TYPE FilterColumns IS VARRAY(10) OF VARCHAR2(30);
1471 TYPE FilterValues IS VARRAY(10) OF VARCHAR2(500);
1472
1473 filtercols FilterColumns;
1474 filtervals FilterValues;
1475
1476 filter_text VARCHAR2(5000);
1477
1478 CURSOR filter_check(col_name VARCHAR2) IS
1479 SELECT decode( usage,
1480 'TRANSFORMATION', write_flag,
1481 error_code_column_flag)
1482 FROM gcs_lex_map_rule_sets rst,
1483 gcs_lex_map_columns mc
1484 WHERE mc.structure_id = rst.structure_id
1485 AND rst.rule_set_id = get_filter_text.rule_set_id
1486 AND UPPER(mc.column_name) = UPPER(col_name);
1487
1488 filter_write_flag VARCHAR2(10);
1489
1490 num_conditions NUMBER := 0;
1491 filter_num NUMBER;
1492
1493 module VARCHAR2(60);
1494 BEGIN
1495 module := g_api || '.Get_Filter_Text';
1496
1497 filtercols := FilterColumns(filter_column_name1,
1498 filter_column_name2,
1499 filter_column_name3,
1500 filter_column_name4,
1501 filter_column_name5,
1502 filter_column_name6,
1503 filter_column_name7,
1504 filter_column_name8,
1505 filter_column_name9,
1506 filter_column_name10);
1507
1508 filtervals := FilterValues( filter_column_value1,
1509 filter_column_value2,
1510 filter_column_value3,
1511 filter_column_value4,
1512 filter_column_value5,
1513 filter_column_value6,
1514 filter_column_value7,
1515 filter_column_value8,
1516 filter_column_value9,
1517 filter_column_value10);
1518
1519 filter_text := '';
1520
1521 log_file_module_write(module, g_module_enter);
1522
1523 FOR i IN 1..10 LOOP
1524 filter_num := i;
1525 IF filtercols(i) IS NOT NULL AND
1526 filtervals(i) IS NOT NULL THEN
1527 OPEN filter_check(filtercols(i));
1528 FETCH filter_check INTO filter_write_flag;
1529 IF filter_check%NOTFOUND THEN
1530 CLOSE filter_check;
1531 raise gcs_lex_invalid_filter_column;
1532 END IF;
1533 CLOSE filter_check;
1534
1535 IF filter_write_flag = 'Y' THEN
1536 raise gcs_lex_filter_column_not_ro;
1537 END IF;
1538 IF num_conditions > 0 THEN
1539 filter_text := filter_text || ' AND ';
1540 END IF;
1541 filter_text := filter_text || filtercols(i) || '=''' ||
1542 REPLACE(filtervals(i), '''', '''''') || '''';
1543 num_conditions := num_conditions + 1;
1544 ELSIF filtercols(i) IS NOT NULL THEN
1545 raise gcs_lex_no_filter_value;
1546 ELSIF filtervals(i) IS NOT NULL THEN
1547 raise gcs_lex_no_filter_column_name;
1548 END IF;
1549 END LOOP;
1550
1551 log_file_module_write(module, g_module_success);
1552
1553 return filter_text;
1554 EXCEPTION
1555 WHEN gcs_lex_no_filter_column_name THEN
1556 add_simple_failed_msg('GCS_IDT_NO_FILTER_COLUMN_NAME', module, 'Y');
1557 log_file_module_write(module, g_module_failure);
1558 raise gcs_lex_filter_error;
1559 WHEN gcs_lex_no_filter_value THEN
1560 add_simple_failed_msg('GCS_IDT_NO_FILTER_VALUE', module, 'Y');
1561 log_file_module_write(module, g_module_failure);
1562 raise gcs_lex_filter_error;
1563 WHEN gcs_lex_filter_column_not_ro THEN
1564 add_column_failed_msg(filtercols(filter_num),
1565 'GCS_IDT_FILTER_COLUMN_NOT_RO', module, 'Y');
1566 log_file_module_write(module, g_module_failure);
1567 raise gcs_lex_filter_error;
1568 WHEN gcs_lex_invalid_filter_column THEN
1569 add_column_failed_msg(filtercols(filter_num),
1570 'GCS_IDT_INVALID_FILTER_COLUMN', module, 'Y');
1571 log_file_module_write(module, g_module_failure);
1572 raise gcs_lex_filter_error;
1573 WHEN OTHERS THEN
1574 add_simple_failed_msg('GCS_IDT_UNEXP_FILTER_ERROR', module, 'Y');
1575 log_file_module_write(module, g_module_failure);
1576 raise gcs_lex_filter_error;
1577 END Get_Filter_Text;
1578
1579 --
1580 -- Procedure
1581 -- Validation_Check
1582 -- Purpose
1583 -- Checks that the validation for the rule given will work.
1584 -- Arguments
1585 -- Rule_Id Rule for which the validation should be checked.
1586 -- File_Write 'Y' if writing to a log file.
1587 -- Example
1588 -- GCS_LEX_MAP_PKG.Validation_Check(123);
1589 -- Notes
1590 --
1591 PROCEDURE Validation_Check( Rule_Id NUMBER,
1592 File_Write VARCHAR2)
1593 IS
1594 CURSOR all_columns IS
1595 SELECT mc.column_name
1596 FROM gcs_lex_map_columns mc,
1597 gcs_lex_map_rules r
1598 WHERE r.lookup_table_id = mc.structure_id
1599 AND r.rule_id = validation_check.rule_id;
1600
1601 check_text VARCHAR2(32767);
1602
1603 TYPE check_cursor IS REF CURSOR;
1604 check_cv check_cursor;
1605
1606 num_cols NUMBER := 0;
1607
1608 lookup_code_column_found VARCHAR2(1);
1609
1610 -- validation type is 'I' for independent, and 'F' for table-validated.
1611 CURSOR value_set_info(val_set_id NUMBER) IS
1612 SELECT ffvs.validation_type
1613 FROM fnd_flex_value_sets ffvs
1614 WHERE ffvs.flex_value_set_id = val_set_id;
1615
1616 vs_validation_type VARCHAR2(50);
1617
1618 tv_vs_check_table NUMBER;
1619
1620 valid_type_code VARCHAR2(30);
1621
1622 CURSOR structure_info IS
1623 SELECT ms.structure_name
1624 FROM gcs_lex_map_rules r,
1625 gcs_lex_map_structs ms
1626 WHERE r.lookup_table_id = ms.structure_id
1627 AND r.rule_id = validation_check.rule_id;
1628
1629 table_name VARCHAR2(100);
1630 value_set_id NUMBER;
1631
1632 module VARCHAR2(60);
1633 BEGIN
1634 module := g_api || '.Validation_Check';
1635 lookup_code_column_found := 'N';
1636
1637 SELECT r.validation_type_code,
1638 r.value_set_id
1639 INTO valid_type_code,
1640 value_set_id
1641 FROM gcs_lex_map_rules r
1642 WHERE r.rule_id = validation_check.rule_id;
1643
1644 IF valid_type_code = 'V' THEN
1645 OPEN value_set_info(value_set_id);
1646 FETCH value_set_info INTO vs_validation_type;
1647 IF value_set_info%NOTFOUND THEN
1648 CLOSE value_set_info;
1649 raise gcs_lex_invalid_value_set_id;
1650 END IF;
1651 CLOSE value_set_info;
1652
1653 IF vs_validation_type = 'F' THEN
1654 -- there should be exactly one row for a table-validated value set
1655 -- in the validation_tables table.
1656 SELECT COUNT(*)
1657 INTO tv_vs_check_table
1658 FROM fnd_flex_validation_tables ffvt,
1659 gcs_lex_map_rules r
1660 WHERE ffvt.flex_value_set_id = r.value_set_id
1661 AND r.rule_id = validation_check.rule_id;
1662
1663 IF tv_vs_check_table <> 1 THEN
1664 raise gcs_lex_invalid_tv_value_set;
1665 END IF;
1666 ELSIF vs_validation_type <> 'I' THEN
1667 raise gcs_lex_invalid_value_set;
1668 END IF;
1669 ELSIF valid_type_code = 'L' THEN
1670 OPEN structure_info;
1671 FETCH structure_info INTO table_name;
1672 IF structure_info%NOTFOUND THEN
1673 CLOSE structure_info;
1674 raise gcs_lex_no_validation_lut;
1675 END IF;
1676 CLOSE structure_info;
1677
1678 check_text := 'SELECT ';
1679
1680 FOR column IN all_columns LOOP
1681 IF num_cols > 0 THEN
1682 check_text := check_text || ',';
1683 END IF;
1684 check_text := check_text || column.column_name;
1685 num_cols := num_cols + 1;
1686 IF UPPER(column.column_name) = 'LOOKUP_CODE' THEN
1687 lookup_code_column_found := 'Y';
1688 END IF;
1689 END LOOP;
1690 check_text := check_text || ' FROM ' || table_name;
1691
1692 IF lookup_code_column_found <> 'Y' THEN
1693 raise gcs_lex_lut_no_lookup_code;
1694 END IF;
1695
1696 begin
1697 OPEN check_cv FOR check_text;
1698 CLOSE check_cv;
1699 exception
1700 when others then
1701 raise gcs_lex_vdation_lut_not_meta;
1702 end;
1703 ELSIF valid_type_code <> 'N' THEN
1704 raise gcs_lex_invalid_valid_code;
1705 END IF;
1706 EXCEPTION
1707 WHEN gcs_lex_invalid_value_set_id THEN
1708 add_id_value_failed_msg(value_set_id, 'GCS_IDT_INVALID_VALUE_SET_ID',
1709 module, file_write);
1710 raise gcs_lex_valid_check_failed;
1711 WHEN gcs_lex_invalid_value_set THEN
1712 add_value_set_failed_msg(rule_id, 'GCS_IDT_INVALID_VALUE_SET',
1713 module, file_write);
1714 raise gcs_lex_valid_check_failed;
1715 WHEN gcs_lex_invalid_tv_value_set THEN
1716 add_value_set_failed_msg(rule_id, 'GCS_IDT_INVALID_TV_VALUE_SET',
1717 module, file_write);
1718 raise gcs_lex_valid_check_failed;
1719 WHEN gcs_lex_lut_no_lookup_code THEN
1720 add_rule_lut_failed_msg(rule_id, 'GCS_IDT_LUT_NO_LOOKUP_CODE',
1721 module, file_write);
1722 raise gcs_lex_valid_check_failed;
1723 WHEN gcs_lex_vdation_lut_not_meta THEN
1724 add_rule_lut_failed_msg(rule_id, 'GCS_IDT_VDATION_LUT_NOT_META',
1725 module, file_write);
1726 raise gcs_lex_valid_check_failed;
1727 WHEN gcs_lex_invalid_valid_code THEN
1728 add_code_value_failed_msg(valid_type_code, 'GCS_IDT_INVALID_VALID_CODE',
1729 module, file_write);
1730 raise gcs_lex_valid_check_failed;
1731 WHEN gcs_lex_no_validation_lut THEN
1732 add_rule_failed_msg(rule_id, 'GCS_IDT_NO_VALIDATION_LUT',
1733 module, file_write);
1734 raise gcs_lex_valid_check_failed;
1735 WHEN OTHERS THEN
1736 add_rule_failed_msg(rule_id, 'GCS_IDT_UNEXP_VLD_CHECK_ERROR',
1737 module, file_write);
1738 raise gcs_lex_valid_check_failed;
1739 END Validation_Check;
1740
1741 --
1742 -- Function
1743 -- Create_Param_List
1744 -- Purpose
1745 -- Creates a text list of parameters, whose structure depends on the
1746 -- list_type_code variable passed in. This will create a function header
1747 -- style list, or a argument-passing (comma-delimited) style list.
1748 -- Arguments
1749 -- Rule_Id Rule for which the parameter list should be created.
1750 -- List_Type_Code 'F' for function header, or 'C' for a comma-delimited.
1751 -- 'H' for error header, 'Q' for error query.
1752 -- File_Write 'Y' if writing to a log file.
1753 -- Example
1754 -- str := GCS_LEX_MAP_PKG.Create_Param_List(123, 'F')
1755 -- Notes
1756 --
1757 FUNCTION Create_Param_List( Rule_Id NUMBER,
1758 List_Type_Code VARCHAR2,
1759 File_Write VARCHAR2)
1760 RETURN VARCHAR2 IS
1761 param_list VARCHAR2(32767);
1762 my_lang VARCHAR2(5);
1763 my_security NUMBER;
1764
1765 -- Cursor listing all parameters necessary to run this function
1766 CURSOR all_params IS
1767 (SELECT mc.column_name column_name,
1768 decode(mc.column_type_code, 'N', 'NUMBER',
1769 'D', 'DATE',
1770 'V', 'VARCHAR2',
1771 '') column_type_code
1772 FROM gcs_lex_map_columns mc,
1773 gcs_lex_map_rules r
1774 WHERE mc.column_id = r.target_column_id
1775 AND r.rule_id = create_param_list.rule_id)
1776 UNION
1777 (SELECT mc.column_name column_name,
1778 decode(mc.column_type_code, 'N', 'NUMBER',
1779 'D', 'DATE',
1780 'V', 'VARCHAR2',
1781 '') column_type_code
1782 FROM gcs_lex_map_columns mc,
1783 gcs_lex_map_drv_details dvd,
1784 gcs_lex_map_derivations d
1785 WHERE d.rule_id = create_param_list.rule_id
1786 AND dvd.derivation_id = d.derivation_id
1787 AND dvd.detail_column_id = mc.column_id)
1788 UNION
1789 (SELECT mc.column_name column_name,
1790 decode(mc.column_type_code, 'N', 'NUMBER',
1791 'D', 'DATE',
1792 'V', 'VARCHAR2',
1793 '') column_type_code
1794 FROM gcs_lex_map_columns mc,
1795 gcs_lex_map_conditions c,
1796 gcs_lex_map_derivations d
1797 WHERE d.rule_id = create_param_list.rule_id
1798 AND c.derivation_id = d.derivation_id
1799 AND c.source_column_id = mc.column_id)
1800 ORDER BY column_name;
1801
1802 module VARCHAR2(60);
1803 BEGIN
1804 module := g_api || '.Create_Param_List';
1805 param_list := '';
1806 my_lang := userenv('LANG');
1807 my_security := fnd_global.lookup_security_group('COLUMN_TYPE', 0);
1808
1809 -- First, pass in the row ID to the function (error checking purposes)
1810 -- if this is not error checking. Then, loop through the params.
1811 IF list_type_code = 'F' THEN
1812 param_list := 'row_id rowid';
1813 FOR param IN all_params LOOP
1814 param_list := param_list || ',' ||
1815 param.column_name || ' ' || param.column_type_code;
1816 END LOOP;
1817 ELSIF list_type_code = 'C' THEN
1818 param_list := 'rowid';
1819 FOR param IN all_params LOOP
1820 param_list := param_list || ',' || param.column_name;
1821 END LOOP;
1822 ELSIF list_type_code = 'H' THEN
1823 FOR param IN all_params LOOP
1824 param_list := param_list ||RPAD(SUBSTR(param.column_name,1,19),20,' ');
1825 END LOOP;
1826 ELSIF list_type_code = 'Q' THEN
1827 FOR param IN all_params LOOP
1828 IF param_list IS NOT NULL THEN
1829 param_list := param_list || ' || ';
1830 END IF;
1831 param_list := param_list || 'RPAD(nvl(SUBSTR(' || param.column_name ||
1832 ',1,19), '' ''),20,'' '')';
1833 END LOOP;
1834 ELSE
1835 raise gcs_lex_invalid_list_code;
1836 END IF;
1837
1838 return param_list;
1839 EXCEPTION
1840 WHEN gcs_lex_invalid_list_code THEN
1841 add_code_value_failed_msg(list_type_code, 'GCS_IDT_INVALID_LIST_CODE',
1842 module, file_write);
1843 raise gcs_lex_param_list_failed;
1844 WHEN OTHERS THEN
1845 add_rule_failed_msg(rule_id, 'GCS_IDT_PARAM_LIST_FAILED',
1846 module, file_write);
1847 raise gcs_lex_param_list_failed;
1848 END Create_Param_List;
1849
1850 --
1851 -- Function
1852 -- Create_Condition
1853 -- Purpose
1854 -- Creates the text for the condition that is associated with the
1855 -- derivation given.
1856 -- Arguments
1857 -- Derivation_Id ID of the Derivation for which the
1858 -- condition text should be created.
1859 -- Example
1860 -- str := GCS_LEX_MAP_PKG.Create_Condition(12345)
1861 -- Notes
1862 --
1863 FUNCTION Create_Condition( Derivation_Id NUMBER)
1864 RETURN VARCHAR2 IS
1865 cond_text VARCHAR2(8000);
1866
1867 CURSOR all_conds IS
1868 SELECT mc.column_name || ' ' || c.comparison_operator_code ||
1869 decode(c.comparison_value,
1870 '', '',
1871 ' ''' || REPLACE(c.comparison_value, '''', '''''') || '''')
1872 simple_cond
1873 FROM gcs_lex_map_columns mc,
1874 gcs_lex_map_conditions c
1875 WHERE c.derivation_id = create_condition.derivation_id
1876 AND c.source_column_id = mc.column_id;
1877
1878 -- for the first condition, do not add ' AND '
1879 num_conditions NUMBER := 0;
1880
1881 module VARCHAR2(60);
1882 BEGIN
1883 module := g_api || '.Create_Condition';
1884 cond_text := '';
1885
1886 FOR cond IN all_conds LOOP
1887 IF num_conditions > 0 THEN
1888 cond_text := cond_text || ' AND ';
1889 END IF;
1890 cond_text := cond_text || cond.simple_cond;
1891 num_conditions := num_conditions + 1;
1892 END LOOP;
1893
1894 return cond_text;
1895 EXCEPTION
1896 WHEN OTHERS THEN
1897 add_deriv_failed_msg(derivation_id, 'GCS_IDT_CREATE_COND_FAILED',
1898 module, 'N');
1899 raise gcs_lex_create_cond_failed;
1900 END Create_Condition;
1901
1902 --
1903 -- Function
1904 -- Create_Lookup_Derivation
1905 -- Purpose
1906 -- Creates the text for the lookup derivation specified.
1907 -- Arguments
1908 -- Func_Name Wrapper function name for which this lookup derivation
1909 -- is being created.
1910 -- Derivation_Id ID of the Derivation for which the lookup derivation
1911 -- text should be created.
1912 -- Usage 'TRANSFORMATION' or 'VALIDATION' based ont he calling
1913 -- function.
1914 -- Example
1915 -- str := GCS_LEX_MAP_PKG.Create_Lookup_Derivation(101, 123, 12345)
1916 -- Notes
1917 --
1918 FUNCTION Create_Lookup_Derivation( Func_Name VARCHAR2,
1919 Derivation_Id NUMBER,
1920 Usage VARCHAR2)
1921 RETURN VARCHAR2 IS
1922 deriv_text VARCHAR2(16000);
1923
1924 CURSOR all_lookup_details IS
1925 SELECT lutmc.column_name lut_col_name,
1926 stgmc.column_name stg_col_name,
1927 dvd.detail_constant detail_constant
1928 FROM gcs_lex_map_drv_details dvd,
1929 gcs_lex_map_columns lutmc,
1930 gcs_lex_map_columns stgmc
1931 WHERE dvd.derivation_id = create_lookup_derivation.derivation_id
1932 AND dvd.lookup_column_id = lutmc.column_id
1933 AND dvd.detail_column_id = stgmc.column_id (+);
1934
1935 -- For the first join, a 'WHERE' should be added, while for subsequent
1936 -- joins an 'AND' should be added
1937 num_joins NUMBER := 0;
1938
1939 -- result column name and lookup table name
1940 lookup_table_name VARCHAR2(50);
1941 result_col_name VARCHAR2(30);
1942
1943 module VARCHAR2(60);
1944 BEGIN
1945 module := g_api || '.Create_Lookup_Derivation';
1946 IF usage = 'TRANSFORMATION' THEN
1947 SELECT ms.structure_name,
1948 mc.column_name
1949 INTO lookup_table_name,
1950 result_col_name
1951 FROM gcs_lex_map_derivations d,
1952 gcs_lex_map_columns mc,
1953 gcs_lex_map_structs ms
1954 WHERE d.lookup_result_column_id = mc.column_id
1955 AND mc.structure_id = ms.structure_id
1956 AND d.derivation_id = create_lookup_derivation.derivation_id;
1957
1958 deriv_text := 'SELECT lut.' || result_col_name || ' INTO ' || g_ret_val;
1959 ELSE
1960 SELECT ms.structure_name
1961 INTO lookup_table_name
1962 FROM gcs_lex_map_structs ms,
1963 gcs_lex_map_derivations d
1964 WHERE d.lookup_table_id = ms.structure_id
1965 AND d.derivation_id = create_lookup_derivation.derivation_id;
1966
1967 deriv_text := 'SELECT DISTINCT 1 INTO dummy';
1968 END IF;
1969
1970 deriv_text := deriv_text || ' FROM ' || lookup_table_name || ' lut WHERE ';
1971
1972 FOR lookup_detail IN all_lookup_details LOOP
1973 IF num_joins > 0 THEN
1974 deriv_text := deriv_text || ' AND ';
1975 END IF;
1976
1977 -- When a column is specified, match to that. Otherwise, match to the
1978 -- constant given.
1979 deriv_text := deriv_text ||'lut.'|| lookup_detail.lut_col_name || '=';
1980 IF lookup_detail.stg_col_name IS NOT NULL THEN
1981 deriv_text := deriv_text || func_name || '.' ||
1982 lookup_detail.stg_col_name;
1983 ELSE
1984 deriv_text := deriv_text || '''' ||
1985 REPLACE(lookup_detail.detail_constant, '''', '''''') ||
1986 '''';
1987 END IF;
1988
1989 num_joins := num_joins + 1;
1990 END LOOP;
1991
1992 deriv_text := deriv_text || ';';
1993
1994 return deriv_text;
1995 EXCEPTION
1996 WHEN OTHERS THEN
1997 add_deriv_failed_msg(derivation_id, 'GCS_IDT_DERIVATION_FAILED',
1998 module, 'N');
1999 raise gcs_lex_derivation_failed;
2000 END Create_Lookup_Derivation;
2001
2002 --
2003 -- Function
2004 -- Create_String_Derivation
2005 -- Purpose
2006 -- Creates the text for the string derivation specified.
2007 -- Arguments
2008 -- Derivation_Id ID of the Derivation for which the
2009 -- string derivation text should be created.
2010 -- Example
2011 -- str := GCS_LEX_MAP_PKG.Create_String_Derivation(12345)
2012 -- Notes
2013 --
2014 FUNCTION Create_String_Derivation( Derivation_Id NUMBER)
2015 RETURN VARCHAR2 IS
2016 deriv_text VARCHAR2(16000);
2017
2018 -- get the list of strings to concatenate here, in the correct order
2019 CURSOR all_string_details IS
2020 SELECT decode(string_action_type_code,
2021 'S', 'SUBSTR('||mc.column_name || ',' ||
2022 dvd.substring_start_index || ',' ||
2023 dvd.substring_length || ')',
2024 'C', mc.column_name,
2025 'F', '''' ||
2026 REPLACE(dvd.detail_constant, '''', '''''') ||
2027 '''',
2028 '') string_action
2029 FROM gcs_lex_map_drv_details dvd,
2030 gcs_lex_map_columns mc
2031 WHERE dvd.derivation_id = create_string_derivation.derivation_id
2032 AND dvd.detail_column_id = mc.column_id (+)
2033 ORDER BY dvd.string_merge_order;
2034
2035 -- For the first string, you do not add a '||' for concatentation
2036 num_strings NUMBER := 0;
2037
2038 module VARCHAR2(60);
2039 BEGIN
2040 module := g_api || '.Create_String_Derivation';
2041 deriv_text := g_ret_val || ':=';
2042
2043 FOR string_detail IN all_string_details LOOP
2044 IF num_strings > 0 THEN
2045 deriv_text := deriv_text || '||';
2046 END IF;
2047 deriv_text := deriv_text || string_detail.string_action;
2048 num_strings := num_strings + 1;
2049 END LOOP;
2050
2051 -- if there were no strings to concatenate, just put in the empty string.
2052 IF num_strings = 0 THEN
2053 deriv_text := deriv_text || '''''';
2054 END IF;
2055
2056 deriv_text := deriv_text || ';';
2057
2058 return deriv_text;
2059 EXCEPTION
2060 WHEN OTHERS THEN
2061 add_deriv_failed_msg(derivation_id, 'GCS_IDT_DERIVATION_FAILED',
2062 module, 'N');
2063 raise gcs_lex_derivation_failed;
2064 END Create_String_Derivation;
2065
2066 --
2067 -- Function
2068 -- Create_PLSQL_Derivation
2069 -- Purpose
2070 -- Creates the text for the PL/SQL function derivation specified.
2071 -- Arguments
2072 -- Derivation_Id ID of the Derivation for which the PL/SQL function
2073 -- derivation text should be created.
2074 -- Usage 'TRANSFORMATION' or 'VALIDATION' based ont he calling
2075 -- function.
2076 -- Example
2077 -- str := GCS_LEX_MAP_PKG.Create_PLSQL_Derivation(12345)
2078 -- Notes
2079 --
2080 FUNCTION Create_PLSQL_Derivation( Derivation_Id NUMBER,
2081 Usage VARCHAR2)
2082 RETURN VARCHAR2 IS
2083 deriv_text VARCHAR2(16000);
2084
2085 CURSOR all_params IS
2086 SELECT plsql_param_name || '=>' ||
2087 decode(plsql_param_source_code,
2088 'C', mc.column_name,
2089 'S',''''||REPLACE(dvd.detail_constant, '''', '''''')||'''',
2090 'N',dvd.detail_constant,
2091 '') plsql_parameter
2092 FROM gcs_lex_map_drv_details dvd,
2093 gcs_lex_map_columns mc
2094 WHERE dvd.derivation_id = create_plsql_derivation.derivation_id
2095 AND dvd.detail_column_id = mc.column_id (+);
2096
2097 -- Don't add a ',' to the beginning for the first parameter passed in
2098 num_params NUMBER := 0;
2099
2100 module VARCHAR2(60);
2101 BEGIN
2102 module := g_api || '.Create_PLSQL_Derivation';
2103
2104 SELECT function_name
2105 INTO deriv_text
2106 FROM gcs_lex_map_derivations d
2107 WHERE d.derivation_id = create_plsql_derivation.derivation_id;
2108
2109 -- With the validation case, we only pass in the rowid
2110 IF usage = 'VALIDATION' THEN
2111 return deriv_text || '(row_id)';
2112 END IF;
2113
2114 deriv_text := g_ret_val || ':=' || deriv_text;
2115
2116 FOR param IN all_params LOOP
2117 IF num_params > 0 THEN
2118 deriv_text := deriv_text || ',' || param.plsql_parameter;
2119 ELSE
2120 deriv_text := deriv_text || '(' || param.plsql_parameter;
2121 END IF;
2122 num_params := num_params + 1;
2123 END LOOP;
2124
2125 IF num_params > 0 THEN
2126 deriv_text := deriv_text || ')';
2127 END IF;
2128
2129 deriv_text := deriv_text || ';';
2130
2131 return deriv_text;
2132 EXCEPTION
2133 WHEN OTHERS THEN
2134 add_deriv_failed_msg(derivation_id, 'GCS_IDT_DERIVATION_FAILED',
2135 module, 'N');
2136 raise gcs_lex_derivation_failed;
2137 END Create_PLSQL_Derivation;
2138
2139 --
2140 -- Procedure
2141 -- Remove_Function
2142 -- Purpose
2143 -- Drops the given function if it exists. Passes through quietly if it
2144 -- doesn't exist.
2145 -- Arguments
2146 -- Func_Name Name of the function to remove.
2147 -- Example
2148 -- GCS_LEX_MAP_PKG.Remove_Function('GCS_LEX_GET_111_123');
2149 -- Notes
2150 --
2151 PROCEDURE Remove_Function(func_name VARCHAR2) IS
2152 PRAGMA AUTONOMOUS_TRANSACTION;
2153 BEGIN
2154 EXECUTE IMMEDIATE 'drop function ' || func_name;
2155 EXCEPTION
2156 WHEN OTHERS THEN
2157 null;
2158 END Remove_Function;
2159
2160 --
2161 -- Procedure
2162 -- Remove_All_Functions
2163 -- Purpose
2164 -- Drops all functions for the given rule set that are not used.
2165 -- Arguments
2166 -- Rule_Set_Id Rule set for which the functions should be removed.
2167 -- Example
2168 -- GCS_LEX_MAP_PKG.Remove_All_Functions(101);
2169 -- Notes
2170 --
2171 PROCEDURE Remove_All_Functions(p_rule_set_id NUMBER) IS
2172 PRAGMA AUTONOMOUS_TRANSACTION;
2173
2174 CURSOR all_funcs IS
2175 SELECT object_name
2176 FROM user_objects
2177 WHERE object_name LIKE
2178 'GCS\_LEX\_GET\_' || p_rule_set_id || '\__%' ESCAPE '\'
2179 AND object_name NOT IN
2180 (SELECT 'GCS_LEX_GET_' || p_rule_set_id || '_' || r.rule_id
2181 FROM gcs_lex_map_rules r,
2182 gcs_lex_map_rule_stages stg
2183 WHERE stg.rule_set_id = p_rule_set_id
2184 AND stg.rule_stage_id = r.rule_stage_id)
2185 AND object_type = 'FUNCTION';
2186 BEGIN
2187 FOR map_func IN all_funcs LOOP
2188 execute immediate 'drop function ' || map_func.object_name;
2189 END LOOP;
2190 END Remove_All_Functions;
2191
2192 --
2193 -- Procedure
2194 -- Test_Deriv
2195 -- Purpose
2196 -- Creates the text for a PL/SQL function which will take a number of
2197 -- inputs and return the appropriate target value for the given rule.
2198 -- Then applies it to the database using dynamic SQL.
2199 -- Arguments
2200 -- Test_Func_Name Name of the testing function.
2201 -- Param_List Minimal list of parameters necessary for the
2202 -- rule this derivation is part of.
2203 -- Target_Column_Type Specifies the type of the column.
2204 -- Derivation_Text Text of the derivation to test.
2205 -- Derivation_Id ID of derivation being tested.
2206 -- My_Appl APPLSYS schema, which is needed for ad_ddl.
2207 -- Example
2208 -- GCS_LEX_MAP_PKG.Test_Deriv
2209 -- ('gcs_lex_get_111_123',
2210 -- 'segment1 varchar2, acct_date date',
2211 -- 'V',
2212 -- 'gcs_rv_ := ''ABC'' || segment1; ');
2213 -- Notes
2214 --
2215 PROCEDURE Test_Deriv( Test_Func_Name VARCHAR2,
2216 Param_List VARCHAR2,
2217 Target_Column_Type VARCHAR2,
2218 Derivation_Text VARCHAR2,
2219 Derivation_Id NUMBER,
2220 My_Appl VARCHAR2)
2221 IS
2222 module VARCHAR2(60);
2223
2224 rv_declaration VARCHAR2(50);
2225 BEGIN
2226 module := g_api || '.Test_Deriv';
2227
2228 IF target_column_type = 'N' THEN
2229 rv_declaration := 'NUMBER IS ' || g_ret_val || ' NUMBER;';
2230 ELSIF target_column_type = 'D' THEN
2231 rv_declaration := 'DATE IS ' || g_ret_val || ' DATE;';
2232 ELSE
2233 rv_declaration := 'VARCHAR2 IS ' || g_ret_val || ' VARCHAR2(32767);';
2234 END IF;
2235
2236 IF create_ad('CREATE OR REPLACE FUNCTION ' || test_func_name ||
2237 '(' || param_list || ') RETURN ' || rv_declaration ||
2238 'BEGIN ' || derivation_text ||
2239 'return ' || g_ret_val || ';' ||
2240 'END ' || test_func_name || ';',
2241 test_func_name, my_appl) <> 'FALSE' THEN
2242 raise gcs_lex_func_failed;
2243 END IF;
2244
2245 remove_function(test_func_name);
2246 EXCEPTION
2247 WHEN OTHERS THEN
2248 add_deriv_failed_msg(derivation_id, 'GCS_IDT_DERIVATION_FAILED',
2249 module, 'N');
2250 remove_function(test_func_name);
2251 END Test_Deriv;
2252
2253 --
2254 -- Function
2255 -- Create_Get_Function
2256 -- Purpose
2257 -- Creates the text for a PL/SQL function which will take a number of
2258 -- inputs and return the appropriate target value for the given rule.
2259 -- Then applies it to the database using dynamic SQL. Returns 'Y' or 'N'
2260 -- depending on the success of creating the function.
2261 -- Arguments
2262 -- Rule_Set_Id Rule Set for which the function should be created.
2263 -- Rule_Id Rule for which the function should be created.
2264 -- My_Appl APPLSYS schema, which is needed for ad_ddl.
2265 -- Example
2266 -- GCS_LEX_MAP_PKG.Create_Get_Function(111, 123, 'APPLSYS')
2267 -- Notes
2268 --
2269 FUNCTION Create_Get_Function( Rule_Set_Id NUMBER,
2270 Rule_Id NUMBER,
2271 My_Appl VARCHAR2) RETURN VARCHAR2 IS
2272 func_body VARCHAR2(32767);
2273
2274 CURSOR all_derivs IS
2275 SELECT d.derivation_id, d.derivation_type_code
2276 FROM gcs_lex_map_derivations d
2277 WHERE d.rule_id = create_get_function.rule_id
2278 ORDER BY d.derivation_sequence;
2279
2280 CURSOR check_condition_c(c_deriv_id NUMBER) IS
2281 SELECT 1
2282 FROM gcs_lex_map_conditions c
2283 WHERE c.derivation_id = c_deriv_id;
2284
2285 dummy NUMBER;
2286
2287 num_derivs NUMBER := 0;
2288 cond_exists BOOLEAN;
2289
2290 target_col_name VARCHAR2(30);
2291 target_column_type VARCHAR2(30);
2292 target_write_flag VARCHAR2(1);
2293
2294 -- wrapper function name for this rule, and parameter list
2295 func_name VARCHAR2(30);
2296 param_list VARCHAR2(8000);
2297
2298 default_condition_made VARCHAR2(1);
2299
2300 exc_txt1 VARCHAR2(500);
2301 exc_txt2 VARCHAR2(250);
2302
2303 -- If an exception occurs, pass back some meaningless value, since it
2304 -- will be rolled back anyway.
2305 default_return VARCHAR2(20);
2306
2307 module VARCHAR2(60);
2308 BEGIN
2309 module := g_api || '.Create_Get_Function';
2310 func_name :='gcs_lex_get_' || rule_set_id || '_' || rule_id;
2311 default_condition_made := 'N';
2312
2313 exc_txt1 :=
2314 'declare error_info GCS_LEX_MAP_API_PKG.error_record_type;' ||
2315 'begin error_info.rule_id := ' || rule_id || ';' ||
2316 'error_info.deriv_num := ' || g_deriv_num || ';' ||
2317 'error_info.row_id := ' || func_name || '.row_id;' ||
2318 'error_info.error_code := ''';
2319
2320 exc_txt2 :=
2321 ''';' ||
2322 'GCS_LEX_MAP_API_PKG.error_table(GCS_LEX_MAP_API_PKG' ||
2323 '.error_table.COUNT+1):=error_info;' ||
2324 'end;';
2325
2326 validation_check(rule_id, 'N');
2327
2328 param_list := create_param_list(rule_id, 'F', 'N');
2329
2330 SELECT mc.column_name, mc.column_type_code, mc.write_flag
2331 INTO target_col_name, target_column_type, target_write_flag
2332 FROM gcs_lex_map_rules r,
2333 gcs_lex_map_columns mc
2334 WHERE r.target_column_id = mc.column_id
2335 AND r.rule_id = create_get_function.rule_id;
2336
2337 IF target_write_flag <> 'Y' THEN
2338 raise gcs_lex_read_only_column_rule;
2339 END IF;
2340
2341 -- first get the function header information
2342 func_body := 'CREATE OR REPLACE FUNCTION ' || func_name || '(' ||
2343 param_list || ') RETURN ';
2344 IF target_column_type = 'N' THEN
2345 default_return := 'return 0;';
2346 func_body := func_body || 'NUMBER IS ' || g_ret_val || ' NUMBER;';
2347 ELSIF target_column_type = 'D' THEN
2348 default_return := 'return sysdate;';
2349 func_body := func_body || 'DATE IS ' || g_ret_val || ' DATE;';
2350 ELSE
2351 default_return := 'return ''a'';';
2352 func_body := func_body || 'VARCHAR2 IS '||g_ret_val||' VARCHAR2(32767);';
2353 END IF;
2354
2355 func_body := func_body || g_deriv_num || ' NUMBER := -1;BEGIN ';
2356
2357 FOR cond_deriv IN all_derivs LOOP
2358 IF default_condition_made = 'Y' THEN
2359 raise gcs_lex_def_cond_not_last;
2360 END IF;
2361
2362 -- Find if any conditions exist for the derivation
2363 OPEN check_condition_c(cond_deriv.derivation_id);
2364 FETCH check_condition_c INTO dummy;
2365 cond_exists := check_condition_c%FOUND;
2366 CLOSE check_condition_c;
2367
2368 IF num_derivs = 0 THEN
2369 IF cond_exists THEN
2370 func_body :=
2371 func_body || g_ret_val || ':=' || target_col_name || ';' ||
2372 'IF ' || create_condition(cond_deriv.derivation_id) || ' THEN ';
2373 ELSE
2374 default_condition_made := 'Y';
2375 END IF;
2376 ELSIF NOT cond_exists THEN
2377 func_body := func_body || 'ELSE ';
2378 default_condition_made := 'Y';
2379 ELSE
2380 func_body := func_body || 'ELSIF ' ||
2381 create_condition(cond_deriv.derivation_id) || ' THEN ';
2382 END IF;
2383
2384 -- register that we are entering this derivation number in case an
2385 -- exception is raised. Then, perform the derivation.
2386 func_body := func_body || g_deriv_num || ':=' ||
2387 to_char(num_derivs+1) || ';';
2388
2389 IF cond_deriv.derivation_type_code = 'LUT' THEN
2390 func_body := func_body || create_lookup_derivation
2391 (func_name, cond_deriv.derivation_id,
2392 'TRANSFORMATION');
2393 ELSIF cond_deriv.derivation_type_code = 'STR' THEN
2394 func_body := func_body || create_string_derivation
2395 (cond_deriv.derivation_id);
2396 ELSE
2397 func_body := func_body || create_plsql_derivation
2398 (cond_deriv.derivation_id, 'TRANSFORMATION');
2399 END IF;
2400
2401 num_derivs := num_derivs + 1;
2402 END LOOP;
2403
2404 IF num_derivs = 0 THEN
2405 raise gcs_lex_rule_no_derivation;
2406 -- if there was an 'IF' statement, then finish that 'IF' statement
2407 ELSIF num_derivs > 1 OR cond_exists THEN
2408 func_body := func_body || 'END IF;';
2409 END IF;
2410
2411 func_body := func_body || 'return ' || g_ret_val ||
2412 ';EXCEPTION ' ||
2413 'WHEN TOO_MANY_ROWS THEN ' ||
2414 exc_txt1 || g_error_lookup_tmr || exc_txt2 || default_return||
2415 'WHEN NO_DATA_FOUND THEN ' ||
2416 exc_txt1 || g_error_lookup_ndf || exc_txt2 || default_return||
2417 'WHEN VALUE_ERROR THEN ' ||
2418 exc_txt1 || g_error_type_mismatch ||exc_txt2||default_return||
2419 'WHEN OTHERS THEN ' ||
2420 exc_txt1 || g_error_unexpected || exc_txt2 || default_return||
2421 'END ' || func_name || ';';
2422
2423 -- Print out the function body to the log repository if appropriate.
2424 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT THEN
2425 FND_LOG.string(FND_LOG.LEVEL_EVENT, module, func_body);
2426 END IF;
2427
2428 IF create_ad(func_body, func_name, my_appl) <> 'FALSE' THEN
2429 raise gcs_lex_func_failed;
2430 END IF;
2431
2432 return 'Y';
2433 EXCEPTION
2434 WHEN gcs_lex_read_only_column_rule THEN
2435 add_rule_failed_msg(rule_id, 'GCS_IDT_READ_ONLY_COLUMN_RULE',
2436 module, 'N');
2437 return 'N';
2438 WHEN gcs_lex_def_cond_not_last THEN
2439 add_rule_failed_msg(rule_id, 'GCS_IDT_DEF_COND_NOT_LAST', module, 'N');
2440 return 'N';
2441 WHEN gcs_lex_rule_no_derivation THEN
2442 add_rule_failed_msg(rule_id, 'GCS_IDT_RULE_NO_DERIVATION', module, 'N');
2443 return 'N';
2444 WHEN gcs_lex_param_list_failed OR
2445 gcs_lex_create_cond_failed OR
2446 gcs_lex_derivation_failed OR
2447 gcs_lex_valid_check_failed THEN
2448 return 'N';
2449 WHEN gcs_lex_func_failed THEN
2450 FOR cond_deriv IN all_derivs LOOP
2451 -- Check for errors in each derivation
2452 IF cond_deriv.derivation_type_code = 'LUT' THEN
2453 test_deriv
2454 (func_name, param_list, target_column_type,
2455 create_lookup_derivation(func_name, cond_deriv.derivation_id,
2456 'TRANSFORMATION'),
2457 cond_deriv.derivation_id, my_appl);
2458 ELSIF cond_deriv.derivation_type_code = 'STR' THEN
2459 test_deriv(func_name, param_list, target_column_type,
2460 create_string_derivation(cond_deriv.derivation_id),
2461 cond_deriv.derivation_id, my_appl);
2462 ELSIF cond_deriv.derivation_type_code = 'PLS' THEN
2463 test_deriv(func_name, param_list, target_column_type,
2464 create_plsql_derivation(cond_deriv.derivation_id,
2465 'TRANSFORMATION'),
2466 cond_deriv.derivation_id, my_appl);
2467 END IF;
2468 END LOOP;
2469 return 'N';
2470 WHEN OTHERS THEN
2471 add_rule_failed_msg(rule_id, 'GCS_IDT_RULE_UNEXPECTED', module, 'N');
2472 return 'N';
2473 END Create_Get_Function;
2474
2475 --
2476 -- Function
2477 -- Validate_Column
2478 -- Purpose
2479 -- Validates the values in a column after a rule stage has been applied.
2480 -- Returns 'Y' if the validation succeeded, and 'N' if it failed.
2481 -- Arguments
2482 -- Rule_Id Rule ID for the validation.
2483 -- Target_Col_Name Column to validate.
2484 -- Validation_Type_Code 'L' for lookup validation, 'V' for value set
2485 -- validation.
2486 -- Lookup_Table_Name Name of the lookup table if this is a lookup
2487 -- validation.
2488 -- Value_Set_Id ID for the value set if this is a value set
2489 -- validation.
2490 -- Staging_Table_Name Staging table to validate.
2491 -- Filter_Text Text of the filter criteria
2492 -- Example
2493 -- str := GCS_LEX_MAP_PKG.Validate_Column(12345, 'segment1', 'L',
2494 -- 'my_lookup_tbl', null,
2495 -- 'gl_interface', 'group_id',
2496 -- '1000')
2497 -- Notes
2498 --
2499 FUNCTION Validate_Column( Rule_Id NUMBER,
2500 Target_Col_Name VARCHAR2,
2501 Validation_Type_Code VARCHAR2,
2502 Lookup_Table_Name VARCHAR2,
2503 Value_Set_Id NUMBER,
2504 Staging_Table_Name VARCHAR2,
2505 Filter_Text VARCHAR2)
2506 RETURN VARCHAR2
2507 IS
2508 TYPE error_cursor_type IS REF CURSOR;
2509 error_cv error_cursor_type;
2510 error_rec error_record_type;
2511
2512 validation_text VARCHAR2(32767);
2513 filter_clause VARCHAR2(200);
2514
2515 vs_validation_type VARCHAR2(50);
2516
2517 tv_table_name VARCHAR2(250);
2518 tv_column_name VARCHAR2(250);
2519 tv_enabled_column VARCHAR2(250);
2520 tv_summary_flag VARCHAR2(1);
2521 tv_summary_column VARCHAR2(250);
2522 tv_where_clause LONG;
2523
2524 module VARCHAR2(60);
2525 BEGIN
2526 module := g_api || '.Validate_Column';
2527 filter_clause := '';
2528
2529 validation_check(rule_id, 'Y');
2530
2531 validation_text := 'SELECT rowid FROM ' || staging_table_name ||
2532 ' stg WHERE ';
2533
2534 IF filter_text IS NOT NULL THEN
2535 filter_clause := ' AND ' || filter_text;
2536 END IF;
2537
2538 IF validation_type_code = 'V' THEN
2539 -- validation type is 'I' for independent, and 'F' for table-validated.
2540 SELECT ffvs.validation_type
2541 INTO vs_validation_type
2542 FROM fnd_flex_value_sets ffvs
2543 WHERE ffvs.flex_value_set_id = validate_column.value_set_id;
2544
2545 IF vs_validation_type = 'I' THEN
2546 validation_text := validation_text || 'NOT EXISTS ' ||
2547 '(SELECT 1 FROM fnd_flex_values ffv ' ||
2548 'WHERE ffv.flex_value_set_id=' || value_set_id ||
2549 ' AND ffv.flex_value = stg.' || target_col_name ||
2550 ' AND ffv.summary_flag = ''N'' ' ||
2551 'AND ffv.enabled_flag = ''Y'')' || filter_clause;
2552 ELSE -- table-validated value set
2553 -- extra info for table-validated value sets.
2554 SELECT ffvt.application_table_name,
2555 ffvt.value_column_name,
2556 ffvt.enabled_column_name,
2557 ffvt.summary_allowed_flag,
2558 ffvt.summary_column_name,
2559 ffvt.additional_where_clause
2560 INTO tv_table_name,
2561 tv_column_name,
2562 tv_enabled_column,
2563 tv_summary_flag,
2564 tv_summary_column,
2565 tv_where_clause
2566 FROM fnd_flex_validation_tables ffvt
2567 WHERE ffvt.flex_value_set_id = validate_column.value_set_id;
2568
2569 validation_text := validation_text || 'NOT EXISTS ' ||
2570 '(SELECT 1 FROM (SELECT * FROM ' || tv_table_name ||
2571 ' ' || tv_where_clause || ') ffv WHERE ffv.' ||
2572 tv_column_name || '=stg.' || target_col_name ||
2573 ' AND ' || tv_enabled_column || '=''Y''';
2574
2575 -- add the summary flag information if applicable
2576 IF tv_summary_flag = 'Y' THEN
2577 validation_text := validation_text || ' AND ' || tv_summary_column ||
2578 '=''N''';
2579 END IF;
2580
2581 validation_text := validation_text || ')' || filter_clause;
2582 END IF;
2583
2584 OPEN error_cv FOR validation_text;
2585 LOOP
2586 FETCH error_cv INTO error_rec.row_id;
2587 EXIT WHEN error_cv%NOTFOUND;
2588 error_rec.rule_id := rule_id;
2589 error_rec.deriv_num := null;
2590 error_rec.error_code := g_error_vsv_failed;
2591 error_table(error_table.COUNT + 1) := error_rec;
2592 END LOOP;
2593 CLOSE error_cv;
2594
2595 IF error_table.COUNT > 0 THEN
2596 raise gcs_lex_fail_vs_validation;
2597 END IF;
2598 ELSE -- validation_type_code = 'L'
2599 validation_text := validation_text ||
2600 'NOT EXISTS (SELECT 1 FROM ' || lookup_table_name ||
2601 ' lut WHERE stg.' || target_col_name ||
2602 '=lut.lookup_code)' || filter_clause;
2603 OPEN error_cv FOR validation_text;
2604 LOOP
2605 FETCH error_cv INTO error_rec.row_id;
2606 EXIT WHEN error_cv%NOTFOUND;
2607 error_rec.rule_id := rule_id;
2608 error_rec.deriv_num := null;
2609 error_rec.error_code := g_error_lutv_failed;
2610 error_table(error_table.COUNT + 1) := error_rec;
2611 END LOOP;
2612 CLOSE error_cv;
2613
2614 IF error_table.COUNT > 0 THEN
2615 raise gcs_lex_fail_lut_validation;
2616 END IF;
2617 END IF;
2618
2619 return 'Y';
2620 EXCEPTION
2621 WHEN gcs_lex_valid_check_failed THEN
2622 return 'N';
2623 WHEN gcs_lex_fail_vs_validation THEN
2624 add_value_set_failed_msg(rule_id, 'GCS_IDT_FAIL_VS_VALIDATION',
2625 module, 'Y');
2626 return 'N';
2627 WHEN gcs_lex_fail_lut_validation THEN
2628 add_rule_lut_failed_msg(rule_id, 'GCS_IDT_FAIL_LUT_VALIDATION',
2629 module, 'Y');
2630 return 'N';
2631 WHEN OTHERS THEN
2632 add_rule_failed_msg(rule_id, 'GCS_IDT_UNEXP_VALID_ERROR', module, 'Y');
2633 return 'N';
2634 END Validate_Column;
2635
2636 --
2637 -- Procedure
2638 -- Validate_Results
2639 -- Purpose
2640 -- Validates the results for a rule stage.
2641 -- Arguments
2642 -- Rule_Stage_Id ID of the Rule Stage whose results should
2643 -- be validated.
2644 -- Staging_Table_Name Staging table on which the rule stage
2645 -- was applied.
2646 -- Filter_Text Text of the filter criteria.
2647 -- Example
2648 -- GCS_LEX_MAP_PKG.Validate_Results(134,'gl_interface','group_id=''1000''')
2649 -- Notes
2650 --
2651 PROCEDURE Validate_Results( Rule_Stage_Id NUMBER,
2652 Staging_Table_Name VARCHAR2,
2653 Filter_Text VARCHAR2)
2654 IS
2655 CURSOR all_validations IS
2656 SELECT r.rule_id rule_id,
2657 tgtmc.column_name target_col_name,
2658 r.validation_type_code validation_type_code,
2659 lutms.structure_name lookup_table_name,
2660 r.value_set_id value_set_id
2661 FROM gcs_lex_map_rules r,
2662 gcs_lex_map_columns tgtmc,
2663 gcs_lex_map_structs lutms
2664 WHERE r.rule_stage_id = validate_results.rule_stage_id
2665 AND r.target_column_id = tgtmc.column_id
2666 AND r.lookup_table_id = lutms.structure_id (+)
2667 AND r.validation_type_code <> 'N';
2668
2669 valid_failed VARCHAR2(1);
2670
2671 module VARCHAR2(60);
2672 BEGIN
2673 module := g_api || '.Validate_Results';
2674 valid_failed := 'N';
2675
2676 log_file_module_write(module, g_module_enter);
2677
2678 FOR validation IN all_validations LOOP
2679 IF validate_column( validation.rule_id,
2680 validation.target_col_name,
2681 validation.validation_type_code,
2682 validation.lookup_table_name,
2683 validation.value_set_id,
2684 staging_table_name,
2685 filter_text) <> 'Y' THEN
2686 valid_failed := 'Y';
2687 END IF;
2688 END LOOP;
2689
2690 IF valid_failed = 'Y' THEN
2691 raise gcs_lex_validation_failed;
2692 END IF;
2693
2694 log_file_module_write(module, g_module_success);
2695 EXCEPTION
2696 WHEN OTHERS THEN
2697 log_file_module_write(module, g_module_failure);
2698 raise;
2699 END Validate_Results;
2700
2701 --
2702 -- Procedure
2703 -- Apply_Stage
2704 -- Purpose
2705 -- Applies the rules for a stage to the staging table
2706 -- Arguments
2707 -- Rule_Set_Id ID of the Rule Set that contains this stage.
2708 -- Rule_Stage_Id ID of the stage that is to be applied.
2709 -- Stage_Num Sequence value of the stage.
2710 -- Staging_Table_Name Staging table to which the IDT will be applied.
2711 -- Filter_Text Text of the filter criteria.
2712 -- Num_Rows_Affected Number of rows affected in previous stage
2713 -- passed in, or -1 if this is the first stage. It
2714 -- will pass back the number of rows affected in
2715 -- this stage. If there is a discrepancy, an
2716 -- exception is raised.
2717 -- Debug_Mode Whether or not debug information should be
2718 -- written to the log file.
2719 -- Example
2720 -- GCS_LEX_MAP_PKG.Apply_Stage(111, 123, 1, 'gl_interface',
2721 -- 'group_id=''1000''', nrows)
2722 -- Notes
2723 --
2724 PROCEDURE Apply_Stage( Rule_Set_Id NUMBER,
2725 Rule_Stage_Id NUMBER,
2726 Stage_Num NUMBER,
2727 Staging_Table_Name VARCHAR2,
2728 Filter_Text VARCHAR2,
2729 Num_Rows_Affected IN OUT NOCOPY NUMBER,
2730 Debug_Mode VARCHAR2)
2731 IS
2732 CURSOR all_stage_rules IS
2733 SELECT r.rule_id rule_id,
2734 mc.column_name column_name,
2735 mc.write_flag write_flag
2736 FROM gcs_lex_map_rules r,
2737 gcs_lex_map_columns mc
2738 WHERE r.rule_stage_id = apply_stage.rule_stage_id
2739 AND r.target_column_id = mc.column_id;
2740
2741 CURSOR check_func_c(c_func_name VARCHAR2) IS
2742 SELECT 1
2743 FROM user_objects
2744 WHERE object_name = c_func_name;
2745
2746 func_name VARCHAR2(50);
2747 dummy NUMBER;
2748
2749 stage_text VARCHAR2(32767);
2750 num_rules NUMBER := 0;
2751
2752 module VARCHAR2(60);
2753
2754 curr_num_rows_affected NUMBER;
2755 BEGIN
2756 module := g_api || '.Apply_Stage';
2757
2758 log_file_module_write(module, g_module_enter);
2759
2760 -- Put down in the log file which stage we are in.
2761 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XL_CURRENT_STAGE');
2762 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
2763 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.get);
2764
2765
2766 -- This savepoint is used to roll back to just before the last stage, so
2767 -- that information on the source values that led to some error can be
2768 -- retrieved.
2769 SAVEPOINT gcs_lex_before_stage;
2770
2771 stage_text := 'UPDATE ' || staging_table_name || ' stg';
2772
2773 FOR stage_rule IN all_stage_rules LOOP
2774 IF num_rules = 0 THEN
2775 stage_text := stage_text || ' SET ';
2776 ELSE
2777 stage_text := stage_text || ',';
2778 END IF;
2779 func_name := 'GCS_LEX_GET_' || rule_set_id || '_' || stage_rule.rule_id;
2780 OPEN check_func_c(func_name);
2781 FETCH check_func_c INTO dummy;
2782 IF check_func_c%NOTFOUND THEN
2783 CLOSE check_func_c;
2784 raise GCS_LEX_RULE_NO_FUNC;
2785 END IF;
2786 CLOSE check_func_c;
2787
2788 stage_text := stage_text || stage_rule.column_name || '=' || func_name ||
2789 '(' ||create_param_list(stage_rule.rule_id,'C','Y')|| ')';
2790 num_rules := num_rules + 1;
2791 END LOOP;
2792
2793 IF num_rules = 0 THEN
2794 raise gcs_lex_stage_no_rule;
2795 END IF;
2796
2797 IF filter_text IS NOT NULL THEN
2798 stage_text := stage_text || ' WHERE ' || filter_text;
2799 END IF;
2800
2801 -- Print the stage dynamic SQL text if appropriate.
2802 IF debug_mode = 'Y' THEN
2803 FND_FILE.PUT_LINE(FND_FILE.LOG, stage_text);
2804 END IF;
2805
2806 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT THEN
2807 FND_LOG.string(FND_LOG.LEVEL_EVENT, module, stage_text);
2808 END IF;
2809
2810 EXECUTE IMMEDIATE stage_text;
2811
2812 curr_num_rows_affected := SQL%ROWCOUNT;
2813
2814 IF num_rows_affected = -1 THEN
2815 num_rows_affected := curr_num_rows_affected;
2816 ELSIF num_rows_affected <> curr_num_rows_affected THEN
2817 raise gcs_lex_num_rows_changed;
2818 END IF;
2819
2820 IF error_table.COUNT > 0 THEN
2821 raise gcs_lex_stage_failed;
2822 END IF;
2823
2824 validate_results(rule_stage_id, staging_table_name, filter_text);
2825
2826 log_file_module_write(module, g_module_success);
2827 EXCEPTION
2828 WHEN gcs_lex_rule_no_func THEN
2829 add_simple_failed_msg('GCS_IDT_NO_RULE_FUNCTION', module, 'Y');
2830 log_file_module_write(module, g_module_failure);
2831 raise gcs_lex_stage_failed;
2832 WHEN gcs_lex_num_rows_changed THEN
2833 add_rows_changed_msg(rule_stage_id, curr_num_rows_affected,
2834 num_rows_affected, 'GCS_IDT_NUM_ROWS_CHANGED',
2835 module, 'Y');
2836 log_file_module_write(module, g_module_failure);
2837 raise gcs_lex_stage_failed;
2838 WHEN gcs_lex_stage_no_rule THEN
2839 add_stage_failed_msg(rule_stage_id, 'GCS_IDT_STAGE_NO_RULE',
2840 module, 'Y');
2841 log_file_module_write(module, g_module_failure);
2842 raise gcs_lex_stage_failed;
2843 WHEN gcs_lex_validation_failed THEN
2844 log_file_module_write(module, g_module_failure);
2845 raise gcs_lex_validation_failed;
2846 WHEN gcs_lex_stage_failed OR
2847 gcs_lex_param_list_failed THEN
2848 log_file_module_write(module, g_module_failure);
2849 raise gcs_lex_stage_failed;
2850 WHEN OTHERS THEN
2851 add_stage_failed_msg(rule_stage_id, 'GCS_IDT_UNEXP_STAGE_ERROR',
2852 module, 'Y');
2853 log_file_module_write(module, g_module_failure);
2854 raise gcs_lex_stage_failed;
2855 END Apply_Stage;
2856
2857 --
2858 -- Procedure
2859 -- Init_Error_Column
2860 -- Purpose
2861 -- Initializes the error column in the staging table by inserting 'NEW'.
2862 -- Arguments
2863 -- Rule_Set_Id ID of the Rule Set.
2864 -- Staging_Table_Name Name of the staging table.
2865 -- Filter_Text Text of the filter criteria.
2866 -- Example
2867 -- GCS_LEX_MAP_PKG.Init_Error_Column(111,'gl_interface','group_id=''100''')
2868 -- Notes
2869 --
2870 PROCEDURE Init_Error_Column( Rule_Set_Id NUMBER,
2871 Staging_Table_Name VARCHAR2,
2872 Filter_Text VARCHAR2)
2873 IS
2874 filter_where_clause VARCHAR2(200);
2875 error_col_name VARCHAR2(50);
2876
2877 module VARCHAR2(60);
2878 BEGIN
2879 module := g_api || '.Init_Error_Column';
2880 filter_where_clause := '';
2881
2882 IF filter_text IS NOT NULL THEN
2883 filter_where_clause := ' WHERE ' || filter_text;
2884 END IF;
2885
2886 SELECT mc.column_name
2887 INTO error_col_name
2888 FROM gcs_lex_map_columns mc,
2889 gcs_lex_map_rule_sets rsts
2890 WHERE mc.structure_id = rsts.structure_id
2891 AND rsts.rule_set_id = init_error_column.rule_set_id
2892 AND mc.error_code_column_flag = 'Y';
2893
2894 EXECUTE IMMEDIATE 'UPDATE ' || staging_table_name ||
2895 ' SET ' || error_col_name || '=''NEW''' ||
2896 filter_where_clause;
2897 EXCEPTION
2898 WHEN OTHERS THEN
2899 add_error_code_failed_msg(rule_set_id, staging_table_name,
2900 'GCS_IDT_ERROR_COLUMN_NOT_SET', module, 'Y');
2901 raise gcs_lex_error_column_not_set;
2902 END Init_Error_Column;
2903
2904 --
2905 -- Procedure
2906 -- Fill_Error_Column
2907 -- Purpose
2908 -- Fills out the error column in the staging table
2909 -- Arguments
2910 -- Rule_Set_Id ID of the Rule Set.
2911 -- Staging_Table_Name Name of the staging table.
2912 -- Filter_Text Text of the filter criteria.
2913 -- Example
2914 -- GCS_LEX_MAP_PKG.Fill_Error_Column(111,'gl_interface','group_id=''100''')
2915 -- Notes
2916 --
2917 PROCEDURE Fill_Error_Column( Rule_Set_Id NUMBER,
2918 Staging_Table_Name VARCHAR2,
2919 Filter_Text VARCHAR2)
2920 IS
2921 error_col VARCHAR2(50);
2922
2923 sql_text VARCHAR2(32767);
2924
2925 temp_rule_id NUMBER;
2926 temp_deriv_num NUMBER;
2927 temp_error_code VARCHAR2(10);
2928
2929 counter1 NUMBER;
2930
2931 module VARCHAR2(60);
2932
2933 error_message_name VARCHAR2(100);
2934 BEGIN
2935 module := g_api || '.Fill_Error_Column';
2936
2937 init_error_column(rule_set_id, staging_table_name, filter_text);
2938
2939 SELECT mc.column_name
2940 INTO error_col
2941 FROM gcs_lex_map_columns mc,
2942 gcs_lex_map_rule_sets rsts
2943 WHERE mc.structure_id = rsts.structure_id
2944 AND mc.error_code_column_flag = 'Y'
2945 AND rsts.rule_set_id = fill_error_column.rule_set_id;
2946
2947 sql_text := 'UPDATE ' || staging_table_name || ' SET ' || error_col ||
2948 '= decode(to_char(' || error_col || '), ''NEW'', '''', ' ||
2949 error_col || '||'','') || :error_code WHERE rowid=:myrow';
2950
2951 -- Here, we populate the error column, and remove all validation related
2952 -- messages (this is because the error_table is used next to write messages
2953 -- onto the message stack, and the validation messages would have already
2954 -- been written in validate_column().
2955 FOR i IN error_table.FIRST..error_table.LAST LOOP
2956 begin
2957 EXECUTE IMMEDIATE sql_text USING error_table(i).error_code,
2958 error_table(i).row_id;
2959 exception
2960 when others then
2961 null;
2962 end;
2963 IF error_table(i).ERROR_CODE IN (g_error_vsv_failed,
2964 g_error_lutv_failed) THEN
2965 error_table.delete(i);
2966 END IF;
2967 END LOOP;
2968
2969 -- In addition to filling the error column, we here create and add messages
2970 -- to the message stack for errors that occurred during processing of
2971 -- a transformation. The errors for validation failures are not written,
2972 -- since they would already have been written in the validate_column()
2973 -- procedure.
2974 counter1 := error_table.FIRST;
2975 WHILE counter1 IS NOT NULL LOOP
2976 IF error_table(counter1).error_code = g_error_lookup_tmr THEN
2977 error_message_name := 'GCS_IDT_LOOKUP_TOO_MANY_ROWS';
2978 ELSIF error_table(counter1).error_code = g_error_lookup_ndf THEN
2979 error_message_name := 'GCS_IDT_LOOKUP_NO_ROWS';
2980 ELSIF error_table(counter1).error_code = g_error_type_mismatch THEN
2981 error_message_name := 'GCS_IDT_TYPE_MISMATCH';
2982 ELSIF error_table(counter1).error_code = g_error_unexpected THEN
2983 error_message_name := 'GCS_IDT_UNEXP_PROC_ERROR';
2984 END IF;
2985
2986 add_deriv_proc_failed_msg(error_table(counter1).rule_id,
2987 error_table(counter1).deriv_num,
2988 error_message_name, module, 'Y');
2989
2990 -- Now we get rid of all errors listed that would create a duplicate
2991 -- of this error message. This is done to keep the number of messages
2992 -- listed to a sane number.
2993 temp_rule_id := error_table(counter1).rule_id;
2994 temp_deriv_num := error_table(counter1).deriv_num;
2995 temp_error_code := error_table(counter1).error_code;
2996
2997 counter1 := error_table.NEXT(counter1);
2998 WHILE counter1 IS NOT NULL AND
2999 temp_rule_id = error_table(counter1).rule_id AND
3000 temp_deriv_num = error_table(counter1).deriv_num AND
3001 temp_error_code = error_table(counter1).error_code LOOP
3002 counter1 := error_table.NEXT(counter1);
3003 END LOOP;
3004 END LOOP;
3005 EXCEPTION
3006 WHEN gcs_lex_error_column_not_set THEN
3007 null;
3008 WHEN OTHERS THEN
3009 add_error_code_failed_msg(rule_set_id, staging_table_name,
3010 'GCS_IDT_ERROR_COL_NOT_FILLED', module, 'Y');
3011 END Fill_Error_Column;
3012
3013
3014
3015 --
3016 -- PRIVATE PROCEDURES for Validation Rule Sets
3017 --
3018
3019 --
3020 -- Function
3021 -- Create_VRS_Get_Function
3022 -- Purpose
3023 -- Creates the text for a PL/SQL function which will take a number of
3024 -- inputs and return the validation status for a given rule. Then this
3025 -- will apply the text to the database using dynamic SQL. Returns 'Y' or
3026 -- 'N' depending on the success of creating the function.
3027 -- Arguments
3028 -- Rule_Set_Id Rule Set for which the function should be created.
3029 -- Rule_Id Rule for which the function should be created.
3030 -- My_Appl APPLSYS schema, which is needed for ad_ddl.
3031 -- Example
3032 -- GCS_LEX_MAP_PKG.Create_VRS_Get_Function(111, 123, 'APPLSYS')
3033 -- Notes
3034 --
3035 FUNCTION Create_VRS_Get_Function( Rule_Set_Id NUMBER,
3036 Rule_Id NUMBER,
3037 Rule_Name VARCHAR2,
3038 My_Appl VARCHAR2) RETURN VARCHAR2 IS
3039 func_body VARCHAR2(32767);
3040
3041 dummy NUMBER;
3042
3043 target_col_name VARCHAR2(30);
3044 validation_type VARCHAR2(30);
3045 deriv_id NUMBER;
3046 error_message CLOB;
3047
3048 -- wrapper function name for this rule, and parameter list
3049 func_name VARCHAR2(30);
3050 param_list VARCHAR2(8000);
3051
3052 log_error_text VARCHAR2(8000);
3053
3054 module VARCHAR2(60);
3055 BEGIN
3056 module := g_api || '.Create_VRS_Get_Function';
3057 func_name :='gcs_lex_get_' || rule_set_id || '_' || rule_id;
3058
3059 param_list := create_param_list(rule_id, 'F', 'N');
3060
3061 SELECT r.validation_type_code, d.derivation_id, r.error_message
3062 INTO validation_type, deriv_id, error_message
3063 FROM gcs_lex_map_rules r,
3064 gcs_lex_map_derivations d
3065 WHERE r.rule_id = create_vrs_get_function.rule_id
3066 AND d.rule_id = r.rule_id;
3067
3068 log_error_text :=
3069 'IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN ' ||
3070 'FND_LOG.string(FND_LOG.LEVEL_ERROR, ''GCS_LEX_MAP_API_PKG.' || func_name ||
3071 ''', ''' || REPLACE(error_message, '''', '''''') || '''); END IF; ';
3072
3073 -- first get the function header information
3074 func_body := 'CREATE OR REPLACE FUNCTION ' || func_name || '(' ||
3075 param_list || ') RETURN VARCHAR2 IS ';
3076
3077 IF validation_type = 'LOOKUP' THEN
3078 func_body := func_body || 'dummy NUMBER; ';
3079 END IF;
3080
3081 func_body := func_body || 'BEGIN ';
3082
3083
3084 -- Write the function body based on the validation type
3085 IF validation_type = 'CONDITION' THEN
3086 func_body := func_body || 'IF ' || create_condition(deriv_id) ||
3087 ' THEN ' || log_error_text || 'return ''EX51''; END IF; ';
3088 ELSIF validation_type = 'LOOKUP' THEN
3089 func_body := func_body || create_lookup_derivation(func_name, deriv_id, 'VALIDATION');
3090 ELSE -- PL/SQL validation
3091 func_body := func_body || 'IF nvl(' ||
3092 create_plsql_derivation(deriv_id, 'VALIDATION') ||
3093 ', ''F'') <> ''SUCCESS'' THEN ' || log_error_text ||
3094 'return ''EX53''; END IF; ';
3095 END IF;
3096
3097 func_body := func_body || 'return ''NEW''; EXCEPTION ';
3098
3099 IF validation_type = 'LOOKUP' THEN
3100 func_body := func_body || 'WHEN NO_DATA_FOUND THEN ' || log_error_text ||
3101 'return ''EX52''; ';
3102 END IF;
3103
3104 func_body := func_body || 'WHEN OTHERS THEN ' || log_error_text;
3105 IF validation_type = 'CONDITION' THEN
3106 func_body := func_body || 'return ''EX54''; ';
3107 ELSIF validation_type = 'LOOKUP' THEN
3108 func_body := func_body || 'return ''EX55''; ';
3109 ELSE -- PL/SQL validation
3110 func_body := func_body || 'return ''EX56''; ';
3111 END IF;
3112
3113 func_body := func_body || 'END ' || func_name || ';';
3114
3115
3116 -- Print out the function body to the log repository if appropriate.
3117 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT THEN
3118 FND_LOG.string(FND_LOG.LEVEL_EVENT, module, func_body);
3119 END IF;
3120
3121 IF create_ad(func_body, func_name, my_appl) <> 'FALSE' THEN
3122 raise gcs_lex_func_failed;
3123 END IF;
3124
3125 return 'Y';
3126 EXCEPTION
3127 WHEN gcs_lex_param_list_failed OR
3128 gcs_lex_create_cond_failed OR
3129 gcs_lex_derivation_failed THEN
3130 return 'N';
3131 WHEN gcs_lex_func_failed THEN
3132 add_vrs_rule_failed_msg(rule_name,'GCS_IDT_VRS_RULE_CREATE_ERR',module,'Y');
3133 return 'N';
3134 WHEN OTHERS THEN
3135 add_rule_failed_msg(rule_id, 'GCS_IDT_RULE_UNEXPECTED', module, 'N');
3136 return 'N';
3137 END Create_VRS_Get_Function;
3138
3139
3140
3141
3142 --
3143 -- PUBLIC PROCEDURES
3144 --
3145
3146 PROCEDURE Create_Map_Functions(
3147 p_init_msg_list VARCHAR2 DEFAULT NULL,
3148 x_return_status OUT NOCOPY VARCHAR2,
3149 x_msg_count OUT NOCOPY NUMBER,
3150 x_msg_data OUT NOCOPY VARCHAR2,
3151 p_rule_set_id NUMBER) IS
3152 CURSOR all_rules IS
3153 SELECT r.rule_id
3154 FROM gcs_lex_map_rule_stages rstg,
3155 gcs_lex_map_rules r
3156 WHERE rstg.rule_set_id = p_rule_set_id
3157 AND r.rule_stage_id = rstg.rule_stage_id;
3158
3159 idt_name VARCHAR2(30);
3160
3161 status VARCHAR2(1);
3162 industry VARCHAR2(1);
3163 my_appl VARCHAR2(30);
3164 app_flag BOOLEAN;
3165
3166 create_func_failure VARCHAR2(1);
3167
3168 v_init_msg_list VARCHAR2(100);
3169
3170 module VARCHAR2(60);
3171 BEGIN
3172 module := g_api || '.Create_Map_Functions';
3173 create_func_failure := 'N';
3174
3175 v_init_msg_list := nvl(p_init_msg_list, FND_API.G_FALSE);
3176
3177 x_return_status := FND_API.G_RET_STS_SUCCESS;
3178
3179 IF FND_API.to_boolean(v_init_msg_list) THEN
3180 FND_MSG_PUB.initialize;
3181 END IF;
3182
3183 idt_name := initial_rule_set_check(p_rule_set_id, 'TRANSFORMATION', 'N');
3184
3185 -- Get APPLSYS information. Needed for ad_ddl
3186 app_flag := fnd_installation.get_app_info('FND', status, industry,my_appl);
3187 IF NOT app_flag THEN
3188 raise gcs_lex_applsys_not_found;
3189 END IF;
3190
3191 -- Clear all functions previously associated with this rule set
3192 remove_all_functions(p_rule_set_id);
3193
3194 -- create a function for each of the rules in the rule set.
3195 FOR rule IN all_rules LOOP
3196 IF (create_get_function(p_rule_set_id, rule.rule_id,my_appl) <> 'Y') THEN
3197 create_func_failure := 'Y';
3198 END IF;
3199 END LOOP;
3200
3201 IF create_func_failure = 'Y' THEN
3202 raise gcs_lex_func_failure;
3203 END IF;
3204
3205 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false,
3206 p_count => x_msg_count,
3207 p_data => x_msg_data);
3208 EXCEPTION
3209 WHEN gcs_lex_init_failed THEN
3210 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3211 p_count => x_msg_count,
3212 p_data => x_msg_data);
3213 x_return_status := FND_API.G_RET_STS_ERROR;
3214 WHEN gcs_lex_disabled THEN
3215 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3216 p_count => x_msg_count,
3217 p_data => x_msg_data);
3218
3219 -- It is fine to run this for a disabled mapping. It will simply
3220 -- remove the unnecessary rule functions.
3221 x_return_status := FND_API.G_RET_STS_SUCCESS;
3222 WHEN gcs_lex_func_failure THEN
3223 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3224 p_count => x_msg_count,
3225 p_data => x_msg_data);
3226 x_return_status := FND_API.G_RET_STS_ERROR;
3227 WHEN gcs_lex_applsys_not_found THEN
3228 add_simple_failed_msg('GCS_APPLSYS_NOT_FOUND', module, 'N');
3229 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3230 p_count => x_msg_count,
3231 p_data => x_msg_data);
3232 x_return_status := FND_API.G_RET_STS_ERROR;
3233 WHEN OTHERS THEN
3234 add_id_value_failed_msg(p_rule_set_id, 'GCS_IDT_UNEXPECTED_ERROR',
3235 module, 'N');
3236 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3237 p_count => x_msg_count,
3238 p_data => x_msg_data);
3239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3240 END Create_Map_Functions;
3241
3242
3243 PROCEDURE Apply_Map(
3244 p_api_version NUMBER,
3245 p_init_msg_list VARCHAR2 DEFAULT NULL,
3246 p_commit VARCHAR2 DEFAULT NULL,
3247 p_validation_level NUMBER DEFAULT NULL,
3248 x_return_status OUT NOCOPY VARCHAR2,
3249 x_msg_count OUT NOCOPY NUMBER,
3250 x_msg_data OUT NOCOPY VARCHAR2,
3251 p_rule_set_id NUMBER,
3252 p_staging_table_name VARCHAR2,
3253 p_debug_mode VARCHAR2 DEFAULT NULL,
3254 p_filter_column_name1 VARCHAR2 DEFAULT NULL,
3255 p_filter_column_value1 VARCHAR2 DEFAULT NULL,
3256 p_filter_column_name2 VARCHAR2 DEFAULT NULL,
3257 p_filter_column_value2 VARCHAR2 DEFAULT NULL,
3258 p_filter_column_name3 VARCHAR2 DEFAULT NULL,
3259 p_filter_column_value3 VARCHAR2 DEFAULT NULL,
3260 p_filter_column_name4 VARCHAR2 DEFAULT NULL,
3261 p_filter_column_value4 VARCHAR2 DEFAULT NULL,
3262 p_filter_column_name5 VARCHAR2 DEFAULT NULL,
3263 p_filter_column_value5 VARCHAR2 DEFAULT NULL,
3264 p_filter_column_name6 VARCHAR2 DEFAULT NULL,
3265 p_filter_column_value6 VARCHAR2 DEFAULT NULL,
3266 p_filter_column_name7 VARCHAR2 DEFAULT NULL,
3267 p_filter_column_value7 VARCHAR2 DEFAULT NULL,
3268 p_filter_column_name8 VARCHAR2 DEFAULT NULL,
3269 p_filter_column_value8 VARCHAR2 DEFAULT NULL,
3270 p_filter_column_name9 VARCHAR2 DEFAULT NULL,
3271 p_filter_column_value9 VARCHAR2 DEFAULT NULL,
3272 p_filter_column_name10 VARCHAR2 DEFAULT NULL,
3273 p_filter_column_value10 VARCHAR2 DEFAULT NULL)
3274 IS
3275 -- Current version number of the lexical mapping API
3276 l_api_version NUMBER:= 1.0;
3277 l_api_name VARCHAR2(20);
3278
3279 CURSOR all_stages IS
3280 SELECT rule_stage_id, stage_number
3281 FROM gcs_lex_map_rule_stages rstg
3282 WHERE rstg.rule_set_id = p_rule_set_id
3283 ORDER BY stage_number;
3284
3285 idt_name VARCHAR2(30);
3286 filter_text VARCHAR2(5000);
3287
3288 num_rows_affected NUMBER := -1;
3289
3290 module VARCHAR2(60);
3291
3292 col_name VARCHAR2(30);
3293 stage_num NUMBER;
3294 vs_name VARCHAR2(60);
3295 lut_name VARCHAR2(50);
3296 error_value VARCHAR2(16000);
3297 param_list VARCHAR2(16000);
3298
3299 v_init_msg_list VARCHAR2(100);
3300 v_commit VARCHAR2(100);
3301 v_validation_level NUMBER;
3302 v_debug_mode VARCHAR2(100);
3303 BEGIN
3304 module := g_api || '.Apply_Map';
3305 l_api_name := 'Apply_Map';
3306
3307 v_init_msg_list := nvl(p_init_msg_list, FND_API.G_FALSE);
3308 v_commit := nvl(p_commit, FND_API.G_FALSE);
3309 v_validation_level := nvl(p_validation_level, FND_API.G_VALID_LEVEL_FULL);
3310 v_debug_mode := nvl(p_debug_mode, 'N');
3311
3312 log_file_module_write(module, g_module_enter);
3313
3314 x_return_status := FND_API.G_RET_STS_SUCCESS;
3315
3316 -- This savepoint is used to roll all the way back if an error occurs
3317 -- during processing of the IDT.
3318 SAVEPOINT gcs_lex_before_mapping;
3319
3320 IF NOT FND_API.compatible_api_call( l_api_version, p_api_version,
3321 l_api_name, g_api) THEN
3322 raise FND_API.g_exc_unexpected_error;
3323 END IF;
3324
3325 IF FND_API.to_boolean(v_init_msg_list) THEN
3326 FND_MSG_PUB.initialize;
3327 END IF;
3328
3329 idt_name := initial_rule_set_check(p_rule_set_id, 'TRANSFORMATION', 'Y');
3330 staging_table_check(p_staging_table_name, p_rule_set_id);
3331
3332 filter_text := get_filter_text(
3333 p_rule_set_id,
3334 'TRANSFORMATION',
3335 p_filter_column_name1, p_filter_column_value1,
3336 p_filter_column_name2, p_filter_column_value2,
3337 p_filter_column_name3, p_filter_column_value3,
3338 p_filter_column_name4, p_filter_column_value4,
3339 p_filter_column_name5, p_filter_column_value5,
3340 p_filter_column_name6, p_filter_column_value6,
3341 p_filter_column_name7, p_filter_column_value7,
3342 p_filter_column_name8, p_filter_column_value8,
3343 p_filter_column_name9, p_filter_column_value9,
3344 p_filter_column_name10, p_filter_column_value10);
3345
3346 write_header_log(idt_name, p_staging_table_name, filter_text);
3347
3348 init_error_column(p_rule_set_id, p_staging_table_name, filter_text);
3349
3350 -- apply each of the rule stages in the rule set
3351 FOR rule_stage IN all_stages LOOP
3352 apply_stage(p_rule_set_id,
3353 rule_stage.rule_stage_id,
3354 rule_stage.stage_number,
3355 p_staging_table_name,
3356 filter_text,
3357 num_rows_affected,
3358 v_debug_mode);
3359 END LOOP;
3360
3361 -- Put down in the log file how many rows were affected.
3362 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XL_NUM_ROWS_AFFECTED');
3363 FND_MESSAGE.set_token('NUM_ROWS', num_rows_affected);
3364 FND_MESSAGE.set_token('STG_TBL', p_staging_table_name);
3365 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.get);
3366
3367 IF FND_API.to_boolean(v_commit) THEN
3368 COMMIT;
3369 END IF;
3370
3371 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false,
3372 p_count => x_msg_count,
3373 p_data => x_msg_data);
3374
3375 gcs_lex_map_api_pkg.error_table.delete;
3376
3377 log_file_module_write(module, g_module_success);
3378
3379 write_header_output(idt_name, p_staging_table_name, filter_text);
3380 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_COMPLETED_SUCCESS');
3381 FND_MESSAGE.set_token('IDT_NAME', idt_name);
3382 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3383 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_NUM_ROWS_AFFECTED');
3384 FND_MESSAGE.set_token('NUM_ROWS', num_rows_affected);
3385 FND_MESSAGE.set_token('STG_TBL', p_staging_table_name);
3386 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3387
3388 write_tail_output;
3389 EXCEPTION
3390 WHEN gcs_lex_error_column_not_set OR
3391 gcs_lex_init_failed OR
3392 gcs_lex_table_check_failed OR
3393 gcs_lex_filter_error THEN
3394 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3395 p_count => x_msg_count,
3396 p_data => x_msg_data);
3397 x_return_status := FND_API.G_RET_STS_ERROR;
3398 gcs_lex_map_api_pkg.error_table.delete;
3399 log_file_module_write(module, g_module_failure);
3400 ROLLBACK TO gcs_lex_before_mapping;
3401 WHEN gcs_lex_disabled THEN
3402 add_idt_failed_msg(p_rule_set_id, 'GCS_IDT_DISABLED',
3403 'GCS_LEX_MAP_API_PKG.Initial_Rule_Set_Check', 'Y');
3404 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3405 p_count => x_msg_count,
3406 p_data => x_msg_data);
3407 x_return_status := FND_API.G_RET_STS_ERROR;
3408 gcs_lex_map_api_pkg.error_table.delete;
3409 log_file_module_write(module, g_module_failure);
3410 ROLLBACK TO gcs_lex_before_mapping;
3411 WHEN gcs_lex_validation_failed THEN
3412 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3413 p_count => x_msg_count,
3414 p_data => x_msg_data);
3415 IF error_table.COUNT > 0 THEN
3416 write_header_output(idt_name, p_staging_table_name, filter_text);
3417 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_COMPLETED_FAILURE');
3418 FND_MESSAGE.set_token('IDT_NAME', idt_name);
3419 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3420 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
3421 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
3422
3423 -- List all validation errors. No need to sort the error table since
3424 -- the entries would already be sorted.
3425 FOR i IN error_table.FIRST..error_table.LAST LOOP
3426 IF i = error_table.FIRST OR
3427 error_table(i).rule_id <> error_table(i-1).rule_id THEN
3428
3429 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
3430 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
3431
3432 IF error_table(i).error_code = g_error_vsv_failed THEN
3433 SELECT mc.column_name,
3434 rstg.stage_number,
3435 ffv.flex_value_set_name
3436 INTO col_name,
3437 stage_num,
3438 vs_name
3439 FROM gcs_lex_map_rules r,
3440 gcs_lex_map_columns mc,
3441 gcs_lex_map_rule_stages rstg,
3442 fnd_flex_value_sets ffv
3443 WHERE r.rule_stage_id = rstg.rule_stage_id
3444 AND r.value_set_id = ffv.flex_value_set_id
3445 AND r.target_column_id = mc.column_id
3446 AND r.rule_id = error_table(i).rule_id;
3447
3448 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_VSV_HEADER');
3449 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
3450 FND_MESSAGE.set_token('COL_NAME', col_name);
3451 FND_MESSAGE.set_token('VS_NAME', vs_name);
3452 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3453 ELSIF error_table(i).error_code = g_error_lutv_failed THEN
3454 SELECT mc.column_name,
3455 rstg.stage_number,
3456 lutms.structure_name
3457 INTO col_name,
3458 stage_num,
3459 lut_name
3460 FROM gcs_lex_map_rules r,
3461 gcs_lex_map_columns mc,
3462 gcs_lex_map_rule_stages rstg,
3463 gcs_lex_map_structs lutms
3464 WHERE r.rule_stage_id = rstg.rule_stage_id
3465 AND r.lookup_table_id = lutms.structure_id
3466 AND r.target_column_id = mc.column_id
3467 AND r.rule_id = error_table(i).rule_id;
3468
3469 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_LUTV_HEADER');
3470 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
3471 FND_MESSAGE.set_token('COL_NAME', col_name);
3472 FND_MESSAGE.set_token('LUT_NAME', lut_name);
3473 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3474 END IF;
3475
3476 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_SHORT_SEPARATOR');
3477 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3478 END IF;
3479
3480 EXECUTE IMMEDIATE 'SELECT ' || col_name || ' FROM ' ||
3481 p_staging_table_name || ' WHERE rowid = :row_id'
3482 INTO error_value
3483 USING error_table(i).row_id;
3484
3485 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, error_value);
3486 END LOOP;
3487
3488 write_tail_output;
3489
3490 ROLLBACK TO gcs_lex_before_mapping;
3491 fill_error_column(p_rule_set_id, p_staging_table_name, filter_text);
3492 ELSE
3493 ROLLBACK TO gcs_lex_before_mapping;
3494 END IF;
3495 x_return_status := FND_API.G_RET_STS_ERROR;
3496 gcs_lex_map_api_pkg.error_table.delete;
3497 log_file_module_write(module, g_module_failure);
3498 WHEN gcs_lex_stage_failed THEN
3499 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3500 p_count => x_msg_count,
3501 p_data => x_msg_data);
3502 IF error_table.COUNT > 0 THEN
3503 write_header_output(idt_name, p_staging_table_name, filter_text);
3504 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_COMPLETED_FAILURE');
3505 FND_MESSAGE.set_token('IDT_NAME', idt_name);
3506 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3507 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
3508 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
3509
3510 -- Sort out the error table by rule_id, deriv_num, and error_code. This
3511 -- allows us to group the errors together and show them in a more
3512 -- organized manner.
3513 qsort_error_table(error_table.FIRST, error_table.LAST);
3514
3515 ROLLBACK TO gcs_lex_before_stage;
3516
3517 -- List all transformation related errors. These should be listed after
3518 -- the rollback to before the stage took place, since we want the
3519 -- source values that caused these errors.
3520 FOR i IN error_table.FIRST..error_table.LAST LOOP
3521 IF i = error_table.FIRST OR
3522 error_table(i).rule_id <> error_table(i-1).rule_id OR
3523 error_table(i).deriv_num <> error_table(i-1).deriv_num OR
3524 error_table(i).error_code <> error_table(i-1).error_code THEN
3525
3526 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
3527 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
3528
3529 SELECT mc.column_name,
3530 rstg.stage_number
3531 INTO col_name,
3532 stage_num
3533 FROM gcs_lex_map_rules r,
3534 gcs_lex_map_rule_stages rstg,
3535 gcs_lex_map_columns mc
3536 WHERE r.rule_stage_id = rstg.rule_stage_id
3537 AND r.target_column_id = mc.column_id
3538 AND r.rule_id = error_table(i).rule_id;
3539
3540 IF error_table(i).error_code = g_error_lookup_tmr THEN
3541 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_TMR_HEADER');
3542 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
3543 FND_MESSAGE.set_token('COL_NAME', col_name);
3544 FND_MESSAGE.set_token('DERIV_NUM', error_table(i).deriv_num);
3545 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3546 ELSIF error_table(i).error_code = g_error_lookup_ndf THEN
3547 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_NR_HEADER');
3548 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
3549 FND_MESSAGE.set_token('COL_NAME', col_name);
3550 FND_MESSAGE.set_token('DERIV_NUM', error_table(i).deriv_num);
3551 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3552 ELSIF error_table(i).error_code = g_error_type_mismatch THEN
3553 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_TYPE_HEADER');
3554 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
3555 FND_MESSAGE.set_token('COL_NAME', col_name);
3556 FND_MESSAGE.set_token('DERIV_NUM', error_table(i).deriv_num);
3557 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3558 ELSIF error_table(i).error_code = g_error_unexpected THEN
3559 IF error_table(i).deriv_num = -1 THEN
3560 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_UNEXP_HEADER_NODRV');
3561 ELSE
3562 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_UNEXP_HEADER');
3563 END IF;
3564 FND_MESSAGE.set_token('STAGE_NUM', stage_num);
3565 FND_MESSAGE.set_token('COL_NAME', col_name);
3566 IF error_table(i).deriv_num <> -1 THEN
3567 FND_MESSAGE.set_token('DERIV_NUM', error_table(i).deriv_num);
3568 END IF;
3569 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3570 END IF;
3571
3572 FND_MESSAGE.set_name('GCS', 'GCS_IDT_XR_SHORT_SEPARATOR');
3573 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.get);
3574
3575 -- Get the header parameter list
3576 param_list := create_param_list(error_table(i).rule_id, 'H', 'Y');
3577 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, param_list);
3578 FOR j IN 1..LENGTH(param_list)/20 LOOP
3579 FND_FILE.PUT(FND_FILE.OUTPUT, '------------------- ');
3580 END LOOP;
3581 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
3582
3583 -- Get the query parameter list
3584 param_list := create_param_list(error_table(i).rule_id, 'Q', 'Y');
3585 END IF;
3586
3587 EXECUTE IMMEDIATE 'SELECT ' || param_list || ' FROM ' ||
3588 p_staging_table_name || ' WHERE rowid = :row_id'
3589 INTO error_value
3590 USING error_table(i).row_id;
3591
3592 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, error_value);
3593 END LOOP;
3594
3595 write_tail_output;
3596
3597 ROLLBACK TO gcs_lex_before_mapping;
3598 fill_error_column(p_rule_set_id, p_staging_table_name, filter_text);
3599 ELSE
3600 ROLLBACK TO gcs_lex_before_mapping;
3601 END IF;
3602 x_return_status := FND_API.G_RET_STS_ERROR;
3603 gcs_lex_map_api_pkg.error_table.delete;
3604 log_file_module_write(module, g_module_failure);
3605 WHEN OTHERS THEN
3606 add_id_value_failed_msg(p_rule_set_id, 'GCS_IDT_UNEXPECTED_ERROR',
3607 module, 'Y');
3608 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3609 p_count => x_msg_count,
3610 p_data => x_msg_data);
3611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3612 gcs_lex_map_api_pkg.error_table.delete;
3613 log_file_module_write(module, g_module_failure);
3614 ROLLBACK TO gcs_lex_before_mapping;
3615 END Apply_Map;
3616
3617
3618 PROCEDURE Create_Validation_Functions(
3619 p_init_msg_list VARCHAR2 DEFAULT NULL,
3620 x_return_status OUT NOCOPY VARCHAR2,
3621 x_msg_count OUT NOCOPY NUMBER,
3622 x_msg_data OUT NOCOPY VARCHAR2,
3623 p_rule_set_id NUMBER) IS
3624 CURSOR all_rules IS
3625 SELECT r.rule_id,
3626 r.rule_name
3627 FROM gcs_lex_map_rule_stages rstg,
3628 gcs_lex_map_rules r
3629 WHERE rstg.rule_set_id = p_rule_set_id
3630 AND r.rule_stage_id = rstg.rule_stage_id;
3631
3632 idt_name VARCHAR2(30);
3633
3634 status VARCHAR2(1);
3635 industry VARCHAR2(1);
3636 my_appl VARCHAR2(30);
3637 app_flag BOOLEAN;
3638
3639 create_func_failure VARCHAR2(1);
3640
3641 v_init_msg_list VARCHAR2(100);
3642
3643 module VARCHAR2(60);
3644 BEGIN
3645 module := g_api || '.Create_Validation_Functions';
3646 create_func_failure := 'N';
3647
3648 v_init_msg_list := nvl(p_init_msg_list, FND_API.G_FALSE);
3649
3650 x_return_status := FND_API.G_RET_STS_SUCCESS;
3651
3652 IF FND_API.to_boolean(v_init_msg_list) THEN
3653 FND_MSG_PUB.initialize;
3654 END IF;
3655
3656 idt_name := initial_rule_set_check(p_rule_set_id, 'VALIDATION', 'N');
3657
3658 -- Get APPLSYS information. Needed for ad_ddl
3659 app_flag := fnd_installation.get_app_info('FND', status, industry,my_appl);
3660 IF NOT app_flag THEN
3661 raise gcs_lex_applsys_not_found;
3662 END IF;
3663
3664 -- Clear all functions previously associated with this rule set
3665 remove_all_functions(p_rule_set_id);
3666
3667 -- create a function for each of the rules in the rule set.
3668 FOR rule IN all_rules LOOP
3669 IF (create_vrs_get_function(p_rule_set_id, rule.rule_id, rule.rule_name, my_appl) <> 'Y') THEN
3670 create_func_failure := 'Y';
3671 END IF;
3672 END LOOP;
3673
3674 IF create_func_failure = 'Y' THEN
3675 raise gcs_lex_func_failure;
3676 END IF;
3677
3678 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false,
3679 p_count => x_msg_count,
3680 p_data => x_msg_data);
3681 EXCEPTION
3682 WHEN gcs_lex_init_failed THEN
3683 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3684 p_count => x_msg_count,
3685 p_data => x_msg_data);
3686 x_return_status := FND_API.G_RET_STS_ERROR;
3687 WHEN gcs_lex_disabled THEN
3688 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3689 p_count => x_msg_count,
3690 p_data => x_msg_data);
3691
3692 -- It is fine to run this for a disabled mapping. It will simply
3693 -- remove the unnecessary rule functions.
3694 x_return_status := FND_API.G_RET_STS_SUCCESS;
3695 WHEN gcs_lex_func_failure THEN
3696 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3697 p_count => x_msg_count,
3698 p_data => x_msg_data);
3699 x_return_status := FND_API.G_RET_STS_ERROR;
3700 WHEN gcs_lex_applsys_not_found THEN
3701 add_simple_failed_msg('GCS_APPLSYS_NOT_FOUND', module, 'N');
3702 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3703 p_count => x_msg_count,
3704 p_data => x_msg_data);
3705 x_return_status := FND_API.G_RET_STS_ERROR;
3706 WHEN OTHERS THEN
3707 add_id_value_failed_msg(p_rule_set_id, 'GCS_IDT_UNEXPECTED_ERROR',
3708 module, 'N');
3709 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3710 p_count => x_msg_count,
3711 p_data => x_msg_data);
3712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3713 END Create_Validation_Functions;
3714
3715
3716
3717 PROCEDURE Apply_Validation(
3718 p_api_version NUMBER,
3719 p_init_msg_list VARCHAR2 DEFAULT NULL,
3720 p_commit VARCHAR2 DEFAULT NULL,
3721 p_validation_level NUMBER DEFAULT NULL,
3722 x_return_status OUT NOCOPY VARCHAR2,
3723 x_msg_count OUT NOCOPY NUMBER,
3724 x_msg_data OUT NOCOPY VARCHAR2,
3725 p_rule_set_id NUMBER,
3726 p_staging_table_name VARCHAR2,
3727 p_debug_mode VARCHAR2 DEFAULT NULL,
3728 p_filter_column_name1 VARCHAR2 DEFAULT NULL,
3729 p_filter_column_value1 VARCHAR2 DEFAULT NULL,
3730 p_filter_column_name2 VARCHAR2 DEFAULT NULL,
3731 p_filter_column_value2 VARCHAR2 DEFAULT NULL,
3732 p_filter_column_name3 VARCHAR2 DEFAULT NULL,
3733 p_filter_column_value3 VARCHAR2 DEFAULT NULL,
3734 p_filter_column_name4 VARCHAR2 DEFAULT NULL,
3735 p_filter_column_value4 VARCHAR2 DEFAULT NULL,
3736 p_filter_column_name5 VARCHAR2 DEFAULT NULL,
3737 p_filter_column_value5 VARCHAR2 DEFAULT NULL,
3738 p_filter_column_name6 VARCHAR2 DEFAULT NULL,
3739 p_filter_column_value6 VARCHAR2 DEFAULT NULL,
3740 p_filter_column_name7 VARCHAR2 DEFAULT NULL,
3741 p_filter_column_value7 VARCHAR2 DEFAULT NULL,
3742 p_filter_column_name8 VARCHAR2 DEFAULT NULL,
3743 p_filter_column_value8 VARCHAR2 DEFAULT NULL,
3744 p_filter_column_name9 VARCHAR2 DEFAULT NULL,
3745 p_filter_column_value9 VARCHAR2 DEFAULT NULL,
3746 p_filter_column_name10 VARCHAR2 DEFAULT NULL,
3747 p_filter_column_value10 VARCHAR2 DEFAULT NULL) IS
3748
3749 -- Current version number of the lexical mapping API
3750 l_api_version NUMBER:= 1.0;
3751 l_api_name VARCHAR2(20);
3752
3753 CURSOR all_rules IS
3754 SELECT r.rule_id, r.rule_name, r.validation_type_code
3755 FROM gcs_lex_map_rule_stages rstg,
3756 gcs_lex_map_rules r
3757 WHERE rstg.rule_set_id = p_rule_set_id
3758 AND r.rule_stage_id = rstg.rule_stage_id
3759 ORDER BY rstg.stage_number;
3760
3761 CURSOR check_func_c(c_func_name VARCHAR2) IS
3762 SELECT 1
3763 FROM user_objects
3764 WHERE object_name = c_func_name;
3765
3766 dummy NUMBER;
3767
3768 idt_name VARCHAR2(30);
3769 filter_text VARCHAR2(5000);
3770 error_col_name VARCHAR2(50);
3771
3772 val_text VARCHAR2(8000);
3773 rule_name VARCHAR2(100);
3774 func_name VARCHAR2(100);
3775
3776 num_rows_total NUMBER := -1;
3777
3778 v_init_msg_list VARCHAR2(100);
3779 v_commit VARCHAR2(100);
3780 v_validation_level NUMBER;
3781 v_debug_mode VARCHAR2(100);
3782
3783 module VARCHAR2(60);
3784 BEGIN
3785 module := g_api || '.Apply_Validation';
3786 l_api_name := 'Apply_Validation';
3787
3788 v_init_msg_list := nvl(p_init_msg_list, FND_API.G_FALSE);
3789 v_commit := nvl(p_commit, FND_API.G_FALSE);
3790 v_validation_level := nvl(p_validation_level, FND_API.G_VALID_LEVEL_FULL);
3791 v_debug_mode := nvl(p_debug_mode, 'N');
3792
3793 log_file_module_write(module, g_module_enter);
3794
3795 x_return_status := FND_API.G_RET_STS_SUCCESS;
3796
3797 IF NOT FND_API.compatible_api_call( l_api_version, p_api_version,
3798 l_api_name, g_api) THEN
3799 raise FND_API.g_exc_unexpected_error;
3800 END IF;
3801
3802 IF FND_API.to_boolean(v_init_msg_list) THEN
3803 FND_MSG_PUB.initialize;
3804 END IF;
3805
3806 idt_name := initial_rule_set_check(p_rule_set_id, 'VALIDATION', 'Y');
3807 staging_table_check(p_staging_table_name, p_rule_set_id);
3808
3809 filter_text := get_filter_text(
3810 p_rule_set_id,
3811 'VALIDATION',
3812 p_filter_column_name1, p_filter_column_value1,
3813 p_filter_column_name2, p_filter_column_value2,
3814 p_filter_column_name3, p_filter_column_value3,
3815 p_filter_column_name4, p_filter_column_value4,
3816 p_filter_column_name5, p_filter_column_value5,
3817 p_filter_column_name6, p_filter_column_value6,
3818 p_filter_column_name7, p_filter_column_value7,
3819 p_filter_column_name8, p_filter_column_value8,
3820 p_filter_column_name9, p_filter_column_value9,
3821 p_filter_column_name10, p_filter_column_value10);
3822
3823 --
3824 -- write_header_log(idt_name, p_staging_table_name, filter_text);
3825 --
3826
3827 init_error_column(p_rule_set_id, p_staging_table_name, filter_text);
3828
3829 -- Now get the error column name and add to the filter text
3830 SELECT mc.column_name
3831 INTO error_col_name
3832 FROM gcs_lex_map_rule_sets rs,
3833 gcs_lex_map_columns mc
3834 WHERE rs.rule_set_id = p_rule_set_id
3835 AND mc.structure_id = rs.structure_id
3836 AND mc.error_code_column_flag = 'Y';
3837
3838 IF filter_text IS NOT NULL THEN
3839 filter_text := filter_text || ' AND to_char(' || error_col_name || ') = ''NEW''';
3840 ELSE
3841 filter_text := 'to_char(' || error_col_name || ') = ''NEW''';
3842 END IF;
3843
3844
3845 -- apply each of the validation rules in the rule set
3846 FOR val_rule IN all_rules LOOP
3847 func_name := 'GCS_LEX_GET_' || p_rule_set_id || '_' || val_rule.rule_id;
3848
3849 OPEN check_func_c(func_name);
3850 FETCH check_func_c INTO dummy;
3851 IF check_func_c%NOTFOUND THEN
3852 CLOSE check_func_c;
3853 raise GCS_LEX_RULE_NO_FUNC;
3854 END IF;
3855 CLOSE check_func_c;
3856
3857 -- Depending on the validation type, perform an action
3858 IF val_rule.validation_type_code = 'PLSQL' THEN
3859 DELETE FROM gcs_lex_vrs_plsql_gt;
3860
3861 val_text :=
3862 'INSERT INTO gcs_lex_vrs_plsql_gt(associated_rowid, error_code) ' ||
3863 'SELECT rowid, ' || func_name || '(' ||
3864 create_param_list(val_rule.rule_id, 'C', 'Y') ||
3865 ') FROM ' || p_staging_table_name || ' WHERE ' || filter_text;
3866 ELSE
3867 val_text := 'UPDATE ' || p_staging_table_name || ' stg SET ' ||
3868 error_col_name || '=' || func_name ||
3869 '(' ||create_param_list(val_rule.rule_id,'C','Y')|| ') ' ||
3870 'WHERE ' || filter_text;
3871 END IF;
3872
3873 IF v_debug_mode = 'Y' THEN
3874 FND_FILE.PUT_LINE(FND_FILE.LOG, val_text);
3875 END IF;
3876
3877 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT THEN
3878 FND_LOG.string(FND_LOG.LEVEL_EVENT, module, val_text);
3879 END IF;
3880
3881 begin
3882 EXECUTE IMMEDIATE val_text;
3883 exception
3884 when others then
3885 rule_name := val_rule.rule_name;
3886 raise GCS_LEX_VRS_RULE_FAILED;
3887 end;
3888
3889 IF num_rows_total = -1 THEN
3890 num_rows_total := SQL%ROWCOUNT;
3891 IF num_rows_total = 0 THEN
3892 raise GCS_LEX_VRS_NO_ROWS;
3893 END IF;
3894 END IF;
3895
3896 IF val_rule.validation_type_code = 'PLSQL' THEN
3897 val_text :=
3898 'UPDATE ' || p_staging_table_name || ' stg SET ' || error_col_name ||
3899 '=(SELECT error_code FROM gcs_lex_vrs_plsql_gt plsgt ' ||
3900 'WHERE plsgt.associated_rowid = stg.rowid) ' ||
3901 'WHERE ' || filter_text;
3902
3903 IF v_debug_mode = 'Y' THEN
3904 FND_FILE.PUT_LINE(FND_FILE.LOG, val_text);
3905 END IF;
3906
3907 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT THEN
3908 FND_LOG.string(FND_LOG.LEVEL_EVENT, module, val_text);
3909 END IF;
3910
3911 EXECUTE IMMEDIATE val_text;
3912 END IF;
3913 END LOOP;
3914
3915 EXECUTE IMMEDIATE
3916 'SELECT decode(COUNT(*), ' || num_rows_total || ', ''' ||
3917 FND_API.G_RET_STS_SUCCESS || ''', ''' || FND_API.G_RET_STS_ERROR ||
3918 ''') FROM ' || p_staging_table_name || ' WHERE ' || filter_text
3919 INTO x_return_status;
3920
3921
3922 -- Put down in the log file how many rows were validated
3923 FND_MESSAGE.set_name('GCS', 'GCS_IDT_VRS_NUM_ROWS_AFFECTED');
3924 FND_MESSAGE.set_token('NUM_ROWS', num_rows_total);
3925 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT THEN
3926 FND_LOG.message(FND_LOG.LEVEL_EVENT, module);
3927 END IF;
3928
3929 IF v_debug_mode = 'Y' THEN
3930 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.get);
3931 END IF;
3932
3933 IF FND_API.to_boolean(v_commit) THEN
3934 COMMIT;
3935 END IF;
3936
3937 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false,
3938 p_count => x_msg_count,
3939 p_data => x_msg_data);
3940
3941 log_file_module_write(module, g_module_success);
3942 EXCEPTION
3943 WHEN gcs_lex_vrs_no_rows THEN
3944 add_simple_failed_msg('GCS_IDT_VRS_NO_ROWS', module, 'Y');
3945 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3946 p_count => x_msg_count,
3947 p_data => x_msg_data);
3948 x_return_status := FND_API.G_RET_STS_ERROR;
3949 log_file_module_write(module, g_module_failure);
3950 WHEN gcs_lex_vrs_rule_failed THEN
3951 add_vrs_rule_failed_msg(rule_name,'GCS_IDT_VRS_RULE_FAILED',module,'Y');
3952 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3953 p_count => x_msg_count,
3954 p_data => x_msg_data);
3955 x_return_status := FND_API.G_RET_STS_ERROR;
3956 log_file_module_write(module, g_module_failure);
3957 WHEN gcs_lex_rule_no_func THEN
3958 add_simple_failed_msg('GCS_IDT_NO_RULE_FUNCTION', module, 'Y');
3959 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3960 p_count => x_msg_count,
3961 p_data => x_msg_data);
3962 x_return_status := FND_API.G_RET_STS_ERROR;
3963 log_file_module_write(module, g_module_failure);
3964 WHEN gcs_lex_error_column_not_set OR
3965 gcs_lex_init_failed OR
3966 gcs_lex_table_check_failed OR
3967 gcs_lex_filter_error THEN
3968 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3969 p_count => x_msg_count,
3970 p_data => x_msg_data);
3971 x_return_status := FND_API.G_RET_STS_ERROR;
3972 log_file_module_write(module, g_module_failure);
3973 WHEN gcs_lex_disabled THEN
3974 add_idt_failed_msg(p_rule_set_id, 'GCS_IDT_DISABLED',
3975 'GCS_LEX_MAP_API_PKG.Initial_Rule_Set_Check', 'Y');
3976 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3977 p_count => x_msg_count,
3978 p_data => x_msg_data);
3979 x_return_status := FND_API.G_RET_STS_ERROR;
3980 log_file_module_write(module, g_module_failure);
3981 WHEN OTHERS THEN
3982 add_id_value_failed_msg(p_rule_set_id, 'GCS_IDT_UNEXPECTED_ERROR',
3983 module, 'Y');
3984 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
3985 p_count => x_msg_count,
3986 p_data => x_msg_data);
3987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3988 log_file_module_write(module, g_module_failure);
3989 END Apply_Validation;
3990
3991
3992 PROCEDURE After_FEM_Refresh IS
3993
3994 BEGIN
3995 UPDATE gcs_lex_map_rule_sets
3996 SET associated_object_id = fem_object_id_seq.nextval;
3997
3998 INSERT INTO fem_object_catalog_b(
3999 object_id, folder_id, object_type_code, object_access_code,
4000 object_origin_code, object_version_number, creation_date, created_by,
4001 last_update_date, last_updated_by, last_update_login)
4002 SELECT associated_object_id, 1300, 'CONSOLIDATION_DATA', 'R', 'USER', 1,
4003 sysdate, created_by, sysdate, last_updated_by, last_update_login
4004 FROM gcs_lex_map_rule_sets rst;
4005
4006 INSERT INTO fem_object_catalog_tl(
4007 object_id, object_name, language, source_lang, description, creation_date,
4008 created_by, last_update_date, last_updated_by, last_update_login)
4009 SELECT associated_object_id, rst.name, userenv('LANG'), userenv('LANG'),
4010 description, sysdate, created_by, sysdate, last_updated_by,
4011 last_update_login
4012 FROM gcs_lex_map_rule_sets rst;
4013
4014 INSERT INTO fem_object_definition_b(
4015 object_definition_id, object_id, effective_start_date, effective_end_date,
4016 object_origin_code, approval_status_code, old_approved_copy_flag,
4017 object_version_number, creation_date, created_by, last_update_date,
4018 last_updated_by, last_update_login)
4019 SELECT fem_object_definition_id_seq.nextval, associated_object_id,
4020 to_date('01-01-1000', 'DD-MM-YYYY'),
4021 to_date('31-12-9999', 'DD-MM-YYYY'), 'USER', 'NOT_APPLICABLE', 'N', 1,
4022 sysdate, created_by, sysdate, last_updated_by, last_update_login
4023 FROM gcs_lex_map_rule_sets rst;
4024
4025 INSERT INTO fem_object_definition_tl(
4026 object_definition_id, object_id, language, source_lang,
4027 old_approved_copy_flag, display_name, description, creation_date,
4028 created_by, last_update_date, last_updated_by, last_update_login)
4029 SELECT odb.object_definition_id, odb.object_id, userenv('LANG'),
4030 userenv('LANG'), 'N', rst.name, rst.description, sysdate,
4031 rst.created_by, sysdate, rst.last_updated_by, rst.last_update_login
4032 FROM gcs_lex_map_rule_sets rst,
4033 fem_object_definition_b odb
4034 WHERE odb.object_id = rst.associated_object_id;
4035
4036 END After_FEM_Refresh;
4037
4038
4039 END GCS_LEX_MAP_API_PKG;
[Home] [Help]