SQL语句 - 查询系统内已接收未入库数据

SQL语句

-- find material received without a deliver transaction 
/* Formatted on 2004/04/24 15:51 (Formatter Plus v4.8.0) */
SELECT mp_from.ORGANIZATION_CODE from_org,
       mp_to.ORGANIZATION_CODE   to_org,
       rt.source_document_code source_doc,
       rsh.receipt_source_code receipt_source,
       rt.transaction_date,
       rt.transaction_type,
       msi.segment1,
       msi.description,
       rt.quantity,
       rt.unit_of_measure,
       DECODE(rt.source_document_code,
              'PO',
              poh.segment1,
              'RMA',
              oeh.order_number,
              rsh.shipment_num) order_number, 
       DECODE(rt.source_document_code,
              'RMA',
              oeot.order_category_code,
              poh.type_lookup_code) type_lookup_code,
       DECODE(rt.source_document_code,
              'PO',
              pov.vendor_name,
              'RMA',
              SUBSTR(hzp.party_name, 1, 50),
              ood.NAME) SOURCE,
       DECODE(rt.source_document_code,
              'RMA',
              oel.line_number,
              'PO',
              pol.line_num,
              rsl.line_num) line_number,
       DECODE(rt.source_document_code,
              'RMA',
              oel.ordered_quantity,
              'PO',
              pol.quantity,
              rsl.quantity_shipped) ordered_qty,
       DECODE(rt.source_document_code,
              'RMA',
              oel.order_quantity_uom,
              'PO',
              mum2.unit_of_measure,
              rsl.unit_of_measure) ordered_uom,
       rt.po_unit_price,
       rsl.item_description,
       DECODE(rt.source_document_code, 'RMA', NULL, pll.shipment_num) po_shipment_number,
       DECODE(rt.source_document_code, 'RMA', NULL, poh.note_to_receiver) note_to_receiver,
       rsl.comments,
       rsl.comments inv_req_note_to_receiver,
       DECODE(rt.source_document_code,
              'RMA',
              oel.schedule_arrival_date,
              'PO',
              NVL(pll.promised_date, pll.need_by_date),
              rsh.expected_receipt_date) due_date,
       rsh.receipt_num,
       rsh.shipment_num         rcv_shipment_num,
       rsh.packing_slip,
       rsh.bill_of_lading,
       rsh.freight_carrier_code freight_carrier,
       rsh.waybill_airbill_num,
       rsl.line_num             rcv_line_num,
       DECODE(rt.source_document_code, 'RMA', NULL, pol.vendor_product_num) vendor_item_number,
       rrh.routing_name, 
       rsup.destination_type_code destination_type_code_hold,
       rsl.to_subinventory subinventory,
       DECODE(rt.source_document_code,
              'RMA',
              msi.un_number_id,
              NVL(pol.un_number_id, msi.un_number_id)) un_number_id,
       rsup.creation_date, 
       DECODE(rt.source_document_code, 'RMA', ood1.NAME, hrl.location_code) current_location,
       oeh.order_number oe_order_num,
       oel.line_number oe_order_line_num
  FROM rcv_supply                   rsup,
       rcv_shipment_lines           rsl,
       rcv_transactions             rt,
       rcv_shipment_headers         rsh,
       apps.po_lookup_codes         plc,
       apps.po_lookup_codes         plc1,
       apps.po_lookup_codes         plc2,
       apps.po_lookup_codes         plc3,
       apps.mtl_units_of_measure    mum,
       apps.mtl_units_of_measure    mum1,
       apps.mtl_units_of_measure    mum2,
       apps.mtl_units_of_measure    mum3,
       apps.mtl_units_of_measure    mum4,
       mtl_system_items_b           msi,
       mtl_system_items_b           msi1,
       mtl_transaction_reasons      mtr,
       hz_cust_accounts             hzca,
       hz_parties                   hzp,
       oe_order_lines_all           oel,
       oe_order_headers_all         oeh,
       oe_transaction_types_tl      oetl,
       oe_transaction_types_all     oeot,
       mtl_customer_items           mci,
       po_lines_all                 pol,
       po_headers_all               poh,
       po_line_locations_all        pll,
       apps.po_releases             pr,
       po_vendors                   pov,
       rcv_routing_headers          rrh,
       hr_locations_all_tl          hrl,
       gl_daily_conversion_types    dct,
       hr_all_organization_units_tl ood,
       hr_all_organization_units_tl ood1,
       mtl_parameters               mp_to,
       mtl_parameters               mp_from
 WHERE rsup.supply_type_code = 'RECEIVING'
   AND rsl.shipment_line_id = rsup.shipment_line_id
   AND rt.transaction_id = rsup.rcv_transaction_id
   AND rt.transaction_type != 'UNORDERED'
   AND rrh.routing_header_id(+) = rt.routing_header_id
   AND rsh.shipment_header_id = rsup.shipment_header_id
   AND rt.transaction_type = plc.lookup_code
   AND plc.lookup_type = 'RCV TRANSACTION TYPE'
   AND plc1.lookup_type(+) = 'INSPECTION STATUS'
   AND plc1.lookup_code(+) = rt.inspection_status_code
   AND plc2.lookup_type(+) = 'RCV DESTINATION TYPE'
   AND plc2.lookup_code(+) = rsup.destination_type_code
   AND oel.line_id(+) = rsup.oe_order_line_id
   AND oeh.header_id(+) = rsup.oe_order_header_id
   AND oel.line_type_id = oeot.transaction_type_id(+)
   AND oeot.transaction_type_id = oetl.transaction_type_id(+)
   AND oetl.LANGUAGE(+) = USERENV('LANG')
   AND oeot.transaction_type_code(+) = 'LINE'
   AND hzca.cust_account_id(+) = rsh.customer_id
   AND hzca.party_id = hzp.party_id(+)
   AND oel.ordered_item_id = mci.customer_item_id(+)
   AND pol.po_line_id(+) = rsup.po_line_id
   AND poh.po_header_id(+) = rsup.po_header_id
   AND pov.vendor_id(+) = rsh.vendor_id
   AND pll.line_location_id(+) = rsup.po_line_location_id
   AND mum.unit_of_measure = rsup.unit_of_measure
   AND mum2.unit_of_measure(+) = pol.unit_meas_lookup_code
   AND (mum4.unit_of_measure(+) = pll.secondary_unit_of_measure)
   AND (mum3.uom_code(+) = oel.ordered_quantity_uom2)
   AND msi.organization_id(+) = rsup.to_organization_id
   AND msi.inventory_item_id(+) = rsup.item_id
   AND mum1.unit_of_measure(+) = msi.primary_unit_of_measure
   AND msi1.organization_id(+) = rsup.from_organization_id
   AND msi1.inventory_item_id(+) = rsup.item_id
   AND pr.po_release_id(+) = rsup.po_release_id
   AND mtr.reason_id(+) = rt.reason_id
   AND hrl.location_id(+) = rt.location_id
   AND hrl.LANGUAGE(+) = USERENV('LANG')
   AND ood.organization_id(+) = rsl.from_organization_id
   AND ood.LANGUAGE(+) = USERENV('LANG')
   AND ood1.organization_id(+) = rt.location_id
   AND ood1.LANGUAGE(+) = USERENV('LANG')
   AND DECODE(rt.source_document_code,
              'PO',
              'PO TYPE',
              'SHIPMENT SOURCE TYPE') = plc3.lookup_type
   AND DECODE(rt.source_document_code,
              'PO',
              poh.type_lookup_code,
              rsh.receipt_source_code) = plc3.lookup_code
   AND dct.conversion_type(+) = rt.currency_conversion_type
   and msi.segment1 is not null  --排除费用类的单子(不需入库)
   and rsl.TO_ORGANIZATION_ID = mp_to.ORGANIZATION_ID
   and rsl.FROM_ORGANIZATION_ID = mp_from.ORGANIZATION_ID(+)
 order by 2, 3, 1


本文标题:SQL语句 - 查询系统内已接收未入库数据

本文链接:https://catonisland.cn/post/141.html

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章