====== pears.NetReportReceipts ======
<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"."NetReportReceipts"( in "pWebUserID" char(20),in "From_Date__D31a" char(20),in "To_Date__D0" char(20) ) 
result( "Title__Hr" char(50),"Client__1" char(50),"Amount__MR" decimal(10,2),"Method__2" char(50),"Paying_In_Slip" char(20),"Cheque_Number" char(50),"Allocation_Date__DR" date,"Allocation__MR" decimal(10,2),"Invoice__R" char(20),"Unallocated__MR" decimal(10,2) ) 
begin
  declare "DivID" char(20);
  declare "fDate" date;
  declare "tDate" date;
  declare "DocID" char(12);
  declare "RecieptAmount" decimal(10,2);
  declare "rTitle" char(50);
  set "DivID" = (select first "staff"."divisionid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  set "fDate" = "iqxnetstringtodate"("From_Date__D31a");
  set "tDate" = "iqxnetstringtodate"("To_Date__D0");
  set "rTitle" = "dateformat"("fDate",'dd/mm/yyyy')+' to '+"dateformat"("tDate",'dd/mm/yyyy');
  select "rTitle",
    (select "name" from "company" where "company"."companyid" = "GetInvoiceCompanyID"("d"."AccountCode")) as "Client",
    "d"."Amount"*-1 as "Amount",
    (select "name" from "IQacPaymentMethod" where "PaymentMethodCode" = "d"."PaymentMethod") as "Method",
    "d"."OurRef" as "Paying_In_Slip",
    "d"."TheirRef" as "Cheque_Number",
    "a"."AllocationDate" as "Allocation_Date",
    "a"."allocatedAmount" as "Allocation",
    (select "OurRef" from "IqAcDocument" where "DocumentID" = "a"."AllocatedDocumentid") as "Invoice",
    "IQacDocumentOutstandingAmount"("a"."AllocationDocumentID","tDate")*-1 as "Unallocated"
    from "IQacDocument" as "d" left outer join "IQacAllocation" as "a"
    where "a"."AllocationDocumentID" <> "a"."AllocatedDocumentID" and "d"."DocumentClass" = 'Payment'
    and "d"."AccountingDate" between "fDate" and "tDate" and "d"."AccountCode" = any(select "ClientCode" from "company" where "divisionid" = "DivID")
    order by "Client" asc,"Invoice" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportReceipts" IS 
{create procedure pears."NetReportReceipts"( in "pWebUserID" char(20),in "From_Date__D31a" char(20),in "To_Date__D0" char(20) ) 
result( "Title__Hr" char(50),"Client__1" char(50),"Amount__MR" decimal(10,2),"Method__2" char(50),"Paying_In_Slip" char(20),"Cheque_Number" char(50),"Allocation_Date__DR" date,"Allocation__MR" decimal(10,2),"Invoice__R" char(20),"Unallocated__MR" decimal(10,2) ) 
begin
  declare "DivID" char(20);
  declare "fDate" date;
  declare "tDate" date;
  declare "DocID" char(12);
  declare "RecieptAmount" decimal(10,2);
  declare "rTitle" char(50);
  set "DivID" = (select first "staff"."divisionid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  set "fDate" = "iqxnetstringtodate"("From_Date__D31a");
  set "tDate" = "iqxnetstringtodate"("To_Date__D0");
  set "rTitle" = "dateformat"("fDate",'dd/mm/yyyy')+' to '+"dateformat"("tDate",'dd/mm/yyyy');
  select "rTitle",
    (select "name" from "company" where "company"."companyid" = "GetInvoiceCompanyID"("d"."AccountCode")) as "Client",
    "d"."Amount"*-1 as "Amount",
    (select "name" from "IQacPaymentMethod" where "PaymentMethodCode" = "d"."PaymentMethod") as "Method",
    "d"."OurRef" as "Paying_In_Slip",
    "d"."TheirRef" as "Cheque_Number",
    "a"."AllocationDate" as "Allocation_Date",
    "a"."allocatedAmount" as "Allocation",
    (select "OurRef" from "IqAcDocument" where "DocumentID" = "a"."AllocatedDocumentid") as "Invoice",
    "IQacDocumentOutstandingAmount"("a"."AllocationDocumentID","tDate")*-1 as "Unallocated"
    from "IQacDocument" as "d" left outer join "IQacAllocation" as "a"
    where "a"."AllocationDocumentID" <> "a"."AllocatedDocumentID" and "d"."DocumentClass" = 'Payment'
    and "d"."AccountingDate" between "fDate" and "tDate" and "d"."AccountCode" = any(select "ClientCode" from "company" where "divisionid" = "DivID")
    order by "Client" asc,"Invoice" asc
end
}
</code>