transactions


Details

Status codes

Status Activity ID Color Quotes (0) Orders (1) Deliveries (2) Invoices (3) Credits (4)
Draft DRAFT 0 green X X X X X
Booked OPEN 1 green X X X X X
Held (On Hold) OPEN 2 orange X X X X X
Cancelled CANCELLED 3 red X X X X X
Closed CLOSED 4 black X X X X X
Partly Ordered OPEN 5 orange X - - - -
Partly Ordered / Cancelled CANCELLED 6 red X - - - -
Partly Ordered / Closed CLOSED 7 black X - - - -
Ordered CLOSED 8 black X - - - -
Partly Delivered OPEN 9 orange - X - - -
Partly Delivered / Cancelled CANCELLED 10 red - X - - -
Partly Delivered / Closed CLOSED 11 black - X - - -
Delivered CLOSED 12 black - X - - -
Partly Invoiced OPEN 13 orange - X X - -
Partly Invoiced / Cancelled CANCELLED 14 red - X X - -
Partly Invoiced / Closed CLOSED 15 black - X X - -
Invoiced CLOSED 16 black - X X - -
Partly Paid OPEN 17 orange - - - X X
Partly Paid / Cancelled CANCELLED 18 red - - - X X
Partly Paid / Closed CLOSED 19 black - - - X X
Paid CLOSED 20 black - - - X X
Overpaid CLOSED 21 black - - - X X

Transaction Items

Read and Write

Every once in a while, you might want to take a closer look at the items involved in the transactions at hand. You can do so by retrieving them in a db:select and then decoding them from JSON into an associative array with decode:json like this:

<db:select var_result="transaction" type="self">
    <db:fields>
        <db:field>items</db:field>
    </db:fields>
    <db:table>transactions</db:table>
    <db:is field="transactionnum">L.1409.1234</db:is>
</db:select>

<decode:json var="transitems">$transaction.items</decode:json>

Conversely, when you create a transaction, you can initialize the items with transitems and encode them with encode:json before you write them into the database:

<transitems var="transitems" />

<encode:json var="transitems" var_result="jsonItems" />

<db:insert table="transactions">
    ...
    <db:data field="items">$jsonItems</db:data>
</db:insert>

Structure

Transaction items are stored as an array of objects (like a list of products). A product has the following basic properties:

Property Description
type 0 for line item, 1 for text
original The original (previous) transaction
subindex The subindex of that transaction
references Further references to other transactions (siblings and descendants)

Each of the references has two properties:

Property Description
transaction The referenced transaction
subindex The subindex of the transaction

If the line is just text, its properties can be found here:

Property Description
variant The variant of the text entry (description, annotation, subtitle, title, header)
text The subindex of the transaction

There are further properties that describe the product:

Property Description
name Name of the product
manufacturer Manufacturer
itemnum Item number
barcode Bar code / EAN
itemtype Item type
unit Unit of measure
amount Amount
amounttaken Amount taken
sellingprice Selling price
purchaseprice Purchase price
rebate Rebate
discount Discount
discount2 Second discount
taxrate Tax rate
weight Weight
item ID of the item
reservation ID of the reservation stock transaction (only for active orders)
transactions Transactions array

The transactions array looks as follows:

Property Description
transaction ID of the stock transaction
storage ID of the storage
amount Amount
chargenum Charge number
location Stock location
serials Serial

The JSON of a full-blown items field looks somewhat like this:


    [{
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 4,
            "text" : "Header-Test"
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 3,
            "text" : "Title Test"
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 2,
            "text" : "Subtitle Test"
        }, {
            "type" : 0,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "name" : "Testartikel",
            "manufacturer" : "Ich",
            "itemnum" : "18929537",
            "barcode" : "2541235256262626233",
            "itemtype" : 1,
            "unit" : "C62",
            "amount" : 23,
            "amounttaken" : 0,
            "sellingprice" : 50,
            "purchaseprice" : 40,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 19,
            "weight" : 2,
            "item" : 1,
            "reservation" : null,
            "transactions" : []
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 0,
            "text" : "Description Test"
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 2,
            "text" : "Subtitle Test 2"
        }, {
            "type" : 0,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "name" : "Serienartikel",
            "manufacturer" : "FACTORY INC.",
            "itemnum" : "ARTNUM12345",
            "barcode" : "165749875341",
            "itemtype" : 1,
            "unit" : "C62",
            "amount" : 2,
            "amounttaken" : 0,
            "sellingprice" : 0,
            "purchaseprice" : 0,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 0,
            "weight" : 0,
            "item" : 4,
            "reservation" : null,
            "transactions" : []
        }, {
            "type" : 0,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "name" : "Testartikel",
            "manufacturer" : "Ich",
            "itemnum" : "18929537",
            "barcode" : "2541235256262626233",
            "itemtype" : 1,
            "unit" : "C62",
            "amount" : 12,
            "amounttaken" : 0,
            "sellingprice" : 50,
            "purchaseprice" : 40,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 19,
            "weight" : 2,
            "item" : 1,
            "reservation" : null,
            "transactions" : []
        }, {
            "type" : 1,
            "original" : null,
            "subindex" : 0,
            "references" : [],
            "variant" : 0,
            "text" : "Description Test 2"
        }, {
            "type" : 0,
            "original" : 46,
            "subindex" : 0,
            "references" : [],
            "name" : "Testartikel",
            "manufacturer" : "Ich",
            "itemnum" : "18929537",
            "barcode" : "2541235256262626233",
            "itemtype" : 0,
            "unit" : "C62",
            "amount" : 5,
            "amounttaken" : 0,
            "sellingprice" : 50,
            "purchaseprice" : 40,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 19,
            "weight" : 2,
            "item" : 1,
            "reservation" : null,
            "transactions" : [{
                    "transaction" : 47,
                    "storage" : 1,
                    "amount" : 12,
                    "chargenum" : "",
                    "serials" : []
                }
            ]
        }, {
            "type" : 0,
            "original" : 46,
            "subindex" : 0,
            "references" : [],
            "name" : "Testartikel",
            "manufacturer" : "Ich",
            "itemnum" : "18929537",
            "barcode" : "2541235256262626233",
            "itemtype" : 0,
            "unit" : "C62",
            "amount" : 12,
            "amounttaken" : 0,
            "sellingprice" : 50,
            "purchaseprice" : 40,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 19,
            "weight" : 2,
            "item" : 1,
            "reservation" : null,
            "transactions" : [{
                    "transaction" : 47,
                    "storage" : 1,
                    "amount" : 12,
                    "chargenum" : "",
                    "serials" : []
                }
            ]
        }, {
            "type" : 0,
            "original" : 57,
            "subindex" : 0,
            "references" : [],
            "name" : "Serienartikel",
            "manufacturer" : "FACTORY INC.",
            "itemnum" : "ARTNUM12345",
            "barcode" : "165749875341",
            "itemtype" : 1,
            "unit" : "C62",
            "amount" : 1,
            "amounttaken" : 0,
            "sellingprice" : 0,
            "purchaseprice" : 0,
            "rebate" : 0,
            "discount" : 0,
            "discount2" : 0,
            "taxrate" : 0,
            "weight" : 0,
            "item" : 4,
            "reservation" : null,
            "transactions" : [{
                    "transaction" : 59,
                    "storage" : 1,
                    "amount" : 1,
                    "chargenum" : "",
                    "serials" : ["98144-98144-98144-98144"]
                }
            ]
        }
    ]

Columns

Column Type Size Nulls Auto Default Children Parents Comments
ID int4 10 √ null
actionsteps.transaction fk_actionsteps_transaction N
couponcodes.transaction fk_couponcodes_transaction N
dunning2transactions.transaction fk_dunning2transactions_transaction C
payments.transaction fk_payments_transaction N
stocktransactions.transaction fk_stocktransactions_transaction N

Transaction ID
ownergroup int4 10 √ null
groups.ID fk_transactions_ownergroup C

Owner group ID (null=PUBLIC)
creator int4 10 √ null
users.ID fk_transactions_creator N

Creator user ID (defaults to authenticated user on creation)
assigneduser int4 10 √ null
users.ID fk_transactions_assigneduser N

Assigned user ID
creationdate int8 19 date_part('epoch'::text, now())

Creation date and time as a Unix time stamp (defaults to current date and time on creation)
lastmodified int8 19 date_part('epoch'::text, now())

Last modification date and time as a Unix time stamp (auto-reset on modification)
account int4 10 √ null
accounts.ID fk_transactions_account N

Account ID; must be null for PRODUCTION
item int4 10 √ null
items.ID fk_transactions_item C

Item ID; is required for PRODUCTION, otherwise must be null
transactionnum text 2147483647 null

Transaction number
type int2 5 0

Transaction type (0=BILLING_QUOTE, 1=BILLING_ORDER, 2=BILLING_DELIVERY, 3=BILLING_INVOICE, 4=BILLING_CREDIT, 5=PROCUREMENT_REQUEST, 6=PROCUREMENT_ORDER, 7=PROCUREMENT_DELIVERY, 8=PROCUREMENT_INVOICE, 9=PROCUREMENT_CREDIT, 10=PRODUCTION_FABRICATION, 11=PRODUCTION_DISASSEMBLY)
date int8 19 date_part('epoch'::text, now())

Designated date and time as a Unix time stamp (defaults to current date and time on creation)
duedate int8 19 √ null

Due date as a Unix time stamp
status int2 5 0

Status (0=DRAFT, 1=BOOKED, 2=HOLD, 3=CANCELLED, 4=CLOSED, 5=PARTLYORDERED, 6=PARTLYORDERED_CANCELLED, 7=PARTLYORDERED_CLOSED, 8=ORDERED, 9=PARTLYDELIVERED, 10=PARTLYDELIVERED_CANCELLED, 11=PARTLYDELIVERED_CLOSED, 12=DELIVERED, 13=PARTLYINVOICED, 14=PARTLYINVOICED_CANCELLED, 15=PARTLYINVOICED_CLOSED, 16=INVOICED, 17=PARTLYPAID, 18=PARTLYPAID_CANCELLED, 19=PARTLYPAID_CLOSED, 20=PAID, 21=OVERPAID, 22=PROCESSED, 23=PROCESSED_CANCELLED)
productionfactor int4 10 √ null

Production factor; is required for PRODUCTION, otherwise must be null
currency varchar 3 null

Currency code (ISO 4217) (Pattern: ^[A-Z]{3}$)
exchangerate float8 17,17 1

Exchange rate as a multiple of one monetary unit of the fixed system currency
shippingrecipient text 2147483647 ''::text

Shipping recipient
shippingaddress text 2147483647 ''::text

Shipping address (street and building/suite number)
shippingpostalcode text 2147483647 ''::text

Shipping postal or ZIP code
shippingcity text 2147483647 ''::text

Shipping city or municipality
shippingregion text 2147483647 ''::text

Shipping region or state
shippingcountry varchar 2 ''::character varying

Shipping country code (ISO 3166-1 alpha-2) (Pattern: ^([A-Z]{2})?$)
billingrecipient text 2147483647 ''::text

Billing recipient
billingaddress text 2147483647 ''::text

Billing address (street and building/suite number)
billingpostalcode text 2147483647 ''::text

Billing postal or ZIP code
billingcity text 2147483647 ''::text

Billing city or municipality
billingregion text 2147483647 ''::text

Billing region or state
billingcountry varchar 2 ''::character varying

Billing country code (ISO 3166-1 alpha-2) (Pattern: ^([A-Z]{2})?$)
discount float8 17,17 0

Total absolute discount
netamount float8 17,17 0

Total net amount
tax float8 17,17 0

Total tax amount
margin float8 17,17 0

Total absolute margin
weight float8 17,17 0

Total shipping weight in kilogram
items json 2147483647 √ null
contract int4 10 √ null
contracts.ID fk_transactions_contract N

Contract ID

Table contained 0 rows

Indexes

Constraint Name Type Sort Column(s)
pk_transactions Primary key Asc ID
fk_transactions_account Performance Asc account
fk_transactions_contract Performance Asc contract
fk_transactions_item Performance Asc item
i_transactions_date_ID Performance Desc/Desc date + ID
i_transactions_noowner Performance Asc ownergroup
i_transactions_owner Performance Asc/Asc ownergroup + assigneduser
i_transactions_transactionnum_ID Performance Desc/Desc transactionnum + ID
i_transactions_type Performance Asc type
s_transactions Performance Asc transactionnum
u_transactions_transactionnum_type Must be unique Asc/Asc transactionnum + type

Relationships

Close relationships within degrees of separation