FND Design Data [Home] [Help]

View: EDW_HR_PERSON_PERSONS_LCV

Product: PER - Human Resources
Description:
Implementation/DBA Data: Not implemented in this database
View Text

SELECT P.PERSON_ID || '-' || INST.INSTANCE_CODE || '-' || 'EMPLOYEE'
, 'ALL'
, SUBSTR(P.FULL_NAME
, 1
, 85) || ' (' || P.EMPLOYEE_NUMBER || ')'
, SUBSTR(P.FULL_NAME
, 1
, 85) || ' (' || P.EMPLOYEE_NUMBER || ')'
, ORG.NAME
, SUBSTR(P.EMPLOYEE_NUMBER
, 1
, 20)
, P.FULL_NAME
, P.LAST_NAME
, P.EFFECTIVE_START_DATE
, TO_DATE(NULL)
, EDW_HR_PERSON_PKG.BUYER_FLAG(P.PERSON_ID)
, EDW_HR_PERSON_PKG.PLANNER_FLAG(P.PERSON_ID)
, EDW_HR_PERSON_PKG.SALES_REP_FLAG(P.PERSON_ID)
, 'N'
, INST.INSTANCE_CODE
, P.PERSON_ID
, P.CREATION_DATE
, GREATEST(P.LAST_UPDATE_DATE
, ORG.LAST_UPDATE_DATE)
, NULL
, NULL
, NULL
, NULL
, NULL
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_ALL_PEOPLE_F P
WHERE P.EMPLOYEE_NUMBER IS NOT NULL
AND P.EFFECTIVE_START_DATE = (SELECT MAX(P2.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F P2
WHERE P2.EMPLOYEE_NUMBER IS NOT NULL
AND P2.PERSON_ID = P.PERSON_ID)
AND P.BUSINESS_GROUP_ID = ORG.ORGANIZATION_ID UNION ALL /*ALL SALES_REPS*/ SELECT RS.SALESREP_ID || '-' || ORG.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE || '-' || 'SALESREP'
, 'ALL'
, SUBSTR(RS.NAME
, 1
, 50) || '-' || SUBSTR(ORG.NAME
, 1
, 50) || ' (' || 'SALESREP' || ')'
, SUBSTR(RS.NAME
, 1
, 50) || '-' || SUBSTR(ORG.NAME
, 1
, 50) || ' (' || 'SALESREP' || ')'
, ORG.NAME
, NULL
, RS.NAME
, NULL
, NVL(RS.START_DATE_ACTIVE
, RS.CREATION_DATE)
, NVL(RS.END_DATE_ACTIVE
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD'))
, 'N'
, 'N'
, 'Y'
, 'Y'
, INST.INSTANCE_CODE
, RS.PERSON_ID
, RS.CREATION_DATE
, GREATEST(RS.LAST_UPDATE_DATE
, NVL(ORG.LAST_UPDATE_DATE
, RS.LAST_UPDATE_DATE))
, NULL
, NULL
, NULL
, NULL
, NULL
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS ORG
, RA_SALESREPS_ALL RS
WHERE RS.ORG_ID = ORG.ORGANIZATION_ID (+) UNION ALL /*ALL PLANNERS*/ SELECT MP.PLANNER_CODE || '-' || ORG.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE || '-' || 'PLANNER'
, 'ALL'
, MP.PLANNER_CODE || '-' || ORG.NAME || ' (' || 'PLANNER' || ')'
, MP.PLANNER_CODE || '-' || ORG.NAME || ' (' || 'PLANNER' || ')'
, ORG.NAME
, NULL
, MP.PLANNER_CODE
, NULL
, MP.CREATION_DATE
, NVL(MP.DISABLE_DATE
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD'))
, 'N'
, 'Y'
, 'N'
, 'Y'
, INST.INSTANCE_CODE
, MP.EMPLOYEE_ID
, MP.CREATION_DATE
, GREATEST(MP.LAST_UPDATE_DATE
, ORG.LAST_UPDATE_DATE)
, NULL
, NULL
, NULL
, NULL
, NULL
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS ORG
, MTL_PLANNERS MP
WHERE MP.ORGANIZATION_ID = ORG.ORGANIZATION_ID

Columns

Name
PERSON_PK
ALL_FK
PERSON_DP
NAME
BUSINESS_GROUP
PERSON_NUM
FULL_NAME
LAST_NAME
START_DATE
END_DATE
BUYER_FLAG
PLANNER_FLAG
SALES_REP_FLAG
SYS_GEN_FLAG
INSTANCE
PERSON_ID
CREATION_DATE
LAST_UPDATE_DATE
USER_ATTRIBUTE1
USER_ATTRIBUTE2
USER_ATTRIBUTE3
USER_ATTRIBUTE4
USER_ATTRIBUTE5