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 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134
| SELECT
'Cryospace' AS [Affiliate Name],
'' AS [Sub Affiliate Name],
'Ingénierie' AS [Business Line],
OPCH.CARDNAME AS [Supplier Name],
'' AS [Supplier Internal/External],
[Supplier Address],
[Supplier ZIP],
[Supplier City],
[Supplier Country Code],
[Supplier Tax Code],
'' AS [Supplier DUNS Code],
'' AS [Contract Code],
[Invoice Currency],
CONVERT(nVARCHAR, OPCH.DOCDATE, 112) AS [Invoice Accounting Date],
PCH1.LINETOTAL AS [Invoice Line Amount in Currency],
PCH1.QUANTITY AS [Invoice Quantity],
'' AS [Invoice Quantity Unit],
OPCH.DOCNUM AS [Invoice Number],
PCH1.LINENUM AS [Invoice Line Number],
[Purchase Order Number],
[Purchase Order Line Number],
OPCH.COMMENTS AS [Invoice Line Description],
[Purchase Order Line Description],
CASE WHEN (([Opex Capex] <> 'OPEX') AND ([Opex Capex] <> 'CAPEX')) THEN 'OPEX'
ELSE [Opex Capex]
END AS [Opex Capex bis],
OSLP.SLPNAME AS [Requisitioner Name],
PCH1.ITEMCODE AS [Air Liquide Article/Service Code],
PCH1.DSCRIPTION AS [Air Liquide Article/Service Name],
[Air Liquide Article/Service Group Name],
[Supplier Article/Service Code],
'' AS [Hermes Code],
PCH1.ACCTCODE AS [Cost Accounting Code],
OACT.ACCTNAME AS [Cost Accounting Name],
CASE WHEN ((PCH1.PROJECT IS NOT NULL) OR (PCH1.PROJECT <> '')) THEN PCH1.PROJECT
ELSE PCH1.OCRCODE
END AS [Cost Center Code],
CASE WHEN ((PCH1.PROJECT IS NOT NULL) OR (PCH1.PROJECT <> '')) THEN OPRJ.PRJNAME
ELSE OOCR.OCRNAME
END AS [Cost Center Name],
[Activity Name],
'' AS [Hermes Mapping Code],
YEAR(OPCH.DocDate) AS NumAnnee,
MONTH(OPCH.DocDate) AS NumMois
FROM
OPCH
INNER JOIN Vue_Spend_Analysis_Branche1 AS [BRANCHE1]
ON OPCH.CARDCODE = BRANCHE1.CARDCODE
INNER JOIN PCH1
ON PCH1.DOCENTRY = OPCH.DOCENTRY
LEFT OUTER JOIN OSLP
ON PCH1.SLPCODE = OSLP.SLPCODE
LEFT OUTER JOIN OACT
ON PCH1.ACCTCODE = OACT.ACCTCODE
LEFT OUTER JOIN OPRJ
ON PCH1.PROJECT = OPRJ.PRJCODE
LEFT OUTER JOIN OOCR
ON PCH1.OCRCODE = OOCR.OCRCODE
LEFT OUTER JOIN Vue_Spend_Analysis_Branche2 AS [BRANCHE2]
ON PCH1.ITEMCODE = BRANCHE2.ITEMCODE
LEFT OUTER JOIN Vue_Spend_Analysis_Branche3 AS [BRANCHE3]
ON PCH1.BaseEntry = BRANCHE3.DocEntry AND PCH1.BASELINE = BRANCHE3.LINENUM
WHERE PCH1.BASETYPE = 22
UNION
SELECT
'Cryospace' AS [Affiliate Name],
'' AS [Sub Affiliate Name],
'Ingénierie' AS [Business Line],
OPCH.CARDNAME AS [Supplier Name],
'' AS [Supplier Internal/External],
[Supplier Address],
[Supplier ZIP],
[Supplier City],
[Supplier Country Code],
[Supplier Tax Code],
'' AS [Supplier DUNS Code],
'' AS [Contract Code],
[Invoice Currency],
CONVERT(nVARCHAR, OPCH.DOCDATE, 112) AS [Invoice Accounting Date],
PCH1.LINETOTAL AS [Invoice Line Amount in Currency],
PCH1.QUANTITY AS [Invoice Quantity],
'' AS [Invoice Quantity Unit],
OPCH.DOCNUM AS [Invoice Number],
PCH1.LINENUM AS [Invoice Line Number],
[Purchase Order Number],
[Purchase Order Line Number],
OPCH.COMMENTS AS [Invoice Line Description],
[Purchase Order Line Description],
CASE WHEN (([Opex Capex] <> 'OPEX') AND ([Opex Capex] <> 'CAPEX')) THEN 'OPEX'
ELSE [Opex Capex]
END AS [Opex Capex bis],
OSLP.SLPNAME AS [Requisitioner Name],
PCH1.ITEMCODE AS [Air Liquide Article/Service Code],
PCH1.DSCRIPTION AS [Air Liquide Article/Service Name],
[Air Liquide Article/Service Group Name],
[Supplier Article/Service Code],
'' AS [Hermes Code],
PCH1.ACCTCODE AS [Cost Accounting Code],
OACT.ACCTNAME AS [Cost Accounting Name],
CASE WHEN ((PCH1.PROJECT IS NOT NULL) OR (PCH1.PROJECT <> '')) THEN PCH1.PROJECT
ELSE PCH1.OCRCODE
END AS [Cost Center Code],
CASE WHEN ((PCH1.PROJECT IS NOT NULL) OR (PCH1.PROJECT <> '')) THEN OPRJ.PRJNAME
ELSE OOCR.OCRNAME
END AS [Cost Center Name],
[Activity Name],
'' AS [Hermes Mapping Code],
YEAR(OPCH.DocDate) AS NumAnnee,
MONTH(OPCH.DocDate) AS NumMois
FROM
OPCH
INNER JOIN Vue_Spend_Analysis_Branche1 AS [BRANCHE1]
ON OPCH.CARDCODE = BRANCHE1.CARDCODE
INNER JOIN PCH1
ON PCH1.DOCENTRY = OPCH.DOCENTRY
LEFT OUTER JOIN OSLP
ON PCH1.SLPCODE = OSLP.SLPCODE
LEFT OUTER JOIN OACT
ON PCH1.ACCTCODE = OACT.ACCTCODE
LEFT OUTER JOIN OPRJ
ON PCH1.PROJECT = OPRJ.PRJCODE
LEFT OUTER JOIN OOCR
ON PCH1.OCRCODE = OOCR.OCRCODE
LEFT OUTER JOIN Vue_Spend_Analysis_Branche2 AS [BRANCHE2]
ON PCH1.ITEMCODE = BRANCHE2.ITEMCODE
LEFT OUTER JOIN Vue_Spend_Analysis_Branche4 AS [BRANCHE4]
ON PCH1.BaseEntry = BRANCHE4.DocEntry AND PCH1.BASELINE = BRANCHE4.LINENUM
WHERE PCH1.BASETYPE = 20 OR PCH1.BASETYPE = -1 |
Partager