[Home] [Help]
PACKAGE BODY: APPS.PSA_REP_ATTRIBUTES
Source
1 PACKAGE BODY PSA_REP_ATTRIBUTES AS
2 /* $Header: psagrattb.pls 120.1 2007/11/30 18:47:04 sasukuma noship $ */
3
4 g_module_name VARCHAR2(100);
5
6 PROCEDURE debug
7 (
8 p_module IN VARCHAR2,
9 p_message IN VARCHAR2
10 )
11 IS
12 BEGIN
13 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
14 fnd_log.string (fnd_log.level_statement, p_module,p_message);
15 END IF;
16 END;
17
18 PROCEDURE error
19 (
20 p_module IN VARCHAR2,
21 p_message IN VARCHAR2
22 )
23 IS
24 BEGIN
25 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
26 fnd_log.string (fnd_log.level_unexpected, p_module,p_message);
27 END IF;
28 fnd_file.put_line (fnd_file.log, p_module||':'||p_message);
29 END;
30
31 PROCEDURE initialize_global_variables
32 IS
33 BEGIN
34 g_module_name := 'psa.plsql.psa_rep_attributes.';
35 fnd_file.put_line(fnd_file.log, 'You are running version '||'$Header: psagrattb.pls 120.1 2007/11/30 18:47:04 sasukuma noship $');
36 END;
37
38 PROCEDURE gl_preparation
39 (
40 errbuf OUT NOCOPY VARCHAR2,
41 retcode OUT NOCOPY VARCHAR2,
42 p_ledger_id IN NUMBER
43 ) IS
44 l_module_name VARCHAR2(100);
45 ---------------------
46 -- Flexfield API type
47 ---------------------
48 lseg_gl_segs fnd_flex_key_api.segment_type; -- Segment Type
49 lseg_glat_segs fnd_flex_key_api.segment_type; -- Segment Type
50 lseg_new_seg fnd_flex_key_api.segment_type;
51 lseg_att_new_seg fnd_flex_key_api.segment_type;
52 --
53 lstr_gl_struc fnd_flex_key_api.structure_type; -- Structure Type
54 lstr_new_struc fnd_flex_key_api.structure_type;
55 lstr_glat_struc fnd_flex_key_api.structure_type;
56 --
57 lflx_gl_flex fnd_flex_key_api.flexfield_type; -- Flexfield Type
58 lflx_new_flex fnd_flex_key_api.flexfield_type;
59 lflx_glat_flex fnd_flex_key_api.flexfield_type;
60 --
61 llst_gl_seg_list fnd_flex_key_api.segment_list; -- Segment List
62 llst_glat_seg_list fnd_flex_key_api.segment_list;
63 ---------------------
64 -- Value set API
65 ---------------------
66 lval_valueset fnd_vset.valueset_r;
67 lval_format fnd_vset.valueset_dr;
68 ---------------------
69 -- Flags and Counters
70 ---------------------
71 lc_flex_val_set_name fnd_flex_value_sets.flex_value_set_name%type;
72 lc_new_flex_flag varchar2(1):='N';
73 ln_no_of_attributes number;
74 ln_func number;
75 ln_gl_nsegs number;
76 ln_glat_nsegs number;
77 ln_segs_ctr number;
78 ln_seg_num number;
79 ln_glat_segs_ctr number;
80 ln_flex_attr_flag number:=0;
81 p_coa_id number;
82 ----------------------
83 --Messages
84 ----------------------
85 lc_api_message varchar2(2000); -- For API Message
86 lc_rep_profile varchar2(132);
87 lc_err_message varchar2(200);
88 lexp_error exception;
89 ln_userid number;
90 ---------------------------
91 -- For FND_INSTALLATION API
92 ---------------------------
93 lc_int_status varchar2(10);
94 lc_int_industry varchar2(10);
95 lc_int_schema varchar2(10);
96 ---------------------------
97 -- INDUSTRY profile option
98 ---------------------------
99 lp_user_id number;
100 lp_user_resp_id number;
101 lp_resp_appl_id number;
102 l_defined boolean;
103
104 --------------------------------
105 -- Segment Attribute Types Cursor
106 --------------------------------
107 CURSOR seg_attr_cur
108 (
109 p_id_flex_num NUMBER,
110 p_id_flex_code VARCHAR2,
111 p_application_id NUMBER,
112 p_app_seg_name VARCHAR2
113 ) IS
114 SELECT typ.segment_attribute_type segment_attribute_type,
115 typ.segment_prompt segment_prompt,
116 typ.description description,
117 typ.global_flag global_flag,
118 typ.required_flag required_flag,
119 typ.unique_flag unique_flag,
120 val.attribute_value attribute_value
121 FROM fnd_segment_attribute_values val,
122 fnd_segment_attribute_types typ
123 WHERE val.id_flex_num =p_id_flex_num -- Bug 3813504
124 AND val.id_flex_code = p_id_flex_code
125 AND val.id_flex_code = typ.id_flex_code
126 AND typ.segment_attribute_type = val.segment_attribute_type
127 AND val.application_id = p_application_id
128 AND val.application_id = typ.application_id
129 AND val.application_column_name = p_app_seg_name;
130
131 -----------------------------
132 -- Reporting Attributes Cursor
133 -- This cursor will select all
134 -- the attributes defined for
135 -- the segment
136 -----------------------------
137 -- Bug 4128077
138 CURSOR rep_attr_cur
139 (
140 p_seg_name VARCHAR2,
141 p_seg_vset_name VARCHAR2,
142 p_application_id NUMBER
143 ) IS
144 SELECT sequence,
145 att.application_id,
146 att.id_flex_code,
147 att.id_flex_num,
148 att.attr_segment_name,
149 att.application_column_name,
150 val.flex_value_set_name value_set_name,
151 att.user_column_name,
152 att.index_flag,
153 att.form_left_prompt,
154 att.form_above_prompt,
155 att.display_size,
156 att.description,
157 att.table_id,
158 att.attribute_num,
159 att.segment_name,
160 fdu.default_type ,
161 fdu.default_value ,
162 fdu.range_code
163 FROM fnd_seg_rpt_attributes att,
164 fnd_flex_value_sets val ,
165 fnd_descr_flex_column_usages fdu
166 WHERE segment_name = p_seg_name
167 AND att.attr_value_set_id = val.flex_value_set_id
168 AND att.enabled_flag = 'Y'
169 AND att.id_flex_num = p_coa_id
170 AND fdu.descriptive_flexfield_name = 'FND_FLEX_VALUES'
171 AND fdu.descriptive_flex_context_code = p_seg_vset_name
172 AND fdu.flex_value_set_id = val.flex_value_set_id
173 AND att.application_column_name = fdu.end_user_column_name
174 AND fdu.application_id=p_application_id
175 ORDER BY 1;
176
177 --================================
178 BEGIN -- This is the main Block
179 --================================
180 l_module_name := g_module_name || 'gl_preparation';
181
182 SELECT chart_of_accounts_id
183 INTO p_coa_id
184 FROM gl_ledgers
185 WHERE ledger_id = p_ledger_id;
186
187 debug (l_module_name, 'p_ledger_id='||p_ledger_id);
188 debug (l_module_name, 'p_coa_id='||p_coa_id);
189
190 ---------------------------------------
191 --This Block does the validation to
192 --check whether the eporting Attributes
193 --profile is set and the installation
194 --is Goverment type
195 ---------------------------------------
196 BEGIN
197 -- The installation info is now implemented as a profile option (INDUSTRY).
198
199 -- Get Calling Application ID / Responsibility ID / User ID
200
201 lp_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
202 lp_user_resp_id := FND_GLOBAL.RESP_ID;
203 lp_user_id := FND_GLOBAL.USER_ID;
204
205 debug (l_module_name, 'lp_user_id='||lp_user_id);
206 debug (l_module_name, 'lp_user_resp_id='||lp_user_resp_id);
207 debug (l_module_name, 'lp_resp_appl_id='||lp_resp_appl_id);
208 debug (l_module_name, 'lc_int_industry='||lc_int_industry);
209 fnd_profile.get_specific
210 (
211 'INDUSTRY',
212 lp_user_id,
213 lp_user_resp_id,
214 lp_resp_appl_id,
215 lc_int_industry,
216 l_defined
217 );
218
219 IF NOT l_defined THEN
220 IF fnd_installation.get_app_info
221 (
222 application_short_name=>'SQLGL',
223 status=>lc_int_status,
224 industry=>lc_int_industry,
225 oracle_schema=>lc_int_schema
226 ) THEN
227 IF lc_int_industry <> 'G' THEN
228 lc_err_message := 'Oracle Government Ledger is not Installed';
229 error (l_module_name, lc_err_message);
230 RAISE lexp_error;
231 END IF;
232 END IF;
233 ELSE
234 IF lc_int_industry <> 'G' THEN
235 lc_err_message := 'Oracle Government Ledger is not Installed';
236 error (l_module_name, lc_err_message);
237 RAISE lexp_error;
238 END IF;
239 END IF;
240
241 --
242 fnd_profile.get('USER_ID',ln_userid);
243 fnd_profile.get('ATTRIBUTE_REPORTING',lc_rep_profile);
244 IF lc_rep_profile = 'N' THEN
245 lc_err_message := 'The Value for the Profile Option: ATTRIBUTE REPORTING is not set ';
246 error (l_module_name, lc_err_message);
247 RAISE lexp_error;
248 END IF;
249 --
250 SELECT COUNT(*)
251 INTO ln_no_of_attributes
252 FROM fnd_seg_rpt_attributes
253 WHERE application_id = 101
254 AND id_flex_code = 'GLAT'
255 AND id_flex_num = p_coa_id;
256 debug (l_module_name, 'ln_no_of_attributes='||ln_no_of_attributes);
257 IF ln_no_of_attributes > 42 THEN
258 lc_err_message := 'The number of attributes defined can not be greater than 42';
259 error (l_module_name, lc_err_message);
260 RAISE lexp_error;
261 END IF;
262 END;
263
264 --------------------------------------------------
265 --Initiate the flexfied api
266 --Find the Info about the GL Accounting flexfied
267 --Find the Structure and Segments for the entered
268 --chart of accounts ID
269 --------------------------------------------------
270 BEGIN
271 fnd_flex_key_api.set_session_mode('seed_data');
272 --
273 debug (l_module_name, 'Calling fnd_flex_key_api.find_flexfield GLLE');
274 lflx_gl_flex := fnd_flex_key_api.find_flexfield('SQLGL','GLLE');
275
276 debug (l_module_name, '=============================');
277 debug (l_module_name, 'DUMP OF flex field SQLGL GLLE');
278 debug (l_module_name, '=============================');
279 debug (l_module_name, 'instantiated='||lflx_gl_flex.instantiated);
280 debug (l_module_name, 'appl_short_name='||lflx_gl_flex.appl_short_name);
281 debug (l_module_name, 'flex_code='||lflx_gl_flex.flex_code);
282 debug (l_module_name, 'flex_title='||lflx_gl_flex.flex_title);
283 debug (l_module_name, 'description='||lflx_gl_flex.description);
284 debug (l_module_name, 'table_appl_short_name='||lflx_gl_flex.table_appl_short_name);
285 debug (l_module_name, 'table_name='||lflx_gl_flex.table_name);
286 debug (l_module_name, 'concatenated_segs_view_name='||lflx_gl_flex.concatenated_segs_view_name);
287 debug (l_module_name, 'unique_id_column='||lflx_gl_flex.unique_id_column);
288 debug (l_module_name, 'structure_column='||lflx_gl_flex.structure_column);
289 debug (l_module_name, 'dynamic_inserts='||lflx_gl_flex.dynamic_inserts);
290 debug (l_module_name, 'allow_id_value_sets='||lflx_gl_flex.allow_id_value_sets);
291 debug (l_module_name, 'index_flag='||lflx_gl_flex.index_flag);
292 debug (l_module_name, 'concat_seg_len_max='||lflx_gl_flex.concat_seg_len_max);
293 debug (l_module_name, 'concat_len_warning='||lflx_gl_flex.concat_len_warning);
294 debug (l_module_name, 'application_id='||lflx_gl_flex.application_id);
295 debug (l_module_name, 'table_application_id='||lflx_gl_flex.table_application_id);
296 debug (l_module_name, 'table_id='||lflx_gl_flex.table_id);
297 debug (l_module_name, '=============================');
298
299 debug (l_module_name, 'fnd_flex_key_api.find_structure');
300 lstr_gl_struc := fnd_flex_key_api.find_structure(lflx_gl_flex,p_coa_id);
301 debug (l_module_name, '======================================');
302 debug (l_module_name, 'DUMP OF flex field structure SQLGL GLLE');
303 debug (l_module_name, '======================================');
304 debug (l_module_name, 'instantiated='||lstr_gl_struc.instantiated);
305 debug (l_module_name, 'structure_number='||lstr_gl_struc.structure_number);
306 debug (l_module_name, 'structure_code='||lstr_gl_struc.structure_code);
307 debug (l_module_name, 'structure_name='||lstr_gl_struc.structure_name);
308 debug (l_module_name, 'description='||lstr_gl_struc.description);
309 debug (l_module_name, 'view_name='||lstr_gl_struc.view_name);
310 debug (l_module_name, 'freeze_flag='||lstr_gl_struc.freeze_flag);
311 debug (l_module_name, 'enabled_flag='||lstr_gl_struc.enabled_flag);
312 debug (l_module_name, 'segment_separator='||lstr_gl_struc.segment_separator);
313 debug (l_module_name, 'cross_val_flag='||lstr_gl_struc.cross_val_flag);
314 debug (l_module_name, 'freeze_rollup_flag='||lstr_gl_struc.freeze_rollup_flag);
315 debug (l_module_name, 'dynamic_insert_flag='||lstr_gl_struc.dynamic_insert_flag);
316 debug (l_module_name, 'shorthand_enabled_flag='||lstr_gl_struc.shorthand_enabled_flag);
317 debug (l_module_name, 'shorthand_prompt='||lstr_gl_struc.shorthand_prompt);
318 debug (l_module_name, 'shorthand_length='||lstr_gl_struc.shorthand_length);
319 debug (l_module_name, '======================================');
320 debug (l_module_name, 'fnd_flex_key_api.get_segments');
321 fnd_flex_key_api.get_segments
322 (
323 flexfield => lflx_gl_flex,
324 structure => lstr_gl_struc,
325 nsegments => ln_gl_nsegs, --nseg stores the number of segments
326 segments => llst_gl_seg_list
327 );
328 debug (l_module_name, '======================================');
329 debug (l_module_name, 'DUMP OF flex field segments SQLGL GLLE');
330 debug (l_module_name, '======================================');
331 debug (l_module_name, 'ln_gl_nsegs='||ln_gl_nsegs);
332 FOR i IN 1..ln_gl_nsegs LOOP
333 debug (l_module_name, 'segment('||i||')='||llst_gl_seg_list(i));
334 END LOOP;
335 debug (l_module_name, '======================================');
336 END;
337
338 ---------------------------------------------
339 --Find Whether GLAT Flexfield exists
340 --When GLAT flexfield does not exist
341 --create a new Flexfield with Flex code GLAT
342 --and register the flexfield
343 --and Create New Structure for GLAT Flexfield
344 --Set the Check Flag as there is no need to
345 --delete records for GLAT Flexfield
346 --Also set the flag for new GLAT flexfield
347 ---------------------------------------------
348 BEGIN
349 debug (l_module_name, 'fnd_flex_key_api.find_flexfield GLAT');
350 lflx_glat_flex :=fnd_flex_key_api.find_flexfield('SQLGL', 'GLAT');
351 debug (l_module_name, '=============================');
352 debug (l_module_name, 'DUMP OF flex field SQLGL GLAT');
353 debug (l_module_name, '=============================');
354 debug (l_module_name, 'instantiated='||lflx_glat_flex.instantiated);
355 debug (l_module_name, 'appl_short_name='||lflx_glat_flex.appl_short_name);
356 debug (l_module_name, 'flex_code='||lflx_glat_flex.flex_code);
357 debug (l_module_name, 'flex_title='||lflx_glat_flex.flex_title);
358 debug (l_module_name, 'description='||lflx_glat_flex.description);
359 debug (l_module_name, 'table_appl_short_name='||lflx_glat_flex.table_appl_short_name);
360 debug (l_module_name, 'table_name='||lflx_glat_flex.table_name);
361 debug (l_module_name, 'concatenated_segs_view_name='||lflx_glat_flex.concatenated_segs_view_name);
362 debug (l_module_name, 'unique_id_column='||lflx_glat_flex.unique_id_column);
363 debug (l_module_name, 'structure_column='||lflx_glat_flex.structure_column);
364 debug (l_module_name, 'dynamic_inserts='||lflx_glat_flex.dynamic_inserts);
365 debug (l_module_name, 'allow_id_value_sets='||lflx_glat_flex.allow_id_value_sets);
366 debug (l_module_name, 'index_flag='||lflx_glat_flex.index_flag);
367 debug (l_module_name, 'concat_seg_len_max='||lflx_glat_flex.concat_seg_len_max);
368 debug (l_module_name, 'concat_len_warning='||lflx_glat_flex.concat_len_warning);
369 debug (l_module_name, 'application_id='||lflx_glat_flex.application_id);
370 debug (l_module_name, 'table_application_id='||lflx_glat_flex.table_application_id);
371 debug (l_module_name, 'table_id='||lflx_glat_flex.table_id);
372 debug (l_module_name, '=============================');
373 EXCEPTION
374 WHEN no_data_found THEN
375 debug (l_module_name, 'No GLAT Defined. Creating GLAT');
376 debug (l_module_name, 'Calling fnd_flex_key_api.new_flexfield');
377 debug (l_module_name, '==============================');
378 debug (l_module_name, 'CREATING flex field SQLGL GLAT');
379 debug (l_module_name, '==============================');
380 debug (l_module_name, 'appl_short_name='||lflx_gl_flex.appl_short_name);
381 debug (l_module_name, 'flex_code='||'GLAT');
382 debug (l_module_name, 'flex_title='||substr('Reporting Attributes:'||lflx_gl_flex.flex_title,1,30));
383 debug (l_module_name, 'description='||substr('Reporting Attributes:'||lflx_gl_flex.description,1,240));
384 debug (l_module_name, 'table_appl_short_name='||lflx_gl_flex.table_appl_short_name);
385 debug (l_module_name, 'table_name='||lflx_gl_flex.table_name);
386 debug (l_module_name, 'unique_id_column='||lflx_gl_flex.unique_id_column);
387 debug (l_module_name, 'structure_column='||lflx_gl_flex.structure_column);
388 debug (l_module_name, 'dynamic_inserts='||lflx_gl_flex.dynamic_inserts);
389 debug (l_module_name, 'allow_id_value_sets='||lflx_gl_flex.allow_id_value_sets);
390 debug (l_module_name, 'index_flag='||lflx_gl_flex.index_flag);
391 debug (l_module_name, 'concat_seg_len_max='||lflx_gl_flex.concat_seg_len_max);
392 debug (l_module_name, 'concat_seg_len_max='||lflx_gl_flex.concat_seg_len_max);
393 debug (l_module_name, 'concat_len_warning='||lflx_gl_flex.concat_len_warning);
394 debug (l_module_name, '==============================');
395 lflx_new_flex := fnd_flex_key_api.new_flexfield
396 (
397 appl_short_name =>lflx_gl_flex.appl_short_name,
398 flex_code =>'GLAT',
399 flex_title =>substr('Reporting Attributes:'||lflx_gl_flex.flex_title,1,30),
400 description =>substr('Reporting Attributes:'||lflx_gl_flex.description,1,240),
401 table_appl_short_name=>lflx_gl_flex.table_appl_short_name,
402 table_name =>lflx_gl_flex.table_name,
403 unique_id_column =>lflx_gl_flex.unique_id_column,
404 structure_column =>lflx_gl_flex.structure_column,
405 dynamic_inserts =>lflx_gl_flex.dynamic_inserts,
406 allow_id_value_sets =>lflx_gl_flex.allow_id_value_sets,
407 index_flag =>lflx_gl_flex.index_flag,
408 concat_seg_len_max =>lflx_gl_flex.concat_seg_len_max,
409 concat_len_warning =>lflx_gl_flex.concat_len_warning
410 );
411 debug (l_module_name, '==========================================');
412 debug (l_module_name, 'DUMP OF flex field SQLGL GLAT just created');
413 debug (l_module_name, '==========================================');
414 debug (l_module_name, 'instantiated='||lflx_new_flex.instantiated);
415 debug (l_module_name, 'appl_short_name='||lflx_new_flex.appl_short_name);
416 debug (l_module_name, 'flex_code='||lflx_new_flex.flex_code);
417 debug (l_module_name, 'flex_title='||lflx_new_flex.flex_title);
418 debug (l_module_name, 'description='||lflx_new_flex.description);
419 debug (l_module_name, 'table_appl_short_name='||lflx_new_flex.table_appl_short_name);
420 debug (l_module_name, 'table_name='||lflx_new_flex.table_name);
421 debug (l_module_name, 'concatenated_segs_view_name='||lflx_new_flex.concatenated_segs_view_name);
422 debug (l_module_name, 'unique_id_column='||lflx_new_flex.unique_id_column);
423 debug (l_module_name, 'structure_column='||lflx_new_flex.structure_column);
424 debug (l_module_name, 'dynamic_inserts='||lflx_new_flex.dynamic_inserts);
425 debug (l_module_name, 'allow_id_value_sets='||lflx_new_flex.allow_id_value_sets);
426 debug (l_module_name, 'index_flag='||lflx_new_flex.index_flag);
427 debug (l_module_name, 'concat_seg_len_max='||lflx_new_flex.concat_seg_len_max);
428 debug (l_module_name, 'concat_len_warning='||lflx_new_flex.concat_len_warning);
429 debug (l_module_name, 'application_id='||lflx_new_flex.application_id);
430 debug (l_module_name, 'table_application_id='||lflx_new_flex.table_application_id);
431 debug (l_module_name, 'table_id='||lflx_new_flex.table_id);
432 debug (l_module_name, '=============================');
433 --
434 debug (l_module_name, 'Calling fnd_flex_key_api.register');
435 fnd_flex_key_api.register
436 (
437 flexfield =>lflx_new_flex,
438 enable_columns => 'Y'
439 );
440 --
441 debug (l_module_name, 'Calling fnd_flex_key_api.find_flexfield after creation of GLAT');
442 lflx_glat_flex :=fnd_flex_key_api.find_flexfield('SQLGL', 'GLAT');
443 debug (l_module_name, '=============================');
444 debug (l_module_name, 'DUMP OF flex field SQLGL GLAT');
445 debug (l_module_name, '=============================');
446 debug (l_module_name, 'instantiated='||lflx_glat_flex.instantiated);
447 debug (l_module_name, 'appl_short_name='||lflx_glat_flex.appl_short_name);
448 debug (l_module_name, 'flex_code='||lflx_glat_flex.flex_code);
449 debug (l_module_name, 'flex_title='||lflx_glat_flex.flex_title);
450 debug (l_module_name, 'description='||lflx_glat_flex.description);
451 debug (l_module_name, 'table_appl_short_name='||lflx_glat_flex.table_appl_short_name);
452 debug (l_module_name, 'table_name='||lflx_glat_flex.table_name);
453 debug (l_module_name, 'concatenated_segs_view_name='||lflx_glat_flex.concatenated_segs_view_name);
454 debug (l_module_name, 'unique_id_column='||lflx_glat_flex.unique_id_column);
455 debug (l_module_name, 'structure_column='||lflx_glat_flex.structure_column);
456 debug (l_module_name, 'dynamic_inserts='||lflx_glat_flex.dynamic_inserts);
457 debug (l_module_name, 'allow_id_value_sets='||lflx_glat_flex.allow_id_value_sets);
458 debug (l_module_name, 'index_flag='||lflx_glat_flex.index_flag);
459 debug (l_module_name, 'concat_seg_len_max='||lflx_glat_flex.concat_seg_len_max);
460 debug (l_module_name, 'concat_len_warning='||lflx_glat_flex.concat_len_warning);
461 debug (l_module_name, 'application_id='||lflx_glat_flex.application_id);
462 debug (l_module_name, 'table_application_id='||lflx_glat_flex.table_application_id);
463 debug (l_module_name, 'table_id='||lflx_glat_flex.table_id);
464 debug (l_module_name, '=============================');
465 --
466 lc_new_flex_flag :='Y';
467 END;
468 -------------------------------------------------
469 --Intialize the counter
470 --LOOP for the each segment in the GL Flexfield
471 -- Get the GL Segment Details
472 -- IF GLAT flexfied exists THEN
473 -- Get segments for GLAT Structure
474 -- Delete the GLAT segments and other details
475 -- Create a new GLAT structure
476 -- END IF
477 -- Create new Segments for GLAT from GL segments
478 -- Create new Segments for GLAT from Attributes
479 --END LOOP
480 -------------------------------------------------
481 ln_segs_ctr := 1;
482 ln_seg_num := 1;
483 debug (l_module_name, 'ln_gl_nsegs='||ln_gl_nsegs);
484 WHILE (ln_segs_ctr<=ln_gl_nsegs) LOOP
485 debug (l_module_name, 'Calling fnd_flex_key_api.find_segment');
486 debug (l_module_name, 'llst_gl_seg_list('||ln_segs_ctr||')='||llst_gl_seg_list(ln_segs_ctr));
487 lseg_gl_segs:=fnd_flex_key_api.find_segment
488 (
489 lflx_gl_flex,
490 lstr_gl_struc,
491 llst_gl_seg_list(ln_segs_ctr)
492 );
493 debug (l_module_name, '=============================');
494 debug (l_module_name, 'DUMP OF flex field SQLGL GLLE Segment Type for '||llst_gl_seg_list(ln_segs_ctr));
495 debug (l_module_name, '=============================');
496 debug (l_module_name, 'instantiated='||lseg_gl_segs.instantiated);
497 debug (l_module_name, 'segment_name='||lseg_gl_segs.segment_name);
498 debug (l_module_name, 'description='||lseg_gl_segs.description);
499 debug (l_module_name, 'column_name='||lseg_gl_segs.column_name);
500 debug (l_module_name, 'segment_number='||lseg_gl_segs.segment_number);
501 debug (l_module_name, 'enabled_flag='||lseg_gl_segs.enabled_flag);
502 debug (l_module_name, 'displayed_flag='||lseg_gl_segs.displayed_flag);
503 debug (l_module_name, 'indexed_flag='||lseg_gl_segs.indexed_flag);
504 debug (l_module_name, 'value_set_id='||lseg_gl_segs.value_set_id);
505 debug (l_module_name, 'value_set_name='||lseg_gl_segs.value_set_name);
506 debug (l_module_name, 'default_type='||lseg_gl_segs.default_type);
507 debug (l_module_name, 'default_value='||lseg_gl_segs.default_value);
508 debug (l_module_name, 'runtime_property_function='||lseg_gl_segs.runtime_property_function);
509 debug (l_module_name, 'additional_where_clause='||lseg_gl_segs.additional_where_clause);
510 debug (l_module_name, 'required_flag='||lseg_gl_segs.required_flag);
511 debug (l_module_name, 'security_flag='||lseg_gl_segs.security_flag);
512 debug (l_module_name, 'range_code='||lseg_gl_segs.range_code);
513 debug (l_module_name, 'display_size='||lseg_gl_segs.display_size);
514 debug (l_module_name, 'description_size='||lseg_gl_segs.description_size);
515 debug (l_module_name, 'concat_size='||lseg_gl_segs.concat_size);
516 debug (l_module_name, 'lov_prompt='||lseg_gl_segs.lov_prompt);
517 debug (l_module_name, 'window_prompt='||lseg_gl_segs.window_prompt);
518 debug (l_module_name, '=============================');
519 BEGIN
520 debug (l_module_name, 'Calling fnd_flex_key_api.find_structure');
521 lstr_glat_struc:=fnd_flex_key_api.find_structure
522 (
523 flexfield => lflx_glat_flex,
524 structure_number => p_coa_id
525 );
526 debug (l_module_name, '======================================');
527 debug (l_module_name, 'DUMP OF flex field structure SQLGL GLAT');
528 debug (l_module_name, '======================================');
529 debug (l_module_name, 'instantiated='||lstr_glat_struc.instantiated);
530 debug (l_module_name, 'structure_number='||lstr_glat_struc.structure_number);
531 debug (l_module_name, 'structure_code='||lstr_glat_struc.structure_code);
532 debug (l_module_name, 'structure_name='||lstr_glat_struc.structure_name);
533 debug (l_module_name, 'description='||lstr_glat_struc.description);
534 debug (l_module_name, 'view_name='||lstr_glat_struc.view_name);
535 debug (l_module_name, 'freeze_flag='||lstr_glat_struc.freeze_flag);
536 debug (l_module_name, 'enabled_flag='||lstr_glat_struc.enabled_flag);
537 debug (l_module_name, 'segment_separator='||lstr_glat_struc.segment_separator);
538 debug (l_module_name, 'cross_val_flag='||lstr_glat_struc.cross_val_flag);
539 debug (l_module_name, 'freeze_rollup_flag='||lstr_glat_struc.freeze_rollup_flag);
540 debug (l_module_name, 'dynamic_insert_flag='||lstr_glat_struc.dynamic_insert_flag);
541 debug (l_module_name, 'shorthand_enabled_flag='||lstr_glat_struc.shorthand_enabled_flag);
542 debug (l_module_name, 'shorthand_prompt='||lstr_glat_struc.shorthand_prompt);
543 debug (l_module_name, 'shorthand_length='||lstr_glat_struc.shorthand_length);
544 debug (l_module_name, '======================================');
545 EXCEPTION
546 WHEN no_data_found THEN
547 lstr_new_struc.structure_number :=p_coa_id;
548 lstr_new_struc.structure_code :=lstr_gl_struc.structure_code;
549 lstr_new_struc.structure_name :=lstr_gl_struc.structure_name;
550 lstr_new_struc.description :=substr('Reporting Attributes:'||lstr_gl_struc.description,1,240);
551 lstr_new_struc.view_name :=lstr_gl_struc.view_name;
552 lstr_new_struc.freeze_flag :='Y';
553 lstr_new_struc.enabled_flag :=lstr_gl_struc.enabled_flag;
554 lstr_new_struc.segment_separator :=lstr_gl_struc.segment_separator;
555 lstr_new_struc.cross_val_flag :=lstr_gl_struc.cross_val_flag;
556 lstr_new_struc.freeze_rollup_flag :=lstr_gl_struc.freeze_rollup_flag;
557 lstr_new_struc.dynamic_insert_flag :=lstr_gl_struc.dynamic_insert_flag;
558 lstr_new_struc.shorthand_enabled_flag:=lstr_gl_struc.shorthand_enabled_flag;
559 lstr_new_struc.shorthand_prompt :=lstr_gl_struc.shorthand_prompt;
560 lstr_new_struc.shorthand_length :=lstr_gl_struc.shorthand_length;
561 --
562 debug (l_module_name, '======================================');
563 debug (l_module_name, 'No structure for SQLGL GLAT Creating');
564 debug (l_module_name, '======================================');
565 debug (l_module_name, 'structure_number='||lstr_new_struc.structure_number);
566 debug (l_module_name, 'structure_code='||lstr_new_struc.structure_code);
567 debug (l_module_name, 'structure_name='||lstr_new_struc.structure_name);
568 debug (l_module_name, 'description='||lstr_new_struc.description);
569 debug (l_module_name, 'view_name='||lstr_new_struc.view_name);
570 debug (l_module_name, 'freeze_flag='||lstr_new_struc.freeze_flag);
571 debug (l_module_name, 'enabled_flag='||lstr_new_struc.enabled_flag);
572 debug (l_module_name, 'segment_separator='||lstr_new_struc.segment_separator);
573 debug (l_module_name, 'cross_val_flag='||lstr_new_struc.cross_val_flag);
574 debug (l_module_name, 'freeze_rollup_flag='||lstr_new_struc.freeze_rollup_flag);
575 debug (l_module_name, 'dynamic_insert_flag='||lstr_new_struc.dynamic_insert_flag);
576 debug (l_module_name, 'shorthand_enabled_flag='||lstr_new_struc.shorthand_enabled_flag);
577 debug (l_module_name, 'shorthand_prompt='||lstr_new_struc.shorthand_prompt);
578 debug (l_module_name, 'shorthand_length='||lstr_new_struc.shorthand_length);
579 debug (l_module_name, '======================================');
580 debug (l_module_name, 'Calling fnd_flex_key_api.add_structure');
581 fnd_flex_key_api.add_structure
582 (
583 flexfield=>lflx_glat_flex,
584 structure=>lstr_new_struc
585 );
586 --
587 debug (l_module_name, 'Calling fnd_flex_key_api.find_structure');
588 lstr_glat_struc :=fnd_flex_key_api.find_structure
589 (
590 flexfield=>lflx_glat_flex,
591 structure_number=>p_coa_id
592 );
593 debug (l_module_name, '======================================');
594 debug (l_module_name, 'DUMP OF flex field structure SQLGL GLAT after creating');
595 debug (l_module_name, '======================================');
596 debug (l_module_name, 'instantiated='||lstr_glat_struc.instantiated);
597 debug (l_module_name, 'structure_number='||lstr_glat_struc.structure_number);
598 debug (l_module_name, 'structure_code='||lstr_glat_struc.structure_code);
599 debug (l_module_name, 'structure_name='||lstr_glat_struc.structure_name);
600 debug (l_module_name, 'description='||lstr_glat_struc.description);
601 debug (l_module_name, 'view_name='||lstr_glat_struc.view_name);
602 debug (l_module_name, 'freeze_flag='||lstr_glat_struc.freeze_flag);
603 debug (l_module_name, 'enabled_flag='||lstr_glat_struc.enabled_flag);
604 debug (l_module_name, 'segment_separator='||lstr_glat_struc.segment_separator);
605 debug (l_module_name, 'cross_val_flag='||lstr_glat_struc.cross_val_flag);
606 debug (l_module_name, 'freeze_rollup_flag='||lstr_glat_struc.freeze_rollup_flag);
607 debug (l_module_name, 'dynamic_insert_flag='||lstr_glat_struc.dynamic_insert_flag);
608 debug (l_module_name, 'shorthand_enabled_flag='||lstr_glat_struc.shorthand_enabled_flag);
609 debug (l_module_name, 'shorthand_prompt='||lstr_glat_struc.shorthand_prompt);
610 debug (l_module_name, 'shorthand_length='||lstr_glat_struc.shorthand_length);
611 debug (l_module_name, '======================================');
612 END;
613
614
615 IF lc_new_flex_flag = 'N' THEN --GLAT Flexfield Structure
616 -----------------------------------------------------------
617 -- Delete the qualifiers for each segment of GLAT Structure
618 -- Delete the GLAT flexfield Structure
619 -- Deletes segments and attribute values
620 -----------------------------------------------------------
621 FOR cv_del_seg_attr_cur IN seg_attr_cur(p_coa_id,
622 'GLLE',
623 101,
624 lseg_gl_segs.column_name) LOOP
625 debug (l_module_name, 'Updating fnd_segment_attribute_values for '||cv_del_seg_attr_cur.segment_attribute_type);
626 UPDATE fnd_segment_attribute_values sav
627 SET sav.attribute_value = 'N'
628 WHERE sav.application_id = 101
629 AND sav.id_flex_code = 'GLAT'
630 AND sav.attribute_value = 'Y'
631 AND sav.segment_attribute_type = cv_del_seg_attr_cur.segment_attribute_type;
632 debug (l_module_name, 'Updated '||SQL%ROWCOUNT||' rows.');
633
634 debug (l_module_name, 'Deleting flex field qualifier '||cv_del_seg_attr_cur.segment_attribute_type);
635 debug (l_module_name, 'Calling fnd_flex_key_api.delete_flex_qualifier');
636 ln_func:=fnd_flex_key_api.delete_flex_qualifier
637 (
638 flexfield=>lflx_glat_flex,
639 qualifier_name=>cv_del_seg_attr_cur.segment_attribute_type
640 );
641 debug (l_module_name, 'ln_func='||ln_func);
642 IF (ln_func < 0) THEN
643 lc_api_message:=fnd_flex_key_api.message;
644 error (l_module_name, lc_api_message);
645 RAISE lexp_error;
646 END IF;
647 END LOOP;
648 --
649 debug (l_module_name, 'Calling fnd_flex_key_api.delete_structure');
650 fnd_flex_key_api.delete_structure
651 (
652 lflx_glat_flex,
653 lstr_glat_struc
654 );
655 ---------------------------------------
656 -- Create New structure for GLAT Flexfield
657 ----------------------------------------
658 debug (l_module_name, 'Calling fnd_flex_key_api.find_flexfield');
659 lflx_glat_flex :=fnd_flex_key_api.find_flexfield('SQLGL', 'GLAT');
660 --
661 lstr_new_struc.structure_number :=p_coa_id;
662 lstr_new_struc.structure_code :=lstr_gl_struc.structure_code;
663 lstr_new_struc.structure_name :=lstr_gl_struc.structure_name;
664 lstr_new_struc.description :=substr('Reporting Attributes:'||lstr_gl_struc.description,1,240);
665 lstr_new_struc.view_name :=lstr_gl_struc.view_name;
666 lstr_new_struc.freeze_flag :='Y';
667 lstr_new_struc.enabled_flag :=lstr_gl_struc.enabled_flag;
668 lstr_new_struc.segment_separator :=lstr_gl_struc.segment_separator;
669 lstr_new_struc.cross_val_flag :=lstr_gl_struc.cross_val_flag;
670 lstr_new_struc.freeze_rollup_flag :=lstr_gl_struc.freeze_rollup_flag;
671 lstr_new_struc.dynamic_insert_flag :=lstr_gl_struc.dynamic_insert_flag;
672 lstr_new_struc.shorthand_enabled_flag :=lstr_gl_struc.shorthand_enabled_flag;
673 lstr_new_struc.shorthand_prompt :=lstr_gl_struc.shorthand_prompt;
674 lstr_new_struc.shorthand_length :=lstr_gl_struc.shorthand_length;
675 -------------------
676 --Add new structure
677 -------------------
678 debug (l_module_name, 'Calling fnd_flex_key_api.add_structure');
679 fnd_flex_key_api.add_structure
680 (
681 flexfield=>lflx_glat_flex,
682 structure=>lstr_new_struc
683 );
684 debug (l_module_name, 'Calling fnd_flex_key_api.find_structure');
685 lstr_glat_struc:=fnd_flex_key_api.find_structure
686 (
687 flexfield=>lflx_glat_flex,
688 structure_number=>p_coa_id
689 );
690 -- Set the flag so that this executes only once
691 lc_new_flex_flag :='E';
692 END IF;
693 --
694
695
696 BEGIN
697 -----------------------------
698 -- Add the GL Segment to GLAT
699 -----------------------------
700 debug (l_module_name, 'lseg_gl_segs.value_set_name='||lseg_gl_segs.value_set_name);
701 debug (l_module_name, 'lseg_gl_segs.value_set_id='||lseg_gl_segs.value_set_id);
702 IF (lseg_gl_segs.value_set_name IS NULL) AND
703 (lseg_gl_segs.value_set_id IS NOT NULL) THEN
704 debug (l_module_name, 'Calling fnd_vset.get_valueset');
705 fnd_vset.get_valueset
706 (
707 valueset_id =>lseg_gl_segs.value_set_id,
708 valueset =>lval_valueset,
709 format =>lval_format
710 );
711 lc_flex_val_set_name := lval_valueset.name;
712 ELSE
713 lc_flex_val_set_name := lseg_gl_segs.value_set_name;
714 END IF;
715
716 --
717
718
719 debug (l_module_name, 'Calling fnd_flex_key_api.new_segment');
720 lseg_new_seg:= fnd_flex_key_api.new_segment
721 (
722 flexfield =>lflx_glat_flex,
723 structure =>lstr_glat_struc,
724 segment_name =>lseg_gl_segs.segment_name,
725 description =>lseg_gl_segs.description,
726 column_name =>lseg_gl_segs.column_name,
727 segment_number =>ln_seg_num,
728 enabled_flag =>lseg_gl_segs.enabled_flag,
729 displayed_flag =>lseg_gl_segs.displayed_flag,
730 indexed_flag =>lseg_gl_segs.indexed_flag,
731 value_set =>lc_flex_val_set_name,
732 default_type =>lseg_gl_segs.default_type,
733 default_value =>lseg_gl_segs.default_value,
734 required_flag =>lseg_gl_segs.required_flag,
735 security_flag =>lseg_gl_segs.security_flag,
736 range_code =>lseg_gl_segs.range_code,
737 display_size =>lseg_gl_segs.display_size,
738 description_size =>lseg_gl_segs.description_size,
739 concat_size =>lseg_gl_segs.concat_size,
740 lov_prompt =>lseg_gl_segs.lov_prompt,
741 window_prompt =>lseg_gl_segs.window_prompt
742 );
743 --------------------------------------
744 --Add a new segment for GLAT Flexfield
745 --------------------------------------
746 debug (l_module_name, 'Calling fnd_flex_key_api.add_segment');
747 fnd_flex_key_api.add_segment
748 (
749 flexfield =>lflx_glat_flex,
750 structure =>lstr_glat_struc,
751 segment =>lseg_new_seg
752 );
753 ln_seg_num:=ln_seg_num+1;
754 END;
755
756 ------------------------------------------------------------
757 -- Get the rows from ATTR Table corresponding to the segment
758 ------------------------------------------------------------
759 BEGIN
760 -- fnd_file.put_line(fnd_file.log,'Value set for the segment ' || lseg_gl_segs.column_name || ' is ' || lc_flex_val_set_name);
761 debug (l_module_name, 'For Loop c_rep_attr_cur');
762 debug (l_module_name, 'lseg_gl_segs.column_name='||lseg_gl_segs.column_name);
763 debug (l_module_name, 'lc_flex_val_set_name='||lc_flex_val_set_name);
764 FOR c_rep_attr_cur IN rep_attr_cur(lseg_gl_segs.column_name,lc_flex_val_set_name,0) LOOP
765 -------------------------------
766 --Create corresponding segments
767 -------------------------------
768 debug (l_module_name, 'Calling fnd_flex_key_api.new_segment');
769 lseg_att_new_seg:= fnd_flex_key_api.new_segment
770 (
771 flexfield =>lflx_glat_flex,
772 structure =>lstr_glat_struc,
773 segment_name =>c_rep_attr_cur.application_column_name,
774 description =>c_rep_attr_cur.description,
775 column_name =>c_rep_attr_cur.attr_segment_name,
776 segment_number =>ln_seg_num,
777 enabled_flag =>'Y',
778 displayed_flag =>'Y',
779 indexed_flag =>c_rep_attr_cur.index_flag,
780 value_set =>c_rep_attr_cur.value_set_name,
781 default_type =>c_rep_attr_cur.default_type,
782 default_value =>c_rep_attr_cur.default_value,
783 required_flag =>'Y',
784 security_flag =>'Y',
785 range_code =>c_rep_attr_cur.range_code,
786 display_size =>c_rep_attr_cur.display_size,
787 description_size =>lseg_gl_segs.description_size,
788 concat_size =>25,
789 lov_prompt =>c_rep_attr_cur.form_above_prompt,
790 window_prompt => c_rep_attr_cur.form_left_prompt
791 );
792 --Enable SEGMENT_ATTRIBUTE columns
793 BEGIN
794 debug (l_module_name, 'Calling fnd_flex_key_api.enable_column');
795 fnd_flex_key_api.enable_column
796 (
797 lflx_glat_flex,
798 lseg_att_new_seg.column_name
799 );
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 debug (l_module_name, 'Exception Null');
803 null;
804 END;
805 --Add the Segments
806 debug (l_module_name, 'Calling fnd_flex_key_api.add_segment');
807 fnd_flex_key_api.add_segment
808 (
809 flexfield =>lflx_glat_flex,
810 structure =>lstr_glat_struc,
811 segment =>lseg_att_new_seg
812 );
813 ln_seg_num:=ln_seg_num+1;
814 END LOOP;
815 END;
816 --
817 -- increment the counter
818 --
819 ln_segs_ctr:=ln_segs_ctr+1;
820 END LOOP;
821 -----------------------------------------
822 -- Add the qualifiers for each segment
823 -----------------------------------------
824 BEGIN
825 debug (l_module_name, 'Calling fnd_flex_key_api.get_segments');
826 fnd_flex_key_api.get_segments
827 (
828 flexfield=>lflx_glat_flex,
829 structure=>lstr_glat_struc,
830 nsegments=>ln_glat_nsegs,
831 segments=>llst_glat_seg_list
832 );
833
834 ln_glat_segs_ctr := 1;
835 WHILE ln_glat_segs_ctr<=ln_glat_nsegs LOOP
836 debug (l_module_name, 'Calling fnd_flex_key_api.find_segment');
837 lseg_glat_segs:=fnd_flex_key_api.find_segment
838 (
839 lflx_glat_flex,
840 lstr_glat_struc,
841 llst_glat_seg_list(ln_glat_segs_ctr)
842 );
843 debug (l_module_name, 'For Loop cv_seg_attr_cur');
844 debug (l_module_name, 'p_coa_id='||p_coa_id);
845 debug (l_module_name, 'lseg_glat_segs.column_name='||lseg_glat_segs.column_name);
846 FOR cv_seg_attr_cur IN seg_attr_cur
847 (
848 p_coa_id,
849 'GLLE',
850 101,
851 lseg_glat_segs.column_name
852 ) LOOP
853 debug (l_module_name, 'ln_flex_attr_flag='||ln_flex_attr_flag);
854 IF ln_flex_attr_flag=0 THEN
855 debug (l_module_name, 'Calling fnd_flex_key_api.add_flex_qualifier');
856 debug (l_module_name, 'segment_attribute_type='||cv_seg_attr_cur.segment_attribute_type);
857 debug (l_module_name, 'segment_prompt='||cv_seg_attr_cur.segment_prompt);
858 debug (l_module_name, 'description='||cv_seg_attr_cur.description);
859 debug (l_module_name, 'global_flag='||cv_seg_attr_cur.global_flag);
860 debug (l_module_name, 'required_flag='||cv_seg_attr_cur.required_flag);
861 debug (l_module_name, 'unique_flag='||cv_seg_attr_cur.unique_flag);
862 fnd_flex_key_api.add_flex_qualifier
863 (
864 flexfield =>lflx_glat_flex,
865 qualifier_name =>cv_seg_attr_cur.segment_attribute_type,
866 prompt =>cv_seg_attr_cur.segment_prompt,
867 description =>cv_seg_attr_cur.description,
868 global_flag =>cv_seg_attr_cur.global_flag,
869 required_flag =>cv_seg_attr_cur.required_flag,
870 unique_flag =>cv_seg_attr_cur.unique_flag
871 );
872 END IF;
873 debug (l_module_name, 'Calling fnd_flex_key_api.assign_qualifier');
874 fnd_flex_key_api.assign_qualifier
875 (
876 flexfield =>lflx_glat_flex,
877 structure=>lstr_glat_struc,
878 segment=>lseg_glat_segs,
879 flexfield_qualifier=>cv_seg_attr_cur.segment_attribute_type,
880 enable_flag=>cv_seg_attr_cur.attribute_value
881 );
882 END LOOP;
883 ln_flex_attr_flag:=1;
884 ln_glat_segs_ctr := ln_glat_segs_ctr+1;
885 END LOOP;
886 END;
887
888 -- Bug 3813504 .. Start
889
890 DECLARE
891 CURSOR c_other_coas IS
892 SELECT id_flex_num
893 FROM fnd_id_flex_structures_vl
894 WHERE application_id = 101
895 AND id_flex_code = 'GLAT'
896 AND id_flex_num <> p_coa_id;
897
898 BEGIN
899 fnd_file.put_line(fnd_file.log, 'Assigning flexfield qualifiers for other chart of accounts...');
900 FOR coa_cntr IN c_other_coas
901 LOOP
902 fnd_file.put_line(fnd_file.log, 'Processing chart of account : '||coa_cntr.id_flex_num);
903 BEGIN
904 lstr_glat_struc:=fnd_flex_key_api.find_structure
905 (flexfield => lflx_glat_flex,
906 structure_number=> coa_cntr.id_flex_num);
907
908 fnd_flex_key_api.get_segments(flexfield=>lflx_glat_flex,
909 structure=>lstr_glat_struc,
910 nsegments=>ln_glat_nsegs,
911 segments=>llst_glat_seg_list);
912
913 ln_glat_segs_ctr := 1;
914 WHILE ln_glat_segs_ctr <= ln_glat_nsegs LOOP
915 lseg_glat_segs:=fnd_flex_key_api.find_segment(lflx_glat_flex,
916 lstr_glat_struc,
917 llst_glat_seg_list(ln_glat_segs_ctr));
918 FOR cv_seg_attr_cur in seg_attr_cur(coa_cntr.id_flex_num,
919 'GLLE',
920 101,
921 lseg_glat_segs.column_name)
922 LOOP
923
924 fnd_flex_key_api.assign_qualifier
925 (flexfield =>lflx_glat_flex,
926 structure=>lstr_glat_struc,
927 segment=>lseg_glat_segs,
928 flexfield_qualifier=>cv_seg_attr_cur.segment_attribute_type,
929 enable_flag=>cv_seg_attr_cur.attribute_value);
930 END LOOP;
931 ln_flex_attr_flag:=1;
932 ln_glat_segs_ctr := ln_glat_segs_ctr+1;
933 END LOOP;
934 EXCEPTION
935 WHEN OTHERS THEN
936 fnd_file.put_line(fnd_file.log,
937 'Chart of account : '||coa_cntr.id_flex_num||' qualifier assignment failed');
938 END;
939 END LOOP;
940 END;
941
942 -- Bug 3813504 .. End
943
944 -------------------
945 --Log file Messages
946 -------------------
947 commit;
948 fnd_file.put_line(FND_FILE.LOG,'Successful completion of Preparation Program');
949 fnd_file.put_line(FND_FILE.LOG,'Chart Of Accounts: '||p_coa_id );
950 fnd_file.put_line(FND_FILE.LOG,'User ID : '||ln_userid );
951 ------------------------------
952 --Exception for the main Block
953 ------------------------------
954 EXCEPTION
955 WHEN lexp_error THEN
956 fnd_file.put_line(FND_FILE.LOG,'Program Completed With Error ');
957 fnd_file.put_line(FND_FILE.LOG,'ERROR :'||lc_err_message);
958 retcode := -1;
959 errbuf := null;
960 WHEN others THEN
961 lc_api_message:=fnd_flex_key_api.message;
962 fnd_file.put_line(FND_FILE.LOG,'Error:'||substr(lc_api_message,1,250));
963 retcode := -1;
964 errbuf := null;
965 END gl_preparation ;
966
967 procedure gl_history(
968 errbuf OUT NOCOPY VARCHAR2,
969 retcode OUT NOCOPY VARCHAR2,
970 p_ledger_id IN NUMBER,
971 p_segment_name IN VARCHAR2,
972 p_denormalized_segment IN VARCHAR2) is
973
974 --
975 lc_select_stmt varchar2(2000);
976 lc_sql_stmt varchar2(10000);
977 --
978 lc_segment_name varchar2(30);
979 lc_p_segment_name varchar2(30);
980 lc_parent_seg_name varchar2(30);
981 lc_p_denorm_seg varchar2(30);
982 --
983 ln_p_coa_id number;
984 ln_params number;
985 li_dummy integer;
986 li_dummy1 integer;
987 --
988 ln_flex_value_set_id number(10);
989 lc_attribute_num varchar2(30);
990 ln_table_id number(10);
991 lc_attr_seg_name varchar2(30);
992 ln_segment_num number(3);
993 lc_attr_segment_name varchar2(30);
994 lc_validation_type varchar2(1);
995 li_cursor_id integer ;
996 li_cursor_id2 integer ;
997 lc_application_column_name varchar2(30);
998 lc_val_table_name varchar2(30);
999 lc_seg_column_val_name varchar2(30);
1000 --
1001 lexp_error exception;
1002 lc_err_message varchar2(200);
1003 --
1004 ln_last_updated_by number;
1005 ln_userid number;
1006 lc_rep_profile varchar2(12);
1007 ---------------------------
1008 -- For FND_INSTALLATION API
1009 ---------------------------
1010 lc_int_status varchar2(10);
1011 lc_int_industry varchar2(10);
1012 lc_int_schema varchar2(10);
1013 ---------------------------
1014 -- INDUSTRY profile option
1015 ---------------------------
1016 lp_user_id number;
1017 lp_user_resp_id number;
1018 lp_resp_appl_id number;
1019 l_defined boolean;
1020
1021
1022 BEGIN
1023 SELECT chart_of_accounts_id
1024 INTO ln_p_coa_id
1025 FROM gl_ledgers
1026 WHERE ledger_id = p_ledger_id;
1027 ----------------------------------------------------------------------
1028 --Perform the Validation to Check for the Reporting Attributes Profile
1029 ----------------------------------------------------------------------
1030 BEGIN
1031 fnd_profile.get('USER_ID',ln_userid);
1032 ln_last_updated_by := to_number(ln_userid);
1033 IF ln_userid is NULL THEN
1034 lc_err_message := 'The Value for the Profile Option: USERID is NULL ';
1035 RAISE lexp_error;
1036 END IF;
1037 --
1038 fnd_profile.get('ATTRIBUTE_REPORTING',lc_rep_profile);
1039 --
1040 IF lc_rep_profile = 'N' THEN
1041 lc_err_message := 'The Value for the Profile Option: ATTRIBUTE REPORTING is not set ';
1042 RAISE lexp_error;
1043 END IF;
1044
1045
1046 -- The installation info is now implemented as a profile option (INDUSTRY).
1047
1048 -- Get Calling Application ID / Responsibility ID / User ID
1049
1050 lp_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
1051 lp_user_resp_id := FND_GLOBAL.RESP_ID;
1052 lp_user_id := FND_GLOBAL.USER_ID;
1053
1054 FND_PROFILE.GET_SPECIFIC('INDUSTRY',
1055 lp_user_id,
1056 lp_user_resp_id,
1057 lp_resp_appl_id,
1058 lc_int_industry,
1059 l_defined);
1060
1061 IF not l_defined then
1062 IF fnd_installation.get_app_info( application_short_name=>'SQLGL'
1063 ,status=>lc_int_status
1064 ,industry=>lc_int_industry
1065 ,oracle_schema=>lc_int_schema) THEN
1066
1067 IF lc_int_industry <> 'G' THEN
1068 lc_err_message := 'Oracle Government Ledger is not Installed';
1069 RAISE lexp_error;
1070 END IF;
1071
1072 END IF;
1073 ELSE
1074 IF lc_int_industry <> 'G' THEN
1075 lc_err_message := 'Oracle Government Ledger is not Installed';
1076 RAISE lexp_error;
1077 END IF;
1078
1079 END IF;
1080
1081 END;
1082 ------------------------------------
1083 --Assign parameter to the variables
1084 --Check the values of the parameters
1085 ------------------------------------
1086 BEGIN
1087 lc_p_segment_name := p_segment_name;
1088 lc_p_denorm_seg :=p_denormalized_segment;
1089 --
1090 IF (lc_p_segment_name IS NULL) AND (lc_p_denorm_seg IS NULL) THEN
1091 ln_params := 2;
1092 ELSIF (lc_p_segment_name IS NOT NULL) AND (lc_p_denorm_seg IS NOT NULL) THEN
1093 ln_params := 4;
1094 ELSE
1095 lc_err_message:='Incorrect number of Parameters';
1096 RAISE lexp_error;
1097 END IF;
1098 ----------------------------
1099 --Start the cursor statement
1100 ----------------------------
1101
1102 IF ln_params = 2 THEN
1103 lc_select_stmt:=
1104 'SELECT nvl(attr.flex_value_set_id,0),
1105 attr.attribute_num,
1106 attr.table_id,
1107 attr.application_column_name,
1108 attr.segment_name,
1109 attr.segment_num,
1110 valset.validation_type,
1111 attr.attr_segment_name
1112 FROM fnd_seg_rpt_attributes attr,
1113 fnd_flex_value_sets valset
1114 WHERE valset.flex_value_set_id = attr.flex_value_set_id
1115 AND attr.id_flex_num = :ln_p_coa_id ';
1116 ELSE
1117 lc_select_stmt:=
1118 'SELECT nvl(attr.flex_value_set_id,0),
1119 attr.attribute_num,
1120 attr.table_id,
1121 attr.application_column_name,
1122 attr.segment_name,
1123 attr.segment_num,
1124 valset.validation_type
1125 FROM fnd_seg_rpt_attributes attr,
1126 fnd_flex_value_sets valset
1127 WHERE valset.flex_value_set_id = attr.flex_value_set_id
1128 AND attr.id_flex_num = :ln_p_coa_id '||
1129 'AND attr.segment_name = :lc_p_segment_name '||
1130 'AND attr.attr_segment_name = :lc_p_denorm_seg ';
1131 END IF;
1132 END;
1133 --
1134 BEGIN
1135 --------------------------------
1136 --Open the cursor for attributes
1137 --------------------------------
1138 li_cursor_id :=DBMS_SQL.OPEN_CURSOR;
1139 DBMS_SQL.PARSE(li_cursor_id,lc_select_stmt,DBMS_SQL.v7);
1140 dbms_sql.bind_variable(li_cursor_id, ':ln_p_coa_id',ln_p_coa_id);
1141 IF ln_params <> 2 THEN
1142 dbms_sql.bind_variable(li_cursor_id, ':lc_p_segment_name',lc_p_segment_name);
1143 dbms_sql.bind_variable(li_cursor_id, ':lc_p_denorm_seg',lc_p_denorm_seg);
1144 END IF;
1145
1146 -----------------------------------------
1147 --Assign variables for the cursor columns
1148 -----------------------------------------
1149 IF ln_params = 2 THEN
1150 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,1,ln_flex_value_set_id);
1151 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,2,lc_attribute_num,30);
1152 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,3,ln_table_id);
1153 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,4,lc_application_column_name,30);
1154 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,5,lc_segment_name,30);
1155 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,6,ln_segment_num);
1156 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,7,lc_validation_type,1);
1157 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,8,lc_attr_segment_name,30);
1158 ELSE
1159 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,1,ln_flex_value_set_id);
1160 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,2,lc_attribute_num,30);
1161 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,3,ln_table_id);
1162 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,4,lc_application_column_name,30);
1163 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,5,lc_segment_name,30);
1164 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,6,ln_segment_num);
1165 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,7,lc_validation_type,1);
1166 END IF;
1167 -----------------------------------
1168 --Execute the cursor for attributes
1169 -----------------------------------
1170 li_dummy:=DBMS_SQL.EXECUTE(li_cursor_id);
1171 --
1172 -----------------------------------------
1173 --Start building the the update statement
1174 -----------------------------------------
1175 lc_sql_stmt:='UPDATE gl_code_combinations glcc SET ';
1176
1177 LOOP
1178 -------------------------------
1179 --Fetch each row for attributes
1180 -------------------------------
1181 IF DBMS_SQL.FETCH_ROWS(li_cursor_id) = 0 THEN
1182 EXIT;
1183 END IF;
1184 IF ln_params=2 THEN
1185 DBMS_SQL.COLUMN_VALUE(li_cursor_id,1,ln_flex_value_set_id);
1186 DBMS_SQL.COLUMN_VALUE(li_cursor_id,2,lc_attribute_num);
1187 DBMS_SQL.COLUMN_VALUE(li_cursor_id,3,ln_table_id);
1188 DBMS_SQL.COLUMN_VALUE(li_cursor_id,4,lc_application_column_name);
1189 DBMS_SQL.COLUMN_VALUE(li_cursor_id,5,lc_segment_name);
1190 DBMS_SQL.COLUMN_VALUE(li_cursor_id,6,ln_segment_num);
1191 DBMS_SQL.COLUMN_VALUE(li_cursor_id,7,lc_validation_type);
1192 DBMS_SQL.COLUMN_VALUE(li_cursor_id,8,lc_attr_segment_name);
1193 ELSE
1194 DBMS_SQL.COLUMN_VALUE(li_cursor_id,1,ln_flex_value_set_id);
1195 DBMS_SQL.COLUMN_VALUE(li_cursor_id,2,lc_attribute_num);
1196 DBMS_SQL.COLUMN_VALUE(li_cursor_id,3,ln_table_id);
1197 DBMS_SQL.COLUMN_VALUE(li_cursor_id,4,lc_application_column_name);
1198 DBMS_SQL.COLUMN_VALUE(li_cursor_id,5,lc_segment_name);
1199 DBMS_SQL.COLUMN_VALUE(li_cursor_id,6,ln_segment_num);
1200 DBMS_SQL.COLUMN_VALUE(li_cursor_id,7,lc_validation_type);
1201 END IF;
1202
1203 ----------------------------------
1204 --If the validation is independent
1205 ----------------------------------
1206 IF (lc_validation_type = 'I' OR lc_validation_type='D') THEN
1207 IF ln_params=2 THEN
1208 lc_sql_stmt:=lc_sql_stmt||lc_attr_segment_name;
1209 ELSE
1210 lc_sql_stmt:=lc_sql_stmt||lc_p_denorm_seg;
1211 END IF;
1212 --
1213 lc_sql_stmt :=lc_sql_stmt||'=(SELECT '|| lc_attribute_num||
1214 ' FROM fnd_flex_values ffval'||
1215 ' WHERE flex_value_set_id = ' ||ln_flex_value_set_id||
1216 ' AND ffval.flex_value = glcc.'||lc_segment_name;
1217 --
1218 IF lc_validation_type = 'D' THEN
1219 SELECT application_column_name
1220 INTO lc_parent_seg_name
1221 FROM fnd_id_flex_segments
1222 WHERE id_flex_code = 'GLAT'
1223 AND flex_value_set_id =
1224 (SELECT parent_flex_value_set_id
1225 FROM fnd_flex_value_sets
1226 WHERE flex_value_set_id = ln_flex_value_set_id);
1227 --
1228 IF lc_parent_seg_name IS NULL THEN
1229 lc_err_message:='The Parent Seg name is null for the Dependent Value Set';
1230 RAISE lexp_error;
1231 END IF;
1232 --
1233 lc_sql_stmt :=lc_sql_stmt||' AND ffval.parent_flex_value_low = glcc.'||lc_parent_seg_name;
1234 END IF;
1235 END IF;
1236 -------------------------------------
1237 --If the validation is based on table
1238 -------------------------------------
1239 IF lc_validation_type = 'F' THEN
1240 SELECT user_table_name
1241 INTO lc_val_table_name
1242 FROM fnd_tables
1243 WHERE application_id = 101
1244 AND table_id = ln_table_id;
1245 --
1246 SELECT value_column_name
1247 INTO lc_seg_column_val_name
1248 FROM fnd_flex_validation_tables
1249 WHERE flex_value_set_id = ln_flex_value_set_id;
1250 --
1251 IF ln_params = 2 THEN
1252 lc_sql_stmt:=lc_sql_stmt||lc_attr_segment_name;
1253 ELSE
1254 lc_sql_stmt:=lc_sql_stmt||lc_p_denorm_seg;
1255 END IF;
1256 lc_sql_stmt :=lc_sql_stmt||' = (SELECT '|| lc_attr_seg_name||
1257 ' FROM '|| lc_val_table_name||
1258 ' VAL WHERE VAL.'|| lc_seg_column_val_name||
1259 '= glcc.'|| lc_segment_name;
1260 END IF;
1261 lc_sql_stmt:=lc_sql_stmt||'),';
1262
1263 END LOOP;
1264
1265 -----------------------------------------
1266 --Append who columns for update statement
1267 -----------------------------------------
1268 lc_sql_stmt := lc_sql_stmt|| 'LAST_UPDATE_DATE = sysdate,'||
1269 'LAST_UPDATED_BY = '||ln_last_updated_by||
1270 ' WHERE CHART_OF_ACCOUNTS_ID = :ln_p_coa_id ';
1271
1272 ----------------------------------------------
1273 --Open, Parse and Execute the Update statement
1274 ----------------------------------------------
1275 li_cursor_id2:=DBMS_SQL.OPEN_CURSOR;
1276 --
1277 DBMS_SQL.PARSE(li_cursor_id2,lc_sql_stmt,DBMS_SQL.v7);
1278 dbms_sql.bind_variable(li_cursor_id2, ':ln_p_coa_id',ln_p_coa_id);
1279 --
1280 li_dummy1:=DBMS_SQL.EXECUTE(li_cursor_id2);
1281 --
1282 DBMS_SQL.CLOSE_CURSOR(li_cursor_id);
1283 --
1284
1285 COMMIT;
1286
1287 --
1288 fnd_file.put_line(FND_FILE.LOG,'Historical Program successfully completed');
1289 fnd_file.put_line(FND_FILE.LOG,'User ID :'||ln_userid);
1290 fnd_file.put_line(FND_FILE.LOG,'Date :'||sysdate);
1291 fnd_file.put_line(FND_FILE.LOG,'COA ID :'||ln_p_coa_id);
1292 END;
1293 EXCEPTION
1294 WHEN lexp_error THEN
1295 fnd_file.put_line(FND_FILE.LOG,'Program Completed With Error ');
1296 fnd_file.put_line(FND_FILE.LOG,'Error : '||lc_err_message);
1297 retcode := -1;
1298 errbuf := null;
1299 WHEN others THEN
1300 fnd_file.put_line(FND_FILE.LOG,'Error : '||substr(SQLERRM,1,70));
1301 retcode := -1;
1302 errbuf := null;
1303 END gl_history;
1304 BEGIN
1305 initialize_global_variables;
1306 END PSA_REP_ATTRIBUTES;