1 PACKAGE BODY pay_jp_formula_function_pkg AS
2 /* $Header: pyjpffuc.pkb 115.9 2003/01/28 11:17:54 miwamoto noship $ */
3 /* ------------------------------------------------------------------------------------ --
4 -- GET_TABLE_VALUE_WITH_DEFAULT
5 -- return the value of specified user defined table
6 -- if fetched return value is null,
7 -- if p_default_by_row is setted 'Y',
8 -- return the udt value by specifying row as p_default_value
9 -- if p_default_by_row is not setted or is setted 'Y' or else,
10 -- return the specified p_default_value directly.
11 -- USAGE: p_default_value : Set return default value
12 -- or column value to fetch udt value
13 -- when p_default_by_row is Y.
14 -- p_default_by_row : Set Y or N.
15 -- If set Y, Use p_default_value as parameter to fetch return value
16 -- If set N(null) or else, Use p_default_value as default return value.
17 -- ------------------------------------------------------------------------------------ */
18 FUNCTION get_table_value_with_default(
19 p_business_group_id IN NUMBER,
20 p_table_name IN VARCHAR2,
21 p_column_name IN VARCHAR2,
22 p_row_value IN VARCHAR2,
23 p_effective_date IN DATE DEFAULT NULL,
24 p_default_value IN VARCHAR2,
25 p_default_by_row IN VARCHAR2 DEFAULT 'N')
26 RETURN VARCHAR2
27 IS
28 --
29 l_effective_date date;
30 l_range_or_match pay_user_tables.range_or_match%type;
31 l_user_table_id pay_user_tables.user_table_id%type;
32 l_value pay_user_column_instances_f.value%type;
33 l_legislation_code per_business_groups.legislation_code%type;
34 --
35 cursor csr_value_match
36 is
37 select puci.value
38 from pay_user_column_instances_f puci,
39 pay_user_columns puc,
40 pay_user_rows_f pur,
41 pay_user_tables put
42 where put.user_table_id = l_user_table_id
43 and pur.user_table_id = put.user_table_id
44 and l_effective_date
45 between pur.effective_start_date and pur.effective_end_date
46 and nvl(pur.business_group_id, p_business_group_id) = p_business_group_id
47 and nvl(pur.legislation_code, l_legislation_code) = l_legislation_code
48 and decode(put.user_key_units,
49 'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
50 'N', pur.row_low_range_or_name,
51 'T', pur.row_low_range_or_name,
52 null)
53 =
54 decode(put.user_key_units,
55 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
56 'N', p_row_value,
57 'T', p_row_value,
58 null)
59 and puc.user_table_id = put.user_table_id
60 and puc.user_column_name = p_column_name
61 and nvl(puc.business_group_id, p_business_group_id) = p_business_group_id
62 and nvl(puc.legislation_code, l_legislation_code) = l_legislation_code
63 and puci.user_row_id = pur.user_row_id
64 and puci.user_column_id = puc.user_column_id
65 and l_effective_date
66 between puci.effective_start_date and puci.effective_end_date;
67 --
68 cursor csr_value_range
69 is
70 select puci.value
71 from pay_user_column_instances_f puci,
72 pay_user_columns puc,
73 pay_user_rows_f pur,
74 pay_user_tables put
75 where put.user_table_id = l_user_table_id
76 and put.user_key_units = 'N'
77 and pur.user_table_id = put.user_table_id
78 and l_effective_date
79 between pur.effective_start_date and pur.effective_end_date
80 and nvl(pur.business_group_id, p_business_group_id) = p_business_group_id
81 and nvl(pur.legislation_code, l_legislation_code) = l_legislation_code
82 and fnd_number.canonical_to_number(p_row_value)
83 between fnd_number.canonical_to_number(pur.row_low_range_or_name)
84 and fnd_number.canonical_to_number(pur.row_high_range)
85 and puc.user_table_id = put.user_table_id
86 and puc.user_column_name = p_column_name
87 and nvl(puc.business_group_id, p_business_group_id) = p_business_group_id
88 and nvl(puc.legislation_code, l_legislation_code) = l_legislation_code
89 and puci.user_row_id = pur.user_row_id
90 and puci.user_column_id = puc.user_column_id
91 and l_effective_date
92 between puci.effective_start_date and puci.effective_end_date;
93 BEGIN
94 --
95 /* Use either the supplied date, or the date from fnd_sessions */
96 if p_effective_date is null then
97 select effective_date
98 into l_effective_date
99 from fnd_sessions
100 where session_id = userenv('sessionid');
101 else
102 l_effective_date := p_effective_date;
103 end if;
104 --
105 /* Get the legislation code */
106 select legislation_code
107 into l_legislation_code
108 from per_business_groups
109 where business_group_id = p_business_group_id;
110 --
111 /* Get the type of query to be performed, either range or match */
112 select range_or_match,
113 user_table_id
114 into l_range_or_match,
115 l_user_table_id
116 from pay_user_tables
117 where user_table_name = p_table_name
118 and nvl(business_group_id, p_business_group_id) = p_business_group_id
119 and nvl(legislation_code, l_legislation_code) = l_legislation_code;
120 --
121 /* Get the value */
122 /* + Matched */
123 if l_range_or_match = 'M' then
124 open csr_value_match;
125 fetch csr_value_match into l_value;
126 close csr_value_match;
127 /* + Range */
128 else
129 open csr_value_range;
130 fetch csr_value_range into l_value;
131 close csr_value_range;
132 end if;
133 --
134 /* Get default value if the value is null */
135 if l_value is null then
136 if p_default_by_row = 'Y' then
137 l_value := hruserdt.get_table_value(
138 p_bus_group_id => p_business_group_id,
139 p_table_name => p_table_name,
140 p_col_name => p_column_name,
141 p_row_value => p_default_value,
142 p_effective_date => l_effective_date);
143 else
144 l_value := p_default_value;
145 end if;
146 end if;
147 --
148 RETURN l_value;
149 END get_table_value_with_default;
150 /* ------------------------------------------------------------------------------------ --
151 -- CHK_SMC
152 -- return the 'TRUE' or 'FALSE'
153 -- If a value which is confirmed is on UDT, returns 'TRUE'. If a value is not on UDT,
154 -- returns 'FALSE'.
155 -- USAGE:
156 -- Name Reqd Type Description
157 -- p_table_name Yes VARCHAR2 UDT table name.
158 -- p_column_name Yes VARCHAR2 UDT column name.
159 -- p_effective_date Yes DATE effective_date.
160 -- p_value Yes VARCHAR2 value to be confirmed.
161 -- ------------------------------------------------------------------------------------ */
162 FUNCTION chk_smc(
163 p_table_name IN VARCHAR2,
164 p_column_name IN VARCHAR2,
165 p_effective_date IN DATE,
166 p_value IN VARCHAR2) RETURN VARCHAR2
167 IS
168 --
169 l_value_exists VARCHAR2(1);
170 --
171 CURSOR udt_value_exists IS
172 select 'Y'
173 from pay_user_column_instances_f i,
174 pay_user_rows_f r,
175 pay_user_columns c,
176 pay_user_tables t
177 where t.legislation_code ='JP'
178 and t.business_group_id is null
179 and t.user_table_name = p_table_name
180 and c.legislation_code = 'JP'
181 and c.business_group_id is null
182 and c.user_table_id = t.user_table_id
183 and c.user_column_name = p_column_name
184 and r.user_table_id = t.user_table_id
185 and p_effective_date between r.effective_start_date and r.effective_end_date
186 and r.legislation_code = 'JP'
187 and r.business_group_id is null
188 and p_effective_date between i.effective_start_date and i.effective_end_date
189 and i.user_row_id = r.user_row_id
190 and i.user_column_id = c.user_column_id
191 and i.value = p_value
192 ;
193 BEGIN
194 OPEN udt_value_exists;
195 FETCH udt_value_exists INTO l_value_exists;
196 --
197 IF udt_value_exists%NOTFOUND THEN
198 --
199 return 'FALSE';
200 --
201 ELSE
202 return 'TRUE';
203 END IF;
204 CLOSE udt_value_exists;
205 END chk_smc;
206 /* ------------------------------------------------------------------------------------ */
207 FUNCTION get_jp_parameter(
208 p_owner IN VARCHAR2,
209 p_parameter_name IN VARCHAR2) RETURN VARCHAR2 IS
210 --
211 CURSOR cel_jp_parameter IS
212 SELECT parameter_value FROM hr_jp_parameters
213 WHERE owner = p_owner
214 AND parameter_name = p_parameter_name;
215 --
216 l_parameter_value hr_jp_parameters.parameter_value%TYPE;
217 --
218 BEGIN
219 --
220 OPEN cel_jp_parameter;
221 FETCH cel_jp_parameter INTO l_parameter_value;
222 --
223 IF cel_jp_parameter%NOTFOUND THEN
224 --
225 l_parameter_value := NULL;
226 --
227 END IF;
228 --
229 CLOSE cel_jp_parameter;
230 --
231 RETURN l_parameter_value;
232 --
233 END get_jp_parameter;
234 --
235 END pay_jp_formula_function_pkg;
[Home] [Help]