pears.NetOwnerRateDetails

CREATE PROCEDURE "pears"."NetOwnerRateDetails"( IN "pWebUserId" CHAR(20),IN "pTempJobTypeID" CHAR(20) ) 
RESULT( "ObjID" CHAR(20),"TempPayBandID" CHAR(20),"Description" CHAR(50),"Units" CHAR(20),"Pay_Rate" CHAR(10),"Charge_Rate" CHAR(10) ) 
BEGIN
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    RETURN
  END IF;
  IF "isnull"("pTempJobTypeID",'') = '' THEN
    SELECT 'New','','','','',''
  ELSE
    SELECT "r"."TempJobRateID" AS "ObjID",
      "b"."TempPayBandID" AS "TempPayBandID",
      "b"."Description" AS "Description",
      "b"."Unit" AS "Units",
      "trim"("str"("r"."payrate",12,2)) AS "Pay_Rate",
      "trim"("str"("r"."chargerate",12,2)) AS "Charge_Rate"
      FROM "TempJobRate" AS "r" KEY JOIN "TempPayBand" AS "b" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
      WHERE "r"."vacancyid" = "pTempJobTypeID" AND "company"."divisionid" = "divid"
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRateDetails" IS 
{CREATE PROCEDURE pears."NetOwnerRateDetails"( IN "pWebUserId" CHAR(20),IN "pTempJobTypeID" CHAR(20) ) 
RESULT( "ObjID" CHAR(20),"TempPayBandID" CHAR(20),"Description" CHAR(50),"Units" CHAR(20),"Pay_Rate" CHAR(10),"Charge_Rate" CHAR(10) ) 
BEGIN
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    RETURN
  END IF;
  IF "isnull"("pTempJobTypeID",'') = '' THEN
    SELECT 'New','','','','',''
  ELSE
    SELECT "r"."TempJobRateID" AS "ObjID",
      "b"."TempPayBandID" AS "TempPayBandID",
      "b"."Description" AS "Description",
      "b"."Unit" AS "Units",
      "trim"("str"("r"."payrate",12,2)) AS "Pay_Rate",
      "trim"("str"("r"."chargerate",12,2)) AS "Charge_Rate"
      FROM "TempJobRate" AS "r" KEY JOIN "TempPayBand" AS "b" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
      WHERE "r"."vacancyid" = "pTempJobTypeID" AND "company"."divisionid" = "divid"
  END IF
END
}