Query to get Purchase Order and requisition in Oracle Fusion

SELECT
POH.PO_HEADER_ID,
POH.SEGMENT1  ,
PRHA.REQUISITION_NUMBER
FROM
PO_HEADERS_ALL POH,
PO_DISTRIBUTIONS_ALL PDA ,
POR_REQ_DISTRIBUTIONS_ALL PRDA ,
POR_REQUISITION_LINES_ALL PRLA ,
POR_REQUISITION_HEADERS_ALL PRHA
WHERE 1=1
AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID
AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID

AND PRHA.REQUISITION_NUMBER = <Requsition_number>

Rest API to Terminate a worker in Oracle Fusion

Method: Post

Header 

Content-Type : application/vnd.oracle.adf.action+json

Rest API: https://<Host>/hcmRestApi/resources/11.13.18.05/workers/{workersUniqID}/child/workRelationships/{PeriodOfServiceId}

Sample Payload 

{

"actionCode": "RESIGNATION",

"terminationDate": "2022-03-01",

"reasonCode":"RESIGN_PERSONAL",

"notificationDate":"2022-03-01",

"recommendedForRehire":"ORA_NS"

}

Response

{

    "result": "true"

}

Status

200 OK

query to get legal entity and business Unit name

SELECT

hou.name BU_NAME,

hou.SHORT_CODE ,

le.NAME Legal_ENtity,

led.NAME Ledger_Name

FROM

hr_operating_units hou,

xla_gl_ledgers led,

Xle_entity_profiles le

WHERE

hou.default_legal_context_id = le.legal_entity_id

AND led.ledger_id = hou.set_of_books_id

Query to get Legal Entity in Oracle Fusion

select 
LEGAL_ENTITY_ID,
LEGAL_ENTITY_IDENTIFIER,
NAME
from xle_entity_profiles

Sample REST Payload to create employee in Oracle Fusion

RESTAPI WSDL for oracle Fusion

 WSDL: https://<Hostname>/hcmRestApi/resources/11.13.18.05/emps

Sample payload to create Employee in oracle Fusion

{

    "Salutation": "MR.",

    "LegalEntityId": 3000000099999988,

    "FirstName": "FirstName",

    "MiddleName": "NA",

    "LastName": "LastName",

    "DisplayName": "FirstName LastName",

    "WorkEmail": "FirstName.LastName@test.com",

    "CitizenshipLegislationCode": "US",

    "CitizenshipStatus": "A",

    "DateOfBirth": "1990-09-09",

    "Gender": "M",

    "UserName": "USERNAME",

    "assignments": [

        {

            "AssignmentName": "TEST_Assignment_1",

            "BusinessUnitId": 300000009999,

            "ActionCode": "HIRE",

            "ActionReasonCode": "NEWHIRE",

            "AssignmentStatus": "ACTIVE",

            "WorkTaxAddressId": null,

            "ManagerType": "LINE_MANAGER"

        }

    ]

}

Query to get current logged in user in Oracle Fusion

select  FND_GLOBAL.USER_GUID, FND_GLOBAL.USER_NAME from dual

Query to get Role and Data access Assigned to user in Oracle Fusion

SELECT
 'DATA ACCESS SET',
 GL.NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.GL_ACCESS_SETS GL,
 FUSION.PER_USERS PU
WHERE
 GL.ACCESS_SET_ID = ROLE.ACCESS_SET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'BUSINESS UNIT',
 BU.BU_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_ALL_BUSINESS_UNITS_V BU,
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.PER_USERS PU
WHERE
 ROLE.ORG_ID = BU.BU_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'LEDGERS',
 LED.NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.GL_LEDGERS LED,
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.PER_USERS PU
WHERE
 ROLE.LEDGER_ID = LED.LEDGER_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'ASSET BOOK',
 BOOK.BOOK_TYPE_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FA_BOOK_CONTROLS BOOK,
 FUSION.PER_USERS PU
WHERE
 BOOK.BOOK_CONTROL_ID = ROLE.BOOK_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'INTERCOMPANY ORGANIZATION',
 INTERCO.INTERCO_ORG_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FUN_INTERCO_ORGANIZATIONS INTERCO,
 FUSION.PER_USERS PU
WHERE
 INTERCO.INTERCO_ORG_ID = ROLE.INTERCO_ORG_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'COST ORGANIZATION',
 COST.COST_ORG_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.CST_COST_ORGS_V COST,
 FUSION.PER_USERS PU
WHERE
 COST.COST_ORG_ID = ROLE.CST_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'MANUFACTURING PLANT',
 MFG.DEF_SUPPLY_SUBINV,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.RCS_MFG_PARAMETERS MFG,
 FUSION.PER_USERS PU
WHERE
 MFG.ORGANIZATION_ID = ROLE.MFG_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'CONTROL BUDGET',
 BUDGET.NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.XCC_CONTROL_BUDGETS BUDGET,
 FUSION.PER_USERS PU
WHERE
 BUDGET.CONTROL_BUDGET_ID = ROLE.CONTROL_BUDGET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'REFERENCE DATA SET',
 ST.SET_NAME,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.FND_SETID_SETS_VL ST,
 FUSION.PER_USERS PU
WHERE
 ST.SET_ID = ROLE.SET_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'INVENTORY ORGANIZATION',
 INV.ORGANIZATION_CODE,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.INV_ORG_PARAMETERS INV,
 FUSION.PER_USERS PU
WHERE
 INV.ORGANIZATION_ID = ROLE.INV_ORGANIZATION_ID
 AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
 'PROJECT ORGANIZATION CLASSIFICATION',
 HR.CLASSIFICATION_CODE,
 ROLE.ROLE_NAME,
 PU.USERNAME,
 ROLE.CREATION_DATE,
 ROLE.LAST_UPDATE_DATE,
 ROLE.LAST_UPDATED_BY
FROM
 FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
 FUSION.HR_ORG_UNIT_CLASSIFICATIONS_F HR,
 FUSION.PER_USERS PU
WHERE
 HR.ORG_UNIT_CLASSIFICATION_ID = ROLE.ORG_ID
 AND PU.USER_GUID = ROLE.USER_GUID