Search for:
Jan 25 2025
BIP – Extract Grade Ladder, Grade and Step details

Below query can be used to extract grade ladder, grades in ladder and step details of a grade.

SELECT pglftl.name  		grade_ladder_name
     , pglf.grade_type		grade_type
     , pglf.active_status	active_status
     , pgftl.name			grade_name
     , pgilf.sequence		grade_seq_in_ladder
	 , pgsf.sequence		grade_step_seq 
	 , pgsftl.name			grade_step_name
  FROM PER_GRADE_LADDERS_F pglf
     , PER_GRADE_LADDERS_F_TL pglftl
	 , PER_GRADES_IN_LADDERS_F pgilf  
	 , PER_GRADES_F_TL pgftl 
     , PER_GRADE_STEPS_F pgsf	 
     , PER_GRADE_STEPS_F_TL pgsftl
WHERE pglf.grade_ladder_id = pglftl.grade_ladder_id
  AND TRUNC(SYSDATE) BETWEEN TRUNC(pglf.effective_start_date) AND TRUNC(pglf.effective_end_date)
  AND pglftl.language='US'
  AND TRUNC(SYSDATE) BETWEEN TRUNC(pglftl.effective_start_date) AND TRUNC(pglftl.effective_end_date)
  AND pgilf.grade_ladder_id = pglftl.grade_ladder_id
  AND TRUNC(SYSDATE) BETWEEN TRUNC(pgilf.effective_start_date) AND TRUNC(pgilf.effective_end_date) 
  AND pgftl.language='US'  
  AND TRUNC(SYSDATE) BETWEEN TRUNC(pgftl.effective_start_date) AND TRUNC(pgftl.effective_end_date)
  AND pgilf.grade_id = pgftl.grade_id
  AND pgsf.grade_id = pgftl.grade_id
  AND TRUNC(SYSDATE) BETWEEN TRUNC(pgsf.effective_start_date) AND TRUNC(pgsf.effective_end_date)
  AND pgsftl.grade_step_id = pgsf.grade_step_id
  AND pgsftl.language='US' 
  AND trunc(sysdate) between trunc(pgsftl.effective_start_date) and trunc(pgsftl.effective_end_date)
Mandeep Gupta 0 Comments
Jan 19 2025
BIP – Report to extract BIP scheduled jobs

I have designed below query to extract the schedule of a BIP submitted jobs which is not available directly. Replace the job name attribute with your job name or run it for all:

/*******************************************************************************************
Job Status	-	HRC_LOADER_ESS_STATE
		1	-	Wait
		2	-	Ready
		3	-	Running
		4	-	Completed
		5	-	Blocked
		6	-	Hold
		7	-	Canceling
		8	-	Expired
		9	-	Canceled
	       10	-	Error
	       11	-	Warning
	       12	-	Succeeded
	       13	-	Paused
	       14	-	Pending Validation
	       15	-	Validation Failed
	       16	-	Schedule Ended
	       18	-	Error Auto Retry
	       19	-	Error Manual recovery
*******************************************************************************************/

WITH schedule_history
AS
(
SELECT DISTINCT rh.username	        scheduled_user_name
	  ,rp.value        		submitted_job_name 
          ,rh.processstart		last_job_start_date
          ,rh.processend		last_job_end_date	  
     -- ,rh.elapsedtime		elapsedtime	  
	  ,flv.meaning 			last_job_status	  
          ,rh.requestedstart	        schedule_start_date
          ,rh.requestedend		schedule_end_date
	  ,rh.scheduled    		next_run_date
	  ,rh.parentrequestid
	  ,rh.requestid
      ,SUBSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
	                        ),3
					)
				   ,(INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
				                         ),3
							      )
						   ,'FREQ=')+5
					)
				   ,(INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
				                         ),3
								  )
						   ,';'
						   )
					 - INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
					                       ),3
								   )
							,'FREQ=')-5
							) 
			 ) schedule_frequency
       ,SUBSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
	                         ),3
					  )
					  ,(INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
					                        ),3
									),';'
							 )+1),(INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
							                           ),3
											   ),';</ical-expression>'
										)-INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
										                      ),3
													   ),';'
											   )-1
								  )
				) schedule_interval	  	  
	  ,(SELECT max(rh2.requestid) 
	      FROM fusion.ess_request_history rh2 
         WHERE rh2.parentrequestid = rh.parentrequestid
		   AND rh.username = rh2.username
		   AND rh2.state = 1) next_requestid
  FROM fusion.ess_request_property rp
      ,fusion.request_history rh
      ,fusion.ess_request_property rp_Param
      ,fusion.fnd_lookup_values flv
WHERE rp.value LIKE '%TEST%NAME%'
  AND rh.requestid = rp.requestid
  AND rh.processstart >= TRUNC(SYSDATE,'MONTH')
  AND rp_Param.requestid = rp.requestid
  AND rp.NAME = 'ujobname'
  AND flv.lookup_type = 'HRC_LOADER_ESS_STATE'
  AND flv.language = USERENV('LANG')
  AND flv.lookup_code = rh.state  
  AND rh.requestid = (select max(rh2.requestid) from fusion.ess_request_history rh2 
                       where rh2.parentrequestid = rh.parentrequestid
					     and rh.USERNAME = rh2.USERNAME
						 AND rh2.state <> 1) -- 1 is wait
)
SELECT schedule_history.scheduled_user_name
      ,MAX(DECODE (rp.name, 'report_url', value)) 		report_url
      ,MAX(DECODE (rp.name, 'layout_name', value)) 		layout_name
      ,schedule_history.submitted_job_name
      ,schedule_history.schedule_start_date
      ,schedule_history.schedule_end_date
      ,schedule_history.schedule_frequency
      ,schedule_history.schedule_interval
      ,rh.scheduled  							next_schedule_date	  
	  ,flv.meaning 										next_job_status
      ,MAX(DECODE (rp.name, 'CONTROL_XML', value)) 		output_details
      ,MAX(DECODE (rp.name, 'OUTPUT_FORMAT', value)) 		output_format
      ,MAX(DECODE (rp.name, 'save_data', value)) 			output_save_data
      ,MAX(DECODE (rp.name, 'email_notification', value)) email_notification
      ,MAX(DECODE (rp.name, 'is_bursting', value))    	bursting_used
      ,MAX(DECODE (rp.name, 'notify_email_when_failed', value)) 	notify_email_when_failed
      ,MAX(DECODE (rp.name, 'notify_email_when_skipped', value)) 	notify_email_when_skipped
      ,MAX(DECODE (rp.name, 'notify_email_when_success', value)) 	notify_email_when_success
      ,MAX(DECODE (rp.name, 'notify_email_when_warning', value)) 	notify_email_when_warning
      ,MAX(DECODE (rp.name, 'notify_to', value)) 					notify_to
      ,MAX(DECODE (rp.name, 'report_param_displays', value)) 		report_param_displays
      ,MAX(DECODE (rp.name, 'report_params', value)) 				report_params
	  ,schedule_history.last_job_start_date 		last_job_start_date
	  ,schedule_history.last_job_end_date 			last_job_end_date
	 -- ,schedule_history.elapsedtime 				last_job_elapsedtime
	  ,schedule_history.last_job_status 			last_job_status  
	  ,schedule_history.parentrequestid				parent_request_id
	  ,schedule_history.requestid					last_job_request_id
	  ,rh.requestid									scheduled_job_request_id
  FROM schedule_history schedule_history
      ,fusion.request_history rh
      ,fusion.ess_request_property rp	  
	  ,fusion.fnd_lookup_values flv
 WHERE schedule_history.parentrequestid = rh.parentrequestid
   AND schedule_history.next_requestid = rh.requestid
   AND flv.lookup_type = 'HRC_LOADER_ESS_STATE'
   AND flv.language = USERENV('LANG')
   AND flv.lookup_code = rh.state
   AND rp.requestid = rh.requestid   
 GROUP BY schedule_history.scheduled_user_name
         ,schedule_history.submitted_job_name
         ,schedule_history.schedule_start_date
         ,schedule_history.schedule_end_date
         ,schedule_history.schedule_frequency
         ,schedule_history.schedule_interval		 
	 ,rh.scheduled    
	 ,flv.meaning
	 ,schedule_history.last_job_start_date 
	 ,schedule_history.last_job_end_date 	
	 -- ,schedule_history.elapsedtime 			
	 ,schedule_history.last_job_status 		
	 ,schedule_history.parentrequestid	
	 ,schedule_history.requestid
	 ,rh.requestid
Mandeep Gupta 0 Comments
Jan 2 2025
BIP – Query to fetch assignment costing details
SELECT prgd.assignment_id
     , pprd.payroll_relationship_id
     , pprd.payroll_relationship_number
     , prt.legislation_code
     , prt.process_in_run
     , prt.base_rel_type_name
     , prtt.relationship_type_name
     , prtt.description
     , pcaf.effective_start_date
     , pcaf.effective_end_date
     , pcaa.segment1
     , pcaa.segment2
     , pcaa.segment3
     , pcaa.segment4
  FROM pay_rel_groups_dn prgd
     , pay_pay_relationships_dn pprd
     , pay_relationship_types prt
     , pay_relationship_types_tl prtt
     , pay_cost_allocations_f pcaf
     , pay_cost_alloc_accounts pcaa
 WHERE pcaf.source_type = 'ASG'
   AND pprd.payroll_relationship_id = pcaf.payroll_relationship_id
   AND pcaf.cost_allocation_record_id = pcaa.cost_allocation_record_id
   AND pprd.payroll_relationship_id = prgd.payroll_relationship_id
   AND prgd.relationship_group_id = pcaf.source_id
   AND trunc(sysdate) BETWEEN pcaf.effective_start_date AND pcaf.effective_end_date
   AND trunc(sysdate) BETWEEN pprd.start_date AND pprd.end_date
   AND pprd.relationship_type_id = prt.relationship_type_id
   AND prt.relationship_type_id = prtt.relationship_type_id
   AND prtt.language = 'US'
   AND pprd.person_id = (select distinct person_id from per_all_people_f where person_number = '212')
Mandeep Gupta 0 Comments
Oct 19 2024
BIP – Query to extract worker manager job name

Below SQL query below shows how a job name can be pulled for a worker’s manager. The SQL can be easily modified to include further employment related attributes for worker’s line manager.

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
, pjft.name mgr_job_name
, 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_jobs_f_tl pjft
, 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
AND paam_mgr.job_id = pjft.job_id
AND pjft.language = 'US'
AND trunc(sysdate) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
Mandeep Gupta 0 Comments
Sep 23 2024
BIP – Find list of message sent from Oracle Alert

Alert composer a very powerful and useful tool to configure and sent notifications to the users. However, it is very important for business to know the list of receipients for each message. I have tried to built a query to get these details.

SELECT hat.name alert_name
      ,harr.sent_to
      ,harr.sent_on
 FROM HRC_ALERTS_TL hat
     ,HRC_ALERT_RUNS har
     ,HRC_ALERT_RUN_MESSAGES harm
     ,HRC_ALERT_RUN_RECIPIENTS harr
WHERE hat.name = 'Learning Assignment Alert'
  AND hat.alert_id = har.alert_id
  AND hat.language = 'US'
  AND har.created_by = '[email protected]'
  AND TRUNC(har.creation_date) = TRUNC(SYSDATE)
  AND har.run_id = harm.run_id
  AND harm.run_id = harr.run_id
  AND harm.run_message_id = harr.run_message_id
  ORDER BY harr.sent_to

From alert history, one can find the emails triggered but it doesn’t give an option to export this. This is where the query is helpful.

Mandeep Gupta 0 Comments
Jan 26 2024
BIP – Extract Address Style Format Information

You use “Manage Address Formats” task from Setup and Maintenance to define the address style for a particular country. Oracle provides a lot of address format for various countries out of the box. You have the capaibility to customize the address formats as per customer requirement.

For example, you can search for all available address style for United Kingdom using country filter:

You can see below details:

You can edit the address format to customize the address style. You can make a seeded attribute mandatory/ non-mandatory or can add a new address element and many more properties can be set:

You can save the changes. And the address format become active.

One of the main challenges, the data migration team and the integration teams face is the mapping of layout fields to actual backend table attribute in per_addresses_f table.

There is no direct way to find the UI attribute name and its corresponding attribute name in backend table. Below query has been developed to find the UI prompt with actual table attribute name:

SELECT ftt.TERRITORY_SHORT_NAME
      ,hsfl.TERRITORY_CODE
      ,hsflb.STYLE_FORMAT_CODE
      ,hsflb.VARIATION_NUMBER
      ,hsflt.PROMPT	  
      ,hsflb.ATTRIBUTE_CODE
      ,hsflb.LINE_NUMBER
      ,hsflb.MANDATORY_FLAG
      ,hsflb.USE_INITIAL_FLAG
      ,hsflb.UPPERCASE_FLAG
      ,hsflb.STATUS_FLAG
      ,hsflb.TRANSFORM_FUNCTION
      ,hsflb.DELIMITER_BEFORE
      ,hsflb.DELIMITER_AFTER
      ,hsflb.BLANK_LINES_BEFORE
      ,hsflb.BLANK_LINES_AFTER
      ,hsflb.START_DATE_ACTIVE
      ,hsflb.END_DATE_ACTIVE
      ,hsflb.DISPLAY_SIZE
  FROM HZ_STYLE_FMT_LAYOUTS_B hsflb 
      ,HZ_STYLE_FMT_LAYOUTS_TL hsflt
      ,HZ_STYLE_FMT_LOCALES hsfl
      ,FND_TERRITORIES_TL ftt
 WHERE hsflb.STYLE_FORMAT_CODE = hsfl.STYLE_FORMAT_CODE
   AND hsflb.STYLE_FMT_LAYOUT_ID = hsflt.STYLE_FMT_LAYOUT_ID
   AND ftt.TERRITORY_CODE = hsfl.TERRITORY_CODE 
   AND ftt.LANGUAGE = 'US'
   AND ftt.LANGUAGE = hsflt.LANGUAGE
   AND ftt.TERRITORY_CODE = 'MX'
ORDER BY hsflb.STYLE_FORMAT_CODE, hsflb.LINE_NUMBER

Output after running above SQL:

Please note:

COUNTY – REGION_1

STATE – REGION_2

PROVINCE – REGION_3

Mandeep Gupta 0 Comments
Jan 16 2024
HDL – Sample HDL file to End Date Position Valid Grades

There can multiple grades assigned as Valid grade at Position level or Job level. However, there is no direct way to end date the valid grades in bulk.

You can end date a valid grade from responsive UI. But it will be a lot of manual effort. You can search for Position and then navigate to Grades section. Then update the position and click on small delete icon next to Grade name (which you want to end date). This will end date the valid grade with an effective end date = date of position update – 1.

To do this in bulk using HDL, you can’t use DELETE command. If you use DELETE command, it will completely Purge the valid grade record from Position. To end date the valid grade, use “ReplaceLastEffectiveEndDate” attribute in the file.

Below is the sample file:

METADATA|PositionGrade|BusinessUnitName|PositionCode|EffectiveStartDate|EffectiveEndDate|GradeCode|GradeSetCode|ReplaceLastEffectiveEndDate
MERGE|PositionGrade|Progress US Business Unit|PRGUSPOS032|2018/12/31|2023/12/31|Hourly01|PRGUSGRADESET|Y

EffectiveStartDate – Earliest Grade Start Date

EffectiveEndDate – Date on which you want to end date the grade.

Once the file is loaded successfully, below is how the data will look in the backend:

Below SQL query can be used to extract valid grades data:

SELECT DISTINCT 
	   TO_CHAR (pvgf.effective_start_date, 'DD/MON/YYYY') effective_start_date,
	   TO_CHAR (pvgf.effective_end_date, 'DD/MON/YYYY') effective_end_date,
	   pjfv.POSITION_CODE,
	   pjfv.name job_name,
	   pgfv.grade_code,
	   pgfv.name grade_name,
	   pvgf.valid_grade_id,
	   pgfv.grade_id,
	   pjfv.job_id
  FROM per_valid_grades_f pvgf,
       HR_ALL_POSITIONS_F_VL pjfv,
       per_grades_f_vl pgfv 
 WHERE 1=1
   AND pvgf.position_id = pjfv.position_id
   AND pvgf.grade_id = pgfv.grade_id
   AND pjfv.POSITION_CODE = 'PRGUSPOS032'
   AND pvgf.effective_start_date BETWEEN pjfv.effective_start_date AND pjfv.effective_end_date
   AND pvgf.effective_start_date BETWEEN pgfv.effective_start_date AND pgfv.effective_end_date
ORDER BY POSITION_CODE,grade_code
Mandeep Gupta 0 Comments
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
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
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
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 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