====== pears.NetProvTimesheetExpenseRatesSelect ======
<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"."NetProvTimesheetExpenseRatesSelect"( in "pWebUserID" char(20),in "pTempProvTimesheetID" char(20) ) 
result( "TempPaybandID" char(20),"description" char(50),"Units" char(20),"SortOrder" char(10) ) 
// IQXWeb
begin
  if "pTempProvTimesheetID" is null or not "pTempProvTimesheetID" = any(select "TempProvTimeSheetID" from "tempprovtimesheet" key join "vacancy" key join "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID" union select "TempProvTimeSheetID" from "tempprovtimesheet" key join "person" key join "iqxnetuserlink" key join "iqxnetuser" as "u" where "u"."iqxnetuserid" = "pWebUserID") then
    select '','','','' where 0 = 1; -- empty result
    return
  end if;
  select distinct
    "b"."temppaybandid",
    "b"."description",
    "b"."unit",
    "b"."sortorder"
    from "temppayband" as "b"
    --key join tempprovtimesheetline 
    where("isexpenses" = 1 or "b"."PayrollFlag" like '%expenses%')
    and("b"."divisionid" is null or "b"."divisionid" = (select "p"."divisionid" from "TempProvTimeSheet" as "t" key join "person" as "p" where "t"."tempprovtimesheetid" = "pTempProvTimesheetID"))
    order by "b"."sortorder" asc
end /* DOC
2016-11-09 PC insert an expense line into tempprovtimesheetline
2016-11-28 PC modify filter for selecting expense lines
2017-06-09 PC modify to select all rates in a division since linking it to a timesheet only works if there is a value there to start with
2018-12-17 MHS fix faulty division logic and tests

IW-95
IW-102

Inputs:
pWebUserID
pTempProvTimesheetID

Outputs:
TempPaybandID
description
Units

Tests:
invalid pWebUserID
valid pWebUserID and invalid pTempProvTimesheetID
all valid but no expense line
all valid

*/
/* TEST
call NetTestSetup('');
select * from NetProvTimesheetExpenseRatesSelect(null,null);
expect EOF;
select * from NetProvTimesheetExpenseRatesSelect('test.client',null);
expect EOF;
insert into tempprovtimesheet (tempprovtimesheetid,period,periodlength,personid) on existing update values ('TEST',200101,'W','TEST');
insert into temppayband (TempPaybandID,Description,Unit,isExpenses,DivisionID,sortorder) on existing update defaults off values ('EXPENSETEST','Expenses','Amount',1,null,-1);
select * from NetProvTimesheetExpenseRatesSelect('test.client','TEST') order by isnull(sortorder,0);
expect TempPaybandID=EXPENSETEST;
delete from temppayband where temppaybandid='EXPENSETEST';


*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetExpenseRatesSelect" IS 
{create PROCEDURE pears."NetProvTimesheetExpenseRatesSelect"(in pWebUserID char(20),in pTempProvTimesheetID char(20))
RESULT(TempPaybandID char(20),description char(50),Units char(20),SortOrder char(10))
// IQXWeb
BEGIN  
    if pTempProvTimesheetID is null or pTempProvTimesheetID not in (select TempProvTimeSheetID from tempprovtimesheet key join vacancy key join employment key join iqxnetuserlink where iqxnetuserid=pWebUserID union select TempProvTimeSheetID from tempprovtimesheet key join person key join iqxnetuserlink key join iqxnetuser u where u.iqxnetuserid=pWebUserID) then
        select '','','','' where 0=1; -- empty result
        return;
    end if;
    select distinct
        b.temppaybandid,
        b.description,
        b.unit,
        b.sortorder
    from 
        temppayband b 
        --key join tempprovtimesheetline 
    where 
        (isexpenses=1 or b.PayrollFlag like '%expenses%' )
        and
        (b.divisionid is null or b.divisionid=(select p.divisionid from TempProvTimeSheet t key join person p where t.tempprovtimesheetid=pTempProvTimesheetID))
    order by 
        b.sortorder
END

/* DOC
2016-11-09 PC insert an expense line into tempprovtimesheetline
2016-11-28 PC modify filter for selecting expense lines
2017-06-09 PC modify to select all rates in a division since linking it to a timesheet only works if there is a value there to start with
2018-12-17 MHS fix faulty division logic and tests

IW-95
IW-102

Inputs:
    pWebUserID
    pTempProvTimesheetID

Outputs:
    TempPaybandID
    description
    Units

Tests:
    invalid pWebUserID
    valid pWebUserID and invalid pTempProvTimesheetID
    all valid but no expense line
    all valid

*/

/* TEST
call NetTestSetup('');
select * from NetProvTimesheetExpenseRatesSelect(null,null);
expect EOF;
select * from NetProvTimesheetExpenseRatesSelect('test.client',null);
expect EOF;
insert into tempprovtimesheet (tempprovtimesheetid,period,periodlength,personid) on existing update values ('TEST',200101,'W','TEST');
insert into temppayband (TempPaybandID,Description,Unit,isExpenses,DivisionID,sortorder) on existing update defaults off values ('EXPENSETEST','Expenses','Amount',1,null,-1);
select * from NetProvTimesheetExpenseRatesSelect('test.client','TEST') order by isnull(sortorder,0);
expect TempPaybandID=EXPENSETEST;
delete from temppayband where temppaybandid='EXPENSETEST';


*/
}
</code>