4.b. retrieveDocumentsForSubgroups

--retrieveDocumentsForSubgroups Modified
select

--count(d.document_metadata_id)

  d.document_metadata_id,
  d.external_document_id,
  d.workflow_step_id,
  wstt.description step_title,
  d.category,
  d.sub_category,
  d.title,
  d.image_type,
  d.url,
  d.created_by_worker,
  d.created_on,
  d.created_acting_for_worker,
  d.last_updated_on,
  d.last_updated_by_worker,
  d.updated_acting_for_worker,
  rp.can_browse,
  rp.can_read,
  rp.can_edit,
  rp.can_add,
  rp.can_delete,
  reqd_subjects.subgroup_id,
  mg.group_id,
  mg.group_name as subjectName,
  mg.group_name as groupName,
  mgt.display_name as groupTypeName,
  mss.subject_compound_id as subject_compound_id,
  mss.subject_type_code as subject_type_code,
  mosaic_names.subject_name(mss.subject_type_code, mss.subject_compound_id) as subgroup_member_name,
  mosaic_names.worker_name (d.CREATED_BY_WORKER) as created_by_worker_name,
  mosaic_names.worker_name (d.CREATED_ACTING_FOR_WORKER) as created_acting_worker_name,
  mosaic_names.worker_name (d.LAST_UPDATED_BY_WORKER) as updated_by_worker_name,
  mosaic_names.worker_name (d.UPDATED_ACTING_FOR_WORKER) as updated_acting_worker_name

from (
 -- select :subgroupId subgroup_id from dual
--union all

	SELECT subgroup_id
	FROM mo_subgroup_subjects
	WHERE subject_compound_id = :personid
	AND subject_type_code = 'PER'


) reqd_subjects
inner join mo_subgroups ss on ss.subgroup_id = reqd_subjects.subgroup_id
inner join mo_subgroup_subjects mss on mss.subgroup_id = ss.subgroup_id
inner join mo_document_metadata d on d.subgroup_id = ss.subgroup_id

--left outer join mo_workflow_steps wst ON ss.subgroup_id = wst.subgroup_id
left outer join mo_workflow_steps wst ON wst.workflow_step_id = d.workflow_step_id

left outer join mo_workflow_step_types wstt on wstt.WORKFLOW_STEP_TYPE_ID = wst.WORKFLOW_STEP_TYPE_ID
left outer join mo_role_permissions rp ON rp.workflow_step_type_id = wst.workflow_step_type_id and rp.role = :role

inner join mo_groups mg on ss.group_id = mg.group_id
inner join mo_group_types mgt on mgt.group_type_code = mg.group_type_code


--inner join mo_document_metadata d on (d.subgroup_id = ss.subgroup_id or d.workflow_step_id = wst.workflow_step_id)
--inner join mo_document_metadata d on (d.workflow_step_id = wst.workflow_step_id)
--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)



--CRITERIA: Not held externally
--and d.external_document_id is null
--CRITERIA: Exclude old FWi Forms which will be deleted shortly
and coalesce(d.image_type, 'x') not in ('Form', 'Letter')
--WHERE d.document_metadata_id = 6821
Order By d.document_metadata_id DESC