Xero intergration

[steven]

Hi All,

Has anyone used the Xero API to import invoices from a nuBuilder App?

I just thought I'd ask here in case its been done before.


Steven

[kev1n]

Hi Steven,

Quick question: do you mean generated PDFs, report JSON, or just invoice-related data records? Just trying to understand what 'invoice' refers to here.

[steven]

OK,

I'm have an ERP that creates invoices for its customers.

I want to put those invoices into Xero (with Xero's API) so that payments for those invoices, can be managed in Xero.

Hope that makes sense.


Steven

[kev1n]

Thanks for the clarification, Steven. That helps, but it’s still a bit unclear how the invoices are actually generated and stored on your end. Are they in PDF format, stored as JSON, saved in a database table? Understanding the format and structure will help determine how best to integrate with Xero's API.

[steven]

OK

These invoices are stored in the nuBuilder (mysql) database.

There is
a table named sale which holds the invoice's information,
a table named sale_item that contains the items on the invoice.
Along with a table with company details and a table named product used by each sale item.

Here is the table structure...

Code: Select all

--
-- Table structure for table `company`
--

CREATE TABLE `company` (
  `company_id` varchar(25) NOT NULL,
  `com_region_id` varchar(25) DEFAULT NULL,
  `com_name` varchar(1000) DEFAULT NULL,
  `com_phone` varchar(25) DEFAULT NULL,
  `com_street1` text DEFAULT NULL,
  `com_street2` text DEFAULT NULL,
  `com_suburb` text DEFAULT NULL,
  `com_city` text DEFAULT NULL,
  `com_postcode` varchar(10) DEFAULT NULL,
  `com_country` text DEFAULT NULL,
  `com_delivery_street1` varchar(500) DEFAULT NULL,
  `com_delivery_street2` varchar(500) DEFAULT NULL,
  `com_delivery_suburb` varchar(500) DEFAULT NULL,
  `com_delivery_city` varchar(500) DEFAULT NULL,
  `com_delivery_postcode` varchar(10) DEFAULT NULL,
  `com_delivery_country` varchar(500) DEFAULT NULL,
  `com_address_format1` text DEFAULT NULL,
  `com_address_format2` text DEFAULT NULL,
  `com_address_format3` text DEFAULT NULL,
  `com_address_format4` text DEFAULT NULL,
  `com_address_format5` text DEFAULT NULL,
  `com_zzzzsys_report_id` varchar(25) DEFAULT NULL,
  `com_email_invoice_subject` varchar(200) DEFAULT NULL,
  `com_email_invoice_content` text DEFAULT NULL,
  `com_email_delivery_subject` varchar(200) DEFAULT NULL,
  `com_email_delivery_content` text DEFAULT NULL,
  `com_invoice_contact_id` varchar(25) DEFAULT NULL,
  `com_delivery_contact_id` varchar(25) DEFAULT NULL,
  `com_freightnet` varchar(3) DEFAULT NULL,
  `com_vat_no` varchar(100) DEFAULT NULL,
  `com_freight_company` varchar(100) DEFAULT NULL,
  `com_freight_account` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `product_id` varchar(25) NOT NULL,
  `pro_code` varchar(100) DEFAULT NULL,
  `pro_description` varchar(1000) DEFAULT NULL,
  `pro_product_group_id` varchar(25) DEFAULT NULL,
  `pro_information` text DEFAULT NULL,
  `pro_weight` decimal(11,4) DEFAULT NULL,
  `pro_unit_cost` decimal(11,4) DEFAULT NULL,
  `pro_units` decimal(11,4) DEFAULT NULL,
  `pro_minimum_units` decimal(11,4) DEFAULT NULL,
  `pro_unit_of_measure` varchar(100) DEFAULT NULL,
  `pro_photo` varchar(1000) DEFAULT NULL,
  `pro_buyable` varchar(3) DEFAULT NULL,
  `pro_sellable` varchar(3) DEFAULT NULL,
  `pro_has_serial_number` varchar(3) DEFAULT NULL,
  `pro_used_by_product_id` varchar(25) DEFAULT NULL,
  `pro_serial_suffix` varchar(10) DEFAULT NULL,
  `pro_is_bundle` varchar(3) DEFAULT NULL,
  `pro_is_box` varchar(3) DEFAULT NULL,
  `pro_box_id` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `sale`
--

CREATE TABLE `sale` (
  `sale_id` varchar(25) NOT NULL,
  `sal_parent_sale_id` varchar(25) DEFAULT NULL,
  `sal_parent_number` varchar(25) DEFAULT NULL,
  `sal_readonly` varchar(1) DEFAULT NULL,
  `sal_sale_type` varchar(100) DEFAULT NULL,
  `sal_sale_description` varchar(100) DEFAULT NULL,
  `sal_number` varchar(25) DEFAULT NULL,
  `sal_purchase_order` varchar(100) DEFAULT NULL,
  `sal_company_id` varchar(25) DEFAULT NULL,
  `sal_date` date DEFAULT NULL,
  `sal_required_by` date DEFAULT NULL,
  `sal_sent` date DEFAULT NULL,
  `sal_sent_reference` varchar(50) DEFAULT NULL,
  `sal_priority` decimal(11,4) DEFAULT 0.0000,
  `sal_packing_date` date DEFAULT NULL,
  `sal_address_format1` text DEFAULT NULL,
  `sal_address_format2` text DEFAULT NULL,
  `sal_address_format3` text DEFAULT NULL,
  `sal_address_format4` text DEFAULT NULL,
  `sal_address_format5` text DEFAULT NULL,
  `sal_tax_percent` decimal(11,4) DEFAULT NULL,
  `sal_tax_total` decimal(11,4) DEFAULT NULL,
  `sal_total` decimal(11,4) DEFAULT NULL,
  `sal_shipping_total` decimal(11,4) DEFAULT NULL,
  `sal_shipping_total_description` varchar(100) DEFAULT NULL,
  `sal_transaction_fee` decimal(11,4) DEFAULT NULL,
  `sal_grand_total` decimal(11,4) DEFAULT NULL,
  `sal_notes` text DEFAULT NULL,
  `sal_freightnet` varchar(3) DEFAULT NULL,
  `sal_fully_boxed` varchar(1) DEFAULT NULL,
  `sal_ship_id` varchar(25) DEFAULT NULL,
  `sal_fully_invoiced` varchar(1) DEFAULT NULL,
  `sal_locked` varchar(1) DEFAULT NULL,
  `sal_cancelled` varchar(1) NOT NULL DEFAULT '0',
  `sal_cancelled_reason` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `sale_item`
--

CREATE TABLE `sale_item` (
  `sale_item_id` varchar(25) NOT NULL,
  `sit_parent_sale_item_id` varchar(25) DEFAULT NULL,
  `sit_invoice_number` varchar(25) DEFAULT NULL,
  `sit_sale_id` varchar(25) DEFAULT NULL,
  `sit_product_id` varchar(25) DEFAULT NULL,
  `sit_bundle` varchar(1) DEFAULT NULL,
  `sit_bundle_parent` varchar(25) DEFAULT NULL,
  `sit_requires_sn` varchar(1) DEFAULT NULL,
  `sit_serial_number` varchar(1000) DEFAULT NULL,
  `sit_received` date DEFAULT NULL,
  `sit_units` decimal(11,4) DEFAULT NULL,
  `sit_produced` date DEFAULT NULL,
  `sit_units_invoiced` decimal(11,4) DEFAULT NULL,
  `sit_unit_cost` decimal(11,4) DEFAULT NULL,
  `sit_unit_price` decimal(11,4) DEFAULT NULL,
  `sit_unit_total` decimal(11,4) DEFAULT NULL,
  `sit_ship_box_id` varchar(25) DEFAULT NULL,
  `sit_box` varchar(200) DEFAULT NULL,
  `sit_box_number` int(11) DEFAULT NULL,
  `sit_ship_id` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Does that help?


Steven