SQLize | PHPize | SQLtest

SELECT [Satış Ülkesi] = (SELECT TOP 1 CountryDescription FROM prCurrAccPostalAddress INNER JOIN cdCountryDesc ON prCurrAccPostalAddress.CountryCode=cdCountryDesc.CountryCode AND LangCode='TR' WHERE prCurrAccPostalAddress.CurrAccCode=trDispOrderHeader.CurrAccCode) ,[Satis_Kanali_Adi] = StoreHierarchyLevel02 ,[Bölge Adı] = (SELECT CurrAccAttribute.AttributeDescription FROM prCurrAccAttribute INNER JOIN CurrAccAttribute('TR') ON CurrAccAttribute.AttributeTypeCode=prCurrAccAttribute.AttributeTypeCode AND CurrAccAttribute.AttributeCode=prCurrAccAttribute.AttributeCode AND CurrAccAttribute.CurrAccTypeCode=5 AND LangCode='TR' AND trDispOrderHeader.CurrAccCode=CurrAccCode AND prCurrAccAttribute.AttributeTypeCode=1) ,[Bölge Müdürü] = (SELECT CurrAccAttribute.AttributeDescription FROM prCurrAccAttribute INNER JOIN CurrAccAttribute('TR') ON CurrAccAttribute.AttributeTypeCode=prCurrAccAttribute.AttributeTypeCode AND CurrAccAttribute.AttributeCode=prCurrAccAttribute.AttributeCode AND CurrAccAttribute.CurrAccTypeCode=5 AND LangCode='TR' AND trDispOrderHeader.CurrAccCode=CurrAccCode AND prCurrAccAttribute.AttributeTypeCode=2) ,[Hedef_Lokasyon_Kodu] = trDispOrderHeader.CurrAccCode ,[Hedef_Lokasyon_Adi] = cdCurrAccDesc.CurrAccDescription ,[Dağıtım Kanalı] = CASE WHEN trDispOrderHeader.ProcessCode ='ES' THEN '211' WHEN trDispOrderHeader.ProcessCode ='WS' THEN '210' WHEN trDispOrderHeader.WarehouseCode ='A34' THEN '215' WHEN trDispOrderHeader.ProcessCode ='S' AND ITAtt04='' THEN '212' ELSE '214' END ,[Taşıma Firması] = ISNULL(cdStoreDistributionGroupDesc.StoreDistributionGroupDescription,SPACE(0)) ,[Nakliye Firması Rota Bilgisi] = (SELECT AttributeCode FROM prCurrAccAttribute WHERE prCurrAccAttribute.CurrAccTypeCode=trDispOrderHeader.CurrAccTypeCode AND prCurrAccAttribute.CurrAccCode=trDispOrderHeader.CurrAccCode AND AttributeTypeCode=19) ,[Kaynak Depo] = trDispOrderHeader.WarehouseCode ,[ÜRÜN ANA GRUBU] = ProductHierarchyLevel01 ,[ÜRÜN ARA GRUBU] = ProductHierarchyLevel02 ,[Urun_Adi] = ItemDescription ,[SKU] = AllOrderLinesWithITAttributes.ItemCode+AllOrderLinesWithITAttributes.ColorCode+AllOrderLinesWithITAttributes.ItemDim1Code ,[Model_Kodu] = AllOrderLinesWithITAttributes.ItemCode ,[Renk_Kodu] = AllOrderLinesWithITAttributes.ColorCode ,[Beden_Kodu] = AllOrderLinesWithITAttributes.ItemDim1Code ,[Asorti] = trDispOrderLine.LineDescription ,[Barkod] = prItemBarcode.Barcode ,[Sipariş Tarihi] = AllOrderLinesWithITAttributes.OrderDate ,[Sipariş Ref.Number] = AllOrderLinesWithITAttributes.OrderNumber ,[Sipariş Tipi] = case when ITAtt04='' then '1-1' else '1-N' END ,[Sevk Emri Numara] = DispOrderNumber ,[Sevk Emri Durumu (İPTAL)] = CASE WHEN trDispOrderHeader.IsReturn=1 THEN trDispOrderHeader.Description ELSE '' END ,[sipariş Durumu] = CASE WHEN (AllOrderLinesWithITAttributes.IsClosed=1 or trDispOrderLine.Qty1=trShipmentLine.Qty1) THEN 'Sipariş Kapalı' ELSE 'Sipariş Açık'END ,[Sevk Emrine Dönüşme (GÜN)] = DATEDIFF(DAY,AllOrderLinesWithITAttributes.CreatedDate,trDispOrderHeader.CreatedDate) ,[Sevk Yaratma Tarihi] = trDispOrderHeader.CreatedDate ,[Depoya Yansıma Tarihi] = trDispOrderHeader.LastUpdatedDate ,[Sevk Emri Miktar] = trDispOrderLine.Qty1 ,[Koli İçi Adet (Sim)] = PackedHeight ,[Desi] = ROUND(ISNULL((ItemWidth*Itemlength*Itemheight)/3000,1),2) ,[Sevkiyat_Numarasi] = trShipmentHeader.ShippingNumber ,[İrsaliye Tarihi] = trShipmentHeader.ShippingDate ,[İrsaliye Ürün Miktarı] = trShipmentLine.Qty1 ,[Sevk Edilmeyen Adet] = (trDispOrderLine.Qty1-trShipmentLine.Qty1) ,[Kapama Nedeni] = CASE AllOrderLinesWithITAttributes.[BaseStoreCode] WHEN '25' THEN 'HASARLI-CEVA' WHEN '26' THEN 'HATALI SIM-MUSTERI' WHEN '27' THEN 'BLOKAJLI URUN' WHEN '28' THEN 'MUSTERI TARAFINDAN İPTAL EDİLDİ' WHEN '29' THEN 'SİPARİŞ HAREKET TİPİNDEN KAYNAKLI' WHEN '30' THEN 'STOK YOK(ALOKE EKSIKLIGI)' WHEN '31' THEN 'STOK YOK (KAYIP STOK)' WHEN '99' THEN 'SİPARİŞ KAPATILDI'END ,[Sevkiyat_Lojistik_No] = LogisticsCompanyBOL ,[ATFID] = (SELECT LTRIM( RTRIM(StrCol) ) from ( SELECT ROW_NUMBER() OVER( ORDER BY StrCol desc ) AS SortNumber, StrCol FROM dbo.DelimetedStringToTable(trShipmentHeader.Description, '||') ) AS t WHERE SortNumber = 3) ,[Ürün Mal kabul Tarihi] = trShipmentHeader.TransferApprovedDate ,[Mağaza Mal Kabul Adet] = (trShipmentLine.Qty1+OverStock-MissingStock) ,[Mağaza Eksik Onay] = MissingStock ,[Mağaza Fazla Onay] = OverStock ,[Nakliye Teslim Tarihi] = (select top 1 ReceivedAt from zt_CevaPackages where (FilloStatus = '1' or TalhaStatus = '1') AND ReceivedAt IS NOT NULL and Shippingnumber=trShipmentHeader.ShippingNumber) ,[Mağaza Teslim Tarihi] = (select top 1 DeliveredAt from zt_CevaPackages where (FilloStatus = '4' or TalhaStatus = '4') AND DeliveredAt IS NOT NULL and Shippingnumber=trShipmentHeader.ShippingNumber) FROM trDispOrderLine INNER JOIN trDispOrderHeader on trDispOrderHeader.DispOrderHeaderID = trDispOrderLine.DispOrderHeaderID INNER JOIN AllOrderLinesWithITAttributes ON trDispOrderLine.OrderLineID=AllOrderLinesWithITAttributes.OrderLineID INNER JOIN cdCurrAcc ON cdCurrAcc.CurrAccCode=trDispOrderHeader.CurrAccCode AND cdCurrAcc.CurrAccTypeCode=trDispOrderHeader.CurrAccTypeCode INNER JOIN cdCurrAccDesc ON cdCurrAcc.CurrAccCode=cdCurrAccDesc.CurrAccCode AND cdCurrAcc.CurrAcctypeCode=cdCurrAccDesc.CurrAcctypeCode AND LangCode='TR' INNER JOIN dbo.StoreHierarchy('TR') ON dbo.StoreHierarchy.StoreHierarchyID=cdCurrAcc.StoreHierarchyID INNER JOIN prStoreProperties WITH(NOLOCK) ON cdCurrAcc.CurrAccCode=prStoreProperties.StoreCode AND cdCurrAcc.CurrAccTypeCode=prStoreProperties.StoreTypeCode INNER JOIN dbo.cdStoreDistributionGroupDesc AS cdStoreDistributionGroupDesc ON cdStoreDistributionGroupDesc.StoreDistributionGroupCode = prStoreProperties.StoreDistributionGroupCode AND cdStoreDistributionGroupDesc.LangCode ='TR' INNER JOIN cdItem ON AllOrderLinesWithITAttributes.ItemCode=cdItem.ItemCode AND AllOrderLinesWithITAttributes.ItemtypeCode=cdItem.ItemtypeCode INNER JOIN cdItemDesc ON cdItem.ItemCode=cdItemDesc.ItemCode AND cdItem.ItemTypeCode=cdItemDesc.ItemTypeCode AND cdItemDesc.LangCode='TR' INNER JOIN dbo.ProductHierarchy('TR') ON cdItem.ProductHierarchyID=ProductHierarchy.ProductHierarchyID INNER JOIN prItemBarcode ON AllOrderLinesWithITAttributes.ItemCode=prItemBarcode.ItemCode AND AllOrderLinesWithITAttributes.ItemDim1Code=prItemBarcode.ItemDim1Code AND AllOrderLinesWithITAttributes.ColorCode=prItemBarcode.ColorCode AND AllOrderLinesWithITAttributes.ItemtypeCode=prItemBarcode.ItemtypeCode AND prItemBarcode.BarcodeTypeCode='EAN13' INNER JOIN prItemMeasuresOfVolume ON AllOrderLinesWithITAttributes.ItemCode=prItemMeasuresOfVolume.ItemCode AND AllOrderLinesWithITAttributes.ItemTypeCode=prItemMeasuresOfVolume.ItemTypeCode AND AllOrderLinesWithITAttributes.ColorCode=prItemMeasuresOfVolume.ColorCode AND AllOrderLinesWithITAttributes.ItemDim1Code=prItemMeasuresOfVolume.ItemDim1Code LEFT JOIN trShipmentLine ON trShipmentLine.DispOrderLineID=trDispOrderLine.DispOrderLineID INNER JOIN trShipmentHeader ON trShipmentLine.ShipmentHeaderID=trShipmentHeader.ShipmentHeaderID LEFT JOIN rpTransferApproved ON trShipmentLine.ShipmentHeaderID=rpTransferApproved.ApplicationID AND rpTransferApproved.ItemCode=trShipmentLine.ItemCode AND rpTransferApproved.ItemDim1Code=trShipmentLine.ItemDim1Code AND rpTransferApproved.ColorCode=trShipmentLine.ColorCode AND rpTransferApproved.ItemtypeCode=trShipmentLine.ItemtypeCode AND rpTransferApproved.ApplicationCode ='Shipm' AND rpTransferApproved.ProcessCode =N'S' LEFT JOIN zt_OtomatikMalKabul ON trShipmentHeader.ShippingNumber=zt_OtomatikMalKabul.ShippingNumber and zt_OtomatikMalKabul.CurrAccCode = trShipmentHeader.CurrAccCode and zt_OtomatikMalKabul.Barcode = prItemBarcode.Barcode WHERE trDispOrderHeader.CompanyCode = 1 and trDispOrderHeader.DispOrderDate between CAST('20230814' AS DATETIME) and CAST('20230817' AS DATETIME) and trDispOrderHeader.WarehouseCode = N'A11' and trDispOrderHeader.ProcessCode = N'S' and trDispOrderHeader.WarehouseCode= N'A11'