Exhibit 12 DSDS-BATCH_FIN_EXT_BILLING.pdf

23DPP00838

Exhibit 12

BATCH_FIN_EXT_BILLING

INTRODUCTION

The process BATCH_FIN_EXT_BILLING creates the extract of billing coupon file to be sent to the NCP.

1.1.1 BATCH_FIN_EXT_BILLING.SP_EXT_BILLING

1.1.1.1. Purpose

This process creates the extract of billing coupon file to be sent to NCP. NCP suppressed from the billing coupon are excluded from this process

1.1.1.2. Database Tables Affected:

1.1.1.2.1. Driver Tables

VCMEM

VCASE

VDEMO

VAHIS

VBSUP

VSORD

VLSUP

VOBLE

VMDET

VICAS

VCTHB

VDMJR

1.1.1.2.2. Output Tables

EXTRACT_BILLING

1.1.1.3. Process Description

Get the run date and last run date from VPARM table and validate that the batch program was not executed for the run date by checking the last run date in the VPARM table. Otherwise, an error message to that effect will be written into VBSTL table and terminate the process.

Delete the records in the temporary table EXTRACT_BILLING using Oracle Truncate.

Eligible Cases for the billing process are determined as below

If the billing process is part of the weekly schedule then select NCP associated with cases created after the previous run date of the weekly batch billing process.

FROM VCASE

CD_STATUS_CASE =’O’ -- Open DT_BEG_VALIDITY >= Last Run date from VPARM for the weekly billing batch.

Exclude NCPs who have been deceased (Check it in VDEMO for the member whether the DT_DECEASED is filled)

Exclude NCPs who have been incarcerated or institutionalized as specified in the VMDET table using the following conditions.

Exclude the cases from being selected for billing coupons to the NCP when the address status on AHIS is ‘end dated’ or ‘confirmed bad’. Coupon books should be mailed to only one ‘confirmed good non end dated address’ per member. Pending addresses should not be included for coupon mailing.

FROM VMDET

ID_MEMBER = ID_MEMBER from VCMEM

Run Date BETWEEN DT_INSTITUTIONALIZED AND DT_RELEASE

Or

Run Date BETWEEN DT_INCARCERATION AND DT_RELEASE

Check the VCASE table whether it is an open instate case or open responding Interstate case or open Initiating Interstate case with referral type ‘Request Registration of Foreign Support Order for Modification’.

FROM VCASE A, VICAS B

A.ID_CASE = VCMEM.ID_CASE

A.CD_STATUS_CASE = ‘O’ -- Open A.CD_RESPOND_INIT Not = ‘I’ – Initiating interstate case

(A.CD_RESPOND_INIT = ‘I’ – Initiating interstate case

AND B.ID_CASE = A.ID_CASE

AND B.CD_FUNCTION = ‘ENF’ -- Enforcement AND B.CD_STATUS = ‘A’ --Active

AND B.DT_END_VALIDITY = ’31-DEC-9999’

AND EXISTS

(SELECT 1

FROM VCTHB C

WHERE B.ID_CASE = C.ID_CASE

AND B.CD_OTH_STATE_FIPS = C.CD_OTH_STATE_FIPS

AND C.IND_IO_DIRECTION = ‘O’ -- Outbound

AND C.CD_FUNCTION = ‘ENF’-- Enforcement

AND C.CD_ACTION = ‘R’ -- Request

AND C.CD_REASON IN

(ERMEE’,-- Request Registration of a Foreign Support Order for Modification and Enforcement by State IV-D Agency

’ERMEM’,-- Request Registration of a Foreign Support Order for Modification and Enforcement by Oblige ‘ERMEO’,-- Request Registration of a Foreign Support Order for Modification and Enforcement by

Obligor

‘ERFSO’, -- Request Registration of a

Foreign Support Order for Modification only by Obligee

‘ERFSM’, -- Request Registration of a

Foreign Support Order for Modification only by Obligor

‘ERFSS’ -- Request Registration of a Foreign

Support Order for Modification only by the

State IV-D agency)

AND C.DT_TRANSACTION =

(SELECT MAX (T.DT_TRANSACTION)

FROM VCTHB T

CD_OTH_STATE_FIPS

IND_IO_DIRECTION

C.CD_FUNCTION

WHERE T.ID_CASE = C.ID_CASE

AND T.CD_OTH_STATE_FIPS = C.

AND T.IND_IO_DIRECTION = C.

AND T.CD_FUNCTION =

AND T.CD_ACTION = C.CD_ACTION

AND T.CD_REASON = C.CD_REASON)

AND NOT EXISTS

(SELECT 1

FROM VCTHB H

WHERE H.ID_CASE = C.ID_CASE

AND H.CD_OTH_STATE_FIPS = C.CD_OTH_STATE_FIPS

AND H.IND_IO_DIRECTION = ‘I’ -- Inbound

AND H.CD_FUNCTION=‘ENF’

--Enforcement

AND H.CD_ACTION = ‘A’ -- Active

AND H.CD_REASON = ‘ANOAD’

-- Acknowledgement, No further information needed

AND H.DT_TRANSACTION >

C.DT_TRANSACTION

AND H.DT_TRANSACTION =

(SELECT MAX (T.DT_TRANSACTION)

FROM VCTHB T

WHERE T.ID_CASE = H.ID_CASE

AND T.CD_OTH_STATE_FIPS =

H.CD_OTH_STATE_FIPS

AND T.IND_IO_DIRECTION =

H.IND_IO_DIRECTION

AND T.CD_FUNCTION = H.CD_FUNCTION

AND T.CD_ACTION = H.CD_ACTION))))

Include the cases only if those are charging and/or have arrears balance

Check whether the case is charging in the VOBLE table using the following conditions (Existence in VOBLE table).

FROM VOBLE

ID_CASE = NCP’s Case ID

DT-RUN BETWEEN DT_BEG_OBLIGATION AND DT_END_OBLIGATION

DT_END_VALIDITY = ’31-DEC-9999’

Check whether the case is having arrears using the common routine by passing the Id case and month.

Exclude Cases having billing suppression by selecting NCP not having Suppression information specified in VBSUP table (Existence in the table for the case).

FROM VBSUP

ID_CASE = Id Case of the NCP

RUN DATE BETWEEN DT_BEGIN AND DT_END

Cases which are having active Income withholding are excluded. Check it in VDMJR using the following conditions.

NOT EXISTS (SELECT 1 FROM VDMJR

WHERE ID_CASE = ID_CASE

AND CD_ACTIVITY_MAJOR IN (‘IMIW’ -- IMMEDIATE INCOME WITHHOLDING,

‘INIW’ -- INITIATED INCOME WITHHOLDING)

AND CD_STATUS = ‘STRT’ -- Start)

Cases which are having active QDRO/EDRO activity chain are excluded. Check it in VDMJR using the following conditions.

AND CD_ACTIVITY_MAJOR = ‘QDRO’ -- QUALIFIED / ELIGIBLE DOMESTIC RELATIONS

ORDER

Skip the case if it is in pending closure. Check it in VDMJR using the following conditions.

AND CD_ACTIVITY_MAJOR IN

(‘CWCL’ – CWA Case Closure, ‘PBCL’ – Probation Case Closure, ‘FACL’ – Family Case Closure)

In addition to the above cases, Include cases for which billing is initiated by the case worker using the “Billing Override” option in the VBHIS table

For all the eligible cases, details to be printed on the bill are extracted as mentioned below

Select the Case Worker No and Office No from the VCASE table by joining the id case.

Select the name of the Client (CP) associated with the case, from the VDEMO by joining the CP’s member id. Get the CP’s member id from VCMEM table using the following conditions

FROM VCMEM

ID_CASE = Id case of the NCP

CD_RELATION_CASE = ‘C’ – CP

CD_STATUS_CASE_MBR = ‘A’ – Active

Select the name of the NCP associated with the case, from the VDEMO by joining the NCP’s member id

Get the NCP’s address using the address hierarchy (Common routine) from VAHIS table. It should satisfy the following conditions:

NCP’s address should not be end dated dt_end > pd_dt_run

Address status should be confirmed good cd_status = ‘Y’ cd_type_address = ‘M’

Select the obligation effective date and the frequency for the case from the VOBLE table. If there are more than one obligation in the case, then the minimum effective date is selected

MIN(DT_BEG_OBLIGATION), CD_FREQUENCY FROM VOBLE for the following conditions.

Following Bill Amount for the Case is selected from the VLSUP table

Arrears balance (Calculated in using the common routine by passing the Id case and Month) Support obligation amount due per ordered frequency

Arrears payback amount due per ordered frequency

If arrears are equal to or less than zero, payback amount will be treated as zero

After all the information is selected for a case ID, insert a record into VBHIS table using the following mapping

Source Target

ID Case of the NCP ID_CASE

Run Date DT_STATEMENT

Current support amount from VOBLE AMT_CURRENT_SUPPORT

Arrears payback amount from VOBLE AMT_EXPECT_TO_PAY

Total Arrears from VLSUP AMT_ARREARS

Unique number generated by the system SEQ_EVENT_GLOBAL

Extract the data from the table VBHIS table and any statewide messages for the period from the MESSAGES table, and create an output file using the oracle UTL_FILE package.

Log any errors or successful completion in the VBSTL table for future reference. Abort the job if any error occurs. The correctness of the header and trailer records is checked, along with basic file layout.

Update Last Run date in the VPARM table with the run date, upon successful completion.

1.1.1.4. Flow Chart

1.1.1.5. Frequency

Weekly

1.1.1.6. Dependencies

1.1.1.6.1. Predecessors:

N/A

1.1.1.6.2. Successors:

1.1.1.7. Restart Procedures

Restart the job 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

Zero

1.1.1.11. Input File(s)

23DPP00838 10 | P a g e

1.1.1.12. O

u t p u t

F i l e s

23DPP00838 11 | P a g e

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

1 EXT_BILLING_COUPONS_NEW_CA

SES.TXT

- FTP - -

File Layout

Field name

Position

Length

Type

C-Char

D-Date

N-Number

Format/

Default value

Description

HEADER RECORD

REC-TYPE 001-001 1 C ‘A’ Header record type

CREATION_DATE 002-009 8

CCYYMMDD Header Date

FILLER 010-399 390 C Spaces

DETAIL-RECORD

REC-TYPE 001-001 1 C ‘B’ Record type

OFFICE-NO 002-004 3 C

Office number

CASE-ID 005-015 11 C

Case Id

CLIENT-LAST-NAME 016-035 20 C

Client last name

CLIENT-SUFFIX 036-039

C

Client suffix

CLIENT-FIRST-NAME 040-054 15 C

Client first name

CLIENT-MIDDLE-INIT 055-055 1 C

Client middle initial

NCP-LAST-NAME

056-075

NCP last name

NCP –SUFFIX

076-079

NCP suffix

NCP – FIRST-NAME

080-094

NCP first name

NCP – MIDDLE-INIT 095-095 1 C

NCP middle initial

ATTN/CARE OF

096-135

Person or Entity through whom the coupon is delivered to the

NCP.

NCP – STREET-1

136-185

NCP address line 1

NCP – STREET-2

186-235

NCP address line 2

NCP – CITY

236-263

NCP address city

NCP – STATE

264-265

NCP address state

NCP – ZIP

266-274

NCP address zip code

NCP – COUNTRY

275-276

NCP address country code

EFT-DATE 277-284 8 C CCYYMMDD Coupon effective date

FREQUENCY

285-285

M – Monthly

W – Weekly

B – Bi-Weekly

S – Semi-Monthly

(Court ordered frequency)

CD_COUNTY 286-287 2 C

County Code

23DPP00838 12 | P a g e

VCMEM

VCASE

NAME_COUNTY 288-357 70 C

County Name

AMT_ARREARS 358-368 11 N

Total arrears amount

AMT_OBLIG

369-379

N

Sum of all obligation amount and Arrears Payback amount

If Case has multiple frequencies, then convert the amount with the least frequency.

FILLER 380-399 20 C Spaces

TRAILER-RECORD

REC-TYPE 001-001 1 C ‘C’ Record type

CREATION_DATE 002-009 8 C CCYYMMDD Trailer record date

REC_COUNT 010-016 7 N

Master Record count

BILLING_MSG1 017-081 65 C

Billing message 1

BILLING_MSG2 082-146 65 C

Billing message 2

FILLER 147-399 253 C Spaces

1.1.1.13. Contact information

Name of the Agency Technical Contact Functional Contact

New SDU Vendor - -

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_BILLING.SP_EXT_BILLING Extract Extract the billing coupon information into a - VCMEM Y - - -file VOBLE Y - - -

VLSUP Y - - -

VCASE Y - - -

VDEMO Y - - -

VBSUP Y - - -

VSORD Y - - -

VAHIS Y - - -

EXTRACT_BILLING - Y - Y

VMDET

VICAS Y - - -

VCTHB Y - - -

VDMJR Y - - -

Y - - -

2 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine Get Run date and Last run date - - - - - -

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

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

5 BATCH_COMMON.SP_EXTRACT_DATA Common Routine Extracts the data from table into a file - - - - - -

6 BATCH_COMMON.FN_GET_ARREARS Common Routine To get the case arrears - - - - - -

7 BATCH_COMMON.SP_GET_ADDRESS Common

Routine

Get the Member’s address using the address hierarchy

1.1.2 BATCH_FIN_EXT_BILLING.SP_EXT_BILLING_MASTER

1.1.2.1 Purpose

This process creates the extract of billing coupon file to be sent to NCP. NCP suppressed from the billing coupon are excluded from this process

1.1.2.2 Database Tables Affected:

1.1.2.5.1. Driver Tables

23DPP00838 13 | P a g e

VDEMO

VAHIS

VBSUP

VSORD

VLSUP

23DPP00838 14 | P a g e

VOBLE

VMDET

VICAS

VCTHB

VDMJR

1.1.2.5.2. Output Tables

EXTRACT_BILLING_MASTER

1.1.2.6 Process Description

Get the run date and last run date from VPARM table and validate that the batch program was not executed for the run date by checking the last run date in the VPARM table. Otherwise, an error message to that effect will be written into VBSTL table and terminate the process.

Delete the records in the temporary table EXTRACT_BILLING_MASTER using Oracle Truncate.

Eligible Cases for the billing process are determined as below

If the billing process is part of the quarterly schedule select all the NCPs in the system from the VCMEM table.

CD_RELATION_CASE = ‘A’ – NCP

Exclude NCPs who have been deceased (Check it in VDEMO for the member whether the DT_DECEASED is filled)

Exclude NCPs who have been incarcerated or institutionalized as specified in the VMDET table using the following conditions.

FROM VMDET

ID_MEMBER = ID_MEMBER from VCMEM Run Date BETWEEN DT_INSTITUTIONALIZED AND DT_RELEASE

Run Date BETWEEN DT_INCARCERATION AND DT_RELEASE

Check the VCASE table whether it is an open instate case or open responding Interstate case or open Initiating Interstate case with referral type ‘Request Registration of Foreign Support Order for Modification’.

FROM VCASE A, VICAS B

A.ID_CASE = VCMEM.ID_CASE

A.CD_STATUS_CASE = ‘O’ -- Open

A.CD_RESPOND_INIT Not = ‘I’ – Initiating interstate case

(A.CD_RESPOND_INIT = ‘I’ – Initiating interstate case

AND B.ID_CASE = A.ID_CASE

AND B.CD_FUNCTION = ‘ENF’ -- Enforcement AND B.CD_STATUS = ‘A’ --Active

AND B.DT_END_VALIDITY = ’31-DEC-9999’

AND EXISTS

23DPP00838 15 | P a g e

(SELECT 1

FROM VCTHB C

WHERE B.ID_CASE = C.ID_CASE

AND B.CD_OTH_STATE_FIPS = C.

CD_OTH_STATE_FIPS

AND C.IND_IO_DIRECTION = ‘O’ -- Outbound

AND C.CD_FUNCTION = ‘ENF’-- Enforcement

AND C.CD_ACTION = ‘R’ -- Request

AND C.CD_REASON IN

(ERMEE’,-- Request Registration of a Foreign Support Order for

Modification and Enforcement by State IV-D Agency

’ERMEM’,-- Request Registration of a Foreign Support Order for

Modification and Enforcement by Oblige

‘ERMEO’,-- Request Registration of a Foreign Support Order for Modification and Enforcement by Obligor

‘ERFSO’, -- Request Registration of a

Foreign Support Order for Modification only by Obligee

‘ERFSM’, -- Request Registration of a

Foreign Support Order for Modification only by Obligor

‘ERFSS’ -- Request Registration of a Foreign

Support Order for Modification only by the

State IV-D agency)

AND C.DT_TRANSACTION =

(SELECT MAX (T.DT_TRANSACTION)

FROM VCTHB T

WHERE T.ID_CASE = C.ID_CASE

AND T.CD_OTH_STATE_FIPS =

C.CD_OTH_STATE_FIPS

AND T.IND_IO_DIRECTION = C.IND_IO_DIRECTION

AND T.CD_FUNCTION = C.CD_FUNCTION

AND T.CD_ACTION = C.CD_ACTION

AND T.CD_REASON = C.CD_REASON)

AND NOT EXISTS

(SELECT 1

FROM VCTHB H

WHERE H.ID_CASE = C.ID_CASE

AND H.CD_OTH_STATE_FIPS = C.CD_OTH_STATE_FIPS

AND H.IND_IO_DIRECTION = ‘I’ -- Inbound

AND H.CD_FUNCTION=‘ENF’

--Enforcement

AND H.CD_ACTION = ‘A’ -- Active

AND H.CD_REASON = ‘ANOAD’

-- Acknowledgement, No further information needed

AND H.DT_TRANSACTION >

C.DT_TRANSACTION

AND H.DT_TRANSACTION =

(SELECT MAX (T.DT_TRANSACTION)

FROM VCTHB T

WHERE T.ID_CASE = H.ID_CASE

AND T.CD_OTH_STATE_FIPS =

AND T.IND_IO_DIRECTION =

H.CD_OTH_STATE_FIPS

H.IND_IO_DIRECTION

CD_FUNCTION

AND T.CD_FUNCTION = H.

AND T.CD_ACTION = H.CD_ACTION))))

Include the cases only if those are charging and/or have arrears balance

Check whether the case is charging in the VOBLE table using the following conditions (Existence in VOBLE table).

FROM VOBLE

23DPP00838 16 | P a g e

C th.

h e c k w h e t h e r t h e c a s e i s h a v i n g a r r e a r s u s i n g t h e c o m m o n r o u t i n e b y p a s s i n g t h e

I d c a s e a n d m o n

23DPP00838 17 | P a g e

Exclude Cases having billing suppression by selecting NCP not having Suppression information specified in VBSUP table (Existence in the table for the case).

FROM VBSUP

ID_CASE = Id Case of the NCP

RUN DATE BETWEEN DT_BEGIN AND DT_END

Cases which are having active Income withholding are excluded. Check it in VDMJR using the following conditions.

AND CD_ACTIVITY_MAJOR IN (‘IMIW’ -- IMMEDIATE INCOME WITHHOLDING,

‘INIW’ -- INITIATED INCOME WITHHOLDING)

Cases which are having active QDRO/EDRO activity chain are excluded. Check it in VDMJR using the following conditions.

AND CD_ACTIVITY_MAJOR = ‘QDRO’ -- QUALIFIED / ELIGIBLE DOMESTIC RELATIONS

ORDER

Skip the case if it is in pending closure. Check it in VDMJR using the following conditions.

AND CD_ACTIVITY_MAJOR = ‘CASE’ – Case Closure

AND CD_ACTIVITY_MAJOR IN

(‘CWCL’ – CWA Case Closure, ‘PBCL’ – Probation Case Closure, ‘FACL’ – Family Case Closure)

In addition to the above cases, Include cases for which billing is initiated by the case worker using the “Billing Override” option in the VBHIS table

For all the eligible cases, details to be printed on the bill are extracted as mentioned below

Select the Case Worker No and Office No from the VCASE table by joining the id case.

Select the name of the Client (CP) associated with the case, from the VDEMO by joining the CP’s member id. Get the CP’s member id from VCMEM table using the following conditions

23DPP00838 18 | P a g e

ID_CASE = Id case of the NCP

23DPP00838 19 | P a g e

CD_RELATION_CASE = ‘C’ – CP

Select the name of the NCP associated with the case, from the VDEMO by joining the NCP’s member id

Get the NCP’s address using the address hierarchy (Common routine) from VAHIS table. It should satisfy the following conditions:

NCP’s address should not be end dated dt_end > pd_dt_run

Address status should be confirmed good cd_status = ‘Y’ cd_type_address = ‘M’

Select the obligation effective date and the frequency for the case from the VOBLE table. If there are more than one obligation in the case, then the minimum effective date is selected

MIN(DT_BEG_OBLIGATION), CD_FREQUENCY FROM VOBLE for the following conditions.

Following Bill Amount for the Case is selected from the VLSUP table

Arrears balance (Calculated in using the common routine by passing the Id case and Month) Support obligation amount due per ordered frequency

Arrears payback amount due per ordered frequency

If arrears are equal to or less than zero, payback amount will be treated as zero

After all the information is selected for a case ID, insert a record into VBHIS table using the following mapping

Source Target

ID Case of the NCP ID_CASE

Run Date DT_STATEMENT

Current support amount from VOBLE AMT_CURRENT_SUPPORT

Arrears payback amount from VOBLE AMT_EXPECT_TO_PAY

Total Arrears from VLSUP AMT_ARREARS

Unique number generated by the system SEQ_EVENT_GLOBAL

Extract the data from the table VBHIS table and any statewide messages for the period from the MESSAGES table, and create an output file using the oracle UTL_FILE package.

Log any errors or successful completion in the VBSTL table for future reference. Abort the job if any error occurs. The correctness of the header and trailer records is checked, along with basic file layout.

23DPP00838 20 | P a g e

Update Last Run date in the VPARM table with the run date, upon successful completion.

23DPP00838 21 | P a g e

1.1.2.4 Flow Chart

1.1.2.5 Frequency

Quarterly

1.1.2.6 Dependencies

1.1.2.6.1. Predecessors:

1.1.2.6.2. Successors:

1.1.2.7 Restart Procedures

Restart the job from beginning

1.1.2.8 Instructions if Job is skipped

Job cannot be skipped.

1.1.2.9 Commit Frequency

One time at end of job

1.1.2.10 Exception Threshold

Zero

23DPP00838 22 | P a g e

1.1.2.11 Input File(s)

23DPP00838 23 | P a g e

1.1.2.12 Output File(s)

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

1 EXT_BILLING_COUPONS_MASTER.

TXT

- FTP - -

File Layout

Field name

Position

Length

Type

C-Char

D-Date

N-Number

Format/

Default value

Description

HEADER RECORD

REC-TYPE 001-001 1 C ‘A’ Header record type

CREATION_DATE 002-009 8

CCYYMMDD Header Date

FILLER 010-399 390 C Spaces

DETAIL-RECORD

REC-TYPE 001-001 1 C ‘B’ Record type

OFFICE-NO 002-004 3 C

Office number

CASE-ID 005-015 11 C

Case Id

CLIENT-LAST-NAME 016-035 20 C

Client last name

CLIENT-SUFFIX 036-039

Client suffix

CLIENT-FIRST-NAME 040-054 15 C

Client first name

CLIENT-MIDDLE-INIT 055-055 1 C

Client middle initial

NCP-LAST-NAME

056-075

NCP last name

NCP –SUFFIX

076-079

NCP suffix

NCP – FIRST-NAME

080-094

NCP first name

NCP – MIDDLE-INIT 095-095 1 C

NCP middle initial

ATTN/CARE OF

096-135

Person or Entity through whom the coupon is delivered to the

NCP.

NCP – STREET-1

136-185

NCP address line 1

NCP – STREET-2

186-235

NCP address line 2

NCP – CITY

236-263

NCP address city

NCP – STATE

264-265

NCP address state

NCP – ZIP

266-274

NCP address zip code

NCP – COUNTRY

275-276

NCP address country code

EFT-DATE 277-284 8 C CCYYMMDD Coupon effective date

FREQUENCY

285-285

M – Monthly

W – Weekly

B – Bi-Weekly

S – Semi-Monthly

(Court ordered frequency)

CD_COUNTY 286-287 2 C

County Code

23DPP00838 24 | P a g e

NAME_COUNTY 288-357 70 C

County Name

AMT_ARREARS 358-368 11 N

Total arrears amount

AMT_OBLIG

369-379

N

Sum of all obligation amount and Arrears Payback amount

If Case has multiple frequencies, then convert the amount with the least frequency.

FILLER 380-399 20 C Spaces

TRAILER-RECORD

REC-TYPE 001-001 1 C ‘C’ Record type

CREATION_DATE 002-009 8 C CCYYMMDD Trailer record date

REC_COUNT 010-016 7 N

Master Record count

BILLING_MSG1 017-081 65 C

Billing message 1

BILLING_MSG2 082-146 65 C

Billing message 2

FILLER 147-399 253 C Spaces

1.1.2.13 Contact information

Name of the Agency Technical Contact Functional Contact

New SDU Vendor - -

1.1.2.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_BILLING. Extract Extract the billing coupon information into a - VCMEM Y - - -

SP_EXT_BILLING_MASTER file VOBLE Y - - -

VLSUP Y - - -

VCASE Y - - -

VDEMO Y - - -

VBSUP Y - - -

VSORD Y - - -

VAHIS Y - - -

EXTRACT_BILLING - Y - Y

_MASTER

VMDET

VICAS Y - - -

VCTHB Y - - -

VDMJR Y - - -

Y - - -

2 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine Get Run date and Last run date - - - - - -

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

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

5 BATCH_COMMON.SP_EXTRACT_DATA Common Routine Extracts the data from table into a file - - - - - -

6 BATCH_COMMON.FN_GET_ARREARS Common Routine To get the case arrears - - - - - -

7 BATCH_COMMON.SP_GET_ADDRESS Common

Routine

Get the Member’s address using the address hierarchy