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