VIEW: APPS.OKC_TERMS_STRUCTURE_V

Source

View Text - Preformatted

SELECT kart.id, okc_terms_util_pvt.get_article_name( kart.sav_sae_id, okc_terms_util_pvt.get_latest_tmpl_art_version_id( kart.sav_sae_id, tmpl.start_date, tmpl.end_date, tmpl.status_code, kart.document_type, kart.document_id)), kart.scn_id, 'SECTION', kart.sav_sae_id, okc_terms_util_pvt.get_latest_tmpl_art_version_id( kart.sav_sae_id, tmpl.start_date, tmpl.end_date, tmpl.status_code, kart.document_type, kart.document_id), kart.display_sequence, kart.label, 'ARTICLE', kart.document_id, kart.document_type, kart.mandatory_yn, okc_terms_util_pvt.has_alternates( kart.sav_sae_id, tmpl.start_date, tmpl.end_date, tmpl.org_id, 'TEMPLATE'), 'Y', kart.OBJECT_VERSION_NUMBER, DECODE(kart.source_flag,'R','Y','N'), kart.MANDATORY_RWA, null FROM okc_k_articles_b kart, okc_terms_templates_all tmpl WHERE kart.document_type = 'TEMPLATE' and kart.document_id = tmpl.template_id and nvl(kart.amendment_operation_code,'?') <> 'DELETED' and nvl(kart.summary_amend_operation_code,'?') <> 'DELETED' UNION ALL SELECT document_id, okc_terms_util_pvt.get_message('OKC','OKC_TERMS_CONTRACT_TERMS'), TO_NUMBER(null), null, TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), null, document_type, document_id, document_type, null, null, null, OBJECT_VERSION_NUMBER, null , null, null FROM okc_template_usages UNION ALL SELECT template_id, okc_terms_util_pvt.get_message('OKC','OKC_TERMS_CONTRACT_TERMS'), TO_NUMBER(null), null, TO_NUMBER(null), TO_NUMBER(null), TO_NUMBER(null), null, 'TEMPLATE', template_id, 'TEMPLATE', null, null, null, OBJECT_VERSION_NUMBER, null , null, null FROM okc_terms_templates_all UNION ALL SELECT id, heading, nvl(scn_id,document_id), decode(scn_id,null,document_type,'SECTION'), TO_NUMBER(null), TO_NUMBER(null), section_sequence, label, 'SECTION', document_id, document_type, null, null, null, OBJECT_VERSION_NUMBER, null , null, Nvl(Decode (sec.orig_system_reference_code, 'COPY', okc_k_entity_locks_grp.isEntityLockedbyOtherDoc('SECTION',sec.orig_system_reference_id1,sec.document_type,sec.document_id), 'N'),'N') FROM okc_sections_b sec WHERE nvl(sec.amendment_operation_code,'?') <> 'DELETED' and nvl(sec.summary_amend_operation_code,'?') <> 'DELETED' UNION ALL SELECT kart.id, okc_terms_util_pvt.get_article_name(art.article_id, ver.article_version_id), kart.scn_id, 'SECTION', kart.sav_sae_id, kart.article_version_id, kart.display_sequence, kart.label, 'ARTICLE', kart.document_id, kart.document_type, kart.mandatory_yn, DECODE(art.standard_yn,'N','N', okc_terms_util_pvt.has_alternates(art.article_id, usg.article_effective_date, null, tmpl.org_id, usg.document_type)), art.standard_yn, kart.OBJECT_VERSION_NUMBER, decode(kart.source_flag,'R','Y','N') , kart.MANDATORY_RWA, Nvl( Decode(kart.orig_system_reference_code, 'COPY', okc_k_entity_locks_grp.isEntityLockedbyOtherDoc('CLAUSE',kart.orig_system_reference_id1,kart.document_type,kart.document_id), 'N'),'N') FROM okc_k_articles_b kart, okc_template_usages usg, okc_article_versions ver, okc_articles_all art, okc_terms_templates_all tmpl WHERE kart.article_version_id = ver.article_version_id AND ver.article_id = art.article_id AND usg.document_type <> 'TEMPLATE' AND usg.document_type = kart.document_type AND usg.document_id = kart.document_id AND nvl(kart.amendment_operation_code,'?') <> 'DELETED' AND nvl(kart.summary_amend_operation_code,'?') <> 'DELETED' AND usg.template_id = tmpl.template_id

View Text - HTML Formatted

SELECT KART.ID
, OKC_TERMS_UTIL_PVT.GET_ARTICLE_NAME( KART.SAV_SAE_ID
, OKC_TERMS_UTIL_PVT.GET_LATEST_TMPL_ART_VERSION_ID( KART.SAV_SAE_ID
, TMPL.START_DATE
, TMPL.END_DATE
, TMPL.STATUS_CODE
, KART.DOCUMENT_TYPE
, KART.DOCUMENT_ID))
, KART.SCN_ID
, 'SECTION'
, KART.SAV_SAE_ID
, OKC_TERMS_UTIL_PVT.GET_LATEST_TMPL_ART_VERSION_ID( KART.SAV_SAE_ID
, TMPL.START_DATE
, TMPL.END_DATE
, TMPL.STATUS_CODE
, KART.DOCUMENT_TYPE
, KART.DOCUMENT_ID)
, KART.DISPLAY_SEQUENCE
, KART.LABEL
, 'ARTICLE'
, KART.DOCUMENT_ID
, KART.DOCUMENT_TYPE
, KART.MANDATORY_YN
, OKC_TERMS_UTIL_PVT.HAS_ALTERNATES( KART.SAV_SAE_ID
, TMPL.START_DATE
, TMPL.END_DATE
, TMPL.ORG_ID
, 'TEMPLATE')
, 'Y'
, KART.OBJECT_VERSION_NUMBER
, DECODE(KART.SOURCE_FLAG
, 'R'
, 'Y'
, 'N')
, KART.MANDATORY_RWA
, NULL
FROM OKC_K_ARTICLES_B KART
, OKC_TERMS_TEMPLATES_ALL TMPL
WHERE KART.DOCUMENT_TYPE = 'TEMPLATE'
AND KART.DOCUMENT_ID = TMPL.TEMPLATE_ID
AND NVL(KART.AMENDMENT_OPERATION_CODE
, '?') <> 'DELETED'
AND NVL(KART.SUMMARY_AMEND_OPERATION_CODE
, '?') <> 'DELETED' UNION ALL SELECT DOCUMENT_ID
, OKC_TERMS_UTIL_PVT.GET_MESSAGE('OKC'
, 'OKC_TERMS_CONTRACT_TERMS')
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, DOCUMENT_TYPE
, DOCUMENT_ID
, DOCUMENT_TYPE
, NULL
, NULL
, NULL
, OBJECT_VERSION_NUMBER
, NULL
, NULL
, NULL
FROM OKC_TEMPLATE_USAGES UNION ALL SELECT TEMPLATE_ID
, OKC_TERMS_UTIL_PVT.GET_MESSAGE('OKC'
, 'OKC_TERMS_CONTRACT_TERMS')
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, 'TEMPLATE'
, TEMPLATE_ID
, 'TEMPLATE'
, NULL
, NULL
, NULL
, OBJECT_VERSION_NUMBER
, NULL
, NULL
, NULL
FROM OKC_TERMS_TEMPLATES_ALL UNION ALL SELECT ID
, HEADING
, NVL(SCN_ID
, DOCUMENT_ID)
, DECODE(SCN_ID
, NULL
, DOCUMENT_TYPE
, 'SECTION')
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SECTION_SEQUENCE
, LABEL
, 'SECTION'
, DOCUMENT_ID
, DOCUMENT_TYPE
, NULL
, NULL
, NULL
, OBJECT_VERSION_NUMBER
, NULL
, NULL
, NVL(DECODE (SEC.ORIG_SYSTEM_REFERENCE_CODE
, 'COPY'
, OKC_K_ENTITY_LOCKS_GRP.ISENTITYLOCKEDBYOTHERDOC('SECTION'
, SEC.ORIG_SYSTEM_REFERENCE_ID1
, SEC.DOCUMENT_TYPE
, SEC.DOCUMENT_ID)
, 'N')
, 'N')
FROM OKC_SECTIONS_B SEC
WHERE NVL(SEC.AMENDMENT_OPERATION_CODE
, '?') <> 'DELETED'
AND NVL(SEC.SUMMARY_AMEND_OPERATION_CODE
, '?') <> 'DELETED' UNION ALL SELECT KART.ID
, OKC_TERMS_UTIL_PVT.GET_ARTICLE_NAME(ART.ARTICLE_ID
, VER.ARTICLE_VERSION_ID)
, KART.SCN_ID
, 'SECTION'
, KART.SAV_SAE_ID
, KART.ARTICLE_VERSION_ID
, KART.DISPLAY_SEQUENCE
, KART.LABEL
, 'ARTICLE'
, KART.DOCUMENT_ID
, KART.DOCUMENT_TYPE
, KART.MANDATORY_YN
, DECODE(ART.STANDARD_YN
, 'N'
, 'N'
, OKC_TERMS_UTIL_PVT.HAS_ALTERNATES(ART.ARTICLE_ID
, USG.ARTICLE_EFFECTIVE_DATE
, NULL
, TMPL.ORG_ID
, USG.DOCUMENT_TYPE))
, ART.STANDARD_YN
, KART.OBJECT_VERSION_NUMBER
, DECODE(KART.SOURCE_FLAG
, 'R'
, 'Y'
, 'N')
, KART.MANDATORY_RWA
, NVL( DECODE(KART.ORIG_SYSTEM_REFERENCE_CODE
, 'COPY'
, OKC_K_ENTITY_LOCKS_GRP.ISENTITYLOCKEDBYOTHERDOC('CLAUSE'
, KART.ORIG_SYSTEM_REFERENCE_ID1
, KART.DOCUMENT_TYPE
, KART.DOCUMENT_ID)
, 'N')
, 'N')
FROM OKC_K_ARTICLES_B KART
, OKC_TEMPLATE_USAGES USG
, OKC_ARTICLE_VERSIONS VER
, OKC_ARTICLES_ALL ART
, OKC_TERMS_TEMPLATES_ALL TMPL
WHERE KART.ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
AND VER.ARTICLE_ID = ART.ARTICLE_ID
AND USG.DOCUMENT_TYPE <> 'TEMPLATE'
AND USG.DOCUMENT_TYPE = KART.DOCUMENT_TYPE
AND USG.DOCUMENT_ID = KART.DOCUMENT_ID
AND NVL(KART.AMENDMENT_OPERATION_CODE
, '?') <> 'DELETED'
AND NVL(KART.SUMMARY_AMEND_OPERATION_CODE
, '?') <> 'DELETED'
AND USG.TEMPLATE_ID = TMPL.TEMPLATE_ID