Typical billing scheme

As a person who was tormented by various ASRs and myself took part in the development of ASRs, I regularly came across the absence of some type of scheme that could be looked at to assess the ASR, as well as before creating my own ASR. The network has a number of works on this topic, for example, I once studied this work when writing a diploma. Methods of modeling and development of billing systems . A diploma is a diploma, and dragging out schemes from it is a strange occupation, since it does not correspond to realities.

As a result, now having quite a lot of experience working with ASR, I decided to make my own scheme. But since I am still one person, it is worth showing it to others and criticizing it. So I hope they are interested in this topic and they will tell me what else needs to be done and how. The scheme that I publish here is already subjected to improvements and adjustments, as well as there is already the opportunity to download it from github . Both as a file for Power Architect and as a finished DDL file for PostgreSQL. The only thing I did not have time to fill out the reference books, but everything has its time. Now let's move on to the scheme.

The first step is to look at the ER-diagram, as the most convenient means of representing the circuit. As you can see, although there are quite a few tables, in fact, the functionality is quite small.





And consists of the following features:

• Keeping a customer agreement and maintaining its balance sheet
• Storage and maintenance of services
• Charging for services provided
• Discounts
• Making payments
• Transfer of funds from contract to contract
• Entering customer balances when transferring them from another system
• Customer billing storage
• Settlement of invoices

This is a necessary minimum for the correct accrual of services and cash accounting.
But before moving on to the description, it is worth mentioning the conventions used in the scheme:

• All foreign keys are in the format <primary key> _ <table name> . If there are several foreign keys or they point to the table itself, either an addition to the name of the form id_ <table name> _ <explaining addition> or id_ <explaining addition> is allowed . As an example, id_trx_from, id_trx_to for the first case and id_revoke , id_revokedby for the second case in the bill.transfer table.
  
• Fields with money are defined as numeric (18.4).
• Fields with a date are prefixed with dt without fail.
• Fields with date and time are prefixed with ts without fail.
• If there is a time interval (dtfrom, dtto or tsfrom, tsto), then the first date is always set and defaults to now (), the second date can be empty and in this case the interval is considered valid at the moment.
• In some cases, directories use a text mnemonic key instead of a numerical primary key. Such keys are designated as sid. It is used solely for the convenience of working with data directly through the RDBMS console.

And now the slides are a description.

The reference tables are:

• Contracts (bill.contract) - the minimum description of the contract necessary for use
• Postings (bill.trx) - Journal of transactions. In fact, the amount of money received or withdrawn from the account.
• The accounting side used (bill.ledgertype) - indicates where the posting is going (debit, credit)
• Reporting periods (bill.period) - reports not periods in the accounting sense of the word. Although it contains a start date and an end date, it is actually always equal to the month
• Invoices issued to the client (bill.invoice) - the same invoices that are issued to the client for the reporting period.

Although these tables are quite enough for billing to work, tables of primary documents are added for convenience. Primary documents mean those documents on the basis of which postings are made on a client’s agreement.

Now in the scheme there are the following primary documents:

• Balances (bill.remain) - incoming balances from another system. These documents should always be, otherwise if you are migrating from another system, you will never know what the client’s balance was at the time of migration. This, by the way, affects many ACPs, as the developers believe that it is enough to introduce a balance. But this is not so, since in a normal system the client’s balance is the calculated value.
• Payments (bill.payment) - funds received from customers.
• Transfers (bill.transfer) - transfer of funds from one account to another. Immediately I note, it is worth explicitly prohibiting the transfer of funds in their absence. Those. if the client’s balance is negative, then the transfer should be prohibited.
• Charges (bill.charge) - charges for the services consumed or provided (in advance)
• Discounts (bill.discount) - Discounts on services. Although in general there is no consensus on how to express a discount. I believe that the discount should be expressed in monetary terms to a certain accrual. This makes working with her easier.
• VAT (bill.vat) - VAT on accrual, is issued as a separate document. All postings from accruals are without VAT, while the accrual itself may include VAT. This, for example, is required for individuals. In this case, bill.charge has an explicit flag that the charge includes VAT. At the same time, the accrual transaction does not include VAT and the full amount of the charge is made up of two postings, the posting on the primary accrual document + the posting under VAT.

Primary documents have both general fields and general rules for working with them. Let's start with the common fields:

• id_contract (id_contract_from, id_contract_to) - indicates a contract or contract document
• id_trx (id_trx_from, id_trx_to) - indicates the posting or postings of the document
• id_period - in which reporting period the document is posted.
• ts - date of document
• tscreate - the date the document was created.
• amount - the amount of the document
• id_revoke - the corrected document. Indicates the document that is being corrected by this.
• id_revokedby - correction document. Indicates the document that corrected the current one.

As for the reporting period and the adjusted and corrective document, I will dwell on more details.

Reporting period.
At the end of the month, you may have two open reporting periods due to the fact that, for example, the last days fall on weekends, and bank payments have not yet been posted. Plus, this is required in cases, for example, when the actual date of the document is January 2014, and the period used is June 2015, held at the beginning of July 2015. That is, in the period June 2015, the creation date is July 2, 2015, and the date of the document is January 2014. Because of this, the document actually has three dates.

Corrective and corrective documents.
In fact, if a document appears in your system that is erroneous, you cannot delete it. It can only be canceled. Those. create a corrective document that is strictly opposite to an incorrect document. It is for this that these two fields are used. id_revoke - is filled in the correction document, and id_revokedby in the corrected document . Correcting in another way, for example, part of a document, is not recommended, as well as deleting documents. Instead, hide such documents if they go in the same period. If the documents are in different periods, then hiding them is just not required. Also note that postings do not have such fields, they are not adjustable.

In addition to common fields, primary documents have their own specific fields:

• Accrual documents (bill.charge) - count and vatincluded . The first indicates the number of services provided, the second does not include VAT in the amount of the document.
• VAT documents (bill.vat) - id_charge and id_vatrate indicating the accrual document, which includes VAT and what percentage of VAT was at the time of the accrual.
• Discount documents (bill.discount) - id_charge indicating which accrual the discount was applied.
• Documents of balances (bill.remain) - sid_ledgertype allows specifying the used side of the account during the document.

The rest of the fields I think are clear from the context and the selected names. This completes the conversation about the primary documents and move on to the remaining lookup tables.

The table contains the following lookup tables:

• Types of payments (bill.paymenttype) - for dividing payments by type. Cash, bank transfer, payment agents, etc.
• Units of measurement (bill.unit) - actually the units of measurement of the services used. For example, you can take OKEY from the directory. The self reference is used for units that include others.
• Services (bill.service) - the name of the services provided under the contract.
• Prices (bill.price) - Cost of services. To account for changes in value added time intervals.
• Posting type (bill.trxtype) - Indicates the type of posting used for primary documents, as well as the default accounting side. If the default side is not specified, the selection occurs during the document. For example, these are documents of balances.

And also two auxiliary tables:

• Balance history (bill.balance) - the history of the change in the balance of the contract with reference to postings.
• Turnover (Balance) for the reporting period (bill.saldo) - a table with aggregated data on the turnover linked to the period. Very often used in various analytics.

And that is all. If you do not mind answering the survey. The results will be taken into account when writing the next post in the series :) Well, ask your questions in the comments.