JG ECE Receivables VAT Register

Description
Categories: BI Publisher
Columns: Reporting Code, Invoice Id, Invoice Number, Export Number, Invoice Date, Trx Line Class Code, Vat Code, Vat Desc, Vat Type, Posted Flag ...
Application: Regional Localizations
Source: ECE Receivables VAT Register
Short Name: JGZZARVR_XMLP
DB package: JG_ZZ_SUMMARY_AR_PKG
	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
Exclude VAT Transaction Type
 
LOV Oracle
Calling Report