Categories: All - execution - analysis - maintenance - parameters

by Manuel Trujillo 6 years ago

281

Edit Checks

The document discusses the maintenance and analysis procedures for a specific system, highlighting file sharing protocols on Sharepoint. It includes detailed information on the PIRL-OSMIS mapping and WIPS edit checks, emphasizing the importance of accurate data submission by federal entities.

Edit Checks

WDA / DTMB

PIRL-OSMIS Mapping Document

PIRL-Edit-Checks-Analysis

Code Analysis Solutions (Queries)
File Shared on Sharepoint (adding tabs)

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

WIPS X.0.0 Edit Checks

FEDs Submit Result (Matt Provider)

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

Maintenance (MUST)

File Shared on Sharepoint
https://stateofmichigan.sharepoint.com/:x:/r/sites/DTMB/cs/govted/MAERS_OSMIS/_layouts/15/doc.aspx?sourcedoc=%7BFD222579-BF32-463F-BA25-10FD78B0EA92%7D&file=PIRL-OSMIS-Mapping.xlsx&action=default

runANNUAL

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;

PIRL Daily JOB

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);



Jobs Execution Logs

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

runQUARTER

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

 );

Finalize

db_batch_run.FinalizeBatchRun(bcr); -> End batch Log

runAggregatesQuarter

pirl_compute_aggregate.generatePIRLAggregates
insert into pirl_aggregate_query -> Queries Aggregate

generateReportableIndvFile

populateErrorSummaryTable

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 table from pirl, pirl_error

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;

runByProgram

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);

generateFileByProgram

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

);

generateFileByFS

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

generateEntireFile
populateTable

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);

report_pirl_taa.populateTable

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;

report_pirl_esa.populateTable

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;

report_pirl_wioa.populateTable

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

Init

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);


initCountyReference
SELECT DISTINCT county county_name, SUBSTR(countyfips, 3) county_num FROM zipcode WHERE state = 'MI' AND county IS NOT NULL
initServiceReference
SELECT * FROM service WHERE program_code IN ('ADULT', 'YTHYNG', 'YTHOLD', 'YTHSCHY', 'YTHSCHN', 'DW', 'ESA', 'TAA', 'NAFTAA', 'PREWIA') ORDER BY program_code, code
initContractNbrs
f41ResponseContractNbrs(1) := '914'; f41ResponseContractNbrs(2) := '1007'; f41ResponseContractNbrs(3) := '1197';
initCredentialPriorityDict
ref_rec.credential_priority_tab('4') := 1; ref_rec.credential_priority_tab('3') := 2; ref_rec.credential_priority_tab('2') := 3; ref_rec.credential_priority_tab('6') := 4; ref_rec.credential_priority_tab('7') := 5; ref_rec.credential_priority_tab('5') := 6; ref_rec.credential_priority_tab('1') := 7; ref_rec.credential_priority_tab('8') := 8; ref_rec.credential_priority_tab('0') := 9;

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

initDomainDict
set CODE,PRIMARY_EXTERNAL_CODE records -> select code,primary_external_code from cd_domain Table

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

initStateDict
set STATE records -> select STATE from ZIPCODE Table
initDWGGrantNbrDict
ref_rec.dwg_grant_nbr_tab('2601') := 'EM-02601-01-60-A-26'; ref_rec.dwg_grant_nbr_tab('2602') := 'EM-02602-02-60-A-26'; ref_rec.dwg_grant_nbr_tab('2603') := 'EM-02603-03-60-A-26'; ref_rec.dwg_grant_nbr_tab('2604') := 'EM-02604-04-60-A-26'; ref_rec.dwg_grant_nbr_tab('2606') := 'EM-02606-06-60-A-26'; ref_rec.dwg_grant_nbr_tab('2607') := 'EM-02607-07-60-A-26'; ref_rec.dwg_grant_nbr_tab('2611') := 'EM-19535-10-60-A-26'; ref_rec.dwg_grant_nbr_tab('2612') := 'EM-19536-10-60-A-26'; ref_rec.dwg_grant_nbr_tab('2613') := 'EM-19537-10-60-A-26'; ref_rec.dwg_grant_nbr_tab('2614') := 'EM-20523-10-60-A-26'; ref_rec.dwg_grant_nbr_tab('2615') := 'EM-21069-10-60-A-26'; ref_rec.dwg_grant_nbr_tab('2616') := 'EM-21188-11-60-A-26'; ref_rec.dwg_grant_nbr_tab('2617') := 'EM-21341-11-60-A-26'; ref_rec.dwg_grant_nbr_tab('2618') := 'EM-22634-12-60-A-26'; ref_rec.dwg_grant_nbr_tab('2619') := 'EM-24457-13-60-A-26'; ref_rec.dwg_grant_nbr_tab('2620') := 'EM-24849-13-60-A-26'; ref_rec.dwg_grant_nbr_tab('2621') := 'EM-25883-14-60-A-26'; ref_rec.dwg_grant_nbr_tab('2622') := 'EM-25864-14-60-A-26'; ref_rec.dwg_grant_nbr_tab('2623') := 'EM-27357-15-60-A-26'; ref_rec.dwg_grant_nbr_tab('2624') := 'EM-28175-16-60-A-26';
initETACodeDict
set ETA_CODE records -> select ETA_CODE from MWA Table
util_pirl.GetAllRuntimes
set records about All batch runtimes
db_batch_run.InitilizeBatchRun
set log Files Tracking Record
Set Log Files
https://devmis.michworks.org/ms/plsql/web_batch_run.BatchRunCriteria
utl_file.fopen('BATCH_OUT_PIRL','pirl.log','a',4000);

FTP >/u01/plio/dev1/batch/out/pirl

config_params.getVal('PIRL.DebugIndicator')
util_pirl.DEBUG = TRUE/FALSE

report_pirl

Mindomo

About Mindomo
Help
Contact
Pricing
Public Mind Map Gallery
Mind Map Templates

More Info

Blog
Terms of Use
Privacy Policy
Security
Accessibility
Security Policy

Faq

What is Mind Mapping?
Create a Mind Map Online
Mind Map Examples
What is Concept Mapping?
Concept Map Maker