[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_YTD_PRE_PROCESS_PKG
Source
1 PACKAGE BODY IGI_IAC_YTD_PRE_PROCESS_PKG AS
2 -- $Header: igiiapyb.pls 120.7.12000000.1 2007/08/01 16:16:22 npandya noship $
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
7 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12 g_path VARCHAR2(100) := 'IGI.PLSQL.igiiapyb.IGI_IAC_YTD_PRE_PROCESS_PKG.';
13
14 --===========================FND_LOG.END=======================================
15
16 PROCEDURE POPULATE_IAC_FA_DEPRN_DATA ( errbuf OUT NOCOPY VARCHAR2
17 , retcode OUT NOCOPY NUMBER
18 , p_book_type_code IN VARCHAR2
19 , p_calling_mode IN VARCHAR2 ) IS
20
21 cursor c_get_dist_ytd(cp_book_type_code varchar2,
22 cp_asset_id number,
23 cp_distribution_id number) is
24 select sum(nvl(fdd.deprn_amount,0)-nvl(fdd.deprn_adjustment_amount,0)) deprn_YTD
25 from fa_deprn_detail fdd
26 where fdd.distribution_id = cp_distribution_id
27 and fdd.book_type_code like cp_book_type_code
28 and fdd.asset_id = cp_asset_id
29 and fdd.period_counter in (select period_counter from fa_deprn_periods
30 where book_type_code = cp_book_type_code
31 and fiscal_year = (select decode(period_num,1,fiscal_year-1,fiscal_year)
32 from fa_deprn_periods
33 where period_close_date is NULL
34 and book_type_code = cp_book_type_code))
35 group by fdd.asset_id,fdd.distribution_id;
36
37 CURSOR c_get_all_iac_assets IS
38 SELECT DISTINCT asset_id
39 FROM igi_iac_transaction_headers
40 WHERE book_type_code = p_book_type_code;
41
42 cursor c_get_dist_deprn(cp_asset_id number,cp_distribution_id number) is
43 select (nvl(deprn_amount,0) - nvl(deprn_adjustment_amount,0)) deprn_amount,
44 deprn_reserve,
45 period_counter
46 from fa_deprn_detail
47 where book_type_code = p_book_type_code
48 and asset_id = cp_asset_id
49 and distribution_id = cp_distribution_id
50 and period_counter = (select max(period_counter)
51 from fa_deprn_summary
52 where book_type_code = p_book_type_code
53 and asset_id = cp_asset_id);
54
55 CURSOR c_get_dists(cp_asset_id number, cp_adjustment_id number) IS
56 SELECT *
57 FROM igi_iac_det_balances
58 WHERE book_type_code = p_book_type_code
59 AND asset_id = cp_asset_id
60 AND adjustment_id = cp_adjustment_id;
61
62 CURSOR c_get_dist_info(cp_asset_id number,cp_adjustment_id number,cp_distribution_id number) IS
63 SELECT period_counter
64 FROM igi_iac_det_balances
65 WHERE book_type_code = p_book_type_code
66 AND asset_id = cp_asset_id
67 AND adjustment_id = cp_adjustment_id
68 AND distribution_id = cp_distribution_id;
69
70 CURSOR c_get_iac_fa_deprn_rec IS
71 SELECT 'X'
72 FROM igi_iac_fa_deprn
73 WHERE book_type_code = p_book_type_code
74 AND rownum = 1;
75
76 cursor c_check_igi_fa_deprn (cp_asset_id number,
77 cp_distribution_id number,
78 cp_adjustment_id number,
79 cp_period_counter number) is
80 Select *
81 From igi_iac_fa_deprn
82 Where book_type_code = p_book_type_code
83 and asset_id = cp_asset_id
84 and distribution_id = cp_distribution_id
85 and adjustment_id = cp_adjustment_id
86 and period_counter = cp_period_counter;
87
88 -- bug 3421734, start 1
89 CURSOR c_fully_reserved(cp_asset_id fa_books.asset_id%TYPE,
90 cp_book_type_code fa_books.book_type_code%TYPE)
91 IS
92 SELECT nvl(period_counter_fully_reserved, 0)
93 FROM fa_books
94 WHERE asset_id = cp_asset_id
95 AND book_type_code = cp_book_type_code
96 AND transaction_header_id_out IS NULL
97 AND date_ineffective IS NULL;
98
99 l_fully_reserved_pc fa_books.period_counter_fully_reserved%TYPE;
100 -- bug 3421734 end 1
101
102 l_Transaction_Type_Code igi_iac_transaction_headers.transaction_type_code%TYPE;
103 l_Transaction_Id NUMBER;
104 l_Mass_Reference_ID NUMBER;
105 l_Adjustment_Id NUMBER;
106 l_Prev_Adjustment_Id NUMBER;
107 l_Adjustment_Status igi_iac_transaction_headers.adjustment_status%TYPE;
108 l_rowid VARCHAR2(300);
109 lv_mesg VARCHAR2(2000);
110 l_debug_mode VARCHAR2(1) := 'N';
111 l_deprn_amount NUMBER;
112 l_deprn_ytd NUMBER;
113 l_deprn_reserve NUMBER;
114 l_deprn_period_counter NUMBER;
115 l_prev_adj_period NUMBER;
116 l_dummy_char VARCHAR2(1);
117 l_check_igi_fa_deprn c_check_igi_fa_deprn%rowtype;
118
119 l_path VARCHAR2(100) := g_path||'POPULATE_IAC_FA_DEPRN_DATA';
120
121 BEGIN
122 SAVEPOINT iac_pre_process;
123 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'************************************');
124 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Start of processing for book :'||p_book_type_code);
125 fnd_file.put_line(fnd_file.log, 'Start of processing for book :'||p_book_type_code);
126 IF NOT igi_gen.is_req_installed('IAC') THEN
127 fnd_message.set_name('IGI','IGI_GEN_PROD_NOT_INSTALLED');
128 lv_mesg := fnd_message.get;
129 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ********** ');
130 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,lv_mesg);
131 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ********** ');
132 retcode := 2;
133 fnd_file.put_line(FND_FILE.log, lv_mesg);
134 errbuf := lv_mesg;
135 RETURN;
136 END IF;
137
138 IF NOT igi_iac_common_utils.is_iac_book(p_book_type_code) THEN
139 fnd_message.set_name('IGI','IGI_IAC_NOT_IAC_BOOK');
140 lv_mesg := fnd_message.get;
141 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ********** ');
142 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,lv_mesg);
143 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ********** ');
144 retcode := 2;
145 errbuf := lv_mesg;
146 RETURN;
147 END IF;
148
149 IF p_calling_mode <> 'SRS' THEN
150
151 OPEN c_get_iac_fa_deprn_rec;
152 FETCH c_get_iac_fa_deprn_rec INTO l_dummy_char;
153 IF c_get_iac_fa_deprn_rec%FOUND THEN
154 CLOSE c_get_iac_fa_deprn_rec;
155 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ********** ');
156 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' This process is not required for this book as the required data is already present.');
157 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ********** ');
158 lv_mesg := ' This process is not required for this book as the required data is already present.';
159 retcode := 2;
160 errbuf := lv_mesg;
161 RETURN;
162 ELSE
163 CLOSE c_get_iac_fa_deprn_rec;
164 END IF;
165 END IF;
166
167 fnd_file.put_line(fnd_file.log, 'Processing assets');
168
169 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Processing the assets');
170 FOR l_asset IN c_get_all_iac_assets LOOP
171 fnd_file.put_line(fnd_file.log, 'Asset_id ' || l_asset.asset_id );
172
173 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Asset_id :'||l_asset.asset_id);
174 l_Transaction_Type_Code := NULL;
175 l_Transaction_Id := NULL;
176 l_Mass_Reference_ID := NULL;
177 l_Adjustment_Id := NULL;
178 l_Prev_Adjustment_Id := NULL;
179 l_Adjustment_Status := NULL;
180
181 IF NOT igi_iac_common_utils.Get_Latest_Transaction (
182 X_book_type_code => p_book_type_code
183 , X_asset_id => l_asset.asset_id
184 , X_Transaction_Type_Code => l_transaction_type_code
185 , X_Transaction_Id => l_transaction_id
186 , X_Mass_Reference_ID => l_mass_reference_id
187 , X_Adjustment_Id => l_adjustment_id
188 , X_Prev_Adjustment_Id => l_prev_adjustment_id
189 , X_Adjustment_Status => l_adjustment_status) THEN
190
191 retcode := 2;
192 errbuf := 'Error in Fetching the Latest Transaction for the asset';
193 RETURN;
194 END IF;
195
196 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Latest Adjustment :'||l_adjustment_id);
197 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Previous Adjustment :'||l_prev_adjustment_id);
198
199 OPEN c_fully_reserved(l_asset.asset_id, p_book_type_code);
200 FETCH c_fully_reserved INTO l_fully_reserved_pc;
201 CLOSE c_fully_reserved;
202
203 FOR l_dist IN c_get_dists(l_asset.asset_id,l_adjustment_id) LOOP
204 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Distribution Id :'||l_dist.distribution_id);
205 fnd_file.put_line(fnd_file.log, 'distribution_id: ' || l_dist.distribution_id );
206
207 l_deprn_ytd := 0;
208 OPEN c_get_dist_ytd(p_book_type_code,l_asset.asset_id,l_dist.distribution_id);
209 FETCH c_get_dist_ytd INTO l_deprn_ytd;
210 IF c_get_dist_ytd%NOTFOUND THEN
211 l_deprn_ytd := 0;
212 END IF;
213 CLOSE c_get_dist_ytd;
214
215 OPEN c_get_dist_deprn(l_asset.asset_id,l_dist.distribution_id);
216 FETCH c_get_dist_deprn INTO l_deprn_amount,l_deprn_reserve,l_deprn_period_counter;
217 IF c_get_dist_deprn%NOTFOUND THEN
218 l_deprn_amount := 0;
219 l_deprn_reserve := 0;
220 END IF;
221 CLOSE c_get_dist_deprn;
222
223 IF l_fully_reserved_pc IS NOT NULL THEN
224 l_deprn_amount := 0;
225 END IF;
226
227 IF p_calling_mode <> 'SRS' THEN
228 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Inserting record for latest adjustment');
229 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_adjustment_id =>'|| l_adjustment_id);
230 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_asset_id =>'|| l_asset.asset_id);
231 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_distribution_id =>'|| l_dist.distribution_id);
232 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_book_type_code =>'|| p_book_type_code);
233 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_period_counter =>'|| l_dist.period_counter);
234 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_period =>'|| l_deprn_amount);
235 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_ytd =>'|| l_deprn_ytd);
236 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_reserve =>'|| l_deprn_reserve);
237 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_active_flag =>'|| l_dist.active_flag);
238
239 l_rowid := NULL;
240 IGI_IAC_FA_DEPRN_PKG.insert_row (
241 x_rowid => l_rowid,
242 x_adjustment_id => l_adjustment_id,
243 x_asset_id => l_asset.asset_id,
244 x_distribution_id => l_dist.distribution_id,
245 x_book_type_code => p_book_type_code,
246 x_period_counter => l_dist.period_counter,
247 x_deprn_period => l_deprn_amount,
248 x_deprn_ytd => l_deprn_ytd,
249 x_deprn_reserve => l_deprn_reserve,
250 x_active_flag => l_dist.active_flag,
251 x_mode => 'R' );
252 ELSE
253 OPEN c_check_igi_fa_deprn(l_asset.asset_id,l_dist.distribution_id,l_adjustment_id,l_dist.period_counter);
254 FETCH c_check_igi_fa_deprn INTO l_check_igi_fa_deprn;
255 IF c_check_igi_fa_deprn%NOTFOUND THEN
256
257 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Inserting record for latest adjustment');
258 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_adjustment_id =>'|| l_adjustment_id);
259 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_asset_id =>'|| l_asset.asset_id);
260 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_distribution_id =>'|| l_dist.distribution_id);
261 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_book_type_code =>'|| p_book_type_code);
262 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_period_counter =>'|| l_dist.period_counter);
263 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_period =>'|| l_deprn_amount);
264 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_ytd =>'|| l_deprn_ytd);
265 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_reserve =>'|| l_deprn_reserve);
266 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_active_flag =>'|| l_dist.active_flag);
267
268 l_rowid := NULL;
269 IGI_IAC_FA_DEPRN_PKG.insert_row (
270 x_rowid => l_rowid,
271 x_adjustment_id => l_adjustment_id,
272 x_asset_id => l_asset.asset_id,
273 x_distribution_id => l_dist.distribution_id,
274 x_book_type_code => p_book_type_code,
275 x_period_counter => l_dist.period_counter,
276 x_deprn_period => l_deprn_amount,
277 x_deprn_ytd => l_deprn_ytd,
278 x_deprn_reserve => l_deprn_reserve,
279 x_active_flag => l_dist.active_flag,
280 x_mode => 'R' );
281 END IF;
282 CLOSE c_check_igi_fa_deprn;
283 END IF;
284
285 IF (nvl(l_adjustment_id,0) <> nvl(l_prev_adjustment_id,0)) AND l_prev_adjustment_id IS NOT NULL THEN
286
287 OPEN c_get_dist_info(l_asset.asset_id,l_prev_adjustment_id,l_dist.distribution_id);
288 FETCH c_get_dist_info INTO l_prev_adj_period;
289 CLOSE c_get_dist_info;
290
291 IF p_calling_mode <> 'SRS' THEN
292 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Inserting record for previous adjustment');
293 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_adjustment_id =>'|| l_adjustment_id);
294 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_asset_id =>'|| l_asset.asset_id);
295 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_distribution_id =>'|| l_dist.distribution_id);
296 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_book_type_code =>'|| p_book_type_code);
297 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_period_counter =>'|| l_dist.period_counter);
298 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_period =>'|| l_deprn_amount);
299 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_ytd =>'|| l_deprn_ytd);
300 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_reserve =>'|| l_deprn_reserve);
301 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_active_flag =>'|| l_dist.active_flag);
302
303 l_rowid := NULL;
304 IGI_IAC_FA_DEPRN_PKG.insert_row (
305 x_rowid => l_rowid,
306 x_adjustment_id => l_prev_adjustment_id,
307 x_asset_id => l_asset.asset_id,
308 x_distribution_id => l_dist.distribution_id,
309 x_book_type_code => p_book_type_code,
310 x_period_counter => l_prev_adj_period,
311 x_deprn_period => l_deprn_amount,
312 x_deprn_ytd => l_deprn_ytd,
313 x_deprn_reserve => l_deprn_reserve,
314 x_active_flag => l_dist.active_flag,
315 x_mode => 'R' );
316 ELSE
317 OPEN c_check_igi_fa_deprn(l_asset.asset_id,l_dist.distribution_id,l_prev_adjustment_id,l_prev_adj_period);
318 FETCH c_check_igi_fa_deprn INTO l_check_igi_fa_deprn;
319 IF c_check_igi_fa_deprn%NOTFOUND THEN
320
321 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Inserting record for previous adjustment');
322 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_adjustment_id =>'|| l_adjustment_id);
323 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_asset_id =>'|| l_asset.asset_id);
324 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_distribution_id =>'|| l_dist.distribution_id);
325 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_book_type_code =>'|| p_book_type_code);
326 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_period_counter =>'|| l_dist.period_counter);
327 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_period =>'|| l_deprn_amount);
328 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_ytd =>'|| l_deprn_ytd);
329 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_deprn_reserve =>'|| l_deprn_reserve);
330 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' x_active_flag =>'|| l_dist.active_flag);
331
332 l_rowid := NULL;
333 IGI_IAC_FA_DEPRN_PKG.insert_row (
334 x_rowid => l_rowid,
335 x_adjustment_id => l_prev_adjustment_id,
336 x_asset_id => l_asset.asset_id,
337 x_distribution_id => l_dist.distribution_id,
338 x_book_type_code => p_book_type_code,
339 x_period_counter => l_prev_adj_period,
340 x_deprn_period => l_deprn_amount,
341 x_deprn_ytd => l_deprn_ytd,
342 x_deprn_reserve => l_deprn_reserve,
343 x_active_flag => l_dist.active_flag,
344 x_mode => 'R' );
345 END IF;
346 CLOSE c_check_igi_fa_deprn;
347 END IF;
348 END IF;
349 END LOOP;
350 END LOOP;
351 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'End of Processing');
352 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*************************');
353 IF p_calling_mode = 'SRS' THEN
354 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Called in SRS mode');
355 IF l_debug_mode = 'Y' THEN
356 ROLLBACK TO iac_pre_process;
357 ELSE
358 COMMIT;
359 END IF;
360 ELSE
361 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Called in Non SRS mode');
362 null;
363 END IF;
364 errbuf := null;
365 retcode := 0;
366
367 EXCEPTION WHEN OTHERS THEN
368 IF p_calling_mode = 'SRS' THEN
369 ROLLBACK TO iac_pre_process;
370 END IF;
371 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
372 errbuf := SQLERRM;
373 retcode := 2;
374
375 END POPULATE_IAC_FA_DEPRN_DATA;
376
377 END IGI_IAC_YTD_PRE_PROCESS_PKG;