View: RCV_FTE_TRANSACTIONS_V

Product:	PO - Purchasing
Description:	Rolled up transactions for logistics
Implementation/DBA Data:	APPS.RCV_FTE_TRANSACTIONS_V

View Text

SELECT RT.TRANSACTION_ID
, TO_NUMBER(NULL) PARENT_TRANSACTION_ID
, TO_CHAR(NULL) PARENT_TRANSACTION_TYPE
, DECODE(RT.TRANSACTION_TYPE
, 'MATCH'
, 'RECEIPT'
, RT.TRANSACTION_TYPE) TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.SHIPMENT_LINE_ID
, RT.PO_UNIT_PRICE
, RT.CURRENCY_CODE
, RT.QC_GRADE
, RT.COUNTRY_OF_ORIGIN_CODE
, RT.LPN_ID
, RT.LOCATOR_ID
, RT.SUBINVENTORY
, RT.QUANTITY
, MUOM.UOM_CODE UOM_CODE
, RT.PRIMARY_QUANTITY
, PRIMARY_MUOM.UOM_CODE PRIMARY_UOM_CODE
, RT.SECONDARY_QUANTITY
, SECONDARY_MUOM.UOM_CODE SECONDARY_UOM_CODE
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_LOGIN
FROM RCV_TRANSACTIONS RT
, MTL_UNITS_OF_MEASURE MUOM
, MTL_UNITS_OF_MEASURE PRIMARY_MUOM
, MTL_UNITS_OF_MEASURE SECONDARY_MUOM
WHERE RT.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE(+) AND RT.PRIMARY_UNIT_OF_MEASURE = PRIMARY_MUOM.UNIT_OF_MEASURE (+) AND RT.SECONDARY_UNIT_OF_MEASURE = SECONDARY_MUOM.UNIT_OF_MEASURE(+) AND RT.TRANSACTION_TYPE IN ('RECEIVE'
, 'MATCH') UNION ALL SELECT RT.TRANSACTION_ID
, RETURN_PARENTS.TRANSACTION_ID PARENT_TRANSACTION_ID
, RETURN_PARENTS.TRANSACTION_TYPE PARENT_TRANSACTION_TYPE
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.SHIPMENT_LINE_ID
, RT.PO_UNIT_PRICE
, RT.CURRENCY_CODE
, RT.QC_GRADE
, RT.COUNTRY_OF_ORIGIN_CODE
, RT.LPN_ID
, RT.LOCATOR_ID
, RT.SUBINVENTORY
, RT.QUANTITY
, MUOM.UOM_CODE UOM_CODE
, RT.PRIMARY_QUANTITY
, PRIMARY_MUOM.UOM_CODE PRIMARY_UOM_CODE
, RT.SECONDARY_QUANTITY
, SECONDARY_MUOM.UOM_CODE SECONDARY_UOM_CODE
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_LOGIN
FROM RCV_TRANSACTIONS RT
, (SELECT A.TRANSACTION_ID ROOT_TRANSACTION
, B.TRANSACTION_ID
, 'RECEIVE' TRANSACTION_TYPE
FROM RCV_TRANSACTIONS A
, RCV_TRANSACTIONS B
WHERE A.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND B.TRANSACTION_ID = ( SELECT C.TRANSACTION_ID FROM RCV_TRANSACTIONS C WHERE C.TRANSACTION_TYPE IN ('RECEIVE'
, 'MATCH') START WITH C.TRANSACTION_TYPE = 'RETURN TO VENDOR' AND A.TRANSACTION_ID = C.TRANSACTION_ID CONNECT BY PRIOR C.PARENT_TRANSACTION_ID = C.TRANSACTION_ID) ) RETURN_PARENTS
, MTL_UNITS_OF_MEASURE MUOM
, MTL_UNITS_OF_MEASURE PRIMARY_MUOM
, MTL_UNITS_OF_MEASURE SECONDARY_MUOM
WHERE RT.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE(+) AND RT.PRIMARY_UNIT_OF_MEASURE = PRIMARY_MUOM.UNIT_OF_MEASURE (+) AND RT.SECONDARY_UNIT_OF_MEASURE = SECONDARY_MUOM.UNIT_OF_MEASURE(+) AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND RETURN_PARENTS.ROOT_TRANSACTION = RT.TRANSACTION_ID UNION ALL SELECT RT.TRANSACTION_ID
, PARENT_TRANSACTION.TRANSACTION_ID PARENT_TRANSACTION_ID
, PARENT_TRANSACTION.TRANSACTION_TYPE PARENT_TRANSACTION_TYPE
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.SHIPMENT_LINE_ID
, RT.PO_UNIT_PRICE
, RT.CURRENCY_CODE
, RT.QC_GRADE
, RT.COUNTRY_OF_ORIGIN_CODE
, RT.LPN_ID
, RT.LOCATOR_ID
, RT.SUBINVENTORY
, RT.QUANTITY
, MUOM.UOM_CODE UOM_CODE
, RT.PRIMARY_QUANTITY
, PRIMARY_MUOM.UOM_CODE PRIMARY_UOM_CODE
, RT.SECONDARY_QUANTITY
, SECONDARY_MUOM.UOM_CODE SECONDARY_UOM_CODE
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_LOGIN
FROM RCV_TRANSACTIONS RT
, RCV_TRANSACTIONS PARENT_TRANSACTION
, MTL_UNITS_OF_MEASURE MUOM
, MTL_UNITS_OF_MEASURE PRIMARY_MUOM
, MTL_UNITS_OF_MEASURE SECONDARY_MUOM
WHERE RT.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE (+)
AND RT.PRIMARY_UNIT_OF_MEASURE = PRIMARY_MUOM.UNIT_OF_MEASURE (+)
AND RT.SECONDARY_UNIT_OF_MEASURE = SECONDARY_MUOM.UNIT_OF_MEASURE (+)
AND RT.TRANSACTION_TYPE = 'CORRECT'
AND RT.PARENT_TRANSACTION_ID = PARENT_TRANSACTION.TRANSACTION_ID
AND PARENT_TRANSACTION.TRANSACTION_TYPE IN ('RECEIVE'
, 'RETURN TO VENDOR'
, 'MATCH')

Columns