EBS: OM-订单类型之分配行流查询

-- OM-订单类型之分配行流查询.sql ; 
select (SELECT OU.NAME FROM HR_ORGANIZATION_UNITS OU WHERE OU.ORGANIZATION_ID= OTT.ORG_ID AND ROWNUM =1 ) AS "组织OU",
  OTT.NAME AS "事务处理类型",
   OWLA.assignment_id,
 OWLA.LINE_TYPE_ID, OWLA.LINE_TYPE AS "行类型",
  OWLA.item_type_code AS "项目类型",
  OWLA.PROCESS_NAME AS "流程名称代码",
  WF.DISPLAY_NAME AS "流程名称",
 OWLA.START_DATE_ACTIVE,OWLA.end_date_active
-- , OWLA.* 
 from OE_WF_LINE_ASSIGN_V  OWLA,  -- 流程分配
  -- oe_workflow_assignments wf
 oe_transaction_types_all_vl ott,  -- 事务处理类型
 (select display_name, description, item_type, name as process_name, max(version) as "VERSION"
  from wf_activities_vl 
  where type = 'PROCESS' 
  and nvl(end_date, sysdate) >= sysdate 
  and item_type = 'OEOL' 
  and runnable_flag = 'Y'  
  and name not like 'UPG_%'
  group by display_name, description, name, item_type
  ) WF -- 流程
WHERE  OWLA.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
 AND OWLA.PROCESS_NAME = WF.process_name(+)
-- AND OTT.ORG_ID = 281 
ORDER BY OTT.ORG_ID, OTT.NAME ,OWLA.assignment_id