Kategori arşivi: CANIAS ERP ve Troia

Troia otomatik mail attırmak.

Yayın tarihi
Cevapla

/*RECETE REVIZE TARIHI 30 GUNU GECMIS ISE MAIL AT*/
OBJECT:
STRING TXT,
STRING USR,
STRING SUBTEXT,
STRING RENK,
STRING HAYALI,
INTEGER MATBOY;

MATBOY=STRLEN(TBLSARJ_MATERIAL);
MATBOY=MATBOY-1;
HAYALI=STRSTR(TBLSARJ_MATERIAL,0,MATBOY);
HAYALI=’H’+HAYALI;
RENK=STRSTR(TBLSARJ_VOPTIONS,9,3);
SELECT CASE WHEN CHANGEDBY=” THEN CREATEDAT ELSE CHANGEDAT END AS CREATEDAT, CASE WHEN CHANGEDBY=” THEN DATEDIFF(DAY,CREATEDAT,GETDATE()) ELSE DATEDIFF(DAY,
CHANGEDAT,GETDATE()) END AS TLINE
FROM PNTFBOMASSREV
WHERE BOMNUMBER=HAYALI
AND OPTIONKEY=RENK
INTO TBLREVIZE;
IF SELECTED THEN

IF TBLREVIZE_TLINE > 30 THEN
SELECT *
FROM IASUSERS
WHERE CLIENT = SYS_CLIENT
AND USERNAME = SYS_USER;

SELECT ‘R: ‘+STEXT AS RENK
FROM IASVAROPTIONX
WHERE VARIANTKEY=’L’
AND ATTRIBUTEKEY=’02′
AND OPTIONKEY=RENK
INTO TBLR;

TXT=SSARJ + TOCHAR(13) +TBLSARJ_MATERIAL+ TOCHAR(13) +TBLR_RENK+ TOCHAR(13) +TBLREVIZE_CREATEDAT+ TOCHAR(13) +TBLREVIZE_TLINE+ TOCHAR(13) +SYS_USER;
USR=’fdonmez@penti.com.tr’+TOCHAR(59)+’bkoc@penti.com.tr’+TOCHAR(59)+’laboratuvar@penti.com.tr’+TOCHAR(59)+’stin@penti.com.tr’;
USR=USR+TOCHAR(59)+’boyasarj@penti.com.tr’+TOCHAR(59)+’boyahane1@penti.com.tr’;

SUBTEXT=’IAS Boyahane Reçete Revize 30 günü geçen kontrol..’;
SENDMAIL MESSAGE TXT TOADDRESS USR SUBJECT SUBTEXT FROM IASUSERS_MAILADRESS ;
ENDIF;

ENDIF;

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;

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