Search for:
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 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
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
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
Mar 12 2023
BIP – Check bypass Approval Rules status

While performing SIT/UAT, sometimes it becomes mandatory to disable (bypass) all configured approval rules except for a few. One has to go in manually in Transaction console and check the status for each task.

An easy way to run the following SQL query and check the tasks for which approvals are yet not Bypassed:

SELECT HRPB.TASK_FILE_NAME TaskName
      ,DECODE(HAAO.APPROVAL_DISABLED,'true','Bypassed','false','Enabled') "Enabled Status"
      ,haao.last_update_date
      ,haao.last_updated_by
  FROM FUSION.HRC_ARM_PROCESS_B HRPB
      ,FUSION.HRC_ARM_APPROVAL_OPTIONS HAAO
 WHERE HRPB.PROCESS_ID=HAAO.PROCESS_ID
Mandeep Gupta 0 Comments
Mar 12 2023
BIP – List of workers without payment method
SELECT papf.person_number

  FROM PER_ALL_PEOPLE_F papf
      ,PER_PERIODS_OF_SERVICE ppos
 WHERE 1=1
  AND papf.person_id = ppos.person_id
  AND (ppos.actual_termination_date IS NULL OR ppos.actual_termination_date >= TRUNC(SYSDATE))
  AND NOT EXISTS (SELECT 'Found' FROM PAY_PAY_RELATIONSHIPS_DN pprd
                                     ,PAY_PERSON_PAY_METHODS_F pppmf
                                WHERE pprd.payroll_relationship_id = pppmf.payroll_relationship_id

                                  AND pprd.person_id = papf.person_id)
Mandeep Gupta 0 Comments
Jan 6 2023
HDL – Script to DELETE positions data

In your test environments, you may encounter issues where you want to DELETE positions data. You can use below script for that:

SELECT DATA_ROW
FROM (
SELECT 'METADATA|Position|PositionId|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE'||'|'||
       'Position' ||'|'||
       hapf.Position_Id||'|'||
       to_char(hapf.Effective_Start_Date,'RRRR/MM/DD')||'|'||
       to_char(hapf.Effective_End_Date,'RRRR/MM/DD') ||'|'||
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) ||'|'||
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) AS DATA_ROW
  from hr_all_positions_f hapf
)
Mandeep Gupta 0 Comments
Nov 20 2022
BIP – Query to extract Supervisor Details
Select papfemp.person_number
     , ppnfemp.first_name                                    person_first_name
     , ppnfemp.last_name                                     person_last_name
     , paamemp.assignment_number                              person_assignment
     , to_char(pasf.effective_start_date, 'YYYY-MM-DD')        effective_start_date
     , to_char(pasf.effective_end_date, 'YYYY-MM-DD')          effective_end_date
     , papf_mgr.person_number                                  manager_number
     , ppnf_mgr.first_name                                    manager_first_name
     , ppnf_mgr.last_name                                     manager_last_name
     , paam_mgr.assignment_number                              manager_assignment
     , pasf.manager_type
  From per_all_people_f                       papfemp
     , per_person_names_f                     ppnfemp
     , per_all_assignments_m                  paamemp
     , per_all_people_f                       papf_mgr
     , per_person_names_f                     ppnf_mgr
     , per_all_assignments_m                  paam_mgr
     , per_assignment_supervisors_f           pasf
 Where ppnfemp.person_id              = papfemp.person_id
   And ppnfemp.name_type              = 'GLOBAL'
   And paamemp.person_id              = papfemp.person_id
   And paamemp.assignment_type            In ('E', 'C', 'N')
   And paamemp.effective_latest_change     = 'Y'
   And paamemp.effective_start_date  Between papfemp.effective_start_date     And papfemp.effective_end_date
   And paamemp.effective_start_date  Between ppnfemp.effective_start_date     And ppnfemp.effective_end_date
   And TRUNC(SYSDATE)  Between paamemp.effective_start_date                      And paamemp.effective_end_date
   And paamemp.effective_start_date  Between pasf.effective_start_date     And pasf.effective_end_date
   And ppnf_mgr.person_id           = papf_mgr.person_id
   And ppnf_mgr.name_type              = 'GLOBAL'
   And paam_mgr.person_id                   = papf_mgr.person_id
   And paam_mgr.assignment_type            In ('E', 'C', 'N')
   And paam_mgr.effective_latest_change     = 'Y'
   And paam_mgr.effective_start_date  Between papf_mgr.effective_start_date     And papf_mgr.effective_end_date
   And paam_mgr.effective_start_date  Between ppnf_mgr.effective_start_date     And ppnf_mgr.effective_end_date
   And paamemp.effective_start_date   Between paam_mgr.effective_start_date     And paam_mgr.effective_end_date

   And pasf.person_id                       = papfemp.person_id
   And pasf.assignment_id                   = paamemp.assignment_id
   And pasf.manager_id                      = papf_mgr.person_id
   And pasf.manager_assignment_id           = paam_mgr.assignment_id
Mandeep Gupta 0 Comments
Oct 6 2022
BIP – Query to extract DOR attachment name
select papf.person_number 
      ,hdor.document_code
      ,hdor.document_name
      ,hdor.document_number
      ,fdt.file_name
      ,fdt.dm_version_number document_id
      ,fdt.dm_document_id UCM_file
 from fnd_attached_documents fad, hr_documents_of_record hdor, fnd_documents_tl fdt
   ,per_all_people_f papf
   ,hr_document_types_tl hdtt
 where ENTITY_NAME = 'HR_DOCUMENTS_OF_RECORD'
  and hdor.documents_of_record_id = fad.PK1_VALUE
  and fad.document_id = fdt.document_id
  and fdt.language = 'US'
  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'
Mandeep Gupta 0 Comments