SELECT
(SELECT I.CODE FROM LG_121_ITEMS I WHERE I.LOGICALREF=SS3.SREF) URUNKODU ,
(SELECT I.NAME FROM LG_121_ITEMS I WHERE I.LOGICALREF=SS3.SREF) URUNKODU ,
SS3.MLYT, ST4.AMOUNT
FROM LG_121_01_STLINE AS ST4 LEFT OUTER JOIN
(SELECT ST3.VATMATRAH / ST3.AMOUNT AS MLYT, SS2.SREF FROM
(SELECT MAX(DISTINCT ST2.LOGICALREF) AS LREF, ST2.STOCKREF AS SREF FROM
(SELECT ST1.STOCKREF, MAX(ST1.DATE_) AS DATE_ FROM LG_117_01_STLINE AS ST1 WHERE
(ST1.IOCODE =1) AND (ST1.LINETYPE = 0) AND (ST1.CANCELLED = 0) AND (ST1.PRICE <> 0) GROUP BY ST1.STOCKREF)
AS SS1 LEFT OUTER JOIN LG_117_01_STLINE AS ST2 ON SS1.DATE_ = ST2.DATE_ AND SS1.STOCKREF = ST2.STOCKREF
WHERE (ST2.LINETYPE = 0) AND (ST2.IOCODE =1) AND (ST2.CANCELLED = 0) AND (ST2.PRICE <> 0) GROUP BY ST2.STOCKREF) AS SS2
LEFT OUTER JOIN LG_117_01_STLINE AS ST3 ON SS2.SREF = ST3.STOCKREF AND SS2.LREF = ST3.LOGICALREF) AS SS3
ON ST4.STOCKREF = SS3.SREF
WHERE (ST4.TRCODE = 14) AND (ST4.CANCELLED = 0) AND (ST4.LINETYPE = 0) AND (SS3.MLYT <> 0) AND (ST4.LINENET = 0)
Tag: devir
LOGO DEVİR MALZEME FİŞİ DEVRALINAN STLINEDAN UPDATE İŞLEMİ
Devralınan firmanın stlineinana göre devredilen firmadaki devir fişini güncelleme
/*DEVRALINAN FİRMA*/
SELECT STOCKREF,
(
/*MAX LOGICALREFLERIN VATMATRAH/AMOUNTU YANI PRICEI*/
SELECT ROUND(VATMATRAH/NULLIF(AMOUNT,0),2) FROM LG_301_01_STLINE S2 WHERE S2.LOGICALREF=T.MAXDATELOGICALREF ) AS DEVRALINANPRICE
FROM (
SELECT *,
(
/*EN SON HAREKET TARİHİNİN LOGICALREFI*/
SELECT TOP 1 S2.LOGICALREF FROM LG_301_01_STLINE S2 WHERE
S2.STOCKREF=S1.STOCKREF AND S2.DATE_=S1.MAXDATE_ AND S2.TRCODE IN (1,14)
ORDER BY S2.LOGICALREF DESC ) AS MAXDATELOGICALREF
FROM (
/*STOCKREFLER VE EN SON HAREKET TARİHLERİ GRUPLU (FİŞ TİPLERİ ALIM VE DEVİR)*/
SELECT STOCKREF,MAX(DATE_) MAXDATE_
FROM LG_301_01_STLINE S1 WHERE TRCODE IN (1,14)
GROUP BY STOCKREF) AS S1
) T
-------------------------------
/*DEVREDİLEN FİRMA-HER UPDATE İŞLEMİ TEK TEK SIRAYLA*/
UPDATE LG_401_01_STLINE SET PRICE=/*DEVRALINANPRICE*/0 FROM WHERE TRCODE=14 AND STOCKREF=/*DEVRALINANSTOCKREF*/0
UPDATE LG_401_01_STLINE SET VATMATRAH=PRICE*AMOUNT WHERE TRCODE=14 AND STOCKREF=/*DEVRALINANSTOCKREF*/0
UPDATE LG_401_01_STLINE SET LINENET=VATMATRAH, TOTAL=VATMATRAH WHERE TRCODE=14 AND STOCKREF=/*DEVRALINANSTOCKREF*/0
Blog of Sem 