SİPARİŞ – Blog of Sem

SELECT  DISTINCT  

  
	(SELECT CAST(C.NR AS VARCHAR)+'-'+C.NAME FROM L_CAPIDIV C WHERE C.FIRMNR=121 AND C.NR = ORFIC.BRANCH) ISYERI,   
/*SLSMC.CODE+'-'+*/SLSMC.DEFINITION_ SIPARIS_SATIS_ELEMANI,
	ORFIC.DATE_ SIPARIS_TARIH,	  
	ORFIC.DOCODE SIPARIS_BELGENO, 
	ORFIC.FICHENO SIPARIS_FISNO, 
	ORFIC.TRADINGGRP SIPARIS_TICARIISLEMGRUBU, 
	ORFIC.SPECODE SIPARIS_OZELKOD, 
	ORFIC.CYPHCODE SIPARIS_YETKIKODU, 
  
	CLNTC.CODE CARI_KOD, 
	CLNTC.DEFINITION_ CARI_AD,
	(SELECT SUM(ORF2.AMOUNT) FROM LG_121_01_ORFLINE ORF2 WHERE ORF2.ORDFICHEREF=ORFIC.LOGICALREF AND LINETYPE=4) FIS_TOPLAM_SIPARIS_MIKTAR,
	(SELECT SUM(ORF2.SHIPPEDAMOUNT) FROM LG_121_01_ORFLINE ORF2 WHERE ORF2.ORDFICHEREF=ORFIC.LOGICALREF AND LINETYPE=4) FIS_TOPLAM_SEVKEDILEN_MIKTAR
	
	/*,
	MALZEME DETAYLARI BAŞ
	USLINE.CODE BIRIM, 
	SERVC.CODE HIZMET_KOD, 
	SERVC.DEFINITION_ HIZMET_AD, 
	OTRNS.AMOUNT SIPARIS_MIKTAR, 
	OTRNS.SHIPPEDAMOUNT SIPARIS_SEVKEDILENMIKTAR
	MALZEME DETAYLARI SON
	*/




	/*, 
	ORFIC.CUSTORDNO,
	ORFIC.STATUS,
	ORFIC.WITHPAYTRANS, 
	ORFIC.FACTORYNR, 
	ORFIC.DEPARTMENT, 
	ORFIC.LOGICALREF, 
	ORFIC.TIME_, 
	ORFIC.SOURCEINDEX, 
	ORFIC.TOTALADDTAX, 
	ORFIC.TRRATE, 
	ORFIC.LINEEXCTYP,
	ORFIC.REPORTRATE, 
	OTRNS.PRCURR, 
	OTRNS.DATE_, 
	OTRNS.SPECODE, 
	OTRNS.SOURCEINDEX, 
	OTRNS.LOGICALREF, 
	OTRNS.ORDFICHEREF, 
	OTRNS.STOCKREF, 
	OTRNS.DETLINE, 
	OTRNS.LINETYPE, 
	OTRNS.REPORTRATE, 
	OTRNS.PRICE, 
	OTRNS.TOTAL, 
	OTRNS.PRPRICE, 
	OTRNS.AMOUNT SIPARIS_MIKTAR, 
	OTRNS.UOMREF, 
	OTRNS.SHIPPEDAMOUNT SIPARIS_SEVKEDILENMIKTAR, 
	OTRNS.CPSTFLAG, 
	OTRNS.CLOSED, 
	OTRNS.ONVEHICLE, 
	OTRNS.DISTRESERVED, 
	OTRNS.LINENO_, 
	OTRNS.UINFO1, 
	OTRNS.UINFO2, 
	OTRNS.ADDTAXAMOUNT,  
	ITMSC.CODE, 
	ITMSC.NAME, 
	ITMSC.ACTIVE, 
	ITMSC.SPECODE, 
	ITMSC.CYPHCODE, 
	ITMSC.PRODUCERCODE, 
	ITMSC.STGRPCODE, 
	ITMSC.LOGICALREF, 
	ITMSC.CARDTYPE, 
	SERVC.LOGICALREF, 
	VARIANT.CODE, 
	VARIANT.NAME,
	CLNTC.SPECODE, 
	CLNTC.CYPHCODE, 
	CLNTC.ACTIVE, 
	CLNTC.LOGICALREF, 
	USLINE.LOGICALREF, 
	USLINE.WIDTHREF, 
	USLINE.LENGTHREF, 
	USLINE.HEIGHTREF, 
	USLINE.AREAREF, 
	USLINE.VOLUMEREF, 
	USLINE.WEIGHTREF, 
	USLINE.UNITSETREF, 
	USLINE.MAINUNIT, 
	USLINE.CONVFACT1, 
	USLINE.CONVFACT2, 
	USLINE.CODE, 
	PROJECT.CODE, 
	PROJECT.NAME, 
	PROJECT.LOGICALREF, 
	SHPINF.CODE, 
	SHPINF.NAME  */
FROM 
  LG_121_01_ORFICHE ORFIC WITH(NOLOCK) 
  LEFT OUTER JOIN LG_121_SHIPINFO SHPINF WITH(NOLOCK) ON (
    ORFIC.SHIPINFOREF = SHPINF.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_01_ORFLINE OTRNS WITH(NOLOCK) ON (
    ORFIC.LOGICALREF = OTRNS.ORDFICHEREF
  ) 
  LEFT OUTER JOIN LG_121_ITEMS ITMSC WITH(NOLOCK) ON (
    OTRNS.STOCKREF = ITMSC.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_SRVCARD SERVC WITH(NOLOCK) ON (
    OTRNS.STOCKREF = SERVC.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_VARIANT VARIANT WITH(NOLOCK) ON (
    OTRNS.VARIANTREF = VARIANT.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_CLCARD CLNTC WITH(NOLOCK) ON (
    OTRNS.CLIENTREF = CLNTC.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_UNITSETL USLINE WITH(NOLOCK) ON (OTRNS.UOMREF = USLINE.LOGICALREF) 
  LEFT OUTER JOIN LG_121_PROJECT PROJECT WITH(NOLOCK) ON (
    OTRNS.PROJECTREF = PROJECT.LOGICALREF
  ) 
   LEFT OUTER JOIN LG_SLSMAN SLSMC WITH(NOLOCK) ON (
    ORFIC.SALESMANREF = SLSMC.LOGICALREF
  ) 
WHERE 
  (
    ORFIC.WITHPAYTRANS IN (1)
  ) 
  AND (
    ORFIC.STATUS IN (4)
  ) 
  AND (
    ORFIC.WITHPAYTRANS IN (1)
  )  
  AND ORFIC.TRCODE = 1 
  AND (SLSMC.CODE IN ( /*M=*/'2021/2022' ,/*G*/ '2021-22'))  
 



SELECT 
  CASE WHEN ORFICHE.TRCODE = 1 THEN 'Satış/Pazarlama Siparişleri' WHEN ORFICHE.TRCODE = 2 THEN 'Alış/Satınalma Siparişleri' ELSE 'Diğer' END AS 'Sipariş Türü', 
  ORFICHE.DATE_ AS 'Fiş Tarihi', 
  ORFICHE.FICHENO AS 'Fiş No', 
  CLCARD.CODE AS 'Cari Kodu', 
  CLCARD.DEFINITION_ as 'Cari Adı', 
  ITEMS.CODE AS 'Stok Kodu', 
  ITEMS.DEFINITION_ AS 'Stok Adı', 
  ORFLINE.AMOUNT AS 'Sipariş Miktar', 
  ISNULL(
    (
      select 
        sum(amount) 
      FROM 
        LG_121_01_STLINE 
      WHERE 
        ORDTRANSREF = ORFLINE.LOGICALREF
    ), 
    0
  ) SEVKMIKTAR 
FROM 
  LG_121_01_ORFICHE ORFICHE 
  INNER JOIN LG_121_01_ORFLINE ORFLINE ON ORFICHE.LOGICALREF = ORFLINE.ORDFICHEREF 
  AND ORFICHE.CLIENTREF = ORFLINE.CLIENTREF 
  INNER JOIN LG_121_CLCARD CLCARD ON ORFICHE.CLIENTREF = CLCARD.LOGICALREF 
  INNER JOIN LG_121_SRVCARD ITEMS ON ORFLINE.STOCKREF = ITEMS.LOGICALREF 
WHERE 
  (ORFLINE.CANCELLED = 0) 
  AND (ORFLINE.STATUS = 4) 
  AND (ORFLINE.CLOSED = 0)

SENARYO

Bir Carinin borcu 50 gün ve daha fazlaysa job ile risk bilgilerinde siparişe kapatılsın güncellenmesi yapılacak (LOGO FIRMA ADI 210).
Job çalışma süresi her gün sabah saat 7

İŞLEYİŞ

Önce bu kritere uyan cariler view ile belirlenecek (VIEW ADI: CARIRISKSIPARISKAPAT_210)
Job ile bu view çağrılıp gerekli güncelleme işlemi yapılacak

CREATE VIEW CARIRISKSIPARISKAPAT_210 AS 
SELECT * FROM (
SELECT 
CLLOGICALREF=		C.LOGICALREF,
[Cari Kodu]=		C.CODE,
[Cari Ünvanı]=		C.DEFINITION_,
[İşlem]=			ISNULL((
						SELECT TOP 1
							CASE WHEN DATEDIFF(DAY,P.DATE_,GETDATE())>=50 THEN 'Siparişe Kapat'  END  ISLEM
						FROM LG_210_01_PAYTRANS P WITH(NOLOCK)
						WHERE 
							P.MODULENR=4 AND P.TRCODE IN (7,8) AND P.PAID=0 AND P.CARDREF=C.LOGICALREF AND P.CANCELLED=0 ORDER BY P.DATE_ ASC
					),''),
[Siparişte]=		ISNULL((SELECT 
							CASE CLR.ORDRISKOVER 
								WHEN 0 THEN 'İşleme Devam Edilecek' 
								WHEN 1 THEN 'Kullanıcı Uyarılacak' 
								WHEN 2 THEN 'İşlem Durdurulacak' 
							END FROM LG_210_01_CLRNUMS CLR WITH(NOLOCK) WHERE CLR.CLCARDREF=C.LOGICALREF),'')
FROM LG_210_CLCARD C WITH(NOLOCK) WHERE ACTIVE=0
) AS T WHERE T.İşlem<>'' and Siparişte not like '%Durdurulacak'

/*JOB SORGUSU UPDATE İŞLEMİ*/  
SET QUOTED_IDENTIFIER ON
UPDATE LG_210_01_CLRNUMS SET ORDRISKOVER = 2
WHERE CLCARDREF IN  (SELECT B.CLLOGICALREF FROM CARIRISKSIPARISKAPAT_210 B )

Blog of Sem

Tag: SİPARİŞ

LOGO SQL SİPARİŞ MİKTAR BEKLEYEN MİKTAR

LOGO CARI RISK SIPARIS KAPAT SQL