[Home] [Help]
PACKAGE BODY: APPS.HR_MULTI_TENANT_INSTALLER
Source
1 PACKAGE BODY hr_multi_tenant_installer AS
2 /* $Header: pemtstup.pkb 120.0.12010000.30 2009/01/23 14:59:15 ppentapa noship $ */
3 --
4 -- Variable declarations
5 --
6 TYPE mt_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
7 l_dummy NUMBER;
8 l_header DBMS_SQL.VARCHAR2S;
9 l_body DBMS_SQL.VARCHAR2S;
10 l_header_line NUMBER:=0;
11 l_body_line NUMBER:=0;
12 l_package_name VARCHAR2(255);
13 l_csr_sql NUMBER;
14 l_rows NUMBER;
15 sqlstmt VARCHAR2(4000);
16 installed_flag BOOLEAN := TRUE;
17 allowed_table mt_table;
18 predicate_table mt_table;
19 no_policy_table mt_table;
20
21 -----------------------------------------------------------------------
22 -----------------------------------------------------------------------
23 -- PRIVATE FUNCTIONS
24 -----------------------------------------------------------------------
25 -----------------------------------------------------------------------
26
27 --
28 --------------------------------------------------------------------
29 --< GET_SCHEMA >----------------------------------------------------
30 --------------------------------------------------------------------
31 --
32 -- Description:
33 -- This function will return the oracle user name for the
34 -- application short name passed as parameter.
35 --
36 --
37
38 FUNCTION get_schema
39 (p_app_short_name IN VARCHAR2
40 )
41 RETURN VARCHAR2
42 IS
43 --
44 l_schema_name VARCHAR2(30);
45 --
46
47 BEGIN
48 --
49 -- Select the oracle user name for the application
50 -- short name passed as parameter.
51 --
52 SELECT fou.oracle_username
53 INTO l_schema_name
54 FROM fnd_oracle_userid fou
55 ,fnd_product_installations fpi
56 ,fnd_application fap
57 WHERE fou.oracle_id = fpi.oracle_id
58 AND fpi.application_id = fap.application_id
59 AND fap.application_short_name = p_app_short_name;
60 RETURN l_schema_name;
61
62 END get_schema;
63
64 --
65 --------------------------------------------------------------------
66 --< GET_SCHEMA_BY_ORACLEID >----------------------------------------
67 --------------------------------------------------------------------
68 --
69 -- Description:
70 -- This function accepts the oracle id as parameter
71 -- and returns the corresponding oracle user name.
72 --
73 -- e.g. get_schema(900) returns 'APPS' as the schema name.
74 --
75
76 FUNCTION get_schema_by_oracleid
77 (p_oracle_id IN NUMBER)
78 RETURN VARCHAR2
79 IS
80 --
81 l_schema_name VARCHAR2(30);
82 --
83 BEGIN
84 --
85 -- Select the oracle user name for the
86 -- oracle id passed as function parameter
87 --
88
89 SELECT fou.oracle_username
90 INTO l_schema_name
91 FROM fnd_oracle_userid fou
92 WHERE fou.oracle_id = p_oracle_id;
93 RETURN l_schema_name;
94
95 END get_schema_by_oracleid;
96
97 --
98 --------------------------------------------------------------------
99 --< ADD_BODY_LINE >-------------------------------------------------
100 --------------------------------------------------------------------
101 --
102 -- Description:
103 -- This procedure accepts text as parameter
104 -- and stores the text. This is required for the dynamic
105 -- generation of the the package body.
106 --
107
108 PROCEDURE add_body_line
109 (text IN VARCHAR2)
110 IS
111 BEGIN
112 --
113 -- Store the text in the array
114 --
115 l_body(l_body_line):=text||'
116 ';
117 l_body_line:=l_body_line+1;
118 END;
119
120 --
121 --------------------------------------------------------------------
122 --< ADD_HEADER_LINE >-----------------------------------------------
123 --------------------------------------------------------------------
124 --
125 -- Description:
126 -- This procedure accepts text as parameter
127 -- and stores the text. This is required for the dynamic
128 -- generation of the the package header.
129 --
130
131 PROCEDURE add_header_line
132 (text IN VARCHAR2)
133 IS
134 BEGIN
135 --
136 -- Store the text in the array
137 --
138 l_header(l_header_line):=text||'
139 ';
140 l_header_line:= l_header_line+1;
141 END;
142
143 --
144 -----------------------------------------------------------------------
145 -----------------------------------------------------------------------
146 -- PUBLIC FUNCTIONS
147 -----------------------------------------------------------------------
148 -----------------------------------------------------------------------
149 --
150 -----------------------------------------------------------------------
151 --< INSTALL_HR_MULTI_TENANT >------------------------------------------
152 -----------------------------------------------------------------------
153 --
154 -- Description:
155 -- This procedure is called by the concurrent program
156 -- 'Enable Multiple Tenant Security Process'. If valid profile
157 -- is not set for HR_ENABLE_MULTI_TENANCY it returns. If
158 -- the multi tenancy solution is not already installed then
159 -- it installs the solution. Irrespective of the soultion being installed
160 -- or not, it generates the package 'HR_MULTI_TENANCY_PKG'
161 -- and 'HR_MULTI_TENANT_INSTALL'.
162 --
163
164
165 PROCEDURE install_hr_multi_tenant (errbuf OUT NOCOPY VARCHAR2
166 ,retcode OUT NOCOPY NUMBER) IS
167
168 BEGIN
169 --
170 -- Check If valid value is set for profile is HR_ENABLE_MULTI_TENANCY
171 -- If value is null or N then this is not a multi tenancy should not be
172 -- installed.
173 --
174
175 IF fnd_profile.value('HR_ENABLE_MULTI_TENANCY') IS NULL
176 OR fnd_profile.value('HR_ENABLE_MULTI_TENANCY') = 'N' THEN
177
178 fnd_file.put_line(fnd_file.output
179 ,'Set HR Enable Multi Tenancy System profile and submit the request');
180
181 fnd_file.put_line(fnd_file.log
182 ,'Set HR Enable Multi Tenancy System profile and submit the request');
183
184 RETURN;
185 END IF;
186
187 --
188 -- Check IF hr_ENTERPRISE_POLICY is already installed in the system.
189 -- It checks if seeded user 'C::ENT' exists for the HR_ENTERPRISE_POLICY.
190 -- If exists, the soultion had already been installed.
191 -- This is to ensure that the concurrent program is rerunnable.
192 --
193
194 BEGIN
195 sqlstmt := 'DECLARE policy_exists NUMBER;
196 BEGIN
197 SELECT 1
198 INTO policy_exists
199 FROM all_sa_users
200 WHERE policy_name=''HR_ENTERPRISE_POLICY''
201 AND user_name = ''C::ENT'';
202 END;';
203 EXECUTE IMMEDIATE sqlstmt;
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 --
207 -- The default value of the installed flag is TRUE.
208 --
209 installed_flag := FALSE;
210 END;
211
212 --
213 -- If it is a fresh installation the create the seeded levels and
214 -- groups. Also grant the various user accesses.
215 --
216
217 IF installed_flag = FALSE THEN
218 --
219 -- Add the text that is to be executed dynamically
220 --
221 add_body_line('DECLARE');
222 add_body_line('dummy NUMBER;');
223 add_body_line('BEGIN');
224 -- add_body_line('sa_sysdba.create_policy(''HR_ENTERPRISE_POLICY'',''HR_ENTERPRISE'',''READ_CONTROL,LABEL_DEFAULT,HIDE'');');
225 add_body_line('sa_components.create_level(''HR_ENTERPRISE_POLICY'',0,''C'',''Confidential'');');
226 add_body_line('sa_components.create_group(''HR_ENTERPRISE_POLICY'',0,''ENT'',''HR Enterprise Default Group'');');
227 add_body_line('sa_user_admin.set_user_privs(''HR_ENTERPRISE_POLICY'',''' || get_schema_by_oracleid(900) || ''',''PROFILE_ACCESS'');');
228 add_body_line('sa_user_admin.set_user_privs(''HR_ENTERPRISE_POLICY'',''' || get_schema('PER') || ''',''PROFILE_ACCESS'');');
229 add_body_line('SELECT to_data_label(''HR_ENTERPRISE_POLICY'',''C::ENT'') INTO dummy FROM dual;');
230 add_body_line('sa_user_admin.set_user_labels (''HR_ENTERPRISE_POLICY'',''' || get_schema_by_oracleid(900) || ''',''C::ENT'');');
231 add_body_line('sa_user_admin.set_user_labels (''HR_ENTERPRISE_POLICY'',''' || get_schema('PER') || ''',''C::ENT'');');
232 add_body_line('sa_user_admin.set_user_labels (''HR_ENTERPRISE_POLICY'',''C::ENT'',''C::ENT'');');
233 add_body_line('END;');
234
235 --
236 -- Execute the dynamic code.
237 --
238
239 l_csr_sql := dbms_sql.open_cursor;
240 dbms_sql.parse( l_csr_sql, l_body,0,l_body_line-1,FALSE, dbms_sql.v7 );
241 l_rows := dbms_sql.EXECUTE( l_csr_sql );
242 dbms_sql.close_cursor( l_csr_sql );
243
244 --
245 -- Reinitialize the variables.
246 --
247
248 l_body_line :=0;
249 l_header_line :=0;
250
251 END IF;
252 --
253 -- Create or replace the hr_multi_tenancy_pkg package
254 --
255
256 l_package_name := 'hr_multi_tenancy_pkg';
257 add_header_line('CREATE OR REPLACE PACKAGE '||l_package_name||' AS ');
258 add_body_line('CREATE OR REPLACE PACKAGE BODY '||l_package_name||' AS ');
259 add_header_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
260 add_header_line('/'||'* $Header: pemtstup.pkb 120.0.12010000.20 2008/11/24 10:27:27 bchakrab noship*'||'/');
261 add_body_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
262 add_body_line('/'||'* $Header: pemtstup.pkb *'||'/');
263 add_header_line('--');
264 add_header_line('-- Name');
265 add_header_line('-- is_multi_tenant_system');
266 add_header_line('--');
267 add_header_line('-- Purpose');
268 add_header_line('-- All PL/SQL Code modified for enterprise should be wrapped IN a call ');
269 add_header_line('-- to this method.');
270 add_header_line('-- Called From');
271 add_header_line('-- 1. hr_signon ');
272 add_header_line('-- Arguments');
273 add_header_line('-- None.');
274 add_header_line('--');
275 add_header_line(' ');
276 add_header_line(' FUNCTION is_multi_tenant_system RETURN boolean;');
277 add_header_line('--');
278 add_header_line('-- Name');
279 add_header_line('-- get_system_model');
280 add_header_line('--');
281 add_header_line('-- Purpose');
282 add_header_line('-- The function returns the System model as P/B or null');
283 add_header_line('-- ');
284 add_header_line('-- Called From');
285 add_header_line('-- 1. Assign Security Profiles form');
286 add_header_line('-- Arguments');
287 add_header_line('-- None.');
288 add_header_line('--');
289 add_header_line(' ');
290 add_header_line(' FUNCTION get_system_model RETURN varchar2;');
291 add_header_line('');
292 add_header_line('PROCEDURE insert_hr_name_formats ( p_enterprise_code IN varchar2);');
293 add_header_line('');
294 add_header_line('--');
295 add_header_line('-- Name');
296 add_header_line('-- set_context');
297 add_header_line('--');
298 add_header_line('-- Purpose');
299 add_header_line('-- The procedure sets/resets OLS enterprise context.');
300 add_header_line('-- ');
301 add_header_line('-- Called From');
302 add_header_line('-- 1. hr_signon with argument null.');
303 add_header_line('-- 2. HRMultiTenancyHelper.java(#resetContext) with argument ENT.');
304 add_header_line('-- Arguments');
305 add_header_line('-- p_context_value => context label to set');
306 add_header_line('--');
307 add_header_line(' ');
308 add_header_line(' PROCEDURE set_context (p_context_value IN VARCHAR2);');
309 add_header_line('');
310 add_header_line(' --');
311 add_header_line(' --------------------------------------------------------------------');
312 add_header_line(' --< set_context_for_person >----------------------------------------');
313 add_header_line(' --------------------------------------------------------------------');
314 add_header_line(' --');
315 add_header_line(' -- Description:');
316 add_header_line(' -- This is a public procedure to set the appropriate Context value');
317 add_header_line(' -- for a person');
318 add_header_line(' --');
319 add_header_line(' PROCEDURE set_context_for_person (p_person_id IN NUMBER);');
320 add_header_line('');
321 add_header_line(' --');
322 add_header_line(' --------------------------------------------------------------------');
323 add_header_line(' --< set_context_for_enterprise >----------------------------------------');
324 add_header_line(' --------------------------------------------------------------------');
325 add_header_line(' --');
326 add_header_line(' -- Description:');
327 add_header_line(' -- This is a public procedure to set the appropriate Context value');
328 add_header_line(' -- for a given enterprise short code');
329 add_header_line(' --');
330 add_header_line(' PROCEDURE set_context_for_enterprise (p_enterprise_short_code IN VARCHAR2);');
331 add_header_line('');
332 add_header_line('--');
333 add_header_line('-- Name');
334 add_header_line('-- get_corporate_branding');
335 add_header_line('--');
336 add_header_line('-- Purpose');
337 add_header_line('-- Gets the corporate branding for the passed/current enterprise.');
338 add_header_line('-- ');
339 add_header_line('-- Called From');
340 add_header_line('-- 1. HRApplicationModuleImpl.java without argument');
341 add_header_line('-- 2. MTHomeAMImpl.java with argument.');
342 add_header_line('-- Arguments');
343 add_header_line('-- p_organization_id => organization id of the current enterprise');
344 add_header_line('--');
345 add_header_line(' ');
346 add_header_line(' FUNCTION get_corporate_branding (p_organization_id IN VARCHAR2 default null) RETURN VARCHAR2;');
347 add_header_line('');
348 add_header_line('--');
349 add_header_line('-- Name');
350 add_header_line('-- get_bus_grp_from_sec_grp');
351 add_header_line('--');
352 add_header_line('-- Purpose');
353 add_header_line('-- Called from HR_SIGNON to RETURN the business group corresponding');
354 add_header_line('-- to the enterprise security group for buisness group initialization. ');
355 add_header_line('--');
356 add_header_line('-- Called From');
357 add_header_line('-- 1. HR_SIGNON ');
358 add_header_line('-- Arguments');
359 add_header_line('-- p_security_group_id => security group id of the current enterprise');
360 add_header_line('--');
361 add_header_line(' FUNCTION get_bus_grp_from_sec_grp (p_security_group_id IN NUMBER) RETURN NUMBER;');
362 add_header_line('');
363 add_header_line('--');
364 add_header_line('-- Name');
365 add_header_line('-- set_security_group_id');
366 add_header_line('--');
367 add_header_line('-- Purpose');
368 add_header_line('-- Called from HR_API to set proper security group. ');
369 add_header_line('--');
370 add_header_line('-- Called From');
371 add_header_line('-- 1. HR_API ');
372 add_header_line('-- Arguments');
373 add_header_line('-- p_security_group_id => security group id of the current enterprise');
374 add_header_line('--');
375 add_header_line(' PROCEDURE set_security_group_id (p_security_group_id IN NUMBER);');
376 add_header_line('');
377 add_header_line('--');
378 add_header_line('-- Name');
379 add_header_line('-- get_org_id_for_person');
380 add_header_line('--');
381 add_header_line('-- Purpose');
382 add_header_line('-- Used IN SSHR to derive the security group from person ');
383 add_header_line('-- i.e. (Notifications/Workflow)');
384 add_header_line('--');
385 add_header_line('-- Arguments');
386 add_header_line('-- p_person_id => id of the selected person');
387 add_header_line('-- ');
388 add_header_line('');
389 add_header_line(' FUNCTION get_org_id_for_person (p_person_id IN NUMBER) RETURN NUMBER;');
390 add_header_line('');
391 add_header_line('--');
392 add_header_line('-- Name');
393 add_header_line('-- get_org_id_for_person');
394 add_header_line('--');
395 add_header_line('-- Purpose');
396 add_header_line('-- Used IN SSHR New Hire flow ');
397 add_header_line('-- Gets the HR Enterprise Organization ID IN the passed business group belonging to');
398 add_header_line('-- to the same enterprise as the passed HR Person.');
399 add_header_line('--');
400 add_header_line('-- Arguments');
401 add_header_line('-- p_person_id => id of the selected person');
402 add_header_line('-- p_business_group_id => id of the selected business group.');
403 add_header_line('-- ');
404 add_header_line(' FUNCTION get_org_id_for_person (p_person_id IN NUMBER');
405 add_header_line(' ,p_business_group_id IN NUMBER) RETURN NUMBER;');
406 add_header_line('');
407 add_header_line('--');
408 add_header_line('-- Name');
409 add_header_line('-- get_label_from_bg');
410 add_header_line('--');
411 add_header_line('-- Purpose');
412 add_header_line('-- Called From PerAppModuleHelper ');
413 add_header_line('-- to get label from BG');
414 add_header_line('--');
415 add_header_line('-- Arguments');
416 add_header_line('-- p_business_group_id => id of the selected business group.');
417 add_header_line('-- ');
418 add_header_line(' FUNCTION get_label_from_bg (p_business_group_id IN NUMBER) RETURN VARCHAR2;');
419 add_header_line('');
420 add_header_line(' --');
421 add_header_line(' -- Name');
422 add_header_line(' -- get_org_id_from_bg_and_sl');
423 add_header_line(' --');
424 add_header_line(' -- Purpose');
425 add_header_line(' -- Called From PerAppModuleHelper');
426 add_header_line(' -- to get orgid from BG and security label');
427 add_header_line(' --');
428 add_header_line(' -- Arguments');
429 add_header_line(' -- p_business_group_id => id of the selected business group.');
430 add_header_line(' -- p_security_label => security label.');
431 add_header_line(' --');
432 add_header_line(' FUNCTION get_org_id_from_bg_and_sl (p_business_group_id IN NUMBER');
433 add_header_line(' ,p_security_label IN VARCHAR2) RETURN NUMBER;');
434 add_header_line('');
435 add_header_line('--');
436 add_header_line('-- Name');
437 add_header_line('-- is_valid_sec_group');
438 add_header_line('--');
439 add_header_line('-- Purpose');
440 add_header_line('-- Returns Y/N if it is a valid security group');
441 add_header_line('--');
442 add_header_line('-- Arguments');
443 add_header_line('-- p_security_group_id => security group id');
444 add_header_line('-- p_business_group_id => id of the selected business group.');
445 add_header_line('--');
446 add_header_line(' FUNCTION is_valid_sec_group (p_security_group_id IN NUMBER');
447 add_header_line(' ,p_business_group_id IN NUMBER) RETURN VARCHAR2;');
448 add_header_line('');
449 add_header_line(' --');
450 add_header_line(' -- Name');
451 add_header_line(' -- add_language');
452 add_header_line(' --');
453 add_header_line(' -- Purpose');
454 add_header_line(' -- Updates TL table');
455 add_header_line(' --');
456 add_header_line(' -- Arguments');
457 add_header_line(' -- None');
458 add_header_line(' --');
459 add_header_line(' PROCEDURE add_language;');
460 add_header_line('');
461
462 add_body_line('/*FUNCTION get_label_from_secgrp');
463 add_body_line(' (p_security_group_id IN NUMBER) RETURN VARCHAR2 AS');
464 add_body_line(' l_security_group_key VARCHAR2(30);');
465 add_body_line(' l_enterprise_label VARCHAR2(4000);');
469 add_body_line(' INTO l_security_group_key');
466 add_body_line(' BEGIN');
467 add_body_line(' BEGIN');
468 add_body_line(' SELECT security_group_key');
470 add_body_line(' FROM fnd_security_groups');
471 add_body_line(' WHERE security_group_id = p_security_group_id;');
472 add_body_line(' EXCEPTION');
473 add_body_line(' WHEN no_data_found THEN');
474 add_body_line(' l_security_group_key := NULL;');
475 add_body_line(' WHEN too_many_rows THEN');
476 add_body_line(' l_security_group_key := NULL;');
477 add_body_line(' END;');
478 add_body_line('');
479 add_body_line(' BEGIN');
480 add_body_line(' SELECT enterprise_label');
481 add_body_line(' INTO l_enterprise_label');
482 add_body_line(' FROM per_ent_security_groups');
483 add_body_line(' WHERE to_char(organization_id) = l_security_group_key');
484 add_body_line(' AND enabled_flag = ''Y'';');
485 add_body_line(' EXCEPTION');
486 add_body_line(' WHEN no_data_found THEN');
487 add_body_line(' l_enterprise_label := ''ENT'';');
488 add_body_line(' END;');
489 add_body_line('');
490 add_body_line(' IF instr(l_enterprise_label, ''C::'') = 0 THEN');
491 add_body_line(' l_enterprise_label := ''C::'' || l_enterprise_label;');
492 add_body_line(' END IF;');
493 add_body_line('');
494 add_body_line(' RETURN l_enterprise_label;');
495 add_body_line(' END get_label_from_secgrp;*/');
496 add_body_line('');
497 add_body_line('-----------------------------------------------------------------------');
498 add_body_line('-----------------------------------------------------------------------');
499 add_body_line('-- PRIVATE FUNCTIONS');
500 add_body_line('-----------------------------------------------------------------------');
501 add_body_line('-----------------------------------------------------------------------');
502 add_body_line('');
503 add_body_line('--');
504 add_body_line('--------------------------------------------------------------------');
505 add_body_line('--< get_label_from_bg >-----------------------------------------------------');
506 add_body_line('--------------------------------------------------------------------');
507 add_body_line('--');
508 add_body_line('-- Description:');
509 add_body_line('-- This function is Called From PerAppModuleHelper ');
510 add_body_line('-- to get label from BG');
511 add_body_line('--');
512 add_body_line('-- Arguments');
513 add_body_line('-- p_business_group_id => id of the selected business group.');
514 add_body_line('--');
515 add_body_line('');
516 add_body_line('FUNCTION get_label_from_bg (p_business_group_id IN NUMBER) RETURN VARCHAR2 IS');
517 add_body_line(' l_enterprise_label VARCHAR2(4000);');
518 add_body_line('');
519 add_body_line(' CURSOR label_bg (p_business_group_id NUMBER) IS');
520 add_body_line(' SELECT enterprise_label');
521 add_body_line(' FROM per_ent_security_groups');
522 add_body_line(' WHERE business_group_id = p_business_group_id');
523 add_body_line(' AND enabled_flag = ''Y'';');
524 add_body_line('');
525 add_body_line('BEGIN');
526 add_body_line(' OPEN label_bg (p_business_group_id => p_business_group_id);');
527 add_body_line(' FETCH label_bg INTO l_enterprise_label;');
528 add_body_line(' IF label_bg%notfound THEN');
529 add_body_line(' l_enterprise_label := ''ENT'';');
530 add_body_line(' END IF;');
531 add_body_line(' RETURN ''C::'' || l_enterprise_label;');
532 add_body_line('END get_label_from_bg;');
533 add_body_line('');
534 add_body_line(' /*FUNCTION get_label_from_session RETURN VARCHAR2 IS');
535 add_body_line(' l_security_group_id NUMBER;');
536 add_body_line(' BEGIN');
537 add_body_line(' l_security_group_id := fnd_global.security_group_id;');
538 add_body_line(' RETURN get_label_from_secgrp');
539 add_body_line(' (p_security_group_id => l_security_group_id);');
540 add_body_line(' END get_label_from_session;*/');
541 add_body_line('');
542 add_body_line('--');
543 add_body_line('--------------------------------------------------------------------');
544 add_body_line('--< init_profiles >-----------------------------------------------------');
545 add_body_line('--------------------------------------------------------------------');
546 add_body_line('--');
547 add_body_line('-- Description:');
548 add_body_line('-- This is a private procedure that is used ');
549 add_body_line('-- to initialize the profile values for the enterprise');
550 add_body_line('--');
551 add_body_line('--');
552 add_body_line('PROCEDURE init_profiles AS');
553 add_body_line(' CURSOR csr_profile_value IS');
554 add_body_line(' SELECT description, meaning');
555 add_body_line(' FROM fnd_lookup_values');
556 add_body_line(' WHERE lookup_type = ''PER_MT_VALUES'' ');
557 add_body_line(' AND enabled_flag = ''Y'' ');
558 add_body_line(' AND language = userenv(''LANG'') ');
559 add_body_line(' AND view_application_id = 3 ');
560 add_body_line(' AND security_group_id = fnd_global.lookup_security_group(lookup_type');
561 add_body_line(' ,view_application_id);');
562 add_body_line('BEGIN');
563 add_body_line(' FOR rec IN csr_profile_value LOOP');
564 add_body_line(' fnd_profile.put(rec.meaning, rec.description);');
565 add_body_line(' END LOOP;');
566 add_body_line('END init_profiles;');
567 add_body_line('');
568 add_body_line('--');
569 add_body_line('--------------------------------------------------------------------');
570 add_body_line('--< init_context >-----------------------------------------------------');
574 add_body_line('-- This is a private procedure that is used ');
571 add_body_line('--------------------------------------------------------------------');
572 add_body_line('--');
573 add_body_line('-- Description:');
575 add_body_line('-- to initialize the OLS context');
576 add_body_line('--');
577 add_body_line('--');
578 add_body_line('');
579 add_body_line('PROCEDURE init_context (p_context IN VARCHAR2) AS');
580 add_body_line(' l_current_context VARCHAR2 (4000);');
581 add_body_line(' l_session_context VARCHAR2 (4000);');
582 add_body_line('BEGIN');
583 add_body_line('--');
584 add_body_line('-- If it is not a multi tenant solution then do not do anything ');
585 add_body_line('--');
586 add_body_line(' IF NOT is_multi_tenant_system THEN');
587 add_body_line(' RETURN;');
588 add_body_line(' END IF;');
589 add_body_line('');
590 add_body_line(' l_current_context := p_context;');
591 add_body_line('--');
592 add_body_line('-- This is to support STANDARD ');
593 add_body_line('--');
594 add_body_line(' IF l_current_context IS NULL THEN');
595 add_body_line(' l_current_context := ''C::ENT'';');
596 add_body_line(' END IF;');
597 add_body_line('');
598 add_body_line(' IF instr(l_current_context, ''C::'') = 0 THEN');
599 add_body_line(' l_current_context := ''C::'' || l_current_context;');
600 add_body_line(' END IF;');
601 add_body_line('');
602 add_body_line(' l_session_context := sa_session.row_label(''HR_ENTERPRISE_POLICY'');');
603 add_body_line('');
604 add_body_line('--');
605 add_body_line('-- Only if context is different from the present context ');
606 add_body_line('-- do the processing');
607 add_body_line('--');
608 add_body_line(' IF l_session_context IS NULL OR ');
609 add_body_line(' l_session_context <> l_current_context THEN');
610 add_body_line(' BEGIN');
611 add_body_line(' sa_session.set_access_profile(''HR_ENTERPRISE_POLICY'', l_current_context);');
612 add_body_line(' IF l_current_context <> ''C::ENT'' THEN');
613 add_body_line(' init_profiles;');
614 add_body_line(' END IF;');
615 add_body_line(' EXCEPTION');
616 add_body_line(' WHEN others THEN');
617 add_body_line('--');
618 add_body_line('-- This is an error condition');
619 add_body_line('--');
620 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
621 add_body_line(' fnd_message.set_token (''ERRMSG'' , sqlerrm);');
622 add_body_line(' fnd_message.raise_error;');
623 add_body_line(' -- Temporary Fix : Fallback code added');
624 add_body_line(' -- sa_session.set_access_profile(''HR_ENTERPRISE_POLICY'', ''C::ENT'');');
625 add_body_line(' END;');
626 add_body_line(' END IF;');
627 add_body_line('END init_context;');
628 add_body_line('');
629 add_body_line(' --');
630 add_body_line(' --------------------------------------------------------------------');
631 add_body_line(' --< init_context_from_secgrp >-----------------------------------------------------');
632 add_body_line(' --------------------------------------------------------------------');
633 add_body_line(' --');
634 add_body_line(' -- Description:');
635 add_body_line(' -- This is a private procedure that is used ');
636 add_body_line(' -- to initialize the OLS context from the security group id passed ');
637 add_body_line(' -- as parameter.');
638 add_body_line(' -- Arguments');
639 add_body_line(' -- p_security_group_id => id of the current security group');
640 add_body_line(' --');
641 add_body_line('');
642 add_body_line('PROCEDURE init_context_from_secgrp (p_security_group_id IN NUMBER) AS');
643 add_body_line(' l_security_label VARCHAR2(4000);');
644 add_body_line('BEGIN');
645 add_body_line(' IF NOT is_multi_tenant_system THEN ');
646 add_body_line(' RETURN;');
647 add_body_line(' END IF;');
648 add_body_line(' init_context (p_context => ''ENT'');');
649 add_body_line(' BEGIN');
650 add_body_line(' SELECT enterprise_label');
651 add_body_line(' INTO l_security_label');
652 add_body_line(' FROM per_ent_security_groups');
653 add_body_line(' WHERE security_group_id = p_security_group_id');
654 add_body_line(' AND enabled_flag = ''Y'';');
655 add_body_line(' EXCEPTION');
656 add_body_line(' WHEN no_data_found THEN');
657 add_body_line(' -- This can happen under three conditions.');
658 add_body_line(' -- 1. The security group corresponds to a business group.');
659 add_body_line(' -- 2. The security group IS NOT related to HR setup.');
660 add_body_line(' -- 3. The enterprise security group IS disabled.');
661 add_body_line(' ');
662 add_body_line(' -- For case 3 we need to raise error');
663 add_body_line(' BEGIN');
664 add_body_line(' SELECT enterprise_label');
665 add_body_line(' INTO l_security_label');
666 add_body_line(' FROM per_ent_security_groups');
667 add_body_line(' WHERE security_group_id = p_security_group_id;');
668 add_body_line(' -- The security group IS disabled.');
669 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
670 add_body_line(' fnd_message.set_token (''ERRMSG'' , ''Security Group '' || p_security_group_id || '' IS disabled.'');');
671 add_body_line(' fnd_message.raise_error;');
672 add_body_line(' EXCEPTION');
673 add_body_line(' WHEN no_data_found THEN');
674 add_body_line(' RETURN;');
675 add_body_line(' END;');
676 add_body_line(' WHEN too_many_rows THEN');
680 add_body_line(' END;');
677 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
678 add_body_line(' fnd_message.set_token (''ERRMSG'' , sqlerrm);');
679 add_body_line(' fnd_message.raise_error;');
681 add_body_line('');
682 add_body_line(' init_context (p_context => l_security_label);');
683 add_body_line('END init_context_from_secgrp;');
684 add_body_line('');
685 add_body_line('--');
686 add_body_line('--------------------------------------------------------------------');
687 add_body_line('--< init_context_from_apps_context >--------------------------------');
688 add_body_line('--------------------------------------------------------------------');
689 add_body_line('--');
690 add_body_line('-- Description:');
691 add_body_line('-- This is a private procedure that is used ');
692 add_body_line('-- to initialize the OLS context from the apps context using fnd_global ');
693 add_body_line('-- security group id.');
694 add_body_line('--');
695 add_body_line('');
696 add_body_line('PROCEDURE init_context_from_apps_context AS');
697 add_body_line(' l_security_group_id NUMBER;');
698 add_body_line('BEGIN');
699 add_body_line(' l_security_group_id := fnd_global.security_group_id;');
700 add_body_line(' init_context_from_secgrp (p_security_group_id => l_security_group_id);');
701 add_body_line('END init_context_from_apps_context;');
702 add_body_line('');
703 add_body_line('--');
704 add_body_line('--------------------------------------------------------------------');
705 add_body_line('--< is_multi_tenant_system >--------------------------------------');
706 add_body_line('--------------------------------------------------------------------');
707 add_body_line('--');
708 add_body_line('-- Description:');
709 add_body_line('-- This is a public function that is used as a wrapper for all calls');
710 add_body_line('-- In normal instance with HR_ENABLE_MULTI_TENANCY not set, the function ');
711 add_body_line('-- returns false. In this case, normal functionality should continued.');
712 add_body_line('-- In case of a multi tenanat system, the function returns false and');
713 add_body_line('-- the multi tenancy specific solution comes into play.');
714 add_body_line('--');
715 add_body_line('FUNCTION is_multi_tenant_system RETURN BOOLEAN AS');
716 add_body_line(' l_profile_value VARCHAR2 (255);');
717 add_body_line('BEGIN');
718 add_body_line(' l_profile_value := fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'');');
719 add_body_line('');
720 add_body_line(' IF l_profile_value = ''P'' OR');
721 add_body_line(' l_profile_value = ''B'' THEN');
722 add_body_line(' RETURN true;');
723 add_body_line(' END IF;');
724 add_body_line(' RETURN false;');
725 add_body_line('END is_multi_tenant_system;');
726 add_body_line('');
727 add_body_line('--');
728 add_body_line('--------------------------------------------------------------------');
729 add_body_line('--< get_system_model >----------------------------------------------');
730 add_body_line('--------------------------------------------------------------------');
731 add_body_line('--');
732 add_body_line('-- Description:');
733 add_body_line('-- This is a public function that returns whether the system is a BPO');
734 add_body_line('-- Model 1 or PEO Model2 . In case of Model 1 the profile value of ');
735 add_body_line('-- HR_ENABLE_MULTI_TENANCY should be set B while for model 2, the value ');
736 add_body_line('-- should be P. For non multi tenant systems it must be null.');
737 add_body_line('--');
738 add_body_line(' FUNCTION get_system_model RETURN VARCHAR2 AS');
739 add_body_line(' l_system_model VARCHAR2(255);');
740 add_body_line(' BEGIN');
741 add_body_line(' l_system_model := fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'');');
742 add_body_line(' ');
743 add_body_line(' IF l_system_model IS NULL THEN');
744 add_body_line(' RETURN ''N'';');
745 add_body_line(' ELSE');
746 add_body_line(' RETURN l_system_model;');
747 add_body_line(' END IF;');
748 add_body_line(' ');
749 add_body_line(' END get_system_model;');
750 add_body_line('');
751 add_body_line('PROCEDURE insert_hr_name_formats ( p_enterprise_code IN varchar2)');
752 add_body_line(' AS');
753 add_body_line(' TYPE hr_name_format_rec IS');
754 add_body_line(' RECORD (format_name VARCHAR2(80)');
755 add_body_line(' ,legislation_code VARCHAR2(30)');
756 add_body_line(' ,user_format_choice VARCHAR2(1)');
757 add_body_line(' ,format_mask VARCHAR2(250)');
758 add_body_line(' ,object_version_number NUMBER(22)');
759 add_body_line(' );');
760 add_body_line(' TYPE hr_name_format_tab_type IS TABLE OF hr_name_format_rec INDEX BY BINARY_INTEGER;');
761 add_body_line(' hr_name_format_tab hr_name_format_tab_type;');
762 add_body_line(' current_hr_name_format hr_name_format_rec;');
763 add_body_line(' max_id number;');
764 add_body_line(' next_id number ;');
765 add_body_line(' ');
766 add_body_line(' CURSOR c_hr_name_format ');
767 add_body_line(' IS');
768 add_body_line(' SELECT format_name');
769 add_body_line(' ,legislation_code');
770 add_body_line(' ,user_format_choice');
771 add_body_line(' ,format_mask');
772 add_body_line(' ,object_version_number');
773 add_body_line(' FROM hr_name_formats;');
774 add_body_line('');
775 add_body_line(' BEGIN');
776 add_body_line(' Open c_hr_name_format;');
777 add_body_line(' Fetch c_hr_name_format bulk collect into hr_name_format_tab;');
778 add_body_line(' close c_hr_name_format;');
779 add_body_line(' ');
783 add_body_line(' -- This is done because seeded data uses non sequential format_id');
780 add_body_line(' for i in 1 .. hr_name_format_tab.count loop');
781 add_body_line(' current_hr_name_format := hr_name_format_tab(i);');
782 add_body_line(' --');
784 add_body_line(' -- And we want smooth insertions without any exceptions due to colliding');
785 add_body_line(' -- sequence numbers.');
786 add_body_line(' --');
787 add_body_line(' select max(name_format_id)');
788 add_body_line(' into max_id');
789 add_body_line(' from hr_name_formats;');
790 add_body_line(' select hr_name_formats_s.nextval into next_id from dual;');
791 add_body_line(' --');
792 add_body_line(' -- This is basically setting the sequence to proper index value');
793 add_body_line(' --');
794 add_body_line(' while(max_id >= next_id) loop');
795 add_body_line(' select hr_name_formats_s.nextval into next_id from dual;');
796 add_body_line(' end loop;');
797 add_body_line(' ');
798 add_body_line('execute immediate ''insert into hr_name_formats (name_format_id');
799 add_body_line(' ,format_name');
800 add_body_line(' ,legislation_code');
801 add_body_line(' ,user_format_choice');
802 add_body_line(' ,format_mask');
803 add_body_line(' ,OBJECT_VERSION_NUMBER');
804 add_body_line(' ,hr_enterprise)');
805 add_body_line(' values(:1');
806 add_body_line(' ,:2');
807 add_body_line(' ,:3');
808 add_body_line(' ,:4');
809 add_body_line(' ,:5');
810 add_body_line(' ,:6');
811 add_body_line(' ,char_to_label(''''HR_ENTERPRISE_POLICY'''',:7))''');
812 add_body_line(' using');
813 add_body_line(' next_id');
814 add_body_line(' ,current_hr_name_format.format_name');
815 add_body_line(' ,current_hr_name_format.legislation_code');
816 add_body_line(' ,current_hr_name_format.user_format_choice');
817 add_body_line(' ,current_hr_name_format.format_mask');
818 add_body_line(' ,current_hr_name_format.OBJECT_VERSION_NUMBER');
819 add_body_line(' ,p_enterprise_code; ');
820 add_body_line(' end loop;');
821 add_body_line(' ');
822 add_body_line(' EXCEPTION');
823 add_body_line(' WHEN OTHERS THEN');
824 add_body_line(' raise;');
825 add_body_line(' --RETURN;');
826 add_body_line(' END; ');
827 add_body_line('');
828 add_body_line('--');
829 add_body_line('--------------------------------------------------------------------');
830 add_body_line('--< set_context >---------------------------------------------------');
831 add_body_line('--------------------------------------------------------------------');
832 add_body_line('--');
833 add_body_line('-- Description:');
834 add_body_line('-- This is a public procedure which is called by HR_SIGNON to set ');
835 add_body_line('-- the appropriate Context value. ');
836 add_body_line('--');
837 add_body_line('');
838 add_body_line('PROCEDURE set_context (p_context_value IN VARCHAR2) AS');
839 add_body_line('sec_flag varchar2(1):= ''N'';');
840 add_body_line('BEGIN');
841 add_body_line('IF get_system_model=''B'' THEN ');
842 add_body_line(' BEGIN');
843 add_body_line(' SELECT ''Y'' ');
844 add_body_line(' INTO sec_flag');
845 add_body_line(' FROM per_ent_security_groups');
846 add_body_line(' WHERE security_group_id = fnd_global.security_group_id;');
847 add_body_line(' EXCEPTION ');
848 add_body_line(' WHEN NO_DATA_FOUND THEN');
849 add_body_line(' sec_flag := ''N'';');
850 add_body_line(' END;');
851 add_body_line(' IF fnd_global.security_group_id = 0 THEN');
852 add_body_line(' sec_flag := ''Y'';');
853 add_body_line(' END IF;');
854 add_body_line(' IF sec_flag = ''N'' THEN');
855 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
856 add_body_line(' fnd_message.set_token (''ERRMSG'' , ''Cannot determine a unique security group within the enterprise.'');');
857 add_body_line(' fnd_message.raise_error;');
858 add_body_line(' END IF;');
859 add_body_line('END IF;');
860 add_body_line('--');
861 add_body_line('-- If null is passed as parameter, set context from apps context.');
862 add_body_line('--');
863 add_body_line(' IF p_context_value IS NULL THEN');
864 add_body_line(' init_context_from_apps_context;');
865 add_body_line(' ELSE');
866 add_body_line('--');
867 add_body_line('-- This code is used to reset context to ENT');
868 add_body_line('--');
869 add_body_line(' IF(p_context_value <> ''ENT'') THEN');
870 add_body_line(' init_context (p_context => ''ENT'');');
871 add_body_line(' END IF;');
872 add_body_line('--');
873 add_body_line('-- This code is used to set the appropriate context');
874 add_body_line('--');
875 add_body_line(' init_context (p_context => p_context_value);');
876 add_body_line(' END IF;');
877 add_body_line('END set_context;');
878 add_body_line('');
879 add_body_line(' --');
880 add_body_line(' --------------------------------------------------------------------');
881 add_body_line(' --< set_context_for_person >----------------------------------------');
882 add_body_line(' --------------------------------------------------------------------');
883 add_body_line(' --');
884 add_body_line(' -- Description:');
885 add_body_line(' -- This is a public procedure to set the appropriate Context value');
886 add_body_line(' -- for a person');
887 add_body_line(' --');
891 add_body_line(' '' FROM PER_ALL_PEOPLE_F '' ||');
888 add_body_line(' PROCEDURE set_context_for_person (p_person_id IN NUMBER) AS');
889 add_body_line(' label_query VARCHAR2(1000) :=');
890 add_body_line(' ''SELECT LABEL_TO_CHAR(HR_ENTERPRISE) ENT_LABEL, BUSINESS_GROUP_ID '' ||');
892 add_body_line(' '' WHERE PERSON_ID = :1 '' ||');
893 add_body_line(' '' AND ROWNUM < 2'';');
894 add_body_line(' l_security_label VARCHAR2(4000);');
895 add_body_line(' l_business_group_id NUMBER;');
896 add_body_line(' l_enabled_flag VARCHAR2(1) := ''N'';');
897 add_body_line(' BEGIN');
898 add_body_line(' INIT_CONTEXT(p_context => ''ENT'');');
899 add_body_line(' BEGIN');
900 add_body_line(' EXECUTE IMMEDIATE label_query');
901 add_body_line(' INTO l_security_label, l_business_group_id');
902 add_body_line(' USING IN p_person_id;');
903 add_body_line(' --If security label for this person is null, set context to ENT');
904 add_body_line(' IF (l_security_label IS NOT NULL AND');
905 add_body_line(' l_security_label = ''C::ENT'' )');
906 add_body_line(' OR l_security_label IS NULL THEN');
907 add_body_line(' set_context(''ENT'');');
908 add_body_line(' ELSE ');
909 add_body_line(' BEGIN');
910 add_body_line(' SELECT enabled_flag');
911 add_body_line(' INTO l_enabled_flag');
912 add_body_line(' FROM per_ent_security_groups');
913 add_body_line(' WHERE business_group_id = l_business_group_id ');
914 add_body_line(' AND enterprise_label = substrb(l_security_label,4);');
915 add_body_line(' EXCEPTION ');
916 add_body_line(' WHEN NO_DATA_FOUND THEN');
917 add_body_line(' l_enabled_flag := ''N'';');
918 add_body_line(' END;');
919 add_body_line(' --If enterprise is enabled, set the context');
920 add_body_line(' IF l_enabled_flag = ''Y'' THEN');
921 add_body_line(' set_context(substrb(l_security_label,4));');
922 add_body_line(' ELSE');
923 add_body_line(' --raise error eneterprise is not enabled');
924 add_body_line(' fnd_message.set_name (''PER'',''PER_ENTERPRISE_NOT_FOUND'');');
925 add_body_line(' fnd_message.raise_error;');
926 add_body_line(' END IF;');
927 add_body_line(' END IF; ');
928 add_body_line(' EXCEPTION');
929 add_body_line(' WHEN no_data_found THEN');
930 add_body_line(' --raise error if person not found');
931 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
932 add_body_line(' fnd_message.set_token (''ERRMSG'' , ''Invalid Person Id'');');
933 add_body_line(' fnd_message.raise_error;');
934 add_body_line(' END;');
935 add_body_line(' END set_context_for_person;');
936 add_body_line('');
937 add_body_line(' --');
938 add_body_line(' --------------------------------------------------------------------');
939 add_body_line(' --< set_context_for_enterprise >----------------------------------------');
940 add_body_line(' --------------------------------------------------------------------');
941 add_body_line(' --');
942 add_body_line(' -- Description:');
943 add_body_line(' -- This is a public procedure to set the appropriate Context value');
944 add_body_line(' -- for a given enterprise short code');
945 add_body_line(' --');
946 add_body_line(' PROCEDURE set_context_for_enterprise (p_enterprise_short_code IN VARCHAR2) AS');
947 add_body_line(' l_security_label VARCHAR2(4000);');
948 add_body_line(' l_exists NUMBER := 0;');
949 add_body_line(' BEGIN');
950 add_body_line(' INIT_CONTEXT(p_context => ''ENT'');');
951 add_body_line(' BEGIN');
952 add_body_line(' SELECT enterprise_label');
953 add_body_line(' INTO l_security_label');
954 add_body_line(' FROM per_enterprises');
955 add_body_line(' WHERE enterprise_short_code = p_enterprise_short_code;');
956 add_body_line(' EXCEPTION');
957 add_body_line(' WHEN NO_DATA_FOUND THEN');
958 add_body_line(' fnd_message.set_name (''PER'',''PER_ENTERPRISE_NOT_FOUND'');');
959 add_body_line(' fnd_message.raise_error;');
960 add_body_line(' END;');
961 add_body_line(' BEGIN');
962 add_body_line(' SELECT count(*)');
963 add_body_line(' INTO l_exists');
964 add_body_line(' FROM per_ent_security_groups');
965 add_body_line(' WHERE enterprise_label = l_security_label');
966 add_body_line(' AND enabled_flag = ''Y'';');
967 add_body_line(' IF l_exists <> 0 THEN');
968 add_body_line(' set_context(l_security_label);');
969 add_body_line(' ELSE');
970 add_body_line(' fnd_message.set_name (''PER'',''PER_ENTERPRISE_NOT_FOUND'');');
971 add_body_line(' fnd_message.raise_error;');
972 add_body_line(' END IF;');
973 add_body_line(' END;');
974 add_body_line(' END set_context_for_enterprise;');
975 add_body_line('--');
976 add_body_line('--------------------------------------------------------------------');
977 add_body_line('--< is_valid_sec_group >--------------------------------------------');
978 add_body_line('--------------------------------------------------------------------');
979 add_body_line('--');
980 add_body_line('-- Description:');
981 add_body_line('-- This is a public function which Returns Y/N if ');
982 add_body_line('-- it is a valid security group');
983 add_body_line('--');
984 add_body_line('-- Arguments');
985 add_body_line('-- p_security_group_id => security group id');
986 add_body_line('-- p_business_group_id => id of the selected business group.');
990 add_body_line(' ,p_business_group_id IN NUMBER) RETURN VARCHAR2 AS');
987 add_body_line('--');
988 add_body_line('');
989 add_body_line('FUNCTION is_valid_sec_group (p_security_group_id IN NUMBER');
991 add_body_line(' ');
992 add_body_line(' l_result VARCHAR2(1);');
993 add_body_line('BEGIN');
994 add_body_line(' IF NOT is_multi_tenant_system THEN');
995 add_body_line(' RETURN ''N'';');
996 add_body_line(' END IF;');
997 add_body_line(' ');
998 add_body_line(' BEGIN');
999 add_body_line(' SELECT ''Y''');
1000 add_body_line(' INTO l_result');
1001 add_body_line(' FROM per_ent_security_groups');
1002 add_body_line(' WHERE security_group_id = p_security_group_id ');
1003 add_body_line(' AND business_group_id = p_business_group_id ');
1004 add_body_line(' AND enabled_flag = ''Y''; ');
1005 add_body_line(' EXCEPTION');
1006 add_body_line(' WHEN others THEN');
1007 add_body_line(' l_result := ''N'';');
1008 add_body_line(' END;');
1009 add_body_line(' RETURN l_result;');
1010 add_body_line('END is_valid_sec_group;');
1011 add_body_line('');
1012 add_body_line('--');
1013 add_body_line('--------------------------------------------------------------------');
1014 add_body_line('--< get_org_id_for_person >-----------------------------------------');
1015 add_body_line('--------------------------------------------------------------------');
1016 add_body_line('--');
1017 add_body_line('-- Description:');
1018 add_body_line('-- Used IN SSHR to derive the security group from person ');
1019 add_body_line('-- i.e. (Notifications/Workflow)');
1020 add_body_line('--');
1021 add_body_line('-- Arguments');
1022 add_body_line('-- p_person_id => id of the selected person');
1023 add_body_line('--');
1024 add_body_line('');
1025 add_body_line('FUNCTION get_org_id_for_person (p_person_id IN NUMBER) RETURN NUMBER AS');
1026 add_body_line('');
1027 add_body_line(' label_query VARCHAR2(1000) :=');
1028 add_body_line(' ''SELECT LABEL_TO_CHAR(HR_ENTERPRISE) ENT_LABEL, BUSINESS_GROUP_ID '' ||');
1029 add_body_line(' '' FROM PER_ALL_PEOPLE_F '' ||');
1030 add_body_line(' '' WHERE PERSON_ID = :1 '' ||');
1031 add_body_line(' '' AND ROWNUM < 2'';');
1032 add_body_line(' l_security_label VARCHAR2(4000);');
1033 add_body_line(' l_business_group_id NUMBER;');
1034 add_body_line(' l_organization_id NUMBER;');
1035 add_body_line('BEGIN');
1036 add_body_line(' BEGIN');
1037 add_body_line(' EXECUTE IMMEDIATE label_query');
1038 add_body_line(' INTO l_security_label, l_business_group_id');
1039 add_body_line(' USING IN p_person_id;');
1040 add_body_line(' ');
1041 add_body_line(' IF (l_security_label IS NOT NULL AND');
1042 add_body_line(' l_security_label = ''C::ENT'' )');
1043 add_body_line(' OR l_security_label IS NULL THEN');
1044 add_body_line(' ');
1045 add_body_line(' BEGIN');
1046 add_body_line(' SELECT business_group_id ');
1047 add_body_line(' INTO l_organization_id ');
1048 add_body_line(' FROM per_all_people_f');
1049 add_body_line(' WHERE person_id = p_person_id');
1050 add_body_line(' AND rownum < 2;');
1051 add_body_line(' RETURN l_organization_id;');
1052 add_body_line(' EXCEPTION ');
1053 add_body_line(' WHEN NO_DATA_FOUND THEN');
1054 add_body_line(' RETURN -1;');
1055 add_body_line(' END;');
1056 add_body_line(' END IF;');
1057 add_body_line(' SELECT organization_id');
1058 add_body_line(' INTO l_organization_id');
1059 add_body_line(' FROM per_ent_security_groups');
1060 add_body_line(' WHERE business_group_id = l_business_group_id ');
1061 add_body_line(' AND enterprise_label = substrb(l_security_label,4) ');
1062 add_body_line(' AND enabled_flag = ''Y'';');
1063 add_body_line(' ');
1064 add_body_line(' RETURN l_organization_id;');
1065 add_body_line(' EXCEPTION');
1066 add_body_line(' WHEN no_data_found THEN');
1067 add_body_line(' RETURN -1;');
1068 add_body_line(' END;');
1069 add_body_line('END get_org_id_for_person;');
1070 add_body_line('');
1071 add_body_line(' --');
1072 add_body_line(' --------------------------------------------------------------------');
1073 add_body_line(' --< get_org_id_for_person >-----------------------------------------');
1074 add_body_line(' --------------------------------------------------------------------');
1075 add_body_line(' --');
1076 add_body_line(' -- Description:');
1077 add_body_line(' -- Used IN SSHR to derive the security group from person ');
1078 add_body_line(' -- i.e. (Notifications/Workflow)');
1079 add_body_line(' -- Get OrganizationId FROM LoginPersonId AND SelectedBusinessGroupId');
1080 add_body_line(' --');
1081 add_body_line(' -- Arguments');
1082 add_body_line(' -- p_person_id => id of the selected person');
1083 add_body_line(' -- p_business_group_id => business group id');
1084 add_body_line('');
1085 add_body_line('FUNCTION get_org_id_for_person (p_person_id IN NUMBER');
1086 add_body_line(' ,p_business_group_id IN NUMBER) RETURN NUMBER AS');
1087 add_body_line(' ');
1088 add_body_line(' label_query VARCHAR2(1000) :=');
1089 add_body_line(' ''SELECT LABEL_TO_CHAR(HR_ENTERPRISE) ENT_LABEL '' ||');
1090 add_body_line(' '' FROM PER_ALL_PEOPLE_F '' ||');
1091 add_body_line(' '' WHERE PERSON_ID = :1 '' ||');
1092 add_body_line(' '' AND ROWNUM < 2'';');
1093 add_body_line(' l_security_label VARCHAR2(4000);');
1097 add_body_line(' BEGIN');
1094 add_body_line(' l_organization_id NUMBER;');
1095 add_body_line(' l_count NUMBER := 0;');
1096 add_body_line(' BEGIN');
1098 add_body_line(' EXECUTE IMMEDIATE label_query');
1099 add_body_line(' INTO l_security_label');
1100 add_body_line(' USING IN p_person_id;');
1101 add_body_line(' EXCEPTION');
1102 add_body_line(' WHEN no_data_found THEN');
1103 add_body_line(' RETURN -1;');
1104 add_body_line(' END;');
1105 add_body_line(' IF l_security_label = ''C::ENT'' THEN');
1106 add_body_line(' RETURN p_business_group_id;');
1107 add_body_line(' ELSE ');
1108 add_body_line(' BEGIN');
1109 add_body_line(' SELECT organization_id');
1110 add_body_line(' INTO l_organization_id');
1111 add_body_line(' FROM per_ent_security_groups');
1112 add_body_line(' WHERE business_group_id = p_business_group_id ');
1113 add_body_line(' AND enterprise_label = substrb(l_security_label,4)');
1114 add_body_line(' AND enabled_flag = ''Y'';');
1115 add_body_line(' RETURN l_organization_id;');
1116 add_body_line(' EXCEPTION');
1117 add_body_line(' WHEN no_data_found THEN');
1118 add_body_line(' RETURN -1;');
1119 add_body_line(' END;');
1120 add_body_line(' END IF;');
1121 add_body_line('END get_org_id_for_person;');
1122 add_body_line('');
1123 add_body_line('--');
1124 add_body_line('--------------------------------------------------------------------');
1125 add_body_line('--< get_org_id_from_bg_and_sl >-------------------------------------');
1126 add_body_line('--------------------------------------------------------------------');
1127 add_body_line('--');
1128 add_body_line('-- Description:');
1129 add_body_line('-- Called From PerAppModuleHelper');
1130 add_body_line('-- to get orgid from BG and security label');
1131 add_body_line('--');
1132 add_body_line('-- Arguments');
1133 add_body_line('-- p_business_group_id => id of the selected business group.');
1134 add_body_line('-- p_security_label => security label.');
1135 add_body_line('--');
1136 add_body_line('');
1137 add_body_line('FUNCTION get_org_id_from_bg_and_sl (p_business_group_id IN NUMBER');
1138 add_body_line(' ,p_security_label IN VARCHAR2) RETURN NUMBER AS');
1139 add_body_line(' l_organization_id NUMBER;');
1140 add_body_line(' BEGIN');
1141 add_body_line(' SELECT organization_id');
1142 add_body_line(' INTO l_organization_id');
1143 add_body_line(' FROM per_ent_security_groups');
1144 add_body_line(' WHERE business_group_id = p_business_group_id');
1145 add_body_line(' AND enterprise_label = p_security_label');
1146 add_body_line(' AND enabled_flag = ''Y'';');
1147 add_body_line(' RETURN l_organization_id;');
1148 add_body_line(' EXCEPTION ');
1149 add_body_line(' WHEN no_data_found THEN');
1150 add_body_line(' RETURN p_business_group_id;');
1151 add_body_line(' END get_org_id_from_bg_and_sl;');
1152 add_body_line('');
1153 add_body_line(' --');
1154 add_body_line(' --------------------------------------------------------------------');
1155 add_body_line(' --< get_corporate_branding >-------------------------------------');
1156 add_body_line(' --------------------------------------------------------------------');
1157 add_body_line(' --');
1158 add_body_line(' -- Description:');
1159 add_body_line(' -- Gets the corporate branding for the passed/current enterprise.');
1160 add_body_line(' -- ');
1161 add_body_line(' -- Called From');
1162 add_body_line(' -- 1. HRApplicationModuleImpl.java without argument');
1163 add_body_line(' -- 2. MTHomeAMImpl.java with argument.');
1164 add_body_line(' -- Arguments');
1165 add_body_line(' -- p_organization_id => organization id of the current enterprise');
1166 add_body_line(' --');
1167 add_body_line('');
1168 add_body_line('FUNCTION get_corporate_branding (p_organization_id VARCHAR2 default NULL) RETURN VARCHAR2 AS');
1169 add_body_line(' l_security_group_id NUMBER; ');
1170 add_body_line(' l_profile_value VARCHAR2(240);');
1171 add_body_line('');
1172 add_body_line('--');
1173 add_body_line('-- The lookup code for corporate branding is 1');
1174 add_body_line('--');
1175 add_body_line(' CURSOR csr_profile_value (p_security_group_id NUMBER) IS');
1176 add_body_line(' SELECT description ');
1177 add_body_line(' FROM fnd_lookup_values');
1178 add_body_line(' WHERE security_group_id = p_security_group_id');
1179 add_body_line(' AND lookup_type = ''PER_MT_VALUES''');
1180 add_body_line(' AND meaning = ''FND_CORPORATE_BRANDING_IMAGE''');
1181 add_body_line(' AND enabled_flag = ''Y''');
1182 add_body_line(' AND language = userenv(''LANG'')');
1183 add_body_line(' AND sysdate between nvl(start_date_active, hr_api.g_sot)');
1184 add_body_line(' AND nvl(end_date_active, hr_api.g_eot);');
1185 add_body_line('BEGIN');
1186 add_body_line(' IF p_organization_id IS NULL THEN');
1187 add_body_line(' l_security_group_id := fnd_global.security_group_id;');
1188 add_body_line(' ELSE');
1189 add_body_line(' SELECT security_group_id ');
1190 add_body_line(' INTO l_security_group_id');
1191 add_body_line(' FROM per_ent_security_groups');
1192 add_body_line(' WHERE to_char(organization_id) = p_organization_id;');
1193 add_body_line(' END IF;');
1194 add_body_line(' OPEN csr_profile_value(l_security_group_id);');
1195 add_body_line(' FETCH csr_profile_value INTO l_profile_value;');
1196 add_body_line(' IF csr_profile_value%notfound THEN');
1197 add_body_line(' CLOSE csr_profile_value;');
1198 add_body_line(' OPEN csr_profile_value(0);');
1202 add_body_line('');
1199 add_body_line(' FETCH csr_profile_value INTO l_profile_value;');
1200 add_body_line(' CLOSE csr_profile_value;');
1201 add_body_line(' END IF; ');
1203 add_body_line(' RETURN l_profile_value;');
1204 add_body_line('END get_corporate_branding;');
1205 add_body_line('');
1206 add_body_line('--');
1207 add_body_line('--------------------------------------------------------------------');
1208 add_body_line('--< get_bus_grp_from_sec_grp >-------------------------------------');
1209 add_body_line('--------------------------------------------------------------------');
1210 add_body_line('--');
1211 add_body_line('-- Description:');
1212 add_body_line('-- Called from HR_SIGNON to RETURN the business group corresponding');
1213 add_body_line('-- to the enterprise security group for buisness group initialization. ');
1214 add_body_line('--');
1215 add_body_line('-- Called From');
1216 add_body_line('-- 1. HR_SIGNON ');
1217 add_body_line('-- Arguments');
1218 add_body_line('-- p_security_group_id => security group id of the current enterprise');
1219 add_body_line('--');
1220 add_body_line('FUNCTION get_bus_grp_from_sec_grp (p_security_group_id IN NUMBER) RETURN NUMBER AS');
1221 add_body_line(' l_business_group_id NUMBER;');
1222 add_body_line('BEGIN');
1223 add_body_line(' SELECT business_group_id');
1224 add_body_line(' INTO l_business_group_id');
1225 add_body_line(' FROM per_ent_security_groups');
1226 add_body_line(' WHERE security_group_id = p_security_group_id');
1227 add_body_line(' AND enabled_flag = ''Y'';');
1228 add_body_line('');
1229 add_body_line(' RETURN l_business_group_id;');
1230 add_body_line('EXCEPTION');
1231 add_body_line(' WHEN no_data_found THEN');
1232 add_body_line(' RETURN NULL; ');
1233 add_body_line('END get_bus_grp_from_sec_grp;');
1234 add_body_line('');
1235 add_body_line('--');
1236 add_body_line('--------------------------------------------------------------------');
1237 add_body_line('--< set_security_group_id >-------------------------------------');
1238 add_body_line('--------------------------------------------------------------------');
1239 add_body_line('--');
1240 add_body_line('-- Description:');
1241 add_body_line('-- Called from HR_API to set proper security group. ');
1242 add_body_line('--');
1243 add_body_line('-- Called From');
1244 add_body_line('-- 1. HR_API ');
1245 add_body_line('-- Arguments');
1246 add_body_line('-- p_security_group_id => security group id of the current enterprise');
1247 add_body_line('--');
1248 add_body_line('PROCEDURE set_security_group_id (p_security_group_id IN NUMBER) AS ');
1249 add_body_line(' l_business_group_id NUMBER;');
1250 add_body_line(' l_security_group_id NUMBER;');
1251 add_body_line(' CURSOR csr_sec_grp_for_bus_grp (p_business_group_id NUMBER) IS');
1252 add_body_line(' SELECT security_group_id');
1253 add_body_line(' FROM per_ent_security_groups');
1254 add_body_line(' WHERE business_group_id = p_business_group_id');
1255 add_body_line(' AND enabled_flag = ''Y'';');
1256 add_body_line('BEGIN');
1257 add_body_line(' SELECT business_group_id');
1258 add_body_line(' INTO l_business_group_id');
1259 add_body_line(' FROM per_business_groups');
1260 add_body_line(' WHERE security_group_id = p_security_group_id;');
1261 add_body_line('');
1262 add_body_line(' OPEN csr_sec_grp_for_bus_grp');
1263 add_body_line(' (p_business_group_id => l_business_group_id);');
1264 add_body_line('');
1265 add_body_line(' LOOP');
1266 add_body_line(' FETCH csr_sec_grp_for_bus_grp INTO l_security_group_id;');
1267 add_body_line('');
1268 add_body_line(' IF csr_sec_grp_for_bus_grp%found THEN');
1269 add_body_line(' IF l_security_group_id = fnd_global.security_group_id THEN');
1270 add_body_line(' CLOSE csr_sec_grp_for_bus_grp;');
1271 add_body_line(' RETURN;');
1272 add_body_line(' END IF;');
1273 add_body_line(' ELSE');
1274 add_body_line(' EXIT;');
1275 add_body_line(' END IF;');
1276 add_body_line(' END LOOP;');
1277 add_body_line(' CLOSE csr_sec_grp_for_bus_grp;');
1278 add_body_line('');
1279 add_body_line(' fnd_global.set_security_group_id_context(p_security_group_id); ');
1280 add_body_line('END set_security_group_id;');
1281 add_body_line('');
1282 add_body_line('--');
1283 add_body_line('--------------------------------------------------------------------');
1284 add_body_line('--< add_language >--------------------------------------------------');
1285 add_body_line('--------------------------------------------------------------------');
1286 add_body_line('--');
1287 add_body_line('-- Description:');
1288 add_body_line('-- Updates TL table');
1289 add_body_line('--');
1290 add_body_line('-- Arguments');
1291 add_body_line('-- None');
1292 add_body_line('--');
1293 add_body_line('PROCEDURE add_language IS');
1294 add_body_line(' CURSOR csr_ent_data_groups IS');
1295 add_body_line(' SELECT pet.enterprise_id');
1296 add_body_line(' ,pet.enterprise_name');
1297 add_body_line(' ,pet.description');
1298 add_body_line(' ,pet.source_lang');
1299 add_body_line(' ,pet.created_by');
1300 add_body_line(' ,pet.creation_date');
1301 add_body_line(' FROM per_enterprises_tl pet');
1302 add_body_line(' WHERE pet.language = userenv(''LANG'');');
1303 add_body_line(' ');
1304 add_body_line(' CURSOR csr_ins_langs (c_enterprise_id NUMBER) IS');
1305 add_body_line(' SELECT l.language_code');
1306 add_body_line(' FROM fnd_languages l');
1307 add_body_line(' WHERE l.installed_flag IN (''I'',''B'')');
1308 add_body_line(' AND NOT EXISTS (SELECT NULL');
1312 add_body_line(' --');
1309 add_body_line(' FROM per_enterprises_tl pet');
1310 add_body_line(' WHERE pet.enterprise_id = c_enterprise_id');
1311 add_body_line(' AND pet.language = l.language_code);');
1313 add_body_line(' BEGIN');
1314 add_body_line(' --');
1315 add_body_line(' DELETE FROM per_enterprises_tl t');
1316 add_body_line(' WHERE NOT EXISTS');
1317 add_body_line(' ( SELECT NULL');
1318 add_body_line(' FROM per_enterprises b');
1319 add_body_line(' WHERE b.enterprise_id = t.enterprise_id');
1320 add_body_line(' );');
1321 add_body_line('');
1322 add_body_line(' UPDATE per_enterprises_tl t');
1323 add_body_line(' SET ( enterprise_name,');
1324 add_body_line(' description ) =');
1325 add_body_line(' ( SELECT b.enterprise_name,');
1326 add_body_line(' b.description');
1327 add_body_line(' FROM per_enterprises_tl b');
1328 add_body_line(' WHERE b.enterprise_id = t.enterprise_id');
1329 add_body_line(' AND b.language = t.source_lang )');
1330 add_body_line(' WHERE ( t.enterprise_id,');
1331 add_body_line(' t.language');
1332 add_body_line(' ) IN');
1333 add_body_line(' ( SELECT subt.enterprise_id,');
1334 add_body_line(' subt.language');
1335 add_body_line(' FROM per_enterprises_tl subb, per_enterprises_tl subt');
1336 add_body_line(' WHERE subb.enterprise_id = subt.enterprise_id');
1337 add_body_line(' AND subb.language = subt.source_lang');
1338 add_body_line(' AND ( subb.enterprise_name <> subt.enterprise_name');
1339 add_body_line(' OR subb.description <> subt.description');
1340 add_body_line(' OR (subb.description IS NULL AND subt.description IS NOT NULL)');
1341 add_body_line(' OR (subb.description IS NOT NULL AND subt.description IS NULL)');
1342 add_body_line(' )');
1343 add_body_line(' );');
1344 add_body_line(' --');
1345 add_body_line(' --');
1346 add_body_line(' FOR l_ent_data_group IN csr_ent_data_groups LOOP');
1347 add_body_line(' FOR l_lang IN csr_ins_langs(l_ent_data_group.enterprise_id) LOOP');
1348 add_body_line(' INSERT INTO per_enterprises_tl');
1349 add_body_line(' (source_lang');
1350 add_body_line(' ,enterprise_id');
1351 add_body_line(' ,enterprise_name');
1352 add_body_line(' ,description');
1353 add_body_line(' ,language');
1354 add_body_line(' ,created_by');
1355 add_body_line(' ,creation_date');
1356 add_body_line(' ,last_updated_by');
1357 add_body_line(' ,last_update_date');
1358 add_body_line(' ) VALUES');
1359 add_body_line(' (l_ent_data_group.source_lang');
1360 add_body_line(' ,l_ent_data_group.enterprise_id');
1361 add_body_line(' ,l_ent_data_group.enterprise_name');
1362 add_body_line(' ,l_ent_data_group.description');
1363 add_body_line(' ,l_lang.language_code');
1364 add_body_line(' ,fnd_global.user_id');
1365 add_body_line(' ,sysdate');
1366 add_body_line(' ,fnd_global.user_id');
1367 add_body_line(' ,sysdate');
1368 add_body_line(' ); ');
1369 add_body_line(' END LOOP;');
1370 add_body_line(' END LOOP;');
1371 add_body_line(' --');
1372 add_body_line(' END add_language;');
1373 add_body_line('');
1374 add_body_line('END '||l_package_name ||';');
1375 add_header_line('END ' ||l_package_name||';');
1376
1377 l_csr_sql := dbms_sql.open_cursor;
1378 dbms_sql.parse( l_csr_sql, l_header,0,l_header_line-1,FALSE, dbms_sql.v7 );
1379 l_rows := dbms_sql.EXECUTE( l_csr_sql );
1380 dbms_sql.close_cursor( l_csr_sql );
1381
1382 l_csr_sql := dbms_sql.open_cursor;
1383 dbms_sql.parse( l_csr_sql, l_body,0,l_body_line-1,FALSE, dbms_sql.v7 );
1384 l_rows := dbms_sql.EXECUTE( l_csr_sql );
1385 dbms_sql.close_cursor( l_csr_sql );
1386
1387 l_header_line :=0;
1388 l_body_line :=0;
1389
1390 --
1391 -- Create or replace the hr_multi_tenant_install package
1392 --
1393
1394 l_package_name := 'hr_multi_tenant_install';
1395 add_header_line('CREATE OR REPLACE PACKAGE '||l_package_name||' AS ');
1396 add_body_line('CREATE OR REPLACE PACKAGE BODY '||l_package_name||' AS ');
1397 add_header_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
1398 add_header_line('/'||'* $Header: pemtstup.pkb 120.0.12010000.20 2008/11/24 10:27:27 bchakrab noship*'||'/');
1399 add_body_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
1400 add_body_line('/'||'* $Header: pemtstup.pkb *'||'/');
1401
1402
1403 add_header_line('--');
1404 add_header_line('-- Name');
1405 add_header_line('-- initialize_orgs');
1406 add_header_line('--');
1407 add_header_line('-- Purpose');
1408 add_header_line('-- This procedure is called by the concurrent program');
1409 add_header_line('-- Setup Enterprise for Multi Tenancy.This procedure attaches the ');
1410 add_header_line('-- the enterprise label with the organization specified as parameters');
1411 add_header_line('--');
1412 add_header_line('-- Arguments');
1413 add_header_line('-- errbuf and retcode.');
1414 add_header_line('-- p_enterprise_id => This specifies the Id of the defined enterprise');
1415 add_header_line('-- p_organization_id => This specifies the Id of the organization ');
1416 add_header_line('-- that is classified as enterprise ');
1417 add_header_line('--');
1418 add_header_line(' ');
1419 add_header_line('PROCEDURE initialize_orgs (errbuf OUT NOCOPY VARCHAR2');
1423 add_header_line(' ');
1420 add_header_line(' ,retcode OUT NOCOPY NUMBER');
1421 add_header_line(' ,p_enterprise_id IN NUMBER');
1422 add_header_line(' ,p_organization_id IN NUMBER);');
1424 add_header_line('--');
1425 add_header_line('-- Name');
1426 add_header_line('-- master_process');
1427 add_header_line('--');
1428 add_header_line('-- Purpose');
1429 add_header_line('-- This procedure is called by the concurrent program');
1430 add_header_line('-- Setup Multi Tenancy for Database Objects.This concurrent program');
1431 add_header_line('-- spawns child processes in batches of size less or equalt to 100. ');
1432 add_header_line('--');
1433 add_header_line('-- Arguments');
1434 add_header_line('-- errbuf and retcode.');
1435 add_header_line('-- install_mode => The valid values are USER_TABLE and SCHEMA. This');
1436 add_header_line('-- specifies the mode of applying policy i.e whether on ');
1437 add_header_line('-- all tables in the schema or on the tables listed');
1438 add_header_line('-- in the user table.');
1439 add_header_line('-- ');
1440 add_header_line('-- population_size => This specifies the batch size of the spawning child ');
1444 add_header_line('procedure master_process');
1441 add_header_line('-- processes. The default batch size is 100 ');
1442 add_header_line('--');
1443 add_header_line(' ');
1445 add_header_line(' (errbuf out nocopy varchar2');
1446 add_header_line(' ,retcode out nocopy number');
1447 add_header_line(' ,install_mode in varchar2');
1448 add_header_line(' ,population_size in number default 100);');
1449 add_header_line('');
1450 add_header_line('');
1451 add_header_line('--');
1452 add_header_line('-- Name');
1453 add_header_line('-- child_process');
1454 add_header_line('--');
1455 add_header_line('-- Purpose');
1456 add_header_line('-- This procedure is called by the concurrent program');
1457 add_header_line('-- Setup Multi Tenancy for Database Objects as a spawned');
1458 add_header_line('-- child processes in batches of size less or equalt to 100. ');
1459 add_header_line('--');
1460 add_header_line('-- Arguments');
1461 add_header_line('-- errbuf and retcode.');
1462 add_header_line('-- install_mode => The valid values are USER_TABLE and SCHEMA. This');
1463 add_header_line('-- specifies the mode of applying policy i.e whether on ');
1464 add_header_line('-- all tables in the schema or on the tables listed');
1465 add_header_line('-- in the user table.');
1466 add_header_line('-- ');
1467 add_header_line('-- population_size => This specifies the batch size of the spawning child ');
1468 add_header_line('-- processes. ');
1469 add_header_line('-- ');
1470 add_header_line('-- population_start => This is the starting count of the batch');
1471 add_header_line('-- population_end => This is the ending count of the batch');
1472 add_header_line('--');
1473 add_header_line('--');
1474 add_header_line('');
1475 add_header_line(' procedure child_process ');
1476 add_header_line(' (errbuf out nocopy varchar2');
1477 add_header_line(' ,retcode out nocopy number');
1478 add_header_line(' ,install_mode in varchar2');
1479 add_header_line(' ,population_size in number');
1480 add_header_line(' ,population_start in number');
1481 add_header_line(' ,population_end in number); ');
1482
1483 add_body_line('');
1484 add_body_line('--');
1485 add_body_line('-- Variable declarations');
1486 add_body_line('--');
1487 add_body_line(' l_body_line NUMBER:=0;');
1488 add_body_line(' l_body DBMS_SQL.VARCHAR2S;');
1489 add_body_line(' l_csr_sql NUMBER;');
1490 add_body_line(' l_rows NUMBER;');
1491 add_body_line('');
1492 add_body_line('-----------------------------------------------------------------------');
1493 add_body_line('-----------------------------------------------------------------------');
1494 add_body_line('-- PRIVATE FUNCTIONS');
1495 add_body_line('-----------------------------------------------------------------------');
1496 add_body_line('-----------------------------------------------------------------------');
1497 add_body_line('');
1498 add_body_line('--');
1499 add_body_line('--------------------------------------------------------------------');
1500 add_body_line('--< CREATE_SEC_GROUP_FOR_ENT >--------------------------------------');
1501 add_body_line('--------------------------------------------------------------------');
1502 add_body_line('--');
1503 add_body_line('-- Description:');
1504 add_body_line('-- This procedure accepts the organization id,');
1505 add_body_line('-- business group id, security label and enterprise id');
1506 add_body_line('-- and creates the necessary back end housekeeping for the organization');
1507 add_body_line('-- to be defined as enterprise.');
1508 add_body_line('--');
1509 add_body_line('-- Arguments:');
1510 add_body_line('-- p_organization_id => Id of the organization to be defined as enterprise');
1511 add_body_line('-- p_business_group_id => Business Group Id of the organization');
1512 add_body_line('-- p_security_label => label of the enterprise');
1513 add_body_line('-- p_enterprise_id => id of the enterprise');
1514 add_body_line('--');
1515 add_body_line('');
1516 add_body_line('PROCEDURE create_sec_group_for_ent (p_organization_id IN NUMBER');
1517 add_body_line(' ,p_business_group_id IN NUMBER');
1518 add_body_line(' ,p_security_label IN VARCHAR2');
1519 add_body_line(' ,p_enterprise_id IN NUMBER ) IS');
1520 add_body_line('');
1521 add_body_line(' CURSOR c_sg_enabled IS');
1522 add_body_line(' SELECT ''Y''');
1523 add_body_line(' FROM fnd_profile_options fpo');
1524 add_body_line(' ,fnd_profile_option_values pov');
1525 add_body_line(' WHERE fpo.profile_option_name = ''ENABLE_SECURITY_GROUPS'' AND');
1526 add_body_line(' fpo.profile_option_id = pov.profile_option_id AND');
1527 add_body_line(' fpo.application_id = pov.application_id AND');
1528 add_body_line(' pov.level_id = 10002 AND');
1529 add_body_line(' pov.profile_option_value = ''Y'' AND');
1530 add_body_line(' to_number(pov.level_value) BETWEEN 800 AND 900;');
1531 add_body_line('');
1532 add_body_line(' CURSOR c_sec_grp_name_curs(p_sec_name VARCHAR2) IS');
1533 add_body_line(' SELECT substrb(security_group_name, 1, 80), security_group_id');
1534 add_body_line(' FROM fnd_security_groups_vl');
1535 add_body_line(' WHERE substrb(security_group_name, 1, 80)= p_sec_name;');
1536 add_body_line('');
1537 add_body_line(' l_security_group_name hr_all_organization_units.name%type;');
1538 add_body_line(' l_exists VARCHAR2(1) DEFAULT NULL;');
1539 add_body_line(' l_sg_enabled BOOLEAN DEFAULT FALSE;');
1540 add_body_line(' l_sec_length NUMBER;');
1541 add_body_line(' l_security_group_id NUMBER;');
1542 add_body_line(' l_ent_name VARCHAR2(80);');
1543 add_body_line(' l_sg_name VARCHAR2(80);');
1544 add_body_line(' l_out_status VARCHAR2(30);');
1545 add_body_line(' l_out_industry VARCHAR2(30);');
1546 add_body_line(' l_owner VARCHAR2(30);');
1547 add_body_line(' l_value BOOLEAN;');
1548 add_body_line(' BEGIN');
1549 add_body_line('--');
1550 add_body_line('-- Check if security groups are enabled in the instance');
1551 add_body_line('--');
1552 add_body_line(' OPEN c_sg_enabled;');
1553 add_body_line(' FETCH c_sg_enabled INTO l_exists;');
1554 add_body_line(' IF c_sg_enabled%FOUND THEN');
1555 add_body_line(' l_sg_enabled := TRUE;');
1556 add_body_line(' ELSE');
1557 add_body_line(' l_sg_enabled := FALSE;');
1558 add_body_line(' END IF;');
1559 add_body_line(' CLOSE c_sg_enabled;');
1560 add_body_line(' IF l_sg_enabled THEN');
1561 add_body_line('--');
1562 add_body_line('-- Fetch the corresponding security group name of the organization.');
1563 add_body_line('--');
1564 add_body_line(' SELECT substrb(hou.name,1,80)');
1565 add_body_line(' INTO l_security_group_name');
1566 add_body_line(' FROM hr_all_organization_units hou');
1567 add_body_line(' WHERE hou.organization_id = p_organization_id;');
1568 add_body_line('');
1569 add_body_line('--');
1570 add_body_line('-- Fetch the security_group_id');
1571 add_body_line('--');
1572 add_body_line(' OPEN c_sec_grp_name_curs(l_security_group_name);');
1573 add_body_line(' FETCH c_sec_grp_name_curs INTO l_sg_name, l_security_group_id;');
1574 add_body_line(' IF c_sec_grp_name_curs%found THEN');
1575 add_body_line(' CLOSE c_sec_grp_name_curs;');
1576 add_body_line('--');
1577 add_body_line('-- This is an error condition in Model 2(PEO).');
1578 add_body_line('--');
1579 add_body_line(' IF hr_multi_tenancy_pkg.get_system_model = ''P'' THEN');
1580 add_body_line(' hr_utility.set_message(800, ''PER_289704_80CHAR_MATCH_SG'');');
1581 add_body_line(' hr_utility.raise_error;');
1582 add_body_line(' END IF; ');
1583 add_body_line(' ELSE');
1584 add_body_line(' CLOSE c_sec_grp_name_curs;');
1585 add_body_line('--');
1586 add_body_line('-- This is an error condition in Model 1 (BPO)');
1587 add_body_line('--');
1588 add_body_line(' IF hr_multi_tenancy_pkg.get_system_model = ''B'' THEN');
1589 add_body_line(' hr_utility.set_message(800, ''PER_OLS_SETUP_ERROR'');');
1590 add_body_line(' hr_utility.raise_error;');
1591 add_body_line(' END IF;');
1592 add_body_line('--');
1593 add_body_line('-- Create a new security group for this organization');
1594 add_body_line('--');
1595 add_body_line(' l_security_group_id := fnd_security_groups_api.create_group (security_group_key => to_char(p_organization_id)');
1596 add_body_line(' ,security_group_name => l_security_group_name');
1597 add_body_line(' ,description => '' '');');
1598 add_body_line(' END IF;');
1599 add_body_line('--');
1600 add_body_line('-- Make an entry in the table for this organization');
1601 add_body_line('--');
1602 add_body_line('BEGIN');
1606 add_body_line(' ,enterprise_label');
1603 add_body_line(' INSERT INTO per_ent_security_groups ');
1604 add_body_line(' (enterprise_id');
1605 add_body_line(' ,organization_id');
1607 add_body_line(' ,business_group_id');
1608 add_body_line(' ,security_group_id');
1609 add_body_line(' ,enabled_flag)');
1610 add_body_line(' VALUES (p_enterprise_id');
1611 add_body_line(' ,p_organization_id');
1612 add_body_line(' ,p_security_label');
1613 add_body_line(' ,p_business_group_id');
1614 add_body_line(' ,l_security_group_id');
1615 add_body_line(' ,''Y'');');
1616 add_body_line('EXCEPTION');
1617 add_body_line(' WHEN OTHERS THEN');
1618 add_body_line(' fnd_file.put_line (fnd_file.log');
1619 add_body_line(' ,''Cannot map Security Group for this enterprise '');');
1620 add_body_line('END;');
1621 add_body_line(' ELSE');
1622 add_body_line(' fnd_file.put_line (fnd_file.log');
1623 add_body_line(' ,''Security Group Not enabled: '');');
1624 add_body_line(' ');
1625 add_body_line(' END IF;');
1626 add_body_line('END create_sec_group_for_ent;');
1627 add_body_line('');
1628 add_body_line('--');
1629 add_body_line('-----------------------------------------------------------------------');
1630 add_body_line('-----------------------------------------------------------------------');
1631 add_body_line('-- PUBLIC FUNCTIONS');
1632 add_body_line('-----------------------------------------------------------------------');
1633 add_body_line('-----------------------------------------------------------------------');
1634 add_body_line('--');
1635 add_body_line('-----------------------------------------------------------------------');
1636 add_body_line('--< initialize_orgs >--------------------------------------------------');
1637 add_body_line('-----------------------------------------------------------------------');
1638 add_body_line('--');
1639 add_body_line('-- Description:');
1640 add_body_line('-- This procedure is called by the concurrent program');
1641 add_body_line('-- Setup Enterprise for Multi Tenancy.This procedure attaches the ');
1642 add_body_line('-- the enterprise label with the organization specified as parameters');
1643 add_body_line('--');
1644 add_body_line('-- Arguments');
1645 add_body_line('-- errbuf and retcode.');
1646 add_body_line('-- p_enterprise_id => This specifies the Id of the defined enterprise');
1647 add_body_line('-- p_organization_id => This specifies the Id of the organization ');
1648 add_body_line('-- that is classified as enterprise ');
1649 add_body_line('--');
1650 add_body_line('');
1651 add_body_line('PROCEDURE initialize_orgs ( errbuf OUT NOCOPY VARCHAR2');
1652 add_body_line(' , retcode OUT NOCOPY NUMBER');
1653 add_body_line(' , p_enterprise_id IN NUMBER');
1654 add_body_line(' , p_organization_id IN NUMBER) AS');
1655 add_body_line(' ');
1656 add_body_line(' TYPE bg_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;');
1657 add_body_line(' TYPE child_table_info IS');
1658 add_body_line(' RECORD (table_name varchar2(30), ');
1659 add_body_line(' child_key_name varchar2(4000), ');
1660 add_body_line(' master_table varchar2(30), ');
1661 add_body_line(' master_key_name varchar2(4000));');
1662 add_body_line(' TYPE child_tables IS TABLE OF child_table_info INDEX BY BINARY_INTEGER;');
1663 add_body_line('');
1664 add_body_line(' init_master_tables bg_table;');
1665 add_body_line(' init_table_name VARCHAR2(256);');
1666 add_body_line(' init_child_tables child_tables;');
1667 add_body_line(' l_org_info VARCHAR2(1000);');
1668 add_body_line(' l_enterprise_name VARCHAR2(30);');
1669 add_body_line(' l_long_name VARCHAR2(80);');
1670 add_body_line(' l_group_number NUMBER;');
1671 add_body_line(' l_is_business_group BOOLEAN DEFAULT FALSE;');
1672 add_body_line(' l_business_group_id NUMBER;');
1673 add_body_line(' l_lbl_value NUMBER;');
1674 add_body_line(' l_company_label VARCHAR2(150);');
1675 add_body_line('');
1676 add_body_line('BEGIN');
1677 add_body_line(' IF p_organization_id IS NOT NULL THEN');
1678 add_body_line('--');
1679 add_body_line('-- Fetch the businessgroup Id of the organization.');
1680 add_body_line('--');
1681 add_body_line(' BEGIN');
1682 add_body_line('--');
1683 add_body_line('-- Check if this is a business group.');
1684 add_body_line('--');
1685 add_body_line(' SELECT business_group_id');
1686 add_body_line(' INTO l_business_group_id');
1687 add_body_line(' FROM hr_all_organization_units');
1688 add_body_line(' WHERE organization_id = p_organization_id;');
1689 add_body_line('');
1690 add_body_line(' BEGIN');
1691 add_body_line(' SELECT 1');
1692 add_body_line(' INTO l_org_info');
1693 add_body_line(' FROM hr_organization_information');
1694 add_body_line(' WHERE org_information_context = ''CLASS''');
1695 add_body_line(' AND organization_id = p_organization_id');
1696 add_body_line(' AND org_information1 IN (''HR_BG'');');
1697 add_body_line('');
1698 add_body_line(' l_is_business_group := TRUE;');
1699 add_body_line('');
1700 add_body_line(' EXCEPTION');
1701 add_body_line(' WHEN no_data_found THEN');
1702 add_body_line(' l_is_business_group := FALSE;');
1703 add_body_line(' END;');
1704 add_body_line('--');
1708 add_body_line(' SELECT substr(enterprise_short_code, 1, 80)');
1705 add_body_line('-- Select the long name and the company label for this enterprise id');
1706 add_body_line('--');
1707 add_body_line('');
1709 add_body_line(' ,enterprise_label');
1710 add_body_line(' INTO l_long_name,l_company_label');
1711 add_body_line(' FROM per_enterprises');
1712 add_body_line(' WHERE enterprise_id = p_enterprise_id;');
1713 add_body_line('');
1714 add_body_line(' l_enterprise_name := substr(l_company_label, 1, 30);');
1715 add_body_line('');
1716 add_body_line(' fnd_file.put_line (fnd_file.log');
1717 add_body_line(' ,''Creating Security Group : '' || l_enterprise_name);');
1718 add_body_line(' fnd_file.put_line (fnd_file.output');
1719 add_body_line(' ,''Creating Security Group : '' || l_enterprise_name);');
1720 add_body_line('');
1721 add_body_line('--');
1722 add_body_line('-- In Model 2 (PEO) create a security group for this enterprise.');
1723 add_body_line('--');
1724 add_body_line(' create_sec_group_for_ent (p_organization_id => p_organization_id');
1725 add_body_line(' ,p_business_group_id => l_business_group_id');
1726 add_body_line(' ,p_security_label => l_enterprise_name');
1727 add_body_line(' ,p_enterprise_id => p_enterprise_id);');
1728 add_body_line('');
1729 add_body_line(' l_enterprise_name := ''C::'' || l_enterprise_name;');
1730 add_body_line('');
1731 add_body_line(' IF NOT l_is_business_group THEN');
1732 add_body_line(' fnd_file.put_line(fnd_file.log');
1733 add_body_line(' ,''Initializing Control :Person Sequence'');');
1734 add_body_line(' ');
1735 add_body_line('--');
1736 add_body_line('-- In model 2 make seperate entries in the PER_NUMBER_GENERATION_CONTROLS');
1737 add_body_line('-- for each enterprise');
1738 add_body_line('--');
1739 add_body_line('EXECUTE IMMEDIATE');
1740 add_body_line(' ''INSERT INTO per_number_generation_controls ');
1741 add_body_line(' (type');
1742 add_body_line(' ,business_group_id');
1743 add_body_line(' ,next_value');
1744 add_body_line(' ,hr_enterprise)');
1745 add_body_line(' VALUES (''''EMP''''');
1746 add_body_line(' ,:1');
1747 add_body_line(' ,1');
1748 add_body_line(' ,char_to_label(''''HR_ENTERPRISE_POLICY'''', :2))''');
1749 add_body_line(' USING l_business_group_id');
1750 add_body_line(' ,l_enterprise_name;');
1751 add_body_line('');
1752 add_body_line('EXECUTE IMMEDIATE');
1753 add_body_line(' ''INSERT INTO per_number_generation_controls ');
1754 add_body_line(' (type');
1755 add_body_line(' ,business_group_id');
1756 add_body_line(' ,next_value');
1757 add_body_line(' ,hr_enterprise)');
1758 add_body_line(' VALUES (''''APL''''');
1759 add_body_line(' ,:1');
1760 add_body_line(' ,1');
1761 add_body_line(' ,char_to_label(''''HR_ENTERPRISE_POLICY''''');
1762 add_body_line(' ,:2))''');
1763 add_body_line(' USING l_business_group_id');
1764 add_body_line(' ,l_enterprise_name;');
1765 add_body_line('');
1766 add_body_line('EXECUTE IMMEDIATE');
1767 add_body_line(' ''INSERT INTO per_number_generation_controls ');
1768 add_body_line(' (type');
1769 add_body_line(' ,business_group_id');
1770 add_body_line(' ,next_value');
1771 add_body_line(' ,hr_enterprise)');
1772 add_body_line(' VALUES (''''CWK''''');
1773 add_body_line(' ,:1');
1774 add_body_line(' ,1');
1775 add_body_line(' ,char_to_label(''''HR_ENTERPRISE_POLICY''''');
1776 add_body_line(' ,:2))''');
1777 add_body_line(' USING l_business_group_id');
1778 add_body_line(' ,l_enterprise_name;');
1779 add_body_line('');
1780 add_body_line(' fnd_file.put_line (fnd_file.log');
1781 add_body_line(' ,''Completed Control: Person Sequence'');');
1782 add_body_line(' ELSE');
1783 add_body_line(' init_master_tables(1) := ''PER_PERSON_TYPES'';');
1784 add_body_line(' init_master_tables(2) := ''PER_NUMBER_GENERATION_CONTROLS'';');
1785 add_body_line(' init_master_tables(3) := ''PER_SECURITY_PROFILES'';');
1786 add_body_line(' init_master_tables(4) := ''PER_JOB_GROUPS'';');
1787 add_body_line(' init_master_tables(5) := ''PAY_CONSOLIDATION_SETS'';');
1788 add_body_line(' init_master_tables(6) := ''PAY_USER_COLUMN_INSTANCES_F'';');
1789 add_body_line(' init_master_tables(7) := ''PER_SPECIAL_INFO_TYPES'';');
1790 add_body_line(' init_master_tables(8) := ''PER_US_OSHA_NUMBERS'';');
1791 add_body_line(' init_master_tables(9) := ''BEN_LER_F'';');
1792 add_body_line(' init_master_tables(10) := ''BEN_ACTN_TYP'';');
1793 add_body_line(' init_master_tables(11) := ''BEN_CM_TYP_F'';');
1794 add_body_line(' init_master_tables(12) := ''BEN_REGN_F'';');
1795 add_body_line('');
1796 add_body_line(' FOR t_index IN 1 .. init_master_tables.COUNT LOOP');
1797 add_body_line(' BEGIN');
1798 add_body_line(' init_table_name :=init_master_tables(t_index);');
1799 add_body_line(' EXECUTE IMMEDIATE ''UPDATE ''||init_table_name||'' ');
1800 add_body_line(' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''', :1)');
1801 add_body_line(' WHERE business_group_id = :2''');
1802 add_body_line(' USING l_enterprise_name');
1803 add_body_line(' ,l_business_group_id;');
1804 add_body_line(' EXCEPTION');
1808 add_body_line(' END;');
1805 add_body_line(' WHEN OTHERS THEN');
1806 add_body_line(' fnd_file.put_line(fnd_file.log');
1807 add_body_line(' ,''Error in updating:''||init_table_name);');
1809 add_body_line(' END LOOP;');
1810 add_body_line('');
1811 add_body_line(' init_child_tables(1).table_name := ''PER_PERSON_TYPES_TL'';');
1812 add_body_line(' init_child_tables(1).child_key_name := ''PERSON_TYPE_ID'';');
1813 add_body_line(' init_child_tables(1).master_table := ''PER_PERSON_TYPES'';');
1814 add_body_line(' init_child_tables(1).master_key_name := ''PERSON_TYPE_ID'';');
1815 add_body_line('');
1816 add_body_line(' init_child_tables(2).table_name := ''PER_SPECIAL_INFO_TYPE_USAGES'';');
1817 add_body_line(' init_child_tables(2).child_key_name := ''SPECIAL_INFORMATION_TYPE_ID'';');
1818 add_body_line(' init_child_tables(2).master_table := ''PER_SPECIAL_INFO_TYPES'';');
1819 add_body_line(' init_child_tables(2).master_key_name := ''SPECIAL_INFORMATION_TYPE_ID''; ');
1820 add_body_line('');
1821 add_body_line(' init_child_tables(3).table_name := ''BEN_LER_F_TL'';');
1822 add_body_line(' init_child_tables(3).child_key_name := ''LER_ID'';');
1823 add_body_line(' init_child_tables(3).master_table := ''BEN_LER_F'';');
1824 add_body_line(' init_child_tables(3).master_key_name := ''LER_ID'';');
1825 add_body_line('');
1826 add_body_line(' init_child_tables(4).table_name := ''BEN_ACTN_TYP_TL'';');
1827 add_body_line(' init_child_tables(4).child_key_name := ''ACTN_TYP_ID'';');
1828 add_body_line(' init_child_tables(4).master_table := ''BEN_ACTN_TYP'';');
1829 add_body_line(' init_child_tables(4).master_key_name := ''ACTN_TYP_ID'';');
1830 add_body_line('');
1831 add_body_line(' init_child_tables(5).table_name := ''BEN_CM_TYP_F_TL'';');
1832 add_body_line(' init_child_tables(5).child_key_name := ''CM_TYP_ID'';');
1833 add_body_line(' init_child_tables(5).master_table := ''BEN_CM_TYP_F'';');
1834 add_body_line(' init_child_tables(5).master_key_name := ''CM_TYP_ID'';');
1835 add_body_line('');
1836 add_body_line(' init_child_tables(6).table_name := ''BEN_REGN_F_TL'';');
1837 add_body_line(' init_child_tables(6).child_key_name := ''REGN_ID'';');
1838 add_body_line(' init_child_tables(6).master_table := ''BEN_REGN_F'';');
1839 add_body_line(' init_child_tables(6).master_key_name := ''REGN_ID'';');
1840 add_body_line(' ');
1841 add_body_line(' FOR t_index IN 1 .. init_child_tables.count LOOP');
1842 add_body_line(' BEGIN');
1843 add_body_line(' EXECUTE IMMEDIATE ''UPDATE '' || init_child_tables(t_index).table_name ||');
1844 add_body_line(' '' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''', :1)'' || ');
1845 add_body_line(' '' WHERE '' || init_child_tables(t_index).child_key_name || '' IN '' || ');
1846 add_body_line(' '' (SELECT '' || init_child_tables(t_index).master_key_name || ');
1847 add_body_line(' '' FROM '' || init_child_tables(t_index).master_table ||');
1848 add_body_line(' '' WHERE business_group_id = :2)''');
1849 add_body_line(' USING l_enterprise_name');
1850 add_body_line(' ,l_business_group_id;');
1851 add_body_line(' EXCEPTION ');
1852 add_body_line(' WHEN OTHERS THEN');
1853 add_body_line(' fnd_file.put_line(fnd_file.log');
1854 add_body_line(' ,''Error in updating:''||init_child_tables(t_index).table_name);');
1855 add_body_line(' END;');
1856 add_body_line(' END LOOP;');
1857 add_body_line('');
1858 add_body_line(' END IF;');
1859 add_body_line(' fnd_file.put_line(fnd_file.log');
1860 add_body_line(' ,''Initializing Control: Enterprise Context'');');
1861 add_body_line('--');
1862 add_body_line('-- In model 1 update entries in the hr_all_organization_units for this enterprise');
1863 add_body_line('--');
1864 add_body_line('EXECUTE IMMEDIATE ''UPDATE hr_all_organization_units');
1865 add_body_line(' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''', :1)');
1866 add_body_line(' WHERE organization_id = :2''');
1867 add_body_line(' USING l_enterprise_name');
1868 add_body_line(' ,p_organization_id;');
1869 add_body_line('');
1870 add_body_line(' UPDATE hr_organization_information');
1871 add_body_line(' SET org_information1 = l_company_label');
1872 add_body_line(' WHERE organization_id = p_organization_id');
1873 add_body_line(' AND org_information_context = ''HR_ENTERPRISE_DETAILS'';');
1874 add_body_line('');
1875 add_body_line('--');
1876 add_body_line('-- In model 1 update entries in the hr_organization_information for this enterprise');
1877 add_body_line('--');
1878 add_body_line('EXECUTE IMMEDIATE ''UPDATE hr_organization_information');
1879 add_body_line(' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',:1 )');
1880 add_body_line(' WHERE organization_id = :2''');
1881 add_body_line(' USING l_enterprise_name');
1882 add_body_line(' ,p_organization_id;');
1883 add_body_line('');
1884 add_body_line('--');
1885 add_body_line('-- In model 1 update entries in the hr_all_organization_units_tl for this enterprise');
1886 add_body_line('--');
1887 add_body_line('IF hr_multi_tenancy_pkg.get_system_model = ''B'' THEN');
1888 add_body_line(' EXECUTE IMMEDIATE ''UPDATE hr_all_organization_units_tl');
1889 add_body_line(' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',:1 )');
1890 add_body_line(' WHERE organization_id = :2''');
1891 add_body_line(' USING l_enterprise_name');
1892 add_body_line(' ,p_organization_id;');
1893 add_body_line('END IF;');
1894 add_body_line('');
1895 add_body_line(' fnd_file.put_line(fnd_file.log');
1896 add_body_line(' ,''Completed Control: Enterprise Context'');');
1897 add_body_line('');
1898 add_body_line(' EXCEPTION');
1899 add_body_line(' WHEN others THEN');
1900 add_body_line(' fnd_file.put_line(fnd_file.output');
1901 add_body_line(' ,''Errored OUT :'' || sqlerrm);');
1902 add_body_line(' fnd_file.put_line(fnd_file.log');
1903 add_body_line(' ,''Errored OUT : '' || sqlerrm);');
1904 add_body_line('');
1905 add_body_line(' END;');
1906 add_body_line(' ELSE');
1907 add_body_line(' fnd_file.put_line(fnd_file.output');
1908 add_body_line(' ,''No organization selected :'');');
1909 add_body_line(' fnd_file.put_line(fnd_file.log');
1910 add_body_line(' ,''No organization selected : '');');
1911 add_body_line('');
1912 add_body_line(' END IF;');
1913 add_body_line(' fnd_file.put_line(fnd_file.log');
1914 add_body_line(' ,''Completed Organization Initialization'');');
1915 add_body_line(' fnd_file.put_line(fnd_file.output');
1916 add_body_line(' ,''Completed Organization Initialization'');');
1917 add_body_line('END initialize_orgs;');
1918 add_body_line('--');
1919 add_body_line('--------------------------------------------------------------------');
1920 add_body_line('--< GET_SCHEMA >----------------------------------------------------');
1921 add_body_line('--------------------------------------------------------------------');
1922 add_body_line('--');
1923 add_body_line('-- Description:');
1924 add_body_line('-- This function will return the oracle user name for the');
1925 add_body_line('-- application short name passed as parameter.');
1926 add_body_line('--');
1927 add_body_line('--');
1928 add_body_line(' function get_schema ');
1929 add_body_line(' (p_app_short_name in varchar2) return varchar2 as');
1930 add_body_line(' l_schema_name varchar2(100);');
1931 add_body_line(' cursor c_schema(c_app_short_name varchar2) is');
1932 add_body_line(' select fou.oracle_username');
1933 add_body_line(' into l_schema_name');
1934 add_body_line(' from fnd_oracle_userid fou');
1935 add_body_line(' ,fnd_product_installations fpi');
1936 add_body_line(' ,fnd_application fap');
1937 add_body_line(' where fou.oracle_id = fpi.oracle_id');
1938 add_body_line(' and fpi.application_id = fap.application_id');
1939 add_body_line(' and fap.application_short_name = c_app_short_name;');
1940 add_body_line(' begin');
1941 add_body_line(' open c_schema(p_app_short_name);');
1942 add_body_line(' fetch c_schema into l_schema_name;');
1943 add_body_line(' close c_schema;');
1944 add_body_line(' return l_schema_name;');
1945 add_body_line('');
1946 add_body_line(' end get_schema;');
1947 add_body_line('');
1948 add_body_line('--');
1949 add_body_line('--------------------------------------------------------------------');
1950 add_body_line('--< MASTER_PROCESS >------------------------------------------------');
1951 add_body_line('--------------------------------------------------------------------');
1952 add_body_line('--');
1953 add_body_line('-- Description:');
1954 add_body_line('-- This procedure is called by the concurrent program');
1958 add_body_line(' ');
1955 add_body_line('-- Setup Multi Tenancy for Database Objects.This concurrent program');
1956 add_body_line('-- spawns child processes in batches of size less or equalt to 100. ');
1957 add_body_line('--');
1959 add_body_line('procedure master_process');
1960 add_body_line(' (errbuf out nocopy varchar2');
1961 add_body_line(' ,retcode out nocopy number');
1962 add_body_line(' ,install_mode in varchar2');
1963 add_body_line(' ,population_size in number default 100) as');
1964 add_body_line(' l_table_count number;');
1965 add_body_line(' l_hr_name varchar2(100);');
1966 add_body_line(' l_ota_name varchar2(100);');
1967 add_body_line(' l_ben_name varchar2(100);');
1968 add_body_line(' l_range number;');
1969 add_body_line(' l_start number;');
1970 add_body_line(' l_end number;');
1971 add_body_line(' l_child_req_id number;');
1972 add_body_line(' l_child_request_status boolean;');
1973 add_body_line(' begin');
1974 add_body_line('--');
1975 add_body_line('-- Fetch the schema names of the applications');
1976 add_body_line('-- that are intended to be OLS applied');
1977 add_body_line('--');
1978 add_body_line(' l_hr_name := get_schema(''PER'');');
1979 add_body_line(' l_ben_name := get_schema(''BEN'');');
1980 add_body_line(' l_ota_name := get_schema(''OTA'');');
1981 add_body_line('');
1982 add_body_line('--');
1983 add_body_line('-- If install mode is SCHEMA then apply policy to all the tables');
1984 add_body_line('-- in the intended schemas. This is for Model 1 only. If the ');
1985 add_body_line('-- install mode is USERTABLE then apply the policy only to tables listed');
1986 add_body_line('-- in the user table HR_OLS_TABLE_LIST');
1987 add_body_line('--');
1988 add_body_line(' if install_mode = ''SCHEMA'' then');
1989 add_body_line(' select count(*) ');
1990 add_body_line(' into l_table_count');
1991 add_body_line(' from all_tables');
1992 add_body_line(' where owner in (l_hr_name, l_ota_name, l_ben_name);');
1993 add_body_line(' elsif install_mode = ''USERTABLE'' then');
1994 add_body_line(' SELECT count(*) ');
1995 add_body_line(' into l_table_count');
1996 add_body_line(' FROM pay_user_rows_f');
1997 add_body_line(' WHERE user_table_id = (SELECT user_table_id');
1998 add_body_line(' FROM pay_user_tables');
1999 add_body_line(' WHERE user_table_name = ''HR_OLS_TABLE_LIST'');');
2000 add_body_line(' end if;');
2001 add_body_line('');
2002 add_body_line(' fnd_file.put_line(fnd_file.log, ''Total Tables : '' || l_table_count);');
2003 add_body_line('');
2004 add_body_line(' l_range := 0;');
2005 add_body_line(' loop');
2006 add_body_line('--');
2007 add_body_line('-- Spawn child processes in specified batch size ');
2008 add_body_line('--');
2009 add_body_line(' if l_range + population_size > l_table_count then');
2010 add_body_line(' l_start := l_range + 1;');
2011 add_body_line(' l_end := l_table_count;');
2012 add_body_line('');
2013 add_body_line(' fnd_file.put_line(fnd_file.log, ''Submitting for range : '' || ');
2014 add_body_line(' to_char(l_start) || '' to '' || to_char(l_end));');
2015 add_body_line('');
2016 add_body_line(' l_child_req_id := fnd_request.submit_request');
2017 add_body_line(' (application => ''PER''');
2018 add_body_line(' ,program => ''HRMTOLSSLV''');
2019 add_body_line(' ,sub_request => false');
2020 add_body_line(' ,argument1 => install_mode');
2021 add_body_line(' ,argument2 => population_size');
2022 add_body_line(' ,argument3 => l_start');
2023 add_body_line(' ,argument4 => l_end);');
2024 add_body_line('');
2025 add_body_line(' commit;');
2026 add_body_line('');
2027 add_body_line(' if l_child_req_id = 0 then');
2028 add_body_line(' fnd_file.put_line(fnd_file.log, ''Error in submitting child request'');');
2029 add_body_line(' else ');
2030 add_body_line(' fnd_file.put_line(fnd_file.log, ''Submitted child request: '' || l_child_req_id);');
2031 add_body_line(' end if;');
2032 add_body_line('');
2033 add_body_line(' exit;');
2034 add_body_line(' else');
2035 add_body_line(' l_start := l_range + 1;');
2036 add_body_line(' l_end := l_range + population_size;');
2037 add_body_line('');
2038 add_body_line(' fnd_file.put_line(fnd_file.log, ''Submitting for range : '' || ');
2039 add_body_line(' to_char(l_start) || '' to '' || to_char(l_end));');
2040 add_body_line('');
2041 add_body_line(' l_child_req_id := fnd_request.submit_request');
2042 add_body_line(' (application => ''PER''');
2043 add_body_line(' ,program => ''HRMTOLSSLV''');
2044 add_body_line(' ,sub_request => false');
2045 add_body_line(' ,argument1 => install_mode');
2046 add_body_line(' ,argument2 => population_size');
2047 add_body_line(' ,argument3 => l_start');
2048 add_body_line(' ,argument4 => l_end);');
2049 add_body_line('');
2050 add_body_line(' commit;');
2051 add_body_line('');
2052 add_body_line(' if l_child_req_id = 0 then');
2053 add_body_line(' fnd_file.put_line(fnd_file.log, ''Error in submitting child request'');');
2054 add_body_line(' else ');
2055 add_body_line(' fnd_file.put_line(fnd_file.log, ''Submitted child request: '' || l_child_req_id);');
2056 add_body_line(' end if;');
2057 add_body_line('');
2058 add_body_line(' end if;');
2059 add_body_line('');
2060 add_body_line(' l_range := l_range + population_size;');
2064 add_body_line(' fnd_concurrent.children_done ');
2061 add_body_line(' end loop;');
2062 add_body_line('');
2063 add_body_line(' l_child_request_status := ');
2065 add_body_line(' (Parent_Request_ID => fnd_global.conc_request_id');
2066 add_body_line(' ,Interval => 20');
2067 add_body_line(' ,Max_Wait => 3600);');
2068 add_body_line('');
2069 add_body_line(' if l_child_request_status then');
2070 add_body_line(' fnd_file.put_line(fnd_file.log, ''Children completed successfully'');');
2071 add_body_line(' else');
2072 add_body_line(' fnd_file.put_line(fnd_file.log, ''Children failed.'');');
2073 add_body_line(' end if;');
2074 add_body_line('');
2075 add_body_line(' end master_process;');
2076 add_body_line('');
2077 add_body_line('--');
2078 add_body_line('--------------------------------------------------------------------');
2079 add_body_line('--< EXECUTE_STATEMENT >------------------------------------------------');
2080 add_body_line('--------------------------------------------------------------------');
2081 add_body_line('--');
2082 add_body_line('-- Description:');
2083 add_body_line('-- This procedure executes three dynamic sql statements. This ');
2084 add_body_line('-- procedure is called in changing table constraints in Model 2.');
2085 add_body_line('--');
2086 add_body_line(' procedure execute_statement (sqlStatement1 IN VARCHAR2');
2087 add_body_line(' ,sqlStatement2 IN VARCHAR2');
2088 add_body_line(' ,sqlStatement3 IN VARCHAR2');
2089 add_body_line(' ,table_name IN VARCHAR2) AS');
2090 add_body_line(' begin');
2091 add_body_line(' begin');
2092 add_body_line(' EXECUTE IMMEDIATE sqlStatement1;');
2093 add_body_line(' exception ');
2094 add_body_line(' when others then');
2095 add_body_line(' fnd_file.put_line(fnd_file.log, ''Exception in 1'' ||table_name );');
2096 add_body_line(' end; ');
2097 add_body_line(' begin');
2098 add_body_line(' EXECUTE IMMEDIATE sqlStatement2;');
2099 add_body_line(' exception ');
2100 add_body_line(' when others then');
2101 add_body_line(' fnd_file.put_line(fnd_file.log, ''Exception in 2'' ||table_name );');
2102 add_body_line(' end; ');
2103 add_body_line(' begin');
2104 add_body_line(' EXECUTE IMMEDIATE sqlStatement3;');
2105 add_body_line(' exception ');
2106 add_body_line(' when others then');
2107 add_body_line(' fnd_file.put_line(fnd_file.log, ''Exception in 3'' ||table_name );');
2108 add_body_line(' end; ');
2109 add_body_line('');
2110 add_body_line('end execute_statement;');
2111 add_body_line('--');
2112 add_body_line('--------------------------------------------------------------------');
2113 add_body_line('--< CHILD_PROCESS >------------------------------------------------');
2114 add_body_line('--------------------------------------------------------------------');
2115 add_body_line('--');
2116 add_body_line('-- Description:');
2117 add_body_line('-- This procedure is called by master_process and is the main');
2118 add_body_line('-- routine which enables the OLS policy on tables depending on the');
2119 add_body_line('-- install mode and the batch size.');
2120 add_body_line('--');
2121 add_body_line(' procedure child_process ');
2122 add_body_line(' (errbuf out nocopy varchar2');
2123 add_body_line(' ,retcode out nocopy number');
2124 add_body_line(' ,install_mode in varchar2');
2125 add_body_line(' ,population_size in number');
2126 add_body_line(' ,population_start in number');
2127 add_body_line(' ,population_end in number) as');
2128 add_body_line(' type mt_table is table of varchar2(256) index by binary_integer;');
2129 add_body_line(' type table_type is table of all_tables.table_name%type;');
2130 add_body_line(' type table_owner_type is table of all_tables.owner%type;');
2131 add_body_line(' type rowtables is table of pay_user_rows_f.row_low_range_or_name%type;');
2132 add_body_line(' type efdtables is table of pay_user_rows_f.effective_start_date%type;');
2133 add_body_line('');
2134 add_body_line(' no_policy_table mt_table;');
2135 add_body_line(' predicate_table mt_table;');
2136 add_body_line(' allowed_table mt_table;');
2137 add_body_line(' all_hr_tables table_type;');
2138 add_body_line(' all_hr_table_owners table_owner_type;');
2139 add_body_line(' rowTab rowTables;');
2140 add_body_line(' efdTab efdTables;');
2141 add_body_line('');
2142 add_body_line(' l_hr_name varchar2(100);');
2143 add_body_line(' l_ota_name varchar2(100);');
2144 add_body_line(' l_ben_name varchar2(100);');
2145 add_body_line(' l_table_name all_tables.table_name%type;');
2146 add_body_line(' l_schema_name all_tables.owner%type;');
2147 add_body_line(' l_tab_enabled varchar2(1);');
2148 add_body_line(' l_np_tab boolean;');
2149 add_body_line(' l_predicate varchar2(240);');
2150 add_body_line('');
2151 add_body_line(' l_table_enabled_value VARCHAR2(4000);');
2152 add_body_line(' l_status VARCHAR2(100); ');
2153 add_body_line(' l_enabled BOOLEAN; ');
2154 add_body_line(' l_effective_start_date DATE;');
2155 add_body_line('');
2156 add_body_line(' cursor acting_tables_schema (c_start number, c_end number, ');
2157 add_body_line(' c_hr_owner varchar2, c_ota_owner varchar2, ');
2158 add_body_line(' c_ben_owner varchar2) is');
2159 add_body_line(' select owner, table_name ');
2160 add_body_line(' from (select owner, ');
2161 add_body_line(' table_name,');
2162 add_body_line(' rownum as colid');
2166 add_body_line(' where colid between c_start and c_end');
2163 add_body_line(' from all_tables');
2164 add_body_line(' where owner in (c_hr_owner, c_ota_owner, c_ben_owner)');
2165 add_body_line(' order by owner, table_name)');
2167 add_body_line(' order by colid;');
2168 add_body_line(' ');
2169 add_body_line(' cursor acting_tables_usertable (c_start number, c_end number) is');
2170 add_body_line(' select row_low_range_or_name, ');
2171 add_body_line(' effective_start_date');
2172 add_body_line(' from (select f.row_low_range_or_name');
2173 add_body_line(' ,f.effective_start_date');
2174 add_body_line(' ,rownum as colid');
2175 add_body_line(' from pay_user_rows_f f, ');
2176 add_body_line(' pay_user_tables t');
2177 add_body_line(' where f.user_table_id = t.user_table_id');
2178 add_body_line(' and t.user_table_name = ''HR_OLS_TABLE_LIST''');
2179 add_body_line(' order by f.row_low_range_or_name)');
2180 add_body_line(' where colid between c_start and c_end');
2181 add_body_line(' order by colid;');
2182 add_body_line('');
2183 add_body_line(' begin');
2184 add_body_line('--');
2185 add_body_line('-- Fetch the schema names for the applications we ');
2186 add_body_line('-- intend to OLS enable.');
2187 add_body_line('--');
2188 add_body_line(' l_hr_name := get_schema(''PER'');');
2189 add_body_line(' l_ben_name := get_schema(''BEN'');');
2190 add_body_line(' l_ota_name := get_schema(''OTA'');');
2191 add_body_line('');
2192 add_body_line('--');
2193 add_body_line('-- There are tables which should not be OLS enabled.');
2194 add_body_line('-- The list contains the name of all these tables.');
2195 add_body_line('--');
2196 add_body_line(' no_policy_table(1) := ''AME_APPROVER_TYPES'';');
2197 add_body_line(' no_policy_table(2) := ''AME_ITEM_CLASSES'';');
2198 add_body_line(' no_policy_table(3) := ''AME_ITEM_CLASSES_TL'';');
2199 add_body_line(' no_policy_table(4) := ''AME_FIELD_HELP'';');
2200 add_body_line(' no_policy_table(5) := ''AME_HELP'';');
2201 add_body_line(' no_policy_table(6) := ''AME_TEMP_HANDLER_STATES'';');
2202 add_body_line('');
2203 add_body_line('--');
2204 add_body_line('-- There are tables which should have a different OLS predicate..');
2205 add_body_line('-- The list contains the name of all these tables.');
2206 add_body_line('--');
2207 add_body_line(' predicate_table(1) :=''PER_NUMBER_GENERATION_CONTROLS'';');
2208 add_body_line(' predicate_table(2) :=''AME_CALLING_APPS'';');
2209 add_body_line(' predicate_table(3) :=''AME_ATTRIBUTE_USAGES'';');
2210 add_body_line(' predicate_table(4) :=''AME_RULES'';');
2211 add_body_line(' predicate_table(5) :=''AME_RULE_USAGES'';');
2212 add_body_line(' predicate_table(6) :=''AME_CONDITION_USAGES'';');
2213 add_body_line(' predicate_table(7) :=''AME_ACTION_USAGES'';');
2214 add_body_line(' predicate_table(8) :=''AME_APPROVAL_GROUPS'';');
2215 add_body_line(' predicate_table(9) :=''AME_APPROVAL_GROUP_CONFIG'';');
2216 add_body_line(' predicate_table(10) :=''AME_APPROVAL_GROUP_ITEMS'';');
2217 add_body_line(' predicate_table(11) :=''AME_APPROVAL_GROUP_MEMBERS'';');
2218 add_body_line(' predicate_table(12) :=''AME_ACTION_TYPE_CONFIG'';');
2219 add_body_line(' predicate_table(13) :=''AME_ITEM_CLASS_USAGES'';');
2220 add_body_line(' predicate_table(14) :=''AME_APPROVAL_GROUPS_TL'';');
2221 add_body_line(' predicate_table(15) :=''AME_CALLING_APPS_TL'';');
2222 add_body_line(' predicate_table(16) :=''AME_RULES_TL'';');
2223 add_body_line(' predicate_table(17) :=''AME_TEST_TRANSACTIONS'';');
2224 add_body_line(' predicate_table(18) :=''AME_TEST_TRANS_ATT_VALUES'';');
2225 add_body_line(' predicate_table(19) :=''AME_TEMP_TRANS_ATT_VALUES'';');
2226 add_body_line(' predicate_table(20) :=''AME_REC_ACCESS_TRANS'';');
2227 add_body_line(' predicate_table(21) :=''AME_TEMP_DELETIONS'';');
2228 add_body_line(' predicate_table(22) :=''AME_TEMP_INSERTIONS'';');
2229 add_body_line(' predicate_table(23) :=''AME_TEMP_OLD_APPROVER_LISTS'';');
2230 add_body_line(' predicate_table(24) :=''AME_TEMP_TRANSACTIONS'';');
2231 add_body_line(' predicate_table(25) :=''AME_TEMP_TRANS_LOCKS'';');
2232 add_body_line(' predicate_table(26) :=''AME_APPROVALS_HISTORY'';');
2233 add_body_line(' predicate_table(27) :=''AME_EXCEPTIONS_LOG'';');
2234 add_body_line(' predicate_table(28) :=''AME_TXN_APPROVERS'';');
2235 add_body_line(' predicate_table(29) :=''AME_TRANS_APPROVAL_HISTORY'';');
2236 add_body_line(' -- Fix for 7645461 ');
2237 add_body_line(' predicate_table(30) :=''HR_NAME_FORMATS''; ');
2238 add_body_line('');
2239 add_body_line('--');
2240 add_body_line('-- There are tables which should not be allowed to be disabled, if they');
2241 add_body_line('-- the policy is applied to them..');
2242 add_body_line('-- The list contains the name of all these tables.');
2243 add_body_line('--');
2244 add_body_line(' allowed_table(1) := ''PER_NUMBER_GENERATION_CONTROLS'';');
2245 add_body_line(' allowed_table(2) := ''HR_LOCATIONS_ALL'';');
2246 add_body_line(' allowed_table(3) := ''PER_GRADES'';');
2247 add_body_line(' allowed_table(4) := ''PER_JOBS'';');
2248 add_body_line(' allowed_table(5) := ''PER_ABSENCE_ATTENDANCE_TYPES'';');
2249 add_body_line(' allowed_table(6) := ''PER_ASSIGNMENT_STATUS_TYPES'';');
2250 add_body_line(' allowed_table(7) := ''PER_PAY_BASES'';');
2251 add_body_line(' allowed_table(8) := ''HR_ALL_ORGANIZATION_UNITS'';');
2252 add_body_line(' allowed_table(9) := ''PER_ALL_POSITIONS'';');
2253 add_body_line(' ');
2254 add_body_line('--');
2255 add_body_line('-- Based on the install mode selectively enable policy on tables ');
2256 add_body_line('--');
2257 add_body_line(' if install_mode =''SCHEMA'' then');
2261 add_body_line(' if fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'')=''P'' then');
2258 add_body_line('--');
2259 add_body_line('-- Schema mode is not a valid option for Model 2 ');
2260 add_body_line('--');
2262 add_body_line(' return;');
2263 add_body_line(' end if;');
2264 add_body_line(' ');
2265 add_body_line('--');
2266 add_body_line('-- Fetch the tables to be applied. This list depends on the');
2267 add_body_line('-- batch start and batch end parameters.');
2268 add_body_line('-- ');
2269 add_body_line('--');
2270 add_body_line(' open acting_tables_schema (');
2271 add_body_line(' c_start => population_start, ');
2272 add_body_line(' c_end => population_end,');
2273 add_body_line(' c_hr_owner => l_hr_name, ');
2274 add_body_line(' c_ota_owner => l_ota_name,');
2275 add_body_line(' c_ben_owner => l_ben_name);');
2276 add_body_line('');
2277 add_body_line(' fetch acting_tables_schema ');
2278 add_body_line(' bulk collect into all_hr_table_owners, all_hr_tables;');
2279 add_body_line('');
2280 add_body_line(' close acting_tables_schema;');
2281 add_body_line('');
2282 add_body_line(' for i in 1 .. all_hr_tables.count loop');
2283 add_body_line(' l_table_name := all_hr_tables(i);');
2284 add_body_line(' l_schema_name := all_hr_table_owners(i);');
2285 add_body_line(' ');
2286 add_body_line(' fnd_file.put_line(fnd_file.log, ''Current Table '' || l_schema_name || ''.'' || l_table_name);');
2287 add_body_line('--');
2288 add_body_line('-- Check if Policy is already applied on this table ');
2289 add_body_line('--');
2290 add_body_line(' begin');
2291 add_body_line(' select ''Y''');
2292 add_body_line(' into l_tab_enabled');
2293 add_body_line(' from all_sa_table_policies');
2294 add_body_line(' where policy_name = ''HR_ENTERPRISE_POLICY''');
2295 add_body_line(' and table_name = l_table_name');
2296 add_body_line(' and schema_name = l_schema_name;');
2297 add_body_line(' exception');
2298 add_body_line(' when no_data_found then');
2299 add_body_line(' l_tab_enabled :=''N''; ');
2300 add_body_line(' end;');
2301 add_body_line('');
2302 add_body_line(' if l_tab_enabled =''N'' then');
2303 add_body_line(' --');
2304 add_body_line(' -- Check if this table needs to be OLS enabled');
2305 add_body_line(' --');
2306 add_body_line(' l_np_tab := false;');
2307 add_body_line('');
2308 add_body_line(' for j in 1..no_policy_table.count loop');
2309 add_body_line(' if l_table_name = no_policy_table(j) then');
2310 add_body_line(' --');
2311 add_body_line(' -- Do not apply policy to this table');
2312 add_body_line(' -- ');
2313 add_body_line(' l_np_tab := true;');
2314 add_body_line(' exit;');
2315 add_body_line(' end if;');
2316 add_body_line(' end loop;');
2317 add_body_line('');
2318 add_body_line(' if not l_np_tab then');
2319 add_body_line(' fnd_file.put_line(fnd_file.log, ''Enabling OLS for '' || l_schema_name || ''.'' || l_table_name);');
2320 add_body_line('');
2321 add_body_line(' l_predicate :=''OR hr_enterprise IS NULL OR hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',''''C::ENT'''')'';');
2322 add_body_line(' --');
2323 add_body_line(' -- Apply to table now using default predicate');
2324 add_body_line(' -- ');
2325 add_body_line(' sa_policy_admin.apply_table_policy');
2326 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
2327 add_body_line(' ,schema_name => l_schema_name');
2328 add_body_line(' ,table_name => l_table_name');
2329 add_body_line(' ,predicate => l_predicate);');
2330 add_body_line('');
2331 add_body_line(' -- Check if table requires special predicate');
2332 add_body_line(' for k in 1..predicate_table.count loop');
2333 add_body_line(' if l_table_name = predicate_table(k) then');
2334 add_body_line(' fnd_file.put_line(fnd_file.log, ''Special OLS for '' || l_schema_name || ''.'' || l_table_name);');
2335 add_body_line(' l_predicate := ''AND HR_ENTERPRISE = '' ||');
2336 add_body_line(' ''char_to_label(''''HR_ENTERPRISE_POLICY'''', SA_SESSION.ROW_LABEL(''''HR_ENTERPRISE_POLICY''''))''; ');
2337 add_body_line(' -- ');
2338 add_body_line(' -- Update null to C::ENT');
2339 add_body_line(' --');
2340 add_body_line(' execute immediate ''UPDATE '' || l_schema_name || ''.'' || l_table_name ||');
2341 add_body_line(' '' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',''''C::ENT'''')'';');
2342 add_body_line('');
2343 add_body_line(' --');
2344 add_body_line(' -- Drop the policy and recreate it with new predicate');
2345 add_body_line(' --');
2346 add_body_line(' sa_policy_admin.remove_table_policy');
2347 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
2348 add_body_line(' ,schema_name => l_schema_name');
2349 add_body_line(' ,table_name => l_table_name');
2350 add_body_line(' ,drop_column => FALSE);');
2351 add_body_line('');
2352 add_body_line(' sa_policy_admin.apply_table_policy');
2353 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
2354 add_body_line(' ,schema_name => l_schema_name');
2355 add_body_line(' ,table_name => l_table_name');
2356 add_body_line(' ,predicate => l_predicate);');
2360 add_body_line(' end loop;');
2357 add_body_line(' ');
2358 add_body_line(' exit;');
2359 add_body_line(' end if;');
2361 add_body_line('');
2362 add_body_line(' -- Fix for Bug 7645461');
2363 add_body_line(' if l_table_name = ''HR_NAME_FORMATS'' then');
2364 add_body_line(' execute immediate ''Drop Index ''|| l_schema_name||''.HR_NAME_FORMATS_U1'';');
2365 add_body_line(' execute immediate ''Create Unique Index ''|| l_schema_name||');
2366 add_body_line(' ''.HR_NAME_FORMATS_U1 On ''|| l_schema_name||''.''||l_table_name||');
2367 add_body_line(' ''(FORMAT_NAME,LEGISLATION_CODE,USER_FORMAT_CHOICE,HR_ENTERPRISE)'';');
2368 add_body_line(' end if;');
2369 add_body_line(' end if;');
2370 add_body_line(' end if;');
2371 add_body_line(' end loop;');
2372 add_body_line(' elsif install_mode =''USERTABLE'' THEN ');
2373 add_body_line(' --');
2374 add_body_line(' -- Installation Module starts for USERTABLE ');
2375 add_body_line(' --');
2376 add_body_line(' ');
2377 add_body_line(' open acting_tables_usertable(c_start => population_start, c_end => population_end);');
2378 add_body_line(' fetch acting_tables_usertable bulk collect into rowtab,efdtab;');
2379 add_body_line(' close acting_tables_usertable;');
2380 add_body_line(' ');
2381 add_body_line(' for tabcnt in rowtab.first .. rowtab.last loop');
2382 add_body_line(' l_table_name := rowTab(tabCnt);');
2383 add_body_line(' l_effective_start_date := efdTab(tabCnt);');
2384 add_body_line(' l_predicate :=''OR hr_enterprise IS NULL OR hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',''''C::ENT'''')'';');
2385 add_body_line(' --');
2386 add_body_line(' -- Get the option provide in user table');
2387 add_body_line(' --');
2388 add_body_line(' BEGIN');
2389 add_body_line(' l_table_enabled_value := hruserdt.get_table_value ');
2390 add_body_line(' (p_bus_group_id => 0');
2391 add_body_line(' ,p_table_name => ''HR_OLS_TABLE_LIST''');
2392 add_body_line(' ,p_col_name => ''Enabled''');
2393 add_body_line(' ,p_row_value => l_table_name');
2394 add_body_line(' ,p_effective_date => l_effective_start_date);');
2395 add_body_line(' l_schema_name := hruserdt.get_table_value ');
2396 add_body_line(' (p_bus_group_id => 0');
2397 add_body_line(' ,p_table_name => ''HR_OLS_TABLE_LIST''');
2398 add_body_line(' ,p_col_name => ''Schema''');
2399 add_body_line(' ,p_row_value => l_table_name');
2400 add_body_line(' ,p_effective_date => l_effective_start_date);');
2401 add_body_line(' EXCEPTION');
2402 add_body_line(' WHEN no_data_found THEN');
2403 add_body_line(' l_table_enabled_value := NULL;');
2404 add_body_line(' ');
2405 add_body_line(' END;');
2406 add_body_line(' ');
2407 add_body_line(' --');
2408 add_body_line(' -- Get the status of applied policy to this table');
2409 add_body_line(' --');
2410 add_body_line(' BEGIN');
2411 add_body_line(' SELECT status');
2412 add_body_line(' INTO l_status');
2413 add_body_line(' FROM all_sa_table_policies ');
2414 add_body_line(' WHERE table_name = l_table_name');
2415 add_body_line(' AND schema_name = l_schema_name');
2416 add_body_line(' AND policy_name =''HR_ENTERPRISE_POLICY'';');
2417 add_body_line(' ');
2418 add_body_line(' l_enabled := TRUE;');
2419 add_body_line(' EXCEPTION');
2420 add_body_line(' WHEN NO_DATA_FOUND THEN');
2421 add_body_line(' l_enabled := FALSE;');
2422 add_body_line(' END;');
2423 add_body_line('');
2424 add_body_line(' --');
2425 add_body_line(' -- Apply the policy to the tables');
2426 add_body_line(' --');
2427 add_body_line(' BEGIN');
2428 add_body_line(' IF NOT l_enabled AND (UPPER(l_table_enabled_value) = ''Y'') THEN');
2429 add_body_line('');
2430 add_body_line(' sa_policy_admin.apply_table_policy');
2431 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
2432 add_body_line(' ,schema_name => l_schema_name');
2433 add_body_line(' ,table_name => l_table_name');
2434 add_body_line(' ,predicate => l_predicate);');
2435 add_body_line(' --');
2436 add_body_line(' -- In PEO mode change the table indexes');
2437 add_body_line(' --');
2438 add_body_line(' IF fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'')=''P'' THEN');
2439 add_body_line(' IF l_table_name = ''HR_LOCATIONS_ALL'' THEN ');
2440 add_body_line(' execute_statement( ''ALTER TABLE '' || l_schema_name || ');
2441 add_body_line(' ''.hr_locations_all DROP CONSTRAINT hr_locations_uk2 CASCADE''');
2442 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.hr_locations_uk2''');
2443 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
2444 add_body_line(' ''.hr_locations_all ADD CONSTRAINT hr_locations_uk2 '' ||');
2445 add_body_line(' '' UNIQUE (location_code, business_group_id,hr_enterprise)''');
2446 add_body_line(' ,l_table_name);');
2447 add_body_line(' ');
2448 add_body_line(' ELSIF l_table_name =''PER_GRADES'' THEN');
2449 add_body_line(' ');
2453 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
2450 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
2451 add_body_line(' ''.per_grades DROP CONSTRAINT PER_GRADES_UK2 CASCADE''');
2452 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.PER_GRADES_UK2''');
2454 add_body_line(' ''.per_grades add CONSTRAINT per_grades_uk2 UNIQUE (name,business_group_id,hr_enterprise)''');
2455 add_body_line(' ,l_table_name); ');
2456 add_body_line(' ELSIF l_table_name =''PER_JOBS'' THEN');
2457 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name ||');
2458 add_body_line(' ''.per_jobs DROP CONSTRAINT per_jobs_uk2 CASCADE''');
2459 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_jobs_uk2''');
2460 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
2461 add_body_line(' ''.per_jobs ADD CONSTRAINT per_jobs_uk2 UNIQUE (name,business_group_id,hr_enterprise)''');
2462 add_body_line(' ,l_table_name); ');
2463 add_body_line(' ELSIF l_table_name =''PER_ABSENCE_ATTENDANCE_TYPES'' THEN');
2464 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
2465 add_body_line(' ''.per_absence_attendance_types DROP CONSTRAINT per_absence_attendance_typ_uk2 CASCADE''');
2466 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_absence_attendance_typ_uk2''');
2467 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ''.per_absence_attendance_types ADD CONSTRAINT per_absence_attendance_typ_uk2 UNIQUE(hr_enterprise, business_group_id, name)''');
2468 add_body_line(' ,l_table_name);');
2469 add_body_line(' ELSIF l_table_name =''PER_ASSIGNMENT_STATUS_TYPES'' THEN');
2470 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
2471 add_body_line(' ''.per_assignment_status_types DROP CONSTRAINT per_assignment_status_type_uk2 CASCADE''');
2472 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_assignment_status_type_uk2''');
2473 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
2474 add_body_line(' ''.per_assignment_status_types ADD CONSTRAINT per_assignment_status_type_uk2 UNIQUE(hr_enterprise, business_group_id, legislation_code, user_status)''');
2475 add_body_line(' ,l_table_name);');
2476 add_body_line(' ELSIF l_table_name =''PER_PAY_BASES'' THEN');
2477 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
2478 add_body_line(' ''.per_pay_bases DROP CONSTRAINT per_pay_bases_uk2 CASCADE''');
2479 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_pay_bases_uk2''');
2480 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
2481 add_body_line(' ''.per_pay_bases ADD CONSTRAINT per_pay_bases_uk2 UNIQUE(hr_enterprise, business_group_id, name)''');
2482 add_body_line(' ,l_table_name);');
2483 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
2484 add_body_line(' ''.per_pay_bases DROP CONSTRAINT per_pay_bases_uk3 CASCADE''');
2485 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_pay_bases_uk3''');
2486 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
2487 add_body_line(' ''.per_pay_bases ADD CONSTRAINT per_pay_bases_uk3 UNIQUE(hr_enterprise, business_group_id, input_value_id, rate_id)''');
2488 add_body_line(' ,l_table_name);');
2489 add_body_line(' ELSIF l_table_name =''HR_ALL_ORGANIZATION_UNITS'' THEN');
2490 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
2491 add_body_line(' ''.hr_all_organization_units DROP CONSTRAINT hr_organization_units_uk2 CASCADE''');
2492 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.hr_organization_units_uk2''');
2493 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
2494 add_body_line(' ''.hr_all_organization_units ADD CONSTRAINT hr_organization_units_uk2 unique (name,business_group_id,hr_enterprise)''');
2495 add_body_line(' ,l_table_name);');
2496 add_body_line(' ELSIF l_table_name =''PER_NUMBER_GENERATION_CONTROLS'' THEN');
2497 add_body_line(' execute immediate ''UPDATE '' || l_schema_name || ''.'' || l_table_name ||');
2498 add_body_line(' '' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',''''C::ENT'''')'';');
2499 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
2500 add_body_line(' ''.per_number_generation_controls DROP CONSTRAINT per_number_generation_cont_pk CASCADE''');
2501 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_number_generation_cont_pk''');
2502 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
2503 add_body_line(' ''.per_number_generation_controls ADD CONSTRAINT per_number_generation_cont_pk PRIMARY KEY (type, business_group_id,hr_enterprise)''');
2504 add_body_line(' ,l_table_name);');
2505 add_body_line(' ');
2506 add_body_line(' ELSIF l_table_name =''PER_ALL_POSITIONS'' THEN');
2507 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
2508 add_body_line(' ''.per_all_positions DROP CONSTRAINT per_positions_uk2 CASCADE''');
2509 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_positions_uk2''');
2510 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
2511 add_body_line(' ''.per_all_positions ADD CONSTRAINT per_positions_uk2 UNIQUE(hr_enterprise, business_group_id, name)''');
2512 add_body_line(' ,l_table_name);');
2513 add_body_line(' ');
2517 add_body_line(' -- ');
2514 add_body_line(' END IF;');add_body_line(' IF l_table_name =''PER_NUMBER_GENERATION_CONTROLS'' THEN');
2515 add_body_line(' l_predicate := ''AND HR_ENTERPRISE = '' ||');
2516 add_body_line(' ''char_to_label(''''HR_ENTERPRISE_POLICY'''', SA_SESSION.ROW_LABEL(''''HR_ENTERPRISE_POLICY''''))''; ');
2518 add_body_line(' -- Update null to C::ENT');
2519 add_body_line(' --');
2520 add_body_line('');
2521 add_body_line(' --');
2522 add_body_line(' -- Drop the policy and recreate it with new predicate');
2523 add_body_line(' --');
2524 add_body_line(' sa_policy_admin.remove_table_policy');
2525 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
2526 add_body_line(' ,schema_name => l_schema_name');
2527 add_body_line(' ,table_name => l_table_name');
2528 add_body_line(' ,drop_column => FALSE);');
2529 add_body_line('');
2530 add_body_line(' sa_policy_admin.apply_table_policy');
2531 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
2532 add_body_line(' ,schema_name => l_schema_name');
2533 add_body_line(' ,table_name => l_table_name');
2534 add_body_line(' ,predicate => l_predicate);');
2535 add_body_line(' END IF;');
2536 add_body_line(' END IF; ');
2537 add_body_line(' ELSIF l_enabled THEN');
2538 add_body_line(' IF (UPPER(l_table_enabled_value) = ''N'') AND l_status= ''ENABLED'' THEN ');
2539 add_body_line(' IF fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'')=''P'' THEN');
2540 add_body_line(' --');
2541 add_body_line(' -- Check if disable is allowed for this table');
2542 add_body_line(' --');
2543 add_body_line(' FOR t_index IN 1 .. allowed_table.COUNT LOOP');
2544 add_body_line(' IF allowed_table(t_index) = l_table_name THEN');
2545 add_body_line(' l_np_tab := TRUE; ');
2546 add_body_line(' EXIT;');
2547 add_body_line(' END IF;');
2548 add_body_line(' END LOOP;');
2549 add_body_line('');
2550 add_body_line(' IF NOT l_np_tab THEN');
2551 add_body_line(' sa_policy_admin.disable_table_policy');
2552 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
2553 add_body_line(' ,schema_name => l_schema_name');
2554 add_body_line(' ,table_name => l_table_name');
2555 add_body_line(' );');
2556 add_body_line(' END IF; ');
2557 add_body_line(' ELSIF fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'')=''B'' THEN ');
2558 add_body_line(' sa_policy_admin.disable_table_policy');
2559 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
2560 add_body_line(' ,schema_name => l_schema_name');
2561 add_body_line(' ,table_name => l_table_name');
2562 add_body_line(' );');
2563 add_body_line(' END IF; ');
2564 add_body_line(' ELSIF (UPPER(l_table_enabled_value) = ''Y'') AND l_status= ''DISABLED'' THEN');
2565 add_body_line(' --');
2566 add_body_line(' -- Enable policy');
2567 add_body_line(' --');
2568 add_body_line(' sa_policy_admin.enable_TABLE_POLICY');
2569 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
2570 add_body_line(' ,schema_name => l_schema_name');
2571 add_body_line(' ,table_name => l_table_name);');
2572 add_body_line(' END IF;');
2573 add_body_line(' END IF;');
2574 add_body_line(' END;');
2575 add_body_line(' END LOOP;');
2576 add_body_line(' end if;');
2577 add_body_line(' exception');
2578 add_body_line(' when others then');
2579 add_body_line(' fnd_file.put_line(fnd_file.log,''Exception caught on: '' || fnd_global.conc_request_id);');
2580 add_body_line(' fnd_file.put_line(fnd_file.log,''l_table_name: '' || l_table_name); ');
2581 add_body_line(' fnd_file.put_line(fnd_file.log,''sqerrmessage: '' || sqlerrm);');
2582 add_body_line(' end child_process;');
2583 add_body_line('');
2584 add_body_line('');
2585 add_body_line('END '||l_package_name ||';');
2586 add_header_line('END ' ||l_package_name||';');
2587
2588 l_csr_sql := dbms_sql.open_cursor;
2589 dbms_sql.parse( l_csr_sql, l_header,0,l_header_line-1,FALSE, dbms_sql.v7 );
2590 l_rows := dbms_sql.EXECUTE( l_csr_sql );
2591 dbms_sql.close_cursor( l_csr_sql );
2592
2593 l_csr_sql := dbms_sql.open_cursor;
2594 dbms_sql.parse( l_csr_sql, l_body,0,l_body_line-1,FALSE, dbms_sql.v7 );
2595 l_rows := dbms_sql.EXECUTE( l_csr_sql );
2596 dbms_sql.close_cursor( l_csr_sql );
2597
2598 EXCEPTION
2599 WHEN OTHERS THEN
2600 fnd_file.put_line(fnd_file.output,'Error occured in installation of HR Multi tenancy '|| sqlerrm);
2601 END install_hr_multi_tenant;
2602 END hr_multi_tenant_installer;