VIEW: APPS.POFV_RECEIVING_TRANSACTIONS

Source

View Text - Preformatted

SELECT DISTINCT RT.TRANSACTION_ID, '_LA:rt.TRANSACTION_TYPE:PO_LOOKUP_CODES:RCV TRANSACTION TYPE:DISPLAYED_FIELD', RT.TRANSACTION_DATE, RT.QUANTITY, RT.COMMENTS, RT.CURRENCY_CONVERSION_RATE, RT.CURRENCY_CONVERSION_DATE, '_LA:rt.DESTINATION_TYPE_CODE:PO_LOOKUP_CODES:RCV DESTINATION TYPE:DISPLAYED_FIELD', RT.INSPECTION_QUALITY_CODE, '_LA:rt.INSPECTION_STATUS_CODE:PO_LOOKUP_CODES:INSPECTION STATUS:DISPLAYED_FIELD', RT.INVOICE_STATUS_CODE, RT.PRIMARY_QUANTITY, RT.SOURCE_DOC_QUANTITY, RT.PO_UNIT_PRICE, '_LA:rt.RECEIPT_EXCEPTION_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', RT.RMA_REFERENCE, '_LA:rt.SOURCE_DOCUMENT_CODE:PO_LOOKUP_CODES:SHIPMENT SOURCE DOCUMENT TYPE:DISPLAYED_FIELD', RT.SUBSTITUTE_UNORDERED_CODE, RT.VENDOR_LOT_NUM, '_LA:rt.USER_ENTERED_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', RE.REASON_NAME, '_DF:PO:RCV_TRANSACTIONS:rt', RT.SHIPMENT_HEADER_ID, SH.SHIPMENT_NUM, RT.SHIPMENT_LINE_ID, SL.LINE_NUM, RT.INV_TRANSACTION_ID, RT.UNIT_OF_MEASURE, TU.UOM_CLASS, TU.DESCRIPTION, RT.PRIMARY_UNIT_OF_MEASURE, PU.UOM_CLASS, PU.DESCRIPTION, RT.SOURCE_DOC_UNIT_OF_MEASURE, SU.UOM_CLASS, SU.DESCRIPTION, RT.EMPLOYEE_ID, RV.EMPLOYEE_NUMBER, RV.FULL_NAME, RT.PARENT_TRANSACTION_ID, '_LA:pt.TRANSACTION_TYPE:PO_LOOKUP_CODES:RCV TRANSACTION TYPE:DISPLAYED_FIELD', PH.ORG_ID, OP.NAME, RT.PO_HEADER_ID, PH.SEGMENT1, '_LA:ph.TYPE_LOOKUP_CODE:PO_LOOKUP_CODES:DOCUMENT SUBTYPE:DISPLAYED_FIELD', RT.PO_RELEASE_ID, PR.RELEASE_NUM, RT.PO_LINE_ID, PL.LINE_NUM, RT.PO_LINE_LOCATION_ID, PS.SHIPMENT_NUM, '_LA:ps.SHIPMENT_TYPE:PO_LOOKUP_CODES:SHIPMENT TYPE:DISPLAYED_FIELD', RT.PO_DISTRIBUTION_ID, PD.DISTRIBUTION_NUM, RT.REQUISITION_LINE_ID, RH.SEGMENT1, RL.LINE_NUM, RT.REQ_DISTRIBUTION_ID, RD.DISTRIBUTION_NUM, RT.CURRENCY_CODE, CU.NAME, RT.CURRENCY_CONVERSION_TYPE, RA.USER_CONVERSION_TYPE, RA.DESCRIPTION, RT.ROUTING_HEADER_ID, RR.ROUTING_NAME, RT.DELIVER_TO_PERSON_ID, DP.FULL_NAME, RT.DELIVER_TO_LOCATION_ID, LO.LOCATION_CODE, RT.VENDOR_ID, VE.VENDOR_NAME, RT.VENDOR_SITE_ID, VS.VENDOR_SITE_CODE, RT.ORGANIZATION_ID, DC.ORGANIZATION_CODE, DO.NAME, RT.SUBINVENTORY, RT.LOCATOR_ID, LC.DESCRIPTION, RT.BOM_RESOURCE_ID, BR.RESOURCE_CODE, RT.DEPARTMENT_CODE, BD.DESCRIPTION, RT.WIP_ENTITY_ID, WE.WIP_ENTITY_NAME, RT.WIP_LINE_ID, WL.LINE_CODE, RT.WIP_OPERATION_SEQ_NUM, RT.WIP_REPETITIVE_SCHEDULE_ID, RT.WIP_RESOURCE_SEQ_NUM, RT.INVOICE_ID, IV.INVOICE_NUM, RT.CREATION_DATE, RT.CREATED_BY, RT.LAST_UPDATE_DATE, RT.LAST_UPDATED_BY, RT.MVT_STAT_STATUS, RT.QUANTITY_BILLED, RT.AMOUNT_BILLED, '_LA:RT.MATCH_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', RT.MATCH_OPTION, RT.SECONDARY_QUANTITY, RT.SECONDARY_UNIT_OF_MEASURE, RT.SECONDARY_UOM_CODE, RT.QC_GRADE, PS.DROP_SHIP_FLAG, PS.SALES_ORDER_UPDATE_DATE, '_LA:RT.PA_ADDITION_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:RT.CONSIGNED_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD' FROM RCV_TRANSACTIONS RT, RCV_SHIPMENT_HEADERS SH, RCV_SHIPMENT_LINES SL, MTL_MATERIAL_TRANSACTIONS IT, MTL_UNITS_OF_MEASURE TU, MTL_UNITS_OF_MEASURE PU, MTL_UNITS_OF_MEASURE SU, PER_PEOPLE_F RV, RCV_TRANSACTIONS PT, HR_ALL_ORGANIZATION_UNITS OP, PO_HEADERS_ALL PH, PO_RELEASES_ALL PR, PO_LINES_ALL PL, PO_LINE_LOCATIONS_ALL PS, PO_DISTRIBUTIONS_ALL PD, PO_REQUISITION_HEADERS_ALL RH, PO_REQUISITION_LINES_ALL RL, PO_REQ_DISTRIBUTIONS_ALL RD, FND_CURRENCIES_VL CU, GL_DAILY_CONVERSION_TYPES RA, RCV_ROUTING_HEADERS RR, PER_PEOPLE_F DP, HR_LOCATIONS LO, PO_VENDORS VE, PO_VENDOR_SITES_ALL VS, MTL_PARAMETERS DC, HR_ALL_ORGANIZATION_UNITS DO, MTL_SECONDARY_INVENTORIES SI, MTL_ITEM_LOCATIONS LC, MTL_TRANSACTION_REASONS RE, AP_INVOICES_ALL IV, BOM_RESOURCES BR, BOM_DEPARTMENTS BD, WIP_ENTITIES WE, WIP_LINES WL, WIP_OPERATIONS WO, WIP_OPERATION_RESOURCES WR WHERE RT.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID(+) AND RT.SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID AND RT.INV_TRANSACTION_ID = IT.TRANSACTION_ID(+) AND RT.UNIT_OF_MEASURE = TU.UNIT_OF_MEASURE AND RT.PRIMARY_UNIT_OF_MEASURE = PU.UNIT_OF_MEASURE(+) AND RT.SOURCE_DOC_UNIT_OF_MEASURE = SU.UNIT_OF_MEASURE(+) AND RT.EMPLOYEE_ID = RV.PERSON_ID(+) AND RV.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE) AND RV.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE) AND RT.PARENT_TRANSACTION_ID = PT.TRANSACTION_ID(+) AND RT.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND PH.ORG_ID = OP.ORGANIZATION_ID(+) AND RT.PO_RELEASE_ID = PR.PO_RELEASE_ID(+) AND RT.PO_LINE_ID = PL.PO_LINE_ID(+) AND RT.PO_LINE_LOCATION_ID = PS.LINE_LOCATION_ID(+) AND RT.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID(+) AND RT.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID(+) AND RL.REQUISITION_HEADER_ID = RH.REQUISITION_HEADER_ID(+) AND RT.REQ_DISTRIBUTION_ID = RD.DISTRIBUTION_ID(+) AND RT.CURRENCY_CODE = CU.CURRENCY_CODE(+) AND RT.CURRENCY_CONVERSION_TYPE = RA.CONVERSION_TYPE(+) AND RT.ROUTING_HEADER_ID = RR.ROUTING_HEADER_ID(+) AND RT.DELIVER_TO_PERSON_ID = DP.PERSON_ID(+) AND DP.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE) AND DP.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE) AND RT.DELIVER_TO_LOCATION_ID = LO.LOCATION_ID(+) AND RT.VENDOR_ID = VE.VENDOR_ID(+) AND RT.VENDOR_SITE_ID = VS.VENDOR_SITE_ID(+) AND RT.ORGANIZATION_ID = DC.ORGANIZATION_ID(+) AND RT.ORGANIZATION_ID = DO.ORGANIZATION_ID(+) AND RT.SUBINVENTORY = SI.SECONDARY_INVENTORY_NAME(+) AND RT.ORGANIZATION_ID = SI.ORGANIZATION_ID(+) AND RT.LOCATOR_ID = LC.INVENTORY_LOCATION_ID(+) AND RT.ORGANIZATION_ID = LC.ORGANIZATION_ID(+) AND RT.REASON_ID = RE.REASON_ID(+) AND RT.INVOICE_ID = IV.INVOICE_ID(+) AND RT.BOM_RESOURCE_ID = BR.RESOURCE_ID(+) AND RT.DEPARTMENT_CODE = BD.DEPARTMENT_CODE(+) AND RT.ORGANIZATION_ID = BD.ORGANIZATION_ID(+) AND RT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID(+) AND RT.ORGANIZATION_ID = WE.ORGANIZATION_ID(+) AND RT.WIP_LINE_ID = WL.LINE_ID(+) AND RT.ORGANIZATION_ID = WL.ORGANIZATION_ID(+) AND RT.WIP_ENTITY_ID = WO.WIP_ENTITY_ID(+) AND RT.ORGANIZATION_ID = WO.ORGANIZATION_ID(+) AND RT.WIP_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM(+) AND RT.WIP_REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID(+) AND RT.WIP_ENTITY_ID = WR.WIP_ENTITY_ID(+) AND RT.ORGANIZATION_ID = WR.ORGANIZATION_ID(+) AND RT.WIP_OPERATION_SEQ_NUM = WR.OPERATION_SEQ_NUM(+) AND RT.WIP_RESOURCE_SEQ_NUM = WR.RESOURCE_SEQ_NUM(+) AND RT.WIP_REPETITIVE_SCHEDULE_ID = WR.REPETITIVE_SCHEDULE_ID(+) WITH READ ONLY

View Text - HTML Formatted

SELECT DISTINCT RT.TRANSACTION_ID
, '_LA:RT.TRANSACTION_TYPE:PO_LOOKUP_CODES:RCV TRANSACTION TYPE:DISPLAYED_FIELD'
, RT.TRANSACTION_DATE
, RT.QUANTITY
, RT.COMMENTS
, RT.CURRENCY_CONVERSION_RATE
, RT.CURRENCY_CONVERSION_DATE
, '_LA:RT.DESTINATION_TYPE_CODE:PO_LOOKUP_CODES:RCV DESTINATION TYPE:DISPLAYED_FIELD'
, RT.INSPECTION_QUALITY_CODE
, '_LA:RT.INSPECTION_STATUS_CODE:PO_LOOKUP_CODES:INSPECTION STATUS:DISPLAYED_FIELD'
, RT.INVOICE_STATUS_CODE
, RT.PRIMARY_QUANTITY
, RT.SOURCE_DOC_QUANTITY
, RT.PO_UNIT_PRICE
, '_LA:RT.RECEIPT_EXCEPTION_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, RT.RMA_REFERENCE
, '_LA:RT.SOURCE_DOCUMENT_CODE:PO_LOOKUP_CODES:SHIPMENT SOURCE DOCUMENT TYPE:DISPLAYED_FIELD'
, RT.SUBSTITUTE_UNORDERED_CODE
, RT.VENDOR_LOT_NUM
, '_LA:RT.USER_ENTERED_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, RE.REASON_NAME
, '_DF:PO:RCV_TRANSACTIONS:RT'
, RT.SHIPMENT_HEADER_ID
, SH.SHIPMENT_NUM
, RT.SHIPMENT_LINE_ID
, SL.LINE_NUM
, RT.INV_TRANSACTION_ID
, RT.UNIT_OF_MEASURE
, TU.UOM_CLASS
, TU.DESCRIPTION
, RT.PRIMARY_UNIT_OF_MEASURE
, PU.UOM_CLASS
, PU.DESCRIPTION
, RT.SOURCE_DOC_UNIT_OF_MEASURE
, SU.UOM_CLASS
, SU.DESCRIPTION
, RT.EMPLOYEE_ID
, RV.EMPLOYEE_NUMBER
, RV.FULL_NAME
, RT.PARENT_TRANSACTION_ID
, '_LA:PT.TRANSACTION_TYPE:PO_LOOKUP_CODES:RCV TRANSACTION TYPE:DISPLAYED_FIELD'
, PH.ORG_ID
, OP.NAME
, RT.PO_HEADER_ID
, PH.SEGMENT1
, '_LA:PH.TYPE_LOOKUP_CODE:PO_LOOKUP_CODES:DOCUMENT SUBTYPE:DISPLAYED_FIELD'
, RT.PO_RELEASE_ID
, PR.RELEASE_NUM
, RT.PO_LINE_ID
, PL.LINE_NUM
, RT.PO_LINE_LOCATION_ID
, PS.SHIPMENT_NUM
, '_LA:PS.SHIPMENT_TYPE:PO_LOOKUP_CODES:SHIPMENT TYPE:DISPLAYED_FIELD'
, RT.PO_DISTRIBUTION_ID
, PD.DISTRIBUTION_NUM
, RT.REQUISITION_LINE_ID
, RH.SEGMENT1
, RL.LINE_NUM
, RT.REQ_DISTRIBUTION_ID
, RD.DISTRIBUTION_NUM
, RT.CURRENCY_CODE
, CU.NAME
, RT.CURRENCY_CONVERSION_TYPE
, RA.USER_CONVERSION_TYPE
, RA.DESCRIPTION
, RT.ROUTING_HEADER_ID
, RR.ROUTING_NAME
, RT.DELIVER_TO_PERSON_ID
, DP.FULL_NAME
, RT.DELIVER_TO_LOCATION_ID
, LO.LOCATION_CODE
, RT.VENDOR_ID
, VE.VENDOR_NAME
, RT.VENDOR_SITE_ID
, VS.VENDOR_SITE_CODE
, RT.ORGANIZATION_ID
, DC.ORGANIZATION_CODE
, DO.NAME
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, LC.DESCRIPTION
, RT.BOM_RESOURCE_ID
, BR.RESOURCE_CODE
, RT.DEPARTMENT_CODE
, BD.DESCRIPTION
, RT.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, RT.WIP_LINE_ID
, WL.LINE_CODE
, RT.WIP_OPERATION_SEQ_NUM
, RT.WIP_REPETITIVE_SCHEDULE_ID
, RT.WIP_RESOURCE_SEQ_NUM
, RT.INVOICE_ID
, IV.INVOICE_NUM
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.MVT_STAT_STATUS
, RT.QUANTITY_BILLED
, RT.AMOUNT_BILLED
, '_LA:RT.MATCH_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, RT.MATCH_OPTION
, RT.SECONDARY_QUANTITY
, RT.SECONDARY_UNIT_OF_MEASURE
, RT.SECONDARY_UOM_CODE
, RT.QC_GRADE
, PS.DROP_SHIP_FLAG
, PS.SALES_ORDER_UPDATE_DATE
, '_LA:RT.PA_ADDITION_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:RT.CONSIGNED_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS SH
, RCV_SHIPMENT_LINES SL
, MTL_MATERIAL_TRANSACTIONS IT
, MTL_UNITS_OF_MEASURE TU
, MTL_UNITS_OF_MEASURE PU
, MTL_UNITS_OF_MEASURE SU
, PER_PEOPLE_F RV
, RCV_TRANSACTIONS PT
, HR_ALL_ORGANIZATION_UNITS OP
, PO_HEADERS_ALL PH
, PO_RELEASES_ALL PR
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS_ALL PS
, PO_DISTRIBUTIONS_ALL PD
, PO_REQUISITION_HEADERS_ALL RH
, PO_REQUISITION_LINES_ALL RL
, PO_REQ_DISTRIBUTIONS_ALL RD
, FND_CURRENCIES_VL CU
, GL_DAILY_CONVERSION_TYPES RA
, RCV_ROUTING_HEADERS RR
, PER_PEOPLE_F DP
, HR_LOCATIONS LO
, PO_VENDORS VE
, PO_VENDOR_SITES_ALL VS
, MTL_PARAMETERS DC
, HR_ALL_ORGANIZATION_UNITS DO
, MTL_SECONDARY_INVENTORIES SI
, MTL_ITEM_LOCATIONS LC
, MTL_TRANSACTION_REASONS RE
, AP_INVOICES_ALL IV
, BOM_RESOURCES BR
, BOM_DEPARTMENTS BD
, WIP_ENTITIES WE
, WIP_LINES WL
, WIP_OPERATIONS WO
, WIP_OPERATION_RESOURCES WR
WHERE RT.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID(+)
AND RT.SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID
AND RT.INV_TRANSACTION_ID = IT.TRANSACTION_ID(+)
AND RT.UNIT_OF_MEASURE = TU.UNIT_OF_MEASURE
AND RT.PRIMARY_UNIT_OF_MEASURE = PU.UNIT_OF_MEASURE(+)
AND RT.SOURCE_DOC_UNIT_OF_MEASURE = SU.UNIT_OF_MEASURE(+)
AND RT.EMPLOYEE_ID = RV.PERSON_ID(+)
AND RV.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE)
AND RV.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE)
AND RT.PARENT_TRANSACTION_ID = PT.TRANSACTION_ID(+)
AND RT.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND PH.ORG_ID = OP.ORGANIZATION_ID(+)
AND RT.PO_RELEASE_ID = PR.PO_RELEASE_ID(+)
AND RT.PO_LINE_ID = PL.PO_LINE_ID(+)
AND RT.PO_LINE_LOCATION_ID = PS.LINE_LOCATION_ID(+)
AND RT.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID(+)
AND RT.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID(+)
AND RL.REQUISITION_HEADER_ID = RH.REQUISITION_HEADER_ID(+)
AND RT.REQ_DISTRIBUTION_ID = RD.DISTRIBUTION_ID(+)
AND RT.CURRENCY_CODE = CU.CURRENCY_CODE(+)
AND RT.CURRENCY_CONVERSION_TYPE = RA.CONVERSION_TYPE(+)
AND RT.ROUTING_HEADER_ID = RR.ROUTING_HEADER_ID(+)
AND RT.DELIVER_TO_PERSON_ID = DP.PERSON_ID(+)
AND DP.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE)
AND DP.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE)
AND RT.DELIVER_TO_LOCATION_ID = LO.LOCATION_ID(+)
AND RT.VENDOR_ID = VE.VENDOR_ID(+)
AND RT.VENDOR_SITE_ID = VS.VENDOR_SITE_ID(+)
AND RT.ORGANIZATION_ID = DC.ORGANIZATION_ID(+)
AND RT.ORGANIZATION_ID = DO.ORGANIZATION_ID(+)
AND RT.SUBINVENTORY = SI.SECONDARY_INVENTORY_NAME(+)
AND RT.ORGANIZATION_ID = SI.ORGANIZATION_ID(+)
AND RT.LOCATOR_ID = LC.INVENTORY_LOCATION_ID(+)
AND RT.ORGANIZATION_ID = LC.ORGANIZATION_ID(+)
AND RT.REASON_ID = RE.REASON_ID(+)
AND RT.INVOICE_ID = IV.INVOICE_ID(+)
AND RT.BOM_RESOURCE_ID = BR.RESOURCE_ID(+)
AND RT.DEPARTMENT_CODE = BD.DEPARTMENT_CODE(+)
AND RT.ORGANIZATION_ID = BD.ORGANIZATION_ID(+)
AND RT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID(+)
AND RT.ORGANIZATION_ID = WE.ORGANIZATION_ID(+)
AND RT.WIP_LINE_ID = WL.LINE_ID(+)
AND RT.ORGANIZATION_ID = WL.ORGANIZATION_ID(+)
AND RT.WIP_ENTITY_ID = WO.WIP_ENTITY_ID(+)
AND RT.ORGANIZATION_ID = WO.ORGANIZATION_ID(+)
AND RT.WIP_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM(+)
AND RT.WIP_REPETITIVE_SCHEDULE_ID = WO.REPETITIVE_SCHEDULE_ID(+)
AND RT.WIP_ENTITY_ID = WR.WIP_ENTITY_ID(+)
AND RT.ORGANIZATION_ID = WR.ORGANIZATION_ID(+)
AND RT.WIP_OPERATION_SEQ_NUM = WR.OPERATION_SEQ_NUM(+)
AND RT.WIP_RESOURCE_SEQ_NUM = WR.RESOURCE_SEQ_NUM(+)
AND RT.WIP_REPETITIVE_SCHEDULE_ID = WR.REPETITIVE_SCHEDULE_ID(+) WITH READ ONLY