31 lines
1.3 KiB
Plaintext
31 lines
1.3 KiB
Plaintext
|
-- 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 )
|