/* Supplier Usage Reports — supplier management extract BackOffice: frmRptVendorUsage */ /* All the tables referenced here are already in DW Query to generate the sample report given by Ops */ SELECT 'MiscOrders' AS UsageReportType, so.TEFileNo, te.TELastName, te.TEFirstName, fs.ServiceType, fs.SrvcProvider, so.OrderSentDate AS OrderDate, so.CompanyName, so.ServiceCode, so.Description AS ServiceDescription, so.OrderDate AS ServiceOrderDate FROM dbo.vwTEServiceOrders so INNER JOIN dbo.tblTEMain1 te ON so.TEFileNo = te.TEFileNo INNER JOIN dbo.tblCorpClient cl ON te.CorpCode = cl.CorpCode INNER JOIN dbo.vwTEFullServiceList fs ON so.TEFileNo = fs.TEFileNo AND so.ServiceCode = fs.ServiceCode AND fs.CancelDate IS NULL WHERE so.OrderCanceled IS NULL AND so.VendorID <> 0 AND so.ServiceCode NOT IN ('TLV', 'TAD') and so.TEFileNo='DAIM260055'; /* 0 - Household Goods Orders Not Placed */ SELECT 'HHGNoOrder' AS UsageReportType, fs.TEFileNo, fs.CorpCode, cl.ParentCompany, fs.FormalName, fs.TELastName, fs.TEFirstName, fs.AuthDate, fs.ServiceType, fs.SrvcProvider, fs.ProfitCenter, fs.CAC, fs.UserType, CAST(NULL AS DATETIME) AS OrderDate, CAST(NULL AS INT) AS VendorParentID, CAST(NULL AS VARCHAR(100)) AS CompanyName FROM dbo.vwTEFullServiceList fs INNER JOIN dbo.tblCorpClient cl ON fs.CorpCode = cl.CorpCode LEFT JOIN dbo.tblTEHouseholdGoods hhg ON fs.TEFileNo = hhg.TEFileNo WHERE fs.ServiceCode = 'HHG' AND fs.CancelDate IS NULL AND fs.DisplayType = 'C' AND fs.InternalOrTestClient = 0 AND ( hhg.HHGOrderID IS NULL OR NOT EXISTS ( SELECT 1 FROM dbo.tblTEHHGOrderEstimates oe WHERE oe.HHGOrderID = hhg.HHGOrderID AND oe.VendorID <> 0 ) ); /* 1 - Household Goods Suppliers */ SELECT 'HHGSuppliers' AS UsageReportType, v.TEFileNo, v.CorpCode, cl.ParentCompany, v.FormalName, CAST(NULL AS VARCHAR(50)) AS TELastName, CAST(NULL AS VARCHAR(50)) AS TEFirstName, CAST(NULL AS DATETIME) AS AuthDate, v.ServiceType, v.SrvcProvider, v.ProfitCenter, v.CAC, v.UserType, v.ROLOrderDate AS OrderDate, v.VendorParentID, v.CompanyName, v.SubName, v.PCName, v.CACName, v.HHGAutoShipment FROM dbo.vwVendorsHHG v INNER JOIN dbo.tblCorpClient cl ON v.CorpCode = cl.CorpCode; /* 2 & 3 - Inspections (Non-company and company suppliers) */ SELECT CASE WHEN vs.VndrSrvcID = 77 THEN 'InspectionsVendor' ELSE 'InspectionsNonVendor' END AS UsageReportType, fs.TEFileNo, fs.CorpCode, cl.ParentCompany, fs.FormalName, fs.TELastName, fs.TEFirstName, CAST(NULL AS DATETIME) AS AuthDate, fs.ServiceType, fs.SrvcProvider, fs.ProfitCenter, fs.CAC, fs.UserType, lo.OrderDate, vp.VendorParentID, vp.CompanyName, lo.InspVendorID, lo.OrderID, lo.OrderType FROM dbo.tblTEOrders lo INNER JOIN dbo.vwTEFullServiceList fs ON lo.TEFileNo = fs.TEFileNo INNER JOIN dbo.tblCorpClient cl ON fs.CorpCode = cl.CorpCode LEFT JOIN dbo.tblVendors v ON lo.InspVendorID = v.VendorID LEFT JOIN dbo.tblVendorParent vp ON v.VendorParentID = vp.VendorParentID LEFT JOIN dbo.tblVendorServices vs ON v.VendorID = vs.VendorID AND vs.VndrSrvcID = 77 WHERE ISNULL(lo.InspVendorID, 0) <> 0 AND fs.CancelDate IS NULL; /* 4 - Temporary Living Orders Not Placed */ SELECT 'TLVNoOrder' AS UsageReportType, fs.TEFileNo, fs.CorpCode, cl.ParentCompany, fs.FormalName, fs.TELastName, fs.TEFirstName, fs.AuthDate, fs.ServiceType, fs.SrvcProvider, fs.ProfitCenter, fs.CAC, fs.UserType, CAST(NULL AS DATETIME) AS OrderDate, CAST(NULL AS INT) AS VendorParentID, CAST(NULL AS VARCHAR(100)) AS CompanyName FROM dbo.vwTEFullServiceList fs INNER JOIN dbo.tblCorpClient cl ON fs.CorpCode = cl.CorpCode WHERE fs.ServiceCode IN ('TLV', 'TAD') AND fs.CancelDate IS NULL AND fs.DisplayType = 'C' AND fs.InternalOrTestClient = 0 AND NOT EXISTS ( SELECT 1 FROM dbo.tblTETempAccomHousingInfo th INNER JOIN dbo.tblTEServiceOrders so ON th.TEServiceOrderID = so.TEServiceOrderID WHERE so.TEFileNo = fs.TEFileNo AND so.VendorID <> 0 ); /* 5 - Temporary Living Suppliers */ SELECT 'TLVSuppliers' AS UsageReportType, fs.TEFileNo, fs.CorpCode, cl.ParentCompany, fs.FormalName, fs.TELastName, fs.TEFirstName, CAST(NULL AS DATETIME) AS AuthDate, fs.ServiceType, fs.SrvcProvider, fs.ProfitCenter, fs.CAC, fs.UserType, th.DateBooked AS OrderDate, vp.VendorParentID, vp.CompanyName, v.VendorID FROM dbo.tblTETempAccomHousingInfo th INNER JOIN dbo.tblTEServiceOrders so ON th.TEServiceOrderID = so.TEServiceOrderID INNER JOIN dbo.vwTEFullServiceList fs ON so.TEFileNo = fs.TEFileNo AND fs.ServiceCode IN ('TLV', 'TAD') INNER JOIN dbo.tblCorpClient cl ON fs.CorpCode = cl.CorpCode INNER JOIN dbo.tblVendors v ON so.VendorID = v.VendorID INNER JOIN dbo.tblVendorParent vp ON v.VendorParentID = vp.VendorParentID WHERE so.VendorID <> 0 AND fs.CancelDate IS NULL; /* 6 - Title Companies */ SELECT 'TitleCompanies' AS UsageReportType, te.TEFileNo, te.CorpCode, cl.ParentCompany, cl.FormalName, te.TELastName, te.TEFirstName, CAST(NULL AS DATETIME) AS AuthDate, CAST(NULL AS SMALLINT) AS ServiceType, sp.Initials AS SrvcProvider, sp.ProfitCenter, CAST(NULL AS VARCHAR(10)) AS CAC, it.UserType, CAST(NULL AS DATETIME) AS OrderDate, vp.VendorParentID, vp.CompanyName, m2.TitleVendorID, m2.PrelimTitleOrdered, m2.ActualCOE FROM dbo.tblTEMain1 te INNER JOIN dbo.tblTEMain2 m2 ON te.TEFileNo = m2.TEFileNo INNER JOIN dbo.tblCorpClient cl ON te.CorpCode = cl.CorpCode LEFT JOIN dbo.tblVendors v ON m2.TitleVendorID = v.VendorID LEFT JOIN dbo.tblVendorParent vp ON v.VendorParentID = vp.VendorParentID LEFT JOIN dbo.tblInitials sp ON te.PrimaryContact = sp.Initials LEFT JOIN dbo.tblInitials it ON te.PrimaryContact = it.Initials WHERE ISNULL(m2.TitleVendorID, 0) <> 0 AND m2.PrelimTitleOrdered = 1; /* 7 - Title Company Fees */ SELECT 'TitleFees' AS UsageReportType, te.TEFileNo, te.CorpCode, cl.ParentCompany, cl.FormalName, te.TELastName, te.TEFirstName, CAST(NULL AS DATETIME) AS AuthDate, CAST(NULL AS SMALLINT) AS ServiceType, sp.Initials AS SrvcProvider, sp.ProfitCenter, CAST(NULL AS VARCHAR(10)) AS CAC, it.UserType, dtl.TransactionDate AS OrderDate, vp.VendorParentID, vp.CompanyName, m2.ActualCOE, dtl.ExpDetailID, dtl.Amount, dtl.VENDORID, dtl.Payee, ec.Category FROM dbo.tblTEMain1 te INNER JOIN dbo.tblTEMain2 m2 ON te.TEFileNo = m2.TEFileNo INNER JOIN dbo.tblCorpClient cl ON te.CorpCode = cl.CorpCode LEFT JOIN dbo.tblVendors v ON m2.TitleVendorID = v.VendorID LEFT JOIN dbo.tblVendorParent vp ON v.VendorParentID = vp.VendorParentID INNER JOIN dbo.tblTEExpenseSheets sh ON te.TEFileNo = sh.TEFileNo INNER JOIN dbo.tblTEExpenseDetail dtl ON sh.ExpenseSheetID = dtl.ExpenseSheetID INNER JOIN dbo.tblExpenseCategory ec ON dtl.CategoryID = ec.CategoryID LEFT JOIN dbo.tblInitials sp ON te.PrimaryContact = sp.Initials LEFT JOIN dbo.tblInitials it ON te.PrimaryContact = it.Initials WHERE dtl.SubmittedForPmt IS NOT NULL AND dtl.DeniedExpense = 0 AND dtl.TrackOnly = 0 AND ec.Category LIKE '%Title%'; /* 8 - Miscellaneous Orders */ SELECT 'MiscOrders' AS UsageReportType, so.TEFileNo, te.CorpCode, cl.ParentCompany, cl.FormalName, te.TELastName, te.TEFirstName, CAST(NULL AS DATETIME) AS AuthDate, fs.ServiceType, fs.SrvcProvider, fs.ProfitCenter, fs.CAC, fs.UserType, so.OrderSentDate AS OrderDate, so.VendorParentID, so.CompanyName, so.VendorID, so.ServiceCode, so.Description AS ServiceDescription, so.OrderDate AS ServiceOrderDate FROM dbo.vwTEServiceOrders so INNER JOIN dbo.tblTEMain1 te ON so.TEFileNo = te.TEFileNo INNER JOIN dbo.tblCorpClient cl ON te.CorpCode = cl.CorpCode INNER JOIN dbo.vwTEFullServiceList fs ON so.TEFileNo = fs.TEFileNo AND so.ServiceCode = fs.ServiceCode AND fs.CancelDate IS NULL WHERE so.OrderCanceled IS NULL AND so.VendorID <> 0 AND so.ServiceCode NOT IN ('TLV', 'TAD');