Employee Deduction Details

  SELECT PersonDetailsPEO.person_id,
          PersonNamePEO.full_name,
          PersonDetailsPEO.PERSON_NUMBER ,
          AssignmentPEO.assignment_number,
          NationalIdentifierPEO.NATIONAL_IDENTIFIER_NUMBER,
          JobTranslationPEO.name AS JOB_NAME,
          GRETranslationPEO.name as GRE,
          OrganizationUnitTranslationPEO.Name  AS ORG_NAME,
          HRLocationTL.location_name,
          SupervisorNamePEO.full_name AS SUP_NAME,
          BUTranslationPEO.name       AS BU_Name ,
          PosiTL.name                 AS POS_Name,
          BalanceCategoriesPEO.BASE_CATEGORY_NAME,
          PayrollActionPEO.effective_date,
          PayrollActionPEO.date_earned,
          PayrollDPEO.payroll_name,
          TimePeriodPEO.END_DATE,
          TimePeriodPEO.START_DATE,
          BalanceTypesPEO.BALANCE_NAME as ELEMENT_NAME,
          BalanceTypesPEO.CURRENCY_CODE,
          sum(PayrollBalancesPEO.balance_value) PTD_Balance
         
        FROM PAY_RUN_BALANCES PayrollBalancesPEO,
          PAY_PAY_RELATIONSHIPS_DN PayrollRelationshipPEO,
          PAY_PAYROLL_REL_ACTIONS PayrollRelationshipActionPEO,
          PAY_PAYROLL_ACTIONS PayrollActionPEO,
          PER_ALL_ASSIGNMENTS_F AssignmentPEO,
          PAY_REL_GROUPS_DN PRG,
          PER_ALL_PEOPLE_F PersonDetailsPEO,
          PAY_DEFINED_BALANCES DefinedBalancesPEO,
          PAY_BALANCE_DIMENSIONS BalanceDimensionsPEO,
          PAY_TIME_PERIODS TimePeriodPEO,
          PAY_BALANCE_TYPES_VL BalanceTypesPEO,
          PAY_BALANCE_CATEGORIES_VL BalanceCategoriesPEO,
          PER_PERSON_NAMES_F_V PersonNamePEO,
          PER_NATIONAL_IDENTIFIERS NationalIdentifierPEO,
          PAY_ALL_PAYROLLS_F PayrollDPEO,
          PER_JOBS_F_TL JobTranslationPEO,
          HR_ORGANIZATION_UNITS_F_TL OrganizationUnitTranslationPEO,
          HR_ORGANIZATION_UNITS_F_TL GRETranslationPEO,
          PER_LOCATION_DETAILS_F HRLocation,
          PER_LOCATION_DETAILS_F_TL HRLocationTL,
          PER_PERSON_NAMES_F_V SupervisorNamePEO,
          HR_ORGANIZATION_UNITS_F_TL BUTranslationPEO,
          --hr_all_positions_f_tl PosiTL,
          PER_ASSIGNMENT_SUPERVISORS_F AssignmentSupervisorPEO,
          PER_PERSON_SECURED_LIST_V PPSLV
        WHERE NVL(PayrollBalancesPEO.payroll_rel_action_id,0)=PayrollRelationshipActionPEO.payroll_rel_action_id
        AND PayrollRelationshipActionPEO.payroll_action_id   =PayrollActionPEO.payroll_action_id
        AND PayrollBalancesPEO.payroll_relationship_id       = PayrollRelationshipPEO.payroll_relationship_id (+)
        AND PayrollRelationshipPEO.person_id                 = AssignmentPEO.person_id
        AND PayrollActionPEO.effective_date BETWEEN AssignmentPEO.effective_start_date AND AssignmentPEO.effective_end_date
        AND PRG.relationship_group_id (+)=PayrollBalancesPEO.payroll_assignment_id
        and AssignmentPEO.ASSIGNMENT_TYPE        in ( 'E','C','N','P')
        and AssignmentPEO.person_id=PersonDetailsPEO.person_id
        AND sysdate between PersonDetailsPEO.effective_start_date and PersonDetailsPEO.effective_end_date
        AND DefinedBalancesPEO.BALANCE_DIMENSION_ID     = BalanceDimensionsPEO.BALANCE_DIMENSION_ID
        AND PayrollBalancesPEO.DEFINED_BALANCE_ID      = DefinedBalancesPEO.DEFINED_BALANCE_ID
        AND PayrollActionPEO.PAYROLL_ID              = TimePeriodPEO.PAYROLL_ID
        AND PayrollActionPEO.EFFECTIVE_DATE between TimePeriodPEO.START_DATE and TimePeriodPEO.END_DATE
        AND DefinedBalancesPEO.BALANCE_TYPE_ID          = BalanceTypesPEO.BALANCE_TYPE_ID
        AND BalanceTypesPEO.BALANCE_CATEGORY_ID     = BalanceCategoriesPEO.BALANCE_CATEGORY_ID
        AND PayrollRelationshipActionPEO.source_id is null
        AND AssignmentPEO.PERSON_ID    = PersonNamePEO.PERSON_ID(+)
        AND PersonNamePEO.name_type(+) = 'GLOBAL'
        AND ( sysdate BETWEEN PersonNamePEO.EFFECTIVE_START_DATE(+) AND PersonNamePEO.EFFECTIVE_END_DATE(+))
        AND PersonDetailsPEO.PERSON_ID           = NationalIdentifierPEO.PERSON_ID(+)
        AND PersonDetailsPEO.PRIMARY_NID_ID      = NationalIdentifierPEO.NATIONAL_IDENTIFIER_ID(+)
        AND PayrollActionPEO.PAYROLL_ID                = PayrollDPEO.PAYROLL_ID
        AND PayrollActionPEO.EFFECTIVE_DATE BETWEEN PayrollDPEO.EFFECTIVE_START_DATE AND PayrollDPEO.EFFECTIVE_END_DATE
        AND AssignmentPEO.JOB_ID                 = JobTranslationPEO.JOB_ID(+)
        AND (USERENV('LANG'))                    = JobTranslationPEO.LANGUAGE(+)
        AND ( sysdate BETWEEN JobTranslationPEO.EFFECTIVE_START_DATE(+) AND JobTranslationPEO.EFFECTIVE_END_DATE(+))
            AND AssignmentPEO.organization_id=OrganizationUnitTranslationPEO.ORGANIZATION_ID(+)
        AND ( sysdate BETWEEN OrganizationUnitTranslationPEO.EFFECTIVE_START_DATE(+) AND OrganizationUnitTranslationPEO.EFFECTIVE_END_DATE(+))
        AND OrganizationUnitTranslationPEO.language(+)='US'
        AND AssignmentPEO.legal_entity_id             =GRETranslationPEO.ORGANIZATION_ID(+)
        AND ( sysdate BETWEEN GRETranslationPEO.EFFECTIVE_START_DATE(+) AND GRETranslationPEO.EFFECTIVE_END_DATE(+))
        AND GRETranslationPEO.language(+)   ='US'
        AND AssignmentPEO.location_id       =HRLocation.location_id(+)
        AND HRLocation.LOCATION_DETAILS_ID  = HRLocationTL.LOCATION_DETAILS_ID(+)
        AND HRLocationTL.LANGUAGE(+)        = USERENV('LANG')
        AND HRLocation.EFFECTIVE_START_DATE = HRLocationTL.EFFECTIVE_START_DATE
        AND HRLocation.EFFECTIVE_END_DATE   = HRLocationTL.EFFECTIVE_END_DATE
        AND sysdate BETWEEN HRLocation.EFFECTIVE_START_DATE(+) AND HRLocation.EFFECTIVE_END_DATE(+)
          AND AssignmentSupervisorPEO.MANAGER_ID = SupervisorNamePEO.PERSON_ID(+)
        AND SupervisorNamePEO.name_type(+)     = 'GLOBAL'
        AND ( sysdate BETWEEN SupervisorNamePEO.EFFECTIVE_START_DATE(+) AND SupervisorNamePEO.EFFECTIVE_END_DATE(+))
            AND AssignmentPEO.Business_unit_id=BUTranslationPEO.ORGANIZATION_ID(+)
        AND ( sysdate BETWEEN BUTranslationPEO.EFFECTIVE_START_DATE(+) AND BUTranslationPEO.EFFECTIVE_END_DATE(+))
        AND BUTranslationPEO.language(+)='US'
        --AND AssignmentPEO.position_id   =PosiTL.position_id(+)
        --AND (USERENV('LANG'))           = PosiTL.LANGUAGE(+)
        AND AssignmentPEO.PERSON_ID = PPSLV.PERSON_ID
        AND sysdate BETWEEN PPSLV.effective_start_date AND PPSLV.effective_end_date
        AND AssignmentPEO.ASSIGNMENT_ID        = AssignmentSupervisorPEO.ASSIGNMENT_ID(+)
        AND ('LINE_MANAGER')                   = AssignmentSupervisorPEO.MANAGER_TYPE(+)
        AND ( sysdate BETWEEN AssignmentSupervisorPEO.EFFECTIVE_START_DATE(+) AND AssignmentSupervisorPEO.EFFECTIVE_END_DATE(+))
       
        AND ( (BalanceDimensionsPEO.PERIOD_TYPE          = 'RUN' ) )
        and TimePeriodPEO.STATUS             = 'O'
        AND AssignmentPEO.primary_flag              ='Y'
        AND (AssignmentPEO.assignment_status_type  = 'ACTIVE')
       
  --Pass the list of categories you want to list results for     
        AND BalanceCategoriesPEO.BASE_CATEGORY_NAME in ('Pre-Statutory Deductions','Voluntary Deductions',
       'Total Tax Deductions','Social Insurance Deductions','Involuntary Deductions')
  -- Pass the list of dimensions that you want to result
        AND  BalanceDimensionsPEO.BASE_DIMENSION_NAME  in
( 'Core Relationship No Calculation Breakdown, Tax Unit Run')
  -- pass the Person number below   
    and (PersonDetailsPEO.PERSON_NUMBER        IN (:p_person_number)
        OR 'All'           IN (:p_person_number
       ||'All'))
   --Pass check date for which you want to view data     
       and PayrollActionPEO.effective_date<=:p_to_date
group by
        PersonDetailsPEO.person_id,
          PersonDetailsPEO.PERSON_NUMBER ,
          AssignmentPEO.assignment_number,
          NationalIdentifierPEO.NATIONAL_IDENTIFIER_NUMBER,
          BalanceCategoriesPEO.BASE_CATEGORY_NAME,
          PayrollActionPEO.effective_date,
          PayrollActionPEO.date_earned,
          PayrollDPEO.payroll_name,
          TimePeriodPEO.END_DATE,
          TimePeriodPEO.START_DATE,
          SupervisorNamePEO.full_name ,
          BUTranslationPEO.name       ,
          PosiTL.name               ,
          BalanceTypesPEO.BALANCE_NAME,
          BalanceTypesPEO.CURRENCY_CODE,
          PersonNamePEO.full_name ,
          JobTranslationPEO.name,
          GRETranslationPEO.name,
          OrganizationUnitTranslationPEO.Name,
          HRLocationTL.location_name