[Home] [Help]
8: from fnd_sessions
9: where session_id = userenv('sessionid');
10: exception
11: when no_data_found then
12: hr_utility.set_location('session date not there, inserting',10);
13: insert into fnd_sessions (session_id,effective_date) values (userenv('sessionid'),trunc(p_effective_date));
14: when others then
15: hr_utility.set_location('issues in session date pulling ',10);
16: raise;
11: when no_data_found then
12: hr_utility.set_location('session date not there, inserting',10);
13: insert into fnd_sessions (session_id,effective_date) values (userenv('sessionid'),trunc(p_effective_date));
14: when others then
15: hr_utility.set_location('issues in session date pulling ',10);
16: raise;
17: end;
18: procedure get_criteria(p_criteria_short_code in varchar2,
19: p_business_group_id in number,
25: where short_code =p_criteria_short_code
26: and business_group_id = p_business_group_id;
27: exception
28: when no_data_found then
29: hr_utility.set_location('no bg specific row exists for short_code'||p_criteria_short_code,25);
30: begin
31: select *
32: into p_crit_rec
33: from ben_eligy_criteria
34: where short_code =p_criteria_short_code
35: and business_group_id is null;
36: exception
37: when no_data_found then
38: hr_utility.set_location('no global row for short_code'||p_criteria_short_code,25);
39: raise;
40: when others then
41: hr_utility.set_location('issues in glb short_code'||p_criteria_short_code,25);
42: raise;
37: when no_data_found then
38: hr_utility.set_location('no global row for short_code'||p_criteria_short_code,25);
39: raise;
40: when others then
41: hr_utility.set_location('issues in glb short_code'||p_criteria_short_code,25);
42: raise;
43: end;
44: when others then
45: hr_utility.set_location('issues in getting criteria for short_code'||p_criteria_short_code,25);
41: hr_utility.set_location('issues in glb short_code'||p_criteria_short_code,25);
42: raise;
43: end;
44: when others then
45: hr_utility.set_location('issues in getting criteria for short_code'||p_criteria_short_code,25);
46: raise;
47: end;
48: function get_rmn_cer(p_rmn_id in number,
49: p_cet_id in number) return number is
54: from ben_copy_entity_results
55: where copy_entity_txn_id = p_cet_id
56: and table_alias ='RMN'
57: and information1 = p_rmn_id;
58: hr_utility.set_location('RMN cer'||l_rmn_cer_id,20);
59: return l_rmn_cer_id;
60: exception
61: when others then
62: hr_utility.set_location('issues in getting RMN cer'||p_rmn_id,10);
58: hr_utility.set_location('RMN cer'||l_rmn_cer_id,20);
59: return l_rmn_cer_id;
60: exception
61: when others then
62: hr_utility.set_location('issues in getting RMN cer'||p_rmn_id,10);
63: raise;
64: end;
65: function get_ph_name(p_pos_hier_ver_id in number) return varchar2 is
66: l_ph_name varchar2(240);
72: and phv.pos_structure_version_id = p_pos_hier_ver_id;
73: return l_ph_name;
74: exception
75: when others then
76: hr_utility.set_location('issues in pulling ph name'||p_pos_hier_ver_id,10);
77: raise;
78: end;
79: function get_oh_name(p_org_hier_ver_id in number) return varchar2 is
80: l_oh_name varchar2(240);
86: and ohv.org_structure_version_id = p_org_hier_ver_id;
87: return l_oh_name;
88: exception
89: when others then
90: hr_utility.set_location('issues in pulling oh name'||p_org_hier_ver_id,10);
91: raise;
92: end;
93: function get_crit_type(p_criteria_short_code in varchar2,
94: p_business_group_id in number) return varchar2 is
125: l_node1_desc varchar2(2000);
126: l_node2_desc varchar2(2000);
127: begin
128: if p_val_type_cd ='LOOKUP' then
129: hr_utility.set_location('based on lkp',10);
130: hr_utility.set_location('lkp_type'||p_lookup_type,40);
131: hr_utility.set_location('lkp_code'||p_char_value1,40);
132: if p_char_value1 is not null then
133: l_node1_desc := hr_general.decode_lookup(p_lookup_type => p_lookup_type,
126: l_node2_desc varchar2(2000);
127: begin
128: if p_val_type_cd ='LOOKUP' then
129: hr_utility.set_location('based on lkp',10);
130: hr_utility.set_location('lkp_type'||p_lookup_type,40);
131: hr_utility.set_location('lkp_code'||p_char_value1,40);
132: if p_char_value1 is not null then
133: l_node1_desc := hr_general.decode_lookup(p_lookup_type => p_lookup_type,
134: p_lookup_code => p_char_value1);
127: begin
128: if p_val_type_cd ='LOOKUP' then
129: hr_utility.set_location('based on lkp',10);
130: hr_utility.set_location('lkp_type'||p_lookup_type,40);
131: hr_utility.set_location('lkp_code'||p_char_value1,40);
132: if p_char_value1 is not null then
133: l_node1_desc := hr_general.decode_lookup(p_lookup_type => p_lookup_type,
134: p_lookup_code => p_char_value1);
135: else
148: else
149: l_node_desc := l_node1_desc ;
150: end if;
151: elsif p_val_type_cd ='ORG_HIER' then
152: hr_utility.set_location('based on oh'||p_number_value1||' : '||p_number_value2,40);
153: l_oh_desc := get_oh_name(p_number_value1);
154: l_org_desc := hr_general.decode_organization(p_organization_id => p_number_value2);
155: l_node_desc := l_oh_desc ||' - '||l_org_desc;
156: elsif p_val_type_cd ='POS_HIER' then
153: l_oh_desc := get_oh_name(p_number_value1);
154: l_org_desc := hr_general.decode_organization(p_organization_id => p_number_value2);
155: l_node_desc := l_oh_desc ||' - '||l_org_desc;
156: elsif p_val_type_cd ='POS_HIER' then
157: hr_utility.set_location('based on ph'||p_number_value1||' : '||p_number_value2,40);
158: l_ph_desc := get_ph_name(p_number_value1);
159: l_pos_desc := hr_general.decode_position(p_position_id => p_number_value2);
160: l_node_desc := l_ph_desc ||' - '||l_pos_desc;
161: elsif p_val_type_cd ='VAL_SET' then
158: l_ph_desc := get_ph_name(p_number_value1);
159: l_pos_desc := hr_general.decode_position(p_position_id => p_number_value2);
160: l_node_desc := l_ph_desc ||' - '||l_pos_desc;
161: elsif p_val_type_cd ='VAL_SET' then
162: hr_utility.set_location('based on vset'||p_value_set_id,40);
163: if p_crit_col_dtyp ='C' then
164: hr_utility.set_location('char based vset',40);
165: l_node1_desc := pqh_utility.get_display_value(p_value => p_char_value1,
166: p_value_set_id => p_value_set_id);
160: l_node_desc := l_ph_desc ||' - '||l_pos_desc;
161: elsif p_val_type_cd ='VAL_SET' then
162: hr_utility.set_location('based on vset'||p_value_set_id,40);
163: if p_crit_col_dtyp ='C' then
164: hr_utility.set_location('char based vset',40);
165: l_node1_desc := pqh_utility.get_display_value(p_value => p_char_value1,
166: p_value_set_id => p_value_set_id);
167: hr_utility.set_location('val1 is '||l_node1_desc,40);
168: if p_range_flag = 'Y' and p_char_value2 is not null then
163: if p_crit_col_dtyp ='C' then
164: hr_utility.set_location('char based vset',40);
165: l_node1_desc := pqh_utility.get_display_value(p_value => p_char_value1,
166: p_value_set_id => p_value_set_id);
167: hr_utility.set_location('val1 is '||l_node1_desc,40);
168: if p_range_flag = 'Y' and p_char_value2 is not null then
169: l_node2_desc := pqh_utility.get_display_value(p_value => p_char_value2,
170: p_value_set_id => p_value_set_id);
171: hr_utility.set_location('val2 is '||l_node2_desc,40);
167: hr_utility.set_location('val1 is '||l_node1_desc,40);
168: if p_range_flag = 'Y' and p_char_value2 is not null then
169: l_node2_desc := pqh_utility.get_display_value(p_value => p_char_value2,
170: p_value_set_id => p_value_set_id);
171: hr_utility.set_location('val2 is '||l_node2_desc,40);
172: l_node_desc := l_node1_desc ||' - '||l_node2_desc;
173: else
174: l_node_desc := l_node1_desc ;
175: end if;
173: else
174: l_node_desc := l_node1_desc ;
175: end if;
176: elsif p_crit_col_dtyp ='N' then
177: hr_utility.set_location('num based vset',40);
178: l_node1_desc := pqh_utility.get_display_value(p_value => to_char(p_number_value1),
179: p_value_set_id => p_value_set_id);
180: hr_utility.set_location('val1 is '||l_node1_desc,40);
181: if p_range_flag = 'Y' and p_number_value2 is not null then
176: elsif p_crit_col_dtyp ='N' then
177: hr_utility.set_location('num based vset',40);
178: l_node1_desc := pqh_utility.get_display_value(p_value => to_char(p_number_value1),
179: p_value_set_id => p_value_set_id);
180: hr_utility.set_location('val1 is '||l_node1_desc,40);
181: if p_range_flag = 'Y' and p_number_value2 is not null then
182: l_node2_desc := pqh_utility.get_display_value(p_value => p_number_value2,
183: p_value_set_id => p_value_set_id);
184: hr_utility.set_location('val2 is '||l_node2_desc,40);
180: hr_utility.set_location('val1 is '||l_node1_desc,40);
181: if p_range_flag = 'Y' and p_number_value2 is not null then
182: l_node2_desc := pqh_utility.get_display_value(p_value => p_number_value2,
183: p_value_set_id => p_value_set_id);
184: hr_utility.set_location('val2 is '||l_node2_desc,40);
185: l_node_desc := l_node1_desc ||' - '||l_node2_desc;
186: else
187: l_node_desc := l_node1_desc ;
188: end if;
186: else
187: l_node_desc := l_node1_desc ;
188: end if;
189: elsif p_crit_col_dtyp ='D' then
190: hr_utility.set_location('Date based vset',40);
191: select to_char(p_date_value1,fnd_profile.value('ICX_DATE_FORMAT_MASK'))
192: into l_node1_desc
193: from dual;
194: hr_utility.set_location('val1 is '||l_node1_desc,40);
190: hr_utility.set_location('Date based vset',40);
191: select to_char(p_date_value1,fnd_profile.value('ICX_DATE_FORMAT_MASK'))
192: into l_node1_desc
193: from dual;
194: hr_utility.set_location('val1 is '||l_node1_desc,40);
195: if p_range_flag = 'Y' and p_date_value2 is not null then
196: select to_char(p_date_value2,fnd_profile.value('ICX_DATE_FORMAT_MASK'))
197: into l_node2_desc
198: from dual;
195: if p_range_flag = 'Y' and p_date_value2 is not null then
196: select to_char(p_date_value2,fnd_profile.value('ICX_DATE_FORMAT_MASK'))
197: into l_node2_desc
198: from dual;
199: hr_utility.set_location('val2 is '||l_node2_desc,40);
200: l_node_desc := l_node1_desc ||' - '||l_node2_desc;
201: else
202: l_node_desc := l_node1_desc ;
203: end if;
228: cursor c_rnv is select * from pqh_rt_matrix_node_values
229: where rate_matrix_node_id = p_rate_matrix_node_id;
230: begin
231: -- get the node details
232: hr_utility.set_location('get node value for '||p_rate_matrix_node_id,10);
233: pqh_utility.init_query_date;
234: pqh_utility.set_query_date(p_effective_date => p_effective_date);
235: if p_node_short_code is null then
236: begin
237: select criteria_short_code
238: into l_crit_short_code
239: from pqh_rate_matrix_nodes
240: where rate_matrix_node_id = p_rate_matrix_node_id;
241: hr_utility.set_location('nodeshort_code is'||l_crit_short_code,10);
242: exception
243: when others then
244: hr_utility.set_location('issues in getting '||l_crit_short_code,10);
245: raise;
240: where rate_matrix_node_id = p_rate_matrix_node_id;
241: hr_utility.set_location('nodeshort_code is'||l_crit_short_code,10);
242: exception
243: when others then
244: hr_utility.set_location('issues in getting '||l_crit_short_code,10);
245: raise;
246: end;
247: else
248: l_crit_short_code := p_node_short_code;
247: else
248: l_crit_short_code := p_node_short_code;
249: end if;
250: if l_crit_short_code is not null then
251: hr_utility.set_location('get criteria detail'||l_crit_short_code,25);
252: get_criteria(p_criteria_short_code => l_crit_short_code,
253: p_business_group_id => p_business_group_id,
254: p_crit_rec => l_crt_rec);
255: hr_utility.set_location('criteria desc is'||substr(l_crt_rec.name,1,30),30);
251: hr_utility.set_location('get criteria detail'||l_crit_short_code,25);
252: get_criteria(p_criteria_short_code => l_crit_short_code,
253: p_business_group_id => p_business_group_id,
254: p_crit_rec => l_crt_rec);
255: hr_utility.set_location('criteria desc is'||substr(l_crt_rec.name,1,30),30);
256: if l_crt_rec.crit_col2_val_type_cd is not null then
257: hr_utility.set_location('2 part critera',30);
258: l_part2 := 'Y';
259: end if;
253: p_business_group_id => p_business_group_id,
254: p_crit_rec => l_crt_rec);
255: hr_utility.set_location('criteria desc is'||substr(l_crt_rec.name,1,30),30);
256: if l_crt_rec.crit_col2_val_type_cd is not null then
257: hr_utility.set_location('2 part critera',30);
258: l_part2 := 'Y';
259: end if;
260: for i in c_rnv loop
261: l_node1_desc := '';
258: l_part2 := 'Y';
259: end if;
260: for i in c_rnv loop
261: l_node1_desc := '';
262: hr_utility.set_location('node_value_id is'||i.node_value_id,40);
263: hr_utility.set_location('node_ dt is'||l_crt_rec.crit_col1_datatype,40);
264: l_node1_desc := node_value(p_val_type_cd => l_crt_rec.crit_col1_val_type_cd,
265: p_lookup_type => l_crt_rec.col1_lookup_type,
266: p_crit_col_dtyp => l_crt_rec.crit_col1_datatype,
259: end if;
260: for i in c_rnv loop
261: l_node1_desc := '';
262: hr_utility.set_location('node_value_id is'||i.node_value_id,40);
263: hr_utility.set_location('node_ dt is'||l_crt_rec.crit_col1_datatype,40);
264: l_node1_desc := node_value(p_val_type_cd => l_crt_rec.crit_col1_val_type_cd,
265: p_lookup_type => l_crt_rec.col1_lookup_type,
266: p_crit_col_dtyp => l_crt_rec.crit_col1_datatype,
267: p_value_set_id => l_crt_rec.col1_value_set_id,
275: p_effective_date => p_effective_date);
276: if l_part2 = 'Y' then
277: l_node2_desc := '';
278:
279: hr_utility.set_location('before node description 2',200);
280: hr_utility.set_location('before node 2 crit_col2_val_type_cd'||l_crt_rec.crit_col2_val_type_cd,200);
281: hr_utility.set_location('before node 2 l_crt_rec.col2_lookup_type'||l_crt_rec.col2_lookup_type,200);
282: hr_utility.set_location('before node 2 l_crt_rec.col2_value_set_id'||l_crt_rec.col2_value_set_id,200);
283: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
276: if l_part2 = 'Y' then
277: l_node2_desc := '';
278:
279: hr_utility.set_location('before node description 2',200);
280: hr_utility.set_location('before node 2 crit_col2_val_type_cd'||l_crt_rec.crit_col2_val_type_cd,200);
281: hr_utility.set_location('before node 2 l_crt_rec.col2_lookup_type'||l_crt_rec.col2_lookup_type,200);
282: hr_utility.set_location('before node 2 l_crt_rec.col2_value_set_id'||l_crt_rec.col2_value_set_id,200);
283: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
284: hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
277: l_node2_desc := '';
278:
279: hr_utility.set_location('before node description 2',200);
280: hr_utility.set_location('before node 2 crit_col2_val_type_cd'||l_crt_rec.crit_col2_val_type_cd,200);
281: hr_utility.set_location('before node 2 l_crt_rec.col2_lookup_type'||l_crt_rec.col2_lookup_type,200);
282: hr_utility.set_location('before node 2 l_crt_rec.col2_value_set_id'||l_crt_rec.col2_value_set_id,200);
283: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
284: hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
285: hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
278:
279: hr_utility.set_location('before node description 2',200);
280: hr_utility.set_location('before node 2 crit_col2_val_type_cd'||l_crt_rec.crit_col2_val_type_cd,200);
281: hr_utility.set_location('before node 2 l_crt_rec.col2_lookup_type'||l_crt_rec.col2_lookup_type,200);
282: hr_utility.set_location('before node 2 l_crt_rec.col2_value_set_id'||l_crt_rec.col2_value_set_id,200);
283: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
284: hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
285: hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
286: hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
279: hr_utility.set_location('before node description 2',200);
280: hr_utility.set_location('before node 2 crit_col2_val_type_cd'||l_crt_rec.crit_col2_val_type_cd,200);
281: hr_utility.set_location('before node 2 l_crt_rec.col2_lookup_type'||l_crt_rec.col2_lookup_type,200);
282: hr_utility.set_location('before node 2 l_crt_rec.col2_value_set_id'||l_crt_rec.col2_value_set_id,200);
283: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
284: hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
285: hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
286: hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
287: hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
280: hr_utility.set_location('before node 2 crit_col2_val_type_cd'||l_crt_rec.crit_col2_val_type_cd,200);
281: hr_utility.set_location('before node 2 l_crt_rec.col2_lookup_type'||l_crt_rec.col2_lookup_type,200);
282: hr_utility.set_location('before node 2 l_crt_rec.col2_value_set_id'||l_crt_rec.col2_value_set_id,200);
283: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
284: hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
285: hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
286: hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
287: hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
288: hr_utility.set_location('before node 2 l_crt_rec.col1_value_set_id'||l_crt_rec.col1_value_set_id,200);
281: hr_utility.set_location('before node 2 l_crt_rec.col2_lookup_type'||l_crt_rec.col2_lookup_type,200);
282: hr_utility.set_location('before node 2 l_crt_rec.col2_value_set_id'||l_crt_rec.col2_value_set_id,200);
283: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
284: hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
285: hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
286: hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
287: hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
288: hr_utility.set_location('before node 2 l_crt_rec.col1_value_set_id'||l_crt_rec.col1_value_set_id,200);
289: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag'||l_crt_rec.allow_range_validation_flag,200);
282: hr_utility.set_location('before node 2 l_crt_rec.col2_value_set_id'||l_crt_rec.col2_value_set_id,200);
283: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
284: hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
285: hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
286: hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
287: hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
288: hr_utility.set_location('before node 2 l_crt_rec.col1_value_set_id'||l_crt_rec.col1_value_set_id,200);
289: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag'||l_crt_rec.allow_range_validation_flag,200);
290: hr_utility.set_location('before node 2 i.number_value1'||i.number_value1,200);
283: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
284: hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
285: hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
286: hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
287: hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
288: hr_utility.set_location('before node 2 l_crt_rec.col1_value_set_id'||l_crt_rec.col1_value_set_id,200);
289: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag'||l_crt_rec.allow_range_validation_flag,200);
290: hr_utility.set_location('before node 2 i.number_value1'||i.number_value1,200);
291: hr_utility.set_location('before node 2 i.char_value1'||i.char_value1,200);
284: hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
285: hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
286: hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
287: hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
288: hr_utility.set_location('before node 2 l_crt_rec.col1_value_set_id'||l_crt_rec.col1_value_set_id,200);
289: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag'||l_crt_rec.allow_range_validation_flag,200);
290: hr_utility.set_location('before node 2 i.number_value1'||i.number_value1,200);
291: hr_utility.set_location('before node 2 i.char_value1'||i.char_value1,200);
292: if (l_crt_rec.crit_col2_val_type_cd = 'VAL_SET' and l_crt_rec.crit_col1_val_type_cd = 'VAL_SET') then
285: hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
286: hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
287: hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
288: hr_utility.set_location('before node 2 l_crt_rec.col1_value_set_id'||l_crt_rec.col1_value_set_id,200);
289: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag'||l_crt_rec.allow_range_validation_flag,200);
290: hr_utility.set_location('before node 2 i.number_value1'||i.number_value1,200);
291: hr_utility.set_location('before node 2 i.char_value1'||i.char_value1,200);
292: if (l_crt_rec.crit_col2_val_type_cd = 'VAL_SET' and l_crt_rec.crit_col1_val_type_cd = 'VAL_SET') then
293: pqh_utility.get_valueset_sql(p_value_set_id => l_crt_rec.col2_value_set_id,
286: hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
287: hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
288: hr_utility.set_location('before node 2 l_crt_rec.col1_value_set_id'||l_crt_rec.col1_value_set_id,200);
289: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag'||l_crt_rec.allow_range_validation_flag,200);
290: hr_utility.set_location('before node 2 i.number_value1'||i.number_value1,200);
291: hr_utility.set_location('before node 2 i.char_value1'||i.char_value1,200);
292: if (l_crt_rec.crit_col2_val_type_cd = 'VAL_SET' and l_crt_rec.crit_col1_val_type_cd = 'VAL_SET') then
293: pqh_utility.get_valueset_sql(p_value_set_id => l_crt_rec.col2_value_set_id,
294: p_validation_type => l_valuset_val_type,
287: hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
288: hr_utility.set_location('before node 2 l_crt_rec.col1_value_set_id'||l_crt_rec.col1_value_set_id,200);
289: hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag'||l_crt_rec.allow_range_validation_flag,200);
290: hr_utility.set_location('before node 2 i.number_value1'||i.number_value1,200);
291: hr_utility.set_location('before node 2 i.char_value1'||i.char_value1,200);
292: if (l_crt_rec.crit_col2_val_type_cd = 'VAL_SET' and l_crt_rec.crit_col1_val_type_cd = 'VAL_SET') then
293: pqh_utility.get_valueset_sql(p_value_set_id => l_crt_rec.col2_value_set_id,
294: p_validation_type => l_valuset_val_type,
295: p_sql_stmt => l_valuset_sql_stmt,
293: pqh_utility.get_valueset_sql(p_value_set_id => l_crt_rec.col2_value_set_id,
294: p_validation_type => l_valuset_val_type,
295: p_sql_stmt => l_valuset_sql_stmt,
296: p_error_status => l_valuset_err_st);
297: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,1,100), 200);
298: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,101,100), 200);
299: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,201,100), 200);
300: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,301,100), 200);
301: Select upper(flex_value_set_name)
294: p_validation_type => l_valuset_val_type,
295: p_sql_stmt => l_valuset_sql_stmt,
296: p_error_status => l_valuset_err_st);
297: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,1,100), 200);
298: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,101,100), 200);
299: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,201,100), 200);
300: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,301,100), 200);
301: Select upper(flex_value_set_name)
302: into l_val_set_name
295: p_sql_stmt => l_valuset_sql_stmt,
296: p_error_status => l_valuset_err_st);
297: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,1,100), 200);
298: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,101,100), 200);
299: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,201,100), 200);
300: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,301,100), 200);
301: Select upper(flex_value_set_name)
302: into l_val_set_name
303: from fnd_flex_value_sets
296: p_error_status => l_valuset_err_st);
297: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,1,100), 200);
298: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,101,100), 200);
299: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,201,100), 200);
300: hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,301,100), 200);
301: Select upper(flex_value_set_name)
302: into l_val_set_name
303: from fnd_flex_value_sets
304: where flex_value_set_id = l_crt_rec.col1_value_set_id;
302: into l_val_set_name
303: from fnd_flex_value_sets
304: where flex_value_set_id = l_crt_rec.col1_value_set_id;
305:
306: hr_utility.set_location('parent value set name '||l_val_set_name, 30);
307:
308: if instr(upper(l_valuset_sql_stmt),':$FLEX$.'||l_val_set_name ) > 0 then
309: if l_crt_rec.crit_col1_datatype = 'C' then
310: l_prnt_value1 := i.char_value1;
331: p_value_set_id => l_crt_rec.col2_value_set_id,
332: p_prnt_valset_nm => l_val_set_name,
333: p_prnt_value => l_prnt_value1);
334: end if;
335: hr_utility.set_location('SJ node2 desc '||l_node2_desc, 30);
336: else
337: l_dep_val_set := 'N';
338: end if;
339: else
338: end if;
339: else
340: l_dep_val_set := 'N';
341: end if;
342: hr_utility.set_location('l_dep_val_set '||l_dep_val_set, 30);
343: if l_dep_val_set = 'N' then
344: l_node2_desc := node_value(p_val_type_cd => l_crt_rec.crit_col2_val_type_cd,
345: p_lookup_type => l_crt_rec.col2_lookup_type,
346: p_crit_col_dtyp => l_crt_rec.crit_col2_datatype,
356: end if;
357:
358: l_node1_desc := l_node1_desc||'/ '||l_node2_desc;
359: end if;
360: hr_utility.set_location('node desc is'||l_node1_desc,25);
361: if l_node_sum_desc is null then
362: l_node_sum_desc := l_node1_desc ;
363: else
364: l_node_sum_desc := l_node_sum_desc ||'; '||l_node1_desc ;
363: else
364: l_node_sum_desc := l_node_sum_desc ||'; '||l_node1_desc ;
365: end if;
366: end loop;
367: hr_utility.set_location('node desc is'||l_node_sum_desc,25);
368: l_node_name := l_crt_rec.name ||' : '||l_node_sum_desc;
369: else
370: hr_utility.set_location('criteria shd be there',40);
371: end if;
366: end loop;
367: hr_utility.set_location('node desc is'||l_node_sum_desc,25);
368: l_node_name := l_crt_rec.name ||' : '||l_node_sum_desc;
369: else
370: hr_utility.set_location('criteria shd be there',40);
371: end if;
372: hr_utility.set_location('node name is'||l_node_name,45);
373: return l_node_name;
374: end build_rate_node_name;
368: l_node_name := l_crt_rec.name ||' : '||l_node_sum_desc;
369: else
370: hr_utility.set_location('criteria shd be there',40);
371: end if;
372: hr_utility.set_location('node name is'||l_node_name,45);
373: return l_node_name;
374: end build_rate_node_name;
375: procedure create_matrix_txn(p_mode in varchar2,
376: p_business_group_id in number,
378: p_copy_entity_txn_id out nocopy number) is
379: l_rbc_txn_cat number;
380: l_ovn number;
381: begin
382: hr_utility.set_location('going for creating cet row',100);
383: begin
384: select transaction_category_id
385: into l_rbc_txn_cat
386: from pqh_transaction_categories
385: into l_rbc_txn_cat
386: from pqh_transaction_categories
387: where short_name ='RBC_MATRIX'
388: and business_group_id is null;
389: hr_utility.set_location('txn_cat is'||l_rbc_txn_cat,100);
390: exception
391: when others then
392: hr_utility.set_location('txn_cat doesnot exist',100);
393: raise;
388: and business_group_id is null;
389: hr_utility.set_location('txn_cat is'||l_rbc_txn_cat,100);
390: exception
391: when others then
392: hr_utility.set_location('txn_cat doesnot exist',100);
393: raise;
394: end;
395: if l_rbc_txn_cat is not null then
396: pqh_copy_entity_txns_api.create_COPY_ENTITY_TXN
409: ) ;
410: end if;
411: exception
412: when others then
413: hr_utility.set_location('issues in creating CET row',100);
414: raise;
415: end create_matrix_txn;
416: procedure delete_matrix(p_copy_entity_txn_id in number) is
417: begin
418: delete from ben_copy_entity_results
419: where copy_entity_txn_id = p_copy_entity_txn_id ;
420: exception
421: when others then
422: hr_utility.set_location('issues in deleting cer rows ',10);
423: raise;
424: end delete_matrix;
425: function get_comp_flag(p_rate_comp_cd in varchar2) return varchar2 is
426: l_comp_flag varchar2(30);
424: end delete_matrix;
425: function get_comp_flag(p_rate_comp_cd in varchar2) return varchar2 is
426: l_comp_flag varchar2(30);
427: begin
428: hr_utility.set_location('rate comp cd is'||p_rate_comp_cd,10);
429: if p_rate_comp_cd in ('AMOUNT','RULE') then
430: l_comp_flag := 'N';
431: elsif p_rate_comp_cd in ('PERCENT','ADD_TO') then
432: l_comp_flag := 'Y';
432: l_comp_flag := 'Y';
433: else
434: l_comp_flag := 'A';
435: end if;
436: hr_utility.set_location('comp flag is'||l_comp_flag,10);
437: return l_comp_flag;
438: end get_comp_flag;
439: procedure matx_rates(p_rate_matrix_id in number,
440: p_cet_id in number,
460: l_crit_name varchar2(250);
461: l_currency_code pqh_criteria_rate_defn.currency_code%type;
462: l_uom pqh_criteria_rate_defn.uom%type;
463: begin
464: hr_utility.set_location('inside copying matrix_rates ',10);
465: for matx_rate in c_matx_rates loop
466: hr_utility.set_location('copying matx_rate to staging'||l_count,20);
467: begin
468: select define_min_rate_flag,define_mid_rate_flag,define_max_rate_flag,define_std_rate_flag,rate_calc_cd, name,currency_code,uom
462: l_uom pqh_criteria_rate_defn.uom%type;
463: begin
464: hr_utility.set_location('inside copying matrix_rates ',10);
465: for matx_rate in c_matx_rates loop
466: hr_utility.set_location('copying matx_rate to staging'||l_count,20);
467: begin
468: select define_min_rate_flag,define_mid_rate_flag,define_max_rate_flag,define_std_rate_flag,rate_calc_cd, name,currency_code,uom
469: into l_min,l_mid,l_max,l_rate,l_calc_mthd,l_crit_name,l_currency_code,l_uom
470: from pqh_criteria_rate_defn_vl
470: from pqh_criteria_rate_defn_vl
471: where criteria_rate_defn_id = matx_rate.criteria_rate_defn_id;
472: exception
473: when others then
474: hr_utility.set_location('issue in getting rate defn det',30);
475: raise;
476: end;
477: l_comp_flag := get_comp_flag(l_calc_mthd);
478: ben_copy_entity_results_api.create_copy_entity_results(
496: ,p_information115 => l_calc_mthd
497: ,p_information116 => l_comp_flag
498: ,p_copy_entity_result_id => l_rcr_cer_id
499: ,p_object_version_number => l_rcr_cer_ovn);
500: hr_utility.set_location('rcr cer row is '||l_rcr_cer_id,30);
501: l_count := l_count + 1;
502: end loop;
503: hr_utility.set_location('total matx rates'||l_count,50);
504: p_count := l_count;
499: ,p_object_version_number => l_rcr_cer_ovn);
500: hr_utility.set_location('rcr cer row is '||l_rcr_cer_id,30);
501: l_count := l_count + 1;
502: end loop;
503: hr_utility.set_location('total matx rates'||l_count,50);
504: p_count := l_count;
505: exception
506: when others then
507: hr_utility.set_location('issues in pulling matx rates',10);
503: hr_utility.set_location('total matx rates'||l_count,50);
504: p_count := l_count;
505: exception
506: when others then
507: hr_utility.set_location('issues in pulling matx rates',10);
508: raise;
509: end matx_rates;
510: procedure get_rate_value(p_rmn_cer_id in number,
511: p_crit_rate_defn_id in number,
538: from pqh_criteria_rate_defn
539: where criteria_rate_defn_id = p_crit_rate_defn_id;
540: exception
541: when no_data_found then
542: hr_utility.set_location('crit rate not exists ',15);
543: raise;
544: when others then
545: hr_utility.set_location('issues in pulling crit rate',20);
546: raise;
541: when no_data_found then
542: hr_utility.set_location('crit rate not exists ',15);
543: raise;
544: when others then
545: hr_utility.set_location('issues in pulling crit rate',20);
546: raise;
547: end ;
548: exception
549: when no_data_found then
546: raise;
547: end ;
548: exception
549: when no_data_found then
550: hr_utility.set_location('no value for node and rate',10);
551: when others then
552: hr_utility.set_location('issues in pulling rate value',20);
553: raise;
554: end get_rate_value;
548: exception
549: when no_data_found then
550: hr_utility.set_location('no value for node and rate',10);
551: when others then
552: hr_utility.set_location('issues in pulling rate value',20);
553: raise;
554: end get_rate_value;
555: procedure get_rate_value(p_rate_matrix_node_id in number,
556: p_crit_rate_defn_id in number,
572: p_max := l_rate_rec.max_rate_value;
573: p_rate := l_rate_rec.rate_value;
574: exception
575: when no_data_found then
576: hr_utility.set_location('no value for node and rate',10);
577: when others then
578: hr_utility.set_location('issues in pulling rate value',20);
579: raise;
580: end get_rate_value;
574: exception
575: when no_data_found then
576: hr_utility.set_location('no value for node and rate',10);
577: when others then
578: hr_utility.set_location('issues in pulling rate value',20);
579: raise;
580: end get_rate_value;
581: function get_annual_factor(p_freq_cd in varchar2) return number is
582: begin
594: return 2;
595: elsif p_freq_cd = 'SMO' then
596: return 24;
597: else
598: hr_utility.set_location('invalid freq passed',10);
599: return -1;
600: end if;
601: end get_annual_factor;
602: procedure get_comp_value(p_rate_defn_id in number,
630: l_ref_freq_ann number;
631: l_curr_cd varchar2(30);
632: l_freq_cd varchar2(30);
633: begin
634: hr_utility.set_location('get computed value for RMN'||p_rmn_cer_id,10);
635: hr_utility.set_location(' CRT'||p_rate_defn_id,10);
636: begin
637: select * into l_crit_rate_rec
638: from pqh_criteria_rate_defn
631: l_curr_cd varchar2(30);
632: l_freq_cd varchar2(30);
633: begin
634: hr_utility.set_location('get computed value for RMN'||p_rmn_cer_id,10);
635: hr_utility.set_location(' CRT'||p_rate_defn_id,10);
636: begin
637: select * into l_crit_rate_rec
638: from pqh_criteria_rate_defn
639: where criteria_rate_defn_id = p_rate_defn_id;
638: from pqh_criteria_rate_defn
639: where criteria_rate_defn_id = p_rate_defn_id;
640: exception
641: when no_data_found then
642: hr_utility.set_location('no rate exists'||p_rate_defn_id,20);
643: raise ;
644: when others then
645: hr_utility.set_location('rate fetch causing issues'||p_rate_defn_id,30);
646: raise ;
641: when no_data_found then
642: hr_utility.set_location('no rate exists'||p_rate_defn_id,20);
643: raise ;
644: when others then
645: hr_utility.set_location('rate fetch causing issues'||p_rate_defn_id,30);
646: raise ;
647: end;
648: if l_crit_rate_rec.rate_calc_cd in ('AMOUNT','RULE') then
649: hr_utility.set_location('rate of type amt ',30);
645: hr_utility.set_location('rate fetch causing issues'||p_rate_defn_id,30);
646: raise ;
647: end;
648: if l_crit_rate_rec.rate_calc_cd in ('AMOUNT','RULE') then
649: hr_utility.set_location('rate of type amt ',30);
650: p_min_c := null;
651: p_mid_c := null;
652: p_max_c := null;
653: p_rate_c := null;
651: p_mid_c := null;
652: p_max_c := null;
653: p_rate_c := null;
654: elsif l_crit_rate_rec.rate_calc_cd in ('ADD_TO','PERCENT','SUM') then
655: hr_utility.set_location('rate of type '||l_crit_rate_rec.rate_calc_cd,40);
656: for i in crit_rate_factors(p_rate_defn_id) loop
657: hr_utility.set_location('rate factors pulled ',45);
658: get_rate_value(p_rmn_cer_id => p_rmn_cer_id,
659: p_cet_id => p_cet_id,
653: p_rate_c := null;
654: elsif l_crit_rate_rec.rate_calc_cd in ('ADD_TO','PERCENT','SUM') then
655: hr_utility.set_location('rate of type '||l_crit_rate_rec.rate_calc_cd,40);
656: for i in crit_rate_factors(p_rate_defn_id) loop
657: hr_utility.set_location('rate factors pulled ',45);
658: get_rate_value(p_rmn_cer_id => p_rmn_cer_id,
659: p_cet_id => p_cet_id,
660: p_crit_rate_defn_id => i.parent_criteria_rate_defn_id,
661: p_effective_date => p_effective_date,
664: p_max => l_rate_max,
665: p_rate => l_rate_value,
666: p_currency_cd => l_curr_cd,
667: p_freq_cd => l_freq_cd);
668: hr_utility.set_location('parent value of rate '||l_rate_value,45);
669: hr_utility.set_location('parent value of min '||l_rate_min,45);
670: hr_utility.set_location('parent value of mid '||l_rate_mid,45);
671: hr_utility.set_location('parent value of max '||l_rate_max,45);
672: if l_freq_cd <> l_crit_rate_rec.reference_period_cd then
665: p_rate => l_rate_value,
666: p_currency_cd => l_curr_cd,
667: p_freq_cd => l_freq_cd);
668: hr_utility.set_location('parent value of rate '||l_rate_value,45);
669: hr_utility.set_location('parent value of min '||l_rate_min,45);
670: hr_utility.set_location('parent value of mid '||l_rate_mid,45);
671: hr_utility.set_location('parent value of max '||l_rate_max,45);
672: if l_freq_cd <> l_crit_rate_rec.reference_period_cd then
673: -- get the conv factor between frequencies
666: p_currency_cd => l_curr_cd,
667: p_freq_cd => l_freq_cd);
668: hr_utility.set_location('parent value of rate '||l_rate_value,45);
669: hr_utility.set_location('parent value of min '||l_rate_min,45);
670: hr_utility.set_location('parent value of mid '||l_rate_mid,45);
671: hr_utility.set_location('parent value of max '||l_rate_max,45);
672: if l_freq_cd <> l_crit_rate_rec.reference_period_cd then
673: -- get the conv factor between frequencies
674: l_rt_freq_ann := get_annual_factor(l_freq_cd);
667: p_freq_cd => l_freq_cd);
668: hr_utility.set_location('parent value of rate '||l_rate_value,45);
669: hr_utility.set_location('parent value of min '||l_rate_min,45);
670: hr_utility.set_location('parent value of mid '||l_rate_mid,45);
671: hr_utility.set_location('parent value of max '||l_rate_max,45);
672: if l_freq_cd <> l_crit_rate_rec.reference_period_cd then
673: -- get the conv factor between frequencies
674: l_rt_freq_ann := get_annual_factor(l_freq_cd);
675: l_ref_freq_ann := get_annual_factor(l_crit_rate_rec.reference_period_cd);
676: l_freq_conv := l_rt_freq_ann/l_ref_freq_ann;
677: else
678: l_freq_conv := 1;
679: end if;
680: hr_utility.set_location('freq conv fctr is '||l_freq_conv,46);
681: if l_curr_cd <> l_crit_rate_rec.currency_code then
682: hr_utility.set_location('Conv from '||l_curr_cd||' To '||l_crit_rate_rec.currency_code,46);
683: -- get the conv factor between currencies from gl_daily_rates
684: begin
678: l_freq_conv := 1;
679: end if;
680: hr_utility.set_location('freq conv fctr is '||l_freq_conv,46);
681: if l_curr_cd <> l_crit_rate_rec.currency_code then
682: hr_utility.set_location('Conv from '||l_curr_cd||' To '||l_crit_rate_rec.currency_code,46);
683: -- get the conv factor between currencies from gl_daily_rates
684: begin
685: select conversion_rate
686: into l_curr_conv
693: and to_currency = l_crit_rate_rec.currency_code
694: and conversion_date <=p_effective_date);
695: exception
696: when no_data_found then
697: hr_utility.set_location('rates not exist',25);
698: l_curr_conv := 1;
699: when others then
700: hr_utility.set_location('daily rates pull error',25);
701: raise;
696: when no_data_found then
697: hr_utility.set_location('rates not exist',25);
698: l_curr_conv := 1;
699: when others then
700: hr_utility.set_location('daily rates pull error',25);
701: raise;
702: end;
703: else
704: l_curr_conv := 1;
702: end;
703: else
704: l_curr_conv := 1;
705: end if;
706: hr_utility.set_location('curr conv factr is'||l_curr_conv,28);
707: l_rate_min := (l_freq_conv * nvl(l_rate_min,0)*l_curr_conv);
708: l_rate_mid := (l_freq_conv * nvl(l_rate_mid,0)*l_curr_conv);
709: l_rate_max := (l_freq_conv * nvl(l_rate_max,0)*l_curr_conv);
710: l_rate_value := (l_freq_conv * nvl(l_rate_value,0)*l_curr_conv);
707: l_rate_min := (l_freq_conv * nvl(l_rate_min,0)*l_curr_conv);
708: l_rate_mid := (l_freq_conv * nvl(l_rate_mid,0)*l_curr_conv);
709: l_rate_max := (l_freq_conv * nvl(l_rate_max,0)*l_curr_conv);
710: l_rate_value := (l_freq_conv * nvl(l_rate_value,0)*l_curr_conv);
711: hr_utility.set_location('conv. parent value of rate '||l_rate_value,45);
712: hr_utility.set_location('conv. parent value of min '||l_rate_min,45);
713: hr_utility.set_location('conv. parent value of mid '||l_rate_mid,45);
714: hr_utility.set_location('conv. parent value of max '||l_rate_max,45);
715: if l_crit_rate_rec.rate_calc_cd = 'ADD_TO' then
708: l_rate_mid := (l_freq_conv * nvl(l_rate_mid,0)*l_curr_conv);
709: l_rate_max := (l_freq_conv * nvl(l_rate_max,0)*l_curr_conv);
710: l_rate_value := (l_freq_conv * nvl(l_rate_value,0)*l_curr_conv);
711: hr_utility.set_location('conv. parent value of rate '||l_rate_value,45);
712: hr_utility.set_location('conv. parent value of min '||l_rate_min,45);
713: hr_utility.set_location('conv. parent value of mid '||l_rate_mid,45);
714: hr_utility.set_location('conv. parent value of max '||l_rate_max,45);
715: if l_crit_rate_rec.rate_calc_cd = 'ADD_TO' then
716: if l_rate_min is not null then
709: l_rate_max := (l_freq_conv * nvl(l_rate_max,0)*l_curr_conv);
710: l_rate_value := (l_freq_conv * nvl(l_rate_value,0)*l_curr_conv);
711: hr_utility.set_location('conv. parent value of rate '||l_rate_value,45);
712: hr_utility.set_location('conv. parent value of min '||l_rate_min,45);
713: hr_utility.set_location('conv. parent value of mid '||l_rate_mid,45);
714: hr_utility.set_location('conv. parent value of max '||l_rate_max,45);
715: if l_crit_rate_rec.rate_calc_cd = 'ADD_TO' then
716: if l_rate_min is not null then
717: l_min_c := l_rate_min + nvl(p_min,0);
710: l_rate_value := (l_freq_conv * nvl(l_rate_value,0)*l_curr_conv);
711: hr_utility.set_location('conv. parent value of rate '||l_rate_value,45);
712: hr_utility.set_location('conv. parent value of min '||l_rate_min,45);
713: hr_utility.set_location('conv. parent value of mid '||l_rate_mid,45);
714: hr_utility.set_location('conv. parent value of max '||l_rate_max,45);
715: if l_crit_rate_rec.rate_calc_cd = 'ADD_TO' then
716: if l_rate_min is not null then
717: l_min_c := l_rate_min + nvl(p_min,0);
718: end if;
851: rmr4_cer_id number;
852: comp4_flag varchar2(30);
853:
854: begin
855: hr_utility.set_location('inside build_matx',10);
856: for rate_types in csr_rate_types loop
857: open csr_rates(rate_types.information1,p_rmn_cer_id);
858: fetch csr_rates into l_rmr_cer_id,l_min,l_max,l_mid,l_rate;
859: if csr_rates%notfound then
856: for rate_types in csr_rate_types loop
857: open csr_rates(rate_types.information1,p_rmn_cer_id);
858: fetch csr_rates into l_rmr_cer_id,l_min,l_max,l_mid,l_rate;
859: if csr_rates%notfound then
860: hr_utility.set_location('rate not found, nulling ',10);
861: hr_utility.set_location('rate type is '||rate_types.information1,10);
862: hr_utility.set_location('rmn_cer is '||p_rmn_cer_id,10);
863: l_min := '';
864: l_mid := '';
857: open csr_rates(rate_types.information1,p_rmn_cer_id);
858: fetch csr_rates into l_rmr_cer_id,l_min,l_max,l_mid,l_rate;
859: if csr_rates%notfound then
860: hr_utility.set_location('rate not found, nulling ',10);
861: hr_utility.set_location('rate type is '||rate_types.information1,10);
862: hr_utility.set_location('rmn_cer is '||p_rmn_cer_id,10);
863: l_min := '';
864: l_mid := '';
865: l_max := '';
858: fetch csr_rates into l_rmr_cer_id,l_min,l_max,l_mid,l_rate;
859: if csr_rates%notfound then
860: hr_utility.set_location('rate not found, nulling ',10);
861: hr_utility.set_location('rate type is '||rate_types.information1,10);
862: hr_utility.set_location('rmn_cer is '||p_rmn_cer_id,10);
863: l_min := '';
864: l_mid := '';
865: l_max := '';
866: l_rate := '';
869: l_max_c := '';
870: l_rate_c := '';
871: l_rmr_cer_id := '';
872: else
873: hr_utility.set_location('rate value '||l_rate,20);
874: get_comp_value(p_rate_defn_id => rate_types.information1,
875: p_rounding_code => 3,
876: p_cet_id => p_cet_id,
877: p_rmn_cer_id => p_rmn_cer_id,
883: p_min_c => l_min_c,
884: p_mid_c => l_mid_c,
885: p_max_c => l_max_c,
886: p_rate_c => l_rate_c);
887: hr_utility.set_location('comp rate '||l_rate_c,20);
888: hr_utility.set_location('comp min '||l_min_c,20);
889: hr_utility.set_location('comp mid '||l_mid_c,20);
890: hr_utility.set_location('comp max '||l_max_c,20);
891: end if;
884: p_mid_c => l_mid_c,
885: p_max_c => l_max_c,
886: p_rate_c => l_rate_c);
887: hr_utility.set_location('comp rate '||l_rate_c,20);
888: hr_utility.set_location('comp min '||l_min_c,20);
889: hr_utility.set_location('comp mid '||l_mid_c,20);
890: hr_utility.set_location('comp max '||l_max_c,20);
891: end if;
892: close csr_rates;
885: p_max_c => l_max_c,
886: p_rate_c => l_rate_c);
887: hr_utility.set_location('comp rate '||l_rate_c,20);
888: hr_utility.set_location('comp min '||l_min_c,20);
889: hr_utility.set_location('comp mid '||l_mid_c,20);
890: hr_utility.set_location('comp max '||l_max_c,20);
891: end if;
892: close csr_rates;
893: if rate_types.information160 = 0 then
886: p_rate_c => l_rate_c);
887: hr_utility.set_location('comp rate '||l_rate_c,20);
888: hr_utility.set_location('comp min '||l_min_c,20);
889: hr_utility.set_location('comp mid '||l_mid_c,20);
890: hr_utility.set_location('comp max '||l_max_c,20);
891: end if;
892: close csr_rates;
893: if rate_types.information160 = 0 then
894: hr_utility.set_location('1st rate values set ',20);
890: hr_utility.set_location('comp max '||l_max_c,20);
891: end if;
892: close csr_rates;
893: if rate_types.information160 = 0 then
894: hr_utility.set_location('1st rate values set ',20);
895: min1 := l_min;
896: mid1 := l_mid;
897: max1 := l_max;
898: rate1 := l_rate;
907: max1_c := l_max_c;
908: rate1_c := l_rate_c;
909: end if;
910: if rate_types.information160 = 1 then
911: hr_utility.set_location('2nd rate values set ',20);
912: min2 := l_min;
913: mid2 := l_mid;
914: max2 := l_max;
915: rate2 := l_rate;
1024: ,p_Information30 => comp4_flag
1025: ,p_information1 => p_rmn_cer_id
1026: ,p_copy_entity_result_id => l_rbr_cer_id
1027: ,p_object_version_number => l_rbr_cer_ovn);
1028: hr_utility.set_location('rbr row created',20);
1029: else
1030: l_rbr_cer_ovn := pqh_gsp_stage_to_ben.get_ovn(
1031: p_table_name => 'BEN_COPY_ENTITY_RESULTS',
1032: p_key_column_name => 'COPY_ENTITY_RESULT_ID',
1031: p_table_name => 'BEN_COPY_ENTITY_RESULTS',
1032: p_key_column_name => 'COPY_ENTITY_RESULT_ID',
1033: p_key_column_value => p_rbr_cer_id,
1034: p_effective_date => '');
1035: hr_utility.set_location('rbr ovn is'||l_rbr_cer_ovn,10);
1036: ben_copy_entity_results_api.update_copy_entity_results(
1037: p_effective_date => p_effective_date
1038: ,p_copy_entity_txn_id => p_cet_id
1039: ,p_result_type_cd => 'DISPLAY'
1099: ,p_information1 => p_rmn_cer_id
1100: ,p_copy_entity_result_id => p_rbr_cer_id
1101: ,p_object_version_number => l_rbr_cer_ovn
1102: ,p_information323 => '');
1103: hr_utility.set_location('rbr row updated',20);
1104: end if;
1105: end build_rbr_for_rmn;
1106: procedure recalc_rate_matx(p_cet_id in number,
1107: p_effective_date in date) is
1113: l_rbr_tr_id number;
1114: l_rbr_tr_name varchar2(150);
1115:
1116: begin
1117: hr_utility.set_location('inside recalc',10);
1118: pqh_gsp_hr_to_stage.get_table_route_details
1119: (p_table_alias => 'RBR',
1120: p_table_route_id => l_rbr_tr_id,
1121: p_table_name => l_rbr_tr_name);
1118: pqh_gsp_hr_to_stage.get_table_route_details
1119: (p_table_alias => 'RBR',
1120: p_table_route_id => l_rbr_tr_id,
1121: p_table_name => l_rbr_tr_name);
1122: hr_utility.set_location('tr name is '||l_rbr_tr_name,10);
1123: for i in c1 loop
1124: rbr_writeback(p_rbr_cer_id => i.copy_entity_result_id,
1125: p_effective_date => p_effective_date);
1126: hr_utility.set_location('rbr wrtbk comple for rmn_cer'||i.information1,22);
1122: hr_utility.set_location('tr name is '||l_rbr_tr_name,10);
1123: for i in c1 loop
1124: rbr_writeback(p_rbr_cer_id => i.copy_entity_result_id,
1125: p_effective_date => p_effective_date);
1126: hr_utility.set_location('rbr wrtbk comple for rmn_cer'||i.information1,22);
1127: build_rbr_for_rmn(p_rmn_cer_id => i.information1,
1128: p_rbr_cer_id => i.copy_entity_result_id,
1129: p_cet_id => p_cet_id,
1130: p_effective_date => p_effective_date,
1129: p_cet_id => p_cet_id,
1130: p_effective_date => p_effective_date,
1131: p_rbr_tr_id => l_rbr_tr_id,
1132: p_rbr_tr_name => l_rbr_tr_name);
1133: hr_utility.set_location('rbr row built ',22);
1134: end loop;
1135: end recalc_rate_matx;
1136: procedure build_rate_matx(p_cet_id in number,
1137: p_effective_date in date) is
1149: l_rbr_tr_id number;
1150: l_rbr_tr_name varchar2(150);
1151: l_rbr_cer_id number;
1152: begin
1153: hr_utility.set_location('inside build_matx',10);
1154: pqh_gsp_hr_to_stage.get_table_route_details
1155: (p_table_alias => 'RBR',
1156: p_table_route_id => l_rbr_tr_id,
1157: p_table_name => l_rbr_tr_name);
1154: pqh_gsp_hr_to_stage.get_table_route_details
1155: (p_table_alias => 'RBR',
1156: p_table_route_id => l_rbr_tr_id,
1157: p_table_name => l_rbr_tr_name);
1158: hr_utility.set_location('tr name is '||l_rbr_tr_name,10);
1159: for node in csr_nodes loop
1160: hr_utility.set_location('rmn cer is '||node.copy_entity_result_id,20);
1161: open csr_rbr(node.copy_entity_result_id);
1162: fetch csr_rbr into l_rbr_cer_id;
1156: p_table_route_id => l_rbr_tr_id,
1157: p_table_name => l_rbr_tr_name);
1158: hr_utility.set_location('tr name is '||l_rbr_tr_name,10);
1159: for node in csr_nodes loop
1160: hr_utility.set_location('rmn cer is '||node.copy_entity_result_id,20);
1161: open csr_rbr(node.copy_entity_result_id);
1162: fetch csr_rbr into l_rbr_cer_id;
1163: if csr_rbr%notfound then
1164: hr_utility.set_location('rbr cer is '||l_rbr_cer_id,10);
1160: hr_utility.set_location('rmn cer is '||node.copy_entity_result_id,20);
1161: open csr_rbr(node.copy_entity_result_id);
1162: fetch csr_rbr into l_rbr_cer_id;
1163: if csr_rbr%notfound then
1164: hr_utility.set_location('rbr cer is '||l_rbr_cer_id,10);
1165: l_rbr_cer_id := '';
1166: end if;
1167: close csr_rbr;
1168: build_rbr_for_rmn(p_rmn_cer_id => node.copy_entity_result_id,
1201: and table_alias ='RCR'
1202: and information160 = p_rate_level;
1203: exception
1204: when no_data_found then
1205: hr_utility.set_location('Rate does not exist',10);
1206: raise;
1207: when others then
1208: hr_utility.set_location('Rate retrival issue ',20);
1209: raise;
1204: when no_data_found then
1205: hr_utility.set_location('Rate does not exist',10);
1206: raise;
1207: when others then
1208: hr_utility.set_location('Rate retrival issue ',20);
1209: raise;
1210: end;
1211: pqh_gsp_hr_to_stage.get_table_route_details
1212: (p_table_alias => 'RMR',
1211: pqh_gsp_hr_to_stage.get_table_route_details
1212: (p_table_alias => 'RMR',
1213: p_table_route_id => l_rmr_tr_id,
1214: p_table_name => l_rmr_tr_name);
1215: hr_utility.set_location('rmr tr name is '||l_rmr_tr_name,10);
1216:
1217: hr_utility.set_location('new rmr row to be cred, rate type'||p_rate_level,10);
1218: ben_copy_entity_results_api.create_copy_entity_results(
1219: p_effective_date => p_effective_date
1213: p_table_route_id => l_rmr_tr_id,
1214: p_table_name => l_rmr_tr_name);
1215: hr_utility.set_location('rmr tr name is '||l_rmr_tr_name,10);
1216:
1217: hr_utility.set_location('new rmr row to be cred, rate type'||p_rate_level,10);
1218: ben_copy_entity_results_api.create_copy_entity_results(
1219: p_effective_date => p_effective_date
1220: ,p_copy_entity_txn_id => p_cet_id
1221: ,p_result_type_cd => 'DISPLAY'
1232: ,p_information162 => l_rcr_rec.information1
1233: ,p_parent_entity_result_id => p_rmn_cer_id
1234: ,p_copy_entity_result_id => l_rmr_cer_id
1235: ,p_object_version_number => l_rmr_cer_ovn);
1236: hr_utility.set_location('rmr cer row is '||l_rmr_cer_id,30);
1237: else
1238: hr_utility.set_location('existing rmr row to be upd',30);
1239: open c1;
1240: fetch c1 into l_rmr_rec;
1234: ,p_copy_entity_result_id => l_rmr_cer_id
1235: ,p_object_version_number => l_rmr_cer_ovn);
1236: hr_utility.set_location('rmr cer row is '||l_rmr_cer_id,30);
1237: else
1238: hr_utility.set_location('existing rmr row to be upd',30);
1239: open c1;
1240: fetch c1 into l_rmr_rec;
1241: if c1%notfound then
1242: hr_utility.set_location('rmr row doesnot exist',30);
1238: hr_utility.set_location('existing rmr row to be upd',30);
1239: open c1;
1240: fetch c1 into l_rmr_rec;
1241: if c1%notfound then
1242: hr_utility.set_location('rmr row doesnot exist',30);
1243: else
1244: if nvl(l_rmr_rec.dml_operation,'COPIED') = 'COPIED' then
1245: l_dml_operation := 'UPDATE';
1246: else
1252: information296 = p_mid,
1253: information297 = p_value,
1254: dml_operation = l_dml_operation
1255: where current of c1;
1256: hr_utility.set_location('rmr row upd',30);
1257: end if;
1258: close c1;
1259: end if;
1260: end write_rmr_row;
1272: update ben_copy_entity_results
1273: set dml_operation ='UPDATE'
1274: where current of c1;
1275: else
1276: hr_utility.set_location('rmn stat'||l_stat,20);
1277: end if;
1278: else
1279: hr_utility.set_location('no rmn'||p_rmn_cer_id,10);
1280: end if;
1275: else
1276: hr_utility.set_location('rmn stat'||l_stat,20);
1277: end if;
1278: else
1279: hr_utility.set_location('no rmn'||p_rmn_cer_id,10);
1280: end if;
1281: close c1;
1282: exception
1283: when no_data_found then
1280: end if;
1281: close c1;
1282: exception
1283: when no_data_found then
1284: hr_utility.set_location('no rmn'||p_rmn_cer_id,10);
1285: raise;
1286: when others then
1287: hr_utility.set_location('issues in getting rmn'||p_rmn_cer_id,10);
1288: raise;
1283: when no_data_found then
1284: hr_utility.set_location('no rmn'||p_rmn_cer_id,10);
1285: raise;
1286: when others then
1287: hr_utility.set_location('issues in getting rmn'||p_rmn_cer_id,10);
1288: raise;
1289: end set_rmn_stat;
1290: procedure rbr_writeback(p_cet_id in number,
1291: p_effective_date in date) is
1316: from ben_copy_entity_results
1317: where copy_entity_result_id = p_rbr_cer_id;
1318: exception
1319: when no_data_found then
1320: hr_utility.set_location('invalid rbr cer passed'||p_rbr_cer_id,10);
1321: raise;
1322: when others then
1323: hr_utility.set_location('issue in rbr pull'||p_rbr_cer_id,10);
1324: raise;
1319: when no_data_found then
1320: hr_utility.set_location('invalid rbr cer passed'||p_rbr_cer_id,10);
1321: raise;
1322: when others then
1323: hr_utility.set_location('issue in rbr pull'||p_rbr_cer_id,10);
1324: raise;
1325: end;
1326: if l_rbr_rec.information160 is not null or
1327: l_rbr_rec.information15 is not null or
1328: l_rbr_rec.information287 is not null or
1329: l_rbr_rec.information288 is not null or
1330: l_rbr_rec.information289 is not null or
1331: l_rbr_rec.information290 is not null then
1332: hr_utility.set_location('1st rmr exists with val '||l_rbr_rec.information290,10);
1333: l_rate1_exists := 'Y';
1334: else
1335: l_rate1_exists := 'N';
1336: end if;
1339: l_rbr_rec.information293 is not null or
1340: l_rbr_rec.information294 is not null or
1341: l_rbr_rec.information295 is not null or
1342: l_rbr_rec.information296 is not null then
1343: hr_utility.set_location('2nd rmr exists with val '||l_rbr_rec.information296,10);
1344: l_rate2_exists := 'Y';
1345: else
1346: l_rate2_exists := 'N';
1347: end if;
1350: l_rbr_rec.information297 is not null or
1351: l_rbr_rec.information298 is not null or
1352: l_rbr_rec.information299 is not null or
1353: l_rbr_rec.information300 is not null then
1354: hr_utility.set_location('3rd rmr exists with val '||l_rbr_rec.information300,10);
1355: l_rate3_exists := 'Y';
1356: else
1357: l_rate3_exists := 'N';
1358: end if;
1361: l_rbr_rec.information301 is not null or
1362: l_rbr_rec.information302 is not null or
1363: l_rbr_rec.information303 is not null or
1364: l_rbr_rec.information304 is not null then
1365: hr_utility.set_location('4th rmr exists with val '||l_rbr_rec.information294,10);
1366: l_rate4_exists := 'Y';
1367: else
1368: l_rate4_exists := 'N';
1369: end if;
1367: else
1368: l_rate4_exists := 'N';
1369: end if;
1370: if l_rate1_exists = 'Y' then
1371: hr_utility.set_location('update rmn status '||l_rbr_rec.information1,10);
1372: set_rmn_stat(p_rmn_cer_id => l_rbr_rec.information1);
1373: hr_utility.set_location('writing 1st rmr for rbr'||p_rbr_cer_id,10);
1374: write_rmr_row(p_rmr_cer_id => l_rbr_rec.information160,
1375: p_rmn_cer_id => l_rbr_rec.information1,
1369: end if;
1370: if l_rate1_exists = 'Y' then
1371: hr_utility.set_location('update rmn status '||l_rbr_rec.information1,10);
1372: set_rmn_stat(p_rmn_cer_id => l_rbr_rec.information1);
1373: hr_utility.set_location('writing 1st rmr for rbr'||p_rbr_cer_id,10);
1374: write_rmr_row(p_rmr_cer_id => l_rbr_rec.information160,
1375: p_rmn_cer_id => l_rbr_rec.information1,
1376: p_rate_level => 0,
1377: p_cet_id => l_rbr_rec.copy_entity_txn_id,
1380: p_mid => l_rbr_rec.information288,
1381: p_max => l_rbr_rec.information289,
1382: p_value => l_rbr_rec.information290);
1383: if l_rate2_exists = 'Y' then
1384: hr_utility.set_location('writing 2nd rmr for rbr'||p_rbr_cer_id,10);
1385: write_rmr_row(p_rmr_cer_id => l_rbr_rec.information161,
1386: p_rmn_cer_id => l_rbr_rec.information1,
1387: p_rate_level => 1,
1388: p_cet_id => l_rbr_rec.copy_entity_txn_id,
1391: p_mid => l_rbr_rec.information294,
1392: p_max => l_rbr_rec.information295,
1393: p_value => l_rbr_rec.information296);
1394: if l_rate3_exists = 'Y' then
1395: hr_utility.set_location('writing 3rd rmr for rbr'||p_rbr_cer_id,10);
1396: write_rmr_row(p_rmr_cer_id => l_rbr_rec.information162,
1397: p_rmn_cer_id => l_rbr_rec.information1,
1398: p_rate_level => 2,
1399: p_cet_id => l_rbr_rec.copy_entity_txn_id,
1402: p_mid => l_rbr_rec.information298,
1403: p_max => l_rbr_rec.information299,
1404: p_value => l_rbr_rec.information300);
1405: if l_rate4_exists = 'Y' then
1406: hr_utility.set_location('writing 4th rmr for rbr'||p_rbr_cer_id,10);
1407: write_rmr_row(p_rmr_cer_id => l_rbr_rec.information169,
1408: p_rmn_cer_id => l_rbr_rec.information1,
1409: p_rate_level => 3,
1410: p_cet_id => l_rbr_rec.copy_entity_txn_id,
1413: p_mid => l_rbr_rec.information302,
1414: p_max => l_rbr_rec.information303,
1415: p_value => l_rbr_rec.information304);
1416: else
1417: hr_utility.set_location('4th rate not there',10);
1418: end if;
1419: else
1420: hr_utility.set_location('3rd rate not there',10);
1421: end if;
1416: else
1417: hr_utility.set_location('4th rate not there',10);
1418: end if;
1419: else
1420: hr_utility.set_location('3rd rate not there',10);
1421: end if;
1422: else
1423: hr_utility.set_location('2nd rate not there',10);
1424: end if;
1419: else
1420: hr_utility.set_location('3rd rate not there',10);
1421: end if;
1422: else
1423: hr_utility.set_location('2nd rate not there',10);
1424: end if;
1425: else
1426: hr_utility.set_location('1st rate not there',10);
1427: end if;
1422: else
1423: hr_utility.set_location('2nd rate not there',10);
1424: end if;
1425: else
1426: hr_utility.set_location('1st rate not there',10);
1427: end if;
1428: end rbr_writeback;
1429: procedure load_plan(p_rate_matrix_id in number,
1430: p_cet_id in number,
1452: where pl_id = p_rate_matrix_id
1453: and p_effective_date between effective_start_date and effective_end_date;
1454: begin
1455: for pl_row in c1 loop
1456: hr_utility.set_location('for copying matx plan row',10);
1457: pqh_gsp_hr_to_stage.get_table_route_details
1458: (p_table_alias => 'PLN',
1459: p_table_route_id => l_pln_tr_id,
1460: p_table_name => l_pln_tr_name);
1457: pqh_gsp_hr_to_stage.get_table_route_details
1458: (p_table_alias => 'PLN',
1459: p_table_route_id => l_pln_tr_id,
1460: p_table_name => l_pln_tr_name);
1461: hr_utility.set_location('tr name is '||l_pln_tr_name,10);
1462:
1463: pqh_gsp_hr_to_stage.get_table_route_details
1464: (p_table_alias => 'RMN',
1465: p_table_route_id => l_rmn_tr_id,
1463: pqh_gsp_hr_to_stage.get_table_route_details
1464: (p_table_alias => 'RMN',
1465: p_table_route_id => l_rmn_tr_id,
1466: p_table_name => l_rmn_tr_name);
1467: hr_utility.set_location('tr name is '||l_rmn_tr_name,10);
1468:
1469: pqh_gsp_hr_to_stage.get_table_route_details
1470: (p_table_alias => 'RCR',
1471: p_table_route_id => l_rcr_tr_id,
1469: pqh_gsp_hr_to_stage.get_table_route_details
1470: (p_table_alias => 'RCR',
1471: p_table_route_id => l_rcr_tr_id,
1472: p_table_name => l_rcr_tr_name);
1473: hr_utility.set_location('tr name is '||l_rcr_tr_name,10);
1474:
1475:
1476: ben_copy_entity_results_api.create_copy_entity_results(
1477: p_effective_date => p_effective_date
1492: ,p_information93 => pl_row.short_code
1493: ,p_information94 => pl_row.short_name
1494: ,p_copy_entity_result_id => l_plan_cer_id
1495: ,p_object_version_number => l_plan_cer_ovn);
1496: hr_utility.set_location('pln cer row is '||l_plan_cer_id,10);
1497: p_plan_cer_id := l_plan_cer_id;
1498: if p_plan_cer_id is not null then
1499: matx_rates(p_rate_matrix_id => pl_row.pl_id,
1500: p_cet_id => p_cet_id,
1502: p_business_group_id => p_business_group_id,
1503: p_rcr_tr_name => l_rcr_tr_name,
1504: p_rcr_tr_id => l_rcr_tr_id,
1505: p_count => l_rate_count);
1506: hr_utility.set_location('rates counted and copied '||l_rate_count,50);
1507: end if;
1508: end loop;
1509: exception
1510: when others then
1507: end if;
1508: end loop;
1509: exception
1510: when others then
1511: hr_utility.set_location('issues in copying plan row',10);
1512: raise;
1513: end load_plan;
1514:
1515: procedure node_val_details(p_rate_matrix_node_id in number,
1530: l_number_xn_value number;
1531: cursor c_node_val is select * from pqh_rt_matrix_node_values
1532: where rate_matrix_node_id = p_rate_matrix_node_id;
1533: begin
1534: hr_utility.set_location('inside copying node_val ',10);
1535: for node_val in c_node_val loop
1536: hr_utility.set_location('copying node_val to staging',20);
1537: if p_crit_type = 'OH' then
1538: l_number_oh_value := node_val.number_value1;
1532: where rate_matrix_node_id = p_rate_matrix_node_id;
1533: begin
1534: hr_utility.set_location('inside copying node_val ',10);
1535: for node_val in c_node_val loop
1536: hr_utility.set_location('copying node_val to staging',20);
1537: if p_crit_type = 'OH' then
1538: l_number_oh_value := node_val.number_value1;
1539: l_number_on_value := node_val.number_value2;
1540: elsif p_crit_type = 'PH' then
1576: ,p_parent_entity_result_id => p_rmn_cer_id
1577: ,p_gs_parent_entity_result_id => p_rmn_cer_id
1578: ,p_copy_entity_result_id => l_rnv_cer_id
1579: ,p_object_version_number => l_rnv_cer_ovn);
1580: hr_utility.set_location('rnv cer row is '||l_rnv_cer_id,30);
1581: end loop;
1582: exception
1583: when others then
1584: hr_utility.set_location('issues in pulling node values',10);
1580: hr_utility.set_location('rnv cer row is '||l_rnv_cer_id,30);
1581: end loop;
1582: exception
1583: when others then
1584: hr_utility.set_location('issues in pulling node values',10);
1585: raise;
1586: end node_val_details;
1587: procedure node_rates(p_rate_matrix_node_id in number,
1588: p_rmn_cer_id in number,
1596: cursor c_node_rate is select * from pqh_rate_matrix_rates_f
1597: where rate_matrix_node_id = p_rate_matrix_node_id
1598: and p_effective_date between effective_start_date and effective_end_date;
1599: begin
1600: hr_utility.set_location('inside copying node_rate ',10);
1601: for node_rate in c_node_rate loop
1602: hr_utility.set_location('copying node_rate to staging',20);
1603: ben_copy_entity_results_api.create_copy_entity_results(
1604: p_effective_date => p_effective_date
1598: and p_effective_date between effective_start_date and effective_end_date;
1599: begin
1600: hr_utility.set_location('inside copying node_rate ',10);
1601: for node_rate in c_node_rate loop
1602: hr_utility.set_location('copying node_rate to staging',20);
1603: ben_copy_entity_results_api.create_copy_entity_results(
1604: p_effective_date => p_effective_date
1605: ,p_copy_entity_txn_id => p_cet_id
1606: ,p_result_type_cd => 'DISPLAY'
1621: ,p_information161 => p_rate_matrix_node_id
1622: ,p_parent_entity_result_id => p_rmn_cer_id
1623: ,p_copy_entity_result_id => l_rmr_cer_id
1624: ,p_object_version_number => l_rmr_cer_ovn);
1625: hr_utility.set_location('rmr cer row is '||l_rmr_cer_id,30);
1626: end loop;
1627: exception
1628: when others then
1629: hr_utility.set_location('issues in pulling node rates',10);
1625: hr_utility.set_location('rmr cer row is '||l_rmr_cer_id,30);
1626: end loop;
1627: exception
1628: when others then
1629: hr_utility.set_location('issues in pulling node rates',10);
1630: raise;
1631: end node_rates;
1632: procedure load_matrix_dtls(p_rate_matrix_id in number,
1633: p_cet_id in number,
1684: order by level_number;
1685:
1686: --
1687: begin
1688: hr_utility.set_location('inside plan dtl copy ',10);
1689: pqh_gsp_hr_to_stage.get_table_route_details
1690: (p_table_alias => 'RBC_CRIT',
1691: p_table_route_id => l_crt_tr_id,
1692: p_table_name => l_crt_tr_name);
1689: pqh_gsp_hr_to_stage.get_table_route_details
1690: (p_table_alias => 'RBC_CRIT',
1691: p_table_route_id => l_crt_tr_id,
1692: p_table_name => l_crt_tr_name);
1693: hr_utility.set_location('rbc_crit tr name is '||l_crt_tr_name,10);
1694:
1695: pqh_gsp_hr_to_stage.get_table_route_details
1696: (p_table_alias => 'RMN',
1697: p_table_route_id => l_rmn_tr_id,
1695: pqh_gsp_hr_to_stage.get_table_route_details
1696: (p_table_alias => 'RMN',
1697: p_table_route_id => l_rmn_tr_id,
1698: p_table_name => l_rmn_tr_name);
1699: hr_utility.set_location('rmn tr name is '||l_rmn_tr_name,10);
1700:
1701: pqh_gsp_hr_to_stage.get_table_route_details
1702: (p_table_alias => 'RMV',
1703: p_table_route_id => l_rnv_tr_id,
1701: pqh_gsp_hr_to_stage.get_table_route_details
1702: (p_table_alias => 'RMV',
1703: p_table_route_id => l_rnv_tr_id,
1704: p_table_name => l_rnv_tr_name);
1705: hr_utility.set_location('rnv tr name is '||l_rnv_tr_name,10);
1706:
1707: pqh_gsp_hr_to_stage.get_table_route_details
1708: (p_table_alias => 'RMR',
1709: p_table_route_id => l_rmr_tr_id,
1707: pqh_gsp_hr_to_stage.get_table_route_details
1708: (p_table_alias => 'RMR',
1709: p_table_route_id => l_rmr_tr_id,
1710: p_table_name => l_rmr_tr_name);
1711: hr_utility.set_location('rmr tr name is '||l_rmr_tr_name,10);
1712:
1713: begin
1714: for crit in c_crit loop
1715: hr_utility.set_location('for copying matx crit row',10);
1711: hr_utility.set_location('rmr tr name is '||l_rmr_tr_name,10);
1712:
1713: begin
1714: for crit in c_crit loop
1715: hr_utility.set_location('for copying matx crit row',10);
1716: if crit.criteria_short_code is not null then
1717: get_criteria(p_criteria_short_code => crit.criteria_short_code,
1718: p_business_group_id => p_business_group_id,
1719: p_crit_rec => l_crit);
1736: ,p_parent_entity_result_id => p_plan_cer_id
1737: ,p_gs_parent_entity_result_id => nvl(l_crt_cer_id,p_plan_cer_id)
1738: ,p_copy_entity_result_id => l_crt_cer_id
1739: ,p_object_version_number => l_crt_cer_ovn);
1740: hr_utility.set_location('crt cer row is '||l_crt_cer_id,10);
1741: end loop;
1742: exception
1743: when others then
1744: hr_utility.set_location('issues in copying rbc_crit row',10);
1740: hr_utility.set_location('crt cer row is '||l_crt_cer_id,10);
1741: end loop;
1742: exception
1743: when others then
1744: hr_utility.set_location('issues in copying rbc_crit row',10);
1745: raise;
1746: end;
1747: for node in c_node loop
1748: hr_utility.set_location('for copying matx plan row',10);
1744: hr_utility.set_location('issues in copying rbc_crit row',10);
1745: raise;
1746: end;
1747: for node in c_node loop
1748: hr_utility.set_location('for copying matx plan row',10);
1749: if node.level_number = 1 then
1750: hr_utility.set_location('root node, no need to build name',10);
1751: else
1752: l_node_name := build_rate_node_name
1746: end;
1747: for node in c_node loop
1748: hr_utility.set_location('for copying matx plan row',10);
1749: if node.level_number = 1 then
1750: hr_utility.set_location('root node, no need to build name',10);
1751: else
1752: l_node_name := build_rate_node_name
1753: (p_rate_matrix_node_id => node.rate_matrix_node_id,
1754: p_business_group_id => p_business_group_id,
1783: ,p_parent_entity_result_id => p_plan_cer_id
1784: ,p_gs_parent_entity_result_id => l_parent_id
1785: ,p_copy_entity_result_id => l_rmn_cer_id
1786: ,p_object_version_number => l_rmn_cer_ovn);
1787: hr_utility.set_location('rmn cer row is '||l_rmn_cer_id,10);
1788: if node.criteria_short_code is not null then
1789: l_crit_type := get_crit_type(node.criteria_short_code,p_business_group_id);
1790: hr_utility.set_location('crit type is '||l_crit_type,10);
1791: if l_rmn_cer_id is not null then
1786: ,p_object_version_number => l_rmn_cer_ovn);
1787: hr_utility.set_location('rmn cer row is '||l_rmn_cer_id,10);
1788: if node.criteria_short_code is not null then
1789: l_crit_type := get_crit_type(node.criteria_short_code,p_business_group_id);
1790: hr_utility.set_location('crit type is '||l_crit_type,10);
1791: if l_rmn_cer_id is not null then
1792: node_val_details(p_rate_matrix_node_id => node.rate_matrix_node_id,
1793: p_rmn_cer_id => l_rmn_cer_id,
1794: p_cet_id => p_cet_id,
1804: p_business_group_id => p_business_group_id,
1805: p_rmr_tr_name => l_rmr_tr_name,
1806: p_rmr_tr_id => l_rmr_tr_id);
1807: else
1808: hr_utility.set_location('xxxxxxxxxxxxxxxxx',32);
1809: end if;
1810: else
1811: hr_utility.set_location('for top node ',40);
1812: end if;
1807: else
1808: hr_utility.set_location('xxxxxxxxxxxxxxxxx',32);
1809: end if;
1810: else
1811: hr_utility.set_location('for top node ',40);
1812: end if;
1813: end loop;
1814: p_status_flag := l_status_flag;
1815: exception
1813: end loop;
1814: p_status_flag := l_status_flag;
1815: exception
1816: when others then
1817: hr_utility.set_location('issues in copying rmn row',10);
1818: raise;
1819: end load_matrix_dtls;
1820:
1821: procedure load_matrix(p_rate_matrix_id in number,
1825: p_status_flag out nocopy number) is
1826: l_status_flag number := 0;
1827: l_plan_cer_id number;
1828: begin
1829: hr_utility.set_location('here to copy plan',10);
1830: load_plan(p_rate_matrix_id => p_rate_matrix_id,
1831: p_cet_id => p_copy_entity_txn_id,
1832: p_effective_date => p_effective_date,
1833: p_business_group_id => p_business_group_id,
1831: p_cet_id => p_copy_entity_txn_id,
1832: p_effective_date => p_effective_date,
1833: p_business_group_id => p_business_group_id,
1834: p_plan_cer_id => l_plan_cer_id);
1835: hr_utility.set_location('copied plan cer '||l_plan_cer_id,15);
1836: hr_utility.set_location('here to copy plan details',20);
1837: load_matrix_dtls(p_rate_matrix_id => p_rate_matrix_id,
1838: p_cet_id => p_copy_entity_txn_id,
1839: p_plan_cer_id => l_plan_cer_id,
1832: p_effective_date => p_effective_date,
1833: p_business_group_id => p_business_group_id,
1834: p_plan_cer_id => l_plan_cer_id);
1835: hr_utility.set_location('copied plan cer '||l_plan_cer_id,15);
1836: hr_utility.set_location('here to copy plan details',20);
1837: load_matrix_dtls(p_rate_matrix_id => p_rate_matrix_id,
1838: p_cet_id => p_copy_entity_txn_id,
1839: p_plan_cer_id => l_plan_cer_id,
1840: p_effective_date => p_effective_date,
1840: p_effective_date => p_effective_date,
1841: p_business_group_id => p_business_group_id,
1842: p_status_flag => l_status_flag);
1843: p_status_flag := l_status_flag;
1844: hr_utility.set_location('plan dtls copied with stat '||l_status_flag,25);
1845: end load_matrix;
1846: procedure chk_matrix_presence(p_rate_matrix_id in number,
1847: p_business_group_id in number,
1848: p_copy_entity_txn_id out nocopy number) is
1856: and txn.status = 'UPDATE'
1857: and txn.context_business_group_id = p_business_group_id
1858: and cer.table_alias = 'PLN'
1859: and cer.information1 = p_rate_matrix_id;
1860: hr_utility.set_location('cet row found is '||l_cet_id,10);
1861: p_copy_entity_txn_id := l_cet_id;
1862: exception
1863: when no_data_found then
1864: p_copy_entity_txn_id := l_cet_id;
1861: p_copy_entity_txn_id := l_cet_id;
1862: exception
1863: when no_data_found then
1864: p_copy_entity_txn_id := l_cet_id;
1865: hr_utility.set_location('no cet row exists ',10);
1866: when others then
1867: hr_utility.set_location('issues in checking matx presence',10);
1868: raise;
1869: end chk_matrix_presence;
1863: when no_data_found then
1864: p_copy_entity_txn_id := l_cet_id;
1865: hr_utility.set_location('no cet row exists ',10);
1866: when others then
1867: hr_utility.set_location('issues in checking matx presence',10);
1868: raise;
1869: end chk_matrix_presence;
1870: procedure upd_matrix(p_rate_matrix_id in number,
1871: p_effective_date in date,
1878: begin
1879: p_matrix_loaded := 'ERR';
1880: set_session_date(p_effective_date => p_effective_date);
1881: if p_mode ='NORMAL' then
1882: hr_utility.set_location('checking matx presence in staging',10);
1883: chk_matrix_presence(p_rate_matrix_id => p_rate_matrix_id,
1884: p_business_group_id => p_business_group_id,
1885: p_copy_entity_txn_id => l_cet_id);
1886: if l_cet_id is null then
1883: chk_matrix_presence(p_rate_matrix_id => p_rate_matrix_id,
1884: p_business_group_id => p_business_group_id,
1885: p_copy_entity_txn_id => l_cet_id);
1886: if l_cet_id is null then
1887: hr_utility.set_location('matx not in stage, copying ',10);
1888: create_matrix_txn(p_mode => 'UPDATE',
1889: p_business_group_id => p_business_group_id,
1890: p_effective_date => p_effective_date,
1891: p_copy_entity_txn_id => l_cet_id);
1888: create_matrix_txn(p_mode => 'UPDATE',
1889: p_business_group_id => p_business_group_id,
1890: p_effective_date => p_effective_date,
1891: p_copy_entity_txn_id => l_cet_id);
1892: hr_utility.set_location('cet row created '||l_cet_id,10);
1893: load_matrix(p_rate_matrix_id => p_rate_matrix_id,
1894: p_copy_entity_txn_id => l_cet_id,
1895: p_business_group_id => p_business_group_id,
1896: p_effective_date => p_effective_date,
1895: p_business_group_id => p_business_group_id,
1896: p_effective_date => p_effective_date,
1897: p_status_flag => l_status_flag );
1898: if l_status_flag = 0 then
1899: hr_utility.set_location('matrix copied ',10);
1900: p_matrix_loaded := 'YES';
1901: p_copy_entity_txn_id := l_cet_id;
1902: else
1903: hr_utility.set_location('matrix copy error ',10);
1899: hr_utility.set_location('matrix copied ',10);
1900: p_matrix_loaded := 'YES';
1901: p_copy_entity_txn_id := l_cet_id;
1902: else
1903: hr_utility.set_location('matrix copy error ',10);
1904: p_matrix_loaded := 'ERR';
1905: end if;
1906: else
1907: p_copy_entity_txn_id := l_cet_id;
1907: p_copy_entity_txn_id := l_cet_id;
1908: p_matrix_loaded := 'NO';
1909: end if;
1910: elsif p_mode ='OVERRIDE' then
1911: hr_utility.set_location('override matrix in stage',100);
1912: hr_utility.set_location('checking matx presence in staging',10);
1913: chk_matrix_presence(p_rate_matrix_id => p_rate_matrix_id,
1914: p_business_group_id => p_business_group_id,
1915: p_copy_entity_txn_id => l_cet_id);
1908: p_matrix_loaded := 'NO';
1909: end if;
1910: elsif p_mode ='OVERRIDE' then
1911: hr_utility.set_location('override matrix in stage',100);
1912: hr_utility.set_location('checking matx presence in staging',10);
1913: chk_matrix_presence(p_rate_matrix_id => p_rate_matrix_id,
1914: p_business_group_id => p_business_group_id,
1915: p_copy_entity_txn_id => l_cet_id);
1916: if l_cet_id is null then
1913: chk_matrix_presence(p_rate_matrix_id => p_rate_matrix_id,
1914: p_business_group_id => p_business_group_id,
1915: p_copy_entity_txn_id => l_cet_id);
1916: if l_cet_id is null then
1917: hr_utility.set_location('matx doesnot exist',10);
1918: create_matrix_txn(p_mode => 'UPDATE',
1919: p_business_group_id => p_business_group_id,
1920: p_effective_date => p_effective_date,
1921: p_copy_entity_txn_id => l_cet_id);
1918: create_matrix_txn(p_mode => 'UPDATE',
1919: p_business_group_id => p_business_group_id,
1920: p_effective_date => p_effective_date,
1921: p_copy_entity_txn_id => l_cet_id);
1922: hr_utility.set_location('new cet row created '||l_cet_id,10);
1923: else
1924: delete_matrix(p_copy_entity_txn_id => l_cet_id);
1925: hr_utility.set_location('cer rows for cet deleted',100);
1926: end if;
1921: p_copy_entity_txn_id => l_cet_id);
1922: hr_utility.set_location('new cet row created '||l_cet_id,10);
1923: else
1924: delete_matrix(p_copy_entity_txn_id => l_cet_id);
1925: hr_utility.set_location('cer rows for cet deleted',100);
1926: end if;
1927: load_matrix(p_rate_matrix_id => p_rate_matrix_id,
1928: p_copy_entity_txn_id => l_cet_id,
1929: p_business_group_id => p_business_group_id,
1929: p_business_group_id => p_business_group_id,
1930: p_effective_date => p_effective_date,
1931: p_status_flag => l_status_flag );
1932: if l_status_flag = 0 then
1933: hr_utility.set_location('matrix copied ',10);
1934: p_matrix_loaded := 'YES';
1935: p_copy_entity_txn_id := l_cet_id;
1936: else
1937: hr_utility.set_location('matrix copy error ',10);
1933: hr_utility.set_location('matrix copied ',10);
1934: p_matrix_loaded := 'YES';
1935: p_copy_entity_txn_id := l_cet_id;
1936: else
1937: hr_utility.set_location('matrix copy error ',10);
1938: p_matrix_loaded := 'ERR';
1939: end if;
1940: else
1941: hr_utility.set_location('wrong mode passed',100);
1937: hr_utility.set_location('matrix copy error ',10);
1938: p_matrix_loaded := 'ERR';
1939: end if;
1940: else
1941: hr_utility.set_location('wrong mode passed',100);
1942: end if;
1943: end upd_matrix;
1944: procedure cre_matrix(p_business_group_id in number,
1945: p_effective_date in date,
1947: l_cet_id number;
1948: l_cer_id number;
1949: l_cer_ovn number;
1950: begin
1951: hr_utility.set_location('creating cet row',10);
1952: create_matrix_txn(p_mode => 'CREATE',
1953: p_business_group_id => p_business_group_id,
1954: p_effective_date => p_effective_date,
1955: p_copy_entity_txn_id => l_cet_id);
1953: p_business_group_id => p_business_group_id,
1954: p_effective_date => p_effective_date,
1955: p_copy_entity_txn_id => l_cet_id);
1956: if l_cet_id is not null then
1957: hr_utility.set_location('populate out params',10);
1958: p_copy_entity_txn_id := l_cet_id;
1959: else
1960: hr_utility.set_location('cet row is not there',10);
1961: end if;
1956: if l_cet_id is not null then
1957: hr_utility.set_location('populate out params',10);
1958: p_copy_entity_txn_id := l_cet_id;
1959: else
1960: hr_utility.set_location('cet row is not there',10);
1961: end if;
1962: end cre_matrix;
1963: end pqh_rbc_stage;