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.

VIEW: APPS.EDWBV_HR_ASGN_ASSGNMNT_LCV

Source

View Text - Preformatted


SELECT /* $Header: hrisvas.odf 120.0 2005/05/29 06:43:11 appldev noship $ */ 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))

View Text - HTML Formatted

SELECT /* $HEADER: HRISVAS.ODF 120.0 2005/05/29 06:43:11 APPLDEV NOSHIP $ */ 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))