HRL_CONN_DF_WORKER_INFO

Details

Schema: FUSION
Object owner: HRL
Object type: VIEW

Columns

Name
PERSON_ID ASSIGNMENT_ID PK1_NUM_VALUE PK2_NUM_VALUE PK3_NUM_VALUE PK1_CHAR_VALUE PK2_CHAR_VALUE GLOBAL_DISPLAY_NAME LOCAL_DISPLAY_NAME LOCAL_NAME_CHAR_CONTEXT ASSIGNMENT_NAME DIRECT_REPORTS_CNT ALL_REPORTS_CNT ALL_REPORTS_EMP_CNT ALL_REPORTS_CWK_CNT ALL_REPORTS_NWK_CNT ALL_REPORTS_DEPT_CNT ALL_REPORTS_LOC_CNT

Name

PERSON_ID

ASSIGNMENT_ID

PK1_NUM_VALUE

PK2_NUM_VALUE

PK3_NUM_VALUE

PK1_CHAR_VALUE

PK2_CHAR_VALUE

GLOBAL_DISPLAY_NAME

LOCAL_DISPLAY_NAME

LOCAL_NAME_CHAR_CONTEXT

ASSIGNMENT_NAME

DIRECT_REPORTS_CNT

ALL_REPORTS_CNT

ALL_REPORTS_EMP_CNT

ALL_REPORTS_CWK_CNT

ALL_REPORTS_NWK_CNT

ALL_REPORTS_DEPT_CNT

ALL_REPORTS_LOC_CNT

Query

SQL_Statement
SELECT asg.person_id, asg.assignment_id, asg.location_id pk1_num_value, asg.organization_id pk2_num_value, null pk3_num_value, asg.assignment_type pk1_char_value, null pk2_char_value, gname.display_name global_display_name, lname.display_name local_display_name, lname.char_set_context local_name_char_context, asg.assignment_name, NVL(DIRECT_REPORTS_CNT,0) DIRECT_REPORTS_CNT, NVL(ALL_REPORTS_CNT,0) ALL_REPORTS_CNT, NVL(ALL_REPORTS_EMP_CNT,0) ALL_REPORTS_EMP_CNT, NVL(ALL_REPORTS_CWK_CNT,0) ALL_REPORTS_CWK_CNT, NVL(ALL_REPORTS_NWK_CNT,0) ALL_REPORTS_NWK_CNT, NVL(ALL_REPORTS_DEPT_CNT,0) ALL_REPORTS_DEPT_CNT, NVL(ALL_REPORTS_LOC_CNT,0) ALL_REPORTS_LOC_CNT from ( select manager_id person_id, manager_assignment_id assignment_id , SUM(decode(manager_level, 1, 1, 0)) DIRECT_REPORTS_CNT, count(1) ALL_REPORTS_CNT, sum(decode(assignment_type,'E',1,0)) ALL_REPORTS_EMP_CNT, sum(decode(assignment_type,'C',1,0)) ALL_REPORTS_CWK_CNT, sum(decode(assignment_type,'N',1,0)) ALL_REPORTS_NWK_CNT, count(distinct NVL(organization_id,0)) ALL_REPORTS_DEPT_CNT, count(distinct NVL(location_id,0)) ALL_REPORTS_LOC_CNT from per_manager_hrchy_dn pmdh, hrl_conn_public_workers_v paam where 1=1 and pmdh.person_id = paam.person_id and pmdh.assignment_id = paam.assignment_id and trunc(sysdate) between pmdh.effective_start_date and pmdh.effective_end_date and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date and paam.effective_latest_change = 'Y' and paam.assignment_type IN ('E','C','N') and paam.assignment_status_type IN ( 'ACTIVE' , 'SUSPENDED' ) group by pmdh.manager_id, pmdh.manager_assignment_id ) cnts, per_person_names_f gname, per_person_names_f lname, hrl_conn_public_workers_v asg where asg.person_id = gname.person_id and gname.name_type = 'GLOBAL' and asg.person_id = lname.person_id and lname.name_type != 'GLOBAL' and trunc(sysdate) between gname.effective_start_date and gname.effective_end_date and trunc(sysdate) between lname.effective_start_date and lname.effective_end_date and cnts.person_id(+) = asg.person_id and cnts.assignment_id(+) = asg.assignment_id and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date and asg.effective_latest_change = 'Y' and asg.assignment_type IN ('E','C','N') and asg.assignment_status_type IN ( 'ACTIVE' , 'SUSPENDED' )

SQL_Statement

SELECT asg.person_id, asg.assignment_id,

asg.location_id pk1_num_value, asg.organization_id pk2_num_value, null pk3_num_value, asg.assignment_type pk1_char_value, null pk2_char_value,

gname.display_name global_display_name, lname.display_name local_display_name,

lname.char_set_context local_name_char_context, asg.assignment_name,

NVL(DIRECT_REPORTS_CNT,0) DIRECT_REPORTS_CNT, NVL(ALL_REPORTS_CNT,0) ALL_REPORTS_CNT,

NVL(ALL_REPORTS_EMP_CNT,0) ALL_REPORTS_EMP_CNT, NVL(ALL_REPORTS_CWK_CNT,0) ALL_REPORTS_CWK_CNT, NVL(ALL_REPORTS_NWK_CNT,0) ALL_REPORTS_NWK_CNT,

NVL(ALL_REPORTS_DEPT_CNT,0) ALL_REPORTS_DEPT_CNT, NVL(ALL_REPORTS_LOC_CNT,0) ALL_REPORTS_LOC_CNT

from (

select manager_id person_id, manager_assignment_id assignment_id ,

SUM(decode(manager_level, 1, 1, 0)) DIRECT_REPORTS_CNT,

count(1) ALL_REPORTS_CNT,

sum(decode(assignment_type,'E',1,0)) ALL_REPORTS_EMP_CNT,

sum(decode(assignment_type,'C',1,0)) ALL_REPORTS_CWK_CNT,

sum(decode(assignment_type,'N',1,0)) ALL_REPORTS_NWK_CNT,

count(distinct NVL(organization_id,0)) ALL_REPORTS_DEPT_CNT,

count(distinct NVL(location_id,0)) ALL_REPORTS_LOC_CNT

from per_manager_hrchy_dn pmdh, hrl_conn_public_workers_v paam

where 1=1

and pmdh.person_id = paam.person_id

and pmdh.assignment_id = paam.assignment_id

and trunc(sysdate) between pmdh.effective_start_date and pmdh.effective_end_date

and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date

and paam.effective_latest_change = 'Y'

and paam.assignment_type IN ('E','C','N')

and paam.assignment_status_type IN ( 'ACTIVE' , 'SUSPENDED' )

group by pmdh.manager_id, pmdh.manager_assignment_id

) cnts, per_person_names_f gname, per_person_names_f lname, hrl_conn_public_workers_v asg

where asg.person_id = gname.person_id and gname.name_type = 'GLOBAL'

and asg.person_id = lname.person_id and lname.name_type != 'GLOBAL'

and trunc(sysdate) between gname.effective_start_date and gname.effective_end_date

and trunc(sysdate) between lname.effective_start_date and lname.effective_end_date

and cnts.person_id(+) = asg.person_id and cnts.assignment_id(+) = asg.assignment_id

and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date

and asg.effective_latest_change = 'Y'

and asg.assignment_type IN ('E','C','N') and asg.assignment_status_type IN ( 'ACTIVE' , 'SUSPENDED' )