Capacity Tariffs

Capacity Tariffs zijn de capaciteitstarieven.

Onderstaande query kan gebruikt worden om de benodigde data te selecteren.

erp_cap_tar.sql

use dveperp_prod
go
;

WITH CTE AS(
SELECT  cc.CaptarContractId,
        cc.ConnectionId,
        con.EAN,
        cc.StartDate,
        cc.EndDate,
        cc.ValidStartDate,
        cc.ValidEndDate,
        nb.NBCode,
        c.Description AS CaptarCode,
        '87' + nb.NBCode + c.Description AS EanCaptarCode,
        c.PresentationDescription AS FysicalCapicity,
        cc.FysicalStatus,
        fs.PresentationDescription FysicalStatusDescription,
        cc.ContractModel ,
        cc.VatCategoryId,
        cm.PresentationDescription ContractModelDescription,
        cn.CaptarNBId,
        cn.CaptarId,
        cn.NBId,
        cn.AmountYear,
        cn.AmountDay,
        cn.AmountAPYear,
        cn.AmountAPMonth,
        cn.AmountAPDay,
        cn.AmountCaptar,
        cn.AmountConnectionService,
        cn.AmountMeterRent,
        cn.AmountSystemService,
        cn.AmountFixedCharge,
        CASE WHEN cc.VatCategoryId = 2 THEN cn.VATLowpc
        ELSE cn.VATpc END VATpc,
        cn.StartDate AS CaptarNBStartDate,
        cn.EndDate AS CaptarNBEndDate,
        cn.ValidStartDate AS CaptarNBValidStartDate,
        cn.ValidEndDate AS CaptarNBValidEndDate
FROM               dbo.tbl_CaptarContract  AS cc
        INNER JOIN dbo.tbl_Captar          AS c ON cc.CaptarId = c.CaptarId
        INNER JOIN dbo.tbl_NB              AS nb ON cc.NBId = nb.NBId
        LEFT OUTER JOIN dbo.tbl_CaptarNB   AS cn ON cc.CaptarId = cn.CaptarId
        AND cc.NBId = cn.NBId
        AND cn.StartDate < cc.EndDate
        AND cn.EndDate > cc.StartDate
        LEFT JOIN tbl_enum                 AS cm ON cc.ContractModel = cm.id and cm.EnumType = 'ContractModel'
        LEFT JOIN tbl_enum                 AS fs ON cc.FysicalStatus = fs.id and fs.EnumType = 'FysicalStatus'
        INNER JOIN tbl_Connection          AS con ON cc.ConnectionId = con.ConnectionId
        WHERE cc.ValidEndDate> getdate()
        AND cn.ValidEndDate > getdate()
        AND cc.EndDate > '1-1-2022'
),
captarGroup AS (SELECT DISTINCT CaptarCode, LEFT(captarcode,12) shortCaptar FROM tbl_ADC
WHERE LEN(captarcode) > 12
)
SELECT  CaptarContractId,
        ConnectionId,
        EAN,
        StartDate,
        EndDate,
        ValidStartDate,
        ValidEndDate,
        NBCode,
        cte.captarcode,
        adc.CaptarCode,
        EanCaptarCode,
        FysicalCapicity,
        FysicalStatus,
        FysicalStatusDescription,
        ContractModel ,
        VatCategoryId,
        ContractModelDescription,
        CaptarNBId,
        CaptarId,
        NBId,
        AmountYear,
        AmountDay,
        AmountAPYear,
        AmountAPMonth,
        AmountAPDay,
        AmountCaptar,
        AmountConnectionService,
        AmountMeterRent,
        AmountSystemService,
        AmountFixedCharge,
        VATpc,
        CaptarNBStartDate,
        CaptarNBEndDate,
        CaptarNBValidStartDate,
        CaptarNBValidEndDate FROM CTE
LEFT JOIN captarGroup AS adc ON CTE.EanCaptarCode = adc.shortCaptar

De ingelezen data wordt opgeslagen in de database in udq.captar:

cap_tar_contract_id
cap_tar_nb_id
connection_id
ean
start_date
end_date
valid_start_date
valid_end_date
nb_code
cap_id
cap_tar_code
ean_cap_tar_code
physical_capacity
physical_status
physical_status_description
contract_model
vat_category_id
contract_model_description
cap_tar_id
nb_id
amount_year
amount_day
amount_ap_year
amount_ap_month
amount_ap_day
amount_cap_tar
amount_connection_service
amount_meter_rent
amount_system_service
amount_fixed_charge
vat_pc
cap_tar_nb_start_date
cap_tar_nb_end_date
cap_tar_nb_valid_start_date
cap_tar_nb_valid_end_date

Controles

Bij het inlezen wordt gecontroleerd of een Capacity Tariff voor een specifieke cap_tar_contract_id en cap_tar_nb_id al is ingelezen, mocht dit zo zijn dan wordt de betreffende Capacity Tariff bijgewerkt met de aangeleverde data.