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
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
No comments:
Post a Comment