[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_FA_CALC_ADJUSTMENT_PKG
Source
1 PACKAGE BODY jl_zz_fa_calc_adjustment_pkg AS
2 /* $Header: jlzzfcab.pls 115.4 2002/11/06 00:09:30 cleyvaol ship $ */
3 ----------------------------------------------------------------------------
4 -- FUNCTION --
5 -- Calc_adjustments --
6 -- --
7 -- DESCRIPTION --
8 -- Use this function to calculate adjustments in view --
9 -- JL_ZZ_FA_ADJ_SUMMARY_V. --
10 -- --
11 -- PURPOSE: --
12 -- Oracle Applications Rel 11.0 --
13 -- --
14 -- PARAMETERS: --
15 -- p_book_type_code - Book Type Code --
16 -- p_period_counter - Period Counter --
17 -- p_asset_id - Asset ID --
18 -- P_adjustment_type - Adjustment Type --
19 -- P_mode - Mode --
20 -- --
21 -- HISTORY: --
22 -- 02/19/99 Sujit Dalai Created --
23 ----------------------------------------------------------------------------
24
25 FUNCTION calc_adjustments
26 ( p_book_type_code VARCHAR2,
27 p_period_counter NUMBER,
28 p_asset_id NUMBER,
29 p_adjustment_type VARCHAR2,
30 p_mode VARCHAR2
31 )
32 RETURN NUMBER AS
33 x_dr_amount NUMBER := 0;
34 x_cr_amount NUMBER := 0;
35
36 BEGIN
37
38 SELECT nvl (sum (decode (aj.debit_credit_flag, 'DR', aj.adjustment_amount, 0)), 0),
39 nvl (sum (decode (aj.debit_credit_flag, 'CR', aj.adjustment_amount, 0)), 0)
40 INTO x_dr_amount,
41 x_cr_amount
42 FROM fa_adjustments aj
43 WHERE aj.asset_id = p_asset_id
44 AND aj.book_type_code = p_book_type_code
45 AND aj.period_counter_created = p_period_counter
46 AND aj.adjustment_type = p_adjustment_type
47 GROUP BY aj.asset_id,
48 aj.book_type_code,
49 aj.period_counter_created,
50 aj.adjustment_type ;
51
52 IF (p_mode = 'DR') THEN
53 RETURN (x_dr_amount);
54 ELSIF (p_mode = 'CR') THEN
55 RETURN (x_cr_amount);
56 ELSIF (p_mode = 'NET') THEN
57 RETURN (x_dr_amount - x_cr_amount);
58 END IF;
59
60 EXCEPTION
61 WHEN others THEN
62 RETURN (0);
63
64 END calc_adjustments;
65
66 ----------------------------------------------------------------------------
67 -- FUNCTION --
68 -- calc_corp_book_cost --
69 -- --
70 -- DESCRIPTION --
71 -- Use this function to calculate --
72 -- JL_ZZ_FA_ADJ_SUMMARY_V. --
73 -- --
74 -- PURPOSE: --
75 -- Oracle Applications Rel 11.0 --
76 -- --
77 -- PARAMETERS: --
78 -- p_book_type_code - Distribution Source Book --
79 -- p_period_counter - Period Counter --
80 -- p_asset_id - Asset ID --
81 -- P_mode - --
82 -- --
83 -- HISTORY: --
84 -- 12/09/99 Santosh Vaze Created --
85 ----------------------------------------------------------------------------
86
87 FUNCTION calc_corp_book_cost
88 ( p_book_type_code VARCHAR2,
89 p_period_counter NUMBER,
90 p_asset_id NUMBER,
91 p_mode VARCHAR2
92 )
93 RETURN NUMBER AS
94 x_cost NUMBER := 0;
95
96 BEGIN
97
98 IF (p_mode = 'CC') THEN
99 SELECT bkc.cost
100 INTO x_cost
101 FROM fa_deprn_periods dpc,
102 fa_book_controls bcc,
103 fa_books bkc
104 WHERE bcc.book_type_code = p_book_type_code
105 AND dpc.book_type_code = bcc.book_type_code
106 AND dpc.period_counter <= bcc.last_period_counter
107 AND dpc.period_counter = p_period_counter
108 AND bkc.asset_id = p_asset_id
109 AND bkc.book_type_code = dpc.book_type_code
110 AND bkc.date_effective <= nvl (dpc.period_close_date, sysdate)
111 AND nvl (dpc.period_close_date, sysdate) <= nvl (bkc.date_ineffective, sysdate);
112
113 RETURN (x_cost);
114 END IF;
115
116 IF (p_mode = 'CO') THEN
117 SELECT bkc.cost
118 INTO x_cost
119 FROM fa_deprn_periods dpc,
120 fa_book_controls bcc,
121 fa_books bkc
122 WHERE bcc.book_type_code = p_book_type_code
123 AND dpc.book_type_code = bcc.book_type_code
124 AND dpc.period_counter = bcc.last_period_counter
125 AND bkc.asset_id = p_asset_id
126 AND bkc.book_type_code = dpc.book_type_code
127 AND bkc.date_effective <= nvl (dpc.period_close_date, sysdate)
128 AND nvl (dpc.period_close_date, sysdate) <= nvl (bkc.date_ineffective, sysdate);
129
130 RETURN (x_cost);
131 END IF;
132
133 IF (p_mode = 'OO') THEN
134 SELECT bkc.cost
135 INTO x_cost
136 FROM fa_deprn_periods dpc,
137 fa_book_controls bcc,
138 fa_books bkc
139 WHERE bcc.book_type_code = p_book_type_code
140 AND dpc.book_type_code = bcc.book_type_code
141 AND dpc.period_counter = bcc.last_period_counter + 1
142 AND bkc.asset_id = p_asset_id
143 AND bkc.book_type_code = dpc.book_type_code
144 AND bkc.date_effective <= nvl (dpc.period_close_date, sysdate)
145 AND nvl (dpc.period_close_date, sysdate) <= nvl (bkc.date_ineffective, sysdate);
146
147 RETURN (x_cost);
148 END IF;
149
150 EXCEPTION
151 WHEN others THEN
152 RETURN (0);
153
154 END calc_corp_book_cost;
155
156 ----------------------------------------------------------------------------
157 -- FUNCTION --
158 -- calc_corp_deprn_reserve --
159 -- --
160 -- DESCRIPTION --
161 -- Use this function to calculate --
162 -- JL_ZZ_FA_ADJ_SUMMARY_V. --
163 -- --
164 -- PURPOSE: --
165 -- Oracle Applications Rel 11.0 --
166 -- --
167 -- PARAMETERS: --
168 -- p_book_type_code - Distribution Source Book --
169 -- p_period_counter - Period Counter --
170 -- p_asset_id - Asset ID --
171 -- P_mode - --
172 -- --
173 -- HISTORY: --
174 -- 12/09/99 Santosh Vaze Created --
175 ----------------------------------------------------------------------------
176
177 FUNCTION calc_corp_deprn_reserve
178 ( p_book_type_code VARCHAR2,
179 p_period_counter NUMBER,
180 p_asset_id NUMBER,
181 p_mode VARCHAR2
182 )
183 RETURN NUMBER AS
184 x_deprn_reserve NUMBER := 0;
185
186 BEGIN
187
188 IF (p_mode = 'CC') THEN
189 SELECT dsc.deprn_reserve
190 INTO x_deprn_reserve
191 FROM fa_deprn_summary dsc,
192 fa_deprn_periods dpc,
193 fa_book_controls bcc,
194 fa_books bkc
195 WHERE bcc.book_type_code = p_book_type_code
196 AND dpc.book_type_code = bcc.book_type_code
197 AND dpc.period_counter <= bcc.last_period_counter
198 AND dpc.period_counter = p_period_counter
199 AND bkc.asset_id = p_asset_id
200 AND bkc.book_type_code = dpc.book_type_code
201 AND bkc.date_effective <= nvl (dpc.period_close_date, sysdate)
202 AND nvl (dpc.period_close_date, sysdate) <= nvl (bkc.date_ineffective, sysdate)
203 AND dsc.asset_id = p_asset_id
204 AND dsc.book_type_code = dpc.book_type_code
205 AND dsc.period_counter = dpc.period_counter;
206
207 RETURN (x_deprn_reserve);
208 END IF;
209
210 IF (p_mode = 'CO') THEN
211 SELECT dsc.deprn_reserve
212 INTO x_deprn_reserve
213 FROM fa_deprn_summary dsc,
214 fa_deprn_periods dpc,
215 fa_book_controls bcc,
216 fa_books bkc
217 WHERE bcc.book_type_code = p_book_type_code
218 AND dpc.book_type_code = bcc.book_type_code
219 AND dpc.period_counter = bcc.last_period_counter
220 AND bkc.asset_id = p_asset_id
221 AND bkc.book_type_code = dpc.book_type_code
222 AND bkc.date_effective <= nvl (dpc.period_close_date, sysdate)
223 AND nvl (dpc.period_close_date, sysdate) <= nvl (bkc.date_ineffective, sysdate)
224 AND dsc.asset_id = p_asset_id
225 AND dsc.book_type_code = dpc.book_type_code
226 AND dsc.period_counter = dpc.period_counter;
227
228 RETURN (x_deprn_reserve);
229 END IF;
230
231 IF (p_mode = 'OO') THEN
232 SELECT dsc.deprn_reserve - jl_zz_fa_calc_adjustment_pkg.calc_adjustments (bcc.book_type_code, dpc.period_counter, p_asset_id, 'RESERVE', 'NET' )
233 INTO x_deprn_reserve
234 FROM fa_deprn_summary dsc,
235 fa_deprn_periods dpc,
236 fa_book_controls bcc,
237 fa_books bkc
238 WHERE bcc.book_type_code = p_book_type_code
239 AND dpc.book_type_code = bcc.book_type_code
240 AND dpc.period_counter = bcc.last_period_counter + 1
241 AND bkc.asset_id = p_asset_id
242 AND bkc.book_type_code = dpc.book_type_code
243 AND bkc.date_effective <= nvl (dpc.period_close_date, sysdate)
244 AND nvl (dpc.period_close_date, sysdate) <= nvl (bkc.date_ineffective, sysdate)
245 AND dsc.asset_id = p_asset_id
246 AND dsc.book_type_code = dpc.book_type_code
247 AND dsc.period_counter = bcc.last_period_counter;
248
249 RETURN (x_deprn_reserve);
250 END IF;
251
252 EXCEPTION
253 WHEN others THEN
254 RETURN (0);
255
256 END calc_corp_deprn_reserve;
257
258 ----------------------------------------------------------------------------
259 -- FUNCTION --
260 -- calc_corp_ytd_deprn --
261 -- --
262 -- DESCRIPTION --
263 -- Use this function to calculate --
264 -- JL_ZZ_FA_ADJ_SUMMARY_V. --
265 -- --
266 -- PURPOSE: --
267 -- Oracle Applications Rel 11.0 --
268 -- --
269 -- PARAMETERS: --
270 -- p_book_type_code - Distribution Source Book --
271 -- p_period_counter - Period Counter --
272 -- p_asset_id - Asset ID --
273 -- P_mode - --
274 -- --
275 -- HISTORY: --
276 -- 12/09/99 Santosh Vaze Created --
277 ----------------------------------------------------------------------------
278
279 FUNCTION calc_corp_ytd_deprn
280 ( p_book_type_code VARCHAR2,
281 p_period_counter NUMBER,
282 p_asset_id NUMBER,
283 p_mode VARCHAR2
284 )
285 RETURN NUMBER AS
286 x_ytd_deprn NUMBER := 0;
287
288 BEGIN
289
290 IF (p_mode = 'CC') THEN
291 SELECT dsc.ytd_deprn
292 INTO x_ytd_deprn
293 FROM fa_deprn_summary dsc,
294 fa_deprn_periods dpc,
295 fa_book_controls bcc,
296 fa_books bkc
297 WHERE bcc.book_type_code = p_book_type_code
298 AND dpc.book_type_code = bcc.book_type_code
299 AND dpc.period_counter <= bcc.last_period_counter
300 AND dpc.period_counter = p_period_counter
301 AND bkc.asset_id = p_asset_id
302 AND bkc.book_type_code = dpc.book_type_code
303 AND bkc.date_effective <= nvl (dpc.period_close_date, sysdate)
304 AND nvl (dpc.period_close_date, sysdate) <= nvl (bkc.date_ineffective, sysdate)
305 AND dsc.asset_id = p_asset_id
306 AND dsc.book_type_code = dpc.book_type_code
307 AND dsc.period_counter = dpc.period_counter;
308
309 RETURN (x_ytd_deprn);
310 END IF;
311
312 IF (p_mode = 'CO') THEN
313 SELECT dsc.ytd_deprn
314 INTO x_ytd_deprn
315 FROM fa_deprn_summary dsc,
316 fa_deprn_periods dpc,
317 fa_book_controls bcc,
318 fa_books bkc
319 WHERE bcc.book_type_code = p_book_type_code
320 AND dpc.book_type_code = bcc.book_type_code
321 AND dpc.period_counter = bcc.last_period_counter
322 AND bkc.asset_id = p_asset_id
323 AND bkc.book_type_code = dpc.book_type_code
324 AND bkc.date_effective <= nvl (dpc.period_close_date, sysdate)
325 AND nvl (dpc.period_close_date, sysdate) <= nvl (bkc.date_ineffective, sysdate)
326 AND dsc.asset_id = p_asset_id
327 AND dsc.book_type_code = dpc.book_type_code
328 AND dsc.period_counter = dpc.period_counter;
329
330 RETURN (x_ytd_deprn);
331 END IF;
332
333 IF (p_mode = 'OO') THEN
334 SELECT dsc.ytd_deprn + jl_zz_fa_calc_adjustment_pkg.calc_adjustments (bcc.book_type_code, dpc.period_counter, p_asset_id, 'EXPENSE', 'NET' )
335 INTO x_ytd_deprn
336 FROM fa_deprn_summary dsc,
337 fa_deprn_periods dpc,
338 fa_book_controls bcc,
339 fa_books bkc
340 WHERE bcc.book_type_code = p_book_type_code
341 AND dpc.book_type_code = bcc.book_type_code
342 AND dpc.period_counter = bcc.last_period_counter + 1
343 AND bkc.asset_id = p_asset_id
347 AND dsc.asset_id = p_asset_id
344 AND bkc.book_type_code = dpc.book_type_code
345 AND bkc.date_effective <= nvl (dpc.period_close_date, sysdate)
346 AND nvl (dpc.period_close_date, sysdate) <= nvl (bkc.date_ineffective, sysdate)
348 AND dsc.book_type_code = dpc.book_type_code
349 AND dsc.period_counter = bcc.last_period_counter;
350
351 RETURN (x_ytd_deprn);
352 END IF;
353
354
355 EXCEPTION
356 WHEN others THEN
357 RETURN (0);
358
359 END calc_corp_ytd_deprn;
360
361 END jl_zz_fa_calc_adjustment_pkg;