# Release 5.73 DB changes

Alter table [wh].[Warehouse]
Add [SubmittedOn] DATETIME  NULL;


Alter table [dbo].[LargeScaleProject]
DROP CONSTRAINT DF__LargeScal__IsOpe__45832891;

Alter table [dbo].[LargeScaleProject]
Drop Column [IsOperationsAndMaintenanceProject];


Alter table [dbo].[LargeScaleProject]
Add [WarehouseId] INT NULL;

Alter VIEW [dbo].[vProjectStageTransaction]
	AS

WITH _ProjectTransaction as (
    SELECT
    Id,
    DateCreated,
    MaterialListId,
    ProjectStageId,
    MaterialTransferId,
    MonitorClarified,
    VoidTransactionId,
    [Type],
    NULL AS StorageFacilityName,
    null as StorageFacilityId,
    Id as ProjectStageTransactionId,
    null as WarehouseId,
    Null as TransactionId
    FROM dbo.ProjectStageTransaction),
    _TransactionToStorageFacility as (
    SELECT
    t.Id,
    t.CreatedOn as DateCreated,
    t.MaterialListId,
    t.FromAccountId as ProjectStageId,
    null as MaterialTransferId,
    null as MonitorClarified,
    null as VoidTransactionId,
    3 as [Type], -- to storage facility transfer
    sf.Name AS StorageFacilityName,
    sf.Id as StorageFacilityId,
    null as ProjectStageTransactionId,
    null as WarehouseId,
    t.Id as TransactionId
    FROM tx.[Transaction] t
    INNER JOIN StorageFacility AS sf ON sf.Id = t.ToAccountId
    WHERE t.FromAccountType = 'ProjectStage' AND t.ToAccountType = 'StorageFacility'),
    _TransactionFromStorageFacility as (
    SELECT
    t.Id,
    t.CreatedOn as DateCreated,
    t.MaterialListId,
    t.ToAccountId as ProjectStageId,
    null as MaterialTransferId,
    null as MonitorClarified,
    null as VoidTransactionId,
    4 as [Type], -- from storage facility transfer
    sf.Name AS StorageFacilityName,
    sf.Id as StorageFacilityId,
    null as ProjectStageTransactionId,
    null as WarehouseId,
    t.Id as TransactionId
    FROM tx.[Transaction] t
    INNER JOIN StorageFacility AS sf ON sf.Id = t.FromAccountId
    WHERE t.ToAccountType = 'ProjectStage' AND t.FromAccountType = 'StorageFacility'),

    _TransactionFromMaterialTransferCoordinator as (
    SELECT
    t.Id,
    t.CreatedOn as DateCreated,
    t.MaterialListId,
    t.ToAccountId as ProjectStageId,
    null as MaterialTransferId,
    null as MonitorClarified,
    null as VoidTransactionId,
    5 as [Type], -- from material-transfer-coordinator
    null as StorageFacilityName,
    null as StorageFacilityId,
    null as ProjectStageTransactionId,
    null as WarehouseId,
    t.Id as TransactionId
    FROM tx.[Transaction] t
    WHERE t.ToAccountType = 'ProjectStage' AND t.FromAccountType = 'material-transfer-coordinator'),

    _TransactionToMaterialTransferCoordinator as (
    SELECT
    t.Id,
    t.CreatedOn as DateCreated,
    t.MaterialListId,
    t.FromAccountId as ProjectStageId,
    null as MaterialTransferId,
    null as MonitorClarified,
    null as VoidTransactionId,
    6 as [Type], -- to material-transfer-coordinator
    null AS StorageFacilityName,
    null as StorageFacilityId,
    null as ProjectStageTransactionId,
    null as WarehouseId,
    t.Id as TransactionId
    FROM tx.[Transaction] t
    WHERE t.FromAccountType = 'ProjectStage' AND t.ToAccountType = 'material-transfer-coordinator'),
    _TransactionToWarehouse as (
    SELECT
    t.Id,
    t.CreatedOn as DateCreated,
    t.MaterialListId,
    t.FromAccountId as ProjectStageId,
    null as MaterialTransferId,
    null as MonitorClarified,
    null as VoidTransactionId,
    12 as [Type], -- to Warehouse
    null AS StorageFacilityName,
    null as StorageFacilityId,
    null as ProjectStageTransactionId,
    t.ToAccountId as WarehouseId,
    t.Id as TransactionId
    FROM tx.[Transaction] t
    WHERE t.FromAccountType = 'ProjectStage' AND t.ToAccountType = 'Warehouse')
SELECT * FROM _ProjectTransaction t
UNION
SELECT * FROM _TransactionToStorageFacility
UNION
SELECT * FROM _TransactionFromStorageFacility
UNION
SELECT * FROM _TransactionFromMaterialTransferCoordinator
UNION
SELECT * FROM _TransactionToMaterialTransferCoordinator
UNION
SELECT * FROM _TransactionToWarehouse

Alter VIEW [dbo].[vProjectStageMaterialTransfer]
AS
    WITH _MaterialTransfer as (
    SELECT
    Id,
    DateCreated,
    CreatedByUserId,
    MaterialListId,
    FromProjectStageId,
    ToProjectStageId,
    FromVendorId,
    ToVendorId,
    null as FromStorageFacilityId,
    null as ToStorageFacilityId,
    null as ToWarehouseId,
    ProblemComments,
    DateReceived,
    [Status],
    CONVERT(BIT, 0) [IsNewTransferImplementation]
    FROM dbo.MaterialTransfer),
    _MaterialTransferRequestFromStage as (
    SELECT
    t.Id,
    t.DateCreated as DateCreated,
    CreatedByUserId,
    t.MaterialListId,
    t.FromAccountId as FromProjectStageId,
    null as ToProjectStageId,
    null as FromVendorId,
    null as ToVendorId,
    null as FromStorageFacilityId,
    CASE t.ToAccountType WHEN 'StorageFacility'
    THEN  t.ToAccountId else null END as ToStorageFacilityId,
    CASE t.ToAccountType WHEN 'Warehouse'
    THEN  t.ToAccountId else null END as ToWarehouseId,
    ProblemComments,
    DateReceived,
    [Status],
    CONVERT(BIT, 1) [IsNewTransferImplementation]
    FROM [tx].[MaterialTransferRequest] t
    WHERE t.FromAccountType = 'ProjectStage' AND (t.ToAccountType = 'StorageFacility' or t.ToAccountType = 'Warehouse')),
    _MaterialTransferRequestToStage as (
    SELECT
    t.Id,
    t.DateCreated as DateCreated,
    CreatedByUserId,
    t.MaterialListId,
    null as FromProjectStageId,
    t.ToAccountId as ToProjectStageId,
    null as FromVendorId,
    null as ToVendorId,
    t.FromAccountId as FromStorageFacilityId,
    null as ToStorageFacilityId,
    null as ToWarehouseId,
    ProblemComments,
    DateReceived,
    [Status],
    CONVERT(BIT, 1) [IsNewTransferImplementation]
    FROM [tx].[MaterialTransferRequest] t
    WHERE t.FromAccountType = 'StorageFacility' AND t.ToAccountType = 'ProjectStage')

SELECT * FROM _MaterialTransfer t
UNION
SELECT * FROM _MaterialTransferRequestFromStage
UNION
SELECT * FROM _MaterialTransferRequestToStage