Search for:
Sep 30 2021
BIP – Extract UDT data

Use below query to extract data related to User Defined Tables in Oracle HCM

select fut.base_user_table_name		"User Table"
      ,(select pldgt.name 
         from per_legislative_data_groups_tl pldgt 
        where pldgt.legislative_data_group_id=fucif.legislative_data_group_id
          and pldgt.language=userenv('LANG')) 	"Legsilative Data Group"
      ,fuc.base_user_column_name	"Column Name"
	  ,furf.row_low_range_or_name	"Row Name"
      ,to_char(fucif.effective_start_date,'YYYY/MM/DD') "Value Date Start"
	  ,to_char(fucif.effective_end_date,'YYYY/MM/DD')	"Value Date End"
      ,fucif.value
 from  ff_user_column_instances_f fucif,
       ff_user_columns fuc,
       ff_user_rows_f furf,
       ff_user_tables fut
 where fucif.user_column_id=fuc.user_column_id
   and furf.user_row_id=fucif.user_row_id
   and furf.user_table_id=fut.user_table_id
   and fuc.user_table_id=fut.user_table_id
Mandeep Gupta 0 Comments
Sep 26 2021
HDL – Sample HDL to load Performance Rating

Historical performance ratings can be loaded into Worker Talent Profile using TalentProfile.dat business object.

Below is the sample HDL (enhanced profiles (v2)), to load performance rating data:

METADATA|ProfileItem|ProfileItemId|ProfileId|ProfileCode|ContentItemId|ContentTypeId|ContentType|CountryId|DateFrom|DateTo|RatingModelId1|RatingModelCode1|RatingLevelId1|RatingLevelCode1|SectionId|SourceSystemOwner|SourceSystemId
MERGE|ProfileItem|||PER_0055|||PERFORMANCE_RATING||2018/01/01|2018/12/31||XXX_PERFORMANCE||3.75|9933|HRC_SQLLOADER|PR_0055
MERGE|ProfileItem|||PER_0031|||PERFORMANCE_RATING|| 2018/01/01|2018/12/31||XXX_PERFORMANCE||3.90|9933| HRC_SQLLOADER |PR _0055
Mandeep Gupta 0 Comments
Sep 26 2021
BIP – Query to extract business unit attached to a Job

In multi country HCM Implementations, work structures data can be stored in a COMMON set or a country specific set. In such cases, it becomes important to show the correct business unit name against the jobs in reports.

Use the below report to extract the Job and the business unit:

SELECT BU_ID
      ,BU_NAME
	  ,DEFAULT_SET_ID
	  ,SHORT_CODE
	  ,pjft.name
FROM  PER_JOBS_F pjf
     ,PER_JOBS_F_TL pjft
     ,FUN_ALL_BUSINESS_UNITS_V fabuv
WHERE pjf.SET_ID = fabuv.DEFAULT_SET_ID
and pjf.job_id = pjft.job_id
and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
and trunc(sysdate) between pjft.effective_start_date and pjft.effective_end_date
and pjft.language = 'US'
order by pjft.name

To get more details on SET Name, Set assignments, you can make use of following tables:

FND_SETID_ASSIGNMENTS

FND_SETID_SETS_VL

Mandeep Gupta 0 Comments
Sep 24 2021
BIP – Configure AuditViewDB for BIP Reports Audit

In order to audit the BIP reports, AuditViewDB should be first configured in list of data sources.

Follow the below steps to configure it:

  1. Login to your Fusion application instance (analytics).
  2. Click on Administration -> Manage BI Publisher -> Data Sources -> JNDI Connection

3. Click on Add Data Source and enter below details and click on Test Connection (it may take couple of click to open the Add Data Source page):

4. Once the connection is successful, you can start using it in BIP data models.

Mandeep Gupta 0 Comments
Sep 22 2021
BIP – Query to extract Fast Formulas used in HCM Extracts

Use the below query to extract list of “Extract Rule Type” fast formulas used in Oracle HCM Extracts:

select pedv.definition_name
      ,ffv.formula_name
  from per_ext_data_elements_vl pedev
      ,pay_report_records_f prrf
      ,pay_report_blocks prb
      ,per_ext_definitions_vl pedv
      ,ff_formulas_vl ffv 
where pedev.report_record_id = prrf.report_record_id
  and prrf.report_block_id = prb.report_block_id
  and prb.ext_definition_id = pedv.ext_definition_id
  and pedv.definition_name LIKE 'Dec%Absence%'
  and ffv.formula_id = pedev.rule_id
Mandeep Gupta 0 Comments
Sep 16 2021
BIP – Configure SFTP using private key for Authentication

In this article, we will see how to setup an SFTP server which will use private key for authentication. First step is to obtain the private key. Once you have the key, login into Oracle Fusion Application Analytics and click on Administration link on the right top side:

Click ‘Manage BI Publisher’ under BI Publisher on Administration Page:

Click on ‘Upload Center’ under ‘System Maintenance’:

Upload the key file with File Type as SSH Private Key:

Once the key is uploaded successfully. Return back.

Click on FTP under Delivery and click on Add Server:

Provide the details and choose private key.

Mandeep Gupta 0 Comments
Sep 16 2021
BIP – Update Sheet Name in RTF template for Excel output

Excel output in Fusion BIP can be generated using multiple ways i.e. either using excel template or RTF templates. When you use RTF templates to generate excel output, by default the data is generated in sheet with name “Sheet1:

If you want to rename Sheet1 to user defined value, then use the below expression in your RTF template:

<?spreadsheet-sheet-name: {‘Test’}?>

If you want it be dynamic, use below expression:

<?spreadsheet-sheet-name: {.//NAME}?>

Make the changes in RTF template and upload. The output file generated will have sheet name as Test.

Mandeep Gupta 0 Comments
Sep 12 2021
Configuration – Enable Location Based Access

By default Location Based Access is disabled. Navigate to Tools -> Administration and you will not find Location Based Access Tab:

To enable the location based access tab, set the profile option ‘ASE_ADMINISTER_LOCATION_BASED_ACCESS_CONTROL’ to Yes:

Once this is set, Location Based Access tab will start appearing in the Tools -> Administration:

Click Enable Location Based access checkbox to enable the Location Based access:

Make sure atleast one valid IP address is entered otherwise the application will become inaccessible.

Also, please make sure that you make IT Security Manager Role Public.

Configuration – How to make a role public? (LBAC)

By default you can enter only 10000 characters. If you want to extend this limit, please set the following profile option to Yes at site level:

ASE_EXTEND_LOCATION_BASED_ACCESS_CONTROL_IP_STORAGE

Mandeep Gupta 0 Comments
Sep 12 2021
Configuration – How to make a role public? (LBAC)

While implementing Location Based Access control for your application, make sure you set the IT Security Manager role as public. This will help you to access the application and the tasks in an event where a wrong list of IP’s has been supplied and no user is able to access the application.

Follow the below steps to make a role public:

  1. Enable to location based access from Tools -> Administration and check the ‘Enable Location Based Access’ checkbox under Location Based Access tab.
2. After that, from the Tools -> Roles -> Search for IT Security Manager role and click on Edit Role:
And check the ‘Enable Role for Access from All IP Addresses’ checkbox:
Keep clicking on next button and submit the changes.
Mandeep Gupta 0 Comments
Sep 12 2021
BIP – Query to extract Actions.dat data

Many a time there is a requirement to update/change the existing actions and actions reasons usages. Doing this manually in UI will become a cumbersome process for multiple actions and may lead to human errors as well.

For this, HCM Data Loader can used to update the changes using Actions.dat file.

Use the below SQL queries to extract Actions and Action Reason Usages data from your Pod in HDL format:

Actions:

Select 'MERGE|Actions|'
||
actb.action_code
||'|'||
actt.action_name
||'|'||
actb.action_type_code
||'|'||
to_char(actb.start_date, 'yyyy/mm/dd')
||'|'||
to_char(actb.end_date, 'yyyy/mm/dd')
||'|'||
map.source_system_id
||'|'||
map.source_system_owner ACTIONS_HDL
from
PER_ACTIONS_B actb,
PER_ACTIONS_TL actt,
hrc_integration_key_map map
where 1=1
and actb.action_id = actt.action_id
and actt.language = USERENV('LANG')
and actb.action_id = map.surrogate_id

Action Reason Usage:

Select 'MERGE|ActionReasonUsage|'
||
aru.action_code
||'|'||
aru.action_reason_code
||'|'||
to_char(aru.start_date, 'yyyy/mm/dd')
||'|'||
to_char(aru.end_date, 'yyyy/mm/dd')
||'|'||
km.source_system_id
||'|'||
km.source_system_owner ARC_HDL
from 
hrc_integration_key_map km,
PER_ACTION_REASON_USAGES aru
where 1=1
and aru.ACTION_REASON_USAGE_ID = km.surrogate_id

Copy and save the data as Actions.dat and do the required changes.

Sample File:

METADATA|Actions|ActionCode|ActionName|ActionTypeCode|StartDate|EndDate|SourceSystemId|SourceSystemOwner
MERGE|Actions|TEST_MANAGE_CONTRACT|Test Manage Contract|EMPL_CONTRACT_EXTN|1951/01/01|4712/12/31|300000072427734|FUSION

METADATA|ActionReasonUsage|ActionCode|ActionReasonCode|StartDate|EndDate|SourceSystemId|SourceSystemOwner
MERGE|ActionReasonUsage|TEST_MANAGE_CONTRACT|CHANGE_CONTRACT|1951/01/01|4712/12/31|300000072427735|FUSION

Link to refresh business objects to get latest attributes of Actions.dat:

https://fusionhcmconsulting.com/2021/01/hdl-refresh-business-objects/

Mandeep Gupta 0 Comments
Sep 12 2021
Configuration – Make a document type defaulted in search

When an employee opens document of records, by default it will show all the document types. There are many scenarios where the business wants to set a default filter to see only a particular type of document type. The other document types should be visible only if the default filter is removed.

Follow the below steps to achieve this:

  1. Create a sandbox and add Page composer tool.
  2. Navigate to Me -> Personal Information -> Document of Record (or navigation at your pod):

Click on show filters:

  • Enter Document Type as Payslip and click on Save button under Saved search:
  • Give a name and click on Ok:
  • Test the changes by click on Home icon and navigating back to same place(document of record). The data should be displayed as per new search criteria.
  • If changes look ok, publish the sandbox.
Mandeep Gupta 0 Comments
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
Jul 10 2021
Configuration – Value set to get Grade Steps based on Worker Grade

Assignment DFF supports a number of parameters which can be used to get dynamic values based on Worker assignment.

List of supported parameters for a DFF can be found using below link:

Configuration – How to get list of supported parameters for a DFF?

In this example, we will create a value set to display list of Grade steps based on Worker’s assigned grade. Create a Table validated value set using below:

FROM ClausePER_GRADE_STEPS_F_TL pgftl, PER_GRADE_STEPS_F pgsf,PER_GRADES_F pgf
Value Attributes Table Alias 
*Value Column NameSUBSTR(pgftl.NAME,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column NameSUBSTR(pgftl.NAME,1,150)
Description Column Type VARCHAR2
Description Column Length 150
ID Column NameSUBSTR(pgftl.NAME,1,150)
ID Column TypeVARCHAR2
ID Column Length150
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausepgftl.GRADE_STEP_ID=pgsf.GRADE_STEP_ID
     AND pgftl.language=’US’
AND trunc(sysdate) between trunc(pgftl.effective_start_date) and trunc(pgftl.effective_end_date)
AND pgsf.GRADE_ID=pgf.GRADE_ID
     AND trunc(sysdate) between trunc(pgsf.effective_start_date) and trunc(pgsf.effective_end_date)
     AND trunc(sysdate) between trunc(pgf.effective_start_date) and trunc(pgf.effective_end_date)
AND pgf.grade_id = :{PARAMETER.GRADE_ID}
ORDER BY ClauseSUBSTR(pgftl.NAME,1,150)
Mandeep Gupta 1 Comments