[Home] [Help]
PACKAGE BODY: APPS.GL_GLCOAM_PKG
Source
1 PACKAGE BODY GL_GLCOAM_PKG as
2 /* $Header: glmrcoab.pls 120.8 2011/06/02 05:24:29 skotakar ship $ */
3
4 PROCEDURE run_prog(X_id_flex_num NUMBER,
5 X_mode VARCHAR2) IS
6 x_userid VARCHAR2(8);
7 x_loginid VARCHAR2(10);
8 x_user_id NUMBER; --Variable to hold the USER ID
9 x_login_id NUMBER; --Variable to hold hte LOGIN ID
10 request_id NUMBER; --Variable to get the REQUEST ID
11 x_num NUMBER; --Dummy variable to decide whether there is
12 --already an existing structure with the same
13 --ID
14 msg VARCHAR2(2000);--The variable to get the message
15 failed_request EXCEPTION; --Exception to handle the request submission
16 --failure.
17 x_id_flex_strt_code VARCHAR2(30);--Variable to get the structure code.
18
19 CURSOR insert_update(flex_num NUMBER) IS
20 SELECT id_flex_num
21 FROM FND_ID_FLEX_STRUCTURES
22 WHERE application_id = 101
23 AND id_flex_code = 'GLLE'
24 AND id_flex_num = flex_num;
25
26 CURSOR segments_exist(flex_num NUMBER) IS
27 SELECT id_flex_num
28 FROM FND_ID_FLEX_SEGMENTS
29 WHERE application_id = 101
30 AND id_flex_code = 'GL#'
31 AND id_flex_num = flex_num;
32
33
34 BEGIN
35
36 IF(X_mode = 'N') THEN
37 -- Bug8459241 : changed FND_PROFILE to FND_GLOBAL
38 -- FND_PROFILE.get('USER_ID',x_userid);
39 -- FND_PROFILE.get('CONC_LOGIN_ID',x_loginid);
40 x_user_id := FND_GLOBAL.user_id;
41 x_login_id := FND_GLOBAL.conc_login_id;
42 ELSE
43 x_user_id := 1;
44 x_login_id := 0;
45 END IF;
46
47 --If there is already an existing structure with the same ID, delete it.
48 OPEN insert_update(X_id_flex_num);
49 FETCH insert_update INTO x_num;
50 IF(insert_update%FOUND)THEN
51 DELETE FROM FND_ID_FLEX_STRUCTURES
52 WHERE application_id = 101
53 AND id_flex_code = 'GLLE'
54 AND id_flex_num = X_id_flex_num;
55
56 DELETE FROM FND_ID_FLEX_STRUCTURES_TL
57 WHERE application_id = 101
58 AND id_flex_code = 'GLLE'
59 AND id_flex_num = X_id_flex_num;
60
61 DELETE FROM FND_ID_FLEX_SEGMENTS
62 WHERE application_id = 101
63 AND id_flex_code = 'GLLE'
64 AND id_flex_num = X_id_flex_num;
65
66 DELETE FROM FND_ID_FLEX_SEGMENTS_TL
67 WHERE application_id = 101
68 AND id_flex_code = 'GLLE'
69 AND id_flex_num = X_id_flex_num;
70
71 DELETE FROM FND_SEGMENT_ATTRIBUTE_VALUES
72 WHERE application_id = 101
73 AND id_flex_code = 'GLLE'
74 AND id_flex_num = X_id_flex_num;
75
76 DELETE FROM FND_COMPILED_ID_FLEX_STRUCTS
77 WHERE application_id = 101
78 AND id_flex_code = 'GLLE'
79 AND id_flex_num = X_id_flex_num;
80
81 IF(X_mode = 'N') THEN
82 fnd_message.set_name('SQLGL','GL_COA_MIRROR_DELETE');
83 msg := fnd_message.get;
84 fnd_file.put_line(FND_FILE.LOG,msg);
85 END IF;
86 END IF;
87 CLOSE insert_update;
88
89 --Insert the new chart of accounts based on the Ledger Flexfield.
90 INSERT INTO FND_ID_FLEX_STRUCTURES
91 (application_id,
92 id_flex_code,
93 id_flex_num,
94 last_update_date,
95 last_updated_by,
96 creation_date,
97 created_by,
98 last_update_login,
99 concatenated_segment_delimiter,
100 cross_segment_validation_flag,
101 dynamic_inserts_allowed_flag,
102 enabled_flag,
103 freeze_flex_definition_flag,
104 freeze_structured_hier_flag,
105 shorthand_enabled_flag,
106 shorthand_length,
107 structure_view_name,
108 id_flex_structure_code)
109 -- security_group_id)
110 SELECT
111 101,
112 'GLLE',
113 X_id_flex_num,
114 sysdate,
115 x_user_id,
116 sysdate,
117 x_user_id,
118 x_login_id,
119 concatenated_segment_delimiter,
120 cross_segment_validation_flag,
121 'N',
122 enabled_flag,
123 'Y',
124 freeze_structured_hier_flag,
125 shorthand_enabled_flag,
126 shorthand_length,
127 structure_view_name,
128 id_flex_structure_code--,
129 -- security_group_id
130 FROM FND_ID_FLEX_STRUCTURES
131 WHERE application_id = 101
132 AND id_flex_code = 'GL#'
133 AND id_flex_num = X_id_flex_num;
134
135 --Insert the chart of accounts in multiple languages supported by the
136 --application.
137 INSERT INTO FND_ID_FLEX_STRUCTURES_TL
138 (application_id,
139 id_flex_code,
140 id_flex_num,
141 language,
142 last_update_date,
143 last_updated_by,
144 creation_date,
145 created_by,
146 last_update_login,
147 id_flex_structure_name,
148 description,
149 shorthand_prompt,
150 source_lang)
151 -- security_group_id)
152 SELECT
153 101,
154 'GLLE',
155 X_id_flex_num,
156 language,
157 sysdate,
158 x_user_id,
159 sysdate,
160 x_user_id,
161 x_login_id,
162 id_flex_structure_name,
163 description,
164 shorthand_prompt,
165 source_lang
166 -- security_group_id
167 FROM FND_ID_FLEX_STRUCTURES_TL
168 WHERE application_id = 101
169 AND id_flex_code = 'GL#'
170 AND id_flex_num = X_id_flex_num;
171
172 -- Insert segments only if GL# segments exist
173 OPEN segments_exist(X_id_flex_num);
174 FETCH segments_exist INTO x_num;
175 IF(segments_exist%FOUND)THEN
176
177 --Insert the ledger segment as the first segment for the new structure.
178 INSERT INTO FND_ID_FLEX_SEGMENTS
179 (application_id,
180 id_flex_code,
181 id_flex_num,
182 application_column_name,
183 segment_name,
184 last_update_date,
185 last_updated_by,
186 creation_date,
187 created_by,
188 last_update_login,
189 segment_num,
190 application_column_index_flag,
191 enabled_flag,
192 required_flag,
193 display_flag,
194 display_size,
195 security_enabled_flag,
196 maximum_description_len,
197 concatenation_description_len,
198 flex_value_set_id,
199 range_code,
200 default_type,
201 default_value,
202 runtime_property_function)
203 -- security_group_id)
204 SELECT
205 101,
206 'GLLE',
207 X_id_flex_num,
208 'LEDGER_SEGMENT',
209 lv.meaning,
210 sysdate,
211 x_user_id,
212 sysdate,
213 x_user_id,
214 x_login_id,
215 1,
216 'Y',
217 'Y',
218 'Y',
219 'Y',
220 20,
221 'N',
222 50,
223 25,
224 fv.flex_value_set_id,
225 null,
226 'S',
227 'SELECT short_name FROM gl_ledgers WHERE ledger_id = gl_formsinfo.get_default_ledger(:$PROFILES$.access_set_id,''R'',NULL)',
228 null
229 -- security_group_id
230 FROM FND_FLEX_VALUE_SETS fv,
231 FND_LANGUAGES l,
232 FND_LOOKUP_VALUES lv
233 WHERE fv.flex_value_set_name = 'GL_COA_MIRROR_LEDGER'
234 AND l.installed_flag = 'B'
235 AND lv.language = l.language_code
236 AND lv.lookup_type = 'LEDGERS'
237 AND lv.lookup_code = 'L'
238 AND lv.view_application_id = 101;
239
240 --Copy the segments of the same structure based on the Accounting Flexfield.
241 INSERT INTO FND_ID_FLEX_SEGMENTS
242 (application_id,
243 id_flex_code,
244 id_flex_num,
245 application_column_name,
246 segment_name,
247 last_update_date,
248 last_updated_by,
249 creation_date,
250 created_by,
251 last_update_login,
252 segment_num,
253 application_column_index_flag,
254 enabled_flag,
255 required_flag,
256 display_flag,
257 display_size,
258 security_enabled_flag,
259 maximum_description_len,
260 concatenation_description_len,
261 flex_value_set_id,
262 range_code,
263 default_type,
264 default_value,
265 runtime_property_function)
266 -- security_group_id)
267 SELECT
268 101,
269 'GLLE',
270 X_id_flex_num,
271 application_column_name,
272 segment_name,
273 sysdate,
274 x_user_id,
275 sysdate,
276 x_user_id,
277 x_login_id,
278 segment_num+1,
279 application_column_index_flag,
280 enabled_flag,
281 required_flag,
282 display_flag,
283 display_size,
284 security_enabled_flag,
285 maximum_description_len,
286 concatenation_description_len,
287 flex_value_set_id,
288 range_code,
289 default_type,
290 default_value,
291 runtime_property_function
292 -- security_group_id
293 FROM FND_ID_FLEX_SEGMENTS
294 WHERE application_id = 101
295 AND id_flex_code = 'GL#'
296 AND id_flex_num = X_id_flex_num;
297
298 --Insert the ledger segment in multiple languages supported by the
299 --application.
300 INSERT INTO FND_ID_FLEX_SEGMENTS_TL
301 (application_id,
302 id_flex_code,
303 id_flex_num,
304 application_column_name,
305 language,
306 last_update_date,
307 last_updated_by,
308 creation_date,
309 created_by,
310 last_update_login,
311 form_left_prompt,
312 form_above_prompt,
313 description,
314 source_lang)
315 -- security_group_id)
316 SELECT
317 101,
318 'GLLE',
319 X_id_flex_num,
320 'LEDGER_SEGMENT',
321 l.language_code,
322 sysdate,
323 x_user_id,
324 sysdate,
325 x_user_id,
326 x_login_id,
327 lv.meaning,
328 lv.meaning,
329 lv.description,
330 userenv('LANG')
331 -- security_group_id
332 FROM FND_LOOKUP_VALUES lv,
333 FND_LANGUAGES l
334 WHERE l.installed_flag in ('B','I')
335 AND NOT EXISTS
336 (SELECT NULL
337 FROM FND_ID_FLEX_SEGMENTS_TL t
338 WHERE t.application_id = 101
339 AND t.id_flex_code = 'GLLE'
340 AND t.id_flex_num = X_id_flex_num
341 AND t.application_column_name = 'LEDGER_SEGMENT'
342 AND t.language = l.language_code)
343 AND lv.lookup_type = 'LEDGERS'
344 AND lv.lookup_code = 'L'
345 AND lv.language = l.language_code
346 AND lv.view_application_id = 101;
347
348 --Insert the remaining segments in multiple languages supported by the
349 --application.
350 INSERT INTO FND_ID_FLEX_SEGMENTS_TL
351 (application_id,
352 id_flex_code,
353 id_flex_num,
354 application_column_name,
355 language,
356 last_update_date,
357 last_updated_by,
358 creation_date,
359 created_by,
360 last_update_login,
361 form_left_prompt,
362 form_above_prompt,
363 description,
364 source_lang)
365 -- security_group_id)
366 SELECT
367 101,
368 'GLLE',
369 X_id_flex_num,
370 application_column_name,
371 language,
372 sysdate,
373 x_user_id,
374 sysdate,
375 x_user_id,
376 x_login_id,
377 form_left_prompt,
378 form_above_prompt,
379 description,
380 source_lang
381 -- security_group_id
382 FROM FND_ID_FLEX_SEGMENTS_TL
383 WHERE application_id = 101
384 AND id_flex_code = 'GL#'
385 AND id_flex_num = X_id_flex_num;
386
387 --Insert the GL_LEDGER qualifier for all of the segments.
388 INSERT INTO FND_SEGMENT_ATTRIBUTE_VALUES
389 (application_id,
390 id_flex_code,
391 id_flex_num,
392 application_column_name,
393 segment_attribute_type,
394 last_update_date,
395 last_updated_by,
396 creation_date,
397 created_by,
398 last_update_login,
399 attribute_value)
400 -- security_group_id)
401 SELECT
402 101,
403 'GLLE',
404 X_id_flex_num,
405 application_column_name,
406 'GL_LEDGER',
407 sysdate,
408 x_user_id,
409 sysdate,
410 x_user_id,
411 x_login_id,
412 decode(application_column_name,'LEDGER_SEGMENT','Y','N')
413 -- security_group_id
414 FROM FND_ID_FLEX_SEGMENTS
415 WHERE application_id =101
416 AND id_flex_code = 'GLLE'
417 AND id_flex_num = X_id_flex_num;
418
419 --Insert the qualifiers for the ledger segment.
420 INSERT INTO FND_SEGMENT_ATTRIBUTE_VALUES
421 (application_id,
422 id_flex_code,
423 id_flex_num,
424 application_column_name,
425 segment_attribute_type,
426 last_update_date,
427 last_updated_by,
428 creation_date,
429 created_by,
430 last_update_login,
431 attribute_value)
432 -- security_group_id)
433 SELECT
434 101,
435 'GLLE',
436 X_id_flex_num,
437 'LEDGER_SEGMENT',
438 val.segment_attribute_type,
439 sysdate,
440 x_user_id,
441 sysdate,
442 x_user_id,
443 x_login_id,
444 decode(val.segment_attribute_type,'GL_GLOBAL','Y','N')
445 -- security_group_id
446 FROM FND_SEGMENT_ATTRIBUTE_VALUES val,
447 FND_SEGMENT_ATTRIBUTE_TYPES typ
448 WHERE val.application_id=101
449 AND val.id_flex_code = 'GL#'
450 AND val.id_flex_num = X_id_flex_num
451 AND val.application_column_name =
452 (SELECT application_column_name
453 FROM FND_ID_FLEX_SEGMENTS
454 WHERE application_id = 101
455 AND id_flex_code = 'GL#'
456 AND id_flex_num = X_id_flex_num
457 AND rownum=1)
458 AND typ.application_id = 101
459 AND typ.id_flex_code = 'GLLE'
460 AND typ.segment_attribute_type = val.segment_attribute_type;
461
462 --Insert the qualifiers for the remaining segments.
463 INSERT INTO FND_SEGMENT_ATTRIBUTE_VALUES
464 (application_id,
465 id_flex_code,
466 id_flex_num,
467 application_column_name,
468 segment_attribute_type,
469 last_update_date,
470 last_updated_by,
471 creation_date,
472 created_by,
473 last_update_login,
474 attribute_value)
475 -- security_group_id)
476 SELECT
477 101,
478 'GLLE',
479 X_id_flex_num,
480 val.application_column_name,
481 val.segment_attribute_type,
482 sysdate,
483 x_user_id,
484 sysdate,
485 x_user_id,
486 x_login_id,
487 val.attribute_value
488 -- security_group_id
489 FROM FND_SEGMENT_ATTRIBUTE_VALUES val,
490 FND_SEGMENT_ATTRIBUTE_TYPES typ
491 WHERE val.application_id = 101
492 AND val.id_flex_code = 'GL#'
493 AND val.id_flex_num = X_id_flex_num
494 AND typ.application_id = 101
495 AND typ.id_flex_code = 'GLLE'
496 AND typ.segment_attribute_type = val.segment_attribute_type;
497
498 END IF;
499 CLOSE segments_exist;
500
501 SELECT id_flex_structure_code
502 INTO x_id_flex_strt_code
503 FROM FND_ID_FLEX_STRUCTURES
504 WHERE application_id = 101
505 AND id_flex_code = 'GLLE'
506 AND id_flex_num = X_id_flex_num;
507
508 IF(X_mode = 'N') THEN
509 request_id := fnd_request.submit_request(
510 'FND', 'FDFCMPK', '', '', FALSE,
511 'K', 'SQLGL',
512 'GLLE', X_id_flex_num);
513
514 IF (request_id = 0) THEN
515 raise failed_request;
516 ELSE
517 fnd_file.put_line(FND_FILE.LOG, 'Request ID is: '||request_id);
518 END IF;
519 END IF;
520
521 EXCEPTION
522 WHEN failed_request THEN
523 rollback;
524 fnd_message.set_name('SQLGL','GL_API_COA_FLEX_COMPILE_ERR');
525 fnd_message.set_token('STRUCTURECODE',x_id_flex_strt_code);
526 msg := fnd_message.get;
527 fnd_file.put_line(FND_FILE.LOG,msg);
528 WHEN OTHERS THEN
529 rollback;
530 IF(X_mode = 'N') THEN
531 msg := SUBSTRB(SQLERRM,1,2000);
532 fnd_file.put_line(FND_FILE.LOG,msg);
533 END IF;
534
535 END run_prog;
536
537
538 FUNCTION gl_coam_rule(p_subscription_guid IN RAW,
539 p_event IN OUT NOCOPY WF_EVENT_T)
540 RETURN VARCHAR2 IS
541 src_req_id VARCHAR2(15);
542 application_id VARCHAR2(15);
543 id_flex_code VARCHAR2(10);
544 id_flex_num VARCHAR2(15);
545 request_id NUMBER;
546 BEGIN
547 FND_PROFILE.get('CONC_REQUEST_ID', src_req_id);
548
549 -- only necessary when the event is raised directly from the form
550 IF (to_number(src_req_id) <= 0) THEN
551
552 application_id := p_event.GetValueForParameter('APPLICATION_ID');
553 id_flex_code := p_event.GetValueForParameter('ID_FLEX_CODE');
554 id_flex_num := p_event.GetValueForParameter('ID_FLEX_NUM');
555
556 IF (application_id = '101' AND id_flex_code = 'GL#') THEN
557 request_id := fnd_request.submit_request(
558 'SQLGL', 'GLCOAM', '', '', FALSE,
559 id_flex_num, chr(0), '', '', '', '', '', '', '', '',
560 '', '', '', '', '', '', '', '', '', '',
561 '', '', '', '', '', '', '', '', '', '',
562 '', '', '', '', '', '', '', '', '', '',
563 '', '', '', '', '', '', '', '', '', '',
564 '', '', '', '', '', '', '', '', '', '',
565 '', '', '', '', '', '', '', '', '', '',
566 '', '', '', '', '', '', '', '', '', '',
567 '', '', '', '', '', '', '', '', '', '',
568 '', '', '', '', '', '', '', '', '', '');
569 IF (request_id = 0) THEN
570 WF_CORE.CONTEXT('GL_GLCOAM_PKG', 'gl_coam_rule',
571 p_event.getEventName, p_subscription_guid);
572 WF_EVENT.setErrorInfo(p_event, FND_MESSAGE.get);
573 return 'WARNING';
574 END IF;
575 END IF;
576
577 END IF;
578
579 RETURN 'SUCCESS';
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 WF_CORE.CONTEXT('GL_GLCOAM_PKG', 'gl_coam_rule',
584 p_event.getEventName, p_subscription_guid);
585 WF_EVENT.setErrorInfo(p_event, 'ERROR');
586 return 'ERROR';
587 END gl_coam_rule;
588
589 END GL_GLCOAM_PKG;