Kategorien: Alle - execution - analysis - maintenance - parameters

von Manuel Trujillo Vor 5 Jahren

271

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

Über Mindomo
Hilfe
Kontakt
Preise
Öffentliche Mindmap Galerie
Mindmap Vorlagen

Mehr Info

Blog
AGB's
Datenschutzrichtlinie
Sicherheit
Barrierefreiheit
Sicherheitsrichtlinie

FAQ

Mindmapping – was ist das?
Mindmap Online
Mindmaps Beispiele
Was ist Concept-Mapping?
Erstellen Sie eine Concept-Map