Tag: BIP - Page 7

BIP, Element Entry, Fusion Report, HDL, Report

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

ByMandeep Gupta

BIP, HRT Profiles, Position Profile

BIP – Query to get position profile details

select hapf.position_code
,hpb.profile_code
,hpt.description profile_desc
,hpt.summary
,hpeiv.DESCRIPTION
,hpeiv.RESPONSIBILITIES
,hpeiv.QUALIFICATIONS
from HRT_PROFILE_ITEMS hpi
,HRT_PROFILES_B hpb
,HRT_PROFILES_TL hpt
,HRT_PROFILE_RELATIONS hpr
,HRT_PROFILE_EXTRA_INFO_VL hpeiv
,HR_ALL_POSITIONS_F hapf
where hpi.profile_id = hpb.profile_id
and hpb.profile_usage_code = 'M'
and hpi.profile_id = hpr.profile_id
and hpi.profile_id = hpt.profile_id
and hpi.profile_id = hpeiv.profile_id
and hapf.position_id = hpr.object_id
and trunc(sysdate) between hapf.effective_start_date and hapf.effective_end_Date
and hapf.position_code= '1099'
and hpt.language = 'US'

Query to extract only the profile data:

select hpb.profile_code
,hpt.description
,hpt.summary
,hpeiv.DESCRIPTION desc1
,hpeiv.RESPONSIBILITIES
,hpeiv.QUALIFICATIONS
,hikm.source_system_id
,hikm.source_system_owner
from HRT_PROFILES_B hpb
,HRT_PROFILES_TL hpt
,HRT_PROFILE_EXTRA_INFO_VL hpeiv
,HRC_INTEGRATION_KEY_MAP hikm
where hpeiv.profile_id = hpb.profile_id
and hpb.profile_usage_code = 'M'
and hpt.language = 'US'
and hpb.profile_code like '%TEST%'
and hikm.surrogate_id = hpeiv.PROFILE_EXTRA_INFO_ID
order by hpb.creation_date desc

ByMandeep Gupta

BIP, OLC, Renewal Options, reports

BIP – Query to get Default Assignment Rules of the Course

Default Assignment Rules like Validity Period, Expiration, Renewal Options, Renewal Period etc are maintained at the course level in Oracle Learning Cloud.

These details are stored in the backend table ‘WLF_ASSIGNMENT_RULES’.

The query from below post can be joined with WLF_ASSIGNMENT_RULES using ASSIGNMENT_RULE_ID column:

https://fusionhcmconsulting.com/2021/03/reports-bip-query-to-get-assignee-details-for-a-course/?amp

and warf.ASSIGNMENT_RULE_ID = war.ASSIGNMENT_RULE_ID

And you will get the required output.

ByMandeep Gupta

BIP, Common Lookups, Module Key, Module Type, Value Set

Reports (BIP) – Query to find module type and key

While defining Common Lookups or value sets, you need to provide module value. Each module has an associated module type, module key and product code associated with it. For example:

These details are stored in backed in a table – FND_APPL_TAXONOMY.

Use the below query to find module type, module key etc for a module:

select fat.MODULE_NAME
,fat.MODULE_TYPE
,fat.MODULE_KEY
,fat.PRODUCT_CODE
from FND_APPL_TAXONOMY fat

ByMandeep Gupta

BIP, Data Masking, Salary

Data Masking – Mask Salary Data in lower environment

There is a common requirement to mask salary data post P2T refreshes. This should be done in order to hide the actual salaries information as salary is a very sensitive information.

Use the below query to generate data in HDL format in a test environment immediately after P2T refresh. The below query generate a random salary amount. Save the downloaded data in .dat file format and upload it back to the instance.

Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId' Header, 1 data_flow_order
from dual
UNION
SELECT 'MERGE|Salary'||'|'||
paam.assignment_number||'|'||
round(DBMS_RANDOM.VALUE (1,15000) , 2)||'|'||
TO_CHAR(cs.date_from,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
TO_CHAR(cs.date_to,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
cs.salary_basis_id||'|'||
cs.salary_id data_row,
2 data_flow_order
FROM cmp_salary cs,
per_all_assignments_m paam
WHERE cs.assignment_id= paam.assignment_id
AND trunc(sysdate) between paam.effective_start_date AND paam.effective_end_date
AND paam.assignment_type in ('E', 'C', 'P')
AND paam.assignment_number ='E788880'
ORDER BY data_flow_order

ByMandeep Gupta

BIP, Seniority Date, Setup

Reports (BIP) – Query to extract seniority dates setup

select hr_general.decode_lookup('ORA_PER_SENIORITY_ITEMS',psds.seniority_date_code) seniority_date_code_meaning
,hr_general.decode_lookup('ORA_PER_SENIORITY_LEVELS',psds.level_code) level_code_meaning
,hr_general.decode_lookup('ORA_PER_TRIG_FIELDS',psds.triggering_field) triggering_field_meaning
,psds.active_flag
,psds.allow_edit_flag
,psds.display_in_ui
,psds.cumulative_flag
,psds.hours_in_year
,psds.hours_in_month
,psds.hours_in_day
,psds.object
,psds.source_field
,psds.seniority_version
,psds.record_creator
,psds.setup_seniority_basis
from per_seniority_dates_setup psds

ByMandeep Gupta

BIP, Role, User, User role

Reports (BIP) – User Role Details

SELECT distinct pu.username
,prd.role_common_name
,prd.abstract_role
,prd.job_role
,prd.data_role
,prd.delegation_allowed
,prd.active_flag
,prdt.role_name
,prdt.description
FROM per_users pu
,per_roles_dn prd
,per_user_roles pur
,per_roles_dn_tl prdt
WHERE pu.user_id = pur.user_id
and pur.role_id = prdt.role_id
and pur.role_id = prd.role_id
and prdt.language = 'US'

ByMandeep Gupta

BIP, Org Manager, reports

Reports (BIP) – Query to get Department Manager Name

SELECT distinct houft.name, 
       ppnfv.full_name
FROM HR_ORGANIZATION_INFORMATION_F hoif,
     per_person_names_f_v ppnfv,
     hr_organization_units_f_tl houft
WHERE trunc(sysdate) BETWEEN hoif.EFFECTIVE_START_DATE AND hoif.EFFECTIVE_END_DATE
AND trunc(sysdate) BETWEEN ppnfv.effective_Start_date AND ppnfv.effective_end_date
AND ppnfv.person_id=hoif.ORG_INFORMATION2
AND houft.organization_id = hoif.organization_id
AND hoif.ORG_INFORMATION_CONTEXT = 'PER_ORG_MANAGER_INFO'
AND houft.language = 'US'
AND TRUNC(sysdate) BETWEEN houft.EFFECTIVE_START_DATE AND houft.EFFECTIVE_END_DATE

ByMandeep Gupta

BIP, Contact, Report

BIP (Reports) – Query to check if the contact is an employee

select CASE WHEN COUNT(ppos.period_of_service_id) = 0
            THEN 'N'
            ELSE 'Y'
        END working_as_employee
from per_periods_of_service ppos
where ppos.person_id = pcr.CONTACT_PERSON_ID -- person_id of contact
and NVL(ppos.actual_termination_date, TRUNC(sysdate)) >= TRUNC(sysdate)

ByMandeep Gupta

Assignee, BIP, Course, Learner, Learning Cloud, Offering, OLC

Reports (BIP) – Query to get Assignee details for a course

SELECT warf.ASSIGNMENT_RECORD_ID,
warf.EVENT_TYPE,
we.EVENT_TYPE we_event_type,
warf.EVENT_SUB_TYPE,
we.EVENT_SUB_TYPE we_EVENT_SUB_TYPE,
(SELECT person_number
FROM per_all_people_f
WHERE person_id = warf.EVENT_CREATED_BY_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) created_By,
we.CREATED_BY_ID "Assigned_By_Person_Id",
(SELECT person_number
FROM per_all_people_f
WHERE person_id = we.CREATED_BY_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) "Assigned_By",
warf.ATTRIBUTION_TYPE,
we.ATTRIBUTION_TYPE we_ATTRIBUTION_TYPE,
(SELECT person_number
FROM per_all_people_f
WHERE person_id = warf.ATTRIBUTION_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date) attribution_id,
warf.ATTRIBUTION_ID warf_ATTRIBUTION_ID,
we.ATTRIBUTION_ID we_ATTRIBUTION_ID,
warf.learner_id,
warf.ATTRIBUTION_LOOKUP_CODE,
warf.EVENT_ASSIGNMENT_ID
from WLF_ASSIGNMENT_RECORDS_F warf,
WLF_EVENT_ASSIGNMENTS_F weaf,
WLF_EVENTS we
where warf.EVENT_ASSIGNMENT_ID = weaf.EVENT_ASSIGNMENT_ID
and trunc(sysdate) between warf.effective_start_date and warf.effective_end_date
and trunc(sysdate) between weaf.effective_start_date and weaf.effective_end_date
and we.event_id = weaf.event_id

ByMandeep Gupta

BIP, Course, Default Access, HDL, OLC, Permissions

Reports (BIP) – Query to extract default Access Group data for a Course/Offering

In Oracle Learning cloud, Learning Admin can define the Default access at Course or offering level. Course.dat can be used to load this information.

In order to extract this information from DB in HDL format, please use below query:

SELECT 'MERGE' 	                                           "METADATA"       
      ,'CourseDefaultAccess' 		                   "CourseDefaultAccess"                  
      ,to_char(wapf.effective_start_date,'RRRR/MM/DD')     "EffectiveStartDate"           
      ,to_char(wapf.effective_end_date,'RRRR/MM/DD')       "EffectiveEndDate"       
      ,wapf.access_permission_number                       "DefaultAccessNumber"       
      ,wlif.learning_item_number                           "CourseNumber"       
      ,wapf.follow_spec 	                           "FollowSpecialization"       
      ,wapf.assign_as_manager                		   "MgrInitialStatus"       
      ,wapf.assign_for_self                                "SelfInitialStatus"        
      ,wlif.ss_view_mode                                   "SelfViewMode"    
 FROM WLF_ACCESS_PERMISSIONS_F wapf      
     ,WLF_LEARNING_ITEMS_F wlif 
WHERE 1=1   
  AND wapf.access_permission_id= wlif.access_permission_id   
--AND trunc(sysdate) between wapf.effective_start_date and wapf.effective_end_date   
  AND trunc(sysdate) between wlif.effective_start_date and wlif.effective_end_date   
  AND wlif.learning_item_number = 'OLC251051'

Also, learners can be assigned using Access Groups.

To extract this information below SQL can be used:

SELECT wlif.learning_item_id
      ,wlif.learning_item_number course_Number
      ,wlif_tl.name course_Name
      ,wlif_tl.description_short description
      ,wlif_ag.learning_item_number access_group_number
      ,wlif_tl_ag.name access_group_name
  FROM wlf_learning_items_f 	wlif
      ,wlf_learning_items_f_tl 	wlif_tl
      ,wlf_learning_items_f 	wlif_ag
      ,wlf_learning_items_f_tl 	wlif_tl_ag
      ,wlf_li_ag_relations_f 	wlarf
 WHERE 1=1
   AND wlif_tl.learning_item_id 		= wlif.learning_item_id
   AND wlif_tl.name 				= 'Test 1234'
   AND wlif_ag.learning_item_type 		= 'ORA_ACCESS_GROUP'
   AND wlif_tl_ag.learning_item_id 		= wlif_ag.learning_item_id
   AND wlarf.access_learning_item_id 	= wlif_ag.learning_item_id
   AND wlarf.catalog_learning_item_id 	= wlif.learning_item_id   
   AND TRUNC(SYSDATE) BETWEEN wlif_tl_ag.effective_start_date AND wlif_tl_ag.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN wlif_ag.effective_start_date AND wlif_ag.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN wlif_tl.effective_start_date AND wlif_tl.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN wlif.effective_start_date AND wlif.effective_end_date

ByMandeep Gupta

activity time zone, BIP, Timezone

Reports (BIP) – Query to get correct time zone

When an activity in learning is created with a start time and end time, the time is stored in UTC format in the data base. When any reporting is done on this data, the output is incorrect as time is shown in UTC format which is different from user time format.

This is applicable for all transactions. Please use the below workaorund to get the correct date time in the SQL:

select wlif.start_date
      ,wlaf.time_zone
      ,TZ_OFFSET(wlaf.time_zone) time_zone_offset
      ,(FROM_TZ(CAST(wlif.start_date AS TIMESTAMP),(case when TZ_OFFSET(wlaf.time_zone)     like '+%'
then '-'||substr(TZ_OFFSET(wlaf.time_zone),2)
else '+'||substr(TZ_OFFSET(wlaf.time_zone),2)
end
))) actual_start_date
      ,TO_CHAR(CAST(wlif.start_date AT TIME ZONE wlaf.time_zone AS DATE),'HH:Mi AM','nls_date_language=American') actual_start_time
 from wlf_learning_items_f wlif
    , wlf_li_activities_f wlaf
where learning_item_number= 'OLC245017'
  and wlif.learning_item_id = wlaf.learning_item_id
  and trunc(sysdate) between wlif.effective_start_date and wlif.effective_end_date
  and trunc(sysdate) between wlaf.effective_start_date and wlaf.effective_end_date

ByMandeep Gupta

Assignment Grade Steps, BIP, HDL, Report

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

ByMandeep Gupta

BIP, Fusion, Name Styles, reports

Reports (BIP) – Query to extract Manage Person Name Styles

Manage Person Name Styles is a task that can be used to configure additional Name attributes for a country if required.

Also, a name component can be made required as per requirement.

In multi country implementations, it is required to know the setup for technical developers as it is hard to check the setup for each country from the UI. In such cases, below SQL can be used to extract the information from backend tables:

SELECT pnsv.legislation_code
      ,pensv.display_sequence
      ,pensv.column_name
      ,pensv.prompt
      ,pensv.required_flag
  FROM PER_EDIT_NAME_SETUP_VL pensv,
       PER_NAME_STYLES_VL pnsv
WHERE pensv.name_style_id = pnsv.name_style_id
ORDER BY 1,2

ByMandeep Gupta

BIP, Checklist, Checklist e sign, Configurable attributes

Reports (BIP) – Query to get checklist configurable attributes

SELECT DISTINCT PAT.PERFORMER_ORIG_SYS_ID PERSON_ID
,PAT.ALLOCATED_CHECKLIST_ID AS P_ALLOCATED_CHECKLIST_ID
,PAT.ATTRIBUTE1
,PAT.ATTRIBUTE3
,PAT.ATTRIBUTE2
,PAT.ATTRIBUTE4
,PAT.ATTRIBUTE5
,PAT.ATTRIBUTE6
,PAT.ATTRIBUTE7
,PAT.ATTRIBUTE8
,PAT.ATTRIBUTE9
,PAT.ATTRIBUTE12
,PAT.ATTRIBUTE13
,PAT.ATTRIBUTE10
,PAT.ATTRIBUTE11
,PAT.ATTRIBUTE14
,PAT.ATTRIBUTE15
,PAT.FLEX_CONTEXT_CODE
FROM PER_ALLOCATED_TASKS_VL PAT
WHERE PAT.FLEX_CONTEXT_CODE IN ('XYZ Details')
AND PAT.STATUS='COM'
AND PAT.ALLOCATED_CHECKLIST_ID = NVL(:P_ALLOCATED_CHECKLIST_ID,PAT.ALLOCATED_CHECKLIST_ID)

ByMandeep Gupta

BIP, Performance Rating

Reports (BIP) – Query to get Performance Rating Details

SELECT DISTINCT PAPF.PERSON_NUMBER,
TO_CHAR(HPI.DATE_FROM,'DD-MM-YYYY') DATE_FROM,
To_CHAR( HPI.DATE_TO,'DD-MM-YYYY') DATE_TO,
HRL.RATING_DESCRIPTION
FROM HRT_PROFILES_B HPB
INNER JOIN PER_ALL_PEOPLE_F PAPF ON HPB.PERSON_ID = PAPF.PERSON_ID
INNER JOIN HRT_PROFILE_ITEMS HPI ON HPI.PROFILE_ID = HPB.PROFILE_ID
INNER JOIN HRT_RATING_LEVELS_TL HRL ON HRL.RATING_LEVEL_ID = HPI.RATING_LEVEL_ID1
INNER JOIN HRT_CONTENT_TYPES_B HCT ON HPI.CONTENT_TYPE_ID = HCT.CONTENT_TYPE_ID
WHERE HPB.PROFILE_USAGE_CODE = 'P'
AND HCT.CONTEXT_NAME = 'PERFORMANCE_RATING'
AND HRL.LANGUAGE = 'US'
AND TRUNC(HPI.DATE_FROM) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
ORDER BY PAPF.PERSON_NUMBER,DATE_FROM

ByMandeep Gupta

Absence, Absence Ajustment, BIP

Reports (BIP) – Query to get Adjustments in Absences

select papf.person_number,
ppnf.full_name,
absence_plan.name ,
apaed1.value,
apaed1.type,
to_char(apaed1.procd_date,’DD-MON-YYYY’) processed_date,
apaed1.created_by
from anc_per_acrl_entry_dtls apaed1,
ANC_PER_PLAN_ENROLLMENT apaed,
per_all_people_f papf,
per_person_names_f ppnf,
(SELECT aapf.absence_plan_id, aapft.NAME
FROM anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf
WHERE aapft.absence_plan_id = aapf.absence_plan_id
AND aapf.plan_status = ‘A’ — added to pick only Active Absence Plans
AND trunc(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
AND trunc(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
AND aapft.language = ‘US’
) absence_plan
where apaed.plan_id = absence_plan.absence_plan_id
and apaed.plan_id = apaed1.pl_id
AND ppnf.name_type = ‘GLOBAL’
AND ppnf.person_id = apaed.person_id
AND ppnf.person_id = apaed1.person_id
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date and ppnf.effective_end_date
AND apaed.person_id = papf.person_id
AND apaed1.person_id = papf.person_id
AND TRUNC(sysdate) BETWEEN papf.effective_start_date and papf.effective_end_date
–AND UPPER(absence_plan.name) NOT LIkE ‘%SICK%’
AND apaed1.type = ‘ADJOTH’
–and apaed1.created_by not like ‘FUSION_APPS_HCM_ESS_LOADER_APPID’
ORDER BY papf.person_number,absence_plan.name

ByMandeep Gupta

BIP, Fusion HCM, Weekly Scheudle

Reports (BIP) – Function to get weekly schedule details

Use below query to get weekly schedule details of a worker:

select PER_AVAILABILITY_DETAILS.GET_WEEKLY_SCH_DETAILS (ASSIGNMENT_ID, TRUNC(SYSDATE,'D') + 1) Weekly_Schedule
from per_All_assignments_m
where assignment_type = 'E'

ByMandeep Gupta

BIP, Disability, Fusion BIP, Person Disability, reports

Reports (BIP) – Query to extract Worker disability details

Below query can be used to extract disability details of a worker:

SELECT papf.person_id
,papf.person_number
,ppnf.full_name
,to_char(pdf.effective_start_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_eff_st_dt
,to_char(pdf.effective_end_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_eff_end_dt
,pdf.legislation_code
,pdf.disability_code
,(select pou.name from per_organization_units pou
where pou.organization_id = pdf.organization_id) disability_org_name
,to_char(pdf.registration_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_registration_date
,to_char(pdf.registration_exp_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_registration_exp_date
,to_char(pdf.assessment_due_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_assessment_due_date
,pdf.category
,pdf.description
,pdf.degree
,pdf.quota_fte
,pdf.reason
FROM PER_DISABILITIES_F pdf
,PER_ALL_PEOPLE_F papf
,PER_PERSON_NAMES_F ppnf
WHERE papf.person_id = pdf.person_id
AND ppnf.person_id = papf.person_id
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

ByMandeep Gupta

Assigned Payroll, BIP, Fusion Reports, Payroll relationship, reports

Reports (BIP) – Query to get assigned payroll details

Use the below query to extract assigned payroll details from Fusion HCM:

SELECT papf.person_id
	  ,papf.person_number
	  ,ppnf.full_name
	  ,papd.payroll_id
	  ,papf_pay.payroll_name
	  ,prrd.payroll_relationship_number
	  ,to_char(prrd.start_date,'DD-Mon-RRRR', 'nls_date_language=American') payroll_rel_start_date
	  ,to_char(prrd.end_date,'DD-Mon-RRRR', 'nls_date_language=American') payroll_rel_end_date
	  ,to_char(papd.FSED,'DD-Mon-RRRR', 'nls_date_language=American') fsed
	  ,to_char(papd.FINC,'DD-Mon-RRRR', 'nls_date_language=American') finc
	  ,to_char(papd.LSPD,'DD-Mon-RRRR', 'nls_date_language=American') lspd
	  ,to_char(papd.LSED,'DD-Mon-RRRR', 'nls_date_language=American') lsed
	  ,paam.assignment_number
	  ,prgf.time_card_req
  FROM pay_assigned_payrolls_dn papd
	  ,pay_payroll_terms ppt
	  ,pay_pay_relationships_dn prrd
	  ,pay_all_payrolls_f papf_pay
	  ,pay_rel_groups_dn prgd
	  ,pay_rel_groups_f prgf
	  ,per_all_people_f papf
	  ,per_person_names_f ppnf
	  ,per_all_assignments_m paam 
 WHERE papd.payroll_term_id = ppt.payroll_term_id
   AND ppt.payroll_relationship_id = prrd.payroll_relationship_id
   AND papd.payroll_id = papf_pay.payroll_id
   AND prrd.person_id = papf.person_id
   AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
   AND prgd.relationship_group_id = prgf.relationship_group_id
   AND prgd.assignment_id = paam.assignment_id
   AND paam.effective_latest_change = 'Y'
   AND prgd.group_type = 'A'
   AND ppnf.person_id = papf.person_id
   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
   AND TRUNC(SYSDATE) BETWEEN prgf.effective_start_date AND prgf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND papf.person_number = '11111'
 ORDER BY 2,3

Tag Archive BIP

BIP – Query to get element entry details

BIP – Query to get position profile details

BIP – Query to get Default Assignment Rules of the Course

Reports (BIP) – Query to find module type and key

Data Masking – Mask Salary Data in lower environment

Reports (BIP) – Query to extract seniority dates setup

Reports (BIP) – User Role Details

Reports (BIP) – Query to get Department Manager Name

BIP (Reports) – Query to check if the contact is an employee

Reports (BIP) – Query to get Assignee details for a course

Reports (BIP) – Query to extract default Access Group data for a Course/Offering

Reports (BIP) – Query to get correct time zone

HDL – Query to get Assignment Grade Steps

Reports (BIP) – Query to extract Manage Person Name Styles

Reports (BIP) – Query to get checklist configurable attributes

Reports (BIP) – Query to get Performance Rating Details

Reports (BIP) – Query to get Adjustments in Absences

Reports (BIP) – Function to get weekly schedule details

Reports (BIP) – Query to extract Worker disability details

Reports (BIP) – Query to get assigned payroll details

Latest Posts

Categories

Recent Comments

Archives