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