====== pears.NetClientInvoiceTotal ======
<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"."NetClientInvoiceTotal"( in "pWebUserID" char(20) ) 
result( "pBalance" decimal(10,2),"pSum" decimal(10,2) ) 
begin
  declare "pClientCode" char(20);
  declare "pCompanyID" char(20);
  set "pClientCode" = (select top 1 "c"."clientcode" from "iqxnetuserlink" as "l" key join "employment" as "e" key join "company" as "c" where "l"."iqxnetuserid" = "pWebUserID");
  set "pCompanyID" = (select top 1 "e"."companyid" from "iqxnetuserlink" as "l" key join "employment" as "e" where "l"."iqxnetuserid" = "pWebUserID");
  select "amount",
    "sum"("IQacDocumentOutstandingAmount"("documentid",null)) as "Amount"
    from "iqacdocument"
    where "ledgerid" = 'Sales'
    and(("accountcode" = "pClientCode") or("xrefid2" = "pCompanyID")) and "fullyallocated" is null
end /* DOC
2016-11-30 PC Doc and Test
2017-08-03 PC possible more than one row in subquery resolved 

Inputs:
pWebUserID

Outputs:
pBalance

Tests:
invalid pWebUserID
valid pWebUserID

*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
insert into iqacaccount (ledgerid,accountcode,name) on existing update values ('Sales','TESTAC','TestAccount');
insert into iqacdocument (documentid,LedgerID,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype,amount) on existing update values ('TEST1','Sales','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Invoice',50.00);
insert into iqacdocument (documentid,LedgerID,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype,amount) on existing update values ('TEST2','Sales','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Credit Note',10.00);
select isnull(pBalance,0) as A,isnull(pSum,0) as B from NetClientInvoiceTotal(null);
expect A=0,B=0;
select isnull(pBalance,0) as A,isnull(pSum,0) as B from NetClientInvoiceTotal('test.client');
expect A=60,B=60;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientInvoiceTotal" IS 
{create PROCEDURE pears."NetClientInvoiceTotal"(in pWebUserID char(20))
RESULT(pBalance decimal(10,2),pSum decimal(10,2))
BEGIN
  declare pClientCode char(20);
  declare pCompanyID char(20);
  set pClientCode=(select top 1 c.clientcode from iqxnetuserlink as l key join employment as e key join company as c where l.iqxnetuserid = pWebUserID);
  set pCompanyID=(select top 1 e.companyid from iqxnetuserlink as l key join employment as e where l.iqxnetuserid = pWebUserID);
  select 
    amount,    
    sum(IQacDocumentOutstandingAmount(documentid,null)) as Amount
  from 
    iqacdocument 
  where 
    ledgerid='Sales' 
    and ((accountcode=pClientCode) or (xrefid2=pCompanyID)) and fullyallocated is null;
END

/* DOC
2016-11-30 PC Doc and Test
2017-08-03 PC possible more than one row in subquery resolved 

Inputs:
    pWebUserID

Outputs:
    pBalance

Tests:
    invalid pWebUserID
    valid pWebUserID

*/

/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
insert into iqacaccount (ledgerid,accountcode,name) on existing update values ('Sales','TESTAC','TestAccount');
insert into iqacdocument (documentid,LedgerID,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype,amount) on existing update values ('TEST1','Sales','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Invoice',50.00);
insert into iqacdocument (documentid,LedgerID,AccountCode,XrefID,Xref,accountingdate,documentdate,duedate,documentclass,documenttype,amount) on existing update values ('TEST2','Sales','TESTAC','TEST','P',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'yyyy-mm-dd'),'Invoice','Credit Note',10.00);
select isnull(pBalance,0) as A,isnull(pSum,0) as B from NetClientInvoiceTotal(null);
expect A=0,B=0;
select isnull(pBalance,0) as A,isnull(pSum,0) as B from NetClientInvoiceTotal('test.client');
expect A=60,B=60;
*/
}
</code>