VIEW: APPS.RCV_UNPROCESSED_LOT_ISSUES_V

Source

View Text - Preformatted

SELECT rli.lot_num, rli.item_id, rli.shipment_line_id, rti.parent_transaction_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 )) FROM RCV_LOTS_INTERFACE RLI,RCV_TRANSACTIONS_INTERFACE RTI WHERE rli.interface_transaction_id = rti.interface_transaction_id GROUP BY rli.lot_num, rli.item_id, rli.shipment_line_id, rti.parent_transaction_id

View Text - HTML Formatted

SELECT RLI.LOT_NUM
, RLI.ITEM_ID
, RLI.SHIPMENT_LINE_ID
, RTI.PARENT_TRANSACTION_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 ))
FROM RCV_LOTS_INTERFACE RLI
, RCV_TRANSACTIONS_INTERFACE RTI
WHERE RLI.INTERFACE_TRANSACTION_ID = RTI.INTERFACE_TRANSACTION_ID GROUP BY RLI.LOT_NUM
, RLI.ITEM_ID
, RLI.SHIPMENT_LINE_ID
, RTI.PARENT_TRANSACTION_ID