VIEW: APPS.RCV_UNPROCESSED_LOTS_V

Source

View Text - Preformatted

SELECT rli.lot_num , rli.item_id , rli.shipment_line_id, sum( decode(rti.transaction_type, 'DELIVER' , rli.primary_quantity, 'CORRECT' , SIGN(rti.primary_quantity)* rli.primary_quantity, 'RECEIVE', rli.primary_quantity, 'RETURN TO RECEIVING', rli.primary_quantity * -1, 'RETURN TO VENDOR', rli.primary_quantity * -1, 0)), sum( decode(rti.transaction_type, 'DELIVER' , rli.quantity, 'CORRECT' , SIGN(rti.quantity)*rli.quantity, 'RECEIVE', rli.quantity, 'RETURN TO RECEIVING', rli.quantity * -1, 'RETURN TO VENDOR', rli.quantity * -1, 0)), rli.parent_lot_number, sum( decode(rti.transaction_type, 'DELIVER' , rli.secondary_quantity, 'CORRECT' , SIGN(rti.secondary_quantity)*rli.secondary_quantity, 'RECEIVE', rli.secondary_quantity, 'RETURN TO RECEIVING', rli.secondary_quantity * -1, 'RETURN TO VENDOR', rli.secondary_quantity * -1, 0)) FROM RCV_LOTS_INTERFACE RLI,RCV_TRANSACTIONS_INTERFACE RTI WHERE rli.interface_transaction_id = rti.interface_transaction_id AND rti.processing_status_code = 'PENDING' and nvl(rti.transaction_status_code,'PENDING') = 'PENDING' GROUP BY rli.lot_num, rli.item_id, rli.shipment_line_id,rli.parent_lot_number

View Text - HTML Formatted

SELECT RLI.LOT_NUM
, RLI.ITEM_ID
, RLI.SHIPMENT_LINE_ID
, SUM( DECODE(RTI.TRANSACTION_TYPE
, 'DELIVER'
, RLI.PRIMARY_QUANTITY
, 'CORRECT'
, SIGN(RTI.PRIMARY_QUANTITY)* RLI.PRIMARY_QUANTITY
, 'RECEIVE'
, RLI.PRIMARY_QUANTITY
, 'RETURN TO RECEIVING'
, RLI.PRIMARY_QUANTITY * -1
, 'RETURN TO VENDOR'
, RLI.PRIMARY_QUANTITY * -1
, 0))
, SUM( DECODE(RTI.TRANSACTION_TYPE
, 'DELIVER'
, RLI.QUANTITY
, 'CORRECT'
, SIGN(RTI.QUANTITY)*RLI.QUANTITY
, 'RECEIVE'
, RLI.QUANTITY
, 'RETURN TO RECEIVING'
, RLI.QUANTITY * -1
, 'RETURN TO VENDOR'
, RLI.QUANTITY * -1
, 0))
, RLI.PARENT_LOT_NUMBER
, SUM( DECODE(RTI.TRANSACTION_TYPE
, 'DELIVER'
, RLI.SECONDARY_QUANTITY
, 'CORRECT'
, SIGN(RTI.SECONDARY_QUANTITY)*RLI.SECONDARY_QUANTITY
, 'RECEIVE'
, RLI.SECONDARY_QUANTITY
, 'RETURN TO RECEIVING'
, RLI.SECONDARY_QUANTITY * -1
, 'RETURN TO VENDOR'
, RLI.SECONDARY_QUANTITY * -1
, 0))
FROM RCV_LOTS_INTERFACE RLI
, RCV_TRANSACTIONS_INTERFACE RTI
WHERE RLI.INTERFACE_TRANSACTION_ID = RTI.INTERFACE_TRANSACTION_ID
AND RTI.PROCESSING_STATUS_CODE = 'PENDING'
AND NVL(RTI.TRANSACTION_STATUS_CODE
, 'PENDING') = 'PENDING' GROUP BY RLI.LOT_NUM
, RLI.ITEM_ID
, RLI.SHIPMENT_LINE_ID
, RLI.PARENT_LOT_NUMBER