重庆退料校验SQL(待检验)

--修改前
Begin
     fnd_client_info.set_org_context(82); -- 83 为当前组织（OU）的ORG_ID
     fnd_client_info.setup_client_info(
                                       application_id => 401 ,
                                       responsibility_id => 20634,
                                       user_id => 0,
                                       security_group_id => 0
                                       );
     --application_id为当前应用的APPLICATION_ID
     --responsibility_id为当前职责的RESP_ID
     --user_id为当前用户的USER_ID
     --security_group_id为当前安全组的SECURITY_GROUP_ID
End;
--select * from cpa_projects_expend_v


declare
  v_orgid varchar2(20);
  v_count BINARY_INTEGER;
  cursor c_Orgid is
    select hou.organization_id from hr_operating_units hou;
begin
  open c_Orgid;
  loop
    fetch c_Orgid
      into v_orgid;
    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 = v_orgid
       and mmt.organization_id = 238
       and mmt.transaction_action_id = 1
       and mmt.source_project_id = ppa.project_id
       and ppa.project_id = ppp.project_id
       and ppp.organization_id = 238; --必须把变量放在into后的变量里，因为实在函数内
    dbms_output.put_line(v_count);
    exit when c_Orgid%NOTFOUND;
  end loop;
end;






--修改后
declare
  v_orgid varchar2(20);
  v_count BINARY_INTEGER;
  cursor c_Orgid is
    select hou.organization_id from hr_operating_units hou;
begin
  open c_Orgid;
  loop
    fetch c_Orgid
      into v_orgid;
    select count(ppa.project_id)
      into v_count
      from pa_projects_all ppa
     where ppa.carrying_out_organization_id = v_orgid
       and exists (select null
              from mtl_material_transactions mmt
             where mmt.organization_id = 238
               and 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 ppp.organization_id = 238)
       and pa_project_utils.check_prj_stus_action_allowed(ppa.project_status_code,
                                                          'NEW_TXNS') = 'Y'; --必须把变量放在into后的变量里，因为实在函数内
    dbms_output.put_line(v_count);
    exit when c_Orgid%NOTFOUND;
  end loop;
end;