[Home] [Help]
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
Name |
ID1 |
ID2 |
RESOURCE_NUMBER |
RESOURCE_TYPE |
NAME |
DESCRIPTION |
SOURCE_ID |
ADDRESS_ID |
CONTACT_ID |
SUPPORT_SITE_ID |
ORG_ID |
START_DATE_ACTIVE |
END_DATE_ACTIVE |
USER_ID |
B_STATUS |
STATUS |
EMAIL_ADDRESS |