[Home] [Help]
PACKAGE BODY: APPS.PQH_CBR_ENGINE
Source
1 PACKAGE BODY PQH_CBR_ENGINE as
2 /* $Header: pqcbreng.pkb 120.0 2005/05/29 01:36:58 appldev noship $ */
3 function build_message(p_application_id in number,
4 p_message_cd in varchar2,
5 p_message_token1 in varchar2 default null,
6 p_message_param1 in varchar2 default null,
7 p_message_token2 in varchar2 default null,
8 p_message_param2 in varchar2 default null,
9 p_message_token3 in varchar2 default null,
10 p_message_param3 in varchar2 default null) return varchar2 is
11 l_message fnd_new_messages.message_text%type;
12 l_proc varchar2(71) := g_package||'build_message';
13 begin
14 hr_utility.set_Location('inside'||l_proc,10);
15 hr_utility.set_message(applid => p_application_id,
16 l_message_name => p_message_cd);
17 if p_message_param1 is not null then
18 hr_utility.set_message_token(l_token_name => p_message_param1,
19 l_token_value => p_message_token1);
20 end if;
21 if p_message_param2 is not null then
22 hr_utility.set_message_token(l_token_name => p_message_param2,
23 l_token_value => p_message_token2);
24 end if;
25 if p_message_param3 is not null then
26 hr_utility.set_message_token(l_token_name => p_message_param3,
27 l_token_value => p_message_token3);
28 end if;
29 l_message := hr_utility.get_message;
30 hr_utility.set_Location('message'||substr(l_message,1,60),20);
31 hr_utility.set_Location('leaving'||l_proc,100);
32 return l_message;
33 end build_message;
34 procedure get_period_details (p_budget_period_id in number,
35 p_budget_entity_id in number,
36 p_prd_avl_amt out nocopy number,
37 p_prd_act_amt out nocopy number,
38 p_prd_com_amt out nocopy number,
39 p_prd_don_amt out nocopy number,
40 p_prd_res_amt out nocopy number,
41 p_prd_bgt_amt out nocopy number,
42 p_prd_start_date out nocopy date) is
43 l_prd_start_date date;
44 l_prd_end_date date;
45 l_job_id number;
46 l_grade_id number;
47 l_organization_id number;
48 l_unit1_value number;
49 l_unit2_value number;
50 l_unit3_value number;
51 l_proc varchar2(71) := g_package||'get_period_details';
52 begin
53 hr_utility.set_Location('inside'||l_proc,10);
54 select calstart.start_date,calend.end_date,budget_unit1_value,budget_unit2_value,budget_unit3_value
55 into l_prd_start_date,l_prd_end_date,l_unit1_value,l_unit2_value,l_unit3_value
56 from pqh_budget_periods per,per_time_periods calstart, per_time_periods calend
57 where budget_period_id = p_budget_period_id
58 and per.start_time_period_id = calstart.time_period_id
59 and per.end_time_period_id = calend.time_period_id;
60
61 if g_budget_unit_num = 1 then
62 p_prd_bgt_amt := nvl(l_unit1_value,0);
63 elsif g_budget_unit_num = 2 then
64 p_prd_bgt_amt := nvl(l_unit2_value,0);
65 elsif g_budget_unit_num = 3 then
66 p_prd_bgt_amt := nvl(l_unit3_value,0);
67 end if;
68 hr_utility.set_Location('bgt_amt is'||p_prd_bgt_amt,20);
69 if g_budget_entity ='POSITION' then
70 p_prd_act_amt := pqh_mgmt_rpt_pkg.get_position_actual_cmmtmnts
71 (p_budget_version_id => g_budget_version_id,
72 p_position_id => p_budget_entity_id,
73 p_start_date => l_prd_start_date,
74 p_end_date => l_prd_end_date,
75 p_unit_of_measure_id => g_budget_unit_id,
76 p_value_type => 'A' ,
77 p_currency_code => g_budget_currency);
78 hr_utility.set_Location('act_amt is'||p_prd_act_amt,30);
79 if g_budget_unit_type ='M' then
80 p_prd_com_amt:= pqh_mgmt_rpt_pkg.get_position_actual_cmmtmnts
81 (p_budget_version_id => g_budget_version_id,
82 p_position_id => p_budget_entity_id,
83 p_start_date => l_prd_start_date,
84 p_end_date => l_prd_end_date,
85 p_unit_of_measure_id => g_budget_unit_id,
86 p_value_type => 'C' ,
87 p_currency_code => g_budget_currency);
88 else
89 p_prd_com_amt := nvl(p_prd_act_amt,0);
90 end if;
91 hr_utility.set_Location('com_amt is'||p_prd_com_amt,40);
92 else
93 if g_budget_entity ='JOB' then
94 l_job_id := p_budget_entity_id;
95 elsif g_budget_entity ='GRADE' then
96 l_grade_id := p_budget_entity_id;
97 elsif g_budget_entity ='ORGANIZATION' then
98 l_organization_id := p_budget_entity_id;
99 end if;
100 p_prd_act_amt := pqh_mgmt_rpt_pkg.get_entity_actual_cmmtmnts
101 (p_budget_version_id => g_budget_version_id,
102 p_budgeted_entity_cd => g_budget_entity,
103 p_job_id => l_job_id,
104 p_grade_id => l_grade_id,
105 p_organization_id => l_organization_id,
106 p_start_date => l_prd_start_date,
107 p_end_date => l_prd_end_date,
108 p_unit_of_measure_id => g_budget_unit_id,
109 p_value_type => 'A' ,
110 p_currency_code => g_budget_currency);
111 hr_utility.set_Location('act_amt is'||p_prd_act_amt,50);
112 if g_budget_unit_type ='M' then
113 p_prd_com_amt := pqh_mgmt_rpt_pkg.get_entity_actual_cmmtmnts
114 (p_budget_version_id => g_budget_version_id,
115 p_budgeted_entity_cd => g_budget_entity,
116 p_job_id => l_job_id,
117 p_grade_id => l_grade_id,
118 p_organization_id => l_organization_id,
119 p_start_date => l_prd_start_date,
120 p_end_date => l_prd_end_date,
121 p_unit_of_measure_id => g_budget_unit_id,
122 p_value_type => 'C' ,
123 p_currency_code => g_budget_currency);
124 else
125 p_prd_com_amt := p_prd_act_amt;
126 end if;
127 hr_utility.set_Location('com_amt is'||p_prd_com_amt,60);
128 end if;
129 p_prd_don_amt := pqh_bdgt_realloc_utility.get_prd_realloc_reserved_amt
130 (p_budget_period_id => p_budget_period_id,
131 p_entity_type => g_budget_entity,
132 p_budget_unit_id => g_budget_unit_id,
133 p_transaction_type => 'DD', -- donated
134 p_approval_status => 'A', -- approved
135 p_amount_type => 'R'); -- Reallocated
136 hr_utility.set_Location('don_amt is'||p_prd_don_amt,70);
137 --
138 p_prd_res_amt := pqh_bdgt_realloc_utility.get_prd_realloc_reserved_amt
139 (p_budget_period_id => p_budget_period_id,
140 p_entity_type => g_budget_entity,
141 p_budget_unit_id => g_budget_unit_id,
142 p_transaction_type => 'DD', -- donated
143 p_approval_status => 'A', -- approved
144 p_amount_type => 'RV'); -- Reserved
145 hr_utility.set_Location('res_amt is'||p_prd_res_amt,80);
146 --
147 if g_budget_unit_type ='M' then
148 p_prd_avl_amt := nvl(p_prd_bgt_amt,0) - nvl(p_prd_act_amt,0) - nvl(p_prd_com_amt,0) - nvl(p_prd_don_amt,0)- nvl(p_prd_res_amt,0);
149 else
150 p_prd_avl_amt := nvl(p_prd_bgt_amt,0) - nvl(p_prd_act_amt,0) - nvl(p_prd_don_amt,0) - nvl(p_prd_res_amt,0);
151 end if;
152 hr_utility.set_Location('avl_amt is'||p_prd_avl_amt,90);
153 hr_utility.set_Location('leaving'||l_proc,100);
154 exception
155 when others then
156 hr_utility.set_location('errors in computing period details',420);
157 raise;
158 end get_period_details;
159
160 function get_txn_child_count(p_child_type in varchar2,
161 p_txn_id in number) return number is
162 l_number number;
163 begin
164 select count(*)
165 into l_number
166 from pqh_bdgt_pool_realloctions
167 where pool_id = p_txn_id
168 and transaction_type = p_child_type;
169 return l_number;
170 exception
171 when others then
172 hr_utility.set_location('invalid child type',10);
173 raise;
174 end;
175 procedure get_txn_balances(p_txn_id in number,
176 p_donor_realloc_amt out nocopy number,
177 p_donor_reserve_amt out nocopy number,
178 p_rcvr_realloc_amt out nocopy number) is
179 begin
180 select sum(nvl(period.reallocation_amt,0)),sum(nvl(period.reserved_amt,0))
181 into p_donor_realloc_amt,p_donor_reserve_amt
182 from pqh_bdgt_pool_realloctions donor, pqh_bdgt_pool_realloctions period
183 where donor.pool_id = p_txn_id
184 and period.txn_detail_id = donor.reallocation_id
185 and donor.transaction_type = 'D'
186 and period.transaction_type = 'DD' ;
187
188 select sum(nvl(period.reallocation_amt,0))
189 into p_rcvr_realloc_amt
190 from pqh_bdgt_pool_realloctions rcvr, pqh_bdgt_pool_realloctions period
191 where rcvr.pool_id = p_txn_id
192 and period.txn_detail_id = rcvr.reallocation_id
193 and rcvr.transaction_type = 'R'
194 and period.transaction_type = 'RD' ;
195 exception
196 when others then
197 hr_utility.set_location('invalid txn_id ',10);
198 raise;
199 end;
200 procedure get_budget_details(p_budget_version_id in number,
201 p_budget_unit_id in number,
202 p_budget_name out nocopy varchar2,
203 p_budget_currency out nocopy varchar2,
204 p_entity_type out nocopy varchar2,
205 p_budget_start_date out nocopy date,
206 p_budget_end_date out nocopy date,
207 p_budget_unit_name out nocopy varchar2,
208 p_budget_unit_num out nocopy number,
209 p_bmu_name out nocopy varchar2,
210 p_budget_unit_type out nocopy varchar2) is
211 l_budget_id number;
212 l_budget_unit1_id number;
213 l_budget_unit2_id number;
214 l_budget_unit3_id number;
215 begin
216 -- budget details are to be pulled in
217 select bgt.budget_id,bgt.budget_name,bgt.budgeted_entity_cd,bgt.budget_start_date,
218 bgt.budget_end_date,bgt.currency_code,bgt.budget_unit1_id,bgt.budget_unit2_id,bgt.budget_unit3_id
219 into l_budget_id,p_budget_name,p_entity_type,p_budget_start_date,
220 p_budget_end_date,p_budget_currency,l_budget_unit1_id,l_budget_unit2_id,l_budget_unit3_id
221 from pqh_budgets bgt, pqh_budget_versions bvr
222 where bgt.budget_id = bvr.budget_id
223 and bvr.budget_version_id = p_budget_version_id
224 and bgt.position_control_flag ='Y';
225
226 -- business group currency to be used, in budget currency null
227 if p_budget_currency is null then
228 p_budget_currency := pqh_budget.get_currency_cd(p_budget_id => l_budget_id);
229 end if;
230
231 -- unit num which is being reallocated
232 if l_budget_unit1_id = p_budget_unit_id then
233 p_budget_unit_num := 1;
234 elsif l_budget_unit2_id = p_budget_unit_id then
235 p_budget_unit_num := 2;
236 elsif l_budget_unit3_id = p_budget_unit_id then
237 p_budget_unit_num := 3;
238 else
239 hr_utility.set_location ('Error condition',10);
240 end if;
241
242 -- unit details are to be pulled in
243 select system_type_cd,shared_type_name
244 into p_bmu_name,p_budget_unit_name
245 from per_shared_types
246 where lookup_type ='BUDGET_MEASUREMENT_TYPE'
247 and shared_type_id = p_budget_unit_id;
248 if p_bmu_name ='MONEY' then
249 p_budget_unit_type := 'M' ;
250 else
251 p_budget_unit_type := 'N' ;
252 end if;
253 exception
254 when others then
255 hr_utility.set_location('invalid budget_id',10);
256 raise;
257 end get_budget_details;
258 function check_org_valid_rule(p_organization_id in number,
259 p_rule_org_id in number,
260 p_rule_applicability in varchar2,
261 p_rule_category in varchar2,
262 p_rule_org_str_id in number,
263 p_rule_start_org_id in number) return boolean is
264 l_proc varchar2(71) := g_package||'check_org_valid_rule';
265 Cursor csr_parent_nodes(P_ORGANIZATION_ID in number ,
266 P_ORG_STRUCTURE_VERSION_ID in number) is
267 Select level,organization_id_parent
268 From per_org_structure_elements
269 where org_structure_version_id = p_org_structure_version_id
270 connect by prior organization_id_parent = organization_id_child
271 and ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
272 start with ORG_STRUCTURE_VERSION_ID = P_ORG_STRUCTURE_VERSION_ID
273 and organization_id_child = P_ORGANIZATION_ID
274 UNION
275 Select 0,p_organization_id
276 from dual
277 order by 1 asc;
278 l_lowest_level boolean;
279 l_org_str_version_id number;
280 l_rule_level_cd varchar2(30);
281 l_rule_set_id number;
282 l_lo_rule_level_cd varchar2(30);
283 l_lo_rule_set_id number;
284 l_lo_start_org_id number;
285 l_lo_org_str_id number;
286 l_rule_exists boolean;
287 begin
288 hr_utility.set_Location('inside'||l_proc,10);
289 if p_rule_org_id = p_organization_id then
290 l_rule_exists := TRUE;
291 -- rule is defined at current org
292 else
293 hr_utility.set_Location('get_org_hier'||l_proc,20);
294 get_org_structure_version_id
295 (p_org_structure_id => p_rule_org_str_id,
296 p_org_structure_version_id=> l_org_str_version_id);
297 for l_str_nodes_rec in csr_parent_nodes(p_organization_id => p_organization_id,
298 p_org_structure_version_id => l_org_str_version_id) loop
299 hr_utility.set_Location('finding nodes'||l_proc,30);
300 l_rule_exists := check_rule_existence
301 (p_organization_structure_id => p_rule_org_str_id,
302 p_starting_organization_id => l_str_nodes_rec.organization_id_parent,
303 p_business_group_id => g_business_group_id,
304 p_rule_category => p_rule_category,
305 p_rule_applicability => p_rule_applicability);
306 if l_rule_exists then
307 -- rule is found in the hierarchy
308 hr_utility.set_Location('get out of loop '||l_proc,40);
309 exit;
310 end if;
311 end loop;
312 end if;
313 return l_rule_exists;
314 end check_org_valid_rule;
315
316 function check_rule_existence(p_organization_structure_id in number,
317 p_starting_organization_id in number,
318 p_business_group_id in number,
319 p_rule_category in varchar2,
320 p_rule_applicability in varchar2) return boolean is
321 l_rule_set_id number;
322 begin
323 hr_utility.set_Location('check_rule_existence p_organization_structure_id'||p_organization_structure_id,40);
324 hr_utility.set_Location('check_rule_existence p_starting_organization_id'||p_starting_organization_id,40);
325 hr_utility.set_Location('check_rule_existence p_business_group_id'||p_business_group_id,40);
326 hr_utility.set_Location('check_rule_existence p_rule_category'||p_rule_category,40);
327 hr_utility.set_Location('check_rule_existence p_rule_applicability'||p_rule_applicability,40);
328 select rule_set_id
329 into l_rule_set_id
330 from pqh_rule_sets
331 where business_group_id = p_business_group_id
332 and organization_structure_id = p_organization_structure_id
333 and starting_organization_id = p_starting_organization_id
334 and rule_category =p_rule_category
335 and rule_applicability = p_rule_applicability;
336 if l_rule_set_id is not null then
337 hr_utility.set_Location('rule exists ',40);
338 return TRUE;
339 else
340 hr_utility.set_Location('rule does not exist ',40);
341 return FALSE;
342 end if;
343 exception
344 when others then
345 hr_utility.set_Location('check_rule_existence errors',40);
346 return FALSE;
347 end check_rule_existence;
348
349 function get_position_organization(p_position_id in number,
350 p_effective_date in date) return number is
351 l_organization_id number;
352 begin
353 select organization_id
354 into l_organization_id
355 from hr_all_positions_f
356 where position_id = p_position_id
357 and p_effective_date between effective_start_date and effective_end_date;
358 return l_organization_id;
359 end get_position_organization;
360 --
361 Procedure get_org_structure_version_id (p_org_structure_id IN NUMBER,
362 p_org_structure_version_id OUT nocopy NUMBER) is
363 Cursor c1 is
364 Select org_structure_version_id
365 From per_org_structure_versions
366 Where organization_structure_id = p_org_structure_id
367 AND version_number =
368 (select max(version_number)
369 From per_org_structure_versions
370 Where organization_structure_id = p_org_structure_id);
371 --
372 l_proc varchar2(72) := g_package||'g_org_structure_version_id';
373 --
374 Begin
375 hr_utility.set_location('Entering:'||l_proc, 5);
376 --
377 Open c1;
378 Fetch c1 into p_org_structure_version_id;
379 If c1%notfound then
380 hr_utility.set_message(8302, 'PQH_ORG_STRUCT_VER_NOT_FOUND');
381 hr_utility.raise_error;
382 End if;
383 Close c1;
384 --
385 --
386 hr_utility.set_location(' Leaving:'||l_proc, 10);
387 exception
388 when others then
389 p_org_structure_version_id := null;
390 End;
391 --
392 procedure populate_context(p_entity_id in number,
393 p_folder_id in number) is
394 l_proc varchar2(71) := g_package||'populate_context';
395 begin
396 -- Following parameters should be there in g_refresh_tab for right conversion
397 -- Position_id : Current entity id is budget entity is position
398 -- Business_group_id : we have it in global variable
399 -- Organization_id : for position or organization we will get it.
400 -- Pool_id : I guess it is folder id , we will be having it
401 -- Entity_id : is current row.
402 hr_utility.set_location('Entering:'||l_proc, 5);
403 pqh_refresh_data.g_refresh_tab.DELETE;
404 hr_utility.set_location('dropped the context table:'||l_proc, 10);
405 pqh_refresh_data.g_refresh_tab(1).column_name := 'ENTITY_ID';
406 pqh_refresh_data.g_refresh_tab(1).TXN_VAL := p_entity_id;
407 pqh_refresh_data.g_refresh_tab(2).column_name := 'BUSINESS_GROUP_ID';
408 pqh_refresh_data.g_refresh_tab(2).TXN_VAL := g_business_group_id;
409 pqh_refresh_data.g_refresh_tab(3).column_name := 'POOL_ID';
410 pqh_refresh_data.g_refresh_tab(3).TXN_VAL := p_folder_id;
411 hr_utility.set_location('budget entity is:'||g_budget_entity||l_proc, 20);
412 if g_budget_entity ='ORGANIZATION' then
413 pqh_refresh_data.g_refresh_tab(4).column_name := 'ORGANIZATION_ID';
414 pqh_refresh_data.g_refresh_tab(4).TXN_VAL := p_entity_id;
415 elsif g_budget_entity ='POSITION' then
416 -- l_pos_organization_id := get_position_organization(p_position_id => p_entity_id,
417 -- p_effective_date => trunc(sysdate));
418 -- pqh_refresh_data.g_refresh_tab(4).column_name := 'ORGANIZATION_ID';
419 -- pqh_refresh_data.g_refresh_tab(4).TXN_VAL := l_organization_id;
420 pqh_refresh_data.g_refresh_tab(4).column_name := 'POSITION_ID';
421 pqh_refresh_data.g_refresh_tab(4).TXN_VAL := p_entity_id;
422 end if;
423 hr_utility.set_location('leaving '||l_proc, 100);
424 end populate_context;
425 --
426 Function GET_TRANSACTION_VALUE (p_entity_id IN number,
427 p_attribute_id IN number) RETURN varchar2 IS
428 l_sel_stmt varchar2(4000);
429 l_from_clause PQH_TABLE_ROUTE.FROM_CLAUSE%TYPE;
430 l_where_clause_in PQH_TABLE_ROUTE.WHERE_CLAUSE%TYPE;
431 l_where_clause_out PQH_TABLE_ROUTE.WHERE_CLAUSE%TYPE;
432 l_column_name PQH_ATTRIBUTES.COLUMN_NAME%TYPE;
433 l_column_type PQH_ATTRIBUTES.COLUMN_TYPE%TYPE;
434 l_selected_value_v varchar2(2000);
435 l_selected_value_n number;
436 l_selected_value_d date;
437 l_proc varchar2(100) := 'get_transaction_value';
438 l_table_route_id PQH_TABLE_ROUTE.TABLE_ROUTE_ID%TYPE;
439 BEGIN
440 hr_utility.set_location('Entering : '||l_proc, 5);
441 if (p_entity_id is not null and p_attribute_id is not null) then
442
443 -- get the attribute details
444 select column_name, master_table_route_id,column_type
445 into l_column_name, l_table_route_id,l_column_type
446 from pqh_attributes
447 where attribute_id = p_attribute_id;
448 hr_utility.set_location('column_name1 is : '||substr(l_column_name,1,50),10);
449 hr_utility.set_location('column_name2 is : '||substr(l_column_name,51,50),11);
450 hr_utility.set_location('column_name3 is : '||substr(l_column_name,101,50),12);
451 hr_utility.set_location('column_name4 is : '||substr(l_column_name,151,50),13);
452 hr_utility.set_location('column_type is : '||l_column_type,15);
453 hr_utility.set_location('table_route is : '||l_table_route_id,20);
454
455 -- table route is selected, get the details
456
457 select from_clause, where_clause
458 into l_from_clause, l_where_clause_in
459 from pqh_table_route where table_route_id = l_table_route_id;
460 --broke the set_location for from_clause into two lines kgowripe
461 hr_utility.set_location('from_clause1 is : '||substr(l_from_clause,1,30),30);
462 hr_utility.set_location('from_clause2 is : '||substr(l_from_clause,31,30),31);
463 hr_utility.set_location('where_clause 1is : '||substr(l_where_clause_in,1,40),40);
464 hr_utility.set_location('where_clause 2is : '||substr(l_where_clause_in,41,40),40);
465
466 -- update the where clause with the context values
467
468 pqh_refresh_data.replace_where_params(
469 p_where_clause_in => l_where_clause_in,
470 p_txn_tab_flag => 'N',
471 p_txn_id => p_entity_id,
472 p_where_clause_out => l_where_clause_out);
473 hr_utility.set_location('where_clause 1is : '||substr(l_where_clause_out,1,40),50);
474 hr_utility.set_location('where_clause 2is : '||substr(l_where_clause_out,41,40),50);
475
476 -- build up the statement to be used for getting the value
477 l_sel_stmt := 'select '||l_column_name||' from '||l_from_clause||' where '||l_where_clause_out ;
478 hr_utility.set_location('stmt1 '||substr(l_sel_stmt,1,60),55);
479 hr_utility.set_location('stmt2 '||substr(l_sel_stmt,61,60),55);
480 hr_utility.set_location('stmt3 '||substr(l_sel_stmt,121,60),55);
481 hr_utility.set_location('stmt4 '||substr(l_sel_stmt,181,60),55);
482 hr_utility.set_location('stmt5 '||substr(l_sel_stmt,241,60),55);
483 hr_utility.set_location('stmt6 '||substr(l_sel_stmt,361,60),55);
484
485 -- execute the dynamic sql
486 if l_column_type ='D' then
487 hr_utility.set_location('date being fetched ',60);
488 execute immediate l_sel_stmt into l_selected_value_d;
489 -- converting the date to character format
490 l_selected_value_v := fnd_date.date_to_canonical(l_selected_value_d);
491 elsif l_column_type ='N' then
492 hr_utility.set_location('number being fetched ',60);
493 execute immediate l_sel_stmt into l_selected_value_n;
494 l_selected_value_v := to_char(l_selected_value_n);
495 else
496 hr_utility.set_location('varchar being fetched ',60);
497 execute immediate l_sel_stmt into l_selected_value_v;
498 end if;
499 hr_utility.set_location('leaving with value: '||l_selected_value_v, 90);
500 return l_selected_value_v;
501 else
502 hr_utility.set_location('values passed was null. '||l_proc, 420);
503 return null;
504 end if;
505 EXCEPTION
506 when no_data_found then
507 hr_utility.set_location('no data exists '||l_proc, 100);
508 return null;
509 WHEN others THEN
510 hr_utility.set_location('Failure in program unit: '||l_proc, 420);
511 return null;
512 END GET_TRANSACTION_VALUE;
513
514 function check_attribute_result(p_rule_value in varchar2,
515 p_txn_value in varchar2,
516 p_operation_code in varchar2,
517 p_attribute_type in varchar2) return BOOLEAN is
518 BEGIN
519 if (p_rule_value is null or
520 p_attribute_type is null or
521 p_txn_value is null or
522 p_operation_code is null) then
523 return false;
524 else
525 hr_utility.set_location('p_rule_value is '||p_rule_value, 5);
526 hr_utility.set_location('p_attribute_type is '||p_attribute_type, 10);
527 hr_utility.set_location('p_txn_value is '||p_txn_value, 15);
528 hr_utility.set_location('p_operation_code is '||p_operation_code, 20);
529 if p_operation_code = 'EQ' then
530 if p_txn_value = p_rule_value then
531 hr_utility.set_location('EQ true', 25);
532 return TRUE;
533 else
534 hr_utility.set_location('EQ false', 25);
535 return false;
536 end if;
537 elsif p_operation_code = 'GT' then
538 --added by kgowripe for Numeric attribute comparision
539 if p_attribute_type = 'N' THEN
540 if to_number(p_txn_value) > to_number(p_rule_value) then
541 hr_utility.set_location('Numeric GT True',21);
542 return true;
543 else
544 hr_utility.set_location('Numeric GT False',21);
545 return false;
546 end if;
547 else
548 --code changes by kgowripe ends
549 if p_txn_value > p_rule_value then
550 hr_utility.set_location('GT true', 25);
551 return true;
552 else
553 hr_utility.set_location('GT false', 25);
554 return false;
555 end if;
556 end if;
557 elsif p_operation_code = 'LT' then
558 --added by kgowripe for Numeric attribute comparision
559 if p_attribute_type = 'N' then
560 if to_number(p_txn_value) < to_number(p_rule_value) then
561 hr_utility.set_location('Numeric LT Rule true ',22);
562 return true;
563 else
564 hr_utility.set_location('Numeric LT Rule False',22);
565 return false;
566 end if;
567 else
568 --end code changes by kgowripe
569 if p_txn_value < p_rule_value then
570 hr_utility.set_location('LT true', 25);
571 return true;
572 else
573 hr_utility.set_location('LT false', 25);
574 return false;
575 end if;
576 end if;
577 elsif p_operation_code = 'NEQ' then
578 if p_txn_value <> p_rule_value then
579 hr_utility.set_location('NEQ true', 25);
580 return true;
581 else
582 hr_utility.set_location('NEQ false', 25);
583 return false;
584 end if;
585 else
586 return false;
587 end if;
588 end if;
589 EXCEPTION
590 WHEN others THEN
591 return false;
592 END CHECK_ATTRIBUTE_RESULT;
593
594 -- routine, which will be called by page.
595 PROCEDURE apply_rules(p_transaction_type IN varchar2,
596 p_business_group_id IN Number,
597 p_transaction_id IN number,
598 p_effective_date IN date DEFAULT sysdate,
599 p_status_flag OUT NOCOPY varchar2) is
600 l_proc varchar2(71) := g_package||'apply_rules';
601 begin
602 hr_utility.set_location('inside '||l_proc,10);
603 if p_transaction_type ='REALLOCATION' then
604 hr_utility.set_location('calling CBR '||l_proc,20);
605 apply_cbr_realloc(p_transaction_id => p_transaction_id,
606 p_business_group_id => p_business_group_id,
607 p_effective_date => p_effective_date,
608 p_status_flag => p_status_flag);
609 hr_utility.set_location('finished CBR_ENG with status'||p_status_flag,40);
610 else
611 hr_utility.set_location('invalid txn type'||p_transaction_type,30);
612 end if;
613 hr_utility.set_location('leaving '||l_proc,100);
614 end apply_rules;
615
616 -- routine which cntrols reallocation related rule applications
617 PROCEDURE apply_CBR_realloc(p_transaction_id IN number,
618 p_business_group_id IN number,
619 p_effective_date IN DATE,
620 p_status_flag OUT NOCOPY varchar2) is
621 l_proc varchar2(71) := g_package||'apply_cbr_realloc';
622 l_status_flag boolean;
623 begin
624 hr_utility.set_location('inside '||l_proc,10);
625 populate_globals(p_transaction_id => p_transaction_id);
626 hr_utility.set_location('globals populated '||l_proc,20);
627 apply_business_rules(p_transaction_id => p_transaction_id,
628 p_business_group_id => p_business_group_id,
629 p_effective_date => p_effective_date,
630 p_status_flag => l_status_flag);
631 if l_status_flag then
632 hr_utility.set_location('out with status TRUE '||l_proc,20);
633 else
634 hr_utility.set_location('out with status FALSE'||l_proc,30);
635 end if;
636 if l_status_flag then
637 hr_utility.set_location('checking defined rules'||l_proc,40);
638 apply_defined_rules(p_transaction_id => p_transaction_id,
639 p_business_group_id => p_business_group_id,
640 p_effective_date => p_effective_date,
641 p_status_flag => p_status_flag);
642 if p_status_flag is null then
643 hr_utility.set_location('defined rules cleared',50);
644 else
645 hr_utility.set_location('defined rules failed',60);
646 end if;
647 else
648 hr_utility.set_location('business rules failed',70);
649 p_status_flag := 'E';
650 end if;
651 end apply_cbr_realloc;
652
653 -- process rules are applied for donor/ receivers
654 PROCEDURE apply_defined_rules(p_transaction_id IN number,
655 p_business_group_id IN number,
656 p_effective_date IN DATE,
657 p_status_flag OUT NOCOPY varchar2) is
658 l_proc varchar2(71) := g_package||'apply_defined_rules';
659 l_rule_matx t_rule_matx;
660 l_cond_matx t_cond_matx;
661 l_attr_matx t_attr_matx;
662 l_cond_result boolean;
663 l_transaction_value varchar2(2000);
664 l_prev_txn_id number;
665 l_prev_txn_type varchar2(30);
666 l_txn_type varchar2(30);
667 l_final_stat varchar2(30);
668 l_prev_entity_id number;
669 l_rule_message fnd_new_messages.message_text%type;
670 rule_counter number;
671 cond_counter number;
672 begin
673 hr_utility.set_location('inside'||l_proc,10);
674 -- attribute properties populated
675 populate_attr_matx(l_attr_matx);
676 /*
677 -- used for debugging only
678 for i in 1..l_attr_matx.count loop
679 hr_utility.set_location(i||'column_name '||substr(l_attr_matx(i).column_name,1,50),70);
680 hr_utility.set_location(i||'entity_type '||l_attr_matx(i).entity_type,80);
681 hr_utility.set_location(i||'applicability '||l_attr_matx(i).applicability,90);
682 end loop;
683 */
684 hr_utility.set_location('attribute matrix populated',20);
685 -- valid rules for the folder populated
686 valid_process_rules(p_transaction_id => p_transaction_id,
687 p_business_group_id => p_business_group_id,
688 p_rule_category => 'REALLOCATION',
689 p_effective_date => p_effective_date,
690 l_rule_matx => l_rule_matx);
691 hr_utility.set_location('valid_rule matrix populated',30);
692 -- folder process logging started
693 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => 'F',
694 p_folder_id => p_transaction_id);
695 hr_utility.set_location('rules to apply are :'||l_rule_matx.count,40);
696 for rule_counter in 1..l_rule_matx.count loop
697 hr_utility.set_location('applying rule '||l_rule_matx(rule_counter).rule_set_id,20);
698 if l_rule_matx(rule_counter).rule_applicability ='DONOR' then
699 l_txn_type := 'D';
700 elsif l_rule_matx(rule_counter).rule_applicability ='RECEIVER' then
701 l_txn_type := 'R';
702 else
703 hr_utility.set_location('rule applicability is'||l_rule_matx(rule_counter).rule_applicability ,30);
704 end if;
705 /*
706 If rule matrix is not ordered by entity id then we will have an issue
707 */
708 -- get the row, if the txn is same as last one, we don't have to start the log
709 if l_prev_txn_id is not null then
710 -- not 1st time in loop, variable is set
711 hr_utility.set_location('inside itxn chk',25);
712 if l_prev_txn_id <> nvl(l_rule_matx(rule_counter).txn_id,-1) then
713 hr_utility.set_location('inside entity chk',28);
714 -- different txn is being started
715 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => l_prev_txn_type,
716 p_folder_id => p_transaction_id,
717 p_transaction_id => l_prev_txn_id,
718 p_entity_id => l_prev_entity_id);
719 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => 'T',
720 p_folder_id => p_transaction_id,
721 p_transaction_id => l_prev_txn_id);
722 hr_utility.set_location('old log ended, new being started ',29);
723 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => 'T',
724 p_folder_id => p_transaction_id,
725 p_transaction_id => l_prev_txn_id);
726 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => l_txn_type, --line modified by kgowripe
727 -- l_rule_matx(rule_counter).rule_applicability,
728 p_folder_id => p_transaction_id,
729 p_transaction_id => l_rule_matx(rule_counter).txn_id,
730 --Added by kgowripe
731 p_bdgt_entity_type=> g_budget_entity,
732 --
733 p_entity_id => l_rule_matx(rule_counter).entity_id);
734 hr_utility.set_location('new log started ',30);
735 else
736 -- same txn is getting start check same entity
737 hr_utility.set_location('same txn ',35);
738 if l_prev_entity_id <> l_rule_matx(rule_counter).entity_id then
739 -- different entity getting start
740 hr_utility.set_location('different txn ',40);
741 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => l_prev_txn_type,
742 p_folder_id => p_transaction_id,
743 p_transaction_id => l_prev_txn_id,
744 p_entity_id => l_prev_entity_id);
745 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => l_txn_type,-- line modified by kgowripe
746 --l_rule_matx(rule_counter).rule_applicability,
747 p_folder_id => p_transaction_id,
748 p_transaction_id => l_rule_matx(rule_counter).txn_id,
749 --Added by kgowripe
750 p_bdgt_entity_type=> g_budget_entity,
751 --
752 p_entity_id => l_rule_matx(rule_counter).entity_id);
753 hr_utility.set_location('log started ',45);
754 else
755 -- same entity is being worked on
756 hr_utility.set_location('another rule for entity ',50);
757 end if;
758 end if;
759 else
760 -- 1st time in loop so start the txn and entity, nothing is there to end.
761 hr_utility.set_location('1st time in rule chk ',60);
762 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => 'T',
763 p_folder_id => p_transaction_id,
764 p_transaction_id => l_rule_matx(rule_counter).txn_id);
765 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => l_txn_type,
766 p_folder_id => p_transaction_id,
767 p_transaction_id => l_rule_matx(rule_counter).txn_id,
768 --Added by kgowripe
769 p_bdgt_entity_type=> g_budget_entity,
770 --
771 p_entity_id => l_rule_matx(rule_counter).entity_id);
772 hr_utility.set_location('log started ',65);
773 end if;
774 hr_utility.set_location('populate rule conditions'||l_proc,68);
775 valid_rule_conditions(p_entity_type => g_budget_entity,
776 p_rule_set_id => l_rule_matx(rule_counter).rule_set_id,
777 p_rule_applicability => l_rule_matx(rule_counter).rule_applicability,
778 p_attr_matx => l_attr_matx,
779 p_cond_matx => l_cond_matx);
780 -- context is set for applying all rules belonging to a condition
781 populate_context(p_entity_id => l_rule_matx(rule_counter).entity_id,
782 p_folder_id => p_transaction_id);
783 for cond_counter in 1..l_cond_matx.count loop
784 hr_utility.set_location('applying rule conditions'||l_proc,70);
785 l_transaction_value := get_transaction_value
786 (p_entity_id => l_rule_matx(rule_counter).entity_id,
787 p_attribute_id => l_cond_matx(cond_counter).attribute_id);
788 hr_utility.set_location('txn_value '||l_transaction_value,75);
789 if l_transaction_value is not null
790 and l_cond_matx(cond_counter).attribute_value is not null then
791 hr_utility.set_location('checking result '||l_proc,75);
792 l_cond_result := check_attribute_result
793 (p_rule_value => l_cond_matx(cond_counter).attribute_value,
794 p_txn_value => l_transaction_value,
795 p_operation_code => l_cond_matx(cond_counter).operation_code,
796 p_attribute_type => l_cond_matx(cond_counter).column_type);
797 if l_cond_result then
798 -- rule is satisfied, hence process log should be updated with the values
799 hr_utility.set_location('condition is being satisfied by txn',10);
800 if l_rule_matx(rule_counter).rule_level_cd ='E' then
801 p_status_flag := 'E';
802 -- rule is an error rule, so no more conditions for this rule
803 /* exit after logging the error message in process log. commented by kgowripe
804 exit; -- get out of condition loop
805 */
806 else
807 p_status_flag := 'W';
808 end if;
809 l_rule_message := build_message (p_application_id => 8302,
810 p_message_cd => l_rule_matx(rule_counter).message_cd);
811 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
812 (p_folder_id => p_transaction_id,
813 p_transaction_id => l_rule_matx(rule_counter).txn_id,
814 p_txn_entity_type => l_txn_type, -- line modified by kgowripe
815 --l_rule_matx(rule_counter).rule_applicability,
816 --Added by kgowripe
817 p_bdgt_entity_type=> g_budget_entity,
818 p_rule_name => l_rule_matx(rule_counter).rule_name,
819 --
820 p_entity_id => l_rule_matx(rule_counter).entity_id,
821 p_rule_level => p_status_flag,
822 p_rule_msg_cd => l_rule_message);
823 --code inserted by kgowripe
824 --leave the conditions loop after logging the message, in case the rule level is Error
825 if l_rule_matx(rule_counter).rule_level_cd = 'E' then
826 exit;
827 end if;
828 --end code inserted by kgowripe
829 else
830 -- rule fails
831 hr_utility.set_location('condition no match ',90);
832 end if;
833 end if;
834 hr_utility.set_location('going for next condition',100);
835 end loop; -- conditions for a rule
836 l_prev_txn_type := l_txn_type; -- modified by kgowripe-- l_rule_matx(rule_counter).rule_applicability;
837 l_prev_entity_id := l_rule_matx(rule_counter).entity_id;
838 l_prev_txn_id := l_rule_matx(rule_counter).txn_id;
839
840 -- if final stat is null and we encounter some issue, we log that
841 -- or we encounter an error we log that
842 -- so if an error is reported and after that 4 warnings, final stat will be 'E'
843 if p_status_flag ='E' or (l_final_stat is null and p_status_flag is not null) then
844 -- this status will be passed to the calling routine
845 l_final_stat := p_status_flag;
846 end if;
847 p_status_flag := '';
848 hr_utility.set_location('going for next rule',140);
849 end loop; -- rules for all entities of a folder
850 hr_utility.set_location('all rule applied '||l_final_stat,145);
851 -- close the entity, txn and folder processing
852 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => l_prev_txn_type,
853 p_folder_id => p_transaction_id,
854 p_transaction_id => l_prev_txn_id,
855 p_entity_id => l_prev_entity_id);
856 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => 'T', --modified by kgowripe l_prev_txn_type,
857 p_folder_id => p_transaction_id,
858 p_transaction_id => l_prev_txn_id);
859 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => 'F', --modified by kgowripe l_prev_txn_type,
860 p_folder_id => p_transaction_id);
861 p_status_flag := l_final_stat;
862 hr_utility.set_location('leaving '||l_proc,200);
863 exception
864 when others then
865 hr_utility.set_location('some error '||l_proc,420);
866 raise;
867 end apply_defined_rules;
868
869 -- business rules related to Reallocation are applied , if error status is returned as false
870 PROCEDURE apply_business_rules(p_transaction_id IN number,
871 p_business_group_id IN number,
872 p_effective_date IN DATE,
873 p_status_flag OUT NOCOPY BOOLEAN) is
874 l_proc varchar2(71) := g_package||'apply_business_rules';
875 l_rule_message fnd_new_messages.message_text%type;
876 l_num_txns number;
877
878 CURSOR csr_txn_rec(p_folder_id in number) is
879 select pool_id txn_id,name
880 from pqh_budget_pools
881 where parent_pool_id = p_folder_id;
882
883 CURSOR csr_donor_rec(p_txn_id in number) is
884 select reallocation_id donor_id,budget_detail_id,entity_id
885 from pqh_bdgt_pool_realloctions
886 where pool_id = p_txn_id
887 and transaction_type ='D' ;
888
889 CURSOR csr_rcvr_rec(p_txn_id in number) is
890 select reallocation_id rcvr_id,entity_id
891 from pqh_bdgt_pool_realloctions
892 where pool_id = p_txn_id
893 and transaction_type ='R' ;
894
895 CURSOR csr_donorperiod_rec(p_donor_id in number) is
896 select reallocation_amt,reserved_amt,budget_period_id
897 from pqh_bdgt_pool_realloctions
898 where txn_detail_id = p_donor_id
899 and pool_id is null
900 and transaction_type ='DD' ;
901
902 CURSOR csr_rcvrperiod_rec(p_rcvr_id in number) is
903 select reallocation_amt,entity_id,start_date,end_date,reallocation_id rcvr_period_id
904 from pqh_bdgt_pool_realloctions
905 where txn_detail_id = p_rcvr_id
906 and pool_id is null
907 and transaction_type ='RD' ;
908
909 l_num_donors number;
910 l_num_rcvrs number;
911 l_donor_realloc_amt number;
912 l_donor_reserve_amt number;
913 l_rcvr_realloc_amt number;
914 l_lo_don_prd_start_date date;
915 l_prd_start_date date;
916 l_prd_avl_amt number;
917 l_prd_act_amt number;
918 l_prd_com_amt number;
919 l_prd_don_amt number;
920 l_prd_res_amt number;
921 l_prd_rec_amt number;
922 l_prd_bgt_amt number;
923 begin
924 hr_utility.set_location('inside'||l_proc,10);
925 p_status_flag := TRUE;
926 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => 'F',
927 p_folder_id => p_transaction_id);
928 hr_utility.set_location('plg flder started'||l_proc,20);
929 select count(*) into l_num_txns
930 from pqh_budget_pools
931 where parent_pool_id = p_transaction_id;
932 if nvl(l_num_txns,0) = 0 then
933 hr_utility.set_location('no txn '||l_proc,25);
934 p_status_flag := FALSE;
935 /*
936 This code is commented as Process log does not allow recording of log at folder level
937 The same should be captured in review page.
938 The error condition is being handled here, just in case.
939 */
940 l_rule_message := build_message (p_application_id => 8302,
941 p_message_cd => 'PQH_BGT_REALLOC_NO_TXN');
942 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
943 (p_folder_id => p_transaction_id,
944 p_transaction_id => p_transaction_id,
945 p_txn_entity_type => 'F',
946 p_rule_level => 'E',
947 p_rule_msg_cd => l_rule_message);
948
949 end if;
950 if p_status_flag then
951 for l_txn_rec in csr_txn_rec(p_folder_id => p_transaction_id) loop
952 hr_utility.set_location('txn is '||l_txn_rec.name,30);
953 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => 'T',
954 p_folder_id => p_transaction_id,
955 p_transaction_id => l_txn_rec.txn_id);
956 hr_utility.set_location('plg txn started'||l_proc,40);
957 -- Many-many rule checked here
958 l_num_donors := get_txn_child_count(p_child_type => 'D',
959 p_txn_id => l_txn_rec.txn_id);
960 l_num_rcvrs := get_txn_child_count(p_child_type => 'R',
961 p_txn_id => l_txn_rec.txn_id);
962 hr_utility.set_location('donors'||l_num_donors,50);
963 hr_utility.set_location('rcvrs'||l_num_rcvrs,60);
964 if l_num_donors > 1 then
965 -- multiple donors exist
966 hr_utility.set_Location('Many donors exist in txn',50);
967 if l_num_rcvrs > 1 then
968 -- error condition , many to many
969 hr_utility.set_Location('Many rcvrs exist in txn',60);
970 p_status_flag := FALSE;
971 l_rule_message := build_message (p_application_id => 8302,
972 p_message_cd => 'PQH_BGT_REALLOC_MANY_MANY');
973 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
974 (p_folder_id => p_transaction_id,
975 p_transaction_id => l_txn_rec.txn_id,
976 p_txn_entity_type => 'T',
977 p_rule_level => 'E',
978 p_rule_msg_cd => l_rule_message);
979 elsif l_num_rcvrs = 0 then
980
981 -- Stared by mvankada to fix Bug : 2897642
982 -- error Donor has only reserved amount
983 p_status_flag := FALSE;
984 -- Reallocation amount, reserved amount
985 hr_utility.set_Location('get txn bal'||l_proc,61);
986 get_txn_balances(p_txn_id => l_txn_rec.txn_id,
987 p_donor_realloc_amt => l_donor_realloc_amt,
988 p_donor_reserve_amt => l_donor_reserve_amt,
989 p_rcvr_realloc_amt => l_rcvr_realloc_amt);
990 hr_utility.set_Location('l_donor_realloc_amt .. '||l_donor_realloc_amt,62);
991 hr_utility.set_Location('l_donor_reserve_amt .. '||l_donor_reserve_amt,63);
992
993 if (nvl(l_donor_realloc_amt,0) = 0 and nvl(l_donor_reserve_amt,0) <> 0) then
994 -- Donor has only reserved amount
995 hr_utility.set_Location('donor has reserved amount only ',65);
996 p_status_flag := FALSE;
997 l_rule_message := build_message (p_application_id => 8302,
998 p_message_cd => 'PQH_BGT_REALLOC_RESERVED_AMT');
999 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1000 (p_folder_id => p_transaction_id,
1001 p_transaction_id => l_txn_rec.txn_id,
1002 p_txn_entity_type => 'T',
1003 p_rule_level => 'E',
1004 p_rule_msg_cd => l_rule_message);
1005 else
1006 -- Ended by mvankada to fix Bug : 2897642
1007 -- error condition , No receiver
1008 hr_utility.set_Location('No rcvrs exist in txn',30);
1009 l_rule_message := build_message (p_application_id => 8302,
1010 p_message_cd => 'PQH_BGT_REALLOC_NO_RCVR');
1011 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1012 (p_folder_id => p_transaction_id,
1013 p_transaction_id => l_txn_rec.txn_id,
1014 p_txn_entity_type => 'T',
1015 p_rule_level => 'E',
1016 p_rule_msg_cd => l_rule_message);
1017 end if;
1018 end if;
1019 elsif l_num_donors = 0 then
1020 -- error condition, no donor
1021 hr_utility.set_Location('No donor exist in txn',40);
1022 p_status_flag := FALSE;
1023 l_rule_message := build_message (p_application_id => 8302,
1024 p_message_cd => 'PQH_BGT_REALLOC_NO_DONOR');
1025 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1026 (p_folder_id => p_transaction_id,
1027 p_transaction_id => l_txn_rec.txn_id,
1028 p_txn_entity_type => 'T',
1029 p_rule_level => 'E',
1030 p_rule_msg_cd => l_rule_message);
1031 else -- # of donors is 1 then
1032 if l_num_rcvrs = 0 then
1033
1034 -- Stared by mvankada to fix Bug : 2897642
1035 -- error Donor has only reserved amount
1036 p_status_flag := FALSE;
1037 -- Reallocation amount, reserved amount
1038 hr_utility.set_Location('get txn bal'||l_proc,45);
1039 get_txn_balances(p_txn_id => l_txn_rec.txn_id,
1040 p_donor_realloc_amt => l_donor_realloc_amt,
1041 p_donor_reserve_amt => l_donor_reserve_amt,
1042 p_rcvr_realloc_amt => l_rcvr_realloc_amt);
1043 hr_utility.set_Location('l_donor_realloc_amt .. '||l_donor_realloc_amt,47);
1044 hr_utility.set_Location('l_donor_reserve_amt .. '||l_donor_reserve_amt,50);
1045
1046 if (nvl(l_donor_realloc_amt,0) = 0 and nvl(l_donor_reserve_amt,0) <> 0) then
1047 -- Donor has only reserved amount
1048 hr_utility.set_Location('donor has reserved amount only ',55);
1049 p_status_flag := FALSE;
1050 l_rule_message := build_message (p_application_id => 8302,
1051 p_message_cd => 'PQH_BGT_REALLOC_RESERVED_AMT');
1052 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1053 (p_folder_id => p_transaction_id,
1054 p_transaction_id => l_txn_rec.txn_id,
1055 p_txn_entity_type => 'T',
1056 p_rule_level => 'E',
1057 p_rule_msg_cd => l_rule_message);
1058 else
1059
1060 -- Ended by mvankada to fix Bug : 2897642
1061 -- error condition , No receiver
1062 hr_utility.set_Location('No rcvrs exist in txn',30);
1063 l_rule_message := build_message (p_application_id => 8302,
1064 p_message_cd => 'PQH_BGT_REALLOC_NO_RCVR');
1065 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1066 (p_folder_id => p_transaction_id,
1067 p_transaction_id => l_txn_rec.txn_id,
1068 p_txn_entity_type => 'T',
1069 p_rule_level => 'E',
1070 p_rule_msg_cd => l_rule_message);
1071 end if;
1072 end if;
1073 end if;
1074 if p_status_flag then
1075 -- txn_balance should be 0
1076 hr_utility.set_Location('get txn bal'||l_proc,50);
1077 get_txn_balances(p_txn_id => l_txn_rec.txn_id,
1078 p_donor_realloc_amt => l_donor_realloc_amt,
1079 p_donor_reserve_amt => l_donor_reserve_amt,
1080 p_rcvr_realloc_amt => l_rcvr_realloc_amt);
1081 if l_donor_realloc_amt is null or l_rcvr_realloc_amt is null then
1082 -- txn is not balanced, write in process log with txn name
1083 hr_utility.set_Location('donor or rcvr null ',52);
1084 p_status_flag := FALSE;
1085 l_rule_message := build_message (p_application_id => 8302,
1086 p_message_cd => 'PQH_BGT_REALLOC_UNBAL_TXN');
1087 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1088 (p_folder_id => p_transaction_id,
1089 p_transaction_id => l_txn_rec.txn_id,
1090 p_txn_entity_type => 'T',
1091 p_rule_level => 'E',
1092 p_rule_msg_cd => l_rule_message);
1093 elsif nvl(l_donor_realloc_amt,0) <> nvl(l_rcvr_realloc_amt,0) then
1094 -- txn is not balanced, write in process log with txn name
1095 hr_utility.set_Location('txn not balanced',50);
1096 p_status_flag := FALSE;
1097 l_rule_message := build_message (p_application_id => 8302,
1098 p_message_cd => 'PQH_BGT_REALLOC_UNBAL_TXN');
1099 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1100 (p_folder_id => p_transaction_id,
1101 p_transaction_id => l_txn_rec.txn_id,
1102 p_txn_entity_type => 'T',
1103 p_rule_level => 'E',
1104 p_rule_msg_cd => l_rule_message);
1105 end if;
1106 if p_status_flag then
1107 hr_utility.set_Location('validate donors '||l_proc,60);
1108 for l_donor_rec in csr_donor_rec(p_txn_id => l_txn_rec.txn_id) loop
1109 hr_utility.set_Location('donor log started '||l_proc,70);
1110 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => 'D',
1111 p_folder_id => p_transaction_id,
1112 p_transaction_id => l_txn_rec.txn_id,
1113 p_bdgt_entity_type => g_budget_entity,
1114 p_entity_id => l_donor_rec.entity_id); --modified by kgowripe l_donor_rec.donor_id);
1115 hr_utility.set_Location('validate donor periods '||l_proc,80);
1116 for l_donorperiod_rec in csr_donorperiod_rec(p_donor_id => l_donor_rec.donor_id) loop
1117 hr_utility.set_Location('donor period id '||l_donor_rec.donor_id,90);
1118 if nvl(l_donorperiod_rec.reallocation_amt,0) < 0 and p_status_flag then
1119 -- error condition negative amount
1120 hr_utility.set_Location('donorperiod realloc negative',60);
1121 p_status_flag := FALSE;
1122 l_rule_message := build_message (p_application_id => 8302,
1123 p_message_cd => 'PQH_BGT_REALLOC_DONOR_REA_NEG');
1124 hr_utility.set_Location('log rule '||l_proc,100);
1125 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1126 (p_folder_id => p_transaction_id,
1127 p_transaction_id => l_txn_rec.txn_id,
1128 p_txn_entity_type => 'DP',--modified by kgowripe 'D',
1129 p_entity_id => l_donor_rec.entity_id, --modified by kgowripe l_donor_rec.donor_id,
1130 p_budget_period_id => l_donorperiod_rec.budget_period_id,
1131 p_rule_level => 'E',
1132 p_rule_msg_cd => l_rule_message);
1133 end if;
1134 if nvl(l_donorperiod_rec.reserved_amt,0) < 0 and p_status_flag then
1135 -- error condition negative amount
1136 hr_utility.set_Location('donorperiod reserve negative',70);
1137 p_status_flag := FALSE;
1138 l_rule_message := build_message (p_application_id => 8302,
1139 p_message_cd => 'PQH_BGT_REALLOC_DONOR_RES_NEG');
1140 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1141 (p_folder_id => p_transaction_id,
1142 p_transaction_id => l_txn_rec.txn_id,
1143 p_txn_entity_type => 'DP',--modified by kgowripe 'D',
1144 p_entity_id => l_donor_rec.entity_id,--modified by kgowripe l_donor_rec.donor_id,
1145 p_budget_period_id => l_donorperiod_rec.budget_period_id,
1146 p_rule_level => 'E',
1147 p_rule_msg_cd => l_rule_message);
1148 end if;
1149 -- details are being fetched here so that same can be passed to the process log
1150 hr_utility.set_Location('get the period details '||l_proc,120);
1151 get_period_details (p_budget_period_id => l_donorperiod_rec.budget_period_id,
1152 p_budget_entity_id => l_donor_rec.entity_id, --modified by kgowripe l_donor_rec.donor_id,
1153 p_prd_avl_amt => l_prd_avl_amt,
1154 p_prd_act_amt => l_prd_act_amt,
1155 p_prd_com_amt => l_prd_com_amt,
1156 p_prd_don_amt => l_prd_don_amt,
1157 p_prd_res_amt => l_prd_res_amt,
1158 p_prd_bgt_amt => l_prd_bgt_amt,
1159 p_prd_start_date => l_prd_start_date);
1160 if (nvl(l_prd_avl_amt,0) < (nvl(l_donorperiod_rec.reallocation_amt,0) +
1161 nvl(l_donorperiod_rec.reserved_amt,0))) and p_status_flag then
1162 -- error condition, amount available is less than used
1163 hr_utility.set_Location('donorperiod available is less',80);
1164 p_status_flag := FALSE;
1165 l_rule_message := build_message (p_application_id => 8302,
1166 p_message_cd => 'PQH_BGT_REALLOC_DONOR_LESS_AVL');
1167 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1168 (p_folder_id => p_transaction_id,
1169 p_transaction_id => l_txn_rec.txn_id,
1170 p_txn_entity_type => 'DP',--Modified by kgowripe 'D',
1171 p_entity_id => l_donor_rec.entity_id,--modified by kgowripe l_donor_rec.donor_id,
1172 p_budget_period_id => l_donorperiod_rec.budget_period_id,
1173 p_rule_level => 'E',
1174 p_rule_msg_cd => l_rule_message);
1175 end if;
1176 if l_lo_don_prd_start_date > l_prd_start_date then
1177 -- earlist donor period start date is being captured here
1178 l_lo_don_prd_start_date := l_prd_start_date;
1179 hr_utility.set_Location('earliest donorperiod is '||to_char(l_prd_start_date,'ddmmRRRR'),90);
1180 end if;
1181 if not p_status_flag then
1182 exit;
1183 end if;
1184 end loop; -- donor periods inside the donor loop
1185 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => 'D',
1186 p_folder_id => p_transaction_id,
1187 p_transaction_id => l_txn_rec.txn_id,
1188 p_entity_id => l_donor_rec.entity_id);--modified by kgowripe l_donor_rec.donor_id);
1189 if not p_status_flag then
1190 exit;
1191 end if;
1192 end loop; -- donors inside the txn loop
1193 if p_status_flag then
1194 for l_rcvr_rec in csr_rcvr_rec(p_txn_id => l_txn_rec.txn_id) loop
1195 pqh_bdgt_realloc_log_pkg.start_log(p_txn_entity_type => 'R',
1196 p_folder_id => p_transaction_id,
1197 p_transaction_id => l_txn_rec.txn_id,
1198 p_bdgt_entity_type => g_budget_entity,
1199 p_entity_id => l_rcvr_rec.entity_id);--modified by kgowripe l_rcvr_rec.rcvr_id);
1200 for l_rcvrperiod_rec in csr_rcvrperiod_rec(p_rcvr_id => l_rcvr_rec.rcvr_id) loop
1201 if l_rcvrperiod_rec.start_date < g_budget_start_date and p_status_flag then
1202 -- error condition, receiver starting prior to budget date
1203 hr_utility.set_Location('rcvrperiod starting <budget',100);
1204 p_status_flag := FALSE;
1205 l_rule_message := build_message (p_application_id => 8302,
1206 p_message_cd => 'PQH_BGT_REALLOC_RCVR_START_BGT');
1207 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1208 (p_folder_id => p_transaction_id,
1209 p_transaction_id => l_txn_rec.txn_id,
1210 p_txn_entity_type => 'RP',
1211 p_entity_id => l_rcvr_rec.entity_id,--modified by kgowripe l_rcvr_rec.rcvr_id,
1212 p_budget_period_id => l_rcvrperiod_rec.rcvr_period_id,--added by kgowriope
1213 p_rule_level => 'E',
1214 p_rule_msg_cd => l_rule_message);
1215 end if;
1216 if l_rcvrperiod_rec.end_date > g_budget_end_date and p_status_flag then
1217 -- error condition, receiver ending later than budget end date.
1218 hr_utility.set_Location('rcvrperiod ending >budget',110);
1219 p_status_flag := FALSE;
1220 l_rule_message := build_message (p_application_id => 8302,
1221 p_message_cd => 'PQH_BGT_REALLOC_RCVR_END_BGT');
1222 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1223 (p_folder_id => p_transaction_id,
1224 p_transaction_id => l_txn_rec.txn_id,
1225 p_txn_entity_type => 'RP',
1226 p_entity_id => l_rcvr_rec.entity_id,--modified by kgowripe l_rcvr_rec.rcvr_id,
1227 p_budget_period_id => l_rcvrperiod_rec.rcvr_period_id,--added by kgowriope
1228 p_rule_level => 'E',
1229 p_rule_msg_cd => l_rule_message);
1230 end if;
1231 if l_rcvrperiod_rec.start_date < l_lo_don_prd_start_date and p_status_flag then
1232 -- error condition, receiver starting prior to earliest donor period date
1233 hr_utility.set_Location('rcvrperiod starting <donor',110);
1234 p_status_flag := FALSE;
1235 l_rule_message := build_message (p_application_id => 8302,
1236 p_message_cd => 'PQH_BGT_REALLOC_RCVR_START_DON');
1237 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1238 (p_folder_id => p_transaction_id,
1239 p_transaction_id => l_txn_rec.txn_id,
1240 p_txn_entity_type => 'RP',
1241 p_entity_id => l_rcvr_rec.entity_id,--modified by kgowripe l_rcvr_rec.rcvr_id,
1242 p_budget_period_id => l_rcvrperiod_rec.rcvr_period_id,--added by kgowriope
1243 p_rule_level => 'E',
1244 p_rule_msg_cd => l_rule_message);
1245 end if;
1246 if nvl(l_rcvrperiod_rec.reallocation_amt,0) < 0 and p_status_flag then
1247 -- error condition negative amount
1248 hr_utility.set_Location('rcvrperiod realloc negative',120);
1249 p_status_flag := FALSE;
1250 l_rule_message := build_message (p_application_id => 8302,
1251 p_message_cd => 'PQH_BGT_REALLOC_RCVR_REA_NEG');
1252 pqh_bdgt_realloc_log_pkg.log_rule_for_entity
1253 (p_folder_id => p_transaction_id,
1254 p_transaction_id => l_txn_rec.txn_id,
1255 p_txn_entity_type => 'RP',
1256 p_entity_id => l_rcvr_rec.entity_id,--modified by kgowripe l_rcvr_rec.rcvr_id,
1257 p_budget_period_id => l_rcvrperiod_rec.rcvr_period_id,--added by kgowriope
1258 p_rule_level => 'E',
1259 p_rule_msg_cd => l_rule_message);
1260 end if;
1261 if not p_status_flag then
1262 exit;
1263 end if;
1264 end loop; -- rcvr periods inside the rcvr loop
1265 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => 'R',
1266 p_folder_id => p_transaction_id,
1267 p_transaction_id => l_txn_rec.txn_id,
1268 p_entity_id => l_rcvr_rec.entity_id);
1269 if not p_status_flag then
1270 exit;
1271 end if;
1272 end loop; -- rcvrs inside the txn loop
1273 else
1274 hr_utility.set_location('not going for rcvr, as error exist',140);
1275 end if;
1276 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => 'T',
1277 p_folder_id => p_transaction_id,
1278 p_transaction_id => l_txn_rec.txn_id);
1279 end if; -- endif, if txn had balance errors
1280 end if; -- endif, if txn had num errors
1281 if not p_status_flag then
1282 exit;
1283 end if;
1284 end loop; -- transactions inside the folder loop
1285 end if; -- if folder had txns
1286 pqh_bdgt_realloc_log_pkg.end_log(p_txn_entity_type => 'F',
1287 p_folder_id => p_transaction_id);
1288 hr_utility.set_location('done business rules',150);
1289 end apply_business_rules;
1290
1291 -- populates valid rule conditions for an entity
1292 -- Set of attributes are defined and mapping is stored in p_attr_matx that which attribute is
1293 -- applicable for which one
1294 procedure valid_rule_conditions(p_entity_type in varchar2,
1295 p_rule_set_id in number,
1296 p_rule_applicability in varchar2,
1297 p_attr_matx in t_attr_matx,
1298 p_cond_matx out NOCOPY t_cond_matx) is
1299 l_proc varchar2(71) := g_package||'valid_rule_conditions';
1300 cursor csr_condition_rec is
1301 select rule_attribute_id,attribute_value,operation_code,attribute_code
1302 from pqh_rule_attributes
1303 where rule_set_id = p_rule_set_id;
1304 read_counter number := 1;
1305 write_counter number := 1;
1306 l_valid_applicability boolean;
1307 l_valid_entity boolean;
1308 begin
1309 hr_utility.set_location('inside'||l_proc,10);
1310 -- pqh_rule_attributes stores the attribute code (which links to attribute_id of pqh_attributes)
1311 -- this attribute id is linked to txn_category budget reallocation
1312 -- In this procedure we are getting conditions defined for a rule and populating
1313 -- the seeded attributes by comparing attribute_id
1314 for l_condition_rec in csr_condition_rec loop
1315 hr_utility.set_location('next condition'||l_condition_rec.attribute_code,20);
1316 for read_counter in 1..p_attr_matx.count loop
1317 hr_utility.set_location('next cond attribute'||l_condition_rec.attribute_code,25);
1318 hr_utility.set_location('next attribute'||p_attr_matx(read_counter).attribute_id,30);
1319 if l_condition_rec.attribute_code = p_attr_matx(read_counter).attribute_id then
1320 hr_utility.set_location('attribute match',30);
1321 hr_utility.set_location('rule_applicability :'||p_rule_applicability,32);
1322 hr_utility.set_location('entity_type :'||p_entity_type,34);
1323 if p_rule_applicability = 'DONOR' and
1324 p_attr_matx(read_counter).applicability in ('DONOR','BOTH') then
1325 hr_utility.set_location('applicability valid',40);
1326 l_valid_applicability := TRUE;
1327 elsif p_rule_applicability = 'RECEIVER' and
1328 p_attr_matx(read_counter).applicability in ('RECEIVER','BOTH') then
1329 hr_utility.set_location('applicability valid',50);
1330 l_valid_applicability := TRUE;
1331 else
1332 l_valid_applicability := FALSE;
1333 hr_utility.set_location('applicability invalid'||p_rule_applicability,70);
1334 end if;
1335 if p_entity_type = 'POSITION' and
1336 p_attr_matx(read_counter).entity_type in ('POSITION','ALL') then
1337 hr_utility.set_location('entity valid',50);
1338 l_valid_entity := TRUE;
1339 elsif p_entity_type = 'ORGANIZATION' and
1340 p_attr_matx(read_counter).entity_type in ('ORGANIZATION','ALL') then
1341 hr_utility.set_location('entity valid',50);
1342 l_valid_entity := TRUE;
1343 elsif p_entity_type = 'JOB' and
1344 p_attr_matx(read_counter).entity_type in ('JOB','ALL') then
1345 hr_utility.set_location('entity valid',50);
1346 l_valid_entity := TRUE;
1347 elsif p_entity_type = 'GRADE' and
1348 p_attr_matx(read_counter).entity_type in ('GRADE','ALL') then
1349 hr_utility.set_location('entity valid',50);
1350 l_valid_entity := TRUE;
1351 else
1352 l_valid_entity := FALSE;
1353 hr_utility.set_location('entity invalid '||p_entity_type,70);
1354 end if;
1355 if l_valid_applicability and l_valid_entity then
1356 hr_utility.set_location('writing condition',80);
1357 -- attribute matches the entity type and applicability and
1358 -- is a valid rule to be applied.
1359 p_cond_matx(write_counter).rule_attribute_id := l_condition_rec.rule_attribute_id;
1360 p_cond_matx(write_counter).attribute_id := p_attr_matx(read_counter).attribute_id;
1361 p_cond_matx(write_counter).column_type := p_attr_matx(read_counter).column_type;
1362 p_cond_matx(write_counter).column_name := p_attr_matx(read_counter).column_name;
1363 p_cond_matx(write_counter).operation_code := l_condition_rec.operation_code;
1364 p_cond_matx(write_counter).attribute_value := l_condition_rec.attribute_value;
1365 write_counter := write_counter + 1;
1366 end if; -- populating condtion matrix with the match
1367 exit; -- as match found, get next condition attribute
1368 else
1369 hr_utility.set_location('no match, go for next ',88);
1370 end if; -- attribute compare if end
1371 end loop; -- attribute property matrix loop
1372 hr_utility.set_location('out of attribute loop',90);
1373 end loop; -- conditions loop
1374 hr_utility.set_location('out of conditions loop',95);
1375 /*
1376 -- used for debugging only
1377 for i in 1..p_cond_matx.count loop
1378 hr_utility.set_location('cond# is'||i,100);
1379 hr_utility.set_location('cond_attr'||p_cond_matx(i).attribute_id,101);
1380 hr_utility.set_location('column_name'||substr(p_cond_matx(i).column_name,1,50),102);
1381 hr_utility.set_location('attribute_value'||p_cond_matx(i).attribute_value,103);
1382 end loop;
1383 */
1384 hr_utility.set_location('leaving '||l_proc,110);
1385 exception
1386 when others then
1387 hr_utility.set_location('error in valid_rule_conditions',420);
1388 end valid_rule_conditions;
1389
1390 -- populates valid rules for a transaction folder
1391 procedure valid_process_rules(p_transaction_id in varchar2,
1392 p_business_group_id in number,
1393 p_rule_category in varchar2,
1394 p_effective_date in date,
1395 l_rule_matx out NOCOPY t_rule_matx) is
1396 l_proc varchar2(71) := g_package||'valid_process_rules';
1397
1398 CURSOR csr_rule_rec(p_rule_applicability in varchar2) is
1399 select rule_set_id,organization_structure_id,starting_organization_id,organization_id,
1400 rule_set_name,rule_level_cd,rule_applicability
1401 from pqh_rule_sets
1402 where business_group_id = p_business_group_id
1403 and rule_category = p_rule_category
1404 and rule_applicability = p_rule_applicability
1405 and rule_level_cd <> 'I' ;
1406
1407 CURSOR csr_entity_rec IS
1408 SELECT txndtl.budget_detail_id budget_detail_id,
1409 txndtl.entity_id entity_id,
1410 txndtl.transaction_type txn_type,
1411 txndtl.pool_id txn_id
1412 FROM pqh_budget_pools fld,
1413 pqh_budget_pools txn,
1414 pqh_bdgt_pool_realloctions txndtl
1415 WHERE fld.pool_id = p_transaction_id
1416 AND fld.parent_pool_id IS NULL
1417 AND fld.pool_id = txn.parent_pool_id
1418 AND txn.pool_id = txndtl.pool_id;
1419 counter number := 1;
1420 l_rule_added boolean;
1421 l_rule_scope varchar2(30);
1422 l_rule_valid boolean;
1423 l_org_str_version_id number;
1424 l_pos_organization_id number ;
1425 l_organization_id number ;
1426 l_rule_conditions number;
1427 l_rule_messages number;
1428 l_rule_set_id number;
1429 l_message_cd fnd_new_messages.message_name%type;
1430 l_rule_applicability varchar2(30);
1431 begin
1432 -- for a donor entity all the donor rules defined in the business group are pulled in
1433 -- and checked for validity and added the plsql table
1434 hr_utility.set_Location('inside'||l_proc,10);
1435 for l_entity_rec in csr_entity_rec loop
1436 hr_utility.set_Location('entity is'||l_entity_rec.entity_id,20);
1437 if l_entity_rec.txn_type ='R' then
1438 l_rule_applicability := 'RECEIVER';
1439 elsif l_entity_rec.txn_type ='D' then
1440 l_rule_applicability := 'DONOR';
1441 else
1442 hr_utility.set_location('Error '||l_entity_rec.txn_type,10);
1443 end if;
1444 if g_budget_entity in ('POSITION','ORGANIZATION') then
1445 if g_budget_entity = 'POSITION' then
1446 l_pos_organization_id := get_position_organization(p_position_id => l_entity_rec.entity_id,
1447 p_effective_date => p_effective_date);
1448 end if;
1449 l_organization_id := nvl(l_pos_organization_id,l_entity_rec.entity_id);
1450 end if;
1451 for l_rule_rec in csr_rule_rec(p_rule_applicability => l_rule_applicability) loop
1452 hr_utility.set_location('finding rule '||l_rule_applicability,30);
1453 l_rule_valid := FALSE ; -- initializing the result variable
1454 select count(*) into l_rule_messages from pqh_rules where rule_set_id = l_rule_rec.rule_set_id;
1455 select count(*) into l_rule_conditions from pqh_rule_attributes where rule_set_id = l_rule_rec.rule_set_id;
1456 if l_rule_messages <> 1 or l_rule_conditions = 0 then
1457 hr_utility.set_location('Either message or condition is undefined',50);
1458 l_rule_valid := FALSE ;
1459 else
1460 l_rule_valid := TRUE ;
1461 select message_name
1462 into l_message_cd
1463 from pqh_rules
1464 where rule_set_id = l_rule_rec.rule_set_id
1465 and application_id = 8302;
1466 hr_utility.set_location('message is'||l_message_cd,55);
1467 end if;
1468 if l_rule_valid then
1469 hr_utility.set_location('valid rule ',60);
1470 if l_rule_rec.organization_structure_id is null and l_rule_rec.organization_id is null then
1471 -- rule is defined for BG level
1472 l_rule_valid := TRUE;
1473 else
1474 if g_budget_entity in ('POSITION','ORGANIZATION') then
1475 l_rule_valid := check_org_valid_rule
1476 (p_organization_id => l_organization_id,
1477 p_rule_org_id => l_rule_rec.organization_id,
1478 p_rule_applicability => l_rule_rec.rule_applicability,
1479 p_rule_category => p_rule_category,
1480 p_rule_org_str_id => l_rule_rec.organization_structure_id,
1481 p_rule_start_org_id => l_rule_rec.starting_organization_id);
1482 end if;
1483 end if;
1484 end if;
1485 if l_rule_valid then
1486 l_rule_matx(counter).rule_set_id := l_rule_rec.rule_set_id;
1487 l_rule_matx(counter).rule_name := l_rule_rec.rule_set_name;
1488 l_rule_matx(counter).rule_applicability := l_rule_rec.rule_applicability;
1489 l_rule_matx(counter).entity_id := l_entity_rec.entity_id;
1490 l_rule_matx(counter).txn_id := l_entity_rec.txn_id;
1491 l_rule_matx(counter).message_cd := l_message_cd;
1492 --rule level code is not being added to the matrix. added by kgowripe
1493 l_rule_matx(counter).rule_level_cd := l_rule_rec.rule_level_cd;
1494 --
1495 counter := counter+1;
1496 else
1497 hr_utility.set_location('invalid rule '||l_rule_rec.rule_set_name,75);
1498 end if;
1499 end loop; -- all the rules defined for BG loop
1500 end loop; -- entities loop
1501 for i in 1..l_rule_matx.count loop
1502 hr_utility.set_location('rule# is'||i,100);
1503 hr_utility.set_location('rule_name'||l_rule_matx(i).rule_name,101);
1504 hr_utility.set_location('entity_id'||l_rule_matx(i).entity_id,102);
1505 hr_utility.set_location('message_cd'||l_rule_matx(i).message_cd,103);
1506 hr_utility.set_location('applicability'||l_rule_matx(i).rule_applicability,104);
1507 end loop;
1508 exception
1509 when others then
1510 hr_utility.set_location('unexpected error',420);
1511 raise;
1512 end valid_process_rules;
1513
1514 procedure populate_globals(p_transaction_id in number) is
1515 l_proc varchar2(71) := g_package||'.populate_globals';
1516 l_budget_unit_id number;
1517 begin
1518 hr_utility.set_location('inside '||l_proc,10);
1519 g_folder_id := p_transaction_id;
1520 SELECT budget_version_id,budget_unit_id,name,business_group_id
1521 into g_budget_version_id,l_budget_unit_id,g_folder_name,g_business_group_id
1522 FROM pqh_budget_pools
1523 WHERE pool_id = p_transaction_id
1524 and parent_pool_id is null;
1525 hr_utility.set_location('folder name '||g_folder_name,20);
1526 get_budget_details(p_budget_version_id => g_budget_version_id,
1527 p_budget_unit_id => l_budget_unit_id,
1528 p_budget_name => g_budget_name,
1529 p_budget_currency => g_budget_currency,
1530 p_entity_type => g_budget_entity,
1531 p_budget_start_date => g_budget_start_date,
1532 p_budget_end_date => g_budget_end_date,
1533 p_budget_unit_name => g_budget_unit_name,
1534 p_budget_unit_num => g_budget_unit_num,
1535 p_bmu_name => g_measurement_unit,
1536 p_budget_unit_type => g_budget_unit_type);
1537 -- modified by kgowripe.set the session date as budget start date
1538 dt_fndate.set_effective_date(p_effective_date => g_budget_start_date);
1539 --
1540 hr_utility.set_location('budget name '||g_budget_name,30);
1541 hr_utility.set_location('entity type'||g_budget_entity,40);
1542 hr_utility.set_location('unit name '||g_budget_unit_name,50);
1543 end;
1544
1545 procedure populate_attr_matx (p_attr_matx out nocopy t_attr_matx)is
1546 l_proc varchar2(71) := g_package||'populate_attr_matx';
1547 cursor csr_attribute_rec is
1548 select tcat.txn_category_attribute_id tcat_attribute_id,
1549 att.attribute_id attribute_id,
1550 att.column_type column_type,
1551 att.column_name column_name
1552 from pqh_attributes att,pqh_txn_category_attributes tcat, pqh_transaction_categories txn
1553 where txn.transaction_category_id = tcat.transaction_category_id
1554 and txn.short_name = 'PQH_BPR'
1555 and txn.business_group_id is null
1556 and tcat.attribute_id = att.attribute_id
1557 and nvl(tcat.list_identifying_flag,'N') = 'N'
1558 and nvl(tcat.member_identifying_flag,'N') = 'N'
1559 and att.enable_flag ='Y';
1560 counter number := 1;
1561 l_entity_type varchar2(30);
1562 l_applicability varchar2(30);
1563 procedure set_attr (p_attr_matx in out nocopy t_attr_matx
1564 , p_subscript in number
1565 , p_attribute_id in number
1566 , p_column_type in varchar2
1567 , p_column_name in varchar2
1568 , p_txn_catg_attribute_id in number
1569 , p_entity_type in varchar2
1570 , p_applicability in varchar2)
1571 is
1572 begin
1573 p_attr_matx(p_subscript).attribute_id := p_attribute_id ;
1574 p_attr_matx(p_subscript).column_name := p_column_name ;
1575 p_attr_matx(p_subscript).column_type := p_column_type ;
1576 p_attr_matx(p_subscript).txn_catg_attribute_id := p_txn_catg_attribute_id ;
1577 p_attr_matx(p_subscript).entity_type := p_entity_type;
1578 p_attr_matx(p_subscript).applicability := p_applicability;
1579 end;
1580 begin
1581 -- possible values of entity type are
1582 -- Position
1583 -- Organization
1584 -- All (Any entity type is allowed)
1585
1586 -- Possible values of applicability are
1587 -- Both (used for Donor as well as Receiver)
1588 -- Donor
1589 -- Transaction
1590
1591 for l_attribute_rec in csr_attribute_rec loop
1592 hr_utility.set_location('inside'||l_proc,10);
1593 --debug by kmg
1594 hr_utility.set_location('Column name length '||length(l_attribute_rec.column_name),24);
1595 --
1596 hr_utility.set_location('column_name'||substr(l_attribute_rec.column_name,1,50),20);
1597 hr_utility.set_location('column_name'||substr(l_attribute_rec.column_name,51,50),21);
1598 hr_utility.set_location('column_name'||substr(l_attribute_rec.column_name,101,50),22);
1599 hr_utility.set_location('column_name'||substr(l_attribute_rec.column_name,151,50),23);
1600 if upper(l_attribute_rec.column_name) in ('FTE','MAX_PERSONS','LOCATION_ID','JOB_ID','ORGANIZATION_ID','PERMANENT_TEMPORARY_FLAG','SEASONAL_FLAG','STATUS','POSITION_TYPE') then
1601 l_entity_type := 'POSITION';
1602 l_applicability := 'BOTH';
1603 elsif upper(l_attribute_rec.column_name) in ('HR_GENERAL.DECODE_JOB(JOB_ID)','HR_GENERAL.DECODE_LOCATION(LOCATION_ID)') then
1604 l_entity_type := 'POSITION';
1605 l_applicability := 'BOTH';
1606 elsif upper(l_attribute_rec.column_name) in ('HR_GENERAL.DECODE_LOOKUP(''POSITION_TYPE'',POSITION_TYPE)','HR_GENERAL.DECODE_ORGANIZATION(ORGANIZATION_ID)') then
1607 l_entity_type := 'POSITION';
1608 l_applicability := 'BOTH';
1609 elsif upper(l_attribute_rec.column_name) in ('ORU.LOCATION_ID','ORU.TYPE','ORU.NAME','HR_GENERAL.DECODE_LOCATION(ORU.LOCATION_ID)') then
1610 l_entity_type := 'ORGANIZATION';
1611 l_applicability := 'BOTH';
1612 elsif upper(l_attribute_rec.column_name) like 'PQH_MGMT_RPT_PKG.GET_ENTITY_BUDGET_AMT(FLD.ENTITY_TYPE,TRNXAMT.ENTITY_ID,FLD.BUDGET_VERSION_ID%' then
1613 l_entity_type := 'ALL';
1614 l_applicability := 'BOTH';
1615 elsif upper(l_attribute_rec.column_name) like 'PQH_BDGT_ACTUAL_CMMTMNT_PKG.GET_ENT_ACTUAL_AND_CMMTMNT(FLD.BUDGET_VERSION_ID%' then
1616 l_entity_type := 'ALL';
1617 l_applicability := 'BOTH';
1618 elsif upper(l_attribute_rec.column_name) like 'PQH_BDGT_REALLOC_UTILITY.GET_PRD_REALLOC_RESERVED_AMT(%' then
1619 l_entity_type := 'ALL';
1620 l_applicability := 'BOTH';
1621 end if;
1622 hr_utility.set_location('going for setting'||l_proc,30);
1623 hr_utility.set_location('entity_type '||l_entity_type,40);
1624 hr_utility.set_location('applicability '||l_applicability,50);
1625 set_attr(p_attr_matx => p_attr_matx,
1626 p_subscript => counter,
1627 p_attribute_id => l_attribute_rec.attribute_id,
1628 p_txn_catg_attribute_id => l_attribute_rec.tcat_attribute_id,
1629 p_column_name => l_attribute_rec.column_name,
1630 p_column_type => l_attribute_rec.column_type,
1631 p_entity_type => l_entity_type,
1632 p_applicability => l_applicability);
1633 counter := counter + 1;
1634 l_entity_type := '';
1635 l_applicability := '';
1636 end loop;
1637 /*
1638 -- can be used for debugging , no need for functionality
1639 for i in 1..p_attr_matx.count loop
1640 hr_utility.set_location(i||'column_name '||substr(p_attr_matx(i).column_name,1,50),70);
1641 hr_utility.set_location(i||'entity_type '||p_attr_matx(i).entity_type,80);
1642 hr_utility.set_location(i||'applicability '||p_attr_matx(i).applicability,90);
1643 end loop;
1644 */
1645 end;
1646 end pqh_cbr_engine;