Ticari sistem üzerinden üretim emirleri listesini çekmek için aşağıdaki sorguyu kullanabilirsiniz.
CREATE VIEW [dbo].[Uretim_Emirleri_Listesi]
as
SELECT P.DATE_ AS 'Uretim Emri Tarihi',P.FICHENO as 'Uretim Emri No',P.SPECODE AS 'Uretim Emri Turu'
,(CASE P.STATUS WHEN 0 THEN 'Baslamadı' WHEN 1 THEN 'Devam Ediyor' WHEN '3' THEN 'Kapandı' END) AS 'Uretim Emri Durumu'
,I.CODE as 'Malzeme Kodu',I.PRODUCERCODE as 'Eski Kod',I.NAME as 'Malzeme Adı'
,(CASE SUBSTRING(I.CODE,10,1) WHEN 'A' THEN P.PLNAMOUNT WHEN 'K'
THEN
P.PLNAMOUNT / (SELECT CONVFACT2 FROM
LG_271_ITMUNITA ITM (NOLOCK) WHERE I.LOGICALREF = ITM.ITEMREF AND ITM.LINENR= 2)
END) AS 'Plan_Ur_Adt'
,(CASE SUBSTRING(I.CODE,10,1) WHEN 'K' THEN P.PLNAMOUNT WHEN 'A'
THEN
P.PLNAMOUNT * (SELECT CONVFACT2 FROM
LG_271_ITMUNITA ITM (NOLOCK) WHERE I.LOGICALREF = ITM.ITEMREF AND ITM.LINENR= 2)
END) AS 'Plan_Ur_Kg'
,(CASE SUBSTRING(I.CODE,10,1) WHEN 'A' THEN (SELECT SUM(STL.AMOUNT) FROM LG_271_01_STLINE STL (NOLOCK) WHERE STL.PRODORDERREF = P.LOGICALREF AND STL.LPRODSTAT = 0 AND STL.TRCODE = 13) WHEN 'K'
THEN (SELECT SUM(STL.AMOUNT) FROM LG_271_01_STLINE STL (NOLOCK) WHERE STL.PRODORDERREF = P.LOGICALREF AND STL.LPRODSTAT = 0 AND STL.TRCODE = 13) / (SELECT CONVFACT2 FROM LG_271_ITMUNITA ITM (NOLOCK) WHERE I.LOGICALREF = ITM.ITEMREF AND ITM.LINENR= 2)
END) AS 'Ger_Ur_Adt'
,(CASE SUBSTRING(I.CODE,10,1) WHEN 'K' THEN (SELECT SUM(STL.AMOUNT) FROM LG_271_01_STLINE STL (NOLOCK) WHERE STL.PRODORDERREF = P.LOGICALREF AND STL.LPRODSTAT = 0 AND STL.TRCODE = 13) WHEN 'A'
THEN (SELECT SUM(STL.AMOUNT) FROM LG_271_01_STLINE STL (NOLOCK) WHERE STL.PRODORDERREF = P.LOGICALREF AND STL.LPRODSTAT = 0 AND STL.TRCODE = 13) * (SELECT CONVFACT2 FROM LG_271_ITMUNITA ITM (NOLOCK) WHERE I.LOGICALREF = ITM.ITEMREF AND ITM.LINENR= 2)
END) AS 'Ger_Ur_Kg'
,ISNULL(O.NAME,0) AS 'Operasyon Adı'
,(CASE D.LINESTATUS WHEN 1 THEN 'DEVAM EDIYOR' WHEN 3 THEN 'TAMAMLANDI' END) AS 'Operasyon Durumu'
,(CASE(SELECT T.TAMIR FROM TAMIR T (NOLOCK) WHERE T.LOGREF = P.LOGICALREF) WHEN 'TÇ' THEN 'Tamire Cıktı' WHEN 'TD' THEN 'Tamirden Döndü' END) AS 'Tamir Durumu'
,(CASE XT11.FIRMNR WHEN '271' THEN (SELECT ORF271.DATE_ FROM LG_271_01_ORFICHE ORF271
(NOLOCK) WHERE XT11.ORDFICHEREF = ORF271.LOGICALREF)
WHEN
'371' THEN (SELECT ORF371.DATE_ FROM LG_371_01_ORFICHE ORF371
(NOLOCK) WHERE XT11.ORDFICHEREF = ORF371.LOGICALREF) END ) AS 'Siparis Tarihi'
,(CASE XT11.FIRMNR WHEN '271' THEN (SELECT ORF271.FICHENO
FROM LG_271_01_ORFICHE ORF271 (NOLOCK) WHERE XT11.ORDFICHEREF
= ORF271.LOGICALREF)
WHEN
'371' THEN (SELECT ORF371.FICHENO
FROM LG_371_01_ORFICHE ORF371 (NOLOCK) WHERE XT11.ORDFICHEREF
= ORF371.LOGICALREF) END ) AS 'Siparis No'
,(CASE XT11.FIRMNR WHEN '271' THEN (SELECT C271.DEFINITION_
FROM LG_271_01_ORFICHE ORF271 (NOLOCK) , LG_271_CLCARD C271 (NOLOCK) WHERE ORF271.CLIENTREF = C271.LOGICALREF AND XT11.ORDFICHEREF = ORF271.LOGICALREF)
WHEN
'371' THEN (SELECT C371.DEFINITION_
FROM LG_371_01_ORFICHE ORF371 (NOLOCK),
LG_371_CLCARD C371 (NOLOCK) WHERE ORF371.CLIENTREF
= C371.LOGICALREF
AND XT11.ORDFICHEREF
= ORF371.LOGICALREF) END ) AS 'Musteri Adı'
,(CASE XT11.FIRMNR WHEN '271' THEN
(CASE SUBSTRING(I.CODE,10,1) WHEN 'A' THEN (SELECT ORFL271.AMOUNT
FROM LG_271_01_ORFLINE ORFL271 (NOLOCK) WHERE XT11.ORFLINEREF
= ORFL271.LOGICALREF)
WHEN 'K' THEN (SELECT ORFL271.AMOUNT
FROM LG_271_01_ORFLINE ORFL271 (NOLOCK) WHERE XT11.ORFLINEREF
= ORFL271.LOGICALREF)
*(SELECT CONVFACT2 FROM
LG_271_ITMUNITA ITM (NOLOCK) WHERE I.LOGICALREF = ITM.ITEMREF AND ITM.LINENR= 2) END)
WHEN
'371' THEN (CASE SUBSTRING(I.CODE,10,1) WHEN 'A' THEN (SELECT ORFL371.AMOUNT FROM
LG_371_01_ORFLINE ORFL371 (NOLOCK) WHERE XT11.ORFLINEREF = ORFL371.LOGICALREF)
WHEN 'K' THEN (SELECT ORFL371.AMOUNT
FROM LG_371_01_ORFLINE ORFL371 (NOLOCK) WHERE XT11.ORFLINEREF
= ORFL371.LOGICALREF)
*(SELECT CONVFACT2 FROM
LG_271_ITMUNITA ITM (NOLOCK) WHERE I.LOGICALREF = ITM.ITEMREF AND ITM.LINENR= 2) END)END) AS 'Siparis Miktarı_Adt'
,(CASE XT11.FIRMNR WHEN '271' THEN
(CASE SUBSTRING(I.CODE,10,1) WHEN 'K' THEN (SELECT ORFL271.AMOUNT
FROM LG_271_01_ORFLINE ORFL271 (NOLOCK) WHERE XT11.ORFLINEREF
= ORFL271.LOGICALREF)
WHEN 'A' THEN (SELECT ORFL271.AMOUNT
FROM LG_271_01_ORFLINE ORFL271 (NOLOCK) WHERE XT11.ORFLINEREF
= ORFL271.LOGICALREF)
/(SELECT CONVFACT2 FROM
LG_271_ITMUNITA ITM (NOLOCK) WHERE I.LOGICALREF = ITM.ITEMREF AND ITM.LINENR= 2) END)
WHEN
'371' THEN (CASE SUBSTRING(I.CODE,10,1) WHEN 'K' THEN (SELECT ORFL371.AMOUNT FROM
LG_371_01_ORFLINE ORFL371 (NOLOCK) WHERE XT11.ORFLINEREF = ORFL371.LOGICALREF)
WHEN 'A' THEN (SELECT ORFL371.AMOUNT
FROM LG_371_01_ORFLINE ORFL371 (NOLOCK) WHERE XT11.ORFLINEREF
= ORFL371.LOGICALREF)
/(SELECT CONVFACT2 FROM
LG_271_ITMUNITA ITM (NOLOCK) WHERE I.LOGICALREF = ITM.ITEMREF AND ITM.LINENR= 2) END)END) AS 'Siparis Miktarı_Kg'
,SA.Seviye6Kod,SA.Seviye6Ad,SA.Seviye5Kod,SA.Seviye5Ad,SA.Seviye4Kod,SA.Seviye4Ad,SA.Seviye3Kod,SA.Seviye3Ad,SA.Seviye2Kod,SA.Seviye2Ad
FROM
LG_271_PRODORD P (NOLOCK) LEFT OUTER JOIN
ISEMRIDURUM ISD (NOLOCK) ON P.LOGICALREF = ISD.PRODORDREF LEFT OUTER JOIN
LG_271_DISPLINE D (NOLOCK) ON D.LOGICALREF = ISD.LOGREF LEFT OUTER JOIN
LG_271_OPERTION O (NOLOCK) ON D.OPERATIONREF = O.LOGICALREF INNER JOIN
LG_271_ITEMS I (NOLOCK) ON P.ITEMREF = I.LOGICALREF LEFT OUTER JOIN
LG_XT011001_271 XT11 (NOLOCK) ON P.LOGICALREF = XT11.PRODREF INNER JOIN
SINIF_AGACI SA (NOLOCK) ON I.LOGICALREF = SA.LOGICALREF
