Search for:
Sep 3 2023
BIP – Query to find size of documents attached in DOR

Oracle HCM provides a functionality to store different kinds of worker documents in Document Records. Over a period of time, a large number of documents get accumulated for different workers. In such cases, there are requirements to know size of attachments in DORs. For, this I have developed a simple query which can be modified as needed:

select fdv.file_name
      ,fdv.title
      ,dm_document_id
      ,TRUNC(SUM(wcc_documents.dFileSize)/1024,2) 			as "Size in KB"
      ,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024,2) 		as "Size in MB"
      ,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024/1024,2) as "Size in GB"
 from hr_documents_of_record hdr, 
      fnd_attached_documents fad, 
      fnd_documents_vl fdv,
      fusion_ocserver11g.revisions wcc_revisions,  
      fusion_ocserver11g.documents wcc_documents
where to_char(hdr.documents_of_record_id) = fad.pk1_value(+)
  and fad.entity_name(+) = 'HR_DOCUMENTS_OF_RECORD'
  and fad.document_id = fdv.document_id(+)
  and fdv.dm_version_number = wcc_revisions.did
  and wcc_revisions.did = wcc_documents.did
  and wcc_documents.disprimary = 1
group by fdv.file_name
        ,fdv.title
	,dm_document_id	
order by 1
Mandeep Gupta 0 Comments
Sep 3 2023
BIP – Query to find workers with FTE < 1

Below SQL query can be used to find part-timers in HCM. Any worker where FTE is less than 1 is normally considered as part-timer:

select paam.assignment_number, pawmf.value
  from per_legal_employers ple
      ,per_periods_of_service ppos
      ,per_all_assignments_m paam
      ,PER_ASSIGN_WORK_MEASURES_F pawmf
 where ppos.legal_entity_id = ple.organization_id
   and paam.period_of_Service_id = ppos.period_of_Service_id
   and TRUNC(SYSDATE) between paam.effective_start_date AND paam.effective_end_date
   and TRUNC(SYSDATE) between pawmf.effective_start_date AND pawmf.effective_end_date
   and paam.assignment_status_type like 'ACTIVE%'
   and ple.name like 'XX%United%Stat%'
   and paam.assignment_id = pawmf.assignment_id
   and pawmf.unit = 'FTE'
   and  pawmf.value <1
   and paam.assignment_type ='E'
   and ppos.actual_termination_date is null
 order by 1
Mandeep Gupta 0 Comments
Sep 3 2023
BIP – Extract Absence Entries in HDL Format

Recently, I faced a scenario for a customer where after go-live, where there was an issue found with absence plan configuration. The absence plan was incorrectly set up. The Balance Frequency Source wasn’t setup to “Repeating Period”, so the accrual was calculated incorrectly. In order to fix this, a new absence plan was created and the absence entries were made against the new plan.

So, the approach taken was to take a backup of all absence entries from PROD, enroll the employees into new plan and reupload the absence entries.

A BIP report was developed to take a backup of existing absence entries in HDL format. Below is the query for same:

SELECT DATA_ROW
FROM (
SELECT 'METADATA'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || 'PerAbsenceEntryId'
|| CHR (124) || 'AbsenceType'
|| CHR (124) || 'AbsenceTypeId'
|| CHR (124) || 'AbsenceStatus'
|| CHR (124) || 'ApprovalStatus'
|| CHR (124) || 'AssignmentNumber'
|| CHR (124) || 'AssignmentId'
|| CHR (124) || 'EmployerId'
|| CHR (124) || 'PersonNumber'
|| CHR (124) || 'PersonId'
|| CHR (124) || 'StartDate'
|| CHR (124) || 'StartTime'
|| CHR (124) || 'StartDateDuration'
|| CHR (124) || 'EndDate'
|| CHR (124) || 'EndTime'
|| CHR (124) || 'EndDateDuration'
|| CHR (124) || 'AbsenceReasonId'
|| CHR (124) || 'SubmittedDate'
|| CHR (124) || 'PlannedEndDate'
|| CHR (124) || 'NotificationDate'
|| CHR (124) || 'ConfirmedDate'
|| 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) || aatft.name
|| CHR (124) || apae.absence_type_id
|| CHR (124) || apae.absence_status_cd
|| CHR (124) || apae.approval_status_cd
|| CHR (124) || paam.assignment_number
|| CHR (124) || paam.assignment_id
|| CHR (124) || apae.legal_entity_id
|| CHR (124) || papf.person_number
|| CHR (124) || apae.person_id
|| CHR (124) || to_char(apae.start_date,'RRRR/MM/DD')
|| CHR (124) || apae.start_time
|| CHR (124) || apae.start_date_duration
|| CHR (124) || to_char(apae.end_date,'RRRR/MM/DD')
|| CHR (124) || apae.end_time
|| CHR (124) || apae.end_date_duration
|| CHR (124) || apae.absence_type_reason_id
|| CHR (124) || to_char(apae.submitted_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.planned_end_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.notification_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.confirmed_date,'RRRR/MM/DD')
|| CHR (124) || hikm.source_system_id
|| CHR (124) || hikm.source_system_owner
FROM ANC_PER_ABS_ENTRIES apae,
     ANC_ABSENCE_TYPES_F_TL aatft,
     PER_ALL_ASSIGNMENTS_M paam,
     PER_ALL_PEOPLE_F papf,
     HRC_INTEGRATION_KEY_MAP hikm
WHERE hikm.surrogate_id = apae.per_absence_entry_id
  AND apae.absence_type_id = aatft.absence_type_id
  AND apae.assignment_id = paam.assignment_id
  AND apae.person_id = papf.person_id
  AND paam.person_id = papf.person_id
  AND aatft.language = 'US'
  AND aatft.name LIKE 'Annual%'
  AND TRUNC(SYSDATE) BETWEEN aatft.effective_start_date AND aatft.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  AND apae.start_date BETWEEN paam.effective_start_date AND paam.effective_end_date
 )

Once the data is extracted, you need to make sure that Source System Owner is updated from FUSION to HRC_SQLLOADER. Source System Owner is set to FUSION when an entry is created from UI.

Once the output of the BIP is ready, Change “MERGE” to “DELETE” to delete all the absence entries. Then enroll the workers in new plan, do the required changes in BIP extract and upload the data back in Fusion HCM.

Mandeep Gupta 0 Comments
Aug 6 2023
BIP – Queries to find count of workers in HCM

Below sample queries can be used to extract count of workers (Active/ Inactive) in HCM:

Query 1:

select count(distinct person_number) 
  from per_all_people_f papf
  where 1=1
    and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)

Query 2: Find count workers not having basic assignment details:

select (distinct person_number) 
  from per_all_people_f papf
  where 1=1
    and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)
    and person_number not in (SELECT papf.person_number 
                                FROM per_all_people_f papf,
                                     per_person_names_f ppnf,
                                     per_all_assignments_m paam,
                                     per_jobs_f_tl pjft,
                                     hr_organization_units_f_tl hauft,
  	                                 per_periods_of_service ppos
                               WHERE paam.person_id = ppnf.person_id
                                 AND papf.person_id = ppnf.person_id
                                 AND papf.person_id = paam.person_id
                                 AND paam.job_id = pjft.job_id (+)
                                AND pjft.language(+) = 'US'
                                 AND hauft.language (+) = 'US'
-                              -and papf.person_number = '414'
                                 AND hauft.organization_id (+) = paam.organization_id
                                 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 trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
                                 AND trunc(sysdate) BETWEEN hauft.effective_start_date(+) AND hauft.effective_end_date(+)
                                 AND trunc(sysdate) BETWEEN pjft.effective_start_date(+) AND pjft.effective_end_date(+)
                                 AND paam.effective_latest_change = 'Y'
                                 AND paam.primary_flag = 'Y'
                                 AND ppnf.name_type = 'GLOBAL'
                                 AND paam.period_of_service_id = ppos.period_of_service_id
                                 AND ppos.date_start = (select max(ppos2.date_start) from per_periods_of_service ppos2
                                                         where ppos2.person_id = ppos.person_id
                                                             and ppos2.primary_flag = 'Y')
                             )
Mandeep Gupta 0 Comments
Aug 6 2023
BIP – Query to find list of same person having same position multiple times

On the responsive Position UI, the position screens shows the Pending Worker and Worker record as an incumbent and the FTE goes in negative.

Below SQL can be used to identify such persons:

SELECT paam.person_id, hapfv.name
  FROM per_all_assignments_m paam
      ,hr_all_positions_f_vl hapfv
 WHERE paam.position_id = hapfv.position_id
   AND paam.assignment_type NOT LIKE '%T'
   AND paam.assignment_status_type NOT LIKE 'INACTIVE'
   AND TRUNC(SYSDATE) BETWEEN hapfv.effective_start_date AND hapfv.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  GROUP BY paam.person_id, hapfv.name
 HAVING COUNT(*) > 1
Mandeep Gupta 0 Comments
Jul 9 2023
BIP – Extract Document Record File Link from Content Server

Below query can be used to extract the document record from content server.

SELECT papf.person_number		"Person Number",
       ppnf.first_name			"First Name",
       ppnf.last_name			"Last Name",
       fdt.file_name 			"Attached File Name",
       fdt.dm_version_number 	"Document Id",
       fdt.dm_document_id 		"UCM Content Id",
       (SELECT 'https://'||external_virtual_host
          FROM fusion.ask_deployed_domains
         WHERE deployed_domain_name = 'FADomain')
	   ||'/cs/idcplg?IdcService=GET_FILE' 
	   || chr(38) 
	   || 'dID='
       || fdt.dm_version_number
       || '&dDocName='
       || fdt.dm_document_id
       || '&allowInterrupt=1' 	"UCM File Link"  
  FROM per_all_people_f papf,
       per_person_names_f ppnf,
       hr_documents_of_record hdor,
       fnd_attached_documents fad,
       fnd_documents_tl fdt
 WHERE 1=1
   AND hdor.person_id = papf.person_id
   AND papf.person_id = ppnf.person_id
   AND hdor.documents_of_record_id = fad.pk1_value
   AND fad.document_id = fdt.document_id
   AND fdt.language = 'US'
   AND fad.entity_name = 'HR_DOCUMENTS_OF_RECORD'
   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
 ORDER BY 1
Mandeep Gupta 0 Comments
Jul 9 2023
BIP – Suppress special characters

Oracle HCM allows special characters to be used in fields like Person Names/ Department Names/ Jobs etc. But in reporting, there may be a requirement to change the special characters (like ý, ě, ž, ů etc) to normal English characters.

CONVERT function can be used for such requirements.

select CONVERT('Politických vězňů ižní Předměstí Plzeň', 'US7ASCII') normal_english from dual

Output:

Mandeep Gupta 0 Comments
Jul 8 2023
REST API Error –  The specified operation is not supported for the invoked HTTP method

Many a times while trying to use Post method using REST API, we encounter – ” The specified operation is not supported for the invoked HTTP method. Please check the URL and the headers.”

Cause of this error is incorrect header parameters/ missing header parameters while making a call to REST API.

To fix the issue:

  1. Open Postman
  2. Under Headers:

3. Set below values:

Key – Content-type

Value – application/vnd.oracle.adf.action+json

Once the header values are set, the error will go away:

Mandeep Gupta 0 Comments
Jul 5 2023
HDL – Sample HDL file to create Role Provisioning Rules
METADATA|RoleMapping|RoleMappingId|MappingName|DateFrom|DateTo|LegalEmployerName|SystemPersonType|UserPersonType|AssignmentType|AssignmentStatus|SourceSystemId|SourceSystemOwner
MERGE|RoleMapping||Test HDL|1951/01/01|4712/12/31|Test Legal Employer|Employee|Employee|E|ACTIVE|RoleMapping_123|HRC_SQLLOADER


METADATA|Role|RoleMappingRoleId|RoleMappingId(SourceSystemId)|MappingName|RoleId|RequestableFlag|SelfRequestableFlag|UseForAutoProvisioningFlag|RoleCommonName|SourceSystemId|SourceSystemOwner
MERGE|Role||RoleMapping_123|Test HDL||N|N|Y|TEST_EMP_DATA|Role_123|HRC_SQLLOADER

The rules can be verified from UI, once the HDL load is successful:

Mandeep Gupta 0 Comments
Jul 2 2023
Configuration – Pull configured actions/action reasons

Below query can be used to pull actions and configured actions for these reasons along with action type:

SELECT pav.action_type_code
      ,pav.action_code
      ,pav.action_name
      ,parv.action_reason_code
      ,parv.action_reason
      ,to_char(aru.start_date, 'yyyy/mm/dd') start_date
      ,to_char(aru.end_date, 'yyyy/mm/dd') end_date
  FROM PER_ACTION_REASON_USAGES aru,
       PER_ACTIONS_VL pav,
       PER_ACTION_REASONS_VL parv
 WHERE 1=1
   AND aru.action_id = pav.action_id
   AND aru.action_reason_id = parv.action_reason_id
   AND pav.action_code = 'CHANGE_SALARY'
ORDER BY 1,3,5
Mandeep Gupta 0 Comments
Jul 2 2023
HDL – Loaded Salary Data Extract in HDL Format
Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId|SourceSystemId|SourceSystemOwner|ActionCode|ActionReasonCode' Header, 1 data_flow_order
from dual
UNION
SELECT 'MERGE|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||'|'||
hikm.source_system_id||'|'|| 
source_system_owner||'|'|| 
pav.action_code||'|'||
'XX_ANNL_REVIEW'  data_row,
2 data_flow_order
FROM cmp_salary cs,
per_all_assignments_m paam,
hrc_integration_key_map hikm,
PER_ACTIONS_VL pav,
PER_ACTION_REASONS_VL parv
WHERE cs.assignment_id= paam.assignment_id
AND trunc(sysdate) between paam.effective_start_date AND paam.effective_end_date
AND paam.assignment_type not like '%T'
AND cs.salary_id = hikm.surrogate_id
and cs.action_id = pav.action_id
and cs.action_reason_id = parv.action_reason_id
and parv.action_reason_code = 'CMP_ANNV'
ORDER BY data_flow_order
Mandeep Gupta 0 Comments
Jun 26 2023
BIP – Extract Job Details

Once Jobs data is loaded/created, you can use below SQL to extract the setup data for Jobs:

Select pjft.name
      ,pjf.job_code
	  ,TO_CHAR(pjf.effective_start_date,'YYYY/MM/DD') start_date
      ,TO_CHAR(pjf.effective_end_date,'YYYY/MM/DD') end_Date
      ,(select add_set.set_code
          from fnd_setid_sets add_set
         where add_set.set_id = pjf.set_id
		   and add_set.language = USERENV('LANG')) set_code
      ,pjf.active_status
  from per_jobs_f pjf,
       per_jobs_f_tl pjft
 where 1=1
   and pjf.job_id = pjft.job_id
   and pjft.language = USERENV('LANG')
   and trunc(SYSDATE) between pjft.effective_start_date and pjft.effective_end_date
   and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
ORDER BY 2,4
Mandeep Gupta 0 Comments
Jun 4 2023
BIP – Extract Checklist configuration details

Below query can be used to extract checklist configuration details:

select PER_CHECKLISTS_TL.NAME
      ,PER_CHECKLISTS_TL.DESCRIPTION checklist_desc
      ,PER_TASKS_IN_CHECKLIST_B.CHECKLIST_TASK_NAME tin_ctn
      ,PER_TASKS_IN_CHECKLIST_B.DESCRIPTION task_desc
      ,PER_TASKS_IN_CHECKLIST_B.TASK_ACTION_ID
      ,PER_TASKS_IN_CHECKLIST_B.TASK_CONFIGURATION
      ,PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_ID
      ,PER_CHECKLIST_TASKS_B.TASK_LEVEL_CODE
      ,PER_CHECKLIST_TASKS_B.TASK_LEVEL_VALUE
      ,PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_CODE
      ,PER_CHECKLIST_TASKS_B.TASK_CATEGORY
      ,PER_CHECKLIST_TASKS_B.ACTION_TYPE
      ,PER_CHECKLIST_TASKS_B.TASK_ACTION_ID ctb_action_id
      ,PER_CHECKLIST_TASKS_B.TASK_ACTION_CODE
      ,PER_CHECKLIST_TASKS_TL.CHECKLIST_TASK_NAME ctc_ctn
      ,PER_CHECKLIST_TASKS_TL.DESCRIPTION
      ,PER_CHECKLIST_TASKS_TL.ACTION_URL
      ,PER_CHECKLIST_TASKS_TL.USER_DISPLAY_NAME
FROM  PER_CHECKLISTS_TL PER_CHECKLISTS_TL
     ,PER_TASKS_IN_CHECKLIST_VL PER_TASKS_IN_CHECKLIST_B 
     ,PER_CHECKLIST_TASKS_B PER_CHECKLIST_TASKS_B
     ,PER_CHECKLIST_TASKS_TL PER_CHECKLIST_TASKS_TL
WHERE PER_CHECKLIST_TASKS_B.ACTION_TYPE = 'ORA_CHK_APP_TASK'
AND PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_ID = PER_CHECKLIST_TASKS_TL.CHECKLIST_TASK_ID
AND PER_CHECKLIST_TASKS_TL.LANGUAGE = 'US'
AND PER_CHECKLISTS_TL.LANGUAGE = 'US'
AND PER_TASKS_IN_CHECKLIST_B.CHECKLIST_ID = PER_CHECKLISTS_TL.CHECKLIST_ID
AND PER_TASKS_IN_CHECKLIST_B.TASK_ACTION_CODE = PER_CHECKLIST_TASKS_B.TASK_ACTION_CODE
AND PER_TASKS_IN_CHECKLIST_B.ACTION_TYPE = 'ORA_CHK_APP_TASK'
Mandeep Gupta 0 Comments
May 28 2023
HDL – Inactivate secondary Org classification

Oracle HCM allows an Organization to be classified as multiple Orgs. However, sometime there is a need to inactivate one org classification while keeping the primary classification as Active.

Below is a sample HDL file which can be used for this purpose:

METADATA|Organization|OrganizationId|EffectiveStartDate|EffectiveEndDate|ClassificationCode
MERGE|Organization|300000085401190|2023/04/01||PA_EXPENDITURE_ORG

METADATA|OrgUnitClassification|OrganizationId|OrgUnitClassificationId|EffectiveStartDate|EffectiveEndDate|Status|ClassificationCode
MERGE|OrgUnitClassification|300000085401190|300000261056753|2023/04/01||I|PA_EXPENDITURE_ORG
Mandeep Gupta 0 Comments
May 28 2023
BIP – Query to find list of jobs without job profiles

There is no easy way in OTBI to find list of jobs which don’t have a model profile associated with them. This can be achieved easily using BIP query.

Sample Query:

SELECT * FROM PER_JOBS_F pjf
 WHERE trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_Date
   AND NOT EXISTS ( SELECT 1
                      FROM HRT_PROFILE_ITEMS hpi
                          ,HRT_PROFILES_B hpb
                          ,HRT_PROFILE_RELATIONS hpr
                     WHERE hpi.profile_id = hpb.profile_id
                       AND hpb.profile_usage_code = 'M'
                       AND hpi.profile_id = hpr.profile_id
                       AND pjf.job_id = hpr.object_id
				  )
Mandeep Gupta 0 Comments
May 28 2023
BIP – Query to find Primary flags for a Worker

There are multiple primary flags for a worker in assignment table. Namely – Primary Assignment Flag, Primary Work Terms flag and Primary flag.

Below query can be used to check these flags:

--> Primary Flags Query
SELECT papf.person_number
      ,ppnf.first_name
      ,ppnf.last_name
      ,to_char(ppos.date_start,'RRRR/MM/DD') start_date
	  ,paam.assignment_number
      ,to_char(paam.effective_start_date,'RRRR/MM/DD') asg_eff_start_date
      ,to_char(paam.effective_end_date,'RRRR/MM/DD') asg_eff_end_date
	  ,paam.action_code
	  ,pastt.user_status
	  ,paam.assignment_status_type
	  ,paam.primary_flag
	  ,paam.primary_assignment_flag
	  ,paam.primary_work_relation_flag
	  ,ppos.primary_flag "WR Table Primary Flag"
  FROM PER_ALL_PEOPLE_F papf
      ,PER_PERSON_NAMES_F ppnf
      ,PER_ALL_ASSIGNMENTS_M paam
	  ,PER_PERIODS_OF_SERVICE ppos
      ,PER_ASSIGNMENT_STATUS_TYPES_TL pastt
WHERE papf.person_id = ppnf.person_id
  AND ppnf.name_type = 'GLOBAL'
  AND papf.person_id = paam.person_id
  AND paam.period_of_service_id = ppos.period_of_service_id
  AND paam.assignment_status_type_id = pastt.assignment_status_type_id
  AND paam.effective_sequence = 1
  AND paam.assignment_type NOT LIKE '%T'
  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 pastt.language = 'US'
  AND papf.person_number IN ('500035','500036')
ORDER BY 1, 6
Mandeep Gupta 0 Comments