[Home] [Help]
PACKAGE BODY: APPS.HR_MULTI_TENANT_INSTALLER
Source
1 PACKAGE BODY hr_multi_tenant_installer AS
2 /* $Header: pemtstup.pkb 120.9 2010/11/19 09:17:39 psengupt 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_components.create_group(''HR_ENTERPRISE_POLICY'',1,''GLOBAL'',''Global Group'');');
228 add_body_line('sa_user_admin.set_user_privs(''HR_ENTERPRISE_POLICY'',''' || get_schema_by_oracleid(900) || ''',''PROFILE_ACCESS'');');
229 add_body_line('sa_user_admin.set_user_privs(''HR_ENTERPRISE_POLICY'',''' || get_schema('PER') || ''',''PROFILE_ACCESS'');');
230 add_body_line('SELECT to_data_label(''HR_ENTERPRISE_POLICY'',''C::ENT'') INTO dummy FROM dual;');
231 add_body_line('SELECT to_data_label(''HR_ENTERPRISE_POLICY'',''C::GLOBAL'') INTO dummy FROM dual;');
232 add_body_line('sa_user_admin.set_user_labels(policy_name => ''HR_ENTERPRISE_POLICY'', user_name => ''' || get_schema_by_oracleid(900) || ''', max_read_label => ''C::ENT,GLOBAL'', row_label => ''C::GLOBAL'');');
233 add_body_line('sa_user_admin.set_user_labels(policy_name => ''HR_ENTERPRISE_POLICY'', user_name => ''' || get_schema('PER') || ''', max_read_label => ''C::ENT,GLOBAL'', row_label => ''C::GLOBAL'');');
234 add_body_line('sa_user_admin.set_user_labels(policy_name => ''HR_ENTERPRISE_POLICY'', user_name => ''C::ENT'', max_read_label => ''C::ENT,GLOBAL'', row_label => ''C::GLOBAL'');');
235 add_body_line('END;');
236
237 --
238 -- Execute the dynamic code.
239 --
240
241 l_csr_sql := dbms_sql.open_cursor;
242 dbms_sql.parse( l_csr_sql, l_body,0,l_body_line-1,FALSE, dbms_sql.v7 );
243 l_rows := dbms_sql.EXECUTE( l_csr_sql );
244 dbms_sql.close_cursor( l_csr_sql );
245
246 --
247 -- Reinitialize the variables.
248 --
249
250 l_body_line :=0;
251 l_header_line :=0;
252
253 END IF;
254 --
255 -- Create or replace the hr_multi_tenancy_pkg package
256 --
257
258 l_package_name := 'hr_multi_tenancy_pkg';
259 add_header_line('CREATE OR REPLACE PACKAGE '||l_package_name||' AS ');
260 add_body_line('CREATE OR REPLACE PACKAGE BODY '||l_package_name||' AS ');
261 add_header_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
262 add_header_line('/'||'* $Header: pemtstup.pkb 120.0.12010000.37 2008/11/24 10:27:27 bchakrab noship*'||'/');
263 add_body_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
264 add_body_line('/'||'* $Header: pemtstup.pkb 120.0.12010000.37*'||'/');
265 add_header_line('--');
266 add_header_line('-- Name');
267 add_header_line('-- is_multi_tenant_system');
268 add_header_line('--');
269 add_header_line('-- Purpose');
270 add_header_line('-- All PL/SQL Code modified for enterprise should be wrapped IN a call ');
271 add_header_line('-- to this method.');
272 add_header_line('-- Called From');
273 add_header_line('-- 1. hr_signon ');
274 add_header_line('-- Arguments');
275 add_header_line('-- None.');
276 add_header_line('--');
277 add_header_line(' ');
278 add_header_line(' FUNCTION is_multi_tenant_system RETURN boolean;');
279 add_header_line('--');
280 add_header_line('-- Name');
281 add_header_line('-- get_system_model');
282 add_header_line('--');
283 add_header_line('-- Purpose');
284 add_header_line('-- The function returns the System model as P/B or null');
285 add_header_line('-- ');
286 add_header_line('-- Called From');
287 add_header_line('-- 1. Assign Security Profiles form');
288 add_header_line('-- Arguments');
289 add_header_line('-- None.');
290 add_header_line('--');
291 add_header_line(' ');
292 add_header_line(' FUNCTION get_system_model RETURN varchar2;');
293 add_header_line('');
294 add_header_line('PROCEDURE process_enterprise ( p_enterprise_code IN varchar2 default null, p_enterprise_id number);');
295 add_header_line('');
296 add_header_line(' --');
297 add_header_line(' -- Name');
298 add_header_line(' -- get_enterprise_for_bg');
299 add_header_line(' --');
300 add_header_line(' -- Purpose');
301 add_header_line(' -- The function returns the short code of the enterprise to which ');
302 add_header_line(' -- the business group belongs');
303 add_header_line(' -- Arguments');
304 add_header_line(' -- person_id');
305 add_header_line(' --');
306 add_header_line(' function get_enterprise_for_bg(p_bg_id number) return varchar2; ');
307 add_header_line('');
308 add_header_line(' --');
309 add_header_line(' -- Name');
310 add_header_line(' -- get_enterprise_for_person');
311 add_header_line(' --');
312 add_header_line(' -- Purpose');
313 add_header_line(' -- The function returns the short code of the enterprise to which ');
314 add_header_line(' -- the person belongs');
315 add_header_line(' -- Arguments');
316 add_header_line(' -- person_id');
317 add_header_line(' --');
318 add_header_line(' function get_enterprise_for_person(p_person_id number) return varchar2;');
319 add_header_line('');
320 add_header_line('--');
321 add_header_line('-- Name');
322 add_header_line('-- set_context');
323 add_header_line('--');
324 add_header_line('-- Purpose');
325 add_header_line('-- The procedure sets/resets OLS enterprise context.');
326 add_header_line('-- ');
327 add_header_line('-- Called From');
328 add_header_line('-- 1. hr_signon with argument null.');
329 add_header_line('-- 2. HRMultiTenancyHelper.java(#resetContext) with argument ENT.');
330 add_header_line('-- Arguments');
331 add_header_line('-- p_context_value => context label to set');
332 add_header_line('--');
333 add_header_line(' ');
334 add_header_line(' PROCEDURE set_context (p_context_value IN VARCHAR2);');
335 add_header_line('');
336 add_header_line(' --');
337 add_header_line(' --------------------------------------------------------------------');
338 add_header_line(' --< set_context_for_person >----------------------------------------');
339 add_header_line(' --------------------------------------------------------------------');
340 add_header_line(' --');
341 add_header_line(' -- Description:');
342 add_header_line(' -- This is a public procedure to set the appropriate Context value');
343 add_header_line(' -- for a person');
344 add_header_line(' --');
345 add_header_line(' PROCEDURE set_context_for_person (p_person_id IN NUMBER);');
346 add_header_line('');
347 add_header_line(' --');
348 add_header_line(' --------------------------------------------------------------------');
349 add_header_line(' --< set_context_for_enterprise >----------------------------------------');
350 add_header_line(' --------------------------------------------------------------------');
351 add_header_line(' --');
352 add_header_line(' -- Description:');
353 add_header_line(' -- This is a public procedure to set the appropriate Context value');
354 add_header_line(' -- for a given enterprise short code');
355 add_header_line(' --');
356 add_header_line(' PROCEDURE set_context_for_enterprise (p_enterprise_short_code IN VARCHAR2);');
357 add_header_line('');
358 add_header_line('--');
359 add_header_line('-- Name');
360 add_header_line('-- get_corporate_branding');
361 add_header_line('--');
362 add_header_line('-- Purpose');
363 add_header_line('-- Gets the corporate branding for the passed/current enterprise.');
364 add_header_line('-- ');
365 add_header_line('-- Called From');
366 add_header_line('-- 1. HRApplicationModuleImpl.java without argument');
367 add_header_line('-- 2. MTHomeAMImpl.java with argument.');
368 add_header_line('-- Arguments');
369 add_header_line('-- p_organization_id => organization id of the current enterprise');
370 add_header_line('--');
371 add_header_line(' ');
375 add_header_line('-- Name');
372 add_header_line(' FUNCTION get_corporate_branding (p_organization_id IN VARCHAR2 default null) RETURN VARCHAR2;');
373 add_header_line('');
374 add_header_line('--');
376 add_header_line('-- get_bus_grp_from_sec_grp');
377 add_header_line('--');
378 add_header_line('-- Purpose');
379 add_header_line('-- Called from HR_SIGNON to RETURN the business group corresponding');
380 add_header_line('-- to the enterprise security group for buisness group initialization. ');
381 add_header_line('--');
382 add_header_line('-- Called From');
383 add_header_line('-- 1. HR_SIGNON ');
384 add_header_line('-- Arguments');
385 add_header_line('-- p_security_group_id => security group id of the current enterprise');
386 add_header_line('--');
387 add_header_line(' FUNCTION get_bus_grp_from_sec_grp (p_security_group_id IN NUMBER) RETURN NUMBER;');
388 add_header_line('');
389 add_header_line('--');
390 add_header_line('-- Name');
391 add_header_line('-- set_security_group_id');
392 add_header_line('--');
393 add_header_line('-- Purpose');
394 add_header_line('-- Called from HR_API to set proper security group. ');
395 add_header_line('--');
396 add_header_line('-- Called From');
397 add_header_line('-- 1. HR_API ');
398 add_header_line('-- Arguments');
399 add_header_line('-- p_security_group_id => security group id of the current enterprise');
400 add_header_line('--');
401 add_header_line(' PROCEDURE set_security_group_id (p_security_group_id IN NUMBER);');
402 add_header_line('');
403 add_header_line('--');
404 add_header_line('-- Name');
405 add_header_line('-- get_org_id_for_person');
406 add_header_line('--');
407 add_header_line('-- Purpose');
408 add_header_line('-- Used IN SSHR to derive the security group from person ');
409 add_header_line('-- i.e. (Notifications/Workflow)');
410 add_header_line('--');
411 add_header_line('-- Arguments');
412 add_header_line('-- p_person_id => id of the selected person');
413 add_header_line('-- ');
414 add_header_line('');
415 add_header_line(' FUNCTION get_org_id_for_person (p_person_id IN NUMBER) RETURN NUMBER;');
416 add_header_line('');
417 add_header_line('--');
418 add_header_line('-- Name');
419 add_header_line('-- get_org_id_for_person');
420 add_header_line('--');
421 add_header_line('-- Purpose');
422 add_header_line('-- Used IN SSHR New Hire flow ');
423 add_header_line('-- Gets the HR Enterprise Organization ID IN the passed business group belonging to');
424 add_header_line('-- to the same enterprise as the passed HR Person.');
425 add_header_line('--');
426 add_header_line('-- Arguments');
427 add_header_line('-- p_person_id => id of the selected person');
428 add_header_line('-- p_business_group_id => id of the selected business group.');
429 add_header_line('-- ');
430 add_header_line(' FUNCTION get_org_id_for_person (p_person_id IN NUMBER');
431 add_header_line(' ,p_business_group_id IN NUMBER) RETURN NUMBER;');
432 add_header_line('');
433 add_header_line('--');
434 add_header_line('-- Name');
435 add_header_line('-- get_label_from_bg');
436 add_header_line('--');
437 add_header_line('-- Purpose');
438 add_header_line('-- Called From PerAppModuleHelper ');
439 add_header_line('-- to get label from BG');
440 add_header_line('--');
441 add_header_line('-- Arguments');
442 add_header_line('-- p_business_group_id => id of the selected business group.');
443 add_header_line('-- ');
444 add_header_line(' FUNCTION get_label_from_bg (p_business_group_id IN NUMBER) RETURN VARCHAR2;');
445 add_header_line('');
446 add_header_line(' --');
447 add_header_line(' -- Name');
448 add_header_line(' -- get_org_id_from_bg_and_sl');
449 add_header_line(' --');
450 add_header_line(' -- Purpose');
451 add_header_line(' -- Called From PerAppModuleHelper');
452 add_header_line(' -- to get orgid from BG and security label');
453 add_header_line(' --');
454 add_header_line(' -- Arguments');
455 add_header_line(' -- p_business_group_id => id of the selected business group.');
456 add_header_line(' -- p_security_label => security label.');
457 add_header_line(' --');
458 add_header_line(' FUNCTION get_org_id_from_bg_and_sl (p_business_group_id IN NUMBER');
459 add_header_line(' ,p_security_label IN VARCHAR2) RETURN NUMBER;');
460 add_header_line('');
461 add_header_line('--');
462 add_header_line('-- Name');
463 add_header_line('-- is_valid_sec_group');
464 add_header_line('--');
465 add_header_line('-- Purpose');
466 add_header_line('-- Returns Y/N if it is a valid security group');
467 add_header_line('--');
468 add_header_line('-- Arguments');
469 add_header_line('-- p_security_group_id => security group id');
470 add_header_line('-- p_business_group_id => id of the selected business group.');
471 add_header_line('--');
472 add_header_line(' FUNCTION is_valid_sec_group (p_security_group_id IN NUMBER');
473 add_header_line(' ,p_business_group_id IN NUMBER) RETURN VARCHAR2;');
474 add_header_line('');
475 add_header_line(' --');
476 add_header_line(' -- Name');
477 add_header_line(' -- add_language');
478 add_header_line(' --');
479 add_header_line(' -- Purpose');
480 add_header_line(' -- Updates TL table');
481 add_header_line(' --');
482 add_header_line(' -- Arguments');
483 add_header_line(' -- None');
484 add_header_line(' --');
485 add_header_line(' PROCEDURE add_language;');
486 add_header_line('');
487
488 add_body_line('/*FUNCTION get_label_from_secgrp');
492 add_body_line(' BEGIN');
489 add_body_line(' (p_security_group_id IN NUMBER) RETURN VARCHAR2 AS');
490 add_body_line(' l_security_group_key VARCHAR2(30);');
491 add_body_line(' l_enterprise_label VARCHAR2(4000);');
493 add_body_line(' BEGIN');
494 add_body_line(' SELECT security_group_key');
495 add_body_line(' INTO l_security_group_key');
496 add_body_line(' FROM fnd_security_groups');
497 add_body_line(' WHERE security_group_id = p_security_group_id;');
498 add_body_line(' EXCEPTION');
499 add_body_line(' WHEN no_data_found THEN');
500 add_body_line(' l_security_group_key := NULL;');
501 add_body_line(' WHEN too_many_rows THEN');
502 add_body_line(' l_security_group_key := NULL;');
503 add_body_line(' END;');
504 add_body_line('');
505 add_body_line(' BEGIN');
506 add_body_line(' SELECT enterprise_label');
507 add_body_line(' INTO l_enterprise_label');
508 add_body_line(' FROM per_ent_security_groups');
509 add_body_line(' WHERE to_char(organization_id) = l_security_group_key');
510 add_body_line(' AND enabled_flag = ''Y'';');
511 add_body_line(' EXCEPTION');
512 add_body_line(' WHEN no_data_found THEN');
513 add_body_line(' l_enterprise_label := ''ENT'';');
514 add_body_line(' END;');
515 add_body_line('');
516 add_body_line(' IF instr(l_enterprise_label, ''C::'') = 0 THEN');
517 add_body_line(' l_enterprise_label := ''C::'' || l_enterprise_label;');
518 add_body_line(' END IF;');
519 add_body_line('');
520 add_body_line(' RETURN l_enterprise_label;');
521 add_body_line(' END get_label_from_secgrp;*/');
522 add_body_line('');
523 add_body_line('-----------------------------------------------------------------------');
524 add_body_line('-----------------------------------------------------------------------');
525 add_body_line('-- PRIVATE FUNCTIONS');
526 add_body_line('-----------------------------------------------------------------------');
527 add_body_line('-----------------------------------------------------------------------');
528 add_body_line('');
529 add_body_line('--');
530 add_body_line('--------------------------------------------------------------------');
531 add_body_line('--< get_label_from_bg >-----------------------------------------------------');
532 add_body_line('--------------------------------------------------------------------');
533 add_body_line('--');
534 add_body_line('-- Description:');
535 add_body_line('-- This function is Called From PerAppModuleHelper ');
536 add_body_line('-- to get label from BG');
537 add_body_line('--');
538 add_body_line('-- Arguments');
539 add_body_line('-- p_business_group_id => id of the selected business group.');
540 add_body_line('--');
541 add_body_line('');
542 add_body_line('FUNCTION get_label_from_bg (p_business_group_id IN NUMBER) RETURN VARCHAR2 IS');
543 add_body_line(' l_enterprise_label VARCHAR2(4000);');
544 add_body_line('');
545 add_body_line(' CURSOR label_bg (p_business_group_id NUMBER) IS');
546 add_body_line(' SELECT enterprise_label');
547 add_body_line(' FROM per_ent_security_groups');
548 add_body_line(' WHERE business_group_id = p_business_group_id');
549 add_body_line(' AND enabled_flag = ''Y'';');
550 add_body_line('');
551 add_body_line('BEGIN');
552 add_body_line(' OPEN label_bg (p_business_group_id => p_business_group_id);');
553 add_body_line(' FETCH label_bg INTO l_enterprise_label;');
554 add_body_line(' IF label_bg%notfound THEN');
555 add_body_line(' l_enterprise_label := ''ENT'';');
556 add_body_line(' END IF;');
557 add_body_line(' RETURN ''C::'' || l_enterprise_label;');
558 add_body_line('END get_label_from_bg;');
559 add_body_line('');
560 add_body_line(' /*FUNCTION get_label_from_session RETURN VARCHAR2 IS');
561 add_body_line(' l_security_group_id NUMBER;');
562 add_body_line(' BEGIN');
563 add_body_line(' l_security_group_id := fnd_global.security_group_id;');
564 add_body_line(' RETURN get_label_from_secgrp');
565 add_body_line(' (p_security_group_id => l_security_group_id);');
566 add_body_line(' END get_label_from_session;*/');
567 add_body_line('');
568 add_body_line('--');
569 add_body_line('--------------------------------------------------------------------');
570 add_body_line('--< init_profiles >-----------------------------------------------------');
571 add_body_line('--------------------------------------------------------------------');
572 add_body_line('--');
573 add_body_line('-- Description:');
574 add_body_line('-- This is a private procedure that is used ');
575 add_body_line('-- to initialize the profile values for the enterprise');
576 add_body_line('--');
577 add_body_line('--');
578 add_body_line('PROCEDURE init_profiles AS');
579 add_body_line(' CURSOR csr_profile_value IS');
580 add_body_line(' SELECT description, meaning');
581 add_body_line(' FROM fnd_lookup_values');
582 add_body_line(' WHERE lookup_type = ''PER_MT_VALUES'' ');
583 add_body_line(' AND enabled_flag = ''Y'' ');
584 add_body_line(' AND language = userenv(''LANG'') ');
585 add_body_line(' AND view_application_id = 3 ');
586 add_body_line(' AND security_group_id = fnd_global.lookup_security_group(lookup_type');
587 add_body_line(' ,view_application_id);');
588 add_body_line('BEGIN');
589 add_body_line(' FOR rec IN csr_profile_value LOOP');
590 add_body_line(' fnd_profile.put(rec.meaning, rec.description);');
591 add_body_line(' END LOOP;');
592 add_body_line('END init_profiles;');
593 add_body_line('');
594 add_body_line('--');
598 add_body_line('--');
595 add_body_line('--------------------------------------------------------------------');
596 add_body_line('--< init_context >-----------------------------------------------------');
597 add_body_line('--------------------------------------------------------------------');
599 add_body_line('-- Description:');
600 add_body_line('-- This is a private procedure that is used ');
601 add_body_line('-- to initialize the OLS context');
602 add_body_line('--');
603 add_body_line('--');
604 add_body_line('');
605 add_body_line('PROCEDURE init_context (p_context IN VARCHAR2) AS');
606 add_body_line(' l_current_context VARCHAR2 (4000);');
607 add_body_line(' l_session_context VARCHAR2 (4000);');
608 add_body_line('BEGIN');
609 add_body_line('--');
610 add_body_line('-- If it is not a multi tenant solution then do not do anything ');
611 add_body_line('--');
612 add_body_line(' IF NOT is_multi_tenant_system THEN');
613 add_body_line(' RETURN;');
614 add_body_line(' END IF;');
615 add_body_line('');
616 add_body_line(' l_current_context := p_context;');
617 add_body_line('--');
618 add_body_line('-- This is to support STANDARD ');
619 add_body_line('--');
620 add_body_line(' IF l_current_context IS NULL THEN');
621 add_body_line(' l_current_context := ''C::ENT'';');
622 add_body_line(' END IF;');
623 add_body_line('');
624 add_body_line(' IF instr(l_current_context, ''C::'') = 0 THEN');
625 add_body_line(' l_current_context := ''C::'' || l_current_context;');
626 add_body_line(' END IF;');
627 add_body_line('');
628 add_body_line(' l_session_context := sa_session.row_label(''HR_ENTERPRISE_POLICY'');');
629 add_body_line('');
630 add_body_line('--');
631 add_body_line('-- Only if context is different from the present context ');
632 add_body_line('-- do the processing');
633 add_body_line('--');
634 add_body_line(' IF l_session_context IS NULL OR ');
635 add_body_line(' l_session_context <> l_current_context THEN');
636 add_body_line(' BEGIN');
637 add_body_line(' sa_session.set_access_profile(''HR_ENTERPRISE_POLICY'', l_current_context);');
638 add_body_line(' IF l_current_context <> ''C::ENT'' THEN');
639 add_body_line(' init_profiles;');
640 add_body_line(' END IF;');
641 add_body_line(' EXCEPTION');
642 add_body_line(' WHEN others THEN');
643 add_body_line('--');
644 add_body_line('-- This is an error condition');
645 add_body_line('--');
646 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
647 add_body_line(' fnd_message.set_token (''ERRMSG'' , sqlerrm);');
648 add_body_line(' fnd_message.raise_error;');
649 add_body_line(' -- Temporary Fix : Fallback code added');
650 add_body_line(' -- sa_session.set_access_profile(''HR_ENTERPRISE_POLICY'', ''C::ENT'');');
651 add_body_line(' END;');
652 add_body_line(' END IF;');
653 add_body_line('END init_context;');
654 add_body_line('');
655 add_body_line(' --');
656 add_body_line(' --------------------------------------------------------------------');
657 add_body_line(' --< init_context_from_secgrp >-----------------------------------------------------');
658 add_body_line(' --------------------------------------------------------------------');
659 add_body_line(' --');
660 add_body_line(' -- Description:');
661 add_body_line(' -- This is a private procedure that is used ');
662 add_body_line(' -- to initialize the OLS context from the security group id passed ');
663 add_body_line(' -- as parameter.');
664 add_body_line(' -- Arguments');
665 add_body_line(' -- p_security_group_id => id of the current security group');
666 add_body_line(' --');
667 add_body_line('');
668 add_body_line('PROCEDURE init_context_from_secgrp (p_security_group_id IN NUMBER) AS');
669 add_body_line(' l_security_label VARCHAR2(4000);');
670 add_body_line('BEGIN');
671 add_body_line(' IF NOT is_multi_tenant_system THEN ');
672 add_body_line(' RETURN;');
673 add_body_line(' END IF;');
674 add_body_line(' init_context (p_context => ''ENT'');');
675 add_body_line(' BEGIN');
676 add_body_line(' SELECT enterprise_label');
677 add_body_line(' INTO l_security_label');
678 add_body_line(' FROM per_ent_security_groups');
679 add_body_line(' WHERE security_group_id = p_security_group_id');
680 add_body_line(' AND enabled_flag = ''Y'';');
681 add_body_line(' EXCEPTION');
682 add_body_line(' WHEN no_data_found THEN');
683 add_body_line(' -- This can happen under three conditions.');
684 add_body_line(' -- 1. The security group corresponds to a business group.');
685 add_body_line(' -- 2. The security group IS NOT related to HR setup.');
686 add_body_line(' -- 3. The enterprise security group IS disabled.');
687 add_body_line(' ');
688 add_body_line(' -- For case 3 we need to raise error');
689 add_body_line(' BEGIN');
690 add_body_line(' SELECT enterprise_label');
691 add_body_line(' INTO l_security_label');
692 add_body_line(' FROM per_ent_security_groups');
693 add_body_line(' WHERE security_group_id = p_security_group_id;');
694 add_body_line(' -- The security group IS disabled.');
695 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
696 add_body_line(' fnd_message.set_token (''ERRMSG'' , ''Security Group '' || p_security_group_id || '' IS disabled.'');');
697 add_body_line(' fnd_message.raise_error;');
698 add_body_line(' EXCEPTION');
699 add_body_line(' WHEN no_data_found THEN');
703 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
700 add_body_line(' RETURN;');
701 add_body_line(' END;');
702 add_body_line(' WHEN too_many_rows THEN');
704 add_body_line(' fnd_message.set_token (''ERRMSG'' , sqlerrm);');
705 add_body_line(' fnd_message.raise_error;');
706 add_body_line(' END;');
707 add_body_line('');
708 add_body_line(' init_context (p_context => l_security_label);');
709 add_body_line('END init_context_from_secgrp;');
710 add_body_line('');
711 add_body_line('--');
712 add_body_line('--------------------------------------------------------------------');
713 add_body_line('--< init_context_from_apps_context >--------------------------------');
714 add_body_line('--------------------------------------------------------------------');
715 add_body_line('--');
716 add_body_line('-- Description:');
717 add_body_line('-- This is a private procedure that is used ');
718 add_body_line('-- to initialize the OLS context from the apps context using fnd_global ');
719 add_body_line('-- security group id.');
720 add_body_line('--');
721 add_body_line('');
722 add_body_line(' PROCEDURE init_context_from_apps_context AS');
723 add_body_line(' l_security_group_id NUMBER;');
724 add_body_line(' l_sgid_char varchar2(10);');
725 add_body_line(' BEGIN');
726 add_body_line(' l_security_group_id := fnd_global.security_group_id;');
727 add_body_line('');
728 add_body_line(' -- As we set the security id in client_info only in set_security_group_id ');
729 add_body_line(' -- procedure in hr_multi_tenancy_pkg, we get the security group id from ');
730 add_body_line(' -- client info, but not fnd_global.security_group_id');
731 add_body_line(' if l_security_group_id = 0 then');
732 add_body_line(' l_sgid_char := substr(userenv(''CLIENT_INFO''),55,10);');
733 add_body_line(' if l_sgid_char is null or l_sgid_char = '' '' or l_sgid_char = ''0'' then');
734 add_body_line(' l_security_group_id := 0;');
735 add_body_line(' else');
736 add_body_line(' l_security_group_id := to_number(l_sgid_char);');
737 add_body_line(' end if;');
738 add_body_line(' end if;');
739 add_body_line(' init_context_from_secgrp (p_security_group_id => l_security_group_id);');
740 add_body_line(' END init_context_from_apps_context;');
741 add_body_line('');
742 add_body_line('--');
743 add_body_line('--------------------------------------------------------------------');
744 add_body_line('--< is_multi_tenant_system >--------------------------------------');
745 add_body_line('--------------------------------------------------------------------');
746 add_body_line('--');
747 add_body_line('-- Description:');
748 add_body_line('-- This is a public function that is used as a wrapper for all calls');
749 add_body_line('-- In normal instance with HR_ENABLE_MULTI_TENANCY not set, the function ');
750 add_body_line('-- returns false. In this case, normal functionality should continued.');
751 add_body_line('-- In case of a multi tenanat system, the function returns false and');
752 add_body_line('-- the multi tenancy specific solution comes into play.');
753 add_body_line('--');
754 add_body_line('FUNCTION is_multi_tenant_system RETURN BOOLEAN AS');
755 add_body_line(' l_profile_value VARCHAR2 (255);');
756 add_body_line('BEGIN');
757 add_body_line(' l_profile_value := fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'');');
758 add_body_line('');
759 add_body_line(' IF l_profile_value = ''P'' OR');
760 add_body_line(' l_profile_value = ''B'' THEN');
761 add_body_line(' RETURN true;');
762 add_body_line(' END IF;');
763 add_body_line(' RETURN false;');
764 add_body_line('END is_multi_tenant_system;');
765 add_body_line('');
766 add_body_line('--');
767 add_body_line('--------------------------------------------------------------------');
768 add_body_line('--< get_system_model >----------------------------------------------');
769 add_body_line('--------------------------------------------------------------------');
770 add_body_line('--');
771 add_body_line('-- Description:');
772 add_body_line('-- This is a public function that returns whether the system is a BPO');
773 add_body_line('-- Model 1 or PEO Model2 . In case of Model 1 the profile value of ');
774 add_body_line('-- HR_ENABLE_MULTI_TENANCY should be set B while for model 2, the value ');
775 add_body_line('-- should be P. For non multi tenant systems it must be null.');
776 add_body_line('--');
777 add_body_line(' FUNCTION get_system_model RETURN VARCHAR2 AS');
778 add_body_line(' l_system_model VARCHAR2(255);');
779 add_body_line(' BEGIN');
780 add_body_line(' l_system_model := fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'');');
781 add_body_line(' ');
782 add_body_line(' IF l_system_model IS NULL THEN');
783 add_body_line(' RETURN ''N'';');
784 add_body_line(' ELSE');
785 add_body_line(' RETURN l_system_model;');
786 add_body_line(' END IF;');
787 add_body_line(' ');
788 add_body_line(' END get_system_model;');
789 add_body_line('');
790 add_body_line('PROCEDURE insert_hr_name_formats ( p_enterprise_code IN varchar2)');
791 add_body_line(' AS');
792 add_body_line(' TYPE hr_name_format_rec IS');
793 add_body_line(' RECORD (format_name VARCHAR2(80)');
794 add_body_line(' ,legislation_code VARCHAR2(30)');
795 add_body_line(' ,user_format_choice VARCHAR2(1)');
796 add_body_line(' ,format_mask VARCHAR2(250)');
797 add_body_line(' ,object_version_number NUMBER(22)');
798 add_body_line(' );');
802 add_body_line(' max_id number;');
799 add_body_line(' TYPE hr_name_format_tab_type IS TABLE OF hr_name_format_rec INDEX BY BINARY_INTEGER;');
800 add_body_line(' hr_name_format_tab hr_name_format_tab_type;');
801 add_body_line(' current_hr_name_format hr_name_format_rec;');
803 add_body_line(' next_id number ;');
804 add_body_line(' ');
805 add_body_line(' CURSOR c_hr_name_format ');
806 add_body_line(' IS');
807 add_body_line(' SELECT format_name');
808 add_body_line(' ,legislation_code');
809 add_body_line(' ,user_format_choice');
810 add_body_line(' ,format_mask');
811 add_body_line(' ,object_version_number');
812 add_body_line(' FROM hr_name_formats;');
813 add_body_line('');
814 add_body_line(' BEGIN');
815 add_body_line(' Open c_hr_name_format;');
816 add_body_line(' Fetch c_hr_name_format bulk collect into hr_name_format_tab;');
817 add_body_line(' close c_hr_name_format;');
818 add_body_line(' ');
819 add_body_line(' for i in 1 .. hr_name_format_tab.count loop');
820 add_body_line(' current_hr_name_format := hr_name_format_tab(i);');
821 add_body_line(' --');
822 add_body_line(' -- This is done because seeded data uses non sequential format_id');
823 add_body_line(' -- And we want smooth insertions without any exceptions due to colliding');
824 add_body_line(' -- sequence numbers.');
825 add_body_line(' --');
826 add_body_line(' select max(name_format_id)');
827 add_body_line(' into max_id');
828 add_body_line(' from hr_name_formats;');
829 add_body_line(' select hr_name_formats_s.nextval into next_id from dual;');
830 add_body_line(' --');
831 add_body_line(' -- This is basically setting the sequence to proper index value');
832 add_body_line(' --');
833 add_body_line(' while(max_id >= next_id) loop');
834 add_body_line(' select hr_name_formats_s.nextval into next_id from dual;');
835 add_body_line(' end loop;');
836 add_body_line(' ');
837 add_body_line('execute immediate ''insert into hr_name_formats (name_format_id');
838 add_body_line(' ,format_name');
839 add_body_line(' ,legislation_code');
840 add_body_line(' ,user_format_choice');
841 add_body_line(' ,format_mask');
842 add_body_line(' ,OBJECT_VERSION_NUMBER');
843 add_body_line(' ,hr_enterprise)');
844 add_body_line(' values(:1');
845 add_body_line(' ,:2');
846 add_body_line(' ,:3');
847 add_body_line(' ,:4');
848 add_body_line(' ,:5');
849 add_body_line(' ,:6');
850 add_body_line(' ,char_to_label(''''HR_ENTERPRISE_POLICY'''',:7))''');
851 add_body_line(' using');
852 add_body_line(' next_id');
853 add_body_line(' ,current_hr_name_format.format_name');
854 add_body_line(' ,current_hr_name_format.legislation_code');
855 add_body_line(' ,current_hr_name_format.user_format_choice');
856 add_body_line(' ,current_hr_name_format.format_mask');
857 add_body_line(' ,current_hr_name_format.OBJECT_VERSION_NUMBER');
858 add_body_line(' ,p_enterprise_code; ');
859 add_body_line(' end loop;');
860 add_body_line(' ');
861 add_body_line(' EXCEPTION');
862 add_body_line(' WHEN OTHERS THEN');
863 add_body_line(' raise;');
864 add_body_line(' --RETURN;');
865 add_body_line(' END; ');
866 add_body_line('');
867 add_body_line(' --');
868 add_body_line(' --------------------------------------------------------------------');
869 add_body_line(' --< process_enterprise >----------------------------------------------');
870 add_body_line(' --------------------------------------------------------------------');
871 add_body_line(' --');
872 add_body_line(' -- Description:');
873 add_body_line(' -- This procedure called from PerEnterprisesTlEOImpl post insert. ');
874 add_body_line(' -- OLS group, label are created and post processing is done for ');
875 add_body_line(' -- the created enterprise');
876 add_body_line(' --');
877 add_body_line(' PROCEDURE process_enterprise (p_enterprise_code IN varchar2 default null, p_enterprise_id number) AS');
878 add_body_line(' l_enterprise_label varchar2(40);');
879 add_body_line(' l_label1 varchar2(40);');
880 add_body_line(' l_group_number number; ');
881 add_body_line(' l_group_exists varchar2(1) := ''N'';');
882 add_body_line(' l_user_exists varchar2(1) := ''N'';');
883 add_body_line(' l_lbl_value number;');
884 add_body_line(' l_proc varchar2(30) := ''process_enterprise'';');
885 add_body_line(' BEGIN');
886 add_body_line(' hr_utility.set_location(''Entering process_enterprise...'' || p_enterprise_code, 10);');
887 add_body_line(' if p_enterprise_code is not null then ');
888 add_body_line('');
889 add_body_line(' begin');
890 add_body_line(' select ''Y''');
891 add_body_line(' into l_group_exists ');
892 add_body_line(' from all_sa_groups');
893 add_body_line(' where policy_name = ''HR_ENTERPRISE_POLICY''');
894 add_body_line(' and short_name = p_enterprise_code;');
895 add_body_line(' exception');
896 add_body_line(' when no_data_found then');
897 add_body_line(' l_group_exists := ''N'';');
898 add_body_line(' end;');
899 add_body_line(' hr_utility.set_location(l_proc, 15);');
900 add_body_line('');
901 add_body_line(' if l_group_exists <> ''Y'' then');
905 add_body_line(' from all_sa_groups');
902 add_body_line(' hr_utility.set_location(l_proc, 20);');
903 add_body_line(' select max(group_num) + 1');
904 add_body_line(' into l_group_number');
906 add_body_line(' where policy_name = ''HR_ENTERPRISE_POLICY'';');
907 add_body_line('');
908 add_body_line(' sa_components.create_group');
909 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
910 add_body_line(' ,group_num => l_group_number');
911 add_body_line(' ,short_name => p_enterprise_code');
912 add_body_line(' ,long_name => p_enterprise_code');
913 add_body_line(' ,parent_name => ''ENT''');
914 add_body_line(' );');
915 add_body_line(' hr_utility.set_location(l_proc, 25);');
916 add_body_line(' end if;');
917 add_body_line(' l_enterprise_label := ''C::'' || p_enterprise_code;');
918 add_body_line('');
919 add_body_line(' select to_data_label(''HR_ENTERPRISE_POLICY'', l_enterprise_label)');
920 add_body_line(' into l_lbl_value');
921 add_body_line(' from dual;');
922 add_body_line(' hr_utility.set_location(l_proc, 30);');
923 add_body_line('');
924 add_body_line(' begin');
925 add_body_line(' select ''Y'' ');
926 add_body_line(' into l_user_exists');
927 add_body_line(' from all_sa_users');
928 add_body_line(' where policy_name = ''HR_ENTERPRISE_POLICY''');
929 add_body_line(' and user_name = l_enterprise_label;');
930 add_body_line(' exception');
931 add_body_line(' when no_data_found then');
932 add_body_line(' l_user_exists := ''N'';');
933 add_body_line(' end;');
934 add_body_line(' hr_utility.set_location(l_proc, 35);');
935 add_body_line(' if l_user_exists <> ''Y'' then');
936 add_body_line(' sa_user_admin.set_user_labels');
937 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
938 add_body_line(' ,user_name => l_enterprise_label');
939 add_body_line(' ,max_read_label => l_enterprise_label || '',GLOBAL''');
940 add_body_line(' ,max_write_label => l_enterprise_label');
941 add_body_line(' ,row_label => l_enterprise_label);');
942 add_body_line(' hr_utility.set_location(l_proc, 40);');
943 add_body_line(' end if;');
944 add_body_line('');
945 add_body_line(' update per_enterprises');
946 add_body_line(' set hr_enterprise = char_to_label(''HR_ENTERPRISE_POLICY'', l_enterprise_label)');
947 add_body_line(' where enterprise_id = p_enterprise_id;');
948 add_body_line(' hr_utility.set_location(l_proc, 43);');
949 add_body_line('');
950 add_body_line(' if hr_multi_tenancy_pkg.get_system_model = ''B'' then ');
951 add_body_line(' insert_hr_name_formats(l_enterprise_label);');
952 add_body_line(' hr_utility.set_location(l_proc, 50);');
953 add_body_line(' end if;');
954 add_body_line('');
955 add_body_line(' hr_multi_tenancy_pkg.set_context(p_enterprise_code);');
956 add_body_line(' hr_utility.set_location(l_proc, 55);');
957 add_body_line('');
958 add_body_line(' else');
959 add_body_line('');
960 add_body_line(' select label_to_char(HR_ENTERPRISE) into l_enterprise_label ');
961 add_body_line(' from per_enterprises where enterprise_id = p_enterprise_id; ');
962 add_body_line(' hr_utility.set_location(l_proc, 56);');
963 add_body_line('');
964 add_body_line(' select enterprise_label into l_label1 ');
965 add_body_line(' from per_enterprises where enterprise_id = p_enterprise_id; ');
966 add_body_line(' hr_utility.set_location(l_proc, 57);');
967 add_body_line('');
968 add_body_line(' update per_enterprises_tl');
969 add_body_line(' set hr_enterprise = char_to_label(''HR_ENTERPRISE_POLICY'', l_enterprise_label)');
970 add_body_line(' where enterprise_id = p_enterprise_id;');
971 add_body_line(' hr_utility.set_location(l_proc, 58);');
972 add_body_line('');
973 add_body_line(' hr_multi_tenancy_pkg.set_context(l_label1);');
974 add_body_line(' hr_utility.set_location(l_proc, 59);');
975 add_body_line('');
976 add_body_line(' end if; ');
977 add_body_line('');
978 add_body_line(' hr_multi_tenancy_pkg.set_context(''ENT'');');
979 add_body_line(' hr_utility.set_location(''Leaving process_enterprise'', 60);');
980 add_body_line(' END process_enterprise;');
981 add_body_line('');
982 add_body_line(' function get_enterprise_for_bg(p_bg_id number) return varchar2 is');
983 add_body_line(' l_ent_short_code varchar2(30);');
984 add_body_line(' begin');
985 add_body_line(' select ent.enterprise_short_code into l_ent_short_code');
986 add_body_line(' from per_enterprises ent,');
987 add_body_line(' per_ent_security_groups pesg');
988 add_body_line(' where ent.enterprise_id = pesg.enterprise_id');
989 add_body_line(' and pesg.organization_id = p_bg_id;');
990 add_body_line(' return l_ent_short_code;');
991 add_body_line(' exception when no_data_found then');
992 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
993 add_body_line(' fnd_message.set_token (''ERRMSG'' , ''Business group is not associated with any enterprise'');');
994 add_body_line(' fnd_message.raise_error;');
995 add_body_line(' when others then');
996 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
997 add_body_line(' fnd_message.set_token (''ERRMSG'' , ''Invalid Business group'');');
998 add_body_line(' fnd_message.raise_error;');
999 add_body_line(' end get_enterprise_for_bg;');
1000 add_body_line('');
1004 add_body_line(' begin');
1001 add_body_line(' function get_enterprise_for_person(p_person_id number) return varchar2 is');
1002 add_body_line(' l_ent_short_code varchar2(30);');
1003 add_body_line(' l_bg_id number;');
1005 add_body_line(' begin');
1006 add_body_line(' select business_group_id into l_bg_id');
1007 add_body_line(' from per_all_people_f');
1008 add_body_line(' where person_id = p_person_id');
1009 add_body_line(' and rownum < 2;');
1010 add_body_line(' exception when others then');
1011 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
1012 add_body_line(' fnd_message.set_token (''ERRMSG'' , ''Invalid Person Id'');');
1013 add_body_line(' fnd_message.raise_error;');
1014 add_body_line(' end;');
1015 add_body_line(' l_ent_short_code := get_enterprise_for_bg(l_bg_id);');
1016 add_body_line(' return l_ent_short_code; ');
1017 add_body_line(' end get_enterprise_for_person;');
1018 add_body_line('');
1019 add_body_line('--');
1020 add_body_line('--------------------------------------------------------------------');
1021 add_body_line('--< set_context >---------------------------------------------------');
1022 add_body_line('--------------------------------------------------------------------');
1023 add_body_line('--');
1024 add_body_line('-- Description:');
1025 add_body_line('-- This is a public procedure which is called by HR_SIGNON to set ');
1026 add_body_line('-- the appropriate Context value. ');
1027 add_body_line('--');
1028 add_body_line('');
1029 add_body_line('PROCEDURE set_context (p_context_value IN VARCHAR2) AS');
1030 add_body_line('sec_flag varchar2(1):= ''N'';');
1031 add_body_line('user_name varchar2(200);');
1032 add_body_line('BEGIN');
1033 add_body_line('select sa_session.sa_user_name(''HR_ENTERPRISE_POLICY'') into user_name from dual;');
1034 add_body_line('init_context(p_context => ''ENT'');');
1035 add_body_line('IF get_system_model=''B'' THEN ');
1036 add_body_line(' BEGIN');
1037 add_body_line(' SELECT ''Y'' ');
1038 add_body_line(' INTO sec_flag');
1039 add_body_line(' FROM per_ent_security_groups');
1040 add_body_line(' WHERE security_group_id = fnd_global.security_group_id;');
1041 add_body_line(' EXCEPTION ');
1042 add_body_line(' WHEN NO_DATA_FOUND THEN');
1043 add_body_line(' sec_flag := ''N'';');
1044 add_body_line(' END;');
1045 add_body_line(' IF fnd_global.security_group_id = 0 THEN');
1046 add_body_line(' sec_flag := ''Y'';');
1047 add_body_line(' END IF;');
1048 add_body_line(' IF sec_flag = ''N'' THEN');
1049 add_body_line(' sa_session.set_access_profile(''HR_ENTERPRISE_POLICY'', user_name);');
1050 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
1051 add_body_line(' fnd_message.set_token (''ERRMSG'' , ''Cannot determine a unique security group within the enterprise.'');');
1052 add_body_line(' fnd_message.raise_error;');
1053 add_body_line(' END IF;');
1054 add_body_line('END IF;');
1055 add_body_line('--');
1056 add_body_line('-- If null is passed as parameter, set context from apps context.');
1057 add_body_line('--');
1058 add_body_line(' IF p_context_value IS NULL THEN');
1059 add_body_line(' init_context_from_apps_context;');
1060 add_body_line(' ELSE');
1061 add_body_line('--');
1062 add_body_line('-- This code is used to reset context to ENT');
1063 add_body_line('--');
1064 add_body_line(' IF(p_context_value <> ''ENT'') THEN');
1065 add_body_line(' init_context (p_context => ''ENT'');');
1066 add_body_line(' END IF;');
1067 add_body_line('--');
1068 add_body_line('-- This code is used to set the appropriate context');
1069 add_body_line('--');
1070 add_body_line(' init_context (p_context => p_context_value);');
1071 add_body_line(' END IF;');
1072 add_body_line('END set_context;');
1073 add_body_line('');
1074 add_body_line(' --');
1075 add_body_line(' --------------------------------------------------------------------');
1076 add_body_line(' --< set_context_for_person >----------------------------------------');
1077 add_body_line(' --------------------------------------------------------------------');
1078 add_body_line(' --');
1079 add_body_line(' -- Description:');
1080 add_body_line(' -- This is a public procedure to set the appropriate Context value');
1081 add_body_line(' -- for a person');
1082 add_body_line(' --');
1083 add_body_line(' PROCEDURE set_context_for_person (p_person_id IN NUMBER) AS');
1084 add_body_line(' label_query VARCHAR2(1000) :=');
1085 add_body_line(' ''SELECT LABEL_TO_CHAR(HR_ENTERPRISE) ENT_LABEL, BUSINESS_GROUP_ID '' ||');
1086 add_body_line(' '' FROM PER_ALL_PEOPLE_F '' ||');
1087 add_body_line(' '' WHERE PERSON_ID = :1 '' ||');
1088 add_body_line(' '' AND ROWNUM < 2'';');
1089 add_body_line(' l_security_label VARCHAR2(4000);');
1090 add_body_line(' l_business_group_id NUMBER;');
1091 add_body_line(' l_enabled_flag VARCHAR2(1) := ''N'';');
1092 add_body_line(' BEGIN');
1093 add_body_line(' INIT_CONTEXT(p_context => ''ENT'');');
1094 add_body_line(' BEGIN');
1095 add_body_line(' EXECUTE IMMEDIATE label_query');
1096 add_body_line(' INTO l_security_label, l_business_group_id');
1097 add_body_line(' USING IN p_person_id;');
1098 add_body_line(' --If security label for this person is null, set context to ENT');
1099 add_body_line(' IF (l_security_label IS NOT NULL AND');
1100 add_body_line(' l_security_label = ''C::ENT'' )');
1101 add_body_line(' OR l_security_label IS NULL THEN');
1102 add_body_line(' set_context(''ENT'');');
1103 add_body_line(' ELSE ');
1104 add_body_line(' BEGIN');
1108 add_body_line(' WHERE business_group_id = l_business_group_id ');
1105 add_body_line(' SELECT enabled_flag');
1106 add_body_line(' INTO l_enabled_flag');
1107 add_body_line(' FROM per_ent_security_groups');
1109 add_body_line(' AND enterprise_label = substrb(l_security_label,4);');
1110 add_body_line(' EXCEPTION ');
1111 add_body_line(' WHEN NO_DATA_FOUND THEN');
1112 add_body_line(' l_enabled_flag := ''N'';');
1113 add_body_line(' END;');
1114 add_body_line(' --If enterprise is enabled, set the context');
1115 add_body_line(' IF l_enabled_flag = ''Y'' THEN');
1116 add_body_line(' set_context(substrb(l_security_label,4));');
1117 add_body_line(' ELSE');
1118 add_body_line(' --raise error eneterprise is not enabled');
1119 add_body_line(' fnd_message.set_name (''PER'',''PER_ENTERPRISE_NOT_FOUND'');');
1120 add_body_line(' fnd_message.raise_error;');
1121 add_body_line(' END IF;');
1122 add_body_line(' END IF; ');
1123 add_body_line(' EXCEPTION');
1124 add_body_line(' WHEN no_data_found THEN');
1125 add_body_line(' --raise error if person not found');
1126 add_body_line(' fnd_message.set_name (''PER'',''PER_OLS_SETUP_ERROR'');');
1127 add_body_line(' fnd_message.set_token (''ERRMSG'' , ''Invalid Person Id'');');
1128 add_body_line(' fnd_message.raise_error;');
1129 add_body_line(' END;');
1130 add_body_line(' END set_context_for_person;');
1131 add_body_line('');
1132 add_body_line(' --');
1133 add_body_line(' --------------------------------------------------------------------');
1134 add_body_line(' --< set_context_for_enterprise >----------------------------------------');
1135 add_body_line(' --------------------------------------------------------------------');
1136 add_body_line(' --');
1137 add_body_line(' -- Description:');
1138 add_body_line(' -- This is a public procedure to set the appropriate Context value');
1139 add_body_line(' -- for a given enterprise short code');
1140 add_body_line(' --');
1141 add_body_line(' PROCEDURE set_context_for_enterprise (p_enterprise_short_code IN VARCHAR2) AS');
1142 add_body_line(' l_security_label VARCHAR2(4000);');
1143 add_body_line(' l_exists NUMBER := 0;');
1144 add_body_line(' BEGIN');
1145 add_body_line(' INIT_CONTEXT(p_context => ''ENT'');');
1146 add_body_line(' BEGIN');
1147 add_body_line(' SELECT enterprise_label');
1148 add_body_line(' INTO l_security_label');
1149 add_body_line(' FROM per_enterprises');
1150 add_body_line(' WHERE enterprise_short_code = p_enterprise_short_code;');
1151 add_body_line(' EXCEPTION');
1152 add_body_line(' WHEN NO_DATA_FOUND THEN');
1153 add_body_line(' fnd_message.set_name (''PER'',''PER_ENTERPRISE_NOT_FOUND'');');
1154 add_body_line(' fnd_message.raise_error;');
1155 add_body_line(' END;');
1156 add_body_line(' BEGIN');
1157 add_body_line(' SELECT count(*)');
1158 add_body_line(' INTO l_exists');
1159 add_body_line(' FROM per_ent_security_groups');
1160 add_body_line(' WHERE enterprise_label = l_security_label');
1161 add_body_line(' AND enabled_flag = ''Y'';');
1162 add_body_line(' IF l_exists <> 0 THEN');
1163 add_body_line(' set_context(l_security_label);');
1164 add_body_line(' ELSE');
1165 add_body_line(' fnd_message.set_name (''PER'',''PER_ENTERPRISE_NOT_FOUND'');');
1166 add_body_line(' fnd_message.raise_error;');
1167 add_body_line(' END IF;');
1168 add_body_line(' END;');
1169 add_body_line(' END set_context_for_enterprise;');
1170 add_body_line('--');
1171 add_body_line('--------------------------------------------------------------------');
1172 add_body_line('--< is_valid_sec_group >--------------------------------------------');
1173 add_body_line('--------------------------------------------------------------------');
1174 add_body_line('--');
1175 add_body_line('-- Description:');
1176 add_body_line('-- This is a public function which Returns Y/N if ');
1177 add_body_line('-- it is a valid security group');
1178 add_body_line('--');
1179 add_body_line('-- Arguments');
1180 add_body_line('-- p_security_group_id => security group id');
1181 add_body_line('-- p_business_group_id => id of the selected business group.');
1182 add_body_line('--');
1183 add_body_line('');
1184 add_body_line('FUNCTION is_valid_sec_group (p_security_group_id IN NUMBER');
1185 add_body_line(' ,p_business_group_id IN NUMBER) RETURN VARCHAR2 AS');
1186 add_body_line(' ');
1187 add_body_line(' l_result VARCHAR2(1);');
1188 add_body_line('BEGIN');
1189 add_body_line(' IF NOT is_multi_tenant_system THEN');
1190 add_body_line(' RETURN ''N'';');
1191 add_body_line(' END IF;');
1192 add_body_line(' ');
1193 add_body_line(' BEGIN');
1194 add_body_line(' SELECT ''Y''');
1195 add_body_line(' INTO l_result');
1196 add_body_line(' FROM per_ent_security_groups');
1197 add_body_line(' WHERE security_group_id = p_security_group_id ');
1198 add_body_line(' AND business_group_id = p_business_group_id ');
1199 add_body_line(' AND enabled_flag = ''Y''; ');
1200 add_body_line(' EXCEPTION');
1201 add_body_line(' WHEN others THEN');
1202 add_body_line(' l_result := ''N'';');
1203 add_body_line(' END;');
1204 add_body_line(' RETURN l_result;');
1205 add_body_line('END is_valid_sec_group;');
1206 add_body_line('');
1207 add_body_line('--');
1208 add_body_line('--------------------------------------------------------------------');
1212 add_body_line('-- Description:');
1209 add_body_line('--< get_org_id_for_person >-----------------------------------------');
1210 add_body_line('--------------------------------------------------------------------');
1211 add_body_line('--');
1213 add_body_line('-- Used IN SSHR to derive the security group from person ');
1214 add_body_line('-- i.e. (Notifications/Workflow)');
1215 add_body_line('--');
1216 add_body_line('-- Arguments');
1217 add_body_line('-- p_person_id => id of the selected person');
1218 add_body_line('--');
1219 add_body_line('');
1220 add_body_line('FUNCTION get_org_id_for_person (p_person_id IN NUMBER) RETURN NUMBER AS');
1221 add_body_line('');
1222 add_body_line(' label_query VARCHAR2(1000) :=');
1223 add_body_line(' ''SELECT LABEL_TO_CHAR(HR_ENTERPRISE) ENT_LABEL, BUSINESS_GROUP_ID '' ||');
1224 add_body_line(' '' FROM PER_ALL_PEOPLE_F '' ||');
1225 add_body_line(' '' WHERE PERSON_ID = :1 '' ||');
1226 add_body_line(' '' AND ROWNUM < 2'';');
1227 add_body_line(' l_security_label VARCHAR2(4000);');
1228 add_body_line(' l_business_group_id NUMBER;');
1229 add_body_line(' l_organization_id NUMBER;');
1230 add_body_line('BEGIN');
1231 add_body_line(' BEGIN');
1232 add_body_line(' EXECUTE IMMEDIATE label_query');
1233 add_body_line(' INTO l_security_label, l_business_group_id');
1234 add_body_line(' USING IN p_person_id;');
1235 add_body_line(' ');
1236 add_body_line(' IF (l_security_label IS NOT NULL AND');
1237 add_body_line(' l_security_label = ''C::ENT'' )');
1238 add_body_line(' OR l_security_label IS NULL THEN');
1239 add_body_line(' ');
1240 add_body_line(' BEGIN');
1241 add_body_line(' SELECT business_group_id ');
1242 add_body_line(' INTO l_organization_id ');
1243 add_body_line(' FROM per_all_people_f');
1244 add_body_line(' WHERE person_id = p_person_id');
1245 add_body_line(' AND rownum < 2;');
1246 add_body_line(' RETURN l_organization_id;');
1247 add_body_line(' EXCEPTION ');
1248 add_body_line(' WHEN NO_DATA_FOUND THEN');
1249 add_body_line(' RETURN -1;');
1250 add_body_line(' END;');
1251 add_body_line(' END IF;');
1252 add_body_line(' SELECT organization_id');
1253 add_body_line(' INTO l_organization_id');
1254 add_body_line(' FROM per_ent_security_groups');
1255 add_body_line(' WHERE business_group_id = l_business_group_id ');
1256 add_body_line(' AND enterprise_label = substrb(l_security_label,4) ');
1257 add_body_line(' AND enabled_flag = ''Y'';');
1258 add_body_line(' ');
1259 add_body_line(' RETURN l_organization_id;');
1260 add_body_line(' EXCEPTION');
1261 add_body_line(' WHEN no_data_found THEN');
1262 add_body_line(' RETURN -1;');
1263 add_body_line(' END;');
1264 add_body_line('END get_org_id_for_person;');
1265 add_body_line('');
1266 add_body_line(' --');
1267 add_body_line(' --------------------------------------------------------------------');
1268 add_body_line(' --< get_org_id_for_person >-----------------------------------------');
1269 add_body_line(' --------------------------------------------------------------------');
1270 add_body_line(' --');
1271 add_body_line(' -- Description:');
1272 add_body_line(' -- Used IN SSHR to derive the security group from person ');
1273 add_body_line(' -- i.e. (Notifications/Workflow)');
1274 add_body_line(' -- Get OrganizationId FROM LoginPersonId AND SelectedBusinessGroupId');
1275 add_body_line(' --');
1276 add_body_line(' -- Arguments');
1277 add_body_line(' -- p_person_id => id of the selected person');
1278 add_body_line(' -- p_business_group_id => business group id');
1279 add_body_line('');
1280 add_body_line('FUNCTION get_org_id_for_person (p_person_id IN NUMBER');
1281 add_body_line(' ,p_business_group_id IN NUMBER) RETURN NUMBER AS');
1282 add_body_line(' ');
1283 add_body_line(' label_query VARCHAR2(1000) :=');
1284 add_body_line(' ''SELECT LABEL_TO_CHAR(HR_ENTERPRISE) ENT_LABEL '' ||');
1285 add_body_line(' '' FROM PER_ALL_PEOPLE_F '' ||');
1286 add_body_line(' '' WHERE PERSON_ID = :1 '' ||');
1287 add_body_line(' '' AND ROWNUM < 2'';');
1288 add_body_line(' l_security_label VARCHAR2(4000);');
1289 add_body_line(' l_organization_id NUMBER;');
1290 add_body_line(' l_count NUMBER := 0;');
1291 add_body_line(' BEGIN');
1292 add_body_line(' BEGIN');
1293 add_body_line(' EXECUTE IMMEDIATE label_query');
1294 add_body_line(' INTO l_security_label');
1295 add_body_line(' USING IN p_person_id;');
1296 add_body_line(' EXCEPTION');
1297 add_body_line(' WHEN no_data_found THEN');
1298 add_body_line(' RETURN -1;');
1299 add_body_line(' END;');
1300 add_body_line(' IF l_security_label = ''C::ENT'' THEN');
1301 add_body_line(' RETURN p_business_group_id;');
1302 add_body_line(' ELSE ');
1303 add_body_line(' BEGIN');
1304 add_body_line(' SELECT organization_id');
1305 add_body_line(' INTO l_organization_id');
1306 add_body_line(' FROM per_ent_security_groups');
1307 add_body_line(' WHERE business_group_id = p_business_group_id ');
1308 add_body_line(' AND enterprise_label = substrb(l_security_label,4)');
1309 add_body_line(' AND enabled_flag = ''Y'';');
1310 add_body_line(' RETURN l_organization_id;');
1311 add_body_line(' EXCEPTION');
1312 add_body_line(' WHEN no_data_found THEN');
1313 add_body_line(' RETURN -1;');
1314 add_body_line(' END;');
1318 add_body_line('--');
1315 add_body_line(' END IF;');
1316 add_body_line('END get_org_id_for_person;');
1317 add_body_line('');
1319 add_body_line('--------------------------------------------------------------------');
1320 add_body_line('--< get_org_id_from_bg_and_sl >-------------------------------------');
1321 add_body_line('--------------------------------------------------------------------');
1322 add_body_line('--');
1323 add_body_line('-- Description:');
1324 add_body_line('-- Called From PerAppModuleHelper');
1325 add_body_line('-- to get orgid from BG and security label');
1326 add_body_line('--');
1327 add_body_line('-- Arguments');
1328 add_body_line('-- p_business_group_id => id of the selected business group.');
1329 add_body_line('-- p_security_label => security label.');
1330 add_body_line('--');
1331 add_body_line('');
1332 add_body_line('FUNCTION get_org_id_from_bg_and_sl (p_business_group_id IN NUMBER');
1333 add_body_line(' ,p_security_label IN VARCHAR2) RETURN NUMBER AS');
1334 add_body_line(' l_organization_id NUMBER;');
1335 add_body_line(' BEGIN');
1336 add_body_line(' SELECT organization_id');
1337 add_body_line(' INTO l_organization_id');
1338 add_body_line(' FROM per_ent_security_groups');
1339 add_body_line(' WHERE business_group_id = p_business_group_id');
1340 add_body_line(' AND enterprise_label = p_security_label');
1341 add_body_line(' AND enabled_flag = ''Y'';');
1342 add_body_line(' RETURN l_organization_id;');
1343 add_body_line(' EXCEPTION ');
1344 add_body_line(' WHEN no_data_found THEN');
1345 add_body_line(' RETURN p_business_group_id;');
1346 add_body_line(' END get_org_id_from_bg_and_sl;');
1347 add_body_line('');
1348 add_body_line(' --');
1349 add_body_line(' --------------------------------------------------------------------');
1350 add_body_line(' --< get_corporate_branding >-------------------------------------');
1351 add_body_line(' --------------------------------------------------------------------');
1352 add_body_line(' --');
1353 add_body_line(' -- Description:');
1354 add_body_line(' -- Gets the corporate branding for the passed/current enterprise.');
1355 add_body_line(' -- ');
1356 add_body_line(' -- Called From');
1357 add_body_line(' -- 1. HRApplicationModuleImpl.java without argument');
1358 add_body_line(' -- 2. MTHomeAMImpl.java with argument.');
1359 add_body_line(' -- Arguments');
1360 add_body_line(' -- p_organization_id => organization id of the current enterprise');
1361 add_body_line(' --');
1362 add_body_line('');
1363 add_body_line('FUNCTION get_corporate_branding (p_organization_id VARCHAR2 default NULL) RETURN VARCHAR2 AS');
1364 add_body_line(' l_security_group_id NUMBER; ');
1365 add_body_line(' l_profile_value VARCHAR2(240);');
1366 add_body_line('');
1367 add_body_line('--');
1368 add_body_line('-- The lookup code for corporate branding is 1');
1369 add_body_line('--');
1370 add_body_line(' CURSOR csr_profile_value (p_security_group_id NUMBER) IS');
1371 add_body_line(' SELECT description ');
1372 add_body_line(' FROM fnd_lookup_values');
1373 add_body_line(' WHERE security_group_id = p_security_group_id');
1374 add_body_line(' AND lookup_type = ''PER_MT_VALUES''');
1375 add_body_line(' AND meaning = ''FND_CORPORATE_BRANDING_IMAGE''');
1376 add_body_line(' AND enabled_flag = ''Y''');
1377 add_body_line(' AND language = userenv(''LANG'')');
1378 add_body_line(' AND sysdate between nvl(start_date_active, hr_api.g_sot)');
1379 add_body_line(' AND nvl(end_date_active, hr_api.g_eot);');
1380 add_body_line('BEGIN');
1381 add_body_line(' IF p_organization_id IS NULL THEN');
1382 add_body_line(' l_security_group_id := fnd_global.security_group_id;');
1383 add_body_line(' ELSE');
1384 add_body_line(' SELECT security_group_id ');
1385 add_body_line(' INTO l_security_group_id');
1386 add_body_line(' FROM per_ent_security_groups');
1387 add_body_line(' WHERE to_char(organization_id) = p_organization_id;');
1388 add_body_line(' END IF;');
1389 add_body_line(' OPEN csr_profile_value(l_security_group_id);');
1390 add_body_line(' FETCH csr_profile_value INTO l_profile_value;');
1391 add_body_line(' IF csr_profile_value%notfound THEN');
1392 add_body_line(' CLOSE csr_profile_value;');
1393 add_body_line(' OPEN csr_profile_value(0);');
1394 add_body_line(' FETCH csr_profile_value INTO l_profile_value;');
1395 add_body_line(' CLOSE csr_profile_value;');
1396 add_body_line(' END IF; ');
1397 add_body_line('');
1398 add_body_line(' RETURN l_profile_value;');
1399 add_body_line('END get_corporate_branding;');
1400 add_body_line('');
1401 add_body_line('--');
1402 add_body_line('--------------------------------------------------------------------');
1403 add_body_line('--< get_bus_grp_from_sec_grp >-------------------------------------');
1404 add_body_line('--------------------------------------------------------------------');
1405 add_body_line('--');
1406 add_body_line('-- Description:');
1407 add_body_line('-- Called from HR_SIGNON to RETURN the business group corresponding');
1408 add_body_line('-- to the enterprise security group for buisness group initialization. ');
1409 add_body_line('--');
1410 add_body_line('-- Called From');
1411 add_body_line('-- 1. HR_SIGNON ');
1412 add_body_line('-- Arguments');
1413 add_body_line('-- p_security_group_id => security group id of the current enterprise');
1414 add_body_line('--');
1415 add_body_line('FUNCTION get_bus_grp_from_sec_grp (p_security_group_id IN NUMBER) RETURN NUMBER AS');
1416 add_body_line(' l_business_group_id NUMBER;');
1417 add_body_line('BEGIN');
1418 add_body_line(' SELECT business_group_id');
1422 add_body_line(' AND enabled_flag = ''Y'';');
1419 add_body_line(' INTO l_business_group_id');
1420 add_body_line(' FROM per_ent_security_groups');
1421 add_body_line(' WHERE security_group_id = p_security_group_id');
1423 add_body_line('');
1424 add_body_line(' RETURN l_business_group_id;');
1425 add_body_line('EXCEPTION');
1426 add_body_line(' WHEN no_data_found THEN');
1427 add_body_line(' RETURN NULL; ');
1428 add_body_line('END get_bus_grp_from_sec_grp;');
1429 add_body_line('');
1430 add_body_line('--');
1431 add_body_line('--------------------------------------------------------------------');
1432 add_body_line('--< set_security_group_id >-------------------------------------');
1433 add_body_line('--------------------------------------------------------------------');
1434 add_body_line('--');
1435 add_body_line('-- Description:');
1436 add_body_line('-- Called from HR_API to set proper security group. ');
1437 add_body_line('--');
1438 add_body_line('-- Called From');
1439 add_body_line('-- 1. HR_API ');
1440 add_body_line('-- Arguments');
1441 add_body_line('-- p_security_group_id => security group id of the current enterprise');
1442 add_body_line('--');
1443 add_body_line('PROCEDURE set_security_group_id (p_security_group_id IN NUMBER) AS ');
1444 add_body_line(' l_business_group_id NUMBER;');
1445 add_body_line(' l_security_group_id NUMBER;');
1446 add_body_line(' l_security_group_id1 NUMBER;');
1447 add_body_line(' CURSOR csr_sec_grp_for_bus_grp (p_business_group_id NUMBER) IS');
1448 add_body_line(' SELECT security_group_id');
1449 add_body_line(' FROM per_ent_security_groups');
1450 add_body_line(' WHERE business_group_id = p_business_group_id');
1451 add_body_line(' AND enabled_flag = ''Y'';');
1452 add_body_line('BEGIN');
1453 add_body_line(' IF p_security_group_id IS NULL THEN ');
1454 add_body_line(' l_security_group_id1 := fnd_global.security_group_id;');
1455 add_body_line(' ELSE');
1456 add_body_line(' l_security_group_id1 := p_security_group_id;');
1457 add_body_line(' END IF;');
1458 add_body_line('');
1459 add_body_line(' BEGIN ');
1460 add_body_line(' SELECT business_group_id');
1461 add_body_line(' INTO l_business_group_id');
1462 add_body_line(' FROM per_business_groups');
1463 add_body_line(' WHERE security_group_id = to_char(l_security_group_id1);');
1464 add_body_line(' EXCEPTION');
1465 add_body_line(' WHEN NO_DATA_FOUND THEN');
1466 add_body_line(' RETURN;');
1467 add_body_line(' END;');
1468 add_body_line('');
1469 add_body_line(' OPEN csr_sec_grp_for_bus_grp');
1470 add_body_line(' (p_business_group_id => l_business_group_id);');
1471 add_body_line('');
1472 add_body_line(' LOOP');
1473 add_body_line(' FETCH csr_sec_grp_for_bus_grp INTO l_security_group_id;');
1474 add_body_line('');
1475 add_body_line(' IF csr_sec_grp_for_bus_grp%found THEN');
1476 add_body_line(' IF l_security_group_id = fnd_global.security_group_id THEN');
1477 add_body_line(' CLOSE csr_sec_grp_for_bus_grp;');
1478 add_body_line(' RETURN;');
1479 add_body_line(' END IF;');
1480 add_body_line(' ELSE');
1481 add_body_line(' EXIT;');
1482 add_body_line(' END IF;');
1483 add_body_line(' END LOOP;');
1484 add_body_line(' CLOSE csr_sec_grp_for_bus_grp;');
1485 add_body_line('');
1486 add_body_line(' -- This has been modified to be in sync with SSHR fix ');
1487 add_body_line(' -- in hr_api.set_security_group_id. ');
1488 add_body_line(' --fnd_global.set_security_group_id_context(p_security_group_id); ');
1489 add_body_line(' fnd_client_info.set_security_group_context(to_char(p_security_group_id));');
1490 add_body_line('END set_security_group_id;');
1491 add_body_line('');
1492 add_body_line('--');
1493 add_body_line('--------------------------------------------------------------------');
1494 add_body_line('--< add_language >--------------------------------------------------');
1495 add_body_line('--------------------------------------------------------------------');
1496 add_body_line('--');
1497 add_body_line('-- Description:');
1498 add_body_line('-- Updates TL table');
1499 add_body_line('--');
1500 add_body_line('-- Arguments');
1501 add_body_line('-- None');
1502 add_body_line('--');
1503 add_body_line('PROCEDURE add_language IS');
1504 add_body_line(' CURSOR csr_ent_data_groups IS');
1505 add_body_line(' SELECT pet.enterprise_id');
1506 add_body_line(' ,pet.enterprise_name');
1507 add_body_line(' ,pet.description');
1508 add_body_line(' ,pet.source_lang');
1509 add_body_line(' ,pet.created_by');
1510 add_body_line(' ,pet.creation_date');
1511 add_body_line(' FROM per_enterprises_tl pet');
1512 add_body_line(' WHERE pet.language = userenv(''LANG'');');
1513 add_body_line(' ');
1514 add_body_line(' CURSOR csr_ins_langs (c_enterprise_id NUMBER) IS');
1515 add_body_line(' SELECT l.language_code');
1516 add_body_line(' FROM fnd_languages l');
1517 add_body_line(' WHERE l.installed_flag IN (''I'',''B'')');
1518 add_body_line(' AND NOT EXISTS (SELECT NULL');
1519 add_body_line(' FROM per_enterprises_tl pet');
1520 add_body_line(' WHERE pet.enterprise_id = c_enterprise_id');
1521 add_body_line(' AND pet.language = l.language_code);');
1522 add_body_line(' --');
1523 add_body_line(' BEGIN');
1524 add_body_line(' --');
1525 add_body_line(' DELETE FROM per_enterprises_tl t');
1526 add_body_line(' WHERE NOT EXISTS');
1527 add_body_line(' ( SELECT NULL');
1528 add_body_line(' FROM per_enterprises b');
1532 add_body_line(' UPDATE per_enterprises_tl t');
1529 add_body_line(' WHERE b.enterprise_id = t.enterprise_id');
1530 add_body_line(' );');
1531 add_body_line('');
1533 add_body_line(' SET ( enterprise_name,');
1534 add_body_line(' description ) =');
1535 add_body_line(' ( SELECT b.enterprise_name,');
1536 add_body_line(' b.description');
1537 add_body_line(' FROM per_enterprises_tl b');
1538 add_body_line(' WHERE b.enterprise_id = t.enterprise_id');
1539 add_body_line(' AND b.language = t.source_lang )');
1540 add_body_line(' WHERE ( t.enterprise_id,');
1541 add_body_line(' t.language');
1542 add_body_line(' ) IN');
1543 add_body_line(' ( SELECT subt.enterprise_id,');
1544 add_body_line(' subt.language');
1545 add_body_line(' FROM per_enterprises_tl subb, per_enterprises_tl subt');
1546 add_body_line(' WHERE subb.enterprise_id = subt.enterprise_id');
1547 add_body_line(' AND subb.language = subt.source_lang');
1548 add_body_line(' AND ( subb.enterprise_name <> subt.enterprise_name');
1549 add_body_line(' OR subb.description <> subt.description');
1550 add_body_line(' OR (subb.description IS NULL AND subt.description IS NOT NULL)');
1551 add_body_line(' OR (subb.description IS NOT NULL AND subt.description IS NULL)');
1552 add_body_line(' )');
1553 add_body_line(' );');
1554 add_body_line(' --');
1555 add_body_line(' --');
1556 add_body_line(' FOR l_ent_data_group IN csr_ent_data_groups LOOP');
1557 add_body_line(' FOR l_lang IN csr_ins_langs(l_ent_data_group.enterprise_id) LOOP');
1558 add_body_line(' INSERT INTO per_enterprises_tl');
1559 add_body_line(' (source_lang');
1560 add_body_line(' ,enterprise_id');
1561 add_body_line(' ,enterprise_name');
1562 add_body_line(' ,description');
1563 add_body_line(' ,language');
1564 add_body_line(' ,created_by');
1565 add_body_line(' ,creation_date');
1566 add_body_line(' ,last_updated_by');
1567 add_body_line(' ,last_update_date');
1568 add_body_line(' ) VALUES');
1569 add_body_line(' (l_ent_data_group.source_lang');
1570 add_body_line(' ,l_ent_data_group.enterprise_id');
1571 add_body_line(' ,l_ent_data_group.enterprise_name');
1572 add_body_line(' ,l_ent_data_group.description');
1573 add_body_line(' ,l_lang.language_code');
1574 add_body_line(' ,fnd_global.user_id');
1575 add_body_line(' ,sysdate');
1576 add_body_line(' ,fnd_global.user_id');
1577 add_body_line(' ,sysdate');
1578 add_body_line(' ); ');
1579 add_body_line(' END LOOP;');
1580 add_body_line(' END LOOP;');
1581 add_body_line(' --');
1582 add_body_line(' END add_language;');
1583 add_body_line('');
1584 add_body_line('END '||l_package_name ||';');
1585 add_header_line('END ' ||l_package_name||';');
1586
1587 l_csr_sql := dbms_sql.open_cursor;
1588 dbms_sql.parse( l_csr_sql, l_header,0,l_header_line-1,FALSE, dbms_sql.v7 );
1589 l_rows := dbms_sql.EXECUTE( l_csr_sql );
1590 dbms_sql.close_cursor( l_csr_sql );
1591
1592 l_csr_sql := dbms_sql.open_cursor;
1593 dbms_sql.parse( l_csr_sql, l_body,0,l_body_line-1,FALSE, dbms_sql.v7 );
1594 l_rows := dbms_sql.EXECUTE( l_csr_sql );
1595 dbms_sql.close_cursor( l_csr_sql );
1596
1597 l_header_line :=0;
1598 l_body_line :=0;
1599
1600 --
1601 -- Create or replace the hr_multi_tenant_install package
1602 --
1603
1604 l_package_name := 'hr_multi_tenant_install';
1605 add_header_line('CREATE OR REPLACE PACKAGE '||l_package_name||' AS ');
1606 add_body_line('CREATE OR REPLACE PACKAGE BODY '||l_package_name||' AS ');
1607 add_header_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
1608 add_header_line('/'||'* $Header: pemtstup.pkb 120.0.12010000.37 2008/11/24 10:27:27 bchakrab noship*'||'/');
1609 add_body_line('--Code generated on '||to_char(sysdate,'DD/MM/YYYY HH:MI:SS'));
1610 add_body_line('/'||'* $Header: pemtstup.pkb 120.0.12010000.37*'||'/');
1611
1612
1613 add_header_line('--');
1614 add_header_line('-- Name');
1615 add_header_line('-- initialize_orgs');
1616 add_header_line('--');
1617 add_header_line('-- Purpose');
1618 add_header_line('-- This procedure is called by the concurrent program');
1619 add_header_line('-- Setup Enterprise for Multi Tenancy.This procedure attaches the ');
1620 add_header_line('-- the enterprise label with the organization specified as parameters');
1621 add_header_line('--');
1622 add_header_line('-- Arguments');
1623 add_header_line('-- errbuf and retcode.');
1624 add_header_line('-- p_enterprise_id => This specifies the Id of the defined enterprise');
1625 add_header_line('-- p_organization_id => This specifies the Id of the organization ');
1626 add_header_line('-- that is classified as enterprise ');
1627 add_header_line('--');
1628 add_header_line(' ');
1629 add_header_line('PROCEDURE initialize_orgs (errbuf OUT NOCOPY VARCHAR2');
1630 add_header_line(' ,retcode OUT NOCOPY NUMBER');
1631 add_header_line(' ,p_enterprise_id IN NUMBER');
1632 add_header_line(' ,p_organization_id IN NUMBER);');
1633 add_header_line(' ');
1634 add_header_line('--');
1635 add_header_line('-- Name');
1636 add_header_line('-- master_process');
1637 add_header_line('--');
1638 add_header_line('-- Purpose');
1639 add_header_line('-- This procedure is called by the concurrent program');
1643 add_header_line('-- Arguments');
1640 add_header_line('-- Setup Multi Tenancy for Database Objects.This concurrent program');
1641 add_header_line('-- spawns child processes in batches of size less or equalt to 100. ');
1642 add_header_line('--');
1644 add_header_line('-- errbuf and retcode.');
1645 add_header_line('-- install_mode => The valid values are USER_TABLE and SCHEMA. This');
1646 add_header_line('-- specifies the mode of applying policy i.e whether on ');
1647 add_header_line('-- all tables in the schema or on the tables listed');
1648 add_header_line('-- in the user table.');
1649 add_header_line('-- ');
1650 add_header_line('-- population_size => This specifies the batch size of the spawning child ');
1651 add_header_line('-- processes. The default batch size is 100 ');
1652 add_header_line('--');
1653 add_header_line(' ');
1654 add_header_line('procedure master_process');
1655 add_header_line(' (errbuf out nocopy varchar2');
1656 add_header_line(' ,retcode out nocopy number');
1657 add_header_line(' ,install_mode in varchar2');
1658 add_header_line(' ,population_size in number default 100);');
1659 add_header_line('');
1660 add_header_line('');
1661 add_header_line('--');
1662 add_header_line('-- Name');
1663 add_header_line('-- child_process');
1664 add_header_line('--');
1665 add_header_line('-- Purpose');
1666 add_header_line('-- This procedure is called by the concurrent program');
1667 add_header_line('-- Setup Multi Tenancy for Database Objects as a spawned');
1668 add_header_line('-- child processes in batches of size less or equalt to 100. ');
1669 add_header_line('--');
1670 add_header_line('-- Arguments');
1671 add_header_line('-- errbuf and retcode.');
1675 add_header_line('-- in the user table.');
1672 add_header_line('-- install_mode => The valid values are USER_TABLE and SCHEMA. This');
1673 add_header_line('-- specifies the mode of applying policy i.e whether on ');
1674 add_header_line('-- all tables in the schema or on the tables listed');
1676 add_header_line('-- ');
1677 add_header_line('-- population_size => This specifies the batch size of the spawning child ');
1678 add_header_line('-- processes. ');
1679 add_header_line('-- ');
1680 add_header_line('-- population_start => This is the starting count of the batch');
1681 add_header_line('-- population_end => This is the ending count of the batch');
1682 add_header_line('--');
1683 add_header_line('--');
1684 add_header_line('');
1685 add_header_line(' procedure child_process ');
1686 add_header_line(' (errbuf out nocopy varchar2');
1687 add_header_line(' ,retcode out nocopy number');
1688 add_header_line(' ,install_mode in varchar2');
1689 add_header_line(' ,population_size in number');
1690 add_header_line(' ,population_start in number');
1691 add_header_line(' ,population_end in number); ');
1692
1693 add_body_line('');
1694 add_body_line('--');
1695 add_body_line('-- Variable declarations');
1696 add_body_line('--');
1697 add_body_line(' l_body_line NUMBER:=0;');
1698 add_body_line(' l_body DBMS_SQL.VARCHAR2S;');
1699 add_body_line(' l_csr_sql NUMBER;');
1700 add_body_line(' l_rows NUMBER;');
1701 add_body_line('');
1702 add_body_line('-----------------------------------------------------------------------');
1703 add_body_line('-----------------------------------------------------------------------');
1704 add_body_line('-- PRIVATE FUNCTIONS');
1705 add_body_line('-----------------------------------------------------------------------');
1706 add_body_line('-----------------------------------------------------------------------');
1707 add_body_line('');
1708 add_body_line('--');
1709 add_body_line('--------------------------------------------------------------------');
1710 add_body_line('--< CREATE_SEC_GROUP_FOR_ENT >--------------------------------------');
1711 add_body_line('--------------------------------------------------------------------');
1712 add_body_line('--');
1713 add_body_line('-- Description:');
1714 add_body_line('-- This procedure accepts the organization id,');
1715 add_body_line('-- business group id, security label and enterprise id');
1716 add_body_line('-- and creates the necessary back end housekeeping for the organization');
1717 add_body_line('-- to be defined as enterprise.');
1718 add_body_line('--');
1719 add_body_line('-- Arguments:');
1720 add_body_line('-- p_organization_id => Id of the organization to be defined as enterprise');
1721 add_body_line('-- p_business_group_id => Business Group Id of the organization');
1725 add_body_line('');
1722 add_body_line('-- p_security_label => label of the enterprise');
1723 add_body_line('-- p_enterprise_id => id of the enterprise');
1724 add_body_line('--');
1726 add_body_line('PROCEDURE create_sec_group_for_ent (p_organization_id IN NUMBER');
1727 add_body_line(' ,p_business_group_id IN NUMBER');
1728 add_body_line(' ,p_security_label IN VARCHAR2');
1729 add_body_line(' ,p_enterprise_id IN NUMBER ) IS');
1730 add_body_line('');
1731 add_body_line(' CURSOR c_sg_enabled IS');
1732 add_body_line(' SELECT ''Y''');
1733 add_body_line(' FROM fnd_profile_options fpo');
1734 add_body_line(' ,fnd_profile_option_values pov');
1735 add_body_line(' WHERE fpo.profile_option_name = ''ENABLE_SECURITY_GROUPS'' AND');
1736 add_body_line(' fpo.profile_option_id = pov.profile_option_id AND');
1737 add_body_line(' fpo.application_id = pov.application_id AND');
1738 add_body_line(' pov.level_id = 10002 AND');
1739 add_body_line(' pov.profile_option_value = ''Y'' AND');
1740 add_body_line(' to_number(pov.level_value) BETWEEN 800 AND 900;');
1741 add_body_line('');
1742 add_body_line(' CURSOR c_sec_grp_name_curs(p_sec_name VARCHAR2) IS');
1743 add_body_line(' SELECT substrb(security_group_name, 1, 80), security_group_id');
1744 add_body_line(' FROM fnd_security_groups_vl');
1745 add_body_line(' WHERE substrb(security_group_name, 1, 80)= p_sec_name;');
1746 add_body_line('');
1747 add_body_line(' l_security_group_name hr_all_organization_units.name%type;');
1748 add_body_line(' l_exists VARCHAR2(1) DEFAULT NULL;');
1749 add_body_line(' l_sg_enabled BOOLEAN DEFAULT FALSE;');
1750 add_body_line(' l_sec_length NUMBER;');
1751 add_body_line(' l_security_group_id NUMBER;');
1752 add_body_line(' l_ent_name VARCHAR2(80);');
1753 add_body_line(' l_sg_name VARCHAR2(80);');
1754 add_body_line(' l_out_status VARCHAR2(30);');
1755 add_body_line(' l_out_industry VARCHAR2(30);');
1756 add_body_line(' l_owner VARCHAR2(30);');
1757 add_body_line(' l_value BOOLEAN;');
1758 add_body_line(' BEGIN');
1759 add_body_line('--');
1760 add_body_line('-- Check if security groups are enabled in the instance');
1761 add_body_line('--');
1762 add_body_line(' OPEN c_sg_enabled;');
1763 add_body_line(' FETCH c_sg_enabled INTO l_exists;');
1764 add_body_line(' IF c_sg_enabled%FOUND THEN');
1765 add_body_line(' l_sg_enabled := TRUE;');
1766 add_body_line(' ELSE');
1767 add_body_line(' l_sg_enabled := FALSE;');
1768 add_body_line(' END IF;');
1769 add_body_line(' CLOSE c_sg_enabled;');
1770 add_body_line(' IF l_sg_enabled THEN');
1771 add_body_line('--');
1772 add_body_line('-- Fetch the corresponding security group name of the organization.');
1773 add_body_line('--');
1774 add_body_line(' SELECT substrb(hou.name,1,80)');
1775 add_body_line(' INTO l_security_group_name');
1776 add_body_line(' FROM hr_all_organization_units hou');
1777 add_body_line(' WHERE hou.organization_id = p_organization_id;');
1778 add_body_line('');
1779 add_body_line('--');
1780 add_body_line('-- Fetch the security_group_id');
1781 add_body_line('--');
1782 add_body_line(' OPEN c_sec_grp_name_curs(l_security_group_name);');
1783 add_body_line(' FETCH c_sec_grp_name_curs INTO l_sg_name, l_security_group_id;');
1784 add_body_line(' IF c_sec_grp_name_curs%found THEN');
1788 add_body_line('--');
1785 add_body_line(' CLOSE c_sec_grp_name_curs;');
1786 add_body_line('--');
1787 add_body_line('-- This is an error condition in Model 2(PEO).');
1789 add_body_line(' IF hr_multi_tenancy_pkg.get_system_model = ''P'' THEN');
1790 add_body_line(' hr_utility.set_message(800, ''PER_289704_80CHAR_MATCH_SG'');');
1791 add_body_line(' hr_utility.raise_error;');
1792 add_body_line(' END IF; ');
1793 add_body_line(' ELSE');
1794 add_body_line(' CLOSE c_sec_grp_name_curs;');
1795 add_body_line('--');
1796 add_body_line('-- This is an error condition in Model 1 (BPO)');
1797 add_body_line('--');
1798 add_body_line(' IF hr_multi_tenancy_pkg.get_system_model = ''B'' THEN');
1799 add_body_line(' hr_utility.set_message(800, ''PER_OLS_SETUP_ERROR'');');
1800 add_body_line(' hr_utility.raise_error;');
1801 add_body_line(' END IF;');
1802 add_body_line('--');
1803 add_body_line('-- Create a new security group for this organization');
1804 add_body_line('--');
1808 add_body_line(' END IF;');
1805 add_body_line(' l_security_group_id := fnd_security_groups_api.create_group (security_group_key => to_char(p_organization_id)');
1806 add_body_line(' ,security_group_name => l_security_group_name');
1807 add_body_line(' ,description => '' '');');
1809 add_body_line('--');
1810 add_body_line('-- Make an entry in the table for this organization');
1811 add_body_line('--');
1812 add_body_line('BEGIN');
1813 add_body_line(' EXECUTE IMMEDIATE ''INSERT INTO per_ent_security_groups ');
1814 add_body_line(' (enterprise_id');
1815 add_body_line(' ,organization_id');
1816 add_body_line(' ,enterprise_label');
1817 add_body_line(' ,business_group_id');
1818 add_body_line(' ,security_group_id');
1819 add_body_line(' ,enabled_flag');
1820 add_body_line(' ,hr_enterprise)');
1821 add_body_line(' VALUES (:1, :2, :3, :4, :5, ''''Y'''', char_to_label(''''HR_ENTERPRISE_POLICY'''', :6))'' ');
1822 add_body_line(' USING p_enterprise_id');
1823 add_body_line(' ,p_organization_id');
1824 add_body_line(' ,p_security_label');
1825 add_body_line(' ,p_business_group_id');
1826 add_body_line(' ,l_security_group_id');
1827 add_body_line(' ,''C::'' || p_security_label;');
1828 add_body_line('EXCEPTION');
1829 add_body_line(' WHEN OTHERS THEN');
1830 add_body_line(' fnd_file.put_line (fnd_file.log');
1831 add_body_line(' ,''Cannot map Security Group for this enterprise '');');
1832 add_body_line('END;');
1833 add_body_line(' ELSE');
1834 add_body_line(' fnd_file.put_line (fnd_file.log');
1835 add_body_line(' ,''Security Group Not enabled: '');');
1836 add_body_line(' ');
1837 add_body_line(' END IF;');
1838 add_body_line('END create_sec_group_for_ent;');
1839 add_body_line('');
1840 add_body_line('--');
1841 add_body_line('-----------------------------------------------------------------------');
1842 add_body_line('-----------------------------------------------------------------------');
1843 add_body_line('-- PUBLIC FUNCTIONS');
1844 add_body_line('-----------------------------------------------------------------------');
1845 add_body_line('-----------------------------------------------------------------------');
1846 add_body_line('--');
1847 add_body_line('-----------------------------------------------------------------------');
1848 add_body_line('--< initialize_orgs >--------------------------------------------------');
1849 add_body_line('-----------------------------------------------------------------------');
1850 add_body_line('--');
1851 add_body_line('-- Description:');
1852 add_body_line('-- This procedure is called by the concurrent program');
1853 add_body_line('-- Setup Enterprise for Multi Tenancy.This procedure attaches the ');
1854 add_body_line('-- the enterprise label with the organization specified as parameters');
1855 add_body_line('--');
1856 add_body_line('-- Arguments');
1857 add_body_line('-- errbuf and retcode.');
1858 add_body_line('-- p_enterprise_id => This specifies the Id of the defined enterprise');
1859 add_body_line('-- p_organization_id => This specifies the Id of the organization ');
1860 add_body_line('-- that is classified as enterprise ');
1861 add_body_line('--');
1862 add_body_line('');
1863 add_body_line('PROCEDURE initialize_orgs ( errbuf OUT NOCOPY VARCHAR2');
1864 add_body_line(' , retcode OUT NOCOPY NUMBER');
1865 add_body_line(' , p_enterprise_id IN NUMBER');
1866 add_body_line(' , p_organization_id IN NUMBER) AS');
1867 add_body_line(' ');
1868 add_body_line(' TYPE bg_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;');
1869 add_body_line(' TYPE child_table_info IS');
1870 add_body_line(' RECORD (table_name varchar2(30), ');
1871 add_body_line(' child_key_name varchar2(4000), ');
1872 add_body_line(' master_table varchar2(30), ');
1873 add_body_line(' master_key_name varchar2(4000));');
1874 add_body_line(' TYPE child_tables IS TABLE OF child_table_info INDEX BY BINARY_INTEGER;');
1875 add_body_line('');
1876 add_body_line(' init_master_tables bg_table;');
1877 add_body_line(' init_table_name VARCHAR2(256);');
1878 add_body_line(' init_child_tables child_tables;');
1879 add_body_line(' l_org_info VARCHAR2(1000);');
1880 add_body_line(' l_enterprise_name VARCHAR2(30);');
1881 add_body_line(' l_long_name VARCHAR2(80);');
1882 add_body_line(' l_group_number NUMBER;');
1883 add_body_line(' l_is_business_group BOOLEAN DEFAULT FALSE;');
1884 add_body_line(' l_business_group_id NUMBER;');
1885 add_body_line(' l_lbl_value NUMBER;');
1886 add_body_line(' l_company_label VARCHAR2(150);');
1887 add_body_line('');
1888 add_body_line('BEGIN');
1889 add_body_line(' IF p_organization_id IS NOT NULL THEN');
1890 add_body_line('--');
1891 add_body_line('-- Fetch the businessgroup Id of the organization.');
1892 add_body_line('--');
1893 add_body_line(' BEGIN');
1894 add_body_line('--');
1895 add_body_line('-- Check if this is a business group.');
1896 add_body_line('--');
1900 add_body_line(' WHERE organization_id = p_organization_id;');
1897 add_body_line(' SELECT business_group_id');
1898 add_body_line(' INTO l_business_group_id');
1899 add_body_line(' FROM hr_all_organization_units');
1901 add_body_line('');
1902 add_body_line(' BEGIN');
1903 add_body_line(' SELECT 1');
1904 add_body_line(' INTO l_org_info');
1905 add_body_line(' FROM hr_organization_information');
1906 add_body_line(' WHERE org_information_context = ''CLASS''');
1907 add_body_line(' AND organization_id = p_organization_id');
1908 add_body_line(' AND org_information1 IN (''HR_BG'');');
1909 add_body_line('');
1910 add_body_line(' l_is_business_group := TRUE;');
1911 add_body_line('');
1912 add_body_line(' EXCEPTION');
1913 add_body_line(' WHEN no_data_found THEN');
1914 add_body_line(' l_is_business_group := FALSE;');
1915 add_body_line(' END;');
1916 add_body_line('--');
1917 add_body_line('-- Select the long name and the company label for this enterprise id');
1918 add_body_line('--');
1919 add_body_line('');
1920 add_body_line(' SELECT substr(enterprise_short_code, 1, 80)');
1921 add_body_line(' ,enterprise_label');
1922 add_body_line(' INTO l_long_name,l_company_label');
1923 add_body_line(' FROM per_enterprises');
1924 add_body_line(' WHERE enterprise_id = p_enterprise_id;');
1925 add_body_line('');
1926 add_body_line(' l_enterprise_name := substr(l_company_label, 1, 30);');
1927 add_body_line('');
1928 add_body_line(' fnd_file.put_line (fnd_file.log');
1929 add_body_line(' ,''Creating Security Group : '' || l_enterprise_name);');
1930 add_body_line(' fnd_file.put_line (fnd_file.output');
1931 add_body_line(' ,''Creating Security Group : '' || l_enterprise_name);');
1932 add_body_line('');
1933 add_body_line('--');
1934 add_body_line('-- In Model 2 (PEO) create a security group for this enterprise.');
1935 add_body_line('--');
1936 add_body_line(' create_sec_group_for_ent (p_organization_id => p_organization_id');
1937 add_body_line(' ,p_business_group_id => l_business_group_id');
1938 add_body_line(' ,p_security_label => l_enterprise_name');
1939 add_body_line(' ,p_enterprise_id => p_enterprise_id);');
1940 add_body_line('');
1941 add_body_line(' l_enterprise_name := ''C::'' || l_enterprise_name;');
1942 add_body_line('');
1943 add_body_line(' IF NOT l_is_business_group THEN');
1944 add_body_line(' fnd_file.put_line(fnd_file.log');
1945 add_body_line(' ,''Initializing Control :Person Sequence'');');
1946 add_body_line(' ');
1947 add_body_line('--');
1948 add_body_line('-- In model 2 make seperate entries in the PER_NUMBER_GENERATION_CONTROLS');
1949 add_body_line('-- for each enterprise');
1950 add_body_line('--');
1951 add_body_line('EXECUTE IMMEDIATE');
1952 add_body_line(' ''INSERT INTO per_number_generation_controls ');
1953 add_body_line(' (type');
1954 add_body_line(' ,business_group_id');
1955 add_body_line(' ,next_value');
1956 add_body_line(' ,hr_enterprise)');
1957 add_body_line(' VALUES (''''EMP''''');
1958 add_body_line(' ,:1');
1959 add_body_line(' ,1');
1960 add_body_line(' ,char_to_label(''''HR_ENTERPRISE_POLICY'''', :2))''');
1961 add_body_line(' USING l_business_group_id');
1962 add_body_line(' ,l_enterprise_name;');
1963 add_body_line('');
1964 add_body_line('EXECUTE IMMEDIATE');
1965 add_body_line(' ''INSERT INTO per_number_generation_controls ');
1966 add_body_line(' (type');
1967 add_body_line(' ,business_group_id');
1968 add_body_line(' ,next_value');
1969 add_body_line(' ,hr_enterprise)');
1970 add_body_line(' VALUES (''''APL''''');
1971 add_body_line(' ,:1');
1972 add_body_line(' ,1');
1973 add_body_line(' ,char_to_label(''''HR_ENTERPRISE_POLICY''''');
1974 add_body_line(' ,:2))''');
1975 add_body_line(' USING l_business_group_id');
1976 add_body_line(' ,l_enterprise_name;');
1977 add_body_line('');
1978 add_body_line('EXECUTE IMMEDIATE');
1979 add_body_line(' ''INSERT INTO per_number_generation_controls ');
1980 add_body_line(' (type');
1981 add_body_line(' ,business_group_id');
1982 add_body_line(' ,next_value');
1983 add_body_line(' ,hr_enterprise)');
1984 add_body_line(' VALUES (''''CWK''''');
1985 add_body_line(' ,:1');
1986 add_body_line(' ,1');
1987 add_body_line(' ,char_to_label(''''HR_ENTERPRISE_POLICY''''');
1988 add_body_line(' ,:2))''');
1989 add_body_line(' USING l_business_group_id');
1993 add_body_line(' ,''Completed Control: Person Sequence'');');
1990 add_body_line(' ,l_enterprise_name;');
1991 add_body_line('');
1992 add_body_line(' fnd_file.put_line (fnd_file.log');
1994 add_body_line(' ELSE');
1995 add_body_line(' init_master_tables(1) := ''PER_PERSON_TYPES'';');
1996 add_body_line(' init_master_tables(2) := ''PER_NUMBER_GENERATION_CONTROLS'';');
1997 add_body_line(' init_master_tables(3) := ''PER_SECURITY_PROFILES'';');
1998 add_body_line(' init_master_tables(4) := ''PER_JOB_GROUPS'';');
1999 add_body_line(' init_master_tables(5) := ''PAY_CONSOLIDATION_SETS'';');
2000 add_body_line(' init_master_tables(6) := ''PAY_USER_COLUMN_INSTANCES_F'';');
2001 add_body_line(' init_master_tables(7) := ''PER_SPECIAL_INFO_TYPES'';');
2002 add_body_line(' init_master_tables(8) := ''PER_US_OSHA_NUMBERS'';');
2003 add_body_line(' init_master_tables(9) := ''BEN_LER_F'';');
2004 add_body_line(' init_master_tables(10) := ''BEN_ACTN_TYP'';');
2005 add_body_line(' init_master_tables(11) := ''BEN_CM_TYP_F'';');
2006 add_body_line(' init_master_tables(12) := ''BEN_REGN_F'';');
2007 add_body_line('');
2008 add_body_line(' FOR t_index IN 1 .. init_master_tables.COUNT LOOP');
2009 add_body_line(' BEGIN');
2010 add_body_line(' init_table_name :=init_master_tables(t_index);');
2011 add_body_line(' EXECUTE IMMEDIATE ''UPDATE ''||init_table_name||'' ');
2012 add_body_line(' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''', :1)');
2013 add_body_line(' WHERE business_group_id = :2''');
2014 add_body_line(' USING l_enterprise_name');
2015 add_body_line(' ,l_business_group_id;');
2016 add_body_line(' EXCEPTION');
2017 add_body_line(' WHEN OTHERS THEN');
2018 add_body_line(' fnd_file.put_line(fnd_file.log');
2019 add_body_line(' ,''Error in updating:''||init_table_name);');
2020 add_body_line(' END;');
2021 add_body_line(' END LOOP;');
2022 add_body_line('');
2023 add_body_line(' init_child_tables(1).table_name := ''PER_PERSON_TYPES_TL'';');
2024 add_body_line(' init_child_tables(1).child_key_name := ''PERSON_TYPE_ID'';');
2025 add_body_line(' init_child_tables(1).master_table := ''PER_PERSON_TYPES'';');
2026 add_body_line(' init_child_tables(1).master_key_name := ''PERSON_TYPE_ID'';');
2027 add_body_line('');
2028 add_body_line(' init_child_tables(2).table_name := ''PER_SPECIAL_INFO_TYPE_USAGES'';');
2029 add_body_line(' init_child_tables(2).child_key_name := ''SPECIAL_INFORMATION_TYPE_ID'';');
2030 add_body_line(' init_child_tables(2).master_table := ''PER_SPECIAL_INFO_TYPES'';');
2031 add_body_line(' init_child_tables(2).master_key_name := ''SPECIAL_INFORMATION_TYPE_ID''; ');
2032 add_body_line('');
2033 add_body_line(' init_child_tables(3).table_name := ''BEN_LER_F_TL'';');
2034 add_body_line(' init_child_tables(3).child_key_name := ''LER_ID'';');
2035 add_body_line(' init_child_tables(3).master_table := ''BEN_LER_F'';');
2036 add_body_line(' init_child_tables(3).master_key_name := ''LER_ID'';');
2037 add_body_line('');
2038 add_body_line(' init_child_tables(4).table_name := ''BEN_ACTN_TYP_TL'';');
2039 add_body_line(' init_child_tables(4).child_key_name := ''ACTN_TYP_ID'';');
2040 add_body_line(' init_child_tables(4).master_table := ''BEN_ACTN_TYP'';');
2041 add_body_line(' init_child_tables(4).master_key_name := ''ACTN_TYP_ID'';');
2042 add_body_line('');
2043 add_body_line(' init_child_tables(5).table_name := ''BEN_CM_TYP_F_TL'';');
2044 add_body_line(' init_child_tables(5).child_key_name := ''CM_TYP_ID'';');
2045 add_body_line(' init_child_tables(5).master_table := ''BEN_CM_TYP_F'';');
2046 add_body_line(' init_child_tables(5).master_key_name := ''CM_TYP_ID'';');
2047 add_body_line('');
2048 add_body_line(' init_child_tables(6).table_name := ''BEN_REGN_F_TL'';');
2049 add_body_line(' init_child_tables(6).child_key_name := ''REGN_ID'';');
2050 add_body_line(' init_child_tables(6).master_table := ''BEN_REGN_F'';');
2051 add_body_line(' init_child_tables(6).master_key_name := ''REGN_ID'';');
2052 add_body_line(' ');
2053 add_body_line(' FOR t_index IN 1 .. init_child_tables.count LOOP');
2054 add_body_line(' BEGIN');
2055 add_body_line(' EXECUTE IMMEDIATE ''UPDATE '' || init_child_tables(t_index).table_name ||');
2056 add_body_line(' '' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''', :1)'' || ');
2057 add_body_line(' '' WHERE '' || init_child_tables(t_index).child_key_name || '' IN '' || ');
2058 add_body_line(' '' (SELECT '' || init_child_tables(t_index).master_key_name || ');
2059 add_body_line(' '' FROM '' || init_child_tables(t_index).master_table ||');
2060 add_body_line(' '' WHERE business_group_id = :2)''');
2061 add_body_line(' USING l_enterprise_name');
2062 add_body_line(' ,l_business_group_id;');
2063 add_body_line(' EXCEPTION ');
2064 add_body_line(' WHEN OTHERS THEN');
2065 add_body_line(' fnd_file.put_line(fnd_file.log');
2066 add_body_line(' ,''Error in updating:''||init_child_tables(t_index).table_name);');
2067 add_body_line(' END;');
2068 add_body_line(' END LOOP;');
2069 add_body_line('');
2070 add_body_line(' END IF;');
2071 add_body_line(' fnd_file.put_line(fnd_file.log');
2075 add_body_line('--');
2072 add_body_line(' ,''Initializing Control: Enterprise Context'');');
2073 add_body_line('--');
2074 add_body_line('-- In model 1 update entries in the hr_all_organization_units for this enterprise');
2076 add_body_line('EXECUTE IMMEDIATE ''UPDATE hr_all_organization_units');
2077 add_body_line(' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''', :1)');
2078 add_body_line(' WHERE organization_id = :2''');
2079 add_body_line(' USING l_enterprise_name');
2080 add_body_line(' ,p_organization_id;');
2081 add_body_line('');
2082 add_body_line(' UPDATE hr_organization_information');
2083 add_body_line(' SET org_information1 = l_company_label');
2084 add_body_line(' WHERE organization_id = p_organization_id');
2085 add_body_line(' AND org_information_context = ''HR_ENTERPRISE_DETAILS'';');
2086 add_body_line('');
2087 add_body_line('--');
2088 add_body_line('-- In model 1 update entries in the hr_organization_information for this enterprise');
2089 add_body_line('--');
2090 add_body_line('EXECUTE IMMEDIATE ''UPDATE hr_organization_information');
2091 add_body_line(' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',:1 )');
2092 add_body_line(' WHERE organization_id = :2''');
2093 add_body_line(' USING l_enterprise_name');
2094 add_body_line(' ,p_organization_id;');
2095 add_body_line('');
2096 add_body_line('--');
2097 add_body_line('-- In model 1 update entries in the hr_all_organization_units_tl for this enterprise');
2098 add_body_line('--');
2099 add_body_line('IF hr_multi_tenancy_pkg.get_system_model = ''B'' THEN');
2100 add_body_line(' EXECUTE IMMEDIATE ''UPDATE hr_all_organization_units_tl');
2101 add_body_line(' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',:1 )');
2102 add_body_line(' WHERE organization_id = :2''');
2103 add_body_line(' USING l_enterprise_name');
2104 add_body_line(' ,p_organization_id;');
2105 add_body_line('END IF;');
2106 add_body_line('');
2107 add_body_line(' fnd_file.put_line(fnd_file.log');
2108 add_body_line(' ,''Completed Control: Enterprise Context'');');
2109 add_body_line('');
2110 add_body_line(' EXCEPTION');
2111 add_body_line(' WHEN others THEN');
2112 add_body_line(' fnd_file.put_line(fnd_file.output');
2116 add_body_line('');
2113 add_body_line(' ,''Errored OUT :'' || sqlerrm);');
2114 add_body_line(' fnd_file.put_line(fnd_file.log');
2115 add_body_line(' ,''Errored OUT : '' || sqlerrm);');
2117 add_body_line(' END;');
2118 add_body_line(' ELSE');
2119 add_body_line(' fnd_file.put_line(fnd_file.output');
2120 add_body_line(' ,''No organization selected :'');');
2121 add_body_line(' fnd_file.put_line(fnd_file.log');
2122 add_body_line(' ,''No organization selected : '');');
2123 add_body_line('');
2124 add_body_line(' END IF;');
2125 add_body_line(' fnd_file.put_line(fnd_file.log');
2126 add_body_line(' ,''Completed Organization Initialization'');');
2127 add_body_line(' fnd_file.put_line(fnd_file.output');
2128 add_body_line(' ,''Completed Organization Initialization'');');
2129 add_body_line('END initialize_orgs;');
2130 add_body_line('--');
2131 add_body_line('--------------------------------------------------------------------');
2132 add_body_line('--< GET_SCHEMA >----------------------------------------------------');
2133 add_body_line('--------------------------------------------------------------------');
2134 add_body_line('--');
2135 add_body_line('-- Description:');
2136 add_body_line('-- This function will return the oracle user name for the');
2137 add_body_line('-- application short name passed as parameter.');
2138 add_body_line('--');
2139 add_body_line('--');
2140 add_body_line(' function get_schema ');
2141 add_body_line(' (p_app_short_name in varchar2) return varchar2 as');
2142 add_body_line(' l_schema_name varchar2(100);');
2143 add_body_line(' cursor c_schema(c_app_short_name varchar2) is');
2144 add_body_line(' select fou.oracle_username');
2145 add_body_line(' into l_schema_name');
2146 add_body_line(' from fnd_oracle_userid fou');
2147 add_body_line(' ,fnd_product_installations fpi');
2148 add_body_line(' ,fnd_application fap');
2149 add_body_line(' where fou.oracle_id = fpi.oracle_id');
2150 add_body_line(' and fpi.application_id = fap.application_id');
2154 add_body_line(' fetch c_schema into l_schema_name;');
2151 add_body_line(' and fap.application_short_name = c_app_short_name;');
2152 add_body_line(' begin');
2153 add_body_line(' open c_schema(p_app_short_name);');
2155 add_body_line(' close c_schema;');
2156 add_body_line(' return l_schema_name;');
2157 add_body_line('');
2158 add_body_line(' end get_schema;');
2159 add_body_line('');
2160 add_body_line('--');
2161 add_body_line('--------------------------------------------------------------------');
2162 add_body_line('--< MASTER_PROCESS >------------------------------------------------');
2163 add_body_line('--------------------------------------------------------------------');
2164 add_body_line('--');
2165 add_body_line('-- Description:');
2166 add_body_line('-- This procedure is called by the concurrent program');
2167 add_body_line('-- Setup Multi Tenancy for Database Objects.This concurrent program');
2168 add_body_line('-- spawns child processes in batches of size less or equalt to 100. ');
2169 add_body_line('--');
2170 add_body_line(' ');
2171 add_body_line('procedure master_process');
2172 add_body_line(' (errbuf out nocopy varchar2');
2173 add_body_line(' ,retcode out nocopy number');
2174 add_body_line(' ,install_mode in varchar2');
2175 add_body_line(' ,population_size in number default 100) as');
2176 add_body_line(' l_table_count number;');
2177 add_body_line(' l_hr_name varchar2(100);');
2178 add_body_line(' l_ota_name varchar2(100);');
2179 add_body_line(' l_ben_name varchar2(100);');
2180 add_body_line(' l_range number;');
2181 add_body_line(' l_start number;');
2182 add_body_line(' l_end number;');
2183 add_body_line(' l_child_req_id number;');
2184 add_body_line(' l_child_request_status boolean;');
2185 add_body_line(' begin');
2186 add_body_line('--');
2187 add_body_line('-- Fetch the schema names of the applications');
2188 add_body_line('-- that are intended to be OLS applied');
2189 add_body_line('--');
2190 add_body_line(' l_hr_name := get_schema(''PER'');');
2191 add_body_line(' l_ben_name := get_schema(''BEN'');');
2192 add_body_line(' l_ota_name := get_schema(''OTA'');');
2193 add_body_line('');
2194 add_body_line('--');
2195 add_body_line('-- If install mode is SCHEMA then apply policy to all the tables');
2196 add_body_line('-- in the intended schemas. This is for Model 1 only. If the ');
2197 add_body_line('-- install mode is USERTABLE then apply the policy only to tables listed');
2198 add_body_line('-- in the user table HR_OLS_TABLE_LIST');
2199 add_body_line('--');
2200 add_body_line(' if install_mode = ''SCHEMA'' then');
2201 add_body_line(' select count(*) ');
2202 add_body_line(' into l_table_count');
2203 add_body_line(' from all_tables');
2204 add_body_line(' where owner in (l_hr_name, l_ota_name, l_ben_name);');
2205 add_body_line('');
2206 add_body_line(' BEGIN');
2207 add_body_line(' sa_policy_admin.apply_schema_policy(');
2208 add_body_line(' policy_name => ''HR_ENTERPRISE_POLICY''');
2209 add_body_line(' ,schema_name => l_hr_name');
2210 add_body_line(' ,default_options => ''READ_CONTROL, LABEL_DEFAULT, HIDE'');');
2211 add_body_line(' EXCEPTION');
2212 add_body_line(' WHEN OTHERS THEN');
2213 add_body_line(' fnd_file.put_line(fnd_file.log, ''The policy is already applied on the schema '' || l_hr_name);');
2214 add_body_line(' END;');
2215 add_body_line('');
2216 add_body_line(' BEGIN');
2217 add_body_line(' sa_policy_admin.apply_schema_policy(');
2218 add_body_line(' policy_name => ''HR_ENTERPRISE_POLICY''');
2219 add_body_line(' ,schema_name => l_ota_name');
2220 add_body_line(' ,default_options => ''READ_CONTROL, LABEL_DEFAULT, HIDE'');');
2221 add_body_line(' EXCEPTION');
2222 add_body_line(' WHEN OTHERS THEN');
2223 add_body_line(' fnd_file.put_line(fnd_file.log, ''The policy is already applied on the schema '' || l_ota_name);');
2224 add_body_line(' END;');
2225 add_body_line('');
2226 add_body_line(' BEGIN');
2227 add_body_line(' sa_policy_admin.apply_schema_policy(');
2228 add_body_line(' policy_name => ''HR_ENTERPRISE_POLICY''');
2229 add_body_line(' ,schema_name => l_ben_name');
2230 add_body_line(' ,default_options => ''READ_CONTROL, LABEL_DEFAULT, HIDE'');');
2231 add_body_line(' EXCEPTION');
2232 add_body_line(' WHEN OTHERS THEN');
2233 add_body_line(' fnd_file.put_line(fnd_file.log, ''The policy is already applied on the schema '' || l_ben_name);');
2234 add_body_line(' END;');
2235 add_body_line(' elsif install_mode = ''USERTABLE'' then');
2236 add_body_line(' SELECT count(*) ');
2237 add_body_line(' into l_table_count');
2238 add_body_line(' FROM pay_user_rows_f');
2239 add_body_line(' WHERE user_table_id = (SELECT user_table_id');
2240 add_body_line(' FROM pay_user_tables');
2241 add_body_line(' WHERE user_table_name = ''HR_OLS_TABLE_LIST'');');
2242 add_body_line(' end if;');
2243 add_body_line('');
2244 add_body_line(' fnd_file.put_line(fnd_file.log, ''Total Tables : '' || l_table_count);');
2245 add_body_line('');
2246 add_body_line(' l_range := 0;');
2247 add_body_line(' loop');
2248 add_body_line('--');
2249 add_body_line('-- Spawn child processes in specified batch size ');
2250 add_body_line('--');
2251 add_body_line(' if l_range + population_size > l_table_count then');
2252 add_body_line(' l_start := l_range + 1;');
2253 add_body_line(' l_end := l_table_count;');
2254 add_body_line('');
2258 add_body_line(' l_child_req_id := fnd_request.submit_request');
2255 add_body_line(' fnd_file.put_line(fnd_file.log, ''Submitting for range : '' || ');
2256 add_body_line(' to_char(l_start) || '' to '' || to_char(l_end));');
2257 add_body_line('');
2259 add_body_line(' (application => ''PER''');
2260 add_body_line(' ,program => ''HRMTOLSSLV''');
2261 add_body_line(' ,sub_request => false');
2262 add_body_line(' ,argument1 => install_mode');
2263 add_body_line(' ,argument2 => population_size');
2264 add_body_line(' ,argument3 => l_start');
2265 add_body_line(' ,argument4 => l_end);');
2266 add_body_line('');
2267 add_body_line(' commit;');
2268 add_body_line('');
2269 add_body_line(' if l_child_req_id = 0 then');
2270 add_body_line(' fnd_file.put_line(fnd_file.log, ''Error in submitting child request'');');
2271 add_body_line(' else ');
2272 add_body_line(' fnd_file.put_line(fnd_file.log, ''Submitted child request: '' || l_child_req_id);');
2273 add_body_line(' end if;');
2274 add_body_line('');
2275 add_body_line(' exit;');
2276 add_body_line(' else');
2277 add_body_line(' l_start := l_range + 1;');
2278 add_body_line(' l_end := l_range + population_size;');
2279 add_body_line('');
2280 add_body_line(' fnd_file.put_line(fnd_file.log, ''Submitting for range : '' || ');
2281 add_body_line(' to_char(l_start) || '' to '' || to_char(l_end));');
2282 add_body_line('');
2283 add_body_line(' l_child_req_id := fnd_request.submit_request');
2284 add_body_line(' (application => ''PER''');
2285 add_body_line(' ,program => ''HRMTOLSSLV''');
2286 add_body_line(' ,sub_request => false');
2287 add_body_line(' ,argument1 => install_mode');
2288 add_body_line(' ,argument2 => population_size');
2289 add_body_line(' ,argument3 => l_start');
2290 add_body_line(' ,argument4 => l_end);');
2291 add_body_line('');
2292 add_body_line(' commit;');
2293 add_body_line('');
2294 add_body_line(' if l_child_req_id = 0 then');
2295 add_body_line(' fnd_file.put_line(fnd_file.log, ''Error in submitting child request'');');
2296 add_body_line(' else ');
2297 add_body_line(' fnd_file.put_line(fnd_file.log, ''Submitted child request: '' || l_child_req_id);');
2298 add_body_line(' end if;');
2299 add_body_line('');
2300 add_body_line(' end if;');
2301 add_body_line('');
2302 add_body_line(' l_range := l_range + population_size;');
2303 add_body_line(' end loop;');
2304 add_body_line('');
2305 add_body_line(' l_child_request_status := ');
2306 add_body_line(' fnd_concurrent.children_done ');
2307 add_body_line(' (Parent_Request_ID => fnd_global.conc_request_id');
2308 add_body_line(' ,Interval => 20');
2309 add_body_line(' ,Max_Wait => 3600);');
2310 add_body_line('');
2311 add_body_line(' if l_child_request_status then');
2312 add_body_line(' fnd_file.put_line(fnd_file.log, ''Children completed successfully'');');
2313 add_body_line(' else');
2314 add_body_line(' fnd_file.put_line(fnd_file.log, ''Children failed.'');');
2315 add_body_line(' end if;');
2316 add_body_line('');
2317 add_body_line(' end master_process;');
2318 add_body_line('');
2319 add_body_line('--');
2320 add_body_line('--------------------------------------------------------------------');
2321 add_body_line('--< EXECUTE_STATEMENT >------------------------------------------------');
2322 add_body_line('--------------------------------------------------------------------');
2323 add_body_line('--');
2324 add_body_line('-- Description:');
2325 add_body_line('-- This procedure executes three dynamic sql statements. This ');
2326 add_body_line('-- procedure is called in changing table constraints in Model 2.');
2327 add_body_line('--');
2328 add_body_line(' procedure execute_statement (sqlStatement1 IN VARCHAR2');
2329 add_body_line(' ,sqlStatement2 IN VARCHAR2');
2330 add_body_line(' ,sqlStatement3 IN VARCHAR2');
2331 add_body_line(' ,table_name IN VARCHAR2) AS');
2332 add_body_line(' begin');
2333 add_body_line(' begin');
2334 add_body_line(' EXECUTE IMMEDIATE sqlStatement1;');
2335 add_body_line(' exception ');
2336 add_body_line(' when others then');
2337 add_body_line(' fnd_file.put_line(fnd_file.log, ''Exception in 1'' ||table_name );');
2338 add_body_line(' end; ');
2339 add_body_line(' begin');
2340 add_body_line(' EXECUTE IMMEDIATE sqlStatement2;');
2341 add_body_line(' exception ');
2342 add_body_line(' when others then');
2343 add_body_line(' fnd_file.put_line(fnd_file.log, ''Exception in 2'' ||table_name );');
2344 add_body_line(' end; ');
2345 add_body_line(' begin');
2346 add_body_line(' EXECUTE IMMEDIATE sqlStatement3;');
2347 add_body_line(' exception ');
2348 add_body_line(' when others then');
2349 add_body_line(' fnd_file.put_line(fnd_file.log, ''Exception in 3'' ||table_name );');
2350 add_body_line(' end; ');
2351 add_body_line('');
2352 add_body_line('end execute_statement;');
2353 add_body_line('--');
2357 add_body_line('--');
2354 add_body_line('--------------------------------------------------------------------');
2355 add_body_line('--< CHILD_PROCESS >------------------------------------------------');
2356 add_body_line('--------------------------------------------------------------------');
2358 add_body_line('-- Description:');
2359 add_body_line('-- This procedure is called by master_process and is the main');
2360 add_body_line('-- routine which enables the OLS policy on tables depending on the');
2361 add_body_line('-- install mode and the batch size.');
2362 add_body_line('--');
2363 add_body_line(' procedure child_process ');
2364 add_body_line(' (errbuf out nocopy varchar2');
2365 add_body_line(' ,retcode out nocopy number');
2366 add_body_line(' ,install_mode in varchar2');
2367 add_body_line(' ,population_size in number');
2368 add_body_line(' ,population_start in number');
2369 add_body_line(' ,population_end in number) as');
2370 add_body_line(' type mt_table is table of varchar2(256) index by binary_integer;');
2371 add_body_line(' type table_type is table of all_tables.table_name%type;');
2372 add_body_line(' type table_owner_type is table of all_tables.owner%type;');
2373 add_body_line(' type rowtables is table of pay_user_rows_f.row_low_range_or_name%type;');
2374 add_body_line(' type efdtables is table of pay_user_rows_f.effective_start_date%type;');
2375 add_body_line('');
2376 add_body_line(' no_policy_table mt_table;');
2377 add_body_line(' predicate_table mt_table;');
2378 add_body_line(' allowed_table mt_table;');
2379 add_body_line(' seeddata_table mt_table;');
2380 add_body_line(' all_hr_tables table_type;');
2381 add_body_line(' all_hr_table_owners table_owner_type;');
2382 add_body_line(' rowTab rowTables;');
2383 add_body_line(' efdTab efdTables;');
2384 add_body_line('');
2385 add_body_line(' l_hr_name varchar2(100);');
2386 add_body_line(' l_ota_name varchar2(100);');
2387 add_body_line(' l_ben_name varchar2(100);');
2388 add_body_line(' l_table_name all_tables.table_name%type;');
2389 add_body_line(' l_schema_name all_tables.owner%type;');
2390 add_body_line(' l_tab_enabled varchar2(1);');
2391 add_body_line(' l_np_tab boolean;');
2392 add_body_line(' l_predicate varchar2(240);');
2393 add_body_line(' l_upd_tab boolean;');
2394 add_body_line(' l_upd_policy boolean;');
2395 add_body_line(' l_table_options varchar2(300);');
2396 add_body_line('');
2397 add_body_line(' l_table_enabled_value VARCHAR2(4000);');
2398 add_body_line(' l_status VARCHAR2(100); ');
2399 add_body_line(' l_enabled BOOLEAN; ');
2400 add_body_line(' l_effective_start_date DATE;');
2401 add_body_line('');
2402 add_body_line(' cursor acting_tables_schema (c_start number, c_end number, ');
2403 add_body_line(' c_hr_owner varchar2, c_ota_owner varchar2, ');
2404 add_body_line(' c_ben_owner varchar2) is');
2405 add_body_line(' select owner, table_name ');
2406 add_body_line(' from (select owner, ');
2407 add_body_line(' table_name,');
2408 add_body_line(' rownum as colid');
2409 add_body_line(' from all_tables');
2410 add_body_line(' where owner in (c_hr_owner, c_ota_owner, c_ben_owner)');
2411 add_body_line(' order by owner, table_name)');
2412 add_body_line(' where colid between c_start and c_end');
2413 add_body_line(' order by colid;');
2414 add_body_line(' ');
2415 add_body_line(' cursor acting_tables_usertable (c_start number, c_end number) is');
2416 add_body_line(' select row_low_range_or_name, ');
2417 add_body_line(' effective_start_date');
2418 add_body_line(' from (select f.row_low_range_or_name');
2419 add_body_line(' ,f.effective_start_date');
2420 add_body_line(' ,rownum as colid');
2421 add_body_line(' from pay_user_rows_f f, ');
2422 add_body_line(' pay_user_tables t');
2423 add_body_line(' where f.user_table_id = t.user_table_id');
2424 add_body_line(' and t.user_table_name = ''HR_OLS_TABLE_LIST''');
2425 add_body_line(' order by f.row_low_range_or_name)');
2426 add_body_line(' where colid between c_start and c_end');
2427 add_body_line(' order by colid;');
2428 add_body_line('');
2429 add_body_line(' begin');
2430 add_body_line('--');
2431 add_body_line('-- Fetch the schema names for the applications we ');
2432 add_body_line('-- intend to OLS enable.');
2433 add_body_line('--');
2434 add_body_line(' l_hr_name := get_schema(''PER'');');
2435 add_body_line(' l_ben_name := get_schema(''BEN'');');
2436 add_body_line(' l_ota_name := get_schema(''OTA'');');
2437 add_body_line('');
2438 add_body_line('--');
2439 add_body_line('-- There are tables which should not be OLS enabled.');
2440 add_body_line('-- The list contains the name of all these tables.');
2441 add_body_line('--');
2442 add_body_line(' no_policy_table(1) := ''AME_APPROVER_TYPES'';');
2446 add_body_line(' no_policy_table(5) := ''AME_HELP'';');
2443 add_body_line(' no_policy_table(2) := ''AME_ITEM_CLASSES'';');
2444 add_body_line(' no_policy_table(3) := ''AME_ITEM_CLASSES_TL'';');
2445 add_body_line(' no_policy_table(4) := ''AME_FIELD_HELP'';');
2447 add_body_line(' no_policy_table(6) := ''AME_TEMP_HANDLER_STATES'';');
2448 add_body_line('');
2449 add_body_line('--');
2450 add_body_line('-- There are tables which should have a different OLS predicate..');
2451 add_body_line('-- The list contains the name of all these tables.');
2452 add_body_line('--');
2453 add_body_line(' predicate_table(1) :=''PER_NUMBER_GENERATION_CONTROLS'';');
2454 add_body_line(' predicate_table(2) :=''AME_CALLING_APPS'';');
2455 add_body_line(' predicate_table(3) :=''AME_ATTRIBUTE_USAGES'';');
2456 add_body_line(' predicate_table(4) :=''AME_RULES'';');
2457 add_body_line(' predicate_table(5) :=''AME_RULE_USAGES'';');
2458 add_body_line(' predicate_table(6) :=''AME_CONDITION_USAGES'';');
2459 add_body_line(' predicate_table(7) :=''AME_ACTION_USAGES'';');
2460 add_body_line(' predicate_table(8) :=''AME_APPROVAL_GROUPS'';');
2461 add_body_line(' predicate_table(9) :=''AME_APPROVAL_GROUP_CONFIG'';');
2462 add_body_line(' predicate_table(10) :=''AME_APPROVAL_GROUP_ITEMS'';');
2463 add_body_line(' predicate_table(11) :=''AME_APPROVAL_GROUP_MEMBERS'';');
2464 add_body_line(' predicate_table(12) :=''AME_ACTION_TYPE_CONFIG'';');
2465 add_body_line(' predicate_table(13) :=''AME_ITEM_CLASS_USAGES'';');
2466 add_body_line(' predicate_table(14) :=''AME_APPROVAL_GROUPS_TL'';');
2467 add_body_line(' predicate_table(15) :=''AME_CALLING_APPS_TL'';');
2468 add_body_line(' predicate_table(16) :=''AME_RULES_TL'';');
2469 add_body_line(' predicate_table(17) :=''AME_TEST_TRANSACTIONS'';');
2470 add_body_line(' predicate_table(18) :=''AME_TEST_TRANS_ATT_VALUES'';');
2471 add_body_line(' predicate_table(19) :=''AME_TEMP_TRANS_ATT_VALUES'';');
2472 add_body_line(' predicate_table(20) :=''AME_REC_ACCESS_TRANS'';');
2473 add_body_line(' predicate_table(21) :=''AME_TEMP_DELETIONS'';');
2474 add_body_line(' predicate_table(22) :=''AME_TEMP_INSERTIONS'';');
2475 add_body_line(' predicate_table(23) :=''AME_TEMP_OLD_APPROVER_LISTS'';');
2476 add_body_line(' predicate_table(24) :=''AME_TEMP_TRANSACTIONS'';');
2477 add_body_line(' predicate_table(25) :=''AME_TEMP_TRANS_LOCKS'';');
2478 add_body_line(' predicate_table(26) :=''AME_APPROVALS_HISTORY'';');
2479 add_body_line(' predicate_table(27) :=''AME_EXCEPTIONS_LOG'';');
2480 add_body_line(' predicate_table(28) :=''AME_TXN_APPROVERS'';');
2481 add_body_line(' predicate_table(29) :=''AME_TRANS_APPROVAL_HISTORY'';');
2482 add_body_line(' -- Fix for 7645461 ');
2483 add_body_line(' predicate_table(30) :=''HR_NAME_FORMATS''; ');
2484 add_body_line('');
2485 add_body_line('-- There are tables which should have a different table options..');
2486 add_body_line('--');
2487 add_body_line(' --HR Tables');
2488 add_body_line(' seeddata_table(1) := ''FF_ROUTE_PARAMETER_VALUES'';');
2489 add_body_line(' seeddata_table(2) := ''FF_USER_ENTITIES'';');
2490 add_body_line(' seeddata_table(3) := ''FF_DATABASE_ITEMS'';');
2491 add_body_line(' seeddata_table(4) := ''FF_DATABASE_ITEMS_TL'';');
2492 add_body_line(' seeddata_table(5) := ''PAY_USER_COLUMN_INSTANCES_F'';');
2493 add_body_line(' seeddata_table(6) := ''GHR_DUTY_STATIONS_F'';');
2494 add_body_line(' seeddata_table(7) := ''HR_ITEM_PROPERTIES_TL'';');
2495 add_body_line(' seeddata_table(8) := ''PAY_US_CITY_NAMES'';');
2496 add_body_line(' seeddata_table(9) := ''HR_ITEM_PROPERTIES_B'';');
2497 add_body_line(' seeddata_table(10) := ''PAY_DEFINED_BALANCES'';');
2498 add_body_line(' seeddata_table(11) := ''HR_TEMPLATE_ITEM_CONTEXT_PAGES'';');
2499 add_body_line(' seeddata_table(12) := ''HR_TEMPLATE_ITEM_CONTEXTS_B'';');
2500 add_body_line(' seeddata_table(13) := ''PQH_ATTRIBUTES_TL'';');
2501 add_body_line(' seeddata_table(14) := ''PAY_US_CITY_TAX_INFO_F'';');
2502 add_body_line(' seeddata_table(15) := ''HR_TEMPLATE_ITEMS_B'';');
2503 add_body_line(' seeddata_table(16) := ''PQH_ATTRIBUTES'';');
2504 add_body_line(' seeddata_table(17) := ''PQH_TXN_CATEGORY_ATTRIBUTES'';');
2505 add_body_line(' seeddata_table(18) := ''PAY_USER_ROWS_F'';');
2506 add_body_line(' seeddata_table(19) := ''HR_TEMPLATE_ITEM_TAB_PAGES'';');
2507 add_body_line(' seeddata_table(20) := ''PAY_INPUT_VALUES_F'';');
2508 add_body_line(' seeddata_table(21) := ''PAY_INPUT_VALUES_F_TL'';');
2509 add_body_line(' seeddata_table(22) := ''PAY_USER_ROWS_F_TL'';');
2510 add_body_line(' seeddata_table(23) := ''PAY_BALANCE_FEEDS_F'';');
2511 add_body_line(' seeddata_table(24) := ''PAY_FORMULA_RESULT_RULES_F'';');
2512 add_body_line(' seeddata_table(25) := ''FF_FUNCTION_PARAMETERS'';');
2513 add_body_line(' seeddata_table(26) := ''GHR_NOAC_REMARKS'';');
2514 add_body_line(' seeddata_table(27) := ''DT_COLUMN_PROMPTS_TL'';');
2515 add_body_line(' seeddata_table(28) := ''HR_API_HOOKS'';');
2516 add_body_line(' seeddata_table(29) := ''AME_ATTRIBUTE_USAGES'';');
2517 add_body_line(' seeddata_table(30) := ''PAY_TAXABILITY_RULES'';');
2518 add_body_line(' seeddata_table(31) := ''PAY_USER_COLUMNS_TL'';');
2519 add_body_line(' seeddata_table(32) := ''PAY_USER_COLUMNS'';');
2520 add_body_line(' seeddata_table(33) := ''PAY_SHADOW_DEFINED_BALANCES'';');
2521 add_body_line(' seeddata_table(34) := ''GHR_NOA_FAM_PROC_METHODS'';');
2522 add_body_line(' seeddata_table(35) := ''PQH_SPECIAL_ATTRIBUTES'';');
2523 add_body_line(' seeddata_table(36) := ''PAY_BALANCE_TYPES_TL'';');
2524 add_body_line(' seeddata_table(37) := ''PAY_BALANCE_TYPES'';');
2525 add_body_line(' seeddata_table(38) := ''HR_FORM_ITEMS_TL'';');
2526 add_body_line(' seeddata_table(39) := ''AME_ACTION_TYPE_CONFIG'';');
2527 add_body_line(' seeddata_table(40) := ''HR_NAVIGATION_PATHS_TL'';');
2528 add_body_line(' seeddata_table(41) := ''AME_APPROVAL_GROUP_CONFIG'';');
2532 add_body_line(' seeddata_table(45) := ''PAY_ELEMENT_TYPES_F_TL'';');
2529 add_body_line(' seeddata_table(42) := ''GHR_NOAC_LAS'';');
2530 add_body_line(' seeddata_table(43) := ''PAY_ELEMENT_TYPES_F'';');
2531 add_body_line(' seeddata_table(44) := ''HR_PUMP_MODULE_PARAMETERS'';');
2533 add_body_line(' seeddata_table(46) := ''FF_FUNCTION_CONTEXT_USAGES'';');
2534 add_body_line(' seeddata_table(47) := ''PAY_SHADOW_INPUT_VALUES'';');
2535 add_body_line(' seeddata_table(48) := ''PER_ESTABLISHMENTS'';');
2536 add_body_line(' seeddata_table(49) := ''FF_FORMULAS_F'';');
2537 add_body_line(' seeddata_table(50) := ''PAY_BALANCE_CLASSIFICATIONS'';');
2538 add_body_line(' seeddata_table(51) := ''HR_FORM_ITEMS_B'';');
2539 add_body_line(' seeddata_table(52) := ''HR_API_MODULES'';');
2540 add_body_line(' seeddata_table(53) := ''PER_RI_VIEW_REPORTS_TL'';');
2541 add_body_line(' seeddata_table(54) := ''HR_DM_HIERARCHIES'';');
2542 add_body_line(' seeddata_table(55) := ''FF_FORMULAS_F_TL'';');
2543 add_body_line(' seeddata_table(56) := ''FF_ROUTES'';');
2544 add_body_line(' seeddata_table(57) := ''FF_GLOBALS_F'';');
2545 add_body_line(' seeddata_table(58) := ''PAY_SHADOW_BALANCE_FEEDS'';');
2546 add_body_line(' seeddata_table(59) := ''FF_FUNCTIONS'';');
2547 add_body_line(' seeddata_table(60) := ''GHR_POIS'';');
2548 add_body_line(' seeddata_table(61) := ''HR_DM_TABLE_GROUPINGS'';');
2549 add_body_line(' seeddata_table(62) := ''PAY_SUB_CLASSIFICATION_RULES_F'';');
2550 add_body_line(' seeddata_table(63) := ''PAY_SHADOW_FORMULA_RULES'';');
2551 add_body_line(' seeddata_table(64) := ''PAY_ELEMENT_CLASSIFICATIONS'';');
2552 add_body_line(' seeddata_table(65) := ''PAY_ELEMENT_CLASSIFICATIONS_TL'';');
2553 add_body_line(' seeddata_table(66) := ''AME_ATTRIBUTES_TL'';');
2554 add_body_line(' seeddata_table(67) := ''PQH_TEMPLATE_ATTRIBUTES'';');
2555 add_body_line(' seeddata_table(68) := ''GHR_NOA_FAMILIES'';');
2556 add_body_line(' seeddata_table(69) := ''HR_TAB_PAGE_PROPERTIES_TL'';');
2557 add_body_line(' seeddata_table(70) := ''PAY_BALANCE_DIMENSIONS_TL'';');
2558 add_body_line(' seeddata_table(71) := ''PER_RI_VIEW_REPORTS'';');
2559 add_body_line(' seeddata_table(72) := ''PAY_DIMENSION_ROUTES'';');
2560 add_body_line(' seeddata_table(73) := ''AME_ATTRIBUTES'';');
2561 add_body_line(' seeddata_table(74) := ''FF_GLOBALS_F_TL'';');
2562 add_body_line(' seeddata_table(75) := ''PAY_REPORT_FORMAT_ITEMS_F'';');
2563 add_body_line(' seeddata_table(76) := ''HR_TAB_PAGE_PROPERTIES_B'';');
2564 add_body_line(' seeddata_table(77) := ''HR_TEMPLATE_TAB_PAGES_B'';');
2565 add_body_line(' seeddata_table(78) := ''PAY_BALANCE_ATTRIBUTES'';');
2566 add_body_line(' seeddata_table(79) := ''PAY_ELEMENT_TYPE_USAGES_F'';');
2567 add_body_line(' seeddata_table(80) := ''HR_ORG_INFORMATION_TYPES_TL'';');
2568 add_body_line(' seeddata_table(81) := ''PAY_RETRO_COMPONENT_USAGES'';');
2569 add_body_line(' seeddata_table(82) := ''PQH_TABLE_ROUTE_TL'';');
2570 add_body_line(' seeddata_table(83) := ''PER_TIME_PERIODS'';');
2571 add_body_line(' seeddata_table(84) := ''HR_API_HOOK_CALLS'';');
2572 add_body_line(' seeddata_table(85) := ''PAY_DATETRACKED_EVENTS'';');
2573 add_body_line(' seeddata_table(86) := ''PAY_STATUS_PROCESSING_RULES_F'';');
2574 add_body_line(' seeddata_table(87) := ''PAY_USER_TABLES_TL'';');
2575 add_body_line(' seeddata_table(88) := ''PAY_USER_TABLES'';');
2576 add_body_line(' seeddata_table(89) := ''AME_ACTIONS_TL'';');
2577 add_body_line(' seeddata_table(90) := ''HR_DM_TABLES'';');
2578 add_body_line(' seeddata_table(91) := ''PAY_REPORT_FORMAT_MAPPINGS_F'';');
2579 add_body_line(' seeddata_table(92) := ''PER_INFO_TYPE_SECURITY'';');
2580 add_body_line(' seeddata_table(93) := ''PAY_SHADOW_BALANCE_TYPES'';');
2581 add_body_line(' seeddata_table(94) := ''PAY_US_GARN_EXEMPTION_RULES_F'';');
2582 add_body_line(' seeddata_table(95) := ''DT_TITLE_PROMPTS_TL'';');
2583 add_body_line(' seeddata_table(96) := ''AME_ACTIONS'';');
2584 add_body_line(' seeddata_table(97) := ''AME_CALLING_APPS_TL'';');
2585 add_body_line(' seeddata_table(98) := ''PAY_TEMPLATE_EXCLUSION_RULES'';');
2586 add_body_line(' seeddata_table(99) := ''HR_DU_COLUMN_MAPPINGS'';');
2587 add_body_line(' seeddata_table(100) := ''PAY_ELEMENT_SPAN_USAGES'';');
2588 add_body_line(' seeddata_table(101) := ''HR_CANVAS_PROPERTIES'';');
2589 add_body_line(' seeddata_table(102) := ''PAY_FR_CONTRIBUTION_USAGES'';');
2590 add_body_line(' seeddata_table(103) := ''PAY_REPORT_FORMAT_PARAMETERS'';');
2591 add_body_line(' seeddata_table(104) := ''HR_ORG_INFO_TYPES_BY_CLASS'';');
2592 add_body_line(' seeddata_table(105) := ''PAY_SHADOW_ELEMENT_TYPES'';');
2593 add_body_line(' seeddata_table(106) := ''PAY_US_COUNTY_TAX_INFO_F'';');
2594 add_body_line(' seeddata_table(107) := ''HR_TEMPLATE_CANVASES_B'';');
2595 add_body_line(' seeddata_table(108) := ''GHR_LOCALITY_PAY_AREAS_F'';');
2596 add_body_line(' seeddata_table(109) := ''HR_ORG_INFORMATION_TYPES'';');
2597 add_body_line(' seeddata_table(110) := ''PAY_US_STATE_TAX_INFO_F'';');
2598 add_body_line(' seeddata_table(111) := ''PAY_EVENT_UPDATES'';');
2599 add_body_line(' seeddata_table(112) := ''PQH_TABLE_ROUTE'';');
2600 add_body_line(' seeddata_table(113) := ''GHR_REMARKS'';');
2601 add_body_line(' seeddata_table(114) := ''PAY_BALANCE_CATEGORIES_F'';');
2602 add_body_line(' seeddata_table(115) := ''PAY_BALANCE_CATEGORIES_F_TL'';');
2603 add_body_line(' seeddata_table(116) := ''PQP_FLXDU_COLUMNS'';');
2604 add_body_line(' seeddata_table(117) := ''AME_ITEM_CLASS_USAGES'';');
2605 add_body_line(' seeddata_table(118) := ''HR_NAVIGATION_UNITS_TL'';');
2606 add_body_line(' seeddata_table(119) := ''PER_US_OSHA_NUMBERS'';');
2607 add_body_line(' seeddata_table(120) := ''GHR_PLAN_SERVICE_AREAS_F'';');
2608 add_body_line(' seeddata_table(121) := ''HR_WINDOW_PROPERTIES_TL'';');
2612 add_body_line(' seeddata_table(125) := ''HR_DM_GROUPS'';');
2609 add_body_line(' seeddata_table(122) := ''AME_CALLING_APPS'';');
2610 add_body_line(' seeddata_table(123) := ''PAY_ELE_CLASSIFICATION_RULES'';');
2611 add_body_line(' seeddata_table(124) := ''HR_DM_APPLICATION_GROUPS'';');
2613 add_body_line(' seeddata_table(126) := ''PER_SOLUTION_CMPT_NAMES'';');
2614 add_body_line(' seeddata_table(127) := ''PER_SOLUTIONS'';');
2615 add_body_line(' seeddata_table(128) := ''PAY_AU_MODULE_PARAMETERS'';');
2616 add_body_line(' seeddata_table(129) := ''PER_STANDARD_HOLIDAYS'';');
2617 add_body_line(' seeddata_table(130) := ''HR_WINDOW_PROPERTIES_B'';');
2618 add_body_line(' seeddata_table(131) := ''HR_TEMPLATE_WINDOWS_B'';');
2619 add_body_line(' seeddata_table(132) := ''PAY_US_GARN_FEE_RULES_F'';');
2620 add_body_line(' seeddata_table(133) := ''HR_FORM_TEMPLATES_TL'';');
2621 add_body_line(' seeddata_table(134) := ''PER_ASSIGNMENT_INFO_TYPES_TL'';');
2622 add_body_line(' seeddata_table(135) := ''GHR_PA_DATA_FIELDS'';');
2623 add_body_line(' seeddata_table(136) := ''GHR_NATURE_OF_ACTIONS'';');
2624 add_body_line(' seeddata_table(137) := ''PAY_LEG_SETUP_DEFAULTS'';');
2625 add_body_line(' seeddata_table(138) := ''FF_FORMULA_TYPES'';');
2626 add_body_line(' seeddata_table(139) := ''GHR_DUAL_ACTIONS'';');
2627 add_body_line(' seeddata_table(140) := ''PAY_SHADOW_FORMULAS'';');
2628 add_body_line(' seeddata_table(141) := ''PER_PEOPLE_INFO_TYPES'';');
2629 add_body_line(' seeddata_table(142) := ''PER_RI_SETUP_SUB_TASKS_TL'';');
2630 add_body_line(' seeddata_table(143) := ''AME_STRING_VALUES'';');
2631 add_body_line(' seeddata_table(144) := ''PER_SHARED_TYPES_TL'';');
2632 add_body_line(' seeddata_table(145) := ''PAY_BAL_ATTRIBUTE_DEFINITIONS'';');
2633 add_body_line(' seeddata_table(146) := ''PAY_RUN_TYPES_F_TL'';');
2634 add_body_line(' seeddata_table(147) := ''PAY_MONETARY_UNITS'';');
2635 add_body_line(' seeddata_table(148) := ''PAY_MONETARY_UNITS_TL'';');
2636 add_body_line(' seeddata_table(149) := ''PAY_MX_LEGISLATION_INFO_F'';');
2637 add_body_line(' seeddata_table(150) := ''PER_ASSIGNMENT_INFO_TYPES'';');
2638 add_body_line(' seeddata_table(151) := ''PAY_RUN_TYPE_USAGES_F'';');
2639 add_body_line(' seeddata_table(152) := ''PAY_EVENT_GROUPS'';');
2640 add_body_line(' seeddata_table(153) := ''PER_RI_SETUP_TASKS_TL'';');
2641 add_body_line(' seeddata_table(154) := ''AME_RULES_TL'';');
2642 add_body_line(' seeddata_table(155) := ''PAY_ROUTE_TO_DESCR_FLEXS'';');
2643 add_body_line(' seeddata_table(156) := ''PER_CAGR_API_PARAMETERS_TL'';');
2644 add_body_line(' seeddata_table(157) := ''GHR_FAMILIES'';');
2645 add_body_line(' seeddata_table(158) := ''AME_CONDITION_USAGES'';');
2646 add_body_line(' seeddata_table(159) := ''AME_CONDITIONS'';');
2647 add_body_line(' seeddata_table(160) := ''HR_FORM_TEMPLATES_B'';');
2648 add_body_line(' seeddata_table(161) := ''HR_FORM_PROPERTIES'';');
2649 add_body_line(' seeddata_table(162) := ''PAY_RUN_TYPES_F'';');
2650 add_body_line(' seeddata_table(163) := ''PAY_TRIGGER_EVENTS'';');
2651 add_body_line(' seeddata_table(164) := ''PQP_CONFIGURATION_VALUES'';');
2652 add_body_line(' seeddata_table(165) := ''PAY_ELEMENT_TEMPLATES'';');
2653 add_body_line(' seeddata_table(166) := ''PAY_RESTRICTION_PARAMETERS'';');
2654 add_body_line(' seeddata_table(167) := ''GHR_PAY_PLANS'';');
2655 add_body_line(' seeddata_table(168) := ''PAY_PAYMENT_TYPES_TL'';');
2656 add_body_line(' seeddata_table(169) := ''PER_PROPOSAL_QUESTION_TYPES'';');
2657 add_body_line(' seeddata_table(170) := ''AME_MANDATORY_ATTRIBUTES'';');
2658 add_body_line(' seeddata_table(171) := ''PAY_ELEMENT_TYPE_EXTRA_INFO'';');
2659 add_body_line(' seeddata_table(172) := ''PER_PROPOSAL_QUESTION_MEMBERS'';');
2660 add_body_line(' seeddata_table(173) := ''HR_ITEM_CONTEXTS'';');
2661 add_body_line(' seeddata_table(174) := ''AME_CONFIG_VARS'';');
2662 add_body_line(' seeddata_table(175) := ''PAY_UPGRADE_DEFINITIONS_TL'';');
2663 add_body_line(' seeddata_table(176) := ''HR_REPORT_LOOKUPS'';');
2664 add_body_line(' seeddata_table(177) := ''PAY_SHADOW_ELE_TYPE_USAGES'';');
2665 add_body_line(' seeddata_table(178) := ''PAY_ELEMENT_TYPE_RULES'';');
2666 add_body_line(' seeddata_table(179) := ''PER_RI_SETUP_SUB_TASKS'';');
2667 add_body_line(' seeddata_table(180) := ''GHR_PA_REQUEST_INFO_TYPES'';');
2668 add_body_line(' seeddata_table(181) := ''PER_ASSIGNMENT_STATUS_TYPES_TL'';');
2669 add_body_line(' seeddata_table(182) := ''PAY_MX_EARN_EXEMPTION_RULES_F'';');
2670 add_body_line(' seeddata_table(183) := ''PAY_REPORT_VARIABLES'';');
2671 add_body_line(' seeddata_table(184) := ''PAY_BAL_ATTRIBUTE_DEFAULTS'';');
2672 add_body_line(' seeddata_table(185) := ''PAY_REPORT_CATEGORY_COMPONENTS'';');
2673 add_body_line(' seeddata_table(186) := ''PQP_ALIEN_STATE_TREATIES_F'';');
2674 add_body_line(' seeddata_table(187) := ''PER_SHARED_TYPES'';');
2675 add_body_line(' seeddata_table(188) := ''HR_LEGISLATION_INSTALLATIONS'';');
2676 add_body_line(' seeddata_table(189) := ''HR_KI_HIERARCHIES_TL'';');
2677 add_body_line(' seeddata_table(190) := ''PAY_STATE_RULES'';');
2678 add_body_line(' seeddata_table(191) := ''PAY_REPORT_DEFINITIONS'';');
2679 add_body_line(' seeddata_table(192) := ''PER_RI_CONFIG_RESPONSIBILITY'';');
2680 add_body_line(' seeddata_table(193) := ''GHR_RESTRICTED_PROC_METHODS'';');
2681 add_body_line(' seeddata_table(194) := ''PER_GEN_HIER_NODE_TYPES'';');
2682 add_body_line(' seeddata_table(195) := ''PQP_CONFIGURATION_TYPES'';');
2683 add_body_line(' seeddata_table(196) := ''PER_GEN_HIERARCHY_NODES'';');
2684 add_body_line(' seeddata_table(197) := ''PAY_FUNCTIONAL_TRIGGERS'';');
2685 add_body_line(' seeddata_table(198) := ''PQP_EXCEPTION_REPORT_SUFFIX'';');
2686 add_body_line(' seeddata_table(199) := ''PAY_PAYMENT_TYPES'';');
2687 add_body_line(' seeddata_table(200) := ''HR_DM_PHASE_RULES'';');
2688 add_body_line(' seeddata_table(201) := ''PAY_TRIGGER_COMPONENTS'';');
2692 add_body_line(' seeddata_table(205) := ''PAY_ELEMENT_SETS_TL'';');
2689 add_body_line(' seeddata_table(202) := ''PAY_SHADOW_SUB_CLASSI_RULES'';');
2690 add_body_line(' seeddata_table(203) := ''PQH_TRANSACTION_CATEGORIES_TL'';');
2691 add_body_line(' seeddata_table(204) := ''HR_FORM_TAB_PAGES_TL'';');
2693 add_body_line(' seeddata_table(206) := ''PAY_ELEMENT_SETS'';');
2694 add_body_line(' seeddata_table(207) := ''AME_RULE_USAGES'';');
2695 add_body_line(' seeddata_table(208) := ''PER_PERSON_TYPES_TL'';');
2696 add_body_line(' seeddata_table(209) := ''PER_RI_SETUP_TASKS'';');
2697 add_body_line(' seeddata_table(210) := ''HR_PUMP_MAPPING_PACKAGES'';');
2698 add_body_line(' seeddata_table(211) := ''AME_RULES'';');
2699 add_body_line(' seeddata_table(212) := ''PER_STARTUP_PERSON_TYPES_TL'';');
2700 add_body_line(' seeddata_table(213) := ''AME_ACTION_TYPES'';');
2701 add_body_line(' seeddata_table(214) := ''PAY_US_GARN_LIMIT_RULES_F'';');
2702 add_body_line(' seeddata_table(215) := ''PER_CAGR_API_PARAMETERS'';');
2703 add_body_line(' seeddata_table(216) := ''PER_RI_WORKBENCH_ITEMS_TL'';');
2704 add_body_line(' seeddata_table(217) := ''AME_ACTION_USAGES'';');
2705 add_body_line(' seeddata_table(218) := ''PAY_UPGRADE_LEGISLATIONS'';');
2706 add_body_line(' seeddata_table(219) := ''PAY_US_FEDERAL_TAX_INFO_F'';');
2707 add_body_line(' seeddata_table(220) := ''AME_ACTION_TYPE_USAGES'';');
2708 add_body_line(' seeddata_table(221) := ''PQH_COPY_ENTITY_FUNCTIONS'';');
2709 add_body_line(' seeddata_table(222) := ''HR_DM_LOADER_PARAMS'';');
2710 add_body_line(' seeddata_table(223) := ''HR_ATH_VARIABLEMAP'';');
2711 add_body_line(' seeddata_table(224) := ''PAY_UPGRADE_DEFINITIONS'';');
2712 add_body_line(' seeddata_table(225) := ''PAY_EVENT_VALUE_CHANGES_F'';');
2713 add_body_line(' seeddata_table(226) := ''PAY_DATED_TABLES'';');
2714 add_body_line(' seeddata_table(227) := ''HR_DOCUMENT_TYPES_TL'';');
2715 add_body_line(' seeddata_table(228) := ''PAY_ELEMENT_TYPE_INFO_TYPES'';');
2716 add_body_line(' seeddata_table(229) := ''PAY_TEMPLATE_FF_USAGES'';');
2717 add_body_line(' seeddata_table(230) := ''AME_APPROVAL_GROUPS_TL'';');
2718 add_body_line(' seeddata_table(231) := ''AME_ACTION_TYPES_TL'';');
2719 add_body_line(' seeddata_table(232) := ''PER_ASSIGNMENT_STATUS_TYPES'';');
2720 add_body_line(' seeddata_table(233) := ''HR_KI_HIERARCHIES'';');
2721 add_body_line(' seeddata_table(234) := ''PQH_RULE_SETS_TL'';');
2722 add_body_line(' seeddata_table(235) := ''PER_PROPOSAL_CATEGORY_TYPES'';');
2723 add_body_line(' seeddata_table(236) := ''HR_FORM_TAB_STACKED_CANVASES'';');
2724 add_body_line(' seeddata_table(237) := ''HR_FORM_CANVASES_TL'';');
2725 add_body_line(' seeddata_table(238) := ''PAY_REPORT_CATEGORIES'';');
2726 add_body_line(' seeddata_table(239) := ''PER_PROPOSAL_OFFER_PARAGRAPHS'';');
2727 add_body_line(' seeddata_table(240) := ''PQH_TEMPLATES_TL'';');
2728 add_body_line(' seeddata_table(241) := ''HR_FORM_TAB_PAGES_B'';');
2729 add_body_line(' seeddata_table(242) := ''PQH_TRANSACTION_CATEGORIES'';');
2730 add_body_line(' seeddata_table(243) := ''PER_PERSON_TYPES'';');
2731 add_body_line(' seeddata_table(244) := ''PER_RI_WORKBENCH_ITEMS'';');
2732 add_body_line(' seeddata_table(245) := ''GHR_DUAL_PROC_METHODS'';');
2733 add_body_line(' seeddata_table(246) := ''PAY_RESTRICTION_VALUES'';');
2734 add_body_line(' seeddata_table(247) := ''PER_COBRA_COVERAGE_PERIODS'';');
2735 add_body_line(' seeddata_table(248) := ''PQP_EXTRACT_ATTRIBUTES'';');
2736 add_body_line(' seeddata_table(249) := ''PAY_AU_MODULES'';');
2737 add_body_line(' seeddata_table(250) := ''GHR_PREMIUM_PAY_INDICATORS'';');
2738 add_body_line(' seeddata_table(251) := ''AME_CONFIG_VARS_TL'';');
2739 add_body_line(' seeddata_table(252) := ''PQP_EXCEPTION_REPORTS'';');
2740 add_body_line(' seeddata_table(253) := ''PQP_EXCEPTION_REPORTS_TL'';');
2741 add_body_line(' seeddata_table(254) := ''FF_FORMULA_TYPE_COMPONENTS'';');
2742 add_body_line(' seeddata_table(255) := ''PAY_REPORT_GROUPS'';');
2743 add_body_line(' seeddata_table(256) := ''PAY_AU_PROCESS_PARAMETERS'';');
2744 add_body_line(' seeddata_table(257) := ''PAY_UPGRADE_PARAMETERS'';');
2745 add_body_line(' seeddata_table(258) := ''PER_PROPOSAL_CATEGORY_MEMBERS'';');
2746 add_body_line(' seeddata_table(259) := ''PQH_COPY_ENTITY_CONTEXTS'';');
2747 add_body_line(' seeddata_table(260) := ''PER_PROPOSAL_QUESTIONS_ADV'';');
2748 add_body_line(' seeddata_table(261) := ''PER_RI_RT_METADATA'';');
2749 add_body_line(' seeddata_table(262) := ''PER_TIME_PERIOD_TYPES_TL'';');
2750 add_body_line(' seeddata_table(263) := ''HR_LOCATION_INFO_TYPES'';');
2751 add_body_line(' seeddata_table(264) := ''HR_DOCUMENT_TYPES'';');
2752 add_body_line(' seeddata_table(265) := ''AME_APPROVER_TYPE_USAGES'';');
2753 add_body_line(' seeddata_table(266) := ''PER_POSITION_INFO_TYPES'';');
2754 add_body_line(' seeddata_table(267) := ''AME_APPROVAL_GROUPS'';');
2755 add_body_line(' seeddata_table(268) := ''PAY_CUSTOM_RESTRICTIONS_TL'';');
2756 add_body_line(' seeddata_table(269) := ''PQH_RULES'';');
2757 add_body_line(' seeddata_table(270) := ''PAY_ITERATIVE_RULES_F'';');
2758 add_body_line(' seeddata_table(271) := ''FF_LOOKUPS'';');
2759 add_body_line(' seeddata_table(272) := ''AME_ITEM_CLASSES_TL'';');
2760 add_body_line(' seeddata_table(273) := ''PQH_RULE_SETS'';');
2761 add_body_line(' seeddata_table(274) := ''PAY_AU_PROCESS_MODULES'';');
2762 add_body_line(' seeddata_table(275) := ''PQP_CONFIGURATION_MODULES'';');
2763 add_body_line(' seeddata_table(276) := ''PQH_WIZARD_CANVASES'';');
2764 add_body_line(' seeddata_table(277) := ''PER_RI_DEPENDENCIES'';');
2765 add_body_line(' seeddata_table(278) := ''HR_FORM_CANVASES_B'';');
2766 add_body_line(' seeddata_table(279) := ''PAY_EVENT_QUALIFIERS_F'';');
2767 add_body_line(' seeddata_table(280) := ''PQH_TEMPLATES'';');
2768 add_body_line(' seeddata_table(281) := ''PAY_CUSTOMIZED_RESTRICTIONS'';');
2772 add_body_line(' seeddata_table(285) := ''PAY_FUNCTIONAL_AREAS'';');
2769 add_body_line(' seeddata_table(282) := ''GHR_PAY_PLAN_WAITING_PERIODS'';');
2770 add_body_line(' seeddata_table(283) := ''PER_CONTACT_INFO_TYPES_TL'';');
2771 add_body_line(' seeddata_table(284) := ''PAY_SHADOW_ITERATIVE_RULES'';');
2773 add_body_line(' seeddata_table(286) := ''GHR_MASS_AWARD_CRITERIA_COLS'';');
2774 add_body_line(' seeddata_table(287) := ''BEN_BENEFIT_CLASSIFICATIONS'';');
2775 add_body_line(' seeddata_table(288) := ''AME_ITEM_CLASSES'';');
2776 add_body_line(' seeddata_table(289) := ''PQH_REF_TEMPLATES'';');
2777 add_body_line(' seeddata_table(290) := ''PER_TIME_PERIOD_RULES'';');
2778 add_body_line(' seeddata_table(291) := ''HR_NAME_FORMATS'';');
2779 add_body_line(' seeddata_table(292) := ''PER_TIME_PERIOD_TYPES'';');
2780 add_body_line(' seeddata_table(293) := ''PER_SOLUTION_TYPE_CMPTS'';');
2781 add_body_line(' seeddata_table(294) := ''HR_KI_OPTION_TYPES_TL'';');
2782 add_body_line(' seeddata_table(295) := ''PER_COBRA_QFYING_EVENTS_F'';');
2783 add_body_line(' seeddata_table(296) := ''PER_CAGR_APIS_TL'';');
2784 add_body_line(' seeddata_table(297) := ''PAY_EVENT_PROCEDURES'';');
2785 add_body_line(' seeddata_table(298) := ''PER_CONTACT_INFO_TYPES'';');
2786 add_body_line(' seeddata_table(299) := ''PAY_SHADOW_BAL_ATTRIBUTES'';');
2787 add_body_line(' seeddata_table(300) := ''HR_FORM_WINDOWS_TL'';');
2788 add_body_line(' seeddata_table(301) := ''PQH_ROLE_INFO_TYPES'';');
2789 add_body_line(' seeddata_table(302) := ''PER_SCH_INHERIT_HIER'';');
2790 add_body_line(' seeddata_table(303) := ''PER_GEN_HIERARCHY'';');
2791 add_body_line(' seeddata_table(304) := ''HR_PUMP_DEFAULT_EXCEPTIONS'';');
2792 add_body_line(' seeddata_table(305) := ''HR_ORGANIZATION_INFORMATION'';');
2793 add_body_line(' seeddata_table(306) := ''PER_GEN_HIERARCHY_VERSIONS'';');
2794 add_body_line(' seeddata_table(307) := ''PER_PREV_JOB_INFO_TYPES'';');
2795 add_body_line(' seeddata_table(308) := ''PQH_PTX_INFO_TYPES'';');
2796 add_body_line(' seeddata_table(309) := ''PQP_VEH_REPOS_INFO_TYPES'';');
2797 add_body_line(' seeddata_table(310) := ''HR_PATTERN_PURPOSE_USAGES'';');
2798 add_body_line(' seeddata_table(311) := ''PER_CAGR_APIS'';');
2799 add_body_line(' seeddata_table(312) := ''HR_KI_OPTION_TYPES'';');
2800 add_body_line(' seeddata_table(313) := ''PAY_FUNCTIONAL_USAGES'';');
2801 add_body_line(' seeddata_table(314) := ''PAY_AU_MODULE_TYPES'';');
2802 add_body_line(' seeddata_table(315) := ''PAY_AU_PROCESSES'';');
2803 add_body_line(' seeddata_table(316) := ''HR_FORM_WINDOWS_B'';');
2804 add_body_line(' seeddata_table(317) := ''HR_DU_MODULES'';');
2805 add_body_line(' seeddata_table(318) := ''AME_APPROVER_TYPES'';');
2806 add_body_line(' seeddata_table(319) := ''PER_JOB_INFO_TYPES'';');
2807 add_body_line(' seeddata_table(320) := ''PER_SOLUTION_TYPES'';');
2808 add_body_line(' seeddata_table(321) := ''PQH_COPY_ENTITY_ATTRIBS'';');
2809 add_body_line(' seeddata_table(322) := ''HR_KI_TOPICS_TL'';');
2810 add_body_line(' seeddata_table(323) := ''IRC_ALL_RECRUITING_SITES'';');
2811 add_body_line(' seeddata_table(324) := ''DT_DATE_PROMPTS_TL'';');
2812 add_body_line(' seeddata_table(325) := ''HR_ALL_ORGANIZATION_UNITS_TL'';');
2813 add_body_line(' seeddata_table(326) := ''PQH_COPY_ENTITY_TXNS'';');
2814 add_body_line(' seeddata_table(327) := ''PER_JOB_GROUPS'';');
2815 add_body_line(' seeddata_table(328) := ''PER_NUMBER_GENERATION_CONTROLS'';');
2816 add_body_line(' seeddata_table(329) := ''PER_PROPOSAL_TEMPLATES'';');
2817 add_body_line(' seeddata_table(330) := ''PER_SECURITY_PROFILES'';');
2818 add_body_line(' seeddata_table(331) := ''HR_KI_TOPICS'';');
2819 add_body_line(' seeddata_table(332) := ''PAY_TAXABILITY_RULES_DATES'';');
2820 add_body_line(' seeddata_table(333) := ''GHR_PAYROLL_OFFICE_NUMBER'';');
2821 add_body_line(' seeddata_table(334) := ''GHR_PROCESS_LOG'';');
2822 add_body_line(' seeddata_table(335) := ''HR_ALL_ORGANIZATION_UNITS'';');
2823 add_body_line(' seeddata_table(336) := ''PQP_VEH_ALLOC_INFO_TYPES'';');
2824 add_body_line(' seeddata_table(337) := ''FF_FDI_USAGES_F'';');
2825 add_body_line(' seeddata_table(338) := ''PAY_US_ZIP_CODES'';');
2826 add_body_line(' seeddata_table(339) := ''PAY_US_CITY_GEOCODES'';');
2827 add_body_line(' seeddata_table(340) := ''AME_EXCEPTIONS_LOG'';');
2828 add_body_line(' seeddata_table(341) := ''HR_APPLICATION_OWNERSHIPS'';');
2829 add_body_line(' seeddata_table(342) := ''PAY_TRIGGER_PARAMETERS'';');
2830 add_body_line(' seeddata_table(343) := ''HR_NAVIGATION_PATHS'';');
2831 add_body_line(' seeddata_table(344) := ''HR_NAVIGATION_NODE_USAGES'';');
2832 add_body_line(' seeddata_table(345) := ''FF_ROUTE_CONTEXT_USAGES'';');
2833 add_body_line(' seeddata_table(346) := ''PAY_US_CITY_SCHOOL_DSTS'';');
2834 add_body_line(' seeddata_table(347) := ''PAY_US_COUNTIES'';');
2835 add_body_line(' seeddata_table(348) := ''FF_COMPILED_INFO_F'';');
2836 add_body_line(' seeddata_table(349) := ''FF_ROUTE_PARAMETERS'';');
2837 add_body_line(' seeddata_table(350) := ''HR_S_APPLICATION_OWNERSHIPS'';');
2838 add_body_line(' seeddata_table(351) := ''PAY_MAGNETIC_RECORDS'';');
2839 add_body_line(' seeddata_table(352) := ''FF_FTYPE_CONTEXT_USAGES'';');
2840 add_body_line(' seeddata_table(353) := ''PAY_US_MODIFIED_GEOCODES'';');
2841 add_body_line(' seeddata_table(354) := ''PAY_BALANCE_DIMENSIONS'';');
2842 add_body_line(' seeddata_table(355) := ''PAY_DYNDBI_CHANGES'';');
2843 add_body_line(' seeddata_table(356) := ''PAY_MAGNETIC_BLOCKS'';');
2844 add_body_line(' seeddata_table(357) := ''HR_NAV_UNIT_GLOBAL_USAGES'';');
2845 add_body_line(' seeddata_table(358) := ''PAY_LEGISLATION_RULES'';');
2846 add_body_line(' seeddata_table(359) := ''PAY_REPORT_FORMAT_MAPPINGS_TL'';');
2847 add_body_line(' seeddata_table(360) := ''PAY_LEGISLATIVE_FIELD_INFO'';');
2848 add_body_line(' seeddata_table(361) := ''HR_WORKFLOWS'';');
2849 add_body_line(' seeddata_table(362) := ''PAY_AC_VENDOR_MAPPINGS'';');
2853 add_body_line(' seeddata_table(365) := ''HR_NAVIGATION_NODES'';');
2850 add_body_line(' seeddata_table(363) := ''HR_TIPS'';');
2851 add_body_line(' --seeddata_table(364) := ''PAY_BALANCE_VALIDATION'';');
2852 add_body_line(' seeddata_table(364) := ''PAY_BALANCE_SETS'';');
2854 add_body_line(' seeddata_table(366) := ''PAY_ZA_IRP5_BAL_CODES'';');
2855 add_body_line(' seeddata_table(367) := ''PAY_CA_LEGISLATION_INFO'';');
2856 add_body_line(' seeddata_table(368) := ''PAY_US_TAX_BALANCES'';');
2857 add_body_line(' seeddata_table(369) := ''PAY_TRIGGER_INITIALISATIONS'';');
2858 add_body_line(' seeddata_table(370) := ''PAY_TRIGGER_DECLARATIONS'';');
2859 add_body_line(' seeddata_table(371) := ''PAY_PATCH_STATUS'';');
2860 add_body_line(' seeddata_table(372) := ''HR_NAVIGATION_UNITS'';');
2861 add_body_line(' seeddata_table(373) := ''AME_FIELD_HELP'';');
2862 add_body_line(' seeddata_table(374) := ''PAY_US_TAX_REPORT_BALANCES'';');
2863 add_body_line(' seeddata_table(375) := ''HR_OWNER_DEFINITIONS'';');
2864 add_body_line(' seeddata_table(376) := ''HR_NAVIGATION_CONTEXT_RULES'';');
2865 add_body_line(' seeddata_table(377) := ''PAY_TIME_DEFINITIONS'';');
2866 add_body_line(' seeddata_table(378) := ''PER_FORM_FUNCTIONS'';');
2867 add_body_line(' seeddata_table(379) := ''PAY_TIME_SPANS'';');
2868 add_body_line(' seeddata_table(380) := ''PAY_US_STATES'';');
2869 add_body_line(' seeddata_table(381) := ''PAY_ACTION_CLASSIFICATIONS'';');
2870 add_body_line(' seeddata_table(382) := ''HR_LEGISLATION_SUBGROUPS'';');
2871 add_body_line(' seeddata_table(383) := ''PAY_ELE_TMPLT_CLASS_USAGES'';');
2872 add_body_line(' seeddata_table(384) := ''PAY_RETRO_COMPONENTS'';');
2873 add_body_line(' seeddata_table(385) := ''PAY_RETRO_DEFN_COMPONENTS'';');
2874 add_body_line(' seeddata_table(386) := ''FF_CONTEXTS'';');
2875 add_body_line(' seeddata_table(387) := ''HR_STU_HISTORY'';');
2876 add_body_line(' seeddata_table(388) := ''PAY_LEGISLATION_CONTEXTS'';');
2877 add_body_line(' seeddata_table(389) := ''PAY_RETRO_DEFINITIONS'';');
2878 add_body_line(' seeddata_table(390) := ''PER_GB_XDO_TEMPLATES'';');
2879 add_body_line(' seeddata_table(391) := ''PAY_PURGE_ACTION_TYPES'';');
2880 add_body_line(' seeddata_table(392) := ''AME_APPROVAL_GROUP_MEMBERS'';');
2881 add_body_line(' seeddata_table(393) := ''PAY_US_TAX_TYPES'';');
2882 add_body_line(' seeddata_table(394) := ''HR_PUMP_SPREAD_LOADERS'';');
2883 add_body_line(' seeddata_table(395) := ''PAY_US_TAX_REPORT_DIMENSIONS'';');
2884 add_body_line(' seeddata_table(396) := ''PAY_ACTION_PARAMETER_VALUES'';');
2885 add_body_line(' seeddata_table(397) := ''PAY_UPGRADE_STATUS'';');
2886 add_body_line(' seeddata_table(398) := ''PAY_US_COUNTY_SCHOOL_DSTS'';');
2887 add_body_line(' seeddata_table(499) := ''PAY_ACTION_PARAMETERS_OLD'';');
2888 add_body_line(' seeddata_table(400) := ''PAY_PROCESS_GROUP_ACTIONS'';');
2889 add_body_line(' seeddata_table(401) := ''IRC_ALL_RECRUITING_SITES_TL'';');
2890 add_body_line(' seeddata_table(402) := ''PAY_TIME_DEF_USAGES'';');
2891 add_body_line(' seeddata_table(403) := ''HR_S_HISTORY'';');
2892 add_body_line(' seeddata_table(404) := ''PAY_PROCESS_GROUPS'';');
2893 add_body_line(' seeddata_table(405) := ''PAY_REPORT_MAGNETIC_PROCEDURES'';');
2894 add_body_line(' ');
2895 add_body_line(' --BEN tables');
2896 add_body_line(' seeddata_table(406) := ''BEN_DM_COLUMN_MAPPINGS'';');
2897 add_body_line(' seeddata_table(407) := ''BEN_EXT_DATA_ELMT_IN_RCD'';');
2898 add_body_line(' seeddata_table(408) := ''BEN_EXT_DATA_ELMT'';');
2899 add_body_line(' seeddata_table(409) := ''BEN_EXT_FLD'';');
2900 add_body_line(' seeddata_table(410) := ''BEN_DM_TABLES'';');
2901 add_body_line(' seeddata_table(411) := ''BEN_DM_HIERARCHIES'';');
2902 add_body_line(' seeddata_table(412) := ''BEN_COPY_ENTITY_RESULTS'';');
2903 add_body_line(' seeddata_table(413) := ''BEN_EXT_RCD_IN_FILE'';');
2904 add_body_line(' seeddata_table(414) := ''BEN_EXT_RCD'';');
2905 add_body_line(' seeddata_table(415) := ''BEN_EXT_CRIT_VAL'';');
2906 add_body_line(' seeddata_table(416) := ''BEN_EXT_INCL_CHG'';');
2907 add_body_line(' seeddata_table(417) := ''BEN_EXT_CRIT_TYP'';');
2908 add_body_line(' seeddata_table(418) := ''BEN_EXT_DATA_ELMT_DECD'';');
2909 add_body_line(' seeddata_table(419) := ''BEN_EXT_WHERE_CLAUSE'';');
2910 add_body_line(' seeddata_table(420) := ''BEN_ACTN_TYP_TL'';');
2911 add_body_line(' seeddata_table(421) := ''BEN_LER_F_TL'';');
2912 add_body_line(' seeddata_table(422) := ''BEN_EXT_DFN'';');
2913 add_body_line(' seeddata_table(423) := ''BEN_EXT_CRIT_PRFL'';');
2914 add_body_line(' seeddata_table(424) := ''BEN_EXT_FILE'';');
2915 add_body_line(' seeddata_table(425) := ''BEN_DM_TABLE_ORDER'';');
2916 add_body_line(' seeddata_table(426) := ''BEN_STARTUP_ACTN_TYP_TL'';');
2917 add_body_line(' seeddata_table(427) := ''BEN_STARTUP_LERS_TL'';');
2918 add_body_line(' seeddata_table(428) := ''BEN_ELIGY_CRITERIA'';');
2919 add_body_line(' seeddata_table(429) := ''BEN_CM_TRGR'';');
2920 add_body_line(' seeddata_table(430) := ''BEN_STARTUP_LERS'';');
2921 add_body_line(' seeddata_table(431) := ''BEN_STARTUP_ACTN_TYP'';');
2922 add_body_line(' seeddata_table(432) := ''BEN_REGN_F_TL'';');
2923 add_body_line(' seeddata_table(433) := ''BEN_STARTUP_REGN_TL'';');
2924 add_body_line(' seeddata_table(434) := ''BEN_LER_F'';');
2925 add_body_line(' seeddata_table(435) := ''BEN_CM_TYP_F_TL'';');
2926 add_body_line(' seeddata_table(436) := ''BEN_STARTUP_CM_TYP_TL'';');
2927 add_body_line(' seeddata_table(437) := ''BEN_STARTUP_REGN'';');
2928 add_body_line(' seeddata_table(438) := ''BEN_STARTUP_CM_TYP'';');
2929 add_body_line(' seeddata_table(439) := ''BEN_ACTN_TYP'';');
2930 add_body_line(' seeddata_table(440) := ''BEN_CM_TYP_F'';');
2931 add_body_line(' seeddata_table(441) := ''BEN_PGM_INFO_TYPES'';');
2932 add_body_line(' seeddata_table(442) := ''BEN_TRANSACTION'';');
2933 add_body_line(' ');
2937 add_body_line('--');
2934 add_body_line(' --OTA tables');
2935 add_body_line(' seeddata_table(443) := ''OTA_UPGRADE_LOG'';');
2936 add_body_line(' seeddata_table(444) := ''OTA_TITLE_CONVERSIONS'';');
2938 add_body_line('-- There are tables which should not be allowed to be disabled, if they');
2939 add_body_line('-- the policy is applied to them..');
2940 add_body_line('-- The list contains the name of all these tables.');
2941 add_body_line('--');
2942 add_body_line(' allowed_table(1) := ''PER_NUMBER_GENERATION_CONTROLS'';');
2943 add_body_line(' allowed_table(2) := ''HR_LOCATIONS_ALL'';');
2944 add_body_line(' allowed_table(3) := ''PER_GRADES'';');
2945 add_body_line(' allowed_table(4) := ''PER_JOBS'';');
2946 add_body_line(' allowed_table(5) := ''PER_ABSENCE_ATTENDANCE_TYPES'';');
2947 add_body_line(' allowed_table(6) := ''PER_ASSIGNMENT_STATUS_TYPES'';');
2948 add_body_line(' allowed_table(7) := ''PER_PAY_BASES'';');
2949 add_body_line(' allowed_table(8) := ''HR_ALL_ORGANIZATION_UNITS'';');
2950 add_body_line(' allowed_table(9) := ''PER_ALL_POSITIONS'';');
2951 add_body_line(' ');
2952 add_body_line('--');
2953 add_body_line('-- Based on the install mode selectively enable policy on tables ');
2954 add_body_line('--');
2955 add_body_line(' if install_mode =''SCHEMA'' then');
2956 add_body_line('--');
2957 add_body_line('-- Schema mode is not a valid option for Model 2 ');
2958 add_body_line('--');
2959 add_body_line(' if fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'')=''P'' then');
2960 add_body_line(' return;');
2961 add_body_line(' end if;');
2962 add_body_line(' ');
2963 add_body_line('--');
2964 add_body_line('-- Fetch the tables to be applied. This list depends on the');
2965 add_body_line('-- batch start and batch end parameters.');
2966 add_body_line('-- ');
2967 add_body_line('--');
2968 add_body_line(' open acting_tables_schema (');
2969 add_body_line(' c_start => population_start, ');
2970 add_body_line(' c_end => population_end,');
2971 add_body_line(' c_hr_owner => l_hr_name, ');
2972 add_body_line(' c_ota_owner => l_ota_name,');
2973 add_body_line(' c_ben_owner => l_ben_name);');
2974 add_body_line('');
2975 add_body_line(' fetch acting_tables_schema ');
2976 add_body_line(' bulk collect into all_hr_table_owners, all_hr_tables;');
2977 add_body_line('');
2978 add_body_line(' close acting_tables_schema;');
2979 add_body_line('');
2980 add_body_line(' for i in 1 .. all_hr_tables.count loop');
2981 add_body_line(' l_table_name := all_hr_tables(i);');
2982 add_body_line(' l_schema_name := all_hr_table_owners(i);');
2983 add_body_line(' ');
2984 add_body_line(' fnd_file.put_line(fnd_file.log, ''Current Table '' || l_schema_name || ''.'' || l_table_name);');
2985 add_body_line(' l_np_tab := false;');
2986 add_body_line(' l_upd_tab := false;');
2987 add_body_line(' l_upd_policy := false;');
2988 add_body_line('');
2989 add_body_line(' for j in 1..no_policy_table.count loop');
2990 add_body_line(' if l_table_name = no_policy_table(j) then');
2991 add_body_line(' --');
2992 add_body_line(' -- Do not apply policy to this table');
2993 add_body_line(' -- ');
2994 add_body_line(' l_np_tab := true;');
2995 add_body_line(' exit;');
2996 add_body_line(' end if;');
2997 add_body_line(' end loop;');
2998 add_body_line('');
2999 add_body_line(' if l_np_tab then');
3000 add_body_line(' fnd_file.put_line(fnd_file.log, ''Current Table is in the no policy table list.'');');
3001 add_body_line(' sa_policy_admin.remove_table_policy');
3002 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
3003 add_body_line(' ,schema_name => l_schema_name');
3004 add_body_line(' ,table_name => l_table_name');
3005 add_body_line(' ,drop_column => FALSE);');
3006 add_body_line(' else');
3007 add_body_line(' l_table_options := ''READ_CONTROL, LABEL_DEFAULT, HIDE'';');
3008 add_body_line(' for m in 1..seeddata_table.count loop');
3009 add_body_line(' if l_table_name = seeddata_table(m) then');
3010 add_body_line(' fnd_file.put_line(fnd_file.log, ''Current Table is in the seed data table list.'');');
3011 add_body_line(' l_table_options := ''READ_CONTROL, UPDATE_CONTROL, DELETE_CONTROL, LABEL_DEFAULT, HIDE'';');
3012 add_body_line(' l_upd_policy := true;');
3013 add_body_line(' exit;');
3014 add_body_line(' end if;');
3015 add_body_line(' end loop;');
3016 add_body_line('');
3017 add_body_line(' l_predicate := null;');
3018 add_body_line(' for k in 1..predicate_table.count loop ');
3019 add_body_line(' if l_table_name = predicate_table(k) then');
3020 add_body_line(' fnd_file.put_line(fnd_file.log, ''Current Table is in the predicate table list.'');');
3021 add_body_line(' l_predicate := ''AND HR_ENTERPRISE = '' ||');
3022 add_body_line(' ''char_to_label(''''HR_ENTERPRISE_POLICY'''', SA_SESSION.ROW_LABEL(''''HR_ENTERPRISE_POLICY''''))'';');
3023 add_body_line(' l_upd_tab := true;');
3024 add_body_line(' l_upd_policy := true;');
3025 add_body_line(' exit;');
3026 add_body_line(' end if;');
3027 add_body_line(' end loop;');
3028 add_body_line('');
3029 add_body_line(' if l_upd_policy then');
3030 add_body_line(' sa_policy_admin.remove_table_policy');
3034 add_body_line(' ,drop_column => FALSE);');
3031 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
3032 add_body_line(' ,schema_name => l_schema_name');
3033 add_body_line(' ,table_name => l_table_name');
3035 add_body_line('');
3036 add_body_line(' if l_upd_tab then');
3037 add_body_line(' execute immediate ''UPDATE '' || l_schema_name || ''.'' || l_table_name ||');
3038 add_body_line(' '' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',''''C::GLOBAL'''')'' || ');
3039 add_body_line(' '' WHERE hr_enterprise is null'';');
3040 add_body_line(' end if;');
3041 add_body_line('');
3042 add_body_line(' sa_policy_admin.apply_table_policy');
3043 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
3044 add_body_line(' ,schema_name => l_schema_name');
3045 add_body_line(' ,table_name => l_table_name');
3046 add_body_line(' ,table_options => l_table_options');
3047 add_body_line(' ,predicate => l_predicate);');
3048 add_body_line(' end if;');
3049 add_body_line('');
3050 add_body_line(' -- Fix for Bug 7645461');
3051 add_body_line(' if l_table_name = ''HR_NAME_FORMATS'' then');
3052 add_body_line(' execute immediate ''Drop Index ''|| l_schema_name||''.HR_NAME_FORMATS_U1'';');
3053 add_body_line(' execute immediate ''Create Unique Index ''|| l_schema_name||');
3054 add_body_line(' ''.HR_NAME_FORMATS_U1 On ''|| l_schema_name||''.''||l_table_name||');
3055 add_body_line(' ''(FORMAT_NAME,LEGISLATION_CODE,USER_FORMAT_CHOICE,HR_ENTERPRISE)'';');
3056 add_body_line(' end if;');
3057 add_body_line(' end if;');
3058 add_body_line(' end loop;');
3059 add_body_line(' elsif install_mode =''USERTABLE'' THEN ');
3060 add_body_line(' --');
3061 add_body_line(' -- Installation Module starts for USERTABLE ');
3062 add_body_line(' --');
3063 add_body_line(' ');
3064 add_body_line(' open acting_tables_usertable(c_start => population_start, c_end => population_end);');
3065 add_body_line(' fetch acting_tables_usertable bulk collect into rowtab,efdtab;');
3066 add_body_line(' close acting_tables_usertable;');
3067 add_body_line(' ');
3068 add_body_line(' for tabcnt in rowtab.first .. rowtab.last loop');
3069 add_body_line(' l_table_name := rowTab(tabCnt);');
3070 add_body_line(' l_effective_start_date := efdTab(tabCnt);');
3071 add_body_line(' --');
3072 add_body_line(' -- Get the option provide in user table');
3073 add_body_line(' --');
3074 add_body_line(' BEGIN');
3075 add_body_line(' l_table_enabled_value := hruserdt.get_table_value ');
3076 add_body_line(' (p_bus_group_id => 0');
3077 add_body_line(' ,p_table_name => ''HR_OLS_TABLE_LIST''');
3078 add_body_line(' ,p_col_name => ''Enabled''');
3079 add_body_line(' ,p_row_value => l_table_name');
3080 add_body_line(' ,p_effective_date => l_effective_start_date);');
3081 add_body_line(' l_schema_name := hruserdt.get_table_value ');
3082 add_body_line(' (p_bus_group_id => 0');
3083 add_body_line(' ,p_table_name => ''HR_OLS_TABLE_LIST''');
3084 add_body_line(' ,p_col_name => ''Schema''');
3085 add_body_line(' ,p_row_value => l_table_name');
3086 add_body_line(' ,p_effective_date => l_effective_start_date);');
3087 add_body_line(' EXCEPTION');
3088 add_body_line(' WHEN no_data_found THEN');
3089 add_body_line(' l_table_enabled_value := NULL;');
3090 add_body_line(' ');
3091 add_body_line(' END;');
3092 add_body_line(' ');
3093 add_body_line(' --');
3094 add_body_line(' -- Get the status of applied policy to this table');
3095 add_body_line(' --');
3096 add_body_line(' BEGIN');
3097 add_body_line(' SELECT status');
3098 add_body_line(' INTO l_status');
3099 add_body_line(' FROM all_sa_table_policies ');
3100 add_body_line(' WHERE table_name = l_table_name');
3101 add_body_line(' AND schema_name = l_schema_name');
3102 add_body_line(' AND policy_name =''HR_ENTERPRISE_POLICY'';');
3103 add_body_line(' ');
3104 add_body_line(' l_enabled := TRUE;');
3105 add_body_line(' EXCEPTION');
3106 add_body_line(' WHEN NO_DATA_FOUND THEN');
3107 add_body_line(' l_enabled := FALSE;');
3108 add_body_line(' END;');
3109 add_body_line('');
3110 add_body_line(' --');
3111 add_body_line(' -- Apply the policy to the tables');
3112 add_body_line(' --');
3113 add_body_line(' BEGIN');
3114 add_body_line(' IF NOT l_enabled AND (UPPER(l_table_enabled_value) = ''Y'') THEN');
3115 add_body_line('');
3116 add_body_line(' sa_policy_admin.apply_table_policy');
3117 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
3118 add_body_line(' ,schema_name => l_schema_name');
3119 add_body_line(' ,table_name => l_table_name);');
3120 add_body_line(' --');
3121 add_body_line(' -- In PEO mode change the table indexes');
3122 add_body_line(' --');
3123 add_body_line(' IF fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'')=''P'' THEN');
3127 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.hr_locations_uk2''');
3124 add_body_line(' IF l_table_name = ''HR_LOCATIONS_ALL'' THEN ');
3125 add_body_line(' execute_statement( ''ALTER TABLE '' || l_schema_name || ');
3126 add_body_line(' ''.hr_locations_all DROP CONSTRAINT hr_locations_uk2 CASCADE''');
3128 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
3129 add_body_line(' ''.hr_locations_all ADD CONSTRAINT hr_locations_uk2 '' ||');
3130 add_body_line(' '' UNIQUE (location_code, business_group_id,hr_enterprise)''');
3131 add_body_line(' ,l_table_name);');
3132 add_body_line(' ');
3133 add_body_line(' ELSIF l_table_name =''PER_GRADES'' THEN');
3134 add_body_line(' ');
3135 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
3136 add_body_line(' ''.per_grades DROP CONSTRAINT PER_GRADES_UK2 CASCADE''');
3137 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.PER_GRADES_UK2''');
3138 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
3139 add_body_line(' ''.per_grades add CONSTRAINT per_grades_uk2 UNIQUE (name,business_group_id,hr_enterprise)''');
3140 add_body_line(' ,l_table_name); ');
3141 add_body_line(' ELSIF l_table_name =''PER_JOBS'' THEN');
3142 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name ||');
3143 add_body_line(' ''.per_jobs DROP CONSTRAINT per_jobs_uk2 CASCADE''');
3144 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_jobs_uk2''');
3145 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
3146 add_body_line(' ''.per_jobs ADD CONSTRAINT per_jobs_uk2 UNIQUE (name,business_group_id,hr_enterprise)''');
3147 add_body_line(' ,l_table_name); ');
3148 add_body_line(' ELSIF l_table_name =''PER_ABSENCE_ATTENDANCE_TYPES'' THEN');
3149 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
3150 add_body_line(' ''.per_absence_attendance_types DROP CONSTRAINT per_absence_attendance_typ_uk2 CASCADE''');
3151 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_absence_attendance_typ_uk2''');
3152 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)''');
3153 add_body_line(' ,l_table_name);');
3154 add_body_line(' ELSIF l_table_name =''PER_ASSIGNMENT_STATUS_TYPES'' THEN');
3155 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
3156 add_body_line(' ''.per_assignment_status_types DROP CONSTRAINT per_assignment_status_type_uk2 CASCADE''');
3157 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_assignment_status_type_uk2''');
3158 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
3159 add_body_line(' ''.per_assignment_status_types ADD CONSTRAINT per_assignment_status_type_uk2 UNIQUE(hr_enterprise, business_group_id, legislation_code, user_status)''');
3160 add_body_line(' ,l_table_name);');
3161 add_body_line(' ELSIF l_table_name =''PER_PAY_BASES'' THEN');
3162 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
3163 add_body_line(' ''.per_pay_bases DROP CONSTRAINT per_pay_bases_uk2 CASCADE''');
3164 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_pay_bases_uk2''');
3165 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
3166 add_body_line(' ''.per_pay_bases ADD CONSTRAINT per_pay_bases_uk2 UNIQUE(hr_enterprise, business_group_id, name)''');
3167 add_body_line(' ,l_table_name);');
3168 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
3169 add_body_line(' ''.per_pay_bases DROP CONSTRAINT per_pay_bases_uk3 CASCADE''');
3170 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_pay_bases_uk3''');
3171 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
3172 add_body_line(' ''.per_pay_bases ADD CONSTRAINT per_pay_bases_uk3 UNIQUE(hr_enterprise, business_group_id, input_value_id, rate_id)''');
3173 add_body_line(' ,l_table_name);');
3174 add_body_line(' ELSIF l_table_name =''HR_ALL_ORGANIZATION_UNITS'' THEN');
3175 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
3176 add_body_line(' ''.hr_all_organization_units DROP CONSTRAINT hr_organization_units_uk2 CASCADE''');
3177 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.hr_organization_units_uk2''');
3178 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
3179 add_body_line(' ''.hr_all_organization_units ADD CONSTRAINT hr_organization_units_uk2 unique (name,business_group_id,hr_enterprise)''');
3180 add_body_line(' ,l_table_name);');
3181 add_body_line(' ELSIF l_table_name =''PER_NUMBER_GENERATION_CONTROLS'' THEN');
3182 add_body_line(' execute immediate ''UPDATE '' || l_schema_name || ''.'' || l_table_name ||');
3183 add_body_line(' '' SET hr_enterprise = char_to_label(''''HR_ENTERPRISE_POLICY'''',''''C::ENT'''')'';');
3184 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
3185 add_body_line(' ''.per_number_generation_controls DROP CONSTRAINT per_number_generation_cont_pk CASCADE''');
3186 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_number_generation_cont_pk''');
3187 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
3191 add_body_line(' ELSIF l_table_name =''PER_ALL_POSITIONS'' THEN');
3188 add_body_line(' ''.per_number_generation_controls ADD CONSTRAINT per_number_generation_cont_pk PRIMARY KEY (type, business_group_id,hr_enterprise)''');
3189 add_body_line(' ,l_table_name);');
3190 add_body_line(' ');
3192 add_body_line(' execute_statement(''ALTER TABLE '' || l_schema_name || ');
3193 add_body_line(' ''.per_all_positions DROP CONSTRAINT per_positions_uk2 CASCADE''');
3194 add_body_line(' ,''DROP INDEX '' || l_schema_name || ''.per_positions_uk2''');
3195 add_body_line(' ,''ALTER TABLE '' || l_schema_name || ');
3196 add_body_line(' ''.per_all_positions ADD CONSTRAINT per_positions_uk2 UNIQUE(hr_enterprise, business_group_id, name)''');
3197 add_body_line(' ,l_table_name);');
3198 add_body_line(' ');
3199 add_body_line(' END IF;');add_body_line(' IF l_table_name =''PER_NUMBER_GENERATION_CONTROLS'' THEN');
3200 add_body_line(' l_predicate := ''AND HR_ENTERPRISE = '' ||');
3201 add_body_line(' ''char_to_label(''''HR_ENTERPRISE_POLICY'''', SA_SESSION.ROW_LABEL(''''HR_ENTERPRISE_POLICY''''))''; ');
3202 add_body_line(' -- ');
3203 add_body_line(' -- Update null to C::ENT');
3204 add_body_line(' --');
3205 add_body_line('');
3206 add_body_line(' --');
3207 add_body_line(' -- Drop the policy and recreate it with new predicate');
3208 add_body_line(' --');
3209 add_body_line(' sa_policy_admin.remove_table_policy');
3210 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
3211 add_body_line(' ,schema_name => l_schema_name');
3212 add_body_line(' ,table_name => l_table_name');
3213 add_body_line(' ,drop_column => FALSE);');
3214 add_body_line('');
3215 add_body_line(' sa_policy_admin.apply_table_policy');
3216 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
3217 add_body_line(' ,schema_name => l_schema_name');
3218 add_body_line(' ,table_name => l_table_name');
3219 add_body_line(' ,predicate => l_predicate);');
3220 add_body_line(' END IF;');
3221 add_body_line(' END IF; ');
3222 add_body_line(' ELSIF l_enabled THEN');
3223 add_body_line(' IF (UPPER(l_table_enabled_value) = ''N'') AND l_status= ''ENABLED'' THEN ');
3224 add_body_line(' IF fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'')=''P'' THEN');
3225 add_body_line(' --');
3226 add_body_line(' -- Check if disable is allowed for this table');
3227 add_body_line(' --');
3228 add_body_line(' FOR t_index IN 1 .. allowed_table.COUNT LOOP');
3229 add_body_line(' IF allowed_table(t_index) = l_table_name THEN');
3230 add_body_line(' l_np_tab := TRUE; ');
3231 add_body_line(' EXIT;');
3232 add_body_line(' END IF;');
3233 add_body_line(' END LOOP;');
3234 add_body_line('');
3235 add_body_line(' IF NOT l_np_tab THEN');
3236 add_body_line(' sa_policy_admin.disable_table_policy');
3237 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
3238 add_body_line(' ,schema_name => l_schema_name');
3239 add_body_line(' ,table_name => l_table_name');
3240 add_body_line(' );');
3241 add_body_line(' END IF; ');
3242 add_body_line(' ELSIF fnd_profile.value(''HR_ENABLE_MULTI_TENANCY'')=''B'' THEN ');
3243 add_body_line(' sa_policy_admin.disable_table_policy');
3244 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
3245 add_body_line(' ,schema_name => l_schema_name');
3246 add_body_line(' ,table_name => l_table_name');
3247 add_body_line(' );');
3248 add_body_line(' END IF; ');
3249 add_body_line(' ELSIF (UPPER(l_table_enabled_value) = ''Y'') AND l_status= ''DISABLED'' THEN');
3250 add_body_line(' --');
3251 add_body_line(' -- Enable policy');
3252 add_body_line(' --');
3253 add_body_line(' sa_policy_admin.enable_TABLE_POLICY');
3254 add_body_line(' (policy_name => ''HR_ENTERPRISE_POLICY''');
3255 add_body_line(' ,schema_name => l_schema_name');
3256 add_body_line(' ,table_name => l_table_name);');
3257 add_body_line(' END IF;');
3258 add_body_line(' END IF;');
3259 add_body_line(' END;');
3260 add_body_line(' END LOOP;');
3261 add_body_line(' end if;');
3262 add_body_line(' exception');
3263 add_body_line(' when others then');
3264 add_body_line(' fnd_file.put_line(fnd_file.log,''Exception caught on: '' || fnd_global.conc_request_id);');
3265 add_body_line(' fnd_file.put_line(fnd_file.log,''l_table_name: '' || l_table_name); ');
3266 add_body_line(' fnd_file.put_line(fnd_file.log,''sqerrmessage: '' || sqlerrm);');
3267 add_body_line(' end child_process;');
3268 add_body_line('');
3269 add_body_line('');
3270 add_body_line('END '||l_package_name ||';');
3271 add_header_line('END ' ||l_package_name||';');
3272
3273 l_csr_sql := dbms_sql.open_cursor;
3274 dbms_sql.parse( l_csr_sql, l_header,0,l_header_line-1,FALSE, dbms_sql.v7 );
3275 l_rows := dbms_sql.EXECUTE( l_csr_sql );
3276 dbms_sql.close_cursor( l_csr_sql );
3277
3278 l_csr_sql := dbms_sql.open_cursor;
3279 dbms_sql.parse( l_csr_sql, l_body,0,l_body_line-1,FALSE, dbms_sql.v7 );
3280 l_rows := dbms_sql.EXECUTE( l_csr_sql );
3284 WHEN OTHERS THEN
3281 dbms_sql.close_cursor( l_csr_sql );
3282
3283 EXCEPTION
3285 fnd_file.put_line(fnd_file.output,'Error occured in installation of HR Multi tenancy '|| sqlerrm);
3286 END install_hr_multi_tenant;
3287 END hr_multi_tenant_installer;