CREATE VIEW qrPaymentDetail1 AS

SELECT 

    det.iPaymentNomer, 
    det.iPaymentPartNomer, 
    prt.dtPaymentPartDate,
    det.iLANomer,
    det.iLASubNomer,
    sCurrencySymbol = CASE
        WHEN la.sCurrencySymbol = 'DEM' AND 
             prt.dtPaymentPartDate > CAST('2000/12/31' AS smalldatetime) 
        THEN 'EUR'
        ELSE la.sCurrencySymbol
    END,
    dbLAAmountMIO = CAST(
        CASE
            WHEN la.sCurrencySymbol = 'DEM' AND 
                 prt.dtPaymentPartDate > CAST('2000/12/31' AS smalldatetime) 
            THEN ROUND(la.dbLAamountMIO/ 1 . 95583 , 2 )
            ELSE la.dbLAAmountMIO
        END 
        AS NUMERIC( 6 , 2 )
    ),
    dbLAAmount = CAST(
        CASE
            WHEN la.sCurrencySymbol = 'DEM' AND 
                 prt.dtPaymentPartDate > CAST('2000/12/31' AS smalldatetime) 
            THEN ROUND(la.dbLAamount/ 1 . 95583 , 2 )
            ELSE la.dbLAAmount
        END 
        AS NUMERIC( 12 , 2 )
    ),

    sLAOriginalCurrencySymbol = la.sCurrencySymbol,
    dbLAOriginalAmountMIO = la.dbLAAmountMIO,
    dbLAOriginalAmount = la.dbLAAmount,

    la.dtLADate,
    la.sLAStatus,
    la.sLAType,
    sBankAgentOrCreditor,
    dbPrincipal, 
    pm.dbPaymentAmountUSDVal
    
FROM 

    tb_PAYMENT_Detail det
    INNER JOIN tb_PAYMENT_Part prt
        ON prt.iPaymentNomer = det.iPaymentNomer 
           AND prt.iPaymentPartNomer = det.iPaymentPartNomer
    INNER  JOIN tb_PAYMENT_Main pm
        ON pm.iPaymentNomer = det.iPaymentNomer
    INNER JOIN tb_PAYMENT_Loan la 
        ON det.iLANomer = la.iLANomer

CREATE VIEW qrPaymentDetail2 AS

SELECT 

    det1.*,
    dbRate_0 = cur0.dbCurrencyRateUSD,
    dbRate_1 = cur1.dbCurrencyRateUSD,
    dbRate_2 = ISNULL(cur2.dbRate_2, cur1.dbCurrencyRateUSD),
    dbPrincipalUSD = CAST(dbPrincipal/cur0.dbCurrencyRateUSD AS MONEY)

FROM 

    qrPaymentDetail1 det1
    INNER JOIN tb_PAYMENT_Currency cur0 
        ON  cur0.sCurrencySymbol   = det1.sCurrencySymbol
        AND cur0.iPaymentNomer     = det1.iPaymentNomer
        AND cur0.iPaymentPartNomer =  0 
    INNER JOIN tb_PAYMENT_Currency cur1 
        ON  cur1.sCurrencySymbol   = det1.sCurrencySymbol
        AND cur1.iPaymentNomer     = det1.iPaymentNomer
        AND cur1.iPaymentPartNomer = det1.iPaymentPartNomer
    LEFT JOIN tb_PAYMENT_LoanCurrency cur2 
        ON  cur2.sCurrencySymbol   = det1.sCurrencySymbol
        AND cur2.iPaymentNomer     = det1.iPaymentNomer
        AND cur2.iPaymentPartNomer = det1.iPaymentPartNomer
        AND cur2.iLANomer          = det1.iLANomer

CREATE VIEW qrPaymentDetail AS 

SELECT 

    det2.*, 
    sm.dbPrincipalUSDSum,
    dbPrincipalPercent = 
        CAST(det2.dbPrincipalUSD AS NUMERIC( 24 , 12 )) / 
        CAST(sm.dbPrincipalUSDSum AS NUMERIC( 24 , 12 )), 
    dbPaymentAmountUSD = CAST(
        CAST(det2.dbPaymentAmountUSDVal AS NUMERIC( 24 , 12 )) * 
        (
            CAST(det2.dbPrincipalUSD  AS NUMERIC( 24 , 12 )) / 
            CAST(sm.dbPrincipalUSDSum  AS NUMERIC( 24 , 12 ))
        )
    AS MONEY),
    dbPaymentAmount = CASE det2.iPaymentNomer 
        WHEN  1  THEN 
            CAST(
                ROUND(
                    (
                        CAST(det2.dbPaymentAmountUSDVal AS NUMERIC( 24 , 12 )) * 
                        (
                            CAST(det2.dbPrincipalUSD  AS NUMERIC( 24 , 12 )) / 
                            CAST(sm.dbPrincipalUSDSum  AS NUMERIC( 24 , 12 ))
                        )
                    ) * dbRate_2,
                     0 )
            AS MONEY)
        ELSE
            CAST(
                (
                    CAST(det2.dbPaymentAmountUSDVal AS NUMERIC( 24 , 12 )) * 
                    (
                        CAST(det2.dbPrincipalUSD  AS NUMERIC( 24 , 12 )) / 
                        CAST(sm.dbPrincipalUSDSum  AS NUMERIC( 24 , 12 ))
                    )
                ) * dbRate_2
            AS MONEY)
        END

FROM 

    qrPaymentDetail2 det2
    INNER JOIN sumPaymentUSD sm 
    ON sm.iPaymentNomer = det2.iPaymentNomer