1 Reply Latest reply: Aug 13, 2013 9:39 AM by Vigneswar Battu RSS

    How to track personal details changes in the report without using triggers on the database tables

    SMGupta

      Hi There,

       

      I'm having a requirement to track the oracle HRMS Personal details changes in the report not using the triggers.

       

      ex: if some changes the address of the person, then that changed values should be populated in the report.

       

      Please find below query:

       

      If any of the following columns data is updated, the new value should be populated in the report.

       

      SELECT PAPF.PERSON_ID                                                       PAPF_PERSON_ID

      ,      PAPF.EMPLOYEE_NUMBER                                               EMPLOYEE_NUMBER        

      ,      PAPF.FIRST_NAME                                                            FIRST_NAME   

      ,      PAPF.LAST_NAME                                                            LAST_NAME

      ,      TO_CHAR(PAPF.DATE_OF_BIRTH ,'DD-MON-YYYY')     DATE_OF_BIRTH

      ,      ADDR.ADDRESS_LINE1                                                   ADDRESS_1

      ,      ADDR.ADDRESS_LINE2                                                   ADDRESS_2

      ,      ADDR.ADDRESS_LINE3                                                   ADDRESS_3

      ,      ADDR.TOWN_OR_CITY                                                    TOWN

      ,      ADDR.REGION_1                                                              COUNTY

      ,      ADDR.POSTAL_CODE                                                     POST_CODE

      ,      ADDR.COUNTRY                                                              COUNTRY

      ,      PAY.PAYROLL_NAME                                                     PAYROLL_NAME

      ,      PLN_TYP.NAME                                                              PLAN_TYP

      ,      PLN.NAME                                                                     PRODUCT_NAME

      ,      BOF.NAME                                                                     COVERAGE

      FROM   PER_ALL_PEOPLE_F                                                PAPF

      ,      PER_ALL_ASSIGNMENTS_F                                          PAF

      ,      PER_ADDRESSES                                                        ADDR

      ,      PAY_PAYROLLS_F                                                       PAY

      ,      BEN_PRTT_ENRT_RSLT_F                                                 PEN

      ,      BEN_PL_TYP_F                                                         PLN_TYP

      ,      BEN_PL_F                                                             PLN

      ,      BEN_OIPL_F                                                           BOIPF

      ,      BEN_OPT_F                                                            BOF

      WHERE 1                                     = 1

      AND PAPF.PERSON_ID                          = PAF.PERSON_ID

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

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

      AND PAPF.PERSON_ID                          = ADDR.PERSON_ID

      AND ADDR.PRIMARY_FLAG                       = 'Y'

      AND ADDR.DATE_TO                            IS NULL

      AND PAF.PAYROLL_ID                          = PAY.PAYROLL_ID(+)

      AND PEN.PERSON_ID                           = PAPF.PERSON_ID

      AND PEN.BUSINESS_GROUP_ID                   = PAPF.BUSINESS_GROUP_ID

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

      AND PEN.PRTT_ENRT_RSLT_STAT_CD              IS NULL

      AND PEN.SSPNDD_FLAG                         = 'N'

      AND ( PEN.ENRT_CVG_THRU_DT                  >= TRUNC(SYSDATE)

      OR    PEN.ENRT_OVRIDN_FLAG                  = 'Y')

      AND EXISTS(SELECT PIL.PER_IN_LER_ID

                 FROM   BEN_PER_IN_LER PIL

                 WHERE  PIL.PER_IN_LER_ID         = PEN.PER_IN_LER_ID

                 AND    PIL.BUSINESS_GROUP_ID     = PEN.BUSINESS_GROUP_ID

                 AND    PIL.PER_IN_LER_STAT_CD    NOT IN ('VOIDD','BCKDT'))

      AND PEN.PL_TYP_ID                           = PLN_TYP.PL_TYP_ID  

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

      AND PLN_TYP.PL_TYP_ID                       = PLN.PL_TYP_ID 

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

      AND PLN.PL_ID                               = PEN.PL_ID

      AND PEN.OIPL_ID                             = BOIPF.OIPL_ID(+)

      AND TRUNC(SYSDATE)                          BETWEEN NVL(BOIPF.EFFECTIVE_START_DATE,HR_GENERAL.START_OF_TIME) AND NVL(BOIPF.EFFECTIVE_END_DATE,HR_GENERAL.END_OF_TIME)

      AND BOIPF.OPT_ID                            = BOF.OPT_ID(+)

      AND TRUNC(SYSDATE)                          BETWEEN NVL(BOF.EFFECTIVE_START_DATE,HR_GENERAL.START_OF_TIME) AND NVL(BOF.EFFECTIVE_END_DATE,HR_GENERAL.END_OF_TIME)

      AND (:P_SYSDATE)                            BETWEEN TRUNC(PEN.ENRT_CVG_STRT_DT) AND TRUNC(PEN.ENRT_CVG_THRU_DT)

      AND (CASE WHEN :P_PAYROLL_ID IS NULL THEN 'Y' ELSE NVL((SELECT 'Y' FROM DUAL WHERE PAY.PAYROLL_ID  = :P_PAYROLL_ID), 'N') END) = 'Y'

      AND PAPF.LAST_UPDATE_DATE                   = (:P_SYSDATE) ;

      --AND PAPF.LAST_UPDATE_DATE                   BETWEEN to_date(:P_SYSDATE) and (to_date(:p_sysdate) + INTERVAL '31' Day);

      --AND (:P_SYSDATE)                           = PAPF.LAST_UPDATE_DATE ;

       

      Thanks