4 Replies Latest reply: May 15, 2012 3:33 PM by Hussein Sawwan-Oracle RSS

    Release information to another manager question

    SNI-BryanJ
      We have a question. We are trying to find a query including tables that shows us whose information was released to whom, who made that release, and what time the release was made. We have an issue that we are trying to determine the scope of but are only able to see who was released to who, not who did it and what time it was done. How can we find this?

      apps.PER_PERSON_LIST is the table we are looking at but we are not sure what other tables would be involved or could be pulled in to get this information.
        • 1. Re: Release information to another manager question
          SNI-BryanJ
          We were able to find the information we needed in order to solve this issue.
          • 2. Re: Release information to another manager question
            Hussein Sawwan-Oracle
            We were able to find the information we needed in order to solve this issue.
            It would be great if you could share it with us.

            Thanks,
            Hussein
            • 3. Re: Release information to another manager question
              SNI-BryanJ
              Below is the query we ended up using. It did not give us the answer to "Who" made the release however, we were able to determine what groups of people should or should not be released based of of their positions and grades.

              SELECT FROM_EMP.PERSON_ID FROM_EMP_PERSON_ID
              ,FROM_EMP.EMPLOYEE_NUMBER FROM_EMP_EMPLOYEE_NUMBER
              ,FROM_EMP.NPW_NUMBER FROM_EMP_WORKER_NUMBER
              ,FROM_EMP.FULL_NAME FROM_EMP_FULL_NAME
              ,FROM_POS.NAME FROM_EMP_POSITION
              ,FROM_ASG.MANAGER_FLAG FROM_EMP_MANAGER
              ,FROM_GRADE.NAME FROM_GRADE
              ,TO_EMP.PERSON_ID TO_EMP_PERSON_ID
              ,TO_EMP.EMPLOYEE_NUMBER TO_EMP_EMPLOYEE_NUMBER
              ,TO_EMP.NPW_NUMBER TO_EMP_WORKER_NUMBER
              ,TO_EMP.FULL_NAME TO_EMP_FULL_NAME
              ,TO_POS.NAME TO_EMP_POSITION
              ,TO_ASG.MANAGER_FLAG TO_EMP_MANAGER
              ,TO_GRADE.NAME TO_GRADE
              FROM APPS.PER_PERSON_LIST PMAP
              ,APPS.FND_USER USR
              ,APPS.PER_ALL_PEOPLE_F FROM_EMP
              ,APPS.PER_ALL_PEOPLE_F TO_EMP
              ,APPS.PER_ALL_ASSIGNMENTS_F FROM_ASG
              ,APPS.PER_ALL_ASSIGNMENTS_F TO_ASG
              ,APPS.PER_ALL_POSITIONS FROM_POS
              ,APPS.PER_ALL_POSITIONS TO_POS
              ,APPS.PER_GRADES FROM_GRADE
              ,APPS.PER_GRADES TO_GRADE
              WHERE 1=1
              AND PMAP.GRANTED_USER_ID = USR.USER_ID
              AND TRUNC(SYSDATE) BETWEEN NVL(USR.START_DATE,TRUNC(SYSDATE)) AND NVL(USR.END_DATE,TRUNC(SYSDATE))
              ----
              AND PMAP.person_id = FROM_EMP.person_id
              AND TRUNC(SYSDATE) BETWEEN FROM_EMP.EFFECTIVE_START_DATE AND FROM_EMP.EFFECTIVE_END_DATE
              AND (FROM_EMP.CURRENT_EMPLOYEE_FLAG = 'Y' OR FROM_EMP.CURRENT_NPW_FLAG = 'Y')
              ----
              AND FROM_ASG.PERSON_ID = FROM_EMP.PERSON_ID
              AND TRUNC(SYSDATE) BETWEEN FROM_ASG.EFFECTIVE_START_DATE AND FROM_ASG.EFFECTIVE_END_DATE
              AND FROM_ASG.PRIMARY_FLAG = 'Y'
              AND FROM_ASG.ASSIGNMENT_TYPE IN ('E','C')
              ----
              AND USR.EMPLOYEE_ID = TO_EMP.PERSON_ID
              AND TRUNC(SYSDATE) BETWEEN TO_EMP.EFFECTIVE_START_DATE AND TO_EMP.EFFECTIVE_END_DATE
              AND (TO_EMP.CURRENT_EMPLOYEE_FLAG = 'Y' OR TO_EMP.CURRENT_NPW_FLAG = 'Y')
              ----
              AND TO_ASG.PERSON_ID = TO_EMP.PERSON_ID
              AND TRUNC(SYSDATE) BETWEEN TO_ASG.EFFECTIVE_START_DATE AND TO_ASG.EFFECTIVE_END_DATE
              AND TO_ASG.PRIMARY_FLAG = 'Y'
              AND TO_ASG.ASSIGNMENT_TYPE IN ('E','C')
              ----
              AND FROM_ASG.POSITION_ID = FROM_POS.POSITION_ID
              AND TRUNC(SYSDATE) BETWEEN NVL(FROM_POS.DATE_EFFECTIVE,TRUNC(SYSDATE)) AND NVL(FROM_POS.DATE_END,TRUNC(SYSDATE))
              ----
              AND TO_ASG.POSITION_ID = TO_POS.POSITION_ID
              AND TRUNC(SYSDATE) BETWEEN NVL(TO_POS.DATE_EFFECTIVE,TRUNC(SYSDATE)) AND NVL(TO_POS.DATE_END,TRUNC(SYSDATE))
              ----
              AND NOT EXISTS (SELECT 'A'
              FROM APPS.PER_ALL_ASSIGNMENTS_F ASG
              WHERE TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
              AND ASG.PRIMARY_FLAG = 'Y'
              AND ASG.ASSIGNMENT_TYPE IN ('E','C')
              AND ASG.PERSON_ID = PMAP.PERSON_ID
              AND ASG.SUPERVISOR_ID = USR.EMPLOYEE_ID)

              ----

              AND TRUNC(SYSDATE) BETWEEN NVL(FROM_GRADE.DATE_FROM,TRUNC(SYSDATE)) AND NVL(FROM_GRADE.DATE_TO,TRUNC(SYSDATE))
              ---

              AND TRUNC(SYSDATE) BETWEEN NVL(TO_GRADE.DATE_FROM,TRUNC(SYSDATE)) AND NVL(TO_GRADE.DATE_TO,TRUNC(SYSDATE))
              --
              AND FROM_ASG.GRADE_ID =FROM_GRADE.GRADE_ID(+)

              AND TO_ASG.GRADE_ID = TO_GRADE.GRADE_ID(+)
              and FROM_POS.NAME like 'Leader, Driver Business%'
              and TO_POS.NAME like 'Leader, Driver Business%'
              and FROM_ASG.MANAGER_FLAG = 'Y'
              ;
              • 4. Re: Release information to another manager question
                Hussein Sawwan-Oracle
                Thanks for the update and for sharing the solution!