[Home] [Help]
137: annual_to_period
138: 21-Jun-04 bmanyam 115.62 Bug# 3704632 - Added NVL() to p_end_date parameter
139: in the cursot to find number_of_periods in function get_periods_between().
140: 22-Jun-04 bmanyam 115.63 Bug# 3704632 - Removed the above change. Added
141: select clause to fetch end-date of pay-year from per_time_periods
142: for 'Calender Month' and 'Lunar Month'
143: 29-Nov-04 kmahendr 115.64 Codes added for new Rate Start date
144: 03-Dec-04 vvprabhu 115.65 Bug 3980063 SSBEN Trace Enhancement
145: 21-Dec-04 kmahendr 115.66 Bug#4037102 - nvl used to pass start date
442: v_start_date in date,
443: v_end_date in date) is
444: select ptp.start_date,
445: ptp.end_date
446: from per_time_periods ptp
447: where ptp.payroll_id = v_payroll_id
448: and ptp.end_date between
449: v_start_date and v_end_date;
450: --
478: select sum(
479: ben_distribute_rates.decde_bits(
480: bitand(power(2,count(end_date )) -1,p_frq_bitmap_no)
481: ))
482: from per_time_periods
483: where payroll_id = p_payroll_id
484: and --end_date -- Bug 6830210
485: regular_payment_date -- Bug 6830210
486: between p_rt_start
491: cursor c_count_periods(v_payroll_id in number,
492: v_start_date in date,
493: v_end_date in date) is
494: select count(1)
495: from per_time_periods ptp
496: where ptp.payroll_id = v_payroll_id
497: -- and nvl(ptp.regular_payment_date,ptp.end_date) between -- 13067064
498: and ptp.end_date between
499: v_start_date and v_end_date;
621: -- 3704632 : Added IF-CLAUSE here.
622: if (p_end_date is null) THEN
623: select MAX(end_date)
624: into l_max_end_date
625: from per_time_periods
626: where payroll_id = p_payroll_id
627: and TO_CHAR(end_date,'YYYY') =
628: (SELECT TO_CHAR(end_date,'YYYY')
629: from per_time_periods
625: from per_time_periods
626: where payroll_id = p_payroll_id
627: and TO_CHAR(end_date,'YYYY') =
628: (SELECT TO_CHAR(end_date,'YYYY')
629: from per_time_periods
630: where payroll_id = p_payroll_id
631: and p_start_date between start_date and end_date
632: );
633: END IF;
634: -- 3704632 : End changes
635:
636: select count(*)
637: into l_periods
638: from per_time_periods
639: where payroll_id = p_payroll_id
640: and end_date between p_start_date and NVL(p_end_date,l_max_end_date); -- 3704632 : Added NVL() here.
641:
642: else
1087: v_start_date in date,
1088: v_end_date in date) is
1089: select ptp.start_date,
1090: ptp.end_date
1091: from per_time_periods ptp
1092: where ptp.payroll_id = v_payroll_id
1093: and ptp.end_date between
1094: v_start_date and v_end_date;
1095: --
1125: select sum(
1126: ben_distribute_rates.decde_bits(
1127: bitand(power(2,count(end_date )) -1,p_frq_bitmap_no)
1128: ))
1129: from per_time_periods
1130: where payroll_id = p_payroll_id
1131: and --end_date -- Bug 6830210
1132: regular_payment_date -- Bug 6830210
1133: between p_rt_start
1138: cursor c_count_periods(v_payroll_id in number,
1139: v_start_date in date,
1140: v_end_date in date) is
1141: select count(1)
1142: from per_time_periods ptp
1143: where ptp.payroll_id = v_payroll_id
1144: and nvl(ptp.regular_payment_date,ptp.end_date) between -- 13067064
1145: -- and ptp.end_date between
1146: v_start_date and v_end_date
1153: cursor c_count_periods_chq(v_payroll_id in number,
1154: v_start_date in date,
1155: v_end_date in date) is
1156: select count(1)
1157: from per_time_periods ptp
1158: where ptp.payroll_id = v_payroll_id
1159: -- and ptp.end_date between
1160: -- v_start_date and v_end_date
1161: -- Bug 6455096
1161: -- Bug 6455096
1162: -- and ptp.end_date >= v_start_date
1163: -- bug 7314120
1164: and ptp.regular_payment_date >= (select regular_payment_date
1165: from per_time_periods
1166: where payroll_id = v_payroll_id
1167: and v_start_date between start_date
1168: and end_date)--v_start_date
1169: -- bug 7314120
1176: cursor c_get_period_num(p_payroll_id in number,
1177: p_yr_start_date in date, -- 10058794
1178: p_end_date in date) is
1179: select count(1) -- 10058794
1180: from per_time_periods ptp
1181: where ptp.payroll_id = p_payroll_id
1182: -- 10058794
1183: and ptp.regular_payment_date
1184: between p_yr_start_date
1324: else
1325: --
1326: select count(*)
1327: into l_periods
1328: from per_time_periods
1329: where payroll_id = p_payroll_id
1330: and end_date
1331: between p_start_date
1332: and p_end_date;
2336: cursor c_parse_periods(v_payroll_id in number,
2337: v_start_date in date,
2338: v_end_date in date ) is
2339: select min(ptp.start_date)
2340: from per_time_periods ptp
2341: where ptp.payroll_id = v_payroll_id
2342: and ptp.start_date between
2343: v_start_date and v_end_date;
2344: --
3417: --
3418: cursor c_get_check_date(p_payroll_id in number,
3419: p_start_date in date) is
3420: select ptp.regular_payment_date
3421: from per_time_periods ptp
3422: where ptp.payroll_id = p_payroll_id
3423: and p_start_date
3424: between ptp.start_date
3425: and ptp.end_date;
3427: cursor c_get_period_start_date(p_payroll_id in number
3428: ,p_yr_start_date in date
3429: ) is
3430: select ptp.start_date
3431: from per_time_periods ptp
3432: where ptp.payroll_id = p_payroll_id
3433: and ptp.regular_payment_date >= p_yr_start_date
3434: order by ptp.regular_payment_date;
3435: --
3827: --
3828: cursor c_get_check_date(p_payroll_id in number,
3829: p_start_date in date) is
3830: select ptp.regular_payment_date
3831: from per_time_periods ptp
3832: where ptp.payroll_id = p_payroll_id
3833: and p_start_date
3834: between ptp.start_date
3835: and ptp.end_date;
3837: cursor c_get_period_start_date(p_payroll_id in number
3838: ,p_yr_start_date in date
3839: ) is
3840: select ptp.start_date
3841: from per_time_periods ptp
3842: where ptp.payroll_id = p_payroll_id
3843: and ptp.regular_payment_date >= p_yr_start_date
3844: order by ptp.regular_payment_date;
3845: --
4760: cursor c_check_date(p_payroll_id number,
4761: p_date date)
4762: is
4763: SELECT prd.regular_payment_date
4764: FROM per_time_periods prd
4765: WHERE prd.payroll_id = p_payroll_id
4766: AND p_date BETWEEN prd.start_date
4767: AND prd.end_date;
4768: l_check_date date;