Spain SII Report

----Received Invoices PAYABLES
SELECT
	PK PK_G4,
	CONPAR1||'|'||CONPAR2||'|'||CONPAR3||'|'||CONPAR4||'|'||CONPAR5||'|'||CONPAR6 CONPAR_STR
--, INVOICE_NUM
--, CONPAR2
--, CONPAR4
FROM
(
SELECT DISTINCT 
	AIA.INVOICE_ID PK,
	'CONPAR' CONPAR1,
	HZP.PARTY_NAME CONPAR2,
	NULL CONPAR3,
 	(CASE WHEN zptp.COUNTRY_CODE = 'ES' THEN REGEXP_REPLACE(REP_REGISTRATION_NUMBER, '^ES','')
      WHEN  zptp.COUNTRY_CODE = 'US' THEN 'US'||PSP.INCOME_TAX_ID
      ELSE (CASE WHEN SUBSTR(UPPER(ZPTP.REP_REGISTRATION_NUMBER), 1, 2) = UPPER( zptp.COUNTRY_CODE) THEN ZPTP.REP_REGISTRATION_NUMBER
                 ELSE  zptp.COUNTRY_CODE || ZPTP.REP_REGISTRATION_NUMBER
           END)  
    END)  CONPAR4, 

	 (CASE WHEN zptp.COUNTRY_CODE <> 'ES' THEN zptp.COUNTRY_CODE ELSE NULL END) CONPAR5, 

    	
	 (CASE WHEN zptp.COUNTRY_CODE IN (
	'AT','BE','BG','CY','DK','FI','FR','GR','HU','IE','IT','LU','MT','NL','PL','PT','DE','RO','SE'
	,'LV','EE','LT','CZ','SK','SI','HR') THEN '02' 
	WHEN zptp.COUNTRY_CODE = 'ES' THEN NULL
	ELSE '06'
	END) CONPAR6 
	
	
	
FROM
	AP_INVOICES_ALL AIA,
	AP_INVOICE_LINES_ALL AILA,
	POZ_SUPPLIERS PS, 
	POZ_SUPPLIERS_PII PSP,
	HZ_PARTIES HZP,
	ZX_LINES ZL,
	GL_PERIODS GP,
	GL_LEDGERS GL,
	ZX_RATES_VL ZRV,
	ZX_PARTY_TAX_PROFILE ZPTP

	
WHERE
	1=1
	AND AIA.INVOICE_ID = AILA.INVOICE_ID
	AND AIA.VENDOR_ID = PS.VENDOR_ID
	AND PS.VENDOR_ID = PSP.VENDOR_ID(+)
	AND PS.PARTY_ID = HZP.PARTY_ID(+)
	AND AIA.INVOICE_ID = ZL.TRX_ID
	AND AILA.PERIOD_NAME = GP.PERIOD_NAME
	AND AIA.SET_OF_BOOKS_ID = GL.LEDGER_ID
	AND GL.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
	AND AILA.TAX_RATE_ID = ZRV.TAX_RATE_ID(+)
	AND AILA.TAX_RATE_CODE = ZRV.TAX_RATE_CODE(+)
	AND AIA.APPROVAL_STATUS = 'APPROVED'
	--AND HZP.IDEN_ADDR_PARTY_SITE_ID = ZPTP.PARTY_ID(+)
	AND aia.party_site_id = zptp.party_id (+)
	AND AP_INVOICES_PKG.GET_POSTING_STATUS(AIA.INVOICE_ID) = 'Y'
		AND 'Received Invoices Register' = :P_REGISTER_TYPE
	AND GL.NAME IN ('PL SPAIN EUR GAAP','SL SPAIN EUR LOCAL')
	AND (GL.name                    	IN (:P_LEDGER)
	OR  'All'                       	IN (:P_LEDGER||'All'))
	AND ZL.TAX_REGISTRATION_NUMBER = (case when ZL.TAX_REGISTRATION_NUMBER like 'ES%'
	THEN 
	'ES'||:P_TAX_REG_NUM
	ELSE 
	:P_TAX_REG_NUM END)
	AND AIA.GL_DATE BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
	
UNION
--Issued Invoices - RECEIVABLES
SELECT
	RCTA.CUSTOMER_TRX_ID PK,
	'CONPAR' CONPAR1,
	HP.PARTY_NAME CONPAR2,
	NULL CONPAR3,
/* (CASE WHEN HP.COUNTRY = 'ES' THEN REGEXP_REPLACE(ZPTP.REP_REGISTRATION_NUMBER, '^ES','')
      ELSE (CASE WHEN SUBSTR(UPPER(ZPTP.REP_REGISTRATION_NUMBER), 1, 2) = UPPER(HP.COUNTRY) THEN ZPTP.REP_REGISTRATION_NUMBER
                 ELSE HP.COUNTRY || ZPTP.REP_REGISTRATION_NUMBER
           END)  
    END)	 CONPAR4, */

--**tax Registration num from cust Sites**--
(CASE WHEN HP.COUNTRY = 'ES' THEN REGEXP_REPLACE(nvl(zr1.registration_number, ZPTP.REP_REGISTRATION_NUMBER), '^ES','')
      ELSE (CASE WHEN SUBSTR(UPPER(nvl(zr1.registration_number, ZPTP.REP_REGISTRATION_NUMBER)), 1, 2) = UPPER(HP.COUNTRY) THEN nvl(zr1.registration_number, ZPTP.REP_REGISTRATION_NUMBER)
                 ELSE HP.COUNTRY || nvl(zr1.registration_number, ZPTP.REP_REGISTRATION_NUMBER)
           END)  
    END)	 CONPAR4,
--**end**--

	(CASE WHEN HP.COUNTRY <> 'ES' THEN
	HP.COUNTRY ELSE NULL END) CONPAR5,
	(CASE WHEN ZRV.TAX_RATE_NAME IN ('ESARGOODSEU', 'ESARSERVEU') THEN '02'
      WHEN ZRV.TAX_RATE_NAME IN ('ESARGOODSNEU', 'ESARSERVNEU') THEN '04'
      WHEN ZRV.TAX_RATE_NAME IN ('ESAR05.2GOODSRDE', 'ESAR21.0GOODS','ESSPNLZ', 'ESEMTCNOVAT', 'ESARNOVAT' ) THEN ''
      WHEN RCTA.DEFAULT_TAXATION_COUNTRY IN ('AT','BE','BG','CY','DK','FI','FR','GR','HU','IE','IT','LU','MT','NL','PL','PT','DE','RO','SE','LV','EE','LT','CZ','SK','SI','HR') THEN '02'
      WHEN RCTA.DEFAULT_TAXATION_COUNTRY = 'ES' THEN NULL
      ELSE '04'
     END) CONPAR6

FROM
	RA_CUSTOMER_TRX_ALL RCTA,
	RA_CUSTOMER_TRX_LINES_ALL RCTLA,
	ZX_RATES_VL ZRV,
	ZX_LINES ZL,
	HZ_PARTIES HP,
	GL_LEDGERS GL,
	ZX_PARTY_TAX_PROFILE ZPTP,
	HZ_CUST_ACCOUNTS HCA
--** added to get cust site Tax Reg num**--
, hz_cust_acct_sites_all hcas
, hz_party_sites hps 
, zx_party_tax_profile ztp
, zx_registrations zr1
--**end**--
--**added**--
, zx_registrations zr 
, zx_party_tax_profile zptp1
--**end**--
	
WHERE
	1=1
	AND RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID
	AND RCTLA.TAX_LINE_ID = ZL.TAX_LINE_ID
	AND ZL.TAX_RATE_ID = ZRV.TAX_RATE_ID
	AND GL.LEDGER_ID = RCTA.SET_OF_BOOKS_ID
	AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
	AND HCA.PARTY_ID = HP.PARTY_ID
	AND Hp.IDEN_ADDR_PARTY_SITE_ID = ZPTP.PARTY_ID(+)
	--** To get Tax Registration column (CONPAR 4) from Cust Sites **--	
	AND hca.cust_account_id = hcas.cust_account_id
	AND hcas.party_site_id = hps.party_site_id
	AND hp.party_id = ztp.party_id(+)
	AND ztp.party_tax_profile_id = zr1.party_tax_profile_id(+)
	AND RCTA.COMPLETE_FLAG = 'Y'  -- to get accounted and validated AR invoices
	--**end**--
	
	--**ADDED**--
	AND ZR.PARTY_TAX_PROFILE_ID = zptp1.party_tax_profile_id
    AND zptp1.party_tax_profile_id = ZL.HQ_ESTB_PARTY_TAX_PROF_ID
	--**end**--
	
	AND 'Issued Invoices Register' = :P_REGISTER_TYPE
	AND GL.NAME IN ('PL SPAIN EUR GAAP','SL SPAIN EUR LOCAL')
	AND (GL.name                    	IN (:P_LEDGER)
	OR  'All'                       	IN (:P_LEDGER||'All'))
	AND NVL(ZL.TAX_REGISTRATION_NUMBER,zr.registration_number) = (case when ZL.TAX_REGISTRATION_NUMBER like 'ES%'
	THEN 
	'ES'||:P_TAX_REG_NUM
	ELSE 
	:P_TAX_REG_NUM END)
	AND RCTA.TRX_DATE BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
)
ORDER BY PK