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
| SELECT
MKO.IPTDAT_0 as 'Date Imputation',
substring(convert(varchar(4),YEAR(MKO.IPTDAT_0)),3,2) + CASE
WHEN DATEPART(WK,MKO.IPTDAT_0)<10 then '0'+CAST(DATEPART(WK,MKO.IPTDAT_0) AS VARCHAR(1))
WHEN DATEPART(WK,MKO.IPTDAT_0)>=10 then CAST(DATEPART(WK,MKO.IPTDAT_0) AS VARCHAR(2))
END as 'Semaine Saisie',
ITM.TCLCOD_0 as 'Catégorie',
ITM.TSICOD_0 as 'BU',
AX1.TEXTE_0 as 'Famille Stat 2',
AX2.TEXTE_0 as 'Famille Stat 3',
AX3.TEXTE_0 as 'Famille Stat 4',
AX4.TEXTE_0 as 'Famille Stat 5',
ITM.ITMREF_0 as 'Référence Article',
ITM.ITMDES1_0 as 'Désignation Article',
MKO.ITMREF_0 as 'Gamme',
MKO.MFGTRKNUM_0 as 'Numéro Suivi',
MKO.MFGNUM_0 as 'Numéro Ordre',
MKO.OPENUM_0 as 'Séquence',
MKO.CPLWST_0 as 'Poste de Charge',
AWK.TEXTE_0 as 'Intitulé Poste de Charge',
WRK.WCR_0 as 'Centre de Charge',
ACC.TEXTE_0 as 'Intitulé Centre de Charge',
MKO.EMPNUM_0 as 'Matricule Employé',
MKO.CPLOPETIM_0 as 'Temps Déclaré',
MFG.ROUNUM_0 as 'Gamme Lancée',
MKO.CPLQTY_0 as 'Quantité Réalisé OK',
MFO.EXTUNTTIM_0 as 'Temps Unitaire Théorique',
MFO.EXTUNTTIM_0 * MKO.CPLQTY_0 as 'Temps Ligne Théorique',
MFG.MFGSTA_0 as 'Statut Ordre de Fabrication',
MFG.MFGTRKFLG_0 as 'FLAG Suivi',
TMA.EMPDES_0 as 'Nom Employé',
TMA.ZBURAT_0 as 'BU de Rattachement',
TMA.ZSERAT_0 as 'Secteur de Rattachement',
MFI.ITMREF_0 as 'Référence Article Lancé',
MKO.DACHOU_0 as 'Heure Déclaration',
MKO.CREUSR_0 as 'Opérateur Déclaration'
FROM MFGOPETRK MKO
INNER JOIN MFGITM MFI ON MKO.MFGNUM_0 = MFI.MFGNUM_0
INNER JOIN MFGOPE MFO ON MKO.MFGNUM_0 = MFO.MFGNUM_0 AND MKO.OPENUM_0 = MFO.OPENUM_0 AND MKO.OPESPLNUM_0 = MFO.OPESPLNUM_0
LEFT OUTER JOIN WORKSTATIO WRK ON MKO.CPLWST_0 = WRK.WST_0 AND MKO.MFGFCY_0 = WRK.WCRFCY_0
LEFT OUTER JOIN TABMAT TMA ON MKO.EMPNUM_0 = TMA.EMPNUM_0
INNER JOIN MFGHEAD MFG ON MFI.MFGNUM_0 = MFG.MFGNUM_0
INNER JOIN ITMMASTER ITM ON MFI.ITMREF_0 = ITM.ITMREF_0
INNER JOIN TABWRKCTR TWC ON WRK.WCR_0 = TWC.WCR_0
LEFT OUTER JOIN AVWTEXTRA AX1 ON
AX1.IDENT1_0 = '21' and
ITM.TSICOD_1 = AX1.IDENT2_0 and
AX1.LAN_0 = 'FRA' and
AX1.ZONE_0 = 'LNGDES' and
AX1.CODFIC_0 = 'ATABDIV'
LEFT OUTER JOIN AVWTEXTRA AX2 ON
AX2.IDENT1_0 = '22' and
ITM.TSICOD_2 = AX2.IDENT2_0 and
AX2.LAN_0 = 'FRA' and
AX2.ZONE_0 = 'LNGDES' and
AX2.CODFIC_0 = 'ATABDIV'
LEFT OUTER JOIN AVWTEXTRA AX3 ON
AX3.IDENT1_0 = '23' and
ITM.TSICOD_3 = AX3.IDENT2_0 and
AX3.LAN_0 = 'FRA' and
AX3.ZONE_0 = 'LNGDES' and
AX3.CODFIC_0 = 'ATABDIV'
LEFT OUTER JOIN AVWTEXTRA AX4 ON
AX4.IDENT1_0 = '24' and
ITM.TSICOD_4 = AX4.IDENT2_0 and
AX4.LAN_0 = 'FRA' and
AX4.ZONE_0 = 'LNGDES' and
AX4.CODFIC_0 = 'ATABDIV'
LEFT OUTER JOIN AVWTEXTRA AWK ON
AWK.IDENT2_0 = 'SI1' and
MKO.CPLWST_0 = AWK.IDENT1_0 and
AWK.LAN_0 = 'FRA' and
AWK.ZONE_0 = 'WSTDESAXX' and
AWK.CODFIC_0 = 'WORKSTATIO'
LEFT OUTER JOIN AVWTEXTRA ACC ON
WRK.WCR_0 = ACC.IDENT1_0 and
ACC.LAN_0 = 'FRA' and
ACC.ZONE_0 = 'WCRDESAXX' and
ACC.CODFIC_0 = 'TABWRKCTR' |
