-- Ursprünglich bei Input Ostalb, 
-- auch HPH Bersenbrück Wohnassistenz, ChristopherusHausMID

-- funktioniert nur für monatliche Einzel-ServiceInvoice

select 
p.LastName as Nachname, 
p.FirstName as Vorname,  
cb2sc.ApprovedStartDate as 'Bewilligt von', 
cb2sc.ApprovedEndDate as 'Bewilligt bis',
o.`Name` as 'Kostentraeger'
 from `person` p 
 join `customer` cust on p.Oid = cust.PersonOid 
 join `supportconcept` sc on cust.Oid = sc.CustomerOid 
 join `costbearer2supportconcept` cb2sc on sc.Oid = cb2sc.SupportConceptOid 
 join `costbearer` cb on cb2sc.`CostBearerOid` = cb.`Oid` 
 join `organisation` o on cb.`Oid` = o.`CostBearerOid` 
 WHERE cb2sc.ApprovedEndDate is not null and cust.IsActive = 1 and sc.IsActive = 1
 and cb2sc.oid not in
 (select 
 c2s.oid
 from
 person p
 inner join customer c on c.personoid = p.oid
 inner join supportconcept sc on sc.customeroid = c.oid
 inner join costbearer2supportconcept c2s on c2s.supportconceptoid = sc.oid
 inner join invoicebase ib on ib.costbearer2supportconceptoid = c2s.oid
 inner join serviceinvoice si on si.invoicebaseoid = ib.oid
 inner join serviceinvoiceperiod sip on sip.serviceinvoiceoid = si.oid
where ib.AccountingPeriodStart >= ':Monat_Start' and ib.AccountingPeriodStart < ':Monat_End' and ib.IsActive = 1
order by p.Lastname, p.Firstname )