VIEW: APPS.IEC_O_VALIDATION_REPORT_SUMM_V

Source

View Text - Preformatted

SELECT E.SCHEDULE_ID, E.LIST_HEADER_ID, E.LOOKUP_CODE, E.COUNT FROM (SELECT DISTINCT D.SCHEDULE_ID , D.LIST_HEADER_ID , A.LOOKUP_CODE , NVL((SELECT NVL(B.COUNT, 0) FROM IEC_O_VAL_DNU_S1_COUNTS_MV B WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON AND D.LIST_HEADER_ID = B.LIST_HEADER_ID) , 0) + NVL((SELECT NVL(B.COUNT, 0) FROM IEC_O_VAL_DNU_S2_COUNTS_MV B WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON AND D.LIST_HEADER_ID = B.LIST_HEADER_ID) , 0) + NVL((SELECT NVL(B.COUNT, 0) FROM IEC_O_VAL_DNU_S3_COUNTS_MV B WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON AND D.LIST_HEADER_ID = B.LIST_HEADER_ID) , 0) + NVL((SELECT NVL(B.COUNT, 0) FROM IEC_O_VAL_DNU_S4_COUNTS_MV B WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON AND D.LIST_HEADER_ID = B.LIST_HEADER_ID) , 0) + NVL((SELECT NVL(B.COUNT, 0) FROM IEC_O_VAL_DNU_S5_COUNTS_MV B WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON AND D.LIST_HEADER_ID = B.LIST_HEADER_ID) , 0) + NVL((SELECT NVL(B.COUNT, 0) FROM IEC_O_VAL_DNU_S6_COUNTS_MV B WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON AND D.LIST_HEADER_ID = B.LIST_HEADER_ID) , 0) COUNT FROM IEC_LOOKUPS A, IEC_G_AO_LISTS_V D WHERE A.LOOKUP_TYPE = 'IEC_DNU_REASON' AND A.LOOKUP_CODE BETWEEN '400' AND '499' AND UPPER(A.DESCRIPTION) <> 'RESERVED') E WHERE E.COUNT > 0

View Text - HTML Formatted

SELECT E.SCHEDULE_ID
, E.LIST_HEADER_ID
, E.LOOKUP_CODE
, E.COUNT
FROM (SELECT DISTINCT D.SCHEDULE_ID
, D.LIST_HEADER_ID
, A.LOOKUP_CODE
, NVL((SELECT NVL(B.COUNT
, 0)
FROM IEC_O_VAL_DNU_S1_COUNTS_MV B
WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON
AND D.LIST_HEADER_ID = B.LIST_HEADER_ID)
, 0) + NVL((SELECT NVL(B.COUNT
, 0)
FROM IEC_O_VAL_DNU_S2_COUNTS_MV B
WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON
AND D.LIST_HEADER_ID = B.LIST_HEADER_ID)
, 0) + NVL((SELECT NVL(B.COUNT
, 0)
FROM IEC_O_VAL_DNU_S3_COUNTS_MV B
WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON
AND D.LIST_HEADER_ID = B.LIST_HEADER_ID)
, 0) + NVL((SELECT NVL(B.COUNT
, 0)
FROM IEC_O_VAL_DNU_S4_COUNTS_MV B
WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON
AND D.LIST_HEADER_ID = B.LIST_HEADER_ID)
, 0) + NVL((SELECT NVL(B.COUNT
, 0)
FROM IEC_O_VAL_DNU_S5_COUNTS_MV B
WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON
AND D.LIST_HEADER_ID = B.LIST_HEADER_ID)
, 0) + NVL((SELECT NVL(B.COUNT
, 0)
FROM IEC_O_VAL_DNU_S6_COUNTS_MV B
WHERE A.LOOKUP_CODE = B.DO_NOT_USE_REASON
AND D.LIST_HEADER_ID = B.LIST_HEADER_ID)
, 0) COUNT
FROM IEC_LOOKUPS A
, IEC_G_AO_LISTS_V D
WHERE A.LOOKUP_TYPE = 'IEC_DNU_REASON'
AND A.LOOKUP_CODE BETWEEN '400'
AND '499'
AND UPPER(A.DESCRIPTION) <> 'RESERVED') E
WHERE E.COUNT > 0