Description
The view is used to present the Malta Social Security Tax calculation summary for historical periods. It includes the details of employer tax, number of Malta weeks (NIs), Category, Basic Weekly Wage, and Gross Salary.
We recommend splitting the view by year to avoid the issues with view performance due to lengthy historical data processing.
Selection
To create separate views per year, update the periods' selections at the bottom of the script in this line:
and pyh.periodeused>202200 and pyh.periodeused<=202212
SELECT
p01.PIN,
p01.CLIENT NUMORGID,
p01.EMPLOYMENTSTARTDATE,
p01.EMPLOYMENTENDDATE,
P01.NAME,
1 AS [COUNT],
C02.NAME AS 'RANK',
PYH.ACCOUNTDIM3 AS 'DEPARTMENT NAME',
VES.NAME AS 'VESSEL NAME',
pyh.ENTRYFIELD4 AS 'CATEGORY',
pyh.PAYSCALETABLE,
pyh.PAYSCALECODE,
pyh.periodeused as 'PERIOD',
case when entrycode in (887, 886) and (ROUND (CALCULATEDAMOUNT1, 2) + ROUND (CALCULATEDAMOUNT3, 2) <> 0) then 1 else 0 end AS perscount,
case when entrycode in (887, 886) then CONVERT(FLOAT, CASE WHEN entryfield5 = '' THEN '0' ELSE REPLACE(entryfield5, ',', '.') END ) else 0 end AS 'NIs',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT1, 2) else 0 end AS 'EE Amount EUR',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT3, 2) else 0 end AS 'ER Amount EUR',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT1, 2) + ROUND (CALCULATEDAMOUNT3, 2) else 0 end AS 'TOTAL in EUR',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT1 * currencyrateforpayment, 2) else 0 end AS 'EE Amount USD',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT3 * currencyrateforpayment, 2) else 0 end AS 'ER Amount USD',
case when entrycode in (887, 886) then (ROUND (CALCULATEDAMOUNT1, 2) + ROUND (CALCULATEDAMOUNT3, 2)) * currencyrateforpayment else 0 end AS 'TOTAL in USD',
case when entrycode in (1) then ROUND (Calculatedamount1, 2) else 0 end GrossEUR,
case when entrycode in (1) then ROUND (Calculatedamount1*currencyrateforpayment, 2) else 0 end GrossUSD,
Case When pyh.entryfield6 = '' or ENTRYFIELD6 like '%[a-z]%' then null else round(cast(replace(pyh.entryfield6,',','.') as float),2) end as 'Weekly Rate EUR',
Case When pyh.entryfield6 = '' or ENTRYFIELD6 like '%[a-z]%' then null else round(cast(replace(pyh.entryfield6,',','.') as float),2) end * pyh.currencyrateforpayment as 'Weekly Rate USD'
FROM pw001p01 p01
JOIN PW001PYH pyh ON p01.pin = pyh.pin
LEFT JOIN pw001c02 c02 ON c02.code = pyh.rank
LEFT JOIN PWORG VES ON VES.NUMORGID=PYH.ACCOUNTDIM2
LEFT JOIN PWPSC000 PYS ON PYS.REGULATIVECODE = PYH.PAYSCALETABLE
LEFT JOIN PW001P1R P1R ON P1R.PIN=PYH.PIN AND P1R.RATENO=1
WHERE pyh.entrycode in (1, 887 ,886)
and exists(select pin from PW001PAY h2 where h2.pin=pyh.pin and h2.periodeused=pyh.periodeused and h2.entrycode in (887))
and pyh.periodeused>202200 and pyh.periodeused<=202212
Field Specification
Other
