SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[CaSubmission]
    (
      @Pi_PlacementID int


    )
    as
    begin

    SET NOCOUNT ON;
    declare @submissionId int, @PrevSubmissionID int

    select top 1 @submissionId = SubmissionID
    from Submission where PlacementID = @Pi_PlacementID
    order by CreatedUTCDate desc

    select top 1 @PrevSubmissionID = SubmissionID
    from Submission
    where PlacementID = @Pi_PlacementID and Completed = 1
    order by CreatedUTCDate desc

        select
         P.PlacementID
        ,CC.CarrierName as MarketName
        ,CC.MarketMatchCarrierID as CarrierID
        , case when isnull(C.IsPanel,0) = 0 then 0
               when C.IsPanel = 1 then 1
           end as CarrierType
        ,case when C.IsPanel = 1 then C.CarrierName  else null end as PanelMember
        ,case when C.IsPanel = 1 then C.MarketMatchCarrierID else null end  as PanelMemberID
        ,PC.PlacementCarrierID
        ,case when C.IsPanel = 1 then isnull(CS.[RequestedCommission],CS.SuggestedCommision) else CS.[RequestedCommission] end  as Commission
        ,REPLACE(CONVERT(NVARCHAR,CS.DueDate, 106), ' ', '-') as NeedBy
        ,CR.CommissionReasonID
        ,CR.CommissionReason
        ,CS.SubmissionMessage
        --,isnull(S.SubmissionName,cast((case when len(Datepart(HH,GETUTCDATE()))>1 then Datepart(HH,GETUTCDATE()) else '0'+ cast((Datepart(HH,GETUTCDATE())) as nvarchar) end) as nvarchar)+':'
                    --              +cast(Datepart(MINUTE,GETUTCDATE()) as nvarchar)+' '+Replace(CONVERT(varchar,GetUTCDATE(),106),' ','-')+' GMT') as SubmissionVersion
        ,case when S.Completed = 1 then S.SubmissionName else null end as SubmissionVersion
        ,CD.CarrierEmail as CarrierEmail
        ,case when C.IsPanel = 1 then CS.SuggestedCommision else null end SuggestedCommision
        ,s.SubmissionID,
        cs.CarrierSubmissionID,
        S.Completed,
        RS.ResponseStatusID as StatusID,
        RS.ResponseStatus as StatusName,
        case when exists(select 1 from Quote Q where Q.PlacementCarrierID = PC.PlacementCarrierID  and Q.QuoteStatusID in (1,2)) then 'true' else 'false' end as IsBound,
        case when CS.IsConfirmed = 1 then 'true' else 'false' end IsConfirmed
    from
     --dbo.Placement Placement
    dbo.PlacementCarrier PC
    inner join dbo.Carrier as C on c.CarrierID = PC.CarrierID --and PC.PlacementID is null
    inner join dbo.Placement as P on P.PlacementID= PC.PlacementID
    inner join CarrierSubmission CS on PC.PlacementCarrierID = CS.PlacementCarrierID
    inner join [dbo].[Submission] as S on CS.SubmissionID = S.SubmissionID
    left join [dbo].[Carrier] as CC on case when C.IsPanel = 1 then C.ParentCarrierID else C.CarrierID end = CC.CarrierID--and isnull(CC.IsPanel,0) = 1

    left join [dbo].[CommissionReason] as CR on CS.CommissionReasonID = CR.CommissionReasonID
    left join [dbo].[ResponseStatus] as RS on CS.ResponseStatusID = RS.ResponseStatusID
    Left join (select max(carrieremail) as carrieremail,
              carrierid from carrierdetail
             group by carrierid) CD
    on C.CarrierID = CD.CarrierID
    where  PC.PlacementID=@Pi_PlacementID
    --and C.IsFacility=0 and isnull(C.IsPanel,0)=0
    and S.SubmissionID= @submissionId
    and PC.IsSelected=1
    and (case when C.IsPanel = 1 and C.ParentCarrierID is null then 0 else 1 end) = 1
    --and s.Completed =0
        order by Case when C.IsPanel = 1 then 1 else 2
           end  ,
           MarketName,
           PanelMember,
           PC.PlacementCarrierID,
           C.CarrierID

         -----------------

        select
        isnull(PC.PlacementCarrierId,T.PlacementCarrierID) as PlacementCarrierId,
        --T.PlacementCarrierID as PlacementCarrierId,
        S.SubmissionName as SubmissionVersion,
        D.DocumentId,
        D.DocumentName,
        D.DocumentumId,
        D.PlacementId,
        D.DocumentTypeId,
        D.CreatedUser,
        D.LastUpdatedUTCDate  as LastUpdateDateTime,
        D.DocumentFolderId,
        DF.DocumentFolderName,
        DF.ParentFolderID,
        DF.Level,
        DF.IsCommon,
        DV.DocumentVersion,
        isnull(FT.FileType,'') FileContentType,
        FT.FileExtension,
        'File' as Typedesc,
        case when S.Completed = 0 then Convert(nvarchar,T.LastUpdatedUTCDate,112)  else NULL end as DOCLastUpdatedDate,
        case when exists(select 1 from CarrierSubmissionDocument CSD where CSD.SubmissionDocumentID = sd.SubmissionDocumentID and CSD.CarrierSubmissionID = CS.CarrierSubmissionID) then 'true' else 'false' end
            'IsCurrentVersion'

        from submission s
        inner join SubmissionDocument sd on s.SubmissionID = sd.SubmissionID
       join DocumentVersion as DV on SD.DocumentVersionId = DV.DocumentVersionID
       join Document as D on DV.DocumentId = D.DocumentId
    --left join CarrierSubmission cs on S.SubmissionID = CS.SubmissionID --csd.CarrierSubmissionID = cs.CarrierSubmissionID

        left join DocumentFolder DF on D.DocumentFolderId = DF.DocumentFolderId
        Left join FileType FT on D.FileTypeID = FT.FileTypeID
        --left join CarrierSubmissionDocument as CSD on SD.SubmissionDocumentID = CSD.SubmissionDocumentID
        left join CarrierSubmission CS on S.SubmissionID = CS.SubmissionID
        left join PlacementCarrier PC on CS.PlacementCarrierID = PC.PlacementCarrierID
        left join
        (
            select PlacementCarrierID,DocumentumID ,LastUpdatedUTCDate
            from
            (select cs.PlacementCarrierID, D.DocumentumID , CSD.LastUpdatedUTCDate,s.SubmissionID , ROW_NUMBER() over(partition by cs.PlacementCarrierID,D.DocumentumID  order by  CSD.LastUpdatedUTCDate desc) 'RowNum'
            from Submission as S
            join carrierSubmission cs on S.SubmissionID = CS.SubmissionID
            join SubmissionDocument as SD on S.SubmissionID = SD.SubmissionID
            join DocumentVersion as DV on SD.DocumentVersionID = DV.DocumentVersionID
            join Document as D on DV.DocumentID = D.DocumentID
            join CarrierSubmissionDocument as CSD on SD.SubmissionDocumentID = CSD.SubmissionDocumentID and CS.CarrierSubmissionID = CSD.CarrierSubmissionID
            where-- D.DocumentumID = @DocumentumId
            -- S.SubmissionID = @PrevSubmissionID
               --and
               S.PlacementID = @Pi_PlacementID and S.Completed = 1 and S.SubmissionID <> @submissionId
            )A where RowNum = 1
        )T  on  (D.DocumentumID = T.DocumentumID or T.DocumentumID is null ) and (PC.PlacementCarrierID = T.PlacementCarrierID or (PC.PlacementCarrierID is NULL or T.PlacementCarrierID is null))
        --left join DocumentVersion DV
        --on D.DocumentumID = DV.DocumentumID
        where D.PlacementId = @Pi_PlacementID
        and S.SubmissionID= @submissionId
        and (T.PlacementCarrierID is not null or PC.PlacementCarrierID is not NULL)
        --and PC.IsSelected=1
        and not exists(select 1 from DocumentType where DocumentTypeID = D.DocumentTypeID and DocumentType = 'Declined to Quote')
        order by sd.SubmissionDocumentID desc


select s.SubmissionID,s.SubmissionName as SubmissionVersion from dbo.submission S
where s.placementid = @Pi_PlacementID
and S.SubmissionID <> @submissionId
and s.Completed = 1
order by CreatedUTCDate desc
end