Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

« Previous Version 3 Next »

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

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 at in this document -

 <Put link to dock here >

A sample outcome is here.

This is created by using the TimeBillings mapping and select  NDIS Detail.

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.

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 a AuthorisedBy . These are installation specific and setup using the Custom Variable labels.

 

Login as Administrator. Select System Setins / 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 again each user. Add the appropriate values.

For the serious tecy these are implemented in the hSQL of the NDIS Detail mapping. Heres 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

 

 

 

 

 

  • No labels