[Home] [Help]
Skip to content
PACKAGE BODY: APPS.FARX_AD
Source
1 PACKAGE BODY FARX_AD as
2 /* $Header: farxadb.pls 120.29 2011/11/18 10:47:01 saalampa 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
409 l_param_where := l_param_where || ' AND AD.ASSET_NUMBER <= '''
410 || to_asset_num || '''';
411 END IF;
412 */
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) - Commented for bug 11674659 */
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
516 FALU.LOOKUP_TYPE = ''ASSET TYPE''
517 AND
518 IT.INVOICE_TRANSACTION_ID = AI_IN.INVOICE_TRANSACTION_ID_IN
519 AND AI_IN.ASSET_ID (+) = TH.ASSET_ID
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
562 -- Bug 5222214 Added OR condition on DP.PERIOD_COUNTER so that
563 -- capitalized assets in the current open period are selected from deprn_periods.
564
565 where_clause4 := '
566 DS.BOOK_TYPE_CODE = :h_book AND
567 DS.ASSET_ID = th.asset_id and
568 DS.DEPRN_SOURCE_CODE = ''DEPRN'' AND
569 DS.PERIOD_COUNTER = DD.PERIOD_COUNTER
570 AND DH.BOOK_TYPE_CODE = :h_dist_source_book AND
571 DH.ASSET_ID = dd.ASSET_ID AND
572 DH.DISTRIBUTION_ID =
573 decode(th.asset_id, null,DD.DISTRIBUTION_ID, DD.DISTRIBUTION_ID) AND
574 DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
575 AND DD.BOOK_TYPE_CODE = :h_book AND
576 DD.ASSET_ID = TH.ASSET_ID AND
577 DD.PERIOD_COUNTER =
578 ( select max(DD1.PERIOD_COUNTER)
579 from FA_DEPRN_DETAIL DD1, FA_DEPRN_PERIODS DP2
580 where dd1.period_counter <= dp2.period_counter
581 and DD1.ASSET_ID = DD.ASSET_ID
582 and DD1.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
583 and DP2.BOOK_TYPE_CODE = DD1.BOOK_TYPE_CODE
584 and DD1.PERIOD_COUNTER >= :h_period1_pc
585 and DP2.PERIOD_COUNTER >= :h_period1_pc
586 and DP2.PERIOD_COUNTER <= :h_period2_pc )
587 AND TH.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND
588 TH.DATE_EFFECTIVE < nvl(DP.PERIOD_CLOSE_DATE,th.date_effective+1) AND
589 TH.BOOK_TYPE_CODE = :h_book AND
590 th.asset_id = dd.asset_id and
591 TH.TRANSACTION_TYPE_CODE = ''ADDITION''
592 AND THDIS.TRANSACTION_TYPE_CODE = ''TRANSFER IN'' AND
593 THDIS.BOOK_TYPE_CODE = :h_book AND
594 THDIS.ASSET_ID = TH.ASSET_ID AND
595 THDIS.DATE_EFFECTIVE < DP.PERIOD_OPEN_DATE
596 AND BK.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
597 AND DP.BOOK_TYPE_CODE =
598 decode(th.asset_id, null,dd.BOOK_TYPE_CODE,dd.BOOK_TYPE_CODE )
599 and ( (DP.PERIOD_COUNTER = dd.PERIOD_COUNTER) OR
600 (DP.PERIOD_COUNTER >= :h_period1_pc AND
601 DP.PERIOD_COUNTER <= :h_period2_pc AND
602 DP.PERIOD_CLOSE_DATE IS NULL AND
603 DP.DEPRN_RUN IS NULL))
604 AND AH.ASSET_ID = dd.ASSET_ID AND
605 AH.DATE_EFFECTIVE <= NVL(DP.PERIOD_CLOSE_DATE, ah.date_effective+1) AND
606 NVL(AH.DATE_INEFFECTIVE, SYSDATE+1) >
607 NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
608 AND AD.ASSET_ID = ah.ASSET_ID
609 AND CB.CATEGORY_ID = AH.CATEGORY_ID AND
610 CB.BOOK_TYPE_CODE = :h_book
611 AND FALU.LOOKUP_TYPE = ''ASSET TYPE''
612 AND AH.ASSET_TYPE = FALU.LOOKUP_CODE
613 AND CB.CATEGORY_ID = CAT.CATEGORY_ID
614 AND GAD.ASSET_ID(+) = BK.GROUP_ASSET_ID';
615
616 IF (l_param_where is not NULL) THEN
617 where_clause1 := where_clause1 || l_param_where;
618 where_clause2 := where_clause2 || l_param_where;
619 where_clause3 := where_clause3 || l_param_where;
620 where_clause4 := where_clause4 || l_param_where;
621 END IF;
622
623 h_mesg_name := 'FA_ADDITION_SQL_DCUR';
624
625 IF (g_print_debug) THEN
626 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'where_clause1:' || where_clause1);
627 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'where_clause2:' || where_clause2);
628 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'where_clause3:' || where_clause3);
629 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'where_clause4:' || where_clause4);
630 END IF;
631
632 -- open additions for
633
634 select_statement := '
635 SELECT DISTINCT
636 DECODE(TH.MASS_REFERENCE_ID,NULL,''Manual Addition'',''Mass Addition''),
637 dhcc.code_combination_id,
638 FALU.MEANING,
639 AH.ASSET_TYPE,
640 DECODE(AH.ASSET_TYPE, ''CIP'', CB.CIP_COST_ACCT,CB.ASSET_COST_ACCT),
641 AD.ASSET_NUMBER,
642 AD.description,
643 ad.tag_number, ad.serial_number, ad.inventorial,
644 ad.asset_key_ccid,
645 PO_VEND.segment1,
646 AI_IN.INVOICE_NUMBER ,
647 AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
648 AI_IN.DESCRIPTION,
649 AI_IN.PAYABLES_COST,
650 DH.UNITS_ASSIGNED/AH.UNITS * AI_IN.FIXED_ASSETS_COST,
651 TO_NUMBER (NULL), -- cost to clear
652 DECODE(IT.TRANSACTION_TYPE,''INVOICE ADDITION'',''M'',
653 ''INVOICE ADJUSTMENT'',''A'',
654 ''INVOICE TRANSFER'',''T'',
655 ''INVOICE REINSTATE'',''R'',NULL),
656 bk.date_placed_in_service,
657 bk.deprn_method_code,
658 bk.life_in_months,
659 bk.production_capacity,
660 bk.adjusted_rate,
661 cb.deprn_reserve_acct,
662 ds.bonus_Rate,
663 cb.category_id, dh.location_id,
664 TO_NUMBER (NULL), --DD.YTD_DEPRN, Bug 7675486/9148511. For invoice lines it is null.
665 TO_NUMBER (NULL), --DD.DEPRN_RESERVE, Bug 7675486/9148511. For invoice lines it is null.
666 TH.TRANSACTION_HEADER_ID,'||
667 maj_select_statement ||','||
668 min_select_statement ||','||
669 spec_select_statement ||' ,
670 gad.asset_number
671 FROM
672 PO_VENDORS PO_VEND,
673 FA_INVOICE_TRANSACTIONS IT,
674 FA_ASSET_INVOICES AI_IN,
675 FA_DEPRN_SUMMARY DS,
676 FA_ADDITIONS AD,
677 GL_CODE_COMBINATIONS DHCC,
678 FA_DISTRIBUTION_HISTORY DH,
679 FA_LOOKUPS FALU,
680 FA_CATEGORY_BOOKS CB,
681 FA_ASSET_HISTORY AH,
682 FA_BOOKS BK,
683 FA_TRANSACTION_HEADERS TH,
684 /* FA_DEPRN_PERIODS DP1,*/
685 FA_DEPRN_PERIODS DP,
686 FA_DEPRN_DETAIL DD,
687 FA_CATEGORIES CAT,
688 FA_ADDITIONS_B GAD
689 WHERE ' || where_clause1 || '
690 UNION ALL
691 SELECT DISTINCT
692 DECODE(TH.MASS_REFERENCE_ID,NULL,''Manual Addition'',''Mass Addition''),
693 dhcc.code_combination_id,
694 FALU.MEANING,
695 AH.ASSET_TYPE ,
696 DECODE(AH.ASSET_TYPE, ''CIP'', CB.CIP_COST_ACCT,
697 CB.ASSET_COST_ACCT),
698 AD.ASSET_NUMBER,
699 AD.description,
700 ad.tag_number, ad.serial_number, ad.inventorial,
701 ad.asset_key_ccid,
702 PO_VEND.segment1,
703 AI_IN.INVOICE_NUMBER ,
704 AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
705 AI_IN.DESCRIPTION ,
706 AI_IN.PAYABLES_COST,
707 DH.UNITS_ASSIGNED/AH.UNITS * AI_IN.FIXED_ASSETS_COST,
708 TO_NUMBER (NULL), -- cost to clear
709 DECODE(IT.TRANSACTION_TYPE,''INVOICE ADDITION'',''M'',
710 ''INVOICE ADJUSTMENT'',''A'',
711 ''INVOICE TRANSFER'',''T'',
712 ''INVOICE REINSTATE'',''R'',NULL),
713 bk.date_placed_in_service,
714 bk.deprn_method_code,
715 bk.life_in_months,
716 bk.production_capacity,
717 bk.adjusted_rate,
718 cb.deprn_reserve_acct,
719 ds.bonus_Rate,
720 cb.category_id, dh.location_id,
721 DD.YTD_DEPRN,
722 DD.DEPRN_RESERVE,
723 TH.TRANSACTION_HEADER_ID,'||
724 maj_select_statement ||','||
725 min_select_statement ||','||
726 spec_select_statement ||' ,
727 gad.asset_number
728 FROM
729 PO_VENDORS PO_VEND,
730 FA_INVOICE_TRANSACTIONS IT,
731 FA_ASSET_INVOICES AI_IN,
732 FA_DISTRIBUTION_HISTORY DH,
733 GL_CODE_COMBINATIONS DHCC,
734 fa_deprn_summary ds,
735 FA_TRANSACTION_HEADERS THDIS,
736 fa_books bk,
737 FA_ADDITIONS AD,
738 FA_CATEGORY_BOOKS CB,
739 FA_LOOKUPS FALU,
740 FA_ASSET_HISTORY AH,
741 FA_TRANSACTION_HEADERS THADD,
742 FA_DEPRN_PERIODS DP2,
743 FA_DEPRN_PERIODS DP1,
744 FA_DEPRN_PERIODS DP,
745 FA_TRANSACTION_HEADERS TH,
746 fa_deprn_detail dd,
747 fa_categories cat,
748 fa_additions_b GAD
749 WHERE ' || where_clause2 || '
750 UNION ALL
751 SELECT DISTINCT
752 DECODE(TH.MASS_REFERENCE_ID,NULL,''Manual Addition'',''Mass Addition''),
753 dhcc.code_combination_id,
754 FALU.MEANING,
755 AH.ASSET_TYPE ,
756 DECODE(AH.ASSET_TYPE, ''CIP'', CB.CIP_COST_ACCT,
757 CB.ASSET_COST_ACCT),
758 AD.ASSET_NUMBER,
759 AD.description,
760 ad.tag_number, ad.serial_number, ad.inventorial,
761 ad.asset_key_ccid,
762 NULL, -- vendor number
763 NULL, -- invoice number
764 TO_NUMBER(NULL), -- line number
765 NULL, -- invoice description
766 TO_NUMBER(NULL), -- invoice original cost
767 TO_NUMBER(NULL), -- invoice cost
768 NVL(DD.ADDITION_COST_TO_CLEAR, 0),
769 NULL, -- invoice flag
770 bk.date_placed_in_service,
771 bk.deprn_method_code,
772 bk.life_in_months,
773 bk.production_capacity,
774 bk.adjusted_rate,
775 cb.deprn_reserve_acct,
776 ds.bonus_Rate,
777 cb.category_id, dh.location_id,
778 DD.YTD_DEPRN,
779 DD.DEPRN_RESERVE,
780 TH.TRANSACTION_HEADER_ID,'||
781 maj_select_statement ||','||
782 min_select_statement ||','||
783 spec_select_statement ||' ,
784 gad.asset_number
785 FROM
786 FA_DEPRN_SUMMARY DS,
787 FA_ADDITIONS AD,
788 GL_CODE_COMBINATIONS DHCC,
789 FA_DISTRIBUTION_HISTORY DH,
790 FA_LOOKUPS FALU,
791 FA_CATEGORY_BOOKS CB,
792 FA_ASSET_HISTORY AH,
793 FA_BOOKS BK,
794 FA_TRANSACTION_HEADERS TH,
795 FA_DEPRN_PERIODS DP,
796 FA_DEPRN_DETAIL DD,
797 fa_categories cat,
798 fa_additions_b GAD
799 WHERE ' || where_clause3 || '
800 UNION ALL
801 SELECT DISTINCT
802 DECODE(TH.MASS_REFERENCE_ID,NULL,''Manual Addition'',''Mass Addition''),
803 dhcc.code_combination_id,
804 FALU.MEANING,
805 AH.ASSET_TYPE ,
806 DECODE(AH.ASSET_TYPE, ''CIP'', CB.CIP_COST_ACCT,
807 CB.ASSET_COST_ACCT),
808 AD.ASSET_NUMBER,
809 AD.description,
810 ad.tag_number, ad.serial_number, ad.inventorial,
811 ad.asset_key_ccid,
812 NULL, -- vendor number
813 NULL, -- invoice number
814 TO_NUMBER(NULL), -- line number
815 NULL, -- invoice description
816 TO_NUMBER(NULL), -- invoice original cost
817 TO_NUMBER(NULL), -- invoice cost
818 bk.cost,
819 NULL, -- invoice flag
820 bk.date_placed_in_service,
821 bk.deprn_method_code,
822 bk.life_in_months,
823 bk.production_capacity,
824 bk.adjusted_rate,
825 cb.deprn_reserve_acct,
826 ds.bonus_Rate,
827 cb.category_id, dh.location_id,
828 DD.YTD_DEPRN,
829 DD.DEPRN_RESERVE,
830 TH.TRANSACTION_HEADER_ID,'||
831 maj_select_statement ||','||
832 min_select_statement ||','||
833 spec_select_statement ||' ,
834 gad.asset_number
835 FROM
836 FA_DISTRIBUTION_HISTORY DH,
837 GL_CODE_COMBINATIONS DHCC,
838 fa_deprn_summary ds,
839 FA_TRANSACTION_HEADERS THDIS,
840 fa_books bk,
841 FA_ADDITIONS AD,
842 FA_CATEGORY_BOOKS CB,
843 FA_LOOKUPS FALU,
844 FA_ASSET_HISTORY AH,
845 FA_DEPRN_PERIODS DP,
846 FA_TRANSACTION_HEADERS TH,
847 fa_deprn_detail dd,
848 fa_categories cat,
849 FA_ADDITIONS_B GAD
850 WHERE ' || where_clause4 ;
851
852 IF (g_print_debug) THEN
853 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'select_statement:='|| select_statement);
854 END IF;
855
856 /* BUG# 2939771
857 open additions for select_statement;
858 -- USING where_clause1,where_clause2,where_clause3,where_clause4;
859 */
860 open additions for select_statement using
861 h_book, -- where_clause1
862 h_dist_source_book,
863 h_book,
864 h_book,
865 h_period1_pc,
866 h_period2_pc,
867 h_book,
868 -- h_period1_pc,
869 -- h_period2_pc,
870 -- h_book,
871 h_book,
872 from_maj_cat, -- l_param_where
873 from_maj_cat,
874 to_maj_cat,
875 to_maj_cat,
876 from_min_cat,
877 from_min_cat,
878 to_min_cat,
879 to_min_cat,
880 from_cat_seg_val,
881 from_cat_seg_val,
882 to_cat_seg_val,
883 to_cat_seg_val,
884 from_cc,
885 from_cc,
886 to_cc,
887 to_cc,
888 from_asset_num,
889 from_asset_num,
890 to_asset_num,
891 to_asset_num,
892 h_book, -- where_clause2
893 h_dist_source_book,
894 h_book,
895 h_book,
896 h_period1_pc,
897 h_period2_pc,
898 h_book,
899 h_period1_pc,
900 h_period2_pc,
901 h_book,
902 h_book,
903 from_maj_cat, -- l_param_where
904 from_maj_cat,
905 to_maj_cat,
906 to_maj_cat,
907 from_min_cat,
908 from_min_cat,
909 to_min_cat,
910 to_min_cat,
911 from_cat_seg_val,
912 from_cat_seg_val,
913 to_cat_seg_val,
914 to_cat_seg_val,
915 from_cc,
916 from_cc,
917 to_cc,
918 to_cc,
919 from_asset_num,
920 from_asset_num,
921 to_asset_num,
922 to_asset_num,
923 h_book, -- where_clause3
924 h_dist_source_book,
925 h_book,
926 h_book,
927 h_period1_pc,
928 h_period2_pc,
929 h_book,
930 h_book,
931 from_maj_cat, -- l_param_where
932 from_maj_cat,
933 to_maj_cat,
934 to_maj_cat,
935 from_min_cat,
936 from_min_cat,
937 to_min_cat,
938 to_min_cat,
939 from_cat_seg_val,
940 from_cat_seg_val,
941 to_cat_seg_val,
942 to_cat_seg_val,
943 from_cc,
944 from_cc,
945 to_cc,
946 to_cc,
947 from_asset_num,
948 from_asset_num,
949 to_asset_num,
950 to_asset_num,
951 h_book, -- where_clause4
952 h_dist_source_book,
953 h_book,
954 h_period1_pc,
955 h_period1_pc,
956 h_period2_pc,
957 h_book,
958 h_book,
959 h_period1_pc, -- Bug 5222214
960 h_period2_pc, -- Bug 5222214
961 h_book,
962 from_maj_cat, -- l_param_where
963 from_maj_cat,
964 to_maj_cat,
965 to_maj_cat,
966 from_min_cat,
967 from_min_cat,
968 to_min_cat,
969 to_min_cat,
970 from_cat_seg_val,
971 from_cat_seg_val,
972 to_cat_seg_val,
973 to_cat_seg_val,
974 from_cc,
975 from_cc,
976 to_cc,
977 to_cc,
978 from_asset_num,
979 from_asset_num,
980 to_asset_num,
981 to_asset_num;
982
983
984 IF (g_print_debug) THEN
985 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'Before Loop');
986 END IF;
987
988 loop
989
990 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
991
992 fetch additions into
993 h_source,
994 h_ccid,
995 h_asset_type_mean,
996 h_asset_type,
997 h_cost_acct,
998 h_asset_number,
999 h_description,
1000 h_tag_number, h_serial_number, h_inventorial,
1001 h_asset_key_ccid,
1002 h_vendor_number,
1003 h_invoice_number,
1004 h_line_number,
1005 h_invoice_descr,
1006 h_invoice_orig_cost,
1007 h_invoice_cost,
1008 h_cost_to_clear,
1009 h_invoice_flag,
1010 h_dpis,
1011 h_method,
1012 h_life_months,
1013 h_prod_capacity,
1014 h_adjusted_Rate,
1015 h_reserve_acct,
1016 h_bonus_rate,
1017 h_category_id,
1018 h_location_id,
1019 h_ytd_deprn,
1020 h_deprn_reserve,
1021 h_tran_header_id,
1022 h_maj_cat,
1023 h_min_cat,
1024 h_specified_cat,
1025 h_group_asset_number;
1026
1027 if (additions%NOTFOUND) then exit; end if;
1028 ctr := ctr + 1;
1029
1030 mesg := 'concat_account';
1031
1032 h_mesg_name := 'FA_RX_CONCAT_SEGS';
1033 h_flex_error := 'GL#';
1034 h_ccid_error := h_ccid;
1035
1036 fa_rx_shared_pkg.concat_acct (
1037 struct_id => h_acct_flex_struct,
1038 ccid => h_ccid,
1039 concat_string => h_concat_acct,
1040 segarray => h_acct_segs);
1041
1042 mesg := 'concat_category';
1043
1044 h_flex_error := 'CAT#';
1045 h_ccid_error := h_category_id;
1046
1047 fa_rx_shared_pkg.concat_category (
1048 struct_id => h_cat_flex_struct,
1049 ccid => h_category_id,
1050 concat_string => h_concat_cat,
1051 segarray => h_cat_segs);
1052
1053 mesg := 'concat_location';
1054
1055 h_flex_error := 'LOC#';
1056 h_ccid_error := h_location_id;
1057
1058 fa_rx_shared_pkg.concat_location (
1059 struct_id => h_loc_flex_struct,
1060 ccid => h_location_id,
1061 concat_string => h_concat_loc,
1062 segarray => h_loc_segs);
1063
1064
1065
1066 if h_asset_key_ccid is not null then
1067 mesg := 'concat_asset_key';
1068
1069 h_flex_error := 'KEY#';
1070 h_ccid_error := h_asset_key_ccid;
1071
1072 fa_rx_shared_pkg.concat_asset_key (
1073 struct_id => h_assetkey_flex_structure,
1074 ccid => h_asset_key_ccid,
1075 concat_string => h_concat_key,
1076 segarray => h_key_segs);
1077 else
1078 h_concat_key := '';
1079
1080 end if;
1081
1082 select decode(h_life_months, null, null,
1083 to_char(floor(h_life_months/12)) || '.' ||
1084 to_char(mod(h_life_months,12)))
1085 into h_life_year_month
1086 from dual;
1087
1088 h_life_year_month_num := fnd_number.canonical_to_number(h_life_year_month);
1089
1090 /* h_account_desc :=
1091 fa_rx_flex_pkg.get_description(
1092 p_application_id => 101,
1093 p_id_flex_code => 'GL#',
1094 p_id_flex_num => h_chart_of_accounts_id,
1095 p_qualifier => 'GL_ACCOUNT',
1096 p_data => h_acct_segs(h_acct_seg));
1097 */
1098
1099 h_account_desc :=
1100 fa_rx_flex_pkg.get_description(
1101 p_application_id => 101,
1102 p_id_flex_code => 'GL#',
1103 p_id_flex_num => h_chart_of_accounts_id,
1104 p_qualifier => 'GL_ACCOUNT',
1105 p_data => h_cost_acct);
1106
1107 h_cost_center_desc :=
1108 fa_rx_flex_pkg.get_description(
1109 p_application_id => 101,
1110 p_id_flex_code => 'GL#',
1111 p_id_flex_num => h_chart_of_accounts_id,
1112 p_qualifier => 'FA_COST_CTR',
1113 p_data => h_acct_segs(h_cost_seg));
1114
1115
1116 IF (g_print_debug) THEN
1117 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || '** assetkey_flex_struct:' || h_assetkey_flex_structure);
1118 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || '** category_id:' || h_category_id);
1119 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || '** specified_cat:' || h_cat_seg_num);
1120 END IF;
1121
1122 /*
1123 h_maj_cat :=
1124 fa_rx_flex_pkg.get_value(
1125 p_application_id => 140,
1126 p_id_flex_code => 'CAT#',
1127 p_id_flex_num => h_assetkey_flex_structure,
1128 p_qualifier => 'BASED_CATEGORY',
1129 p_ccid => h_category_id);
1130 */
1131 begin
1132 h_maj_cat_desc :=
1133 fa_rx_flex_pkg.get_description(
1134 p_application_id => 140,
1135 p_id_flex_code => 'CAT#',
1136 p_id_flex_num => h_assetkey_flex_structure,
1137 p_qualifier => 'BASED_CATEGORY',
1138 p_data => h_maj_cat);
1139 exception
1140 when others then
1141 h_maj_cat_desc := null;
1142 end;
1143
1144 /*
1145 BEGIN
1146 h_min_cat :=
1147 fa_rx_flex_pkg.get_value(
1148 p_application_id => 140,
1149 p_id_flex_code => 'CAT#',
1150 p_id_flex_num => h_assetkey_flex_structure,
1151 p_qualifier => 'MINOR_CATEGORY',
1152 p_ccid => h_category_id);
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 h_min_cat := null;
1156 end;
1157 */
1158 begin
1159 h_min_cat_desc :=
1160 fa_rx_flex_pkg.get_description(
1161 p_application_id => 140,
1162 p_id_flex_code => 'CAT#',
1163 p_id_flex_num => h_assetkey_flex_structure,
1164 p_qualifier => 'MINOR_CATEGORY',
1165 p_data => h_min_cat);
1166 EXCEPTION
1167 WHEN OTHERS THEN
1168 h_min_cat_desc := null;
1169 end;
1170 /*
1171 BEGIN
1172 h_specified_cat :=
1173 fa_rx_flex_pkg.get_value(
1174 p_application_id => 140,
1175 p_id_flex_code => 'CAT#',
1176 p_id_flex_num => h_assetkey_flex_structure,
1177 p_qualifier => h_cat_seg_num,
1178 p_ccid => h_category_id);
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 h_specified_cat := null;
1182 end;
1183 */
1184 begin
1185 h_specified_cat_desc :=
1186 fa_rx_flex_pkg.get_description(
1187 p_application_id => 140,
1188 p_id_flex_code => 'CAT#',
1189 p_id_flex_num => h_assetkey_flex_structure,
1190 p_qualifier => h_cat_seg_num,
1191 p_data => h_specified_cat);
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194 h_specified_cat_desc := null;
1195 end;
1196
1197 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
1198
1199 insert into fa_addition_rep_itf (
1200 request_id, source, company, cost_Center, expense_acct,
1201 asset_type, asset_number,
1202 tag_number, serial_number, inventorial, description, vendor_number,
1203 invoice_number, line_number, invoice_descr,
1204 invoice_orig_cost, invoice_cost, cost_to_clear,
1205 invoice_flag, date_placed_in_service, method,
1206 life_year_month, prod_capacity, adjusted_rate,
1207 reserve_acct, cost_acct, category, location,
1208 last_update_date, creation_date, last_updated_by,
1209 last_update_login, created_by,
1210 reserve, set_of_books_id, functional_currency_code,organization_name,
1211 book_type_code, period_name, period_name_to,
1212 account_description, cost_center_description, ytd_depreciation,
1213 transaction_header_id, major_category, major_category_desc,
1214 minor_category, minor_category_desc,specified_category_seg,
1215 specified_cat_seg_desc, group_asset_number, asset_key ) values (
1216 request_id, h_source, h_acct_segs(h_bal_seg),
1217 h_acct_segs(h_cost_seg), h_acct_segs(h_acct_seg),
1218 h_asset_type_mean, h_asset_number,
1219 h_tag_number, h_serial_number, h_inventorial, h_description,
1220 h_vendor_number, h_invoice_number, h_line_number,
1221 h_invoice_descr, h_invoice_orig_cost, h_invoice_cost,
1222 h_cost_to_clear, h_invoice_flag, h_dpis,
1223 h_method, h_life_year_month_num, h_prod_capacity,
1224 h_adjusted_rate, h_reserve_acct, h_cost_acct,
1225 h_concat_cat, h_concat_loc, sysdate, sysdate,
1226 user_id, h_login_id, user_id,
1227 h_deprn_reserve, h_set_of_books_id, h_currency_code,h_organization_name,
1228 h_book, h_period_name, h_period_name_to, h_account_desc,
1229 h_cost_center_desc, h_ytd_deprn, h_tran_header_id,
1230 h_maj_cat, h_maj_cat_desc, h_min_cat, h_min_cat_desc, h_specified_cat,
1231 h_specified_cat_desc, h_group_asset_number, h_concat_key);
1232
1233
1234 end loop;
1235
1236 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
1237
1238 close additions;
1239
1240 IF (g_print_debug) THEN
1241 fa_rx_util_pkg.debug('ADD_BY_PERIOD: ' || 'loop counter:' || ctr);
1242 END IF;
1243
1244 exception when others then
1245 fa_Rx_conc_mesg_pkg.log('Error occurred');
1246 fa_Rx_conc_mesg_pkg.log(h_mesg_name);
1247 if SQLCODE <> 0 then
1248 fa_Rx_conc_mesg_pkg.log(SQLERRM);
1249 end if;
1250 fnd_message.set_name('OFA',h_mesg_name);
1251 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
1252 fnd_message.set_token('TABLE','FA_ADDITION_REP_ITF',FALSE);
1253 end if;
1254 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
1255 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
1256 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
1257 end if;
1258
1259 h_mesg_str := fnd_message.get;
1260 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1261 retcode := 2;
1262
1263 end add_by_period;
1264
1265
1266 procedure add_by_date (
1267 book in varchar2,
1268 begin_dpis in date,
1269 end_dpis in date,
1270 request_id in number,
1271 user_id in number,
1272 retcode out nocopy number,
1273 errbuf out nocopy varchar2) is
1274
1275 mesg varchar2(200);
1276 ctr number;
1277
1278 h_book varchar2(15);
1279 h_request_id number;
1280 h_login_id number;
1281
1282 h_bonus_rate number;
1283 h_reserve_acct varchar2(25);
1284 h_adjusted_Rate number;
1285 h_prod_capacity number;
1286 h_life_months number;
1287 h_life_year_month varchar2(10);
1288 h_life_year_month_num number;
1289 h_method varchar2(15);
1290 h_dpis date;
1291 h_invoice_flag varchar2(1);
1292 h_cost_to_clear number;
1293 h_invoice_cost number;
1294 h_invoice_orig_cost number;
1295 h_invoice_descr varchar2(80);
1296 h_line_number number;
1297 h_invoice_number varchar2(50);
1298 h_tag_number varchar2(15);
1299 h_serial_number varchar2(35);
1300 h_inventorial varchar2(3);
1301 h_vendor_number varchar2(30);
1302 h_description varchar2(80);
1303 h_asset_number varchar2(15);
1304 h_asset_type varchar2(15);
1305 h_cost_acct varchar2(25);
1306 h_asset_type_mean varchar2(80);
1307 h_ccid number;
1308 h_source varchar2(20);
1309
1310 h_category_id number;
1311 h_location_id number;
1312
1313 h_concat_acct varchar2(200);
1314 h_concat_cat varchar2(200);
1315 h_concat_loc varchar2(200);
1316 h_acct_segs fa_rx_shared_pkg.Seg_Array;
1317 h_cat_segs fa_rx_shared_pkg.Seg_Array;
1318 h_loc_segs fa_rx_shared_pkg.Seg_Array;
1319
1320 h_acct_seg number;
1321 h_cost_seg number;
1322 h_bal_seg number;
1323
1324 h_dist_source_book varchar2(15);
1325
1326 h_acct_flex_struct number;
1327 h_cat_flex_struct number;
1328 h_loc_flex_struct number;
1329
1330 h_count number;
1331
1332 h_mesg_name varchar2(50);
1333 h_mesg_str varchar2(2000);
1334 h_flex_error varchar2(5);
1335 h_ccid_error number;
1336
1337 cursor additions is
1338 SELECT DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'), -- source
1339 dh.code_combination_id, -- expense account
1340 FALU.MEANING, -- translated asset type
1341 AH.ASSET_TYPE,
1342 DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1343 CB.ASSET_COST_ACCT),
1344 AD.ASSET_NUMBER,
1345 AD.description,
1346 ad.tag_number, ad.serial_number, ad.inventorial,
1347 NULL, -- vendor number
1348 NULL, -- invoice number
1349 TO_NUMBER(NULL), -- line number
1350 NULL, -- invoice description
1351 TO_NUMBER(NULL) , -- invoice original cost
1352 TO_NUMBER(NULL), -- invoice cost
1353
1354 ---bug fix 4275433
1355 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))) ,
1356
1357 -- NVL(DD.ADDITION_COST_TO_CLEAR, 0), -- cost-to-clear
1358 NULL, -- flag
1359 bk.date_placed_in_service,
1360 bk.deprn_method_code,
1361 bk.life_in_months,
1362 bk.production_capacity,
1363 bk.adjusted_rate,
1364 cb.deprn_reserve_acct,
1365 ds.bonus_Rate,
1366 cb.category_id, dh.location_id
1367 FROM
1368 FA_DISTRIBUTION_HISTORY DH,
1369 FA_ASSET_HISTORY AH,
1370 FA_CATEGORY_BOOKS CB,
1371 FA_LOOKUPS FALU,
1372 FA_ADDITIONS AD,
1373 --GL_CODE_COMBINATIONS DHCC,
1374 FA_BOOKS BK,
1375 FA_DEPRN_SUMMARY DS,
1376 FA_TRANSACTION_HEADERS TH,
1377 FA_DEPRN_DETAIL DD,
1378 fa_adjustments adj
1379
1380 WHERE
1381 bk.book_type_code = th.book_type_code AND
1382 bk.asset_id = th.asset_id AND
1383 bk.transaction_header_id_in = th.transaction_header_id AND
1384 bk.date_placed_in_service >= begin_dpis AND
1385 bk.date_placed_in_service <= end_dpis
1386 AND
1387 ds.book_type_code = dd.book_type_code AND
1388 ds.asset_id = dd.asset_id AND
1389 ds.period_counter = dd.period_counter
1390 AND
1391 th.asset_id = dd.asset_id AND
1392 th.transaction_type_code = 'ADDITION' AND
1393 -- bug fix 3807732
1394 th.book_type_code = h_book
1395 AND
1396 DH.BOOK_TYPE_CODE = h_dist_source_book AND
1397 DH.ASSET_ID = DD.ASSET_ID AND
1398 --DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
1399 --AND
1400 DD.BOOK_TYPE_CODE = h_book AND
1401 DD.DEPRN_SOURCE_CODE = 'B' AND
1402 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
1403 AND
1404 ADJ.book_type_code(+) = h_book AND
1405 ADJ.asset_id(+) = dh.ASSET_ID AND
1406 ADJ.source_type_code(+) like '%ADDITION' AND
1407 adj.adjustment_type(+) like 'COST' and
1408 ADJ.distribution_id(+) = DH.DISTRIBUTION_ID
1409 AND
1410 CB.CATEGORY_ID = AH.CATEGORY_ID AND
1411 CB.BOOK_TYPE_CODE = h_book
1412 AND
1413 AD.ASSET_ID = DD.ASSET_ID
1414 AND
1415 AH.ASSET_ID = AD.ASSET_ID AND
1416 AH.DATE_EFFECTIVE <= th.date_effective AND
1417 NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > th.date_effective
1418 AND
1419 AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
1420 FALU.LOOKUP_TYPE = 'ASSET TYPE'
1421 --GROUP BY
1422 -- DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1423 -- dh.code_combination_id,
1424 -- FALU.MEANING,
1425 -- AH.ASSET_TYPE,
1426 -- DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1427 -- CB.ASSET_COST_ACCT),
1428 -- AD.ASSET_NUMBER,
1429 -- AD.description,
1430 -- bk.date_placed_in_service,
1431 -- bk.deprn_method_code,
1432 -- bk.life_in_months,
1433 -- bk.production_capacity,
1434 -- bk.adjusted_rate,
1435 -- cb.deprn_reserve_acct,
1436 -- ds.bonus_Rate,
1437 -- cb.category_id, dh.location_id
1438 UNION ALL
1439 SELECT DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1440 dh.code_combination_id,
1441 FALU.MEANING,
1442 AH.ASSET_TYPE,
1443 DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1444 CB.ASSET_COST_ACCT),
1445 AD.ASSET_NUMBER,
1446 AD.description,
1447 ad.tag_number, ad.serial_number, ad.inventorial,
1448 NULL,
1449 NULL,
1450 TO_NUMBER(NULL),
1451 NULL,
1452 TO_NUMBER(NULL),
1453 TO_NUMBER(NULL),
1454 DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR',1,-1) *
1455 ADJ.ADJUSTMENT_AMOUNT,
1456 NULL,
1457 bk.date_placed_in_service,
1458 bk.deprn_method_code,
1459 bk.life_in_months,
1460 bk.production_capacity,
1461 bk.adjusted_rate,
1462 cb.deprn_reserve_acct,
1463 ds.bonus_Rate,
1464 cb.category_id, dh.location_id
1465 FROM
1466 fa_books bk,
1467 fa_deprn_summary ds,
1468 FA_TRANSACTION_HEADERS TH,
1469 FA_TRANSACTION_HEADERS THDIS,
1470 FA_ADDITIONS AD,
1471 FA_ASSET_HISTORY AH,
1472 FA_CATEGORY_BOOKS CB,
1473 FA_DISTRIBUTION_HISTORY DH,
1474 --GL_CODE_COMBINATIONS DHCC,
1475 --GL_CODE_COMBINATIONS AJCC,
1476 FA_LOOKUPS FALU,
1477 FA_ADJUSTMENTS ADJ,
1478 fa_deprn_periods dp
1479 WHERE
1480 DP.BOOK_TYPE_CODE = h_book AND
1481 DP.period_open_date >= bk.date_effective --AND
1482 -- dp.period_close_date <= nvl(bk.date_ineffective,sysdate)
1483 AND
1484 ds.asset_id = bk.asset_id and
1485 ds.book_type_code = bk.book_type_code and
1486 -- bugfix 3807732
1487 ds.deprn_source_code = 'BOOKS' and
1488 (ds.period_counter + 1) = dp.period_counter
1489 AND
1490 bk.asset_id = th.asset_id and
1491 bk.book_type_code = th.book_type_code and
1492 bk.transaction_header_id_in = th.transaction_header_id AND
1493 bk.date_placed_in_service >= begin_dpis AND
1494 bk.date_placed_in_service <= end_dpis
1495 AND
1496 TH.BOOK_TYPE_CODE = h_book AND
1497 TH.TRANSACTION_TYPE_CODE = 'ADDITION'
1498 AND
1499
1500 THDIS.TRANSACTION_TYPE_CODE = 'TRANSFER IN' AND
1501 THDIS.BOOK_TYPE_CODE = h_book AND
1502 THDIS.ASSET_ID = TH.ASSET_ID AND
1503 THDIS.DATE_EFFECTIVE < th.date_effective
1504 AND
1505 ADJ.BOOK_TYPE_CODE = h_book AND
1506 ADJ.ASSET_ID = TH.ASSET_ID AND
1507 ADJ.SOURCE_TYPE_CODE = 'ADDITION' AND
1508 ADJ.ADJUSTMENT_TYPE = 'COST' AND
1509 ADJ.PERIOD_COUNTER_CREATED = DP.PERIOD_COUNTER AND
1510 --ADJ.CODE_COMBINATION_ID = AJCC.CODE_COMBINATION_ID
1511 --AND
1512 DH.BOOK_TYPE_CODE = h_book AND
1513 DH.ASSET_ID = TH.ASSET_ID AND
1514 DH.DISTRIBUTION_ID = ADJ.DISTRIBUTION_ID AND
1515 --DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
1516 --AND
1517 CB.CATEGORY_ID = AH.CATEGORY_ID AND
1518 CB.BOOK_TYPE_CODE = h_book
1519 AND
1520 AD.ASSET_ID = TH.ASSET_ID
1521 AND
1522 AH.ASSET_ID = TH.ASSET_ID AND
1523 AH.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
1524 NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > TH.DATE_EFFECTIVE
1525 AND
1526 AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
1527 FALU.LOOKUP_TYPE = 'ASSET TYPE'
1528 --GROUP BY
1529 -- DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1530 -- dh.code_combination_id,
1531 -- FALU.MEANING,
1532 -- AH.ASSET_TYPE,
1533 -- DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1534 -- CB.ASSET_COST_ACCT),
1535 -- AD.ASSET_NUMBER,
1536 -- AD.description,
1537 -- bk.date_placed_in_service,
1538 -- bk.deprn_method_code,
1539 -- bk.life_in_months,
1540 -- bk.production_capacity,
1541 -- bk.adjusted_rate,
1542 -- cb.deprn_reserve_acct,
1543 -- ds.bonus_Rate,
1544 -- cb.category_id, dh.location_id
1545 UNION ALL
1546 SELECT DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1547 dh.code_combination_id,
1548 FALU.MEANING,
1549 AH.ASSET_TYPE,
1550 DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1551 CB.ASSET_COST_ACCT),
1552 AD.ASSET_NUMBER,
1553 AD.description,
1554 ad.tag_number, ad.serial_number, ad.inventorial,
1555 PO_VEND.segment1,
1556 AI_IN.INVOICE_NUMBER,
1557 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,
1643 -- AI_IN.DESCRIPTION,
1644 -- AI_IN.PAYABLES_COST ,
1645 -- DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
1646 -- 'INVOICE ADJUSTMENT','A',
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 AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
1670 AI_IN.DESCRIPTION ,
1671 AI_IN.PAYABLES_COST,
1672 DH.UNITS_ASSIGNED/AH.UNITS * AI_IN.FIXED_ASSETS_COST,
1673 TO_NUMBER(NULL),
1674 DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
1675 'INVOICE ADJUSTMENT','A',
1676 'INVOICE TRANSFER','T',
1677 'INVOICE REINSTATE','R',NULL),
1678 bk.date_placed_in_service,
1679 bk.deprn_method_code,
1680 bk.life_in_months,
1681 bk.production_capacity,
1682 bk.adjusted_rate,
1683 cb.deprn_reserve_acct,
1684 ds.bonus_Rate,
1685 cb.category_id, dh.location_id
1686 FROM
1687 fa_books bk,
1688 fa_deprn_summary ds,
1689 FA_ASSET_INVOICES AI_IN,
1690 FA_INVOICE_TRANSACTIONS IT,
1691 FA_TRANSACTION_HEADERS THDIS,
1692 FA_DISTRIBUTION_HISTORY DH,
1693 FA_ASSET_HISTORY AH,
1694 FA_CATEGORY_BOOKS CB,
1695 FA_LOOKUPS FALU,
1696 PO_VENDORS PO_VEND,
1697 FA_ADDITIONS AD,
1698 --GL_CODE_COMBINATIONS DHCC,
1699 FA_TRANSACTION_HEADERS TH,
1700 FA_DEPRN_PERIODS DP
1701 WHERE
1702 DP.BOOK_TYPE_CODE = h_book AND
1703 dp.period_open_date >= bk.date_effective --AND
1704 -- dp.period_close_date <= nvl(bk.date_ineffective,sysdate)
1705 AND
1706 ds.asset_id = bk.asset_id and
1707 ds.book_type_code = bk.book_type_code and
1708 -- bug fix 3807732
1709 ds.deprn_source_code = 'BOOKS' and
1710 (ds.period_counter + 1) = dp.period_counter
1711 AND
1712 bk.asset_id = th.asset_id and
1713 bk.book_type_code = th.book_type_code and
1714 bk.date_placed_in_service >= begin_dpis AND
1715 bk.date_placed_in_service <= end_dpis AND
1716 bk.transaction_header_id_in = th.transaction_header_id
1717 AND
1718 TH.BOOK_TYPE_CODE = h_book AND
1719 TH.TRANSACTION_TYPE_CODE = 'ADDITION'
1720 AND
1721 THDIS.TRANSACTION_TYPE_CODE = 'TRANSFER IN' AND
1722 THDIS.BOOK_TYPE_CODE = h_book AND
1723 THDIS.ASSET_ID = TH.ASSET_ID AND
1724 THDIS.DATE_EFFECTIVE < th.date_effective
1725 AND
1726 DH.BOOK_TYPE_CODE = h_book AND
1727 DH.ASSET_ID = TH.ASSET_ID AND
1728 --DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID AND
1729 DH.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
1730 NVL(DH.DATE_INEFFECTIVE, SYSDATE) > TH.DATE_EFFECTIVE
1731 AND
1732 CB.CATEGORY_ID = AH.CATEGORY_ID AND
1733 CB.BOOK_TYPE_CODE = h_book
1734 AND
1735 AD.ASSET_ID = TH.ASSET_ID
1736 AND
1737 AH.ASSET_ID = TH.ASSET_ID AND
1738 AH.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
1739 NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > TH.DATE_EFFECTIVE
1740 AND
1741 AH.ASSET_TYPE = FALU.LOOKUP_CODE AND
1742 FALU.LOOKUP_TYPE = 'ASSET TYPE'
1743 AND
1744 IT.INVOICE_TRANSACTION_ID = AI_IN.INVOICE_TRANSACTION_ID_IN
1745 AND
1746 AI_IN.ASSET_ID = TH.ASSET_ID AND
1747 AI_IN.DATE_EFFECTIVE <= th.date_effective AND
1748 NVL(AI_IN.DATE_INEFFECTIVE, SYSDATE+1) > th.date_effective AND
1749 AI_IN.DELETED_FLAG = 'NO'
1750 AND
1751 PO_VEND.VENDOR_ID(+) = AI_IN.PO_VENDOR_ID;
1752 --GROUP BY
1753 -- DECODE(TH.MASS_REFERENCE_ID,NULL,'Manual Addition','Mass Addition'),
1754 -- dh.code_combination_id,
1755 -- FALU.MEANING,
1756 -- AH.ASSET_TYPE,
1757 -- DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
1758 -- CB.ASSET_COST_ACCT),
1759 -- AD.ASSET_NUMBER,
1760 -- AD.description,
1761 -- PO_VEND.segment1,
1762 -- AI_IN.INVOICE_NUMBER,
1763 -- AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
1764 -- AI_IN.DESCRIPTION,
1765 -- AI_IN.PAYABLES_COST ,
1766 -- DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
1767 -- 'INVOICE ADJUSTMENT','A',
1768 -- 'INVOICE TRANSFER','T',
1769 -- 'INVOICE REINSTATE','R',NULL),
1770 -- bk.date_placed_in_service,
1771 -- bk.deprn_method_code,
1772 -- bk.life_in_months,
1773 -- bk.production_capacity,
1774 -- bk.adjusted_rate,
1775 -- cb.deprn_reserve_acct,
1776 -- ds.bonus_Rate,
1777 -- cb.category_id, dh.location_id;
1778
1779
1780
1781 begin
1782
1783 h_book := book;
1784 ctr := 0;
1785 h_request_id := request_id;
1786
1787 select fcr.last_update_login into h_login_id
1788 from fnd_concurrent_requests fcr
1789 where fcr.request_id = h_request_id;
1790
1791 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
1792
1793 select nvl(distribution_source_book, book_type_code), accounting_flex_structure
1794 into h_dist_source_book, h_acct_flex_struct
1795 from fa_book_controls
1796 where book_type_code = h_book;
1797
1798 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
1799
1800 select location_flex_structure, category_flex_structure
1801 into h_loc_flex_struct, h_cat_flex_struct
1802 from fa_system_controls;
1803
1804 h_mesg_name := 'FA_RX_SEGNUMS';
1805
1806 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
1807 BOOK => h_book,
1808 BALANCING_SEGNUM => h_bal_seg,
1809 ACCOUNT_SEGNUM => h_acct_seg,
1810 CC_SEGNUM => h_cost_seg,
1811 CALLING_FN => 'ADD_BY_PERIOD');
1812
1813
1814 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
1815
1816 open additions;
1817 loop
1818 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
1819
1820 fetch additions into
1821 h_source,
1822 h_ccid,
1823 h_asset_type_mean,
1824 h_asset_type,
1825 h_cost_acct,
1826 h_asset_number,
1827 h_description,
1828 h_tag_number, h_serial_number, h_inventorial,
1829 h_vendor_number,
1830 h_invoice_number,
1831 h_line_number,
1832 h_invoice_descr,
1833 h_invoice_orig_cost,
1834 h_invoice_cost,
1835 h_cost_to_clear,
1836 h_invoice_flag,
1837 h_dpis,
1838 h_method,
1839 h_life_months,
1840 h_prod_capacity,
1841 h_adjusted_Rate,
1842 h_reserve_acct,
1843 h_bonus_rate,
1844 h_category_id,
1845 h_location_id;
1846
1847
1848 if (additions%NOTFOUND) then exit; end if;
1849 ctr := ctr + 1;
1850
1851 mesg := 'concat_account';
1852
1853 h_mesg_name := 'FA_RX_CONCAT_SEGS';
1854 h_flex_error := 'GL#';
1855 h_ccid_error := h_ccid;
1856
1857 fa_rx_shared_pkg.concat_acct (
1858 struct_id => h_acct_flex_struct,
1859 ccid => h_ccid,
1860 concat_string => h_concat_acct,
1861 segarray => h_acct_segs);
1862
1863 mesg := 'concat_category';
1864
1865 h_flex_error := 'CAT#';
1866 h_ccid_error := h_category_id;
1867
1868 fa_rx_shared_pkg.concat_category (
1869 struct_id => h_cat_flex_struct,
1870 ccid => h_category_id,
1871 concat_string => h_concat_cat,
1872 segarray => h_cat_segs);
1873
1874 mesg := 'concat_location';
1875
1876 h_flex_error := 'LOC#';
1877 h_ccid_error := h_location_id;
1878
1879 fa_rx_shared_pkg.concat_location (
1880 struct_id => h_loc_flex_struct,
1881 ccid => h_location_id,
1882 concat_string => h_concat_loc,
1883 segarray => h_loc_segs);
1884
1885 select decode(h_life_months, null, null,
1886 to_char(floor(h_life_months/12)) || '.' ||
1887 to_char(mod(h_life_months,12)))
1888 into h_life_year_month
1889 from dual;
1890
1891 h_life_year_month_num := fnd_number.canonical_to_number(h_life_year_month);
1892
1893 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
1894
1895 insert into fa_addition_rep_itf (
1896 request_id, source, company, cost_Center, expense_acct,
1897 asset_type, asset_number, description,
1898 tag_number, serial_number, inventorial, vendor_number,
1899 invoice_number, line_number, invoice_descr,
1900 invoice_orig_cost, invoice_cost, cost_to_clear,
1901 invoice_flag, date_placed_in_service, method,
1902 life_year_month, prod_capacity, adjusted_rate,
1903 reserve_acct, cost_acct, category, location,
1904 last_update_date, creation_date, last_updated_by,
1905 last_update_login, created_by) values (
1906 request_id, h_source, h_acct_segs(h_bal_seg),
1907 h_acct_segs(h_cost_seg), h_acct_segs(h_acct_seg),
1908 h_asset_type_mean, h_asset_number, h_description,
1909 h_tag_number, h_serial_number, h_inventorial,
1910 h_vendor_number, h_invoice_number, h_line_number,
1911 h_invoice_descr, h_invoice_orig_cost, h_invoice_cost,
1912 h_cost_to_clear, h_invoice_flag, h_dpis,
1913 h_method, h_life_year_month_num, h_prod_capacity,
1914 h_adjusted_rate, h_reserve_acct, h_cost_acct,
1915 h_concat_cat, h_concat_loc, sysdate, sysdate,
1916 user_id, h_login_id, user_id);
1917
1918
1919
1920
1921 end loop;
1922
1923 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
1924
1925 close additions;
1926
1927 exception when others then
1928 if SQLCODE <> 0 then
1929 fa_Rx_conc_mesg_pkg.log(SQLERRM);
1930 end if;
1931 fnd_message.set_name('OFA',h_mesg_name);
1932 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
1933 fnd_message.set_token('TABLE','FA_ADDITION_REP_ITF',FALSE);
1934 end if;
1935 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
1936 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
1937 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
1938 end if;
1939
1940 h_mesg_str := fnd_message.get;
1941 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1942 retcode := 2;
1943
1944 end add_by_date;
1945
1946
1947
1948 procedure add_by_resp (
1949 book in varchar2,
1950 period in varchar2,
1951 begin_cc in varchar2,
1952 end_cc in varchar2,
1953 request_id in number,
1954 user_id in number,
1955 retcode out nocopy number,
1956 errbuf out nocopy varchar2) is
1957
1958 mesg varchar2(200);
1959 ctr number;
1960
1961 h_book varchar2(15);
1962 h_request_id number;
1963 h_login_id number;
1964
1965 h_period1_pc number;
1966 h_period2_pc number;
1967 h_period1_pod date;
1968 h_period2_pcd date;
1969
1970 h_bonus_rate number;
1971 h_reserve_acct varchar2(25);
1972 h_adjusted_Rate number;
1973 h_prod_capacity number;
1974 h_life_months number;
1975 h_life_year_month varchar2(10);
1976 h_life_year_month_num number;
1977 h_method varchar2(15);
1978 h_dpis date;
1979 h_invoice_flag varchar2(1);
1980 h_cost_to_clear number;
1981 h_invoice_cost number;
1982 h_invoice_orig_cost number;
1983 h_invoice_descr varchar2(80);
1984 h_line_number number;
1985 h_invoice_number varchar2(50);
1986 h_vendor_number varchar2(30);
1987 h_description varchar2(80);
1988 h_tag_number varchar2(15);
1989 h_serial_number varchar2(35);
1990 h_inventorial varchar2(3);
1991 h_asset_number varchar2(15);
1992 h_asset_type varchar2(15);
1993 h_cost_acct varchar2(25);
1994 h_asset_type_mean varchar2(80);
1995 h_ccid number;
1996 h_source varchar2(20);
1997 h_emp_name varchar2(240);
1998 h_emp_number varchar2(30);
1999 h_units number;
2000 h_period_name varchar2(15);
2001 h_reserve number;
2002
2003 h_category_id number;
2004 h_location_id number;
2005
2006 h_concat_acct varchar2(200);
2007 h_concat_cat varchar2(200);
2008 h_concat_loc varchar2(200);
2009 h_acct_segs fa_rx_shared_pkg.Seg_Array;
2010 h_cat_segs fa_rx_shared_pkg.Seg_Array;
2011 h_loc_segs fa_rx_shared_pkg.Seg_Array;
2012
2013 h_acct_seg number;
2014 h_cost_seg number;
2015 h_bal_seg number;
2016
2017 h_dist_source_book varchar2(15);
2018
2019 h_acct_flex_struct number;
2020 h_cat_flex_struct number;
2021 h_loc_flex_struct number;
2022
2023
2024 h_major_category varchar2(50);
2025 h_minor_category varchar2(50);
2026
2027 maj_select_column varchar2(50);
2028 min_select_column varchar2(50);
2029 sql_stmt varchar2(1000);
2030
2031 TYPE cur IS ref cursor;
2032 category_segments cur;
2033
2034 h_major_cat_desc varchar2(200);
2035 h_minor_cat_desc varchar2(200);
2036
2037 h_count number;
2038
2039 h_mesg_name varchar2(50);
2040 h_mesg_str varchar2(2000);
2041 h_flex_error varchar2(5);
2042 h_ccid_error number;
2043
2044 -- added NVL for all h_period2_pcd in the cursor resp_additions
2045 -- SLA
2046 cursor resp_additions is SELECT
2047 cc.code_combination_id,
2048 EMP.FULL_NAME, emp.employee_number,
2049 loc.location_id,
2050 ah.category_id,
2051 AD.ASSET_NUMBER,
2052 AD.DESCRIPTION,
2053 DH.UNITS_ASSIGNED,
2054 AD.SERIAL_NUMBER,
2055 AD.TAG_NUMBER, ad.inventorial,
2056 BOOKS.LIFE_IN_MONTHS,
2057 BOOKS.ADJUSTED_RATE,
2058 BOOKS.PRODUCTION_CAPACITY ,
2059 NVL(DS.BONUS_RATE,0),
2060 nvl(DD.ADDITION_COST_TO_CLEAR, 0),
2061 nvl(DD.DEPRN_RESERVE,0),
2062 NULL,
2063 period --dp.period_name
2064 FROM
2065 FA_TRANSACTION_HEADERS TH,
2066 PER_ALL_PEOPLE_F EMP,
2067 FA_LOCATIONS LOC,
2068 GL_CODE_COMBINATIONS CC,
2069 FA_ADDITIONS AD,
2070 FA_ASSET_HISTORY AH,
2071 FA_DISTRIBUTION_HISTORY DH,
2072 FA_BOOKS BOOKS,
2073 FA_DEPRN_SUMMARY DS,
2074 FA_DEPRN_DETAIL DD
2075 --FA_DEPRN_PERIODS DP
2076 WHERE
2077 TH.DATE_EFFECTIVE >= h_period1_pod AND
2078 TH.DATE_EFFECTIVE <= nvl(h_period2_pcd ,sysdate) AND
2079 TH.BOOK_TYPE_CODE = h_book AND
2080 TH.TRANSACTION_TYPE_CODE = 'TRANSFER IN'
2081 AND
2082 DH.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID AND
2083 --nvl(DH.DATE_INEFFECTIVE, nvl(h_period2_pcd ,sysdate)+1) > nvl(h_period2_pcd ,sysdate) AND /* SLA */
2084 DH.BOOK_TYPE_CODE = h_book AND
2085 DH.ASSET_ID = TH.ASSET_ID
2086 AND
2087 TH.ASSET_ID = AH.ASSET_ID AND
2088 TH.date_effective between ah.date_effective and nvl(ah.date_ineffective,sysdate)
2089 AND
2090 BOOKS.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
2091 nvl(BOOKS.DATE_INEFFECTIVE,SYSDATE) > TH.DATE_EFFECTIVE AND
2092 BOOKS.ASSET_ID = TH.ASSET_ID AND
2093 BOOKS.BOOK_TYPE_CODE = h_book
2094 AND
2095 DD.BOOK_TYPE_CODE = h_book AND
2096 DD.ASSET_ID = TH.ASSET_ID AND
2097 DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
2098 DD.DEPRN_SOURCE_CODE = 'B' AND
2099 DD.PERIOD_COUNTER >= h_period1_pc - 1 AND
2100 dd.period_counter <= h_period2_pc - 1
2101 AND
2102 CC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
2103 AND
2104 AD.ASSET_ID = TH.ASSET_ID
2105 AND
2106 EMP.PERSON_ID(+) = DH.ASSIGNED_TO
2107 AND
2108 TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE(+) AND EFFECTIVE_END_DATE(+)
2109 AND
2110 LOC.LOCATION_ID = DH.LOCATION_ID
2111 /* AND
2112 dp.period_counter = ds.period_counter and
2113 dp.book_type_code = ds.book_type_code */ --SLA
2114 AND
2115 DS.ASSET_ID (+) = BOOKS.ASSET_ID AND
2116 DS.BOOK_TYPE_CODE (+) = h_book AND
2117 DS.PERIOD_COUNTER (+) >= h_period1_pc AND
2118 DS.period_counter(+) <= h_period2_pc
2119
2120 UNION
2121 SELECT
2122 cc.code_combination_id,
2123 EMP.FULL_NAME, emp.employee_number,
2124 loc.location_id,
2125 ah.category_id,
2126 AD.ASSET_NUMBER ,
2127 AD.DESCRIPTION ,
2128 DH.UNITS_ASSIGNED ,
2129 AD.SERIAL_NUMBER,
2130 AD.TAG_NUMBER , ad.inventorial,
2131 BOOKS.LIFE_IN_MONTHS,
2132 BOOKS.ADJUSTED_RATE,
2133 BOOKS.PRODUCTION_CAPACITY,
2134 NVL(DS.BONUS_RATE,0),
2135 sum(CADJ.ADJUSTMENT_AMOUNT *
2136 DECODE(CADJ.DEBIT_CREDIT_FLAG,'CR',-1,'DR',1)),
2137 0 , -- RESERVE,
2138 'T',
2139 period --dp.period_name
2140 FROM
2141 FA_TRANSACTION_HEADERS TH,
2142 PER_ALL_PEOPLE_F EMP,
2143 FA_LOCATIONS LOC,
2144 GL_CODE_COMBINATIONS CC,
2145 FA_ADDITIONS AD,
2146 FA_ASSET_HISTORY AH,
2147 FA_DISTRIBUTION_HISTORY DH,
2148 FA_BOOKS BOOKS,
2149 FA_DEPRN_SUMMARY DS,
2150 FA_ADJUSTMENTS CADJ
2151 --fa_deprn_periods dp
2152 WHERE
2153 TH.DATE_EFFECTIVE >= h_period1_pod AND
2154 TH.DATE_EFFECTIVE <= nvl(h_period2_pcd ,sysdate) AND
2155 TH.BOOK_TYPE_CODE = h_book AND
2156 TH.TRANSACTION_TYPE_CODE = 'TRANSFER'
2157 AND
2158 DH.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID AND
2159 --nvl(DH.DATE_INEFFECTIVE, nvl(h_period2_pcd ,sysdate)+1) > nvl(h_period2_pcd ,sysdate) AND
2160 DH.BOOK_TYPE_CODE = h_book AND
2161 DH.ASSET_ID = TH.ASSET_ID
2162 AND
2163 TH.ASSET_ID = AH.ASSET_ID AND
2164 TH.date_effective between ah.date_effective and nvl(ah.date_ineffective,sysdate)
2165 AND
2166 BOOKS.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
2167 nvl(BOOKS.DATE_INEFFECTIVE, SYSDATE) > TH.DATE_EFFECTIVE AND
2168 BOOKS.ASSET_ID = TH.ASSET_ID AND
2169 BOOKS.BOOK_TYPE_CODE = h_book
2170 AND
2171 CC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID AND
2172 CADJ.BOOK_TYPE_CODE = H_BOOK AND
2173 CADJ.ASSET_ID = TH.ASSET_ID AND
2174 CADJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
2175 CADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND
2176 CADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND
2177 CADJ.PERIOD_COUNTER_CREATED >= h_period1_pc AND
2178 cadj.period_counter_created <= h_period2_pc and
2179 CADJ.ADJUSTMENT_TYPE in ('COST','CIP COST')
2180 AND
2181 AD.ASSET_ID = TH.ASSET_ID
2182 AND
2183 EMP.PERSON_ID(+) = DH.ASSIGNED_TO
2184 AND
2185 TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+) AND EMP.EFFECTIVE_END_DATE(+)
2186 AND
2187 LOC.LOCATION_ID = DH.LOCATION_ID
2188 /*AND
2189 dp.period_counter = ds.period_counter and
2190 dp.book_type_code = ds.book_type_code*/
2191 AND
2192 DS.ASSET_ID (+) = BOOKS.ASSET_ID AND
2193 DS.BOOK_TYPE_CODE (+) = h_book AND
2194 DS.PERIOD_COUNTER (+) >= h_period1_pc and
2195 ds.period_counter(+) <= h_period2_pc
2196 GROUP BY
2197 cc.code_combination_id,
2198 EMP.FULL_NAME, emp.employee_number,
2199 loc.location_id,
2200 ah.category_id,
2201 AD.DESCRIPTION,
2202 DH.UNITS_ASSIGNED,
2203 AD.SERIAL_NUMBER,
2204 AD.TAG_NUMBER, ad.inventorial,
2205 AD.ASSET_NUMBER,
2206 BOOKS.LIFE_IN_MONTHS,
2207 BOOKS.ADJUSTED_RATE,
2208 BOOKS.PRODUCTION_CAPACITY,
2209 DS.BONUS_RATE,
2210 period --dp.period_name
2211 UNION
2212 SELECT
2213 cc.code_combination_id,
2214 EMP.FULL_NAME, emp.employee_number,
2215 loc.location_id,
2216 ah.category_id,
2217 AD.ASSET_NUMBER,
2218 AD.DESCRIPTION ,
2219 DH.UNITS_ASSIGNED,
2220 AD.SERIAL_NUMBER,
2221 AD.TAG_NUMBER , ad.inventorial,
2222 BOOKS.LIFE_IN_MONTHS,
2223 BOOKS.ADJUSTED_RATE,
2224 BOOKS.PRODUCTION_CAPACITY,
2225 NVL(DS.BONUS_RATE,0),
2226 0, -- COST,
2227 sum(RADJ.ADJUSTMENT_AMOUNT *
2228 DECODE(RADJ.DEBIT_CREDIT_FLAG,'CR',1,'DR',-1)),
2229 'T',
2230 period --dp.period_name
2231 FROM
2232 FA_TRANSACTION_HEADERS TH,
2233 PER_ALL_PEOPLE_F EMP,
2234 FA_LOCATIONS LOC,
2235 GL_CODE_COMBINATIONS CC,
2236 FA_ADDITIONS AD,
2237 FA_ASSET_HISTORY AH,
2238 FA_DISTRIBUTION_HISTORY DH,
2239 FA_DEPRN_SUMMARY DS,
2240 FA_BOOKS BOOKS,
2241 FA_ADJUSTMENTS RADJ
2242 --fa_deprn_periods dp
2243 WHERE
2244 TH.DATE_EFFECTIVE >= h_period1_pod AND
2245 TH.DATE_EFFECTIVE <= nvl(h_period2_pcd ,sysdate) AND
2246 TH.BOOK_TYPE_CODE = h_book AND
2247 TH.TRANSACTION_TYPE_CODE = 'TRANSFER'
2248 AND
2249 DH.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID AND
2250 --nvl(DH.DATE_INEFFECTIVE, nvl(h_period2_pcd ,sysdate)+1) > nvl(h_period2_pcd ,sysdate) AND
2251 DH.BOOK_TYPE_CODE = h_book AND
2252 DH.ASSET_ID = TH.ASSET_ID
2253 AND
2254 TH.ASSET_ID = AH.ASSET_ID AND
2255 TH.date_effective between ah.date_effective and nvl(ah.date_ineffective,sysdate)
2256 AND
2257 BOOKS.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
2258 nvl(BOOKS.DATE_INEFFECTIVE, SYSDATE) > TH.DATE_EFFECTIVE AND
2259 BOOKS.ASSET_ID = TH.ASSET_ID AND
2260 BOOKS.BOOK_TYPE_CODE = h_book
2261 AND
2262 CC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID AND
2263 RADJ.BOOK_TYPE_CODE = H_BOOK AND
2264 RADJ.ASSET_ID = TH.ASSET_ID AND
2265 RADJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
2266 RADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND
2267 RADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND
2268 RADJ.PERIOD_COUNTER_CREATED >= h_period1_pc AND
2269 radj.period_counter_created <= h_period2_pc and
2270 RADJ.ADJUSTMENT_TYPE = 'RESERVE'
2271 AND
2272 AD.ASSET_ID = TH.ASSET_ID
2273 AND
2274 EMP.PERSON_ID(+) = DH.ASSIGNED_TO
2275 AND
2276 TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+) AND EMP.EFFECTIVE_END_DATE(+)
2277 AND
2278 LOC.LOCATION_ID = DH.LOCATION_ID
2279 /*AND
2280 dp.period_counter = ds.period_counter and
2281 dp.book_type_code = ds.book_type_code*/
2282 AND
2283 DS.ASSET_ID (+) = BOOKS.ASSET_ID AND
2284 DS.BOOK_TYPE_CODE (+) = h_book AND
2285 DS.PERIOD_COUNTER (+) >= h_period1_pc and
2286 ds.period_counter(+) <= h_period2_pc
2287 GROUP BY
2288 cc.code_combination_id,
2289 EMP.FULL_NAME, emp.employee_number,
2290 loc.location_id,
2291 ah.category_id,
2292 AD.DESCRIPTION,
2293 DH.UNITS_ASSIGNED,
2294 AD.SERIAL_NUMBER,
2295 AD.TAG_NUMBER, ad.inventorial,
2296 AD.ASSET_NUMBER,
2297 BOOKS.LIFE_IN_MONTHS,
2298 BOOKS.ADJUSTED_RATE,
2299 BOOKS.PRODUCTION_CAPACITY,
2300 DS.BONUS_RATE,
2301 period; --dp.period_name;
2302
2303 begin
2304
2305 h_book := book;
2306 h_request_id := request_id;
2307 ctr := 0;
2308
2309 select fcr.last_update_login into h_login_id
2310 from fnd_concurrent_requests fcr
2311 where fcr.request_id = h_request_id;
2312
2313 h_mesg_name := 'FA_REC_SQL_ACCT_FLEX';
2314
2315 select nvl(distribution_source_book, book_type_code), accounting_flex_structure
2316 into h_dist_source_book, h_acct_flex_struct
2317 from fa_book_controls
2318 where book_type_code = h_book;
2319
2320 h_mesg_name := 'FA_FA_LOOKUP_IN_SYSTEM_CTLS';
2321
2322 select location_flex_structure, category_flex_structure
2323 into h_loc_flex_struct, h_cat_flex_struct
2324 from fa_system_controls;
2325
2326 h_mesg_name := 'FA_RX_SEGNUMS';
2327
2328 fa_rx_shared_pkg.GET_ACCT_SEGMENT_NUMBERS (
2329 BOOK => h_book,
2330 BALANCING_SEGNUM => h_bal_seg,
2331 ACCOUNT_SEGNUM => h_acct_seg,
2332 CC_SEGNUM => h_cost_seg,
2333 CALLING_FN => 'ADD_BY_PERIOD');
2334
2335 h_mesg_name := 'FA_AMT_SEL_PERIODS';
2336
2337 select period_counter, period_open_date
2338 into h_period1_pc, h_period1_pod
2339 from fa_deprn_periods
2340 where book_type_code = h_book and period_name = period;
2341
2342 select count(*) into h_count
2343 from fa_deprn_periods where period_name = period
2344 and book_type_code = h_book;
2345
2346 if (h_count > 0) then
2347 select period_counter, nvl(period_close_date,sysdate)
2348 into h_period2_pc, h_period2_pcd
2349 from fa_deprn_periods
2350 where book_type_code = h_book and period_name = period;
2351 else
2352 h_period2_pc := null;
2353 h_period2_pcd := null;
2354 end if;
2355
2356
2357 h_mesg_name := 'FA_DEPRN_SQL_DCUR';
2358
2359 open resp_additions;
2360 loop
2361
2362 h_mesg_name := 'FA_DEPRN_SQL_FCUR';
2363
2364 --SLA
2365
2366 --dev_debug(h_book);
2367 --dev_debug(to_char(h_period1_pc));
2368 --dev_debug(to_char(h_period2_pc));
2369 --dev_debug(to_char(h_period1_pod));
2370 --dev_debug(to_char(h_period2_pcd));
2371
2372 fetch resp_additions into
2373 h_ccid,
2374 h_emp_name, h_emp_number,
2375 h_location_id,
2376 h_category_id,
2377 h_asset_number,
2378 h_description,
2379 h_units,
2380 h_serial_number, h_tag_number, h_inventorial,
2381 h_life_months,
2382 h_adjusted_rate,
2383 h_prod_capacity,
2384 h_bonus_rate,
2385 h_cost_to_clear,
2386 h_reserve,
2387 h_invoice_flag,
2388 h_period_name;
2389
2390
2391 if (resp_additions%NOTFOUND) then
2392 --dev_debug('test1');
2393 exit; end if;
2394 --dev_debug('test2');
2395 ctr := ctr + 1;
2396
2397 mesg := 'concat_account';
2398
2399 h_mesg_name := 'FA_RX_CONCAT_SEGS';
2400 h_flex_error := 'GL#';
2401 h_ccid_error := h_ccid;
2402 --dev_debug('test3');
2403 fa_rx_shared_pkg.concat_acct (
2404 struct_id => h_acct_flex_struct,
2405 ccid => h_ccid,
2406 concat_string => h_concat_acct,
2407 segarray => h_acct_segs);
2408 --dev_debug('test4');
2409 --dev_debug(to_char(h_cost_seg));
2410 --dev_debug(to_char(begin_cc));
2411 --dev_debug(to_char(end_cc));
2412 if (h_acct_segs(h_cost_seg) >= begin_cc
2413 and h_acct_segs(h_cost_seg) <= end_cc) then
2414
2415 mesg := 'concat_location';
2416 --dev_debug('test5');
2417 h_flex_error := 'LOC#';
2418 h_ccid_error := h_location_id;
2419
2420 fa_rx_shared_pkg.concat_location (
2421 struct_id => h_loc_flex_struct,
2422 ccid => h_location_id,
2423 concat_string => h_concat_loc,
2424 segarray => h_loc_segs);
2425 --dev_debug('test6');
2426 select decode(h_life_months, null, null,
2427 to_char(floor(h_life_months/12)) || '.' ||
2428 to_char(mod(h_life_months,12)))
2429 into h_life_year_month
2430 from dual;
2431
2432 h_life_year_month_num := fnd_number.canonical_to_number(h_life_year_month);
2433
2434
2435
2436 mesg := 'concat_category';
2437
2438 h_flex_error := 'CAT#';
2439 h_ccid_error := h_category_id;
2440
2441 fa_rx_shared_pkg.concat_category (
2442 struct_id => h_cat_flex_struct,
2443 ccid => h_category_id,
2444 concat_string => h_concat_cat,
2445 segarray => h_cat_segs);
2446
2447
2448
2449 -- dynamic sql for major and minor category.
2450
2451 maj_select_column := null;
2452 min_select_column := null;
2453
2454 maj_select_column := fa_rx_flex_pkg.flex_sql(140,'CAT#',h_cat_flex_struct,'CAT','SELECT','BASED_CATEGORY');
2455 maj_select_column := maj_select_column || ' MAJOR_CATEGORY';
2456
2457 begin
2458 min_select_column := fa_rx_flex_pkg.flex_sql(140,'CAT#',h_cat_flex_struct,'CAT','SELECT','MINOR_CATEGORY');
2459 min_select_column := min_select_column || ' MINOR_CATEGORY';
2460 exception
2461 when others then
2462 min_select_column := 'NULL';
2463 end;
2464
2465 sql_stmt := 'select ' || maj_select_column || ' , ' || min_select_column ||
2466 ' from fa_categories cat where category_id = ' || h_category_id ;
2467
2468
2469 OPEN category_segments FOR sql_stmt;
2470 FETCH category_segments INTO
2471 h_major_category,
2472 h_minor_category;
2473 CLOSE category_segments;
2474
2475 --
2476
2477 mesg := 'getting_major_category_desc';
2478
2479 h_major_cat_desc :=
2480 fa_rx_flex_pkg.get_description(
2481 p_application_id => 140,
2482 p_id_flex_code => 'CAT#',
2483 p_id_flex_num => h_cat_flex_struct,
2484 p_qualifier => 'BASED_CATEGORY',
2485 p_data => h_major_category);
2486
2487
2488 mesg := 'getting_minor_category_desc';
2489 h_minor_cat_desc :=
2490 fa_rx_flex_pkg.get_description(
2491 p_application_id => 140,
2492 p_id_flex_code => 'CAT#',
2493 p_id_flex_num => h_cat_flex_struct,
2494 p_qualifier => 'MINOR_CATEGORY',
2495 p_data => h_minor_category);
2496
2497
2498
2499 h_mesg_name := 'FA_SHARED_INSERT_FAILED';
2500 --dev_debug('test7');
2501 --dev_debug(to_char(request_id));
2502 insert into fa_addition_rep_itf (
2503 request_id, company, cost_Center, expense_acct,
2504 asset_number, description, reserve,
2505 cost_to_clear, invoice_flag,
2506 life_year_month, prod_capacity, adjusted_rate,
2507 employee_name, employee_number, location,
2508 serial_number, tag_number, inventorial, period_name,
2509 last_update_date, creation_date, last_updated_by,
2510 last_update_login, created_by,
2511 category,
2512 major_category,
2513 minor_category,
2514 major_category_desc,
2515 minor_category_desc
2516 ) values (
2517 request_id, h_acct_segs(h_bal_seg),
2518 h_acct_segs(h_cost_seg), h_acct_segs(h_acct_seg),
2519 h_asset_number, h_description, h_reserve, h_cost_to_clear,
2520 h_invoice_flag, h_life_year_month_num, h_prod_capacity,
2521 h_adjusted_rate, h_emp_name, h_emp_number,
2522 h_concat_loc, h_serial_number, h_tag_number, h_inventorial,
2523 h_period_name, sysdate, sysdate,
2524 user_id, h_login_id, user_id,
2525 h_concat_cat,
2526 h_major_category,
2527 h_minor_category,
2528 h_major_cat_desc,
2529 h_minor_cat_desc);
2530
2531 end if; -- if cc between...
2532
2533 end loop;
2534
2535
2536 h_mesg_name := 'FA_DEPRN_SQL_CCUR';
2537
2538 close resp_additions;
2539
2540 exception when others then
2541 if SQLCODE <> 0 then
2542 fa_Rx_conc_mesg_pkg.log(SQLERRM);
2543 end if;
2544 fnd_message.set_name('OFA',h_mesg_name);
2545 if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
2546 fnd_message.set_token('TABLE','FA_ADDITION_REP_ITF',FALSE);
2547 end if;
2548 if h_mesg_name = 'FA_RX_CONCAT_SEGS' then
2549 fnd_message.set_token('CCID',to_char(h_ccid_error),FALSE);
2550 fnd_message.set_token('FLEX_CODE',h_flex_error,FALSE);
2551 end if;
2552
2553 h_mesg_str := fnd_message.get;
2554 fa_rx_conc_mesg_pkg.log(h_mesg_str);
2555 retcode := 2;
2556
2557 end add_by_resp;
2558
2559
2560 END FARX_AD;