Need report of SVC_SERVICE_REQUESTS table details with SR employee, his dept, reporting dept, team, unit,section, trying with following query, but i am getting primary contact department details. Please help me on this:
SELECT
ssr.SR_NUMBER,
(SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF,
HR_ALL_ORGANIZATION_UNITS PDS
WHERE PDS.ORGANIZATION_ID = CF.DEP31_PK1_VALUE
-- AND TREE_CODE like 'CCS_HR_STRUCTURE_HIERARCHY'
AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
and rownum=1
) LEVEL_1,
(SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF,
HR_ALL_ORGANIZATION_UNITS PDS
WHERE PDS.ORGANIZATION_ID = CF.DEP30_PK1_VALUE
-- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
AND DISTANCE >= 1
and rownum=1
) LEVEL_2,
(SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF,
HR_ALL_ORGANIZATION_UNITS PDS
WHERE PDS.ORGANIZATION_ID = CF.DEP29_PK1_VALUE
-- AND TREE.TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
AND DISTANCE >= 2
and rownum=1
) LEVEL_3,
(SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF,
HR_ALL_ORGANIZATION_UNITS PDS
WHERE PDS.ORGANIZATION_ID = CF.DEP28_PK1_VALUE
-- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
AND DISTANCE >= 3
and rownum=1
) LEVEL_4,
(SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF,
HR_ALL_ORGANIZATION_UNITS PDS
WHERE PDS.ORGANIZATION_ID = CF.DEP27_PK1_VALUE
-- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
AND DISTANCE >= 4
and rownum=1
) LEVEL_5,
(SELECT PDS.NAME
FROM PER_ORG_TREE_NODE_CF CF,
HR_ALL_ORGANIZATION_UNITS PDS
WHERE PDS.ORGANIZATION_ID = CF.DEP26_PK1_VALUE
-- AND TREE_CODE = 'CCS_HR_STRUCTURE_HIERARCHY'
AND DEP25_PK1_VALUE = TREE.PK1_START_VALUE
AND DISTANCE >= 5
and rownum=1
) LEVEL_6
FROM
PER_PERSONS PP,
SVC_SR_CONTACT_MEMBERS sscm,
SVC_SERVICE_REQUESTS SSR,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERIODS_OF_SERVICE PPS,
HR_ALL_ORGANIZATION_UNITS HAOU,
HR_ALL_ORGANIZATION_UNITS PD,
PER_JOBS PJ,
PER_GRADES PG,
HR_LOCATIONS HL,
HR_ALL_POSITIONS_F_TL POS,
PER_ORG_TREE_NODE TREE
WHERE 1=1
AND SSR.SR_ID = SSCM.SR_ID
and sscm.RELATION_TYPE_CD = 'ORA_SVC_AFFECTED_PARTY'
and pp.person_id = sscm.person_id
--AND upper(PPNF.FULL_NAME) = upper('Gilbert, Lawrence')
and PAPF.PERSON_ID = PP.PERSON_ID
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAPF.PERSON_ID = PPS.PERSON_ID
AND PAAM.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND PD.ORGANIZATION_ID(+) = PAAM.ORGANIZATION_ID
AND PAAM.LEGAL_ENTITY_ID =HAOU.ORGANIZATION_ID(+)
AND PAAM.JOB_ID = PJ.JOB_ID(+)
AND PG.GRADE_ID(+)= PAAM.GRADE_ID
AND HL.LOCATION_ID(+)= PAAM.LOCATION_ID
AND POS.POSITION_ID(+) = PAAM.POSITION_ID
AND TREE.PK1_START_VALUE = PD.ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PG.EFFECTIVE_START_DATE(+) AND PG.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PJ.EFFECTIVE_START_DATE(+) AND PJ.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN POS.EFFECTIVE_START_DATE(+) AND POS.EFFECTIVE_END_DATE(+)
--AND PD.ATTRIBUTE7 IN NVL(:P_ORGANIZATION_TYPE,PD.ATTRIBUTE7)/* DFF in PAAM table*/
AND TRIM(TREE_CODE) ='LB Brent Hierarchy'
AND PAAM.ASSIGNMENT_TYPE IN ('E','C')
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PPNF.NAME_TYPE='GLOBAL'
ORDER BY TREE.DEPTH
