1 PACKAGE BODY Igs_Ad_Imp_023 AS
2 /* $Header: IGSADA1B.pls 120.1 2006/02/23 06:11:12 gmaheswa noship $ */
3 FUNCTION get_discrepancy_result (
4 p_attribute_name IN VARCHAR2, -- For Eg. 'SURNAME'
5 p_ad_col_value IN VARCHAR2, -- For Eg. 'Navin'
6 p_int_col_value IN VARCHAR2, -- For Eg. 'Navinkrs'
7 p_source_type_id IN NUMBER, -- For Eg. 27
8 p_category IN VARCHAR2 -- For Eg. 'PERSON'
9 ) RETURN VARCHAR2
10 AS
11 /*
12 || Created By : [email protected]
13 || Created On : 21-Jun-2001
14 || Purpose : This function gets the Discrepancy Rule for the column(i.e, p_attribute_name).
15 || Evaluates the Discrepancy Rule and based on it's value (i.e, 'I'- 'Import' or 'E'- 'Keep'),
16 || function returns either p_int_col_value or p_ad_col_value.
17 || Known limitations, enhancements or remarks : Overloaded for VARCHAR2 values.
18 || Change History :
19 || Who When What
20 || (reverse chronological order - newest change first)
21 */
22
23 l_vc_return_val VARCHAR2(2000);
24
25 -- Define cursor to get the Detail Level Discrepancy Rule.
26 CURSOR get_discrepancy_rule_cur IS
27 SELECT discrepancy_rule_cd
28 FROM igs_ad_dscp_attr
29 WHERE UPPER(attribute_name) = UPPER(p_attribute_name)
30 AND src_cat_id = (SELECT src_cat_id
31 FROM igs_ad_source_cat
32 WHERE source_type_id = p_source_type_id
33 AND category_name = p_category);
34 get_discrepancy_rule_rec get_discrepancy_rule_cur%ROWTYPE;
35
36 -- Evaluates the Discrepancy Rule
37
38 BEGIN
39 OPEN get_discrepancy_rule_cur;
40 FETCH get_discrepancy_rule_cur INTO get_discrepancy_rule_rec;
41 CLOSE get_discrepancy_rule_cur;
42
43 IF get_discrepancy_rule_rec.discrepancy_rule_cd = 'E' THEN
44 l_vc_return_val := p_ad_col_value ;
45 ELSIF get_discrepancy_rule_rec.discrepancy_rule_cd = 'I' THEN
46 l_vc_return_val := NVL(p_int_col_value,p_ad_col_value);
47 ELSE
48 l_vc_return_val := p_ad_col_value;
49 END IF;
50
51 RETURN l_vc_return_val;
52 END get_discrepancy_result;
53
54 FUNCTION get_discrepancy_result (
55 p_attribute_name IN VARCHAR2, -- For Eg. 'LEVEL_OF_QUAL_ID'
56 p_ad_col_value IN NUMBER, -- For Eg. 5
57 p_int_col_value IN NUMBER, -- For Eg. 22
58 p_source_type_id IN NUMBER, -- For Eg. 27
59 p_category IN VARCHAR2 -- For Eg. 'PERSON'
60 ) RETURN NUMBER
61 AS
62 /*
63 || Created By : [email protected]
64 || Created On : 21-Jun-2001
65 || Purpose : This function gets the Discrepancy Rule for the column(i.e, p_attribute_name).
66 || Evaluates the Discrepancy Rule and based on it's value (i.e, 'I'- 'Import' or 'E'- 'Keep'),
67 || function returns either p_int_col_value or p_ad_col_value.
68 || Known limitations, enhancements or remarks : Overloaded for NUMBER values.
69 || Change History :
70 || Who When What
71 || (reverse chronological order - newest change first)
72 */
73
74 l_n_return_val NUMBER;
75
76 -- Define cursor to get the Detail Level Discrepancy Rule.
77 CURSOR get_discrepancy_rule_cur IS
78 SELECT discrepancy_rule_cd
79 FROM igs_ad_dscp_attr
80 WHERE UPPER(attribute_name) = UPPER(p_attribute_name)
81 AND src_cat_id = (SELECT src_cat_id
82 FROM igs_ad_source_cat
83 WHERE source_type_id = p_source_type_id
84 AND category_name = p_category);
85 get_discrepancy_rule_rec get_discrepancy_rule_cur%ROWTYPE;
86
87 -- Evaluates the Discrepancy Rule
88
89 BEGIN
90 OPEN get_discrepancy_rule_cur;
91 FETCH get_discrepancy_rule_cur INTO get_discrepancy_rule_rec;
92 CLOSE get_discrepancy_rule_cur;
93
94 IF get_discrepancy_rule_rec.discrepancy_rule_cd = 'E' THEN
95 l_n_return_val := p_ad_col_value ;
96 ELSIF get_discrepancy_rule_rec.discrepancy_rule_cd = 'I' THEN
97 l_n_return_val := NVL(p_int_col_value,p_ad_col_value);
98 ELSE
99 l_n_return_val := p_ad_col_value;
100 END IF;
101
102 RETURN l_n_return_val;
103 END get_discrepancy_result;
104
105 FUNCTION get_discrepancy_result (
106 p_attribute_name IN VARCHAR2, -- For Eg. 'BIRTH_DT'
107 p_ad_col_value IN DATE, -- For Eg. SYSDATE
108 p_int_col_value IN DATE, -- For Eg. SYSDATE - 1
109 p_source_type_id IN NUMBER, -- For Eg. 27
110 p_category IN VARCHAR2 -- For Eg. 'PERSON'
111 ) RETURN DATE
112 AS
113 /*
114 || Created By : [email protected]
115 || Created On : 21-Jun-2001
116 || Purpose : This function gets the Discrepancy Rule for the column(i.e, p_attribute_name).
117 || Evaluates the Discrepancy Rule and based on it's value (i.e, 'I'- 'Import' or 'E'- 'Keep'),
118 || function returns either p_int_col_value or p_ad_col_value.
119 || Known limitations, enhancements or remarks : Overloaded for DATE values.
120 || Change History :
121 || Who When What
122 || (reverse chronological order - newest change first)
123 */
124
125 l_d_return_val DATE;
126
127 -- Define cursor to get the Detail Level Discrepancy Rule.
128 CURSOR get_discrepancy_rule_cur IS
129 SELECT discrepancy_rule_cd
130 FROM igs_ad_dscp_attr
131 WHERE UPPER(attribute_name) = UPPER(p_attribute_name)
132 AND src_cat_id = (SELECT src_cat_id
133 FROM igs_ad_source_cat
134 WHERE source_type_id = p_source_type_id
135 AND category_name = p_category);
136 get_discrepancy_rule_rec get_discrepancy_rule_cur%ROWTYPE;
137
138 -- Evaluates the Discrepancy Rule
139 BEGIN
140 OPEN get_discrepancy_rule_cur;
141 FETCH get_discrepancy_rule_cur INTO get_discrepancy_rule_rec;
142 CLOSE get_discrepancy_rule_cur;
143
144 IF get_discrepancy_rule_rec.discrepancy_rule_cd = 'E' THEN
145 l_d_return_val := p_ad_col_value;
146 ELSIF get_discrepancy_rule_rec.discrepancy_rule_cd = 'I' THEN
147 l_d_return_val := NVL(p_int_col_value,p_ad_col_value);
148 ELSE
149 l_d_return_val := p_ad_col_value;
150 END IF;
151
152 RETURN l_d_return_val;
153 END get_discrepancy_result;
154
155 FUNCTION prepare_pk_where_clause(
156 p_param1 VARCHAR2 DEFAULT NULL, -- First PK column Name.
157 p_param2 VARCHAR2 DEFAULT NULL, -- First PK column value.
158 p_param3 VARCHAR2 DEFAULT NULL, -- Second PK column Name.
159 p_param4 VARCHAR2 DEFAULT NULL, -- Second PK column value.
160 p_param5 VARCHAR2 DEFAULT NULL, -- Third PK column Name.
161 p_param6 VARCHAR2 DEFAULT NULL, -- Third PK column value.
162 p_param7 VARCHAR2 DEFAULT NULL, -- Forth PK column Name.
163 p_param8 VARCHAR2 DEFAULT NULL, -- Forth PK column value.
164 p_param9 VARCHAR2 DEFAULT NULL, -- Fifth PK column Name.
165 p_param10 VARCHAR2 DEFAULT NULL -- Fifth PK column value.
166 ) RETURN VARCHAR2
167 AS
168 /*
169 || Created By : [email protected]
170 || Created On : 21-Jun-2001
171 || Purpose : This function accepts a set of input Primary Key(PK) column names
172 || and their values in VARCHAR2 format and returns a string 'WHERE clause' values.
173 || Known limitations, enhancements or remarks : Currently this function supports only
174 || five column names along with their values.
175 || Change History :
176 || Who When What
177 || gmaheswa 23-Feb-2006 Stubbed As part of literal usage fix.
178 || (reverse chronological order - newest change first)
179 */
180 BEGIN
181 NULL;
182 END prepare_pk_where_clause;
183
184 FUNCTION find_detail_discrepancy_rule (
185 p_source_type_id IN NUMBER, -- For Eg. 27
186 p_category IN VARCHAR2, -- For Eg. 'PERSON'
187 p_int_pk_where_clause IN VARCHAR2, -- For Eg. 'INTERFACE_ID = 55'
188 p_ad_pk_where_clause IN VARCHAR2 -- For Eg. 'PERSON_ID = 9855582'
189 ) RETURN BOOLEAN
190 AS
191 /*
192 || Created By : [email protected]
193 || Created On : 21-Jun-2001
194 || Purpose : This function evaluates the 'Review' Discrepancy Rules at
195 || column for a category(i.e, p_category). If column level
196 || discrepancy exists this function returns TRUE otherwise it returns FALSE.
197 || Known limitations, enhancements or remarks : Uses REF cursor for checking the Detail
198 || Level Discrepancy Rule.
199 || Change History :
200 || Who When What
201 || pkpatel 18-DEC-2003 Bug 3091707 (Moved the log messages from FND_FILE to FND_LOG_REPOSITORY)
202 || gmaheswa 23-Feb-2006 Stubbed and created a new overloaded function. as part of literal usage fix.
203 || (reverse chronological order - newest change first)
204 */
205
206 BEGIN
207 null;
208 END find_detail_discrepancy_rule;
209
210 FUNCTION find_detail_discrepancy_rule (
211 p_source_type_id IN NUMBER, -- For Eg. 27
212 p_category IN VARCHAR2, -- For Eg. 'PERSON'
213 p_int_pk_col_name IN VARCHAR2, -- Interface Table PK column Name.'INTERFACE_ID'
214 p_int_pk_col_val IN VARCHAR2, -- Interface Table PK column value.7
215 p_ad_pk_col_name IN VARCHAR2, -- Admission Table PK column Name.'PERSON_ID'
216 p_ad_pk_col_val IN VARCHAR2 -- Admission Table PK column value.123
217 ) RETURN BOOLEAN
218 AS
219 /*
220 || Created By : [email protected]
221 || Created On : 23-Feb-2006
222 || Purpose : This function evaluates the 'Review' Discrepancy Rules at
223 || column for a category(i.e, p_category). If column level
224 || discrepancy exists this function returns TRUE otherwise it returns FALSE.
225 || Known limitations, enhancements or remarks : Uses REF cursor for checking the Detail
226 || Level Discrepancy Rule.
227 || Change History :
228 || Who When What
229 || (reverse chronological order - newest change first)
230 */
231
232 -- Define the REF cursor for checking the Detail Level Discrepancy Rule.
233 TYPE discrepancy_check_cur IS REF CURSOR;
234 discrepancy_check_ref_cur discrepancy_check_cur;
235 l_ref_cur_sql VARCHAR2(2000);
236 l_discrepancy_exists NUMBER(1) :=0;
237 l_admission_sql VARCHAR2(2000);
238 l_interface_sql VARCHAR2(2000);
239 l_ad_column_null NUMBER(1) :=0;
240 l_int_column_null NUMBER(1) :=0;
241
242 -- Variables for FND logging
243 l_request_id NUMBER(15) := fnd_global.conc_request_id;
244 l_prog_label VARCHAR2(100) := 'igs.plsql.igs_ad_imp_002.prc_pe_dtls';
245 l_label VARCHAR2(100) := 'igs.plsql.igs_ad_imp_023.find_detail_discrepancy_rule.';
246 l_debug_str VARCHAR2(10000);
247
248 -- Get the SRC_CAT_ID, AD_TAB_NAME, INT_TAB_NAME from igs_ad_source_cat table.
249 CURSOR source_cat_cur IS
250 SELECT *
251 FROM igs_ad_source_cat
252 WHERE source_type_id = p_source_type_id
253 AND category_name = p_category;
254
255 source_cat_rec source_cat_cur%ROWTYPE;
256
257 -- Get the ATTRIBUTE_NAME for which discrepancy rule is marked for Review.
258 CURSOR detail_discrepancy_cur(cp_src_cat_id igs_ad_source_cat.src_cat_id%TYPE) IS
259 SELECT *
260 FROM igs_ad_dscp_attr
261 WHERE src_cat_id = cp_src_cat_id
262 AND NVL(discrepancy_rule_cd,'I') = 'R';
263
264 detail_discrepancy_rec detail_discrepancy_cur%ROWTYPE;
265
266
267 BEGIN
268
269
270 -- Get the values of SRC_CAT_ID, AD_TAB_NAME, INT_TAB_NAME
271 OPEN source_cat_cur;
272 FETCH source_cat_cur INTO source_cat_rec;
273 CLOSE source_cat_cur;
274
275 -- Loop through all the columns of the current processing table.
276 -- And check discrepancy rule for each column.
277 FOR detail_discrepancy_rec IN detail_discrepancy_cur(source_cat_rec.src_cat_id) LOOP
278 /* Prepare the sql string to check for discrepancy rule for the current column.
279 ** SQL String will be like :
280 ** ' SELECT 1
281 ** FROM IGS_PE_PERSON
282 ** WHERE PERSON_ID = 9855582
283 ** AND BIRTH_DT = SELECT BIRTH_DT
284 ** FROM IGS_AD_INTERFACE_DTL_DSCP_V
285 ** WHERE INTERFACE_ID = 55 ) '
286 */
287 --Dynamic SQL statements are declared for both Admission and Interface columns to check for NULL values.
288 l_admission_sql:='SELECT 1 FROM ' ||source_cat_rec.ad_tab_name
289 ||' WHERE ' || p_ad_pk_col_name || '= :cp_ad_pk_col_value AND '
290 || detail_discrepancy_rec.attribute_name
291 ||' IS NULL';
292
293 l_interface_sql :='SELECT 1 FROM ' ||source_cat_rec.int_tab_name
294 ||' WHERE ' || p_int_pk_col_name || '= :cp_int_pk_col_value AND '
295 || detail_discrepancy_rec.attribute_name
296 ||' IS NULL';
297 --Assign 1 to the counters if the column value IS NULL
298 OPEN discrepancy_check_ref_cur FOR l_admission_sql USING p_ad_pk_col_val;
299 FETCH discrepancy_check_ref_cur INTO l_ad_column_null;
300 CLOSE discrepancy_check_ref_cur;
301
302 OPEN discrepancy_check_ref_cur FOR l_interface_sql USING p_int_pk_col_val;
303 FETCH discrepancy_check_ref_cur INTO l_int_column_null;
304 CLOSE discrepancy_check_ref_cur;
305
306 --If only one of the counters is '1' then discrepancy exists RETURN TRUE
307 --If both of them = 0 then compare the values in both the fields in both the tables.
308 --If both the counters are '1',then Both values are NULL. Hence NO discrepancy, proceed for the next record
309
310 IF (l_ad_column_null = 1 AND l_int_column_null <> 1) OR (l_ad_column_null <> 1 AND l_int_column_null = 1) THEN
311
312 -- Discrepancy exists, match indicator for this record should be updated to '20'.
313 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
314
315 l_label := l_label || 'discpNULL';
319 END IF;
316 l_debug_str := p_int_pk_col_name||' = '|| p_int_pk_col_val || ' Discrepancy exists as only one of the value IS NULL for : '|| detail_discrepancy_rec.attribute_name;
317
318 fnd_log.string_with_context( fnd_log.level_procedure,l_label, l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
320
321 RETURN TRUE;
322 ELSIF l_ad_column_null = 0 AND l_int_column_null = 0 THEN
323 --Dynamic SQL statement to compare the values in both the Tables
324 l_ref_cur_sql := ' SELECT 1 FROM ' || source_cat_rec.ad_tab_name
325 || ' WHERE ' || p_ad_pk_col_name || '= :cp_ad_pk_col_value AND '
326 || detail_discrepancy_rec.attribute_name
327 || ' = ' || ' ( SELECT ' || detail_discrepancy_rec.attribute_name
328 || ' FROM ' || source_cat_rec.int_tab_name
329 || ' WHERE ' || p_int_pk_col_name || '= :cp_int_pk_col_value )' ;
330
331 OPEN discrepancy_check_ref_cur FOR l_ref_cur_sql USING p_ad_pk_col_val, p_int_pk_col_val;
332 FETCH discrepancy_check_ref_cur INTO l_discrepancy_exists;
333 IF discrepancy_check_ref_cur%FOUND THEN
334
335 -- Discrepancy does not exist, give me more..
336 CLOSE discrepancy_check_ref_cur;
337 ELSE
338 -- Discrepancy exists, match indicator for this record should be updated to '20'.
339 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
340
341 l_label := l_label || 'discpNOTNULL';
342 l_debug_str := p_int_pk_col_name||' = '|| p_int_pk_col_val ||' Discrepancy exists, Value of l_ref_cur_sql is :***>>'|| l_ref_cur_sql;
343
344 fnd_log.string_with_context( fnd_log.level_procedure,l_label, l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
345 END IF;
346
347 CLOSE discrepancy_check_ref_cur;
348 RETURN TRUE;
349 END IF;
350 --ELSE l_ad_column_null=1 AND l_int_column_null=1 THEN
351 -- Discrepancy does not exist, give me more..
352 END IF;
353 --refreshing the counter values
354 l_ad_column_null := 0;
355 l_int_column_null := 0;
356 END LOOP;
357
358 -- Discrepancy does not exist, match indicator for this record should be updated to '23'.
359 RETURN FALSE;
360
361 EXCEPTION WHEN OTHERS THEN
362 -- Some SQL execution problem occurred. Mark this record for Discrepancy check.
363 -- Discrepancy exists, match indicator for this record should be updated to '20'.
364 -- Capture the error details in the logfile.
365
366 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
367
368 l_label := l_label || 'Exception';
369 l_debug_str := p_int_pk_col_name||' = '|| p_int_pk_col_val ||' When Other Exception Raised. To be on safer side Returning TRUE. Value of l_ref_cur_sql is :***>>'|| l_ref_cur_sql||
370 ' Value of SQLERRM is :***>> '||SQLERRM;
371
372 fnd_log.string_with_context( fnd_log.level_procedure,l_label, l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
373 END IF;
374
375 RETURN TRUE;
376 END find_detail_discrepancy_rule;
377
378 FUNCTION find_attribute_rule(
379 p_Source_type_id IN NUMBER,
380 p_Category IN VARCHAR2 ) RETURN VARCHAR2
381 AS
382 /*
383 || Created By : [email protected]
384 || Created On : 21-Jun-2001
385 || Purpose : This function returns one of the following :
386 || 'I' : If Discrepancy rule for some of the columns are marked for Import: 'I'.
387 || 'R' : If Discrepancy rule for some of the columns are marked for Review: 'R'.
388 || 'E' : If Discrepancy rule for all the columns are marked for Keep: 'E'.
389 || Known limitations, enhancements or remarks :
390 || Change History :
391 || Who When What
392 || (reverse chronological order - newest change first)
393 */
394 l_rec_rev_count NUMBER;
395 l_rec_import_count NUMBER;
396
397 -- Check if any column discrepancy rule is marked for Review : 'R'.
398 CURSOR check_dtl_for_rev_cur IS
399 SELECT count(ROWID)
400 FROM igs_ad_dscp_attr
401 WHERE src_cat_id = (SELECT src_cat_id
402 FROM igs_ad_source_cat
403 WHERE category_name = p_category
404 AND source_type_id = p_Source_type_id)
405 AND NVL(discrepancy_rule_cd,'E') = 'R';
406
407 -- Check if any column discrepancy rule is marked for Import : 'I'.
408 CURSOR check_dtl_for_import_cur IS
409 SELECT count(ROWID)
410 FROM igs_ad_dscp_attr
411 WHERE src_cat_id = (SELECT src_cat_id
412 FROM igs_ad_source_cat
413 WHERE category_name = p_category
414 AND source_type_id = p_Source_type_id)
415 AND NVL(discrepancy_rule_cd,'E') = 'I';
416
417 BEGIN
418
419 -- Check if any column discrepancy rule is marked for Review : 'R'.
420 OPEN check_dtl_for_rev_cur;
421 FETCH check_dtl_for_rev_cur INTO l_rec_rev_count;
422 CLOSE check_dtl_for_rev_cur;
423 -- Check if any column discrepancy rule is marked for Import : 'I'.
424 OPEN check_dtl_for_import_cur;
425 FETCH check_dtl_for_import_cur INTO l_rec_import_count;
426 CLOSE check_dtl_for_import_cur;
427
428 IF l_rec_rev_count > 0 THEN
429 -- Discrepancy rule for some of the columns are marked for Review: 'R'.
430 RETURN 'R';
431 ELSIF l_rec_import_count > 0 THEN
432 -- Discrepancy rule for some of the columns are marked for Import: 'I'.
433 RETURN 'I';
434 ELSE
435 -- Discrepancy rule for all the columns are marked for Keep: 'E'.
436 RETURN 'E';
437 END IF;
438
439 END find_attribute_rule;
440
441 END Igs_Ad_Imp_023;
[Home] [Help]