Exhibit 7 DSDS-BATCH_FIN_EXT_NJSDU~1.pdf

23DPP00838 1 | P a g e

Exhibit 7

BATCH_FIN_EXT_NJSDU

INTRODUCTION

The process BATCH_FIN_EXT_NJSDU creates master file of all cases of NJKiDS. This file includes all open cases with or without order and closed cases with order. This file is for the SDU to be able to correctly identify receipts.

1.1.1 BATCH_FIN_EXT_NJSDU.SP_EXT_NJSDU

1.1.1.1. Purpose

This procedure identifies the cases according to the selection criteria specified below, moves the data to the temporary table and creates an output file to SDU.

1.1.1.2. Database Tables Affected:

1.1.1.2.1. Driver Tables

CASE_DETAILS (CASE)

CASE_MEMBERS (CMEM)

ADDRESS_DETAILS (AHIS)

MEMBER_DEMOGRAPHICS (DEMO)

1.1.1.2.2. Output Tables

EXTRACT_NJSDU

1.1.1.3. Process Description

This procedure identifies the cases, moves the data to the temporary table EXTRACT_NJSDU and creates an output file using the oracle package UTL_FILE to SDU.

Get the run date and last run date from PARM (Parameters table) and validate that the batch program was not executed for the run date by checking the last run date in the PARM Table.

Delete already existing records from the EXTRACT_NJSDU Table.

Read PARM table to determine if the job needs to be run for daily extract or Weekly extract.

DAILY EXTRACT:

Daily EXTRACT:

BATCH_FIN_EXT_NJSDU.SP_EXT_NJSDU

Find all newly added open cases with/without court order and closed cases from CASE that has not previously been sent to SDU. Get the NCP DCN, NCP’s name, NCP’s SSN, CP DCN, CP’s name and CP’s DCN from Member Demographics screen DEMO/DEMO table and Office number, County code from Case Details CASE screen/ Case table to EXTRACT_NJSDU table if the frequency is daily.

Weekly EXTRACT:

BATCH_FIN_EXT_NJSDU.SP_EXT_NJSDU_MASTER

For all open cases with/without court order in CASE, find the NCP and CP from Case member screen CMEM/CMEM table. Get the NCP DCN, NCP’s name, NCP’s SSN, CP DCN, CP’s name and CP’s DCN from Member Demographics screen DEMO/DEMO table, Office number, County code from Case Details CASE screen/ Case table, NCP’s address, CP’s address from Member Address History screen AHIS/AHIS table, and move to the EXTRACT_NJSDU table if the frequency is Weekly.

Create indicator fields for CP/NCP PIN Notice eligibility. Field value will be set to ‘Y’- Yes or ‘N’- No to indicate whether or not the member is eligible. Select NCPs on open Instate cases or Responding Interstate cases or Initiating Interstate cases with referral type Request Registration for Modification. Exclude NCP’s where DT_DECEASED does not equal ’01-JAN-0001’ on VDEMO. Select CP’s

23DPP00838 2 | P a g e from open Instate/Initiating Interstate cases. Exclude CP’s from Responding Interstate cases and from cases where Case Type is either J- Non Federal Foster Care or F- PA-IVE Foster Care. Exclude CP’s where DT_DECEASED does not equal ’01-JAN-0001’ on VDEMO.

Excluded NCP’s from PIN Notice eligibility in the following conditions

23DPP00838 3 | P a g e

If NCP is in CWA Office, where paternity status is Putative Father & Male.

If NCP is in CWA Office where good cause is either Approved or Pending

Create a Header record with an appropriate record type and create date of the file.

Create a Trailer record with an appropriate record type and total count of detail records.

Extract the data from the temporary table EXTRACT_NJSDU and create an output file using UTL_FILE package given by oracle.

Log the Error encountered or successful completion in the BSTL table for future references.

Update the last run date in the PARM table with the run date, upon successful completion.

1.1.1.4. Flow Chart

1.1.1.5. Frequency

Run Daily. On a weekly frequency, a full refresh is done. The daily update will not be scheduled on days when the weekly refresh is scheduled.

1.1.1.6. Dependencies

23DPP00838 4 | P a g e

1.1.1.6.1. Predecessors:

1.1.1.6.2. Successors:

1.1.1.7. Restart Procedures

Start from beginning

1.1.1.8. Instructions if Job is skipped

Job cannot be skipped.

1.1.1.9. Commit Frequency

One time at end of job

1.1.1.10. Exception Threshold

1.1.1.11. Input File(s)

S.No Name of the File File Format Transfer Mechanism File location Estimated size (in MB’s)

1.1.1.11.1. File Layout

1.1.1.12. Output File(s)

S.No Name of the File File Format Transfer Mechanism File location Estimated size (in MB’s)

1 Daily :

EXT_NJSDU_VALIDATION_NEW_CASES.

TXT

Weekly:

EXT_NJSDU_VALIDATION_MASTER.TXT

TXT SFTP - -

1.1.1.12.1. File Layout (Daily)

Field name

Position

Length

Type

C-char D-date

N-number

Format/ Default value

Description

HEADER

TYPE 1-1 1 C ‘H’

CRDATE 2-9 8 D CCYYMMDD Date when this file is generated.

FILE_TYPE 10-12 3 C ‘DLY’ – Daily Extract

FILLER 13-122 110 C Spaces

DETAIL

TYPE 1-1 1 C ‘D’

OFFICE-NO 2-4 3 C Office Number

COUNTY_NUM 5-6 2 C County Code

CASE-ID 7-17 11 C Id Case

FILLER 18-20 3 C Spaces

CP-NAME:

CP-LAST-NAME 21-38 18 C CP’s Last Name

CP-SUFFIX 39-41 3 C CP’s Suffix

23DPP00838 5 | P a g e

CP-FIRST-NAME 42-53 12 C CP’s First Name

CP-MIDDLE-INITIAL 54-54 1 C CP’s Middle Initial

CP-SSN 55-63 9 C CP’s SSN

CP-DCN 64-71 8 C CP’s DCN

NCP-NAME:

NCP-LAST-NAME 72-89 18 C NCP’s Last Name

NCP-SUFFIX 90-92 3 C NCP’s Suffix

NCP-FIRST-NAME 93-104 12 C NCP’s Fist Name

NCP-MIDDLE-INITIAL 105-105 1 C NCP’s Middle Initial

NCP-SSN 106-114 9 C NCP’s SSN

NCP-DCN 115-122 8 C NCP’s DCN

TRAILER:

TYPE 1-1 1 C ‘T’

RECORD-COUNT 2-8 7 N Total number of records

FILLER 9-122 114 C Spaces

Mapping table below shows the tables and columns from where the output file is loaded.

Source Target (MASTER_FILE)

DEFAULT VALUE : ‘D’ TYPE

VCASE.CD_OFFICE OFFICE_NO

VCASE.CD_COUNTY COUNTY_NUM

VCASE.ID_CASE CASE_ID

DEFAULT VALUE : SPACES FILLER

VDEMO.NAME_LAST CP_LAST_NAME

VDEMO.NAME_SUFFIX CP_SUFFIX

VDEMO.NAME_FIRST CP_FIRST_NAME

VDEMO.NAME_MI CP_MIDDLE_INITIAL

VDEMO.MEM_SSN CP_SSN

VCMEM.ID_MEMBER CP_DCN

VDEMO.NAME_LAST NCP_LAST_NAME

VDEMO.NAME_SUFFIX NCP_SUFFIX

VDEMO.NAME_FIRST NCP_FIRST_NAME

VDEMO.NAME_MI NCP_MIDDLE_INITIAL

VDEMO.MEM_SSN NCP_SSN

VCMEM.ID_MEMBER NCP_DCN

23DPP00838 6 | P a g e

1.1.1.12.2 File Layout (Weekly)

Field name

Position

Length

Type

C-char D-date

N-number

Format/ Default value

Description

HEADER

TYPE 1-1 1 C ‘H’

CRDATE 2-9 8 D CCYYMMDD Date when this file is generated.

FILE_TYPE

10-12

C

‘WKY’ – Weekly Extract

FILLER 13-122 110 C Spaces

DETAIL

TYPE 1-1 1 C ‘D’

OFFICE-NO 2-4 3 C Office Number

COUNTY_NUM 5-6 2 C County Code

CASE-ID 7-17 11 C Id Case

FILLER 18-20 3 C Spaces

CP-NAME:

CP-LAST-NAME 21-38 18 C CP’s Last Name

CP-SUFFIX 39-41 3 C CP’s Suffix

CP-FIRST-NAME 42-53 12 C CP’s First Name

CP-MIDDLE-INITIAL 54-54 1 C CP’s Middle Initial

CP-SSN 55-63 9 C CP’s SSN

CP-DCN 64-71 8 C CP’s DCN

NCP-NAME:

NCP-LAST-NAME 72-89 18 C NCP’s Last Name

NCP-SUFFIX 90-92 3 C NCP’s Suffix

NCP-FIRST-NAME 93-104 12 C NCP’s Fist Name

NCP-MIDDLE-INITIAL 105-105 1 C NCP’s Middle Initial

NCP-SSN 106-114 9 C NCP’s SSN

NCP-DCN 115-122 8 C NCP’s DCN

CP-ADDRESS:

CP_ADDR_ATTN 123-162 40 C CP’s Address

CP_ADDR_LINE1 163-212 50 C

CP_ADDR_LINE2 213-262 50 C

CP_ADDR_LINE3 263-312 50 C

CP_ADDR_CITY 313-340 28 C

CP_ADDR_STATE 341-342 2 C

CP_ADDR_ZIP 343-357 15 N

CP_ADDR_COUNTRY 358-359 2 C

NCP-ADDRESS:

NCP_ADDR_ATTN 360-399 40 C NCP’s Address

NCP_ADDR_LINE1 400-449 50 C

NCP_ADDR_LINE2 450-499 50 C

NCP_ADDR_LINE3 500-549 50 C

NCP_ADDR_CITY 550-577 28 C

NCP_ADDR_STATE 578-579 2 C

NCP_ADDR_ZIP 580-594 15 N

NCP_ADDR_COUNTRY 595-596 2 C

PIN NOTICE ELIGIBILITY:

23DPP00838 7 | P a g e

CP_PIN_INDICATOR 597-597 1 C ‘Y’ or ‘N’ CP PIN Notice Indicator

NCP_PIN_INDICATOR 598-598 1 C ‘Y’ or ‘N’ NCP PIN Notice Indicator

TRAILER:

TYPE 1-1 1 C ‘T’

RECORD-COUNT 2-8 7 N Total number of records

FILLER 9-122 114 C Spaces

Mapping table below shows the tables and columns from where the output file is loaded.

23DPP00838 8 | P a g e

Source Target (MASTER_FILE)

DEFAULT VALUE : ‘D’ TYPE

VCASE.CD_OFFICE OFFICE_NO

VCASE.CD_COUNTY COUNTY_NUM

VCASE.ID_CASE CASE_ID

DEFAULT VALUE : SPACES FILLER

VDEMO.NAME_LAST CP_LAST_NAME

VDEMO.NAME_SUFFIX CP_SUFFIX

VDEMO.NAME_FIRST CP_FIRST_NAME

VDEMO.NAME_MI CP_MIDDLE_INITIAL

VDEMO.MEM_SSN CP_SSN

VCMEM.ID_MEMBER CP_DCN

VDEMO.NAME_LAST NCP_LAST_NAME

VDEMO.NAME_SUFFIX NCP_SUFFIX

VDEMO.NAME_FIRST NCP_FIRST_NAME

VDEMO.NAME_MI NCP_MIDDLE_INITIAL

VDEMO.MEM_SSN NCP_SSN

VCMEM.ID_MEMBER NCP_DCN

VAHIS.ADDR_ATTN CP_ADDR_ATTN

VAHIS.ADDR_LINE1 CP_ADDR_LINE1

VAHIS.ADDR_LINE2 CP_ADDR_LINE2

VAHIS.ADDR_LINE3 CP_ADDR_LINE3

VAHIS.ADDR_CITY CP_ADDR_CITY

VAHIS.ADDR_STATE CP_ADDR_STATE

VAHIS.ADDR_ZIP CP_ADDR_ZIP

VAHIS.ADDR_COUNTRY CP_ADDR_COUNTRY

VAHIS.ADDR_ATTN NCP_ADDR_ATTN

VAHIS.ADDR_LINE1 NCP_ADDR_LINE1

VAHIS.ADDR_LINE2 NCP_ADDR_LINE2

VAHIS.ADDR_LINE3 NCP_ADDR_LINE3

VAHIS.ADDR_CITY NCP_ADDR_CITY

VAHIS.ADDR_STATE NCP_ADDR_STATE

VAHIS.ADDR_ZIP NCP_ADDR_ZIP

VAHIS.ADDR_COUNTRY NCP_ADDR_COUNTRY

‘Y’ or ‘N’ based on selection criteria CP_PIN_INDICATOR

‘Y’ or ‘N’ based on selection criteria NCP_PIN_INDICATOR

1.1.1.13. Contact information

Name of the Agency Technical Contact Functional Contact

23DPP00838 9 | P a g e

1.1.1.14. Stored Procedure / Functions matrix

No Stored Procedure Name/ Arguments Nature of the procedure

/ Function Description Output Tables / Screens Applicability

I A U D

1 BATCH_FIN_EXT_NJSDU.SP_EXT_NJSDU Extract Get CP’s Name, SSN, DCN, NCP’s Name, NCP’s SSN, DCN, County code and Office

- VCASE Y - - -

Number VCMEM Y - - - Sorted on Office Number and NCP’s SSN.

Create an output file to SDU. VDEMO Y - - -

EXTRACT_NJSDU Y Y - Y

2 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine Getting Run date - - - - - -

3 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine Getting Last Run date - - - - - -

4 BATCH_COMMON.SP_UPDATE_PARM_DATE Common Routine Updating Last run date - - - - - -

5 BATCH_COMMON.SP_BSTL_LOG Common Routine Inserting the batch status - - - - - -

6 BATCH_COMMON.SP_BATE_LOG Common Routine - - - - - -

7 BATCH_FIN_EXT_NJSDU. Extract Get CP’s Name, SSN, DCN, CP’s Address, - VCASE Y - - - SP_EXT_NJSDU_MASTER CP’s PIN Notice Eligibility, NCP’s Name, NCP’s SSN, DCN, NCP’s Address, NCP’s PIN Notice Eligibility, County code and

VCMEM Y

Office Number Sorted on Office Number and NCP’s SSN.

VDEMO Y - - -

Create an output file to SDU. VAHIS Y - - -

EXTRACT_NJSDU Y Y

- Y