[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.5 2006/01/09 05:31:04 snarayan noship $ */
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 h_cost_clearing_acct_ccid,
307 h_num_of_segments,
308 h_concat_array_segments)) THEN
309
310 RAISE prepare_expense_ccid_error;
311 END IF;
312 -- Updating array with new account value
313 h_concat_array_segments(h_flex_segment_number) := h_new_deprn_exp_acct;
314
315 -- Retrieve new ccid with overlaid account
316 -- get_combination_id function generates new ccid if rules allows.
317 IF (NOT FND_FLEX_EXT.GET_COMBINATION_ID(
318 'SQLGL',
319 'GL#',
320 h_chart_of_accounts_id,
321 SYSDATE,
322 h_num_of_segments,
323 h_concat_array_segments,
324 l_target_ccid)) THEN
325
326 h_delimiter := FND_FLEX_APIS.get_segment_delimiter(
327 101,
328 'GL#',
329 h_chart_of_accounts_id);
330
331 -- fill the string for messaging with concat segs...
332
333 while (h_num < h_num_of_segments) loop
334
335 h_num := h_num + 1;
336
337 if (h_num > 1) then
338 h_concat_segs := h_concat_segs ||
339 h_delimiter;
340 end if;
341
342 h_concat_segs := h_concat_segs ||
343 h_concat_array_segments(h_num);
344
345 end loop;
346
347 h_errmsg := FND_FLEX_EXT.GET_ENCODED_MESSAGE;
348
349
350 FA_SRVR_MSG.ADD_MESSAGE
351 (CALLING_FN=>'FAFLEX_PKG_WF.START_PROCESS',
352 NAME=>'FA_FLEXBUILDER_FAIL_CCID',
353 TOKEN1 => 'ACCOUNT_TYPE',
354 VALUE1 => 'DEPRN_EXP',
355 TOKEN2 => 'BOOK_TYPE_CODE',
356 VALUE2 => px_mass_add_rec.book_type_code,
357 TOKEN3 => 'DIST_ID',
358 VALUE3 => 'NEW',
359 TOKEN4 => 'CONCAT_SEGS',
360 VALUE4 => h_concat_segs
361 );
362
363 fnd_message.set_encoded(h_errmsg);
364 fnd_msg_pub.add;
365
366
367
368 RAISE prepare_expense_ccid_error;
369 END IF;
370 l_debug_str := 'Generated Expense CCID '|| to_char(l_target_ccid);
371 if (p_log_level_rec.statement_level) then
372 fa_debug_pkg.add(l_calling_fn,
373 l_debug_str,
374 '',
375 p_log_level_rec => p_log_level_rec);
376 end if;
377
378 /*commented for bug 4928682
379 l_plsql_block := 'begin
380 fa_xla_tab_pkg.WRITE_ONLINE_TAB(
381 P_API_VERSION => :l_API_VERSION,
382 P_SOURCE_DISTRIB_ID_NUM_1 => :l_SOURCE_DISTRIB_ID_NUM_1,
383 P_SOURCE_DISTRIB_ID_NUM_2 => :l_SOURCE_DISTRIB_ID_NUM_2,
384 P_SOURCE_DISTRIB_ID_NUM_3 => :l_SOURCE_DISTRIB_ID_NUM_3,
385 P_SOURCE_DISTRIB_ID_NUM_4 => :l_SOURCE_DISTRIB_ID_NUM_4,
386 P_SOURCE_DISTRIB_ID_NUM_5 => :l_SOURCE_DISTRIB_ID_NUM_5,
387 P_ACCOUNT_TYPE_CODE => :l_ACCOUNT_TYPE_CODE,
388 DEFAULT_CCID => :l_default_ccid,
389 DEPRN_EXPENSE_ACCOUNT_CCID => :l_DEPRN_EXPENSE_ACCT_CCID,
390 PAYABLES_CCID => :l_PAYABLES_CCID,
391 X_RETURN_STATUS => :l_RETURN_STATUS,
392 X_MSG_COUNT => :l_MSG_COUNT ,
393 X_MSG_DATA => :l_MSG_DATA);
394 end;';
395
396 EXECUTE IMMEDIATE l_plsql_block
397 USING l_API_VERSION, l_SOURCE_DISTRIB_ID_NUM_1, l_SOURCE_DISTRIB_ID_NUM_2, l_SOURCE_DISTRIB_ID_NUM_3,
398 l_SOURCE_DISTRIB_ID_NUM_4, l_SOURCE_DISTRIB_ID_NUM_5, l_ACCOUNT_TYPE_CODE,
399 l_default_ccid,l_DEPRN_EXPENSE_ACCT_CCID,
400 l_PAYABLES_CCID, out l_RETURN_STATUS, out l_MSG_COUNT, out l_MSG_DATA;
401
402 l_plsql_block := 'begin
403 FA_XLA_TAB_PKG.run(
404 P_API_VERSION => :l_API_VERSION,
405 P_ACCOUNT_DEFINITION_TYPE_CODE => :l_ACCOUNT_DEFINITION_TYPE_CODE,
406 P_ACCOUNT_DEFINITION_CODE => :l_ACCOUNT_DEFINITION_CODE,
407 P_TRANSACTION_COA_ID => :l_TRANSACTION_COA_ID ,
408 P_MODE => :l_MODE,
409 X_RETURN_STATUS => :l_RETURN_STATUS,
410 X_MSG_COUNT => :l_MSG_COUNT,
411 X_MSG_DATA => :l_MSG_DATA)
412 end;';
413
414 EXECUTE IMMEDIATE l_plsql_block
415 USING l_API_VERSION, l_ACCOUNT_DEFINITION_TYPE_CODE, l_ACCOUNT_DEFINITION_CODE, l_TRANSACTION_COA_ID,
416 l_MODE, out l_RETURN_STATUS, out l_MSG_COUNT, out l_MSG_DATA;
417
418 l_plsql_block := 'begin
419 fa_xla_tab_pkg.READ_ONLINE_TAB(
420 P_API_VERSION => :l_API_VERSION,
421 P_SOURCE_DISTRIB_ID_NUM_1 => :l_SOURCE_DISTRIB_ID_NUM_1,
422 P_SOURCE_DISTRIB_ID_NUM_2 => :l_SOURCE_DISTRIB_ID_NUM_2,
423 P_SOURCE_DISTRIB_ID_NUM_3 => :l_SOURCE_DISTRIB_ID_NUM_3,
424 P_SOURCE_DISTRIB_ID_NUM_4 => :l_SOURCE_DISTRIB_ID_NUM_4,
425 P_SOURCE_DISTRIB_ID_NUM_5 => :l_SOURCE_DISTRIB_ID_NUM_5,
426 P_ACCOUNT_TYPE_CODE => :l_ACCOUNT_TYPE_CODE,
427 X_TARGET_CCID => :l_TARGET_CCID,
428 X_CONCATENATED_SEGMENTS => :l_CONCATENATED_SEGMENTS,
429 X_RETURN_STATUS => :l_RETURN_STATUS,
430 X_MSG_COUNT => :l_MSG_COUNT ,
431 X_MSG_DATA => :l_MSG_DATA)
432 end;';
433
434 EXECUTE IMMEDIATE l_plsql_block
435 USING l_API_VERSION, l_SOURCE_DISTRIB_ID_NUM_1, l_SOURCE_DISTRIB_ID_NUM_2, l_SOURCE_DISTRIB_ID_NUM_3,
436 l_SOURCE_DISTRIB_ID_NUM_4, l_SOURCE_DISTRIB_ID_NUM_5, l_ACCOUNT_TYPE_CODE, out l_TARGET_CCID,
437 out l_CONCATENATED_SEGMENTS, out l_RETURN_STATUS, out l_MSG_COUNT, out l_MSG_DATA;
438 */
439
440 px_mass_add_rec.expense_code_combination_id := l_target_ccid;
441
442 return true;
443 exception
444 when prepare_expense_ccid_error then
445 l_debug_str := 'Unable to generate the Expense Account';
446 if (p_log_level_rec.statement_level) then
447 fa_debug_pkg.add(l_calling_fn,
448 l_debug_str,
449 '',
450 p_log_level_rec => p_log_level_rec);
451 end if;
452
453 return false;
454 when no_data_found then
455 return false;
456 when too_many_rows then
457 return false;
458 when others then
459 return false;
460
461 end;
462
463 function prepare_attributes(px_mass_add_rec IN out NOCOPY FA_MASSADD_PREPARE_PKG.mass_add_rec,
464 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
465 return boolean is
466
467 Result boolean;
468 l_status number;
469 l_debug_str varchar2(1000);
470 l_calling_fn varchar2(40) := 'prepare_attributes';
471 begin
472
473 if (px_mass_add_rec.asset_category_id is null) then
474 l_debug_str := 'calling prepare_asset_category_id';
475 if (p_log_level_rec.statement_level) then
476 fa_debug_pkg.add(l_calling_fn,
477 l_debug_str,
478 '',
479 p_log_level_rec => p_log_level_rec);
480 end if;
481 if not
482 prepare_asset_category_id(px_mass_add_rec,
483 p_log_level_rec => p_log_level_rec) then
484 null;
485 end if;
486 end if;
487
488 if (px_mass_add_rec.expense_code_combination_id is null) then
489 l_debug_str := 'Calling prepare_expense_ccid';
490 if (p_log_level_rec.statement_level) then
491 fa_debug_pkg.add(l_calling_fn,
492 l_debug_str,
493 '',
494 p_log_level_rec => p_log_level_rec);
495 end if;
496 if not prepare_expense_ccid(px_mass_add_rec,
497 p_log_level_rec => p_log_level_rec) then
498 l_debug_str := 'Default prepare_expense_ccid returned failure';
499 if (p_log_level_rec.statement_level) then
500 fa_debug_pkg.add(l_calling_fn,
501 l_debug_str,
502 '',
503 p_log_level_rec => p_log_level_rec);
504 end if;
505 end if;
506 end if;
507 Result := True;
508 return(Result);
509 exception
510 when others then
511 return false;
512 end prepare_attributes;
513
514 end FA_MASSADD_PREP_DEFAULT_PKG;