pears.NetPersonShifts

CREATE PROCEDURE "pears"."NetPersonShifts"( IN "pWebUserID" CHAR(20) ) 
RESULT( "Position" CHAR(50),"Company" CHAR(60),"ShiftDate" DATE,"TimeFrom" TIME,"TimeTo" TIME,"BreakMinutes" SMALLINT,"State" CHAR(1),"Description" CHAR(50),"TheirRef" CHAR(20),"TempShiftID" CHAR(20),"Note" CHAR(20),"OrderedBy" CHAR(20),"ConfirmedWith" CHAR(20) ) 
BEGIN
  SELECT "vacancy"."Position",
    "company"."name" AS "Company",
    "TempShift"."ShiftDate",
    "TempShift"."TimeFrom",
    "TempShift"."TimeTo",
    "TempShift"."BreakMinutes",
    "TempShift"."State",
    "tempshiftplan"."Description",
    "isnull"("tempshift"."referencecode","tempshiftplan"."referencecode") AS "TheirRef",
    "tempshift"."tempshiftid",
    "TempShift"."Note",
    "tempshiftplan"."OrderedBy",
    "tempshift"."ConfirmedWith"
    FROM "tempshift" KEY LEFT OUTER JOIN "tempshiftplan"
      ,"tempshift" KEY JOIN "person"
      ,"tempshift" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
    WHERE "person"."personid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    --and tempshift.shiftdate >= current date 
    ORDER BY "TempShift"."ShiftDate" ASC,"TempShift"."TimeFrom" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetPersonShifts" IS 
{CREATE PROCEDURE pears."NetPersonShifts"( IN "pWebUserID" CHAR(20) ) 
RESULT( "Position" CHAR(50),"Company" CHAR(60),"ShiftDate" DATE,"TimeFrom" TIME,"TimeTo" TIME,"BreakMinutes" SMALLINT,"State" CHAR(1),"Description" CHAR(50),"TheirRef" CHAR(20),"TempShiftID" CHAR(20),"Note" CHAR(20),"OrderedBy" CHAR(20),"ConfirmedWith" CHAR(20) ) 
BEGIN
  SELECT "vacancy"."Position",
    "company"."name" AS "Company",
    "TempShift"."ShiftDate",
    "TempShift"."TimeFrom",
    "TempShift"."TimeTo",
    "TempShift"."BreakMinutes",
    "TempShift"."State",
    "tempshiftplan"."Description",
    "isnull"("tempshift"."referencecode","tempshiftplan"."referencecode") AS "TheirRef",
    "tempshift"."tempshiftid",
    "TempShift"."Note",
    "tempshiftplan"."OrderedBy",
    "tempshift"."ConfirmedWith"
    FROM "tempshift" KEY LEFT OUTER JOIN "tempshiftplan"
      ,"tempshift" KEY JOIN "person"
      ,"tempshift" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
    WHERE "person"."personid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    --and tempshift.shiftdate >= current date 
    ORDER BY "TempShift"."ShiftDate" ASC,"TempShift"."TimeFrom" ASC
END
}