Category: Reports - Page 4

HDL Reports

Mar 18 2022

BIP – Query to extract Rating Model data in HDL Format

You can use below query to extract Rating Model data from fusion environment for updates.

SELECT 'METADATA|RatingModel|RatingModelCode|DateFrom|DateTo|RatingName|RatingDescription|SourceSystemId|SourceSystemOwner' DATA_LINE, 1 ORDER_NUM
  FROM DUAL
UNION
select
'MERGE'
||'|'||
'RatingModel'
||'|'||
hrmb.rating_model_code
||'|'||
TO_CHAR(hrmb.date_from,'YYYY/MM/DD')
||'|'||
TO_CHAR(hrmb.date_to,'YYYY/MM/DD')
||'|'||
rtl.rating_name
||'|'||
rtl.rating_description
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner  DATA_LINE, 2 ORDER_NUM
FROM hrt_rating_models_b hrmb,
     hrt_rating_models_tl rtl,
     hrc_integration_key_map hikm
WHERE hrmb.rating_model_id = rtl.rating_model_id
  AND hikm.surrogate_id = hrmb.rating_model_id
  AND rtl.language = 'US'
ORDER BY ORDER_NUM

Save the data as RatingModel.dat before uploading to Fusion. You can make use of this query to extract data for reference or use it as a method to extract data from one instance and upload it to a different instance.

Check below post for sample RatingModel HDL file:

https://fusionhcmconsulting.com/2022/03/hdl-sample-file-to-upload-rating-model/

Mandeep Gupta 0 Comments

Reports

Feb 25 2022

BIP – Query to check if employee bank account is active or not

select ext_bank_account_id
,case when nvl(end_date,trunc(sysdate)) >= trunc(sysdate)
 then 'Active'
 else 'Inactive'
end active_inactive
from IBY_EXT_BANK_ACCOUNTS

Mandeep Gupta 0 Comments

HDL Reports

Feb 25 2022

HDL – Sample HDL to Update User Table value

SQL Query to extract data for a particular value of a user table:

SELECT DATA
FROM (
SELECT 'METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId' DATA, 1 DATA_ROW
FROM DUAL
UNION ALL
SELECT 
'MERGE'
||'|'||
'UserDefinedTableColumnInstance'
||'|'||
to_char(fuci.EFFECTIVE_END_DATE,'YYYY/MM/DD')
||'|'||
to_char(fuci.EFFECTIVE_START_DATE,'YYYY/MM/DD')
||'|'||
fuci.LEGISLATIVE_DATA_GROUP_ID
||'|'||
fuci.USER_COLUMN_ID
||'|'||
fuci.USER_COLUMN_INSTANCE_ID
||'|'||
fuci.USER_ROW_ID
||'|'||
fuci.Value
||'|'||
hikm.source_system_owner  
||'|'||
hikm.source_system_id
||'|'||
fuc.USER_TABLE_ID
 DATA, 2 DATA_ROW
FROM 
FF_USER_COLUMN_INSTANCES_F fuci,
FF_USER_COLUMNS fuc,
FF_USER_TABLES ft,
HRC_INTEGRATION_KEY_MAP hikm
WHERE fuci.USER_COLUMN_ID=fuc.USER_COLUMN_ID
AND fuc.USER_TABLE_ID=ft.USER_TABLE_ID
AND hikm.surrogate_id = fuci.USER_COLUMN_INSTANCE_ID
AND ft.BASE_USER_TABLE_NAME = 'TEST_WAGE'
) ORDER BY DATA_ROW

Sample HDL:

METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId

MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|300000046974912|300000175181219|300000175188123|300000175187989|5.20|FUSION|300000175144123|300000175181333

Mandeep Gupta 0 Comments

HDL Reports

Feb 25 2022

BIP – HDL to update Grade Rate values

SQL to extract data in HDL Format:

SELECT DATA
FROM(
SELECT 'METADATA|GradeRateValue|SourceSystemId|SourceSystemOwner|EffectiveStartDate|EffectiveEndDate|RateId|GradeCode|SetCode|LegislativeDataGroup|MinimumAmount|MaximumAmount|MidValueAmount|ValueAmount' DATA, 1 DATA_ROW
FROM DUAL
UNION ALL
SELECT 'MERGE|GradeRateValue|'
||
hikm.source_system_id 
||'|'||
hikm.source_system_owner
||'|'||
TO_CHAR(prvf.effective_start_date,'YYYY/MM/DD')
||'|'||
TO_CHAR(prvf.effective_end_date,'YYYY/MM/DD')
||'|'||
prvf.rate_id
||'|'||
pgf.grade_code
||'|'||
fssv.set_code
||'|'||
ldg.name
||'|'||
prvf.minimum
||'|'||
prvf.maximum
||'|'||
prvf.mid_value
||'|'||
prvf.value DATA, 2 DATA_ROW
FROM  per_rate_values_f prvf,
      per_legislative_data_groups_tl ldg,
      per_grades_f pgf,
      fnd_setid_sets_vl fssv,
      per_rates_f pr,
      hrc_integration_key_map hikm
where 1=1
and pr.legislative_data_group_id = ldg.legislative_data_group_id
and ldg.language = USERENV('LANG')
and trunc(sysdate) between prvf.effective_start_date and prvf.effective_end_date
and upper(prvf.rate_object_type) = 'GRADE'
and prvf.rate_object_id = pgf.grade_id
and trunc(sysdate) between pgf.effective_start_date and pgf.effective_end_date
AND   pgf.set_id=fssv.set_id
and prvf.rate_id = pr.rate_id
and trunc(sysdate) between pr.effective_start_date and pr.effective_end_date
and hikm.surrogate_id = prvf.rate_value_id

) ORDER BY DATA_ROW

Sample HDL:

METADATA|GradeRateValue|SourceSystemId|SourceSystemOwner|EffectiveStartDate|EffectiveEndDate|RateId|GradeCode|SetCode|LegislativeDataGroup|MinimumAmount|MaximumAmount|MidValueAmount|ValueAmount

MERGE|GradeRateValue|300000107518119|FUSION|1951/01/01|4712/12/31|300000106295381|ADMIN05|COMMON|KZ Legislative Data Group|870|962|913.5|

Mandeep Gupta 0 Comments

Reports

Feb 25 2022

BIP – Query to extract Person Address Details

select

distinct

PAPF.PERSON_NUMBER,

PAPF.PERSON_ID,

PPAUF.PERSON_ADDR_USAGE_ID "ADDRESS_ID",

TO_CHAR(PA.EFFECTIVE_START_DATE,'YYYY/MM/DD')"EFFECTIVE_START_DATE",

TO_CHAR(PA.EFFECTIVE_END_DATE,'YYYY/MM/DD')"EFFECTIVE_END_DATE",

PPAUF.ADDRESS_TYPE,

PA.ADDRESS_LINE_1,

PA.ADDRESS_LINE_2,

PA.ADDRESS_LINE_3

from

PER_ADDRESSES_F PA,

PER_ALL_PEOPLE_F PAPF,

PER_PERSON_ADDR_USAGES_F PPAUF

where

  PAPF.PERSON_ID = PPAUF.PERSON_ID(+)

AND PPAUF.ADDRESS_ID = PA.ADDRESS_ID(+)

Mandeep Gupta 0 Comments

HDL Reports

Jan 25 2022

HDL – Sample file to update user name to email address

SELECT 'METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner' Headerrow, 1 dataorder
FROM DUAL
UNION
SELECT 'METADATA|User'
      ||'|'||papf.person_number 
      ||'|'||pea.email_address
      ||'|'||hikm.source_system_id
      ||'|'||hikm.source_system_owner Headerrow, 2 dataorder
  FROM per_all_people_f papf
      ,per_email_addresses pea
	  ,per_users pu
	  ,hrc_integration_key_map hikm
 WHERE papf.person_id = pea.person_id
   and pu.person_id = pea.person_id
   and pu.user_id = hikm.surrogate_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
order by dataorder

Sample File:

METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner
MERGE|User|11101|[email protected]|300000120863888|FUSION

Mandeep Gupta 1 Comments

HDL Reports

Dec 28 2021

BIP – Extract Absence Entry data in HDL Format

The below query extracts the minimum required attributes for absence entry update. In the current example, the absence status is updated to Withdrawn. You can make the changes as per your need:

SELECT DATA_ROW
FROM (
SELECT 'METADATA'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || 'PerAbsenceEntryId'
|| CHR (124) || 'AbsenceStatus'
|| CHR (124) || 'AbsenceTypeId'
|| CHR (124) || 'AssignmentId'
|| CHR (124) || 'EmployerId'
|| CHR (124) || 'PersonId'
|| CHR (124) || 'StartDate'
|| CHR (124) || 'EndDate'
|| CHR (124) || 'SourceSystemId' 
|| CHR (124) || 'SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select  'MERGE'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || apae.per_absence_entry_id
|| CHR (124) || 'ORA_WITHDRAWN'
|| CHR (124) || apae.absence_type_id
|| CHR (124) || apae.assignment_id
|| CHR (124) || apae.legal_entity_id
|| CHR (124) || apae.person_id
|| CHR (124) || to_char(apae.start_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.end_date,'RRRR/MM/DD')
|| CHR (124) || hikm.source_system_id
|| CHR (124) || hikm.source_system_owner
FROM ANC_PER_ABS_ENTRIES apae,
	 HRC_INTEGRATION_KEY_MAP hikm
WHERE hikm.surrogate_id = apae.per_absence_entry_id
  --AND apae.absence_type_id = 3000012211
   AND apae.per_absence_entry_id = 300000079292534
)

Sample HDL File:

METADATA|PersonAbsenceEntry|PerAbsenceEntryId|AbsenceStatus|AbsenceTypeId|AssignmentId|EmployerId|PersonId|StartDate|EndDate|SourceSystemId|SourceSystemOwner
MERGE|PersonAbsenceEntry|300000079292534|ORA_WITHDRAWN|300000076789541||300000048608295|300000049253269|2014/06/11|2014/06/13|300000079292534|FUSION

Mandeep Gupta 0 Comments

Reports

Dec 20 2021

BIP – Query to extract child positions for a logged in person’s position

Fusion HCM provides you the capability of using Position Trees as well as Position Hierarchies. In case you need to extract child position details based on position id of logged in person, you can make use of below query:

SELECT pphf.position_id
  FROM per_position_hierarchy_f pphf
 WHERE 1=1
  START WITH pphf.parent_position_id = NVL((select position_id from per_all_assignments_m paam
                                         where paam.assignment_type = 'E'
					   and paam.person_id = hrc_session_util.get_user_personid
					   and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
					   and paam.effective_latest_change = 'Y'
					   and paam.effective_sequence = 1),300000141634831)
CONNECT BY PRIOR pphf.parent_position_id = 	pphf.position_id

Mandeep Gupta 0 Comments

Reports

Dec 16 2021

BIP – Extract Length of service

Use the below query to extract the length of service of a worker. In case of rehires this becomes little tricky. Below query makes use of earliest hire date for length of service calculation. If Seniority dates are implemented, then you can avoid using below query:

select papf.person_number, papf.person_id, trunc(months_between(trunc(sysdate),MIN(ppos.date_start))/12,2) LOS
  from per_periods_of_Service ppos
      ,per_all_people_f papf
 where ppos.person_id = papf.person_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
group by papf.person_number, papf.person_id

Mandeep Gupta 0 Comments

Reports

Dec 16 2021

BIP – Query to extract department Tree

Use the following query to extract department tree and its child departments. Please pass correct department name before executing:

WITH org_tree
AS (
        SELECT /*+ materialize */
               DISTINCT *
        FROM (
               SELECT  (
                               SELECT haoufv_p.name
                                 FROM hr_all_organization_units_f_vl haoufv_p
                                WHERE haoufv_p.organization_id = potnv.parent_organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_p.effective_start_date AND haoufv_p.effective_end_date
                        ) parent_org_name
                      ,(
                               SELECT haoufv_c.name
                                 FROM hr_all_organization_units_f_vl haoufv_c
                                WHERE haoufv_c.organization_id = potnv.organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_c.effective_start_date AND haoufv_c.effective_end_date
                        ) child_org_name
                       ,potnv.tree_structure_code
                       ,potnv.parent_organization_id parent_org_id
                       ,potnv.organization_id child_org_id
                       ,LEVEL levelcount
               FROM per_dept_tree_node_v potnv
			       ,fnd_tree_version ftv
              WHERE potnv.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
			    AND potnv.tree_code = 'Global100'
				AND potnv.tree_version_id = ftv.tree_version_id
				AND ftv.tree_code = potnv.tree_code
				AND ftv.status = 'ACTIVE' 
				AND TRUNC(SYSDATE) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
			   START WITH potnv.parent_organization_id IS NULL 
			 CONNECT BY PRIOR potnv.organization_id = potnv.parent_organization_id
               )
        ORDER BY levelcount ASC
        )
,dept_tree
 AS (
        SELECT /*+ materialize */
              level1.child_org_name "level1"
             ,level2.child_org_name "level2"
             ,level3.child_org_name "level3"
             ,level4.child_org_name "level4"
         FROM org_tree level1
		     ,org_tree level2
		     ,org_tree level3
		     ,org_tree level4
			 ,hr_all_organization_units_f haouf
       WHERE level1.child_org_id = level2.parent_org_id
	     AND level2.child_org_id = level3.parent_org_id
	     AND level3.child_org_id = level4.parent_org_id
         AND level1.parent_org_name IS NULL
		 AND haouf.organization_id = level4.child_org_id
		 AND TRUNC(SYSDATE) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
        )
SELECT *
FROM dept_tree

Mandeep Gupta 0 Comments

Reports

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

Reports

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

Reports

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

Reports

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

Reports

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

Reports

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

Reports

Nov 5 2021

BIP – Query to get fast formula details

select fff.formula_id
      ,fff.formula_type_id
      ,fff.formula_name
      ,fff.creation_date
	  ,fftt.FORMULA_TYPE_NAME
  from ff_formulas_f fff
      ,ff_formula_types_tl fftt
 where fff.FORMULA_TYPE_ID = fftt.FORMULA_TYPE_ID
   and fftt.language = 'US' 
order by creation_date desc

Mandeep Gupta 0 Comments

Reports

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

Reports

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

Reports

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

MSPL

MSPL

BIP – Query to extract Rating Model data in HDL Format

BIP – Query to check if employee bank account is active or not

HDL – Sample HDL to Update User Table value

BIP – HDL to update Grade Rate values

BIP – Query to extract Person Address Details

HDL – Sample file to update user name to email address

BIP – Extract Absence Entry data in HDL Format

BIP – Query to extract child positions for a logged in person’s position

BIP – Extract Length of service

BIP – Query to extract department Tree

BIP – Query to extract questions related to Job Requisition

BIP – SQL to extract Job Valid grades

BIP – Query to extract count of persons without Primary email address

BIP – Query to extract Course details with offering and activitiy details

BIP – Query to extract element eligibility details

BIP – Extract Termiation approval details

BIP – Query to get fast formula details

BIP – Query to find Workers with missing Payrolls

BIP – Report to get basic employee details

BIP – Query to Extract Learner Completion Details

MSPL

MSPL

Category Archives: Reports