重庆退料检验PLSQL程序段（第二版）

--修改前--


--查找每个ou下的项目数
declare
  --v_orgid varchar2(20);
  v_count BINARY_INTEGER;
  hourow hr_operating_units%ROWTYPE;
  v_respid  varchar2(20);
  v_userid  varchar2(20);
  
  cursor c_Message is
    select * from hr_operating_units hou;
    
  cursor c_Permision is
    select  t1.responsibility_id, t3.user_id 
    from (select fr.responsibility_id, fr.responsibility_name, fr.application_id
            from fnd_responsibility_tl fr) t1,
           FND_USER_RESP_GROUPS_DIRECT   T3 
     where  t1.responsibility_id = t3.responsibility_id
     and nvl(t3.END_DATE,sysdate)  >  trunc(sysdate)
     and rownum=1
     and t1.application_id>=401
     and exists (select 'x'  from fnd_user fu where fu.user_id = t3.user_id and    nvl(fu.END_DATE,sysdate)  >  trunc(sysdate)) ;
  
begin
  open c_Permision;
      fetch c_Permision into v_respid, v_userid;
                                       
  open c_Message;
  loop
    fetch c_Message
      into hourow;
      exit when c_Message%NOTFOUND;
      --初始化
      fnd_client_info.set_org_context(hourow.organization_id); 
      fnd_client_info.setup_client_info(
                                       application_id => 401 ,
                                       responsibility_id => v_respid,
                                       user_id => v_userid,
                                       security_group_id => 0
                                       );
      --选出ou下项目数
    select count(distinct ppa.project_id)
      into v_count
      from mtl_material_transactions mmt,
           cpa_projects_expend_v     ppa,
           pjm_project_parameters    ppp
     where ppa.carrying_out_organization_id = hourow.organization_id
       and mmt.transaction_action_id = 1
       and mmt.source_project_id = ppa.project_id
       and ppa.project_id = ppp.project_id
       order by Project_number; 
       dbms_output.put_line('ou = ' || hourow.organization_id ||' '||'projects='||v_count);
    
  end loop;
  close c_Permision;
  close c_Message;
end;






--修改后--
--查找每个ou下的项目数
declare
  hourow hr_operating_units%ROWTYPE;
  v_count BINARY_INTEGER;
  cursor c_Message is
    select * from hr_operating_units;
begin
  open c_Message;
  loop
    fetch c_Message
      into hourow;
    exit when c_Message%NOTFOUND;
    
    select count(ppa.project_id)
      into v_count
      from pa_projects_all ppa
     where ppa.carrying_out_organization_id = hourow.organization_id
     and exists (select null
            from mtl_material_transactions mmt
           where  mmt.transaction_action_id = 1
             and mmt.source_project_id = ppa.project_id)
     and exists(select null
            from pjm_project_parameters ppp
           where ppa.project_id = ppp.project_id)
     and pa_project_utils.check_prj_stus_action_allowed(ppa.project_status_code, 'NEW_TXNS') = 'Y';
       
    dbms_output.put_line('ou = ' || hourow.organization_id ||' '||'projects='||v_count);
  end loop;
  close c_Message;
end;