AUDACON Запчасти

Получение списка запчастей, необходимых для проведения работы WorkID

SET @TypeID = 55193; /* Идентификатор автомобиля Audacon */
SET @WorkID = 13701731; /* Идентификатор выбранной работы */

SELECT GenArtNr FROM TB_ItemMpID_GenArt WHERE ItemMpID IN(
SELECT TB_LT_LTInclusive.ItemMpID
FROM (
SELECT _int.WorkID, _int.OnlyMaintenance, _int.CountryID, _int.TypeID, _int.ItemMpID, _int.KorID, _int.BodyQualColID, _int.QualColID, _int.WorkTime, _int.CompositeTime, _int.AddMdWork, TB_LT_WorkPos.WorkPosText WorkPos
FROM TB_TreeType
INNER JOIN TB_LT_LT _int ON TB_TreeType.TypeID = @TypeID AND TB_TreeType.TypeID = _int.TypeID AND (_int.CountryID = 1 OR _int.CountryID = 0)
LEFT OUTER
JOIN TB_LT_LT _nat ON _int.TypeID = _nat.TypeID AND (_nat.CountryID = 1 OR _nat.CountryID = 0) AND _nat.CountryID = 1 AND _int.CountryID <> _nat.CountryID AND _int.ItemMpID = _nat.ItemMpID AND _int.KorID = _nat.KorID
LEFT OUTER
JOIN TB_LT_LTExcludeCountry _ex ON _int.WorkID = _ex.WorkID AND _ex.ExcludeCountryID = 1
LEFT OUTER
JOIN TB_LT_WorkPos ON _int.WorkPosID = TB_LT_WorkPos.WorkPosID
WHERE (_ex.WorkID IS NULL) AND (_nat.WorkID IS NULL)) TB_LT_LT
INNER JOIN TB_LT_LTInclusive ON TB_LT_LT.WorkID = TB_LT_LTInclusive.WorkID
WHERE TB_LT_LTInclusive.WorkID=@WorkID
UNION ALL
SELECT TB_LT_LTExclusive.ItemMpID
FROM (
SELECT _int.WorkID, _int.OnlyMaintenance, _int.CountryID, _int.TypeID, _int.ItemMpID, _int.KorID, _int.BodyQualColID, _int.QualColID, _int.WorkTime, _int.CompositeTime, _int.AddMdWork, TB_LT_WorkPos.WorkPosText WorkPos
FROM TB_TreeType
INNER JOIN TB_LT_LT _int ON TB_TreeType.TypeID = @TypeID AND TB_TreeType.TypeID = _int.TypeID AND (_int.CountryID = 1 OR _int.CountryID = 0)
LEFT OUTER
JOIN TB_LT_LT _nat ON _int.TypeID = _nat.TypeID AND (_nat.CountryID = 1 OR _nat.CountryID = 0) AND _nat.CountryID = 1 AND _int.CountryID <> _nat.CountryID AND _int.ItemMpID = _nat.ItemMpID AND _int.KorID = _nat.KorID
LEFT OUTER
JOIN TB_LT_LTExcludeCountry _ex ON _int.WorkID = _ex.WorkID AND _ex.ExcludeCountryID = 1
LEFT OUTER
JOIN TB_LT_WorkPos ON _int.WorkPosID = TB_LT_WorkPos.WorkPosID
WHERE (_ex.WorkID IS NULL) AND (_nat.WorkID IS NULL)) TB_LT_LT
INNER JOIN TB_LT_LTExclusive ON TB_LT_LT.WorkID = TB_LT_LTExclusive.WorkID
WHERE TB_LT_LTExclusive.WorkID=@WorkID
)


Получение идентификатора автомобиля БД TecDoc


SET @TypeID = 55193; /* Идентификатор автомобиля Audacon */

SELECT TCDTYPE FROM tb_treetype WHERE TypeID=@TypeID LIMIT 1


Получение перечня кросс-запчастей для работы


Информацию, полученную на предыдущих шагах, отправляем в Текдок для получения перечня всех необходимых для работы запчастей

SET @TYP_ID = 26911; /* Идентификатор автомобиля TecDoc */
SET @GA_NR = '7,135,624,626,1607,1608,3015'; /* Идентификаторы запчастей для работ TecDoc */

SELECT DISTINCT LAT_SUP_ID SUP_ID,
SUP_COU.SUP_BRAND SUPPLIER,
GA_NR,
LNG_TXT.TEX_TEXT MASTERBEZ,
IF (ARL_KIND IN (2,3), ARL_DISPLAY_NR, ART_ARTICLE_NR) NUMBER,
1 FIRSTGR,
1 LASTGR,
0 SPECIAL_FILTER
FROM GENERIC_ARTICLES
JOIN DESIGNATIONS LNG_DES
ON LNG_DES.DES_ID = GA_DES_ID AND
LNG_DES.DES_LNG_ID = 16
JOIN DES_TEXTS LNG_TXT
ON LNG_TXT.TEX_ID = LNG_DES.DES_TEX_ID
JOIN LINK_LA_TYP
ON LAT_GA_ID = GA_ID AND
LAT_TYP_ID = @TYP_ID
JOIN LINK_ART ON LA_ID = LAT_LA_ID
JOIN ARTICLES ON ART_ID = LA_ART_ID
JOIN ART_LOOKUP ON ARL_ART_ID = LA_ART_ID
LEFT OUTER JOIN SUPPLIERS SUP_COU
ON SUP_COU.SUP_ID = LAT_SUP_ID
WHERE FIND_IN_SET(GA_NR, @GA_NR);