<DODocTemplateConfigRoot><DODocTemplateList><DODocTemplate STATE="3"><DODocTemplate2DeviceList/><DODocTemplateParamList><DODocTemplateParam STATE="0"><DocTemplateParamId>6588</DocTemplateParamId><DocTemplateId>7586</DocTemplateId><ParamName>$transactionid</ParamName><ParamType>1</ParamType><CntxPropertyType>22</CntxPropertyType><ParamValue></ParamValue><ParamCaption>$transactionid</ParamCaption></DODocTemplateParam><DODocTemplateParam STATE="0"><DocTemplateParamId>6589</DocTemplateParamId><DocTemplateId>7586</DocTemplateId><ParamName>saleid</ParamName><ParamType>3</ParamType><CntxPropertyType>23</CntxPropertyType><ParamValue></ParamValue><ParamCaption>saleid</ParamCaption></DODocTemplateParam></DODocTemplateParamList><DODocRepositoryList/><DODocTemplateParamValueList><DODocTemplateParamValue STATE="0"><DocTemplateParamValueId>6702</DocTemplateParamValueId><DocTemplateParamId>6588</DocTemplateParamId><DocTemplateId>7586</DocTemplateId><CntxPropertyType>0</CntxPropertyType><ParamValue></ParamValue><ParamCaption>$transactionid</ParamCaption><ReadOnly>1</ReadOnly><NotNull>0</NotNull></DODocTemplateParamValue><DODocTemplateParamValue STATE="0"><DocTemplateParamValueId>6703</DocTemplateParamValueId><DocTemplateParamId>6589</DocTemplateParamId><DocTemplateId>7586</DocTemplateId><CntxPropertyType>0</CntxPropertyType><ParamValue></ParamValue><ParamCaption>saleid</ParamCaption><ReadOnly>1</ReadOnly><NotNull>0</NotNull></DODocTemplateParamValue></DODocTemplateParamValueList><DocTemplateId>620</DocTemplateId><SystemId>0</SystemId><DocTemplateAk>9433B286AD2A4263B68210C3B925A5B7</DocTemplateAk><DocCntxType>2</DocCntxType><ReportDescription></ReportDescription><DocTemplateType>1</DocTemplateType><DirectPrint>0</DirectPrint><DocTemplateName>1Transaction Receipt</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>9433B286AD2A4263B68210C3B925A5B7</DocTemplateAk><DocTemplateContent>&lt;root namespaceuri="otn.overview7.xmldata"&gt;&lt;queries&gt;&lt;query name="reportxmldata" context="2"&gt;&lt;params&gt;&lt;param name="$transactionid" datatype="1" contextproperty="22"/&gt;&lt;param name="saleid" datatype="3" contextproperty="23"/&gt;&lt;/params&gt;&lt;/query&gt;&lt;/queries&gt;&lt;datasets&gt;&lt;dataset name="Transaction" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT 
	T.TransactionDate, 
	T.Serial AS TransactionNumber, 
	T.BoxId AS BoxNumber, 
	S.SiteCode, 
	OA.AreaCode, 
	W.WorkstationAK AS Station, 
	LK.Description AS TransactionType, 
	S.SiteName, 
	T.TransactionAK, 
	CNF_User.UserName, 
	CNF_User.UserCode, 
	T.Rounding 
FROM 
	DATA_Transaction T
    LEFT OUTER JOIN CNF_Site S
		ON S.SiteId = T.SiteId
    LEFT OUTER JOIN CNF_Workstation W
		ON W.WorkStationId = T.WorkStationId
    LEFT OUTER JOIN CNF_OperatingArea OA
		ON OA.OperatingAreaId = W.OperatingAreaId 
	LEFT OUTER JOIN CNF_LookUp LK
		ON LK.LookUpTableId = 3 -- Lookup_TransactionType 
		AND LK.ItemId = T.TransactionType 
	LEFT OUTER JOIN CNF_User 
		ON CNF_User.UserId = T.UserId 
WHERE 
	T.TransactionId in (/*$transactionid*/ 0 /*end*/)
ORDER BY 
	T.TransactionId&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Totals" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select  (SUM(Revenue - TotalTaxes) - (select sum(paymentfee)
      from data_Payment
      WHERE 
       TransactionId in (/*$transactionid*/ 0 /*end*/) 
                            and PaymentStatus = 1 )) AS SubTotal,
       sum(TotalTaxes ) as TotalTaxes,
    sum(TotalAmount) as TotalAmount,
    sum(Tendered) as Tendered,
    sum(Change) as Change,
    sum(BreakageTip) as BreakageTip,
    sum(PaymentFees) as PaymentFees,
    sum(TotalDiscount) as TotalDiscount
from(


SELECT 
 sum(T.Revenue) as Revenue, 
 sum(ISNULL(TotalTaxes, 0)) as TotalTaxes,
 SUM(T.Revenue) AS TotalAmount, 
 SUM(T.Revenue - T.Change + T.Rounding) AS Tendered, 
 SUM(T.Change) AS Change, 
 SUM(T.Tip) AS BreakageTip,
 (select sum(paymentfee)
    from data_Payment
   WHERE TransactionId in (/*$transactionid*/ 0 /*end*/) 
        and PaymentStatus = 1 ) as PaymentFees,
 isnull((SELECT sum(SIR.DiscountAmount)
   FROM data_transaction
    inner join data_transactionItem    
                on data_transactionitem.transactionid = 
                      data_transaction.transactionid
    inner join DATA_SaleItemRule SIR   
                on SIR.Saleitemid = data_transactionitem.saleitemid
 WHERE 
data_transaction.TransactionId in (/*$transactionid*/ 0 /*end*/)), 0) 
     as TotalDiscount         
FROM 
 DATA_Transaction T
WHERE 
 T.TransactionId in (/*$transactionid*/ 0 /*end*/)
 
 )r&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Item" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select matrixcellid,
saleitemid,
case when Accountid = Saleitemid then 0
     else Accountid
end Accountid,     
ProductType, 
ProductTypeName, 
AttributeNames, 
AttributeCodes, 
ProductDescription, 
ProductAdditionalDescription, 
ProductCode, 
ProductName,
Sum(QTY) as QTY,
UnitPrice,
Sum(PeopleCount) as PeopleCount,
sum(TotalAmount) as TotalAmount,
Saleid
from (


select distinct matrixcellid,
saleitemid,
Accountid,     
ProductType, 
ProductTypeName, 
AttributeNames, 
AttributeCodes, 
ProductDescription, 
ProductAdditionalDescription, 
ProductCode, 
ProductName,
QTY,
UnitPrice,
PeopleCount,
TotalAmount,
Saleid,
AcctROWNUMBER
from (

SELECT   
matrixcellid,
coalesce(SIRSaleItemId, sipSaleItemId,  case when SI2AAccountid 
is not null and AcctROWNUMBER = 1 then saleitemid
else  (select top 1 saleitemid
from data_saleitem
where data_saleitem.saleid = p2.saleid
and matrixcellid = p2.matrixcellid
)
end
) as saleitemid,
SI2AAccountid as Accountid,     
ProductType, 
ProductTypeName, 
AttributeNames, 
AttributeCodes, 
ProductDescription, 
ProductAdditionalDescription, 
ProductCode, 
ProductName,
QTY,
UnitPrice,
UnitTaxes,
PeopleCount,
TotalAmount,
Performances,
HasAPerformance,
Saleid,
case when SI2AAccountid = saleitemid then 0 else AcctROWNUMBER end AcctROWNUMBER
from(

select matrixcellid,
saleitemid,
si2aaccountid,
sirsaleitemid,
sipsaleitemid,
producttype,
producttypename,
AttributeNames,
AttributeCodes,
ProductDescription,
ProductAdditionalDescription,
ProductCode,
productName,
UnitPrice,
UnitTaxes,
PeopleCount,
TotalAmount,
Performances,
HasAPerformance,
Saleid,
case when si2aaccountid is null and sirsaleitemid is null 
and sipsaleitemid is null then  sum(Quantity)
     else case when sum(quantity) &amp;lt; 0 then Count(*) *-1 else Count(*) end
end QTY,
ROW_NUMBER () over(PARTITION BY si2aaccountid order by SaleItemId) 
as AcctROWNUMBER
from (

select 
SI.matrixcellid,   
SI.saleitemid,
ISNULL(SI2A.Accountid, SI.saleitemid) as SI2AAccountid, 
SIR.SaleItemId as SIRSaleItemId,
sip.SaleItemId as sipSaleItemId,
LkPtype.ItemId as ProductType, 
LkPtype.Description as ProductTypeName, 
MC.name as AttributeNames, 
MC.code as AttributeCodes, 
MC.Description as ProductDescription, 
MC.Description2 as ProductAdditionalDescription, 
CASE WHEN MC.Code is null THEN mc.description
ELSE MC.Code 
END ProductCode, 
CASE 
WHEN MC.Name is null THEN MC.Description 
ELSE MC.Name 
END ProductName,

SI.Quantity,
SI.UnitPrice,
SI.UnitTaxes,
SI.TotalTaxes,       
SI.PeopleCount,
SI.TotalAmount, 
 
(
SELECT 
count(distinct SI2P.PerformanceId) 
FROM 
DATA_SaleItem2Performance SI2P
WHERE 
SI2P.SaleItemId = SI.SaleItemId
and SI2p.Active = 1
) Performances,
(  
select count(*) as total
from
(
select distinct Performanceid, count(*) as total
FROM 
DATA_SaleItem2Performance SI2P
Inner join data_saleitem 
on data_saleitem.saleitemid = si2p.saleitemid
WHERE 
data_saleitem.saleid = SI.saleid
and SI2p.Active = 1
group by Performanceid
)t1) as HasAPerformance,              
sip.Performanceid,
si.saleid


FROM 
DATA_SaleItem SI 
inner join DATA_Sale S        
on SI.SaleId = S.SaleId
LEFT JOIN data_transactionitem TI on TI.saleitemid = si.saleitemid 
inner join CNF_LookUp LkPtype       
on LkPtype.LookUpTableId = 18 and LkPtype.ItemId = SI.ProductType 
inner join CNF_MatrixCell MC       
on MC.MatrixCellId = SI.MatrixCellId 
left outer join DATA_SaleItem2Performance sip   
on SI.SaleItemId =  sip.saleitemid and SIP.Active = 1
left outer join DATA_SaleItemRule SIR     
ON SI.SaleItemId = SIR.SaleItemId
left outer join DATA_SaleItem2Account SI2A     
ON SI.SaleItemId = SI2A.SaleItemId 
     
WHERE TransactionId in (/*$transactionid*/ 0 /*end*/)
and (SI.SaleId = :saleid)


)p1

group by   matrixcellid,
saleitemid,
si2aaccountid,
sirsaleitemid,
sipsaleitemid,
producttype,
producttypename,
AttributeNames,
AttributeCodes,
ProductDescription,
ProductAdditionalDescription,
ProductCode,
productName,
Quantity,
UnitPrice,
UnitTaxes,
TotalTaxes,
PeopleCount,
TotalAmount,
Performances,
HasAPerformance,
Saleid,
performanceid


)p2


)p3

)p4
group by matrixcellid,
saleitemid,
Accountid,     
ProductType, 
ProductTypeName, 
AttributeNames, 
AttributeCodes, 
ProductDescription, 
ProductAdditionalDescription, 
ProductCode, 
ProductName,
UnitPrice,
Saleid&lt;/db&gt;&lt;/select&gt;&lt;datasets&gt;&lt;dataset name="Performance" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT
	SI2P.SaleItemId, 
	P.PerfDateTime, 
	E.EventName, 
	L.Name as LocationName
FROM 
	DATA_SaleItem2Performance SI2P 
	left outer join CNF_Performance P 
		on SI2P.PerformanceId = P.PerformanceId 
	left outer join CNF_Event E 
		on P.EventId = E.EventId 
	left outer join ADM_AccessConfiguration AC 
		on P.AccessConfigurationId = AC.AccessConfigurationId 
	left outer join CNF_Location L 
		on AC.LocationId = L.LocationId 
WHERE 
	(SI2P.SaleItemId = :SaleItemId) 
 and SI2p.Active = 1
ORDER BY 
	P.PerfDateTime&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="MediaIdentifier" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT 
	S2T.SaleItemId, 
	MI.IdentifierCode 
FROM 
	DATA_SaleItem2Ticket S2T 
	inner join DATA_Ticket2Media T2M 
		on (T2M.TicketId = S2T.TicketId) 
	inner join DATA_MediaIdentifier MI 
		on (MI.MediaId = T2M.MediaId) 
WHERE 
	S2T.SaleItemId = :SaleItemId&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Promotion" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT
	SIR.SaleItemId,
	P.Code,
	P.Description,	 
	SIR.Perc,
	(-1*SIR.Forfait) AS Forfait,
	SIR.Price,
	ISNULL(SIR.DiscountAmount, 0) as DiscountAmount,
	SIR.PromotionItemRuleId
FROM 
	DATA_SaleItemRule SIR 
	inner join CNF_PromotionItemRule cpir WITH(NOLOCK) 
    on cpir.PromotionItemRuleId= SIR.PromotionItemRuleId 
	inner join CNF_PromotionItem cpi WITH(NOLOCK)  
    on cpi.promotionitemid = cpir.PromotionItemId 
	inner join CNF_Promotion p WITH(NOLOCK) on p.PromotionId = cpi.PromotionId
WHERE 
	SIR.SaleItemId = :SaleItemId&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Account" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT
	A.AccountAK, 
	A.DisplayName, 
	A.FirstName, 
	A.SurName, 
	A.MobilePhone
FROM 
	DATA_SaleItem2Account SI2A 
	LEFT OUTER JOIN DATA_Account A ON (A.AccountId = SI2A.AccountId)
WHERE
	SI2A.Used = 1
	and SI2A.AccountId = :AccountId&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Price" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT 
	SI.SaleItemId, 
	LkPtype.ItemId as ProductType, 
	LkPtype.Description as ProductTypeName, 
	ASet.AttributeNames, 
	ASet.AttributeCodes, 
	substring(MC.Description, 1, 29) + SPACE(29-len(substring(MC.Description, 1, 29))) as ProductDescription, 
	MC.Description2 as ProductAdditionalDescription, 
	CASE 
		WHEN MC.Code is null THEN ASet.AttributeCodes 
		ELSE MC.Code 
	END ProductCode, 
	CASE 
		WHEN MC.Name is null THEN ASet.AttributeNames 
		ELSE MC.Name 
	END ProductName, 
	si.Quantity AS Qty,
	SI.UnitPrice AS ItemPrice,
	SI.UnitPrice AS ItemAltPrice, 
	SI.Quantity * SI.UnitPrice AS ItemSubTotal, 
	SI.UnitTaxes AS Tax, 
	SI.Quantity * SI.UnitTaxes AS TaxSubTotal, 
	(
		SELECT 
			count(distinct SI2P.PerformanceId) 
		FROM 
			DATA_SaleItem2Performance SI2P
		WHERE 
			SI2P.SaleItemId = SI.SaleItemId
	) Performances, 
	SI.PeopleCount, 
	MC.Price, 
	case when mc.taxtype = 2 then SI.TotalAmount - si.totaltaxes
	     else SI.TotalAmount 
	  end totalamount
FROM 
	DATA_SaleItem SI 
	left outer join DATA_Sale S 
		on SI.SaleId = S.SaleId 
	left outer join CNF_LookUp LkPtype 
		on LkPtype.LookUpTableId = 18 /*Lookup_ProductType*/ and LkPtype.ItemId = SI.ProductType 
	left outer join CNF_MatrixCell MC 
		on MC.MatrixCellId = SI.MatrixCellId 
	left outer join CNF_AttributeSet ASet 
		on ASet.AttributeSetId = MC.AttributeSetId     
WHERE 
  (SI.SaleItemId = :SaleItemId )
  and si.Quantity &amp;gt;= 1
  and SI.TotalAmount &amp;gt; 0&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="TaxesItem" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select  distinct
p4.saleitemid,
cnf_Tax.taxcode,
taxname,
data_saleitemtax.taxamount,
Saleid
from (


select distinct matrixcellid,
saleitemid,
Accountid,     
ProductType, 
ProductTypeName, 
AttributeNames, 
AttributeCodes, 
ProductDescription, 
ProductAdditionalDescription, 
ProductCode, 
ProductName,
QTY,
UnitPrice,
UnitTaxes,
TotalTaxes,
PeopleCount,
TotalAmount,
Saleid,
AcctROWNUMBER
from (

SELECT   
matrixcellid,
coalesce(SIRSaleItemId, sipSaleItemId,  case when SI2AAccountid is not null 
and AcctROWNUMBER = 1 then saleitemid
else  (select top 1 saleitemid
from data_saleitem
where data_saleitem.saleid = p2.saleid
and matrixcellid = p2.matrixcellid
)
end
) as saleitemid,
SI2AAccountid as Accountid,     
ProductType, 
ProductTypeName, 
AttributeNames, 
AttributeCodes, 
ProductDescription, 
ProductAdditionalDescription, 
ProductCode, 
ProductName,
QTY,
UnitPrice,
UnitTaxes,
TotalTaxes,
PeopleCount,
TotalAmount,
Performances,
HasAPerformance,
Saleid,
case when SI2AAccountid = saleitemid then 0 else AcctROWNUMBER end AcctROWNUMBER
from(

select matrixcellid,
saleitemid,
si2aaccountid,
sirsaleitemid,
sipsaleitemid,
producttype,
producttypename,
AttributeNames,
AttributeCodes,
ProductDescription,
ProductAdditionalDescription,
ProductCode,
productName,
UnitPrice,
UnitTaxes,
TotalTaxes, 
PeopleCount,
TotalAmount,
Performances,
HasAPerformance,
Saleid,
case when ((si2aaccountid is null) or (saleitemid = si2aaccountid)) 
and sirsaleitemid is null and sipsaleitemid is null  then  sum(Quantity)
else Count(*)
end QTY,
ROW_NUMBER () over(PARTITION BY si2aaccountid order by SaleItemId) 
as AcctROWNUMBER
from (

select 
SI.matrixcellid,   
SI.saleitemid,
ISNULL(SI2A.Accountid, SI.saleitemid) as SI2AAccountid, 
SIR.SaleItemId as SIRSaleItemId,
sip.SaleItemId as sipSaleItemId,
LkPtype.ItemId as ProductType, 
LkPtype.Description as ProductTypeName, 
ASet.AttributeNames, 
ASet.AttributeCodes, 
MC.Description as ProductDescription, 
MC.Description2 as ProductAdditionalDescription, 
CASE 
WHEN MC.Code is null THEN ASet.AttributeCodes 
ELSE MC.Code 
END ProductCode, 
CASE 
WHEN MC.Name is null THEN ASet.AttributeNames 
ELSE MC.Name 
END ProductName,

SI.Quantity,
SI.UnitPrice,
SI.UnitTaxes,
SI.TotalTaxes,       
SI.PeopleCount,
SI.TotalAmount, 
 
(
SELECT 
count(distinct SI2P.PerformanceId) 
FROM 
DATA_SaleItem2Performance SI2P
WHERE 
SI2P.SaleItemId = SI.SaleItemId
and SI2p.Active = 1
) Performances,
(  
select count(*) as total
from
(
select distinct Performanceid, count(*) as total
FROM 
DATA_SaleItem2Performance SI2P
Inner join data_saleitem 
on data_saleitem.saleitemid = si2p.saleitemid
WHERE 
data_saleitem.saleid = SI.saleid
and SI2p.Active = 1
group by Performanceid
)t1) as HasAPerformance,              
sip.Performanceid,
si.saleid


FROM 
DATA_SaleItem SI 
left outer join DATA_Sale S        
on SI.SaleId = S.SaleId
LEFT JOIN data_transactionitem TI on TI.saleitemid = si.saleitemid 
left outer join CNF_LookUp LkPtype       
on LkPtype.LookUpTableId = 18 and LkPtype.ItemId = SI.ProductType 
left outer join CNF_MatrixCell MC       
on MC.MatrixCellId = SI.MatrixCellId 
left outer join CNF_AttributeSet ASet     
on ASet.AttributeSetId = MC.AttributeSetId 
left outer join DATA_SaleItem2Performance sip   
on SI.SaleItemId =  sip.saleitemid and SIP.Active = 1
left outer join DATA_SaleItemRule SIR     
ON SI.SaleItemId = SIR.SaleItemId
left outer join DATA_SaleItem2Account SI2A     
ON SI.SaleItemId = SI2A.SaleItemId 
     
WHERE TransactionId in (/*$transactionid*/ 0 /*end*/)
and (SI.SaleId = :saleid)


)p1

group by   matrixcellid,
saleitemid,
si2aaccountid,
sirsaleitemid,
sipsaleitemid,
producttype,
producttypename,
AttributeNames,
AttributeCodes,
ProductDescription,
ProductAdditionalDescription,
ProductCode,
productName,
Quantity,
UnitPrice,
UnitTaxes,
TotalTaxes,
PeopleCount,
TotalAmount,
Performances,
HasAPerformance,
Saleid,
performanceid


)p2


)p3

)p4
INNER JOIN data_saleitemtax on data_saleitemtax.saleitemid = p4.saleitemid
INNER JOIN cnf_Tax on cnf_Tax.Taxid = data_saleitemtax.taxid
INNER JOIN cnf_matrixcell on cnf_matrixcell.matrixcellid = p4.Matrixcellid

where (p4.Saleitemid = :SaleItemId)




--&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/dataset&gt;&lt;dataset name="Payment" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT  
	LkPType.Description as BOSPaymentType, 
	PDR.Description as PaymentTypeDesc, 
	PM.Description as PaymentMethod, 
	CUR.Symbol as Currency, 
	CUR.ISOCode, 
	sum(CASE CUR.CurrencyType -- main currency type is 1
		WHEN 1 THEN PAY.PaymentAmount
		ELSE PAY.CurrencyAmount
	END) Amount, 
	CASE PAY.PaymentType 
		WHEN 2 THEN PCARD.CardNumber 
		WHEN 6 THEN '[' + AccountCredit.AccountAK + '] ' + AccountCredit.DisplayName + ' #' + VOUCH.VoucherCode 
		ELSE ''  
	END PaymentDetails 
FROM 
	DATA_Payment PAY 
	inner join CNF_LookUp LkPType 
		on LkPType.LookupTableId = 7 and LkPType.ItemId = PAY.PaymentType 
	inner join CNF_PaymentMethod PM 
		on PAY.PaymentMethodId = PM.PaymentMethodId 
	inner join CNF_Driver PDR 
		on PDR.DriverId = PM.DriverId 
	left outer join CNF_Currency CUR 
		on PAY.CurrencyID = CUR.CurrencyId 
	left outer join DATA_PaymentCard PCARD 
		on PAY.PaymentId = PCARD.PaymentId 
	left outer join DATA_PaymentCredit PCRED 
		on PAY.PaymentId = PCRED.PaymentId 
	left outer join DATA_Account AccountCredit 
		on AccountCredit.AccountId = PCRED.AccountId 
	left outer join DATA_Voucher VOUCH 
		on PCRED.EntityTable = 186 and PCRED.EntityId = VOUCH.VoucherId  
WHERE 
	PAY.TransactionId in (/*$transactionid*/ 0 /*end*/) and 
	PAY.PaymentStatus = 1 

Group by LkPType.Description,
	PDR.Description, 
	PM.Description, 
	CUR.Symbol, 
	CUR.ISOCode,
	PAY.PaymentType,
	PCARD.CardNumber,
	AccountCredit.AccountAK,
	AccountCredit.DisplayName,
	VOUCH.VoucherCode

ORDER BY 
	LkPType.Description&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Reservation" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT  
	R.ReservationAK, 
	A.AccountAK, 
	A.DisplayName, 
	A.FirstName,
	A.SurName
FROM 
	DATA_Reservation R 
	left outer join DATA_Account A 
		on R.AccountId = A.AccountId 
WHERE 
	R.SaleId = :saleid&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="MoneyCardRecharge" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT 
	(TI.Quantity * TI.UnitRecharge) Amount, 
	CASE 
		WHEN MC.Code is null THEN ASet.AttributeCodes 
		ELSE MC.Code 
	END ProductCode, 
	CASE 
		WHEN MC.Name is null THEN ASet.AttributeNames 
		ELSE MC.Name 
	END ProductName, 
	ASET.AttributeCodes, 
	ASET.AttributeNames, 
	CASE 
		WHEN LkTrType.ItemId in (14, 16) THEN 'Money Card Recharge' 
		ELSE LkTrType.Description 
	END MoneyCardOperation 
FROM 
	DATA_TransactionItem TI 
	inner join DATA_Transaction TR 
		on (TI.TransactionId = TR.TransactionId) 
	inner join CNF_LookUp LkTrType 
		on (TR.TransactionType = LkTrType.ItemId and LkTrType.LookUpTableId = 3) 
	inner join DATA_SaleItem SI 
		on (TI.SaleItemId = SI.SaleItemId) 
	inner join CNF_MatrixCell MC 
		on (SI.MatrixCellId = MC.MatrixCellId) 
	inner join CNF_AttributeSet ASET 
		on ASET.AttributeSetId = MC.AttributeSetId 
WHERE 
	TI.TransactionId in (/*$transactionid*/ 0 /*end*/) 
	and TI.UnitRecharge &amp;lt;&amp;gt; 0 
ORDER BY 
	TI.TransactionId&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Sale" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT 
	SaleId, 
	LkType.Description SaleType, 
	LkStatus.Description SaleStatus, 
	FSaleDate, 
	Serial, 
	SaleAk, 
	Quantity, 
	TicketCount,  
	CNF_Site.SiteId, 
	CNF_Site.SiteName, 
	CNF_Site.SiteDescription, 
	CNF_Site.SiteAK, 
	SYST.SystemId, 
	SYST.SystemCode, 
	CUR.ISOCode, 
	CUR.Symbol Currency 
FROM 
	DATA_Sale S 
	left outer join CNF_LookUp LkType 
		on (S.SaleType = LkType.ItemId and LkType.LookUpTableId = 48) /* Lk_SaleType */ 
	left outer join CNF_LookUp LkStatus 
		on (S.StatusCode = LkStatus.ItemId and LkStatus.LookUpTableId = 39) /* Lk_SaleStatus */ 
	left outer join CNF_Workstation WS 
		on (S.WorkstationId = WS.WorkstationId) 
	left outer join CNF_Site 
		on (WS.SiteId = CNF_Site.SiteId) 
	left outer join CNF_System SYST 
		on (S.SystemId = SYST.SystemId) 
	left outer join CNF_Currency CUR 
		on S.CurrencyId = CUR.CurrencyId 
WHERE 
	S.SaleId = :saleid&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Coupon" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT
	P.Code CouponCode, 
	P.Description CouponName
FROM 
	CNF_Promotion P
	INNER JOIN DATA_SaleCoupon SC 
		ON P.PromotionId = SC.PromotionId 
	INNER JOIN DATA_Sale S 
		ON SC.SaleId = S.SaleId 
	INNER JOIN DATA_Transaction T 
		ON S.SaleId = T.SaleId 
WHERE 
	T.TransactionId in (/*$transactionid*/ 0 /*end*/)&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
	PA.PaymentDate,
    CUR.Symbol as Currency,  
	CUR.ISOCode, 
	PA.PaymentAmount
FROM 
	DATA_Transaction as TR 
	inner join DATA_Payment as PA 
		on (PA.TransactionId=TR.TransactionId) and PA.PaymentStatus = 1
	left outer join CNF_Currency as CUR 
		on (CUR.CurrencyId=PA.CurrencyId) 
WHERE 
	TR.SaleId = :saleid 
	and TR.TransactionType = 12&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="PaymentPlan" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT PP.PaymentPlanId
     , PP.PaymentMethodId
     , PP.PaymentNumber
     , PP.ScheduledDate
     , PP.NextAttemptDate
     , PP.PaymentDate
     , PP.AttemptCount
     , PP.Amount
     , PP.FlexPaymentStatus
     , PP.PaymentId
     , PP.PaymentActivityType
     , PP.PriorityOrder
     , PP.AppliedAmount
     , PP.FeeAmount
     , PP.RefundableFeeAmount
	 , CUR.Symbol as Currency 
	 , CUR.ISOCode 

FROM 
	DATA_Payment PAY 
	inner join DATA_PaymentPlan PP on PP.PaymentId = PAY.PaymentId    
	left outer join CNF_Currency CUR 
		on PAY.CurrencyID = CUR.CurrencyId 
WHERE 
	PAY.TransactionId in (/*$transactionid*/ 0 /*end*/) 
  and 
	PAY.PaymentStatus = 1 
  and
	PAY.PaymentType = 17 
ORDER BY 
	PP.PaymentNumber&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="PaymentPlanExists" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT 1
FROM 
	DATA_Payment PAY 
WHERE 
	PAY.TransactionId in (/*$transactionid*/ 0 /*end*/) 
  and 
	PAY.PaymentStatus = 1 
  and
	PAY.PaymentType = 17&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Discount" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT DATA_SaleItemRule.PromotionItemRuleID,
       UPPER(CNF_PromotionItem.Description) as Description,
       Cast(sum(DATA_SaleItemRule.DiscountAmount) as money) as TotalDiscount
 FROM dbo.DATA_SaleItemRule WITH (NOLOCK)
	INNER JOIN dbo.CNF_PromotionItemRule WITH (NOLOCK) ON CNF_PromotionItemRule.PromotionItemRuleId = DATA_SaleItemRule.PromotionItemRuleId
	INNER JOIN dbo.CNF_PromotionItem WITH (NOLOCK) ON CNF_PromotionItem.PromotionItemId = CNF_PromotionItemRule.PromotionItemId
	INNER JOIN dbo.DATA_SaleItem WITH (NOLOCK) ON DATA_SaleItem.SaleItemId = DATA_SaleItemRule.SaleItemId


WHERE 
	DATA_SaleItem.SaleId = :saleid

Group by DATA_SaleItemRule.PromotionItemRuleID, CNF_PromotionItem.Description&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Taxes" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select description, sum(taxAmount) as TaxAmount
from (

select	
CASE WHEN DATA_SaleItem.ProductTaxType = 1 then + CNF_Tax.TaxName   
		ELSE CNF_Tax.TaxName 
END as description, 
case when DATA_SaleItem.Quantity &amp;lt; 0 
then sum(DATA_SaleItemTax.TaxAmount * DATA_SaleItem.Quantity)*-1  
     else sum(DATA_SaleItemTax.TaxAmount * DATA_SaleItem.Quantity)
end as TaxAmount


from DATA_TransactionItem
INNER JOIN DATA_SaleItem on data_transactionitem.saleitemid = 
data_saleitem.saleitemid
INNER JOIN DATA_SaleItemTax on DATA_SaleItemTax.saleitemid = 
data_saleitem.saleitemid
Inner join CNF_Tax on CNF_Tax.TaxId = 
DATA_SaleItemTax.TaxId 


WHERE DATA_TransactionItem.TransactionId in (/*$transactionid*/ 0 /*end*/)


Group by DATA_SaleItem.ProductTaxType, 
         CNF_Tax.TaxName,
         DATA_SaleItem.Quantity


)p

group by description
Order by 2 desc&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/root&gt;
</DocTemplateContent><ArchiveFileName>9433B286AD2A4263B68210C3B925A5B7</ArchiveFileName></DocTemplateData></DODocTemplateDataList></DODocTemplateConfigRoot>