[Home] [Help]
PACKAGE BODY: APPS.HRUSERDT
Source
1 package body hruserdt as
2 /* $Header: pyuserdt.pkb 120.4 2011/07/12 06:53:33 asnell ship $ */
3 --
4 g_leg_code varchar2(2);
5 g_bg_id pay_user_tables.business_group_id%type;
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 -- cache g_leg_code for change of business group, Bug 11864853
64 if nvl(g_bg_id,-1) <> p_bus_group_id then
65 select legislation_code
66 into g_leg_code
67 from per_business_groups
68 where business_group_id = p_bus_group_id;
69 g_bg_id := p_bus_group_id;
70 end if;
71 end;
72 --
73 -- get the type of query to be performed, either range or match
74 --
75 select range_or_match, user_table_id
76 into l_range_or_match, l_table_id
77 from pay_user_tables
78 where upper(user_table_name) = upper(p_table_name)
79 and nvl (business_group_id,
80 p_bus_group_id) = p_bus_group_id
81 and nvl(legislation_code, g_leg_code) = g_leg_code;
82 --
83 if (l_range_or_match = 'M') then -- matched
84 begin
85 select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
86 INDEX(R PAY_USER_ROWS_F_FK1)
87 INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
88 ORDERED */
89 CINST.value
90 into l_value
91 from pay_user_tables TAB
92 , pay_user_columns C
93 , pay_user_rows_f R
94 , pay_user_column_instances_f CINST
95 where TAB.user_table_id = l_table_id
96 and C.user_table_id = TAB.user_table_id
97 and nvl (C.business_group_id,
98 p_bus_group_id) = p_bus_group_id
99 and nvl (C.legislation_code,
100 g_leg_code) = g_leg_code
101 and upper (C.user_column_name) = upper (p_col_name)
102 and CINST.user_column_id = C.user_column_id
103 and R.user_table_id = TAB.user_table_id
104 and l_effective_date between R.effective_start_date
105 and R.effective_end_date
106 and nvl (R.business_group_id,
107 p_bus_group_id) = p_bus_group_id
108 and nvl (R.legislation_code,
109 g_leg_code) = g_leg_code
110 and decode
111 (TAB.user_key_units,
112 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
113 'N', to_char(fnd_number.canonical_to_number(p_row_value)),
114 'T', upper (p_row_value),
115 null) =
116 decode
117 (TAB.user_key_units,
118 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
119 'N', to_char(fnd_number.canonical_to_number(R.row_low_range_or_name)),
120 'T', upper (R.row_low_range_or_name),
121 null)
122 and CINST.user_row_id = R.user_row_id
123 and l_effective_date between CINST.effective_start_date
124 and CINST.effective_end_date
125 and nvl (CINST.business_group_id,
126 p_bus_group_id) = p_bus_group_id
127 and nvl (CINST.legislation_code,
128 g_leg_code) = g_leg_code;
129 --
130 exception
131 when plsql_value_error then
132 select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
133 INDEX(R PAY_USER_ROWS_F_FK1)
134 INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
135 ORDERED */
136 CINST.value
137 into l_value
138 from pay_user_tables TAB
139 , pay_user_columns C
140 , pay_user_rows_f R
141 , pay_user_column_instances_f CINST
142 where TAB.user_table_id = l_table_id
143 and C.user_table_id = TAB.user_table_id
144 and nvl (C.business_group_id,
145 p_bus_group_id) = p_bus_group_id
146 and nvl (C.legislation_code,
147 g_leg_code) = g_leg_code
148 and upper (C.user_column_name) = upper (p_col_name)
149 and CINST.user_column_id = C.user_column_id
150 and R.user_table_id = TAB.user_table_id
151 and l_effective_date between R.effective_start_date
152 and R.effective_end_date
153 and nvl (R.business_group_id,
154 p_bus_group_id) = p_bus_group_id
155 and nvl (R.legislation_code,
156 g_leg_code) = g_leg_code
157 and decode
158 (TAB.user_key_units,
159 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
160 'N', p_row_value,
161 'T', upper (p_row_value),
162 null) =
163 decode
164 (TAB.user_key_units,
165 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
166 'N', R.row_low_range_or_name,
167 'T', upper (R.row_low_range_or_name),
168 null)
169 and CINST.user_row_id = R.user_row_id
170 and l_effective_date between CINST.effective_start_date
171 and CINST.effective_end_date
172 and nvl (CINST.business_group_id,
173 p_bus_group_id) = p_bus_group_id
174 and nvl (CINST.legislation_code,
175 g_leg_code) = g_leg_code;
176 --
177 when others then
178 raise;
179 end;
180 --
181 return l_value;
182 --
183 else -- range
184 begin
185 select /*+ INDEX(C PAY_USER_COLUMNS_FK1)
186 INDEX(R PAY_USER_ROWS_F_FK1)
187 INDEX(CINST PAY_USER_COLUMN_INSTANCES_N1)
188 ORDERED */
189 CINST.value
190 into l_value
191 from pay_user_tables TAB
192 , pay_user_columns C
193 , pay_user_rows_f R
194 , pay_user_column_instances_f CINST
195 where TAB.user_table_id = l_table_id
196 and C.user_table_id = TAB.user_table_id
197 and nvl (C.business_group_id,
198 p_bus_group_id) = p_bus_group_id
199 and nvl (C.legislation_code,
200 g_leg_code) = g_leg_code
201 and upper (C.user_column_name) = upper (p_col_name)
202 and CINST.user_column_id = C.user_column_id
203 and R.user_table_id = TAB.user_table_id
204 and l_effective_date between R.effective_start_date
205 and R.effective_end_date
206 and nvl (R.business_group_id,
207 p_bus_group_id) = p_bus_group_id
208 and nvl (R.legislation_code,
209 g_leg_code) = g_leg_code
210 and fnd_number.canonical_to_number (p_row_value)
211 between fnd_number.canonical_to_number (R.row_low_range_or_name)
212 and fnd_number.canonical_to_number (R.row_high_range)
213 and TAB.user_key_units = 'N'
214 and CINST.user_row_id = R.user_row_id
215 and l_effective_date between CINST.effective_start_date
216 and CINST.effective_end_date
217 and nvl (CINST.business_group_id,
218 p_bus_group_id) = p_bus_group_id
219 and nvl (CINST.legislation_code,
220 g_leg_code) = g_leg_code;
221 --
222 return l_value;
223 end;
224 end if;
225
226 end get_table_value;
227
228 END hruserdt;