<DODocTemplateConfigRoot><DODocTemplateList><DODocTemplate STATE="3"><DODocTemplate2DeviceList/><DODocTemplateParamList><DODocTemplateParam STATE="0"><DocTemplateParamId>406</DocTemplateParamId><DocTemplateId>209</DocTemplateId><ParamName>depositid</ParamName><ParamType>3</ParamType><CntxPropertyType>91</CntxPropertyType><ParamValue></ParamValue><ParamCaption>depositid</ParamCaption></DODocTemplateParam></DODocTemplateParamList><DODocRepositoryList/><DODocTemplateParamValueList><DODocTemplateParamValue STATE="0"><DocTemplateParamValueId>431</DocTemplateParamValueId><DocTemplateParamId>406</DocTemplateParamId><DocTemplateId>209</DocTemplateId><CntxPropertyType>0</CntxPropertyType><ParamValue></ParamValue><ParamCaption>depositid</ParamCaption><ReadOnly>1</ReadOnly><NotNull>0</NotNull></DODocTemplateParamValue></DODocTemplateParamValueList><DocTemplateId>511</DocTemplateId><SystemId>0</SystemId><DocTemplateAk>32BB78EDB3AD4FA8B3D8319997685C69</DocTemplateAk><DocCntxType>9</DocCntxType><ReportDescription></ReportDescription><DocTemplateType>1</DocTemplateType><DirectPrint>0</DirectPrint><DocTemplateName>Box Deposit</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="1"><DocTemplateAk>32BB78EDB3AD4FA8B3D8319997685C69</DocTemplateAk><DocTemplateContent>&lt;root namespaceuri="otn.overview7.xmldata"&gt;&lt;queries&gt;&lt;query name="reportxmldata" context="9"&gt;&lt;params&gt;&lt;param name="depositid" datatype="3" contextproperty="91"/&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 
  D.DepositAmount,
  D.DepositDate,
  D.BagNumber,
  Lk.Description as DepositType,
  B.BoxAK,
  u.userid,
  U.UserCode,
  U.UserName, 
  WS.WorkstationCode,
  WS.WorkstationAK,
  WS.WorkstationDescription,
  OA.AreaCode,
  OA.OpAreaName,  

   (D.DepositAmount)  as "DepAmount",

  (select sum(DD.Amount) as "Amount"
							from DATA_DepositDetail DD
							left join CNF_PaymentMethod PM on (DD.PaymentMethodId = PM.PaymentMethodId)
							left join CNF_LookUp Fund on (Fund.LookUpTableId = 71 and Fund.ItemId = DD.DetailCode)

							where DD.DepositId = :depositid
							  and DD.DetailCode &amp;gt; 0) 
    as CashAmount,
	(select 
		  sum(DD.Amount*-1) as "Amount"

		from DATA_DepositDetail DD
			left join CNF_PaymentMethod PM on (DD.PaymentMethodId = PM.PaymentMethodId)

		where DD.DepositId = :depositid
		  and DD.DetailCode = 0
		  and driverid in (9, 10)) ChecksAmount,
	(select 
		  sum(DD.Amount*-1) as "Amount"

		from DATA_DepositDetail DD
			left join CNF_PaymentMethod PM on (DD.PaymentMethodId = PM.PaymentMethodId)

		where DD.DepositId = :depositid
		  and DD.DetailCode = 0
		  and driverid NOT IN (9, 10)) NONCASHAmount         

from DATA_Deposit D

left join CNF_LookUp Lk on (Lk.LookUpTableId = 64 and D.DepositType = Lk.ItemId)
inner join DATA_Box B on (D.BoxId = B.BoxId)
inner join CNF_User U on (D.UserId = U.UserId)
inner join CNF_Workstation WS on (D.WorkstationId = WS.WorkstationId)
inner join CNF_OperatingArea OA on (WS.OperatingAreaId = OA.OperatingAreaId)

where D.DepositId = :depositid&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Deposit" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select 
  DD.PaymentMethodId,
  DD.DetailCode,

  case 
    when DD.DetailCode &amp;gt; 0 then upper(max(Fund.Description))
    else upper(max(PM.Description))
  end as Description,

  (sum(-1*DD.Amount)) as "Amount"  

from DATA_DepositDetail DD
left join CNF_PaymentMethod PM on (DD.PaymentMethodId = PM.PaymentMethodId)
left join CNF_LookUp Fund on (Fund.LookUpTableId = 71 and Fund.ItemId = DD.DetailCode)

where DD.DepositId = :depositid

group by DD.CurrencyId, DD.PaymentMethodId, DD.DetailCode&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="CashDeposit" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select 
  DD.CurrencyId,
  DD.PaymentMethodId,
  DD.DetailCode,  
  case 
    when DD.DetailCode &amp;gt; 0 then upper(max(Fund.Description))+ ':'
    else upper(max(PM.Description))+ ':'
  end as Description,

  sum(DD.Amount*-1) as "Amount"

from DATA_DepositDetail DD
left join CNF_PaymentMethod PM on (DD.PaymentMethodId = PM.PaymentMethodId)
left join CNF_LookUp Fund on (Fund.LookUpTableId = 71 and Fund.ItemId = DD.DetailCode)

where DD.DepositId = :depositid
  and DD.DetailCode &amp;gt; 0

group by DD.CurrencyId, DD.PaymentMethodId, DD.DetailCode&lt;/db&gt;&lt;/select&gt;&lt;datasets&gt;&lt;dataset name="CashItem" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select 
  C.Symbol as Currency,
  ABS(CD.Amount) as SinglePrice,
  CD.FundCategory as DetailCode,
  case 
    when DD.Quantity is null then 0
    else DD.Quantity 
  end as Quantity,
  case 
    when (DD.Amount) is null then 0
    else (-1*DD.Amount)
  end as Amount,
  DD.PaymentMethodId

from CNF_Currency C 
inner join CNF_CurrencyDetail CD on (C.CurrencyId = CD.CurrencyId)
left join DATA_DepositDetail DD on (
          DD.DepositId = :depositid and
          DD.PaymentMethodId = :PaymentMethodId and
          DD.CurrencyId = C.CurrencyId and 
          DD.DetailCode = CD.FundCategory and 
          ABS(CD.Amount) = ABS(DD.SinglePrice)
)

where 
      C.CurrencyId = :CurrencyId      
  and CD.FundCategory = :DetailCode

order by CD.FundCategory, ABS(CD.Amount)&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/dataset&gt;&lt;dataset name="NoCashDeposit" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select 
  DD.CurrencyId,
  DD.PaymentMethodId,
  DD.DetailCode,
  case 
    when DD.DetailCode &amp;gt; 0 then Substring(upper(max(Fund.Description)), 0 , 21) +':'
    else Substring(upper(max(PM.Description)), 0 , 21) +':'
  end as Description,

  (sum(-1*DD.Amount)) as "Amount"

from DATA_DepositDetail DD
left join CNF_PaymentMethod PM on (DD.PaymentMethodId = PM.PaymentMethodId)
left join CNF_LookUp Fund on (Fund.LookUpTableId = 71 and Fund.ItemId = DD.DetailCode)

where DD.DepositId = :depositid
  and DD.DetailCode = 0
  and driverid NOT in (9, 10)
  
group by DD.CurrencyId, DD.PaymentMethodId, DD.DetailCode&lt;/db&gt;&lt;/select&gt;&lt;datasets&gt;&lt;dataset name="NoCashItem" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select 
  C.Symbol as Currency,
  DD.SinglePrice,
  DD.DetailCode,
  DD.Quantity,
  (-1*DD.Amount)Amount,
  DD.PaymentMethodId
from DATA_DepositDetail DD
inner join CNF_Currency C on (DD.CurrencyId = C.CurrencyId)
where 
     DD.DepositId = :depositid
 and DD.PaymentMethodId = :PaymentMethodId
 and DD.DetailCode = :DetailCode
order by ABS(SinglePrice)&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="CreditCardItem" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT 
  pp.boxid,
  PP.PaymentDesc,
  PP.Description,
  PP.PaymentMethodId,
  strvalue,
  SUM(PP.RealTotal)  as TOTAL_PAYMENT
FROM 
  (
     SELECT
	    data_transaction.WorkstationId,
		data_transaction.OperatingAreaId,
		data_transaction.siteid,
	    data_transaction.Boxid, 
        CNF_PaymentMethod.Code,
	    CNF_PaymentMethod.Description as PaymentDesc, 
	    CNF_PaymentCard.CCDescription as Description, 
       CNF_PaymentMethod.PaymentMethodId ,
       SUM(DATA_Payment.PaymentAmount)  as RealTotal
     FROM 
      DATA_Payment WITH(NOLOCK) 
	    INNER JOIN DATA_Transaction on DATA_Transaction.transactionid = data_payment.transactionid
        INNER JOIN CNF_PaymentMethod WITH(NOLOCK)   ON (CNF_PaymentMethod.PaymentMethodId = DATA_Payment.PaymentMethodId)
	    LEFT OUTER JOIN DATA_PaymentCard WITH(NOLOCK)    ON (DATA_Payment.PaymentId = DATA_PaymentCard.PaymentId)
        LEFT OUTER JOIN CNF_PaymentCard WITH(NOLOCK)     ON (DATA_PaymentCard.PaymentCardId = CNF_PaymentCard.PaymentCardId)
		LEFT join  DATA_Deposit on DATA_Deposit.boxid = data_transaction.boxid and DATA_Deposit.workstationid = DATA_Transaction.workstationid
    where CNF_PaymentCard.CCCode is not null
	  AND DATA_Payment.PAymentStatus in (1)
	  and DATA_Deposit.depositid = :depositid
	  and data_payment.paymentmethodid = :PaymentMethodId
    GROUP BY 
	  data_transaction.Boxid,
      CNF_PaymentMethod.PaymentMethodId,
      CNF_PaymentMethod.Code,
      CNF_PaymentMethod.Description,
	    CNF_PaymentCard.CCDescription,
	    data_transaction.WorkstationId,
		data_transaction.OperatingAreaId,
		data_transaction.siteid		 
  ) AS PP
       LEFT JOIN (select top 1 Case when strValue = 1 THEN 1
							  else 0
						  end as strvalue,
						  sourceid,
						  destid
						from data_right
						where rightid = (select rightid
										   from cnf_right
										  where rightak = 'AutoFillPayments')
						order by desttype desc)x  
	                                               on x.Sourceid = pp.PaymentMethodId  and destid in (pp.Workstationid, pp.OperatingAreaId, pp.siteid)
where ((strValue is null) OR (strValue = 1))
GROUP BY 
  PP.Boxid,
  PP.PaymentMethodId,
  PP.PaymentDesc,
  PP.Description,
  strvalue
ORDER BY PaymentDesc, Description, PaymentMethodId&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/dataset&gt;&lt;dataset name="VoidedTcks" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT          
  -- VOID
  SUM(Mast.TCK_VOIDED_COUNT) AS TCK_VOIDED_COUNT,
  SUM(Mast.TCK_VOIDED_AMOUNT) AS TCK_VOIDED_AMOUNT,

  SUM(Mast.PARTIAL_REFUNDS) AS PARTIAL_REFUNDS,

  SUM(Mast.TOTAL_REFUNDS) AS TOTAL_REFUNDS,
  
  -- MONEY REFUND
  SUM(Mast.MONEY_REFUND_AMOUNT) AS MONEY_REFUND_AMOUNT,

  SUM(Mast.NONRevenue) as NONRevenue
FROM
(
select 

  -- VOID
  Abs(SUM((CASE WHEN (Tr.TransactionType in (22)) THEN (SI.PeopleCount * SI.Quantity)ELSE 0 END))) +
  Abs(SUM((CASE WHEN (Tr.TransactionType in (5)) THEN (data_transactionitem.Quantity)ELSE 0 END))) AS TCK_VOIDED_COUNT,
  Abs(SUM((CASE WHEN (Tr.TransactionType in (22)) THEN (SI.TotalAmount) ELSE 0 END))) +  
  Abs(SUM((CASE WHEN (Tr.TransactionType in (5)) THEN (data_transactionitem.TotalAmount) ELSE 0 END)))
  
  AS TCK_VOIDED_AMOUNT,

  ABS((select sum(ff.Revenue)
                          FROM DATA_Transaction ff
						    inner join DATA_Box B on (FF.BoxId = B.BoxId)
							INNER JOIN DATA_Deposit d ON (d.BOXid = B.bOXID)
                        where D.DepositId = :depositid  
						and transactiontype in (10, 12))) as NONRevenue,
  Abs(SUM((CASE WHEN (Tr.TransactionType in (18)) THEN (SI.TotalAmount) ELSE 0 END))) AS PARTIAL_REFUNDS,
  
  Abs(SUM((CASE WHEN (Tr.TransactionType in (20)) THEN (SI.TotalAmount) ELSE 0 END))) AS TOTAL_REFUNDS,

  
  -- MONEY REFUND
  Abs(SUM((CASE WHEN (Tr.TransactionType in (13)) THEN (Tr.Revenue) ELSE 0 END))) AS MONEY_REFUND_AMOUNT
 
          
from 
  DATA_Transaction as Tr WITH(NOLOCK)
  inner join DATA_Sale as SA WITH(NOLOCK)         ON (SA.SaleId=Tr.SaleId)
  left join DATA_SaleItem as SI WITH(NOLOCK)      ON (SA.SaleId=Si.SaleId)
  left join data_transactionitem                  ON data_transactionitem.TransactionId = tr.TransactionId and data_transactionitem.Saleitemid = si.Saleitemid  
   
      
 WHERE 
   Tr.Completed = 1 and
   SA.Encoded = 1 and
   Tr.transactionid in (select ff.Transactionid
                          FROM DATA_Transaction ff
						    inner join DATA_Box B on (FF.BoxId = B.BoxId)
							INNER JOIN DATA_Deposit d ON (d.BOXid = B.bOXID)
                        where D.DepositId = :depositid                      
         )
                        
 
)  as Mast&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 InitialFund,
       add_Lifts,
	   finalclose,
	   total,
	   overshort,
	   totalTransCash,
	   InitialFund + TotalTransCash + Add_Lifts as Generated

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,

	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) + sum(change) as totalCash
		from data_transaction
			INNER JOIN data_payment   on data_payment.transactionid = data_transaction.transactionid  and data_payment.PaymentMethodId = 1
		where boxid = d.boxid) as TotalTransCash       

from DATA_Deposit D

where D.BOXID IN
		(select boxid
		   from DATA_Deposit
		   where depositid = :depositid
		)
and D.Deposittype in (0, 1, 2, 3, 4)
group by boxid

)p&lt;/db&gt;&lt;/select&gt;&lt;datasets&gt;&lt;dataset name="DepositItem" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select 
ROW_NUMBER()
  over (ORDER BY DepositDate) as Sequence,
  RIGHT(d.BagNumber, 6) as bagid,
  convert(varchar, DepositDate, 108) as DepositTime,
   cnf_lookup.Description as DepositType,
    ISNULL(D.BagNumber, 0 ) as BagNumber,
   case when d.deposittype = 4 then D.DepositAmount*-1
      else D.DepositAmount
    end as DepAmount,

   d.deposittype,
    D.DepositAmount

from DATA_Deposit D

left join  CNF_LookUp Lk on (Lk.LookUpTableId = 64 and D.DepositType = Lk.ItemId)
inner join DATA_Box B on (D.BoxId = B.BoxId)
inner join CNF_User U on (D.UserId = U.UserId)
inner join CNF_Workstation WS on (D.WorkstationId = WS.WorkstationId)
inner join CNF_OperatingArea OA on (WS.OperatingAreaId = OA.OperatingAreaId)
inner join cnf_lookup           on (d.deposittype = cnf_lookup.itemid and cnf_lookup.LookUpTableId = 64)

where D.BOXID IN
		(select boxid
		   from DATA_Deposit
		   where depositid = :depositid 
		)
and D.Deposittype in (0, 1, 2, 3, 4)&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/dataset&gt;&lt;dataset name="ChecksDeposit" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select 
  DD.CurrencyId,
  DD.PaymentMethodId,
  DD.DetailCode,  
  PM.Description + ':' as Description,
  DD.DetailCode,
  sum(DD.Amount*-1) as "Amount"

from DATA_DepositDetail DD
    left join CNF_PaymentMethod PM on (DD.PaymentMethodId = PM.PaymentMethodId)

where DD.DepositId = :depositid
  and DD.DetailCode = 0
  and driverid in (9, 10)

group by DD.CurrencyId, DD.PaymentMethodId, DD.DetailCode, PM.Description&lt;/db&gt;&lt;/select&gt;&lt;datasets&gt;&lt;dataset name="CheckItem" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select CNF_PaymentMethod.Description,
  C.Symbol as Currency,
  DD.SinglePrice,
  DD.DetailCode,
  DD.Quantity,
  (-1*DD.Amount)Amount,
  DD.PaymentMethodId
from DATA_DepositDetail DD
inner join CNF_Currency C on (DD.CurrencyId = C.CurrencyId)
INNER JOIN CNF_PaymentMethod on CNF_PaymentMethod.PaymentMethodId = dd.PaymentMethodId
where 
     DD.DepositId = :depositid
 and DD.PaymentMethodId = :PaymentMethodId
 and DD.DetailCode = :DetailCode
order by ABS(SinglePrice)&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/root&gt;
</DocTemplateContent><ArchiveFileName>32BB78EDB3AD4FA8B3D8319997685C69</ArchiveFileName></DocTemplateData></DODocTemplateDataList></DODocTemplateConfigRoot>