[Home] [Help]
2: /* $Header: pyucslis.pkb 120.36.12020000.4 2013/02/27 12:47:46 swrajapa ship $ */
3: --
4: --
5: g_package varchar2(20) := 'pay_pyucslis_pkg.';
6: g_debug boolean := hr_utility.debug_enabled;
7:
8: -- Start changes for bug 13504049
9: g_use_temp_table varchar2(10) := 'N';
10: g_generation_scope varchar2(20) := 'ALL_PROFILES';
29: l_upd_date date := trunc(sysdate);
30: --
31: begin
32: --
33: hr_utility.set_location('Entering: '||l_proc, 10);
34: -- Insert a record for each contact of the person. Only process those
35: -- profiles which are in the generation scope but include ALL contacts
36: -- for this person_id.
37:
40: -- security as this is assessed dynamically.
41:
42: --Start changes for the code for 13504049
43: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
44: hr_utility.set_location(l_proc||' using stage table',20);
45: insert into per_person_list_stage(security_profile_id, person_id, request_id,
46: program_application_id, program_id,
47: program_update_date)
48: select /*+ USE_NL(PSP) */
77: and ppl1.granted_user_id is null
78: and ppl1.security_profile_id = ppl.security_profile_id);
79:
80: else
81: hr_utility.set_location(l_proc||' using actual table',30);
82: insert into per_person_list(security_profile_id, person_id, request_id,
83: program_application_id, program_id,
84: program_update_date)
85: select /*+ USE_NL(PSP) */
114: and ppl1.granted_user_id is null
115: and ppl1.security_profile_id = ppl.security_profile_id);
116: end if;
117: --
118: hr_utility.set_location('Leaving: '||l_proc, 99);
119: --
120: end add_contacts_for_person;
121:
122: --
197: --
198:
199: begin
200: --
201: hr_utility.set_location('Entering: '||l_proc, 1);
202: hr_utility.set_location('Request ID '||p_request_id, 15);
203: --
204: -- Bulk collect the unrelated contacts into PL/SQL tables.
205: open csr_get_unrelated_contacts;
198:
199: begin
200: --
201: hr_utility.set_location('Entering: '||l_proc, 1);
202: hr_utility.set_location('Request ID '||p_request_id, 15);
203: --
204: -- Bulk collect the unrelated contacts into PL/SQL tables.
205: open csr_get_unrelated_contacts;
206: fetch csr_get_unrelated_contacts bulk collect into l_per_tbl, l_per_bg_tbl;
205: open csr_get_unrelated_contacts;
206: fetch csr_get_unrelated_contacts bulk collect into l_per_tbl, l_per_bg_tbl;
207: close csr_get_unrelated_contacts;
208: --
209: hr_utility.set_location(l_proc, 10);
210: --
211: if l_per_tbl.count > 0 then
212: -- When there are unrelated contacts, bulk collect the security
213: -- profiles that restrict by contacts.
210: --
211: if l_per_tbl.count > 0 then
212: -- When there are unrelated contacts, bulk collect the security
213: -- profiles that restrict by contacts.
214: hr_utility.set_location(l_proc, 20);
215: --
216: if p_generation_scope = 'ALL_BUS_GRP' then
217: --
218: open csr_get_sec_profs_bg;
226: close csr_get_sec_profs;
227: --
228: end if;
229: --
230: hr_utility.set_location(l_proc, 30);
231: --
232: if l_sp_tbl.count > 0 then
233: --
234: hr_utility.set_location(l_proc, 40);
230: hr_utility.set_location(l_proc, 30);
231: --
232: if l_sp_tbl.count > 0 then
233: --
234: hr_utility.set_location(l_proc, 40);
235: --
236: for i in l_sp_tbl.first..l_sp_tbl.last loop
237: -- Insert the unrelated contacts for each security profile.
238: -- Enforce the business group restriction when restricting
258: when no_Data_found then
259:
260: -- Start changes for bug 13504049
261: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
262: hr_utility.set_location(l_proc||' using stage table',50);
263: insert into per_person_list_stage(security_profile_id, person_id,
264: request_id, program_application_id,
265: program_id, program_update_date)
266: values (l_sp_tbl(i), l_per_tbl(j), l_req_id,
265: program_id, program_update_date)
266: values (l_sp_tbl(i), l_per_tbl(j), l_req_id,
267: l_appl_id, l_prog_id, l_upd_date);
268: else
269: hr_utility.set_location(l_proc||' using actual table',60);
270: insert into per_person_list(security_profile_id, person_id,
271: request_id, program_application_id,
272: program_id, program_update_date)
273: values (l_sp_tbl(i), l_per_tbl(j), l_req_id,
285: end if;
286: --
287: end if;
288: --
289: hr_utility.set_location('Leaving: '||l_proc, 999);
290: --
291: end add_unrelated_contacts;
292: --
293:
304: PROCEDURE delete_old_person_list_changes (l_effective_date DATE)
305: IS
306: BEGIN
307: --
308: hr_utility.set_location('hr_listgen.delete_old_person_list_changes',10);
309: --
310: -- Stubb out as part of ex-person security enhancements.
311: --
312: hr_utility.set_location('hr_listgen.delete_old_person_list_changes',20);
308: hr_utility.set_location('hr_listgen.delete_old_person_list_changes',10);
309: --
310: -- Stubb out as part of ex-person security enhancements.
311: --
312: hr_utility.set_location('hr_listgen.delete_old_person_list_changes',20);
313: --
314: END delete_old_person_list_changes;
315: --
316: --
343: BEGIN
344: --
345: IF (l_include_exclude_payroll_flag = 'I') THEN
346: --
347: hr_utility.set_location('hr_listgen.build_payroll_list', 10);
348: --
349: INSERT INTO pay_payroll_list
350: (payroll_id,
351: security_profile_id,
369: AND pay.effective_end_date;*/
370: --
371: ELSE -- exclude payrolls
372: --
373: hr_utility.set_location('hr_listgen.build_payroll_list', 20);
374: --
375: INSERT INTO pay_payroll_list
376: (payroll_id,
377: security_profile_id,
444: l_proc varchar2(100) := 'pay_pyucslis_pkg.build_organization_list';
445: --
446: begin
447: --
448: hr_utility.set_location(l_proc, 10);
449: --
450: -- Insert all organizations in the hierarchy (excluding the top organization).
451: --
452: INSERT INTO per_organization_list
472: WHERE v.organization_structure_id = l_organization_structure_id
473: AND l_effective_date BETWEEN v.date_from
474: AND NVL(v.date_to, TO_DATE('31-12-4712','dd-mm-yyyy')));
475: --
476: hr_utility.set_location(l_proc, 20);
477: --
478: -- Insert all organizations in the organization list that have their
479: -- include / exclude flag set to 'I'.
480: --
499: FROM per_organization_list pol
500: WHERE pol.security_profile_id = l_security_profile_id
501: AND pol.organization_id = pso.organization_id);
502: --
503: hr_utility.set_location(l_proc, 30);
504: --
505: -- Include the Top Organization if the security profile permits.
506: --
507: IF (l_include_top_org_flag = 'Y') THEN
507: IF (l_include_top_org_flag = 'Y') THEN
508: --
509: IF l_organization_id IS NOT NULL THEN
510: --
511: hr_utility.set_location(l_proc, 40);
512: --
513: INSERT INTO per_organization_list
514: (security_profile_id,
515: organization_id,
535: END IF;
536: --
537: END IF;
538: --
539: hr_utility.set_location(l_proc, 50);
540: --
541: IF p_business_group_mode='GLOBAL' AND
542: NVL(l_exclude_business_groups_flag, 'N') = 'N' THEN
543: --
673: BEGIN
674: --
675: IF (l_view_all_organizations_flag = 'N') THEN
676: --
677: hr_utility.set_location('hr_listgen.build_position_list', 10);
678: --
679: INSERT INTO per_position_list
680: (security_profile_id,
681: position_id,
711: -- Include the top position.
712: --
713: IF ( l_include_top_position_flag = 'Y') THEN
714: --
715: hr_utility.set_location('hr_listgen.build_position_list', 20);
716: --
717: INSERT INTO per_position_list
718: (security_profile_id,
719: position_id,
739: END IF; -- Include the top position.
740: --
741: ELSE -- l_view_all_organizations_flag
742: --
743: hr_utility.set_location('hr_listgen.build_position_list', 30);
744: --
745: INSERT INTO per_position_list
746: (security_profile_id,
747: position_id,
770: -- Include top position.
771: --
772: IF ( l_include_top_position_flag = 'Y') THEN
773: --
774: hr_utility.set_location('hr_listgen.build_position_list', 40);
775: --
776: INSERT INTO per_position_list
777: (security_profile_id,
778: position_id,
822: l_upd_date date := trunc(sysdate);
823: --
824: begin
825: --
826: hr_utility.set_location('Entering: ' || l_proc, 10);
827:
828: IF p_view_all_contacts_flag = 'Y' then ---- Added for bug (6376000/4774264)
829: -- If the Security profile set as -- View All contacts = Yes
830: -- In this case we need to Insert the contact records for,
842:
843: -- Query #1
844: -- Start changes for bug 13504049
845: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
846: hr_utility.set_location(l_proc||' using stage table', 14);
847: insert into per_person_list_stage(security_profile_id, request_id, program_id
848: ,program_application_id, program_update_date
849: ,person_id)
850: select distinct p_security_profile_id, l_req_id, l_prog_id,
866: where ppl1.person_id = pcr.contact_person_id
867: and ppl1.granted_user_id is null
868: and ppl1.security_profile_id = p_security_profile_id ); -- ppl.security_profile_id) for bug (6376000/4774264)
869: else
870: hr_utility.set_location(l_proc||' using actual table', 18);
871: insert into per_person_list(security_profile_id, request_id, program_id
872: ,program_application_id, program_update_date
873: ,person_id)
874: select distinct p_security_profile_id, l_req_id, l_prog_id,
892: and ppl1.security_profile_id = p_security_profile_id ); -- ppl.security_profile_id) for bug (6376000/4774264)
893: end if;
894: -- End changes for bug 13504049
895: --
896: hr_utility.set_location(l_proc, 20);
897: Else
898: -- Insert into person list, all people with a contact relationship to
899: -- someone already in the person list as long as their system person type
900: -- is 'other'
910: -- earlier part of LISTGEN.
911: -- Query #2
912:
913: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
914: hr_utility.set_location(l_proc||' using stage table', 24);
915: insert into per_person_list_stage(security_profile_id, request_id, program_id
916: ,program_application_id, program_update_date
917: ,person_id)
918: select distinct ppl.security_profile_id, l_req_id, l_prog_id,
934: where ppl1.person_id = pcr.contact_person_id
935: and ppl1.granted_user_id is null
936: and ppl1.security_profile_id = ppl.security_profile_id);
937: else
938: hr_utility.set_location(l_proc||' using actual table', 28);
939: insert into per_person_list(security_profile_id, request_id, program_id
940: ,program_application_id, program_update_date
941: ,person_id)
942: select distinct ppl.security_profile_id, l_req_id, l_prog_id,
959: and ppl1.granted_user_id is null
960: and ppl1.security_profile_id = ppl.security_profile_id);
961: end if;
962: --
963: hr_utility.set_location(l_proc, 30);
964: End if;
965: -- Inserts all unrelated contacts(excluding the candidates, those
966: -- registered from iRecruitment) who do not have any other assignments.
967: -- If there are additional assignments these will be excluded from here
969: -- stages of listgen.
970: -- Query #3
971:
972: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
973: hr_utility.set_location(l_proc||' using stage table', 34);
974: insert into per_person_list_stage(security_profile_id, request_id,
975: program_application_id, program_id,
976: program_update_date, person_id)
977: select distinct psp.security_profile_id, l_req_id, l_appl_id,
1002: where ppl.person_id = papf.person_id
1003: and ppl.granted_user_id is null
1004: and ppl.security_profile_id = psp.security_profile_id);
1005: else
1006: hr_utility.set_location(l_proc||' using actual table', 38);
1007: insert into per_person_list(security_profile_id, request_id,
1008: program_application_id, program_id,
1009: program_update_date, person_id)
1010: select distinct psp.security_profile_id, l_req_id, l_appl_id,
1036: and ppl.granted_user_id is null
1037: and ppl.security_profile_id = psp.security_profile_id);
1038: end if;
1039: --
1040: hr_utility.set_location('Leaving: ' || l_proc, 99);
1041: --
1042: end build_contact_list;
1043: --
1044: /* =======================================================================
1065: IS
1066: l_proc varchar2(72):= g_package||'add_person_list_changes';
1067: BEGIN
1068: --
1069: hr_utility.set_location('Entering: '||l_proc,10);
1070: --
1071: -- Start change for bug 13504049
1072: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1073: hr_utility.set_location(l_proc||' using stage table', 14);
1069: hr_utility.set_location('Entering: '||l_proc,10);
1070: --
1071: -- Start change for bug 13504049
1072: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1073: hr_utility.set_location(l_proc||' using stage table', 14);
1074: INSERT INTO per_person_list_stage
1075: (security_profile_id,
1076: person_id,
1077: request_id,
1105: WHERE ppl.person_id = plc.person_id
1106: AND ppl.granted_user_Id IS NULL
1107: AND ppl.security_profile_id = plc.security_profile_id);
1108: else
1109: hr_utility.set_location(l_proc||' using actual table', 18);
1110: INSERT INTO per_person_list
1111: (security_profile_id,
1112: person_id,
1113: request_id,
1143: AND ppl.security_profile_id = plc.security_profile_id);
1144: end if;
1145: -- End change for bug 13504049
1146: --
1147: hr_utility.set_location('hr_listgen.add_person_list_changes',20);
1148: --
1149: END add_person_list_changes;
1150:
1151:
1184: l_num number(10);
1185: j number(10):=1;
1186: i number(10):=50;
1187: BEGIN
1188: hr_utility.set_location('Entering execute_statement',10);
1189: l_cursor_id := dbms_sql.open_cursor;
1190: fnd_dsql.set_cursor(l_cursor_id);
1191:
1192: l_dsql_text := fnd_dsql.get_text(FALSE);
1194:
1195: /* -- use to print the final sql query
1196: while j < l_num +50
1197: loop
1198: hr_utility.set_location(substr(l_dsql_text,j,i),10);
1199: -- hr_utility.set_location('-------------------------',20);
1200: -- dbms_output.put_line(substr(l_dsql_text,i));
1201: j := j+50;
1202: end loop;
1195: /* -- use to print the final sql query
1196: while j < l_num +50
1197: loop
1198: hr_utility.set_location(substr(l_dsql_text,j,i),10);
1199: -- hr_utility.set_location('-------------------------',20);
1200: -- dbms_output.put_line(substr(l_dsql_text,i));
1201: j := j+50;
1202: end loop;
1203: */
1201: j := j+50;
1202: end loop;
1203: */
1204: dbms_sql.parse(l_cursor_id, l_dsql_text, dbms_sql.native);
1205: hr_utility.set_location('after parse',10);
1206: fnd_dsql.do_binds;
1207: hr_utility.set_location('after bind',10);
1208: l_num_of_rows := dbms_sql.execute(l_cursor_id);
1209: hr_utility.set_location('after execuate ',10);
1203: */
1204: dbms_sql.parse(l_cursor_id, l_dsql_text, dbms_sql.native);
1205: hr_utility.set_location('after parse',10);
1206: fnd_dsql.do_binds;
1207: hr_utility.set_location('after bind',10);
1208: l_num_of_rows := dbms_sql.execute(l_cursor_id);
1209: hr_utility.set_location('after execuate ',10);
1210: dbms_sql.close_cursor(l_cursor_id);
1211: hr_utility.set_location('Leaveing execute_statement',10);
1205: hr_utility.set_location('after parse',10);
1206: fnd_dsql.do_binds;
1207: hr_utility.set_location('after bind',10);
1208: l_num_of_rows := dbms_sql.execute(l_cursor_id);
1209: hr_utility.set_location('after execuate ',10);
1210: dbms_sql.close_cursor(l_cursor_id);
1211: hr_utility.set_location('Leaveing execute_statement',10);
1212: END execute_statement;
1213:
1207: hr_utility.set_location('after bind',10);
1208: l_num_of_rows := dbms_sql.execute(l_cursor_id);
1209: hr_utility.set_location('after execuate ',10);
1210: dbms_sql.close_cursor(l_cursor_id);
1211: hr_utility.set_location('Leaveing execute_statement',10);
1212: END execute_statement;
1213:
1214: /*-- for the bug 5214715 --*/
1215: PROCEDURE add_comm_str ( p_sec_rec IN PER_SECURITY_PROFILES%ROWTYPE ) as
1215: PROCEDURE add_comm_str ( p_sec_rec IN PER_SECURITY_PROFILES%ROWTYPE ) as
1216: l_restriction_flags varchar2(2000);
1217: l_exclude_flags varchar2(2000);
1218: BEGIN
1219: hr_utility.set_location('Entering add_comm_str',10);
1220: l_restriction_flags :='';
1221: if (p_sec_rec.view_all_cwk_flag = 'N') then
1222: if length(l_restriction_flags)>0 then
1223: l_restriction_flags:=l_restriction_flags||' OR ';
1283: fnd_dsql.add_text(' and ( ');
1284: fnd_dsql.add_text(l_exclude_flags);
1285: fnd_dsql.add_text(' ) ');
1286: end if;
1287: hr_utility.set_location('Leaveing add_comm_str',10);
1288: END add_comm_str;
1289:
1290: /*-- for the bug 5214715 --*/
1291: -- XXCUSTOM - procedure to add static insert statement text
1303: ,p_sec_rec IN PER_SECURITY_PROFILES%ROWTYPE)
1304: AS
1305:
1306: BEGIN
1307: hr_utility.set_location('Entering init_statement',10);
1308: fnd_dsql.init;
1309:
1310: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1311: hr_utility.set_location('init_statement using stage table',14);
1307: hr_utility.set_location('Entering init_statement',10);
1308: fnd_dsql.init;
1309:
1310: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1311: hr_utility.set_location('init_statement using stage table',14);
1312: fnd_dsql.add_text(
1313: 'INSERT into per_person_list_stage
1314: (security_profile_id,
1315: person_id,
1317: program_application_id,
1318: program_id,
1319: program_update_date ) ');
1320: else
1321: hr_utility.set_location('init_statement using actual table',18);
1322: fnd_dsql.add_text(
1323: 'INSERT into per_person_list
1324: (security_profile_id,
1325: person_id,
1453: if p_sec_rec.view_all_organizations_flag='Y' then
1454: null;
1455: end if;
1456: /*-------------- End where clause -------------------*/
1457: hr_utility.set_location('Leaveing init_statement',10);
1458: end init_statement;
1459:
1460: BEGIN
1461: hr_utility.set_location('Entering Create_person_list',10);
1457: hr_utility.set_location('Leaveing init_statement',10);
1458: end init_statement;
1459:
1460: BEGIN
1461: hr_utility.set_location('Entering Create_person_list',10);
1462: init_statement
1463: (
1464: --p_person_id => p_person_id
1465: p_request_id => p_request_id
1553: fnd_dsql.add_text(sec_rec.restriction_text);
1554: end if;
1555:
1556: if g_debug then
1557: hr_utility.trace('select '||to_char(length(l_select_text)));
1558: hr_utility.trace('where '||to_char(length(l_where_clause)));
1559: l_execution_stmt2:=l_execution_stmt;
1560: while length(l_execution_stmt2)>0 loop
1561: hr_utility.trace(substr(l_execution_stmt2,1,70));
1554: end if;
1555:
1556: if g_debug then
1557: hr_utility.trace('select '||to_char(length(l_select_text)));
1558: hr_utility.trace('where '||to_char(length(l_where_clause)));
1559: l_execution_stmt2:=l_execution_stmt;
1560: while length(l_execution_stmt2)>0 loop
1561: hr_utility.trace(substr(l_execution_stmt2,1,70));
1562: l_execution_stmt2:=substr(l_execution_stmt2,71);
1557: hr_utility.trace('select '||to_char(length(l_select_text)));
1558: hr_utility.trace('where '||to_char(length(l_where_clause)));
1559: l_execution_stmt2:=l_execution_stmt;
1560: while length(l_execution_stmt2)>0 loop
1561: hr_utility.trace(substr(l_execution_stmt2,1,70));
1562: l_execution_stmt2:=substr(l_execution_stmt2,71);
1563: end loop;
1564: end if;
1565:
1563: end loop;
1564: end if;
1565:
1566: Execute_statement;
1567: hr_utility.set_location('Leaveing Craete_person_list',10);
1568: END create_person_list;
1569:
1570:
1571: --
1587: l_effective_date date;
1588: --
1589: begin
1590: --
1591: hr_utility.set_location('Entering : '||l_proc,10);
1592: --
1593: -- Clear the records for this person.
1594:
1595: -- Bug6809753 - start
1852: end if;
1853:
1854: -- Bug6809753 -end
1855:
1856: hr_utility.set_location('Leaving : '||l_proc,20);
1857: --
1858: end clear_per_list_table;
1859: --
1860: --
1870: l_proc varchar2(72) := g_package||'clear_unrelated_contacts';
1871: --
1872: begin
1873: --
1874: hr_utility.set_location('Entering : '||l_proc,10);
1875: --
1876: if p_generation_scope <> 'ALL_BUS_GRP' then
1877:
1878: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1875: --
1876: if p_generation_scope <> 'ALL_BUS_GRP' then
1877:
1878: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1879: hr_utility.set_location(l_proc||' using stage table',14);
1880: delete from per_person_list_stage ppl
1881: where ppl.security_profile_id in
1882: (select pspf.security_profile_id
1883: from per_security_profiles pspf
1899: (select null
1900: from per_contact_relationships pcr
1901: where pcr.contact_person_id = ppl.person_id);
1902: else
1903: hr_utility.set_location(l_proc||' using actual table',18);
1904: delete from per_person_list ppl
1905: where ppl.security_profile_id in
1906: (select pspf.security_profile_id
1907: from per_security_profiles pspf
1947: from per_contact_relationships pcr
1948: where pcr.contact_person_id = ppl.person_id);
1949: end if;
1950: --
1951: hr_utility.set_location('Leaving : '||l_proc,20);
1952: --
1953: end clear_unrelated_contacts;
1954: --
1955: -- ----------------------------------------------------------------------------
1966: l_proc varchar2(72) := g_package||'clear_sp_list_table';
1967: --
1968: begin
1969: --
1970: hr_utility.set_location('Entering : '||l_proc, 10);
1971: -- Clearing Organization static list
1972: delete
1973: from per_organization_list
1974: where security_profile_id = p_security_profile_id
1973: from per_organization_list
1974: where security_profile_id = p_security_profile_id
1975: and user_id is null;
1976: --
1977: hr_utility.set_location(l_proc, 20);
1978: -- Clearing Position static list
1979: delete
1980: from per_position_list
1981: where security_profile_id = p_security_profile_id
1980: from per_position_list
1981: where security_profile_id = p_security_profile_id
1982: and user_id is null;
1983: --
1984: hr_utility.set_location(l_proc, 30);
1985: -- Clearing the Person static list
1986: if p_clear_people_flag then
1987: --
1988: -- Start changes for bug 13504049
1986: if p_clear_people_flag then
1987: --
1988: -- Start changes for bug 13504049
1989: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1990: hr_utility.set_location(l_proc||' using stage table',34);
1991: delete
1992: from per_person_list_stage
1993: where security_profile_id = p_security_profile_id
1994: and granted_user_id is null;
1992: from per_person_list_stage
1993: where security_profile_id = p_security_profile_id
1994: and granted_user_id is null;
1995: else
1996: hr_utility.set_location(l_proc||' using actual table',38);
1997: delete
1998: from per_person_list
1999: where security_profile_id = p_security_profile_id
2000: and granted_user_id is null;
2002: -- End changes for bug 13504049
2003: --
2004: end if;
2005: --
2006: hr_utility.set_location(l_proc, 40);
2007: -- Clearing Payroll static list
2008: delete
2009: from pay_payroll_list
2010: where security_profile_id = p_security_profile_id;
2008: delete
2009: from pay_payroll_list
2010: where security_profile_id = p_security_profile_id;
2011: --
2012: hr_utility.set_location('Leaving : '||l_proc, 99);
2013: --
2014: end clear_sp_list_table;
2015: --
2016: /* =======================================================================
2278: PROCEDURE profile_add_to_cache IS
2279: l_proc varchar2(100):= 'profile_add_to_person_list.profile_add_to_cache';
2280: Begin
2281: if g_debug then
2282: hr_utility.set_location('Entering '||l_proc, 10);
2283: End if;
2284: If l_security_profile_table_temp.count > 0 then
2285: if g_debug then
2286: hr_utility.set_location(l_proc, 20);
2282: hr_utility.set_location('Entering '||l_proc, 10);
2283: End if;
2284: If l_security_profile_table_temp.count > 0 then
2285: if g_debug then
2286: hr_utility.set_location(l_proc, 20);
2287: End if;
2288: for I in l_security_profile_table_temp.first .. l_security_profile_table_temp.last
2289: loop
2290: hr_utility.set_location(l_security_profile_table_temp(i), 25);
2286: hr_utility.set_location(l_proc, 20);
2287: End if;
2288: for I in l_security_profile_table_temp.first .. l_security_profile_table_temp.last
2289: loop
2290: hr_utility.set_location(l_security_profile_table_temp(i), 25);
2291: l_security_profile_table(l_collection_index + i):= l_security_profile_table_temp(i);
2292: end loop;
2293: l_collection_index := l_security_profile_table.last;
2294: End if;
2292: end loop;
2293: l_collection_index := l_security_profile_table.last;
2294: End if;
2295: if g_debug then
2296: hr_utility.set_location('Leaveing '||l_proc, 30);
2297: End if;
2298: End profile_add_to_cache;
2299:
2300: begin
2299:
2300: begin
2301:
2302: if g_debug then
2303: hr_utility.set_location('Entering:'|| l_proc, 10);
2304: end if;
2305: open asg_details;
2306: fetch asg_details into
2307: l_person_id,
2312: l_assignment_type,
2313: l_current_employee_flag,
2314: l_current_npw_flag;
2315: if g_debug then
2316: hr_utility.set_location(l_proc, 30);
2317: end if;
2318: if(asg_details%found) then
2319: close asg_details;
2320: if p_generation_scope = 'ALL_BUS_GRP' then
2317: end if;
2318: if(asg_details%found) then
2319: close asg_details;
2320: if p_generation_scope = 'ALL_BUS_GRP' then
2321: hr_utility.set_location(l_proc, 31);
2322: l_bggr_str := ' sec.business_group_id = :l_business_group_id and ';
2323: end if;
2324: if p_generation_scope = 'ALL_GLOBAL' then
2325: hr_utility.set_location(l_proc, 32);
2321: hr_utility.set_location(l_proc, 31);
2322: l_bggr_str := ' sec.business_group_id = :l_business_group_id and ';
2323: end if;
2324: if p_generation_scope = 'ALL_GLOBAL' then
2325: hr_utility.set_location(l_proc, 32);
2326: l_bggr_str := ' sec.business_group_id is null and ';
2327: end if;
2328: if p_generation_scope = 'ALL_PROFILES' then
2329: hr_utility.set_location(l_proc, 33);
2325: hr_utility.set_location(l_proc, 32);
2326: l_bggr_str := ' sec.business_group_id is null and ';
2327: end if;
2328: if p_generation_scope = 'ALL_PROFILES' then
2329: hr_utility.set_location(l_proc, 33);
2330: l_bggr_str := ' (sec.business_group_id = :l_business_group_id
2331: or sec.business_group_id is null) and ';
2332: end if;
2333: if l_assignment_type = 'E' then
2330: l_bggr_str := ' (sec.business_group_id = :l_business_group_id
2331: or sec.business_group_id is null) and ';
2332: end if;
2333: if l_assignment_type = 'E' then
2334: hr_utility.set_location(l_proc, 34);
2335: l_asgt_str := ' (sec.view_all_employees_flag = :l_Restrict or
2336: (sec.view_all_employees_flag = :l_all and
2337: (sec.view_all_contacts_flag = :l_Restrict or
2338: (sec.view_all_contacts_flag = :l_all and
2338: (sec.view_all_contacts_flag = :l_all and
2339: sec.view_all_candidates_flag = :l_None)))) and ';
2340: end if;
2341: if l_assignment_type = 'C' then
2342: hr_utility.set_location(l_proc, 35);
2343: l_asgt_str := ' (sec.view_all_cwk_flag = :l_Restrict or
2344: (sec.view_all_cwk_flag = :l_all and
2345: (sec.view_all_contacts_flag = :l_Restrict or
2346: (sec.view_all_contacts_flag = :l_all and
2346: (sec.view_all_contacts_flag = :l_all and
2347: sec.view_all_candidates_flag = :l_None)))) and ';
2348: end if;
2349: if l_assignment_type = 'A' then
2350: hr_utility.set_location(l_proc, 36);
2351: l_asgt_str := ' (sec.view_all_applicants_flag = :l_Restrict or
2352: (sec.view_all_applicants_flag = :l_all and
2353: (sec.view_all_contacts_flag = :l_Restrict or
2354: (sec.view_all_contacts_flag = :l_all and
2365: if l_position_id is null then
2366: if l_payroll_id is null then
2367: begin
2368: if g_debug then
2369: hr_utility.set_location(l_proc, 40);
2370: end if;
2371: l_from_str := ' from per_security_profiles sec,
2372: per_organization_list org ';
2373: -- Modified for bug 11737636
2383:
2384: if g_debug then
2385: l_exec_str_print:= l_exec_str;
2386: while length(l_exec_str_print)>0 loop
2387: hr_utility.trace(substr(l_exec_str_print,1,70));
2388: l_exec_str_print:=substr(l_exec_str_print,71);
2389: end loop;
2390: End if;
2391: if p_generation_scope = 'ALL_GLOBAL' then
2439: profile_add_to_cache;
2440: exception
2441: when no_data_found then
2442: if g_debug then
2443: hr_utility.set_location(l_proc, 50);
2444: end if;
2445: null;
2446: when others then
2447: if g_debug then
2444: end if;
2445: null;
2446: when others then
2447: if g_debug then
2448: hr_utility.set_location(l_proc, 60);
2449: hr_utility.set_location(l_proc||sqlerrm, 60);
2450: end if;
2451: raise;
2452: end;
2445: null;
2446: when others then
2447: if g_debug then
2448: hr_utility.set_location(l_proc, 60);
2449: hr_utility.set_location(l_proc||sqlerrm, 60);
2450: end if;
2451: raise;
2452: end;
2453: begin
2451: raise;
2452: end;
2453: begin
2454: if g_debug then
2455: hr_utility.set_location(l_proc, 70);
2456: end if;
2457: l_from_str := ' from per_security_profiles sec ';
2458: -- Modified for 11737636
2459: l_cond_str := ' sec.view_all_organizations_flag = :l_all
2465: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2466: if g_debug then
2467: l_exec_str_print:= l_exec_str;
2468: while length(l_exec_str_print)>0 loop
2469: hr_utility.trace(substr(l_exec_str_print,1,70));
2470: l_exec_str_print:=substr(l_exec_str_print,71);
2471: end loop;
2472: End if;
2473: if p_generation_scope = 'ALL_GLOBAL' then
2519: profile_add_to_cache;
2520: exception
2521: when no_data_found then
2522: if g_debug then
2523: hr_utility.set_location(l_proc, 80);
2524: end if;
2525: null;
2526: when others then
2527: if g_debug then
2524: end if;
2525: null;
2526: when others then
2527: if g_debug then
2528: hr_utility.set_location(l_proc||sqlerrm, 90);
2529: hr_utility.set_location(l_proc, 90);
2530: end if;
2531: raise;
2532: end;
2525: null;
2526: when others then
2527: if g_debug then
2528: hr_utility.set_location(l_proc||sqlerrm, 90);
2529: hr_utility.set_location(l_proc, 90);
2530: end if;
2531: raise;
2532: end;
2533: begin
2531: raise;
2532: end;
2533: begin
2534: if g_debug then
2535: hr_utility.set_location(l_proc, 100);
2536: end if;
2537: l_from_str := ' from per_security_profiles sec ';
2538: l_cond_str := ' sec.view_all_organizations_flag = :l_all
2539: and sec.view_all_positions_flag = :l_all
2544: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2545: if g_debug then
2546: l_exec_str_print:= l_exec_str;
2547: while length(l_exec_str_print)>0 loop
2548: hr_utility.trace(substr(l_exec_str_print,1,70));
2549: l_exec_str_print:=substr(l_exec_str_print,71);
2550: end loop;
2551: End if;
2552: if p_generation_scope = 'ALL_GLOBAL' then
2596: profile_add_to_cache;
2597: exception
2598: when no_data_found then
2599: if g_debug then
2600: hr_utility.set_location(l_proc, 110);
2601: end if;
2602: null;
2603: when others then
2604: if g_debug then
2601: end if;
2602: null;
2603: when others then
2604: if g_debug then
2605: hr_utility.set_location(l_proc||sqlerrm, 120);
2606: hr_utility.set_location(l_proc, 120);
2607: end if;
2608: raise;
2609: end;
2602: null;
2603: when others then
2604: if g_debug then
2605: hr_utility.set_location(l_proc||sqlerrm, 120);
2606: hr_utility.set_location(l_proc, 120);
2607: end if;
2608: raise;
2609: end;
2610: else -- position is null but payroll is not
2609: end;
2610: else -- position is null but payroll is not
2611: begin
2612: if g_debug then
2613: hr_utility.set_location(l_proc, 130);
2614: end if;
2615: l_from_str := ' from per_security_profiles sec,
2616: pay_payroll_list pay,
2617: per_organization_list org ';
2627: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2628: if g_debug then
2629: l_exec_str_print:= l_exec_str;
2630: while length(l_exec_str_print)>0 loop
2631: hr_utility.trace(substr(l_exec_str_print,1,70));
2632: l_exec_str_print:=substr(l_exec_str_print,71);
2633: end loop;
2634: End if;
2635: if p_generation_scope = 'ALL_GLOBAL' then
2681: profile_add_to_cache;
2682: exception
2683: when no_data_found then
2684: if g_debug then
2685: hr_utility.set_location(l_proc, 140);
2686: end if;
2687: null;
2688: when others then
2689: if g_debug then
2686: end if;
2687: null;
2688: when others then
2689: if g_debug then
2690: hr_utility.set_location(l_proc||sqlerrm, 150);
2691: hr_utility.set_location(l_proc, 150);
2692: end if;
2693: raise;
2694: end;
2687: null;
2688: when others then
2689: if g_debug then
2690: hr_utility.set_location(l_proc||sqlerrm, 150);
2691: hr_utility.set_location(l_proc, 150);
2692: end if;
2693: raise;
2694: end;
2695: begin
2693: raise;
2694: end;
2695: begin
2696: if g_debug then
2697: hr_utility.set_location(l_proc, 160);
2698: end if;
2699: l_from_str := ' from per_security_profiles sec,
2700: per_organization_list org ';
2701: l_cond_str := ' org.security_profile_id = sec.security_profile_id
2707: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2708: if g_debug then
2709: l_exec_str_print:= l_exec_str;
2710: while length(l_exec_str_print)>0 loop
2711: hr_utility.trace(substr(l_exec_str_print,1,70));
2712: l_exec_str_print:=substr(l_exec_str_print,71);
2713: end loop;
2714: End if;
2715: if p_generation_scope = 'ALL_GLOBAL' then
2759: profile_add_to_cache;
2760: exception
2761: when no_data_found then
2762: if g_debug then
2763: hr_utility.set_location(l_proc, 170);
2764: end if;
2765: null;
2766: when others then
2767: if g_debug then
2764: end if;
2765: null;
2766: when others then
2767: if g_debug then
2768: hr_utility.set_location(l_proc||sqlerrm, 180);
2769: hr_utility.set_location(l_proc, 180);
2770: end if;
2771: raise;
2772: end;
2765: null;
2766: when others then
2767: if g_debug then
2768: hr_utility.set_location(l_proc||sqlerrm, 180);
2769: hr_utility.set_location(l_proc, 180);
2770: end if;
2771: raise;
2772: end;
2773: begin
2771: raise;
2772: end;
2773: begin
2774: if g_debug then
2775: hr_utility.set_location(l_proc, 190);
2776: end if;
2777: l_from_str := ' from per_security_profiles sec,
2778: pay_payroll_list pay ';
2779: l_cond_str := ' pay.security_profile_id = sec.security_profile_id
2784: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2785: if g_debug then
2786: l_exec_str_print:= l_exec_str;
2787: while length(l_exec_str_print)>0 loop
2788: hr_utility.trace(substr(l_exec_str_print,1,70));
2789: l_exec_str_print:=substr(l_exec_str_print,71);
2790: end loop;
2791: End if;
2792: if p_generation_scope = 'ALL_GLOBAL' then
2832: profile_add_to_cache;
2833: exception
2834: when no_data_found then
2835: if g_debug then
2836: hr_utility.set_location(l_proc, 200);
2837: end if;
2838: null;
2839: when others then
2840: if g_debug then
2837: end if;
2838: null;
2839: when others then
2840: if g_debug then
2841: hr_utility.set_location(l_proc||sqlerrm, 210);
2842: hr_utility.set_location(l_proc, 210);
2843: end if;
2844: raise;
2845: end;
2838: null;
2839: when others then
2840: if g_debug then
2841: hr_utility.set_location(l_proc||sqlerrm, 210);
2842: hr_utility.set_location(l_proc, 210);
2843: end if;
2844: raise;
2845: end;
2846: begin
2844: raise;
2845: end;
2846: begin
2847: if g_debug then
2848: hr_utility.set_location(l_proc, 220);
2849: end if;
2850: l_from_str := ' from per_security_profiles sec ';
2851: l_cond_str := ' sec.view_all_organizations_flag = :l_all
2852: and sec.view_all_payrolls_flag = :l_all
2857: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2858: if g_debug then
2859: l_exec_str_print:= l_exec_str;
2860: while length(l_exec_str_print)>0 loop
2861: hr_utility.trace(substr(l_exec_str_print,1,70));
2862: l_exec_str_print:=substr(l_exec_str_print,71);
2863: end loop;
2864: End if;
2865: if p_generation_scope = 'ALL_GLOBAL' then
2917:
2918: exception
2919: when no_data_found then
2920: if g_debug then
2921: hr_utility.set_location(l_proc, 230);
2922: end if;
2923: null;
2924: when others then
2925: if g_debug then
2922: end if;
2923: null;
2924: when others then
2925: if g_debug then
2926: hr_utility.set_location(l_proc||sqlerrm, 240);
2927: hr_utility.set_location(l_proc, 240);
2928: end if;
2929: raise;
2930: end;
2923: null;
2924: when others then
2925: if g_debug then
2926: hr_utility.set_location(l_proc||sqlerrm, 240);
2927: hr_utility.set_location(l_proc, 240);
2928: end if;
2929: raise;
2930: end;
2931: end if;
2930: end;
2931: end if;
2932: begin
2933: if g_debug then
2934: hr_utility.set_location(l_proc, 250);
2935: end if;
2936: l_from_str := ' from per_security_profiles sec ';
2937: l_cond_str := ' sec.view_all_organizations_flag = :l_all
2938: and sec.view_all_positions_flag = :l_all
2943: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
2944: if g_debug then
2945: l_exec_str_print:= l_exec_str;
2946: while length(l_exec_str_print)>0 loop
2947: hr_utility.trace(substr(l_exec_str_print,1,70));
2948: l_exec_str_print:=substr(l_exec_str_print,71);
2949: end loop;
2950: End if;
2951: if p_generation_scope = 'ALL_GLOBAL' then
3001: profile_add_to_cache;
3002: exception
3003: when no_data_found then
3004: if g_debug then
3005: hr_utility.set_location(l_proc, 260);
3006: end if;
3007: null;
3008: when others then
3009: if g_debug then
3006: end if;
3007: null;
3008: when others then
3009: if g_debug then
3010: hr_utility.set_location(l_proc||sqlerrm, 270);
3011: hr_utility.set_location(l_proc, 270);
3012: end if;
3013: raise;
3014: end;
3007: null;
3008: when others then
3009: if g_debug then
3010: hr_utility.set_location(l_proc||sqlerrm, 270);
3011: hr_utility.set_location(l_proc, 270);
3012: end if;
3013: raise;
3014: end;
3015: else -- position is not null
3015: else -- position is not null
3016: if l_payroll_id is null then
3017: begin
3018: if g_debug then
3019: hr_utility.set_location(l_proc, 280);
3020: end if;
3021: l_from_str := ' from per_security_profiles sec,
3022: per_position_list posl,
3023: per_organization_list org ';
3037: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3038: if g_debug then
3039: l_exec_str_print:= l_exec_str;
3040: while length(l_exec_str_print)>0 loop
3041: hr_utility.trace(substr(l_exec_str_print,1,70));
3042: l_exec_str_print:=substr(l_exec_str_print,71);
3043: end loop;
3044: End if;
3045: if p_generation_scope = 'ALL_GLOBAL' then
3103: profile_add_to_cache;
3104: exception
3105: when no_data_found then
3106: if g_debug then
3107: hr_utility.set_location(l_proc, 290);
3108: end if;
3109: null;
3110: when others then
3111: if g_debug then
3108: end if;
3109: null;
3110: when others then
3111: if g_debug then
3112: hr_utility.set_location(l_proc||sqlerrm, 300);
3113: hr_utility.set_location(l_proc, 300);
3114: end if;
3115: raise;
3116: end;
3109: null;
3110: when others then
3111: if g_debug then
3112: hr_utility.set_location(l_proc||sqlerrm, 300);
3113: hr_utility.set_location(l_proc, 300);
3114: end if;
3115: raise;
3116: end;
3117: begin
3115: raise;
3116: end;
3117: begin
3118: if g_debug then
3119: hr_utility.set_location(l_proc, 310);
3120: end if;
3121: l_from_str := ' from per_security_profiles sec,
3122: per_organization_list org ';
3123: -- Changed for bug 11737636
3132: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3133: if g_debug then
3134: l_exec_str_print:= l_exec_str;
3135: while length(l_exec_str_print)>0 loop
3136: hr_utility.trace(substr(l_exec_str_print,1,70));
3137: l_exec_str_print:=substr(l_exec_str_print,71);
3138: end loop;
3139: End if;
3140: if p_generation_scope = 'ALL_GLOBAL' then
3192: profile_add_to_cache;
3193: exception
3194: when no_data_found then
3195: if g_debug then
3196: hr_utility.set_location(l_proc, 320);
3197: end if;
3198: null;
3199: when others then
3200: if g_debug then
3197: end if;
3198: null;
3199: when others then
3200: if g_debug then
3201: hr_utility.set_location(l_proc||sqlerrm, 330);
3202: hr_utility.set_location(l_proc, 330);
3203: end if;
3204: raise;
3205: end;
3198: null;
3199: when others then
3200: if g_debug then
3201: hr_utility.set_location(l_proc||sqlerrm, 330);
3202: hr_utility.set_location(l_proc, 330);
3203: end if;
3204: raise;
3205: end;
3206: begin
3204: raise;
3205: end;
3206: begin
3207: if g_debug then
3208: hr_utility.set_location(l_proc, 340);
3209: end if;
3210: l_from_str := ' from per_security_profiles sec ';
3211: -- Changed for bug 11737636
3212: l_cond_str := ' sec.view_all_organizations_flag = :l_all
3216: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3217: if g_debug then
3218: l_exec_str_print:= l_exec_str;
3219: while length(l_exec_str_print)>0 loop
3220: hr_utility.trace(substr(l_exec_str_print,1,70));
3221: l_exec_str_print:=substr(l_exec_str_print,71);
3222: end loop;
3223: End if;
3224: if p_generation_scope = 'ALL_GLOBAL' then
3270: profile_add_to_cache;
3271: exception
3272: when no_data_found then
3273: if g_debug then
3274: hr_utility.set_location(l_proc, 350);
3275: end if;
3276: null;
3277: when others then
3278: if g_debug then
3275: end if;
3276: null;
3277: when others then
3278: if g_debug then
3279: hr_utility.set_location(l_proc||sqlerrm, 360);
3280: hr_utility.set_location(l_proc, 360);
3281: end if;
3282: raise;
3283: end;
3276: null;
3277: when others then
3278: if g_debug then
3279: hr_utility.set_location(l_proc||sqlerrm, 360);
3280: hr_utility.set_location(l_proc, 360);
3281: end if;
3282: raise;
3283: end;
3284: begin
3282: raise;
3283: end;
3284: begin
3285: if g_debug then
3286: hr_utility.set_location(l_proc, 370);
3287: end if;
3288: l_from_str := ' from per_security_profiles sec,
3289: per_position_list posl ';
3290: --Modified for bug 11737636
3301: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3302: if g_debug then
3303: l_exec_str_print:= l_exec_str;
3304: while length(l_exec_str_print)>0 loop
3305: hr_utility.trace(substr(l_exec_str_print,1,70));
3306: l_exec_str_print:=substr(l_exec_str_print,71);
3307: end loop;
3308: End if;
3309: if p_generation_scope = 'ALL_GLOBAL' then
3364: profile_add_to_cache;
3365: exception
3366: when no_data_found then
3367: if g_debug then
3368: hr_utility.set_location(l_proc, 380);
3369: end if;
3370: null;
3371: when others then
3372: if g_debug then
3369: end if;
3370: null;
3371: when others then
3372: if g_debug then
3373: hr_utility.set_location(l_proc||sqlerrm, 390);
3374: hr_utility.set_location(l_proc, 390);
3375: end if;
3376: raise;
3377: end;
3370: null;
3371: when others then
3372: if g_debug then
3373: hr_utility.set_location(l_proc||sqlerrm, 390);
3374: hr_utility.set_location(l_proc, 390);
3375: end if;
3376: raise;
3377: end;
3378: begin
3376: raise;
3377: end;
3378: begin
3379: if g_debug then
3380: hr_utility.set_location(l_proc, 400);
3381: end if;
3382: l_from_str := ' from per_security_profiles sec ';
3383: l_cond_str := ' sec.view_all_organizations_flag = :l_all
3384: and sec.view_all_positions_flag = :l_all
3389: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3390: if g_debug then
3391: l_exec_str_print:= l_exec_str;
3392: while length(l_exec_str_print)>0 loop
3393: hr_utility.trace(substr(l_exec_str_print,1,70));
3394: l_exec_str_print:=substr(l_exec_str_print,71);
3395: end loop;
3396: End if;
3397: if p_generation_scope = 'ALL_GLOBAL' then
3447: profile_add_to_cache;
3448: exception
3449: when no_data_found then
3450: if g_debug then
3451: hr_utility.set_location(l_proc, 410);
3452: end if;
3453: null;
3454: when others then
3455: if g_debug then
3452: end if;
3453: null;
3454: when others then
3455: if g_debug then
3456: hr_utility.set_location(l_proc||sqlerrm, 420);
3457: hr_utility.set_location(l_proc, 420);
3458: end if;
3459: raise;
3460: end;
3453: null;
3454: when others then
3455: if g_debug then
3456: hr_utility.set_location(l_proc||sqlerrm, 420);
3457: hr_utility.set_location(l_proc, 420);
3458: end if;
3459: raise;
3460: end;
3461: else -- position and payroll are not null
3460: end;
3461: else -- position and payroll are not null
3462: begin
3463: if g_debug then
3464: hr_utility.set_location(l_proc, 430);
3465: end if;
3466: l_from_str := ' from per_security_profiles sec,
3467: pay_payroll_list pay,
3468: per_position_list posl,
3485: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3486: if g_debug then
3487: l_exec_str_print:= l_exec_str;
3488: while length(l_exec_str_print)>0 loop
3489: hr_utility.trace(substr(l_exec_str_print,1,70));
3490: l_exec_str_print:=substr(l_exec_str_print,71);
3491: end loop;
3492: End if;
3493: if p_generation_scope = 'ALL_GLOBAL' then
3553: profile_add_to_cache;
3554: exception
3555: when no_data_found then
3556: if g_debug then
3557: hr_utility.set_location(l_proc, 440);
3558: end if;
3559: null;
3560: when others then
3561: if g_debug then
3558: end if;
3559: null;
3560: when others then
3561: if g_debug then
3562: hr_utility.set_location(l_proc||sqlerrm, 450);
3563: hr_utility.set_location(l_proc, 450);
3564: end if;
3565: raise;
3566: end;
3559: null;
3560: when others then
3561: if g_debug then
3562: hr_utility.set_location(l_proc||sqlerrm, 450);
3563: hr_utility.set_location(l_proc, 450);
3564: end if;
3565: raise;
3566: end;
3567: begin
3565: raise;
3566: end;
3567: begin
3568: if g_debug then
3569: hr_utility.set_location(l_proc, 460);
3570: end if;
3571: l_from_str := ' from per_security_profiles sec,
3572: per_position_list posl,
3573: per_organization_list org ';
3585: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3586: if g_debug then
3587: l_exec_str_print:= l_exec_str;
3588: while length(l_exec_str_print)>0 loop
3589: hr_utility.trace(substr(l_exec_str_print,1,70));
3590: l_exec_str_print:=substr(l_exec_str_print,71);
3591: end loop;
3592: End if;
3593: if p_generation_scope = 'ALL_GLOBAL' then
3651: profile_add_to_cache;
3652: exception
3653: when no_data_found then
3654: if g_debug then
3655: hr_utility.set_location(l_proc, 470);
3656: end if;
3657: null;
3658: when others then
3659: if g_debug then
3656: end if;
3657: null;
3658: when others then
3659: if g_debug then
3660: hr_utility.set_location(l_proc||sqlerrm, 480);
3661: hr_utility.set_location(l_proc, 480);
3662: end if;
3663: raise;
3664: end;
3657: null;
3658: when others then
3659: if g_debug then
3660: hr_utility.set_location(l_proc||sqlerrm, 480);
3661: hr_utility.set_location(l_proc, 480);
3662: end if;
3663: raise;
3664: end;
3665: begin
3663: raise;
3664: end;
3665: begin
3666: if g_debug then
3667: hr_utility.set_location(l_proc, 490);
3668: end if;
3669: l_from_str := ' from per_security_profiles sec,
3670: pay_payroll_list pay,
3671: per_organization_list org ';
3681: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3682: if g_debug then
3683: l_exec_str_print:= l_exec_str;
3684: while length(l_exec_str_print)>0 loop
3685: hr_utility.trace(substr(l_exec_str_print,1,70));
3686: l_exec_str_print:=substr(l_exec_str_print,71);
3687: end loop;
3688: End if;
3689: if p_generation_scope = 'ALL_GLOBAL' then
3743: profile_add_to_cache;
3744: exception
3745: when no_data_found then
3746: if g_debug then
3747: hr_utility.set_location(l_proc, 500);
3748: end if;
3749: null;
3750: when others then
3751: if g_debug then
3748: end if;
3749: null;
3750: when others then
3751: if g_debug then
3752: hr_utility.set_location(l_proc||sqlerrm, 510);
3753: hr_utility.set_location(l_proc, 510);
3754: end if;
3755: raise;
3756: end;
3749: null;
3750: when others then
3751: if g_debug then
3752: hr_utility.set_location(l_proc||sqlerrm, 510);
3753: hr_utility.set_location(l_proc, 510);
3754: end if;
3755: raise;
3756: end;
3757: begin
3755: raise;
3756: end;
3757: begin
3758: if g_debug then
3759: hr_utility.set_location(l_proc, 520);
3760: end if;
3761: l_from_str := ' from per_security_profiles sec,
3762: pay_payroll_list pay,
3763: per_position_list posl ';
3774: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3775: if g_debug then
3776: l_exec_str_print:= l_exec_str;
3777: while length(l_exec_str_print)>0 loop
3778: hr_utility.trace(substr(l_exec_str_print,1,70));
3779: l_exec_str_print:=substr(l_exec_str_print,71);
3780: end loop;
3781: End if;
3782: if p_generation_scope = 'ALL_GLOBAL' then
3836: profile_add_to_cache;
3837: exception
3838: when no_data_found then
3839: if g_debug then
3840: hr_utility.set_location(l_proc, 530);
3841: end if;
3842: null;
3843: when others then
3844: if g_debug then
3841: end if;
3842: null;
3843: when others then
3844: if g_debug then
3845: hr_utility.set_location(l_proc||sqlerrm, 540);
3846: hr_utility.set_location(l_proc, 540);
3847: end if;
3848: raise;
3849: end;
3842: null;
3843: when others then
3844: if g_debug then
3845: hr_utility.set_location(l_proc||sqlerrm, 540);
3846: hr_utility.set_location(l_proc, 540);
3847: end if;
3848: raise;
3849: end;
3850: begin
3848: raise;
3849: end;
3850: begin
3851: if g_debug then
3852: hr_utility.set_location(l_proc, 550);
3853: end if;
3854: l_from_str := ' from per_security_profiles sec,
3855: per_organization_list org ';
3856: l_cond_str := ' org.security_profile_id = sec.security_profile_id
3863: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3864: if g_debug then
3865: l_exec_str_print:= l_exec_str;
3866: while length(l_exec_str_print)>0 loop
3867: hr_utility.trace(substr(l_exec_str_print,1,70));
3868: l_exec_str_print:=substr(l_exec_str_print,71);
3869: end loop;
3870: End if;
3871: if p_generation_scope = 'ALL_GLOBAL' then
3924: profile_add_to_cache;
3925: exception
3926: when no_data_found then
3927: if g_debug then
3928: hr_utility.set_location(l_proc, 560);
3929: end if;
3930: null;
3931: when others then
3932: if g_debug then
3929: end if;
3930: null;
3931: when others then
3932: if g_debug then
3933: hr_utility.set_location(l_proc||sqlerrm, 570);
3934: hr_utility.set_location(l_proc, 570);
3935: end if;
3936: raise;
3937: end;
3930: null;
3931: when others then
3932: if g_debug then
3933: hr_utility.set_location(l_proc||sqlerrm, 570);
3934: hr_utility.set_location(l_proc, 570);
3935: end if;
3936: raise;
3937: end;
3938: begin
3936: raise;
3937: end;
3938: begin
3939: if g_debug then
3940: hr_utility.set_location(l_proc, 580);
3941: end if;
3942: l_from_str := ' from per_security_profiles sec,
3943: per_position_list posl ';
3944: l_cond_str := ' posl.security_profile_id = sec.security_profile_id
3951: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
3952: if g_debug then
3953: l_exec_str_print:= l_exec_str;
3954: while length(l_exec_str_print)>0 loop
3955: hr_utility.trace(substr(l_exec_str_print,1,70));
3956: l_exec_str_print:=substr(l_exec_str_print,71);
3957: end loop;
3958: End if;
3959: if p_generation_scope = 'ALL_GLOBAL' then
4011: profile_add_to_cache;
4012: exception
4013: when no_data_found then
4014: if g_debug then
4015: hr_utility.set_location(l_proc, 590);
4016: end if;
4017: null;
4018: when others then
4019: if g_debug then
4016: end if;
4017: null;
4018: when others then
4019: if g_debug then
4020: hr_utility.set_location(l_proc||sqlerrm, 600);
4021: hr_utility.set_location(l_proc, 600);
4022: end if;
4023: raise;
4024: end;
4017: null;
4018: when others then
4019: if g_debug then
4020: hr_utility.set_location(l_proc||sqlerrm, 600);
4021: hr_utility.set_location(l_proc, 600);
4022: end if;
4023: raise;
4024: end;
4025: begin
4023: raise;
4024: end;
4025: begin
4026: if g_debug then
4027: hr_utility.set_location(l_proc, 610);
4028: end if;
4029: l_from_str := ' from per_security_profiles sec,
4030: pay_payroll_list pay ';
4031: l_cond_str := ' pay.security_profile_id = sec.security_profile_id
4037: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
4038: if g_debug then
4039: l_exec_str_print:= l_exec_str;
4040: while length(l_exec_str_print)>0 loop
4041: hr_utility.trace(substr(l_exec_str_print,1,70));
4042: l_exec_str_print:=substr(l_exec_str_print,71);
4043: end loop;
4044: End if;
4045: if p_generation_scope = 'ALL_GLOBAL' then
4093: profile_add_to_cache;
4094: exception
4095: when no_data_found then
4096: if g_debug then
4097: hr_utility.set_location(l_proc, 620);
4098: end if;
4099: null;
4100: when others then
4101: if g_debug then
4098: end if;
4099: null;
4100: when others then
4101: if g_debug then
4102: hr_utility.set_location(l_proc||sqlerrm, 630);
4103: hr_utility.set_location(l_proc, 630);
4104: end if;
4105: raise;
4106: end;
4099: null;
4100: when others then
4101: if g_debug then
4102: hr_utility.set_location(l_proc||sqlerrm, 630);
4103: hr_utility.set_location(l_proc, 630);
4104: end if;
4105: raise;
4106: end;
4107: begin
4105: raise;
4106: end;
4107: begin
4108: if g_debug then
4109: hr_utility.set_location(l_proc, 640);
4110: end if;
4111: l_from_str := ' from per_security_profiles sec ';
4112: l_cond_str := ' sec.view_all_organizations_flag = :l_all
4113: and sec.view_all_positions_flag = :l_all
4123:
4124: if g_debug then
4125: l_exec_str_print:= l_exec_str;
4126: while length(l_exec_str_print)>0 loop
4127: hr_utility.trace(substr(l_exec_str_print,1,70));
4128: l_exec_str_print:=substr(l_exec_str_print,71);
4129: end loop;
4130: End if;
4131: if p_generation_scope = 'ALL_GLOBAL' then
4167: profile_add_to_cache;
4168: exception
4169: when no_data_found then
4170: if g_debug then
4171: hr_utility.set_location(l_proc, 650);
4172: end if;
4173: null;
4174: when others then
4175: if g_debug then
4172: end if;
4173: null;
4174: when others then
4175: if g_debug then
4176: hr_utility.set_location(l_proc||sqlerrm, 660);
4177: hr_utility.set_location(l_proc, 660);
4178: end if;
4179: raise;
4180: end;
4173: null;
4174: when others then
4175: if g_debug then
4176: hr_utility.set_location(l_proc||sqlerrm, 660);
4177: hr_utility.set_location(l_proc, 660);
4178: end if;
4179: raise;
4180: end;
4181: begin
4179: raise;
4180: end;
4181: begin
4182: if g_debug then
4183: hr_utility.set_location(l_proc, 670);
4184: end if;
4185: l_from_str := ' from per_security_profiles sec ';
4186: l_cond_str := ' sec.view_all_organizations_flag = :l_all
4187: and sec.view_all_positions_flag = :l_all
4192: l_bggr_str||l_asgt_str||l_cond_str||l_comm_str;
4193: if g_debug then
4194: l_exec_str_print:= l_exec_str;
4195: while length(l_exec_str_print)>0 loop
4196: hr_utility.trace(substr(l_exec_str_print,1,70));
4197: l_exec_str_print:=substr(l_exec_str_print,71);
4198: end loop;
4199: End if;
4200: if p_generation_scope = 'ALL_GLOBAL' then
4250: profile_add_to_cache;
4251: exception
4252: when no_data_found then
4253: if g_debug then
4254: hr_utility.set_location(l_proc, 680);
4255: end if;
4256: null;
4257: when others then
4258: if g_debug then
4255: end if;
4256: null;
4257: when others then
4258: if g_debug then
4259: hr_utility.set_location(l_proc||sqlerrm, 690);
4260: hr_utility.set_location(l_proc, 690);
4261: end if;
4262: raise;
4263: end;
4256: null;
4257: when others then
4258: if g_debug then
4259: hr_utility.set_location(l_proc||sqlerrm, 690);
4260: hr_utility.set_location(l_proc, 690);
4261: end if;
4262: raise;
4263: end;
4264: end if;
4263: end;
4264: end if;
4265: end if;
4266: if g_debug then
4267: hr_utility.set_location(l_proc, 700);
4268: end if;
4269: else
4270: close asg_details;
4271: end if;
4270: close asg_details;
4271: end if;
4272:
4273: if g_debug then
4274: hr_utility.set_location(l_proc, 705);
4275: end if;
4276: exception
4277: when no_data_found then
4278: if g_debug then
4275: end if;
4276: exception
4277: when no_data_found then
4278: if g_debug then
4279: hr_utility.set_location(l_proc, 730);
4280: end if;
4281: end profile_add_to_person_list;
4282:
4283:
4293: l_update_date date := trunc(sysdate);
4294:
4295: Begin
4296: if g_debug then
4297: hr_utility.set_location('Entering '||l_proc, 10);
4298: End if;
4299: If l_c_security_profile_table.count > 0 then
4300: if g_debug then
4301: hr_utility.set_location(l_proc, 20);
4297: hr_utility.set_location('Entering '||l_proc, 10);
4298: End if;
4299: If l_c_security_profile_table.count > 0 then
4300: if g_debug then
4301: hr_utility.set_location(l_proc, 20);
4302: End if;
4303: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
4304: hr_utility.set_location(l_proc||' using stage table',24);
4305: forall per_rec in l_c_security_profile_table.first .. l_c_security_profile_table.last
4300: if g_debug then
4301: hr_utility.set_location(l_proc, 20);
4302: End if;
4303: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
4304: hr_utility.set_location(l_proc||' using stage table',24);
4305: forall per_rec in l_c_security_profile_table.first .. l_c_security_profile_table.last
4306: SAVE EXCEPTIONS
4307: Insert into per_person_list_stage(security_profile_id,
4308: person_id,request_id,
4316: nvl(l_program_id, ''),
4317: to_date(to_char(l_update_date,'dd/mm/yyyy'), 'dd/mm/yyyy')
4318: );
4319: else
4320: hr_utility.set_location(l_proc||' using actual table',24);
4321: forall per_rec in l_c_security_profile_table.first .. l_c_security_profile_table.last
4322: SAVE EXCEPTIONS
4323: Insert into per_person_list(security_profile_id,
4324: person_id,request_id,
4335: end if;
4336: -- End changes for bug 13504049
4337: End if;
4338: if g_debug then
4339: hr_utility.set_location('Leaveing '||l_proc, 30);
4340: End if;
4341: Exception
4342: WHEN dml_errors THEN
4343: errors := SQL%BULK_EXCEPTIONS.COUNT;
4343: errors := SQL%BULK_EXCEPTIONS.COUNT;
4344: l_cnt := l_cnt + errors;
4345: FOR i IN 1..errors LOOP
4346: If g_debug then
4347: hr_utility.trace ('Error occurred during iteration ' ||
4348: SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||' Oracle error is ' ||
4349: SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
4350: End if;
4351: If SQL%BULK_EXCEPTIONS(i).ERROR_CODE <> 1 then
4365: l_proc varchar2(100):= 'process_person.profile_delete_cache_from_list';
4366:
4367: Begin
4368: if g_debug then
4369: hr_utility.set_location('Entering '||l_proc, 10);
4370: End if;
4371: If l_d_security_profile_table.count > 0 then
4372: if g_debug then
4373: hr_utility.set_location(l_proc, 20);
4369: hr_utility.set_location('Entering '||l_proc, 10);
4370: End if;
4371: If l_d_security_profile_table.count > 0 then
4372: if g_debug then
4373: hr_utility.set_location(l_proc, 20);
4374: End if;
4375:
4376:
4377: /* forall per_rec in l_d_security_profile_table.first .. l_d_security_profile_table.last
4400: and ppt.system_person_type = 'OTHER')
4401: and ppl.granted_user_id is null;*/
4402: -- Start changes for bug 13504049
4403: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
4404: hr_utility.set_location(l_proc||'deleting stage table', 24);
4405: forall per_rec in l_d_security_profile_table.first .. l_d_security_profile_table.last
4406: SAVE EXCEPTIONS
4407: Delete from per_person_list_stage
4408: where person_id = p_person_id
4407: Delete from per_person_list_stage
4408: where person_id = p_person_id
4409: and security_profile_id = l_d_security_profile_table(per_rec);
4410: else
4411: hr_utility.set_location(l_proc||'deleting actual table', 28);
4412: forall per_rec in l_d_security_profile_table.first .. l_d_security_profile_table.last
4413: SAVE EXCEPTIONS
4414: Delete from per_person_list
4415: where person_id = p_person_id
4417: end if;
4418: -- End changes for bug 13504049
4419: End if;
4420: if g_debug then
4421: hr_utility.set_location('Leaving '||l_proc, 30);
4422: End if;
4423: Exception
4424: WHEN dml_errors THEN
4425: errors := SQL%BULK_EXCEPTIONS.COUNT;
4425: errors := SQL%BULK_EXCEPTIONS.COUNT;
4426: l_cnt := l_cnt + errors;
4427: FOR i IN 1..errors LOOP
4428: If g_debug then
4429: hr_utility.trace ('Error occurred during iteration ' ||
4430: SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||' Oracle error is ' ||
4431: SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
4432: End if;
4433: If SQL%BULK_EXCEPTIONS(i).ERROR_CODE <> 1 then
4453: j number;
4454: k number;
4455:
4456: begin
4457: hr_utility.set_location('Entering '||l_proc,10);
4458: select security_profile_id bulk collect into l_c_security_profile_table
4459: from per_person_list ppl where
4460: ppl.person_id = p_person_id
4461: and ppl.granted_user_id is null and exists
4462: (select 'X' from per_security_profiles pspf
4463: where pspf.security_profile_id = ppl.security_profile_id);
4464:
4465:
4466: hr_utility.set_location('Current list',1);
4467: k := l_security_profile_table.first;
4468: while k is not null
4469: loop
4470: hr_utility.set_location(l_security_profile_table(k),2);
4466: hr_utility.set_location('Current list',1);
4467: k := l_security_profile_table.first;
4468: while k is not null
4469: loop
4470: hr_utility.set_location(l_security_profile_table(k),2);
4471: k := l_security_profile_table.next(k);
4472: end loop;
4473:
4474: i:=l_c_security_profile_table.first;
4473:
4474: i:=l_c_security_profile_table.first;
4475: while i is not null
4476: loop
4477: hr_utility.set_location('i = '||l_c_security_profile_table(i),20);
4478: chk:=0;
4479: j:=l_security_profile_table.first;
4480: while j is not null
4481: loop
4478: chk:=0;
4479: j:=l_security_profile_table.first;
4480: while j is not null
4481: loop
4482: hr_utility.set_location('j = '||l_security_profile_table(j),30);
4483: if l_c_security_profile_table(i)= l_security_profile_table(j) then
4484: chk := 1;
4485: hr_utility.set_location('Found-'||l_c_security_profile_table(i),40);
4486: l_c_security_profile_table.delete(i);
4481: loop
4482: hr_utility.set_location('j = '||l_security_profile_table(j),30);
4483: if l_c_security_profile_table(i)= l_security_profile_table(j) then
4484: chk := 1;
4485: hr_utility.set_location('Found-'||l_c_security_profile_table(i),40);
4486: l_c_security_profile_table.delete(i);
4487: l_security_profile_table.delete(j);
4488: exit;
4489: end if;
4489: end if;
4490: j:=l_security_profile_table.next(j);
4491: end loop;
4492: if chk = 0 then
4493: hr_utility.set_location('Not Found- Delete'||l_c_security_profile_table(i),50);
4494: l_d_security_profile_table(d_counter) := l_c_security_profile_table(i);
4495: d_counter := d_counter + 1;
4496: l_c_security_profile_table.delete(i);
4497: end if;
4503: l_c_security_profile_table.delete;
4504:
4505: k := l_security_profile_table.first;
4506: while k is not null loop
4507: hr_utility.set_location('New list : '|| l_security_profile_table(k),60);
4508: l_c_security_profile_table(l_counter):= l_security_profile_table(k);
4509: l_counter := l_counter + 1;
4510: k := l_security_profile_table.next(k);
4511: end loop;
4510: k := l_security_profile_table.next(k);
4511: end loop;
4512:
4513: end if;
4514: hr_utility.set_location('List to insert',70);
4515: k := l_c_security_profile_table.first;
4516: while k is not null
4517: loop
4518: hr_utility.set_location(l_c_security_profile_table(k),80);
4514: hr_utility.set_location('List to insert',70);
4515: k := l_c_security_profile_table.first;
4516: while k is not null
4517: loop
4518: hr_utility.set_location(l_c_security_profile_table(k),80);
4519: k := l_c_security_profile_table.next(k);
4520: end loop;
4521:
4522: hr_utility.set_location('List to delete',701);
4518: hr_utility.set_location(l_c_security_profile_table(k),80);
4519: k := l_c_security_profile_table.next(k);
4520: end loop;
4521:
4522: hr_utility.set_location('List to delete',701);
4523: k := l_d_security_profile_table.first;
4524: while k is not null
4525: loop
4526: hr_utility.set_location(l_d_security_profile_table(k),801);
4522: hr_utility.set_location('List to delete',701);
4523: k := l_d_security_profile_table.first;
4524: while k is not null
4525: loop
4526: hr_utility.set_location(l_d_security_profile_table(k),801);
4527: k := l_d_security_profile_table.next(k);
4528: end loop;
4529:
4530: hr_utility.set_location('Leaving '||l_proc,999);
4526: hr_utility.set_location(l_d_security_profile_table(k),801);
4527: k := l_d_security_profile_table.next(k);
4528: end loop;
4529:
4530: hr_utility.set_location('Leaving '||l_proc,999);
4531: end profile_process_list;
4532:
4533: -- Code changes for bug 8691129 end.
4534:
4534:
4535:
4536: BEGIN
4537:
4538: hr_utility.set_location('Entering : '||l_proc,10);
4539: hr_utility.set_location('p_person_id '||p_person_id,15);
4540: hr_utility.set_location('p_effective_date '||to_char(p_effective_date,
4541: 'DD-MON-YYYY'),20);
4542: hr_utility.set_location('p_business_group_id '||p_business_group_id,25);
4535:
4536: BEGIN
4537:
4538: hr_utility.set_location('Entering : '||l_proc,10);
4539: hr_utility.set_location('p_person_id '||p_person_id,15);
4540: hr_utility.set_location('p_effective_date '||to_char(p_effective_date,
4541: 'DD-MON-YYYY'),20);
4542: hr_utility.set_location('p_business_group_id '||p_business_group_id,25);
4543: hr_utility.set_location('p_generation_scope '||p_generation_scope,30);
4536: BEGIN
4537:
4538: hr_utility.set_location('Entering : '||l_proc,10);
4539: hr_utility.set_location('p_person_id '||p_person_id,15);
4540: hr_utility.set_location('p_effective_date '||to_char(p_effective_date,
4541: 'DD-MON-YYYY'),20);
4542: hr_utility.set_location('p_business_group_id '||p_business_group_id,25);
4543: hr_utility.set_location('p_generation_scope '||p_generation_scope,30);
4544:
4538: hr_utility.set_location('Entering : '||l_proc,10);
4539: hr_utility.set_location('p_person_id '||p_person_id,15);
4540: hr_utility.set_location('p_effective_date '||to_char(p_effective_date,
4541: 'DD-MON-YYYY'),20);
4542: hr_utility.set_location('p_business_group_id '||p_business_group_id,25);
4543: hr_utility.set_location('p_generation_scope '||p_generation_scope,30);
4544:
4545: /*
4546: ** We need to find out quickly if the person is an APL today or in the
4539: hr_utility.set_location('p_person_id '||p_person_id,15);
4540: hr_utility.set_location('p_effective_date '||to_char(p_effective_date,
4541: 'DD-MON-YYYY'),20);
4542: hr_utility.set_location('p_business_group_id '||p_business_group_id,25);
4543: hr_utility.set_location('p_generation_scope '||p_generation_scope,30);
4544:
4545: /*
4546: ** We need to find out quickly if the person is an APL today or in the
4547: ** future so we can control processing later.
4555: fetch c_is_former into l_is_former;
4556: close c_is_former;
4557: */
4558:
4559: hr_utility.set_location(l_proc,40);
4560:
4561:
4562: -- Code change for bug 8691129 Start
4563:
4596: fetch c_current_person into l_person_id, l_person_proc_date;
4597:
4598: if c_current_person%found then
4599: close c_current_person;
4600: hr_utility.set_location(l_proc,601);
4601: for l_asgrec in c_get_asg(l_person_id, l_person_proc_date) loop
4602: hr_utility.trace('processing assignment :'||to_char(l_asgrec.assignment_id));
4603: profile_add_to_person_list(
4604: p_effective_date => l_person_proc_date,
4598: if c_current_person%found then
4599: close c_current_person;
4600: hr_utility.set_location(l_proc,601);
4601: for l_asgrec in c_get_asg(l_person_id, l_person_proc_date) loop
4602: hr_utility.trace('processing assignment :'||to_char(l_asgrec.assignment_id));
4603: profile_add_to_person_list(
4604: p_effective_date => l_person_proc_date,
4605: p_assignment_id => l_asgrec.assignment_id,
4606: p_business_group_id => p_business_group_id,
4618: -- last assignment they had.
4619:
4620: if p_who_to_process in ('TERM','ALL') then
4621:
4622: hr_utility.set_location(l_proc,701);
4623:
4624: for l_former_person in c_former_person loop
4625: for l_asgrec in c_get_asg(l_former_person.person_id,l_former_person.effective_date) loop
4626: hr_utility.set_location(to_char(l_asgrec.assignment_id),901);
4622: hr_utility.set_location(l_proc,701);
4623:
4624: for l_former_person in c_former_person loop
4625: for l_asgrec in c_get_asg(l_former_person.person_id,l_former_person.effective_date) loop
4626: hr_utility.set_location(to_char(l_asgrec.assignment_id),901);
4627: hr_utility.trace('processing assignment :'||to_char(l_asgrec.assignment_id));
4628: profile_add_to_person_list(
4629: p_effective_date => l_former_person.effective_date,
4630: p_assignment_id => l_asgrec.assignment_id,
4623:
4624: for l_former_person in c_former_person loop
4625: for l_asgrec in c_get_asg(l_former_person.person_id,l_former_person.effective_date) loop
4626: hr_utility.set_location(to_char(l_asgrec.assignment_id),901);
4627: hr_utility.trace('processing assignment :'||to_char(l_asgrec.assignment_id));
4628: profile_add_to_person_list(
4629: p_effective_date => l_former_person.effective_date,
4630: p_assignment_id => l_asgrec.assignment_id,
4631: p_business_group_id => p_business_group_id,
4632: p_generation_scope => p_generation_scope);
4633:
4634: end loop;
4635: if l_former_person.assignment_type in ('E','C') then
4636: hr_utility.set_location(l_proc,1001);
4637: exit;
4638: end if;
4639: end loop;
4640:
4678: ** Optimizations for this issue will be considered at a later date. If this comment
4679: ** is still in the file then you know the optimization has not yet been completed.
4680: */
4681:
4682: hr_utility.set_location(l_proc,50);
4683: /*
4684: ** We are processing current EMP/APL/CWK or
4685: ** we are processing former EMP/APL/CWK but the person we are dealing
4686: ** with is both a current APL and a former EMP/CWK in which case we
4707: /*
4708: ** Populate the person list for each assignment that this person has.
4709: */
4710: for l_asgrec in c_get_asg(l_person_id, l_person_proc_date) loop
4711: hr_utility.set_location(l_proc,60);
4712: hr_utility.trace('processing assignment :'||
4713: to_char(l_asgrec.assignment_id));
4714: hr_security_internal.add_to_person_list(
4715: p_effective_date => l_person_proc_date,
4708: ** Populate the person list for each assignment that this person has.
4709: */
4710: for l_asgrec in c_get_asg(l_person_id, l_person_proc_date) loop
4711: hr_utility.set_location(l_proc,60);
4712: hr_utility.trace('processing assignment :'||
4713: to_char(l_asgrec.assignment_id));
4714: hr_security_internal.add_to_person_list(
4715: p_effective_date => l_person_proc_date,
4716: p_assignment_id => l_asgrec.assignment_id,
4727: -- or ( p_who_to_process = 'CURRENT'
4728: -- and l_is_current_apl = 'Y'
4729: -- and l_is_former = 'Y')
4730: then
4731: hr_utility.set_location(l_proc,70);
4732: /*
4733: ** We are processing former EMP/APL/CWK or
4734: ** we are processing current EMP/APL/CWK but the person we are dealing
4735: ** with is both a current APL and a former EMP/CWK in which case we
4750: /*-- End changes made for the bug 5252738 - Bug6809753 ---*/
4751: -- Bug6809753 -end
4752:
4753: for l_former_person in c_former_person loop
4754: hr_utility.set_location(l_proc,80);
4755: if l_cleared_ppl <> TRUE then
4756: /*
4757: ** Clear out the person list for this person_id if it's not been cleared
4758: ** already for this person.
4768: */
4769: for l_asgrec in c_get_asg(l_former_person.person_id,
4770: l_former_person.effective_date) loop
4771:
4772: hr_utility.set_location(l_proc,90);
4773: hr_utility.trace('processing assignment :'||
4774: to_char(l_asgrec.assignment_id));
4775:
4776: hr_security_internal.add_to_person_list(
4769: for l_asgrec in c_get_asg(l_former_person.person_id,
4770: l_former_person.effective_date) loop
4771:
4772: hr_utility.set_location(l_proc,90);
4773: hr_utility.trace('processing assignment :'||
4774: to_char(l_asgrec.assignment_id));
4775:
4776: hr_security_internal.add_to_person_list(
4777: p_effective_date => l_former_person.effective_date,
4789: ** person for the EX-type they were last. i.e. an EMP leaves and returns
4790: ** as CWK then leaves they will be both EX-EMP and EX-CWK in the PTU
4791: ** table. We only want to process them as EX-CWK.
4792: */
4793: hr_utility.set_location(l_proc,100);
4794: exit;
4795: end if;
4796: end loop;
4797: -- Bug6809753 -start
4806: p_effective_date => p_effective_date,
4807: p_generation_scope => p_generation_scope,
4808: p_business_group_id => p_business_group_id);
4809:
4810: hr_utility.set_location('Leaving : '||l_proc,110);
4811:
4812: end if;
4813: -- code changes for bug 8691129 end
4814:
4842: --
4843: begin
4844: --
4845: -- Get the session date and the current date to avoid multiple selects.
4846: hr_utility.set_location('Entering '||l_proc, 10);
4847: hr_utility.set_location('Request ID '||p_request_id, 15);
4848: --
4849: l_effective_date := trunc(p_effective_date);
4850: l_update_date := trunc(sysdate);
4843: begin
4844: --
4845: -- Get the session date and the current date to avoid multiple selects.
4846: hr_utility.set_location('Entering '||l_proc, 10);
4847: hr_utility.set_location('Request ID '||p_request_id, 15);
4848: --
4849: l_effective_date := trunc(p_effective_date);
4850: l_update_date := trunc(sysdate);
4851: --
4848: --
4849: l_effective_date := trunc(p_effective_date);
4850: l_update_date := trunc(sysdate);
4851: --
4852: hr_utility.set_location(l_proc, 20);
4853: --
4854: for sec_rec in security_profiles loop
4855: --
4856: -- Delete previous entries for the profile. By using this function
4860: p_business_group_id => sec_rec.business_group_id,
4861: p_security_profile_id => sec_rec.security_profile_id,
4862: p_clear_people_flag => false);
4863: --
4864: hr_utility.set_location(l_proc, 30);
4865: --
4866: if (sec_rec.view_all_flag = 'N') then
4867: --
4868: hr_utility.set_location(l_proc, 40);
4864: hr_utility.set_location(l_proc, 30);
4865: --
4866: if (sec_rec.view_all_flag = 'N') then
4867: --
4868: hr_utility.set_location(l_proc, 40);
4869: --
4870: if (sec_rec.view_all_payrolls_flag = 'N') then
4871: --
4872: hr_utility.set_location(l_proc, 50);
4868: hr_utility.set_location(l_proc, 40);
4869: --
4870: if (sec_rec.view_all_payrolls_flag = 'N') then
4871: --
4872: hr_utility.set_location(l_proc, 50);
4873: --
4874: -- Build the payroll list.
4875: build_payroll_list(sec_rec.security_profile_id,
4876: sec_rec.business_group_id,
4883: -- Do not insert the orgs if using user-based security.
4884: if (sec_rec.view_all_organizations_flag = 'N' and
4885: nvl(sec_rec.top_organization_method, 'S') <> 'U') then
4886: --
4887: hr_utility.set_location(l_proc, 60);
4888: --
4889: -- Determine business_group mode for the current security profile
4890: if sec_rec.business_group_id is null then
4891: l_business_group_mode := 'GLOBAL';
4908: -- Do not insert the positions if using user-based security.
4909: if (sec_rec.view_all_positions_flag = 'N' and
4910: nvl(sec_rec.top_position_method, 'S') <> 'U') then
4911: --
4912: hr_utility.set_location(l_proc, 70);
4913: --
4914: -- Build position list
4915: build_position_list(sec_rec.security_profile_id,
4916: sec_rec.view_all_organizations_flag,
4921: l_update_date);
4922: --
4923: end if;
4924: --
4925: hr_utility.set_location(l_proc, 80);
4926: --
4927: end if;
4928: --
4929: l_found:=true;
4927: end if;
4928: --
4929: l_found:=true;
4930: --
4931: hr_utility.set_location('Request ID '||p_request_id, 15);
4932: --
4933: end loop;
4934: --
4935: hr_utility.set_location(l_proc, 80);
4931: hr_utility.set_location('Request ID '||p_request_id, 15);
4932: --
4933: end loop;
4934: --
4935: hr_utility.set_location(l_proc, 80);
4936: -- Clearing all unrelated contacts.
4937: clear_unrelated_contacts(p_generation_scope => p_generation_scope,
4938: p_business_group_id => p_business_group_id);
4939: --
4937: clear_unrelated_contacts(p_generation_scope => p_generation_scope,
4938: p_business_group_id => p_business_group_id);
4939: --
4940: -- Add unrelated contacts for the profiles we've processed.
4941: hr_utility.set_location(l_proc, 90);
4942: hr_utility.set_location('Request ID '||p_request_id, 15);
4943: --
4944: add_unrelated_contacts(p_business_group_id => p_business_group_id,
4945: p_generation_scope => p_generation_scope,
4938: p_business_group_id => p_business_group_id);
4939: --
4940: -- Add unrelated contacts for the profiles we've processed.
4941: hr_utility.set_location(l_proc, 90);
4942: hr_utility.set_location('Request ID '||p_request_id, 15);
4943: --
4944: add_unrelated_contacts(p_business_group_id => p_business_group_id,
4945: p_generation_scope => p_generation_scope,
4946: p_effective_date => l_effective_date);
4944: add_unrelated_contacts(p_business_group_id => p_business_group_id,
4945: p_generation_scope => p_generation_scope,
4946: p_effective_date => l_effective_date);
4947: --
4948: hr_utility.set_location('Leaving '||l_proc, 130);
4949: --
4950: end generate_opp_lists;
4951:
4952: --
5039: AND responsibility_id = fusg.responsibility_id)
5040: AND rownum = 1;
5041:
5042: BEGIN
5043: hr_utility.set_location('Entering: '||l_proc,10);
5044: hr_utility.set_location(l_proc||' sec prof id '||
5045: to_char(p_sec_prof_rec.security_profile_id),13);
5046: hr_utility.set_location(l_proc||' p_effective_date '||
5047: p_effective_date,13);
5040: AND rownum = 1;
5041:
5042: BEGIN
5043: hr_utility.set_location('Entering: '||l_proc,10);
5044: hr_utility.set_location(l_proc||' sec prof id '||
5045: to_char(p_sec_prof_rec.security_profile_id),13);
5046: hr_utility.set_location(l_proc||' p_effective_date '||
5047: p_effective_date,13);
5048: hr_utility.set_location(l_proc||' p_user_id '||
5042: BEGIN
5043: hr_utility.set_location('Entering: '||l_proc,10);
5044: hr_utility.set_location(l_proc||' sec prof id '||
5045: to_char(p_sec_prof_rec.security_profile_id),13);
5046: hr_utility.set_location(l_proc||' p_effective_date '||
5047: p_effective_date,13);
5048: hr_utility.set_location(l_proc||' p_user_id '||
5049: p_user_id,13);
5050:
5044: hr_utility.set_location(l_proc||' sec prof id '||
5045: to_char(p_sec_prof_rec.security_profile_id),13);
5046: hr_utility.set_location(l_proc||' p_effective_date '||
5047: p_effective_date,13);
5048: hr_utility.set_location(l_proc||' p_user_id '||
5049: p_user_id,13);
5050:
5051: --
5052: -- Check that the mandatory parameters have been entered.
5104: csr_get_resp(user_rec.user_id,to_char(p_sec_prof_rec.security_profile_id));
5105: FETCH csr_get_resp INTO l_resp_id,
5106: l_resp_app_id,
5107: l_sec_grp_id;
5108: hr_utility.set_location('Security Groups No - Resp id-'||l_resp_id,555);
5109: CLOSE csr_get_resp;
5110: else
5111: OPEN
5112: csr_get_resp_sge(user_rec.user_id,to_char(p_sec_prof_rec.security_profile_id));
5112: csr_get_resp_sge(user_rec.user_id,to_char(p_sec_prof_rec.security_profile_id));
5113: FETCH csr_get_resp_sge INTO l_resp_id,
5114: l_resp_app_id,
5115: l_sec_grp_id;
5116: hr_utility.set_location('Security Groups Yes - Resp id '||l_resp_id,556);
5117: CLOSE csr_get_resp_sge;
5118: end if;
5119: IF l_resp_id is not null then
5120: fnd_global.apps_initialize(user_rec.user_id,
5121: l_resp_id,
5122: l_resp_app_id,
5123: l_sec_grp_id);
5124: else
5125: hr_utility.set_location('Security Profile was not attached to any responsibility of this user',557);
5126: end if;
5127: END IF;
5128:
5129: -- If user-based custom security is not used or
5169: ,p_del_static_lists_warning => l_del_static_lists_warning);
5170: END IF;
5171: END LOOP;
5172: END IF; -- p_sec_prof_rec.security_profile_id has value
5173: hr_utility.set_location('Leaving : '||l_proc,50);
5174: END build_lists_for_users;
5175: --
5176: -- ----------------------------------------------------------------------------
5177: -- |---------------------< build_lists_for_user >------------------------------|
5216: where security_profile_id = l_security_profile_id;
5217: --
5218: BEGIN
5219: --
5220: hr_utility.trace('Processing for Single User');
5221: hr_utility.set_location('Entering : '||l_proc,10);
5222: --
5223: -- Check that the mandatory parameters have been entered.
5224: --
5217: --
5218: BEGIN
5219: --
5220: hr_utility.trace('Processing for Single User');
5221: hr_utility.set_location('Entering : '||l_proc,10);
5222: --
5223: -- Check that the mandatory parameters have been entered.
5224: --
5225: IF p_security_profile_id IS NOT NULL
5231: FETCH csr_check_user_exists
5232: INTO l_user_id,
5233: l_security_profile_id;
5234: CLOSE csr_check_user_exists;
5235: hr_utility.set_location(l_proc||'p_effective_date : '||p_effective_date,20);
5236: hr_utility.set_location(l_proc||'p_user_id : '||p_user_id,21);
5237: hr_utility.set_location(l_proc||'p_security_profile_id : '||
5238: p_security_profile_id,23);
5239: hr_utility.set_location(l_proc||'l_user_id : '||l_user_id,25);
5232: INTO l_user_id,
5233: l_security_profile_id;
5234: CLOSE csr_check_user_exists;
5235: hr_utility.set_location(l_proc||'p_effective_date : '||p_effective_date,20);
5236: hr_utility.set_location(l_proc||'p_user_id : '||p_user_id,21);
5237: hr_utility.set_location(l_proc||'p_security_profile_id : '||
5238: p_security_profile_id,23);
5239: hr_utility.set_location(l_proc||'l_user_id : '||l_user_id,25);
5240: hr_utility.set_location(l_proc||'l_security_profile_id : '||
5233: l_security_profile_id;
5234: CLOSE csr_check_user_exists;
5235: hr_utility.set_location(l_proc||'p_effective_date : '||p_effective_date,20);
5236: hr_utility.set_location(l_proc||'p_user_id : '||p_user_id,21);
5237: hr_utility.set_location(l_proc||'p_security_profile_id : '||
5238: p_security_profile_id,23);
5239: hr_utility.set_location(l_proc||'l_user_id : '||l_user_id,25);
5240: hr_utility.set_location(l_proc||'l_security_profile_id : '||
5241: l_security_profile_id,27);
5235: hr_utility.set_location(l_proc||'p_effective_date : '||p_effective_date,20);
5236: hr_utility.set_location(l_proc||'p_user_id : '||p_user_id,21);
5237: hr_utility.set_location(l_proc||'p_security_profile_id : '||
5238: p_security_profile_id,23);
5239: hr_utility.set_location(l_proc||'l_user_id : '||l_user_id,25);
5240: hr_utility.set_location(l_proc||'l_security_profile_id : '||
5241: l_security_profile_id,27);
5242: hr_utility.set_location('IF l_user_id or l_security profile id is null '
5243: ,30);
5236: hr_utility.set_location(l_proc||'p_user_id : '||p_user_id,21);
5237: hr_utility.set_location(l_proc||'p_security_profile_id : '||
5238: p_security_profile_id,23);
5239: hr_utility.set_location(l_proc||'l_user_id : '||l_user_id,25);
5240: hr_utility.set_location(l_proc||'l_security_profile_id : '||
5241: l_security_profile_id,27);
5242: hr_utility.set_location('IF l_user_id or l_security profile id is null '
5243: ,30);
5244: hr_utility.set_location('then it has not been found in per_security_users'
5238: p_security_profile_id,23);
5239: hr_utility.set_location(l_proc||'l_user_id : '||l_user_id,25);
5240: hr_utility.set_location(l_proc||'l_security_profile_id : '||
5241: l_security_profile_id,27);
5242: hr_utility.set_location('IF l_user_id or l_security profile id is null '
5243: ,30);
5244: hr_utility.set_location('then it has not been found in per_security_users'
5245: ,30);
5246: --
5240: hr_utility.set_location(l_proc||'l_security_profile_id : '||
5241: l_security_profile_id,27);
5242: hr_utility.set_location('IF l_user_id or l_security profile id is null '
5243: ,30);
5244: hr_utility.set_location('then it has not been found in per_security_users'
5245: ,30);
5246: --
5247: -- if they exist then get security profile for user
5248: --
5250: FETCH csr_security_profile_record
5251: INTO l_sec_prof_rec;
5252: CLOSE csr_security_profile_record;
5253: -- call build lists for users for a single user
5254: hr_utility.set_location(l_proc||'call build_lists_for_users '
5255: ||p_effective_date,40);
5256: build_lists_for_users
5257: (p_sec_prof_rec => l_sec_prof_rec
5258: ,p_effective_date => trunc(p_effective_date)
5259: ,p_debug => l_debug
5260: ,p_user_id => l_user_id
5261: ,p_process_all_users => l_process_all_users);
5262: END IF;
5263: hr_utility.set_location('Leaving : '||l_proc,69);
5264: END build_lists_for_user;
5265: --
5266: -- ----------------------------------------------------------------------------
5267: -- |---------------------< generate_list_control >-----------------------------|
5334: AND org_security_mode IN ('NONE','HIER')
5335: );
5336: --
5337: begin
5338: hr_utility.set_location('Entering : '||l_proc,10);
5339:
5340: l_effective_date := fnd_date.date_to_canonical(p_effective_date);
5341: l_security_profile_id := to_number(p_security_profile_id);
5342: l_business_group_id := to_number(p_business_group_id);
5347: /*
5348: ** Perform restart checking....
5349: */
5350: l_request_data := fnd_conc_global.request_data;
5351: hr_utility.set_location(l_request_data,12);
5352: if l_request_data is not null then
5353:
5354: OPEN csr_get_action_param;
5355: FETCH csr_get_action_param INTO l_logging;
5368: -- revise.
5369: /*
5370: IF p_user_id IS NOT NULL
5371: THEN
5372: hr_utility.trace('Processing for Single User - 1');
5373: --
5374: build_lists_for_user
5375: (p_security_profile_id => p_security_profile_id
5376: ,p_user_id => l_user_id
5390: ** On restart we need to finish off by processing the
5391: ** ex-emps.
5392: */
5393: FOR sec_rec in security_profiles LOOP
5394: hr_utility.set_location(l_proc||' SP id '||
5395: to_char(sec_rec.security_profile_id),13);
5396: add_person_list_changes (sec_rec.security_profile_id,
5397: p_effective_date,
5398: l_update_date);
5423: dev_phase => l_dev_phase,
5424: dev_status => l_dev_status,
5425: message => l_message);
5426:
5427: hr_utility.set_location(l_proc||' Dev phase:'||l_dev_phase,14);
5428: hr_utility.set_location(l_proc||' Dev status:'||l_dev_status,15);
5429:
5430: if l_dev_phase = 'COMPLETE' and l_dev_status = 'ERROR' then
5431: errbuf := l_message;
5424: dev_status => l_dev_status,
5425: message => l_message);
5426:
5427: hr_utility.set_location(l_proc||' Dev phase:'||l_dev_phase,14);
5428: hr_utility.set_location(l_proc||' Dev status:'||l_dev_status,15);
5429:
5430: if l_dev_phase = 'COMPLETE' and l_dev_status = 'ERROR' then
5431: errbuf := l_message;
5432: retcode := 2;
5448: p_security_profile_id is null then
5449: /*
5450: ** No security profile has been specified.
5451: */
5452: hr_utility.set_message(800,'PER_289776_NO_PROF_ID');
5453: hr_utility.raise_error;
5454: elsif p_generation_scope = 'ALL_BUS_GRP' and
5455: p_business_group_id is null then
5456: /*
5449: /*
5450: ** No security profile has been specified.
5451: */
5452: hr_utility.set_message(800,'PER_289776_NO_PROF_ID');
5453: hr_utility.raise_error;
5454: elsif p_generation_scope = 'ALL_BUS_GRP' and
5455: p_business_group_id is null then
5456: /*
5457: ** No business group has been specified.
5455: p_business_group_id is null then
5456: /*
5457: ** No business group has been specified.
5458: */
5459: hr_utility.set_message(800,'PER_289777_NO_BG_ID');
5460: hr_utility.raise_error;
5461: elsif p_generation_scope = 'SINGLE_USER' and
5462: p_user_id is null then
5463: /*
5456: /*
5457: ** No business group has been specified.
5458: */
5459: hr_utility.set_message(800,'PER_289777_NO_BG_ID');
5460: hr_utility.raise_error;
5461: elsif p_generation_scope = 'SINGLE_USER' and
5462: p_user_id is null then
5463: /*
5464: ** No user has been specified. Bug 4338667.
5462: p_user_id is null then
5463: /*
5464: ** No user has been specified. Bug 4338667.
5465: */
5466: hr_utility.set_message(800,'PER_50293_NO_USER_ID');
5467: hr_utility.raise_error;
5468: end if;
5469: -- Bug 4338667 call build_lists_for_user directly if single user option
5470: -- specified.
5463: /*
5464: ** No user has been specified. Bug 4338667.
5465: */
5466: hr_utility.set_message(800,'PER_50293_NO_USER_ID');
5467: hr_utility.raise_error;
5468: end if;
5469: -- Bug 4338667 call build_lists_for_user directly if single user option
5470: -- specified.
5471: --
5470: -- specified.
5471: --
5472: /* IF p_generation_scope = 'SINGLE_USER'
5473: THEN
5474: hr_utility.trace('Processing for Single User');
5475: build_lists_for_user
5476: (p_security_profile_id => p_security_profile_id
5477: ,p_user_id => l_user_id
5478: ,p_effective_date => p_effective_date);
5477: ,p_user_id => l_user_id
5478: ,p_effective_date => p_effective_date);
5479: ELSE */
5480: if p_generation_scope in ('SINGLE_PROF','SINGLE_USER') then
5481: hr_utility.trace('Processing for Single Profile or Single User');
5482: hr_utility.set_location(l_proc||' Single Profile or Single User ',19);
5483: hr_utility.set_location(l_proc||' call generate_lists ',20);
5484: hr_utility.set_location(l_proc||' p_use_temp_tables '||p_use_temp_tables,20);
5485:
5478: ,p_effective_date => p_effective_date);
5479: ELSE */
5480: if p_generation_scope in ('SINGLE_PROF','SINGLE_USER') then
5481: hr_utility.trace('Processing for Single Profile or Single User');
5482: hr_utility.set_location(l_proc||' Single Profile or Single User ',19);
5483: hr_utility.set_location(l_proc||' call generate_lists ',20);
5484: hr_utility.set_location(l_proc||' p_use_temp_tables '||p_use_temp_tables,20);
5485:
5486: -- Start changes for bug 13504049
5479: ELSE */
5480: if p_generation_scope in ('SINGLE_PROF','SINGLE_USER') then
5481: hr_utility.trace('Processing for Single Profile or Single User');
5482: hr_utility.set_location(l_proc||' Single Profile or Single User ',19);
5483: hr_utility.set_location(l_proc||' call generate_lists ',20);
5484: hr_utility.set_location(l_proc||' p_use_temp_tables '||p_use_temp_tables,20);
5485:
5486: -- Start changes for bug 13504049
5487: g_use_temp_table := p_use_temp_tables;
5480: if p_generation_scope in ('SINGLE_PROF','SINGLE_USER') then
5481: hr_utility.trace('Processing for Single Profile or Single User');
5482: hr_utility.set_location(l_proc||' Single Profile or Single User ',19);
5483: hr_utility.set_location(l_proc||' call generate_lists ',20);
5484: hr_utility.set_location(l_proc||' p_use_temp_tables '||p_use_temp_tables,20);
5485:
5486: -- Start changes for bug 13504049
5487: g_use_temp_table := p_use_temp_tables;
5488: g_generation_scope := p_generation_scope;
5494: p_user_id => l_user_id,
5495: p_static_user_processing => p_static_user_processing);
5496:
5497: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
5498: hr_utility.set_location(l_proc||'Sync stage table with actual table',21);
5499: insert into per_person_list(
5500: SECURITY_PROFILE_ID,
5501: PERSON_ID,
5502: REQUEST_ID,
5530: and nvl(granted_user_id,-1) = nvl(a.granted_user_id,-1));
5531:
5532: commit;
5533:
5534: hr_utility.set_location(l_proc||'Synchronization completed',21);
5535: end if;
5536:
5537: g_use_temp_table := 'N';
5538: -- End changes for bug 13504049
5542: ** In this case we can process by assignment using PYUGEN if HR is installed.
5543: ** If HR is shared then use the old sequential mechanism.
5544: */
5545: if hr_general.chk_product_installed(800) = 'TRUE' then
5546: hr_utility.set_location(l_proc,30);
5547: /*
5548: ** HR is fully installed so we will use PYUGEN for the person list. First
5549: ** we must generate the Org, Pos and Payroll list information...
5550: */
5553: p_business_group_id => l_business_group_id);
5554: /*
5555: ** ...now submit PYUGEN to do the people bit...
5556: */
5557: hr_utility.set_location(l_proc,40);
5558:
5559: l_request_id := fnd_request.submit_request(application => 'PER',
5560: program => 'MSL_PERSON_LIST',
5561: sub_request => TRUE,
5594: /*
5595: ** HR is shared so use the old mechanism but using the new submission
5596: ** mechanism.
5597: */
5598: hr_utility.set_location(l_proc,50);
5599:
5600: generate_lists(p_effective_date => p_effective_date,
5601: p_generation_scope => p_generation_scope,
5602: p_business_group_id => l_business_group_id,
5606:
5607: end if;
5608: --end if;
5609: end if;
5610: hr_utility.set_location('Leaving : '||l_proc,100);
5611:
5612: end generate_list_control;
5613: --
5614:
5726: l_proc varchar2(100) := g_package||'submit_security';
5727:
5728: begin
5729:
5730: -- hr_utility.trace_on('F','LISTGEN');
5731:
5732: hr_utility.set_location('Entering '||l_proc,10);
5733:
5734: --
5728: begin
5729:
5730: -- hr_utility.trace_on('F','LISTGEN');
5731:
5732: hr_utility.set_location('Entering '||l_proc,10);
5733:
5734: --
5735: -- Set variables used for WHO columns
5736: --
5759: errbuf => errbuf,
5760: retcode => retcode,
5761: p_use_temp_tables => p_use_temp_tables); --Added for bug 13504049
5762:
5763: hr_utility.set_location('Leaving '||l_proc,20);
5764: end;
5765: --
5766: -- ----------------------------------------------------------------------------
5767: -- |----------------------< generate_lists >----------------------------------|
5849: --
5850: begin
5851: --
5852: -- Get the session date and the current date to avoid multiple selects.
5853: hr_utility.set_location('Entering '||l_proc, 10);
5854: --
5855: l_effective_date := trunc(p_effective_date);
5856: l_update_date := trunc(sysdate);
5857: l_user_id := p_user_id;
5867: -- Bug 4338667: if user name is passed then this must be running for single
5868: -- user in single security profile, so just call build_list_for_user directly.
5869: /* IF p_user_id IS NOT NULL
5870: THEN
5871: hr_utility.trace('Processing for Single User - 2');
5872: build_lists_for_user
5873: (p_security_profile_id => p_security_profile_id
5874: ,p_user_id => l_user_id
5875: ,p_effective_date => p_effective_date);
5883: ELSE
5884: l_process_all_users := TRUE;
5885: END IF;
5886: --
5887: hr_utility.set_location(l_proc, 20);
5888: --
5889: for sec_rec in security_profiles loop
5890:
5891: -- Start chages for bug 13504049
5893: fetch csr_get_static_users into l_static_user_id;
5894: close csr_get_static_users;
5895:
5896: if l_static_user_id is not null then
5897: hr_utility.set_location(l_proc||' static list in use', 10);
5898: g_static_list_profile := 'Y';
5899:
5900: else
5901:
5932: p_business_group_id => sec_rec.business_group_id,
5933: p_security_profile_id => sec_rec.security_profile_id,
5934: p_clear_people_flag => true);
5935: --
5936: hr_utility.set_location(l_proc, 30);
5937: --
5938: -- If there are no restrictions or this profile uses user-based
5939: -- security, do not execute the inserts.
5940: if(sec_rec.view_all_employees_flag = 'N' or
5947: sec_rec.view_all_positions_flag = 'N' or
5948: sec_rec.view_all_payrolls_flag = 'N' or
5949: sec_rec.custom_restriction_flag = 'Y') then
5950: --
5951: hr_utility.set_location(l_proc, 40);
5952: --
5953: if (sec_rec.view_all_payrolls_flag = 'N') then
5954: --
5955: hr_utility.set_location(l_proc, 50);
5951: hr_utility.set_location(l_proc, 40);
5952: --
5953: if (sec_rec.view_all_payrolls_flag = 'N') then
5954: --
5955: hr_utility.set_location(l_proc, 50);
5956: --
5957: -- Build the payroll list.
5958: build_payroll_list(sec_rec.security_profile_id,
5959: sec_rec.business_group_id,
5966: -- Do not insert if using user-based security.
5967: if(sec_rec.view_all_organizations_flag = 'N' and
5968: nvl(sec_rec.top_organization_method, 'S') <> 'U') then
5969: --
5970: hr_utility.set_location(l_proc, 60);
5971: -- Determine business_group mode for the current security profile
5972: if sec_rec.business_group_id is null then
5973: l_business_group_mode := 'GLOBAL';
5974: else
5990: if(sec_rec.view_all_positions_flag = 'N' and
5991: nvl(sec_rec.top_organization_method, 'S') <> 'U' and
5992: nvl(sec_rec.top_position_method, 'S') <> 'U') then
5993: --
5994: hr_utility.set_location(l_proc, 70);
5995: -- Build position list
5996: build_position_list(sec_rec.security_profile_id,
5997: sec_rec.view_all_organizations_flag,
5998: sec_rec.include_top_position_flag,
6063: l_found := true;
6064: --
6065: end loop;
6066: --
6067: hr_utility.set_location(l_proc, 130);
6068: --
6069: if not l_found then
6070: --
6071: hr_utility.set_message(800, 'HR_PROFILE_NOT_FOUND');
6067: hr_utility.set_location(l_proc, 130);
6068: --
6069: if not l_found then
6070: --
6071: hr_utility.set_message(800, 'HR_PROFILE_NOT_FOUND');
6072: hr_utility.set_message_token ('PROFILE_NAME', p_security_profile_name);
6073: hr_utility.raise_error;
6074: --
6075: end if;
6068: --
6069: if not l_found then
6070: --
6071: hr_utility.set_message(800, 'HR_PROFILE_NOT_FOUND');
6072: hr_utility.set_message_token ('PROFILE_NAME', p_security_profile_name);
6073: hr_utility.raise_error;
6074: --
6075: end if;
6076: --
6069: if not l_found then
6070: --
6071: hr_utility.set_message(800, 'HR_PROFILE_NOT_FOUND');
6072: hr_utility.set_message_token ('PROFILE_NAME', p_security_profile_name);
6073: hr_utility.raise_error;
6074: --
6075: end if;
6076: --
6077: hr_utility.set_location('Leaving '||l_proc, 140);
6073: hr_utility.raise_error;
6074: --
6075: end if;
6076: --
6077: hr_utility.set_location('Leaving '||l_proc, 140);
6078: --
6079: end generate_lists;
6080: --
6081: -- ----------------------------------------------------------------------------
6087:
6088: l_proc varchar2(100) := g_package||'range_curosr';
6089: l_generation_scope varchar2(20);
6090: begin
6091: --hr_utility.trace_on('F','LISTGEN');
6092: hr_utility.set_location('Entering : '||l_proc,10);
6093: select pay_core_utils.get_parameter('GENERATION_SCOPE',
6094: pa1.legislative_parameters)
6095: into l_generation_scope
6088: l_proc varchar2(100) := g_package||'range_curosr';
6089: l_generation_scope varchar2(20);
6090: begin
6091: --hr_utility.trace_on('F','LISTGEN');
6092: hr_utility.set_location('Entering : '||l_proc,10);
6093: select pay_core_utils.get_parameter('GENERATION_SCOPE',
6094: pa1.legislative_parameters)
6095: into l_generation_scope
6096: from pay_payroll_actions pa1
6102: */
6103: if l_generation_scope = 'ALL_PROFILES'
6104: or l_generation_scope = 'ALL_GLOBAL'
6105: then
6106: hr_utility.set_location(l_proc,20);
6107: sqlstr := 'select distinct per.person_id
6108: from per_all_people_f per
6109: ,pay_payroll_actions ppa
6110: where ppa.payroll_action_id = :payroll_action_id
6114: else
6115: /*
6116: **scope is ALL_BUS_GRP
6117: */
6118: hr_utility.set_location(l_proc,30);
6119: sqlstr := 'select distinct per.person_id
6120: from per_all_people_f per
6121: ,pay_payroll_actions ppa
6122: where ppa.payroll_action_id = :payroll_action_id
6126: order by per.person_id';
6127: -- and ppa.effective_date between per.effective_start_date
6128: -- and per.effective_end_date
6129: end if;
6130: hr_utility.set_location('Leaving : '||l_proc,40);
6131: end range_cursor;
6132: --
6133: -- ----------------------------------------------------------------------------
6134: -- |--------------------------< action_creation >-----------------------------|
6441: l_generation_scope varchar2(20);
6442: l_who_to_process varchar2(30);
6443:
6444: begin
6445: --hr_utility.trace_on('F','PERSLM');
6446:
6447:
6448: select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',
6449: ppa.legislative_parameters),
6540: and pspv.view_all_candidates_flag <> p_profile_option;
6541: --
6542: begin
6543: --
6544: hr_utility.set_location('Entering '||l_proc, 10);
6545: --
6546: -- Checking that whether iRecruitment is installed.
6547: if nvl(fnd_profile.value('IRC_INSTALLED_FLAG'), 'N') = 'N' then
6548: raise e_irec_not_installed;
6547: if nvl(fnd_profile.value('IRC_INSTALLED_FLAG'), 'N') = 'N' then
6548: raise e_irec_not_installed;
6549: end if;
6550: --
6551: hr_utility.set_location(l_proc, 20);
6552: -- Assume that, iRecruitment is installed, hence continuing the process.
6553: for r_sec in csr_security_prof loop
6554: --
6555: begin
6553: for r_sec in csr_security_prof loop
6554: --
6555: begin
6556: --
6557: hr_utility.set_location(l_proc, 30);
6558: -- Get the lock of the respective security profile record.
6559: per_security_profiles_pkg.lock_row(
6560: x_rowid => r_sec.row_id,
6561: x_security_profile_id => r_sec.security_profile_id,
6592: x_top_organization_method => rtrim(r_sec.top_organization_method),
6593: x_top_position_method => rtrim(r_sec.top_position_method)
6594: );
6595: --
6596: hr_utility.set_location(l_proc, 40);
6597: -- Sucessfully locked the row, now updating the
6598: -- view_all_candidates_flag with the given value through parameter
6599: -- p_profile_option
6600: per_security_profiles_pkg.update_row(
6637: x_program_id => l_prog_id,
6638: x_program_update_date => l_upd_date
6639: );
6640: --
6641: hr_utility.set_location('Sec Prof Id:'||r_sec.security_profile_id, 50);
6642: hr_utility.set_location('Sec Name:'||r_sec.security_profile_name, 55);
6643: hr_utility.set_location('BG Id:'||r_sec.business_group_id, 60);
6644: --
6645: exception
6638: x_program_update_date => l_upd_date
6639: );
6640: --
6641: hr_utility.set_location('Sec Prof Id:'||r_sec.security_profile_id, 50);
6642: hr_utility.set_location('Sec Name:'||r_sec.security_profile_name, 55);
6643: hr_utility.set_location('BG Id:'||r_sec.business_group_id, 60);
6644: --
6645: exception
6646: --
6639: );
6640: --
6641: hr_utility.set_location('Sec Prof Id:'||r_sec.security_profile_id, 50);
6642: hr_utility.set_location('Sec Name:'||r_sec.security_profile_name, 55);
6643: hr_utility.set_location('BG Id:'||r_sec.business_group_id, 60);
6644: --
6645: exception
6646: --
6647: -- Could not obtain the lock.
6656: := r_sec.security_profile_name;
6657: l_sec_cnt := l_sec_cnt + 1;
6658: --
6659: -- Keeping the information traced.
6660: hr_utility.trace('Cannot process security profile :');
6661: hr_utility.trace('Sec Prof Id: '||r_sec.security_profile_id);
6662: hr_utility.trace('Sec Name: '||r_sec.security_profile_name);
6663: hr_utility.trace('BG Id: '||r_sec.business_group_id);
6664: hr_utility.trace('BG Name: '||r_sec.business_group_name);
6657: l_sec_cnt := l_sec_cnt + 1;
6658: --
6659: -- Keeping the information traced.
6660: hr_utility.trace('Cannot process security profile :');
6661: hr_utility.trace('Sec Prof Id: '||r_sec.security_profile_id);
6662: hr_utility.trace('Sec Name: '||r_sec.security_profile_name);
6663: hr_utility.trace('BG Id: '||r_sec.business_group_id);
6664: hr_utility.trace('BG Name: '||r_sec.business_group_name);
6665: --
6658: --
6659: -- Keeping the information traced.
6660: hr_utility.trace('Cannot process security profile :');
6661: hr_utility.trace('Sec Prof Id: '||r_sec.security_profile_id);
6662: hr_utility.trace('Sec Name: '||r_sec.security_profile_name);
6663: hr_utility.trace('BG Id: '||r_sec.business_group_id);
6664: hr_utility.trace('BG Name: '||r_sec.business_group_name);
6665: --
6666: --
6659: -- Keeping the information traced.
6660: hr_utility.trace('Cannot process security profile :');
6661: hr_utility.trace('Sec Prof Id: '||r_sec.security_profile_id);
6662: hr_utility.trace('Sec Name: '||r_sec.security_profile_name);
6663: hr_utility.trace('BG Id: '||r_sec.business_group_id);
6664: hr_utility.trace('BG Name: '||r_sec.business_group_name);
6665: --
6666: --
6667: end;
6660: hr_utility.trace('Cannot process security profile :');
6661: hr_utility.trace('Sec Prof Id: '||r_sec.security_profile_id);
6662: hr_utility.trace('Sec Name: '||r_sec.security_profile_name);
6663: hr_utility.trace('BG Id: '||r_sec.business_group_id);
6664: hr_utility.trace('BG Name: '||r_sec.business_group_name);
6665: --
6666: --
6667: end;
6668: --
6667: end;
6668: --
6669: end loop;
6670: --
6671: hr_utility.set_location(l_proc, 70);
6672: -- Needs to format the report of failed records (if any) after the
6673: -- warning message.
6674: if sec_not_processed.count > 0 then
6675: --
6672: -- Needs to format the report of failed records (if any) after the
6673: -- warning message.
6674: if sec_not_processed.count > 0 then
6675: --
6676: hr_utility.set_location(l_proc, 80);
6677: -- Setting the message to get the translated message text
6678: fnd_message.set_name('PER', 'PER_449705_SEC_UPDATE_FAILED');
6679: --
6680: errbuf := null;
6692: end loop;
6693: --
6694: end if;
6695: --
6696: hr_utility.set_location('Leaving '||l_proc, 99);
6697: --
6698: exception
6699: --
6700: when e_irec_not_installed then
6705: errbuf := null;
6706: errbuf := nvl(fnd_message.get, 'PER_449706_IRC_NOT_INSTALLED');
6707: retcode := 1; -- Concurrent process finished with a warning.
6708: --
6709: hr_utility.set_location('Leaving '||l_proc, 99);
6710: --
6711: end submit_cand_sec_opt;
6712: --
6713: END pay_pyucslis_pkg;