Search for:
BIP – Query to get Benefits Relationship details

Use below SQL to get benefits relationship name and status for a worker:

SELECT papf.person_number
      ,paam.assignment_number
      ,houft.name legal_entity_name
      ,bbrf.benefit_relation_name
      ,bbrf.status
      ,TO_CHAR(bbrf.effective_start_date,'YYYY/MM/DD') effective_start_date
      ,TO_CHAR(bbrf.effective_end_date,'YYYY/MM/DD') effective_end_date 
  FROM per_all_people_f papf 
      ,per_all_assignments_m paam  
      ,ben_benefit_relations_f bbrf
      ,hr_organization_units_f_tl houft
 WHERE 1 =1 
   AND bbrf.person_id = papf.person_id 
   AND paam.person_id = papf.person_id 
   AND bbrf.rel_prmry_asg_id = paam.assignment_id   
   AND bbrf.legal_entity_id = houft.organization_id 
   AND houft.LANGUAGE = 'US'
   AND paam.assignment_type NOT LIKE '%T'
   AND paam.effective_latest_change = 'Y'
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
 ORDER BY papf.person_number,paam.assignment_number,bbrf.effective_start_date
BIP – Error Exception happened when delivery document to wcc

Many a times when the reports are delivering the content to UCM server, we get below error:

Document delivery failed
[INSTANCE_ID=bip.bi_server1] [DELIVERY_ID=-1]Exception happened when delivery document to wcc
deliver API call throw ProcessingException

WCC delivery failed with Exception
WCC delivery failed with DeliveryException
oracle.xdo.service.delivery.DeliveryException: oracle.j2ee.ws.client.jaxws.JRFSOAPFaultException: Client received SOAP Fault from server : InvalidSecurity : error in processing the WS-Security security header
oracle.xdo.service.delivery.DeliveryException: oracle.xdo.service.delivery.DeliveryException: oracle.j2ee.ws.client.jaxws.JRFSOAPFaultException: Client received SOAP Fault from server : InvalidSecurity : error in processing the WS-Security security header
at oracle.xdo.service.delivery.impl.DeliveryServiceImpl.deliverToWCC(DeliveryServic

In this case look at the password for account used to connect to UCM server. If the password is reset make sure it is updated at the UCM connection as well.

HDL – Loading Person Profile Items

In order to load person profile items, a person should have a profile code. There are two ways to create the profile code for a person record.

  1. From UI :- When any business user clicks on Talent Profile for a worker, a profile code is automatically generated in the backend. Profile code is not visible in UI and is always stored in the backend.
  2. Using HDL :- Profile codes can be loaded in bulk using TalentProfile.dat business object of HCM Data Loader (HDL).

It is advisable to load profile codes in bulk as part of data migration using HDL. But there are cases where a user will click on Talent Profile of a worker just after migrating the Core data for the worker. In this case, a profile code is generated in HRT_PROFILES_B table. So, when TalentProfile.dat is used, the profile record will fail for this particular worker.

Below SQL can be used to get list of all active workers who don’t have a talent profile code yet:

select * 
  from per_all_people_f papf
     , per_periods_of_service ppos
 where papf.person_id = ppos.person_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and ppos.actual_termination_date is NULL
   and not exists (select 1 from HRT_PROFILES_B hpb where papf.person_id = hpb.person_id )
HDL – Sample file to end date element entry value

All recurring element entires are loaded/ created with an effective end date of 31-Dec-4712. But I have seen scenarios, where the business have requirements to end date an element entry as of a certain date. To achieve this in Bulk, one can use ElementEntry.dat HDL business object.

Let us take an example, where an employee has an element entry with an effective end date of 31-Dec-4712:

The effective end date is set to blank which is equivalent to 31-Dec-4712 in the backend table.

So, let us assume the business has requested to end date this particular element as of 31-Jan-2024 for all employees. To achieve, this we need to pull the existing data from element entries table for this particular element. Below SQL can be used to get the ID values and to verify the results before and after HDL load:

SELECT DISTINCT peevf.element_entry_value_id
       ,peef.element_entry_id
       ,petf.base_element_name
       ,peef.effective_start_date ele_sd
       ,peef.effective_end_date ele_ed
       ,peevf.effective_start_date
       ,peevf.effective_end_date
       ,paam.assignment_number
   FROM per_all_assignments_m paam
       ,pay_element_types_f petf
       ,pay_element_entries_f peef
       ,pay_element_entry_values_f peevf
 WHERE 1=1
   AND paam.person_id = peef.person_id
   AND peef.element_type_id = petf.element_type_id
   AND peef.element_entry_id = peevf.element_entry_id
   AND paam.ASSIGNMENT_TYPE in ('E')
   AND paam.primary_assignment_flag = 'Y'
   AND petf.base_element_name = 'Test XYZ Bonus'
   AND paam.assignment_number = 'E2121212'
   AND trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
   AND trunc(sysdate) between paam.effective_start_date and paam.effective_end_date

A normal HDL file with new effective end date will just create a date split in the data. To avoid this a new attribute called ReplaceLastEffectiveEndDate should be added in the HDL file which will updated the effective end date from 31-Dec-4712 to 31-Jan-2024.

METADATA|ElementEntry|AssignmentNumber|ElementName|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|MultipleEntryCount|EntryType|ReplaceLastEffectiveEndDate

MERGE|ElementEntry|E2121212|Test XYZ Bonus|2012/01/31|2024/01/31|GB Legislative Data Group|1|E|Y

Once this HDL is run successfully, the effective end date will get updted.

BIP – Extract Address Style Format Information

You use “Manage Address Formats” task from Setup and Maintenance to define the address style for a particular country. Oracle provides a lot of address format for various countries out of the box. You have the capaibility to customize the address formats as per customer requirement.

For example, you can search for all available address style for United Kingdom using country filter:

You can see below details:

You can edit the address format to customize the address style. You can make a seeded attribute mandatory/ non-mandatory or can add a new address element and many more properties can be set:

You can save the changes. And the address format become active.

One of the main challenges, the data migration team and the integration teams face is the mapping of layout fields to actual backend table attribute in per_addresses_f table.

There is no direct way to find the UI attribute name and its corresponding attribute name in backend table. Below query has been developed to find the UI prompt with actual table attribute name:

SELECT ftt.TERRITORY_SHORT_NAME
      ,hsfl.TERRITORY_CODE
      ,hsflb.STYLE_FORMAT_CODE
      ,hsflb.VARIATION_NUMBER
      ,hsflt.PROMPT	  
      ,hsflb.ATTRIBUTE_CODE
      ,hsflb.LINE_NUMBER
      ,hsflb.MANDATORY_FLAG
      ,hsflb.USE_INITIAL_FLAG
      ,hsflb.UPPERCASE_FLAG
      ,hsflb.STATUS_FLAG
      ,hsflb.TRANSFORM_FUNCTION
      ,hsflb.DELIMITER_BEFORE
      ,hsflb.DELIMITER_AFTER
      ,hsflb.BLANK_LINES_BEFORE
      ,hsflb.BLANK_LINES_AFTER
      ,hsflb.START_DATE_ACTIVE
      ,hsflb.END_DATE_ACTIVE
      ,hsflb.DISPLAY_SIZE
  FROM HZ_STYLE_FMT_LAYOUTS_B hsflb 
      ,HZ_STYLE_FMT_LAYOUTS_TL hsflt
      ,HZ_STYLE_FMT_LOCALES hsfl
      ,FND_TERRITORIES_TL ftt
 WHERE hsflb.STYLE_FORMAT_CODE = hsfl.STYLE_FORMAT_CODE
   AND hsflb.STYLE_FMT_LAYOUT_ID = hsflt.STYLE_FMT_LAYOUT_ID
   AND ftt.TERRITORY_CODE = hsfl.TERRITORY_CODE 
   AND ftt.LANGUAGE = 'US'
   AND ftt.LANGUAGE = hsflt.LANGUAGE
   AND ftt.TERRITORY_CODE = 'MX'
ORDER BY hsflb.STYLE_FORMAT_CODE, hsflb.LINE_NUMBER

Output after running above SQL:

Please note:

COUNTY – REGION_1

STATE – REGION_2

PROVINCE – REGION_3

HDL – Sample file to update Grade name and Grade Step Name

File for Grade Name change:

METADATA|Grade|GradeId|GradeName|GradeCode|SetCode|EffectiveStartDate|EffectiveEndDate
MERGE|Grade|30001221212|New Grade Name|GRADE_9|COMMON|1951/01/01|

File for Grade Step Name change:

METADATA|GradeStep|GradeStepId|GradeId|GradeStepName|EffectiveStartDate|EffectiveEndDate
MERGE|GradeStep|30001451452|30001221212|New GSN Level 1|1951/01/01|

Save the files as Grade.dat and upload them.

Approvals – Remove Approve/ Reject buttons from Notifications

There are many scenarios where the users don’t want to have Approve/ Reject buttons on the Bell Notifications or the notification received over email.

This can be achieved quite easily using simple settings in BPM.

To remove the Approve/Reject buttons from email:- Disable the “Make Notifications Actionable” option under the appropriate task.

To remove the Approve/Reject buttons from worklist notifications:– Remove task actions from Actions under Access for the particular task:

Make sure to Commit the changes once you make the above suggested changes.

HDL – Sample HDL file to End Date Position Valid Grades

There can multiple grades assigned as Valid grade at Position level or Job level. However, there is no direct way to end date the valid grades in bulk.

You can end date a valid grade from responsive UI. But it will be a lot of manual effort. You can search for Position and then navigate to Grades section. Then update the position and click on small delete icon next to Grade name (which you want to end date). This will end date the valid grade with an effective end date = date of position update – 1.

To do this in bulk using HDL, you can’t use DELETE command. If you use DELETE command, it will completely Purge the valid grade record from Position. To end date the valid grade, use “ReplaceLastEffectiveEndDate” attribute in the file.

Below is the sample file:

METADATA|PositionGrade|BusinessUnitName|PositionCode|EffectiveStartDate|EffectiveEndDate|GradeCode|GradeSetCode|ReplaceLastEffectiveEndDate
MERGE|PositionGrade|Progress US Business Unit|PRGUSPOS032|2018/12/31|2023/12/31|Hourly01|PRGUSGRADESET|Y

EffectiveStartDate – Earliest Grade Start Date

EffectiveEndDate – Date on which you want to end date the grade.

Once the file is loaded successfully, below is how the data will look in the backend:

Below SQL query can be used to extract valid grades data:

SELECT DISTINCT 
	   TO_CHAR (pvgf.effective_start_date, 'DD/MON/YYYY') effective_start_date,
	   TO_CHAR (pvgf.effective_end_date, 'DD/MON/YYYY') effective_end_date,
	   pjfv.POSITION_CODE,
	   pjfv.name job_name,
	   pgfv.grade_code,
	   pgfv.name grade_name,
	   pvgf.valid_grade_id,
	   pgfv.grade_id,
	   pjfv.job_id
  FROM per_valid_grades_f pvgf,
       HR_ALL_POSITIONS_F_VL pjfv,
       per_grades_f_vl pgfv 
 WHERE 1=1
   AND pvgf.position_id = pjfv.position_id
   AND pvgf.grade_id = pgfv.grade_id
   AND pjfv.POSITION_CODE = 'PRGUSPOS032'
   AND pvgf.effective_start_date BETWEEN pjfv.effective_start_date AND pjfv.effective_end_date
   AND pvgf.effective_start_date BETWEEN pgfv.effective_start_date AND pgfv.effective_end_date
ORDER BY POSITION_CODE,grade_code