Alfa Romeo Forum banner

1 - 20 of 140 Posts

·
Registered
Joined
·
459 Posts
Discussion Starter #1
Due to the amount of violent crime that occurs on this forum, in the form of thread hijacking, I thought I would start a kinda official chat thread for all the incidentals that are guilty of changing the nature of a thread. Therefore this thread may be hijacked, shot, beaten to a pulp, robbed, raped, and left for dead. But please do it politely!!!!!!
 

·
Premium Member
Joined
·
5,467 Posts
I rarely watch TV nowadays, but last night saw "the Mentalist"..... great series, I love the attitude and lack of tact..... maybe I should be a bit more tactless :cheese: , it may endear me to more people.
 

·
Registered
Joined
·
2,758 Posts
LOL.... Just had a lovely toasted sandwich. Will have to see later on.

So then... How busy is everyone at work at the moment...?

Great thread Joe.
 

·
Registered
Joined
·
7,640 Posts
im battling with absolute bull****
---------------------------BUILD STATEMENT ITEM RULES - COLSTATEMENTITEM ----------------------------------
--Debit Order Start Date
UPDATE A
SET A.colDebitOrderStartDate = Z.DebitOrderStartDate
FROM dimDealLink A
INNER JOIN
(
SELECT cpkDealLinkInt, min(colGregorianDate) 'DebitOrderStartDate'
FROM dimDealLink
INNER JOIN fctStatementRun ON cpkDealLinkInt = cskDealLinkInt
INNER JOIN dimFlow on cskSourceFlowInt = cpkFlowInt and colFlowType = 'P'
INNER JOIN dimTime on cskSourceTransActionDate = cpkTimeInt
WHERE colDebitOrderAmount IS NOT NULL
group by cpkDealLinkInt
) Z
ON A.cpkDealLinkInt = Z.cpkDealLinkInt

--Investment

UPDATE fctStatementRun
SET colStatementItem = 'Investment',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'P'


--Automated Interest Addition
UPDATE fctStatementRun
SET colStatementItem = 'Automated Interest Addition',
colDescription = 'Not Applicable',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = '^'

--Automated Interest Addition Description
UPDATE A
SET A.colDescription = 'Retirement Fund Tax'
FROM fctStatementRun A
INNER JOIN
(
SELECT DISTINCT cpkStatementRunInt, cskProductInt, dimProduct.colProductCode, lkpProductGroup.colProductGroupProcess
FROM fctStatementRun
INNER JOIN dimProduct
ON cskProductInt = cpkProductInt
INNER JOIN lkpProductGroup
on dimProduct.colProductCode = lkpProductGroup.colProductCode
AND lkpProductGroup.colProductGroupProcess IN ('EB','EK','EI')
WHERE colStatementItem = 'Automated Interest Addition'
) Z
ON A.cpkStatementRunInt = Z.cpkStatementRunInt

UPDATE A
SET A.colDescription = 'Endowment Tax'
FROM fctStatementRun A
INNER JOIN
(
SELECT DISTINCT cpkStatementRunInt, cskProductInt, dimProduct.colProductCode, lkpProductGroup.colProductGroupProcess
FROM fctStatementRun
INNER JOIN dimProduct
ON cskProductInt = cpkProductInt
INNER JOIN lkpProductGroup
on dimProduct.colProductCode = lkpProductGroup.colProductCode
AND lkpProductGroup.colProductGroupProcess IN ('EM')
WHERE colStatementItem = 'Automated Interest Addition'
) Z
ON A.cpkStatementRunInt = Z.cpkStatementRunInt


--Interest Capitalised
UPDATE fctStatementRun
SET colStatementItem = 'Interest Capitalised',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationAmount --changed from colDestinationNetAmount, as value is zero
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'B'

--KJA 20090511 split annual and admin
--Annual Fees/Reductions
UPDATE fctStatementRun
SET colStatementItem = 'Annual Fees/Reductions',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType IN ('G','!')

/*--Admin Fees/Reductions
UPDATE fctStatementRun
SET colStatementItem = 'Admin. Fees/Reductions',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType IN ('!')*/

--Income
UPDATE fctStatementRun
SET colStatementItem = 'Income',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'I'


--Distributions
UPDATE fctStatementRun
SET colStatementItem = 'Distributions',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'D'


--Withdrawal -- note description will be updated by transactiontype
UPDATE fctStatementRun
SET colStatementItem = 'Withdrawal',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType IN ('L')

--KJA 20090513 split L and 8
UPDATE fctStatementRun
SET colStatementItem = 'Adhoc Incomes',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType IN ('8')

--Tax
UPDATE fctStatementRun
SET colStatementItem = 'Tax',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'Z'


--Inter Investment Transfer Destination
UPDATE fctStatementRun
SET colStatementItem = 'Inter Investment Transfer',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'X'


--Inter Investment Transfer Source
UPDATE fctStatementRun
SET colStatementItem = 'Inter Investment Transfer',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'X'


--------------------------------------------------------------------------------------------------------------
--KJA 20090507 add in debit order reversals
--Debit Order Reversals
UPDATE fctStatementRun
SET colStatementItem = 'Debit Order Reversal',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '9'

--------------------------------------------------------------------------------------------------------------
--KJA 20090511 add in Error Correction - check both directions - sometimes not included on statement, why?
--Error Correction

UPDATE fctStatementRun
SET colStatementItem = 'Error Correction',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'E'
AND colSourceAmount <> 0

UPDATE fctStatementRun
SET colStatementItem = 'Error Correction',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'E'
AND colDestinationAmount <> 0

--------------------------------------------------------------------------------------------------------------
--KJA 20090511 add in Broker Service Fee
--Broker Service Fee
UPDATE fctStatementRun
SET colStatementItem = 'Broker Service Fee',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '~'
AND colSourceAmount <> 0

--------------------------------------------------------------------------------------------------------------
--KJA 20090511 add in CGT Deduction
--CGT Deduction
UPDATE fctStatementRun
SET colStatementItem = 'CGT Deduction',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '&'
AND colSourceAmount <> 0

--------------------------------------------------------------------------------------------------------------
--KJA 20090511 add in Commercial Bank - no sample
--Commercial Bank
UPDATE fctStatementRun
SET colStatementItem = 'Commercial Bank',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'Y'
AND colDestinationAmount <> 0
AND colStatementItem IS NULL

--------------------------------------------------------------------------------------------------------------
--KJA 20090511 add in Annuity Funding - no sample
--Annuity Funding
UPDATE fctStatementRun
SET colStatementItem = 'Annuity Funding',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'H'
AND colDestinationAmount <> 0
AND colStatementItem IS NULL

--------------------------------------------------------------------------------------------------------------
--KJA 20090511 add in Delayed settlement
--Delayed settlement
UPDATE fctStatementRun
SET colStatementItem = 'Delayed settlement',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '4'
AND colSourceAmount <> 0

UPDATE fctStatementRun
SET colStatementItem = 'Interest - Delayed Settlement',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'O'
AND colSourceAmount <> 0

--------------------------------------------------------------------------------------------------------------
--KJA 20090511 add in Maturity - note description will be overridden by transactiontype

--Maturity
UPDATE fctStatementRun
SET colStatementItem = 'Maturity',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'M'
AND colSourceAmount <> 0

--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in Residual Re-Investment
--Residual Re-Investment
UPDATE fctStatementRun
SET colStatementItem = 'Residual Re-Investment',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'Q'
AND colDestinationAmount <> 0

--KJA 20090608 add in Residual Re-Investment for source as well
UPDATE fctStatementRun
SET colStatementItem = 'Residual Re-Investment',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'Q'
AND colSourceAmount <> 0

--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in Stamp Duty - old

--Stamp Duty
UPDATE fctStatementRun
SET colStatementItem = 'Stamp Duty',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '3'
AND colSourceAmount <> 0
AND colStatementItem IS NULL

--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in Investment During Term - old, no sample, but just in case!
--Investment During Term
UPDATE fctStatementRun
SET colStatementItem = 'Investment During Term',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '6'
AND colSourceAmount <> 0
AND colStatementItem IS NULL

--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in System Generated Write Off - old
--System Generated Write Off
UPDATE fctStatementRun
SET colStatementItem = 'System Generated Write Off',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'N'
AND colSourceAmount <> 0
AND colStatementItem IS NULL

--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in Statement Accounting - old
--Statement Accounting
UPDATE fctStatementRun
SET colStatementItem = 'Statement Accounting',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'A'
AND colStatementItem IS NULL

--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in Tax Rebate Investment - old
--Tax Rebate Investment --- comment out until default corrected
/*
UPDATE fctStatementRun
SET colStatementItem = 'Tax Rebate Investment',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = '0'
AND colStatementItem IS NULL

select * from dimflow
*/
--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in Journal - old
--Journal Destination
UPDATE fctStatementRun
SET colStatementItem = 'Journal',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'J'
AND colStatementItem IS NULL

--Journal Source
UPDATE fctStatementRun
SET colStatementItem = 'Journal',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'J'
AND colStatementItem IS NULL

--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in NBS, RMB zero fees - old
--NBS, RMB zero fees Destination
UPDATE fctStatementRun
SET colStatementItem = 'NBS, RMB zero fees',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = '7'
AND colStatementItem IS NULL

--NBS, RMB zero fees Source
UPDATE fctStatementRun
SET colStatementItem = 'NBS, RMB zero fees',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '7'
AND colStatementItem IS NULL
--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in RMBT, Norwich zero fee - old
--RMBT, Norwich zero fee Destination
UPDATE fctStatementRun
SET colStatementItem = 'RMBT, Norwich zero fee',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = '2'
AND colStatementItem IS NULL

--RMBT, Norwich zero fee Source
UPDATE fctStatementRun
SET colStatementItem = 'RMBT, Norwich zero fee',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '2'
AND colStatementItem IS NULL

--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in Loan Repayment - old, no sample
--Loan Repayment
UPDATE fctStatementRun
SET colStatementItem = 'Loan Repayment',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = '#'
AND colDestinationAmount <> 0
AND colStatementItem IS NULL

--------------------------------------------------------------------------------------------------------------
--KJA 20090512 add in Third Party Re-investment
--Third Party Re-investment
UPDATE fctStatementRun
SET colStatementItem = 'Third Party Re-investment',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'S'
AND colDestinationAmount <> 0

---------------------------------------------------------------------------------------------------------------------------------------------------------
---------ADD SOURCE FOR SWITCHES (Intra & Inter)
--Inter Manco Switch Destination
UPDATE fctStatementRun
SET colStatementItem = 'Inter Manco Switch',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'V'

--Inter Manco Switch Source
UPDATE fctStatementRun
SET colStatementItem = 'Inter Manco Switch',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'V'

--KJA 20090512 add in Intra Class Switch and Multipart Manco Switch and
--Intra Class Switch Destination
UPDATE fctStatementRun
SET colStatementItem = 'Intra Class Switch',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'W'

--Intra Class Switch Source
UPDATE fctStatementRun
SET colStatementItem = 'Intra Class Switch',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'W'

--Multipart Manco Switch Destination
UPDATE fctStatementRun
SET colStatementItem = 'Multipart Manco Switch',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'U'

--Multipart Manco Switch Source
UPDATE fctStatementRun
SET colStatementItem = 'Multipart Manco Switch',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'U'

--JMM Zero Fee Switches Destination
UPDATE fctStatementRun
SET colStatementItem = 'JMM Zero Fee Switches',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = '1'
AND colStatementItem IS NULL

--JMM Zero Fee Switches Source
UPDATE fctStatementRun
SET colStatementItem = 'JMM Zero Fee Switches',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '1'
AND colStatementItem IS NULL


--Intra Manco Switch Destination
UPDATE fctStatementRun
SET colStatementItem = 'Intra Manco Switch',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'T'


--Intra Manco Switch Source
UPDATE fctStatementRun
SET colStatementItem = 'Intra Manco Switch',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'T'

--KJA 20090511 differentiate broker and admin fund select
--Fund Select Rebate - broker
UPDATE A
SET colStatementItem = 'FundSelect Rebate (Broker)',
-- cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM fctStatementRun A,
dimFlow F,
(SELECT B.cpkStatementRunInt, B.cskDealLinkInt, B.colSourceFlowNumber
FROM fctStatementRun B, dimInstrument I, dimFlow F2
WHERE B.cskSourceFlowInt = F2.cpkFlowInt and F2.colFlowType = 'K'
AND B.cskSourceInstrumentInt = I.cpkInstrumentInt and I.colInstrumentNumber = 984) C
WHERE A.cskDestinationFlowInt = F.cpkFlowInt and F.colFlowType = 'K'
AND A.cskDealLinkInt = C.cskDealLinkInt
AND A.colDestinationFlowNumber = C.colSourceFlowNumber

--Fund Select Rebate - admin
UPDATE A
SET colStatementItem = 'FundSelect Rebate (Admin)',
-- cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM fctStatementRun A,
dimFlow F,
(SELECT B.cpkStatementRunInt, B.cskDealLinkInt, B.colSourceFlowNumber
FROM fctStatementRun B, dimInstrument I, dimFlow F2
WHERE B.cskSourceFlowInt = F2.cpkFlowInt and F2.colFlowType = 'K'
AND B.cskSourceInstrumentInt = I.cpkInstrumentInt and I.colInstrumentNumber = 985) C
WHERE A.cskDestinationFlowInt = F.cpkFlowInt and F.colFlowType = 'K'
AND A.cskDealLinkInt = C.cskDealLinkInt
AND A.colDestinationFlowNumber = C.colSourceFlowNumber

--Risk Premium
UPDATE fctStatementRun
SET colStatementItem = 'Risk Premium',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '-'

/*--Loan (Member)
UPDATE fctStatementRun
SET colStatementItem = 'Loan (Member)',
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow, dimInstrument
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = '8'
AND cskSourceInstrumentInt = cpkInstrumentInt and colUnderlyingCode <> 'CASH'
*/

--KJA 20090429 add in feed flow type
--Feed
UPDATE fctStatementRun
SET colStatementItem = 'Feed',
cskStatementTransactionLinkInt = cskSourceTransactionLinkInt,
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType = 'F'

UPDATE fctStatementRun
SET colStatementItem = 'Feed',
cskStatementTransactionLinkInt = cskDestinationTransactionLinkInt,
cskStatementInstrumentInt = cskDestinationInstrumentInt,
cskStatementTransActionDate = cskDestinationTransActionDate,
colStatementAmount = colDestinationAmount,
colStatementUnits = colDestinationUnits,
colStatementInitialFee = colDestinationInitialFee,
colStatementNetAmount = colDestinationNetAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType = 'F'

-----------------------------------------------------------------------------------------------------------------------------
--KJA 20090513 fix up statmentitem with transactiontype

UPDATE fctStatementRun
SET colStatementItem = TT.colTransactTypeDescription --select R.cskcontractInt, colStatementItem, TT.colTransactTypeDescription
FROM fctStatementRun R,
fctTransaction T,
dimTransact TT
WHERE R.cskStatementTransactionLinkInt <> -1
AND R.cskStatementTransactionLinkInt = T.cskTransactionLinkInt
AND T.cskTransactInt <> -1
AND T.cskTransactInt = TT.cpkTransactInt
AND colStatementItem IS NOT NULL
AND RTRIM(ISNULL(TT.colTransactTypeDescription,'')) <> ''
AND RTRIM(TT.colTransactTypeDescription) <> RTRIM(colStatementItem)
--order by R.cskcontractInt


--Interest Free Loans--------------------------------------------------------------
--KJA 20090324 use new columns
UPDATE A
SET A.colLoanItem = 'Interest Free Loan',
A.colLoanAmount = Z.colSourceAmount,
A.colLoanFlowNumber = Z.colSourceFlowNumber
FROM fctStatementRun A
INNER JOIN
(
SELECT cpkStatementRunInt,
cskDealLinkInt,P.colProductCode,colSourceFlowNumber,
colSourceAmount, cskSourceInstrumentInt, cskSourceTransActionDate
FROM fctStatementRun A
INNER JOIN dimInstrument I
ON A.cskSourceInstrumentInt = I.cpkInstrumentInt
INNER JOIN dimProduct P
ON A.cskProductInt = P.cpkProductInt
INNER JOIN lkpProductGroup LP
ON P.colProductCode = LP.colProductcode
--NN 20090420
AND P.colProductCode NOT IN
( SELECT colProductCode from lkpProductGroup where colProductGroupProcess = 'BZ')
WHERE I.colInstrumentNumber = 7282 -- cskInstrumentInt = 98
--AND LP.colProductGroupProcess <> 'BZ'
--AND colSourceAmount > 0
) Z
ON A.cpkStatementRunInt = Z.cpkStatementRunInt

UPDATE A
SET A.colLoanItem = 'Interest Free Loan',
A.colLoanAmount = Z.colDestinationAmount,
A.colLoanFlowNumber = Z.colDestinationFlowNumber
FROM fctStatementRun A
INNER JOIN
(
SELECT cpkStatementRunInt,
cskDealLinkInt,P.colProductCode,colDestinationFlowNumber,
colDestinationAmount, cskDestinationInstrumentInt, cskDestinationTransActionDate, LP.colProductGroupProcess
FROM fctStatementRun A
INNER JOIN dimInstrument I
ON A.cskDestinationInstrumentInt = I.cpkInstrumentInt
INNER JOIN dimProduct P
ON A.cskProductInt = P.cpkProductInt
INNER JOIN lkpProductGroup LP
ON P.colProductCode = LP.colProductcode
--NN 20090420 - to exclude BZ
AND P.colProductCode NOT IN
( SELECT colProductCode from lkpProductGroup where colProductGroupProcess = 'BZ')
WHERE I.colInstrumentNumber = 7282 -- cskInstrumentInt = 98
--AND LP.colProductGroupProcess <> 'BZ'
--AND colDestinationAmount > 0
--AND cskDealLinkInt = 127
) Z
ON A.cpkStatementRunInt = Z.cpkStatementRunInt


--KJA 20090327 include CGT if same flow
/*
--KJA 20090512 loan CGT the wrong way?
UPDATE A
SET A.colLoanItem = 'Interest Free Loan CGT',
A.colLoanAmount = X.colDestinationAmount
FROM fctStatementRun A,
(SELECT B.cpkStatementRunInt, B.cskDealLinkInt, B.colDestinationAmount, B.colDestinationFlowNumber
FROM fctStatementRun B
INNER JOIN dimFlow F
ON B.cskDestinationFlowInt = F.cpkFlowInt
WHERE F.colFlowType = '&') X
WHERE A.colLoanItem = 'Interest Free Loan'
AND A.colLoanFlowNumber = X.colDestinationFlowNumber
AND A.cskDealLinkInt = X.cskDealLinkInt
*/

UPDATE A
SET A.colLoanItem = 'Interest Free Loan CGT',
A.colLoanAmount = A.colDestinationAmount
FROM fctStatementRun A,
dimFlow F,
(SELECT B.cpkStatementRunInt, B.cskDealLinkInt, B.colLoanAmount, B.colLoanFlowNumber
FROM fctStatementRun B
WHERE B.colLoanItem = 'Interest Free Loan') X
WHERE A.cskDestinationFlowInt = F.cpkFlowInt
AND F.colFlowType = '&'
AND A.colDestinationFlowNumber = X.colLoanFlowNumber
AND A.cskDealLinkInt = X.cskDealLinkInt

----------------------------BUILD STATEMENT ITEM RULES - COLSPLITITEM ----------------------------------
--Repurchase
/*UPDATE fctStatementRun
SET colSplitItem = 'Repurchase',
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount
FROM dimFlow, dimInstrument
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType IN ('L','8')
AND cskDestinationInstrumentInt = cpkInstrumentInt and colInstrumentNumber IN (45,7282,9061)
select * from fctstatementrun where colAdditionalItem = 'Repurchase' and cskcontractint in (103,212)
order by cskcontractint
*/
--KJA 20090331 link in instruction number - going to use loanFlowNumber so as not to add new column, will clear later
update fctStatementRun
set colAdditionalItem = 'Repurchase',
colDescription = T.colTransactTypeDescription, --colRepurchaseItem
colStatementAmount = R.colDestinationAmount, --colRepurchaseAmount
cskStatementTransActionDate = Tm.cpkTimeInt, --cskRepurchaseDateID
colTempInt = 1, --colRepurchaseOrder
colLoanflowNumber = colDestinationInstructionNumber
from fctStatementRun R,
dimInstrument I,
dimFlow F,
dimTransact T,
dimTime Tm
where colDestinationInstructionNumber > 0
and R.cskDestinationInstrumentInt = I.cpkInstrumentInt
and I.colInstrumentNumber = 890
and R.cskDestinationFlowInt = F.cpkFlowInt
and R.cskDestinationTransactInt = T.cpkTransactInt
and R.cskDestinationTransActionDate = Tm.cpkTimeInt
--and Tm.colGregorianDate between '16 Aug 2008' AND '31 Dec 2008'
and ((F.colFlowType = 'M'
AND T.colTransactType IN (47,48,49,50,51,52,53,54,55,56,63,64,65,66,67))
OR (F.colFlowType = '8'
AND T.colTransactType IN (68,69,70,72,73,74,75,76))
OR (F.colFlowType = 'I'
AND T.colTransactType = 71)
OR (F.colFlowType = 'L'
AND T.colTransactType = 77)
OR (F.colFlowType = '-'
AND T.colTransactType = 78))

--** Repurchase get tax overpayments
update fctStatementRun
set colAdditionalItem = 'Repurchase',
colDescription = 'PAYE / IT88 Tax Adjustment',
colStatementAmount = -1 * R.colSourceAmount,
cskStatementTransActionDate = Tm.cpkTimeInt,
colTempInt = 1,
colLoanflowNumber = colSourceInstructionNumber
from fctStatementRun R,
dimInstrument I,
dimFlow F,
dimTransact T,
dimTime Tm
where colSourceInstructionNumber > 0
and R.cskSourceInstrumentInt = I.cpkInstrumentInt
and I.colInstrumentNumber IN (9063, 9064) -- IT88, PAYE
and R.cskSourceFlowInt = F.cpkFlowInt
and R.cskSourceTransactInt = T.cpkTransactInt
and R.cskSourceTransActionDate = Tm.cpkTimeInt
--and Tm.colGregorianDate between '16 Aug 2008' AND '31 Dec 2008'
and F.colFlowType = 'Z'
and T.colTransactType IN (79,90)



--** Repurchase get tax underpayments
update fctStatementRun
set colAdditionalItem = 'Repurchase',
colDescription = 'PAYE / IT88 Tax Adjustment',
colStatementAmount = R.colSourceAmount,
cskStatementTransActionDate = Tm.cpkTimeInt,
colTempInt = 1,
colLoanflowNumber = colSourceInstructionNumber
from fctStatementRun R,
dimInstrument I,
dimFlow F,
dimTransact T,
dimTime Tm
where colSourceInstructionNumber > 0
and R.cskSourceInstrumentInt = I.cpkInstrumentInt
and I.colInstrumentNumber IN (6)
and R.cskSourceFlowInt = F.cpkFlowInt
and R.cskSourceTransactInt = T.cpkTransactInt
and R.cskSourceTransActionDate = Tm.cpkTimeInt
--and Tm.colGregorianDate between '16 Aug 2008' AND '31 Dec 2008'
and F.colFlowType = 'Z'
and T.colTransactType IN (79,90)

--** Repurchase get the fee details
update fctStatementRun
set colAdditionalItem = 'Repurchase',
colDescription = T.colTransactTypeDescription,
colStatementAmount = R.colSourceAmount,
cskStatementTransActionDate = Tm.cpkTimeInt,
colTempInt = 2,
colLoanflowNumber = colSourceInstructionNumber
from fctStatementRun R,
dimInstrument I,
dimFlow F,
dimTransact T,
dimTime Tm
where colSourceInstructionNumber > 0
and R.cskSourceInstrumentInt = I.cpkInstrumentInt
and I.colInstrumentNumber IN (890)
and R.cskSourceFlowInt = F.cpkFlowInt
and R.cskSourceTransactInt = T.cpkTransactInt
and R.cskSourceTransActionDate = Tm.cpkTimeInt
--and Tm.colGregorianDate between '16 Aug 2008' AND '31 Dec 2008'
and F.colFlowType = 'G'
and T.colTransactType IN (82)


--** Repurchase get GAPU CGT details
update fctStatementRun
set colAdditionalItem = 'Repurchase',
colDescription = 'CGT',
colStatementAmount = R.colSourceAmount,
cskStatementTransActionDate = Tm.cpkTimeInt,
colTempInt = 2,
colLoanflowNumber = colSourceInstructionNumber
from fctStatementRun R,
dimInstrument I,
dimFlow F,
dimTransact T,
dimTime Tm,
dimProduct P
where colSourceInstructionNumber > 0
and R.cskSourceInstrumentInt = I.cpkInstrumentInt
and I.colInstrumentNumber IN (892)
and R.cskSourceFlowInt = F.cpkFlowInt
and R.cskSourceTransactInt = T.cpkTransactInt
and R.cskSourceTransActionDate = Tm.cpkTimeInt
--and Tm.colGregorianDate between '16 Aug 2008' AND '31 Dec 2008'
and F.colFlowType = '&'
and T.colTransactType IN (80)
and R.cskProductInt = P.cpkProductInt
and P.colProductGroupProcess = 'OS' --** GAPU



--** Repurchase get remaining CGT details
update fctStatementRun
set colAdditionalItem = 'Repurchase',
colDescription = 'CGT',
colStatementAmount = R.colSourceAmount,
cskStatementTransActionDate = Tm.cpkTimeInt,
colTempInt = 2,
colLoanflowNumber = colSourceInstructionNumber
from fctStatementRun R,
dimInstrument I,
dimFlow F,
dimTransact T,
dimTime Tm,
dimProduct P
where colSourceInstructionNumber > 0
and R.cskSourceInstrumentInt = I.cpkInstrumentInt
and I.colInstrumentNumber IN (890)
and R.cskSourceFlowInt = F.cpkFlowInt
and R.cskSourceTransactInt = T.cpkTransactInt
and R.cskSourceTransActionDate = Tm.cpkTimeInt
--and Tm.colGregorianDate between '16 Aug 2008' AND '31 Dec 2008'
and F.colFlowType = '&'
and T.colTransactType IN (80)
and R.cskProductInt = P.cpkProductInt
and P.colProductGroupProcess <> 'OS'



--** Repurchase get cash out details
update fctStatementRun
set colAdditionalItem = 'Repurchase',
colDescription= I.colInstrumentDescription,
colStatementAmount = R.colDestinationAmount,
cskStatementTransActionDate = Tm.cpkTimeInt,
colTempInt = 3,
colLoanflowNumber = colDestinationInstructionNumber
from fctStatementRun R,
dimInstrument I,
dimTime Tm
where colDestinationInstructionNumber > 0
and R.cskDestinationInstrumentInt = I.cpkInstrumentInt
and I.colInstrumentNumber IN (45,7282,9061)
and R.cskDestinationTransActionDate = Tm.cpkTimeInt
--and Tm.colGregorianDate between '16 Aug 2008' AND '31 Dec 2008'

--** Repurchase get cash in
update fctStatementRun
set colAdditionalItem = 'Repurchase',
colDescription = I.colInstrumentDescription,
colStatementAmount = R.colSourceAmount,
cskStatementTransActionDate = Tm.cpkTimeInt,
colTempInt = 3,
colLoanflowNumber = colSourceInstructionNumber
from fctStatementRun R,
dimInstrument I,
dimFlow F,
dimTransact T,
dimTime Tm
where colSourceInstructionNumber > 0
and R.cskSourceInstrumentInt = I.cpkInstrumentInt
and I.colInstrumentNumber IN (6)
and R.cskSourceFlowInt = F.cpkFlowInt
and R.cskSourceTransactInt = T.cpkTransactInt
and R.cskSourceTransActionDate = Tm.cpkTimeInt
--and Tm.colGregorianDate between '16 Aug 2008' AND '31 Dec 2008'
and F.colFlowType = 'Z'
and T.colTransactType IN (79,90)

--KJA 20090331 now fix up dates
--KJA 20090608 move date correction to Detail
/*
DROP TABLE #AllDates

SELECT colLoanflowNumber,
T.cskTransActionDate,
Tm.colGregorianDate,
bMin = 0
INTO #AllDates
FROM fctStatementRun R,
fctTransaction T,
dimTime Tm
WHERE R.colLoanflowNumber = T.colInstructionNumber
AND R.colAdditionalItem = 'Repurchase'
AND T.cskTransActionDate = Tm.cpkTimeInt
GROUP BY colLoanflowNumber,
T.cskTransActionDate,
Tm.colGregorianDate

DROP TABLE #MinDates

SELECT colLoanflowNumber,
colGregorianDate = MIN(colGregorianDate)
INTO #MinDates
FROM #AllDates
GROUP BY colLoanflowNumber

UPDATE #AllDates
SET bMin = 1
FROM #AllDates A,
#MinDates M
WHERE A.colLoanflowNumber = M.colLoanflowNumber
AND A.colGregorianDate = M.colGregorianDate

--KJA 20090513 add flag!
UPDATE fctStatementRun
SET cskStatementTransActionDate = A.cskTransActionDate
FROM fctStatementRun R,
#AllDates A
WHERE R.colLoanflowNumber = A.colLoanflowNumber
AND R.colAdditionalItem = 'Repurchase'
AND bMin = 1

UPDATE fctStatementRun
SET colLoanflowNumber = 0
WHERE colAdditionalItem = 'Repurchase'
*/

------------------------------------------------------------------------------------------------------------
--CGT
--KJA 20090402 replace
drop table #CGTOSTrx

select R1.cpkStatementRunInt, R1.cskDestinationTransactionLinkInt, T.colTransactionNumber, R1.colDestinationFlowNumber,R1.cskDealLinkInt,
R1.cskDestinationInstrumentInt, R1.colDestinationAmount, R1.cskDestinationTransActionDate
into #CGTOSTrx
from fctStatementRun R1, dimFlow, dimProduct, lkpProductGroup, dimTransactionLink T--, dimTime
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType IN ('&')
AND cskProductInt = cpkProductInt
AND dimProduct.colProductCode = lkpProductGroup.colProductCode
AND lkpProductGroup.colProductGroupProcess = 'OS'
and R1.cskDestinationTransactionLinkInt = cpkTransactionLinkInt
--and cskStatementTransActionDate = cpkTimeInt
--and colGregorianDate between '1 Dec 2008' AND '28 Feb 2009'

--select * from #CGTOSTrx
drop table #CGTOtherTrx

-- KJA 20090504 fix up negative - had already negated
select R1.cskSourceTransactionLinkInt, T.colTransactionNumber, R1.colSourceFlowNumber,R1.cskDealLinkInt,
R1.cskSourceInstrumentInt, colSourceAmount = -1 * R1.colSourceAmount, R1.cskSourceTransActionDate
INTO #CGTOtherTrx
from fctStatementRun R1, #CGTOSTrx R2,dimFlow , dimTransactionLink T
where R1.colSourceFlowNumber = R2.colDestinationFlowNumber
and R1.cskDealLinkInt = R2.cskDealLinkInt
and R1.cskSourceFlowInt = cpkFlowInt and colFlowType NOT IN ('&')
and R1.cskSourceTransactionLinkInt = cpkTransactionLinkInt

--select * from #CGTOSTrx where cskDealLinkInt = 235

drop table #CGTSource

SELECT C.cskDealLinkInt,
C.colTransactionNumber,
Source_Transaction = MAX(O.colTransactionNumber)
INTO #CGTSource
FROM #CGTOSTrx C,
#CGTOtherTrx O
WHERE O.colTransactionNumber < C.colTransactionNumber
AND O.cskDealLinkInt = C.cskDealLinkInt
AND O.colSourceFlowNumber = C.colDestinationFlowNumber
GROUP BY C.cskDealLinkInt,
C.colTransactionNumber

--select * from #CGTSource

drop table #CGT

SELECT C.cpkStatementRunInt,
C.cskDealLinkInt,
O.cskSourceInstrumentInt,
C.cskDestinationTransActionDate,
Gross_Repurchase_Amount = O.colSourceAmount,
CGT_Deducted = C.colDestinationAmount,
Net_Repurchase_Amount = O.colSourceAmount - C.colDestinationAmount --, O.colSourceAmount,C.colDestinationAmount
INTO #CGT
FROM #CGTOSTrx C,
#CGTOtherTrx O,
#CGTSource S
WHERE C.cskDealLinkInt = O.cskDealLinkInt
AND C.colTransactionNumber = S.colTransactionNumber
AND O.colTransactionNumber = S.Source_Transaction
--and c.cskdeallinkint = 235

UPDATE fctStatementRun
SET colSplitItem = 'CGT',
cskStatementInstrumentInt = C.cskSourceInstrumentInt,
cskStatementTransActionDate = C.cskDestinationTransActionDate,
colStatementAmount = Gross_Repurchase_Amount,
colStatementInitialFee = CGT_Deducted,
colStatementNetAmount = Net_Repurchase_Amount
FROM fctStatementRun R,
#CGT C
WHERE R.cpkStatementRunInt = C.cpkStatementRunInt

--------------------------------------------------------------------------------------------------------------

--Interest Accrual
/*UPDATE fctStatementRun
SET colSplitItem = 'Interest Accrual',
cskStatementInstrumentInt = cskSourceInstrumentInt,
cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementUnits = colSourceUnits,
colStatementInitialFee = colSourceInitialFee,
colStatementNetAmount = colSourceNetAmount
FROM fctStatementRun
WHERE colDataSourceItem = 'Transaction Accrual'*/

--SANJAY
--Interest Earned
UPDATE fctStatementRun
SET colSplitItem = 'Interest Earned'
FROM fctStatementRun R,
dimInstrument I,
dimFlow F
WHERE cskDestinationInstrumentInt = cpkInstrumentInt and colInstrumentType IN ('CALL','FIX','GCAL','NOT','FEED','FSCAL')
AND cskDestinationFlowInt = cpkFlowInt and colFlowType = 'B'

----------------------------BUILD CONTRACT ITEM RULES----------------------------------
--Initial Amount Invested
/*UPDATE fctStatementRun
SET colContractItem = 'Initial Amount Invested',
colStatementAmount = colDestinationAmount
FROM dimFlow
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType in ('P', '+')*/

/*UPDATE fctStatementRun
SET colContractItem = 'Initial Amount Invested',
colStatementAmount = colSourceAmount
FROM dimFlow
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType in ('P', '+')
AND colSourceAmount > 0*/

UPDATE fctStatementRun
SET colContractItem = 'Initial Amount Invested',
colContractAmount = colSourceAmount * -1,
cskContractTransActionDate = cskSourceTransActionDate
FROM dimInstrument
WHERE cskSourceInstrumentInt = cpkInstrumentInt and colInstrumentNumber = 6


UPDATE fctStatementRun
SET colContractItem = 'Initial Amount Invested',
colContractAmount = colDestinationAmount *-1,
cskContractTransActionDate = cskDestinationTransActionDate
FROM dimInstrument
WHERE cskDestinationInstrumentInt = cpkInstrumentInt and colInstrumentNumber in (355,460)


UPDATE fctStatementRun
SET colContractItem = 'Initial Amount Invested',
colContractAmount = 0
FROM dimProduct
WHERE cskProductInt = cpkProductInt and colProductCode in ('VIA')

--KJA 20090513 fix direction
--Amount Transferred Out
UPDATE fctStatementRun
SET colContractItem = 'Amount Transferred Out',
colContractAmount = colDestinationAmount,
cskContractTransActionDate = cskDestinationTransActionDate
FROM dimFlow, dimInstrument
WHERE cskDestinationFlowInt = cpkFlowInt and colFlowType in ('*','X')
AND cskDestinationInstrumentInt = cpkInstrumentInt and colInstrumentType = 'XFER'

--Amount Transferred In
UPDATE fctStatementRun
SET colContractItem = 'Amount Transferred In',
colContractAmount = -1 * colSourceAmount,
cskContractTransActionDate = cskSourceTransActionDate
FROM dimFlow, dimInstrument
WHERE cskSourceFlowInt = cpkFlowInt and colFlowType in ('*','X')
AND cskSourceInstrumentInt = cpkInstrumentInt and colInstrumentType = 'XFER'

--Amount Withdrawn
UPDATE fctStatementRun
SET colContractItem = 'Amount Withdrawn',
colContractAmount = colDestinationAmount,
cskContractTransActionDate = cskDestinationTransActionDate
FROM dimInstrument
WHERE cskDestinationInstrumentInt = cpkInstrumentInt and colInstrumentNumber = 45

--KJA 20090327 add in tax
UPDATE R
SET R.colContractItem = 'Amount Withdrawn',
R.colContractAmount = R.colDestinationAmount
FROM fctStatementRun R,
dimFlow F,
dimInstrument I,
fctStatementRun R2
WHERE R.cskDestinationFlowInt = cpkFlowInt
AND F.colFlowType = 'Z'
AND R.cskDestinationInstrumentInt = I.cpkInstrumentInt
AND I.colInstrumentNumber IN (226,227)
AND R.cskDealLinkInt = R2.cskDealLinkInt
AND R.cskDestinationTransActionDate = R2.cskDestinationTransActionDate
AND R2.colContractItem = 'Amount Withdrawn'

/*UPDATE fctStatementRun
SET colContractItem = 'Amount Withdrawn',
colStatementAmount = colSourceAmount
FROM dimInstrument
WHERE cskSourceInstrumentInt = cpkInstrumentInt and colInstrumentNumber = 45
AND colSourceAmount > 0*/

---------------------------UPDATE FEE INFORMATION -----------------------------------------------
UPDATE fctStatementRun
SET cskStatementTransActionDate = cskSourceTransActionDate,
colStatementNetAmount = case when colSourceFeeNetAmount <> 0 then colSourceFeeNetAmount else
colSourceAmount end ,
colStatementAmount = colSourceAmount
Where colDataSourceItem = 'Fee'

--update fctStatementRun SET cskStatementTransActionDate = -1, colStatementNetAmount = 0, colStatementAmount = 0
/*UPDATE fctStatementRun
SET cskStatementTransActionDate = cskSourceTransActionDate,
colStatementNetAmount = case when colSourceFeeNetAmount <> 0 then colSourceFeeNetAmount else
colSourceAmount end ,
colStatementAmount = colSourceAmount
Where colDataSourceItem = 'Fee' AND cskStatementTransActionDate = -1*/

---------------------------UPDATE INCOME INFORMATION -----------------------------------------------
--KJA 20090608 effective date being overwritten, put into contractdate
UPDATE fctStatementRun
SET cskStatementTransActionDate = cskSourceTransActionDate,
colStatementAmount = colSourceAmount,
colStatementInitialFee = colSourceInitialFee,
cskContractTransActionDate = cskStatementTransActionDate
Where colDataSourceItem = 'Income Withdrawal'


------------------------------------ UNITISE RECORDS ----------------------------------
UPDATE dimInstrument
set colUnitised = CASE
WHEN colProcess = 'AU' THEN 0
WHEN colProcess = 'AT' THEN 1
WHEN colProcess = 'BJ' THEN 3
ELSE -1
END
from stgLispImsIAM..tblInstrumentTypeGroupConverted
where colInstrumentType_Code = colInstrumentType
and colProcess IN ('AT','AU', 'BJ')


--Unitise Units
UPDATE fctStatementRun
set colUnitisedUnits = case when colUnitised = 0 then 0 else colStatementUnits end
from dimInstrument
where cskStatementInstrumentInt = cpkInstrumentInt
and colUnitised in (0,1)

--Unitise Amount
UPDATE fctStatementRun
set colUnitisedAmount = case when colUnitised = 0 then colStatementAmount else 0 end
from dimInstrument
where cskStatementInstrumentInt = cpkInstrumentInt
and colUnitised in (0,1)


----------------------------------UPDATE PRICING -----------------------------
--Pricing - Price Date
UPDATE A
SET A.colPriceDate = B.colPriceDate
FROM fctStatementRun A
INNER JOIN
(
SELECT stg.cskStatementInstrumentInt, max(lkpPrice.colPriceDate) 'colPriceDate'
FROM fctStatementRun stg,
lkpPrice, dimTime, dimInstrument
where
stg.cskStatementInstrumentInt = cpkInstrumentInt
and dimInstrument.colInstrumentNumber = lkpPrice.colInstrumentNumber
and colPriceType = 'P'
and cskStatementTransactionDate= cpkTimeInt
and lkpPrice.colPriceDate <= colGregorianDate
--and Authorised = true
group by stg.cskStatementInstrumentInt
) B
ON A.cskStatementInstrumentInt = B.cskStatementInstrumentInt

--Pricing - Sell Price
UPDATE fctStatementRun
set fctStatementRun.colSellPrice = lkpPrice.colSellPrice
FROM lkpPrice, dimInstrument, dimTime
WHERE cskStatementInstrumentInt = cpkInstrumentInt
and dimInstrument.colInstrumentNumber = lkpPrice.colInstrumentNumber
and cskStatementTransActionDate = cpkTimeInt
and lkpPrice.colPriceDate = colGregorianDate
and colPriceType = 'P'
--and Authorised = true


/*
----------------------------------OUTPUT TEST ---------------------------------
select
cskContractInt, dimBroker.colPeopleNumber colBrokerNo, dimPrimaryMember.colPeopleNumber colPrimaryMember,
dimSecondaryMember.colPeopleNumber colSecondaryMember,
colContractNumber, colDealReference, colProductKey,
srcTrans.colTransactionNumber srccolTransactionNumber, destTrans.colTransactionNumber destcolTransactionNumber,
srcInst.colInstrumentNumber srccolInstrumentNumber, destInst.colInstrumentNumber destcolInstrumentNumber,
srcFlow.colFlowType srccolFlowType, destFlow.colFlowType destcolFlowType,
srcTime.colGregorianDate srccolTransActionDate, destTime.colGregorianDate destcolTransActionDate,
stmtTime.colGregorianDate stmtcolTransActionDate,
srcTransact.colTransactType srccolTransactType, destTransact.colTransactType destcolTransactType,
colSourceAmount, colDestinationAmount, colStatementAmount, colSourceUnits, colDestinationUnits, colStatementUnits,
colPriceDate, colSellPrice, colStatementInitialFee, colStatementNetAmount,
colStatementItem, colSplitItem, colContractItem, colDataSourceItem
from
fctStatementRun A, dimContract, dimDealLink, dimTransactionLink srcTrans, dimTransactionLink destTrans,
dimInstrument srcInst, dimInstrument destInst, dimFlow srcFlow, dimFlow destFlow,
dimTime srcTime, dimTime destTime, dimTime stmtTime,
dimPeople dimBroker, dimPeople dimPrimaryMember, dimPeople dimSecondaryMember,
dimTransact srcTransact, dimTransact destTransact, dimProduct
where cskContractint = cpkContractInt and colContractNumber = 10082
and cskDealLinkInt = cpkDealLinkInt
and cskBrokerInt = dimBroker.cpkPeopleInt
and cskPrimaryMemberInt = dimPrimaryMember.cpkPeopleInt
and cskSecondaryMemberInt = dimSecondaryMember.cpkPeopleInt
and cskSourceTransactionLinkInt = srcTrans.cpkTransactionLinkInt
and cskDestinationTransactionLinkInt = destTrans.cpkTransactionLinkInt
and cskSourceInstrumentInt = srcInst.cpkInstrumentInt
and cskDestinationInstrumentInt = destInst.cpkInstrumentInt
and cskSourceFlowInt = srcFlow.cpkFlowInt
and cskDestinationFlowInt = destFlow.cpkFlowInt
and cskSourceTransActionDate = srcTime.cpkTimeInt
and cskDestinationTransActionDate = destTime.cpkTimeInt
and cskStatementTransActionDate = stmtTime.cpkTimeInt
and cskSourceTransactInt = srcTransact.cpkTransactInt
and cskDestinationTransactInt = destTransact.cpkTransactInt
and cskProductInt = cpkProductInt
ORDER BY colContractNumber, colDealReference, colProductKey, srcTime.colGregorianDate, A.colTransactionNumber

*/

--select * from fctStatementRun, dimTime where colSplitItem = 'Interest Free Loan'
--and cskStatementTransActionDate = cpkTimeInt order by colGregorianDate
 

·
Registered
Joined
·
7,640 Posts
i hate code review
 

·
Registered
Joined
·
2,758 Posts
Tyre tread is a serious issue......

AR156... When is work ever that great to do...?

**** me.... But Hygrade looks real busy
 

·
Registered
Joined
·
7,640 Posts
you never ever write a no lock on a DM warehouse. your no lock will reside on your thin client but not yur statement extracts... get witht he now lol
 
G

·
Tyre tread is a serious issue......

AR156... When is work ever that great to do...?

**** me.... But Hygrade looks real busy
Tyre tread is only an issue if you see steel or thread, anything before that is called slicks!
Slicks are good for racing. Racing is what we want.
 

·
Registered
Joined
·
7,640 Posts
code is good woohooo... moving one.. 11 more scripts left
 
G

·
you never ever write a no lock on a DM warehouse. your no lock will reside on your thin client but not yur statement extracts... get witht he now lol
My bad :( , you are right of course. I hate datawarehouses, I hate cubes, I hate SSIS.

SQL is great though :cheese:
 

·
Registered
Joined
·
2,758 Posts
Tyre tread is only an issue if you see steel or thread, anything before that is called slicks!
Slicks are good for racing. Racing is what we want.
So in other words, all that steel wire sticking out my tyres is no good...? I thought thats what you guys meant by " My car sticks on the road like rails" you know what I mean... Steel rails...:lol:



I see your problem dude, line 123 should read:

stg.cskStatementInwhatthehellisthisstrumentInt = cpkInstrumentInt

Sorted.
:cheese: :thumbs:

I was busy getting my Binary book out but you beat me to it
 

·
Registered
Joined
·
7,640 Posts
My bad :( , you are right of course. I hate datawarehouses, I hate cubes, I hate SSIS.

SQL is great though :cheese:
This solution is right up your tree then


SSIS fires off scripts to update to stage, second leg of SSIS kicks in to transform from stage I to Stage S for data transformation. Then third container updates from stage S to DM fact and dim tables. fourth container execs cube build plugged in to DM. Then there is SSRS via an API and web call to generate statements. then a little C++ to render the output into filestore which is then published for review. after that there is another SSIS to call the same data off the cube and sent via flat file across FTP to a 3rd party vendor who then rendors and distributes. After the distribution another flat file is FTPd across back to us and we use another SSIS to bring control totals in. and there you go 88 000 statements in under 30 min.
 

·
Registered
Joined
·
665 Posts
I think you guys that understand all that gibberish code stuff should be in a movie..... like the Matrix... or maybe Revenge of the Nerds.... hahaha
 

·
Registered
Joined
·
7,640 Posts
hehehehe **** like this is what keeps your little world spinning

no code writers = no cell phones, no computers, no forums, no tv, no cars, no radio.... pretty much nothing electronic
 
1 - 20 of 140 Posts
Top