bda_allmyemployees_forUpds

[LadyBird0]

create view bda_allmyemployees_forUpds
AS
-- all active employees
SELECT PEOPLE.PEOPLE_ID,
PEOPLE.FIRSTNAME,
PEOPLE.LASTNAME,
PEOPLE.PAYROLLNO,
PEOPLE.GENDER,
PEOPLE.DATEOFJOIN,
CASE JOBDETAIL.COMPANY
WHEN '3F5A0BB2-FF30-40B8-864E-5D0B6BCE8A48' THEN 'Christian Aid'
WHEN 'CE49B4F7-4165-443D-A777-79818F4D3E8D' THEN 'InspirAction'
WHEN 'ADA13139-AF77-4919-83C3-853F32B12AC6' THEN 'Change Alliance'
WHEN '499AC50A-ECFC-4D36-9EA0-C38E00A8008B' THEN 'Partnering Hope Into Action'
ELSE 'OTHERS'
END as organization,
CASE JOBDETAIL.ORGLEVEL1
WHEN '3366FA5C-E11B-45C5-B32F-C8790F1C5839' THEN 'International'
WHEN 'C49E7B9F-3752-42C5-9B23-0A8DF979D2AD' THEN 'Directorate'
WHEN '85828BEF-F5C9-4025-99B7-309F8D4AA768' THEN 'Corporate Services'
WHEN 'B7A9EEF3-92D6-4E7A-85DB-4BCEF6279768' THEN 'Programme Innovation and Funding Group'
WHEN 'CF4C288C-B5D4-4122-AD6C-63396CFFAD0A' THEN 'Policy and Public Affairs'
WHEN '43A41CD2-78BE-41EF-AD85-7424B046F668' THEN 'Supporter and Community Partnerships'
ELSE 'OTHERS'
END AS DEPARTMENTS,
CASE JOBDETAIL.ORGLEVEL2
WHEN '43FCF5E9-1222-4B19-A73D-F203B2F095FE' THEN 'Scotland'
WHEN '36732127-E950-43FF-9864-0D2DFBA3B1FC' THEN 'Ireland'
WHEN '935D143B-7902-45BC-9DC0-4A1E8F754724' THEN 'England'
WHEN 'E84B3471-7ECB-4A19-800A-8A1B893E2D82' THEN 'Wales'
WHEN '18014167-6D35-475F-A2F7-C417E2231A8A' THEN 'Fundraising, Communications and Campaigns'
WHEN '54930294-6D05-4F3A-9AD4-0FF4D4D1531E' THEN 'Performance Development and Insight'
ELSE 'OTHERS'
END AS DIVISION,
JOBDETAIL.ORGLEVEL3,
JOBDETAIL.ORGLEVEL4,
JOBDETAIL.LOCATION,
LOCATION.COUNTRY,
JOBDETAIL.JOBTITLE,
JOBDETAIL.CONTRACTTYPE,
JOBDETAIL.JOBSTATUS,
JOBDETAIL.FULLPARTTIME,
SALARYHISTORY.CONTRACTEDHOURS,
SALARYHISTORY.FULLTIMEEQUIVELANTHOURS,
SALARYHISTORY.FTENEW,
PEOPLE.LINEMANAGERNEW,
PEOPLE.HRADVISORNEW,
PEOPLE.TIER1HRSONEW,
JOBDETAIL.TOPJOB,
PEOPLE.WORKEMAIL,
PEOPLE.BUSINESSPARTNER
FROM PEOPLE
LEFT OUTER JOIN JOBDETAIL ON
JOBDETAIL.PEOPLE_ID =
PEOPLE.PEOPLE_ID
INNER JOIN SALARYHISTORY ON
SALARYHISTORY.PEOPLE_ID =
PEOPLE.PEOPLE_ID
INNER JOIN LOCATION ON
LOCATION.LOCATION_ID =
JOBDETAIL.LOCATION
WHERE ( PEOPLE.STATUS = N'A' )
AND ( JOBDETAIL.CURRENTRECORD = N'Yes' )
AND ( SALARYHISTORY.ISCURRENT = N'T' )

[LadyBird0]

update PEOPLE
set businesspartner ='C8F8932E-747A-4E02-9993-FB986EF64F4E',HRADVISORNEW ='F060CADD-74A7-438C-9E6E-D677A7900452'
where PEOPLE_ID in( bda_allmyemployees_forUpds)
--criteria in the view can be customized as we need it....
select people_id from bda_allmyemployees_forUpds where departments ='International' and COUNTRY='England'