[Home] [Help]
50: l_rmn_cer_id number;
51: begin
52: select copy_entity_result_id
53: into l_rmn_cer_id
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);
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);
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(
479: p_effective_date => p_effective_date
480: ,p_copy_entity_txn_id => p_cet_id
481: ,p_result_type_cd => 'DISPLAY'
482: ,p_table_name => p_rcr_tr_name
516: p_max out nocopy number,
517: p_rate out nocopy number,
518: p_currency_cd out nocopy varchar2,
519: p_freq_cd out nocopy varchar2) is
520: l_rate_rec ben_copy_entity_results%rowtype;
521: begin
522: select *
523: into l_rate_rec
524: from ben_copy_entity_results
520: l_rate_rec ben_copy_entity_results%rowtype;
521: begin
522: select *
523: into l_rate_rec
524: from ben_copy_entity_results
525: where copy_entity_txn_id = p_cet_id
526: and information162 = p_crit_rate_defn_id
527: and table_alias = 'RMR'
528: and parent_entity_result_id = p_rmn_cer_id
767: p_effective_date in date,
768: p_rbr_tr_id in number,
769: p_rbr_tr_name in varchar2) is
770: cursor csr_rate_types is select *
771: from ben_copy_entity_results
772: where copy_entity_txn_id = p_cet_id
773: and table_alias = 'RCR'
774: order by information160; -- in rate order
775: cursor csr_rates (p_rate_defn_id in number, p_node_cer_id in number) is
773: and table_alias = 'RCR'
774: order by information160; -- in rate order
775: cursor csr_rates (p_rate_defn_id in number, p_node_cer_id in number) is
776: select copy_entity_result_id,information294,information295,information296,information297
777: from ben_copy_entity_results
778: where copy_entity_txn_id = p_cet_id
779: and table_alias = 'RMR'
780: and information162 = p_rate_defn_id
781: and parent_entity_result_id = p_node_cer_id and dml_operation <> 'DELETE';
957: rate4_c := l_rate_c;
958: end if;
959: end loop;
960: if p_rbr_cer_id is null then
961: ben_copy_entity_results_api.create_copy_entity_results(
962: p_effective_date => p_effective_date
963: ,p_copy_entity_txn_id => p_cet_id
964: ,p_result_type_cd => 'DISPLAY'
965: ,p_table_name => p_rbr_tr_name
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',
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);
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'
1040: ,p_table_name => p_rbr_tr_name
1105: end build_rbr_for_rmn;
1106: procedure recalc_rate_matx(p_cet_id in number,
1107: p_effective_date in date) is
1108: cursor c1 is select *
1109: from ben_copy_entity_results
1110: where copy_entity_txn_id = p_cet_id
1111: and table_alias = 'RBR'
1112: and nvl(dml_operation,'UPDATE') = 'UPDATE';
1113: l_rbr_tr_id number;
1135: end recalc_rate_matx;
1136: procedure build_rate_matx(p_cet_id in number,
1137: p_effective_date in date) is
1138: cursor csr_nodes is select copy_entity_result_id
1139: from ben_copy_entity_results
1140: where copy_entity_txn_id = p_cet_id
1141: and table_alias = 'RMN'
1142: and dml_operation <> 'DELETE';
1143: cursor csr_rbr(p_rmn_cer_id number) is
1141: and table_alias = 'RMN'
1142: and dml_operation <> 'DELETE';
1143: cursor csr_rbr(p_rmn_cer_id number) is
1144: select copy_entity_result_id
1145: from ben_copy_entity_results
1146: where copy_entity_txn_id = p_cet_id
1147: and table_alias = 'RBR'
1148: and information1 = p_rmn_cer_id;
1149: l_rbr_tr_id number;
1181: p_min in number,
1182: p_mid in number,
1183: p_max in number,
1184: p_value in number) is
1185: l_rcr_rec ben_copy_entity_results%rowtype;
1186: l_rmr_cer_id number;
1187: l_rmr_cer_ovn number;
1188: l_rmr_tr_id number;
1189: l_rmr_tr_name varchar2(80);
1186: l_rmr_cer_id number;
1187: l_rmr_cer_ovn number;
1188: l_rmr_tr_id number;
1189: l_rmr_tr_name varchar2(80);
1190: l_rmr_rec ben_copy_entity_results%rowtype;
1191: l_dml_operation varchar2(30);
1192: cursor c1 is select * from ben_copy_entity_results
1193: where copy_entity_result_id = p_rmr_cer_id
1194: for update of dml_operation;
1188: l_rmr_tr_id number;
1189: l_rmr_tr_name varchar2(80);
1190: l_rmr_rec ben_copy_entity_results%rowtype;
1191: l_dml_operation varchar2(30);
1192: cursor c1 is select * from ben_copy_entity_results
1193: where copy_entity_result_id = p_rmr_cer_id
1194: for update of dml_operation;
1195: begin
1196: if p_rmr_cer_id is null then
1195: begin
1196: if p_rmr_cer_id is null then
1197: begin
1198: select * into l_rcr_rec
1199: from ben_copy_entity_results
1200: where copy_entity_txn_id = p_cet_id
1201: and table_alias ='RCR'
1202: and information160 = p_rate_level;
1203: exception
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'
1222: ,p_table_name => l_rmr_tr_name
1245: l_dml_operation := 'UPDATE';
1246: else
1247: l_dml_operation := l_rmr_rec.dml_operation;
1248: end if;
1249: update ben_copy_entity_results
1250: set information294 = p_min,
1251: information295 = p_max,
1252: information296 = p_mid,
1253: information297 = p_value,
1259: end if;
1260: end write_rmr_row;
1261: procedure set_rmn_stat(p_rmn_cer_id in number) is
1262: cursor c1 is select dml_operation
1263: from ben_copy_entity_results
1264: where copy_entity_result_id = p_rmn_cer_id
1265: for update of dml_operation;
1266: l_stat varchar2(80);
1267: begin
1268: open c1;
1269: fetch c1 into l_stat;
1270: if c1%found then
1271: if l_stat not in ('CREATE','UPDATE') then
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);
1288: raise;
1289: end set_rmn_stat;
1290: procedure rbr_writeback(p_cet_id in number,
1291: p_effective_date in date) is
1292: cursor c1 is select * from ben_copy_entity_results
1293: where copy_entity_txn_id = p_cet_id
1294: and table_alias ='RBR'
1295: and nvl(dml_operation,'UPDATE') = 'UPDATE'
1296: for update of dml_operation;
1297: begin
1298: for i in c1 loop
1299: rbr_writeback(p_rbr_cer_id => i.copy_entity_result_id,
1300: p_effective_date => p_effective_date);
1301: update ben_copy_entity_results
1302: set dml_operation = 'COPIED'
1303: where current of c1;
1304: end loop;
1305: end rbr_writeback;
1304: end loop;
1305: end rbr_writeback;
1306: procedure rbr_writeback(p_rbr_cer_id in number,
1307: p_effective_date in date) is
1308: l_rbr_rec ben_copy_entity_results%rowtype;
1309: l_rate1_exists varchar2(30);
1310: l_rate2_exists varchar2(30);
1311: l_rate3_exists varchar2(30);
1312: l_rate4_exists varchar2(30);
1312: l_rate4_exists varchar2(30);
1313: begin
1314: begin
1315: select * into l_rbr_rec
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);
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
1478: ,p_copy_entity_txn_id => p_cet_id
1479: ,p_result_type_cd => 'DISPLAY'
1480: ,p_table_name => l_pln_tr_name
1543: else
1544: l_number_xh_value := node_val.number_value1;
1545: l_number_xn_value := node_val.number_value2;
1546: end if;
1547: ben_copy_entity_results_api.create_copy_entity_results(
1548: p_effective_date => p_effective_date
1549: ,p_copy_entity_txn_id => p_cet_id
1550: ,p_result_type_cd => 'DISPLAY'
1551: ,p_table_name => p_rnv_tr_name
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'
1607: ,p_table_name => p_rmr_tr_name
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);
1720: end if;
1721: ben_copy_entity_results_api.create_copy_entity_results(
1722: p_effective_date => p_effective_date
1723: ,p_copy_entity_txn_id => p_cet_id
1724: ,p_result_type_cd => 'DISPLAY'
1725: ,p_table_name => l_crt_tr_name
1760: p_cet_id => p_cet_id);
1761: else
1762: l_parent_id := p_plan_cer_id;
1763: end if;
1764: ben_copy_entity_results_api.create_copy_entity_results(
1765: p_effective_date => p_effective_date
1766: ,p_copy_entity_txn_id => p_cet_id
1767: ,p_result_type_cd => 'DISPLAY'
1768: ,p_table_name => l_rmn_tr_name
1849: l_cet_id number;
1850: begin
1851: select txn.copy_entity_txn_id
1852: into l_cet_id
1853: from pqh_copy_entity_txns txn , ben_copy_entity_results cer
1854: where cer.copy_entity_txn_id = txn.copy_entity_txn_id
1855: and txn.context ='RBC_MATRIX'
1856: and txn.status = 'UPDATE'
1857: and txn.context_business_group_id = p_business_group_id