ALTER PROCEDURE [dbo].[sp_cfa_AfsSplitLT_Taxes](@afsid int )
AS
--Get afsSource for return
Begin
Declare @afsSource nvarchar(20)
Set @afsSource = (Select afsSource from proc_cfa.dbo.P_Availableforsale where afsid = @afsid)
End
--Add taxes to Available for sale
Begin
--Get Summary Data
;WITH taxesCTE(P_Fed, P_St, P_City, P_R1, afsAmount, afsSource, afsSourceDetail, afsDate, afsStatus, afsTransaction)
--from
AS ( Select round(IsNull(p.fedW2G,0) / 100 * x.afsAmount,2) [P_Fed],
round(IsNull(p.stateW2G,0) / 100 * x.afsAmount,2) [P_St],
round(IsNull(p.cityW2G,0) / 100 * x.afsAmount,2) [P_City],
x.afsAmount - (round(IsNull(p.fedW2G,0) / 100 * x.afsAmount,2))
- (round(IsNull(p.stateW2G,0) / 100 * x.afsAmount,2))
- (round(IsNull(p.cityW2G,0) / 100 * x.afsAmount,2)) [P_R1],
x.afsAmount,
x.afsSource,
x.afsSourceDetail,
x.afsDate,
x.afsStatus,
x.afsTransaction
From proc_lt.dbo.p_deals p
Join (Select top 1 SUBSTRING(afsSource,4,4) DealID, *
from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid
order by afsSource) x
on p.DealID = x.DealID
)
--use CTE Data and Insert all rows with one insert/ where each insert afsamount > 0
INSERT INTO PROC_CFA.dbo.P_AvailableForSale
(afsSource
,afsSourceDetail
,afsDate
,afsAmount
,afsStatus
,afsTransaction)
SELECT afsSource, afsSourceDetail+ '-REG', afsDate, P_R1, afsStatus, afsTransaction
FROM taxesCTE where P_R1 > 0
UNION
SELECT afsSource, afsSourceDetail+ '-FED', afsDate, P_Fed, afsStatus, afsTransaction
FROM taxesCTE where P_Fed > 0
UNION
SELECT afsSource, afsSourceDetail+ '-ST', afsDate, P_St, afsStatus, afsTransaction
FROM taxesCTE where P_St > 0
UNION
SELECT afsSource, afsSourceDetail+ '-CITY', afsDate, P_City, afsStatus, afsTransaction
FROM taxesCTE where P_City > 0
--Remove original afsid
Delete from proc_cfa.dbo.P_AvailableForSale where afsid = @afsid
End
Select @afsSource as afsSource
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.