how to increase session timeout in Oracle apps r12
1. login in myaccount.microsoft.com
like abc.d@standard-group.com
2. go to Devices option
3. Click the Downarrow option
4. View Bitlocker Keys option will be visible after a while
5. Click View Bitlocker Keys
6. Click Show Recovery Key
7. Copy the Recovery Key
8. Click the drive to recover & paste Recovery Key into Recovery Key option
View https://www.youtube.com/watch?v=J1UMvVuyQAU
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
PER_ASSIGNMENT_SUPERVISORS_F. 4
PER_PEOPLE_LEGISLATIVE_F_V.. 5
PER_ASSIGNMENT_SUPERVISORS_F. 6
PER_PAYROLL_STATUTORY_UNITS. 7
HCM_EXTENDED_LOOKUP_CODES_B. 7
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 |
select NAME from HR_ORGANIZATION_UNITS WHERE ORGANIZATION_ID
= 300000001955046
300000001955001 (STANDARD GROUP)
NAME |
Standard Group LE |
select INTERNAL_LOCATION_CODE from PER_LOCATIONS WHERE
LOCATION_ID = 300000008812728
INTERNAL_LOCATION_CODE |
Standard Group |
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 |
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) |
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 |
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) |
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 |
SELECT IMAGE_ID,IMAGE FROM PER_IMAGES WHERE PERSON_ID
=300000007491461
IMAGE_ID |
IMAGE |
100000004602850 |
AFLAKJFLKAJFLKACNLKLLJLAKJFLKAJLK….. |
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 |
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 |
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 |
SELECT PHONE_NUMBER , PHONE_TYPE FROM PER_PHONES WHERE
PERSON_ID =300000007491461
PHONE_TYPE |
PHONE_NUMBER |
W1 |
01716591619 |
SELECT RELIGION FROM
PER_RELIGIONS WHERE PERSON_ID =300000007491461
RELIGION |
MUSLIM |
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 |
SELECT *
FROM PER_ASSIGNMENT_SUPERVISORS_F
--WHERE
ADDRESS_ID = 300000007491474
ADDRESS & OTHER INFO
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 |
select ORGANIZATION_ID, name from
PER_PAYROLL_STATUTORY_UNITS
ORGANIZATION_ID |
NAME |
300000002817481 |
Test LE |
300000001955001 |
Standard Group |
300000001955001 |
Standard Group |
300000002817481 |
Test LE |
SELECT
ADDRESS_ID,ADDRESS_TYPE
FROM
PER_PERSON_ADDR_USAGES_F
WHERE
PERSON_ID = 300000007491461
ADDRESS_ID |
ADDRESS_TYPE |
300000007491474 |
HOME |
300000007491476 |
MAIL |
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)
SELECT NAME,
ORGANIZATION_ID FROM PER_DEPARTMENTS
WHERE ORGANIZATION_ID = 300000001955938
ORGANIZATION_ID |
NAME |
300000001955938 |
Information Technology & Services |
|
|
SELECT EMAIL_ADDRESS FROM PER_EMAIL_ADDRESSES_V WHERE PERSON_ID
=300000007491461
EMAIL_ADDRESS |
assaduzzaman.s@standard-group.com |
SELECT NAME,JOB_FAMILY_ID FROM PER_JOBS WHERE JOB_ID=300000008328813
NAME |
JOB_FAMILY_ID |
System Analyst |
300000006583496 |
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 |
SELECT NATIONAL_IDENTIFIER_NUMBER
FROM PER_NATIONAL_IDENTIFIERS
WHERE PERSON_ID =300000007491461
NATIONAL_IDENTIFIER_NUMBER |
2696405625007 |
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 |
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 |
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 |
NO DATA
NO DATA
May be valid data
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 |
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 |
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 |
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 |
select CITIZENSHIP_ID,
LEGISLATION_CODE from per_citizenships
where PERSON_ID = 300000007499130
CITIZENSHIP_ID |
LEGISLATION_CODE |
300000009236083 |
BD |
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 |
|||||||
|
|
|
|
|
|
|
|
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 |
/* 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
/* 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
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