Alert composer a very powerful and useful tool to configure and sent notifications to the users. However, it is very important for business to know the list of receipients for each message. I have tried to built a query to get these details.
SELECT hat.name alert_name
,harr.sent_to
,harr.sent_on
FROM HRC_ALERTS_TL hat
,HRC_ALERT_RUNS har
,HRC_ALERT_RUN_MESSAGES harm
,HRC_ALERT_RUN_RECIPIENTS harr
WHERE hat.name = 'Learning Assignment Alert'
AND hat.alert_id = har.alert_id
AND hat.language = 'US'
AND har.created_by = '[email protected]'
AND TRUNC(har.creation_date) = TRUNC(SYSDATE)
AND har.run_id = harm.run_id
AND harm.run_id = harr.run_id
AND harm.run_message_id = harr.run_message_id
ORDER BY harr.sent_to
SELECT pjf.job_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
,PER_JOBS_F pjf
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 pjf.job_id = hpr.object_id
AND trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_Date
AND pjf.job_code= '1099'
AND hpt.language = 'US'
SELECT pjf.job_id
, pjf.job_code
, pjft.name
, pjffv.job_family_name
, pjf.effective_start_date
, pjf.job_function_code
, hikm.source_system_id
, hikm.source_system_owner
FROM per_jobs_f pjf
, per_jobs_f_tl pjft
, per_job_family_f_vl pjffv
, hrc_integration_key_map hikm
WHERE pjf.job_id = hikm.surrogate_id
AND pjft.job_id = pjf.job_id
AND trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
AND trunc(sysdate) between pjft.effective_start_date and pjft.effective_end_date
AND trunc(sysdate) between pjffv.effective_start_date and pjffv.effective_end_date
AND pjft.language = 'US'
AND pjffv.job_family_id = pjf.job_family_id
List of all job families not associated with a Job:
SELECT *
FROM per_job_family_f_vl pjffv
WHERE 1=1
AND NOT EXISTS (SELECT 1
FROM per_jobs_f pjf
WHERE pjffv.job_family_id = pjf.job_family_id)
With the security concerns regarding access to Oracle HCM applications, I have seen many customers asking for a way to restrict access to a particular DEV/TEST environment having unmasked data. One option in such scenario’s is to keep only the admin user accounts active in the particular environment and deactivate all other user accounts. This way, the user roles data is kept intact and access is restricted to only a set of limited users.
Let us now understand, the kind of users which can exist in Fusion HCM environment. There can be system users (seeded), service accounts, worker accounts (users tied to a person), standalone user accounts (for vendors/ SI partners). So, it is really important to filter the right set of user accounts which should be deactivated. Also, the method of deactivation can vary depending upon the type of user.
Bulk deactivation of users can be performed using either HDL or by using SCIM REST API. While HDL is bulk data upload tool but it has its own set of limitations. HDL can’t be used to deactivate standalone users i.e. the users which don’t have an associated person record. To deactivate standalone users, REST API should be used.
I will discuss both the approaches in details. Let us first find a way to store the admin user accounts which should remain active. My preferred way of doing this is to create a Common Lookup and add the details (user names) in this lookup. This is because lookup values can be updated easily using a spreadsheet loader.
Below is the sample lookup (XX_ACTIVE_USER_ACCOUNTS) which I created to store the admin user names:
Next step is to add the user accounts in the meaning attribute:
Two user accounts – [email protected] and [email protected] have been added. The next steps will be to filter these record from the deactivation steps.
Let us now discuss the first approach which is to deactive user accounts using HDL. Below SQL query can be used to get a list of all required active user accounts in User.dat HDL format:
SELECT 'METADATA|User|UserId|Suspended' datarow
,1 seq
FROM DUAl
UNION
SELECT 'MERGE|User|'
|| pu.user_id
|| '|Y' datarow
,2 seq
FROM per_users pu
WHERE pu.person_id IS NOT NULL
AND pu.created_by NOT IN ('anonymous')
AND pu.username NOT LIKE 'FUSION%APPS%'
AND pu.username NOT IN ('AIACS_AIAPPS_LHR_STAGE_APPID','FAAdmin','FAWService','FAWService_APPID','FIISUSER','HCMSI-98f0f163a79a46c58fa4572e41fac8ed_scim_client_APPID','IDROUser','IDRWUser', 'OCLOUD9_osn_APPID','PSCR_PROXY_USER','PUBLIC','app_monitor1','app_monitor', 'em_monitoring2','fa_monitor','faoperator','oamAdminUser','puds.pscr.anonymous.user','weblogic_idm','anonymous'
)
AND pu.suspended = 'N'
AND lower(pu.username) NOT IN (SELECT lower(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'XX_ACTIVE_USER_ACCOUNTS'
AND flv.language = 'US'
AND flv.enabled_flag = 'Y'
)
ORDER BY seq
So, the above query will return only those active user accounts which are attached to a person record and don’t exist in the custom lookup XX_ACTIVE_USER_ACCOUNTS.
**Suspended Flag in PER_USERS table indicate if the user is active (N) or inactive (Y).
Next step is to create a BIP data model and a report and save the output data in excel format. From excel, copy the data in a Notepad and save the file as User.dat.
Sample Output in excel format:
Copy the data except for “DATAROW” and paste it in a Notepad. Save the file as User.dat:
zip the User.dat file and upload it in HCM using Data Exchange -> Import and Load.
Once the load is successful, please run – ‘Send Pending LDAP Requests’ ESS job. This should deactivate all the extracted users.
You can run quick queries on per_users to make sure that the user accounts have been deactivated.
Second approach is to use SCIM REST API to bulk deactivate user accounts. I recommend to use this approach only for those users where no person record is attached to the user account.
Please check below MOS note for details on the step by step instructions on SCIM REST API:
Fusion Security: Using SCIM REST API (Doc ID 2346455.1)
Please note that in order to run this REST API, the user should have – IT Security Manager role.
{
"Operations":[
{
"method":"PATCH",
"path":"/Users/0453A72EE08D419BE0631078680AA831",
"bulkId":"100000001",
"data":{
"schemas":[
"urn:scim:schemas:core:2.0:User"
],
"active":false
}
},
{
"method":"PATCH",
"path":"/Users/0453A72EE08D419BE0631078612AA832",
"bulkId":"100000001",
"data":{
"schemas":[
"urn:scim:schemas:core:2.0:User"
],
"active":false
}
}
]
}
Please note (taken from above Oracle note):
The bulkId attribute value should be set to UNIQUE value, while creating user accounts in BULK. This is required as per IETF SCIM Specifications while creating new resources using POST method. You may use a common value for the bulkId attribute while using PATCH, DELETE, PUT methods in a Bulk operation.
The main challenge with this approach is to get the correct JSON Payload for multiple users from system. I have created a BIP report for this which will generate the output data in required JSON format. Below is the sample code:
SELECT '{
"Operations":['
data_row, 1 seq
FROM DUAL
UNION
SELECT
'{
"method":"PATCH",
"path":"/Users/'
||pu.user_guid||
'",
"bulkId":"1000000000001",
"data":{
"schemas":[
"urn:scim:schemas:core:2.0:User"
],
"active":false
}
},' data_row, 2 seq
FROM per_users pu
WHERE pu.person_id IS NOT NULL
AND pu.created_by NOT IN ('anonymous')
AND pu.username NOT LIKE 'FUSION%APPS%'
AND pu.username NOT IN ('AIACS_AIAPPS_LHR_STAGE_APPID','FAAdmin','FAWService','FAWService_APPID','FIISUSER',
'HCMSI-98f0f163a79a46c58fa4572e41fac8ed_scim_client_APPID','IDROUser','IDRWUser',
'OCLOUD9_osn_APPID','PSCR_PROXY_USER','PUBLIC','app_monitor1','app_monitor',
'em_monitoring2','fa_monitor','faoperator','oamAdminUser','puds.pscr.anonymous.user',
'weblogic_idm','anonymous'
)
AND pu.suspended = 'N'
AND lower(pu.username) NOT IN (SELECT lower(flv.meaning)
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'XX_ACTIVE_USER_ACCOUNTS'
AND flv.language = 'US'
AND flv.enabled_flag = 'Y'
)
UNION
SELECT '
]
}' data_row, 3 seq
FROM dual
ORDER BY seq
You can create a BIP data model and report to get data from this query. Extract the data in excel format and copy it to a notepad. Then you need to remove the highlighted comma in order for this JSON payload to work.
You can use SOAP UI/Postman to run the REST API and provide the output from Notepad as JSON input. Once the API runs successfully, the suspended flag will get changed to Y in per_users table.
Use below SQL to get benefits relationship name and status for a worker:
SELECT papf.person_number
,paam.assignment_number
,houft.name legal_entity_name
,bbrf.benefit_relation_name
,bbrf.status
,TO_CHAR(bbrf.effective_start_date,'YYYY/MM/DD') effective_start_date
,TO_CHAR(bbrf.effective_end_date,'YYYY/MM/DD') effective_end_date
FROM per_all_people_f papf
,per_all_assignments_m paam
,ben_benefit_relations_f bbrf
,hr_organization_units_f_tl houft
WHERE 1 =1
AND bbrf.person_id = papf.person_id
AND paam.person_id = papf.person_id
AND bbrf.rel_prmry_asg_id = paam.assignment_id
AND bbrf.legal_entity_id = houft.organization_id
AND houft.LANGUAGE = 'US'
AND paam.assignment_type NOT LIKE '%T'
AND paam.effective_latest_change = 'Y'
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
ORDER BY papf.person_number,paam.assignment_number,bbrf.effective_start_date
Many a times when the reports are delivering the content to UCM server, we get below error:
Document delivery failed
[INSTANCE_ID=bip.bi_server1] [DELIVERY_ID=-1]Exception happened when delivery document to wcc
deliver API call throw ProcessingException
WCC delivery failed with Exception
WCC delivery failed with DeliveryException
oracle.xdo.service.delivery.DeliveryException: oracle.j2ee.ws.client.jaxws.JRFSOAPFaultException: Client received SOAP Fault from server : InvalidSecurity : error in processing the WS-Security security header
oracle.xdo.service.delivery.DeliveryException: oracle.xdo.service.delivery.DeliveryException: oracle.j2ee.ws.client.jaxws.JRFSOAPFaultException: Client received SOAP Fault from server : InvalidSecurity : error in processing the WS-Security security header
at oracle.xdo.service.delivery.impl.DeliveryServiceImpl.deliverToWCC(DeliveryServic
In this case look at the password for account used to connect to UCM server. If the password is reset make sure it is updated at the UCM connection as well.
You use “Manage Address Formats” task from Setup and Maintenance to define the address style for a particular country. Oracle provides a lot of address format for various countries out of the box. You have the capaibility to customize the address formats as per customer requirement.
For example, you can search for all available address style for United Kingdom using country filter:
You can see below details:
You can edit the address format to customize the address style. You can make a seeded attribute mandatory/ non-mandatory or can add a new address element and many more properties can be set:
You can save the changes. And the address format become active.
One of the main challenges, the data migration team and the integration teams face is the mapping of layout fields to actual backend table attribute in per_addresses_f table.
There is no direct way to find the UI attribute name and its corresponding attribute name in backend table. Below query has been developed to find the UI prompt with actual table attribute name:
SELECT ftt.TERRITORY_SHORT_NAME
,hsfl.TERRITORY_CODE
,hsflb.STYLE_FORMAT_CODE
,hsflb.VARIATION_NUMBER
,hsflt.PROMPT
,hsflb.ATTRIBUTE_CODE
,hsflb.LINE_NUMBER
,hsflb.MANDATORY_FLAG
,hsflb.USE_INITIAL_FLAG
,hsflb.UPPERCASE_FLAG
,hsflb.STATUS_FLAG
,hsflb.TRANSFORM_FUNCTION
,hsflb.DELIMITER_BEFORE
,hsflb.DELIMITER_AFTER
,hsflb.BLANK_LINES_BEFORE
,hsflb.BLANK_LINES_AFTER
,hsflb.START_DATE_ACTIVE
,hsflb.END_DATE_ACTIVE
,hsflb.DISPLAY_SIZE
FROM HZ_STYLE_FMT_LAYOUTS_B hsflb
,HZ_STYLE_FMT_LAYOUTS_TL hsflt
,HZ_STYLE_FMT_LOCALES hsfl
,FND_TERRITORIES_TL ftt
WHERE hsflb.STYLE_FORMAT_CODE = hsfl.STYLE_FORMAT_CODE
AND hsflb.STYLE_FMT_LAYOUT_ID = hsflt.STYLE_FMT_LAYOUT_ID
AND ftt.TERRITORY_CODE = hsfl.TERRITORY_CODE
AND ftt.LANGUAGE = 'US'
AND ftt.LANGUAGE = hsflt.LANGUAGE
AND ftt.TERRITORY_CODE = 'MX'
ORDER BY hsflb.STYLE_FORMAT_CODE, hsflb.LINE_NUMBER
There can multiple grades assigned as Valid grade at Position level or Job level. However, there is no direct way to end date the valid grades in bulk.
You can end date a valid grade from responsive UI. But it will be a lot of manual effort. You can search for Position and then navigate to Grades section. Then update the position and click on small delete icon next to Grade name (which you want to end date). This will end date the valid grade with an effective end date = date of position update – 1.
To do this in bulk using HDL, you can’t use DELETE command. If you use DELETE command, it will completely Purge the valid grade record from Position. To end date the valid grade, use “ReplaceLastEffectiveEndDate” attribute in the file.
Below is the sample file:
METADATA|PositionGrade|BusinessUnitName|PositionCode|EffectiveStartDate|EffectiveEndDate|GradeCode|GradeSetCode|ReplaceLastEffectiveEndDate
MERGE|PositionGrade|Progress US Business Unit|PRGUSPOS032|2018/12/31|2023/12/31|Hourly01|PRGUSGRADESET|Y
EffectiveStartDate – Earliest Grade Start Date
EffectiveEndDate – Date on which you want to end date the grade.
Once the file is loaded successfully, below is how the data will look in the backend:
Below SQL query can be used to extract valid grades data:
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.POSITION_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,
HR_ALL_POSITIONS_F_VL pjfv,
per_grades_f_vl pgfv
WHERE 1=1
AND pvgf.position_id = pjfv.position_id
AND pvgf.grade_id = pgfv.grade_id
AND pjfv.POSITION_CODE = 'PRGUSPOS032'
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 POSITION_CODE,grade_code
You can attach multiple adjustment reasons on accrual plan level. You can create any new custom adjustment reasons and add them in a lookup – ANC_ABS_PLAN_OTHER_REASONS.
Once the reason is added in the lookup, you can navigate to Absence Plans.
You can choose the newly added lookup code under “Enteries and Balances” tab.
Once the reasons are added, you can run below SQL query to extract the reasons added to the plan:
SELECT aapft.name
,aapf.legislation_code
,aapf.other_reasons
FROM ANC_ABSENCE_PLANS_F aapf
,ANC_ABSENCE_PLANS_F_TL aapft
WHERE aapf.absence_plan_id = aapft.absence_plan_id
AND aapft.language = 'US'
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 aapf.other_reasons IS NOT NULL
AND aapf.other_reasons LIKE '%XX_MLA%'
ORDER BY 2,1
SELECT papf.person_number
,ppnf.full_name
,pao.action_type_code
,pav.action_name
,part.action_reason termination_reason
FROM per_all_people_f papf
,per_person_names_f ppnf
,per_periods_of_service ppos
,per_actions_vl pav
,per_action_reasons_tl part
,per_action_occurrences pao
WHERE papf.person_id =ppnf.person_id
AND papf.person_id =ppos.person_id
AND UPPER(ppnf.name_type)='GLOBAL'
--AND TO_CHAR(ppos.actual_termination_date,'dd-mm-yyyy')<TO_CHAR(SYSDATE,'dd-mm-yyyy')
AND ppos.action_occurrence_id = pao.action_occurrence_id
AND pao.action_reason_id = part.action_reason_id (+)
AND part.language (+) = USERENV('LANG')
AND TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) AND TRUNC(papf.effective_end_date)
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppnf.effective_start_date) AND TRUNC(ppnf.effective_end_date)
AND papf.person_number = '123351'
AND pao.action_id = pav.action_id
order by papf.person_number
Learning admin can attachments on offering level for different roles – Admin/ Employee etc.
In case, you need to extract the attachment details for an offering, below BIP can be used:
SELECT wlf_learning_items_f.learning_item_id
,wlf_learning_items_f.learning_item_number
,wlf_li_relations_f.relation_id
,fad.pk1_value
,fad.attached_document_id
,fdt.document_id
,fdt.title
,fdt.file_name
,fad.creation_date
FROM fnd_attached_documents fad
,fnd_documents_tl fdt
,wlf_learning_items_f wlf_learning_items_f
,wlf_li_relations_f wlf_li_relations_f
WHERE fad.document_id = fdt.document_id
AND fdt.language = 'US'
AND fad.entity_name = 'WLF_LI_RELATIONS_F'
AND fad.pk1_value = wlf_li_relations_f.relation_id
AND wlf_learning_items_f.learning_item_id = wlf_li_relations_f.source_id
AND TRUNC(SYSDATE) BETWEEN wlf_learning_items_f.effective_start_date AND wlf_learning_items_f.effective_end_date
AND TRUNC(SYSDATE) BETWEEN wlf_li_relations_f.effective_start_date AND wlf_li_relations_f.effective_end_date
ORDER BY fad.creation_date DESC
Once a worker record is terminated, it is expected that the associated user account will get inactive. However, it depends upon the configuration on Enterprise Level and auto provisioning rules setup. If there is even on role assigned to user name, the user account will not get terminated in HCM.
To help the business to get a list of active user accounts even if the associated worker record is terminated, below query is created:
SELECT DISTINCT papf.person_number
,ppos.actual_termination_date
FROM PER_ALL_PEOPLE_F papf
,PER_PERIODS_OF_SERVICE ppos
,PER_USERS pu
,PER_USER_ROLES pur
WHERE papf.person_id = ppos.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = pu.person_id
AND pu.user_id = pur.user_id
AND ppos.actual_termination_date IS NOT NULL
AND ppos.actual_termination_date < TRUNC(SYSDATE) /*Check only for past dated terminations*/
AND NOT EXISTS (SELECT 1 FROM PER_PERIODS_OF_SERVICE ppos2
WHERE ppos2.person_id = ppos.person_id
AND ppos2.actual_termination_date IS NULL)
ORDER BY 1,2
In HCM, you can define approval rules for different HCM tasks from Tools -> Transaction Console -> Approval Rules
For example, you want to define an approval rule for Promote action. You can search Promo% in Find box and it will give you all matching approval rules:
You can then click on ‘Configure Rules’ to define the rules.
However, Transaction console has many limitations. You can’t define complex approval rules. You can’t make use of functions etc (Day between) in transaction console. In order to define more complex rules, you need to do it from BPM.
First step is that you need to search for a task from “Task Configuration”:
The issue here is sometimes the Approval Rule names in Transaction Console doesn’t match with Task names in BPM.
In such case, you can make sure of below Query:
SELECT HAPT.NAME "Approval Rule Name"
,HAPT.DESCRIPTION
,HRPB.TASK_FILE_NAME TaskName
,HRPB.TXN_MODULE_IDENTIFIER
,HAPT.CATEGORY_NAME
,HRPB.CATEGORY_CODE
,HAPT.SUBCATEGORY_NAME
,HRPB.SUBCATEGORY_CODE
,HRPB.FAMILY
,DECODE(HAAO.APPROVAL_DISABLED,'true','Bypassed','false','Enabled') "Enabled Status"
FROM FUSION.HRC_ARM_PROCESS_B HRPB
,FUSION.HRC_ARM_PROCESS_TL HAPT
,FUSION.HRC_ARM_APPROVAL_OPTIONS HAAO
WHERE HRPB.PROCESS_ID=HAAO.PROCESS_ID
AND HRPB.PROCESS_ID=HAPT.PROCESS_ID
AND HAPT.LANGUAGE = 'US'
AND HRPB.FAMILY = 'HCM'
ORDER BY 1
Oracle HCM provides a functionality to store different kinds of worker documents in Document Records. Over a period of time, a large number of documents get accumulated for different workers. In such cases, there are requirements to know size of attachments in DORs. For, this I have developed a simple query which can be modified as needed:
select fdv.file_name
,fdv.title
,dm_document_id
,TRUNC(SUM(wcc_documents.dFileSize)/1024,2) as "Size in KB"
,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024,2) as "Size in MB"
,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024/1024,2) as "Size in GB"
from hr_documents_of_record hdr,
fnd_attached_documents fad,
fnd_documents_vl fdv,
fusion_ocserver11g.revisions wcc_revisions,
fusion_ocserver11g.documents wcc_documents
where to_char(hdr.documents_of_record_id) = fad.pk1_value(+)
and fad.entity_name(+) = 'HR_DOCUMENTS_OF_RECORD'
and fad.document_id = fdv.document_id(+)
and fdv.dm_version_number = wcc_revisions.did
and wcc_revisions.did = wcc_documents.did
and wcc_documents.disprimary = 1
group by fdv.file_name
,fdv.title
,dm_document_id
order by 1
Below SQL query can be used to find part-timers in HCM. Any worker where FTE is less than 1 is normally considered as part-timer:
select paam.assignment_number, pawmf.value
from per_legal_employers ple
,per_periods_of_service ppos
,per_all_assignments_m paam
,PER_ASSIGN_WORK_MEASURES_F pawmf
where ppos.legal_entity_id = ple.organization_id
and paam.period_of_Service_id = ppos.period_of_Service_id
and TRUNC(SYSDATE) between paam.effective_start_date AND paam.effective_end_date
and TRUNC(SYSDATE) between pawmf.effective_start_date AND pawmf.effective_end_date
and paam.assignment_status_type like 'ACTIVE%'
and ple.name like 'XX%United%Stat%'
and paam.assignment_id = pawmf.assignment_id
and pawmf.unit = 'FTE'
and pawmf.value <1
and paam.assignment_type ='E'
and ppos.actual_termination_date is null
order by 1
Recently, I faced a scenario for a customer where after go-live, where there was an issue found with absence plan configuration. The absence plan was incorrectly set up. The Balance Frequency Source wasn’t setup to “Repeating Period”, so the accrual was calculated incorrectly. In order to fix this, a new absence plan was created and the absence entries were made against the new plan.
So, the approach taken was to take a backup of all absence entries from PROD, enroll the employees into new plan and reupload the absence entries.
A BIP report was developed to take a backup of existing absence entries in HDL format. Below is the query for same:
Once the data is extracted, you need to make sure that Source System Owner is updated from FUSION to HRC_SQLLOADER. Source System Owner is set to FUSION when an entry is created from UI.
Once the output of the BIP is ready, Change “MERGE” to “DELETE” to delete all the absence entries. Then enroll the workers in new plan, do the required changes in BIP extract and upload the data back in Fusion HCM.
There are times when we just need a quick query to count number of positions created in HCM against a department. Below is a sample SQL which can be handy in such situations:
select *
from hr_all_positions_f hapf
where hapf.organization_id IN (select distinct organization_id
from HR_ORGANIZATION_UNITS_F_TL houft
where (houft.name LIKE 'Test%'
OR houft.name LIKE 'Abc%'
)
)
Below sample queries can be used to extract count of workers (Active/ Inactive) in HCM:
Query 1:
select count(distinct person_number)
from per_all_people_f papf
where 1=1
and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)
Query 2: Find count workers not having basic assignment details:
select (distinct person_number)
from per_all_people_f papf
where 1=1
and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)
and person_number not in (SELECT papf.person_number
FROM per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_m paam,
per_jobs_f_tl pjft,
hr_organization_units_f_tl hauft,
per_periods_of_service ppos
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 papf.person_number = '414'
AND hauft.organization_id (+) = paam.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 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 ppnf.name_type = 'GLOBAL'
AND paam.period_of_service_id = ppos.period_of_service_id
AND ppos.date_start = (select max(ppos2.date_start) from per_periods_of_service ppos2
where ppos2.person_id = ppos.person_id
and ppos2.primary_flag = 'Y')
)
On the responsive Position UI, the position screens shows the Pending Worker and Worker record as an incumbent and the FTE goes in negative.
Below SQL can be used to identify such persons:
SELECT paam.person_id, hapfv.name
FROM per_all_assignments_m paam
,hr_all_positions_f_vl hapfv
WHERE paam.position_id = hapfv.position_id
AND paam.assignment_type NOT LIKE '%T'
AND paam.assignment_status_type NOT LIKE 'INACTIVE'
AND TRUNC(SYSDATE) BETWEEN hapfv.effective_start_date AND hapfv.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
GROUP BY paam.person_id, hapfv.name
HAVING COUNT(*) > 1