eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_PYUCSLIS_PKG dependencies on FND_DSQL

Line 1190: fnd_dsql.set_cursor(l_cursor_id);

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);
1193: l_num := length(l_dsql_text);
1194:

Line 1192: l_dsql_text := fnd_dsql.get_text(FALSE);

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);
1193: l_num := length(l_dsql_text);
1194:
1195: /* -- use to print the final sql query
1196: while j < l_num +50

Line 1206: fnd_dsql.do_binds;

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);
1210: dbms_sql.close_cursor(l_cursor_id);

Line 1252: fnd_dsql.add_text(' and ( ');

1248:
1249: if l_restriction_flags is not null or
1250: length(l_restriction_flags) = 0
1251: then
1252: fnd_dsql.add_text(' and ( ');
1253: fnd_dsql.add_text(l_restriction_flags);
1254: fnd_dsql.add_text(' ) ');
1255: end if;
1256:

Line 1253: fnd_dsql.add_text(l_restriction_flags);

1249: if l_restriction_flags is not null or
1250: length(l_restriction_flags) = 0
1251: then
1252: fnd_dsql.add_text(' and ( ');
1253: fnd_dsql.add_text(l_restriction_flags);
1254: fnd_dsql.add_text(' ) ');
1255: end if;
1256:
1257: l_exclude_flags :='';

Line 1254: fnd_dsql.add_text(' ) ');

1250: length(l_restriction_flags) = 0
1251: then
1252: fnd_dsql.add_text(' and ( ');
1253: fnd_dsql.add_text(l_restriction_flags);
1254: fnd_dsql.add_text(' ) ');
1255: end if;
1256:
1257: l_exclude_flags :='';
1258: if (p_sec_rec.view_all_cwk_flag = 'X') then

Line 1283: fnd_dsql.add_text(' and ( ');

1279: --
1280: if l_exclude_flags is not null or
1281: length(l_exclude_flags) = 0
1282: then
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);

Line 1284: fnd_dsql.add_text(l_exclude_flags);

1280: if l_exclude_flags is not null or
1281: length(l_exclude_flags) = 0
1282: then
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;

Line 1285: fnd_dsql.add_text(' ) ');

1281: length(l_exclude_flags) = 0
1282: then
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:

Line 1308: fnd_dsql.init;

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);
1312: fnd_dsql.add_text(

Line 1312: fnd_dsql.add_text(

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,
1316: request_id,

Line 1322: fnd_dsql.add_text(

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,
1326: request_id,

Line 1333: fnd_dsql.add_text( ' SELECT DISTINCT ');

1329: program_update_date ) ');
1330: end if;
1331:
1332: -- Add the insert statement and binds
1333: fnd_dsql.add_text( ' SELECT DISTINCT ');
1334: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1335: fnd_dsql.add_text(' , assignment.person_id ');
1336: fnd_dsql.add_text(' ,');
1337: fnd_dsql.add_bind(nvl(p_request_id,''));

Line 1334: fnd_dsql.add_bind(p_sec_rec.security_profile_id);

1330: end if;
1331:
1332: -- Add the insert statement and binds
1333: fnd_dsql.add_text( ' SELECT DISTINCT ');
1334: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1335: fnd_dsql.add_text(' , assignment.person_id ');
1336: fnd_dsql.add_text(' ,');
1337: fnd_dsql.add_bind(nvl(p_request_id,''));
1338: fnd_dsql.add_text(' , ');

Line 1335: fnd_dsql.add_text(' , assignment.person_id ');

1331:
1332: -- Add the insert statement and binds
1333: fnd_dsql.add_text( ' SELECT DISTINCT ');
1334: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1335: fnd_dsql.add_text(' , assignment.person_id ');
1336: fnd_dsql.add_text(' ,');
1337: fnd_dsql.add_bind(nvl(p_request_id,''));
1338: fnd_dsql.add_text(' , ');
1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));

Line 1336: fnd_dsql.add_text(' ,');

1332: -- Add the insert statement and binds
1333: fnd_dsql.add_text( ' SELECT DISTINCT ');
1334: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1335: fnd_dsql.add_text(' , assignment.person_id ');
1336: fnd_dsql.add_text(' ,');
1337: fnd_dsql.add_bind(nvl(p_request_id,''));
1338: fnd_dsql.add_text(' , ');
1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1340: fnd_dsql.add_text(' , ');

Line 1337: fnd_dsql.add_bind(nvl(p_request_id,''));

1333: fnd_dsql.add_text( ' SELECT DISTINCT ');
1334: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1335: fnd_dsql.add_text(' , assignment.person_id ');
1336: fnd_dsql.add_text(' ,');
1337: fnd_dsql.add_bind(nvl(p_request_id,''));
1338: fnd_dsql.add_text(' , ');
1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1340: fnd_dsql.add_text(' , ');
1341: fnd_dsql.add_bind(nvl(p_program_id,''));

Line 1338: fnd_dsql.add_text(' , ');

1334: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1335: fnd_dsql.add_text(' , assignment.person_id ');
1336: fnd_dsql.add_text(' ,');
1337: fnd_dsql.add_bind(nvl(p_request_id,''));
1338: fnd_dsql.add_text(' , ');
1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1340: fnd_dsql.add_text(' , ');
1341: fnd_dsql.add_bind(nvl(p_program_id,''));
1342: fnd_dsql.add_text(' , ');

Line 1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));

1335: fnd_dsql.add_text(' , assignment.person_id ');
1336: fnd_dsql.add_text(' ,');
1337: fnd_dsql.add_bind(nvl(p_request_id,''));
1338: fnd_dsql.add_text(' , ');
1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1340: fnd_dsql.add_text(' , ');
1341: fnd_dsql.add_bind(nvl(p_program_id,''));
1342: fnd_dsql.add_text(' , ');
1343: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

Line 1340: fnd_dsql.add_text(' , ');

1336: fnd_dsql.add_text(' ,');
1337: fnd_dsql.add_bind(nvl(p_request_id,''));
1338: fnd_dsql.add_text(' , ');
1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1340: fnd_dsql.add_text(' , ');
1341: fnd_dsql.add_bind(nvl(p_program_id,''));
1342: fnd_dsql.add_text(' , ');
1343: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1344: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');

Line 1341: fnd_dsql.add_bind(nvl(p_program_id,''));

1337: fnd_dsql.add_bind(nvl(p_request_id,''));
1338: fnd_dsql.add_text(' , ');
1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1340: fnd_dsql.add_text(' , ');
1341: fnd_dsql.add_bind(nvl(p_program_id,''));
1342: fnd_dsql.add_text(' , ');
1343: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1344: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1345:

Line 1342: fnd_dsql.add_text(' , ');

1338: fnd_dsql.add_text(' , ');
1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1340: fnd_dsql.add_text(' , ');
1341: fnd_dsql.add_bind(nvl(p_program_id,''));
1342: fnd_dsql.add_text(' , ');
1343: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1344: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1345:
1346: /*-------- additional select clause ---------------*/

Line 1343: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

1339: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1340: fnd_dsql.add_text(' , ');
1341: fnd_dsql.add_bind(nvl(p_program_id,''));
1342: fnd_dsql.add_text(' , ');
1343: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1344: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1345:
1346: /*-------- additional select clause ---------------*/
1347:

Line 1344: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');

1340: fnd_dsql.add_text(' , ');
1341: fnd_dsql.add_bind(nvl(p_program_id,''));
1342: fnd_dsql.add_text(' , ');
1343: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1344: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1345:
1346: /*-------- additional select clause ---------------*/
1347:
1348: if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or

Line 1352: fnd_dsql.add_text(', per_all_people_f PERSON ');

1348: if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or
1349: (p_sec_rec.view_all_applicants_flag = 'N'
1350: and (p_sec_rec.view_all_employees_flag <>'Y'
1351: or p_sec_rec.view_all_cwk_flag <>'Y')) then
1352: fnd_dsql.add_text(', per_all_people_f PERSON ');
1353: End if;
1354: --Commented for debug- 8533491
1355: /* if p_sec_rec.view_all_organizations_flag='N' then
1356: Fnd_dsql.add_text(', per_organization_list ol ');

Line 1356: Fnd_dsql.add_text(', per_organization_list ol ');

1352: fnd_dsql.add_text(', per_all_people_f PERSON ');
1353: End if;
1354: --Commented for debug- 8533491
1355: /* if p_sec_rec.view_all_organizations_flag='N' then
1356: Fnd_dsql.add_text(', per_organization_list ol ');
1357: end if;*/
1358:
1359: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1360: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');

Line 1360: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');

1356: Fnd_dsql.add_text(', per_organization_list ol ');
1357: end if;*/
1358:
1359: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1360: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');
1361: end if;
1362: --Commented for debug- 8533491
1363: /* if p_sec_rec.view_all_positions_flag='N' then
1364: fnd_dsql.add_text(', per_position_list pl ');

Line 1364: fnd_dsql.add_text(', per_position_list pl ');

1360: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');
1361: end if;
1362: --Commented for debug- 8533491
1363: /* if p_sec_rec.view_all_positions_flag='N' then
1364: fnd_dsql.add_text(', per_position_list pl ');
1365: end if;
1366:
1367: if p_sec_rec.view_all_payrolls_flag='N' then
1368: fnd_dsql.add_text( ' , pay_payroll_list ppl ');

Line 1368: fnd_dsql.add_text( ' , pay_payroll_list ppl ');

1364: fnd_dsql.add_text(', per_position_list pl ');
1365: end if;
1366:
1367: if p_sec_rec.view_all_payrolls_flag='N' then
1368: fnd_dsql.add_text( ' , pay_payroll_list ppl ');
1369: end if;*/
1370:
1371: if p_sec_rec.view_all_organizations_flag='Y' then
1372: null;

Line 1377: fnd_dsql.add_text(' Where ');

1373: end if;
1374: /*------------------ end additional select clause -----------------*/
1375:
1376: /*-------------- start where clause -------------------*/
1377: fnd_dsql.add_text(' Where ');
1378: fnd_dsql.add_text(' ASSIGNMENT.business_group_id = ');
1379:
1380: if p_sec_rec.business_group_id is null then
1381: fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),

Line 1378: fnd_dsql.add_text(' ASSIGNMENT.business_group_id = ');

1374: /*------------------ end additional select clause -----------------*/
1375:
1376: /*-------------- start where clause -------------------*/
1377: fnd_dsql.add_text(' Where ');
1378: fnd_dsql.add_text(' ASSIGNMENT.business_group_id = ');
1379:
1380: if p_sec_rec.business_group_id is null then
1381: fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),
1382: 'ASSIGNMENT.business_group_id')||'), ASSIGNMENT.business_group_id) ');

Line 1381: fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),

1377: fnd_dsql.add_text(' Where ');
1378: fnd_dsql.add_text(' ASSIGNMENT.business_group_id = ');
1379:
1380: if p_sec_rec.business_group_id is null then
1381: fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),
1382: 'ASSIGNMENT.business_group_id')||'), ASSIGNMENT.business_group_id) ');
1383: else
1384: fnd_dsql.add_bind(p_sec_rec.business_group_id);
1385: end if;

Line 1384: fnd_dsql.add_bind(p_sec_rec.business_group_id);

1380: if p_sec_rec.business_group_id is null then
1381: fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),
1382: 'ASSIGNMENT.business_group_id')||'), ASSIGNMENT.business_group_id) ');
1383: else
1384: fnd_dsql.add_bind(p_sec_rec.business_group_id);
1385: end if;
1386:
1387: if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or
1388: (p_sec_rec.view_all_applicants_flag = 'N'

Line 1392: fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');

1388: (p_sec_rec.view_all_applicants_flag = 'N'
1389: and (p_sec_rec.view_all_employees_flag <>'Y'
1390: or p_sec_rec.view_all_cwk_flag <>'Y')) then
1391:
1392: fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1393: fnd_dsql.add_text(' ( '); -- 5214715
1394: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1395: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1396: /* Got a fresh future person */

Line 1393: fnd_dsql.add_text(' ( '); -- 5214715

1389: and (p_sec_rec.view_all_employees_flag <>'Y'
1390: or p_sec_rec.view_all_cwk_flag <>'Y')) then
1391:
1392: fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1393: fnd_dsql.add_text(' ( '); -- 5214715
1394: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1395: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1396: /* Got a fresh future person */
1397: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');

Line 1394: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

1390: or p_sec_rec.view_all_cwk_flag <>'Y')) then
1391:
1392: fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1393: fnd_dsql.add_text(' ( '); -- 5214715
1394: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1395: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1396: /* Got a fresh future person */
1397: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1398: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

Line 1395: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');

1391:
1392: fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1393: fnd_dsql.add_text(' ( '); -- 5214715
1394: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1395: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1396: /* Got a fresh future person */
1397: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1398: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1399: fnd_dsql.add_text(' AND NOT EXISTS

Line 1397: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');

1393: fnd_dsql.add_text(' ( '); -- 5214715
1394: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1395: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1396: /* Got a fresh future person */
1397: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1398: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1399: fnd_dsql.add_text(' AND NOT EXISTS
1400: (SELECT NULL
1401: FROM per_all_people_f papf1

Line 1398: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

1394: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1395: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1396: /* Got a fresh future person */
1397: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1398: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1399: fnd_dsql.add_text(' AND NOT EXISTS
1400: (SELECT NULL
1401: FROM per_all_people_f papf1
1402: WHERE papf1.person_id = PERSON.person_id

Line 1399: fnd_dsql.add_text(' AND NOT EXISTS

1395: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1396: /* Got a fresh future person */
1397: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1398: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1399: fnd_dsql.add_text(' AND NOT EXISTS
1400: (SELECT NULL
1401: FROM per_all_people_f papf1
1402: WHERE papf1.person_id = PERSON.person_id
1403: AND papf1.effective_start_date < PERSON.effective_start_date)) ');

Line 1405: fnd_dsql.add_text(' ) '); -- 5214715

1401: FROM per_all_people_f papf1
1402: WHERE papf1.person_id = PERSON.person_id
1403: AND papf1.effective_start_date < PERSON.effective_start_date)) ');
1404:
1405: fnd_dsql.add_text(' ) '); -- 5214715
1406: End if;
1407:
1408: if p_sec_rec.view_all_organizations_flag='N' then
1409: --Modified for debug- 8533491

Line 1410: /* fnd_dsql.add_text(' and ol.security_profile_id = ');

1406: End if;
1407:
1408: if p_sec_rec.view_all_organizations_flag='N' then
1409: --Modified for debug- 8533491
1410: /* fnd_dsql.add_text(' and ol.security_profile_id = ');
1411: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1412: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');*/
1413:
1414: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_organization_list) */ 1 FROM per_organization_list');

Line 1411: fnd_dsql.add_bind(p_sec_rec.security_profile_id);

1407:
1408: if p_sec_rec.view_all_organizations_flag='N' then
1409: --Modified for debug- 8533491
1410: /* fnd_dsql.add_text(' and ol.security_profile_id = ');
1411: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1412: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');*/
1413:
1414: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_organization_list) */ 1 FROM per_organization_list');
1415: fnd_dsql.add_text(' WHERE security_profile_id = ');

Line 1412: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');*/

1408: if p_sec_rec.view_all_organizations_flag='N' then
1409: --Modified for debug- 8533491
1410: /* fnd_dsql.add_text(' and ol.security_profile_id = ');
1411: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1412: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');*/
1413:
1414: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_organization_list) */ 1 FROM per_organization_list');
1415: fnd_dsql.add_text(' WHERE security_profile_id = ');
1416: fnd_dsql.add_bind(p_sec_rec.security_profile_id);

Line 1414: fnd_dsql.add_text(' and EXISTS ( SELECT /+ use_nl(per_organization_list) / 1 FROM per_organization_list');

1410: /* fnd_dsql.add_text(' and ol.security_profile_id = ');
1411: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1412: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');*/
1413:
1414: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_organization_list) */ 1 FROM per_organization_list');
1415: fnd_dsql.add_text(' WHERE security_profile_id = ');
1416: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1417: fnd_dsql.add_text(' AND organization_id=ASSIGNMENT.organization_id)');
1418: end if;

Line 1415: fnd_dsql.add_text(' WHERE security_profile_id = ');

1411: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1412: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');*/
1413:
1414: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_organization_list) */ 1 FROM per_organization_list');
1415: fnd_dsql.add_text(' WHERE security_profile_id = ');
1416: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1417: fnd_dsql.add_text(' AND organization_id=ASSIGNMENT.organization_id)');
1418: end if;
1419:

Line 1416: fnd_dsql.add_bind(p_sec_rec.security_profile_id);

1412: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');*/
1413:
1414: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_organization_list) */ 1 FROM per_organization_list');
1415: fnd_dsql.add_text(' WHERE security_profile_id = ');
1416: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1417: fnd_dsql.add_text(' AND organization_id=ASSIGNMENT.organization_id)');
1418: end if;
1419:
1420: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then

Line 1417: fnd_dsql.add_text(' AND organization_id=ASSIGNMENT.organization_id)');

1413:
1414: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_organization_list) */ 1 FROM per_organization_list');
1415: fnd_dsql.add_text(' WHERE security_profile_id = ');
1416: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1417: fnd_dsql.add_text(' AND organization_id=ASSIGNMENT.organization_id)');
1418: end if;
1419:
1420: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1421: fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');

Line 1421: fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');

1417: fnd_dsql.add_text(' AND organization_id=ASSIGNMENT.organization_id)');
1418: end if;
1419:
1420: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1421: fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');
1422: fnd_dsql.add_bind (to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1423: fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');
1424: end if;
1425:

Line 1422: fnd_dsql.add_bind (to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

1418: end if;
1419:
1420: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1421: fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');
1422: fnd_dsql.add_bind (to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1423: fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');
1424: end if;
1425:
1426: if p_sec_rec.view_all_positions_flag='N' then

Line 1423: fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');

1419:
1420: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1421: fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');
1422: fnd_dsql.add_bind (to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1423: fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');
1424: end if;
1425:
1426: if p_sec_rec.view_all_positions_flag='N' then
1427: --Modified for debug-8533491

Line 1428: /*fnd_dsql.add_text(' and pl.security_profile_id = ');

1424: end if;
1425:
1426: if p_sec_rec.view_all_positions_flag='N' then
1427: --Modified for debug-8533491
1428: /*fnd_dsql.add_text(' and pl.security_profile_id = ');
1429: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1430: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');*/
1431:
1432: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_position_list) */ 1 FROM per_position_list ');

Line 1429: fnd_dsql.add_bind(p_sec_rec.security_profile_id );

1425:
1426: if p_sec_rec.view_all_positions_flag='N' then
1427: --Modified for debug-8533491
1428: /*fnd_dsql.add_text(' and pl.security_profile_id = ');
1429: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1430: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');*/
1431:
1432: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_position_list) */ 1 FROM per_position_list ');
1433: fnd_dsql.add_text(' WHERE security_profile_id = ');

Line 1430: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');*/

1426: if p_sec_rec.view_all_positions_flag='N' then
1427: --Modified for debug-8533491
1428: /*fnd_dsql.add_text(' and pl.security_profile_id = ');
1429: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1430: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');*/
1431:
1432: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_position_list) */ 1 FROM per_position_list ');
1433: fnd_dsql.add_text(' WHERE security_profile_id = ');
1434: fnd_dsql.add_bind(p_sec_rec.security_profile_id );

Line 1432: fnd_dsql.add_text(' and EXISTS ( SELECT /+ use_nl(per_position_list) / 1 FROM per_position_list ');

1428: /*fnd_dsql.add_text(' and pl.security_profile_id = ');
1429: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1430: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');*/
1431:
1432: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_position_list) */ 1 FROM per_position_list ');
1433: fnd_dsql.add_text(' WHERE security_profile_id = ');
1434: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1435: fnd_dsql.add_text(' and (position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null ))');
1436:

Line 1433: fnd_dsql.add_text(' WHERE security_profile_id = ');

1429: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1430: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');*/
1431:
1432: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_position_list) */ 1 FROM per_position_list ');
1433: fnd_dsql.add_text(' WHERE security_profile_id = ');
1434: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1435: fnd_dsql.add_text(' and (position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null ))');
1436:
1437: end if;

Line 1434: fnd_dsql.add_bind(p_sec_rec.security_profile_id );

1430: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');*/
1431:
1432: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_position_list) */ 1 FROM per_position_list ');
1433: fnd_dsql.add_text(' WHERE security_profile_id = ');
1434: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1435: fnd_dsql.add_text(' and (position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null ))');
1436:
1437: end if;
1438:

Line 1435: fnd_dsql.add_text(' and (position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null ))');

1431:
1432: fnd_dsql.add_text(' and EXISTS ( SELECT /*+ use_nl(per_position_list) */ 1 FROM per_position_list ');
1433: fnd_dsql.add_text(' WHERE security_profile_id = ');
1434: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1435: fnd_dsql.add_text(' and (position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null ))');
1436:
1437: end if;
1438:
1439: if p_sec_rec.view_all_payrolls_flag='N' then

Line 1441: /* fnd_dsql.add_text(' and (( ppl.security_profile_id = ');

1437: end if;
1438:
1439: if p_sec_rec.view_all_payrolls_flag='N' then
1440: --Modified for debug- 8533491.
1441: /* fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1442: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1443: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1444: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');*/
1445:

Line 1442: fnd_dsql.add_bind(p_sec_rec.security_profile_id);

1438:
1439: if p_sec_rec.view_all_payrolls_flag='N' then
1440: --Modified for debug- 8533491.
1441: /* fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1442: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1443: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1444: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');*/
1445:
1446: fnd_dsql.add_text(' AND EXISTS ( SELECT /*+ use_nl(pay_payroll_list) */ 1 FROM pay_payroll_list');

Line 1443: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');

1439: if p_sec_rec.view_all_payrolls_flag='N' then
1440: --Modified for debug- 8533491.
1441: /* fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1442: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1443: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1444: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');*/
1445:
1446: fnd_dsql.add_text(' AND EXISTS ( SELECT /*+ use_nl(pay_payroll_list) */ 1 FROM pay_payroll_list');
1447: fnd_dsql.add_text(' WHERE (security_profile_id = ');

Line 1444: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');*/

1440: --Modified for debug- 8533491.
1441: /* fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1442: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1443: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1444: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');*/
1445:
1446: fnd_dsql.add_text(' AND EXISTS ( SELECT /*+ use_nl(pay_payroll_list) */ 1 FROM pay_payroll_list');
1447: fnd_dsql.add_text(' WHERE (security_profile_id = ');
1448: fnd_dsql.add_bind(p_sec_rec.security_profile_id);

Line 1446: fnd_dsql.add_text(' AND EXISTS ( SELECT /+ use_nl(pay_payroll_list) / 1 FROM pay_payroll_list');

1442: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1443: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1444: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');*/
1445:
1446: fnd_dsql.add_text(' AND EXISTS ( SELECT /*+ use_nl(pay_payroll_list) */ 1 FROM pay_payroll_list');
1447: fnd_dsql.add_text(' WHERE (security_profile_id = ');
1448: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1449: fnd_dsql.add_text(' AND payroll_id=ASSIGNMENT.payroll_id ))');
1450: -- fnd_dsql.add_text(' OR ASSIGNMENT.payroll_id is null)');

Line 1447: fnd_dsql.add_text(' WHERE (security_profile_id = ');

1443: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1444: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');*/
1445:
1446: fnd_dsql.add_text(' AND EXISTS ( SELECT /*+ use_nl(pay_payroll_list) */ 1 FROM pay_payroll_list');
1447: fnd_dsql.add_text(' WHERE (security_profile_id = ');
1448: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1449: fnd_dsql.add_text(' AND payroll_id=ASSIGNMENT.payroll_id ))');
1450: -- fnd_dsql.add_text(' OR ASSIGNMENT.payroll_id is null)');
1451: end if;

Line 1448: fnd_dsql.add_bind(p_sec_rec.security_profile_id);

1444: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');*/
1445:
1446: fnd_dsql.add_text(' AND EXISTS ( SELECT /*+ use_nl(pay_payroll_list) */ 1 FROM pay_payroll_list');
1447: fnd_dsql.add_text(' WHERE (security_profile_id = ');
1448: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1449: fnd_dsql.add_text(' AND payroll_id=ASSIGNMENT.payroll_id ))');
1450: -- fnd_dsql.add_text(' OR ASSIGNMENT.payroll_id is null)');
1451: end if;
1452:

Line 1449: fnd_dsql.add_text(' AND payroll_id=ASSIGNMENT.payroll_id ))');

1445:
1446: fnd_dsql.add_text(' AND EXISTS ( SELECT /*+ use_nl(pay_payroll_list) */ 1 FROM pay_payroll_list');
1447: fnd_dsql.add_text(' WHERE (security_profile_id = ');
1448: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1449: fnd_dsql.add_text(' AND payroll_id=ASSIGNMENT.payroll_id ))');
1450: -- fnd_dsql.add_text(' OR ASSIGNMENT.payroll_id is null)');
1451: end if;
1452:
1453: if p_sec_rec.view_all_organizations_flag='Y' then

Line 1450: -- fnd_dsql.add_text(' OR ASSIGNMENT.payroll_id is null)');

1446: fnd_dsql.add_text(' AND EXISTS ( SELECT /*+ use_nl(pay_payroll_list) */ 1 FROM pay_payroll_list');
1447: fnd_dsql.add_text(' WHERE (security_profile_id = ');
1448: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1449: fnd_dsql.add_text(' AND payroll_id=ASSIGNMENT.payroll_id ))');
1450: -- fnd_dsql.add_text(' OR ASSIGNMENT.payroll_id is null)');
1451: end if;
1452:
1453: if p_sec_rec.view_all_organizations_flag='Y' then
1454: null;

Line 1490: fnd_dsql.add_text(' and ( ');

1486: add_comm_str( p_sec_rec => sec_rec );
1487:
1488: /*--------- end for l_restriction_flags and l_exclude_flags----------------*/
1489:
1490: fnd_dsql.add_text(' and ( ');
1491: fnd_dsql.add_text(' ( '); -- 5214715
1492: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1493: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1494: or ( ASSIGNMENT.effective_start_date>= ');

Line 1491: fnd_dsql.add_text(' ( '); -- 5214715

1487:
1488: /*--------- end for l_restriction_flags and l_exclude_flags----------------*/
1489:
1490: fnd_dsql.add_text(' and ( ');
1491: fnd_dsql.add_text(' ( '); -- 5214715
1492: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1493: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1494: or ( ASSIGNMENT.effective_start_date>= ');
1495: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

Line 1492: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

1488: /*--------- end for l_restriction_flags and l_exclude_flags----------------*/
1489:
1490: fnd_dsql.add_text(' and ( ');
1491: fnd_dsql.add_text(' ( '); -- 5214715
1492: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1493: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1494: or ( ASSIGNMENT.effective_start_date>= ');
1495: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1496: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL

Line 1493: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)

1489:
1490: fnd_dsql.add_text(' and ( ');
1491: fnd_dsql.add_text(' ( '); -- 5214715
1492: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1493: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1494: or ( ASSIGNMENT.effective_start_date>= ');
1495: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1496: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1497: FROM per_all_assignments_f pos1

Line 1495: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

1491: fnd_dsql.add_text(' ( '); -- 5214715
1492: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1493: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1494: or ( ASSIGNMENT.effective_start_date>= ');
1495: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1496: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1497: FROM per_all_assignments_f pos1
1498: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1499: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

Line 1496: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL

1492: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1493: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1494: or ( ASSIGNMENT.effective_start_date>= ');
1495: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1496: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1497: FROM per_all_assignments_f pos1
1498: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1499: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1500: fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');

Line 1499: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

1495: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1496: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1497: FROM per_all_assignments_f pos1
1498: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1499: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1500: fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');
1501: fnd_dsql.add_text('
1502: AND NOT EXISTS
1503: (SELECT NULL

Line 1500: fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');

1496: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1497: FROM per_all_assignments_f pos1
1498: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1499: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1500: fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');
1501: fnd_dsql.add_text('
1502: AND NOT EXISTS
1503: (SELECT NULL
1504: FROM per_all_assignments_f pos1

Line 1501: fnd_dsql.add_text('

1497: FROM per_all_assignments_f pos1
1498: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1499: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1500: fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');
1501: fnd_dsql.add_text('
1502: AND NOT EXISTS
1503: (SELECT NULL
1504: FROM per_all_assignments_f pos1
1505: WHERE pos1.person_id = ASSIGNMENT.person_id

Line 1516: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

1512: ASSIGNMENT.period_of_placement_date_start))
1513: AND pos1.effective_start_date< ASSIGNMENT.effective_start_date)
1514: or (ASSIGNMENT.effective_end_date < ');
1515:
1516: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1517: -- added and clause for bug 5168364
1518: fnd_dsql.add_text(' and assignment.effective_end_date = (select max(effective_end_date)
1519: from per_all_assignments_f asg
1520: where asg.person_id = assignment.person_id

Line 1518: fnd_dsql.add_text(' and assignment.effective_end_date = (select max(effective_end_date)

1514: or (ASSIGNMENT.effective_end_date < ');
1515:
1516: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1517: -- added and clause for bug 5168364
1518: fnd_dsql.add_text(' and assignment.effective_end_date = (select max(effective_end_date)
1519: from per_all_assignments_f asg
1520: where asg.person_id = assignment.person_id
1521: and asg.assignment_type in(''A'',''C'',''E'')
1522: ) ');

Line 1523: fnd_dsql.add_text(' AND NOT EXISTS

1519: from per_all_assignments_f asg
1520: where asg.person_id = assignment.person_id
1521: and asg.assignment_type in(''A'',''C'',''E'')
1522: ) ');
1523: fnd_dsql.add_text(' AND NOT EXISTS
1524: (SELECT NULL
1525: FROM per_all_assignments_f papf
1526: WHERE papf.person_Id = ASSIGNMENT.person_id
1527: AND papf.assignment_type in(''A'',''C'',''E'')

Line 1529: fnd_dsql.add_bind( to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

1525: FROM per_all_assignments_f papf
1526: WHERE papf.person_Id = ASSIGNMENT.person_id
1527: AND papf.assignment_type in(''A'',''C'',''E'')
1528: AND papf.effective_end_date >= ');
1529: fnd_dsql.add_bind( to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1530: fnd_dsql.add_text(' ))))'); -- 5214715
1531:
1532: -- Start changes for bug 13504049
1533: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then

Line 1530: fnd_dsql.add_text(' ))))'); -- 5214715

1526: WHERE papf.person_Id = ASSIGNMENT.person_id
1527: AND papf.assignment_type in(''A'',''C'',''E'')
1528: AND papf.effective_end_date >= ');
1529: fnd_dsql.add_bind( to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1530: fnd_dsql.add_text(' ))))'); -- 5214715
1531:
1532: -- Start changes for bug 13504049
1533: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1534: fnd_dsql.add_text(' and not exists(select 1

Line 1534: fnd_dsql.add_text(' and not exists(select 1

1530: fnd_dsql.add_text(' ))))'); -- 5214715
1531:
1532: -- Start changes for bug 13504049
1533: if g_use_temp_table = 'Y' and g_generation_scope = 'SINGLE_PROF' and g_static_list_profile = 'N' then
1534: fnd_dsql.add_text(' and not exists(select 1
1535: from per_person_list_stage ppl
1536: where ppl.security_profile_id = ');
1537: else
1538:

Line 1539: fnd_dsql.add_text(' and not exists(select 1

1535: from per_person_list_stage ppl
1536: where ppl.security_profile_id = ');
1537: else
1538:
1539: fnd_dsql.add_text(' and not exists(select 1
1540: from per_person_list ppl
1541: where ppl.security_profile_id = ');
1542: end if;
1543: -- End changes for bug 13504049

Line 1544: fnd_dsql.add_bind(sec_rec.security_profile_id);

1540: from per_person_list ppl
1541: where ppl.security_profile_id = ');
1542: end if;
1543: -- End changes for bug 13504049
1544: fnd_dsql.add_bind(sec_rec.security_profile_id);
1545: fnd_dsql.add_text(' and ppl.person_id = assignment.person_id
1546: and ppl.granted_user_id is null)');
1547:
1548: -- if the custom sql flag is set and the restriction text is not empty

Line 1545: fnd_dsql.add_text(' and ppl.person_id = assignment.person_id

1541: where ppl.security_profile_id = ');
1542: end if;
1543: -- End changes for bug 13504049
1544: fnd_dsql.add_bind(sec_rec.security_profile_id);
1545: fnd_dsql.add_text(' and ppl.person_id = assignment.person_id
1546: and ppl.granted_user_id is null)');
1547:
1548: -- if the custom sql flag is set and the restriction text is not empty
1549: -- (>2 chars) then append the custom sql to the end of the statement

Line 1552: fnd_dsql.add_text(' and ');

1548: -- if the custom sql flag is set and the restriction text is not empty
1549: -- (>2 chars) then append the custom sql to the end of the statement
1550:
1551: if sec_rec.custom_restriction_flag='Y' and length(sec_rec.restriction_text)>2 then
1552: fnd_dsql.add_text(' and ');
1553: fnd_dsql.add_text(sec_rec.restriction_text);
1554: end if;
1555:
1556: if g_debug then

Line 1553: fnd_dsql.add_text(sec_rec.restriction_text);

1549: -- (>2 chars) then append the custom sql to the end of the statement
1550:
1551: if sec_rec.custom_restriction_flag='Y' and length(sec_rec.restriction_text)>2 then
1552: fnd_dsql.add_text(' and ');
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)));