Description
This script is used for creating a Mail Merge Contract that contains personal data, Passport, Seaman’s book details, joining data like Joining Port, Vessel, Position, and monthly salary amounts like Basic Wage, Guaranteed Overtime, Leave Payment, etc. It also contains amounts of Employee’s Malta Social Security contribution and Employer’s contribution - which is a total of Malta Social Security contribution and Malta Maternity Leave contribution (both calculated based on the conditions for EC 887 Malta Maternity leave and 889 Malta Social Security).
SQL Statement
-- upd-31/01/2022
SELECT
P01.PIN,
p01.CONTRACTTYPE,
ORG.NAME ORG_NAME,
P01.FIRSTNAME,
P01.LASTNAME,
P01.MIDDLENAME,
P01.BIRTHDATE,
P01.PLACEOFBIRTH BIRTHPLACE,
nat.countryname as NAT,
DATEDIFF (MONTH, P03.DATEFROM, P03.TODATEESTIMATED +1) AS MONTHS,
PASS.TDNUMBER PASSPORTNO,
PASS.DATETO PASSPORT_EXPIRY,
PASS.DATEFROM PASSPORT_ISSUE,
CTRPASS.COUNTRYNAME ISSUED_BY,
CASE
WHEN SB1.CODE IS NOT NULL THEN
SB1.TDNUMBER
WHEN SB2.CODE IS NOT NULL THEN
SB2.TDNUMBER
WHEN SB3.CODE IS NOT NULL THEN
SB3.TDNUMBER
END AS SEAMAN_BOOK,
CASE
WHEN SB1.CODE IS NOT NULL THEN
SB1.DATETO
WHEN SB2.CODE IS NOT NULL THEN
SB2.DATETO
WHEN SB3.CODE IS NOT NULL THEN
SB3.DATETO
END AS SEAMAN_BOOK_EXPIRY,
CASE
WHEN SB1.CODE IS NOT NULL THEN
SB1.DATEFROM
WHEN SB2.CODE IS NOT NULL THEN
SB2.DATEFROM
WHEN SB3.CODE IS NOT NULL THEN
SB3.DATEFROM
END AS SEAMAN_BOOK_ISSUE,
CASE
WHEN SB1.CODE IS NOT NULL THEN
CTRSB1.COUNTRYNAME
WHEN SB2.CODE IS NOT NULL THEN
CTRSB2.COUNTRYNAME
WHEN SB3.CODE IS NOT NULL THEN
CTRSB3.COUNTRYNAME
END AS SEAMAN_BOOK_ISSUED_BY,
ORGPOS.NAME POSITION,
ORGVES.NAME AS VESSEL,
ves.IMONUMBER as IMO_NUMBER,
VES.REGISTERFLAG REGISTER_FLAG,
VES.VESSELTYPE VESSEL_TYPE,
PORTON.NAME ONSIGNING_PORT,
PORTON.NAME ONSIGNING_PORT1,
P01.CONTRACTSTARTDATE CONTRACT_START,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 'C') as BASIC,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 5), 'C') as OT,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 6), 'C') as CONS_TO,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 7), 'C') as GOT,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 10), 'C') as WEEKEND_OT,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 11), 'C') as WEEKEND,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 14), 'C') as LP,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 15), 'C') as SUBSIS,
CASE
WHEN p1r13.Raten='N' OR p1r13.Raten='n' THEN '0'
WHEN dbo.ad_StrToFloat(p1r13.Raten) <> 0 THEN FORMAT (dbo.ad_StrToFloat(p1r13.RATEN),'C')
else
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 19), 'C')
END as RESERVERD,
CASE
WHEN dbo.ad_StrToFloat(p1r11.Raten) <> 0 THEN FORMAT (dbo.ad_StrToFloat(p1r11.RATEN),'C')
else
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 20), 'C')
END as SUPPL1,
CASE
WHEN dbo.ad_StrToFloat(p1r12.Raten) <> 0 THEN FORMAT (dbo.ad_StrToFloat(p1r12.RATEN),'C')
else
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 21), 'C')
END as SUPPL2,
/*FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0,25), 'C') as TOTAL,*/
FORMAT (
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1) +
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 6) +
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 7) +
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 10) +
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 11) +
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 14) +
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 15)+
CASE
WHEN p1r13.Raten='N' OR p1r13.Raten='n' THEN '0'
WHEN dbo.ad_StrToFloat(p1r13.Raten) <> 0 THEN dbo.ad_StrToFloat(p1r13.RATEN)
else
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 19) END +
CASE
WHEN dbo.ad_StrToFloat(p1r11.Raten) <> 0 THEN dbo.ad_StrToFloat(p1r11.RATEN)
else
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 20) END +
CASE
WHEN dbo.ad_StrToFloat(p1r12.Raten) <> 0 THEN dbo.ad_StrToFloat(p1r12.RATEN)
else
dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 21) END ,'C') AS TOTAL,
-- Calculating Seafarer's part of Malta social security contribution
FORMAT ((CASE WHEN --1. not EU address or No in PR 10 - 0 contribution
EUCOUNTRY.EEA=0 OR
(P1R10.RATEN <> 'N' OR
P1R10.RATEN <> 'n' OR
P1R10.RATEN <> 'NO' OR
P1R10.RATEN <> 'No') THEN '0'
WHEN --2. Student <18 - contract type='10'
P01.CONTRACTTYPE = '10' AND
Datediff(yy,p01.birthdate,getdate())<18 AND
((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)*CB33.RATE/100) > CB32.RATENO THEN
cast(CB32.RATE as numeric(10,2))*4.33 --4.33 - Nr of weeks in a 30-days month
WHEN --3. Student over 18
P01.CONTRACTTYPE = '10'
AND Datediff(yy,p01.birthdate,getdate())>=18
AND ((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB33.RATE) > CB34.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) * CB31.RATE
WHEN --4. //Cat A - Person under 18 and earning not more then indicated in CB21
Datediff(yy,p01.birthdate,getdate())<18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE
THEN
cast(CB25.RATE as numeric(10,2)) * 4.33
WHEN -- 5. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21
Datediff(yy,p01.birthdate,getdate())>=18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB26.RATE as numeric(10,2)) * 4.33
WHEN --6. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount
Datediff(yy,p01.birthdate,getdate())>=18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB27.RATE
WHEN --7. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB22 and born before 31.12.1961
P01.BIRTHDATE <='19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB21.RATE AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB22.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB28.RATE
WHEN -- 8. Cat D - All persons whose basic weekly wage is equal to or exceeds CB22 and born before 31.12.1961
P01.BIRTHDATE <='19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB22.RATE
THEN
cast(CB29.RATE as numeric(10,2)) * 4.33
WHEN --9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB23 and born after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB21.RATE AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB23.RATE
THEN (CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB28.RATE
WHEN --10. Cat D - All persons whose basic weekly wage is equal to or exceeds CB23 and born after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB23.RATE
THEN
cast(CB30.RATE as numeric(10,2))*4.33
END),'C') AS SSC_SEAFARER,
-- Malta Maternity Leave - in case need to check separate amount for MLT Maternity leave used in calculation of SSC_EMPLOYER
/*
FORMAT ((CASE WHEN --1. not EU address or No in PR 10 - 0 contribution
EUCOUNTRY.EEA=0 OR
(P1R10.RATEN <> 'N' OR
P1R10.RATEN <> 'n' OR
P1R10.RATEN <> 'NO' OR
P1R10.RATEN <> 'No') THEN '0'
WHEN --2. Student <18 - contract type='10'
P01.CONTRACTTYPE = '10' AND
Datediff(yy,p01.birthdate,getdate())<18 AND
((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)*CB53.RATE/100) > CB52.RATENO THEN
cast(CB52.RATE as numeric(10,2))*4.33 --4.33 - Nr of weeks in a 30-days month
WHEN --3. Student over 18
P01.CONTRACTTYPE = '10'
AND Datediff(yy,p01.birthdate,getdate())>=18
AND ((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB53.RATE) > CB54.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) * CB51.RATE
WHEN --4. //Cat A - Person under 18 and earning not more then indicated in CB21
Datediff(yy,p01.birthdate,getdate())<18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE
THEN
cast(CB45.RATE as numeric(10,2)) * 4.33
WHEN -- 5. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21
Datediff(yy,p01.birthdate,getdate())>=18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB46.RATE as numeric(10,2)) * 4.33
WHEN --6. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount
Datediff(yy,p01.birthdate,getdate())>=18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB27.RATE
WHEN --7. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB22 and born before 31.12.1961
P01.BIRTHDATE <='19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB41.RATE AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB42.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB48.RATE
WHEN -- 8. Cat D - All persons whose basic weekly wage is equal to or exceeds CB22 and born before 31.12.1961
P01.BIRTHDATE <='19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB42.RATE
THEN
cast(CB49.RATE as numeric(10,2)) * 4.33
WHEN --9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB23 and born after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB41.RATE AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB43.RATE
THEN (CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB48.RATE
WHEN --10. Cat D - All persons whose basic weekly wage is equal to or exceeds CB23 and born after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB43.RATE
THEN
cast(CB50.RATE as numeric(10,2))*4.33
END),'C') AS SSC_Materinity*/
-- Calculating Employer's part of Malta contribution: Social Security+Maternity leave
-- Malta Social Security:
FORMAT
((CASE WHEN --1. not EU address or No in PR 10 - 0 contribution
EUCOUNTRY.EEA=0 OR
(P1R10.RATEN <> 'N' OR
P1R10.RATEN <> 'n' OR
P1R10.RATEN <> 'NO' OR
P1R10.RATEN <> 'No') THEN '0'
WHEN --2. Student <18 - contract type='10'
P01.CONTRACTTYPE = '10' AND
Datediff(yy,p01.birthdate,getdate())<18 AND
((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)*CB33.RATE/100) > CB32.RATENO THEN
cast(CB32.RATE as numeric(10,2))*4.33 --4.33 - Nr of weeks in a 30-days month
WHEN --3. Student over 18
P01.CONTRACTTYPE = '10'
AND Datediff(yy,p01.birthdate,getdate())>=18
AND ((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB33.RATE) > CB34.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) * CB31.RATE
WHEN --4. //Cat A - Person under 18 and earning not more then indicated in CB21
Datediff(yy,p01.birthdate,getdate())<18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE
THEN
cast(CB25.RATE as numeric(10,2)) * 4.33
WHEN -- 5. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21
Datediff(yy,p01.birthdate,getdate())>=18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB26.RATE as numeric(10,2)) * 4.33
WHEN --6. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount
Datediff(yy,p01.birthdate,getdate())>=18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB27.RATE
WHEN --7. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB22 and born before 31.12.1961
P01.BIRTHDATE <='19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB21.RATE AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB22.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB28.RATE
WHEN -- 8. Cat D - All persons whose basic weekly wage is equal to or exceeds CB22 and born before 31.12.1961
P01.BIRTHDATE <='19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB22.RATE
THEN
cast(CB29.RATE as numeric(10,2)) * 4.33
WHEN --9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB23 and born after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB21.RATE AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB23.RATE
THEN (CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB28.RATE
WHEN --10. Cat D - All persons whose basic weekly wage is equal to or exceeds CB23 and born after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB23.RATE
THEN
cast(CB30.RATE as numeric(10,2))*4.33
END) + -- + Malta Maternity leave
(CASE WHEN --1. not EU address or No in PR 10 - 0 contribution
EUCOUNTRY.EEA=0 OR
(P1R10.RATEN <> 'N' OR
P1R10.RATEN <> 'n' OR
P1R10.RATEN <> 'NO' OR
P1R10.RATEN <> 'No') THEN '0'
WHEN --2. Student <18 - contract type='10'
P01.CONTRACTTYPE = '10' AND
Datediff(yy,p01.birthdate,getdate())<18 AND
((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)*CB53.RATE/100) > CB52.RATENO THEN
cast(CB52.RATE as numeric(10,2))*4.33 --4.33 - Nr of weeks in a 30-days month
WHEN --3. Student over 18
P01.CONTRACTTYPE = '10'
AND Datediff(yy,p01.birthdate,getdate())>=18
AND ((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB53.RATE) > CB54.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) * CB51.RATE
WHEN --4. //Cat A - Person under 18 and earning not more then indicated in CB21
Datediff(yy,p01.birthdate,getdate())<18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE
THEN
cast(CB45.RATE as numeric(10,2)) * 4.33
WHEN -- 5. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21
Datediff(yy,p01.birthdate,getdate())>=18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB46.RATE as numeric(10,2)) * 4.33
WHEN --6. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount
Datediff(yy,p01.birthdate,getdate())>=18 AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB27.RATE
WHEN --7. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB22 and born before 31.12.1961
P01.BIRTHDATE <='19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB41.RATE AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB42.RATE
THEN
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB48.RATE
WHEN -- 8. Cat D - All persons whose basic weekly wage is equal to or exceeds CB22 and born before 31.12.1961
P01.BIRTHDATE <='19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB42.RATE
THEN
cast(CB49.RATE as numeric(10,2)) * 4.33
WHEN --9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB23 and born after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB41.RATE AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB43.RATE
THEN (CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB48.RATE
WHEN --10. Cat D - All persons whose basic weekly wage is equal to or exceeds CB23 and born after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB43.RATE
THEN
cast(CB50.RATE as numeric(10,2))*4.33
END),'C') AS SSC_EMPLOYER,
DATEDIFF(MONTH,convert(date,p03.DATEFROM),convert(date,convert(varchar,year(p03.datefrom))+'-12-31'))+1 AS Contract_Duration,
SH.[ON_OFF],
Case
When SH.[ON_OFF] in ('1/1','2/2','3/3') Then '183'
When SH.[ON_OFF] in ('3/2') Then '219'
When SH.[ON_OFF] in ('3/2') Then '243'
When SH.[ON_OFF] in ('4/2') Then '243'
Else ''
End AS 'Min_Days_Onb',
/*Case
When SH.[ON_OFF] in ('3/3') Then FORMAT((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 25)/(30*(6/12))), 'C')
When SH.[ON_OFF] in ('4/2') Then FORMAT((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 25)/(30*(8/12))), 'C')
Else
FORMAT((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 25)/30), 'C')
End AS MONTHLY_DAILY,*/
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 33), 'C') as MONTHLY_DAILY,
FORMAT(
Isnull(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0,25),0.00) /* TOTAL */
* Isnull(DATEDIFF(MONTH,convert(date,p03.DATEFROM),convert(date,convert(varchar,year(p03.datefrom))+'-12-31'))+1,0.00) /* Contract_Duration */
* Isnull(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0,30),0.00) /* Would return 0 because PS rate 30 is not defined */
, 'C') AS 'Annual_bonus',
Round (dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 35),0) as DAYS
FROM PW001P01 P01
LEFT JOIN PWORG ORG ON ORG.NUMORGID=P01.CLIENT
/*LEFT*/ JOIN PW001P03 P03 ON P03.PIN=P01.PIN
AND P03.Code IN (SELECT Code FROM PW001C12 WHERE OPTIONS LIKE '%S%')
--AND P03.DATEFROM >= GETDATE()
AND P03.PLANNED = 'Y'
AND NOT EXISTS (SELECT 1
FROM PW001P03 p03t
WHERE p03t.PIN = p01.Pin
AND p03t.CODE IN (SELECT Code FROM PW001C12 WHERE OPTIONS LIKE '%S%')
--AND p03t.DATEFROM >= GETDATE()
AND p03t.PLANNED = 'Y'
AND p03t.DATEFROM < P03.Datefrom
)
LEFT JOIN PWROT_SHIFT_ACTIVITIES PSA on P03.SEQUENCENO = PSA.ACTIVITIES
LEFT JOIN PWROT_ACTIVE_SHIFT PAS on PAS.SEQUENCENO= PSA.SEQUENCENO
LEFT JOIN (SELECT
CODE,
[NAME],
Case When [NAME] like '%/%'
Then Right(Left([NAME],charindex(')',name)-1),(charindex(')',name)-(charindex('(',name)+1)))
Else ''
End AS 'ON_OFF'
FROM PW001C36) SH on SH.CODE = Isnull(PAS.TURNUSCODE,P03.TURNUSCODE)
LEFT JOIN PW001P08 PASS ON P01.PIN=PASS.PIN
AND PASS.CODE='P010'
LEFT JOIN PW001P08 SB1 ON SB1.PIN=P01.PIN and SB1.CODE='S005'
LEFT JOIN PW001P08 SB2 ON SB2.PIN=P01.PIN and SB2.CODE='S006'
LEFT JOIN PW001P08 SB3 ON SB3.PIN=P01.PIN and SB3.CODE='S007'
left join pwcountry nat on nat.countrycode=p01.nationality
LEFT JOIN PWORG orgves
ON orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
LEFT JOIN PWORG orgpos
ON orgpos.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 5)
LEFT JOIN PWORGVES ves
ON ves.NUMORGID = orgves.NUMORGID
LEFT JOIN PWCCMCHANGECREWLIST sgnon
ON sgnon.NEXTACT = p03.SEQUENCENO
LEFT JOIN PWORGVESACT vpOn
ON vpOn.SEQNO = sgnon.SEQNO
LEFT JOIN PWPORT portOn
ON portOn.PORTCODE = vpOn.PLACEFROM
LEFT JOIN PWPSC000 PYS ON PYS.REGULATIVECODE = P03.PAYSCALETABLE
LEFT JOIN PW001P1R p1r10
ON p1r10.PIN = p01.PIN
AND p1r10.RATENO = 10
LEFT JOIN PWORGRAT CB21
ON CB21.NUMORGID = p01.CLIENT
AND CB21.RATENO = 21
LEFT JOIN PWORGRAT CB22
ON CB22.NUMORGID = p01.CLIENT
AND CB22.RATENO = 22
LEFT JOIN PWORGRAT CB23
ON CB23.NUMORGID = p01.CLIENT
AND CB23.RATENO = 23
LEFT JOIN PWORGRAT CB25
ON CB25.NUMORGID = p01.CLIENT
AND CB25.RATENO = 25
LEFT JOIN PWORGRAT CB26
ON CB26.NUMORGID = p01.CLIENT
AND CB26.RATENO = 26
LEFT JOIN PWORGRAT CB27
ON CB27.NUMORGID = p01.CLIENT
AND CB27.RATENO = 27
LEFT JOIN PWORGRAT CB28
ON CB28.NUMORGID = p01.CLIENT
AND CB28.RATENO = 28
LEFT JOIN PWORGRAT CB29
ON CB29.NUMORGID = p01.CLIENT
AND CB29.RATENO = 29
LEFT JOIN PWORGRAT CB30
ON CB30.NUMORGID = p01.CLIENT
AND CB30.RATENO = 30
LEFT JOIN PWORGRAT CB31
ON CB31.NUMORGID = p01.CLIENT
AND CB31.RATENO = 31
LEFT JOIN PWORGRAT CB32
ON CB32.NUMORGID = p01.CLIENT
AND CB32.RATENO = 32
LEFT JOIN PWORGRAT CB33
ON CB33.NUMORGID = p01.CLIENT
AND CB33.RATENO = 33
LEFT JOIN PWORGRAT CB34
ON CB34.NUMORGID = p01.CLIENT
AND CB34.RATENO = 34
LEFT JOIN PWORGRAT CB41
ON CB41.NUMORGID = p01.CLIENT
AND CB41.RATENO = 41
LEFT JOIN PWORGRAT CB42
ON CB42.NUMORGID = p01.CLIENT
AND CB42.RATENO = 42
LEFT JOIN PWORGRAT CB43
ON CB43.NUMORGID = p01.CLIENT
AND CB43.RATENO = 43
LEFT JOIN PWORGRAT CB45
ON CB45.NUMORGID = p01.CLIENT
AND CB45.RATENO = 45
LEFT JOIN PWORGRAT CB46
ON CB46.NUMORGID = p01.CLIENT
AND CB46.RATENO = 46
LEFT JOIN PWORGRAT CB47
ON CB47.NUMORGID = p01.CLIENT
AND CB47.RATENO = 47
LEFT JOIN PWORGRAT CB48
ON CB48.NUMORGID = p01.CLIENT
AND CB48.RATENO = 48
LEFT JOIN PWORGRAT CB49
ON CB49.NUMORGID = p01.CLIENT
AND CB49.RATENO = 49
LEFT JOIN PWORGRAT CB50
ON CB50.NUMORGID = p01.CLIENT
AND CB50.RATENO = 50
LEFT JOIN PWORGRAT CB51
ON CB51.NUMORGID = p01.CLIENT
AND CB51.RATENO = 51
LEFT JOIN PWORGRAT CB52
ON CB52.NUMORGID = p01.CLIENT
AND CB52.RATENO = 52
LEFT JOIN PWORGRAT CB53
ON CB53.NUMORGID = p01.CLIENT
AND CB53.RATENO = 53
LEFT JOIN PWORGRAT CB54
ON CB54.NUMORGID = p01.CLIENT
AND CB54.RATENO = 54
LEFT JOIN PWCOUNTRY CTRPASS
ON CTRPASS.COUNTRYCODE=PASS.ISSUE_COUNTRY
LEFT JOIN PWCOUNTRY CTRSB1
ON CTRSB1.COUNTRYCODE=SB1.ISSUE_COUNTRY
LEFT JOIN PWCOUNTRY CTRSB2
ON CTRSB2.COUNTRYCODE=SB2.ISSUE_COUNTRY
LEFT JOIN PWCOUNTRY CTRSB3
ON CTRSB3.COUNTRYCODE=SB3.ISSUE_COUNTRY
LEFT JOIN PWCOUNTRYPROP EUCOUNTRY
ON EUCOUNTRY.COUNTRYCODE=P01.ADDRESS_COUNTRY
LEFT JOIN PW001P1R p1r11
ON p1r11.PIN = p01.PIN
AND p1r11.RATENO = 11
LEFT JOIN PW001P1R p1r12
ON p1r12.PIN = p01.PIN
AND p1r12.RATENO = 12
LEFT JOIN PW001P1R p1r13
ON p1r13.PIN = p01.PIN
AND p1r13.RATENO = 13