USP_DATAFORMTEMPLATE_VIEW_RECONCILIATIONDETAILS
The load procedure used by the view dataform template "Reconciliation Details View Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @USERID | nvarchar(50) | INOUT | User name |
| @RECONCILIATIONDATE | datetime | INOUT | Reconcile date |
| @DEPOSITDETAILS | xml | INOUT | DEPOSITDETAILS |
| @OTHERDETAILS | xml | INOUT | OTHERDETAILS |
| @TOTALDEPOSITAMOUNT | money | INOUT | Total deposit amount |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RECONCILIATIONDETAILS
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@USERID nvarchar(50) = null output,
@RECONCILIATIONDATE datetime = null output,
@DEPOSITDETAILS xml = null output,
@OTHERDETAILS xml = null output,
@TOTALDEPOSITAMOUNT money = null output
)
as
set nocount on;
set @DATALOADED = 0;
select
@DATALOADED = 1,
@USERID = dbo.UFN_APPUSER_GETNAME([APPUSER].[ID]),
@RECONCILIATIONDATE = [RECONCILIATION].[RECONCILIATIONDATE]
from dbo.[RECONCILIATION]
inner join dbo.[APPUSER] on [RECONCILIATION].[APPUSERID] = [APPUSER].[ID]
where [RECONCILIATION].[ID] = @ID;
declare @RECONCILIATIONDETAILS xml = null
select @RECONCILIATIONDETAILS = (
select
[PAYMENTTYPES].[PAYMENTMETHODCODE],
[PAYMENTTYPES].[PAYMENTMETHOD],
[PAYMENTTYPES].[DEPOSIT],
[PAYMENTTYPES].[EXPECTED],
[PAYMENTTYPES].[OTHERPAYMENTMETHODCODEID]
from (
select
isnull(EXPECTED.PAYMENTMETHODCODE, REFUND.PAYMENTMETHODCODE) as PAYMENTMETHODCODE,
case isnull(EXPECTED.PAYMENTMETHODCODE, REFUND.PAYMENTMETHODCODE)
when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(isnull(EXPECTED.CREDITTYPECODEID, REFUND.CREDITTYPECODEID))
when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(isnull(EXPECTED.OTHERPAYMENTMETHODCODEID, REFUND.OTHERPAYMENTMETHODCODEID))
else dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(isnull(EXPECTED.PAYMENTMETHODCODE, REFUND.PAYMENTMETHODCODE))
end as PAYMENTMETHOD,
isnull([EXPECTED].[EXPECTEDAMOUNT], 0) - isnull([REFUND].[REFUNDAMOUNT],0) [DEPOSIT],
isnull([EXPECTED].[EXPECTEDCOUNT], 0) as [EXPECTED],
isnull([EXPECTED].[OTHERPAYMENTMETHODCODEID], [REFUND].[OTHERPAYMENTMETHODCODEID]) as [OTHERPAYMENTMETHODCODEID]
from
(
-- expected count and amount
-- from payment tables
select
[REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] as [PAYMENTMETHODCODE],
[CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
[OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID],
count(SALESORDERPAYMENT.ID)as [EXPECTEDCOUNT],
sum([SALESORDERPAYMENT].[AMOUNT]) as [EXPECTEDAMOUNT]
from (select ID, SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT from dbo.SALESORDERPAYMENT where [DONOTRECONCILE] = 0
union all
select ID, RESERVATIONID as SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
) as SALESORDERPAYMENT
inner join dbo.[REVENUE]
on [SALESORDERPAYMENT].[PAYMENTID] = [REVENUE].[ID]
inner join dbo.[REVENUEPAYMENTMETHOD]
on [REVENUEPAYMENTMETHOD].[REVENUEID] = [REVENUE].[ID]
left join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
left join dbo.[OTHERPAYMENTMETHODDETAIL] on [OTHERPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
where
REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (1,2,10) and
[SALESORDERPAYMENT].[RECONCILIATIONID] = @ID
group by
[REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
[CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
[OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID]
union all
select
0 as [PAYMENTMETHODCODE],
null as [CREDITTYPECODEID],
null as [OTHERPAYMENTMETHODCODEID],
(
select count(SALESORDERPAYMENT.ID)as [EXPECTEDCOUNT]
from dbo.[SALESORDERPAYMENT]
inner join dbo.[REVENUE]
on [SALESORDERPAYMENT].[PAYMENTID] = [REVENUE].[ID]
inner join dbo.[REVENUEPAYMENTMETHOD]
on [REVENUEPAYMENTMETHOD].[REVENUEID] = [REVENUE].[ID]
left join dbo.[CREDITCARDPAYMENTMETHODDETAIL]
on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
left join dbo.[OTHERPAYMENTMETHODDETAIL]
on [OTHERPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
where
REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 and
[SALESORDERPAYMENT].[RECONCILIATIONID] = [RECONCILIATION].[ID] and
[SALESORDERPAYMENT].[DONOTRECONCILE] = 0
) as [EXPECTEDCOUNT],
[RECONCILIATION].[ACTUALCASH] as [EXPECTEDAMOUNT]
from dbo.[RECONCILIATION]
where [RECONCILIATION].[ID] = @ID
) as [EXPECTED]
full outer join
(
select
[CREDITS].[PAYMENTMETHODCODE],
[CREDITS].[CREDITTYPECODEID],
[CREDITS].[OTHERPAYMENTMETHODCODEID],
sum([CREDITS].[AMOUNT]) as [REFUNDAMOUNT]
from (
select
[PAYMENTMETHODCODE],
case when [PAYMENTMETHODCODE] = 2 then
(
select top 1 [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]
from dbo.[CREDITCARDPAYMENTMETHODDETAIL]
inner join dbo.[REVENUEPAYMENTMETHOD]
on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
left join dbo.[REVENUESPLIT]
on [REVENUEPAYMENTMETHOD].[REVENUEID] = [REVENUESPLIT].[REVENUEID]
where
[REVENUEPAYMENTMETHOD].[REVENUEID] = [CREDITPAYMENT].[REVENUEID] or
[REVENUESPLIT].[ID] = [CREDITPAYMENT].[REVENUESPLITID]
)
else null
end as [CREDITTYPECODEID],
[OTHERPAYMENTMETHODCODEID],
[AMOUNT]
from dbo.[CREDITPAYMENT]
where
[CREDITPAYMENT].[RECONCILIATIONID] = @ID and
[CREDITPAYMENT].[PAYMENTMETHODCODE] in (2,10)
) as [CREDITS]
group by
[CREDITS].[PAYMENTMETHODCODE],
[CREDITS].[CREDITTYPECODEID],
[CREDITS].[OTHERPAYMENTMETHODCODEID]
) as REFUND
on
(
[EXPECTED].[PAYMENTMETHODCODE] = [REFUND].[PAYMENTMETHODCODE] and
(
([EXPECTED].[PAYMENTMETHODCODE] = 2 and [EXPECTED].[CREDITTYPECODEID] = [REFUND].[CREDITTYPECODEID]) or
([EXPECTED].[PAYMENTMETHODCODE] = 10 and [EXPECTED].[OTHERPAYMENTMETHODCODEID] = [REFUND].[OTHERPAYMENTMETHODCODEID])
)
)
) as [PAYMENTTYPES]
order by
[PAYMENTTYPES].[PAYMENTMETHODCODE],
[PAYMENTTYPES].[PAYMENTMETHOD]
for xml raw ('ITEM'), type, elements, root('RECONCILIATIONDETAILS'), BINARY BASE64
);
select @DEPOSITDETAILS = (
select
T.item.value('(PAYMENTMETHODCODE)[1]','tinyint') as [PAYMENTMETHODCODE],
T.item.value('(PAYMENTMETHOD)[1]','nvarchar(40)') as [PAYMENTMETHOD],
T.item.value('(DEPOSIT)[1]','decimal(20,4)') as [DEPOSIT],
T.item.value('(EXPECTED)[1]','integer') as [EXPECTED]
from @RECONCILIATIONDETAILS.nodes('/RECONCILIATIONDETAILS/ITEM') T(item)
where T.item.value('(PAYMENTMETHODCODE)[1]','tinyint') in (0,1,2)
or (T.item.value('(PAYMENTMETHODCODE)[1]','tinyint') = 10 and
T.item.value('(OTHERPAYMENTMETHODCODEID)[1]','uniqueidentifier') not in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD)
)
for xml raw ('ITEM'), type, elements, root('DEPOSITDETAILS'), BINARY BASE64
);
select @TOTALDEPOSITAMOUNT = (
select coalesce(sum(T.item.value('(DEPOSIT)[1]','decimal(20,4)')),0)
from @DEPOSITDETAILS.nodes('/DEPOSITDETAILS/ITEM') T(item)
);
select @OTHERDETAILS = (
select
[OTHERDETAILS].[PAYMENTMETHODCODE],
[OTHERDETAILS].[PAYMENTMETHOD],
[OTHERDETAILS].[AMOUNT],
[OTHERDETAILS].[EXPECTED],
[OTHERDETAILS].[ISREFUND]
from (
select
T.item.value('(PAYMENTMETHODCODE)[1]','tinyint') as [PAYMENTMETHODCODE],
T.item.value('(PAYMENTMETHOD)[1]','nvarchar(40)') as [PAYMENTMETHOD],
T.item.value('(DEPOSIT)[1]','decimal(20,4)') as [AMOUNT],
T.item.value('(EXPECTED)[1]','integer') as [EXPECTED],
cast(0 as bit) as [ISREFUND]
from @RECONCILIATIONDETAILS.nodes('/RECONCILIATIONDETAILS/ITEM') T(item)
where T.item.value('(PAYMENTMETHODCODE)[1]','tinyint') = 10
and T.item.value('(OTHERPAYMENTMETHODCODEID)[1]','uniqueidentifier') in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD)
union all
select
[REFUNDPAYMENTTYPES].[PAYMENTMETHODCODE],
[REFUNDPAYMENTTYPES].[PAYMENTMETHOD],
-1 * [REFUNDPAYMENTTYPES].[AMOUNT],
[REFUNDPAYMENTTYPES].[EXPECTED],
[REFUNDPAYMENTTYPES].[ISREFUND]
from (
select
[CREDITS].[PAYMENTMETHODCODE],
case [CREDITS].[PAYMENTMETHODCODE]
when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITS].[CREDITTYPECODEID])
when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([CREDITS].[OTHERPAYMENTMETHODCODEID])
else dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION([CREDITS].[PAYMENTMETHODCODE])
end [PAYMENTMETHOD],
sum([CREDITS].[AMOUNT]) as [AMOUNT],
count([CREDITS].[ID]) as [EXPECTED],
cast(1 as bit) as [ISREFUND]
from (
select
[CREDITPAYMENT].[ID] as [ID],
[PAYMENTMETHODCODE],
case when [PAYMENTMETHODCODE] = 2 then
(
select top 1 [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]
from dbo.[CREDITCARDPAYMENTMETHODDETAIL]
inner join dbo.[REVENUEPAYMENTMETHOD]
on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
left join dbo.[REVENUESPLIT]
on [REVENUEPAYMENTMETHOD].[REVENUEID] = [REVENUESPLIT].[REVENUEID]
where
[REVENUEPAYMENTMETHOD].[REVENUEID] = [CREDITPAYMENT].[REVENUEID] or
[REVENUESPLIT].[ID] = [CREDITPAYMENT].[REVENUESPLITID]
)
else null
end as [CREDITTYPECODEID],
[OTHERPAYMENTMETHODCODEID],
[AMOUNT]
from dbo.[CREDITPAYMENT]
where
[CREDITPAYMENT].[RECONCILIATIONID] = @ID and
[CREDITPAYMENT].[PAYMENTMETHODCODE] in (0, 1, 2, 10)
) as [CREDITS]
group by
[CREDITS].[PAYMENTMETHODCODE],
[CREDITS].[CREDITTYPECODEID],
[CREDITS].[OTHERPAYMENTMETHODCODEID]
) as [REFUNDPAYMENTTYPES]
) as [OTHERDETAILS]
for xml raw ('ITEM'), type, elements, root('OTHERDETAILS'), BINARY BASE64
);
return 0;