Fetches invoice data for Malta VAT Return

VAT.sql

----------------------------------------
/**
 * 
 * Returns Malta VAT Information.
 * 
 */
----------------------------------------

SELECT 
	poh.POHNUM_0 AS order_number,
	bps.BPSNUM_0 AS supplier_code,
	bps.BPSNAM_0 AS supplier_name,
	bps.supplier_location AS  supplier_location,
	bps.goods_or_services AS goods_or_services,
	cls.CLSCOD_0 AS acct_classification,
	cls.CLSNAM_0 AS acct_classification_description,
	itm.ACCCOD_0 AS acct_code,
	itm.ITMDES1_0 AS acct_code_description,
	pid.ITMDES1_0 AS invoice_line_description,
	txt.TEXTE_0 AS order_summary,
	pih.NUM_0 AS invoice_number,
	poh.CUR_0 AS order_currency,
	ptd.LINAMT_0 AS net_amt_currency,
	ptd.DEDTAXLIN1_0 AS vat_amt_currency,
	pih.VAC_0 AS tax_rule,
	pid.VAT_0 AS vat_code,
	'' AS site_group,
	CASE 
		WHEN bps.supplier_location = 'EU' AND bps.goods_or_services = 'Goods and Services' AND cls.CLSCOD_0 IN (6,7,73) THEN '3'
		WHEN bps.supplier_location = 'Non-EU' AND bps.goods_or_services = 'Goods and Services' AND cls.CLSCOD_0 IN (6,7,73) THEN '4'
		WHEN bps.supplier_location = 'EU' AND bps.goods_or_services = 'Goods' AND cls.CLSCOD_0 IN (6,7,73) THEN '9'
		WHEN bps.supplier_location = 'EU' AND bps.goods_or_services = 'Services' AND cls.CLSCOD_0 IN (6,7,73) THEN '9A'
		WHEN bps.supplier_location = 'EU' AND bps.goods_or_services = 'Goods' AND cls.CLSCOD_0 BETWEEN 1 AND 5 THEN '10'
		WHEN bps.supplier_location = 'Non-EU' AND bps.goods_or_services = 'Goods and Services' AND cls.CLSCOD_0 IN (6,7,73) THEN '11'
		WHEN bps.supplier_location IN ('Non-EU','Malta') AND bps.goods_or_services = 'Goods' AND cls.CLSCOD_0 IN (6,7,73) AND pid.VAT_0 = 'SM' THEN '27'
		WHEN bps.supplier_location IN ('Non-EU','Malta') AND bps.goods_or_services = 'Goods' AND cls.CLSCOD_0 IN (6,7,73) AND pid.VAT_0 IN ('RM','R') THEN '28'
		WHEN bps.supplier_location IN ('Non-EU','Malta') AND bps.goods_or_services = 'Goods' AND cls.CLSCOD_0 IN (6,7,73) AND pid.VAT_0 IN ('E','Z') THEN '29'
		WHEN bps.supplier_location IN ('Non-EU','Malta') AND bps.goods_or_services = 'Goods' AND cls.CLSCOD_0 BETWEEN 1 AND 5 THEN '30'
		WHEN bps.supplier_location IN ('Malta') AND bps.goods_or_services = 'Services' AND cls.CLSCOD_0 IN (6,7,73) AND pid.VAT_0 = 'SM' THEN '31'
		WHEN bps.supplier_location IN ('Malta') AND bps.goods_or_services = 'Services' AND cls.CLSCOD_0 IN (6,7,73) AND pid.VAT_0 IN ('RM','R') THEN '32'
		ELSE '-'
	END AS malta_vat_return_box
FROM
	PINVOICE pih
JOIN 
	PINVOICED pid
	ON 
		pih.NUM_0 = pid.NUM_0
LEFT JOIN 
	(
		SELECT 
			poq.PTHNUM_0, poq.LINTEX_0, clb.CODE_0, clb.TEXTE_0, poq.PTDLIN_0
		FROM 
			PORDERQ poq 
		JOIN 
			TEXCLOB clb 
		ON 
			poq.LINTEX_0 = clb.CODE_0
	) AS txt
		ON
			pid.PTHNUM_0 = txt.PTHNUM_0
		AND 
			pid.PTDLIN_0 = txt.PTDLIN_0
JOIN 
	CPTANALIN cal
	ON 
		cal.ABRFIC_0 = 'PID' 
	AND 
		cal.VCRTYP_0 = 0 
	AND 
		cal.VCRNUM_0 = pid.NUM_0 
	AND 
		cal.VCRLIN_0 = pid.PIDLIN_0 
	AND 
		cal.VCRSEQ_0 = 0 
	AND 
		cal.ANALIG_0 = 1		
JOIN 
	PORDER poh
	ON 
		pid.POHNUM_0 = poh.POHNUM_0 
JOIN 
	(
		SELECT 
			a.BPSNUM_0,
			a.BPSNAM_0,
			CASE 
				WHEN b.CRY_0 IN ('AT','BE','BG','GB','ES','HR','CY','CZ','DK','EE','FI','FR','DE','GR','HU','IE','IT','LV','LT','LU','NL','PL','PT','RO','SK','SI','SE') THEN 'EU'
				WHEN b.CRY_0 IN ('MT') THEN 'Malta'
				WHEN b.CRY_0 IN ('EG') THEN 'Egypt'
				ELSE 'Non-EU'
			END AS supplier_location,
			CASE 
				WHEN a.ZGORS_0 = 1 THEN 'Goods'
				WHEN a.ZGORS_0 = 2 THEN 'Services'
				WHEN a.ZGORS_0 = 3 THEN 'Goods and Services'
				ELSE '-'
			END AS goods_or_services
		FROM 
			BPSUPPLIER a
		JOIN 
			BPARTNER b
			ON 
				a.BPSNUM_0 = b.BPRNUM_0
		JOIN 
			BPADDRESS c
			ON 
				b.BPRNUM_0 = c.BPANUM_0
	) AS bps
		ON 
			poh.BPSNUM_0 = bps.BPSNUM_0
JOIN 
	PRECEIPTD ptd 
	ON 
		pid.PTHNUM_0 = ptd.PTHNUM_0
	AND 
		pid.PIDLIN_0 = ptd.PTDLIN_0
JOIN 
	PORDERP pop
	ON 
		ptd.POHNUM_0 = pop.POHNUM_0
	AND 
		ptd.POPLIN_0 = pop.POPLIN_0
JOIN 
	ITMMASTER itm
	ON 
		pid.ITMREF_0 = itm.ITMREF_0
JOIN 
	GACCOUNT gac
	ON 
		cal.COA_0 = gac.COA_0 and cal.ACC_0 = gac.ACC_0
JOIN
	GACCCLS cls
	ON 
		gac.CLSCOD_0 = cls.CLSCOD_0