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