Need report of SVC_SERVICE_REQUESTS table details with SR employee, his dept, reporting dept, team

rafi02

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

SteveF-Oracle

Sounds like you need an OTBI or Fusion channel.

This is not the correct place for HCM Content questions.

Possibly you are using Fusion Data Intelligence Platform (formerly Fusion Analytics Warehouse).

Perhaps if you clarify what products you are using, then a moderator can redirect you.