daily pastebin goal
26%
SHARE
TWEET

4.b. retrieveDocumentsForSubgroups

proxpreon Jun 13th, 2018 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --retrieveDocumentsForSubgroups Modified
  2. select
  3.  
  4. --count(d.document_metadata_id)
  5.  
  6.   d.document_metadata_id,
  7.   d.external_document_id,
  8.   d.workflow_step_id,
  9.   wstt.description step_title,
  10.   d.category,
  11.   d.sub_category,
  12.   d.title,
  13.   d.image_type,
  14.   d.url,
  15.   d.created_by_worker,
  16.   d.created_on,
  17.   d.created_acting_for_worker,
  18.   d.last_updated_on,
  19.   d.last_updated_by_worker,
  20.   d.updated_acting_for_worker,
  21.   rp.can_browse,
  22.   rp.can_read,
  23.   rp.can_edit,
  24.   rp.can_add,
  25.   rp.can_delete,
  26.   reqd_subjects.subgroup_id,
  27.   mg.group_id,
  28.   mg.group_name as subjectName,
  29.   mg.group_name as groupName,
  30.   mgt.display_name as groupTypeName,
  31.   mss.subject_compound_id as subject_compound_id,
  32.   mss.subject_type_code as subject_type_code,
  33.   mosaic_names.subject_name(mss.subject_type_code, mss.subject_compound_id) as subgroup_member_name,
  34.   mosaic_names.worker_name (d.CREATED_BY_WORKER) as created_by_worker_name,
  35.   mosaic_names.worker_name (d.CREATED_ACTING_FOR_WORKER) as created_acting_worker_name,
  36.   mosaic_names.worker_name (d.LAST_UPDATED_BY_WORKER) as updated_by_worker_name,
  37.   mosaic_names.worker_name (d.UPDATED_ACTING_FOR_WORKER) as updated_acting_worker_name
  38.  
  39. from (
  40.  -- select :subgroupId subgroup_id from dual
  41. --union all
  42.  
  43.     SELECT subgroup_id
  44.     FROM mo_subgroup_subjects
  45.     WHERE subject_compound_id = :personid
  46.     AND subject_type_code = 'PER'
  47.  
  48.  
  49. ) reqd_subjects
  50. inner join mo_subgroups ss on ss.subgroup_id = reqd_subjects.subgroup_id
  51. inner join mo_subgroup_subjects mss on mss.subgroup_id = ss.subgroup_id
  52. inner join mo_document_metadata d on d.subgroup_id = ss.subgroup_id
  53.  
  54. --left outer join mo_workflow_steps wst ON ss.subgroup_id = wst.subgroup_id
  55. left outer join mo_workflow_steps wst ON wst.workflow_step_id = d.workflow_step_id
  56.  
  57. left outer join mo_workflow_step_types wstt on wstt.WORKFLOW_STEP_TYPE_ID = wst.WORKFLOW_STEP_TYPE_ID
  58. left outer join mo_role_permissions rp ON rp.workflow_step_type_id = wst.workflow_step_type_id and rp.role = :role
  59.  
  60. inner join mo_groups mg on ss.group_id = mg.group_id
  61. inner join mo_group_types mgt on mgt.group_type_code = mg.group_type_code
  62.  
  63.  
  64. --inner join mo_document_metadata d on (d.subgroup_id = ss.subgroup_id or d.workflow_step_id = wst.workflow_step_id)
  65. --inner join mo_document_metadata d on (d.workflow_step_id = wst.workflow_step_id)
  66. --inner join mo_document_metadata d on ((d.workflow_step_id is null and d.subgroup_id = ss.subgroup_id)or d.workflow_step_id = wst.workflow_step_id)
  67.  
  68.  
  69.  
  70. --CRITERIA: Not held externally
  71. --and d.external_document_id is null
  72. --CRITERIA: Exclude old FWi Forms which will be deleted shortly
  73. and coalesce(d.image_type, 'x') not in ('Form', 'Letter')
  74. --WHERE d.document_metadata_id = 6821
  75. Order By d.document_metadata_id DESC
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top