一个查询过程

 create or replace procedure PRC_Item_setup_pos_Qry(
  c_Setup_No     varchar2,    --上料表
  c_Item_Bcode   varchar2,    --物料条码
  c_Result   out varchar2,
  p_result  out sys_refcursor
) is
  c_Item_qty    varchar2(50);
  c_Task_No     varchar2(50);    --任务令
  c_Item_Code   varchar2(50);
  c_Item_Lot    varchar2(50);
  c_org_Item    varchar2(50);
  c_Item_Task_No  varchar2(50);       --物料对应的任务令
  n_id          number;
  n_item_id     number;
  n_COMANAGEMENT_FLAG number;
  c_CUSTOMER_NO  varchar2(50);
  LV_TMP         varchar2(1000);
  lex exception;
  n   number;
  /*******************************
  *   物料上料位置查询           *
  *  created by wanxb  20080216  *
  *  modifed by zh.h   20080227  *
  *  modifed by zh.h   20080328  *
  *  modifed by zh.h   20080704  *
  ********************************/
begin

  c_Result:='';
  begin
    select t.task_no into c_Task_No from smt_setup_task t where t.setup_no=c_Setup_No and rownum = 1;
  exception
    when no_data_found then
      c_Result:= '上料表不存在';
      raise lex;
  end;

  if substr(c_Item_Bcode,1,1) = 'W' and substr(c_Item_Bcode,10,1) = 'Y' then
    --psn码，获取物料编码、批次
    Begin
      select id,CUSTOMER_ITEM_NO,LOT_NO,quantity,Task_No,COMANAGEMENT_FLAG
        into n_item_id,c_Item_Code,c_Item_Lot,c_Item_qty,c_Item_Task_No,n_COMANAGEMENT_FLAG
        from
        (select s.id,s.CUSTOMER_ITEM_NO,b.LOT_NO,b.quantity,b.Task_No,s.COMANAGEMENT_FLAG
        From smt_inv_business b,smt_items s
        Where b.ITEM_no = s.ITEM_NO
          And b.ITEM_BARCODE = c_Item_Bcode
          and b.bill_type_id = 2
          and b.transaction_id = 3
          order by b.create_date desc)
          where rownum = 1;
      If c_Task_No is not null and (Fun_task_find_pack(c_Task_No) <> c_Item_Task_No and n_COMANAGEMENT_FLAG = 1) Then
        c_Result := '物料不是上料表对应生产订单发放的物料';
        raise lex;
      End If;
    exception
      when No_Data_Found then
      c_Result := '物料条码无效';
      raise lex;
    end;

    --校验是否批量隔离
    if Fun_Analyse_Item_Lot_Close(n_item_id,c_Item_Lot) <> 0 then
      c_Result := '物料已经批量隔离，不能使用';
      raise lex;
    end if;

    --校验物料是否环保兼容
    n_id := Fun_Analyse_Item_ep(c_Item_Bcode,c_Task_No);

    if n_id = 0 then
      c_Result := '物料与任务令的环保要求不兼容';
      raise lex;
    elsif n_id = -1 then
      c_Result := '任务令错误';
      raise lex;
    elsif n_id = -2 then
      c_Result := '物料错误';
      raise lex;
    end if;

    --校验是否已经发料,可用数量是否足够
    begin
      select nvl(sum(AVAILABLE_QUANTITY),0)
        into n_id
        from smt_supply_list
        where TASK_NO = Fun_task_find_pack(c_Task_No)
          and PO_CODE = c_Item_Bcode;
      if n_id <= 0 then
        c_Result := '物料可用数量为零';
        raise lex;
      end if;
    exception
      when No_Data_Found then
      c_Result := '物料没有发料到当前上料表';
      raise lex;
    end;
  else
    --09等其他条码则判断任务令客户，根据客户编码规则，提取物料编码
    begin
      select CUSTOMER_NO
        into c_CUSTOMER_NO
        from smt_dep_task_info i
        where task_no = c_Task_No;
    exception
      when No_Data_Found then
      c_Result := '任务令没有对应的客户，无法找到物料编码规则';
      raise lex;
    end;

    pg_rf.get_c_item_code(p_customer_no    => c_customer_no,
                    p_c_item_barcode => c_item_bcode,
                    c_result         => lv_tmp);
    if substr(lv_tmp, 1, 4) = '001[' then
      c_item_code := substr(lv_tmp, 5, length(lv_tmp));
    else
      c_result := lv_tmp;
      raise lex;
    end if;
  end if;

  --上料位置提取
  --是否替代物料,是替代物料则找到原物料及其对应位置
  begin
    select item_code into c_org_Item
      from smt_item_instead i
      where i.instead_Item = c_Item_Code
        and TASK_No = Fun_task_find_pack(c_Task_No)
        and rownum = 1;
  exception
    when No_Data_Found then
      c_org_Item := c_Item_Code ;
  end;
 
    select count(1)
      into n
      from smt_setup_info i,smt_setup_info_detail d
      where i.setup_info_id = d.setup_info_id
        and i.setup_no = c_Setup_No
        and d.Item_Code = c_org_Item;
    if n = 0 then
      c_Result := '物料在当前上料表中没有上料位置';
      raise lex;
    end if;

  open p_result for
    select i.model_num 机器顺序号,
           i.loca_num 分区号,
           d.track_no 栈位,
           d.division_num 槽位,
           f.feeder_size   飞达型号,
           d.item_code 物料编码
      from smt_setup_info i,smt_setup_info_detail d ,smt_com_feeder_sizes f
      where i.setup_info_id = d.setup_info_id
        and d.COM_SMT_FEEDER_SIZES_ID= f.id
        and i.setup_no = c_Setup_No
        and Item_Code = c_org_Item
        and not exists (select 'X' from SMT_CHECK_ITEM_POS p
                          where p.item_code = d.item_code
                            and p.setup_no = i.setup_no
                            and p.MODEL_NUM = i.MODEL_NUM
                            and p.LOCA_NUM =  i.LOCA_NUM 
                            and p.TRACK_NO =  d.TRACK_NO 
                            and p.DIVISION_NUM =  d.DIVISION_NUM);
     --原始物料的上料位置
exception
  when lex then
  open p_result for
    select i.model_num 机器顺序号,
           i.loca_num 分区号,
           d.track_no 栈位,
           d.division_num 槽位,
           f.feeder_size   飞达型号,
           d.item_code 物料编码
      from smt_setup_info i,smt_setup_info_detail d ,smt_com_feeder_sizes f
      where  1=2 ;
end PRC_Item_setup_pos_Qry;
/

//=====================西门子线空机数据库

SELECT AliasName_1.ObjectName AS 上料表编号, AliasName.ObjectName AS 版面编号,
      AliasName_2.ObjectName AS 机器条码, CPickupLink.nLocation AS 分区,
      CPickupLink.lTrack AS 栈位, CPickupLink.lReserve AS 槽位,
      CComponentPlacement.CCPCollection_CComBSTR AS 位置信息,
      AliasName_3.ObjectName AS 物料编码, CPickupLink.lTower AS 是否托盘料
FROM CHeadStep INNER JOIN
      CHeadSchedule ON CHeadStep.PID = CHeadSchedule.OID INNER JOIN
      CPlacementLink ON CHeadStep.lPlacementLink = CPlacementLink.lIndex AND
      CHeadSchedule.PID = CPlacementLink.PID INNER JOIN
      CPickupLink ON CHeadStep.lPickupLink = CPickupLink.lIndex AND
      CPlacementLink.PID = CPickupLink.PID INNER JOIN
      AliasName ON CHeadSchedule.PID = AliasName.PID INNER JOIN
      CRecipe ON CPickupLink.PID = CRecipe.OID INNER JOIN
      AliasName AliasName_1 ON CRecipe.spSetupRef = AliasName_1.PID INNER JOIN
      AliasName AliasName_2 ON CPickupLink.spStation = AliasName_2.PID INNER JOIN
      CComponentPlacement ON
      CPlacementLink.spComponentPlacement = CComponentPlacement.OID INNER JOIN
      AliasName AliasName_3 ON
      CPickupLink.spComponentRef = AliasName_3.PID
where AliasName_1.ObjectName = 'UG01MVDB-VD-T&B-081014'
ORDER BY AliasName.ObjectName, AliasName_2.ObjectName DESC,
      CPickupLink.nLocation, CPickupLink.lTrack, CPickupLink.lReserve