Preface
The document is created based on online references related
to Oracle Cloud HCM - Human Capital
Management Solution which may be changed based on version or implementation
cases.
Credit
S.M. Assaduzzaman Sarker |
ITS Department | STANDARD GROUP
Contents
Tables
HR_ORGANIZATION_UNITS_F_TL
OR HR_ALL_ORGANIZATION_UNITS_VL
SELECT ORGANIZATION_ID, NAME FROM HR_ORGANIZATION_UNITS_F_TL
ORGANIZATION_ID |
NAME |
1 |
Standard Group Enterprise |
300000001955046 |
Standard Group LE |
300000001955046 |
Standard Group LE |
300000001955853 |
Industrial Engineering |
300000001955873 |
Engineering (Non-Professional) |
300000001955878 |
Engineering (Professional) |
300000001955883 |
Finance & Accounts |
300000001955958 |
Quality |
300000001955968 |
Sales & Marketing |
300000001955978 |
Administration |
300000001955988 |
Technical |
300000002502003 |
Customer Service |
300000002502008 |
Commercial |
300000002503113 |
The Civil Engineers Limited (Unit-2) |
300000002503149 |
Wear Mag Limited |
300000002503203 |
Crazy Fashions Limited |
HR_ORGANIZATION_UNITS
select NAME from HR_ORGANIZATION_UNITS WHERE ORGANIZATION_ID
= 300000001955046
300000001955001 (STANDARD GROUP)
NAME |
Standard Group LE |
PER_LOCATIONS
select INTERNAL_LOCATION_CODE from PER_LOCATIONS WHERE
LOCATION_ID = 300000008812728
INTERNAL_LOCATION_CODE |
Standard Group |
HR_ALL_POSITIONS_F_TL
SELECT
POSITION_ID, NAME
FROM HR_ALL_POSITIONS_F_TL
WHERE
NAME = 'Senior Admin Manager'
NAME |
POSITION_ID |
Senior Admin Manager |
300000009140171 |
Senior Admin Manager |
300000009140151 |
Senior Admin Manager |
300000009146552 |
Senior Admin Manager |
300000009140181 |
PER_ALL_PEOPLE_F
SELECT PERSON_ID, PERSON_NUMBER FROM PER_ALL_PEOPLE_F WHERE
PERSON_NUMBER ='1222513'
PERSON_ID |
PERSON_NUMBER |
300000007491461 |
1222513 |
PERSON_ID |
PERSON_NUMBER |
300000007499130 |
1033577(WAHID BHAI) |
PER_PEOPLE_EXTRA_INFO_F
SELECT
PEI_INFORMATION_CATEGORY,PEI_INFORMATION1,PEI_INFORMATION2
FROM
PER_PEOPLE_EXTRA_INFO_F
WHERE
PERSON_ID = 300000008793576
PEI_INFORMATION_CATEGORY |
PEI_INFORMATION1 |
PEI_INFORMATION2 |
HRX_IN_MISCELLANEOUS |
||
SG_CONTACTS |
Father |
dfghjkl |
SG_CONTACTS |
Mother |
sdfghjk |
PER_ASSIGNMENT_SUPERVISORS_F
SELECT
PERSON_ID, MANAGER_ID FROM
PER_ASSIGNMENT_SUPERVISORS_F
WHERE
MANAGER_TYPE= 'LINE_MANAGER' AND PERSON_ID= 300000007499130
MANAGER_ID |
PERSON_ID |
300000007498154 (AZAD SIR) |
300000007499130 (WAHID BHAI) |
PER_GRADES_F
SELECT GRADE_CODE, GRADE_ID FROM PER_GRADES_F
GRADE_ID |
GRADE_CODE |
300000001955508 |
SMC-M3 |
300000001955511 |
S3 |
300000001955514 |
SMC-M2 |
300000001955517 |
W7 |
300000001955520 |
JMC-M9 |
PER_IMAGES
SELECT IMAGE_ID,IMAGE FROM PER_IMAGES WHERE PERSON_ID
=300000007491461
IMAGE_ID |
IMAGE |
100000004602850 |
AFLAKJFLKAJFLKACNLKLLJLAKJFLKAJLK….. |
PER_PERSONS
SELECT BLOOD_TYPE, TO_CHAR(DATE_OF_BIRTH, 'DD-MON-RR')
DATE_OF_BIRTH FROM PER_PERSONS WHERE
PERSON_ID =300000007491461
BLOOD_TYPE |
DATE_OF_BIRTH |
O+ |
30-12-81 |
PER_PERSON_NAMES_F
SELECT
FULL_NAME, KNOWN_AS SHORT_NAME FROM PER_PERSON_NAMES_F
WHERE
PERSON_ID =300000007491461 AND PERSON_NAME_ID = 300000007491465
FULL_NAME |
SHORT_NAME |
S.M. ASSADUZZAMAN SARKER |
ASSADUZZAMAN |
PER_PEOPLE_LEGISLATIVE_F_V
SELECT
SEX,MARITAL_STATUS,HIGHEST_EDUCATION_LEVEL
FROM PER_PEOPLE_LEGISLATIVE_F_V WHERE PERSON_ID
= 300000007491461
SEX |
MARITAL_STATUS |
HIGHEST_EDUCATION_LEVEL |
M |
M |
M.Sc |
PER_PHONES
SELECT PHONE_NUMBER , PHONE_TYPE FROM PER_PHONES WHERE
PERSON_ID =300000007491461
PHONE_TYPE |
PHONE_NUMBER |
W1 |
01716591619 |
PER_RELIGIONS
SELECT RELIGION FROM
PER_RELIGIONS WHERE PERSON_ID =300000007491461
RELIGION |
MUSLIM |
PER_ALL_PEOPLE_F_V
SELECT
PRIMARY_EMAIL_ID,PRIMARY_PHONE_ID,MAILING_ADDRESS_ID,PRIMARY_NID_ID
FROM PER_ALL_PEOPLE_F_V
WHERE
PERSON_ID =300000007491461
PRIMARY_EMAIL_ID |
PRIMARY_PHONE_ID |
MAILING_ADDRESS_ID |
PRIMARY_NID_ID |
300000007491481 |
300000007491482 |
300000007491474 |
300000007491484 |
PER_ASSIGNMENT_SUPERVISORS_F
SELECT *
FROM PER_ASSIGNMENT_SUPERVISORS_F
--WHERE
ADDRESS_ID = 300000007491474
PER_ALL_PEOPLE_F_V
ADDRESS & OTHER INFO
PER_ADDRESSES_F
SELECT ADDRESS_LINE_1,
ADDRESS_LINE_2,COUNTRY,POSTAL_CODE,REGION_1,REGION_2,REGION_3,TOWN_OR_CITY
FROM PER_ADDRESSES_F WHERE ADDRESS_ID =
300000007491474
ADDRESS_LINE_1 |
ADDRESS_LINE_2 |
COUNTRY |
POSTAL_CODE |
REGION_1 |
REGION_2 |
REGION_3 |
TOWN_OR_CITY |
Village: Taluk khutamara |
Lalmonirhat |
BD |
Lalmonirhat Sadar-5500 |
Lalmonirhat Sadar |
Rangpur |
Lalmonirhat |
Lalmonirhat |
PER_PAYROLL_STATUTORY_UNITS
select ORGANIZATION_ID, name from
PER_PAYROLL_STATUTORY_UNITS
ORGANIZATION_ID |
NAME |
300000002817481 |
Test LE |
300000001955001 |
Standard Group |
300000001955001 |
Standard Group |
300000002817481 |
Test LE |
PER_PERSON_ADDR_USAGES_F
SELECT
ADDRESS_ID,ADDRESS_TYPE
FROM
PER_PERSON_ADDR_USAGES_F
WHERE
PERSON_ID = 300000007491461
ADDRESS_ID |
ADDRESS_TYPE |
300000007491474 |
HOME |
300000007491476 |
MAIL |
HCM_EXTENDED_LOOKUP_CODES_B
SELECT EXTENDED_LOOKUP_CODE_ID,ENTERPRISE_ID,LEGISLATION_CODE,LOOKUP_TYPE,
LOOKUP_CODE, EXTENDED_LOOKUP_CODE
FROM HCM_EXTENDED_LOOKUP_CODES_B
WHERE
EXTENDED_LOOKUP_CODE_ID IN (300000000252599, 300000000252600,300000000252601,
300000000252602)
EXTENDED_LOOKUP_CODE_ID |
ENTERPRISE_ID |
LEGISLATION_CODE |
LOOKUP_TYPE |
LOOKUP_CODE |
EXTENDED_LOOKUP_CODE |
300000000252599 |
0 |
RU |
ORA_PER_HIGHEST_EDUCATION_LEVE |
40 |
40 |
300000000252600 |
0 |
RU |
ORA_PER_HIGHEST_EDUCATION_LEVE |
600 |
600 |
300000000252601 |
0 |
RU |
ORA_PER_HIGHEST_EDUCATION_LEVE |
80 |
80 |
300000000252602 |
0 |
RU |
ORA_PER_HIGHEST_EDUCATION_LEVE |
ORA_HRX_EDU1 |
ORA_HRX_EDU1 |
SELECT *
FROM HCM_EXTENDED_LOOKUP_CODES_TL
EXTENDED_LOOKUP_CODE_ID
IN (300000000252599, 300000000252600,300000000252601, 300000000252602)
PER_DEPARTMENTS
SELECT NAME,
ORGANIZATION_ID FROM PER_DEPARTMENTS
WHERE ORGANIZATION_ID = 300000001955938
ORGANIZATION_ID |
NAME |
300000001955938 |
Information Technology & Services |
|
|
PER_EMAIL_ADDRESSES_V
SELECT EMAIL_ADDRESS FROM PER_EMAIL_ADDRESSES_V WHERE PERSON_ID
=300000007491461
EMAIL_ADDRESS |
assaduzzaman.s@standard-group.com |
PER_JOBS
SELECT NAME,JOB_FAMILY_ID FROM PER_JOBS WHERE JOB_ID=300000008328813
NAME |
JOB_FAMILY_ID |
System Analyst |
300000006583496 |
CMP_SALARY
SELECT
SALARY_AMOUNT, CURRENCY_CODE, SALARY_BASIS_CODE
FROM CMP_SALARY
WHERE
PERSON_ID =300000007491461
SALARY_AMOUNT |
SALARY_BASIS_CODE |
CURRENCY_CODE |
10000 |
MONTHLY |
BDT |
PER_NATIONAL_IDENTIFIERS
SELECT NATIONAL_IDENTIFIER_NUMBER
FROM PER_NATIONAL_IDENTIFIERS
WHERE PERSON_ID =300000007491461
NATIONAL_IDENTIFIER_NUMBER |
2696405625007 |
PER_ASSIGNMENTS_F
Employee History
Table
PERSON_ID |
CONTENT_TYPE_NAME |
CONTENT_ITEM_NAME |
SPECIFICAION |
GRADE |
INSTITUTE |
LEVEL_ |
COM_YEAR |
300000007495370 |
Degrees |
M.S.S |
Economics |
2nd |
National University |
Masters |
2006 |
300000007495370 |
Degrees |
H.S.C |
Science |
2nd |
Dhaka Board |
Higher Secondary |
2001 |
300000007495370 |
Degrees |
S.S.C |
Science |
1st |
Dhaka Board |
Secondary |
1998 |
300000007495370 |
Degrees |
B.S.S |
Economics |
2nd |
National University |
Graduate |
2005 |
PER_CONTACT_RELSHIPS_F
CONTACT_PERSON_ID |
CONTACT_RELATIONSHIP_ID |
CREATED_BY |
300000008793802 |
300000008793806 |
BROTHER |
300000008888357 |
300000008888361 |
S |
300000009039001 |
300000009039005 |
IN_FR |
300000009130558 |
300000009130562 |
IN_FR |
300000008767985 |
300000008767989 |
BROTHER |
PER_CONTRACTS_F
SELECT
ASSIGNMENT_ID,CONTRACT_NUMBER,PERSON_ID,PERIOD_OF_SERVICE_ID FROM
PER_CONTRACTS_F
PERSON_ID |
CONTRACT_NUMBER |
PERIOD_OF_SERVICE_ID |
ASSIGNMENT_ID |
300000008218399 |
CONT2000006 |
300000008305199 |
300000008305200 |
300000008319872 |
CONT2000007 |
300000008319887 |
300000008319888 |
300000008674906 |
CONT2000009 |
300000008674910 |
300000008674911 |
300000008794269 |
CONT2000051 |
300000008794273 |
300000008794274 |
300000008862119 |
CONT1000002 |
300000008862128 |
300000008862129 |
PER_LOCATION_EXTRA_INFO_F_
NO DATA
PER_LOCATIONS_
NO DATA
PER_LOCATION_EXTRA_INFO_F
May be valid data
PER_LOCATION_DETAILS_F_TL
SELECT LOCATION_DETAILS_ID, LOCATION_CODE,LOCATION_NAME
DESCRIPTION FROM PER_LOCATION_DETAILS_F_TL
LOCATION_DETAILS_ID |
LOCATION_CODE |
DESCRIPTION |
300000001955242 |
CEC-2 |
CEC-2 |
300000001955260 |
CEL-WOVEN |
CEL-WOVEN |
300000009277776 |
Test Location |
Test Location |
300000001955272 |
SFL |
SFL |
300000001955266 |
SDL |
SDL |
PER_LOCATION_DETAILS_F
SELECT
LOCATION_DETAILS_ID, LOCATION_ID, MAIN_ADDRESS_ID
FROM
PER_LOCATION_DETAILS_F
LOCATION_DETAILS_ID |
LOCATION_ID |
MAIN_ADDRESS_ID |
300000001955242 |
300000001955241 |
300000001955244 |
300000001955260 |
300000001955259 |
300000001955262 |
300000009277776 |
300000009277777 |
300000009277779 |
300000001955272 |
300000001955271 |
300000001955274 |
300000001955266 |
300000001955265 |
300000001955268 |
SELECT
LOCATION_ID,LOC_ADDRESS_USAGE_ID, ADDRESS_USAGE_TYPE
FROM PER_LOC_ADDRESS_USAGES_F
LOCATION_ID |
LOC_ADDRESS_USAGE_ID |
ADDRESS_USAGE_TYPE |
300000001955241 |
300000001955243 |
MAIN |
300000001955259 |
300000001955261 |
MAIN |
300000006721016 |
300000006721017 |
MAIN |
300000009277777 |
300000009277778 |
MAIN |
300000001955265 |
300000001955267 |
MAIN |
PER_CONTACT_RELSHIPS_F
CONTACT_PERSON_ID |
CONTACT_RELATIONSHIP_ID |
CREATED_BY |
300000008793802 |
300000008793806 |
BROTHER |
300000008888357 |
300000008888361 |
S |
300000009039001 |
300000009039005 |
IN_FR |
300000009130558 |
300000009130562 |
IN_FR |
300000008767985 |
300000008767989 |
BROTHER |
PER_CONTRACTS_F
SELECT
ASSIGNMENT_ID,CONTRACT_NUMBER,PERSON_ID,PERIOD_OF_SERVICE_ID FROM
PER_CONTRACTS_F
PERSON_ID |
CONTRACT_NUMBER |
PERIOD_OF_SERVICE_ID |
ASSIGNMENT_ID |
300000008218399 |
CONT2000006 |
300000008305199 |
300000008305200 |
300000008319872 |
CONT2000007 |
300000008319887 |
300000008319888 |
300000008674906 |
CONT2000009 |
300000008674910 |
300000008674911 |
300000008794269 |
CONT2000051 |
300000008794273 |
300000008794274 |
300000008862119 |
CONT1000002 |
300000008862128 |
300000008862129 |
PER_CITIZENSHIPS
select CITIZENSHIP_ID,
LEGISLATION_CODE from per_citizenships
where PERSON_ID = 300000007499130
CITIZENSHIP_ID |
LEGISLATION_CODE |
300000009236083 |
BD |
PER_PASSPORTS
SELECT
PASSPORT_NUMBER,PASSPORT_TYPE,ISSUE_DATE,EXPIRATION_DATE,ISSUING_AUTHORITY,ISSUING_COUNTRY,ISSUING_LOCATION,PROFESSION
FROM PER_PASSPORTS
WHERE PERSON_ID = 300000007499130
PASSPORT_NUMBER |
PASSPORT_TYPE |
ISSUE_DATE |
EXPIRATION_DATE |
ISSUING_AUTHORITY |
ISSUING_COUNTRY |
ISSUING_LOCATION |
PROFESSION |
34567890 |
|||||||
|
|
|
|
|
|
|
|
Queries
Some common query
SELECT DISTINCT ENTITY_TYPE
FROM PER_ACTION_OCCURRENCES
ENTITY_TYPE |
Salary Position Grade Location Organization Job |
BEN_PL_TYP_F
NO VALID DATA
PER_RATES_F
NO DATA
PAY_ENTRY_PCT_DIST
NO DATA
SELECT ORGANIZATION_ID,
ESTABLISHMENT_ID,LOCATION_ID
FROM HR_ALL_ORGANIZATION_UNITS_F
ORGANIZATION_ID |
ESTABLISHMENT_ID |
LOCATION_ID |
1 |
||
300000001955046 |
300000001950038 |
|
300000001955046 |
300000001950038 |
|
300000001955853 |
||
300000001955868 |
SELECT NAME, ORGANIZATION_ID
FROM HR_ORGANIZATION_UNITS_F_TL
NAME |
ORGANIZATION_ID |
Standard Group Enterprise |
1 |
Standard Group LE |
300000001955046 |
Standard Group LE |
300000001955046 |
Industrial Engineering |
300000001955853 |
Engineering (Non-Professional) |
300000001955873 |
SELECT DISTINCT ORG_INFORMATION_ID,
ORG_INFORMATION_CONTEXT,
ORGANIZATION_ID
FROM HR_ORGANIZATION_INFORMATION_F
ORG_INFORMATION_ID |
ORG_INFORMATION_CONTEXT |
ORGANIZATION_ID |
300000006724780 |
FUN_BUSINESS_UNIT |
300000006724777 |
300000002501983 |
PER_WORK_DAY_INFO |
300000001955001 |
300000002503960 |
PER_ORG_MANAGER_INFO |
300000002503958 |
300000006711474 |
PER_ORG_MANAGER_INFO |
300000001955978 |
300000006724672 |
FUN_BUSINESS_UNIT |
300000006724669 |
SELECT DISTINCT
ORG_UNIT_CLASSIFICATION_ID,ACTION_OCCURRENCE_ID,CLASSIFICATION_CODE,
LEGISLATION_CODE,STATUS,ORGANIZATION_ID,SET_ID,CATEGORY_CODE
FROM HR_ORG_UNIT_CLASSIFICATIONS_F
ORG_UNIT_CLASSIFICATION_ID |
ACTION_OCCURRENCE_ID |
CLASSIFICATION_CODE |
LEGISLATION_CODE |
STATUS |
ORGANIZATION_ID |
SET_ID |
CATEGORY_CODE |
300000001955934 |
300000001955932 |
DEPARTMENT |
A |
300000001955933 |
300000000001412 |
DEPARTMENT |
|
300000001955959 |
300000001955957 |
DEPARTMENT |
A |
300000001955958 |
300000000001412 |
DEPARTMENT |
|
300000001955979 |
300000006711477 |
DEPARTMENT |
A |
300000001955978 |
300000000001412 |
DEPARTMENT |
|
300000002503096 |
300000002503097 |
FUN_BUSINESS_UNIT |
A |
300000002503095 |
FUN_BUSINESS_UNIT |
||
300000002503114 |
300000002503115 |
FUN_BUSINESS_UNIT |
A |
300000002503113 |
FUN_BUSINESS_UNIT |
SELECT DISTINCT
CLASSIFICATION_CODE,CATEGORY_CODE
FROM HR_ORG_UNIT_CLASSIFICATIONS_F
CLASSIFICATION_CODE |
CATEGORY_CODE |
HCM_TRU |
HCM_TRU |
HCM_LRU |
HCM_LRU |
HCM_REPORTING_ESTABLISHMENT |
HCM_REPORTING_ESTABLISHMENT |
INV |
NO_EFF_CATEGORY |
DEPARTMENT |
DEPARTMENT |
HCM_TRU |
HCM_TRU_US |
HCM_LEMP |
HCM_LEMP_US |
FUN_BUSINESS_UNIT |
FUN_BUSINESS_UNIT |
HCM_PSU |
HCM_PSU_US |
HCM_PSU |
HCM_PSU |
ENTERPRISE |
ENTERPRISE |
CONTRACTS_RESOURCE_ORGANIZATION |
NO_EFF_CATEGORY |
HCM_LEMP |
HCM_LEMP |
select LOOKUP_TYPE,LOOKUP_CODE,MEANING
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'CONTACT'
LOOKUP_TYPE |
LOOKUP_CODE |
MEANING |
CONTACT |
A |
Adopted child |
CONTACT |
BROTHER |
Brother |
CONTACT |
C |
Child |
CONTACT |
DP |
Domestic partner |
CONTACT |
EMRG |
Emergency |
SELECT CF.CATEGORY_CODE,
OI.ORG_INFORMATION_ID, OI.ORG_INFORMATION_CONTEXT,
O.NAME , AL.ESTABLISHMENT_ID
FROM HR_ORG_UNIT_CLASSIFICATIONS_F CF,
HR_ORGANIZATION_INFORMATION_F OI, HR_ORGANIZATION_UNITS_F_TL O,
HR_ALL_ORGANIZATION_UNITS_F AL
WHERE CF.CATEGORY_CODE =' FUN_BUSINESS_UNIT '
AND CF.ORGANIZATION_ID =
OI.ORGANIZATION_ID
AND CF.ORGANIZATION_ID =
O.ORGANIZATION_ID
AND CF.ORGANIZATION_ID =
AL.ORGANIZATION_ID
ORG_INFORMATION_ID |
ORG_INFORMATION_CONTEXT |
NAME |
ESTABLISHMENT_ID |
300000002503188 |
FUN_BUSINESS_UNIT |
Adhunik Poshak Shilpa Limited |
|
300000002503314 |
FUN_BUSINESS_UNIT |
Kazipur Fashions Limited (Unit-1) |
|
300000002503404 |
FUN_BUSINESS_UNIT |
Standard Group Limited (Baraiderchala) |
|
300000006724650 |
FUN_BUSINESS_UNIT |
Standard Stitches (Gmts Unit) Limited |
|
300000006724780 |
FUN_BUSINESS_UNIT |
Adhunik Plastic Industries Limited |
SELECT CF.CATEGORY_CODE,
OI.ORG_INFORMATION_ID, OI.ORG_INFORMATION_CONTEXT,
O.NAME , AL.ESTABLISHMENT_ID
FROM HR_ORG_UNIT_CLASSIFICATIONS_F CF,
HR_ORGANIZATION_INFORMATION_F OI, HR_ORGANIZATION_UNITS_F_TL O,
HR_ALL_ORGANIZATION_UNITS_F AL
WHERE CF.CATEGORY_CODE =
'HCM_REPORTING_ESTABLISHMENT'
AND CF.ORGANIZATION_ID = OI.ORGANIZATION_ID
AND CF.ORGANIZATION_ID =
O.ORGANIZATION_ID
AND CF.ORGANIZATION_ID =
AL.ORGANIZATION_ID
ORG_INFORMATION_ID |
ORG_INFORMATION_CONTEXT |
NAME |
CATEGORY_CODE |
300000002503866 |
PER_ORG_MANAGER_INFO |
Peak Fashions Limited |
HCM_REPORTING_ESTABLISHMENT |
300000002503938 |
PER_ORG_MANAGER_INFO |
Adhunik Poshak Shilpa Limited |
HCM_REPORTING_ESTABLISHMENT |
300000002746079 |
PER_ORG_MANAGER_INFO |
Standard Fashions Limited |
HCM_REPORTING_ESTABLISHMENT |
300000002503888 |
PER_ORG_MANAGER_INFO |
Wear Mag Limited |
HCM_REPORTING_ESTABLISHMENT |
300000002503960 |
PER_ORG_MANAGER_INFO |
Crazy Fashions Limited |
HCM_REPORTING_ESTABLISHMENT |
SELECT ORGANIZATION_ID , NAME FROM HR_ORGANIZATION_UNITS_F_TL
WHERE ORGANIZATION_ID IN (
300000001955938, -- ORGANIZATION_ID--
300000001955001, --LEGAL_ENTITY_ID --
300000002503131, -- BUSINESS_UNIT_ID -
300000002503842, -- ESTABLISHMENT_ID--
NULL -- SET_OF_BOOKS_ID
)
ORGANIZATION_ID |
NAME |
300000001955001 |
Standard Group |
300000001955001 |
Standard Group |
300000001955938 |
Information Technology & Services |
300000002503131 |
The Civil Engineers Limited (Woven Unit) |
300000002503842 |
The Civil Engineers Limited |
Employee Profile Query:
/* Formatted on 11/19/2020
12:57:56 PM (QP5 v5.215.12089.38647) */
SELECT DISTINCT
M.PERSON_ID,
M.ASSIGNMENT_NUMBER,
N.FULL_NAME,
N.KNOWN_AS SHORT_NAME,
M.ASSIGNMENT_NAME DESIGNATION,
M.ASSIGNMENT_ID,
M.TIME_NORMAL_FINISH OUTTIME,
M.TIME_NORMAL_START INTIME,
M.ASSIGNMENT_STATUS_TYPE
ACTIVE_INACTIVE,
M.ASS_ATTRIBUTE2 LOCATION,
M.ASS_ATTRIBUTE1 FLOOR_,
M.ASS_ATTRIBUTE3 SAL_TYPE,
PP.BLOOD_TYPE,
TO_CHAR (PP.DATE_OF_BIRTH, 'DD-MON-RR') DATE_OF_BIRTH,
CASE
WHEN P.SEX = 'M' THEN 'Male'
WHEN P.SEX = 'F' THEN 'Female'
ELSE NULL
END
SEX,
CASE WHEN P.MARITAL_STATUS = 'M' THEN 'Married' ELSE 'Unmarried' END
MARITAL_STATUS,
P.HIGHEST_EDUCATION_LEVEL,
B.NAME BUSINESSUNIT,
BB.NAME SALARYUNIT,
E.EMAIL_ADDRESS,
--PH.PHONE_NUMBER ,
(SELECT COUNTRY_CODE_NUMBER || '-' || PHONE_NUMBER
FROM PER_PHONES PH
WHERE PHONE_TYPE = 'WM' AND PH.PERSON_ID = P.PERSON_ID)
OFFICE_MOBILE_PHONE,
(SELECT COUNTRY_CODE_NUMBER || '-' || PHONE_NUMBER
FROM PER_PHONES PH1
WHERE PHONE_TYPE = 'W1' AND PH1.PERSON_ID = P.PERSON_ID)
OFFICE_DESK_PHONE,
(SELECT COUNTRY_CODE_NUMBER || '-' || PHONE_NUMBER
FROM PER_PHONES PH2
WHERE PHONE_TYPE = 'H1' AND PH2.PERSON_ID = P.PERSON_ID)
HOME_PHONE,
R.RELIGION,
G.GRADE_CODE,
O.NAME ORGANIZATION_UNITS,
AD.ADDRESS_LINE_1,
AD.ADDRESS_LINE_2,
AD.COUNTRY,
AD.POSTAL_CODE,
AD.REGION_1,
AD.REGION_2,
AD.REGION_3,
AD.TOWN_OR_CITY,
MAIL.ADDRESS_LINE_1
ADDRESS_LINE_11,
MAIL.ADDRESS_LINE_2
ADDRESS_LINE_22,
MAIL.COUNTRY COUNTRY1,
MAIL.POSTAL_CODE POSTAL_CODE1,
MAIL.REGION_1 REGION_11,
MAIL.REGION_2 REGION_21,
MAIL.REGION_3 REGION_31,
MAIL.TOWN_OR_CITY TOWN_OR_CITY1,
SNM.MANAGER_NAME,
NI.NATIONAL_IDENTIFIER_NUMBER,
TO_CHAR (PSER.DATE_START, 'DD-Mon-RR') HIRE_DATE,
PASS.PASSPORT_NUMBER,
MOTHER_IN.MOTHER_INFO,
FATHER_IN.FATHER_INFO,
fn.full_name FATHER_name,
sn.full_name spouse_name
FROM PER_PERSONS PP,
PER_PERSON_NAMES_F N,
--PER_PHONES PH,
PER_RELIGIONS R,
PER_ADDRESSES_F AD,
PER_ALL_PEOPLE_F_V AL,
PER_ADDRESSES_F
MAIL,
HR_ORGANIZATION_UNITS_F_TL B,
HR_ORGANIZATION_UNITS_F_TL BB,
PER_NATIONAL_IDENTIFIERS NI,
PER_PERIODS_OF_SERVICE PSER,
PER_PERSON_ADDR_USAGES_F AU,
PER_PERSON_ADDR_USAGES_F AUMAIL,
PER_ALL_ASSIGNMENTS_M M,
PER_PEOPLE_LEGISLATIVE_F_V P,
PER_EMAIL_ADDRESSES_V E,
PER_GRADES_F G,
HR_ORGANIZATION_UNITS O,
(SELECT SUP.PERSON_ID, SN.FULL_NAME MANAGER_NAME
FROM PER_ASSIGNMENT_SUPERVISORS_F
SUP, PER_PERSON_NAMES_F SN
WHERE SUP.MANAGER_TYPE
= 'LINE_MANAGER'
AND SUP.MANAGER_ID = SN.PERSON_ID
AND SN.NAME_TYPE = 'GLOBAL') SNM,
(SELECT FF.PERSON_ID, FF.PEI_INFORMATION2 MOTHER_INFO
FROM PER_PEOPLE_EXTRA_INFO_F FF
WHERE PEI_INFORMATION1 = 'Mother'
AND PEI_INFORMATION_CATEGORY = 'SG_CONTACTS') MOTHER_IN,
(SELECT FF.PERSON_ID, FF.PEI_INFORMATION2 FATHER_INFO
FROM
PER_PEOPLE_EXTRA_INFO_F FF
WHERE PEI_INFORMATION1 = 'Mother'
AND PEI_INFORMATION_CATEGORY = 'SG_CONTACTS') FATHER_IN,
(SELECT p.FULL_NAME, r.PERSON_ID
FROM PER_ALL_PEOPLE_F f,
PER_PERSON_NAMES_F p,
PER_CONTACT_RELSHIPS_F r
WHERE p.person_id
= f.person_id
AND r.CONTACT_PERSON_ID = p.person_id
AND p.NAME_TYPE = 'GLOBAL'
AND CONTACT_TYPE = 'IN_FR') fn,
(SELECT p.FULL_NAME, r.PERSON_ID
FROM PER_ALL_PEOPLE_F f,
PER_PERSON_NAMES_F p,
PER_CONTACT_RELSHIPS_F r
WHERE p.person_id
= f.person_id
AND r.CONTACT_PERSON_ID = p.person_id
AND p.NAME_TYPE = 'GLOBAL'
AND CONTACT_TYPE = 'DP') sn,
PER_PASSPORTS PASS
WHERE --M.PERSON_ID=
300000007491461 --300000007491461
--N.PERSON_NAME_ID =
300000007491465
--AND AD.ADDRESS_ID =
300000007491474
N.NAME_TYPE = 'GLOBAL'
AND M.PRIMARY_FLAG = 'Y'
AND ASSIGNMENT_TYPE IN ('E', 'C')
--and ph.PHONE_TYPE ='W1'
AND SYSDATE BETWEEN M.EFFECTIVE_START_DATE AND M.EFFECTIVE_END_DATE
AND M.BUSINESS_UNIT_ID = B.ORGANIZATION_ID
AND M.ESTABLISHMENT_ID = BB.ORGANIZATION_ID
AND M.PERSON_ID = P.PERSON_ID
AND M.PERSON_ID = NI.PERSON_ID
AND M.PERSON_ID = AL.PERSON_ID
AND M.PERSON_ID = MOTHER_IN.PERSON_ID(+)
AND M.PERSON_ID = FATHER_IN.PERSON_ID(+)
AND M.PERSON_ID = fn.PERSON_ID(+)
AND M.PERSON_ID = sn.PERSON_ID(+)
AND M.PERSON_ID = PSER.PERSON_ID
AND M.PERSON_ID = SNM.PERSON_ID(+)
AND M.PERSON_ID = N.PERSON_ID
AND M.PERSON_ID = PASS.PERSON_ID(+)
AND M.PERSON_ID = R.PERSON_ID
AND PP.PERSON_ID = P.PERSON_ID
--AND
PH.PERSON_ID=P.PERSON_ID
AND M.PERSON_ID = E.PERSON_ID
AND M.PERSON_ID = AU.PERSON_ID
AND M.ASS_ATTRIBUTE_NUMBER1 = :P_EMP_ID
AND M.PERSON_ID = AUMAIL.PERSON_ID
AND M.ORGANIZATION_ID = O.ORGANIZATION_ID
AND AU.ADDRESS_ID = AD.ADDRESS_ID(+)
AND AUMAIL.ADDRESS_ID = MAIL.ADDRESS_ID(+)
AND AUMAIL.ADDRESS_TYPE = 'MAIL'
AND AU.ADDRESS_TYPE = 'HOME'
AND M.GRADE_ID = G.GRADE_ID
--AND O.NAME ='Information
Technology & Services'
ORDER BY M.ASSIGNMENT_NAME
Query of Degree
/* Formatted on 11/19/2020
12:58:52 PM (QP5 v5.215.12089.38647) */
SELECT CTP.CONTENT_TYPE_NAME,
CTI.NAME AS CONTENT_ITEM_NAME,
HPI.ITEM_TEXT240_1 SPECIFICAION,
HPI.ITEM_TEXT240_6 GRADE,
HPI.ITEM_TEXT2000_1 INSTITUTE,
HPI.ITEM_TEXT30_8 LEVEL_,
HPI.ITEM_NUMBER_8 COM_YEAR,
HPB.PERSON_ID
FROM HRT_PROFILES_B HPB,
HRT_PROFILE_ITEMS HPI,
HRT_CONTENT_TYPES_TL CTP,
HRT_CONTENT_ITEMS_TL CTI
WHERE HPB.PROFILE_ID
= HPI.PROFILE_ID
AND HPI.CONTENT_TYPE_ID = CTP.CONTENT_TYPE_ID(+)
AND HPI.CONTENT_ITEM_ID = CTI.CONTENT_ITEM_ID(+)
--AND HPB.PERSON_ID =
300000007499130
AND CONTENT_TYPE_NAME = 'Degrees'
ORDER BY HPI.ITEM_NUMBER_8 DESC
Referance
HCM Detail documentation
https://docs.oracle.com/en/cloud/saas/human-resources/20b/faigh/person.html#FAIGH1115189
hcm table structure
https://docs.oracle.com/en/cloud/saas/human-resources/20c/oedmh/overview.html#overview
https://docs.oracle.com/en/cloud/saas/human-resources/20b/oedmh/global-human-resources.html#tables
SOME COMMONLY USED QUERIES IN ORACLE HCM CLOUD
Fusion Middleware Data Modeling Guide for Oracle Business
Intelligence Publisher
Fusion Middleware Report Designer's Guide for Oracle
Business Intelligence Publisher
Fusion Middleware Report Designer's Guide for Oracle
Business Intelligence Publisher
image
http://oraclemasterminds.blogspot.com/2016/09/advanced-rtf-template-image-chart-and.html
https://oraclecloudhcmtechfunc.blogspot.com/2019/02/how-to-get-profile-images-in-otbi-report.html
https://emqu-test.fa.ap1.oraclecloud.com/hcmUI/personImage?personId=300000007499130
http://www.ebiztechnics.net/2017/04/oracle-bi-publisher-reports-generating-dynamic-images.html
https://stackoverflow.com/questions/40608525/blob-image-at-report-header
Customizing OTBI Enterprise Reports and Dashboards