Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement