Code: Select all
<project name="TEA Placement Staffing Detail WA Smart Export" mainModule="Main" version="2.0" logLevel="debug">
<description>TEA Placement Staffing Detail</description>
<module name="Main">
<sql label="Connect to DB" resourceId="FinSql4 WASmart" version="1.0" logLevel="debug">
<query label="Extract TEA Data" outputVariable="data">
<statement>
select Value, Description
into #CyGlobalPayCodeSetXHR2
from LinkSmartMaster.SmartMaster.dbo.CyGlobalPayCodeSet
where Codeset = 'XHR2'
select Value, Description
into #CyGlobalPayCodeSetHR59
FROM LinkSmartMaster.SmartMaster.dbo.CyGlobalPayCodeSet cs59
where CODESET LIKE 'HR59'
select Social, SchoolKey, Subject, Fte, SchoolCode, row_number()
over (partition by Social order by Fte desc) rownumber
into #TempRepAssign
from RepAssign
--delete from #TempRepAssign where rownumber <> 1
--select * from #TempRepAssign
select Social, SchoolKey, Subject, Fte, SchoolCode, rownumber
into #TempRepAssign2
from #TempRepAssign
where rownumber = 1
CREATE TABLE #TempRepTable
(
EmployeeNumber char(10) NULL,
Subject char(05) NULL,
Fte decimal(5,2) NULL,
RepSchoolCode char(10) NULL,
RepSubjectAssign char(50) NULL
);
insert into #TempRepTable (EmployeeNumber, Subject, Fte,
RepSchoolCode, RepSubjectAssign)
select ra.Social, ra.Subject, ra.Fte, ra.SchoolCode,
/*use subselect for this field to get into paycodeset*/
(select Description from #CyGlobalPayCodeSetXHR2 cs02
where ra.Subject = cs02.Value) RepSubjectAssign
--cs02.Description
from #TempRepAssign2 ra
UPDATE #TempRepTable
-- take out ',' from RepSubjectAssign
set RepSubjectAssign = replace(RepSubjectAssign, ',', '')
-- select * from #TempRepTable
---------------------------------------
CREATE TABLE #TempEndorsements
(
CredentialIdent int NULL,
Endorsements varchar(MAX) NULL
);
insert into #TempEndorsements (CredentialIdent, Endorsements)
select ec.CredentialIdent, edx.list
from EmpCredentials ec
--from #EmpCredentials ec
cross apply (
select ee.Endorsement + ','
from EmpEndorsements ee
where ec.CredentialIdent = ee.CredentialIdent
order by DateIssued desc, REPOverride asc
for XML path('') ) edx (list)
UPDATE #TempEndorsements
-- take out ',' from Endorsements
set Endorsements = replace(Endorsements, ',', '/')
--select * from #TempEndorsements
--------------------------------------
CREATE TABLE #EmpCredentials
( CredentialIdent int not null,
EmployeeNumber char(10) NULL,
CertificateNumber char(15) NULL,
CertificateTypeCode char(4) NULL,
RepOverride char(1) NULL,
DateIssued smalldatetime NULL,
DateExpired smalldatetime NULL,
Endorsements varchar(MAX) NULL
)
insert into #EmpCredentials (CredentialIdent, EmployeeNumber,
CertificateNumber, CertificateTypeCode, RepOverride,
DateIssued, DateExpired, Endorsements)
select --*
ec.CredentialIdent, ec.EmployeeNumber, ec.CertificateNumber,
ec.CertificateTypeCode,
ec.RepOverride, ec.DateIssued, ec.DateExpired, ee.Endorsements
from EmpCredentials ec
left join #TempEndorsements ee
on ec.CredentialIdent = ee.CredentialIdent
WHERE RepOverride IN ('R', 'S')
-------------------------------------------above----- certificate retrieval
CREATE TABLE #TempEmpCredentials
(
EmployeeNumber char(10) NULL,
CertificateNumber char(15) NULL,
CertificateTypeCode char(4) NULL,
RepOverride char(1) NULL,
DateIssued smalldatetime NULL,
DateExpired smalldatetime NULL,
CertificateTypeDesc char(50) NULL,
Endorsements varchar(MAX) NULL
);
insert into #TempEmpCredentials (EmployeeNumber, CertificateNumber,
CertificateTypeCode, RepOverride, DateIssued,
DateExpired, CertificateTypeDesc, Endorsements )
SELECT ec.EmployeeNumber, ec.CertificateNumber,
ec.CertificateTypeCode, ec.RepOverride, ec.DateIssued,
ec.DateExpired,
/*use subselect for this field to get into paycodeset*/
(select Description from #CyGlobalPayCodeSetHR59 cs59
where ec.CertificateTypeCode = cs59.Value) RepSubjectAssign,
--cs59.Description,
ec.Endorsements
from #EmpCredentials ec
--left join #CyGlobalPayCodeSetHR59 cs59
-- ON ec.CertificateTypeCode = cs59.Value
UPDATE #TempEmpCredentials
-- take out ',' from CertificateTypeDesc
set CertificateTypeDesc = replace(CertificateTypeDesc, ',', '')
-- select * from #TempEmpCredentials
------------------------------------------------------
select ed.EmployeeNumber, ed.Lastname, ed.FirstName,
pc.Description as CheckLocDesc,
ej.JobTitle,
tr.RepSubjectAssign,
ec.CertificateTypeDesc,
ec.Endorsements,
convert(decimal(8,4), ROUND(ex.RateValue4 * .01, 4)) AS RateValue4,
convert(decimal(8,4), ROUND(ex.RateValue5 * .01, 4)) AS RateValue5,
ed.UnionCode
from EmpDemo ED
left join dbo.EmpJobAssignment ej
on ed.EmployeeNumber = ej.EmployeeNumber
and ej.currentflag = '1'
LEFT join #TempRepTable tr
on ed.EmployeeNumber = tr.EmployeeNumber
inner join #TempEmpCredentials ec
on ed.EmployeeNumber = ec.EmployeeNumber
LEFT join dbo.EmpExtraNumeric ex
on ed.EmployeeNumber = ex.EmployeeNumber
AND ex.RateValue4 > 0
left JOIN CyPayCodeset pc
ON ed.CheckLocation = pc.Value
AND pc.codeset = 'BARL'
INNER JOIN EmpHire eh ON ed.EmployeeNumber = eh.EmployeeNumber and eh.CurrentFlag = 1
and SUBSTRING(eh.ResultingStatusCode,1,1) not in ('1', '5', '9')
-- and SUBSTRING(eh.ResultingStatusCode,1,1) = '0'
where ed.UnionCode in ('0A', '0D', '0E', '0K') and
RateValue4 is not NULL
order by ex.RateValue4 DESC, ex.RateValue5
</statement>
</query>
</sql>
<writeCSV label="Convert TEA Details to CSV" inputRowSetVariable="${data}" outputFile="resource:smb://webapps1/wa/WATEAdata${CurrentDate()}.csv" whenFileExists="overwrite" fieldDelimiter="tab" includeHeadings="true" version="1.0">
<data trim="right" dateFormat="MM/dd/yyyy" timeFormat="HH:mm:ss" timestampFormat="MM/dd/yyyy HH:mm:ss.SSS" />
</writeCSV>
<sendEmail label="Send reminder report is ready" resourceId="SMTP server" toList="stimetb@resa.net" version="2.0">
<from address="stimetb@resa.net" />
<subject>
<![CDATA[TEA Placement Staffing reports]]>
</subject>
<message>
<![CDATA[Your TEA file(s) has been uploaded to RESA's secure file.
]]>
</message>
</sendEmail>
</module>
</project>
