1 PACKAGE BODY FA_REC_PVT_PKG2 AS
2 /* $Header: FAXVRC2B.pls 120.5 2010/01/18 11:00:30 mswetha ship $ */
3
4 --
5 -- FUNCTION Validate_Redefault
6 --
7
8 FUNCTION Validate_Redefault(
9 p_asset_id IN NUMBER,
10 p_new_category_id IN NUMBER,
11 p_book_type_code IN VARCHAR2,
12 p_amortize_flag IN VARCHAR2 := 'NO',
13 p_mr_req_id IN NUMBER := -1,
14 x_rule_change_exists OUT NOCOPY BOOLEAN,
15 x_old_rules OUT NOCOPY FA_LOAD_TBL_PKG.asset_deprn_info,
16 x_new_rules OUT NOCOPY FA_LOAD_TBL_PKG.asset_deprn_info,
17 x_use_rules OUT NOCOPY BOOLEAN,
18 x_prorate_date OUT NOCOPY DATE,
19 x_rate_source_rule OUT NOCOPY VARCHAR2,
20 x_deprn_basis_rule OUT NOCOPY VARCHAR2
21 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
22 l_old_rules FA_LOAD_TBL_PKG.asset_deprn_info;
23 l_new_rules FA_LOAD_TBL_PKG.asset_deprn_info;
24 l_found BOOLEAN := FALSE;
25 CURSOR get_old_rules IS
26 SELECT book_type_code,
27 date_placed_in_service, date_placed_in_service,
28 prorate_convention_code, deprn_method_code,
29 life_in_months, basic_rate, adjusted_rate,
30 production_capacity, unit_of_measure,
31 bonus_rule, NULL, ceiling_name,
32 depreciate_flag, allowed_deprn_limit,
33 allowed_deprn_limit_amount, percent_salvage_value
34 FROM FA_BOOKS
35 WHERE book_type_code = p_book_type_code
36 AND asset_id = p_asset_id
37 AND date_ineffective IS NULL;
38 BEGIN
39
40 ---- Prepare for validations ----
41
42 /* Get old(current) depreciation rules from the current books row. */
43 OPEN get_old_rules;
44 FETCH get_old_rules INTO l_old_rules;
45 CLOSE get_old_rules;
46
47 /* Get new depreciation rules. */
48 FA_LOAD_TBL_PKG.Get_Deprn_Rules(
49 p_book_type_code => p_book_type_code,
50 p_date_placed_in_service => l_old_rules.start_dpis,
51 x_deprn_rules_rec => l_new_rules,
52 x_found => l_found, p_log_level_rec => p_log_level_rec);
53 IF not l_found THEN
54 FA_SRVR_MSG.Add_Message(
55 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Redefault',
56 NAME => 'FA_REC_NO_CAT_DEFAULTS', p_log_level_rec => p_log_level_rec);
57 /* Message text:
58 'The new category default depreciation rules do not
59 exist for this asset and its date placed in service.' */
60 RETURN (FALSE);
61 END IF;
62
63 /* We will not redefault depreciate_flag through mass reclass. */
64 l_new_rules.depreciate_flag := l_old_rules.depreciate_flag;
65
66 /* See if any rule change is needed. */
67 IF NOT ((l_old_rules.prorate_conv_code = l_new_rules.prorate_conv_code) AND
68 (l_old_rules.deprn_method = l_new_rules.deprn_method) AND
69 (nvl(l_old_rules.life_in_months, 99999) =
70 nvl(l_new_rules.life_in_months, 99999)) AND
71 (nvl(l_old_rules.basic_rate, 99999) =
72 nvl(l_new_rules.basic_rate, 99999)) AND
73 (nvl(l_old_rules.adjusted_rate, 99999) =
74 nvl(l_new_rules.adjusted_rate, 99999)) AND
75 (nvl(l_old_rules.production_capacity, 99999) =
76 nvl(l_new_rules.production_capacity, 99999)) AND
77 (nvl(l_old_rules.unit_of_measure, 99999) =
78 nvl(l_new_rules.unit_of_measure, 99999)) AND
79 (nvl(l_old_rules.bonus_rule, 'NULL') =
80 nvl(l_new_rules.bonus_rule, 'NULL')) AND
81 (nvl(l_old_rules.ceiling_name, 'NULL') =
82 nvl(l_new_rules.ceiling_name, 'NULL')) AND
83 /* Skip this check -- we will not change depreciate flag through mass reclass.
84 (l_old_rules.depreciate_flag = l_new_rules.depreciate_flag) AND
85 */
86 (nvl(l_old_rules.allow_deprn_limit, 99) =
87 nvl(l_new_rules.allow_deprn_limit, 99)) AND
88 (nvl(to_char(l_old_rules.deprn_limit_amount), 'NULL') =
89 nvl(to_char(l_new_rules.deprn_limit_amount), 'NULL')) AND
90 (nvl(l_old_rules.percent_salvage_value, 99) =
91 nvl(l_new_rules.percent_salvage_value, 99)))
92 THEN
93 x_rule_change_exists := TRUE; -- At least one change is needed.
94 ELSE
95 x_rule_change_exists := FALSE;
96 RETURN (TRUE);
97 -- No rule change is needed. Skip the validations.
98 END IF;
99
100 ---- Validations -----
101
102 /* Validate an adjustment transaction. */
103 IF NOT Validate_Adjustment(
104 p_asset_id => p_asset_id,
105 p_book_type_code => p_book_type_code,
106 p_amortize_flag => p_amortize_flag,
107 p_mr_req_id => p_mr_req_id,
108 p_log_level_rec => p_log_level_rec)
109 THEN
110 FA_SRVR_MSG.Add_Message(
111 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Redefault', p_log_level_rec => p_log_level_rec);
112 RETURN (FALSE);
113 END IF;
114
115 /* Validate depreciation rule changes. */
116 IF NOT FA_REC_PVT_PKG3.Validate_Rule_Changes(
117 p_asset_id => p_asset_id,
118 p_new_category_id => p_new_category_id,
119 p_book_type_code => p_book_type_code,
120 p_amortize_flag => p_amortize_flag,
121 p_old_rules => l_old_rules,
122 p_new_rules => l_new_rules,
123 x_prorate_date => x_prorate_date,
124 x_rate_source_rule => x_rate_source_rule,
125 x_deprn_basis_rule => x_deprn_basis_rule,
126 p_log_level_rec => p_log_level_rec
127 )
128 THEN
129 FA_SRVR_MSG.Add_Message(
130 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Redefault', p_log_level_rec => p_log_level_rec);
131 RETURN (FALSE);
132 END IF;
133
134 x_old_rules := l_old_rules;
135 x_new_rules := l_new_rules;
136 x_use_rules := TRUE;
137 RETURN (TRUE);
138 EXCEPTION
139 WHEN OTHERS THEN
140 x_use_rules := FALSE;
141 FA_SRVR_MSG.Add_SQL_Error(
142 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Redefault', p_log_level_rec => p_log_level_rec);
143 RETURN (FALSE);
144 END Validate_Redefault;
145
146
147 --
148 -- FUNCTION Validate_Adjustment
149 --
150
151 FUNCTION Validate_Adjustment(
152 p_asset_id IN NUMBER,
153 p_book_type_code IN VARCHAR2,
154 p_amortize_flag IN VARCHAR2,
155 p_mr_req_id IN NUMBER := -1
156 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
157 check_flag VARCHAR2(3);
158 prior_transaction_date DATE;
159 prior_date_effective DATE;
160 check_another_trans NUMBER;
161 CURSOR check_mass_change_allowed IS
162 SELECT 'x' FROM FA_BOOK_CONTROLS
163 WHERE book_type_code = p_book_type_code AND allow_mass_changes = 'NO'
164 AND p_mr_req_id <> -1;
165 CURSOR check_prior_amort IS
166 SELECT 'x' FROM FA_BOOKS
167 WHERE book_type_code = p_book_type_code AND asset_id = p_asset_id
168 AND rate_adjustment_factor <> 1;
169 CURSOR check_cip IS
170 SELECT 'x' FROM FA_ADDITIONS
171 WHERE asset_id = p_asset_id AND asset_type = 'CIP';
172 CURSOR check_amort_allowed IS
173 SELECT 'x' FROM FA_BOOK_CONTROLS
174 WHERE book_type_code = p_book_type_code AND amortize_flag = 'NO';
175 CURSOR check_deprn IS
176 SELECT 'x' FROM FA_DEPRN_SUMMARY
177 WHERE book_type_code = p_book_type_code AND asset_id = p_asset_id
178 AND deprn_source_code = 'DEPRN'; --Bug#8969958
179 BEGIN
180 -- Check if mass change(adjustment) is allowed.
181 OPEN check_mass_change_allowed;
182 FETCH check_mass_change_allowed INTO check_flag;
183 IF (check_mass_change_allowed%found) THEN
184 CLOSE check_mass_change_allowed;
185 FA_SRVR_MSG.Add_Message(
186 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Adjustment',
187 NAME => 'FA_REC_MASSCHG_NOT_ALLOWED', p_log_level_rec => p_log_level_rec);
188 /* Message text:
189 'You cannot use mass transaction to redefault depreciation rules
190 for the asset in this book because Allow Mass Changes field in the
191 Book Controls form is set to No. */
192 RETURN (FALSE);
193 END IF;
194 CLOSE check_mass_change_allowed;
195
196 ---- Checks for Expensed Adjustment ----
197
198 /* Check if there were prior amortized adjustments, in case of expensed
199 adjustment. */
200 IF (p_amortize_flag = 'NO') THEN
201 OPEN check_prior_amort;
202 FETCH check_prior_amort INTO check_flag;
203 IF (check_prior_amort%found) THEN
204 CLOSE check_prior_amort;
205 FA_SRVR_MSG.Add_Message(
206 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Adjustment',
207 NAME => 'FA_BOOK_CANT_EXP_AFTER_AMORT', p_log_level_rec => p_log_level_rec);
208 RETURN (FALSE);
209 END IF;
210 CLOSE check_prior_amort;
211
212 ---- Checks for Amortized Adjustment ----
213
214 ELSE
215 /* Check if an asset is a CIP asset. CIP assets cannot be amortized.
216 Rule from fa_fin_adj3_pkg.transaction_type(). */
217 OPEN check_cip;
218 FETCH check_cip INTO check_flag;
219 IF (check_cip%found) THEN
220 CLOSE check_cip;
221 FA_SRVR_MSG.Add_Message(
222 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Adjustment',
223 NAME => 'FA_REC_CIP_CANNOT_AMORT', p_log_level_rec => p_log_level_rec);
224 /* Message text:
225 'You cannot amortize an adjustment for a CIP asset.' */
226 RETURN (FALSE);
227 END IF;
228 CLOSE check_cip;
229
230 /* Check if amortization is allowed in this book. */
231 OPEN check_amort_allowed;
232 FETCH check_amort_allowed INTO check_flag;
233 IF (check_amort_allowed%found) THEN
234 CLOSE check_amort_allowed;
235 FA_SRVR_MSG.Add_Message(
236 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Adjustment',
237 NAME => 'FA_BOOK_AMORTIZED_NOT_ALLOW', p_log_level_rec => p_log_level_rec);
238 RETURN (FALSE);
239 END IF;
240 CLOSE check_amort_allowed;
241
242 /* Check if the asset has already been depreciated. */
243 OPEN check_deprn;
244 FETCH check_deprn INTO check_flag;
245 IF (check_deprn%notfound) THEN
246 CLOSE check_deprn;
247 FA_SRVR_MSG.Add_Message(
248 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Adjustment',
249 NAME => 'FA_BOOK_CANT_AMORT_BEF_DEPRN', p_log_level_rec => p_log_level_rec);
250 RETURN (FALSE);
251 END IF;
252 CLOSE check_deprn;
253
254 END IF;
255
256 RETURN (TRUE);
257 EXCEPTION
258 WHEN OTHERS THEN
259 FA_SRVR_MSG.Add_SQL_Error(
260 CALLING_FN => 'FA_REC_PVT_PKG2.Validate_Adjustment', p_log_level_rec => p_log_level_rec);
261 RETURN (FALSE);
262 END Validate_Adjustment;
263
264
265 END FA_REC_PVT_PKG2;