1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| INSERT INTO BasePamRecon ( PaNum, [Begin], Xbegin, [End], Xend, CpnType, BaseRate, XBaseRate, Rate, XRate, Cur, Xcur, Amount, Xamount )
SELECT DISTINCT
LV1FP901_ICPAM.PADLNO AS PaNum,
CDate([PASTMM] & "/" & [PASTDD] & "/" & [PASTYY]) AS [Begin],
No AS Xbegin,
IIf([cpntype]=70,IIf([PAMTMM]>12,CDate("01" & "/" & "01" & "/" & "2045"),CDate([PAMTMM] & "/" & [PAMTDD] & "/" & [PAMTYY])),
IIf([PAMTMM]>12,CDate("01" & "/" & "01" & "/" & "2045"),CDate([PAENMM] & "/" & [PAENDD] & "/" & [PAENYY]))) AS [End],
No AS Xend,
Chr([CpnType]) AS Expr1,
IIf([CPnTYPE]=86,[VrBase],IIf([cpntype]=65,"ADJ RATE","FIXED RATE")) AS BaseRate,
No AS XBaseRate,
IIf([mhfsttrn].[invmgr]="TR",Val(Left([PABASR],9))+(Val([VrAdjustment])/100),Val(Left([PABASR],9))) AS Rate,
No AS XRate,
LV1FP901_ICPAM.PACCY AS Cur,
No AS Xcur,
Val([PAPAMT]) AS Amount,
No AS Xamount
FROM (((LV1FP901_ICPAM INNER JOIN (mhfsttrn INNER JOIN mhfsisu ON mhfsttrn.Cusip = mhfsisu.Cusip) ON LV1FP901_ICPAM.PADLNO = mhfsttrn.TaNum) INNER JOIN mhfshdr ON mhfsttrn.Portfolio = mhfshdr.Portfolio) INNER JOIN MajorCodes ON mhfsisu.MajorType = MajorCodes.Code) INNER JOIN pmcshflw ON mhfsisu.CashFlowID = pmcshflw.CashFlowID
WHERE (((Right(Left([mhfshdr].[ShortName],1),3) & "000")<>1000) AND ((LV1FP901_ICPAM.PADLST)="PA") AND ((MajorCodes.TranType)="SEC"))
ORDER BY LV1FP901_ICPAM.PADLNO, Val([PAPAMT]); |
Partager