[Home] [Help]
PACKAGE BODY: APPS.PQH_SIT_ENGINE
Source
1 PACKAGE BODY PQH_SIT_ENGINE as
2 /* $Header: pqsiteng.pkb 120.0 2005/05/29 02:41 appldev noship $ */
3
4 procedure get_attribute(p_txn_catg_attr_id in number, p_attribute_id out NOCOPY number,
5 p_value_style_cd OUT NOCOPY varchar2) is
6 l_attribute_id number;
7 l_value_style_cd Varchar2(30);
8 begin
9 select attribute_id, value_style_cd
10 into l_attribute_id, l_value_style_cd
11 from pqh_txn_category_attributes
12 where txn_category_attribute_id = p_txn_catg_attr_id;
13 hr_utility.set_location('attr : '||l_attribute_id, 5);
14 hr_utility.set_location('l_value_style_cd : '||l_value_style_cd, 5);
15 p_attribute_id := l_attribute_id;
16 p_value_style_cd := l_value_style_cd;
17 exception
18 when no_data_found then
19 hr_utility.set_location('ndf txn_cat_attr : '||p_txn_catg_attr_id, 10);
20 raise;
21 when others then
22 hr_utility.set_location('other error, txn_cat_attr : '||p_txn_catg_attr_id, 10);
23 raise;
24 end get_attribute;
25
26 Function GET_TRANSACTION_VALUE (p_person_id IN number,
27 p_effective_date in date,
28 p_attribute_id IN number) RETURN varchar2 IS
29 l_sel_stmt varchar2(4000);
30 l_from_clause PQH_TABLE_ROUTE.FROM_CLAUSE%TYPE;
31 l_where_clause_in PQH_TABLE_ROUTE.WHERE_CLAUSE%TYPE;
32 l_where_clause_out PQH_TABLE_ROUTE.WHERE_CLAUSE%TYPE;
33 l_column_name PQH_ATTRIBUTES.COLUMN_NAME%TYPE;
34 l_column_type PQH_ATTRIBUTES.COLUMN_TYPE%TYPE;
35 l_selected_value_v varchar2(2000);
36 l_selected_value_n number;
37 l_selected_value_d date;
38 l_proc varchar2(100) := 'get_transaction_value';
39 l_table_route_id PQH_TABLE_ROUTE.TABLE_ROUTE_ID%TYPE;
40 BEGIN
41 hr_utility.set_location('Entering : '||l_proc, 5);
42 if (p_person_id is not null and p_attribute_id is not null) then
43
44 -- get the attribute details
45 select column_name, master_table_route_id,column_type
46 into l_column_name, l_table_route_id,l_column_type
47 from pqh_attributes
48 where attribute_id = p_attribute_id;
49 hr_utility.set_location('column_name1 is : '||substr(l_column_name,1,50),10);
50 hr_utility.set_location('column_name2 is : '||substr(l_column_name,51,50),11);
51 hr_utility.set_location('column_name3 is : '||substr(l_column_name,101,50),12);
52 hr_utility.set_location('column_name4 is : '||substr(l_column_name,151,50),13);
53 hr_utility.set_location('column_type is : '||l_column_type,15);
54 hr_utility.set_location('table_route is : '||l_table_route_id,20);
55
56 -- table route is selected, get the details
57
58 select from_clause, where_clause
59 into l_from_clause, l_where_clause_in
60 from pqh_table_route where table_route_id = l_table_route_id;
61
62 hr_utility.set_location('from_clause1 is : '||substr(l_from_clause,1,30),30);
63 hr_utility.set_location('from_clause2 is : '||substr(l_from_clause,31,30),31);
64 hr_utility.set_location('where_clause 1is : '||substr(l_where_clause_in,1,40),40);
65 hr_utility.set_location('where_clause 2is : '||substr(l_where_clause_in,41,40),40);
66
67 -- update the where clause with the context values
68
69 pqh_refresh_data.replace_where_params(
70 p_where_clause_in => l_where_clause_in,
71 p_txn_tab_flag => 'N',
72 p_txn_id => p_person_id,
73 p_where_clause_out => l_where_clause_out);
74 hr_utility.set_location('where_clause 1is : '||substr(l_where_clause_out,1,40),50);
75 hr_utility.set_location('where_clause 2is : '||substr(l_where_clause_out,41,40),50);
76
77 hr_utility.set_location('select clause 1is : '||substr(l_column_name,1,40),50);
78 l_column_name := replace(l_column_name,'p_person_id',p_person_id);
79 hr_utility.set_location('select clause 2is : '||substr(l_column_name,1,40),50);
80 l_column_name := replace(l_column_name,'p_effective_date',''''||to_char(p_effective_date,'dd/mm/yyyy')||'''');
81 hr_utility.set_location('select clause 3is : '||substr(l_column_name,1,40),50);
82 -- build up the statement to be used for getting the value
83 l_sel_stmt := 'select '||l_column_name||' from '||l_from_clause||' where '||l_where_clause_out ;
84 hr_utility.set_location('stmt1 '||substr(l_sel_stmt,1,60),55);
85 hr_utility.set_location('stmt2 '||substr(l_sel_stmt,61,60),55);
86 hr_utility.set_location('stmt3 '||substr(l_sel_stmt,121,60),55);
87 hr_utility.set_location('stmt4 '||substr(l_sel_stmt,181,60),55);
88 hr_utility.set_location('stmt5 '||substr(l_sel_stmt,241,60),55);
89 hr_utility.set_location('stmt6 '||substr(l_sel_stmt,361,60),55);
90
91 -- execute the dynamic sql
92 if l_column_type ='D' then
93 hr_utility.set_location('date being fetched ',60);
94 execute immediate l_sel_stmt into l_selected_value_d;
95 -- converting the date to character format
96 l_selected_value_v := fnd_date.date_to_canonical(l_selected_value_d);
97 elsif l_column_type ='N' then
98 hr_utility.set_location('number being fetched ',60);
99 execute immediate l_sel_stmt into l_selected_value_n;
100 l_selected_value_v := to_char(l_selected_value_n);
101 else
102 hr_utility.set_location('varchar being fetched ',60);
103 execute immediate l_sel_stmt into l_selected_value_v;
104 end if;
105 hr_utility.set_location('leaving with value: '||l_selected_value_v, 90);
106 return l_selected_value_v;
107 else
108 hr_utility.set_location('values passed was null. '||l_proc, 420);
109 return null;
110 end if;
111 EXCEPTION
112 when no_data_found then
113 hr_utility.set_location('no data exists '||l_proc, 100);
114 return null;
115 WHEN others THEN
116 hr_utility.set_location('Failure in program unit: '||l_proc, 420);
117 return null;
118 END GET_TRANSACTION_VALUE;
119
120 function check_attribute_result(p_rule_from in varchar2,
121 p_txn_value in varchar2,
122 p_rule_to in varchar2,
123 p_value_style_cd in varchar2,
124 p_exclude_flag in varchar2) return BOOLEAN is
125 l_proc varchar2(30) := g_package||'check_attr';
126 BEGIN
127 hr_utility.set_location('entering '||l_proc, 5);
128 hr_utility.set_location('p_rule_from is '||p_rule_from, 5);
129 hr_utility.set_location('p_rule_to is '||p_rule_to, 10);
130 hr_utility.set_location('p_txn_value is '||p_txn_value, 15);
131 hr_utility.set_location('p_value_style_cd is '||p_value_style_cd, 15);
132 hr_utility.set_location('p_exclude_flag is '||p_exclude_flag, 15);
133
134 if p_txn_value is null then
135 hr_utility.set_location('txn_value_null'||l_proc, 420);
136 return false;
137 else
138 IF p_value_style_cd = 'EXACT' THEN
139 hr_utility.set_location('Value Cd is Exact '||l_proc, 420);
140 IF p_txn_value = p_rule_from THEN
141 hr_utility.set_location('txn value is equal to rule_from ', 420);
142 IF p_exclude_flag = 'Y' THEN
143 hr_utility.set_location('exclude flag Y ', 420);
144 RETURN FALSE;
145 ELSE
146 hr_utility.set_location('exclude flag N ', 420);
147 RETURN TRUE;
148 END IF;
149 ELSE
150 hr_utility.set_location('txn value is not equal to rule_from ', 420);
151 IF p_exclude_flag = 'Y' THEN
152 hr_utility.set_location('exclude flag Y ', 420);
153 RETURN TRUE;
154 ELSE
155 hr_utility.set_location('exclude flag N ', 420);
156 RETURN FALSE;
157 END IF;
158 END IF;
159 ELSIF p_value_style_cd = 'RANGE' THEN
160 if p_rule_from is null then
161 hr_utility.set_location('rule_from null'||l_proc, 420);
162 if p_txn_value <= p_rule_to then
163 hr_utility.set_location('txn_value less than to '||l_proc, 420);
164 IF p_exclude_flag = 'Y' THEN
165 RETURN FALSE;
166 ELSE
167 RETURN TRUE;
168 END IF;
169 else
170 hr_utility.set_location('txn_value more than to '||l_proc, 420);
171 IF p_exclude_flag = 'Y' THEN
172 RETURN TRUE;
173 ELSE
174 RETURN FALSE;
175 END IF;
176 end if;
177 else
178 if p_rule_to is null then
179 hr_utility.set_location('rule_to is null '||l_proc, 420);
180 if p_txn_value >= p_rule_from then
181 hr_utility.set_location('txn_value more than rule_from '||l_proc, 420);
182 IF p_exclude_flag = 'Y' THEN
183 RETURN FALSE;
184 ELSE
185 RETURN TRUE;
186 END IF;
187 else
188 hr_utility.set_location('txn_value less than rule_from '||l_proc, 420);
189 IF p_exclude_flag = 'Y' THEN
190 RETURN TRUE;
191 ELSE
192 RETURN FALSE;
193 END IF;
194 end if;
195 else
196 if p_txn_value between p_rule_from and p_rule_to then
197 hr_utility.set_location('txn_value between rule values '||l_proc, 420);
198 IF p_exclude_flag = 'Y' THEN
199 RETURN FALSE;
200 ELSE
201 RETURN TRUE;
202 END IF;
203 else
204 hr_utility.set_location('txn_value not between rule values '||l_proc, 420);
205 IF p_exclude_flag = 'Y' THEN
206 RETURN TRUE;
207 ELSE
208 RETURN FALSE;
209 END IF;
210 end if;
211 end if;
212 end if;
213 END IF;
214 end if;
215 EXCEPTION
216 WHEN others THEN
217 return false;
218 END CHECK_ATTRIBUTE_RESULT;
219
220 PROCEDURE apply_defined_rules(p_stat_sit_id IN number,
221 p_person_id IN number,
222 p_effective_date IN DATE,
223 p_rule_type IN VARCHAR2 DEFAULT 'REQUIRED',
224 p_status_flag OUT NOCOPY varchar2) is
225 l_proc varchar2(71) := g_package||' apply_defined_rules';
226 l_cond_result boolean;
227 l_transaction_value varchar2(2000);
228 l_rule_from varchar2(2000);
229 l_rule_to varchar2(2000);
230 l_txn_type varchar2(30);
231 l_final_stat varchar2(30);
232 l_rule_message fnd_new_messages.message_text%type;
233 l_attribute_id number;
234 l_required_flag varchar2(2);
235 l_exclude_flag varchar2(2);
236 l_value_style_cd pqh_txn_category_attributes.value_style_cd%type;
237
238 cursor csr_sit_rules (p_required_flag VARCHAR2) is
239 select * from pqh_fr_stat_situation_rules
240 where statutory_situation_id = p_stat_sit_id
241 and nvl(enabled_flag,'N') ='Y'
242 and required_flag = p_required_flag
243 order by processing_sequence;
244 begin
245 hr_utility.set_location('inside'||l_proc,10);
246
247 p_status_flag := 'NO_RULES_DEFINED';
248
249 hr_utility.set_location('p_rule_type '||p_rule_type,10);
250
251 IF p_rule_type = 'REQUIRED' THEN
252 l_required_flag := 'Y';
253 ELSE
254 l_required_flag := 'N';
255 END IF;
256
257 hr_utility.set_location('l_required_flag '||l_required_flag,10);
258
259 for i in csr_sit_rules(l_required_flag) loop
260 hr_utility.set_location('Checking rule conditions '||l_proc,70);
261 l_rule_from := i.from_value;
262 l_rule_to := i.to_value;
263 l_exclude_flag := i.exclude_flag;
264
265 hr_utility.set_location('rule_from is '||l_rule_from,70);
266 hr_utility.set_location('rule_to is '||l_rule_to,70);
267 hr_utility.set_location('Exclude_flag is '||l_exclude_flag,70);
268
269 hr_utility.set_location('Getting Attribute values ',70);
270 get_attribute(i.txn_category_attribute_id, l_attribute_id, l_value_style_cd);
271 hr_utility.set_location('attribute is '||l_attribute_id,70);
272 hr_utility.set_location('l_value_style_cd is '||l_value_style_cd,70);
273
274 if l_attribute_id is not null then
275 l_transaction_value := get_transaction_value(p_person_id => p_person_id,
276 p_effective_date => p_effective_date,
277 p_attribute_id => l_attribute_id
278 );
279 hr_utility.set_location('txn_value '||l_transaction_value,75);
280 end if;
281
282 if l_transaction_value is not null and l_rule_from is not null then
283 hr_utility.set_location('checking result '||l_proc,75);
284 l_cond_result := check_attribute_result(p_rule_from => l_rule_from,
285 p_txn_value => l_transaction_value,
286 p_rule_to => l_rule_to,
287 p_value_style_cd => l_value_style_cd,
288 p_exclude_flag => l_exclude_flag);
289
290 -- hr_utility.set_location('l_cond_result '||to_char(l_cond_result),70);
291
292 IF l_cond_result THEN
293 p_status_flag := 'YES';
294 IF p_rule_type = 'OPTIONAL' THEN
295 EXIT;
296 END IF;
297 ELSE
298 p_status_flag := 'NO';
299 IF p_rule_type = 'REQUIRED' THEN
300 fnd_message.set_name('PQH','PQH_FR_REQD_SIT_RULE_FAIL');
301 HR_MULTI_MESSAGE.ADD;
302 RAISE HR_MULTI_MESSAGE.error_message_exist;
303 EXIT;
304 END IF;
305 END IF;
306 end if;
307 hr_utility.set_location('going for next rule',140);
308 end loop; -- rules for all entities of a folder
309 -- hr_utility.set_location('all rule applied '||l_final_stat,145);
310 hr_utility.set_location('leaving '||l_proc,200);
311 exception
312 when others then
313 hr_utility.set_location('some error '||l_proc,420);
314 raise;
315 end apply_defined_rules;
316
317 Function is_situation_valid (p_person_id in number,
318 p_effective_date IN DATE,
319 p_statutory_situation_id in NUMBER)
320 RETURN VARCHAR2
321 IS
322 l_passed_all_reqd VARCHAR2(30);
323 l_passed VARCHAR2(30);
324 l_return_status VARCHAR2(10) := 'N';
325 l_rule_valid BOOLEAN := FALSE;
326 BEGIN
327 --Validate the Mandatory Conditions first. If it meets all the conditions, then situation is valid.
328 apply_defined_rules(p_statutory_situation_id ,p_person_id,p_effective_date,
329 'REQUIRED',l_passed_all_reqd);
330
331 IF l_passed_all_reqd = 'YES' THEN
332 hr_utility.set_location('l_passed_all_reqd '||l_passed_all_reqd,10);
333 l_return_status := 'Y';
334 ELSIF l_passed_all_reqd = 'NO' THEN
335 hr_utility.set_location('l_passed_all_reqd '||l_passed_all_reqd,10);
336 l_return_status := 'N';
337 ELSIF l_passed_all_reqd = 'NO_RULES_DEFINED' THEN
338 hr_utility.set_location('l_passed_all_reqd '||l_passed_all_reqd,10);
339 --No Required rules defined. See if meets atleast one optional rule.
340 apply_defined_rules(p_statutory_situation_id ,p_person_id,p_effective_date,
341 'OPTIONAL',l_passed);
342 hr_utility.set_location('l_passed '||l_passed,10);
343 IF l_passed = 'YES' OR l_passed = 'NO_RULES_DEFINED' THEN
344 l_return_status := 'Y';
345 ELSE
346 l_return_status := 'N';
347 fnd_message.set_name('PQH','PQH_FR_OPT_SIT_RULE_FAIL');
348 HR_MULTI_MESSAGE.ADD;
349 RAISE HR_MULTI_MESSAGE.error_message_exist;
350 END IF;
351 END IF;
352 RETURN l_return_status;
353 hr_utility.set_location('leaving is_situation_valid ',10);
354 END is_situation_valid;
355
356 end pqh_sit_engine;