[Home] [Help]
PACKAGE BODY: APPS.GL_REP_ATTRIBUTES
Source
1 PACKAGE BODY GL_REP_ATTRIBUTES AS
2 /* $Header: glgrattb.pls 120.8 2006/03/29 09:59:24 kbhatt noship $ */
3
4 procedure gl_preparation( retcode OUT NOCOPY VARCHAR2,
5 errbuf OUT NOCOPY VARCHAR2,
6 p_chart_of_accounts_id IN NUMBER) is
7 ---------------------
8 -- Flexfield API type
9 ---------------------
10 lseg_gl_segs fnd_flex_key_api.segment_type; -- Segment Type
11 lseg_glat_segs fnd_flex_key_api.segment_type; -- Segment Type
12 lseg_new_seg fnd_flex_key_api.segment_type;
13 lseg_att_new_seg fnd_flex_key_api.segment_type;
14 --
15 lstr_gl_struc fnd_flex_key_api.structure_type; -- Structure Type
16 lstr_new_struc fnd_flex_key_api.structure_type;
17 lstr_glat_struc fnd_flex_key_api.structure_type;
18 --
19 lflx_gl_flex fnd_flex_key_api.flexfield_type; -- Flexfield Type
20 lflx_new_flex fnd_flex_key_api.flexfield_type;
21 lflx_glat_flex fnd_flex_key_api.flexfield_type;
22 --
23 llst_gl_seg_list fnd_flex_key_api.segment_list; -- Segment List
24 llst_glat_seg_list fnd_flex_key_api.segment_list;
25 ---------------------
26 -- Value set API
27 ---------------------
28 lval_valueset fnd_vset.valueset_r;
29 lval_format fnd_vset.valueset_dr;
30 ---------------------
31 -- Flags and Counters
32 ---------------------
33 lc_flex_val_set_name fnd_flex_value_sets.flex_value_set_name%type;
34 lc_new_flex_flag varchar2(1):='N';
35 ln_no_of_attributes number;
36 ln_func number;
37 ln_gl_nsegs number;
38 ln_glat_nsegs number;
39 ln_segs_ctr number;
40 ln_seg_num number;
41 ln_glat_segs_ctr number;
42 ln_flex_attr_flag number:=0;
43 p_coa_id number:= p_chart_of_accounts_id;
44 ----------------------
45 --Messages
46 ----------------------
47 lc_api_message varchar2(2000); -- For API Message
48 lc_rep_profile varchar2(132);
49 lc_err_message varchar2(200);
50 lexp_error exception;
51 ln_userid number;
52 ---------------------------
53 -- For FND_INSTALLATION API
54 ---------------------------
55 lc_int_status varchar2(10);
56 lc_int_industry varchar2(10);
57 lc_int_schema varchar2(10);
58 ---------------------------
59 -- INDUSTRY profile option
60 ---------------------------
61 lp_user_id number;
62 lp_user_resp_id number;
63 lp_resp_appl_id number;
64 l_defined boolean;
65
66 --------------------------------
67 -- Segment Attribute Types Cursor
68 --------------------------------
69 CURSOR seg_attr_cur (p_id_flex_num number,
70 p_id_flex_code varchar2,
71 p_application_id number,
72 p_app_seg_name varchar2) IS
73 SELECT
74 typ.segment_attribute_type segment_attribute_type,
75 typ.segment_prompt segment_prompt,
76 typ.description description,
77 typ.global_flag global_flag,
78 typ.required_flag required_flag,
79 typ.unique_flag unique_flag,
80 val.attribute_value attribute_value
81 FROM fnd_segment_attribute_values val,
82 fnd_segment_attribute_types typ
83 WHERE val.id_flex_num =p_id_flex_num -- Bug 3813504
84 AND val.id_flex_code = p_id_flex_code
85 AND val.id_flex_code = typ.id_flex_code
86 AND typ.segment_attribute_type = val.segment_attribute_type
87 AND val.application_id = p_application_id
88 AND val.application_id = typ.application_id
89 AND val.application_column_name = p_app_seg_name;
90 -----------------------------
91 -- Reporting Attributes Cursor
92 -- This cursor will select all
93 -- the attributes defined for
94 -- the segment
95 -----------------------------
96 -- Bug 4128077
97 CURSOR rep_attr_cur(p_seg_name varchar , p_seg_vset_name VARCHAR2, p_application_id number) IS
98 SELECT sequence,
99 att.application_id,
100 att.id_flex_code,
101 att.id_flex_num,
102 att.attr_segment_name,
103 att.application_column_name,
104 val.flex_value_set_name value_set_name,
105 att.user_column_name,
106 att.index_flag,
107 att.form_left_prompt,
108 att.form_above_prompt,
109 att.display_size,
110 att.description,
111 att.table_id,
112 att.attribute_num,
113 att.segment_name,
114 fdu.default_type ,
115 fdu.default_value ,
116 fdu.range_code
117 FROM fnd_seg_rpt_attributes att,
118 fnd_flex_value_sets val ,
119 fnd_descr_flex_column_usages fdu
120 WHERE segment_name = p_seg_name
121 AND att.attr_value_set_id = val.flex_value_set_id
122 AND att.enabled_flag = 'Y'
123 AND att.id_flex_num = p_coa_id
124 AND fdu.descriptive_flexfield_name = 'FND_FLEX_VALUES'
125 AND fdu.descriptive_flex_context_code = p_seg_vset_name
126 AND fdu.flex_value_set_id = val.flex_value_set_id
127 AND att.application_column_name = fdu.end_user_column_name
128 AND fdu.application_id=p_application_id
129 ORDER BY 1;
130
131 --================================
132 BEGIN -- This is the main Block
133 --================================
134 ---------------------------------------
135 --This Block does the validation to
136 --check whether the eporting Attributes
137 --profile is set and the installation
138 --is Goverment type
139 ---------------------------------------
140 BEGIN
141
142 -- The installation info is now implemented as a profile option (INDUSTRY).
143
144 -- Get Calling Application ID / Responsibility ID / User ID
145
146 lp_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
147 lp_user_resp_id := FND_GLOBAL.RESP_ID;
148 lp_user_id := FND_GLOBAL.USER_ID;
149
150 FND_PROFILE.GET_SPECIFIC('INDUSTRY',
151 lp_user_id,
152 lp_user_resp_id,
153 lp_resp_appl_id,
154 lc_int_industry,
155 l_defined);
156
157 IF not l_defined then
158 IF fnd_installation.get_app_info( application_short_name=>'SQLGL'
159 ,status=>lc_int_status
160 ,industry=>lc_int_industry
161 ,oracle_schema=>lc_int_schema) THEN
162
163 IF lc_int_industry <> 'G' THEN
164 lc_err_message := 'Oracle Government Ledger is not Installed';
165 RAISE lexp_error;
166 END IF;
167
168 END IF;
169 ELSE
170 IF lc_int_industry <> 'G' THEN
171 lc_err_message := 'Oracle Government Ledger is not Installed';
172 RAISE lexp_error;
173 END IF;
174
175 END IF;
176
177 --
178 fnd_profile.get('USER_ID',ln_userid);
179 fnd_profile.get('ATTRIBUTE_REPORTING',lc_rep_profile);
180 IF lc_rep_profile = 'N' THEN
181 lc_err_message := 'The Value for the Profile Option: ATTRIBUTE REPORTING is not set ';
182 RAISE lexp_error;
183 END IF;
184 --
185 SELECT COUNT(*)
186 INTO ln_no_of_attributes
187 FROM fnd_seg_rpt_attributes
188 WHERE application_id = 101
189 AND id_flex_code = 'GLAT'
190 AND id_flex_num = p_coa_id;
191 IF ln_no_of_attributes > 42 THEN
192 lc_err_message := 'The number of attributes defined can not be greater than 42';
193 RAISE lexp_error;
194 END IF;
195 END;
196 --------------------------------------------------
197 --Initiate the flexfied api
198 --Find the Info about the GL Accounting flexfied
199 --Find the Structure and Segments for the entered
200 --chart of accounts ID
201 --------------------------------------------------
202 BEGIN
203 fnd_flex_key_api.set_session_mode('seed_data');
204 --
205 lflx_gl_flex := fnd_flex_key_api.find_flexfield('SQLGL','GL#');
206 lstr_gl_struc := fnd_flex_key_api.find_structure(lflx_gl_flex,p_coa_id);
207 fnd_flex_key_api.get_segments(flexfield=>lflx_gl_flex,
208 structure=>lstr_gl_struc,
209 nsegments=>ln_gl_nsegs, --nseg stores the number of segments
210 segments=>llst_gl_seg_list);
211 END;
212 ---------------------------------------------
213 --Find Whether GLAT Flexfield exists
214 --When GLAT flexfield does not exist
215 --create a new Flexfield with Flex code GLAT
216 --and register the flexfield
217 --and Create New Structure for GLAT Flexfield
218 --Set the Check Flag as there is no need to
219 --delete records for GLAT Flexfield
220 --Also set the flag for new GLAT flexfield
221 ---------------------------------------------
222 BEGIN
223 lflx_glat_flex :=fnd_flex_key_api.find_flexfield('SQLGL',
224 'GLAT');
225 EXCEPTION WHEN no_data_found THEN
226 lflx_new_flex := fnd_flex_key_api.new_flexfield
227 (appl_short_name =>lflx_gl_flex.appl_short_name,
228 flex_code =>'GLAT',
229 flex_title =>substr('Reporting Attributes:'||lflx_gl_flex.flex_title,1,30),
230 description =>substr('Reporting Attributes:'||lflx_gl_flex.description,1,240),
231 table_appl_short_name=>lflx_gl_flex.table_appl_short_name,
232 table_name =>lflx_gl_flex.table_name,
233 unique_id_column =>lflx_gl_flex.unique_id_column,
234 structure_column =>lflx_gl_flex.structure_column,
235 dynamic_inserts =>lflx_gl_flex.dynamic_inserts,
236 allow_id_value_sets =>lflx_gl_flex.allow_id_value_sets,
237 index_flag =>lflx_gl_flex.index_flag,
238 concat_seg_len_max =>lflx_gl_flex.concat_seg_len_max,
239 concat_len_warning =>lflx_gl_flex.concat_len_warning);
240 --
241 fnd_flex_key_api.register(flexfield =>lflx_new_flex,
242 enable_columns => 'Y');
243 --
244 lflx_glat_flex :=fnd_flex_key_api.find_flexfield('SQLGL',
245 'GLAT');
246 --
247 lc_new_flex_flag :='Y';
248 END;
249 -------------------------------------------------
250 --Intialize the counter
251 --LOOP for the each segment in the GL Flexfield
252 -- Get the GL Segment Details
253 -- IF GLAT flexfied exists THEN
254 -- Get segments for GLAT Structure
255 -- Delete the GLAT segments and other details
256 -- Create a new GLAT structure
257 -- END IF
258 -- Create new Segments for GLAT from GL segments
259 -- Create new Segments for GLAT from Attributes
260 --END LOOP
261 -------------------------------------------------
262 ln_segs_ctr := 1;
263 ln_seg_num := 1;
264 WHILE ln_segs_ctr<=ln_gl_nsegs LOOP
265 lseg_gl_segs:=fnd_flex_key_api.find_segment(lflx_gl_flex,
266 lstr_gl_struc,
267 llst_gl_seg_list(ln_segs_ctr));
268 BEGIN
269 lstr_glat_struc:=fnd_flex_key_api.find_structure
270 (flexfield =>lflx_glat_flex,
271 structure_number=>p_coa_id);
272 EXCEPTION WHEN no_data_found THEN
273 --
274 lstr_new_struc.structure_number :=p_coa_id;
275 lstr_new_struc.structure_code :=lstr_gl_struc.structure_code;
276 lstr_new_struc.structure_name :=lstr_gl_struc.structure_name;
277 lstr_new_struc.description :=substr('Reporting Attributes:'||lstr_gl_struc.description,1,240);
278 lstr_new_struc.view_name :=lstr_gl_struc.view_name;
279 lstr_new_struc.freeze_flag :='Y';
280 lstr_new_struc.enabled_flag :=lstr_gl_struc.enabled_flag;
281 lstr_new_struc.segment_separator :=lstr_gl_struc.segment_separator;
282 lstr_new_struc.cross_val_flag :=lstr_gl_struc.cross_val_flag;
283 lstr_new_struc.freeze_rollup_flag :=lstr_gl_struc.freeze_rollup_flag;
284 lstr_new_struc.dynamic_insert_flag :=lstr_gl_struc.dynamic_insert_flag;
285 lstr_new_struc.shorthand_enabled_flag:=lstr_gl_struc.shorthand_enabled_flag;
286 lstr_new_struc.shorthand_prompt :=lstr_gl_struc.shorthand_prompt;
287 lstr_new_struc.shorthand_length :=lstr_gl_struc.shorthand_length;
288 --
289 fnd_flex_key_api.add_structure(flexfield=>lflx_glat_flex,
290 structure=>lstr_new_struc);
291 --
292 lstr_glat_struc :=fnd_flex_key_api.find_structure(flexfield=>lflx_glat_flex,
293 structure_number=>p_coa_id);
294 END;
295
296
297 IF lc_new_flex_flag = 'N' THEN --GLAT Flexfield Structure
298 -----------------------------------------------------------
299 -- Delete the qualifiers for each segment of GLAT Structure
300 -- Delete the GLAT flexfield Structure
301 -- Deletes segments and attribute values
302 -----------------------------------------------------------
303 FOR cv_del_seg_attr_cur in seg_attr_cur(p_coa_id,
304 'GL#',
305 101,
306 lseg_gl_segs.column_name) LOOP
307 ln_func:=fnd_flex_key_api.delete_flex_qualifier
308 (flexfield=>lflx_glat_flex,
309 qualifier_name=>cv_del_seg_attr_cur.segment_attribute_type);
310 END LOOP;
311 --
312 fnd_flex_key_api.delete_structure(lflx_glat_flex,
313 lstr_glat_struc);
314 ---------------------------------------
315 -- Create New structure for GLAT Flexfield
316 ----------------------------------------
317 lflx_glat_flex :=fnd_flex_key_api.find_flexfield('SQLGL',
318 'GLAT');
319 --
320 lstr_new_struc.structure_number :=p_coa_id;
321 lstr_new_struc.structure_code :=lstr_gl_struc.structure_code;
322 lstr_new_struc.structure_name :=lstr_gl_struc.structure_name;
323 lstr_new_struc.description :=substr('Reporting Attributes:'||lstr_gl_struc.description,1,240);
324 lstr_new_struc.view_name :=lstr_gl_struc.view_name;
325 lstr_new_struc.freeze_flag :='Y';
326 lstr_new_struc.enabled_flag :=lstr_gl_struc.enabled_flag;
327 lstr_new_struc.segment_separator :=lstr_gl_struc.segment_separator;
328 lstr_new_struc.cross_val_flag :=lstr_gl_struc.cross_val_flag;
329 lstr_new_struc.freeze_rollup_flag :=lstr_gl_struc.freeze_rollup_flag;
330 lstr_new_struc.dynamic_insert_flag :=lstr_gl_struc.dynamic_insert_flag;
331 lstr_new_struc.shorthand_enabled_flag :=lstr_gl_struc.shorthand_enabled_flag;
332 lstr_new_struc.shorthand_prompt :=lstr_gl_struc.shorthand_prompt;
333 lstr_new_struc.shorthand_length :=lstr_gl_struc.shorthand_length;
334 -------------------
335 --Add new structure
336 -------------------
337 fnd_flex_key_api.add_structure(flexfield=>lflx_glat_flex,
338 structure=>lstr_new_struc);
339 lstr_glat_struc:=fnd_flex_key_api.find_structure(flexfield=>lflx_glat_flex,
340 structure_number=>p_coa_id);
341 -- Set the flag so that this executes only once
342 lc_new_flex_flag :='E';
343 END IF;
344 --
345
346
347 BEGIN
348 -----------------------------
349 -- Add the GL Segment to GLAT
350 -----------------------------
351 IF lseg_gl_segs.value_set_name IS NULL AND
352 lseg_gl_segs.value_set_id IS NOT NULL THEN
353 fnd_vset.get_valueset(valueset_id =>lseg_gl_segs.value_set_id,
354 valueset =>lval_valueset,
355 format =>lval_format);
356 lc_flex_val_set_name := lval_valueset.name;
357 ELSE
358 lc_flex_val_set_name := lseg_gl_segs.value_set_name;
359 END IF;
360
361 --
362
363
364 lseg_new_seg:= fnd_flex_key_api.new_segment
365 (flexfield =>lflx_glat_flex,
366 structure =>lstr_glat_struc,
367 segment_name =>lseg_gl_segs.segment_name,
368 description =>lseg_gl_segs.description,
369 column_name =>lseg_gl_segs.column_name,
370 segment_number =>ln_seg_num,
371 enabled_flag =>lseg_gl_segs.enabled_flag,
372 displayed_flag =>lseg_gl_segs.displayed_flag,
373 indexed_flag =>lseg_gl_segs.indexed_flag,
374 value_set =>lc_flex_val_set_name,
375 default_type =>lseg_gl_segs.default_type,
376 default_value =>lseg_gl_segs.default_value,
377 required_flag =>lseg_gl_segs.required_flag,
378 security_flag =>lseg_gl_segs.security_flag,
379 range_code =>lseg_gl_segs.range_code,
380 display_size =>lseg_gl_segs.display_size,
381 description_size =>lseg_gl_segs.description_size,
382 concat_size =>lseg_gl_segs.concat_size,
383 lov_prompt =>lseg_gl_segs.lov_prompt,
384 window_prompt =>lseg_gl_segs.window_prompt);
385 --------------------------------------
386 --Add a new segment for GLAT Flexfield
387 --------------------------------------
388 fnd_flex_key_api.add_segment(flexfield =>lflx_glat_flex,
389 structure =>lstr_glat_struc,
390 segment =>lseg_new_seg);
391 ln_seg_num:=ln_seg_num+1;
392 END;
393
394 ------------------------------------------------------------
395 -- Get the rows from ATTR Table corresponding to the segment
396 ------------------------------------------------------------
397 BEGIN
398 -- fnd_file.put_line(fnd_file.log,'Value set for the segment ' || lseg_gl_segs.column_name || ' is ' || lc_flex_val_set_name);
399 FOR c_rep_attr_cur IN rep_attr_cur(lseg_gl_segs.column_name,lc_flex_val_set_name,101) LOOP
400 -------------------------------
401 --Create corresponding segments
402 -------------------------------
403 lseg_att_new_seg:= fnd_flex_key_api.new_segment
404 (flexfield =>lflx_glat_flex,
405 structure =>lstr_glat_struc,
406 segment_name =>c_rep_attr_cur.application_column_name,
407 description =>c_rep_attr_cur.description,
408 column_name =>c_rep_attr_cur.attr_segment_name,
409 segment_number =>ln_seg_num,
410 enabled_flag =>'Y',
411 displayed_flag =>'Y',
412 indexed_flag =>c_rep_attr_cur.index_flag,
413 value_set =>c_rep_attr_cur.value_set_name,
414 default_type =>c_rep_attr_cur.default_type,
415 default_value =>c_rep_attr_cur.default_value,
416 required_flag =>'Y',
417 security_flag =>'Y',
418 range_code =>c_rep_attr_cur.range_code,
419 display_size =>c_rep_attr_cur.display_size,
420 description_size =>lseg_gl_segs.description_size,
421 concat_size =>25,
422 lov_prompt =>c_rep_attr_cur.form_above_prompt,
423 window_prompt => c_rep_attr_cur.form_left_prompt);
424 --Enable SEGMENT_ATTRIBUTE columns
425 BEGIN
426 fnd_flex_key_api.enable_column
427 (lflx_glat_flex,lseg_att_new_seg.column_name);
428 EXCEPTION WHEN no_data_found THEN
429 null;
430 END;
431 --Add the Segments
432 fnd_flex_key_api.add_segment(flexfield =>lflx_glat_flex,
433 structure =>lstr_glat_struc,
434 segment =>lseg_att_new_seg);
435 ln_seg_num:=ln_seg_num+1;
436 END LOOP;
437 END;
438 --
439 -- increment the counter
440 --
441 ln_segs_ctr:=ln_segs_ctr+1;
442 END LOOP;
443 -----------------------------------------
444 -- Add the qualifiers for each segment
445 -----------------------------------------
446 BEGIN
447 fnd_flex_key_api.get_segments(flexfield=>lflx_glat_flex,
448 structure=>lstr_glat_struc,
449 nsegments=>ln_glat_nsegs,
450 segments=>llst_glat_seg_list);
451
452
453 ln_glat_segs_ctr := 1;
454 WHILE ln_glat_segs_ctr<=ln_glat_nsegs LOOP
455 lseg_glat_segs:=fnd_flex_key_api.find_segment(lflx_glat_flex,
456 lstr_glat_struc,
457 llst_glat_seg_list(ln_glat_segs_ctr));
458 FOR cv_seg_attr_cur in seg_attr_cur(p_coa_id,
459 'GL#',
460 101,
461 lseg_glat_segs.column_name) LOOP
462 IF ln_flex_attr_flag=0 THEN
463 fnd_flex_key_api.add_flex_qualifier
464 (flexfield =>lflx_glat_flex,
465 qualifier_name=>cv_seg_attr_cur.segment_attribute_type,
466 prompt =>cv_seg_attr_cur.segment_prompt,
467 description =>cv_seg_attr_cur.description,
468 global_flag =>cv_seg_attr_cur.global_flag,
469 required_flag =>cv_seg_attr_cur.required_flag,
470 unique_flag =>cv_seg_attr_cur.unique_flag);
471 END IF;
472 fnd_flex_key_api.assign_qualifier
473 (flexfield =>lflx_glat_flex,
474 structure=>lstr_glat_struc,
475 segment=>lseg_glat_segs,
476 flexfield_qualifier=>cv_seg_attr_cur.segment_attribute_type,
477 enable_flag=>cv_seg_attr_cur.attribute_value);
478 END LOOP;
479 ln_flex_attr_flag:=1;
480 ln_glat_segs_ctr := ln_glat_segs_ctr+1;
481 END LOOP;
482 END;
483
484 -- Bug 3813504 .. Start
485
486 DECLARE
487 CURSOR c_other_coas IS
488 SELECT id_flex_num
489 FROM fnd_id_flex_structures_vl
490 WHERE application_id = 101
491 AND id_flex_code = 'GLAT'
492 AND id_flex_num <> p_coa_id;
493
494 BEGIN
495 fnd_file.put_line(fnd_file.log, 'Assigning flexfield qualifiers for other chart of accounts...');
496 FOR coa_cntr IN c_other_coas
497 LOOP
498 fnd_file.put_line(fnd_file.log, 'Processing chart of account : '||coa_cntr.id_flex_num);
499 BEGIN
500 lstr_glat_struc:=fnd_flex_key_api.find_structure
501 (flexfield => lflx_glat_flex,
502 structure_number=> coa_cntr.id_flex_num);
503
504 fnd_flex_key_api.get_segments(flexfield=>lflx_glat_flex,
505 structure=>lstr_glat_struc,
506 nsegments=>ln_glat_nsegs,
507 segments=>llst_glat_seg_list);
508
509 ln_glat_segs_ctr := 1;
510 WHILE ln_glat_segs_ctr <= ln_glat_nsegs LOOP
511 lseg_glat_segs:=fnd_flex_key_api.find_segment(lflx_glat_flex,
512 lstr_glat_struc,
513 llst_glat_seg_list(ln_glat_segs_ctr));
514 FOR cv_seg_attr_cur in seg_attr_cur(coa_cntr.id_flex_num,
515 'GL#',
516 101,
517 lseg_glat_segs.column_name)
518 LOOP
519
520 fnd_flex_key_api.assign_qualifier
521 (flexfield =>lflx_glat_flex,
522 structure=>lstr_glat_struc,
523 segment=>lseg_glat_segs,
524 flexfield_qualifier=>cv_seg_attr_cur.segment_attribute_type,
525 enable_flag=>cv_seg_attr_cur.attribute_value);
526 END LOOP;
527 ln_flex_attr_flag:=1;
528 ln_glat_segs_ctr := ln_glat_segs_ctr+1;
529 END LOOP;
530 EXCEPTION
531 WHEN OTHERS THEN
532 fnd_file.put_line(fnd_file.log,
533 'Chart of account : '||coa_cntr.id_flex_num||' qualifier assignment failed');
534 END;
535 END LOOP;
536 END;
537
538 -- Bug 3813504 .. End
539
540 -------------------
541 --Log file Messages
542 -------------------
543 commit;
544 fnd_file.put_line(FND_FILE.LOG,'Successful completion of Preparation Program');
545 fnd_file.put_line(FND_FILE.LOG,'Chart Of Accounts: '||p_coa_id );
546 fnd_file.put_line(FND_FILE.LOG,'User ID : '||ln_userid );
547 ------------------------------
548 --Exception for the main Block
549 ------------------------------
550 EXCEPTION
551 WHEN lexp_error THEN
552 fnd_file.put_line(FND_FILE.LOG,'Program Completed With Error ');
553 fnd_file.put_line(FND_FILE.LOG,'ERROR :'||lc_err_message);
554 retcode := -1;
555 errbuf := null;
556 WHEN others THEN
557 lc_api_message:=fnd_flex_key_api.message;
558 fnd_file.put_line(FND_FILE.LOG,'Error:'||substr(lc_api_message,1,250));
559 retcode := -1;
560 errbuf := null;
561 END gl_preparation ;
562
563 procedure gl_history( retcode OUT NOCOPY VARCHAR2,
564 errbuf OUT NOCOPY VARCHAR2,
565 p_chart_of_accounts_id IN NUMBER,
566 p_segment_name IN VARCHAR2,
567 p_denormalized_segment IN VARCHAR2) is
568
569 --
570 lc_select_stmt varchar2(2000);
571 lc_sql_stmt varchar2(10000);
572 --
573 lc_segment_name varchar2(30);
574 lc_p_segment_name varchar2(30);
575 lc_parent_seg_name varchar2(30);
576 lc_p_denorm_seg varchar2(30);
577 --
578 ln_p_coa_id number;
579 ln_params number;
580 li_dummy integer;
581 li_dummy1 integer;
582 --
583 ln_flex_value_set_id number(10);
584 lc_attribute_num varchar2(30);
585 ln_table_id number(10);
586 lc_attr_seg_name varchar2(30);
587 ln_segment_num number(3);
588 lc_attr_segment_name varchar2(30);
589 lc_validation_type varchar2(1);
590 li_cursor_id integer ;
591 li_cursor_id2 integer ;
592 lc_application_column_name varchar2(30);
593 lc_val_table_name varchar2(30);
594 lc_seg_column_val_name varchar2(30);
595 --
596 lexp_error exception;
597 lc_err_message varchar2(200);
598 --
599 ln_last_updated_by number;
600 ln_userid number;
601 lc_rep_profile varchar2(12);
602 ---------------------------
603 -- For FND_INSTALLATION API
604 ---------------------------
605 lc_int_status varchar2(10);
606 lc_int_industry varchar2(10);
607 lc_int_schema varchar2(10);
608 ---------------------------
609 -- INDUSTRY profile option
610 ---------------------------
611 lp_user_id number;
612 lp_user_resp_id number;
613 lp_resp_appl_id number;
614 l_defined boolean;
615
616
617 BEGIN
618 ----------------------------------------------------------------------
619 --Perform the Validation to Check for the Reporting Attributes Profile
620 ----------------------------------------------------------------------
621 BEGIN
622 fnd_profile.get('USER_ID',ln_userid);
623 ln_last_updated_by := to_number(ln_userid);
624 IF ln_userid is NULL THEN
625 lc_err_message := 'The Value for the Profile Option: USERID is NULL ';
626 RAISE lexp_error;
627 END IF;
628 --
629 fnd_profile.get('ATTRIBUTE_REPORTING',lc_rep_profile);
630 --
631 IF lc_rep_profile = 'N' THEN
632 lc_err_message := 'The Value for the Profile Option: ATTRIBUTE REPORTING is not set ';
633 RAISE lexp_error;
634 END IF;
635
636
637 -- The installation info is now implemented as a profile option (INDUSTRY).
638
639 -- Get Calling Application ID / Responsibility ID / User ID
640
641 lp_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
642 lp_user_resp_id := FND_GLOBAL.RESP_ID;
643 lp_user_id := FND_GLOBAL.USER_ID;
644
645 FND_PROFILE.GET_SPECIFIC('INDUSTRY',
646 lp_user_id,
647 lp_user_resp_id,
648 lp_resp_appl_id,
649 lc_int_industry,
650 l_defined);
651
652 IF not l_defined then
653 IF fnd_installation.get_app_info( application_short_name=>'SQLGL'
654 ,status=>lc_int_status
655 ,industry=>lc_int_industry
656 ,oracle_schema=>lc_int_schema) THEN
657
658 IF lc_int_industry <> 'G' THEN
659 lc_err_message := 'Oracle Government Ledger is not Installed';
660 RAISE lexp_error;
661 END IF;
662
663 END IF;
664 ELSE
665 IF lc_int_industry <> 'G' THEN
666 lc_err_message := 'Oracle Government Ledger is not Installed';
667 RAISE lexp_error;
668 END IF;
669
670 END IF;
671
672 END;
673 ------------------------------------
674 --Assign parameter to the variables
675 --Check the values of the parameters
676 ------------------------------------
677 BEGIN
678 ln_p_coa_id := p_chart_of_accounts_id;
679 lc_p_segment_name := p_segment_name;
680 lc_p_denorm_seg :=p_denormalized_segment;
681 --
682 IF (lc_p_segment_name IS NULL) AND (lc_p_denorm_seg IS NULL) THEN
683 ln_params := 2;
684 ELSIF (lc_p_segment_name IS NOT NULL) AND (lc_p_denorm_seg IS NOT NULL) THEN
685 ln_params := 4;
686 ELSE
687 lc_err_message:='Incorrect number of Parameters';
688 RAISE lexp_error;
689 END IF;
690 ----------------------------
691 --Start the cursor statement
692 ----------------------------
693
694 IF ln_params = 2 THEN
695 lc_select_stmt:=
696 'SELECT nvl(attr.flex_value_set_id,0),
697 attr.attribute_num,
698 attr.table_id,
699 attr.application_column_name,
700 attr.segment_name,
701 attr.segment_num,
702 valset.validation_type,
703 attr.attr_segment_name
704 FROM fnd_seg_rpt_attributes attr,
705 fnd_flex_value_sets valset
706 WHERE valset.flex_value_set_id = attr.flex_value_set_id
707 AND attr.id_flex_num = :ln_p_coa_id ';
708 ELSE
709 lc_select_stmt:=
710 'SELECT nvl(attr.flex_value_set_id,0),
711 attr.attribute_num,
712 attr.table_id,
713 attr.application_column_name,
714 attr.segment_name,
715 attr.segment_num,
716 valset.validation_type
717 FROM fnd_seg_rpt_attributes attr,
718 fnd_flex_value_sets valset
719 WHERE valset.flex_value_set_id = attr.flex_value_set_id
720 AND attr.id_flex_num = :ln_p_coa_id '||
721 'AND attr.segment_name = :lc_p_segment_name '||
722 'AND attr.attr_segment_name = :lc_p_denorm_seg ';
723 END IF;
724 END;
725 --
726 BEGIN
727 --------------------------------
728 --Open the cursor for attributes
729 --------------------------------
730 li_cursor_id :=DBMS_SQL.OPEN_CURSOR;
731 DBMS_SQL.PARSE(li_cursor_id,lc_select_stmt,DBMS_SQL.v7);
732 dbms_sql.bind_variable(li_cursor_id, ':ln_p_coa_id',ln_p_coa_id);
733 IF ln_params <> 2 THEN
734 dbms_sql.bind_variable(li_cursor_id, ':lc_p_segment_name',lc_p_segment_name);
735 dbms_sql.bind_variable(li_cursor_id, ':lc_p_denorm_seg',lc_p_denorm_seg);
736 END IF;
737
738 -----------------------------------------
739 --Assign variables for the cursor columns
740 -----------------------------------------
741 IF ln_params = 2 THEN
742 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,1,ln_flex_value_set_id);
743 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,2,lc_attribute_num,30);
744 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,3,ln_table_id);
745 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,4,lc_application_column_name,30);
746 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,5,lc_segment_name,30);
747 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,6,ln_segment_num);
748 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,7,lc_validation_type,1);
749 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,8,lc_attr_segment_name,30);
750 ELSE
751 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,1,ln_flex_value_set_id);
752 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,2,lc_attribute_num,30);
753 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,3,ln_table_id);
754 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,4,lc_application_column_name,30);
755 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,5,lc_segment_name,30);
756 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,6,ln_segment_num);
757 DBMS_SQL.DEFINE_COLUMN(li_cursor_id,7,lc_validation_type,1);
758 END IF;
759 -----------------------------------
760 --Execute the cursor for attributes
761 -----------------------------------
762 li_dummy:=DBMS_SQL.EXECUTE(li_cursor_id);
763 --
764 -----------------------------------------
765 --Start building the the update statement
766 -----------------------------------------
767 lc_sql_stmt:='UPDATE gl_code_combinations glcc SET ';
768
769 LOOP
770 -------------------------------
771 --Fetch each row for attributes
772 -------------------------------
773 IF DBMS_SQL.FETCH_ROWS(li_cursor_id) = 0 THEN
774 EXIT;
775 END IF;
776 IF ln_params=2 THEN
777 DBMS_SQL.COLUMN_VALUE(li_cursor_id,1,ln_flex_value_set_id);
778 DBMS_SQL.COLUMN_VALUE(li_cursor_id,2,lc_attribute_num);
779 DBMS_SQL.COLUMN_VALUE(li_cursor_id,3,ln_table_id);
780 DBMS_SQL.COLUMN_VALUE(li_cursor_id,4,lc_application_column_name);
781 DBMS_SQL.COLUMN_VALUE(li_cursor_id,5,lc_segment_name);
782 DBMS_SQL.COLUMN_VALUE(li_cursor_id,6,ln_segment_num);
783 DBMS_SQL.COLUMN_VALUE(li_cursor_id,7,lc_validation_type);
784 DBMS_SQL.COLUMN_VALUE(li_cursor_id,8,lc_attr_segment_name);
785 ELSE
786 DBMS_SQL.COLUMN_VALUE(li_cursor_id,1,ln_flex_value_set_id);
787 DBMS_SQL.COLUMN_VALUE(li_cursor_id,2,lc_attribute_num);
788 DBMS_SQL.COLUMN_VALUE(li_cursor_id,3,ln_table_id);
789 DBMS_SQL.COLUMN_VALUE(li_cursor_id,4,lc_application_column_name);
790 DBMS_SQL.COLUMN_VALUE(li_cursor_id,5,lc_segment_name);
791 DBMS_SQL.COLUMN_VALUE(li_cursor_id,6,ln_segment_num);
792 DBMS_SQL.COLUMN_VALUE(li_cursor_id,7,lc_validation_type);
793 END IF;
794
795 ----------------------------------
796 --If the validation is independent
797 ----------------------------------
798 IF (lc_validation_type = 'I' OR lc_validation_type='D') THEN
799 IF ln_params=2 THEN
800 lc_sql_stmt:=lc_sql_stmt||lc_attr_segment_name;
801 ELSE
802 lc_sql_stmt:=lc_sql_stmt||lc_p_denorm_seg;
803 END IF;
804 --
805 lc_sql_stmt :=lc_sql_stmt||'=(SELECT '|| lc_attribute_num||
806 ' FROM fnd_flex_values ffval'||
807 ' WHERE flex_value_set_id = ' ||ln_flex_value_set_id||
808 ' AND ffval.flex_value = glcc.'||lc_segment_name;
809 --
810 IF lc_validation_type = 'D' THEN
811 SELECT application_column_name
812 INTO lc_parent_seg_name
813 FROM fnd_id_flex_segments
814 WHERE id_flex_code = 'GLAT'
815 AND flex_value_set_id =
816 (SELECT parent_flex_value_set_id
817 FROM fnd_flex_value_sets
818 WHERE flex_value_set_id = ln_flex_value_set_id);
819 --
820 IF lc_parent_seg_name IS NULL THEN
821 lc_err_message:='The Parent Seg name is null for the Dependent Value Set';
822 RAISE lexp_error;
823 END IF;
824 --
825 lc_sql_stmt :=lc_sql_stmt||' AND ffval.parent_flex_value_low = glcc.'||lc_parent_seg_name;
826 END IF;
827 END IF;
828 -------------------------------------
829 --If the validation is based on table
830 -------------------------------------
831 IF lc_validation_type = 'F' THEN
832 SELECT user_table_name
833 INTO lc_val_table_name
834 FROM fnd_tables
835 WHERE application_id = 101
836 AND table_id = ln_table_id;
837 --
838 SELECT value_column_name
839 INTO lc_seg_column_val_name
840 FROM fnd_flex_validation_tables
841 WHERE flex_value_set_id = ln_flex_value_set_id;
842 --
843 IF ln_params = 2 THEN
844 lc_sql_stmt:=lc_sql_stmt||lc_attr_segment_name;
845 ELSE
846 lc_sql_stmt:=lc_sql_stmt||lc_p_denorm_seg;
847 END IF;
848 lc_sql_stmt :=lc_sql_stmt||' = (SELECT '|| lc_attr_seg_name||
849 ' FROM '|| lc_val_table_name||
850 ' VAL WHERE VAL.'|| lc_seg_column_val_name||
851 '= glcc.'|| lc_segment_name;
852 END IF;
853 lc_sql_stmt:=lc_sql_stmt||'),';
854
855 END LOOP;
856
857 -----------------------------------------
858 --Append who columns for update statement
859 -----------------------------------------
860 lc_sql_stmt := lc_sql_stmt|| 'LAST_UPDATE_DATE = sysdate,'||
861 'LAST_UPDATED_BY = '||ln_last_updated_by||
862 ' WHERE CHART_OF_ACCOUNTS_ID = :ln_p_coa_id ';
863
864 ----------------------------------------------
865 --Open, Parse and Execute the Update statement
866 ----------------------------------------------
867 li_cursor_id2:=DBMS_SQL.OPEN_CURSOR;
868 --
869 DBMS_SQL.PARSE(li_cursor_id2,lc_sql_stmt,DBMS_SQL.v7);
870 dbms_sql.bind_variable(li_cursor_id2, ':ln_p_coa_id',ln_p_coa_id);
871 --
872 li_dummy1:=DBMS_SQL.EXECUTE(li_cursor_id2);
873 --
874 DBMS_SQL.CLOSE_CURSOR(li_cursor_id);
875 --
876
877 COMMIT;
878
879 --
880 fnd_file.put_line(FND_FILE.LOG,'Historical Program successfully completed');
881 fnd_file.put_line(FND_FILE.LOG,'User ID :'||ln_userid);
882 fnd_file.put_line(FND_FILE.LOG,'Date :'||sysdate);
883 fnd_file.put_line(FND_FILE.LOG,'COA ID :'||ln_p_coa_id);
884 END;
885 EXCEPTION
886 WHEN lexp_error THEN
887 fnd_file.put_line(FND_FILE.LOG,'Program Completed With Error ');
888 fnd_file.put_line(FND_FILE.LOG,'Error : '||lc_err_message);
889 retcode := -1;
890 errbuf := null;
891 WHEN others THEN
892 fnd_file.put_line(FND_FILE.LOG,'Error : '||substr(SQLERRM,1,70));
893 retcode := -1;
894 errbuf := null;
895 END gl_history;
896
897 END GL_REP_ATTRIBUTES;