UFN_PDACCOUNTCODEMAPPING_VALIDMAPPING
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 | |
| @RevenueType | int | IN | |
| @ApplicationType | int | IN | |
| @PaymentMethod | int | IN |
Definition
Copy
CREATE function dbo.UFN_PDACCOUNTCODEMAPPING_VALIDMAPPING(@PDAccountCodeMappingID uniqueidentifier, @RevenueType int, @ApplicationType int, @PaymentMethod int)
--kwb Function is created with the four arguments so that check constraint CK_PDACCOUNTCODEMAPPING_VALIDMAPPING
--is created as a table level constraint instead of a column constraint. Do not remove the unused arguments
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 = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B' 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 = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B' 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