[Home] [Help]
PACKAGE BODY: APPS.CEP_STANDARD
Source
1 package body CEP_STANDARD AS
2 /* $Header: ceseutlb.pls 120.29 2011/07/19 09:29:15 bkkashya ship $ */
3 /*-------------------------------------------------------------------------+
4 | |
5 | PRIVATE VARIABLES |
6 | |
7 +-------------------------------------------------------------------------*/
8
9 debug_flag varchar2(1) := null; -- 'F' for file debug and 'S' for screen debug
10
11 FUNCTION return_patch_level RETURN VARCHAR2 IS
12 BEGIN
13 RETURN (G_patch_level);
14 END return_patch_level;
15
16
17 /*----------------------------------------------------------------------------*
18 | PUBLIC PROCEDURE |
19 | debug - Print a debug message |
20 | |
21 | DESCRIPTION |
22 | Generate standard debug information sending it to dbms_output so that |
23 | the client tool can log it for the user. |
24 | |
25 | REQUIRES |
26 | line_of_text The line of text that will be displayed. |
27 | |
28 | EXCEPTIONS RAISED |
29 | |
30 | KNOWN BUGS |
31 | |
32 | NOTES |
33 | |
34 | HISTORY |
35 | 12 Jun 95 Ganesh Vaidee Created |
36 | 28 Jul 99 K Adams Added option to either send it to a file or |
37 | dbms_output. |
38 | |
39 *----------------------------------------------------------------------------*/
40 procedure debug( line in varchar2 ) is
41 begin
42 If g_debug = 'Y' Then /* Bug 7125240 */
43 /* Bug 3234187 */
44 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
45 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
46 'ce', line);
47 end if;
48
49
50 IF fnd_global.CONC_REQUEST_ID <> -1 THEN
51 FND_FILE.put_line(FND_FILE.LOG, line);
52 END IF;
53 End If ;
54
55 end;
56 --
57 /*----------------------------------------------------------------------------*
58 | PUBLIC PROCEDURE |
59 | enable_debug - Enable run time debugging |
60 | |
61 | DESCRIPTION |
62 | Generate standard debug information sending it to dbms_output so that |
63 | the client tool can log it for the user. |
64 | |
65 | REQUIRES |
66 | |
67 | EXCEPTIONS RAISED |
68 | |
69 | KNOWN BUGS |
70 | |
71 | NOTES |
72 | |
73 | HISTORY |
74 | 12 Jun 95 Ganesh Vaidee Created |
75 | 28 Jul 99 K Adams Added option to either send it to a file or |
76 | dbms_output. |
77 | If debug path and file name are passed, |
78 | it writes to the path/file_name instead |
79 | of dbms_output. |
80 | If AR is installed, it includes ar debug |
81 | messages, too. |
82 | |
83 *----------------------------------------------------------------------------*/
84 procedure enable_debug( path_name in varchar2 default NULL,
85 file_name in varchar2 default NULL) is
86
87 install BOOLEAN;
88 status VARCHAR2(1);
89 industry VARCHAR2(1);
90
91 begin
92 install := fnd_installation.get(222,222,status,industry);
93
94 if (path_name is not null and file_name is not null) then
95 debug_flag := 'F';
96 ce_debug_pkg.enable_file_debug(path_name, file_name);
97 /* Bug 7445326 - removed AR logging
98 if (status = 'I') then
99 arp_standard.enable_file_debug(path_name,file_name);
100 end if;*/
101 else
102 debug_flag := 'S';
103 /* Bug 7445326 - removed AR logging
104 if (status = 'I') then
105 arp_standard.enable_debug;
106 end if;*/
107 end if;
108 exception
109 when others then
110 raise;
111 end;
112 --
113 /*----------------------------------------------------------------------------*
114 | PUBLIC PROCEDURE |
115 | disable_debug - Disable run time debugging |
116 | |
117 | DESCRIPTION |
118 | Generate standard debug information sending it to dbms_output so that |
119 | the client tool can log it for the user. |
120 | |
121 | REQUIRES |
122 | |
123 | EXCEPTIONS RAISED |
124 | |
125 | KNOWN BUGS |
126 | |
127 | NOTES |
128 | |
129 | HISTORY |
130 | 12 Jun 95 Ganesh Vaidee Created |
131 | 28 Jul 99 K Adams Added option to either send it to a file or |
132 | dbms_output. |
133 | |
134 *----------------------------------------------------------------------------*/
135 procedure disable_debug (display_debug in varchar2) is
136
137 install BOOLEAN;
138 status VARCHAR2(1);
139 industry VARCHAR2(1);
140
141 begin
142 if display_debug = 'Y' then
143 debug_flag := null;
144 ce_debug_pkg.disable_file_debug;
145 /* - Bug 7445326 Removed ar logging
146 install := fnd_installation.get(222,222,status,industry);
147 if (status ='I') then
148 arp_standard.disable_debug;
149 arp_standard.disable_file_debug;
150 end if;
151 */
152 end if;
153 exception
154 when others then
155 raise;
156 end;
157 --
158
159 FUNCTION Get_Window_Session_Title(p_org_id number default NULL,
160 p_legal_entity_id number default NULL) RETURN VARCHAR2 IS
161
162
163 l_multi_org VARCHAR2(1);
164 l_multi_cur VARCHAR2(1);
165 l_wnd_context VARCHAR2(80);
166 l_id VARCHAR2(15);
167
168 BEGIN
169
170 /*
171 ***
172 *** Get multi-org and MRC information on the current
173 *** prodcut installation.
174 ***
175 */
176 SELECT nvl(multi_org_flag, 'N')
177 , nvl(multi_currency_flag, 'N')
178 INTO l_multi_org
179 , l_multi_cur
180 FROM fnd_product_groups;
181
182
183 /*
184 ***
185 *** Case #1 : Non-Multi-Org or Multi-SOB
186 ***
187 *** A. MRC not installed, OR
188 *** MRC installed, Non-Primary/Reporting Books
189 *** Form Name (SPB Short Name) - context Info
190 *** e.g. Maintain Forecasts (US OPS) - Forecast Context Info
191 ***
192 *** B. MRC installed, Primary Books
193 *** Form Name (SOB Short Name: Primary Currency) - Context Info
194 *** e.g. Maintain Forecasts (US OPS: USD) - Forecast Context Info
195 *** C. MRC installed, Report Books
196 *** Form Name (SOB Short Name: Reporting Currency) - Context Info
197 *** e.g. Maintain Forecasts (US OPS: EUR) - Forecast Context Info
198 ***
199 ***
200 */
201 IF (l_multi_org = 'N') THEN
202
203 BEGIN
204 select g.short_name ||
205 decode(g.mrc_sob_type_code, 'N', NULL,
206 decode(l_multi_cur, 'N', NULL,
207 ': ' || substr(g.currency_code, 1, 5)))
208 into l_wnd_context
209 from gl_sets_of_books g
210 , ce_system_parameters c
211 where c.set_of_books_id = g.set_of_books_id;
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 return (NULL);
215 END;
216
217 /*
218 ***
219 *** Case #2 : Multi-Org
220 ***
221 *** A. MRC not installed, OR
222 *** MRC installed, Non-Primary/Reporting Books
223 *** Form Name (OU Name) - Context Info
224 *** e.g. Maintain Forecasts (US West) - Forecast Context Info
225 **
226 *** B. MRC installed, Primary Books
227 *** Form Name (OU Name: Primary Currency) - Context Info
228 *** e.g. Maintain Forecast (US West: USD) - Forecast Context Info
229 ***
230 *** C. MRC installed, Reporting Books
231 *** Form Name (OU Name: Reporting Currency) - Context Info
232 *** e.g. Maintain Forecast (US West: EUR) - Forecast Context Info
233 ***
234 ***
235 */
236 ELSE
237
238 --bug 3676745 MOAC and BA uptake
239 IF (p_org_id is not null) THEN
240
241 --FND_PROFILE.GET ('ORG_ID', l_id);
242
243 BEGIN
244 select substr(h.name, 1, 53) ||
245 decode(g.mrc_sob_type_code, 'N', substr(h.name, 54, 7),
246 decode(l_multi_cur, 'N', substr(h.name, 54, 7),
247 ': ' || substr(g.currency_code, 1, 5)))
248 into l_wnd_context
249 from gl_sets_of_books g,
250 ce_system_parameters c,
251 XLE_FP_OU_LEDGER_V xo,
252 hr_operating_units h
253 where h.organization_id = to_number(p_org_id)
254 --where h.organization_id = to_number(l_id)
255 --and h.organization_id = c.org_id
256 and h.organization_id = xo.OPERATING_UNIT_ID
257 and xo.LEGAL_ENTITY_ID = c.LEGAL_ENTITY_ID
258 and c.set_of_books_id = g.set_of_books_id;
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN null;
261 WHEN OTHERS THEN null;
262 END;
263 ELSIF (p_legal_entity_id is not null) THEN
264
265 BEGIN
266 select substr(h.name, 1, 53) ||
267 decode(g.mrc_sob_type_code, 'N', substr(h.name, 54, 7),
268 decode(l_multi_cur, 'N', substr(h.name, 54, 7),
269 ': ' || substr(g.currency_code, 1, 5)))
270 into l_wnd_context
271 from gl_sets_of_books g,
272 ce_system_parameters c,
273 XLE_ENTITY_PROFILES h
274 where h.LEGAL_ENTITY_ID = to_number(p_legal_entity_id)
275 and h.LEGAL_ENTITY_ID = c.LEGAL_ENTITY_ID
276 and c.set_of_books_id = g.set_of_books_id;
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN null;
279 WHEN OTHERS THEN null;
280 END;
281 END IF;
282
283 END IF;
284
285 return l_wnd_context;
286
287 END Get_Window_Session_Title;
288
289 /*----------------------------------------------------------------------------*
290 | PUBLIC PROCEDURE |
291 | get_effective_date |
292 | |
293 | DESCRIPTION |
294 | This is primarily for AR autolockbox interface. Calculates the |
295 | effective date for receipts. |
296 | |
297 | REQUIRES |
298 | |
299 | EXCEPTIONS RAISED |
300 | |
301 | KNOWN BUGS |
302 | |
303 | NOTES |
304 | |
305 | HISTORY |
306 | 29 Oct 1996 Bidemi Carrol Created |
307 | |
308 *----------------------------------------------------------------------------*/
309 function get_effective_date(p_bank_account_id NUMBER,
310 p_trx_code VARCHAR2,
311 p_receipt_date DATE) RETURN DATE IS
312 fd ce_transaction_codes.float_days%TYPE;
313 begin
314 select nvl(float_days,0)
315 into fd
316 from ce_transaction_codes ctc
317 where ctc.trx_code = p_trx_code
318 and ctc.bank_account_id = p_bank_account_id;
319
320 return (p_receipt_date + fd);
321 exception
322 when others then
323 return p_receipt_date;
324 end get_effective_date;
325
326
327 /**
328 * PROCEDURE debug_msg_stack
329 *
330 * DESCRIPTION
331 * Show debug messages on message stack.
332 *
333 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
334 *
335 * ARGUMENTS
336 * IN:
337 * p_msg_count Message count in message stack.
338 * p_msg_data Message data if message count is 1.
339 *
340 * MODIFICATION HISTORY
341 *
342 * 15-SEP-2004 Xin Wang Created.
343 *
344 */
345 PROCEDURE debug_msg_stack(p_msg_count IN NUMBER,
346 p_msg_data IN VARCHAR2) IS
347 i NUMBER;
348
349 BEGIN
350
351 IF p_msg_count <= 0 THEN
352 RETURN;
353 END IF;
354
355 IF p_msg_count = 1 THEN
356 debug( p_msg_data);
357 ELSE
358 FOR i IN 1..p_msg_count LOOP
359 debug( FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ));
360 END LOOP;
361 END IF;
362
363 END debug_msg_stack;
364
365
366 /*=======================================================================+
367 | PUBLIC PRECEDURE sql_error |
368 | |
369 | DESCRIPTION |
370 | This procedure sets the error message and raise an exception |
371 | for unhandled sql errors. |
372 | Called by other routines. |
373 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
374 | |
375 | ARGUMENTS |
376 | IN: |
377 | p_routine |
378 | p_errcode |
379 | p_errmsg |
380 +=======================================================================*/
381 PROCEDURE sql_error(p_routine IN VARCHAR2,
382 p_errcode IN NUMBER,
383 p_errmsg IN VARCHAR2) IS
384 BEGIN
385 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
386 fnd_message.set_token('ROUTINE', p_routine);
387 fnd_message.set_token('ERRNO', p_errcode);
388 fnd_message.set_token('REASON', p_errmsg);
389 app_exception.raise_exception;
390 EXCEPTION
391 WHEN OTHERS THEN RAISE;
392 END sql_error;
393
394
395 /*=======================================================================+
396 | PUBLIC PRECEDURE get_umx_predicate |
397 | |
398 | DESCRIPTION |
399 | This procedure return where clause predicate generated from UMX API |
400 | to apply BA access security or BAT security |
401 | USAGE |
402 | From clause should include XLE_FIRSTPARTY_INFORMATION_V or any |
403 | other view/table that has LEGAL_ENTITY_ID column since Where clause |
404 | predicate references LEGAL_ENTITY_ID column. |
405 | |
406 | ARGUMENTS |
407 | IN: |
408 | p_object_name: CEBAT, CEBAC |
409 +=======================================================================*/
410 FUNCTION get_umx_predicate(p_object_name IN VARCHAR2) RETURN VARCHAR2 IS
411 p_predicate varchar2(32767);
412 p_return_status varchar2(30);
413 BEGIN
414
415 FND_DATA_SECURITY.GET_SECURITY_PREDICATE(
416 1.0,
417 null,
418 p_object_name,
419 null,
420 fnd_global.user_name,
421 null,
422 p_predicate,
423 p_return_status,
424 null);
425
426 IF p_return_status <> 'T' THEN
427 RETURN ('1=2');
428 ELSE
429 RETURN p_predicate;
430 END IF;
431 EXCEPTION
432 WHEN OTHERS THEN RAISE;
433 END get_umx_predicate;
434
435
436 /*=======================================================================+
437 | PUBLIC PRECEDURE check_ba_security |
438 | |
439 | DESCRIPTION |
440 | This function checks if user has access to the input LE based on |
441 | Bank account Access or Bank Account Transfer security defined in UMX|
442 | ARGUMENTS |
443 | IN: |
444 | p_le_id: Legal Entity ID |
445 | p_security_mode: CEBAT for Bank Account Transfer security |
446 | CEBAC for Bank Account Creation security |
447 | OUT: |
448 | 1: if user has access |
449 | 0: otherwise |
450 +=======================================================================*/
451 FUNCTION check_ba_security ( p_le_id NUMBER,
452 p_security_mode VARCHAR2) RETURN NUMBER IS
453 l_predicate varchar2(32767);
454 l_return_status varchar2(30);
455 l_final_query varchar2(32767);
456 l_cursor_id NUMBER;
457 l_exec_id NUMBER;
458 l_row NUMBER;
459 BEGIN
460
461 l_cursor_id := DBMS_SQL.open_cursor;
462
463 l_final_query := 'SELECT 1 from fnd_grants grt, fnd_objects obj, wf_user_roles rol, xle_entity_profiles le ' ||
464 'where grt.object_id = obj.object_id and Trunc(Nvl(grt.end_date,SYSDATE)) >= Trunc(SYSDATE) and obj.obj_name = ''' || p_security_mode || ''' and ' ||
465 'GRANTEE_TYPE = '''||'GROUP'||''' and GRANTEE_KEY = rol.role_name and ' ||
466 'rol.user_name in ((select fnd_global.user_name from dual) UNION ALL '||
467 '(select incrns.name from wf_local_roles incrns, fnd_user f '||
468 'where '''|| 'HZ_PARTY' ||''' = incrns.orig_system and f.user_name = fnd_global.user_name '||
469 'and f.person_party_id = incrns.orig_system_id and incrns.partition_id = 9)) '||
470 'and INSTANCE_PK1_VALUE = to_char(le.legal_entity_id) '||
471 'and le.legal_entity_id = '|| to_char(p_le_id);
472
473
474 DBMS_SQL.Parse(l_cursor_id,
475 l_final_query,
476 DBMS_SQL.v7);
477
478 cep_standard.debug('Parsed sucessfully');
479
480 l_exec_id := DBMS_SQL.execute(l_cursor_id);
481 l_row := DBMS_SQL.FETCH_ROWS(l_cursor_id);
482 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
483
484 RETURN l_row;
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 cep_standard.debug('EXCEPTION - OTHERS: check_bat_security');
489 IF DBMS_SQL.IS_OPEN(l_cursor_id) THEN
490 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
491 cep_standard.debug('Cursor Closed');
492 END IF;
493 RAISE;
494 END check_ba_security;
495
496 /*=======================================================================+
497 | PUBLIC PRECEDURE get_conversion_rate |
498 | |
499 | DESCRIPTION |
500 | This function calls gl_currency_api.get_rate to return exchange |
501 | rate. If there is no rate defined in GL or any exception occurs |
502 | then this function returns 0. |
503 | ARGUMENTS |
504 | IN: |
505 | p_ledger_id: Ledger ID |
506 | p_currency_code: Currency_code |
507 | p_exchange_date: Exchange rate date |
508 | p_exchange_rate_type; Exchang rate type |
509 | OUT: |
510 | exchange rate |
511 | 0: if no rate defined in GL or error occurs |
512 +=======================================================================*/
513 FUNCTION get_conversion_rate ( p_ledger_id NUMBER,
514 p_currency_code VARCHAR2,
515 p_exchange_date DATE,
516 p_exchange_rate_type VARCHAR2) RETURN NUMBER IS
517 BEGIN
518
519 RETURN nvl(gl_currency_api.get_rate(p_ledger_id,
520 p_currency_code,
521 p_exchange_date,
522 p_exchange_rate_type),0);
523 EXCEPTION
524 WHEN OTHERS THEN
525 RETURN 0;
526 END get_conversion_rate;
527
528 /*=======================================================================+
529 | PUBLIC PRECEDURE init_security |
530 | |
531 | DESCRIPTION |
532 | This procedure populates global temp table, ce_security_profiles_tmp,|
533 | based on ce_security_profiles_v. The ce_security_profiles_tmp table |
534 | is referenced from ce_bank_accts_gt_v. |
535 +=======================================================================*/
536 PROCEDURE init_security IS
537 l_resp_appl_id NUMBER(15); -- 8823179: Added variable
538 l_appl_name VARCHAR2(50);
539 BEGIN
540 -- 8823179: IF block added
541 IF FND_GLOBAL.resp_appl_id=101 THEN
542 l_resp_appl_id := 260;
543 ELSE
544 l_resp_appl_id := FND_GLOBAL.resp_appl_id;
545 END IF;
546
547 -- call MO Init if it has not been set yet
548 IF MO_GLOBAL.is_mo_init_done = 'N' THEN
549 select APPLICATION_SHORT_NAME
550 into l_appl_name
551 from FND_APPLICATION
552 where APPLICATION_ID = l_resp_appl_id; -- 8823179 : Changed l_resp_appl_id
553
554 -- Bug 5860453. Do not call MOAC if the product is XTR
555 IF (l_appl_name <> 'XTR') THEN
556 -- Set MOAC security
557 MO_GLOBAL.init(l_appl_name);
558 END IF;
559 END IF;
560
561 -- clean up the GT table
562 delete ce_security_profiles_gt;
563
564 insert into ce_security_profiles_gt
565 (organization_type,
566 organization_id,
567 name)
568 select organization_type,
569 organization_id,
570 name
571 from ce_security_profiles_v;
572 EXCEPTION
573 WHEN OTHERS THEN
574 null;
575 END init_security;
576
577 /*=======================================================================+
578 | PUBLIC PRECEDURE init_security_baui |
579 | |
580 | DESCRIPTION |
581 | This procedure populates global temp table, ce_security_profiles_tmp,|
582 | based on security logic in ce_security_profiles_v except for BG. |
583 | All available BG will be populated to ce_security_profiles_tmp table |
584 +=======================================================================*/
585 PROCEDURE init_security_baui IS
586 BEGIN
587 -- Set MOAC security
588 MO_GLOBAL.init('CE');
589
590 -- clean up the GT table
591 delete ce_security_profiles_gt;
592
593 insert into ce_security_profiles_gt
594 (organization_type,
595 organization_id,
596 name)
597 ( select organization_type,
598 organization_id,
599 name
600 from ce_security_profiles_v
601 union
602 select 'BUSINESS_GROUP',
603 org.BUSINESS_GROUP_ID,
604 org.NAME
605 from hr_organization_information oi,
606 hr_all_organization_units org
607 WHERE oi.organization_id = org.organization_id
608 and oi.org_information_context = 'CLASS'
609 AND oi.org_information1 = 'HR_BG');
610 EXCEPTION
611 WHEN OTHERS THEN
612 null;
613 END init_security_baui;
614
615 /* begin code added for the bug 7125240 */
616 Begin
617 g_debug := FND_PROFILE.value('CE_DEBUG') ;
618
619 end CEP_STANDARD;