VIEW: APPS.IEC_O_VALIDATION_REPORT_SUMM_V

Source

View Text - Preformatted

SELECT distinct d.schedule_id, val.list_header_id,val.LOOKUP_CODE,val.count from ( select B.list_header_id, A.LOOKUP_CODE, sum(decode(A.LOOKUP_CODE, B.DO_NOT_USE_REASON_S1,1,0) + decode(A.LOOKUP_CODE, B.DO_NOT_USE_REASON_S2,1,0) + decode(A.LOOKUP_CODE, B.DO_NOT_USE_REASON_S3,1,0) + decode(A.LOOKUP_CODE, B.DO_NOT_USE_REASON_S4,1,0) + decode(A.LOOKUP_CODE, B.DO_NOT_USE_REASON_S5,1,0) + decode(A.LOOKUP_CODE, B.DO_NOT_USE_REASON_S6,1,0) ) count from IEC_O_VALIDATION_REPORT_DETS B, IEC_LOOKUPS A where A.LOOKUP_TYPE = 'IEC_DNU_REASON' AND A.LOOKUP_CODE BETWEEN '400' AND '499' AND A.DESCRIPTION <> 'RESERVED' AND (A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S1 or A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S2 or A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S3 or A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S4 or A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S5 or A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S6) group by B.list_header_id,A.LOOKUP_CODE) val ,IEC_G_AO_LISTS_V d where d.list_header_id = val.list_header_id and val.count>0

View Text - HTML Formatted

SELECT DISTINCT D.SCHEDULE_ID
, VAL.LIST_HEADER_ID
, VAL.LOOKUP_CODE
, VAL.COUNT
FROM ( SELECT B.LIST_HEADER_ID
, A.LOOKUP_CODE
, SUM(DECODE(A.LOOKUP_CODE
, B.DO_NOT_USE_REASON_S1
, 1
, 0) + DECODE(A.LOOKUP_CODE
, B.DO_NOT_USE_REASON_S2
, 1
, 0) + DECODE(A.LOOKUP_CODE
, B.DO_NOT_USE_REASON_S3
, 1
, 0) + DECODE(A.LOOKUP_CODE
, B.DO_NOT_USE_REASON_S4
, 1
, 0) + DECODE(A.LOOKUP_CODE
, B.DO_NOT_USE_REASON_S5
, 1
, 0) + DECODE(A.LOOKUP_CODE
, B.DO_NOT_USE_REASON_S6
, 1
, 0) ) COUNT
FROM IEC_O_VALIDATION_REPORT_DETS B
, IEC_LOOKUPS A
WHERE A.LOOKUP_TYPE = 'IEC_DNU_REASON'
AND A.LOOKUP_CODE BETWEEN '400'
AND '499'
AND A.DESCRIPTION <> 'RESERVED'
AND (A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S1 OR A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S2 OR A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S3 OR A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S4 OR A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S5 OR A.LOOKUP_CODE = B.DO_NOT_USE_REASON_S6) GROUP BY B.LIST_HEADER_ID
, A.LOOKUP_CODE) VAL
, IEC_G_AO_LISTS_V D
WHERE D.LIST_HEADER_ID = VAL.LIST_HEADER_ID
AND VAL.COUNT>0