LOGO INVOICE TABLOSU VE URUN SATIRLARI(SQL STUFF)

On January 4, 2023 By semt20In LOGO ERP, SqlLeave a comment

Invoice tablosu baz alınarak sql stuff komutuyla malzeme/hizmetleri getirmek için:

SELECT 
FIS_MALZEME_HIZMET=	(
Stuff	(
					(
					SELECT DISTINCT
						N', ' + CASE 
									WHEN STL.LINETYPE=0 THEN (SELECT ITEMS.NAME FROM LG_121_ITEMS ITEMS WHERE ITEMS.LOGICALREF=STL.STOCKREF)
									WHEN STL.LINETYPE=4 THEN (SELECT SRVCARD.DEFINITION_ FROM LG_121_SRVCARD SRVCARD WHERE SRVCARD.LOGICALREF=STL.STOCKREF)
								END
					FROM 
						LG_121_01_STLINE STL
					WHERE
						STL.INVOICEREF=I.LOGICALREF AND STL.LINETYPE IN (0/*MALZEME*/,4/*HIZMET*/)
					/*ORDER BY 
						STL.LOGICALREF*/
					FOR XML PATH(''),TYPE
					)
.value('text()[1]','nvarchar(max)'),1,2,N''
				)
)
,
* FROM LG_121_01_INVOICE I

LOGO SQL HAREKET GÖRMEYEN MALZEMELER LİSTESİ

On January 17, 2022 By semt20In LOGO ERP, SqlLeave a comment

Talep: 122 Firmasında hareket görmeyen malzemeler(devir ile 120->121->122 gelmekte yıllık)

SELECT STOCKREF,MALZEMEKODU,MALZEMEADI,SONHAREKETTARIHI FROM (
SELECT *,
   (SELECT MAX(SONHAREKET)
      FROM (VALUES (SONHAREKET120),(SONHAREKET121),(SONHAREKET122)) AS UpdateDate(SONHAREKET)) 
   AS SONHAREKETTARIHI

FROM (
SELECT LOGICALREF AS STOCKREF, CODE AS MALZEMEKODU, NAME AS MALZEMEADI,
ISNULL((SELECT  MAX(DATE_) SONHAREKETTARIHI FROM [ERULKU2016_2020].[dbo].[LG_120_01_STLINE] T  WHERE T.STOCKREF=ITEMS.LOGICALREF AND T.LINETYPE=0 AND T.CANCELLED=0 ),'01-01-2000') SONHAREKET120,
ISNULL((SELECT  MAX(DATE_) SONHAREKETTARIHI FROM [LG_121_01_STLINE] T  WHERE T.STOCKREF=ITEMS.LOGICALREF AND T.LINETYPE=0 AND T.CANCELLED=0 ),'01-01-2000') SONHAREKET121,
ISNULL((SELECT  MAX(DATE_) SONHAREKETTARIHI FROM [LG_122_01_STLINE] T  WHERE T.STOCKREF=ITEMS.LOGICALREF AND T.LINETYPE=0 AND T.CANCELLED=0 ),'01-01-2000') SONHAREKET122
FROM LG_122_ITEMS ITEMS
) AS T
) AS TFINAL WHERE SONHAREKETTARIHI<='2020-01-01'

LOGO SQL MALZEME BIRIM DURUMLARI

On June 21, 2019August 20, 2021 By semt20In LOGO ERP, LOGO OBJECTSLeave a comment

CREATE VIEW BM_MALZEME_BIRIM_DURUMLARI AS
 SELECT DISTINCT LOGICALREF,M.ANAMALZEMEKOD,M.ANAMZELEMEAD, M.BIRIMKODU,M.BIRIMADI,
 ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF)  FROM LG_210_01_STLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK') STLINEDAVARMI,
  ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF)  FROM LG_210_01_ORFLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK') ORFLINEDAVARMI,
 CASE WHEN ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF)  FROM LG_210_01_STLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK') ='YOK'
 AND   ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF)  FROM LG_210_01_ORFLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK')='YOK' THEN 'SIL' ELSE 'SILME'
 END
  SILINEBILIR
  FROM (SELECT
  UNITSETL.CODE BIRIMKODU,
  UNITSETL.NAME BIRIMADI,
 (SELECT CODE FROM LG_210_UNITSETF UNITSETF WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF) ANAMALZEMEKOD,
 (SELECT UNITSETF.NAME FROM LG_210_UNITSETF UNITSETF WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF) ANAMZELEMEAD,
LOGICALREF, CODE, NAME, UNITSETREF, LINENR, MAINUNIT, CONVFACT1, CONVFACT2, WIDTH, LENGTH, HEIGHT, AREA, VOLUME_, WEIGHT, WIDTHREF, LENGTHREF, HEIGHTREF, AREAREF, VOLUMEREF, WEIGHTREF, DIVUNIT, MEASURECODE, GLOBALCODE
 FROM
LG_210_UNITSETL UNITSETL   WITH(NOLOCK) ) M

Blog of Sem

Tag: LG

LOGO INVOICE TABLOSU VE URUN SATIRLARI(SQL STUFF)

LOGO SQL HAREKET GÖRMEYEN MALZEMELER LİSTESİ

LOGO SQL MALZEME BIRIM DURUMLARI