Oracle Application

Thursday, 28 January 2021

Oracle Fusion CRM - Custom Object - Find Entity - SOAP Envelop - Request and Response Sample payload

--CRM Custom Object WSDL Link--

https://servername/crmService/CustomReferenceService?WSDL

--Find Entity--SOAP Request

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/custom/extnService/types/" xmlns:typ1="http://xmlns.oracle.com/adf/svc/types/">
<soapenv:Header/>
<soapenv:Body>
<typ:findEntity>
<typ:findCriteria>
<typ1:fetchStart>0</typ1:fetchStart>
<typ1:fetchSize>500</typ1:fetchSize>
</typ:findCriteria>
<typ:findControl>
<typ1:retrieveAllTranslations>false</typ1:retrieveAllTranslations>
</typ:findControl>
<typ:objectName>Test_c</typ:objectName>
</typ:findEntity>
</soapenv:Body>
</soapenv:Envelope>

--SOAP Response

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing" xmlns:typ="http://xmlns.oracle.com/apps/custom/extnService/types/">
<env:Header>
<wsa:Action>http://xmlns.oracle.com/apps/custom/extnService//CustomReferenceService/findEntityResponse</wsa:Action>
<wsa:MessageID>urn:uuid:0607830a-c1b5-42a4-aa70-e849b2860957</wsa:MessageID>
</env:Header>
<env:Body>
<ns0:findEntityResponse xmlns:ns0="http://xmlns.oracle.com/apps/custom/extnService/types/">
<ns2:result xsi:type="ns1:Test_c" xmlns:ns2="http://xmlns.oracle.com/apps/custom/extnService/types/" xmlns:ns1="http://xmlns.oracle.com/apps/custom" xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ns1:Id>300000012023030</ns1:Id>
<ns1:RecordName>754</ns1:RecordName>
<ns1:CreatedBy>test.scm</ns1:CreatedBy>
<ns1:CreationDate>2021-01-18T06:33:54.851Z</ns1:CreationDate>
<ns1:LastUpdatedBy>test.scm</ns1:LastUpdatedBy>
<ns1:LastUpdateDate>2021-01-18T06:34:09.485Z</ns1:LastUpdateDate>
<ns1:ObjectVersionNumber>1</ns1:ObjectVersionNumber>
<ns1:RecordNumber>2005</ns1:RecordNumber>
<ns1:LastUpdateLogin xsi:nil="true"/>
<ns1:UserLastUpdateDate xsi:nil="true"/>
<ns1:CurrencyCode>USD</ns1:CurrencyCode>
<ns1:CurcyConvRateType>Corporate</ns1:CurcyConvRateType>
<ns1:CorpCurrencyCode>USD</ns1:CorpCurrencyCode>
<ns1:OraZcxOwner_Id_c>-1</ns1:OraZcxOwner_Id_c>
<ns1:OraZcxOwner_c>-1</ns1:OraZcxOwner_c>
<ns1:Name_c>Test123</ns1:Name_c>
<ns1:SelectedRow xsi:nil="true"/>
</ns2:result>
</ns0:findEntityResponse>
</env:Body>
</env:Envelope>

Oracle Fusion CRM - Custom Object - Create Entity - SOAP Envelop - Request and Response Sample payload

--CRM Custom Object WSDL Link--

https://servername/crmService/CustomReferenceService?WSDL

--CreateEntity: SOAP Request--

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://xmlns.oracle.com/apps/custom/extnService/types/" xmlns:ns1="http://xmlns.oracle.com/apps/custom">
<env:Body>
<ns2:createEntity>
<ns2:object xsi:type="ns1:Test_c" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ns1:RecordName>Test3</ns1:RecordName>
<ns1:Name_c>Test3</ns1:Name_c>
</ns2:object>
<ns2:objectName>Test_c</ns2:objectName>
</ns2:createEntity>
</env:Body>
</env:Envelope>

--SOAP Response---

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing" xmlns:typ="http://xmlns.oracle.com/apps/custom/extnService/types/">
<env:Header>
<wsa:Action>http://xmlns.oracle.com/apps/custom/extnService//CustomReferenceService/createEntityResponse</wsa:Action>
<wsa:MessageID>urn:uuid:e6cec740-6908-4619-8e62-2a1093887970</wsa:MessageID>
</env:Header>
<env:Body>
<ns0:createEntityResponse xmlns:ns0="http://xmlns.oracle.com/apps/custom/extnService/types/">
<ns2:result xsi:type="ns1:Test_c" xmlns:ns2="http://xmlns.oracle.com/apps/custom/extnService/types/" xmlns:ns1="http://xmlns.oracle.com/apps/custom" xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ns1:Id>300000012171956</ns1:Id>
<ns1:RecordName>Test3</ns1:RecordName>
<ns1:CreatedBy>test.scm</ns1:CreatedBy>
<ns1:CreationDate>2021-01-28T09:34:30.0Z</ns1:CreationDate>
<ns1:LastUpdatedBy>test.scm</ns1:LastUpdatedBy>
<ns1:LastUpdateDate>2021-01-28T09:34:32.827Z</ns1:LastUpdateDate>
<ns1:ObjectVersionNumber>1</ns1:ObjectVersionNumber>
<ns1:RecordNumber>3013</ns1:RecordNumber>
<ns1:LastUpdateLogin xsi:nil="true"/>
<ns1:UserLastUpdateDate xsi:nil="true"/>
<ns1:CurrencyCode>USD</ns1:CurrencyCode>
<ns1:CurcyConvRateType>Corporate</ns1:CurcyConvRateType>
<ns1:CorpCurrencyCode>USD</ns1:CorpCurrencyCode>
<ns1:OraZcxOwner_Id_c>-1</ns1:OraZcxOwner_Id_c>
<ns1:OraZcxOwner_c>-1</ns1:OraZcxOwner_c>
<ns1:Name_c>Test3</ns1:Name_c>
<ns1:SelectedRow xsi:nil="true"/>
</ns2:result>
</ns0:createEntityResponse>
</env:Body>
</env:Envelope>

Saturday, 23 January 2021

Oracle Fusion - HCM - User and Manager Details Query

************************************************************************************************************************************

Seq Date Version Developer Requested By Comments

--------------------------------------------------------------------------------------------------------------

1 04-NOV-2020 1.0 Chetan Rajput Aniket Newly Developed

************************************************************************************************************************************

select

'KEY' AS "KEY",

ROWNUM SR_NO,

X.*

FROM

(

SELECT

PU.PERSON_ID EMP_PERSON_ID,

PU.USERNAME EMP_USERNAME,

ppnf.EFFECTIVE_START_DATE EMP_START_DATE,

ppnf.EFFECTIVE_END_DATE EMP_END_DATE,

ppnf.FULL_NAME EMP_FULL_NAME,

ppnf.TITLE,

ppnf.FIRST_NAME,

ppnf.LAST_NAME,

email.email_address USER_EMAIL,

PRDV.ROLE_NAME EMP_ROLE_NAME,

haouf.NAME EMP_RIG_NAME,

haouf.ORGANIZATION_ID EMP_RIG_ID,

PASF.MANAGER_ID,

T_MANAGER.USERNAME MANAGER_USERNAME,

T_MANAGER.PERSON_ID MANAGER_PERSON_ID,

T_MANAGER.ROLE_NAME MANAGER_ROLE_NAME,

T_MANAGER.RIG_NAME MANAGER_RIG_NAME,

T_MANAGER.RIG_ID MANAGER_RIG_ID,

T_MANAGER.EFFECTIVE_START_DATE MANAGER_START_DATE,

T_MANAGER.EFFECTIVE_END_DATE MANAGER_END_DATE,

T_MANAGER.FULL_NAME MANAGER_FULL_NAME

CASE

WHEN (TRUNC (PU.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

PU.LAST_UPDATE_DATE

WHEN (TRUNC (ppnf.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

ppnf.LAST_UPDATE_DATE

WHEN (TRUNC (FURDA.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

FURDA.LAST_UPDATE_DATE

WHEN (TRUNC (PUR.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

PUR.LAST_UPDATE_DATE

WHEN (TRUNC (PRDV.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

PRDV.LAST_UPDATE_DATE

WHEN (TRUNC (PASF.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

PASF.LAST_UPDATE_DATE

WHEN (TRUNC (T_MANAGER.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

T_MANAGER.LAST_UPDATE_DATE

END

AS LAST_UPDATE_DATE

from PER_USERS PU,

per_person_names_f ppnf,

FUSION.FUN_USER_ROLE_DATA_ASGNMNTS FURDA,

INV_ORG_PARAMETERS IOP,

hr_all_organization_units_f_vl haouf,

PER_USER_ROLES PUR,

PER_ROLES_DN_VL PRDV,

PER_ASSIGNMENT_SUPERVISORS_F_V PASF,

per_email_addresses email,

PER_ALL_PEOPLE_F B,

(select

PU.PERSON_ID,

PU.USERNAME,

ppnf.EFFECTIVE_START_DATE,

ppnf.EFFECTIVE_END_DATE,

ppnf.FULL_NAME,

PRDV.ROLE_NAME,

haouf.NAME RIG_NAME,

haouf.ORGANIZATION_ID RIG_ID

/*,

CASE

WHEN (TRUNC (PU.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

PU.LAST_UPDATE_DATE

WHEN (TRUNC (ppnf.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

ppnf.LAST_UPDATE_DATE

WHEN (TRUNC (FURDA.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

FURDA.LAST_UPDATE_DATE

WHEN (TRUNC (PUR.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

PUR.LAST_UPDATE_DATE

WHEN (TRUNC (PRDV.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

THEN

PRDV.LAST_UPDATE_DATE

END

AS LAST_UPDATE_DATE

from PER_USERS PU,

per_person_names_f ppnf,

FUSION.FUN_USER_ROLE_DATA_ASGNMNTS FURDA,

INV_ORG_PARAMETERS IOP,

hr_all_organization_units_f_vl haouf,

PER_USER_ROLES PUR,

PER_ROLES_DN_VL PRDV

where

1=1

AND PU.PERSON_ID=ppnf.PERSON_ID

and ppnf.NAME_TYPE <> 'GLOBAL'

AND trunc(sysdate) between ppnf.EFFECTIVE_START_DATE and NVL(ppnf.EFFECTIVE_END_DATE,sysdate)

AND FURDA.USER_GUID=PU.USER_GUID

AND FURDA.INV_ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND PUR.user_id=PU.user_id

AND PUR.role_id=PRDV.role_id

AND PRDV.ROLE_COMMON_NAME=FURDA.ROLE_NAME

AND PRDV.ROLE_NAME <> 'Employee Custom'

) T_MANAGER

where

1=1

AND PU.PERSON_ID=ppnf.PERSON_ID

and ppnf.NAME_TYPE <> 'GLOBAL'

AND trunc(sysdate) between ppnf.EFFECTIVE_START_DATE and NVL(ppnf.EFFECTIVE_END_DATE,sysdate)

AND FURDA.USER_GUID=PU.USER_GUID

AND FURDA.INV_ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND PUR.user_id=PU.user_id

AND PUR.role_id=PRDV.role_id

AND PRDV.ROLE_COMMON_NAME=FURDA.ROLE_NAME

AND PRDV.ROLE_NAME <> 'Employee Custom'

AND ppnf.PERSON_ID=PASF.person_id(+)

AND PASF.MANAGER_ID=T_MANAGER.PERSON_ID(+)

AND TRUNC (SYSDATE) BETWEEN TRUNC (b.EFFECTIVE_START_DATE)

AND TRUNC (b.EFFECTIVE_END_DATE)

AND b.person_id = ppnf.person_id

AND email.person_id(+) = b.person_id

AND email.email_address_id(+) = b.primary_email_id

AND TRUNC (SYSDATE) BETWEEN NVL (email.date_from(+), SYSDATE)

AND NVL (email.date_to(+), SYSDATE)

AND email.email_type = 'W1'

AND ( (TRUNC (PU.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

OR (TRUNC (ppnf.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

OR (TRUNC (FURDA.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

OR (TRUNC (PUR.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

OR (TRUNC (PRDV.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

OR (TRUNC (PASF.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

OR (TRUNC (T_MANAGER.LAST_UPDATE_DATE) BETWEEN :P_START_DT

AND :P_END_DT)

)

-- AND PU.USERNAME='5140'

ORDER BY PU.USERNAME