Skip to end of banner
Go to start of banner

Contract with Malta Payroll

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

« Previous Version 4 Next »

Summary

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

Keywords

Contract, Malta Payroll

Description

This script is used for creating a Mail Merge Contract that contains personal data, Passport, Seaman’s book details, joining data like Joining Port, Vessel, Position, and monthly salary amounts like Basic Wage, Guaranteed Overtime, Leave Payment, etc. It also contains amounts of Employee’s Malta Social Security contribution and Employer’s contribution - which is a total of Malta Social Security contribution and Malta Maternity Leave contribution (both calculated based on the conditions for EC 887 Malta Maternity leave and 889 Malta Social Security).

SQL Statement

-- upd. 10.09.2021
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, 7), 'C') as GOT,
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, 5), 'C') as OT,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 20), 'C') as SUPPL1,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 21), 'C') as SUPPL2,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0,25), 'C') as TOTAL,
FORMAT(dbo.ad_ReturnPayscaleAmount(P03.Payscale, P03.PayscaleTable, 0, 19), 'C') as RESERVERD,

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

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

  • No labels