[Home] [Help]
PACKAGE BODY: APPS.FA_MASSADD_CREATE_PKG
Source
1 PACKAGE BODY FA_MASSADD_CREATE_PKG as
2 /* $Header: FAMADCB.pls 120.34.12020000.5 2013/03/18 11:24:50 rmandali 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
11 type num_tbl_type is table of number index by binary_integer;
12 type rowid_tbl_type is table of rowid index by binary_integer;
13 type char_tbl_type is table of varchar2(15) index by binary_integer;
14
15 g_log_level_rec fa_api_types.log_level_rec_type;
16
17 G_child_iteration_count number := 0; -- used to track recursion level
18
19 G_batch_size number := 1000;
20
21 --------------------------------------------------------------------------------
22
23 FUNCTION Get_Account_Segment
24 (P_segment_num IN NUMBER
25 ,P_base_ccid IN NUMBER
26 ,P_coa_id IN VARCHAR2
27 ,P_calling_fn IN VARCHAR2 DEFAULT NULL
28 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
29 ) RETURN VARCHAR2 IS
30
31 l_result BOOLEAN;
32 l_num_of_segments NUMBER;
33 l_base_segments FND_FLEX_EXT.SEGMENTARRAY;
34 l_debug_info VARCHAR2(240);
35 l_calling_fn VARCHAR2(200) := 'FA_MASS_ADDITIONS_PKG.Get_Account_Segment';
36
37 BEGIN
38
39 if (g_log_level_rec.statement_level) then
40 fa_debug_pkg.add(l_calling_fn
41 ,'Calling FND_FLEX_EXT.Get_segments'
42 ,p_coa_id
43 ,p_log_level_rec => g_log_level_rec);
44 end if;
45
46 l_result := FND_FLEX_EXT.GET_SEGMENTS
47 ('SQLGL'
48 ,'GL#'
49 ,P_coa_id
50 ,P_base_ccid
51 ,l_num_of_segments
52 ,l_base_segments);
53
54 if (g_log_level_rec.statement_level) then
55 fa_debug_pkg.add(l_calling_fn
56 ,'Segment Number'
57 ,l_base_segments(P_segment_num)
58 ,p_log_level_rec => g_log_level_rec);
59 end if;
60
61 return (l_base_segments(P_segment_num));
62
63 EXCEPTION
64 WHEN OTHERS THEN
65 IF (SQLCODE <> -20001) THEN
66 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
67 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
68 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_calling_fn);
69 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
70 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_base_ccid: '
71 ||TO_CHAR(P_base_ccid)
72 ||',P_coa_id: '
73 ||P_coa_id );
74
75 END IF;
76
77 fa_srvr_msg.add_sql_error
78 (calling_fn => l_calling_fn
79 ,p_log_level_rec => g_log_level_rec);
80
81 APP_EXCEPTION.RAISE_EXCEPTION;
82
83
84 END Get_Account_Segment;
85
86 --------------------------------------------------------------------------------
87
88 FUNCTION Prepare_Clearing_GT
89 (p_ledger_id number
90 ,p_book_type_code varchar2
91 ,p_coa_id number
92 ,p_segment_num number
93 ) return boolean is
94
95 l_calling_fn VARCHAR2(200) := 'FA_MASS_ADDITIONS_PKG.Prepare_Clearing_GT';
96
97 l_count number;
98 error_found exception;
99
100 BEGIN
101
102 select count(*)
103 into l_count
104 from fa_book_controls
105 where set_of_books_id = p_ledger_id
106 and book_class = 'CORPORATE';
107
108 if (g_log_level_rec.statement_level) then
109 fa_debug_pkg.add(l_calling_fn
110 ,'number of book for this ledger'
111 ,to_char(l_count)
112 ,p_log_level_rec => g_log_level_rec);
113
114 fa_debug_pkg.add(l_calling_fn
115 ,'inserting'
116 ,'category accounts into GT'
117 ,p_log_level_rec => g_log_level_rec);
118 end if;
119
120 -- load the category gt
121
122 /*fa_categories table code added for Bug:10028857*/
123 if (l_count = 1) then
124
125 insert into fa_category_accounts_gt
126 (clearing_acct
127 ,book_type_code
128 ,asset_type)
129 select clearing_acct
130 ,book_type_code
131 ,decode(max(acct_type),
132 1, 'CIP',
133 'CAPITALIZED')
134 from (select asset_clearing_acct clearing_acct
135 ,book_type_code
136 , 2 acct_type
137 from fa_category_books cb,
138 fa_categories fc
139 where book_type_code = p_book_type_code
140 and cb.category_id = fc.category_id
141 and fc.enabled_flag = 'Y'
142 UNION
143 select cip_clearing_acct , book_type_code, 1
144 from fa_category_books cb,
145 fa_categories fc
146 where cip_clearing_acct is not null
147 and book_type_code = p_book_type_code
148 and cb.category_id = fc.category_id
149 and fc.enabled_flag = 'Y')
150 group by clearing_acct, book_type_code;
151
152 else
153
154 insert into fa_category_accounts_gt
155 (clearing_acct
156 ,book_type_code
157 ,asset_type)
158 select clearing_acct
159 ,book_type_code
160 ,decode(max(acct_type),
161 1, 'CIP',
162 'CAPITALIZED')
163 from (select asset_clearing_acct clearing_acct
164 ,cb.book_type_code
165 ,2 acct_type
166 from fa_category_books cb,
167 fa_book_controls bc,
168 fa_categories fc
169 where cb.book_type_code = bc.book_type_code
170 and bc.book_class = 'CORPORATE'
171 and bc.set_of_books_id = p_ledger_id
172 and cb.category_id = fc.category_id
173 and fc.enabled_flag = 'Y'
174 UNION
175 select cip_clearing_acct
176 ,cb.book_type_code
177 ,1
178 from fa_category_books cb,
179 fa_book_controls bc,
180 fa_categories fc
181 where cip_clearing_acct is not null
182 and cb.book_type_code = bc.book_type_code
183 and bc.book_class = 'CORPORATE'
184 and bc.set_of_books_id = p_ledger_id
185 and cb.category_id = fc.category_id
186 and fc.enabled_flag = 'Y')
187 group by clearing_acct, book_type_code;
188
189 end if;
190
191
192 l_count := SQL%ROWCOUNT;
193 if (g_log_level_rec.statement_level) then
194 fa_debug_pkg.add(l_calling_fn
195 ,'No of Records Inserted '
196 ,to_char(l_count)
197 ,p_log_level_rec => g_log_level_rec);
198 fa_debug_pkg.add(l_calling_fn
199 ,'Deleting duplicate rows from '
200 ,'fa_category_accounts_gt'
201 ,p_log_level_rec => g_log_level_rec);
202 end if;
203
204 -- purge any duplicates from other books
205 -- this will result in FIFO processing if accounts
206 -- are not unique across books!!!!
207
208 delete
209 from fa_category_accounts_gt gt1
210 where gt1.book_type_code <> p_book_type_code
211 and exists
212 (select /*+ index (gt2 FA_CATEGORY_ACCOUNTS_GT_N1) */ 1
213 from fa_category_accounts_gt gt2
214 where gt2.book_type_code = p_book_type_code
215 and gt2.clearing_acct = gt1.clearing_acct);
216
217 l_count := SQL%ROWCOUNT;
218
219 if (g_log_level_rec.statement_level) then
220 fa_debug_pkg.add(l_calling_fn
221 ,'No of Records Deleted '
222 ,to_char(l_count)
223 ,p_log_level_rec => g_log_level_rec);
224 fa_debug_pkg.add(l_calling_fn
225 ,'Deleting duplicate other book rows from '
226 ,'fa_category_accounts_gt'
227 ,p_log_level_rec => g_log_level_rec);
228 end if;
229
230 delete
231 from fa_category_accounts_gt gt1
232 where not exists
233 (select /*+ index (gt2 FA_CATEGORY_ACCOUNTS_GT_N1) */ 1
234 from fa_category_accounts_gt gt2
235 where gt2.book_type_code = p_book_type_code
236 and gt2.clearing_acct = gt1.clearing_acct)
237 and gt1.rowid <>
238 (select min(rowid)
239 from fa_category_accounts_gt gt3
240 where gt3.clearing_acct = gt1.clearing_acct);
241
242 l_count := SQL%ROWCOUNT;
243 if (g_log_level_rec.statement_level) then
244 fa_debug_pkg.add(l_calling_fn
245 ,'No of Records Deleted '
246 ,to_char(l_count)
247 ,p_log_level_rec => g_log_level_rec);
248 end if;
249
250 return true;
251
252 exception
253 when error_found then
254 return false;
255
256 WHEN OTHERS THEN
257 fa_srvr_msg.add_sql_error
258 (calling_fn => l_calling_fn
259 ,p_log_level_rec => g_log_level_rec);
260
261 RETURN FALSE;
262 end;
263
264
265 --------------------------------------------------------------------------------
266
267 -- NOTE: the incoming mass_addition_id is always the root parent (not immedate one)!
268
269 FUNCTION Preprocess_Child_Lines
270 (p_book_type_code IN varchar2
271 ,p_mode IN varchar2
272 ,p_invoice_dist_id_tbl IN num_tbl_type
273 ,p_mass_add_id_tbl IN num_tbl_type
274 ,p_asset_id_tbl IN num_tbl_type
275 ,p_line_status_tbl IN char_tbl_type
276 ,p_posting_status_tbl IN char_tbl_type
277 ,p_queue_name_tbl IN char_tbl_type
278 ,p_asset_type_tbl IN char_tbl_type
279 ,p_merged_code_tbl IN char_tbl_type) RETURN BOOLEAN IS
280
281
282 l_calling_fn varchar2(80) :=
283 'FA_MASSADD_CREATE_PKG.Preprocess_Child_Lines';
284
285 l_invoice_dist_id_tbl num_tbl_type;
286 l_mass_add_id_tbl num_tbl_type;
287 l_asset_id_tbl num_tbl_type;
288 l_line_status_tbl char_tbl_type;
289 l_posting_status_tbl char_tbl_type;
290 l_queue_name_tbl char_tbl_type;
291 l_asset_type_tbl char_tbl_type;
292 l_merged_code_tbl char_tbl_type;
293
294 error_found exception;
295
296 begin
297
298 G_child_iteration_count := G_child_iteration_count + 1;
299
300 forall i in 1..p_invoice_dist_id_tbl.count
301 update /*+ index(FA_MASS_ADDITIONS_GT FA_MASS_ADDITIONS_GT_N4) */fa_mass_additions_gt
302 set mass_addition_id = fa_mass_additions_s.nextval
303 ,book_type_code = p_book_type_code
304 ,line_status = p_line_status_tbl(i)
305 ,posting_status = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_posting_status_tbl(i), 'NEW') /* ER 14739752 */
306 ,queue_name = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_queue_name_tbl(i), 'NEW')
307 ,asset_type = p_asset_type_tbl(i)
308 ,split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_merged_code_tbl(i),NULL) /* ER 14739752 */
309 ,merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_merged_code_tbl(i),NULL)
310 ,parent_mass_addition_id = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_mass_add_id_tbl(i),NULL)
311 ,merge_parent_mass_additions_id = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_mass_add_id_tbl(i),NULL)
312 ,add_to_asset_id = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', p_asset_id_tbl(i),NULL)
313 where parent_invoice_dist_id = p_invoice_dist_id_tbl(i)
314 and line_status = 'NEW'
315 and ledger_category_code = 'P'
316 returning invoice_distribution_id
317 ,parent_mass_addition_id
318 ,add_to_asset_id
319 ,line_status
320 ,posting_status
321 ,queue_name
322 ,asset_type
323 ,merged_code
324 bulk collect
325 into l_invoice_dist_id_tbl
326 ,l_mass_add_id_tbl
327 ,l_asset_id_tbl
328 ,l_line_status_tbl
329 ,l_posting_status_tbl
330 ,l_queue_name_tbl
331 ,l_asset_type_tbl
332 ,l_merged_code_tbl;
333
334 if (g_log_level_rec.statement_level) then
335 fa_debug_pkg.add(l_calling_fn
336 ,'No of elements in the p_inv_dist_array'
337 ,p_invoice_dist_id_tbl.count
338 ,p_log_level_rec => g_log_level_rec);
339 fa_debug_pkg.add(l_calling_fn
340 ,'No of updated records fetched for iteration' || to_char(G_child_iteration_count)
341 , to_char(l_invoice_dist_id_tbl.count)
342 ,p_log_level_rec => g_log_level_rec);
343 end if;
344
345 if (l_invoice_dist_id_tbl.count <> 0) then
346
347 -- continue performing the recursive call until no matches are found
348 if not Preprocess_Child_Lines(p_book_type_code => p_book_type_code
349 ,p_mode => 'GT'
350 ,p_invoice_dist_id_tbl => l_invoice_dist_id_tbl
351 ,p_mass_add_id_tbl => l_mass_add_id_tbl
352 ,p_asset_id_tbl => l_asset_id_tbl
353 ,p_line_status_tbl => l_line_status_tbl
354 ,p_posting_status_tbl => l_posting_status_tbl
355 ,p_queue_name_tbl => l_queue_name_tbl
356 ,p_asset_type_tbl => l_asset_type_tbl
357 ,p_merged_code_tbl => l_merged_code_tbl) then
358 raise error_found;
359 end if;
360 end if;
361
362 G_child_iteration_count := G_child_iteration_count - 1;
363
364 return true;
365
366 exception
367 WHEN OTHERS THEN
368 fa_srvr_msg.add_sql_error
369 (calling_fn => l_calling_fn
370 ,p_log_level_rec => g_log_level_rec);
371
372 RETURN FALSE;
373
374 end;
375
376 --------------------------------------------------------------------------------
377 --
378 -- for a child line with a split parent, insert the lines as non-merged for now
379 --
380 --------------------------------------------------------------------------------
381
382
383 FUNCTION Preprocess_Split_Lines
384 (p_book_type_code IN varchar2
385 ,p_mode IN varchar2
386 ,p_invoice_dist_id_tbl IN num_tbl_type
387 ,p_asset_type_tbl IN char_tbl_type
388 ) RETURN BOOLEAN IS
389
390
391 l_calling_fn varchar2(80) := 'FA_MASSADD_CREATE_PKG.Preprocess_Split_Lines';
392
393 l_invoice_dist_id_tbl num_tbl_type;
394 l_mass_add_id_tbl num_tbl_type;
395 l_asset_id_tbl num_tbl_type;
396 l_line_status_tbl char_tbl_type;
397 l_posting_status_tbl char_tbl_type;
398 l_queue_name_tbl char_tbl_type;
399 l_asset_type_tbl char_tbl_type;
400 l_merged_code_tbl char_tbl_type;
401
402 error_found exception;
403
404 begin
405
406 forall i in 1..p_invoice_dist_id_tbl.count
407 update /*+ index(FA_MASS_ADDITIONS_GT FA_MASS_ADDITIONS_GT_N4) */ fa_mass_additions_gt
408 set mass_addition_id = fa_mass_additions_s.nextval
409 ,book_type_code = p_book_type_code
410 ,line_status = 'VALID'
411 ,posting_status = 'NEW'
412 ,queue_name = 'NEW'
413 ,asset_type = p_asset_type_tbl(i)
414 where parent_invoice_dist_id = p_invoice_dist_id_tbl(i)
415 and line_status = 'NEW'
416 and ledger_category_code = 'P'
417 returning invoice_distribution_id, mass_addition_id, asset_type
418 bulk collect
419 into l_invoice_dist_id_tbl, l_mass_add_id_tbl, l_asset_type_tbl;
420
421 if (g_log_level_rec.statement_level) then
422 fa_debug_pkg.add(l_calling_fn,
423 'No of elements in the p_inv_dist_array'
424 ,p_invoice_dist_id_tbl.count
425 ,p_log_level_rec => g_log_level_rec);
426 fa_debug_pkg.add(l_calling_fn
427 ,'No of updated records updated for split MAD/AI lines'
428 , l_invoice_dist_id_tbl.count
429 ,p_log_level_rec => g_log_level_rec);
430 end if;
431
432 if (l_invoice_dist_id_tbl.count <> 0) then
433
434 -- proactive set the values for merged children
435 for i in 1..l_invoice_dist_id_tbl.count loop
436 l_asset_id_tbl(l_asset_id_tbl.count + 1) := null;
437 l_line_status_tbl(l_line_status_tbl.count + 1) := 'VALID';
438 if (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N') = 'Y') then /* ER 14739752 */
439 l_posting_status_tbl(l_posting_status_tbl.count + 1) := 'NEW';
440 l_merged_code_tbl(l_merged_code_tbl.count + 1) := null;
441 else
442 l_posting_status_tbl(l_posting_status_tbl.count + 1) := 'MERGED';
443 l_merged_code_tbl(l_merged_code_tbl.count + 1) := 'MC';
444 end if;
445 l_queue_name_tbl(l_queue_name_tbl.count + 1) := 'NEW';
446
447 end loop;
448
449 -- flag these updated lines as parent MP where applicable
450 forall i in 1..l_mass_add_id_tbl.count
451 update /*+ index(gt FA_MASS_ADDITIONS_GT_N3) */ fa_mass_additions_gt gt
452 set split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL), /* ER 14739752 */
453 merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL)
454 where mass_addition_id = l_mass_add_id_tbl(i)
455 and book_type_code = p_book_type_code
456 and invoice_payment_id is null -- exclude discounts
457 and ledger_category_code = 'P'
458 and exists
459 (select 1
460 from fa_mass_additions_gt gt2
461 where gt2.parent_invoice_dist_id = gt.invoice_distribution_id
462 and gt2.ledger_category_code = 'P' --Bug#10263900
463 and gt2.rowid <> gt.rowid);
464
465 -- continue performing the recursive call until no matches are found
466 if not Preprocess_Child_Lines(p_book_type_code => p_book_type_code
467 ,p_mode => 'GT'
468 ,p_invoice_dist_id_tbl => l_invoice_dist_id_tbl
469 ,p_mass_add_id_tbl => l_mass_add_id_tbl
470 ,p_asset_id_tbl => l_asset_id_tbl
471 ,p_line_status_tbl => l_line_status_tbl
472 ,p_posting_status_tbl => l_posting_status_tbl
473 ,p_queue_name_tbl => l_queue_name_tbl
474 ,p_asset_type_tbl => l_asset_type_tbl
475 ,p_merged_code_tbl => l_merged_code_tbl) then
476 raise error_found;
477 end if;
478
479 end if;
480
481
482 return true;
483
484 exception
485 when error_found then
486 return false;
487
488 WHEN OTHERS THEN
489 fa_srvr_msg.add_sql_error
490 (calling_fn => l_calling_fn
491 ,p_log_level_rec => g_log_level_rec);
492
493 RETURN FALSE;
494
495 END;
496
497 --------------------------------------------------------------------------------
498 --
499 -- update the immediate child lines (those linked directly to item/accrual)
500 -- we will execute this recursively to work down from second level (MISC/FREIGHT)
501 -- to lower levels (TAX/DISCOUNT) and continue until no rows are updated
502 --
503 -- note this is called multiple times, so do not add savepoints directly to this
504 -- but in the caller... the first poses no risks
505 --
506 -- the first is for children whose parents are in the GT
507 -- (this would pose no risk of a split parent, but could be mult-level)
508 --
509 -- the second is for children whose immediate parents are
510 -- children to parents in the massadd interface
511 -- (this would pose a risk of a split parent and could be mult-level)
512 --
513 -- the third is for children whose immediate parents are
514 -- children to parents in asset_invoices
515 -- (this would pose a risk of a split parent and could be mult-level
516 -- but we will not be doing add-to-assets)
517 --
518 -- thus the status will be used to later determine if the merge_parent
519 -- should be the immediate parent line or the root parent or none in
520 -- the add-to-asset case
521 --
522 --------------------------------------------------------------------------------
523
524 FUNCTION Preprocess_GT_Records
525 (p_book_type_code IN varchar2
526 ,p_coa_id IN number
527 ,p_segment_num IN number
528 ,p_column_name IN varchar2
529 ,p_ledger_id IN NUMBER
530 ,p_def_dpis_dt IN DATE
531 ) RETURN BOOLEAN IS
532
533 l_calling_fn varchar2(80) := 'FA_MASSADD_CREATE_PKG.Preprocess_GT_Records';
534 l_count number;
535 l_sql varchar(4000);
536 error_found exception;
537
538
539 l_rowid rowid_tbl_type;
540 l_mass_addition_id_tbl num_tbl_type;
541
542 -- used for top level orphan children
543 l_invoice_dist_id_tbl num_tbl_type;
544 l_mass_add_id_tbl num_tbl_type;
545 l_line_status_tbl char_tbl_type;
546
547 l_add_to_asset_id_tbl num_tbl_type;
548 l_asset_id_tbl num_tbl_type;
549 l_mad_count_tbl num_tbl_type;
550 l_posting_status_tbl char_tbl_type;
551 l_queue_name_tbl char_tbl_type;
552 l_asset_type_tbl char_tbl_type;
553 l_merged_code_tbl char_tbl_type;
554
555 l_temp_inv_dist_id1_tbl num_tbl_type;
556
557 l_child_inv_dist_id1_tbl num_tbl_type;
558 l_child_mass_add_id1_tbl num_tbl_type;
559 l_child_asset_id1_tbl num_tbl_type;
560 l_child_line_status1_tbl char_tbl_type;
561 l_child_posting_status1_tbl char_tbl_type;
562 l_child_queue_name1_tbl char_tbl_type;
563 l_child_asset_type1_tbl char_tbl_type;
564 l_child_merged_code1_tbl char_tbl_type;
565
566 l_child_inv_dist_id1A_tbl num_tbl_type;
567 l_child_asset_id1A_tbl num_tbl_type;
568 l_child_line_status1A_tbl char_tbl_type;
569 l_child_asset_type1A_tbl char_tbl_type;
570
571 -- used for splits
572 l_child_inv_dist_id2_tbl num_tbl_type;
573 l_child_asset_type2_tbl char_tbl_type;
574
575 l_ai_count_tbl num_tbl_type;
576 l_ai_distinct_asset_count_tbl num_tbl_type;
577
578 cursor c_rejected is
579 select mad.invoice_distribution_id,
580 nvl(mad.parent_mass_addition_id,mad.mass_addition_id)
581 from fa_mass_additions mad
582 where book_type_code = p_book_type_code
583 and posting_status = 'DELETE'
584 and not exists
585 (select 1
586 from fa_mass_additions mad2
587 where mad2.book_type_code = p_book_type_code
588 and mad2.invoice_distribution_id = mad.invoice_distribution_id
589 and mad2.posting_status not in ('DELETE', 'SPLIT'))
590 union
591 select mad_c.invoice_distribution_id,
592 nvl(mad_c.parent_mass_addition_id,mad_c.mass_addition_id)
593 from fa_mass_additions mad_c,
594 fa_mass_additions mad_p
595 where mad_p.book_type_code = p_book_type_code
596 and mad_p.posting_status = 'DELETE'
597 and mad_c.parent_mass_addition_id = mad_p.mass_addition_id
598 and not exists
599 (select 1
600 from fa_mass_additions mad2
601 where mad2.book_type_code = p_book_type_code
602 and mad2.invoice_distribution_id = mad_p.invoice_distribution_id
603 and mad2.posting_status not in ('DELETE', 'SPLIT')) ;
604
605 -- Bug 13809266 : Allow unallocated lines from AP
606 cursor c_item_accrual is
607 select /*+ index(gt FA_MASS_ADDITIONS_GT_N2) */ rowid,
608 fa_mass_additions_s.nextval
609 from fa_mass_additions_gt gt
610 where (gt.line_type_lookup_code in ('ITEM', 'ACCRUAL') OR
611 (gt.line_type_lookup_code in ('NONREC_TAX','FREIGHT','MISCELLANEOUS') and
612 gt.parent_invoice_dist_id is null and
613 nvl(fa_cache_pkg.fazcbc_record.allow_unallocated_lines_flag,'N') = 'Y')
614 )
615 and gt.ledger_category_code = 'P'
616 and gt.line_status = 'NEW'
617 and gt.invoice_payment_id is null; -- exclude discounts
618 -- and gt.parent_invoice_dist_id is null; -- exclude corrections
619
620 cursor c_orphans is
621 select mad.invoice_distribution_id,
622 min(nvl(mad.parent_mass_addition_id,mad.mass_addition_id)),
623 min(mad.add_to_asset_id),
624 min(ad.asset_id),
625 min(mad.posting_status),
626 max(mad.asset_type),
627 count(distinct mad.rowid)
628 from fa_mass_additions_gt gt,
629 fa_mass_additions mad,
630 fa_additions_b ad
631 where ad.asset_number(+) = mad.asset_number
632 and mad.book_type_code = p_book_type_code
633 and mad.invoice_distribution_id = gt.parent_invoice_dist_id
634 -- BUG# 9162562 - see discussion for why we can only
635 -- join by dist_id here...
636 -- and mad.invoice_id = gt.invoice_id
637 and mad.posting_status not in ('SPLIT', 'DELETE')
638 and mad.invoice_payment_id is null -- do not merge to discount
639 and gt.ledger_category_code = 'P'
640 and gt.line_status = 'NEW'
641 group by mad.invoice_distribution_id;
642
643 cursor c_ai_parents is
644 select ai.invoice_distribution_id,
645 min(ai.asset_id),
646 max(ad.asset_type),
647 count(distinct ai.rowid),
648 count(distinct ai.asset_id)
649 from fa_asset_invoices ai,
650 fa_additions_b ad,
651 fa_mass_additions_gt gt
652 where ai.invoice_distribution_id = gt.parent_invoice_dist_id
653 and ad.asset_id = ai.asset_id
654 and gt.ledger_category_code = 'P'
655 and gt.line_status = 'NEW'
656 group by ai.invoice_distribution_id;
657
658 begin
659
660 -- populates the clearing gt used for ITEM/ACCRUAL category/book validation
661
662 if (g_log_level_rec.statement_level) then
663 fa_debug_pkg.add(l_calling_fn
664 ,'Calling '
665 ,'Prepare_Clearing_GT '
666 ,p_log_level_rec => g_log_level_rec);
667 end if;
668
669 savepoint FAMADC_preprocess1;
670
671 if not Prepare_Clearing_GT (p_ledger_id => p_ledger_id
672 ,p_book_type_code => p_book_type_code
673 ,p_coa_id => p_coa_id
674 ,p_segment_num => p_segment_num
675 ) then
676 raise error_found;
677 end if;
678
679 if (g_log_level_rec.statement_level) then
680 fa_debug_pkg.add(l_calling_fn
681 ,'Updating children to rejected'
682 ,'due to parents in the delete queue'
683 ,p_log_level_rec => g_log_level_rec);
684 end if;
685
686 savepoint FAMADC_preprocess2;
687
688 -- remove any child lines in NEW status whose only matching parents are in the delete queue
689 -- per discussion with AP Dev/PM this is correct (just as if te parent was excluded in
690 -- the post accounting setups)
691 if (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N') = 'N') then /* ER 14739752 */
692 open c_rejected;
693
694 loop
695 fetch c_rejected bulk collect
696 into l_invoice_dist_id_tbl, l_mass_add_id_tbl
697 limit G_batch_size;
698
699 if (l_invoice_dist_id_tbl.count = 0) then
700 exit;
701 end if;
702
703 if (g_log_level_rec.statement_level) then
704 fa_debug_pkg.add(l_calling_fn
705 ,'No of rejected parents fetched'
706 ,l_invoice_dist_id_tbl.count
707 ,p_log_level_rec => g_log_level_rec);
708 fa_debug_pkg.add(l_calling_fn
709 ,'calling'
710 ,'child line hook'
711 ,p_log_level_rec => g_log_level_rec);
712 end if;
713
714 -- update the child lines of these recursively
715
716 if (l_invoice_dist_id_tbl.count > 0) then
717 for i in 1..l_invoice_dist_id_tbl.count loop
718 l_asset_id_tbl(i) := null;
719 l_posting_status_tbl(i) := 'NEW';
720 l_queue_name_tbl(i) := 'NEW';
721 l_asset_type_tbl(i) := 'CAPITALIZED';
722 l_merged_code_tbl(i) := null;
723 l_line_status_tbl(i) := 'REJECTED';
724 end loop;
725
726 savepoint FAMADC_preprocess3;
727
728 if not Preprocess_Child_Lines
729 (p_book_type_code => p_book_type_code
730 ,p_mode => 'REJECT'
731 ,p_invoice_dist_id_tbl => l_invoice_dist_id_tbl
732 ,p_mass_add_id_tbl => l_mass_add_id_tbl
733 ,p_asset_id_tbl => l_asset_id_tbl
734 ,p_line_status_tbl => l_line_status_tbl
735 ,p_posting_status_tbl => l_posting_status_tbl
736 ,p_queue_name_tbl => l_queue_name_tbl
737 ,p_asset_type_tbl => l_asset_type_tbl
738 ,p_merged_code_tbl => l_merged_code_tbl) then
739 raise error_found;
740 end if;
741 end if;
742 end loop;
743
744 close c_rejected;
745 end if;
746
747 if (g_log_level_rec.statement_level) then
748 fa_debug_pkg.add(l_calling_fn
749 ,'processing'
750 ,'item and accrual lines'
751 ,p_log_level_rec => g_log_level_rec);
752 end if;
753
754 -- update the parents - this is the driving logic. top level parents
755 -- (in this case ITEM / ACCRUAL lines) will be based on clearing account
756 -- validation
757
758 -- rather than in-line update, splitting this into bulk fetch/bulk update
759 -- in order to keep control of the array size of the bulk returned values
760
761 savepoint FAMADC_preprocess4;
762
763 open c_item_accrual;
764 loop
765
766 if (g_log_level_rec.statement_level) then
767 fa_debug_pkg.add(l_calling_fn
768 ,'fetching '
769 ,'item / accrual'
770 ,p_log_level_rec => g_log_level_rec);
771 end if;
772
773 -- update the parents - this is the driving logic. top level
774 fetch c_item_accrual bulk collect
775 into l_rowid,
776 l_mass_addition_id_tbl
777 limit g_batch_size;
778
779 if (l_rowid.count = 0) then
780 exit;
781 end if;
782
783 if (g_log_level_rec.statement_level) then
784 fa_debug_pkg.add(l_calling_fn
785 ,'updating item / accrual, array count: '
786 ,l_rowid.count
787 ,p_log_level_rec => g_log_level_rec);
788 fa_debug_pkg.add(l_calling_fn
789 ,'segment_num: '
790 ,p_segment_num
791 ,p_log_level_rec => g_log_level_rec);
792 end if;
793
794
795 /*code for second decode changed for Bug:10028857*/
796 l_sql := '
797 update fa_mass_additions_gt gt
798 set (asset_type, line_status, book_type_code, mass_addition_id) =
799 (select /*+ index(fca FA_CATEGORY_ACCOUNTS_GT_N2 */
800 decode(gt.asset_type,
801 null, decode(glcc.account_type,
802 ''E'', ''EXPENSED'',
803 nvl(fca.asset_type, gt.asset_type)),
804 gt.asset_type),
805 decode(glcc.account_type,
806 ''E'', ''VALID'',
807 decode(gt.book_type_code,
808 null, decode(fca.book_type_code,
809 :h_book_type_code, ''VALID'',
810 null, ''REJECTED'',
811 ''OTHER BOOK''),
812 decode(gt.book_type_code,
813 :h_book_type_code, decode(fca.book_type_code,
814 :h_book_type_code, ''VALID'',
815 ''REJECTED''),
816 ''OTHER BOOK''))),
817 decode(glcc.account_type,
818 ''E'', :h_book_type_code,
819 decode(gt.book_type_code,
820 null, decode(fca.book_type_code,
821 :h_book_type_code, :h_book_type_code,
822 null),
823 gt.book_type_code)),
824 :mass_add_id
825 from gl_code_combinations glcc,
826 fa_category_accounts_gt fca
827 where gt.payables_code_combination_id = glcc.code_combination_id
828 and fca.clearing_acct(+) = ' ||
829 ' glcc.' || p_column_name || '
830 )
831 where rowid = :l_rowid
832 returning invoice_distribution_id, mass_addition_id, line_status,
833 asset_type
834 into :inv_tbl, :massadd_tbl, :line_status_tbl,
835 :asset_type_tbl';
836
837 -- BMR: the above fails to set the line status on item/accruals
838 -- which have no match clearing table.... (glcc.type = A)
839 -- double check this - believe its fixed...
840
841 FORALL i in 1..l_rowid.count
842 EXECUTE IMMEDIATE l_sql
843 USING p_book_type_code
844 ,p_book_type_code
845 ,p_book_type_code
846 ,p_book_type_code
847 ,p_book_type_code
848 ,p_book_type_code
849 ,l_mass_addition_id_tbl(i)
850 ,l_rowid(i)
851 RETURNING BULK COLLECT
852 INTO l_invoice_dist_id_tbl, l_mass_add_id_tbl,
853 l_line_status_tbl, l_asset_type_tbl;
854
855 if (g_log_level_rec.statement_level) then
856 fa_debug_pkg.add(l_calling_fn
857 ,'No of item/accrual lines Updated '
858 ,l_invoice_dist_id_tbl.count
859 ,p_log_level_rec => g_log_level_rec);
860 fa_debug_pkg.add(l_calling_fn
861 ,'calling'
862 ,'child line hook'
863 ,p_log_level_rec => g_log_level_rec);
864 end if;
865
866 -- update the child lines which have their parent in the gt
867 -- in new code (one itteration) this will be everything including some discounts
868
869 if (l_invoice_dist_id_tbl.count > 0) then
870 for i in 1..l_invoice_dist_id_tbl.count loop
871 l_asset_id_tbl(i) := null;
872 if (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N') = 'Y') then /* ER 14739752 */
873 l_posting_status_tbl(i) := 'NEW';
874 l_merged_code_tbl(i) := null;
875 else
876 l_posting_status_tbl(i) := 'MERGED';
877 l_merged_code_tbl(i) := 'MC';
878 end if;
879 l_queue_name_tbl(i) := 'NEW';
880 end loop;
881
882 if not Preprocess_Child_Lines
883 (p_book_type_code => p_book_type_code
884 ,p_mode => 'GT'
885 ,p_invoice_dist_id_tbl => l_invoice_dist_id_tbl
886 ,p_mass_add_id_tbl => l_mass_add_id_tbl
887 ,p_asset_id_tbl => l_asset_id_tbl
888 ,p_line_status_tbl => l_line_status_tbl
889 ,p_posting_status_tbl => l_posting_status_tbl
890 ,p_queue_name_tbl => l_queue_name_tbl
891 ,p_asset_type_tbl => l_asset_type_tbl
892 ,p_merged_code_tbl => l_merged_code_tbl) then
893 raise error_found;
894 end if;
895 end if;
896 /*10209969 - Need to update inside loop as l_mass_add_id_tbl is fetched in each iteration */
897 -- flag the parent as MP
898 forall i in 1..l_mass_add_id_tbl.count
899 update fa_mass_additions_gt gt
900 set split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL), /* ER 14739752 */
901 merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL)
902 where mass_addition_id = l_mass_add_id_tbl(i)
903 and book_type_code = p_book_type_code
904 and invoice_payment_id is null
905 and ledger_category_code = 'P'
906 and exists
907 (select 1
908 from fa_mass_additions_gt gt2
909 where gt2.parent_invoice_dist_id = gt.invoice_distribution_id
910 and gt2.ledger_category_code = 'P' --Bug#10263900
911 and gt2.rowid <> gt.rowid);
912 end loop; -- bulk
913
914 close c_item_accrual;
915
916 savepoint FAMADC_preprocess5;
917
918 if (g_log_level_rec.statement_level) then
919 fa_debug_pkg.add(l_calling_fn
920 ,'Processing'
921 ,'orphan child lines with parents in MAD'
922 ,p_log_level_rec => g_log_level_rec);
923 end if;
924
925 -- update child lines with parent info when match is found
926 -- these are lines whose parents were brought over in
927 -- previous runs. Per new AP dependancy, the book is always
928 -- populated on such lines ***
929
930 -- in new code (one iteration) this would only include discounts processed
931 -- after invoice was originally paid/accounted/transfered but due to datafixes
932 -- its possible other lines could come in as well... In either case, the root
933 -- ITEM/ACCURAL line must have previously posted in the dfix case
934
935 -- the code used counts to check if parent was split and if so
936 -- we currently do not try to auto-split and follow... the complexities
937 -- and corner cases will/would make this complex. (e.g. what if some
938 -- parents posted and others had not - how do you do allocation of cost?)
939
940 open c_orphans;
941
942 loop
943
944 fetch c_orphans bulk collect
945 into l_invoice_dist_id_tbl
946 ,l_mass_add_id_tbl
947 ,l_add_to_asset_id_tbl
948 ,l_asset_id_tbl
949 ,l_posting_status_tbl
950 ,l_asset_type_tbl
951 ,l_mad_count_tbl
952 limit g_batch_size;
953
954 if (g_log_level_rec.statement_level) then
955 fa_debug_pkg.add(l_calling_fn
956 ,'No of MAD orphans fetched'
957 ,l_invoice_dist_id_tbl.count
958 ,p_log_level_rec => g_log_level_rec);
959 end if;
960
961 if (l_invoice_dist_id_tbl.count = 0) then
962 exit;
963 end if;
964
965 l_child_inv_dist_id1_tbl.delete;
966 l_child_mass_add_id1_tbl.delete;
967 l_child_asset_id1_tbl.delete;
968 l_child_line_status1_tbl.delete;
969 l_child_posting_status1_tbl.delete;
970 l_child_queue_name1_tbl.delete;
971 l_child_asset_type1_tbl.delete;
972 l_child_merged_code1_tbl.delete;
973
974 l_child_inv_dist_id2_tbl.delete;
975 l_child_asset_type2_tbl.delete;
976
977 for i in 1..l_invoice_dist_id_tbl.count loop
978
979 if (l_mad_count_tbl(i) = 1) then
980
981 -- match found, now handle merge / add to asset
982 if (l_posting_status_tbl(i) <> 'POSTED') then
983
984 -- NOTE: POSTED lines will fall into the AI handling later
985
986 l_child_inv_dist_id1_tbl(l_child_inv_dist_id1_tbl.count + 1) := l_invoice_dist_id_tbl(i);
987 l_child_mass_add_id1_tbl(l_child_mass_add_id1_tbl.count + 1) := l_mass_add_id_tbl(i);
988 l_child_asset_id1_tbl(l_child_asset_id1_tbl.count + 1) := NULL;
989 l_child_line_status1_tbl(l_child_line_status1_tbl.count + 1) := 'VALID';
990 if (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N') = 'Y') then /* ER 14739752 */
991 l_child_posting_status1_tbl(l_child_posting_status1_tbl.count + 1) := 'NEW';
992 l_child_merged_code1_tbl(l_child_merged_code1_tbl.count + 1) := null;
993 else
994 l_child_posting_status1_tbl(l_child_posting_status1_tbl.count + 1) := 'MERGED';
995 l_child_merged_code1_tbl(l_child_merged_code1_tbl.count + 1) := 'MC';
996 end if;
997 l_child_queue_name1_tbl(l_child_queue_name1_tbl.count + 1) := 'NEW';
998 l_child_asset_type1_tbl(l_child_asset_type1_tbl.count + 1) := l_asset_type_tbl(i);
999
1000 end if;
1001
1002 else
1003 -- parent lines were split
1004 l_child_inv_dist_id2_tbl(l_child_inv_dist_id2_tbl.count + 1) := l_invoice_dist_id_tbl(i);
1005 l_child_asset_type2_tbl(l_child_asset_type2_tbl.count + 1) := l_asset_type_tbl(i);
1006
1007 end if;
1008
1009 end loop;
1010
1011 if (g_log_level_rec.statement_level) then
1012 fa_debug_pkg.add(l_calling_fn
1013 ,'No of simple MAD orphans'
1014 ,l_child_inv_dist_id1_tbl.count
1015 ,p_log_level_rec => g_log_level_rec);
1016 fa_debug_pkg.add(l_calling_fn
1017 ,'No of split MAD orphans'
1018 ,l_child_inv_dist_id2_tbl.count
1019 ,p_log_level_rec => g_log_level_rec);
1020 end if;
1021
1022 if (l_child_mass_add_id1_tbl.count > 0) then
1023
1024 -- flag the parent as MP
1025 -- no need for existance check as we're already driving by child
1026 forall i in 1..l_child_mass_add_id1_tbl.count
1027 update fa_mass_additions
1028 set split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL), /* ER 14739752 */
1029 merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL)
1030 where mass_addition_id = l_child_mass_add_id1_tbl(i)
1031 and book_type_code = p_book_type_code;
1032
1033 -- process singles
1034 if not Preprocess_Child_Lines
1035 (p_book_type_code => p_book_type_code
1036 ,p_mode => 'MAD'
1037 ,p_invoice_dist_id_tbl => l_child_inv_dist_id1_tbl
1038 ,p_mass_add_id_tbl => l_child_mass_add_id1_tbl
1039 ,p_asset_id_tbl => l_child_asset_id1_tbl
1040 ,p_line_status_tbl => l_child_line_status1_tbl
1041 ,p_posting_status_tbl => l_child_posting_status1_tbl
1042 ,p_queue_name_tbl => l_child_queue_name1_tbl
1043 ,p_asset_type_tbl => l_child_asset_type1_tbl
1044 ,p_merged_code_tbl => l_child_merged_code1_tbl) then
1045 raise error_found;
1046 end if;
1047 end if;
1048
1049 -- process splits
1050 if (l_child_inv_dist_id2_tbl.count > 0) then
1051 if not Preprocess_Split_Lines
1052 (p_book_type_code => p_book_type_code
1053 ,p_mode => 'MAD'
1054 ,p_invoice_dist_id_tbl => l_child_inv_dist_id2_tbl
1055 ,p_asset_type_tbl => l_child_asset_type2_tbl) then
1056 raise error_found;
1057 end if;
1058 end if;
1059
1060 end loop; -- bulk
1061
1062 close c_orphans;
1063
1064 if (g_log_level_rec.statement_level) then
1065 fa_debug_pkg.add(l_calling_fn
1066 ,'processing'
1067 ,'asset invoices children'
1068 ,p_log_level_rec => g_log_level_rec);
1069 end if;
1070
1071 savepoint FAMADC_preprocess6;
1072
1073 -- process children whose parents are now in asset invoices as valid
1074 -- but were purged from massadd interface
1075
1076 open c_ai_parents;
1077
1078 loop
1079
1080 fetch c_ai_parents bulk collect
1081 into l_invoice_dist_id_tbl,
1082 l_asset_id_tbl,
1083 l_asset_type_tbl,
1084 l_ai_count_tbl,
1085 l_ai_distinct_asset_count_tbl
1086 limit g_batch_size;
1087
1088 if (g_log_level_rec.statement_level) then
1089 fa_debug_pkg.add(l_calling_fn
1090 ,'No AI orphans fetched'
1091 ,l_invoice_dist_id_tbl.count
1092 ,p_log_level_rec => g_log_level_rec);
1093 end if;
1094
1095 if (l_invoice_dist_id_tbl.count = 0) then
1096 exit;
1097 end if;
1098
1099 l_child_inv_dist_id1_tbl.delete;
1100 l_child_mass_add_id1_tbl.delete;
1101 l_child_asset_id1_tbl.delete;
1102 l_child_line_status1_tbl.delete;
1103 l_child_posting_status1_tbl.delete;
1104 l_child_queue_name1_tbl.delete;
1105 l_child_asset_type1_tbl.delete;
1106 l_child_merged_code1_tbl.delete;
1107
1108 l_child_inv_dist_id1A_tbl.delete;
1109 l_child_asset_id1A_tbl.delete;
1110 l_child_line_status1A_tbl.delete;
1111 l_child_asset_type1A_tbl.delete;
1112
1113 l_child_inv_dist_id2_tbl.delete;
1114 l_child_asset_type2_tbl.delete;
1115
1116 for i in 1..l_invoice_dist_id_tbl.count loop
1117
1118 if (l_ai_count_tbl(i) = 1 or l_ai_distinct_asset_count_tbl(i) = 1) then
1119
1120 -- single match found which is the simplest case proceed with add-to-asset
1121 -- except for inv/asset/mad arrays, the others are preset for
1122 -- subsequent recursive call rather then returning clause
1123
1124 l_child_inv_dist_id1_tbl(l_child_inv_dist_id1_tbl.count + 1) := l_invoice_dist_id_tbl(i);
1125 l_child_asset_id1_tbl(l_child_asset_id1_tbl.count + 1) := l_asset_id_tbl(i);
1126 l_child_asset_type1_tbl(l_child_asset_type1_tbl.count + 1) := l_asset_type_tbl(i);
1127
1128 else
1129 -- more than one ai row... we will bring these in but they must be handled by customer for now
1130 l_child_inv_dist_id2_tbl(l_child_inv_dist_id2_tbl.count + 1) := l_invoice_dist_id_tbl(i);
1131 l_child_asset_type2_tbl(l_child_asset_type2_tbl.count + 1) := l_asset_type_tbl(i);
1132
1133
1134 end if;
1135
1136 end loop;
1137
1138 if (g_log_level_rec.statement_level) then
1139 fa_debug_pkg.add(l_calling_fn, 'No of simple AI orphans',l_child_inv_dist_id1_tbl.count
1140 ,p_log_level_rec => g_log_level_rec);
1141 fa_debug_pkg.add(l_calling_fn, 'No of split AI orphans', l_child_inv_dist_id2_tbl.count
1142 ,p_log_level_rec => g_log_level_rec);
1143 end if;
1144
1145 if (l_child_inv_dist_id1_tbl.count > 0) then
1146
1147 -- in the AI case, the first level line is GT
1148 -- will not be a MC line (unlike MAD case) and thus
1149 -- we need to do one update outside the recursive routine here
1150
1151 forall i in 1..l_child_inv_dist_id1_tbl.count
1152 update /*+ index(FA_MASS_ADDITIONS_GT FA_MASS_ADDITIONS_GT_N4) */ fa_mass_additions_gt
1153 set mass_addition_id = fa_mass_additions_s.nextval,
1154 --book_type_code = p_book_type_code,
1155 line_status = 'VALID',
1156 posting_status = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'ON HOLD','NEW'), /* ER 14739752 */
1157 queue_name = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'ON HOLD','NEW'),
1158 asset_type = l_child_asset_type1_tbl(i), --decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', l_child_asset_type1_tbl(i),asset_type),
1159 split_merged_code = NULL,
1160 merged_code = NULL,
1161 parent_mass_addition_id = NULL,
1162 merge_parent_mass_additions_id = NULL,
1163 add_to_asset_id = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', l_child_asset_id1_tbl(i),NULL)
1164 where parent_invoice_dist_id = l_child_inv_dist_id1_tbl(i)
1165 and line_status = 'NEW'
1166 and ledger_category_code = 'P'
1167 and book_type_code = p_book_type_code
1168 returning mass_addition_id
1169 , invoice_distribution_id
1170 , add_to_asset_id
1171 , line_status
1172 , asset_type bulk collect
1173 into l_child_mass_add_id1_tbl
1174 , l_child_inv_dist_id1A_tbl
1175 , l_child_asset_id1A_tbl
1176 , l_child_line_status1A_tbl
1177 , l_child_asset_type1A_tbl;
1178
1179 for i in 1..l_child_mass_add_id1_tbl.count loop
1180 l_child_line_status1_tbl(l_child_line_status1_tbl.count + 1) := 'VALID';
1181 if (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N') = 'Y') then /* ER 14739752 */
1182 l_child_posting_status1_tbl(l_child_posting_status1_tbl.count + 1) := 'NEW';
1183 l_child_merged_code1_tbl(l_child_merged_code1_tbl.count + 1) := null;
1184 else
1185 l_child_posting_status1_tbl(l_child_posting_status1_tbl.count + 1) := 'MERGED';
1186 l_child_merged_code1_tbl(l_child_merged_code1_tbl.count + 1) := 'MC';
1187 end if;
1188 l_child_queue_name1_tbl(l_child_queue_name1_tbl.count + 1) := 'NEW';
1189 end loop;
1190
1191 if (g_log_level_rec.statement_level) then
1192 fa_debug_pkg.add(l_calling_fn
1193 ,'l_child_inv_dist_id1A_tbl.count'
1194 ,l_child_inv_dist_id1A_tbl.count
1195 ,p_log_level_rec => g_log_level_rec);
1196 fa_debug_pkg.add(l_calling_fn
1197 ,'l_child_mass_add_id1_tbl.count'
1198 ,l_child_mass_add_id1_tbl.count
1199 ,p_log_level_rec => g_log_level_rec);
1200 fa_debug_pkg.add(l_calling_fn
1201 ,'l_child_asset_id1A_tbl.count'
1202 ,l_child_asset_id1A_tbl.count
1203 ,p_log_level_rec => g_log_level_rec);
1204 fa_debug_pkg.add(l_calling_fn
1205 ,'l_child_line_status1A_tbl.count'
1206 ,l_child_line_status1A_tbl.count
1207 ,p_log_level_rec => g_log_level_rec);
1208 fa_debug_pkg.add(l_calling_fn
1209 ,'l_child_asset_type1A_tbl.count'
1210 ,l_child_asset_type1A_tbl.count
1211 ,p_log_level_rec => g_log_level_rec);
1212 fa_debug_pkg.add(l_calling_fn
1213 ,'l_child_posting_status1_tbl.count'
1214 ,l_child_posting_status1_tbl.count
1215 ,p_log_level_rec => g_log_level_rec);
1216 fa_debug_pkg.add(l_calling_fn
1217 ,'l_child_queue_name1_tbl.count'
1218 ,l_child_queue_name1_tbl.count
1219 ,p_log_level_rec => g_log_level_rec);
1220 fa_debug_pkg.add(l_calling_fn
1221 ,'l_child_merged_code1_tbl.count'
1222 ,l_child_merged_code1_tbl.count
1223 ,p_log_level_rec => g_log_level_rec);
1224 end if;
1225
1226 -- flag these updated lines as parent MP where applicable
1227 forall i in 1..l_child_mass_add_id1_tbl.count
1228 update /*+ index(gt FA_MASS_ADDITIONS_GT_N3) */ fa_mass_additions_gt gt
1229 set split_merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL), /* ER 14739752 */
1230 merged_code = decode (nvl(fa_cache_pkg.fazcbc_record.IMPORT_ALLOC_INV_LINES_AS_NEW,'N'), 'N', 'MP',NULL)
1231 where invoice_distribution_id = l_child_inv_dist_id1A_tbl(i)
1232 and invoice_payment_id is null -- exclude discounts
1233 and book_type_code = p_book_type_code
1234 and ledger_category_code = 'P'
1235 and exists
1236 (select 1
1237 from fa_mass_additions_gt gt2
1238 where gt2.parent_invoice_dist_id = gt.invoice_distribution_id
1239 and gt2.rowid <> gt.rowid);
1240
1241 -- process singles
1242 if not Preprocess_Child_Lines
1243 (p_book_type_code => p_book_type_code
1244 ,p_mode => 'AI'
1245 ,p_invoice_dist_id_tbl => l_child_inv_dist_id1A_tbl
1246 ,p_mass_add_id_tbl => l_child_mass_add_id1_tbl
1247 ,p_asset_id_tbl => l_child_asset_id1A_tbl
1248 ,p_line_status_tbl => l_child_line_status1A_tbl
1249 ,p_posting_status_tbl => l_child_posting_status1_tbl
1250 ,p_queue_name_tbl => l_child_queue_name1_tbl
1251 ,p_asset_type_tbl => l_child_asset_type1A_tbl
1252 ,p_merged_code_tbl => l_child_merged_code1_tbl) then
1253 raise error_found;
1254 end if;
1255 end if;
1256
1257 -- process splits
1258 if (l_child_inv_dist_id2_tbl.count > 0) then
1259 if not Preprocess_Split_Lines
1260 (p_book_type_code => p_book_type_code
1261 ,p_mode => 'AI'
1262 ,p_invoice_dist_id_tbl => l_child_inv_dist_id2_tbl
1263 ,p_asset_type_tbl => l_child_asset_type2_tbl) then
1264 raise error_found;
1265 end if;
1266 end if;
1267
1268 end loop; -- bulk
1269
1270 close c_ai_parents;
1271
1272 savepoint FAMADC_preprocess7;
1273
1274 if (g_log_level_rec.statement_level) then
1275 fa_debug_pkg.add(l_calling_fn
1276 ,'Updating depreciate_flag in'
1277 ,'fa_mass_additions_gt'
1278 ,p_log_level_rec => g_log_level_rec);
1279 end if;
1280
1281 -- update the depreciate_flag and inventorial on valid lines
1282 update /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt
1283 set depreciate_flag =
1284 (select decode(gt.asset_type
1285 ,'EXPENSED','NO'
1286 ,nvl(CBD.depreciate_flag, 'YES'))
1287 from fa_category_book_defaults CBD
1288 where CBD.book_type_code(+) = p_book_type_code
1289 and CBD.category_id(+)= gt.asset_category_id
1290 and p_def_dpis_dt between CBD.start_DPIS(+)
1291 and nvl(CBD.end_DPIS(+),p_def_dpis_dt)),
1292 inventorial =
1293 (select nvl(inventorial, 'YES')
1294 from fa_categories_b c
1295 where c.category_id(+) = gt.asset_category_id)
1296 where gt.book_type_code = p_book_type_code
1297 and gt.line_status = 'VALID'
1298 and gt.ledger_category_code = 'P'
1299 and gt.asset_category_id is not null
1300 and gt.add_to_asset_id is null;
1301
1302 l_count := SQL%ROWCOUNT;
1303
1304 if (g_log_level_rec.statement_level) then
1305 fa_debug_pkg.add(l_calling_fn
1306 ,'No of Records Updated'
1307 ,to_char(l_count)
1308 ,p_log_level_rec => g_log_level_rec);
1309 end if;
1310
1311 RETURN TRUE;
1312
1313 EXCEPTION
1314 WHEN ERROR_FOUND then
1315 RETURN FALSE;
1316
1317 WHEN OTHERS THEN
1318 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1319 ,p_log_level_rec => g_log_level_rec);
1320
1321 RETURN FALSE;
1322
1323 end Preprocess_GT_Records;
1324
1325 --------------------------------------------------------------------------------
1326
1327 PROCEDURE create_lines
1328 (p_book_type_code IN VARCHAR2
1329 ,p_api_version IN NUMBER
1330 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1331 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1332 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1333 ,p_calling_fn IN VARCHAR2
1334 ,x_return_status OUT NOCOPY VARCHAR2
1335 ,x_msg_count OUT NOCOPY NUMBER
1336 ,x_msg_data OUT NOCOPY VARCHAR2
1337 ) IS
1338
1339
1340 l_calling_fn varchar2(40) := 'FA_MASS_ADDITIONS_PKG.create_lines';
1341
1342 l_count INTEGER;
1343
1344 l_date_ineffective FA_BOOK_CONTROLS.DATE_INEFFECTIVE%TYPE;
1345 l_book_class FA_BOOK_CONTROLS.BOOK_CLASS%TYPE;
1346 l_sob_id FA_BOOK_CONTROLS.SET_OF_BOOKS_ID%TYPE;
1347 l_coa_id FA_BOOK_CONTROLS.ACCOUNTING_FLEX_STRUCTURE%TYPE;
1348 l_segment_num NUMBER;
1349 l_app_column_name VARCHAR2(100);
1350 l_def_dpis_option VARCHAR2(1);
1351 l_def_dpis_enabled INTEGER;
1352 l_def_dpis_dt DATE;
1353 l_ledger_id NUMBER;
1354 l_period_close_date DATE;
1355 l_calendar_period_close_date DATE;
1356 l_request_id NUMBER;
1357 l_login_id NUMBER;
1358 l_user_id NUMBER;
1359 l_result BOOLEAN;
1360
1361 fa_ineffective_book EXCEPTION;
1362 fa_not_corp_book EXCEPTION;
1363 create_err EXCEPTION;
1364
1365 BEGIN
1366
1367 savepoint FAMADC_create1;
1368
1369 if (not g_log_level_rec.initialized) then
1370 if (NOT fa_util_pub.get_log_level_rec (
1371 x_log_level_rec => g_log_level_rec
1372 )) then
1373 raise create_err;
1374 end if;
1375 end if;
1376
1377 l_request_id := FND_GLOBAL.conc_request_id;
1378 l_user_id := FND_GLOBAL.user_id;
1379 l_login_id := FND_GLOBAL.login_id;
1380
1381 if (g_log_level_rec.statement_level) then
1382 fa_debug_pkg.add(l_calling_fn, 'Stamp FA_SYSTEM_CONTROLS WITH ', l_request_id
1383 ,p_log_level_rec => g_log_level_rec);
1384 end if;
1385
1386 update fa_system_controls
1387 set last_mass_additions = l_request_id;
1388
1389 if (g_log_level_rec.statement_level) then
1390 fa_debug_pkg.add(l_calling_fn
1391 , 'Validating book '
1392 , p_book_type_code
1393 ,p_log_level_rec => g_log_level_rec);
1394 fa_debug_pkg.add(l_calling_fn
1395 ,'calling'
1396 ,'fa_cache_pkg.fazcbc'
1397 ,p_log_level_rec => g_log_level_rec);
1398 end if;
1399
1400 if NOT fa_cache_pkg.fazcbc(X_book => p_book_type_code,
1401 p_log_level_rec => g_log_level_rec) then
1402 raise create_err;
1403 end if;
1404
1405 if (g_log_level_rec.statement_level) then
1406 fa_debug_pkg.add(l_calling_fn
1407 ,'calling'
1408 ,'fa_cache_pkg.fazcdp'
1409 ,p_log_level_rec => g_log_level_rec);
1410 end if;
1411
1412 if not fa_cache_pkg.fazcdp (x_book_type_code => p_book_type_code ) THEN
1413 raise create_err;
1414 end if;
1415
1416 l_ledger_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
1417 l_date_ineffective := fa_cache_pkg.fazcbc_record.date_ineffective;
1418 l_book_class := fa_cache_pkg.fazcbc_record.book_class;
1419 l_coa_id := fa_cache_pkg.fazcbc_record.accounting_flex_structure;
1420
1421 l_calendar_period_close_date :=
1422 fa_cache_pkg.fazcdp_record.calendar_period_close_date ;
1423
1424 IF l_date_ineffective IS NOT NULL THEN
1425 if (g_log_level_rec.statement_level) then
1426 fa_debug_pkg.add(l_calling_fn
1427 ,'Ineffective book '
1428 ,p_book_type_code
1429 ,p_log_level_rec => g_log_level_rec);
1430 end if;
1431 raise fa_ineffective_book;
1432 END IF;
1433
1434 IF l_book_class <> 'CORPORATE' THEN
1435 if (g_log_level_rec.statement_level) then
1436 fa_debug_pkg.add(l_calling_fn
1437 ,'Incorrect Book class '
1438 ,p_book_type_code
1439 ,p_log_level_rec => g_log_level_rec);
1440 end if;
1441 raise fa_not_corp_book;
1442 END IF;
1443
1444 -- Get Qualifier segment number
1445 if (g_log_level_rec.statement_level) then
1446 fa_debug_pkg.add(l_calling_fn
1447 ,'Get Qualifier Segment Column Name for Chart of Accounts ID '
1448 ,l_coa_id
1449 ,p_log_level_rec => g_log_level_rec);
1450 end if;
1451
1452 -- Bug 14041424 : Get the qualifier segment column directly
1453 if NOT (FND_FLEX_APIS.get_segment_column(101
1454 ,'GL#'
1455 ,l_coa_id
1456 ,'GL_ACCOUNT'
1457 ,l_app_column_name)) then
1458 raise create_err;
1459 end if;
1460
1461 if (g_log_level_rec.statement_level) then
1462 fa_debug_pkg.add(l_calling_fn
1463 ,'application column name '
1464 ,l_app_column_name
1465 ,p_log_level_rec => g_log_level_rec);
1466 fa_debug_pkg.add(l_calling_fn
1467 ,'Checking Profile '
1468 ,'FA_DEFAULT_DPIS_TO_INV_DATE'
1469 ,p_log_level_rec => g_log_level_rec);
1470 end if;
1471
1472 l_def_dpis_option := fnd_profile.value('FA_DEFAULT_DPIS_TO_INV_DATE');
1473
1474 IF (l_def_dpis_option = 'Y') THEN
1475 l_def_dpis_enabled := 1;
1476 ELSE
1477 l_def_dpis_enabled := 0;
1478 END IF;
1479
1480 IF (l_def_dpis_enabled = 0) THEN /* For Future Dated Txns */
1481 if (g_log_level_rec.statement_level) then
1482 fa_debug_pkg.add(l_calling_fn
1483 ,'Get Default DPIS for '
1484 ,p_book_type_code
1485 ,p_log_level_rec => g_log_level_rec);
1486 end if;
1487
1488 l_def_dpis_dt :=
1489 greatest(nvl(fa_cache_pkg.fazcdp_record.calendar_period_open_date,
1490 sysdate),
1491 least(sysdate,
1492 nvl(fa_cache_pkg.fazcdp_record.calendar_period_close_date, sysdate)));
1493
1494 END IF;
1495
1496
1497 if (g_log_level_rec.statement_level) then
1498 fa_debug_pkg.add(l_calling_fn,'PreProcess Records in GT','that have book type code NULL'
1499 ,p_log_level_rec => g_log_level_rec);
1500 end if;
1501
1502 IF NOT Preprocess_GT_Records
1503 (p_book_type_code => p_book_type_code
1504 ,p_coa_id => l_coa_id
1505 ,p_segment_num => l_segment_num
1506 ,p_column_name => l_app_column_name
1507 ,p_ledger_id => l_ledger_id
1508 ,p_def_dpis_dt => l_def_dpis_dt) THEN
1509 if (g_log_level_rec.statement_level) then
1510 fa_debug_pkg.add(l_calling_fn,'Error during PreProcessing in FA API ',p_book_type_code
1511 ,p_log_level_rec => g_log_level_rec);
1512 end if;
1513 raise create_err;
1514 END IF;
1515
1516
1517 -- Call the cache pkg to fetch the calendar_period_close_date
1518 if not fa_cache_pkg.fazcdp (x_book_type_code => p_book_type_code ) THEN
1519 if (g_log_level_rec.statement_level) then
1520 fa_debug_pkg.add(l_calling_fn
1521 ,'Unable to find valid depreciation information '
1522 ,p_book_type_code
1523 ,p_log_level_rec => g_log_level_rec);
1524 end if;
1525 raise fa_ineffective_book;
1526 end if;
1527
1528 if (g_log_level_rec.statement_level) then
1529 fa_debug_pkg.add(l_calling_fn
1530 ,'Insert FA_MASS_ADDITIONS lines for primary ledger '
1531 ,p_book_type_code
1532 ,p_log_level_rec => g_log_level_rec);
1533 end if;
1534
1535 savepoint FAMADC_create2;
1536
1537 insert into fa_mass_additions(
1538 mass_addition_id ,
1539 asset_number ,
1540 tag_number ,
1541 description ,
1542 asset_category_id ,
1543 manufacturer_name ,
1544 serial_number ,
1545 model_number ,
1546 book_type_code ,
1547 date_placed_in_service ,
1548 fixed_assets_cost ,
1549 payables_units ,
1550 fixed_assets_units ,
1551 payables_code_combination_id ,
1552 expense_code_combination_id ,
1553 location_id ,
1554 assigned_to ,
1555 feeder_system_name ,
1556 create_batch_date ,
1557 create_batch_id ,
1558 last_update_date ,
1559 last_updated_by ,
1560 reviewer_comments ,
1561 invoice_number ,
1562 vendor_number ,
1563 po_vendor_id ,
1564 po_number ,
1565 posting_status ,
1566 queue_name ,
1567 invoice_date ,
1568 invoice_created_by ,
1569 invoice_updated_by ,
1570 payables_cost ,
1571 invoice_id ,
1572 payables_batch_name ,
1573 depreciate_flag ,
1574 parent_mass_addition_id ,
1575 parent_asset_id ,
1576 split_merged_code ,
1577 ap_distribution_line_number ,
1578 post_batch_id ,
1579 add_to_asset_id ,
1580 amortize_flag ,
1581 new_master_flag ,
1582 asset_key_ccid ,
1583 asset_type ,
1584 deprn_reserve ,
1585 ytd_deprn ,
1586 beginning_nbv ,
1587 created_by ,
1588 creation_date ,
1589 last_update_login ,
1590 salvage_value ,
1591 accounting_date ,
1592 attribute_category_code ,
1593 fully_rsvd_revals_counter ,
1594 merge_invoice_number ,
1595 merge_vendor_number ,
1596 production_capacity ,
1597 reval_amortization_basis ,
1598 reval_reserve ,
1599 unit_of_measure ,
1600 unrevalued_cost ,
1601 ytd_reval_deprn_expense ,
1602 merged_code ,
1603 split_code ,
1604 merge_parent_mass_additions_id ,
1605 split_parent_mass_additions_id ,
1606 project_asset_line_id ,
1607 project_id ,
1608 task_id ,
1609 sum_units ,
1610 dist_name ,
1611 context ,
1612 inventorial ,
1613 short_fiscal_year_flag ,
1614 conversion_date ,
1615 original_deprn_start_date ,
1616 group_asset_id ,
1617 cua_parent_hierarchy_id ,
1618 units_to_adjust ,
1619 bonus_ytd_deprn ,
1620 bonus_deprn_reserve ,
1621 amortize_nbv_flag ,
1622 amortization_start_date ,
1623 transaction_type_code ,
1624 transaction_date ,
1625 warranty_id ,
1626 lease_id ,
1627 lessor_id ,
1628 property_type_code ,
1629 property_1245_1250_code ,
1630 in_use_flag ,
1631 owned_leased ,
1632 new_used ,
1633 asset_id ,
1634 material_indicator_flag ,
1635 invoice_distribution_id ,
1636 invoice_line_number ,
1637 invoice_payment_id ,
1638 warranty_number)
1639 select /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */
1640 gt.mass_addition_id , --fa_mass_additions_s.nextval,
1641 gt.asset_number ,
1642 gt.tag_number ,
1643 gt.description ,
1644 gt.asset_category_id ,
1645 gt.manufacturer_name ,
1646 gt.serial_number ,
1647 gt.model_number ,
1648 p_book_type_code ,
1649 decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt ) ,
1650 gt.fixed_assets_cost ,
1651 gt.payables_units ,
1652 gt.fixed_assets_units ,
1653 gt.payables_code_combination_id ,
1654 gt.expense_code_combination_id ,
1655 gt.location_id ,
1656 gt.assigned_to ,
1657 gt.feeder_system_name ,
1658 gt.create_batch_date ,
1659 gt.create_batch_id ,
1660 gt.last_update_date ,
1661 gt.last_updated_by ,
1662 gt.reviewer_comments ,
1663 gt.invoice_number ,
1664 gt.vendor_number ,
1665 gt.po_vendor_id ,
1666 gt.po_number ,
1667 gt.posting_status ,
1668 gt.queue_name ,
1669 gt.invoice_date ,
1670 gt.invoice_created_by ,
1671 gt.invoice_updated_by ,
1672 gt.payables_cost ,
1673 gt.invoice_id ,
1674 gt.payables_batch_name ,
1675 gt.depreciate_flag ,
1676 gt.parent_mass_addition_id ,
1677 gt.parent_asset_id ,
1678 gt.split_merged_code ,
1679 gt.ap_distribution_line_number ,
1680 gt.post_batch_id ,
1681 gt.add_to_asset_id ,
1682 gt.amortize_flag ,
1683 gt.new_master_flag ,
1684 gt.asset_key_ccid ,
1685 gt.asset_type , -- reinstated
1686 gt.deprn_reserve ,
1687 gt.ytd_deprn ,
1688 gt.beginning_nbv ,
1689 gt.created_by ,
1690 gt.creation_date ,
1691 gt.last_update_login ,
1692 gt.salvage_value ,
1693 gt.accounting_date ,
1694 gt.attribute_category_code ,
1695 gt.fully_rsvd_revals_counter ,
1696 gt.merge_invoice_number ,
1697 gt.merge_vendor_number ,
1698 gt.production_capacity ,
1699 gt.reval_amortization_basis ,
1700 gt.reval_reserve ,
1701 gt.unit_of_measure ,
1702 gt.unrevalued_cost ,
1703 gt.ytd_reval_deprn_expense ,
1704 gt.merged_code ,
1705 gt.split_code ,
1706 gt.merge_parent_mass_additions_id ,
1707 gt.split_parent_mass_additions_id ,
1708 gt.project_asset_line_id ,
1709 gt.project_id ,
1710 gt.task_id ,
1711 /* gt.sum_units */
1712 null,
1713 gt.dist_name ,
1714 gt.context ,
1715 gt.inventorial ,
1716 gt.short_fiscal_year_flag ,
1717 gt.conversion_date ,
1718 gt.original_deprn_start_date ,
1719 gt.group_asset_id ,
1720 gt.cua_parent_hierarchy_id ,
1721 gt.units_to_adjust ,
1722 gt.bonus_ytd_deprn ,
1723 gt.bonus_deprn_reserve ,
1724 gt.amortize_nbv_flag ,
1725 gt.amortization_start_date ,
1726 /* transaction_type_code - only future add in future period */
1727 decode(sign(decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt)
1728 - l_calendar_period_close_date), 1, 'FUTURE ADD', NULL ),
1729 /* transaction date */
1730 decode(sign(decode(l_def_dpis_enabled, 1, gt.invoice_date, l_def_dpis_dt)
1731 - l_calendar_period_close_date), 1, decode(l_def_dpis_enabled,
1732 1, invoice_date, l_def_dpis_dt), null ),
1733 gt.warranty_id ,
1734 gt.lease_id ,
1735 gt.lessor_id ,
1736 gt.property_type_code ,
1737 gt.property_1245_1250_code ,
1738 gt.in_use_flag ,
1739 gt.owned_leased ,
1740 gt.new_used ,
1741 gt.asset_id ,
1742 gt.material_indicator_flag ,
1743 gt.invoice_distribution_id ,
1744 gt.invoice_line_number ,
1745 gt.invoice_payment_id ,
1746 gt.warranty_number
1747 from fa_mass_additions_gt gt
1748 where gt.book_type_code = p_book_type_code
1749 and gt.ledger_category_code = 'P'
1750 and gt.line_status = 'VALID';
1751
1752 l_count := SQL%ROWCOUNT;
1753 if (g_log_level_rec.statement_level) then
1754 fa_debug_pkg.add(l_calling_fn
1755 ,'No of Records Inserted '
1756 ,to_char(l_count)
1757 ,p_log_level_rec => g_log_level_rec);
1758 fa_debug_pkg.add(l_calling_fn
1759 ,'Inserting into FA_MC_MASS_RATES for reporting ledger(s) '
1760 ,p_book_type_code
1761 ,p_log_level_rec => g_log_level_rec);
1762 end if;
1763
1764 -- insert into mc_rates
1765
1766 savepoint FAMADC_create6;
1767
1768 Insert into fa_mc_mass_rates
1769 ( set_of_books_id,
1770 mass_addition_id,
1771 fixed_assets_cost,
1772 exchange_rate)
1773 select /*+ leading(gt) */
1774 gt.ledger_id,
1775 mad.mass_addition_id,
1776 gt.fixed_assets_cost,
1777 0
1778 from fa_mass_additions mad,
1779 fa_mass_additions_gt gt,
1780 fa_mc_book_controls mcbc
1781 where mad.book_type_code = p_book_type_code
1782 and mad.invoice_distribution_id = gt.invoice_distribution_id
1783 and nvl(mad.invoice_payment_id, -99) = nvl(gt.invoice_payment_id, -99)
1784 and gt.ledger_category_code = 'ALC'
1785 and mcbc.book_type_code = mad.book_type_code
1786 and mcbc.set_of_books_id = gt.ledger_id
1787 and mcbc.enabled_flag = 'Y'
1788 and mcbc.mrc_converted_flag = 'Y';
1789
1790 l_count := SQL%ROWCOUNT;
1791 if (g_log_level_rec.statement_level) then
1792 fa_debug_pkg.add(l_calling_fn
1793 ,'No of Records Inserted '
1794 ,to_char(l_count)
1795 ,p_log_level_rec => g_log_level_rec);
1796 end if;
1797
1798 -- update for rejected is already handled in the preprocessing logic
1799
1800 if (g_log_level_rec.statement_level) then
1801 fa_debug_pkg.add(l_calling_fn
1802 ,'Updating successful/processed rows in '
1803 ,'fa_mass_additions_gt'
1804 ,p_log_level_rec => g_log_level_rec);
1805 end if;
1806
1807 savepoint FAMADC_create7;
1808
1809 update /*+ index(gt FA_MASS_ADDITIONS_GT_N1) */ fa_mass_additions_gt gt
1810 set gt.line_status = 'PROCESSED'
1811 where book_type_code = p_book_type_code
1812 and line_status = 'VALID'
1813 and ledger_category_code = 'P'
1814 and exists
1815 ( select 1
1816 from fa_mass_additions mad
1817 where mad.mass_addition_id = gt.mass_addition_id);
1818
1819
1820 l_count := SQL%ROWCOUNT;
1821
1822 if (g_log_level_rec.statement_level) then
1823 fa_debug_pkg.add(l_calling_fn
1824 ,'No of Records Processed'
1825 ,to_char(l_count)
1826 ,p_log_level_rec => g_log_level_rec);
1827 end if;
1828
1829 savepoint FAMADC_create8;
1830
1831 x_return_status := FND_API.G_RET_STS_SUCCESS;
1832
1833 EXCEPTION
1834
1835 when create_err then
1836 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1837 ,p_log_level_rec => g_log_level_rec);
1838 x_return_status := FND_API.G_RET_STS_ERROR;
1839
1840 when fa_ineffective_book then
1841 if (g_log_level_rec.statement_level) then
1842 fa_debug_pkg.add(l_calling_fn
1843 ,'This book does not exist or has a date ineffective on or before today'
1844 ,p_book_type_code
1845 ,p_log_level_rec => g_log_level_rec);
1846 end if;
1847 x_return_status := FND_API.G_RET_STS_ERROR;
1848
1849 when fa_not_corp_book then
1850 if (g_log_level_rec.statement_level) then
1851 fa_debug_pkg.add(l_calling_fn
1852 ,'Mass Additions Create cannot be run for non-corporate book'
1853 ,p_book_type_code
1854 ,p_log_level_rec => g_log_level_rec);
1855 end if;
1856 x_return_status := FND_API.G_RET_STS_ERROR;
1857
1858 when others then
1859 -- BMR: do not rollback entire processing set here
1860 -- when debugging
1861 rollback;
1862
1863 fa_srvr_msg.add_sql_error(
1864 calling_fn => l_calling_fn
1865 ,p_log_level_rec => g_log_level_rec);
1866
1867 x_return_status := FND_API.G_RET_STS_ERROR;
1868 END;
1869
1870
1871 END FA_MASSADD_CREATE_PKG;