-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