Tag: reports

Reports

Nov 5 2023

BIP – Extract action type and action information from Work relationship

SELECT papf.person_number
      ,ppnf.full_name
      ,pao.action_type_code
      ,pav.action_name
      ,part.action_reason termination_reason 
  FROM per_all_people_f papf
      ,per_person_names_f ppnf
      ,per_periods_of_service ppos
      ,per_actions_vl pav
      ,per_action_reasons_tl part
      ,per_action_occurrences pao
 WHERE papf.person_id =ppnf.person_id 
   AND papf.person_id =ppos.person_id  
   AND UPPER(ppnf.name_type)='GLOBAL' 
  --AND TO_CHAR(ppos.actual_termination_date,'dd-mm-yyyy')<TO_CHAR(SYSDATE,'dd-mm-yyyy') 
   AND ppos.action_occurrence_id = pao.action_occurrence_id 
   AND pao.action_reason_id = part.action_reason_id (+)
   AND part.language (+) = USERENV('LANG')
   AND TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) AND TRUNC(papf.effective_end_date)
   AND TRUNC(SYSDATE) BETWEEN TRUNC(ppnf.effective_start_date) AND TRUNC(ppnf.effective_end_date)
   AND papf.person_number = '123351'
   AND pao.action_id = pav.action_id
order by papf.person_number

Mandeep Gupta 0 Comments

Reports

Nov 5 2023

BIP – Query to find attachment details for related materials

Learning admin can attachments on offering level for different roles – Admin/ Employee etc.

In case, you need to extract the attachment details for an offering, below BIP can be used:

SELECT wlf_learning_items_f.learning_item_id
      ,wlf_learning_items_f.learning_item_number    
	  ,wlf_li_relations_f.relation_id
	  ,fad.pk1_value 
	  ,fad.attached_document_id
	  ,fdt.document_id 
	  ,fdt.title 
	  ,fdt.file_name 
	  ,fad.creation_date
  FROM fnd_attached_documents fad 
      ,fnd_documents_tl fdt 
	  ,wlf_learning_items_f wlf_learning_items_f
	  ,wlf_li_relations_f wlf_li_relations_f
 WHERE fad.document_id = fdt.document_id 
   AND fdt.language = 'US'
   AND fad.entity_name = 'WLF_LI_RELATIONS_F'
   AND fad.pk1_value = wlf_li_relations_f.relation_id
   AND wlf_learning_items_f.learning_item_id = wlf_li_relations_f.source_id
   AND TRUNC(SYSDATE) BETWEEN wlf_learning_items_f.effective_start_date AND wlf_learning_items_f.effective_end_date 
   AND TRUNC(SYSDATE) BETWEEN wlf_li_relations_f.effective_start_date AND wlf_li_relations_f.effective_end_date 
 ORDER BY fad.creation_date DESC

Output:

Mandeep Gupta 0 Comments

Configuration Reports

Oct 29 2023

BIP – Extract absence type schedule hierachy start point

While defining absences, schedule hierarchy start point can be defined to tell the system from where to pick the working days for a person.

Once the values are defined, you can use below query to validate if setup has been defined correctly:

select * from ANC_ABSENCE_TYPES_F
WHERE LEGISLATION_CODE IN ('US','GB','CA','CO')
and DUR_CALC_BASIS is not null
order by LEGISLATION_CODE

Mandeep Gupta 0 Comments

Configuration Reports

Oct 24 2023

BIP – Query to find active user accounts post termination

Once a worker record is terminated, it is expected that the associated user account will get inactive. However, it depends upon the configuration on Enterprise Level and auto provisioning rules setup. If there is even on role assigned to user name, the user account will not get terminated in HCM.

To help the business to get a list of active user accounts even if the associated worker record is terminated, below query is created:

SELECT DISTINCT papf.person_number
      ,ppos.actual_termination_date 
  FROM PER_ALL_PEOPLE_F papf
      ,PER_PERIODS_OF_SERVICE ppos
	  ,PER_USERS pu
	  ,PER_USER_ROLES pur
 WHERE papf.person_id = ppos.person_id
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND papf.person_id = pu.person_id
   AND pu.user_id = pur.user_id
   AND ppos.actual_termination_date IS NOT NULL
   AND ppos.actual_termination_date < TRUNC(SYSDATE) /*Check only for past dated terminations*/
   AND NOT EXISTS (SELECT 1 FROM PER_PERIODS_OF_SERVICE ppos2
                    WHERE ppos2.person_id = ppos.person_id
					  AND ppos2.actual_termination_date IS NULL) 
 ORDER BY 1,2

Mandeep Gupta 0 Comments

Configuration Reports

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

Reports

Aug 16 2023

BIP – Query to find list of positions against a department

There are times when we just need a quick query to count number of positions created in HCM against a department. Below is a sample SQL which can be handy in such situations:

select * 
  from hr_all_positions_f hapf
 where hapf.organization_id IN (select distinct organization_id 
                                  from HR_ORGANIZATION_UNITS_F_TL houft
                                 where (houft.name LIKE 'Test%'
                                     OR houft.name LIKE 'Abc%'
									    )
								)

Mandeep Gupta 0 Comments

Reports

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

Reports

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

Reports

Aug 6 2023

BIP – Extract list of counties for GB

Below SQL can be used to extract list counties configured for GB in HCM:

SELECT *
  FROM fusion.hz_geographies hg
 WHERE hg.geography_type = 'COUNTY'
   AND country_code= 'GB'
 ORDER BY geography_name

Mandeep Gupta 0 Comments

Reports

Aug 6 2023

BIP – Query to find number of work relations in a legal employer

There are different requirements where one wants to get number of active/inactive work relationships in a legal entity.

Below SQL query can be run to get these counts:

select ple.name, count(ppos.period_of_Service_id) number_of_wrs 
  from per_legal_employers ple
      ,per_periods_of_service ppos
 where ppos.legal_entity_id = ple.organization_id
 group by ple.name
 order by 1

Mandeep Gupta 0 Comments

Configuration HDL Reports

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

Reports

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

HDL

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

HDL Reports

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

Reports

Mar 12 2023

BIP – Query to find element entry counts

SELECT pett.element_name, count(peef.element_type_id)
  FROM pay_element_types_tl pett
      ,pay_element_entries_f peef
 WHERE pett.element_type_id = peef.element_type_id
   AND pett.language = 'US'
   AND to_date('01-04-2022','DD/MM/RRRR') BETWEEN peef.effective_start_date AND peef.effective_end_date
 GROUP BY pett.element_name

Mandeep Gupta 0 Comments

Reports

Mar 12 2023

BIP – Query to find persons with multiple active work relationships

SELECT papf.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 (ppos.actual_termination_date IS NULL or ppos.actual_termination_date>= TRUNC(SYSDATE))
               GROUP BY ppos.person_id 
              HAVING COUNT (ppos.period_of_service_id) >1
            )

Mandeep Gupta 0 Comments

Approvals Reports

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

Reports Uncategorized

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

Configuration Reports

Aug 25 2022

BIP – Queries related to Bank, Bank Branches and External Bank accounts

Query for bank details:

select * from ce_banks_v

Query for bank branch details:

select * from ce_bank_branches_v

select cbbv.bank_name
      ,cbbv.bank_branch_name
      ,cbbv.bank_home_country
      ,cbbv.branch_number 
      ,TO_CHAR(cbbv.start_date,'YYYY/MM/DD') branch_start_date
      ,TO_CHAR(cbbv.end_date,'YYYY/MM/DD') branch_end_date
  from ce_bank_branches_v cbbv

Query to extract Internal bank accounts:

select * from ce_bank_accounts

Query to extract External bank accounts:

select * from iby_ext_bank_accounts

Mandeep Gupta 0 Comments

Configuration

Aug 25 2022

BIP – Query to extract Disability Org Details

Use below query to extract disability org details from Fusion HCM:

SELECT houft.name disability_org_name
      ,TO_CHAR(haouf.effective_start_date,'RRRR/MM/DD') disability_org_start_date
      ,TO_CHAR(haouf.effective_end_date,'RRRR/MM/DD') disability_org_end_date
      ,houcf.classification_code
      ,haouf.internal_address_line
      ,houcf.status
  FROM hr_org_unit_classifications_f houcf
     , hr_all_organization_units_f haouf
     , hr_organization_units_f_tl houft
 WHERE houcf.organization_id = haouf.organization_id
   AND houft.organization_id = haouf.organization_id
   AND TRUNC(SYSDATE) BETWEEN houcf.effective_start_date AND houcf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
   AND haouf.effective_start_date BETWEEN houft.effective_start_date AND houft.effective_end_date
   AND houft.language = USERENV('LANG')
   AND houcf.category_code = 'HCM_DISABILITY_ORGANIZATION'

Mandeep Gupta 0 Comments

MSPL

MSPL

BIP – Extract action type and action information from Work relationship

BIP – Extract absence type schedule hierachy start point

BIP – Query to find active user accounts post termination

BIP – Query to find size of documents attached in DOR

BIP – Query to find list of positions against a department

BIP – Queries to find count of workers in HCM

BIP – Query to find list of same person having same position multiple times

BIP – Extract list of counties for GB

BIP – Query to find number of work relations in a legal employer

BIP – Extract Document Record File Link from Content Server

BIP – Suppress special characters

HDL – Sample HDL file to create Role Provisioning Rules

BIP – Query to find Primary flags for a Worker

BIP – Query to find element entry counts

BIP – Query to find persons with multiple active work relationships

BIP – Check bypass Approval Rules status

BIP – Query to extract DOR attachment name

BIP – Queries related to Bank, Bank Branches and External Bank accounts

BIP – Query to extract Disability Org Details

MSPL

MSPL

Tag Archives: reports