Search for:
Aug 25 2021
BIP – Report Cannot Be Rendered Error

When designing BIP reports, sometimes the developer is encountered with “Report Cannot Be Rendered Error”. There can be various reasons of this error. The main reason behind this error is incorrect configuration.

To resolve this issue for excel templates, make sure that there are no invalid references under Name Manager.

Navigate to Formulas -> Name Manager and make sure there are no fields with invalid references. If there exist a field with invalid reference, delete the reference, save your template and upload again.

In the below example, FIELD1 has invalid reference:

You can test if the template is rendering proper data or not by uploading the sample xml data before actually uploading the file to BIP.

With incorrect reference, you will get below error:

Mandeep Gupta 0 Comments
Aug 19 2021
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)*/
Mandeep Gupta 0 Comments
Aug 14 2021
BIP – Query to get worker Parent Position
Select  DISTINCT papf_emp.person_number 				 				employee_number,
        ppnf.first_name||' '||ppnf.last_name 				employee_name,
		(select hapft.name
		   from hr_all_positions_f hapf, HR_ALL_POSITIONS_F_TL hapft
          WHERE hapf.position_id = paam.position_id
            AND hapf.position_id = hapft.position_id
	        AND trunc(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date
	        AND trunc(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date) employee_position,
		(select hapft.name
		   from hr_all_positions_f hapf, hr_all_positions_f_tl hapft,per_position_hierarchy_f pphf
          WHERE hapf.position_id = pphf.parent_position_id
		    AND pphf.position_id=paam.position_id
            AND hapf.position_id = hapft.position_id
	        AND trunc(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date
			AND trunc(SYSDATE) BETWEEN pphf.effective_start_date AND pphf.effective_end_date
	        AND trunc(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date) employee_position_parent
   from per_all_assignments_m paam,
		per_all_people_f papf_emp,
		per_person_names_f ppnf
  where papf_emp.person_id = paam.person_id
	and papf_emp.person_id = ppnf.person_id
	and trunc(sysdate) between papf_emp.effective_start_date and papf_emp.effective_end_date
    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 ppnf.name_type ='GLOBAL'
	and paam.assignment_type ='E'
	and paam.assignment_status_type = 'ACTIVE'
	--and paam.position_id = 300000009196111
order by papf_emp.person_number
Mandeep Gupta 1 Comments
Aug 1 2021
BIP – Extract Salary Basis Details
SELECT csb.name
      ,csb.salary_basis_code
      ,csb.salary_annualization_factor
      ,pldgt.name legislative_dg
      ,petft.element_name
      ,pivt.name input_val_name
      ,prft.name rate_name
 FROM cmp_salary_bases csb
     ,per_legislative_data_groups_tl pldgt
     ,pay_element_types_tl petft
     ,pay_input_values_f pivf
     ,pay_input_values_tl pivt
     ,per_rates_f_tl prft
WHERE csb.legislative_data_group_id = pldgt.legislative_data_group_id
  AND pldgt.language = USERENV('LANG')
  AND csb.element_type_id = petft.element_type_id
  AND petft.language = USERENV('LANG')
  AND csb.element_type_id = pivf.element_type_id
  AND csb.input_value_id = pivf.input_value_id
  AND trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date
  AND pivf.input_value_id = pivt.input_value_id
  AND pivt.language = USERENV('LANG')
  AND csb.grade_rate_id = prft.rate_id(+)
  AND NVL(prft.language,USERENV('LANG')) = USERENV('LANG')
Mandeep Gupta 0 Comments
Aug 1 2021
BIP – Query to extract Currency Details
select fct.name
      ,fcb.currency_code  
      ,fcb.symbol
	  ,fct.description
      ,fcb.enabled_flag
      ,to_char(fcb.start_date_active,'DD-MON-RRRR') start_date_act
      ,to_char(fcb.end_date_active,'DD-MON-RRRR') end_date_act
      ,fcb.issuing_territory_code
      ,fcb.precision
      ,fcb.extended_precision
      ,fcb.iso_flag
      ,fcb.derive_effective
 from fnd_currencies_b fcb 
     ,fnd_currencies_tl fct 
where fcb.currency_code =fct.currency_code
Mandeep Gupta 0 Comments
Aug 1 2021
BIP – Query to extract Salary component details
SELECT 	DISTINCT paam.assignment_id,
        paam.assignment_number,
		to_char(cs.date_from,'DD-MON-RRRR') date_from,  
		to_char(cs.date_to,'DD-MON-RRRR') date_to,
		cs.salary_id,
		csc.salary_component_id,
		csc.change_amount,
		csc.change_percentage,
		csc.component_reason_code
FROM 	PER_ALL_ASSIGNMENTS_M 		paam,
		CMP_SALARY					cs,		
		CMP_SALARY_COMPONENTS		csc
WHERE	cs.ASSIGNMENT_ID = paam.ASSIGNMENT_ID		
  AND   cs.SALARY_ID = csc.SALARY_ID
Mandeep Gupta 0 Comments