[Home] [Help]
PACKAGE BODY: APPS.FARX_AD
Source
1 PACKAGE BODY FARX_AD as
2 /* $Header: farxadb.pls 120.21.12010000.3 2008/11/11 07:45:59 souroy ship $ */
3
4 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5
6 procedure ADD_BY_PERIOD (
7 book in varchar2,
8 begin_period in varchar2,
9 end_period in varchar2,
10 from_maj_cat in varchar2,
11 to_maj_cat in varchar2,
12 from_min_cat in varchar2,
13 to_min_cat in varchar2,
14 from_cc in varchar2,
15 to_cc in varchar2,
16 cat_seg_num in varchar2,
17 from_cat_seg_val in varchar2,
18 to_cat_seg_val in varchar2,
19 from_asset_num in varchar2,
20 to_asset_num in varchar2,
21 request_id in number,
22 user_id in number,
23 retcode out nocopy number,
24 errbuf out nocopy varchar2) is
25
26 mesg varchar2(200);
27 ctr number;
28
29 h_login_id number;
30 h_request_id number;
31
32 h_book varchar2(15);
33 h_period1_pc varchar2(15);
34 h_period2_pc varchar2(15);
35
36 h_bonus_rate number;
37 h_reserve_acct varchar2(25);
38 h_adjusted_Rate number;
39 h_prod_capacity number;
40 h_life_months number;
41 h_life_year_month varchar2(10);
42 h_life_year_month_num number;
43 h_method varchar2(15);
44 h_dpis date;
45 h_invoice_flag varchar2(1);
46 h_cost_to_clear number;
47 h_invoice_cost number;
48 h_invoice_orig_cost number;
49 h_invoice_descr varchar2(80);
50 h_line_number number;
51 h_invoice_number varchar2(50);
52 h_tag_number varchar2(15);
53 h_serial_number varchar2(35);
54 h_inventorial varchar2(3);
55 h_vendor_number varchar2(30);
56 h_description varchar2(80);
57 h_asset_number varchar2(15);
58 h_asset_type varchar2(15);
59 h_cost_acct varchar2(25);
60 h_asset_type_mean varchar2(80);
61 h_ccid number;
62 h_source varchar2(20);
63 h_set_of_books_id number;
64 h_currency_code varchar2(15);
65 h_organization_name varchar2(80);
66
67 h_period_name varchar2(25);
68 h_period_name_to varchar2(25);
69 h_account_desc varchar2(240);
70 h_cost_center_desc varchar2(240);
71 h_ytd_deprn number;
72 h_deprn_reserve number;
73 h_tran_header_id number;
74
75 h_maj_cat varchar2(240);
76 h_maj_cat_desc varchar2(240);
77 h_min_cat varchar2(240);
78 h_min_cat_desc varchar2(240);
79 h_specified_cat varchar2(240);
80 h_specified_cat_desc varchar2(240);
81
82 h_category_id number;
83 h_location_id number;
84 h_asset_key_ccid number;
85 h_cat_seg_num varchar2(15);
86
87 h_concat_acct varchar2(200);
88 h_concat_cat varchar2(200);
89 h_concat_loc varchar2(200);
90 h_concat_key varchar2(200);
91 h_acct_segs fa_rx_shared_pkg.Seg_Array;
92 h_cat_segs fa_rx_shared_pkg.Seg_Array;
93 h_loc_segs fa_rx_shared_pkg.Seg_Array;
94 h_key_segs fa_rx_shared_pkg.Seg_Array;
95
96 h_acct_seg number;
97 h_cost_seg number;
98 h_bal_seg number;
99
100 h_dist_source_book varchar2(15);
101
102 h_acct_flex_struct number;
103 h_cat_flex_struct number;
104 h_loc_flex_struct number;
105 h_assetkey_flex_structure number;
106 h_chart_of_accounts_id number;
107
108 h_count number;
109
110 h_mesg_name varchar2(50);
111 h_mesg_str varchar2(2000);
112 h_flex_error varchar2(5);
113 h_ccid_error number;
114
115 maj_select_statement varchar2(50);
116 min_select_statement varchar2(50);
117 spec_select_statement varchar2(50);
118
119 l_param_where varchar2(1000);
120 where_clause1 varchar2(4000);
121 where_clause2 varchar2(4000);
122 where_clause3 varchar2(4000);
123 where_clause4 varchar2(4000);
124 select_statement varchar2(25000);
125
126 type var_cur is ref cursor;
127 additions var_cur;
128
129 h_sort varchar2(3);
130 h_group_asset_number varchar2(15);
131
132 begin
133 IF (g_print_debug) THEN
134 fa_rx_util_pkg.debug('farx_ad.add_by_period()+');
135 END IF;
136
137 h_book := book;
138 h_period_name := begin_period;
139 h_period_name_to := end_period;
140 h_cat_seg_num := cat_seg_num;
141 ctr := 0;
142 h_request_id := request_id;
143
144 select fcr.last_update_login into h_login_id
145 from fnd_concurrent_requests fcr
146 where fcr.request_id = h_request_id;
147
148 IF (g_print_debug) THEN
149 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || '********login_id:' || h_login_id);
150 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || '********login_id:' || h_login_id);
151 END IF;
152
153 h_mesg_name := 'FA_AMT_SEL_PERIODS';
154
155 select period_counter
156 into h_period1_pc
157 from fa_deprn_periods
158 where book_type_code = h_book and period_name = begin_period;
159
160 select count(*) into h_count
161 from fa_deprn_periods where period_name = end_period
162 and book_type_code = h_book;
163
164 if (h_count > 0) then
165 select period_counter
166 into h_period2_pc
167 from fa_deprn_periods
168 where book_type_code = h_book and period_name = end_period;
169 else
170 h_period2_pc := null;
171 end if;
172
173 IF (g_print_debug) THEN
174 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'begin_period,h_period1_pc:' || begin_period || ',' || h_period1_pc);
175 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'end_period,h_period2_pc:' || end_period || ',' || h_period2_pc);
176 END IF;
177
178 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
179
180 select nvl(distribution_source_book, book_type_code), accounting_flex_structure
181 into h_dist_source_book, h_acct_flex_struct
182 from fa_book_controls
183 where book_type_code = h_book;
184
185 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
186
187 select location_flex_structure, category_flex_structure,asset_key_flex_structure
188 into h_loc_flex_struct, h_cat_flex_struct, h_assetkey_flex_structure
189 from fa_system_controls;
190
191 h_mesg_name := 'FA_RX_SEGNUMS';
192
193 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
194 BOOK => h_book,
195 BALANCING_SEGNUM => h_bal_seg,
196 ACCOUNT_SEGNUM => h_acct_seg,
197 CC_SEGNUM => h_cost_seg,
198 CALLING_FN => 'ADD_BY_PERIOD');
199
200 select sob.chart_of_accounts_id,
201 sob.set_of_books_id,
202 substr(sob.currency_code,1,15),
203 substr(sob.name,1,80)
204 into h_chart_of_accounts_id,
205 h_set_of_books_id,
206 h_currency_code,
207 h_organization_name
208 from fa_book_controls bc, gl_sets_of_books sob, fnd_currencies cur
209 WHERE bc.book_type_code = h_book
210 AND sob.set_of_books_id = bc.set_of_books_id
211 AND sob.currency_code = cur.currency_code; -- Added set_of_books_id and currency_code to display those on report
212
213
214 IF (g_print_debug) THEN
215 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'chart of account ID:' || h_chart_of_accounts_id);
216 END IF;
217
218 --
219 -- Get Columns for Major_category, Minor_category and Specified_category
220 --
221 maj_select_statement := fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT','SELECT', 'BASED_CATEGORY');
222
223 begin
224 min_select_statement := fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT','SELECT', 'MINOR_CATEGORY');
225 exception
226 when others then
227 min_select_statement := 'null';
228 end;
229
230 begin
231 spec_select_statement := fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT','SELECT', cat_seg_num);
232 exception
233 when others then
234 spec_select_statement := 'null';
235 end;
236
237 --
238 -- Figure out the where clause for the parameters
239 --
240
241 -- parameter where clause --
242
243
244 l_param_where := null;
245
246 /* BUG# 2939771
247 -- Major Category --
248 IF(from_maj_cat = to_maj_cat) THEN
249 l_param_where := l_param_where || ' AND ' ||
250 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
251 'WHERE', 'BASED_CATEGORY','=', from_maj_cat);
252 elsif (from_maj_cat is not NULL) and (to_maj_cat is not NULL) THEN
253 l_param_where := l_param_where || ' AND ' ||
254 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
255 'WHERE', 'BASED_CATEGORY','BETWEEN', from_maj_cat, to_maj_cat);
256 elsif (from_maj_cat is not NULL) THEN
257 l_param_where := l_param_where || ' AND ' ||
258 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
259 'WHERE', 'BASED_CATEGORY','>=', from_maj_cat);
260 elsif (to_maj_cat is not NULL) THEN
261 l_param_where := l_param_where || ' AND ' ||
262 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
263 'WHERE', 'BASED_CATEGORY','<=', to_maj_cat);
264 END IF;
265 */
266
267 -- Major Category --
268 l_param_where := l_param_where || ' AND (' ||
269 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
270 'SELECT', 'BASED_CATEGORY') ||' >= :from_maj_cat or :from_maj_cat is NULL)';
271
272 l_param_where := l_param_where || ' AND (' ||
273 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
274 'SELECT', 'BASED_CATEGORY') ||' <= :to_maj_cat or :to_maj_cat is NULL)';
275
276
277 /* BUG# 2939771
278 -- Minor Category --
279 IF (from_min_cat = to_min_cat) THEN
280 l_param_where := l_param_where || ' AND ' ||
281 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
282 'WHERE', 'MINOR_CATEGORY','=', from_min_cat);
283 elsif (from_min_cat is not NULL) and (to_min_cat is not NULL) THEN
284 l_param_where := l_param_where || ' AND ' ||
285 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
286 'WHERE', 'MINOR_CATEGORY','BETWEEN', from_min_cat, to_min_cat);
287 elsif (from_min_cat is not NULL) THEN
288 l_param_where := l_param_where || ' AND ' ||
289 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
290 'WHERE', 'MINOR_CATEGORY','>=', from_min_cat);
291 elsif (to_min_cat is not NULL) THEN
292 l_param_where := l_param_where || ' AND ' ||
293 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
294 'WHERE', 'MINOR_CATEGORY','<=', to_min_cat);
295 END IF;
296 */
297
298 -- Minor Category --
299 /* Fix for Bug# 2973255: Added expection handling to proceed
300 in case that flex_sql fails when from_min_cat or to_min_cat are null
301 */
302 begin
303 l_param_where := l_param_where || ' AND (' ||
304 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
305 'SELECT', 'MINOR_CATEGORY') ||' >= :from_min_cat or :from_min_cat is NULL)';
306 exception
307 when others then
308 l_param_where := l_param_where || ' AND (:from_min_cat is NULL and :from_min_cat is NULL)';
309 end;
310
311 begin
312 l_param_where := l_param_where || ' AND (' ||
313 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
314 'SELECT', 'MINOR_CATEGORY') ||' <= :to_min_cat or :to_min_cat is NULL)';
315 exception
316 when others then
317 l_param_where := l_param_where || ' AND (:to_min_cat is NULL and :to_min_cat is NULL)';
318 end;
319
320
321 /* BUG# 2939771
322 -- Category Segment Number --
323 IF (cat_seg_num IS NOT NULL) THEN
324 h_cat_seg_num := cat_seg_num;
325 IF (from_cat_seg_val = to_cat_seg_val) THEN
326 l_param_where := l_param_where || ' AND ' ||
327 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
328 'WHERE',cat_seg_num ,'=', from_cat_seg_val);
329 elsif (from_cat_seg_val is not NULL) and (to_cat_seg_val is not NULL) THEN
330 l_param_where := l_param_where || ' AND ' ||
331 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
332 'WHERE',cat_seg_num ,'BETWEEN', from_cat_seg_val, to_cat_seg_val);
333 elsif (from_cat_seg_val is not NULL) THEN
334 l_param_where := l_param_where || ' AND ' ||
335 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
336 'WHERE',cat_seg_num ,'>=', from_cat_seg_val);
337 elsif (to_cat_seg_val is not NULL) THEN
338 l_param_where := l_param_where || ' AND ' ||
339 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
340 'WHERE',cat_seg_num ,'<=', to_cat_seg_val);
341 END IF;
342 END IF;
343 */
344
345 -- Category Segment Number --
346 IF (cat_seg_num IS NOT NULL) THEN
347 h_cat_seg_num := cat_seg_num;
348 l_param_where := l_param_where || ' AND (' ||
349 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
350 'SELECT', cat_seg_num) ||' >= :from_cat_seg_val or :from_cat_seg_val is NULL)';
351
352 l_param_where := l_param_where || ' AND (' ||
353 fa_rx_flex_pkg.flex_sql(140,'CAT#', h_assetkey_flex_structure,'CAT',
354 'SELECT', cat_seg_num) ||' <= :to_cat_seg_val or :to_cat_seg_val is NULL)';
355 ELSE
356 l_param_where := l_param_where || ' AND ( nvl(:from_cat_seg_val,-999) = -999 or :from_cat_seg_val is null)';
357 l_param_where := l_param_where || ' AND ( nvl(:to_cat_seg_val,-999) = -999 or :to_cat_seg_val is null)';
358 END IF;
359
360
361 /*
362 -- Category Conditions --
363 IF (l_param_where is not NULL) THEN
364 l_param_where := l_param_where || ' AND CB.CATEGORY_ID = CAT.CATEGORY_ID';
365 END IF;
366 */
367
368 /* BUG# 2939771
369 -- COST CENTER --
370 If (from_cc = to_cc) THEN
371 l_param_where := l_param_where || ' AND ' ||
372 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_accounts_id,'DHCC',
373 'WHERE', 'FA_COST_CTR','=', from_cc);
374 elsif (from_cc is not NULL) and (to_cc is not NULL) THEN
375 l_param_where := l_param_where || ' AND ' ||
376 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_accounts_id,'DHCC',
377 'WHERE', 'FA_COST_CTR','BETWEEN', from_cc, to_cc);
378 elsif (from_cc is not NULL) THEN
379 l_param_where := l_param_where || ' AND ' ||
380 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_accounts_id,'DHCC',
381 'WHERE', 'FA_COST_CTR','>=', from_cc);
382 elsif (to_cc is not NULL) THEN
383 l_param_where := l_param_where || ' AND ' ||
384 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_accounts_id,'DHCC',
385 'WHERE', 'FA_COST_CTR','<=', to_cc);
386 end if;
387 */
388
389 l_param_where := l_param_where || ' AND (' ||
390 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_accounts_id,'DHCC',
391 'SELECT', 'FA_COST_CTR') ||' >= :from_cc or :from_cc is NULL)';
392
393 l_param_where := l_param_where || ' AND (' ||
394 fa_rx_flex_pkg.flex_sql(101,'GL#', h_chart_of_accounts_id,'DHCC',
395 'SELECT', 'FA_COST_CTR') ||' <= :to_cc or :to_cc is NULL)';
396
397 /* BUG # 2939771
398 -- Asset Number --
399 IF (from_asset_num = to_asset_num) THEN
400 l_param_where := l_param_where || ' AND AD.ASSET_NUMBER = '''
401 || from_asset_num || '''';
402 elsif (from_asset_num is not NULL) and (to_asset_num is not NULL) THEN
403 l_param_where := l_param_where || ' AND AD.ASSET_NUMBER BETWEEN '''
404 || from_asset_num || '''' || ' AND ''' || to_asset_num || '''';
405 elsif (from_asset_num is not NULL) THEN
406 l_param_where := l_param_where || ' AND AD.ASSET_NUMBER >= '''
407 || from_asset_num || '''';
408 elsif (to_asset_num is not NULL) THEN
412 */
409 l_param_where := l_param_where || ' AND AD.ASSET_NUMBER <= '''
410 || to_asset_num || '''';
411 END IF;
413 -- Asset Number --
414 l_param_where := l_param_where || ' AND (AD.ASSET_NUMBER >= :from_asset_num OR :from_asset_num is NULL)';
415 l_param_where := l_param_where || ' AND (AD.ASSET_NUMBER <= :to_asset_num OR :to_asset_num is NULL)';
416
417 IF (g_print_debug) THEN
418 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'l_param_where:' || l_param_where);
419 END IF;
420
421 where_clause1 := 'DS.BOOK_TYPE_CODE (+) = :h_book AND
422 DS.ASSET_ID (+) = DD.ASSET_ID AND
423 DS.DEPRN_SOURCE_CODE (+) = ''DEPRN'' AND
424 DS.PERIOD_COUNTER (+) = DD.PERIOD_COUNTER + 1 AND
425 DH.ASSET_ID = DD.ASSET_ID AND
426 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
427 AND DH.BOOK_TYPE_CODE = :h_dist_source_book AND
428 DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
429 AND AD.ASSET_ID = DD.ASSET_ID
430 AND DP.BOOK_TYPE_CODE = :h_book AND
431 DP.PERIOD_COUNTER = dd.period_counter+1
432 AND DP1.BOOK_TYPE_CODE = :h_book AND
433 DP1.PERIOD_COUNTER >= :h_period1_pc AND
434 DP1.PERIOD_COUNTER <= nvl(:h_period2_pc ,
435 DP1.PERIOD_COUNTER)
436 AND DD.BOOK_TYPE_CODE = :h_book AND
437 DD.DEPRN_SOURCE_CODE = ''B'' AND
438 DD.PERIOD_COUNTER >= :h_period1_pc - 1 and
439 dd.period_counter <= :h_period2_pc - 1
440 AND bk.transaction_header_id_in = th.transaction_header_id
441 AND AH.ASSET_ID = th.ASSET_ID AND
442 AH.DATE_EFFECTIVE <= NVL(DP.PERIOD_CLOSE_DATE, SYSDATE) AND
443 NVL(AH.DATE_INEFFECTIVE, SYSDATE+1) >
444 NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
445 AND CB.CATEGORY_ID = AH.CATEGORY_ID AND
446 CB.BOOK_TYPE_CODE = :h_book
447 AND AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
448 FALU.LOOKUP_TYPE = ''ASSET TYPE''
449 AND TH.ASSET_ID = DD.ASSET_ID AND
450 TH.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND
451 TH.DATE_EFFECTIVE < nvl(DP.PERIOD_CLOSE_DATE,th.date_effective+1) AND
452 TH.BOOK_TYPE_CODE = :h_book AND
453 TH.TRANSACTION_TYPE_CODE in (''CIP ADDITION'',''ADDITION'')
454 AND
455 IT.INVOICE_TRANSACTION_ID = AI_IN.INVOICE_TRANSACTION_ID_IN
456 AND AI_IN.ASSET_ID (+) = TH.ASSET_ID
457 AND AI_IN.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND -- modified
458 AI_IN.DATE_EFFECTIVE < nvl(DP.PERIOD_CLOSE_DATE,sysdate+1) -- modified
459 AND nvl(AI_IN.DATE_INEFFECTIVE,sysdate) not between -- modified
460 dp.period_open_date and nvl(DP.PERIOD_CLOSE_DATE,sysdate -1) -- modified
461 AND AI_IN.DELETED_FLAG (+) = ''NO''
462 AND PO_VEND.VENDOR_ID(+) = AI_IN.PO_VENDOR_ID
463 AND CB.CATEGORY_ID = CAT.CATEGORY_ID
464 AND GAD.ASSET_ID(+) = BK.GROUP_ASSET_ID';
465
466 where_clause2 := '
467 DS.BOOK_TYPE_CODE = :h_book AND
468 DS.ASSET_ID = th.asset_id and
469 DS.DEPRN_SOURCE_CODE = ''DEPRN'' AND
470 DS.PERIOD_COUNTER = DD.PERIOD_COUNTER
471 AND DH.BOOK_TYPE_CODE = :h_dist_source_book AND
472 DH.ASSET_ID = dd.ASSET_ID AND
473 DH.DISTRIBUTION_ID =
474 decode(th.asset_id, null,DD.DISTRIBUTION_ID, DD.DISTRIBUTION_ID) AND
475 DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
476 AND DD.BOOK_TYPE_CODE = :h_book AND
477 DD.ASSET_ID = TH.ASSET_ID AND
478 DD.PERIOD_COUNTER =
479 ( select max(DD1.PERIOD_COUNTER)
480 from FA_DEPRN_DETAIL DD1
481 where dd1.period_counter <= dp1.period_counter
482 and DD1.ASSET_ID = DD.ASSET_ID
483 and DD1.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE)
484 AND DP1.BOOK_TYPE_CODE = :h_book AND
485 DP1.PERIOD_COUNTER >= :h_period1_pc AND
486 DP1.PERIOD_COUNTER <= nvl(:h_period2_pc,
487 DP1.PERIOD_COUNTER)
488 AND TH.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND
489 TH.DATE_EFFECTIVE < nvl(DP.PERIOD_CLOSE_DATE,th.date_effective+1) AND
490 TH.BOOK_TYPE_CODE = :h_book AND
491 th.asset_id = dd.asset_id and
492 TH.TRANSACTION_TYPE_CODE in ( ''ADDITION'' , ''CIP ADJUSTMENT'' )
493 and thadd.book_type_code =th.book_type_code
494 and thadd.asset_id = th.asset_id
495 and thadd.transaction_type_code = ''ADDITION''
496 and thadd.date_effective between dp2.period_open_date and nvl(dp2.period_close_date,sysdate)
497 and dp2.book_type_code = th.book_type_code
498 and dp2.period_counter >= :h_period1_pc
499 and dp2.period_counter <= nvl(:h_period2_pc, dp2.period_counter)
500 AND THDIS.TRANSACTION_TYPE_CODE = ''TRANSFER IN'' AND
501 THDIS.BOOK_TYPE_CODE = :h_book AND
502 THDIS.ASSET_ID = TH.ASSET_ID AND
503 THDIS.DATE_EFFECTIVE < DP.PERIOD_OPEN_DATE
504 AND BK.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
505 AND DP.BOOK_TYPE_CODE =
506 decode(th.asset_id, null,dd.BOOK_TYPE_CODE,dd.BOOK_TYPE_CODE )
507 and DP.PERIOD_COUNTER = dd.PERIOD_COUNTER
508 AND AH.ASSET_ID = dd.ASSET_ID AND
509 AH.DATE_EFFECTIVE <= NVL(DP.PERIOD_CLOSE_DATE, ah.date_effective+1) AND
510 NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) >
511 NVL(DP.PERIOD_CLOSE_DATE,SYSDATE)
512 AND AD.ASSET_ID = ah.ASSET_ID
513 AND CB.CATEGORY_ID = AH.CATEGORY_ID AND
514 CB.BOOK_TYPE_CODE = :h_book
515 AND AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
519 AND AI_IN.ASSET_ID (+) = TH.ASSET_ID
516 FALU.LOOKUP_TYPE = ''ASSET TYPE''
517 AND
518 IT.INVOICE_TRANSACTION_ID = AI_IN.INVOICE_TRANSACTION_ID_IN
520 AND AI_IN.DATE_EFFECTIVE >= DP1.PERIOD_OPEN_DATE AND
521 AI_IN.DATE_EFFECTIVE < nvl(DP1.PERIOD_CLOSE_DATE,ai_in.date_effective+1)
522 and ai_in.date_ineffective is null
523 AND AI_IN.DELETED_FLAG (+) = ''NO''
524 AND PO_VEND.VENDOR_ID(+) = AI_IN.PO_VENDOR_ID
525 AND CB.CATEGORY_ID = CAT.CATEGORY_ID
526 AND GAD.ASSET_ID(+) = BK.GROUP_ASSET_ID';
527
528 where_clause3 := '
529 DS.BOOK_TYPE_CODE (+) = :h_book AND
530 DS.ASSET_ID (+) = DD.ASSET_ID AND
531 DS.DEPRN_SOURCE_CODE (+) = ''DEPRN'' AND
532 DS.PERIOD_COUNTER (+) = DD.PERIOD_COUNTER + 1 AND
533 DH.ASSET_ID = DD.ASSET_ID AND
534 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
535 AND DH.BOOK_TYPE_CODE = :h_dist_source_book AND
536 DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
537 AND AD.ASSET_ID = DD.ASSET_ID
538 AND DP.BOOK_TYPE_CODE = :h_book AND
539 DP.PERIOD_COUNTER = dd.period_counter+1
540 AND DD.BOOK_TYPE_CODE = :h_book AND
541 DD.DEPRN_SOURCE_CODE = ''B'' AND
542 DD.PERIOD_COUNTER >= :h_period1_pc - 1 and
543 dd.period_counter <= :h_period2_pc - 1
544 AND bk.transaction_header_id_in = th.transaction_header_id
545 AND AH.ASSET_ID = th.ASSET_ID AND
546 AH.DATE_EFFECTIVE <= NVL(DP.PERIOD_CLOSE_DATE,SYSDATE) AND
547 NVL(AH.DATE_INEFFECTIVE, SYSDATE+1) >
548 NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
549 AND CB.CATEGORY_ID = AH.CATEGORY_ID AND
550 CB.BOOK_TYPE_CODE = :h_book
551 AND AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
552 FALU.LOOKUP_TYPE = ''ASSET TYPE''
553 AND TH.ASSET_ID = DD.ASSET_ID AND
554 TH.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND
555 TH.DATE_EFFECTIVE < nvl(DP.PERIOD_CLOSE_DATE,th.date_effective+1) AND
556 TH.TRANSACTION_TYPE_CODE in (''CIP ADDITION'',''ADDITION'') AND
557 TH.BOOK_TYPE_CODE = :h_book
558 AND CB.CATEGORY_ID = CAT.CATEGORY_ID
559 AND GAD.ASSET_ID(+) = BK.GROUP_ASSET_ID';
560
561 -- Bug 5222214 Added OR condition on DP.PERIOD_COUNTER so that
562 -- capitalized assets in the current open period are selected from deprn_periods.
563
564 where_clause4 := '
565 DS.BOOK_TYPE_CODE = :h_book AND
566 DS.ASSET_ID = th.asset_id and
567 DS.DEPRN_SOURCE_CODE = ''DEPRN'' AND
568 DS.PERIOD_COUNTER = DD.PERIOD_COUNTER
569 AND DH.BOOK_TYPE_CODE = :h_dist_source_book AND
570 DH.ASSET_ID = dd.ASSET_ID AND
571 DH.DISTRIBUTION_ID =
572 decode(th.asset_id, null,DD.DISTRIBUTION_ID, DD.DISTRIBUTION_ID) AND
573 DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
574 AND DD.BOOK_TYPE_CODE = :h_book AND
575 DD.ASSET_ID = TH.ASSET_ID AND
576 DD.PERIOD_COUNTER =
577 ( select max(DD1.PERIOD_COUNTER)
578 from FA_DEPRN_DETAIL DD1, FA_DEPRN_PERIODS DP2
579 where dd1.period_counter <= dp2.period_counter
580 and DD1.ASSET_ID = DD.ASSET_ID
581 and DD1.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
582 and DP2.BOOK_TYPE_CODE = DD1.BOOK_TYPE_CODE
583 and DD1.PERIOD_COUNTER >= :h_period1_pc
584 and DP2.PERIOD_COUNTER >= :h_period1_pc
585 and DP2.PERIOD_COUNTER <= :h_period2_pc )
586 AND TH.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND
587 TH.DATE_EFFECTIVE < nvl(DP.PERIOD_CLOSE_DATE,th.date_effective+1) AND
588 TH.BOOK_TYPE_CODE = :h_book AND
589 th.asset_id = dd.asset_id and
590 TH.TRANSACTION_TYPE_CODE = ''ADDITION''
591 AND THDIS.TRANSACTION_TYPE_CODE = ''TRANSFER IN'' AND
592 THDIS.BOOK_TYPE_CODE = :h_book AND
593 THDIS.ASSET_ID = TH.ASSET_ID AND
594 THDIS.DATE_EFFECTIVE < DP.PERIOD_OPEN_DATE
595 AND BK.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
596 AND DP.BOOK_TYPE_CODE =
597 decode(th.asset_id, null,dd.BOOK_TYPE_CODE,dd.BOOK_TYPE_CODE )
598 and ( (DP.PERIOD_COUNTER = dd.PERIOD_COUNTER) OR
599 (DP.PERIOD_COUNTER >= :h_period1_pc AND
600 DP.PERIOD_COUNTER <= :h_period2_pc AND
601 DP.PERIOD_CLOSE_DATE IS NULL AND
602 DP.DEPRN_RUN IS NULL))
603 AND AH.ASSET_ID = dd.ASSET_ID AND
604 AH.DATE_EFFECTIVE <= NVL(DP.PERIOD_CLOSE_DATE, ah.date_effective+1) AND
605 NVL(AH.DATE_INEFFECTIVE, SYSDATE+1) >
606 NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
610 AND FALU.LOOKUP_TYPE = ''ASSET TYPE''
607 AND AD.ASSET_ID = ah.ASSET_ID
608 AND CB.CATEGORY_ID = AH.CATEGORY_ID AND
609 CB.BOOK_TYPE_CODE = :h_book
611 AND AH.ASSET_TYPE = FALU.LOOKUP_CODE
612 AND CB.CATEGORY_ID = CAT.CATEGORY_ID
613 AND GAD.ASSET_ID(+) = BK.GROUP_ASSET_ID';
614
615 IF (l_param_where is not NULL) THEN
616 where_clause1 := where_clause1 || l_param_where;
617 where_clause2 := where_clause2 || l_param_where;
618 where_clause3 := where_clause3 || l_param_where;
619 where_clause4 := where_clause4 || l_param_where;
620 END IF;
621
622 h_mesg_name := 'FA_ADDITION_SQL_DCUR';
623
624 IF (g_print_debug) THEN
625 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'where_clause1:' || where_clause1);
626 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'where_clause2:' || where_clause2);
627 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'where_clause3:' || where_clause3);
628 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'where_clause4:' || where_clause4);
629 END IF;
630
631 -- open additions for
632
633 select_statement := '
634 SELECT DISTINCT
635 DECODE(TH.MASS_REFERENCE_ID,NULL,''Manual Addition'',''Mass Addition''),
636 dhcc.code_combination_id,
637 FALU.MEANING,
638 AH.ASSET_TYPE,
639 DECODE(AH.ASSET_TYPE, ''CIP'', CB.CIP_COST_ACCT,CB.ASSET_COST_ACCT),
640 AD.ASSET_NUMBER,
641 AD.description,
642 ad.tag_number, ad.serial_number, ad.inventorial,
643 ad.asset_key_ccid,
644 PO_VEND.segment1,
645 AI_IN.INVOICE_NUMBER ,
646 AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
647 AI_IN.DESCRIPTION,
648 AI_IN.PAYABLES_COST,
649 DH.UNITS_ASSIGNED/AH.UNITS * AI_IN.FIXED_ASSETS_COST,
650 TO_NUMBER (NULL), -- cost to clear
651 DECODE(IT.TRANSACTION_TYPE,''INVOICE ADDITION'',''M'',
652 ''INVOICE ADJUSTMENT'',''A'',
653 ''INVOICE TRANSFER'',''T'',
654 ''INVOICE REINSTATE'',''R'',NULL),
655 bk.date_placed_in_service,
656 bk.deprn_method_code,
657 bk.life_in_months,
658 bk.production_capacity,
659 bk.adjusted_rate,
660 cb.deprn_reserve_acct,
661 ds.bonus_Rate,
662 cb.category_id, dh.location_id,
663 DD.YTD_DEPRN,
664 DD.DEPRN_RESERVE,
665 TH.TRANSACTION_HEADER_ID,'||
666 maj_select_statement ||','||
667 min_select_statement ||','||
668 spec_select_statement ||' ,
669 gad.asset_number
670 FROM
671 PO_VENDORS PO_VEND,
672 FA_INVOICE_TRANSACTIONS IT,
673 FA_ASSET_INVOICES AI_IN,
674 FA_DEPRN_SUMMARY DS,
675 FA_ADDITIONS AD,
676 GL_CODE_COMBINATIONS DHCC,
677 FA_DISTRIBUTION_HISTORY DH,
678 FA_LOOKUPS FALU,
679 FA_CATEGORY_BOOKS CB,
680 FA_ASSET_HISTORY AH,
681 FA_BOOKS BK,
682 FA_TRANSACTION_HEADERS TH,
683 FA_DEPRN_PERIODS DP1,
684 FA_DEPRN_PERIODS DP,
685 FA_DEPRN_DETAIL DD,
686 FA_CATEGORIES CAT,
687 FA_ADDITIONS_B GAD
688 WHERE ' || where_clause1 || '
689 UNION ALL
690 SELECT DISTINCT
691 DECODE(TH.MASS_REFERENCE_ID,NULL,''Manual Addition'',''Mass Addition''),
692 dhcc.code_combination_id,
693 FALU.MEANING,
694 AH.ASSET_TYPE ,
695 DECODE(AH.ASSET_TYPE, ''CIP'', CB.CIP_COST_ACCT,
696 CB.ASSET_COST_ACCT),
697 AD.ASSET_NUMBER,
698 AD.description,
699 ad.tag_number, ad.serial_number, ad.inventorial,
700 ad.asset_key_ccid,
701 PO_VEND.segment1,
702 AI_IN.INVOICE_NUMBER ,
703 AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
704 AI_IN.DESCRIPTION ,
705 AI_IN.PAYABLES_COST,
706 DH.UNITS_ASSIGNED/AH.UNITS * AI_IN.FIXED_ASSETS_COST,
707 TO_NUMBER (NULL), -- cost to clear
708 DECODE(IT.TRANSACTION_TYPE,''INVOICE ADDITION'',''M'',
709 ''INVOICE ADJUSTMENT'',''A'',
710 ''INVOICE TRANSFER'',''T'',
711 ''INVOICE REINSTATE'',''R'',NULL),
712 bk.date_placed_in_service,
713 bk.deprn_method_code,
714 bk.life_in_months,
715 bk.production_capacity,
716 bk.adjusted_rate,
717 cb.deprn_reserve_acct,
718 ds.bonus_Rate,
719 cb.category_id, dh.location_id,
720 DD.YTD_DEPRN,
721 DD.DEPRN_RESERVE,
722 TH.TRANSACTION_HEADER_ID,'||
723 maj_select_statement ||','||
724 min_select_statement ||','||
725 spec_select_statement ||' ,
726 gad.asset_number
727 FROM
728 PO_VENDORS PO_VEND,
729 FA_INVOICE_TRANSACTIONS IT,
730 FA_ASSET_INVOICES AI_IN,
731 FA_DISTRIBUTION_HISTORY DH,
732 GL_CODE_COMBINATIONS DHCC,
733 fa_deprn_summary ds,
734 FA_TRANSACTION_HEADERS THDIS,
735 fa_books bk,
739 FA_ASSET_HISTORY AH,
736 FA_ADDITIONS AD,
737 FA_CATEGORY_BOOKS CB,
738 FA_LOOKUPS FALU,
740 FA_TRANSACTION_HEADERS THADD,
741 FA_DEPRN_PERIODS DP2,
742 FA_DEPRN_PERIODS DP1,
743 FA_DEPRN_PERIODS DP,
744 FA_TRANSACTION_HEADERS TH,
745 fa_deprn_detail dd,
746 fa_categories cat,
747 fa_additions_b GAD
748 WHERE ' || where_clause2 || '
749 UNION ALL
750 SELECT DISTINCT
751 DECODE(TH.MASS_REFERENCE_ID,NULL,''Manual Addition'',''Mass Addition''),
752 dhcc.code_combination_id,
753 FALU.MEANING,
754 AH.ASSET_TYPE ,
755 DECODE(AH.ASSET_TYPE, ''CIP'', CB.CIP_COST_ACCT,
756 CB.ASSET_COST_ACCT),
757 AD.ASSET_NUMBER,
758 AD.description,
759 ad.tag_number, ad.serial_number, ad.inventorial,
760 ad.asset_key_ccid,
761 NULL, -- vendor number
762 NULL, -- invoice number
763 TO_NUMBER(NULL), -- line number
764 NULL, -- invoice description
765 TO_NUMBER(NULL), -- invoice original cost
766 TO_NUMBER(NULL), -- invoice cost
767 NVL(DD.ADDITION_COST_TO_CLEAR, 0),
768 NULL, -- invoice flag
769 bk.date_placed_in_service,
770 bk.deprn_method_code,
771 bk.life_in_months,
772 bk.production_capacity,
773 bk.adjusted_rate,
774 cb.deprn_reserve_acct,
775 ds.bonus_Rate,
776 cb.category_id, dh.location_id,
777 DD.YTD_DEPRN,
778 DD.DEPRN_RESERVE,
779 TH.TRANSACTION_HEADER_ID,'||
780 maj_select_statement ||','||
781 min_select_statement ||','||
782 spec_select_statement ||' ,
783 gad.asset_number
784 FROM
785 FA_DEPRN_SUMMARY DS,
786 FA_ADDITIONS AD,
787 GL_CODE_COMBINATIONS DHCC,
788 FA_DISTRIBUTION_HISTORY DH,
789 FA_LOOKUPS FALU,
790 FA_CATEGORY_BOOKS CB,
791 FA_ASSET_HISTORY AH,
792 FA_BOOKS BK,
793 FA_TRANSACTION_HEADERS TH,
794 FA_DEPRN_PERIODS DP,
795 FA_DEPRN_DETAIL DD,
796 fa_categories cat,
797 fa_additions_b GAD
798 WHERE ' || where_clause3 || '
799 UNION ALL
800 SELECT DISTINCT
801 DECODE(TH.MASS_REFERENCE_ID,NULL,''Manual Addition'',''Mass Addition''),
802 dhcc.code_combination_id,
803 FALU.MEANING,
804 AH.ASSET_TYPE ,
805 DECODE(AH.ASSET_TYPE, ''CIP'', CB.CIP_COST_ACCT,
806 CB.ASSET_COST_ACCT),
807 AD.ASSET_NUMBER,
808 AD.description,
809 ad.tag_number, ad.serial_number, ad.inventorial,
810 ad.asset_key_ccid,
811 NULL, -- vendor number
812 NULL, -- invoice number
813 TO_NUMBER(NULL), -- line number
814 NULL, -- invoice description
815 TO_NUMBER(NULL), -- invoice original cost
816 TO_NUMBER(NULL), -- invoice cost
817 bk.cost,
818 NULL, -- invoice flag
819 bk.date_placed_in_service,
820 bk.deprn_method_code,
821 bk.life_in_months,
822 bk.production_capacity,
823 bk.adjusted_rate,
824 cb.deprn_reserve_acct,
825 ds.bonus_Rate,
826 cb.category_id, dh.location_id,
827 DD.YTD_DEPRN,
828 DD.DEPRN_RESERVE,
829 TH.TRANSACTION_HEADER_ID,'||
830 maj_select_statement ||','||
831 min_select_statement ||','||
832 spec_select_statement ||' ,
833 gad.asset_number
834 FROM
835 FA_DISTRIBUTION_HISTORY DH,
836 GL_CODE_COMBINATIONS DHCC,
837 fa_deprn_summary ds,
838 FA_TRANSACTION_HEADERS THDIS,
839 fa_books bk,
840 FA_ADDITIONS AD,
841 FA_CATEGORY_BOOKS CB,
842 FA_LOOKUPS FALU,
843 FA_ASSET_HISTORY AH,
844 FA_DEPRN_PERIODS DP,
845 FA_TRANSACTION_HEADERS TH,
846 fa_deprn_detail dd,
847 fa_categories cat,
848 FA_ADDITIONS_B GAD
849 WHERE ' || where_clause4 ;
850
851 IF (g_print_debug) THEN
852 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'select_statement:='|| select_statement);
853 END IF;
854
855 /* BUG# 2939771
856 open additions for select_statement;
857 -- USING where_clause1,where_clause2,where_clause3,where_clause4;
858 */
859 open additions for select_statement using
860 h_book, -- where_clause1
861 h_dist_source_book,
862 h_book,
863 h_book,
864 h_period1_pc,
865 h_period2_pc,
866 h_book,
870 h_book,
867 h_period1_pc,
868 h_period2_pc,
869 h_book,
871 from_maj_cat, -- l_param_where
872 from_maj_cat,
873 to_maj_cat,
874 to_maj_cat,
875 from_min_cat,
876 from_min_cat,
877 to_min_cat,
878 to_min_cat,
879 from_cat_seg_val,
880 from_cat_seg_val,
881 to_cat_seg_val,
882 to_cat_seg_val,
883 from_cc,
884 from_cc,
885 to_cc,
886 to_cc,
887 from_asset_num,
888 from_asset_num,
889 to_asset_num,
890 to_asset_num,
891 h_book, -- where_clause2
892 h_dist_source_book,
893 h_book,
894 h_book,
895 h_period1_pc,
896 h_period2_pc,
897 h_book,
898 h_period1_pc,
899 h_period2_pc,
900 h_book,
901 h_book,
902 from_maj_cat, -- l_param_where
903 from_maj_cat,
904 to_maj_cat,
905 to_maj_cat,
906 from_min_cat,
907 from_min_cat,
908 to_min_cat,
909 to_min_cat,
910 from_cat_seg_val,
911 from_cat_seg_val,
912 to_cat_seg_val,
913 to_cat_seg_val,
914 from_cc,
915 from_cc,
916 to_cc,
917 to_cc,
918 from_asset_num,
919 from_asset_num,
920 to_asset_num,
921 to_asset_num,
922 h_book, -- where_clause3
923 h_dist_source_book,
924 h_book,
925 h_book,
926 h_period1_pc,
927 h_period2_pc,
928 h_book,
929 h_book,
930 from_maj_cat, -- l_param_where
931 from_maj_cat,
932 to_maj_cat,
933 to_maj_cat,
934 from_min_cat,
935 from_min_cat,
936 to_min_cat,
937 to_min_cat,
938 from_cat_seg_val,
939 from_cat_seg_val,
940 to_cat_seg_val,
941 to_cat_seg_val,
942 from_cc,
943 from_cc,
944 to_cc,
945 to_cc,
946 from_asset_num,
947 from_asset_num,
948 to_asset_num,
949 to_asset_num,
950 h_book, -- where_clause4
951 h_dist_source_book,
952 h_book,
953 h_period1_pc,
954 h_period1_pc,
955 h_period2_pc,
956 h_book,
957 h_book,
958 h_period1_pc, -- Bug 5222214
959 h_period2_pc, -- Bug 5222214
960 h_book,
961 from_maj_cat, -- l_param_where
962 from_maj_cat,
963 to_maj_cat,
964 to_maj_cat,
965 from_min_cat,
966 from_min_cat,
967 to_min_cat,
968 to_min_cat,
969 from_cat_seg_val,
970 from_cat_seg_val,
971 to_cat_seg_val,
972 to_cat_seg_val,
973 from_cc,
974 from_cc,
975 to_cc,
976 to_cc,
977 from_asset_num,
978 from_asset_num,
979 to_asset_num,
980 to_asset_num;
981
982
983 IF (g_print_debug) THEN
984 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'Before Loop');
985 END IF;
986
987 loop
988
989 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
990
991 fetch additions into
992 h_source,
993 h_ccid,
994 h_asset_type_mean,
995 h_asset_type,
996 h_cost_acct,
997 h_asset_number,
998 h_description,
999 h_tag_number, h_serial_number, h_inventorial,
1000 h_asset_key_ccid,
1001 h_vendor_number,
1002 h_invoice_number,
1003 h_line_number,
1004 h_invoice_descr,
1005 h_invoice_orig_cost,
1006 h_invoice_cost,
1007 h_cost_to_clear,
1008 h_invoice_flag,
1009 h_dpis,
1010 h_method,
1011 h_life_months,
1012 h_prod_capacity,
1013 h_adjusted_Rate,
1014 h_reserve_acct,
1015 h_bonus_rate,
1016 h_category_id,
1017 h_location_id,
1018 h_ytd_deprn,
1019 h_deprn_reserve,
1020 h_tran_header_id,
1021 h_maj_cat,
1022 h_min_cat,
1023 h_specified_cat,
1024 h_group_asset_number;
1025
1026 if (additions%NOTFOUND) then exit; end if;
1027 ctr := ctr + 1;
1028
1029 mesg := 'concat_account';
1030
1031 h_mesg_name := 'FA_RX_CONCAT_SEGS';
1032 h_flex_error := 'GL#';
1033 h_ccid_error := h_ccid;
1034
1035 fa_rx_shared_pkg.concat_acct (
1036 struct_id => h_acct_flex_struct,
1037 ccid => h_ccid,
1038 concat_string => h_concat_acct,
1039 segarray => h_acct_segs);
1040
1041 mesg := 'concat_category';
1042
1043 h_flex_error := 'CAT#';
1047 struct_id => h_cat_flex_struct,
1044 h_ccid_error := h_category_id;
1045
1046 fa_rx_shared_pkg.concat_category (
1048 ccid => h_category_id,
1049 concat_string => h_concat_cat,
1050 segarray => h_cat_segs);
1051
1052 mesg := 'concat_location';
1053
1054 h_flex_error := 'LOC#';
1055 h_ccid_error := h_location_id;
1056
1057 fa_rx_shared_pkg.concat_location (
1058 struct_id => h_loc_flex_struct,
1059 ccid => h_location_id,
1060 concat_string => h_concat_loc,
1061 segarray => h_loc_segs);
1062
1063
1064
1065 if h_asset_key_ccid is not null then
1066 mesg := 'concat_asset_key';
1067
1068 h_flex_error := 'KEY#';
1069 h_ccid_error := h_asset_key_ccid;
1070
1071 fa_rx_shared_pkg.concat_asset_key (
1072 struct_id => h_assetkey_flex_structure,
1073 ccid => h_asset_key_ccid,
1074 concat_string => h_concat_key,
1075 segarray => h_key_segs);
1076 else
1077 h_concat_key := '';
1078
1079 end if;
1080
1081 select decode(h_life_months, null, null,
1082 to_char(floor(h_life_months/12)) || '.' ||
1083 to_char(mod(h_life_months,12)))
1084 into h_life_year_month
1085 from dual;
1086
1087 h_life_year_month_num := fnd_number.canonical_to_number(h_life_year_month);
1088
1089 /* h_account_desc :=
1090 fa_rx_flex_pkg.get_description(
1091 p_application_id => 101,
1092 p_id_flex_code => 'GL#',
1093 p_id_flex_num => h_chart_of_accounts_id,
1094 p_qualifier => 'GL_ACCOUNT',
1095 p_data => h_acct_segs(h_acct_seg));
1096 */
1097
1098 h_account_desc :=
1099 fa_rx_flex_pkg.get_description(
1100 p_application_id => 101,
1101 p_id_flex_code => 'GL#',
1102 p_id_flex_num => h_chart_of_accounts_id,
1103 p_qualifier => 'GL_ACCOUNT',
1104 p_data => h_cost_acct);
1105
1106 h_cost_center_desc :=
1107 fa_rx_flex_pkg.get_description(
1108 p_application_id => 101,
1109 p_id_flex_code => 'GL#',
1110 p_id_flex_num => h_chart_of_accounts_id,
1111 p_qualifier => 'FA_COST_CTR',
1112 p_data => h_acct_segs(h_cost_seg));
1113
1114
1115 IF (g_print_debug) THEN
1116 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || '** assetkey_flex_struct:' || h_assetkey_flex_structure);
1117 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || '** category_id:' || h_category_id);
1118 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || '** specified_cat:' || h_cat_seg_num);
1119 END IF;
1120
1121 /*
1122 h_maj_cat :=
1123 fa_rx_flex_pkg.get_value(
1124 p_application_id => 140,
1125 p_id_flex_code => 'CAT#',
1126 p_id_flex_num => h_assetkey_flex_structure,
1127 p_qualifier => 'BASED_CATEGORY',
1128 p_ccid => h_category_id);
1129 */
1130 begin
1131 h_maj_cat_desc :=
1132 fa_rx_flex_pkg.get_description(
1133 p_application_id => 140,
1134 p_id_flex_code => 'CAT#',
1135 p_id_flex_num => h_assetkey_flex_structure,
1136 p_qualifier => 'BASED_CATEGORY',
1137 p_data => h_maj_cat);
1138 exception
1139 when others then
1140 h_maj_cat_desc := null;
1141 end;
1142
1143 /*
1144 BEGIN
1145 h_min_cat :=
1146 fa_rx_flex_pkg.get_value(
1147 p_application_id => 140,
1148 p_id_flex_code => 'CAT#',
1149 p_id_flex_num => h_assetkey_flex_structure,
1150 p_qualifier => 'MINOR_CATEGORY',
1151 p_ccid => h_category_id);
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 h_min_cat := null;
1155 end;
1156 */
1157 begin
1158 h_min_cat_desc :=
1159 fa_rx_flex_pkg.get_description(
1160 p_application_id => 140,
1161 p_id_flex_code => 'CAT#',
1162 p_id_flex_num => h_assetkey_flex_structure,
1163 p_qualifier => 'MINOR_CATEGORY',
1164 p_data => h_min_cat);
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 h_min_cat_desc := null;
1168 end;
1169 /*
1170 BEGIN
1171 h_specified_cat :=
1172 fa_rx_flex_pkg.get_value(
1173 p_application_id => 140,
1174 p_id_flex_code => 'CAT#',
1175 p_id_flex_num => h_assetkey_flex_structure,
1176 p_qualifier => h_cat_seg_num,
1177 p_ccid => h_category_id);
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 h_specified_cat := null;
1181 end;
1182 */
1183 begin
1184 h_specified_cat_desc :=
1185 fa_rx_flex_pkg.get_description(
1186 p_application_id => 140,
1187 p_id_flex_code => 'CAT#',
1188 p_id_flex_num => h_assetkey_flex_structure,
1189 p_qualifier => h_cat_seg_num,
1190 p_data => h_specified_cat);
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 h_specified_cat_desc := null;
1194 end;
1195
1196 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
1197
1198 insert into fa_addition_rep_itf (
1199 request_id, source, company, cost_Center, expense_acct,
1203 invoice_orig_cost, invoice_cost, cost_to_clear,
1200 asset_type, asset_number,
1201 tag_number, serial_number, inventorial, description, vendor_number,
1202 invoice_number, line_number, invoice_descr,
1204 invoice_flag, date_placed_in_service, method,
1205 life_year_month, prod_capacity, adjusted_rate,
1206 reserve_acct, cost_acct, category, location,
1207 last_update_date, creation_date, last_updated_by,
1208 last_update_login, created_by,
1209 reserve, set_of_books_id, functional_currency_code,organization_name,
1210 book_type_code, period_name, period_name_to,
1211 account_description, cost_center_description, ytd_depreciation,
1212 transaction_header_id, major_category, major_category_desc,
1213 minor_category, minor_category_desc,specified_category_seg,
1214 specified_cat_seg_desc, group_asset_number, asset_key ) values (
1215 request_id, h_source, h_acct_segs(h_bal_seg),
1216 h_acct_segs(h_cost_seg), h_acct_segs(h_acct_seg),
1217 h_asset_type_mean, h_asset_number,
1218 h_tag_number, h_serial_number, h_inventorial, h_description,
1219 h_vendor_number, h_invoice_number, h_line_number,
1220 h_invoice_descr, h_invoice_orig_cost, h_invoice_cost,
1221 h_cost_to_clear, h_invoice_flag, h_dpis,
1222 h_method, h_life_year_month_num, h_prod_capacity,
1223 h_adjusted_rate, h_reserve_acct, h_cost_acct,
1224 h_concat_cat, h_concat_loc, sysdate, sysdate,
1225 user_id, h_login_id, user_id,
1226 h_deprn_reserve, h_set_of_books_id, h_currency_code,h_organization_name,
1227 h_book, h_period_name, h_period_name_to, h_account_desc,
1228 h_cost_center_desc, h_ytd_deprn, h_tran_header_id,
1229 h_maj_cat, h_maj_cat_desc, h_min_cat, h_min_cat_desc, h_specified_cat,
1230 h_specified_cat_desc, h_group_asset_number, h_concat_key);
1231
1232
1233 end loop;
1234
1235 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
1236
1237 close additions;
1238
1239 IF (g_print_debug) THEN
1240 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'loop counter:' || ctr);
1241 END IF;
1242
1243 exception when others then
1244 fa_Rx_conc_mesg_pkg.log('Error occurred');
1245 fa_Rx_conc_mesg_pkg.log(h_mesg_name);
1246 if SQLCODE <> 0 then
1247 fa_Rx_conc_mesg_pkg.log(SQLERRM);
1248 end if;
1249 fnd_message.set_name('OFA',h_mesg_name);
1250 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
1251 fnd_message.set_token('TABLE','FA_ADDITION_REP_ITF',FALSE);
1252 end if;
1253 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
1254 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
1255 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
1256 end if;
1257
1258 h_mesg_str := fnd_message.get;
1259 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1260 retcode := 2;
1261
1262 end add_by_period;
1263
1264
1265 procedure add_by_date (
1266 book in varchar2,
1267 begin_dpis in date,
1268 end_dpis in date,
1269 request_id in number,
1270 user_id in number,
1271 retcode out nocopy number,
1272 errbuf out nocopy varchar2) is
1273
1274 mesg varchar2(200);
1275 ctr number;
1276
1277 h_book varchar2(15);
1278 h_request_id number;
1279 h_login_id number;
1280
1281 h_bonus_rate number;
1282 h_reserve_acct varchar2(25);
1283 h_adjusted_Rate number;
1284 h_prod_capacity number;
1285 h_life_months number;
1286 h_life_year_month varchar2(10);
1287 h_life_year_month_num number;
1288 h_method varchar2(15);
1289 h_dpis date;
1290 h_invoice_flag varchar2(1);
1291 h_cost_to_clear number;
1292 h_invoice_cost number;
1293 h_invoice_orig_cost number;
1294 h_invoice_descr varchar2(80);
1295 h_line_number number;
1296 h_invoice_number varchar2(50);
1297 h_tag_number varchar2(15);
1298 h_serial_number varchar2(35);
1299 h_inventorial varchar2(3);
1300 h_vendor_number varchar2(30);
1301 h_description varchar2(80);
1302 h_asset_number varchar2(15);
1303 h_asset_type varchar2(15);
1304 h_cost_acct varchar2(25);
1305 h_asset_type_mean varchar2(80);
1306 h_ccid number;
1307 h_source varchar2(20);
1308
1309 h_category_id number;
1310 h_location_id number;
1311
1312 h_concat_acct varchar2(200);
1313 h_concat_cat varchar2(200);
1314 h_concat_loc varchar2(200);
1315 h_acct_segs fa_rx_shared_pkg.Seg_Array;
1316 h_cat_segs fa_rx_shared_pkg.Seg_Array;
1317 h_loc_segs fa_rx_shared_pkg.Seg_Array;
1318
1319 h_acct_seg number;
1320 h_cost_seg number;
1321 h_bal_seg number;
1322
1323 h_dist_source_book varchar2(15);
1324
1325 h_acct_flex_struct number;
1326 h_cat_flex_struct number;
1327 h_loc_flex_struct number;
1328
1329 h_count number;
1330
1331 h_mesg_name varchar2(50);
1332 h_mesg_str varchar2(2000);
1333 h_flex_error varchar2(5);
1334 h_ccid_error number;
1335
1336 cursor additions is
1337 SELECT DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'), -- source
1338 dh.code_combination_id, -- expense account
1339 FALU.MEANING, -- translated asset type
1340 AH.ASSET_TYPE,
1341 DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1342 CB.ASSET_COST_ACCT),
1343 AD.ASSET_NUMBER,
1344 AD.description,
1348 TO_NUMBER(NULL), -- line number
1345 ad.tag_number, ad.serial_number, ad.inventorial,
1346 NULL, -- vendor number
1347 NULL, -- invoice number
1349 NULL, -- invoice description
1350 TO_NUMBER(NULL) , -- invoice original cost
1351 TO_NUMBER(NULL), -- invoice cost
1352
1353 ---bug fix 4275433
1354 decode( (decode(adj.debit_credit_flag,'DR',1,-1) * nvl(adj.adjustment_amount,0)),0,dd.addition_cost_to_clear,(decode(adj.debit_credit_flag,'DR',1,-1) * nvl(adj.adjustment_amount,0))) ,
1355
1356 -- NVL(DD.ADDITION_COST_TO_CLEAR, 0), -- cost-to-clear
1357 NULL, -- flag
1358 bk.date_placed_in_service,
1359 bk.deprn_method_code,
1360 bk.life_in_months,
1361 bk.production_capacity,
1362 bk.adjusted_rate,
1363 cb.deprn_reserve_acct,
1364 ds.bonus_Rate,
1365 cb.category_id, dh.location_id
1366 FROM
1367 FA_DISTRIBUTION_HISTORY DH,
1368 FA_ASSET_HISTORY AH,
1369 FA_CATEGORY_BOOKS CB,
1370 FA_LOOKUPS FALU,
1371 FA_ADDITIONS AD,
1372 --GL_CODE_COMBINATIONS DHCC,
1373 FA_BOOKS BK,
1374 FA_DEPRN_SUMMARY DS,
1375 FA_TRANSACTION_HEADERS TH,
1376 FA_DEPRN_DETAIL DD,
1377 fa_adjustments adj
1378
1379 WHERE
1380 bk.book_type_code = th.book_type_code AND
1381 bk.asset_id = th.asset_id AND
1382 bk.transaction_header_id_in = th.transaction_header_id AND
1383 bk.date_placed_in_service >= begin_dpis AND
1384 bk.date_placed_in_service <= end_dpis
1385 AND
1386 ds.book_type_code = dd.book_type_code AND
1387 ds.asset_id = dd.asset_id AND
1388 ds.period_counter = dd.period_counter
1389 AND
1390 th.asset_id = dd.asset_id AND
1391 th.transaction_type_code = 'ADDITION' AND
1392 -- bug fix 3807732
1393 th.book_type_code = h_book
1394 AND
1395 DH.BOOK_TYPE_CODE = h_dist_source_book AND
1396 DH.ASSET_ID = DD.ASSET_ID AND
1397 --DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
1398 --AND
1399 DD.BOOK_TYPE_CODE = h_book AND
1400 DD.DEPRN_SOURCE_CODE = 'B' AND
1401 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
1402 AND
1403 ADJ.book_type_code(+) = h_book AND
1404 ADJ.asset_id(+) = dh.ASSET_ID AND
1405 ADJ.source_type_code(+) like '%ADDITION' AND
1406 adj.adjustment_type(+) like 'COST' and
1407 ADJ.distribution_id(+) = DH.DISTRIBUTION_ID
1408 AND
1409 CB.CATEGORY_ID = AH.CATEGORY_ID AND
1410 CB.BOOK_TYPE_CODE = h_book
1411 AND
1412 AD.ASSET_ID = DD.ASSET_ID
1413 AND
1414 AH.ASSET_ID = AD.ASSET_ID AND
1415 AH.DATE_EFFECTIVE <= th.date_effective AND
1416 NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > th.date_effective
1417 AND
1418 AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
1419 FALU.LOOKUP_TYPE = 'ASSET TYPE'
1420 --GROUP BY
1421 -- DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1422 -- dh.code_combination_id,
1423 -- FALU.MEANING,
1424 -- AH.ASSET_TYPE,
1425 -- DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1426 -- CB.ASSET_COST_ACCT),
1427 -- AD.ASSET_NUMBER,
1428 -- AD.description,
1429 -- bk.date_placed_in_service,
1430 -- bk.deprn_method_code,
1431 -- bk.life_in_months,
1432 -- bk.production_capacity,
1433 -- bk.adjusted_rate,
1434 -- cb.deprn_reserve_acct,
1435 -- ds.bonus_Rate,
1436 -- cb.category_id, dh.location_id
1437 UNION ALL
1438 SELECT DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1439 dh.code_combination_id,
1440 FALU.MEANING,
1441 AH.ASSET_TYPE,
1442 DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1443 CB.ASSET_COST_ACCT),
1444 AD.ASSET_NUMBER,
1445 AD.description,
1446 ad.tag_number, ad.serial_number, ad.inventorial,
1447 NULL,
1448 NULL,
1449 TO_NUMBER(NULL),
1450 NULL,
1451 TO_NUMBER(NULL),
1452 TO_NUMBER(NULL),
1453 DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR',1,-1) *
1454 ADJ.ADJUSTMENT_AMOUNT,
1455 NULL,
1456 bk.date_placed_in_service,
1457 bk.deprn_method_code,
1458 bk.life_in_months,
1459 bk.production_capacity,
1460 bk.adjusted_rate,
1461 cb.deprn_reserve_acct,
1462 ds.bonus_Rate,
1463 cb.category_id, dh.location_id
1464 FROM
1465 fa_books bk,
1466 fa_deprn_summary ds,
1467 FA_TRANSACTION_HEADERS TH,
1468 FA_TRANSACTION_HEADERS THDIS,
1469 FA_ADDITIONS AD,
1470 FA_ASSET_HISTORY AH,
1471 FA_CATEGORY_BOOKS CB,
1472 FA_DISTRIBUTION_HISTORY DH,
1473 --GL_CODE_COMBINATIONS DHCC,
1474 --GL_CODE_COMBINATIONS AJCC,
1475 FA_LOOKUPS FALU,
1476 FA_ADJUSTMENTS ADJ,
1477 fa_deprn_periods dp
1478 WHERE
1479 DP.BOOK_TYPE_CODE = h_book AND
1480 DP.period_open_date >= bk.date_effective --AND
1481 -- dp.period_close_date <= nvl(bk.date_ineffective,sysdate)
1482 AND
1483 ds.asset_id = bk.asset_id and
1484 ds.book_type_code = bk.book_type_code and
1485 -- bugfix 3807732
1486 ds.deprn_source_code = 'BOOKS' and
1487 (ds.period_counter + 1) = dp.period_counter
1488 AND
1489 bk.asset_id = th.asset_id and
1490 bk.book_type_code = th.book_type_code and
1491 bk.transaction_header_id_in = th.transaction_header_id AND
1492 bk.date_placed_in_service >= begin_dpis AND
1493 bk.date_placed_in_service <= end_dpis
1497 AND
1494 AND
1495 TH.BOOK_TYPE_CODE = h_book AND
1496 TH.TRANSACTION_TYPE_CODE = 'ADDITION'
1498
1499 THDIS.TRANSACTION_TYPE_CODE = 'TRANSFER IN' AND
1500 THDIS.BOOK_TYPE_CODE = h_book AND
1501 THDIS.ASSET_ID = TH.ASSET_ID AND
1502 THDIS.DATE_EFFECTIVE < th.date_effective
1503 AND
1504 ADJ.BOOK_TYPE_CODE = h_book AND
1505 ADJ.ASSET_ID = TH.ASSET_ID AND
1506 ADJ.SOURCE_TYPE_CODE = 'ADDITION' AND
1507 ADJ.ADJUSTMENT_TYPE = 'COST' AND
1508 ADJ.PERIOD_COUNTER_CREATED = DP.PERIOD_COUNTER AND
1509 --ADJ.CODE_COMBINATION_ID = AJCC.CODE_COMBINATION_ID
1510 --AND
1511 DH.BOOK_TYPE_CODE = h_book AND
1512 DH.ASSET_ID = TH.ASSET_ID AND
1513 DH.DISTRIBUTION_ID = ADJ.DISTRIBUTION_ID AND
1514 --DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
1515 --AND
1516 CB.CATEGORY_ID = AH.CATEGORY_ID AND
1517 CB.BOOK_TYPE_CODE = h_book
1518 AND
1519 AD.ASSET_ID = TH.ASSET_ID
1520 AND
1521 AH.ASSET_ID = TH.ASSET_ID AND
1522 AH.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
1523 NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > TH.DATE_EFFECTIVE
1524 AND
1525 AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
1526 FALU.LOOKUP_TYPE = 'ASSET TYPE'
1527 --GROUP BY
1528 -- DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1529 -- dh.code_combination_id,
1530 -- FALU.MEANING,
1531 -- AH.ASSET_TYPE,
1532 -- DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1533 -- CB.ASSET_COST_ACCT),
1534 -- AD.ASSET_NUMBER,
1535 -- AD.description,
1536 -- bk.date_placed_in_service,
1537 -- bk.deprn_method_code,
1538 -- bk.life_in_months,
1539 -- bk.production_capacity,
1540 -- bk.adjusted_rate,
1541 -- cb.deprn_reserve_acct,
1542 -- ds.bonus_Rate,
1543 -- cb.category_id, dh.location_id
1544 UNION ALL
1545 SELECT DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1546 dh.code_combination_id,
1547 FALU.MEANING,
1548 AH.ASSET_TYPE,
1549 DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1550 CB.ASSET_COST_ACCT),
1551 AD.ASSET_NUMBER,
1552 AD.description,
1553 ad.tag_number, ad.serial_number, ad.inventorial,
1554 PO_VEND.segment1,
1555 AI_IN.INVOICE_NUMBER,
1556 decode(AI_IN.INVOICE_LINE_NUMBER, null, AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
1557 AI_IN.INVOICE_LINE_NUMBER||' - '||AI_IN.AP_DISTRIBUTION_LINE_NUMBER ),
1558 AI_IN.DESCRIPTION,
1559 AI_IN.PAYABLES_COST,
1560 DH.UNITS_ASSIGNED/AH.UNITS * AI_IN.FIXED_ASSETS_COST,
1561 TO_NUMBER(NULL),
1562 DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
1563 'INVOICE ADJUSTMENT','A',
1564 'INVOICE TRANSFER','T',
1565 'INVOICE REINSTATE','R',NULL),
1566 bk.date_placed_in_service,
1567 bk.deprn_method_code,
1568 bk.life_in_months,
1569 bk.production_capacity,
1570 bk.adjusted_rate,
1571 cb.deprn_reserve_acct,
1572 ds.bonus_Rate,
1573 cb.category_id, dh.location_id
1574 FROM
1575 FA_ASSET_INVOICES AI_IN,
1576 FA_INVOICE_TRANSACTIONS IT,
1577 FA_BOOKS BK,
1578 FA_DEPRN_SUMMARY DS,
1579 FA_TRANSACTION_HEADERS TH,
1580 FA_DISTRIBUTION_HISTORY DH,
1581 FA_ASSET_HISTORY AH,
1582 FA_CATEGORY_BOOKS CB,
1583 FA_LOOKUPS FALU,
1584 PO_VENDORS PO_VEND,
1585 FA_ADDITIONS AD,
1586 --GL_CODE_COMBINATIONS DHCC,
1587 FA_DEPRN_DETAIL DD
1588 WHERE
1589 bk.book_type_code = th.book_type_code AND
1590 bk.asset_id = th.asset_id AND
1591 bk.date_placed_in_service >= begin_dpis AND
1592 bk.date_placed_in_service <= end_dpis AND
1593 bk.transaction_header_id_in = th.transaction_header_id
1594 AND
1595 th.asset_id = dd.asset_id AND
1596 th.book_type_code = h_book AND
1597 th.transaction_type_code = 'ADDITION'
1598 AND
1599 ds.book_type_code = dd.book_type_code AND
1600 ds.asset_id = dd.asset_id AND
1601 ds.period_counter = dd.period_counter
1602 AND
1603 DH.BOOK_TYPE_CODE = h_dist_source_book AND
1604 DH.ASSET_ID = DD.ASSET_ID AND
1605 --DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
1606 --AND
1607 DD.BOOK_TYPE_CODE = h_book AND
1608 DD.DEPRN_SOURCE_CODE = 'B' AND
1609 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
1610 AND
1611 CB.CATEGORY_ID = AH.CATEGORY_ID AND
1612 CB.BOOK_TYPE_CODE = h_book
1613 AND
1614 AD.ASSET_ID = DD.ASSET_ID
1615 AND
1616 AH.ASSET_ID = AD.ASSET_ID AND
1617 AH.DATE_EFFECTIVE <= th.date_effective AND
1618 NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > th.date_effective
1619 AND
1620 AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
1621 FALU.LOOKUP_TYPE = 'ASSET TYPE'
1622 AND
1623 IT.INVOICE_TRANSACTION_ID = AI_IN.INVOICE_TRANSACTION_ID_IN
1624 AND
1625 AI_IN.ASSET_ID = TH.ASSET_ID AND
1626 AI_IN.DATE_EFFECTIVE <= th.date_effective AND
1627 NVL(AI_IN.DATE_INEFFECTIVE, SYSDATE+1) > th.date_effective AND
1628 AI_IN.DELETED_FLAG = 'NO'
1629 AND
1630 PO_VEND.VENDOR_ID(+) = AI_IN.PO_VENDOR_ID
1631 --GROUP BY
1632 -- DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1633 -- dh.code_combination_id,
1634 -- FALU.MEANING,
1635 -- AH.ASSET_TYPE,
1636 -- DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1637 -- CB.ASSET_COST_ACCT),
1638 -- AD.ASSET_NUMBER,
1639 -- AD.description,
1640 -- PO_VEND.segment1,
1641 -- AI_IN.INVOICE_NUMBER,
1642 -- AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
1646 -- 'INVOICE ADJUSTMENT','A',
1643 -- AI_IN.DESCRIPTION,
1644 -- AI_IN.PAYABLES_COST ,
1645 -- DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
1647 -- 'INVOICE TRANSFER','T',
1648 -- 'INVOICE REINSTATE','R',NULL),
1649 -- bk.date_placed_in_service,
1650 -- bk.deprn_method_code,
1651 -- bk.life_in_months,
1652 -- bk.production_capacity,
1653 -- bk.adjusted_rate,
1654 -- cb.deprn_reserve_acct,
1655 -- ds.bonus_Rate,
1656 -- cb.category_id, dh.location_id
1657 UNION ALL
1658 SELECT DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1659 dh.code_combination_id,
1660 FALU.MEANING,
1661 AH.ASSET_TYPE ,
1662 DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1663 CB.ASSET_COST_ACCT),
1664 AD.ASSET_NUMBER,
1665 AD.description,
1666 ad.tag_number, ad.serial_number, ad.inventorial,
1667 PO_VEND.segment1,
1668 AI_IN.INVOICE_NUMBER ,
1669 decode(AI_IN.INVOICE_LINE_NUMBER, null, AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
1670 AI_IN.INVOICE_LINE_NUMBER||' - '||AI_IN.AP_DISTRIBUTION_LINE_NUMBER ),
1671 --AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
1672 AI_IN.DESCRIPTION ,
1673 AI_IN.PAYABLES_COST,
1674 DH.UNITS_ASSIGNED/AH.UNITS * AI_IN.FIXED_ASSETS_COST,
1675 TO_NUMBER(NULL),
1676 DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
1677 'INVOICE ADJUSTMENT','A',
1678 'INVOICE TRANSFER','T',
1679 'INVOICE REINSTATE','R',NULL),
1680 bk.date_placed_in_service,
1681 bk.deprn_method_code,
1682 bk.life_in_months,
1683 bk.production_capacity,
1684 bk.adjusted_rate,
1685 cb.deprn_reserve_acct,
1686 ds.bonus_Rate,
1687 cb.category_id, dh.location_id
1688 FROM
1689 fa_books bk,
1690 fa_deprn_summary ds,
1691 FA_ASSET_INVOICES AI_IN,
1692 FA_INVOICE_TRANSACTIONS IT,
1693 FA_TRANSACTION_HEADERS THDIS,
1694 FA_DISTRIBUTION_HISTORY DH,
1695 FA_ASSET_HISTORY AH,
1696 FA_CATEGORY_BOOKS CB,
1697 FA_LOOKUPS FALU,
1698 PO_VENDORS PO_VEND,
1699 FA_ADDITIONS AD,
1700 --GL_CODE_COMBINATIONS DHCC,
1701 FA_TRANSACTION_HEADERS TH,
1702 FA_DEPRN_PERIODS DP
1703 WHERE
1704 DP.BOOK_TYPE_CODE = h_book AND
1705 dp.period_open_date >= bk.date_effective --AND
1706 -- dp.period_close_date <= nvl(bk.date_ineffective,sysdate)
1707 AND
1708 ds.asset_id = bk.asset_id and
1709 ds.book_type_code = bk.book_type_code and
1710 -- bug fix 3807732
1711 ds.deprn_source_code = 'BOOKS' and
1712 (ds.period_counter + 1) = dp.period_counter
1713 AND
1714 bk.asset_id = th.asset_id and
1715 bk.book_type_code = th.book_type_code and
1716 bk.date_placed_in_service >= begin_dpis AND
1717 bk.date_placed_in_service <= end_dpis AND
1718 bk.transaction_header_id_in = th.transaction_header_id
1719 AND
1720 TH.BOOK_TYPE_CODE = h_book AND
1721 TH.TRANSACTION_TYPE_CODE = 'ADDITION'
1722 AND
1723 THDIS.TRANSACTION_TYPE_CODE = 'TRANSFER IN' AND
1724 THDIS.BOOK_TYPE_CODE = h_book AND
1725 THDIS.ASSET_ID = TH.ASSET_ID AND
1726 THDIS.DATE_EFFECTIVE < th.date_effective
1727 AND
1728 DH.BOOK_TYPE_CODE = h_book AND
1729 DH.ASSET_ID = TH.ASSET_ID AND
1730 --DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID AND
1731 DH.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
1732 NVL(DH.DATE_INEFFECTIVE, SYSDATE) > TH.DATE_EFFECTIVE
1733 AND
1734 CB.CATEGORY_ID = AH.CATEGORY_ID AND
1735 CB.BOOK_TYPE_CODE = h_book
1736 AND
1737 AD.ASSET_ID = TH.ASSET_ID
1738 AND
1739 AH.ASSET_ID = TH.ASSET_ID AND
1740 AH.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
1741 NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > TH.DATE_EFFECTIVE
1742 AND
1743 AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
1744 FALU.LOOKUP_TYPE = 'ASSET TYPE'
1745 AND
1746 IT.INVOICE_TRANSACTION_ID = AI_IN.INVOICE_TRANSACTION_ID_IN
1747 AND
1748 AI_IN.ASSET_ID = TH.ASSET_ID AND
1749 AI_IN.DATE_EFFECTIVE <= th.date_effective AND
1750 NVL(AI_IN.DATE_INEFFECTIVE, SYSDATE+1) > th.date_effective AND
1751 AI_IN.DELETED_FLAG = 'NO'
1752 AND
1753 PO_VEND.VENDOR_ID(+) = AI_IN.PO_VENDOR_ID;
1754 --GROUP BY
1755 -- DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1756 -- dh.code_combination_id,
1757 -- FALU.MEANING,
1758 -- AH.ASSET_TYPE,
1759 -- DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1760 -- CB.ASSET_COST_ACCT),
1761 -- AD.ASSET_NUMBER,
1762 -- AD.description,
1763 -- PO_VEND.segment1,
1764 -- AI_IN.INVOICE_NUMBER,
1765 -- AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
1766 -- AI_IN.DESCRIPTION,
1767 -- AI_IN.PAYABLES_COST ,
1768 -- DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
1769 -- 'INVOICE ADJUSTMENT','A',
1770 -- 'INVOICE TRANSFER','T',
1771 -- 'INVOICE REINSTATE','R',NULL),
1772 -- bk.date_placed_in_service,
1773 -- bk.deprn_method_code,
1774 -- bk.life_in_months,
1775 -- bk.production_capacity,
1776 -- bk.adjusted_rate,
1777 -- cb.deprn_reserve_acct,
1778 -- ds.bonus_Rate,
1779 -- cb.category_id, dh.location_id;
1780
1781
1782
1783 begin
1784
1785 h_book := book;
1786 ctr := 0;
1787 h_request_id := request_id;
1788
1789 select fcr.last_update_login into h_login_id
1790 from fnd_concurrent_requests fcr
1791 where fcr.request_id = h_request_id;
1792
1793 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
1794
1798 where book_type_code = h_book;
1795 select nvl(distribution_source_book, book_type_code), accounting_flex_structure
1796 into h_dist_source_book, h_acct_flex_struct
1797 from fa_book_controls
1799
1800 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
1801
1802 select location_flex_structure, category_flex_structure
1803 into h_loc_flex_struct, h_cat_flex_struct
1804 from fa_system_controls;
1805
1806 h_mesg_name := 'FA_RX_SEGNUMS';
1807
1808 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
1809 BOOK => h_book,
1810 BALANCING_SEGNUM => h_bal_seg,
1811 ACCOUNT_SEGNUM => h_acct_seg,
1812 CC_SEGNUM => h_cost_seg,
1813 CALLING_FN => 'ADD_BY_PERIOD');
1814
1815
1816 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
1817
1818 open additions;
1819 loop
1820 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
1821
1822 fetch additions into
1823 h_source,
1824 h_ccid,
1825 h_asset_type_mean,
1826 h_asset_type,
1827 h_cost_acct,
1828 h_asset_number,
1829 h_description,
1830 h_tag_number, h_serial_number, h_inventorial,
1831 h_vendor_number,
1832 h_invoice_number,
1833 h_line_number,
1834 h_invoice_descr,
1835 h_invoice_orig_cost,
1836 h_invoice_cost,
1837 h_cost_to_clear,
1838 h_invoice_flag,
1839 h_dpis,
1840 h_method,
1841 h_life_months,
1842 h_prod_capacity,
1843 h_adjusted_Rate,
1844 h_reserve_acct,
1845 h_bonus_rate,
1846 h_category_id,
1847 h_location_id;
1848
1849
1850 if (additions%NOTFOUND) then exit; end if;
1851 ctr := ctr + 1;
1852
1853 mesg := 'concat_account';
1854
1855 h_mesg_name := 'FA_RX_CONCAT_SEGS';
1856 h_flex_error := 'GL#';
1857 h_ccid_error := h_ccid;
1858
1859 fa_rx_shared_pkg.concat_acct (
1860 struct_id => h_acct_flex_struct,
1861 ccid => h_ccid,
1862 concat_string => h_concat_acct,
1863 segarray => h_acct_segs);
1864
1865 mesg := 'concat_category';
1866
1867 h_flex_error := 'CAT#';
1868 h_ccid_error := h_category_id;
1869
1870 fa_rx_shared_pkg.concat_category (
1871 struct_id => h_cat_flex_struct,
1872 ccid => h_category_id,
1873 concat_string => h_concat_cat,
1874 segarray => h_cat_segs);
1875
1876 mesg := 'concat_location';
1877
1878 h_flex_error := 'LOC#';
1879 h_ccid_error := h_location_id;
1880
1881 fa_rx_shared_pkg.concat_location (
1882 struct_id => h_loc_flex_struct,
1883 ccid => h_location_id,
1884 concat_string => h_concat_loc,
1885 segarray => h_loc_segs);
1886
1887 select decode(h_life_months, null, null,
1888 to_char(floor(h_life_months/12)) || '.' ||
1889 to_char(mod(h_life_months,12)))
1890 into h_life_year_month
1891 from dual;
1892
1893 h_life_year_month_num := fnd_number.canonical_to_number(h_life_year_month);
1894
1895 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
1896
1897 insert into fa_addition_rep_itf (
1898 request_id, source, company, cost_Center, expense_acct,
1899 asset_type, asset_number, description,
1900 tag_number, serial_number, inventorial, vendor_number,
1901 invoice_number, line_number, invoice_descr,
1902 invoice_orig_cost, invoice_cost, cost_to_clear,
1903 invoice_flag, date_placed_in_service, method,
1904 life_year_month, prod_capacity, adjusted_rate,
1905 reserve_acct, cost_acct, category, location,
1906 last_update_date, creation_date, last_updated_by,
1907 last_update_login, created_by) values (
1908 request_id, h_source, h_acct_segs(h_bal_seg),
1909 h_acct_segs(h_cost_seg), h_acct_segs(h_acct_seg),
1910 h_asset_type_mean, h_asset_number, h_description,
1911 h_tag_number, h_serial_number, h_inventorial,
1912 h_vendor_number, h_invoice_number, h_line_number,
1913 h_invoice_descr, h_invoice_orig_cost, h_invoice_cost,
1914 h_cost_to_clear, h_invoice_flag, h_dpis,
1915 h_method, h_life_year_month_num, h_prod_capacity,
1916 h_adjusted_rate, h_reserve_acct, h_cost_acct,
1917 h_concat_cat, h_concat_loc, sysdate, sysdate,
1918 user_id, h_login_id, user_id);
1919
1920
1921
1922
1923 end loop;
1924
1925 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
1926
1927 close additions;
1928
1929 exception when others then
1930 if SQLCODE <> 0 then
1931 fa_Rx_conc_mesg_pkg.log(SQLERRM);
1932 end if;
1933 fnd_message.set_name('OFA',h_mesg_name);
1934 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
1935 fnd_message.set_token('TABLE','FA_ADDITION_REP_ITF',FALSE);
1936 end if;
1937 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
1938 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
1939 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
1940 end if;
1941
1942 h_mesg_str := fnd_message.get;
1943 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1944 retcode := 2;
1945
1946 end add_by_date;
1947
1948
1949
1950 procedure add_by_resp (
1951 book in varchar2,
1952 period in varchar2,
1953 begin_cc in varchar2,
1954 end_cc in varchar2,
1955 request_id in number,
1956 user_id in number,
1957 retcode out nocopy number,
1958 errbuf out nocopy varchar2) is
1959
1963 h_book varchar2(15);
1960 mesg varchar2(200);
1961 ctr number;
1962
1964 h_request_id number;
1965 h_login_id number;
1966
1967 h_period1_pc number;
1968 h_period2_pc number;
1969 h_period1_pod date;
1970 h_period2_pcd date;
1971
1972 h_bonus_rate number;
1973 h_reserve_acct varchar2(25);
1974 h_adjusted_Rate number;
1975 h_prod_capacity number;
1976 h_life_months number;
1977 h_life_year_month varchar2(10);
1978 h_life_year_month_num number;
1979 h_method varchar2(15);
1980 h_dpis date;
1981 h_invoice_flag varchar2(1);
1982 h_cost_to_clear number;
1983 h_invoice_cost number;
1984 h_invoice_orig_cost number;
1985 h_invoice_descr varchar2(80);
1986 h_line_number number;
1987 h_invoice_number varchar2(50);
1988 h_vendor_number varchar2(30);
1989 h_description varchar2(80);
1990 h_tag_number varchar2(15);
1991 h_serial_number varchar2(35);
1992 h_inventorial varchar2(3);
1993 h_asset_number varchar2(15);
1994 h_asset_type varchar2(15);
1995 h_cost_acct varchar2(25);
1996 h_asset_type_mean varchar2(80);
1997 h_ccid number;
1998 h_source varchar2(20);
1999 h_emp_name varchar2(240);
2000 h_emp_number varchar2(30);
2001 h_units number;
2002 h_period_name varchar2(15);
2003 h_reserve number;
2004
2005 h_category_id number;
2006 h_location_id number;
2007
2008 h_concat_acct varchar2(200);
2009 h_concat_cat varchar2(200);
2010 h_concat_loc varchar2(200);
2011 h_acct_segs fa_rx_shared_pkg.Seg_Array;
2012 h_cat_segs fa_rx_shared_pkg.Seg_Array;
2013 h_loc_segs fa_rx_shared_pkg.Seg_Array;
2014
2015 h_acct_seg number;
2016 h_cost_seg number;
2017 h_bal_seg number;
2018
2019 h_dist_source_book varchar2(15);
2020
2021 h_acct_flex_struct number;
2022 h_cat_flex_struct number;
2023 h_loc_flex_struct number;
2024
2025
2026 h_major_category varchar2(50);
2027 h_minor_category varchar2(50);
2028
2029 maj_select_column varchar2(50);
2030 min_select_column varchar2(50);
2031 sql_stmt varchar2(1000);
2032
2033 TYPE cur IS ref cursor;
2034 category_segments cur;
2035
2036 h_major_cat_desc varchar2(200);
2037 h_minor_cat_desc varchar2(200);
2038
2039 h_count number;
2040
2041 h_mesg_name varchar2(50);
2042 h_mesg_str varchar2(2000);
2043 h_flex_error varchar2(5);
2044 h_ccid_error number;
2045
2046 -- added NVL for all h_period2_pcd in the cursor resp_additions
2047 -- SLA
2048 cursor resp_additions is SELECT
2049 cc.code_combination_id,
2050 EMP.FULL_NAME, emp.employee_number,
2051 loc.location_id,
2052 ah.category_id,
2053 AD.ASSET_NUMBER,
2054 AD.DESCRIPTION,
2055 DH.UNITS_ASSIGNED,
2056 AD.SERIAL_NUMBER,
2057 AD.TAG_NUMBER, ad.inventorial,
2058 BOOKS.LIFE_IN_MONTHS,
2059 BOOKS.ADJUSTED_RATE,
2060 BOOKS.PRODUCTION_CAPACITY ,
2061 NVL(DS.BONUS_RATE,0),
2062 nvl(DD.ADDITION_COST_TO_CLEAR, 0),
2063 nvl(DD.DEPRN_RESERVE,0),
2064 NULL,
2065 period --dp.period_name
2066 FROM
2067 FA_TRANSACTION_HEADERS TH,
2068 PER_ALL_PEOPLE_F EMP,
2069 FA_LOCATIONS LOC,
2070 GL_CODE_COMBINATIONS CC,
2071 FA_ADDITIONS AD,
2072 FA_ASSET_HISTORY AH,
2073 FA_DISTRIBUTION_HISTORY DH,
2074 FA_BOOKS BOOKS,
2075 FA_DEPRN_SUMMARY DS,
2076 FA_DEPRN_DETAIL DD
2077 --FA_DEPRN_PERIODS DP
2078 WHERE
2079 TH.DATE_EFFECTIVE >= h_period1_pod AND
2080 TH.DATE_EFFECTIVE <= nvl(h_period2_pcd ,sysdate) AND
2081 TH.BOOK_TYPE_CODE = h_book AND
2082 TH.TRANSACTION_TYPE_CODE = 'TRANSFER IN'
2083 AND
2084 DH.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID AND
2085 --nvl(DH.DATE_INEFFECTIVE, nvl(h_period2_pcd ,sysdate)+1) > nvl(h_period2_pcd ,sysdate) AND /* SLA */
2086 DH.BOOK_TYPE_CODE = h_book AND
2087 DH.ASSET_ID = TH.ASSET_ID
2088 AND
2089 TH.ASSET_ID = AH.ASSET_ID AND
2090 TH.date_effective between ah.date_effective and nvl(ah.date_ineffective,sysdate)
2091 AND
2092 BOOKS.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
2093 nvl(BOOKS.DATE_INEFFECTIVE,SYSDATE) > TH.DATE_EFFECTIVE AND
2094 BOOKS.ASSET_ID = TH.ASSET_ID AND
2095 BOOKS.BOOK_TYPE_CODE = h_book
2096 AND
2097 DD.BOOK_TYPE_CODE = h_book AND
2098 DD.ASSET_ID = TH.ASSET_ID AND
2099 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
2100 DD.DEPRN_SOURCE_CODE = 'B' AND
2101 DD.PERIOD_COUNTER >= h_period1_pc - 1 AND
2102 dd.period_counter <= h_period2_pc - 1
2103 AND
2104 CC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
2105 AND
2106 AD.ASSET_ID = TH.ASSET_ID
2107 AND
2108 EMP.PERSON_ID(+) = DH.ASSIGNED_TO
2109 AND
2113 /* AND
2110 TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE(+) AND EFFECTIVE_END_DATE(+)
2111 AND
2112 LOC.LOCATION_ID = DH.LOCATION_ID
2114 dp.period_counter = ds.period_counter and
2115 dp.book_type_code = ds.book_type_code */ --SLA
2116 AND
2117 DS.ASSET_ID (+) = BOOKS.ASSET_ID AND
2118 DS.BOOK_TYPE_CODE (+) = h_book AND
2119 DS.PERIOD_COUNTER (+) >= h_period1_pc AND
2120 DS.period_counter(+) <= h_period2_pc
2121
2122 UNION
2123 SELECT
2124 cc.code_combination_id,
2125 EMP.FULL_NAME, emp.employee_number,
2126 loc.location_id,
2127 ah.category_id,
2128 AD.ASSET_NUMBER ,
2129 AD.DESCRIPTION ,
2130 DH.UNITS_ASSIGNED ,
2131 AD.SERIAL_NUMBER,
2132 AD.TAG_NUMBER , ad.inventorial,
2133 BOOKS.LIFE_IN_MONTHS,
2134 BOOKS.ADJUSTED_RATE,
2135 BOOKS.PRODUCTION_CAPACITY,
2136 NVL(DS.BONUS_RATE,0),
2137 sum(CADJ.ADJUSTMENT_AMOUNT *
2138 DECODE(CADJ.DEBIT_CREDIT_FLAG,'CR',-1,'DR',1)),
2139 0 , -- RESERVE,
2140 'T',
2141 period --dp.period_name
2142 FROM
2143 FA_TRANSACTION_HEADERS TH,
2144 PER_ALL_PEOPLE_F EMP,
2145 FA_LOCATIONS LOC,
2146 GL_CODE_COMBINATIONS CC,
2147 FA_ADDITIONS AD,
2148 FA_ASSET_HISTORY AH,
2149 FA_DISTRIBUTION_HISTORY DH,
2150 FA_BOOKS BOOKS,
2151 FA_DEPRN_SUMMARY DS,
2152 FA_ADJUSTMENTS CADJ
2153 --fa_deprn_periods dp
2154 WHERE
2155 TH.DATE_EFFECTIVE >= h_period1_pod AND
2156 TH.DATE_EFFECTIVE <= nvl(h_period2_pcd ,sysdate) AND
2157 TH.BOOK_TYPE_CODE = h_book AND
2158 TH.TRANSACTION_TYPE_CODE = 'TRANSFER'
2159 AND
2160 DH.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID AND
2161 --nvl(DH.DATE_INEFFECTIVE, nvl(h_period2_pcd ,sysdate)+1) > nvl(h_period2_pcd ,sysdate) AND
2162 DH.BOOK_TYPE_CODE = h_book AND
2163 DH.ASSET_ID = TH.ASSET_ID
2164 AND
2165 TH.ASSET_ID = AH.ASSET_ID AND
2166 TH.date_effective between ah.date_effective and nvl(ah.date_ineffective,sysdate)
2167 AND
2168 BOOKS.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
2169 nvl(BOOKS.DATE_INEFFECTIVE, SYSDATE) > TH.DATE_EFFECTIVE AND
2170 BOOKS.ASSET_ID = TH.ASSET_ID AND
2171 BOOKS.BOOK_TYPE_CODE = h_book
2172 AND
2173 CC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID AND
2174 CADJ.BOOK_TYPE_CODE = H_BOOK AND
2175 CADJ.ASSET_ID = TH.ASSET_ID AND
2176 CADJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
2177 CADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND
2178 CADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND
2179 CADJ.PERIOD_COUNTER_CREATED >= h_period1_pc AND
2180 cadj.period_counter_created <= h_period2_pc and
2181 CADJ.ADJUSTMENT_TYPE in ('COST','CIP COST')
2182 AND
2183 AD.ASSET_ID = TH.ASSET_ID
2184 AND
2185 EMP.PERSON_ID(+) = DH.ASSIGNED_TO
2186 AND
2187 TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+) AND EMP.EFFECTIVE_END_DATE(+)
2188 AND
2189 LOC.LOCATION_ID = DH.LOCATION_ID
2190 /*AND
2191 dp.period_counter = ds.period_counter and
2192 dp.book_type_code = ds.book_type_code*/
2193 AND
2194 DS.ASSET_ID (+) = BOOKS.ASSET_ID AND
2195 DS.BOOK_TYPE_CODE (+) = h_book AND
2196 DS.PERIOD_COUNTER (+) >= h_period1_pc and
2197 ds.period_counter(+) <= h_period2_pc
2198 GROUP BY
2199 cc.code_combination_id,
2200 EMP.FULL_NAME, emp.employee_number,
2201 loc.location_id,
2202 ah.category_id,
2203 AD.DESCRIPTION,
2204 DH.UNITS_ASSIGNED,
2205 AD.SERIAL_NUMBER,
2206 AD.TAG_NUMBER, ad.inventorial,
2207 AD.ASSET_NUMBER,
2208 BOOKS.LIFE_IN_MONTHS,
2209 BOOKS.ADJUSTED_RATE,
2210 BOOKS.PRODUCTION_CAPACITY,
2211 DS.BONUS_RATE,
2212 period --dp.period_name
2213 UNION
2214 SELECT
2215 cc.code_combination_id,
2216 EMP.FULL_NAME, emp.employee_number,
2217 loc.location_id,
2218 ah.category_id,
2219 AD.ASSET_NUMBER,
2220 AD.DESCRIPTION ,
2221 DH.UNITS_ASSIGNED,
2222 AD.SERIAL_NUMBER,
2223 AD.TAG_NUMBER , ad.inventorial,
2224 BOOKS.LIFE_IN_MONTHS,
2225 BOOKS.ADJUSTED_RATE,
2226 BOOKS.PRODUCTION_CAPACITY,
2227 NVL(DS.BONUS_RATE,0),
2228 0, -- COST,
2229 sum(RADJ.ADJUSTMENT_AMOUNT *
2230 DECODE(RADJ.DEBIT_CREDIT_FLAG,'CR',1,'DR',-1)),
2231 'T',
2232 period --dp.period_name
2233 FROM
2234 FA_TRANSACTION_HEADERS TH,
2235 PER_ALL_PEOPLE_F EMP,
2236 FA_LOCATIONS LOC,
2237 GL_CODE_COMBINATIONS CC,
2238 FA_ADDITIONS AD,
2239 FA_ASSET_HISTORY AH,
2240 FA_DISTRIBUTION_HISTORY DH,
2241 FA_DEPRN_SUMMARY DS,
2242 FA_BOOKS BOOKS,
2246 TH.DATE_EFFECTIVE >= h_period1_pod AND
2243 FA_ADJUSTMENTS RADJ
2244 --fa_deprn_periods dp
2245 WHERE
2247 TH.DATE_EFFECTIVE <= nvl(h_period2_pcd ,sysdate) AND
2248 TH.BOOK_TYPE_CODE = h_book AND
2249 TH.TRANSACTION_TYPE_CODE = 'TRANSFER'
2250 AND
2251 DH.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID AND
2252 --nvl(DH.DATE_INEFFECTIVE, nvl(h_period2_pcd ,sysdate)+1) > nvl(h_period2_pcd ,sysdate) AND
2253 DH.BOOK_TYPE_CODE = h_book AND
2254 DH.ASSET_ID = TH.ASSET_ID
2255 AND
2256 TH.ASSET_ID = AH.ASSET_ID AND
2257 TH.date_effective between ah.date_effective and nvl(ah.date_ineffective,sysdate)
2258 AND
2259 BOOKS.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
2260 nvl(BOOKS.DATE_INEFFECTIVE, SYSDATE) > TH.DATE_EFFECTIVE AND
2261 BOOKS.ASSET_ID = TH.ASSET_ID AND
2262 BOOKS.BOOK_TYPE_CODE = h_book
2263 AND
2264 CC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID AND
2265 RADJ.BOOK_TYPE_CODE = H_BOOK AND
2266 RADJ.ASSET_ID = TH.ASSET_ID AND
2267 RADJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
2268 RADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND
2269 RADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND
2270 RADJ.PERIOD_COUNTER_CREATED >= h_period1_pc AND
2271 radj.period_counter_created <= h_period2_pc and
2272 RADJ.ADJUSTMENT_TYPE = 'RESERVE'
2273 AND
2274 AD.ASSET_ID = TH.ASSET_ID
2275 AND
2276 EMP.PERSON_ID(+) = DH.ASSIGNED_TO
2277 AND
2278 TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+) AND EMP.EFFECTIVE_END_DATE(+)
2279 AND
2280 LOC.LOCATION_ID = DH.LOCATION_ID
2281 /*AND
2282 dp.period_counter = ds.period_counter and
2283 dp.book_type_code = ds.book_type_code*/
2284 AND
2285 DS.ASSET_ID (+) = BOOKS.ASSET_ID AND
2286 DS.BOOK_TYPE_CODE (+) = h_book AND
2287 DS.PERIOD_COUNTER (+) >= h_period1_pc and
2288 ds.period_counter(+) <= h_period2_pc
2289 GROUP BY
2290 cc.code_combination_id,
2291 EMP.FULL_NAME, emp.employee_number,
2292 loc.location_id,
2293 ah.category_id,
2294 AD.DESCRIPTION,
2295 DH.UNITS_ASSIGNED,
2296 AD.SERIAL_NUMBER,
2297 AD.TAG_NUMBER, ad.inventorial,
2298 AD.ASSET_NUMBER,
2299 BOOKS.LIFE_IN_MONTHS,
2300 BOOKS.ADJUSTED_RATE,
2301 BOOKS.PRODUCTION_CAPACITY,
2302 DS.BONUS_RATE,
2303 period; --dp.period_name;
2304
2305 begin
2306
2307 h_book := book;
2308 h_request_id := request_id;
2309 ctr := 0;
2310
2311 select fcr.last_update_login into h_login_id
2312 from fnd_concurrent_requests fcr
2313 where fcr.request_id = h_request_id;
2314
2315 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
2316
2317 select nvl(distribution_source_book, book_type_code), accounting_flex_structure
2318 into h_dist_source_book, h_acct_flex_struct
2319 from fa_book_controls
2320 where book_type_code = h_book;
2321
2322 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
2323
2324 select location_flex_structure, category_flex_structure
2325 into h_loc_flex_struct, h_cat_flex_struct
2326 from fa_system_controls;
2327
2328 h_mesg_name := 'FA_RX_SEGNUMS';
2329
2330 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
2331 BOOK => h_book,
2332 BALANCING_SEGNUM => h_bal_seg,
2333 ACCOUNT_SEGNUM => h_acct_seg,
2334 CC_SEGNUM => h_cost_seg,
2335 CALLING_FN => 'ADD_BY_PERIOD');
2336
2337 h_mesg_name := 'FA_AMT_SEL_PERIODS';
2338
2339 select period_counter, period_open_date
2340 into h_period1_pc, h_period1_pod
2341 from fa_deprn_periods
2342 where book_type_code = h_book and period_name = period;
2343
2344 select count(*) into h_count
2345 from fa_deprn_periods where period_name = period
2346 and book_type_code = h_book;
2347
2348 if (h_count > 0) then
2349 select period_counter, nvl(period_close_date,sysdate)
2350 into h_period2_pc, h_period2_pcd
2351 from fa_deprn_periods
2352 where book_type_code = h_book and period_name = period;
2353 else
2354 h_period2_pc := null;
2355 h_period2_pcd := null;
2356 end if;
2357
2358
2359 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
2360
2361 open resp_additions;
2362 loop
2363
2364 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
2365
2366 --SLA
2367
2368 --dev_debug(h_book);
2369 --dev_debug(to_char(h_period1_pc));
2370 --dev_debug(to_char(h_period2_pc));
2371 --dev_debug(to_char(h_period1_pod));
2372 --dev_debug(to_char(h_period2_pcd));
2373
2374 fetch resp_additions into
2375 h_ccid,
2376 h_emp_name, h_emp_number,
2377 h_location_id,
2378 h_category_id,
2379 h_asset_number,
2380 h_description,
2381 h_units,
2382 h_serial_number, h_tag_number, h_inventorial,
2383 h_life_months,
2384 h_adjusted_rate,
2385 h_prod_capacity,
2389 h_invoice_flag,
2386 h_bonus_rate,
2387 h_cost_to_clear,
2388 h_reserve,
2390 h_period_name;
2391
2392
2393 if (resp_additions%NOTFOUND) then
2394 --dev_debug('test1');
2395 exit; end if;
2396 --dev_debug('test2');
2397 ctr := ctr + 1;
2398
2399 mesg := 'concat_account';
2400
2401 h_mesg_name := 'FA_RX_CONCAT_SEGS';
2402 h_flex_error := 'GL#';
2403 h_ccid_error := h_ccid;
2404 --dev_debug('test3');
2405 fa_rx_shared_pkg.concat_acct (
2406 struct_id => h_acct_flex_struct,
2407 ccid => h_ccid,
2408 concat_string => h_concat_acct,
2409 segarray => h_acct_segs);
2410 --dev_debug('test4');
2411 --dev_debug(to_char(h_cost_seg));
2412 --dev_debug(to_char(begin_cc));
2413 --dev_debug(to_char(end_cc));
2414 if (h_acct_segs(h_cost_seg) >= begin_cc
2415 and h_acct_segs(h_cost_seg) <= end_cc) then
2416
2417 mesg := 'concat_location';
2418 --dev_debug('test5');
2419 h_flex_error := 'LOC#';
2420 h_ccid_error := h_location_id;
2421
2422 fa_rx_shared_pkg.concat_location (
2423 struct_id => h_loc_flex_struct,
2424 ccid => h_location_id,
2425 concat_string => h_concat_loc,
2426 segarray => h_loc_segs);
2427 --dev_debug('test6');
2428 select decode(h_life_months, null, null,
2429 to_char(floor(h_life_months/12)) || '.' ||
2430 to_char(mod(h_life_months,12)))
2431 into h_life_year_month
2432 from dual;
2433
2434 h_life_year_month_num := fnd_number.canonical_to_number(h_life_year_month);
2435
2436
2437
2438 mesg := 'concat_category';
2439
2440 h_flex_error := 'CAT#';
2441 h_ccid_error := h_category_id;
2442
2443 fa_rx_shared_pkg.concat_category (
2444 struct_id => h_cat_flex_struct,
2445 ccid => h_category_id,
2446 concat_string => h_concat_cat,
2447 segarray => h_cat_segs);
2448
2449
2450
2451 -- dynamic sql for major and minor category.
2452
2453 maj_select_column := null;
2454 min_select_column := null;
2455
2456 maj_select_column := fa_rx_flex_pkg.flex_sql(140,'CAT#',h_cat_flex_struct,'CAT','SELECT','BASED_CATEGORY');
2457 maj_select_column := maj_select_column || ' MAJOR_CATEGORY';
2458
2459 begin
2460 min_select_column := fa_rx_flex_pkg.flex_sql(140,'CAT#',h_cat_flex_struct,'CAT','SELECT','MINOR_CATEGORY');
2461 min_select_column := min_select_column || ' MINOR_CATEGORY';
2462 exception
2463 when others then
2464 min_select_column := 'NULL';
2465 end;
2466
2467 sql_stmt := 'select ' || maj_select_column || ' , ' || min_select_column ||
2468 ' from fa_categories cat where category_id = ' || h_category_id ;
2469
2470
2471 OPEN category_segments FOR sql_stmt;
2472 FETCH category_segments INTO
2473 h_major_category,
2474 h_minor_category;
2475 CLOSE category_segments;
2476
2477 --
2478
2479 mesg := 'getting_major_category_desc';
2480
2481 h_major_cat_desc :=
2482 fa_rx_flex_pkg.get_description(
2483 p_application_id => 140,
2484 p_id_flex_code => 'CAT#',
2485 p_id_flex_num => h_cat_flex_struct,
2486 p_qualifier => 'BASED_CATEGORY',
2487 p_data => h_major_category);
2488
2489
2490 mesg := 'getting_minor_category_desc';
2491 h_minor_cat_desc :=
2492 fa_rx_flex_pkg.get_description(
2493 p_application_id => 140,
2494 p_id_flex_code => 'CAT#',
2495 p_id_flex_num => h_cat_flex_struct,
2496 p_qualifier => 'MINOR_CATEGORY',
2497 p_data => h_minor_category);
2498
2499
2500
2501 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
2502 --dev_debug('test7');
2503 --dev_debug(to_char(request_id));
2504 insert into fa_addition_rep_itf (
2505 request_id, company, cost_Center, expense_acct,
2506 asset_number, description, reserve,
2507 cost_to_clear, invoice_flag,
2508 life_year_month, prod_capacity, adjusted_rate,
2509 employee_name, employee_number, location,
2510 serial_number, tag_number, inventorial, period_name,
2511 last_update_date, creation_date, last_updated_by,
2512 last_update_login, created_by,
2513 category,
2514 major_category,
2515 minor_category,
2516 major_category_desc,
2517 minor_category_desc
2518 ) values (
2519 request_id, h_acct_segs(h_bal_seg),
2520 h_acct_segs(h_cost_seg), h_acct_segs(h_acct_seg),
2521 h_asset_number, h_description, h_reserve, h_cost_to_clear,
2522 h_invoice_flag, h_life_year_month_num, h_prod_capacity,
2523 h_adjusted_rate, h_emp_name, h_emp_number,
2524 h_concat_loc, h_serial_number, h_tag_number, h_inventorial,
2525 h_period_name, sysdate, sysdate,
2526 user_id, h_login_id, user_id,
2527 h_concat_cat,
2528 h_major_category,
2529 h_minor_category,
2530 h_major_cat_desc,
2531 h_minor_cat_desc);
2532
2533 end if; -- if cc between...
2534
2535 end loop;
2536
2537
2538 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
2539
2540 close resp_additions;
2541
2542 exception when others then
2543 if SQLCODE <> 0 then
2544 fa_Rx_conc_mesg_pkg.log(SQLERRM);
2545 end if;
2546 fnd_message.set_name('OFA',h_mesg_name);
2547 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
2548 fnd_message.set_token('TABLE','FA_ADDITION_REP_ITF',FALSE);
2549 end if;
2550 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
2551 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
2552 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
2553 end if;
2554
2555 h_mesg_str := fnd_message.get;
2556 fa_rx_conc_mesg_pkg.log(h_mesg_str);
2557 retcode := 2;
2558
2559 end add_by_resp;
2560
2561
2562 END FARX_AD;