ZCH_CLNS_RESULTS_V

Details

• Schema: FUSION

• Object owner: ZCH

• Object type: VIEW

Columns

Name
DATA_MGMT_BATCH_ID COUNTRY TOTAL NO_OF_CLEANSED NO_OF_VERIFIED NO_OF_SKIPPED NO_OF_FAILED NO_OF_ACCEPTED NO_OF_ERROR NO_OF_EXCLUDED NO_OF_NEW NO_OF_REJECTED NO_OF_SUBMITTED COUNT_STAR CT_TOTAL CT_OF_CLEANSED CT_OF_VERIFIED CT_OF_SKIPPED CT_OF_FAILED CT_OF_ACCEPTED CT_OF_ERROR CT_OF_EXCLUDED CT_OF_NEW CT_OF_REJECTED CT_OF_SUBMITTED SUMSUBMITTED SUMACCEPTED SUMREJECTED SUMERROR SUMEXCLUDED SUMNEW

Query

SQL_Statement

SELECT ZCR.DATA_MGMT_BATCH_ID, ZCR.COUNTRY, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, NULL, 0, 1)) Total, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'CLEANSED', 1, 0)) no_of_Cleansed, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'VERIFIED', 1, 0)) no_of_Verified, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'SKIPPED', 1, 0)) no_of_Skipped, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'FAILED', 1, 0)) no_of_Failed, SUM(DECODE(ZCR.USAGE_STATUS_CODE, 'ACCEPTED', 1, 0)) no_of_Accepted, SUM(DECODE(ZCR.USAGE_STATUS_CODE, 'ERROR', 1, 0)) no_of_Error, SUM(DECODE(ZCR.USAGE_STATUS_CODE, 'EXCLUDED', 1, 0)) no_of_Excluded, SUM(DECODE(ZCR.USAGE_STATUS_CODE, 'NEW', 1, 0)) no_of_New, SUM(DECODE(ZCR.USAGE_STATUS_CODE, 'REJECTED', 1, 0)) no_of_Rejected, SUM(DECODE(ZCR.USAGE_STATUS_CODE, 'SUBMITTED', 1, 0)) no_of_Submitted, COUNT(*) COUNT_STAR, COUNT(DECODE(ZCR.PROCESS_STATUS_CODE, NULL, 0, 1)) ct_Total, COUNT(DECODE(ZCR.PROCESS_STATUS_CODE, 'CLEANSED', 1, 0)) ct_of_Cleansed, COUNT(DECODE(ZCR.PROCESS_STATUS_CODE, 'VERIFIED', 1, 0)) ct_of_Verified, COUNT(DECODE(ZCR.PROCESS_STATUS_CODE, 'SKIPPED', 1, 0)) ct_of_Skipped, COUNT(DECODE(ZCR.PROCESS_STATUS_CODE, 'FAILED', 1, 0)) ct_of_Failed, COUNT(DECODE(ZCR.USAGE_STATUS_CODE, 'ACCEPTED', 1, 0)) ct_of_Accepted, COUNT(DECODE(ZCR.USAGE_STATUS_CODE, 'ERROR', 1, 0)) ct_of_Error, COUNT(DECODE(ZCR.USAGE_STATUS_CODE, 'EXCLUDED', 1, 0)) ct_of_Excluded, COUNT(DECODE(ZCR.USAGE_STATUS_CODE, 'NEW', 1, 0)) ct_of_New, COUNT(DECODE(ZCR.USAGE_STATUS_CODE, 'REJECTED', 1, 0)) ct_of_Rejected, COUNT(DECODE(ZCR.USAGE_STATUS_CODE, 'SUBMITTED', 1, 0)) ct_of_Submitted, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'CLEANSED', DECODE(ZCR.USAGE_STATUS_CODE, 'SUBMITTED', 1, 0) ,0)) SumSubmitted, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'CLEANSED', DECODE(ZCR.USAGE_STATUS_CODE, 'ACCEPTED', 1, 0) ,0)) SumAccepted, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'CLEANSED', DECODE(ZCR.USAGE_STATUS_CODE, 'REJECTED', 1, 0) ,0)) SumRejected, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'CLEANSED', DECODE(ZCR.USAGE_STATUS_CODE, 'ERROR', 1, 0) ,0)) SumError, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'CLEANSED', DECODE(ZCR.USAGE_STATUS_CODE, 'EXCLUDED', 1, 0) ,0)) SumExcluded, SUM(DECODE(ZCR.PROCESS_STATUS_CODE, 'CLEANSED', DECODE(ZCR.USAGE_STATUS_CODE, 'NEW', 1, 0) ,0)) SumNew FROM FUSION.ZCH_CLNS_LOC_RESULTS ZCR GROUP BY ZCR.DATA_MGMT_BATCH_ID, ZCR.COUNTRY