Search for:
Feb 19 2021
ORC – Query to get candidate application status
select distinct ic.candidate_number,
ppnfv.full_name,
irv.requisition_number,
ipv.name as phase,
isv.name as state
from irc_requisitions_vl irv,
irc_submissions isub,
irc_phases_vl ipv,
irc_states_vl isv,
per_person_names_f_v ppnfv,
irc_candidates ic
where 1=1
AND irv.REQUISITION_ID (+) = isub.REQUISITION_ID
AND ipv.PHASE_ID (+) = isub.CURRENT_PHASE_ID
AND isv.STATE_ID (+) = isub.CURRENT_STATE_ID
AND isub.PERSON_ID (+) = ic.PERSON_ID
AND ic.PERSON_ID = ppnfv.PERSON_ID
AND ppnfv.NAME_TYPE = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN ppnfv.EFFECTIVE_START_DATE AND ppnfv.EFFECTIVE_END_DATE
ORDER BY ic.candidate_number
Mandeep Gupta 0 Comments
Feb 14 2021
Reports (BIP) – Query to extract basic position details
SELECT
to_char(hapf.Effective_Start_Date,'YYYY/MM/DD') "Effective_Start_Date",
to_char(hapf.Effective_End_Date,'YYYY/MM/DD') "Effective_End_Date",
(SELECT DISTINCT BU_NAME from FUN_ALL_BUSINESS_UNITS_V fun where fun.BU_ID= BUSINESS_UNIT_ID) "BU",
(select DISTINCT hapft.NAME
from HR_ALL_POSITIONS_F_TL hapft
where hapft.position_id=hapf.position_id
AND trunc(sysdate) between trunc(hapft.effective_start_date) and trunc(hapft.effective_end_date)
AND hapft.language = USERENV('LANG')
) "Pos_Name",
hapf.POSITION_CODE "PosCode",
hapf.ACTIVE_STATUS "Active_Inactive",
(select DISTINCT horg.NAME from HR_ALL_ORGANIZATION_UNITS_F_VL horg where horg.ORGANIZATION_ID=hapf.ORGANIZATION_ID
AND trunc(sysdate) between trunc(horg.effective_start_date) and trunc(horg.effective_end_date)) "Dept",
(select pjft.name
from per_jobs_f_tl pjft
WHERE pjft.job_id = hapf.job_id
AND trunc(SYSDATE) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
AND language = USERENV('LANG')) "Job",
(select pj.job_code from per_jobs pj where pj.job_id=hapf.job_id
AND trunc(sysdate) between trunc(pj.effective_start_date) and trunc(pj.effective_end_date)) "JobCode",
(select hlaf.location_code from hr_locations_all_f_vl HLAF where HLAF.location_id=hapf.location_id
AND trunc(sysdate) between trunc(HLAF.effective_start_date) and trunc(HLAF.effective_end_date)
) "Location"
FROM HR_ALL_POSITIONS_F hapf
where TRUNC(SYSDATE) between trunc(hapf.effective_start_date) and trunc(hapf.effective_end_date)
Mandeep Gupta 0 Comments
Feb 13 2021
Configuration – Using User Interface Text

User Interface Text is a tool provided with Oracle Fusion Applications which can be used to replace any words/sentences across the application. For example, you want to rename Worker to Employee, then you can make use of this tool.

To make use of this tool, you have to first create a sandbox and then add User Interface Text tool in it. Then test your changes in sandbox mode. Once you are satisfied, you can publish your changes.

  • Navigate to User Name -> Settings and Actions -> Administration -> Edit Pages
  • Click on Activate Sandbox:
  • Click on ‘Create Sandbox’. Give a name and choose ‘User Interface Text’ tool:
  • Click on ‘Create and Enter’ button.
  • Click on User Interface Text:
  • In the Find text box give the string you want to search and in Replace text box give the text which you want to display and click on Search button:
  • The result will show all the places where it finds the string:
  • Check the other tabs as well for results to see the texts which will be replaced:
  • Click on ‘Replace Strings’ button if you want to proceed ahead with the changes or click on ‘New Search’ to start a new search:

Please note that initial search button will not get enabled unless you supply both (search and replace) values.

Mandeep Gupta 0 Comments
Feb 13 2021
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
Mandeep Gupta 0 Comments
Feb 11 2021
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)
Mandeep Gupta 0 Comments
Feb 11 2021
Approvals – Designing approval based on Probation Period

There is a common requirement for Resignation approval workflows where the approval is triggered based on a condition by checking whether the worker has completed the probation period or not. The probation end date attribute is not exposed in BPM. So it becomes little tricky to get the probation end date.

In such cases, days between function can be used to calculate the probation end date based on probation period. The pre-requisite is that the probation period for all workers should be entered with UOM as ‘Days’.

Duration.days between(Task.payload.Worker’s Current Assignment.result.Work Relationship Start Date.toGregorianCalendar(),CurrentDate.date) <= Task.payload.Worker’s Current Assignment.result.Probation Period 

Mandeep Gupta 0 Comments
Feb 9 2021
Reports (BIP) – Query to get Content Item Details

Use below sample queries to get details of loaded/created content items like – Languages, Degrees etc.

Query to get language details:

Select pr.person_id,
HCITL.NAME lang_name,
(select HRL.RATING_DESCRIPTION
from HRT_RATING_LEVELS_TL HRL
where HRL.RATING_LEVEL_ID = pi.RATING_LEVEL_ID1
and HRL.LANGUAGE = 'US') read_a,
(select HRL.RATING_DESCRIPTION
from HRT_RATING_LEVELS_TL HRL
where HRL.RATING_LEVEL_ID = pi.RATING_LEVEL_ID2
and HRL.LANGUAGE = 'US') write_a,
(select HRL.RATING_DESCRIPTION
from HRT_RATING_LEVELS_TL HRL
where HRL.RATING_LEVEL_ID = pi.RATING_LEVEL_ID3
and HRL.LANGUAGE = 'US') speak_a
from hrt_profiles_vl pr
,hrt_profile_items pi
,hrt_content_types_vl ct
,hrt_content_items_tl hcitl
where pi.profile_id=pr.profile_id
and pi.content_type_id=ct.content_type_id
and ct.content_type_name = 'Languages'
and hcitl.content_item_id = pi.content_item_id

Query to get Degree Details:

select hpb.person_id person_id,
hcitl.name degree,
item_text240_1 major,
est.name school,
item_decimal_1 gpa,
item_date_4 completion_date
from hrt_profiles_b hpb,hrt_profile_items hpi ,hrt_content_types_b hct,hrt_content_items_tl hcitl , hrt_establishments_vl est
where hpb.profile_id = hpi.profile_id
and hpi.content_type_id = hct.content_type_id
and hcitl.content_item_id = hpi.content_item_id
and est.establishment_id= hpi.item_number_9
and hct.context_name = 'DEGREE'
and hcitl.language ='US'
Mandeep Gupta 0 Comments
Feb 8 2021
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
Mandeep Gupta 0 Comments
Feb 8 2021
HRHD – Query to find Worker Resource Details
select hp.party_number
,hp.party_name
,hp.party_type
,hp.ORIG_SYSTEM_REFERENCE
,jrrp.PERSON_NAME
,jrrp.PERSON_FIRST_NAME
,hp.PERSON_FIRST_NAME hp_PERSON_FIRST_NAME
,jrrp.PERSON_LAST_NAME
,hp.PERSON_LAST_NAME hp_PERSON_LAST_NAME
,jrrp.PERSON_TITLE
,hp.PERSON_TITLE hp_PERSON_TITLE
,jrrp.EMAIL_ADDRESS
,hp.EMAIL_ADDRESS hp_EMAIL_ADDRESS
,jrrp.PRIMARY_PHONE_NUMBER
,hp.PRIMARY_PHONE_NUMBER hp_PRIMARY_PHONE_NUMBER
,jrrp.PARTY_ID
,jrrp.START_DATE_ACTIVE
,jrrp.END_DATE_ACTIVE
,jrrr.ROLE_RESOURCE_TYPE
,jrrb.ROLE_ID
,jrrb.ROLE_CODE
,jrrb.ROLE_TYPE_CODE
from HZ_PARTIES hp
,JTF_RS_ROLE_RELATIONS jrrr
,JTF_RS_RESOURCE_PROFILES jrrp
,JTF_RS_ROLES_B jrrb
where jrrr.ROLE_RESOURCE_ID = hp.party_id
and jrrr.role_id = jrrb.role_id
and jrrp.party_id = hp.party_id
Mandeep Gupta 0 Comments
Feb 6 2021
HCM Infolets – Enable HCM Infolets on Home screen (Part – 1)

HCM Infolets are hidden out of the box. These can enabled using configuration. Follow the below steps to enable HCM Infolets:

  • Navigate to Configuration -> Structure:
  • Click on “Go to the Manage Sandboxes Page” and click on Create Sandbox button. Give a name and choose ‘Structure’ and ‘Page Composer’ from available tools:

And click on Create and Enter the sandox.

  • From ‘Tools’ choose ‘Structure’:
  • Click on Home Icon and then on HCM Infolets and choose the value of ‘Visible’ to “Yes:
  • Click on Save, once you have made the changes:
  • Click on Application Home icon and scroll down to make sure that HCM Infolets are visible at the end of the page:
  • After verifying the changes, publish the sandbox.

Mandeep Gupta 0 Comments
Feb 5 2021
HDL – Load Goal Library Data

Goal Library is loaded as Content items using ContentItem.dat. The various attributes and their mapping can be found using GoalLibraryAttributes.xlsx which can be found on metalink using below note id:

Query To Get Goal Library Goals (Doc ID 2228128.1)

How to Load Library Goals (Doc ID 2201757.1)

Please note that ItemText2 (Priority Code) and ItemText12 (External ID) is a required attribute. Although these are not marked as mandatory and HDL load will complete successfully with a NULL value for these, however, the loaded Goals will not be visible in UI.

ItemText12 – Can be any random numeric value.

Sample HDL File:

METADATA|ContentItem|ContentItemId|ContentTypeId|ContentItemCode|Name|ItemDescrlong|ItemTextTl11|DateFrom|DateTo|ItemText1|ItemText2|ItemDate1|ItemDate2|ItemDate3|ItemText3|ItemTextTl1|ItemText5|ItemTextTl2|ItemNumber1|ItemText6|ItemText7|RatingModelId|ItemText9|ItemText10|ItemText12|SourceSystemId|SourceSystemOwner
MERGE|ContentItem||124||Test Goal Libarary 123|Goal Loaded into libarary using content item HDL|Schedule regular headcount and budget review meetings. Facilitate regular reporting distributions to senior management on a biweekly basis|2000/01/01||A|HIGH||||STRETCH||QUANTITATIVE||20|MAX|PERCENT||PERFORMANCE|ALL|1000090000|VIS09090909|VIS
Mandeep Gupta 0 Comments
Feb 5 2021
BIP Reports – Query to get pending absence transactions
select distinct module_identifier "ProcessName"
, p.person_number
, n.full_name
, subject
, STATUS
, h.INITIATOR_USER_ID "SubmittedBy" 
, d.submitted_date
, ppnf_sup.full_name "assignees"
, ps.person_number "ApproverPersonNumber"
, ppnf_sup.full_name "PendingApproverName", h.transaction_id
, module_identifier
, object
, object_id
from
hrc_txn_header h, 
hrc_txn_data d,
anc_per_abs_entries abs,
PER_PERSON_SECURED_LIST_V p, 
per_person_names_f n,
PER_ASSIGNMENT_SECURED_LIST_V a,
per_assignment_supervisors_f AssignmentSupervisor,
per_person_names_f_v ppnf_sup,
per_all_people_f ps
where 
h.transaction_id = d.transaction_id  
and object_id= abs.per_absence_entry_id
and abs.person_id = p.person_id
and n.person_id = p.person_id
and a.person_id = p.person_id
and abs.approval_status_cd = 'AWAITING'
and a.assignment_type ='E'
and n.name_type = 'GLOBAL'
and ppnf_sup.name_type = 'GLOBAL'
and a.assignment_status_type = 'ACTIVE'
and ps.person_id = ppnf_sup.person_id
and AssignmentSupervisor.assignment_id = a.assignment_id
and AssignmentSupervisor.manager_id = ppnf_sup.person_id
and sysdate between p.effective_start_date and p.effective_end_date
and sysdate between n.effective_start_date and n.effective_end_date
and sysdate between a.effective_start_date and a.effective_end_date
and sysdate between ps.effective_start_date and ps.effective_end_date
and sysdate between AssignmentSupervisor.effective_start_date and AssignmentSupervisor.effective_end_date
and sysdate between ppnf_sup.effective_start_date and ppnf_sup.effective_end_date
Mandeep Gupta 0 Comments
Feb 5 2021
Value Set – Table Value set to get list of courses
*FROM Clausewlf_learning_items_f wlif, wlf_learning_items_f_tl wlift
Value Attributes Table Alias 
*Value Column NameSUBSTR(wlift.name,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column Name 
Description Column Type 
Description Column Length 
ID Column NameTO_CHAR(wlif.LEARNING_ITEM_ID)
ID Column TypeVARCHAR2
ID Column Length40
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausewlif.LEARNING_ITEM_ID = wlift.LEARNING_ITEM_ID AND wlift.language = ‘US’ AND wlif.LEARNING_ITEM_TYPE = ‘ORA_COURSE’ AND TRUNC(SYSDATE) BETWEEN wlif.effective_start_date and wlif.effective_end_date AND wlif.effective_start_date BETWEEN wlift.effective_start_date and wlift.effective_end_date
ORDER BY Clause 
Mandeep Gupta 0 Comments
Feb 5 2021
Value Set – Table Value set to get list of departments

Use below setup to create a table based value set which will show the list of all departments:

*FROM ClauseHR_ORG_UNIT_CLASSIFICATIONS_F HOCF, HR_ORGANIZATION_UNITS_F_TL HOUT
Value Attributes Table Alias 
*Value Column NameSUBSTR(HOUT.NAME,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column Name 
Description Column Type 
Description Column Length 
ID Column NameTO_CHAR(HOUT.ORGANIZATION_ID)
ID Column TypeVARCHAR2
ID Column Length40
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE ClauseHOCF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID AND TRUNC(SYSDATE) BETWEEN HOCF.EFFECTIVE_START_DATE AND HOCF.EFFECTIVE_END_DATE AND HOCF.EFFECTIVE_START_DATE BETWEEN HOUT.EFFECTIVE_START_DATE AND HOUT.EFFECTIVE_END_DATE AND HOUT.LANGUAGE = USERENV(‘LANG’) AND HOCF.CATEGORY_CODE = ‘DEPARTMENT’
ORDER BY Clause 
Mandeep Gupta 0 Comments
Feb 5 2021
HRHD – Query to get Service Request Details

Use below query to get service request details from Fusion BIP:

SELECT ssr.sr_id,
ssr.title,
ssr.problem_desc,
to_char(ssr.open_date,’RRRR/MM/DD’) sr_opening_date,
ssr.primary_contact_party_id,
ssr.sr_number,
ssr.status_cd,
(SELECT flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_code = ssr.status_cd
AND flv.lookup_type = ‘ORA_SVC_SR_STATUS_CD’
AND flv.LANGUAGE = ‘US’) sr_status_meaning,
ssr.severity_cd,
(SELECT meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_code = ssr.severity_cd
AND flv.lookup_type = ‘ORA_SVC_SR_SEVERITY_CD’
AND flv.LANGUAGE = ‘US’) sr_severity_meaning,
sc.category_name category,
sq.queue_name queue,
to_char(ssr.last_resource_assign_date,’RRRR/MM/DD’) sr_last_resource_assigned_on
FROM svc_service_requests ssr,
svc_queues sq,
svc_categories sc
WHERE ssr.queue_id = sq.queue_id
AND ssr.category_id = sc.category_id
–AND ssr.status_cd in (‘ORA_SVC_NEW’,’ORA_SVC_INPROGRESS’)

Mandeep Gupta 0 Comments
Feb 5 2021
Reports (BIP) – Query to extract Person EIT Data
select papf.person_number ,
ppeif.PEI_INFORMATION1,
ppeif.PEI_INFORMATION2,
ppeif.PEI_INFORMATION3,
to_char(ppeif.PEI_INFORMATION_DATE1,'yyyy/mm/dd')date_from,
to_char(ppeif.PEI_INFORMATION_DATE2,'yyyy/mm/dd')date_to
from per_all_people_f papf,
PER_PEOPLE_EXTRA_INFO_F_V ppeif
where 1=1
and papf.person_id = ppeif.person_id
AND ppeif.INFORMATION_TYPE = 'XYZ Test Results'
AND ppeif.PEI_INFORMATION_CATEGORY = 'XYZ Test Results'
Mandeep Gupta 0 Comments
Feb 4 2021
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

Mandeep Gupta 0 Comments
Feb 3 2021
Reports (BIP) – Query to get Legislative Data group associated with Legal Entity

select ple.name legal_employer
, pldgt.name legislative_data_group
from per_legislative_data_groups pldg
,per_legislative_data_groups_tl pldgt
,Hr_Organization_Information_F Psu_Inf
,per_legal_employers ple
where 1=1
and pldg.legislative_data_group_id = pldgt.legislative_data_group_id
and pldgt.language = ‘US’
and psu_inf.Org_Information_Context = ‘PER_PSU_PAYROLL_INFO’
and psu_inf.Org_Information1 = pldgt.legislative_data_group_id
and Psu_Inf.Organization_Id = ple.Organization_Id
and TRUNC(sysdate) BETWEEN ple.effective_start_date AND ple.effective_end_date

Mandeep Gupta 0 Comments