VIEW: APPS.PAY_ASGS_WITHOUT_PAYROLLS_V

Source

View Text - Preformatted

SELECT per.full_name, per.order_name, per.effective_start_date person_start_date, per.effective_end_date person_end_date, org.name organization_name, asg.assignment_number, asg.effective_start_date assign_start_date, asg.effective_end_date assign_end_date, sta.user_status user_status, loc.location_code address, per.person_id, asg.assignment_id, asg.business_group_id, sta.assignment_status_type_id, org.organization_id, loc.location_id, typ.person_type_id from per_people_f per, per_assignments_f asg, per_assignment_status_types sta, hr_organization_units org, hr_locations loc, per_person_types typ WHERE per.person_id = asg.person_id and per.person_type_id = typ.person_type_id and sta.assignment_status_type_id = asg.assignment_status_type_id and org.organization_id = asg.organization_id and loc.location_id(+)= asg.location_id and sta.pay_system_status = 'P' and typ.system_person_type in ('EMP', 'EX_EMP', 'EMP_APL') and asg.effective_start_date <= per.effective_end_date and asg.effective_end_date >= per.effective_start_date and asg.payroll_id is null

View Text - HTML Formatted

SELECT PER.FULL_NAME
, PER.ORDER_NAME
, PER.EFFECTIVE_START_DATE PERSON_START_DATE
, PER.EFFECTIVE_END_DATE PERSON_END_DATE
, ORG.NAME ORGANIZATION_NAME
, ASG.ASSIGNMENT_NUMBER
, ASG.EFFECTIVE_START_DATE ASSIGN_START_DATE
, ASG.EFFECTIVE_END_DATE ASSIGN_END_DATE
, STA.USER_STATUS USER_STATUS
, LOC.LOCATION_CODE ADDRESS
, PER.PERSON_ID
, ASG.ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID
, STA.ASSIGNMENT_STATUS_TYPE_ID
, ORG.ORGANIZATION_ID
, LOC.LOCATION_ID
, TYP.PERSON_TYPE_ID
FROM PER_PEOPLE_F PER
, PER_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES STA
, HR_ORGANIZATION_UNITS ORG
, HR_LOCATIONS LOC
, PER_PERSON_TYPES TYP
WHERE PER.PERSON_ID = ASG.PERSON_ID
AND PER.PERSON_TYPE_ID = TYP.PERSON_TYPE_ID
AND STA.ASSIGNMENT_STATUS_TYPE_ID = ASG.ASSIGNMENT_STATUS_TYPE_ID
AND ORG.ORGANIZATION_ID = ASG.ORGANIZATION_ID
AND LOC.LOCATION_ID(+)= ASG.LOCATION_ID
AND STA.PAY_SYSTEM_STATUS = 'P'
AND TYP.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EX_EMP'
, 'EMP_APL')
AND ASG.EFFECTIVE_START_DATE <= PER.EFFECTIVE_END_DATE
AND ASG.EFFECTIVE_END_DATE >= PER.EFFECTIVE_START_DATE
AND ASG.PAYROLL_ID IS NULL