eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OKX_RESOURCES_V

Source

View Text - Preformatted


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 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

View Text - HTML Formatted

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 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