JE Croatian Customer Invoice Tax

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Croatia Customer Invoice Tax Report
Application: European Localizations
Source: Croatian Customer Invoice Tax Report
Short Name: JEHRCITR_XMLP
DB package: JG_ZZ_SUMMARY_AR_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

	SELECT  
			JZVTD.reporting_code REPORTING_CODE
			,JZVTD.trx_id INVOICE_ID
			,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number) INVOICE_NUMBER
			,NULL EXPORT_NUMBER
			,TO_CHAR(JZVTD.trx_date,'DD-Mon-RRRR') INVOICE_DATE
			,JZVTD.trx_line_class TRX_LINE_CLASS_CODE
			,JZVTD.tax_rate_code_vat_trx_type_mng VAT_CODE
			,JZVTD.tax_rate_vat_trx_type_desc VAT_DESC
			,JZVTD.tax_rate_vat_trx_type_code VAT_TYPE
			,JZVTD.posted_flag POSTED_FLAG
			,SUM(NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt) + NVL( JZVTD.taxable_amt_funcl_curr,JZVTD.TAXABLE_AMT)
				) TRX_AMOUNT
			,SUM(NVL(JZVTD.tax_amt_funcl_curr,0) + NVL(JZVTD.taxable_amt_funcl_curr,0)
				) FUNC_AMOUNT
			,SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0 
										  ,'VAT-EXEMPT',0
										  ,'VAT-0',0
										  ,(DECODE(JZVTD.TAX_RATE,0,0, NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)))
						)
				) TAXABLE_AMOUNT 
			,SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0 
										  ,'VAT-EXEMPT',0
										  ,'VAT-0',0
										  ,(DECODE(JZVTD.TAX_RATE,0,0, NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt)))
						)
				) TAX_AMOUNT
			,SUM((DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
										  ,'VAT-EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
										  ,'VAT-0',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
										  ,DECODE(JZVTD.tax_rate,0,NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt),0)
						)				  
				   )
				) EXEMPT_AMOUNT 
				,SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0 
										  ,'VAT-EXEMPT',0
										  ,'VAT-0',0
										  ,(DECODE(JZVTD.TAX_RATE,0,0, NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)))
						)
				) + 
			    SUM(DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT', 0 
										  ,'VAT-EXEMPT',0
										  ,'VAT-0',0
										  ,(DECODE(JZVTD.TAX_RATE,0,0, NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt)))
						)
				) +
			    SUM((DECODE(JZVTD.reporting_code,'IL_VAT_EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
										  ,'VAT-EXEMPT',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
										  ,'VAT-0',NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
										  ,DECODE(JZVTD.tax_rate,0,NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt),0)
						)				  
				   )
				) DERIVED_TOT_AMT                                 -- bug 22480577 
			,NVL(JZVTD.billing_tp_taxpayer_id,'N') TAX_PAY_ID
	FROM     
			JG_ZZ_VAT_TRX_DETAILS JZVTD 
			,JG_ZZ_VAT_REP_STATUS JZVRS
	WHERE  	
			:P_CALLINGREPORT        		= 'JEILARDR'
			AND JZVRS.vat_reporting_entity_id    	= :P_VAT_REP_ENTITY_ID
			AND JZVTD.reporting_status_id         	= JZVRS.REPORTING_STATUS_ID
			AND JZVRS.tax_calendar_period        	= :P_PERIOD
			AND (JZVTD.tax_rate_vat_trx_type_code 	= :P_VAT_TRX_TYPE OR :P_VAT_TRX_TYPE IS NULL)
			AND JZVRS.source                     	= 'AR'
			AND JZVTD.reporting_code             	NOT IN ('VAT-NO-REP','VAT-Y')		
	GROUP BY 
			JZVTD.reporting_code
			,JZVTD.trx_id 
			,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number)
			,JZVTD.trx_date
			,JZVTD.trx_line_class
			,JZVTD.tax_rate_code_vat_trx_type_mng
			,JZVTD.tax_rate_vat_trx_type_desc
			,JZVTD.tax_rate_vat_trx_type_code
			,JZVTD.posted_flag
			,NVL(JZVTD.billing_tp_taxpayer_id,'N') 
UNION ALL
	SELECT 
			JZVTD.reporting_code REPORTING_CODE
			,JZVTD.trx_id INVOICE_ID
			,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number) INVOICE_NUMBER
			,NVL(RCTLA.global_attribute1,'999999999') EXPORT_NUMBER			
			,TO_CHAR(DECODE(LENGTH(NVL(RCTLA.global_attribute2,'A')),
                        1,JZVTD.TRX_DATE,                       
                        19,TO_DATE(RCTLA.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),
                        TO_DATE(RCTLA.global_attribute2, 'DD-MM-RRRR')
					),'DD-Mon-RRRR') INVOICE_DATE
			,JZVTD.trx_line_class TRX_LINE_CLASS_CODE
			,JZVTD.tax_rate_code_vat_trx_type_mng VAT_CODE
			,JZVTD.tax_rate_vat_trx_type_desc VAT_DESC
			,JZVTD.tax_rate_vat_trx_type_code VAT_TYPE
			,JZVTD.posted_flag POSTED_FLAG
			,SUM(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)+ NVL( JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt)
			    )  TRX_AMOUNT
			,SUM(NVL(JZVTD.tax_amt_funcl_curr,0) + NVL(JZVTD.taxable_amt_funcl_curr,0)
			    ) FUNC_AMOUNT
			,0 TAXABLE_AMOUNT 
			,0 TAX_AMOUNT 
			,SUM(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)) EXEMPT_AMOUNT
			,SUM(NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)) DERIVED_TOT_AMT  -- bug22480577 
			,'N/A' TAX_PAY_ID
	FROM  	
			JG_ZZ_VAT_TRX_DETAILS 		JZVTD 
			,JG_ZZ_VAT_REP_STATUS 		JZVRS
			,RA_CUSTOMER_TRX_ALL  		RCTA
			,RA_CUSTOMER_TRX_LINES_ALL  RCTLA
	WHERE  	
			:P_CALLINGREPORT        			= 'JEILARDR'
			AND JZVRS.vat_reporting_entity_id   		= :P_VAT_REP_ENTITY_ID
			AND JZVTD.reporting_status_id        		= JZVRS.REPORTING_STATUS_ID
			AND RCTA.customer_trx_id 			= RCTLA.customer_trx_id
			AND RCTA.customer_trx_id 		 	= JZVTD.trx_id
			AND RCTLA.customer_trx_line_id 			= JZVTD.trx_line_id
			AND JZVRS.tax_calendar_period        		= :P_PERIOD
			AND (JZVTD.tax_rate_vat_trx_type_code 		= :P_VAT_TRX_TYPE OR :p_vat_trx_type  IS NULL)
			AND JZVRS.source                     	 	= 'AR'
			AND JZVTD.reporting_code 		 	= 'VAT-Y'
	GROUP BY    
			JZVTD.reporting_code
			,JZVTD.trx_id 
			,DECODE(JZVTD.trx_line_class ,'ADJUSTMENT',JZVTD.applied_to_trx_number , JZVTD.trx_number)
			,NVL(RCTLA.global_attribute1,'999999999') 
			,DECODE(LENGTH(NVL(RCTLA.global_attribute2,'A')),
                        1,JZVTD.TRX_DATE,
                        19,TO_DATE(RCTLA.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),
                        TO_DATE(RCTLA.global_attribute2, 'DD-MM-RRRR'))
			,JZVTD.trx_line_class
			,JZVTD.tax_rate_code_vat_trx_type_mng
			,JZVTD.tax_rate_vat_trx_type_desc
			,JZVTD.tax_rate_vat_trx_type_code
			,JZVTD.posted_flag
	ORDER BY 6,1,4
Parameter Name SQL text Validation
Reporting Identifier 
 
 LOV Oracle
Tax Calendar Period 
 
 LOV Oracle
VAT Transaction Type 
 
 LOV Oracle
Download
 
Related Blitz Reports
JG EMEA VAT: Summary AR Extract JG ECE Receivables VAT Register JE German VAT for On-Account Receipts JE Israeli VAT AR Detail Register JE Israeli Summary VAT to Tax Authorities JE Portuguese Annual VAT JE UAE VAT JE Italian Payables Summary VAT JE Portuguese Periodic VAT
Blitz Report™

Blitz Report™ provides multiple benefits: