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