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_ACCOUNTSBIP – 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_ROWSample 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|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(+) 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|FUSIONBIP – 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 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_treeBIP – 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'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/01Sometime 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
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%'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,2BIP – Query to extract data from Pay Inbound Records
Third party payslip data can be loaded into Payroll Inbound Records in Fusion HCM. Use below query to extract the data:
select distinct hpir.inbd_record_id
,to_char(hpir.start_date,'RRRR/MM/DD', 'nls_date_language=American') startDate
,hpir.entity_identifier employeeNumber
,hpir.payroll_id
,hpir.time_period_id
,hpir.person_id
,(select pldft.LOCATION_NAME
from per_location_details_f pldf,PER_LOCATION_DETAILS_F_TL pldft
where pldf.location_id = paam.location_id
and pldft.location_details_id = pldf.location_details_id
and trunc(SYSDATE) BETWEEN pldf.effective_start_date AND pldf.effective_end_date
and trunc(SYSDATE) BETWEEN pldft.effective_start_date AND pldft.effective_end_date) location_name
,(select hauft.NAME
from HR_ORG_UNIT_CLASSIFICATIONS_F houcf,
HR_ALL_ORGANIZATION_UNITS_F haouf,
HR_ORGANIZATION_UNITS_F_TL hauft
where hauft.organization_id = paam.legal_entity_id
AND haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID
AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID
AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE
AND hauft.LANGUAGE = 'US'
AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE
AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE
AND houcf.CLASSIFICATION_CODE = 'HCM_LEMP'
AND trunc(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date) le
,(select TO_CHAR(ptp.end_date,'DD-MON-RRRR', 'nls_date_language=American') from pay_time_periods ptp
where ptp.time_period_id = hpir.time_period_id) periodEndDate
,ppnf.first_name||' '||ppnf.last_name employeeName
,(select meaning from fnd_lookup_values
where lookup_type = 'EMP_CAT'
and lookup_code = paam.employment_category) catgr
,pp.attribute1
,pp.attribute2
,pni.national_identifier_number
from hry_pi_inbd_records hpir,
per_all_assignments_m paam,
per_person_names_f ppnf,
per_persons pp,
per_national_identifiers pni
where hpir.person_id = paam.person_id
and hpir.person_id = ppnf.person_id
and hpir.person_id = pp.person_id
and hpir.person_id = pni.person_id
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 assignment_type not like 'ET%'
and paam.ACTION_CODE <> 'TERMINATION'
/* and hpir.person_id = (select pu.person_id
from per_users pu
where pu.user_guid=fnd_global.user_guid)*/
BIP – Extract Salary Basis Details
SELECT csb.name
,csb.salary_basis_code
,csb.salary_annualization_factor
,pldgt.name legislative_dg
,petft.element_name
,pivt.name input_val_name
,prft.name rate_name
FROM cmp_salary_bases csb
,per_legislative_data_groups_tl pldgt
,pay_element_types_tl petft
,pay_input_values_f pivf
,pay_input_values_tl pivt
,per_rates_f_tl prft
WHERE csb.legislative_data_group_id = pldgt.legislative_data_group_id
AND pldgt.language = USERENV('LANG')
AND csb.element_type_id = petft.element_type_id
AND petft.language = USERENV('LANG')
AND csb.element_type_id = pivf.element_type_id
AND csb.input_value_id = pivf.input_value_id
AND trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date
AND pivf.input_value_id = pivt.input_value_id
AND pivt.language = USERENV('LANG')
AND csb.grade_rate_id = prft.rate_id(+)
AND NVL(prft.language,USERENV('LANG')) = USERENV('LANG')BIP – Providing Link of BIP report on Self Service
There is one common requirement where Employees should access reports directly from self-service instead of navigating to analytics. In such cases, a report link can be created and added as a static link on Navigator. This will enable the employee to access report directly. The report can be then viewed in different views using different parameters in report link.
Steps to enable report link on self-service:
- Derive the report link. Navigate to analytics and open the report:
- https://xxxx-fa-ext.oracledemos.com/analytics/
Prepare the report link in below format:
https://xxxx-fa-ext.oracledemos.com/xmlpserver/Custom/HCM/INVOKE_BIP_RPT.xdo?_xpt=0&_xmode=4&_xf=pdf&_xautorun=true
Refer the below link for more details on link parameters
Xmode parameter is used for different views. Below are different views depending upon xmode value:
xmode=0
xmode=1
xmode=2
xmode=4
Passing Parameter in report link:
https://ucf6-zuyv-fa-ext.oracledemos.com/xmlpserver/Custom/HCM/INVOKE_BIP_RPT.xdo?_xpt=0&_xmode=4&p_emp_num=10&_xt=Simple&_xf=html
- Create a new Sandbox and add Structure under tools. Click on Create and Enter:
Click on Tools under sandbox and click on Create Page Entry:
Create a page entry with below details and click on Save and Close:
You can now see Report Link under Me:
Click on report link and it will open the report in a new tab:
If everything looks ok, you can publish the Sandbox.
BIP – My Public Info Extract
Each person can publish public info in HCM Cloud by navigating to Me -> Quick Actions -> Public Info. There are various sections under Public Info like Public Message, Contact Info, About Me etc.
You can use the below SQL queries to extract the data for relevant sections.
Public Message:
SQL Query:
SELECT papf.person_number
,ppnf.full_name
,ppmv.PORTRAIT_MESSAGE_CONTENT
,ppmv.TIME_FROM
,ppmv.TIME_TO
FROM per_all_people_f papf
,per_person_names_f ppnf
,per_portrait_messages_vl ppmv
WHERE papf.person_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND papf.person_id = ppmv.target_person_id
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_dateAbout Me:
SQL Query:
select papf.person_number
,ppnf.full_name
,hpv.summary about_me
,hpkaoe.keywords area_of_expertise
,regexp_replace(regexp_replace(regexp_replace(hpkaoe.KEYWORDS, '</p><br/>'), '</p>'),'<p>') as Area_Of_Expert
ise_wo_html
,hpkaoi.keywords area_of_interest
,regexp_replace(regexp_replace(regexp_replace(hpkaoi.KEYWORDS, '</p><br/>'), '</p>'),'<p>') as area_of_interest_wo_html
from PER_ALL_PEOPLE_F papf,
PER_PERSON_NAMES_F ppnf,
HRT_PROFILES_VL hpv,
HRT_PROFILE_KEYWORDS hpkaoe,
HRT_PROFILE_KEYWORDS hpkaoi
where hpv.profile_id = hpkaoe.profile_id
and hpkaoe.profile_id = hpkaoi.profile_id
and hpkaoe.keyword_type = 'AOE'
and hpkaoi. keyword_type = 'AOI'
and papf.person_id = hpv.person_id
and papf.person_id = ppnf.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_dateBIP – Query to Extract Position FTE values
When position hierarchy is enabled in Cloud HCM, Manage Positions UI shows some dynamically populated fields along with Parent position.
For example:
Use the below query to extract above details:
SELECT Positions.name "Position Name"
,Positions.FTE "Position Current FTE"
,Positions.INCUMBENT_FTE "Current Incumbent FTE"
,(Positions.FTE - Positions.INCUMBENT_FTE) "Difference FTE"
FROM
(SELECT HAPFT.NAME,
HAPF.FTE,
(select SUM(PAWMF.VALUE)
from PER_ALL_ASSIGNMENTS_M PAAM,
PER_ASSIGN_WORK_MEASURES_F PAWMF
where 1=1
AND PAAM.POSITION_ID = HAPF.POSITION_ID
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.ASSIGNMENT_ID = PAWMF.ASSIGNMENT_ID
AND PAWMF.UNIT = 'FTE') AS INCUMBENT_FTE
FROM HR_ALL_POSITIONS_F HAPF,
HR_ALL_POSITIONS_F_TL HAPFT
WHERE HAPF.POSITION_ID = HAPFT.POSITION_ID
AND USERENV('LANG') = HAPFT.LANGUAGE
AND TRUNC(SYSDATE) BETWEEN HAPF.EFFECTIVE_START_DATE AND HAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN HAPFT.EFFECTIVE_START_DATE AND HAPFT.EFFECTIVE_END_DATE
AND HAPFT.NAME IN('Test Position')
ORDER BY HAPFT.NAME ) Positions
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.
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_DATEReports (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,2Reports – Query to get business Unit details
Use below query to get business unit details:
SELECT fabuv.bu_name,
fabuv.short_code,
to_char(fabuv.date_from, 'yyyy/mm/dd') start_date,
to_char(fabuv.date_to, 'yyyy/mm/dd') end_date,
fabuv.enabled_for_hr_flag
FROM fun_all_business_units_v fabuv
WHERE TRUNC(SYSDATE) BETWEEN fabuv.date_from AND NVL(fabuv.date_to,TRUNC(SYSDATE))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