API tools faq
paste
Advertisement
bytecoded

Query of SalesExecutivesInfoFromInvoices

bytecoded
Mar 30th, 2019
427
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.76 KB | None | 0 0
raw download clone embed print report
  1. SELECT ALLOWED
  2. CASE
  3. WHEN SalesTurnovers.Recorder = UNDEFINED
  4. THEN NULL
  5. ELSE SalesTurnovers.Recorder
  6. END AS Recorder,
  7. SalesTurnovers.Item AS Item,
  8. SalesTurnovers.Item AS ItemWithSKU,
  9. SalesTurnovers.Item.Description AS ItemPresentation,
  10. CASE
  11. WHEN SalesTurnovers.Characteristic = VALUE(Catalog.ItemCharacteristics.EmptyRef)
  12. THEN CASE
  13. WHEN SalesTurnovers.Batch <> VALUE(Catalog.ItemBatches.EmptyRef)
  14. THEN "N/A"
  15. ELSE NULL
  16. END
  17. ELSE SalesTurnovers.Characteristic
  18. END AS Characteristic,
  19. SalesTurnovers.Characteristic AS CharacteristicGrossProfit,
  20. CASE
  21. WHEN SalesTurnovers.Batch = VALUE(Catalog.ItemBatches.EmptyRef)
  22. THEN NULL
  23. ELSE SalesTurnovers.Batch
  24. END AS Batch,
  25. SalesTurnovers.Batch AS BatchGrossProfit,
  26. SalesTurnovers.Document AS Document,
  27. SalesTurnovers.VATRate AS VATRate,
  28. SalesTurnovers.Entity AS Entity,
  29. SalesTurnovers.CustomerOrder AS CustomerOrder,
  30. SalesTurnovers.QuantityTurnover AS Quantity,
  31. SalesTurnovers.AmountTurnover AS Amount,
  32. SalesTurnovers.AmountTurnover - SalesTurnovers.VATAmountTurnover AS AmountWithoutVAT,
  33. SalesTurnovers.VATAmountTurnover AS VATAmount,
  34. SalesTurnovers.CostPriceTurnover AS CostPrice,
  35. SalesTurnovers.Item.UnitOfMeasure AS ItemUnitOfMeasure,
  36. SalesTurnovers.Item.SKU AS ItemSKU,
  37. Companies.Ref AS Company,
  38. SalesTurnovers.Project AS Project,
  39. SalesTurnovers.Document.Date AS DocumentDate,
  40. SalesTurnovers.Document.Number AS DocumentNumber,
  41. SalesTurnovers.CustomerOrder.Date AS CustomerOrderDate,
  42. SalesTurnovers.CustomerOrder.Number AS CustomerOrderNumber,
  43. SalesTurnovers.WeekPeriod AS WeekPeriod,
  44. SalesTurnovers.MonthPeriod AS MonthPeriod,
  45. SalesTurnovers.QuarterPeriod AS QuarterPeriod,
  46. SalesTurnovers.HalfYearPeriod AS HalfYearPeriod,
  47. SalesTurnovers.YearPeriod AS YearPeriod,
  48. CASE
  49. WHEN SalesTurnovers.Item.Commissionable
  50. THEN SalesTurnovers.AmountTurnover - SalesTurnovers.VATAmountTurnover
  51. ELSE 0
  52. END AS AmountWithoutVATCI,
  53. CASE
  54. WHEN NOT SalesTurnovers.Item.Commissionable
  55. THEN SalesTurnovers.AmountTurnover - SalesTurnovers.VATAmountTurnover
  56. ELSE 0
  57. END AS AmountWithoutVATNCI,
  58. 0 AS Payment,
  59. SalesTurnovers.Document.Department AS Department,
  60. NULL AS AmountCancelled
  61. INTO Sales
  62. FROM
  63. AccumulationRegister.Sales.Turnovers(, , Auto, ) AS SalesTurnovers
  64. LEFT JOIN Catalog.Companies AS Companies
  65. ON SalesTurnovers.Document.Company = Companies.Ref
  66.  
  67. UNION ALL
  68.  
  69. SELECT
  70. AccountsReceivableDeferredTurnovers.Recorder,
  71. NULL,
  72. NULL,
  73. NULL,
  74. NULL,
  75. NULL,
  76. NULL,
  77. NULL,
  78. AccountsReceivableDeferredTurnovers.Document,
  79. NULL,
  80. AccountsReceivableDeferredTurnovers.Entity,
  81. NULL,
  82. NULL,
  83. NULL,
  84. NULL,
  85. NULL,
  86. NULL,
  87. NULL,
  88. NULL,
  89. AccountsReceivableDeferredTurnovers.Company,
  90. NULL,
  91. NULL,
  92. NULL,
  93. NULL,
  94. NULL,
  95. NULL,
  96. NULL,
  97. NULL,
  98. NULL,
  99. NULL,
  100. NULL,
  101. NULL,
  102. AccountsReceivableDeferredTurnovers.AmountExpense,
  103. AccountsReceivableDeferredTurnovers.Document.Department,
  104. NULL
  105. FROM
  106. AccumulationRegister.AccountsReceivableDeferred.Turnovers(, , Auto, &IncludingCheques) AS AccountsReceivableDeferredTurnovers
  107. WHERE
  108. AccountsReceivableDeferredTurnovers.Recorder REFS Document.ChequeReceipt
  109.  
  110. UNION ALL
  111.  
  112. SELECT
  113. CustomerAccountsTurnovers.Recorder,
  114. NULL,
  115. NULL,
  116. NULL,
  117. NULL,
  118. NULL,
  119. NULL,
  120. NULL,
  121. CustomerAccountsTurnovers.Document,
  122. NULL,
  123. CustomerAccountsTurnovers.Entity,
  124. NULL,
  125. NULL,
  126. NULL,
  127. NULL,
  128. NULL,
  129. NULL,
  130. NULL,
  131. NULL,
  132. CustomerAccountsTurnovers.Company,
  133. NULL,
  134. NULL,
  135. NULL,
  136. NULL,
  137. NULL,
  138. NULL,
  139. NULL,
  140. NULL,
  141. NULL,
  142. NULL,
  143. NULL,
  144. NULL,
  145. CustomerAccountsTurnovers.AmountExpense,
  146. CustomerAccountsTurnovers.Document.Department,
  147. NULL
  148. FROM
  149. AccumulationRegister.CustomerAccounts.Turnovers(, , Auto, ) AS CustomerAccountsTurnovers
  150. WHERE
  151. CASE
  152. WHEN &IncludingCheques
  153. THEN NOT CustomerAccountsTurnovers.Recorder.DocumentBasis REFS Document.ChequeReceipt
  154. ELSE TRUE
  155. END
  156. AND (CustomerAccountsTurnovers.Recorder REFS Document.PettyCashReceipt
  157. OR CustomerAccountsTurnovers.Recorder REFS Document.PaymentReceipt)
  158.  
  159. UNION ALL
  160.  
  161. SELECT
  162. AccountsReceivableDeferredTurnovers.Recorder,
  163. NULL,
  164. NULL,
  165. NULL,
  166. NULL,
  167. NULL,
  168. NULL,
  169. NULL,
  170. AccountsReceivableDeferredTurnovers.Document,
  171. NULL,
  172. AccountsReceivableDeferredTurnovers.Entity,
  173. NULL,
  174. NULL,
  175. NULL,
  176. NULL,
  177. NULL,
  178. NULL,
  179. NULL,
  180. NULL,
  181. AccountsReceivableDeferredTurnovers.Company,
  182. NULL,
  183. NULL,
  184. NULL,
  185. NULL,
  186. NULL,
  187. NULL,
  188. NULL,
  189. NULL,
  190. NULL,
  191. NULL,
  192. NULL,
  193. NULL,
  194. NULL,
  195. AccountsReceivableDeferredTurnovers.Document.Department,
  196. AccountsReceivableDeferredTurnovers.AmountReceipt
  197. FROM
  198. AccumulationRegister.AccountsReceivableDeferred.Turnovers(, , Auto, &IncludingCheques) AS AccountsReceivableDeferredTurnovers
  199. WHERE
  200. AccountsReceivableDeferredTurnovers.Recorder REFS Document.ChequeCancellation
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!