Bozulan IASINVSTOCK u düzelten SQL cümleciği

-- IASINVSTOCK silinip

DELETE FROM IASINVSTOCK 
WHERE  CLIENT = '00' AND COMPANY = '01' 
AND MATERIAL LIKE 'CP____'

-- IASINVITEM'dan oluşturulmuş stoklar alınıyor.
INSERT INTO IASINVSTOCK 
SELECT CLIENT, COMPANY, PLANT, MATERIAL, WAREHOUSE, STOCKPLACE
, SPECIALSTOCK, BATCHNUM, MAX( ENTRYDATE ) AS ENTRYDATE
, 0 AS ISLOCKED
, SUM( ( 1 - 2 * QPOSTWAY )  * SKQUANTITY ) AS TOTALSTOCK             
, SUM( CASE  WHEN VALUEFIELD = 1 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS AVAILSTOCK
, SUM( CASE  WHEN VALUEFIELD = 4 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS TRANSTOCK
, SUM( CASE  WHEN VALUEFIELD = 3 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS BLOCKSTOCK
, SUM( CASE  WHEN VALUEFIELD = 2 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS QUALITYSTOCK
, SUM( CASE  WHEN VALUEFIELD = 5 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS RESERVESTOCK
, MAX( ENTRYDATE ) AS LINVDATE, 0 AS LASTMONINV, 0 AS PREVMONINV
, SUM( CASE  WHEN VALUEFIELD = 6 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS REVISESTOCK
, 1 AS PUNIT, MAX( QUNIT ) AS QUNIT, 0 AS PRICE
, MAX( EXPIRYDATE ) AS EXPIRYDATE
, CONVERT( DATETIME, '01.01.2030' ) AS BATCHEXPDATE
, SUM( ( 1 - 2 * QPOSTWAY )  * QUANTITYX ) AS TOTALSTOCK2            
, SUM( CASE  WHEN VALUEFIELD = 1 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS AVAILSTOCK2
, SUM( CASE  WHEN VALUEFIELD = 4 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS TRANSTOCK2
, SUM( CASE  WHEN VALUEFIELD = 3 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS BLOCKSTOCK2
, SUM( CASE  WHEN VALUEFIELD = 2 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS QUALITYSTOCK2
, SUM( CASE  WHEN VALUEFIELD = 5 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS RESERVESTOCK2
, SUM( CASE  WHEN VALUEFIELD = 6 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS REVISESTOCK2
, MAX( QUNITX ) AS QUNITX, MAX( ISVARIANT ) AS ISVARIANT
, VARIANTKEY, VOPTIONS
FROM IASINVITEM 
WHERE  CLIENT = '00' AND COMPANY = '01' AND MATERIAL LIKE 'CP____'
AND ISCANCELED=0
GROUP BY  CLIENT, COMPANY, PLANT, MATERIAL, WAREHOUSE, STOCKPLACE
, SPECIALSTOCK, BATCHNUM, VARIANTKEY, VOPTIONS
HAVING SUM( ( 1 - 2 * QPOSTWAY )  * SKQUANTITY ) != 0

Bir Cevap Yazın

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