create or replace function f_GetDaysForLastMonthInvoices(
custID in invoiceheader.billtocustomer_link_id%type,
dayNR in day.daynumber%type
)
  return number is
  mindate DATE;
	maxdate DATE;
  Result number;
begin
  select min(ih.startdate) into mindate
  from invoiceheader ih, status st where ih.billtocustomer_link_id = custID
   and st.status_id = ih.status_id and st.code = '2' --Finalized
   and ih.invoicedate between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),-1))
   and not ih.startdate is null;
  select max(ih.enddate) into maxdate
  from invoiceheader ih, status st where ih.billtocustomer_link_id = custID
   and st.status_id = ih.status_id and st.code = '2' --Finalized
   and ih.invoicedate between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),-1))
   and not ih.enddate is null;
  SELECT COUNT(1) into Result FROM dual
  WHERE TO_CHAR(mindate + LEVEL - 1, 'D', 'nls_date_language = ENGLISH') IN TO_CHAR(dayNR)
  CONNECT BY TRUNC(mindate) + LEVEL - 1 <= TRUNC(maxdate);
  return Result;
end f_GetDaysForLastMonthInvoices;
/
