transactions

Module: billing

Billing and procurement transactions (quotes, orders, invoices).

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:

XML

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

XML

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

JSON

	[{
			"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"]
				}
			]
		}
	]

Name	Type	Nullable	Default Value	Notes
PKID	integer			Transaction ID
fork forks.ID	integer			Fork ID (`null` for base module)
ownergroup groups.ID	integer			Owner group ID (`null`=PUBLIC)
creator	integer			Creator user ID (defaults to authenticated user on creation)
assigneduser users.ID	integer			Assigned user ID
creationdate	bigint		date_part('epoch', now())	Creation date and time as a Unix timestamp (defaults to current date and time on creation)
lastmodified	bigint		date_part('epoch', now())	Last modification date and time as a Unix timestamp (auto-reset on modification)
account accounts.ID	integer			Account ID
item items.ID	integer			Item ID; must be `null` for BILLING and PROCUREMENT
contract contracts.ID	integer			Contract ID
transactionnum	text			Transaction number
type	smallint		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	bigint		date_part('epoch', now())	Designated date and time as a Unix timestamp (defaults to current date and time on creation)
duedate	bigint			Due date as a Unix timestamp
status	smallint		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)
calculation	smallint		0	Calculation method (`0`=NET, `1`=GROSS, `2`=EXACT, `3`=LEGACY, `4`=EXTERNAL)
productionfactor	integer			Production factor; is required for PRODUCTION, otherwise must be `null`
currency	character varying(3)			Currency code (ISO 4217)
exchangerate	double precision		1	Exchange rate as a multiple of one monetary unit of the fixed system currency
taxid	text		''	Buyer Tax ID (e.g. VATIN or SSN)
shippingrecipient	text		''	Shipping recipient
shippingaddress	text		''	Shipping address (street and building/suite number)
shippingpostalcode	text		''	Shipping postal or ZIP code
shippingcity	text		''	Shipping city or locality
shippingregion	text		''	Shipping region or state
shippingcountry	character varying(2)		''	Shipping country code (ISO 3166-1 alpha-2)
billingrecipient	text		''	Billing recipient
billingaddress	text		''	Billing address (street and building/suite number)
billingpostalcode	text		''	Billing postal or ZIP code
billingcity	text		''	Billing city or locality
billingregion	text		''	Billing region or state
billingcountry	character varying(2)		''	Billing country code (ISO 3166-1 alpha-2)
sellertaxid	text		''	Seller Tax ID (e.g. VATIN or SSN)
sellername	text		''	Seller Name
selleraddress	text		''	Seller address (street and building/suite number)
sellerpostalcode	text		''	Seller postal or ZIP code
sellercity	text		''	Seller city or locality
sellerregion	text		''	Seller region or state
sellercountry	character varying(2)		''	Seller country code (ISO 3166-1 alpha-2)
discount	double precision		0	Total absolute discount
netamount	double precision		0	Total net amount
tax	double precision		0	Total tax amount
margin	double precision		0	Total absolute margin
weight	double precision		0	Total shipping weight in kilograms (only positive line items)
items	json			JSON-encoded items (array)

Name	Unique	Nulls Distinct	Primary	Partial	Method	Keys
fk_transactions_account					btree	account
fk_transactions_assigneduser					gin	assigneduser
fk_transactions_contract					btree	contract
fk_transactions_fork					gin	fork
fk_transactions_item					btree	item
fk_transactions_ownergroup					gin	ownergroup
i_transactions_billing_date					btree	date
i_transactions_collection_date					btree	date
i_transactions_nofork					gin	fork
i_transactions_noowner					gin	ownergroup
i_transactions_procurement_date					btree	date
i_transactions_production_date					btree	date
i_transactions_transactionnum					btree	transactionnum
pk_transactions					btree	ID
s_transactions_transactionnum					gin	transactionnum
u_transactions_transactionnum_type					btree	transactionnum, type

Name	Type
c_transactions_type	check
dc_transactions_billingaddress	check
dc_transactions_billingcity	check
dc_transactions_billingcountry	check
dc_transactions_billingpostalcode	check
dc_transactions_billingrecipient	check
dc_transactions_billingregion	check
dc_transactions_calculation	check
dc_transactions_currency	check
dc_transactions_exchangerate	check
dc_transactions_items	check
dc_transactions_selleraddress	check
dc_transactions_sellercity	check
dc_transactions_sellercountry	check
dc_transactions_sellername	check
dc_transactions_sellerpostalcode	check
dc_transactions_sellerregion	check
dc_transactions_sellertaxid	check
dc_transactions_shippingaddress	check
dc_transactions_shippingcity	check
dc_transactions_shippingcountry	check
dc_transactions_shippingpostalcode	check
dc_transactions_shippingrecipient	check
dc_transactions_shippingregion	check
dc_transactions_status	check
dc_transactions_taxid	check
dc_transactions_transactionnum	check
dc_transactions_type	check
dc_transactions_weight	check
fk_transactions_account	foreign_key
fk_transactions_assigneduser	foreign_key
fk_transactions_contract	foreign_key
fk_transactions_fork	foreign_key
fk_transactions_item	foreign_key
fk_transactions_ownergroup	foreign_key
pk_transactions	primary_key

Name	Type	Events	Function	Definition
td_transactions_dunning	after	delete	ftud_transactions_dunning
ti_transactions_item	before	insert	ftiu_transactions_item
ti_transactions_status	before	insert	ftiu_transactions_status
tu_transactions_dunning	after	update	ftud_transactions_dunning
tu_transactions_item	before	update	ftiu_transactions_item
tu_transactions_status	before	update	ftiu_transactions_status
tu_transactions_type	before	update	ft_integrity

Loading...