pears.NetReportDebtorsDetailed

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetReportDebtorsDetailed"( IN "pWebUserID" CHAR(20),IN "Cutoff_Date__D31a" CHAR(20),IN "DSO_Period__I30" INTEGER ) 
RESULT( "Title__Hr" CHAR(50),"CompanyID__H" CHAR(20),"Client__S1" CHAR(50),"Invoice_No__BT2" CHAR(50),"Days_Elapsed__IH" INTEGER,"OutstandingAmount__IH" DECIMAL(10,2),"Credit_Limit__R" INTEGER,"DSO__IR" INTEGER,"Days_0_to_14__MR" DECIMAL(10,2),"Days_15_to_30__MR" 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),"Total__MR" DECIMAL(10,2) ) 
BEGIN
  DECLARE "DivID" CHAR(20);
  DECLARE "CompID" CHAR(20);
  DECLARE "CutOffDate" DATE;
  DECLARE "rTitle" CHAR(50);
  SET "DivID" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  SET "CutOffDate" = "iqxnetstringtodate"("Cutoff_Date__D31a");
  SET "rTitle" = "dateformat"("cutoffdate",'dd/mm/yyyy');
  SELECT "rTitle",
    "GetInvoiceCompanyID"("d"."AccountCode") AS "InvoiceCompanyID",
    "company"."name" AS "Client",
    "d"."OurRef" AS "Invoice_No",
    "DateDiff"("day","d"."AccountingDate","CutoffDate") AS "Days_Elapsed",
    "IQacDocumentOutstandingAmount"("d"."DocumentID","CutOffDate") AS "OutstandingAmount",
    (SELECT "CreditLimit" FROM "CompanyAccount" KEY JOIN "company" WHERE "company"."companyid" = "InvoiceCompanyID") AS "Credit_Limit",
    "NetDebtorsDaySalesOutstanding"("d"."AccountCode","DSO_Period__I30","CutOffDate") AS "DSO",
    "sum"((IF "Days_Elapsed" <= 14 THEN "OutstandingAmount" ELSE 0 endif)) AS "Days_0_to_14",
    "sum"((IF "Days_Elapsed" BETWEEN 15 AND 30 THEN "OutstandingAmount" ELSE 0 endif)) AS "Days_15_to_30",
    "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",
    "sum"("OutstandingAmount") AS "Total"
    FROM "IQacDocument" AS "d" JOIN "company" ON "company"."companyid" = "InvoiceCompanyID"
    WHERE "company"."divisionid" = "DivID" AND "OutstandingAmount" <> 0 AND "d"."AccountingDate" <= "CutoffDate"
    GROUP BY GROUPING sets((),("InvoiceCompanyID","Client","dso","Credit_Limit","d"."AccountCode"),("InvoiceCompanyID","Client","d"."AccountCode","d"."OurRef","d"."accountingDate","d"."DocumentID"))
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportDebtorsDetailed" IS 
{CREATE PROCEDURE pears."NetReportDebtorsDetailed"( IN "pWebUserID" CHAR(20),IN "Cutoff_Date__D31a" CHAR(20),IN "DSO_Period__I30" INTEGER ) 
RESULT( "Title__Hr" CHAR(50),"CompanyID__H" CHAR(20),"Client__S1" CHAR(50),"Invoice_No__BT2" CHAR(50),"Days_Elapsed__IH" INTEGER,"OutstandingAmount__IH" DECIMAL(10,2),"Credit_Limit__R" INTEGER,"DSO__IR" INTEGER,"Days_0_to_14__MR" DECIMAL(10,2),"Days_15_to_30__MR" 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),"Total__MR" DECIMAL(10,2) ) 
BEGIN
  DECLARE "DivID" CHAR(20);
  DECLARE "CompID" CHAR(20);
  DECLARE "CutOffDate" DATE;
  DECLARE "rTitle" CHAR(50);
  SET "DivID" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  SET "CutOffDate" = "iqxnetstringtodate"("Cutoff_Date__D31a");
  SET "rTitle" = "dateformat"("cutoffdate",'dd/mm/yyyy');
  SELECT "rTitle",
    "GetInvoiceCompanyID"("d"."AccountCode") AS "InvoiceCompanyID",
    "company"."name" AS "Client",
    "d"."OurRef" AS "Invoice_No",
    "DateDiff"("day","d"."AccountingDate","CutoffDate") AS "Days_Elapsed",
    "IQacDocumentOutstandingAmount"("d"."DocumentID","CutOffDate") AS "OutstandingAmount",
    (SELECT "CreditLimit" FROM "CompanyAccount" KEY JOIN "company" WHERE "company"."companyid" = "InvoiceCompanyID") AS "Credit_Limit",
    "NetDebtorsDaySalesOutstanding"("d"."AccountCode","DSO_Period__I30","CutOffDate") AS "DSO",
    "sum"((IF "Days_Elapsed" <= 14 THEN "OutstandingAmount" ELSE 0 endif)) AS "Days_0_to_14",
    "sum"((IF "Days_Elapsed" BETWEEN 15 AND 30 THEN "OutstandingAmount" ELSE 0 endif)) AS "Days_15_to_30",
    "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",
    "sum"("OutstandingAmount") AS "Total"
    FROM "IQacDocument" AS "d" JOIN "company" ON "company"."companyid" = "InvoiceCompanyID"
    WHERE "company"."divisionid" = "DivID" AND "OutstandingAmount" <> 0 AND "d"."AccountingDate" <= "CutoffDate"
    GROUP BY GROUPING sets((),("InvoiceCompanyID","Client","dso","Credit_Limit","d"."AccountCode"),("InvoiceCompanyID","Client","d"."AccountCode","d"."OurRef","d"."accountingDate","d"."DocumentID"))
END
}