--a5pq2g96
--drop sequence SQ_NEW_GR_APP
--commit
--Create sequence 
create sequence SQ_NEW_GR_APP
minvalue 1
maxvalue 999999999999999999999999999
start with 5000000
increment by 1
cache 20
order;

-- Create Cache table
create table GR_JOB_CACHE
(
  GR_JOB_ID           NUMBER(38) not null,
  GR_ADVERTISEMENT_ID NUMBER(38) not null,
  GR_ADVERTISEMENT_NO VARCHAR2(50) not null,
  CASE_NO             VARCHAR2(25),
  JOB_TITLE           VARCHAR2(154),
  SUBJECT_NAME        VARCHAR2(150),
  GR_DEPARTMENT_NAME  VARCHAR2(100),
  MINISTRY_NAME       VARCHAR2(2000),
  GRADE               VARCHAR2(43),
  GR_JOB_SPAY         VARCHAR2(150),
  GR_JOB_DUTIES       VARCHAR2(3000),
  JOB_STATUS          VARCHAR2(160),
  ELIG                VARCHAR2(45),
  GR_AGE_RELAXATION   VARCHAR2(100),
  GR_JOB_MIN_AGE      NUMBER(20),
  GR_JOB_MAX_AGE      NUMBER(20),
  GR_JOB_EXP          VARCHAR2(2000),
  GR_NOTE             VARCHAR2(2000)
)

--drop table gr_job_cache
-- Create Cache table with data
create table gr_job_cache as
(
select jp.gr_job_id, pa.gr_advertisement_id, pa.gr_advertisement_no, 
'F.4-'||jp.gr_case_no||'/'||trim(to_char(jp.GR_JOB_POST_DATE,'YYYY'))||'-R ' as case_no,
jp.gr_job_title as job_title, js.gr_sub_name as subject_name, md.GR_DEPARTMENT_NAME, m.ministry_name, 'BS-'||jp.gr_job_grade grade, jp.gr_job_spay, jp.gr_job_duties, 
decode(jp.gr_job_status,1,'Permanent',2,'Temporary ')||jp.gr_job_tperiod job_status, 
decode(jp.GR_ELIGIBLE_GENDER,'BOTH','Both male and female candidates are eligible ','MALE','Only male candidates are eligible',
'FEMALE','Only female candidates are eligible') as ELIG, jp.gr_age_relaxation, jp.gr_job_min_age, jp.gr_job_max_age, 
jp.gr_job_exp, jn.gr_note
from gr_job_post jp, gr_ministries m, gr_min_dept md, gr_job_subs js, gr_job_sub_depend jsd, gr_job_note jn, gr_post_advertisement pa
where jp.gr_job_ministry = m.ministry_id
and jp.gr_job_dept = md.gr_department_id
and jp.gr_job_id = jn.gr_job_id(+)
and m.ministry_id = md.gr_ministry_id
and jsd.gr_dep_job_id(+) = jp.gr_job_id
and jsd.gr_sub_id = js.gr_sub_id(+)
and jp.gr_advert_id = pa.gr_advertisement_id
and pa.gr_advertisement_id = 1798
)


--create table
-- drop table NEW_GR_APP commit
create table NEW_GR_APP
(
  GR_APPLICANT_ID        NUMBER(38) not null,
  GR_APPLICANT_NAME      VARCHAR2(100) not null,
  GR_FATHER_NAME         VARCHAR2(100) not null,
  GR_DOB                 DATE not null,
  GR_NEW_NIC             VARCHAR2(15) not null,
  GR_GENDER              VARCHAR2(20) not null,
  GR_RELIGION            VARCHAR2(20),
  GR_POSTAL_ADD          VARCHAR2(200),
  GR_APPLICANT_EMAIL     VARCHAR2(50),
  GR_APPLICANT_PHONE_OFF VARCHAR2(20),
  GR_APP_MOBILE          VARCHAR2(25),
  GR_AGEREX1             VARCHAR2(10),
  GR_RELAX_YEARS         NUMBER(2) default 0,
  GR_PHOTO               BLOB,
  GR_DOMICILE            NUMBER(30),
  GR_DISTRICT_DOMICILE   NUMBER(30),
  TOTAL_AGE              NUMBER(30) default 0,
  GR_DISABILITY          VARCHAR2(2),
  GR_HELPER              VARCHAR2(4),
  GR_JOB_ID              NUMBER(30) not null,
  TR_DATE                DATE,
  GR_AMOUNT              NUMBER(5),
  GR_ROLL_NO             VARCHAR2(10),
  DATE_APPLIED           DATE not null,
  GR_CENTER_ID           NUMBER(3),
  GR_CENTER_NAME         VARCHAR2(50),
  GR_INTERVIEW_ID        NUMBER(3),
  GR_INTERVIEW_NAME      VARCHAR2(50),
  GR_REQUIRED_QUALEXP    VARCHAR2(3),
  GR_RDD                 DATE,
  GR_ADVERTISEMENT_ID    NUMBER(10),
  GR_BANK_BRANCH_NAME    VARCHAR2(200),
  GR_BANK_CODE           VARCHAR2(50),
  GR_BANK_DISTRICT       NUMBER(38),
  GR_EXAMINATION_ID1     NUMBER(38),
  GR_EXAMINATION_NAME1   VARCHAR2(100),
  GR_RESULT_DATE1        DATE,
  GR_GRADE1              VARCHAR2(4),
  GR_DIVISION1           VARCHAR2(10),
  GR_CGP1                VARCHAR2(10),
  GR_UNIVERSITY_ID1      NUMBER(38),
  GR_UNIVERSITY_NAME1    VARCHAR2(100),
  GR_BOARD_OTHER1        VARCHAR2(100),
  GR_MAJOR_COURSE_WORKS1 VARCHAR2(100),
  GR_EXAMINATION_ID2     NUMBER(38),
  GR_EXAMINATION_NAME2   VARCHAR2(100),
  GR_RESULT_DATE2        DATE,
  GR_GRADE2              VARCHAR2(4),
  GR_DIVISION2           VARCHAR2(10),
  GR_CGP2                VARCHAR2(10),
  GR_UNIVERSITY_ID2      NUMBER(38),
  GR_UNIVERSITY_NAME2    VARCHAR2(100),
  GR_BOARD_OTHER2        VARCHAR2(100),
  GR_MAJOR_COURSE_WORKS2 VARCHAR2(100),
  GR_EXAMINATION_ID3     NUMBER(38),
  GR_EXAMINATION_NAME3   VARCHAR2(100),
  GR_RESULT_DATE3        DATE,
  GR_GRADE3              VARCHAR2(4),
  GR_DIVISION3           VARCHAR2(10),
  GR_CGP3                VARCHAR2(10),
  GR_UNIVERSITY_ID3      NUMBER(38),
  GR_UNIVERSITY_NAME3    VARCHAR2(100),
  GR_BOARD_OTHER3        VARCHAR2(100),
  GR_MAJOR_COURSE_WORKS3 VARCHAR2(100),
  GR_EXAMINATION_ID4     NUMBER(38),
  GR_EXAMINATION_NAME4   VARCHAR2(100),
  GR_RESULT_DATE4        DATE,
  GR_GRADE4              VARCHAR2(4),
  GR_DIVISION4           VARCHAR2(10),
  GR_CGP4                VARCHAR2(10),
  GR_UNIVERSITY_ID4      NUMBER(38),
  GR_UNIVERSITY_NAME4    VARCHAR2(100),
  GR_BOARD_OTHER4        VARCHAR2(100),
  GR_MAJOR_COURSE_WORKS4 VARCHAR2(100),
  GR_EXAMINATION_ID5     NUMBER(38),
  GR_EXAMINATION_NAME5   VARCHAR2(100),
  GR_RESULT_DATE5        DATE,
  GR_GRADE5              VARCHAR2(4),
  GR_DIVISION5           VARCHAR2(10),
  GR_CGP5                VARCHAR2(10),
  GR_UNIVERSITY_ID5      NUMBER(38),
  GR_UNIVERSITY_NAME5    VARCHAR2(100),
  GR_BOARD_OTHER5        VARCHAR2(100),
  GR_MAJOR_COURSE_WORKS5 VARCHAR2(100),
  GR_POST_NAME1          VARCHAR2(100),
  GR_ORGANIZATION_NAME1  VARCHAR2(200),
  GR_EXP_FROM_DATE1      DATE,
  GR_EXP_TO_DATE1        DATE,
  GR_JOB_STATUS1         VARCHAR2(100),
  GR_MAIN_DUTIES1        VARCHAR2(500),
  GR_JOB_NATURE1         VARCHAR2(20),
  GR_BPS1                VARCHAR2(20),
  GR_POST_NAME2          VARCHAR2(100),
  GR_ORGANIZATION_NAME2  VARCHAR2(200),
  GR_EXP_FROM_DATE2      DATE,
  GR_EXP_TO_DATE2        DATE,
  GR_JOB_STATUS2         VARCHAR2(100),
  GR_MAIN_DUTIES2        VARCHAR2(500),
  GR_JOB_NATURE2         VARCHAR2(20),
  GR_BPS2                VARCHAR2(20),
  GR_POST_NAME3          VARCHAR2(100),
  GR_ORGANIZATION_NAME3  VARCHAR2(200),
  GR_EXP_FROM_DATE3      DATE,
  GR_EXP_TO_DATE3        DATE,
  GR_JOB_STATUS3         VARCHAR2(100),
  GR_MAIN_DUTIES3        VARCHAR2(500),
  GR_JOB_NATURE3         VARCHAR2(20),
  GR_BPS3                VARCHAR2(20),
  GR_POST_NAME4          VARCHAR2(100),
  GR_ORGANIZATION_NAME4  VARCHAR2(200),
  GR_EXP_FROM_DATE4      DATE,
  GR_EXP_TO_DATE4        DATE,
  GR_JOB_STATUS4         VARCHAR2(100),
  GR_MAIN_DUTIES4        VARCHAR2(500),
  GR_JOB_NATURE4         VARCHAR2(20),
  GR_BPS4                VARCHAR2(20),
  GR_POST_NAME5          VARCHAR2(100),
  GR_ORGANIZATION_NAME5  VARCHAR2(200),
  GR_EXP_FROM_DATE5      DATE,
  GR_EXP_TO_DATE5        DATE,
  GR_JOB_STATUS5         VARCHAR2(100),
  GR_MAIN_DUTIES5        VARCHAR2(500),
  GR_JOB_NATURE5         VARCHAR2(20),
  GR_BPS5                VARCHAR2(20)
)

-- Drop all constraints 
alter table NEW_GR_APP_OLD drop constraint PK_NEW_GR_APP;
alter table NEW_GR_APP_OLD drop constraint UNIQUE_NEW_GR_APP;
alter table NEW_GR_APP_OLD drop constraint CHK2_NEW_GR_APP_APPNAME;
alter table NEW_GR_APP_OLD drop constraint CHK2_NEW_GR_APP_CNIC;
alter table NEW_GR_APP_OLD drop constraint CHK2_NEW_GR_APP_EMAIL;
alter table NEW_GR_APP_OLD drop constraint CHK2_NEW_GR_APP_FNAME;
alter table NEW_GR_APP_OLD drop constraint CHK2_NEW_GR_APP_MOBILE;

-- Create/Recreate primary, unique and foreign key constraints
alter table NEW_GR_APP
  add constraint PK_NEW_GR_APP primary key (GR_APPLICANT_ID)
 ;
alter table NEW_GR_APP
  add constraint UNIQUE_NEW_GR_APP unique (GR_NEW_NIC, GR_JOB_ID)
 ;
-- Create/Recreate check constraints 
alter table NEW_GR_APP
  add constraint CHK2_NEW_GR_APP_APPNAME
  check (GR_APPLICANT_NAME NOT LIKE ' %' AND GR_APPLICANT_NAME NOT LIKE '% ');
alter table NEW_GR_APP
  add constraint CHK2_NEW_GR_APP_CNIC
  check (GR_NEW_NIC LIKE '_____-_______-_' AND GR_NEW_NIC NOT LIKE '% %' AND SUBSTR(GR_NEW_NIC,1,5)>=0 AND SUBSTR(GR_NEW_NIC,7,7)>=0 AND SUBSTR(GR_NEW_NIC,15) >=0);
alter table NEW_GR_APP
  add constraint CHK2_NEW_GR_APP_EMAIL
  check (GR_APPLICANT_EMAIL NOT LIKE '% %' AND GR_APPLICANT_EMAIL LIKE '%@%.%');
alter table NEW_GR_APP
  add constraint CHK2_NEW_GR_APP_FNAME
  check (GR_FATHER_NAME NOT LIKE ' %' AND GR_FATHER_NAME NOT LIKE '% ');
alter table NEW_GR_APP
  add constraint CHK2_NEW_GR_APP_MOBILE
  check (GR_APP_MOBILE LIKE '92%' AND LENGTH(GR_APP_MOBILE)=12 AND GR_APP_MOBILE >=0);
  commit
