OE RMA

最新推荐文章于 2023-12-25 14:28:50 发布

转载最新推荐文章于 2023-12-25 14:28:50 发布 · 112 阅读

本文提供了多个复杂的SQL查询案例，涉及多个表之间的连接查询，包括正向和反向查询，以及根据特定条件筛选记录的方法。这些案例有助于理解如何通过SQL解决实际业务问题。

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