1 PACKAGE BODY FA_AMORT_VAL_PKG as
2 /* $Header: FAAMRTVB.pls 120.3 2005/07/25 10:04:50 yyoon ship $ */
3
4 -- this function validates amortization start date entered by user
5 -- if there is any retirement/reinstatement/revaluation
6 -- after the amortization start date then new_amort_start_date
7 -- will be populated with same date as transaction date of retire/reinstate/reval
8 -- calling program will use new amortization start date as amortization date
9 -- if user choose to use it.
10 FUNCTION val_amort_date(x_amort_start_date date,
11 x_new_amort_start_date out nocopy date,
12 x_book varchar2,
13 x_asset_id number,
14 x_dpis date,
15 x_txns_exist in out nocopy varchar2,
16 x_err_code out nocopy varchar2,
17 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
18 return boolean is
19
20 h_transaction_date date;
21 h_period_close_date date;
22 h_period_open_date date;
23 h_prior_transaction_date date;
24 h_prior_date_effective date;
25 h_amort_date date;
26 h_avail_date date;
27 h_count number;
28 dpis_jdate number;
29 amort_jdate number;
30 dpis_fy number;
31 amort_fy number;
32 dpis_per_num number;
33 amort_per_num number;
34 fy_name varchar2(45);
35 cal_type varchar2(15);
36 start_jdate number;
37
38 CURSOR current_period_info IS
39 select greatest(calendar_period_open_date,
40 least(sysdate, calendar_period_close_date)),
41 calendar_period_close_date, calendar_period_open_date
42 from fa_deprn_periods
43 where book_type_code = x_book
44 and period_close_date is null;
45
46 begin
47
48 x_new_amort_start_date := x_amort_start_date;
49 x_txns_exist := 'N'; -- sets to Y if any txn exist between current period
50 -- and amortization period
51
52 -- x_amort_start_date cannot be future period
53
54 open current_period_info;
55 fetch current_period_info
56 into h_transaction_date, h_period_close_date, h_period_open_date;
57 close current_period_info;
58
59 if (x_amort_start_date > h_period_close_date) then
60 x_err_code := 'FA_SHARED_CANNOT_FUTURE';
61 return FALSE;
62 end if;
63
64 -- x_amort_start_date cannot be less than DPIS
65
66 if (x_amort_start_date < x_dpis) then
67 x_new_amort_start_date := x_dpis;
68 x_txns_exist := 'Y';
69 --x_err_code := 'FA_AMORT_DATE_INVALID';
70 --return FALSE;
71 end if;
72
73 select fiscal_year_name,deprn_calendar
74 into fy_name, cal_type
75 from fa_book_controls
76 where book_type_code = x_book;
77
78 /* this check is already done in the books form
79
80 -- checks if dpis is valid dpis
81 dpis_jdate := to_number(to_char(x_dpis,'J'));
82 if (not fa_cache_pkg.fazccp(cal_type,fy_name,dpis_jdate,
83 dpis_per_num,dpis_fy,start_jdate
84 ,p_log_level_rec => p_log_level_rec)) then
85 x_err_code := 'FA_PROD_INCORRECT_DATE';
86 return FALSE;
87 end if;
88 */
89
90 -- checks if amort start date is valid
91 amort_jdate := to_number(to_char(x_new_amort_start_date,'J'));
92 if (not fa_cache_pkg.fazccp(cal_type,fy_name,amort_jdate,
93 amort_per_num,amort_fy,start_jdate
94 ,p_log_level_rec => p_log_level_rec)) then
95 x_err_code := 'FA_PROD_INCORRECT_DATE';
96 return FALSE;
97 end if;
98
99 /*
100 if (amort_fy = dpis_fy) then
101 if (amort_per_num < dpis_per_num) then
102 x_err_code := 'FA_AMORT_DATE_INVALID';
103 return FALSE;
104 end if;
105 else
106 if (amort_fy < dpis_fy) then
107 x_err_code := 'FA_AMORT_DATE_INVALID';
108 return FALSE;
109 end if;
110 end if;
111 */
112
113
114 -- check if amort start date is eariler than
115 -- previous txn date, set txns_exist
116
117 select MAX(transaction_date_entered),MAX(date_effective)
118 into h_prior_transaction_date,h_prior_date_effective
119 from fa_transaction_headers
120 where asset_id = x_asset_id
121 and book_type_code = x_book;
122
123 if (x_new_amort_start_date < h_prior_transaction_date) then
124 --x_err_code := 'FA_SHARED_OTHER_TRX_FOLLOW';
125 x_txns_exist := 'Y';
126 end if;
127
128
129 select count(*)
130 into h_count
131 from fa_deprn_periods pdp, fa_deprn_periods adp
132 where pdp.book_type_code = x_book
133 and pdp.book_type_code = adp.book_type_code
134 and pdp.period_counter > adp.period_counter
135 and h_prior_date_effective between pdp.period_open_date
136 and nvl(pdp.period_close_date, to_date('31-12-4712','DD-MM-YYYY'))
137 and x_new_amort_start_date between
138 adp.calendar_period_open_date and adp.calendar_period_close_date;
139
140 if (h_count > 0) then
141 --x_err_code := 'FA_SHARED_OTHER_TRX_FOLLOW';
142 x_txns_exist := 'Y';
143 end if;
144
145 -- check to see if any retire/reinstate/reval txn is in between
146 -- x_new_amort_start_date and current_period.
147 -- this check covers for the prior period retire/reinste/reval
148 -- set x_new_amort_start_date := latest txn date
149
150 select MAX(date_effective)
151 into h_prior_date_effective
152 from fa_transaction_headers
153 where asset_id = x_asset_id
154 and book_type_code = x_book
155 and transaction_type_code in ('REVALUATION');
156
157 if (h_prior_date_effective is not null) then
158
159 -- get the latest available date
160 select greatest(calendar_period_open_date,
161 least(SYSDATE, calendar_period_close_date))
162 into h_amort_date
163 from fa_deprn_periods
164 where book_type_code = X_book
165 and h_prior_date_effective between
166 period_open_date and
167 nvl(period_close_date,sysdate);
168
169 if (x_new_amort_start_date < h_amort_date) then
170 x_new_amort_start_date := h_amort_date;
171 end if;
172 end if;
173
174 return TRUE;
175
176 exception
177 when others then
178 x_err_code := sqlcode;
179 return FALSE;
180 end;
181
182 END FA_AMORT_VAL_PKG;
[Home] [Help]