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.

[Home] [Help]

View: EDWBV_HR_PERM_ASSIGN_LCV

Product:	HRI - Human Resources Intelligence
Description:
Implementation/DBA Data:	APPS.EDWBV_HR_PERM_ASSIGN_LCV

View Text

SELECT PEO.PERSON_ID || '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS' ASSIGNMENT_PK
, 'ALL' ALL_FK
, DECODE(TREE.SUB_PERSON_ID
, NULL
, '1-NA_EDW-'|| INST.INSTANCE_CODE
, DECODE(SIGN(TREE.SUB_LEVEL-14)
, 1
, '1-' || HRI_EDW_DIM_PERSON.GET_NEAREST_PARENT(PEO.PERSON_ID) || '-' || INST.INSTANCE_CODE
, 0
, '1-'|| TREE.SUB_PERSON_ID || '-' || INST.INSTANCE_CODE
, TO_CHAR(15-TREE.SUB_LEVEL) || '-' || TREE.SUB_PERSON_ID || '-' || INST.INSTANCE_CODE || '-TL' || TO_CHAR(15-TREE.SUB_LEVEL) )) SPRVSR_LVL1_FK
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) || '
, ' || 'EMPLOYEE' || ')' NAME
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) || '
, ' || 'EMPLOYEE' || ')' PERSON_DP
, BGR.NAME BUSINESS_GROUP
, NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) PERSON_NUM
, PEO.FULL_NAME FULL_NAME
, PEO.LAST_NAME LAST_NAME
, PPS.DATE_START START_DATE
, PPS.ACTUAL_TERMINATION_DATE END_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) EMPLOYEE_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) APPLICANT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_BUYER(PEO.PERSON_ID)) BUYER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_PLANNER(PEO.PERSON_ID)) PLANNER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_SALES_REP(PEO.PERSON_ID)) SALES_REP_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') SYS_GEN_FLAG
, INST.INSTANCE_CODE INSTANCE
, PEO.PERSON_ID PERSON_ID
, TO_NUMBER(NULL) SALESREP_ID
, NULL PLANNER_CODE
, PEO.BUSINESS_GROUP_ID ORGANIZATION_ID
, PEO.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, DECODE(PEO.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE(NULL)
, PEO.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PEO.FIRST_NAME FIRST_NAME
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, PEO.KNOWN_AS KNOWN_AS
, HR_GENERAL.DECODE_LOOKUP('TITLE'
, PEO.TITLE) TITLE
, PEO.SUFFIX NAME_SUFFIX
, PEO.PRE_NAME_ADJUNCT NAME_PREFIX
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, HR_GENERAL.DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS) MARITAL_STATUS
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.REHIRE_RECOMMENDATION) REHIRE_RCMMNDTN
, LNG.NLS_LANGUAGE CRRSPNDNC_LANGUAGE
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMP_DATA_VRFD
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.REGISTERED_DISABLED_FLAG
, 'N')) DISABILITY_FLAG
, PEO.RESUME_LAST_UPDATED RESUME_UPDATED_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.RESUME_EXISTS
, 'N')) RESUME_EXISTS
, HR_GENERAL.DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE) FAST_PATH_EMPLOYEE
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, PEO.FTE_CAPACITY FTE_CAPACITY
, HR_GENERAL.DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS) STUDENT_STATUS
, PEO.TOWN_OF_BIRTH TOWN_OF_BIRTH
, PEO.REGION_OF_BIRTH REGION_OF_BIRTH
, FTV.TERRITORY_SHORT_NAME COUNTRY_OF_BIRTH
, HR_GENERAL.DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY) NATIONALITY
, PEO.GLOBAL_PERSON_ID GLOBAL_PERSON_ID
, PEO.CREATION_DATE CREATION_DATE
, GREATEST( NVL(PEO.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(TREE.SUB_LAST_PTNTL_CHANGE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(BGR.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS BGR
, PER_ALL_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
, FND_LANGUAGES LNG
, FND_TERRITORIES_VL FTV
, HRI_SUPV_HRCHY_SUMMARY TREE /* GENERATES LIST OF LATEST DATE TRACKED END DATES BY PERSON */
, (SELECT PERSON_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PEO MINUS /* REMOVE ANY PERSON END DATE WHICH HAS AN LATER END DATE */ SELECT PERSON_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PEO
WHERE EXISTS (SELECT 1
FROM PER_ALL_PEOPLE_F PEO1
WHERE PEO1.PERSON_ID = PEO.PERSON_ID
AND PEO1.EFFECTIVE_END_DATE > PEO.EFFECTIVE_END_DATE) ) PEO_END
WHERE /* IF THERE ARE FUTURE DATED CHANGES
, ONLY SELECT THE CURRENT DATE TRACKED ROW */ ((SYSDATE < PEO_END.EFFECTIVE_START_DATE
AND SYSDATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE) OR (SYSDATE >= PEO_END.EFFECTIVE_START_DATE
AND PEO_END.EFFECTIVE_START_DATE = PEO.EFFECTIVE_START_DATE))
AND PEO_END.PERSON_ID = PEO.PERSON_ID
AND PEO.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND NOT EXISTS (SELECT NULL
FROM PER_PERIODS_OF_SERVICE LATER_PPS
WHERE LATER_PPS.PERSON_ID = PEO.PERSON_ID
AND LATER_PPS.DATE_START > PPS.DATE_START)
AND PEO.CORRESPONDENCE_LANGUAGE = LNG.LANGUAGE_CODE (+)
AND PEO.COUNTRY_OF_BIRTH = FTV.TERRITORY_CODE (+)
AND PEO.PERSON_ID = TREE.SUB_PERSON_ID (+)
AND NVL(TREE.SUPV_LEVEL
, -2) + 1 = NVL(TREE.SUB_LEVEL
, -2 + 1)
AND PEO.PERSON_ID NOT IN (SELECT SUPV_PERSON_ID
FROM HRI_SUPV_HRCHY_SUMMARY TREE2
WHERE TREE2.SUPV_LEVEL = 0
AND TREE2.SUPV_PERSON_ID = PEO.PERSON_ID) UNION ALL /* ALL SUPERVISORS AT TOP OF SUPERVISOR HIERARCHY */ SELECT PEO.PERSON_ID || '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS' ASSIGNMENT_PK
, 'ALL' ALL_FK
, DECODE(PEO.PERSON_ID
, TREE.SUPV_PERSON_ID
, DECODE(TREE.SUPV_PERSON_ID
, NULL
, '1-NA_EDW-'|| INST.INSTANCE_CODE
, DECODE(SIGN(TREE.SUPV_LEVEL-15)
, 1
, '1-' || TREE.SUPV_PERSON_ID || '-' || INST.INSTANCE_CODE
, 0
, '1-'|| TREE.SUPV_PERSON_ID || '-' || INST.INSTANCE_CODE
, (TO_CHAR(15-TREE.SUPV_LEVEL) || '-' || TREE.SUPV_PERSON_ID || '-' || INST.INSTANCE_CODE || '-TL' || TO_CHAR(15-TREE.SUPV_LEVEL)) ))) SPRVSR_LVL1_FK
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) || '
, ' || 'EMPLOYEE' || ')' NAME
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) || '
, ' || 'EMPLOYEE' || ')' PERSON_DP
, BGR.NAME BUSINESS_GROUP
, NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) PERSON_NUM
, PEO.FULL_NAME FULL_NAME
, PEO.LAST_NAME LAST_NAME
, PPS.DATE_START START_DATE
, PPS.ACTUAL_TERMINATION_DATE END_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) EMPLOYEE_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) APPLICANT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_BUYER(PEO.PERSON_ID)) BUYER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_PLANNER(PEO.PERSON_ID)) PLANNER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_SALES_REP(PEO.PERSON_ID)) SALES_REP_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') SYS_GEN_FLAG
, INST.INSTANCE_CODE INSTANCE
, PEO.PERSON_ID PERSON_ID
, TO_NUMBER(NULL) SALESREP_ID
, NULL PLANNER_CODE
, PEO.BUSINESS_GROUP_ID ORGANIZATION_ID
, PEO.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, DECODE(PEO.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE(NULL)
, PEO.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PEO.FIRST_NAME FIRST_NAME
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, PEO.KNOWN_AS KNOWN_AS
, HR_GENERAL.DECODE_LOOKUP('TITLE'
, PEO.TITLE) TITLE
, PEO.SUFFIX NAME_SUFFIX
, PEO.PRE_NAME_ADJUNCT NAME_PREFIX
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, HR_GENERAL.DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS) MARITAL_STATUS
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.REHIRE_RECOMMENDATION) REHIRE_RCMMNDTN
, LNG.NLS_LANGUAGE CRRSPNDNC_LANGUAGE
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMP_DATA_VRFD
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.REGISTERED_DISABLED_FLAG
, 'N')) DISABILITY_FLAG
, PEO.RESUME_LAST_UPDATED RESUME_UPDATED_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.RESUME_EXISTS
, 'N')) RESUME_EXISTS
, HR_GENERAL.DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE) FAST_PATH_EMPLOYEE
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, PEO.FTE_CAPACITY FTE_CAPACITY
, HR_GENERAL.DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS) STUDENT_STATUS
, PEO.TOWN_OF_BIRTH TOWN_OF_BIRTH
, PEO.REGION_OF_BIRTH REGION_OF_BIRTH
, FTV.TERRITORY_SHORT_NAME COUNTRY_OF_BIRTH
, HR_GENERAL.DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY) NATIONALITY
, PEO.GLOBAL_PERSON_ID GLOBAL_PERSON_ID
, PEO.CREATION_DATE CREATION_DATE
, GREATEST( NVL(PEO.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(TREE.SUPV_LAST_PTNTL_CHANGE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(BGR.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS BGR
, PER_ALL_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
, FND_LANGUAGES LNG
, FND_TERRITORIES_VL FTV
, (SELECT DISTINCT SUPV_PERSON_ID
, SUPV_ASSIGNMENT_ID
, SUPV_LEVEL
, SUPV_LAST_PTNTL_CHANGE
FROM HRI_SUPV_HRCHY_SUMMARY SHS
WHERE SUPV_LEVEL = 0
AND SUB_LEVEL = SUPV_LEVEL+1) TREE /* GENERATES LIST OF LATEST DATE TRACKED END DATES BY PERSON */
, (SELECT PERSON_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PEO MINUS /* REMOVE ANY PERSON END DATE WHICH HAS AN LATER END DATE */ SELECT PERSON_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PEO
WHERE EXISTS (SELECT 1
FROM PER_ALL_PEOPLE_F PEO1
WHERE PEO1.PERSON_ID = PEO.PERSON_ID
AND PEO1.EFFECTIVE_END_DATE > PEO.EFFECTIVE_END_DATE) ) PEO_END
WHERE /* IF THERE ARE FUTURE DATED CHANGES
, ONLY SELECT THE CURRENT DATE TRACKED ROW */ ((SYSDATE < PEO_END.EFFECTIVE_START_DATE
AND SYSDATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE) OR (SYSDATE >= PEO_END.EFFECTIVE_START_DATE
AND PEO_END.EFFECTIVE_START_DATE = PEO.EFFECTIVE_START_DATE))
AND PEO_END.PERSON_ID = PEO.PERSON_ID
AND PEO.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND NOT EXISTS (SELECT NULL
FROM PER_PERIODS_OF_SERVICE LATER_PPS
WHERE LATER_PPS.PERSON_ID = PEO.PERSON_ID
AND LATER_PPS.DATE_START > PPS.DATE_START)
AND PEO.CORRESPONDENCE_LANGUAGE = LNG.LANGUAGE_CODE (+)
AND PEO.COUNTRY_OF_BIRTH = FTV.TERRITORY_CODE (+)
AND PEO.PERSON_ID = TREE.SUPV_PERSON_ID UNION ALL /*ALL SALES_REPS*/ SELECT RS.SALESREP_ID || '-' || ORG.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE || '-SALESREP-PERS' ASSIGNMENT_PK
, 'ALL' ALL_FK
, TO_CHAR('1-NA_EDW-'|| INST.INSTANCE_CODE) SPRVSR_LVL1_FK
, RS.NAME || '-' || ORG.NAME || '(' || 'SALESREP' || ')' NAME
, RS.NAME || '-' || ORG.NAME || '(' || 'SALESREP' || ')' PERSON_DP
, ORG.NAME BUSINESS_GROUP
, RS.SALESREP_NUMBER PERSON_NUM
, RS.NAME FULL_NAME
, NULL LAST_NAME
, NVL(RS.START_DATE_ACTIVE
, RS.CREATION_DATE) START_DATE
, RS.END_DATE_ACTIVE END_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') EMPLOYEE_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') APPLICANT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') BUYER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') PLANNER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'Y') SALES_REP_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'Y') SYS_GEN_FLAG
, INST.INSTANCE_CODE INSTANCE
, TO_NUMBER(NULL) PERSON_ID
, RS.SALESREP_ID SALESREP_ID
, NULL PLANNER_CODE
, RS.ORG_ID ORGANIZATION_ID
, NVL(RS.START_DATE_ACTIVE
, RS.CREATION_DATE) EFFECTIVE_START_DATE
, NVL(RS.END_DATE_ACTIVE
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD')) EFFECTIVE_END_DATE
, NULL FIRST_NAME
, NULL MIDDLE_NAMES
, NULL PREVIOUS_LAST_NAME
, NULL KNOWN_AS
, NULL TITLE
, NULL NAME_SUFFIX
, NULL NAME_PREFIX
, RS.EMAIL_ADDRESS EMAIL_ADDRESS
, NULL MARITAL_STATUS
, NULL REHIRE_RCMMNDTN
, NULL CRRSPNDNC_LANGUAGE
, TO_DATE(NULL) DATE_EMP_DATA_VRFD
, NULL DISABILITY_FLAG
, TO_DATE(NULL) RESUME_UPDATED_DATE
, NULL RESUME_EXISTS
, NULL GENDER
, NULL INTERNAL_LOCATION
, NULL MAILSTOP
, NULL FAST_PATH_EMPLOYEE
, NULL NATIONAL_IDENTIFIER
, TO_DATE(NULL) DATE_OF_BIRTH
, TO_NUMBER(NULL) FTE_CAPACITY
, NULL STUDENT_STATUS
, NULL TOWN_OF_BIRTH
, NULL REGION_OF_BIRTH
, NULL COUNTRY_OF_BIRTH
, NULL NATIONALITY
, NULL GLOBAL_PERSON_ID
, RS.CREATION_DATE CREATION_DATE
, GREATEST( NVL( RS.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(ORG.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
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-PERS' ASSIGNMENT_PK
, 'ALL' ALL_FK
, TO_CHAR('1-NA_EDW-'|| INST.INSTANCE_CODE) SPRVSR_LVL1_FK
, MP.PLANNER_CODE || '-' || ORG.NAME || ' (' || 'PLANNER' || ')' NAME
, MP.PLANNER_CODE || '-' || ORG.NAME || ' (' || 'PLANNER' || ')' PERSON_DP
, ORG.NAME BUSINESS_GROUP
, NULL PERSON_NUM
, MP.DESCRIPTION FULL_NAME
, NULL LAST_NAME
, MP.CREATION_DATE START_DATE
, MP.DISABLE_DATE END_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') EMPLOYEE_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') APPLICANT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') BUYER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'Y') PLANNER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') SALESREP_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'Y') SYS_GEN_FLAG
, INST.INSTANCE_CODE INSTANCE
, TO_NUMBER(NULL) PERSON_ID
, TO_NUMBER(NULL) SALESREP_ID
, MP.PLANNER_CODE PLANNER_CODE
, MP.ORGANIZATION_ID ORGANIZATION_ID
, MP.CREATION_DATE EFFECTIVE_START_DATE
, NVL(MP.DISABLE_DATE
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD')) EFFECTIVE_END_DATE
, NULL FIRST_NAME
, NULL MIDDLE_NAMES
, NULL PREVIOUS_LAST_NAME
, NULL KNOWN_AS
, NULL TITLE
, NULL NAME_SUFFIX
, NULL NAME_PREFIX
, MP.ELECTRONIC_MAIL_ADDRESS EMAIL_ADDRESS
, NULL MARITAL_STATUS
, NULL REHIRE_RCMMNDTN
, NULL CRRSPNDNC_LANGUAGE
, TO_DATE(NULL) DATE_EMP_DATA_VRFD
, NULL DISABILITY_FLAG
, TO_DATE(NULL) RESUME_UPDATED_DATE
, NULL RESUME_EXISTS
, NULL GENDER
, NULL INTERNAL_LOCATION
, NULL MAILSTOP
, NULL FAST_PATH_EMPLOYEE
, NULL NATIONAL_IDENTIFIER
, TO_DATE(NULL) DATE_OF_BIRTH
, TO_NUMBER(NULL) FTE_CAPACITY
, NULL STUDENT_STATUS
, NULL TOWN_OF_BIRTH
, NULL REGION_OF_BIRTH
, NULL COUNTRY_OF_BIRTH
, NULL NATIONALITY
, NULL GLOBAL_PERSON_ID
, MP.CREATION_DATE CREATION_DATE
, GREATEST( NVL(MP.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(ORG.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS ORG
, MTL_PLANNERS MP
WHERE MP.ORGANIZATION_ID = ORG.ORGANIZATION_ID

Columns

Name
ASSIGNMENT_PK
ALL_FK
SPRVSR_LVL1_FK
NAME
PERSON_DP
BUSINESS_GROUP
PERSON_NUM
FULL_NAME
LAST_NAME
START_DATE
END_DATE
EMPLOYEE_FLAG
APPLICANT_FLAG
BUYER_FLAG
PLANNER_FLAG
SALES_REP_FLAG
SYS_GEN_FLAG
INSTANCE
PERSON_ID
SALESREP_ID
PLANNER_CODE
ORGANIZATION_ID
EFFECTIVE_START_DATE
EFFECTIVE_END_DATE
FIRST_NAME
MIDDLE_NAMES
PREVIOUS_LAST_NAME
KNOWN_AS
TITLE
NAME_SUFFIX
NAME_PREFIX
EMAIL_ADDRESS
MARITAL_STATUS
REHIRE_RCMMNDTN
CRRSPNDNC_LANGUAGE
DATE_EMP_DATA_VRFD
DISABILITY_FLAG
RESUME_UPDATED_DATE
RESUME_EXISTS
GENDER
INTERNAL_LOCATION
MAILSTOP
FAST_PATH_EMPLOYEE
NATIONAL_IDENTIFIER
DATE_OF_BIRTH
FTE_CAPACITY
STUDENT_STATUS
TOWN_OF_BIRTH
REGION_OF_BIRTH
COUNTRY_OF_BIRTH
NATIONALITY
GLOBAL_PERSON_ID
CREATION_DATE
LAST_UPDATE_DATE