· 5 years ago · Oct 28, 2020, 09:56 AM
1USE [MMK_DataWarehouse]
2GO
3
4/****** Object: View [dbo].[V_SALESDEFBASE] Script Date: 28.10.2020 10:49:16 ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11CREATE view [dbo].[V_SALESDEFBASE]
12-- FILE: $HeadURL: http://srv-ha-bisvn.mmk.mmdom.net/svn/MMBI/D365/allgemein/sql/v_salesdefbase.sql $
13-- REVISION: $Revision: 26539 $ $Date: 2020-09-28 17:10:38 +0200 (Mo., 28 Sep 2020) $ $Author: viecwi $
14as
15WITH
16/*
17Equivalence for Statistics group 2 in 3s
18*/
19ProductCategory as (
20 select prodCat.PARTITION, prodCat.PRODUCT, category.CODE, category.NAME from ECORESPRODUCTCATEGORY prodCat
21 join ECORESCATEGORYHIERARCHY hierarchy on hierarchy.PARTITION = prodCat.PARTITION
22 and hierarchy.RECID = prodCat.CATEGORYHIERARCHY
23 join ECORESCATEGORY category on category.PARTITION = prodCat.PARTITION
24 and category.RECID = prodCat.CATEGORY
25 where hierarchy.NAME = 'Reporting Hierarchy'
26),
27TaxTransFirst as (
28 SELECT * FROM (
29 SELECT *,
30 rn = ROW_NUMBER() OVER (
31 PARTITION BY PARTITION,
32 DATAAREAID,
33 VOUCHER,
34 TRANSDATE,
35 INVENTTRANSID
36 ORDER BY RECID)
37 FROM TAXTRANS tt) T
38 WHERE T.rn = 1
39),
40InvoicePackingSlips as (
41 SELECT invPackLink.PARTITION,
42 invPackLink.DATAAREAID,
43 invPackLink.INVOICESOURCEDOCUMENTLINE,
44 PACKINGSLIPIDS = STUFF(CAST((
45 SELECT ', ' + psTrans.PACKINGSLIPID FROM CUSTPACKINGSLIPTRANS psTrans
46 JOIN CUSTINVOICEPACKINGSLIPQUANTITYMATCH invPackLink2 ON invPackLink2.PARTITION = psTrans.PARTITION
47 AND invPackLink2.DATAAREAID = psTrans.DATAAREAID
48 AND invPackLink2.PACKINGSLIPSOURCEDOCUMENTLINE = psTrans.SOURCEDOCUMENTLINE
49 WHERE psTrans.PARTITION = invPackLink.PARTITION
50 AND psTrans.DATAAREAID = invPackLink.DATAAREAID
51 AND invPackLink2.INVOICESOURCEDOCUMENTLINE = invPackLink.INVOICESOURCEDOCUMENTLINE
52 GROUP BY psTrans.PACKINGSLIPID
53 FOR XML PATH(''), TYPE) AS NVARCHAR(1000)), 1, 2, '')
54 FROM CUSTINVOICEPACKINGSLIPQUANTITYMATCH invPackLink
55 GROUP BY invPackLink.PARTITION, invPackLink.DATAAREAID, invPackLink.INVOICESOURCEDOCUMENTLINE
56),
57CompanyInfo as (
58 SELECT pt.PARTITION,
59 pt.DATAAREA AS DATAAREAID,
60 pt.LANGUAGEID,
61 l.ACCOUNTINGCURRENCY AS CURRENCYCODE,
62 l.DEFAULTEXCHANGERATETYPE,
63 et.NAME AS DEFAULTEXCHANGERATETYPENAME
64 FROM DIRPARTYTABLE pt
65 JOIN TABLEIDTABLE t ON t.ID = pt.INSTANCERELATIONTYPE
66 AND t.NAME = 'CompanyInfo'
67 JOIN LEDGER l ON l.PARTITION = pt.PARTITION
68 AND l.PRIMARYFORLEGALENTITY = pt.RECID
69 LEFT JOIN EXCHANGERATETYPE et ON et.RECID = l.DEFAULTEXCHANGERATETYPE
70
71),
72InventTransPalletCnt as (
73 SELECT itro.PARTITION, itro.DATAAREAID, itro.INVENTTRANSID, PalletCnt = COUNT(DISTINCT iDimTr.LICENSEPLATEID) FROM INVENTTRANS itr
74 JOIN INVENTTRANSORIGIN itro on itro.RECID = itr.INVENTTRANSORIGIN
75 JOIN INVENTDIM iDimTr on iDimTr.PARTITION = itr.PARTITION
76 AND iDimTr.DATAAREAID = itr.DATAAREAID
77 AND iDimTr.INVENTDIMID = itr.INVENTDIMID
78 WHERE itr.STATUSISSUE = 0 /* Sold */
79 GROUP BY itro.PARTITION, itro.DATAAREAID, itro.INVENTTRANSID
80),
81markupAmountDocLine as (
82 SELECT mt.PARTITION,
83 mt.DATAAREAID,
84 mt.TRANSTABLEID,
85 tId.NAME AS TRANSTABLENAME,
86 mt.TRANSRECID,
87 SUM(mt.CALCULATEDAMOUNT) AS CALCULATEDAMOUNT
88 FROM MARKUPTRANS mt
89 JOIN TABLEIDTABLE tId ON tId.ID = mt.TRANSTABLEID
90 GROUP BY mt.PARTITION,
91 mt.DATAAREAID,
92 mt.TRANSTABLEID,
93 mt.TRANSRECID,
94 tId.NAME
95),
96intercompanyCustGroups as (
97 SELECT *
98 FROM (
99 VALUES ('IC-3RD'collate Latin1_General_CI_AS ),
100 ('IC-EU' collate Latin1_General_CI_AS ),
101 ('IC-INL' collate Latin1_General_CI_AS ),
102 ('IC-INL-ORG' collate Latin1_General_CI_AS )
103 ) AS Groups(GroupId)
104),
105SalesFact_CTE
106AS (
107 SELECT
108 ip.PARTITION,
109 dataareaid = i.DATAAREAID,
110 c_accountnum = i.ORDERACCOUNT,
111 c_name = c_dpt.NAME,
112 std_auftrag_nr = s_sl.SALESID,
113 std_auftrag_pos = s_sl.LINENUM,
114 order_date = s_sl.CREATEDDATETIME,
115 abr_auftrag_nr = a_sl.SALESID,
116 abr_auftrag_pos = a_sl.LINENUM,
117 abr_auftrag_typ = a_s.SALESTYPE,
118 abr_auftrag_typ_name = enumTranslation.ENUMITEMLABEL,
119 order_accountnum = null, --n/a s_sl.MMEPURCHORDERFORMNUM
120 salesname = s_s.SALESNAME,
121 auftrag_id_sb = wSalesTaker.PARTYNUMBER,
122 inventtransid = s_sl.INVENTTRANSID,
123 trade_goods = a_sl.TRADINGGOOD_MME,
124 salesgroup = a_sl.SALESGROUP,
125 custaccountfinal = isnull(trt.ACCOUNTNUM, i.ORDERACCOUNT),
126 custname = isnull(trtCust_dpt.NAME, c_dpt.NAME),
127 FINALCUSTPURCHASEORDER = trt.ENDCUSTOMERORDERNO_MME,
128 FINALCUSTPURCHASEORDERPOS = trt.ENDCUSTOMERORDERPOS_MME,
129 VENDORDERNUMBEREXT = null, --n/a trt.VENDORDERNUMBEREXT,
130 salespoolid = s_sl.FWPSALESPOOLID,
131 salesidprev = null, -- n/a
132 linenumprev = null, -- n/a
133 IC_ENDPOINTID = null, --AIF is deprecated
134 i_c_accountnum = i.INVOICEACCOUNT,
135 i_c_accountname = i_c_dpt.NAME,
136 custgroup = i.CUSTGROUP,
137 custgroup_name = cGroup.NAME,
138 custitemnum = eart.EXTERNALITEMID,
139 cust_release_order = s_sl.CUSTCALLOFFNO_MME,
140 c_segmentid = c.SEGMENTID,
141 c_segmentid_name = segment.DESCRIPTION,
142 -- todo: --> outer apply dbo.get_1st_purchline_recid_tvf(s_sl.DATAAREAID,s_sl.INVENTTRANSID,null) pl -- not implement now
143 vendorid_1st_po = null,
144 vendorname_1st_po = null,
145 purchid_1st_po = null,
146 purchlinenum_1st_po = null,
147 -- todo: <-- get_1st_purchline_recid_tvf...
148 c_partyid = c_dpt.PARTYNUMBER,
149 externalcountryversion = eart.EXTERNALITEMTXT,
150
151 -- todo: need linked server
152 panem_key = null, --case when n.t2_id in (13279,12669) then '' else 'Panem' end panem_key,
153 t2 = null, --n.t2,
154 t3 = null, --n.t3,
155 t4 = null, --n.t4,
156 t5 = null, --n.t5,
157 t6 = null, --n.t6,
158 T4F = null, --nf.t4 T4F,
159 -- todo: linked server <--
160
161 invoiceid = ip.INVOICEID,
162 i_linenum = ip.LINENUM,
163 tax_code = taxTr.TAXCODE,
164 preinvoicetype = null, -- n/a
165 invoicetype =
166 case when i.INVOICEAMOUNT >= 0 then
167 case when i.PROFORMA = 1 then
168 case when i.PREPAYMENT = 1
169 then 'Vorauszahlungsrechnung'
170 else 'Proforma-Rechnung'
171 end
172 else 'Rechnung'
173 end
174 when i.INVOICEAMOUNT < 0 and i.PROFORMA=0 and i.PREPAYMENT = 0 then 'Gutschrift'
175 else 'Andere' --mmwh.dbo.enum_name(i.MMEInvoiceType,'MMESalesInvoiceType','DE-AT')
176 end,
177
178 ledgervoucher = i.LEDGERVOUCHER,
179 i_custgroup = i_c.CUSTGROUP,
180 i_custgroup_name = i_cGroup.NAME,
181 ledgeraccount = ip_dimCombi.MAINACCOUNTVALUE,
182 LEDGERACCOUNT_SETUP = Ledger.MAINACCOUNTVALUE,
183 i_addrname = i.INVOICINGNAME,
184 i_city = invoiceAddr.CITY,
185 i_zipcode = invoiceAddr.ZIPCODE,
186 i_street = invoiceAddr.STREET,
187 i_countryregionid = invoiceAddr.COUNTRYREGIONID,
188 i_countryregion_name = invoiceAddrCountryTranslation.SHORTNAME,
189
190 term_of_payment = null, -- tp.term_of_payment_code -- todo: viecwi to check if MMWH view [d365].[v_termofpayment] is available; if yes -> use it, otherwise re-implement it
191 term_of_payment_desc = null, -- mmwh.dbo.term_of_payment_desc(i.payment,i.CASHDISCCODE,'DE-AT')
192 i_priceunit = ip.SALESUNIT, -- MMEALTPRICEUNIT equivalence not implemented yet
193 i_currency = ip.CURRENCYCODE,
194 i_taxgroup = ip.TAXGROUP,
195 i_vatnum = i.VATNUM,
196 externalinvoiceid = null, -- n/a
197 ledgerpostingtype = null, -- todo: mmwh.dbo.enum_name(ledgerpostingtype,'LedgerPostingType', 'DE-AT') ledgerpostingtype, find_ledgertrans_dimension4_tvf, not implement now
198 i_amount_incl_vat_company_col = i.INVOICEAMOUNTMST,
199 sl_corrected_by = null, -- outer apply mmwh.dbo.sl_correction_tvf(a_sl.DATAAREAID,a_sl.INVENTTRANSID) slc.corrected_by -- not implement now
200 sl_correction_to = null, -- ... slc.correction_to -- not implement now
201 i_deladdrname = i.DELIVERYNAME,
202 i_delcountryregionid = dlvAddr.COUNTRYREGIONID,
203 i_delcity = dlvAddr.CITY,
204 i_delzipcode = dlvAddr.ZIPCODE,
205 i_delstreet = dlvAddr.STREET,
206 i_delterm = i.DLVTERM,
207 i_delmode = i.DLVMODE,
208 i_deldate = ip.DLVDATE,
209 packingslipid = invPackSlips.PACKINGSLIPIDS, -- diffent granularity -> Comma separated
210 externalpackingslipid = null, -- n/a
211 itemid = ip.ITEMID,
212 itemname = prodTranslation.NAME,
213 itemvariante = id.CONFIGID,
214 itemvariante_name = id.CONFIGID,
215 itemgroupid = itGroupLink.ITEMGROUPID,
216 itemcatid = null, -- n/a
217 itemclassid = null, -- n/a
218 -- todo: from item attributes --> not implement now -->
219 item_property_m104 = null,
220 item_property_m305 = null,
221 item_property_3010 = null,
222 samplebox = null,
223 printgroupid = null,
224 printgroupname = null,
225 material = null,
226 -- todo: from item attributes <--
227 inventlocationid = id.INVENTLOCATIONID,
228 wmslocationid = id.WMSLOCATIONID,
229 number_of_pallets = palletCnt.PalletCnt,
230 -- todo: from item attributes, not implement now -->
231 config_prop_m305 = null,
232 config_prop_m670 = null,
233 config_prop_m708 = null,
234 -- todo: from item attributes, not implement now <--
235 -- not implement fin. dimensions now
236 department = null, -- department from invoice line or invoice, n/a?
237 LOGISTICS_SITE = LOWER(ISNULL(NULLIF(id.INVENTSITEID, ''), i.DATAAREAID)),
238 financial_site_num = finSite.DISPLAYVALUE,
239 financial_site = finSiteTag.DESCRIPTION,
240 OWNERID = null, -- todo: sl.MMEOwnerId -- not implement now
241 reference_no = s_sl.SALESID,
242 kostentraeger = null, -- todo: purpose dim from invoice line or invoice
243 sl_kostentraeger = null, -- todo: purpose dim from s_sl
244 standort = null, -- todo: = fin site
245 kostenstelle = null, -- todo: cost center dim from invoice line or invoice
246 warencode = null, -- todo: ?? InventTable.INTRACODE -- not implement now
247 warencode_name = null, --mmwh.dbo.warencode_name(dataareaid,warencode,1) warencode_name, -- not implement now
248 statisticsgroupid1 = null, -- n/a
249 statisticsgroupname1 = null, --mmwh.dbo.statisticgroup_name(dataareaid,1,statisticsgroupid1,1) statisticsgroupname1,
250 statisticsgroupid2 = pCat.CODE, -- product hierarchy
251 statisticsgroupname2 = pCat.NAME,
252 statisticsgroupid3 = null, -- n/a
253 statisticsgroupname3 = null, --mmwh.dbo.statisticgroup_name(dataareaid,3,statisticsgroupid3,1) statisticsgroupname3,
254 statisticsgroupid4 = null, -- n/a
255 statisticsgroupname4 = null, --mmwh.dbo.statisticgroup_name(dataareaid,4,statisticsgroupid4,1) statisticsgroupname4,
256 statisticsgroupid5 = null, -- n/a
257 statisticsgroupname5 = null, --mmwh.dbo.statisticgroup_name(dataareaid,5,statisticsgroupid5,1) statisticsgroupname5,
258 fieldservice = workField.PERSONNELNUMBER,
259 indoorservice = workIndoor.PERSONNELNUMBER,
260 production_orders_str = null, -- todo: mmwh.dbo.production_orders_str(s_sl.DATAAREAID, s_sl.INVENTTRANSID) -- not implement now
261 deliverycountryregionid = dlvAddr.COUNTRYREGIONID,
262 LEDGERTRANS_SITE = null, -- todo: ledger trans site -- not implement now
263 LEDGERTRANS_SITE_DESC = null, -- todo: site name, -- not implement now
264 PRICEGROUPID = s_s.PRICEGROUPID, -- todo:
265 /*
266 case when nullif(ct.MMESALESPRICEGROUPID, '') is null
267 then case when nullif(art.MMESALESPRICEGROUPID, '') is null
268 then ''
269 else art.MMESALESPRICEGROUPID
270 end
271 else ct.MMESALESPRICEGROUPID
272 end pricegroupid,
273 */
274 pricegroupname = pdGroup.NAME,
275 pricegroupmmecustname = pdGroup.NAME, -- n/a
276 COMMISSIONGROUP = c.COMMISSIONGROUP,
277 COMMISSCALC = iif(ip.COMMISSCALC=1, 'Yes', 'No'),
278 revenue_type = null, -- todo: mmwh.dbo.revenue_type2(i.DATAAREAID,ip.LEDGERACCOUNT,i.LEDGERVOUCHER) -- not implement now
279 CTL_ACCOUNT = null, -- todo: mmwh.dbo.ctl_account(i.DATAAREAID,ip.LEDGERACCOUNT,i.LEDGERVOUCHER) -- not implement now
280 i_salesunit = ip.SALESUNIT,
281 invoicedate = ip.INVOICEDATE,
282 invoice_month = format(ip.INVOICEDATE, 'MM'),
283 invoice_month_yr = format(ip.INVOICEDATE, 'yyyy-MM'),
284 invoice_quarter = datepart(q, ip.INVOICEDATE),
285 invoice_year = format(ip.INVOICEDATE, 'yyyy'),
286
287 ic_order = IIF(CustIsIC.GroupId IS NOT NULL AND isnull(pCat.CODE, '') <> '5002', 1, 0),
288 currency_company = cInfo.CURRENCYCODE,
289 exch_rate_type_company = cInfo.DEFAULTEXCHANGERATETYPE,
290 i_qty = ip.QTY,
291 i_amount_net = ip.LINEAMOUNT,
292 i_amount_net_with_mp = ip.LINEAMOUNT + ISNULL(mt.CALCULATEDAMOUNT, 0),
293 i_amount_net_with_mp_company = ip.LINEAMOUNTMST + IIF(ip.LINEAMOUNT <> 0, ISNULL(mt.CALCULATEDAMOUNT, 0) * ip.LINEAMOUNTMST / ip.LINEAMOUNT, 0),
294 i_commissamount_company = ip.COMMISSAMOUNTMST,
295 i_commissamount = ip.COMMISSAMOUNTCUR
296 FROM CUSTINVOICETRANS ip
297 LEFT JOIN CompanyInfo cInfo ON cInfo.PARTITION = ip.PARTITION
298 AND cInfo.DATAAREAID = ip.DATAAREAID
299 LEFT JOIN CUSTINVOICEJOUR i ON ip.PARTITION = i.PARTITION
300 AND ip.DATAAREAID = i.DATAAREAID
301 AND ip.INVOICEID = i.INVOICEID
302 AND ip.INVOICEDATE = i.INVOICEDATE
303 AND ip.SALESID = i.SALESID
304 LEFT JOIN INVENTTABLE art ON art.PARTITION = ip.PARTITION
305 AND art.DATAAREAID = ip.DATAAREAID
306 AND art.ITEMID = ip.ITEMID
307 LEFT JOIN INVENTITEMGROUPITEM itGroupLink ON itGroupLink.PARTITION = art.PARTITION
308 AND itGroupLink.ITEMDATAAREAID = art.DATAAREAID
309 AND itGroupLink.ITEMID = art.ITEMID
310 LEFT JOIN ProductCategory pCat on pCat.PARTITION = art.PARTITION
311 AND pCat.PRODUCT = art.PRODUCT
312 LEFT JOIN ECORESPRODUCTTRANSLATION prodTranslation ON prodTranslation.PARTITION = art.PARTITION
313 AND prodTranslation.PRODUCT = art.PRODUCT
314 AND prodTranslation.LANGUAGEID = cInfo.LANGUAGEID
315 LEFT JOIN INVENTDIM id ON id.PARTITION = ip.PARTITION
316 AND id.DATAAREAID = ip.DATAAREAID
317 AND id.INVENTDIMID = ip.INVENTDIMID
318 LEFT JOIN SALESLINE a_sl ON a_sl.PARTITION = ip.PARTITION
319 AND a_sl.DATAAREAID = ip.DATAAREAID
320 AND a_sl.INVENTTRANSID = ip.INVENTTRANSID
321 LEFT JOIN SALESTABLE a_s ON a_s.PARTITION = a_sl.PARTITION
322 AND a_s.DATAAREAID = a_sl.DATAAREAID
323 AND a_s.SALESID = a_sl.SALESID
324 LEFT JOIN DIRPARTYTABLE wSalesTaker on wSalesTaker.RECID = a_s.WORKERSALESTAKER
325 LEFT JOIN SALESLINE s_sl ON s_sl.PARTITION = a_sl.PARTITION
326 and s_sl.DATAAREAID = a_sl.DATAAREAID
327 and s_sl.INVENTTRANSID = ISNULL(NULLIF(a_sl.INVENTTRANSIDRETURN, ''), a_sl.INVENTTRANSID)
328 LEFT JOIN SALESTABLE s_s ON s_s.PARTITION = s_sl.PARTITION
329 AND s_s.DATAAREAID = s_sl.DATAAREAID
330 AND s_s.SALESID = s_sl.SALESID
331 LEFT JOIN CUSTTABLE c ON c.PARTITION = i.PARTITION
332 AND c.DATAAREAID = i.DATAAREAID
333 AND c.ACCOUNTNUM = i.ORDERACCOUNT
334 LEFT JOIN DIRPARTYTABLE c_dpt ON c_dpt.RECID = c.PARTY
335 LEFT JOIN CUSTGROUP cGroup ON cGroup.PARTITION = c.PARTITION
336 AND cGroup.DATAAREAID = c.DATAAREAID
337 AND cGroup.CUSTGROUP = c.CUSTGROUP
338 LEFT JOIN SMMBUSRELSEGMENTGROUP segment ON segment.PARTITION = c.PARTITION
339 AND segment.DATAAREAID = c.DATAAREAID
340 AND segment.SEGMENTID = c.SEGMENTID
341 LEFT JOIN CUSTTABLE i_c ON i_c.PARTITION = i.PARTITION
342 AND i_c.DATAAREAID = i.DATAAREAID
343 AND i_c.ACCOUNTNUM = i.INVOICEACCOUNT
344 LEFT JOIN DIRPARTYTABLE i_c_dpt ON i_c_dpt.RECID = c.PARTY
345 LEFT JOIN CUSTGROUP i_cGroup ON i_cGroup.PARTITION = c.PARTITION
346 AND i_cGroup.DATAAREAID = c.DATAAREAID
347 AND i_cGroup.CUSTGROUP = c.CUSTGROUP
348 LEFT JOIN TRIANGULARTRADE_MME trt ON trt.PARTITION = s_sl.PARTITION
349 AND trt.DATAAREAID = s_sl.DATAAREAID
350 AND trt.INVENTTRANSID = s_sl.INVENTTRANSID
351 LEFT JOIN CUSTTABLE trtCust ON trtCust.PARTITION = trt.PARTITION
352 AND trtCust.DATAAREAID = trt.DATAAREAID
353 AND trtCust.ACCOUNTNUM = trt.ACCOUNTNUM
354 LEFT JOIN DIRPARTYTABLE trtCust_dpt ON trtCust_dpt.RECID = trtCust.PARTY
355 LEFT JOIN TaxTransFirst taxTr ON taxTr.PARTITION = ip.PARTITION
356 AND taxTr.DATAAREAID = ip.DATAAREAID
357 AND taxTr.VOUCHER = i.LEDGERVOUCHER
358 AND taxTr.TRANSDATE = i.INVOICEDATE
359 AND taxTr.INVENTTRANSID = ip.INVENTTRANSID
360 LEFT JOIN DIMENSIONATTRIBUTEVALUECOMBINATION ip_dimCombi ON ip_dimCombi.RECID = ip.LEDGERDIMENSION
361 OUTER APPLY (
362 -- todo: it's not how AX search ledger account, maybe need to improve in future
363 SELECT TOP 1 dimCombi.MAINACCOUNTVALUE FROM INVENTPOSTING ipo
364 JOIN DIMENSIONATTRIBUTEVALUECOMBINATION dimCombi
365 ON (
366 (a_sl.TRADINGGOOD_MME = 0 AND dimCombi.RECID = ipo.LEDGERDIMENSION)
367 OR (a_sl.TRADINGGOOD_MME = 1 AND dimCombi.RECID = ipo.LEDGERDIMENSIONTRADEGOOD_MME)
368 )
369 WHERE ipo.PARTITION = ip.PARTITION
370 AND ipo.DATAAREAID = ip.DATAAREAID
371 AND ipo.INVENTACCOUNTTYPE = 23 /* SalesRevenue */
372 AND (
373 (ipo.ITEMCODE = 0 /* Table */ AND ipo.ITEMRELATION = ip.ITEMID)
374 OR (ipo.ITEMCODE = 1 /* Group */ AND ipo.ITEMRELATION = itGroupLink.ITEMGROUPID)
375 )
376 AND CUSTVENDRELATION = iif(CUSTVENDCODE = 2, '', i.CUSTGROUP)
377 ) Ledger
378 LEFT JOIN LOGISTICSPOSTALADDRESS invoiceAddr ON invoiceAddr.RECID = i.INVOICEPOSTALADDRESS
379 LEFT JOIN LOGISTICSADDRESSCOUNTRYREGIONTRANSLATION invoiceAddrCountryTranslation ON invoiceAddrCountryTranslation.PARTITION = invoiceAddr.PARTITION
380 AND invoiceAddrCountryTranslation.COUNTRYREGIONID = invoiceAddr.COUNTRYREGIONID
381 AND invoiceAddrCountryTranslation.LANGUAGEID = 'DE-AT'
382 LEFT JOIN LOGISTICSPOSTALADDRESS dlvAddr ON dlvAddr.RECID = i.INVOICEPOSTALADDRESS
383 LEFT JOIN LOGISTICSADDRESSCOUNTRYREGIONTRANSLATION dlvAddrCountryTranslation ON dlvAddrCountryTranslation.PARTITION = dlvAddr.PARTITION
384 AND dlvAddrCountryTranslation.COUNTRYREGIONID = dlvAddr.COUNTRYREGIONID
385 AND dlvAddrCountryTranslation.LANGUAGEID = 'DE-AT'
386 OUTER APPLY (
387 SELECT TOP 1 EXTERNALITEMID, EXTERNALITEMTXT FROM CUSTVENDEXTERNALITEM extItem
388 JOIN INVENTDIM iDimExtItem ON iDimExtItem.PARTITION = extItem.PARTITION
389 AND iDimExtItem.DATAAREAID = extItem.DATAAREAID
390 AND iDimExtItem.INVENTDIMID = extItem.INVENTDIMID
391 WHERE extItem.PARTITION = art.PARTITION
392 AND extItem.DATAAREAID = art.DATAAREAID
393 AND extItem.ITEMID = art.ITEMID
394 AND (
395 (extItem.MODULETYPE = 4 /* Cust */ AND extItem.CUSTVENDRELATION = i_c.ACCOUNTNUM)
396 OR (extItem.MODULETYPE = 6 /* CustGroup */ AND extItem.CUSTVENDRELATION = i_c.CUSTITEMGROUPID)
397 )
398 AND iDimExtItem.CONFIGID = id.CONFIGID
399 ORDER BY MODULETYPE
400 ) eart
401 LEFT JOIN InvoicePackingSlips invPackSlips ON invPackSlips.PARTITION = ip.PARTITION
402 AND invPackSlips.DATAAREAID = ip.DATAAREAID
403 AND invPackSlips.INVOICESOURCEDOCUMENTLINE = ip.SOURCEDOCUMENTLINE
404 LEFT JOIN InventTransPalletCnt palletCnt ON palletCnt.PARTITION = ip.PARTITION
405 AND palletCnt.DATAAREAID = ip.DATAAREAID
406 AND palletCnt.INVENTTRANSID = ip.INVENTTRANSID
407 LEFT JOIN DEFAULTDIMENSIONVIEW finSite on finSite.PARTITION = ip.PARTITION
408 AND finSite.DEFAULTDIMENSION = ip.DEFAULTDIMENSION
409 AND finSite.NAME = 'Site'
410 LEFT JOIN DIMENSIONFINANCIALTAG finSiteTag ON finSiteTag.RECID = finSite.ENTITYINSTANCE
411 LEFT JOIN HCMWORKER workField ON workField.RECID = a_s.FIELDSERVICEWORKER_MME
412 LEFT JOIN HCMWORKER workIndoor ON workIndoor.RECID = a_s.WORKERSALESRESPONSIBLE
413 LEFT JOIN PRICEDISCGROUP pdGroup ON pdGroup.PARTITION = s_s.PARTITION
414 AND pdGroup.DATAAREAID = s_s.DATAAREAID
415 AND pdGroup.GROUPID = s_s.PRICEGROUPID
416 LEFT JOIN intercompanyCustGroups CustIsIC ON CustIsIC.GroupId = c.CUSTGROUP
417 LEFT JOIN markupAmountDocLine mt ON mt.PARTITION = ip.PARTITION
418 AND mt.DATAAREAID = ip.DATAAREAID
419 AND mt.TRANSTABLENAME = 'CustInvoiceTrans'
420 AND mt.TRANSRECID = ip.RECID
421 LEFT JOIN CUSTPARAMETERS custParm ON custParm.PARTITION = ip.PARTITION
422 AND custParm.DATAAREAID = ip.DATAAREAID
423 AND custParm.Key_ = 0
424 LEFT JOIN BIENUMTRANSLATION_MME enumTranslation ON enumTranslation.LANGUAGEID = cInfo.LANGUAGEID
425 AND enumTranslation.ENUMNAME = 'SalesType'
426 AND enumTranslation.ENUMITEMVALUE = a_s.SALESTYPE
427
428 WHERE a_sl.SALESCATEGORY NOT IN (
429 NULLIF(custParm.PREPAYMENTINVOICESALESCATEGORY_MME, 0),
430 NULLIF(custParm.PREPAYMENTINVOICEREVERSALSALESCATEGORY_MME, 0))
431 AND NOT EXISTS (
432 SELECT 1
433 FROM FWFVATMATRIXPARAMETERS x
434 WHERE x.PARTITION = ip.PARTITION
435 AND x.DATAAREAID = ip.DATAAREAID
436 AND x.TRANSFERCUSTOMER <> ''
437 AND x.TRANSFERCUSTOMER = i.ORDERACCOUNT
438 AND ISNULL(NULLIF(x.TRANSFERITEM, ''), ip.ITEMID) = ip.ITEMID
439 )
440 ),
441SalesFactAgg_CTE
442AS (
443 SELECT PARTITION,
444 INVOICE_MONTH,
445 INVOICE_MONTH_YR,
446 INVOICE_QUARTER,
447 INVOICE_YEAR,
448 STD_AUFTRAG_NR,
449 ORDER_DATE,
450 STD_AUFTRAG_POS,
451 ORDER_ACCOUNTNUM,
452 ABR_AUFTRAG_NR,
453 ABR_AUFTRAG_POS,
454 ABR_AUFTRAG_TYP,
455 ABR_AUFTRAG_TYP_NAME,
456 SALESNAME,
457 AUFTRAG_ID_SB,
458 TRADE_GOODS,
459 SALESGROUP,
460 CUSTACCOUNTFINAL,
461 CUSTNAME,
462 FINALCUSTPURCHASEORDER,
463 FINALCUSTPURCHASEORDERPOS,
464 VENDORDERNUMBEREXT,
465 SALESPOOLID,
466 SALESIDPREV,
467 LINENUMPREV,
468 IC_ORDER,
469 IC_ENDPOINTID,
470 C_ACCOUNTNUM,
471 C_NAME,
472 I_C_ACCOUNTNUM,
473 I_C_ACCOUNTNAME,
474 CUSTGROUP,
475 CUSTGROUP_NAME,
476 CUSTITEMNUM,
477 CUST_RELEASE_ORDER,
478 C_SEGMENTID,
479 C_SEGMENTID_NAME,
480 VENDORID_1ST_PO,
481 VENDORNAME_1ST_PO,
482 C_PARTYID,
483 EXTERNALCOUNTRYVERSION,
484 PANEM_KEY,
485 T2,
486 T3,
487 T4,
488 T5,
489 T6,
490 T4F,
491 INVOICEID,
492 I_LINENUM,
493 TAX_CODE,
494 PREINVOICETYPE,
495 INVOICETYPE,
496 INVOICEDATE,
497 LEDGERVOUCHER,
498 I_CUSTGROUP,
499 I_CUSTGROUP_NAME,
500 LEDGERACCOUNT,
501 LEDGERACCOUNT_SETUP,
502 I_ADDRNAME,
503 I_CITY,
504 I_ZIPCODE,
505 I_STREET,
506 I_COUNTRYREGIONID,
507 I_COUNTRYREGION_NAME,
508 TERM_OF_PAYMENT,
509 TERM_OF_PAYMENT_DESC,
510 I_PRICEUNIT,
511 I_CURRENCY,
512 I_TAXGROUP,
513 I_VATNUM,
514 EXTERNALINVOICEID,
515 LEDGERPOSTINGTYPE,
516 I_AMOUNT_INCL_VAT_COMPANY_COL,
517 SL_CORRECTED_BY,
518 SL_CORRECTION_TO,
519 I_DELADDRNAME,
520 I_DELCOUNTRYREGIONID,
521 I_DELCITY,
522 I_DELZIPCODE,
523 I_DELSTREET,
524 I_DELTERM,
525 I_DELMODE,
526 I_DELDATE,
527 PACKINGSLIPID,
528 EXTERNALPACKINGSLIPID,
529 ITEMID,
530 ITEMNAME,
531 ITEMVARIANTE,
532 ITEMVARIANTE_NAME,
533 ITEMGROUPID,
534 ITEMCATID,
535 ITEMCLASSID,
536 ITEM_PROPERTY_M104,
537 ITEM_PROPERTY_M305,
538 ITEM_PROPERTY_3010,
539 SAMPLEBOX,
540 PRINTGROUPID,
541 PRINTGROUPNAME,
542 MATERIAL,
543 INVENTLOCATIONID,
544 WMSLOCATIONID,
545 CONFIG_PROP_M305,
546 CONFIG_PROP_M670,
547 CONFIG_PROP_M708,
548 DEPARTMENT,
549 PURCHID_1ST_PO,
550 PURCHLINENUM_1ST_PO,
551 DATAAREAID,
552 LOGISTICS_SITE,
553 FINANCIAL_SITE_NUM,
554 FINANCIAL_SITE,
555 OWNERID,
556 REFERENCE_NO,
557 KOSTENTRAEGER,
558 SL_KOSTENTRAEGER,
559 STANDORT,
560 KOSTENSTELLE,
561 WARENCODE,
562 WARENCODE_NAME,
563 STATISTICSGROUPID1,
564 STATISTICSGROUPNAME1,
565 STATISTICSGROUPID2,
566 STATISTICSGROUPNAME2,
567 STATISTICSGROUPID3,
568 STATISTICSGROUPNAME3,
569 STATISTICSGROUPID4,
570 STATISTICSGROUPNAME4,
571 STATISTICSGROUPID5,
572 STATISTICSGROUPNAME5,
573 FIELDSERVICE,
574 INDOORSERVICE,
575 PRODUCTION_ORDERS_STR,
576 DELIVERYCOUNTRYREGIONID,
577 LEDGERTRANS_SITE,
578 LEDGERTRANS_SITE_DESC,
579 PRICEGROUPID,
580 PRICEGROUPNAME,
581 PRICEGROUPMMECUSTNAME,
582 COMMISSIONGROUP,
583 COMMISSCALC,
584 REVENUE_TYPE,
585 CTL_ACCOUNT,
586 I_SALESUNIT,
587 CURRENCY_COMPANY,
588 EXCH_RATE_TYPE_COMPANY,
589 NUMBER_OF_PALLETS = SUM(NUMBER_OF_PALLETS),
590 I_QTY = SUM(I_QTY),
591 I_AMOUNT_NET = SUM(I_AMOUNT_NET),
592 I_AMOUNT_NET_WITH_MP = SUM(I_AMOUNT_NET_WITH_MP),
593 I_AMOUNT_NET_WITH_MP_COMPANY = SUM(A.I_AMOUNT_NET_WITH_MP_COMPANY),
594 I_COMMISSAMOUNT_COMPANY = SUM(I_COMMISSAMOUNT_COMPANY),
595 I_COMMISSAMOUNT = SUM(I_COMMISSAMOUNT)
596 FROM SalesFact_CTE a
597 GROUP BY PARTITION,
598 INVOICE_MONTH,
599 INVOICE_MONTH_YR,
600 INVOICE_QUARTER,
601 INVOICE_YEAR,
602 STD_AUFTRAG_NR,
603 ORDER_DATE,
604 STD_AUFTRAG_POS,
605 ORDER_ACCOUNTNUM,
606 ABR_AUFTRAG_NR,
607 ABR_AUFTRAG_POS,
608 ABR_AUFTRAG_TYP,
609 ABR_AUFTRAG_TYP_NAME,
610 SALESNAME,
611 AUFTRAG_ID_SB,
612 TRADE_GOODS,
613 SALESGROUP,
614 CUSTACCOUNTFINAL,
615 CUSTNAME,
616 FINALCUSTPURCHASEORDER,
617 FINALCUSTPURCHASEORDERPOS,
618 VENDORDERNUMBEREXT,
619 SALESPOOLID,
620 SALESIDPREV,
621 LINENUMPREV,
622 IC_ORDER,
623 IC_ENDPOINTID,
624 C_ACCOUNTNUM,
625 C_NAME,
626 I_C_ACCOUNTNUM,
627 I_C_ACCOUNTNAME,
628 CUSTGROUP,
629 CUSTGROUP_NAME,
630 CUSTITEMNUM,
631 CUST_RELEASE_ORDER,
632 C_SEGMENTID,
633 C_SEGMENTID_NAME,
634 VENDORID_1ST_PO,
635 VENDORNAME_1ST_PO,
636 C_PARTYID,
637 EXTERNALCOUNTRYVERSION,
638 PANEM_KEY,
639 T2,
640 T3,
641 T4,
642 T5,
643 T6,
644 T4F,
645 INVOICEID,
646 I_LINENUM,
647 TAX_CODE,
648 PREINVOICETYPE,
649 INVOICETYPE,
650 INVOICEDATE,
651 LEDGERVOUCHER,
652 I_CUSTGROUP,
653 I_CUSTGROUP_NAME,
654 LEDGERACCOUNT,
655 LEDGERACCOUNT_SETUP,
656 I_ADDRNAME,
657 I_CITY,
658 I_ZIPCODE,
659 I_STREET,
660 I_COUNTRYREGIONID,
661 I_COUNTRYREGION_NAME,
662 TERM_OF_PAYMENT,
663 TERM_OF_PAYMENT_DESC,
664 I_PRICEUNIT,
665 I_CURRENCY,
666 I_TAXGROUP,
667 I_VATNUM,
668 EXTERNALINVOICEID,
669 LEDGERPOSTINGTYPE,
670 I_AMOUNT_INCL_VAT_COMPANY_COL,
671 SL_CORRECTED_BY,
672 SL_CORRECTION_TO,
673 I_DELADDRNAME,
674 I_DELCOUNTRYREGIONID,
675 I_DELCITY,
676 I_DELZIPCODE,
677 I_DELSTREET,
678 I_DELTERM,
679 I_DELMODE,
680 I_DELDATE,
681 PACKINGSLIPID,
682 EXTERNALPACKINGSLIPID,
683 ITEMID,
684 ITEMNAME,
685 ITEMVARIANTE,
686 ITEMVARIANTE_NAME,
687 ITEMGROUPID,
688 ITEMCATID,
689 ITEMCLASSID,
690 ITEM_PROPERTY_M104,
691 ITEM_PROPERTY_M305,
692 ITEM_PROPERTY_3010,
693 SAMPLEBOX,
694 PRINTGROUPID,
695 PRINTGROUPNAME,
696 MATERIAL,
697 INVENTLOCATIONID,
698 WMSLOCATIONID,
699 NUMBER_OF_PALLETS,
700 CONFIG_PROP_M305,
701 CONFIG_PROP_M670,
702 CONFIG_PROP_M708,
703 DEPARTMENT,
704 PURCHID_1ST_PO,
705 PURCHLINENUM_1ST_PO,
706 DATAAREAID,
707 LOGISTICS_SITE,
708 FINANCIAL_SITE_NUM,
709 FINANCIAL_SITE,
710 OWNERID,
711 REFERENCE_NO,
712 KOSTENTRAEGER,
713 SL_KOSTENTRAEGER,
714 STANDORT,
715 KOSTENSTELLE,
716 WARENCODE,
717 WARENCODE_NAME,
718 STATISTICSGROUPID1,
719 STATISTICSGROUPNAME1,
720 STATISTICSGROUPID2,
721 STATISTICSGROUPNAME2,
722 STATISTICSGROUPID3,
723 STATISTICSGROUPNAME3,
724 STATISTICSGROUPID4,
725 STATISTICSGROUPNAME4,
726 STATISTICSGROUPID5,
727 STATISTICSGROUPNAME5,
728 FIELDSERVICE,
729 INDOORSERVICE,
730 PRODUCTION_ORDERS_STR,
731 DELIVERYCOUNTRYREGIONID,
732 LEDGERTRANS_SITE,
733 LEDGERTRANS_SITE_DESC,
734 PRICEGROUPID,
735 PRICEGROUPNAME,
736 PRICEGROUPMMECUSTNAME,
737 COMMISSIONGROUP,
738 COMMISSCALC,
739 REVENUE_TYPE,
740 CTL_ACCOUNT,
741 I_SALESUNIT,
742 CURRENCY_COMPANY,
743 EXCH_RATE_TYPE_COMPANY
744 ),
745SalesFactAggCalc_CTE
746AS (
747 SELECT a.*,
748 I_AMOUNT_NET_EUR = I_AMOUNT_NET * R.CROSSRATE / 100,
749 I_AMOUNT_NET_WITH_MP_EUR = I_AMOUNT_NET_WITH_MP_COMPANY * R.CROSSRATE / 100
750 FROM SalesFactAgg_CTE a
751 LEFT JOIN EXCHANGERATEEFFECTIVEVIEW r ON r.PARTITION = a.PARTITION
752 AND r.FROMCURRENCYCODE = a.currency_company
753 AND r.TOCURRENCYCODE = 'EUR'
754 AND r.EXCHANGERATETYPE = a.exch_rate_type_company
755 AND a.invoicedate BETWEEN r.VALIDFROM AND r.VALIDTO
756 )
757SELECT *,
758 OWN_PROD_INVOICE_QTY = IIF(FACT.TRADE_GOODS = 0, FACT.I_QTY, 0),
759 OWN_PROD_INVOICE_AMOUNT = IIF(FACT.TRADE_GOODS = 0, FACT.I_AMOUNT_NET, 0),
760 TRADE_GOODS_INVOICE_QTY = IIF(FACT.TRADE_GOODS = 1, FACT.I_QTY, 0),
761 TRADE_GOODS_INVOICE_AMOUNT = IIF(FACT.TRADE_GOODS = 1, FACT.I_AMOUNT_NET, 0)
762FROM SalesFactAggCalc_CTE fact
763WHERE fact.PARTITION = 5637144576
764 --AND fact.dataareaid = 'npa'
765GO
766
767
768