<DODocTemplateConfigRoot><DODocTemplateList><DODocTemplate STATE="0"><DODocTemplate2DeviceList/><DODocTemplateParamList><DODocTemplateParam STATE="0"><DocTemplateParamId>2038</DocTemplateParamId><DocTemplateId>224</DocTemplateId><ParamName>BoxId</ParamName><ParamType>3</ParamType><CntxPropertyType>100</CntxPropertyType><ParamValue></ParamValue><ParamCaption></ParamCaption></DODocTemplateParam></DODocTemplateParamList><DODocRepositoryList/><DODocTemplateParamValueList><DODocTemplateParamValue STATE="0"><DocTemplateParamValueId>2270</DocTemplateParamValueId><DocTemplateParamId>2038</DocTemplateParamId><DocTemplateId>224</DocTemplateId><CntxPropertyType>0</CntxPropertyType><ParamValue></ParamValue><ParamCaption></ParamCaption><ReadOnly>1</ReadOnly><NotNull>0</NotNull></DODocTemplateParamValue></DODocTemplateParamValueList><DocTemplateId>651</DocTemplateId><SystemId>0</SystemId><DocTemplateAk>C83CCD94AA914400A0E3798BB61E1E79</DocTemplateAk><DocCntxType>27</DocCntxType><ReportDescription></ReportDescription><DocTemplateType>1</DocTemplateType><DirectPrint>0</DirectPrint><DocTemplateName>Estimate Content</DocTemplateName><Enabled>1</Enabled><DocDatasourceId>0</DocDatasourceId><PriorityOrder>0</PriorityOrder><DmgCategoryId>0</DmgCategoryId><SystemTemplate>1</SystemTemplate><DeleteFromConfig></DeleteFromConfig><DocTemplateGroup></DocTemplateGroup></DODocTemplate></DODocTemplateList><DODocTemplateDataList><DocTemplateData STATE="0"><DocTemplateAk>C83CCD94AA914400A0E3798BB61E1E79</DocTemplateAk><DocTemplateContent>&lt;root NameSpaceUri="otn.overview7.xmldata"&gt;&lt;queries&gt;&lt;query name="reportxmldata" context="27"&gt;&lt;params&gt;&lt;param name="BoxId" datatype="3" contextproperty="100"/&gt;&lt;/params&gt;&lt;/query&gt;&lt;/queries&gt;&lt;datasets&gt;&lt;dataset name="MainData" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select 
  B.BoxAK,
  u.userid,
  U.UserCode,
  U.UserName, 
  WS.WorkstationCode,
  WS.WorkstationAK,
  WS.WorkstationDescription,
  OA.AreaCode,
  OA.OpAreaName    

from 
DATA_Box as B with(nolock) 
inner join CNF_User as U with(nolock)  on (B.UserId = U.UserId)
inner join CNF_Workstation as WS with(nolock)  on (B.WorkstationId = WS.WorkstationId)
inner join CNF_OperatingArea as OA with(nolock)  on (WS.OperatingAreaId = OA.OperatingAreaId)
where
  B.BoxId = :BoxId&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Data" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT
  PaymentType,
  EstimatedContent.CurrencyId,
  PaymentMethodId,
  CASE when PaymentType in (4, 5) then Description + ' ' + ISOCode
  	   else Description
  END AS PaymentMethodDesc,
  Sum(case when (PaymentType &amp;lt;&amp;gt; 1) then Amount else (Amount + Change) end ) AS Amount,
  Sum(case when (PaymentType &amp;lt;&amp;gt; 1) then CurrAmount else  (CurrAmount + Change) end ) as CurrAmount,
  Sum(Change)  AS CurrChange,
  ISOCode,
  Symbol,
  Format
  FROM (
    SELECT
      P.PaymentType,
      P.CurrencyId,
      P.PaymentMethodId,
      PM.Description,
      Sum(convert(money, P.PaymentAmount)) AS Amount,
      Sum(convert(money, P.CurrencyAmount)) AS CurrAmount,
 	  case when P.PaymentType = 1 then (select sum(change)
                                          from data_transaction 
                                         where data_transaction.boxid = t.Boxid)
	       else 0 
	   end AS Change
    FROM
      DATA_Payment as P with(nolock)
      inner join DATA_Transaction  as T  with(nolock) ON (P.TransactionId=T.TransactionId) 
      inner join CNF_PaymentMethod as PM with(nolock) ON (PM.PaymentMethodId=P.PaymentMethodId)
     WHERE
      T.BoxId= :BoxId
      and P.PaymentStatus = 1
     GROUP BY
       t.Boxid,
       P.PaymentType,
       P.CurrencyId,
       P.PaymentMethodId,
       PM.Description
     UNION
     SELECT
       DR.DriverSubType,
       DD.CurrencyId,
       PM.PaymentMethodId,
       PM.Description,
       Sum(convert(money, DD.Amount)) AS Amount,
       Sum(convert(money, DD.Amount)) As CurrAmount,
       0 AS Change
     FROM
       DATA_DepositDetail as DD 
       inner join DATA_Deposit as D ON DD.DepositId = D.DepositId 
       left join CNF_PaymentMethod as PM ON DD.PaymentMethodId = PM.PaymentMethodId 
       left join CNF_Driver as DR ON DR.DriverId = PM.DriverId
     WHERE
        D.BoxId = :BoxId
        and (D.AuditId IS NULL)
     GROUP BY
       DR.DriverSubType,
       DD.CurrencyId,
       PM.PaymentMethodId,
       PM.Description
    ) EstimatedContent
    INNER JOIN CNF_Currency ON EstimatedContent.CurrencyId = CNF_Currency.CurrencyId
  GROUP BY
    PaymentType,
    EstimatedContent.CurrencyId,
    PaymentMethodId,
    Description,
    ISOCode,
    Symbol,
	Format&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Deposits" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select ISNULL(InitialFund, 0) InitialFund,
       ISNULL(add_Lifts,  0) add_Lifts,
	   ISNULL(FinalClose,  0) FinalClose,
	   ISNULL(total, 0) total,
	   ISNULL(overshort, 0) overshort,
	   case when ISNULL(overshort, 0) &amp;lt; (ISNULL(InitialFund, 0) + ISNULL(totalTransCash, 0) + ISNULL(add_Lifts,  0)) 
                then ISNULL(overshort, 0)*-1
	        else ISNULL(overshort, 0)
		end overshort,       
	   ISNULL(totalTransCash, 0) totalTransCash,
       ISNULL(TotalCheck, 0) TotalCheck,
	   ISNULL(TotalTransOther, 0) TotalTransOther ,
       ISNULL(InitialFund, 0) + ISNULL(totalTransCash, 0) + ISNULL(TotalCheck, 0) + ISNULL(add_Lifts,  0) + ISNULL(TotalTransOther, 0) as Generated,
       ISNULL(ABS(Change), 0) as Change
from
(select  
   SUM(CASE WHEN DepositType IN (0, 1) THEN (D.DepositAmount)
            ELSE 0
		END
	) AS InitialFund,

	SUM(CASE WHEN DepositType IN (2, 3) THEN (D.DepositAmount)
            ELSE 0
		END
	) AS Add_Lifts,

	ABS(SUM(CASE WHEN DepositType IN (4) THEN (D.DepositAmount)
            ELSE 0
		END
	)) AS FinalClose,

	SUM(CASE WHEN DepositType IN (0, 1, 2, 3, 4) THEN (D.DepositAmount)
            ELSE 0
		END
	) AS Total,

	(Select Overshort *-1
	   from DATA_Box
	  where data_box.boxid = d.boxid) as overshort,
      
	(select sum(paymentamount) as TotalTransCash
		from data_transaction
			INNER JOIN data_payment   on data_payment.transactionid = data_transaction.transactionid  
            and data_payment.PaymentMethodId in (select paymentmethodid 
			                                       from cnf_paymentmethod
												   where driverid in (select driverid
												                        from cnf_driver
																		where driverak = 'TPayTpCash'
																	  )
												)
		where boxid = d.boxid) as TotalTransCash,
    
	(select sum(paymentamount) as TotalCheck
		from data_transaction
			INNER JOIN data_payment   on data_payment.transactionid = data_transaction.transactionid              
            and data_payment.PaymentMethodId in (select paymentmethodid 
			                                       from cnf_paymentmethod
												   where driverid in (select driverid
												                        from cnf_driver
																		where driverak in ('TPayTpCheque'
                                                                                          , 'TPayTpTravellerCheque')
																	  )
												)
		where boxid = d.boxid) as TotalCheck,
		
	(select sum(paymentamount) as TotalTransOther
		from data_transaction
			INNER JOIN data_payment   on data_payment.transactionid = data_transaction.transactionid              
            and data_payment.PaymentMethodId in (select paymentmethodid 
			                                       from cnf_paymentmethod
												   where driverid in (select driverid
												                        from cnf_driver
																		where driverak NOT in ('TPayTpCash',
                                                                                          'TPayTpCheque',
                                                                                          'TPayTpTravellerCheque')
																	  )
												)
		where boxid = d.boxid
        and data_payment.paymentstatus = 1) as TotalTransOther,
	(Select sum(Change)
	   from data_transaction T
	   where T.Boxid = d.BoxID) as Change 	       
         	       

from DATA_Deposit D

where D.BOXID = :BoxId
and D.Deposittype in (0, 1, 2, 3, 4)
group by boxid

)p&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/root&gt;
</DocTemplateContent><ArchiveFileName>C83CCD94AA914400A0E3798BB61E1E79</ArchiveFileName></DocTemplateData></DODocTemplateDataList></DODocTemplateConfigRoot>