View: OKC_KOL_STD_ART_LIB_V

View Text

SELECT ART.ARTICLE_ID ID
, ART.ARTICLE_TITLE NAME
, TM.MEANING SUBJECT
, ART.ARTICLE_TYPE SBT_CODE
, ARTV.ARTICLE_VERSION_NUMBER SAE_OBJECT_VERSION_NUMBER
, ARTV.ARTICLE_VERSION_NUMBER SAV_RELEASE
, ARTV.START_DATE
, ARTV.END_DATE
, ARTV.ARTICLE_DESCRIPTION SHORT_DESCRIPTION
, ARTV.ARTICLE_VERSION_NUMBER SAV_OBJECT_VERSION_NUMBER
, ART.ARTICLE_INTENT ARTICLE_SET
, ART.ARTICLE_TYPE SAT_CODE
, ARTV.ARTICLE_VERSION_NUMBER SAM_OBJECT_VERSION_NUMBER
, ARTV.ARTICLE_TEXT TEXT
, DBMS_LOB.SUBSTR(ARTV.ARTICLE_TEXT
, 240)||' ....' SHORT_TEXT
, OKC_STD_ARTICLE_PUB.USED_IN_CONTRACTS(ARTV.ARTICLE_ID
, ARTV.ARTICLE_VERSION_NUMBER)
, OKC_STD_ARTICLE_PUB.LATEST_OR_FUTURE_RELEASE(ARTV.ARTICLE_ID
, ARTV.ARTICLE_VERSION_NUMBER
, ARTV.START_DATE)
FROM OKC_ARTICLES_ALL ART
, OKC_ARTICLE_VERSIONS ARTV
, FND_LOOKUPS SM
, FND_LOOKUPS TM
, FND_LOOKUPS DSM
, FND_LOOKUPS PM
, FND_LOOKUPS IM
WHERE ART.STANDARD_YN = 'Y'
AND ART.ARTICLE_ID = ARTV.ARTICLE_ID
AND TM.LOOKUP_TYPE ='OKC_SUBJECT'
AND SM.LOOKUP_TYPE ='OKC_ARTICLE_STATUS'
AND DSM.LOOKUP_TYPE(+) ='OKC_ARTICLE_SECTION'
AND PM.LOOKUP_TYPE ='OKC_ARTICLE_PROVISION'
AND IM.LOOKUP_TYPE ='OKC_ARTICLE_INTENT'
AND TM.LOOKUP_CODE = ART.ARTICLE_TYPE
AND SM.LOOKUP_CODE = DECODE(ARTV.ARTICLE_STATUS
, 'APPROVED'
, DECODE(GREATEST(NVL(TRUNC(END_DATE)
, TRUNC(SYSDATE))+1
, TRUNC(SYSDATE))
, TRUNC(SYSDATE)
, 'EXPIRED'
, ARTV.ARTICLE_STATUS)
, ARTV.ARTICLE_STATUS)
AND DSM.LOOKUP_CODE(+) = ARTV.DEFAULT_SECTION
AND PM.LOOKUP_CODE = ARTV.PROVISION_YN
AND IM.LOOKUP_CODE = ART.ARTICLE_INTENT
AND ARTV.GLOBAL_YN='Y'

Columns