npep NCP

PROCESS LOGIC NARRATIVE

Identify the NCPs participating in NPEP, whose Referral date is > 04/01/2006 regardless of the Case status and Program type is ‘W’ or ‘L’.

Read the Pram file for the dates and if it is empty
get the Batch cycle date form the Table VBATCH_CYCLE_CTRL

EXEC SQL
SELECT BATCH_CYCLE_DT
INTO :DCLVBATCH-CYCLE-CTRL.BATCH-CYCLE-DT
FROM VBATCH_CYCLE_CTRL
WITH UR
END-EXEC

Get the Batch cycle date from VBATCH_CYCLE_CTRL Table and

The Driver cursor..

EXEC SQL
DECLARE NPEP_CUR CURSOR WITH HOLD FOR
SELECT D.JURISD_CD
,D.JURISD_NAM
,A.CASE_NUM
,A.CASE_STATUS
,A.AP_IRN
,COALESCE((RTRIM(C.CL_LAST_NAME) CONCAT ', '
CONCAT RTRIM(C.CL_FIRST_NAME) CONCAT ' '
CONCAT RTRIM(C.CL_MIDDLE_NAME)),' ')
AS NCP_NAME
,B.REFERRAL_DT
,B.REFERRAL_TYPE_CD
,B.REFERRED_COUNTY
,B.PROGRAM_TYPE_CD
,B.ENROLLMENT_DT
,B.COMPLETION_DT
,B.NOT_COMPLETED_IND
FROM VCASE A
,VNPEP_REFERRAL B
,VCLIENT_ID C
,VJURISDICTION D
WHERE B.IRN > :WS-CKPT-IRN
AND A.AP_IRN = B.IRN
AND B.IRN = C.IRN
AND B.REFERRAL_DATE > '04-01-2006'
AND B.PROGRAM_TYPE_CD IN ('W','L')
AND D.JURISD_CD = B.REFERRED_COUNTY
ORDER BY B.IRN
WITH UR
END-EXEC.

EXEC SQL
OPEN NPEP_CUR
END-EXEC.

EXEC SQL
FETCH NPEP_CUR
INTO :DCLVJURISDICTION.JURISD-CD
,:DCLVJURISDICTION.JURISD-NAM
,:DCLVCASE.CASE-NUM
,:DCLVCASE.CASE-STATUS
,:DCLVCASE.AP-IRN
,:WS-NCP-NAME
,:DCLVNPEP-REFERRAL.REFERRAL-DT
,:DCLVNPEP-REFERRAL.REFERRAL-TYPE-CD
,:DCLVNPEP-REFERRAL.REFERRAL-COUNTY
,:DCLVNPEP-REFERRAL.PROGRAM-TYPE-CD
,:DCLVNPEP-REFERRAL.ENROLLMENT-DT
,:DCLVNPEP-REFERRAL.COMPLETION-DT
,:DCLVNPEP-REFERRAL.NOT-COMPLETED-IND
END-EXEC.

After successful Fetch

Get the Total Payments made since NCP is Referred, exclude Payment source
F-Federal Tax Intercept, S-State Tax Intercept, or L-Lottery Intercept.

EXEC SQL
SELECT SUM(PAYMENT)
FROM
(
SELECT COALESCE(SUM(RCPT_AMT),0) AS PAYMENT
FROM SIDPD008.VPAYMENT A
WHERE A.IRN = :DCLVCASE.AP-IRN

AND A.PYMNT_SRC NOT IN ('F','S','L')
AND A.COLLECTION_DT > '2006-04-01'

UNION ALL

SELECT COALESCE(SUM(
CASE
WHEN TRANS_TYPE = 'RP'
THEN (0 - B.APPLIED_AMT)
ELSE APPLIED_AMT
END
),0) AS PAYMENT
FROM SIDPD008.VPAYMENT_HISTORY A
,SIDPD008.VDISTRIBUTION_HIST B
WHERE A.RCPT_NUM = B.RCPT_NUM
AND A.JURISD = B.JURISD
AND DBCR_CD IN ('20','30','60','70')
AND A.PYMNT_SRC NOT IN ('F','S','L')
AND A.COLLECTION_DT > '2006-04-01'
AND A.IRN = :DCLVCASE.AP-IRN
) AS PAYTBL

END-EXEC

Get the last Payment Source

EXEC SQL
SELECT PYMNT_SRC
FROM
(SELECT PYMNT_SRC,COLLECTION_DT
FROM SIDZ1008.VPAYMENT
WHERE IRN = :DCLVCASE.AP-IRN
AND PYMNT_SRC NOT IN ('F','S','L')
AND COLLECTION_DT > '2006-11-01'

UNION ALL

SELECT PYMNT_SRC,COLLECTION_DT
FROM SIDZ1008.VPAYMENT_HISTORY
WHERE IRN = :DCLVCASE.AP-IRN
AND PYMNT_SRC NOT IN ('F','S','L')
AND COLLECTION_DT > '2006-11-01' ) AS A
ORDER BY COLLECTION_DT DESC
FETCH FIRST ROW ONLY
END-EXEC

• MOVE all the variables to output file layout

WRITE the Extract file.

After creating Extract file

EXEC SQL
CLOSE NPEP_CUR
END-EXEC.