eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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;