pears.NetOwnerPayRateEdit

CREATE PROCEDURE "pears"."NetOwnerPayRateEdit"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pObjID" CHAR(20),IN "pCode" CHAR(20),IN "pParentObject" CHAR(20),IN "pPay_Rate" CHAR(10),IN "pCharge_Rate" CHAR(10),IN "pOldDescription" CHAR(60),IN "pOldUnits" CHAR(20),IN "pOldPay_Rate" CHAR(10),IN "pOldCharge_Rate" CHAR(10) ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "ssql" CHAR(255);
  DECLARE "divid" CHAR(20);
  DECLARE "TJobRateID" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "ssql" = '';
  IF "pCode" = '' THEN
    SELECT '100:~Please enter a valid shift type';
    RETURN
  END IF;
  IF "pPay_Rate" = '' OR("isnumeric"("pPay_rate") = 0) THEN
    SELECT '101:~Please enter a valid Pay Rate';
    RETURN
  END IF;
  IF "pCharge_Rate" = '' OR("isnumeric"("pCharge_rate") = 0) THEN
    SELECT '102:~Please enter a valid Charge Rate';
    RETURN
  END IF;
  IF "isnull"("pPay_Rate",'') <> "isnull"("pOldPay_Rate",'') THEN
    SET "ssql" = "ssql"+',payrate = pPay_Rate'
  END IF;
  IF("isnull"("pCharge_Rate",'') <> "isnull"("pOldCharge_Rate",'')) THEN
    SET "ssql" = "ssql"+',chargerate = pCharge_Rate'
  END IF;
  EXECUTE IMMEDIATE 'update TempJobRate set temppaybandid = pCode'+"ssql"+' where TempJobRateid = pObjID';
  IF @@rowcount = 0 THEN
    SET "TJobRateID" = "uniquekey"("pCode");
    INSERT INTO "TempJobRate"( "TempJobRateID","TempPayBandID","VacancyID","payrate","Chargerate" ) VALUES( "TJobRateID","pCode","pVacancyID","pPay_Rate","pCharge_Rate" ) 
  END IF;
  SELECT '0:TJobRateID~Success'
--select string('0:',TJobRateID,'#',pParentObject,'~Success')
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerPayRateEdit" IS 
{CREATE PROCEDURE pears."NetOwnerPayRateEdit"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pObjID" CHAR(20),IN "pCode" CHAR(20),IN "pParentObject" CHAR(20),IN "pPay_Rate" CHAR(10),IN "pCharge_Rate" CHAR(10),IN "pOldDescription" CHAR(60),IN "pOldUnits" CHAR(20),IN "pOldPay_Rate" CHAR(10),IN "pOldCharge_Rate" CHAR(10) ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "ssql" CHAR(255);
  DECLARE "divid" CHAR(20);
  DECLARE "TJobRateID" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "ssql" = '';
  IF "pCode" = '' THEN
    SELECT '100:~Please enter a valid shift type';
    RETURN
  END IF;
  IF "pPay_Rate" = '' OR("isnumeric"("pPay_rate") = 0) THEN
    SELECT '101:~Please enter a valid Pay Rate';
    RETURN
  END IF;
  IF "pCharge_Rate" = '' OR("isnumeric"("pCharge_rate") = 0) THEN
    SELECT '102:~Please enter a valid Charge Rate';
    RETURN
  END IF;
  IF "isnull"("pPay_Rate",'') <> "isnull"("pOldPay_Rate",'') THEN
    SET "ssql" = "ssql"+',payrate = pPay_Rate'
  END IF;
  IF("isnull"("pCharge_Rate",'') <> "isnull"("pOldCharge_Rate",'')) THEN
    SET "ssql" = "ssql"+',chargerate = pCharge_Rate'
  END IF;
  EXECUTE IMMEDIATE 'update TempJobRate set temppaybandid = pCode'+"ssql"+' where TempJobRateid = pObjID';
  IF @@rowcount = 0 THEN
    SET "TJobRateID" = "uniquekey"("pCode");
    INSERT INTO "TempJobRate"( "TempJobRateID","TempPayBandID","VacancyID","payrate","Chargerate" ) VALUES( "TJobRateID","pCode","pVacancyID","pPay_Rate","pCharge_Rate" ) 
  END IF;
  SELECT '0:TJobRateID~Success'
--select string('0:',TJobRateID,'#',pParentObject,'~Success')
END
}