VIEW: APPS.OKC_ARTICLES_LOCAL_V

Source

View Text - Preformatted

SELECT art.article_title, art.article_number, art.article_type, tm.meaning, artv.article_version_number, 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) article_status, sm.meaning, artv.adoption_type, am.meaning, artv.article_text, artv.article_description, art.org_id, art.article_id, artv.article_version_id, art.article_intent, im.meaning, artv.provision_yn, pm.meaning, artv.start_date, artv.end_date, artv.default_section, dsm.meaning from okc_articles_all art, okc_article_versions artv, okc_lookups_v sm, okc_lookups_v tm, okc_lookups_v dsm, okc_lookups_v pm, okc_lookups_v im, okc_lookups_v am WHERE art.standard_yn = 'Y' and art.article_id = artv.article_id and artv.adoption_type in ('LOCAL','LOCALIZED') 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 am.lookup_type ='OKC_ARTICLE_ADOPTION_TYPE' 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 am.lookup_code = artv.adoption_type union all select art.article_title, art.article_number, art.article_type, tm.meaning, artv.article_version_number, decode(arta.adoption_type, 'AVAILABLE', decode( decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status), 'APPROVED', arta.adoption_status, 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'REJECTED', 'REJECTED' ), 'ADOPTED', decode( decode(arta.adoption_status, 'PENDING_APPROVAL', 'PENDING_APPROVAL', decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status) ), 'APPROVED', arta.adoption_status, 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'PENDING_APPROVAL', 'PENDING_APPROVAL' ) ) article_status, decode(arta.adoption_type, 'AVAILABLE', decode( decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status), 'APPROVED', decode(arta.adoption_status, null, null, sm.meaning), 'EXPIRED', sm.meaning, 'ON_HOLD', sm.meaning, 'REJECTED', sm.meaning ), 'ADOPTED', decode( decode(arta.adoption_status, 'PENDING_APPROVAL', 'PENDING_APPROVAL', decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status) ), 'APPROVED', sm.meaning, 'EXPIRED', sm.meaning, 'ON_HOLD', sm.meaning, 'PENDING_APPROVAL', sm.meaning ) ), arta.adoption_type, am.meaning, artv.article_text, artv.article_description, arta.local_org_id, art.article_id, arta.global_article_version_id, art.article_intent, im.meaning, artv.provision_yn, pm.meaning, artv.start_date, artv.end_date, artv.default_section, dsm.meaning from okc_articles_all art, okc_article_versions artv, okc_article_adoptions arta, okc_lookups_v sm, okc_lookups_v tm, okc_lookups_v dsm, okc_lookups_v pm, okc_lookups_v im, okc_lookups_v am where art.standard_yn = 'Y' and artv.article_version_id = arta.global_article_version_id and artv.article_id = art.article_id and arta.adoption_type in ('ADOPTED','AVAILABLE') 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 am.lookup_type ='OKC_ARTICLE_ADOPTION_TYPE' and tm.lookup_code(+) = art.article_type and sm.lookup_code = decode(arta.adoption_type, 'AVAILABLE', decode( decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status), 'APPROVED', nvl(arta.adoption_status,'DRAFT'), 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'REJECTED', 'REJECTED' ), 'ADOPTED', decode( decode(arta.adoption_status, 'PENDING_APPROVAL', 'PENDING_APPROVAL', decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status) ), 'APPROVED', arta.adoption_status, 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'PENDING_APPROVAL', 'PENDING_APPROVAL' ) ) and dsm.lookup_code(+) = artv.default_section and pm.lookup_code = artv.provision_yn and im.lookup_code = art.article_intent and am.lookup_code = arta.adoption_type

View Text - HTML Formatted

SELECT ART.ARTICLE_TITLE
, ART.ARTICLE_NUMBER
, ART.ARTICLE_TYPE
, TM.MEANING
, ARTV.ARTICLE_VERSION_NUMBER
, 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) ARTICLE_STATUS
, SM.MEANING
, ARTV.ADOPTION_TYPE
, AM.MEANING
, ARTV.ARTICLE_TEXT
, ARTV.ARTICLE_DESCRIPTION
, ART.ORG_ID
, ART.ARTICLE_ID
, ARTV.ARTICLE_VERSION_ID
, ART.ARTICLE_INTENT
, IM.MEANING
, ARTV.PROVISION_YN
, PM.MEANING
, ARTV.START_DATE
, ARTV.END_DATE
, ARTV.DEFAULT_SECTION
, DSM.MEANING
FROM OKC_ARTICLES_ALL ART
, OKC_ARTICLE_VERSIONS ARTV
, OKC_LOOKUPS_V SM
, OKC_LOOKUPS_V TM
, OKC_LOOKUPS_V DSM
, OKC_LOOKUPS_V PM
, OKC_LOOKUPS_V IM
, OKC_LOOKUPS_V AM
WHERE ART.STANDARD_YN = 'Y'
AND ART.ARTICLE_ID = ARTV.ARTICLE_ID
AND ARTV.ADOPTION_TYPE IN ('LOCAL'
, 'LOCALIZED')
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 AM.LOOKUP_TYPE ='OKC_ARTICLE_ADOPTION_TYPE'
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 AM.LOOKUP_CODE = ARTV.ADOPTION_TYPE UNION ALL SELECT ART.ARTICLE_TITLE
, ART.ARTICLE_NUMBER
, ART.ARTICLE_TYPE
, TM.MEANING
, ARTV.ARTICLE_VERSION_NUMBER
, DECODE(ARTA.ADOPTION_TYPE
, 'AVAILABLE'
, DECODE( DECODE(GREATEST(NVL(TRUNC(END_DATE)
, TRUNC(SYSDATE))+1
, TRUNC(SYSDATE))
, TRUNC(SYSDATE)
, 'EXPIRED'
, ARTV.ARTICLE_STATUS)
, 'APPROVED'
, ARTA.ADOPTION_STATUS
, 'EXPIRED'
, 'EXPIRED'
, 'ON_HOLD'
, 'ON_HOLD'
, 'REJECTED'
, 'REJECTED' )
, 'ADOPTED'
, DECODE( DECODE(ARTA.ADOPTION_STATUS
, 'PENDING_APPROVAL'
, 'PENDING_APPROVAL'
, DECODE(GREATEST(NVL(TRUNC(END_DATE)
, TRUNC(SYSDATE))+1
, TRUNC(SYSDATE))
, TRUNC(SYSDATE)
, 'EXPIRED'
, ARTV.ARTICLE_STATUS) )
, 'APPROVED'
, ARTA.ADOPTION_STATUS
, 'EXPIRED'
, 'EXPIRED'
, 'ON_HOLD'
, 'ON_HOLD'
, 'PENDING_APPROVAL'
, 'PENDING_APPROVAL' ) ) ARTICLE_STATUS
, DECODE(ARTA.ADOPTION_TYPE
, 'AVAILABLE'
, DECODE( DECODE(GREATEST(NVL(TRUNC(END_DATE)
, TRUNC(SYSDATE))+1
, TRUNC(SYSDATE))
, TRUNC(SYSDATE)
, 'EXPIRED'
, ARTV.ARTICLE_STATUS)
, 'APPROVED'
, DECODE(ARTA.ADOPTION_STATUS
, NULL
, NULL
, SM.MEANING)
, 'EXPIRED'
, SM.MEANING
, 'ON_HOLD'
, SM.MEANING
, 'REJECTED'
, SM.MEANING )
, 'ADOPTED'
, DECODE( DECODE(ARTA.ADOPTION_STATUS
, 'PENDING_APPROVAL'
, 'PENDING_APPROVAL'
, DECODE(GREATEST(NVL(TRUNC(END_DATE)
, TRUNC(SYSDATE))+1
, TRUNC(SYSDATE))
, TRUNC(SYSDATE)
, 'EXPIRED'
, ARTV.ARTICLE_STATUS) )
, 'APPROVED'
, SM.MEANING
, 'EXPIRED'
, SM.MEANING
, 'ON_HOLD'
, SM.MEANING
, 'PENDING_APPROVAL'
, SM.MEANING ) )
, ARTA.ADOPTION_TYPE
, AM.MEANING
, ARTV.ARTICLE_TEXT
, ARTV.ARTICLE_DESCRIPTION
, ARTA.LOCAL_ORG_ID
, ART.ARTICLE_ID
, ARTA.GLOBAL_ARTICLE_VERSION_ID
, ART.ARTICLE_INTENT
, IM.MEANING
, ARTV.PROVISION_YN
, PM.MEANING
, ARTV.START_DATE
, ARTV.END_DATE
, ARTV.DEFAULT_SECTION
, DSM.MEANING
FROM OKC_ARTICLES_ALL ART
, OKC_ARTICLE_VERSIONS ARTV
, OKC_ARTICLE_ADOPTIONS ARTA
, OKC_LOOKUPS_V SM
, OKC_LOOKUPS_V TM
, OKC_LOOKUPS_V DSM
, OKC_LOOKUPS_V PM
, OKC_LOOKUPS_V IM
, OKC_LOOKUPS_V AM
WHERE ART.STANDARD_YN = 'Y'
AND ARTV.ARTICLE_VERSION_ID = ARTA.GLOBAL_ARTICLE_VERSION_ID
AND ARTV.ARTICLE_ID = ART.ARTICLE_ID
AND ARTA.ADOPTION_TYPE IN ('ADOPTED'
, 'AVAILABLE')
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 AM.LOOKUP_TYPE ='OKC_ARTICLE_ADOPTION_TYPE'
AND TM.LOOKUP_CODE(+) = ART.ARTICLE_TYPE
AND SM.LOOKUP_CODE = DECODE(ARTA.ADOPTION_TYPE
, 'AVAILABLE'
, DECODE( DECODE(GREATEST(NVL(TRUNC(END_DATE)
, TRUNC(SYSDATE))+1
, TRUNC(SYSDATE))
, TRUNC(SYSDATE)
, 'EXPIRED'
, ARTV.ARTICLE_STATUS)
, 'APPROVED'
, NVL(ARTA.ADOPTION_STATUS
, 'DRAFT')
, 'EXPIRED'
, 'EXPIRED'
, 'ON_HOLD'
, 'ON_HOLD'
, 'REJECTED'
, 'REJECTED' )
, 'ADOPTED'
, DECODE( DECODE(ARTA.ADOPTION_STATUS
, 'PENDING_APPROVAL'
, 'PENDING_APPROVAL'
, DECODE(GREATEST(NVL(TRUNC(END_DATE)
, TRUNC(SYSDATE))+1
, TRUNC(SYSDATE))
, TRUNC(SYSDATE)
, 'EXPIRED'
, ARTV.ARTICLE_STATUS) )
, 'APPROVED'
, ARTA.ADOPTION_STATUS
, 'EXPIRED'
, 'EXPIRED'
, 'ON_HOLD'
, 'ON_HOLD'
, 'PENDING_APPROVAL'
, 'PENDING_APPROVAL' ) )
AND DSM.LOOKUP_CODE(+) = ARTV.DEFAULT_SECTION
AND PM.LOOKUP_CODE = ARTV.PROVISION_YN
AND IM.LOOKUP_CODE = ART.ARTICLE_INTENT
AND AM.LOOKUP_CODE = ARTA.ADOPTION_TYPE