ALTER PROCEDURE xspStyleReconciliationGetList
(
    @guid xdtLongName,
    @fromPage xdtName,
    @pageParams xdtUniCodeMaxText='',
    @searchParams xdtUniCodeMaxText='',
    @sortParams xdtUniCodeLongText='',
    @pageIndex smallInt=1,
    @pageSize Int=20,
    @errorMsg VARCHAR(1000)='' OUTPUT
)
AS
BEGIN
    DECLARE @companyCode xdtCompanyCode,@memberCompanyCode xdtCompanyCode,@companyDivisionCode xdtCompanyCode,@userName xdtName
    EXEC xspGetSessionVariable  @guid=@guid ,@companyCode =@companyCode  OUTPUT,@membercompanyCode =@membercompanyCode  OUTPUT,
    @companyDivisionCode =@companyDivisionCode  OUTPUT, @userName =@userName OUTPUT

    DECLARE  @StyleCode xdtLongCode, @StyleName xdtText, @BuyerStyleRef xdtName, @StyleClosedDate xdtName
        , @StyleClosedDateFrom xdtDate, @StyleClosedDateTo xdtDate, @ClosedBy xdtUniCodeLongText
        , @LastETDDate xdtName, @LastETDDateFrom xdtDate, @LastETDDateTo xdtDate, @TranNum xdtName, @CalledFrom xdtName
        , @Buyer xdtName, @DivisionManager xdtUnicodeLongText, @Manager xdtUnicodeLongText, @Status xdtName
        , @IsExportToExcel xdtTinyNum

    IF (ISNULL(@searchParams,'') <>'')
    BEGIN
        CREATE TABLE #SearchParam(ParamName Nvarchar(1000),ParamValue Nvarchar(max))
        EXEC dbo.xspGetTableFromString @details =@searchParams,@rowSeprator ='~',@columnSeperator ='|',@tableName ='#SearchParam'
        SELECT @StyleCode=ParamValue From #SearchParam Where ParamName='colStyleCode'
        SELECT @StyleName=ParamValue From #SearchParam Where ParamName='colStyleName'
        SELECT @BuyerStyleRef=ParamValue From #SearchParam Where ParamName='colBuyerStyleRef'
        SELECT @StyleClosedDate=ParamValue From #SearchParam Where ParamName='colClosedDate'
        SELECT @LastETDDate=ParamValue From #SearchParam Where ParamName='colLastETDDate'
        SELECT @ClosedBy=ParamValue From #SearchParam Where ParamName='colClosedBy'
        SELECT @TranNum=ParamValue From #SearchParam Where ParamName='colTranNum'
        SELECT @Status = ParamValue From #SearchParam Where ParamName='colStatus'
        SELECT @Buyer = ParamValue From #SearchParam Where ParamName='colBuyer'

        IF ISNULL(@StyleClosedDate,'')=''
        BEGIN
            SELECT @StyleClosedDateFrom=paramvalue from #SearchParam where ParamName='colClosedDateFrom';
            SELECT @StyleClosedDateTo=paramvalue from #SearchParam where paramname='colClosedDateTo'
        END
        ELSE
        EXEC xspGetDateRange @param=@StyleClosedDate, @fromDate=@StyleClosedDateFrom output, @toDate=@StyleClosedDateTo output, @errorMsg=@errorMsg output

        IF ISNULL(@LastETDDate,'')=''
        BEGIN
            SELECT @LastETDDateFrom=paramvalue from #SearchParam where ParamName='colLastETDDateFrom';
            SELECT @LastETDDateTo=paramvalue from #SearchParam where paramname='colLastETDDateTo'
        END
        ELSE
        EXEC xspGetDateRange @param=@LastETDDate, @fromDate=@LastETDDateFrom output, @toDate=@LastETDDateTo output, @errorMsg=@errorMsg output
    END

    if ISNULL(@pageParams,'') <>''
    BEGIN
        CREATE TABLE #PageParam(ParamName varchar(1000),ParamValue varchar(max))
        EXEC dbo.xspGetTableFromString @Details =@pageParams,@RowSeprator ='~',@ColumnSeperator ='|',@TableName ='#PageParam'
        SELECT @fromPage=ParamValue FROM #PageParam where ParamName='FromPage'
        SELECT @CalledFrom=ParamValue FROM #PageParam where ParamName='CalledFrom'
        select @IsExportToExcel=ParamValue FROM #PageParam where ParamName='IsExportToExcel'
    END

    CREATE TABLE #PendingStyleReconciledData(
        [StyleReconciliationID] xdtId, [BuyerCompanyName] xdtUnicodeText, [DivisionManager] xdtUniCodeLongText NULL,
        [Manager] xdtUniCodeLongText NULL, [Style] xdtText NULL, [StyleCode]  xdtText NULL, [StyleName]  xdtText NULL,
        [BuyerStyleRefNo]  xdtName NULL, [LastETDDate] xdtName NULL, [GMPOETDDate] xdtName NULL, [StyleClosedDate] xdtName NULL,
        [TranNum] xdtName NULL, [ClosedBy] xdtLongName NULL, [Status] xdtName NULL, [IsStyleReconciled] xdtbit NULL,
        [SupplierShipmentCode] xdtInt NULL
    )

    IF (@CalledFrom='PendingStyleReconciliationList')
    BEGIN
        /*Start<IssueID:112364>*/
        INSERT INTO #PendingStyleReconciledData(StyleReconciliationID, StyleCode, StyleName, BuyerStyleRefNo, BuyerCompanyName, DivisionManager, Manager
            , LastETDDate,GMPOETDDate, ClosedBy, Status, TranNum,IsStyleReconciled,StyleClosedDate,SupplierShipmentCode)
        SELECT st.id , st.ArticleCode, st.ArticleName, st.BuyerReference, buyer.CompanyName , ''[DivisionManager], ''[Manager],
            ''[LastETDDate],NULL , ''[ClosedBy],
            CASE WHEN (IsNull(st.IsRMReco,0)= 1 OR IsNull(st.IsShipmentDelayRemarks,0)= 1 OR IsNull(st.IsGarmentQtyReco,0)= 1) THEN 'Part Reco'
            ELSE '' END,'',st.IsStyleReconciled,
            Convert(varchar(11),st.LastChanged,106), 0 SupplierShipmentCode
        From xtArticle st(nolock)
        JOIN xtCompany buyer(NOLOCK) on buyer.MemberCompanyCode=st.Buyer
        where Not Exists(Select top 1 1 from xtArticle art
                Where art.ID=st.ID and Isnull(art.IsStyleReconciled,0) = 1 And IsNull(art.ReconTranNum,'')<>''
                    And IsNull(art.IsShipmentDelayRemarks,0)=1 And IsNull(art.IsGarmentQtyReco,0)=1 And IsNull(art.IsRMReco,0)=1)

		AND EXISTS(
			SELECT TOP  1 1  FROM  xtOrder ord
			JOIN xtOrderShipment os ON os.OrderCode = ord.OrderCode and os.IsLastVersion =1 and os.Confirmed=1
			WHERE ord.OCType<>-1 and ord.IsLastVersion = 1 And ord.OrderType<>6 and ord.StyleID = st.ID
		)
		AND NOT EXISTS  (
			SELECT TOP  1 1  FROM  xtOrder ord
			JOIN xtOrderShipment os ON os.OrderCode = ord.OrderCode and os.IsLastVersion =1 and os.Confirmed=1
			LEFT JOIN xtGMPOActualConsASN asn (NOLOCK) ON asn.SupplierShipmentCode=os.GMPOASNSupplierShipmentCode
				AND asn.ShipmentRevisedCode = os.ShipmentRevisedCode
			WHERE ord.OCType<>-1 and ord.IsLastVersion = 1 And ord.OrderType<>6 and ord.StyleID = st.ID and asn.GMPOActualConsASNID IS NULL
		)

        AND (IsNull(@StyleCode,'')='' OR st.ArticleCode LIKE '%' + @StyleCode + '%')
        AND (IsNull(@StyleName,'')='' OR st.ArticleName LIKE '%' + @StyleName + '%')
        AND (IsNull(@BuyerStyleRef,'')='' OR st.BuyerReference like '%' + @BuyerStyleRef + '%')
        AND (IsNull(@Buyer,'')='' OR Buyer.CompanyName like '%' + @Buyer + '%')
        Order By st.ID Desc
        /*End<IssueID:112364>*/
    END
    Else IF (@CalledFrom='StyleReconciliationList')
    BEGIN
        /*Start<IssueID:112364>*/
        INSERT INTO #PendingStyleReconciledData(StyleReconciliationID, StyleCode, StyleName, BuyerStyleRefNo, BuyerCompanyName, DivisionManager, Manager
            , LastETDDate,GMPOETDDate, ClosedBy, Status, TranNum,IsStyleReconciled,StyleClosedDate,SupplierShipmentCode)
        SELECT st.ID , st.ArticleCode, st.ArticleName, st.BuyerReference, buyer.CompanyName , ''[DivisionManager], ''[Manager],
            ''[LastETDDate],NULL , us.FullName [ClosedBy],'',st.ReconTranNum,st.IsStyleReconciled,Convert(varchar(11),st.LastChanged,106), 0 SupplierShipmentCode
        FROM xtArticle st(NOLOCK)
        JOIN xtCompany buyer(NOLOCK) on buyer.MemberCompanyCode=st.Buyer
        JOIN xtUser us(NOLOCK) on us.UserName=st.CreatedBy
        where IsStyleReconciled = 1 And ReconTranNum Is Not NULL
            AND (IsNull(@StyleCode,'')='' OR st.ArticleCode LIKE '%' + @StyleCode + '%')
            AND (IsNull(@StyleName,'')='' OR st.ArticleName LIKE '%' + @StyleName + '%')
            AND (IsNull(@BuyerStyleRef,'')='' OR st.BuyerReference like '%' + @BuyerStyleRef + '%')
            AND (IsNull(@Buyer,'')='' OR Buyer.CompanyName like '%' + @Buyer + '%')
            AND (@StyleClosedDateFrom IS NULL OR (st.LastChanged) > = @StyleClosedDateFrom)
            AND (@StyleClosedDateTo IS NULL OR (st.LastChanged) < = @StyleClosedDateTo)
            AND (IsNull(@ClosedBy,'')='' OR us.FullName like '%' + @ClosedBy + '%')
            AND (IsNull(@TranNum,'')='' OR st.ReconTranNum like '%' + @TranNum + '%')
        /*End<IssueID:112364>*/
    END

    UPDATE g SET g.DivisionManager=u.FullName,g.Manager = u1.FullName
    from  #PendingStyleReconciledData  g
    JOIN xtOrder a(NOLOCK)  on a.StyleID = g.StyleReconciliationID  and a.OrderCode>0  and a.OCType<>-1 and a.IsLastVersion=1
        and a.ID =(select Min(ID) from xtOrder  where StyleID = a.StyleID and OrderCode>0  and OCType<>-1 and IsLastVersion=1)
    JOIN xtUser u(NOLOCK) On u.UserName = a.DivisionManagerUserName
    JOIN xtUser u1(NOLOCK) On u1.UserName = a.ManagerUserName

    Select  g.StyleReconciliationID, max(dbo.xfGetETDDate(b.ShipmentMode,b.AWBETD,b.SubETD,b.BLMotherETD))  LastETDDate, b.SupplierShipmentCode
    INTO #ShipmentETDDate
    from  #PendingStyleReconciledData  g
    Join xtOrder a(NOLOCK)  on a.StyleID = g.StyleReconciliationID  and a.OrderCode>0  and a.OCType<>-1 and a.IsLastVersion=1
    Join xtOrderShipment  b on b.CompanyCode = a.CompanyCode AND b.OrderCode=a.OrderCode and b.IsLastVersion  = 1
    GROUP BY g.StyleReconciliationID, b.SupplierShipmentCode

    UPDATE a SET a.LastETDDate = Convert(varchar(11),b.LastETDDate,106), a.GMPOETDDate = Convert(varchar(11),b.LastETDDate,106),
    a.SupplierShipmentCode = b.SupplierShipmentCode
    From #PendingStyleReconciledData a
    JOIN #ShipmentETDDate b On a.StyleReconciliationID = b.StyleReconciliationID

    --UPDATE a set a.ClosedBy = c.FullName
    --From #PendingStyleReconciledData a
    --JOIN xtArticleRC b (NOLOCK) ON b.StyleID = a.StyleReconciliationID
    --JOIN xtUser c (NOLOCK) ON c.UserName = b.CreatedBy

    SELECT * INTO #GMPOData1
    FROM #PendingStyleReconciledData
    Where (IsNull(@DivisionManager,'')='' OR DivisionManager like '%' + @DivisionManager + '%')
        AND (IsNull(@Manager,'')='' OR Manager like '%' + @Manager + '%')

    SELECT * INTO #GMPOData
    FROM #GMPOData1
    Where (IsNull(@LastETDDateFrom ,'')='' OR (LastETDDate) > = @LastETDDateFrom)
        AND (IsNull(@LastETDDateTo ,'')='' OR (LastETDDate) < = @LastETDDateTo)
        And (@Status Is NULL OR Status LIKE '%' + @Status + '%')
       -- and (@searchParams <> '' OR ISNULL(@LastETDDate, '') <> '' OR (ISNULL(@LastETDDateFrom, '') = '' AND ISNULL(@LastETDDateTo, '') = ''
		--		AND DATEDIFF(M, LastETDDate, GETDate()) <=2 ))

    If ISNULL(@sortParams,'') =''
        set @sortParams = 'colStyleReconciliationID|Desc'

    Select * into #GMPODataFinal From #GMPOData where 1=2

    IF @IsExportToExcel = '1'
    BEGIN
        SELECT @pageIndex = 1, @pageSize = COUNT(1) FROM #GMPOData
    END
    Execute xspGetPageData @sourceTable = '#GMPOData',@sortParams=@sortParams,@pageIndex=@pageIndex, @pageSize=@pageSize,@errorMsg=@errorMsg output

    SELECT  * FROM #GMPODataFinal Order By StyleReconciliationID Desc
    SELECT Count(1) RecordCount FROM [#GMPODataFinal]
END