pears.NetTimesheetRates [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.NetTimesheetRates ======
<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"."NetTimesheetRates"( in "pWebUserID" char(20),in "pTempTimesheetID" char(20) ) 
result( "TempTimesheetLineID" char(20),"IsExpenses" smallint,"BandDescription" char(50),"UnitDescription" char(100),"Units" decimal(12,2),"Rate" decimal(12,2),"Total" decimal(12,2),"Rate2" decimal(12,2),"Total2" decimal(12,2),"ShiftDate" date ) 
// IQXWeb
begin
  declare "uclass" char(20);
  declare "divid" char(20);
  set "uclass" = (select first "iqxnetuserclassid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
  if "uclass" = 'CLIENT' then
    select "TempTimesheetLine"."TempTimesheetLineID",
      (if "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' or "isnull"("temppayband"."isexpenses",0) = 1 then 1 else 0 endif) as "IsExpenses",
      "temppayband"."description",
      (if "IsExpenses" = 1 then "temptimesheetline"."description" else "temppayband"."unit" endif),
      "nullif"("temptimesheetline"."unitscharged",0.0) as "units","nullif"("temptimesheetline"."chargerate",0.0) as "rate","isnull"("units"*"rate",0.0) as "total",null,null,
      "date"("temptimesheetline"."shifttime")
      from "temptimesheetline" key join "temppayband" where "temptimesheetline"."temptimesheetid" = "pTempTimesheetID"
      order by "temptimesheetline"."linenumber" asc
  else if "uclass" = 'OWNER' then
      select "TempTimesheetLine"."TempTimesheetLineID",
        (if "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' or "isnull"("temppayband"."isexpenses",0) = 1 then 1 else 0 endif) as "IsExpenses",
        "temppayband"."description",
        (if "IsExpenses" = 1 then "temptimesheetline"."description" else "temppayband"."unit" endif),
        "nullif"("temptimesheetline"."unitspaid",0.0) as "units","nullif"("temptimesheetline"."payrate",0.0) as "rate","isnull"("units"*"rate",0.0) as "total",
        "nullif"("temptimesheetline"."chargerate",0.0) as "rate2","isnull"("units"*"rate2",0.0) as "total2",
        "date"("temptimesheetline"."shifttime")
        from "temptimesheetline" key join "temppayband" where "temptimesheetline"."temptimesheetid" = "pTempTimesheetID"
        order by "temptimesheetline"."linenumber" asc
    else
      select "TempTimesheetLine"."TempTimesheetLineID",
        (if "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' or "isnull"("temppayband"."isexpenses",0) = 1 then 1 else 0 endif) as "IsExpenses",
        "temppayband"."description",
        (if "IsExpenses" = 1 then "temptimesheetline"."description" else "temppayband"."unit" endif),
        "nullif"("temptimesheetline"."unitspaid",0.0) as "units","nullif"("temptimesheetline"."payrate",0.0) as "rate","isnull"("units"*"rate",0.0) as "total",null,null,
        "date"("temptimesheetline"."shifttime")
        from "temptimesheetline" key join "temppayband" where "temptimesheetline"."temptimesheetid" = "pTempTimesheetID"
        order by "temptimesheetline"."linenumber" asc
    end if
  end if
end /* TEST
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.candidate','test');
expect TempTimesheetLineID=test,IsExpenses=0,BandDescription=TestPayBandOne,UnitDescription=Day;
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.client','test');
expect TempTimesheetLineID=test,IsExpenses=0,BandDescription=TestPayBandOne,UnitDescription=Day;
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.owner','test');
expect TempTimesheetLineID=test,IsExpenses=0,BandDescription=TestPayBandOne,UnitDescription=Day;
update temppayband set payrollflag='EXPENSES' where temppaybandid='TEST1';
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.candidate','test');
expect TempTimesheetLineID=test,IsExpenses=1,BandDescription=TestPayBandOne;
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.client','test');
expect TempTimesheetLineID=test,IsExpenses=1,BandDescription=TestPayBandOne;
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.owner','test');
expect TempTimesheetLineID=test,IsExpenses=1,BandDescription=TestPayBandOne;
update temppayband set payrollflag='99' where temppaybandid='TEST1';
*/
/* DOC
2018-09-11 PC test routines

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTimesheetRates" IS 
{create PROCEDURE pears."NetTimesheetRates"(in pWebUserID char(20),in pTempTimesheetID char(20))
result(TempTimesheetLineID char(20),IsExpenses smallint,BandDescription char(50),UnitDescription char(100),Units decimal(12,2),Rate decimal(12,2),Total decimal(12,2),Rate2 decimal(12,2),Total2 decimal(12,2),ShiftDate date)
// IQXWeb
begin
  declare uclass char(20);
  declare divid char(20);
  set uclass=(select first iqxnetuserclassid from iqxnetuser where iqxnetuserid = pWebUserID);
  if uclass = 'CLIENT' then
    select TempTimesheetLine.TempTimesheetLineID,
      (if isnull(temppayband.payrollflag,'') = 'EXPENSES' or isnull(temppayband.isexpenses,0) = 1 then 1 else 0
      endif) as IsExpenses,temppayband.description,
      (if IsExpenses = 1 then temptimesheetline.description else temppayband.unit
      endif),nullif(temptimesheetline.unitscharged,0.0) as units,nullif(temptimesheetline.chargerate,0.0) as rate,isnull(units*rate,0.0) as total,null,null,
	  date(temptimesheetline.shifttime)
      from temptimesheetline key join temppayband where temptimesheetline.temptimesheetid = pTempTimesheetID order by
      temptimesheetline.linenumber asc
  else if uclass = 'OWNER' then
      select TempTimesheetLine.TempTimesheetLineID,
        (if isnull(temppayband.payrollflag,'') = 'EXPENSES' or isnull(temppayband.isexpenses,0) = 1 then 1 else 0
        endif) as IsExpenses,temppayband.description,
        (if IsExpenses = 1 then temptimesheetline.description else temppayband.unit
        endif),nullif(temptimesheetline.unitspaid,0.0) as units,nullif(temptimesheetline.payrate,0.0) as rate,isnull(units*rate,0.0) as total,
        nullif(temptimesheetline.chargerate,0.0) as rate2,isnull(units*rate2,0.0) as total2,
		date(temptimesheetline.shifttime)
        from temptimesheetline key join temppayband where temptimesheetline.temptimesheetid = pTempTimesheetID order by
        temptimesheetline.linenumber asc
    else
      select TempTimesheetLine.TempTimesheetLineID,
        (if isnull(temppayband.payrollflag,'') = 'EXPENSES' or isnull(temppayband.isexpenses,0) = 1 then 1 else 0
        endif) as IsExpenses,temppayband.description,
        (if IsExpenses = 1 then temptimesheetline.description else temppayband.unit
        endif),nullif(temptimesheetline.unitspaid,0.0) as units,nullif(temptimesheetline.payrate,0.0) as rate,isnull(units*rate,0.0) as total,null,null,
		date(temptimesheetline.shifttime)
        from temptimesheetline key join temppayband where temptimesheetline.temptimesheetid = pTempTimesheetID order by
        temptimesheetline.linenumber asc
    end if
  end if
end

/* TEST
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.candidate','test');
expect TempTimesheetLineID=test,IsExpenses=0,BandDescription=TestPayBandOne,UnitDescription=Day;
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.client','test');
expect TempTimesheetLineID=test,IsExpenses=0,BandDescription=TestPayBandOne,UnitDescription=Day;
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.owner','test');
expect TempTimesheetLineID=test,IsExpenses=0,BandDescription=TestPayBandOne,UnitDescription=Day;
update temppayband set payrollflag='EXPENSES' where temppaybandid='TEST1';
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.candidate','test');
expect TempTimesheetLineID=test,IsExpenses=1,BandDescription=TestPayBandOne;
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.client','test');
expect TempTimesheetLineID=test,IsExpenses=1,BandDescription=TestPayBandOne;
select TempTimesheetLineID,IsExpenses,BandDescription,UnitDescription from NetTimesheetRates('test.owner','test');
expect TempTimesheetLineID=test,IsExpenses=1,BandDescription=TestPayBandOne;
update temppayband set payrollflag='99' where temppaybandid='TEST1';
*/

/* DOC
2018-09-11 PC test routines

*/
}
</code>