Too many matching records found. Specify additional criteria to limit the number of records.

If you get this issue when creating an implementation project then all u have to do is Save and Close as in below screenshot.

Too many matching records found. Specify additional criteria to limit the number of records

Query to fetch Legal Employer and Payroll of an employee in Oracle Cloud HCM

PER_LEGAL_EMPLOYERS consists the Legal Employer details

select papf.person_number,
ppnf.full_name,
PAP.PAYROLL_NAME,
PAP.PAYROLL_NAME,
PLE.NAME LE_NAME
from
per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_f paaf,
PER_PERIODS_OF_SERVICE ppos,
pay_payroll_assignments ppasg,
PAY_ASSIGNED_PAYROLLS_DN papd,
pay_all_payrolls_f pap,
per_legal_employers ple
where 1=1
and papf.person_id = paaf.person_id
and papf.person_id = ppnf.person_id
and papf.person_id = ppos.person_id
and paaf.period_of_service_id = ppos.period_of_service_id
and paaf.assignment_id = ppasg.hr_assignment_id(+)
--and paaf.person_id = ppasg.person_id
and ppasg.payroll_term_id = papd.payroll_term_id(+)
and papd.payroll_id = pap.payroll_id(+)
and paaf.legal_entity_id = ple.organization_id
and ple.status = 'A'
and paaf.PRIMARY_FLAG ='Y'
--and paaf.ASSIGNMENT_TYPE = 'E'
AND PPOS.date_start = (SELECT max(date_start) FROM PER_PERIODS_OF_SERVICE PPOS_IN WHERE PPOS_IN.person_id = PAPF.person_id)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and trunc(sysdate) between ppasg.start_date(+) and ppasg.end_date(+)
and trunc(sysdate) between papd.start_date(+) and papd.end_date(+)
and trunc(sysdate) between pap.effective_start_date(+) and pap.effective_end_date(+)
and trunc(sysdate) between ple.effective_start_date and ple.effective_end_date
and ppnf.name_type='GLOBAL'--'US'
and paaf.assignment_type IN ('E','C','P','N')
and papf.person_number='21029'

Query to fetch absences data in Oracle HCM Cloud

Absences related tables in cloud are anc_per_absence_entries and ANC_ABSENCE_TYPES_F.



select  per.person_number,PER_ABSENCE_ENTRY_ID ,abs.absence_type_id,
            abs.name absence_type,
            to_char(apae.start_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')  start_date,
            to_char(apae.end_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')  end_date,
            duration
from ANC_PER_ABS_ENTRIES apae,
         PER_PERIODS_OF_SERVICE pps,
         PER_ALL_PEOPLE_F per,
   ANC_ABSENCE_TYPES_VL abs
where apae.period_of_service_id = pps.period_of_Service_id
    and apae.absence_type_id = abs.absence_type_id
    and pps.person_id = per.person_id
 and trunc(sysdate) between abs.effective_start_date and abs.effective_end_Date
 and trunc(sysdate) between per.effective_start_date and per.effective_end_Date
and per.person_number = '11111111'
 order by per.person_number

Query to get Employee Bank Account Details tagged to personal payment method in Oracle HCM Cloud

iby_ext_bank_accounts_v is the view which stores the bank account details and linked to a personal payment method of the employee. The column to be joined is primary_acct_owner_party_id of iby_ext_bank_accounts_v with party_id column of hz_parties. The column orig_system_reference of hz_parties is then joined with person_id of per_all_people_f.


SELECT papf.person_number
  ,pprd.payroll_relationship_number
  ,paaf.assignment_number
  ,iebc.EXT_BANK_ACCOUNT_ID
  ,iebc.primary_acct_owner_name
  ,iebc.bank_account_name
  ,iebc.bank_account_number
  ,iebc.bank_account_type
  ,iebc.bank_name
  ,iebc.bank_number
  ,iebc.bank_branch_name
  ,iebc.branch_number
  ,iebc.country_code
  ,iebc.CHECK_DIGITS
  , PPPM.PERCENTAGE
  , PPPM.PRIORITY
  , POPM.ORG_PAYMENT_METHOD_NAME
  , PPPM.amount
  FROM hz_parties hz_per,
       per_all_people_f papf,
   iby_ext_bank_accounts_v iebc,
   per_all_assignments_f paaf,
   per_periods_of_service ppos,
   pay_pay_relationships_dn pprd,
   pay_payroll_assignments ppasg,
   PAY_PERSONAL_PAYMENT_METHODS_F PPPM,
   PAY_ORG_PAY_METHODS_VL POPM
 WHERE hz_per.orig_system_reference = papf.person_id
   AND hz_per.party_id = iebc.primary_acct_owner_party_id
   AND papf.person_id = pprd.person_id
   and paaf.assignment_id = ppasg.hr_assignment_id
   AND ppos.date_start=(SELECT MAX(ppos1.date_start)
   FROM per_periods_of_service  ppos1
  WHERE ppos1.person_id=ppos.person_id
    --AND ppos1.period_of_service_id = ppos.period_of_service_id
)
  AND ppos.period_of_service_id = paaf.period_of_service_id
  AND paaf.person_id = papf.person_id
  AND paaf.assignment_type IN ('C','N','E')
  AND paaf.primary_flag = 'Y'
  AND NVL(PPOS.actual_termination_date,trunc(sysdate)) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
  AND NVL(PPOS.actual_termination_date,trunc(sysdate)) BETWEEN papf.effective_start_date AND papf.effective_end_date
  and NVL(PPOS.actual_termination_date,trunc(sysdate)) between ppasg.start_date and ppasg.end_date
  --AND TRUNC(SYSDATE) <= NVL(ppos.actual_termination_date, TO_DATE('12314712','MMDDYYYY'))   
  AND iebc.country_code = 'GB'
  AND iebc.BANK_ACCOUNT_ID = PPPM.BANK_ACCOUNT_ID
  AND pprd.payroll_relationship_id = pppm.payroll_relationship_id
  and pppm.PAYROLL_RELATIONSHIP_ID = ppasg.PAYROLL_RELATIONSHIP_ID----------------------------------most important
  AND POPM.ORG_PAYMENT_METHOD_ID = PPPM.ORG_PAYMENT_METHOD_ID
  AND TRUNC(SYSDATE) BETWEEN PPPM.effective_start_date AND PPPM.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN POPM.effective_start_date AND POPM.effective_end_date 
ORDER BY papf.person_number