When accounting is enabled, the PrintAccounting database of PRISMAdirect stores the accounting information when you finalize an order. In addition, the values of a number of ticket items are also stored, for example, information about the media and sheets. The tables of the PrintAccounting database and their content are described in detail in section The tables and their content of this topic.
The PrintAccounting database offers a number of stored procedures and views.
A stored procedure is prepared SQL code that you save so you can reuse the code over and over again. So, you can write a query and save it as a stored procedure. You can call the stored procedure to execute the SQL code that you saved as part of the stored procedure. In addition, you can pass parameters to the stored procedure. Now, the stored procedure acts based on the parameter values that were passed.
You cannot query the tables OrderCustomItems and JobCustomItems directly. You have to use the stored procedures that can query these two tables. You can query all other tables either directly or you can use stored procedures. The PrintAccounting database offers a number of default stored procedures:
getFinishingAction
This stored procedure:
Contains the finishing actions.
Uses the startDate and endDate as parameters.
Uses the view uv_FinishingAction.
uspGetAccountingDataDump
This stored procedure:
Contains all information of the orders and jobs including the custom order items and the custom job items.
Uses the startDate and endDate as parameters.
Queries the tables: OrderItems, OrderCustomItems, JobItems, JobCustomItems, User, MediaItem, FinishingAction, JobMediaSettings, ImageSettings, JobImageSettings.
Uses the view uv_JobImageSettings.
Uses the startDate and endDate as parameters.
uspGetAllJobsItems
This stored procedure:
Contains all information of the jobs including custom job items.
Queries the tables: JobItems, JobCustomItems.
uspGetAllOrdersItems
This stored procedure:
Contains all information of the orders including the custom order items.
Queries the tables: OrderItems, OrderCustomItems.
uspGetOrderJobsItems
This stored procedure:
Contains all information of the orders and jobs including the custom order items and the custom job items.
Queries the tables: OrderItems, OrderCustomItems, JobItems, JobCustomItems, User.
uspGetOrderJobsItemsDump
This stored procedure:
Contains all information of the orders and jobs including the custom order items and the custom job items.
Queries the tables: OrderItems, OrderCustomItems, JobItems, JobCustomItems, User.
Executes uspGetOrderJobsItems with parameters @DumpDataInTempTable = True and @TempTable = ##ojdetails. The name for @TempTable is hard-coded.
NOTE
Both uspGetOrderJobsItems and uspGetOrderJobsItemsDump contain the same information.
A view is a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query. A view can contain one or more rows from a table, or even all rows of a table. A view can be created from one or many tables which depends on the written SQL query to create a view.
The PrintAccounting database offers a number of default views:
uv_FinishingAction
This view:
Contains the finishing information and the key OrderAccountingId. The key OrderAccountingId is displayed as FinishingAction_Accounting_Id.
Queries the tables: FinishingAction, JobItems, OrderItems.
uv_JobImageSettings
This view:
Contains the information of the images of the jobs and the key JobAccountingId. An image contains all digital information of the page, e.g. text, images, frames, etc.
Queries the tables: JobImageSettings, ImageSettings.
The default name for the database containing the accounting information is “PrintAccounting”. This database is created when you install PRISMAdirect. The following tables are always available:
FinishingAction
ImageSettings
InvoiceSettings
JobCustomItems
JobImageSettings
JobItems
JobMediaSettings
MediaItem
OrderCustomItems
OrderItems
Pricing
This table is available since PRISMAdirect 1.3.4
Transactions
User
Two additional tables can be created depending on the configuration of PRISMAdirect:
TransactionInfoes
Enable payment to create this table.
ShipmentPackagesHistories
Enable shipping to create this table.
To enable accounting, go to the [Configuration] workspace and select [System] - [Accounting and reporting] - [Accounting] - [Accounting workflow]. When you have enabled accounting, then the accounting information is stored in the database when you finalize an order. In addition, the values of a number of ticket items are also stored, for example, information about the media and sheets.
Table: OrderItems
This table contains a large part of the common order items. The OrderCustomItems table contains the remainder of the common order items and all custom order items.
One record per order.
You can either query this table directly or you can use a stored procedure that has access to this table.
The items CostCenterName and CostCenterID only receive a value when uniFLOW is paired with PRISMAdirect.
|
Column name |
Item name |
Item caption |
Description |
|---|---|---|---|
|
OrderAccountingID |
Primary key |
||
|
OrderNumber |
OrderNumber |
Order number |
|
|
OrderName |
OrderName |
Order name |
|
|
OrderQueueEntryID |
OrderQEntryID |
Non-public identification item |
|
|
SubmissionTime |
CreationDate |
Creation date |
|
|
AcceptanceTime |
AcceptanceTime |
Accepted on |
Has a value only if item AcceptedBy has a value |
|
AcceptedBy |
AcceptedBy |
Accepted by |
|
|
QuotationSentTime |
QuotationSentTime |
Quotation sent on |
Has a value only if item QuotationSentBy has a value |
|
QuotationSendBy |
QuotationSentBy |
Quotation sent by |
|
|
QuatationAcceptedTime |
QuotationAcceptTime |
Quotation accepted on |
Has a value only if item QuotationAcceptedBy has a value |
|
QuotationAcceptedBy |
QuotationAcceptedBy |
Quotation accepted by |
|
|
OrderDeadline |
Date |
Delivery date |
|
|
OrderCompletionTime |
FinalizedTime |
Finalized on |
Has a value only if item FinalizedBy has a value |
|
OrderCompletedBy |
FinalizedBy |
Finalized by |
|
|
OrderDispatchTime |
DispatchTime |
Dispatched on |
Has a value only if item DispatchedBy has a value |
|
OrderDispatchedBy |
DispatchedBy |
Dispatched by |
|
|
OrderDelivery |
No value |
||
|
CostCenterID |
CostCenterID |
Cost center ID |
This item only receives a value when uniFLOW is paired |
|
CostCenterName |
CostCenterName |
Cost center name |
This item only receives a value when uniFLOW is paired |
|
FinalCost |
FinalCost |
Total price |
|
|
ContactAddress |
ContactAddress |
Contact address |
|
|
DeliveryAddress |
DeliveryAddress |
Delivery address |
|
|
BillingAddress |
BillingAddress |
Billing address |
|
|
Version |
Version of PRISMAdirect |
||
|
ShipmentPrice |
ShipmentPrice |
Shipping price |
|
|
ShipmentTotalPrice |
ShipmentTotalPrice |
Total shipping price |
|
|
NumberOfShipmentPackages |
NumberOfShipmentPackages |
Number of packages |
|
|
OrderTaxPrice |
OrderTaxPrice |
Tax |
|
|
Discount |
Sum of the value of item JobDiscount for all jobs |
||
|
OrderCurrency |
OrderCurrency |
Currency |
Table: JobItems
This table contains a large part of the common job items. The JobCustomItems table contains the remainder of the common job items and all custom job items.
One record per job.
You can either query this table directly or you can use a stored procedure that has access to this table.
|
Column name |
Item name |
Item caption |
Description |
|---|---|---|---|
|
JobAccountingID |
Primary key |
||
|
OrderAccountingID |
Foreign key of table OrderItems |
||
|
JobNumber |
JobNumber |
Job number |
|
|
JobName |
JobName |
Job name |
|
|
JobQueueEntryID |
QueueEntryID |
Non-public identification item |
|
|
JobComment |
Comments |
Remarks |
|
|
ProductType |
Job product type, for example, stationery |
||
|
ProductName |
Product name |
Job product name, for example, monthly report |
|
|
ProductCaption |
Job product caption in the installation language of PRISMAdirect, for example, monthly report) |
||
|
JobInfo |
JobInfo |
Job information |
|
|
JobDeadline |
Date |
Delivery date |
|
|
JobCompletionTime |
FinalizedDate |
Job marked 'Finalized' on |
Has a value only if item FinalizedBy has a value |
|
JobCompletedBy |
FinalizedBy |
Finalized by |
|
|
JobDispatchTime |
DispatchTime |
Dispatched on |
Has a value only if item DispatchedBy has a value |
|
JobDispatchedBy |
DispatchedBy |
Dispatched by |
|
|
Copies |
Copies, or Quantity for stationery products |
Copies / Quantity |
|
|
Covers |
CoverPlace |
Cover |
FrontAndBack, None, etc |
|
CoverMedia |
NewCoverMedia |
Cover media |
For example: ,S_A4,S_Plain,S_Red_249_37_94,120 g/m2,False,False,False,1,False,,[0.1;0;Millimeters],[2.5;0;Millimeters],[2.5;0;Millimeters] |
|
DocumentMedia |
Media |
Media |
For example: ,S_A4,S_Plain,S_White_255_255_255,80 g/m2,False,False,False,1,False,,[0.1;0;Millimeters],[2.5;0;Millimeters],[2.5;0;Millimeters] |
|
BWPages |
EstimatedBWPages |
Number of B&W pages |
|
|
ColorPages |
EstimatedColorPages |
Number of color pages |
|
|
PrintPages |
Not used |
||
|
ScanPages |
Not used |
||
|
PrintArea |
Not used |
||
|
StandardPrice |
Not used |
||
|
Price1 |
Not used |
||
|
Price2 |
Not used |
||
|
Price3 |
Not used |
||
|
FinalCost |
FinalCost |
Total price |
|
|
PreparationDuration |
PrepareTime |
Time to prepare job |
|
|
FinishingDuration |
FinishingTime |
Time to finish job |
|
|
Labor |
ManualLabor |
Labor cost |
|
|
PrinterUsed |
PrinterName |
Printer name |
|
|
PrintingDuration |
PrintingDuration |
Time to print job |
|
|
NumberOfDocumentSets |
NumberOfCopiesPerSet * NumberOfBussinessCardsPerSet |
||
|
JobTaxPrice |
JobTaxPrice |
Tax |
|
|
QuotationJobTaxPrice |
QuotationJobTaxPrice |
Tax |
Contains only tax price for job quotation |
|
JobTaxRate |
JobTaxRate |
Tax rate (%) |
|
|
JobDiscount |
JobDiscount |
Discounts |
Table: OrderCustomItems
This table contains the remainder of the common order items that are not in the OrderItems table. It also contains all custom order items.
One record per custom item.
You cannot query this table directly. You have to use a stored procedure that has access to this table.
|
Column name |
Item name |
Description |
|---|---|---|
|
OrderAccountingID |
Foreign key of table OrderItems |
|
|
OrderItemName |
Name of the custom order item |
|
|
OrderItemType |
For example: nvarchar, datetime, real. The types are stored as SQL item types. |
|
|
Value |
The item value is always stored as string. |
Table: JobCustomItems
This table contains the remainder of the common job items that are not in the JobItems table. It also contains all custom job items.
One record per custom item.
You cannot query this table directly. You have to use a stored procedure that has access to this table.
|
Column name |
Item name |
Description |
|---|---|---|
|
JobAccountingID |
Foreign key of table JobItems |
|
|
JobItemName |
Name of the custom job item |
|
|
JobItemType |
For example: nvarchar, datetime, real. The types are stored as SQL item types. |
|
|
Value |
The item value is always stored as string. |
Table: User
This table contains the information of the customer for a given order.
One record per customer.
You can either query this table directly or you can use a stored procedure that has access to this table.
|
Column name |
Item name |
Description |
|---|---|---|
|
OrderAccountingID |
Foreign key of table OrderItems |
|
|
UserName |
UserId |
|
|
AccountName |
Account |
|
|
LastName |
LastName |
|
|
FirstName |
FirstName |
|
|
Department |
Department |
|
|
Location |
Location |
|
|
Company |
Company |
|
|
TelephoneNumber |
TelephoneNumber |
|
|
|
EmailAddress |
Table: Transactions
This table contains the transactions per cost center.
One record per cost center.
You can either query this table directly or you can use a stored procedure that has access to this table.
|
Column name |
Item name |
Description |
|---|---|---|
|
TransactionID |
Primary key |
|
|
Date |
Date of transaction |
|
|
OrderAccountingID |
Foreign key of table OrderItems |
|
|
UserName |
UserId |
|
|
TransactionType |
set / spent |
|
|
TransactionValue |
FinalCost |
|
|
CostCenterName |
CostCenterName |
|
|
CostCenterID |
CostCenterId |
Table: InvoiceSettings
This table contains information that is used for an invoice.
One record per invoice.
You can either query this table directly or you can use a stored procedure that has access to this table.
|
Column name |
Item name |
Description |
|---|---|---|
|
CurrencyID |
Primary key |
|
|
CurrencyString |
EUR, CHF, … |
|
|
CurrencyPosition |
TRUE if the currency is displayed in front of the amount |
|
|
CompanyLogo |
Company logo image |
|
|
CompanyName |
Company name |
|
|
CompanyAddress |
Company address |
Table: Pricing
This table contains all items that are used in the formula, along with the calculated price for each item.
One record per item.
You can either query this table directly or you can use a stored procedure that has access to this table.
|
Column name |
Item name |
Description |
|---|---|---|
|
JobAccountingID |
Foreign key of table JobItems |
|
|
VariableName |
Formula variable name. For example, 800BW, Copies, FinishingPricing, DocumentMediaWeight. |
|
|
Value |
The calculated price for the item as numeric value. |
Table: FinishingAction
This table contains information about the selected finishing for a job, for example, binding, folding, punching.
One record per job.
You can either query this table directly or you can use a stored procedure that has access to this table.
You can prepare a PDF file in PRISMAprepare. When you close PRISMAprepare, the values of the prepared items are stored in the PDF file itself. When you finalize the order, the finishing items are read from the PDF file and stored in the database. In this case, the values of the finishing items of PRISMAdirect are ignored. Reason: the values of the finishing items of PRISMAprepare are considered to be more correct.
|
Column name |
Item name |
Description |
|---|---|---|
|
FinishingActionID |
Primary key |
|
|
JobAccountingID |
Foreign key of table JobItems |
|
|
Type |
For example, Staples_4, Holes_3 |
|
|
Unit |
For example, Copy, Sheet |
|
|
FinishingQuantity |
Numeric value |
Table: ImageSettings
This table contains the unique image settings of all jobs. The database will not contain double entries for an image. An image contains all digital information of the page, e.g. text, images, frames, etc.
One record per image.
You can either query this table directly or you can use a stored procedure that has access to this table.
You can prepare a PDF file in PRISMAprepare. When you close PRISMAprepare, the values of the prepared items are stored in the PDF file itself. When you finalize the order, the image settings are read from the PDF file and stored in the database. In this case, the image settings used by PRISMAdirect are ignored.
Reason: in PRISMAprepare you can use multiple image sizes in a file, for example, A4 and A3 images. In PRISMAdirect only one image size is possible. For correct accounting, all sizes must be taken into account.
|
Column name |
Item name |
Description |
|---|---|---|
|
ImageSettingID |
Primary key |
|
|
ImageKey |
This key is created by appending the five image items in combination with the “,” separator. For example: Duplex,Color,2100,2970,TenthOfMillimeter |
|
|
Plexity |
Simplex, Duplex |
|
|
Colour |
BlackAndWhite, Color, Mixed |
|
|
ImageWidth |
||
|
ImageHeight |
||
|
ImageSizeUnit |
For example, Millimeters, Centimeters, TenthOfMillimeter, HundredthOfMillimeter, ThousandthOfMillimeter |
|
|
OTSName |
Not used |
Table: JobImageSettings
This table contains the number of images per job. An image contains all digital information of the page, e.g. text, images, frames, etc.
One record per job.
You can either query this table directly or you can use a stored procedure that has access to this table.
|
Column name |
Item name |
Description |
|---|---|---|
|
JobAccountingID |
Foreign key of table JobItems |
|
|
ImageSettingID |
Foreign key of table ImageSettings |
|
|
ImageCount |
Number of pages for image settings |
|
|
Range |
Not used |
Table: MediaItem
This table contains the unique media of all jobs. The database will not contain double entries for a media.
One record per media.
You can either query this table directly or you can use a stored procedure that has access to this table.
The following media items will be stored:
When no PDF file is available, the media as defined in the ticket of PRISMAdirect is stored.
When a PDF file is available, but the file is not prepared in PRISMAprepare, then:
The media as defined in the ticket of PRISMAdirect is stored. The media used by the PDF file is ignored.
When the PDF file is prepared in PRISMAprepare, then:
You can prepare a PDF file in PRISMAprepare. When you close PRISMAprepare, the values of the prepared items are stored in the PDF file itself. When you finalize the order, the media items of PRISMAprepare are read from the PDF file and stored in the database. In this case, the media items used by PRISMAdirect are ignored.
|
Column name |
Item name |
Description |
|---|---|---|
|
MediaItemID |
Primary key |
|
|
MediaKey |
For example, 595x842 points, Tab(False), Cyclic(1), 120 g/m2, Plain, Red |
|
|
MediaName |
Media name |
|
|
MediaType |
For example, Plain, Transparent, Envelope, etc |
|
|
MediaWidth |
Numeric value |
|
|
MediaHeight |
Numeric value |
|
|
MediaSizeUnit |
For example, points, TenthOfMillimeter |
|
|
MediaWeight |
Numeric value |
|
|
MediaWeightUnit |
For example, g/m2 |
|
|
MediaColor |
For example, White, Red |
|
|
PrePunchedMedia |
Media prepunched numbers (numeric value) |
|
|
TabMedia |
True or False |
|
|
CycleLength |
Numeric value |
Table: JobMediaSettings
This table contains the number of sheets per job.
One record per job.
You can either query this table directly or you can use a stored procedure that has access to this table.
|
Column name |
Item name |
Description |
|---|---|---|
|
JobAccountingID |
Foreign key of table JobItems |
|
|
MediaItemID |
Foreign key of table MediaItem |
|
|
SheetCount |
Number of media sheets |
|
|
Range |
Not used |
Table: TransactionInfoes
PRISMAdirect creates this table when payment is enabled. This table contains the information concerning the payment transactions.
One record per transaction.
You can either query this table directly or you can use a stored procedure that has access to this table.
|
Column name |
Description |
|---|---|
|
Id |
Primary key |
|
OrderNumber |
Order number |
|
OrderName |
Order name |
|
OrderId |
Non-public identification item |
|
Provider |
The payment provider |
|
TransactionId |
The payment transaction ID received from the provider |
|
Timestamp |
The timestamp of the payment transaction |
|
Status |
Information about the payment transaction. For example, start, end, cancel, refund. |
|
Amount |
The price involved in the payment transaction |
|
Currency |
The currency used in the payment transaction |
|
PayerName |
Payer name |
|
PayerEmailAddress |
Payer email address |
|
PayerPhoneNumber |
Payer telephone number |
|
PayerShippingAddress |
Payer address |
|
PayerStatus |
Payer status received from the payment provider |
|
PayerIP |
IP host address of the payer |
|
ServerName |
Name of the computer where PRISMAdirect is installed |
|
ServerIP |
IP address of the computer where PRISMAdirect is installed |
|
Tax |
The tax involved in the payment transaction. The tax is included in the amount. |
Table: ShipmentPackagesHistories
PRISMAdirect creates this table when shipment is enabled. This table contains the information concerning the sent packages.
One record per package.
You can either query this table directly or you can use a stored procedure that has access to this table.
|
Column name |
Description |
|---|---|
|
Id |
Primary key |
|
OrderNumber |
Order number |
|
OrderName |
Order name |
|
Description |
Description of the package description. This field is mandatory for international shipping. |
|
ShipmentProvider |
The shipping provider |
|
DeliveryDateUTC |
The timestamp of the shipping transaction |
|
Length |
Length of the package |
|
Width |
Width of the package |
|
Height |
Height of the package |
|
Unit |
Unit of the package size in [inch] or [cm] |
|
WeightValue |
Weight of the package |
|
WeightUnit |
Unit of the package weight in [lb] or [kg] |
|
ShipmentAddressFrom |
The shipping address of the sender |
|
ShipmentAddressDestination |
The shipping address of the recipient |
|
InsuranceValue |
Insurance value of the package |
|
InsuranceCurency |
The currency used for the insurance value of the package |
|
ShipmentCostValue |
The shipping cost for the package |
|
ShipmentCostCurency |
The currency used for the shipping cost |