[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_REC_UTILS_PKG
Source
1 PACKAGE BODY FA_MASS_REC_UTILS_PKG AS
2 /* $Header: FAXMRUTB.pls 120.3 2005/07/28 00:15:46 tkawamur ship $ */
3
4
5 /*====================================================================================+
6 | PROCEDURE Convert_Formats |
7 +=====================================================================================*/
8
9 PROCEDURE Convert_Formats(
10 X_Life_In_Months IN NUMBER := NULL,
11 X_Basic_Rate IN NUMBER := NULL,
12 X_Adjusted_Rate IN NUMBER := NULL,
13 X_Allowed_Deprn_Limit IN NUMBER := NULL,
14 X_Percent_Salvage_Val IN NUMBER := NULL,
15 X_Life OUT NOCOPY VARCHAR2,
16 X_Basic_Rate_Pct OUT NOCOPY NUMBER,
17 X_Adjusted_Rate_Pct OUT NOCOPY NUMBER,
18 X_Deprn_Limit_Pct OUT NOCOPY NUMBER,
19 X_Salvage_Val_Pct OUT NOCOPY NUMBER,
20 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
21 BEGIN
22 IF X_Life_In_Months IS NOT NULL THEN
23 X_Life := lpad(to_char(trunc(X_Life_In_Months/12)), 3)||'.'||
24 substr(to_char(mod(X_Life_In_Months, 12), '00'), 2, 2);
25 -- Need to get the substring from the second position, since
26 -- to_char conversion with the format, always attaches extra space
27 -- at the beginning of the string.
28 ELSE
29 X_Life := NULL;
30 END IF;
31
32 IF X_Basic_Rate IS NOT NULL THEN
33 X_Basic_Rate_Pct := round(X_Basic_Rate*100, 2);
34 /* May use the following format in report output:
35 substr(to_char(round(X_Basic_Rate*100, 2), '999.99'), 2, 6) or
36 lpad(to_char(round(X_Basic_Rate*100, 2)), 6) */
37 ELSE
38 X_Basic_Rate_Pct := NULL;
39 END IF;
40
41 IF X_Adjusted_Rate IS NOT NULL THEN
42 X_Adjusted_Rate_Pct := round(X_Adjusted_Rate*100, 2);
43 ELSE
44 X_Adjusted_Rate_Pct := NULL;
45 END IF;
46
47 IF X_Allowed_Deprn_Limit IS NOT NULL THEN
48 X_Deprn_Limit_Pct := round(X_Allowed_Deprn_limit*100, 2);
49 ELSE
50 X_Deprn_Limit_Pct := NULL;
51 END IF;
52
53 IF X_Percent_Salvage_Val IS NOT NULL THEN
54 X_Salvage_Val_Pct := round(X_Percent_Salvage_Val*100, 2);
55 ELSE
56 X_Salvage_Val_Pct := NULL;
57 END IF;
58 EXCEPTION
59 WHEN OTHERS THEN
60 FA_SRVR_MSG.Add_SQL_Error(
61 CALLING_FN => 'FA_MASS_REC_UTILS_PKG.Convert_Formats'
62 ,p_log_level_rec => p_log_level_rec);
63 raise; -- raise the exception.
64 END Convert_Formats;
65
66
67 /*====================================================================================+
68 | PROCEDURE Load_Conversion_Table |
69 +=====================================================================================*/
70
71 PROCEDURE Load_Conversion_Table (p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
72 IS
73 load_exc EXCEPTION;
74 h_life_in_months NUMBER(4);
75 h_basic_rate NUMBER;
76 h_adjusted_rate NUMBER;
77 h_allowed_deprn_limit NUMBER;
78 h_percent_salvage_val NUMBER;
79 BEGIN
80 IF (FA_LOAD_TBL_PKG.g_deprn_count = 0) THEN
81 -- deprn_table is not loaded. deprn_table has to be loaded first.
82 raise load_exc;
83 END IF;
84
85 -- Initialize conversion_table.
86 conv_tbl.delete;
87
88 FOR i IN FA_LOAD_TBL_PKG.deprn_table.FIRST .. FA_LOAD_TBL_PKG.deprn_table.LAST
89 LOOP
90 IF FA_LOAD_TBL_PKG.deprn_table.exists(i) THEN
91 -- Load into exactly same matching index postion.
92 conv_tbl(i).book_type_code := FA_LOAD_TBL_PKG.deprn_table(i).book_type_code;
93 conv_tbl(i).start_dpis := FA_LOAD_TBL_PKG.deprn_table(i).start_dpis;
94 conv_tbl(i).end_dpis := FA_LOAD_TBL_PKG.deprn_table(i).end_dpis;
95 h_life_in_months := FA_LOAD_TBL_PKG.deprn_table(i).life_in_months;
96 h_basic_rate := FA_LOAD_TBL_PKG.deprn_table(i).basic_rate;
97 h_adjusted_rate := FA_LOAD_TBL_PKG.deprn_table(i).adjusted_rate;
98 h_allowed_deprn_limit := FA_LOAD_TBL_PKG.deprn_table(i).allow_deprn_limit;
99 h_percent_salvage_val := FA_LOAD_TBL_PKG.deprn_table(i).percent_salvage_value;
100 Convert_Formats(
101 X_Life_In_Months => h_life_in_months,
102 X_Basic_Rate => h_basic_rate,
103 X_Adjusted_Rate => h_adjusted_rate,
104 X_Allowed_Deprn_Limit => h_allowed_deprn_limit,
105 X_Percent_Salvage_Val => h_percent_salvage_val,
106 X_Life => conv_tbl(i).life,
107 X_Basic_Rate_Pct => conv_tbl(i).basic_rate_pct,
108 X_Adjusted_Rate_Pct => conv_tbl(i).adjusted_rate_pct,
109 X_Deprn_Limit_Pct => conv_tbl(i).deprn_limit_pct,
110 X_Salvage_Val_Pct => conv_tbl(i).salvage_val_pct);
111 END IF;
112 END LOOP;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 FA_SRVR_MSG.Add_SQL_Error(
117 CALLING_FN => 'FA_MASS_REC_UTILS_PKG.Load_Conversion_Table'
118 ,p_log_level_rec => p_log_level_rec);
119 raise;
120 END Load_Conversion_Table;
121
122
123 /*====================================================================================+
124 | PROCEDURE Insert_Itf |
125 +=====================================================================================*/
126
127 PROCEDURE Insert_Itf(
128 X_Report_Type IN VARCHAR2,
129 X_Request_Id IN NUMBER,
130 X_Mass_Reclass_Id IN NUMBER,
131 X_Asset_Rec IN ASSET_REC,
132 X_New_Category IN VARCHAR2 := NULL,
133 X_Last_Update_Date IN DATE,
134 X_Last_Updated_By IN NUMBER,
135 X_Created_By IN NUMBER,
136 X_Creation_Date IN DATE,
137 X_Last_Update_Login IN NUMBER,
138 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
139 -- cursor to fetch the meaning for depreciate flag.
140 CURSOR get_deprn_flag IS
141 SELECT nvl(meaning, X_Asset_Rec.depreciate_flag)
142 FROM fa_lookups
143 WHERE lookup_code = X_Asset_Rec.depreciate_flag
144 AND lookup_type = 'YESNO';
145 h_deprn_flag_mean VARCHAR2(80);
146 BEGIN
147 -- Insert the meaning, not the lookup code for the depreciate flag.
148 OPEN get_deprn_flag;
149 FETCH get_deprn_flag INTO h_deprn_flag_mean;
150 CLOSE get_deprn_flag;
151
152 IF (X_Report_Type = 'PREVIEW') THEN
153 INSERT INTO fa_mass_reclass_itf (
154 request_id, mass_reclass_id,
155 asset_id, asset_number,
156 description, book,
157 old_category, new_category,
158 convention, ceiling, method,
159 life, basic_rate, adjusted_rate,
160 bonus_rule, production_capacity, unit_of_measure,
161 depreciate_flag, deprn_limit_percentage,
162 deprn_limit_amount, salvage_val_percentage,
163 cost_acct, deprn_reserve_acct,
164 last_update_date, last_updated_by, created_by,
165 creation_date, last_update_login
166 )
167 VALUES (
168 X_Request_Id, X_Mass_Reclass_Id,
169 X_Asset_Rec.asset_id, X_Asset_Rec.asset_number,
170 X_Asset_Rec.description, X_Asset_Rec.book_type_code,
171 X_Asset_Rec.category, X_New_Category,
172 X_Asset_Rec.convention, X_Asset_Rec.ceiling, X_Asset_Rec.method,
173 fnd_number.canonical_to_number(X_Asset_Rec.life),
174 X_Asset_Rec.basic_rate_pct, X_Asset_Rec.adjusted_rate_pct,
175 X_Asset_Rec.bonus_rule, X_Asset_Rec.capacity, X_Asset_Rec.unit_of_measure,
176 h_deprn_flag_mean, X_Asset_Rec.deprn_limit_pct,
177 X_Asset_Rec.deprn_limit_amt, X_Asset_Rec.salvage_val_pct,
178 X_Asset_Rec.cost_acct, X_Asset_Rec.deprn_rsv_acct,
179 X_Last_Update_Date, X_Last_Updated_By, X_Created_By,
180 X_Creation_Date, X_Last_Update_Login
181 );
182 ELSIF (X_Report_Type = 'REVIEW') THEN
183 INSERT INTO fa_mass_reclass_itf (
184 request_id, mass_reclass_id,
185 asset_id, asset_number,
186 description, book,
187 old_category, new_category,
188 convention, ceiling, method,
189 life, basic_rate, adjusted_rate,
190 bonus_rule, production_capacity, unit_of_measure,
191 depreciate_flag, deprn_limit_percentage,
192 deprn_limit_amount, salvage_val_percentage,
193 cost_acct, deprn_reserve_acct,
194 last_update_date, last_updated_by, created_by,
195 creation_date, last_update_login
196 )
197 VALUES (
198 X_Request_Id, X_Mass_Reclass_Id,
199 X_Asset_Rec.asset_id, X_Asset_Rec.asset_number,
200 X_Asset_Rec.description, X_Asset_Rec.book_type_code,
201 NULL, X_Asset_Rec.category,
202 X_Asset_Rec.convention, X_Asset_Rec.ceiling, X_Asset_Rec.method,
203 fnd_number.canonical_to_number(X_Asset_Rec.life),
204 X_Asset_Rec.basic_rate_pct, X_Asset_Rec.adjusted_rate_pct,
205 X_Asset_Rec.bonus_rule, X_Asset_Rec.capacity, X_Asset_Rec.unit_of_measure,
206 h_deprn_flag_mean, X_Asset_Rec.deprn_limit_pct,
207 X_Asset_Rec.deprn_limit_amt, X_Asset_Rec.salvage_val_pct,
208 X_Asset_Rec.cost_acct, X_Asset_Rec.deprn_rsv_acct,
209 X_Last_Update_Date, X_Last_Updated_By, X_Created_By,
210 X_Creation_Date, X_Last_Update_Login
211 );
212 END IF;
213 EXCEPTION
214 WHEN OTHERS THEN
215 FA_SRVR_MSG.Add_Message(
216 CALLING_FN => 'FA_MASS_REC_UTILS_PKG.Insert_Itf',
217 NAME => 'FA_SHARED_INSERT_FAILED',
218 TOKEN1 => 'FAILED',
219 VALUE1 => 'FA_MASS_RECLASS_ITF'
220 ,p_log_level_rec => p_log_level_rec);
221 FA_SRVR_MSG.Add_SQL_Error(
222 CALLING_FN => 'FA_MASS_REC_UTILS_PKG.Insert_Itf'
223 ,p_log_level_rec => p_log_level_rec);
224 raise;
225 END Insert_Itf;
226
227
228 /*====================================================================================+
229 | PROCEDURE Get_Selection_Criteria |
230 +=====================================================================================*/
231
232 PROCEDURE Get_Selection_Criteria(
233 X_Mass_Reclass_ID IN NUMBER,
234 X_Book_Type_Code OUT NOCOPY VARCHAR2,
235 X_Asset_Type OUT NOCOPY VARCHAR2,
236 X_Fully_Rsvd OUT NOCOPY VARCHAR2,
237 X_From_Cost OUT NOCOPY NUMBER,
238 X_To_Cost OUT NOCOPY NUMBER,
239 X_From_Asset OUT NOCOPY VARCHAR2,
240 X_To_Asset OUT NOCOPY VARCHAR2,
241 X_From_Dpis OUT NOCOPY DATE,
242 X_To_Dpis OUT NOCOPY DATE,
243 X_Location OUT NOCOPY VARCHAR2,
244 X_Employee_Name OUT NOCOPY VARCHAR2,
245 X_Employee_Number OUT NOCOPY VARCHAR2,
246 X_Old_Category OUT NOCOPY VARCHAR2,
247 X_New_Category OUT NOCOPY VARCHAR2,
248 X_Asset_Key OUT NOCOPY VARCHAR2,
249 X_From_Exp_Acct OUT NOCOPY VARCHAR2,
250 X_To_Exp_Acct OUT NOCOPY VARCHAR2,
251 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
252
253 mr_rec mass_reclass_rec;
254 precis NUMBER;
255
256 -- cursor to get mass reclass record.
257 CURSOR mass_reclass IS
258 SELECT mr.book_type_code, mr.asset_type,
259 mr.include_fully_rsvd_flag,
260 mr.from_cost, mr.to_cost,
261 mr.from_asset_number, mr.to_asset_number,
262 mr.from_date_placed_in_service, mr.to_date_placed_in_service,
263 mr.location_id, mr.employee_id,
264 mr.from_category_id, mr.to_category_id, mr.asset_key_id,
265 mr.segment1_low, mr.segment2_low, mr.segment3_low,
266 mr.segment4_low, mr.segment5_low, mr.segment6_low,
267 mr.segment7_low, mr.segment8_low, mr.segment9_low,
268 mr.segment10_low, mr.segment11_low, mr.segment12_low,
269 mr.segment13_low, mr.segment14_low, mr.segment15_low,
270 mr.segment16_low, mr.segment17_low, mr.segment18_low,
271 mr.segment19_low, mr.segment20_low, mr.segment21_low,
272 mr.segment22_low, mr.segment23_low, mr.segment24_low,
273 mr.segment25_low, mr.segment26_low, mr.segment27_low,
274 mr.segment28_low, mr.segment29_low, mr.segment30_low,
275 mr.segment1_high, mr.segment2_high, mr.segment3_high,
276 mr.segment4_high, mr.segment5_high, mr.segment6_high,
277 mr.segment7_high, mr.segment8_high, mr.segment9_high,
278 mr.segment10_high, mr.segment11_high, mr.segment12_high,
279 mr.segment13_high, mr.segment14_high, mr.segment15_high,
280 mr.segment16_high, mr.segment17_high, mr.segment18_high,
281 mr.segment19_high, mr.segment20_high, mr.segment21_high,
282 mr.segment22_high, mr.segment23_high, mr.segment24_high,
283 mr.segment25_high, mr.segment26_high, mr.segment27_high,
284 mr.segment28_high, mr.segment29_high, mr.segment30_high
285 FROM fa_mass_reclass mr
286 WHERE mr.mass_reclass_id = X_Mass_Reclass_Id;
287
288 -- cursor to get precision.
289 CURSOR get_precision IS
290 SELECT curr.precision
291 FROM fnd_currencies curr, gl_sets_of_books sob,
292 fa_book_controls bc
293 WHERE curr.currency_code = sob.currency_code
294 AND sob.set_of_books_id = bc.set_of_books_id
295 AND bc.book_type_code = mr_rec.book_type_code;
296
297 -- cursor to get flex structures.
298 CURSOR get_flex_structs IS
299 SELECT category_flex_structure, location_flex_structure,
300 asset_key_flex_structure
301 FROM fa_system_controls;
302
303 -- cursor to get employee.
304 CURSOR get_employee IS
305 SELECT name, employee_number
306 FROM fa_employees
307 WHERE employee_id = mr_rec.employee_id;
308
309 -- cursor to get accounting flexfield structure
310 CURSOR get_acct_flex_struct IS
311 SELECT accounting_flex_structure FROM fa_book_controls
312 WHERE book_type_code = mr_rec.book_type_code;
313
314 -- cursor to get gl_code_combinations table id
315 CURSOR get_gl_table_id IS
316 SELECT table_id FROM fnd_tables
317 WHERE table_name = 'GL_CODE_COMBINATIONS' AND application_id = 101;
318
319 h_acct_flex_struct NUMBER;
320 -- cursor to get the delimiter value between accounting flexfield segments
321 CURSOR get_delim IS
322 SELECT s.concatenated_segment_delimiter
323 FROM fnd_id_flex_structures s, fnd_application a
324 WHERE s.application_id = a.application_id
325 AND s.id_flex_code = 'GL#'
326 AND s.id_flex_num = h_acct_flex_struct
327 AND a.application_short_name = 'SQLGL';
328
329 h_table_id NUMBER;
330 -- cursor to figure out number of segments used for the accounting flexfield
331 CURSOR segcolumns IS
332 SELECT distinct g.application_column_name, g.segment_num
333 FROM fnd_columns c, fnd_id_flex_segments g
334 WHERE g.application_id = 101
335 AND g.id_flex_code = 'GL#'
336 AND g.id_flex_num = h_acct_flex_struct
337 AND g.enabled_flag = 'Y'
338 AND c.application_id = 101
339 AND c.table_id = h_table_id
340 AND c.column_name = g.application_column_name;
341 h_asset_type VARCHAR2(11) := NULL;
342 h_fully_rsvd VARCHAR2(3) := NULL;
343 -- cursors to get meanings for asset type and fully reserved flag.
344 CURSOR get_asset_type_mean IS
345 SELECT nvl(meaning, h_asset_type)
346 FROM fa_lookups
347 WHERE lookup_code = h_asset_type
348 AND lookup_type = 'ASSET TYPE';
349 CURSOR get_fully_rsvd_mean IS
350 SELECT nvl(meaning, h_fully_rsvd)
351 FROM fa_lookups
352 WHERE lookup_code = h_fully_rsvd
353 AND lookup_type = 'YESNO';
354
355 delim VARCHAR2(1);
356 col_name VARCHAR2(25);
357 v_return INTEGER;
358 num_segs NUMBER := 0;
359 segarr FA_RX_SHARED_PKG.Seg_Array;
360 h_cat_flex_struct NUMBER;
361 h_loc_flex_struct NUMBER;
362 h_key_flex_struct NUMBER;
363 -- from/to expense accounts in concatenated strings
364 from_acct_concat VARCHAR2(780) := '';
365 to_acct_concat VARCHAR2(780) := '';
366 BEGIN
367 -- Get mass reclass record.
368 OPEN mass_reclass;
369 FETCH mass_reclass
370 INTO mr_rec.book_type_code, h_asset_type,
371 h_fully_rsvd,
372 mr_rec.from_cost, mr_rec.to_cost,
373 X_From_Asset, X_To_Asset,
374 X_From_Dpis, X_To_Dpis,
375 mr_rec.location_id, mr_rec.employee_id,
376 mr_rec.from_category_id, mr_rec.to_category_id, mr_rec.asset_key_id,
377 mr_rec.segment1_low, mr_rec.segment2_low, mr_rec.segment3_low,
378 mr_rec.segment4_low, mr_rec.segment5_low, mr_rec.segment6_low,
379 mr_rec.segment7_low, mr_rec.segment8_low, mr_rec.segment9_low,
380 mr_rec.segment10_low, mr_rec.segment11_low, mr_rec.segment12_low,
381 mr_rec.segment13_low, mr_rec.segment14_low, mr_rec.segment15_low,
382 mr_rec.segment16_low, mr_rec.segment17_low, mr_rec.segment18_low,
383 mr_rec.segment19_low, mr_rec.segment20_low, mr_rec.segment21_low,
384 mr_rec.segment22_low, mr_rec.segment23_low, mr_rec.segment24_low,
385 mr_rec.segment25_low, mr_rec.segment26_low, mr_rec.segment27_low,
386 mr_rec.segment28_low, mr_rec.segment29_low, mr_rec.segment30_low,
387 mr_rec.segment1_high, mr_rec.segment2_high, mr_rec.segment3_high,
388 mr_rec.segment4_high, mr_rec.segment5_high, mr_rec.segment6_high,
389 mr_rec.segment7_high, mr_rec.segment8_high, mr_rec.segment9_high,
390 mr_rec.segment10_high, mr_rec.segment11_high, mr_rec.segment12_high,
391 mr_rec.segment13_high, mr_rec.segment14_high, mr_rec.segment15_high,
392 mr_rec.segment16_high, mr_rec.segment17_high, mr_rec.segment18_high,
393 mr_rec.segment19_high, mr_rec.segment20_high, mr_rec.segment21_high,
394 mr_rec.segment22_high, mr_rec.segment23_high, mr_rec.segment24_high,
395 mr_rec.segment25_high, mr_rec.segment26_high, mr_rec.segment27_high,
396 mr_rec.segment28_high, mr_rec.segment29_high, mr_rec.segment30_high;
397 CLOSE mass_reclass;
398
399 -- Get meanings for asset type and fully reserved flag.
400 OPEN get_asset_type_mean;
401 FETCH get_asset_type_mean INTO X_Asset_Type;
402 CLOSE get_asset_type_mean;
403
404 OPEN get_fully_rsvd_mean;
405 FETCH get_fully_rsvd_mean INTO X_Fully_Rsvd;
406 CLOSE get_fully_rsvd_mean;
407
408 -- Get precision.
409 OPEN get_precision;
410 FETCH get_precision INTO precis;
411 IF get_precision%NOTFOUND THEN
412 precis := 2;
413 END IF;
414 CLOSE get_precision;
415
416 -- Get flex structures
417 OPEN get_flex_structs;
418 FETCH get_flex_structs
419 INTO h_cat_flex_struct, h_loc_flex_struct, h_key_flex_struct;
420 CLOSE get_flex_structs;
421
422 -- Get old and new category.
423 IF mr_rec.from_category_id IS NOT NULL THEN
424 FA_RX_SHARED_PKG.Concat_Category (
425 struct_id => h_cat_flex_struct,
426 ccid => mr_rec.from_category_id,
427 concat_string => X_Old_Category,
428 segarray => segarr);
429 ELSE
430 X_Old_Category := NULL;
431 END IF;
432
433 FA_RX_SHARED_PKG.Concat_Category (
434 struct_id => h_cat_flex_struct,
435 ccid => mr_rec.to_category_id,
436 concat_string => X_New_Category,
437 segarray => segarr);
438
439 -- Get location in concatenated string.
440 IF mr_rec.location_id IS NOT NULL THEN
441 FA_RX_SHARED_PKG.Concat_Location (
442 struct_id => h_loc_flex_struct,
443 ccid => mr_rec.location_id,
444 concat_string => X_Location,
445 segarray => segarr);
446 ELSE
447 X_Location := NULL;
448 END IF;
449
450 -- Get asset key.
451 IF mr_rec.asset_key_id IS NOT NULL THEN
452 FA_RX_SHARED_PKG.Concat_Asset_Key (
453 struct_id => h_key_flex_struct,
454 ccid => mr_rec.asset_key_id,
455 concat_string => X_Asset_Key,
456 segarray => segarr);
457 ELSE
458 X_Asset_Key := NULL;
459 END IF;
460
461 -- Get employee information.
462 IF mr_rec.employee_id IS NOT NULL THEN
463 OPEN get_employee;
464 FETCH get_employee INTO X_Employee_Name, X_Employee_Number;
465 CLOSE get_employee;
466 ELSE
467 X_Employee_Name := NULL;
468 X_Employee_Number := NULL;
469 END IF;
470
471 -- Get accounting flexfield structure for expense account selection
472 -- criteria report output.
473 OPEN get_acct_flex_struct;
474 FETCH get_acct_flex_struct INTO h_acct_flex_struct;
475 CLOSE get_acct_flex_struct;
476
477 OPEN get_gl_table_id;
478 FETCH get_gl_table_id INTO h_table_id;
479 CLOSE get_gl_table_id;
480
481 OPEN get_delim;
482 FETCH get_delim INTO delim;
483 CLOSE get_delim;
484
485 -- Get number of segments for the accounting flexfield structure.
486 -- (Number of segments in use varies among books.)
487 OPEN segcolumns;
488 LOOP
489 FETCH segcolumns INTO col_name, v_return;
490 IF (segcolumns%NOTFOUND) THEN EXIT; END IF;
491 num_segs := num_segs + 1;
492 END LOOP;
493
494 -- Get low segment values.
495 segarr(1) := mr_rec.segment1_low;
496 segarr(2) := mr_rec.segment2_low;
497 segarr(3) := mr_rec.segment3_low;
498 segarr(4) := mr_rec.segment4_low;
499 segarr(5) := mr_rec.segment5_low;
500 segarr(6) := mr_rec.segment6_low;
501 segarr(7) := mr_rec.segment7_low;
502 segarr(8) := mr_rec.segment8_low;
503 segarr(9) := mr_rec.segment9_low;
504 segarr(10) := mr_rec.segment10_low;
505 segarr(11) := mr_rec.segment11_low;
506 segarr(12) := mr_rec.segment12_low;
507 segarr(13) := mr_rec.segment13_low;
508 segarr(14) := mr_rec.segment14_low;
509 segarr(15) := mr_rec.segment15_low;
510 segarr(16) := mr_rec.segment16_low;
511 segarr(17) := mr_rec.segment17_low;
512 segarr(18) := mr_rec.segment18_low;
513 segarr(19) := mr_rec.segment19_low;
514 segarr(20) := mr_rec.segment20_low;
515 segarr(21) := mr_rec.segment21_low;
516 segarr(22) := mr_rec.segment22_low;
517 segarr(23) := mr_rec.segment23_low;
518 segarr(24) := mr_rec.segment24_low;
519 segarr(25) := mr_rec.segment25_low;
520 segarr(26) := mr_rec.segment26_low;
521 segarr(27) := mr_rec.segment27_low;
522 segarr(28) := mr_rec.segment28_low;
523 segarr(29) := mr_rec.segment29_low;
524 segarr(30) := mr_rec.segment30_low;
525
526 -- Get From Expense Account in concatenated string.
527 FOR seg_ctr IN 1 .. num_segs-1 LOOP
528 from_acct_concat := from_acct_concat || segarr(seg_ctr) || delim;
529 END LOOP;
530 from_acct_concat := from_acct_concat || segarr(num_segs);
531
532 -- Get high segment values.
533 segarr(1) := mr_rec.segment1_high;
534 segarr(2) := mr_rec.segment2_high;
535 segarr(3) := mr_rec.segment3_high;
536 segarr(4) := mr_rec.segment4_high;
537 segarr(5) := mr_rec.segment5_high;
538 segarr(6) := mr_rec.segment6_high;
539 segarr(7) := mr_rec.segment7_high;
540 segarr(8) := mr_rec.segment8_high;
541 segarr(9) := mr_rec.segment9_high;
542 segarr(10) := mr_rec.segment10_high;
543 segarr(11) := mr_rec.segment11_high;
544 segarr(12) := mr_rec.segment12_high;
545 segarr(13) := mr_rec.segment13_high;
546 segarr(14) := mr_rec.segment14_high;
547 segarr(15) := mr_rec.segment15_high;
548 segarr(16) := mr_rec.segment16_high;
549 segarr(17) := mr_rec.segment17_high;
550 segarr(18) := mr_rec.segment18_high;
551 segarr(19) := mr_rec.segment19_high;
552 segarr(20) := mr_rec.segment20_high;
553 segarr(21) := mr_rec.segment21_high;
554 segarr(22) := mr_rec.segment22_high;
555 segarr(23) := mr_rec.segment23_high;
556 segarr(24) := mr_rec.segment24_high;
557 segarr(25) := mr_rec.segment25_high;
558 segarr(26) := mr_rec.segment26_high;
559 segarr(27) := mr_rec.segment27_high;
560 segarr(28) := mr_rec.segment28_high;
561 segarr(29) := mr_rec.segment29_high;
562 segarr(30) := mr_rec.segment30_high;
563
564 -- Get To Expense Account in concatenated string.
565 FOR seg_ctr IN 1 .. num_segs-1 LOOP
566 to_acct_concat := to_acct_concat || segarr(seg_ctr) || delim;
567 END LOOP;
568 to_acct_concat := to_acct_concat || segarr(num_segs);
569
570 X_Book_Type_Code := mr_rec.book_type_code;
571 IF mr_rec.from_cost IS NOT NULL THEN
572 X_From_Cost := round(mr_rec.from_cost, precis);
573 ELSE
574 X_From_Cost := NULL;
575 END IF;
576 IF mr_rec.to_cost IS NOT NULL THEN
577 X_To_Cost := round(mr_rec.to_cost, precis);
578 ELSE
579 X_To_Cost := NULL;
580 END IF;
581 X_From_Exp_Acct := from_acct_concat;
582 X_To_Exp_Acct := to_acct_concat;
583
584 EXCEPTION
585 WHEN NO_DATA_FOUND THEN
586 X_Book_Type_Code := NULL;
587 X_Asset_Type := NULL;
588 X_Fully_Rsvd := NULL;
589 X_From_Cost := NULL;
590 X_To_Cost := NULL;
591 X_From_Asset := NULL;
592 X_To_Asset := NULL;
593 X_From_Dpis := NULL;
594 X_To_Dpis := NULL;
595 X_Location := NULL;
596 X_Employee_Name := NULL;
597 X_Employee_Number := NULL;
598 X_Old_Category := NULL;
599 X_New_Category := NULL;
600 X_Asset_Key := NULL;
601 X_From_Exp_Acct := NULL;
602 X_To_Exp_Acct := NULL;
603 END Get_Selection_Criteria;
604
605
606 /*====================================================================================+
607 | PROCEDURE Compare_Cat_Major_Segs |
608 +=====================================================================================*/
609
610 PROCEDURE Compare_Cat_Major_Segs(
611 X_Category_Id1 IN NUMBER,
612 X_Category_Id2 IN NUMBER,
613 X_Same_Values OUT NOCOPY VARCHAR2,
614 X_Return_Status OUT NOCOPY BOOLEAN,
615 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
616 CURSOR get_cat_flex_struct IS
617 SELECT category_flex_structure
618 FROM FA_SYSTEM_CONTROLS;
619 l_cat_flex_struct NUMBER;
620 l_gsval BOOLEAN;
621 l_bal_segnum NUMBER;
622 l_numof_segs NUMBER;
623 l_all_segs_old FND_FLEX_EXT.SegmentArray;
624 l_all_segs_new FND_FLEX_EXT.SegmentArray;
625 l_old_bal_seg VARCHAR2(50);
626 l_new_bal_seg VARCHAR2(50);
627 compare_failure EXCEPTION;
628 BEGIN
629 -- Get major category segment values.
630 OPEN get_cat_flex_struct;
631 FETCH get_cat_flex_struct INTO l_cat_flex_struct;
632 CLOSE get_cat_flex_struct;
633
634 -- Get the segment number for the major category.
635 l_gsval := FND_FLEX_APIS.Get_Qualifier_Segnum (
636 appl_id => 140 ,
637 key_flex_code => 'CAT#',
638 structure_number => l_cat_flex_struct,
639 flex_qual_name => 'BASED_CATEGORY',
640 segment_number => l_bal_segnum);
641
642 IF NOT l_gsval THEN
643 raise compare_failure;
644 END IF;
645
646 -- Get the segment values for the old category.
647 l_gsval := FND_FLEX_EXT.Get_Segments(
648 application_short_name => 'OFA',
649 key_flex_code => 'CAT#',
650 structure_number => l_cat_flex_struct,
651 combination_id => X_Category_Id1,
652 n_segments => l_numof_segs,
653 segments => l_all_segs_old);
654
655 IF NOT l_gsval THEN
656 raise compare_failure;
657 END IF;
658
659 -- Get the old major category segment value.
660 l_old_bal_seg := l_all_segs_old(l_bal_segnum);
661
662 -- Get the segment values for the new category.
663 l_gsval := FND_FLEX_EXT.Get_Segments(
664 application_short_name => 'OFA',
665 key_flex_code => 'CAT#',
666 structure_number => l_cat_flex_struct,
667 combination_id => X_Category_Id2,
668 n_segments => l_numof_segs,
669 segments => l_all_segs_new);
670
671 IF NOT l_gsval THEN
672 raise compare_failure;
673 END IF;
674
675 -- Get the new major category segment value.
676 l_new_bal_seg := l_all_segs_new(l_bal_segnum);
677
678 -- Update only the necessary columns.
679 IF (l_old_bal_seg = l_new_bal_seg) THEN
680 X_Same_Values := 'YES';
681 ELSE
682 X_Same_Values := 'NO';
683 END IF;
684
685 X_Return_Status := TRUE;
686
687 EXCEPTION
688 WHEN compare_failure THEN
689 X_Same_Values := 'NO';
690 X_Return_Status := FALSE;
691 FA_SRVR_MSG.Add_Message(
692 CALLING_FN => 'FA_MASS_REC_UTILS_PKG.Compare_Cat_Major_Segs',
693 NAME => 'FA_REC_GET_CATSEG_FAILED'
694 ,p_log_level_rec => p_log_level_rec);
695 -- Message: 'Failed to get category segments.'
696 raise;
697 WHEN OTHERS THEN
698 X_Same_Values := 'NO';
699 X_Return_Status := FALSE;
700 FA_SRVR_MSG.Add_SQL_Error(
701 CALLING_FN => 'FA_MASS_REC_UTILS_PKG.Compare_Cat_Major_Segs'
702 ,p_log_level_rec => p_log_level_rec);
703 raise;
704 END Compare_Cat_Major_Segs;
705
706
707 END FA_MASS_REC_UTILS_PKG;