Merhaba, tedarikçi ve malzeme bazında son satınalma fiyatlarına ve güncel satınalma fiyatlarına ihtiyacınız olursa şu şekilde bir sorgu ile bu bilgiye ulaşabilirsiniz:
SELECT
t.PURINVTYPE
,t.PURINVNUM
,t.MATERIAL
,t.VENDOR
,t.NAME1
,t.DOCDATE
,t.TANIMLISATINALMAFIYATI
,t.TANIMLISATINALMADOVIZCINSI
,t.SONSATINALMAFATURAFIYATI
,t.SONSATINALMAFATURADOVIZCINSI
FROM (SELECT
H.PURINVTYPE
,H.PURINVNUM
,I.MATERIAL
,I.PCURRENCY
,H.DOCDATE
,H.VENDOR
,H.NAME1
,ISNULL(P.PRICE, 0) AS TANIMLISATINALMAFIYATI
,ISNULL(P.CURRENCY, '') AS TANIMLISATINALMADOVIZCINSI
,(I.ITEMTOTAL / I.QUANTITY) AS SONSATINALMAFATURAFIYATI
,H.CURRENCY AS SONSATINALMAFATURADOVIZCINSI
,ROW_NUMBER() OVER (PARTITION BY I.MATERIAL, H.VENDOR, H.CURRENCY ORDER BY H.DOCDATE DESC) AS rn
FROM IASVERITEM I WITH (NOLOCK)
INNER JOIN IASVERHEAD H WITH (NOLOCK)
ON H.CLIENT = '00'
AND H.COMPANY = '01'
AND H.PURINVTYPE = I.PURINVTYPE
AND H.PURINVNUM = I.PURINVNUM
LEFT OUTER JOIN (SELECT
MATERIAL
,VENDOR
,CURRENCY
,PRICE
FROM (SELECT
MATERIAL
,CURRENCY
,PRICE
,VENDOR
,ROW_NUMBER() OVER (PARTITION BY MATERIAL, VENDOR, CURRENCY ORDER BY VALIDUNTIL DESC, VALIDFROM DESC) AS rn
FROM IASPURINF WITH (NOLOCK)
WHERE CLIENT = '00'
AND COMPANY = '01'
AND ISDELETE = 0) AS B
WHERE rn = 1) P
ON P.MATERIAL = I.MATERIAL
AND P.VENDOR = H.VENDOR
AND P.CURRENCY = H.CURRENCY
WHERE I.CLIENT = '00'
AND I.COMPANY = '01'
AND I.PURINVTYPE IN ('FI', 'FD')) AS t
WHERE rn = 1
ORDER BY t.MATERIAL, t.VENDOR
Selamlar.
