SELECT DISTINCT SM.TRIP_ID                                                                AS TripID,
                        X.LEGACY_ID                                                               AS UserLoadNumber,
                        T.RTG_TP                                                                  AS RoutingTypeId,
                        ST.ZONE_ID                                                                AS ZoneStopId,
                        ST.STP_ID                                                                 AS StopID,
                        ST.SEQ_NBR                                                                AS StopSequence,
                        SM.STPMST_SEQ                                                             AS ZoneStopSequence,
                        DESTA.NM                                                                  AS DestinationName,
                        DESTA.ADDR_ID                                                             AS DestinationAddressId,
                        DESTA.ADDR                                                                AS DestinationAddressLine1,
                        DESTA.ADDR2                                                               AS DestinationAddressLine2,
                        DESTA.CTY                                                                 AS DestinationCity,
                        DESTA.ST                                                                  AS DestinationState,
                        DESTA.PSTL_CD                                                             AS DestinationPostalCode,
                        SM.CAR_ID                                                                 AS CarrierId,
                        C.NM                                                                      AS CarrierName,
                        ST.APPT_NBR                                                               AS AppointmentNumber,
                        SM.STPMST_TP                                                              AS StopMstTypeId,
                        SE.ITEM_ID                                                                AS ShipmentId,
                        SH.DEST_ID                                                                AS ShipmentDestinationShipLocId,
                        SL.NM                                                                     AS ShipmentDestinationShipLocName,
                        SLA.ADDR_ID                                                               AS ShipmentDestinationAddressId,
                        SLA.NM                                                                    AS ShipmentDestinationAddressName,
                        SLA.ADDR                                                                  AS ShipmentDestAddrLine1,
                        SLA.ADDR2                                                                 AS ShipmentDestAddrLine2,
                        SLA.CTY                                                                   AS ShipmentDestAddrCity,
                        SLA.ST                                                                    AS ShipmentDestAddrState,
                        SLA.PSTL_CD                                                               AS ShipDestAddrPostalCode,
                        CASE
                          WHEN BCD.IMBC IS NULL
                          THEN BCD.BARCD
                          ELSE BCD.IMBC
                        END                                                                       AS BarcodeId,
                        CASE
                          WHEN BCD.IMBC IS NULL
                          THEN BCD.DISPLAY_BARCD
                          ELSE BCD.IMBC
                        END                                                                       AS DisplayBarcodeId,
                        CASE
                          WHEN SH.SHPMNT_ID IN (SELECT SE2.ITEM_ID
                                                  FROM QUAD0083..STOPMST SM2
                                                       LEFT JOIN QUAD0083..STOPS ST2
                                                              ON ST2.STPMST_ID = SM2.STPMST_ID
                                                       LEFT JOIN QUAD0083..STOP_EVENTS SE2
                                                              ON ST2.STP_ID = SE2.STP_ID
                                                 WHERE SM2.STPMST_TP = 2)
                               AND SM.STPMST_TP = 1
                          THEN 'Cover Manifest'
                          ELSE 'Regular Manifest'
                        END                                                                       AS ManifestType,
                        CASE
                          WHEN SH.SHPMNT_ID IN (SELECT SE2.ITEM_ID
                                                  FROM QUAD0083..STOPMST SM2
                                                       LEFT JOIN QUAD0083..STOPS ST2
                                                              ON ST2.STPMST_ID = SM2.STPMST_ID
                                                       LEFT JOIN QUAD0083..STOP_EVENTS SE2
                                                              ON ST2.STP_ID = SE2.STP_ID
                                                 WHERE SM2.STPMST_TP = 2)
                               AND SH.SHPMNT_ID IN (SELECT SE2.ITEM_ID
                                                      FROM QUAD0083..STOPMST SM2
                                                           LEFT JOIN QUAD0083..STOPS ST2
                                                                  ON ST2.STPMST_ID = SM2.STPMST_ID
                                                           LEFT JOIN QUAD0083..STOP_EVENTS SE2
                                                                  ON ST2.STP_ID = SE2.STP_ID
                                                     WHERE SM2.STPMST_TP = 1
                                                       AND SE2.EVENT_TP_ID = 2)
                               AND SM.STPMST_TP = 1
                               AND DESTA.NM IS NOT NULL
                               AND DESTA.ADDR IS NOT NULL
                          THEN 0
                          ELSE 1
                        END                                                                       AS ShowConsolidatedProductSection,
                        CASE
                          WHEN SHD.FRGT_CLS_ID = 1
                               AND BCD.MISSING != 0
                               AND SHDIA_ENTRY.ID IS NOT NULL
                          THEN FA.BARCD_LBL_TP + ', ' + FA.BARCD_LBL_CTY + ', ' + FA.BARCD_LBL_ST
                          ELSE NULL
                        END                                                                       AS BarcodeDescription,
                        BCD.QUANTITY                                                              AS BarcodeCopies,
                        BCD.WEIGHT                                                                AS BarcodeWeight,
                        BCD.WEIGHT                                                                AS BarcodeActualWeight,
                        BCD.CARTON_COUNT                                                          AS BarcodeCartonCount,
                        BCD.LINKED_BARCD                                                          AS LinkedBarcodeId,
                        SHDIA.ID                                                                  AS AttentionTo,
                        SHD.FRGT_CLS_ID                                                           AS FreightClassId,
                        BCD.MDAT_CNTR_ID                                                          AS ContainerId,
                        SHD.SHPMNT_DTL_ID                                                         AS ShipmentDetailId,
                        SL.SHIP_LOC_ID                                                            AS FacilityId,
                        SDIA_BIPAD.ATTRIB_VAL                                                     AS BiPad,
                        SDIA_JOB.ATTRIB_VAL                                                       AS JobNumber,
                        SDIA_VERSION.ATTRIB_VAL                                                   AS Version,
                        COALESCE(( SHDIBB.QUANTITY_PER_BUNDLE * SHDIBB.BUNDLES ), SHDIB.QUANTITY) AS Copies,
                        SHDIBB.BUNDLES                                                            AS Bundles,
                        SDIA_ISSUE.ATTRIB_VAL                                                     AS IssueName,
                        SHDIBB.QUANTITY_PER_BUNDLE                                                AS CopiesPerBundle,
                        COALESCE(( SHDIBB.BUNDLES * SHDIBB.BUNDLE_WEIGHT ), SHDIB.WEIGHT)         AS Weight,
                        SDIA_TITLE.ATTRIB_VAL                                                     AS TitleName,
                        CASE
                          WHEN STD4AD.DATE IS NOT NULL
                          THEN STD4AD.DATE
                          ELSE STD4AD.DATE
                        END                                                                       AS ShipDate
          FROM QUAD0083..STOPMST SM
               LEFT JOIN QUAD0083..STOPS ST
                      ON SM.STPMST_ID = ST.STPMST_ID
               LEFT JOIN QUAD0083..STOP_EVENTS SE
                      ON ST.STP_ID = SE.STP_ID
                         AND SE.EVENT_TP_ID = 2 /*delivery*/
                         AND SE.ITEM_TP = 1 /*shipment*/
               LEFT JOIN QUAD0083..SHIPMENT SH
                      ON SH.SHPMNT_ID = SE.ITEM_ID
               LEFT JOIN QUAD0083..SHIP_LOC SL4STPMST
                      ON SM.BREAKUP_LOCATION_ID = SL4STPMST.SHIP_LOC_ID
               LEFT JOIN QUAD0083..SHIP_LOC SL
                      ON SL.SHIP_LOC_ID = SH.DEST_ID
               LEFT JOIN QUAD0083..ADDR SLA
                      ON SLA.ADDR_ID = SL.ADDR_ID
               LEFT JOIN QUAD0083..ADDR DESTA
                      ON DESTA.ADDR_ID = SL4STPMST.ADDR_ID
               LEFT JOIN QUAD0083..CARRIER C
                      ON C.CAR_ID = SM.CAR_ID
               LEFT JOIN QUAD0083..TRIP T
                      ON T.TRIP_ID = SM.TRIP_ID
               LEFT JOIN QUAD0083..SHIPMENT_DTL SHD
                      ON SH.SHPMNT_ID = SHD.SHPMNT_ID
               LEFT JOIN QUAD0083..SHIPMENT_DTL_ITEM SHDI
                      ON SHD.SHPMNT_DTL_ID = SHDI.SHPMNT_DTL_ID
               LEFT JOIN QUAD0083..SHPMNT_DTL_ITEM_BARCD SHDIB
                      ON SHDI.ID = SHDIB.SHPMNT_DTL_ITEM_ID
               LEFT JOIN QUAD0083..BARCD BCD
                      ON BCD.BARCD = SHDIB.BARCD
               LEFT JOIN QUAD0083..STATUS STS
                      ON SE.EVENT_STS = STS.STS_ID
                         AND STS.BEHAVIOR_ID = 3
               LEFT JOIN QUAD0083..FACILITY F
                      ON SH.DEST_ID = F.SHIP_LOC_ID
               LEFT JOIN QUAD0083..CARRIER_USPS_SCHEDULER CUS
                      ON SM.CAR_ID = CUS.CAR_ID
                         AND CUS.ACTIVE = 1 /*Active*/
               LEFT JOIN QUAD0083..RAPTOR_LEGACY_X_REF X
                      ON T.TRIP_ID = X.RAPTOR_ID
                         AND X.RAPTOR_CLM_NM = 'TRIP_ID'
                         AND X.RAPTOR_TBL_NM = 'TRIP'
                         AND X.LEGACY_CLM_NM = 'USR_LOAD_NBR'
                         AND X.LEGACY_TBL_NM = 'LOADMST'
               LEFT JOIN QUAD0066..LOADMST LM
                      ON LM.USR_LOAD_NBR = X.LEGACY_ID
               LEFT JOIN QUAD0066..LOADDET LD
                      ON LM.LOAD_NBR = LD.LOAD_NBR
                         AND LD.STP_TP = 'GR'
               LEFT JOIN QUAD0083..USPS_STOP_CONTENT USC
                      ON LD.KY_TO_SHIP_STP = USC.STOP_ID
                         AND USC.SCHEDULER_ID = CUS.SCHEDULER_ID
                         AND USC.FACILITY_NBR = F.DROP_SITE_KEY
                         AND USC.USPS_CONTENT_ID IS NOT NULL
                         AND USC.STATUS NOT IN ( 3, 4 )
               LEFT JOIN QUAD0083..SHPMNT_DTL_ITEM_ATTRIBUTES SHDIA_ENTRY
                      ON SHDIB.SHPMNT_DTL_ITEM_ID = SHDIA_ENTRY.SHPMNT_DTL_ITEM_ID
                         AND SHDIA_ENTRY.ATTRIB_TP = 38
               LEFT JOIN QUAD0083..FACILITY FA
                      ON CONVERT(INT, SHDIA_ENTRY.ATTRIB_VAL) = F.SHIP_LOC_ID
               LEFT JOIN QUAD0083..STOP_DATES SDSDB
                      ON SDSDB.DATE_TP = 2
                         AND ST.STP_ID = SDSDB.STP_ID
               LEFT JOIN QUAD0083..STOP_DATES SDSDE
                      ON SDSDE.DATE_TP = 3
                         AND ST.STP_ID = SDSDE.STP_ID
               LEFT JOIN QUAD0083..STOP_DATES SDSA
                      ON SDSA.DATE_TP = 4
                         AND ST.STP_ID = SDSA.STP_ID
               LEFT JOIN QUAD0083..STOP_DATES SDSHD
                      ON ST.STP_ID = SDSA.STP_ID
                         AND SDSA.DATE_TP = 9
                          OR SDSA.DATE_TP = 7
               LEFT JOIN QUAD0083..SHPMNT_DTL_ITEM_ATTRIBUTES SHDIA
                      ON SHDI.ID = SHDIA.SHPMNT_DTL_ITEM_ID
                         AND SHDIA.ATTRIB_TP = 26
               LEFT JOIN QUAD0083..SHPMNT_DTL_ITEM_ATTRIBUTES SDIA_BIPAD
                      ON SDIA_BIPAD.SHPMNT_DTL_ITEM_ID = SHDI.ID
                         AND SDIA_BIPAD.ATTRIB_TP = 46
               LEFT JOIN QUAD0083..SHPMNT_DTL_ITEM_ATTRIBUTES SDIA_JOB
                      ON SHDI.ID = SDIA_JOB.SHPMNT_DTL_ITEM_ID
                         AND SDIA_JOB.ATTRIB_TP = 2
               LEFT JOIN QUAD0083..SHPMNT_DTL_ITEM_ATTRIBUTES SDIA_VERSION
                      ON SHDI.ID = SDIA_VERSION.SHPMNT_DTL_ITEM_ID
                         AND SDIA_VERSION.ATTRIB_TP = 3
               LEFT JOIN QUAD0083..SHPMNT_DTL_ITEM_BARCD_BUNDLES SHDIBB
                      ON SHDIBB.SHPMNT_DTL_ITEM_BARCD_ID = SHDIB.ID
               LEFT JOIN QUAD0083..SHPMNT_DTL_ITEM_ATTRIBUTES SDIA_TITLE
                      ON SHDI.ID = SDIA_TITLE.SHPMNT_DTL_ITEM_ID
                         AND SDIA_TITLE.ATTRIB_TP = 16
               LEFT JOIN QUAD0083..SHPMNT_DTL_ITEM_ATTRIBUTES SDIA_ISSUE
                      ON SHDI.ID = SDIA_ISSUE.SHPMNT_DTL_ITEM_ID
                         AND SDIA_ISSUE.ATTRIB_TP = 19
               LEFT JOIN QUAD0083..STOP_DATES STD4AD /* ActualDeparture */
                      ON ST.STP_ID = STD4AD.STP_ID
                         AND STD4AD.DATE_TP = 9
               LEFT JOIN QUAD0083..STOP_DATES STD4PG /* PaperworkGenerated */
                      ON ST.STP_ID = STD4AD.STP_ID
                         AND STD4AD.DATE_TP = 7
         WHERE ST.STP_ID IN ( 7173279 )
         ORDER BY ST.SEQ_NBR,
                  COALESCE(BCD.LINKED_BARCD, BCD.BARCD) ASC