1 PACKAGE Igs_Ad_Imp_023 AS
2 /* $Header: IGSADA1S.pls 120.1 2006/02/23 06:10:49 gmaheswa noship $ */
3
4 /*
5 || Created By : [email protected]
6 || Created On : 21-Jun-2001
7 || Purpose : This function gets the Discrepancy Rule for the column(i.e, p_attribute_name).
8 || Evaluates the Discrepancy Rule and based on it's value (i.e, 'I'- 'Import' or 'E'- 'Keep'),
9 || function returns either p_int_col_value or p_ad_col_value.
10 || Known limitations, enhancements or remarks : Overloaded for VARCHAR2 values.
11 || Change History :
12 || Who When What
13 || (reverse chronological order - newest change first)
14 */
15 FUNCTION get_discrepancy_result (
16 p_attribute_name IN VARCHAR2, -- For Eg. 'LEVEL_OF_QUAL_ID'
17 p_ad_col_value IN NUMBER, -- For Eg. 5
18 p_int_col_value IN NUMBER, -- For Eg. 22
19 p_source_type_id IN NUMBER, -- For Eg. 27
20 p_category IN VARCHAR2 -- For Eg. 'PERSON'
21 ) RETURN NUMBER;
22
23
24 /*
25 || Created By : [email protected]
26 || Created On : 21-Jun-2001
27 || Purpose : This function gets the Discrepancy Rule for the column(i.e, p_attribute_name).
28 || Evaluates the Discrepancy Rule and based on it's value (i.e, 'I'- 'Import' or 'E'- 'Keep'),
29 || function returns either p_int_col_value or p_ad_col_value.
30 || Known limitations, enhancements or remarks : Overloaded for NUMBER values.
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 */
35 FUNCTION get_discrepancy_result (
36 p_attribute_name IN VARCHAR2, -- For Eg. 'BIRTH_DT'
37 p_ad_col_value IN DATE, -- For Eg. SYSDATE
38 p_int_col_value IN DATE, -- For Eg. SYSDATE - 1
39 p_source_type_id IN NUMBER, -- For Eg. 27
40 p_category IN VARCHAR2 -- For Eg. 'PERSON'
41 ) RETURN DATE;
42
43
44 /*
45 || Created By : [email protected]
46 || Created On : 21-Jun-2001
47 || Purpose : This function gets the Discrepancy Rule for the column(i.e, p_attribute_name).
48 || Evaluates the Discrepancy Rule and based on it's value (i.e, 'I'- 'Import' or 'E'- 'Keep'),
49 || function returns either p_int_col_value or p_ad_col_value.
50 || Known limitations, enhancements or remarks : Overloaded for DATE values.
51 || Change History :
52 || Who When What
53 || (reverse chronological order - newest change first)
54 */
55 FUNCTION get_discrepancy_result (
56 p_attribute_name IN VARCHAR2, -- For Eg. 'SURNAME'
57 p_ad_col_value IN VARCHAR2, -- For Eg. 'Navin'
58 p_int_col_value IN VARCHAR2, -- For Eg. 'Navinkrs'
59 p_source_type_id IN NUMBER, -- For Eg. 27
60 p_category IN VARCHAR2 -- For Eg. 'PERSON'
61 ) RETURN VARCHAR2;
62
63
64 /*
65 || Created By : [email protected]
66 || Created On : 21-Jun-2001
67 || Purpose : This function accepts a set of input Primary Key(PK) column names
68 || and their values in VARCHAR2 format and returns a string 'WHERE clause' values.
69 || Known limitations, enhancements or remarks : Currently this function supports only
70 || five column names along with their values.
71 || Change History :
72 || Who When What
73 || gmaheswa 23-Feb-2006 Following Function is obsoleted.
74 || (reverse chronological order - newest change first)
75 */
76 FUNCTION prepare_pk_where_clause(
77 p_param1 VARCHAR2 DEFAULT NULL, -- First PK column Name.
78 p_param2 VARCHAR2 DEFAULT NULL, -- First PK column value.
79 p_param3 VARCHAR2 DEFAULT NULL, -- Second PK column Name.
80 p_param4 VARCHAR2 DEFAULT NULL, -- Second PK column value.
81 p_param5 VARCHAR2 DEFAULT NULL, -- Third PK column Name.
82 p_param6 VARCHAR2 DEFAULT NULL, -- Third PK column value.
83 p_param7 VARCHAR2 DEFAULT NULL, -- Forth PK column Name.
84 p_param8 VARCHAR2 DEFAULT NULL, -- Forth PK column value.
85 p_param9 VARCHAR2 DEFAULT NULL, -- Fifth PK column Name.
86 p_param10 VARCHAR2 DEFAULT NULL -- Fifth PK column value.
87 ) RETURN VARCHAR2;
88
89
90
91 /*
92 || Created By : [email protected]
93 || Created On : 21-Jun-2001
94 || Purpose : This function evaluates the 'Review' Discrepancy Rules at
95 || column for a category(i.e, p_category). If column level
96 || discrepancy exists this function returns TRUE otherwise it returns FALSE.
97 || Known limitations, enhancements or remarks : Uses REF cursor for checking the Detail
98 || Level Discrepancy Rule.
99 || Change History :
100 || Who When What
101 || (reverse chronological order - newest change first)
102 */
103 FUNCTION find_detail_discrepancy_rule (
104 p_source_type_id IN NUMBER, -- For Eg. 27
105 p_category IN VARCHAR2, -- For Eg. 'PERSON'
106 p_int_pk_col_name IN VARCHAR2, -- Interface Table PK column Name.
107 p_int_pk_col_val IN VARCHAR2, -- Interface Table PK column value.
108 p_ad_pk_col_name IN VARCHAR2, -- Admission Table PK column Name.
109 p_ad_pk_col_val IN VARCHAR2 -- Admission Table PK column value.
110 ) RETURN BOOLEAN;
111
112
113 /*
114 || Created By : [email protected]
115 || Created On : 21-Jun-2001
116 || Purpose : This function evaluates the 'Review' Discrepancy Rules at
117 || column for a category(i.e, p_category). If column level
118 || discrepancy exists this function returns TRUE otherwise it returns FALSE.
119 || Known limitations, enhancements or remarks : Uses REF cursor for checking the Detail
120 || Level Discrepancy Rule.
121 || Change History :
122 || Who When What
123 || gmaheswa 23-Feb-2006 Following Function is obsoleted.
124 || (reverse chronological order - newest change first)
125 */
126 FUNCTION find_detail_discrepancy_rule (
127 p_source_type_id IN NUMBER, -- For Eg. 27
128 p_category IN VARCHAR2, -- For Eg. 'PERSON'
129 p_int_pk_where_clause IN VARCHAR2, -- For Eg. 'INTERFACE_ID = 55'
130 p_ad_pk_where_clause IN VARCHAR2 -- For Eg. 'PERSON_ID = 9855582'
131 ) RETURN BOOLEAN;
132
133 /*
134 || Created By : [email protected]
135 || Created On : 21-Jun-2001
136 || Purpose : This function returns one of the following :
137 || 'I' : If Discrepancy rule for some of the columns are marked for Import: 'I'.
138 || 'R' : If Discrepancy rule for some of the columns are marked for Review: 'R'.
139 || 'E' : If Discrepancy rule for all the columns are marked for Keep: 'E'.
140 || Known limitations, enhancements or remarks :
141 || Change History :
142 || Who When What
143 || (reverse chronological order - newest change first)
144 */
145 FUNCTION find_attribute_rule(
146 p_source_type_id IN NUMBER, -- For Eg. 27
147 p_category IN VARCHAR2 -- For Eg. 'PERSON'
148 ) RETURN VARCHAR2;
149 END Igs_Ad_Imp_023;