View: OKX_RESOURCES_V

Product:	OKX - Contracts Integration
Description:	Information about resources
Implementation/DBA Data:	APPS.OKX_RESOURCES_V

View Text

SELECT RSC.RESOURCE_ID ID1
, '#' ID2
, RSC.RESOURCE_NUMBER RESOURCE_NUMBER
, RSC.CATEGORY RESOURCE_TYPE
, C.LAST_NAME NAME
, C.FIRST_NAME DESCRIPTION
, RSC.SOURCE_ID SOURCE_ID
, RSC.ADDRESS_ID ADDRESS_ID
, RSC.CONTACT_ID CONTACT_ID
, RSC.SUPPORT_SITE_ID SUPPORT_SITE_ID
, S.ORG_ID ORG_ID
, RSC.START_DATE_ACTIVE START_DATE_ACTIVE
, RSC.END_DATE_ACTIVE END_DATE_ACTIVE
, RSC.USER_ID USER_ID
, NULL B_STATUS
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(RSC.START_DATE_ACTIVE
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(RSC.END_DATE_ACTIVE
, TRUNC(SYSDATE)))
, 1
, 'I'
, DECODE(SIGN(NVL(C.INACTIVE_DATE - 1
, SYSDATE) - SYSDATE)
, -1
, 'I'
, 'A'))) STATUS
, S.EMAIL_ADDRESS EMAIL_ADDRESS
FROM JTF_RS_RESOURCE_EXTNS RSC
, PO_VENDOR_SITES S
, PO_VENDOR_CONTACTS C
WHERE RSC.CATEGORY = 'SUPPLIER_CONTACT'
AND C.VENDOR_CONTACT_ID = RSC.SOURCE_ID
AND S.VENDOR_SITE_ID = C.VENDOR_SITE_ID UNION ALL SELECT RSC.RESOURCE_ID ID1
, '#' ID2
, RSC.RESOURCE_NUMBER RESOURCE_NUMBER
, RSC.CATEGORY RESOURCE_TYPE
, EMP.FULL_NAME NAME
, U.USER_NAME DESCRIPTION
, RSC.SOURCE_ID SOURCE_ID
, RSC.ADDRESS_ID ADDRESS_ID
, RSC.CONTACT_ID CONTACT_ID
, RSC.SUPPORT_SITE_ID SUPPORT_SITE_ID
, -99 ORG_ID
, RSC.START_DATE_ACTIVE START_DATE_ACTIVE
, RSC.END_DATE_ACTIVE END_DATE_ACTIVE
, RSC.USER_ID USER_ID
, NULL B_STATUS
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(RSC.START_DATE_ACTIVE
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(RSC.END_DATE_ACTIVE
, TRUNC(SYSDATE)))
, 1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(EMP.EFFECTIVE_START_DATE
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(EMP.EFFECTIVE_END_DATE
, TRUNC(SYSDATE)))
, 1
, 'I'
, 'A')))) STATUS
, EMP.EMAIL_ADDRESS EMAIL_ADDRESS
FROM JTF_RS_RESOURCE_EXTNS RSC
, FND_USER U
, PER_ALL_PEOPLE_F EMP
WHERE RSC.CATEGORY = 'EMPLOYEE'
AND EMP.PERSON_ID = RSC.SOURCE_ID
AND U.USER_ID = RSC.USER_ID
AND EMP.EFFECTIVE_END_DATE >= TRUNC(SYSDATE) UNION ALL SELECT RSC.RESOURCE_ID ID1
, '#' ID2
, RSC.RESOURCE_NUMBER RESOURCE_NUMBER
, RSC.CATEGORY RESOURCE_TYPE
, PARTY.PARTY_NAME NAME
, U.USER_NAME DESCRIPTION
, RSC.SOURCE_ID SOURCE_ID
, RSC.ADDRESS_ID ADDRESS_ID
, RSC.CONTACT_ID CONTACT_ID
, RSC.SUPPORT_SITE_ID SUPPORT_SITE_ID
, -99 ORG_ID
, RSC.START_DATE_ACTIVE START_DATE_ACTIVE
, RSC.END_DATE_ACTIVE END_DATE_ACTIVE
, RSC.USER_ID USER_ID
, PARTY.STATUS B_STATUS
, DECODE(PARTY.STATUS
, 'A'
, 'A'
, 'I') STATUS
, PARTY.EMAIL_ADDRESS EMAIL_ADDRESS
FROM JTF_RS_RESOURCE_EXTNS RSC
, FND_USER U
, HZ_PARTIES PARTY
WHERE RSC.CATEGORY IN ( 'PARTY'
, 'PARTNER')
AND PARTY.PARTY_ID = RSC.SOURCE_ID
AND U.USER_ID = RSC.USER_ID UNION ALL SELECT RSC.RESOURCE_ID ID1
, '#' ID2
, RSC.RESOURCE_NUMBER RESOURCE_NUMBER
, RSC.CATEGORY RESOURCE_TYPE
, SRP.NAME NAME
, U.USER_NAME DESCRIPTION
, RSC.SOURCE_ID SOURCE_ID
, RSC.ADDRESS_ID ADDRESS_ID
, RSC.CONTACT_ID CONTACT_ID
, RSC.SUPPORT_SITE_ID SUPPORT_SITE_ID
, SRP.ORG_ID ORG_ID
, RSC.START_DATE_ACTIVE START_DATE_ACTIVE
, RSC.END_DATE_ACTIVE END_DATE_ACTIVE
, RSC.USER_ID USER_ID
, NULL B_STATUS
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(RSC.START_DATE_ACTIVE
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(RSC.END_DATE_ACTIVE
, TRUNC(SYSDATE)))
, 1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(SRP.START_DATE_ACTIVE
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE)- NVL(SRP.END_DATE_ACTIVE
, TRUNC(SYSDATE)))
, 1
, 'I'
, 'A')))) STATUS
, SRP.EMAIL_ADDRESS EMAIL_ADDRESS
FROM JTF_RS_RESOURCE_EXTNS RSC
, FND_USER U
, JTF_RS_SALESREPS_MO_V SRP
WHERE RSC.CATEGORY = 'OTHER'
AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
AND U.USER_ID = RSC.USER_ID

Columns