Oracle EBS-SQL (QA-2):检查接收未检验.SQL

SELECT

         rsh.receipt_num                                                       收据号,
         pov.vendor_name                                               供应商名称,
         poh.segment1                                                       采购订单,
         decode(POH.AUTHORIZATION_STATUS,'APPROVED','已批准','IN PROCESS','过程中','','未完成') 批准状态,
         pol.line_num                                                           订单行,
         PTL.LINE_TYPE                                                        行类型,
         decode(POL.closed_code,'OPEN','打开','CLOSED','关闭','FINALLY CLOSED','最后关闭')   栏状态,
         msi.segment1                                                       物料编码,
         msi.description                                                     物料描述,
         POL.QUANTITY                                                        行数量,
          --    plla.quantity_received                                 接收总数量,
          --    PLLA.QUANTITY                                                    TT,
          --    plla.quantity_accepted                                      已入库,
          --    plla.quantity_received-PLLA.QUANTITY                   HH,
          --    plla.QUANTITY-plla.quantity_received           未接收数量,
          --    plla.quantity_received-plla.quantity_accepted 接收中数量,
         rcvt.unit_of_measure                                              计量单位,
         rcvt.quantity                                                           入库数量,
         rcvt.transaction_date                                               入库日期,
         rcvt.subinventory                                                     接收子库,
         DECODE(rcvt.transaction_type,
                'REJECT',
                '拒绝',
                'RETURN TO RECEIVING',
                '退回至接收',
                'DELIVER',
                '交货',
                'RECEIVE',
                '接收',
                'ACCEPT',
                '接受',
                'TRANSFER',
                '转移',
                'RETURN TO VENDOR',
                '退回至供应商')               类型,
         rcvt.comments                     备注,
         rcvt.RMA_REFERENCE     红冲号码,
         RCVT.                 *
    FROM po.rcv_transactions          rcvt,
         po.rcv_shipment_headers      rsh,
         po.po_headers_all                poh,
         po.po_lines_all                      pol,
         po.po_line_locations_all        plla,
         inv.mtl_system_items_b       msi,
  --    inv.MTL_ITEM_LOCATIONS     mil,
         po.po_vendors       pov,
         PO.PO_LINE_TYPES_TL PTL
   WHERE rcvt.organization_id = x                                                    AND
         rcvt.shipment_header_id = rsh.shipment_header_id                 AND
         poh.po_header_id = rcvt.po_header_id AND poh.org_id = y      AND
         pol.po_line_id = rcvt.po_line_id AND msi.organization_id = x    AND
         pol.item_id = msi.inventory_item_id                                        AND
         rsh.vendor_id = pov.vendor_id AND PTL.LANGUAGE = 'ZHS'      AND
         PTL.LINE_TYPE_ID = POL.LINE_TYPE_ID                                  AND
      --plla.quantity_accepted >0                                                       AND
      --plla.quantity_accepted > rcvt.quantity                                      AND
         pOL.po_line_id = plla.po_line_id(+)                                         AND
         pOH.org_id = plla.org_id                                                         AND

      --poh.segment1 = '20**010001'                                                AND
         rcvt.INSPECTION_STATUS_CODE = 'NOT INSPECTED'               AND
         POL.line_num = '12'                                                               AND

      --rsh.receipt_num = '20**010100'                                             AND
         (rcvt.transaction_type = 'RECEIVE' OR
         rcvt.transaction_type = 'TRANSFER')
   ORDER BY rsh.receipt_num