SHARE
TWEET

Top 4 Query (1C)

bytecoded Nov 9th, 2019 116 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.     CASE
  3.         WHEN ShipmentOrdersStatuses.Status = &PartShipment
  4.             THEN 1
  5.         WHEN ShipmentOrdersStatuses.Status = &FullShipment
  6.             THEN 2
  7.         ELSE 0
  8.     END AS ShipmentPictureNumber,          
  9.     CASE
  10.         WHEN ISNULL(AccountsPayablePaymentTermsBalance.AmountCurBalance, 0) > 0
  11.             THEN CASE
  12.                     WHEN ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfPaymentCurTurnover, 0) + ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfAdvanceCurTurnover, 0) = 0
  13.                         THEN 4
  14.                     WHEN ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountCurTurnover, 0) <= ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfPaymentCurTurnover, 0) + ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfAdvanceCurTurnover, 0)
  15.                         THEN 3
  16.                     WHEN ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfPaymentCurTurnover, 0) + ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfAdvanceCurTurnover, 0) < ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountCurTurnover, 0) / 2
  17.                         THEN 5
  18.                     ELSE 6
  19.                 END
  20.         ELSE CASE
  21.                 WHEN ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfPaymentCurTurnover, 0) + ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfAdvanceCurTurnover, 0) = 0
  22.                     THEN 0
  23.                 WHEN ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountCurTurnover, 0) <= ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfPaymentCurTurnover, 0) + ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfAdvanceCurTurnover, 0)
  24.                     THEN 3
  25.                 WHEN ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfPaymentCurTurnover, 0) + ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountOfAdvanceCurTurnover, 0) < ISNULL(InvoicesAndOrdersPaymentTurnovers.AmountCurTurnover, 0) / 2
  26.                     THEN 1
  27.                 ELSE 2
  28.             END
  29.     END AS PaymentPictureNumber,   
  30.     DocumentPurchaseOrder.Ref,
  31.     DocumentPurchaseOrder.DeletionMark,
  32.     DocumentPurchaseOrder.Number,
  33.     DocumentPurchaseOrder.Date,
  34.     DocumentPurchaseOrder.Posted,
  35.     DocumentPurchaseOrder.Entity AS Entity,
  36.     DocumentPurchaseOrder.TransactionType,
  37.     DocumentPurchaseOrder.Company,
  38.     DocumentPurchaseOrder.Company.ContactPerson AS ContactPerson,
  39.     DocumentPurchaseOrder.Contract,
  40.     DocumentPurchaseOrder.ReceiptDate AS ReceiptDate,
  41.     DocumentPurchaseOrder.DocumentCurrency,
  42.     DocumentPurchaseOrder.AmountVATIn,
  43.     DocumentPurchaseOrder.IncludeVATInCost,
  44.     DocumentPurchaseOrder.ExchangeRate,
  45.     DocumentPurchaseOrder.Repetition,
  46.     DocumentPurchaseOrder.CustomerOrder,
  47.     DocumentPurchaseOrder.DocumentAmount,
  48.     DocumentPurchaseOrder.CompanyPriceKind,
  49.     DocumentPurchaseOrder.Event,
  50.     DocumentPurchaseOrder.Responsible,
  51.     DocumentPurchaseOrder.BaseUnit,
  52.     DocumentPurchaseOrder.Comment,
  53.     DocumentPurchaseOrder.AddToPaymentCalendar,
  54.     DocumentPurchaseOrder.PettyCash,
  55.     DocumentPurchaseOrder.BankAccount,
  56.     DocumentPurchaseOrder.CashType,
  57.     DocumentPurchaseOrder.StatusOfOrder,
  58.     DocumentPurchaseOrder.StatusOfOrder.OrderStatus AS StatusOfOrderState,
  59.     CASE
  60.         WHEN (DocumentPurchaseOrder.StatusOfOrder.OrderStatus = VALUE(Enum.OrderStatuses.Open)
  61.                 OR DocumentPurchaseOrder.StatusOfOrder.OrderStatus = VALUE(Enum.OrderStatuses.InProcess))
  62.                 AND NOT DocumentPurchaseOrder.IsClosed
  63.             THEN "In Process"
  64.         ELSE CASE
  65.                 WHEN DocumentPurchaseOrder.StatusOfOrder.OrderStatus = VALUE(Enum.OrderStatuses.Completed)
  66.                     THEN "Completed"
  67.                 ELSE "Cancelled"
  68.             END
  69.     END AS OrderStatus,
  70.     DocumentPurchaseOrder.IsClosed,
  71.     DocumentPurchaseOrder.IncomingDocumentNo,
  72.     DocumentPurchaseOrder.IncomingDocumentDate,
  73.     DocumentPurchaseOrder.Author,
  74.     DocumentPurchaseOrder.Inventory.(
  75.         Ref,
  76.         LineNumber,
  77.         Item,
  78.         Characteristic,
  79.         Quantity,
  80.         UnitOfMeasure,
  81.         Price,
  82.         Amount,
  83.         VATRate,
  84.         VATAmount,
  85.         ReceiptDate,
  86.         AmountTotal
  87.     ),
  88.     DocumentPurchaseOrder.Materials.(
  89.         Ref,
  90.         LineNumber,
  91.         Item,
  92.         Characteristic,
  93.         Quantity,
  94.         UnitOfMeasure,
  95.         ShippingDate
  96.     ),
  97.     DocumentPurchaseOrder.PaymentCalendar.(
  98.         Ref,
  99.         LineNumber,
  100.         PayDate,
  101.         PaymentPercentage,
  102.         AmountOfPayment,
  103.         PayVATAmount
  104.     ),
  105.     DocumentPurchaseOrder.PointInTime,
  106.     PurchaseOrdersBalanceAndTurnovers.QuantityClosingBalance AS ToReceive,
  107.     InvoicesAndOrdersPaymentTurnovers.AmountCurTurnover - InvoicesAndOrdersPaymentTurnovers.AmountOfPaymentCurTurnover - InvoicesAndOrdersPaymentTurnovers.AmountOfAdvanceCurTurnover AS ForPayment,
  108.     CASE
  109.         WHEN DocumentPurchaseOrder.Posted
  110.                 AND DocumentPurchaseOrder.DocumentAmount > 0
  111.             THEN CAST((InvoicesAndOrdersPaymentTurnovers.AmountOfPaymentCurTurnover + InvoicesAndOrdersPaymentTurnovers.AmountOfAdvanceCurTurnover) * 100 / DocumentPurchaseOrder.DocumentAmount AS NUMBER(15, 2))
  112.         ELSE 0
  113.     END AS PaymentPercentage,
  114.     CASE
  115.         WHEN VendorAccountsBalances.AmountBalance < 0
  116.                 AND CustomerAccountsBalances.AmountBalance > 0
  117.             THEN -1 * VendorAccountsBalances.AmountBalance + CustomerAccountsBalances.AmountBalance
  118.         WHEN VendorAccountsBalances.AmountBalance < 0
  119.             THEN -VendorAccountsBalances.AmountBalance
  120.         WHEN CustomerAccountsBalances.AmountBalance > 0
  121.             THEN CustomerAccountsBalances.AmountBalance
  122.         ELSE 0
  123.     END AS CompanyDebt,
  124.     CASE
  125.         WHEN VendorAccountsBalances.AmountBalance > 0
  126.                 AND CustomerAccountsBalances.AmountBalance < 0
  127.             THEN -1 * CustomerAccountsBalances.AmountBalance + VendorAccountsBalances.AmountBalance
  128.         WHEN VendorAccountsBalances.AmountBalance > 0
  129.             THEN VendorAccountsBalances.AmountBalance
  130.         WHEN CustomerAccountsBalances.AmountBalance < 0
  131.             THEN -CustomerAccountsBalances.AmountBalance
  132.         ELSE 0
  133.     END AS OurDebt,
  134.     DocumentPurchaseOrder.Company.Presentation AS PresentationOfCompany,
  135.     CompanyEmail.Presentation AS CompanyEmail,
  136.     CompanyPhone.Presentation AS CompanyPhone,
  137.     CompanyFax.Presentation AS CompanyFax,
  138.     CompanyLegalAddress.Presentation AS CompanyLegalAddress,
  139.     CompanyDeliveryAddress.Presentation AS CompanyDeliveryAddress,
  140.     OtherCompanyInfo.Presentation AS OtherCompanyInfo,
  141.     DocumentPurchaseOrder.Company.ContactPerson.Presentation AS PresentationOfContactPerson,
  142.     ContactPersonEmail.Presentation AS ContactPersonEmail,
  143.     ContactPersonMobile.Presentation AS ContactPersonMobile,
  144.     ContactPersonPhone.Presentation AS ContactPersonPhone,
  145.     InvoicesAndOrdersPaymentTurnovers.AmountOfPaymentCurTurnover + InvoicesAndOrdersPaymentTurnovers.AmountOfAdvanceCurTurnover AS AmountOfPaymentTurnover,
  146.     DocumentPurchaseOrder.CustomerOrderPosition,
  147.     DocumentPurchaseOrder.ReceiptDatePosition
  148. FROM
  149.     Constant.NationalCurrency AS ConstantNationalCurrency,
  150.     Document.PurchaseOrder AS DocumentPurchaseOrder
  151.         LEFT JOIN AccumulationRegister.PurchaseOrders.BalanceAndTurnovers(, , Auto, , ) AS PurchaseOrdersBalanceAndTurnovers
  152.         ON (PurchaseOrdersBalanceAndTurnovers.PurchaseOrder = DocumentPurchaseOrder.Ref)
  153.         LEFT JOIN AccumulationRegister.VendorAccounts.Balance(, ) AS VendorAccountsBalances
  154.         ON DocumentPurchaseOrder.Company = VendorAccountsBalances.Company
  155.         LEFT JOIN AccumulationRegister.CustomerAccounts.Balance(, ) AS CustomerAccountsBalances
  156.         ON DocumentPurchaseOrder.Company = CustomerAccountsBalances.Company
  157.         LEFT JOIN AccumulationRegister.InvoicesAndOrdersPayment.Turnovers AS InvoicesAndOrdersPaymentTurnovers
  158.         ON DocumentPurchaseOrder.Ref = InvoicesAndOrdersPaymentTurnovers.InvoiceForPayment
  159.         LEFT JOIN AccumulationRegister.Purchasing.Turnovers AS PurchasingTurnovers
  160.         ON DocumentPurchaseOrder.Ref = PurchasingTurnovers.PurchaseOrder
  161.         LEFT JOIN InformationRegister.CurrencyRates.SliceLast(
  162.                 ,
  163.                 Currency IN
  164.                     (SELECT
  165.                         ConstantDefaultCurrency.Value
  166.                     FROM
  167.                         Constant.DefaultCurrency AS ConstantDefaultCurrency)) AS ManagCurrencyRates
  168.         ON (TRUE)
  169.         LEFT JOIN InformationRegister.CurrencyRates.SliceLast(
  170.                 ,
  171.                 Currency IN
  172.                     (SELECT
  173.                         ConstantNationalCurrency.Value
  174.                     FROM
  175.                         Constant.NationalCurrency AS ConstantNationalCurrency)) AS RegCurrencyRates
  176.         ON (TRUE)
  177.         LEFT JOIN Catalog.Companies.ContactInformation AS CompanyEmail
  178.         ON DocumentPurchaseOrder.Company = CompanyEmail.Ref
  179.             AND (CompanyEmail.Kind = VALUE(Catalog.ContactInformationKinds.CompanyEmail))
  180.         LEFT JOIN Catalog.Companies.ContactInformation AS CompanyPhone
  181.         ON DocumentPurchaseOrder.Company = CompanyPhone.Ref
  182.             AND (CompanyPhone.Kind = VALUE(Catalog.ContactInformationKinds.CompanyPhone))
  183.         LEFT JOIN Catalog.Companies.ContactInformation AS CompanyFax
  184.         ON DocumentPurchaseOrder.Company = CompanyFax.Ref
  185.             AND (CompanyFax.Kind = VALUE(Catalog.ContactInformationKinds.CompanyFax))
  186.         LEFT JOIN Catalog.Addresses AS CompanyLegalAddress
  187.         ON DocumentPurchaseOrder.Company.LegalAddress = CompanyLegalAddress.Ref
  188.         LEFT JOIN Catalog.Addresses AS CompanyDeliveryAddress
  189.         ON DocumentPurchaseOrder.Company.DeliveryAddress = CompanyDeliveryAddress.Ref
  190.         LEFT JOIN Catalog.Companies.ContactInformation AS OtherCompanyInfo
  191.         ON DocumentPurchaseOrder.Company = OtherCompanyInfo.Ref
  192.             AND (OtherCompanyInfo.Kind = VALUE(Catalog.ContactInformationKinds.CompanyOtherInformation))
  193.         LEFT JOIN Catalog.ContactPersons.ContactInformation AS ContactPersonEmail
  194.         ON DocumentPurchaseOrder.Company.ContactPerson = ContactPersonEmail.Ref
  195.             AND (ContactPersonEmail.Kind = VALUE(Catalog.ContactInformationKinds.ContactPersonEmail))
  196.         LEFT JOIN Catalog.ContactPersons.ContactInformation AS ContactPersonMobile
  197.         ON DocumentPurchaseOrder.Company.ContactPerson = ContactPersonMobile.Ref
  198.             AND (ContactPersonMobile.Kind = VALUE(Catalog.ContactInformationKinds.ContactPersonMobile))
  199.         LEFT JOIN Catalog.ContactPersons.ContactInformation AS ContactPersonPhone
  200.         ON DocumentPurchaseOrder.Company.ContactPerson = ContactPersonPhone.Ref
  201.             AND (ContactPersonPhone.Kind = VALUE(Catalog.ContactInformationKinds.ContactPersonPhone))
  202.         LEFT JOIN InformationRegister.ShipmentOrdersStatuses AS ShipmentOrdersStatuses
  203.         ON (ShipmentOrdersStatuses.Order = DocumentPurchaseOrder.Ref)      
  204.         LEFT JOIN AccumulationRegister.AccountsPayablePaymentTerms.Balance(&CurrentDate, ) AS AccountsPayablePaymentTermsBalance
  205.         ON DocumentPurchaseOrder.Ref = AccountsPayablePaymentTermsBalance.PurchaseOrder
RAW Paste Data
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
 
Top