F_Belege_queries.sql 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447
  1. CREATE
  2. OR
  3. ALTER VIEW [load].[current_date_Prognose]
  4. AS
  5. SELECT --[Bundeslaender Id] AS [Bundeslaender Id],
  6. [Datum] AS [Bookkeep Date],
  7. --[Wochentage Id] AS [Wochentage Id],
  8. [Arbeitstag Mofr] AS [Arbeitstag Mofr],
  9. [Zaehler Mofr] AS [Zaehler Mofr],
  10. [Summe Mofr] AS [Summe Mofr],
  11. --[Arbeitstag Mosa] AS [Arbeitstag Mosa],
  12. --[Zaehler Mosa] AS [Zaehler Mosa],
  13. --[Summe Mosa] AS [Summe Mosa],
  14. --[Feiertage Id] AS [Feiertage Id],
  15. --[Jahr] AS [Jahr],
  16. --[Arbeitstag Nr Jahr] AS [Arbeitstag Nr Jahr],
  17. --[Gesamt Arbeitstage] AS [Gesamt Arbeitstage]
  18. FROM [export_csv].[current_date_Prognose]
  19. GO
  20. CREATE
  21. OR
  22. ALTER VIEW [load].[Kontenrahmen]
  23. AS
  24. SELECT --[Konto_Nr] AS [Konto_Nr],
  25. --[Konto_Bezeichnung] AS [Konto_Bezeichnung],
  26. --[Konto_Art] AS [Konto_Art],
  27. [Kostenstelle] AS [Kostenstelle],
  28. --[STK] AS [STK],
  29. --[Konto_1] AS [Konto_1],
  30. --[Konto_2] AS [Konto_2],
  31. --[Konto_3] AS [Konto_3],
  32. --[Konto_4] AS [Konto_4],
  33. --[Konto_5] AS [Konto_5],
  34. [Ebene1] AS [Ebene1],
  35. [Ebene2] AS [Ebene2],
  36. [Ebene3] AS [Ebene3],
  37. [Ebene4] AS [Ebene4],
  38. [Ebene5] AS [Ebene5],
  39. --[Ebene6] AS [Ebene6],
  40. --[Ebene7] AS [Ebene7],
  41. --[Ebene8] AS [Ebene8],
  42. --[Ebene9] AS [Ebene9],
  43. --[Ebene10] AS [Ebene10],
  44. --[Ebene11] AS [Ebene11],
  45. [Ebene12] AS [Ebene12],
  46. [Ebene13] AS [Ebene13],
  47. [Ebene14] AS [Ebene14],
  48. [Ebene15] AS [Ebene15],
  49. --[Ebene16] AS [Ebene16],
  50. --[Ebene17] AS [Ebene17],
  51. --[Ebene18] AS [Ebene18],
  52. --[Ebene19] AS [Ebene19],
  53. --[Ebene20] AS [Ebene20],
  54. [Ebene21] AS [Ebene21],
  55. [Ebene22] AS [Ebene22],
  56. [Ebene23] AS [Ebene23],
  57. [Ebene24] AS [Ebene24],
  58. [Ebene25] AS [Ebene25],
  59. [Ebene26] AS [Ebene26],
  60. --[Ebene27] AS [Ebene27],
  61. --[Ebene28] AS [Ebene28],
  62. --[Ebene29] AS [Ebene29],
  63. --[Ebene30] AS [Ebene30],
  64. [Ebene31] AS [Ebene31],
  65. [Ebene32] AS [Ebene32],
  66. [Ebene33] AS [Ebene33],
  67. [Ebene34] AS [Ebene34],
  68. [Ebene35] AS [Ebene35],
  69. [Ebene36] AS [Ebene36],
  70. --[Ebene37] AS [Ebene37],
  71. --[Ebene38] AS [Ebene38],
  72. --[Ebene39] AS [Ebene39],
  73. --[Ebene40] AS [Ebene40],
  74. --[Ebene41] AS [Ebene41],
  75. --[Ebene42] AS [Ebene42],
  76. --[Ebene43] AS [Ebene43],
  77. --[Ebene44] AS [Ebene44],
  78. --[Ebene45] AS [Ebene45],
  79. --[Ebene46] AS [Ebene46],
  80. --[Ebene47] AS [Ebene47],
  81. --[Ebene48] AS [Ebene48],
  82. --[Ebene49] AS [Ebene49],
  83. --[Ebene50] AS [Ebene50],
  84. --[Ebene51] AS [Ebene51],
  85. --[Ebene52] AS [Ebene52],
  86. --[Ebene53] AS [Ebene53],
  87. --[Ebene54] AS [Ebene54],
  88. --[Ebene55] AS [Ebene55],
  89. --[Ebene56] AS [Ebene56],
  90. --[Ebene57] AS [Ebene57],
  91. --[Ebene58] AS [Ebene58],
  92. --[Ebene59] AS [Ebene59],
  93. --[Ebene60] AS [Ebene60],
  94. [Konto_Art] AS [GuV_Bilanz],
  95. left ( [Konto_Nr] , 1 ) AS [Susa],
  96. [Konto_Nr] AS [Acct Nr],
  97. [Konto_Nr] + ' - ' + [Konto_Bezeichnung] AS [Konto]
  98. FROM [export_csv].[Kontenrahmen]
  99. GO
  100. CREATE
  101. OR
  102. ALTER VIEW [load].[Kontenrahmen_STK]
  103. AS
  104. SELECT --[Konto_Nr] AS [Konto_Nr],
  105. --[Konto_Bezeichnung] AS [Konto_Bezeichnung],
  106. --[Konto_Art] AS [Konto_Art],
  107. [Kostenstelle] AS [Kostenstelle],
  108. --[STK] AS [STK],
  109. --[Konto_1] AS [Konto_1],
  110. --[Konto_2] AS [Konto_2],
  111. --[Konto_3] AS [Konto_3],
  112. --[Konto_4] AS [Konto_4],
  113. --[Konto_5] AS [Konto_5],
  114. [Ebene1] AS [Ebene1],
  115. [Ebene2] AS [Ebene2],
  116. [Ebene3] AS [Ebene3],
  117. [Ebene4] AS [Ebene4],
  118. [Ebene5] AS [Ebene5],
  119. --[Ebene6] AS [Ebene6],
  120. --[Ebene7] AS [Ebene7],
  121. --[Ebene8] AS [Ebene8],
  122. --[Ebene9] AS [Ebene9],
  123. --[Ebene10] AS [Ebene10],
  124. --[Ebene11] AS [Ebene11],
  125. [Ebene12] AS [Ebene12],
  126. [Ebene13] AS [Ebene13],
  127. [Ebene14] AS [Ebene14],
  128. [Ebene15] AS [Ebene15],
  129. --[Ebene16] AS [Ebene16],
  130. --[Ebene17] AS [Ebene17],
  131. --[Ebene18] AS [Ebene18],
  132. --[Ebene19] AS [Ebene19],
  133. --[Ebene20] AS [Ebene20],
  134. [Ebene21] AS [Ebene21],
  135. [Ebene22] AS [Ebene22],
  136. [Ebene23] AS [Ebene23],
  137. [Ebene24] AS [Ebene24],
  138. [Ebene25] AS [Ebene25],
  139. [Ebene26] AS [Ebene26],
  140. --[Ebene27] AS [Ebene27],
  141. --[Ebene28] AS [Ebene28],
  142. --[Ebene29] AS [Ebene29],
  143. --[Ebene30] AS [Ebene30],
  144. [Ebene31] AS [Ebene31],
  145. [Ebene32] AS [Ebene32],
  146. [Ebene33] AS [Ebene33],
  147. [Ebene34] AS [Ebene34],
  148. [Ebene35] AS [Ebene35],
  149. [Ebene36] AS [Ebene36],
  150. --[Ebene37] AS [Ebene37],
  151. --[Ebene38] AS [Ebene38],
  152. --[Ebene39] AS [Ebene39],
  153. --[Ebene40] AS [Ebene40],
  154. --[Ebene41] AS [Ebene41],
  155. --[Ebene42] AS [Ebene42],
  156. --[Ebene43] AS [Ebene43],
  157. --[Ebene44] AS [Ebene44],
  158. --[Ebene45] AS [Ebene45],
  159. --[Ebene46] AS [Ebene46],
  160. --[Ebene47] AS [Ebene47],
  161. --[Ebene48] AS [Ebene48],
  162. --[Ebene49] AS [Ebene49],
  163. --[Ebene50] AS [Ebene50],
  164. --[Ebene51] AS [Ebene51],
  165. --[Ebene52] AS [Ebene52],
  166. --[Ebene53] AS [Ebene53],
  167. --[Ebene54] AS [Ebene54],
  168. --[Ebene55] AS [Ebene55],
  169. --[Ebene56] AS [Ebene56],
  170. --[Ebene57] AS [Ebene57],
  171. --[Ebene58] AS [Ebene58],
  172. --[Ebene59] AS [Ebene59],
  173. --[Ebene60] AS [Ebene60],
  174. [GuV_Bilanz] AS [GuV_Bilanz],
  175. [Susa] AS [Susa],
  176. [Konto_Nr] AS [Acct Nr],
  177. [Konto_Nr] + ' - ' + [Konto_Bezeichnung] AS [Konto]
  178. FROM [export_csv].[Kontenrahmen_STK]
  179. GO
  180. CREATE
  181. OR
  182. ALTER VIEW [load].[Belege]
  183. AS
  184. SELECT --[No] AS [No],
  185. --[Name] AS [Name],
  186. --[Account Type] AS [Account Type],
  187. --[Income Balance] AS [Income Balance],
  188. --[Last Date Modified] AS [Last Date Modified],
  189. --[Entry No] AS [Entry No],
  190. --[G L Account No] AS [G L Account No],
  191. --[Posting Date] AS [Posting Date],
  192. --[Document Type] AS [Document Type],
  193. --[Document No] AS [Document No],
  194. --[Description] AS [Description],
  195. --[Bal Account No] AS [Bal Account No],
  196. --[Department Code] AS [Department Code],
  197. --[Make Code] AS [Make Code],
  198. --[User Id] AS [User Id],
  199. --[Source Code] AS [Source Code],
  200. --[Quantity] AS [Quantity],
  201. --[Reason Code] AS [Reason Code],
  202. --[Gen Posting Type] AS [Gen Posting Type],
  203. --[Gen Bus Posting Group] AS [Gen Bus Posting Group],
  204. --[Gen Prod Posting Group] AS [Gen Prod Posting Group],
  205. --[Bal Account Type] AS [Bal Account Type],
  206. --[Transaction No] AS [Transaction No],
  207. --[Document Date] AS [Document Date],
  208. --[External Document No] AS [External Document No],
  209. --[Source Type] AS [Source Type],
  210. --[Source No] AS [Source No],
  211. --[No Series] AS [No Series],
  212. --[Branch Code] AS [Branch Code],
  213. --[Main Area] AS [Main Area],
  214. --[Vin] AS [Vin],
  215. --[Book No] AS [Book No],
  216. --[Veh Source Code] AS [Veh Source Code],
  217. --[Reposted] AS [Reposted],
  218. --[Corrected] AS [Corrected],
  219. --[Correction To Curr No] AS [Correction To Curr No],
  220. --[Reposting To Curr No] AS [Reposting To Curr No],
  221. --[Code] AS [Code],
  222. [Bookkeep Date] AS [Bookkeep Date],
  223. [Betrieb] AS [Betrieb Nr],
  224. [Acct Nr] AS [Acct Nr],
  225. [Betrag] AS [Betrag],
  226. --[Amount_1] AS [Amount_1],
  227. [Text] AS [Text],
  228. [Hauptbetrieb] AS [Hauptbetrieb],
  229. [Marke] AS [Marke]
  230. FROM [export_csv].[Belege]
  231. GO
  232. CREATE
  233. OR
  234. ALTER VIEW [load].[NW_GW_GK_Stk_fuer_FIBU_Betrag]
  235. AS
  236. SELECT --[Entry No] AS [Entry No],
  237. --[Entry Type] AS [Entry Type],
  238. --[Document No] AS [Document No],
  239. --[Posting Date] AS [Posting Date],
  240. --[Book No] AS [Book No],
  241. --[Vin] AS [Vin],
  242. --[Description] AS [Description],
  243. --[Quantity] AS [Quantity],
  244. --[Department Code_veh_ledger_entry] AS [Department Code_veh_ledger_entry],
  245. --[Make Code] AS [Make Code],
  246. --[Document Date] AS [Document Date],
  247. --[Type] AS [Type],
  248. --[No] AS [No],
  249. --[License No] AS [License No],
  250. --[Location Code] AS [Location Code],
  251. [Date Of Sale] AS [Bookkeep Date],
  252. --[Open] AS [Open],
  253. --[Current Sales Ledger Entry] AS [Current Sales Ledger Entry],
  254. --[Credit Memo] AS [Credit Memo],
  255. --[Sold] AS [Sold],
  256. --[Gen Prod Posting Group] AS [Gen Prod Posting Group],
  257. --[Vat Prod Posting Group] AS [Vat Prod Posting Group],
  258. --[Bal Account No] AS [Bal Account No],
  259. --[Gen Bus Posting Group] AS [Gen Bus Posting Group],
  260. --[Vat Bus Posting Group] AS [Vat Bus Posting Group],
  261. --[Salespers Purch Code] AS [Salespers Purch Code],
  262. --[Model No] AS [Model No],
  263. --[Vehicle] AS [Vehicle],
  264. --[Vehicle Status] AS [Vehicle Status],
  265. --[Branch Book No] AS [Branch Book No],
  266. --[Source Type] AS [Source Type],
  267. --[Source No] AS [Source No],
  268. --[Source No (payment)] AS [Source No (payment)],
  269. --[Source Group Code] AS [Source Group Code],
  270. --[Main Customer] AS [Main Customer],
  271. --[Main Area] AS [Main Area],
  272. --[Document Type] AS [Document Type],
  273. --[Value Ledger Entry No] AS [Value Ledger Entry No],
  274. --[Veh Status Change] AS [Veh Status Change],
  275. --[Item Ledger Entry Type] AS [Item Ledger Entry Type],
  276. --[Inventory Posting Group] AS [Inventory Posting Group],
  277. --[Source Posting Group] AS [Source Posting Group],
  278. --[Valued Quantity] AS [Valued Quantity],
  279. --[Invoiced Quantity] AS [Invoiced Quantity],
  280. --[Cost Posted To G L] AS [Cost Posted To G L],
  281. --[Amount_Value_ledger_entry] AS [Amount_Value_ledger_entry],
  282. --[Amount] AS [Amount],
  283. --[Variance Type] AS [Variance Type],
  284. --[Item Type_value_ledger_entry] AS [Item Type_value_ledger_entry],
  285. --[Menge_ori] AS [Menge_ori],
  286. --[Summe_Menge] AS [Summe_Menge],
  287. --[Menge] AS [Menge],
  288. --[Summe (Menge) Nr.4] AS [Summe (Menge) Nr.4],
  289. --[Summe (Menge) Nr.3] AS [Summe (Menge) Nr.3],
  290. --[G L Account No] AS [G L Account No],
  291. --[Source Code] AS [Source Code],
  292. --[Veh Source Code] AS [Veh Source Code],
  293. --[Document No_GL_Entry] AS [Document No_GL_Entry],
  294. --[Department Code] AS [Department Code],
  295. --[Vat Prod Posting Group_GL_Account] AS [Vat Prod Posting Group_GL_Account],
  296. --[Branch Code] AS [Branch Code],
  297. --[Geschäftsart] AS [Geschäftsart],
  298. --[Filter_Document_No] AS [Filter_Document_No],
  299. --[Filter_Vehicle_Source_Code] AS [Filter_Vehicle_Source_Code],
  300. --[Jahr] AS [Jahr],
  301. [Betrieb Nr] AS [Betrieb Nr],
  302. --[Konto Nr] AS [Konto Nr],
  303. --[Description_GL_Entry] AS [Description_GL_Entry],
  304. [Text] AS [Text],
  305. [Betrag] AS [Betrag],
  306. [Hauptbetrieb] AS [Hauptbetrieb],
  307. [Marke] AS [Marke],
  308. [Acct Nr] AS [Acct Nr]
  309. FROM [export_csv].[NW_GW_GK_Stk_fuer_FIBU_Betrag]
  310. GO
  311. CREATE
  312. OR
  313. ALTER VIEW [load].[NW_GW_GK_Stk_fuer_FIBU_Menge]
  314. AS
  315. SELECT --[Entry No] AS [Entry No],
  316. --[Entry Type] AS [Entry Type],
  317. --[Document No] AS [Document No],
  318. --[Posting Date] AS [Posting Date],
  319. --[Book No] AS [Book No],
  320. --[Vin] AS [Vin],
  321. --[Description] AS [Description],
  322. --[Quantity] AS [Quantity],
  323. --[Department Code_veh_ledger_entry] AS [Department Code_veh_ledger_entry],
  324. --[Make Code] AS [Make Code],
  325. --[Document Date] AS [Document Date],
  326. --[Type] AS [Type],
  327. --[No] AS [No],
  328. --[License No] AS [License No],
  329. --[Location Code] AS [Location Code],
  330. [Date Of Sale] AS [Bookkeep Date],
  331. --[Open] AS [Open],
  332. --[Current Sales Ledger Entry] AS [Current Sales Ledger Entry],
  333. --[Credit Memo] AS [Credit Memo],
  334. --[Sold] AS [Sold],
  335. --[Gen Prod Posting Group] AS [Gen Prod Posting Group],
  336. --[Vat Prod Posting Group] AS [Vat Prod Posting Group],
  337. --[Bal Account No] AS [Bal Account No],
  338. --[Gen Bus Posting Group] AS [Gen Bus Posting Group],
  339. --[Vat Bus Posting Group] AS [Vat Bus Posting Group],
  340. --[Salespers Purch Code] AS [Salespers Purch Code],
  341. --[Model No] AS [Model No],
  342. --[Vehicle] AS [Vehicle],
  343. --[Vehicle Status] AS [Vehicle Status],
  344. --[Branch Book No] AS [Branch Book No],
  345. --[Source Type] AS [Source Type],
  346. --[Source No] AS [Source No],
  347. --[Source No (payment)] AS [Source No (payment)],
  348. --[Source Group Code] AS [Source Group Code],
  349. --[Main Customer] AS [Main Customer],
  350. --[Main Area] AS [Main Area],
  351. --[Document Type] AS [Document Type],
  352. --[Value Ledger Entry No] AS [Value Ledger Entry No],
  353. --[Veh Status Change] AS [Veh Status Change],
  354. --[Item Ledger Entry Type] AS [Item Ledger Entry Type],
  355. --[Inventory Posting Group] AS [Inventory Posting Group],
  356. --[Source Posting Group] AS [Source Posting Group],
  357. --[Valued Quantity] AS [Valued Quantity],
  358. --[Invoiced Quantity] AS [Invoiced Quantity],
  359. --[Cost Posted To G L] AS [Cost Posted To G L],
  360. --[Amount_Value_ledger_entry] AS [Amount_Value_ledger_entry],
  361. --[Amount] AS [Amount],
  362. --[Variance Type] AS [Variance Type],
  363. --[Item Type_value_ledger_entry] AS [Item Type_value_ledger_entry],
  364. --[Menge_ori] AS [Menge_ori],
  365. --[Summe_Menge] AS [Summe_Menge],
  366. --[Menge_alt] AS [Menge_alt],
  367. --[Summe (Menge) Nr.4] AS [Summe (Menge) Nr.4],
  368. --[Summe (Menge) Nr.3] AS [Summe (Menge) Nr.3],
  369. --[G L Account No] AS [G L Account No],
  370. --[Source Code] AS [Source Code],
  371. --[Veh Source Code] AS [Veh Source Code],
  372. --[Document No_GL_Entry] AS [Document No_GL_Entry],
  373. --[Department Code] AS [Department Code],
  374. --[Vat Prod Posting Group_GL_Account] AS [Vat Prod Posting Group_GL_Account],
  375. --[Branch Code] AS [Branch Code],
  376. --[Geschäftsart] AS [Geschäftsart],
  377. --[Filter_Document_No] AS [Filter_Document_No],
  378. --[Filter_Vehicle_Source_Code] AS [Filter_Vehicle_Source_Code],
  379. --[Jahr] AS [Jahr],
  380. [Betrieb Nr] AS [Betrieb Nr],
  381. --[Konto Nr] AS [Konto Nr],
  382. --[Description_GL_Entry] AS [Description_GL_Entry],
  383. [Text] AS [Text],
  384. [Menge_FIBU] AS [Menge_FIBU],
  385. [Hauptbetrieb] AS [Hauptbetrieb],
  386. [Marke] AS [Marke],
  387. [Acct Nr] AS [Acct Nr]
  388. FROM [export_csv].[NW_GW_GK_Stk_fuer_FIBU_Menge]
  389. GO
  390. CREATE
  391. OR
  392. ALTER VIEW [load].[Service_Plan_ohne_FL]
  393. AS
  394. SELECT [Hauptbetrieb] AS [Hauptbetrieb],
  395. [Betrieb Nr] AS [Betrieb Nr],
  396. [Kostenstelle] AS [Kostenstelle],
  397. [Ebene1] AS [Ebene1],
  398. [Ebene2] AS [Ebene2],
  399. [Ebene3] AS [Ebene3],
  400. --[Gesamt] AS [Gesamt],
  401. --[Periode13] AS [Periode13],
  402. --[Jan] AS [Jan],
  403. --[Feb] AS [Feb],
  404. --[Mar] AS [Mar],
  405. --[Apr] AS [Apr],
  406. --[Mai] AS [Mai],
  407. --[Jun] AS [Jun],
  408. --[Jul] AS [Jul],
  409. --[Aug] AS [Aug],
  410. --[Sep] AS [Sep],
  411. --[Okt] AS [Okt],
  412. --[Nov] AS [Nov],
  413. --[Dez] AS [Dez],
  414. [Version] AS [Version],
  415. --[Jahr] AS [Jahr],
  416. --[Bundeslaender Id] AS [Bundeslaender Id],
  417. [Datum] AS [Bookkeep Date],
  418. --[Wochentage Id] AS [Wochentage Id],
  419. --[Arbeitstag Mofr] AS [Arbeitstag Mofr_],
  420. --[Zaehler Mofr] AS [Zaehler Mofr_],
  421. --[Summe Mofr] AS [Summe Mofr_],
  422. --[Arbeitstag Mosa] AS [Arbeitstag Mosa],
  423. --[Zaehler Mosa] AS [Zaehler Mosa],
  424. --[Summe Mosa] AS [Summe Mosa],
  425. --[Feiertage Id] AS [Feiertage Id],
  426. --[Jahr_] AS [Jahr_],
  427. --[Arbeitstag Nr Jahr] AS [Arbeitstag Nr Jahr],
  428. --[Gesamt Arbeitstage] AS [Gesamt Arbeitstage],
  429. --[Monat] AS [Monat],
  430. --[Plan] AS [Plan_ori],
  431. [Plan_ori] * - 1 AS [Plan]
  432. FROM [export_csv].[Service_Plan_ohne_FL]
  433. GO