Versions Compared

Version Old Version 4 New Version 5
Changes made by

Daria Chebotaieva

Helen Ustych

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe
Show if
groupIds5d023959-9633-4358-b622-e0592e954889
users
matchUsingany

The contract was originally implemented for Ritz-Carlton

Page Properties

Summary

Script for Merge Contract that shows Malta Social Security and Maternity Leave contribution amounts

Keywords

Contract, Malta Payroll

...

SQL Statement

Code Block
-- upd. 10.09.2021-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, 75), 'C') as GOTOT,
	FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 116), 'C') as WEEKENDCONS_TO,
	FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 147), 'C') as LPGOT,
	FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 510), 'C') as WEEKEND_OT,
	FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 2011), 'C') as SUPPL1WEEKEND,
	FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 2114), 'C') as SUPPL2LP,
	FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0,25 15), 'C') as TOTAL,
FORMAT(dbo.ad_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 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
((	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, 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 ((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, 1)/4.33/100)*CB33.RATE) > CB34.RATE
THEN
(dbo.ad_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, 1)/10019) *END CB31.RATE+
	
WHEN	CASE 
	WHEN dbo.ad_StrToFloat(p1r11.Raten) <> 0            --4. //Cat A - Person under 18 and earning not more then indicated in CB21
Datediff(yy,p01.birthdate,getdate())<18 AND
(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, 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
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<18 AND 
	((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)*CB33.RATE/100) > <=CB21CB32.RATERATENO THEN 
	cast(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
(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) > <=CB22CB34.RATE
	THEN
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) *CB28 CB31.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
--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)> CB22<=CB21.RATE
	THEN
	cast(CB29CB25.RATE as numeric(10,2)) * 4.33
	
	WHEN        		--9 5. Cat C - AllB personsPersons whoseaged basic18 weeklyand wageover isearning equal to or exceeds CB21 and lessnot more then CB23amount andindicated bornin after 31.12.1961
P01.BIRTHDATE >'19611231'CB21
	Datediff(yy,p01.birthdate,getdate())>=18 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
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)> CB23<=CB21.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)/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)*CB53> CB22.RATE/100) >
CB52.RATENO 	THEN
	cast(CB52CB29.RATE as numeric(10,2)) * 4.33
	
	WHEN   		--4.339. Cat C - NrAll ofpersons weekswhose inbasic aweekly 30-dayswage monthis WHENequal to or exceeds CB21 and less then CB23 and born              --3. Student over 18
P01.CONTRACTTYPE = '10'
AND Datediff(yy,p01.birthdate,getdate())>=18

AND ((after 31.12.1961
	P01.BIRTHDATE >'19611231' AND 
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB53.RATE) > CB54CB21.RATE THENAND
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/1004.33) * CB51<=CB23.RATE

WHEN                 --4. //Cat A - Person under 18 and earning not more then indicated in CB21
Datediff(yy,p01.birthdate,getdate())<18 AND
(	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)> <=CB41CB23.RATE
	THEN 
	cast(CB45CB30.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
	
	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
(<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
*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) > <=CB42CB54.RATE
	THEN
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) *CB48 CB51.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
          --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)> CB42<=CB41.RATE
	THEN
	cast(CB49CB45.RATE as numeric(10,2)) * 4.33
	
	WHEN        		--9 5. Cat C - AllB personsPersons whoseaged basic18 weeklyand wageover isearning equalnot to or exceeds CB21 and less more then CB23amount andindicated bornin after 31.12.1961
P01.BIRTHDATE >'19611231'CB21
	Datediff(yy,p01.birthdate,getdate())>=18 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
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)> CB43<=CB41.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)/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)*CB33> CB42.RATE/100) >
CB32.RATENO 	THEN
	cast(CB32CB49.RATE as numeric(10,2)) * 4.33
	
	WHEN   		--4.339. Cat C - NrAll ofpersons weekswhose inbasic aweekly 30-dayswage monthis WHENequal to or exceeds CB21 and less then CB23 and born              --3. Student over 18
P01.CONTRACTTYPE = '10'
AND Datediff(yy,p01.birthdate,getdate())>=18

AND ((after 31.12.1961
	P01.BIRTHDATE >'19611231' AND 
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33/100)*CB33.RATE) > CB34CB41.RATE THENAND
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/1004.33) * CB31<=CB43.RATE

WHEN                 --4. //Cat A - Person under 18 and earning not more then indicated in CB21
Datediff(yy,p01.birthdate,getdate())<18 AND
	THEN (CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/4.33) <=CB21.RATE
THEN
cast(CB25.RATE as numeric(10,2)) * 4.33

WHEN        100)*CB48.RATE
	WHEN 		-- 510. Cat D - All persons Bwhose Personsbasic agedweekly 18wage andis overequal earningto notor moreexceeds thenCB23 amountand indicatedborn in CB21
Datediff(yy,p01.birthdate,getdate())>=18 AND
after 31.12.1961
	P01.BIRTHDATE >'19611231' AND 
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> <=CB21CB43.RATE
AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB26	THEN 
	cast(CB50.RATE as numeric(10,2)) * 4.33
	END),'C') AS 
WHEN	SSC_Materinity*/
	
	
	--6. CatCalculating -Employer's Bpart Personsof agedMalta 18contribution: andSocial overSecurity+Maternity earningleave
not	-- moreMalta thenSocial amountSecurity:
indicated	FORMAT 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
	
	((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)/1004.33)*CB28CB33.RATE/100) > CB32.RATENO THEN 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.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) > CB22CB34.RATE
	THEN
cast(CB29.RATE as numeric(10,2)	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) * 4CB31.33RATE
	
	WHEN 		--9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 and less then CB23--4. //Cat A - Person under 18 and bornearning not after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB21.RATE AND
more then indicated in CB21
	Datediff(yy,p01.birthdate,getdate())<18 AND
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB23CB21.RATE
	THEN
(CONVERT(MONEY, ROUND(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB28.RATE
WHEN	cast(CB25.RATE as numeric(10,2)) * 4.33
	
	WHEN        		--10 5. Cat D - AllB personsPersons whoseaged basic18 weeklyand wageover isearning equalnot tomore orthen exceedsamount CB23indicated andin born after 31.12.1961
P01.BIRTHDATE >'19611231'CB21
	Datediff(yy,p01.birthdate,getdate())>=18 AND
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB23<=CB21.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
	THEN
	cast(CB30CB26.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
(	
	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)*CB53 <=CB22.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 (	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/100)*CB53.RATE) > CB54CB22.RATE 
	THEN
	cast(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100CB29.RATE as numeric(10,2)) * CB514.RATE33
	
	WHEN 		--9. Cat C - All persons whose basic weekly wage is equal to or exceeds CB21 --4. //Cat A - Person under 18and less then CB23 and earningborn not more then indicated in CB21
Datediff(yy,p01.birthdate,getdate())<18 AND
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) <=CB41CB23.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
(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)> <=CB41CB23.RATE
AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB46	THEN 
	cast(CB30.RATE as numeric(10,2)) * 4.33
	
WHEN			END) +  --6. Cat+ -Malta BMaternity Personsleave
aged	
18	
and	(CASE over earningWHEN --1. not moreEU thenaddress amountor indicatedNo in CB21PR and10 with- volunteer0 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(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) >= CB41CB52.RATERATENO THEN AND
	cast(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

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 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_COUNTRYCB52.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