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
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