Etiket arşivi: erp

IAS ta saat kriterine göre anlık stok çekmek

Yayın tarihi
Cevapla 
SELECT I.COMPANY, I.PLANT, I.WAREHOUSE, I.STOCKPLACE,
         I.SPECIALSTOCK, I.MATERIAL,
         MAX( I.SKUNIT ) AS SKUNIT, MAX( M.STEXT ) AS STEXT
         , SUM( I.SKQUANTITY * ( 1 - 2 * I.QPOSTWAY ) ) AS TOTALSTOCK
         , MAX( I.ISVARIANT ) AS ISVARIANT
         , MAX( I.VARIANTKEY ) AS VARIANTKEY
         , MAX( I.VOPTIONS ) AS VOPTIONS
         ,I.PROJECT,I.CONFIRMATION,I.CONFIRMPOS,I.BATCHNUM 

FROM IASINVITEM I WITH (NOLOCK),  IASMATX M WITH(NOLOCK) 

WHERE I.CLIENT = '00'  AND I.COMPANY LIKE '01'  AND I.PLANT LIKE '01'  
AND I.WAREHOUSE LIKE '008'  AND I.STOCKPLACE LIKE '01'  
AND I.SPECIALSTOCK LIKE '%'  AND I.MATERIAL LIKE '%'  
AND I.CREATEDAT <= '11.21.2012 12:15:00'  
AND M.CLIENT = I.CLIENT  
AND M.COMPANY = I.COMPANY  AND M.PLANT = '*'  AND M.LANGU = 'T'  
AND M.MATERIAL = I.MATERIAL  
AND M.VALIDFROM <= I.CREATEDAT  
AND M.VALIDUNTIL >= I.CREATEDAT 
AND I.ISCANCELED = 0 

GROUP BY I.COMPANY, I.PLANT, I.WAREHOUSE, I.STOCKPLACE
         , I.SPECIALSTOCK,  I.MATERIAL
,I.ISVARIANT ,I.VARIANTKEY,I.VOPTIONS,I.PROJECT,I.CONFIRMATION
,I.CONFIRMPOS,I.BATCHNUM

HAVING SUM( I.SKQUANTITY * ( 1 - 2 * I.QPOSTWAY ) ) > 0.01 OR  
SUM( I.SKQUANTITY * ( 1 - 2 * I.QPOSTWAY ) ) < -0.01

ORDER BY I.COMPANY, I.MATERIAL,I.VOPTIONS, I.PLANT, I.WAREHOUSE
         , I.STOCKPLACE, I.SPECIALSTOCK

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

Yayın tarihi
Cevapla 
-- 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