CREATE VIEW [dbo].[v_pivot]
AS

WITH ctePivotQuoteP 
AS (
    SELECT *
    FROM (
            SELECT QuoteType
                , CodQuote
                , QuoteRev
                , Descr
                , Parameter
            FROM dbo.QuoteParam
        ) AS SourceTable

    PIVOT(MIN(Parameter) FOR Descr IN (
          ItemDescr
        , CodifProfilo
        , ClasseMerc
        , CodClientAssoc
        , ClientNameAssoc
        , CodDiesGroup
        , TipoDies
        , DiamDies
        , HoleNumDies
        , NumDiesInList
        , NumDiesExaust
        , CodPlant
        , KeyAM
        , KeyAMIL
        , NumSMED
        , KgTeo
        , KgProdotti
        , QuotaMan
        , DiesPrice
        , DiesExpLife
        , DiesEOLife
		, DiesByClass
        , PkgDesc
        , PRMWeight
        , PWeight
        , PTare
        , TareStRatio
        , LivPackage
        , LivPackageDett
		-- START nuovi campi x SIM
        , DiesPrice_sim
        , SamplePrice
        , DiesFullCost
        , DiesPriceClientQuote
        , DiesTecLife
        , DiesCommLife
        , MonthSalesPrev
        , SalesPrevCost
        , DiesFinCost
        , ClientQuoteReven
        , ExtraDiscountDies
        , WSR01_sim
        , MSR01_sim
        , NetProd01_sim
		, DSR_sim
		-- end nuovi campi x SIM
        )
  ) AS PivotData
),
-- =================================================
-- PIVOT TABELLA QuoteOC
-- =================================================
ctePivotQuoteOC AS (   
    SELECT *
    FROM (
            SELECT    QuoteType
                    , CodQuote
                    , QuoteRev
                    , Class01 + ProcessNum AS ClassProc
                    , C4UG
            FROM dbo.QuoteOC
            ) AS SourceTable
    PIVOT(SUM(C4UG) FOR ClassProc IN 
              (   EE01
                , GAS01
                , MATRICE01
                , VENDITA01
                , ROTTAME01
                , VENDITA04
                , ROTTAME04
                , IMBALLO04
                , TRASPORTI04
                , PROVVIGIONI04
              ) 
       ) AS PivotData
)
-- =================================================
-- MAIN Select Tabella con INTO temp table
-- =================================================
SELECT 'V14.1' AS Vers
    , QL.QuoteType
    , QL.CodQuote
    , QL.QuoteRev
    , QL.IdxQState
    , QL.CodClient
    , CD.ClientName
    , QL.CodItem
    , QL.CodItemGroup
    , QL.RawMat
    , QL.UnitWeight
    , QL.BatchQty
    , QL.CodInco
    , CD.TranspZone
    , QL.KeyAM
    , QL.Note
    , QL.PriceOff
    , QL.OrdDate
    , QL.OrdNum
    , QL.OrdRow
    , QL.OrdPrice
    , QL.OrdQty
    , pQP.CodClientAssoc
    , pQP.ClientNameAssoc
    , pQP.ItemDescr
    , pQP.CodifProfilo
    , pQP.ClasseMerc
    , pQP.TipoDies
    , pQP.CodDiesGroup
    , CONVERT(DECIMAL(18,6),pQP.DiamDies)               AS DiamDies
    , CONVERT(INT,pQP.HoleNumDies)                      AS HoleNumDies
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesPrice),0)    AS DiesPrice
    , CONVERT(DECIMAL(18,6),pQP.DiesExpLife)            AS DiesExpLife
    , CONVERT(DECIMAL(18,6),pQP.DiesEOLife)             AS DiesEOLife
    , CONVERT(BIT, ISNULL(pQP.DiesByClass,0))           AS DiesByClass
    , CONVERT(DECIMAL(18,6),pQP.NumDiesInList)          AS NumDiesInList
    , CONVERT(DECIMAL(18,6),pQP.NumDiesExaust)          AS NumDiesExaust
    , pQP.CodPlant
    , pQP.KeyAMIL
    , CONVERT(DECIMAL(18,6),pQP.KgTeo)                  AS KgTeo
    , CONVERT(DECIMAL(18,6),pQP.NumSMED)                AS NumSMED
    , CONVERT(DECIMAL(18,6),pQP.QuotaMan)               AS QuotaMan
    , CONVERT(DECIMAL(18,6),pQP.PRMWeight)              AS PRMWeight
    , CONVERT(DECIMAL(18,6),pQP.PWeight)                AS PWeight
    , CONVERT(DECIMAL(18,6),pQP.PTare)                  AS PTare
    , pQP.LivPackage
    , pQP.LivPackageDett
	-- START nuovi campi x SIM
	, ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesPrice_sim),0)            AS DiesPrice_sim
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.SamplePrice),0)              AS SamplePrice
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesFullCost),0)             AS DiesFullCost
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesPriceClientQuote),0)     AS DiesPriceClientQuote
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesTecLife),0)              AS DiesTecLife
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesCommLife),1)             AS DiesCommLife
    , ISNULL(CONVERT(INT,pQP.MonthSalesPrev),0)                     AS MonthSalesPrev
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.SalesPrevCost),0)            AS SalesPrevCost
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.DiesFinCost),0)              AS DiesFinCost
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.ClientQuoteReven),0)         AS ClientQuoteReven
    , ISNULL(CONVERT(DECIMAL(18,6),pQP.ExtraDiscountDies),0)        AS ExtraDiscountDies
    , ISNULL(CONVERT(DECIMAL(9,8),pQP.WSR01_sim),0)                 AS WSR01_sim
    , ISNULL(CONVERT(DECIMAL(9,8),pQP.MSR01_sim),0)                 AS MSR01_sim
    , ISNULL(CONVERT(DECIMAL(9,3),pQP.NetProd01_sim),0)             AS NetProd01_sim
	, ISNULL(CONVERT(DECIMAL(9,8),pQP.DSR_sim),0)                  AS DSR_sim
	-- end nuovi campi x SIM
    , QRM.RawMatCost
    , QRM.RawMatExtraCost
    , QRM.RawMatCost + QRM.RawMatExtraCost AS RawMatFullCost -- fare campo calcolato ?!?
    , QRM.CodPackag
    , pQP.PkgDesc
    , QRM.FSR
    , ISNULL(pQOC.PROVVIGIONI04, 0) AS ProvvCost
    , ISNULL(pQOC.EE01, 0)          AS EECost
    , ISNULL(pQOC.GAS01, 0)         AS GasCost
    , ISNULL(pQOC.MATRICE01, 0)     AS DiesCost
    , ISNULL(pQOC.IMBALLO04, 0)     AS PackCost
    , ISNULL(pQOC.TRASPORTI04, 0)   AS TranspCost
    , ISNULL(pQOC.VENDITA01, 0)     AS SellCost01
    , ISNULL(pQOC.ROTTAME01, 0)     AS RefCost01
    , ISNULL(pQOC.VENDITA04, 0)     AS SellCost04
    , ISNULL(pQOC.ROTTAME04, 0)     AS RefCost04
    , QWI01.WSR                     AS WSR01
    , QWI01.MSR                     AS MSR01
    , QWI01.NetProd                 AS NetProd01
    , QWI01.FC4UG                   AS FC4UG01
    , QWI01.VC4UG                   AS VC4UG01
    , QWI01.OH4UG                   AS OH4UG01
    , QWI01.SC4UG                   AS SC4UG01
    , 1 - (QWI01.DYR * QWI02.DYR * QWI03.DYR * QWI04.DYR) AS DSR
    , QWI04.NetProd                 AS NetProd04
    , QWI04.FC4UG                   AS FC4UG04
    , QWI04.VC4UG                   AS VC4UG04
    , ISNULL(pQOC.VENDITA01, 0) + ISNULL(pQOC.ROTTAME01, 0) + ISNULL(pQOC.VENDITA04, 0) + ISNULL(pQOC.ROTTAME04, 0) + ISNULL(pQOC.EE01, 0) + ISNULL(pQOC.GAS01, 0) + ISNULL(pQOC.MATRICE01, 0) + ISNULL(pQOC.IMBALLO04, 0) + ISNULL(pQOC.PROVVIGIONI04, 0) + ISNULL(pQOC.TRASPORTI04, 0) + QWI01.VC4UG + QWI04.VC4UG + QWI01.SC4UG AS FVC
    , PD.Charge
    , QL.valid
    , QL.errorScore

FROM dbo.QuoteList AS QL

INNER JOIN dbo.ClientDet CD
    ON QL.CodClient = CD.CodClient

INNER JOIN ctePivotQuoteP AS pQP
    ON      QL.QuoteType = pQP.QuoteType
        AND QL.CodQuote  = pQP.CodQuote
        AND QL.QuoteRev  = pQP.QuoteRev

INNER JOIN dbo.QuoteRM AS QRM
    ON      QL.QuoteType = QRM.QuoteType
        AND QL.CodQuote  = QRM.CodQuote
        AND QL.QuoteRev  = QRM.QuoteRev
        AND QL.rawmat    = QRM.RawMat

INNER JOIN  ctePivotQuoteOC AS pQOC
        ON      QL.QuoteType = pQOC.QuoteType
            AND QL.CodQuote  = pQOC.CodQuote
            AND QL.QuoteRev  = pQOC.QuoteRev

INNER JOIN dbo.QuoteWorkInt AS QWI01
    ON      QL.QuoteType = QWI01.QuoteType
        AND QL.CodQuote  = QWI01.CodQuote
        AND QL.QuoteRev  = QWI01.QuoteRev
        AND QWI01.ProcessNum = '01'
INNER JOIN dbo.QuoteWorkInt AS QWI02
    ON      QL.QuoteType = QWI02.QuoteType
        AND QL.CodQuote  = QWI02.CodQuote
        AND QL.QuoteRev  = QWI02.QuoteRev
        AND QWI02.ProcessNum = '02'
INNER JOIN dbo.QuoteWorkInt AS QWI03
    ON      QL.QuoteType = QWI03.QuoteType
        AND QL.CodQuote  = QWI03.CodQuote
        AND QL.QuoteRev  = QWI03.QuoteRev
        AND QWI03.ProcessNum = '03'
INNER JOIN dbo.QuoteWorkInt AS QWI04
    ON      QL.QuoteType = QWI04.QuoteType
        AND QL.CodQuote  = QWI04.CodQuote
        AND QL.QuoteRev  = QWI04.QuoteRev
        AND QWI04.ProcessNum = '04'
INNER JOIN dbo.PlantsDet AS PD
    ON PD.CodPlant = pQP.CodPlant
GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 1, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_pivot';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_pivot';