[Home] [Help]
PACKAGE BODY: APPS.FA_XLA_CMP_CCID_PKG
Source
1 PACKAGE BODY fa_xla_cmp_ccid_pkg AS
2 /* $Header: faxlacib.pls 120.6.12010000.1 2008/07/28 13:26:36 appldev ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | fa_xla_cmp_ccid_pkg |
10 | |
11 | DESCRIPTION |
12 | This is a FA private package, which contains all the APIs required |
13 | for to create ccid extract for each extract type |
14 | |
15 | |
16 | HISTORY |
17 | 25-FEB-2006 BRIDGWAY Created |
18 | |
19 +===========================================================================*/
20
21
22 --+============================================+
23 --| |
24 --| PRIVATE PROCEDURES/FUNCTIONS |
25 --| |
26 --+============================================+
27
28
29 C_PRIVATE_API_1 CONSTANT VARCHAR2(32000) := '
30
31 /*======================================================================+
32 | |
33 | Private Function |
34 | Load_Generated_Ccids |
35 | |
36 +======================================================================*/
37
38 ----------------------------------------------------
39 --
40 -- Account Generator Hook
41 --
42 ----------------------------------------------------
43 PROCEDURE Load_Generated_Ccids
44 (p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
45
46 l_mesg_count number := 0;
47 l_mesg_len number;
48 l_mesg varchar2(4000);
49
50 l_procedure_name varchar2(80) := ''fa_xla_extract_def_pkg.load_generated_ccids''; -- BMR make this dynamic on type
51
52 type char_tab_type is table of varchar2(64) index by binary_integer;
53 type num_tab_type is table of number index by binary_integer;
54
55
56 ';
57
58 C_PRIVATE_API_DEPRN CONSTANT VARCHAR2(32000) := '
59
60 type deprn_rec_type is record
61 (rowid VARCHAR2(64),
62 book_type_code VARCHAR2(15),
63 distribution_id NUMBER(15),
64 distribution_ccid NUMBER(15),
65 deprn_entered_amount NUMBER,
66 bonus_entered_amount NUMBER,
67 reval_entered_amount NUMBER,
68 generated_ccid NUMBER(15),
69 generated_offset_ccid NUMBER(15),
70 bonus_generated_ccid NUMBER(15),
71 bonus_generated_offset_ccid NUMBER(15),
72 reval_generated_ccid NUMBER(15),
73 reval_generated_offset_ccid NUMBER(15),
74 -- DEPRN_EXPENSE_ACCOUNT_CCID NUMBER(15),
75 DEPRN_RESERVE_ACCOUNT_CCID NUMBER(15),
76 --BONUS_EXP_ACCOUNT_CCID NUMBER(15),
77 BONUS_RSV_ACCOUNT_CCID NUMBER(15),
78 REVAL_AMORT_ACCOUNT_CCID NUMBER(15),
79 REVAL_RSV_ACCOUNT_CCID NUMBER(15),
80 DEPRN_EXPENSE_ACCT VARCHAR2(25),
81 DEPRN_RESERVE_ACCT VARCHAR2(25),
82 BONUS_DEPRN_EXPENSE_ACCT VARCHAR2(25),
83 BONUS_RESERVE_ACCT VARCHAR2(25),
84 REVAL_AMORT_ACCT VARCHAR2(25),
85 REVAL_RESERVE_ACCT VARCHAR2(25)
86 );
87
88 type deprn_tbl_type is table of deprn_rec_type index by binary_integer;
89
90 l_deprn_tbl deprn_tbl_type;
91
92 l_generated_ccid num_tab_type;
93 l_generated_offset_ccid num_tab_type;
94 l_bonus_generated_ccid num_tab_type;
95 l_bonus_generated_offset_ccid num_tab_type;
96 l_reval_generated_ccid num_tab_type;
97 l_reval_generated_offset_ccid num_tab_type;
98 l_rowid char_tab_type;
99
100 l_last_book varchar2(15) := '' '';
101
102 cursor c_deprn is
103 select /*+ leading(xg) index(xb, FA_XLA_EXT_HEADERS_B_GT_U1) index(xl, FA_XLA_EXT_LINES_B_GT_U1) */
104 xl.rowid,
105 xb.book_type_code,
106 xl.distribution_id,
107 xl.EXPENSE_ACCOUNT_CCID,
108 xl.entered_amount,
109 xl.bonus_entered_amount,
110 xl.reval_entered_amount,
111 nvl(xl.GENERATED_CCID, da.DEPRN_EXPENSE_ACCOUNT_CCID),
112 nvl(xl.GENERATED_OFFSET_CCID, da.DEPRN_RESERVE_ACCOUNT_CCID),
113 nvl(xl.BONUS_GENERATED_CCID, da.BONUS_EXP_ACCOUNT_CCID),
114 nvl(xl.BONUS_GENERATED_OFFSET_CCID, da.BONUS_RSV_ACCOUNT_CCID),
115 nvl(xl.REVAL_GENERATED_CCID, da.REVAL_AMORT_ACCOUNT_CCID),
116 nvl(xl.REVAL_GENERATED_OFFSET_CCID, da.REVAL_RSV_ACCOUNT_CCID),
117 -- xl.DEPRN_EXPENSE_ACCOUNT_CCID,
118 xl.RESERVE_ACCOUNT_CCID,
119 -- xl.BONUS_EXP_ACCOUNT_CCID,
120 xl.BONUS_RESERVE_ACCT_CCID,
121 xl.REVAL_AMORT_ACCOUNT_CCID,
122 xl.REVAL_RESERVE_ACCOUNT_CCID,
123 xl.deprn_expense_acct,
124 xl.DEPRN_RESERVE_ACCT,
125 xl.bonus_deprn_expense_acct,
126 xl.BONUS_RESERVE_ACCT,
127 xl.REVAL_AMORT_ACCT,
128 xl.REVAL_RESERVE_ACCT
129 from xla_events_gt xg,
130 fa_xla_ext_headers_b_gt xb,
131 fa_xla_ext_lines_b_gt xl,
132 fa_distribution_accounts da
133 where xg.event_class_code = ''DEPRECIATION''
134 and xb.event_id = xg.event_id
135 and xl.event_id = xg.event_id
136 and xl.distribution_id = da.distribution_id(+)
137 and xl.book_type_code = da.book_type_code(+);
138
139
140 BEGIN
141
142 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
143 fnd_log.string(G_LEVEL_PROCEDURE,
144 G_MODULE_NAME||l_procedure_name||''.begin'',
145 ''Beginning of procedure'');
146 END IF;
147
148 open c_deprn;
149 fetch c_deprn bulk collect into l_deprn_tbl;
150 close c_deprn;
151
152 for i in 1..l_deprn_tbl.count loop
153
154 if (l_last_book <> l_deprn_tbl(i).book_type_code or
155 i = 1) then
156
157 if not (fa_cache_pkg.fazcbc
158 (X_BOOK => l_deprn_tbl(i).book_type_code,
159 P_LOG_LEVEL_REC => p_log_level_rec)) then
160 null;
161
162 end if;
163 l_last_book := l_deprn_tbl(i).book_type_code;
164 end if;
165
166
167 -- call FAFBGCC if the ccid doesnt exist in distribution accounts
168
169 if (l_deprn_tbl(i).generated_ccid is null and
170 l_deprn_tbl(i).deprn_entered_amount <> 0) then
171
172 if (not FA_GCCID_PKG.fafbgcc
173 (X_book_type_code => l_deprn_tbl(i).book_type_code,
174 X_fn_trx_code => ''DEPRN_EXPENSE_ACCT'',
175 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
176 X_acct_segval => l_deprn_tbl(i).deprn_expense_acct,
177 X_account_ccid => 0,
178 X_distribution_id => l_deprn_tbl(i).distribution_id,
179 X_rtn_ccid => l_deprn_tbl(i).generated_ccid,
180 P_LOG_LEVEL_REC => p_log_level_rec)) then
181 FA_SRVR_MSG.ADD_MESSAGE
182 (NAME => ''FA_GET_ACCOUNT_CCID'',
183 CALLING_FN => ''FA_INS_ADJUST_PKG.fadoflx'',
184 P_LOG_LEVEL_REC => p_log_level_rec);
185 l_deprn_tbl(i).generated_ccid := -1;
186 end if;
187 end if;
188
189 if (l_deprn_tbl(i).generated_offset_ccid is null and
190 l_deprn_tbl(i).deprn_entered_amount <> 0) then
191
192
193 if (not FA_GCCID_PKG.fafbgcc
194 (X_book_type_code => l_deprn_tbl(i).book_type_code,
195 X_fn_trx_code => ''DEPRN_RESERVE_ACCT'',
196 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
197 X_acct_segval => l_deprn_tbl(i).deprn_reserve_acct,
198 X_account_ccid => l_deprn_tbl(i).deprn_reserve_account_ccid,
199 X_distribution_id => l_deprn_tbl(i).distribution_id,
200 X_rtn_ccid => l_deprn_tbl(i).generated_offset_ccid,
201 P_LOG_LEVEL_REC => p_log_level_rec)) then
202
203 FA_SRVR_MSG.ADD_MESSAGE
204 (NAME => ''FA_GET_ACCOUNT_CCID'',
205 CALLING_FN => ''FA_INS_ADJUST_PKG.fadoflx'',
206 P_LOG_LEVEL_REC => p_log_level_rec);
207 l_deprn_tbl(i).generated_offset_ccid := -1;
208 end if;
209 end if;
210
211 if (l_deprn_tbl(i).bonus_generated_ccid is null and
212 l_deprn_tbl(i).bonus_entered_amount <> 0) then
213
214 if (not FA_GCCID_PKG.fafbgcc
215 (X_book_type_code => l_deprn_tbl(i).book_type_code,
216 X_fn_trx_code => ''BONUS_DEPRN_EXPENSE_ACCT'',
217 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
218 X_acct_segval => l_deprn_tbl(i).bonus_deprn_expense_acct,
219 X_account_ccid => 0,
220 X_distribution_id => l_deprn_tbl(i).distribution_id,
221 X_rtn_ccid => l_deprn_tbl(i).bonus_generated_ccid,
222 P_LOG_LEVEL_REC => p_log_level_rec)) then
223 FA_SRVR_MSG.ADD_MESSAGE
224 (NAME => ''FA_GET_ACCOUNT_CCID'',
225 CALLING_FN => ''FA_INS_ADJUST_PKG.fadoflx'',
226 P_LOG_LEVEL_REC => p_log_level_rec);
227 l_deprn_tbl(i).bonus_generated_ccid := -1;
228
229 end if;
230 end if;
231
232 if (l_deprn_tbl(i).bonus_generated_offset_ccid is null and
233 l_deprn_tbl(i).bonus_entered_amount <> 0) then
234
235 if (not FA_GCCID_PKG.fafbgcc
236 (X_book_type_code => l_deprn_tbl(i).book_type_code,
237 X_fn_trx_code => ''BONUS_DEPRN_RESERVE_ACCT'',
238 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
239 X_acct_segval => l_deprn_tbl(i).bonus_reserve_acct,
240 X_account_ccid => l_deprn_tbl(i).bonus_rsv_account_ccid,
241 X_distribution_id => l_deprn_tbl(i).distribution_id,
242 X_rtn_ccid => l_deprn_tbl(i).bonus_generated_offset_ccid,
243 P_LOG_LEVEL_REC => p_log_level_rec)) then
244 FA_SRVR_MSG.ADD_MESSAGE
245 (NAME => ''FA_GET_ACCOUNT_CCID'',
246 CALLING_FN => ''FA_INS_ADJUST_PKG.fadoflx'',
247 P_LOG_LEVEL_REC => p_log_level_rec);
248 l_deprn_tbl(i).bonus_generated_offset_ccid := -1;
249
250 end if;
251 end if;
252
253
254 if (l_deprn_tbl(i).reval_generated_ccid is null and
255 l_deprn_tbl(i).reval_entered_amount <> 0) then
256
257 if (not FA_GCCID_PKG.fafbgcc
258 (X_book_type_code => l_deprn_tbl(i).book_type_code,
259 X_fn_trx_code => ''REVAL_AMORTIZATION_ACCT'',
260 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
261 X_acct_segval => l_deprn_tbl(i).reval_amort_acct,
262 X_account_ccid => l_deprn_tbl(i).reval_amort_account_ccid,
263 X_distribution_id => l_deprn_tbl(i).distribution_id,
264 X_rtn_ccid => l_deprn_tbl(i).reval_generated_ccid,
265 P_LOG_LEVEL_REC => p_log_level_rec)) then
266 FA_SRVR_MSG.ADD_MESSAGE
267 (NAME => ''FA_GET_ACCOUNT_CCID'',
268 CALLING_FN => ''FA_INS_ADJUST_PKG.fadoflx'',
269 P_LOG_LEVEL_REC => p_log_level_rec);
270 l_deprn_tbl(i).reval_generated_ccid := -1;
271 end if;
272 end if;
273
274 if (l_deprn_tbl(i).reval_generated_offset_ccid is null and
275 l_deprn_tbl(i).reval_entered_amount <> 0) then
276
277 if (not FA_GCCID_PKG.fafbgcc
278 (X_book_type_code => l_deprn_tbl(i).book_type_code,
279 X_fn_trx_code => ''REVAL_RESERVE_ACCT'',
280 X_dist_ccid => l_deprn_tbl(i).distribution_ccid,
281 X_acct_segval => l_deprn_tbl(i).reval_reserve_acct,
282 X_account_ccid => l_deprn_tbl(i).reval_rsv_account_ccid,
283 X_distribution_id => l_deprn_tbl(i).distribution_id,
284 X_rtn_ccid => l_deprn_tbl(i).reval_generated_offset_ccid,
285 P_LOG_LEVEL_REC => p_log_level_rec)) then
286 FA_SRVR_MSG.ADD_MESSAGE
287 (NAME => ''FA_GET_ACCOUNT_CCID'',
288 CALLING_FN => ''FA_INS_ADJUST_PKG.fadoflx'',
289 P_LOG_LEVEL_REC => p_log_level_rec);
290 l_deprn_tbl(i).reval_generated_offset_ccid := -1;
291
292 end if;
293 end if;
294
295 end loop;
296
297 for i in 1.. l_deprn_tbl.count loop
298
299 l_generated_ccid(i) := l_deprn_tbl(i).generated_ccid;
300 l_generated_offset_ccid(i) := l_deprn_tbl(i).generated_offset_ccid;
301 l_bonus_generated_ccid(i) := l_deprn_tbl(i).bonus_generated_ccid;
302 l_bonus_generated_offset_ccid(i) := l_deprn_tbl(i).bonus_generated_offset_ccid;
303 l_reval_generated_ccid(i) := l_deprn_tbl(i).reval_generated_ccid;
304 l_reval_generated_offset_ccid(i) := l_deprn_tbl(i).reval_generated_offset_ccid;
305 l_rowid(i) := l_deprn_tbl(i).rowid;
306
307 end loop;
308
309 forall i in 1..l_deprn_tbl.count
310 update fa_xla_ext_lines_b_gt
311 set generated_ccid = l_generated_ccid(i),
312 generated_offset_ccid = l_generated_offset_ccid(i),
313 bonus_generated_ccid = l_bonus_generated_ccid(i),
314 bonus_generated_offset_ccid = l_bonus_generated_offset_ccid(i),
315 reval_generated_ccid = l_reval_generated_ccid(i),
316 reval_generated_offset_ccid = l_reval_generated_offset_ccid(i)
317 where rowid = l_rowid(i);
318
319 ';
320
321 C_PRIVATE_API_TRX CONSTANT VARCHAR2(32000) := '
322
323
324 -- bug 5563601: Increased length of variable account_type to 50
325 type adj_rec_type is record
326 (rowid VARCHAR2(64),
327 book_type_code VARCHAR2(15),
328 distribution_id NUMBER(15),
329 distribution_ccid NUMBER(15),
330 entered_amount NUMBER,
331 account_type VARCHAR2(50),
332 generated_ccid NUMBER(15),
333 account_ccid NUMBER(15),
334 account_segment VARCHAR2(25),
335 offset_account_type VARCHAR2(25),
336 generated_offset_ccid NUMBER(15),
337 offset_account_ccid NUMBER(15),
338 offset_account_segment VARCHAR2(25)
339 );
340
341 type adj_tbl_type is table of adj_rec_type index by binary_integer;
342
343 l_adj_tbl adj_tbl_type;
344
345 l_generated_ccid num_tab_type;
346 l_generated_offset_ccid num_tab_type;
347 l_rowid char_tab_type;
348
349 error_found exception;
350
351 l_last_book varchar2(15) := '' '';
352
353 cursor c_trx is
354 select /*+ leading(xg) index(xb, FA_XLA_EXT_HEADERS_B_GT_U1) index(xl, FA_XLA_EXT_LINES_B_GT_U1) */
355 xl.rowid,
356 xb.book_type_code,
357 xl.distribution_id,
358 xl.expense_account_ccid,
359 xl.entered_amount,
360 decode
361 (adjustment_type,
362 ''COST'', ''ASSET_COST_ACCT'',
363 ''CIP COST'', ''CIP_COST_ACCT'',
364 ''COST CLEARING'', decode(xl.asset_type,
365 ''CIP'', ''CIP_CLEARING_ACCT'',
366 ''ASSET_CLEARING_ACCT''),
367 ''EXPENSE'', ''DEPRN_EXPENSE_ACCT'',
368 ''RESERVE'', ''DEPRN_RESERVE_ACCT'',
369 ''BONUS EXPENSE'', ''BONUS_DEPRN_EXPENSE_ACCT'',
370 ''BONUS RESERVE'', ''BONUS_DEPRN_RESERVE_ACCT'',
371 ''REVAL RESERVE'', ''REVAL_RESERVE_ACCT'',
372 ''DEPRN ADJUST'', ''DEPRN_ADJUSTMENT_ACCT'',
373 ''PROCEEDS CLR'', ''PROCEEDS_OF_SALE_CLEARING_ACCT'',
374 ''REMOVALCOST CLR'', ''COST_OF_REMOVAL_CLEARING_ACCT'',
375 ''REMOVALCOST'', decode(sign(gain_loss_amount),
376 -1, ''COST_OF_REMOVAL_LOSS_ACCT'',
377 ''COST_OF_REMOVAL_GAIN_ACCT''),
378 ''PROCEEDS'', decode(sign(gain_loss_amount),
379 -1, ''PROCEEDS_OF_SALE_LOSS_ACCT'',
380 ''PROCEEDS_OF_SALE_GAIN_ACCT''),
381 ''REVAL RSV RET'', decode(sign(gain_loss_amount),
382 -1, ''REVAL_RSV_RETIRED_LOSS_ACCT'',
383 ''REVAL_RSV_RETIRED_GAIN_ACCT''),
384 ''NBV RETIRED'', decode(asset_type,
385 ''GROUP'', decode(sign(gain_loss_amount),
386 -1, ''NBV_RETIRED_LOSS_ACCT'',
387 ''NBV_RETIRED_GAIN_ACCT''),
388 decode(sign(gain_loss_amount),
389 -1, ''NBV_RETIRED_LOSS_ACCT'',
390 ''NBV_RETIRED_GAIN_ACCT'')),
391 NULL),
392 decode(xl.adjustment_type,
393 ''COST'', nvl(xl.generated_ccid, da.ASSET_COST_ACCOUNT_CCID),
394 ''CIP COST'', nvl(xl.generated_ccid, da.CIP_COST_ACCOUNT_CCID),
395 ''COST CLEARING'', decode(xl.asset_type,
396 ''CIP'', nvl(xl.generated_ccid, da.CIP_CLEARING_ACCOUNT_CCID),
397 nvl(xl.generated_ccid, da.ASSET_CLEARING_ACCOUNT_CCID)),
398 ''EXPENSE'', nvl(xl.generated_ccid, da.DEPRN_EXPENSE_ACCOUNT_CCID),
399 ''RESERVE'', nvl(xl.generated_ccid, da.DEPRN_RESERVE_ACCOUNT_CCID),
400 ''BONUS EXPENSE'', nvl(xl.generated_ccid, da.BONUS_EXP_ACCOUNT_CCID),
401 ''BONUS RESERVE'', nvl(xl.generated_ccid, da.BONUS_RSV_ACCOUNT_CCID),
402 ''REVAL RESERVE'', nvl(xl.generated_ccid, da.REVAL_RSV_ACCOUNT_CCID),
403 ''DEPRN ADJUST'', nvl(xl.generated_ccid, da.DEPRN_ADJ_ACCOUNT_CCID),
404 ''PROCEEDS CLR'', nvl(xl.generated_ccid, da.PROCEEDS_SALE_CLEARING_CCID),
405 ''REMOVALCOST CLR'', nvl(xl.generated_ccid, da.COST_REMOVAL_CLEARING_CCID),
406 ''PROCEEDS'', decode(sign(xl.gain_loss_amount),
407 -1, nvl(xl.generated_ccid, da.PROCEEDS_SALE_LOSS_CCID),
408 nvl(xl.generated_ccid, da.PROCEEDS_SALE_GAIN_CCID)),
409 ''REMOVALCOST'', decode(sign(xl.gain_loss_amount),
410 -1, nvl(xl.generated_ccid, da.COST_REMOVAL_LOSS_CCID),
411 nvl(xl.generated_ccid, da.COST_REMOVAL_GAIN_CCID)),
412 ''REVAL RSV RET'', decode(sign(xl.gain_loss_amount),
413 -1, nvl(xl.generated_ccid, da.REVAL_RSV_LOSS_ACCOUNT_CCID),
414 nvl(xl.generated_ccid, da.REVAL_RSV_GAIN_ACCOUNT_CCID)),
415 ''NBV RETIRED'', decode(sign(xl.gain_loss_amount),
416 -1, nvl(xl.generated_ccid, da.NBV_RETIRED_LOSS_CCID),
417 nvl(xl.generated_ccid, da.NBV_RETIRED_GAIN_CCID)),
418 NULL),
419 decode(xl.adjustment_type,
420 ''COST'', xl.ASSET_COST_ACCOUNT_CCID,
421 ''CIP COST'', xl.CIP_COST_ACCOUNT_CCID,
422 ''COST CLEARING'', decode(xl.asset_type,
423 ''CIP'', xl.CIP_CLEARING_ACCOUNT_CCID,
424 xl.ASSET_CLEARING_ACCOUNT_CCID),
425 ''RESERVE'', xl.RESERVE_ACCOUNT_CCID,
426 ''BONUS RESERVE'', xl.BONUS_RESERVE_ACCT_CCID,
427 ''REVAL RESERVE'', xl.REVAL_RESERVE_ACCOUNT_CCID,
428 0),
429 decode(xl.adjustment_type,
430 ''COST'', xl.ASSET_COST_ACCT,
431 ''CIP COST'', xl.CIP_COST_ACCT,
432 ''COST CLEARING'', decode(xl.asset_type,
433 ''CIP'', xl.CIP_CLEARING_ACCT,
434 xl.ASSET_CLEARING_ACCT),
435 ''EXPENSE'', xl.DEPRN_EXPENSE_ACCT,
436 ''RESERVE'', xl.DEPRN_RESERVE_ACCT,
437 ''BONUS EXPENSE'', xl.BONUS_DEPRN_EXPENSE_ACCT,
438 ''BONUS RESERVE'', xl.BONUS_RESERVE_ACCT,
439 ''REVAL RESERVE'', xl.REVAL_RESERVE_ACCT,
440 ''PROCEEDS CLR'', xb.PROCEEDS_OF_SALE_CLEARING_ACCT,
441 ''REMOVALCOST CLR'', xb.COST_OF_REMOVAL_CLEARING_ACCT,
442 ''NBV RETIRED'', decode(sign(xl.gain_loss_amount),
443 -1, xb.NBV_RETIRED_LOSS_ACCT,
444 xb.NBV_RETIRED_GAIN_ACCT),
445 ''PROCEEDS'', decode(sign(xl.gain_loss_amount),
446 -1, xb.PROCEEDS_OF_SALE_LOSS_ACCT,
447 xb.PROCEEDS_OF_SALE_GAIN_ACCT),
448 ''REMOVALCOST'', decode(sign(xl.gain_loss_amount),
449 -1, xb.COST_OF_REMOVAL_LOSS_ACCT,
450 xb.COST_OF_REMOVAL_GAIN_ACCT),
451 ''REVAL RSV RET'', decode(sign(xl.gain_loss_amount),
452 -1, xb.REVAL_RSV_RETIRED_LOSS_ACCT,
453 xb.REVAL_RSV_RETIRED_GAIN_ACCT),
454 NULL),
455 decode(xl.adjustment_type,
456 ''EXPENSE'', ''DEPRN_RESERVE_ACCT'',
457 ''BONUS EXPENSE'', ''BONUS_DEPRN_RESERVE_ACCT'',
458 NULL),
459 decode(xl.adjustment_type,
460 ''EXPENSE'', da.DEPRN_RESERVE_ACCOUNT_CCID,
461 ''BONUS EXPENSE'', da.BONUS_RSV_ACCOUNT_CCID,
462 NULL),
463 decode(xl.adjustment_type,
464 ''EXPENSE'', xl.RESERVE_ACCOUNT_CCID,
465 ''BONUS EXPENSE'', xl.BONUS_RESERVE_ACCT_CCID,
466 NULL),
467 decode(xl.adjustment_type,
468 ''EXPENSE'', xl.DEPRN_RESERVE_ACCT,
469 ''BONUS EXPENSE'', xl.BONUS_RESERVE_ACCT,
470 NULL)
471 from xla_events_gt xg,
472 fa_xla_ext_headers_b_gt xb,
473 fa_xla_ext_lines_b_gt xl,
474 fa_distribution_accounts da
475 where xg.event_class_code not in (''DEPRECIATION'', ''DEFERRED'')
476 and xb.event_id = xg.event_id
477 and xl.event_id = xg.event_id
478 and xl.distribution_id = da.distribution_id(+)
479 and xl.book_type_code = da.book_type_code(+);
480
481
482 BEGIN
483
484 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
485 fnd_log.string(G_LEVEL_PROCEDURE,
486 G_MODULE_NAME||l_procedure_name||''.begin'',
487 ''Beginning of procedure'');
488 END IF;
489
490
491 open c_trx;
492 fetch c_trx
493 bulk collect into l_adj_tbl;
494 close c_trx;
495
496 for i in 1..l_adj_tbl.count loop
497
498 if (l_last_book <> l_adj_tbl(i).book_type_code or
499 i = 1) then
500 if not (fa_cache_pkg.fazcbc
501 (X_BOOK => l_adj_tbl(1).book_type_code,
502 P_LOG_LEVEL_REC => p_log_level_rec)) then
503 null;
504 end if;
505 l_last_book := l_adj_tbl(i).book_type_code;
506 end if;
507
508 -- call FAFBGCC if the ccid doesnt exist in distribution accounts
509
510 if (l_adj_tbl(i).generated_ccid is null and
511 l_adj_tbl(i).entered_amount <> 0) then
512
513 if (not FA_GCCID_PKG.fafbgcc
514 (X_book_type_code => l_adj_tbl(i).book_type_code,
515 X_fn_trx_code => l_adj_tbl(i).account_type,
516 X_dist_ccid => l_adj_tbl(i).distribution_ccid,
517 X_acct_segval => l_adj_tbl(i).account_segment,
518 X_account_ccid => l_adj_tbl(i).account_ccid,
519 X_distribution_id => l_adj_tbl(i).distribution_id,
520 X_rtn_ccid => l_adj_tbl(i).generated_ccid,
521 P_LOG_LEVEL_REC => p_log_level_rec)) then
522 FA_SRVR_MSG.ADD_MESSAGE
523 (NAME => ''FA_GET_ACCOUNT_CCID'',
524 CALLING_FN => ''FA_INS_ADJUST_PKG.fadoflx'',
525 P_LOG_LEVEL_REC => p_log_level_rec);
526 l_adj_tbl(i).generated_ccid := -1;
527 end if;
528 end if;
529
530 if (l_adj_tbl(i).account_type in
531 (''DEPRN_EXPENSE_ACCT'', ''BONUS_DEPRN_EXPENSE_ACCT'') and
532 l_adj_tbl(i).generated_offset_ccid is null and
533 l_adj_tbl(i).entered_amount <> 0) then
534
535 if (not FA_GCCID_PKG.fafbgcc
536 (X_book_type_code => l_adj_tbl(i).book_type_code,
537 X_fn_trx_code => l_adj_tbl(i).offset_account_type,
538 X_dist_ccid => l_adj_tbl(i).distribution_ccid,
539 X_acct_segval => l_adj_tbl(i).offset_account_segment,
540 X_account_ccid => l_adj_tbl(i).offset_account_ccid,
541 X_distribution_id => l_adj_tbl(i).distribution_id,
542 X_rtn_ccid => l_adj_tbl(i).generated_offset_ccid,
543 P_LOG_LEVEL_REC => p_log_level_rec)) then
544 FA_SRVR_MSG.ADD_MESSAGE
545 (NAME => ''FA_GET_ACCOUNT_CCID'',
546 CALLING_FN => ''FA_INS_ADJUST_PKG.fadoflx'',
547 P_LOG_LEVEL_REC => p_log_level_rec);
548 l_adj_tbl(i).generated_offset_ccid := -1;
549 end if;
550 end if;
551
552 end loop;
553
554 for i in 1.. l_adj_tbl.count loop
555
556 l_generated_ccid(i) := l_adj_tbl(i).generated_ccid;
557 l_generated_offset_ccid(i) := l_adj_tbl(i).generated_offset_ccid;
558 l_rowid(i) := l_adj_tbl(i).rowid;
559
560 end loop;
561
562 forall i in 1..l_adj_tbl.count
563 update fa_xla_ext_lines_b_gt
564 set generated_ccid = l_generated_ccid(i),
565 generated_offset_ccid = l_generated_offset_ccid(i)
566 where rowid = l_rowid(i);
567
568 ';
569
570 C_PRIVATE_API_DEF CONSTANT VARCHAR2(32000) := '
571
572 type def_deprn_rec_type is record
573 (rowid VARCHAR2(64),
574 book_type_code VARCHAR2(15),
575 distribution_id NUMBER(15),
576 distribution_ccid NUMBER(15),
577 def_deprn_entered_amount NUMBER,
578 generated_ccid NUMBER(15),
579 generated_offset_ccid NUMBER(15),
580 DEF_DEPRN_EXPENSE_ACCT VARCHAR2(25),
581 DEF_DEPRN_RESERVE_ACCT VARCHAR2(25)
582 );
583
584 type def_deprn_tbl_type is table of def_deprn_rec_type index by binary_integer;
585
586 l_def_deprn_tbl def_deprn_tbl_type;
587
588 l_generated_ccid num_tab_type;
589 l_generated_offset_ccid num_tab_type;
590 l_rowid char_tab_type;
591
592 l_last_book varchar2(15) := '' '';
593
594 cursor c_def_deprn is
595 select /*+ leading(xg) index(xb, FA_XLA_EXT_HEADERS_B_GT_U1) index(xl, FA_XLA_EXT_LINES_B_GT_U1) */
596 xl.rowid,
597 xb.book_type_code,
598 xl.distribution_id,
599 xl.EXPENSE_ACCOUNT_CCID,
600 xl.entered_amount,
601 nvl(xl.generated_ccid, da.DEFERRED_EXP_ACCOUNT_CCID),
602 nvl(xl.generated_offset_ccid, da.DEFERRED_RSV_ACCOUNT_CCID),
603 xb.DEFERRED_DEPRN_EXPENSE_ACCT,
604 xb.DEFERRED_DEPRN_RESERVE_ACCT
605 from xla_events_gt xg,
606 fa_xla_ext_headers_b_gt xb,
607 fa_xla_ext_lines_b_gt xl,
608 fa_distribution_accounts da
609 where xg.event_class_code = ''DEFERRED DEPRECIATION''
610 and xb.event_id = xg.event_id
611 and xl.event_id = xg.event_id
612 and xl.distribution_id = da.distribution_id(+)
613 and xl.tax_book_type_code = da.book_type_code(+);
614
615
616
617 BEGIN
618
619 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
620 fnd_log.string(G_LEVEL_PROCEDURE,
621 G_MODULE_NAME||l_procedure_name||''.begin'',
622 ''Beginning of procedure'');
623 END IF;
624
625 open c_def_deprn;
626 fetch c_def_deprn bulk collect into l_def_deprn_tbl;
627 close c_def_deprn;
628
629 for i in 1..l_def_deprn_tbl.count loop
630
631 if (l_last_book <> l_def_deprn_tbl(i).book_type_code or
632 i = 1) then
633
634 if not (fa_cache_pkg.fazcbc
635 (X_BOOK => l_def_deprn_tbl(i).book_type_code,
636 P_LOG_LEVEL_REC => p_log_level_rec)) then
637 null;
638
639 end if;
640 l_last_book := l_def_deprn_tbl(i).book_type_code;
641 end if;
642
643
644 -- call FAFBGCC if the ccid doesnt exist in distribution accounts
645
646 if (l_def_deprn_tbl(i).generated_ccid is null and
647 l_def_deprn_tbl(i).def_deprn_entered_amount <> 0) then
648
649 if (not FA_GCCID_PKG.fafbgcc
650 (X_book_type_code => l_def_deprn_tbl(i).book_type_code,
651 X_fn_trx_code => ''DEFERRED_DEPRN_EXPENSE_ACCT'',
652 X_dist_ccid => l_def_deprn_tbl(i).distribution_ccid,
653 X_acct_segval => l_def_deprn_tbl(i).def_deprn_expense_acct,
654 X_account_ccid => 0,
655 X_distribution_id => l_def_deprn_tbl(i).distribution_id,
656 X_rtn_ccid => l_def_deprn_tbl(i).generated_ccid,
657 P_LOG_LEVEL_REC => p_log_level_rec)) then
658 FA_SRVR_MSG.ADD_MESSAGE
659 (NAME => ''FA_GET_ACCOUNT_CCID'',
660 CALLING_FN => ''fa_xla_extract_def_pkg.Load_Generated_Ccids'',
661 P_LOG_LEVEL_REC => p_log_level_rec);
662 l_def_deprn_tbl(i).generated_ccid := -1;
663 end if;
664 end if;
665
666 if (l_def_deprn_tbl(i).generated_offset_ccid is null and
667 l_def_deprn_tbl(i).def_deprn_entered_amount <> 0) then
668
669
670 if (not FA_GCCID_PKG.fafbgcc
671 (X_book_type_code => l_def_deprn_tbl(i).book_type_code,
672 X_fn_trx_code => ''DEFERRED_DEPRN_RESERVE_ACCT'',
673 X_dist_ccid => l_def_deprn_tbl(i).distribution_ccid,
674 X_acct_segval => l_def_deprn_tbl(i).def_deprn_reserve_acct,
675 X_account_ccid => 0,
676 X_distribution_id => l_def_deprn_tbl(i).distribution_id,
677 X_rtn_ccid => l_def_deprn_tbl(i).generated_offset_ccid,
678 P_LOG_LEVEL_REC => p_log_level_rec)) then
679
680 FA_SRVR_MSG.ADD_MESSAGE
681 (NAME => ''FA_GET_ACCOUNT_CCID'',
682 CALLING_FN => ''fa_xla_extract_def_pkg.Load_Generated_Ccids'',
683 P_LOG_LEVEL_REC => p_log_level_rec);
684 l_def_deprn_tbl(i).generated_offset_ccid := -1;
685 end if;
686 end if;
687
688 end loop;
689
690 for i in 1.. l_def_deprn_tbl.count loop
691
692 l_generated_ccid(i) := l_def_deprn_tbl(i).generated_ccid;
693 l_generated_offset_ccid(i) := l_def_deprn_tbl(i).generated_offset_ccid;
694 l_rowid(i) := l_def_deprn_tbl(i).rowid;
695
696 end loop;
697
698 forall i in 1..l_def_deprn_tbl.count
699 update fa_xla_ext_lines_b_gt
700 set generated_ccid = l_generated_ccid(i),
701 generated_offset_ccid = l_generated_offset_ccid(i)
702 where rowid = l_rowid(i);
703
704 ';
705
706
707 C_PRIVATE_API_3 CONSTANT VARCHAR2(32000) := '
708 --
709
710 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
711 fnd_log.string(G_LEVEL_PROCEDURE,
712 G_MODULE_NAME||l_procedure_name||''.end'',
713 ''End of procedure'');
714 END IF;
715
716 EXCEPTION
717 WHEN others THEN
718 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
719 fnd_message.set_name(''OFA'',''FA_SHARED_ORACLE_ERR'');
720 fnd_message.set_token(''ORACLE_ERR'',SQLERRM);
721 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
722 END IF;
723 raise;
724
725 END load_generated_ccids;
726
727 ';
728
729
730
731 --
732 --+==========================================================================+
733 --| |
734 --| Private global constants |
735 --| |
736 --+==========================================================================+
737 --
738 C_CREATED_ERROR CONSTANT BOOLEAN := FALSE;
739 C_CREATED CONSTANT BOOLEAN := TRUE;
740 --
741 g_Max_line CONSTANT NUMBER := 225;
742 g_chr_quote CONSTANT VARCHAR2(10):='''';
743 g_chr_newline CONSTANT VARCHAR2(10):= fa_cmp_string_pkg.g_chr_newline;
744
745 g_log_level_rec fa_api_types.log_level_rec_type;
746
747 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
748
749 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
750 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
751 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
752 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
753 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
754 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
755
756 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_cmp_header_pkg.';
757
758 FUNCTION GenerateCcidExtract
759 (p_extract_type IN VARCHAR2,
760 p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S) RETURN BOOLEAN IS
761
762 l_array_pkg DBMS_SQL.VARCHAR2S;
763 l_BodyPkg VARCHAR2(32000);
764 l_array_body DBMS_SQL.VARCHAR2S;
765 l_procedure_name varchar2(80) := 'GenerateLoadExtract';
766
767 BEGIN
768
769 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
770 fnd_log.string(G_LEVEL_PROCEDURE,
771 G_MODULE_NAME||l_procedure_name||'.begin',
772 'Beginning of procedure');
773 END IF;
774
775 l_array_body := fa_cmp_string_pkg.g_null_varchar2s;
776 l_array_pkg := fa_cmp_string_pkg.g_null_varchar2s;
777
778 -- deferred does not use locking - exit returning nothing
779 l_bodypkg := C_PRIVATE_API_1;
780
781 fa_cmp_string_pkg.CreateString
782 (p_package_text => l_BodyPkg
783 ,p_array_string => l_array_pkg);
784
785 if (p_extract_type = 'DEPRN') then
786
787 l_bodypkg := C_PRIVATE_API_DEPRN;
788
789 elsif (p_extract_type = 'TRX') then
790
791 l_bodypkg := C_PRIVATE_API_TRX;
792
793 elsif (p_extract_type = 'DEF') then
794
795 l_bodypkg := C_PRIVATE_API_DEF;
796
797 else
798 null; -- unkown type
799 end if;
800
801 fa_cmp_string_pkg.CreateString
802 (p_package_text => l_BodyPkg
803 ,p_array_string => l_array_body);
804
805 l_array_pkg :=
806 fa_cmp_string_pkg.ConcatTwoStrings
807 (p_array_string_1 => l_array_pkg
808 ,p_array_string_2 => l_array_body);
809
810
811 l_bodypkg := C_PRIVATE_API_3;
812
813 fa_cmp_string_pkg.CreateString
814 (p_package_text => l_BodyPkg
815 ,p_array_string => l_array_body);
816
817 l_array_pkg :=
818 fa_cmp_string_pkg.ConcatTwoStrings
819 (p_array_string_1 => l_array_pkg
820 ,p_array_string_2 => l_array_body);
821
822 p_package_body := l_array_pkg;
823
824 RETURN TRUE;
825
826 EXCEPTION
827 WHEN OTHERS THEN
828 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
829 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
830 fnd_message.set_token('ORACLE_ERR',SQLERRM);
831 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
832 END IF;
833 RETURN FALSE;
834
835 END GenerateCcidExtract;
836
837 END fa_xla_cmp_ccid_pkg;