this screen will load the data for the period  selected (whatever you choose .. from one month or 900 months.. suggest update current and 2 prior months as a rule)

The mapping of PI5 depts and staff codes is complex and IS DOCUMENTED HERE

data is in <tla>_PI5Data and <tla>_PI5Util Tables for SQL queries..

Data table holds more detail in Raw form as copied by union query view_PI5_local from the various PI5 servers around the state..

Please ensure that your Application Setup Screen has PI5 set as Allied health System

PI5 Development

This is a complex little thing 

Contents

PI5 Development........1

Mapping........3

Mapping Payroll ID to Department (Courtesy of Col Roberts)........4

Jims Comments........4

Development Options........5

Some tips on getting faster queries........6

PI5 Development

This is a complex little thing J

Shortcomings in the detail of data applied to “Program” in PI5 means that the only way to identify special Allied health Funded programs is by the use of PAYROLL staff ID’s. There are three Dept elements mappable in PI5.

• Program which appears to be generally used to match Discipline
• Professional Group of the person (bit unclear for assitants)
• Payroll Number of Staff member (thousands)

This is broken out in talons for each Campus TLA as 

• a mapping for program,
• one for Program code - professional groupcode combination
• and one for staff ID

(At ROBINA & GCH and GCHU there is a letter prefix as they are all Campus_TLA GCH)

While this may seem daunting, once setup it will be simple to keep in order

An ordered priority cascade exists in the views using a COALESCE sql function

1. Mapped Staff ID code
2. Program-profession mapping
3. Program mapping

The consequence of a new staff member being unmapped is that they will be default map to their program/profession until they are, and if that is not mapped then to the program which will be.

The Products part of the feeder that provides the most manageable number of products is

MINS-<pay level>, with a quantity of minutes. (see options for this)

Group sessions are properly dealt with and shared using the field called DIVISOR.

Where UR’s / MRN’s are not recorded the Talons dummy <x>909090 is used – Mr (or Mrs) Phumph

Data is linked to IP, ED and OP using rules suitable for the fact we have no time for the events recorded. A time of 12:00 midday is used rather than midnight as middle of daylight seemed best. Not sure if AH staff work shifts. Something to think about

Data will be loaded weekly in tblPI5Data routinely in future, an Option on the third party load form exists to pull/refresh a specific period is in place.

Data has been loaded from 1/Jul/2011

As the data is replicated in APP2 Clinics.. no allocation of Allied health Labour and parts should be done in the APP2 Clinic Allied health RVUS – very important point.. we cannot costs the same stuff in two places.

How you have been allocating IP Costs for Allied Health needs to be clearly understood, Allied health distribution on bedday models needs to be considered very carefully if using PI5..
If PI5 is not catching all your inpatients and outpatients.. you may have a problem

Mapping

After import a spreadsheet is created in T:\PI5 (if you have the Talons SQL Client installed and working correctly!!)

The filename will be similar to <TLA>- PI5-DEPT-MAPPINGS-2013-08-04-1408.xls with the date time of the run in there..

It is similar to the table in dept mapping but with THREE extra columns

Use the right three Columns with Excel filters and then paste appropriate values into DEPT Code column. you should  choose to update on the same filter the Record Date

When finished.. filter using the record date column to those you have changed today,  Highlight the columns from Company_TLA to Record Date and the rows you want to paste, right click then copy  them, then  open the dept mapping table in Talons SQL, then right click in the table grid are and choose paste rows..

My suggestion from scratch,

1. filter those with a specific program (not none one program at a time) staff id = 000000 , and professional group  = none then map that to the Allied health Department T2 Dept code say 6 or 8 depts. Update DEPT_CODE and  Update record Date only, then clear filters
2. Filter those with a specific professional group (not none), staff id = 000000 and overlay over any mappings with the default dept for that profession (not Allied health Assistants and nursing staff) one professional group at a time (ignore program) and map to the general Dept for that profession. Update DEPT_CODE and Update record Date only,  then clear filters
3. Filter those with a general professional group like Allied health Assistants and nursing staff then filter one Program at a time (including NONE) and any staff id and map to the T2 Dept. Update DEPT_CODE and  Update record Date only, then clear filters
4. That should be everyone mapped.. however select those unmapped YY9999 / XX9999 / ZZ9999 AND  repeat steps one two and three until you get it right..
5. Identify all your specially funded programs like stroke, outreach to rural hospitals, Community, sport program, school programs  etc
6. Now find a list of staff id s and names and where they are paid from that are paid from - for example the STROKE program
7. Filter one at a time to the staff id or search the description for the name. Update DEPT_CODE and  Update record Date only, then clear filters
8. Okay now finished clear filters then filter to the dates you have entered for record date.. You can select all rows yes..
9. Select from <row two> to bottom and right to include only record date NOT including heading row!!, right click copy
10. Open Dept mapping table, right click, and choose paste rows, then verify and save..

Mapping Payroll ID to Department (Courtesy of Col Roberts)

Until PI5 is enhanced to record where an intervention is funded from, use the following interim workaround.

At DDHHS we had 321 payroll IDs recorded within PI5. This effectively gives us 321 “products” to map to TII departments within TALONS SQL. I consider this a manageable number, given new products will only appear as new staff are appointed. (where staff work at higher duties and different pay levels there may be more products!!)

Use the following DSS view to extract FTEs paid from each cost centre for each Payroll ID during 2012/13.

Click on the link below in order to view 'Measures by Cost Centre Code,Paypoint Code,Employee Code,Employee'
http://10.21.44.160/Panorama/Dashboard/dss/CustomOpenView.asp?webpartguid=19CAD30554-84F5-41F0-B7C7-8776784D0C42|1 

Export this as an Excel.

For each employee, select the cost centre with the maximum FTE, or the one you consider most appropriate to map the product against in TII. Delete any other rows for that payroll ID. Now you have one CC per payroll ID.

You will not find some payroll IDs, as in PI5 an interim ID may be used for new employees and this may never be updated to the actual ID. Similarly students and other unpaid workers will be assigned a dummy ID. If you don’t match to an ID search your payroll download for the employee’s name. If still not found, confirm with your Allied Health directors that this person has not been paid during the period (in our case 2012/13).

Write a Crystal from TII to show your Cost Centre to Department mapping. Export as Excel data only.

Import both these spreadsheets to an MS Access database (or use a VLOOKUP) to tag on the department for each Cost Centre for each payroll ID.

This is your TALONS Dept Code to enter against each payroll ID.

Jims Comments

The consequence of a new staff member being unmapped is that they will be default map to their program/profession until they are, and if that is not mapped then to the program which will be.

If there is a need an option to drag this data into Talons SQL by a strictly formatted Excel spreadsheet or two can be considered..

Development Options

• There are more interesting product that have service delivery, client present, Service location and a whole host of stuff.. but this model created many thousands of products – quite unsuitable to manage, more arriving weekly.

• For those who wish to discriminate Inpatient and Outpatient at the product level so different RVUS can be applied. that is possible – potentially doubling the simple product count
• Views for use in SQL or crystal repoting have been provided <tla>_PI5Data and <tla>_PI5Util, one reflects the data we drag in the other what we do to it to create utilisation. Both have dynamic Talons episode matching and Dept code mapping (e.g. mapping to IP episodes and T2 Dept code is as it is when you run the report not as when loaded) Loaded data is in tblPI5Data and will be visible as <tla>_tableview_PI5Data

Some tips on getting faster queries

Dynamic episode matching and dept mapping requires all records in a table to be tested for the presence of the episode if you ask for an episode.  soooo slow!!!

Using sub queries to limit the scope of the data in which you want to find the episode is much much faster

e.g.
select * from atable where dyn_episode = ‘123456-1’ has to look at all records in atable..

select * from (select * from atable where ur = ‘1234561’) as aa where dyn_episode = ‘123456-1’
limits the examination  to a very small set of records. Indexes are used to find the ones with the correct UR and the table of the sub-query passed back to the outer query. Almost instant.

MSSQL does this optimising for many many queries automagically - but function columns like episode cannot be optimised unless you do it.

The same applies to limiting the query by other criteria like date range.