pears.NetReportDebtorsDetailed [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetReportDebtorsDetailed ======
<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"."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
}
</code>