1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
|
WITH MnemonicsPoidsDirecte (IdEle,LectureDirecteBP)
AS
(SELECT IdEle, Idmnemo
FROM ApiMnemonics
INNER JOIN ElementSuffixe AS ElementSuffixePoidsDirecte
ON ElementSuffixePoidsDirecte.IdSuf = ApiMnemonics.IdSuf AND ElementSuffixePoidsDirecte.IdTypeMnemonic = 34)
SELECT SuiviList.IdSuivi, SuiviList.IxSL, SuiviList.SuiviName, SuiviList.Description, IdLot.NumFabClient, IdLot.IdArticle, Article.Code, Article.Designation, IdLot.QProg, IdLot.QBatch, IdLot.QDose, dbo.CommandeSuiviGetProvConcat(SuiviList.IdSuivi) AS [Provenance(s)], ElementDest.Name AS Destination, ElementlisteDosage.Name AS CelluleDosage, articleDosage.Code AS codeDosage, articleDosage.Designation AS DesignationDosage, IdLotProv.QuantiteTheo, ApiMnemonicsQ.IdMnemo AS QenCours, LectureDirecteBP, IdLotChrono.DateDebut, SuiviList.Comment, SuiviInfosEtape.LibelleEtape, elementRouteInfosEtape.LibelleEtape as etatCircuit ,elementRouteInfosEtape.CouleurEtape as CouleurEtatCircuit , SuiviInfosEtape.CouleurEtape, ApiMnemonics.IdMnemo AS ValidationSl
FROM SuiviList
INNER JOIN IdLot ON SuiviList.IDlotActuel = IdLot.IdLot
LEFT OUTER JOIN IdLotProv ON IdLotProv.IdLot = IdLot.IdLot AND IdLotProv.etape = 1 AND IdLotProv.IdSuiviToUsed = SuiviList.IdSuivi
LEFT OUTER JOIN Article AS articleDosage on idlotprov.idarticle = articleDosage.idarticle
LEFT OUTER JOIN IdLotChrono ON IdLotChrono.IdChrono = SuiviList.IdChrono
LEFT OUTER JOIN SuiviInfosEtape ON SuiviList.IdSuivi = SuiviInfosEtape.IdSuivi AND SuiviList.EtapeActuel = SuiviInfosEtape.Etape
LEFT OUTER JOIN ApiMnemonics ON SuiviList.IDEle = ApiMnemonics.IdEle
INNER JOIN ElementSuffixe ON ApiMnemonics.IdSuf = ElementSuffixe.IdSuf AND ElementSuffixe.IdTypeMnemonic = 14
INNER JOIN Article ON Article.IdArticle = IdLot.IdArticle
LEFT OUTER JOIN IdLotDest ON IdLotDest.Etape = 1 AND IdLotDest.IdLot = SuiviList.IDlotActuel AND SuiviList.IdSuivi = IdLotDest.IdSuiviToUsed
LEFT OUTER JOIN ElementList AS ElementDest ON IdLotDest.IdEle = ElementDest.IDEle
LEFT OUTER JOIN ApiMnemonics AS ApiMnemonicsQ ON SuiviList.IDEle = ApiMnemonicsQ.IdEle
INNER JOIN ElementSuffixe AS ElementSuffixeq ON ApiMnemonicsQ.IdSuf = ElementSuffixeq.IdSuf AND ElementSuffixeq.IdTypeMnemonic = 12
LEFT OUTER JOIN ElementList AS ElementlisteDosage ON IdLotProv.IdEle = ElementlisteDosage.IDEle
LEFT OUTER JOIN elementRouteList on elementRouteList.ixroute = SuiviList.ixroute
LEFT OUTER JOIN elementRouteInfosEtape on elementRouteList.EtapeActuel = elementRouteInfosEtape.etape
LEFT OUTER JOIN MnemonicsPoidsDirecte AS ApiMnemonicsPoidsDirecte ON SuiviList.IDEle = ApiMnemonicsPoidsDirecte.IdEle |
Partager