CREATE TABLE "APPLICATION_VALIDITY" ("ID" NUMBER, "UOCAVA_APPLICATION_ID" NUMBER, "CODE" VARCHAR2(4000 BYTE), "NOTIFIED_DT" DATE, "NOTIFIED_METHOD" VARCHAR2(4000 BYTE)) Table created.
CREATE TABLE "BALLOT_VALIDITY" ("ID" NUMBER, "UOCAVA_BALLOT_ID" NUMBER, "CODE" VARCHAR2(4000 BYTE), "NOTIFIED_DT" DATE, "NOTIFIED_METHOD" VARCHAR2(4000 BYTE)) Table created.
CREATE TABLE "ELECTION" ("ID" NUMBER, "ELECTION_NAME" VARCHAR2(255 BYTE), "ELECTION_DT" DATE) Table created.
CREATE TABLE "JURISDICTION" ("ID" NUMBER, "NAME" VARCHAR2(255 BYTE), "CODE" VARCHAR2(4000 BYTE)) Table created.
CREATE TABLE "MAILING_ADDRESS" ("ID" NUMBER, "UOCAVA_APPLICATION_ID" NUMBER, "ADDRESS" VARCHAR2(4000 BYTE), "CITY" VARCHAR2(255 BYTE), "COUNTRY" VARCHAR2(4000 BYTE)) Table created.
CREATE TABLE "UOCAVA_APPLICATION" ("ID" NUMBER, "ELECTION_ID" NUMBER, "JURISDICTION_ID" NUMBER, "POSTMARK_DT" DATE, "RECV_DT" DATE, "APPLICATION_DT" DATE, "PROCESS_DT" DATE, "METHOD" VARCHAR2(4000 BYTE), "PARTY_AFFILIATION" VARCHAR2(4000 BYTE), "EXCUSE" VARCHAR2(4000 BYTE), "BALLOT_PREFERENCE" VARCHAR2(4000 BYTE), "EMAIL" VARCHAR2(255 BYTE), "PHONE" NUMBER, "ELECTION_DT" DATE, "VOTER_ID" NUMBER) Table created.
CREATE TABLE "UOCAVA_BALLOT" ("ID" NUMBER, "UOCAVA_APPLICATION_ID" NUMBER, "SEND_DT" DATE, "SEND_METHOD" VARCHAR2(4000 BYTE), "RECV_DT" DATE, "RECV_METH" VARCHAR2(4000 BYTE)) Table created.
REM INSERTING into APPLICATION_VALIDITY INSERTING into APPLICATION_VALIDITY
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454392995348850883522003097453,163320904454360354351721288534286030701,'SIGNATURE_MISMATCH',to_date('25-OCT-18','DD-MON-RR'),'HAND_CARRIED')1 row(s) inserted.
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454394204274670498151177803629,163320904454356727574262444646761912173,'TOO_LATE',to_date('15-NOV-18','DD-MON-RR'),'IN_PERSON')1 row(s) inserted.
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454395413200490112780352509805,163320904454360354351721288534286030701,'NO_PARTY_AFFILIATION',to_date('11-OCT-18','DD-MON-RR'),'HAND_CARRIED')1 row(s) inserted.
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454396622126309727409527215981,163320904454359145425901673905111324525,'ADDRESS_MISMATCH',to_date('14-NOV-18','DD-MON-RR'),'FAX')1 row(s) inserted.
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454397831052129342038701922157,163320904454361563277540903163460736877,'TOO_EARLY',to_date('15-SEP-18','DD-MON-RR'),'HAND_CARRIED')1 row(s) inserted.
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454399039977948956667876628333,163320904454356727574262444646761912173,'DUPLICATE',to_date('11-SEP-18','DD-MON-RR'),'FAX')1 row(s) inserted.
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454400248903768571297051334509,163320904454359145425901673905111324525,'NO_BIRTH_DATE_MISMATCH',to_date('16-OCT-18','DD-MON-RR'),'MAIL')1 row(s) inserted.
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454401457829588185926226040685,163320904454350682945164371500888381293,'NO_ELECTION',to_date('12-SEP-18','DD-MON-RR'),'HAND_CARRIED')1 row(s) inserted.
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454402666755407800555400746861,163320904454361563277540903163460736877,'NO_SUCH_ELECTION',to_date('02-NOV-18','DD-MON-RR'),'FAX')1 row(s) inserted.
Insert into APPLICATION_VALIDITY (ID,UOCAVA_APPLICATION_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454403875681227415184575453037,163320904454357936500082059275936618349,'ID_INVALID',to_date('10-SEP-18','DD-MON-RR'),'MAIL')1 row(s) inserted.
REM INSERTING into BALLOT_VALIDITY INSERTING into BALLOT_VALIDITY
Insert into BALLOT_VALIDITY (ID,UOCAVA_BALLOT_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454382115016474351859430741869,163320904454378488239015507971906623341,'ADDRESS_MISMATCH',to_date('03-OCT-18','DD-MON-RR'),'ONLINE')1 row(s) inserted.
Insert into BALLOT_VALIDITY (ID,UOCAVA_BALLOT_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454384532868113581117780154221,163320904454374861461556664084382504813,'INSUFFICIENT',to_date('08-OCT-18','DD-MON-RR'),'MAIL')1 row(s) inserted.
Insert into BALLOT_VALIDITY (ID,UOCAVA_BALLOT_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454385741793933195746954860397,163320904454370025758278205567683680109,'ID_INVALID',to_date('12-NOV-18','DD-MON-RR'),'HAND_CARRIED')1 row(s) inserted.
Insert into BALLOT_VALIDITY (ID,UOCAVA_BALLOT_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454389368571392039634478978925,163320904454368816832458590938508973933,'DUPLICATE',to_date('07-NOV-18','DD-MON-RR'),'EMAIL')1 row(s) inserted.
Insert into BALLOT_VALIDITY (ID,UOCAVA_BALLOT_ID,CODE,NOTIFIED_DT,NOTIFIED_METHOD) values (163320904454390577497211654263653685101,163320904454376070387376278713557210989,'MULTIPLE_BALLOTS',to_date('12-AUG-18','DD-MON-RR'),'EMAIL')1 row(s) inserted.
REM INSERTING into ELECTION INSERTING into ELECTION
Insert into ELECTION (ID,ELECTION_NAME,ELECTION_DT) values (163320904454338593686968225209141319533,'PRIMARY',to_date('17-SEP-18','DD-MON-RR'))1 row(s) inserted.
Insert into ELECTION (ID,ELECTION_NAME,ELECTION_DT) values (163320904454339802612787839838316025709,'SPECIAL',to_date('09-SEP-18','DD-MON-RR'))1 row(s) inserted.
Insert into ELECTION (ID,ELECTION_NAME,ELECTION_DT) values (163320904454341011538607454467490731885,'PRIMARY',to_date('11-SEP-18','DD-MON-RR'))1 row(s) inserted.
Insert into ELECTION (ID,ELECTION_NAME,ELECTION_DT) values (163320904454342220464427069096665438061,'SPECIAL',to_date('28-AUG-18','DD-MON-RR'))1 row(s) inserted.
Insert into ELECTION (ID,ELECTION_NAME,ELECTION_DT) values (163320904454343429390246683725840144237,'PRIMARY',to_date('09-SEP-18','DD-MON-RR'))1 row(s) inserted.
REM INSERTING into JURISDICTION INSERTING into JURISDICTION
Insert into JURISDICTION (ID,NAME,CODE) values (163320904454344638316066298355014850413,'JEFFERSON','AAD')1 row(s) inserted.
Insert into JURISDICTION (ID,NAME,CODE) values (163320904454345847241885912984189556589,'MONTGOMERY','AAD')1 row(s) inserted.
Insert into JURISDICTION (ID,NAME,CODE) values (163320904454347056167705527613364262765,'CLAY','AAD')1 row(s) inserted.
Insert into JURISDICTION (ID,NAME,CODE) values (163320904454348265093525142242538968941,'CLAY','AAB')1 row(s) inserted.
Insert into JURISDICTION (ID,NAME,CODE) values (163320904454349474019344756871713675117,'CLAY','AAG')1 row(s) inserted.
REM INSERTING into MAILING_ADDRESS INSERTING into MAILING_ADDRESS
Insert into MAILING_ADDRESS (ID,UOCAVA_APPLICATION_ID,ADDRESS,CITY,COUNTRY) values (163320904454362772203360517792635443053,163320904454350682945164371500888381293,'711 Ashley Heights Ave','Tanquecitos','United States')1 row(s) inserted.
Insert into MAILING_ADDRESS (ID,UOCAVA_APPLICATION_ID,ADDRESS,CITY,COUNTRY) values (163320904454363981129180132421810149229,163320904454355518648442830017587205997,'983 Varigny Place','Sugarloaf','United States')1 row(s) inserted.
Insert into MAILING_ADDRESS (ID,UOCAVA_APPLICATION_ID,ADDRESS,CITY,COUNTRY) values (163320904454365190054999747050984855405,163320904454360354351721288534286030701,'402 Shongopovi Street','Dale City','United States')1 row(s) inserted.
Insert into MAILING_ADDRESS (ID,UOCAVA_APPLICATION_ID,ADDRESS,CITY,COUNTRY) values (163320904454366398980819361680159561581,163320904454357936500082059275936618349,'215 Mabel Street','Grosvenor','United States')1 row(s) inserted.
Insert into MAILING_ADDRESS (ID,UOCAVA_APPLICATION_ID,ADDRESS,CITY,COUNTRY) values (163320904454367607906638976309334267757,163320904454361563277540903163460736877,'654 Muez Ave','Riverside','United States')1 row(s) inserted.
REM INSERTING into UOCAVA_APPLICATION INSERTING into UOCAVA_APPLICATION
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454350682945164371500888381293,163320904454339802612787839838316025709,163320904454348265093525142242538968941,to_date('10-SEP-18','DD-MON-RR'),to_date('21-SEP-18','DD-MON-RR'),to_date('09-SEP-18','DD-MON-RR'),to_date('24-SEP-18','DD-MON-RR'),'FAMILY_MEMBER','DEM','OVERSEAS_FPCA','MAIL','gricelda.luebbers@aaab.com',1140615038,to_date('20-SEP-18','DD-MON-RR'),3)1 row(s) inserted.
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454351891870983986130063087469,163320904454343429390246683725840144237,163320904454345847241885912984189556589,to_date('27-SEP-18','DD-MON-RR'),to_date('08-OCT-18','DD-MON-RR'),to_date('26-SEP-18','DD-MON-RR'),to_date('11-OCT-18','DD-MON-RR'),'IN_PERSON','DEM','UNIFORMED_FPCA','EMAIL','dean.bollich@aaac.com',4993112318,to_date('23-OCT-18','DD-MON-RR'),94)1 row(s) inserted.
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454353100796803600759237793645,163320904454343429390246683725840144237,163320904454345847241885912984189556589,to_date('08-AUG-18','DD-MON-RR'),to_date('19-AUG-18','DD-MON-RR'),to_date('07-AUG-18','DD-MON-RR'),to_date('22-AUG-18','DD-MON-RR'),'FAMILY_MEMBER','REP','OVERSEAS_FWAB','MAIL','milo.manoni@aaad.com',2253817828,to_date('05-OCT-18','DD-MON-RR'),52)1 row(s) inserted.
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454354309722623215388412499821,163320904454339802612787839838316025709,163320904454348265093525142242538968941,to_date('07-AUG-18','DD-MON-RR'),to_date('18-AUG-18','DD-MON-RR'),to_date('06-AUG-18','DD-MON-RR'),to_date('21-AUG-18','DD-MON-RR'),'ASSISTED','REP','OVERSEAS_FPCA','MAIL','laurice.karl@aaae.com',7177664071,to_date('16-OCT-18','DD-MON-RR'),79)1 row(s) inserted.
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454355518648442830017587205997,163320904454343429390246683725840144237,163320904454348265093525142242538968941,to_date('27-JUL-18','DD-MON-RR'),to_date('07-AUG-18','DD-MON-RR'),to_date('26-JUL-18','DD-MON-RR'),to_date('10-AUG-18','DD-MON-RR'),'MAIL','GRE','OVERSEAS_FPCA','EMAIL','august.rupel@aaaf.com',9141720770,to_date('25-AUG-18','DD-MON-RR'),23)1 row(s) inserted.
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454356727574262444646761912173,163320904454342220464427069096665438061,163320904454344638316066298355014850413,to_date('09-AUG-18','DD-MON-RR'),to_date('20-AUG-18','DD-MON-RR'),to_date('08-AUG-18','DD-MON-RR'),to_date('23-AUG-18','DD-MON-RR'),'FAMILY_MEMBER','GRE','OVERSEAS_FWAB','MAIL','salome.guisti@aaag.com',7633831731,to_date('12-NOV-18','DD-MON-RR'),46)1 row(s) inserted.
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454357936500082059275936618349,163320904454339802612787839838316025709,163320904454344638316066298355014850413,to_date('06-AUG-18','DD-MON-RR'),to_date('17-AUG-18','DD-MON-RR'),to_date('05-AUG-18','DD-MON-RR'),to_date('20-AUG-18','DD-MON-RR'),'HAND_CARRIED','DEM','OVERSEAS_FPCA','FAX','lovie.ritacco@aaah.com',9997658492,to_date('09-SEP-18','DD-MON-RR'),1)1 row(s) inserted.
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454359145425901673905111324525,163320904454339802612787839838316025709,163320904454349474019344756871713675117,to_date('13-OCT-18','DD-MON-RR'),to_date('24-OCT-18','DD-MON-RR'),to_date('12-OCT-18','DD-MON-RR'),to_date('27-OCT-18','DD-MON-RR'),'HAND_CARRIED','REP','OVERSEAS_FWAB','MAIL','chaya.greczkowski@aaai.com',341133680,to_date('14-OCT-18','DD-MON-RR'),12)1 row(s) inserted.
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454360354351721288534286030701,163320904454342220464427069096665438061,163320904454349474019344756871713675117,to_date('17-AUG-18','DD-MON-RR'),to_date('28-AUG-18','DD-MON-RR'),to_date('16-AUG-18','DD-MON-RR'),to_date('31-AUG-18','DD-MON-RR'),'MAIL','GRE','OVERSEAS_FWAB','FAX','twila.coolbeth@aaaj.com',8568829547,to_date('14-SEP-18','DD-MON-RR'),27)1 row(s) inserted.
Insert into UOCAVA_APPLICATION (ID,ELECTION_ID,JURISDICTION_ID,POSTMARK_DT,RECV_DT,APPLICATION_DT,PROCESS_DT,METHOD,PARTY_AFFILIATION,EXCUSE,BALLOT_PREFERENCE,EMAIL,PHONE,ELECTION_DT,VOTER_ID) values (163320904454361563277540903163460736877,163320904454338593686968225209141319533,163320904454344638316066298355014850413,to_date('21-JUL-18','DD-MON-RR'),to_date('01-AUG-18','DD-MON-RR'),to_date('20-JUL-18','DD-MON-RR'),to_date('04-AUG-18','DD-MON-RR'),'ASSISTED','DEM','UNIFORMED_FWAB','FAX','carlotta.achenbach@aaak.com',2628014525,to_date('18-OCT-18','DD-MON-RR'),27)1 row(s) inserted.
REM INSERTING into UOCAVA_BALLOT INSERTING into UOCAVA_BALLOT
Insert into UOCAVA_BALLOT (ID,UOCAVA_APPLICATION_ID,SEND_DT,SEND_METHOD,RECV_DT,RECV_METH) values (163320904454368816832458590938508973933,163320904454356727574262444646761912173,to_date('22-AUG-18','DD-MON-RR'),'HAND_CARRIED',to_date('12-OCT-18','DD-MON-RR'),'HAND_CARRIED')1 row(s) inserted.
Insert into UOCAVA_BALLOT (ID,UOCAVA_APPLICATION_ID,SEND_DT,SEND_METHOD,RECV_DT,RECV_METH) values (163320904454370025758278205567683680109,163320904454357936500082059275936618349,to_date('02-SEP-18','DD-MON-RR'),'IN_PERSON',to_date('04-SEP-18','DD-MON-RR'),'IN_PERSON')1 row(s) inserted.
Insert into UOCAVA_BALLOT (ID,UOCAVA_APPLICATION_ID,SEND_DT,SEND_METHOD,RECV_DT,RECV_METH) values (163320904454374861461556664084382504813,163320904454350682945164371500888381293,to_date('13-SEP-18','DD-MON-RR'),'ONLINE',to_date('13-AUG-18','DD-MON-RR'),'HAND_CARRIED')1 row(s) inserted.
Insert into UOCAVA_BALLOT (ID,UOCAVA_APPLICATION_ID,SEND_DT,SEND_METHOD,RECV_DT,RECV_METH) values (163320904454376070387376278713557210989,163320904454355518648442830017587205997,to_date('16-SEP-18','DD-MON-RR'),'IN_PERSON',to_date('04-NOV-18','DD-MON-RR'),'IN_PERSON')1 row(s) inserted.
Insert into UOCAVA_BALLOT (ID,UOCAVA_APPLICATION_ID,SEND_DT,SEND_METHOD,RECV_DT,RECV_METH) values (163320904454377279313195893342731917165,163320904454360354351721288534286030701,to_date('30-SEP-18','DD-MON-RR'),'EMAIL',to_date('01-OCT-18','DD-MON-RR'),'FAX')1 row(s) inserted.
Insert into UOCAVA_BALLOT (ID,UOCAVA_APPLICATION_ID,SEND_DT,SEND_METHOD,RECV_DT,RECV_METH) values (163320904454378488239015507971906623341,163320904454351891870983986130063087469,to_date('11-SEP-18','DD-MON-RR'),'MAIL',to_date('11-OCT-18','DD-MON-RR'),'HAND_CARRIED')1 row(s) inserted.
CREATE UNIQUE INDEX "APPLICATION_VALIDI_ID_PK" ON "APPLICATION_VALIDITY" ("ID") Index created.
CREATE INDEX "APPLICATION_VALIDITY_I1" ON "APPLICATION_VALIDITY" ("UOCAVA_APPLICATION_ID") Index created.
CREATE UNIQUE INDEX "BALLOT_VALIDITY_ID_PK" ON "BALLOT_VALIDITY" ("ID") Index created.
CREATE INDEX "BALLOT_VALIDITY_I1" ON "BALLOT_VALIDITY" ("UOCAVA_BALLOT_ID") Index created.
CREATE UNIQUE INDEX "ELECTION_ID_PK" ON "ELECTION" ("ID") Index created.
CREATE UNIQUE INDEX "JURISDICTION_ID_PK" ON "JURISDICTION" ("ID") Index created.
CREATE INDEX "MAILING_ADDRESS_I1" ON "MAILING_ADDRESS" ("UOCAVA_APPLICATION_ID") Index created.
CREATE UNIQUE INDEX "MAILING_ADDRESS_ID_PK" ON "MAILING_ADDRESS" ("ID") Index created.
CREATE INDEX "UOCAVA_APPLICATION_I1" ON "UOCAVA_APPLICATION" ("ELECTION_ID") Index created.
CREATE INDEX "UOCAVA_APPLICATION_I2" ON "UOCAVA_APPLICATION" ("JURISDICTION_ID") Index created.
CREATE UNIQUE INDEX "UOCAVA_APPLICATION_ID_PK" ON "UOCAVA_APPLICATION" ("ID") Index created.
CREATE UNIQUE INDEX "UOCAVA_BALLOT_ID_PK" ON "UOCAVA_BALLOT" ("ID") Index created.
CREATE INDEX "UOCAVA_BALLOT_I1" ON "UOCAVA_BALLOT" ("UOCAVA_APPLICATION_ID") Index created.
CREATE OR REPLACE TRIGGER "APPLICATION_VALIDITY_BIU"
before insert or update
on application_validity
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end application_validity_biu; Trigger created.
ALTER TRIGGER "APPLICATION_VALIDITY_BIU" ENABLETrigger altered.
CREATE OR REPLACE TRIGGER "BALLOT_VALIDITY_BIU"
before insert or update
on ballot_validity
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end ballot_validity_biu; Trigger created.
ALTER TRIGGER "BALLOT_VALIDITY_BIU" ENABLETrigger altered.
CREATE OR REPLACE TRIGGER "ELECTION_BIU"
before insert or update
on election
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end election_biu; Trigger created.
ALTER TRIGGER "ELECTION_BIU" ENABLETrigger altered.
CREATE OR REPLACE TRIGGER "JURISDICTION_BIU"
before insert or update
on jurisdiction
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end jurisdiction_biu; Trigger created.
ALTER TRIGGER "JURISDICTION_BIU" ENABLETrigger altered.
CREATE OR REPLACE TRIGGER "MAILING_ADDRESS_BIU"
before insert or update
on mailing_address
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end mailing_address_biu; Trigger created.
ALTER TRIGGER "MAILING_ADDRESS_BIU" ENABLETrigger altered.
CREATE OR REPLACE TRIGGER "UOCAVA_APPLICATION_BIU"
before insert or update
on uocava_application
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end uocava_application_biu; Trigger created.
ALTER TRIGGER "UOCAVA_APPLICATION_BIU" ENABLETrigger altered.
CREATE OR REPLACE TRIGGER "UOCAVA_BALLOT_BIU"
before insert or update
on uocava_ballot
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end uocava_ballot_biu; Trigger created.
ALTER TRIGGER "UOCAVA_BALLOT_BIU" ENABLETrigger altered.
ALTER TABLE "APPLICATION_VALIDITY" MODIFY ("ID" NOT NULL ENABLE)Table altered.
ALTER TABLE "APPLICATION_VALIDITY" ADD CONSTRAINT "APPLICATION_VALIDI_ID_PK" PRIMARY KEY ("ID") USING INDEX ENABLETable altered.
ALTER TABLE "BALLOT_VALIDITY" MODIFY ("ID" NOT NULL ENABLE)Table altered.
ALTER TABLE "BALLOT_VALIDITY" ADD CONSTRAINT "BALLOT_VALIDITY_ID_PK" PRIMARY KEY ("ID") USING INDEX ENABLETable altered.
ALTER TABLE "ELECTION" MODIFY ("ID" NOT NULL ENABLE)Table altered.
ALTER TABLE "ELECTION" ADD CONSTRAINT "ELECTION_ID_PK" PRIMARY KEY ("ID") USING INDEX ENABLETable altered.
ALTER TABLE "JURISDICTION" MODIFY ("ID" NOT NULL ENABLE)Table altered.
ALTER TABLE "JURISDICTION" ADD CONSTRAINT "JURISDICTION_ID_PK" PRIMARY KEY ("ID") USING INDEX ENABLETable altered.
ALTER TABLE "MAILING_ADDRESS" MODIFY ("ID" NOT NULL ENABLE)Table altered.
ALTER TABLE "MAILING_ADDRESS" ADD CONSTRAINT "MAILING_ADDRESS_ID_PK" PRIMARY KEY ("ID") USING INDEX ENABLETable altered.
ALTER TABLE "UOCAVA_APPLICATION" MODIFY ("ID" NOT NULL ENABLE)Table altered.
ALTER TABLE "UOCAVA_APPLICATION" ADD CONSTRAINT "UOCAVA_APPLICATION_EXCUSE_CC" CHECK (excuse in ('OVERSEAS_FPCA','OVERSEAS_FWAB','UNIFORMED_FPCA','UNIFORMED_FWAB')) ENABLETable altered.
ALTER TABLE "UOCAVA_APPLICATION" ADD CONSTRAINT "UOCAVA_APPLICATION_PHONE_BET" CHECK (phone between 330100000 and 9999999999) ENABLETable altered.
ALTER TABLE "UOCAVA_APPLICATION" ADD CONSTRAINT "UOCAVA_APPLICATION_ID_PK" PRIMARY KEY ("ID") USING INDEX ENABLETable altered.
ALTER TABLE "UOCAVA_BALLOT" MODIFY ("ID" NOT NULL ENABLE)Table altered.
ALTER TABLE "UOCAVA_BALLOT" ADD CONSTRAINT "UOCAVA_BALLOT_ID_PK" PRIMARY KEY ("ID") USING INDEX ENABLETable altered.
WITH
/*
@param p_input IN VARCHAR2 the input to be hashed
@return a hashed value
*/
FUNCTION hash_pii (
p_input IN VARCHAR2
) RETURN VARCHAR2 IS
l_raw RAW(4000);
l_hashed RAW(4000);
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- the salt must be changed prior to use in a jurisdiction!
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
lc_salt VARCHAR2(12) := 'my_salt_here';
BEGIN
l_raw := utl_raw.cast_to_raw(lc_salt || p_input);
-- crypto method used, it could be another secure hash alg
RETURN dbms_crypto.hash(src => l_raw, typ => dbms_crypto.hash_sh256);
END;
SELECT
ua.id AS "Id",
-- hash the voter id to protect anonymity of the voter
hash_pii(ua.voter_id) AS "VoterId",
( CASE ua.method
WHEN 'ASSISTED' THEN 'other'
WHEN 'FAMILY_MEMBER' THEN 'other'
WHEN 'HAND_CARRIED' THEN 'in-person'
WHEN 'IN_PERSON' THEN 'in-person'
WHEN 'MAIL' THEN 'mail'
WHEN 'ONLINE' THEN 'online'
WHEN 'FAX' THEN 'fax'
ELSE 'other'
END ) AS "RequestMethod",
-- example other types
( CASE ua.method
WHEN 'ASSISTED' THEN 'assisted'
WHEN 'FAMILY_MEMBER' THEN 'family-member'
END ) AS "RequestOtherMethod",
TO_CHAR(ua.recv_dt, 'YYYY-MM-DD') AS "RequestDate",
-- rewrite to be more efficient
( CASE
WHEN NOT EXISTS (
SELECT
NULL
FROM
application_validity av
WHERE
av.code <> 'VALID'
AND av.uocava_application_id = ua.id
) THEN 'accepted'
ELSE 'rejected'
END ) AS "RequestStatusType",
(
SELECT
'['
|| listagg('"'
||(CASE av.code
WHEN 'ADDRESS_MISMATCH' THEN 'other' -- filter out
WHEN 'DUPLICATE' THEN 'duplicate'
WHEN 'ID_INVALID' THEN 'missing-identification'
WHEN 'INSUFFICIENT' THEN 'invalid'
WHEN 'NO_ELECTION' THEN 'other'
WHEN 'TOO_EARLY' THEN 'other' -- filter out
WHEN 'TOO_LATE' THEN 'other' -- filter out
WHEN 'NO_ADDESS' THEN 'invalid'
WHEN 'SIGNATURE_MISMATCH' THEN 'mismatch-voter-signature'
WHEN 'VALID' THEN ''
ELSE 'other'
END)
|| '"', ',')
|| ']'
FROM
application_validity av
WHERE
av.uocava_application_id = ua.id
) AS "RequestStatusRejectionType",
(
SELECT
listagg(av.code, ',')
FROM
application_validity av
WHERE
-- only pull in codes that require OtherType mapping
av.code IN (
'ADDRESS_MISMATCH',
'TOO_EARLY',
'TOO_LATE',
'NO_PARTY_AFFILIATION',
'NO_ELECTION'
)
AND av.uocava_application_id = ua.id
) AS "RequestRejectionOtherType",
( CASE ua.excuse
WHEN 'OVERSEAS_FPCA' THEN 'fpca'
WHEN 'UNIFORMED_FPCA' THEN 'fpca'
WHEN 'OVERSEAS_FWAB' THEN 'fwab'
WHEN 'UNIFORMED_FWAB' THEN 'fwab'
-- probably not a UOCAVA request
ELSE 'untracked'
END ) AS "RequestType",
(
SELECT
'['
|| listagg('"'
||(CASE bv.code
WHEN 'ADDRESS_MISMATCH' THEN 'other'
WHEN 'DECEASED' THEN 'voter-died'
WHEN 'ID_INVALID' THEN 'other'
WHEN 'INSUFFICIENT' THEN 'other' -- potential forms of ID on return
WHEN 'MULTIPLE_BALLOTS' THEN 'other'
WHEN 'NOT_TIMELY' THEN 'not-timely'
WHEN 'NO_ADDESS' THEN 'rejected'
WHEN 'SIGNATURE_MISMATCH' THEN 'mismatch-voter-signature'
WHEN 'UNDELIVERABLE' THEN 'undeliverable'
WHEN 'NO_BIRTH_DATE_MISMATCH' THEN 'other'
WHEN 'SPOILED' THEN 'voided-spoiled'
WHEN 'VALID' THEN ''
ELSE 'other'
END)
|| '"', ',')
|| ']' AS list
FROM
ballot_validity bv
WHERE
bv.uocava_ballot_id = ub.id
) AS "BallotRejectionType",
(
SELECT
listagg(bv.code, ',')
FROM
ballot_validity bv
WHERE
-- only pull in codes that require OtherType mapping
bv.code IN (
'ADDRESS_MISMATCH',
'DUPLICATE',
'INSUFFICIENT',
'ID_INVALID',
'TOO_LATE',
'MULTIPLE_BALLOTS',
'NO_BIRTH_DATE_MISMATCH'
)
AND bv.uocava_ballot_id = ub.id
) AS "BallotRejectionOtherType",
TO_CHAR(ub.recv_dt, 'YYYY-MM-DD') AS "BallotReceivedDate",
( CASE ub.recv_meth
WHEN 'EMAIL' THEN 'email'
WHEN 'FAX' THEN 'fax'
WHEN 'HAND_CARRIED' THEN 'in-person'
WHEN 'IN_PERSON' THEN 'in-person'
WHEN 'MAIL' THEN 'mail'
WHEN 'ONLINE' THEN 'online'
ELSE 'other'
END ) AS "BallotReceivedMethod",
NULL AS "BallotReceivedOtherMethod",
TO_CHAR(ub.send_dt, 'YYYY-MM-DD') AS "BallotTransmissionDate",
( CASE ub.send_method
WHEN 'EMAIL' THEN 'email'
WHEN 'FAX' THEN 'fax'
WHEN 'HAND_CARRIED' THEN 'in-person'
WHEN 'IN_PERSON' THEN 'in-person'
WHEN 'MAIL' THEN 'mail'
WHEN 'ONLINE' THEN 'online'
ELSE 'other'
END ) AS "BallotTransmissionMethod",
-- TODO: determine if this needs mapped
NULL AS "BallotTransmissionOtherMethod",
( CASE ua.excuse
WHEN 'OVERSEAS_FPCA' THEN 'absentee'
WHEN 'UNIFORMED_FPCA' THEN 'absentee'
WHEN 'OVERSEAS_FWAB' THEN 'fwab'
WHEN 'UNIFORMED_FWAB' THEN 'fwab'
END ) AS "BallotType",
TO_CHAR(e.election_dt, 'YYYY-MM-DD') AS "ElectionDate",
e.election_name AS "ElectionName",
j.code AS "JurisdictionId",
'local' AS "JurisdictionIdType",
j.name AS "JurisdictionName",
'Ohio' AS "StateName",
(
SELECT
ma.country
FROM
mailing_address ma
WHERE
ma.uocava_application_id = ua.id
) AS "VoterMailingCountry",
( CASE ua.excuse
WHEN 'OVERSEAS_FPCA' THEN 'overseas-citizen'
WHEN 'UNIFORMED_FPCA' THEN 'military'
WHEN 'OVERSEAS_FWAB' THEN 'overseas-citizen'
WHEN 'UNIFORMED_FWAB' THEN 'military'
END ) AS "VoterType"
FROM
uocava_application ua
INNER JOIN uocava_ballot ub ON ub.uocava_application_id = ua.id
INNER JOIN election e ON e.id = ua.election_id
INNER JOIN jurisdiction j ON j.id = ua.jurisdiction_id| Id | VoterId | RequestMethod | RequestOtherMethod | RequestDate | RequestStatusType | RequestStatusRejectionType | RequestRejectionOtherType | RequestType | BallotRejectionType | BallotRejectionOtherType | BallotReceivedDate | BallotReceivedMethod | BallotReceivedOtherMethod | BallotTransmissionDate | BallotTransmissionMethod | BallotTransmissionOtherMethod | BallotType | ElectionDate | ElectionName | JurisdictionId | JurisdictionIdType | JurisdictionName | StateName | VoterMailingCountry | VoterType |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 163320904454356727574262444646761912173 | 43F94435594FCF0D3BA677EF3C0659CB060BB4549B22A788780983403F257D1D | other | family-member | 2018-08-20 | rejected | ["other","duplicate"] | TOO_LATE | fwab | ["other"] | DUPLICATE | 2018-10-12 | in-person | - | 2018-08-22 | in-person | - | fwab | 2018-08-28 | SPECIAL | AAD | local | JEFFERSON | Ohio | - | overseas-citizen |
| 163320904454357936500082059275936618349 | 578C32C44C2C5A070A835B1056E1BD59048155CEE3A4E1FDC21F8ACDCC175431 | in-person | - | 2018-08-17 | rejected | ["missing-identification"] | - | fpca | ["other"] | ID_INVALID | 2018-09-04 | in-person | - | 2018-09-02 | in-person | - | absentee | 2018-09-09 | SPECIAL | AAD | local | JEFFERSON | Ohio | United States | overseas-citizen |
| 163320904454350682945164371500888381293 | 62A98F5E9406F9A5FAB55D29372DA74CB54E2E5BA63604AF98659B61838D4D64 | other | family-member | 2018-09-21 | rejected | ["other"] | NO_ELECTION | fpca | ["other"] | INSUFFICIENT | 2018-08-13 | in-person | - | 2018-09-13 | online | - | absentee | 2018-09-09 | SPECIAL | AAB | local | CLAY | Ohio | United States | overseas-citizen |
| 163320904454355518648442830017587205997 | A3D6A63A7E455603A9FC8259B2D8903982EF021A13C994E775589B2ACC8427F8 | - | 2018-08-07 | accepted | [] | - | fpca | ["other"] | MULTIPLE_BALLOTS | 2018-11-04 | in-person | - | 2018-09-16 | in-person | - | absentee | 2018-09-09 | PRIMARY | AAB | local | CLAY | Ohio | United States | overseas-citizen | |
| 163320904454360354351721288534286030701 | 57A6723DF0EB7099D9B7D621F9AFB3F3C66F89C244D9F4D39E62E6D71FCF6CAD | - | 2018-08-28 | rejected | ["mismatch-voter-signature","other"] | NO_PARTY_AFFILIATION | fwab | [] | - | 2018-10-01 | fax | - | 2018-09-30 | - | fwab | 2018-08-28 | SPECIAL | AAG | local | CLAY | Ohio | United States | overseas-citizen | ||
| 163320904454351891870983986130063087469 | E833E61019F6CC1AD364BB258FE0A1F09152FB7BC9D6C3FE4C092D541CF85B98 | in-person | - | 2018-10-08 | accepted | [] | - | fpca | ["other"] | ADDRESS_MISMATCH | 2018-10-11 | in-person | - | 2018-09-11 | - | absentee | 2018-09-09 | PRIMARY | AAD | local | MONTGOMERY | Ohio | - | military |