[Home] [Help]
PACKAGE BODY: APPS.IGIRX_IAC_PROJ
Source
1 PACKAGE BODY igirx_iac_proj AS
2 -- $Header: igiiaxpb.pls 120.9 2007/08/01 10:45:33 npandya ship $
3
4
5 --===========================FND_LOG.START=====================================
6
7 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
8 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
9 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
10 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
11 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
12 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
13 g_path VARCHAR2(100) := 'IGI.PLSQL.igiiaxpb.igirx_iac_proj.';
14
15 --===========================FND_LOG.END=======================================
16
17 -- ====================================================================
18 -- PROCEDURE Proj: Main procedure that will be called by the RXi
19 -- outer wrapper process for IAC projections
20 -- ====================================================================
21 PROCEDURE proj(p_projection_id NUMBER,
22 p_request_id NUMBER,
23 retcode OUT NOCOPY NUMBER,
24 errbuf OUT NOCOPY VARCHAR2)
25 IS
26
27 -- cursors
28 -- Get the master projection data
29 CURSOR c_get_proj(n_projection_id NUMBER)
30 IS
31 SELECT book_type_code,
32 start_period_counter,
33 end_period,
34 revaluation_period
35 FROM igi_iac_projections
36 WHERE projection_id = n_projection_id;
37
38 -- Get the detail level projection data
39 CURSOR c_proj_data(n_proj_id igi_iac_projections.projection_id%TYPE)
40 IS
41 SELECT dp.projection_id,
42 dp.period_counter proj_period_counter,
43 dp.category_id,
44 fc.description cat_desc,
45 dp.fiscal_year,
46 dp.company,
47 dp.cost_center,
48 dp.asset_id,
49 fad.asset_number,
50 fad.description asset_desc,
51 dp.latest_reval_cost,
52 dp.deprn_period,
53 dp.deprn_ytd,
54 substr(dp.asset_exception,1,30) exception_code
55 FROM igi_iac_proj_details dp,
56 fa_additions fad,
57 fa_categories fc
58 WHERE dp.projection_id = n_proj_id
59 AND fad.asset_id = dp.asset_id
60 AND dp.category_id = fc.category_id
61 ORDER BY dp.asset_id, dp.period_counter;
62
63
64
65
66 -- variables
67 l_login_id NUMBER := fnd_profile.value('LOGIN_ID');
68 l_user_id NUMBER := fnd_profile.value('USER_ID');
69
70 l_cat_struct fa_system_controls.category_flex_structure%TYPE;
71 l_assetkey_struct fa_system_controls.asset_key_flex_structure%TYPE;
72
73 l_get_proj c_get_proj%ROWTYPE;
74 l_sob_id fa_book_controls.set_of_books_id%TYPE;
75 l_currency_code gl_sets_of_books.currency_code%TYPE;
76 l_organization_name gl_sets_of_books.name%TYPE;
77
78 l_min_cat VARCHAR2(50);
79
80 l_concat_cat VARCHAR2(500);
81 l_cat_segs fa_rx_shared_pkg.Seg_Array;
82
83 l_prd_rec igi_iac_types.prd_rec;
84 l_ret_flag BOOLEAN;
85 l_start_period_name VARCHAR2(15);
86 l_end_period_name VARCHAR2(15);
87 l_reval_prd_ctr NUMBER;
88 l_reval_idx_val NUMBER;
89 l_reval_period_name VARCHAR2(15);
90 l_exception_desc VARCHAR2(255);
91
92 TYPE iac_char15_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
93 TYPE iac_char30_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
94 TYPE iac_char80_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
95 TYPE iac_char255_type IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
96 TYPE iac_char500_type IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
97 TYPE iac_num_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
98
99 TYPE rxi_proj_rec IS RECORD (
100 reccount iac_num_type,
101 projection_id iac_num_type,
102 book_type_code iac_char15_type,
103 start_period iac_char15_type,
104 end_period iac_char15_type,
105 proj_period iac_char15_type,
106 fiscal_year iac_num_type,
107 periodic_index iac_num_type,
108 reval_index iac_num_type,
109 reval_period_name iac_char15_type,
110 company iac_char30_type,
111 cost_center iac_char30_type,
112 category_id iac_num_type,
113 major_category iac_char30_type,
114 minor_category iac_char30_type,
115 category iac_char500_type,
116 category_desc iac_char80_type,
117 asset_number iac_char30_type,
118 asset_desc iac_char80_type,
119 deprn_period iac_num_type,
120 deprn_ytd iac_num_type,
121 latest_reval_cost iac_num_type,
122 exception_code iac_char30_type,
123 exception_desc iac_char255_type,
124 period_counter iac_num_type);
125
126
127 l_rxi_proj_rec rxi_proj_rec;
128 l_proj_data c_proj_data%ROWTYPE;
129 l_count NUMBER := 1;
130
131 l_sql VARCHAR2(5000);
132 -- exceptions
133 l_path VARCHAR2(150) := g_path||'proj';
134 BEGIN
135 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Entered inner procedure');
136 -- get the category and asset key flex structures
137 SELECT category_flex_structure, asset_key_flex_structure
138 INTO l_cat_struct, l_assetkey_struct
139 FROM fa_system_controls;
140
141 -- get the book type code for the projection
142 OPEN c_get_proj(p_projection_id);
143 FETCH c_get_proj INTO l_get_proj;
144 IF c_get_proj%NOTFOUND THEN
145 CLOSE c_get_proj;
146 END IF;
147 CLOSE c_get_proj;
148
149 -- get the sob id, currency code and organization name
150 SELECT sob.set_of_books_id,
151 substr(sob.currency_code,1,15),
152 substr(sob.name,1 ,80)
153 INTO l_sob_id,
154 l_currency_code,
155 l_organization_name
156 FROM fa_book_controls bc,
157 gl_sets_of_books sob,
158 fnd_currencies cur
159 WHERE bc.book_type_code = l_get_proj.book_type_code
160 AND sob.set_of_books_id = bc.set_of_books_id
161 AND sob.currency_code = cur.currency_code;
162
163 -- get period name for start period counter
164 l_ret_flag := igi_iac_common_utils.get_period_info_for_counter( l_get_proj.book_type_code,
165 l_get_proj.start_period_counter,
166 l_prd_rec );
167 l_start_period_name := l_prd_rec.period_name;
168
169 -- get period name for end period counter
170 l_ret_flag := igi_iac_common_utils.get_period_info_for_counter( l_get_proj.book_type_code,
171 l_get_proj.end_period,
172 l_prd_rec );
173
174 l_end_period_name := l_prd_rec.period_name;
175
176 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Start populating IAC Projections RXi interface table');
177
178 -- main loop for getting info for interface table
179 FOR l_proj_data IN c_proj_data(p_projection_id)
180 LOOP
181 l_count := l_count + 1;
182
183 l_rxi_proj_rec.reccount(l_count) := l_count;
184 l_rxi_proj_rec.projection_id(l_count) := p_projection_id;
185 l_rxi_proj_rec.book_type_code(l_count) := l_get_proj.book_type_code;
186 l_rxi_proj_rec.start_period(l_count) := l_start_period_name;
187 l_rxi_proj_rec.end_period(l_count) := l_end_period_name;
188 l_rxi_proj_rec.fiscal_year(l_count) := l_proj_data.fiscal_year;
189 l_rxi_proj_rec.asset_number(l_count) := l_proj_data.asset_number;
190 l_rxi_proj_rec.asset_desc(l_count) := l_proj_data.asset_desc;
191 l_rxi_proj_rec.latest_reval_cost(l_count) := l_proj_data.latest_reval_cost;
192 l_rxi_proj_rec.deprn_period(l_count) := l_proj_data.deprn_period;
193 l_rxi_proj_rec.deprn_ytd(l_count) := l_proj_data.deprn_ytd;
194
195 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Asset Number: '||l_proj_data.asset_number||' Reccount: '||l_count);
196
197 -- get period name for projection period counter
198 l_ret_flag := igi_iac_common_utils.get_period_info_for_counter( l_get_proj.book_type_code,
199 l_proj_data.proj_period_counter,
200 l_prd_rec );
201
202 l_rxi_proj_rec.proj_period(l_count) := l_prd_rec.period_name;
203 l_rxi_proj_rec.period_counter(l_count) := l_proj_data.proj_period_counter;
204
205 l_ret_flag := igi_iac_proj_pkg.get_price_index_val(p_book_code => l_get_proj.book_type_code,
206 p_category_id => l_proj_data.category_id,
207 p_period_ctr => l_proj_data.proj_period_counter,
208 p_price_index_val => l_rxi_proj_rec.periodic_index(l_count));
209
210
211 -- Get the reval period info
212 IF (l_prd_rec.period_num = l_get_proj.revaluation_period) THEN
213 l_reval_prd_ctr := l_proj_data.proj_period_counter;
214 l_reval_period_name := l_rxi_proj_rec.proj_period(l_count);
215 l_reval_idx_val := l_rxi_proj_rec.periodic_index(l_count);
216
217 ELSE
218 -- if this is the first period and is not a reval period
219 -- get the revaluation period and index for the start period counter
220 -- Get the previous revaluation period or the DPIS period if it does not exist
221
222 IF (l_proj_data.proj_period_counter = l_get_proj.start_period_counter) THEN
223
224 l_ret_flag := igi_iac_proj_pkg.get_reval_prd_dpis_ctr(l_get_proj.book_type_code,
225 l_proj_data.asset_id,
226 l_reval_prd_ctr);
227
228 l_ret_flag := igi_iac_common_utils.get_period_info_for_counter(l_get_proj.book_type_code,
229 l_reval_prd_ctr,
230 l_prd_rec );
231
232 l_reval_period_name := l_prd_rec.period_name;
233
234
235 l_ret_flag := igi_iac_proj_pkg.get_price_index_val(l_get_proj.book_type_code,
236 l_proj_data.category_id,
237 l_reval_prd_ctr,
238 l_reval_idx_val);
239 END IF;
240 END IF;
241
242 l_rxi_proj_rec.reval_index(l_count) := l_reval_idx_val;
243 l_rxi_proj_rec.reval_period_name(l_count) := l_reval_period_name;
244
245 -- get the company and cost center
246 l_rxi_proj_rec.company(l_count) := l_proj_data.company;
247 l_rxi_proj_rec.cost_center(l_count) := l_proj_data.cost_center;
248
249 -- get the category information
250 l_rxi_proj_rec.category_id(l_count) := l_proj_data.category_id;
251 l_rxi_proj_rec.category_desc(l_count) := l_proj_data.cat_desc;
252
253 -- get the major category and minor category values
254 l_rxi_proj_rec.major_category(l_count) := fa_rx_flex_pkg.get_value(
255 p_application_id => 140,
256 p_id_flex_code => 'CAT#',
257 p_id_flex_num => l_assetkey_struct,
258 p_qualifier => 'BASED_CATEGORY',
259 p_ccid => l_proj_data.category_id);
260 -- change the way the minor category is retrieved
261 -- get only the first qualified segment
262 /* l_min_cat:= fa_rx_flex_pkg.get_value(
263 p_application_id => 140,
264 p_id_flex_code => 'CAT#',
265 p_id_flex_num => l_assetkey_struct,
266 p_qualifier => 'MINOR_CATEGORY',
267 p_ccid => l_proj_data.category_id);
268
269 l_rxi_proj_rec.minor_category(l_count) := substr(l_min_cat, 0, instr(l_min_cat, 1, 1)-1);
270 */
271 begin
272 l_min_cat := fa_rx_flex_pkg.flex_sql(140,
273 'CAT#',
274 l_assetkey_struct,
275 'CAT',
276 'SELECT',
277 'MINOR_CATEGORY');
278 exception
279 when others then
280 l_min_cat := 'null';
281 end;
282
283 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Minor category segment: '||l_min_cat);
284
285 IF (l_min_cat IS NOT NULL) THEN
286 l_sql := 'SELECT ' ||l_min_cat||
287 ' FROM fa_categories cat
288 WHERE cat.category_id = :1';
289 /* Bug 3448431 */
290 execute immediate l_sql into l_rxi_proj_rec.minor_category(l_count) USING l_proj_data.category_id;
291 ELSE
292 l_rxi_proj_rec.minor_category(l_count) := null;
293 END IF;
294
295
296 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Minor category value: '||l_rxi_proj_rec.minor_category(l_count));
297
298 -- get the concatenated category name
299 fa_rx_shared_pkg.concat_category (
300 struct_id => l_cat_struct,
301 ccid => l_proj_data.category_id,
302 concat_string => l_concat_cat,
303 segarray => l_cat_segs);
304
305
306 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Category: '||l_concat_cat);
307 l_rxi_proj_rec.category(l_count) := l_concat_cat;
308
309 -- get the exception code description
310 l_rxi_proj_rec.exception_code(l_count) := l_proj_data.exception_code;
311
312 IF (l_proj_data.exception_code IS NOT NULL) THEN
313 SELECT meaning
314 INTO l_exception_desc
315 FROM IGI_LOOKUPS
316 WHERE lookup_type = 'IGI_IAC_PROJ_EXCEPTIONS'
317 AND lookup_code = l_proj_data.exception_code;
318 ELSE
319 l_exception_desc := NULL;
320 END IF;
321
322 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Exception: '||l_exception_desc);
323 l_rxi_proj_rec.exception_desc(l_count) := l_exception_desc;
324
325 END LOOP;
326
327 -- insert into the RXi interface table
328 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserting.......');
329
330 FORALL i IN l_rxi_proj_rec.reccount.FIRST .. l_rxi_proj_rec.reccount.LAST
331 INSERT INTO igi_iac_proj_rep_itf(
332 projection_id,
333 book_type_code,
334 start_period,
335 end_period,
336 proj_period,
337 fiscal_year,
338 periodic_index,
339 reval_index,
340 reval_period_name,
341 company,
342 cost_center,
343 category_id,
344 major_category,
345 minor_category,
346 category,
347 category_desc,
348 asset_number,
349 asset_desc,
350 deprn_period,
351 deprn_ytd,
352 latest_reval_cost,
353 exception_code,
354 exception_desc,
355 request_id,
356 set_of_books_id,
357 functional_currency_code,
358 organization_name,
359 created_by,
360 creation_date,
361 last_update_login,
362 last_updated_by ,
363 last_update_date,
364 period_counter
365 ) VALUES (
366 p_projection_id,
367 l_rxi_proj_rec.book_type_code(i),
368 l_rxi_proj_rec.start_period(i),
369 l_rxi_proj_rec.end_period(i),
370 l_rxi_proj_rec.proj_period(i),
371 l_rxi_proj_rec.fiscal_year(i),
372 l_rxi_proj_rec.periodic_index(i),
373 l_rxi_proj_rec.reval_index(i),
374 l_rxi_proj_rec.reval_period_name(i),
375 l_rxi_proj_rec.company(i),
376 l_rxi_proj_rec.cost_center(i),
377 l_rxi_proj_rec.category_id(i),
378 l_rxi_proj_rec.major_category(i),
379 l_rxi_proj_rec.minor_category(i),
380 l_rxi_proj_rec.category(i),
381 l_rxi_proj_rec.category_desc(i),
382 l_rxi_proj_rec.asset_number(i),
383 l_rxi_proj_rec.asset_desc(i),
384 l_rxi_proj_rec.deprn_period(i),
385 l_rxi_proj_rec.deprn_ytd(i),
386 l_rxi_proj_rec.latest_reval_cost(i),
387 l_rxi_proj_rec.exception_code(i),
388 l_rxi_proj_rec.exception_desc(i),
389 p_request_id,
390 l_sob_id,
391 l_currency_code,
392 l_organization_name,
393 l_user_id,
394 sysdate,
395 l_login_id,
396 l_user_id ,
397 sysdate,
398 l_rxi_proj_rec.period_counter(i)
399 );
400
401 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'End populating IAC Projections RXi interface table');
402
403 -- 02-Oct-2003, Bug 3140574, delete from main projections tables
404 DELETE FROM igi_iac_projections
405 WHERE projection_id = p_projection_id;
406
407 DELETE FROM igi_iac_proj_details
408 WHERE projection_id = p_projection_id;
409
410 retcode := 0;
411 errbuf := 'RXi Projections Inner Wrapper Successful!';
412 EXCEPTION
413 WHEN OTHERS THEN
414 retcode := 2;
415 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
416 FND_MESSAGE.SET_TOKEN('PACKAGE','igirx_iac_proj');
417 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','Inner Process unsuccessful!');
418
419 igi_iac_debug_pkg.debug_other_msg(g_unexp_level,l_path,FALSE);
420 errbuf := fnd_message.get;
421 igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Population of IAC Projections RXi interface table unsuccessful');
422 END proj;
423
424 END igirx_iac_proj; -- Package body