[Home] [Help]
135: 23-May-02 kmahendr 115.46 Added a parameter to ben_determine_acty_base_rt
136: 10-jun-02 pabodla 115.47 LGE : Create rate certifications
137: 28-Sep-02 ikasire 115.49 reverted the changes made in 115.48
138: 11-Oct-02 vsethi 115.50 Rates Sequence no enhancements. Modified to cater
139: to new column ord_num on ben_acty_base_rt_f
140: 28-Oct-02 kmahendr 115.51 Land O Lakes performance fix - cursor c_enrt_rt
141: split into two.
142: 26-Dec-02 rpillay 115.52 NOCOPY changes
143: 13-feb-02 vsethi 115.53 Enclosed all hr_utility debug calls inside if
197: g_rec benutils.g_batch_rate_rec;
198: --
199: g_debug boolean := hr_utility.debug_enabled;
200: --
201: TYPE g_acty_base_rt_id_table IS TABLE OF ben_acty_base_rt_f.acty_base_rt_id%TYPE
202: INDEX BY BINARY_INTEGER;
203: TYPE g_asn_on_enrt_flag_table IS TABLE OF ben_acty_base_rt_f.asn_on_enrt_flag%TYPE
204: INDEX BY BINARY_INTEGER;
205: --
199: g_debug boolean := hr_utility.debug_enabled;
200: --
201: TYPE g_acty_base_rt_id_table IS TABLE OF ben_acty_base_rt_f.acty_base_rt_id%TYPE
202: INDEX BY BINARY_INTEGER;
203: TYPE g_asn_on_enrt_flag_table IS TABLE OF ben_acty_base_rt_f.asn_on_enrt_flag%TYPE
204: INDEX BY BINARY_INTEGER;
205: --
206: -- ----------------------------------------------------------------------------
207: -- |------< ben_rates_old >------|
503: abr.entr_ann_val_flag,
504: abr.currency_det_cd,
505: abr.element_det_rl,
506: abr.element_type_id
507: from ben_acty_base_rt_f abr
508: where abr.acty_base_rt_id = p_acty_base_rt_id
509: and p_effective_date between abr.effective_start_date and abr.effective_end_date;
510:
511: l_env ben_env_object.g_global_env_rec_type;
726: l_ordr_num(i) := NULL;
727: l_iss_val(i) := NULL;
728: --
729: -- Fetch acty_typ_cd and other not-null values
730: -- from ben_acty_base_rt_f table.
731: open c_abr(p_acty_base_rt_id_table(i));
732: fetch c_abr into l_abr;
733: if c_abr%FOUND then
734: l_acty_typ_cd(i) := l_abr.acty_typ_cd;
1812: CURSOR c_abr_oiplip(
1813: c_effective_date DATE) IS
1814: SELECT abr.acty_base_rt_id,
1815: abr.asn_on_enrt_flag
1816: FROM ben_acty_base_rt_f abr
1817: WHERE abr.oiplip_id = l_oiplip_id
1818: AND abr.rt_usg_cd <> 'FLXCR'
1819: AND abr.acty_base_rt_stat_cd = 'A'
1820: AND c_effective_date BETWEEN abr.effective_start_date
1823: CURSOR c_abr_oipl(
1824: c_effective_date DATE) IS
1825: SELECT abr.acty_base_rt_id,
1826: abr.asn_on_enrt_flag
1827: FROM ben_acty_base_rt_f abr
1828: WHERE abr.acty_base_rt_stat_cd = 'A'
1829: AND abr.oipl_id = l_oipl_id
1830: AND abr.rt_usg_cd <> 'FLXCR'
1831: AND c_effective_date BETWEEN abr.effective_start_date
1835: CURSOR c_abr_opt(
1836: c_effective_date DATE) IS
1837: SELECT abr.acty_base_rt_id,
1838: abr.asn_on_enrt_flag
1839: FROM ben_acty_base_rt_f abr
1840: WHERE abr.acty_base_rt_stat_cd = 'A'
1841: AND abr.opt_id = l_opt_id
1842: AND abr.rt_usg_cd <> 'FLXCR'
1843: AND c_effective_date BETWEEN abr.effective_start_date
1847: CURSOR c_abr_plip(
1848: c_effective_date DATE) IS
1849: SELECT abr.acty_base_rt_id,
1850: abr.asn_on_enrt_flag
1851: FROM ben_acty_base_rt_f abr
1852: WHERE abr.acty_base_rt_stat_cd = 'A'
1853: AND abr.plip_id = l_plip_id
1854: AND abr.rt_usg_cd <> 'FLXCR'
1855: AND abr.cmbn_plip_id IS NULL
1859: CURSOR c_abr_pl(
1860: c_effective_date DATE) IS
1861: SELECT abr.acty_base_rt_id,
1862: abr.asn_on_enrt_flag
1863: FROM ben_acty_base_rt_f abr
1864: WHERE abr.acty_base_rt_stat_cd = 'A'
1865: AND abr.pl_id = l_pl_id
1866: AND abr.rt_usg_cd <> 'FLXCR'
1867: AND c_effective_date BETWEEN abr.effective_start_date
1870: CURSOR c_abr_pgm(
1871: c_effective_date DATE) IS
1872: SELECT abr.acty_base_rt_id,
1873: abr.asn_on_enrt_flag
1874: FROM ben_acty_base_rt_f abr
1875: WHERE abr.acty_base_rt_stat_cd = 'A'
1876: AND abr.pgm_id = l_pgm_id
1877: AND c_effective_date BETWEEN abr.effective_start_date
1878: AND abr.effective_end_date
1880: CURSOR c_abr_ptip(
1881: c_effective_date DATE) IS
1882: SELECT abr.acty_base_rt_id,
1883: abr.asn_on_enrt_flag
1884: FROM ben_acty_base_rt_f abr
1885: WHERE abr.acty_base_rt_stat_cd = 'A'
1886: AND abr.ptip_id = l_ptip_id
1887: AND abr.cmbn_ptip_id IS NULL
1888: AND abr.cmbn_ptip_opt_id IS NULL
1892: CURSOR c_abr_cmbn_ptip(
1893: c_effective_date DATE) IS
1894: SELECT abr.acty_base_rt_id,
1895: abr.asn_on_enrt_flag
1896: FROM ben_acty_base_rt_f abr
1897: WHERE abr.acty_base_rt_stat_cd = 'A'
1898: AND abr.cmbn_ptip_id = l_cmbn_ptip_id
1899: AND c_effective_date BETWEEN abr.effective_start_date
1900: AND abr.effective_end_date
1904: CURSOR c_abr_flx_plip(
1905: c_effective_date DATE) IS
1906: SELECT abr.acty_base_rt_id,
1907: abr.asn_on_enrt_flag
1908: FROM ben_acty_base_rt_f abr
1909: WHERE abr.acty_base_rt_stat_cd = 'A'
1910: AND abr.plip_id = l_plip_id
1911: AND abr.rt_usg_cd = 'FLXCR'
1912: AND abr.cmbn_plip_id IS NULL
1916: CURSOR c_abr_cmbn_plip(
1917: c_effective_date DATE) IS
1918: SELECT abr.acty_base_rt_id,
1919: abr.asn_on_enrt_flag
1920: FROM ben_acty_base_rt_f abr
1921: WHERE abr.acty_base_rt_stat_cd = 'A'
1922: AND abr.cmbn_plip_id = l_cmbn_plip_id
1923: AND c_effective_date BETWEEN abr.effective_start_date
1924: AND abr.effective_end_date
1927: CURSOR c_abr_flx_oiplip(
1928: c_effective_date DATE) IS
1929: SELECT abr.acty_base_rt_id,
1930: abr.asn_on_enrt_flag
1931: FROM ben_acty_base_rt_f abr
1932: WHERE abr.oiplip_id = l_oiplip_id
1933: AND abr.rt_usg_cd = 'FLXCR'
1934: AND abr.acty_base_rt_stat_cd = 'A'
1935: AND c_effective_date BETWEEN abr.effective_start_date
1938: CURSOR c_abr_cmbn_ptip_opt(
1939: c_effective_date DATE) IS
1940: SELECT abr.acty_base_rt_id,
1941: abr.asn_on_enrt_flag
1942: FROM ben_acty_base_rt_f abr
1943: WHERE abr.acty_base_rt_stat_cd = 'A'
1944: AND abr.cmbn_ptip_opt_id = l_cmbn_ptip_opt_id
1945: AND c_effective_date BETWEEN abr.effective_start_date
1946: AND abr.effective_end_date