Search for:
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
Jul 6 2021
BIP – Query to get Active Users for terminated employees

Once a worker is terminated in Oracle HCM Cloud, one would expect that associated User account to be inactive as well. For this to happen automatically, autoprovisioning rules should be setup which will remove the associated roles to the terminated worker’s user. In case, there is even a single role attached to the user, the user will show as active in Security Console.

From the backend, Suspended attribute in PER_USERS table is mapped to active checkbox in UI. If the user is active, the suspended flag will hold N value. For inactive users, the value of this attribute will be Y.

You can use below query to get the list of terminated workers for whom the user is still active:

SELECT papf.person_number,pu.username
  FROM per_all_people_f papf
      ,per_all_assignments_m paam
	  ,per_users pu
 WHERE papf.person_id = paam.person_id
   AND paam.effective_latest_change = 'Y'
   AND paam.effective_sequence = 1
   AND paam.assignment_status_type like 'INACTIVE%' 
   AND paam.assignment_type NOT LIKE '%T'
   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   
   AND papf.person_id = pu.person_id
   AND pu.suspended = 'N'
Mandeep Gupta 0 Comments
Jun 26 2021
BIP – Query to extract logged in person details
select papf.person_number, 
       ppnf.full_name
  from per_All_people_f papf
      ,per_person_names_f ppnf
 where papf.person_id =ppnf.person_id
   and ppnf.name_type= 'GLOBAL'
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
   and papf.person_id = HRC_SESSION_UTIL.GET_USER_PERSONID
Mandeep Gupta 0 Comments
Jun 24 2021
BIP – Query to extract Content Items Details
SELECT  'METADATA'
       ,'ContentItem'
	   ,hctb.content_type_id
       ,hctb.context_name
       ,hcibt.name
       ,hctvt.value_set_name
       ,hg.geography_name
       ,hcibt.item_description
       ,to_char(hcib.date_from,'yyyy/mm/dd')
       ,hg.geography_code
       ,hcib.content_item_code
       ,to_char(hcib.date_to,'yyyy/mm/dd')
       ,hrmb.rating_model_code
       ,hrmb.rating_model_id
       ,hikm.source_system_id
       ,hikm.source_system_owner 
FROM HRT_CONTENT_ITEMS_B hcib
    ,HRT_CONTENT_ITEMS_TL hcibt
	,HRT_CONTENT_TYPES_B hctb
	,HZ_GEOGRAPHIES hg
	,HRT_RATING_MODELS_B hrmb
	,HRT_CONTENT_TP_VALUESETS_TL hctvt
	,HRC_INTEGRATION_KEY_MAP hikm
where hcib.CONTENT_ITEM_ID=hcibt.CONTENT_ITEM_ID
  AND hcibt.LANGUAGE=userenv('LANG')
  AND hcib.CONTENT_TYPE_ID=hctb.CONTENT_TYPE_ID(+)
  AND hikm.surrogate_id = hcib.CONTENT_ITEM_ID
  AND hcib.COUNTRY_ID=hg.GEOGRAPHY_ID(+)
  AND hg.GEOGRAPHY_TYPE(+)='COUNTRY'
  AND trunc(hcib.DATE_FROM) between hg.START_DATE(+) and nvl(hg.END_DATE(+),to_date('4712/12/31','YYYY/MM/DD'))
  AND hcib.RATING_MODEL_ID=hrmb.RATING_MODEL_ID(+)
  AND trunc(hcib.DATE_FROM) between hrmb.DATE_FROM(+) and nvl(hrmb.DATE_TO(+),to_date('4712/12/31','YYYY/MM/DD'))
  AND hcib.CONTENT_VALUE_SET_ID=hctvt.CONTENT_VALUE_SET_ID(+)
  AND hctvt.LANGUAGE(+)=userenv('LANG')
  AND hctb.CONTEXT_NAME = 'LANGUAGE'
Mandeep Gupta 0 Comments
Jun 11 2021
BIP – Providing Link of BIP report on Self Service

There is one common requirement where Employees should access reports directly from self-service instead of navigating to analytics. In such cases, a report link can be created and added as a static link on Navigator. This will enable the employee to access report directly. The report can be then viewed in different views using different parameters in report link.

Steps to enable report link on self-service:

  • Derive the report link. Navigate to analytics and open the report:
    • https://xxxx-fa-ext.oracledemos.com/analytics/

Prepare the report link in below format:

https://xxxx-fa-ext.oracledemos.com/xmlpserver/Custom/HCM/INVOKE_BIP_RPT.xdo?_xpt=0&_xmode=4&_xf=pdf&_xautorun=true

Refer the below link for more details on link parameters

https://docs.oracle.com/cd/E80149_01/bip/BIPRD/GUID-F788B306-99F6-432E-BCD5-F45046D31684.htm#BIPRD3324

Xmode parameter is used for different views. Below are different views depending upon xmode value:

xmode=0

xmode=1

xmode=2

xmode=4

Passing Parameter in report link:

https://ucf6-zuyv-fa-ext.oracledemos.com/xmlpserver/Custom/HCM/INVOKE_BIP_RPT.xdo?_xpt=0&_xmode=4&p_emp_num=10&_xt=Simple&_xf=html

  • Create a new Sandbox and add Structure under tools. Click on Create and Enter:

Click on Tools under sandbox and click on Create Page Entry:

Create a page entry with below details and click on Save and Close:

You can now see Report Link under Me:

Click on report link and it will open the report in a new tab:

If everything looks ok, you can publish the Sandbox.

Mandeep Gupta 0 Comments
Jun 10 2021
BIP – Query to find attachment details

There are multiple objects in Oracle HCM Cloud which support attachments. Often, there is a need to extract the attachment details stored at the object level. All the uploaded attachments are stored in fnd_attached_documents table. Below query is used to extract the attachment details stored at Service Request level in HR Helpdesk. You can replace the table name and primary key join to extract the data as per your requirement:

select ssr.sr_id,
       ssr.title,  
       fad.CATEGORY_NAME ,
       fdt.file_name,
       fdt.dm_version_number document_id,
       fdt.dm_document_id UCM_file
  from fnd_attached_documents fad, svc_service_requests ssr, fnd_documents_tl fdt
 where ENTITY_NAME = 'SVC_SERVICE_REQUESTS'
   and ssr.sr_id = fad.PK1_VALUE
   and fad.document_id = fdt.document_id
   and fdt.language = 'US'

Query to get list of Jobs having an attachment:

 select pjft.name
   from per_jobs_f_tl pjft
  where pjft.language = 'US'
    and exists (select 1  
                  from fnd_attached_documents fad, PER_JOBS_F pjf, fnd_documents_tl fdt
                 where ENTITY_NAME = 'PER_JOBS_F'
                   and pjf.job_id = fad.PK1_VALUE
                   and fad.document_id = fdt.document_id
                   and fdt.language = 'US'
				   and pjf.job_id = pjft.job_id)

Refer below link for attachments on Position profile:

https://fusionhcmconsulting.com/2021/03/bip-reports-query-to-get-position-profile-attachment-details/

In order to download the attchments from UCM, the user should have AttachmentsRead role attached. Please check the below post on how to create AttachmentsRead role:

https://fusionhcmconsulting.com/2021/03/security-role-to-view-document-of-record-attachments-on-ucm/

Mandeep Gupta 0 Comments
Jun 2 2021
BIP – Query to get Position costing details
SELECT hapft.name
      ,pcaa.segment1 
      ,pcaa.segment2 
      ,pcaa.segment3 
      ,pcaa.segment4 
      ,pcaa.segment5 
      ,pcaa.segment6 
      ,pcaa.segment7 
      ,pcaa.segment8 
  FROM PAY_COST_ALLOCATIONS_F pacf
      ,PAY_COST_ALLOC_ACCOUNTS pcaa
	  ,HR_ALL_POSITIONS_F hapf
	  ,HR_ALL_POSITIONS_F_TL hapft
 WHERE pacf.cost_allocation_record_id = pcaa.cost_allocation_record_id 
   AND pacf.source_type = 'POS' 
   AND pacf.source_id = hapf.position_id
   AND hapft.position_id = hapf.position_id
   AND hapft.language = 'US'
   AND TRUNC(SYSDATE) BETWEEN pacf.effective_start_date AND pacf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date
Mandeep Gupta 0 Comments
May 30 2021
BIP – Query to Extract Position FTE values

When position hierarchy is enabled in Cloud HCM, Manage Positions UI shows some dynamically populated fields along with Parent position.

For example:

Use the below query to extract above details:

SELECT Positions.name 				"Position Name"
      ,Positions.FTE 				"Position Current FTE"
      ,Positions.INCUMBENT_FTE      "Current Incumbent FTE"
      ,(Positions.FTE - Positions.INCUMBENT_FTE)      "Difference FTE"
  FROM	  
(SELECT HAPFT.NAME,
        HAPF.FTE, 
	    (select SUM(PAWMF.VALUE)
           from PER_ALL_ASSIGNMENTS_M PAAM,
                PER_ASSIGN_WORK_MEASURES_F PAWMF
		  where 1=1
            AND PAAM.POSITION_ID = HAPF.POSITION_ID 
            AND SYSDATE  BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
            AND PAAM.ASSIGNMENT_TYPE = 'E'
		    AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
            AND PAAM.ASSIGNMENT_ID = PAWMF.ASSIGNMENT_ID
            AND PAWMF.UNIT = 'FTE') AS INCUMBENT_FTE
   FROM HR_ALL_POSITIONS_F HAPF, 
        HR_ALL_POSITIONS_F_TL HAPFT
  WHERE HAPF.POSITION_ID = HAPFT.POSITION_ID 
    AND USERENV('LANG') = HAPFT.LANGUAGE 
    AND TRUNC(SYSDATE)  BETWEEN HAPF.EFFECTIVE_START_DATE AND HAPF.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE)  BETWEEN HAPFT.EFFECTIVE_START_DATE AND HAPFT.EFFECTIVE_END_DATE
    AND HAPFT.NAME IN('Test Position')
  ORDER BY HAPFT.NAME ) Positions
Mandeep Gupta 0 Comments
May 30 2021
BIP – Query to extract fast formula details

Below query will extract all the custom fast formulas along with the name of formula and formula type.

select fff.formula_name
      ,fft.formula_type_name
	  ,fff.formula_text
	  ,fff.compile_flag
	  ,fff.legislation_code
  from ff_formulas_f fff
      ,ff_formula_types fft
 where fff.formula_type_id = fft.formula_type_id 
   --and fft.formula_type_name = 'Extract Criteria'
   and fff.LAST_UPDATED_BY <> 'SEED_DATA_FROM_APPLICATION'
   AND TRUNC(SYSDATE) BETWEEN fff.effective_start_date AND fff.effective_end_date 
ORDER BY 2,1

To extract list of fast formulas used in HCM Extracts, run below query:

SELECT pedv.definition_name
      ,pedv.description
      ,pedv.legislation_code
      ,pedv.xml_tag_name
      ,pedv.ext_type_code
      ,fff.formula_name
	  ,fft.formula_type_name
  FROM pay_rep_criteria_f prcf
      ,pay_report_blocks_vl prbv
      ,per_ext_definitions_vl pedv
	  ,ff_formulas_f fff
	  ,ff_formula_types fft
 WHERE prbv.ext_definition_id = pedv.ext_definition_id
   AND prcf.report_block_id = prbv.report_block_id
   AND fff.formula_id = prcf.formula_id
   AND TRUNC(SYSDATE) BETWEEN fff.effective_start_date AND fff.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN prcf.effective_start_date AND prcf.effective_end_date
   AND fff.formula_type_id = fft.formula_type_id
Mandeep Gupta 0 Comments
May 25 2021
Value Set – Table Value set to get list of employees

While extending the DFF attributes for additional functionality, one of the common requirements is to get the list of workers in the system.

For this purpose a table based value set can be defined and attached to the DFF attribute.

FROM Clauseper_all_people_f papf, per_person_names_f ppnf
Value Attributes Table Alias 
*Value Column Namepapf.person_number
Value Column TypeVARCHAR2
Value Column Length30
Description Column Name ppnf.full_name
Description Column Type 
Description Column Length 
ID Column Namepapf.person_number
ID Column TypeVARCHAR2
ID Column Length30
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausetrunc(SYSDATE) between papf.effective_start_date AND papf.effective_end_date
AND trunc(SYSDATE) between ppnf.effective_start_date AND ppnf.effective_end_date
and ppnf.name_type = ‘GLOBAL’
and ppnf.person_id=papf.person_id
and papf.person_id IN (select distinct person_id from per_periods_of_Service ppos where NVL(ppos.actual_termination_date, trunc(sysdate)) >= trunc(sysdate))
ORDER BY Clause 

The above value set will show a list of only active employees. If you want to include inactive employees as well, please modify the where clause as below:

trunc(SYSDATE) between papf.effective_start_date AND papf.effective_end_date
AND trunc(SYSDATE) between ppnf.effective_start_date AND ppnf.effective_end_date
and ppnf.name_type = 'GLOBAL'
and ppnf.person_id=papf.person_id
and papf.person_id IN (select distinct person_id from per_periods_of_Service ppos)
Mandeep Gupta 0 Comments
May 25 2021
BIP – Query to extract Business Units
 select hauft.NAME 
   from HR_ORG_UNIT_CLASSIFICATIONS_F houcf, 
        HR_ALL_ORGANIZATION_UNITS_F haouf, 
	HR_ORGANIZATION_UNITS_F_TL hauft
  where 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 = 'FUN_BUSINESS_UNIT' 
    AND trunc(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date
Mandeep Gupta 0 Comments
May 24 2021
BIP – Query to get Worker Career Statement
SELECT papf.person_number, ITEM_TEXT2000_1 STATEMENT, ITEM_CLOB_1
FROM HRT_PROFILE_ITEMS ProfileItemPEO,
HRT_PROFILES_VL ProfilePEO,
HRT_PROFILE_TYP_SECTIONS ProfileTypeSectionPEO,
PER_ALL_PEOPLE_F papf
WHERE ProfilePEO.PROFILE_ID= ProfileItemPEO.PROFILE_ID
AND ProfileTypeSectionPEO.SECTION_CONTEXT = 'PERSON_CAREER_STATEMENT'
AND ProfileTypeSectionPEO.BUSINESS_GROUP_ID=ProfilePEO.BUSINESS_GROUP_ID
AND ProfileItemPEO.CONTENT_TYPE_ID=ProfileTypeSectionPEO.CONTENT_TYPE_ID
AND papf.person_id = ProfilePEO.person_id
AND ITEM_TEXT2000_1 IS NOT NULL
Mandeep Gupta 0 Comments
May 11 2021
BIP – Query to get element entry details
Select DISTINCT peevf.element_entry_value_id
,peef.element_entry_id
,petf.base_element_name
,peevf.effective_start_date
,paam.assignment_number
,pivf.base_name
from per_all_assignments_m paam
,pay_element_types_f petf
,pay_element_entries_f peef
,pay_element_entry_values_f peevf
,pay_input_values_f pivf
where 1=1
and paam.person_id = peef.person_id
and peef.element_type_id = petf.element_type_id
and pivf.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 = 'Dental Plan'
and pivf.base_name = 'Amount'
and paam.assignment_number = 'E1111'
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
and trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date

Mandeep Gupta 0 Comments
May 11 2021
BIP – Query to get position profile details
select hapf.position_code
,hpb.profile_code
,hpt.description profile_desc
,hpt.summary
,hpeiv.DESCRIPTION
,hpeiv.RESPONSIBILITIES
,hpeiv.QUALIFICATIONS
from HRT_PROFILE_ITEMS hpi
,HRT_PROFILES_B hpb
,HRT_PROFILES_TL hpt
,HRT_PROFILE_RELATIONS hpr
,HRT_PROFILE_EXTRA_INFO_VL hpeiv
,HR_ALL_POSITIONS_F hapf
where hpi.profile_id = hpb.profile_id
and hpb.profile_usage_code = 'M'
and hpi.profile_id = hpr.profile_id
and hpi.profile_id = hpt.profile_id
and hpi.profile_id = hpeiv.profile_id
and hapf.position_id = hpr.object_id
and trunc(sysdate) between hapf.effective_start_date and hapf.effective_end_Date
and hapf.position_code= '1099'
and hpt.language = 'US'

Query to extract only the profile data:

select hpb.profile_code
,hpt.description
,hpt.summary
,hpeiv.DESCRIPTION desc1
,hpeiv.RESPONSIBILITIES
,hpeiv.QUALIFICATIONS
,hikm.source_system_id
,hikm.source_system_owner
from HRT_PROFILES_B hpb
,HRT_PROFILES_TL hpt
,HRT_PROFILE_EXTRA_INFO_VL hpeiv
,HRC_INTEGRATION_KEY_MAP hikm
where hpeiv.profile_id = hpb.profile_id
and hpb.profile_usage_code = 'M'
and hpt.language = 'US'
and hpb.profile_code like '%TEST%'
and hikm.surrogate_id = hpeiv.PROFILE_EXTRA_INFO_ID
order by hpb.creation_date desc
Mandeep Gupta 0 Comments
May 10 2021
BIP – Query to get Default Assignment Rules of the Course

Default Assignment Rules like Validity Period, Expiration, Renewal Options, Renewal Period etc are maintained at the course level in Oracle Learning Cloud.

These details are stored in the backend table ‘WLF_ASSIGNMENT_RULES’.

The query from below post can be joined with WLF_ASSIGNMENT_RULES using ASSIGNMENT_RULE_ID column:

https://fusionhcmconsulting.com/2021/03/reports-bip-query-to-get-assignee-details-for-a-course/?amp

and warf.ASSIGNMENT_RULE_ID = war.ASSIGNMENT_RULE_ID

And you will get the required output.

Mandeep Gupta 0 Comments
Apr 3 2021
Reports (BIP) – Query to find module type and key

While defining Common Lookups or value sets, you need to provide module value. Each module has an associated module type, module key and product code associated with it. For example:

These details are stored in backed in a table – FND_APPL_TAXONOMY.

Use the below query to find module type, module key etc for a module:

select fat.MODULE_NAME
,fat.MODULE_TYPE
,fat.MODULE_KEY
,fat.PRODUCT_CODE
from FND_APPL_TAXONOMY fat
Mandeep Gupta 0 Comments
Apr 3 2021
Data Masking – Mask Salary Data in lower environment

There is a common requirement to mask salary data post P2T refreshes. This should be done in order to hide the actual salaries information as salary is a very sensitive information.

Use the below query to generate data in HDL format in a test environment immediately after P2T refresh. The below query generate a random salary amount. Save the downloaded data in .dat file format and upload it back to the instance.

Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId' Header, 1 data_flow_order
from dual
UNION
SELECT 'MERGE|Salary'||'|'||
paam.assignment_number||'|'||
round(DBMS_RANDOM.VALUE (1,15000) , 2)||'|'||
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 paam.assignment_number ='E788880'
ORDER BY data_flow_order

Mandeep Gupta 0 Comments