Contract Party Resources having Contract Admin Role in Oracel Fusion

If you are looking for to get contract party resources having a contact role please refer to the below query to get those details

Reference:

Contract Party Resources having Contract Admin Role (Doc ID 2869009.1)

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

SELECT ch.contract_number,
ch.major_version,
ch.start_Date,
ch.end_Date,
ch.creation_Date,
ch.sts_code,
ch.currency_code,
ch.date_approved,
cc.CRO_CODE Party_contact_role,
hp.party_name contract_admin,
horg.name contract_org,
ch.ORG_ID,
PAPF.PERSON_NUMBER,
(
SELECT PHONE_NUMBER
FROM PER_PHONES PP
WHERE PP.PERSON_ID = PAPF.PERSON_ID
AND PP.PHONE_TYPE = 'WF'
) FAX,
(
SELECT PHONE_NUMBER
FROM PER_PHONES PP
WHERE PP.PERSON_ID = PAPF.PERSON_ID
AND PP.PHONE_TYPE = 'W1'
) PHONE
FROM OKC_K_HEADERS_ALL_B ch,
OKC_CONTACTS cc,
hz_parties hp,
hr_organization_units horg,
PER_ALL_PEOPLE_F PAPF
WHERE hp.party_id = cc.OBJECT1_ID1
AND cc.JTOT_OBJECT1_CODE = 'OKX_RESOURCE'
AND cc.DNZ_CHR_ID = ch.id
AND ch.major_version = cc.major_version
-- AND cc.CRO_CODE = 'CONTRACT_ADMIN'
AND ch.OWNING_ORG_ID = horg.organization_id
AND contract_number = 'Professional Contract-99'
AND PAPF.PERSON_ID = HP.ORIG_SYSTEM_REFERENCE
AND TRUNC(SYSDATE) BETWEEN NVL(papf.effective_start_date, SYSDATE)
AND NVL(papf.effective_end_date, SYSDATE)

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