[Home] [Help]
PACKAGE BODY: APPS.FA_MASSADD_CREATE_PKG
Source
1 PACKAGE BODY FA_MASSADD_CREATE_PKG as
2 /* $Header: FAMADCB.pls 120.14.12010000.6 2009/03/05 20:01:00 bridgway ship $ */
3
4
5 --*********************** Global constants ******************************--
6
7 G_PKG_NAME CONSTANT varchar2(30) := 'FA_MASSADD_CREATE_PKG';
8 G_API_NAME CONSTANT varchar2(30) := 'Create_lines';
9 G_API_VERSION CONSTANT number := 1.0;
10 G_WRITE_TO_LOG CONSTANT BOOLEAN := FALSE; /* Do we need to write to conc prog log file? */
11 G_DUMP_GT_RECS CONSTANT BOOLEAN := FALSE; /* Do we need to see details of GT records? */
12
13 g_log_level_rec fa_api_types.log_level_rec_type;
14
15
16 PROCEDURE DebugLog (p_text IN VARCHAR2
17 ,p_param IN VARCHAR2 DEFAULT null
18 ) IS
19 BEGIN
20 IF G_WRITE_TO_LOG THEN
21 fnd_file.put_line ( fnd_file.log, p_text || p_param );
22 END IF;
23
24 IF (g_log_level_rec.statement_level) then
25 fa_debug_pkg.add( G_PKG_NAME||G_API_NAME, p_text , p_param,g_log_level_rec);
26 END IF;
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 NULL;
31 END;
32
33 PROCEDURE DumpGTRecords ( p_event IN varchar2 ) IS
34 l_recs_found BOOLEAN := FALSE;
35 CURSOR c_gt IS
36 SELECT invoice_distribution_id
37 ,parent_invoice_dist_id
38 ,book_type_code
39 ,line_status
40 ,line_type_lookup_code
41 FROM fa_mass_additions_gt
42 ORDER BY 2,1;
43
44 BEGIN
45 IF NOT G_DUMP_GT_RECS THEN
46 return;
47 END IF;
48
49 DebugLog (' ');
50 DebugLog (' ');
51 DebugLog (' -----------------Begin GT Dump '||p_event||'------------------');
52 DebugLog ('Parent Dist ID Inv Dist ID Line type FA book Line Status');
53 DebugLog ('----------------------------------------------------------------------------------------------');
54
55 FOR l_gt IN c_gt LOOP
56 IF NOT l_recs_found THEN
57 l_recs_found := TRUE;
58 END IF;
59
60 DebugLog ( RPAD(l_gt.parent_invoice_dist_id,15,' ') ||' '||
61 RPAD(l_gt.invoice_distribution_id,15,' ') ||' '||
62 RPAD(l_gt.line_type_lookup_code,15,' ') ||' '||
63 RPAD(NVL(l_gt.book_type_code,'-NULL-'),15,' ') ||' '||
64 RPAD(l_gt.line_status,10,' ' )
65 );
66 END LOOP;
67
68 IF NOT l_recs_found THEN
69 DebugLog (' ------- No data found ------------ ');
70 END IF;
71
72 DebugLog (' -----------------End GT Dump '||p_event||'------------------');
73 DebugLog (' ');
74 DebugLog (' ');
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 NULL;
79 END;
80
81
82 FUNCTION Get_Account_Segment(
83 P_segment_num IN NUMBER,
84 P_base_ccid IN NUMBER,
85 P_coa_id IN VARCHAR2,
86 P_calling_fn IN VARCHAR2 DEFAULT NULL,
87 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
88 RETURN VARCHAR2 IS
89
90 l_result BOOLEAN;
91 l_num_of_segments NUMBER;
92 l_base_segments FND_FLEX_EXT.SEGMENTARRAY;
93 l_debug_info VARCHAR2(240);
94 l_calling_fn VARCHAR2(200);
95
96 BEGIN
97 l_calling_fn := 'FA_MASS_ADDITIONS_PKG.Get_Account_Segment';
98
99 if (g_log_level_rec.statement_level) then
100 fa_debug_pkg.add(l_calling_fn, 'Calling FND_FLEX_EXT.Get_segments',p_coa_id
101 ,p_log_level_rec => g_log_level_rec);
102 end if;
103
104 l_result := FND_FLEX_EXT.GET_SEGMENTS(
105 'SQLGL',
106 'GL#',
107 P_coa_id,
108 P_base_ccid,
109 l_num_of_segments,
110 l_base_segments);
111
112 if (g_log_level_rec.statement_level) then
113 fa_debug_pkg.add(l_calling_fn, 'Segment Number',l_base_segments(P_segment_num) );
114 end if;
115
116 return (l_base_segments(P_segment_num));
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 IF (SQLCODE <> -20001) THEN
121 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
122 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
123 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_calling_fn);
124 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
125 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_base_ccid: '
126 ||TO_CHAR(P_base_ccid)
127 ||',P_coa_id: '
128 ||P_coa_id );
129
130 END IF;
131
132 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
133 ,p_log_level_rec => g_log_level_rec);
134
135 APP_EXCEPTION.RAISE_EXCEPTION;
136
137
138 END Get_Account_Segment;
139
140 FUNCTION PreprocessGTRecords (p_book_type_code IN varchar2
141 ,p_coa_id IN number
142 ,p_segment_num IN number
143 ,p_ledger_id IN NUMBER
144 ,p_def_dpis_dt IN DATE) RETURN BOOLEAN IS
145
146 l_calling_fn varchar2(80) :=
147 'FA_MASSADD_CREATE_PKG.PreprocessGTRecords';
148 l_count number;
149 l_sql varchar(4000);
150 error_found exception;
151
152 begin
153
154 savepoint FAMADC_preprocess1;
155
156 select count(*)
157 into l_count
158 from fa_book_controls
159 where set_of_books_id = p_ledger_id
160 and book_class = 'CORPORATE';
161
162 DebugLog( 'number of book for this ledger', to_char(l_count) );
163 DebugLog( 'inserting', 'category accounts into GT');
164
165 -- load the category gt
166 if (l_count = 1) then
167
168 insert into fa_category_accounts_gt
169 (clearing_acct
170 ,book_type_code
171 ,asset_type)
172 select clearing_acct
173 ,book_type_code
174 ,decode(max(acct_type),
175 1, 'CIP',
176 'CAPITALIZED')
177 from (select asset_clearing_acct clearing_acct
178 ,book_type_code
179 , 2 acct_type
180 from fa_category_books
181 where book_type_code = p_book_type_code
182 UNION
183 select cip_clearing_acct , book_type_code, 1
184 from fa_category_books
185 where cip_clearing_acct is not null
186 and book_type_code = p_book_type_code)
187 group by clearing_acct, book_type_code;
188
189 else
190
191 insert into fa_category_accounts_gt
192 (clearing_acct
193 ,book_type_code
194 ,asset_type)
195 select clearing_acct
196 , book_type_code
197 , decode(max(acct_type),
198 1, 'CIP',
199 'CAPITALIZED')
200 from (select asset_clearing_acct clearing_acct
201 , cb.book_type_code
202 , 2 acct_type
203 from fa_category_books cb,
204 fa_book_controls bc
205 where cb.book_type_code = bc.book_type_code
206 and bc.book_class = 'CORPORATE'
207 and bc.set_of_books_id = p_ledger_id
208 UNION
209 select cip_clearing_acct , cb.book_type_code, 1
210 from fa_category_books cb,
211 fa_book_controls bc
212 where cip_clearing_acct is not null
213 and cb.book_type_code = bc.book_type_code
214 and bc.book_class = 'CORPORATE'
215 and bc.set_of_books_id = p_ledger_id)
216 group by clearing_acct, book_type_code;
217
218 end if;
219
220 l_count := SQL%ROWCOUNT;
221 DebugLog( 'No of Records Inserted ', to_char(l_count));
222 DebugLog( 'Deleting duplicate rows from ', 'fa_category_accounts_gt' );
223
224
225 savepoint FAMADC_preprocess2;
226
227 -- purge any duplicates from other books
228 -- this will result in FIFO processing if accounts
229 -- are not unique across books!!!!
230
231 delete
232 from fa_category_accounts_gt gt1
233 where gt1.book_type_code <> p_book_type_code
234 and exists
235 (select /*+ index (gt2 FA_CATEGORY_ACCOUNTS_GT_N1) */ 1
236 from fa_category_accounts_gt gt2
237 where gt2.book_type_code = p_book_type_code
238 and gt2.clearing_acct = gt1.clearing_acct);
239
240 l_count := SQL%ROWCOUNT;
241 DebugLog( 'No of Records Deleted ', to_char(l_count));
242 DebugLog( 'Deleting duplicate other book rows from ', 'fa_category_accounts_gt' );
243
244
245 delete
246 from fa_category_accounts_gt gt1
247 where not exists
248 (select /*+ index (gt2 FA_CATEGORY_ACCOUNTS_GT_N1) */ 1
249 from fa_category_accounts_gt gt2
250 where gt2.book_type_code = p_book_type_code
251 and gt2.clearing_acct = gt1.clearing_acct)
252 and gt1.rowid <>
253 (select min(rowid)
254 from fa_category_accounts_gt gt3
255 where gt3.clearing_acct = gt1.clearing_acct);
256
257 l_count := SQL%ROWCOUNT;
258 DebugLog( 'No of Records Deleted ', to_char(l_count));
259 DebugLog( 'Updating parent lines in ', 'fa_mass_additions_gt' );
260
261
262 savepoint FAMADC_preprocess3;
263
264 -- now update the parents
265 l_sql := '
266 update /*+ index(gt FA_MASS_ADDITIONS_GT_N2) */ fa_mass_additions_gt gt
267 set (asset_type, line_status, book_type_code) =
268 (select /*+ index(fca FA_CATEGORY_ACCOUNTS_GT_N2 */
269 decode(gt.asset_type,
270 null, decode(glcc.account_type,
271 ''E'', ''EXPENSED'',
272 nvl(fca.asset_type, gt.asset_type)),
273 gt.asset_type),
274 decode(glcc.account_type,
275 ''E'', ''VALID'',
276 decode(gt.book_type_code,
277 null, decode(fca.book_type_code,
278 :h_book_type_code, ''VALID'',
279 null, ''REJECTED'',
280 ''OTHER BOOK''),
281 decode(gt.book_type_code,
282 :h_book_type_code, ''VALID'',
283 ''OTHER BOOK''))),
284 decode(glcc.account_type,
285 ''E'', :h_book_type_code,
286 decode(gt.book_type_code,
287 null, decode(fca.book_type_code,
288 :h_book_type_code, :h_book_type_code,
289 null),
290 gt.book_type_code))
291 from gl_code_combinations glcc,
292 fa_category_accounts_gt fca
293 where gt.payables_code_combination_id = glcc.code_combination_id
294 and fca.clearing_acct(+) = ' ||
295 ' glcc.segment' ||
296 to_char(p_segment_num) || '
297 )
298 where gt.line_type_lookup_code in (''ITEM'', ''ACCRUAL'')
299 and gt.ledger_category_code = ''P''
300 and gt.line_status = ''NEW'' ';
301
302
303 EXECUTE IMMEDIATE l_sql
304 USING p_book_type_code
305 ,p_book_type_code
306 ,p_book_type_code
307 ,p_book_type_code
308 ,p_book_type_code;
309
310 l_count := SQL%ROWCOUNT;
311 DebugLog( 'No of Records Updated ', to_char(l_count));
312 DebugLog( 'Updating first set of child lines lines in ', 'fa_mass_additions_gt' );
313
314 -- update the child lines which have their parent in the gt
315 -- book may be null on the parent too
316
317
318 savepoint FAMADC_preprocess4;
319
320 update /*+ index(gt1 FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt1
321 set (asset_type, line_status , book_type_code) =
322 (select /*+ index(gt2 FA_MASS_ADDITIONS_GT_N3) */
323 gt2.asset_type,
324 decode(gt2.line_status,
325 'VALID', 'VALID_CHILD1',
326 gt2.line_status), -- OTHER BOOK / REJECTED
327 nvl(gt1.book_type_code,
328 gt2.book_type_code)
329 from fa_mass_additions_gt gt2
330 where gt2.invoice_distribution_id = gt1.parent_invoice_dist_id)
331 where gt1.line_type_lookup_code not in ('ITEM', 'ACCRUAL')
332 and gt1.ledger_category_code = 'P'
333 and gt1.line_status = 'NEW'
334 and exists
335 (select /*+ index(gt3 FA_MASS_ADDITIONS_GT_N3) */ 1
336 from fa_mass_additions_gt gt3
337 where gt3.invoice_distribution_id = gt1.parent_invoice_dist_id);
338
339
340 l_count := SQL%ROWCOUNT;
341 DebugLog( 'No of Records Updated ', to_char(l_count));
342 DebugLog( 'Updating second set of child lines lines in ', 'fa_mass_additions_gt' );
343
344
345 savepoint FAMADC_preprocess5;
346
347 -- update child lines with parent info when match is found
348 -- these are lines whose parents were brought over in
349 -- previous runs. Per AP dependancy, the book is always
350 -- populated on such lines ***
351
352 update /*+ index(gt1 FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt1
353 set (asset_type, line_status) =
354 (select distinct mad.asset_type,
355 decode(mad.posting_status,
356 'POSTED', 'VALID_ORPHAN1',
357 'DELETED', 'VALID_ORPHAN2',
358 'VALID_CHILD2')
359 from fa_mass_additions mad
360 where mad.book_type_code(+) = p_book_type_code
361 and mad.invoice_distribution_id(+) = gt1.parent_invoice_dist_id
362 and mad.invoice_id(+) = gt1.invoice_id
363 and mad.parent_mass_addition_id(+) is null
364 )
365 where book_type_code = p_book_type_code
366 and gt1.line_type_lookup_code not in ('ITEM', 'ACCRUAL')
367 and gt1.ledger_category_code = 'P'
368 and gt1.line_status = 'NEW'
369 and exists
370 (select 1
371 from fa_mass_additions mad
372 where mad.book_type_code(+) = p_book_type_code
373 and mad.invoice_distribution_id(+) = gt1.parent_invoice_dist_id
374 and mad.invoice_id(+) = gt1.invoice_id
375 and mad.parent_mass_addition_id(+) is null);
376
377
378 l_count := SQL%ROWCOUNT;
379 DebugLog( 'No of Records Updated ', to_char(l_count));
380 DebugLog( 'Updating third set of child lines lines in ', 'fa_mass_additions_gt' );
381
382 savepoint FAMADC_preprocess6;
383
384
385 -- for remaining orphan lines fire the clearing account validation
386 -- if successful they will end up in the interface with
387 -- asset type derived from parent and no merge status
388
389 update /*+ index(gt FA_MASS_ADDITIONS_GT_N2) */ fa_mass_additions_gt gt
390 set parent_payables_ccid =
391 (select min(payables_code_combination_id)
392 from fa_asset_invoices ai
393 where invoice_distribution_id = gt.parent_invoice_dist_id)
394 where gt.line_type_lookup_code not in ('ITEM', 'ACCRUAL')
395 and gt.ledger_category_code = 'P'
396 and gt.line_status = 'NEW';
397
398 savepoint FAMADC_preprocess7;
399
400 l_sql := '
401 update /*+ index(gt FA_MASS_ADDITIONS_GT_N2) */ fa_mass_additions_gt gt
402 set (asset_type, line_status, book_type_code) =
403 (select /*+ index(fca FA_CATEGORY_ACCOUNTS_GT_N2 */
404 decode(asset_type,
405 null, decode(glcc.account_type,
406 ''E'', ''EXPENSED'',
407 nvl(fca.asset_type, gt.asset_type)),
408 asset_type),
409 decode(glcc.account_type,
410 ''E'', ''VALID_ORPHAN3'',
411 decode(gt.book_type_code,
412 null, decode(fca.book_type_code,
413 :h_book_type_code, ''VALID_ORPHAN3'',
414 null, ''REJECTED'',
415 ''OTHER BOOK''),
416 decode(gt.book_type_code,
417 :h_book_type_code, ''VALID_ORPHAN3'',
418 ''OTHER BOOK''))),
419 decode(glcc.account_type,
420 ''E'', :h_book_type_code,
421 decode(gt.book_type_code,
422 null, decode(fca.book_type_code,
423 :h_book_type_code, :h_book_type_code,
424 null),
425 gt.book_type_code))
426 from gl_code_combinations glcc,
427 fa_category_accounts_gt fca
428 where gt.parent_payables_ccid = glcc.code_combination_id
429 and fca.clearing_acct(+) = ' ||
430 ' glcc.segment' ||
431 to_char(p_segment_num) || '
432 )
433 where gt.line_type_lookup_code not in (''ITEM'', ''ACCRUAL'')
434 and gt.ledger_category_code = ''P''
435 and gt.line_status = ''NEW''
436 and gt.parent_payables_ccid is not null ';
437
438 EXECUTE IMMEDIATE l_sql
439 USING p_book_type_code
440 ,p_book_type_code
441 ,p_book_type_code
442 ,p_book_type_code
443 ,p_book_type_code;
444
445 l_count := SQL%ROWCOUNT;
446 DebugLog( 'No of Records Updated ', to_char(l_count));
447 DebugLog( 'Updating depreciate_flag in ', 'fa_mass_additions_gt' );
448
449
450 savepoint FAMADC_preprocess8;
451
452 -- update the depreciate_flag and inventorial on valid lines
453 update /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt
454 set depreciate_flag =
455 (select decode(gt.asset_type
456 ,'EXPENSED','NO'
457 ,nvl(CBD.depreciate_flag, 'YES'))
458 from fa_category_book_defaults CBD
459 where CBD.book_type_code(+) = p_book_type_code
460 and CBD.category_id(+)= gt.asset_category_id
461 and p_def_dpis_dt between CBD.start_DPIS(+)
462 and nvl(CBD.end_DPIS(+),p_def_dpis_dt)),
463 inventorial =
464 (select nvl(inventorial, 'YES')
465 from fa_categories_b c
466 where c.category_id(+) = gt.asset_category_id)
467 where gt.book_type_code = p_book_type_code
468 and gt.line_status like 'VALID%'
469 and gt.asset_category_id is not null;
470
471
472 l_count := SQL%ROWCOUNT;
473 DebugLog( 'No of Records Updated ', to_char(l_count));
474
475 RETURN TRUE;
476
477 EXCEPTION
478 WHEN ERROR_FOUND then
479 RETURN FALSE;
480
481 WHEN OTHERS THEN
482 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
483 ,p_log_level_rec => g_log_level_rec);
484
485 RETURN FALSE;
486
487 end PreprocessGTRecords;
488
489
490 PROCEDURE create_lines(p_book_type_code IN VARCHAR2
491 ,p_api_version IN NUMBER
492 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
493 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
494 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
495 ,p_calling_fn IN VARCHAR2
496 ,x_return_status OUT NOCOPY VARCHAR2
497 ,x_msg_count OUT NOCOPY NUMBER
498 ,x_msg_data OUT NOCOPY VARCHAR2
499 ) IS
500
501
502 l_calling_fn varchar2(40) := 'FA_MASS_ADDITIONS_PKG.create_lines';
503
504 l_count INTEGER;
505
506 l_date_ineffective FA_BOOK_CONTROLS.DATE_INEFFECTIVE%TYPE;
507 l_book_class FA_BOOK_CONTROLS.BOOK_CLASS%TYPE;
508 l_sob_id FA_BOOK_CONTROLS.SET_OF_BOOKS_ID%TYPE;
509 l_coa_id FA_BOOK_CONTROLS.ACCOUNTING_FLEX_STRUCTURE%TYPE;
510 l_segment_num NUMBER;
511 l_def_dpis_option VARCHAR2(1);
512 l_def_dpis_enabled INTEGER;
513 l_def_dpis_dt DATE;
514 l_ledger_id NUMBER;
515 l_period_close_date DATE;
516 l_calendar_period_close_date DATE;
517 l_request_id NUMBER;
518 l_login_id NUMBER;
519 l_user_id NUMBER;
520 l_result BOOLEAN;
521
522 fa_ineffective_book EXCEPTION;
523 fa_not_corp_book EXCEPTION;
524 create_err EXCEPTION;
525
526 BEGIN
527
528 savepoint FAMADC_create1;
529
530 DebugLog ( 'Initializing FND Logging structure ');
531
532 if (not g_log_level_rec.initialized) then
533 if (NOT fa_util_pub.get_log_level_rec (
534 x_log_level_rec => g_log_level_rec
535 )) then
536 raise create_err;
537 end if;
538 end if;
539
540 l_request_id := FND_GLOBAL.conc_request_id;
541 l_user_id := FND_GLOBAL.user_id;
542 l_login_id := FND_GLOBAL.login_id;
543
544 DebugLog ( 'Stamp FA_SYSTEM_CONTROLS WITH ', l_request_id );
545
546 update fa_system_controls
547 set last_mass_additions = l_request_id;
548
549 DebugLog( 'Validating book ', p_book_type_code );
550
551 if NOT fa_cache_pkg.fazcbc(X_book => p_book_type_code,
552 p_log_level_rec => g_log_level_rec) then
553 raise create_err;
554 end if;
555
556 if not fa_cache_pkg.fazcdp (x_book_type_code => p_book_type_code ) THEN
557 raise create_err;
558 end if;
559
560 l_ledger_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
561 l_date_ineffective := fa_cache_pkg.fazcbc_record.date_ineffective;
562 l_book_class := fa_cache_pkg.fazcbc_record.book_class;
563 l_coa_id := fa_cache_pkg.fazcbc_record.accounting_flex_structure;
564
565 l_calendar_period_close_date :=
566 fa_cache_pkg.fazcdp_record.calendar_period_close_date ;
567
568 IF l_date_ineffective IS NOT NULL THEN
569 DebugLog('Ineffective book ',p_book_type_code);
570 raise fa_ineffective_book;
571 END IF;
572
573 IF l_book_class <> 'CORPORATE' THEN
574 DebugLog('Incorrect Book class ', p_book_type_code);
575 raise fa_not_corp_book;
576 END IF;
577
578 -- Get Qualifier segment number
579 DebugLog( 'Get Qualifier Segment for Chart of Accounts ID ', l_coa_id );
580 if NOT (FND_FLEX_APIS.Get_Qualifier_segnum(101
581 ,'GL#'
582 ,l_coa_id
583 ,'GL_ACCOUNT'
584 ,l_segment_num)) then
585 raise create_err;
586 end if;
587
588 DebugLog( 'Checking Profile ', 'FA_DEFAULT_DPIS_TO_INV_DATE' );
589 l_def_dpis_option := fnd_profile.value('FA_DEFAULT_DPIS_TO_INV_DATE');
590
591 IF (l_def_dpis_option = 'Y') THEN
592 l_def_dpis_enabled := 1;
593 ELSE
594 l_def_dpis_enabled := 0;
595 END IF;
596
597
598 IF (l_def_dpis_enabled = 0) THEN /* For Future Dated Txns */
599 DebugLog ( 'Get Default DPIS for ', p_book_type_code );
600 l_def_dpis_dt :=
601 greatest(nvl(fa_cache_pkg.fazcdp_record.calendar_period_open_date,
602 sysdate),
603 least(sysdate,
604 nvl(fa_cache_pkg.fazcdp_record.calendar_period_close_date, sysdate)));
605
606 END IF;
607
608
609 DumpGTRecords ( 'PREPROCESS (FAAPI)');
610
611 DebugLog ('PreProcess Records in GT that have book type code NULL');
612 IF NOT PreprocessGTRecords (p_book_type_code => p_book_type_code
613 ,p_coa_id => l_coa_id
614 ,p_segment_num => l_segment_num
615 ,p_ledger_id => l_ledger_id
616 ,p_def_dpis_dt => l_def_dpis_dt
617 ) THEN
618 DebugLog('Error during PreProcessing in FA API ',p_book_type_code );
619 raise create_err;
620 END IF;
621
622
623 -- Call the cache pkg to fetch the calendar_period_close_date
624 if not fa_cache_pkg.fazcdp (x_book_type_code => p_book_type_code ) THEN
625 DebugLog('Unable to find valid depreciation information ',p_book_type_code );
626 raise fa_ineffective_book;
627 end if;
628
629 DumpGTRecords ( 'MAIN (FAAPI)');
630 DebugLog('Insert FA_MASS_ADDITIONS with ITEM/ACCRUAL lines for primary ledger ',p_book_type_code );
631
632 savepoint FAMADC_create2;
633
634 insert into fa_mass_additions(
635 mass_addition_id ,
636 asset_number ,
637 tag_number ,
638 description ,
639 asset_category_id ,
640 manufacturer_name ,
641 serial_number ,
642 model_number ,
643 book_type_code ,
644 date_placed_in_service ,
645 fixed_assets_cost ,
646 payables_units ,
647 fixed_assets_units ,
648 payables_code_combination_id ,
649 expense_code_combination_id ,
650 location_id ,
651 assigned_to ,
652 feeder_system_name ,
653 create_batch_date ,
654 create_batch_id ,
655 last_update_date ,
656 last_updated_by ,
657 reviewer_comments ,
658 invoice_number ,
659 vendor_number ,
660 po_vendor_id ,
661 po_number ,
662 posting_status ,
663 queue_name ,
664 invoice_date ,
665 invoice_created_by ,
666 invoice_updated_by ,
667 payables_cost ,
668 invoice_id ,
669 payables_batch_name ,
670 depreciate_flag ,
671 parent_mass_addition_id ,
672 parent_asset_id ,
673 split_merged_code ,
674 ap_distribution_line_number ,
675 post_batch_id ,
676 add_to_asset_id ,
677 amortize_flag ,
678 new_master_flag ,
679 asset_key_ccid ,
680 asset_type ,
681 deprn_reserve ,
682 ytd_deprn ,
683 beginning_nbv ,
684 created_by ,
685 creation_date ,
686 last_update_login ,
687 salvage_value ,
688 accounting_date ,
689 attribute_category_code ,
690 fully_rsvd_revals_counter ,
691 merge_invoice_number ,
692 merge_vendor_number ,
693 production_capacity ,
694 reval_amortization_basis ,
695 reval_reserve ,
696 unit_of_measure ,
697 unrevalued_cost ,
698 ytd_reval_deprn_expense ,
699 merged_code ,
700 split_code ,
701 merge_parent_mass_additions_id ,
702 split_parent_mass_additions_id ,
703 project_asset_line_id ,
704 project_id ,
705 task_id ,
706 sum_units ,
707 dist_name ,
708 context ,
709 inventorial ,
710 short_fiscal_year_flag ,
711 conversion_date ,
712 original_deprn_start_date ,
713 group_asset_id ,
714 cua_parent_hierarchy_id ,
715 units_to_adjust ,
716 bonus_ytd_deprn ,
717 bonus_deprn_reserve ,
718 amortize_nbv_flag ,
719 amortization_start_date ,
720 transaction_type_code ,
721 transaction_date ,
722 warranty_id ,
723 lease_id ,
724 lessor_id ,
725 property_type_code ,
726 property_1245_1250_code ,
727 in_use_flag ,
728 owned_leased ,
729 new_used ,
730 asset_id ,
731 material_indicator_flag ,
732 invoice_distribution_id ,
733 invoice_line_number ,
734 invoice_payment_id ,
735 warranty_number)
736 select /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_s.nextval,
737 gt.asset_number ,
738 gt.tag_number ,
739 gt.description ,
740 gt.asset_category_id ,
741 gt.manufacturer_name ,
742 gt.serial_number ,
743 gt.model_number ,
744 p_book_type_code ,
745 decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt ) ,
746 gt.fixed_assets_cost ,
747 gt.payables_units ,
748 gt.fixed_assets_units ,
749 gt.payables_code_combination_id ,
750 gt.expense_code_combination_id ,
751 gt.location_id ,
752 gt.assigned_to ,
753 gt.feeder_system_name ,
754 gt.create_batch_date ,
755 gt.create_batch_id ,
756 gt.last_update_date ,
757 gt.last_updated_by ,
758 gt.reviewer_comments ,
759 gt.invoice_number ,
760 gt.vendor_number ,
761 gt.po_vendor_id ,
762 gt.po_number ,
763 gt.posting_status ,
764 gt.queue_name ,
765 gt.invoice_date ,
766 gt.invoice_created_by ,
767 gt.invoice_updated_by ,
768 gt.payables_cost ,
769 gt.invoice_id ,
770 gt.payables_batch_name ,
771 gt.depreciate_flag ,
772 gt.parent_mass_addition_id ,
773 gt.parent_asset_id ,
774 /* gt.split_merged_code */
775 null ,
776 gt.ap_distribution_line_number ,
777 gt.post_batch_id ,
778 gt.add_to_asset_id ,
779 gt.amortize_flag ,
780 gt.new_master_flag ,
781 gt.asset_key_ccid ,
782 gt.asset_type , -- reinstated
783 gt.deprn_reserve ,
784 gt.ytd_deprn ,
785 gt.beginning_nbv ,
786 gt.created_by ,
787 gt.creation_date ,
788 gt.last_update_login ,
789 gt.salvage_value ,
790 gt.accounting_date ,
791 gt.attribute_category_code ,
792 gt.fully_rsvd_revals_counter ,
793 gt.merge_invoice_number ,
794 gt.merge_vendor_number ,
795 gt.production_capacity ,
796 gt.reval_amortization_basis ,
797 gt.reval_reserve ,
798 gt.unit_of_measure ,
799 gt.unrevalued_cost ,
800 gt.ytd_reval_deprn_expense ,
801 /* gt.merged_code */
802 null,
803 gt.split_code ,
804 gt.merge_parent_mass_additions_id ,
805 gt.split_parent_mass_additions_id ,
806 gt.project_asset_line_id ,
807 gt.project_id ,
808 gt.task_id ,
809 /* gt.sum_units */
810 null,
811 gt.dist_name ,
812 gt.context ,
813 gt.inventorial ,
814 gt.short_fiscal_year_flag ,
815 gt.conversion_date ,
816 gt.original_deprn_start_date ,
817 gt.group_asset_id ,
818 gt.cua_parent_hierarchy_id ,
819 gt.units_to_adjust ,
820 gt.bonus_ytd_deprn ,
821 gt.bonus_deprn_reserve ,
822 gt.amortize_nbv_flag ,
823 gt.amortization_start_date ,
824 /* transaction_type_code - only future add in future period */
825 decode(sign(decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt)
826 - l_calendar_period_close_date), 1, 'FUTURE ADD', NULL ),
827 /* transaction date */
828 decode(sign(decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt)
829 - l_calendar_period_close_date), 1, decode(l_def_dpis_enabled,
830 1, invoice_date, l_def_dpis_dt), null ),
831 gt.warranty_id ,
832 gt.lease_id ,
833 gt.lessor_id ,
834 gt.property_type_code ,
835 gt.property_1245_1250_code ,
836 gt.in_use_flag ,
837 gt.owned_leased ,
838 gt.new_used ,
839 gt.asset_id ,
840 gt.material_indicator_flag ,
841 gt.invoice_distribution_id ,
842 gt.invoice_line_number ,
843 gt.invoice_payment_id ,
844 gt.warranty_number
845 from fa_mass_additions_gt gt
846 where gt.book_type_code = p_book_type_code
847 and gt.line_type_lookup_code in ('ITEM', 'ACCRUAL')
848 and gt.line_status = 'VALID';
849
850 l_count := SQL%ROWCOUNT;
851 DebugLog( 'No of Records Inserted ', to_char(l_count) );
852
853
854 DebugLog( 'Updating FA_MASS_ADDITIONS_GT for child lines', p_book_type_code );
855
856
857 savepoint FAMADC_create3;
858
859 update /*+ leading(gt) index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt1
860 set (posting_status
861 ,parent_mass_addition_id
862 ,split_merged_code
863 ,merged_code
864 ,merge_parent_mass_additions_id) =
865 (select decode(mad.posting_status,
866 'POSTED', gt.posting_status,
867 'DELETE', gt.posting_status,
868 null, gt.posting_status ,
869 'MERGED'),
870 decode(mad.posting_status,
871 'POSTED', null,
872 'DELETE', null,
873 mad.mass_addition_id),
874 decode(mad.posting_status,
875 'POSTED', null,
876 'DELETE', null,
877 null, null,
878 'MC'),
879 decode(mad.posting_status,
880 'POSTED', null,
881 'DELETE', null,
882 null, null,
883 'MC'),
884 decode(mad.posting_status,
885 'POSTED', null,
886 'DELETE', null,
887 mad.mass_addition_id)
888 from fa_mass_additions mad,
889 fa_mass_additions_gt gt
890 where gt.rowid = gt1.rowid
891 and mad.invoice_distribution_id(+) = gt.parent_invoice_dist_id
892 and mad.book_type_code(+) = p_book_type_code
893 and mad.invoice_distribution_id(+) is not null
894 and mad.invoice_id(+) = gt.invoice_id
895 and mad.parent_mass_addition_id(+) is null)
896 where gt1.book_type_code = p_book_type_code
897 and gt1.line_status in ('VALID_CHILD1', 'VALID_CHILD2');
898
899
900 DebugLog( 'Inserting FA_MASS_ADDITIONS with non-ITEM/ACCRUAL lines for primary', p_book_type_code );
901
902 savepoint FAMADC_create4;
903
904 insert into fa_mass_additions(
905 mass_addition_id ,
906 asset_number ,
907 tag_number ,
908 description ,
909 asset_category_id ,
910 manufacturer_name ,
911 serial_number ,
912 model_number ,
913 book_type_code ,
914 date_placed_in_service ,
915 fixed_assets_cost ,
916 payables_units ,
917 fixed_assets_units ,
918 payables_code_combination_id ,
919 expense_code_combination_id ,
920 location_id ,
921 assigned_to ,
922 feeder_system_name ,
923 create_batch_date ,
924 create_batch_id ,
925 last_update_date ,
926 last_updated_by ,
927 reviewer_comments ,
928 invoice_number ,
929 vendor_number ,
930 po_vendor_id ,
931 po_number ,
932 posting_status ,
933 queue_name ,
934 invoice_date ,
935 invoice_created_by ,
936 invoice_updated_by ,
937 payables_cost ,
938 invoice_id ,
939 payables_batch_name ,
940 depreciate_flag ,
941 parent_mass_addition_id ,
942 parent_asset_id ,
943 split_merged_code ,
944 ap_distribution_line_number ,
945 post_batch_id ,
946 add_to_asset_id ,
947 amortize_flag ,
948 new_master_flag ,
949 asset_key_ccid ,
950 asset_type ,
951 deprn_reserve ,
952 ytd_deprn ,
953 beginning_nbv ,
954 created_by ,
955 creation_date ,
956 last_update_login ,
957 salvage_value ,
958 accounting_date ,
959 attribute_category_code ,
960 fully_rsvd_revals_counter ,
961 merge_invoice_number ,
962 merge_vendor_number ,
963 production_capacity ,
964 reval_amortization_basis ,
965 reval_reserve ,
966 unit_of_measure ,
967 unrevalued_cost ,
968 ytd_reval_deprn_expense ,
969 merged_code ,
970 split_code ,
971 merge_parent_mass_additions_id ,
972 split_parent_mass_additions_id ,
973 project_asset_line_id ,
974 project_id ,
975 task_id ,
976 sum_units ,
977 dist_name ,
978 context ,
979 inventorial ,
980 short_fiscal_year_flag ,
981 conversion_date ,
982 original_deprn_start_date ,
983 group_asset_id ,
984 cua_parent_hierarchy_id ,
985 units_to_adjust ,
986 bonus_ytd_deprn ,
987 bonus_deprn_reserve ,
988 amortize_nbv_flag ,
989 amortization_start_date ,
990 transaction_type_code ,
991 transaction_date ,
992 warranty_id ,
993 lease_id ,
994 lessor_id ,
995 property_type_code ,
996 property_1245_1250_code ,
997 in_use_flag ,
998 owned_leased ,
999 new_used ,
1000 asset_id ,
1001 material_indicator_flag ,
1002 invoice_distribution_id ,
1003 invoice_line_number ,
1004 invoice_payment_id ,
1005 warranty_number)
1006 select /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_s.nextval ,
1007 gt.asset_number ,
1008 gt.tag_number ,
1009 gt.description ,
1010 gt.asset_category_id ,
1011 gt.manufacturer_name ,
1012 gt.serial_number ,
1013 gt.model_number ,
1014 p_book_type_code ,
1015 decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt ) ,
1016 gt.fixed_assets_cost ,
1017 gt.payables_units ,
1018 gt.fixed_assets_units ,
1019 gt.payables_code_combination_id ,
1020 gt.expense_code_combination_id ,
1021 gt.location_id ,
1022 gt.assigned_to ,
1023 gt.feeder_system_name ,
1024 gt.create_batch_date ,
1025 gt.create_batch_id ,
1026 gt.last_update_date ,
1027 gt.last_updated_by ,
1028 gt.reviewer_comments ,
1029 gt.invoice_number ,
1030 gt.vendor_number ,
1031 gt.po_vendor_id ,
1032 gt.po_number ,
1033 gt.posting_status ,
1034 gt.queue_name ,
1035 gt.invoice_date ,
1036 gt.invoice_created_by ,
1037 gt.invoice_updated_by ,
1038 gt.payables_cost ,
1039 gt.invoice_id ,
1040 gt.payables_batch_name ,
1041 gt.depreciate_flag ,
1042 gt.parent_mass_addition_id ,
1043 gt.parent_asset_id ,
1044 gt.split_merged_code ,
1045 gt.ap_distribution_line_number ,
1046 gt.post_batch_id ,
1047 gt.add_to_asset_id ,
1048 gt.amortize_flag ,
1049 gt.new_master_flag ,
1050 gt.asset_key_ccid ,
1051 gt.asset_type ,
1052 gt.deprn_reserve ,
1053 gt.ytd_deprn ,
1054 gt.beginning_nbv ,
1055 gt.created_by ,
1056 gt.creation_date ,
1057 gt.last_update_login ,
1058 gt.salvage_value ,
1059 gt.accounting_date ,
1060 gt.attribute_category_code ,
1061 gt.fully_rsvd_revals_counter ,
1062 gt.merge_invoice_number ,
1063 gt.merge_vendor_number ,
1064 gt.production_capacity ,
1065 gt.reval_amortization_basis ,
1066 gt.reval_reserve ,
1067 gt.unit_of_measure ,
1068 gt.unrevalued_cost ,
1069 gt.ytd_reval_deprn_expense ,
1070 gt.merged_code ,
1071 gt.split_code ,
1072 gt.merge_parent_mass_additions_id ,
1073 gt.split_parent_mass_additions_id ,
1074 gt.project_asset_line_id ,
1075 gt.project_id ,
1076 gt.task_id ,
1077 gt.sum_units ,
1078 gt.dist_name ,
1079 gt.context ,
1080 gt.inventorial ,
1081 gt.short_fiscal_year_flag ,
1082 gt.conversion_date ,
1083 gt.original_deprn_start_date ,
1084 gt.group_asset_id ,
1085 gt.cua_parent_hierarchy_id ,
1086 gt.units_to_adjust ,
1087 gt.bonus_ytd_deprn ,
1088 gt.bonus_deprn_reserve ,
1089 gt.amortize_nbv_flag ,
1090 gt.amortization_start_date ,
1091 /* transaction_type_code - only future add in future period */
1092 decode(sign(decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt)
1093 - l_calendar_period_close_date), 1, 'FUTURE ADD', NULL ),
1094 /* transaction date */
1095 decode(sign(decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt)
1096 - l_calendar_period_close_date), 1, decode(l_def_dpis_enabled, 1,
1097 gt.invoice_date, l_def_dpis_dt), null ),
1098 gt.warranty_id ,
1099 gt.lease_id ,
1100 gt.lessor_id ,
1101 gt.property_type_code ,
1102 gt.property_1245_1250_code ,
1103 gt.in_use_flag ,
1104 gt.owned_leased ,
1105 gt.new_used ,
1106 gt.asset_id ,
1107 gt.material_indicator_flag ,
1108 gt.invoice_distribution_id ,
1109 gt.invoice_line_number ,
1110 gt.invoice_payment_id ,
1111 gt.warranty_number
1112 from fa_mass_additions_gt gt
1113 where gt.book_type_code = p_book_type_code
1114 and gt.line_status in ('VALID_CHILD1', 'VALID_CHILD2',
1115 'VALID_ORPHAN1', 'VALID_ORPHAN2', 'VALID_ORPHAN3');
1116
1117 l_count := SQL%ROWCOUNT;
1118 DebugLog( 'No of NON-ITEM Records Inserted ', to_char(l_count) );
1119
1120 DebugLog( 'Updating Parent Lines as Merged Parent of ', 'DISCOUNT');
1121
1122 savepoint FAMADC_create5;
1123
1124 -- Mark the parent line of DISCOUNT lines as Merged Parent, if not already done by prior(AP) non-item lines
1125 -- Performance bugfix 4945306 - Added invoice_id clause to the sub-select
1126 update fa_mass_additions ma
1127 set ma.split_merged_code = 'MP',
1128 ma.merged_code = 'MP',
1129 ma.sum_units = 'NO'
1130 where ma.posting_status not in ('POSTED', 'DELETE')
1131 and ma.book_type_code = p_book_type_code
1132 and ma.parent_mass_addition_id is null
1133 and ma.merged_code is null
1134 and ma.split_merged_code is null
1135 and ma.invoice_payment_id is null
1136 and exists
1137 (select 1
1138 from fa_mass_additions mac,
1139 fa_mass_additions_gt gt
1140 where mac.posting_status = 'MERGED'
1141 and mac.book_type_code = p_book_type_code
1142 and mac.merged_code = 'MC'
1143 and mac.merge_parent_mass_additions_id = ma.mass_addition_id
1144 and mac.invoice_distribution_id = gt.invoice_distribution_id);
1145
1146 l_count := SQL%ROWCOUNT;
1147 DebugLog('No of Records Updated ', to_char(l_count));
1148
1149 -- insert into mc_rates
1150 DebugLog('Inserting into FA_MC_MASS_RATES for reporting ledger(s) ', p_book_type_code);
1151
1152 savepoint FAMADC_create6;
1153
1154 Insert into fa_mc_mass_rates
1155 ( set_of_books_id,
1156 mass_addition_id,
1157 fixed_assets_cost,
1158 exchange_rate)
1159 select /*+ leading(gt) */
1160 gt.ledger_id,
1161 mad.mass_addition_id,
1162 gt.fixed_assets_cost,
1163 0
1164 from fa_mass_additions mad,
1165 fa_mass_additions_gt gt
1166 where mad.book_type_code = p_book_type_code
1167 and mad.invoice_distribution_id = gt.invoice_distribution_id
1168 and gt.book_type_code = p_book_type_code
1169 and gt.ledger_category_code <> 'P';
1170
1171 l_count := SQL%ROWCOUNT;
1172 DebugLog( 'No of Records Inserted ', to_char(l_count));
1173
1174 -- update for rejected is already handled in the preprocessing logic
1175
1176 DebugLog( 'Updating successful/processed rows in ', 'fa_mass_additions_gt' );
1177
1178 savepoint FAMADC_create7;
1179
1180
1181 update /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt
1182 set gt.line_status = 'PROCESSED'
1183 where book_type_code = p_book_type_code
1184 and line_status like 'VALID%'
1185 and gt.invoice_distribution_id IN
1186 ( select mad.invoice_distribution_id
1187 from fa_mass_additions mad
1188 where mad.invoice_distribution_id = gt.invoice_distribution_id
1189 and mad.book_type_code = p_book_type_code
1190 and mad.create_batch_id = gt.create_batch_id
1191 );
1192
1193
1194 l_count := SQL%ROWCOUNT;
1195 DebugLog('No of Records Processed ', to_char(l_count));
1196
1197 savepoint FAMADC_create8;
1198
1199 DumpGTRecords ( 'FINISH (FAAPI)');
1200 x_return_status := FND_API.G_RET_STS_SUCCESS;
1201
1202 EXCEPTION
1203
1204 when create_err then
1205 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1206 ,p_log_level_rec => g_log_level_rec);
1207 x_return_status := FND_API.G_RET_STS_ERROR;
1208
1209 when fa_ineffective_book then
1210 DebugLog( 'This book does not exist or has a date ineffective on or before today', p_book_type_code );
1211 x_return_status := FND_API.G_RET_STS_ERROR;
1212
1213 when fa_not_corp_book then
1214 DebugLog( 'Mass Additions Create cannot be run for non-corporate book', p_book_type_code );
1215 x_return_status := FND_API.G_RET_STS_ERROR;
1216
1217 when others then
1218 rollback;
1219
1220 fa_srvr_msg.add_sql_error(
1221 calling_fn => l_calling_fn
1222 ,p_log_level_rec => g_log_level_rec);
1223
1224 x_return_status := FND_API.G_RET_STS_ERROR;
1225 END;
1226
1227
1228 END FA_MASSADD_CREATE_PKG;