SELECT DISTINCT sh.id AS ShipmentHdrId, RTRIM(se.EventId) AS Id, '1.1' AS Version, GetUTCDate() AS [Timestamp], 'Application Source' AS DocSource, 'R2' AS DocSourceVersion, sh.Shipment AS [shp.Id], om.InternalId AS [shp.sf.Id], lm.PartnerLocnId AS [shp.sf.LocnId], lm.Name AS [shp.sf.Name], lm.Addr1 AS [shp.sf.adr.Address1], lm.Addr2 AS [shp.sf.adr.Address2], lm.Addr3 AS [shp.sf.adr.Address3], lm.Addr4 AS [shp.sf.adr.Address4], lm.Addr5 AS [shp.sf.adr.Address5], lm.City AS [shp.sf.adr.City], lm.State AS [shp.sf.adr.State], lm.Postal AS [shp.sf.adr.PostalCode], lm.Country AS [shp.sf.adr.Country], NULL AS [shp.sf.adr.CountryCode], lm.Contact AS [shp.sf.ci.Name], lm.Telephone AS [shp.sf.ci.Phone], lm.Fax AS [shp.sf.ci.Fax], NULL AS [shp.sf.ci.Email], NULL AS [shp.sf.cpy.Id], NULL AS [shp.sf.cpy.Code], NULL AS [shp.sf.cpy.Name], sh.ShippedDateTime AS [shp.spi.Shipped], NULL AS [shp.spi.Instructions], sh.Qty AS [shp.spi.Qty.Value-qty], sh.Cases AS [shp.spi.Qty.Value-cases], 'EA' AS [shp.spi.Qty.UOMCode-qty], 'CTN' AS [shp.spi.Qty.UOMCode-cases], NULL AS [shp.spi.Qty.UOMDesc-qty], NULL AS [shp.spi.Qty.UOMDesc-cases], sh.Weight AS [shp.spi.wgt.Value], ch.WeightCode AS [shp.spi.wgt.UOMCode], ch.WeightDesc AS [shp.spi.wgt.UOMDesc], sh.Volume AS [shp.spi.vol.Value], ch.VolumeCode AS [shp.spi.vol.UOMCode], ch.VolumeDesc AS [shp.spi.vol.UOMDesc], sh.MasterBOL AS [shp.spi.spr.MasterBOL], sh.PRONbr AS [shp.spi.spr.PRONbr], NULL AS [shp.spi.spr.ShipFromRef], sh.SrcSystem AS [shp.spi.src.Type], NULL AS [shp.spi.src.Id], NULL AS [shp.spi.gr.Type], NULL AS [shp.spi.gr.Id], sh.TransportMethodCode AS [shp.tm.Code], sh.TransportMethodDesc AS [shp.tm.Desc], sh.CarrierId AS [shp.cr.Id], sh.CarrierCode AS [shp.cr.Code], sh.CarrierDesc AS [shp.cr.Desc], sh.CarrierServiceId AS [shp.cr.crs.Id], sh.CarrierServiceDesc AS [shp.cr.crs.Desc], sh.Vehicle AS [shp.v.Id], sh.VehicleType AS [shp.v.Type], NULL AS [shp.v.Desc], om2.InternalId AS [d.sht.Id], lm2.PartnerLocnId AS [d.sht.LocnId], lm2.Name AS [d.sht.Name], lm2.Addr1 AS [d.sht.adr.Address1], lm2.Addr2 AS [d.sht.adr.Address2], lm2.Addr3 AS [d.sht.adr.Address3], lm2.Addr4 AS [d.sht.adr.Address4], lm2.Addr5 AS [d.sht.adr.Address5], lm2.City AS [d.sht.adr.City], lm2.State AS [d.sht.adr.State], lm2.Postal AS [d.sht.adr.PostalCode], lm2.Country AS [d.sht.adr.Country], NULL AS [d.sht.adr.CountryCode], lm2.Contact AS [d.sht.ci.Name], lm2.Telephone AS [d.sht.ci.Phone], lm2.Fax AS [d.sht.ci.Fax], NULL AS [d.sht.ci.Email], NULL AS [d.sht.cpy.Id], NULL AS [d.sht.cpy.Code], NULL AS [d.sht.cpy.Name], sh.EstDeliverDateTime AS [d.shti.Deliver], NULL AS [d.shti.StopNbr], NULL AS [d.shti.Instructions], sh.Qty AS [d.shti.Qty.Value-qty], sh.Cases AS [d.shti.Qty.Value-cases], 'EA' AS [d.shti.Qty.UOMCode-qty], 'CTN' AS [d.shti.Qty.UOMCode-cases], sh.Qty AS [d.shti.Qty.UOMDesc-qty], sh.Cases AS [d.shti.Qty.UOMDesc-cases], sh.Weight AS [d.shti.wgt.Value], ch.WeightCode AS [d.shti.wgt.UOMCode], ch.WeightDesc AS [d.shti.wgt.UOMDesc], sh.Volume AS [d.shti.vol.Value], ch.VolumeCode AS [d.shti.vol.UOMCode], ch.VolumeDesc AS [d.shti.vol.UOMDesc], NULL AS [d.shti.shtr.BOL], NULL AS [d.shti.shtr.ASN], sh.ShipmentShipToRef AS [d.shti.shtr.ShipToRef], sh.FOBCode AS [d.shti.FOB.Code], sh.FOBDesc AS [d.shti.FOB.Desc], NULL AS [d.shti.pv.Value], NULL AS [d.shti.pv.Code], NULL AS [d.shti.pv.Desc], NULL AS [d.shti.COD.Value], NULL AS [d.shti.COD.Code], NULL AS [d.shti.COD.Desc], NULL AS [d.shti.f.fcb.Id], NULL AS [d.shti.f.fcb.Name], NULL AS [d.shti.f.fcb.adr.Address1], NULL AS [d.shti.f.fcb.adr.Address2], NULL AS [d.shti.f.fcb.adr.Address3], NULL AS [d.shti.f.fcb.adr.Address4], NULL AS [d.shti.f.fcb.adr.Address5], NULL AS [d.shti.f.fcb.adr.City], NULL AS [d.shti.f.fcb.adr.State], NULL AS [d.shti.f.fcb.adr.PostalCode], NULL AS [d.shti.f.fcb.adr.Country], NULL AS [d.shti.f.fcb.adr.CountryCode], NULL AS [d.shti.f.fcb.ci.Name], NULL AS [d.shti.f.fcb.ci.Phone], NULL AS [d.shti.f.fcb.ci.Fax], NULL AS [d.shti.f.fcb.ci.Email], NULL AS [d.shti.f.fcb.cpy.Id], NULL AS [d.shti.f.fcb.cpy.Code], NULL AS [d.shti.f.fcb.cpy.Name], NULL AS [d.shti.f.fct.Code], NULL AS [d.shti.f.fct.Desc], appt.id AS [d.shti.apt.Id], appt.ApptDateTime AS [d.shti.apt.DateTime], appt.Dock AS [d.shti.apt.di.Dock], appt.DockDoor AS [d.shti.apt.di.DockDoor], appt.Vehicle AS [d.shti.apt.di.Vehicle], appt.StopNbr AS [d.shti.apt.di.StopNbr], appt.ApptExternalRef AS [d.shti.apt.api.ApptRef], appt.ShippingInstructions AS [d.shti.apt.api.Instructions], appt.ApptTypeCode AS [d.shti.apt.api.ApptType.Code], appt.ApptTypeDesc AS [d.shti.apt.api.ApptType.Desc], appt.Misc1 AS [d.shti.apt.mi.Misc1], appt.Misc2 AS [d.shti.apt.mi.Misc2], appt.Misc3 AS [d.shti.apt.mi.Misc3], appt.MiscNbr1 AS [d.shti.apt.mi.MiscNbr1], appt.MiscNbr2 AS [d.shti.apt.mi.MiscNbr2], appt.MiscNbr3 AS [d.shti.apt.mi.MiscNbr3], NULL AS [d.shti.ntf.ci.Name], NULL AS [d.shti.ntf.ci.Phone], NULL AS [d.shti.ntf.ci.Fax], NULL AS [d.shti.ntf.ci.Email], NULL AS [d.shti.mi.Misc1], NULL AS [d.shti.mi.Misc2], NULL AS [d.shti.mi.Misc3], NULL AS [d.shti.mi.MiscNbr1], NULL AS [d.shti.mi.MiscNbr2], NULL AS [d.shti.mi.MiscNbr3], NULL AS [d.shti.gr.Type], NULL AS [d.shti.gr.Id], ph.PO AS [do.Id], ph.ShippingInstructions AS [do.ShipperComments], ph.CustId AS [do.own.Id], ph.CustName AS [do.own.Name], (SELECT ownAddr.Addr1 FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.Address1], (SELECT ownAddr.Addr2 FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.Address2], (SELECT ownAddr.Addr3 FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.Address3], (SELECT ownAddr.Addr4 FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.Address4], (SELECT ownAddr.Addr5 FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.Address5], (SELECT ownAddr.city FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.City], (SELECT ownAddr.state FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.State], (SELECT ownAddr.postal FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.PostalCode], (SELECT ownAddr.Country FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.Country], (SELECT ownAddr.CountryCode FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.adr.CountryCode], (SELECT ownAddr.ContactName FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.ci.Name], (SELECT ownAddr.ContactPhone FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.ci.Phone], (SELECT ownAddr.ContactFax FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.ci.Fax], (SELECT ownAddr.ContactEmail FROM Address ownAddr WHERE ownAddr.id = ph.CustAddrId) AS [do.own.ci.Email], ph.SupplierId AS [do.s.Id], ph.SupplierName AS [do.s.Name], (SELECT ownAddr.Addr1 FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.Address1], (SELECT ownAddr.Addr2 FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.Address2], (SELECT ownAddr.Addr3 FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.Address3], (SELECT ownAddr.Addr4 FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.Address4], (SELECT ownAddr.Addr5 FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.Address5], (SELECT ownAddr.City FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.City], (SELECT ownAddr.State FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.State], (SELECT ownAddr.Postal FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.PostalCode], (SELECT ownAddr.Country FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.Country], (SELECT ownAddr.CountryCode FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.adr.CountryCode], (SELECT ownAddr.ContactName FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.ci.Name], (SELECT ownAddr.ContactPhone FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.ci.Phone], (SELECT ownAddr.ContactFax FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.ci.Fax], (SELECT ownAddr.ContactEmail FROM Address ownAddr WHERE ownAddr.id = ph.SupplierAddrId) AS [do.s.ci.Email], ph.SupplierCoId AS [do.s.cpy.Id], ph.SupplierCoCode AS [do.s.cpy.Code], ph.SupplierCoName AS [do.s.cpy.Name], (SELECT SUM(cdin.Qty) FROM ContainerDetail cdin WHERE cdin.id = cd.id) AS [do.oi.Qty.Value-all], (SELECT COUNT(chin.Id) FROM ContainerHeader chin WHERE chin.id = chCase.id) AS [do.oi.Qty.Value-cnt], 'EA' AS [do.oi.Qty.UOMCode-all], 'CNT' AS [do.oi.Qty.UOMCode-cnt], NULL AS [do.oi.Qty.UOMDesc-all], NULL AS [do.oi.Qty.UOMDesc-cnt], (SELECT SUM(IsNull(chin.ActualWeight, 0)) FROM ContainerHeader chin WHERE chin.id = ch.id AND chin.id NOT IN (SELECT childid FROM ContainerReference)) AS [do.oi.wgt.Value], ch.WeightCode AS [do.oi.wgt.UOMCode], ch.WeightDesc AS [do.oi.wgt.UOMDesc], (SELECT SUM(IsNull(chin.Volume, 0)) FROM ContainerHeader chin WHERE chin.id = ch.id AND chin.id NOT IN (SELECT childid FROM ContainerReference)) AS [do.oi.vol.Value], ch.VolumeCode AS [do.oi.vol.UOMCode], ch.VolumeCode AS [do.oi.vol.UOMDesc], ph.OrderDate AS [do.oi.odts.Ordered], ph.StartShipDate AS [do.oi.odts.StartShip], ph.EndShipDate AS [do.oi.odts.EndShip], ph.StartDeliverDateTime AS [do.oi.odts.StartDeliver], ph.EndDeliverDateTime AS [do.oi.odts.EndDeliver], ph.CancelDate AS [do.oi.odts.Cancel], ah.ASN AS [do.oi.orfs.ASN], ch.BillingRef AS [do.oi.orfs.BillingRef], ph.BlanketPO AS [do.oi.orfs.BlanketOrder], ph.CustOrder AS [do.oi.orfs.CustomerOrder], ph.SupplierOrderNbr AS [do.oi.orfs.SupplierOrder], ph.OrderRef AS [do.oi.orfs.OwnerOrder], NULL AS [do.oi.orfs.WorkOrder], ph.TypeCode AS [do.oi.ot.Code], ph.TypeDesc AS [do.oi.ot.Desc], ph.PriorityCode AS [do.oi.pr.Code], ph.PriorityDesc AS [do.oi.pr.Desc], ph.CategoryCode AS [do.oi.ct.Code], ph.CategoryDesc AS [do.oi.ct.Desc], ntfyAddr.ContactName AS [do.oi.ntf.ci.Name], ntfyAddr.ContactPhone AS [do.oi.ntf.ci.Phone], ntfyAddr.ContactFax AS [do.oi.ntf.ci.Fax], ntfyAddr.ContactEmail AS [do.oi.ntf.ci.Email], ph.RevisionDateTime AS [do.oi.rv.DateTime], ph.RevisionNbr AS [do.oi.rv.Nbr], ph.RevisionCode AS [do.oi.rv.Code], ph.RevisionDesc AS [do.oi.rv.Desc], ph.Misc1 AS [do.oi.mi.Misc1], ph.Misc2 AS [do.oi.mi.Misc2], ph.Misc3 AS [do.oi.mi.Misc3], ph.MiscNbr1 AS [do.oi.mi.MiscNbr1], ph.MiscNbr2 AS [do.oi.mi.MiscNbr2], ph.MiscNbr3 AS [do.oi.mi.MiscNbr3], ph.SrcSystem AS [do.oi.src.Type], NULL AS [do.oi.src.Id], NULL AS [do.oi.gr.Id], NULL AS [do.oi.gr.Type], pd.ItemId AS [doo.Id], pd.POLine AS [doo.OrderLine], pd.ItemDesc AS [doo.Desc], (SELECT SUM(cdin.Qty) FROM ContainerDetail cdin WHERE cdin.id = cd.id) AS [doo.oii.Qty.Value], pd.UOMCode AS [doo.oii.Qty.UOMCode], pd.UOMDesc AS [doo.oii.Qty.UOMDesc], (SELECT SUM(cdin.ActualWeight) FROM ContainerDetail cdin WHERE cdin.id = cd.id) AS [doo.oii.wgt.Value], cd.WeightCode AS [doo.oii.wgt.UOMCode], cd.WeightDesc AS [doo.oii.wgt.UOMDesc], NULL AS [doo.oii.vol.Value], NULL AS [doo.oii.vol.UOMCode], NULL AS [doo.oii.vol.UOMDesc], pd.ItemBarcode AS [doo.oii.bc.Id], pd.ItemBarcodeType AS [doo.oii.bc.Type], pd.SupplierItemId AS [doo.oii.si.Id], pd.SupplierItemDesc AS [doo.oii.si.Desc], pd.SupplierItemBarcode AS [doo.oii.si.bc.Id], pd.SupplierItemBarcodeType AS [doo.oii.si.bc.Type], pd.MasterItemId AS [doo.oii.mtri.Id], pd.MasterItemDesc AS [doo.oii.mtri.Desc], pd.MasterItemBarcode AS [doo.oii.mtri.bc.Id], pd.MasterItemBarcodeType AS [doo.oii.mtri.bc.Type], pd.AlternateItemId AS [doo.oii.ai.Id], pd.AlternateItemDesc AS [doo.oii.ai.Desc], pd.AlternateItemBarcode AS [doo.oii.ai.bc.Id], pd.AlternateItemBarcodeType AS [doo.oii.ai.bc.Type], pd.SupplierId AS [doo.oii.is.Id], pd.SupplierName AS [doo.oii.is.Name], pd.RevisionDateTime AS [doo.oii.rv.DateTime], pd.RevisionNbr AS [doo.oii.rv.Nbr], pd.RevisionCode AS [doo.oii.rv.Code], pd.RevisionDesc AS [doo.oii.rv.Desc], pd.Misc1 AS [doo.oii.mi.Misc1], pd.Misc2 AS [doo.oii.mi.Misc2], pd.Misc3 AS [doo.oii.mi.Misc3], pd.MiscNbr1 AS [doo.oii.mi.MiscNbr1], pd.MiscNbr2 AS [doo.oii.mi.MiscNbr2], pd.MiscNbr3 AS [doo.oii.mi.MiscNbr3], pd.SrcSystem AS [doo.oii.src.Type], NULL AS [doo.oii.src.Id], NULL AS [doo.oii.gr.Id], NULL AS [doo.oii.gr.Type], ch.Container AS [dc.Id], chPrnt.Container AS [dc.Parent.Id], ch.CreateDateTime AS [dc.cnti.Created], NULL AS [dc.cnti.ContainerOwner], ch.Qty AS [dc.cnti.Qty.Value], NULL AS [dc.cnti.Qty.UOMCode], NULL AS [dc.cnti.Qty.UOMDesc], ch.ActualWeight AS [dc.cnti.wgt.Value], ch.WeightCode AS [dc.cnti.wgt.UOMCode], ch.WeightDesc AS [dc.cnti.wgt.UOMDesc], ch.NetWeight AS [dc.cnti.nw.Value], ch.WeightCode AS [dc.cnti.nw.UOMCode], ch.WeightDesc AS [dc.cnti.nw.UOMDesc], ch.Volume AS [dc.cnti.vol.Value], ch.VolumeCode AS [dc.cnti.vol.UOMCode], ch.VolumeDesc AS [dc.cnti.vol.UOMDesc], ph.CustId AS [dc.cnti.orf.OrderOwnerId], ph.PO AS [dc.cnti.orf.OrderId], ch.BillingRef AS [dc.cnti.crfs.BillingRef], ph.CustOrder AS [dc.cnti.crfs.CustomerOrder], ph.SupplierOrderNbr AS [dc.cnti.crfs.SupplierOrder], NULL AS [dc.cnti.crfs.WorkOrder], ch.ContainerType AS [dc.cnti.ctp.Code], NULL AS [dc.cnti.ctp.Desc], cs.ContainerSize AS [dc.cnti.cs.Code], NULL AS [dc.cnti.cs.Desc], ch.QualityCode AS [dc.cnti.q.Code], (SELECT Code_desc FROM sys_code WHERE code_type = 521 AND appid = 3 AND Code_Id = STR(ch.QualityCode)) AS [dc.cnti.q.Desc], NULL AS [dc.cnti.Store.Type], ch.StoreId AS [dc.cnti.Store.Id], ch.StoreName AS [dc.cnti.Store.Name], ch.Misc1 AS [dc.cnti.mi.Misc1], ch.Misc2 AS [dc.cnti.mi.Misc2], ch.Misc3 AS [dc.cnti.mi.Misc3], ch.MiscNbr1 AS [dc.cnti.mi.MiscNbr1], ch.MiscNbr2 AS [dc.cnti.mi.MiscNbr2], ch.MiscNbr3 AS [dc.cnti.mi.MiscNbr3], ch.SrcSystem AS [dc.cnti.src.Type], NULL AS [dc.cnti.src.Id], NULL AS [dc.cnti.gr.Id], NULL AS [dc.cnti.gr.Type], pd.ItemId AS [dci.Id], pd.POLine AS [dci.OrderLine], cd.Qty AS [dci.cii.Qty.Value], pd.UOMCode AS [dci.cii.Qty.UOMCode], pd.UOMDesc AS [dci.cii.Qty.UOMDesc], cd.NetWeight AS [dci.cii.nw.Value], cd.WeightCode AS [dci.cii.nw.UOMCode], cd.WeightDesc AS [dci.cii.nw.UOMDesc], ph.CustId AS [dci.cii.orf.OrderOwnerId], ph.PO AS [dci.cii.orf.OrderId], cd.QualityCode AS [dci.cii.q.Code], (SELECT Code_desc FROM sys_code WHERE code_type = 521 AND appid = 3 AND Code_Id = cd.QualityCode) AS [dci.cii.q.Desc], pd.ItemBarcode AS [dci.cii.bc.Id], pd.ItemBarcodeType AS [dci.cii.bc.Type], pd.SupplierItemId AS [dci.cii.si.Id], pd.SupplierItemDesc AS [dci.cii.si.Desc], pd.SupplierItemBarcode AS [dci.cii.si.bc.Id], pd.SupplierItemBarcodeType AS [dci.cii.si.bc.Type], pd.MasterItemId AS [dci.cii.mtri.Id], pd.MasterItemDesc AS [dci.cii.mtri.Desc], pd.MasterItemBarcode AS [dci.cii.mtri.bc.Id], pd.MasterItemBarcodeType AS [dci.cii.mtri.bc.Type], pd.AlternateItemId AS [dci.cii.ai.Id], pd.AlternateItemDesc AS [dci.cii.ai.Desc], pd.AlternateItemBarcode AS [dci.cii.ai.bc.Id], pd.AlternateItemBarcodeType AS [dci.cii.ai.bc.Type], pd.SupplierId AS [dci.cii.is.Id], pd.SupplierName AS [dci.cii.is.Name], pd.CustOrder AS [dci.cii.cir.CustomerOrder], ph.SupplierOrderNbr AS [dci.cii.cir.SupplierOrder], NULL AS [dci.cii.cir.WorkOrder], pd.ItemDesc AS [dci.cii.cia.Desc], cd.Batch AS [dci.cii.cia.Batch], cd.SerialNbr AS [dci.cii.cia.SerialNbr], cd.SellBeforeDate AS [dci.cii.cia.BestBefore], cd.ClearanceDays AS [dci.cii.cia.ShelfLife], NULL AS [dci.cii.cia.AlcoholByVolume], ch.CountryCode AS [dci.cii.cia.CountryOfOrigin], NULL AS [dci.cii.cia.CountryConsigned], NULL AS [dci.cii.Bond.Code], NULL AS [dci.cii.Bond.Desc], pd.CommodityCode AS [dci.cii.cmdy.Code], pd.CommodityDesc AS [dci.cii.cmdy.Desc], pd.NMFCCode AS [dci.cii.NMFC.Code], pd.NMFCDesc AS [dci.cii.NMFC.Desc], pd.FreightClassCode AS [dci.cii.fc.Code], pd.FreightClassDesc AS [dci.cii.fc.Desc], pd.HazMatCode AS [dci.cii.hm.Code], pd.HazMatDesc AS [dci.cii.hm.Desc], pd.StdPackQty AS [dci.cii.spk.Qty], pd.StdPackType AS [dci.cii.spk.Type], pd.StdPackCode AS [dci.cii.spk.Code], pd.StdPackDesc AS [dci.cii.spk.Desc], pd.StdContainerQty AS [dci.cii.scnt.Qty], pd.StdContainerType AS [dci.cii.scnt.Type], pd.StdContainerCode AS [dci.cii.scnt.Code], pd.StdContainerDesc AS [dci.cii.scnt.Desc], pd.TicketType AS [dci.cii.tkt.Type], pd.TicketCode AS [dci.cii.tkt.Code], pd.TicketDesc AS [dci.cii.tkt.Desc], NULL AS [dci.cii.mi.Misc1], NULL AS [dci.cii.mi.Misc2], NULL AS [dci.cii.mi.Misc3], NULL AS [dci.cii.mi.MiscNbr1], NULL AS [dci.cii.mi.MiscNbr2], NULL AS [dci.cii.mi.MiscNbr3], cd.SrcSystem AS [dci.cii.src.Type], NULL AS [dci.cii.src.Id], NULL AS [dci.cii.gr.Id], NULL AS [dci.cii.gr.Type], cd.id AS [ContainerDetail.Id] FROM dbo.ShipmentHeader sh INNER JOIN dbo.OrgMaster om ON om.id = sh.OrigOrgId INNER JOIN dbo.LocationMaster lm ON lm.id = sh.OrigLocnId LEFT OUTER JOIN dbo.OrgMaster om2 ON om2.id = sh.DestOrgId LEFT OUTER JOIN dbo.LocationMaster lm2 ON lm2.id = sh.DestLocnId LEFT OUTER JOIN dbo.Appointment appt ON appt.id = sh.CurrApptId LEFT OUTER JOIN dbo.ShipmentASNs sa ON sh.id = sa.ShipmentHdrId LEFT OUTER JOIN dbo.ASNHeader ah ON ah.id = sa.ASNHdrId LEFT OUTER JOIN dbo.ASNContainers ac ON ah.id = ac.ASNHdrId LEFT OUTER JOIN dbo.ContainerHeader ch ON ch.id = ac.ContainerHdrId LEFT OUTER JOIN dbo.ContainerSize cs ON cs.id = ch.ContainerSizeId LEFT OUTER JOIN dbo.ContainerHeader chCase ON chCase.id = ac.ContainerHdrId AND chCase.Type = 1 LEFT OUTER JOIN dbo.ContainerHeader chPallet ON chPallet.id = ac.ContainerHdrId AND chPallet.Type = 2 LEFT OUTER JOIN dbo.ContainerReference crc ON crc.ParentId = ch.id LEFT OUTER JOIN dbo.ContainerHeader chPrnt ON chPrnt.id = crc.ChildId LEFT OUTER JOIN dbo.MOHeader mh ON mh.id = ch.MOHdrId LEFT OUTER JOIN dbo.POHeader ph ON ph.id = mh.POHdrId LEFT OUTER JOIN dbo.ContainerDetail cd ON ch.id = cd.ContainerHdrId LEFT OUTER JOIN dbo.MODetail md ON md.id = cd.MODtlId LEFT OUTER JOIN dbo.MOPODetail mpd ON mpd.id = md.MOPODtlId LEFT OUTER JOIN dbo.PODetail pd ON pd.id = mpd.PODtlId LEFT OUTER JOIN dbo.Address ntfyAddr ON ntfyAddr.id = ph.NotifyAddressId LEFT OUTER JOIN dbo.ShipmentEvent se ON sh.id = se.ShipmentHdrId