<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="SalesVoucherListNotUsed.xsl">
	<title>SalesVoucherListNotUsed</title>

<report-parameter name="TENANT" type="integer" hidden="true">
		<preset>@TENANT_NO</preset>
	</report-parameter>
	<report-parameter name="COMPANY" type="integer" hidden="true">
		<preset>@COMPANY_NO</preset>
	</report-parameter>
	<report-parameter name="DATE" type="date" label="Von Datum:">
		<preset>@TODAY</preset>
		<input type="datepicker" />
	</report-parameter>
	<report-parameter name="TO_DATE" type="date" label="Von Datum:">
		<preset>@TODAY</preset>
		<input type="datepicker" />
	</report-parameter>
	<report-parameter name="BUSINESSUNIT" type="integer">
		<label>Filiale:</label>
		<input type="selectlist">
			<source type="dataset" name="businessunits">
			</source>
		</input>
	</report-parameter>


	<report-result report-dataset="businessunits">
	</report-result>
	<report-result report-dataset="sales_voucher_list">
	</report-result>




	<report-dataset name="businessunits">
		<sql>
			select * from businessunits where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no=@DEPARTMENT_NO and businessunit_no=@BUSINESSUNIT_NO and pos_cd_hint=@POS_CD
		</sql>
	</report-dataset>
	

<report-dataset name="sales_voucher_list">
		<sql>
			
			SELECT  sv.sales_voucher_cd, sv.voucher_value FROM sales_vouchers sv

			JOIN (
					SELECT 	distinct svu.tenant_no, svu.pos_cd, svu.sales_voucher_id 
					FROM 	sales_voucher_uses svu
					where 		svu.tenant_no = @TENANT_NO AND svu.pos_cd = @POS_CD
						AND		CAST (svu.SALES_VOUCHER_USE_TS  AS DATE) &gt;= CAST (@DATE  AS DATE)   
						AND 	CAST (svu.SALES_VOUCHER_USE_TS  AS DATE) &lt;= CAST (@TODATE  AS DATE)  
						AND 	svu.voucher_state = 1 
		
				) svOut
				ON 		svOut.tenant_no = sv.tenant_no
				AND 		svOut.pos_cd = sv.pos_cd
				AND 		svOut.sales_voucher_id = sv.sales_voucher_id
		
			JOIN (
					SELECT distinct	us.tenant_no, us.pos_cd, us.sales_voucher_id  

					FROM 	sales_voucher_uses us
					JOIN 	(
								SELECT 	tenant_no, pos_cd, sales_voucher_id , max(sales_voucher_use_id) maxId
								FROM 	sales_voucher_uses 
								WHERE 		tenant_no = @TENANT_NO 
									AND 	pos_cd = @POS_CD 
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &gt;= CAST (@DATE  AS DATE)   
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &lt;= CAST (@TODATE  AS DATE)  
								GROUP BY  tenant_no, pos_cd, sales_voucher_id
							) maxUse	
					on 		us.tenant_no  = maxUse.tenant_no 
						AND us.pos_cd =  maxUse.pos_cd 
						AND us.sales_voucher_id  = maxUse.sales_voucher_id 
						AND us.sales_voucher_use_id  = maxUse.maxId
					where us.voucher_state not in (2,3,4) 
		
				) svNotIn
				ON 			svNotIn.tenant_no = sv.tenant_no
				AND 		svNotIn.pos_cd = sv.pos_cd
				AND 		svNotIn.sales_voucher_id = sv.sales_voucher_id
		
	where 
			sv.tenant_no = @TENANT_NO
	AND		sv.pos_cd = @POS_CD
	
	AND sv.sales_voucher_cd NOT IN (
				SELECT evu.external_voucher_cd from external_voucher_uses evu
				join (	select tenant_no, external_voucher_cd , max(external_voucher_use_ts) maxTs
						FROM external_voucher_uses
		          		where 		tenant_no = @TENANT_NO 
							AND 	pos_cd != @POS_CD
							AND 	CAST (external_voucher_use_ts  AS DATE) &gt;= CAST (@DATE  AS DATE) 
							AND 	CAST (external_voucher_use_ts  AS DATE) &lt;= CAST (@TODATE  AS DATE)   
						GROUP BY 	tenant_no, external_voucher_cd	 
					 ) maxSalesVoucherUses
					ON 	evu.tenant_no = maxSalesVoucherUses.tenant_no
					AND evu.external_voucher_cd = maxSalesVoucherUses.external_voucher_cd
					AND evu.external_voucher_use_ts = maxSalesVoucherUses.maxTs
		
				where evu.tenant_no = @TENANT_NO
					AND 	evu.pos_cd != @POS_CD
					AND 	CAST (evu.external_voucher_use_ts  AS DATE) &gt;= CAST (@DATE  AS DATE)
					AND 	CAST (evu.external_voucher_use_ts  AS DATE) &lt;= CAST (@TODATE  AS DATE)    
					AND 	evu.voucher_state  = 3
			
	)
	
	
			
			
		</sql>
	</report-dataset>



</report>

