BIP – Query to extract data from Pay Inbound Records

Third party payslip data can be loaded into Payroll Inbound Records in Fusion HCM. Use below query to extract the data:

select distinct hpir.inbd_record_id
	  ,to_char(hpir.start_date,'RRRR/MM/DD', 'nls_date_language=American') startDate
	  ,hpir.entity_identifier employeeNumber
      ,hpir.payroll_id
      ,hpir.time_period_id
      ,hpir.person_id
      ,(select pldft.LOCATION_NAME
          from per_location_details_f pldf,PER_LOCATION_DETAILS_F_TL pldft
		 where pldf.location_id = paam.location_id
           and pldft.location_details_id = pldf.location_details_id
    	   and trunc(SYSDATE) BETWEEN pldf.effective_start_date AND pldf.effective_end_date
	       and trunc(SYSDATE) BETWEEN pldft.effective_start_date AND pldft.effective_end_date)	 location_name
     ,(select hauft.NAME
         from HR_ORG_UNIT_CLASSIFICATIONS_F houcf, 
		      HR_ALL_ORGANIZATION_UNITS_F haouf, 
		      HR_ORGANIZATION_UNITS_F_TL hauft
        where hauft.organization_id = paam.legal_entity_id
          AND haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID 
	      AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID 
	      AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE 
	      AND hauft.LANGUAGE = 'US'
	      AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE 
	      AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE 
	      AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP' 
	      AND trunc(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date)	le   
       ,(select TO_CHAR(ptp.end_date,'DD-MON-RRRR', 'nls_date_language=American')  from pay_time_periods ptp
          where ptp.time_period_id = hpir.time_period_id) periodEndDate
	   ,ppnf.first_name||' '||ppnf.last_name employeeName
	   ,(select meaning from fnd_lookup_values
          where lookup_type = 'EMP_CAT'
            and lookup_code = paam.employment_category) catgr
		,pp.attribute1
		,pp.attribute2
		,pni.national_identifier_number
  from hry_pi_inbd_records hpir,
	   per_all_assignments_m paam,
	   per_person_names_f ppnf,
	   per_persons pp,
	   per_national_identifiers pni
 where hpir.person_id = paam.person_id
     and hpir.person_id = ppnf.person_id
	 and hpir.person_id = pp.person_id
	 and hpir.person_id = pni.person_id
     and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
	 and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
     and assignment_type not like 'ET%'
     and paam.ACTION_CODE <> 'TERMINATION'
    /* and hpir.person_id = (select pu.person_id 
		                  from per_users pu 
						 where pu.user_guid=fnd_global.user_guid)*/