av Manuel Trujillo för 6 årar sedan
281
Mer av detta
av Gary Sibbitts
av Владислав Зорин
av Mike Ton
av Serik Ayazhan
https://stateofmichigan.sharepoint.com/:x:/r/sites/DTMB/cs/govted/MAERS_OSMIS/_layouts/15/doc.aspx?sourcedoc=%7B7E013358-39B1-418C-9E04-F933565A0370%7D&file=PIRL-Edit-Checks-Analysis.xlsx&action=default
https://stateofmichigan.sharepoint.com/:x:/r/sites/DTMB/cs/govted/MAERS_OSMIS/_layouts/15/doc.aspx?sourcedoc=%7BC60F2668-7C06-45C5-8551-EB2FAE71C218%7D&file=WIPS%207.0.0%20Edit%20Checks%20-%2004-20-2018.xlsx&action=default
Note:
1. The runAnnual() is not being used. It is equivalent to running the runQuarter() for the 4th quarter
PROCEDURE runAnnual(
an_py IN NUMBER DEFAULT NULL,
ab_generate_entire_file IN BOOLEAN DEFAULT FALSE,
ab_generate_report_indv_file IN BOOLEAN DEFAULT FALSE,
ab_importUIData IN BOOLEAN DEFAULT TRUE,
ab_applyUIDate IN BOOLEAN DEFAULT TRUE)
calls:
runQuarter(an_py,
util_pirl.ANNUAL,
ab_generate_entire_file,
ab_generate_report_indv_file,
ab_importUIData,
ab_applyUIDate);
Constant Value:
util_pirl.ANNUAL CONSTANT INTEGER := 5;
The Daily Job is executing on Server around 6PM , and has the follow name "RPT - PIRL YTD Job".
The execution call is given as follow example:
/* PIRL Reports Daily */
mediated_service_report.report_pirl.runQuarter(
an_py => TO_CHAR(ADD_MONTHS(SYSDATE, -6), 'YYYY'),
an_qtr => TO_CHAR(ADD_MONTHS(SYSDATE, -6), 'Q'),
ad_daily_snapshot_date => SYSDATE);
The follow address are available to tracking job logs execution:
development
https://devmis.michworks.org/ms/plsql/web_batch_run.BatchRunCriteria
production
https://services.michworks.org/ms/plsql/web_batch_run.BatchRunCriteria
parameters:
PROCEDURE runQuarter(
an_py IN NUMBER DEFAULT NULL,
an_qtr IN NUMBER DEFAULT NULL,
ab_generate_entire_file IN BOOLEAN DEFAULT FALSE,
ab_generate_report_indv_file IN BOOLEAN DEFAULT FALSE,
ab_importUIData IN BOOLEAN DEFAULT TRUE,
ab_applyUIDate IN BOOLEAN DEFAULT TRUE,
ad_daily_snapshot_date IN DATE DEFAULT NULL,
use_pirl_aggregate_query_tab IN BOOLEAN DEFAULT TRUE,
do_finalize IN BOOLEAN DEFAULT TRUE)
execution example:
report_pirl.runQuarter(
an_py => 2007,
an_qtr => 3,
ab_importUIData => TRUE,
ab_applyUIDate => TRUE
);
PROCEDURE populateErrorSummaryTable(
an_py IN PLS_INTEGER,
an_qtr IN PLS_INTEGER
)
Call:
populateErrorSummaryTable(
util_pirl.periods_rec.rpt_py,
util_pirl.periods_rec.rpt_qtr
);
INSERT INTO pirl_error_summary(record_count, py, quarter_nbr, type_code, message)
SELECT COUNT(DISTINCT e.id) record_count, util_pirl.periods_rec.rpt_py, util_pirl.periods_rec.rpt_qtr,
CASE
WHEN ee.warning_ind = 'N' THEN 'E'
WHEN ee.warning_ind = 'Y' THEN 'W'
ELSE NULL
END type_code,
NVL(ee.error, 'N/A') message
FROM pirl e
LEFT OUTER JOIN pirl_error ee
ON e.id = ee.pirl_id
WHERE e.py = util_pirl.periods_rec.rpt_py
AND e.quarter_nbr = util_pirl.periods_rec.rpt_qtr
GROUP BY ee.warning_ind, ee.error;
totrecs := totrecs + SQL%ROWCOUNT;
PROCEDURE runByProgram(
an_py IN NUMBER DEFAULT NULL,
an_qtr IN NUMBER DEFAULT NULL,
as_program_code IN VARCHAR2 DEFAULT NULL,
ab_populate_table IN BOOLEAN DEFAULT TRUE,
ab_generate_files_by_program IN BOOLEAN DEFAULT TRUE,
ad_daily_snapshot_date IN DATE DEFAULT NULL,
do_finalize IN BOOLEAN DEFAULT FALSE
)
Call:
runByProgram(util_pirl.periods_rec.rpt_py, util_pirl.periods_rec.rpt_qtr, 'ADULT', ad_daily_snapshot_date => ad_daily_snapshot_date);
runByProgram(util_pirl.periods_rec.rpt_py, util_pirl.periods_rec.rpt_qtr, 'DW', ad_daily_snapshot_date => ad_daily_snapshot_date);
runByProgram(util_pirl.periods_rec.rpt_py, util_pirl.periods_rec.rpt_qtr, 'YTH', ad_daily_snapshot_date => ad_daily_snapshot_date);
runByProgram(util_pirl.periods_rec.rpt_py, util_pirl.periods_rec.rpt_qtr, 'ESA', ad_daily_snapshot_date => ad_daily_snapshot_date);
runByProgram(util_pirl.periods_rec.rpt_py, util_pirl.periods_rec.rpt_qtr, 'TAA', ad_daily_snapshot_date => ad_daily_snapshot_date);
PROCEDURE generateFileByProgram(
an_py IN NUMBER DEFAULT NULL,
an_qtr IN NUMBER DEFAULT NULL,
as_program_code IN VARCHAR2,
do_finalize IN BOOLEAN DEFAULT FALSE)
call:
generateFileByProgram(
util_pirl.periods_rec.rpt_py,
util_pirl.periods_rec.rpt_qtr,
as_program_code,
FALSE
);
PROCEDURE generateFileByFS(
an_py IN NUMBER,
an_qtr IN NUMBER,
as_funding_stream IN VARCHAR2)
call:
generateFileByFS(util_pirl.periods_rec.rpt_py,
util_pirl.periods_rec.rpt_qtr,
as_program_code);
generateFileByFS(util_pirl.periods_rec.rpt_py,
util_pirl.periods_rec.rpt_qtr,
'ESA');
generateFileByFS(util_pirl.periods_rec.rpt_py,
util_pirl.periods_rec.rpt_qtr,
'YOUTH');
create files into BATCH_OUT_PIRL folder
SELECT d.*
FROM pirl d
WHERE d.py = an_py AND
d.quarter_nbr = an_qtr AND
NVL(d.extra_do_not_output_ind, 'W') = 'W' AND
1 = CASE
WHEN as_funding_stream = 'ADULT' THEN d.fs_adult
WHEN as_funding_stream = 'DW' THEN d.fs_dw
WHEN as_funding_stream = 'DWG' THEN d.fs_dwg
WHEN as_funding_stream = 'YOUTH' THEN d.fs_youth
WHEN as_funding_stream = 'ESA' THEN d.fs_esa
WHEN as_funding_stream = 'JVSG' THEN d.fs_jvsg
WHEN as_funding_stream = 'TAA' THEN d.fs_taa
END
ORDER BY d.id
FTP_PIRL_FILE
batch_jobs_wlds.sftp_file('Y','put' ,as_FileNameOut , 'jak', 'ftp.michworks.org',
'/export/home/billigj/pirl/'|| LOWER(config_params.getVal('System.Type')) ||'/',
'22', 'Y', 'pirl_send.log', 'BATCH_OUT_PIRL', p_return_code
);
--
-- Purpose: Contains the processing for WLDS - Workforce Longitudinal Data System
--
-- MODIFICATION HISTORY
-- Person Date Comments
-- --------- ---------- -----------------------------------------------------
-- Joe Karas 10/20/2016 Jobs for WLDS
FTP -> /u01/plio/dev1/batch/out/pirl
ex: p_adult_dw_Q3_d.txt.zip
PROCEDURE populateTable(
an_py IN PLS_INTEGER,
an_qtr IN PLS_INTEGER,
as_program_code IN VARCHAR2,
ad_daily_snapshot_date IN DATE
)
call:
populateTable(util_pirl.periods_rec.rpt_py,
util_pirl.periods_rec.rpt_qtr,
as_program_code,
ad_daily_snapshot_date);
fetch data from TAA_REG table
importUiadata
PROCEDURE importUiaData(
bcr IN OUT db_batch_run.batch_composite_type,
an_calendar_year IN NUMBER DEFAULT null,
an_calendar_quarter IN NUMBER DEFAULT null,
ab_verifyNewUiaData IN BOOLEAN DEFAULT TRUE)
call:
report_pirl_uia.importUiaData(bcr,
util_pirl.periods_rec.rpt_cal_year,
util_pirl.periods_rec.rpt_cal_qtr);
/**
* This proc imports UIA data from the UIA TAPR_OUTPUT_TBL to the TAA_UIA_PAYMENT_ARCHIVE table.
* This is the table where UIA computes the payment information available in their system
* that needs to be reported on the PIRL.
* A side affect of this proc running is mn_importedUiaRows variable is updated with the number of rows imported.
*/
FUNCTION assignPIRLFields(
taa_rec IN OUT taa_reg%ROWTYPE,
fieldNo IN OUT PLS_INTEGER,
ad_daily_snapshot_date IN DATE,
error_tab OUT util_pirl.error_tab_type
)
RETURN pirl%ROWTYPE
Call:
pirl_rec := assignPIRLFields(taa_rec,
fieldNo,
ad_daily_snapshot_date,
error_tab);
ex:
fieldNo := 188; pirl_rec.enrolled_9grade_plus_sec_edu := util_pirl.getExtCode(taa_rec.enrolled_9thgrade_sec_edu_ind);
IF NVL(pirl_rec.enrolled_9grade_plus_sec_edu, 0) = 0 THEN
IF pirl_rec.edu_status IN (1, 2) OR pirl_rec.exit_school_status IN (1, 2)
THEN
pirl_rec.enrolled_9grade_plus_sec_edu := 1;
END IF;
END IF;
fetch data from ES_REG_COHORT table
util_pirl.Calculate_Elapsed_Time(elapsed_begin_time, elapsed_end_time , elapsed_time_message);
delete_ts DATE := SYSDATE - 0.0005;
-- delete timestamp is about 3 seconds prior to sysdate and used to delete all PIRL records not into the population of the table
call:
DELETE FROM pirl
WHERE py = an_py
AND quarter_nbr = an_qtr
AND SUBSTR(program_code, 1, 3) = SUBSTR(as_program_code, 1, 3)
AND change_date <= ad_delete_timestamp;
PROCEDURE DBUpsertPIRL(
pirl_rec IN OUT pirl%ROWTYPE,
error_tab IN OUT util_pirl.error_tab_type,
insrecs IN OUT NUMBER,
chdrecs IN OUT NUMBER,
updrecs IN OUT NUMBER,
errrecs IN OUT NUMBER)
Call:
util_pirl_dao.DBUpsertPIRL(pirl_rec,
error_tab,
insrecs,
chdrecs,
updrecs,
errrecs);
ex:
SELECT *
INTO pirl_old
FROM pirl
WHERE py = pirl_rec.py
AND quarter_nbr = pirl_rec.quarter_nbr
AND participant_id = pirl_rec.participant_id;
PROCEDURE DBUpdatePIRL(
pirl_rec IN OUT pirl%ROWTYPE,
pirl_old IN pirl%ROWTYPE,
error_tab IN OUT util_pirl.error_tab_type,
chdrecs IN OUT NUMBER,
updrecs IN OUT NUMBER,
errrecs IN OUT NUMBER)
call:
ex:
Dynamic SQL- Update, doing comparative about data change
'UPDATE pirl
SET record_nbr = '|| pirl_rec.record_nbr ||query
IF web_utils.isChanged(pirl_rec.py, pirl_old.py) THEN query := query || '
,py = '||NVL(TO_CHAR(pirl_rec.py), 'NULL')||''; END IF;
FUNCTION assignPIRLFields(
esc_rec IN OUT es_reg_cohort%ROWTYPE,
es_rec IN OUT es_reg%ROWTYPE,
fieldNo IN OUT PLS_INTEGER,
ad_daily_snapshot_date IN DATE,
error_tab OUT util_pirl.error_tab_type
)
RETURN pirl%ROWTYPE
Call:
pirl_rec := assignPIRLFields( esc_rec,
es_rec,
fieldNo,
ad_daily_snapshot_date,
error_tab);
ex:
fieldNo := 32;
IF pirl_rec.veteran_status <> 0 THEN
IF esc_rec.veteran_status_cd = 'VET_DIV' THEN
pirl_rec.disabled_veteran_status := 1;
ELSIF esc_rec.veteran_status_cd = 'VET_SDV' THEN
pirl_rec.disabled_veteran_status := 2;
ELSE
pirl_rec.disabled_veteran_status := 0;
END IF;
END IF;
fieldNo := 7;
IF pirl_rec.special_eta_project_id_1 IS NULL AND
pirl_rec.special_eta_project_id_2 IS NOT NULL
THEN
util_pirl.AddError(pirl_rec, error_tab, fieldNo, 'N', 'B) IF Special Project ID - 1 (PIRL 105) is blanks, THEN Special Project ID - 2 (PIRL 106) must be blank'); --SR 18253
END IF;
fetch data from WIA_REG table
Calculate Elapsed Time
util_pirl.Calculate_Elapsed_Time(elapsed_begin_time, elapsed_end_time , elapsed_time_message);
Delete PIRL records
delete_ts DATE := SYSDATE - 0.0005;
-- delete timestamp is about 3 seconds prior to sysdate and used to delete all PIRL records not into the population of the table
call:
DELETE FROM pirl
WHERE py = an_py
AND quarter_nbr = an_qtr
AND SUBSTR(program_code, 1, 3) = SUBSTR(as_program_code, 1, 3)
AND change_date <= ad_delete_timestamp;
util_pirl_dao.DBUpsertPIRL
PROCEDURE DBUpsertPIRL(
pirl_rec IN OUT pirl%ROWTYPE,
error_tab IN OUT util_pirl.error_tab_type,
insrecs IN OUT NUMBER,
chdrecs IN OUT NUMBER,
updrecs IN OUT NUMBER,
errrecs IN OUT NUMBER)
Call:
util_pirl_dao.DBUpsertPIRL(pirl_rec,
error_tab,
insrecs,
chdrecs,
updrecs,
errrecs);
ex:
SELECT *
INTO pirl_old
FROM pirl
WHERE py = pirl_rec.py
AND quarter_nbr = pirl_rec.quarter_nbr
AND participant_id = pirl_rec.participant_id;
DBUpdatePIRL
PROCEDURE DBUpdatePIRL(
pirl_rec IN OUT pirl%ROWTYPE,
pirl_old IN pirl%ROWTYPE,
error_tab IN OUT util_pirl.error_tab_type,
chdrecs IN OUT NUMBER,
updrecs IN OUT NUMBER,
errrecs IN OUT NUMBER)
call:
ex:
Dynamic SQL- Update, doing comparative about data change
'UPDATE pirl
SET record_nbr = '|| pirl_rec.record_nbr ||query
IF web_utils.isChanged(pirl_rec.py, pirl_old.py) THEN query := query || '
,py = '||NVL(TO_CHAR(pirl_rec.py), 'NULL')||''; END IF;
assignPIRLFields
FUNCTION assignPIRLFields(
wia_rec IN OUT wia_reg%ROWTYPE,
fieldNo IN OUT PLS_INTEGER,
ad_daily_snapshot_date IN DATE,
error_tab OUT util_pirl.error_tab_type
)
RETURN pirl%ROWTYPE
Call:
pirl_rec := assignPIRLFields( wia_rec,
fieldNo,
ad_daily_snapshot_date,
error_tab);
ex:
fieldNo := 7;
IF wfs_rec.national_emergency_grant_nbr2 IS NOT NULL AND
wfs_rec.national_emergency_grant_nbr3 IS NOT NULL AND
SUBSTR(wfs_rec.national_emergency_grant_nbr3, 1, 2) <> '26' THEN
pirl_rec.special_eta_project_id_1 := SUBSTR(wfs_rec.national_emergency_grant_nbr3, 1, 7);
END IF;
PostRowEditChecks
util_pirl_common_errors.PostRowEditChecks
fieldNo := 7;
IF pirl_rec.special_eta_project_id_1 IS NULL AND
pirl_rec.special_eta_project_id_2 IS NOT NULL
THEN
util_pirl.AddError(pirl_rec, error_tab, fieldNo, 'N', 'B) IF Special Project ID - 1 (PIRL 105) is blanks, THEN Special Project ID - 2 (PIRL 106) must be blank'); --SR 18253
END IF;
Any Update error is inserted into pirl_error Table
assignPostRowPIRLFields
PROCEDURE init(an_py IN NUMBER,
an_qtr IN NUMBER,
ad_daily_snapshot_date IN DATE)
execute:
init(an_py, an_qtr, ad_daily_snapshot_date);
1 = Secondary School Diploma/or equivalency 2 = AA or AS Diploma/Degree 3 = BA or BS Diploma/Degree 4 = Graduate/Post Graduate 5 = Occupational Licensure 6 = Occupational Certificate 7 = Occupational Certification 8 = Other Recognized Diploma, Degree, or Certificate 0 = No recognized credential
ADULT 1 ADULT_NOT 2 ADULT_WLFR 3 ADULT_WLFR_NOT 4 BASIC A BASIC_ED 7 CONTACT_ALL 5 CONTACT_NOT 6 CR_AS_DEG 2 CR_BS_DEG 3 CR_HS_GED 1 CR_MA_DEG 4 CR_OCC_CERT 6 CR_OCC_LIC 5 CR_OTHER 8
FTP >/u01/plio/dev1/batch/out/pirl