SELECT 'D' REC_TYPE, A.TR_TYPE TR_TYPE, A.REPORT_TYPE REPORT_TYPE, A.AGT_ID AGT_ID, A.DATETIME DATETIME, A.LEGACY_REF_NBR REF_NBR, A.TRANSACTION_ID TRANSACTION_ID, A.TRAN_DATE TRAN_DATE, A.SND_CNTRY SND_CNTRY, A.RCV_CNTRY RCV_CNTRY, A.TRAN_STATUS TRAN_STATUS, A.FACE_STL_CURR FACE_STL_CURR, A.FEE_STL_CURR FEE_STL_CURR, A.COMM_STL_CURR COMM_STL_CURR, A.FACE_TRAN_CURR FACE_TRAN_CURR, A.FEE_TRAN_CURR FEE_TRAN_CURR, A.COMM_TRAN_CURR COMM_TRAN_CURR, A.FXREVSHARE_TRAN_CURR FXREVSHARE_TRAN_CURR, A.FXREVSHARE_STL_CURR FXREVSHARE_STL_CURR, A.SND_GUAR_RCV_CNTRY SND_GUAR_RCV_CNTRY, A.RCV_AMT_AGENT_CURR RCV_AMT_AGENT_CURR, A.RCV_AMT_CUST_CURR RCV_AMT_CUST_CURR, A.FX_MARGIN FX_MARGIN, A.SND_CUST_FIRST_NAME SND_CUST_FIRST_NAME, A.SND_CUST_LAST_NAME SND_CUST_LAST_NAME, A.RCV_CUST_FIRST_NAME RCV_CUST_FIRST_NAME, A.RCV_CUST_LAST_NAME RCV_CUST_LAST_NAME, A.RCV_AGENT_SERIAL_NBR RCV_AGENT_SERIAL_NBR, A.RCV_CUST_SERIAL_NBR RCV_CUST_SERIAL_NBR, A.RISK_CODE RISK_CODE, A.TOT_AMT_TRAN_CURR TOT_AMT_TRAN_CURR, A.TOT_AMT_STL_CURR TOT_AMT_STL_CURR, A.TOT_COMM_FXREV_TRAN_CURR TOT_COMM_FXREV_TRAN_CURR, A.TOT_COMM_FXREV_STL_CURR TOT_COMM_FXREV_STL_CURR, A.STL_ID STL_ID, A.STLMT_CURRENCY STLMT_CURRENCY, A.STL_ENTITY STL_ENTITY, A.CR_PAY_METH_TEXT CR_PAY_METH_TEXT, A.DB_PAY_METH_TEXT DB_PAY_METH_TEXT, A.REPORT_DATE REPORT_DATE, A.STLMT_PROD_GRP_ID STLMT_PROD_GRP_ID, A.BEGIN_DATE BEGIN_DATE, A.END_DATE END_DATE, A.STLMT_AGT_NAME STLMT_AGT_NAME, A.CONTACT_FIRST_NAME CONTACT_FIRST_NAME, A.CONTACT_PATL_NAME CONTACT_PATL_NAME, A.CONTACT_MATL_NAME CONTACT_MATL_NAME, A.ADDR1 ADDR1, A.ADDR2 ADDR2, A.ADDR3 ADDR3, A.STL_CITY STL_CITY, A.STATEPROVINCE STATEPROVINCE, A.ZIP_POSTALCODE ZIP_POSTALCODE, A.CNTRY CNTRY, A.PHONE PHONE, A.FAX FAX, A.AGENT_NAME AGENT_NAME, A.LEGACYAGENTNUM LEGACYAGENTNUM, A.AGT_STORE AGT_STORE, A.SUB_COL_AGENT_ID SUB_COL_AGENT_ID, A.SUB_LEG_AGENT_ID SUB_LEG_AGENT_ID, A.SUB_AGENT_NAME SUB_AGENT_NAME, A.TRAN_FX_RATE TRAN_FX_RATE, A.STL_FX_RATE STL_FX_RATE, A.DELIVERY_ID DELIVERY_ID, A.STL_PAYMENT_AMT STL_PAYMENT_AMT, A.FX_DATE FX_DATE, A.STL_DATE STL_DATE, A.SND_AGENT_ID SND_AGENT_ID, A.RCV_AGENT_ID RCV_AGENT_ID, A.REVSHARE_FND_IND REVSHARE_FND_IND, A.COMMISH_FND_IND COMMISH_FND_IND, A.FACE_FND_IND FACE_FND_IND, A.FEE_FND_IND FEE_FND_IND, A.LOAD_FND_IND LOAD_FND_IND, A.LOAD_FEE_FND_IND LOAD_FEE_FND_IND, A.PU_FEE_FND_IND PU_FEE_FND_IND, A.PU_COMM_FND_IND PU_COMM_FND_IND, A.LOAD_COMM_FND_IND LOAD_COMM_FND_IND, A.PROC_FEE_FND_IND PROC_FEE_FND_IND, A.PROC_COMM_FND_IND PROC_COMM_FND_IND, A.CARD_NBR_LAST_4 CARD_NBR_LAST_4, A.TRAN_CURRENCY TRAN_CURRENCY, A.STL_CURRENCY STL_CURRENCY, A.STL_CURRENCY || A.TRAN_CURRENCY CURRENCY_COMBO, A.PROD_ID PROD_ID, A.PROD_NAME PROD_NAME, DECODE(A.PROD_TYPE, 'SC', 'PC', A.PROD_TYPE) PROD_TYPE, A.PROC_FEE_STL_CURR PROC_FEE_STL_CURR, A.PROC_COMM_STL_CURR PROC_COMM_STL_CURR, A.PROC_FEE_TRAN_CURR PROC_FEE_TRAN_CURR, A.PROC_COMM_TRAN_CURR PROC_COMM_TRAN_CURR, 0 TRAN_COUNT FROM RPT_MG_STL_RPT_EXT_VW A UNION ALL SELECT 'SS' REC_TYPE, NULL TR_TYPE, NULL REPORT_TYPE, NULL AGT_ID, NULL DATETIME, NULL REF_NBR, NULL TRANSACTION_ID, NULL TRAN_DATE, NULL SND_CNTRY, NULL RCV_CNTRY, NULL TRAN_STATUS, SUM(A.FACE_STL_CURR) FACE_STL_CURR, SUM(A.FEE_STL_CURR) FEE_STL_CURR, SUM(A.COMM_STL_CURR) COMM_STL_CURR, SUM(A.FACE_TRAN_CURR) FACE_TRAN_CURR, SUM(A.FEE_TRAN_CURR) FEE_TRAN_CURR, SUM(A.COMM_TRAN_CURR) COMM_TRAN_CURR, SUM(A.FXREVSHARE_TRAN_CURR) FXREVSHARE_TRAN_CURR, SUM(A.FXREVSHARE_STL_CURR) FXREVSHARE_STL_CURR, NULL SND_GUAR_RCV_CNTRY, NULL RCV_AMT_AGENT_CURR, NULL RCV_AMT_CUST_CURR, NULL FX_MARGIN, NULL SND_CUST_FIRST_NAME, NULL SND_CUST_LAST_NAME, NULL RCV_CUST_FIRST_NAME, NULL RCV_CUST_LAST_NAME, NULL RCV_AGENT_SERIAL_NBR, NULL RCV_CUST_SERIAL_NBR, NULL RISK_CODE, SUM(A.TOT_AMT_TRAN_CURR) TOT_AMT_TRAN_CURR, SUM(A.TOT_AMT_STL_CURR) TOT_AMT_STL_CURR, SUM(A.TOT_COMM_FXREV_TRAN_CURR) TOT_COMM_FXREV_TRAN_CURR, SUM(A.TOT_COMM_FXREV_STL_CURR) TOT_COMM_FXREV_STL_CURR, A.STL_ID STL_ID, A.STLMT_CURRENCY STLMT_CURRENCY, A.STL_ENTITY STL_ENTITY, NULL CR_PAY_METH_TEXT, NULL DB_PAY_METH_TEXT, NULL REPORT_DATE, NULL STLMT_PROD_GRP_ID, A.BEGIN_DATE BEGIN_DATE, A.END_DATE END_DATE, A.STLMT_AGT_NAME STLMT_AGT_NAME, NULL CONTACT_FIRST_NAME, NULL CONTACT_PATL_NAME, NULL CONTACT_MATL_NAME, NULL ADDR1, NULL ADDR2, NULL ADDR3, NULL STL_CITY, NULL STATEPROVINCE, NULL ZIP_POSTALCODE, NULL CNTRY, NULL PHONE, NULL FAX, NULL AGENT_NAME, NULL LEGACYAGENTNUM, NULL AGT_STORE, NULL SUB_COL_AGENT_ID, NULL SUB_LEG_AGENT_ID, NULL SUB_AGENT_NAME, NULL TRAN_FX_RATE, NULL STL_FX_RATE, NULL DELIVERY_ID, NULL STL_PAYMENT_AMT, NULL FX_DATE, NULL STL_DATE, NULL SND_AGENT_ID, NULL RCV_AGENT_ID, MAX(A.REVSHARE_FND_IND) REVSHARE_FND_IND, MAX(A.COMMISH_FND_IND) COMMISH_FND_IND, MAX(A.FACE_FND_IND) FACE_FND_IND, MAX(A.FEE_FND_IND) FEE_FND_IND, MAX(A.LOAD_FND_IND) LOAD_FND_IND, MAX(A.LOAD_FEE_FND_IND) LOAD_FEE_FND_IND, MAX(A.PU_FEE_FND_IND) PU_FEE_FND_IND, MAX(A.PU_COMM_FND_IND) PU_COMM_FND_IND, MAX(A.LOAD_COMM_FND_IND) LOAD_COMM_FND_IND, MAX(A.PROC_FEE_FND_IND) PROC_FEE_FND_IND, MAX(A.PROC_COMM_FND_IND) PROC_COMM_FND_IND, NULL CARD_NBR_LAST_4, NULL TRAN_CURRENCY, A.STL_CURRENCY STL_CURRENCY, A.STL_CURRENCY CURRENCY_COMBO, NULL PROD_ID, NULL PROD_NAME, NULL PROD_TYPE, SUM(A.PROC_FEE_STL_CURR) PROC_FEE_STL_CURR, SUM(A.PROC_COMM_STL_CURR) PROC_COMM_STL_CURR, SUM(A.PROC_FEE_TRAN_CURR) PROC_FEE_TRAN_CURR, SUM(A.PROC_COMM_TRAN_CURR) PROC_COMM_TRAN_CURR, COUNT(*) TRAN_COUNT FROM RPT_MG_STL_RPT_EXT_VW A WHERE A.STLMT_PROD_GRP_ID <> 2 AND A.REPORT_TYPE <> 1 GROUP BY A.STL_ENTITY, A.STLMT_AGT_NAME, A.STL_ID, A.STL_CURRENCY, A.STLMT_CURRENCY, A.BEGIN_DATE, A.END_DATE UNION ALL SELECT 'S' REC_TYPE, A.TR_TYPE TR_TYPE, NULL REPORT_TYPE, NULL AGT_ID, NULL DATETIME, NULL REF_NBR, NULL TRANSACTION_ID, NULL TRAN_DATE, NULL SND_CNTRY, NULL RCV_CNTRY, NULL TRAN_STATUS, SUM(A.FACE_STL_CURR) FACE_STL_CURR, SUM(A.FEE_STL_CURR) FEE_STL_CURR, SUM(A.COMM_STL_CURR) COMM_STL_CURR, SUM(A.FACE_TRAN_CURR) FACE_TRAN_CURR, SUM(A.FEE_TRAN_CURR) FEE_TRAN_CURR, SUM(A.COMM_TRAN_CURR) COMM_TRAN_CURR, SUM(A.FXREVSHARE_TRAN_CURR) FXREVSHARE_TRAN_CURR, SUM(A.FXREVSHARE_STL_CURR) FXREVSHARE_STL_CURR, NULL SND_GUAR_RCV_CNTRY, NULL RCV_AMT_AGENT_CURR, NULL RCV_AMT_CUST_CURR, NULL FX_MARGIN, NULL SND_CUST_FIRST_NAME, NULL SND_CUST_LAST_NAME, NULL RCV_CUST_FIRST_NAME, NULL RCV_CUST_LAST_NAME, NULL RCV_AGENT_SERIAL_NBR, NULL RCV_CUST_SERIAL_NBR, NULL RISK_CODE, SUM(A.TOT_AMT_TRAN_CURR) TOT_AMT_TRAN_CURR, SUM(A.TOT_AMT_STL_CURR) TOT_AMT_STL_CURR, SUM(A.TOT_COMM_FXREV_TRAN_CURR) TOT_COMM_FXREV_TRAN_CURR, SUM(A.TOT_COMM_FXREV_STL_CURR) TOT_COMM_FXREV_STL_CURR, A.STL_ID STL_ID, NULL STLMT_CURRENCY, A.STL_ENTITY STL_ENTITY, NULL CR_PAY_METH_TEXT, NULL DB_PAY_METH_TEXT, NULL REPORT_DATE, NULL STLMT_PROD_GRP_ID, A.BEGIN_DATE BEGIN_DATE, A.END_DATE END_DATE, A.STLMT_AGT_NAME STLMT_AGT_NAME, NULL CONTACT_FIRST_NAME, NULL CONTACT_PATL_NAME, NULL CONTACT_MATL_NAME, NULL ADDR1, NULL ADDR2, NULL ADDR3, NULL STL_CITY, NULL STATEPROVINCE, NULL ZIP_POSTALCODE, NULL CNTRY, NULL PHONE, NULL FAX, NULL AGENT_NAME, NULL LEGACYAGENTNUM, NULL AGT_STORE, NULL SUB_COL_AGENT_ID, NULL SUB_LEG_AGENT_ID, NULL SUB_AGENT_NAME, NULL TRAN_FX_RATE, NULL STL_FX_RATE, NULL DELIVERY_ID, NULL STL_PAYMENT_AMT, NULL FX_DATE, NULL STL_DATE, NULL SND_AGENT_ID, NULL RCV_AGENT_ID, MAX(A.REVSHARE_FND_IND) REVSHARE_FND_IND, MAX(A.COMMISH_FND_IND) COMMISH_FND_IND, MAX(A.FACE_FND_IND) FACE_FND_IND, MAX(A.FEE_FND_IND) FEE_FND_IND, MAX(A.LOAD_FND_IND) LOAD_FND_IND, MAX(A.LOAD_FEE_FND_IND) LOAD_FEE_FND_IND, MAX(A.PU_FEE_FND_IND) PU_FEE_FND_IND, MAX(A.PU_COMM_FND_IND) PU_COMM_FND_IND, MAX(A.LOAD_COMM_FND_IND) LOAD_COMM_FND_IND, MAX(A.PROC_FEE_FND_IND) PROC_FEE_FND_IND, MAX(A.PROC_COMM_FND_IND) PROC_COMM_FND_IND, NULL CARD_NBR_LAST_4, A.TRAN_CURRENCY TRAN_CURRENCY, NULL STL_CURRENCY, A.TRAN_CURRENCY CURRENCY_COMBO, NULL PROD_ID, NULL PROD_NAME, NULL PROD_TYPE, SUM(A.PROC_FEE_STL_CURR) PROC_FEE_STL_CURR, SUM(A.PROC_COMM_STL_CURR) PROC_COMM_STL_CURR, SUM(A.PROC_FEE_TRAN_CURR) PROC_FEE_TRAN_CURR, SUM(A.PROC_COMM_TRAN_CURR) PROC_COMM_TRAN_CURR, COUNT(*) TRAN_COUNT FROM RPT_MG_STL_RPT_EXT_VW A WHERE A.STLMT_PROD_GRP_ID <> 2 AND A.REPORT_TYPE <> 1 GROUP BY A.STL_ENTITY, A.STLMT_AGT_NAME, A.STL_ID, A.TRAN_CURRENCY, A.TR_TYPE, A.BEGIN_DATE, A.END_DATE ORDER BY STL_ENTITY, STL_ID, 1, TRAN_CURRENCY, STL_CURRENCY, SUB_COL_AGENT_ID, AGT_ID, REPORT_TYPE, TRAN_DATE, PROD_ID, PROD_NAME, TRANSACTION_ID