Sacwis Database Update Request s1
SACWIS PRODUCTION DATABASE
UPDATE REQUEST
Date: / November 15, 2012Remedy Request ID: / Not Applicable
Production Build ID: / P.2.4.0
Build Description : / - Activity Log Edit
- Activity Log Move and other enhancements
- Activity Log Multiple Subcategory wish list item
Section 1 –
Type of Build : / ¨ Hot Fix / x FullDatabase Team Responsible Party: / SACWIS DBA’s
Section 2 – Technical Solution
Location of Solution Documentation: / R:\AS\Business Process Layer Unit\Deployment Artifacts\Release 2.4.0\Release 2.4.0 Preliminary Database ChangesSolution Documentation Folder Name:
Software/SQL File Name: / sacwis_prod_db_p.2.4.
List new table(s)/View/MView be added:
List new column(s) be added / modified: / Add 2 New Table(s)
CREATE TABLE Court_Detail (
court_Detail_Id NUMBER NOT NULL,
name VARCHAR2 (200) NOT NULL,
type_Code VARCHAR2 (30) NULL,
county_Code VARCHAR2 (30) NULL,
division VARCHAR2 (200) NULL,
address_Id NUMBER NULL,
phone VARCHAR2 (10) NULL,
phone_Ext VARCHAR2 (7) NULL,
fax VARCHAR2 (10) NULL,
website VARCHAR2 (100) NULL,
Created_By NUMBER NOT NULL,
Created_Date DATE NOT NULL,
Modified_By NUMBER NOT NULL,
Modified_Date DATE NOT NULL)
CREATE TABLE Judge_Magistrate_Info (
judge_Magistrate_Info_Id NUMBER NOT NULL,
court_Detail_Id NUMBER NOT NULL,
person_Id NUMBER NOT NULL,
type_Code VARCHAR2 (30) NOT NULL,
effective_Date DATE NOT NULL,
end_Date DATE NULL,
Created_By NUMBER NOT NULL,
Created_Date DATE NOT NULL,
Modified_By NUMBER NOT NULL,
Modified_Date DATE NOT NULL
)
Added 17 Column(s) to 7 Existing Table(s)
1. alter table rpt_eligibility_detail
add
( ADOPTION_SUBSIDY_ID NUMBER )
2. alter table ENTITY_SEARCH
add
( app_code VARCHAR2 (30) NULL)
3. alter table ENTITY_VIEW
add
( app_code VARCHAR2 (30) NULL)
4. Alter table REPORT_TEMPLATE
add
(create_Activity_Log_Flag NUMBER DEFAULT 0 NOT NULL)
5. alter table TICKLER
add
disposed_By_Task_Id NUMBER NULL,
disposed_By_Task_Type_Code VARCHAR2 (30) NULL)
6. alter table AFCARS_PLACEMENR_EPISODE
add
(AWOL_AT_REMOVAL_FLAG NUMBER,
REMOVED_PLACEMENT_EPISODE_ID NUMBER)
7. alter table AFCARS
add
( CHAR_NOT_COMPLETED_FLAG NUMBER,
ADOPTION_SUBSIDY_AGENCY_ID NUMBER,
AWOL_AT_REMOVAL_FLAG NUMBER,
LEAVE_DATE DATE,
MR_ADOPTION NUMBER,
VISHEAR_ADOPTION NUMBER,
PHYDIS_ADOPTION NUMBER,
DSMIII_ADOPTION NUMBER,
OTHERMED_ADOPTION NUMBER)
Modified 0 Existing Table(s)
List existing table(s) to be deleted: / Droped 4 Table(s)
1. AFCARS_LEAVE
2. AFCARS_PROVIDER_1
3. AFCARS_TEMP_PERSON
4. AFCARS_TEMP_RACE
List existing column(s) to be deleted: / Deleted Columns from 0 Existing Table(s)
Batch Ref Data / Seed Data Update
Application Ref Data / Seed Data / Multiple Insert(s)Update(s):
--AIMANDATE
update ref_data set short_desc = 'Other Children in the Home F- T -F (participant)' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'AIMANDATE' and ref_data_code = 'CHILDFACEPART';
update ref_data set short_desc = 'Initial 4 weeks not including 1st wk in placement' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'AIMANDATE' and ref_data_code = 'INIT4WKS';
update ref_data set short_desc = 'Other Adult in the Home F- T - F (participant)' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'AIMANDATE' and ref_data_code = 'ADULTFACEPART';
update ref_data set short_desc = 'Other Adult in the Home F - T - F (non-part.)' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'AIMANDATE' and ref_data_code = 'ADULTFACENONPART';
update ref_data set short_desc = 'Other Children in the Home F - T - F (non-part.)' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'AIMANDATE' and ref_data_code = 'CHILDFACENONPART';
--CORRESPONDANCE
update ref_data set short_desc = 'A/I Dispo Notification ACV/CSR' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'CORRESPONDANCE' and ref_data_code = 'DISPOSITIONACV';
update ref_data set short_desc = 'A/I Dispo Notification AP/ASR' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'CORRESPONDANCE' and ref_data_code = 'DISPOSITIONAP';
update ref_data set short_desc = 'A/I Dispo Notification Parent/Guardian/Custodian' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'CORRESPONDANCE' and ref_data_code = 'DISPOSITIONPGC';
update ref_data set short_desc = 'Post Invest Notification to Licensing Authority' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'CORRESPONDANCE' and ref_data_code = 'POSTINVESTIGATION';
update ref_data set short_desc = 'Post Invest Notification to OHC Admin' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'CORRESPONDANCE' and ref_data_code = 'POSTIVNOTIFYOHCAREADMIN';
update ref_data set short_desc = 'Free Copy of Med/Ed and Verification of Wardship' , sort_value='71' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'CORRESPONDANCE' and ref_data_code = 'YOUTHPROVIDED';
--ONGOINGVISITS
update ref_data set short_desc = 'Other Children in the Home F - T - F (non-part.)' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'ONGOINGVISITS' and ref_data_code = 'CHILDFACENONPART';
update ref_data set short_desc = 'Other Adult in the Home F- T - F (participant)' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'ONGOINGVISITS' and ref_data_code = 'ADULTFACEPART';
update ref_data set short_desc = 'Other Adult in the Home F - T - F (non-part.)' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'ONGOINGVISITS' and ref_data_code = 'ADULTFACENONPART';
update ref_data set short_desc = 'Other Children in the Home F- T -F (participant)' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'ONGOINGVISITS' and ref_data_code = 'CHILDFACEPART';
update ref_data set short_desc = 'CRC- initial 10 days not including day of placement' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'ONGOINGVISITS' and ref_data_code = 'CRCINITIAL';
update ref_data set short_desc = 'Initial 4 weeks not including 1st wk in placement' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'ONGOINGVISITS' and ref_data_code = 'INIT4WKS';
update ref_data set short_desc = 'IL- Initial 4 weeks not including 1st wk in placement' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'ONGOINGVISITS' and ref_data_code = 'ILFINIT4WKS';
update ref_data set short_desc = 'IL- initial 7 days not including day of placement' where domain_code = 'ActivitySubCategory'
and parent_ref_data_code = 'ONGOINGVISITS' and ref_data_code = 'ILFFACE7DAYS';
CREATE TABLE sacwis_fix.defect_16008_ref_data(
ID NUMBER
);
DECLARE
seqId NUMBER;
BEGIN
select REF_DATA_ID_SEQ.nextval into seqId from dual;
insert into sacwis_fix.defect_16008_ref_data values (seqId);
Insert into SACWIS.REF_DATA
(REF_DATA_ID, DOMAIN_CODE, REF_DATA_CODE, SHORT_DESC, LONG_DESC,
SORT_VALUE, INACTIVE_FLAG, CREATED_BY, CREATED_DATE, MODIFIED_BY,
MODIFIED_DATE, REF_DATA_CONFIG_ID, GROUP_CODE)
Values
(seqId, 'RepaymentPlanAction', 'CreatedInError', 'Created in Error', 'Created in Error',
3, 0, 100, SYSDATE, 100,
SYSDATE, 7942, '00')
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10001,'AR','true',100,sysdate,to_date('04/01/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10003,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10004,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10006,'AR','true',100,sysdate,to_date('04/01/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10008,'AR','true',100,sysdate,to_date('08/01/2011', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10009,'AR','true',100,sysdate,to_date('08/01/2011', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10010,'AR','true',100,sysdate,to_date('08/01/2011', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10011,'AR','true',100,sysdate,to_date('09/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10013,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10014,'AR','true',100,sysdate,to_date('04/01/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10015,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10020,'AR','true',100,sysdate,to_date('08/01/2011', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10021,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10022,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10024,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10025,'AR','true',100,sysdate,to_date('08/23/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10028,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10029,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10030,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10036,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10038,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10042,'AR','true',100,sysdate,to_date('4/01/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10044,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10047,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10048,'AR','true',100,sysdate,to_date('09/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10049,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10050,'AR','true',100,sysdate,to_date('04/01/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10051,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10054,'AR','true',100,sysdate,to_date('08/01/2011', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10055,'AR','true',100,sysdate,to_date('08/23/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10056,'AR','true',100,sysdate,to_date('09/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10057,'AR','true',100,sysdate,to_date('08/23/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10060,'AR','true',100,sysdate,to_date('08/23/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10061,'AR','true',100,sysdate,to_date('08/23/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10064,'AR','true',100,sysdate,to_date('08/23/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10066,'AR','true',100,sysdate,to_date('08/23/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10067,'AR','true',100,sysdate,to_date('08/23/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10068,'AR','true',100,sysdate,to_date('08/01/2011', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10069,'AR','true',100,sysdate,to_date('09/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10070,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10071,'AR','true',100,sysdate,to_date('08/01/2011', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10072,'AR','true',100,sysdate,to_date('08/01/2011', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10073,'AR','true',100,sysdate,to_date('08/01/2011', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10075,'AR','true',100,sysdate,to_date('04/01/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10076,'AR','true',100,sysdate,to_date('09/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10077,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10078,'AR','true',100,sysdate,to_date('07/01/2008', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10081,'AR','true',100,sysdate,to_date('04/01/2012', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
insert into agency_config a
(a.AGENCY_CONFIG_ID,a.AGENCY_ID,a.CONFIG_KEY,a.CONFIG_VALUE,a.CREATED_BY,a.CREATED_DATE,a.EFFECTIVE_DATE,a.END_DATE,a.MODIFIED_BY,a.MODIFIED_DATE)
values (AGENCY_CONFIG_ID_SEQ.nextval,10083,'AR','true',100,sysdate,to_date('11/01/2010', 'MM/DD/YYYY'),to_date('12/31/9999', 'MM/DD/YYYY'),100,sysdate);
update report_template set create_activity_log_flag = 1, MODIFIED_BY=999, MODIFIED_DATE=sysdate where
report_template_code in ('Rpt020', 'Rpt022', 'Rpt023', 'Rpt024', 'Rpt028', 'Rpt029', 'Rpt030', 'Rpt031', 'Rpt032', 'Rpt033', 'Rpt034',
'Rpt328', 'Rpt330')
UPDATE afcars_field_mapping
SET display_text = 'Has the child been clinically diagnosed as having a medical or mental health condition(s)',
modified_by = pkAuditUser.GetBuildUser('2.004'),
modified_date = SYSDATE
WHERE screen_field = 'person.profile.characteristics.characteristicsDetail';
UPDATE afcars_field_mapping
SET display_text = 'Clinically Diagnosed Medical Or Mental Health Condition',
modified_by = pkAuditUser.GetBuildUser('2.004'),
modified_date = SYSDATE
WHERE screen_field = 'person.profile.characteristics.characteristicsDetails';
--START of security info for Activity Log Mover--
--Create Role
INSERT INTO ROLE (ROLE_ID,ROLE_NAME,ROLE_CODE,WORKITEM_ASSIGNABLE_FLAG,STATE_ONLY_ROLE_FLAG,PUBLIC_AGENCY_ONLY_ROLE_FLAG,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE) VALUES(ROLE_ID_seq.nextVal,'Activity Log Mover','ACTIVITY_LOG_MOVER',1,0,1,0,sysdate,0,sysdate);
--Create Security Profiles required by all roles for the business function
INSERT INTO SECURITY_PROFILE (SECURITY_PROFILE_ID,ROLE_ID,BUSINESS_FUNCTION_ID,PROFILE_NAME,PROFILE_DESC,STATE_EXCLUSIVE_FLAG,PUBLIC_ONLY_FLAG,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE, ASGN_RQD_READ_PRV_EXMPT_FLAG, ASGN_RQD_UPDT_PRV_EXMPT_FLAG, ASGN_RQD_READ_PUB_EXMPT_FLAG, ASGN_RQD_UPDT_PUB_EXMPT_FLAG)
VALUES (SECURITY_PROFILE_ID_seq.nextVal,(select ROLE_ID from role where ROLE_CODE='ACTIVITY_LOG_MOVER'),
(select BUSINESS_FUNCTION_ID from BUSINESS_FUNCTION where BUSINESS_FUNCTION_CODE='CM30'),'Case Activity Log - Activity Log Mover','Activity Log Mover (CM30)',0,0,100,sysdate,100,sysdate, 0, 0, 0, 0);
--Create Business actions required by all roles for the business function
INSERT INTO UC_ACTION (UC_ACTION_ID,BUSINESS_FUNCTION_ID,NAME,UC_ACTION_CODE,ACTION_MODE) VALUES(UC_ACTION_ID_seq.nextVal,(select BUSINESS_FUNCTION_ID from BUSINESS_FUNCTION where BUSINESS_FUNCTION_CODE='CM30'),'CM30_MOVE_ACTIVITY_LOG','CM30_MOVE_ACTIVITY_LOG','UPDATE');
--Associate UC actions as required to all roles for the business function
INSERT INTO ACTION_ROLE (UC_ACTION_ID,ROLE_ID,ACTION_ROLE_ID) VALUES((select UC_ACTION_ID from UC_ACTION where UC_ACTION_CODE='CM30_CREATE_AMEND_ACTIVITY_LOG' and ACTION_MODE='UPDATE' and BUSINESS_FUNCTION_ID =(select BUSINESS_FUNCTION_ID from BUSINESS_FUNCTION where BUSINESS_FUNCTION_CODE='CM30')),
(select role_id from role where role_code='ACTIVITY_LOG_MOVER'),ACTION_ROLE_ID_seq.nextVal);
INSERT INTO ACTION_ROLE (UC_ACTION_ID,ROLE_ID,ACTION_ROLE_ID) VALUES((select UC_ACTION_ID from UC_ACTION where UC_ACTION_CODE='CM30_UPDATE_ACTIVITY_LOG' and ACTION_MODE='UPDATE' and BUSINESS_FUNCTION_ID =(select BUSINESS_FUNCTION_ID from BUSINESS_FUNCTION where BUSINESS_FUNCTION_CODE='CM30')),
(select role_id from role where role_code='ACTIVITY_LOG_MOVER'),ACTION_ROLE_ID_seq.nextVal);
INSERT INTO ACTION_ROLE (UC_ACTION_ID,ROLE_ID,ACTION_ROLE_ID) VALUES((select UC_ACTION_ID from UC_ACTION where UC_ACTION_CODE='CM30_MOVE_ACTIVITY_LOG' and ACTION_MODE='UPDATE' and BUSINESS_FUNCTION_ID =(select BUSINESS_FUNCTION_ID from BUSINESS_FUNCTION where BUSINESS_FUNCTION_CODE='CM30')),
(select role_id from role where role_code='ACTIVITY_LOG_MOVER'),ACTION_ROLE_ID_seq.nextVal);
INSERT INTO ACTION_ROLE (UC_ACTION_ID,ROLE_ID,ACTION_ROLE_ID) VALUES((select UC_ACTION_ID from UC_ACTION where UC_ACTION_CODE='CM30_VIEW_ACTIVITY_LOG' and ACTION_MODE='VIEW' and BUSINESS_FUNCTION_ID =(select BUSINESS_FUNCTION_ID from BUSINESS_FUNCTION where BUSINESS_FUNCTION_CODE='CM30')),
(select role_id from role where role_code='ACTIVITY_LOG_MOVER'),ACTION_ROLE_ID_seq.nextVal);
--END of security info for Activity Log Mover—
update report_template
set report_output_format = 'singleXLS,PDF',
modified_by = 999,
modified_date = sysdate
where REPORT_NAME = 'AFCARS Exception'
Data Updates
Materialized View
View
Sequence / Added 2 New Sequence(s) Overall:
1. create sequence court_detail_id_seq
2. create sequence judge_magistrate_info_id_seq
3.
Modified 0 Sequence(s) Overall:
Index / Added 5 New Indexe(s) Overall:
CREATE UNIQUE INDEX COURT_DETAIL_P on COURT_DETAIL
(court_Detail_Id)
CREATE UNIQUE INDEX JUDGE_MAGISTRATE_INFO_P on JUDGE_MAGISTRATE_INFO
(judge_Magistrate_Info_Id)
create index Court_Dtl_F01_Judge_Mgst_Inf on Judge_Magistrate_Info(court_Detail_Id)
create index Court_Dtl_F01_Judge_Mgst_Inf on Judge_Magistrate_Info(court_Detail_Id)
Constraint / Added 3 New and Modified 0 Existing Constraint(s):
ALTER TABLE Court_Detail ADD (
CONSTRAINT court_Detail_P
PRIMARY KEY (court_Detail_Id)
ALTER TABLE Judge_Magistrate_Info ADD (
CONSTRAINT judge_Magistrate_Info_P
PRIMARY KEY (judge_Magistrate_Info_Id)
ALTER TABLE Judge_Magistrate_Info ADD (
CONSTRAINT Court_Dtl_F01_Judge_Mgst_Inf
FOREIGN KEY (court_Detail_Id)
REFERENCES Court_Detail (court_Detail_Id)
Modified 0 Constraint(s) Overall:
Dropped 0 Constraint(s) Overall:
Function Updated / Added 6 New & Modified 2 Existing Function(s) & Dropped 0 Function(s) Overall:
Added 6 New Function(s):
1. GETPERSONAGEINMONTHS
2. GET_ACT_LOG_SUB_CATEGORIES
3. GET_ACTIVITY_SUB_CATEGORIES
4. GET_INVESTIGATIVE_START_DATE
5. GET_LAST_ACTIVITY_DATE_2
6. RPT_4281_SUBSIDY_DAYS
7.
Updated 2 Existing Function(s):
1. LOAD_AFCARS
2. SFGETINVESTIGATIONINITIATED
Dropped 0 Unused Function(s) :
1.
Package(s) Updated / Added 0 New & Modified 6 Package(s) & Dropped 0 Packages Overall:
0 New Package(s):
1.
Updated 6 Existing Package(s):
1. PKG_CASE_MERGE_CONSTANTS
2. PKG_CASE_MERGE
3. FM25_EXCHANGE_INFO_MMIS
4. PKADMCLOSECASE
5. PKINTAKESCREENINGDECISION
6. RPT_4280_4281_PACK
Dropped 0 Unused Package(s) :
Procedure to be compiled / Added 0 New & Modified 2 Existing Procedure(s) & Dropped 0 Existing Overall:
0 New Procedure(s):
1.
Updated 2 Existing Procedure:
1. MMISRESENDMULTIPLE
2. MMISRESENDSINGLE
3.
Dropped 0 Unused Procedure(s) :
1.
Triggers to be updated and enabled / Added 2 New and Modified 3 Existing Trigger(s) Overall:
Added 2 New Trigger(s):
1. COURT_DETAIL_AUDR
2. JUDGE_MAGISTRATE_INFO_AUDR
3.
Modified 3 Existing Trigger(s):
1. REPORT_TEMPLATE_AUDR
2. TICKLER_AUDR
3. PERSON_RESET_MMIS
Views / Modified 1 Existing View(s):
1. RPT_REPORT_HEADER_VW
2.
Script Updated / UPDATE ref_data SET
ref_data_code = 'INTENSEWEEKLY',
modified_by = 999,
modified_date = sysdate
WHERE ref_data_code = 'INTENSENEEDSWEEKLY' AND domain_code = 'ActivitySubCategory';
UPDATE ref_data SET
ref_data_code = 'HOME',
modified_by = 999,
modified_date = sysdate
WHERE ref_data_code = 'HOMEVISIT' AND domain_code = 'ActivitySubCategory';
COMMIT;
TRUNCATE TABLE CASE_ACT_LOG_SUB_CATEGORY;
ALTER TABLE CASE_ACT_LOG_SUB_CATEGORY NOLOGGING;
INSERT /*+ APPEND */ INTO CASE_ACT_LOG_SUB_CATEGORY (ACT_LOG_SUB_CATEGORY_ID,
ACTIVITY_LOG_ID,
SUB_CATEGORY_CODE,
created_by,
created_date,
modified_by,
modified_date)
SELECT ACT_LOG_SUB_CATEGORY_ID_SEQ.NEXTVAL,
activity_log_id,
CASE WHEN SUB_CATEGORY_CODE = 'INTENSENEEDSWEEKLY' THEN 'INTENSEWEEKLY' WHEN SUB_CATEGORY_CODE='HOMEVISIT' THEN 'HOME'
ELSE SUB_CATEGORY_CODE END AS SUB_CATEGORY_CODE,
created_by,
created_date,
modified_by,
modified_date
FROM case_activity_log
WHERE SUB_CATEGORY_CODE IS NOT NULL;
begin
loop
UPDATE ENTITY_SEARCH SET
APP_CODE = 'SACWIS'
WHERE APP_CODE IS NULL
AND ROWNUM < 100001;
if sql%notfound then
commit;
exit;
end if;
begin
loop
UPDATE ENTITY_VIEW SET
APP_CODE = 'SACWIS'
WHERE APP_CODE IS NULL
AND ROWNUM < 100001;
if sql%notfound then
commit;
exit;
end if;
Audit Tables Updated / New Tables and Modified Tables
Reference Above for Table and Column Adds and Modifications
List of reports deployed : / Reference Bill Ennis
List of reports deactivated : / Reference Bill Ennis
Section 3 – Testing Plan/Results
3.1 Test Plan
Identify the database used to test the software/SQL: / UATHow many days removed from production is the data in this database? / n/a
Name of person(s) completing the test in this database: / Stephen Fibelkorn
Identify the database used for user acceptance test: / UAT
How many days removed from production is the data in this database? / Start of build Cycle (32 Days)
Name of person(s) completing the user acceptance test in this database: / Kevin Bullock
Section 4 – Production Execution
4.1 Execution Plan
Identify whether a full or partial backup of the production database is needed, or indicate that no backup is needed. / Full / Successful RMAN backup Needed / PartialExplanation:
Identify when the backup will be taken: / Date: 11/15/2012
Time: 9.00 pm
Identify who will apply the software/SQL: / ODJFS DBA
4.2 Execution Results
Location of results documentation: / Results to be e-mailed to the SACWIS team.Page 1 of 21 sacwis_prod_db_p.2.4.0.doc