JE German VAT for On-Account Receipts

	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