Tag: Report - Page 3

Reports

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

HDL Reports

May 21 2022

HDL – Sample HDL to load HCM Group Members

Oracle provides HCMGroup.dat which can be used to assign members against HCM Group. However, the business object has a major limitation (till release version 22A) that it can be used only for below two seeded Oracle HCM Groups:

Manually Maintained Excluded Members
Manually Maintained Audit Members

Below is the sample HDL file to add members to Audit HCM Group:

METADATA|HCMGroup|GrpMemberId|EffectiveStartDate|GrpId|MemberId|GroupCode|PersonNumber|SourceSystemOwner|SourceSystemId|GUID|EffectiveEndDate
MERGE|HCMGroup||2022/01/04|||MANUALLY MAINTAINED AUDIT MEMBERS|1014|HRC_SQLLOADER|MANUALLY MAINTAINED AUDIT MEMBERS_1014||

For any custom HCM Groups, this can’t be used. If you try to use any custom Group Code in the HDL file, you will receive below error:

Valid values for attribute GROUP CODE: MANUALLY MAINTAINED AUDIT MEMBERS,MANUALLY MAINTAINED EXCLUDED MEMBERS.

Query to extract members of an HCM Group:

SELECT hgt.group_name
      ,papf.person_id
      ,papf.person_number
      ,ppnf.full_name
      ,ppnf.last_name
      ,ppnf.first_name
      ,hgmf.effective_start_date
      ,hgmf.effective_end_date
  FROM PER_ALL_PEOPLE_F papf,
       PER_PERSON_NAMES_F ppnf,
       HWM_GRP_MEMBERS_F hgmf,
       HWM_GRPS_B hgb,
       HWM_GRPS_TL hgt
 WHERE hgb.grp_id = hgmf.grp_id
   AND hgb.grp_id = hgt.grp_id 
   AND hgt.language = 'US'
   AND papf.person_id = hgmf.member_id
   AND papf.person_id = ppnf.person_id
   AND ppnf.name_type ='GLOBAL'
   AND trunc(sysdate) BETWEEN hgmf.effective_start_date and hgmf.effective_end_date
   AND trunc(sysdate) BETWEEN ppnf.effective_start_date and ppnf.effective_end_date
ORDER BY 1,3

Mandeep Gupta 0 Comments

HDL Reports

May 15 2022

HDL – Talent Profile HDL related useful queries

Loading the profile data against a worker/job/position is little tricky one. One has to understand the templates thoroughly before uploading any data.

Below are some of the most commonly used queries to extract some of the required information for loading Talent profiles data.

Extract Person Profile Code:

SELECT distinct papf.person_id, papf.person_number,ProfilePEO.profile_code
  FROM HRT_PROFILES_VL ProfilePEO, PER_ALL_PEOPLE_F papf
 WHERE papf.person_id = ProfilePEO.person_id
    AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date and papf.effective_end_date

Extract Profile Type Id:

Select * from HRT_PROFILE_TYPES_TL

Mandeep Gupta 0 Comments

Reports

May 15 2022

BIP – Extract environment url dynamically

For many BIP reports, it is required to publish the environment url. One can easily hardcode it in SQL query, however, when P2T or T2T happens, the same url gets copied to target instance which is incorrect. The better way of doing it is to use below piece of code in custom SQL to extract environment url dynamically:

SELECT 'https://'||EXTERNAL_VIRTUAL_HOST INSTANCE_NAME
  FROM FUSION.ASK_DEPLOYED_DOMAINS
 WHERE DEPLOYED_DOMAIN_NAME = 'FADomain'

Mandeep Gupta 0 Comments

Reports

May 12 2022

BIP – Extract Value Set values Information

SELECT
    fvvs.value_set_code
   ,fvvv.value
   ,fvvv.description
   ,fvvv.enabled_flag
   ,to_char(fvvv.start_date_active,'YYYY/MM/DD') start_date_active
   ,to_char(fvvv.end_date_active,'YYYY/MM/DD') end_date_active
   ,fvvv.summary_flag
   ,fvvv.translated_value
   ,fvvv.independent_value
   ,fvvv.independent_value_number
   ,to_char(fvvv.independent_value_date,'YYYY/MM/DD') independent_value_date
   ,fvvv.independent_value_timestamp
   ,fvvv.value_number
   ,to_char(fvvv.value_date,'YYYY/MM/DD') value_date
   ,fvvv.value_timestamp
   ,fvvv.sort_order 
FROM
    fnd_vs_values_vl       fvvv,
    fnd_vs_value_sets      fvvs
WHERE    fvvv.value_set_id = fvvs.value_set_id
order by fvvs.value_set_code

Mandeep Gupta 0 Comments

Reports

Apr 29 2022

BIP – Extract Salary basis with associated element details

  SELECT csb.name
		,csb.salary_basis_code
		,csb.salary_annualization_factor
		,pld.name LDG
		,petft.element_name
		,pivt.name input_value_name
		,rate.name rate_name
    FROM cmp_salary_bases csb,
	     per_legislative_data_groups_tl pld,
	     pay_element_types_tl petft,
	     pay_input_values_f pivf,
	     pay_input_values_tl pivt,
	     per_rates_f_tl rate
   WHERE 1=1
     and csb.legislative_data_group_id = pld.legislative_data_group_id
     and pld.language = USERENV('LANG')
     and csb.element_type_id = petft.element_type_id
    -- and trunc(sysdate) between petft.effective_start_date and petft.effective_end_date
     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 = rate.rate_id(+)
     and NVL(rate.language,USERENV('LANG')) = USERENV('LANG')
ORDER BY 1

Mandeep Gupta 0 Comments

HDL Reports

Apr 21 2022

BIP – Query to DELETE Salary data using HDL

There may be a requirement to delete salary data while doing HDL iterations for the first time. You can use below query for the same purpose

Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId' Header, 1 data_flow_order
from dual
UNION
SELECT 'DELETE|Salary'||'|'||
paam.assignment_number||'|'||
SALARY_AMOUNT||'|'||
TO_CHAR(cs.date_from,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
TO_CHAR(cs.date_to,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
cs.salary_basis_id||'|'||
cs.salary_id data_row,
2 data_flow_order
FROM cmp_salary cs,
per_all_assignments_m paam
WHERE cs.assignment_id= paam.assignment_id
AND trunc(sysdate) between paam.effective_start_date AND paam.effective_end_date
AND paam.assignment_type in ('E', 'C', 'P')
AND trunc(cs.creation_date) < trunc(sysdate)-1
ORDER BY data_flow_order

Mandeep Gupta 0 Comments

Mar 21 2022

HDL – Loading Competencies against Worker

TalentProfile.dat can be used to create worker talent profiles and loading content items against the talent profiles. While some of the content items load is straight forward, loading competencies is little tricky one. This is because there are some additional fields like QualifierId1 and QualifierId2 which should be supplied to make the loaded data available on UI.

Sample HDL file to load competencies:

METADATA|ProfileItem|ProfileItemId|ProfileId|ProfileCode|ContentItemId|ContentTypeId|ContentType|CountryId|DateFrom|DateTo|RatingModelId1|RatingModelCode1|RatingLevelId1|RatingLevelCode1|RatingModelId2|RatingModelCode2|RatingLevelId2|RatingLevelCode2|SectionId|SourceSystemOwner|SourceSystemId|QualifierId1|QualifierId2|ItemDate6|ContentItem
MERGE|ProfileItem|||PERSON_112211||104|COMPETENCY||2022/02/07||300000236131123||||300000236131123|PERFORMANCE||3|300000236137110|HRC_SQLLOADER|PERSON_112211|300000236137121|100000086708123|2018/08/18|Leadership Skills

Use the below query to extract qualifiers data:

select QUALIFIER_ID,DESCRIPTION
  from FUSION.HRT_QUALIFIERS_VL
 where QUALIFIER_SET_ID in (
select QUALIFIER_SET_ID from              fusion.HRT_QUALIFIER_SETS_VL where SECTION_ID = 300000236137110)

Sample data from above query:

By default on UI, View By is set to “Official”:

Navigation :- Person Management -> Talent Profile

So, if you pass QualifierId1 as something different from the Id for “Official”, the loaded data will not be visible. Make sure to set “View By” to All.

Below is the high level description of some of the main attributes of ProfileItem business object:

ProfileId – profile Id of the person. If you don’t have it, pass it blank and pass the value under ProfileCode.

ProfileCode – Use this if you don’t know ProfileId (above).

DateFrom – From Date

RatingLevelCode2 – Pass values 1,2,3,4 or 5 based on your rating level (under rating model you can see).

SourceSystemId – Pass same as Profile Id or Profile Code

ItemDate6 – Review Date

ContentItem – Competency Name

QualifierId1 – Qualifier_id for an appropriate evaluator type. Can be used from the query above.

QualifierId2 – Person Id corresponding to evaluator type. For example, if using qualifier_id for Self, pass person_id of the worker in qualifierId2.

Mandeep Gupta 0 Comments

HDL Reports

Mar 18 2022

BIP – Query to extract Rating Model Levels in HDL Format

This is second post in a series to extract Rating Model data from Fusion environment in HDL format. The first post has a query to extract only the rating model data and second post describes the BIP query to extract rating model levels data.

Query to extract Rating Model data:

BIP – Query to extract Rating Model data in HDL Format

Query to extract Rating Model levels data:

SELECT 'METADATA|RatingLevel|RatingModelCode|RatingLevelCode|CareerStrDev|RatingDescription|DateFrom|FromPoints|MaximumRatingDistribution|MinimumRatingDistribution|NumericRating|ReviewPoints|ReviewRatingDescr|RatingShortDescr|StarRating|DateTo|ToPoints|SourceSystemId|SourceSystemOwner' DATA_LINE, 1 ORDER_NUM
  FROM DUAL
UNION
select
'MERGE'
||'|'||
'RatingLevel'
||'|'||
hrmb.rating_model_code
||'|'||
hrlv.rating_level_code
||'|'||
hrlv.career_str_dev
||'|'||
hrlv.rating_description
||'|'||
TO_CHAR(hrlv.date_from,'YYYY/MM/DD')
||'|'||
hrlv.from_points
||'|'||
hrlv.max_rating_distribution
||'|'||
hrlv.min_rating_distribution
||'|'||
hrlv.numeric_rating
||'|'||
hrlv.review_points
||'|'||
hrlv.review_rating_descr
||'|'||
hrlv.rating_short_descr
||'|'||
hrlv.star_rating
||'|'||
TO_CHAR(hrlv.date_to,'YYYY/MM/DD')
||'|'||
hrlv.to_points
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner DATA_LINE, 2 ORDER_NUM
FROM hrt_rating_models_b hrmb
   , hrt_rating_levels_vl hrlv
   , hrc_integration_key_map hikm
WHERE hrmb.rating_model_id = hrlv.rating_model_id
  AND hikm.surrogate_id = hrlv.rating_level_id

Mandeep Gupta 0 Comments

HDL Reports

Mar 18 2022

BIP – Query to extract Rating Model data in HDL Format

You can use below query to extract Rating Model data from fusion environment for updates.

SELECT 'METADATA|RatingModel|RatingModelCode|DateFrom|DateTo|RatingName|RatingDescription|SourceSystemId|SourceSystemOwner' DATA_LINE, 1 ORDER_NUM
  FROM DUAL
UNION
select
'MERGE'
||'|'||
'RatingModel'
||'|'||
hrmb.rating_model_code
||'|'||
TO_CHAR(hrmb.date_from,'YYYY/MM/DD')
||'|'||
TO_CHAR(hrmb.date_to,'YYYY/MM/DD')
||'|'||
rtl.rating_name
||'|'||
rtl.rating_description
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner  DATA_LINE, 2 ORDER_NUM
FROM hrt_rating_models_b hrmb,
     hrt_rating_models_tl rtl,
     hrc_integration_key_map hikm
WHERE hrmb.rating_model_id = rtl.rating_model_id
  AND hikm.surrogate_id = hrmb.rating_model_id
  AND rtl.language = 'US'
ORDER BY ORDER_NUM

Save the data as RatingModel.dat before uploading to Fusion. You can make use of this query to extract data for reference or use it as a method to extract data from one instance and upload it to a different instance.

Check below post for sample RatingModel HDL file:

https://fusionhcmconsulting.com/2022/03/hdl-sample-file-to-upload-rating-model/

Mandeep Gupta 0 Comments

Reports

Feb 25 2022

BIP – Query to check if employee bank account is active or not

select ext_bank_account_id
,case when nvl(end_date,trunc(sysdate)) >= trunc(sysdate)
 then 'Active'
 else 'Inactive'
end active_inactive
from IBY_EXT_BANK_ACCOUNTS

Mandeep Gupta 0 Comments

HDL Reports

Feb 25 2022

HDL – Sample HDL to Update User Table value

SQL Query to extract data for a particular value of a user table:

SELECT DATA
FROM (
SELECT 'METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId' DATA, 1 DATA_ROW
FROM DUAL
UNION ALL
SELECT 
'MERGE'
||'|'||
'UserDefinedTableColumnInstance'
||'|'||
to_char(fuci.EFFECTIVE_END_DATE,'YYYY/MM/DD')
||'|'||
to_char(fuci.EFFECTIVE_START_DATE,'YYYY/MM/DD')
||'|'||
fuci.LEGISLATIVE_DATA_GROUP_ID
||'|'||
fuci.USER_COLUMN_ID
||'|'||
fuci.USER_COLUMN_INSTANCE_ID
||'|'||
fuci.USER_ROW_ID
||'|'||
fuci.Value
||'|'||
hikm.source_system_owner  
||'|'||
hikm.source_system_id
||'|'||
fuc.USER_TABLE_ID
 DATA, 2 DATA_ROW
FROM 
FF_USER_COLUMN_INSTANCES_F fuci,
FF_USER_COLUMNS fuc,
FF_USER_TABLES ft,
HRC_INTEGRATION_KEY_MAP hikm
WHERE fuci.USER_COLUMN_ID=fuc.USER_COLUMN_ID
AND fuc.USER_TABLE_ID=ft.USER_TABLE_ID
AND hikm.surrogate_id = fuci.USER_COLUMN_INSTANCE_ID
AND ft.BASE_USER_TABLE_NAME = 'TEST_WAGE'
) ORDER BY DATA_ROW

Sample HDL:

METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId

MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|300000046974912|300000175181219|300000175188123|300000175187989|5.20|FUSION|300000175144123|300000175181333

Mandeep Gupta 0 Comments

Reports

Feb 25 2022

BIP – Query to extract Person Address Details

select

distinct

PAPF.PERSON_NUMBER,

PAPF.PERSON_ID,

PPAUF.PERSON_ADDR_USAGE_ID "ADDRESS_ID",

TO_CHAR(PA.EFFECTIVE_START_DATE,'YYYY/MM/DD')"EFFECTIVE_START_DATE",

TO_CHAR(PA.EFFECTIVE_END_DATE,'YYYY/MM/DD')"EFFECTIVE_END_DATE",

PPAUF.ADDRESS_TYPE,

PA.ADDRESS_LINE_1,

PA.ADDRESS_LINE_2,

PA.ADDRESS_LINE_3

from

PER_ADDRESSES_F PA,

PER_ALL_PEOPLE_F PAPF,

PER_PERSON_ADDR_USAGES_F PPAUF

where

  PAPF.PERSON_ID = PPAUF.PERSON_ID(+)

AND PPAUF.ADDRESS_ID = PA.ADDRESS_ID(+)

Mandeep Gupta 0 Comments

HDL Reports

Jan 25 2022

HDL – Sample file to update user name to email address

SELECT 'METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner' Headerrow, 1 dataorder
FROM DUAL
UNION
SELECT 'METADATA|User'
      ||'|'||papf.person_number 
      ||'|'||pea.email_address
      ||'|'||hikm.source_system_id
      ||'|'||hikm.source_system_owner Headerrow, 2 dataorder
  FROM per_all_people_f papf
      ,per_email_addresses pea
	  ,per_users pu
	  ,hrc_integration_key_map hikm
 WHERE papf.person_id = pea.person_id
   and pu.person_id = pea.person_id
   and pu.user_id = hikm.surrogate_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
order by dataorder

Sample File:

METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner
MERGE|User|11101|[email protected]|300000120863888|FUSION

Mandeep Gupta 1 Comments

Jan 25 2022

HDL – Query to generate worker email in HDL format

select 'METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|EmailType|PrimaryFlag|EmailAddress|SourceSystemId|SourceSystemOwner'
  from dual
UNION ALL
select 'MERGE|PersonEmail' 						||'|'||												
       NULL 									||'|'||										
       papf.person_id							||'|'||								
       to_char(ppos.date_start,'RRRR/MM/DD')	||'|'||					    
       'W1' 									||'|'||				
	   'Y'													||'|'||		
       'sendmail-discard_'||papf.person_number||'@xyz.com' 	||'|'||
      'PER_EMAIL_'||papf.person_number						||'|'||
      'HRC_SQLLOADER' 												
  from per_all_people_f papf
      ,per_periods_of_service ppos
 where 1=1
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and papf.person_id = ppos.person_id
   and ppos.date_start = (select MAX(ppos2.date_start) from per_periods_of_service ppos2
                           where ppos2.person_id = ppos.person_id)

Sample HDL File:

METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|EmailType|PrimaryFlag|EmailAddress|SourceSystemId|SourceSystemOwner
MERGE|PersonEmail||100000015400564|2021/03/15|W1|Y|[email protected]|PER_EMAIL_00603|HRC_SQLLOADER

Mandeep Gupta 0 Comments

HDL Reports

Dec 28 2021

BIP – Extract Absence Entry data in HDL Format

The below query extracts the minimum required attributes for absence entry update. In the current example, the absence status is updated to Withdrawn. You can make the changes as per your need:

SELECT DATA_ROW
FROM (
SELECT 'METADATA'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || 'PerAbsenceEntryId'
|| CHR (124) || 'AbsenceStatus'
|| CHR (124) || 'AbsenceTypeId'
|| CHR (124) || 'AssignmentId'
|| CHR (124) || 'EmployerId'
|| CHR (124) || 'PersonId'
|| CHR (124) || 'StartDate'
|| CHR (124) || 'EndDate'
|| CHR (124) || 'SourceSystemId' 
|| CHR (124) || 'SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select  'MERGE'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || apae.per_absence_entry_id
|| CHR (124) || 'ORA_WITHDRAWN'
|| CHR (124) || apae.absence_type_id
|| CHR (124) || apae.assignment_id
|| CHR (124) || apae.legal_entity_id
|| CHR (124) || apae.person_id
|| CHR (124) || to_char(apae.start_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.end_date,'RRRR/MM/DD')
|| CHR (124) || hikm.source_system_id
|| CHR (124) || hikm.source_system_owner
FROM ANC_PER_ABS_ENTRIES apae,
	 HRC_INTEGRATION_KEY_MAP hikm
WHERE hikm.surrogate_id = apae.per_absence_entry_id
  --AND apae.absence_type_id = 3000012211
   AND apae.per_absence_entry_id = 300000079292534
)

Sample HDL File:

METADATA|PersonAbsenceEntry|PerAbsenceEntryId|AbsenceStatus|AbsenceTypeId|AssignmentId|EmployerId|PersonId|StartDate|EndDate|SourceSystemId|SourceSystemOwner
MERGE|PersonAbsenceEntry|300000079292534|ORA_WITHDRAWN|300000076789541||300000048608295|300000049253269|2014/06/11|2014/06/13|300000079292534|FUSION

Mandeep Gupta 0 Comments

Reports

Dec 20 2021

BIP – Query to extract child positions for a logged in person’s position

Fusion HCM provides you the capability of using Position Trees as well as Position Hierarchies. In case you need to extract child position details based on position id of logged in person, you can make use of below query:

SELECT pphf.position_id
  FROM per_position_hierarchy_f pphf
 WHERE 1=1
  START WITH pphf.parent_position_id = NVL((select position_id from per_all_assignments_m paam
                                         where paam.assignment_type = 'E'
					   and paam.person_id = hrc_session_util.get_user_personid
					   and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
					   and paam.effective_latest_change = 'Y'
					   and paam.effective_sequence = 1),300000141634831)
CONNECT BY PRIOR pphf.parent_position_id = 	pphf.position_id

Mandeep Gupta 0 Comments

Reports

Dec 16 2021

BIP – Extract Length of service

Use the below query to extract the length of service of a worker. In case of rehires this becomes little tricky. Below query makes use of earliest hire date for length of service calculation. If Seniority dates are implemented, then you can avoid using below query:

select papf.person_number, papf.person_id, trunc(months_between(trunc(sysdate),MIN(ppos.date_start))/12,2) LOS
  from per_periods_of_Service ppos
      ,per_all_people_f papf
 where ppos.person_id = papf.person_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
group by papf.person_number, papf.person_id

Mandeep Gupta 0 Comments

Reports

Dec 16 2021

BIP – Query to extract department Tree

Use the following query to extract department tree and its child departments. Please pass correct department name before executing:

WITH org_tree
AS (
        SELECT /*+ materialize */
               DISTINCT *
        FROM (
               SELECT  (
                               SELECT haoufv_p.name
                                 FROM hr_all_organization_units_f_vl haoufv_p
                                WHERE haoufv_p.organization_id = potnv.parent_organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_p.effective_start_date AND haoufv_p.effective_end_date
                        ) parent_org_name
                      ,(
                               SELECT haoufv_c.name
                                 FROM hr_all_organization_units_f_vl haoufv_c
                                WHERE haoufv_c.organization_id = potnv.organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_c.effective_start_date AND haoufv_c.effective_end_date
                        ) child_org_name
                       ,potnv.tree_structure_code
                       ,potnv.parent_organization_id parent_org_id
                       ,potnv.organization_id child_org_id
                       ,LEVEL levelcount
               FROM per_dept_tree_node_v potnv
			       ,fnd_tree_version ftv
              WHERE potnv.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
			    AND potnv.tree_code = 'Global100'
				AND potnv.tree_version_id = ftv.tree_version_id
				AND ftv.tree_code = potnv.tree_code
				AND ftv.status = 'ACTIVE' 
				AND TRUNC(SYSDATE) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
			   START WITH potnv.parent_organization_id IS NULL 
			 CONNECT BY PRIOR potnv.organization_id = potnv.parent_organization_id
               )
        ORDER BY levelcount ASC
        )
,dept_tree
 AS (
        SELECT /*+ materialize */
              level1.child_org_name "level1"
             ,level2.child_org_name "level2"
             ,level3.child_org_name "level3"
             ,level4.child_org_name "level4"
         FROM org_tree level1
		     ,org_tree level2
		     ,org_tree level3
		     ,org_tree level4
			 ,hr_all_organization_units_f haouf
       WHERE level1.child_org_id = level2.parent_org_id
	     AND level2.child_org_id = level3.parent_org_id
	     AND level3.child_org_id = level4.parent_org_id
         AND level1.parent_org_name IS NULL
		 AND haouf.organization_id = level4.child_org_id
		 AND TRUNC(SYSDATE) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
        )
SELECT *
FROM dept_tree

Mandeep Gupta 0 Comments

Reports

Nov 29 2021

BIP – Query to extract questions related to Job Requisition

SELECT DISTINCT iirq.REQUISITION_ID
	  ,hqb.QUESTIONNAIRE_ID 
      ,irb.REQUISITION_NUMBER
      ,hqb.QUESTIONNAIRE_CODE
      ,hqb.STATUS
	  ,hct.name		"Category"
	  ,hst.name		"Subscriber"
	  ,hqb.MAX_POSSIBLE_SCORE
	  ,hqnb.question_id
	  ,hqt.question_text
	  ,hqnb.QSTN_VERSION_NUM
	  ,hqnb.QUESTION_CODE
	  ,hqnb.QUESTION_TYPE
  FROM IRC_IM_REQ_QSTNRS iirq
      ,IRC_REQUISITIONS_B irb
      ,HRQ_QUESTIONNAIRES_B hqb
	  ,HRQ_CATEGORIES_TL hct
	  ,HRQ_SUBSCRIBERS_TL hst
	  ,HRQ_QSTNR_SECTIONS_B hqsb
      ,HRQ_QSTNR_QUESTIONS hqq
      ,HRQ_QUESTIONS_B hqnb
      ,HRQ_QUESTIONS_TL hqt
WHERE iirq.REQUISITION_ID = irb.REQUISITION_ID
  AND irb.REQUISITION_ID = 300000237266130
  AND iirq.QUESTIONNAIRE_ID = hqb.QUESTIONNAIRE_ID
  AND hqb.category_id = hct.category_id
  AND hct.language = 'US'
  AND hqb.subscriber_id = hst.subscriber_id
  AND hst.language = 'US'
  AND hqsb.QUESTIONNAIRE_ID     = hqb.QUESTIONNAIRE_ID
  AND hqsb.QSTNR_VERSION_NUM    = hqb.QSTNR_VERSION_NUM
  AND hqsb.BUSINESS_GROUP_ID    = hqb.BUSINESS_GROUP_ID
  AND hqq.QSTNR_SECTION_ID      = hqsb.QSTNR_SECTION_ID
  AND hqq.BUSINESS_GROUP_ID     = hqsb.BUSINESS_GROUP_ID
  AND hqq.QUESTION_ID           = hqnb.QUESTION_ID
  AND hqq.QSTN_VERSION_NUM      = hqnb.QSTN_VERSION_NUM
  AND hqq.BUSINESS_GROUP_ID     = hqnb.BUSINESS_GROUP_ID
  AND hqnb.QUESTION_ID          = hqt.QUESTION_ID 
  AND hqt.language = 'US'

Mandeep Gupta 0 Comments

MSPL

MSPL

BIP – Query to extract Calendar Event details

HDL – Sample HDL to load HCM Group Members

HDL – Talent Profile HDL related useful queries

BIP – Extract environment url dynamically

BIP – Extract Value Set values Information

BIP – Extract Salary basis with associated element details

BIP – Query to DELETE Salary data using HDL

HDL – Loading Competencies against Worker

BIP – Query to extract Rating Model Levels in HDL Format

BIP – Query to extract Rating Model data in HDL Format

BIP – Query to check if employee bank account is active or not

HDL – Sample HDL to Update User Table value

BIP – Query to extract Person Address Details

HDL – Sample file to update user name to email address

HDL – Query to generate worker email in HDL format

BIP – Extract Absence Entry data in HDL Format

BIP – Query to extract child positions for a logged in person’s position

BIP – Extract Length of service

BIP – Query to extract department Tree

BIP – Query to extract questions related to Job Requisition

MSPL

MSPL

Tag Archives: Report