[Home] [Help]
142: enp_m.business_group_id=ctu.business_group_id and
143: enp_m.strt_dt=enp_c.strt_dt
144: ) */
145: )
146: and ben_generate_communications.g_comm_start_date
147: between ctu.effective_start_date
148: and ctu.effective_end_date
149: and ctu.cm_typ_id = p_cm_typ_id
150: and ctu.all_r_any_cd = 'ALL'
213: enp_m.business_group_id=ctu.business_group_id and
214: enp_m.strt_dt=enp_c.strt_dt
215: ) */
216: )
217: and ben_generate_communications.g_comm_start_date
218: between ctu.effective_start_date
219: and ctu.effective_end_date
220: and ctu.cm_typ_id = p_cm_typ_id
221: and ctu.all_r_any_cd = 'ALL'
364: -- create usage
365: --
366: hr_utility.set_location('Communication usage Created for BNF',10);
367: --
368: ben_generate_communications.pop_ben_per_cm_usg_f
369: (p_per_cm_id => p_per_cm_id,
370: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
371: p_business_group_id => p_business_group_id,
372: p_effective_date => p_effective_date,
436: --
437: hr_utility.set_location('Communication usage created for DPNT',10)
438: ;
439: --
440: ben_generate_communications.pop_ben_per_cm_usg_f
441: (p_per_cm_id => p_per_cm_id,
442: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
443: p_business_group_id => p_business_group_id,
444: p_effective_date => p_effective_date,
532: p_effective_date))
533: /* Use nvl here as only pgm pl can be populated */
534: and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
535: and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
536: and ben_generate_communications.g_comm_start_date
537: between ctu.effective_start_date
538: and ctu.effective_end_date
539: and ctu.cm_typ_id = p_cm_typ_id
540: and ctu.all_r_any_cd = 'ALL'
588: p_effective_date))
589: /* Use nvl here as only pgm pl can be populated */
590: and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
591: and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
592: and ben_generate_communications.g_comm_start_date
593: between ctu.effective_start_date
594: and ctu.effective_end_date
595: and ctu.cm_typ_id = p_cm_typ_id
596: and ctu.all_r_any_cd = 'ALL'
687: p_effective_date))
688: /* Use nvl here as only pgm pl can be populated */
689: and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
690: and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
691: and ben_generate_communications.g_comm_start_date
692: between ctu.effective_start_date
693: and ctu.effective_end_date
694: and ctu.cm_typ_id = p_cm_typ_id
695: and ctu.all_r_any_cd = 'ALL'
830: --
831: hr_utility.set_location('Communication usage created for RLTD_PER',
832: 10);
833: --
834: ben_generate_communications.pop_ben_per_cm_usg_f
835: (p_per_cm_id => p_per_cm_id,
836: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
837: p_business_group_id => p_business_group_id,
838: p_effective_date => p_effective_date,
898: -- create usage
899: --
900: hr_utility.set_location('Communication usage Created for BNF',10);
901: --
902: ben_generate_communications.pop_ben_per_cm_usg_f
903: (p_per_cm_id => p_per_cm_id,
904: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
905: p_business_group_id => p_business_group_id,
906: p_effective_date => p_effective_date,
969: -- create usage
970: --
971: hr_utility.set_location('Communication usage created for DPNT',10);
972: --
973: ben_generate_communications.pop_ben_per_cm_usg_f
974: (p_per_cm_id => p_per_cm_id,
975: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
976: p_business_group_id => p_business_group_id,
977: p_effective_date => p_effective_date,
1129: p_effective_date))
1130: /* Use nvl here as only pgm pl can be populated */
1131: and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
1132: and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
1133: and ben_generate_communications.g_comm_start_date
1134: between ctu.effective_start_date
1135: and ctu.effective_end_date
1136: and ctu.cm_typ_id = p_cm_typ_id
1137: and ctu.all_r_any_cd = 'ALL'
1219: p_effective_date))
1220: /* Use nvl here as only pgm pl can be populated */
1221: and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
1222: and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
1223: and ben_generate_communications.g_comm_start_date
1224: between ctu.effective_start_date
1225: and ctu.effective_end_date
1226: and ctu.cm_typ_id = p_cm_typ_id
1227: and ctu.all_r_any_cd = 'ALL'
1364: -- create usage
1365: --
1366: hr_utility.set_location('Communication usage Created for BNF',10);
1367: --
1368: ben_generate_communications.pop_ben_per_cm_usg_f
1369: (p_per_cm_id => p_per_cm_id,
1370: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
1371: p_business_group_id => p_business_group_id,
1372: p_effective_date => p_effective_date,
1435: -- create usage
1436: --
1437: hr_utility.set_location('Communication usage created for DPNT',10);
1438: --
1439: ben_generate_communications.pop_ben_per_cm_usg_f
1440: (p_per_cm_id => p_per_cm_id,
1441: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
1442: p_business_group_id => p_business_group_id,
1443: p_effective_date => p_effective_date,
1529: p_effective_date))
1530: /* Use nvl here as only pgm pl can be populated */
1531: and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
1532: and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
1533: and ben_generate_communications.g_comm_start_date
1534: between ctu.effective_start_date
1535: and ctu.effective_end_date
1536: and ctu.cm_typ_id = p_cm_typ_id
1537: and ctu.all_r_any_cd = 'ALL'
1608: p_effective_date))
1609: /* Use nvl here as only pgm pl can be populated */
1610: and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
1611: and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
1612: and ben_generate_communications.g_comm_start_date
1613: between ctu.effective_start_date
1614: and ctu.effective_end_date
1615: and ctu.cm_typ_id = p_cm_typ_id
1616: and ctu.all_r_any_cd = 'ALL'
1749: -- create usage
1750: --
1751: hr_utility.set_location('Communication usage Created for BNF',10);
1752: --
1753: ben_generate_communications.pop_ben_per_cm_usg_f
1754: (p_per_cm_id => p_per_cm_id,
1755: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
1756: p_business_group_id => p_business_group_id,
1757: p_effective_date => p_effective_date,
1820: -- create usage
1821: --
1822: hr_utility.set_location('Communication usage created for DPNT',10);
1823: --
1824: ben_generate_communications.pop_ben_per_cm_usg_f
1825: (p_per_cm_id => p_per_cm_id,
1826: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
1827: p_business_group_id => p_business_group_id,
1828: p_effective_date => p_effective_date,
1903: )
1904: ) or
1905: (ctu.enrt_perd_id is null)
1906: ) -- ???? 5422 : No need to join to ben_enrt_perd
1907: and ben_generate_communications.g_comm_start_date
1908: between ctu.effective_start_date
1909: and ctu.effective_end_date
1910: and ctu.cm_typ_id = p_cm_typ_id
1911: and ctu.all_r_any_cd = 'ALL'
1965: and ((nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
1966: and pel.pgm_id is null)
1967: or nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)))
1968: or (ctu.enrt_perd_id is null))
1969: and ben_generate_communications.g_comm_start_date
1970: between ctu.effective_start_date
1971: and ctu.effective_end_date
1972: and ctu.cm_typ_id = p_cm_typ_id
1973: and ctu.all_r_any_cd = 'ALL'
2102: -- create usage
2103: --
2104: hr_utility.set_location('Communication usage Created for BNF',10);
2105: --
2106: ben_generate_communications.pop_ben_per_cm_usg_f
2107: (p_per_cm_id => p_per_cm_id,
2108: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
2109: p_business_group_id => p_business_group_id,
2110: p_effective_date => p_effective_date,
2174: --
2175: hr_utility.set_location('Communication usage created for DPNT',10)
2176: ;
2177: --
2178: ben_generate_communications.pop_ben_per_cm_usg_f
2179: (p_per_cm_id => p_per_cm_id,
2180: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
2181: p_business_group_id => p_business_group_id,
2182: p_effective_date => p_effective_date,
2239: and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
2240: and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
2241: and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
2242: = nvl(pel.enrt_perd_id,-1)
2243: and ben_generate_communications.g_comm_start_date
2244: between ctu.effective_start_date
2245: and ctu.effective_end_date
2246: and ctu.cm_typ_id = p_cm_typ_id
2247: and ctu.all_r_any_cd = 'ALL'
2282: and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
2283: and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
2284: and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
2285: = nvl(pel.enrt_perd_id,-1)
2286: and ben_generate_communications.g_comm_start_date
2287: between ctu.effective_start_date
2288: and ctu.effective_end_date
2289: and ctu.cm_typ_id = p_cm_typ_id
2290: and ctu.all_r_any_cd = 'ALL'
2407: -- create usage
2408: --
2409: hr_utility.set_location('Communication usage Created for BNF',10);
2410: --
2411: ben_generate_communications.pop_ben_per_cm_usg_f
2412: (p_per_cm_id => p_per_cm_id,
2413: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
2414: p_business_group_id => p_business_group_id,
2415: p_effective_date => p_effective_date,
2479: --
2480: hr_utility.set_location('Communication usage created for DPNT',10)
2481: ;
2482: --
2483: ben_generate_communications.pop_ben_per_cm_usg_f
2484: (p_per_cm_id => p_per_cm_id,
2485: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
2486: p_business_group_id => p_business_group_id,
2487: p_effective_date => p_effective_date,
2546: and (p_pl_typ_id is null or
2547: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2548: and (p_actn_typ_id is null or
2549: nvl(ctu.actn_typ_id,p_actn_typ_id) = p_actn_typ_id)
2550: and ben_generate_communications.g_comm_start_date
2551: between ctu.effective_start_date
2552: and ctu.effective_end_date
2553: and ctu.cm_typ_id = p_cm_typ_id
2554: and ctu.all_r_any_cd = 'ALL'
2619: and (p_pl_typ_id is null or
2620: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2621: and (p_actn_typ_id is null or
2622: nvl(ctu.actn_typ_id,p_actn_typ_id) = p_actn_typ_id)
2623: and ben_generate_communications.g_comm_start_date
2624: between ctu.effective_start_date
2625: and ctu.effective_end_date
2626: and ctu.cm_typ_id = p_cm_typ_id
2627: and ctu.all_r_any_cd = 'ALL'
2779: -- create usage
2780: --
2781: hr_utility.set_location('Communication usage Created for BNF',10);
2782: --
2783: ben_generate_communications.pop_ben_per_cm_usg_f
2784: (p_per_cm_id => p_per_cm_id,
2785: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
2786: p_business_group_id => p_business_group_id,
2787: p_effective_date => p_effective_date,
2851: --
2852: hr_utility.set_location('Communication usage created for DPNT',10)
2853: ;
2854: --
2855: ben_generate_communications.pop_ben_per_cm_usg_f
2856: (p_per_cm_id => p_per_cm_id,
2857: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
2858: p_business_group_id => p_business_group_id,
2859: p_effective_date => p_effective_date,
2911: and (p_pl_id is null or
2912: nvl(ctu.pl_id,p_pl_id) = p_pl_id)
2913: and (p_pl_typ_id is null or
2914: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2915: and ben_generate_communications.g_comm_start_date
2916: between ctu.effective_start_date
2917: and ctu.effective_end_date
2918: and ctu.cm_typ_id = p_cm_typ_id
2919: and ctu.all_r_any_cd = 'ALL'
3050: --
3051: hr_utility.set_location('Communication usage created for DPNT',10)
3052: ;
3053: --
3054: ben_generate_communications.pop_ben_per_cm_usg_f
3055: (p_per_cm_id => p_per_cm_id,
3056: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
3057: p_business_group_id => p_business_group_id,
3058: p_effective_date => p_effective_date,
3110: and (p_pl_id is null or
3111: nvl(ctu.pl_id,p_pl_id) = p_pl_id)
3112: and (p_pl_typ_id is null or
3113: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3114: and ben_generate_communications.g_comm_start_date
3115: between ctu.effective_start_date
3116: and ctu.effective_end_date
3117: and ctu.cm_typ_id = p_cm_typ_id
3118: and ctu.all_r_any_cd = 'ALL'
3165: and (p_pl_id is null or
3166: nvl(ctu.pl_id,p_pl_id) = p_pl_id)
3167: and (p_pl_typ_id is null or
3168: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3169: and ben_generate_communications.g_comm_start_date
3170: between ctu.effective_start_date
3171: and ctu.effective_end_date
3172: and ctu.cm_typ_id = p_cm_typ_id
3173: and ctu.all_r_any_cd = 'ALL'
3313: -- create usage
3314: --
3315: hr_utility.set_location('Communication usage Created for BNF',10);
3316: --
3317: ben_generate_communications.pop_ben_per_cm_usg_f
3318: (p_per_cm_id => p_per_cm_id,
3319: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
3320: p_business_group_id => p_business_group_id,
3321: p_effective_date => p_effective_date,
3385: --
3386: hr_utility.set_location('Communication usage created for DPNT',10)
3387: ;
3388: --
3389: ben_generate_communications.pop_ben_per_cm_usg_f
3390: (p_per_cm_id => p_per_cm_id,
3391: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
3392: p_business_group_id => p_business_group_id,
3393: p_effective_date => p_effective_date,
3475: and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
3476: = nvl(pel.enrt_perd_id, -1)
3477: and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
3478: and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
3479: and ben_generate_communications.g_comm_start_date
3480: between ctu.effective_start_date
3481: and ctu.effective_end_date
3482: and ctu.cm_typ_id = p_cm_typ_id
3483: and ctu.all_r_any_cd = 'ALL'
3532: and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
3533: = nvl(pel.enrt_perd_id, -1)
3534: and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
3535: and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
3536: and ben_generate_communications.g_comm_start_date
3537: between ctu.effective_start_date
3538: and ctu.effective_end_date
3539: and ctu.cm_typ_id = p_cm_typ_id
3540: and ctu.all_r_any_cd = 'ALL'
3656: -- create usage
3657: --
3658: hr_utility.set_location('Communication usage Created for BNF',10);
3659: --
3660: ben_generate_communications.pop_ben_per_cm_usg_f
3661: (p_per_cm_id => p_per_cm_id,
3662: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
3663: p_business_group_id => p_business_group_id,
3664: p_effective_date => p_effective_date,
3728: --
3729: hr_utility.set_location('Communication usage created for DPNT',10)
3730: ;
3731: --
3732: ben_generate_communications.pop_ben_per_cm_usg_f
3733: (p_per_cm_id => p_per_cm_id,
3734: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
3735: p_business_group_id => p_business_group_id,
3736: p_effective_date => p_effective_date,
3794: and (p_pgm_id is null or
3795: nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
3796: and (p_pl_typ_id is null or
3797: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3798: and ben_generate_communications.g_comm_start_date between
3799: ctu.effective_start_date and ctu.effective_end_date
3800: and ctu.cm_typ_id = p_cm_typ_id
3801: and ctu.all_r_any_cd = 'ALL'
3802: and exists (select null
3850: and (p_pgm_id is null or
3851: nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
3852: and (p_pl_typ_id is null or
3853: nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3854: and ben_generate_communications.g_comm_start_date between
3855: ctu.effective_start_date and ctu.effective_end_date
3856: and ctu.cm_typ_id = p_cm_typ_id
3857: and ctu.all_r_any_cd = 'ALL'
3858: and exists (select null
3997: -- create usage
3998: --
3999: hr_utility.set_location('Communication usage Created for BNF',10);
4000: --
4001: ben_generate_communications.pop_ben_per_cm_usg_f
4002: (p_per_cm_id => p_per_cm_id,
4003: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
4004: p_business_group_id => p_business_group_id,
4005: p_effective_date => p_effective_date,
4069: --
4070: hr_utility.set_location('Communication usage created for DPNT',10)
4071: ;
4072: --
4073: ben_generate_communications.pop_ben_per_cm_usg_f
4074: (p_per_cm_id => p_per_cm_id,
4075: p_cm_typ_usg_id => l_usage.cm_typ_usg_id,
4076: p_business_group_id => p_business_group_id,
4077: p_effective_date => p_effective_date,
4158: and p_effective_date between pet.effective_start_date
4159: and pet.effective_end_date
4160: )
4161: )
4162: and ben_generate_communications.g_comm_start_date
4163: between ctu.effective_start_date
4164: and ctu.effective_end_date
4165: and ctu.cm_typ_id = p_cm_typ_id
4166: and ctu.all_r_any_cd = 'ALL';
4206: p_effective_date)) then
4207: --
4208: -- create usage
4209: --
4210: ben_generate_communications.pop_ben_per_cm_usg_f
4211: (p_per_cm_id => p_per_cm_id,
4212: p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
4213: p_business_group_id => p_business_group_id,
4214: p_effective_date => p_effective_date,
4815: (p_proc_cd = 'HPADPNTLC') then
4816: --
4817: savepoint dependent_records;
4818: --
4819: ben_generate_communications.pop_ben_per_cm_f
4820: (p_person_id => l_con_rec(l_count).contact_person_id,
4821: p_ler_id => p_ler_id,
4822: -- CWB Changes
4823: -- p_per_in_ler_id => nvl(p_per_in_ler_id, l_pil_rec.per_in_ler_id),
5134: rollback to dependent_records;
5135: --
5136: else
5137: --
5138: ben_generate_communications.populate_working_tables
5139: (p_person_id => l_con_rec(l_count).contact_person_id,
5140: p_cm_typ_id => p_cm_typ_id,
5141: p_business_group_id => p_business_group_id,
5142: p_effective_date => p_effective_date,
5149: fnd_message.set_name('BEN','BEN_92090_CREATED_DPNT_COMM');
5150: fnd_message.set_token('COMMUNICATION',p_name);
5151: if fnd_global.conc_request_id <> -1 then
5152: benutils.write(fnd_message.get);
5153: benutils.write(ben_generate_communications.g_commu_rec);
5154: ben_generate_communications.g_comm_generated := true;
5155: end if;
5156: --
5157: hr_utility.set_location('Creating Communication for Dependent',10);
5150: fnd_message.set_token('COMMUNICATION',p_name);
5151: if fnd_global.conc_request_id <> -1 then
5152: benutils.write(fnd_message.get);
5153: benutils.write(ben_generate_communications.g_commu_rec);
5154: ben_generate_communications.g_comm_generated := true;
5155: end if;
5156: --
5157: hr_utility.set_location('Creating Communication for Dependent',10);
5158: --