· 7 years ago · Nov 07, 2018, 08:08 AM
1USE [EnterpriseResourcePlanning]
2
3GO
4DROP VIEW IF EXISTS [dbo].[TransformOrder_Select]
5
6GO
7CREATE VIEW [dbo].[TransformOrder_Select]
8AS
9 SELECT
10 ISNULL(TRIM([Order].[invoiceNbr]), '') AS [Id]
11 , [ParentOrder].[ParentId]
12 , TRIM([Order].[invoiceNbr]) AS [Number]
13 , TRIM([Order].[ccn]) AS [CcnId]
14 , [OrderStatus].[OrderState] AS [OrderStatusId]
15 , [Order_Extra].[entryDate] AS [CreatedOn]
16 , [Order_Extra].[CreatedBy] AS [CreatedByUserId]
17 , [Order_Extra].[requestDate] AS [RequestedOn]
18 , [Order_Extra].[shipDate] AS [ShippedOn]
19 , [Commondata].[dbo].[Ship_Modes].[ID] AS [ShippingModeId]
20 , [OrderProcessingSupport].[dbo].[OrderSource_lk].[SourceCode] AS [OrderSourceId]
21 , CONCAT (
22 ISNULL([Order_Extra].[shipmentRemainder], '0.00')
23 ,';'
24 ,[Order].[tranCurrencyCode]
25 ) AS [BacklogAmount]
26 , CONCAT (
27 [Order].[bookAmt]
28 ,';'
29 ,[Order].[tranCurrencyCode]
30 ) AS [ShippedAmount]
31 , CONCAT (
32 [Order].[bookAmt] + ISNULL([Order_Extra].[shipmentRemainder], '0.00')
33 ,';'
34 ,[Order].[tranCurrencyCode]
35 ) AS [TotalAmount]
36 , [Commondata].[dbo].[Division_Code].[Division_Code] AS [MarketSegmentClassificationId]
37 , [Order].[VATCode] AS [VatRuleId]
38 , [Order].[Orderculture] AS [CultureId]
39 , [Order].[bookedDate] AS [BookedOn]
40 , [Account].[CustomerNumber] AS [AccountId]
41 , [Account].[CustomerNumber] AS [CustomerId]
42 , [BillingSalesPerson].[ID] AS [BillingSalesPersonId] -- this way we're sure the id is valid or null
43 , [ShippingSalesPerson].[ID] AS [ShippingSalesPersonId] -- this way we're sure the id is valid or null
44 , [PaymentTerm].[Id] AS [PaymentTermId]
45 , [OrderHandlingCode].[Id] AS [OrderHandlingCodeId]
46 , TRY_CAST([Order].[billAddrId] AS varchar(20)) + [Order].[TypeOrder] AS [OrderBillingAddressId]
47 , TRY_CAST([Order].[shipAddrId] AS varchar(20)) + [Order].[TypeOrder] AS [OrderShippingAddressId]
48 , TRY_CAST([Order].[InvAddrId] AS varchar(20)) + [Order].[TypeOrder] AS [OrderInvoiceAddressId]
49 , [Order].[customerPO] AS [PurchaseOrderNumber]
50 , [Order].[tranCurrencyCode] AS [CurrencyId]
51 , [StandardQuote].[QuoteNumber] AS [StandardQuoteId]
52 , [OrderSPXQuote_ln].[Id] AS [SystemQuoteId]
53 , [BillingSalesOffice].[RepOffice_id] AS [BillingSalesOfficeId]
54 , [ShippingSalesOffice].[RepOffice_id] AS [ShippingSalesOfficeId]
55 , [OrderLineDates].[OriginalEstimateWhenManufacturingIsFinished]
56 , [OrderLineDates].[EstimateWhenManufacturingIsFinished]
57 , [OrderLineDates].[PromiseWhenManufacturingIsFinished]
58 , [Order].[TypeOrder] AS [TypeOrder]
59 -- S = shipped, O = open, D = draft
60 FROM (
61 SELECT
62 [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[invoiceNbr]
63 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[ccn]
64 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[tranCurrencyCode]
65 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[bookAmt]
66 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[divisionCode]
67 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[VATCode]
68 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[Orderculture]
69 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[bookedDate]
70 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[customerNbr]
71 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[billToShipToCode]
72 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[InvAddrId]
73 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[BillToSalesid]
74 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[ShipToSalesid]
75 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[customerPO]
76 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[shipMode]
77 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[ArTerms]
78 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[handlingCode]
79 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[billAddrId]
80 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[shipAddrId]
81 , 'S' AS [TypeOrder]
82 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[billToRep]
83 , [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[shipToRep]
84 FROM [Daily_ShipmentHistory].[dbo].[ShipmentHeader]
85 WHERE
86 [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[billCode] = 'R'
87 AND NOT EXISTS (
88 SELECT 1
89 FROM [Daily_ShipmentHistory].[dbo].[ShipmentHeader] smh2
90 WHERE
91 smh2.[invoiceNbr] = [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[invoiceNbr]
92 AND smh2.[shipHdrId] > [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[shipHdrId]
93 AND smh2.[billCode] = 'R')
94 UNION
95 SELECT
96 [OpenOrder].[dbo].[OpenOrderHeader].[invoiceNbr]
97 , [OpenOrder].[dbo].[OpenOrderHeader].[ccn]
98 , [OpenOrder].[dbo].[OpenOrderHeader].[tranCurrencyCode]
99 , [OpenOrder].[dbo].[OpenOrderHeader].[bookAmt]
100 , [OpenOrder].[dbo].[OpenOrderHeader].[divisionCode]
101 , [OpenOrder].[dbo].[OpenOrderHeader].[VATCode]
102 , [OpenOrder].[dbo].[OpenOrderHeader].[Orderculture]
103 , [OpenOrder].[dbo].[OpenOrderHeader].[bookedDate]
104 , [OpenOrder].[dbo].[OpenOrderHeader].[customerNbr]
105 , [OpenOrder].[dbo].[OpenOrderHeader].[billToShipToCode]
106 , [OpenOrder].[dbo].[OpenOrderHeader].[InvAddrId]
107 , [OpenOrder].[dbo].[OpenOrderHeader].[BillToSalesid]
108 , [OpenOrder].[dbo].[OpenOrderHeader].[ShipToSalesid]
109 , [OpenOrder].[dbo].[OpenOrderHeader].[customerPO]
110 , [OpenOrder].[dbo].[OpenOrderHeader].[shipMode]
111 , [OpenOrder].[dbo].[OpenOrderHeader].[ArTerms]
112 , [OpenOrder].[dbo].[OpenOrderHeader].[handlingCode]
113 , [OpenOrder].[dbo].[OpenOrderHeader].[billAddrId]
114 , [OpenOrder].[dbo].[OpenOrderHeader].[shipAddrId]
115 , 'O' AS [TypeOrder]
116 , [OpenOrder].[dbo].[OpenOrderHeader].[billToRep]
117 , [OpenOrder].[dbo].[OpenOrderHeader].[shipToRep]
118 FROM [OpenOrder].[dbo].[OpenOrderHeader]
119 WHERE
120 NOT EXISTS (
121 SELECT 1
122 FROM [Daily_ShipmentHistory].[dbo].[ShipmentHeader]
123 WHERE
124 [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[billCode] = 'R'
125 AND [Daily_ShipmentHistory].[dbo].[ShipmentHeader].invoiceNbr = [OpenOrder].[dbo].[OpenOrderHeader].[invoiceNbr])
126 AND NOT EXISTS (
127 SELECT 1
128 FROM [OpenOrder].[dbo].[OpenOrderHeader] ooh2
129 WHERE
130 ooh2.[invoiceNbr] = [OpenOrder].[dbo].[OpenOrderHeader].[invoiceNbr]
131 AND ooh2.[shipHdrId] > [OpenOrder].[dbo].[OpenOrderHeader].[shipHdrId])
132 UNION
133 SELECT
134 [DataCollection].[dbo].[DataCollectionHeader].[invoiceNbr]
135 , [DataCollection].[dbo].[DataCollectionHeader].[ccn]
136 , [DataCollection].[dbo].[DataCollectionHeader].[tranCurrencyCode]
137 , [DataCollection].[dbo].[DataCollectionHeader].[bookAmt]
138 , [DataCollection].[dbo].[DataCollectionHeader].[divisionCode]
139 , [DataCollection].[dbo].[DataCollectionHeader].[VATCode]
140 , [DataCollection].[dbo].[DataCollectionHeader].[Orderculture]
141 , [DataCollection].[dbo].[DataCollectionHeader].[bookedDate]
142 , [DataCollection].[dbo].[DataCollectionHeader].[customerNbr]
143 , [DataCollection].[dbo].[DataCollectionHeader].[billToShipToCode]
144 , [DataCollection].[dbo].[DataCollectionHeader].[InvAddrId]
145 , [DataCollection].[dbo].[DataCollectionHeader].[BillToSalesid]
146 , [DataCollection].[dbo].[DataCollectionHeader].[ShipToSalesid]
147 , [DataCollection].[dbo].[DataCollectionHeader].[customerPO]
148 , [DataCollection].[dbo].[DataCollectionHeader].[shipMode]
149 , [DataCollection].[dbo].[DataCollectionHeader].[ArTerms]
150 , [DataCollection].[dbo].[DataCollectionHeader].[handlingCode]
151 , [DataCollection].[dbo].[DataCollectionHeader].[billAddrId]
152 , [DataCollection].[dbo].[DataCollectionHeader].[shipAddrId]
153 , 'D' AS [TypeOrder]
154 , [DataCollection].[dbo].[DataCollectionHeader].[billToRep]
155 , [DataCollection].[dbo].[DataCollectionHeader].[shipToRep]
156 FROM [DataCollection].[dbo].[DataCollectionHeader]
157 WHERE
158 NOT EXISTS (
159 SELECT 1
160 FROM [Daily_ShipmentHistory].[dbo].[ShipmentHeader]
161 WHERE
162 [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[billCode] = 'R'
163 AND [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[invoiceNbr] = [DataCollection].[dbo].[DataCollectionHeader].[invoiceNbr])
164 AND NOT EXISTS (
165 SELECT 1
166 FROM [OpenOrder].[dbo].[OpenOrderHeader]
167 WHERE
168 [OpenOrder].[dbo].[OpenOrderHeader].[invoiceNbr] = [DataCollection].[dbo].[DataCollectionHeader].[invoiceNbr])
169 AND NOT EXISTS (
170 SELECT 1
171 FROM [DataCollection].[dbo].[DataCollectionHeader] dch2
172 WHERE
173 dch2.[invoiceNbr] = [DataCollection].[dbo].[DataCollectionHeader].[invoiceNbr]
174 AND dch2.[shipHdrId] > [DataCollection].[dbo].[DataCollectionHeader].[shipHdrId])
175) AS [Order]
176-- check to see if link to customer exists (outer apply with top 1 to be sure there's only 1 record to join with)
177OUTER APPLY (
178 SELECT TOP 1
179 TRIM([customermaster].[dbo].[CMF_Bill_To].[customer_number]) + TRIM([customermaster].[dbo].[CMF_Bill_To].[customer_number_suffix]) AS [CustomerNumber]
180 FROM
181 [customermaster].[dbo].[CMF_Bill_To]
182 WHERE
183 [customermaster].[dbo].[CMF_Bill_To].[customer_number] = [Order].[customerNbr] AND
184 [customermaster].[dbo].[CMF_Bill_To].[customer_number_suffix] = [Order].[billToShipToCode]
185) AS [Account]
186
187-- Foreign key relationship check.
188OUTER APPLY (
189 SELECT TOP 1
190 [Commondata].[dbo].[PaymentTerms].[ccn] + [Commondata].[dbo].[PaymentTerms].[TermCode] AS [Id]
191 FROM
192 [Commondata].[dbo].[PaymentTerms]
193 WHERE
194 [Order].[ArTerms] = [Commondata].[dbo].[PaymentTerms].[TermCode] AND
195 [Order].[ccn] = [Commondata].[dbo].[PaymentTerms].[ccn]
196 ) AS [PaymentTerm]
197
198-- Foreign key relationship check.
199OUTER APPLY (
200 SELECT TOP 1
201 [Commondata].[dbo].[RepOffices].[RepOffice_id]
202 FROM
203 [Commondata].[dbo].[RepOffices]
204 WHERE
205 [Commondata].[dbo].[RepOffices].[Repno] = [Order].[billToRep]
206 AND [Commondata].[dbo].[RepOffices].[Active] = 1
207) AS [BillingSalesOffice]
208
209-- Foreign key relationship check.
210OUTER APPLY (
211 SELECT TOP 1
212 [Commondata].[dbo].[HandlingCode].[CCN] + [Commondata].[dbo].[HandlingCode].[HandlingCode] AS Id
213 FROM
214 [Commondata].[dbo].[HandlingCode]
215 WHERE
216 [Order].[handlingCode] = [Commondata].[dbo].[HandlingCode].[HandlingCode] AND
217 [order].[ccn] = [Commondata].[dbo].[HandlingCode].[CCN]
218) AS [OrderHandlingCode]
219
220-- Foreign key relationship check.
221OUTER APPLY (
222 SELECT TOP 1
223 TRY_CAST([OrderProcessingSupport].[dbo].[InvoiceQuoteNumber].[quoteNumber] AS INT) AS [QuoteNumber]
224 FROM
225 [OrderProcessingSupport].[dbo].[InvoiceQuoteNumber]
226 WHERE
227 [OrderProcessingSupport].[dbo].[InvoiceQuoteNumber].[quoteNumber] = [OrderProcessingSupport].[dbo].[InvoiceQuoteNumber].[quoteNumber]
228) AS [StandardQuote]
229
230-- Foreign key relationship check.
231OUTER APPLY (
232 SELECT TOP 1
233 [Commondata].[dbo].[RepOffices].[RepOffice_id]
234 FROM
235 [Commondata].[dbo].[RepOffices]
236 WHERE
237 [Commondata].[dbo].[RepOffices].[Repno] = [Order].[shipToRep]
238 AND [Commondata].[dbo].[RepOffices].[Active] = 1
239) AS [ShippingSalesOffice]
240 -- Foreign key relationship check.
241 LEFT JOIN
242 [Commondata].[dbo].[Division_Code] ON [Commondata].[dbo].[Division_Code].[Division_Code] = [Order].[divisionCode]
243 -- Foreign key relationship check.
244 LEFT JOIN
245 [UserProfile].[dbo].[UserProfile] [BillingSalesPerson] ON [BillingSalesPerson].[ID] = [Order].[BillToSalesid]
246 -- Foreign key relationship check.
247 LEFT JOIN
248 [UserProfile].[dbo].[UserProfile] [ShippingSalesPerson] ON [ShippingSalesPerson].[ID] = [Order].[ShipToSalesid]
249 -- Foreign key relationship check.
250 LEFT JOIN
251 [Commondata].[dbo].[Ship_Modes] ON [Order].[shipMode] = [Commondata].[dbo].[Ship_Modes].[Ship_Modes] AND [Order].[CCN] = [Commondata].[dbo].[Ship_Modes].[CCN]
252 LEFT JOIN
253 [OrderProcessingSupport].[dbo].[InvoiceQuoteNumber] ON [Order].[invoiceNbr] = [OrderProcessingSupport].[dbo].[InvoiceQuoteNumber].[invoiceNumber]
254OUTER APPLY (
255 SELECT TOP 1
256 TRIM([OrderProcessingSupport].[dbo].[OrderCrossReference].[invoiceNbr]) AS [ParentId]
257 FROM
258 [OrderProcessingSupport].[dbo].[OrderCrossReference]
259 WHERE
260 [OrderProcessingSupport].[dbo].[OrderCrossReference].[orderfromInvNbr] = [Order].[invoiceNbr] AND
261 [OrderProcessingSupport].[dbo].[OrderCrossReference].[orderfromCCN] = [Order].[ccn]
262) [ParentOrder]
263OUTER APPLY (
264 SELECT TOP 1
265 [OrderProcessingSupport].[dbo].[Invoice].[invoiceNbr]
266 , [OrderProcessingSupport].[dbo].[Invoice].[orderState]
267 , [OrderProcessingSupport].[dbo].[Invoice].[shipmentRemainder]
268 , [OrderProcessingSupport].[dbo].[Invoice].[shipDate]
269 , [OrderProcessingSupport].[dbo].[Invoice].[SourceCode]
270 , [OrderProcessingSupport].[dbo].[Invoice].[entryDate]
271 , [OrderProcessingSupport].[dbo].[Invoice].[requestDate]
272 , [OrderProcessingSupport].[dbo].[Invoice].[CreatedBy]
273 FROM
274 [OrderProcessingSupport].[dbo].[Invoice]
275 WHERE
276 [OrderProcessingSupport].[dbo].[Invoice].[invoiceNbr] = [Order].[invoiceNbr]
277) [Order_Extra]
278-- Foreign key relationship check.
279OUTER APPLY (
280 SELECT TOP 1
281 [OrderProcessingSupport].[dbo].[OrderState_lk].[OrderState]
282 FROM
283 [OrderProcessingSupport].[dbo].[OrderState_lk]
284 WHERE
285 [Order_Extra].[orderState] = [OrderProcessingSupport].[dbo].[OrderState_lk].[OrderState]
286 ORDER BY
287 CASE [OrderProcessingSupport].[dbo].[OrderState_lk].[CCN]
288 WHEN 'SSCO' THEN 1
289 ELSE 2
290 END
291) [OrderStatus]
292 -- Foreign key relationship check.
293 LEFT JOIN
294 [OrderProcessingSupport].[dbo].[OrderSource_lk] ON [OrderProcessingSupport].[dbo].[OrderSource_lk].[SourceCode] = TRIM([Order_Extra].[SourceCode])
295OUTER APPLY (
296 SELECT TOP 1
297 [a2ds].[dbo].[Quote].[Id]
298 FROM
299 [OrderProcessingSupport].[dbo].[OrderSPXQuote_ln]
300 INNER JOIN
301 [a2ds].[dbo].[Quote] ON
302 [OrderProcessingSupport].[dbo].[OrderSPXQuote_ln].[SpxQuoteID] = [a2ds].[dbo].[Quote].[QuoteID]
303 AND [OrderProcessingSupport].[dbo].[OrderSPXQuote_ln].[SpxQuoteVersion] = [a2ds].[dbo].[Quote].[Version]
304 WHERE [Order].[invoiceNbr] = [OrderProcessingSupport].[dbo].[OrderSPXQuote_ln].[InvoiceNbr]
305) [OrderSPXQuote_ln]
306OUTER APPLY (
307 SELECT
308 MAX([asOfAckDate]) AS [OriginalEstimateWhenManufacturingIsFinished]
309 , MAX([ackDate]) AS [EstimateWhenManufacturingIsFinished]
310 , MAX([repromiseDate]) AS [PromiseWhenManufacturingIsFinished]
311 FROM (
312 SELECT
313 [Daily_ShipmentHistory].[dbo].[ShipmentDetail].[asOfAckDate]
314 , [Daily_ShipmentHistory].[dbo].[ShipmentDetail].[ackDate]
315 , [Daily_ShipmentHistory].[dbo].[ShipmentDetail].[repromiseDate]
316 FROM
317 [Daily_ShipmentHistory].[dbo].[ShipmentDetail]
318 JOIN
319 [Daily_ShipmentHistory].[dbo].[ShipmentHeader] ON [Daily_ShipmentHistory].[dbo].[ShipmentDetail].[ShipHdrId] = [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[ShipHdrId]
320 WHERE
321 [Order].[invoiceNbr] = [Daily_ShipmentHistory].[dbo].[ShipmentHeader].[invoiceNbr] AND
322 [Order].[TypeOrder] = 'S'
323 UNION
324 SELECT
325 [OpenOrder].[dbo].[OpenOrderDetail].[asOfAckDate]
326 , [OpenOrder].[dbo].[OpenOrderDetail].[ackDate]
327 , [OpenOrder].[dbo].[OpenOrderDetail].[repromiseDate]
328 FROM
329 [OpenOrder].[dbo].[OpenOrderDetail]
330 JOIN
331 [OpenOrder].[dbo].[OpenOrderHeader] ON [OpenOrder].[dbo].[OpenOrderDetail].[ShipHdrId] = [OpenOrder].[dbo].[OpenOrderHeader].[ShipHdrId]
332 WHERE
333 [Order].[invoiceNbr] = [OpenOrder].[dbo].[OpenOrderHeader].[invoiceNbr] AND
334 [Order].[TypeOrder] = 'O'
335 UNION
336 SELECT
337 [DataCollection].[dbo].[DataCollectionDetail].[asOfAckDate]
338 , [DataCollection].[dbo].[DataCollectionDetail].[ackDate]
339 , [DataCollection].[dbo].[DataCollectionDetail].[repromiseDate]
340 FROM
341 [DataCollection].[dbo].[DataCollectionDetail]
342 JOIN
343 [DataCollection].[dbo].[DataCollectionHeader] ON [DataCollection].[dbo].[DataCollectionDetail].[ShipHdrId] = [DataCollection].[dbo].[DataCollectionHeader].[ShipHdrId]
344 WHERE
345 [Order].[invoiceNbr] = [DataCollection].[dbo].[DataCollectionHeader].[invoiceNbr] AND
346 [Order].[TypeOrder] = 'D'
347 ) AS [OrderLines]
348) AS [OrderLineDates]
349
350-- [OpenOrder].[dbo].[OpenOrderHeader].[shipRepDisplay]: (Swarup) Obsolete.
351-- [OpenOrder].[dbo].[OpenOrderHeader].[shipRepCodeDisplay]: (Swarup) Obsolete.
352-- [OpenOrder].[dbo].[OpenOrderHeader].[releaseNbr]: (Swarup) Obsolete.
353-- [OpenOrder].[dbo].[OpenOrderHeader].[mincqCode]: (Swarup) Obsolete.
354-- [OpenOrder].[dbo].[OpenOrderHeader].[repOverrideCode]: (Swarup) Obsolete.
355-- [OpenOrder].[dbo].[OpenOrderHeader].[maxPartials]: (Swarup) Obsolete.
356-- [OpenOrder].[dbo].[OpenOrderHeader].[UseNetPrice]: (Swarup) Obsolete. Configuration entry for order entry on how to calculate the line item value.
357-- [OpenOrder].[dbo].[OpenOrderHeader].[insExemptCode]: (Swarup) Obsolete.
358-- [OpenOrder].[dbo].[OpenOrderHeader].[TermCode]: (Swarup) Obsolete. CONCAT([ccn], [TermCode]) AS [Id] is used
359-- [OpenOrder].[dbo].[OpenOrderHeader].[CrossOrderLogo]: (Brecht) No values in the database, possibly unused. Specifies if the order came from other company and is direct shipped to the customer.
360-- [OpenOrder].[dbo].[OpenOrderHeader].[restockChargeCode]: (Swarup) Obsolete.
361-- [OpenOrder].[dbo].[OpenOrderHeader].[SeparateShip]: (Swarup) Obsolete.
362-- [OpenOrder].[dbo].[OpenOrderHeader].[RushCode]: (Swarup) Used for internal calculation. We may mark it obsolete.
363-- [OpenOrder].[dbo].[OpenOrderHeader].[OrignalNumber]: (Don) Obsolete.
364-- [OpenOrder].[dbo].[OpenOrderHeader].[ActRelDate]: (Swarup) Obsolete.
365-- [OpenOrder].[dbo].[OpenOrderHeader].[RowVer]: (Brecht) Should not be used.
366-- [OpenOrder].[dbo].[OpenOrderHeader].[LanguageCode]: (Brecht) Shouldn't be saved to the database. (Jim) Should equal the first two letters of OrderCulture.
367-- [OpenOrder].[dbo].[OpenOrderHeader].[ActRelUpId]: (Swarup) Obsolete.
368-- [OpenOrder].[dbo].[OpenOrderHeader].[SentSalesManCode]: (Brecht) shouldn't be saved, we can query this through the ID. (Swarup) it specifies the 3rd representative (person/company) code involved in sales or marketing deal, in other words - the 3rd person who helped between two respresentative on dealing an order. Sourced from - [UserProfile].[dbo].[RepSalesmanCode].[SalesmanCode]
369-- [OpenOrder].[dbo].[OpenOrderHeader].[sentByRep]: (Brecht) shouldn't be saved, we can query this through the ID. (Swarup) it specifies the 3rd representative (person/company) number involved in sales or marketing deal, in other words - the 3rd person who helped between two respresentative on dealing an order. Sourced from [UserProfile].[dbo].[RepSalesmanCode].[RepNbr].
370-- [OpenOrder].[dbo].[OpenOrderHeader].[shipToRep]: (Brecht) shouldn't be saved, we can query this through the ID. (Swarup) it specifies the shipping representative number who involved in order shiping process. Sourced from [UserProfile].[dbo].[RepSalesmanCode].[RepNbr].
371-- [OpenOrder].[dbo].[OpenOrderHeader].[ShipSalesManCode]: (Brecht) shouldn't be saved, we can query this through the ID. (Swarup) it specifies the sales person code who involved in the order shipping process. Sourced from [UserProfile].[dbo].[RepSalesmanCode].[SalesmanCode].
372-- [OpenOrder].[dbo].[OpenOrderHeader].[billToRep]: (Brecht) shouldn't be saved, we can query this through the ID. (Swarup) specifies the sales office incharge of customer order.
373-- [OpenOrder].[dbo].[OpenOrderHeader].[BillSalesManCode]: (Brecht) shouldn't be saved, we can query this through the ID. (Swarup) sales representative who is responsible for Billing location of an order. It maps to [customermaster].[dbo].[CMF_Bill_To].salesman.
374
375-- [OpenOrder].[dbo].[OpenOrderHeader].[InvAddrId]: (Felix) The unique identifier of the OrderInvoiceAddress. ('OrderInvoiceAddressId' int?)
376 --,COALESCE(
377 -- CASE
378 -- WHEN [OpenOrder].[InvAddrId] IS NULL THEN NULL
379 -- ELSE CONCAT([OpenOrder].[InvAddrId], 'O')
380 -- END
381 -- ,CASE
382 -- WHEN [DraftOrder].[InvAddrId] IS NULL THEN NULL
383 -- ELSE CONCAT([DraftOrder].[InvAddrId], 'D')
384 -- END) AS [OrderInvoiceAddressId]
385
386-- [OpenOrder].[dbo].[OpenOrderHeader].[pointerCode]: (Felix) moved from model ('OrderStatus' string)
387 -- TODO: Don will report back if this property should be exposed.
388 -- Order Status ex: 'O' - Open order , 'S' -Shipped order once, 'B' -Shipped order multiple
389 -- Source from : [OrderProcessingSupport].[dbo].[Invoice].[orderState]
390
391-- [OpenOrder].[dbo].[OpenOrderHeader].[PaymentDueDate]: (Felix) moved from model ('PaymentDueDate' DateTime?)
392 -- It specify the last payment date of invoice.
393 -- TODO: why is this not a property of the invoice? Surely the invoice needs to be paid and not an order.
394 -- Yes, it is qualified to be part of Invoice entity. Definition simplified
395
396-- [OpenOrder].[dbo].[OpenOrderHeader].[paymentMethod]: (Felix) moved from model ('PaymentMethodId' string)
397 -- The unique identifier of the PaymentMethod
398 -- TODO: add to PaymentMethod definition "Specifies the way that a customer chooses to compensate us for a good or service. Typical payment methods include credit card, bank transfer." Source from : [Commondata].[dbo].[PaymentMethod]
399 -- PaymentMethod entity not yet added, it is noted down and will add in commondata api, later either will expose from common data api or copied here. </remarks>
400
401-- [OpenOrder].[dbo].[OpenOrderHeader].[ARTerms]: (Felix) moved from model ('PaymentTermId' string)
402 -- specifies the terms of payments ex: in 30 days or 60 or credit card
403 -- Source from : [Commondata].[dbo].[PaymentTerms]
404
405-- [OpenOrder].[dbo].[OpenOrderHeader].[creditCode]: (Felix) moved from model ('CreditTypeId' string)
406 -- Specifies the Mode of customer credit ex: COD
407 -- Source from : [Commondata].[dbo].[CreditType]
408
409-- [OpenOrder].[dbo].[OpenOrderHeader].[invType]: (Felix) moved from model ('InvoiceTypeId' string)
410 -- Specifies the type of invoice maps to commondate
411
412-- [OpenOrder].[dbo].[OpenOrderHeader].[bookedDate]: (Felix) moved from model ('BookedOn' DateTime)
413 -- Specifies the date when order open in the system
414
415-- [OpenOrder].[dbo].[OpenOrderHeader].[Carrier]: (Felix) moved from model ('Carrier' string)
416 -- The provider of the transport service. For example DHL, UPS.
417
418-- [OpenOrder].[dbo].[OpenOrderHeader].[creditDescCode]: (Felix) moved from model ('CreditDescriptionId' string)
419 -- Specifies the description of item resturned
420 -- Sourced from CommonData.dbo.CrtMemType
421 -- TODO: what table does this point to?
422 -- Sourced from CommonData.dbo.CrtMemType
423
424-- [OpenOrder].[dbo].[OpenOrderHeader].[creditShipNbr]: (Felix) moved from model ('CreditShipNumber' string)
425 -- It associated with print sequence number, when the item returned, the assigned print sequence number will be captured in this field
426
427-- [OpenOrder].[dbo].[OpenOrderHeader].[Comments]: (Felix) moved from model ('Comment' string)
428
429-- [OpenOrder].[dbo].[OpenOrderHeader].[CommentCode]: (Felix) moved from model ('CommentCodeId' string)
430 -- TODO: lacks description. Points to Commondata.CommentCode
431
432-- [OpenOrder].[dbo].[OpenOrderHeader].[Commission]: (Felix) moved from model ('Commission' decimal)
433 -- A payment to someone who sells goods that is directly related to the amount sold.
434
435-- [OpenOrder].[dbo].[OpenOrderHeader].[SplitCode]: (Felix) moved from model ('CommissionSplitCodeId' string)
436 -- Specifies if any commission is applicable on this <see cref="Order"/> and how it is split.
437 -- Commondata.dbo.SplitCode
438
439-- [OpenOrder].[dbo].[OpenOrderHeader].[commTotal]: (Felix) moved from model ('CommissionableAmount' decimal)
440 -- The amount which is eligible for commission. The <see cref="Commission"/> is calculated based on this amount.
441
442-- [OpenOrder].[dbo].[OpenOrderHeader].[CRANbr]: (Felix) moved from model ('CRANumber' string)
443 -- Customer Return Authorization Number : It is related to customer returns item that bought.
444
445-- [OpenOrder].[dbo].[OpenOrderHeader].[tranCurrencyCode]: (Felix) moved from model ('CustomerCurrencyCode' string)
446 -- Specifies the type of customer currency used
447 -- TODO: is this a copy of Customer.CurrencyCode? Can we take it from the Customer entity and not expose this or is there a history possible where it differs from Customer.CurrencyCode?
448 -- This is not from Customer CurrenyCode, In Customer.CurrencyCode, it sourced from ISOCurrencyCode table and tranCurrencyCode is from ORDPCore.[dbo].[spGetCurrencies] and inside the Sp, there is table commondata.dbo.OrderEntryExchangeRate used
449 -- It's called CustomerCurrencyCode, does that mean Order.CustomerCurrencyCode == Order.Customer.CurrencyCode?
450
451-- [OpenOrder].[dbo].[OpenOrderHeader].[custFreightAmt]: (Felix) moved from model ('CustomerFreightAmount' decimal)
452 -- CustomerFreightAmount is the the charge paid by customer for carriage or transportation of Order to ship.
453 -- Freight : A charge paid for carriage or transportation of goods by air, land, or sea.
454 -- TODO: what is the difference between this and the Freight/FreightAmount property?-- I don't see Freight property? we have FreightAmount & CustomerFreightAmount
455 -- There is, please recheck.
456
457-- [OpenOrder].[dbo].[OpenOrderHeader].[custInsAmt]: (Felix) moved from model ('CustomerInsuranceAmount' string)
458 -- Insurance paid for the order
459 -- TODO: what is the difference between this and the InsuranceAmount property?
460
461-- [OpenOrder].[dbo].[OpenOrderHeader].[DateBilled]: (Felix) moved from model ('BilledOn' DateTime?)
462 -- DateBilled and Invoice date values are exactly matching in view.
463 -- TODO: better description. What is the difference between this and the InvoiceDate property?
464
465-- [OpenOrder].[dbo].[OpenOrderHeader].[DeliveryCode]: (Felix) moved from model ('DeliveryCode' string)
466 -- TODO: lacks description.
467
468-- [OpenOrder].[dbo].[OpenOrderHeader].[Email]: (Felix) moved from model ('EmailAddress' string)
469 -- The e-mail address which will be used to send order acknowledgements to. By default this is set to the customer's e-mail address but it can be changed.
470
471-- [OpenOrder].[dbo].[OpenOrderHeader].[tranExchangeRate]: (Felix) moved from model ('ExchangeRate' string)
472 -- Specifies the customer currency exchange rate
473
474-- [OpenOrder].[dbo].[OpenOrderHeader].[freightAmt]: (Felix) moved from model ('Freight' decimal)
475 -- A charge paid for carriage or transportation of goods by air, land or sea.
476
477-- [OpenOrder].[dbo].[OpenOrderHeader].[FreightTaxAmt]: (Felix) moved from model ('FreightTax' decimal)
478 -- The taxes levied upon <see cref="Freight"/>.
479
480-- [OpenOrder].[dbo].[OpenOrderHeader].[HandlingCode]: (Felix) moved from model ('HandlingCodeId' string)
481 -- specifies handling code of the system
482 -- TODO: what is a handling code? Does this point to a table?
483
484-- [OpenOrder].[dbo].[OpenOrderHeader].[OrderIncoTermsCode]: (Felix) moved from model ('IncoTermId' int?)
485 -- The unique identifier of the <see cref="IncoTerm"/>.
486 -- Sourced from [Commondata].[dbo].[IncoTermCCN]
487
488-- [OpenOrder].[dbo].[OpenOrderHeader].[insAmt]: (Felix) moved from model ('InsuranceAmount' decimal)
489 -- Specifies the insurance amount for the order
490 -- TODO: who pays this insurance? What's being insured? The transport?
491
492-- [OpenOrder].[dbo].[OpenOrderHeader].[billCode]: (Felix) moved from model ('IsBillOnly' bool)
493 -- It sepecifies whether we need to send invoice or bill only to the customer.
494 -- TODO: check description with Swarup.
495 -- definition simplified
496
497-- [OpenOrder].[dbo].[OpenOrderHeader].[CODOverride]: (Felix) moved from model ('IsCashOnDeliveryOverride' bool)
498 -- Specifies whether we need to override default order shipping mode of the <see cref="CustomerAccount"/> for this <see cref="Order"/>.
499 -- TODO: better description. Why would the shipping mode need to be overridden?
500 -- Order shipping mode sets up in customer master when we create customer, that can be change when placing order
501 -- This is a very weird name then. Why is the property not named OverrideShippingMode instead of cash on delivery? Or is cash on delivery the only thing one is able to override?
502
503-- [OpenOrder].[dbo].[OpenOrderHeader].[domIntl]: (Felix) moved from model ('IsDomestic' bool)
504 -- It specifies whether the shipping and billing address is domestic or international.
505 -- TODO: what about the billing address? Does that have its own flag? If possible this should be moved to the address entity.
506 -- definition simplified
507
508-- [OpenOrder].[dbo].[OpenOrderHeader].[creditMaterialReturn]: (Felix) moved from model ('IsMaterialReturned' bool)
509 -- Specifies whether the <see cref="Account"/> returned the material or product at this point in time. For example if the product is defective.
510
511-- [OpenOrder].[dbo].[OpenOrderHeader].[taxExemptCode]: (Felix) moved from model ('IsSalesTaxExempt' bool)
512 -- TODO: lacks description.
513
514-- [OpenOrder].[dbo].[OpenOrderHeader].[Payments]: (Felix) moved from model ('IsSinglePayment' bool)
515 -- Specifies whether this <see cref="Order"/> should be paid in one single payment or if multiple payments are allowed.
516
517-- [OpenOrder].[dbo].[OpenOrderHeader].[noprintInstr]: (Felix) moved from model ('NoPrintInstruction' string)
518 -- Print on picklist for internal
519
520-- [OpenOrder].[dbo].[OpenOrderHeader].[prtInstr1]: (Felix) moved from model ('PrintOnPackageSlip1' string)
521 -- Specifies the printing instructions on package slip1
522
523-- [OpenOrder].[dbo].[OpenOrderHeader].[prtInstr2]: (Felix) moved from model ('PrintOnPackageSlip2' string)
524 -- Specifies the printing instructions on package slip2
525
526-- [OpenOrder].[dbo].[OpenOrderHeader].[PoDate]: (Felix) moved from model ('PurchaseOrderDate' DateTime?)
527 -- It specifies the date when purchase order created in purchasing system.
528 -- TODO: is this a copy of a property of the PurchaseOrder?
529 -- We don't have purchaseorder entity, if we create separate entity, then yes, this property is qualify for purchaseorder entity
530 -- We do in the Purchasing system. Check the Purchasing database or the Purchasing data layer.
531
532-- [OpenOrder].[dbo].[OpenOrderHeader].[ReceivedDate]: (Felix) moved from model ('PurchaseOrderReceivedDate' DateTime?)
533 -- Specifies the date when we received the purchase order
534 -- TODO: is this a copy of a property of the PurchaseOrder?
535 -- We don't have purchaseorder entity, if we create separate entity, then yes, this property is qualify for purchaseorder entity
536
537-- [OpenOrder].[dbo].[OpenOrderHeader].[RepromiseNbr]: (Felix) moved from model ('RepromiseNumber' int)
538 -- It specifies the number of promise we made to the customer if order has not delivered on requested time.
539 -- TODO: What is promised to the customer?
540 -- Promise to the customer for timely delivering the customer order
541
542-- [OpenOrder].[dbo].[OpenOrderHeader].[CreditReasonCode]: (Felix) moved from model ('ReturnReasonCodeId' string)
543 -- Specifies the reason of item returned from Credit Memo system
544 -- TODO: perhaps a better name is ReturnReasonCode? [Commondata].[dbo].[CreditReasonCode]
545 -- Property renamed
546
547-- [OpenOrder].[dbo].[OpenOrderHeader].[revNbr]: (Felix) moved from model ('RevisionNumber' string)
548 -- Specifies the revision number of an order
549 -- TODO: how can an order have a revision number?
550 -- When an order cancelled and we redo the same order again, then revision number assigned, initially it is empty value
551
552-- [OpenOrder].[dbo].[OpenOrderHeader].[ShipmentRemainder]: (Felix) moved from model ('ShipmentRemainder' decimal)
553 -- Partial shipping remainder: it specifies how many order needs to be ship
554 -- TODO: description unclear. The value appears to be an amount of something. What does the amount mean?
555
556-- [OpenOrder].[dbo].[OpenOrderHeader].[prtSeq]: (Felix) moved from model ('ShipmentSequence' string)
557 -- It's a incremental sequence number of an item moving from one process to another process
558
559-- [OpenOrder].[dbo].[OpenOrderHeader].[ShipMode]: (Felix) moved from model ('ShipMode' string)
560 -- Specifies the delivery method of the goods of this <see cref="Order"/>. Usually tied to the <see cref="Carrier"/>. For example DHL-BBX, Fastway Bag.
561
562-- [OpenOrder].[dbo].[OpenOrderHeader].[ShipAccountNo]: (Felix) moved from model ('ShippingAccountNo' string)
563 -- It specifies the Customer number from CMF_Bill_To or Customer Company Account number mentions in the shipping carrier.
564 -- TODO: description unclear. Is this the CMF_Ship_To, CMF_Bill_To customer account number? Doesn't seem so based on the data.
565 -- definition simplified
566
567-- [OpenOrder].[dbo].[OpenOrderHeader].[taxableAmt]: (Felix) moved from model ('TaxableAmount' decimal)
568
569-- [OpenOrder].[dbo].[OpenOrderHeader].[TaxAmt]: (Felix) moved from model ('TaxAmount' decimal)
570
571-- [OpenOrder].[dbo].[OpenOrderHeader].[TaxRate]: (Felix) moved from model ('TaxRate' decimal)
572
573-- [OpenOrder].[dbo].[OpenOrderHeader].[TrackingCode]: (Felix) moved from model ('TrackingCode' string)
574 -- This field is used by marketing team for analysis on orderlife cycle.
575
576-- [OpenOrder].[dbo].[OpenOrderHeader].[TransactionID]: (Felix) moved from model ('TransactionID' Guid?)
577 -- Identity generated and used by different systems.
578
579-- [OpenOrder].[dbo].[OpenOrderHeader].[OrderType]: (Felix) moved from model ('Type' string)
580 -- Specifies the type of order in a system
581 -- TODO: values found are "S", "A" and "". What do they mean?
582 -- S-'Spx Order, Per Jim, we have only system entered which is "S" - Spx system
583
584-- [OpenOrder].[dbo].[OpenOrderHeader].[UpsMoreTracking]: (Felix) moved from model ('UpsMoreTracking' string)
585 -- It specifies the more tracking code of order shipping to the customer. If we have more than 2 tracking number, will store in UpsMoreTracking # field.
586 -- TODO: lacks description. Is this column being used?
587 -- definition simplified, found it null
588
589-- [OpenOrder].[dbo].[OpenOrderHeader].[UpsTracking1]: (Felix) moved from model ('UpsTracking1' string)
590 -- It specifies the 1st tracking code of order shipping to the customer.
591 -- TODO: lacks description. Is this column being used?
592 -- definition simplified, there are some values in Db.
593
594-- [OpenOrder].[dbo].[OpenOrderHeader].[UpsTracking2]: (Felix) moved from model ('UpsTracking2' string)
595 -- It specifies the 2nd tracking code of order shipping to the customer
596 -- TODO: lacks description. Is this column being used?
597 -- definition simplified, found 1 or 2 records