采购接收入库事务处理数据流

--接收数量
SELECT RSH.RECEIPT_NUM
      ,RT.QUANTITY --当前接收 接收数量
      ,RT.*
  FROM RCV_SHIPMENT_HEADERS RSH
      ,RCV_SHIPMENT_LINES   RSL
      ,RCV_TRANSACTIONS     RT
 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
   AND RSH.RECEIPT_NUM = '255182'
   AND RSH.SHIPMENT_HEADER_ID = 1179227
   AND RT.TRANSACTION_TYPE = 'RECEIVE';
--检验数量
SELECT RT.*
  FROM RCV_SHIPMENT_HEADERS RSH
      ,RCV_SHIPMENT_LINES   RSL
      ,RCV_TRANSACTIONS     RT
 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
   AND RSH.RECEIPT_NUM = '255182'
   AND RSH.SHIPMENT_HEADER_ID = 1179227
   AND RT.TRANSACTION_TYPE IN ('ACCEPT'
                              ,'REJECT');
--入库数量
SELECT RT.*
  FROM RCV_SHIPMENT_HEADERS RSH
      ,RCV_SHIPMENT_LINES   RSL
      ,RCV_TRANSACTIONS     RT
 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
   AND RSH.RECEIPT_NUM = '255182'
   AND RSH.SHIPMENT_HEADER_ID = 1179227
   AND RT.TRANSACTION_TYPE IN ('DELIVER');
--库存退货至供应商数量
SELECT RT.*
  FROM RCV_SHIPMENT_HEADERS RSH
      ,RCV_SHIPMENT_LINES   RSL
      ,RCV_TRANSACTIONS     RT
 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
   AND RSH.RECEIPT_NUM = '255182'
   AND RSH.SHIPMENT_HEADER_ID = 1179227
   AND RT.TRANSACTION_TYPE IN ('RETURN TO VENDOR');
--库存退货至接收数量 （包含库存退货之接收及库存退货之供应商数量，因库存退货之供应商时先退货至接收再从接收退货之供应商）
SELECT RT.*
  FROM RCV_SHIPMENT_HEADERS RSH
      ,RCV_SHIPMENT_LINES   RSL
      ,RCV_TRANSACTIONS     RT
 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
   AND RSH.RECEIPT_NUM = '255182'
   AND RSH.SHIPMENT_HEADER_ID = 1179227
   AND RT.TRANSACTION_TYPE IN ('RETURN TO RECEIVING');

--接收之库存事务处理数量
SELECT MMT.*
  FROM RCV_SHIPMENT_HEADERS      RSH
      ,RCV_SHIPMENT_LINES        RSL
      ,RCV_TRANSACTIONS          RT
      ,MTL_MATERIAL_TRANSACTIONS MMT
 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
   AND MMT.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
   AND RSH.RECEIPT_NUM = '255182'
   AND RSH.SHIPMENT_HEADER_ID = 1179227;

--常用数量
SELECT RSH.RECEIPT_NUM
      ,RT.QUANTITY --当前接收 接收数量
      
      ,NVL((SELECT SUM(RT1.QUANTITY)
             FROM RCV_TRANSACTIONS RT1
            WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RT1.TRANSACTION_TYPE = 'RETURN TO VENDOR')
          ,0) RETURN_TO_VENDOR_QTY --当前接收退货至供应商数量
      
      ,RT.QUANTITY -
       NVL((SELECT SUM(RT1.QUANTITY)
             FROM RCV_TRANSACTIONS RT1
            WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RT1.TRANSACTION_TYPE = 'RETURN TO VENDOR')
          ,0) RECEIVED_QTY --当前接收数量-当前接收退货至供应商数量=实际接收数量
      
      ,NVL((SELECT SUM(RT1.QUANTITY)
             FROM RCV_TRANSACTIONS RT1
            WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RT1.TRANSACTION_TYPE = 'ACCEPT')
          ,0) INSPECTION_ACCEPT_QTY --当前接收检验合格数量
      
      ,NVL((SELECT SUM(RT1.QUANTITY)
             FROM RCV_TRANSACTIONS RT1
            WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RT1.TRANSACTION_TYPE = 'REJECT')
          ,0) INSPECTION_REJECT_QTY --当前接收检验不合格数量
      
      ,NVL((SELECT SUM(RT1.QUANTITY)
             FROM RCV_TRANSACTIONS RT1
            WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RT1.TRANSACTION_TYPE = 'RETURN TO RECEIVING')
          ,0) RETURN_TO_RECEIVE_QTY --当前接收入库后又退货至接收 数量
      
      ,NVL((SELECT SUM(RT1.QUANTITY)
             FROM RCV_TRANSACTIONS RT1
            WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RT1.TRANSACTION_TYPE = 'RETURN TO VENDOR')
          ,0) RETURN_TO_VENDOR_QTY --当前接收入库后又退货至供应商数量
      
      ,NVL((SELECT SUM(RT1.QUANTITY)
             FROM RCV_TRANSACTIONS RT1
            WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RT1.TRANSACTION_TYPE = 'DELIVER')
          ,0) DELIVERED_QTY --已入库数量（不含入库后退货）
      
      ,NVL((SELECT SUM(RT1.QUANTITY)
             FROM RCV_TRANSACTIONS RT1
            WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RT1.TRANSACTION_TYPE = 'DELIVER')
          ,0) - NVL((SELECT SUM(RT1.QUANTITY)
                      FROM RCV_TRANSACTIONS RT1
                     WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
                       AND RT1.TRANSACTION_TYPE = 'RETURN TO RECEIVING')
                   ,0) ACTUAL_DELIVERED_QTY --入库数量 - 退货至接收数量=实际入库数量
      
      ,NVL((SELECT SUM(RT1.QUANTITY)
             FROM RCV_TRANSACTIONS RT1
            WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RT1.TRANSACTION_TYPE = 'RETURN TO RECEIVING')
          ,0) - NVL((SELECT SUM(RT1.QUANTITY)
                      FROM RCV_TRANSACTIONS RT1
                     WHERE RT1.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
                       AND RT1.TRANSACTION_TYPE = 'RETURN TO VENDOR')
                   ,0) UNRECEIVED_QTY --退货至接收数量-退货之供应商数量
  FROM RCV_SHIPMENT_HEADERS RSH
      ,RCV_SHIPMENT_LINES   RSL
      ,RCV_TRANSACTIONS     RT
 WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
   AND RSH.RECEIPT_NUM = '255182'
   AND RSH.SHIPMENT_HEADER_ID = 1179227
   AND RT.TRANSACTION_TYPE = 'RECEIVE';