====== pears.NetReportDoubtfulDebtors ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetReportDoubtfulDebtors"( in "pWebUserID" char(20) ) 
result( "Title__Hr" char(50),"Client__S1" char(50),"InvoiceCompanyID__H" char(20),"Invoice_No__B2" char(50),"Last_Receipt_Date__D" date,"Last_Unpaid_Invoice_Date__DH" integer,"Days_Elapsed__IH" integer,"OutstandingAmount__IH" decimal(10,2),"Days_31_to_40__MR" decimal(10,2),"Days_41_to_45__MR" decimal(10,2),"Days_46_to_60__MR" decimal(10,2),"Days_61_to_90__MR" decimal(10,2),"Days_over_90__MR" decimal(10,2) ) 
begin
  declare "DivID" char(20);
  declare "rTitle" char(50);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    "CliCode" char(12) null,
    "Last_Receipt" date null,
    "Last_Unpaid_Invoice" date null,
    ) not transactional;
  set "DivID" = (select first "staff"."divisionid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  insert into "cos"( "companyid","clicode" ) select "companyid","clientcode" from "company" where "divisionid" = "divid" and "invoiceaddress" = 1 and "trim"("isnull"("clientcode",'')) <> '';
  update "cos" set "Last_Receipt" = (select "max"("AccountingDate") from "IQacDocument" where "DocumentClass" = 'Payment' and "AccountCode" = "clicode");
  delete from "cos" where "isnull"("datediff"("day","Last_Receipt",current date),31) <= 30;
  update "cos" set "Last_unpaid_invoice" = (select "max"("AccountingDate") from "IQacDocument" where "DocumentClass" = 'Invoice' and "AccountCode" = "cliCode" and "IQacDocumentOutstandingAmount"("DocumentID",null) > 0);
  delete from "cos" where "last_unpaid_invoice" is null;
  delete from "cos" where "datediff"("day","Last_Unpaid_Invoice",current date) <= 45;
  set "rTitle" = '';
  select "rTitle",
    "company"."name" as "Client",
    "cos"."CompanyID" as "InvoiceCompanyID",
    "d"."OurRef" as "Invoice_No",
    "cos"."last_receipt" as "Last_Receipt_Date",
    "cos"."last_unpaid_invoice" as "Last_Unpaid_Invoice_Date",
    "DateDiff"("day","d"."AccountingDate",current date) as "Days_Elapsed",
    "IQacDocumentOutstandingAmount"("d"."DocumentID",null) as "OutstandingAmount",
    "sum"((if "Days_Elapsed" between 31 and 40 then "OutstandingAmount" else 0 endif)) as "Days_31_to_40",
    "sum"((if "Days_Elapsed" between 41 and 45 then "OutstandingAmount" else 0 endif)) as "Days_41_to_45",
    "sum"((if "Days_Elapsed" between 46 and 60 then "OutstandingAmount" else 0 endif)) as "Days_46_to_60",
    "sum"((if "Days_Elapsed" between 61 and 90 then "OutstandingAmount" else 0 endif)) as "Days_61_to_90",
    "sum"((if "Days_Elapsed" >= 91 then "OutstandingAmount" else 0 endif)) as "Days_over_90"
    from "IQacDocument" as "d" join "cos" on "d"."accountcode" = "cos"."clicode" join "Company" on "company"."companyid" = "cos"."CompanyID"
    where "OutstandingAmount" <> 0
    group by grouping sets((),("InvoiceCompanyID","Client","Last_Receipt_Date","d"."AccountCode","Last_Unpaid_Invoice_Date"),("InvoiceCompanyID","Client","d"."OurRef","DocumentID","d"."AccountingDate"))
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportDoubtfulDebtors" IS 
{create procedure pears."NetReportDoubtfulDebtors"( in "pWebUserID" char(20) ) 
result( "Title__Hr" char(50),"Client__S1" char(50),"InvoiceCompanyID__H" char(20),"Invoice_No__B2" char(50),"Last_Receipt_Date__D" date,"Last_Unpaid_Invoice_Date__DH" integer,"Days_Elapsed__IH" integer,"OutstandingAmount__IH" decimal(10,2),"Days_31_to_40__MR" decimal(10,2),"Days_41_to_45__MR" decimal(10,2),"Days_46_to_60__MR" decimal(10,2),"Days_61_to_90__MR" decimal(10,2),"Days_over_90__MR" decimal(10,2) ) 
begin
  declare "DivID" char(20);
  declare "rTitle" char(50);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    "CliCode" char(12) null,
    "Last_Receipt" date null,
    "Last_Unpaid_Invoice" date null,
    ) not transactional;
  set "DivID" = (select first "staff"."divisionid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  insert into "cos"( "companyid","clicode" ) select "companyid","clientcode" from "company" where "divisionid" = "divid" and "invoiceaddress" = 1 and "trim"("isnull"("clientcode",'')) <> '';
  update "cos" set "Last_Receipt" = (select "max"("AccountingDate") from "IQacDocument" where "DocumentClass" = 'Payment' and "AccountCode" = "clicode");
  delete from "cos" where "isnull"("datediff"("day","Last_Receipt",current date),31) <= 30;
  update "cos" set "Last_unpaid_invoice" = (select "max"("AccountingDate") from "IQacDocument" where "DocumentClass" = 'Invoice' and "AccountCode" = "cliCode" and "IQacDocumentOutstandingAmount"("DocumentID",null) > 0);
  delete from "cos" where "last_unpaid_invoice" is null;
  delete from "cos" where "datediff"("day","Last_Unpaid_Invoice",current date) <= 45;
  set "rTitle" = '';
  select "rTitle",
    "company"."name" as "Client",
    "cos"."CompanyID" as "InvoiceCompanyID",
    "d"."OurRef" as "Invoice_No",
    "cos"."last_receipt" as "Last_Receipt_Date",
    "cos"."last_unpaid_invoice" as "Last_Unpaid_Invoice_Date",
    "DateDiff"("day","d"."AccountingDate",current date) as "Days_Elapsed",
    "IQacDocumentOutstandingAmount"("d"."DocumentID",null) as "OutstandingAmount",
    "sum"((if "Days_Elapsed" between 31 and 40 then "OutstandingAmount" else 0 endif)) as "Days_31_to_40",
    "sum"((if "Days_Elapsed" between 41 and 45 then "OutstandingAmount" else 0 endif)) as "Days_41_to_45",
    "sum"((if "Days_Elapsed" between 46 and 60 then "OutstandingAmount" else 0 endif)) as "Days_46_to_60",
    "sum"((if "Days_Elapsed" between 61 and 90 then "OutstandingAmount" else 0 endif)) as "Days_61_to_90",
    "sum"((if "Days_Elapsed" >= 91 then "OutstandingAmount" else 0 endif)) as "Days_over_90"
    from "IQacDocument" as "d" join "cos" on "d"."accountcode" = "cos"."clicode" join "Company" on "company"."companyid" = "cos"."CompanyID"
    where "OutstandingAmount" <> 0
    group by grouping sets((),("InvoiceCompanyID","Client","Last_Receipt_Date","d"."AccountCode","Last_Unpaid_Invoice_Date"),("InvoiceCompanyID","Client","d"."OurRef","DocumentID","d"."AccountingDate"))
end
}
</code>