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