Table core.items
This table stores information on items and other associated information.
# Column Name Nullable Data Type Max Length Description
1item_idinteger0The primary key of this table, which is also a serial field.
2item_codecharacter varying12Code given to the item.
3item_namecharacter varying150Name given to the item.
4item_group_idinteger0
5item_type_idinteger0Foreign key to the table core.item_groups.
6brand_idinteger0Foreign key to the table core.brands.
7preferred_supplier_idbigint0Foreign key to the table core.parties.
8lead_time_in_daysinteger0Days taken for the shipment of the ordered goods.
9weight_in_gramsdouble precision0Weight of an individual item in the unit of measure gram.
10width_in_centimetersdouble precision0Width of an individual item in centimeter.
11height_in_centimetersdouble precision0Height of an individual item in centimeter.
12length_in_centimetersdouble precision0Length of an individual item in centimeter.
13machinableboolean0Select "Yes" if the particular item can be handled by machine while shipping.
14preferred_shipping_mail_type_idinteger0Foreign key to the table core.shipping_mail_types.
15shipping_package_shape_idinteger0Foreign key to the table core.shipping_package_shapes.
16unit_idinteger0Foreign key to the table core.items.
17hot_itemboolean0Hot item will be featured on e-commerce website. Select "Yes" if the item is hot item or vice-versa.
18cost_pricemoney_strict0Cost price of the item.
19cost_price_includes_taxboolean0Select "Yes" if the cost price includes tax or vice-versa.
20selling_pricemoney_strict0Selling price of the item.
21selling_price_includes_taxboolean0Select "Yes" if the selling price includes tax or vice-versa.
22sales_tax_idinteger0Foreign key to the table core.sales_taxes.
23reorder_unit_idinteger0Foreign key to the table core.units.
24reorder_levelinteger0The level of stock on which re-order needs to be placed.
25reorder_quantityinteger0The quantity of good that needs to be ordered as the stock reaches re-order level.
26maintain_stockboolean0Select "Yes" if you want to maintain the record of transfer (in & out) of stock items.Selecting "Yes" prohibits the user from maintaining negative stock level.
27audit_user_idinteger0Contains the id of the user who last inserted or updated the corresponding row.
28audit_tstimestamp with time zone0Contains the date and timestamp of the last insert or update action.

Foreign Keys

# Column Name Key Name References
4 item_group_id items_item_group_id_fkey core.item_groups.item_group_id
5 item_type_id items_item_type_id_fkey core.item_types.item_type_id
6 brand_id items_brand_id_fkey core.brands.brand_id
7 preferred_supplier_id items_preferred_supplier_id_fkey core.parties.party_id
14 preferred_shipping_mail_type_id items_preferred_shipping_mail_type_id_fkey core.shipping_mail_types.shipping_mail_type_id
15 shipping_package_shape_id items_shipping_package_shape_id_fkey core.shipping_package_shapes.shipping_package_shape_id
16 unit_id items_unit_id_fkey core.units.unit_id
22 sales_tax_id items_sales_tax_id_fkey core.sales_taxes.sales_tax_id
23 reorder_unit_id items_reorder_unit_id_fkey core.units.unit_id
27 audit_user_id items_audit_user_id_fkey office.users.user_id

Indices

Index Name Owner Access Method Definition Description
items_pkey postgres btree item_id
items_item_name_uix postgres btree upper(item_name::text)

Check Constraints

Constraint Name Description
items_reorder_quantity_chk
CHECK ((core.convert_unit(reorder_unit_id, unit_id) * reorder_quantity::numeric) >= reorder_level::numeric)

Default Values

# Column Name Default
1 item_id nextval('core.items_item_id_seq'::regclass)
8 lead_time_in_days 0
9 weight_in_grams 0
10 width_in_centimeters 0
11 height_in_centimeters 0
12 length_in_centimeters 0
13 machinable false
19 cost_price_includes_tax false
21 selling_price_includes_tax false
25 reorder_quantity 0
26 maintain_stock true
28 audit_ts now()

Triggers

Trigger Name Targets On Event Timing Condition Order Orientation Description
core.items_unit_check_trigger core.items_unit_check_trigger UPDATE AFTER 0 ROW
core.items_unit_check_trigger core.items_unit_check_trigger INSERT AFTER 0 ROW