[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_TRANSFERS_PKG
Source
1 PACKAGE BODY FA_MASS_TRANSFERS_PKG AS
2 /* $Header: FAXMTFRB.pls 120.5 2005/10/20 14:40:52 bridgway noship $ */
3
4 /** The following describes what this function expects as parameters
5 x_mass_transfer_id valid mass transfer id
6 x_from_glccid valid expense account
7 x_to_glccid null or -99 **/
8
9 function famtgcc ( x_mass_transfer_id in number,
10 x_from_glccid in number,
11 x_to_glccid in out nocopy number,
12 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) 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(30);
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 -- Get Chart of Accounts ID
113 Select sob.chart_of_accounts_id
114 Into v_chart_of_accounts_id
115 From fa_book_controls bc,
116 gl_sets_of_books sob
117 Where sob.set_of_books_id = bc.set_of_books_id
118 And bc.book_type_code = mtfr_rec.book_type_code;
119
120 OPEN c_glcc;
121 FETCH c_glcc into x_to_glccid;
122 CLOSE c_glcc;
123
124 -- x_to_glccid := -99;
125 if x_to_glccid = -99 then
126 -- the ccid does not exist and so needs to be created
127
128 -- initialize segment array
129 v_from_segarray.delete;
130
131 -- Get from_glccid segment array in the displayed order
132 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
133 application_short_name => 'SQLGL',
134 key_flex_code => 'GL#',
135 structure_number => v_chart_of_accounts_id,
136 combination_id => x_from_glccid,
137 n_segments => v_nsegs,
138 segments => v_from_segarray)) THEN
139 raise flex_error;
140 END IF;
141
142 -- Prepare segment array for To_gl_ccid
143 if NOT get_segarray ( x_mass_transfer_id => x_mass_transfer_id,
144 x_structure_number => v_chart_of_accounts_id,
145 x_delimiter => v_delimiter,
146 x_nsegments => v_nsegs,
147 x_seg_array => v_to_segarray
148 ,p_log_level_rec => p_log_level_rec) then
149 raise flex_error;
150 end if;
151
152 -- replace missing segments of to_array with those of from_array
153 -- this will be then used to generate the new ccid
154
155 for i in 1..v_nsegs Loop
156 if v_to_segarray(i) is null then
157 v_to_segarray(i) := v_from_segarray(i);
158 end if;
159 end loop;
160
161 -- Updating array with new account value
162 IF (NOT FND_FLEX_EXT.GET_COMBINATION_ID( 'SQLGL',
163 'GL#',
164 v_chart_of_accounts_id,
165 SYSDATE,
166 v_nsegs,
167 v_to_segarray,
168 x_to_glccid)) THEN
169 raise comb_error;
170 END IF;
171 end if; /* if x_gl_ccid = -99 */
172
173 else
174 x_to_glccid := mtfr_rec.to_gl_ccid;
175 end if;
176
177 return (TRUE);
178 EXCEPTION
179 when flex_error THEN
180 v_message := FND_FLEX_EXT.GET_ENCODED_MESSAGE;
181 fnd_msg_pub.add;
182
183 FA_SRVR_MSG.add_message(CALLING_FN => 'FA_MASS_TRANSFERS_PKG.famtgcc',
184 p_log_level_rec => p_log_level_rec);
185
186 RETURN (FALSE);
187
188 when comb_error then
189
190 v_message := FND_FLEX_EXT.GET_ENCODED_MESSAGE;
191 fnd_msg_pub.add;
192
193 for i in 1..v_to_segarray.count loop
194 if (i = 1) then
195 v_to_conc_segs := v_to_segarray(i);
196 else
197 v_to_conc_segs := v_to_conc_segs || v_delimiter || v_to_segarray(i);
198 end if;
199 end loop;
200
201 FA_SRVR_MSG.ADD_MESSAGE(
202 CALLING_FN=>'FA_MASS_TRANSFERS.famtgcc',
203 NAME=>'FA_FLEXBUILDER_FAIL_CCID',
204 TOKEN1 => 'ACCOUNT_TYPE',
205 VALUE1 => 'DEPRN_EXP',
206 TOKEN2 => 'BOOK_TYPE_CODE',
207 VALUE2 => mtfr_rec.book_type_code,
208 TOKEN3 => 'DIST_ID',
209 VALUE3 => 'NEW',
210 TOKEN4 => 'CONCAT_SEGS',
211 VALUE4 => v_to_conc_segs,
212 p_log_level_rec => p_log_level_rec
213 );
214
215 RETURN (FALSE);
216
217
218 when others then
219
220 FA_SRVR_MSG.ADD_SQL_ERROR (
221 CALLING_FN => 'FA_MASS_TRANSFERS.famtgcc'
222 ,p_log_level_rec => p_log_level_rec);
223 RETURN (FALSE);
224 END ;
225
226
227 /* This function is used by get_conc_segments and
228 Mass Transfers Report FAS811.rdf. It returns
229 delimiter, no of segments, and segments is the
230 display order in a segment array */
231
232 FUNCTION get_segarray( x_mass_transfer_id in number,
233 x_structure_number in number,
234 x_delimiter in out nocopy varchar2,
235 x_nsegments in out nocopy number,
236 x_seg_array in out nocopy fnd_flex_ext.segmentarray,
237 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
238
239 v_nsegs number:= null;
240 v_seg_list fnd_flex_key_api.segment_list;
241 v_flex_type fnd_flex_key_api.flexfield_type;
242 v_struct_type fnd_flex_key_api.structure_type;
243 v_seg fnd_flex_key_api.segment_type;
244 -- v_seg_array fnd_flex_ext.segmentarray;
245
246 TYPE my_rec_type IS RECORD
247 (colname varchar2(30),
248 colvalue varchar2(150));
249
250 TYPE my_arr_type IS TABLE OF my_rec_type INDEX BY BINARY_INTEGER;
251 myseg_array my_arr_type;
252
253 Cursor C_mtfr is
254 select segment1, segment2, segment3, segment4, segment5,
255 segment6, segment7, segment8, segment9, segment10,
256 segment11, segment12, segment13, segment14, segment15,
257 segment16, segment17, segment18, segment19, segment20,
258 segment21, segment22, segment23, segment24, segment25,
259 segment26, segment27, segment28, segment29, segment30
260 from fa_mass_transfers
261 where mass_transfer_id = x_mass_transfer_id;
262
263 mtfr_rec c_mtfr%ROWTYPE := null;
264 BEGIN
265
266 x_delimiter := fnd_flex_ext.get_delimiter(
267 application_short_name => 'SQLGL'
268 , key_flex_code => 'GL#'
269 , structure_number => x_structure_number );
270
271 fnd_flex_key_api.set_session_mode('customer_data');
272
273 v_flex_type := fnd_flex_key_api.find_flexfield(
274 appl_short_name => 'SQLGL',
275 flex_code => 'GL#' );
276
277 v_struct_type := fnd_flex_key_api.find_structure(
278 flexfield => v_flex_type,
279 structure_number => x_structure_number );
280
281 fnd_flex_key_api.get_segments( flexfield => v_flex_type,
282 structure => v_struct_type,
283 --enabled_only => enabled_flag,
284 nsegments => x_nsegments,
285 segments => v_seg_list);
286
287 -- initialize
288 mtfr_rec := null;
289
290 OPEN c_mtfr;
291 FETCH C_mtfr INTO mtfr_rec;
292 CLOSE c_mtfr;
293
294 for i in 1..30 Loop
295
296 myseg_array(i).colname := 'SEGMENT'||i;
297 if i= 1 then
298 myseg_array(i).colvalue := mtfr_rec.segment1;
299 elsif i= 2 then
300 myseg_array(i).colvalue := mtfr_rec.segment2;
301 elsif i= 3 then
302 myseg_array(i).colvalue := mtfr_rec.segment3;
303 elsif i= 4 then
304 myseg_array(i).colvalue := mtfr_rec.segment4;
305 elsif i= 5 then
306 myseg_array(i).colvalue := mtfr_rec.segment5;
307 elsif i= 6 then
308 myseg_array(i).colvalue := mtfr_rec.segment6;
309 elsif i= 7 then
310 myseg_array(i).colvalue := mtfr_rec.segment7;
311 elsif i= 8 then
312 myseg_array(i).colvalue := mtfr_rec.segment8;
313 elsif i= 9 then
314 myseg_array(i).colvalue := mtfr_rec.segment9;
315 elsif i= 10 then
316 myseg_array(i).colvalue := mtfr_rec.segment10;
317 elsif i= 11 then
318 myseg_array(i).colvalue := mtfr_rec.segment11;
319 elsif i= 12 then
320 myseg_array(i).colvalue := mtfr_rec.segment12;
321 elsif i= 13 then
322 myseg_array(i).colvalue := mtfr_rec.segment13;
323 elsif i= 14 then
324 myseg_array(i).colvalue := mtfr_rec.segment14;
325 elsif i= 15 then
326 myseg_array(i).colvalue := mtfr_rec.segment15;
327 elsif i= 16 then
328 myseg_array(i).colvalue := mtfr_rec.segment16;
329 elsif i= 17 then
330 myseg_array(i).colvalue := mtfr_rec.segment17;
331 elsif i= 18 then
332 myseg_array(i).colvalue := mtfr_rec.segment18;
333 elsif i= 19 then
334 myseg_array(i).colvalue := mtfr_rec.segment19;
335 elsif i= 20 then
336 myseg_array(i).colvalue := mtfr_rec.segment20;
337 end if;
338 end loop;
339
340 --
341 -- The segments in the seg_list array are sorted in display order.
342 -- i.e. sorted by segment number.
343 --
344 for i in 1..x_nsegments loop
345 v_seg := fnd_flex_key_api.find_segment(v_flex_type, v_struct_type, v_seg_list(i));
346
347 for j in 1..myseg_array.count loop
348
349 if (v_seg.column_name = myseg_array(j).colname) then
350 x_seg_array(i) := myseg_array(j).colvalue;
351 end if;
352 end loop;
353 end loop;
354
355 return TRUE;
356 exception
357 when others then
358 FA_SRVR_MSG.ADD_SQL_ERROR (
359 CALLING_FN => 'FA_MASS_TRANSFERS.get_segarray'
360 ,p_log_level_rec => p_log_level_rec);
361 RETURN (FALSE);
362
363 END get_segarray;
364
365
366 /* This function is called from Mass Transfers
367 form ( FAXMAMTF.fmb ). It returns delimiter,
368 no of segments, and segment is the display
369 order in a segment array */
370
371 FUNCTION get_conc_segments( x_mass_transfer_id in number,
372 x_structure_number in number,
373 x_delimiter in out nocopy varchar2,
374 x_nsegments in out nocopy number,
375 x_concat_segments in out nocopy varchar2,
376 p_log_level_rec in FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
377
378 v_seg_array fnd_flex_ext.segmentarray;
379
380 BEGIN
381
382
383 if NOT get_segarray ( x_mass_transfer_id => x_mass_transfer_id,
384 x_structure_number => x_structure_number,
385 x_delimiter => x_delimiter,
386 x_nsegments => x_nsegments,
387 x_seg_array => v_seg_array,
388 p_log_level_rec => p_log_level_rec) then
389 return FALSE;
390 end if;
391
392 --
393 -- Now we have the all segment values in correct order in segarray.
394 --
395
396 x_concat_segments := fnd_flex_ext.concatenate_segments(
397 x_nsegments,
398 v_seg_array,
399 x_delimiter);
400
401 return TRUE;
402 exception
403 when others then
404 FA_SRVR_MSG.ADD_SQL_ERROR (
405 CALLING_FN => 'FA_MASS_TRANSFERS.get_conc_segments'
406 ,p_log_level_rec => p_log_level_rec);
407 RETURN (FALSE);
408
409 END get_conc_segments;
410
411
412 END FA_MASS_TRANSFERS_PKG;