CREATE OR REPLACE FORCE EDITIONABLE VIEW "OF_OWNER"."V_PERSON_STRING" ("PERSON_ID", "QUALIFICATION", "GROUP_NAMES", "KEY_STRING", "PLANNED_WBS", "COMPANY_NAME", "PERSON_CERTIFICATES", "PROOF_OF_IDENTITY", "PROOF_OF_RESIDENCE", "PROOF_OF_WORK", "ADMIN_PROFILE_CATEGORY","PERSON_ROLE_LIST","PERSON_ID_STRING") AS
  SELECT PERSON_ID, qualification,  GROUP_NAMES, KEY_STRING, PLANNED_WBS, COMPANY_NAME, PERSON_CERTIFICATES,
       Proof_Of_Identity, Proof_Of_Residence, Proof_Of_Work, Admin_Profile_Category,
       UPPER(PERSON_ID_STRING) || ' ' || UPPER(GROUP_NAMES) || ' ' || KEY_STRING PERSON_ID_STRING
  FROM (
      SELECT
      Person.person_id PERSON_ID,
      P_PERSON.get_master_qualification(person.person_id) AS qualification,
      DECODE(LENGTH(TRANSLATE(TRIM(Person.COMPANY_NR),' 0123456789',' ')), NULL, TO_NUMBER(TRIM(Person.COMPANY_NR)), 0) COMPANY_NR_INT,
      P_PERSON.list_person_group_name(Person.PERSON_ID) GROUP_NAMES,
      P_PERSON.list_person_key_strings(Person.PERSON_ID) KEY_STRING,
      P_PERSON.getPlannedWBS(Person.PERSON_ID) PLANNED_WBS,
      p_person.getCompanyName(Person.person_id) COMPANY_NAME,
      P_PERSON.LIST_PERSON_CERTIFICATES(Person.PERSON_ID) PERSON_CERTIFICATES,
      p_person.isAdminProofCatValid(1, Person.PERSON_ID, TRUNC(SYSDATE)) AS Proof_Of_Identity,
      p_person.isAdminProofCatValid(2, Person.PERSON_ID, TRUNC(SYSDATE)) AS Proof_Of_Residence,
      p_person.isAdminProofCatValid(3, Person.PERSON_ID, TRUNC(SYSDATE)) AS Proof_Of_Work,
      P_PERSON.getAdminProfileCategory(Person.ADMIN_PROFILE_ID) AS Admin_Profile_Category,
      P_PERSON.list_person_roles(Person.PERSON_ID) AS PERSON_ROLE_LIST,
      UPPER(p_person.getCompanyName(Person.person_id) || ' ' ||  Person.PHONE || ' ' || Person.BIRTH_DATE || ' ' || Person.EMAIL
            || ' ' || Person.END_WORK_DATE || ' ' || Person.GENDER || ' ' || Person.MSISDN || ' ' || Person.NISS || ' ' || Person.LIMOSA
            || ' ' || P_PERSON.list_person_roles(Person.PERSON_ID)) PERSON_ID_STRING
      FROM person
      WHERE person.person_status_id IN (0, 1)
    );


CREATE MATERIALIZED VIEW "OF_OWNER"."MV_PERSON_STRING" ("PERSON_ID", "QUALIFICATION", "GROUP_NAMES", "KEY_STRING", "PLANNED_WBS", "COMPANY_NAME", "PERSON_ID_STRING", "PERSON_CERTIFICATES", "PROOF_OF_IDENTITY", "PROOF_OF_RESIDENCE", "PROOF_OF_WORK", "ADMIN_PROFILE_CATEGORY","PERSON_ROLE_LIST")
  SEGMENT CREATION IMMEDIATE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FLEETDATA"   NO INMEMORY
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND START WITH SYSDATE+0 NEXT SYSDATE + 10/1440
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
  AS SELECT PERSON_ID, qualification,  GROUP_NAMES, KEY_STRING, PLANNED_WBS,
COMPANY_NAME, PERSON_ID_STRING,
PERSON_CERTIFICATES, PROOF_OF_IDENTITY, PROOF_OF_RESIDENCE, PROOF_OF_WORK,
ADMIN_PROFILE_CATEGORY,PERSON_ROLE_LIST
FROM v_person_string;

   COMMENT ON MATERIALIZED VIEW "OF_OWNER"."MV_PERSON_STRING"  IS 'snapshot table for snapshot OF_OWNER.MV_PERSON_STRING';