IF EXISTS
(
    SELECT *
    FROM sys.views
    WHERE
        object_id = OBJECT_ID(N'[AKUZ].[V_GROUPED_BILL_OF_GOODS_WITH_PRICE]')
)
    DROP VIEW AKUZ.V_GROUPED_BILL_OF_GOODS_WITH_PRICE
GO
CREATE VIEW AKUZ.V_GROUPED_BILL_OF_GOODS_WITH_PRICE
AS
SELECT NEWID()            AS GroupedBillOfGoodsWithPrice,
       bg.CTrn            AS CTrn,
       trn.CMnn           AS CMnn,
       bg.MedicationForms AS MedicationForms,
       dv.Value           AS DoseView,
       bg.IsVital         AS IsVital,
       MAX(ce.Price)      AS Price,
       t.LpuMain          AS LpuMain
FROM STOR.T_CONTRACT_ELEMENTS ce
JOIN PHARM.T_BILL_OF_GOODS bg ON bg.BillOfGoodsID = ce.BillOfGoods
JOIN PHARM.T_C_TRN trn ON trn.CTrnID = bg.CTrn
LEFT JOIN PHARm.T_C_MNN mnn ON mnn.CMnnID = trn.CMnn
LEFT JOIN PHARM.S_DLO_DoseView dv ON dv.Id = bg.DoseView
OUTER APPLY (
    SELECT TOP 1 lpu.LpuMain,
                 cst.ContractStorageLinkID
    FROM STOR.T_CONTRACT_STORAGE_LINK cst
    JOIN STOR.T_SUPPLIER_CONTRACT supplierContract ON cst.SupplierContract = supplierContract.SupplierContractID
    JOIN STOR.T_STORAGE s ON s.StorageID = cst.Storage
    JOIN AKUZ.T_LPU_ISSUE_PLACE lpu ON lpu.IssuePlaceID = s.IssuePlace
    WHERE
          cst.SupplierContract = ce.SupplierContract
      AND supplierContract.type_ =
          (
              SELECT TOP 1 ENTITYID
              FROM VCLIB.T_ENTITIES
              WHERE
                    ENTNAME = 'SUPPLIER_CONTRACT'
                AND [Schema] = 'STOR'
          )
            ) t
WHERE
     t.LpuMain IS NOT NULL
  OR t.ContractStorageLinkID IS NULL
GROUP BY
    bg.CTrn,
    trn.CMnn,
    bg.MedicationForms,
    dv.Value,
    bg.IsVital,
    t.LpuMain
GO
