Some SQL Statments usuful with HRMS 1

 Elements entered by user name

SELECT  PPF.EMPLOYEE_NUMBER                                           EMP_NO
                ,PPF.FULL_NAME                                                 EMP_NAME

          --  ,TO_CHAR(PEE.CREATION_DATE,'DD/MM/YYYY HH24:MI:SS')            CRT_DATE
          -- ,PEE.ELEMENT_ENTRY_ID                                          ENTRY_ID
             ,upper (FUR.USER_NAME)                                              User_Name
             ,PEE.CREATION_DATE

FROM  

      HR.PAY_ELEMENT_ENTRIES_F PEE
      ,HR.PAY_ELEMENT_TYPES_F   PTF
      ,HR.PER_ALL_ASSIGNMENTS_F PAF
      ,HR.PER_all_PEOPLE_F          PPF

 --   ,PAY_ELEMENT_ENTRIES_V PEEV
      ,APPLSYS.FND_USER      FUR

WHERE  

PAF.ASSIGNMENT_ID        = PEE.ASSIGNMENT_ID
AND   FUR.USER_ID = pee.LAST_UPDATED_BY
--and    PEE.ELEMENT_ENTRY_ID =   PEEV.ELEMENT_ENTRY_ID
--AND    :DATE1                   BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
--AND    :DATE1                   BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
--AND    :DATE1                   BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE
--AND    :DATE1                   BETWEEN PTF.EFFECTIVE_START_DATE AND PTF.EFFECTIVE_END_DATE
--AND     pel.payroll_id=:my_Payroll_ID
AND   upper (FUR.USER_NAME) =:USER_NAME 
--and ppf.EMPLOYEE_NUMBER   =:EMPLOYEE_NUMBER
AND    PPF.PERSON_ID            = PAF.PERSON_ID
AND    PTF.ELEMENT_TYPE_ID      = PEE.ELEMENT_TYPE_ID
AND    PTF.ATTRIBUTE1           = 'MISCEAR'
AND  PTF.ELEMENT_NAME   = 'Miscellaneous Earnings'

AND    TRUNC(PEE.CREATION_DATE) BETWEEN ( TO_DATE('12/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))AND
                   (TO_DATE('02/24/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))   -- '30-oct-2011'
--BETWEEN :DATE1 AND :DATE2
ORDER  BY 3,TO_DATE(PEE.CREATION_DATE)DESC
;

  PAYROLL RUN WITH ELEMENTS VALUES

 SELECT
            TO_NUMBER(NVL(PRRV.RESULT_VALUE,0))  RESULT_VALUE
           ,ET.ELEMENT_NAME
           ,PAPF.EMPLOYEE_NUMBER
           ,PAPF.FULL_NAME
    FROM   PAY_RUN_RESULTS          PRR
          ,PAY_RUN_RESULT_VALUES    PRRV
          ,PAY_INPUT_VALUES_F       PIVF
          ,PAY_ELEMENT_TYPES_F      ET
          ,PER_ALL_ASSIGNMENTS_F    PAAF
          ,PAY_ASSIGNMENT_ACTIONS   PAA
          ,PER_ALL_PEOPLE_F         PAPF
    WHERE  PAAF.EFFECTIVE_END_DATE = (  SELECT  MAX(PAAF1.EFFECTIVE_END_DATE)
                                        FROM    PER_ALL_ASSIGNMENTS_F PAAF1
                                        WHERE   PAAF1.ASSIGNMENT_ID =   PAAF.ASSIGNMENT_ID
                                     ) 
    AND    TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE              
    AND    PAPF.PERSON_ID           = PAAF.PERSON_ID
    AND    PRR.RUN_RESULT_ID        = PRRV.RUN_RESULT_ID
    AND    PRR.ELEMENT_TYPE_ID      = PIVF.ELEMENT_TYPE_ID
    AND    ET.ELEMENT_TYPE_ID       = PRR.ELEMENT_TYPE_ID
    AND    PRRV.INPUT_VALUE_ID      = PIVF.INPUT_VALUE_ID
    AND    PIVF.UOM                 = 'M'
    AND    PIVF.NAME                = 'Pay Value'
    AND    PRRV.RESULT_VALUE        IS NOT NULL
    AND    PRR.STATUS               != 'O'
    AND    PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
    AND    PAA.ASSIGNMENT_ID        = PAAF.ASSIGNMENT_ID
    AND    PRR.ASSIGNMENT_ACTION_ID  IN
    (

        SELECT  REG_DATA.ASS_ACTION
        FROM
        (
        SELECT  EMPLOYEE_NUMBER ,ASS_ACTION
        FROM   (
        SELECT PAPF.EMPLOYEE_NUMBER, PAA.ASSIGNMENT_ACTION_ID  ASS_ACTION
               ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        FROM   PER_ALL_PEOPLE_F          PAPF
              ,PER_ALL_ASSIGNMENTS_F     PAAF
              ,PAY_ALL_PAYROLLS_F        PPF
              ,PAY_PAYROLL_ACTIONS       PPA   
              ,PAY_ASSIGNMENT_ACTIONS    PAA
              ,PER_TIME_PERIODS_V        PTPV
              ,HR_SOFT_CODING_KEYFLEX    HSCK
        WHERE  PAPF.PERSON_ID              = PAAF.PERSON_ID
        AND    PAAF.PAYROLL_ID             = PPF.PAYROLL_ID
        AND    PAAF.PAYROLL_ID             = PPA.PAYROLL_ID
        AND    PAAF.PAYROLL_ID             = PTPV.PAYROLL_ID
        AND    PPA.PAYROLL_ACTION_ID       = PAA.PAYROLL_ACTION_ID
        AND    PAAF.ASSIGNMENT_ID          = PAA.ASSIGNMENT_ID
        AND    PTPV.TIME_PERIOD_ID         = PPA.TIME_PERIOD_ID
        AND    PAAF.SOFT_CODING_KEYFLEX_ID = HSCK.SOFT_CODING_KEYFLEX_ID
        AND    PPA.ACTION_STATUS           = 'C'
        AND    PAA.ACTION_STATUS           NOT IN ('V','E')
        AND    PPA.EFFECTIVE_DATE          BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
        AND    PPA.EFFECTIVE_DATE          BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
        AND    PPA.CONSOLIDATION_SET_ID    = :SET_ID
        AND    PPF.PAYROLL_ID              = :PAY
        AND    PTPV.TIME_PERIOD_ID         = :TIME_ID
        AND    HR_GENERAL.DECODE_ORGANIZATION(HSCK.SEGMENT1) = NVL(:ORG1 , HR_GENERAL.DECODE_ORGANIZATION(HSCK.SEGMENT1))
        AND    TO_CHAR(PPA.EFFECTIVE_DATE,'DD-MM-YYYY')= TO_CHAR(:DATE1,'DD-MM-YYYY')
        AND    PAAF.ORGANIZATION_ID = NVL(:EMP_ORG,PAAF.ORGANIZATION_ID)
        )
        GROUP BY EMPLOYEE_NUMBER ,ASS_ACTION
        ) REG_DATA
       WHERE  REG_DATA.EMPLOYEE_NUMBER='12345'
    )

;

EMPLOYEES WITHOUT  PAYROLL RUN 

    SELECT  PAPF.EMPLOYEE_NUMBER--,PAAF.EFFECTIVE_END_DATE
           
            FROM    PER_ALL_PEOPLE_F         PAPF,
                    PER_ALL_ASSIGNMENTS_F    PAAF
       
            WHERE   PAPF.EFFECTIVE_END_DATE = (  SELECT  MAX(PAPF1.EFFECTIVE_END_DATE)
                                                 FROM    PER_ALL_PEOPLE_F       PAPF1
                                                 WHERE   PAPF1.PERSON_ID =   PAPF.PERSON_ID
                                              )
            AND     PAAF.EFFECTIVE_END_DATE = (  SELECT  MAX(PAAF1.EFFECTIVE_END_DATE)
                                                 FROM    PER_ALL_ASSIGNMENTS_F  PAAF1
                                                 WHERE   PAAF1.PERSON_ID =   PAAF.PERSON_ID
                                              )
            AND     CURRENT_EMPLOYEE_FLAG   =   'Y'
            --AND     TO_DATE('01/08/2010','DD/MM/YYYY') BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
            AND     PAAF.PERSON_ID          =   PAPF.PERSON_ID
            AND     PAAF.PRIMARY_FLAG       =   'Y'
            AND     PAAF.PAYROLL_ID         =   :PAY

            MINUS

            SELECT DISTINCT PAPF.EMPLOYEE_NUMBER--,PAAF.EFFECTIVE_END_DATE
                   ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            FROM   PER_ALL_PEOPLE_F          PAPF
                  ,PER_ALL_ASSIGNMENTS_F     PAAF
--                  ,PAY_ALL_PAYROLLS_F        PPF
                  ,PAY_PAYROLL_ACTIONS       PPA   
                  ,PAY_ASSIGNMENT_ACTIONS    PAA
                  ,PER_TIME_PERIODS_V        PTPV
--                  ,HR_SOFT_CODING_KEYFLEX    HSCK
            WHERE  PAPF.PERSON_ID              = PAAF.PERSON_ID
--            AND    PAAF.PAYROLL_ID             = PPF.PAYROLL_ID
            AND    PAAF.PAYROLL_ID             = PPA.PAYROLL_ID
            AND    PAAF.PAYROLL_ID             = PTPV.PAYROLL_ID
            AND    PPA.PAYROLL_ACTION_ID       = PAA.PAYROLL_ACTION_ID
            AND    PAAF.ASSIGNMENT_ID          = PAA.ASSIGNMENT_ID
--            AND    PTPV.TIME_PERIOD_ID         = PPA.TIME_PERIOD_ID
--            AND    PAAF.SOFT_CODING_KEYFLEX_ID = HSCK.SOFT_CODING_KEYFLEX_ID
--            AND    PPA.ACTION_STATUS           = 'C'
--            AND    PAA.ACTION_STATUS           NOT IN ('V','E')
            AND    PPA.EFFECTIVE_DATE          BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
            AND    PPA.EFFECTIVE_DATE          BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
--            AND    PPA.CONSOLIDATION_SET_ID    = :SET_ID
            AND    PAAF.PAYROLL_ID              = :PAY
            AND    PTPV.TIME_PERIOD_ID         = :TIME_ID
            AND    PPA.DATE_EARNED BETWEEN PTPV.START_DATE AND PTPV.END_DATE
--            AND    HR_GENERAL.DECODE_ORGANIZATION(HSCK.SEGMENT1) = NVL(:ORG1 , HR_GENERAL.DECODE_ORGANIZATION(HSCK.SEGMENT1))
--            AND    TO_CHAR(PPA.EFFECTIVE_DATE,'DD-MM-YYYY')= TO_CHAR(TO_DATE('27/08/2010','DD/MM/YYYY'),'DD-MM-YYYY')--TO_CHAR(:DATE1,'DD-MM-YYYY')
--            AND    PAAF.ORGANIZATION_ID = NVL(:EMP_ORG,PAAF.ORGANIZATION_ID)
--and employee_number='12345'

    VIEW SALARY HISTORY WITH INCREMENT

SELECT  PPC1.PROPOSED_SALARY_N - PP1.PROPOSED_SALARY_N,
        TO_CHAR (PPC1.CHANGE_DATE, 'DD/MM/YYYY')
--        ROUND(((PPC1.PROPOSED_SALARY_N - PP1.PROPOSED_SALARY_N)/PP1.PROPOSED_SALARY_N*100),3) INC
FROM    PER_PAY_PROPOSALS   PPC1,
        PER_PAY_PROPOSALS   PP1
WHERE   PPC1.ASSIGNMENT_ID      = :ASSIGNMENT_ID
AND     PPC1.ASSIGNMENT_ID      = PP1.ASSIGNMENT_ID
AND     PPC1.LAST_CHANGE_DATE   = PP1.CHANGE_DATE
AND     PPC1.APPROVED           = 'Y'
AND     (PPC1.PROPOSED_SALARY_N - PP1.PROPOSED_SALARY_N) <> 0
AND     PPC1.CHANGE_DATE        = ( SELECT  MAX(PPC.CHANGE_DATE)
                                    FROM    PER_PAY_PROPOSALS   PPC,
                                            PER_PAY_PROPOSALS   PP
                                    WHERE   PPC.ASSIGNMENT_ID      = :ASSIGNMENT_ID
                                    AND     PPC.CHANGE_DATE       <= SYSDATE
                                    AND     (PPC.PROPOSED_SALARY_N - PP.PROPOSED_SALARY_N) <> 0
                                    AND     PPC.ASSIGNMENT_ID      = PP.ASSIGNMENT_ID
                                    AND     PPC.LAST_CHANGE_DATE   = PP.CHANGE_DATE
                                    AND     PPC.APPROVED           = 'Y'
                                  )