[Home] [Help]
221: --
222: procedure update_cer_with_target(c_pk_rec BEN_PD_COPY_TO_BEN_one.G_PK_REC_TYPE, p_copy_entity_txn_id in number) is
223: begin
224: hr_utility.set_location('Inside update_cer_with_target ',233);
225: update ben_copy_entity_results
226: set information9 = c_pk_rec.copy_reuse_type||'-'||c_pk_rec.new_value
227: where copy_entity_txn_id = p_copy_entity_txn_id
228: and table_route_id = c_pk_rec.table_route_id
229: and information1 = c_pk_rec.old_value ;
262: -- UPD START
263: cpe.dml_operation,
264: cpe.datetrack_mode
265: -- UPD END
266: from ben_copy_entity_results cpe,
267: pqh_table_route tr
268: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
269: and cpe.table_route_id = tr.table_route_id
270: -- and tr.where_clause = l_BEN_CM_TYP_USG_F
280: c_information1 number) is
281: select
282: min(cpe.information2) min_esd,
283: max(cpe.information3) min_eed
284: from ben_copy_entity_results cpe
285: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
286: and cpe.table_route_id = c_table_route_id
287: and cpe.information1 = c_information1 ;
288: --
291: c_information2 date,
292: c_information3 date) is
293: select
294: cpe.*
295: from ben_copy_entity_results cpe
296: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
297: and cpe.table_route_id = c_table_route_id
298: and cpe.information1 = c_information1
299: and cpe.information2 = c_information2
359: --
360: l_update boolean := false ;
361: l_datetrack_mode varchar2(80) := hr_api.g_update;
362: l_process_date date;
363: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
364: --
365: --UPD END
366:
367: l_current_pk_id number := null ;
792: -- UPD START
793: cpe.dml_operation,
794: cpe.datetrack_mode
795: -- UPD END
796: from ben_copy_entity_results cpe,
797: pqh_table_route tr
798: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
799: and cpe.table_route_id = tr.table_route_id
800: -- and tr.where_clause = l_BEN_CM_TYP_TRGR_F
811: c_information1 number) is
812: select
813: min(cpe.information2) min_esd,
814: max(cpe.information3) min_eed
815: from ben_copy_entity_results cpe
816: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
817: and cpe.table_route_id = c_table_route_id
818: and cpe.information1 = c_information1 ;
819: --
822: c_information2 date,
823: c_information3 date) is
824: select
825: cpe.*
826: from ben_copy_entity_results cpe
827: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
828: and cpe.table_route_id = c_table_route_id
829: and cpe.information1 = c_information1
830: and cpe.information2 = c_information2
873: --
874: l_update boolean := false ;
875: l_datetrack_mode varchar2(80) := hr_api.g_update;
876: l_process_date date;
877: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
878: --
879: --UPD END
880:
881: --
1281: -- UPD START
1282: cpe.dml_operation,
1283: cpe.datetrack_mode
1284: -- UPD END
1285: from ben_copy_entity_results cpe,
1286: pqh_table_route tr
1287: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
1288: and cpe.table_route_id = tr.table_route_id
1289: -- and tr.where_clause = l_BEN_CM_DLVRY_MTHD_TYP
1299: c_information1 number) is
1300: select
1301: min(cpe.information2) min_esd,
1302: max(cpe.information3) min_eed
1303: from ben_copy_entity_results cpe
1304: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
1305: and cpe.table_route_id = c_table_route_id
1306: and cpe.information1 = c_information1 ;
1307: --
1310: c_information2 date,
1311: c_information3 date) is
1312: select
1313: cpe.*
1314: from ben_copy_entity_results cpe
1315: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
1316: and cpe.table_route_id = c_table_route_id
1317: and cpe.information1 = c_information1
1318: and rownum = 1 ;
1335: --
1336: l_update boolean := false ;
1337: l_datetrack_mode varchar2(80) := hr_api.g_update;
1338: l_process_date date;
1339: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
1340: --
1341: --UPD END
1342:
1343: l_current_pk_id number := null ;
1651: --UPD START
1652: cpe.dml_operation,
1653: cpe.datetrack_mode
1654: -- UPD END
1655: from ben_copy_entity_results cpe,
1656: pqh_table_route tr
1657: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
1658: and cpe.table_route_id = tr.table_route_id
1659: -- and tr.where_clause = l_BEN_CM_DLVRY_MED_TYP
1669: c_information1 number) is
1670: select
1671: min(cpe.information2) min_esd,
1672: max(cpe.information3) min_eed
1673: from ben_copy_entity_results cpe
1674: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
1675: and cpe.table_route_id = c_table_route_id
1676: and cpe.information1 = c_information1 ;
1677: --
1680: c_information2 date,
1681: c_information3 date) is
1682: select
1683: cpe.*
1684: from ben_copy_entity_results cpe
1685: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
1686: and cpe.table_route_id = c_table_route_id
1687: and cpe.information1 = c_information1
1688: and rownum = 1 ;
1705: --
1706: l_update boolean := false ;
1707: l_datetrack_mode varchar2(80) := hr_api.g_update;
1708: l_process_date date;
1709: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
1710: --
1711: --UPD END
1712:
1713: --
2008: --
2009: cursor c_hierarchy(v_cet_id number, v_tr_id number) is
2010: select distinct rate, Parent_rate
2011: from (select information1 rate ,information267 parent_rate
2012: from ben_copy_entity_results cpe
2013: where table_route_id = v_tr_id
2014: and copy_entity_txn_id = v_cet_id)
2015: start with Parent_rate is null
2016: connect by Parent_rate = prior rate
2017: order by 2 desc, rate;
2018: /*
2019: select distinct information1 rate,
2020: information267 Parent_rate
2021: from ben_copy_entity_results cpe
2022: where table_route_id = v_tr_id
2023: and copy_entity_txn_id = v_cet_id
2024: start with information267 is null and
2025: table_route_id = v_tr_id and
2038: close ben_plan_design_program_module.g_table_route ;
2039: --
2040: for i in c_hierarchy(p_copy_entity_txn_id,l_table_route_id) loop
2041: --
2042: update ben_copy_entity_results cpe
2043: set information169 = l_counter
2044: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
2045: and cpe.information1 = i.rate
2046: and cpe.table_route_id = l_table_route_id ;
2109: cpe.table_route_id,
2110: cpe.information169,
2111: cpe.dml_operation,
2112: cpe.datetrack_mode
2113: from ben_copy_entity_results cpe,
2114: pqh_table_route tr
2115: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
2116: and cpe.table_route_id = tr.table_route_id
2117: -- and tr.where_clause = l_BEN_ACTY_BASE_RT_F
2127: c_information1 number) is
2128: select
2129: min(cpe.information2) min_esd,
2130: max(cpe.information3) min_eed
2131: from ben_copy_entity_results cpe
2132: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
2133: And cpe.table_route_id = c_table_route_id
2134: and cpe.information1 = c_information1 ;
2135: --
2138: c_information2 date,
2139: c_information3 date) is
2140: select
2141: cpe.*
2142: from ben_copy_entity_results cpe
2143: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
2144: and cpe.table_route_id = c_table_route_id
2145: and cpe.information1 = c_information1
2146: and cpe.information2 = c_information2
2244: --
2245: l_update boolean := false ;
2246: l_datetrack_mode varchar2(80) := hr_api.g_update;
2247: l_process_date date;
2248: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
2249: --
2250: --UPD END
2251:
2252: --
2781: --
2782: -- In ghr mode mapping is done based on names so just
2783: -- update the target id's
2784: --
2785: update ben_copy_entity_results
2786: set information176 = l_element_type_id,
2787: information180 = l_input_value_id
2788: where copy_entity_txn_id = p_copy_entity_txn_id
2789: and table_route_id = ben_pd_copy_to_ben_one.g_pk_tbl(ben_pd_copy_to_ben_one.g_count).table_route_id
3043: cpe.information3,
3044: cpe.table_route_id,
3045: cpe.dml_operation,
3046: cpe.datetrack_mode
3047: from ben_copy_entity_results cpe,
3048: pqh_table_route tr
3049: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
3050: and cpe.table_route_id = tr.table_route_id
3051: -- and tr.where_clause = l_BEN_MTCHG_RT_F
3059: c_information1 number) is
3060: select
3061: min(cpe.information2) min_esd,
3062: max(cpe.information3) min_eed
3063: from ben_copy_entity_results cpe
3064: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
3065: and cpe.table_route_id = c_table_route_id
3066: and cpe.information1 = c_information1 ;
3067: --
3070: c_information2 date,
3071: c_information3 date) is
3072: select
3073: cpe.*
3074: from ben_copy_entity_results cpe
3075: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
3076: and cpe.table_route_id = c_table_route_id
3077: and cpe.information1 = c_information1
3078: and cpe.information2 = c_information2
3122: --
3123: l_update boolean := false ;
3124: l_datetrack_mode varchar2(80) := hr_api.g_update;
3125: l_process_date date;
3126: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
3127: --
3128: --UPD END
3129: --
3130: l_current_pk_id number := null ;
3556: cpe.information3,
3557: cpe.table_route_id,
3558: cpe.dml_operation,
3559: cpe.datetrack_mode
3560: from ben_copy_entity_results cpe,
3561: pqh_table_route tr
3562: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
3563: and cpe.table_route_id = tr.table_route_id
3564: -- and tr.where_clause = l_BEN_ACTY_RT_PTD_LMT_F
3572: c_information1 number) is
3573: select
3574: min(cpe.information2) min_esd,
3575: max(cpe.information3) min_eed
3576: from ben_copy_entity_results cpe
3577: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
3578: and cpe.table_route_id = c_table_route_id
3579: and cpe.information1 = c_information1 ;
3580: --
3583: c_information2 date,
3584: c_information3 date) is
3585: select
3586: cpe.*
3587: from ben_copy_entity_results cpe
3588: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
3589: and cpe.table_route_id = c_table_route_id
3590: and cpe.information1 = c_information1
3591: and cpe.information2 = c_information2
3635: --
3636: l_update boolean := false ;
3637: l_datetrack_mode varchar2(80) := hr_api.g_update;
3638: l_process_date date;
3639: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
3640: --
3641: --UPD END
3642:
3643:
4050: cpe.information170 name,
4051: cpe.table_route_id,
4052: cpe.dml_operation,
4053: cpe.datetrack_mode
4054: from ben_copy_entity_results cpe,
4055: pqh_table_route tr
4056: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
4057: and cpe.table_route_id = tr.table_route_id
4058: -- and tr.where_clause = l_BEN_ACTL_PREM_F
4066: c_information1 number) is
4067: select
4068: min(cpe.information2) min_esd,
4069: max(cpe.information3) min_eed
4070: from ben_copy_entity_results cpe
4071: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
4072: and cpe.table_route_id = c_table_route_id
4073: and cpe.information1 = c_information1 ;
4074: --
4077: c_information2 date,
4078: c_information3 date) is
4079: select
4080: cpe.*
4081: from ben_copy_entity_results cpe
4082: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
4083: and cpe.table_route_id = c_table_route_id
4084: and cpe.information1 = c_information1
4085: and cpe.information2 = c_information2
4163: --
4164: l_update boolean := false ;
4165: l_datetrack_mode varchar2(80) := hr_api.g_update;
4166: l_process_date date;
4167: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
4168: --
4169: --UPD END
4170:
4171:
4675: cpe.information3,
4676: cpe.table_route_id,
4677: cpe.dml_operation,
4678: cpe.datetrack_mode
4679: from ben_copy_entity_results cpe,
4680: pqh_table_route tr
4681: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
4682: and cpe.table_route_id = tr.table_route_id
4683: -- and tr.where_clause = l_BEN_ACTY_VRBL_RT_F
4691: c_information1 number) is
4692: select
4693: min(cpe.information2) min_esd,
4694: max(cpe.information3) min_eed
4695: from ben_copy_entity_results cpe
4696: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
4697: and cpe.table_route_id = c_table_route_id
4698: and cpe.information1 = c_information1 ;
4699: --
4702: c_information2 date,
4703: c_information3 date) is
4704: select
4705: cpe.*
4706: from ben_copy_entity_results cpe
4707: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
4708: and cpe.table_route_id = c_table_route_id
4709: and cpe.information1 = c_information1
4710: and cpe.information2 = c_information2
4754: --
4755: l_update boolean := false ;
4756: l_datetrack_mode varchar2(80) := hr_api.g_update;
4757: l_process_date date;
4758: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
4759: --
4760: --UPD END
4761:
4762: l_current_pk_id number := null ;
5174: cpe.information170 name,
5175: cpe.table_route_id,
5176: cpe.dml_operation,
5177: cpe.datetrack_mode
5178: from ben_copy_entity_results cpe,
5179: pqh_table_route tr
5180: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
5181: and cpe.table_route_id = tr.table_route_id
5182: -- and tr.where_clause = l_BEN_VRBL_RT_PRFL_F
5190: c_information1 number) is
5191: select
5192: min(cpe.information2) min_esd,
5193: max(cpe.information3) min_eed
5194: from ben_copy_entity_results cpe
5195: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
5196: and cpe.table_route_id = c_table_route_id
5197: and cpe.information1 = c_information1 ;
5198: --
5201: c_information2 date,
5202: c_information3 date) is
5203: select
5204: cpe.*
5205: from ben_copy_entity_results cpe
5206: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
5207: and cpe.table_route_id = c_table_route_id
5208: and cpe.information1 = c_information1
5209: and cpe.information2 = c_information2
5249: --
5250: l_update boolean := false ;
5251: l_datetrack_mode varchar2(80) := hr_api.g_update;
5252: l_process_date date;
5253: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
5254: --
5255: --UPD END
5256:
5257: l_current_pk_id number := null ;
5867: cpe.information170 name,
5868: cpe.table_route_id,
5869: cpe.dml_operation,
5870: cpe.datetrack_mode
5871: from ben_copy_entity_results cpe,
5872: pqh_table_route tr
5873: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
5874: and cpe.table_route_id = tr.table_route_id
5875: -- and tr.where_clause = l_BEN_CVG_AMT_CALC_MTHD_F
5883: c_information1 number) is
5884: select
5885: min(cpe.information2) min_esd,
5886: max(cpe.information3) min_eed
5887: from ben_copy_entity_results cpe
5888: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
5889: and cpe.table_route_id = c_table_route_id
5890: and cpe.information1 = c_information1 ;
5891: --
5894: c_information2 date,
5895: c_information3 date) is
5896: select
5897: cpe.*
5898: from ben_copy_entity_results cpe
5899: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
5900: and cpe.table_route_id = c_table_route_id
5901: and cpe.information1 = c_information1
5902: and cpe.information2 = c_information2
5978: --
5979: l_update boolean := false ;
5980: l_datetrack_mode varchar2(80) := hr_api.g_update;
5981: l_process_date date;
5982: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
5983: --
5984: --UPD END
5985:
5986: l_current_pk_id number := null ;
6468: cpe.information3,
6469: cpe.table_route_id,
6470: cpe.dml_operation,
6471: cpe.datetrack_mode
6472: from ben_copy_entity_results cpe,
6473: pqh_table_route tr
6474: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
6475: and cpe.table_route_id = tr.table_route_id
6476: -- and tr.where_clause = l_BEN_BNFT_VRBL_RT_F
6484: c_information1 number) is
6485: select
6486: min(cpe.information2) min_esd,
6487: max(cpe.information3) min_eed
6488: from ben_copy_entity_results cpe
6489: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
6490: and cpe.table_route_id = c_table_route_id
6491: and cpe.information1 = c_information1 ;
6492: --
6495: c_information2 date,
6496: c_information3 date) is
6497: select
6498: cpe.*
6499: from ben_copy_entity_results cpe
6500: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
6501: and cpe.table_route_id = c_table_route_id
6502: and cpe.information1 = c_information1
6503: and cpe.information2 = c_information2
6549: --
6550: l_update boolean := false ;
6551: l_datetrack_mode varchar2(80) := hr_api.g_update;
6552: l_process_date date;
6553: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
6554: --
6555: --UPD END
6556:
6557: l_current_pk_id number := null ;
6945: cpe.information3,
6946: cpe.table_route_id,
6947: cpe.dml_operation,
6948: cpe.datetrack_mode
6949: from ben_copy_entity_results cpe,
6950: pqh_table_route tr
6951: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
6952: and cpe.table_route_id = tr.table_route_id
6953: -- and tr.where_clause = l_BEN_BNFT_VRBL_RT_RL_F
6961: c_information1 number) is
6962: select
6963: min(cpe.information2) min_esd,
6964: max(cpe.information3) min_eed
6965: from ben_copy_entity_results cpe
6966: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
6967: and cpe.table_route_id = c_table_route_id
6968: and cpe.information1 = c_information1 ;
6969: --
6972: c_information2 date,
6973: c_information3 date) is
6974: select
6975: cpe.*
6976: from ben_copy_entity_results cpe
6977: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
6978: and cpe.table_route_id = c_table_route_id
6979: and cpe.information1 = c_information1
6980: and cpe.information2 = c_information2
7023: --
7024: l_update boolean := false ;
7025: l_datetrack_mode varchar2(80) := hr_api.g_update;
7026: l_process_date date;
7027: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
7028: --
7029: --UPD END
7030:
7031: --
7415: cpe.information3,
7416: cpe.table_route_id,
7417: cpe.dml_operation,
7418: cpe.datetrack_mode
7419: from ben_copy_entity_results cpe,
7420: pqh_table_route tr
7421: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
7422: and cpe.table_route_id = tr.table_route_id
7423: -- and tr.where_clause = l_BEN_ACTL_PREM_VRBL_RT_F
7431: c_information1 number) is
7432: select
7433: min(cpe.information2) min_esd,
7434: max(cpe.information3) min_eed
7435: from ben_copy_entity_results cpe
7436: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
7437: and cpe.table_route_id = c_table_route_id
7438: and cpe.information1 = c_information1 ;
7439: --
7442: c_information2 date,
7443: c_information3 date) is
7444: select
7445: cpe.*
7446: from ben_copy_entity_results cpe
7447: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
7448: and cpe.table_route_id = c_table_route_id
7449: and cpe.information1 = c_information1
7450: and cpe.information2 = c_information2
7494: --
7495: l_update boolean := false ;
7496: l_datetrack_mode varchar2(80) := hr_api.g_update;
7497: l_process_date date;
7498: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
7499: --
7500: --UPD END
7501:
7502: l_current_pk_id number := null ;
7884: cpe.information3,
7885: cpe.table_route_id,
7886: cpe.dml_operation,
7887: cpe.datetrack_mode
7888: from ben_copy_entity_results cpe,
7889: pqh_table_route tr
7890: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
7891: and cpe.table_route_id = tr.table_route_id
7892: -- and tr.where_clause = l_BEN_ACTL_PREM_VRBL_RT_RL_F
7900: c_information1 number) is
7901: select
7902: min(cpe.information2) min_esd,
7903: max(cpe.information3) min_eed
7904: from ben_copy_entity_results cpe
7905: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
7906: and cpe.table_route_id = c_table_route_id
7907: and cpe.information1 = c_information1 ;
7908: --
7911: c_information2 date,
7912: c_information3 date) is
7913: select
7914: cpe.*
7915: from ben_copy_entity_results cpe
7916: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
7917: and cpe.table_route_id = c_table_route_id
7918: and cpe.information1 = c_information1
7919: and cpe.information2 = c_information2
7962: --
7963: l_update boolean := false ;
7964: l_datetrack_mode varchar2(80) := hr_api.g_update;
7965: l_process_date date;
7966: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
7967: --
7968: --UPD END
7969:
7970: --
8367: cpe.information3,
8368: cpe.table_route_id,
8369: cpe.dml_operation,
8370: cpe.datetrack_mode
8371: from ben_copy_entity_results cpe,
8372: pqh_table_route tr
8373: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
8374: and cpe.table_route_id = tr.table_route_id
8375: -- and tr.where_clause = l_BEN_APLCN_TO_BNFT_POOL_F
8383: c_information1 number) is
8384: select
8385: min(cpe.information2) min_esd,
8386: max(cpe.information3) min_eed
8387: from ben_copy_entity_results cpe
8388: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
8389: and cpe.table_route_id = c_table_route_id
8390: and cpe.information1 = c_information1 ;
8391: --
8394: c_information2 date,
8395: c_information3 date) is
8396: select
8397: cpe.*
8398: from ben_copy_entity_results cpe
8399: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
8400: and cpe.table_route_id = c_table_route_id
8401: and cpe.information1 = c_information1
8402: and cpe.information2 = c_information2
8445: --
8446: l_update boolean := false ;
8447: l_datetrack_mode varchar2(80) := hr_api.g_update;
8448: l_process_date date;
8449: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
8450: --
8451: --UPD END
8452:
8453: --
8852: cpe.information3,
8853: cpe.table_route_id,
8854: cpe.dml_operation,
8855: cpe.datetrack_mode
8856: from ben_copy_entity_results cpe,
8857: pqh_table_route tr
8858: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
8859: and cpe.table_route_id = tr.table_route_id
8860: -- and tr.where_clause = l_BEN_BNFT_POOL_RLOVR_RQMT_F
8868: c_information1 number) is
8869: select
8870: min(cpe.information2) min_esd,
8871: max(cpe.information3) min_eed
8872: from ben_copy_entity_results cpe
8873: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
8874: and cpe.table_route_id = c_table_route_id
8875: and cpe.information1 = c_information1 ;
8876: --
8879: c_information2 date,
8880: c_information3 date) is
8881: select
8882: cpe.*
8883: from ben_copy_entity_results cpe
8884: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
8885: and cpe.table_route_id = c_table_route_id
8886: and cpe.information1 = c_information1
8887: and cpe.information2 = c_information2
8930: --
8931: l_update boolean := false ;
8932: l_datetrack_mode varchar2(80) := hr_api.g_update;
8933: l_process_date date;
8934: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
8935: --
8936: --UPD END
8937:
8938: l_current_pk_id number := null ;
9376: cpe.information3,
9377: cpe.table_route_id,
9378: cpe.dml_operation,
9379: cpe.datetrack_mode
9380: from ben_copy_entity_results cpe,
9381: pqh_table_route tr
9382: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
9383: and cpe.table_route_id = tr.table_route_id
9384: -- and tr.where_clause = l_BEN_DPNT_CVG_RQD_RLSHP_F
9392: c_information1 number) is
9393: select
9394: min(cpe.information2) min_esd,
9395: max(cpe.information3) min_eed
9396: from ben_copy_entity_results cpe
9397: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
9398: and cpe.table_route_id = c_table_route_id
9399: and cpe.information1 = c_information1 ;
9400: --
9403: c_information2 date,
9404: c_information3 date) is
9405: select
9406: cpe.*
9407: from ben_copy_entity_results cpe
9408: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
9409: and cpe.table_route_id = c_table_route_id
9410: and cpe.information1 = c_information1
9411: and cpe.information2 = c_information2
9454: --
9455: l_update boolean := false ;
9456: l_datetrack_mode varchar2(80) := hr_api.g_update;
9457: l_process_date date;
9458: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
9459: --
9460: --UPD END
9461: --
9462: l_current_pk_id number := null ;
9862: cpe.information3,
9863: cpe.table_route_id,
9864: cpe.dml_operation,
9865: cpe.datetrack_mode
9866: from ben_copy_entity_results cpe,
9867: pqh_table_route tr
9868: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
9869: and cpe.table_route_id = tr.table_route_id
9870: -- and tr.where_clause = l_BEN_DSGNTR_ENRLD_CVG_F
9878: c_information1 NUMBER) is
9879: select
9880: min(cpe.information2) min_esd,
9881: max(cpe.information3) min_eed
9882: from ben_copy_entity_results cpe
9883: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
9884: and cpe.table_route_id = c_table_route_id
9885: and cpe.information1 = c_information1 ;
9886: --
9889: c_information2 date,
9890: c_information3 date) is
9891: select
9892: cpe.*
9893: from ben_copy_entity_results cpe
9894: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
9895: and cpe.table_route_id = c_table_route_id
9896: and cpe.information1 = c_information1
9897: and cpe.information2 = c_information2
9940: --
9941: l_update boolean := false ;
9942: l_datetrack_mode varchar2(80) := hr_api.g_update;
9943: l_process_date date;
9944: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
9945: --
9946: --UPD END
9947: --
9948: l_current_pk_id number := null ;
10335: cpe.information3,
10336: cpe.table_route_id,
10337: cpe.dml_operation,
10338: cpe.datetrack_mode
10339: from ben_copy_entity_results cpe,
10340: pqh_table_route tr
10341: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
10342: and cpe.table_route_id = tr.table_route_id
10343: -- and tr.where_clause = l_BEN_DPNT_CVRD_ANTHR_PL_CVG_F
10351: c_information1 number) is
10352: select
10353: min(cpe.information2) min_esd,
10354: max(cpe.information3) min_eed
10355: from ben_copy_entity_results cpe
10356: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
10357: and cpe.table_route_id = c_table_route_id
10358: and cpe.information1 = c_information1 ;
10359: --
10362: c_information2 date,
10363: c_information3 date) is
10364: select
10365: cpe.*
10366: from ben_copy_entity_results cpe
10367: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
10368: and cpe.table_route_id = c_table_route_id
10369: and cpe.information1 = c_information1
10370: and cpe.information2 = c_information2
10413: --
10414: l_update boolean := false ;
10415: l_datetrack_mode varchar2(80) := hr_api.g_update;
10416: l_process_date date;
10417: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
10418: --
10419: --UPD END
10420: --
10421: l_current_pk_id number := null ;
10823: cpe.information3,
10824: cpe.table_route_id,
10825: cpe.dml_operation,
10826: cpe.datetrack_mode
10827: from ben_copy_entity_results cpe,
10828: pqh_table_route tr
10829: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
10830: and cpe.table_route_id = tr.table_route_id
10831: -- and tr.where_clause = l_BEN_ELIG_AGE_CVG_F
10839: c_information1 number) is
10840: select
10841: min(cpe.information2) min_esd,
10842: max(cpe.information3) min_eed
10843: from ben_copy_entity_results cpe
10844: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
10845: and cpe.table_route_id = c_table_route_id
10846: and cpe.information1 = c_information1 ;
10847: --
10850: c_information2 date,
10851: c_information3 date) is
10852: select
10853: cpe.*
10854: from ben_copy_entity_results cpe
10855: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
10856: and cpe.table_route_id = c_table_route_id
10857: and cpe.information1 = c_information1
10858: and cpe.information2 = c_information2
10901: --
10902: l_update boolean := false ;
10903: l_datetrack_mode varchar2(80) := hr_api.g_update;
10904: l_process_date date;
10905: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
10906: --
10907: --UPD END
10908: --
10909: l_current_pk_id number := null ;
11312: cpe.information3,
11313: cpe.table_route_id,
11314: cpe.dml_operation,
11315: cpe.datetrack_mode
11316: from ben_copy_entity_results cpe,
11317: pqh_table_route tr
11318: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
11319: and cpe.table_route_id = tr.table_route_id
11320: -- and tr.where_clause = l_BEN_ELIG_DSBLD_STAT_CVG_F
11328: c_information1 number) is
11329: select
11330: min(cpe.information2) min_esd,
11331: max(cpe.information3) min_eed
11332: from ben_copy_entity_results cpe
11333: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
11334: and cpe.table_route_id = c_table_route_id
11335: and cpe.information1 = c_information1 ;
11336: --
11339: c_information2 date,
11340: c_information3 date) is
11341: select
11342: cpe.*
11343: from ben_copy_entity_results cpe
11344: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
11345: and cpe.table_route_id = c_table_route_id
11346: and cpe.information1 = c_information1
11347: and cpe.information2 = c_information2
11390: --
11391: l_update boolean := false ;
11392: l_datetrack_mode varchar2(80) := hr_api.g_update;
11393: l_process_date date;
11394: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
11395: --
11396: --UPD END
11397: --
11398: l_current_pk_id number := null ;
11788: cpe.information2,
11789: cpe.information3,
11790: cpe.table_route_id,
11791: cpe.dml_operation,cpe.datetrack_mode
11792: from ben_copy_entity_results cpe,
11793: pqh_table_route tr
11794: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
11795: and cpe.table_route_id = tr.table_route_id
11796: -- and tr.where_clause = l_BEN_ELIG_MLTRY_STAT_CVG_F
11804: c_information1 number) is
11805: select
11806: min(cpe.information2) min_esd,
11807: max(cpe.information3) min_eed
11808: from ben_copy_entity_results cpe
11809: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
11810: and cpe.table_route_id = c_table_route_id
11811: and cpe.information1 = c_information1 ;
11812: --
11815: c_information2 date,
11816: c_information3 date) is
11817: select
11818: cpe.*
11819: from ben_copy_entity_results cpe
11820: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
11821: and cpe.table_route_id = c_table_route_id
11822: and cpe.information1 = c_information1
11823: and cpe.information2 = c_information2
11865: --
11866: l_update boolean := false ;
11867: l_datetrack_mode varchar2(80) := hr_api.g_update;
11868: l_process_date date;
11869: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
11870: --
11871: --UPD END
11872: --
11873: l_current_pk_id number := null ;
12272: cpe.information3,
12273: cpe.table_route_id,
12274: cpe.dml_operation,
12275: cpe.datetrack_mode
12276: from ben_copy_entity_results cpe,
12277: pqh_table_route tr
12278: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
12279: and cpe.table_route_id = tr.table_route_id
12280: -- and tr.where_clause = l_BEN_ELIG_MRTL_STAT_CVG_F
12288: c_information1 number) is
12289: select
12290: min(cpe.information2) min_esd,
12291: max(cpe.information3) min_eed
12292: from ben_copy_entity_results cpe
12293: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
12294: and cpe.table_route_id = c_table_route_id
12295: and cpe.information1 = c_information1 ;
12296: --
12299: c_information2 date,
12300: c_information3 date) is
12301: select
12302: cpe.*
12303: from ben_copy_entity_results cpe
12304: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
12305: and cpe.table_route_id = c_table_route_id
12306: and cpe.information1 = c_information1
12307: and cpe.information2 = c_information2
12350: --
12351: l_update boolean := false ;
12352: l_datetrack_mode varchar2(80) := hr_api.g_update;
12353: l_process_date date;
12354: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
12355: --
12356: --UPD END
12357: --
12358: l_current_pk_id number := null ;
12757: cpe.information3,
12758: cpe.table_route_id,
12759: cpe.dml_operation,
12760: cpe.datetrack_mode
12761: from ben_copy_entity_results cpe,
12762: pqh_table_route tr
12763: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
12764: and cpe.table_route_id = tr.table_route_id
12765: -- and tr.where_clause = l_BEN_ELIG_PSTL_CD_R_RNG_CVG_F
12773: c_information1 number) is
12774: select
12775: min(cpe.information2) min_esd,
12776: max(cpe.information3) min_eed
12777: from ben_copy_entity_results cpe
12778: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
12779: and cpe.table_route_id = c_table_route_id
12780: and cpe.information1 = c_information1 ;
12781: --
12784: c_information2 date,
12785: c_information3 date) is
12786: select
12787: cpe.*
12788: from ben_copy_entity_results cpe
12789: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
12790: and cpe.table_route_id = c_table_route_id
12791: and cpe.information1 = c_information1
12792: and cpe.information2 = c_information2
12835: --
12836: l_update boolean := false ;
12837: l_datetrack_mode varchar2(80) := hr_api.g_update;
12838: l_process_date date;
12839: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
12840: --
12841: --UPD END
12842: --
12843: l_current_pk_id number := null ;
13225: cpe.information2,
13226: cpe.information3,
13227: cpe.table_route_id,
13228: cpe.dml_operation,cpe.datetrack_mode
13229: from ben_copy_entity_results cpe,
13230: pqh_table_route tr
13231: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
13232: and cpe.table_route_id = tr.table_route_id
13233: -- and tr.where_clause = l_BEN_ELIG_STDNT_STAT_CVG_F
13241: c_information1 number) is
13242: select
13243: min(cpe.information2) min_esd,
13244: max(cpe.information3) min_eed
13245: from ben_copy_entity_results cpe
13246: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
13247: and cpe.table_route_id = c_table_route_id
13248: and cpe.information1 = c_information1 ;
13249: --
13252: c_information2 date,
13253: c_information3 date) is
13254: select
13255: cpe.*
13256: from ben_copy_entity_results cpe
13257: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
13258: and cpe.table_route_id = c_table_route_id
13259: and cpe.information1 = c_information1
13260: and cpe.information2 = c_information2
13303: --
13304: l_update boolean := false ;
13305: l_datetrack_mode varchar2(80) := hr_api.g_update;
13306: l_process_date date;
13307: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
13308: --
13309: --UPD END
13310: --
13311: l_current_pk_id number := null ;
13707: cpe.information2,
13708: cpe.information3,
13709: cpe.table_route_id,
13710: cpe.dml_operation,cpe.datetrack_mode
13711: from ben_copy_entity_results cpe,
13712: pqh_table_route tr
13713: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
13714: and cpe.table_route_id = tr.table_route_id
13715: -- and tr.where_clause = l_BEN_EXTRA_INPUT_VALUES
13723: c_information1 varchar2) is
13724: select
13725: min(cpe.information2) min_esd,
13726: max(cpe.information3) min_eed
13727: from ben_copy_entity_results cpe
13728: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
13729: and cpe.table_route_id = c_table_route_id
13730: and cpe.information1 = c_information1 ;
13731: --
13734: c_information2 date,
13735: c_information3 date ) is
13736: select
13737: cpe.*
13738: from ben_copy_entity_results cpe
13739: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
13740: and cpe.table_route_id = c_table_route_id
13741: and cpe.information1 = c_information1
13742: and rownum = 1 ;
13759:
13760: cursor c_element_type_id(c_acty_base_rt_id in number,
13761: c_copy_entity_txn_id in number) is
13762: select cpe.information176 element_type_id
13763: from ben_copy_entity_results cpe,
13764: pqh_table_route tre
13765: where cpe.information1 = c_acty_base_rt_id
13766: and cpe.copy_entity_txn_id = c_copy_entity_txn_id
13767: and cpe.table_route_id = tre.table_route_id
13786: --
13787: l_update boolean := false ;
13788: l_datetrack_mode varchar2(80) := hr_api.g_update;
13789: l_process_date date;
13790: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
13791: --
13792: --UPD END
13793: --
13794: l_current_pk_id number := null ;
14142: cpe.information3,
14143: cpe.table_route_id,
14144: cpe.dml_operation,
14145: cpe.datetrack_mode
14146: from ben_copy_entity_results cpe,
14147: pqh_table_route tr
14148: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
14149: and cpe.table_route_id = tr.table_route_id
14150: -- and tr.where_clause = l_BEN_PYMT_SCHED_PY_FREQ
14158: c_information1 varchar2) is
14159: select
14160: min(cpe.information2) min_esd,
14161: max(cpe.information3) min_eed
14162: from ben_copy_entity_results cpe
14163: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
14164: and cpe.table_route_id = c_table_route_id
14165: and cpe.information1 = c_information1 ;
14166: --
14169: c_information2 date,
14170: c_information3 date ) is
14171: select
14172: cpe.*
14173: from ben_copy_entity_results cpe
14174: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
14175: and cpe.table_route_id = c_table_route_id
14176: and cpe.information1 = c_information1
14177: and rownum = 1 ;
14195: --
14196: l_update boolean := false ;
14197: l_datetrack_mode varchar2(80) := hr_api.g_update;
14198: l_process_date date;
14199: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
14200: --
14201: --UPD END
14202: --
14203: l_current_pk_id number := null ;
14499: cpe.information2,
14500: cpe.information3,
14501: cpe.table_route_id,
14502: cpe.dml_operation,cpe.datetrack_mode
14503: from ben_copy_entity_results cpe,
14504: pqh_table_route tr
14505: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
14506: and cpe.table_route_id = tr.table_route_id
14507: -- and tr.where_clause = l_BEN_ACTY_RT_PYMT_SCHED_F
14515: c_information1 varchar2) is
14516: select
14517: min(cpe.information2) min_esd,
14518: max(cpe.information3) min_eed
14519: from ben_copy_entity_results cpe
14520: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
14521: and cpe.table_route_id = c_table_route_id
14522: and cpe.information1 = c_information1 ;
14523: --
14526: c_information2 date,
14527: c_information3 date ) is
14528: select
14529: cpe.*
14530: from ben_copy_entity_results cpe
14531: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
14532: and cpe.table_route_id = c_table_route_id
14533: and cpe.information1 = c_information1
14534: and cpe.information2 = c_information2
14578: --
14579: l_update boolean := false ;
14580: l_datetrack_mode varchar2(80) := hr_api.g_update;
14581: l_process_date date;
14582: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
14583: --
14584: --UPD END
14585:
14586: l_current_pk_id number := null ;
14977: cpe.information2,
14978: cpe.information3,
14979: cpe.table_route_id,
14980: cpe.dml_operation,cpe.datetrack_mode
14981: from ben_copy_entity_results cpe,
14982: pqh_table_route tr
14983: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
14984: and cpe.table_route_id = tr.table_route_id
14985: -- and tr.where_clause = l_BEN_ACTY_BASE_RT_CTFN_F
14993: c_information1 varchar2) is
14994: select
14995: min(cpe.information2) min_esd,
14996: max(cpe.information3) min_eed
14997: from ben_copy_entity_results cpe
14998: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
14999: and cpe.table_route_id = c_table_route_id
15000: and cpe.information1 = c_information1 ;
15001: --
15004: c_information2 date, /* Bug 4350396 */
15005: c_information3 date ) is
15006: select
15007: cpe.*
15008: from ben_copy_entity_results cpe
15009: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
15010: and cpe.table_route_id = c_table_route_id
15011: and cpe.information1 = c_information1
15012: and cpe.information2 = c_information2
15055: --
15056: l_update boolean := false ;
15057: l_datetrack_mode varchar2(80) := hr_api.g_update;
15058: l_process_date date;
15059: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
15060: --
15061: --UPD END
15062:
15063: --
15467: cpe.information2,
15468: cpe.information3,
15469: cpe.table_route_id,
15470: cpe.dml_operation,cpe.datetrack_mode
15471: from ben_copy_entity_results cpe,
15472: pqh_table_route tr
15473: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
15474: and cpe.table_route_id = tr.table_route_id
15475: -- and tr.where_clause = l_BEN_PRTL_MO_RT_PRTN_VAL_F
15483: c_information1 number) is
15484: select
15485: min(cpe.information2) min_esd,
15486: max(cpe.information3) min_eed
15487: from ben_copy_entity_results cpe
15488: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
15489: and cpe.table_route_id = c_table_route_id
15490: and cpe.information1 = c_information1 ;
15491: --
15494: c_information2 date,
15495: c_information3 date) is
15496: select
15497: cpe.*
15498: from ben_copy_entity_results cpe
15499: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
15500: and cpe.table_route_id = c_table_route_id
15501: and cpe.information1 = c_information1
15502: and cpe.information2 = c_information2
15569: --
15570: l_update boolean := false ;
15571: l_datetrack_mode varchar2(80) := hr_api.g_update;
15572: l_process_date date;
15573: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
15574: --
15575: --UPD END
15576: --
15577: l_current_pk_id number := null ;
15989: cpe.information2,
15990: cpe.information3,
15991: cpe.table_route_id,
15992: cpe.dml_operation,cpe.datetrack_mode
15993: from ben_copy_entity_results cpe,
15994: pqh_table_route tr
15995: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
15996: and cpe.table_route_id = tr.table_route_id
15997: -- and tr.where_clause = l_BEN_VRBL_RT_ELIG_PRFL_F
16005: c_information1 number) is
16006: select
16007: min(cpe.information2) min_esd,
16008: max(cpe.information3) min_eed
16009: from ben_copy_entity_results cpe
16010: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
16011: and cpe.table_route_id = c_table_route_id
16012: and cpe.information1 = c_information1 ;
16013: --
16016: c_information2 date,
16017: c_information3 date ) is
16018: select
16019: cpe.*
16020: from ben_copy_entity_results cpe
16021: where cpe.copy_entity_txn_id = p_copy_entity_txn_id
16022: and cpe.table_route_id = c_table_route_id
16023: and cpe.information1 = c_information1
16024: and cpe.information2 = c_information2
16067: --
16068: l_update boolean := false ;
16069: l_datetrack_mode varchar2(80) := hr_api.g_update;
16070: l_process_date date;
16071: l_dml_operation ben_copy_entity_results.dml_operation%TYPE ;
16072: --
16073: --UPD END
16074: --
16075: l_current_pk_id number := null ;