pears.NetReportDoubtfulDebtors

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
}