pears.NetTimesheetRates

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original 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
 
*/
}