VIEW: APPS.AHL_DOCUMENT_ASSOS_V

Source

View Text - Preformatted

SELECT ADS.DOCUMENT_ID, ADS.DOCUMENT_NO, ADT.DOC_TITLE_ASSO_ID, ADT.OBJECT_VERSION_NUMBER, ADT.ASO_OBJECT_ID, ADT.DOC_REVISION_ID, ADR.REVISION_NO, ADS.DOC_TYPE_CODE, DTC.MEANING DOC_TYPE_DESCRIPTION, ADT.ASO_OBJECT_TYPE_CODE, AOB.MEANING ASO_OBJECT_TYPE_DESC, ADS.DOCUMENT_TITLE, ADT.SOURCE_REF_CODE, SRF.MEANING SOURCE_MEANING, ADT. CHAPTER, ADT.SECTION, ADT.SUBJECT, ADT.FIGURE, ADT.PAGE, ADT.NOTE, ADFAB.FILE_ID, NVL(ADFAB.FILE_NAME,ADR.URL), ADR.MEDIA_TYPE_CODE, MED.MEANING, CASE WHEN (ADR.media_type_code IN ('E-FILE','ON-LINE') AND ( ADFAB.FILE_NAME IS NOT NULL OR ADR.URL IS NOT NULL ) ) THEN '0' ELSE '1' END, ADT.USE_LATEST_REV_FLAG , LAT.MEANING FLAG_MEANING, ADR.ENIGMA_DOC_ID, CASE WHEN ADR.ENIGMA_DOC_ID IS NOT NULL THEN '1' ELSE '0' END FROM AHL_DOCUMENTS_VL ADS, AHL_DOC_TITLE_ASSOS_VL ADT, AHL_DOC_REVISIONS_B ADR, AHL_DOC_FILE_ASSOC_B ADFAB, FND_LOOKUP_VALUES_VL DTC, FND_LOOKUP_VALUES_VL AOB, FND_LOOKUP_VALUES_VL SRF, FND_LOOKUP_VALUES_VL MED, FND_LOOKUP_VALUES_VL LAT WHERE ADS.DOCUMENT_ID = ADR.DOCUMENT_ID(+) AND ADR.DOC_REVISION_ID = ADT.DOC_REVISION_ID(+) AND ADT.DOC_REVISION_ID IS NOT NULL AND DTC.LOOKUP_CODE(+) = ADS.DOC_TYPE_CODE AND DTC.LOOKUP_TYPE(+) = 'AHL_DOC_TYPE' AND AOB.LOOKUP_CODE(+) = ADT.ASO_OBJECT_TYPE_CODE AND AOB.LOOKUP_TYPE(+)= 'AHL_OBJECT_TYPE' AND SRF.LOOKUP_CODE(+) = ADT.SOURCE_REF_CODE AND SRF.LOOKUP_TYPE(+) = 'AHL_DI_SOURCE_REF' AND MED.LOOKUP_CODE(+) =ADR.MEDIA_TYPE_CODE AND MED.LOOKUP_TYPE(+) ='AHL_MEDIA_TYPE' AND ADR.DOC_REVISION_ID = ADFAB.REVISION_ID(+) AND ADFAB.DATATYPE_CODE(+) = 'FILE' AND LAT.LOOKUP_CODE(+) = ADT.USE_LATEST_REV_FLAG AND LAT.LOOKUP_TYPE(+) = 'AHL_YES_NO_TYPE' UNION SELECT ADS.DOCUMENT_ID, ADS.DOCUMENT_NO, ADT.DOC_TITLE_ASSO_ID, ADT.OBJECT_VERSION_NUMBER, ADT.ASO_OBJECT_ID, ADT.DOC_REVISION_ID, ADR.REVISION_NO, ADS.DOC_TYPE_CODE, DTC.MEANING DOC_TYPE_DESCRIPTION, ADT.ASO_OBJECT_TYPE_CODE, AOB.MEANING ASO_OBJECT_TYPE_DESC, ADS.DOCUMENT_TITLE, ADT.SOURCE_REF_CODE, SRF.MEANING SOURCE_MEANING, ADT. CHAPTER, ADT.SECTION, ADT.SUBJECT, ADT.FIGURE, ADT.PAGE, ADT.NOTE, ADFAB.FILE_ID, NVL(ADFAB.FILE_NAME,ADR.URL), ADR.MEDIA_TYPE_CODE, MED.MEANING, CASE WHEN (ADR.media_type_code IN ('E-FILE','ON-LINE') AND ( ADFAB.FILE_NAME IS NOT NULL OR ADR.URL IS NOT NULL ) ) THEN '0' ELSE '1' END, ADT.USE_LATEST_REV_FLAG , LAT.MEANING FLAG_MEANING, ADR.ENIGMA_DOC_ID, CASE WHEN ADR.ENIGMA_DOC_ID IS NOT NULL THEN '1' ELSE '0' END FROM AHL_DOC_TITLE_ASSOS_VL ADT, AHL_DOC_REVISIONS_B ADR, AHL_DOCUMENTS_VL ADS, AHL_DOC_FILE_ASSOC_B ADFAB, FND_LOOKUP_VALUES_VL DTC, FND_LOOKUP_VALUES_VL AOB, FND_LOOKUP_VALUES_VL SRF, FND_LOOKUP_VALUES_VL MED , FND_LOOKUP_VALUES_VL LAT WHERE ADS.DOCUMENT_ID = ADT.DOCUMENT_ID(+) AND ADR.DOC_REVISION_ID(+) = ADT.DOC_REVISION_ID AND ADT.DOC_REVISION_ID IS NULL AND DTC.LOOKUP_CODE(+) = ADS.DOC_TYPE_CODE AND DTC.LOOKUP_TYPE(+) = 'AHL_DOC_TYPE' AND AOB.LOOKUP_CODE(+) = ADT.ASO_OBJECT_TYPE_CODE AND AOB.LOOKUP_TYPE(+) = 'AHL_OBJECT_TYPE' AND SRF.LOOKUP_CODE(+) = ADT.SOURCE_REF_CODE AND AOB.LOOKUP_TYPE(+) = 'AHL_DI_SOURCE_REF' AND MED.LOOKUP_CODE(+) =ADR.MEDIA_TYPE_CODE AND MED.LOOKUP_TYPE(+) ='AHL_MEDIA_TYPE' AND ADR.DOC_REVISION_ID = ADFAB.REVISION_ID(+) AND ADFAB.DATATYPE_CODE(+) = 'FILE' AND LAT.LOOKUP_CODE(+) = ADT.USE_LATEST_REV_FLAG AND LAT.LOOKUP_TYPE(+) = 'AHL_YES_NO_TYPE'

View Text - HTML Formatted

SELECT ADS.DOCUMENT_ID
, ADS.DOCUMENT_NO
, ADT.DOC_TITLE_ASSO_ID
, ADT.OBJECT_VERSION_NUMBER
, ADT.ASO_OBJECT_ID
, ADT.DOC_REVISION_ID
, ADR.REVISION_NO
, ADS.DOC_TYPE_CODE
, DTC.MEANING DOC_TYPE_DESCRIPTION
, ADT.ASO_OBJECT_TYPE_CODE
, AOB.MEANING ASO_OBJECT_TYPE_DESC
, ADS.DOCUMENT_TITLE
, ADT.SOURCE_REF_CODE
, SRF.MEANING SOURCE_MEANING
, ADT. CHAPTER
, ADT.SECTION
, ADT.SUBJECT
, ADT.FIGURE
, ADT.PAGE
, ADT.NOTE
, ADFAB.FILE_ID
, NVL(ADFAB.FILE_NAME
, ADR.URL)
, ADR.MEDIA_TYPE_CODE
, MED.MEANING
, CASE WHEN (ADR.MEDIA_TYPE_CODE IN ('E-FILE'
, 'ON-LINE')
AND ( ADFAB.FILE_NAME IS NOT NULL OR ADR.URL IS NOT NULL ) ) THEN '0' ELSE '1' END
, ADT.USE_LATEST_REV_FLAG
, LAT.MEANING FLAG_MEANING
, ADR.ENIGMA_DOC_ID
, CASE WHEN ADR.ENIGMA_DOC_ID IS NOT NULL THEN '1' ELSE '0' END
FROM AHL_DOCUMENTS_VL ADS
, AHL_DOC_TITLE_ASSOS_VL ADT
, AHL_DOC_REVISIONS_B ADR
, AHL_DOC_FILE_ASSOC_B ADFAB
, FND_LOOKUP_VALUES_VL DTC
, FND_LOOKUP_VALUES_VL AOB
, FND_LOOKUP_VALUES_VL SRF
, FND_LOOKUP_VALUES_VL MED
, FND_LOOKUP_VALUES_VL LAT
WHERE ADS.DOCUMENT_ID = ADR.DOCUMENT_ID(+)
AND ADR.DOC_REVISION_ID = ADT.DOC_REVISION_ID(+)
AND ADT.DOC_REVISION_ID IS NOT NULL
AND DTC.LOOKUP_CODE(+) = ADS.DOC_TYPE_CODE
AND DTC.LOOKUP_TYPE(+) = 'AHL_DOC_TYPE'
AND AOB.LOOKUP_CODE(+) = ADT.ASO_OBJECT_TYPE_CODE
AND AOB.LOOKUP_TYPE(+)= 'AHL_OBJECT_TYPE'
AND SRF.LOOKUP_CODE(+) = ADT.SOURCE_REF_CODE
AND SRF.LOOKUP_TYPE(+) = 'AHL_DI_SOURCE_REF'
AND MED.LOOKUP_CODE(+) =ADR.MEDIA_TYPE_CODE
AND MED.LOOKUP_TYPE(+) ='AHL_MEDIA_TYPE'
AND ADR.DOC_REVISION_ID = ADFAB.REVISION_ID(+)
AND ADFAB.DATATYPE_CODE(+) = 'FILE'
AND LAT.LOOKUP_CODE(+) = ADT.USE_LATEST_REV_FLAG
AND LAT.LOOKUP_TYPE(+) = 'AHL_YES_NO_TYPE' UNION SELECT ADS.DOCUMENT_ID
, ADS.DOCUMENT_NO
, ADT.DOC_TITLE_ASSO_ID
, ADT.OBJECT_VERSION_NUMBER
, ADT.ASO_OBJECT_ID
, ADT.DOC_REVISION_ID
, ADR.REVISION_NO
, ADS.DOC_TYPE_CODE
, DTC.MEANING DOC_TYPE_DESCRIPTION
, ADT.ASO_OBJECT_TYPE_CODE
, AOB.MEANING ASO_OBJECT_TYPE_DESC
, ADS.DOCUMENT_TITLE
, ADT.SOURCE_REF_CODE
, SRF.MEANING SOURCE_MEANING
, ADT. CHAPTER
, ADT.SECTION
, ADT.SUBJECT
, ADT.FIGURE
, ADT.PAGE
, ADT.NOTE
, ADFAB.FILE_ID
, NVL(ADFAB.FILE_NAME
, ADR.URL)
, ADR.MEDIA_TYPE_CODE
, MED.MEANING
, CASE WHEN (ADR.MEDIA_TYPE_CODE IN ('E-FILE'
, 'ON-LINE')
AND ( ADFAB.FILE_NAME IS NOT NULL OR ADR.URL IS NOT NULL ) ) THEN '0' ELSE '1' END
, ADT.USE_LATEST_REV_FLAG
, LAT.MEANING FLAG_MEANING
, ADR.ENIGMA_DOC_ID
, CASE WHEN ADR.ENIGMA_DOC_ID IS NOT NULL THEN '1' ELSE '0' END
FROM AHL_DOC_TITLE_ASSOS_VL ADT
, AHL_DOC_REVISIONS_B ADR
, AHL_DOCUMENTS_VL ADS
, AHL_DOC_FILE_ASSOC_B ADFAB
, FND_LOOKUP_VALUES_VL DTC
, FND_LOOKUP_VALUES_VL AOB
, FND_LOOKUP_VALUES_VL SRF
, FND_LOOKUP_VALUES_VL MED
, FND_LOOKUP_VALUES_VL LAT
WHERE ADS.DOCUMENT_ID = ADT.DOCUMENT_ID(+)
AND ADR.DOC_REVISION_ID(+) = ADT.DOC_REVISION_ID
AND ADT.DOC_REVISION_ID IS NULL
AND DTC.LOOKUP_CODE(+) = ADS.DOC_TYPE_CODE
AND DTC.LOOKUP_TYPE(+) = 'AHL_DOC_TYPE'
AND AOB.LOOKUP_CODE(+) = ADT.ASO_OBJECT_TYPE_CODE
AND AOB.LOOKUP_TYPE(+) = 'AHL_OBJECT_TYPE'
AND SRF.LOOKUP_CODE(+) = ADT.SOURCE_REF_CODE
AND AOB.LOOKUP_TYPE(+) = 'AHL_DI_SOURCE_REF'
AND MED.LOOKUP_CODE(+) =ADR.MEDIA_TYPE_CODE
AND MED.LOOKUP_TYPE(+) ='AHL_MEDIA_TYPE'
AND ADR.DOC_REVISION_ID = ADFAB.REVISION_ID(+)
AND ADFAB.DATATYPE_CODE(+) = 'FILE'
AND LAT.LOOKUP_CODE(+) = ADT.USE_LATEST_REV_FLAG
AND LAT.LOOKUP_TYPE(+) = 'AHL_YES_NO_TYPE'