UFN_PDACCOUNTCODEMAPPING_VALIDMAPPING2
Returns 1 if the PDAccountCodeMapping record is unique, 0 otherwise if it is a duplicate of another record.
Return
| Return Type |
|---|
| bit |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PDAccountCodeMappingID | uniqueidentifier | IN | |
| @PDAccountSystemID | uniqueidentifier | IN | |
| @DATECHANGED | datetime | IN |
Definition
Copy
create function dbo.UFN_PDACCOUNTCODEMAPPING_VALIDMAPPING2(@PDAccountCodeMappingID uniqueidentifier, @PDAccountSystemID uniqueidentifier, @DATECHANGED datetime)
returns bit
with execute as caller
as
begin
declare @Valid bit
if exists (select * from
(select isnull(t2.NAMEID,0) as RevenueID, isnull(t3.NAMEID,0) as ApplicationID, isnull(t4.NAMEID,0) as PaymentMethodID, t1.OFFICEID
from dbo.PDACCOUNTCODEMAPPING t1 left join dbo.PDACCOUNTCODEMAPREVENUETYPE t2 on (t1.REVENUETYPE & t2.NAMEID) > 0 and t1.OFFICEID = t2.OFFICEID
left join dbo.PDACCOUNTCODEMAPAPPLICATIONTYPE t3 on (t1.APPLICATIONTYPE & t3.NAMEID) > 0 and t1.OFFICEID = t3.OFFICEID
left join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE t4 on (t1.PAYMENTMETHOD & t4.NAMEID) > 0 and t1.OFFICEID = t4.OFFICEID
where t1.PDACCOUNTSYSTEMID = @PDAccountSystemID and t1.ID != @PDAccountCodeMappingID
and (t2.HASSUBTYPE = 0 or t1.REVENUETYPE = 0)
and (t3.HASSUBTYPE = 0 or t1.APPLICATIONTYPE = 0)
and (t4.HASSUBTYPE = 0 or t1.PAYMENTMETHOD = 0)) V1
inner join
(select isnull(t2.NAMEID,0) as RevenueID, isnull(t3.NAMEID,0) as ApplicationID, isnull(t4.NAMEID,0) as PaymentMethodID, t1.OFFICEID
from dbo.PDACCOUNTCODEMAPPING t1 left join dbo.PDACCOUNTCODEMAPREVENUETYPE t2 on (t1.REVENUETYPE & t2.NAMEID) > 0 and t1.OFFICEID = t2.OFFICEID
left join dbo.PDACCOUNTCODEMAPAPPLICATIONTYPE t3 on (t1.APPLICATIONTYPE & t3.NAMEID) > 0 and t1.OFFICEID = t3.OFFICEID
left join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE t4 on (t1.PAYMENTMETHOD & t4.NAMEID) > 0 and t1.OFFICEID = t4.OFFICEID
where t1.PDACCOUNTSYSTEMID = @PDAccountSystemID and t1.ID = @PDAccountCodeMappingID
and (t2.HASSUBTYPE = 0 or t1.REVENUETYPE = 0)
and (t3.HASSUBTYPE = 0 or t1.APPLICATIONTYPE = 0)
and (t4.HASSUBTYPE = 0 or t1.PAYMENTMETHOD = 0)) V2
on V1.OfficeID = V2.OFFICEID and V1.RevenueID = V2.RevenueID and V1.ApplicationID = V2.ApplicationID and V1.PaymentMethodID = V2.PaymentMethodID
)
set @Valid = 0
else
set @Valid = 1
return @Valid
end