Versions Compared

Version Old Version 1 New Version Current
Changes made by

Valentyna Herasymova

Sigrid Kviteberg

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Page Properties
Description SummaryCompatibility SQL Version

Summary

Malta Social Security Tax calculation

Keywords

Malta Tax

File / Script Link

Compatibility APM Version

Description

The view is used to present the summary of Malta Social Security Tax calculation in the current period. It includes the details of employee and employer tax, number of Malta weeks (NIs), Category, Basic Weekly Wage, and Gross Salary.

...

Code Block
languagesql
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,
	case when entrycode in (888, 889) and ((ROUND (CALCULATEDAMOUNT1, 2) + ROUND (CALCULATEDAMOUNT3, 2)) <> 0) then 1 else 0 end AS perscount,
	case when entrycode in (888, 889) then CONVERT(FLOAT, CASE WHEN entryfield5 = '' THEN '0' ELSE REPLACE(entryfield5, ',', '.') END ) else 0 end  AS 'NIs',
	case when entrycode in (888, 889) then ROUND (CALCULATEDAMOUNT1, 2) else 0 end AS 'EE Amount EUR',
	case when entrycode in (888, 889) then ROUND (CALCULATEDAMOUNT3, 2) else 0 end AS 'ER Amount EUR',
	case when entrycode in (888, 889) then (ROUND (CALCULATEDAMOUNT1, 2) + ROUND (CALCULATEDAMOUNT3, 2)) else 0 end AS 'TOTAL in EUR',
	case when entrycode in (888, 889) then ROUND (CALCULATEDAMOUNT1 * currencyrateforpayment, 2) else 0 end AS 'EE Amount USD',
	case when entrycode in (888, 889) then ROUND (CALCULATEDAMOUNT3 * currencyrateforpayment, 2) else 0 end AS 'ER Amount USD',
	case when entrycode in (888, 889) 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 'Gross EUR',
	case when entrycode in (1) then ROUND (Calculatedamount1*currencyrateforpayment, 2) else 0 end 'Gross USD',
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 pw001pay 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, 888, 889) 
    --and exists(select pin from pw001pay h2 where h2.pin=pyh.pin and h2.periodeused=pyh.periodeused and h2.entrycode in (888, 889))

...