[Home] [Help]
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,
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);
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,
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,
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);
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;
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,
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,
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
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,
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,
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
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,
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
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);
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;
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,
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,
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,
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
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,
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
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,
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;
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);
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
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
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
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
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;
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
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,
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
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: --
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: --
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;
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;
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;
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
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
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
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);
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);
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:
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
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:
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
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:
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
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:
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 ;
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);
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);
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);
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:
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
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
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);
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
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);
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;
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;
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
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;
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,
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
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
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);
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;
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;
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
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;
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
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;
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
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;
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
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;
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
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;
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;
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,
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);
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);
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:
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,
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,
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);
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
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,
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);
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
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';
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:
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
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: */
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);
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
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',
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);
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';
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';
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: */
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,
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
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
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,
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);
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;
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.
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,
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,
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,
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);
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
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,
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
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
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;
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,
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,
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;
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
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);
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
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.
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);
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',
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);
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
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;
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
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);
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
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);
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
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,
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
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);
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
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);
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
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,
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);
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
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
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,
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,
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
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;
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);
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',
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
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,
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
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;
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
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
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
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
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
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
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);
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
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
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
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);
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);
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
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);
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;
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);
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);
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);
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);
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);
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);
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);
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
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;
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
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
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);
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;
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: */
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);
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
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;
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,
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';
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';
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,
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
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
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
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
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);
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;
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
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
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);
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
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
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,
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:
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
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';
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);
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);
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
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';
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';
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
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,
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,
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,
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: */
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;
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;