1 PACKAGE BODY FA_TOFES_YUD_ALEF_PKG AS
2 -- $Header: FASTYAPB.pls 120.4.12010000.8 2010/02/04 08:24:34 mswetha ship $
3 -- ****************************************************************************************
4 -- Copyright (c) 2000 Oracle Solution Services (India) Product Development
5 -- All rights reserved
6 -- ****************************************************************************************
7 --
8 -- PROGRAM NAME
9 -- FASTYAPB.pls
10 --
11 -- DESCRIPTION
12 -- This script creates the package body of FA_TOFES_YUD_ALEF_PKG.
13 -- This package is used to generate Israeli Fixed Asset Report.
14 --
15 -- USAGE
16 -- To install How to Install
17 -- To execute How to Execute
18 --
19 -- PROGRAM LIST DESCRIPTION
20 --
21 -- BEFOREREPORT This function is used to dynamically get the
22 -- WHERE clause in SELECT statement.
23 --
24 --YTD_TRANS_AMT This Funtion is used to Calculate the YTD TRANSACTION AMOUNT
25 --
26 --YTD_DEPRN_AMT This Funtion is used to Calculate the YTD Depreciation Amount
27 --
28 --YTD_DEPRN_RETIRE_AMT This Funtion is used to calcualte the YTD Depreciation retire Amount
29 --
30 --ACCM_DEPRN_PR_AMT This Funtion is used to calcualte the Accumulated Depreciation Prior Year Amount
31 --
32 --PURCHASE_DATE This Funtion is used to calculate the Purchase Date
33 --
34 -- DEPENDENCIES
35 -- None
36 --
37 -- CALLED BY
38 -- Tofes Yud Alef Report 1342 (Israel)
39 --
40 -- LAST UPDATE DATE 22-Jan-2007
41 -- Date the program has been modified for the last time.
42 --
43 -- HISTORY
44 -- =======
45 --
46 -- VERSION DATE AUTHOR(S) DESCRIPTION
47 -- ------- ----------- --------------- --------------------------------------
48 -- Draft1A 22-Jan-2007 Karna Kumar M Initial Creation
49 -- Draft1B 22-Sep-2009 Karna Kumar M updated the code as per the Bug# 8915053
50 -- Draft1C 11-Nov-2009 Karna Kumar M updated the code as per the Bug# 8915053
51 --===========================================================================*/
52
53 --Function to Calculate the YTD TRANSACTION AMOUNT
54 FUNCTION YTD_TRANS_AMT(p_asset_id_in IN NUMBER)
55 RETURN NUMBER
56 IS
57 ln_amount NUMBER;
58 BEGIN
59
60 IF upper(P_MRCSOBTYPE) = 'R'
61 THEN
62 SELECT SUM(DECODE(DEBIT_CREDIT_FLAG, 'CR', ADJUSTMENT_AMOUNT * (-1), ADJUSTMENT_AMOUNT))
63 INTO ln_amount
64 FROM FA_ADJUSTMENTS_MRC_V
65 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
66 AND ASSET_ID = p_asset_id_in
67 AND ADJUSTMENT_TYPE = 'COST'
68 AND SOURCE_TYPE_CODE = 'ADJUSTMENT'
69 AND PERIOD_COUNTER_ADJUSTED BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter;
70
71 ELSE
72
73 SELECT SUM(DECODE(DEBIT_CREDIT_FLAG, 'CR', ADJUSTMENT_AMOUNT * (-1), ADJUSTMENT_AMOUNT))
74 INTO ln_amount
75 FROM FA_ADJUSTMENTS
76 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
77 AND ASSET_ID = p_asset_id_in
78 AND ADJUSTMENT_TYPE = 'COST'
79 AND SOURCE_TYPE_CODE = 'ADJUSTMENT'
80 AND PERIOD_COUNTER_ADJUSTED BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter;
81 END IF;
82
83 RETURN ln_amount;
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 ln_amount := 0;
87 RETURN ln_amount;
88 END YTD_TRANS_AMT;
89
90 --=====================================================================
91 --=====================================================================
92
93 FUNCTION DEPRN_CLAIM_NUMBER (P_ASSET_ID_IN in number)
94 RETURN NUMBER
95 IS
96 ln_number NUMBER;
97 BEGIN
98
99 IF upper(P_MRCSOBTYPE) = 'R'
100 THEN
101 /* Bug#8915053,9103594-Changes done as per new formula for Depr.Claim Rate */
102 SELECT count(PERIOD_COUNTER)
103 INTO ln_number
104 FROM FA_DEPRN_SUMMARY_MRC_V
105 WHERE book_type_code = p_book_name
106 AND asset_id = p_asset_id_in
107 AND period_counter BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter
108 AND deprn_source_code = 'DEPRN'
109 AND deprn_amount <> 0;
110 ELSE
111 /* Bug#8915053,9103594-Changes done as per new formula for Depr.Claim Rate */
112 SELECT count(PERIOD_COUNTER)
113 INTO ln_number
114 FROM fa_Deprn_summary
115 WHERE book_type_code = p_book_name
116 AND asset_id = p_asset_id_in
117 AND period_counter BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter
118 AND deprn_source_code = 'DEPRN'
119 AND deprn_amount <> 0;
120 END IF;
121 RETURN ln_number;
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 ln_number := 1;
125 RETURN ln_number;
126 END DEPRN_CLAIM_NUMBER;
127
128 --=====================================================================
129 --=====================================================================
130 --Function to Calculate the YTD Depreciation Amount
131 FUNCTION YTD_DEPRN_AMT(p_asset_id_in IN NUMBER)
132 RETURN NUMBER
133 IS
134 ln_amount NUMBER;
135 BEGIN
136 /*SELECT SUM(DEPRN_AMOUNT)
137 INTO ln_amount
138 FROM FA_DEPRN_SUMMARY
139 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
140 AND ASSET_ID = p_asset_id_in
141 AND PERIOD_COUNTER BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter;*/
142 --Bug#9337624:Changes done to calculate ytd correctly.
143 IF upper(P_MRCSOBTYPE) = 'R' THEN
144 SELECT sum(deprn_amount)
145 INTO ln_amount
146 FROM FA_DEPRN_SUMMARY_MRC_V
147 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
148 AND ASSET_ID = p_asset_id_in
149 AND DEPRN_SOURCE_CODE = 'DEPRN'
150 AND PERIOD_COUNTER BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter;
151 ELSE
152 SELECT sum(deprn_amount)
153 INTO ln_amount
154 FROM FA_DEPRN_SUMMARY
155 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
156 AND ASSET_ID = p_asset_id_in
157 AND DEPRN_SOURCE_CODE = 'DEPRN'
158 AND PERIOD_COUNTER BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter;
159 END IF;
160
161 RETURN ln_amount;
162
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 ln_amount := 0;
166 RETURN ln_amount;
167 END YTD_DEPRN_AMT;
168
169 --=====================================================================
170 --=====================================================================
171 --Function to Calculate the YTD Depreciation retire Amount
172 FUNCTION YTD_DEPRN_RETIRE_AMT(p_asset_id_in IN NUMBER, p_period_counter IN NUMBER)
173 RETURN NUMBER
174 IS
175 ln_amount NUMBER;
176 BEGIN
177 /*SELECT SUM(DEPRN_AMOUNT)
178 INTO ln_amount
179 FROM FA_DEPRN_SUMMARY
180 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
181 AND ASSET_ID = p_asset_id_in
182 AND PERIOD_COUNTER BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter;*/
183 IF upper(P_MRCSOBTYPE) = 'R'
184 THEN
185 SELECT DEPRN_ADJUSTMENT_AMOUNT
186 INTO ln_amount
187 FROM FA_DEPRN_SUMMARY_MRC_V
188 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
189 AND ASSET_ID = p_asset_id_in
190 AND period_counter = p_period_counter
191 AND PERIOD_COUNTER > (SELECT MAX(fdp.period_counter)
192 FROM FA_DEPRN_PERIODS_MRC_V fdp
193 WHERE FDP.book_type_code = P_BOOK_NAME
194 AND fdp.fiscal_year < (SELECT MAX(fiscal_year)
195 FROM FA_DEPRN_PERIODS_MRC_V
196 WHERE book_type_code = P_BOOK_NAME
197 AND period_close_date IS NOT NULL));
198 ELSE
199
200 SELECT DEPRN_ADJUSTMENT_AMOUNT
201 INTO ln_amount
202 FROM FA_DEPRN_SUMMARY
203 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
204 AND ASSET_ID = p_asset_id_in
205 AND period_counter = p_period_counter
206 AND PERIOD_COUNTER > (SELECT MAX(fdp.period_counter)
207 FROM fa_deprn_periods fdp
208 WHERE FDP.book_type_code = P_BOOK_NAME
209 AND fdp.fiscal_year < (SELECT MAX(fiscal_year)
210 FROM FA_DEPRN_PERIODS
211 WHERE book_type_code = P_BOOK_NAME
212 AND period_close_date IS NOT NULL));
213 END IF;
214
215 RETURN ln_amount;
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 ln_amount := 0;
219 RETURN ln_amount;
220 END YTD_DEPRN_RETIRE_AMT;
221
222 --=====================================================================
223 --=====================================================================
224 --Function to calculate the Accumulated Depreciation Prior Year Amount
225 FUNCTION ACCM_DEPRN_PR_AMT(p_asset_id_in IN NUMBER)
226 RETURN NUMBER
227 IS
228 ln_amount NUMBER;
229 BEGIN
230
231
232 IF upper(P_MRCSOBTYPE) = 'R'
233 THEN
234 SELECT SUM(DEPRN_AMOUNT)
235 INTO ln_amount
236 FROM FA_DEPRN_SUMMARY_MRC_V
237 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
238 AND ASSET_ID = p_asset_id_in
239 AND PERIOD_COUNTER <= (SELECT MAX(fdp.period_counter)
240 FROM FA_DEPRN_PERIODS_MRC_V fdp
241 WHERE FDP.book_type_code = P_BOOK_NAME
242 AND fdp.fiscal_year < (SELECT MAX(fiscal_year)
243 FROM FA_DEPRN_PERIODS_MRC_V
244 WHERE book_type_code = P_BOOK_NAME
245 AND period_close_date IS NOT NULL));
246
247 ELSE
248
249 SELECT SUM(DEPRN_AMOUNT)
250 INTO ln_amount
251 FROM FA_DEPRN_SUMMARY
252 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
253 AND ASSET_ID = p_asset_id_in
254 AND PERIOD_COUNTER <= (SELECT MAX(fdp.period_counter)
255 FROM FA_DEPRN_PERIODS fdp
256 WHERE FDP.book_type_code = P_BOOK_NAME
257 AND fdp.fiscal_year < (SELECT MAX(fiscal_year)
258 FROM FA_DEPRN_PERIODS
259 WHERE book_type_code = P_BOOK_NAME
260 AND period_close_date IS NOT NULL));
261
262 END IF;
263
264 RETURN ln_amount;
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 ln_amount := 0;
268 RETURN ln_amount;
269 END ACCM_DEPRN_PR_AMT;
270
271 --=====================================================================
272 --=====================================================================
273 --Function to calculate the Accumulated Depreciation Prior Year Amount
274 FUNCTION ACCM_DEPRN_PR_RETIRE_AMT(p_asset_id_in IN NUMBER, p_period_counter IN NUMBER)
275 RETURN NUMBER
276 IS
277 ln_amount NUMBER;
278 BEGIN
279
280 IF upper(P_MRCSOBTYPE) = 'R'
281 THEN
282 SELECT SUM(DEPRN_ADJUSTMENT_AMOUNT)
283 INTO ln_amount
284 FROM FA_DEPRN_SUMMARY_MRC_V
285 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
286 AND ASSET_ID = p_asset_id_in
287 AND PERIOD_COUNTER = P_PERIOD_COUNTER
288 AND PERIOD_COUNTER <= (SELECT MAX(fdp.period_counter)
289 FROM FA_DEPRN_PERIODS_MRC_V fdp
290 WHERE FDP.book_type_code = P_BOOK_NAME
291 AND fdp.fiscal_year < (SELECT MAX(fiscal_year)
292 FROM FA_DEPRN_PERIODS_MRC_V
293 WHERE book_type_code = P_BOOK_NAME
294 AND period_close_date IS NOT NULL));
295
296 ELSE
297 SELECT SUM(DEPRN_ADJUSTMENT_AMOUNT)
298 INTO ln_amount
299 FROM FA_DEPRN_SUMMARY
300 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
301 AND ASSET_ID = p_asset_id_in
302 AND PERIOD_COUNTER = P_PERIOD_COUNTER
303 AND PERIOD_COUNTER <= (SELECT MAX(fdp.period_counter)
304 FROM fa_deprn_periods fdp
305 WHERE FDP.book_type_code = P_BOOK_NAME
306 AND fdp.fiscal_year < (SELECT MAX(fiscal_year)
307 FROM FA_DEPRN_PERIODS
308 WHERE book_type_code = P_BOOK_NAME
309 AND period_close_date IS NOT NULL));
310
311 END IF;
312
313 RETURN ln_amount;
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 ln_amount := 0;
317 RETURN ln_amount;
318 END ACCM_DEPRN_PR_RETIRE_AMT;
319
320 --=====================================================================
321 --=====================================================================
322 --Purchase date
323 FUNCTION PURCHASE_DATE(p_asset_id_in IN NUMBER)
324 RETURN DATE
325 IS
326 ld_date DATE;
327 BEGIN
328 SELECT INVOICE_DATE
329 INTO ld_date
330 FROM AP_INVOICES_ALL
331 WHERE INVOICE_ID IN (SELECT FAI.INVOICE_ID
332 FROM FA_ASSET_INVOICES FAI
333 ,FA_ADDITIONS_B FA
334 ,FA_BOOKS FB
335 WHERE FAI.ASSET_ID = FA.ASSET_ID
336 AND FA.ASSET_ID = FB.ASSET_ID
337 AND FA.ASSET_ID = p_asset_id_in
338 AND FB.BOOK_TYPE_CODE = P_BOOK_NAME)
339 AND VENDOR_ID IN (SELECT FAI.PO_VENDOR_ID
340 FROM FA_ASSET_INVOICES FAI
341 ,FA_ADDITIONS_B FA
342 ,FA_BOOKS FB
343 WHERE FAI.ASSET_ID = FA.ASSET_ID
344 AND FA.ASSET_ID = FB.ASSET_ID
345 AND FA.ASSET_ID = p_asset_id_in
346 AND FB.BOOK_TYPE_CODE = P_BOOK_NAME);
347
348 RETURN ld_date;
349 EXCEPTION
350 --Bug#9237853
351 WHEN OTHERS THEN
352 SELECT FB.DATE_PLACED_IN_SERVICE
353 INTO ld_date
354 FROM FA_ADDITIONS_B FA
355 ,FA_BOOKS FB
356 WHERE FA.ASSET_ID = FB.ASSET_ID
357 AND FA.ASSET_ID = p_asset_id_in
358 AND FB.BOOK_TYPE_CODE = P_BOOK_NAME
359 AND FB.TRANSACTION_HEADER_ID_OUT IS NULL;
360
361 RETURN ld_date;
362 END PURCHASE_DATE;
363
364 --=====================================================================
365 --=====================================================================
366
367 function AfterPForm return boolean is
368 begin
369 return (TRUE);
370 end;
371 --=============================================================
372 --=============================================================
373 FUNCTION beforeReport
374 RETURN BOOLEAN
375 IS
376 BEGIN
377
378
379 fnd_file.put_line(fnd_file.log,'p_ca_set_of_books_id is '||p_ca_set_of_books_id);
380 IF p_ca_set_of_books_id <> -1999
381 THEN
382
383 BEGIN
384 select mrc_sob_type_code, currency_code
385 into P_MRCSOBTYPE, lp_currency_code
386 from gl_sets_of_books
387 where set_of_books_id = p_ca_set_of_books_id;
388 fnd_file.put_line(fnd_file.log, 'P_MRCSOBTYPE '||P_MRCSOBTYPE);
389 fnd_file.put_line(fnd_file.log, 'lp_currency_code '||lp_currency_code);
390 EXCEPTION
391 WHEN OTHERS THEN
392 P_MRCSOBTYPE := 'P';
393 END;
394 ELSE
395 P_MRCSOBTYPE := 'P';
396 END IF;
397
398
399 fnd_file.put_line(fnd_file.log,'In the Before Report Proc');
400 fnd_file.put_line(fnd_file.log, 'P_MRCSOBTYPE '||P_MRCSOBTYPE);
401 IF upper(P_MRCSOBTYPE) = 'R'
402 THEN
403 fnd_client_info.set_currency_context(p_ca_set_of_books_id);
404 END IF;
405 fnd_file.put_line(fnd_file.log, ' after fnd_client_info ');
406 fnd_file.put_line(fnd_file.log, 'P_MRCSOBTYPE '||P_MRCSOBTYPE);
407 IF upper(P_MRCSOBTYPE) = 'R'
408 THEN
409 SELECT PERIOD_COUNTER
410 INTO p_lex_begin_period_counter
411 FROM FA_DEPRN_PERIODS_MRC_V
412 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
413 AND PERIOD_NAME = P_BEGIN_PERIOD;
414 fnd_file.put_line(fnd_file.log,'R-p_lex_begin_period_counter::'||p_lex_begin_period_counter);
415
416 SELECT PERIOD_COUNTER
417 INTO p_lex_end_period_counter
418 FROM FA_DEPRN_PERIODS_MRC_V
419 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
420 AND PERIOD_NAME = P_END_PERIOD;
421 fnd_file.put_line(fnd_file.log,'R-p_lex_end_period_counter::'||p_lex_end_period_counter);
422
423 SELECT Accounting_flex_structure
424 INTO lc_acct_flex_struc
425 FROM FA_BOOK_CONTROLS_MRC_V
426 WHERE book_type_code = P_BOOK_NAME;
427 fnd_file.put_line(fnd_file.log,'R-lc_acct_flex_struc::'||lc_acct_flex_struc);
428
429 ELSE
430 SELECT PERIOD_COUNTER
431 INTO p_lex_begin_period_counter
432 FROM FA_DEPRN_PERIODS
433 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
434 AND PERIOD_NAME = P_BEGIN_PERIOD;
435 fnd_file.put_line(fnd_file.log,'p_lex_begin_period_counter::'||p_lex_begin_period_counter);
436
437 SELECT PERIOD_COUNTER
438 INTO p_lex_end_period_counter
439 FROM FA_DEPRN_PERIODS
440 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
441 AND PERIOD_NAME = P_END_PERIOD;
442 fnd_file.put_line(fnd_file.log,'p_lex_end_period_counter::'||p_lex_end_period_counter);
443
444 SELECT Accounting_flex_structure
445 INTO lc_acct_flex_struc
446 FROM FA_BOOK_CONTROLS
447 WHERE book_type_code = P_BOOK_NAME;
448 fnd_file.put_line(fnd_file.log,'lc_acct_flex_struc::'||lc_acct_flex_struc);
449
450 END IF;
451
452 IF upper(P_MRCSOBTYPE) = 'R'
453
454 THEN
455 fnd_file.put_line(fnd_file.log,'R-b4 lp statementss ');
456 LP_FA_BOOK_CONTROLS := 'FA_BOOK_CONTROLS_MRC_V';
457 LP_FA_BOOKS := 'FA_BOOKS_MRC_V';
458 LP_FA_ADJUSTMENTS := 'FA_ADJUSTMENTS_MRC_V';
459 LP_FA_RETIREMENTS := 'FA_RETIREMENTS_MRC_V';
460 LP_FA_DEPRN_PERIODS := 'FA_DEPRN_PERIODS_MRC_V';
461 LP_FA_ASSET_INVOICES := 'FA_ASSET_INVOICES_MRC_V';
462 ELSE
463 lp_fa_book_controls := 'FA_BOOK_CONTROLS';
464 lp_fa_books := 'FA_BOOKS';
465 lp_fa_adjustments := 'FA_ADJUSTMENTS';
466 lp_fa_retirements := 'FA_RETIREMENTS';
467 lp_fa_deprn_periods := 'FA_DEPRN_PERIODS';
468 lp_fa_asset_invoices := 'FA_ASSET_INVOICES';
469
470 END IF;
471
472 RETURN(TRUE);
473 END beforeReport;
474
475 END FA_TOFES_YUD_ALEF_PKG;
[Home] [Help]