pears.NetReportUniTimesheets [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetReportUniTimesheets ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetReportUniTimesheets"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20) ) 
result( "Title__H" char(100),"Company__1" char(100),"Address__2" char(100),"Department__3" char(100),"Candidate__4" char(100),"Contact" char(100),"Job_Title" char(100),"Start_Date__D" date,"End_Date__D" date,"Hours__RMT" double,"Gross_Pay__RMT" double,"Net_Charge__RMT" double,"Charge_Inc_VAT__RMT" double,"Details__Eat" char(100) ) 
begin
  declare "pstart" date;
  declare "pend" date;
  declare "pStartPeriod" integer;
  declare "pEndPeriod" integer;
  declare "rTitle" char(100);
  declare "VATRate" decimal(9,2);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    ) not transactional;
  set "pstart" = "iqxnetstringtodate"("From__D");
  set "pend" = "iqxnetstringtodate"("To__D");
  set "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  set "pStartPeriod" = "weekcontaining"("pstart");
  set "pEndPeriod" = "weekcontaining"("pend");
  set "VATRate" = (select "VATRate" from "IQacVATRate" where "Vatcode" = (select "DefaultVATCode" from "IQacParams"));
  set "VATRate" = (("VATRate"/100)+1);
  -- Get all the Companies the WebUser is Associated with
  insert into "Cos"( "CompanyID" ) 
    select distinct "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID";
  select "rTitle",
    "Company"."Name" as "CompanyName",
    "company"."addr1" as "Address",
    "Employment"."Department",
    "Person"."Name" as "Candidate",
    "Contact"."Name" as "ContactName",
    "PEmp"."Position",
    "Pemp"."startdate" as "EmpStartdate",
    "Pemp"."leavedate" as "EmpLeavedate",
    (select "sum"("UnitsCharged") from "TempTimeSheetLine" key join "TempPayBand" where "TempTimeSheetID" = "TempTimeSheet"."TempTimeSheetID" and "TempPayBand"."Unit" like 'Hour%') as "TotHours",
    (select "sum"("UnitsPaid"*"PayRate") from "TempTimeSheetLine" where "TempTimeSheetID" = "TempTimeSheet"."TempTimesheetID") as "TotPaid",
    (select "sum"("UnitsCharged"*"ChargeRate") from "TempTimesheetLine" where "TempTimeSheetID" = "TempTimeSheet"."TempTimeSheetID") as "TotCharged",
    ("TotCharged"*"VATRate") as "ChargeWVAT",'NetReportUniTimesheetLines&pTimesheetID='+"temptimesheet"."temptimesheetid" as "Details"
    from "TempTimeSheet"
      key join("Placement" key join("Employment" as "PEmp" key join "Person","Vacancy"
      key join("Employment" key join("Person" as "Contact",("Company" join "cos" on "company"."companyid" = "cos"."companyid")))))
    where "TempTimeSheet"."TransferBatch" >= 1
    and "TempTimeSheet"."Period" between "pStartPeriod" and "pEndPeriod"
    and "TempTimeSheet"."PeriodLength" = 'W'
    and "weekmonthenddate"("TempTimeSheet"."period","TempTimeSheet"."periodlength") >= "IQXNetCompanyStartDate"("company"."companyid")
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportUniTimesheets" IS 
{create procedure pears."NetReportUniTimesheets"( in "pWebUserID" char(20),in "From__D" char(20),in "To__D" char(20) ) 
result( "Title__H" char(100),"Company__1" char(100),"Address__2" char(100),"Department__3" char(100),"Candidate__4" char(100),"Contact" char(100),"Job_Title" char(100),"Start_Date__D" date,"End_Date__D" date,"Hours__RMT" double,"Gross_Pay__RMT" double,"Net_Charge__RMT" double,"Charge_Inc_VAT__RMT" double,"Details__Eat" char(100) ) 
begin
  declare "pstart" date;
  declare "pend" date;
  declare "pStartPeriod" integer;
  declare "pEndPeriod" integer;
  declare "rTitle" char(100);
  declare "VATRate" decimal(9,2);
  declare local temporary table "Cos"(
    "CompanyID" char(20) null,
    ) not transactional;
  set "pstart" = "iqxnetstringtodate"("From__D");
  set "pend" = "iqxnetstringtodate"("To__D");
  set "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  set "pStartPeriod" = "weekcontaining"("pstart");
  set "pEndPeriod" = "weekcontaining"("pend");
  set "VATRate" = (select "VATRate" from "IQacVATRate" where "Vatcode" = (select "DefaultVATCode" from "IQacParams"));
  set "VATRate" = (("VATRate"/100)+1);
  -- Get all the Companies the WebUser is Associated with
  insert into "Cos"( "CompanyID" ) 
    select distinct "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID";
  select "rTitle",
    "Company"."Name" as "CompanyName",
    "company"."addr1" as "Address",
    "Employment"."Department",
    "Person"."Name" as "Candidate",
    "Contact"."Name" as "ContactName",
    "PEmp"."Position",
    "Pemp"."startdate" as "EmpStartdate",
    "Pemp"."leavedate" as "EmpLeavedate",
    (select "sum"("UnitsCharged") from "TempTimeSheetLine" key join "TempPayBand" where "TempTimeSheetID" = "TempTimeSheet"."TempTimeSheetID" and "TempPayBand"."Unit" like 'Hour%') as "TotHours",
    (select "sum"("UnitsPaid"*"PayRate") from "TempTimeSheetLine" where "TempTimeSheetID" = "TempTimeSheet"."TempTimesheetID") as "TotPaid",
    (select "sum"("UnitsCharged"*"ChargeRate") from "TempTimesheetLine" where "TempTimeSheetID" = "TempTimeSheet"."TempTimeSheetID") as "TotCharged",
    ("TotCharged"*"VATRate") as "ChargeWVAT",'NetReportUniTimesheetLines&pTimesheetID='+"temptimesheet"."temptimesheetid" as "Details"
    from "TempTimeSheet"
      key join("Placement" key join("Employment" as "PEmp" key join "Person","Vacancy"
      key join("Employment" key join("Person" as "Contact",("Company" join "cos" on "company"."companyid" = "cos"."companyid")))))
    where "TempTimeSheet"."TransferBatch" >= 1
    and "TempTimeSheet"."Period" between "pStartPeriod" and "pEndPeriod"
    and "TempTimeSheet"."PeriodLength" = 'W'
    and "weekmonthenddate"("TempTimeSheet"."period","TempTimeSheet"."periodlength") >= "IQXNetCompanyStartDate"("company"."companyid")
end
}
</code>