SQL Query to fetch employees with missing bank account

In this article, we will try to fetch the employees who doesn’t have the bank account setup. This query will be helpful in validating the data post conversion before running the payroll to validate if all employees have the bank account added or not.

SQL Query:

Select PAPF.PERSON_ID
     , PPRD.PAYROLL_RELATIONSHIP_ID
     , PAAM.ASSIGNMENT_ID
     , PAPF.PERSON_NUMBER
     , PPRD.PAYROLL_RELATIONSHIP_NUMBER
     , PAAM.ASSIGNMENT_NUMBER
     , to_char(PAAM.EFFECTIVE_START_DATE, 'YYYY-MM-DD') ASSIGNMENT_EFFDT
     , PAAM.ASSIGNMENT_STATUS_TYPE ASSIGNMENT_STATUS
     , PLE.NAME LEGAL_EMPLOYER
     , PLDGV.NAME LDG_NAME
  From PER_ALL_PEOPLE_F PAPF
     , PER_PERSON_NAMES_F PPNF
     , PER_PERIODS_OF_SERVICE PPOS
     , PER_ALL_ASSIGNMENTS_M PAAM
     , PAY_PAY_RELATIONSHIPS_DN PPRD
     , PAY_REL_GROUPS_DN PRGD
  Left
  Join (Select PRGD1.PAYROLL_RELATIONSHIP_ID
          From PER_ALL_ASSIGNMENTS_M PAAM1
             , PAY_REL_GROUPS_DN PRGD1
             , PAY_BANK_ACCOUNTS PBA
             , PAY_PERSON_PAY_METHODS_F PPMF
         Where PAAM1.ASSIGNMENT_TYPE = 'E'
           And PAAM1.PRIMARY_FLAG = 'Y'
           And PAAM1.EFFECTIVE_LATEST_CHANGE = 'Y'
           And SYSDATE Between PAAM1.EFFECTIVE_START_DATE And PAAM1.EFFECTIVE_END_DATE
           And PRGD1.ASSIGNMENT_ID = PAAM1.ASSIGNMENT_ID
           And PBA.ACCOUNT_SOURCE = 'EBA'
           And PPMF.PAYROLL_RELATIONSHIP_ID = PRGD1.PAYROLL_RELATIONSHIP_ID
           And PPMF.BANK_ACCOUNT_ID = PBA.BANK_ACCOUNT_ID
           And SYSDATE Between PPMF.EFFECTIVE_START_DATE And PPMF.EFFECTIVE_END_DATE) EXST
    On (EXST.PAYROLL_RELATIONSHIP_ID = PRGD.PAYROLL_RELATIONSHIP_ID)
     , PER_LEGAL_EMPLOYERS PLE
     , PER_LEGISLATIVE_DATA_GROUPS_VL PLDGV
 Where 1 = 1
   And EXST.PAYROLL_RELATIONSHIP_ID Is NULL
   And SYSDATE Between PAPF.EFFECTIVE_START_DATE And PAPF.EFFECTIVE_END_DATE
   And PPNF.PERSON_ID = PAPF.PERSON_ID
   And PPNF.NAME_TYPE = 'GLOBAL'
   And SYSDATE Between PPNF.EFFECTIVE_START_DATE And PPNF.EFFECTIVE_END_DATE
   And PPOS.PERSON_ID = PAPF.PERSON_ID
   And PAAM.PERSON_ID = PAPF.PERSON_ID
   And PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
   And PAAM.ASSIGNMENT_TYPE In ('E', 'C')
   And PAAM.PRIMARY_FLAG = 'Y'
   And PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
   And PAAM.ASSIGNMENT_STATUS_TYPE In ('ACTIVE', 'SUSPENDED')
   And SYSDATE Between PAAM.EFFECTIVE_START_DATE And PAAM.EFFECTIVE_END_DATE
   And PPRD.PERSON_ID = PAPF.PERSON_ID
   And PRGD.PAYROLL_RELATIONSHIP_ID = PPRD.PAYROLL_RELATIONSHIP_ID
   And PRGD.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID
   And PRGD.GROUP_TYPE = 'A'
   And PLE.ORGANIZATION_ID = PRGD.LEGAL_EMPLOYER_ID
   And PLE.STATUS = 'A'
   And SYSDATE Between PLE.EFFECTIVE_START_DATE And PLE.EFFECTIVE_END_DATE
   And PLDGV.LEGISLATIVE_DATA_GROUP_ID = PPRD.LEGISLATIVE_DATA_GROUP_ID
 Order
    By PAPF.PERSON_NUMBER
     , PPRD.PAYROLL_RELATIONSHIP_NUMBER
     , PAAM.ASSIGNMENT_NUMBER

If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.