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 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
| --1ère Partie
SELECT
t0.DocNum AS 'N° CMD',
CASE
WHEN t1.ItemCode NOT LIKE 'HA-%' THEN t1.ItemCode
ELSE ''
END AS 'Article',
CASE
WHEN t1.ItemCode LIKE 'HA-%' THEN t1.ItemCode
ELSE ''
END AS 'Catégorie d''Approvisionnement',
t1.Dscription AS 'Libellé (ou service)',
'' AS 'Configuration',
'' AS 'Taille',
'' AS 'Couleur',
'LMX' AS 'Site',
'' AS 'Entrepôt',
ISNULL(t4.BatchNum, '') AS 'Lot',
'' AS 'Numéro Série',
'' AS 'Numéro Palette',
'' AS 'Fabricant',
'' AS 'Projet',
ISNULL(t1.OpenCreQty, 0) AS 'Quantité',
t1.Price AS 'Prix unitaire',
t1.ShipDate AS 'Date de livraison',
t1.Rate AS 'Taux de change',
t1.Currency AS 'Devise'
FROM [Base].dbo.OPOR t0 --Commande
INNER JOIN [Base].dbo.POR1 t1 ON --Ligne de commande
t0.docentry = t1.docentry
LEFT JOIN [Base].dbo.PDN1 t2 ON --Ligne d'entrée de marchandise
t2.baseentry=t1.docentry and t2.baseline=t1.linenum
LEFT JOIN [Base].dbo.OPDN t3 ON --Entrée de marchandises
t3.docentry = t2.docentry
LEFT JOIN [Base].dbo.IBT1 t4 ON --Lot
t2.docentry = t4.baseentry and t4.basetype=20 and t2.LineNum = t4.BaseLinNum
---- Facture si réception de marchandise
LEFT JOIN [Base].dbo.PCH1 T6 ON T2.DocEntry = T6.BaseEntry -- Facture
AND T2.linenum = T6.BaseLine
AND T2.ObjType = T6.BaseType
---- Facture si aucune réception de marchandise
LEFT JOIN [Base].dbo.PCH1 T7 ON T1.DocEntry = T7.BaseEntry -- Facture
AND T1.LineNum = T7.BaseLine
AND T1.ObjType = T7.BaseType
WHERE t0.Docnum < 800000
AND t0.DocStatus <> 'C'
AND t1.LineStatus <>'C'
--AND isnull(t5.DPCNSTOPHY, 0) <> 0
--Sans facture du tout
AND t7.DocEntry is null
AND t6.DocEntry is null
UNION ALL
--Seconde partie
SELECT
t0.DocNum AS 'N° CMD',
CASE
WHEN t1.ItemCode NOT LIKE 'HA-%' THEN t1.ItemCode
ELSE ''
END AS 'Article',
CASE
WHEN t1.ItemCode LIKE 'HA-%' THEN t1.ItemCode
ELSE ''
END AS 'Catégorie d''Approvisionnement',
t1.Dscription AS 'Libellé (ou service)',
'' AS 'Configuration',
'' AS 'Taille',
'' AS 'Couleur',
'LMX' AS 'Site',
'' AS 'Entrepôt',
'' AS 'Lot',
'' AS 'Numéro Série',
'' AS 'Numéro Palette',
'' AS 'Fabricant',
'' AS 'Projet',
t1.OpenCreQty AS 'Quantité',
t1.Price AS 'Prix unitaire',
t1.ShipDate AS 'Date de livraison',
t1.Rate AS 'Taux de change',
t1.Currency AS 'Devise'
--Ici j'ai remplacé les LEFT JOIN par des INNER JOIN pour ne voir que les cas où j'ai des lots.
FROM [Base].dbo.OPOR t0
INNER JOIN [Base].dbo.POR1 t1 ON
t0.docentry = t1.docentry
INNER JOIN [Base].dbo.PDN1 t2 ON
t2.baseentry=t1.docentry and t2.baseline=t1.linenum
INNER JOIN [Base].dbo.OPDN t3 ON
t3.docentry = t2.docentry
INNER JOIN [Base].dbo.IBT1 t4 ON
t2.docentry = t4.baseentry and t4.basetype=20 and t2.LineNum = t4.BaseLinNum
---- Facture si réception de marchandise
LEFT JOIN [Base].dbo.PCH1 T6 ON T2.DocEntry = T6.BaseEntry -- Facture
AND T2.linenum = T6.BaseLine
AND T2.ObjType = T6.BaseType
WHERE t0.Docnum < 800000
AND t0.DocStatus <> 'C'
AND t1.LineStatus <>'C'
--Sans facture du tout
AND t6.DocEntry is null
--Ligne pour n'ajouter qu'une ligne en plus dans le recordset si je trouve des lots
Group by t0.DocNum, t1.ItemCode, t1.Dscription, t1.OpenCreQty, t1.Price, t1.ShipDate, t1.Rate, t1.Currency |
Partager