Back to Home

AD HOC Report

Overview

This report is generated in the SAM3 Production Server CIWSQLXP1122 by executing a script and saving the data in an excel document. The excel document is then attached to an email and sent once a week / every Monday morning at 9:00 am.

The purpose of this report is to provide details on all audits in Acclaim and what the last response was for each audit.

The script and excel template can be accessed below.

Steps

  1. Open Script in SAM3 Production Server CIWSQLXP1122.
  2. Execute script
  3. Copy Data with Headers
  4. Paste into Excel document Excel Template document and sent to the following:
    • Nancy Hock
    • Jennifer Gormley
    • Chrissy Koch
    • Jo-Ann Hamel
    • Alexandra Henry
drop table #LastResponse

drop table #tempAuditIssue

select UniqueIssueNo, max(StageNo) as StageNo, 0 as ResponseNo into #LastResponse from smart.p10_AuditActivity where Deleted = 0 and RolledBack = 0 and StageNo < 11
group by UniqueIssueNo

update #LastResponse set ResponseNo = b.ResponseNo from #LastResponse a inner join  smart.p10_AuditActivity b
on a.UniqueIssueNo = b.UniqueIssueNo and a.StageNo = b.StageNo and b.RolledBack = 0 and b.Deleted = 0

select a.* into #tempAuditIssue from smart.p10_auditissue a
inner join #lastResponse b on a.uniqueissueno = b.uniqueissueno
where b.ResponseNo != 5 and a.Deleted = 0

select a.UniqueInstanceNo, InstanceID, ReportMonth, d.LookupTitle as BusinessArea,
e.LookupTitle as AuditType, b.SampleName, f.LookupTitle as [Platform], g.FullName as Processor,
h.Supervisor, i.Lead, PointsEarned, PointsPossible,
iif (PointsPossible = 0, NULL, Round(PointsEarned*1.00/PointsPossible*1.00,4)) as OverallScore,
m.LookupTitle as AuditStage,
iif(MIN(CPOwned) = 1, 'true', iif(MIN(CPOwned) = 0, 'false',  CAST(MIN(CPOwned) as varchar(10)))) as CPOwned,
iif(MIN(ErrorLevelNo) = 2, 'Division', iif(MIN(ErrorLevelNo) = 1, 'Processor', CAST(MIN(ErrorLevelNo) as varchar(10)))) as ErrorLevel
from smart.p10_AuditInstance a
inner join smart.p10_Sample b on a.SampleNo = b.SampleNo
inner join smart.p10_CodeList d on b.BusinessAreaNo = d.LookupNo and d.LookupCategory = 'BusinessArea'
inner join smart.p10_CodeList e on a.AuditTypeNo = e.LookupNo and e.LookupCategory = 'AuditType'
left join smart.p10_CodeList f on a.PlatformNo = f.LookupNo and f.LookupCategory = 'Platform'
left join smart.sam_Master_UserList g on a.ProcessorNo = g.UserNo
left join smart.p10_qry_GetProcessorSupervisor h on a.ProcessorNo = h.ProcessorNo and h.SupervisorNo != 75
left join smart.p10_qry_GetSupervisorLead i on h.SupervisorNo = i.SupervisorNo
left join #tempAuditIssue j on a.UniqueInstanceNo = j.UniqueInstanceNo
left join smart.p10_CodeList m on a.StageNo = m.LookupNo and m.LookupCategory = 'AuditStage'
where a.deleted = 0 and a.currentstatus = 1 and ReportMonth > '12/01/2019'
group by a.UniqueInstanceNo, InstanceID, ReportMonth, d.LookupTitle,
e.LookupTitle, b.SampleName, f.LookupTitle, g.FullName,
h.Supervisor, i.Lead, PointsEarned, PointsPossible, m.LookupTitle