VIEW: APPS.PA_C_RES_DENORM_V

Source

View Text - Preformatted

SELECT per.person_id resource_source_id, txn.resource_id, resdenorm.resource_name resource_name, null resource_first_name, null resource_last_name, null resource_middle_names, null resource_type, null resource_type_id, decode(per.current_employee_flag, 'Y', per.employee_number, per.npw_number) resource_number, resdenorm.resource_organization_id resource_Organization_Id, org.name resource_Organization_Name, resdenorm.resource_effective_start_date assignment_start_date, resdenorm.resource_effective_end_date assignment_end_date, per.email_address resource_email_address, flv.meaning resource_person_type from per_people_f per, pa_resources_denorm resdenorm, pa_resource_txn_attributes txn, hr_all_organization_units_tl org, fnd_lookup_values flv WHERE per.person_id = txn.person_id(+) and (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL) and (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y') and txn.person_id = resdenorm.person_id and resdenorm.resource_effective_start_date between per.effective_start_date and per.effective_end_date and resdenorm.resource_effective_start_date = (SELECT min(resdenorm1.resource_effective_start_date) FROM pa_resources_denorm resdenorm1 WHERE resdenorm1.person_id = per.person_id AND resdenorm1.resource_effective_end_date >= trunc(sysdate)) and org.organization_id=resdenorm.resource_organization_id and org.LANGUAGE = USERENV ('LANG') AND flv.view_application_id=3 AND flv.lookup_type = 'PERSON_TYPE' AND flv.language = USERENV('LANG') AND flv.lookup_code=decode(per.current_employee_flag,'Y','EMP','CWK')

View Text - HTML Formatted

SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, TXN.RESOURCE_ID
, RESDENORM.RESOURCE_NAME RESOURCE_NAME
, NULL RESOURCE_FIRST_NAME
, NULL RESOURCE_LAST_NAME
, NULL RESOURCE_MIDDLE_NAMES
, NULL RESOURCE_TYPE
, NULL RESOURCE_TYPE_ID
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) RESOURCE_NUMBER
, RESDENORM.RESOURCE_ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, ORG.NAME RESOURCE_ORGANIZATION_NAME
, RESDENORM.RESOURCE_EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, RESDENORM.RESOURCE_EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, PER.EMAIL_ADDRESS RESOURCE_EMAIL_ADDRESS
, FLV.MEANING RESOURCE_PERSON_TYPE
FROM PER_PEOPLE_F PER
, PA_RESOURCES_DENORM RESDENORM
, PA_RESOURCE_TXN_ATTRIBUTES TXN
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, FND_LOOKUP_VALUES FLV
WHERE PER.PERSON_ID = TXN.PERSON_ID(+)
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND (PER.CURRENT_EMPLOYEE_FLAG = 'Y' OR PER.CURRENT_NPW_FLAG = 'Y')
AND TXN.PERSON_ID = RESDENORM.PERSON_ID
AND RESDENORM.RESOURCE_EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND RESDENORM.RESOURCE_EFFECTIVE_START_DATE = (SELECT MIN(RESDENORM1.RESOURCE_EFFECTIVE_START_DATE)
FROM PA_RESOURCES_DENORM RESDENORM1
WHERE RESDENORM1.PERSON_ID = PER.PERSON_ID
AND RESDENORM1.RESOURCE_EFFECTIVE_END_DATE >= TRUNC(SYSDATE))
AND ORG.ORGANIZATION_ID=RESDENORM.RESOURCE_ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV ('LANG')
AND FLV.VIEW_APPLICATION_ID=3
AND FLV.LOOKUP_TYPE = 'PERSON_TYPE'
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_CODE=DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'CWK')