[Home] [Help]
PACKAGE BODY: APPS.HRUSERDT
Source
1 package body hruserdt as
2 /* $Header: pyuserdt.pkb 120.2.12000000.1 2007/01/18 02:24:42 appldev ship $ */
3 --
4 g_leg_code varchar2(2);
5 cached boolean := FALSE;
6 g_effective_date date := null;
7 --
8 plsql_value_error exception;
9 pragma exception_init(plsql_value_error, -6502);
10 --
11 --
12 -- This procedure sets the g_effective_date cache
13 --
14 procedure set_g_effective_date(p_effective_date in date)
15 is
16 begin
17 -- set g_effective_date cache
18 g_effective_date := p_effective_date;
19 end set_g_effective_date;
20
21 --
22 -- This procedure unsets the g_effective_date cache
23 --
24 procedure unset_g_effective_date
25 is
26 begin
27 -- unset g_effective_date cache
28 g_effective_date := null;
29 end unset_g_effective_date;
30
31 function get_table_value (p_bus_group_id in number,
32 p_table_name in varchar2,
33 p_col_name in varchar2,
34 p_row_value in varchar2,
35 p_effective_date in date default null)
36 return varchar2 is
37 l_effective_date date;
38 l_range_or_match pay_user_tables.range_or_match%type;
39 l_table_id pay_user_tables.user_table_id%type;
40 l_value pay_user_column_instances_f.value%type;
41 begin
42 --
43 -- Use either the supplied date, or the date from fnd_sessions
44 --
45 if (p_effective_date is null) then
46 if (g_effective_date is null) then
47 begin
48 select effective_date
49 into l_effective_date
50 from fnd_sessions
51 where session_id = userenv('sessionid');
52 end;
53 else
54 l_effective_date := g_effective_date;
55 end if;
56 else
57 l_effective_date := p_effective_date;
58 end if;
59 --
60 -- get the legislation code:
61 --
62 begin
63 if cached = FALSE THEN
64 select legislation_code
65 into g_leg_code
66 from per_business_groups
67 where business_group_id = p_bus_group_id;
68 cached := TRUE;
69 end if;
70 end;
71 --
72 -- get the type of query to be performed, either range or match
73 --
74 select range_or_match, user_table_id
75 into l_range_or_match, l_table_id
76 from pay_user_tables
77 where upper(user_table_name) = upper(p_table_name)
78 and nvl (business_group_id,
79 p_bus_group_id) = p_bus_group_id
80 and nvl(legislation_code, g_leg_code) = g_leg_code;
81 --
82 if (l_range_or_match = 'M') then -- matched
83 begin
84 select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
85 INDEX(R PAY_USER_ROWS_F_FK1)
86 INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
87 ORDERED */
88 CINST.value
89 into l_value
90 from pay_user_tables TAB
91 , pay_user_columns C
92 , pay_user_rows_f R
93 , pay_user_column_instances_f CINST
94 where TAB.user_table_id = l_table_id
95 and C.user_table_id = TAB.user_table_id
96 and nvl (C.business_group_id,
97 p_bus_group_id) = p_bus_group_id
98 and nvl (C.legislation_code,
99 g_leg_code) = g_leg_code
100 and upper (C.user_column_name) = upper (p_col_name)
101 and CINST.user_column_id = C.user_column_id
102 and R.user_table_id = TAB.user_table_id
103 and l_effective_date between R.effective_start_date
104 and R.effective_end_date
105 and nvl (R.business_group_id,
106 p_bus_group_id) = p_bus_group_id
107 and nvl (R.legislation_code,
108 g_leg_code) = g_leg_code
109 and decode
110 (TAB.user_key_units,
111 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
112 'N', to_char(fnd_number.canonical_to_number(p_row_value)),
113 'T', upper (p_row_value),
114 null) =
115 decode
116 (TAB.user_key_units,
117 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
118 'N', to_char(fnd_number.canonical_to_number(R.row_low_range_or_name)),
119 'T', upper (R.row_low_range_or_name),
120 null)
121 and CINST.user_row_id = R.user_row_id
122 and l_effective_date between CINST.effective_start_date
123 and CINST.effective_end_date
124 and nvl (CINST.business_group_id,
125 p_bus_group_id) = p_bus_group_id
126 and nvl (CINST.legislation_code,
127 g_leg_code) = g_leg_code;
128 --
129 exception
130 when plsql_value_error then
131 select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
132 INDEX(R PAY_USER_ROWS_F_FK1)
133 INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
134 ORDERED */
135 CINST.value
136 into l_value
137 from pay_user_tables TAB
138 , pay_user_columns C
139 , pay_user_rows_f R
140 , pay_user_column_instances_f CINST
141 where TAB.user_table_id = l_table_id
142 and C.user_table_id = TAB.user_table_id
143 and nvl (C.business_group_id,
144 p_bus_group_id) = p_bus_group_id
145 and nvl (C.legislation_code,
146 g_leg_code) = g_leg_code
147 and upper (C.user_column_name) = upper (p_col_name)
148 and CINST.user_column_id = C.user_column_id
149 and R.user_table_id = TAB.user_table_id
150 and l_effective_date between R.effective_start_date
151 and R.effective_end_date
152 and nvl (R.business_group_id,
153 p_bus_group_id) = p_bus_group_id
154 and nvl (R.legislation_code,
155 g_leg_code) = g_leg_code
156 and decode
157 (TAB.user_key_units,
158 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
159 'N', p_row_value,
160 'T', upper (p_row_value),
161 null) =
162 decode
163 (TAB.user_key_units,
164 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
165 'N', R.row_low_range_or_name,
166 'T', upper (R.row_low_range_or_name),
167 null)
168 and CINST.user_row_id = R.user_row_id
169 and l_effective_date between CINST.effective_start_date
170 and CINST.effective_end_date
171 and nvl (CINST.business_group_id,
172 p_bus_group_id) = p_bus_group_id
173 and nvl (CINST.legislation_code,
174 g_leg_code) = g_leg_code;
175 --
176 when others then
177 raise;
178 end;
179 --
180 return l_value;
181 --
182 else -- range
183 begin
184 select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
185 INDEX(R PAY_USER_ROWS_F_FK1)
186 INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
187 ORDERED */
188 CINST.value
189 into l_value
190 from pay_user_tables TAB
191 , pay_user_columns C
192 , pay_user_rows_f R
193 , pay_user_column_instances_f CINST
194 where TAB.user_table_id = l_table_id
195 and C.user_table_id = TAB.user_table_id
196 and nvl (C.business_group_id,
197 p_bus_group_id) = p_bus_group_id
198 and nvl (C.legislation_code,
199 g_leg_code) = g_leg_code
200 and upper (C.user_column_name) = upper (p_col_name)
201 and CINST.user_column_id = C.user_column_id
202 and R.user_table_id = TAB.user_table_id
203 and l_effective_date between R.effective_start_date
204 and R.effective_end_date
205 and nvl (R.business_group_id,
206 p_bus_group_id) = p_bus_group_id
207 and nvl (R.legislation_code,
208 g_leg_code) = g_leg_code
209 and fnd_number.canonical_to_number (p_row_value)
210 between fnd_number.canonical_to_number (R.row_low_range_or_name)
211 and fnd_number.canonical_to_number (R.row_high_range)
212 and TAB.user_key_units = 'N'
213 and CINST.user_row_id = R.user_row_id
214 and l_effective_date between CINST.effective_start_date
215 and CINST.effective_end_date
216 and nvl (CINST.business_group_id,
217 p_bus_group_id) = p_bus_group_id
218 and nvl (CINST.legislation_code,
219 g_leg_code) = g_leg_code;
220 --
221 return l_value;
222 end;
223 end if;
224
225 end get_table_value;
226
227 END hruserdt;