[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_TRANSFERS_PKG
Source
1 PACKAGE BODY FA_MASS_TRANSFERS_PKG AS
2 /* $Header: FAXMTFRB.pls 120.6 2009/03/27 04:16:05 bridgway ship $ */
3
4
5 /** The following describes what this function expects as parameters
6 x_mass_transfer_id valid mass transfer id
7 x_from_glccid valid expense account
8 x_to_glccid null or -99 **/
9
10 function famtgcc ( x_mass_transfer_id in number,
11 x_from_glccid in number,
12 x_to_glccid in out nocopy number , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
13
14 v_chart_of_accounts_id number(15);
15 v_nsegs number;
16 v_to_conc_segs varchar2(2000) := null;
17 v_delimiter varchar2(1);
18 v_message varchar2(512);
19
20 v_to_segarray FND_FLEX_EXT.SEGMENTARRAY;
21 v_from_segarray FND_FLEX_EXT.SEGMENTARRAY;
22
23 flex_error EXCEPTION;
24 comb_error EXCEPTION;
25
26 CURSOR c_mtfr is
27 select book_type_code,
28 to_gl_ccid,
29 segment1,
30 segment2,
31 segment3,
32 segment4,
33 segment5,
34 segment6,
35 segment7,
36 segment8,
37 segment9,
38 segment10,
39 segment11,
40 segment12,
41 segment13,
42 segment14,
43 segment15,
44 segment16,
45 segment17,
46 segment18,
47 segment19,
48 segment20,
49 segment21,
50 segment22,
51 segment23,
52 segment24,
53 segment25,
54 segment26,
55 segment27,
56 segment28,
57 segment29,
58 segment30
59 from fa_mass_transfers
60 where mass_transfer_id = x_mass_transfer_id;
61
62 mtfr_rec c_mtfr%ROWTYPE := null;
63
64
65 CURSOR C_glcc IS
66 select nvl(glcc.code_combination_id, -99)
67 from gl_code_combinations glcc,
68 ( select * from gl_code_combinations
69 where code_combination_id = x_from_glccid ) from_glcc
70 where glcc.segment1 = nvl(mtfr_rec.segment1, from_glcc.segment1)
71 and glcc.segment2 = nvl(mtfr_rec.segment2, from_glcc.segment2)
72 and glcc.segment3 = nvl(mtfr_rec.segment3, from_glcc.segment3)
73 and glcc.segment4 = nvl(mtfr_rec.segment4, from_glcc.segment4)
74 and glcc.segment5 = nvl(mtfr_rec.segment5, from_glcc.segment5)
75 and glcc.segment6 = nvl(mtfr_rec.segment6, from_glcc.segment6)
76 and glcc.segment7 = nvl(mtfr_rec.segment7, from_glcc.segment7)
77 and glcc.segment8 = nvl(mtfr_rec.segment8, from_glcc.segment8)
78 and glcc.segment9 = nvl(mtfr_rec.segment9, from_glcc.segment9)
79 and glcc.segment10 = nvl(mtfr_rec.segment10, from_glcc.segment10)
80 and glcc.segment11 = nvl(mtfr_rec.segment11, from_glcc.segment11)
81 and glcc.segment12 = nvl(mtfr_rec.segment12, from_glcc.segment12)
82 and glcc.segment13 = nvl(mtfr_rec.segment13, from_glcc.segment13)
83 and glcc.segment14 = nvl(mtfr_rec.segment14, from_glcc.segment14)
84 and glcc.segment15 = nvl(mtfr_rec.segment15, from_glcc.segment15)
85 and glcc.segment16 = nvl(mtfr_rec.segment16, from_glcc.segment16)
86 and glcc.segment17 = nvl(mtfr_rec.segment17, from_glcc.segment17)
87 and glcc.segment18 = nvl(mtfr_rec.segment18, from_glcc.segment18)
88 and glcc.segment19 = nvl(mtfr_rec.segment19, from_glcc.segment19)
89 and glcc.segment20 = nvl(mtfr_rec.segment20, from_glcc.segment20)
90 and glcc.segment21 = nvl(mtfr_rec.segment21, from_glcc.segment21)
91 and glcc.segment22 = nvl(mtfr_rec.segment22, from_glcc.segment22)
92 and glcc.segment23 = nvl(mtfr_rec.segment23, from_glcc.segment23)
93 and glcc.segment24 = nvl(mtfr_rec.segment24, from_glcc.segment24)
94 and glcc.segment25 = nvl(mtfr_rec.segment25, from_glcc.segment25)
95 and glcc.segment26 = nvl(mtfr_rec.segment26, from_glcc.segment26)
96 and glcc.segment27 = nvl(mtfr_rec.segment27, from_glcc.segment27)
97 and glcc.segment28 = nvl(mtfr_rec.segment28, from_glcc.segment28)
98 and glcc.segment29 = nvl(mtfr_rec.segment29, from_glcc.segment29)
99 and glcc.segment30 = nvl(mtfr_rec.segment30, from_glcc.segment30)
100 and glcc.chart_of_accounts_id = v_chart_of_accounts_id ;
101
102 BEGIN
103
104 -- initialize
105 mtfr_rec := null;
106
107 OPEN c_mtfr;
108 FETCH C_mtfr INTO mtfr_rec;
109 CLOSE c_mtfr;
110
111 if mtfr_rec.to_gl_ccid is null then
112
113 if (p_log_level_rec.statement_level) then
114 fa_debug_pkg.add('FA_MASS_TRANSFERS.famtgcc','to_gl_ccid ','null', p_log_level_rec => p_log_level_rec);
115 end if;
116
117
118 -- Get Chart of Accounts ID
119 Select sob.chart_of_accounts_id
120 Into v_chart_of_accounts_id
121 From fa_book_controls bc,
122 gl_sets_of_books sob
123 Where sob.set_of_books_id = bc.set_of_books_id
124 And bc.book_type_code = mtfr_rec.book_type_code;
125
126
127 OPEN c_glcc;
128 FETCH c_glcc into x_to_glccid;
129 CLOSE c_glcc;
130
131 -- x_to_glccid := -99;
132 if x_to_glccid = -99 then
133
134 if (p_log_level_rec.statement_level) then
135 fa_debug_pkg.add('FA_MASS_TRANSFERS.famtgcc','x_to_glccid','null', p_log_level_rec => p_log_level_rec);
136 end if;
137
138
139 -- the ccid does not exist and so needs to be created
140
141 -- initialize segment array
142 v_from_segarray.delete;
143
144 if (p_log_level_rec.statement_level) then
145 fa_debug_pkg.add('FA_MASS_TRANSFERS.famtgcc','calling','FND_FLEX_EXT.GET_SEGMENTS', p_log_level_rec => p_log_level_rec);
146 end if;
147
148 -- Get from_glccid segment array in the displayed order
149 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
150 application_short_name => 'SQLGL',
151 key_flex_code => 'GL#',
152 structure_number => v_chart_of_accounts_id,
153 combination_id => x_from_glccid,
154 n_segments => v_nsegs,
155 segments => v_from_segarray)) THEN
156 raise flex_error;
157 END IF;
158
159 if (p_log_level_rec.statement_level) then
160 fa_debug_pkg.add('FA_MASS_TRANSFERS.famtgcc','calling',' get_segarray ', p_log_level_rec => p_log_level_rec);
161 end if;
162
163 -- Prepare segment array for To_gl_ccid
164 if NOT get_segarray ( x_mass_transfer_id => x_mass_transfer_id,
165 x_structure_number => v_chart_of_accounts_id,
166 x_delimiter => v_delimiter,
167 x_nsegments => v_nsegs,
168 x_seg_array => v_to_segarray,
169 p_log_level_rec => p_log_level_rec ) then
170 raise flex_error;
171 end if;
172
173 -- replace missing segments of to_array with those of from_array
174 -- this will be then used to generate the new ccid
175
176 for i in 1..v_nsegs Loop
177 if v_to_segarray(i) is null then
178 v_to_segarray(i) := v_from_segarray(i);
179 end if;
180 end loop;
181
182
183 if (p_log_level_rec.statement_level) then
184 fa_debug_pkg.add('FA_MASS_TRANSFERS.famtgcc','calling','FND_FLEX_EXT.GET_COMBINATION_ID', p_log_level_rec => p_log_level_rec);
185 end if;
186
187 -- Updating array with new account value
188 IF (NOT FND_FLEX_EXT.GET_COMBINATION_ID( 'SQLGL',
189 'GL#',
190 v_chart_of_accounts_id,
191 SYSDATE,
192 v_nsegs,
193 v_to_segarray,
194 x_to_glccid)) THEN
195 raise comb_error;
196 END IF;
197 end if; /* if x_gl_ccid = -99 */
198
199 else
200 x_to_glccid := mtfr_rec.to_gl_ccid;
201 end if;
202
203 return (TRUE);
204
205 EXCEPTION
206 when flex_error THEN
207
208 v_message := FND_FLEX_EXT.GET_ENCODED_MESSAGE;
209 fnd_msg_pub.add;
210
211 FA_SRVR_MSG.add_message(CALLING_FN => 'FA_MASS_TRANSFERS_PKG.famtgcc', p_log_level_rec => p_log_level_rec);
212
213 RETURN (FALSE);
214
215 when comb_error then
216
217 v_message := FND_FLEX_EXT.GET_ENCODED_MESSAGE;
218 fnd_msg_pub.add;
219
220 for i in 1..v_to_segarray.count loop
221 if (i = 1) then
222 v_to_conc_segs := v_to_segarray(i);
223 else
224 v_to_conc_segs := v_to_conc_segs || v_delimiter || v_to_segarray(i);
225 end if;
226 end loop;
227
228 FA_SRVR_MSG.ADD_MESSAGE(
229 CALLING_FN=>'FA_MASS_TRANSFERS.famtgcc',
230 NAME=>'FA_FLEXBUILDER_FAIL_CCID',
231 TOKEN1 => 'ACCOUNT_TYPE',
232 VALUE1 => 'DEPRN_EXP',
233 TOKEN2 => 'BOOK_TYPE_CODE',
234 VALUE2 => mtfr_rec.book_type_code,
235 TOKEN3 => 'DIST_ID',
236 VALUE3 => 'NEW',
237 TOKEN4 => 'CONCAT_SEGS',
238 VALUE4 => v_to_conc_segs
239 , p_log_level_rec => p_log_level_rec);
240
241 RETURN (FALSE);
242
243 when others then
244
245 FA_SRVR_MSG.ADD_SQL_ERROR (
246 CALLING_FN => 'FA_MASS_TRANSFERS.famtgcc', p_log_level_rec => p_log_level_rec);
247 RETURN (FALSE);
248
249 END ;
250
251
252 /* This function is used by get_conc_segments and
253 Mass Transfers Report FAS811.rdf. It returns
254 delimiter, no of segments, and segments is the
255 display order in a segment array */
256
257 FUNCTION get_segarray( x_mass_transfer_id in number,
258 x_structure_number in number,
259 x_delimiter in out nocopy varchar2,
260 x_nsegments in out nocopy number,
261 x_seg_array in out nocopy fnd_flex_ext.segmentarray , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
262
263 v_nsegs number:= null;
264 v_seg_list fnd_flex_key_api.segment_list;
265 v_flex_type fnd_flex_key_api.flexfield_type;
266 v_struct_type fnd_flex_key_api.structure_type;
267 v_seg fnd_flex_key_api.segment_type;
268 -- v_seg_array fnd_flex_ext.segmentarray;
269
270 TYPE my_rec_type IS RECORD
271 (colname varchar2(30),
272 colvalue varchar2(150));
273
274 TYPE my_arr_type IS TABLE OF my_rec_type INDEX BY BINARY_INTEGER;
275 myseg_array my_arr_type;
276
277 Cursor C_mtfr is
278 select segment1, segment2, segment3, segment4, segment5,
279 segment6, segment7, segment8, segment9, segment10,
280 segment11, segment12, segment13, segment14, segment15,
281 segment16, segment17, segment18, segment19, segment20,
282 segment21, segment22, segment23, segment24, segment25,
283 segment26, segment27, segment28, segment29, segment30
284 from fa_mass_transfers
285 where mass_transfer_id = x_mass_transfer_id;
286
287 mtfr_rec c_mtfr%ROWTYPE := null;
288 BEGIN
289
290 if (p_log_level_rec.statement_level) then
291 fa_debug_pkg.add('FA_MASS_TRANSFERS.get_segarray','calling','fnd_flex_ext.get_delimiter', p_log_level_rec => p_log_level_rec);
292 end if;
293
294 x_delimiter := fnd_flex_ext.get_delimiter(
295 application_short_name => 'SQLGL'
296 , key_flex_code => 'GL#'
297 , structure_number => x_structure_number );
298
299 if (p_log_level_rec.statement_level) then
300 fa_debug_pkg.add('FA_MASS_TRANSFERS.get_segarray','calling','fnd_flex_key_api.set_session_mode', p_log_level_rec => p_log_level_rec);
301 end if;
302
303
304 fnd_flex_key_api.set_session_mode('customer_data');
305
306
307 if (p_log_level_rec.statement_level) then
308 fa_debug_pkg.add('FA_MASS_TRANSFERS.get_segarray','calling','fnd_flex_key_api.find_flexfield', p_log_level_rec => p_log_level_rec);
309 end if;
310
311 v_flex_type := fnd_flex_key_api.find_flexfield(
312 appl_short_name => 'SQLGL',
313 flex_code => 'GL#' );
314
315 if (p_log_level_rec.statement_level) then
316 fa_debug_pkg.add('FA_MASS_TRANSFERS.get_segarray','calling','fnd_flex_key_api.find_structure', p_log_level_rec => p_log_level_rec);
317 end if;
318
319
320 v_struct_type := fnd_flex_key_api.find_structure(
321 flexfield => v_flex_type,
322 structure_number => x_structure_number );
323
324 if (p_log_level_rec.statement_level) then
325 fa_debug_pkg.add('FA_MASS_TRANSFERS.get_segarray','calling','fnd_flex_key_api.get_segments', p_log_level_rec => p_log_level_rec);
326 end if;
327
328
329 fnd_flex_key_api.get_segments( flexfield => v_flex_type,
330 structure => v_struct_type,
331 --enabled_only => enabled_flag,
332 nsegments => x_nsegments,
333 segments => v_seg_list );
334
335 -- initialize
336 mtfr_rec := null;
337
338 OPEN c_mtfr;
339 FETCH C_mtfr INTO mtfr_rec;
340 CLOSE c_mtfr;
341
342 for i in 1..30 Loop
343
344 myseg_array(i).colname := 'SEGMENT'||i;
345 if i= 1 then
346 myseg_array(i).colvalue := mtfr_rec.segment1;
347 elsif i= 2 then
348 myseg_array(i).colvalue := mtfr_rec.segment2;
349 elsif i= 3 then
350 myseg_array(i).colvalue := mtfr_rec.segment3;
351 elsif i= 4 then
352 myseg_array(i).colvalue := mtfr_rec.segment4;
353 elsif i= 5 then
354 myseg_array(i).colvalue := mtfr_rec.segment5;
355 elsif i= 6 then
356 myseg_array(i).colvalue := mtfr_rec.segment6;
357 elsif i= 7 then
358 myseg_array(i).colvalue := mtfr_rec.segment7;
359 elsif i= 8 then
360 myseg_array(i).colvalue := mtfr_rec.segment8;
361 elsif i= 9 then
362 myseg_array(i).colvalue := mtfr_rec.segment9;
363 elsif i= 10 then
364 myseg_array(i).colvalue := mtfr_rec.segment10;
365 elsif i= 11 then
366 myseg_array(i).colvalue := mtfr_rec.segment11;
367 elsif i= 12 then
368 myseg_array(i).colvalue := mtfr_rec.segment12;
369 elsif i= 13 then
370 myseg_array(i).colvalue := mtfr_rec.segment13;
371 elsif i= 14 then
372 myseg_array(i).colvalue := mtfr_rec.segment14;
373 elsif i= 15 then
374 myseg_array(i).colvalue := mtfr_rec.segment15;
375 elsif i= 16 then
376 myseg_array(i).colvalue := mtfr_rec.segment16;
377 elsif i= 17 then
378 myseg_array(i).colvalue := mtfr_rec.segment17;
379 elsif i= 18 then
380 myseg_array(i).colvalue := mtfr_rec.segment18;
381 elsif i= 19 then
382 myseg_array(i).colvalue := mtfr_rec.segment19;
383 elsif i= 20 then
384 myseg_array(i).colvalue := mtfr_rec.segment20;
385 end if;
386 end loop;
387
388 --
389 -- The segments in the seg_list array are sorted in display order.
390 -- i.e. sorted by segment number.
391 --
392 for i in 1..x_nsegments loop
393 v_seg := fnd_flex_key_api.find_segment(v_flex_type, v_struct_type, v_seg_list(i));
394
395 for j in 1..myseg_array.count loop
396
397 if (v_seg.column_name = myseg_array(j).colname) then
398 x_seg_array(i) := myseg_array(j).colvalue;
399 end if;
400 end loop;
401 end loop;
402
403 return TRUE;
404
405 exception
406 when others then
407 FA_SRVR_MSG.ADD_SQL_ERROR (
408 CALLING_FN => 'FA_MASS_TRANSFERS.get_segarray', p_log_level_rec => p_log_level_rec);
409 RETURN (FALSE);
410
411 END get_segarray;
412
413
414 /* This function is called from Mass Transfers
415 form ( FAXMAMTF.fmb ). It returns delimiter,
416 no of segments, and segment is the display
417 order in a segment array */
418
419 FUNCTION get_conc_segments( x_mass_transfer_id in number,
420 x_structure_number in number,
421 x_delimiter in out nocopy varchar2,
422 x_nsegments in out nocopy number,
423 x_concat_segments in out nocopy varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
424
425 v_seg_array fnd_flex_ext.segmentarray;
426
427 BEGIN
428
429
430 if NOT get_segarray ( x_mass_transfer_id => x_mass_transfer_id,
431 x_structure_number => x_structure_number,
432 x_delimiter => x_delimiter,
433 x_nsegments => x_nsegments,
434 x_seg_array => v_seg_array,
435 p_log_level_rec => p_log_level_rec ) then
436 return FALSE;
437 end if;
438
439 --
440 -- Now we have the all segment values in correct order in segarray.
441 --
442
443 x_concat_segments := fnd_flex_ext.concatenate_segments(
444 x_nsegments,
445 v_seg_array,
446 x_delimiter);
447
448 return TRUE;
449 exception
450 when others then
451 FA_SRVR_MSG.ADD_SQL_ERROR (
452 CALLING_FN => 'FA_MASS_TRANSFERS.get_conc_segments', p_log_level_rec => p_log_level_rec);
453 RETURN (FALSE);
454
455 END get_conc_segments;
456
457
458 END FA_MASS_TRANSFERS_PKG;