Table of Contents
minLevel	1
maxLevel	1
type	flat
separator	pipe
Show if
groupIds	68760e96-f155-4a32-82b0-b8c9f860249f
users
matchUsing	any
The below is only visible for Adonis Consultancy:
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 TOTALSUBSIS,
FORMAT(

	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, 6) +
	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 ((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, 1)/4.33/100)*CB33.RATE) > CB34.RATE
THEN
(dbo.ad_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)/10021) END	,'C') * 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

WHENAS 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         		-- 5. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21
              --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) <=CB21*CB33.RATE/100) AND> (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB26CB32.RATENO THEN 
	cast(CB32.RATE as numeric(10,2)) * 4.33  WHEN		   --64. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount33 - 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) > <=CB21CB34.RATE
	THEN
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) *CB27 CB31.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
	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
AND	THEN
	cast(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

WHENCB25.RATE as numeric(10,2)) * 4.33
	
	WHEN        		-- 85. Cat D - AllB personsPersons whoseaged basic18 weeklyand wageover isearning equalnot tomore orthen exceedsamount CB22indicated andin born before 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)> CB22<=CB21.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
	THEN
	cast(CB29CB26.RATE as numeric(10,2)) * 4.33
	
	WHEN 		--96. Cat - CB -Persons Allaged persons18 whoseand basicover weeklyearning wagenot ismore equalthen toamount orindicated exceedsin CB21 and lesswith thenvolunteer 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
amount
	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)*CB28CB27.RATE
	
	WHEN 		--107. Cat DC - All persons whose basic weekly wage is equal to or exceeds CB23CB21 and less bornthen after 31CB22 and born before 31.12.1961
	P01.BIRTHDATE ><='19611231' AND 
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB23CB21.RATE AND 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) <=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)*CB53 <=CB23.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 (((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/100)*CB53.RATE) > CB54CB23.RATE
	THEN 
	cast(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
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<18 AND 
	((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41*CB53.RATE/100) AND> (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB46.RATE CB52.RATENO THEN 
	cast(CB52.RATE as numeric(10,2)) * 4.33  WHEN		   --64. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount33 - 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) <=CB41*CB53.RATE) > CB54.RATE
	THEN
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) *CB27 CB51.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
	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) <=CB42CB41.RATE
	THEN
	cast(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB48.RATE

WHENCB45.RATE as numeric(10,2)) * 4.33
	
	WHEN        		-- 85. Cat D - AllB personsPersons whoseaged basic18 weeklyand wageover isearning equalnot tomore orthen exceedsamount CB22indicated andin born before 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)> CB42<=CB41.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
	THEN
	cast(CB49CB46.RATE as numeric(10,2)) * 4.33
	
	WHEN 		--96. Cat C - AllB personsPersons whoseaged basic18 weeklyand wageover isearning equalnot tomore orthen exceedsamount CB21indicated andin lessCB21 then CB23 and bornwith after 31.12.1961
P01.BIRTHDATE >'19611231' AND
(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)> CB41.RATE AND
volunteer amount
	Datediff(yy,p01.birthdate,getdate())>=18 AND
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB43CB41.RATE
	THEN
(CONVERT(MONEY, ROUND	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1), 2), 0)/100)*CB48CB27.RATE
	
	WHEN 		--107. Cat DC - All persons whose basic weekly wage is equal to or exceeds CB23CB21 and less then CB22 and born afterbefore 31.12.1961
	P01.BIRTHDATE ><='19611231' AND 
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB43CB41.RATE AND 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) <=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)*CB33> CB41.RATE/100) >AND
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.	(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/100)*CB33.RATE) > CB34CB43.RATE
	THEN 
	cast(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
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<18 AND 
	((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB21*CB33.RATE/100) AND> (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB26CB32.RATENO THEN 
	cast(CB32.RATE as numeric(10,2)) * 4.33     WHEN		--64. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount33 - 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) > <=CB21CB34.RATE
	THEN
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) *CB27 CB31.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
	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
AND	THEN
	cast(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

WHENCB25.RATE as numeric(10,2)) * 4.33
	
	WHEN        		-- 85. Cat D - AllB personsPersons whoseaged basic18 weeklyand wageover isearning equalnot tomore orthen exceedsamount CB22indicated andin born before 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)> CB22<=CB21.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
	THEN
	cast(CB29CB26.RATE as numeric(10,2)) * 4.33
	
	WHEN 		--96. Cat - CB -Persons Allaged persons18 whoseand basicover weeklyearning wagenot ismore equalthen toamount orindicated exceedsin CB21 and lesswith thenvolunteer 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
amount
	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)*CB28CB27.RATE
	
	WHEN 		--107. Cat DC - All persons whose basic weekly wage is equal to or exceeds CB23 CB21 and less then CB22 and born afterbefore 31.12.1961
	P01.BIRTHDATE ><='19611231' AND 
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33)>= CB23CB21.RATE AND 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) <=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)*CB53 <=CB23.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 (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/100)*CB53)> CB23.RATE)
> CB54	THEN 
	cast(CB30.RATE THENas (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
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<18 AND 
	((dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/4.33) <=CB41*CB53.RATE/100) AND> (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
THEN
cast(CB46CB52.RATENO THEN 
	cast(CB52.RATE as numeric(10,2)) * 4.33     WHEN		--64. Cat - B Persons aged 18 and over earning not more then amount indicated in CB21 and with volunteer amount33 - 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) <=CB41*CB53.RATE) > CB54.RATE
	THEN
	(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100) *CB27 CB51.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
	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) <=CB42CB41.RATE
	THEN
	cast(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 1)/100)*CB48.RATE

WHEN CB45.RATE as numeric(10,2)) * 4.33
	
	WHEN        		-- 85. Cat D - AllB personsPersons whoseaged basic18 weeklyand wageover isearning equalnot tomore orthen exceedsamount CB22indicated andin born before 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)> CB42<=CB41.RATE AND (P1R10.RATEN='MIN' OR P1R10.RATEN='min')
	THEN
	cast(CB49CB46.RATE as numeric(10,2)) * 4.33
	
	WHEN 		--96. 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_COUNTRY - 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
Version	Old Version 4	New Version Current
Changes made by	Daria Chebotaieva	Helen Ustych
Saved on	Jul 25, 2022	Apr 24, 2024
Page Comparison

Versions Compared

Key

SQL Statement
