ITPub博客

首页 > Linux操作系统 > Linux操作系统 > OE RMA

OE RMA

原创 Linux操作系统 作者:ceo_lxy 时间:2008-11-05 15:47:18 0 删除 编辑

SELECT
  sha.org_id,
  sha.order_number,
  sla.line_number,
  msi.segment1,
  cosv.serial_number,
  sha1.order_number return_order_number,
  mut.SERIAL_NUMBER return_serial_number
FROM
  oe.so_headers_all sha,
  oe.so_lines_all sla,
  inv.mtl_system_items msi,
  apps.ch_oracleso_serial_v cosv,
  oe.so_lines_all sla1,
  oe.so_headers_all sha1,
  inv.mtl_material_transactions mmt,
  inv.MTL_UNIT_TRANSACTIONS mut
WHERE
  sha.header_id=sla.header_id and
  sha.org_id=sla.org_id and
  sla.inventory_item_id=msi.inventory_item_id and
  sla.org_id=msi.organization_id and
  sla.org_id=cosv.org_id and
  sla.header_id=cosv.header_id and
  sla.line_id=cosv.line_id  and
  sla.org_id=sla1.org_id and
  sla.line_id=sla1.RETURN_REFERENCE_ID and
  nvl(sla1.header_id,0)=sha1.header_id and
  nvl(sla1.org_id,0)=sha1.org_id and
  sla1.org_id=mmt.ORGANIZATION_ID and
  sla1.line_id=mmt.TRX_SOURCE_LINE_ID and
  mmt.ORGANIZATION_ID=mut.ORGANIZATION_ID and
  mmt.TRANSACTION_ID=mut.TRANSACTION_ID and
  mmt.TRANSACTION_TYPE_ID=15 and
  sha.org_id=439 and
  msi.item_type='FG'   and
  sha.order_number='88003088'

---------------------------------------------------
SELECT
  sha.org_id,
  sha.order_number,
  sla.line_number,
  msi.segment1,
  cosv.serial_number,
  sla.line_id,
  sla1.line_id return_line_id,
  sha1.order_number return_order_number,
  sla1.line_number,
  mut.SERIAL_NUMBER
FROM
  oe.so_headers_all sha,
  oe.so_lines_all sla,
  inv.mtl_system_items msi,
  apps.ch_oracleso_serial_v cosv,
  oe.so_lines_all sla1,
  oe.so_headers_all sha1,
  inv.mtl_material_transactions mmt,
  inv.MTL_UNIT_TRANSACTIONS mut
WHERE
  sha.header_id=sla.header_id and
  sha.org_id=sla.org_id and
  sla.inventory_item_id=msi.inventory_item_id and
  sla.org_id=msi.organization_id and
  sla.org_id=cosv.org_id and
  sla.header_id=cosv.header_id and
  sla.line_id=cosv.line_id  and
  sla.org_id=sla1.org_id(+) and
  sla.line_id=sla1.RETURN_REFERENCE_ID(+) and
  nvl(sla1.header_id,0)=sha1.header_id(+)  and
  nvl(sla1.org_id,0)=sha1.org_id(+) and
  nvl(sla1.org_id,0)=mmt.ORGANIZATION_ID(+) and
  nvl(sla1.line_id,0)=mmt.TRX_SOURCE_LINE_ID(+) and
  nvl(mmt.ORGANIZATION_ID,0)=mut.ORGANIZATION_ID(+) and
  nvl(mmt.TRANSACTION_ID,0)=mut.TRANSACTION_ID(+) and
  nvl(mmt.TRANSACTION_TYPE_ID,15)=15 and
  sha.org_id=439 and
  msi.item_type='FG'   and
  sha.order_number='88002828'
-------------------------------------------------------------

select  wdep.NAME DEPARTURE_NAME,
        wdel.name  deliver_name,
        wdep.actual_departure_date ,
        to_char(wdep.actual_departure_date,'yyyy-mm') yearmonth,
       substr(to_char(wdep.actual_departure_date,'yyyy-mm-dd'),1,4) year,
       substr(to_char(wdep.actual_departure_date,'yyyy-mm-dd'),6,2) month,
        wdel.EXPECTED_ARRIVAL_DATE,
        l.header_id,
        l.line_id,
        spld.PICKING_LINE_DETAIL_ID,
        nvl(spld.SHIPPED_QUANTITY,0)   LINE_SHIPPED_QUANTITY,
        spld.SERIAL_NUMBER,
        pl.PICKING_LINE_ID,
        pl.inventory_item_id,
        l.org_id,
        spld.SUPPLY_SOURCE_HEADER_ID,
        spld.SUBINVENTORY,
        l.header_id||'.'||l.line_id header_line,
        l.attribute5  option_line,
        wdep.creation_date
from  wsh_deliveries wdel,
      wsh_departures wdep,
      so_picking_headers_all ph,
      so_picking_lines_all pl,
      so_lines_all  l,
      SO_PICKING_LINE_DETAILS spld
where wdep.departure_id = wdel.actual_departure_id
AND  wdel.delivery_id = ph.delivery_id
AND  ph.picking_header_id = pl.picking_header_id
AND  pl.order_line_id = l.line_id
AND  l.line_type_code in ('REGULAR','DETAIL')
and  pl.PICKING_LINE_ID=spld.PICKING_LINE_ID
and  nvl(spld.SHIPPED_QUANTITY,0)<>0
--and  l.header_id=1411
--AND  l.inventory_item_id=122506

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12122734/viewspace-485881/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-06-02

  • 博文量
    516
  • 访问量
    486862