Product: | PA - Projects |
---|---|
Description: | 10SC Only |
Implementation/DBA Data: | APPS.PA_ROUTING_HISTORY_V |
SELECT RT.ROWID ROW_ID
, RT.EXPENDITURE_ID EXPENDITURE_ID
, RT.CREATED_BY CREATED_BY
, RT.CREATION_DATE CREATION_DATE
, RT.LAST_UPDATED_BY LAST_UPDATED_BY
, RT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RT.ROUTED_FROM_PERSON_ID ROUTED_FROM_PERSON_ID
, RT.ROUTED_TO_PERSON_ID ROUTED_TO_PERSON_ID
, RT.ROUTING_STATUS_CODE ROUTING_STATUS_CODE
, RA.MEANING ROUTING_STATUS
, EF.FULL_NAME ROUTED_FROM_FULL_NAME
, ET.FULL_NAME ROUTED_TO_FULL_NAME
, RT.START_DATE ROUTING_DATE
, ROUND(NVL(RT.END_DATE
, SYSDATE) - RT.START_DATE) ELAPSED_DAYS
, RT.ROUTING_COMMENT ROUTING_COMMENT
FROM PA_LOOKUPS RA
, PER_PEOPLE_F EF
, PER_PEOPLE_F ET
, PA_ROUTINGS RT
WHERE RT.ROUTED_TO_PERSON_ID = ET.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN ET.EFFECTIVE_START_DATE
AND ET.EFFECTIVE_END_DATE
AND (ET.EMPLOYEE_NUMBER IS NOT NULL OR ET.NPW_NUMBER IS NOT NULL)
AND RT.ROUTED_FROM_PERSON_ID = EF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN EF.EFFECTIVE_START_DATE
AND EF.EFFECTIVE_END_DATE
AND (EF.EMPLOYEE_NUMBER IS NOT NULL OR EF.NPW_NUMBER IS NOT NULL)
AND RA.LOOKUP_TYPE = 'ROUTING STATUS CODE'
AND RA.LOOKUP_CODE = RT.ROUTING_STATUS_CODE