Search for:
Jun 14 2022
BIP – Restricting succession plans access to logged in person in BIP reports

Succession Plans data is stored in HRM_PLANS table. A succession plan can a PUBLIC or PRIVATE plan. By default if any user accesses HRM_PLANS table, user will be able to see all plans data irrespective of plan type (public or private). The requirement here is that a person who is querying data should be able to see all PUBLIC succession plans and only those PRIVATE plans which are defined by the user.

This can be achieved using below SQL:

select * from HRM_PLANS hp
 where hp.ACCESS_TYPE_CODE = 'PUBLIC'
UNION
select * from HRM_PLANS hp
 where hp.ACCESS_TYPE_CODE = 'PRIVATE'
   and exists (select 1 from HRM_PLAN_OWNERS hpo
                where person_id = hrc_session_util.get_user_personid
				  and hpo.plan_id = hp.plan_id)
Mandeep Gupta 0 Comments
Jun 14 2022
HCM Extracts – Passing multiple values in a parameter

You can pass multiple comma separated values in an HCM Extract parameter and then use below logic to separate the comma separated values into multiple values:

 SELECT * 
   FROM (SELECT trim(regexp_substr(param_person_number,'[^, ]+',1,LEVEL)) 
           FROM (SELECT pay_report_utils.get_parameter_value('PER_NUMBER') param_person_number 
		           FROM DUAL) 
		CONNECT BY regexp_substr(param_person_number,'[^, ]+', 1,LEVEL)>0)
Mandeep Gupta 0 Comments
Jun 14 2022
BIP – Query to extract Collective Agreement Detials
SELECT pcafv.collective_agreement_name
      ,TO_CHAR(pcafv.effective_start_date,'YYYY/MM/DD') Start_Date
      ,pcafv.legislation_code
      ,pcafv.status
      ,pcafv.identification_code
      ,pcafv.description
      ,pcafv.comments
      ,houf_union.name Union_Name
      ,pcafv.bargaining_unit_code
      ,ple.name Legal_Employer
      ,pcafv.employee_org_name
      ,pcafv.employee_org_contact
      ,pcafv.employer_org_name
      ,pcafv.employer_org_contact
  FROM per_col_agreements_f_vl pcafv
      ,hr_all_organization_units_f_vl houf_union
      ,per_legal_employers ple
 WHERE pcafv.legal_entity_id = ple.organization_id(+)
   AND pcafv.union_id = houf_union.organization_id(+)
   AND trunc(SYSDATE) BETWEEN pcafv.effective_start_date AND pcafv.effective_end_date
   AND trunc(SYSDATE) BETWEEN houf_union.effective_start_date(+) AND houf_union.effective_end_date(+)
   AND trunc(SYSDATE) BETWEEN ple.effective_start_date(+) AND ple.effective_end_date(+)

Mandeep Gupta 0 Comments
Jun 14 2022
BIP – Query to extract Calendar Event details

Calendar events are used in HCM to capture public holidays available to employees based on their location or department.

Below SQL queries can be used to extract the list of configured calendar events from backend table:

List of all calendar events configured:

SELECT pcetl.name
      ,pce.short_code
      ,pcetl.description
      ,pce.category
      ,pce.coverage_type 
      ,pce.start_date_time
      ,pce.end_date_time 
      ,pce.tree_code
      ,pce.tree_structure_code 
 FROM per_calendar_events pce,
      per_calendar_events_tl pcetl
WHERE pce.calendar_event_id=pcetl.calendar_event_id
  AND pcetl.LANGUAGE=userenv('LANG')

List of calendar events by location:

SELECT hla.location_id
      ,hla.location_name             
      ,hla.internal_location_code 
      ,pcet.name
	  ,pce.short_code
      ,pcet.description
      ,pce.category
      ,pce.coverage_type	  
	  ,pce.start_date_time
	  ,pce.end_date_time
      ,pce.tree_code
      ,pce.tree_structure_code 	  
 FROM hr_locations_all hla
     ,per_calendar_events pce
	 ,per_calendar_events_tl pcet
	 ,per_cal_event_coverage pcec
	 ,per_geo_tree_node_rf pgtnr
WHERE hla.geo_hierarchy_node_value = pgtnr.ancestor_pk1_value
  AND pgtnr.tree_structure_code = 'PER_GEO_TREE_STRUCTURE'
  AND pgtnr.distance in (0)
  AND pgtnr.ancestor_tree_node_id = pcec.tree_node_id
  AND pcec.calendar_event_id = pcet.calendar_event_id
  AND pcet.calendar_event_id = pce.calendar_event_id
  AND pcet.language = userenv('lang')
  AND pcec.coverage_flag in ('I')
  AND trunc(sysdate) between hla.effective_start_date and hla.effective_end_date
Mandeep Gupta 0 Comments