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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
| DECLARE @Facility VARCHAR(3);
SET @Facility='580';
WITH originalBOMTable (Facility, PartNumber, StructureType, ComponentPartNumber)
AS
(
/* J'ai créé cette table comme un préfiltrage de la table des nomenclatures / BOMs en considérant que ça améliorerait l'efficacité du traitement récursif qui se base sur cette table au lieu de la vraie table t_MPDMAT:
La table t_MPDMAT gère les nomenclatures / BOMs (une ligne par lien composant - BOM/produit)
Filtrage selon:
- le statut obsolète ou non du produit cas d'emploi (status ne commençant pas par '9'),
- la validité de la ligne (To date = 99999999),
- l'établissement (Facility)
- le type de structure (ou nomenclature) 'MET'
Dans cette table:
- [PRNO - Product] = produit / cas d'emploi dont la BOM/nomenclature contient l'article [MTNO - Component number]
- [MTNO - Component number] = composant appartenant à la nomenclature de l'article [PRNO - Product]
NB: Pas de boucle possible: un produit ne peut pas appartenir à lui-même quelle que soit la profondeur d'arborescence)
*/
select t_MPDMAT.[FACI - Facility],
t_MPDMAT.[PRNO - Product],
t_MPDMAT.[STRT - Product structure type],
t_MPDMAT.[MTNO - Component number]
from [M3JDTP500].[V_MPDMAT - MF: Connect materials (PM)] as t_MPDMAT
INNER JOIN [M3_ODS_PROD].[M3JDTP500].[V_MITMAS - MF: Item Master File (MM)] AS t_MITMAS ON t_MITMAS.[ITNO - Item number] = t_MPDMAT.[PRNO - Product] AND t_MITMAS.[STAT - Status] NOT LIKE '9%'
where (t_MPDMAT.[TDAT - To date] = 99999999 and t_MPDMAT.[FACI - Facility] = @Facility and t_MPDMAT.[STRT - Product structure type] = 'MET')
),
filteredBOMTable (Facility, PartNumber, StructureType, ComponentPartNumber, BOM_Depth, BOM_Path)
AS
(
/* Table récursive cherchant tous les cas d'emploi successifs des articles appartenant à la table initiale précisée ci-dessous */
SELECT [Facility],
[PartNumber],
[StructureType],
[ComponentPartNumber],
1,
CAST([PartNumber] AS VARCHAR(MAX))
FROM originalBOMTable
WHERE [ComponentPartNumber] in (
/* Table initiale pour la récurrence, basée sur les articles en stock SAV (SAV = Dépôt/Warehouse en '58D' ou '58S') à partir desquels on va chercher récursivement tous les cas d'emploi (parents, grand-parents, etc) */
select distinct [_].[ITNO - Item number]
from [M3JDTP500].[V_MITBAL - MF: Item - warehouse (MB)] as [_]
where ([_].[WHLO - Warehouse] = LEFT(@Facility, 2) + 'D' or [_].[WHLO - Warehouse] = LEFT(@Facility, 2) + 'S') and ([_].[STQT - On-hand balance approved] + [_].[QUQT - On-hand balance for inspection] + [_].[RJQT - Rejected on-hand balance]) <> 0
)
UNION ALL
SELECT oBT.[Facility],
oBT.[PartNumber],
oBT.[StructureType],
fBT.[ComponentPartNumber],
fBT.[BOM_Depth] + 1, /* Calcul de la profondeur d'arborescence */
CAST(oBT.[PartNumber] AS VARCHAR(MAX)) + ' / ' + fBT.[BOM_Path] /* Chaîne contenant l'arborescence complète du produit */
FROM originalBOMTable AS oBT
INNER JOIN filteredBOMTable AS fBT ON (oBT.[ComponentPartNumber] = fBT.[PartNumber])
)
/* Une fois la table récursive filteredBOMTable construite, il reste à compléter la table finale attendue via les infos obtenues par quelques JOIN ci-dessous.
Les tables *_Comp récupèrent des informations appartenant au composant originel du cas d'emploi (issu de la table initiale utilisée pour la récurrence).
Les autres tables récupèrent des informations appartenant au cas d'emploi (parent, grand-parent ou ...). */
SELECT fBT.[Facility],
t_MITBAL_Comp.[WHLO - Warehouse] AS [Warehouse],
fBT.[ComponentPartNumber] AS [Component Part Number],
t_MITMAS_Comp.[ITDS - Name] AS [Component Name],
t_MITMAS_Comp.[STAT - Status] AS [Component Status],
t_MITMAS_Comp.[ACRF - User-defined accounting control object] AS [Component Nature],
t_MITMAS_Comp.[ITGR - Item group] AS [Component Group],
t_CSYTAB_Comp.[TX40 - Description] AS [Component Group Name],
t_MITBAL_Comp.[IDDT - Last receipt date] AS [Last receipt date],
t_MITBAL_Comp.[ODDT - Last issue date] AS [Last issue date],
t_MITBAL_Comp.[SSQT - Safety stock] AS [Component Safety Stock],
t_MITBAL_Comp.[STQT - On-hand balance approved] AS [On-hand balance approved],
t_MITBAL_Comp.[QUQT - On-hand balance for inspection] AS [On-hand balance for inspection],
t_MITBAL_Comp.[RJQT - Rejected on-hand balance] AS [Rejected on-hand balance],
t_MITBAL_Comp.[REQT - Reserved quantity] AS [Reserved quantity],
fBT.[PartNumber] As [Item Part Number],
t_MITMAS.[ITDS - Name] AS [Item Name],
t_MITMAS.[FUDS - Description 2] AS [Item Description],
t_MITMAS.[STAT - Status] AS [Item Status],
t_MPDHED.[STAT - Status] As [Item Structure Status],
t_MITMAS.[ITGR - Item group] AS [Item Group],
t_CSYTAB.[TX40 - Description] AS [Item Group Name],
fBT.[StructureType] AS [Item Structure Type],
fBT.[BOM_Depth],
fBT.[BOM_Path]
FROM filteredBOMTable AS fBT
INNER JOIN [M3_ODS_PROD].[M3JDTP500].[V_MITMAS - MF: Item Master File (MM)] AS t_MITMAS_Comp ON t_MITMAS_Comp.[ITNO - Item number] = fBT.[ComponentPartNumber]
INNER JOIN [M3_ODS_PROD].[M3JDTP500].[V_MITMAS - MF: Item Master File (MM)] AS t_MITMAS ON t_MITMAS.[ITNO - Item number] = fBT.[PartNumber]
INNER JOIN [M3_ODS_PROD].[M3JDTP500].[V_MITBAL - MF: Item - warehouse (MB)] AS t_MITBAL_Comp ON t_MITBAL_Comp.[ITNO - Item number] = fBT.[ComponentPartNumber] AND (t_MITBAL_Comp.[WHLO - Warehouse] = LEFT(fBT.[Facility], 2) + 'D' OR t_MITBAL_Comp.[WHLO - Warehouse] = LEFT(fBT.[Facility], 2) + 'S')
INNER JOIN [M3_ODS_PROD].[M3JDTP500].[V_MPDHED - MF: Product file header (PH)] AS t_MPDHED ON t_MPDHED.[PRNO - Product] = fBT.[PartNumber] AND t_MPDHED.[FACI - Facility] = fBT.[Facility] AND t_MPDHED.[STRT - Product structure type] = fBT.[StructureType]
LEFT OUTER JOIN [M3_ODS_PROD].[M3JDTP500].[V_CSYTAB - MF: System tables (CT)] AS t_CSYTAB_Comp ON t_CSYTAB_Comp.[STCO - Constant value] = 'ITGR' AND t_CSYTAB_Comp.[STKY - Key value] = t_MITMAS_Comp.[ITGR - Item group]
LEFT OUTER JOIN [M3_ODS_PROD].[M3JDTP500].[V_CSYTAB - MF: System tables (CT)] AS t_CSYTAB ON t_CSYTAB.[STCO - Constant value] = 'ITGR' AND t_CSYTAB.[STKY - Key value] = t_MITMAS.[ITGR - Item group]
OPTION (MAXRECURSION 10) |
Requête SQL récursive: problème de performance
Répondre avec citation
Partager