pears.NetProvTimesheetExpenseLineCreate

CREATE PROCEDURE "pears"."NetProvTimesheetExpenseLineCreate"( IN "pWebUserID" CHAR(20),IN "pTempProvTimesheetID" CHAR(20),IN "pTempPayBandID" CHAR(20),IN "pDescription" CHAR(100) DEFAULT NULL,IN "pChargeRate" DECIMAL DEFAULT 0,IN "pUnitsCharged" DECIMAL DEFAULT 0 ) 
RESULT( "pResult" CHAR(255) ) 
//IQXWeb
BEGIN
  DECLARE "LineNo" INTEGER;
  IF NOT "pTempProvTimesheetID" = any(SELECT "TempProvTimeSheetID" FROM "tempprovtimesheet" KEY JOIN "person" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID") THEN
    SELECT '99:~Not able to create line'
  END IF;
  IF NOT "pTempPayBandID" = any(SELECT "TempPayBandID" FROM "TempPayBand") THEN
    SELECT '98:~Not able to create line'
  END IF;
  IF("isnull"("pDescription",'') = '') OR "pChargeRate" = 0 OR "pUnitsCharged" = 0 THEN
    SELECT '97:~Not able to create line'
  END IF;
  SET "LineNo" = (SELECT "count"() FROM "tempprovtimesheetLine" WHERE "tempprovtimesheetid" = "pTempProvTimesheetID")+1;
  INSERT INTO "tempprovtimesheetline"
    ( "TempProvTimesheetLineID","TempProvTimesheetID","TempPayBandID","LineNumber","UnitsPaid","UnitsCharged","PayRate","ChargeRate","description" ) VALUES
    ( "uniqueKey"("pTempProvTimesheetID"),"pTempProvTimesheetID","pTempPayBandID","LineNo","pUnitsCharged","pUnitsCharged","pChargeRate","pChargeRate","pDescription" ) ;
  SELECT '0:~Success'
END /* DOC
2016-11-09 PC insert an expense line into tempprovtimesheetline
IW-95
2018-07-25 PC include improved error handler
 
Inputs:
pWebUserID
pTempProvTimesheetID
pTempPayBandID
pDescription - e.g. cheese sandwich
pChargeRate - monetary amount per unit claimed e.g. £3.50
pUnitsCharged - quantity claimed for - e.g. 2
 
Outputs:
pResult
 
Tests:
invalid pWebUserID
valid pWebUserID but invalid pTempProvTimesheetID
valid pWebUserID, pTempProvTimesheetID but invalid pTempPayBandID
valid pWebUserID, pTempProvTimesheetID, pTempPayBandID but invalid pDescription
valid pWebUserID, pTempProvTimesheetID, pTempPayBandID, pDescription but invalid pChargeRate
valid pWebUserID, pTempProvTimesheetID, pTempPayBandID, pDescription,pChargeRate but invalid pUnitsCharged
all valid
 
*/
/* TEST
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetExpenseLineCreate" IS 
{CREATE PROCEDURE pears."NetProvTimesheetExpenseLineCreate"( IN pWebUserID CHAR(20),IN pTempProvTimesheetID CHAR(20),IN pTempPayBandID CHAR(20),IN pDescription CHAR(100) DEFAULT NULL,IN pChargeRate DECIMAL DEFAULT 0,IN pUnitsCharged DECIMAL DEFAULT 0)
RESULT(pResult CHAR(255)) 
//IQXWeb
BEGIN
    DECLARE LineNo INTEGER;
    IF pTempProvTimesheetID NOT IN (SELECT TempProvTimeSheetID FROM tempprovtimesheet KEY JOIN person KEY JOIN iqxnetuserlink WHERE iqxnetuserid=pWebUserID) THEN
        SELECT '99:~Not able to create line';
    END IF;
    IF pTempPayBandID NOT IN (SELECT TempPayBandID FROM TempPayBand) THEN
        SELECT '98:~Not able to create line';
    END IF;
    IF (isnull(pDescription,'')='') OR pChargeRate=0 OR pUnitsCharged=0 THEN
        SELECT '97:~Not able to create line';
    END IF;
    SET LineNo=(SELECT COUNT() FROM tempprovtimesheetLine WHERE tempprovtimesheetid=pTempProvTimesheetID)+1;
	INSERT INTO tempprovtimesheetline 
        (TempProvTimesheetLineID,TempProvTimesheetID,TempPayBandID,LineNumber,UnitsPaid,UnitsCharged,PayRate,ChargeRate, description) 
    VALUES
        (uniqueKey(pTempProvTimesheetID), pTempProvTimesheetID,pTempPayBandID,LineNo,pUnitsCharged,pUnitsCharged,pChargeRate,pChargeRate,pDescription);
    SELECT '0:~Success';
END
 
/* DOC
2016-11-09 PC insert an expense line into tempprovtimesheetline
IW-95
2018-07-25 PC include improved error handler
 
Inputs:
    pWebUserID
    pTempProvTimesheetID
    pTempPayBandID
    pDescription - e.g. cheese sandwich
    pChargeRate - monetary amount per unit claimed e.g. £3.50
    pUnitsCharged - quantity claimed for - e.g. 2
 
Outputs:
    pResult
 
Tests:
    invalid pWebUserID
    valid pWebUserID but invalid pTempProvTimesheetID
    valid pWebUserID, pTempProvTimesheetID but invalid pTempPayBandID
    valid pWebUserID, pTempProvTimesheetID, pTempPayBandID but invalid pDescription
    valid pWebUserID, pTempProvTimesheetID, pTempPayBandID, pDescription but invalid pChargeRate
    valid pWebUserID, pTempProvTimesheetID, pTempPayBandID, pDescription,pChargeRate but invalid pUnitsCharged
    all valid
 
*/
 
/* TEST
 
*/
}