NDIS Claim Portal CSV

The NDIS scheme allows claims to be made on their portal. The claims can be CSV or XML formatted files.

The implementation Guide and case study is available at BI for MYOB Help Centre. The article is Using MYOB for NDIS Processing

The Time Billing facility of MYOB has been utilised in conjunction with Activity Sheets to collect time spent with NDIS participants and to claim for work done with that participant.

A TimeBilling specific mapping has been created in BI for MYOB for reporting on time billing invoices.

A specific detail report NDIS Detail is available in that mapping.

The format of the CSV file is detailed on the NDIS website and the documents is here.

Bulk Claim Upload Specification 1.2.doc

A sample CSV outcome is here.

This is created by using the TimeBillings mapping and select NDIS Detail in BI for MYOB.

This gives a report which has a key and description along with Avg Price, HoursUnits, Totals Sales and Total Units. This need to be stripped out using +/- Columns

So remove the unneeded columns

You now have the columns you need but there is still a Grand Total Line.

To remove this you need to expose all the totalling options in the Properties (Grand Totals and Group Totals) so you need to Group By a field, any field.

I have chosen Activities - this gives the following

Then press the Properties on the Toolbar. which reveals this form.

and unstick the Grand And Sub-Totals.

And lastly remove the Group By by selecting (No Grouping)

The resultant file will be formatted for use on the NDIS portal.

Save It as a favourite

No you don't need to do this overtime. Just save it as a favourite NDIS CSV Claim and use the favourite when making a claim by setting the date range.

You can save this as a favourites so there is no need to do the above formatting again.

All done - now select CSV export and give it a name.

This save the file as CSV.

Don't use Excel to Open this

Do not use Excel to open the CSV file. If you do then Excel will change the formatting of dates to dd-mmm-yyyy, the number will be right justified and leading zeros removed. In short he file will be trashed.

If you wish to inspect the CSV file then use the Notepad program because it's does not reformat of change the file.

Her is what it looks like in Notepad.

Technical Stuff

The NDIS form require both a RegistrationNumber and an AuthorisedBy value. These are installation specific and setup using the Custom Variable labels.

Login as Administrator. Select System Settings / General. Set the Number of User Custom Fields to 2 and name these fields 'User No' and 'NDIS No'

Now select User and Groups and you will see these labels against each user. Add the appropriate values.

For the serious techy these are implemented in the SQL of the NDIS Detail mapping. Here is the select statement.

SQL code for User Labels

SELECT  Sub.*, IIF( Qty_Hours = 0, NULL, FORMAT ( LTRIM(STR(INT(Hours_fmt))) & ':' & LTRIM(STR((Hours_fmt - INT(Hours_fmt)) * 60)), 'Short Time')) AS Hours_fmtHm,
'@user_custom1' as authorized_by,'TRUE'  as approved, '@user_custom2' as RegistrationNumber
FROM 
(
 SELECT 
  TBSLA.*, 
  ir_Customers.CardIdentification,
  FORMAT(TBSLA.InvoiceDate, 'yyyy-mm-dd') AS InvoiceDate_fmt,
  FORMAT(TBSLA.LineDate, 'yyyy-mm-dd') AS InvoiceLineDate_fmt,
  IIF(TBSLA.Qty_Units = 0, NULL, TBSLA.Qty_Units) AS Quantity_fmt,
  IIF(TBSLA.Qty_Hours = 0, NULL, TBSLA.Qty_Hours) AS Hours_fmt,
  FORMAT(TBSLA.TaxExclusiveRate, 'Fixed') AS UnitPrice_fmt,   
  IIF(TX.TaxCode = 'FRE', 'Tax Free (0%)',
  IIF(TX.TaxCode = 'GST', 'Tax Claimable (10%)', 'Oops')) AS GSTCode_fmt
 FROM ir_Taxcodes AS TX INNER JOIN 
 (ir_TimeBillingSaleLinesAll AS TBSLA INNER JOIN ir_Customers
  ON TBSLA.CardRecordID = ir_Customers.CustomerID) 
  ON TX.TaxCodeID = TBSLA.TaxCodeID
) AS Sub