Etiket arşivi: troia

Troia Linked Server olarak bağlı Distributor True olan sunucudaki Tablolara işlem yapmak.

Yayın tarihi
Cevapla 
/*DIKISOTOMASYON VERITABANINA KAYDET*/
 OBJECT :
 STRING STR1,
 STRING PARCA,
 INTEGER DETEXPOS;
STR1=’SET XACT_ABORT ON’;
 SELECT *
 FROM MACELLAN..VW_DIKIS_OTOMASYON
 WHERE CONFIRMATION=PCONFIRMATION
 AND CONFIRMPOS=PCONFPOS AND MATERIAL NOT LIKE ‘%P’
 ORDERBY MATERIAL DESC
 INTO TBLIAS ;
 IF NOTSELECTED THEN
 SELECT *
 FROM MAC2011..VW_DIKIS_OTOMASYON
 WHERE CONFIRMATION=PCONFIRMATION
 AND CONFIRMPOS=PCONFPOS AND MATERIAL NOT LIKE ‘%P’
 ORDERBY MATERIAL DESC
 INTO TBLIAS ;
 ENDIF;
IF SELECTED THEN
LOOP AT TBLIAS
 BEGIN
 PARCA=STRSTR(TBLIAS_MATERIAL,5,1);
IF PARCA==’P’ THEN
 RETURN;
 ENDIF;
 IF PARCA==’V’ THEN
 RETURN;
 ENDIF;
APPEND COLUMN CYCLE,INTEGER,2 TO TBLIAS;
 DETEXPOS=STRPOS(TBLIAS_DIKISKODU,’D1′);
IF DETEXPOS>0 THEN
 MOVE 24 TO TBLIAS_CYCLE;
 ENDIF;
DETEXPOS=STRPOS(TBLIAS_DIKISKODU,’D2′);
IF DETEXPOS>0 THEN
 MOVE 29 TO TBLIAS_CYCLE;
 ENDIF;
DETEXPOS=STRPOS(TBLIAS_DIKISKODU,’D3′);
IF DETEXPOS>0 THEN
 MOVE 36 TO TBLIAS_CYCLE;
 ENDIF;
DETEXPOS=STRPOS(TBLIAS_DIKISKODU,’D4′);
IF DETEXPOS>0 THEN
 MOVE 43 TO TBLIAS_CYCLE;
 ENDIF;
 IF SELECTED THEN
 BEGINTRAN;
 EXECUTESQL STR1;
 SELECT *
 FROM [DIKISOTOMASYON\WINTR].[IAS].[dbo].[TBLORGU]
 WHERE 1=2
 INTO TBLOTOMASYON;
MOVE-CORRESPONDING TBLIAS TO TBLOTOMASYON;
 INSERT INTO TBLOTOMASYON;
IF SYS_STATUS THEN
 ROLLBACKTRAN ;
 RETURN;
 ENDIF;
COMMITTRAN;
 ENDIF;
ENDLOOP;
ENDIF;

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