how to increase session timeout in Oracle apps r12



Find Profile name ICX:Session Timeout


Default : None
Recommendation : 30 (minutes)











 Question: pick release process could not allocate of item  for order as there was insufficient quantity available or inventory allocations have failed, and the line has been back ordered.
Oracle EBS Apps R12


Answer: 

Please confirm whether the inventory period is currently accessible for the specified dates of the order. If it is not, kindly proceed to initiate the opening of the inventory period and conduct a thorough examination.


SELECT SEVERITY, STATUS, A.*
FROM WSH_EXCEPTIONS A
WHERE BATCH_ID = 6393351




 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.

Link to view as Video

Credit

S.M. Assaduzzaman Sarker | ITS Department | STANDARD GROUP

Contents

Tables. 3

HR_ORGANIZATION_UNITS_F_TL. 3

HR_ORGANIZATION_UNITS. 3

PER_LOCATIONS. 3

HR_ALL_POSITIONS_F_TL. 4

PER_ALL_PEOPLE_F. 4

PER_PEOPLE_EXTRA_INFO_F. 4

PER_ASSIGNMENT_SUPERVISORS_F. 4

PER_GRADES_F. 5

PER_IMAGES. 5

PER_PERSONS. 5

PER_PERSON_NAMES_F. 5

PER_PEOPLE_LEGISLATIVE_F_V.. 5

PER_PHONES. 6

PER_RELIGIONS. 6

PER_ALL_PEOPLE_F_V.. 6

PER_ASSIGNMENT_SUPERVISORS_F. 6

PER_ALL_PEOPLE_F_V.. 6

PER_ADDRESSES_F. 6

PER_PAYROLL_STATUTORY_UNITS. 7

PER_PERSON_ADDR_USAGES_F. 7

HCM_EXTENDED_LOOKUP_CODES_B. 7

PER_DEPARTMENTS. 8

PER_EMAIL_ADDRESSES_V.. 8

PER_JOBS. 8

CMP_SALARY. 8

PER_NATIONAL_IDENTIFIERS. 8

PER_ASSIGNMENTS_F. 8

PER_CONTACT_RELSHIPS_F. 9

PER_CONTRACTS_F. 9

PER_LOCATION_EXTRA_INFO_F_. 9

PER_LOCATIONS_. 10

PER_LOCATION_EXTRA_INFO_F. 10

PER_LOCATION_DETAILS_F_TL. 10

PER_LOCATION_DETAILS_F. 10

PER_CONTACT_RELSHIPS_F. 11

PER_CONTRACTS_F. 11

PER_CITIZENSHIPS. 11

PER_PASSPORTS. 11

Queries. 12

Some common query. 12

Employee Profile Query: 15

Query of Degree. 18

Referance. 19

 

 

 

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

https://docs.oracle.com/en/cloud/saas/human-resources/20c/oedmh/global-payroll.html#payallpayrollsf-22193

https://docs.oracle.com/en/cloud/saas/human-resources/20b/oedmh/global-human-resources.html#global-human-resources

SOME COMMONLY USED QUERIES IN ORACLE HCM CLOUD

https://www.apps2fusion.com/oracle-fusion-online-training/fusion-applications/fusion-payroll/2343-some-commonly-used-queries-in-oracle-hcm-cloud

Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher

https://docs.oracle.com/middleware/12211/bip/BIPDM/GUID-9A9233A9-1D48-4D50-AB00-7FDD29EB0E23.htm#BIPDM534

Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher

https://docs.oracle.com/middleware/12211/bip/BIPRD/GUID-58D7E662-146A-44AA-86F9-111E2C6DB3A5.htm#BIPRD2395

Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher

https://docs.oracle.com/middleware/12211/bip/BIPRD/GUID-58D7E662-146A-44AA-86F9-111E2C6DB3A5.htm#BIPRD2392

 

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

https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/otbi_ent/rel10/analyses_dashboards/analyses_dashboards.html#section2