GasAccounting - 12 Nov 19

UPDATE C SET C.OutstandingAmount = '0.01' FROM Fdr..Contracts C
left outer join Fdr..ContractsExtension CE ON C.ContractId = CE.ContractId
left outer join FDR..ContractMaster CM ON C.ContractMasterId = CM.ContractMasterId
left outer join Fdr..Counterparties CP ON C.CounterpartyId = CP.CounterpartyId
WHERE C.ReportingDate = '2019-11-12' --AND CM.OriginSystemId = 'SCM'
AND (C.UndrawnAmount != 0 OR C.UndrawnAmount IS NULL) AND C.OutstandingAmount = 0 AND C.MaturityDate >= C.ReportingDate;

--------------------------------------------------------------------------------------------------------------

UPDATE CE SET CE.GASAccounting =
CASE WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CB'
    AND SUBSTRING (CE.BUCODE, 3, 1) NOT IN ('0', 'C')
    THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 3) = 'CBC'
    THEN 'COMMERCIAL'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'SM'
    THEN 'SMALL BUSINESS'
        --CASE
        --    WHEN C.PRODUCTTYPE LIKE '%CASH%' OR C.PRODUCTTYPE LIKE '%TUNAI%'
        --    THEN 'CONSUMER'
        --    ELSE 'SMALL BUSINESS'
        --    END
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MB'
    THEN 'MICRO BANKING'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CL'
    THEN 'CONSUMER'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('FI','GI')
        THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('CC')
        THEN 'CREDIT CARD'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'TR'
        THEN 'TREASURY'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CR'
        THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MM'
        THEN 'SMALL BUSINESS'
    WHEN CE.BUCODE IS NULL
        THEN NULL
    ELSE
        'SMALL BUSINESS'
    END,
CE.GASReporting =
CASE WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CB'
    AND SUBSTRING (CE.BUCODE, 3, 1) NOT IN ('0', 'C')
    THEN 'WHOLESALE CIB'
    WHEN SUBSTRING (CE.BUCODE, 1, 3) = 'CBC'
    THEN 'WHOLESALE COMMERCIAL'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'SM'
    THEN 'RETAIL SME'
        --CASE
        --    WHEN C.PRODUCTTYPE LIKE '%CASH%' OR C.PRODUCTTYPE LIKE '%TUNAI%'
        --    THEN 'RETAIL INDIVIDU'
        --    ELSE 'RETAIL SME'
        --    END
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MB'
    THEN 'RETAIL MICRO'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CL'
    THEN 'RETAIL INDIVIDU'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('FI','GI')
        THEN 'WHOLESALE CIB'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('CC')
        THEN 'CREDIT CARD'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'TR'
        THEN 'TREASURY'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CR'
        THEN 'SAM'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MM'
        THEN 'RETAIL SME'
    WHEN CE.BUCODE IS NULL
        THEN NULL
    ELSE 'RETAIL SME'
    END
FROM FDR..ContractsExtension CE
INNER JOIN FDR..Contracts C ON C.ContractId = CE.ContractId
INNER JOIN FDR..ContractMaster CM ON C.ContractMasterId = CM.ContractMasterId
WHERE
--CM.OriginSystemId IN ('SCM') AND
C.ReportingDate = '2019-11-12'
AND BUCode IS NOT NULL

--------------------------------------------------------------------------------------------------------------

UPDATE C SET C.Segment =
CASE WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CB'
    AND SUBSTRING (CE.BUCODE, 3, 1) NOT IN ('0', 'C')
    THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 3) = 'CBC'
    THEN 'COMMERCIAL'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'SM'
    THEN 'SMALL BUSINESS'
        --CASE
        --    WHEN C.PRODUCTTYPE LIKE '%CASH%' OR C.PRODUCTTYPE LIKE '%TUNAI%'
        --    THEN 'CONSUMER'
        --    ELSE 'SMALL BUSINESS'
        --    END
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MB'
    THEN 'MICRO BANKING'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CL'
    THEN 'CONSUMER'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('FI','GI')
        THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('CC')
        THEN 'CREDIT CARD'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'TR'
        THEN 'TREASURY'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CR'
        THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MM'
        THEN 'SMALL BUSINESS'
    WHEN CE.BUCODE IS NULL
        THEN NULL
    ELSE
        'SMALL BUSINESS'
    END
FROM FDR..Contracts C
INNER JOIN FDR..ContractMaster CM ON C.ContractMasterId = CM.ContractMasterId
INNER JOIN FDR..ContractsExtension CE ON CE.ContractId = C.ContractId
WHERE
--CM.OriginSystemId IN ('SCM') AND
C.ReportingDate = '2019-11-12'
AND BUCode IS NOT NULL