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 990: fnd_dsql.set_cursor(l_cursor_id);

986: i number(10):=50;
987: BEGIN
988: hr_utility.set_location('Entering execute_statement',10);
989: l_cursor_id := dbms_sql.open_cursor;
990: fnd_dsql.set_cursor(l_cursor_id);
991:
992: l_dsql_text := fnd_dsql.get_text(FALSE);
993: l_num := length(l_dsql_text);
994:

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

988: hr_utility.set_location('Entering execute_statement',10);
989: l_cursor_id := dbms_sql.open_cursor;
990: fnd_dsql.set_cursor(l_cursor_id);
991:
992: l_dsql_text := fnd_dsql.get_text(FALSE);
993: l_num := length(l_dsql_text);
994:
995: /* -- use to print the final sql query
996: while j < l_num +50

Line 1006: fnd_dsql.do_binds;

1002: end loop;
1003: */
1004: dbms_sql.parse(l_cursor_id, l_dsql_text, dbms_sql.native);
1005: hr_utility.set_location('after parse',10);
1006: fnd_dsql.do_binds;
1007: hr_utility.set_location('after bind',10);
1008: l_num_of_rows := dbms_sql.execute(l_cursor_id);
1009: hr_utility.set_location('after execuate ',10);
1010: dbms_sql.close_cursor(l_cursor_id);

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

1048:
1049: if l_restriction_flags is not null or
1050: length(l_restriction_flags) = 0
1051: then
1052: fnd_dsql.add_text(' and ( ');
1053: fnd_dsql.add_text(l_restriction_flags);
1054: fnd_dsql.add_text(' ) ');
1055: end if;
1056:

Line 1053: fnd_dsql.add_text(l_restriction_flags);

1049: if l_restriction_flags is not null or
1050: length(l_restriction_flags) = 0
1051: then
1052: fnd_dsql.add_text(' and ( ');
1053: fnd_dsql.add_text(l_restriction_flags);
1054: fnd_dsql.add_text(' ) ');
1055: end if;
1056:
1057: l_exclude_flags :='';

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

1050: length(l_restriction_flags) = 0
1051: then
1052: fnd_dsql.add_text(' and ( ');
1053: fnd_dsql.add_text(l_restriction_flags);
1054: fnd_dsql.add_text(' ) ');
1055: end if;
1056:
1057: l_exclude_flags :='';
1058: if (p_sec_rec.view_all_cwk_flag = 'X') then

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

1079: --
1080: if l_exclude_flags is not null or
1081: length(l_exclude_flags) = 0
1082: then
1083: fnd_dsql.add_text(' and ( ');
1084: fnd_dsql.add_text(l_exclude_flags);
1085: fnd_dsql.add_text(' ) ');
1086: end if;
1087: hr_utility.set_location('Leaveing add_comm_str',10);

Line 1084: fnd_dsql.add_text(l_exclude_flags);

1080: if l_exclude_flags is not null or
1081: length(l_exclude_flags) = 0
1082: then
1083: fnd_dsql.add_text(' and ( ');
1084: fnd_dsql.add_text(l_exclude_flags);
1085: fnd_dsql.add_text(' ) ');
1086: end if;
1087: hr_utility.set_location('Leaveing add_comm_str',10);
1088: END add_comm_str;

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

1081: length(l_exclude_flags) = 0
1082: then
1083: fnd_dsql.add_text(' and ( ');
1084: fnd_dsql.add_text(l_exclude_flags);
1085: fnd_dsql.add_text(' ) ');
1086: end if;
1087: hr_utility.set_location('Leaveing add_comm_str',10);
1088: END add_comm_str;
1089:

Line 1108: fnd_dsql.init;

1104: )
1105: AS
1106: BEGIN
1107: hr_utility.set_location('Entering init_statement',10);
1108: fnd_dsql.init;
1109:
1110: fnd_dsql.add_text(
1111: 'INSERT into per_person_list
1112: (security_profile_id,

Line 1110: fnd_dsql.add_text(

1106: BEGIN
1107: hr_utility.set_location('Entering init_statement',10);
1108: fnd_dsql.init;
1109:
1110: fnd_dsql.add_text(
1111: 'INSERT into per_person_list
1112: (security_profile_id,
1113: person_id,
1114: request_id,

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

1116: program_id,
1117: program_update_date ) ');
1118:
1119: -- Add the insert statement and binds
1120: fnd_dsql.add_text( ' SELECT DISTINCT ');
1121: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1122: fnd_dsql.add_text(' , assignment.person_id ');
1123: fnd_dsql.add_text(' ,');
1124: fnd_dsql.add_bind(nvl(p_request_id,''));

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

1117: program_update_date ) ');
1118:
1119: -- Add the insert statement and binds
1120: fnd_dsql.add_text( ' SELECT DISTINCT ');
1121: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1122: fnd_dsql.add_text(' , assignment.person_id ');
1123: fnd_dsql.add_text(' ,');
1124: fnd_dsql.add_bind(nvl(p_request_id,''));
1125: fnd_dsql.add_text(' , ');

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

1118:
1119: -- Add the insert statement and binds
1120: fnd_dsql.add_text( ' SELECT DISTINCT ');
1121: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1122: fnd_dsql.add_text(' , assignment.person_id ');
1123: fnd_dsql.add_text(' ,');
1124: fnd_dsql.add_bind(nvl(p_request_id,''));
1125: fnd_dsql.add_text(' , ');
1126: fnd_dsql.add_bind(nvl(p_program_application_id,''));

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

1119: -- Add the insert statement and binds
1120: fnd_dsql.add_text( ' SELECT DISTINCT ');
1121: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1122: fnd_dsql.add_text(' , assignment.person_id ');
1123: fnd_dsql.add_text(' ,');
1124: fnd_dsql.add_bind(nvl(p_request_id,''));
1125: fnd_dsql.add_text(' , ');
1126: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1127: fnd_dsql.add_text(' , ');

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

1120: fnd_dsql.add_text( ' SELECT DISTINCT ');
1121: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1122: fnd_dsql.add_text(' , assignment.person_id ');
1123: fnd_dsql.add_text(' ,');
1124: fnd_dsql.add_bind(nvl(p_request_id,''));
1125: fnd_dsql.add_text(' , ');
1126: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1127: fnd_dsql.add_text(' , ');
1128: fnd_dsql.add_bind(nvl(p_program_id,''));

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

1121: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1122: fnd_dsql.add_text(' , assignment.person_id ');
1123: fnd_dsql.add_text(' ,');
1124: fnd_dsql.add_bind(nvl(p_request_id,''));
1125: fnd_dsql.add_text(' , ');
1126: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1127: fnd_dsql.add_text(' , ');
1128: fnd_dsql.add_bind(nvl(p_program_id,''));
1129: fnd_dsql.add_text(' , ');

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

1122: fnd_dsql.add_text(' , assignment.person_id ');
1123: fnd_dsql.add_text(' ,');
1124: fnd_dsql.add_bind(nvl(p_request_id,''));
1125: fnd_dsql.add_text(' , ');
1126: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1127: fnd_dsql.add_text(' , ');
1128: fnd_dsql.add_bind(nvl(p_program_id,''));
1129: fnd_dsql.add_text(' , ');
1130: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

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

1123: fnd_dsql.add_text(' ,');
1124: fnd_dsql.add_bind(nvl(p_request_id,''));
1125: fnd_dsql.add_text(' , ');
1126: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1127: fnd_dsql.add_text(' , ');
1128: fnd_dsql.add_bind(nvl(p_program_id,''));
1129: fnd_dsql.add_text(' , ');
1130: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1131: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');

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

1124: fnd_dsql.add_bind(nvl(p_request_id,''));
1125: fnd_dsql.add_text(' , ');
1126: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1127: fnd_dsql.add_text(' , ');
1128: fnd_dsql.add_bind(nvl(p_program_id,''));
1129: fnd_dsql.add_text(' , ');
1130: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1131: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1132:

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

1125: fnd_dsql.add_text(' , ');
1126: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1127: fnd_dsql.add_text(' , ');
1128: fnd_dsql.add_bind(nvl(p_program_id,''));
1129: fnd_dsql.add_text(' , ');
1130: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1131: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1132:
1133: /*-------- additional select clause ---------------*/

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

1126: fnd_dsql.add_bind(nvl(p_program_application_id,''));
1127: fnd_dsql.add_text(' , ');
1128: fnd_dsql.add_bind(nvl(p_program_id,''));
1129: fnd_dsql.add_text(' , ');
1130: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1131: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1132:
1133: /*-------- additional select clause ---------------*/
1134:

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

1127: fnd_dsql.add_text(' , ');
1128: fnd_dsql.add_bind(nvl(p_program_id,''));
1129: fnd_dsql.add_text(' , ');
1130: fnd_dsql.add_bind(to_date(to_char(p_update_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1131: fnd_dsql.add_text(' FROM per_all_assignments_f ASSIGNMENT ');
1132:
1133: /*-------- additional select clause ---------------*/
1134:
1135: if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or

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

1135: if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or
1136: (p_sec_rec.view_all_applicants_flag = 'N'
1137: and (p_sec_rec.view_all_employees_flag <>'Y'
1138: or p_sec_rec.view_all_cwk_flag <>'Y')) then
1139: fnd_dsql.add_text(', per_all_people_f PERSON ');
1140: End if;
1141:
1142: if p_sec_rec.view_all_organizations_flag='N' then
1143: Fnd_dsql.add_text(', per_organization_list ol ');

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

1139: fnd_dsql.add_text(', per_all_people_f PERSON ');
1140: End if;
1141:
1142: if p_sec_rec.view_all_organizations_flag='N' then
1143: Fnd_dsql.add_text(', per_organization_list ol ');
1144: end if;
1145:
1146: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1147: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');

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

1143: Fnd_dsql.add_text(', per_organization_list ol ');
1144: end if;
1145:
1146: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1147: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');
1148: end if;
1149:
1150: if p_sec_rec.view_all_positions_flag='N' then
1151: fnd_dsql.add_text(', per_position_list pl ');

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

1147: Fnd_dsql.add_text(', per_person_type_usages_f PERSON_TYPE ');
1148: end if;
1149:
1150: if p_sec_rec.view_all_positions_flag='N' then
1151: fnd_dsql.add_text(', per_position_list pl ');
1152: end if;
1153:
1154: if p_sec_rec.view_all_payrolls_flag='N' then
1155: fnd_dsql.add_text( ' , pay_payroll_list ppl ');

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

1151: fnd_dsql.add_text(', per_position_list pl ');
1152: end if;
1153:
1154: if p_sec_rec.view_all_payrolls_flag='N' then
1155: fnd_dsql.add_text( ' , pay_payroll_list ppl ');
1156: end if;
1157:
1158: if p_sec_rec.view_all_organizations_flag='Y' then
1159: null;

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

1160: end if;
1161: /*------------------ end additional select clause -----------------*/
1162:
1163: /*-------------- start where clause -------------------*/
1164: fnd_dsql.add_text(' Where ');
1165: fnd_dsql.add_text(' ASSIGNMENT.business_group_id = ');
1166:
1167: if p_sec_rec.business_group_id is null then
1168: fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),

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

1161: /*------------------ end additional select clause -----------------*/
1162:
1163: /*-------------- start where clause -------------------*/
1164: fnd_dsql.add_text(' Where ');
1165: fnd_dsql.add_text(' ASSIGNMENT.business_group_id = ');
1166:
1167: if p_sec_rec.business_group_id is null then
1168: fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),
1169: 'ASSIGNMENT.business_group_id')||'), ASSIGNMENT.business_group_id) ');

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

1164: fnd_dsql.add_text(' Where ');
1165: fnd_dsql.add_text(' ASSIGNMENT.business_group_id = ');
1166:
1167: if p_sec_rec.business_group_id is null then
1168: fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),
1169: 'ASSIGNMENT.business_group_id')||'), ASSIGNMENT.business_group_id) ');
1170: else
1171: fnd_dsql.add_bind(p_sec_rec.business_group_id);
1172: end if;

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

1167: if p_sec_rec.business_group_id is null then
1168: fnd_dsql.add_text(' nvl(to_number('||nvl(to_char(p_sec_rec.business_group_id),
1169: 'ASSIGNMENT.business_group_id')||'), ASSIGNMENT.business_group_id) ');
1170: else
1171: fnd_dsql.add_bind(p_sec_rec.business_group_id);
1172: end if;
1173:
1174: if (instr(UPPER(p_sec_rec.restriction_text),'PERSON.')>0) or
1175: (p_sec_rec.view_all_applicants_flag = 'N'

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

1175: (p_sec_rec.view_all_applicants_flag = 'N'
1176: and (p_sec_rec.view_all_employees_flag <>'Y'
1177: or p_sec_rec.view_all_cwk_flag <>'Y')) then
1178:
1179: fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1180: fnd_dsql.add_text(' ( '); -- 5214715
1181: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1182: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1183: /* Got a fresh future person */

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

1176: and (p_sec_rec.view_all_employees_flag <>'Y'
1177: or p_sec_rec.view_all_cwk_flag <>'Y')) then
1178:
1179: fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1180: fnd_dsql.add_text(' ( '); -- 5214715
1181: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1182: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1183: /* Got a fresh future person */
1184: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');

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

1177: or p_sec_rec.view_all_cwk_flag <>'Y')) then
1178:
1179: fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1180: fnd_dsql.add_text(' ( '); -- 5214715
1181: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1182: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1183: /* Got a fresh future person */
1184: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1185: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

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

1178:
1179: fnd_dsql.add_text(' and ASSIGNMENT.person_id=PERSON.person_id and ');
1180: fnd_dsql.add_text(' ( '); -- 5214715
1181: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1182: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1183: /* Got a fresh future person */
1184: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1185: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1186: fnd_dsql.add_text(' AND NOT EXISTS

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

1180: fnd_dsql.add_text(' ( '); -- 5214715
1181: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1182: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1183: /* Got a fresh future person */
1184: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1185: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1186: fnd_dsql.add_text(' AND NOT EXISTS
1187: (SELECT NULL
1188: FROM per_all_people_f papf1

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

1181: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1182: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1183: /* Got a fresh future person */
1184: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1185: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1186: fnd_dsql.add_text(' AND NOT EXISTS
1187: (SELECT NULL
1188: FROM per_all_people_f papf1
1189: WHERE papf1.person_id = PERSON.person_id

Line 1186: fnd_dsql.add_text(' AND NOT EXISTS

1182: fnd_dsql.add_text(' between PERSON.effective_start_date and PERSON.effective_end_date ');
1183: /* Got a fresh future person */
1184: fnd_dsql.add_text( ' or (PERSON.effective_start_date>= ');
1185: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1186: fnd_dsql.add_text(' AND NOT EXISTS
1187: (SELECT NULL
1188: FROM per_all_people_f papf1
1189: WHERE papf1.person_id = PERSON.person_id
1190: AND papf1.effective_start_date < PERSON.effective_start_date)) ');

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

1188: FROM per_all_people_f papf1
1189: WHERE papf1.person_id = PERSON.person_id
1190: AND papf1.effective_start_date < PERSON.effective_start_date)) ');
1191:
1192: fnd_dsql.add_text(' ) '); -- 5214715
1193: End if;
1194:
1195: if p_sec_rec.view_all_organizations_flag='N' then
1196: fnd_dsql.add_text(' and ol.security_profile_id = ');

Line 1196: fnd_dsql.add_text(' and ol.security_profile_id = ');

1192: fnd_dsql.add_text(' ) '); -- 5214715
1193: End if;
1194:
1195: if p_sec_rec.view_all_organizations_flag='N' then
1196: fnd_dsql.add_text(' and ol.security_profile_id = ');
1197: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1198: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');
1199: end if;
1200:

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

1193: End if;
1194:
1195: if p_sec_rec.view_all_organizations_flag='N' then
1196: fnd_dsql.add_text(' and ol.security_profile_id = ');
1197: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1198: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');
1199: end if;
1200:
1201: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then

Line 1198: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');

1194:
1195: if p_sec_rec.view_all_organizations_flag='N' then
1196: fnd_dsql.add_text(' and ol.security_profile_id = ');
1197: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1198: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');
1199: end if;
1200:
1201: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1202: fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');

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

1198: fnd_dsql.add_text(' and ol.organization_id=ASSIGNMENT.organization_id ');
1199: end if;
1200:
1201: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1202: fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');
1203: fnd_dsql.add_bind (to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1204: fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');
1205: end if;
1206:

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

1199: end if;
1200:
1201: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1202: fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');
1203: fnd_dsql.add_bind (to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1204: fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');
1205: end if;
1206:
1207: if p_sec_rec.view_all_positions_flag='N' then

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

1200:
1201: if instr(UPPER(p_sec_rec.restriction_text),'PERSON_TYPE.')>0 then
1202: fnd_dsql.add_text(' and PERSON_TYPE.person_id = ASSIGNMENT.person_id and ');
1203: fnd_dsql.add_bind (to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1204: fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');
1205: end if;
1206:
1207: if p_sec_rec.view_all_positions_flag='N' then
1208: fnd_dsql.add_text(' and pl.security_profile_id = ');

Line 1208: fnd_dsql.add_text(' and pl.security_profile_id = ');

1204: fnd_dsql.add_text(' BETWEEN PERSON_TYPE.effective_start_date AND PERSON_TYPE.effective_end_date ');
1205: end if;
1206:
1207: if p_sec_rec.view_all_positions_flag='N' then
1208: fnd_dsql.add_text(' and pl.security_profile_id = ');
1209: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1210: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');
1211: end if;
1212:

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

1205: end if;
1206:
1207: if p_sec_rec.view_all_positions_flag='N' then
1208: fnd_dsql.add_text(' and pl.security_profile_id = ');
1209: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1210: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');
1211: end if;
1212:
1213: if p_sec_rec.view_all_payrolls_flag='N' then

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

1206:
1207: if p_sec_rec.view_all_positions_flag='N' then
1208: fnd_dsql.add_text(' and pl.security_profile_id = ');
1209: fnd_dsql.add_bind(p_sec_rec.security_profile_id );
1210: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');
1211: end if;
1212:
1213: if p_sec_rec.view_all_payrolls_flag='N' then
1214: fnd_dsql.add_text(' and (( ppl.security_profile_id = ');

Line 1214: fnd_dsql.add_text(' and (( ppl.security_profile_id = ');

1210: fnd_dsql.add_text(' and (pl.position_id=ASSIGNMENT.position_id or ASSIGNMENT.position_id is null )');
1211: end if;
1212:
1213: if p_sec_rec.view_all_payrolls_flag='N' then
1214: fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1215: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1216: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1217: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');
1218: end if;

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

1211: end if;
1212:
1213: if p_sec_rec.view_all_payrolls_flag='N' then
1214: fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1215: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1216: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1217: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');
1218: end if;
1219:

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

1212:
1213: if p_sec_rec.view_all_payrolls_flag='N' then
1214: fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1215: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1216: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1217: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');
1218: end if;
1219:
1220: if p_sec_rec.view_all_organizations_flag='Y' then

Line 1217: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');

1213: if p_sec_rec.view_all_payrolls_flag='N' then
1214: fnd_dsql.add_text(' and (( ppl.security_profile_id = ');
1215: fnd_dsql.add_bind(p_sec_rec.security_profile_id);
1216: fnd_dsql.add_text(' and ppl.payroll_id=ASSIGNMENT.payroll_id )');
1217: fnd_dsql.add_text(' or ASSIGNMENT.payroll_id is null )');
1218: end if;
1219:
1220: if p_sec_rec.view_all_organizations_flag='Y' then
1221: null;

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

1253: add_comm_str( p_sec_rec => sec_rec );
1254:
1255: /*--------- end for l_restriction_flags and l_exclude_flags----------------*/
1256:
1257: fnd_dsql.add_text(' and ( ');
1258: fnd_dsql.add_text(' ( '); -- 5214715
1259: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1260: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1261: or ( ASSIGNMENT.effective_start_date>= ');

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

1254:
1255: /*--------- end for l_restriction_flags and l_exclude_flags----------------*/
1256:
1257: fnd_dsql.add_text(' and ( ');
1258: fnd_dsql.add_text(' ( '); -- 5214715
1259: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1260: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1261: or ( ASSIGNMENT.effective_start_date>= ');
1262: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

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

1255: /*--------- end for l_restriction_flags and l_exclude_flags----------------*/
1256:
1257: fnd_dsql.add_text(' and ( ');
1258: fnd_dsql.add_text(' ( '); -- 5214715
1259: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1260: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1261: or ( ASSIGNMENT.effective_start_date>= ');
1262: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1263: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL

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

1256:
1257: fnd_dsql.add_text(' and ( ');
1258: fnd_dsql.add_text(' ( '); -- 5214715
1259: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1260: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1261: or ( ASSIGNMENT.effective_start_date>= ');
1262: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1263: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1264: FROM per_all_assignments_f pos1

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

1258: fnd_dsql.add_text(' ( '); -- 5214715
1259: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1260: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1261: or ( ASSIGNMENT.effective_start_date>= ');
1262: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1263: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1264: FROM per_all_assignments_f pos1
1265: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1266: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));

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

1259: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1260: fnd_dsql.add_text(' between ASSIGNMENT.effective_start_date and ASSIGNMENT.effective_end_date)
1261: or ( ASSIGNMENT.effective_start_date>= ');
1262: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1263: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1264: FROM per_all_assignments_f pos1
1265: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1266: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1267: fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');

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

1262: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1263: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1264: FROM per_all_assignments_f pos1
1265: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1266: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1267: fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');
1268: fnd_dsql.add_text('
1269: AND NOT EXISTS
1270: (SELECT NULL

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

1263: fnd_dsql.add_text(' AND NOT EXISTS ( SELECT NULL
1264: FROM per_all_assignments_f pos1
1265: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1266: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1267: fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');
1268: fnd_dsql.add_text('
1269: AND NOT EXISTS
1270: (SELECT NULL
1271: FROM per_all_assignments_f pos1

Line 1268: fnd_dsql.add_text('

1264: FROM per_all_assignments_f pos1
1265: WHERE pos1.person_id = ASSIGNMENT.person_id AND ');
1266: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1267: fnd_dsql.add_text(' BETWEEN pos1.effective_start_date AND pos1.effective_end_date ) ');
1268: fnd_dsql.add_text('
1269: AND NOT EXISTS
1270: (SELECT NULL
1271: FROM per_all_assignments_f pos1
1272: WHERE pos1.person_id = ASSIGNMENT.person_id

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

1279: ASSIGNMENT.period_of_placement_date_start))
1280: AND pos1.effective_start_date< ASSIGNMENT.effective_start_date)
1281: or (ASSIGNMENT.effective_end_date < ');
1282:
1283: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1284: -- added and clause for bug 5168364
1285: fnd_dsql.add_text(' and assignment.effective_end_date = (select max(effective_end_date)
1286: from per_all_assignments_f asg
1287: where asg.person_id = assignment.person_id

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

1281: or (ASSIGNMENT.effective_end_date < ');
1282:
1283: fnd_dsql.add_bind(to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1284: -- added and clause for bug 5168364
1285: fnd_dsql.add_text(' and assignment.effective_end_date = (select max(effective_end_date)
1286: from per_all_assignments_f asg
1287: where asg.person_id = assignment.person_id
1288: and asg.assignment_type in(''A'',''C'',''E'')
1289: ) ');

Line 1290: fnd_dsql.add_text(' AND NOT EXISTS

1286: from per_all_assignments_f asg
1287: where asg.person_id = assignment.person_id
1288: and asg.assignment_type in(''A'',''C'',''E'')
1289: ) ');
1290: fnd_dsql.add_text(' AND NOT EXISTS
1291: (SELECT NULL
1292: FROM per_all_assignments_f papf
1293: WHERE papf.person_Id = ASSIGNMENT.person_id
1294: AND papf.assignment_type in(''A'',''C'',''E'')

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

1292: FROM per_all_assignments_f papf
1293: WHERE papf.person_Id = ASSIGNMENT.person_id
1294: AND papf.assignment_type in(''A'',''C'',''E'')
1295: AND papf.effective_end_date >= ');
1296: fnd_dsql.add_bind( to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1297: fnd_dsql.add_text(' ))))'); -- 5214715
1298:
1299: fnd_dsql.add_text(' and not exists(select 1
1300: from per_person_list ppl

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

1293: WHERE papf.person_Id = ASSIGNMENT.person_id
1294: AND papf.assignment_type in(''A'',''C'',''E'')
1295: AND papf.effective_end_date >= ');
1296: fnd_dsql.add_bind( to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1297: fnd_dsql.add_text(' ))))'); -- 5214715
1298:
1299: fnd_dsql.add_text(' and not exists(select 1
1300: from per_person_list ppl
1301: where ppl.security_profile_id = ');

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

1295: AND papf.effective_end_date >= ');
1296: fnd_dsql.add_bind( to_date(to_char(p_effective_date,'DD/MM/YYYY'),'DD/MM/YYYY'));
1297: fnd_dsql.add_text(' ))))'); -- 5214715
1298:
1299: fnd_dsql.add_text(' and not exists(select 1
1300: from per_person_list ppl
1301: where ppl.security_profile_id = ');
1302: fnd_dsql.add_bind(sec_rec.security_profile_id);
1303: fnd_dsql.add_text(' and ppl.person_id = assignment.person_id

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

1298:
1299: fnd_dsql.add_text(' and not exists(select 1
1300: from per_person_list ppl
1301: where ppl.security_profile_id = ');
1302: fnd_dsql.add_bind(sec_rec.security_profile_id);
1303: fnd_dsql.add_text(' and ppl.person_id = assignment.person_id
1304: and ppl.granted_user_id is null)');
1305:
1306: -- if the custom sql flag is set and the restriction text is not empty

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

1299: fnd_dsql.add_text(' and not exists(select 1
1300: from per_person_list ppl
1301: where ppl.security_profile_id = ');
1302: fnd_dsql.add_bind(sec_rec.security_profile_id);
1303: fnd_dsql.add_text(' and ppl.person_id = assignment.person_id
1304: and ppl.granted_user_id is null)');
1305:
1306: -- if the custom sql flag is set and the restriction text is not empty
1307: -- (>2 chars) then append the custom sql to the end of the statement

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

1306: -- if the custom sql flag is set and the restriction text is not empty
1307: -- (>2 chars) then append the custom sql to the end of the statement
1308:
1309: if sec_rec.custom_restriction_flag='Y' and length(sec_rec.restriction_text)>2 then
1310: fnd_dsql.add_text(' and ');
1311: fnd_dsql.add_text(sec_rec.restriction_text);
1312: end if;
1313:
1314: if g_debug then

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

1307: -- (>2 chars) then append the custom sql to the end of the statement
1308:
1309: if sec_rec.custom_restriction_flag='Y' and length(sec_rec.restriction_text)>2 then
1310: fnd_dsql.add_text(' and ');
1311: fnd_dsql.add_text(sec_rec.restriction_text);
1312: end if;
1313:
1314: if g_debug then
1315: hr_utility.trace('select '||to_char(length(l_select_text)));