JE German VAT for On-Account Receipts

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: European Localizations
Source: German VAT for On-Account Receipts
Short Name: JEDEDVOR_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 Level
 
LOV Oracle
Legal Entity
 
LOV Oracle
Ledger
 
LOV Oracle
Balancing Segment
 
Regime Code
 
LOV Oracle
Tax
 
LOV Oracle
Tax Status
 
LOV Oracle
Jurisdiction
 
LOV Oracle
Tax Rate
 
LOV Oracle
GL Start Date
 
Date
GL End Date
 
Date
Chart of Accounts ID
 
LOV Oracle
Calling Report