Uncategorized

LOGO Devir Fişi – Son Satınalma ve Tanımlı Satınalma Fiyatına Göre Maliyet Bulma

ALTER VIEW HK_SSM AS
WITH TBL AS (
SELECT TOP 10000000000 ROW_NUMBER () OVER (ORDER BY STOCKREF) RN, * FROM (
SELECT STL.STOCKREF , STL.DATE_, STL.LINENET/ STL.AMOUNT PRICE , STL.TRRATE ,STL.TRCURR FROM LG_001_01_STLINE STL WHERE TRCODE IN (1,2,14) AND STL.CANCELLED=0 AND STL.LINETYPE=0 AND ISNULL(STL.AMOUNT,0)>0 AND STL.LINENET>0
UNION
SELECT STL.STOCKREF , STL.DATE_, STL.LINENET/ STL.AMOUNT PRICE , STL.TRRATE ,STL.TRCURR FROM LG_001_02_STLINE STL WHERE TRCODE IN (1,2,14) AND STL.CANCELLED=0 AND STL.LINETYPE=0 AND ISNULL(STL.AMOUNT,0)>0AND STL.LINENET>0
UNION
SELECT STL.STOCKREF , STL.DATE_, STL.LINENET/ STL.AMOUNT PRICE , STL.TRRATE ,STL.TRCURR FROM LG_001_03_STLINE STL WHERE TRCODE IN (1,2,14) AND STL.CANCELLED=0 AND STL.LINETYPE=0 AND STL.AMOUNT>0 AND STL.LINENET>0
UNION
SELECT STL.STOCKREF , STL.DATE_, STL.LINENET/ STL.AMOUNT PRICE , STL.TRRATE ,STL.TRCURR FROM LG_001_04_STLINE STL WHERE TRCODE IN (1,2,14) AND STL.CANCELLED=0 AND STL.LINETYPE=0 AND STL.AMOUNT>0 AND STL.LINENET>0
) T ORDER BY 2,3
)

SELECT ITM.CODE,ITM.NAME
,TBL2.DATE_, TBL2.PRICE TRPRICE ,TBL2.PRICE / CASE WHEN TBL2.TRRATE>0 THEN TBL2.TRRATE ELSE ISNULL(EXCH.RATES1,1) END IDPRICE ,ISNULL(CL.CURCODE,’TRY’) IDCURR
,ROUND(TBL2.PRICE/EXCHRPR.RATES1,5) RPRPRICE,’EUR’ RPRCURR
,TANIMFIYAT.PRICE,TANIMFIYAT.CURRENCY
FROM
(SELECT TBL1.STOCKREF,MAX(TBL1.RN) RN
FROM TBL TBL1
GROUP BY TBL1.STOCKREF)Tx1
INNER JOIN TBL TBL2 ON Tx1.RN=TBL2.RN
INNER JOIN LG_001_ITEMS ITM ON Tx1.STOCKREF=ITM.LOGICALREF
OUTER APPLY (SELECT TOP 1 EX.RATES1 FROM LG_EXCHANGE_001 EX WHERE EX.CRTYPE=TBL2.TRCURR AND EX.EDATE<=TBL2.DATE_ ORDER BY EDATE DESC ) EXCH
OUTER APPLY (SELECT TOP 1 EX.RATES1 FROM LG_EXCHANGE_001 EX WHERE EX.CRTYPE=20 AND EX.EDATE<=TBL2.DATE_ ORDER BY EDATE DESC ) EXCHRPR
LEFT JOIN L_CURRENCYLIST CL ON CL.CURTYPE=TBL2.TRCURR AND CL.FIRMNR=1
OUTER APPLY(SELECT TOP 1 PRC.PRICE,PRC.CURRENCY FROM LG_001_PRCLIST PRC WHERE PRC.PTYPE=2 AND PRC.CARDREF=ITM.LOGICALREF AND PRC.ACTIVE=0 AND TBL2.DATE_ BETWEEN PRC.BEGDATE AND PRC.ENDDATE ORDER BY PRC.LOGICALREF DESC) AS TANIMFIYAT

Bir cevap yazın

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