Fusion BIP security

Ensure that the queries which you are building drive through one or more of the following views:

PER_POSITION_SECURED_LIST_V

PER_PUB_PERS_SECURED_LIST_V

CMP_SALARY_SECURED_LIST_V

PER_ASSIGNMENT_SECURED_LIST_V

PER_DEPARTMENT_SECURED_LIST_V

PER_JOB_SECURED_LIST_V

PER_LDG_SECURED_LIST_V

PER_LEGAL_EMPL_SECURED_LIST_V

PER_LOCATION_SECURED_LIST_V

PAY_PAYROLL_SECURED_LIST_V

PER_PERSON_SECURED_LIST_V

PER_GRADE_SECURED_LIST_V

Terminated Employees Details

SELECT PersonNamePEO.FULL_NAME "Full Name",
PersonDetailsPEO.person_number "Person Number",
  PPNF_MGR.full_name "Supervisor Full Name",
  TO_CHAR(PeriodOfServicePEO.ACTUAL_TERMINATION_DATE,'MM-DD-YYYY') "Actual Termination Date",
  JobTranslationPEO.name "Job Name",
  TO_CHAR(PersonPEO.start_date,'MM-DD-YYYY') "Date of Hire",
  HrLocation.location_code "Location Name",
  OrganizationUnitTranslationPEO.name "Organization Name",
  (
  SELECT TO_CHAR(MAX(perpos.date_start),'MM-DD-YYYY')
  FROM per_periods_of_service perpos,
    per_all_assignments_m perpaam
  WHERE perpos.person_id          =PersonDetailsPEO.person_id
  AND perpaam.period_of_service_id=perpos.period_of_service_id
  AND perpaam.person_id           =perpos.person_id
  AND perpaam.ACTION_CODE        IN('REHIRE','HIRE')
  AND perpaam.effective_end_date <=PeriodOfServicePEO.ACTUAL_TERMINATION_DATE
  ) "Latest Date of Hire",
  TO_CHAR(PeriodOfServicePEO.original_date_of_hire,'MM-DD-YYYY') "ORIGINAL_DATE_OF_HIRE",
  PeriodOfServicePEO.last_updated_by "Terminated By",
  TO_CHAR(PeriodOfServicePEO.last_update_date,'MM-DD-YYYY') "Termination Last Update Date",
  AssignmentPEO.assignment_number "Assignment Number",
  TO_CHAR(AssignmentPEO.effective_start_date,'MM-DD-YYYY') "Effective Start Date",
  TO_CHAR(AssignmentPEO.effective_end_date,'MM-DD-YYYY') "Effective End Date",
  PEA.EMAIL_ADDRESS,
  NVL(PART.ACTION_REASON,'No Reason') "Leaving Reason",
  NationalIdentifierPEO.NATIONAL_IDENTIFIER_NUMBER "Social Security Number"
FROM PER_PERSONS PersonPEO,
  PER_ALL_PEOPLE_F PersonDetailsPEO,
  PER_PERSON_NAMES_F_V PersonNamePEO,
  PER_ALL_ASSIGNMENTS_M AssignmentPEO,
  PER_PERSON_TYPES_TL PersonTypesTranslationPEO,
  PER_PERIODS_OF_SERVICE PeriodOfServicePEO,
  PER_NATIONAL_IDENTIFIERS NationalIdentifierPEO,
  HR_ORGANIZATION_UNITS_F_TL OrganizationUnitTranslationPEO,
  PER_JOBS_F_TL JobTranslationPEO,
  hr_locations_all HrLocation,
  PER_ACTION_REASONS_B PARB,
  PER_ACTION_REASONS_TL PART,
  per_assignment_supervisors_f PASF,
  per_person_names_f PPNF_MGR,
  PER_EMAIL_ADDRESSES PEA,
  PER_ACTIONS_B ActionsPEO,
  HR_ORGANIZATION_UNITS_F_TL GRETranslationPEO,
  HR_ORGANIZATION_UNITS_F_TL BUTranslationPEO,
  hr_all_positions_f_tl PosiTL,
  PER_PERSON_SECURED_LIST_V PPSLV
WHERE PersonPEO.PERSON_ID                    = PersonDetailsPEO.PERSON_ID
AND PersonPEO.PERSON_ID                      = PersonNamePEO.PERSON_ID
AND PersonPEO.PERSON_ID                      = AssignmentPEO.PERSON_ID
AND AssignmentPEO.PERSON_TYPE_ID             = PersonTypesTranslationPEO.PERSON_TYPE_ID(+)
AND (USERENV('LANG'))                        = PersonTypesTranslationPEO.LANGUAGE
AND AssignmentPEO.PERIOD_OF_SERVICE_ID       = PeriodOfServicePEO.PERIOD_OF_SERVICE_ID(+)
AND AssignmentPEO.PERSON_ID                  = PeriodOfServicePEO.PERSON_ID(+)
AND PersonDetailsPEO.PERSON_ID               = NationalIdentifierPEO.PERSON_ID(+)
AND PersonDetailsPEO.PRIMARY_NID_ID          = NationalIdentifierPEO.NATIONAL_IDENTIFIER_ID(+)
AND ( (AssignmentPEO.EFFECTIVE_LATEST_CHANGE = 'Y' ) )
AND ( (AssignmentPEO.ASSIGNMENT_TYPE        IN ('E','C' ,'N','P') ))
AND AssignmentPEO.organization_id            = OrganizationUnitTranslationPEO.ORGANIZATION_ID(+)
AND (USERENV('LANG'))                        = OrganizationUnitTranslationPEO.LANGUAGE(+)
AND AssignmentPEO.JOB_ID                     = JobTranslationPEO.JOB_ID(+)
AND (USERENV('LANG'))                        = JobTranslationPEO.LANGUAGE(+)
AND HrLocation.location_id(+)                =AssignmentPEO.location_id

AND ( sysdate BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE AND PersonDetailsPEO.EFFECTIVE_END_DATE)
AND ( sysdate BETWEEN PersonNamePEO.EFFECTIVE_START_DATE AND PersonNamePEO.EFFECTIVE_END_DATE)
AND ( sysdate BETWEEN AssignmentPEO.EFFECTIVE_START_DATE AND AssignmentPEO.EFFECTIVE_END_DATE)
AND ( sysdate BETWEEN OrganizationUnitTranslationPEO.EFFECTIVE_START_DATE(+) AND OrganizationUnitTranslationPEO.EFFECTIVE_END_DATE(+))
AND ( sysdate BETWEEN JobTranslationPEO.EFFECTIVE_START_DATE(+) AND JobTranslationPEO.EFFECTIVE_END_DATE(+))

AND PARB.ACTION_REASON_ID        = PART.ACTION_REASON_ID(+)
AND AssignmentPEO.REASON_CODE    = PARB.ACTION_REASON_CODE(+)
AND PART.language(+)             ='US'
AND PeriodOfServicePEO.person_id = PASF.person_id(+)
AND PeriodOfServicePEO.ACTUAL_TERMINATION_DATE BETWEEN PASF.effective_start_date(+) AND PASF.effective_end_date(+)
AND PASF.manager_type(+) = 'LINE_MANAGER'
AND PASF.manager_id      = PPNF_MGR.person_id(+)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PPNF_MGR.effective_start_date(+)) AND TRUNC(PPNF_MGR.effective_end_date(+))
AND PPNF_MGR.name_type(+)             = 'GLOBAL'
AND PersonDetailsPEO.primary_email_id = PEA.email_address_id(+)
AND AssignmentPEO.ACTION_CODE         = ActionsPEO.ACTION_CODE(+)
AND AssignmentPEO.BUSINESS_GROUP_ID   =ActionsPEO.BUSINESS_GROUP_ID(+)

AND ActionsPEO.ACTION_TYPE_CODE   = 'EMPL_TERMINATE'
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.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 PeriodOfServicePEO.ACTUAL_TERMINATION_DATE BETWEEN PPSLV.EFFECTIVE_START_DATE AND PPSLV.EFFECTIVE_END_DATE

AND PeriodOfServicePEO.ACTUAL_TERMINATION_DATE >= (:p_from_date)
AND PeriodOfServicePEO.ACTUAL_TERMINATION_DATE <= (:p_to_date)

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
          

Filter items by color

If you've applied different cell or font colors or a conditional format, you can filter by the colors or icons that are shown in your table.

1. Click the arrow  in the table header of the column that has color formatting or conditional formatting applied.
2. Click Filter by Color and then pick the cell color, font color, or icon you want to filter by.
   
   The types of color options you’ll have available depend on the types of format you have applied.