--function getLastSecurityBriefingDate(p_person in number) return date;
function getLastSecurityBriefingDate(p_person in number) return date is
    v_wbs_person_presence_id number(10);
    v_last_briefing_date date;
    begin
        select max(wpr.wbs_person_presence_id)
        into v_wbs_person_presence_id
        from wbs_person_presence wpr
        where wpr.person_id = p_person_id;
        exception when no_data_found then
        return null;
    end;
    begin
        select wpr.presence_date
        into v_last_briefing_date
        from wbs w, wbs_person_presence wpr
        where wpr.wbs_person_presence_id = v_wbs_person_presence_id
        and w.wbs_id = wpr.wbs_id;
        exception when no_data_found then
        return null;
    end;
    return v_last_briefing_date;
end;

--function getLastSecurityBriefingWBSStr (p_person in number) return varcha2;
function getLastSecurityBriefingWBSStr (p_person in number) return varchar2 is
    v_wbs_person_presence_id number(10);
    v_last_briefing_wbs varchar2(200);
    begin
        select max(wpr.wbs_person_presence_id)
        into v_wbs_person_presence_id
        from wbs_person_presence wpr
        where wpr.person_id = p_person_id;
        exception when no_data_found then
        return null;
    end;
    begin
        select wbs.name || ' ' || wbs.ref_number
        into v_last_briefing_wbs
        from wbs w, wbs_person_presence wpr
        where wpr.wbs_person_presence_id = v_wbs_person_presence_id
        and w.wbs_id = wpr.wbs_id;
        exception when no_data_found then
        return null;
    end;
    return v_wbs_last_briefing_wbs;
end;
/


--  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OF_OWNER"."V_ORDS_GET_PERSON_LIST" ("PERSON_ID", "VEHICLE_OWNER_ID", "VO_PERSON_CATEGORY_ID", "FIRST_NAME", "LAST_NAME", "AGE", "BIRTH_DATE", "GENDER", "LANGUAGE", "COMPANY_NR", "PHONE", "MSISDN", "LIMOSA", "NISS", "EMAIL", "HOME_POI_ID", "STREET", "STREET_NR", "LOCATION", "POST_CODE", "COUNTRY", "ADDITIONAL_ADRESS_INFO", "WORKING_SCHEDULE_ID", "JOB_TITLE", "OC_CALENDAR_ID", "PERSON_STATUS_ID", "START_WORK_DATE", "END_WORK_DATE", "LAST_ATTACH_DOC_ADMIN_SHARING_DATE", "LAST_ATTACH_DOC_ADMIN_SHARING_MC_ID", "LAST_SO_TRANSACTION_ID", "HIRING_AUTHORIZATION_DATE", "HIRING_AUTHORIZATION_REQUEST_DATE", "FIRING_AUTHORIZATION_DATE", "FIRING_AUTHORIZATION_REQUEST_DATE", "VO_DIVISION_ID", "REAL_EMPLOYER_NAME", "REAL_EMPLOYER_TIN", "REAL_EMPLOYER_ADMIN_REGION_ID", "RANK1_SC_NAME", "RANK1_SC_TIN", "RANK1_SC_ADMIN_REGION_ID", "PERSON_ADMIN_REGION_ID", "ADMIN_STATUS_CATEGORY_ID", "IS_ADMIN_STATUS_OK", "IS_BLACKLISTED", "NATIONALITY_CODE_ID", "EMPLOYEE_CONTRACT_TYPE_ID", "VO_EMPLOYEE_CONTRACT_TYPE_ID", "LM_NAME", "LM_DATE", "LM_COMMENT", "IS_CROSS_BORDER_WORKER", "COMPANY_NAME", "COMPANY_TIN", "ALL_ADMIN_ATTACH_DOC_VALIDATED", "ADMIN_PROFILE_ID", "LAST_CAW_POI_NAME", "LAST_CAW_POI_TIN", "LAST_CAW_REF_NUMBER", "LAST_CAW_CREATION_DATE", "LAST_CAW_WORK_DATE", "LAST_CAW_STATUS", "SO_TRANSACTION_STATUS_ID", "LAST_CAW_LM_DATE", "LAST_CAW_COMMENT", "SOT_REMARK_EXIST", "ADMIN_PROOF_DOC", "ADMIN_PROOF_CATEGORY_VALIDITIES", "RE_VALIDATION_STATUS", "SC_VALIDATION_STATUS","MANAGER1_FIRST_NAME","MANAGER1_LAST_NAME","MANAGER1_COMPANY_NR","MANAGER2_FIRST_NAME","MANAGER2_LAST_NAME","MANAGER2_COMPANY_NR","PERSON_LAST_MED_VISIT_DATE","LAST_MED_VISIT_STATUS_ID","PERSON_LAST_MED_VISIT_STATUS","LAST_SECURITY_BRIEFING_DATE","LAST_SECURITY_BRIEFING_WBS") AS
  select
p.person_id,
p.vehicle_owner_id,
vpc.vo_person_category_id,
p.first_name,
p.last_name,
p.age,
p.birth_date,
p.gender,
p.language,
p.company_nr,
p.phone,
p.msisdn,
p.limosa,
p.niss,
p.email,
p.home_poi_id,
home.street,
home.street_nr,
home.location,
home.post_code,
home.country,
home.additional_adress_info,
p.working_schedule_id,
p.job_title,
p.oc_calendar_id,
p.person_status_id,
p.start_work_date,
p.end_work_date,
p.last_attach_doc_admin_sharing_date,
p.last_attach_doc_admin_sharing_mc_id,
p.last_so_transaction_id,
p.hiring_authorization_date,
p.hiring_authorization_request_date,
p.firing_authorization_date,
p.firing_authorization_request_date,
p.vo_division_id,

decode(p.real_employer_tin, 'Double entry', 'Double entry', 'Unknown', 'Unknown', 'UNKNOWN', 'Unknown', 'unknown', 'Unknown', re_vt.real_employer_name) real_employer_name,
decode(p.real_employer_tin, 'Double entry', 'Double entry', nvl(re_vt.tin, p.real_employer_tin)) real_employer_tin,
cast(decode(p.real_employer_tin, 'Double entry', null, 'Unknown', null, 'UNKNOWN', null, 'unknown', null, re_vt.admin_region_id) as number(10)) real_employer_admin_region_id,

decode(p.rank1_sc_tin, 'Double entry', 'Double entry', 'Unknown', 'Unknown', 'UNKNOWN', 'Unknown', 'unknown', 'Unknown', sc_vt.real_employer_name) rank1_sc_name,
decode(p.rank1_sc_tin, 'Double entry', 'Double entry', nvl(sc_vt.tin, p.rank1_sc_tin)) rank1_sc_tin,
cast(decode(p.rank1_sc_tin, 'Double entry', null, 'Unknown', null, 'UNKNOWN', null, 'unknown', null, sc_vt.admin_region_id) as number(10)) rank1_sc_admin_region_id,

p.person_admin_region_id,
p.admin_status_category_id,
p.is_admin_status_ok,
p.is_blacklisted,
p.nationality_code_id,
p.employee_contract_type_id,
p.vo_employee_contract_type_id,
p.lm_name,
p.lm_date,
p.lm_comment,
p.is_cross_border_worker,
vo.company_name,
vo.company_tin,
p.all_admin_attach_doc_validated,
ap.admin_profile_category_id admin_profile_id,
ocp.name last_caw_poi_name,
wbs.poi_tin last_caw_poi_tin,
wbs.ref_number last_caw_ref_number,
sot.creation_date last_caw_creation_date,
sot.work_date last_caw_work_date,
sots.name last_caw_status,
sots.so_transaction_status_id,
sot.lm_date last_caw_lm_date,
sot.so_comment last_caw_comment,
sot.sot_remark_exist,
(select json_arrayagg(json_object(vapd.admin_profile_category_id,
                                  vapd.admin_proof_category_id,
                                  vapd.admin_proof_category_name,
                                  vapd.admin_proof_short_name,
                                  vapd.admin_proof_id,
                                  vapd.document_type_name,
                                  vapd.document_type_id,
                                  vapd.is_admin_proof_doc_valid) returning clob)
        from v_admin_proof_doc vapd
        where vapd.person_id = p.person_id) admin_proof_doc,
              json_array(json_object('admin_proof_category_id' value 1,
                       'valid' value p_person.isAdminProofCatValid(1, p.person_id, trunc(sysdate))),
              json_object('admin_proof_category_id' value 2,
                       'valid' value p_person.isAdminProofCatValid(2, p.person_id, trunc(sysdate))),
              json_object('admin_proof_category_id' value 3,
                       'valid' value p_person.isAdminProofCatValid(3, p.person_id, trunc(sysdate))),
              json_object('admin_proof_category_id' value 4,
                       'valid' value p_person.isAdminProofCatValid(4, p.person_id, trunc(sysdate)))
              ) admin_proof_category_validities,
re_vnv.vat_validation_status re_validation_status,
sc_vnv.vat_validation_status sc_validation_status,
manager1.first_name manager1_first_name,
manager1.last_name manager1_last_name,
manager1.company_nr manager1_company_nr,
manager2.first_name manager2_first_name,
manager2.last_name manager2_last_name,
manager2.company_nr manager2_company_nr,
p.last_med_visit_date person_last_med_visit_date,
p.last_med_visit_status_id,
last_medical_visit_status.name person_last_med_visit_status,
p_person.getLastSecurityBriefingDate(p.person_id) last_security_briefing_date,
p_person.getLastSecurityBriefingWBSStr(p.person_id) last_security_briefing_wbs
from vo_person_category vpc,
     oc_poi home,
     vehicle_owner vo,
     person p,
     social_office_transaction sot,
     wbs,
     oc_poi ocp,
     so_transaction_status sots,
     vo_tin re_vt,
     vo_tin sc_vt,
     vat_number_validation re_vnv,
     vat_number_validation sc_vnv,
     admin_profile ap,
     person manager1,
     person manager2,
     medical_visit_status last_medical_visit_status
where vpc.vo_person_category_id = p.vo_person_category_id
and home.poi_id = p.home_poi_id
and vo.vehicle_owner_id = vpc.vehicle_owner_id
and sot.social_office_transaction_id (+) = p.last_so_transaction_id
and wbs.wbs_id (+) = sot.wbs_id
and ocp.poi_id (+) = wbs.poi_id
and sots.so_transaction_status_id (+) = sot.so_transaction_status_id
and re_vt.tin (+) = p.real_employer_tin
and re_vt.vehicle_owner_id (+) = vo.vehicle_owner_id
and p.admin_profile_id = ap.admin_profile_id (+)
--and nvl(p.person_status_id,0) <> 2

and decode(SUBSTR(re_vnv.vat_number (+), 1,3), 'BE0', SUBSTR(re_vnv.vat_number (+), 4), re_vnv.vat_number (+)) = p.real_employer_tin
and decode(SUBSTR(sc_vnv.vat_number (+), 1,3), 'BE0', SUBSTR(sc_vnv.vat_number (+), 4), sc_vnv.vat_number (+)) = p.rank1_sc_tin

and sc_vt.tin (+) = p.rank1_sc_tin
and sc_vt.vehicle_owner_id (+) = vo.vehicle_owner_id
and p.manager_person_id = manager1.person_id(+)
and p.manager2_person_id = manager2.person_id(+)
and p.last_med_visit_status_id = last_medical_visit_status.medical_visit_status_id(+);


--  GRANT SELECT ON "OF_OWNER"."V_ORDS_GET_PERSON_LIST" TO "OF_PBI";