Contract Switch Periods

Contract Switch Periods zijn de contract en switch perioden vastgelegd bij een connection uit ERP. Alle contract en switch perioden vanaf 01-01-2021 zijn beschikbaar.

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

erp_csp.sql

use dveperp_prod
go

select c.ConnectionId,
       tc.ContractId,
       sp.SwitchPeriodId,
       c.EAN,
       tc.StartDate as ContractStartDate,
       tc.EndDate as ContractEndDate,
       d.DebtorId,
       d.DebtorReference,
       sp.StartDate,
       sp.EndDate,
       sp.SwitchPeriodType,
       sp.TransactionReason,
       w.WozId,
       w.Description as WOZ,
       o.OrganizationId,
       pr.PropositionId,
       pr.Description as Proposition,
       icoc.CollectiveId,
       o_collective.Name as Collective
from tbl_Connection as C
join itbl_Contract_Connection icc       on   c.ConnectionId = icc.ConnectionId
join tbl_Contract tc                    on icc.ContractId = tc.ContractId
join itbl_Contract_Proposition icp      on tc.ContractId = icp.ContractId
join tbl_Proposition pr                 on icp.PropositionId = pr.PropositionId
join itbl_Contract_Debtor icb           on tc.ContractId = icb.ContractId
join tbl_Debtor d                       on icb.DebtorId = d.DebtorId
join itbl_Connection_SwitchPeriod ics   on ics.ConnectionId = c.ConnectionId
join tbl_SwitchPeriod sp                on sp.SwitchPeriodId = ics.SwitchPeriodId
join itbl_Woz_Connection iwc            on c.ConnectionId = iwc.ConnectionId
join itbl_Organization_Woz iow          on iwc.WozId = iow.WozId
join tbl_Organization o                 on iow.OrganizationId = o.OrganizationId
join itbl_Collective_Organization ico   on o.OrganizationId = ico.OrganizationId
join itbl_Company_Collective icoc       on ico.CollectiveId = icoc.CollectiveId
join tbl_Organization o_collective      on icoc.CollectiveId = o_collective.OrganizationId
join tbl_Woz w                          on iow.WozId = w.WozId
where icc.ValidStartDate <= getdate() and icc.ValidEndDate > getdate()
and  icb.ValidStartDate <= getdate() and icb.ValidEndDate > getdate()
and  ics.ValidStartDate <= getdate() and ics.ValidEndDate > getdate()
and  iow.ValidStartDate <= getdate() and iow.ValidEndDate > getdate()
and  iwc.ValidStartDate <= getdate() and iwc.ValidEndDate > getdate()
and  icp.ValidStartDate <= getdate() and icp.ValidEndDate > getdate()
and  ico.ValidStartDate <= getdate() and ico.ValidEndDate > getdate()
and  icoc.ValidStartDate <= getdate() and icoc.ValidEndDate > getdate()
and  (tc.StartDate >= '2021-01-01' or tc.EndDate > '2021-01-01')
;

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

connection_id
contract_id
switch_period_id
ean
contract_start_date
contract_end_date
debtor_id
debtor_reference
start_date
end_date
switch_period_type
transaction_reason
woz_id
woz
organization_id
proposition_id
proposition
collective_id
collective
datafile_id

Controles

Bij het inlezen wordt gecontroleerd of een Contract Switch Period voor een specifieke connection_id, contract_id en switch_period_id al is ingelezen, mocht dit zo zijn dan wordt de betreffende Contract Switch Period bijgewerkt met de aangeleverde data.