[Home] [Help]
2887:
2888: -- Bug 9576503 : Use join with fa_fiscal_year to make sure of same year
2889: select fcp2.period_num + 1 - fcp1.period_num, fcp3.end_date + 1 - fcp3.start_date
2890: into h_ret_pd_nums, h_tot_days_in_mon
2891: from fa_calendar_periods fcp1,
2892: fa_calendar_periods fcp2,
2893: fa_calendar_periods fcp3,
2894: fa_fiscal_year fy
2895: where fcp1.calendar_type = p_cal
2888: -- Bug 9576503 : Use join with fa_fiscal_year to make sure of same year
2889: select fcp2.period_num + 1 - fcp1.period_num, fcp3.end_date + 1 - fcp3.start_date
2890: into h_ret_pd_nums, h_tot_days_in_mon
2891: from fa_calendar_periods fcp1,
2892: fa_calendar_periods fcp2,
2893: fa_calendar_periods fcp3,
2894: fa_fiscal_year fy
2895: where fcp1.calendar_type = p_cal
2896: and bk.ret_prorate_date between fcp1.start_date and fcp1.end_date
2889: select fcp2.period_num + 1 - fcp1.period_num, fcp3.end_date + 1 - fcp3.start_date
2890: into h_ret_pd_nums, h_tot_days_in_mon
2891: from fa_calendar_periods fcp1,
2892: fa_calendar_periods fcp2,
2893: fa_calendar_periods fcp3,
2894: fa_fiscal_year fy
2895: where fcp1.calendar_type = p_cal
2896: and bk.ret_prorate_date between fcp1.start_date and fcp1.end_date
2897: and fcp2.calendar_type = fcp1.calendar_type
2907: -- Bug 9311291 : Need to make sure that fiscal year is also same
2908: -- Bug 9411825 : Removed the join between cp.start_date and bk.ret_prorate_date
2909: select cp.end_date + 1 - cp.start_date
2910: into h_tot_days_in_mon
2911: from fa_calendar_periods cp,
2912: fa_fiscal_year fy
2913: where cp.calendar_type = d_cal
2914: and cp.period_num = h_i
2915: and fy.fiscal_year_name = h_fiscal_year_name
3120: if p_pers_per_yr = 365 then
3121: if h_i = h_start_pdnum then
3122: select fcp2.period_num + 1 - fcp1.period_num, fcp3.end_date + 1 - fcp3.start_date
3123: into h_ret_pd_nums, h_tot_days_in_mon
3124: from fa_calendar_periods fcp1,
3125: fa_calendar_periods fcp2,
3126: fa_calendar_periods fcp3,
3127: fa_fiscal_year ffy1,
3128: fa_fiscal_year ffy3
3121: if h_i = h_start_pdnum then
3122: select fcp2.period_num + 1 - fcp1.period_num, fcp3.end_date + 1 - fcp3.start_date
3123: into h_ret_pd_nums, h_tot_days_in_mon
3124: from fa_calendar_periods fcp1,
3125: fa_calendar_periods fcp2,
3126: fa_calendar_periods fcp3,
3127: fa_fiscal_year ffy1,
3128: fa_fiscal_year ffy3
3129: where fcp1.calendar_type = p_cal
3122: select fcp2.period_num + 1 - fcp1.period_num, fcp3.end_date + 1 - fcp3.start_date
3123: into h_ret_pd_nums, h_tot_days_in_mon
3124: from fa_calendar_periods fcp1,
3125: fa_calendar_periods fcp2,
3126: fa_calendar_periods fcp3,
3127: fa_fiscal_year ffy1,
3128: fa_fiscal_year ffy3
3129: where fcp1.calendar_type = p_cal
3130: and bk.ret_prorate_date between fcp1.start_date and fcp1.end_date
3141: end if;
3142:
3143: select cp.end_date + 1 - cp.start_date
3144: into h_tot_days_in_mon
3145: from fa_calendar_periods cp,
3146: fa_fiscal_year fy
3147: where cp.calendar_type = d_cal
3148: and cp.period_num = h_i
3149: and fy.fiscal_year_name = h_fiscal_year_name
3304: select ( cp1.period_num) ,( cp2.period_num)
3305: into h_new_ret_pn,h_old_ret_pn
3306: from fa_retirements new_ret
3307: ,fa_conventions conv
3308: ,fa_calendar_periods cp1
3309: ,fa_retirements old_ret
3310: ,fa_conventions conv2
3311: ,fa_calendar_periods cp2
3312: where new_ret.transaction_header_id_in=ret.th_id_in
3307: ,fa_conventions conv
3308: ,fa_calendar_periods cp1
3309: ,fa_retirements old_ret
3310: ,fa_conventions conv2
3311: ,fa_calendar_periods cp2
3312: where new_ret.transaction_header_id_in=ret.th_id_in
3313: and conv.prorate_convention_code=new_ret.retirement_prorate_convention
3314: and new_ret.date_retired between conv.start_date and conv.end_date
3315: and cp1.calendar_type = d_cal
3354: BEGIN
3355:
3356: select h_pc - (fy1.fiscal_year * pds_per_year + cp1.period_num)
3357: into l_num_d_periods
3358: from fa_calendar_periods cp1
3359: , fa_fiscal_year fy1
3360: where cp1.calendar_type = d_cal
3361: and bk.prorate_date between cp1.start_Date and cp1.end_date
3362: and fy1.fiscal_year_name = h_fiscal_year_name
4221: /* Bug#4605961 */
4222: begin
4223: SELECT pcal.period_num
4224: INTO h_start_pd_endpp
4225: FROM fa_calendar_periods pcal,
4226: fa_deprn_periods dp
4227: WHERE calendar_type = p_cal
4228: AND dp.book_type_code = h_book
4229: AND dp.fiscal_year = h_current_fiscal_yr
4585: if bk.fully_reserved then
4586: begin
4587: select pcal.period_num
4588: into h_fully_rsv_pd
4589: from fa_calendar_periods pcal,
4590: fa_deprn_periods dp
4591: where calendar_type = p_cal
4592: and dp.book_type_code = h_book
4593: and dp.fiscal_year = h_current_fiscal_yr
5354:
5355: begin
5356: SELECT cp.period_num
5357: INTO h_work_pdnum
5358: FROM fa_calendar_periods cp
5359: WHERE h_ret_p_date
5360: between cp.start_date and cp.end_date
5361: AND cp.calendar_type = h_d_cal;
5362: EXCEPTION
5407: */
5408:
5409: SELECT to_number (to_char (dcp.start_date, 'J'))
5410: INTO h_cpp_jstartdate
5411: FROM fa_calendar_periods dcp,
5412: fa_fiscal_year fy,
5413: fa_book_controls bc
5414: WHERE bc.book_type_code = h_book
5415: AND bc.fiscal_year_name = fy.fiscal_year_name
5430: BEGIN
5431: SELECT to_number (to_char (bk.prorate_date, 'J'))
5432: INTO h_dpis_pr_jdt
5433: FROM fa_books bk,
5434: fa_calendar_periods dcp
5435: WHERE bk.book_type_code = ret.book
5436: AND transaction_header_id_out is null
5437: AND asset_id = ret.asset_id
5438: AND dcp.calendar_type = h_d_cal
5451:
5452:
5453: SELECT period_num
5454: INTO h_startpp
5455: FROM fa_calendar_periods
5456: WHERE calendar_type = h_p_cal
5457: AND to_date (h_cpp_jstartdate,'J')
5458: between start_date and end_date;
5459:
5465: AND dp.period_num = h_stop_pdnum;
5466:
5467: SELECT period_num
5468: INTO h_endpp
5469: FROM fa_calendar_periods
5470: WHERE calendar_type = h_p_cal
5471: AND to_date (h_cpp_jenddate,'J')
5472: between start_date and end_date;
5473: