[Home] [Help]
PACKAGE BODY: APPS.FA_MASSADD_PREP_DEFAULT_PKG
Source
1 package body FA_MASSADD_PREP_DEFAULT_PKG as
2 /* $Header: FAMAPREPDB.pls 120.6 2009/06/12 13:48:52 pvataj ship $ */
3
4 -- Private type declarations
5
6 -- Private constant declarations
7
8 -- Private variable declarations
9 -- Function and procedure implementations
10 function prepare_asset_category_id(px_mass_add_rec IN out NOCOPY FA_MASSADD_PREPARE_PKG.mass_add_rec,
11 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
12 return boolean is
13 l_gl_ccid_rec GL_CODE_COMBINATIONS%ROWTYPE;
14 l_mass_add_rec FA_MASSADD_PREPARE_PKG.mass_add_rec;
15 l_debug_str varchar2(500);
16 TYPE varchar30_tbl IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
17 TYPE num_tbl IS TABLE OF number INDEX BY BINARY_INTEGER;
18 l_seg_num num_tbl;
19 l_segment varchar30_tbl;
20 l_major_category varchar2(50);
21 l_minor_category varchar2(50);
22 l_major_index number;
23 l_minor_index number;
24 l_major_ccid number;
25 l_minor_ccid number;
26 l_clearing_acct_ccid number;
27 l_category_id number;
28 l_gl_ccid_enabled_flag varchar2(1);
29 l_ACCOUNTING_FLEX_STRUCTURE fa_book_controls.accounting_flex_structure%type;
30 l_bal_seg_num NUMBER;
31 l_lookup_code varchar2(30);
32 h_chart_of_accounts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
33 l_account_segment varchar2(30);
34 l_calling_fn varchar2(40) := 'prepare_asset_category_id';
35 h_flex_segment_number number;
36 l_asset_clearing_acct varchar2(25);
37 begin
38
39 Select sob.chart_of_accounts_id
40 into h_chart_of_accounts_id
41 From fa_book_controls bc, gl_sets_of_books sob
42 Where sob.set_of_books_id = bc.set_of_books_id
43 And bc.book_type_code = px_mass_add_rec.book_type_code;
44
45 if (px_mass_add_rec.PAYABLES_CODE_COMBINATION_ID is not null) then
46 l_debug_str := 'Get the Accounting Flex Field';
47 if (p_log_level_rec.statement_level) then
48 fa_debug_pkg.add(l_calling_fn,
49 l_debug_str,
50 '',
51 p_log_level_rec => p_log_level_rec);
52 end if;
53 for i in 1 .. 30 loop
54 l_segment(i) := null;
55 l_seg_num(i) := -1;
56 end loop;
57 l_debug_str := 'Get the Category Mapping';
58 if (p_log_level_rec.statement_level) then
59 fa_debug_pkg.add(l_calling_fn,
60 l_debug_str,
61 '',
62 p_log_level_rec => p_log_level_rec);
63 end if;
64 select ACCOUNTING_FLEX_STRUCTURE
65 into l_ACCOUNTING_FLEX_STRUCTURE
66 from fa_book_controls
67 where book_type_code = px_mass_add_rec.BOOK_TYPE_CODE;
68
69 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
70 101,
71 'GL#',
72 h_chart_of_accounts_id,
73 'GL_ACCOUNT',
74 h_flex_segment_number)) THEN
75 null;
76 end if;
77
78 l_seg_num(h_flex_segment_number) := 1;
79
80 select Segment1,
81 Segment2,
82 Segment3,
83 Segment4,
84 Segment5,
85 Segment6,
86 Segment7,
87 Segment8,
88 Segment9,
89 Segment10,
90 Segment11,
91 Segment12,
92 Segment13,
93 Segment14,
94 Segment15,
95 Segment16,
96 Segment17,
97 Segment18,
98 Segment19,
99 Segment20,
100 Segment21,
101 Segment22,
102 Segment23,
103 Segment24,
104 Segment25,
105 Segment26,
106 Segment27,
107 Segment28,
108 Segment29,
109 Segment30,
110 enabled_flag
111 into l_segment(1),
112 l_segment(2),
113 l_segment(3),
114 l_segment(4),
115 l_segment(5),
116 l_segment(6),
117 l_segment(7),
118 l_segment(8),
119 l_segment(9),
120 l_segment(10),
121 l_segment(11),
122 l_segment(12),
123 l_segment(13),
124 l_segment(14),
125 l_segment(15),
126 l_segment(16),
127 l_segment(17),
128 l_segment(18),
129 l_segment(19),
130 l_segment(20),
131 l_segment(21),
132 l_segment(22),
133 l_segment(23),
134 l_segment(24),
135 l_segment(25),
136 l_segment(26),
137 l_segment(27),
138 l_segment(28),
139 l_segment(29),
140 l_segment(30),
141 l_gl_ccid_enabled_flag
142 from gl_code_combinations
143 where code_combination_id =
144 px_mass_add_rec.payables_code_combination_id
145 and CHART_OF_ACCOUNTS_ID = h_chart_of_accounts_id;
146
147 for i in 1 .. 30 loop
148 IF (l_seg_num(i) < 0) THEN
149 l_segment(i) := null;
150 END IF;
151 end loop;
152
153 for i in 1 .. 30 loop
154 IF (i = h_flex_segment_number) THEN
155 l_asset_clearing_acct := l_segment(i);
156 END IF;
157 end loop;
158
159 if (px_mass_add_rec.asset_type = 'CAPITALIZED') then
160 l_debug_str := 'Get the Clearing Account for Capitalized Assets';
161 if (p_log_level_rec.statement_level) then
162 fa_debug_pkg.add(l_calling_fn,
163 l_debug_str,
164 '',
165 p_log_level_rec => p_log_level_rec);
166 end if;
167 l_debug_str := 'Get the Category for Capitalized Assets';
168 if (p_log_level_rec.statement_level) then
169 fa_debug_pkg.add(l_calling_fn,
170 l_debug_str,
171 '',
172 p_log_level_rec => p_log_level_rec);
173 end if;
174
175 select category_id
176 into px_mass_add_rec.ASSET_CATEGORY_ID
177 from fa_category_books
178 where ASSET_CLEARING_ACCT = l_asset_clearing_acct
179 and book_type_code = px_mass_add_rec.book_type_code
180 and rownum = 1;
181
182 elsif (px_mass_add_rec.asset_type = 'CIP') then
183 l_debug_str := 'Get the Clearing Account for CIP Assets';
184 if (p_log_level_rec.statement_level) then
185 fa_debug_pkg.add(l_calling_fn,
186 l_debug_str,
187 '',
188 p_log_level_rec => p_log_level_rec);
189 end if;
190 select category_id
191 into px_mass_add_rec.ASSET_CATEGORY_ID
192 from fa_category_books
193 where CIP_CLEARING_ACCT = l_asset_clearing_acct
194 and book_type_code = px_mass_add_rec.book_type_code
195 and rownum = 1;
196 END IF;
197
198 end if;
199 return true;
200 exception
201 when too_many_rows then
202 return false;
203 when no_data_found then
204 return false;
205 when others then
206 return false;
207 end;
208
209 /*********************Prepare Expense Account******************************************/
210 function prepare_expense_ccid(px_mass_add_rec IN out NOCOPY FA_MASSADD_PREPARE_PKG.mass_add_rec,
211 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
212 return boolean is
213 l_exp_acct_col_name fnd_id_flex_segments.application_column_name%TYPE;
214 TYPE varchar30_tbl IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
215 l_segment varchar30_tbl;
216 l_exp_acct_index number;
217 l_exp_acct_ccid number;
218 l_gl_ccid_enabled_flag varchar2(1);
219 l_ACCOUNTING_FLEX_STRUCTURE fa_book_controls.accounting_flex_structure%type;
220 l_calling_fn varchar2(40) := 'prepare_expense_ccid';
221 h_chart_of_accounts_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
222 h_flex_segment_delimiter varchar2(5);
223 h_flex_segment_number number;
224 h_num_of_segments NUMBER;
225 h_concat_array_segments FND_FLEX_EXT.SEGMENTARRAY;
226 h_new_deprn_exp_acct VARCHAR2(26);
227 h_cost_acct_ccid NUMBER := 0;
228 h_cost_clearing_acct_ccid NUMBER := 0;
229 l_API_VERSION NUMBER := 1.0;
230 l_SOURCE_DISTRIB_ID_NUM_1 NUMBER;
231 l_SOURCE_DISTRIB_ID_NUM_2 NUMBER;
232 l_SOURCE_DISTRIB_ID_NUM_3 NUMBER;
233 l_SOURCE_DISTRIB_ID_NUM_4 NUMBER;
234 l_SOURCE_DISTRIB_ID_NUM_5 NUMBER;
235 l_ACCOUNT_TYPE_CODE VARCHAR2(30) := 'FA_EXPENSE_ACCT';
236 l_DEPRN_EXPENSE_ACCT_CCID NUMBER;
237 l_PAYABLES_CCID NUMBER;
238 l_default_ccid number;
239
240 l_ACCOUNT_DEFINITION_TYPE_CODE VARCHAR2(30) := 'S';
241 l_ACCOUNT_DEFINITION_CODE VARCHAR2(30) := 'FA_EXPENSE_ACCT2';
242 l_TRANSACTION_COA_ID NUMBER := 101;
243 l_MODE VARCHAR2(30) := 'ONLINE';
244 l_RETURN_STATUS VARCHAR2(1);
245 l_MSG_COUNT NUMBER;
246 l_MSG_DATA VARCHAR2(255);
247
248 l_TARGET_CCID NUMBER;
249 l_CONCATENATED_SEGMENTS VARCHAR2(4000);
250 l_plsql_block varchar2(1000);
251
252 h_num number := 0;
253 h_string varchar2(100);
254 h_errmsg varchar2(512);
255 h_concat_segs varchar2(2000) := '';
256 h_delimiter varchar2(1);
257 l_debug_str varchar2(500);
258 prepare_expense_ccid_error EXCEPTION;
259 begin
260 /*commented for bug 4928682
261 select FLEXBUILDER_DEFAULTS_CCID
262 into l_default_ccid
263 from fa_book_controls
264 where book_type_code = px_mass_add_rec.book_type_code;
265 */
266 SELECT deprn_expense_acct,
267 asset_cost_account_ccid,
268 ASSET_CLEARING_ACCOUNT_CCID
269 INTO h_new_deprn_exp_acct,
270 h_cost_acct_ccid,
271 h_cost_clearing_acct_ccid
272 FROM
273 fa_category_books
274 WHERE book_type_code = px_mass_add_rec.book_type_code
275 AND category_id = px_mass_add_rec.asset_category_id;
276
277 -- Get Chart of Accounts ID
278 Select sob.chart_of_accounts_id
279 into h_chart_of_accounts_id
280 From fa_book_controls bc,
281 gl_sets_of_books sob
282 Where sob.set_of_books_id = bc.set_of_books_id
283 And bc.book_type_code = px_mass_add_rec.book_type_code;
284
285 /* Use the clearing ccid and then overlay the account segment with deprn exp acct from
286 the category which will be returned when there is 1 to 1 setup in terms of category and
287 clearing account in Asset Clearing acount.*/
288
289 l_TRANSACTION_COA_ID := h_chart_of_accounts_id;
290
291 l_PAYABLES_CCID := px_mass_add_rec.payables_code_combination_id;
292
293 -- Get Account Qualifier Segment Number
294 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
295 101,
296 'GL#',
297 h_chart_of_accounts_id,
298 'GL_ACCOUNT',
299 h_flex_segment_number)) THEN
300 RAISE prepare_expense_ccid_error;
301 END IF;
302 -- Retreive segments
303 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL',
304 'GL#',
305 h_chart_of_accounts_id,
306 --bug 8596581 h_cost_clearing_acct_ccid,
307 l_PAYABLES_CCID,
308 h_num_of_segments,
309 h_concat_array_segments)) THEN
310
311 RAISE prepare_expense_ccid_error;
312 END IF;
313 -- Updating array with new account value
314 h_concat_array_segments(h_flex_segment_number) := h_new_deprn_exp_acct;
315
316 -- Retrieve new ccid with overlaid account
317 -- get_combination_id function generates new ccid if rules allows.
318 IF (NOT FND_FLEX_EXT.GET_COMBINATION_ID(
319 'SQLGL',
320 'GL#',
321 h_chart_of_accounts_id,
322 SYSDATE,
323 h_num_of_segments,
324 h_concat_array_segments,
325 l_target_ccid)) THEN
326
327 h_delimiter := FND_FLEX_APIS.get_segment_delimiter(
328 101,
329 'GL#',
330 h_chart_of_accounts_id);
331
332 -- fill the string for messaging with concat segs...
333
334 while (h_num < h_num_of_segments) loop
335
336 h_num := h_num + 1;
337
338 if (h_num > 1) then
339 h_concat_segs := h_concat_segs ||
340 h_delimiter;
341 end if;
342
343 h_concat_segs := h_concat_segs ||
344 h_concat_array_segments(h_num);
345
346 end loop;
347
348 h_errmsg := FND_FLEX_EXT.GET_ENCODED_MESSAGE;
349
350
351 FA_SRVR_MSG.ADD_MESSAGE
352 (CALLING_FN=>'FAFLEX_PKG_WF.START_PROCESS',
353 NAME=>'FA_FLEXBUILDER_FAIL_CCID',
354 TOKEN1 => 'ACCOUNT_TYPE',
355 VALUE1 => 'DEPRN_EXP',
356 TOKEN2 => 'BOOK_TYPE_CODE',
357 VALUE2 => px_mass_add_rec.book_type_code,
358 TOKEN3 => 'DIST_ID',
359 VALUE3 => 'NEW',
360 TOKEN4 => 'CONCAT_SEGS',
361 VALUE4 => h_concat_segs
362 );
363
364 fnd_message.set_encoded(h_errmsg);
365 fnd_msg_pub.add;
366
367
368
369 RAISE prepare_expense_ccid_error;
370 END IF;
371 l_debug_str := 'Generated Expense CCID '|| to_char(l_target_ccid);
372 if (p_log_level_rec.statement_level) then
373 fa_debug_pkg.add(l_calling_fn,
374 l_debug_str,
375 '',
376 p_log_level_rec => p_log_level_rec);
377 end if;
378
379 /*commented for bug 4928682
380 l_plsql_block := 'begin
381 fa_xla_tab_pkg.WRITE_ONLINE_TAB(
382 P_API_VERSION => :l_API_VERSION,
383 P_SOURCE_DISTRIB_ID_NUM_1 => :l_SOURCE_DISTRIB_ID_NUM_1,
384 P_SOURCE_DISTRIB_ID_NUM_2 => :l_SOURCE_DISTRIB_ID_NUM_2,
385 P_SOURCE_DISTRIB_ID_NUM_3 => :l_SOURCE_DISTRIB_ID_NUM_3,
386 P_SOURCE_DISTRIB_ID_NUM_4 => :l_SOURCE_DISTRIB_ID_NUM_4,
387 P_SOURCE_DISTRIB_ID_NUM_5 => :l_SOURCE_DISTRIB_ID_NUM_5,
388 P_ACCOUNT_TYPE_CODE => :l_ACCOUNT_TYPE_CODE,
389 DEFAULT_CCID => :l_default_ccid,
390 DEPRN_EXPENSE_ACCOUNT_CCID => :l_DEPRN_EXPENSE_ACCT_CCID,
391 PAYABLES_CCID => :l_PAYABLES_CCID,
392 X_RETURN_STATUS => :l_RETURN_STATUS,
393 X_MSG_COUNT => :l_MSG_COUNT ,
394 X_MSG_DATA => :l_MSG_DATA);
395 end;';
396
397 EXECUTE IMMEDIATE l_plsql_block
398 USING l_API_VERSION, l_SOURCE_DISTRIB_ID_NUM_1, l_SOURCE_DISTRIB_ID_NUM_2, l_SOURCE_DISTRIB_ID_NUM_3,
399 l_SOURCE_DISTRIB_ID_NUM_4, l_SOURCE_DISTRIB_ID_NUM_5, l_ACCOUNT_TYPE_CODE,
400 l_default_ccid,l_DEPRN_EXPENSE_ACCT_CCID,
401 l_PAYABLES_CCID, out l_RETURN_STATUS, out l_MSG_COUNT, out l_MSG_DATA;
402
403 l_plsql_block := 'begin
404 FA_XLA_TAB_PKG.run(
405 P_API_VERSION => :l_API_VERSION,
406 P_ACCOUNT_DEFINITION_TYPE_CODE => :l_ACCOUNT_DEFINITION_TYPE_CODE,
407 P_ACCOUNT_DEFINITION_CODE => :l_ACCOUNT_DEFINITION_CODE,
408 P_TRANSACTION_COA_ID => :l_TRANSACTION_COA_ID ,
409 P_MODE => :l_MODE,
410 X_RETURN_STATUS => :l_RETURN_STATUS,
411 X_MSG_COUNT => :l_MSG_COUNT,
412 X_MSG_DATA => :l_MSG_DATA)
413 end;';
414
415 EXECUTE IMMEDIATE l_plsql_block
416 USING l_API_VERSION, l_ACCOUNT_DEFINITION_TYPE_CODE, l_ACCOUNT_DEFINITION_CODE, l_TRANSACTION_COA_ID,
417 l_MODE, out l_RETURN_STATUS, out l_MSG_COUNT, out l_MSG_DATA;
418
419 l_plsql_block := 'begin
420 fa_xla_tab_pkg.READ_ONLINE_TAB(
421 P_API_VERSION => :l_API_VERSION,
422 P_SOURCE_DISTRIB_ID_NUM_1 => :l_SOURCE_DISTRIB_ID_NUM_1,
423 P_SOURCE_DISTRIB_ID_NUM_2 => :l_SOURCE_DISTRIB_ID_NUM_2,
424 P_SOURCE_DISTRIB_ID_NUM_3 => :l_SOURCE_DISTRIB_ID_NUM_3,
425 P_SOURCE_DISTRIB_ID_NUM_4 => :l_SOURCE_DISTRIB_ID_NUM_4,
426 P_SOURCE_DISTRIB_ID_NUM_5 => :l_SOURCE_DISTRIB_ID_NUM_5,
427 P_ACCOUNT_TYPE_CODE => :l_ACCOUNT_TYPE_CODE,
428 X_TARGET_CCID => :l_TARGET_CCID,
429 X_CONCATENATED_SEGMENTS => :l_CONCATENATED_SEGMENTS,
430 X_RETURN_STATUS => :l_RETURN_STATUS,
431 X_MSG_COUNT => :l_MSG_COUNT ,
432 X_MSG_DATA => :l_MSG_DATA)
433 end;';
434
435 EXECUTE IMMEDIATE l_plsql_block
436 USING l_API_VERSION, l_SOURCE_DISTRIB_ID_NUM_1, l_SOURCE_DISTRIB_ID_NUM_2, l_SOURCE_DISTRIB_ID_NUM_3,
437 l_SOURCE_DISTRIB_ID_NUM_4, l_SOURCE_DISTRIB_ID_NUM_5, l_ACCOUNT_TYPE_CODE, out l_TARGET_CCID,
438 out l_CONCATENATED_SEGMENTS, out l_RETURN_STATUS, out l_MSG_COUNT, out l_MSG_DATA;
439 */
440
441 px_mass_add_rec.expense_code_combination_id := l_target_ccid;
442
443 return true;
444 exception
445 when prepare_expense_ccid_error then
446 l_debug_str := 'Unable to generate the Expense Account';
447 if (p_log_level_rec.statement_level) then
448 fa_debug_pkg.add(l_calling_fn,
449 l_debug_str,
450 '',
451 p_log_level_rec => p_log_level_rec);
452 end if;
453
454 return false;
455 when no_data_found then
456 return false;
457 when too_many_rows then
458 return false;
459 when others then
460 return false;
461
462 end;
463
464 function prepare_attributes(px_mass_add_rec IN out NOCOPY FA_MASSADD_PREPARE_PKG.mass_add_rec,
465 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
466 return boolean is
467
468 Result boolean;
469 l_status number;
470 l_debug_str varchar2(1000);
471 l_calling_fn varchar2(40) := 'prepare_attributes';
472 begin
473
474 if (px_mass_add_rec.asset_category_id is null) then
475 l_debug_str := 'calling prepare_asset_category_id';
476 if (p_log_level_rec.statement_level) then
477 fa_debug_pkg.add(l_calling_fn,
478 l_debug_str,
479 '',
480 p_log_level_rec => p_log_level_rec);
481 end if;
482 if not
483 prepare_asset_category_id(px_mass_add_rec,
484 p_log_level_rec => p_log_level_rec) then
485 null;
486 end if;
487 end if;
488
489 if (px_mass_add_rec.expense_code_combination_id is null) then
490 l_debug_str := 'Calling prepare_expense_ccid';
491 if (p_log_level_rec.statement_level) then
492 fa_debug_pkg.add(l_calling_fn,
493 l_debug_str,
494 '',
495 p_log_level_rec => p_log_level_rec);
496 end if;
497 if not prepare_expense_ccid(px_mass_add_rec,
498 p_log_level_rec => p_log_level_rec) then
499 l_debug_str := 'Default prepare_expense_ccid returned failure';
500 if (p_log_level_rec.statement_level) then
501 fa_debug_pkg.add(l_calling_fn,
502 l_debug_str,
503 '',
504 p_log_level_rec => p_log_level_rec);
505 end if;
506 end if;
507 end if;
508 Result := True;
509 return(Result);
510 exception
511 when others then
512 return false;
513 end prepare_attributes;
514
515 end FA_MASSADD_PREP_DEFAULT_PKG;