1. Firstly, we need to run the below query to get the assigned security profile to the data roles. 2. Secondly, we need to create the OTBI report to get the users who have been assigned the data roles.
SELECT r_code.*,"Role Code" || ' - '||"Role Name"|| ' - '||"Job Role Name" || ' - '||"Organization Security Profile"|| ' - '||"Position Security Profile"|| ' - '||"Country Security Profile"|| ' - '||"LDG Security Profile"|| ' - '||"Person Security Profile"|| ' - '||"Public Person Security Profile"||' - '||"Document Type Security Profile"||
select papf.person_number, ppnf.display_name, paaf.assignment_number,
Select pea.email_address_id, papf.person_id, pea.email_type,pea.email_address, case when pea.email_address_id = papf.primary_email_id then 'Y'
SELECT d.*-- h.transaction_id, d.state,d.status,h.module_identifier, h.CREATION_DATE FROM HRC_TXN_HEADER h,HRC_TXN_DATA d WHERE h.Transaction_id = d.Transaction_id and h.transaction_id = 000000000000000
A client wants the employee cannot add the absence type in the future date, it should be today’s day only. We have written the fast formula. DEFAULT FOR IV_START_DATE is '0001/01/01 00:00:00' (Date) INPUTS ARE IV_START_DATE (Date)
select * from per_all_people_f where person_number= '1734'
select per.person_number,PER_ABSENCE_ENTRY_ID ,abs.absence_type_id, abs.name absence_type, to_char(apae.start_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') start_date, to_char(apae.end_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') end_date, duration from ANC_PER_ABS_ENTRIES apae, PER_PERIODS_OF_SERVICE pps, PER_ALL_PEOPLE_F per,
Select ITEM.CONTENT_ITEM_ID, ITEM.CONTENT_TYPE_ID, ITEM.CONTENT_ITEM_CODE,ITEM.DATE_FROM, ITEM.BUSINESS_GROUP_ID, TL.NAME, TL.ITEM_DESCRIPTION, TYPE.CONTEXT_NAME, ITEM.CREATED_BY, ITEM.CREATION_DATE, ITEM.LAST_UPDATED_BY, ITEM.LAST_UPDATE_DATE
select "HRG_GOAL_PLN_ASSIGNMENTS"."GOAL_PLAN_ASSIGNMENT_ID" as "GOAL_PLAN_ASSIGNMENT_ID", "HRG_GOAL_PLN_ASSIGNMENTS"."GOAL_PLAN_ID" as "GOAL_PLAN_ID", "HRG_GOAL_PLN_ASSIGNMENTS"."ASSIGNMENT_ID" as "ASSIGNMENT_ID",
Select Txnd.Status As Txn_Status ,Txnh.Module_Identifier ,p.person_number ,txnh.CREATION_DATE ,Txnh.Transaction_Id
select "PER_PERIODS_OF_SERVICE"."LAST_UPDATE_DATE" as "LAST_UPDATE_DATE", "PER_PERIODS_OF_SERVICE"."LAST_UPDATED_BY" as "LAST_UPDATED_BY", "PER_ALL_PEOPLE_F"."PERSON_NUMBER" as "PERSON_NUMBER"
SELECT SeniorityDateSetUp.seniority_date_code, HcmLookup.MEANING,SeniorityDateSetUp.SENIORITY_VERSION FROM fusion.per_seniority_dates_setup SeniorityDateSetUp,
Select distinct papf.person_number,profile_code from PER_ALL_PEOPLE_F PAPF, HRT_PROFILES_B HPB where PAPF.PERSON_ID = HPB.PERSON_ID AND Profile_Code is NULL
EXISTS (SELECT 1 FROM PER_ALL_ASSIGNMENTS_M ASG,PER_PERIODS_OF_SERVICE PS WHERE ASG.ASSIGNMENT_TYPE IN('E','C','N','P') AND ASG.EFFECTIVE_LATEST_CHANGE='Y' AND SYSDATE BETWEEN LEAST(SYSDATE,ASG.EFFECTIVE_START_DATE) AND ASG.EFFECTIVE_END_DATE AND
SELECT DISTINCT dhou.ATTRIBUTE2 as Project_Code, dhou.ATTRIBUTE3 as Expense_Code, dhou.name AS DEPT_NAME, dhou1.name AS PARENT_DEPARTMENT_NAME
For specific employee select * from PER_EMAIL_ADDRESSES where PERSON_ID in (select person_id from per_all_people_f where person_number = 'XXXX') For All employees select * from PER_EMAIL_ADDRESSES
The following SQL will give the list of all data roles and the column 'PERSON_SECURITY_PROFILE' will give the list of person security profiles associated with them. You can use this a build a report and filter on that. Alternatively, you can use the SQL and modify it to fetch the...
select * from ps_ceh_ft_emplid where emplid = '00000000'
Data File Name - Worker.dat METADATA|PersonEmail|EmailAddressId|PersonId|PersonNumber|DateFrom|DateTo|EmailType|EmailAddress|PrimaryFlag|GUID|SourceSystemOwner|SourceSystemId MERGE|PersonEmail|300000000000000|400000000000000|100|1951/01/01|4712/12/31|H1|HOMEEMAIL@oracle.com|N|||