Search for:
Nov 29 2021
BIP – Query to extract questions related to Job Requisition
SELECT DISTINCT iirq.REQUISITION_ID
	  ,hqb.QUESTIONNAIRE_ID 
      ,irb.REQUISITION_NUMBER
      ,hqb.QUESTIONNAIRE_CODE
      ,hqb.STATUS
	  ,hct.name		"Category"
	  ,hst.name		"Subscriber"
	  ,hqb.MAX_POSSIBLE_SCORE
	  ,hqnb.question_id
	  ,hqt.question_text
	  ,hqnb.QSTN_VERSION_NUM
	  ,hqnb.QUESTION_CODE
	  ,hqnb.QUESTION_TYPE
  FROM IRC_IM_REQ_QSTNRS iirq
      ,IRC_REQUISITIONS_B irb
      ,HRQ_QUESTIONNAIRES_B hqb
	  ,HRQ_CATEGORIES_TL hct
	  ,HRQ_SUBSCRIBERS_TL hst
	  ,HRQ_QSTNR_SECTIONS_B hqsb
      ,HRQ_QSTNR_QUESTIONS hqq
      ,HRQ_QUESTIONS_B hqnb
      ,HRQ_QUESTIONS_TL hqt
WHERE iirq.REQUISITION_ID = irb.REQUISITION_ID
  AND irb.REQUISITION_ID = 300000237266130
  AND iirq.QUESTIONNAIRE_ID = hqb.QUESTIONNAIRE_ID
  AND hqb.category_id = hct.category_id
  AND hct.language = 'US'
  AND hqb.subscriber_id = hst.subscriber_id
  AND hst.language = 'US'
  AND hqsb.QUESTIONNAIRE_ID     = hqb.QUESTIONNAIRE_ID
  AND hqsb.QSTNR_VERSION_NUM    = hqb.QSTNR_VERSION_NUM
  AND hqsb.BUSINESS_GROUP_ID    = hqb.BUSINESS_GROUP_ID
  AND hqq.QSTNR_SECTION_ID      = hqsb.QSTNR_SECTION_ID
  AND hqq.BUSINESS_GROUP_ID     = hqsb.BUSINESS_GROUP_ID
  AND hqq.QUESTION_ID           = hqnb.QUESTION_ID
  AND hqq.QSTN_VERSION_NUM      = hqnb.QSTN_VERSION_NUM
  AND hqq.BUSINESS_GROUP_ID     = hqnb.BUSINESS_GROUP_ID
  AND hqnb.QUESTION_ID          = hqt.QUESTION_ID 
  AND hqt.language = 'US'
Mandeep Gupta 0 Comments
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 25 2021
HDL – Sample file for Dependent Enrollments
METADATA|DependentEnrollment|PersonNumber|ParticipantLastName|ParticipantFirstName|BenefitRelationship|LifeEvent|LifeEventOccuredDate|CloseLifeEvent|CloseLifeEventDate|EffectiveDate|LegalEmployer

MERGE|DependentEnrollment|22211|Singh|Manu|DFLT|New Hire|2021/01/01|Y|2021/01/01|2021/01/01|XYZ Legal Employer



METADATA|DesignateDependent|PersonNumber|Plan|Program|Option|OriginalEnrollmentDate|DependentPersonNumber|LineNumber|DependentFirstName|DependentLastName|DependentDateOfBirth
MERGE|DesignateDependent|22211|Benefit Plan 1|Benefit Program|Employee Only|2020/01/01|122211|1|Singh|Test 1|1992/01/31
MERGE|DesignateDependent|22211|Benefit Plan 1|Benefit Program|Employee Only|2020/01/01|122212|2|Singh|Test 2|2015/01/01
MERGE|DesignateDependent|22211|Benefit Plan 1|Benefit Program|Employee Only|2020/01/01|122213|3|Singh|Test 3|2017/01/05
Mandeep Gupta 0 Comments
Nov 20 2021
HDL – Sample file to delete element eligibility

HDL supports deletion of element eligibilities. You can make use of PayrollElementDefinition business object. Child object ElementEligibility supports create, update and delete operations.

You can create a simple BIP query to extract the requisite data and create an HDL file out of it:

select pelf.element_link_id
      ,petf.element_type_id
      ,petf.base_element_name
      ,pelf.effective_start_date 
  from pay_element_types_f petf
     , pay_element_links_f pelf
 where petf.element_type_id = pelf.element_type_id
   and element_link_id=300000175215375   
   and TRUNC(sysdate) BETWEEN petf.effective_start_date and petf.effective_end_date

Sample HDL file:

METADATA|ElementEligibility|ElementLinkId|ElementId|EffectiveStartDate
DELETE|ElementEligibility|300000175215375|300000175200627|1951/01/01

Sometime you may get an error that You can’t delete element eligibility as element entries exist for this eligibility. In this case first you need to identify the corresponding element entries and Delete those and then retry deleting element eligibility.

Please check mos note – When Attempting to Delete Element Eligibility Get Error ‘The element eligibility record can’t be deleted because it would invalidate existing element entries with effective start dates in the future. (PAY-1635756)’ (Doc ID 2686914.1) for sample query and sample file for element entry deletion.

Version Validated : 21C

Mandeep Gupta 0 Comments
Nov 20 2021
BIP – Query to extract count of persons without Primary email address
SELECT COUNT(DISTINCT pea.person_id)
  FROM per_email_Addresses pea
 WHERE pea.email_type = 'W1'
   AND NOT EXISTS (select 1 from per_all_people_f papf
                    where papf.primary_email_id = pea.email_address_id)

Below is the query to find active employees without primary email address:

SELECT 'Total Employees without Primary Work Email ', COUNT(DISTINCT pea.person_id)
  FROM per_email_Addresses pea
 WHERE pea.email_type = 'W1'
  AND NOT EXISTS (select 1 from per_all_people_f papf
                   where papf.primary_email_id = pea.email_address_id) 
UNION
SELECT 'Active Employees without Primary Work Email ', COUNT(DISTINCT pea.person_id)
  FROM per_email_Addresses pea
 WHERE pea.email_type = 'W1'
  AND NOT EXISTS (select 1 from per_all_people_f papf
                   where papf.primary_email_id = pea.email_address_id) 
  AND EXISTS (SELECt 1 from per_all_assignments_m paam
               where assignment_type ='E'
                 and trunc(sysdate) between effective_start_date and effective_end_date
                 and paam.person_id = pea.person_id	
                 and paam.assignment_status_type like 'ACTIVE%')
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 19 2021
BIP – Query to extract element eligibility details
SELECT fff.formula_id

   ,fff.formula_type_id

   ,fff.formula_name

   ,fff.creation_date

   ,fftt.formula_type_name

   ,petft.base_element_name "result rules element_name"

 ,pfrrf.element_type_id "result rules element_id"

   ,pfrrf.result_name

   ,pfrrf.result_rule_type

 ,petft_spr.base_element_name "status proc rules element_name"

   ,psprf.element_type_id "status proc rules element_id"

   ,pivf.base_name

 FROM PAY_FORMULA_RESULT_RULES_F pfrrf

   ,PAY_STATUS_PROC_RULES_F psprf

   ,FF_FORMULAS_F fff

   ,FF_FORMULA_TYPES_TL fftt

   ,PAY_ELEMENT_TYPES_F petft

   ,PAY_ELEMENT_TYPES_F petft_spr

,PAY_INPUT_VALUES_F pivf

WHERE pfrrf.STATUS_PROCESSING_RULE_ID = psprf.STATUS_PROCESSING_RULE_ID

 AND fff.FORMULA_TYPE_ID = fftt.FORMULA_TYPE_ID

 AND fftt.LANGUAGE = 'US'

 AND fff.FORMULA_ID = psprf.FORMULA_ID  

 AND petft_spr.ELEMENT_TYPE_ID = psprf.ELEMENT_TYPE_ID

 AND petft.ELEMENT_TYPE_ID = pfrrf.ELEMENT_TYPE_ID

 AND petft.ELEMENT_TYPE_ID = pivf.ELEMENT_TYPE_ID

 AND pfrrf.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

 AND TRUNC(SYSDATE) BETWEEN pfrrf.EFFECTIVE_START_DATE AND pfrrf.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN psprf.EFFECTIVE_START_DATE AND psprf.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN fff.EFFECTIVE_START_DATE AND fff.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN petft.EFFECTIVE_START_DATE AND petft.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN petft_spr.EFFECTIVE_START_DATE AND petft_spr.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

 --AND RESULT_RULE_TYPE = 'I'

 AND fff.formula_name = 'BASIC SALARY UK EARNINGS'
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 – Query to find Workers with missing Payrolls
SELECT papf.person_number 
      ,ppnf.full_name
      ,paam.assignment_number
  FROM per_all_people_f papf,
       per_person_names_f ppnf,
       per_all_assignments_m paam
 WHERE paam.person_id = ppnf.person_id
   AND papf.person_id = ppnf.person_id
   AND papf.person_id = paam.person_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 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'
   AND NOT EXISTS (SELECT 1
                     FROM pay_rel_groups_dn prgd
					     ,pay_pay_relationships_dn prrd 
						 ,pay_assigned_payrolls_dn papd
	                     ,pay_payroll_terms ppt
					WHERE prgd.assignment_id = paam.assignment_id
					  AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
					  AND papd.payroll_term_id = ppt.payroll_term_id
					  AND ppt.payroll_relationship_id = prrd.payroll_relationship_id) 
ORDER BY 1,2
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 24 2021
BIP – Query to Extract Learner Completion Details

Use below query to extract the learner completion details. The same query can be used to generate the Learning Record HDL format with small changes.

SELECT TO_CHAR(warf.EFFECTIVE_START_DATE,'RRRR/MM/DD') "Learning Record EffectiveStartDate"
	   ,warf.ASSIGNMENT_RECORD_NUMBER "Learning Record Number"
	   ,weaf.ASSIGNMENT_PROFILE_NUMBER "Assignment Number"
	   ,wlif.LEARNING_ITEM_TYPE "Learning ItemType"
	   ,wlif.LEARNING_ITEM_NUMBER "Learning ItemNumber"
	   ,warf.EVENT_TYPE "Assignment Type"
	   ,warf.EVENT_SUB_TYPE "Assignment SubType"
	   ,warf.ATTRIBUTION_TYPE "Assignment AttributionType"
	   ,warf.ATTRIBUTION_LOOKUP_CODE "Assignment AttributionCode"
       ,papf.PERSON_NUMBER "Learner Number"
	   ,warf.STATUS "Learning RecordStatus"
       ,TO_CHAR(NVL(warf.ASSIGNMENT_STARTED_ON_DATE,warf.EFFECTIVE_START_DATE),'RRRR/MM/DD') "Learning Record StartDate"
       ,TO_CHAR(warf.CALCULATED_DUE_DATE,'RRRR/MM/DD') "Due Date"	   
       ,TO_CHAR(warf.COMPLETION_DATE,'RRRR/MM/DD') "Completion Date"	   
       ,warf.ACTUAL_SCORE "Actual Score"
  FROM WLF_LEARNING_ITEMS_F wlif,
       WLF_ASSIGNMENT_RECORDS_F warf,
       WLF_EVENT_ASSIGNMENTS_F weaf,
       PER_ALL_PEOPLE_F papf	   
 WHERE to_char(warf.LEARNING_ITEM_ID) = to_char(wlif.LEARNING_ITEM_ID)
   AND warf.STATUS = 'ORA_ASSN_REC_COMPLETE'
   AND TRUNC(SYSDATE) BETWEEN warf.EFFECTIVE_START_DATE AND warf.EFFECTIVE_END_DATE
   AND TRUNC(SYSDATE) BETWEEN wlif.EFFECTIVE_START_DATE AND wlif.EFFECTIVE_END_DATE
   AND warf.learner_id = papf.person_id
   AND TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
   AND weaf.EVENT_ASSIGNMENT_ID = warf.EVENT_ASSIGNMENT_ID
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 30 2021
BIP – Extract UDT data

Use below query to extract data related to User Defined Tables in Oracle HCM

select fut.base_user_table_name		"User Table"
      ,(select pldgt.name 
         from per_legislative_data_groups_tl pldgt 
        where pldgt.legislative_data_group_id=fucif.legislative_data_group_id
          and pldgt.language=userenv('LANG')) 	"Legsilative Data Group"
      ,fuc.base_user_column_name	"Column Name"
	  ,furf.row_low_range_or_name	"Row Name"
      ,to_char(fucif.effective_start_date,'YYYY/MM/DD') "Value Date Start"
	  ,to_char(fucif.effective_end_date,'YYYY/MM/DD')	"Value Date End"
      ,fucif.value
 from  ff_user_column_instances_f fucif,
       ff_user_columns fuc,
       ff_user_rows_f furf,
       ff_user_tables fut
 where fucif.user_column_id=fuc.user_column_id
   and furf.user_row_id=fucif.user_row_id
   and furf.user_table_id=fut.user_table_id
   and fuc.user_table_id=fut.user_table_id
Mandeep Gupta 0 Comments
Sep 26 2021
HDL – Sample HDL to load Performance Rating

Historical performance ratings can be loaded into Worker Talent Profile using TalentProfile.dat business object.

Below is the sample HDL (enhanced profiles (v2)), to load performance rating data:

METADATA|ProfileItem|ProfileItemId|ProfileId|ProfileCode|ContentItemId|ContentTypeId|ContentType|CountryId|DateFrom|DateTo|RatingModelId1|RatingModelCode1|RatingLevelId1|RatingLevelCode1|SectionId|SourceSystemOwner|SourceSystemId
MERGE|ProfileItem|||PER_0055|||PERFORMANCE_RATING||2018/01/01|2018/12/31||XXX_PERFORMANCE||3.75|9933|HRC_SQLLOADER|PR_0055
MERGE|ProfileItem|||PER_0031|||PERFORMANCE_RATING|| 2018/01/01|2018/12/31||XXX_PERFORMANCE||3.90|9933| HRC_SQLLOADER |PR _0055
Mandeep Gupta 0 Comments
Sep 26 2021
BIP – Query to extract business unit attached to a Job

In multi country HCM Implementations, work structures data can be stored in a COMMON set or a country specific set. In such cases, it becomes important to show the correct business unit name against the jobs in reports.

Use the below report to extract the Job and the business unit:

SELECT BU_ID
      ,BU_NAME
	  ,DEFAULT_SET_ID
	  ,SHORT_CODE
	  ,pjft.name
FROM  PER_JOBS_F pjf
     ,PER_JOBS_F_TL pjft
     ,FUN_ALL_BUSINESS_UNITS_V fabuv
WHERE pjf.SET_ID = fabuv.DEFAULT_SET_ID
and pjf.job_id = pjft.job_id
and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
and trunc(sysdate) between pjft.effective_start_date and pjft.effective_end_date
and pjft.language = 'US'
order by pjft.name

To get more details on SET Name, Set assignments, you can make use of following tables:

FND_SETID_ASSIGNMENTS

FND_SETID_SETS_VL

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
Sep 22 2021
BIP – Query to extract Fast Formulas used in HCM Extracts

Use the below query to extract list of “Extract Rule Type” fast formulas used in Oracle HCM Extracts:

select pedv.definition_name
      ,ffv.formula_name
  from per_ext_data_elements_vl pedev
      ,pay_report_records_f prrf
      ,pay_report_blocks prb
      ,per_ext_definitions_vl pedv
      ,ff_formulas_vl ffv 
where pedev.report_record_id = prrf.report_record_id
  and prrf.report_block_id = prb.report_block_id
  and prb.ext_definition_id = pedv.ext_definition_id
  and pedv.definition_name LIKE 'Dec%Absence%'
  and ffv.formula_id = pedev.rule_id
Mandeep Gupta 0 Comments