main.sql

--IMMIGRATION IS A VITAL PART IN EMPLOYING FOREIGN TRAINED HEALTHCARE PROFESSIONALS
-- THIS DATABASE IS AN EXAMPLE OF A AN IMMIGRATION FIRM MANAGING DOCUMENTS OF BENEFICIARIES. IT IS MAINLY COMPOSED OF 8 TABLES
-- WHEN A FOREIGN TRAINED PROFESSIONAL WISHES TO WORK IN THE USA, THEY ARE REQUIRED A SET OF VARIOUS DOCUMENTS INVOLVING IMMIGRATION
-- THIS INCLUDES LICENSES, RESUMES, WORK PERMITS, TRANSCRIPTS AND A LOT MORE
-- A DOCUMENT MAY BE REQUIRED MORE THAN ONCE AND DIFFERENT VERSIONS ARE REQUIRED OF IT FROM TIME TO TIME
--DECISIONS AND PETITION ARE BASED ON A SERIES OF DOCUMENTS AND CAN BE REUSED A MULTITUDE OF TIMES
-- THIS DATABASE PROVIDES A BORDERLINE EXAMPLE OF WHAT AN IMMIGRATION DATABASE MAY LOOK LIKE
--SURELY THERE ARE MORE DOCUMENTS IN MANAGING AN IMMIGRATION FIRM, AND MULTIPLE DISCIPLINES, BUT FOR THIS PROJECT, IT IS ONLY ASSUMING MOST BASIC REQUIREMENTS OF AN APPLICANT
-- AN IMMIGRATION FIRM MAY RECEIVE MULTIPLE DOCUMENTS FOR MULTIPLE PETITIONS FOR A SINGLE APPLICANT
-- OR YET, AN APPLICANT MAY ONLY HAVE ONE SINGLE PETITION
-- A DECISION IS WHAT DETERMINES THE PETITIONS APPROVAL PROCESS WHETHER APPROVED OR NOT
--FURTHERMORE, SAMPLE DATA IS INSERTED TO REFLECT SOME IMPORTANT QUERIES OR RESULTS THAT ARE VITAL TO AN IMMIGRATION FIRM

--Reset command TO REMOVE ALL TABLES
drop table M_BENEFICIARY;
drop table M_FACILITY;
drop table M_SPONSOR;
drop table M_DOCS;
drop table M_PETDOCS;
drop table M_WAGE;
drop table M_PETITION;
drop table M_DECISION;
drop sequence m_wagedet;

--Construct the Beneficiary table
--REFERS TO WHERE THE BENEFICIARY WILL BE WORKING
CREATE TABLE M_BENEFICIARY (
    BeneID int NOT NULL PRIMARY KEY, -- REFERS TO THE BENEFECIARIES ID
    BName VARCHAR2(100) NOT NULL, --THE NAME OF THE BENEFICIARY
    BLocation VARCHAR2(50) NOT NULL, -- LOCATION WHETHER INSIDE OR OUTSIDE OF THE COUNTRY OF THE BENEFICIARY
    BAddress VARCHAR2(100)NOT NULL, --ADDRESS OF THE BENEFICIARY
    BCity VARCHAR2(50)NOT NULL, -- REFERS TO THE CURRENT CITY OF THE BENEFICIARY
    BState VARCHAR2(50), ---- REFERS TO THE CURRENT STATE OF THE BENEFICIARY
    BCountry VARCHAR2(50)NOT NULL, -- REFERS TO THE CURRENT COUNTRY OF THE BENEFICIARY
    BZipCode VARCHAR2(20) -- REFERS TO THE ZIPCODE OF THE BENEFICIARY
);


--Construct Facility Table
--REFERS TO WHERE THE BENEFICIARY WILL BE WORKING
CREATE TABLE M_FACILITY (
    FaciID int NOT NULL PRIMARY KEY, -- FACILITY ID OF WHERE THEY WILL BE WORKING
    FaciName VARCHAR2(100) NOT NULL, -- REFERS TO THE NAME OF THE FACILITY
    FaciAddress VARCHAR2(50)NOT NULL, -- REFERS TO THE ADDRESS OF THE FACILITY
    FaciCity VARCHAR2(50)NOT NULL, -- REFERS TO THE CITY OF THE FACILITY
    FaciState VARCHAR2(2)NOT NULL,  ---- REFERS TO THE STATE OF THE FACILITY
    FaciZipCode VARCHAR2(20)NOT NULL -- REFERS TO THE ZIPCODE OF THE FACILITY
);

--Construct Sponsor Table
-- REFERS TO PRIMARY SPONSOR OF APPLICANT WHO WILL BE MAKING THE PETITION
CREATE TABLE M_SPONSOR ( 
    SponsorID int NOT NULL PRIMARY KEY, -- REFERS TO PRIMARY SPONSOR ID
    SponsorName VARCHAR2(50) NOT NULL, -- REFERS TO PRIMARY SPONSOR NAME
    SponsorAgency VARCHAR2(100), ---- REFERS TO AGENCY OR BUSINESS NAME IF APPLICABLE
    SponsorContact VARCHAR2(12) -- REFERS TO PRIMARY SPONSOR CONTACT NUMBE
);

--Construct Documents Ownership table
-- DOCUMENTS OF AN APPLICANT
--SHOULD HAVE ONLY ONE OWNER
CREATE TABLE M_DOCS (
    DocsID int NOT NULL PRIMARY KEY, ---- REFERS TO THE UNIQUE IDENTIFIED OF THE DOCID
    BeneID INT NOT NULL, -- OWNER OF THE DOCUMENT
    DocType VARCHAR2(50) NOT NULL, -- WHAT KIND OF DOCUMENT
    DocDate DATE, -- DATE OF DOCUMENT UPLOADED
    DocExpiration DATE -- EXPIRED DATE OF DOCUMENT
);

--Construct Documents related to Petition Table
-- A PETITION HAS A MANY TO MANY RELATIONSHIP WITH DOCUMENTS SINCE A DOCUMENT CAN BE USED IN MANY PETITIONS
CREATE TABLE M_PETDOCS (
    PeteID VARCHAR2(15), ---- REFERS TO PETTION ID
    DocsID int NOT NULL, ---- REFERS TO DOCUMENT ID
    CONSTRAINT PETDOCSIDPK PRIMARY KEY (PeteID, DocsID) --Use a Constraint of PeteID and DocsID as a PRIMARY KEY Combination
);

--Construct the wage table
-- REFERS TO HOW MUCH AN EMPLOYEE IS PAID
CREATE TABLE M_WAGE(
    WageID int NOT NULL, -- REFERS TO WAGEID
    FaciID int NOT NULL,-- REFERS TO FACILITY THEY WILL WORK
    Expiration Date,-- TILL WHEN IS THIS DOCUMENT APPLICABLE
    WDegree VARCHAR2(10),-- THE DEGREE APPLICABLE
    Salary Number(7,2),-- HOW MUCH IS THE SALARY
    CONSTRAINT WAGEPK PRIMARY KEY (WageID)-- WAGEID IS THE PRIMARY KEY
);    

--Construct Petition Table
-- REFERS TO PETITION NUMBER OF THE CASE
CREATE TABLE M_PETITION( 
    PetitionID VARCHAR2(15)NOT NULL,-- UNIQUE COMBINATION OF THE ID
    SponsorID int NOT NULL, -- REFERS TO THE SPONSOR OF THE PETITION
    WageID int NOT NULL, -- REFERS TO WAGE ID OF THE PETITION
    FiledDate DATE, -- WHEN THE PETITION WAS FILED
    PetitionType VARCHAR2(15) NOT NULL, --WWHAT KIND OF PETITION WAS FILED
    CONSTRAINT PETITIONPK PRIMARY KEY (PetitionID)
);
--Construct Decision Table
CREATE TABLE M_DECISION(
    DecisionID int NOT NULL, --REFERS TO THE DECISION ISSUED
    PetitionID VARCHAR2(15) NOT NULL, --TO WHAT DECISION IT IS REFERRING TO 
    Decision VARCHAR2(15)NOT NULL, --THE DECISION ITSELF
    Reason VARCHAR2(15)NOT NULL, --REASON BEHIND THE DECISION
    Decided DATE NOT NULL, -- DATE WHEN IT WAS DECIDED
    CONSTRAINT DECISIONPK PRIMARY KEY (DecisionID)
);

CREATE SEQUENCE M_WAGEDET
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

--Creating Beneficiary Values
--INSERTS THEM INDIVIDUALLY
INSERT INTO M_BENEFICIARY VALUES
		(001, 'ALFRED LORZANO', 'OUTSIDE', '305 HOLLARD STREET', 'MANILA', NULL, 'PHILIPPINES', '46260');
INSERT INTO M_BENEFICIARY VALUES
		(002, 'CHAD OKOYE', 'INSIDE', '02 BROADWAY APT A', 'NEW YORK', 'NY', 'USA', '13502');
INSERT INTO M_BENEFICIARY VALUES		
		(003, 'JONATHAN CHEN', 'OUTSIDE', '12 DUMPLING STREET', 'BEIJING', NULL, 'CHINA', '90532A');
INSERT INTO M_BENEFICIARY VALUES
		(004, 'MARIA OSCAR', 'OUTSIDE', '900 FELIPE STREET', 'TIJUANA', NULL, 'MEXICO', '87445');
INSERT INTO M_BENEFICIARY VALUES
		(005, 'ASHE OAK', 'OUTSIDE', '12 SHIBUYA APT 45', 'TOKYO', NULL, 'JAPAN', 'AE950');
INSERT INTO M_BENEFICIARY VALUES		
		(006, 'DONNIE YEN', 'INSIDE', '3791 LA SALLE STREET APT C', 'CHICAGO', 'IL', 'USA', '60007');
INSERT INTO M_BENEFICIARY VALUES		
		(007, 'YINGFEI LI', 'INSIDE', '520 OHARE MAIN STREET', 'OHARE', 'IL', 'USA', '65035');
INSERT INTO M_BENEFICIARY VALUES		
		(008, 'MANUEL GARCIA', 'OUTSIDE', '300 TEQUILA STREET', 'CHIHUAHUA', NULL, 'MAXICO', '89287');
INSERT INTO M_BENEFICIARY VALUES		
		(009, 'MELINDA BENITO', 'INSIDE', '56 ORANGE STREET APT A ', 'LOS ANGELES', 'CA', 'USA', '90005');
INSERT INTO M_BENEFICIARY VALUES		
		(0010, 'IGOR SHUKARATOV', 'INSIDE', '900 MIDWAY LANE', 'MIDLAND', 'TX', 'USA', '79701');
INSERT INTO M_BENEFICIARY VALUES		
		(011, 'CHELSEA NABAKOVA', 'OUTSIDE', '8902 CHERUMTA ST', 'RUSSIA', NULL, 'RUSSIA', '97504-GH');
INSERT INTO M_BENEFICIARY VALUES		
		(012, 'ANA AMARI', 'INSIDE', '570 HOLLYWOOD BLVD FLR 2', 'HOLLYWOOD', 'FL', 'USA', '32500');
INSERT INTO M_BENEFICIARY VALUES		
		(013, 'JULIUS ROMAN', 'OUTSIDE', '688 SAMPAGUITA LANE', 'MAKATI CITY', NULL, 'PHILIPPINES', '15200');
INSERT INTO M_BENEFICIARY VALUES
		(014, 'JAKE BOSEMAN', 'OUTSIDE', '902A PEBBLE BLVD ', 'CAIRO', NULL, 'EGYPT', 'P65204');
INSERT INTO M_BENEFICIARY VALUES
		(015, 'ALEXIS OBANA', 'INSIDE', '963 POSTDAM STREET', 'POSTDAM', 'NY', 'USA', '13676');
INSERT INTO M_BENEFICIARY VALUES
		(016, 'CHEUNG MAE MEL', 'INSIDE', '371 WOODDRIVE WAY', 'CHICAGO', 'IL', 'USA', '60100');
INSERT INTO M_BENEFICIARY VALUES		
		(017, 'SANDRA PARK', 'OUTSIDE', '788 BUSAN MAIN STREET', 'BUSAN', NULL, 'SOUTH KOREA', '600-011');
INSERT INTO M_BENEFICIARY VALUES		
		(018, 'JOSEPH NELTHORPE', 'INSIDE', '654 EAST MICHIGAN RD', 'SANTA MONICA', 'CA', 'USA', '90510');
INSERT INTO M_BENEFICIARY VALUES		
		(019, 'ANMOL KAPOOR', 'INSIDE', '365 AXEL PARKWAY', 'INDIANAPOLIS','IN', 'USA', '46032');
INSERT INTO M_BENEFICIARY VALUES
		(020, 'ROSIE GILL', 'INSIDE', '1002 LAFAYETTE ROAD', 'INDIANAPOLIS', 'IN', 'USA', '46250');
SELECT * FROM M_BENEFICIARY;





--Creating Facility Values
INSERT INTO M_FACILITY VALUES
(001, 'PROGRESSIVE HEALTHCARE', '105 MAIN STREET', 'INDIANAPOLIS', 'IN', '46260');
INSERT INTO M_FACILITY VALUES
(002, 'METHODOLOGICAL HOSPITAL', '900 DAVENPORT AVE', 'NEW HAVEN', 'CT', '92315');
INSERT INTO M_FACILITY VALUES
(003, 'HOSP AND DOCS', '9200 EAST TREMONT', 'BRONX', 'NY', '10469');
INSERT INTO M_FACILITY VALUES
(004, 'QUADTRIC HEALTH PROVIDERS', '15 NILON', 'OCEANSIDE', 'NY', '10050');
INSERT INTO M_FACILITY VALUES
(005, 'THE OUTPATIENT PTS', '854 XAVIER STE A', 'PEORIA', 'IL', '61260');
INSERT INTO M_FACILITY VALUES
(006, 'ARMS AND REHABS', '62-11 25TH AVENUE', 'SANTA CRUZ', 'CA', '97850');
INSERT INTO M_FACILITY VALUES
(007, 'LUKES NURSING HOME', '200 EAST 6TH STREET', 'MIDLAND', 'TX', '75000');
INSERT INTO M_FACILITY VALUES
(008, 'BURTON HEALTH CENTER', '100 HWAY 951', 'ROSEBUD', 'SD', '58000');
INSERT INTO M_FACILITY VALUES
(009, 'REHAB OF CHICAGO', '4600 N CALIFORNIA STREET', 'CHICAGO', 'IL', '63000');
INSERT INTO M_FACILITY VALUES
(010, 'NORTHERN MEDICAL CENTER', '34 HOSPITAL LOOP', 'ROSEBUD', 'SD', '52236');
INSERT INTO M_FACILITY VALUES
(011, 'MIDWEST INSTITUTE', '22 NORTH COTTAGE', 'PALATINE', 'IL', '66052');
INSERT INTO M_FACILITY VALUES
(012, 'NY HEALTH', '900 SOUTHWOODS', 'UTICA', 'NY', '12534');
INSERT INTO M_FACILITY VALUES
(013, 'ORANGE HEALTH CENTER', '22 TOKAY STREET', 'DELANO', 'CA', '92001');
INSERT INTO M_FACILITY VALUES
(014, 'EASTERN HEALTH AND CLINIC', '980 NORTH EAST STREET', 'TAMPA', 'FL', '32460');
INSERT INTO M_FACILITY VALUES
(015, 'STEPPING STONE PTS', '859 10TH STREET', 'ATLANTE', 'GA', '37700');
INSERT INTO M_FACILITY VALUES
(016, 'MEDICAL CENTER OF INDIANA', '12 MAIN STREET', ' INDIANAPOLIS', 'IN', '46250');
INSERT INTO M_FACILITY VALUES
(017, 'FULL ACHIEVE REHAB', '5 VETERANS PARKWAY', 'DENVER', 'CO', '81060');
INSERT INTO M_FACILITY VALUES
(018, 'AMERICAN HEART CENTER', '210 VALENTINE', 'VALENTINE', 'NE', '69300');
INSERT INTO M_FACILITY VALUES
(019, 'CA MEMORIAL HEALTH', '223 HILLCREST BLVD', 'SANTA MONICA', 'CA', '56040');
INSERT INTO M_FACILITY VALUES
(020, 'BERRY HOSPITAL AND CLINIC', '556 WESTOWN STREET', 'DES MOINES', 'IA', '54660');
INSERT INTO M_FACILITY VALUES
(021, 'DAKOTA HEALTH SYSTEM', '540 SOLDIER CREEK', 'ROSEBUD', 'SD', '53055');
INSERT INTO M_FACILITY VALUES
(022, 'MONTANA HOME HEALTH', '9002 GENESEE ONEIDA', 'JACKSON', 'FL', '39905');
INSERT INTO M_FACILITY VALUES
(023, 'DRIVE AND CARE NURSES', '250 MOLE DRIVE', 'SOCORRO', 'TX', '78852');
INSERT INTO M_FACILITY VALUES
(024, 'RAPID HEALTH SERVICES', '135-08 BEACH STREET', 'BROOKLYN', 'NY', '11650');
INSERT INTO M_FACILITY VALUES
(025, 'FULL REHAB AND NURSING', '72 ROUTE 60', 'GALLUP', 'NM', '87305');
INSERT INTO M_FACILITY VALUES
(026, 'IL GENERAL HOSPITAL', '140 GENESIS STREET', 'WOODSTOCK', 'IL', '64260');
INSERT INTO M_FACILITY VALUES
(027, 'SOUTH MEMORIAL HOSPITAL', '11 BROADWAY', 'FARGO', 'ND', '58260');
INSERT INTO M_FACILITY VALUES
(028, 'HOSPICE OF TEXAS', '800 MADISON STREET', 'ARLINGTON', 'TX', '79701');
INSERT INTO M_FACILITY VALUES
(029, 'MS COVENANT HOSPITAL', '2 RICHLAND MEDIA', 'COLUMBIA', 'SC', '29800');
INSERT INTO M_FACILITY VALUES
(030, 'IN MOTION PHYSICAL THERAPIST', '600 MORISSON STREET FLR A', 'CHARLESTON', 'WV', '25500');
SELECT * FROM M_FACILITY;
--CONSTRUCT SPONSOR TABLES
INSERT INTO M_SPONSOR VALUES
(001, 'SCOTT BAILEY', 'RELIANCE ASSOC', '3175550900');
INSERT INTO M_SPONSOR VALUES
(002, 'RAMON RODRIGUEZ', NULL, '4535554220');
INSERT INTO M_SPONSOR VALUES
(003, 'ANDREA HAMM', 'WORLDWIDE HEALTH', '6405558713');
INSERT INTO M_SPONSOR VALUES
(004, 'BARBARA JAIN', 'CROSS RECRUITS', '5585553994');
INSERT INTO M_SPONSOR VALUES
(005, 'RISHER HARRIS', NULL, '2085556010');
INSERT INTO M_SPONSOR VALUES
(006, 'LEO HOLMS', 'MEDICAL HR INC', '6635550900');
INSERT INTO M_SPONSOR VALUES
(007, 'MATTHEW MARRS', 'SCRUBS ON THE GO', '8065554033');
INSERT INTO M_SPONSOR VALUES
(008, 'KEITH SNOW', 'ONE CALL TRAVEL', '4885559911');
INSERT INTO M_SPONSOR VALUES
(009, 'ZACHARY LONG', 'PARAMOUNT THERAPIES', '6705558775');
INSERT INTO M_SPONSOR VALUES
(010, 'CHRISTINE EVANS', 'SCRUB AND STAFFING', '3215552210');
INSERT INTO M_SPONSOR VALUES
(011, 'CALVIN WALTZ', NULL, '8795550032');
SELECT * FROM M_SPONSOR;


--DOCS DATA INSERT
INSERT INTO M_DOCS VALUES
(001, 001, 'I-140', TO_DATE('01/20/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(002, 001, 'RESUME', TO_DATE('01/20/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(003, 001, 'LICENSE', TO_DATE('01/20/2015', 'MM/DD/YYYY'), TO_DATE('10/31/2018', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(004, 002, 'I-140', TO_DATE('03/22/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(005, 002, 'LICENSE', TO_DATE('03/22/2015', 'MM/DD/YYYY'), TO_DATE('01/01/2028', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(006, 002, 'RESUME', TO_DATE('03/23/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(007, 002, 'TRANSCRIPT', TO_DATE('03/23/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(008, 002, 'LICENSE', TO_DATE('03/25/2015', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(009, 003, 'LICENSE', TO_DATE('03/28/2015', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(010, 004, 'RESUME', TO_DATE('04/05/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(011, 004, 'TRANSCRIPT', TO_DATE('04/05/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(012, 003, 'I-140', TO_DATE('04/25/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(013, 004, 'RESUME', TO_DATE('04/30/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(014, 001, 'LICENSE', TO_DATE('04/30/2015', 'MM/DD/YYYY'), TO_DATE('06/30/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(015, 001, 'DIPLOMA', TO_DATE('04/30/2015', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(016, 002, 'TRANSCRIPT', TO_DATE('05/17/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(017, 002, 'RESUME', TO_DATE('05/17/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(018, 005, 'PERMIT', TO_DATE('05/17/2015', 'MM/DD/YYYY'), TO_DATE('12/05/2015', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(019, 004, 'I-140', TO_DATE('08/17/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(020, 002, 'LICENSE', TO_DATE('08/22/2015', 'MM/DD/YYYY'), TO_DATE('07/15/2019', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(021, 003, 'PERMIT', TO_DATE('08/22/2015', 'MM/DD/YYYY'), TO_DATE('02/15/2016', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(022, 003, 'RESUME', TO_DATE('03/23/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(023, 003, 'TRANSCRIPT', TO_DATE('03/23/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(024, 005, 'I-140', TO_DATE('08/25/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(025, 005, 'LICENSE', TO_DATE('09/22/2015', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(026, 005, 'TRANSCRIPT', TO_DATE('09/23/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(027, 005, 'RESUME', TO_DATE('09/23/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(028, 006, 'I-140', TO_DATE('09/25/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(029, 003, 'PERMIT', TO_DATE('10/10/2015', 'MM/DD/YYYY'), TO_DATE('08/15/2016', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(030, 010, 'PERMIT', TO_DATE('10/10/2015', 'MM/DD/YYYY'), TO_DATE('10/12/2016', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(031, 003, 'TRANSCRIPT', TO_DATE('10/23/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(032, 004, 'RESUME', TO_DATE('10/23/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(033, 007, 'TRANSCRIPT', TO_DATE('11/03/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(034, 004, 'RESUME', TO_DATE('11/05/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(035, 007, 'I-140', TO_DATE('12/15/2015', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(036, 007, 'DIPLOMA', TO_DATE('12/25/2015', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(037, 008, 'I-140', TO_DATE('02/20/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(038, 008, 'RESUME', TO_DATE('02/20/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(039, 008, 'LICENSE', TO_DATE('02/22/2016', 'MM/DD/YYYY'), TO_DATE('12/15/2018', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(040, 009, 'I-140', TO_DATE('03/01/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(041, 009, 'LICENSE', TO_DATE('03/22/2016', 'MM/DD/YYYY'), TO_DATE('01/15/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(042, 006, 'LICENSE', TO_DATE('03/22/2016', 'MM/DD/YYYY'), TO_DATE('01/15/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(043, 003, 'TRANSCRIPT', TO_DATE('03/23/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(044, 001, 'I-140', TO_DATE('05/02/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(045, 009, 'TRANSCRIPT', TO_DATE('05/05/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(046, 003, 'PERMIT', TO_DATE('05/05/2016', 'MM/DD/YYYY'), TO_DATE('04/15/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(047, 010, 'TRANSCRIPT', TO_DATE('05/03/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(048, 010, 'LICENSE', TO_DATE('05/15/2016', 'MM/DD/YYYY'), TO_DATE('09/15/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(049, 010, 'RESUME', TO_DATE('05/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(050, 010, 'I-140', TO_DATE('06/01/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(051, 013, 'TRANSCRIPT', TO_DATE('06/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(052, 013, 'TRANSCRIPT', TO_DATE('06/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(053, 002, 'RESUME', TO_DATE('06/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(054, 002, 'I-140', TO_DATE('07/30/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(055, 002, 'TRANSCRIPT', TO_DATE('07/30/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(056, 003, 'DIPLOMA', TO_DATE('08/15/2016', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(057, 012, 'I-140', TO_DATE('08/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(058, 002, 'I-140', TO_DATE('08/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(059, 014, 'TRANSCRIPT', TO_DATE('08/23/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(060, 012, 'LICENSE', TO_DATE('08/23/2016', 'MM/DD/YYYY'), TO_DATE('06/15/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(061, 016, 'RESUME', TO_DATE('08/23/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(062, 017, 'I-140', TO_DATE('09/10/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(063, 018, 'LICENSE', TO_DATE('03/22/2016', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(064, 020, 'LICENSE', TO_DATE('03/22/2016', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(065, 019, 'RESUME', TO_DATE('03/23/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(066, 012, 'I-140', TO_DATE('09/10/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(067, 015, 'TRANSCRIPT', TO_DATE('09/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(068, 017, 'TRANSCRIPT', TO_DATE('09/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(069, 018, 'LICENSE', TO_DATE('09/20/2016', 'MM/DD/YYYY'), TO_DATE('11/15/2018', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(070, 017, 'RESUME', TO_DATE('09/20/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(071, 014, 'PERMIT', TO_DATE('09/20/2016', 'MM/DD/YYYY'), TO_DATE('09/10/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(072, 014, 'PERMIT', TO_DATE('09/20/2016', 'MM/DD/YYYY'), TO_DATE('06/10/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(073, 013, 'I-140', TO_DATE('10/12/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(074, 014, 'I-140', TO_DATE('11/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(075, 016, 'TRANSCRIPT', TO_DATE('12/13/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(076, 011, 'RESUME', TO_DATE('12/13/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(077, 006, 'I-140', TO_DATE('12/15/2016', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(078, 009, 'TRANSCRIPT', TO_DATE('01/18/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(079, 004, 'LICENSE', TO_DATE('01/18/2017', 'MM/DD/YYYY'), TO_DATE('07/01/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(080, 007, 'I-140', TO_DATE('01/25/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(081, 017, 'I-140', TO_DATE('01/25/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(082, 011, 'PERMIT', TO_DATE('01/25/2017', 'MM/DD/YYYY'), TO_DATE('12/31/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(083, 012, 'RESUME', TO_DATE('01/25/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(084, 011, 'I-140', TO_DATE('01/28/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(085, 013, 'PERMIT', TO_DATE('01/30/2017', 'MM/DD/YYYY'), TO_DATE('01/15/2028', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(086, 015, 'RESUME', TO_DATE('01/30/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(087, 013, 'RESUME', TO_DATE('01/30/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(088, 006, 'I-140', TO_DATE('02/01/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(089, 006, 'I-140', TO_DATE('02/28/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(090, 003, 'PERMIT', TO_DATE('03/05/2017', 'MM/DD/YYYY'), TO_DATE('10/31/2017', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(091, 009, 'I-140', TO_DATE('03/12/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(092, 015, 'LICENSE', TO_DATE('03/12/2017', 'MM/DD/YYYY'), TO_DATE('12/31/2021', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(093, 016, 'LICENSE', TO_DATE('03/15/2017', 'MM/DD/YYYY'), TO_DATE('12/31/2021', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(094, 013, 'I-140', TO_DATE('04/12/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(095, 012, 'RESUME', TO_DATE('04/12/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(096, 019, 'DIPLOMA', TO_DATE('04/12/2017', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(097, 001, 'I-140', TO_DATE('04/12/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(098, 005, 'DIPLOMA', TO_DATE('04/19/2017', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(099, 009, 'DIPLOMA', TO_DATE('04/19/2017', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(100, 003, 'PERMIT', TO_DATE('04/19/2017', 'MM/DD/YYYY'), TO_DATE('11/01/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(101, 011, 'I-140', TO_DATE('04/23/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(102, 012, 'I-140', TO_DATE('04/23/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(103, 012, 'LICENSE', TO_DATE('04/25/2017', 'MM/DD/YYYY'), TO_DATE('03/15/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(104, 007, 'PERMIT', TO_DATE('04/25/2017', 'MM/DD/YYYY'), TO_DATE('04/25/2019', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(105, 009, 'PERMIT', TO_DATE('04/25/2017', 'MM/DD/YYYY'), TO_DATE('03/06/2018', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(106, 003, 'DIPLOMA', TO_DATE('05/01/2017', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(107, 003, 'TRANSCRIPT', TO_DATE('05/10/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(108, 003, 'RESUME', TO_DATE('05/20/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(109, 012, 'I-140', TO_DATE('06/10/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(110, 011, 'RESUME', TO_DATE('07/10/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(111, 013, 'LICENSE', TO_DATE('07/22/2017', 'MM/DD/YYYY'), TO_DATE('12/15/2018', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(112, 015, 'TRANSCRIPT', TO_DATE('07/23/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(113, 019, 'I-140', TO_DATE('08/10/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(114, 020, 'RESUME', TO_DATE('08/12/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(115, 017, 'TRANSCRIPT', TO_DATE('08/12/2017', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(116, 017, 'I-140', TO_DATE('02/01/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(117, 018, 'LICENSE', TO_DATE('02/01/2018', 'MM/DD/YYYY'), TO_DATE('06/10/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(118, 019, 'TRANSCRIPT', TO_DATE('02/01/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(119, 020, 'I-140', TO_DATE('02/01/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(120, 011, 'RESUME', TO_DATE('03/23/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(121, 014, 'LICENSE', TO_DATE('03/22/2018', 'MM/DD/YYYY'), TO_DATE('06/22/2019', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(122, 015, 'I-140', TO_DATE('03/28/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(123, 020, 'LICENSE', TO_DATE('03/22/2018', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(124, 020, 'RESUME', TO_DATE('03/23/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(125, 015, 'I-140', TO_DATE('04/15/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(126, 016, 'LICENSE', TO_DATE('03/22/2018', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(127, 016, 'RESUME', TO_DATE('03/23/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(128, 017, 'I-140', TO_DATE('04/28/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(129, 017, 'I-140', TO_DATE('05/30/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(130, 020, 'RESUME', TO_DATE('06/05/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(131, 021, 'LICENSE', TO_DATE('06/05/2018', 'MM/DD/YYYY'), TO_DATE('01/19/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(132, 020, 'TRANSCRIPT', TO_DATE('06/05/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(133, 019, 'I-140', TO_DATE('06/12/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(134, 001, 'LICENSE', TO_DATE('06/25/2018', 'MM/DD/YYYY'), TO_DATE('07/15/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(135, 005, 'LICENSE', TO_DATE('06/25/2018', 'MM/DD/YYYY'), TO_DATE('11/23/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(136, 003, 'DIPLOMA', TO_DATE('06/25/2018', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(137, 003, 'RESUME', TO_DATE('06/28/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(138, 009, 'TRANSCRIPT', TO_DATE('06/28/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(139, 009, 'RESUME', TO_DATE('06/28/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(140, 010, 'I-140', TO_DATE('06/30/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(141, 010, 'PERMIT', TO_DATE('07/01/2018', 'MM/DD/YYYY'), TO_DATE('11/20/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(142, 011, 'LICENSE', TO_DATE('07/01/2018', 'MM/DD/YYYY'), TO_DATE('05/31/2019', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(143, 011, 'PERMIT', TO_DATE('07/01/2018', 'MM/DD/YYYY'), TO_DATE('06/13/2018', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(144, 012, 'LICENSE', TO_DATE('07/01/2018', 'MM/DD/YYYY'), TO_DATE('07/15/2018', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(145, 015, 'LICENSE', TO_DATE('07/01/2018', 'MM/DD/YYYY'), TO_DATE('12/25/2023', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(146, 015, 'PERMIT', TO_DATE('07/01/2018', 'MM/DD/YYYY'), TO_DATE('10/31/2025', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(147, 017, 'RESUME', TO_DATE('07/01/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(148, 017, 'PERMIT', TO_DATE('07/01/2018', 'MM/DD/YYYY'), TO_DATE('07/15/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(149, 020, 'PERMIT', TO_DATE('07/01/2018', 'MM/DD/YYYY'), TO_DATE('06/10/2022', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(150, 003, 'PERMIT', TO_DATE('05/10/2018', 'MM/DD/YYYY'), TO_DATE('01/15/2021', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(151, 020, 'I-140', TO_DATE('07/18/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(152, 020, 'I-140', TO_DATE('08/23/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(153, 019, 'PERMIT', TO_DATE('09/10/2018', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(154, 018, 'RESUME', TO_DATE('09/15/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(155, 020, 'DIPLOMA', TO_DATE('09/15/2018', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(156, 015, 'LICENSE', TO_DATE('10/22/2018', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(157, 017, 'LICENSE', TO_DATE('10/22/2018', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(158, 018, 'I-140', TO_DATE('10/15/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(159, 020, 'RESUME', TO_DATE('10/15/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(160, 001, 'DIPLOMA', TO_DATE('10/23/2018', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(161, 002, 'PERMIT', TO_DATE('11/23/2018', 'MM/DD/YYYY'), TO_DATE('11/30/2019', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(162, 006, 'TRANSCRIPT', TO_DATE('12/15/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(163, 006, 'TRANSCRIPT', TO_DATE('12/22/2018', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(164, 010, 'LICENSE', TO_DATE('12/22/2018', 'MM/DD/YYYY'), TO_DATE('12/31/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(165, 011, 'I-140', TO_DATE('01/19/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(166, 012, 'LICENSE', TO_DATE('02/22/2019', 'MM/DD/YYYY'), TO_DATE('02/22/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(167, 012, 'LICENSE', TO_DATE('02/22/2019', 'MM/DD/YYYY'), TO_DATE('05/06/2021', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(168, 019, 'I-140', TO_DATE('02/15/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(169, 013, 'RESUME', TO_DATE('02/20/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(170, 020, 'PERMIT', TO_DATE('02/23/2019', 'MM/DD/YYYY'), TO_DATE('06/15/2022', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(171, 020, 'TRANSCRIPT', TO_DATE('03/23/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(172, 011, 'I-140', TO_DATE('03/29/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(173, 012, 'PERMIT', TO_DATE('04/15/2019', 'MM/DD/YYYY'), TO_DATE('12/15/2019', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(174, 011, 'I-140', TO_DATE('04/22/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(175, 015, 'DIPLOMA', TO_DATE('04/23/2019', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(176, 013, 'PERMIT', TO_DATE('04/23/2019', 'MM/DD/YYYY'), TO_DATE('08/10/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(177, 012, 'LICENSE', TO_DATE('05/10/2019', 'MM/DD/YYYY'), TO_DATE('11/01/2019', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(178, 009, 'I-140', TO_DATE('05/22/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(179, 008, 'DIPLOMA', TO_DATE('06/10/2019', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(180, 007, 'PERMIT', TO_DATE('06/10/2019', 'MM/DD/YYYY'), TO_DATE('01/05/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(181, 006, 'LICENSE', TO_DATE('06/19/2019', 'MM/DD/YYYY'), TO_DATE('01/05/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(182, 003, 'I-140', TO_DATE('07/18/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(183, 005, 'DIPLOMA', TO_DATE('10/06/2019', 'MM/DD/YYYY'),NULL);
INSERT INTO M_DOCS VALUES
(184, 009, 'PERMIT', TO_DATE('10/06/2019', 'MM/DD/YYYY'), TO_DATE('12/15/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(185, 012, 'LICENSE', TO_DATE('10/10/2019', 'MM/DD/YYYY'), TO_DATE('03/15/2020', 'MM/DD/YYYY'));
INSERT INTO M_DOCS VALUES
(186, 012, 'I-140', TO_DATE('12/23/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(187, 013, 'RESUME', TO_DATE('12/25/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(188, 020, 'RESUME', TO_DATE('12/25/2019', 'MM/DD/YYYY'), NULL);
INSERT INTO M_DOCS VALUES
(189, 020, 'I-140', TO_DATE('12/28/2019', 'MM/DD/YYYY'), NULL);
SELECT * FROM M_DOCS;

--CREATING VALUES FOR PETITION AND DOCS CROSSED
INSERT INTO M_PETDOCS VALUES
('A5612385486122', 001);
INSERT INTO M_PETDOCS VALUES
('A5612385486122', 002);
INSERT INTO M_PETDOCS VALUES
('A5612385486122', 003);
INSERT INTO M_PETDOCS VALUES
('G1795602359172', 004);
INSERT INTO M_PETDOCS VALUES
('G1795602359172', 005);
INSERT INTO M_PETDOCS VALUES
('G1795602359172', 006);
INSERT INTO M_PETDOCS VALUES
('G1795602359172', 007);
INSERT INTO M_PETDOCS VALUES
('C4226854930284', 012);
INSERT INTO M_PETDOCS VALUES
('C4226854930284', 009);
INSERT INTO M_PETDOCS VALUES
('C4226854930284', 018);
INSERT INTO M_PETDOCS VALUES
('C4226854930284', 022);
INSERT INTO M_PETDOCS VALUES
('C7005988363131', 019);
INSERT INTO M_PETDOCS VALUES
('C7005988363131', 013);
INSERT INTO M_PETDOCS VALUES
('H7392048445617', 024);
INSERT INTO M_PETDOCS VALUES
('H7392620581025', 025);
INSERT INTO M_PETDOCS VALUES
('H7392620581025', 026);
INSERT INTO M_PETDOCS VALUES
('H7392620581025', 027);
INSERT INTO M_PETDOCS VALUES
('C7005612109941', 035);
INSERT INTO M_PETDOCS VALUES
('C7005612109941', 036);
INSERT INTO M_PETDOCS VALUES
('C7005612109941', 032);
INSERT INTO M_PETDOCS VALUES
('C7005612109941', 041);
INSERT INTO M_PETDOCS VALUES
('G1795584930012', 037);
INSERT INTO M_PETDOCS VALUES
('G1795584930012', 038);
INSERT INTO M_PETDOCS VALUES
('G1795584930012', 039);
INSERT INTO M_PETDOCS VALUES
('G1795584930012', 041);
INSERT INTO M_PETDOCS VALUES
('G1795967745354', 042);
INSERT INTO M_PETDOCS VALUES
('G1795967745354', 045);
INSERT INTO M_PETDOCS VALUES
('C4226065829422', 001);
INSERT INTO M_PETDOCS VALUES
('C4226065829422', 002);
INSERT INTO M_PETDOCS VALUES
('C4226065829422', 003);
INSERT INTO M_PETDOCS VALUES
('A5612069574244', 047);
INSERT INTO M_PETDOCS VALUES
('A5612069574244', 048);
INSERT INTO M_PETDOCS VALUES
('A5612069574244', 049);
INSERT INTO M_PETDOCS VALUES
('A5612069574244', 050);
INSERT INTO M_PETDOCS VALUES
('C4226019402955', 058);
INSERT INTO M_PETDOCS VALUES
('C4226019402955', 054);
INSERT INTO M_PETDOCS VALUES
('C4226019402955', 055);
INSERT INTO M_PETDOCS VALUES
('C4226019402955', 053);
INSERT INTO M_PETDOCS VALUES
('G1795112049502', 062);
INSERT INTO M_PETDOCS VALUES
('G1795112049502', 068);
INSERT INTO M_PETDOCS VALUES
('G1795112049502', 070);
INSERT INTO M_PETDOCS VALUES
('C4226029402029', 073);
INSERT INTO M_PETDOCS VALUES
('C4226029402029', 087);
INSERT INTO M_PETDOCS VALUES
('C4226029402029', 051);
INSERT INTO M_PETDOCS VALUES
('H7392015995020', 074);
INSERT INTO M_PETDOCS VALUES
('H7392015995020', 059);
INSERT INTO M_PETDOCS VALUES
('H7392015995020', 071);
INSERT INTO M_PETDOCS VALUES
('H7392015995020', 072);
INSERT INTO M_PETDOCS VALUES
('C7005423354657', 077);
INSERT INTO M_PETDOCS VALUES
('C7005423354657', 042);
INSERT INTO M_PETDOCS VALUES
('C4226112579204', 080);
INSERT INTO M_PETDOCS VALUES
('C4226112579204', 033);
INSERT INTO M_PETDOCS VALUES
('C4226112579204', 036);
INSERT INTO M_PETDOCS VALUES
('G1795198294050', 081);
INSERT INTO M_PETDOCS VALUES
('G1795198294050', 070);
INSERT INTO M_PETDOCS VALUES
('A5612194820486', 094);
INSERT INTO M_PETDOCS VALUES
('A5612194820486', 087);
INSERT INTO M_PETDOCS VALUES
('A5612194820486', 052);
INSERT INTO M_PETDOCS VALUES
('C7005014810295', 097);
INSERT INTO M_PETDOCS VALUES
('C7005014810295', 014);
INSERT INTO M_PETDOCS VALUES
('C7005014810295', 015);
INSERT INTO M_PETDOCS VALUES
('G1795024859202', 101);
INSERT INTO M_PETDOCS VALUES
('G1795024859202', 176);
INSERT INTO M_PETDOCS VALUES
('H7392134859202', 109);
INSERT INTO M_PETDOCS VALUES
('H7392134859202', 103);
INSERT INTO M_PETDOCS VALUES
('H7392134859202', 095);
INSERT INTO M_PETDOCS VALUES
('A5612159906725', 133);
INSERT INTO M_PETDOCS VALUES
('A5612159906725', 118);
INSERT INTO M_PETDOCS VALUES
('A5612159906725', 096);
INSERT INTO M_PETDOCS VALUES
('G1795491958195', 140);
INSERT INTO M_PETDOCS VALUES
('G1795491958195', 047);
INSERT INTO M_PETDOCS VALUES
('G1795491958195', 048);
INSERT INTO M_PETDOCS VALUES
('G1795491958195', 049);
INSERT INTO M_PETDOCS VALUES
('C4226049102485', 165);
INSERT INTO M_PETDOCS VALUES
('C4226049102485', 082);
INSERT INTO M_PETDOCS VALUES
('C4226049102485', 076);
INSERT INTO M_PETDOCS VALUES
('C4226049102485', 120);
INSERT INTO M_PETDOCS VALUES
('A5612019502058', 001);
INSERT INTO M_PETDOCS VALUES
('A5612019502058', 160);
INSERT INTO M_PETDOCS VALUES
('A5612019502058', 014);
INSERT INTO M_PETDOCS VALUES
('A5612019502058', 015);
INSERT INTO M_PETDOCS VALUES
('C7005768590202', 012);
INSERT INTO M_PETDOCS VALUES
('C7005768590202', 136);
INSERT INTO M_PETDOCS VALUES
('C7005768590202', 137);
INSERT INTO M_PETDOCS VALUES
('C7005019502052', 035);
INSERT INTO M_PETDOCS VALUES
('C7005019502052', 180);
INSERT INTO M_PETDOCS VALUES
('C7005019502052', 036);
INSERT INTO M_PETDOCS VALUES
('A5612015829202', 094);
INSERT INTO M_PETDOCS VALUES
('A5612015829202', 111);
INSERT INTO M_PETDOCS VALUES
('A5612015829202', 085);
INSERT INTO M_PETDOCS VALUES
('H7392012486669', 058);
INSERT INTO M_PETDOCS VALUES
('H7392012486669', 016);
INSERT INTO M_PETDOCS VALUES
('H7392012486669', 017);
SELECT * FROM M_PETDOCS;

--CREATING VALUES FOR M_WAGE TABLE
INSERT INTO M_WAGE VALUES
(001, 001,	TO_DATE('01/31/2017', 'MM/DD/YYYY'), 'BACHELORS',49995);
INSERT INTO M_WAGE VALUES
(002, 002,	TO_DATE('02/15/2017', 'MM/DD/YYYY'), 'BACHELORS', 56230);
INSERT INTO M_WAGE VALUES
(003, 009,  TO_DATE('05/31/2017', 'MM/DD/YYYY'), 'MASTERS', 76849);
INSERT INTO M_WAGE VALUES
(004, 005,  TO_DATE('07/31/2017', 'MM/DD/YYYY'), 'BACHELORS', 59020);
INSERT INTO M_WAGE VALUES
(005, 006,  TO_DATE('03/15/2018', 'MM/DD/YYYY'), 'MASTERS', 67829);
INSERT INTO M_WAGE VALUES
(006, 007,	TO_DATE('03/15/2018', 'MM/DD/YYYY'), 'MASTERS', 72839);
INSERT INTO M_WAGE VALUES
(007, 008,  TO_DATE('06/20/2018', 'MM/DD/YYYY'), 'BACHELORS', 59002);
INSERT INTO M_WAGE VALUES
(008, 003,  TO_DATE('08/31/2018', 'MM/DD/YYYY'), 'BACHELORS', 61820);
INSERT INTO M_WAGE VALUES
(009, 004,  TO_DATE('08/31/2018', 'MM/DD/YYYY'), 'MASTERS', 71022);
INSERT INTO M_WAGE VALUES
(010, 017,  TO_DATE('11/01/2018', 'MM/DD/YYYY'), 'BACHELORS', 45030);
INSERT INTO M_WAGE VALUES
(011, 015,	TO_DATE('11/01/2018', 'MM/DD/YYYY'), 'MASTERS', 90201);
INSERT INTO M_WAGE VALUES
(012, 017,	TO_DATE('12/15/2018', 'MM/DD/YYYY'), 'BACHELORS', 45603);
INSERT INTO M_WAGE VALUES
(013, 020,	TO_DATE('12/15/2018', 'MM/DD/YYYY'), 'BACHELORS', 61839);
INSERT INTO M_WAGE VALUES
(014, 021,	TO_DATE('12/15/2018', 'MM/DD/YYYY'), 'BACHELORS', 55967);
INSERT INTO M_WAGE VALUES
(015, 023,	TO_DATE('12/15/2018', 'MM/DD/YYYY'), 'MASTERS', 63902);
INSERT INTO M_WAGE VALUES
(016, 025, 	TO_DATE('01/20/2019', 'MM/DD/YYYY'), 'MASTERS', 69002);
INSERT INTO M_WAGE VALUES
(017, 001,	TO_DATE('01/20/2019', 'MM/DD/YYYY'), 'BACHELORS', 51462);
INSERT INTO M_WAGE VALUES
(018, 003,	TO_DATE('02/15/2019', 'MM/DD/YYYY'), 'MASTERS', 75002);
INSERT INTO M_WAGE VALUES
(019, 005,	TO_DATE('02/15/2019', 'MM/DD/YYYY'), 'BACHELORS', 58029);
INSERT INTO M_WAGE VALUES
(020, 012,	TO_DATE('02/15/2019', 'MM/DD/YYYY'), 'MASTERS', 72092);
INSERT INTO M_WAGE VALUES
(021, 013, 	TO_DATE('03/29/2019', 'MM/DD/YYYY'), 'BACHELORS', 61092);
INSERT INTO M_WAGE VALUES
(022, 015,	TO_DATE('07/15/2019', 'MM/DD/YYYY'), 'MASTERS', 79359);
INSERT INTO M_WAGE VALUES
(023, 016,  TO_DATE('10/15/2019', 'MM/DD/YYYY'), 'MASTERS', 95100);
INSERT INTO M_WAGE VALUES
(024, 017,  TO_DATE('12/31/2020', 'MM/DD/YYYY'), 'BACHELORS', 59280);
INSERT INTO M_WAGE VALUES
(025, 018,	TO_DATE('12/31/2020', 'MM/DD/YYYY'), 'BACHELORS', 63500);
INSERT INTO M_WAGE VALUES
(026, 019,  TO_DATE('12/31/2020', 'MM/DD/YYYY'), 'MASTERS', 72170);
INSERT INTO M_WAGE VALUES
(027, 020,  TO_DATE('12/31/2020', 'MM/DD/YYYY'), 'BACHELORS', 57289);
SELECT * FROM M_WAGE;
--DEMONSTRATE A SAVEPOINT
SAVEPOINT WAGECLASSA;
--UPDATING A M_WAGE ROW
UPDATE  M_WAGE
    SET SALARY = 60000
    WHERE WAGEID = 22;
--SAVE IT AS A DIFFERENT SAVEPOINT    
SAVEPOINT WAGECLASSB;
--CHECK IF IT WAS UPDATED
SELECT * FROM M_WAGE;
--ROLLBACK TO BEFORE THE UPDATE
ROLLBACK TO WAGECLASSA;
--RUN REPORT
SELECT * FROM M_WAGE;
--SHOWS CURRENT UPDATE DUE TO ROLLBACK
--UPDATE AGAIN
UPDATE  M_WAGE
    SET SALARY = 75000
    WHERE WAGEID = 22;
SAVEPOINT WAGECLASSA;
SELECT * FROM M_WAGE;
--COMMIT CHANGES
COMMIT;




--CREATING VALUES FOR PETITION
INSERT INTO M_PETITION VALUES
('A5612385486122', 001, 001, TO_DATE('01/30/2015', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('G1795602359172', 011, 020, TO_DATE('03/29/2015', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('C4226854930284', 003, 019, TO_DATE('05/01/2015', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('C7005988363131', 001, 003, TO_DATE('08/25/2015', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('H7392048445617', 002, 005, TO_DATE('09/10/2015', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('H7392620581025', 007, 010, TO_DATE('10/31/2015', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('C7005612109941', 004, 015, TO_DATE('12/01/2015', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('G1795584930012', 003, 018, TO_DATE('04/15/2016', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('G1795967745354', 003, 019, TO_DATE('06/22/2016', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('C4226065829422', 002, 011, TO_DATE('06/22/2016', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('A5612069574244', 001, 003, TO_DATE('07/30/2016', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('C4226019402955', 005, 009, TO_DATE('07/30/2016', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('G1795112049502', 006, 005, TO_DATE('10/15/2016', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('C4226029402029', 010, 003, TO_DATE('10/15/2016', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('H7392015995020', 007, 002, TO_DATE('12/15/2016', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('C7005423354657', 008, 007, TO_DATE('01/22/2017', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('C4226112579204', 009, 011, TO_DATE('01/30/2017', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('G1795198294050', 011, 019, TO_DATE('02/28/2017', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('A5612194820486', 002, 006, TO_DATE('07/11/2017', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('C7005014810295', 003, 003, TO_DATE('07/11/2017', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('G1795024859202', 006, 008, TO_DATE('10/12/2017', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('G1795149500158', 007, 009, TO_DATE('12/31/2017', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('H7392134859202', 010, 012, TO_DATE('12/31/2017', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('A5612159906725', 011, 013, TO_DATE('05/03/2018', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('G1795491958195', 001, 005, TO_DATE('09/19/2018', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('C4226049102485', 003, 017, TO_DATE('01/30/2019', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('H7392012486669', 004, 016, TO_DATE('04/19/2019', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('C4226019502960', 005, 025, TO_DATE('04/08/2019', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('A5612015829202', 007, 022, TO_DATE('06/08/2019', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('C7005019502052', 009, 026, TO_DATE('06/08/2019', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('C7005768590202', 009, 021, TO_DATE('10/08/2019', 'MM/DD/YYYY'), 'EB-3');
INSERT INTO M_PETITION VALUES
('A5612019502058', 002, 025, TO_DATE('11/08/2019', 'MM/DD/YYYY'), 'EB-2');
INSERT INTO M_PETITION VALUES
('G1795188594020', 003, 023, TO_DATE('11/08/2019', 'MM/DD/YYYY'), 'EB-2');
SELECT * FROM M_PETITION;
--DEMONSTRATE SAVEPOINT
SAVEPOINT PETITIONSAVEPOINTA;
--CREATED A SAVEPOINT
--UPDATING MULTIPLE ROWS
UPDATE  M_PETITION
    SET PETITIONTYPE = 'EB-4'
    WHERE PETITIONID = 'C7005019502052';
UPDATE  M_PETITION
    SET PETITIONTYPE = 'EB-4'
    WHERE PETITIONID = 'H7392048445617';
--SAVEPOINT INITIATED    
SAVEPOINT PETITIONSAVEPOINTB;

--RUN A REPORT
SELECT * FROM M_PETITION;
--ROLLBACK TO PREVIOUS PETITION
ROLLBACK TO PETITIONSAVEPOINTA;
--SHOW UPDATED PETITION WITH ROLLBACK
SELECT * FROM M_PETITION;
--MAKE AN UPDATE
UPDATE  M_PETITION
    SET PETITIONTYPE = 'H1B'
    WHERE PETITIONID = 'C7005019502052';
UPDATE  M_PETITION
    SET PETITIONTYPE = 'H1B'
    WHERE PETITIONID = 'H7392048445617';
SELECT * FROM M_PETITION;
--SAVE NEW CHANGES TO SAVEPOINT
SAVEPOINT PETITIONSAVEPOINTA;
--COMMIT NEW CHANGES
COMMIT;



--CREATING VALUES FOR DECISIONS
-- USE INSERT ALL FUNCTION
INSERT ALL INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 001, 'A5612385486122', 'DENIED', 'EDUCATION', TO_DATE('12/15/2015', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 002, 'G1795602359172', 'DENIED', 'EXPERIENCE', TO_DATE('01/12/2016', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES (003, 'C4226854930284', 'DENIED', 'EXPERIENCE', TO_DATE('03/15/2016', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES (004, 'C7005988363131', 'ACCEPTED', 'GRANTED', TO_DATE('10/01/2016', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES (005, 'H7392048445617', 'ACCEPTED', 'GRANTED', TO_DATE('01/05/2017', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES (006, 'H7392620581025', 'DENIED', 'FINANCIAL', TO_DATE('01/25/2017', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 007, 'C7005612109941', 'DENIED', 'FINANCIAL', TO_DATE('12/1/2017', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 008, 'C4226065829422', 'DENIED', 'EXPERIENCE', TO_DATE('02/14/2018', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 009, 'G1795112049502', 'ACCEPTED', 'GRANTED', TO_DATE('02/14/2018', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 010, 'G1795198294050', 'DENIED', 'EDUCATION', TO_DATE('06/28/2018', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 011, 'C4226112579204', 'DENIED', 'EDUCATION', TO_DATE('12/20/2018', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 012, 'A5612015829202', 'ACCEPTED', 'GRANTED', TO_DATE('11/03/2019', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 013, 'C7005019502052', 'DENIED', 'FINANCIAL', TO_DATE('11/03/2019', 'MM/DD/YYYY'))
INTO M_DECISION (DecisionID, PetitionID, Decision, Reason, Decided) VALUES ( 014, 'C4226029402029', 'DENIED', 'EDUCATION', TO_DATE('12/15/2019', 'MM/DD/YYYY'))
SELECT * FROM DUAL;

SELECT * 
FROM M_DECISION;

--RESET FOR VIEWS
DROP VIEW M_CURRENTWAGE;
DROP VIEW M_ALLFILINGS;
DROP VIEW M_EXPIRWAGE;
DROP VIEW M_EXPIRDOCS;
DROP VIEW M_SPONSORED;

--THIS VIEW SHOWS ALL WAGES WITH FACILITY AND SALARY
-- IT IS NECESSARY TO REFER TO THIS VIEW WHEN FILING A PETITION TO KEEP TRACK OF SALARY INVENTORY
--IT MERGES THE FACILITY AND WAGE TABLE
CREATE VIEW M_CURRENTWAGE
    AS SELECT FACINAME, FACISTATE, WDEGREE, EXPIRATION, TO_CHAR(SALARY, '$999,999.99') "SALARY"
    FROM M_WAGE, M_FACILITY
    WHERE M_WAGE.FACIID = M_FACILITY.FACIID
    ORDER BY EXPIRATION ASC;
--PULLS ALL FILINGS MADE
--SHOWS ALSO THE BENEFICIARY OF EACH PETITION
--EACH PETITION CAN ONLY HAVE I-140 DOCUMENT WHEN REFERRED TO
CREATE VIEW M_ALLFILINGS
    AS SELECT M_PETDOCS.PETEID, M_BENEFICIARY.BNAME, M_PETITION.PETITIONTYPE, M_PETITION.FILEDDATE
    FROM M_BENEFICIARY, M_PETITION, M_DOCS, M_PETDOCS
    WHERE M_BENEFICIARY.BeneID = M_DOCS.BeneID and M_DOCS.DOCSID = M_PETDOCS.DOCSID AND M_PETDOCS.PETEID = M_PETITION.PETITIONID AND M_DOCS.DOCTYPE= 'I-140'
    ORDER BY FILEDDATE ASC;


--THESE ARE THE AVAILABLE WAGES FOR FUTURE FILINGS
--STATES WHERE THEY CAN INITATE A PETITION THAT HASNT EXPIRED YET
--THIS IS USED TO WHEN TO INITATE A PETITION
CREATE VIEW M_EXPIRWAGE
    AS SELECT FACISTATE, WDEGREE,TO_CHAR(SALARY, '$999,999.99') "SALARY"
    FROM M_FACILITY, M_WAGE
    WHERE M_FACILITY.FACIID=M_WAGE.FACIID AND EXPIRATION >SYSDATE;

--SOME DOCUMENTS SUBMITTED BY A BENEFICIARY MAY EXPIRE SUCH AS A LICENSE AND PERMIT
--THIS PROVIDES A FUTURE REFERENCE TO DETERMINE WHEN THEY ARE EXPIRING
CREATE VIEW M_EXPIRDOCS
    AS SELECT BNAME, DOCSID, DOCTYPE, DOCEXPIRATION
    FROM M_BENEFICIARY, M_DOCS
    WHERE M_BENEFICIARY.BENEID=M_DOCS.BENEID AND DOCEXPIRATION >SYSDATE
    ORDER BY DOCEXPIRATION ASC;
--AFTER SHOWING THE BENEFICIARY, THIS KEEPS TRACK OF THOSE WHO SPONSORED THE PETITION
--SHOWS THE SPONSOR OF EACH PETITION
CREATE VIEW M_SPONSORED
    AS SELECT SPONSORNAME, PETITIONID, FILEDDATE, PETITIONTYPE
    FROM M_PETITION
    INNER JOIN M_SPONSOR
    ON M_PETITION.SPONSORID=M_SPONSOR.SPONSORID; 
    
--USING RPAD
--USES COUNT FUNCTIONS
--FIRST 5 ALPHANUMERIC REFERS TO THE LOCATION OF WHERE IT WAS FILED
SELECT RPAD(PETITIONID, 5) AS CODE, COUNT(PETITIONID) AS TOTAL
FROM M_PETITION
GROUP BY RPAD(PETITIONID,5);

--VIEW ALL APPLICABLE WAGES
--GROUP BY ROLLUP
--USES MIN FUNCTIONS
--SUMMARIZES ALL WAGES THAT CAN BE PAID TO AN APPLICANT MINIUM BY STATE AND BY DEGREE
SELECT  WDEGREE,FACISTATE, MIN(SALARY)
FROM M_CURRENTWAGE
GROUP BY ROLLUP (WDEGREE,FACISTATE);
--VIEW ALL APPLICABLE WAGES
--GROUP BY ROLLUP
--USES MIN FUNCTIONS
--SUMMARIZES ALL WAGES THAT CAN BE PAID TO AN APPLICANT MAXIMUM BY STATE AND BY DEGREE
SELECT  WDEGREE, FACISTATE, MAX(SALARY)
FROM M_CURRENTWAGE
GROUP BY ROLLUP (WDEGREE, FACISTATE);

--USE TO CHAR TO LOOKUP UPCOMING EXPIRATION
--UPCOMING WAGES THAT ARE EXPIRING AND WHAT MONTH ARE THEY
SELECT FACINAME, TO_CHAR(EXPIRATION, 'MON, YYYY')
FROM M_CURRENTWAGE
WHERE EXPIRATION >sysdate;


--USE DECODE FUNCTION
--RUNS A QUERY OF WHICH STATE THE PETITION WAS FILED
SELECT BNAME,DECODE(RPAD(PETEID, 5), 'A5612', 'NEW YORK',
                            'G1795', 'CALIFORNIA',
                            'C4226', 'ILLINOIS',
                            'C7005', 'TEXAS',
                            'H7392', 'DAKOTA') AS STATE
FROM M_ALLFILINGS;   

--SHOW ALL FACILITIES WITHOUT A WAGE DETERMINATION YET
-- USES OUTER JOIN
SELECT M_FACILITY.FACIID, FACINAME, FACIADDRESS, FACICITY, FACISTATE, FACIZIPCODE
FROM  M_WAGE 
RIGHT OUTER JOIN M_FACILITY
ON M_WAGE.FACIID = M_FACILITY.FACIID
WHERE M_WAGE.FACIID IS NULL
ORDER BY M_FACILITY.FACIID ASC
;

--USES ROUND FUNCTION
--USES AVERAGE FUNCTION
--USES HAVING FUNCTION
--USES GROUP BY
--USES JOIN
--DETERMINES AVERAGE SALARY PER STATE THAT IS HIGHER THAN ALL THE AVERAGE
--USES A SUBQYERY
SELECT FACISTATE, ROUND(AVG(SALARY),2)
FROM M_WAGE
INNER JOIN M_FACILITY
ON M_WAGE.FACIID = M_FACILITY.FACIID
GROUP BY FACISTATE
HAVING AVG(SALARY)>=(SELECT ROUND(AVG(SALARY),2) FROM M_WAGE);


--USE A SUBQUERY TO LOOKUP SPONSORS WHO ARE EQUAL OR HIGHER THAN THE AVERAGE
--THE NUMBER OF PETITIONS THAT A SPONSOR HANDLED
--USES HAVING
--USES A SUBQUERY TO DETERMINE THE AVERAGE
--ALSO USES A COUNT FUNCTION IN THE SUBQUERY
SELECT SPONSORNAME, COUNT(SPONSORNAME)
FROM M_SPONSORED
GROUP BY SPONSORNAME
HAVING COUNT(SPONSORNAME)>=(SELECT AVG(COUNT(SPONSORNAME)) FROM M_SPONSORED GROUP BY SPONSORNAME);

--USING RPAD BY CONSTRUCTING A MINI REPORT OF THE CONTROL NUMBER OF EACH PETITION
SELECT RPAD(BNAME, 50,'.'),PETEID AS CONTROL
FROM M_ALLFILINGS;

--TOCHAR FUNCTION
--REFERS TO THE AMOUNT OF MONTHS LEFT TO AN EXPIRING DOCUMENT
--ROUNDED TO 2 DECIMAL POINTS
SELECT BNAME, DOCTYPE, TO_CHAR(DOCEXPIRATION,'MONTH')AS MONTH, ROUND(MONTHS_BETWEEN(DOCEXPIRATION, SYSDATE),2) AS MONTHS_REMAINING
FROM M_EXPIRDOCS;



SAVEPOINT CLASSC;
ROLLBACK TO CLASSC;

--USING MERGE
drop table M_SPONSOR2;
--CREATING A TABLE
--HAS SIMILAR VALUES TO M_SPONSOR TABLE
CREATE TABLE M_SPONSOR2 (
    Sponsor2ID int NOT NULL PRIMARY KEY,
    Sponsor2Name VARCHAR2(50) NOT NULL,
    Sponsor2Agency VARCHAR2(100),
    Sponsor2Contact VARCHAR2(12)
);
--INSERT VALUES TO THIS TABLE
INSERT INTO M_SPONSOR2 VALUES
(009, 'MATTHEW REEVES', 'RELIANCE ASSOC', '9905550900');
INSERT INTO M_SPONSOR2 VALUES
(013, 'EMILY WAZOW', NULL, '4615554220');
INSERT INTO M_SPONSOR2 VALUES
(014, 'HAPPY LLANG', 'WORLDWIDE HEALTH', '1275558713');
--MERGES THE TABLE
--UPDATES WHEN IT FINDS A SAME VALUE
--WHEN DOESN'T MATCH, IT ADDS THOSE VALUES TO M_SPONSOR
MERGE INTO M_SPONSOR USING M_SPONSOR2 on (M_SPONSOR.SPONSORID=M_SPONSOR2.Sponsor2ID)
WHEN MATCHED THEN UPDATE SET M_SPONSOR.SPONSORNAME = M_SPONSOR2.SPONSOR2NAME
WHEN NOT MATCHED THEN INSERT (M_SPONSOR.SPONSORID,M_SPONSOR.SPONSORNAME, M_SPONSOR.SPONSORAGENCY, M_SPONSOR.SPONSORCONTACT) VALUES (M_SPONSOR2.SPONSOR2ID,M_SPONSOR2.SPONSOR2NAME,M_SPONSOR2.SPONSOR2AGENCY,M_SPONSOR2.SPONSOR2CONTACT);

--CREATING AN INDEX
CREATE INDEX M_FACILITY2
ON M_WAGE(WDEGREE);