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