[Home] [Help]
PACKAGE BODY: APPS.JA_CN_SYSOPS_BANK_PKG
Source
1 package body JA_CN_SYSOPS_BANK_PKG as
2 --$Header: JACNSBPB.pls 120.0.12020000.4 2013/02/06 09:03:15 chongwan noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNSBPB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to get balance segment |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Get_BSV_LIST |
16 --| PROCEDURE Get_SECURITY_BSV |
17 --| PROCEDURE Check_LE_BSV |
18 --| PROCEDURE Submit_XML_Charset_Conversion |
19 --| |
20 --| HISTORY |
21 --| Oct-31-2012 Jar Wang Created |
22 --+======================================================================*/
23 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_SYSOPS_BANK_PKG';
24
25 --==========================================================================
26 -- PROCEDURE NAME:
27 -- Get_BSV_LIST public
28 --
29 -- DESCRIPTION:
30 -- This procedure returns a list(with comma) of all balancing segment
31 -- value have right to access.
32 --
33 -- PARAMETERS:
34 -- In: p_resp_app_id NUMBER
35 -- In: p_resp_id NUMBER
36 -- In: p_access_set_id NUMBER
37 -- Out: p_bsv_list Varchar2
38 -- DESIGN REFERENCES:
39 -- None
40 --
41 -- CHANGE HISTORY:
42 -- Nov-1-2012 Jar Wang Created
43 --===========================================================================
44 PROCEDURE Get_BSV_LIST(p_resp_app_id in pls_integer,
45 p_resp_id in pls_integer,
46 p_access_set_id in pls_integer,
47 p_bsv_list out nocopy varchar2) IS
48
49 CURSOR CUR_LE(c_ledger_id pls_integer) IS
50 select xep.legal_entity_id legal_entity_id,
51 xep.name legal_entity_name,
52 lg.ledger_id,
53 rs.primary_ledger_id
54 from gl_ledger_config_details primdet,
55 gl_ledgers lg,
56 gl_ledger_relationships rs,
57 gl_ledger_configurations cfg,
58 gl_ledger_config_details cfgdet,
59 XLE_ENTITY_PROFILES xep
60 where rs.application_id = 101
61 and ((rs.target_ledger_category_code = 'SECONDARY' and
62 rs.relationship_type_code <> 'NONE') or
63 (rs.target_ledger_category_code = 'PRIMARY' and
64 rs.relationship_type_code = 'NONE') or
65 (rs.target_ledger_category_code = 'ALC' and
66 rs.relationship_type_code in ('JOURNAL', 'SUBLEDGER')))
67 and lg.ledger_id = rs.target_ledger_id
68 and lg.ledger_category_code = rs.target_ledger_category_code
69 and nvl(lg.complete_flag, 'Y') = 'Y'
70 and primdet.object_id = rs.primary_ledger_id
71 and primdet.object_type_code = 'PRIMARY'
72 and primdet.setup_step_code = 'NONE'
73 and cfg.configuration_id = primdet.configuration_id
74 and cfgdet.configuration_id(+) = cfg.configuration_id
75 and cfgdet.object_type_code(+) = 'LEGAL_ENTITY'
76 and xep.legal_entity_id = cfgdet.object_id
77 and ledger_id =c_ledger_id;
78
79 CURSOR CUR_BSV(c_legal_id pls_integer) IS
80 SELECT gleb.flex_segment_value
81 FROM GL_LEGAL_ENTITIES_BSVS gleb
82 WHERE gleb.legal_entity_id = c_legal_id;
83
84 CURSOR CUR_NON_BSV(c_flex_value_set_id pls_integer) IS
85 SELECT ffv.FLEX_VALUE
86 FROM FND_FLEX_VALUES_VL ffv
87 WHERE ffv.FLEX_VALUE_SET_ID=c_flex_value_set_id;
88
89
90 CURSOR CUR_LEDGER IS
91 SELECT ledger_id
92 FROM GL_ACCESS_SET_LEDGERS gas
93 where gas.access_set_id = p_access_set_id;
94
95 lv_procedure_name VARCHAR2(40) := 'Get_BSV_LIST';
96 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
97 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
98 l_bsv CUR_BSV%rowtype;
99 l_non_bsv CUR_NON_BSV%rowtype;
100 l_flex_valueset_id pls_integer;
101 l_security_status varchar2(30);
102 l_error_message varchar2(1000);
103 x_bsv_list varchar2(4000);
104 x_where varchar2(4000);
105 l_bsv_count pls_integer;
106 BEGIN
107 IF (ln_proc_level >= ln_dbg_level) THEN
108 FND_LOG.STRING(ln_proc_level,
109 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
110 '.begin',
111 'Enter procedure');
112
113 END IF; --ln_proc_level>=ln_dbg_level
114 FOR l_ledger in CUR_LEDGER LOOP
115
116 SELECT fifsv.FLEX_VALUE_SET_ID
117 into l_flex_valueset_id
118 FROM FND_ID_FLEX_SEGMENTS_VL fifsv,
119 FND_SEGMENT_ATTRIBUTE_VALUES FSAV
120 WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
121 AND FSAV.APPLICATION_ID = 101
122 AND FSAV.ID_FLEX_CODE = 'GL#'
123 AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
124 AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
125 AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
126 AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
127 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
128 AND fifsv.ID_FLEX_NUM in
129 (SELECT chart_of_accounts_id
130 FROM gl_ledgers gls
131 where gls.ledger_id = l_ledger.ledger_id);
132
133 FOR l_legal in CUR_LE(l_ledger.ledger_id) LOOP
134 select count(1) into l_bsv_count
135 from GL_LEGAL_ENTITIES_BSVS gle
136 where gle.legal_entity_id = l_legal.legal_entity_id;
137
138 IF l_bsv_count>0 THEN
139 OPEN CUR_BSV(l_legal.legal_entity_id);
140 LOOP
141 FETCH CUR_BSV INTO l_bsv;
142 EXIT WHEN CUR_BSV%NOTFOUND;
143 fnd_flex_server.check_value_security(p_security_check_mode => 'YH',
144 p_flex_value_set_id => l_flex_valueset_id,
145 p_parent_flex_value => null,
146 p_flex_value => l_bsv.flex_segment_value, --BSV
147 p_resp_application_id => p_resp_app_id,
148 p_responsibility_id => p_resp_id,
149 x_security_status => l_security_status,
150 x_error_message => l_error_message);
151 IF (l_security_status = 'NOT-SECURED') THEN
152 x_bsv_list:= x_bsv_list ||','''||l_bsv.flex_segment_value||'''';
153 END IF;
154 END LOOP;
155 CLOSE CUR_BSV;
156 ELSE
157
158 OPEN CUR_NON_BSV(l_flex_valueset_id);
159 LOOP
160 FETCH CUR_NON_BSV INTO l_non_bsv;
161 EXIT WHEN CUR_NON_BSV%NOTFOUND;
162
163 fnd_flex_server.check_value_security(p_security_check_mode => 'YH',
164 p_flex_value_set_id => l_flex_valueset_id,
165 p_parent_flex_value => null,
166 p_flex_value => l_non_bsv.FLEX_VALUE, --BSV
167 p_resp_application_id => p_resp_app_id,
168 p_responsibility_id => p_resp_id,
169 x_security_status => l_security_status,
170 x_error_message => l_error_message);
171
172 IF (l_security_status = 'NOT-SECURED') THEN
173 x_bsv_list:= x_bsv_list ||','''||l_non_bsv.FLEX_VALUE||'''';
174 END IF;
175
176 END LOOP;
177 CLOSE CUR_NON_BSV;
178 GOTO FULL_EXIT;
179 END IF;
180 END LOOP;
181 END LOOP;
182
183 <<FULL_EXIT>>
184 x_where := 'FLEX_SEGMENT_VALUE in ('|| substr(x_bsv_list,2,length(x_bsv_list)) ||')';
185 x_where := x_where ||' and ledger_id in (select ledger_id from gl_access_set_ledgers where access_set_id='||p_access_set_id||')';
186 --logging for debug
187 IF (ln_proc_level >= ln_dbg_level) THEN
188 FND_LOG.STRING(ln_proc_level,
189 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
190 'Exit procedure');
191 END IF; -- (ln_proc_level>=ln_dbg_level)
192 p_bsv_list := x_where;
193 Exception
194 WHEN OTHERS THEN
195 RAISE_APPLICATION_ERROR(-20000,GV_MODULE_PREFIX||'.'||lv_procedure_name||':'||SQLERRM);
196 END Get_BSV_LIST;
197
198
199 --==========================================================================
200 -- PROCEDURE NAME:
201 -- Get_SECURITY_BSV public
202 --
203 -- DESCRIPTION:
204 -- This procedure returns all balancing segment value have right to access.
205 --
206 -- PARAMETERS:
207 -- In: p_resp_app_id NUMBER
208 -- In: p_resp_id NUMBER
209 -- In: p_ledger_id NUMBER
210 -- In: p_legal_id NUMBER
211 -- DESIGN REFERENCES:
212 -- None
213 --
214 -- CHANGE HISTORY:
215 -- Nov-1-2012 Jar Wang Created
216 --===========================================================================
217 FUNCTION Get_SECURITY_BSV(p_resp_app_id pls_integer,
218 p_resp_id pls_integer,
219 p_ledger_id pls_integer,
220 p_legal_id pls_integer) RETURN bsv_type
221 pipelined IS
222 --assigned bsv
223 CURSOR CUR_BSV IS
224 SELECT ffv.FLEX_VALUE, ffv.DESCRIPTION, ffv.FLEX_VALUE_SET_ID
225 FROM FND_FLEX_VALUE_SETS ffs,
226 FND_FLEX_VALUES_VL ffv,
227 GL_LEGAL_ENTITIES_BSVS gleb
228 WHERE ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
229 AND ffs.flex_value_set_id = gleb.flex_value_set_id
230 AND ffv.FLEX_VALUE = gleb.flex_segment_value
231 AND trunc(nvl(START_DATE_ACTIVE, sysdate), 'DD') <=
232 trunc(sysdate, 'DD')
233 AND trunc(nvl(END_DATE_ACTIVE, sysdate), 'DD') >=
234 trunc(sysdate, 'DD')
235 AND trunc(nvl(start_date,sysdate),'DD')<=
236 trunc(sysdate, 'DD')
237 AND trunc(nvl(end_date,sysdate), 'DD') >=
238 trunc(sysdate, 'DD')
239 AND gleb.legal_entity_id = p_legal_id;
240 --non assigned bsv
241 CURSOR CUR_NON_BSV(c_flex_value_set_id pls_integer) IS
242 SELECT ffv.FLEX_VALUE, ffv.DESCRIPTION, ffv.FLEX_VALUE_SET_ID
243 FROM FND_FLEX_VALUE_SETS ffs, FND_FLEX_VALUES_VL ffv
244 WHERE ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
245 AND SUMMARY_FLAG = 'N'
246 AND ENABLED_FLAG = 'Y'
247 AND trunc(nvl(START_DATE_ACTIVE, sysdate), 'DD') <=
248 trunc(sysdate, 'DD')
249 AND trunc(nvl(END_DATE_ACTIVE, sysdate), 'DD') >=
250 trunc(sysdate, 'DD')
251 AND ffv.FLEX_VALUE_SET_ID = c_flex_value_set_id;
252
253 --Legal entity under the ledger
254 CURSOR CUR_LE IS
255 select cfgdet.object_id legal_entity_id
256 from gl_ledger_config_details primdet,
257 gl_ledgers lg,
258 gl_ledger_relationships rs,
259 gl_ledger_configurations cfg,
260 gl_ledger_config_details cfgdet
261 where rs.application_id = 101
262 and ((rs.target_ledger_category_code = 'SECONDARY' and
263 rs.relationship_type_code <> 'NONE') or
264 (rs.target_ledger_category_code = 'PRIMARY' and
265 rs.relationship_type_code = 'NONE') or
266 (rs.target_ledger_category_code = 'ALC' and
267 rs.relationship_type_code in ('JOURNAL', 'SUBLEDGER')))
268 and lg.ledger_id = rs.target_ledger_id
269 and lg.ledger_category_code = rs.target_ledger_category_code
270 and nvl(lg.complete_flag, 'Y') = 'Y'
271 and primdet.object_id = rs.primary_ledger_id
272 and primdet.object_type_code = 'PRIMARY'
273 and primdet.setup_step_code = 'NONE'
274 and cfg.configuration_id = primdet.configuration_id
275 and cfgdet.configuration_id(+) = cfg.configuration_id
276 and cfgdet.object_type_code(+) = 'LEGAL_ENTITY'
277 and ledger_id = p_ledger_id;
278
279 l_bsv_record bsv_record;
280 l_bsv_count pls_integer :=0;
281 l_bsv_count_t pls_integer :=0;
282 l_flex_valueset_id pls_integer;
283 l_security_status varchar2(30);
284 l_error_message varchar2(1000);
285 BEGIN
286
287 SELECT fifsv.FLEX_VALUE_SET_ID
288 into l_flex_valueset_id
289 FROM FND_ID_FLEX_SEGMENTS_VL fifsv, FND_SEGMENT_ATTRIBUTE_VALUES FSAV
290 WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
291 AND FSAV.APPLICATION_ID = 101
292 AND FSAV.ID_FLEX_CODE = 'GL#'
293 AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
294 AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
295 AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
296 AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
297 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
298 AND fifsv.ID_FLEX_NUM in
299 (SELECT chart_of_accounts_id
300 FROM gl_ledgers gls
301 where gls.ledger_id = p_ledger_id);
302
303 --Whether assigned bsv to all legal entity
304 FOR l_le in CUR_LE
305 LOOP
306 select count(1) into l_bsv_count_t
307 from GL_LEGAL_ENTITIES_BSVS gle
308 where gle.legal_entity_id = l_le.legal_entity_id
309 and (sysdate between nvl(start_date,sysdate) and nvl(end_date,sysdate) );
310 l_bsv_count :=l_bsv_count + l_bsv_count_t;
311 END LOOP;
312
313 --Non assigned bsv
314 IF l_bsv_count =0 THEN
315 FOR l_non_bsv in CUR_NON_BSV(l_flex_valueset_id) LOOP
316 fnd_flex_server.check_value_security(p_security_check_mode => 'YH',
317 p_flex_value_set_id => l_flex_valueset_id,
318 p_parent_flex_value => null,
319 p_flex_value => l_non_bsv.flex_value, --BSV
320 p_resp_application_id => p_resp_app_id,
321 p_responsibility_id => p_resp_id,
322 x_security_status => l_security_status,
323 x_error_message => l_error_message);
324 IF (l_security_status = 'NOT-SECURED') THEN
325 l_bsv_record.FLEX_VALUE := l_non_bsv.flex_value;
326 l_bsv_record.DESCRIPTION := l_non_bsv.DESCRIPTION;
327 l_bsv_record.FLEX_VALUE_SET_ID := l_non_bsv.FLEX_VALUE_SET_ID;
328 PIPE ROW(l_bsv_record);
329 END IF;
330 END LOOP;
331 GOTO FULL_EXIT;
332 ELSE
333 --Assigned bsv
334 FOR l_bsv in CUR_BSV LOOP
335 fnd_flex_server.check_value_security(p_security_check_mode => 'YH',
336 p_flex_value_set_id => l_flex_valueset_id,
337 p_parent_flex_value => null,
338 p_flex_value => l_bsv.flex_value, --BSV
339 p_resp_application_id => p_resp_app_id,
340 p_responsibility_id => p_resp_id,
341 x_security_status => l_security_status,
342 x_error_message => l_error_message);
343 IF (l_security_status = 'NOT-SECURED') THEN
344 l_bsv_record.FLEX_VALUE := l_bsv.flex_value;
345 l_bsv_record.DESCRIPTION := l_bsv.DESCRIPTION;
346 l_bsv_record.FLEX_VALUE_SET_ID := l_bsv.FLEX_VALUE_SET_ID;
347 PIPE ROW(l_bsv_record);
348 END IF;
349 END LOOP;
350 END IF;
351 <<FULL_EXIT>>
352
353 RETURN;
354 END Get_SECURITY_BSV;
355
356
357 PROCEDURE Check_LE( p_ledger pls_integer,
358 p_legal_entity pls_integer,
359 p_bsv VARCHAR2
360 )
361 IS
362 l_procedure_name VARCHAR2(40) := 'Check_LE';
363 CURSOR CUR_PARENT(c_valueset_id pls_integer, c_bsv varchar2) IS
364 select ffh.flex_value_set_id, ffh.FLEX_VALUE, SUMMARY_FLAG
365 from FND_FLEX_VALUE_CHILDREN_V ffh
366 where ffh.flex_value_set_id = c_valueset_id
367 and ffh.PARENT_FLEX_VALUE = c_bsv
368 order by ffh.FLEX_VALUE;
369 l_flex_valueset_id PLS_INTEGER;
370 l_bsv_temp VARCHAR2(50):=p_bsv;
371 l_exists PLS_INTEGER:=0;
372 l_summary CHAR(1);
373 BEGIN
374 --get valueset_id
375 SELECT fifsv.FLEX_VALUE_SET_ID
376 into l_flex_valueset_id
377 FROM FND_ID_FLEX_SEGMENTS_VL fifsv,
378 FND_SEGMENT_ATTRIBUTE_VALUES FSAV
379 WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
380 AND FSAV.APPLICATION_ID = 101
381 AND FSAV.ID_FLEX_CODE = 'GL#'
382 AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
383 AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
384 AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
385 AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
386 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
387 AND fifsv.ID_FLEX_NUM in
388 (SELECT chart_of_accounts_id
389 FROM gl_ledgers gls
390 where gls.ledger_id = p_ledger);
391
392 --get bsv summary
393 SELECT SUMMARY_FLAG
394 INTO l_summary
395 FROM FND_FLEX_VALUE_SETS ffs, FND_FLEX_VALUES_VL ffv
396 WHERE ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
397 AND ffv.FLEX_VALUE_SET_ID = l_flex_valueset_id
398 AND ffv.FLEX_VALUE = l_bsv_temp;
399
400 --child
401 IF l_summary='N' THEN
402 SELECT (SELECT COUNT(1)
403 FROM GL_LEGAL_ENTITIES_BSVS
404 WHERE LEGAL_ENTITY_ID = p_legal_entity
405 AND FLEX_VALUE_SET_ID = l_flex_valueset_id
406 AND trunc(nvl(start_date, sysdate), 'DD') <=
407 trunc(sysdate, 'DD')
408 AND trunc(nvl(end_date, sysdate), 'DD') >=
409 trunc(sysdate, 'DD')
410 AND FLEX_SEGMENT_VALUE = l_bsv_temp)
411 INTO l_exists
412 FROM DUAL;
413 IF l_exists =1 THEN
414 INSERT INTO JA_CN_BANK_LE_GT(LEGAL_ENTITY_ID,Balance_Segment,flag) VALUES(p_legal_entity,l_bsv_temp,l_exists);
415 END IF;
416 ELSE
417 --parent
418 FOR l_parent in CUR_PARENT(l_flex_valueset_id,l_bsv_temp) LOOP
419 l_bsv_temp := l_parent.FLEX_VALUE;
420 --recursion
421 IF l_parent.SUMMARY_FLAG='Y' THEN
422 Check_LE(p_ledger,p_legal_entity,l_bsv_temp);
423 ELSE
424 SELECT (SELECT COUNT(1)
425 FROM GL_LEGAL_ENTITIES_BSVS
426 WHERE LEGAL_ENTITY_ID = p_legal_entity
427 AND FLEX_VALUE_SET_ID = l_flex_valueset_id
428 AND trunc(nvl(start_date, sysdate), 'DD') <=
429 trunc(sysdate, 'DD')
430 AND trunc(nvl(end_date, sysdate), 'DD') >=
431 trunc(sysdate, 'DD')
432 AND FLEX_SEGMENT_VALUE = l_bsv_temp)
433 INTO l_exists
434 FROM DUAL;
435 IF l_exists =1 THEN
436 INSERT INTO JA_CN_BANK_LE_GT(LEGAL_ENTITY_ID,Balance_Segment,flag) VALUES(p_legal_entity,l_bsv_temp,l_exists);
437 EXIT;
438 END IF;
439 END IF;
440 END LOOP;
441 END IF;
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 RAISE_APPLICATION_ERROR(-20000,l_procedure_name||':'||SQLERRM);
446 END Check_LE;
447
448 --==========================================================================
449 -- FUNCTION NAME:
450 -- Check_LE_BSV public
451 --
452 -- DESCRIPTION:
453 -- This function returns a boolean value whether the bsv is assigned to the legal entity.
454 --
455 -- PARAMETERS:
456 -- In: p_ledger NUMBER
457 -- In: p_legal_entity NUMBER
458 -- In: p_bsv NUMBER
459 -- RETURN:
460 -- false=>not exists, true=>exists
461 -- DESIGN REFERENCES:
462 -- None
463 --
464 -- CHANGE HISTORY:
465 -- Nov-27-2012 Jar Wang Created
466 --===========================================================================
467 FUNCTION Check_LE_BSV( p_ledger pls_integer,
468 p_legal_entity pls_integer,
469 p_bsv VARCHAR2
470 ) RETURN BOOLEAN
471 IS
472 CURSOR CUR_LEGAL IS
473 select cfgdet.object_id legal_entity_id
474 from gl_ledger_config_details primdet,
475 gl_ledgers lg,
476 gl_ledger_relationships rs,
477 gl_ledger_configurations cfg,
478 gl_ledger_config_details cfgdet
479 where rs.application_id = 101
480 and ((rs.target_ledger_category_code = 'SECONDARY' and
481 rs.relationship_type_code <> 'NONE') or
482 (rs.target_ledger_category_code = 'PRIMARY' and
483 rs.relationship_type_code = 'NONE') or
484 (rs.target_ledger_category_code = 'ALC' and
485 rs.relationship_type_code in ('JOURNAL', 'SUBLEDGER')))
486 and lg.ledger_id = rs.target_ledger_id
487 and lg.ledger_category_code = rs.target_ledger_category_code
488 and nvl(lg.complete_flag, 'Y') = 'Y'
489 and primdet.object_id = rs.primary_ledger_id
490 and primdet.object_type_code = 'PRIMARY'
491 and primdet.setup_step_code = 'NONE'
492 and cfg.configuration_id = primdet.configuration_id
493 and cfgdet.configuration_id(+) = cfg.configuration_id
494 and cfgdet.object_type_code(+) = 'LEGAL_ENTITY'
495 and ledger_id = p_ledger;
496 l_legal CUR_LEGAL%ROWTYPE;
497 l_accumulate PLS_INTEGER:=0;
498 l_exists PLS_INTEGER:=0;
499 l_flex_valueset_id PLS_INTEGER;
500 l_boolean BOOLEAN:=false;
501 BEGIN
502 --get valueset_id
503 SELECT fifsv.FLEX_VALUE_SET_ID
504 into l_flex_valueset_id
505 FROM FND_ID_FLEX_SEGMENTS_VL fifsv,
506 FND_SEGMENT_ATTRIBUTE_VALUES FSAV
507 WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
508 AND FSAV.APPLICATION_ID = 101
509 AND FSAV.ID_FLEX_CODE = 'GL#'
510 AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
511 AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
512 AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
513 AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
514 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
515 AND fifsv.ID_FLEX_NUM in
516 (SELECT chart_of_accounts_id
517 FROM gl_ledgers gls
518 where gls.ledger_id = p_ledger);
519 --Validate all legal entity under current ledger whether be assigned a bsv at least.
520 OPEN CUR_LEGAL;
521 LOOP
522 FETCH CUR_LEGAL INTO l_legal;
523 EXIT WHEN CUR_LEGAL%NOTFOUND;
524 SELECT (SELECT COUNT(1)
525 FROM GL_LEGAL_ENTITIES_BSVS
526 WHERE LEGAL_ENTITY_ID = l_legal.legal_entity_id
527 AND trunc(nvl(start_date, sysdate), 'DD') <=
528 trunc(sysdate, 'DD')
529 AND trunc(nvl(end_date, sysdate), 'DD') >=
530 trunc(sysdate, 'DD')
531 AND FLEX_VALUE_SET_ID = l_flex_valueset_id)
532 INTO l_exists
533 FROM DUAL;
534 l_accumulate :=l_accumulate+ l_exists;
535 END LOOP;
536 CLOSE CUR_LEGAL;
537 IF l_accumulate = 0 THEN
538 RETURN TRUE;
539 END IF;
540
541 Check_LE( p_ledger, p_legal_entity, p_bsv ) ;
542 SELECT (SELECT count(1) FROM JA_CN_BANK_LE_GT )
543 INTO l_exists
544 FROM DUAL;
545 IF l_exists =0 THEN
546 --the bsv don't belong to the le
547 RETURN FALSE;
548 ELSE
549 RETURN TRUE;
550 END IF;
551 RETURN l_boolean;
552 END Check_LE_BSV ;
553
554
555 --==========================================================================
556 -- PROCEDURE NAME:
557 -- Submit_XML_Charset_Conversion Private
558 --
559 -- DESCRIPTION:
560 -- This function is used to submit charset conversion concurrent
561 -- for XML files.
562 -- PARAMETERS:
563 -- In: p_xml_request_id xml publisher concurrent request id
564 -- p_source_charset source charset
565 -- p_destination_charset destination charset
566 -- p_source_separator source separator
567 -- Out: x_charset_request_id charset conversion request id
568 -- x_result_flag result flag
569 --
570 --
571 -- DESIGN REFERENCES:
572 -- None
573 --
574 -- CHANGE HISTORY:
575 -- 03/12/2012 Jarwang Created
576 --==========================================================================
577
578
579 --==========================================================================
580 -- FUNCTION NAME:
581 -- Check_DAS_BSV public
582 --
583 -- DESCRIPTION:
584 -- This function will check whether the bsv belong to the data access set.
585 --
586 -- PARAMETERS:
587 -- In: p_das NUMBER
588 -- In: p_ledger NUMBER
589 -- In: p_bsv NUMBER
590 -- RETURN: 1--pass -1--das failure -2--security rule failure
591 -- DESIGN REFERENCES:
592 -- None
593 --
594 -- CHANGE HISTORY:
595 -- Jan-14-2013 Jar Wang Created for bug#16053344
596 --===========================================================================
597 FUNCTION Check_DAS_BSV(p_das pls_integer,
598 p_ledger pls_integer,
599 p_bsv VARCHAR2
600 ) RETURN PLS_INTEGER
601 IS
602 CURSOR CUR_DAS
603 IS
604 select segment_value
605 from GL_ACCESS_SET_NORM_ASSIGN gasn, GL_ACCESS_SETS gas
606 where gasn.access_set_id = gas.access_set_id
607 and SECURITY_SEGMENT_CODE = 'B'
608 and ALL_SEGMENT_VALUE_FLAG='N'
609 and gasn.access_set_id = p_das
610 and ledger_id = p_ledger;
611 l_das CUR_DAS%rowtype;
612 l_bsv_all CHAR(1);
613 l_das_bsv PLS_INTEGER:=0;
614 l_exists PLS_INTEGER:=0;
615 l_return PLS_INTEGER:=0;
616 l_flex_valueset_id pls_integer;
617 l_security_status varchar2(30);
618 l_error_message varchar2(1000);
619 BEGIN
620 --Get the bsv flag of the das
621 select count(1)
622 into l_das_bsv
623 from GL_ACCESS_SETS gas
624 where SECURITY_SEGMENT_CODE = 'B'
625 and gas.access_set_id = p_das;
626
627 --Enable Security rule
628 IF l_das_bsv=0 THEN
629 SELECT fifsv.FLEX_VALUE_SET_ID
630 into l_flex_valueset_id
631 FROM FND_ID_FLEX_SEGMENTS_VL fifsv,
632 FND_SEGMENT_ATTRIBUTE_VALUES FSAV
633 WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
634 AND FSAV.APPLICATION_ID = 101
635 AND FSAV.ID_FLEX_CODE = 'GL#'
636 AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
637 AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
638 AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
639 AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
640 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
641 AND fifsv.ID_FLEX_NUM in
642 (SELECT chart_of_accounts_id
643 FROM gl_ledgers gls
644 where gls.ledger_id = p_ledger);
645 fnd_flex_server.check_value_security(p_security_check_mode => 'YH',
646 p_flex_value_set_id => l_flex_valueset_id,
647 p_parent_flex_value => null,
648 p_flex_value => p_bsv, --BSV
649 p_resp_application_id => fnd_global.RESP_APPL_ID,
650 p_responsibility_id => fnd_global.RESP_ID,
651 x_security_status => l_security_status,
652 x_error_message => l_error_message);
653 IF (l_security_status = 'EXCLUDED') THEN
654 l_return:=-2;
655 ELSE
656 l_return:=1;
657 END IF;
658 RETURN l_return;
659 ELSE
660 --das
661 BEGIN
662 --Get 'ALL' flag of bsv in the das
663 select ALL_SEGMENT_VALUE_FLAG
664 INTO l_bsv_all
665 from GL_ACCESS_SET_NORM_ASSIGN gasn, GL_ACCESS_SETS gas
666 where gasn.access_set_id = gas.access_set_id
667 and SECURITY_SEGMENT_CODE = 'B'
668 and ALL_SEGMENT_VALUE_FLAG = 'Y'
669 and gasn.access_set_id = p_das
670 and ledger_id = p_ledger
671 and rownum = 1;
672 IF l_bsv_all='Y' THEN
673 l_return:=1;
674 RETURN l_return;
675 END IF;
676 EXCEPTION
677 WHEN NO_DATA_FOUND THEN
678 NULL;
679 END;
680 --
681 OPEN CUR_DAS;
682 LOOP
683 FETCH CUR_DAS INTO l_das;
684 EXIT WHEN CUR_DAS%NOTFOUND;
685 --das include this bsv
686 IF l_das.segment_value= p_bsv THEN
687 l_exists:=1;
688 EXIT;
689 ELSE
690 l_exists:=-1;
691 END IF;
692 END LOOP;
693 CLOSE CUR_DAS;
694
695 IF l_exists=-1 THEN
696 l_return:=-1;
697 ELSE
698 l_return:=1;
699 END IF;
700
701 RETURN l_return;
702 END IF;
703 END Check_DAS_BSV;
704
705
706 PROCEDURE Submit_XML_Charset_Conversion(p_Xml_Request_Id IN NUMBER,
707 p_Source_Charset IN VARCHAR2,
708 p_Destination_Charset IN VARCHAR2,
709 p_Source_Separator IN VARCHAR2,
710 x_Charset_Request_Id OUT NOCOPY NUMBER,
711 x_Result_Flag OUT NOCOPY VARCHAR2) IS
712
713 l_Procedure_Name VARCHAR2(30) := 'Submit_XML_Charset_Conversion ';
714 l_Runtime_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
715 l_Procedure_Level NUMBER := Fnd_Log.Level_Procedure;
716 --l_statement_level NUMBER := fnd_log.level_statement;
717 --l_exception_level NUMBER := fnd_log.level_exception;
718 l_Complete_Flag BOOLEAN;
719 l_Phase VARCHAR2(100);
720 l_Status VARCHAR2(100);
721 l_Del_Phase VARCHAR2(100);
722 l_Del_Status VARCHAR2(100);
723 l_Message VARCHAR2(1000);
724
725 BEGIN
726 --log for debug
727 IF (l_Procedure_Level >= l_Runtime_Level)
728 THEN
729 Fnd_Log.STRING(l_Procedure_Level,
730 GV_MODULE_PREFIX || '.' || l_Procedure_Name || '.begin',
731 'begin procedure');
732 END IF; --l_procedure_level >= l_runtime_level
733 -- submit charset conversion concurrent program
734 x_Charset_Request_Id := Fnd_Request.Submit_Request('JA',
735 'JACNBCXP',
736 NULL,
737 SYSDATE,
738 FALSE,
739 p_Xml_Request_Id,
740 p_Source_Charset,
741 p_Destination_Charset,
742 p_Source_Separator);
743
744 IF (x_Charset_Request_Id <= 0 OR x_Charset_Request_Id IS NULL)
745 THEN
746 x_Result_Flag := 'Error';
747 ELSE
748 COMMIT;
749 --Wait for concurrent complete
750 l_Complete_Flag := Fnd_Concurrent.Wait_For_Request(x_Charset_Request_Id,
751 1,
752 0,
753 l_Phase,
754 l_Status,
755 l_Del_Phase,
756 l_Del_Status,
757 l_Message);
758 IF l_Complete_Flag = FALSE
759 OR Ja_Cn_Utility.Get_Lookup_Code(p_Lookup_Meaning => l_Status,
760 p_Lookup_Type => 'CP_STATUS_CODE') <> 'C'
761 THEN
762 x_Result_Flag := 'Error';
763 ELSE
764 x_Result_Flag := 'Success';
765 END IF; -- l_complete_flag = false
766 END IF; -- (x_xml_request_id <= 0 OR x_xml_request_id IS NULL)
767
768 --log for debug
769 IF (l_Procedure_Level >= l_Runtime_Level)
770 THEN
771 Fnd_Log.STRING(l_Procedure_Level,
772 GV_MODULE_PREFIX || '.' || l_Procedure_Name || '.end',
773 'end procedure');
774 END IF; --l_procedure_level >= l_runtime_level
775 EXCEPTION
776 WHEN OTHERS THEN
777 --log for debug
778 IF (Fnd_Log.Level_Unexpected >= Fnd_Log.g_Current_Runtime_Level)
779 THEN
780 Fnd_Log.STRING(Fnd_Log.Level_Unexpected,
781 GV_MODULE_PREFIX || l_Procedure_Name ||
782 '. OTHER_EXCEPTION ',
783 SQLCODE || SQLERRM);
784 END IF; -- fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
785 RAISE;
786 END Submit_XML_Charset_Conversion;
787
788
789 FUNCTION Get_Currency(reportName VARCHAR2)
790 RETURN VARCHAR2
791 IS
792 v_currency varchar2(20);
793 BEGIN
794 select NVL(MEANING, 'Units') INTO v_currency
795 from RG_REPORT_AXES rga, RG_LOOKUPS LK
796 where LK.LOOKUP_CODE = rga.display_units
797 and LK.LOOKUP_TYPE = 'DISPLAY_UNITS'
798 and rownum = 1
799 AND AXIS_SET_ID =
800 (select COLUMN_SET_ID from RG_REPORTS WHERE NAME = reportName)
801 ORDER BY AXIS_SEQ;
802
803 return v_currency;
804 exception
805 when no_data_found then
806 return 'Units';
807 END Get_Currency;
808 end JA_CN_SYSOPS_BANK_PKG;
809
810
811