View: core.item_view

 CREATE OR REPLACE VIEW core.item_view
 AS
 SELECT items.item_id,
    items.item_code,
    items.item_name,
    ((item_groups.item_group_code::text || ' ('::text) || item_groups.item_group_name::text) || ')'::text AS item_group,
    ((item_types.item_type_code::text || ' ('::text) || item_types.item_type_name::text) || ')'::text AS item_type,
    items.maintain_stock,
    ((brands.brand_code::text || ' ('::text) || brands.brand_name::text) || ')'::text AS brand,
    ((parties.party_code::text || ' ('::text) || parties.party_name) || ')'::text AS preferred_supplier,
    items.lead_time_in_days,
    items.weight_in_grams,
    items.width_in_centimeters,
    items.height_in_centimeters,
    items.length_in_centimeters,
    items.machinable,
    ((shipping_mail_types.shipping_mail_type_code::text || ' ('::text) || shipping_mail_types.shipping_mail_type_name::text) || ')'::text AS preferred_shipping_mail_type,
    ((shipping_package_shapes.shipping_package_shape_code::text || ' ('::text) || shipping_package_shapes.shipping_package_shape_name::text) || ')'::text AS preferred_shipping_package_shape,
    ((units.unit_code::text || ' ('::text) || units.unit_name::text) || ')'::text AS unit,
    ((base_unit.unit_code::text || ' ('::text) || base_unit.unit_name::text) || ')'::text AS base_unit,
    items.hot_item,
    items.cost_price,
    items.cost_price_includes_tax,
    items.selling_price,
    items.selling_price_includes_tax,
    ((sales_taxes.sales_tax_code::text || ' ('::text) || sales_taxes.sales_tax_name::text) || ')'::text AS sales_tax,
    ((reorder_unit.unit_code::text || ' ('::text) || reorder_unit.unit_name::text) || ')'::text AS reorder_unit,
    items.reorder_level,
    items.reorder_quantity
   FROM core.items
   JOIN core.item_groups ON items.item_group_id = item_groups.item_group_id
   JOIN core.item_types ON items.item_type_id = item_types.item_type_id
   JOIN core.brands ON items.brand_id = brands.brand_id
   JOIN core.parties ON items.preferred_supplier_id = parties.party_id
   JOIN core.units ON items.unit_id = units.unit_id
   JOIN core.units base_unit ON core.get_root_unit_id(items.unit_id) = units.unit_id
   JOIN core.units reorder_unit ON items.reorder_unit_id = reorder_unit.unit_id
   JOIN core.sales_taxes ON items.sales_tax_id = sales_taxes.sales_tax_id
   LEFT JOIN core.shipping_mail_types ON items.preferred_shipping_mail_type_id = shipping_mail_types.shipping_mail_type_id
   LEFT JOIN core.shipping_package_shapes ON items.shipping_package_shape_id = shipping_package_shapes.shipping_package_shape_id;