Advertisement
psi_mmobile

Untitled

Mar 30th, 2023
177
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.72 KB | None | 0 0
  1. function getLastSecurityBriefingDate(p_person in number) return date is
  2. v_wbs_person_presence_id number(10);
  3. v_last_briefing_date date;
  4. begin
  5. select max(wpr.wbs_person_presence_id)
  6. into v_wbs_person_presence_id
  7. from wbs_person_presence wpr
  8. where wpr.person_id = p_person_id;
  9. exception when no_data_found then
  10. return null;
  11. end;
  12. begin
  13. select wpr.presence_date
  14. into v_last_briefing_date
  15. from wbs w, wbs_person_presence wpr
  16. where wpr.wbs_person_presence_id = v_wbs_person_presence_id
  17. and w.wbs_id = wpr.wbs_id;
  18. exception when no_data_found then
  19. return null;
  20. end;
  21. return v_last_briefing_date;
  22. end;
  23.  
  24. function getLastSecurityBriefingWBSStr (p_person in number) return date is
  25. v_wbs_person_presence_id number(10);
  26. v_last_briefing_wbs varchar2(200);
  27. begin
  28. select max(wpr.wbs_person_presence_id)
  29. into v_wbs_person_presence_id
  30. from wbs_person_presence wpr
  31. where wpr.person_id = p_person_id;
  32. exception when no_data_found then
  33. return null;
  34. end;
  35. begin
  36. select wbs.name || ' ' || wbs.ref_number
  37. into v_last_briefing_wbs
  38. from wbs w, wbs_person_presence wpr
  39. where wpr.wbs_person_presence_id = v_wbs_person_presence_id
  40. and w.wbs_id = wpr.wbs_id;
  41. exception when no_data_found then
  42. return null;
  43. end;
  44. return v_wbs_last_briefing_wbs;
  45. end;
  46. /
  47.  
  48.  
  49. -- 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") AS
  50. select
  51. p.person_id,
  52. p.vehicle_owner_id,
  53. vpc.vo_person_category_id,
  54. p.first_name,
  55. p.last_name,
  56. p.age,
  57. p.birth_date,
  58. p.gender,
  59. p.language,
  60. p.company_nr,
  61. p.phone,
  62. p.msisdn,
  63. p.limosa,
  64. p.niss,
  65. p.email,
  66. p.home_poi_id,
  67. home.street,
  68. home.street_nr,
  69. home.location,
  70. home.post_code,
  71. home.country,
  72. home.additional_adress_info,
  73. p.working_schedule_id,
  74. p.job_title,
  75. p.oc_calendar_id,
  76. p.person_status_id,
  77. p.start_work_date,
  78. p.end_work_date,
  79. p.last_attach_doc_admin_sharing_date,
  80. p.last_attach_doc_admin_sharing_mc_id,
  81. p.last_so_transaction_id,
  82. p.hiring_authorization_date,
  83. p.hiring_authorization_request_date,
  84. p.firing_authorization_date,
  85. p.firing_authorization_request_date,
  86. p.vo_division_id,
  87.  
  88. decode(p.real_employer_tin, 'Double entry', 'Double entry', 'Unknown', 'Unknown', 'UNKNOWN', 'Unknown', 'unknown', 'Unknown', re_vt.real_employer_name) real_employer_name,
  89. decode(p.real_employer_tin, 'Double entry', 'Double entry', nvl(re_vt.tin, p.real_employer_tin)) real_employer_tin,
  90. 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,
  91.  
  92. decode(p.rank1_sc_tin, 'Double entry', 'Double entry', 'Unknown', 'Unknown', 'UNKNOWN', 'Unknown', 'unknown', 'Unknown', sc_vt.real_employer_name) rank1_sc_name,
  93. decode(p.rank1_sc_tin, 'Double entry', 'Double entry', nvl(sc_vt.tin, p.rank1_sc_tin)) rank1_sc_tin,
  94. 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,
  95.  
  96. p.person_admin_region_id,
  97. p.admin_status_category_id,
  98. p.is_admin_status_ok,
  99. p.is_blacklisted,
  100. p.nationality_code_id,
  101. p.employee_contract_type_id,
  102. p.vo_employee_contract_type_id,
  103. p.lm_name,
  104. p.lm_date,
  105. p.lm_comment,
  106. p.is_cross_border_worker,
  107. vo.company_name,
  108. vo.company_tin,
  109. p.all_admin_attach_doc_validated,
  110. ap.admin_profile_category_id admin_profile_id,
  111. ocp.name last_caw_poi_name,
  112. wbs.poi_tin last_caw_poi_tin,
  113. wbs.ref_number last_caw_ref_number,
  114. sot.creation_date last_caw_creation_date,
  115. sot.work_date last_caw_work_date,
  116. sots.name last_caw_status,
  117. sots.so_transaction_status_id,
  118. sot.lm_date last_caw_lm_date,
  119. sot.so_comment last_caw_comment,
  120. sot.sot_remark_exist,
  121. (select json_arrayagg(json_object(vapd.admin_profile_category_id,
  122. vapd.admin_proof_category_id,
  123. vapd.admin_proof_category_name,
  124. vapd.admin_proof_short_name,
  125. vapd.admin_proof_id,
  126. vapd.document_type_name,
  127. vapd.document_type_id,
  128. vapd.is_admin_proof_doc_valid) returning clob)
  129. from v_admin_proof_doc vapd
  130. where vapd.person_id = p.person_id) admin_proof_doc,
  131. json_array(json_object('admin_proof_category_id' value 1,
  132. 'valid' value p_person.isAdminProofCatValid(1, p.person_id, trunc(sysdate))),
  133. json_object('admin_proof_category_id' value 2,
  134. 'valid' value p_person.isAdminProofCatValid(2, p.person_id, trunc(sysdate))),
  135. json_object('admin_proof_category_id' value 3,
  136. 'valid' value p_person.isAdminProofCatValid(3, p.person_id, trunc(sysdate))),
  137. json_object('admin_proof_category_id' value 4,
  138. 'valid' value p_person.isAdminProofCatValid(4, p.person_id, trunc(sysdate)))
  139. ) admin_proof_category_validities,
  140. re_vnv.vat_validation_status re_validation_status,
  141. sc_vnv.vat_validation_status sc_validation_status,
  142. manager1.first_name manager1_first_name,
  143. manager1.last_name manager1_last_name,
  144. manager1.company_nr manager1_company_nr,
  145. manager2.first_name manager2_first_name,
  146. manager2.last_name manager2_last_name,
  147. manager2.company_nr manager2_company_nr,
  148. p.last_med_visit_date person_last_med_visit_date,
  149. p.last_med_visit_status_id,
  150. last_medical_visit_status.name person_last_med_visit_status,
  151. p_person.getLastSecurityBriefingDate(p.person_id) last_security_briefing_date,
  152. p_person.getLastSecurityBriefingWBSStr(p.person_id) last_security_briefing_wbs
  153. from vo_person_category vpc,
  154. oc_poi home,
  155. vehicle_owner vo,
  156. person p,
  157. social_office_transaction sot,
  158. wbs,
  159. oc_poi ocp,
  160. so_transaction_status sots,
  161. vo_tin re_vt,
  162. vo_tin sc_vt,
  163. vat_number_validation re_vnv,
  164. vat_number_validation sc_vnv,
  165. admin_profile ap,
  166. person manager1,
  167. person manager2,
  168. medical_visit_status last_medical_visit_status
  169. where vpc.vo_person_category_id = p.vo_person_category_id
  170. and home.poi_id = p.home_poi_id
  171. and vo.vehicle_owner_id = vpc.vehicle_owner_id
  172. and sot.social_office_transaction_id (+) = p.last_so_transaction_id
  173. and wbs.wbs_id (+) = sot.wbs_id
  174. and ocp.poi_id (+) = wbs.poi_id
  175. and sots.so_transaction_status_id (+) = sot.so_transaction_status_id
  176. and re_vt.tin (+) = p.real_employer_tin
  177. and re_vt.vehicle_owner_id (+) = vo.vehicle_owner_id
  178. and p.admin_profile_id = ap.admin_profile_id (+)
  179. --and nvl(p.person_status_id,0) <> 2
  180.  
  181. and decode(SUBSTR(re_vnv.vat_number (+), 1,3), 'BE0', SUBSTR(re_vnv.vat_number (+), 4), re_vnv.vat_number (+)) = p.real_employer_tin
  182. and decode(SUBSTR(sc_vnv.vat_number (+), 1,3), 'BE0', SUBSTR(sc_vnv.vat_number (+), 4), sc_vnv.vat_number (+)) = p.rank1_sc_tin
  183.  
  184. and sc_vt.tin (+) = p.rank1_sc_tin
  185. and sc_vt.vehicle_owner_id (+) = vo.vehicle_owner_id
  186. and p.manager_person_id = manager1.person_id(+)
  187. and p.manager2_person_id = manager2.person_id(+)
  188. and p.last_med_visit_status_id = last_medical_visit_status.medical_visit_status_id(+);
  189.  
  190.  
  191. -- GRANT SELECT ON "OF_OWNER"."V_ORDS_GET_PERSON_LIST" TO "OF_PBI";
  192.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement