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' |
Partager