1 PACKAGE BODY FA_TOFES_YUD_ALEF_PKG AS
2 -- $Header: FASTYAPB.pls 120.4.12010000.1 2008/07/28 13:17:46 appldev 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 --===========================================================================*/
50
51 --Function to Calculate the YTD TRANSACTION AMOUNT
52 FUNCTION YTD_TRANS_AMT(p_asset_id_in IN NUMBER)
53 RETURN NUMBER
54 IS
55 ln_amount NUMBER;
56 BEGIN
57 SELECT SUM(DECODE(DEBIT_CREDIT_FLAG, 'CR', ADJUSTMENT_AMOUNT * (-1), ADJUSTMENT_AMOUNT))
58 INTO ln_amount
59 FROM FA_ADJUSTMENTS
60 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
61 AND ASSET_ID = p_asset_id_in
62 AND ADJUSTMENT_TYPE = 'COST'
63 AND SOURCE_TYPE_CODE = 'ADJUSTMENT'
64 AND PERIOD_COUNTER_ADJUSTED BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter;
65 RETURN ln_amount;
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 ln_amount := 0;
69 RETURN ln_amount;
70 END YTD_TRANS_AMT;
71
72 --=====================================================================
73 --=====================================================================
74
75 FUNCTION DEPRN_CLAIM_NUMBER
76 RETURN NUMBER
77 IS
78 ln_number NUMBER;
79 BEGIN
80 SELECT CASE WHEN (MONTHS_BETWEEN(ADD_MONTHS(FB.DATE_PLACED_IN_SERVICE,FB.LIFE_IN_MONTHS), FDP.PERIOD_OPEN_DATE)) > 12 THEN 12
81 ELSE (MONTHS_BETWEEN(ADD_MONTHS(FB.DATE_PLACED_IN_SERVICE,FB.LIFE_IN_MONTHS), FDP.PERIOD_OPEN_DATE))
82 END
83 INTO ln_number
84 FROM FA_BOOKS FB
85 ,FA_DEPRN_PERIODS FDP
86 WHERE FB.BOOK_type_code = FDP.book_type_code
87 AND FB.BOOK_type_code = P_BOOK_NAME
88 AND FDP.period_counter = p_lex_begin_period_counter
89 AND FB.DEPRECIATE_FLAG = 'YES'
90 AND FB.TRANSACTION_HEADER_ID_IN = (SELECT max(fb1.TRANSACTION_HEADER_ID_IN)
91 FROM FA_BOOKS fb1
92 WHERE fb1.BOOK_type_code = P_BOOK_NAME
93 AND fb1.DEPRECIATE_FLAG = 'YES');
94 RETURN ln_number;
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 ln_number := 1;
98 RETURN ln_number;
99 END DEPRN_CLAIM_NUMBER;
100
101 --=====================================================================
102 --=====================================================================
103 --Function to Calculate the YTD Depreciation Amount
104 FUNCTION YTD_DEPRN_AMT(p_asset_id_in IN NUMBER)
105 RETURN NUMBER
106 IS
107 ln_amount NUMBER;
108 BEGIN
109 /*SELECT SUM(DEPRN_AMOUNT)
110 INTO ln_amount
111 FROM FA_DEPRN_SUMMARY
112 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
113 AND ASSET_ID = p_asset_id_in
114 AND PERIOD_COUNTER BETWEEN p_lex_begin_period_counter AND p_lex_end_period_counter;*/
115 SELECT YTD_DEPRN
116 INTO ln_amount
117 FROM FA_DEPRN_SUMMARY
118 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
119 AND ASSET_ID = p_asset_id_in
120 AND PERIOD_COUNTER = p_lex_end_period_counter;
121 RETURN ln_amount;
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 ln_amount := 0;
125 RETURN ln_amount;
126 END YTD_DEPRN_AMT;
127
128 --=====================================================================
129 --=====================================================================
130 --Function to Calculate the YTD Depreciation retire Amount
131 FUNCTION YTD_DEPRN_RETIRE_AMT(p_asset_id_in IN NUMBER, p_period_counter 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 SELECT DEPRN_ADJUSTMENT_AMOUNT
143 INTO ln_amount
144 FROM FA_DEPRN_SUMMARY
145 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
146 AND ASSET_ID = p_asset_id_in
147 AND period_counter = p_period_counter
148 AND PERIOD_COUNTER > (SELECT MAX(fdp.period_counter)
149 FROM fa_deprn_periods fdp
150 WHERE FDP.book_type_code = P_BOOK_NAME
151 AND fdp.fiscal_year < (SELECT MAX(fiscal_year)
152 FROM FA_DEPRN_PERIODS
153 WHERE book_type_code = P_BOOK_NAME
154 AND period_close_date IS NOT NULL));
155 RETURN ln_amount;
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 ln_amount := 0;
159 RETURN ln_amount;
160 END YTD_DEPRN_RETIRE_AMT;
161
162 --=====================================================================
163 --=====================================================================
164 --Function to calculate the Accumulated Depreciation Prior Year Amount
165 FUNCTION ACCM_DEPRN_PR_AMT(p_asset_id_in IN NUMBER)
166 RETURN NUMBER
167 IS
168 ln_amount NUMBER;
169 BEGIN
170 SELECT SUM(DEPRN_AMOUNT)
171 INTO ln_amount
172 FROM FA_DEPRN_SUMMARY
173 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
174 AND ASSET_ID = p_asset_id_in
175 AND PERIOD_COUNTER <= (SELECT MAX(fdp.period_counter)
176 FROM fa_deprn_periods fdp
177 WHERE FDP.book_type_code = P_BOOK_NAME
178 AND fdp.fiscal_year < (SELECT MAX(fiscal_year)
179 FROM FA_DEPRN_PERIODS
180 WHERE book_type_code = P_BOOK_NAME
181 AND period_close_date IS NOT NULL));
182 RETURN ln_amount;
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 ln_amount := 0;
186 RETURN ln_amount;
187 END ACCM_DEPRN_PR_AMT;
188
189 --=====================================================================
190 --=====================================================================
191 --Function to calculate the Accumulated Depreciation Prior Year Amount
192 FUNCTION ACCM_DEPRN_PR_RETIRE_AMT(p_asset_id_in IN NUMBER, p_period_counter IN NUMBER)
193 RETURN NUMBER
194 IS
195 ln_amount NUMBER;
196 BEGIN
197 SELECT SUM(DEPRN_ADJUSTMENT_AMOUNT)
198 INTO ln_amount
199 FROM FA_DEPRN_SUMMARY
200 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
201 AND ASSET_ID = p_asset_id_in
202 AND PERIOD_COUNTER = P_PERIOD_COUNTER
203 AND PERIOD_COUNTER <= (SELECT MAX(fdp.period_counter)
204 FROM fa_deprn_periods fdp
205 WHERE FDP.book_type_code = P_BOOK_NAME
206 AND fdp.fiscal_year < (SELECT MAX(fiscal_year)
207 FROM FA_DEPRN_PERIODS
208 WHERE book_type_code = P_BOOK_NAME
209 AND period_close_date IS NOT NULL));
210 RETURN ln_amount;
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 ln_amount := 0;
214 RETURN ln_amount;
215 END ACCM_DEPRN_PR_RETIRE_AMT;
216
217 --=====================================================================
218 --=====================================================================
219 --Purchase date
220 FUNCTION PURCHASE_DATE(p_asset_id_in IN NUMBER)
221 RETURN DATE
222 IS
223 ld_date DATE;
224 BEGIN
225 SELECT INVOICE_DATE
226 INTO ld_date
227 FROM AP_INVOICES_ALL
228 WHERE INVOICE_ID IN (SELECT FAI.INVOICE_ID
229 FROM FA_ASSET_INVOICES FAI
230 ,FA_ADDITIONS_B FA
231 ,FA_BOOKS FB
232 WHERE FAI.ASSET_ID = FA.ASSET_ID
233 AND FA.ASSET_ID = FB.ASSET_ID
234 AND FA.ASSET_ID = p_asset_id_in
235 AND FB.BOOK_TYPE_CODE = P_BOOK_NAME)
236 AND VENDOR_ID IN (SELECT FAI.PO_VENDOR_ID
237 FROM FA_ASSET_INVOICES FAI
238 ,FA_ADDITIONS_B FA
239 ,FA_BOOKS FB
240 WHERE FAI.ASSET_ID = FA.ASSET_ID
241 AND FA.ASSET_ID = FB.ASSET_ID
242 AND FA.ASSET_ID = p_asset_id_in
243 AND FB.BOOK_TYPE_CODE = P_BOOK_NAME);
244
245 RETURN ld_date;
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248 SELECT FB.DATE_PLACED_IN_SERVICE
249 INTO ld_date
250 FROM FA_ADDITIONS_B FA
251 ,FA_BOOKS FB
252 WHERE FA.ASSET_ID = FB.ASSET_ID
253 AND FA.ASSET_ID = p_asset_id_in
254 AND FB.BOOK_TYPE_CODE = P_BOOK_NAME
255 AND FB.TRANSACTION_HEADER_ID_OUT IS NULL;
256
257 RETURN ld_date;
258 END PURCHASE_DATE;
259
260 --=====================================================================
261 --=====================================================================
262
263 FUNCTION beforeReport
264 RETURN BOOLEAN
265 IS
266 BEGIN
267 fnd_file.put_line(fnd_file.log,'In the Before Report Proc');
268 SELECT PERIOD_COUNTER
269 INTO p_lex_begin_period_counter
270 FROM FA_DEPRN_PERIODS
271 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
272 AND PERIOD_NAME = P_BEGIN_PERIOD;
273 fnd_file.put_line(fnd_file.log,'p_lex_begin_period_counter::'||p_lex_begin_period_counter);
274
275 SELECT PERIOD_COUNTER
276 INTO p_lex_end_period_counter
277 FROM FA_DEPRN_PERIODS
278 WHERE BOOK_TYPE_CODE = P_BOOK_NAME
279 AND PERIOD_NAME = P_END_PERIOD;
280 fnd_file.put_line(fnd_file.log,'p_lex_end_period_counter::'||p_lex_end_period_counter);
281
282 SELECT Accounting_flex_structure
283 INTO lc_acct_flex_struc
284 FROM FA_BOOK_CONTROLS
285 WHERE book_type_code = P_BOOK_NAME;
286 fnd_file.put_line(fnd_file.log,'lc_acct_flex_struc::'||lc_acct_flex_struc);
287
288 RETURN(TRUE);
289 END beforeReport;
290
291 END FA_TOFES_YUD_ALEF_PKG;
[Home] [Help]