<DODocTemplateConfigRoot><DODocTemplateList><DODocTemplate STATE="0"><DODocTemplate2DeviceList/><DODocTemplateParamList><DODocTemplateParam STATE="0"><DocTemplateParamId>9360</DocTemplateParamId><DocTemplateId>6826</DocTemplateId><ParamName>$transactionid</ParamName><ParamType>1</ParamType><CntxPropertyType>22</CntxPropertyType><ParamValue></ParamValue><ParamCaption>$transactionid</ParamCaption></DODocTemplateParam><DODocTemplateParam STATE="0"><DocTemplateParamId>9361</DocTemplateParamId><DocTemplateId>6826</DocTemplateId><ParamName>saleid</ParamName><ParamType>3</ParamType><CntxPropertyType>23</CntxPropertyType><ParamValue></ParamValue><ParamCaption>saleid</ParamCaption></DODocTemplateParam></DODocTemplateParamList><DODocRepositoryList/><DODocTemplateParamValueList><DODocTemplateParamValue STATE="0"><DocTemplateParamValueId>9598</DocTemplateParamValueId><DocTemplateParamId>9360</DocTemplateParamId><DocTemplateId>6826</DocTemplateId><CntxPropertyType>0</CntxPropertyType><ParamValue></ParamValue><ParamCaption>$transactionid</ParamCaption><ReadOnly>1</ReadOnly><NotNull>0</NotNull></DODocTemplateParamValue><DODocTemplateParamValue STATE="0"><DocTemplateParamValueId>9599</DocTemplateParamValueId><DocTemplateParamId>9361</DocTemplateParamId><DocTemplateId>6826</DocTemplateId><CntxPropertyType>0</CntxPropertyType><ParamValue></ParamValue><ParamCaption>saleid</ParamCaption><ReadOnly>1</ReadOnly><NotNull>0</NotNull></DODocTemplateParamValue></DODocTemplateParamValueList><DocTemplateId>635</DocTemplateId><SystemId>0</SystemId><DocTemplateAk>3651DAA67494448C8751CF52FE3EA286</DocTemplateAk><DocCntxType>2</DocCntxType><ReportDescription></ReportDescription><DocTemplateType>1</DocTemplateType><DirectPrint>0</DirectPrint><DocTemplateName>Copy of Transaction Tax Included 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="0"><DocTemplateAk>3651DAA67494448C8751CF52FE3EA286</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,
    T.TransactionID 
FROM 
	DATA_Transaction T WITH(NOLOCK)
    LEFT OUTER JOIN CNF_Site S WITH(NOLOCK)
		ON S.SiteId = T.SiteId
    LEFT OUTER JOIN CNF_Workstation W WITH(NOLOCK)
		ON W.WorkStationId = T.WorkStationId
    LEFT OUTER JOIN CNF_OperatingArea OA WITH(NOLOCK)
		ON OA.OperatingAreaId = W.OperatingAreaId 
	LEFT OUTER JOIN CNF_LookUp LK WITH(NOLOCK)
		ON LK.LookUpTableId = 3 -- Lookup_TransactionType 
		AND LK.ItemId = T.TransactionType 
	LEFT OUTER JOIN CNF_User WITH(NOLOCK) 
		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(TotalAmount) - sum(TotalTaxes)) AS SubTotal,
       sum(TotalTaxes ) as TotalTaxes,
    sum(TotalAmount) + sum(PaymentFees + Installmentfees) as TotalAmount,
    sum(Tend) as Tendered,
    sum(Change) as Change,
    sum(BreakageTip) as BreakageTip,
    sum(PaymentFees + Installmentfees) as PaymentFees,
    sum(TotalDiscount) as TotalDiscount,
    sum(Donations) as Donations,
    ISNULL((SELECT sum(DL.EntryAmount) as Refund_Due

FROM DATA_LEDGER DL WITH(NOLOCK)
     INNER JOIN CNF_LedgerAccount WITH(NOLOCK) ON CNF_LedgerAccount.ledgerAccountid = dl.ledgerAccountId
	 INNER JOIN data_transaction WITH(NOLOCK) on data_transaction.transactionid = dl.transactionid
	 INNER JOIN data_sale WITH(NOLOCK)        on data_sale.saleid = data_transaction.saleid
	 LEFT  JOIN data_reservation WITH(NOLOCK) on data_reservation.saleid = data_transaction.saleid
	 LEFT  JOIN data_account WITH(NOLOCK)     on data_account.accountid = data_reservation.accountid
	 LEFT JOIN (select Rownum, 
					   transactionid,
					   Description,
					   fPaymentDate,
					   PaymentAmount
				from
				(

				select ROW_NUMBER() over (Partition by data_Payment.transactionid ORDER BY data_Payment.PaymentId desc
							   ) as ROWNum,
					   data_Payment.transactionid,
					   CNF_PaymentMethod.Description, 
					   data_payment.FPaymentDate, 
					   (select sum(paymentAmount)
						   from data_payment dp WITH(NOLOCK)
						 where dp.transactionid = data_payment.transactionid) as PaymentAmount

				from data_Payment WITH(NOLOCK)
					 INNER JOIN data_transaction WITH(NOLOCK) on data_transaction.transactionid = data_Payment.transactionid
					 INNER JOIN CNF_PaymentMethod WITH(NOLOCK) ON CNF_PaymentMethod.PaymentMethodId = data_payment.PaymentMethodId  and CNF_PaymentMethod.CheckOverpayment = 1
				)p
				where rowNum = 1)r on r.transactionid = dl.transactionid

where systemAccountid = 15
  and systemaccounttable = 119
  and DL.Transactionid in (/*$transactionid*/ 0 /*end*/)), 0) as CheckOverPayment
from(


SELECT 
 sum(T.Revenue) as Revenue, 
 sum(ISNULL(TotalTaxes, 0)) as TotalTaxes,
 SUM(T.TotalAmount) AS TotalAmount, 
 SUM(T.Revenue - T.Change + T.Rounding) AS Tendered, 
 SUM(T.Change) AS Change, 
 SUM(T.Tip) AS BreakageTip,
 ISNULL((select sum(paymentfee)
    from data_Payment WITH(NOLOCK)
   WHERE TransactionId in (/*$transactionid*/ 0 /*end*/) 
        and PaymentStatus = 1 ), 0) as PaymentFees,
        
        
 ISNULL((select 
       sum(data_paymentflex.downpaymentfeeamount) 
from data_transaction WITH(NOLOCK)
	INNER JOIN data_payment WITH(NOLOCK)				ON data_payment.transactionid = data_transaction.transactionid
	INNER JOIN data_paymentflex WITH(NOLOCK)			ON data_paymentflex.paymentid = data_payment.paymentid


WHERE data_transaction.TransactionId in (/*$transactionid*/ 0 /*end*/) ), 0) Installmentfees, 


       
 isnull((SELECT sum(SIR.DiscountAmount)
   FROM data_transaction WITH(NOLOCK)
    inner join data_transactionItem WITH(NOLOCK)    
                on data_transactionitem.transactionid = 
                      data_transaction.transactionid
    inner join DATA_SaleItemRule SIR WITH(NOLOCK)   
                on SIR.Saleitemid = data_transactionitem.saleitemid
 WHERE 
data_transaction.TransactionId in (/*$transactionid*/ 0 /*end*/)), 0) 
     as TotalDiscount,
           		 ISNULL((select sum(donationamount)
			from data_Payment WITH(NOLOCK)
		   WHERE TransactionId in (/*$transactionid*/ 0 /*end*/) 
				and PaymentStatus = 1 ), 0) as Donations,
            
(select sum(paymentamount)
   from data_payment WITH(NOLOCK)
   where data_payment.transactionid in (/*$transactionid*/ 0 /*end*/) and PaymentStatus = 1) as Tend            
                         
FROM 
 DATA_Transaction T WITH(NOLOCK)
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, 
producttaxtype,
ProductTypeName, 
AttributeNames, 
AttributeCodes, 
substring(ProductDescription, 0, 40) as ProductDescription, 
ProductAdditionalDescription, 
ProductCode, 
ProductName,
Sum(QTY) as QTY,
UnitPrice as unitprice,
unitrawprice,
sum(UnitPrice)*Sum(QTY)  as netprice,
Sum(PeopleCount) as PeopleCount,
sum(unitprice) * sum(Qty) as TotalAmount,
Saleid
from (


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

SELECT   
matrixcellid,
saleitemid,
SI2AAccountid as Accountid,     
ProductType,
producttaxtype, 
ProductTypeName, 
AttributeNames, 
AttributeCodes, 
ProductDescription, 
ProductAdditionalDescription, 
ProductCode, 
ProductName,
QTY,
UnitPrice,
unitrawprice,
UnitTaxes,
PeopleCount,
TotalAmount,
Performances,
HasAPerformance,
Saleid,
case when SI2AAccountid = saleitemid then 0 else AcctROWNUMBER end AcctROWNUMBER
from(

select matrixcellid,
saleitemid,
si2aaccountid,
sirsaleitemid,
sipsaleitemid,
producttype,
producttaxtype,
producttypename,
AttributeNames,
AttributeCodes,
ProductDescription,
ProductAdditionalDescription,
ProductCode,
productName,
UnitPrice,
unitrawprice,
UnitTaxes,
PeopleCount,
TotalAmount,
Performances,
HasAPerformance,
Saleid,
case when ((si2aaccountid is null) or (saleitemid = si2aaccountid)) and 
             (sirsaleitemid is null or (saleitemid = sirsaleitemid))and 
             (sipsaleitemid is null  or (saleitemid = si2aaccountid))  
             then  sum(Quantity)
     else quantity
end QTY,

ROW_NUMBER () over(PARTITION BY si2aaccountid order by SaleItemId) 
as AcctROWNUMBER
from (

select 
SI.matrixcellid,   
SI.saleitemid,
ISNULL(SI.saleitemid, 0) 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,
producttaxtype,
SI.Quantity,
SI.UnitPrice,
SI.unitrawprice,
SI.UnitTaxes,
SI.TotalTaxes,       
SI.PeopleCount,
SI.TotalAmount, 
 
(
SELECT 
count(distinct SI2P.PerformanceId) 
FROM 
DATA_SaleItem2Performance SI2P WITH(NOLOCK)
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 WITH(NOLOCK)
Inner join data_saleitem  WITH(NOLOCK)
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,
tr.transactiontype


FROM 
DATA_SaleItem SI  WITH(NOLOCK)
inner join DATA_Sale S WITH(NOLOCK)        
on SI.SaleId = S.SaleId
LEFT JOIN data_transactionitem TI WITH(NOLOCK) on TI.saleitemid = si.saleitemid 
LEFT JOIN data_transaction TR WITH(NOLOCK) on tr.transactionid = ti.transactionid

inner join CNF_LookUp LkPtype WITH(NOLOCK)       
on LkPtype.LookUpTableId = 18 and LkPtype.ItemId = SI.ProductType 
inner join CNF_MatrixCell MC  WITH(NOLOCK)      
on MC.MatrixCellId = SI.MatrixCellId 
left outer join DATA_SaleItem2Performance sip  WITH(NOLOCK) 
on SI.SaleItemId =  sip.saleitemid and SIP.Active = 1

and sip.SaleItem2PerformanceId = (select max(saleitem2PerformanceID) 
from data_saleitem2Performance SIP2  WITH(NOLOCK) where SIP2.saleitemid = si.saleitemid)

left outer join DATA_SaleItemRule SIR			
ON SI.SaleItemId = SIR.SaleItemId and SIR.SaleItemRuleId =
(select max(saleitemruleid)
   from data_saleitemrule WITH(NOLOCK)
  where data_saleitemrule.saleitemid = SI.saleitemid)
     
WHERE TI.TransactionId in (/*$transactionid*/ 0 /*end*/)
AND (SI.Saleitemid not in (select data_saleitemrule.saleitemid 
from data_saleitemrule WITH(NOLOCK)
INNER JOIN data_saleitem WITH(NOLOCK) on data_saleitem.saleitemid = data_saleitemrule.saleitemid
where data_saleitemrule.Promotiontype in (300, 301)
and data_saleitem.saleid in (select saleid from data_transaction WITH(NOLOCK)
where transactionid in  (/*$transactionid*/ 0 /*end*/)



)
))
and SI.productType &amp;lt;&amp;gt; 7

)p1

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


)p2


)p3

)p4
group by matrixcellid,
saleitemid,
Accountid,     
ProductType,
producttaxtype, 
ProductTypeName, 
AttributeNames, 
AttributeCodes, 
ProductDescription, 
ProductAdditionalDescription, 
ProductCode, 
ProductName,
UnitPrice,
Saleid,
UnitTaxes,
unitrawprice&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, 
	substring(isnull(p.Performancename, E.EventName), 0, 20) as EventName,  
	L.Name as LocationName
FROM 
	DATA_SaleItem2Performance SI2P  WITH(NOLOCK)
	left outer join CNF_Performance P WITH(NOLOCK) 
		on SI2P.PerformanceId = P.PerformanceId 
	left outer join CNF_Event E  WITH(NOLOCK)
		on P.EventId = E.EventId 
	left outer join ADM_AccessConfiguration AC  WITH(NOLOCK)
		on P.AccessConfigurationId = AC.AccessConfigurationId 
	left outer join CNF_Location L  WITH(NOLOCK)
		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  WITH(NOLOCK)
	inner join DATA_Ticket2Media T2M   WITH(NOLOCK)
		on (T2M.TicketId = S2T.TicketId) 
	inner join DATA_MediaIdentifier MI  WITH(NOLOCK)
		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   WITH(NOLOCK)
	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
  and p.PromotionType not in (3, 6, 7)&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  WITH(NOLOCK)
	LEFT OUTER JOIN DATA_Account A WITH(NOLOCK) 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 WITH(NOLOCK)
		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 WITH(NOLOCK) 
	left outer join DATA_Sale S  WITH(NOLOCK)
		on SI.SaleId = S.SaleId 
	left outer join CNF_LookUp LkPtype WITH(NOLOCK) 
		on LkPtype.LookUpTableId = 18 /*Lookup_ProductType*/ and LkPtype.ItemId = SI.ProductType 
	left outer join CNF_MatrixCell MC  WITH(NOLOCK)
		on MC.MatrixCellId = SI.MatrixCellId 
	left outer join CNF_AttributeSet ASet  WITH(NOLOCK)
		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 SI.saleitemid,
cnf_Tax.taxcode,
substring(taxname, 0, 20) taxname,
ABS(data_saleitemtax.taxamount) as taxamount


FROM DATA_SaleItem SI WITH(NOLOCK)
left outer join DATA_Sale S WITH(NOLOCK)       on SI.SaleId = S.SaleId
LEFT JOIN data_transactionitem TI WITH(NOLOCK) on TI.saleitemid = si.saleitemid 
INNER JOIN data_saleitemtax WITH(NOLOCK) 
on data_saleitemtax.saleitemid = SI.saleitemid
INNER JOIN cnf_Tax WITH(NOLOCK)		on cnf_Tax.Taxid = data_saleitemtax.taxid   
							
WHERE TransactionId in (/*$transactionid*/ 0 /*end*/)
  and (SI.SaleId = :saleid)		
  and (SI.Saleitemid = :SaleItemId)
  and data_saleitemtax.taxamount &amp;lt;&amp;gt; 0&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  Pay.paymentid,
	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,
    AuthorizationCode 
FROM 
	DATA_Payment PAY  with(nolock)
	inner join CNF_LookUp LkPType  with(nolock)
		on LkPType.LookupTableId = 7 and LkPType.ItemId = PAY.PaymentType 
	inner join CNF_PaymentMethod PM  with(nolock)
		on PAY.PaymentMethodId = PM.PaymentMethodId 
	inner join CNF_Driver PDR      with(nolock)
		on PDR.DriverId = PM.DriverId 
	left outer join CNF_Currency CUR  with(nolock)
		on PAY.CurrencyID = CUR.CurrencyId 
	left outer join DATA_PaymentCard PCARD  with(nolock)
		on PAY.PaymentId = PCARD.PaymentId 
	left outer join DATA_PaymentCredit PCRED with(nolock) 
		on PAY.PaymentId = PCRED.PaymentId 
	left outer join DATA_Account AccountCredit with(nolock) 
		on AccountCredit.AccountId = PCRED.AccountId 
	left outer join DATA_Voucher VOUCH     with(nolock)
		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,
    Pay.paymentid,
    AuthorizationCode
ORDER BY 
	LkPType.Description&lt;/db&gt;&lt;/select&gt;&lt;datasets&gt;&lt;dataset name="Hotel" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select  HotelTransactionId,
  'Room Number ' + RoomNumber as RoomNumber,
  GuestLastName,
 'Reservation # ' +  HotelReservationId as ReservationId

from DATA_PaymentHotel WITH(NOLOCK)
inner join DATA_Payment WITH(NOLOCK) 
on data_payment.PaymentId = data_paymenthotel.paymentid

 WHERE  RoomNumber is not null
  and GuestLastName is not null
  and HotelReservationId is not null
  and DATA_Payment.Paymentid = :PaymentID&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&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     WITH(NOLOCK)
	left outer join DATA_Account A   WITH(NOLOCK)
		on R.AccountId = A.AccountId 
    left join data_transaction  WITH(NOLOCK)
     on data_transaction.saleid = R.Saleid
    
WHERE 
	data_transaction.transactionid in (/*$transactionid*/ 0 /*end*/)&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 WITH(NOLOCK) 
	inner join DATA_Transaction TR  WITH(NOLOCK)
		on (TI.TransactionId = TR.TransactionId) 
	inner join CNF_LookUp LkTrType  WITH(NOLOCK)
		on (TR.TransactionType = LkTrType.ItemId and LkTrType.LookUpTableId = 3) 
	inner join DATA_SaleItem SI  WITH(NOLOCK)
		on (TI.SaleItemId = SI.SaleItemId) 
	inner join CNF_MatrixCell MC WITH(NOLOCK) 
		on (SI.MatrixCellId = MC.MatrixCellId) 
	inner join CNF_AttributeSet ASET WITH(NOLOCK) 
		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 
	s.SaleId, 
	LkType.Description SaleType, 
	LkStatus.Description SaleStatus, 
	s.FSaleDate, 
	s.Serial, 
	s.SaleAk, 
	s.Quantity, 
	s.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 WITH(NOLOCK)
	left outer join CNF_LookUp LkType  WITH(NOLOCK)
		on (S.SaleType = LkType.ItemId and LkType.LookUpTableId = 48) /* Lk_SaleType */ 
	left outer join CNF_LookUp LkStatus WITH(NOLOCK)
		on (S.StatusCode = LkStatus.ItemId and LkStatus.LookUpTableId = 39) /* Lk_SaleStatus */ 
	left outer join CNF_Workstation WS WITH(NOLOCK)
		on (S.WorkstationId = WS.WorkstationId) 
	left outer join CNF_Site    WITH(NOLOCK)
		on (WS.SiteId = CNF_Site.SiteId) 
	left outer join CNF_System SYST   WITH(NOLOCK)
		on (S.SystemId = SYST.SystemId) 
	left outer join CNF_Currency CUR  WITH(NOLOCK)
		on S.CurrencyId = CUR.CurrencyId
    left outer join data_transaction   WITH(NOLOCK)
      on data_transaction.saleid = s.saleid 
WHERE 
	data_transaction.transactionid in (/*$transactionid*/ 0 /*end*/)&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 WITH(NOLOCK)
	INNER JOIN DATA_SaleCoupon SC WITH(NOLOCK) 
		ON P.PromotionId = SC.PromotionId 
	INNER JOIN DATA_Sale S WITH(NOLOCK) 
		ON SC.SaleId = S.SaleId 
	INNER JOIN DATA_Transaction T WITH(NOLOCK) 
		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 WITH(NOLOCK) 
	inner join DATA_Payment as PA WITH(NOLOCK) 
		on (PA.TransactionId=TR.TransactionId) and PA.PaymentStatus = 1
	left outer join CNF_Currency as CUR WITH(NOLOCK) 
		on (CUR.CurrencyId=PA.CurrencyId) 
WHERE 
	TR.TransactionId in (/*$transactionid*/ 0 /*end*/)
	and TR.TransactionType in (12, 15)&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 WITH(NOLOCK) 
	inner join DATA_PaymentPlan PP WITH(NOLOCK) on PP.PaymentId = PAY.PaymentId    
	left outer join CNF_Currency CUR WITH(NOLOCK) 
		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 WITH(NOLOCK) 
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="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT
	sum((SIR.DiscountAmount)) AS Forfait,
	CUR.ISOCode, 
	CUR.Symbol Currency,
    p.Description
 
FROM DATA_SAleitem WITH(NOLOCK) 
INNER JOIN DATA_SaleItemRule SIR WITH(NOLOCK) 
	ON SIR.saleitemid = data_saleitem.saleitemid
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
INNER JOIN DATA_Sale S WITH(NOLOCK)
	ON s.Saleid = data_saleitem.saleid
left outer join CNF_Currency CUR WITH(NOLOCK) 
		on S.CurrencyId = CUR.CurrencyId 

WHERE 
	DATA_SAleitem.SaleId in (select saleid
              from data_transaction  WITH(NOLOCK)
              where data_transaction.transactionid 
              in (/*$transactionid*/ 0 /*end*/)    )
 and DATA_SAleitem.producttype &amp;lt;&amp;gt; 6
group by CUR.ISOCode, 
	     CUR.Symbol,
         p.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 substring(description, 0, 30) 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 WITH(NOLOCK)
INNER JOIN DATA_SaleItem WITH(NOLOCK) on data_transactionitem.saleitemid = 
data_saleitem.saleitemid
INNER JOIN DATA_SaleItemTax WITH(NOLOCK) on DATA_SaleItemTax.saleitemid = 
data_saleitem.saleitemid
Inner join CNF_Tax WITH(NOLOCK) 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 substring(description, 0, 30)

Order by 2 desc&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="PerformanceChange" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;SELECT
DATA_SaleItem2Performance.SaleItemId, 
p.PerfDateTime, 
E.EventName, 
L.Name as LocationName

from data_transaction WITH(NOLOCK)
INNER JOIN DATA_Transaction2Ticket WITH(NOLOCK)			
on data_transaction2ticket.transactionid = data_transaction.transactionid
INNER JOIN DATA_Ticket WITH(NOLOCK)						
on DATA_Ticket.ticketid = data_transaction2ticket.ticketid
INNER JOIN DATA_SaleItem2Performance WITH(NOLOCK)		
on data_saleitem2Performance.Saleitemid = data_ticket.saleitemid and active = 1
INNER JOIN CNF_Performance P WITH(NOLOCK)				
on p.performanceid = data_saleitem2Performance.Performanceid
left outer join CNF_Event E	 WITH(NOLOCK)				
on P.EventId = E.EventId 
left outer join ADM_AccessConfiguration AC WITH(NOLOCK)  
on P.AccessConfigurationId = AC.AccessConfigurationId 
left outer join CNF_Location L WITH(NOLOCK)				
on AC.LocationId = L.LocationId 


where data_transaction.transactiontype = 41
and data_transaction.TransactionId in (/*$transactionid*/ 0 /*end*/)&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="SiteInformation" donotgeneratelist="S"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select *
from vwSiteinformation
where vwSiteinformation.siteid in (
select top 1 siteid
from data_sale ds WITH(NOLOCK)
	INNER JOIN DATA_Transaction WITH(NOLOCK)
        on data_transaction.saleid = ds.saleid
where DATA_Transaction.transactionid in (/*$transactionid*/ 0 /*end*/)
)&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="Package" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select saleid,
       description,
	   case when hideItemPrice = 1 then sum(totalprice)
	        else null
	    end  as totalprice,
	   groupingcode


from (
SELECT DATA_SaleCoupon.SaleId,   
	   COALESCE(data_saleItemRule.Description, CNF_Promotion.Description) 
       as description,  
	   case when CNF_Promotion.hideItemPrice = 1 
       then (unitprice - unittaxes) * quantity
	        else 0
		end as totalprice,
	   data_saleItem.GroupingCode,
	   data_saleitem.ProductTaxType,
	   DATA_SaleItemRule.hideItemPrice,
	   Quantity
	   
FROM DATA_SaleCoupon with(nolock)  
	inner join data_saleItem with(nolock)							
    on data_saleItem.SaleItemId = DATA_SaleCoupon.SaleItemID  
	inner join DATA_SaleItemRule with (nolock)			
    on DATA_SaleItemRule.SaleItemId = DATA_SaleItem.SaleItemID  
	left join data_coupon with (nolock)					
    on data_coupon.couponid = DATA_SaleCoupon.couponid  
	inner join CNF_Promotion with (nolock)				
    on CNF_Promotion.PromotionId = DATA_SaleCoupon.PromotionId 
	   
where  DATA_SaleCoupon.SaleID in 
(select saleid from data_transaction WITH(NOLOCK)
  where data_transaction.transactionid in (/*$transactionid*/ 0 /*end*/)) 
	   
)p

group by saleid,
         description,
		 groupingcode,
		 hideItemPrice
       
order by  groupingcode&lt;/db&gt;&lt;/select&gt;&lt;datasets&gt;&lt;dataset name="PackageItem" 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, 
producttaxtype, 
producttypename, 
attributenames, 
attributecodes, 
Substring(productdescription, 0, 40) AS ProductDescription, 
productadditionaldescription, 
productcode, 
productname, 
Sum(qty)                             AS QTY, 
case when HideItemPrice &amp;lt;&amp;gt; 1 then cast(unitprice as varchar(10))
else '--'
END AS unitprice, 
Sum(peoplecount) AS PeopleCount,
case when HideItemPrice &amp;lt;&amp;gt; 1 then cast((unitprice)*Sum(qty) as varchar(10)) 
else '--'
END AS netprice, 

case when HideItemPrice &amp;lt;&amp;gt; 1 then cast(Sum(unitprice) * Sum(qty)  as varchar(10))
else '--'
END AS TotalAmount, 

saleid,
HideItemPrice  
FROM   (SELECT DISTINCT matrixcellid, 
saleitemid, 
accountid, 
producttype, 
producttaxtype, 
producttypename, 
attributenames, 
attributecodes, 
productdescription, 
productadditionaldescription, 
productcode, 
productname, 
qty, 
unitprice, 
unittaxes, 
peoplecount, 
totalamount, 
saleid, 
acctrownumber,
Position,
Transactiontype,
HideItemPrice
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  WITH(NOLOCK)
WHERE  data_saleitem.saleid = p2.saleid 
AND matrixcellid = p2.matrixcellid) 
END) AS saleitemid 
, 
si2aaccountid 
AS Accountid, 
producttype, 
producttaxtype, 
producttypename, 
attributenames, 
attributecodes, 
productdescription, 
productadditionaldescription, 
productcode, 
productname, 
qty, 
unitprice, 
unittaxes, 
peoplecount, 
totalamount, 
performances, 
hasaperformance, 
saleid, 
Position,
Transactiontype,
HideItemPrice,
CASE 
WHEN si2aaccountid = saleitemid THEN 0 
ELSE acctrownumber 
END                                         AcctROWNUMBER 
FROM  (SELECT matrixcellid, 
saleitemid, 
si2aaccountid, 
sirsaleitemid, 
sipsaleitemid, 
producttype, 
producttaxtype, 
producttypename, 
attributenames, 
attributecodes, 
productdescription, 
productadditionaldescription, 
productcode, 
productname, 
unitprice, 
unittaxes, 
peoplecount, 
totalamount, 
performances, 
hasaperformance, 
saleid, 
CASE 
WHEN ( ( si2aaccountid IS NULL ) 
OR ( saleitemid = si2aaccountid ) ) 
AND ( sirsaleitemid IS NULL 
OR ( saleitemid = sirsaleitemid ) ) 
AND ( sipsaleitemid IS NULL 
OR ( saleitemid = si2aaccountid ) ) 
THEN 
Sum( 
quantity) 
ELSE Count(*) 
END                      QTY, 
Position,
Transactiontype,
HideItemPrice,
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 
, 
producttaxtype, 
SI.quantity, 
SI.unitprice, 
SI.unittaxes, 
SI.totaltaxes, 
SI.peoplecount, 
SI.totalamount, 
(SELECT Count(DISTINCT SI2P.performanceid) 
FROM   data_saleitem2performance SI2P  WITH(NOLOCK) 
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   WITH(NOLOCK)
INNER JOIN data_saleitem  WITH(NOLOCK) 
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,
si.position,
(select transactiontype
from data_transaction  WITH(NOLOCK)
where transactionid = ti.transactionid) as transactiontype,
data_saleitemrule.HideItemPrice
FROM   data_saleitem SI  WITH(NOLOCK) 
INNER JOIN data_sale S   WITH(NOLOCK)
ON SI.saleid = S.saleid 
LEFT JOIN data_transactionitem TI   WITH(NOLOCK)
ON TI.saleitemid = si.saleitemid 
INNER JOIN cnf_lookup LkPtype   WITH(NOLOCK)
ON LkPtype.lookuptableid = 18 
AND LkPtype.itemid = 
SI.producttype 
INNER JOIN cnf_matrixcell MC  WITH(NOLOCK) 
ON MC.matrixcellid = 
SI.matrixcellid 
LEFT OUTER JOIN data_saleitem2performance 
sip   WITH(NOLOCK)
ON SI.saleitemid = 
sip.saleitemid 
AND SIP.active = 1 
LEFT OUTER JOIN data_saleitemrule SIR  WITH(NOLOCK) 
ON SI.saleitemid = 
SIR.saleitemid 
LEFT OUTER JOIN data_saleitem2account SI2A  WITH(NOLOCK) 
ON SI.saleitemid = 
SI2A.saleitemid
INNER JOIN data_saleitemrule  WITH(NOLOCK) 
on data_saleitemrule.saleitemid = SI.saleitemid 
WHERE  transactionid IN (/*$transactionid*/ 0 /*end*/) 
AND ( SI.saleid = :saleid )
and data_saleitemrule.Promotiontype in (300, 301)
and si.groupingcode = :groupingcode

)p1 
GROUP  BY matrixcellid, 
saleitemid, 
si2aaccountid, 
sirsaleitemid, 
sipsaleitemid, 
producttype, 
producttaxtype, 
producttypename, 
attributenames, 
attributecodes, 
productdescription, 
productadditionaldescription, 
productcode, 
productname, 
quantity, 
unitprice, 
unittaxes, 
totaltaxes, 
peoplecount, 
totalamount, 
performances, 
hasaperformance,
Position,
Transactiontype, 
saleid, 
performanceid,
HideItemPrice)p2)p3)p4 
GROUP  BY matrixcellid, 
saleitemid, 
accountid, 
producttype, 
producttaxtype, 
producttypename, 
attributenames, 
attributecodes, 
productdescription, 
productadditionaldescription, 
productcode, 
productname, 
unitprice, 
saleid, 
unittaxes,
Position,
Transactiontype,
HideItemPrice&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/dataset&gt;&lt;dataset name="Fees" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select Code,
	   ISNULL(Sum(paymentfee), 0) as Paymentfee

from
(

select CNF_paymentMethod.code + ' Payment Fee' as Code,
	   data_payment.paymentfee
from data_transaction with(nolock)
	INNER JOIN DATA_Payment 	with(nolock)	on data_payment.transactionid = data_transaction.transactionid
	INNER JOIN CNF_paymentMethod with(nolock)		on cnf_paymentmethod.paymentmethodid = data_payment.paymentmethodid

where data_payment.paymentfee &amp;lt;&amp;gt; 0
  and data_transaction.transactionid in (/*$transactionid*/ 0 /*end*/) 
   and DATA_Payment.PaymentStatus = 1

  union all

  select SIR.description,
	   sum(DiscountAmount) as DiscountAmount

FROM 
DATA_SaleItem SI WITH(NOLOCK) 
LEFT JOIN data_transactionitem TI WITH(NOLOCK) on TI.saleitemid = si.saleitemid 
LEFT JOIN data_transaction TR WITH(NOLOCK) on tr.transactionid = ti.transactionid

left outer join DATA_SaleItemRule SIR WITH(NOLOCK)			
ON SI.SaleItemId = SIR.SaleItemId and SIR.SaleItemRuleId =
(select max(saleitemruleid)
   from data_saleitemrule WITH(NOLOCK)
  where data_saleitemrule.saleitemid = SI.saleitemid)

     
WHERE TI.TransactionId in (/*$transactionid*/ 0 /*end*/) 
and si.producttype = 6

group by SIR.description,
       SIR.Code


UNION ALL

select 'Installment Fees',
       sum(data_paymentflex.downpaymentfeeamount) 
from data_transaction WITH(NOLOCK)
	INNER JOIN data_payment WITH(NOLOCK)				ON data_payment.transactionid = data_transaction.transactionid
	INNER JOIN data_paymentflex WITH(NOLOCK)			ON data_paymentflex.paymentid = data_payment.paymentid


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

)p


group by Code&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;dataset name="DonationTickets" donotgeneratelist="N"&gt;&lt;select&gt;&lt;db query="reportxmldata" type="*" commadtype="query"&gt;select mc.Matrixcellid,
       mc.Code,
	   mc.name,
	   mc.Description,
	   SI.TotalAmount

FROM 
DATA_SaleItem SI WITH(NOLOCK) 
inner join DATA_Sale S WITH(NOLOCK)        
on SI.SaleId = S.SaleId
LEFT JOIN data_transactionitem TI WITH(NOLOCK) on TI.saleitemid = si.saleitemid 
LEFT JOIN data_transaction TR WITH(NOLOCK) on tr.transactionid = ti.transactionid

inner join CNF_LookUp LkPtype WITH(NOLOCK)       
on LkPtype.LookUpTableId = 18 and LkPtype.ItemId = SI.ProductType 
inner join CNF_MatrixCell MC WITH(NOLOCK)       
on MC.MatrixCellId = SI.MatrixCellId 
left outer join DATA_SaleItem2Performance sip WITH(NOLOCK)   
on SI.SaleItemId =  sip.saleitemid and SIP.Active = 1

and sip.SaleItem2PerformanceId = (select max(saleitem2PerformanceID) 
from data_saleitem2Performance SIP2 WITH(NOLOCK) where SIP2.saleitemid = si.saleitemid)

left outer join DATA_SaleItemRule SIR WITH(NOLOCK)			
ON SI.SaleItemId = SIR.SaleItemId and SIR.SaleItemRuleId =
(select max(saleitemruleid)
   from data_saleitemrule WITH(NOLOCK)
  where data_saleitemrule.saleitemid = SI.saleitemid)
     
WHERE TI.TransactionId in (/*$transactionid*/ 0 /*end*/)
AND (SI.Saleitemid not in (select data_saleitemrule.saleitemid 
							from data_saleitemrule WITH(NOLOCK)
							INNER JOIN data_saleitem WITH(NOLOCK) on data_saleitem.saleitemid = data_saleitemrule.saleitemid
							where data_saleitemrule.Promotiontype in (300, 301)
							and data_saleitem.saleid in (select saleid from data_transaction WITH(NOLOCK)
															where transactionid in  (/*$transactionid*/ 0 /*end*/)

														)
))
and SI.productType = 7&lt;/db&gt;&lt;/select&gt;&lt;datasets/&gt;&lt;/dataset&gt;&lt;/datasets&gt;&lt;/root&gt;
</DocTemplateContent><ArchiveFileName>3651DAA67494448C8751CF52FE3EA286</ArchiveFileName></DocTemplateData></DODocTemplateDataList></DODocTemplateConfigRoot>