Author: Mandeep Gupta - Page 9

Configuration

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:

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

HDL Reports

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

Configuration

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:

Create a sandbox and add Page composer tool.
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

Reports

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

Reports

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

Reports

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

Configuration Reports

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

Configuration Reports

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

Reports

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

Configuration

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 Clause	PER_GRADE_STEPS_F_TL pgftl, PER_GRADE_STEPS_F pgsf,PER_GRADES_F pgf
Value Attributes Table Alias
*Value Column Name	SUBSTR(pgftl.NAME,1,150)
Value Column Type	VARCHAR2
Value Column Length	150
Description Column Name	SUBSTR(pgftl.NAME,1,150)
Description Column Type	VARCHAR2
Description Column Length	150
ID Column Name	SUBSTR(pgftl.NAME,1,150)
ID Column Type	VARCHAR2
ID Column Length	150
Enabled Flag Column Name
Start Date Column Name
End Date Column Name
WHERE Clause	pgftl.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 Clause	SUBSTR(pgftl.NAME,1,150)

Mandeep Gupta 1 Comments

Reports

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

Jul 6 2021

HDL – Loading Classroom Resources in Bulk

HCM Data Loader object ClassroomResource can be used to bulk upload classroom resources in Oracle Learning Cloud. Existing locations created as part of Global HR can also be designated as classroom resources.

Below is a sample file to upload classroom resources using HDL:

METADATA|ClassroomResource|ClassroomResourceNumber|Title|Description|Capacity|SetCode|LocationCode|SourceSystemOwner|SourceSystemId|OwnedByPersonNumber
MERGE|ClassroomResource|CLASS1001|Room 1|Room 1|10|COMMON|JPLoc004 Kyushu|HRC_SQLLOADER|1001|101
MERGE|ClassroomResource|CLASS1002|Room 2|Room 2|15|COMMON|JPLoc004 Kyushu|HRC_SQLLOADER|1002|101
MERGE|ClassroomResource|CLASS1003|Room 3|Room 3|20|COMMON|JPLoc004 Kyushu|HRC_SQLLOADER|1003|101

Once the data is loaded successfully, you can verify the results from UI:

Navigation :- My Client Groups -> Learning -> Catalog Resources -> Classrooms

Mandeep Gupta 0 Comments

Reports

Jun 30 2021

BIP – Extract Union Details from Assignment

SELECT paam.assignment_number
      ,paam.labour_union_member_flag
      ,houft.name
  FROM per_all_assignments_m paam
      ,hr_organization_units_f_tl houft
 WHERE paam.assignment_type ='E'
   AND paam.union_id =  houft.organization_id
   AND houft.language = 'US'
   AND paam.effective_latest_change = 'Y'
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN houft.effective_start_date AND houft.effective_end_date

Mandeep Gupta 0 Comments

Jun 28 2021

HDL – Sample File to DELETE documents of Record

Use the below query to extract document of record data which should be deleted. You can add additional filters by adding person numbers.

select 'DELETE' 					"METADATA"
      ,'DocumentsOfRecord'			"DocumentsOfRecord"
	  ,hdor.documents_of_record_id	"DocumentsOfRecordId"
	  ,hdor.document_type_id	    "DocumentTypeId"
	  ,hdor.document_code			"DocumentCode"
	  ,hdor.document_name			"DocumentName"
	  ,hdor.document_number			"DocumentNumber"
	  ,hikm.source_system_owner		"SourceSystemOwner"
	  ,hikm.source_system_id		"SourceSystemId"
  from HR_DOCUMENTS_OF_RECORD hdor
      ,HRC_INTEGRATION_KEY_MAP hikm
 where hdor.documents_of_record_id = hikm.surrogate_id
   and hdor.documents_of_record_id = 300000217125443

Query for Person Number and Document Type:

select DISTINCT 'DELETE' 					"METADATA"
      ,'DocumentsOfRecord'			"DocumentsOfRecord"
	  ,hdor.documents_of_record_id	"DocumentsOfRecordId"
	  ,hdor.document_type_id	    "DocumentTypeId"
	  ,hdor.document_code			"DocumentCode"
	  ,hdor.document_name			"DocumentName"
	  ,hdor.document_number			"DocumentNumber"
	  ,hikm.source_system_owner		"SourceSystemOwner"
	  ,hikm.source_system_id		"SourceSystemId"
  from HR_DOCUMENTS_OF_RECORD hdor
      ,HRC_INTEGRATION_KEY_MAP hikm
	  ,PER_ALL_PEOPLE_F papf
	  ,HR_DOCUMENT_TYPES_TL hdtt
 where hdor.documents_of_record_id = hikm.surrogate_id
   --and hdor.documents_of_record_id = 300000217125443
   and hdor.person_id = papf.person_id
   and TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   and hdor.document_type_id = hdtt.document_type_id
   and hdtt.language = 'US'
   and hdtt.DOCUMENT_TYPE = 'Test Doc'
   and papf.person_number IN ('12','23')

Sample HDL File:

METADATA|DocumentsOfRecord|DocumentsOfRecordId|DocumentTypeId|DocumentCode|DocumentName|DocumentNumber|SourceSystemOwner|SourceSystemId
DELETE|DocumentsOfRecord|300000217125443|300000217168555|TMAD_001|Multiple Attachments||HRC_SQLLOADER|HRC_SQLLOADER_101_TMAD_001

Mandeep Gupta 0 Comments

Reports

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

Reports

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

Reports

Jun 19 2021

BIP – Extract Supervisor HDL details

SELECT a.datarow
  FROM (
SELECT 'METADATA|AssignmentSupervisor|AssignmentSupervisorId|AssignmentNumber|ManagerAssignmentNumber|ManagerPersonNumber|ManagerType|AssignmentId|EffectiveEndDate|EffectiveStartDate|ActionCode|ManagerId|PersonId|PrimaryFlag|ReasonCode|ManagerAssignmentId|SourceSystemId|SourceSystemOwner' DATAROW, 1 SEQUENCE
   FROM DUAL
UNION ALL
SELECT   DISTINCT 'MERGE'
         || CHR (124)
		 ||'AssignmentSupervisor'
		 || CHR (124)
		 ||NULL
		 || CHR (124)
		 ||paaf.assignment_number
		 || CHR (124)
         ||paaf_m.assignment_number
		 || CHR (124)
		 ||papf.person_number 
		 || CHR (124)
         ||pasup.manager_type
         || CHR (124)
		 ||NULL		 
         || CHR (124)		 
		 ||TO_CHAR (pasup.effective_end_date,'DD/MON/YYYY')
		 || CHR (124)
         ||TO_CHAR (pasup.effective_start_date,'DD/MON/YYYY')
         || CHR (124)         
	     ||pab.action_code
		 || CHR (124)         
	     ||NULL
		 || CHR (124)         
	     ||NULL
		 || CHR (124)         
	     ||pasup.primary_flag
		 || CHR (124)         
	     ||parb.action_reason_code
		 || CHR (124)         
	     ||NULL
		 || CHR (124)        
         ||hikm.source_system_id
		 || CHR (124)         		 
         ||hikm.source_system_owner DATAROW, 2 SEQUENCE
    FROM per_all_people_f papf,
         per_assignment_supervisors_f pasup,
         per_all_assignments_m paaf,
         per_all_assignments_m paaf_m,
		 hrc_integration_key_map hikm,
		 per_action_occurrences pao,
		 per_action_reasons_b parb,
		 per_actions_b pab
   WHERE pasup.assignment_id = paaf.assignment_id
     AND pasup.manager_assignment_id = paaf_m.assignment_id
     AND papf.person_id = paaf_m.person_id
     AND paaf.assignment_type = 'E'
     --AND paaf.assignment_status_type = 'ACTIVE'
     AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
     AND pasup.effective_start_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
     AND pasup.effective_start_date BETWEEN paaf_m.effective_start_date AND paaf_m.effective_end_date
     AND pasup.assignment_supervisor_id = hikm.surrogate_id
     AND OBJECT_NAME  = 'AssignmentSupervisor'
     AND pao.action_occurrence_id = pasup.action_occurrence_id
     AND pao.action_id = pab.action_id
     AND pao.action_reason_id = parb.action_reason_id	 
) a 
ORDER BY SEQUENCE

Mandeep Gupta 0 Comments

Reports

Jun 19 2021

BIP – Extract Worker Contact Details

SELECT DISTINCT papf.person_number	"Emp Person Number"
               ,papf_cont.person_number	"Contact Person Number"
			   ,hl.meaning 			"Relationship"
			   ,TO_CHAR (papf_cont.effective_start_date,'RRRR/MM/DD') "Contact Eff Start D"
			   ,TO_CHAR (papf_cont.effective_end_date,'RRRR/MM/DD') "Contact Eff End D"
			   ,TO_CHAR (papf_cont.start_date, 'RRRR/MM/DD') "Contact Start Date"
			   ,TO_CHAR (pcrf.effective_start_date,'RRRR/MM/DD') "Rel Start Date"
			   ,TO_CHAR (pcrf.effective_end_date,'RRRR/MM/DD') "Rel End Date"
			   ,TO_CHAR (pp.date_of_birth, 'RRRR/MM/DD') "Contact Person DOB"
			   ,ppnf.first_name	"Contact First Name"
			   ,ppnf.last_name 	"Contact Last Name"
          FROM per_contact_relships_f pcrf
		      ,per_all_people_f papf_cont
			  ,per_all_people_f papf
			  ,per_persons pp
			  ,per_person_names_f ppnf
			  ,hcm_lookups hl
         WHERE papf_cont.person_id = pcrf.contact_person_id
		   AND papf.person_id = pcrf.person_id
           AND pp.person_id = pcrf.contact_person_id
           AND ppnf.person_id = pcrf.contact_person_id
           AND ppnf.name_type = 'GLOBAL'
           AND hl.lookup_code = pcrf.contact_type
           AND hl.lookup_type = 'CONTACT'
      ORDER BY papf.person_number, hl.meaning

Mandeep Gupta 0 Comments

Jun 12 2021

HDL – Update Goal Weight

Goal Weightage can be updated using GoalMeasurement metadata in Goal business object. First, we need to extract the uploaded measurements. Use the below query to extract the details:

SELECT HDL_DATA
FROM (
SELECT 'METADATA|GoalMeasurement|MeasurementId|MeasurementName|StartDate|EndDate|TargetValue|TargetPercentage|UomCode|MeasureTypeCode|TargetType|GoalId|SourceSystemId|SourceSystemOwner' HDL_DATA, 1 SEQ
  FROM DUAL
UNION ALL
SELECT 'MERGE'
     || CHR (124)
     || 'GoalMeasurement'
     || CHR (124)	 
	 || hgm.measurement_id
     || CHR (124)
	 || hgm.measurement_name
     || CHR (124)
	 || TO_CHAR(hgm.start_date,'RRRR/MM/DD')
     || CHR (124)
	 || TO_CHAR(hgm.end_date,'RRRR/MM/DD')
     || CHR (124)
	 || hgm.target_value
     || CHR (124)
	 || hgm.target_percentage
     || CHR (124)
	 || hgm.uom_code
     || CHR (124)
	 || hgm.measure_type_code
     || CHR (124)
	 || hgm.target_type
     || CHR (124)
	 || hg.goal_id
     || CHR (124)
	 || hikm.source_system_id
     || CHR (124)
	 || hikm.source_system_owner HDL_DATA, 2 SEQ
  FROM hrg_goals hg
      ,hrg_goal_measurements hgm
      ,hrc_integration_key_map hikm
 WHERE hg.goal_id = hgm.goal_id
   AND hgm.measurement_id = hikm.surrogate_id
   AND hgm.uom_code = 'PERCENT'
   --AND hg.goal_id = 300000215856607
)
ORDER BY SEQ

Save the data in Goal.dat HDL file. Update the target value as required. Save the .dat file and upload.

Sample HDL:

METADATA|GoalMeasurement|MeasurementId|MeasurementName|StartDate|EndDate|TargetValue|TargetPercentage|UomCode|MeasureTypeCode|TargetType|GoalId|SourceSystemId|SourceSystemOwner
MERGE|GoalMeasurement|300000215856608|Customer Survey Results|2019/01/01|2019/12/31|80||PERCENT|QUANTITATIVE|MIN|300000215856607|300000215856608|FUSION

Mandeep Gupta 0 Comments

Reports

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: