Finance_Payments_OrderBalance VIEW (Domain Api 2019.1)

This view contains the payment orders and their paid amounts collected from released (non voided) payment transactions. Each item represents a payment order with an additional property - PaidAmount.

Only payment orders that are not fully paid are returned i.e. there will be no items with OrderAmount equal to PaidAmount. The useful information from this view is the remaining amount to pay which is OrderAmount minus PaidAmount.

mceclip0.png

The view can be filtered to query the payment status of a document.

The query bellow will return all payment orders for the specified document (in this case a sales order) that are not fully paid:

https://example.com/api/domain/odata/Finance_Payments_OrderBalances?$filter=RefDocument eq 'Crm_Sales_SalesOrders(b488d924-6332-e311-81cb-00155d001f00)'

Note that there may be more than one item in the returned result  because the not fully paid payment orders for the sales order may be more than one. To calculate the remaining amount to pay we need to calculate SUM(item.OrderAmount - item.PaidAmount).

Have more questions? Submit a request

13 Comments

  • 0
    Avatar
    Georgi Kaloferov

    Браво!

  • 0
    Avatar
    Михаил Денев

    Здравейте,

    Имам въпрос относно примера

    The query bellow will return all payment orders for the specified document - in this case a sales order:

    https://example.com/api/domain/odata/Finance_Payments_OrderBalances?$filter=RefDocument eq 'Crm_Sales_SalesOrders(b488d924-6332-e311-81cb-00155d001f00)'

    Note that there may be more than one item in the returned result  because the payment orders for the sales order may be more than one. The sum of PaidAmount of each payment order is the total paid amount for the sales order.

     

    На база на писаното по-горе - а именно че не се връщат приключени платежни искания предполагам че или твърдението "The sum of PaidAmount of each payment order is the total paid amount for the sales order" не е вярно, защото може да има и приключени или при такъв тип заявки се връщат се връщат и приключените платежни искания.

     

    Поздрави

  • 0
    Avatar
    Nikolay Nikolov [ERP.BG]

    Явно наистина "The sum of PaidAmount of each payment order is the total paid amount for the sales order" не е вярно. Ще го коригирам.

  • 0
    Avatar
    Dobri Kostadinov

    Здравейте и от мен. Възможно ли е във View-то да се вдигнат контакт механизмите на контрагента?

  • 0
    Avatar
    Ivan Argentinski [ERP.BG]

    Please use English when commenting in EN blogs.

    The view already contains more attributes than we initially wanted.

    The general idea for VIEWs is that they contain minimum data attributes, so that they can be useful (with good performance) for wider range of scenarios. The necessary additional data can be obtained by $expand or additional queries. VIEWs typically process on server much larger datasets than what is finally returned, so expanding or issuing additional queries are a small cost to pay.

  • 0
    Avatar
    Dobri Kostadinov

    That all I want. To be able to $expand the contact mechanisms from the "Party" json object. Currently there are no contact mechanisms at all:

    I am pasting here the response of the party object. As you can see there is no contact mechanisms. Why is that?

    Party": {
    "@odata.id": "General_Contacts_Parties(d87a44ed-abf3-46d5-8527-9003b3d3cca5)",
    "PartyId": "d87a44ed-abf3-46d5-8527-9003b3d3cca5",
    "IsActive": true,
    "AdministrativeRegionId": null,
    "AreaId": null,
    "PartyCreationTime": "2009-05-16T17:24:56.51Z",
    "PartyCreationUser": "admin",
    "DefaultProductCodingSystemId": null,
    "GLN": null,
    "PartyNotes": null,
    "ParentPartyId": null,
    "PartyCode": "00003",
    "PartyType": "Company",
    "PartyUniqueNumber": "55555",
    "PartyUpdateTime": null,
    "PartyUpdateUser": null,
    "PartyName": {
    "BG": "Клиент 1 ООД"
    },
    "AdministrativeRegion": null,
    "Area": null,
    "DefaultProductCodingSystem": null,
    "ParentParty": null
    }

  • 0
    Avatar
    Nikolay Nikolov [ERP.BG]

    You must expand the property ContactMechanisms as well:

    Finance_Payments_OrderBalances?$top=10&$select=Party&$expand=Party($expand=ContactMechanisms($expand=ContactMechanism))

  • 0
    Avatar
    Ivan Argentinski [ERP.BG]

    Also, you can make calculated attributes for Party like:

    • Phone1
    • Phone2
    • Email1
    • etc.
  • 0
    Avatar
    Dobri Kostadinov

    Hello,

    2 questions:

     

    Q1 EN: Is the View returning all postponed PaymentOrders or it returns only postponed to current date.

     

    Q1. BG: слагам и въпроса на български: Резултатът в момента само просрочени ли връща или връща всички задължения? Т.е. взима ли под внимание “разликата между текущата дата и Падежната дата е по-голяма от 0”

     

    Q2: How can I filter PaymentOrder/EnterpriseCompany eq something? 

    I could not find filter examples with expand clause on your documentations.

    I try something like:
    $filter=PaymentOrder(EnterpriseCompany eq 'General_EnterpriseCompanies(34c21fb6-c647-4cfa-9d0c-3e02878e3789)') but it does not work

     

  • 0
    Avatar
    Nikolay Nikolov [ERP.BG]

    Hello,

    1. The view does not consider DueDate. It returns all not fully paid payment orders on state 'Released'.

    The SQL View is:

    SELECT ... FROM PaymentOrders

    WHERE State = 30

    AND ABS(Order_Amount - Paid_Amount) >= 0.01

    ----

    Paid_Amount = SUM(PaymentTransaction.Covered_Order_Amount) WHERE PaymentTransaction.State >= 30

    2. I'm afraid that filter by enterprise company is not supported.

    The correct syntax is: $filter=PaymentOrder/EnterpriseCompany eq 'General_EnterpriseCompanies(34c21fb6-c647-4cfa-9d0c-3e02878e3789)'

    BUT this is not supported by this view. You can filter only by properties defined in the view itself. 

  • 0
    Avatar
    Dobri Kostadinov

    Hello,

    the following query should return me the characteristic X but it does not return anything. My colleagues assured me that the characteristic should be present on the Party field. The api did not return it at all. Is that a bug of the domain api or I do something wrong?

     

    https://XXXX/api/domain/odata/Finance_Payments_OrderBalances?$expand=Currency,Party($select=default,CustomProperty_SendMailForOverdueParty;$expand=ContactMechanisms($expand=ContactMechanism)),RefInvoiceDocument($expand=EnterpriseCompany),PaymentOrder

  • 0
    Avatar
    Nikolay Nikolov [ERP.BG]

    If you want newly created custom properties or calculated attributes to show up in API metadata you must refresh the API repository source by calling

    GET https://XXXX/api/domain/reset

  • 0
    Avatar
    Dobri Kostadinov

    Характеристиката все още я няма

Please sign in to leave a comment.
Powered by Zendesk