Nebim v3 Mobil – Rezerve ve Sevk Emri Düşülmüş Envanter Gösterme

Categories Nebim-V3, SQLPosted on

Sql üzerinde ItemInventoryForProcess fonksiyonu üzerinde değişiklik yapıyoruz.

Eski fonksiyonumuzu drop and create to diyerek önceklikle yedekliyoruz.

Sonra 1. Satırdaki [VERİTABANI] yazan yere kendi bilgilerimizi giriyoruz ve çalıştırıyoruz.

Artık el terminalimizde Envanter seçilen depo için Gerçek Envater = Envanter – (Rezerve Miktarı + Sevk emri miktarı ) formuluyle çalışıyor.

-- =============================================
-- 26.11.2018 16:34:58
-- 18.4.1.0
-- Auto Generated by NebimV3ERP.exe
-- =============================================

DELETE bsQueryCustom WHERE QueryName = 'ProductWithHierarchyLevel'
INSERT INTO bsQueryCustom (QueryName, KeyCodes, QueryText) VALUES(N'ProductWithHierarchyLevel', N'ProductCode'
,N'
SELECT [STATU] = data.ProductAtt29
,[HASIRCILAR KALAN] = [HSC ENVANTER] - ([HSC REZERVE] + [HASIRCILAR BEKLEYEN SEVK])
, [ANTREPO KALAN] = [ANTREPO ENVANTER] - ([ANTREPO REZERVE] + [ANTREPO BEKLEYEN SEVK] )
,[LALELI ENV] = [LALELI ENVANTER] - ([LALELI REZERVE] + [LALELI BEKLEYEN SEVK] )
,[PRF]=[PRFIYAT]
,[USD]=[FIYAT]
,[AN]=[ANFIYAT]
,[TL]=[TLFIYAT]
,[NET]=[NETUSD]
,[PROM]=[PROMFIYAT]
,[FREEZONE]=[FREE]

--,[ANTREPO PROFORMADAN KALAN] = [ANTREPO PROFORMA ADETI] - [ANTREPO REZERVE]
,*
FROM (SELECT ProductCode = cdItem.ItemCode
, ProductDescription = ISNULL(ItemDescription, SPACE(0))
, ProductAtt34 = ProductAttributesFilter.ProductAtt34
, ProductAtt24 = ProductAttributesFilter.ProductAtt24
, ProductAtt30 = ProductAttributesFilter.ProductAtt30

, ProductAtt24Desc = ProductAttributeDescriptions.ProductAtt24Desc
, ProductAtt30Desc = ProductAttributeDescriptions.ProductAtt30Desc

--,[İngilizce] = ISNULL((SELECT ItemDescription FROM cdItemDesc WHERE ItemTypeCode = cdItem.ItemTypeCode AND ItemCode = cdItem.ItemCode AND LangCode = ('
'EN'')), SPACE(0))
, CustomsTariffNumberCode
, cdItem.ProductTypeCode
, ProductTypeDescription = ISNULL((SELECT ProductTypeDescription FROM bsProductTypeDesc WITH(NOLOCK) WHERE bsProductTypeDesc.ProductTypeCode = cdItem.ProductTypeCode AND bsProductTypeDesc.LangCode = {LangCode}),SPACE(0))

, cdItem.ItemDimTypeCode
--, ItemDimTypeDescription = ISNULL((SELECT ItemDimTypeDescription FROM ItemDimType({LangCode}) WHERE ItemDimType.ItemDimTypeCode = cdItem.ItemDimTypeCode),SPACE(0))
--, UnitOfMeasureCode1
--, UnitOfMeasureCode2
, cdItem.UsePOS
, cdItem.UseStore
,[HSC ENVANTER] = ISNULL((SELECT SUM(Inventoryqty1) from ItemInventory
where ItemInventory.ItemTypeCode = 1
AND ItemInventory.ItemCode = cdItem.ItemCode
AND ItemInventory.WareHouseCode = '
'01''),0)
, [HSC REZERVE] = ISNULL((SELECT Qty1 FROM ReserveStates
WHERE WarehouseCode = '
'01''
AND ItemTypeCode = cdItem.ItemTypeCode
AND ItemCode = cdItem.ItemCode
),SPACE(0))

, [HASIRCILAR BEKLEYEN SEVK] = ISNULL((SELECT Qty1 FROM DispOrderStates
WHERE ItemCode = cdItem.ItemCode
AND ItemTypeCode = cdItem.ItemTypeCode
AND WarehouseCode = '
'01''),0)

,[ANTREPO ENVANTER] = ISNULL((SELECT SUM(Inventoryqty1) from ItemInventory
where ItemInventory.ItemTypeCode = 1
AND ItemInventory.ItemCode = cdItem.ItemCode
AND ItemInventory.WareHouseCode = '
'ANT01''),0)

, [ANTREPO REZERVE] = ISNULL((SELECT Qty1 FROM ReserveStates
WHERE ItemCode = cdItem.ItemCode
AND ItemTypeCode = cdItem.ItemTypeCode
AND WarehouseCode = '
'ANT01''),0)
, [ANTREPO BEKLEYEN SEVK] = ISNULL((SELECT Qty1 FROM DispOrderStates
WHERE ItemCode = cdItem.ItemCode
AND ItemTypeCode = cdItem.ItemTypeCode
AND WarehouseCode = '
'ANT01''),0)

,[KONSINYE ENVANTER] = ISNULL((SELECT TOP 1 SUM(Inventoryqty1) from ItemInventory
where ItemInventory.ItemTypeCode = 1
AND ItemInventory.ItemCode = cdItem.ItemCode
AND ItemInventory.WareHouseCode = '
'KO1''),0)

,[LALELI ENVANTER] = ISNULL((SELECT SUM(Inventoryqty1) from ItemInventory
where ItemInventory.ItemTypeCode = 1
AND ItemInventory.ItemCode = cdItem.ItemCode
AND ItemInventory.WareHouseCode = '
'03''),0)

, [LALELI REZERVE] = ISNULL((SELECT Qty1 FROM ReserveStates
WHERE ItemCode = cdItem.ItemCode
AND ItemTypeCode = cdItem.ItemTypeCode
AND WarehouseCode = '
'03''),0)
, [LALELI BEKLEYEN SEVK] = ISNULL((SELECT Qty1 FROM DispOrderStates
WHERE ItemCode = cdItem.ItemCode
AND ItemTypeCode = cdItem.ItemTypeCode
AND WarehouseCode = '
'03''),0)

, [FIYAT] = ISNULL((SELECT TOP 1 Price
FROM ProductValidPrices (GETDATE(), GETDATE())
WHERE ProductValidPrices.PriceGroupCode = '
'TS_USD''
AND ProductValidPrices.ItemTypeCode = cdItem.ItemTypeCode
AND ProductValidPrices.ItemCode = cdItem.ItemCode
ORDER BY ProductValidPrices.ColorCode DESC, ProductValidPrices.PriceListDate DESC),0)
, [PRFIYAT] = ISNULL((SELECT TOP 1 Price
FROM ProductValidPrices (GETDATE(), GETDATE())
WHERE ProductValidPrices.PriceGroupCode = '
'PRF''
AND ProductValidPrices.ItemTypeCode = cdItem.ItemTypeCode
AND ProductValidPrices.ItemCode = cdItem.ItemCode
ORDER BY ProductValidPrices.ColorCode DESC, ProductValidPrices.PriceListDate DESC),0)
, [ANFIYAT] = ISNULL((SELECT TOP 1 Price
FROM ProductValidPrices (GETDATE(), GETDATE())
WHERE ProductValidPrices.PriceGroupCode = '
'AN''
AND ProductValidPrices.ItemTypeCode = cdItem.ItemTypeCode
AND ProductValidPrices.ItemCode = cdItem.ItemCode
ORDER BY ProductValidPrices.ColorCode DESC, ProductValidPrices.PriceListDate DESC),0)
, [PROMFIYAT] = ISNULL((SELECT TOP 1 Price
FROM ProductValidPrices (GETDATE(), GETDATE())
WHERE ProductValidPrices.PriceGroupCode = '
'PROM_USD''
AND ProductValidPrices.ItemTypeCode = cdItem.ItemTypeCode
AND ProductValidPrices.ItemCode = cdItem.ItemCode
ORDER BY ProductValidPrices.ColorCode DESC, ProductValidPrices.PriceListDate DESC),0)

, [TLFIYAT] = ISNULL((SELECT TOP 1 Price
FROM ProductValidPrices (GETDATE(), GETDATE())
WHERE ProductValidPrices.PriceGroupCode = '
'TS''
AND ProductValidPrices.ItemTypeCode = cdItem.ItemTypeCode
AND ProductValidPrices.ItemCode = cdItem.ItemCode
ORDER BY ProductValidPrices.ColorCode DESC, ProductValidPrices.PriceListDate DESC),0)
, [NETUSD] = ISNULL((SELECT TOP 1 Price
FROM ProductValidPrices (GETDATE(), GETDATE())
WHERE ProductValidPrices.PriceGroupCode = '
'NETUSD''
AND ProductValidPrices.ItemTypeCode = cdItem.ItemTypeCode
AND ProductValidPrices.ItemCode = cdItem.ItemCode
ORDER BY ProductValidPrices.ColorCode DESC, ProductValidPrices.PriceListDate DESC),0)

, [FREE] = ISNULL((SELECT TOP 1 Price
FROM ProductValidPrices (GETDATE(), GETDATE())
WHERE ProductValidPrices.PriceGroupCode = '
'FREEZONE''
AND ProductValidPrices.ItemTypeCode = cdItem.ItemTypeCode
AND ProductValidPrices.ItemCode = cdItem.ItemCode
ORDER BY ProductValidPrices.ColorCode DESC, ProductValidPrices.PriceListDate DESC),0)

, ProductAtt29 = ProductAttributesFilter.ProductAtt29

, ProductHierarchyLevel01
, ProductHierarchyLevel02
, ProductHierarchyLevel03
, ProductHierarchyLevel04
, ProductHierarchyLevel05
, ProductHierarchyLevel06
, ProductHierarchyLevel07
, ProductHierarchyLevel08
, ProductHierarchyLevel09
, ProductHierarchyLevel10
, CompanyBrandCode = ISNULL((SELECT N'
'{'' + CompanyBrandCode + N''}''
FROM (SELECT DISTINCT CompanyBrandCode
FROM prItemCompanyBrand WITH(NOLOCK)
WHERE prItemCompanyBrand.ItemTypeCode = cdItem.ItemTypeCode
AND prItemCompanyBrand.ItemCode = cdItem.ItemCode
AND prItemCompanyBrand.IsBlocked = 0
AND prItemCompanyBrand.CompanyBrandCode <> SPACE(0)
) AS Sizes
ORDER BY CompanyBrandCode FOR XML PATH('
''')), SPACE(0))
--, cdItem.CreatedDate
--, cdItem.LastUpdatedDate
, cdItem.IsBlocked
FROM cdItem WITH(NOLOCK)
LEFT OUTER JOIN cdItemDesc WITH(NOLOCK) ON cdItemDesc.ItemTypeCode = cdItem.ItemTypeCode AND cdItemDesc.ItemCode = cdItem.ItemCode AND cdItemDesc.LangCode = {LangCode}
LEFT OUTER JOIN ProductHierarchy({LangCode}) ON cdItem.ProductHierarchyID = ProductHierarchy.ProductHierarchyID
LEFT OUTER JOIN ProductAttributesFilter
ON ProductAttributesFilter.ItemTypeCode = 1
AND ProductAttributesFilter.ItemCode = cdItem.ItemCode
LEFT OUTER JOIN ProductAttributeDescriptions({LangCode}) on ProductAttributeDescriptions.ItemTypeCode = CdItem.ItemTypeCode
AND ProductAttributeDescriptions.ItemCode = CdItem.ItemCode
WHERE cdItem.ItemTypeCode = 1
AND cdItem.ItemCode <> SPACE(0)
)DATA
'
)
GO

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

This site uses Akismet to reduce spam. Learn how your comment data is processed.