View: EDWBV_HR_ASGN_ASSGNMNT_LCV

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

View Text

SELECT /* $HEADER: HRIVIEW.LDT 115.3 2001/09/18 10:51:35 PKM SHIP $ */ ASG.ASSIGNMENT_ID || '-' || INST.INSTANCE_CODE ASSIGNMENT_PK
, 'ALL' ALL_FK
, ASG.ASSIGNMENT_NUMBER || '(' || INST.INSTANCE_CODE || ')' NAME
, ASG.ASSIGNMENT_NUMBER || '(' || INST.INSTANCE_CODE || ')' ASSIGNMENT_DP
, BGR.NAME BUSINESS_GROUP
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG_START.EFFECTIVE_START_DATE START_DATE
, DECODE(ASG_END.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE(NULL)
, ASG_END.EFFECTIVE_END_DATE) END_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, ASG.PRIMARY_FLAG) PRIMARY_FLAG
, INST.INSTANCE_CODE INSTANCE_FK
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_ID
, BGR.ORGANIZATION_ID BUSINESS_GROUP_ID
, PGR.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, ASG.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, DECODE(ASG.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE(NULL)
, ASG.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, ASG.TITLE TITLE
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, ASG.FREQUENCY) NORMAL_HOURS_FREQUENCY
, ASG.NORMAL_HOURS NORMAL_HOURS
, AST.USER_STATUS ASSIGNMENT_STATUS
, HR_GENERAL.DECODE_LOOKUP('EMP_APL'
, ASG.ASSIGNMENT_TYPE) ASSIGNMENT_TYPE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, ASG.MANAGER_FLAG) MANAGER_FLAG
, ASG.TIME_NORMAL_START TIME_NORMAL_START
, ASG.TIME_NORMAL_FINISH TIME_NORMAL_END
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, ASG.PROBATION_UNIT) PROBATION_PERIOD_UNIT
, ASG.PROBATION_PERIOD PROBATION_PERIOD
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, ASG.PERF_REVIEW_PERIOD_FREQUENCY) PERF_REVIEW_PERIOD_FRQNCY
, ASG.PERF_REVIEW_PERIOD PERFORMANCE_REVIEW_PERIOD
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, ASG.SAL_REVIEW_PERIOD_FREQUENCY) SLRY_REVIEW_PERIOD_FRQNCY
, ASG.SAL_REVIEW_PERIOD SLRY_REVIEW_PERIOD
, HR_GENERAL.DECODE_LOOKUP('HOURLY_SALARIED_CODE'
, ASG.HOURLY_SALARIED_CODE) HOURLY_SALARIED_FLAG
, '_DF:PER:PER_ASSIGNMENTS:ASG'
, '_KF:PAY:GRP:PGR'
, ASG.CREATION_DATE CREATION_DATE
, GREATEST( NVL(AST.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(ASG.LAST_UPDATE_DATE
, 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 PER_ALL_ASSIGNMENTS_F ASG /* GENERATES LIST OF EARLIEST DATE TRACKED START DATES BY ASSIGNMENT */
, (SELECT ASSIGNMENT_ID
, EFFECTIVE_START_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_TYPE = 'E' MINUS /* REMOVE ANY ASSIGNMENT START DATE WHICH HAS AN EARLIER START DATE */ SELECT ASSIGNMENT_ID
, EFFECTIVE_START_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE EXISTS (SELECT 1
FROM PER_ALL_ASSIGNMENTS_F ASG1
WHERE ASG1.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG1.ASSIGNMENT_TYPE = 'E'
AND ASG1.EFFECTIVE_START_DATE < ASG.EFFECTIVE_START_DATE) ) ASG_START /* GENERATES LIST OF LATEST DATE TRACKED END DATES BY ASSIGNMENT */
, (SELECT ASG.ASSIGNMENT_ID
, ASG.EFFECTIVE_END_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN' MINUS /* REMOVE ANY ASSIGNMENT END DATE WHICH HAS AN LATER END DATE */ SELECT ASG.ASSIGNMENT_ID
, ASG.EFFECTIVE_END_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE EXISTS (SELECT 1
FROM PER_ALL_ASSIGNMENTS_F ASG1
, PER_ASSIGNMENT_STATUS_TYPES AST1
WHERE ASG1.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG1.ASSIGNMENT_STATUS_TYPE_ID = AST1.ASSIGNMENT_STATUS_TYPE_ID
AND AST1.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASG1.EFFECTIVE_END_DATE > ASG.EFFECTIVE_END_DATE)
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN' ) ASG_END
, PER_ASSIGNMENT_STATUS_TYPES AST
, HR_ALL_ORGANIZATION_UNITS BGR
, PAY_PEOPLE_GROUPS PGR
, EDW_LOCAL_INSTANCE INST
WHERE ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND BGR.ORGANIZATION_ID = ASG.BUSINESS_GROUP_ID
AND PGR.PEOPLE_GROUP_ID (+) = ASG.PEOPLE_GROUP_ID
AND ASG_START.ASSIGNMENT_ID (+) = ASG.ASSIGNMENT_ID
AND ASG_END.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID /* REMOVE BENEFITS ASSIGNMENTS */
AND ASG.ASSIGNMENT_TYPE IN ('E'
, 'A') /* FILTER TO ONLY RETURN THE CURRENT ASSIGNMENT RECORD FOR ANY ASSIGNMENT */
AND ((SYSDATE > ASG_END.EFFECTIVE_END_DATE
AND ASG_END.EFFECTIVE_END_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE) OR (SYSDATE <= ASG_END.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE))

Columns