Dúvida Oracle AME (Approvals Managment)

Marcelo, boa noite!

 

Segue abaixo as querys que o workflow core utiliza para identificar o Supervisor com base no employee_id

 

Caso o Setup seja Aprovação por Supervisor    

 

SELECT DISTINCT Pafe.supervisor_id

FROM   Per_All_Assignments_f pafe,  — <BUG 6615913>

       Per_All_People_f ppfs,       — <BUG 6615913>

       Per_All_Assignments_f pafs,  — <BUG 6615913>

       per_person_types_v ppts,

       per_person_type_usages_f pptu

WHERE  pafe.person_id = p_empid

     AND Trunc(SYSDATE) BETWEEN pafe.Effective_Start_Date

                       AND pafe.Effective_End_Date

     AND pafe.Primary_Flag = ‘Y’

     AND pafe.Assignment_Type IN (‘E’,’C’)

     AND ppfs.Person_Id = pafe.Supervisor_Id

     AND Trunc(SYSDATE) BETWEEN ppfs.Effective_Start_Date

                     AND ppfs.Effective_End_Date

     AND Pafs.Person_Id = ppfs.Person_Id

     AND Trunc(SYSDATE) BETWEEN pafs.Effective_Start_Date

                      AND pafs.Effective_End_Date

     AND pafs.Primary_Flag = ‘Y’

     AND pafs.Assignment_Type IN (‘E’,’C’)

     AND pptu.Person_Id = ppfs.Person_Id

     AND ppts.person_type_id = pptu.person_type_id

     AND ppts.System_Person_Type IN (‘EMP’,’EMP_APL’,’CWK’);  

 

Caso o Setup seja Aprovação por Hierarquia de Posição

 

SELECT POEH.superior_id, poeh.superior_level, HREC.full_name

  FROM   PO_EMPLOYEES_CURRENT_X HREC,   — <BUG 6615913>

         PO_EMPLOYEE_HIERARCHIES POEH

  WHERE  POEH.position_structure_id = p_approval_path_id

  AND    POEH.employee_id = p_empid

  AND    HREC.employee_id = POEH.superior_id

  AND    POEH.superior_level > 0

  UNION ALL

  SELECT poeh.superior_id, poeh.superior_level, cwk.full_name

  FROM   PER_ALL_PEOPLE_F CWK,

         PO_EMPLOYEE_HIERARCHIES POEH,

         PER_ALL_ASSIGNMENTS_F A,

         PER_PERIODS_OF_SERVICE PS

  WHERE  poeh.position_structure_id = p_approval_path_id

  AND    poeh.employee_id = p_empid

  AND    cwk.person_id = poeh.superior_id

  AND    poeh.superior_level > 0

  AND    nvl(fnd_profile.value(‘HR_TREAT_CWK_AS_EMP’),’N’) = ‘Y’

  AND    A.PERSON_ID      = CWK.PERSON_ID

  AND    A.PERSON_ID      = PS.PERSON_ID

  AND    A.ASSIGNMENT_TYPE=’E’

  AND    CWK.EMPLOYEE_NUMBER IS NOT NULL

  AND    A.PERIOD_OF_SERVICE_ID = PS.PERIOD_OF_SERVICE_ID

  AND    A.PRIMARY_FLAG         = ‘Y’

  AND    TRUNC(SYSDATE) BETWEEN CWK.EFFECTIVE_START_DATE AND CWK.EFFECTIVE_END_DATE

  AND    TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE

  AND    (  PS.ACTUAL_TERMINATION_DATE>= TRUNC(sysdate) OR PS.ACTUAL_TERMINATION_DATE IS NULL )

  UNION ALL

  SELECT

         POEH.SUPERIOR_ID , POEH.SUPERIOR_LEVEL, CWK.FULL_NAME

  FROM   PER_ALL_PEOPLE_F CWK,

         PO_EMPLOYEE_HIERARCHIES POEH,

         PER_ALL_ASSIGNMENTS_F A,

         PER_PERIODS_OF_PLACEMENT PP

  WHERE  poeh.position_structure_id = p_approval_path_id

  AND    poeh.employee_id = p_empid

  AND    cwk.person_id = poeh.superior_id

  AND    poeh.superior_level > 0

  AND    nvl(fnd_profile.value(‘HR_TREAT_CWK_AS_EMP’),’N’) = ‘Y’

  AND    A.PERSON_ID      = CWK.PERSON_ID

  AND    A.PERSON_ID      = PP.PERSON_ID

  AND    A.ASSIGNMENT_TYPE=’C’

  AND    CWK.NPW_NUMBER IS NOT NULL

  AND    A.PERIOD_OF_PLACEMENT_DATE_START = PP.DATE_START

  AND    A.PRIMARY_FLAG         = ‘Y’

  AND    TRUNC(SYSDATE) BETWEEN CWK.EFFECTIVE_START_DATE AND CWK.EFFECTIVE_END_DATE

  AND    TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE

  AND    (  PP.ACTUAL_TERMINATION_DATE>= TRUNC(sysdate) OR PP.ACTUAL_TERMINATION_DATE IS NULL )

  ORDER BY superior_level, full_name;

 

 

Abs.

Eberton