Search for:
Nov 27 2021
BIP – SQL to extract Job Valid grades
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.job_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,
       per_jobs_f_vl pjfv,
       per_grades_f_vl pgfv 
 WHERE 1=1
   AND pvgf.job_id = pjfv.job_id
   AND pvgf.grade_id = pgfv.grade_id
   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 job_code,grade_code
Mandeep Gupta 0 Comments
Nov 20 2021
BIP – Query to extract Course details with offering and activitiy details
WITH course as 
(
 SELECT 
      LearningItemDEO.LEARNING_ITEM_ID,             
      LearningItemDEO.EFFECTIVE_START_DATE,         
      LearningItemDEO.EFFECTIVE_END_DATE,           
      LearningItemDEO.LEARNING_ITEM_TYPE,
      LearningItemDEO.LEARNING_ITEM_SUB_TYPE,          
      LearningItemDEO.LEARNING_ITEM_NUMBER,         
      LearningItemDEO.STATUS,                    
       to_date(TO_CHAR( LearningItemDEO.CREATION_DATE,'MM/DD/YYYY'),'MM/DD/YYYY') AS LearningItemDEOCreationDate,                
       LearningItemTranslationDEO.NAME,
       LearningItemTranslationDEO.DESCRIPTION,
       LearningItemDEO.LI_START_DATE as LearningItemDEOLiStartDate,
       LearningItemDEO.LI_END_DATE as LearningItemDEOLiEndDate
       FROM WLF_LEARNING_ITEMS_F LearningItemDEO,
       WLF_LEARNING_ITEMS_F_TL LearningItemTranslationDEO

       WHERE  LearningItemDEO.LEARNING_ITEM_TYPE IN ('ORA_COURSE')
       AND LearningItemDEO.LEARNING_ITEM_ID          = LearningItemTranslationDEO.LEARNING_ITEM_ID
       AND TRUNC(SYSDATE) BETWEEN LearningItemTranslationDEO.EFFECTIVE_START_DATE AND LearningItemTranslationDEO.EFFECTIVE_END_DATE
       AND TRUNC(SYSDATE) BETWEEN LearningItemDEO.EFFECTIVE_START_DATE AND LearningItemDEO.EFFECTIVE_END_DATE
       AND LearningItemTranslationDEO.LANGUAGE = USERENV('lang')
)
, offering as
(
select WLFI.LEARNING_ITEM_NUMBER
      ,WLFI.LEARNING_ITEM_ID Offering_LII
      ,TO_CHAR(WLFI.EFFECTIVE_END_DATE,'YYYY/MM/DD')
      ,TO_CHAR(WLFI.EFFECTIVE_START_DATE,'YYYY/MM/DD')
      ,WLFT.DESCRIPTION
      ,WLFT.DESCRIPTION_LONG
      ,WLCF.ENABLE_CAPACITY
      ,WLCF.ENABLE_WAITLIST
      ,WLFI.LANGUAGE_CODE
	  ,WLFI.STATUS
      ,WLCLF.MAXIMUM_ATTENDEES
      ,WLCLF.MINIMUM_ATTENDEES
      ,TO_CHAR(WLCLF.ENROLMENT_END_DATE,'YYYY/MM/DD')
      ,TO_CHAR(WLCLF.ENROLMENT_START_DATE,'YYYY/MM/DD')
      ,WLCLF.DELIVERY_MODE
      ,TO_CHAR(WLFI.END_DATE,'YYYY/MM/DD')
      ,TO_CHAR(WLFI.START_DATE,'YYYY/MM/DD') offer_start_Date
      ,WLFT.NAME
	  ,WLCLF.PRIMARY_CLASSROOM_ID
	  ,WLCF.LEARNING_ITEM_ID WLCF_LEARNING_ITEM_ID
	  ,(SELECT DISTINCT LOCATION_CODE FROM HR_LOCATIONS_ALL WHERE LOCATION_ID = WLCLF.LOCATION_ID
           AND WLFI.START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) Offer_Venue
 FROM WLF_LEARNING_ITEMS_F WLFI,
      WLF_LI_COURSES_F WLCF, --LEARNING_ITEM_ID course
      WLF_LI_CLASSES_F WLCLF,
      WLF_LEARNING_ITEMS_F_TL WLFT
WHERE WLFI.LEARNING_ITEM_ID = WLFT.LEARNING_ITEM_ID 
  AND WLFI.LEARNING_ITEM_ID = WLCLF.LEARNING_ITEM_ID 
  AND WLCLF.COURSE_LEARNING_ITEM_ID = WLCF.LEARNING_ITEM_ID
  AND WLFT.LANGUAGE = USERENV('lang')
  AND TRUNC(SYSDATE) BETWEEN WLFI.EFFECTIVE_START_DATE AND WLFI.EFFECTIVE_END_DATE 
  AND TRUNC(SYSDATE) BETWEEN WLCF.EFFECTIVE_START_DATE AND WLCF.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN WLCLF.EFFECTIVE_START_DATE AND WLCLF.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN WLFT.EFFECTIVE_START_DATE AND WLFT.EFFECTIVE_END_DATE
  AND WLFI.LEARNING_ITEM_TYPE = 'ORA_CLASS'
)  
SELECT course.name								"Course Name"
	  ,course.learning_item_number				"Course Number"
	  ,offering.name							"Offering Name"
      ,offering.LEARNING_ITEM_NUMBER			"Offering Number"
      ,offering.offer_start_Date				"Offering Start Date"
      ,offering.DELIVERY_MODE					"Offering Delivery Mode"
      ,offering.STATUS							"Offering Status"
      ,offering.Offer_Venue						"Offering Venue"
	  ,offering.PRIMARY_CLASSROOM_ID
	  ,WLFT.name								"Activity Name"
	  ,WLFI.LEARNING_ITEM_NUMBER				"Activity Number"
	  ,WLFI.start_date
	  ,WLFI.end_date
	  ,wlaf.virtual_classroom_url  				"VirtualClassroomUrl"
	  ,wlaf.SELF_COMPLETE_FLAG 					"SelfCompleteFlag"
      ,wlaf.TIME_ZONE		 					"TimeZone"
      ,TO_CHAR(CAST(WLFI.start_date AT TIME ZONE wlaf.time_zone AS DATE),'HH:Mi AM','nls_date_language=American') activity_start_time	  
      ,TO_CHAR(CAST(WLFI.end_date AT TIME ZONE wlaf.time_zone AS DATE),'HH:Mi AM','nls_date_language=American') activity_end_time	  
	  ,(select wrt.name
	      from wlf_resources_tl wrt
		 where language = 'US'
		   and wrt.resource_id = wlb.BOOKED_ITEM_ID)	"Classroom Locations"
  FROM course
	  ,offering
	  ,WLF_LI_ACTIVITIES_F wlaf
	  ,WLF_LEARNING_ITEMS_F_TL wlft
	  ,WLF_LEARNING_ITEMS_F wlfi
	  ,WLF_LI_BOOKINGS  wlb
 WHERE 1=1
   AND course.LEARNING_ITEM_ID = offering.WLCF_LEARNING_ITEM_ID
   AND offering.LEARNING_ITEM_NUMBER = 'OLC133462'
   AND wlaf.PARENT_LEARNING_ITEM_ID = offering.Offering_LII
   AND wlaf.LEARNING_ITEM_ID = WLFT.LEARNING_ITEM_ID
   AND WLFI.LEARNING_ITEM_ID = WLFT.LEARNING_ITEM_ID
   AND wlb.learning_item_id (+) = wlaf.LEARNING_ITEM_ID
   AND wlb.BOOKED_ITEM_TYPE (+) = 'ORA_RESOURCE'
   AND WLFT.language = 'US'
   AND TRUNC(SYSDATE) BETWEEN wlaf.effective_start_date AND wlaf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN WLFT.effective_start_date AND WLFT.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN WLFI.effective_start_date AND WLFI.effective_end_date
Mandeep Gupta 0 Comments
Nov 14 2021
BIP – Extract Termiation approval details
select distinct module_identifier "processname"
     , subject
	 , wf.title	"Notification Title"
         , hth.initiator_user_id "submittedby" 
	 , wf.creator
	 , wf.fromuser
	 , wf.fromuserdisplayname
	 , hth.transaction_id
         , module_identifier
         , object
         , object_id
	 , wf.assignees
	 , wf.assigneesdisplayname
	 , wf.approvers
	 , wf.assigneddate
	 , wf.state
	 , wf.enddate	"Approval Date"
from hrc_txn_header hth, 
     fa_fusion_soainfra.wftask wf	 
where wf.identificationkey = TO_CHAR (hth.transaction_id)
  --and wf.outcome = 'APPROVE'
  and wf.compositename like '%Termination%'
Mandeep Gupta 0 Comments
Nov 5 2021
BIP – Report to get basic employee details
SELECT papf.person_number 
      ,ppnf.first_name
	  ,ppnf.middle_names
      ,ppnf.last_name
      ,ppnf.title
      ,pjft.name job_name
      ,hauft.name department_name
      ,haouf.attribute1 	  
      ,haouf.attribute2 	  
      ,haouf.attribute3 	  
      ,haouf.attribute4 	  
      ,to_char(ppos.date_start,'DD-MM-RRRR', 'nls_date_language=American') date_of_joining
      ,to_char(pp.date_of_birth,'DD-MM-RRRR', 'nls_date_language=American') date_of_birth
  FROM per_all_people_f papf,
       per_person_names_f ppnf,
       per_all_assignments_m paam,
       per_jobs_f_tl pjft,
       per_periods_of_service ppos,
       per_persons pp,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 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 paam.period_of_service_id  = ppos.period_of_service_id
   AND paam.person_id = pp.person_id 
   AND haouf.organization_id = paam.organization_id
   AND haouf.organization_id = hauft.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 haouf.effective_start_date AND haouf.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 paam.assignment_type ='E'
   AND paam.assignment_status_type = 'ACTIVE' 
   AND ppnf.name_type = 'GLOBAL'
ORDER BY 1,2
Mandeep Gupta 0 Comments
Oct 5 2021
BIP – Query to extract GL Cost Details from Department

GL Cost center Information can be maintained at Department Level.

This information can be extracted using below SQL:

SELECT DISTINCT houft.name, hoif.ORG_INFORMATION2	"Value Set Id"
       ,hoif.ORG_INFORMATION3	"Cost Center 1"
	   ,hoif.ORG_INFORMATION7	"Record Identifier"
  FROM HR_ORGANIZATION_INFORMATION_F hoif,
       HR_ORGANIZATION_UNITS_F_TL houft
 WHERE trunc(sysdate) BETWEEN hoif.EFFECTIVE_START_DATE AND hoif.EFFECTIVE_END_DATE
   AND houft.organization_id = hoif.organization_id
   AND hoif.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
   AND houft.language = 'US'
   AND TRUNC(sysdate) BETWEEN houft.EFFECTIVE_START_DATE AND houft.EFFECTIVE_END_DATE
ORDER by 1

To bulk upload GL Cost Centers Organization.dat HDL can be used. Please check the below link for more details:

https://fusionhcmconsulting.com/2021/01/hdl-sample-hdl-to-load-gl-information-at-department-level/

Mandeep Gupta 0 Comments
Sep 24 2021
BIP – Configure AuditViewDB for BIP Reports Audit

In order to audit the BIP reports, AuditViewDB should be first configured in list of data sources.

Follow the below steps to configure it:

  1. Login to your Fusion application instance (analytics).
  2. Click on Administration -> Manage BI Publisher -> Data Sources -> JNDI Connection

3. Click on Add Data Source and enter below details and click on Test Connection (it may take couple of click to open the Add Data Source page):

4. Once the connection is successful, you can start using it in BIP data models.

Mandeep Gupta 0 Comments
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
May 11 2021
BIP – Query to get element entry details
Select DISTINCT peevf.element_entry_value_id
,peef.element_entry_id
,petf.base_element_name
,peevf.effective_start_date
,paam.assignment_number
,pivf.base_name
from per_all_assignments_m paam
,pay_element_types_f petf
,pay_element_entries_f peef
,pay_element_entry_values_f peevf
,pay_input_values_f pivf
where 1=1
and paam.person_id = peef.person_id
and peef.element_type_id = petf.element_type_id
and pivf.element_type_id = petf.element_type_id
and peef.element_entry_id = peevf.element_entry_id
and paam.ASSIGNMENT_TYPE in ('E')
and paam.primary_assignment_flag = 'Y'
and petf.base_element_name = 'Dental Plan'
and pivf.base_name = 'Amount'
and paam.assignment_number = 'E1111'
and trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date

Mandeep Gupta 0 Comments
Mar 29 2021
BIP (Reports) – Query to extract Accrual details
select papf.person_number,
aapft.name aapftan_name ,
apae.begin_bal,
apae.accrued,
apae.used,
apae.accrual_period,
apae.end_bal,
apae.first_last_prd_in_aapft_term
from per_all_peoaapfte_f papf,
anc_per_accrual_entries apae,
anc_absence_aapftans_f_tl aapft
where 1=1
and papf.person_id = apae.person_id
and apae.aapftan_id= aapft.absence_aapftan_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and aapft.language ='US'
and trunc(sysdate) between aapft.effective_start_date and aapft.effective_end_date
and apae.accrual_period <= TO_DATE('2021/12/31','YYYY/MM/DD')
Mandeep Gupta 0 Comments
Mar 28 2021
Reports (BIP) – Basic Query for Checklist E-sign Report

Oracle provided a new functionality to include e signatures in checklist tasks. A custom report can be configured and upon e-signature and completion, it will be saved to worker’s document of records.

You can follow the below document on my oracle support for detailed steps – 2611795.1

Below is the basic SQL query to start the data model development. You can add additional tables/columns as per your need.

SELECT DISTINCT Person.PERSON_ID
,PAT.ALLOCATED_CHECKLIST_ID AS P_ALLOCATED_CHECKLIST_ID
--,PAT.ALLOCATED_TASK_ID AS TASK_ID
,PersonName.FULL_NAME FULL_NAME
,PersonName.DISPLAY_NAME DISPLAY_NAME
,PersonName.first_name FIRST_NAME
,PersonName.last_name LAST_NAME
,PersonName.middle_names MIDDLE_NAMES
,to_char(Person.date_of_birth,'DD-MM-RRRR', 'nls_date_language=American') DOB
,(SELECT flv.meaning
FROM FND_LOOKUP_VALUES flv
WHERE flv.lookup_type = 'TITLE'
AND flv.language = 'US'
AND flv.lookup_code = PersonName.title) TITLE
--,PAT.SIGNER_NAME AS P_SIGNER_NAME
--,TO_CHAR(PAT.SIGN_DATE,'MM/DD/YY') AS P_SIGN_DATE
,:P_SIGNER_NAME P_SIGNER_NAME
,:P_SIGN_DATE P_SIGN_DATE
FROM PER_PERSONS Person,
PER_PERSON_NAMES_F PersonName,
PER_ALLOCATED_CHECKLISTS PAC,
PER_ALLOCATED_TASKS_VL PAT
WHERE PAC.ALLOCATED_CHECKLIST_ID = NVL(:P_ALLOCATED_CHECKLIST_ID,PAC.ALLOCATED_CHECKLIST_ID)
AND PAC.ALLOCATED_CHECKLIST_ID=PAT.ALLOCATED_CHECKLIST_ID
AND PAT.PERFORMER_ORIG_SYS_ID = Person.PERSON_ID
AND PersonName.PERSON_ID = Person.PERSON_ID
AND PersonName.NAME_TYPE = 'GLOBAL'
AND GREATEST(TRUNC(PAC.ACTION_DATE),TRUNC(SYSDATE)) BETWEEN LEAST(TRUNC(SYSDATE),PersonName.EFFECTIVE_START_DATE) AND PersonName.EFFECTIVE_END_DATE
AND PAT.STATUS='COM'
AND PAT.allocated_task_id=3000012222113131
Mandeep Gupta 0 Comments
Feb 27 2021
HDL – Query to get Assignment Grade Steps
SELECT 'MERGE' "METADATA"
      ,'AssignmentGradeSteps' "AssignmentGradeSteps"
      ,pagsf.EFFECTIVE_START_DATE "EffectiveStartDate"
      ,pagsf.EFFECTIVE_END_DATE "EffectiveEndDate"
      ,pagsf.ASSIGN_GRADE_STEP_ID "AssignGradeStepId"
      ,paam.assignment_number "AssignmentNumber"
      ,pav.ACTION_CODE "ActionCode"
      ,parv.ACTION_REASON_CODE "ReasonCode"
      ,pgsfv.name "GradeStepName"
      ,NULL "NewGradeStepName" --> to be supplied
 FROM PER_ASSIGN_GRADE_STEPS_F pagsf,
      PER_GRADE_STEPS_F_VL pgsfv,
      PER_ALL_ASSIGNMENTS_M paam,
      PER_ACTION_OCCURRENCES pao,
      PER_ACTIONS_VL pav,
      PER_ACTION_REASONS_VL parv
WHERE pagsf.GRADE_STEP_ID = pgsfv.GRADE_STEP_ID
  AND TRUNC(SYSDATE) BETWEEN pagsf.effective_start_date AND pagsf.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN pgsfv.effective_start_date AND pgsfv.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  AND pagsf.assignment_id = paam.assignment_id
  AND paam.assignment_type = 'E'
  AND paam.effective_latest_change = 'Y'
  AND pagsf.ACTION_OCCURRENCE_ID = pao.ACTION_OCCURRENCE_ID
  AND pav.action_id = pao.action_id
  AND parv.action_reason_id = pao.action_reason_id
Mandeep Gupta 0 Comments