EBS: Report Builder 的公式程序中不支持over()分组

在ORACLE Report Builder 10G中function功能程序中不支持 ROW_NUMBER() OVER(PARTITION BY XXXX, YYY  ORDER BY ZZZ,JJJ ) 

的分组函数。 

 

-- 2021/11/22,add: 按工單分組,按最新異常原因降序排序.然後返回最新一個異常代碼
-- 在Report Builder10G中不支持此寫法 
select flex_value from (
SELECT x.flex_value,
       row_number() over(partition by nwj.wip_entity_id order by nwj.allocation_id desc) as rn
  --into v_exception
FROM   wip_operations wo
      ,nj.nj_wip_joexception nwj
      ,(select a.flex_value ,a.description 
from fnd_flex_values_vl a ,fnd_flex_value_sets b 
where a.flex_value_set_id = b.flex_value_set_id 
and b.flex_value_set_name = 'WIP_JOEXCEPTION' 
and a.enabled_flag = 'Y') x
 WHERE wo.wip_entity_id = nwj.wip_entity_id
   AND wo.department_id = nwj.exception_depart_id
   --and  wo.quantity_in_queue=1
   and  wo.quantity_in_queue>=1
   and  wo.quantity_completed=0
   AND wo.wip_entity_id =:wip_entity_id
   and nwj.jo_exception=x.DESCRIPTION   
  -- and rownum=1
 --ORDER BY nwj.allocation_id desc
  ) m1 where rn =1  ;
  
-- 環境: PROD 
-- 2021/11/22,add: 按工單分組,按最新異常原因降序排序.然後返回最新一個異常代碼
-- 因在Report Builder10G中不支持ROW_NUMBER OVER()寫法   
-- 改為以下方式
SELECT x.flex_value
  into v_exception
FROM   wip_operations wo
      ,nj.nj_wip_joexception nwj,
      (select nwj3.wip_entity_id, max(nwj3.allocation_id) as allocation_id
        from nj.nj_wip_joexception  nwj3
         where nwj3.creation_date> add_months(sysdate,-36)
      group by nwj3.wip_entity_id ) nwj2
      ,(select a.flex_value ,a.description 
        from fnd_flex_values_vl a ,fnd_flex_value_sets b 
        where a.flex_value_set_id = b.flex_value_set_id 
        and b.flex_value_set_name = 'WIP_JOEXCEPTION' 
        and a.enabled_flag = 'Y') x
 WHERE wo.wip_entity_id = nwj.wip_entity_id
   AND wo.department_id = nwj.exception_depart_id
   --and  wo.quantity_in_queue=1
   and  wo.quantity_in_queue>=1 
   and  wo.quantity_completed=0
   AND wo.wip_entity_id =:wip_entity_id
   and nwj.jo_exception=x.DESCRIPTION   
   AND nwj.allocation_id = nwj2.allocation_id
   and nwj.wip_entity_id = nwj2.wip_entity_id 
   and rownum=1;