Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."NetClientVacancies"( IN "pWebUserID" CHAR(20),IN "pStartDate" DATE DEFAULT NULL,IN "pEndDate" DATE DEFAULT NULL ) RESULT( "RowID" CHAR(30),"StartDate" DATE,"StartTime" TIME,"FinishTime" TIME,"FinishDate" DATE,"IsShift" SMALLINT,"VacancyID" CHAR(20),"TempShiftPlanID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Status" CHAR(40),"Department" CHAR(30),"Consultant" CHAR(60),"OurRef" CHAR(20),"TheirRef" CHAR(50),"ContractRef" CHAR(20),"CascadeDateTime" TIMESTAMP,"CascadeLevel" SMALLINT,"WhenEntered" TIMESTAMP,"FilledBy" CHAR(250),"Filled" SMALLINT,"Unconfirmed" SMALLINT,"LongDescription" long VARCHAR,"CompanyAddress" CHAR(250) ) -- modified 20110815 to include shift date and time as well as plan date and time ref ASA -- modified 20110823 to use temporary table for lookup to improve speed BEGIN DECLARE @DateRangeStart DATE; DECLARE @DateRangeEnd DATE; DECLARE @companycount INTEGER; DECLARE LOCAL TEMPORARY TABLE "IDs"( "ID" CHAR(20) NOT NULL, "CompanyStartDate" DATE NULL, ) NOT transactional; INSERT INTO "IDs"( "ID","CompanyStartDate" ) SELECT DISTINCT "companyid","IQXNetCompanyStartDate"("companyid") FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"; SET @DateRangeStart = "isnull"("pStartDate","dateadd"("month",-3,CURRENT DATE)); SET @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,CURRENT DATE)); SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"); SELECT 'V'+"v"."VacancyID" AS "RowID", "v"."StartDate", CAST(NULL AS TIME) AS "StartTime", CAST(NULL AS TIME) AS "FinishTime", "v"."FinishDate", 0 AS "IsShift", "v"."VacancyID", CAST(NULL AS CHAR(20)) AS "TempShiftPlanID", "v"."Position" AS "Position", CAST(NULL AS CHAR(50)) AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Person"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", "v"."RefCode" AS "OurRef", "v"."TheirRef", "v"."ContractRef", "v"."CascadeDateTime", "v"."CascadeLevel", "v"."WhenEntered", (SELECT "list"("person"."name") FROM "vacancy" KEY JOIN "placement" KEY JOIN "employment" KEY JOIN "person" WHERE "vacancy"."vacancyid" = "v"."vacancyid") AS "FilledBy", "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled", (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed", "v"."OtherNotes" AS "LongDescription", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress" FROM "Vacancy" AS "v" KEY JOIN("Employment","tempshift" AS "t") KEY JOIN("Company","Person") JOIN "IDs" ON "Company"."CompanyID" = "IDs"."ID" WHERE "v"."StartDate" <= @DateRangeEnd AND "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart AND "v"."temp" = 1 AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S' AND "v"."StartDate" >= "IDs"."CompanyStartDate" UNION ALL SELECT 'P'+"p"."TempShiftPlanID" AS "RowID", "isnull"("ts"."shiftdate","p"."ShiftDate") AS "StartDate", "isnull"("ts"."timefrom","p"."TimeFrom") AS "StartTime", "isnull"("ts"."timeto","p"."TimeTo") AS "FinishTime", (IF "StartTime" < "FinishTime" THEN "StartDate" ELSE "StartDate"+1 endif) AS "FinishDate", 1 AS "IsShift", "v"."VacancyID", "p"."TempShiftPlanID", "v"."Position", "p"."Description" AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Pe"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "OurRef", "p"."ReferenceCode" AS "TheirRef", "v"."ContractRef", "p"."CascadeDateTime", "p"."CascadeLevel", "p"."WhenEntered", (SELECT "name" FROM "person" WHERE "personid" = "ts"."personid") AS "FilledBy", "ifnull"("FilledBy",0,1) AS "Filled", "isnull"((SELECT FIRST 1 FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" = '~'),0) AS "UnConfirmed", CAST(NULL AS long VARCHAR) AS "LongDescription", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress" FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person" AS "pe") JOIN "IDs" ON "Company"."CompanyID" = "IDs"."ID" ,"TempShiftPlan" AS "p" LEFT OUTER JOIN "tempshift" AS "ts" ON "ts"."tempshiftplanid" = "p"."tempshiftplanid" AND("ts"."state" <> 'C' OR "ts"."cancelreason" = '~') WHERE "p"."ShiftDate" BETWEEN @DateRangeStart AND @DateRangeEnd AND "StartDate" >= "IDs"."CompanyStartDate" AND NOT EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "isnull"("crefill",0) = 0 AND "isnull"("cancelreason",'') <> '~') ORDER BY 2 ASC,3 ASC END GO COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacancies" IS {CREATE PROCEDURE pears."NetClientVacancies"( IN "pWebUserID" CHAR(20),IN "pStartDate" DATE DEFAULT NULL,IN "pEndDate" DATE DEFAULT NULL ) RESULT( "RowID" CHAR(30),"StartDate" DATE,"StartTime" TIME,"FinishTime" TIME,"FinishDate" DATE,"IsShift" SMALLINT,"VacancyID" CHAR(20),"TempShiftPlanID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Status" CHAR(40),"Department" CHAR(30),"Consultant" CHAR(60),"OurRef" CHAR(20),"TheirRef" CHAR(50),"ContractRef" CHAR(20),"CascadeDateTime" TIMESTAMP,"CascadeLevel" SMALLINT,"WhenEntered" TIMESTAMP,"FilledBy" CHAR(250),"Filled" SMALLINT,"Unconfirmed" SMALLINT,"LongDescription" long VARCHAR,"CompanyAddress" CHAR(250) ) -- modified 20110815 to include shift date and time as well as plan date and time ref ASA -- modified 20110823 to use temporary table for lookup to improve speed BEGIN DECLARE @DateRangeStart DATE; DECLARE @DateRangeEnd DATE; DECLARE @companycount INTEGER; DECLARE LOCAL TEMPORARY TABLE "IDs"( "ID" CHAR(20) NOT NULL, "CompanyStartDate" DATE NULL, ) NOT transactional; INSERT INTO "IDs"( "ID","CompanyStartDate" ) SELECT DISTINCT "companyid","IQXNetCompanyStartDate"("companyid") FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"; SET @DateRangeStart = "isnull"("pStartDate","dateadd"("month",-3,CURRENT DATE)); SET @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,CURRENT DATE)); SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"); SELECT 'V'+"v"."VacancyID" AS "RowID", "v"."StartDate", CAST(NULL AS TIME) AS "StartTime", CAST(NULL AS TIME) AS "FinishTime", "v"."FinishDate", 0 AS "IsShift", "v"."VacancyID", CAST(NULL AS CHAR(20)) AS "TempShiftPlanID", "v"."Position" AS "Position", CAST(NULL AS CHAR(50)) AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Person"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", "v"."RefCode" AS "OurRef", "v"."TheirRef", "v"."ContractRef", "v"."CascadeDateTime", "v"."CascadeLevel", "v"."WhenEntered", (SELECT "list"("person"."name") FROM "vacancy" KEY JOIN "placement" KEY JOIN "employment" KEY JOIN "person" WHERE "vacancy"."vacancyid" = "v"."vacancyid") AS "FilledBy", "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled", (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed", "v"."OtherNotes" AS "LongDescription", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress" FROM "Vacancy" AS "v" KEY JOIN("Employment","tempshift" AS "t") KEY JOIN("Company","Person") JOIN "IDs" ON "Company"."CompanyID" = "IDs"."ID" WHERE "v"."StartDate" <= @DateRangeEnd AND "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart AND "v"."temp" = 1 AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S' AND "v"."StartDate" >= "IDs"."CompanyStartDate" UNION ALL SELECT 'P'+"p"."TempShiftPlanID" AS "RowID", "isnull"("ts"."shiftdate","p"."ShiftDate") AS "StartDate", "isnull"("ts"."timefrom","p"."TimeFrom") AS "StartTime", "isnull"("ts"."timeto","p"."TimeTo") AS "FinishTime", (IF "StartTime" < "FinishTime" THEN "StartDate" ELSE "StartDate"+1 endif) AS "FinishDate", 1 AS "IsShift", "v"."VacancyID", "p"."TempShiftPlanID", "v"."Position", "p"."Description" AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Pe"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "OurRef", "p"."ReferenceCode" AS "TheirRef", "v"."ContractRef", "p"."CascadeDateTime", "p"."CascadeLevel", "p"."WhenEntered", (SELECT "name" FROM "person" WHERE "personid" = "ts"."personid") AS "FilledBy", "ifnull"("FilledBy",0,1) AS "Filled", "isnull"((SELECT FIRST 1 FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" = '~'),0) AS "UnConfirmed", CAST(NULL AS long VARCHAR) AS "LongDescription", (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress" FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person" AS "pe") JOIN "IDs" ON "Company"."CompanyID" = "IDs"."ID" ,"TempShiftPlan" AS "p" LEFT OUTER JOIN "tempshift" AS "ts" ON "ts"."tempshiftplanid" = "p"."tempshiftplanid" AND("ts"."state" <> 'C' OR "ts"."cancelreason" = '~') WHERE "p"."ShiftDate" BETWEEN @DateRangeStart AND @DateRangeEnd AND "StartDate" >= "IDs"."CompanyStartDate" AND NOT EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "isnull"("crefill",0) = 0 AND "isnull"("cancelreason",'') <> '~') ORDER BY 2 ASC,3 ASC END }