Module Output

SQL:[IDI_Community].[chld_reports_of_concern].[reports_of_concern_YYYYMM]
SAS: libname cm_ot ODBC dsn=idi_community_srvprd schema=chld_reports_of_concern; run ; proc print data = cm_ot.reports_of_concern_YYYYMM; run ;
How to access a code module in the Data Lab:Read here
All workshop recordings : Find here

Purpose

This module creates tables which will replicate general reporting counts and can be used to identify the individuals and volume of notifications to Oranga Tamariki for Care and Protection (CNP) and Youth Justice (YJ) issues from various notifiers for analysis. It will distinguish those notifications in the Care and Protection (CNP) system which are deemed to be about a legitimate
potential care and protection issue and so become what is known as a Report of Concern (ROC) which once assessed may proceed further through the system if deemed that Further Action Required (FAR) was needed by the organisation.

The Report of Concern (ROC) is a critical component of child protection system and helps support understanding of children and young people at risk.

Key Concepts

The Report of Concern (ROC) is a first step to identify potential cases of child abuse and neglect and provide child protection services to individual children and young people by child welfare agencies like Oranga Tamariki.

Under s15 of the Oranga Tamariki Act 1989, any person who believes that any child or young person has been, or is likely to be harmed (whether physically, emotionally, or sexually), ill-treated, abused, neglected, or deprived may notify the matter to Oranga Tamariki. Many people make a notification to Oranga Tamariki through the national contact centre, though a small proportion contact the sites directly.

When a notification is received, Oranga Tamariki starts an intake decision response and will then determine the best course of action to protect te tamaiti from further harm. The intake process is a multi-stage process which represents several explicit and implicit decision points, including at the national contact centre and at the site.

A notification can be made for one or more children or young people. If one child from a family is reported, the social worker will also collect information and look at any care and protection concerns for other siblings in the family as well.

If it is determined that a notification highlights a potential legitimate care and protection issue, the notification is taken on as a Report of Concern (ROC).
Alternatively, a notification may not become a Report of Concern for a variety of reasons, such as there being no substance to the report, not being in relation to a care and protection concern, or the report not meeting the threshold for further assessment by Oranga Tamariki. The notifications that don’t become “Report of Concern” will be filtered out by “[cyf_clean].[cyf_intakes_details].[cyf_ind_cnp_notification_ind] = ‘Y’”.

A ROC can have two major outcomes that includes Further Action Required (FAR) and No Further Action Required (NFA). Reports of Concern after initial assessment deemed as requiring further investigation or intervention by Oranga Tamariki are assigned the outcome of Further Action Required whereas those that do not are assigned the No Further Action Required outcome (sometimes these involve referral to another service listed in the detailed outcome type).

Generally most Reports of Concern with a Further Action Required outcome will proceed to a Investigation/Child and Family Assessment event.

Comparison against other sources

1. Comparison between the code module output and the report here https://www.orangatamariki.govt.nz/about-us/information-releases/statistics-about-how-we-work-with-children/.
   They are similar in terms of
   • the total number of ROC by financial years.
   • the total number of distinct people who had ROC by financial years.
   • the total number of FAR by financial years.
   • the total number of distinct people who had FAR by financial years.

References & Contacts

Statistics about how we work with children | Oranga Tamariki | Ministry for Children
Analysis of the decrease of Reports of Concern | Oranga Tamariki | Ministry for Children
Complexity of tamariki interacting with Oranga Tamariki reports of concern compared to entries to care | Oranga Tamariki | Ministry for Children

Community of Interest

Module Business Rules

To select between the care and protection system we use this variable: cyf_ind_business_area_type_code: Business area owning this event for example: CNP = Care and Protection, YJU = Youth Justice. The business area code refers to the type of business area the notification section falls under, care and protection or youth justice.

cyf_ine_event_type_wid : The value will be always 2 for this intake event.
This variable denote the event type which consistently holds the value 2.
It is essential to recognize that when linking this table with other event related tables the event types hold different value, which will allow us to explore a broader range of event types for comprehensive data analysis.

Code module technical information

Open Issues/Comments

There are many internal and external factors behind the variation in number of ROC we receive each year, including:

• public awareness about child abuse, and the potential signs of abuse or neglect.
• the actual rates of child abuse and domestic violence.
• the overall public sentiment about Oranga Tamariki.
• a practice change to the process that we assess a potential ROC.
• Oranga Tamariki’s shift into a community-led approach with more focus on early prevention.
• an increasing coordinated response across government agencies to child abuse.
• changes in visibility of children due to changes in wider environment, such as COVID.

Researchers should also note that, data extracted from our operational case management system records are subject to change as more information becomes available or lagged data entry occurs.
As a result figures produced from this operational data may sometimes differ from official published results or reports to some degree as those were based on the time the data was extracted and collated.

Parameters

The following parameters should be supplied to this module to run it in the database:

1. {targetdb}: The SQL database on which the spell datasets are to be created.
2. “{idicleanversion}”: The IDI Clean version that the spell datasets need to be based on.
3. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
4. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that have the same name.

Dependencies

1. {idicleanversion}.[cyf_clean].cyf_intakes_event; This table holds intakes events for the integrated person view and it is a fact table. Each client may have multiple rows for same event happening in different time frame.
2. {idicleanversion}.[cyf_clean].cyf_intakes_details; This table holds details about the intake events and it is a dimension table.

Outputs

[IDI_Community].[cm_read_OT_REPORTS_OF_CONCERN].[ot_reports_of_concern_YYYYMM]

Variable Descriptions

* = required columns

Module Version & Change History

Code

:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"
/* :setvar idicleanversion "IDI_Clean_202410" */
:setvar idimetaversion "{idimetaversion}"

/* Combine the two tables [cyf_intakes_event] and [cyf_intakes_details]*/

drop table if exists #cyf_combined;

select t1.[snz_uid]
      ,t1.[snz_msd_uid]
      ,t1.[cyf_ine_event_type_wid_nbr]
      ,t1.[snz_composite_event_uid]
      ,t1.[snz_systm_prsn_uid]
      ,t1.[cyf_ine_source_uk_var1_text]
      ,t1.[cyf_ine_source_uk_var2_text]
      ,t1.[cyf_ine_source_uk_var3_text]
      ,t1.[cyf_ine_source_uk_var4_text]
      ,t1.[cyf_ine_event_from_datetime]
      ,t1.[cyf_ine_event_to_datetime]
      ,t1.[cyf_ine_event_from_date_wid_date]
      ,t1.[cyf_ine_event_to_date_wid_date]
      ,t1.[cyf_ine_number_of_days_nbr]
      ,t1.[cyf_ine_direct_daily_nett_amt]
      ,t1.[cyf_ine_direct_daily_gross_amt]
      ,t1.[cyf_ine_indirect_daily_nett_amt]
      ,t1.[cyf_ine_indirect_daily_gross_amt]
      ,t1.[cyf_ine_count_nbr]
      ,t1.[cyf_ine_extracted_datetime]
      ,t2.[cyf_ind_extracted_datetime]
      ,t2.[cyf_ind_fgc_referral_ind]
      ,t2.[cyf_ind_cnp_notification_ind]
      ,t2.[cyf_ind_business_area_type_code]
      ,t2.[cyf_ind_intake_type_code]
      ,t2.[cyf_ind_final_urgency_type_code]
      ,t2.[cyf_ind_final_outcome_actn_code]
      ,t2.[cyf_ind_final_outcome_type_code]
      ,t2.[snz_crnt_org_unit_uid]
      ,t2.[snz_rcvd_org_unit_uid]
      ,t2.[snz_refrd_org_unit_uid]
      ,t2.[cyf_ind_intake_ref_status_code]
      ,t2.[cyf_ind_contact_method_code]
      ,t2.[cyf_ind_notifier_role_type_code]
      ,t2.[snz_cyf_social_work_phase_uid]
	into #cyf_combined
  from [$(idicleanversion)].[cyf_clean].[cyf_intakes_event] as t1
  left join [$(idicleanversion)].[cyf_clean].[cyf_intakes_details] as t2
  on t1.[snz_composite_event_uid] = t2.[snz_composite_event_uid]
;

drop table if exists #cyf_combined_ind;

/* Create indicators */

select 
		[snz_uid],
		[snz_composite_event_uid],
		convert(date, [cyf_ine_event_from_datetime]) as event_date,
		datepart(year, [cyf_ine_event_from_datetime]) as year,
		[cyf_ind_contact_method_code],
		case 
			when datepart(month, [cyf_ine_event_from_datetime]) >= 7 then datepart(year, [cyf_ine_event_from_datetime]) + 1
			else datepart(year, [cyf_ine_event_from_datetime])
		end as Fyear,
		case
			when [cyf_ind_business_area_type_code] = 'CNP' then 1
			else 0
		end as ind_cnp_intake,
		case 
			when [cyf_ind_business_area_type_code] = 'YJU' then 1
			else 0
		end as ind_yju_intake,
		case 
			when [cyf_ind_business_area_type_code] = 'CNP' and [cyf_ind_cnp_notification_ind] = 'Y' then 1
			else 0
		end as ind_cnp_ROC,
		case
			when [cyf_ind_business_area_type_code] = 'CNP' and [cyf_ind_cnp_notification_ind] = 'Y' and [cyf_ind_final_outcome_actn_code] = 'FAR' then 1
			else 0
		end as ind_cnp_FAR,
		[cyf_ind_notifier_role_type_code] as notifier_role_type_code,
		[cyf_ind_final_outcome_actn_code],
		[cyf_ind_final_outcome_type_code]
	into #cyf_combined_ind
	from #cyf_combined
;

 select 
 *
 from #cyf_combined_ind