CREATE VIEW [dbo].[RPMViewPriceSheet]
AS
SELECT
    ITEM.ITEMNMBR,
    PriceMethod = CASE ITEM.PRICMTHD
        WHEN 1 THEN 'Currency Amount'
        WHEN 2 THEN '% of List Price'
        WHEN 3 THEN '% Markup – Current Cost'
        WHEN 4 THEN '% Markup – Standard Cost'
        WHEN 5 THEN '% Margin – Current Cost'
        WHEN 6 THEN '% Margin – Standard Cost'
        ELSE 'OTHER' END,
    ITEMLISTPRICE.CURNCYID,
    --ITEMLISTPRICE.LISTPRCE, --THIS IS PROVIDED IN ITEM CURRENCIES, BUT IV00105 NEEDED TO JOIN CURRENCY INFO TO PRICE LISTS
    PRICEDETAIL.PRCLEVEL,
    PRICEDETAIL.UOFM,
    PRICEDETAIL.PRICE,
    PRICEDETAIL.QuantityRulesOption,
    PRICEDETAIL.QuantityBreaks,
    CUR.ISOCURRC
FROM IV00101 ITEM
LEFT JOIN IV00105 ITEMLISTPRICE
    ON ITEMLISTPRICE.ITEMNMBR = ITEM.ITEMNMBR
LEFT JOIN (
    SELECT 
        ITEMNMBR, 
        CURNCYID, 
        PRCLEVEL, 
        UOFM, 
        PRICE = MAX(UOMPRICE),
        QuantityRulesOption = CASE WHEN COUNT(*) > 1 THEN 'Volume Pricing' ELSE NULL END,
        QuantityBreaks = CASE 
            WHEN COUNT(*) > 1 THEN STUFF((
                SELECT '~' + CONVERT(VARCHAR(50), FROMQTY) + ':' + CONVERT(VARCHAR(50), UOMPRICE)
                FROM IV00108 AS sub
                WHERE sub.ITEMNMBR = IV.ITEMNMBR 
                  AND sub.CURNCYID = IV.CURNCYID 
                  AND sub.PRCLEVEL = IV.PRCLEVEL 
                  AND sub.UOFM = IV.UOFM
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
            ELSE NULL END
    FROM IV00108 IV
    GROUP BY ITEMNMBR, CURNCYID, PRCLEVEL, UOFM
) PRICEDETAIL
    ON PRICEDETAIL.ITEMNMBR = ITEMLISTPRICE.ITEMNMBR
    AND PRICEDETAIL.CURNCYID = ITEMLISTPRICE.CURNCYID
LEFT JOIN @SYSTEMDATABASE@.dbo.MC40200 CUR
    ON PRICEDETAIL.CURNCYID = CUR.CURNCYID
WHERE PRICE IS NOT NULL --ELIMINATES CURRENCIES THAT HAVE NO IV00108 RECORDS
