Sample SAP Scripts for Rules Based Update

Jenzabar provides the following scripts to assist you in setting up a sample Satisfactory Progress calculation for testing in your play database environment. These scripts populate the Rules Based Update tables that support our sample SAP rules that are based on Federal Regulations.

Updates to the UPDATE_DEF Table

This script creates the SAP1 update code to define the Rule Assignment update.
 

INSERT INTO update_def VALUES (

'SAP1',

'SAP',

'Rule Assignment',

'U',

'PF_SAP_HIST',

'RULE_VALUE',

‘Jenzabar',

'Sample Data',

'6-30-2006 15:27:27.173');

This script creates the SAP2 update code to define the Pass/Fail Calculation update.

INSERT INTO update_def VALUES (

'SAP2',

'SAP',

'Pass/Fail Calculation',

'U',

'PF_SAP_HIST',

'CURRENT_PROGRESS_1',

‘Jenzabar',

'Sample Data',

'6-30-2006 15:27:27.173');

This script creates the SAP3 update code to define the Final Progress Status update.

INSERT INTO update_def VALUES (

'SAP3',

'SAP',

'Progress Status Assignment',

'U',

'PF_SAP_HIST',

'FINAL_PROGRESS_1',

‘Jenzabar',

'Sample Data',

'6-30-2006 15:27:27.173');

Updates to the UPDATE_DEF_ACCESS Table

Jenzabar recommends that you edit this to assign access to the groups you have defined.  Replace TE_ADMIN with the appropriate group.

This script grants access to the SAP1 update code to define the Rule Assignment update.

INSERT INTO update_def_access VALUES (

'SAP1',

'SAP',

'TE_ADMIN',

‘Jenzabar',

'Sample Data',

'6-30-2006 16:17:56.400');

This script grants access to the SAP2 update code to define the Pass/Fail Calculation update.

INSERT INTO update_def_access VALUES (

'SAP2',

'SAP',

'TE_ADMIN',

‘Jenzabar',

'Sample Data',

'6-30-2006 16:17:56.400');

This script grants access to the SAP3 update code to define the Final Progress Status update.

INSERT INTO update_def_access VALUES (

'SAP3',

'SAP',

'TE_ADMIN',

‘Jenzabar',

'Sample Data',

'6-30-2006 16:17:56.400');

Updates to the UPDATE_TYPE_CLUSTERS Table

These scripts create the SAP cluster that executes SAP1, SAP2, and SAP3 in proper sequence.

INSERT INTO update_type_clusters VALUES (

'SAP1',

'SAP',

'SAP',

‘Jenzabar',

'Sample Data',

'6-30-2006 4:54:32.600');

INSERT INTO update_type_clusters VALUES (

'SAP2',

'SAP',

'SAP',

‘Jenzabar',

'Sample Data',

'6-30-2006 4:54:32.600');

INSERT INTO update_type_clusters VALUES (

'SAP3',

'SAP',

'SAP',

‘Jenzabar',

'Sample Data',

'6-30-2006 4:54:32.600');

Updates to the UPDATE_RESULTS_DEF Table

These scripts set the definition values used.

INSERT INTO update_results_def VALUES (

'SAP1',

'SAP',

1,

'UNDERG',

‘Jenzabar',

'Sample Data',

'2-16-2006 17:0:1.670');

INSERT INTO update_results_def VALUES (

'SAP2',

'SAP',

1,

'PASS',

‘Jenzabar',

'Sample Data',

'6-30-2006 12:28:9.646');

INSERT INTO update_results_def VALUES (

'SAP2',

'SAP',

2,

'FAIL',

‘Jenzabar',

'Sample Data',

'6-30-2006 12:19:8.830');

INSERT INTO update_results_def VALUES (

'SAP3',

'SAP',

1,

'GOOD',

‘Jenzabar',

'Sample Data',

'6-30-2006 11:55:59.160');

INSERT INTO update_results_def VALUES (

'SAP3',

'SAP',

2,

'PROB',

‘Jenzabar',

'Sample Data',

'6-30-2006 12:19:8.830');

INSERT INTO update_results_def VALUES (

'SAP3',

'SAP',

3,

'SUSP',

‘Jenzabar',

'Sample Data',

'6-30-2006 12:28:9.646');

Updates to the UPDATE_RULES_RESULTS Table

These scripts detail the rules used to determine the definition values to set.

INSERT INTO update_results_rules VALUES (

'SAP1',

'SAP',

1,

1,

'PF_SAP_HIST.DIV_CDE',

'=',

'''U''',

'',

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.000');

INSERT INTO update_results_rules VALUES (

'SAP2',

'SAP',

1,

1,

'PF_SAP_HIST.RULE_VALUE',

'=',

'''UNDERG''',

'AND',

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.000');

INSERT INTO update_results_rules VALUES (

'SAP2',

'SAP',

1,

2,

'PF_SAP_HIST.CUMULATIVE_GPA',

'>=',

'2.0',

'AND',

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.000');

INSERT INTO update_results_rules VALUES (

'SAP2',

'SAP',

1,

3,

'(PF_SAP_HIST.CUM_HRS_EARNED/PF_SAP_HIST.CUM_HRS_ATTEMPTED)',

'>=',

'.80',

'AND',

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.013');

INSERT INTO update_results_rules VALUES (

'SAP2',

'SAP',

1,

4,

'PF_SAP_HIST.TOTAL_HRS_ATTEMPTED',

'<=',

'180',

NULL,

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.013');

INSERT INTO update_results_rules VALUES (

'SAP2',

'SAP',

2,

1,

'PF_SAP_HIST.RULE_VALUE',

'=',

'''UNDERG''',

'AND',

‘Jenzabar',

'dw_results_rules',

'4-25-2006 10:19:6.073');

INSERT INTO update_results_rules VALUES (

'SAP2',

'SAP',

2,

2,

'( PF_SAP_HIST.CUMULATIVE_GPA',

'<',

'2.0',

'OR',

‘Jenzabar',

'dw_results_rules',

'4-25-2006 10:19:6.073');

INSERT INTO update_results_rules VALUES (

'SAP2',

'SAP',

2,

3,

'(PF_SAP_HIST.CUM_HRS_EARNED/PF_SAP_HIST.CUM_HRS_ATTEMPTED)',

'<',

'.80',

'OR',

‘Jenzabar',

'dw_results_rules',

'4-25-2006 10:19:6.073');

INSERT INTO update_results_rules VALUES (

'SAP2',

'SAP',

2,

4,

'PF_SAP_HIST.TOTAL_HRS_ATTEMPTED',

'>',

'180 )',

NULL,

‘Jenzabar',

'dw_results_rules',

'4-25-2006 10:19:6.073');

INSERT INTO update_results_rules VALUES (

'SAP3',

'SAP',

1,

1,

'PF_SAP_HIST.CURRENT_PROGRESS_1',

'=',

'''PASS''',

NULL,

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.013');

INSERT INTO update_results_rules VALUES (

'SAP3',

'SAP',

2,

1,

'PF_SAP_HIST.CURRENT_PROGRESS_1',

'=',

'''FAIL''',

'AND',

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.013');

INSERT INTO update_results_rules VALUES (

'SAP3',

'SAP',

2,

2,

'(PF_SAP_HIST.PREVIOUS_PROGRESS_1',

'=',

'''GOOD''',

'OR',

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.013');

INSERT INTO update_results_rules VALUES (

'SAP3',

'SAP',

2,

3,

'PF_SAP_HIST.PREVIOUS_PROGRESS_1',

'IS',

'NULL)',

NULL,

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.013');

INSERT INTO update_results_rules VALUES (

'SAP3',

'SAP',

3,

1,

'PF_SAP_HIST.CURRENT_PROGRESS_1',

'=',

'''FAIL''',

'AND',

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.013');

INSERT INTO update_results_rules VALUES (

'SAP3',

'SAP',

3,

2,

'PF_SAP_HIST.PREVIOUS_PROGRESS_1',

'IN',

'(''PROB'',''SUSP'')',

NULL,

‘Jenzabar',

'SAMPLE DATA',

'6-30-2006 10:22:39.030');