pears.NetReportReceipts

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
}