Simplify a complex query

Select Case len(snxxxxxxxx)  When 1 Then '000' +  snxxxxxxxx  
                              When 2 Then '00'  +  snxxxxxxxx 
                              When 3 Then '0'   +  snxxxxxxxx 
                              Else snxxxxxxxx
                              End  As snxxxxxxxx,
                              mfpidxxxxxxxx,
                              FPIDxxxxxxxxx,
                              MFPSxxxxxxxx,
                              ANxxxxxxx,
                              ADxxxxxxxxxxx,
                              ASDxxxxhxhxxxx,
                              ASNxxxolnsdf,                                                                                                           
                              Case len(Cast(APxxxxlkjbluhvkugvy As Varchar(3)))  
                              When 1 Then '00' +  Cast(APxxxxlkjbluhvkugvy As Varchar(3))  
                              When 2 Then '0'  +  Cast(APxxxxlkjbluhvkugvy As Varchar(3))                               
                              Else Cast(APxxxxlkjbluhvkugvy As Varchar(3)) 
                              End  As 
                              APxxxxlkjbluhvkugvy,
                              AIIDxxxxxlkhluyfv,
                              PBIDxxxxxkjbsjfsdhbd,
                              PPIDxxxx;jobsdlugvsjhvbs,
                              NOPxxxxjksvulhvsdlhvbs,                            
                              ISxxxxjhasgvukgvsjhvbshk,
                              MAIDxxxklbsuvshvbsljkn,
                              VPOIDxxxxljhbsulhvsjksbls,
                              GOLTIDxxxxlknkljbnjkvhhf,
                              LTSxxxjhgcaycsjhskljhliygv,
                              POIGIDxxxxjlhvbsjlhvbsuhvsssss,
                              POGSxxxxkhbshgcshgvsjhvbskbjs,
                              GPOIDxxxxxjhvasysykgcskgvsykgs,
                              PLxxxxjhgvbasjvsjgvsjvs,
                              SLxxxxrfgycashcshgvshgvjhsg,
                              SSLxxxxlihbdluhvbdlhvbdlhbd,
                              SELxxxxtyackkuvsykgcskjm,
                              IPXxxxxyfgcyyywgijsnlkjnsksj,
                              IPYxxxxycfsghsgysysgbsjsjsklsm,
                              COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc  AS MSxxxxjhsgvshsuksoihstrs                           
FROM
(
            Select snxxxxxxxx,mfpidxxxxxxxx,FPIDxxxxxxxxx,MFPSxxxxxxxx,ADxxxxxxxxxxx,ASDxxxxhxhxxxx,ANxxxxxxx,ASNxxxolnsdf,Row_Number ()  Over( Partition By snxxxxxxxx,FPIDxxxxxxxxx,ANxxxxxxx,ASNxxxolnsdf 
            order by snxxxxxxxx,FPIDxxxxxxxxx,ANxxxxxxx,ASNxxxolnsdf,MerchAreaINxxxxihsbkuhsgvysdjsuihsgvblihb ASC,COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc ASC)   As   APxxxxlkjbluhvkugvy,AIIDxxxxxlkhluyfv,PBIDxxxxxkjbsjfsdhbd,PPIDxxxx;jobsdlugvsjhvbs, NOPxxxxjksvulhvsdlhvbs,ISxxxxjhasgvukgvsjhvbshk,
            MAIDxxxklbsuvshvbsljkn,VPOIDxxxxljhbsulhvsjksbls,GOLTIDxxxxlknkljbnjkvhhf,LTSxxxjhgcaycsjhskljhliygv,POIGIDxxxxjlhvbsjlhvbsuhvsssss,POGSxxxxkhbshgcshgvsjhvbskbjs,COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc,
                                        GPOIDxxxxxjhvasysykgcskgvsykgs,PLxxxxjhgvbasjvsjgvsjvs,SLxxxxrfgycashcshgvshgvjhsg,SSLxxxxlihbdluhvbdlhvbdlhbd,SELxxxxtyackkuvsykgcskjm,IPXxxxxyfgcyyywgijsnlkjnsksj,IPYxxxxycfsghsgysysgbsjsjsklsm
 
                            FROM                
                            (       
                                SELECT  snxxxxxxxx,FPIDxxxxxxxxx,mfpidxxxxxxxx,MFPSxxxxxxxx,ANxxxxxxx,ADxxxxxxxxxxx,ASDxxxxhxhxxxx,
                                                CASE ASNxxxolnsdf 
                                                WHEN 999  THEN                                                  
                                                    CASE ASDxxxxhxhxxxx WHEN 'NO AISLE'    THEN 1 
                                                                              WHEN 'NO FIXTURE'  THEN 2 
                                                         ELSE 2 
                                                    END     
                                                ELSE    ASNxxxolnsdf
                                                END AS ASNxxxolnsdf,
                                        APxxxxlkjbluhvkugvy,MerchAreaINxxxxihsbkuhsgvysdjsuihsgvblihb,MAIDxxxklbsuvshvbsljkn,COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc,VPOIDxxxxljhbsulhvsjksbls,GOLTIDxxxxlknkljbnjkvhhf,LTSxxxjhgcaycsjhskljhliygv,POIGIDxxxxjlhvbsjlhvbsuhvsssss,POGSxxxxkhbshgcshgvsjhvbskbjs,
                                        GPOIDxxxxxjhvasysykgcskgvsykgs,PLxxxxjhgvbasjvsjgvsjvs,SLxxxxrfgycashcshgvshgvjhsg,SSLxxxxlihbdluhvbdlhvbdlhbd,SELxxxxtyackkuvsykgcskjm,IPXxxxxyfgcyyywgijsnlkjnsksj,IPYxxxxycfsghsgysysgbsjsjsklsm,PPIDxxxx;jobsdlugvsjhvbs,
                                        PBIDxxxxxkjbsjfsdhbd, ISNULL(NOPxxxxjksvulhvsdlhvbs,1)  AS NOPxxxxjksvulhvsdlhvbs,ISNULL(ISxxxxjhasgvukgvsjhvbshk,0)  AS ISxxxxjhasgvukgvsjhvbshk,AIIDxxxxxlkhluyfv
                                    FROM 
                                    (
                                    SELECT      dbo.tblStore.SCxxxxrtfdctfgghghhhh AS snxxxxxxxx, 
                                                dbo.tblFloorPlan.FPIDxxxxxxxxx, 
                                                dbo.tblMasterFloorPlan.mfpidxxxxxxxx, 
                                                dbo.tblMasterFloorPlan.MFPSxxxxxxxxID, 
                                                dbo.tblStatus.Status AS MFPSxxxxxxxx, 
                                                ISNULL(dbo.tblAisleInstance.ANxxxxxxx, 999) AS ANxxxxxxx, 
                                                LTRIM(RTRIM(ISNULL(dbo.tblAisleInstance.Description, 'NA'))) AS ADxxxxxxxxxxx, 
                                                CASE LTRIM(RTRIM(ISNULL(dbo.tblAisleInstance.ASDxxxxhxhxxxx, ''))) 
                                                          WHEN '' THEN 
                                                          CASE WHEN FixtureCount = 0 OR FixtureCount IS NULL THEN 'NO FIXTURE' 
                                                               WHEN FixtureCount > 0 THEN 'NO AISLE' 
                                                          END ELSE 
                                                          dbo.tblAisleInstance.ASDxxxxhxhxxxx 
                                                END AS ASDxxxxhxhxxxx, 
                                                ISNULL(dbo.tblAisleInstance.ASNxxxolnsdf, 999) AS ASNxxxolnsdf, 
                                              dbo.tblAisleInstance.AIIDxxxxxlkhluyfv  AS AIIDxxxxxlkhluyfv,     
                                              dbo.tblMerchandisableAreaPosition.MAPDxxxgshsilskosyhsfsjk AS APxxxxlkjbluhvkugvy, 
                                              dbo.tblVaultMerchandisableArea.INxxxxihsbkuhsgvysdjsuihsgvblihb AS MerchAreaINxxxxihsbkuhsgvysdjsuihsgvblihb, 
                                              dbo.tblVaultMerchandisableArea.VaultMAIDxxxklbsuvshvbsljkn AS MAIDxxxklbsuvshvbsljkn,
                                              dbo.tblVaultMerchandisableAreaDetail.COISNxxxxyhsgshsiosksl;sjshskssskugvxfjc, 
                                              dbo.tblVaultPlanObject.VPOIDxxxxljhbsulhvsjksbls, 
                                              CASE  
                                                        WHEN dbo.tblVaultPlanObject.GOLTIDxxxxlknkljbnjkvhhf IS NULL THEN MissingLifeTimes.MissingGOLTIDxxxxlknkljbnjkvhhf
                                                        WHEN dbo.tblVaultPlanObject.GOLTIDxxxxlknkljbnjkvhhf = 0 THEN MissingLifeTimes.MissingGOLTIDxxxxlknkljbnjkvhhf
                                                        ELSE dbo.tblVaultPlanObject.GOLTIDxxxxlknkljbnjkvhhf END 
                                              AS GOLTIDxxxxlknkljbnjkvhhf,                                                      
                                              CASE 
                                                        WHEN tblLTSxxxjhgcaycsjhskljhliygv.STATUS IS NULL THEN  MissingLifeTimes.MissingLTSxxxjhgcaycsjhskljhliygv
                                                        ELSE tblLTSxxxjhgcaycsjhskljhliygv.STATUS END                                                                                                               
                                                 AS LTSxxxjhgcaycsjhskljhliygv,
                                              dbo.tblVaultPlanObject.POIGIDxxxxjlhvbsjlhvbsuhvsssss, dbo.tblVaultPlanObject.POGSxxxxkhbshgcshgvsjhvbskbjs, 
                                              dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs, 
                                              dbo.tblVaultPlanObject.ActualLength / 2.54 AS PLxxxxjhgvbasjvsjgvsjvs, 
                                              dbo.tblVaultPlanObject.SLxxxxrfgycashcshgvshgvjhsg / 2.54 AS SLxxxxrfgycashcshgvshgvjhsg, 
                                              dbo.tblVaultPlanObject.SSLxxxxlihbdluhvbdlhvbdlhbd / 2.54 AS SSLxxxxlihbdluhvbdlhvbdlhbd, dbo.tblVaultPlanObject.SELxxxxtyackkuvsykgcskjm / 2.54 AS SELxxxxtyackkuvsykgcskjm, 
                                              dbo.tblVaultPlanObject.IPXxxxxyfgcyyywgijsnlkjnsksj, dbo.tblVaultPlanObject.IPYxxxxycfsghsgysysgbsjsjsklsm, dbo.tblGlobalPlanObject.User_int_2 AS PPIDxxxx;jobsdlugvsjhvbs, 
                                              dbo.tblGlobalPlanObject.User_int_1 AS PBIDxxxxxkjbsjfsdhbd,
                                              NOPxxxxjksvulhvsdlhvbs,
                                              ISxxxxjhasgvukgvsjhvbshk,
                                              ROW_NUMBER()OVER ( PARTITION  BY dbo.tblVaultPlanObject.POIGIDxxxxjlhvbsjlhvbsuhvsssss  ORDER BY dbo.tblVaultPlanObject.VPOIDxxxxljhbsulhvsjksbls  ASC)  AS SplitNumber                                         
                                    FROM      dbo.tblStoreCategoryHierarchy AS tblStoreCategoryHierarchy_1 WITH (NOLOCK) INNER JOIN
                                              dbo.tblMasterFloorPlan WITH (NOLOCK) INNER JOIN
                                              dbo.tblFloorPlan WITH (NOLOCK) INNER JOIN
                                              dbo.tblFloorPlanAnalysisDetail WITH (NOLOCK) ON dbo.tblFloorPlan.FPIDxxxxxxxxx = dbo.tblFloorPlanAnalysisDetail.FloorPlanInstanceID INNER JOIN
                                              dbo.tblFloorPlanAnalysisHeader WITH (NOLOCK) ON dbo.tblFloorPlanAnalysisDetail.FloorPlanAnalysisID = dbo.tblFloorPlanAnalysisHeader.FloorPlanAnalysisID INNER JOIN
                                              dbo.tblVaultPlanObject WITH (NOLOCK) ON dbo.tblFloorPlan.FPIDxxxxxxxxx = dbo.tblVaultPlanObject.FPIDxxxxxxxxx INNER JOIN
                                              dbo.tblVaultMerchandisableAreaDetail WITH (NOLOCK) ON dbo.tblVaultPlanObject.VPOIDxxxxljhbsulhvsjksbls = dbo.tblVaultMerchandisableAreaDetail.VaultChildObjectID INNER JOIN
                                              dbo.tblGlobalPlanObject WITH (NOLOCK) ON dbo.tblVaultPlanObject.PlanObjectGlobalID = dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs INNER JOIN
                                              dbo.tblMerchandisableAreaPosition WITH (NOLOCK) ON 
                                              dbo.tblVaultMerchandisableAreaDetail.MerchandisableAreaPositionID = dbo.tblMerchandisableAreaPosition.MerchandisableAreaPositionID INNER JOIN
                                              dbo.tblVaultMerchandisableArea WITH (NOLOCK) ON 
                                              dbo.tblVaultMerchandisableAreaDetail.VaultMAIDxxxklbsuvshvbsljkn = dbo.tblVaultMerchandisableArea.VaultMAIDxxxklbsuvshvbsljkn ON 
                                              dbo.tblMasterFloorPlan.mfpidxxxxxxxx = dbo.tblFloorPlan.mfpidxxxxxxxx INNER JOIN
                                              dbo.tblStore WITH (NOLOCK) INNER JOIN
                                              dbo.tblStoreProject WITH (NOLOCK) ON dbo.tblStore.StoreID = dbo.tblStoreProject.StoreID ON dbo.tblMasterFloorPlan.StoreProjectID = dbo.tblStoreProject.StoreProjectID INNER JOIN
                                              dbo.tblStatus WITH (NOLOCK) ON dbo.tblMasterFloorPlan.MFPSxxxxxxxxID = dbo.tblStatus.StatusID INNER JOIN
                                              dbo.tblStoreCategoryHierarchyDetail WITH (NOLOCK) ON dbo.tblStore.StoreID = dbo.tblStoreCategoryHierarchyDetail.StoreID ON 
                                              tblStoreCategoryHierarchy_1.StoreCategoryHierarchyID = dbo.tblStoreCategoryHierarchyDetail.StorecategoryHierarchyID INNER JOIN
                                              dbo.tblRetailer WITH (NOLOCK) INNER JOIN
                                              dbo.tblStoreCategoryHierarchy WITH (NOLOCK) ON dbo.tblRetailer.RetailerID = dbo.tblStoreCategoryHierarchy.RetailerID ON 
                                              tblStoreCategoryHierarchy_1.ParentCategoryHierarchyID = dbo.tblStoreCategoryHierarchy.StoreCategoryHierarchyID LEFT OUTER JOIN
                                              dbo.tblVaultAisleMerchandisableAreaInstance WITH (NOLOCK) LEFT OUTER JOIN
                                              dbo.tblAisleInstance WITH (NOLOCK) ON dbo.tblVaultAisleMerchandisableAreaInstance.AIIDxxxxxlkhluyfv = dbo.tblAisleInstance.AIIDxxxxxlkhluyfv ON 
                                              dbo.tblVaultMerchandisableArea.VaultMAIDxxxklbsuvshvbsljkn = dbo.tblVaultAisleMerchandisableAreaInstance.VaultMAIDxxxklbsuvshvbsljkn 
                                              LEFT JOIN tblGlobalObjectLifeTime ON dbo.tblVaultPlanObject.GOLTIDxxxxlknkljbnjkvhhf =  tblGlobalObjectLifeTime.GOLTIDxxxxlknkljbnjkvhhf
                                              LEFT JOIN tblStatus AS tblLTSxxxjhgcaycsjhskljhliygv  ON dbo.tblGlobalObjectLifeTime.GlobalObjectStatusID = tblLTSxxxjhgcaycsjhskljhliygv.StatusID                                              
                                              LEFT OUTER JOIN
                                                  (
                                                                SELECT     tblVaultPlanObjectFixtureSequence.VPOIDxxxxljhbsulhvsjksbls, 
                                                                           COUNT(dbo.tblVaultPlanObjectFixtureSequence.VaultFixtureID) AS FixtureCount
                                                                FROM       dbo.tblVaultPlanObjectFixtureSequence WITH (NOLOCK)
                                                                GROUP BY tblVaultPlanObjectFixtureSequence.VPOIDxxxxljhbsulhvsjksbls
                                                  )   AS POGFixtures ON dbo.tblVaultPlanObject.VPOIDxxxxljhbsulhvsjksbls = POGFixtures.VPOIDxxxxljhbsulhvsjksbls
                                                    INNER JOIN 
                                                    (
                                                                Select FPIDxxxxxxxxx  
                                                                From 
                                                                (
                                                                                    SELECT     dbo.tblMasterFloorPlan.mfpidxxxxxxxx, dbo.tblFloorPlan.FPIDxxxxxxxxx, Row_Number () OVER(Partition By  dbo.tblMasterFloorPlan.mfpidxxxxxxxx order by  dbo.tblFloorPlan.FPIDxxxxxxxxx DESC)   As RowNumber  
                                                                                    FROM       dbo.tblFloorPlan WITH (NOLOCK) INNER JOIN
                                                                                               dbo.tblFloorPlanAnalysisDetail WITH (NOLOCK) ON dbo.tblFloorPlan.FPIDxxxxxxxxx = dbo.tblFloorPlanAnalysisDetail.FloorPlanInstanceID INNER JOIN
                                                                                               dbo.tblFloorPlanAnalysisHeader WITH (NOLOCK) ON dbo.tblFloorPlanAnalysisDetail.FloorPlanAnalysisID = dbo.tblFloorPlanAnalysisHeader.FloorPlanAnalysisID INNER JOIN
                                                                                               dbo.tblMasterFloorPlan WITH (NOLOCK) ON dbo.tblFloorPlan.mfpidxxxxxxxx = dbo.tblMasterFloorPlan.mfpidxxxxxxxx
                                                                                    WHERE     (dbo.tblFloorPlanAnalysisHeader.IsVault = 1) 
                                                                                    AND       (dbo.tblMasterFloorPlan.MFPSxxxxxxxxID = 38) 
                                                                                    AND       (dbo.tblFloorPlan.FloorPlanStatusID = 38)
                                                                )  As LIVEFloorPlans 
                                                                   Where RowNumber = 1 
                                                    )  As LIVEFloorPlans ON LIVEFloorPlans.FPIDxxxxxxxxx  =  tblFloorPlan.FPIDxxxxxxxxx
 
                                                     LEFT OUTER JOIN 
 
                                                     (
 
                                                        SELECT      dbo.tblVaultPlanObject.FPIDxxxxxxxxx,PlanObjectGlobalID,COUNT(DISTINCT POIGIDxxxxjlhvbsjlhvbsuhvsssss) As NOPxxxxjksvulhvsdlhvbs
                                                        FROM        dbo.tblVaultPlanObject  WITH (NOLOCK) INNER JOIN dbo.tblFloorPlan WITH (NOLOCK)
                                                        ON          dbo.tblVaultPlanObject.FPIDxxxxxxxxx = dbo.tblFloorPlan.FPIDxxxxxxxxx
                                                        WHERE       (dbo.tblVaultPlanObject.PlanObjectGlobalID > 0) AND (dbo.tblFloorPlan.FloorPlanStatusID = 38)
                                                        GROUP BY    dbo.tblVaultPlanObject.FPIDxxxxxxxxx,PlanObjectGlobalID
                                                        HAVING      (COUNT(DISTINCT POIGIDxxxxjlhvbsjlhvbsuhvsssss) > 1)
 
                                                     )  As View_NOPxxxxjksvulhvsdlhvbs ON  dbo.tblVaultPlanObject.PlanObjectGlobalID =  View_NOPxxxxjksvulhvsdlhvbs.PlanObjectGlobalID
                                                             AND  dbo.tblVaultPlanObject.FPIDxxxxxxxxx  = View_NOPxxxxjksvulhvsdlhvbs.FPIDxxxxxxxxx                                                                                                                 
                                                    LEFT OUTER JOIN                                                     
                                                    (                                                   
                                                            SELECT       POIGIDxxxxjlhvbsjlhvbsuhvsssss,1 AS ISxxxxjhasgvukgvsjhvbshk
                                                            FROM         dbo.tblVaultPlanObject WITH (NOLOCK) INNER JOIN dbo.tblFloorPlan WITH (NOLOCK)
                                                            ON           dbo.tblVaultPlanObject.FPIDxxxxxxxxx = dbo.tblFloorPlan.FPIDxxxxxxxxx
                                                            WHERE       (dbo.tblVaultPlanObject.PlanObjectGlobalID > 0) AND (dbo.tblFloorPlan.FloorPlanStatusID = 38)
                                                            GROUP BY     POIGIDxxxxjlhvbsjlhvbsuhvsssss
                                                            HAVING      (COUNT(DISTINCT VPOIDxxxxljhbsulhvsjksbls) > 1)                                                                                                     
                                                    ) As View_SplitPlanograms ON  dbo.tblVaultPlanObject.POIGIDxxxxjlhvbsjlhvbsuhvsssss =  View_SplitPlanograms.POIGIDxxxxjlhvbsjlhvbsuhvsssss                                                  
                                                    INNER JOIN                                                  
                                                    (                                               
                                                        SELECT GPOIDxxxxxjhvasysykgcskgvsykgs,GOLTIDxxxxlknkljbnjkvhhf AS MissingGOLTIDxxxxlknkljbnjkvhhf,LTSxxxjhgcaycsjhskljhliygv AS MissingLTSxxxjhgcaycsjhskljhliygv
                                                        FROM 
                                                        (
                                                        SELECT GPOIDxxxxxjhvasysykgcskgvsykgs,GOLTIDxxxxlknkljbnjkvhhf,LTSxxxjhgcaycsjhskljhliygv,ROW_NUMBER()  OVER ( PARTITION  BY GPOIDxxxxxjhvasysykgcskgvsykgs ORDER BY StatusPriority ASC)   AS RowNumber 
                                                        FROM 
                                                        (
                                                        SELECT     dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs, dbo.tblGlobalObjectLifeTime.GOLTIDxxxxlknkljbnjkvhhf,tblStatus.Status  AS LTSxxxjhgcaycsjhskljhliygv , 
                                                        CASE dbo.tblGlobalObjectLifeTime.GlobalObjectStatusID  
                                                           WHEN 20  THEN 1 
                                                           WHEN 25  THEN 2 
                                                           WHEN 24 THEN 3 ELSE 4 END 
                                                           StatusPriority 
                                                        FROM         dbo.tblGlobalPlanObject WITH (NOLOCK) LEFT OUTER JOIN
                                                        dbo.tblGlobalObjectLifeTime WITH (NOLOCK) ON dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs = dbo.tblGlobalObjectLifeTime.GlobalObjectID  
                                                        INNER JOIN dbo.tblStatus  WITH (NOLOCK) ON dbo.tblGlobalObjectLifeTime.GlobalObjectStatusID = dbo.tblStatus.StatusID                 
                                                        ) AS SelSet 
                                                        ) AS FinalSet
                                                        WHERE RowNumber = 1
                                                    ) AS MissingLifeTimes ON dbo.tblGlobalPlanObject.GPOIDxxxxxjhvasysykgcskgvsykgs  =  MissingLifeTimes.GPOIDxxxxxjhvasysykgcskgvsykgs
                                    WHERE   (dbo.tblFloorPlanAnalysisHeader.IsVault = 1) 
                                        AND (dbo.tblVaultMerchandisableAreaDetail.ChildObjectTypeID = 60) 
                                        AND (dbo.tblMasterFloorPlan.MFPSxxxxxxxxID = 38) 
                                        AND (UPPER(LTRIM(RTRIM(dbo.tblRetailer.RetailerDescription))) = 'BI-LO') 
                                        AND (dbo.tblVaultPlanObject.PlanObjectGlobalID > 0)                                                                                                 
                                    ) AS InnerSet  
                        Where SplitNumber  = 1                                    
            ) As SelectionSet           
) As FinalSet