eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.RCV_RETURNS_V

Source

View Text - Preformatted


SELECT rt.transaction_id, rt.last_update_date, rt.last_updated_by, rt.creation_date, rt.created_by, rt.last_update_login, rt.request_id, rt.program_application_id, rt.program_id, rt.program_update_date, rt.transaction_type, rt.transaction_date, rt.quantity, rt.unit_of_measure, rt.shipment_header_id, rt.shipment_line_id, rt.user_entered_flag, rt.interface_source_code, rt.interface_source_line_id, rt.inv_transaction_id, rt.source_document_code, rt.destination_type_code, rt.primary_quantity, rt.primary_unit_of_measure, rt.uom_code, rt.employee_id, rt.parent_transaction_id, rt.po_header_id, rt.po_release_id, rt.po_line_id, rt.po_line_location_id, rt.po_distribution_id, rt.po_revision_num, rt.requisition_line_id, NVL(pll.price_override, pl.unit_price) po_unit_price, rt.currency_code, rt.currency_conversion_type, rt.currency_conversion_rate, rt.currency_conversion_date, rt.routing_header_id, rt.routing_step_id, rrh.routing_name, rt.deliver_to_person_id, rt.deliver_to_location_id, DECODE(rt.destination_type_code, 'RECEIVING', rt.location_id, rt.deliver_to_location_id ), rt.location_id, hrl.location_code, DECODE(rt.source_document_code, 'RMA', NULL, pov.vendor_id) vendor_id, rt.vendor_site_id, DECODE(rt.source_document_code, 'RMA', NULL, pov.vendor_name) vendor, rt.organization_id, rt.subinventory, rt.locator_id, rt.wip_entity_id, rt.wip_line_id, rt.wip_repetitive_schedule_id, rt.wip_operation_seq_num, rt.wip_resource_seq_num, rt.bom_resource_id, rt.substitute_unordered_code, rt.receipt_exception_flag, rt.inspection_status_code, rt.accrual_status_code, rt.inspection_quality_code, rt.vendor_lot_num, rt.rma_reference, rt.comments, rt.attribute_category, rt.attribute1, rt.attribute2, rt.attribute3, rt.attribute4, rt.attribute5, rt.attribute6, rt.attribute7, rt.attribute8, rt.attribute9, rt.attribute10, rt.attribute11, rt.attribute12, rt.attribute13, rt.attribute14, rt.attribute15, rt.req_distribution_id, rt.department_code, rt.reason_id, rt.destination_context, rt.locator_attribute, rt.child_inspection_flag, rt.source_doc_unit_of_measure, rt.source_doc_quantity, rt.movement_id, DECODE(rt.source_document_code, 'RMA', oetl.NAME, NULL) order_type, rsl.government_context, rsl.ussgl_transaction_code, DECODE(rt.source_document_code, 'RMA', NULL, prl.requisition_header_id ) requisition_header_id, rsh.receipt_num, rsh.shipment_num, rsh.freight_carrier_code, rsh.bill_of_lading, rsl.packing_slip, rsl.item_id, rsl.item_description, rsl.category_id, rsl.item_revision, NVL(msi.location_control_code, 1), DECODE(msi.restrict_locators_code, 1, 'Y', 'N'), DECODE(msi.restrict_subinventories_code, 1, 'Y', 'N'), rsl.line_num, rsh.receipt_source_code, NVL(msi.allowed_units_lookup_code, 2), msi.serial_number_control_code, msi.revision_qty_control_code revision_qty_control_code, msi.lot_control_code, NVL(msi.shelf_life_code, 1) shelf_life, NVL(msi.shelf_life_days, 0) shelf_days, msi.organization_id item_organization_id, DECODE (rt.transaction_type, 'UNORDERED', 'STANDARD', DECODE (rt.source_document_code, 'PO', ph.type_lookup_code, rsh.receipt_source_code ) ) po_type, DECODE(rt.source_document_code, 'RMA', NULL, ph.segment1) po_number, DECODE(rt.source_document_code, 'RMA', NULL, pl.line_num) po_line_number, DECODE(rt.source_document_code, 'RMA', oel.ordered_quantity, pll.quantity ) ordered_qty, DECODE(rt.source_document_code, 'RMA', oel.order_quantity_uom, pl.unit_meas_lookup_code ) ordered_uom, DECODE(rt.source_document_code, 'RMA', NULL, pl.vendor_product_num) vendor_item_number, DECODE(rt.source_document_code, 'RMA', OEL.SHIPMENT_NUMBER, pll.shipment_num) po_shipment_number, DECODE(rt.source_document_code, 'RMA', NULL, pl.hazard_class_id) po_hazard_class_id, DECODE(rt.source_document_code, 'RMA', NULL, pl.un_number_id) po_un_number_id, DECODE(rt.source_document_code, 'RMA', NULL, pr.release_num) po_release_num, DECODE(rt.source_document_code, 'RMA', msi.hazard_class_id, NVL(pl.hazard_class_id, msi.hazard_class_id) ) hazard_class_id, DECODE(rt.source_document_code, 'RMA', msi.un_number_id, NVL(pl.un_number_id, msi.un_number_id) ) un_number_id, NVL(msi.outside_operation_flag, 'N'), DECODE(rt.source_document_code, 'RMA', oel.schedule_arrival_date, 'PO', NVL(pll.promised_date, pll.need_by_date) ) expected_receipt_date, rt.oe_order_header_id, DECODE(rt.source_document_code, 'RMA', oeh.order_number, NULL) oe_order_number, rt.oe_order_line_id, DECODE(rt.source_document_code, 'RMA', oel.line_number, NULL) oe_order_line_number, NVL(rt.customer_id, rsh.customer_id) customer_id, rt.customer_site_id, NVL(rt.transfer_lpn_id, rt.lpn_id) lpn_id, DECODE(rt.lpn_id, NVL(rt.transfer_lpn_id, rt.lpn_id), TO_NUMBER(NULL), rt.lpn_id ) transfer_lpn_id, rt.secondary_quantity, rt.secondary_unit_of_measure, DECODE(rt.source_document_code, 'RMA', oel.ordered_quantity2, pll.secondary_quantity ) secondary_ordered_qty, DECODE(rt.source_document_code, 'RMA', oel.ordered_quantity_uom2, pll.secondary_unit_of_measure ) secondary_ordered_uom, rt.qc_grade qc_grade, DECODE(rt.source_document_code, 'RMA', DECODE(oet.order_category_code, 'RETURN', oeh.shipping_instructions, NULL ), ph.note_to_receiver ) note_to_receiver, DECODE(rt.source_document_code, 'RMA', oel.shipping_instructions, pll.note_to_receiver ) pll_note_to_receiver, rt.consigned_flag consigned_flag, DECODE(MSI.TRACKING_QUANTITY_IND,'PS',MSI.SECONDARY_DEFAULT_IND,NULL) SECONDARY_DEFAULT_IND, DECODE(RT.SOURCE_DOCUMENT_CODE,'PO',PH.ORG_ID,'RMA',OEH.ORG_ID,NULL), rt.lcm_shipment_line_id, rt.unit_landed_cost FROM RCV_TRANSACTIONS RT, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, PO_REQUISITION_LINES PRL, HR_LOCATIONS_ALL_TL HRL, MTL_SYSTEM_ITEMS MSI, OE_ORDER_LINES_ALL OEL, OE_ORDER_HEADERS_ALL OEH, OE_TRANSACTION_TYPES_TL OETL, OE_TRANSACTION_TYPES_ALL OET, PO_HEADERS_ALL PH, PO_LINES_ALL PL, PO_LINE_LOCATIONS PLL, PO_RELEASES_ALL PR, RCV_ROUTING_HEADERS RRH, PO_VENDORS POV, FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE ( ( ( ( RT.TRANSACTION_TYPE IN ('RECEIVE', 'TRANSFER', 'ACCEPT' , 'REJECT', 'MATCH') ) OR ( RT.TRANSACTION_TYPE = 'UNORDERED' AND NOT EXISTS ( SELECT 'PROCESSED MATCH ROWS' FROM RCV_TRANSACTIONS RT2 WHERE RT2.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID AND RT2.TRANSACTION_TYPE = 'MATCH' ) AND NOT EXISTS ( SELECT 'UNPROCESSED MATCH ROWS' FROM RCV_TRANSACTIONS_INTERFACE RTI WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID AND RTI.TRANSACTION_TYPE = 'MATCH' ) ) ) AND EXISTS ( SELECT 'POSTIVE RCV SUPPLY' FROM RCV_SUPPLY RS WHERE RS.RCV_TRANSACTION_ID = RT.TRANSACTION_ID AND RS.TO_ORG_PRIMARY_QUANTITY > ( SELECT NVL(SUM(RTI.PRIMARY_QUANTITY),0) FROM RCV_TRANSACTIONS_INTERFACE RTI WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID AND RTI.TRANSACTION_STATUS_CODE = 'PENDING' AND RTI.PROCESSING_STATUS_CODE = 'PENDING' ) ) ) OR ( RT.TRANSACTION_TYPE = 'DELIVER' AND RT.SOURCE_DOCUMENT_CODE <> 'RMA' ) ) AND RT.SOURCE_DOCUMENT_CODE in ('PO', 'RMA') AND NOT EXISTS ( SELECT 'PURCHASE ORDER SHIPMENT CANCELLED OR FC' FROM PO_LINE_LOCATIONS PLL WHERE PLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID AND ( NVL(PLL.CANCEL_FLAG,'N') = 'Y' OR NVL(PLL.CLOSED_CODE,'OPEN') = 'FINALLY CLOSED' OR NVL(PLL.APPROVED_FLAG,'N') <> 'Y' ) ) AND NOT EXISTS ( SELECT 'REQUISITION LINE CANCELLED OR FC' FROM PO_REQUISITION_LINES PRL WHERE PRL.REQUISITION_LINE_ID = RT.REQUISITION_LINE_ID AND ( NVL(PRL.CANCEL_FLAG,'N') = 'Y' OR NVL(PRL.CLOSED_CODE,'OPEN') = 'FINALLY CLOSED' ) ) AND ( MSI.INVENTORY_ITEM_ID(+) = RSL.ITEM_ID AND NVL (MSI.ORGANIZATION_ID, RT.ORGANIZATION_ID) = RT.ORGANIZATION_ID ) AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND RT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+) AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND OEL.LINE_ID(+) = RT.OE_ORDER_LINE_ID AND OEH.HEADER_ID(+) = RT.OE_ORDER_HEADER_ID AND OET.TRANSACTION_TYPE_ID (+) = OEH.ORDER_TYPE_ID AND OET.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+) AND OETL.LANGUAGE(+) = userenv('LANG') AND OET.TRANSACTION_TYPE_CODE(+) = 'ORDER' AND (PH.PO_HEADER_ID (+) = RT.PO_HEADER_ID) AND (PL.PO_LINE_ID (+) = RT.PO_LINE_ID) AND (PLL.LINE_LOCATION_ID (+) = RT.PO_LINE_LOCATION_ID) AND NVL(PLL.ORG_ID,-99) = NVL(PH.ORG_ID,-99) AND (PR.PO_RELEASE_ID(+) = RT.PO_RELEASE_ID) AND RRH.ROUTING_HEADER_ID(+) = RT.ROUTING_HEADER_ID AND POV.VENDOR_ID(+) = RSH.VENDOR_ID AND RT.LOCATION_ID = HRL.LOCATION_ID (+) AND HRL.LANGUAGE(+) = USERENV('LANG') AND NVL(PLL.MATCHING_BASIS(+),'QUANTITY') <> 'AMOUNT' AND PLL.PAYMENT_TYPE IS NULL AND PH.ORG_ID = FSP.ORG_ID (+) AND ( RT.SOURCE_DOCUMENT_CODE!='PO' OR ( NVL(FSP.purch_encumbrance_flag,'N') = 'Y' AND NVL(PLL.encumbered_flag,'N') = 'Y' ) OR NVL(FSP.purch_encumbrance_flag,'N') = 'N' OR ( NVL(FSP.purch_encumbrance_flag,'N') = 'Y' AND NVL(PLL.encumbered_flag,'N') = 'N' AND RT.SOURCE_DOCUMENT_CODE = 'PO' AND exists ( select 1 from po_distributions where line_location_id = pll.line_location_id and destination_type_code = 'SHOP FLOOR' ) ) )

View Text - HTML Formatted

SELECT RT.TRANSACTION_ID
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_LOGIN
, RT.REQUEST_ID
, RT.PROGRAM_APPLICATION_ID
, RT.PROGRAM_ID
, RT.PROGRAM_UPDATE_DATE
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.QUANTITY
, RT.UNIT_OF_MEASURE
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, RT.USER_ENTERED_FLAG
, RT.INTERFACE_SOURCE_CODE
, RT.INTERFACE_SOURCE_LINE_ID
, RT.INV_TRANSACTION_ID
, RT.SOURCE_DOCUMENT_CODE
, RT.DESTINATION_TYPE_CODE
, RT.PRIMARY_QUANTITY
, RT.PRIMARY_UNIT_OF_MEASURE
, RT.UOM_CODE
, RT.EMPLOYEE_ID
, RT.PARENT_TRANSACTION_ID
, RT.PO_HEADER_ID
, RT.PO_RELEASE_ID
, RT.PO_LINE_ID
, RT.PO_LINE_LOCATION_ID
, RT.PO_DISTRIBUTION_ID
, RT.PO_REVISION_NUM
, RT.REQUISITION_LINE_ID
, NVL(PLL.PRICE_OVERRIDE
, PL.UNIT_PRICE) PO_UNIT_PRICE
, RT.CURRENCY_CODE
, RT.CURRENCY_CONVERSION_TYPE
, RT.CURRENCY_CONVERSION_RATE
, RT.CURRENCY_CONVERSION_DATE
, RT.ROUTING_HEADER_ID
, RT.ROUTING_STEP_ID
, RRH.ROUTING_NAME
, RT.DELIVER_TO_PERSON_ID
, RT.DELIVER_TO_LOCATION_ID
, DECODE(RT.DESTINATION_TYPE_CODE
, 'RECEIVING'
, RT.LOCATION_ID
, RT.DELIVER_TO_LOCATION_ID )
, RT.LOCATION_ID
, HRL.LOCATION_CODE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POV.VENDOR_ID) VENDOR_ID
, RT.VENDOR_SITE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POV.VENDOR_NAME) VENDOR
, RT.ORGANIZATION_ID
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, RT.WIP_ENTITY_ID
, RT.WIP_LINE_ID
, RT.WIP_REPETITIVE_SCHEDULE_ID
, RT.WIP_OPERATION_SEQ_NUM
, RT.WIP_RESOURCE_SEQ_NUM
, RT.BOM_RESOURCE_ID
, RT.SUBSTITUTE_UNORDERED_CODE
, RT.RECEIPT_EXCEPTION_FLAG
, RT.INSPECTION_STATUS_CODE
, RT.ACCRUAL_STATUS_CODE
, RT.INSPECTION_QUALITY_CODE
, RT.VENDOR_LOT_NUM
, RT.RMA_REFERENCE
, RT.COMMENTS
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
, RT.REQ_DISTRIBUTION_ID
, RT.DEPARTMENT_CODE
, RT.REASON_ID
, RT.DESTINATION_CONTEXT
, RT.LOCATOR_ATTRIBUTE
, RT.CHILD_INSPECTION_FLAG
, RT.SOURCE_DOC_UNIT_OF_MEASURE
, RT.SOURCE_DOC_QUANTITY
, RT.MOVEMENT_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OETL.NAME
, NULL) ORDER_TYPE
, RSL.GOVERNMENT_CONTEXT
, RSL.USSGL_TRANSACTION_CODE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PRL.REQUISITION_HEADER_ID ) REQUISITION_HEADER_ID
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RSH.FREIGHT_CARRIER_CODE
, RSH.BILL_OF_LADING
, RSL.PACKING_SLIP
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, RSL.CATEGORY_ID
, RSL.ITEM_REVISION
, NVL(MSI.LOCATION_CONTROL_CODE
, 1)
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'N')
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N')
, RSL.LINE_NUM
, RSH.RECEIPT_SOURCE_CODE
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2)
, MSI.SERIAL_NUMBER_CONTROL_CODE
, MSI.REVISION_QTY_CONTROL_CODE REVISION_QTY_CONTROL_CODE
, MSI.LOT_CONTROL_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_DAYS
, MSI.ORGANIZATION_ID ITEM_ORGANIZATION_ID
, DECODE (RT.TRANSACTION_TYPE
, 'UNORDERED'
, 'STANDARD'
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PH.TYPE_LOOKUP_CODE
, RSH.RECEIPT_SOURCE_CODE ) ) PO_TYPE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PH.SEGMENT1) PO_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.LINE_NUM) PO_LINE_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY
, PLL.QUANTITY ) ORDERED_QTY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDER_QUANTITY_UOM
, PL.UNIT_MEAS_LOOKUP_CODE ) ORDERED_UOM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.VENDOR_PRODUCT_NUM) VENDOR_ITEM_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPMENT_NUMBER
, PLL.SHIPMENT_NUM) PO_SHIPMENT_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.HAZARD_CLASS_ID) PO_HAZARD_CLASS_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.UN_NUMBER_ID) PO_UN_NUMBER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PR.RELEASE_NUM) PO_RELEASE_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.HAZARD_CLASS_ID
, NVL(PL.HAZARD_CLASS_ID
, MSI.HAZARD_CLASS_ID) ) HAZARD_CLASS_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.UN_NUMBER_ID
, NVL(PL.UN_NUMBER_ID
, MSI.UN_NUMBER_ID) ) UN_NUMBER_ID
, NVL(MSI.OUTSIDE_OPERATION_FLAG
, 'N')
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SCHEDULE_ARRIVAL_DATE
, 'PO'
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) ) EXPECTED_RECEIPT_DATE
, RT.OE_ORDER_HEADER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEH.ORDER_NUMBER
, NULL) OE_ORDER_NUMBER
, RT.OE_ORDER_LINE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.LINE_NUMBER
, NULL) OE_ORDER_LINE_NUMBER
, NVL(RT.CUSTOMER_ID
, RSH.CUSTOMER_ID) CUSTOMER_ID
, RT.CUSTOMER_SITE_ID
, NVL(RT.TRANSFER_LPN_ID
, RT.LPN_ID) LPN_ID
, DECODE(RT.LPN_ID
, NVL(RT.TRANSFER_LPN_ID
, RT.LPN_ID)
, TO_NUMBER(NULL)
, RT.LPN_ID ) TRANSFER_LPN_ID
, RT.SECONDARY_QUANTITY
, RT.SECONDARY_UNIT_OF_MEASURE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY2
, PLL.SECONDARY_QUANTITY ) SECONDARY_ORDERED_QTY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY_UOM2
, PLL.SECONDARY_UNIT_OF_MEASURE ) SECONDARY_ORDERED_UOM
, RT.QC_GRADE QC_GRADE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, DECODE(OET.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL )
, PH.NOTE_TO_RECEIVER ) NOTE_TO_RECEIVER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPPING_INSTRUCTIONS
, PLL.NOTE_TO_RECEIVER ) PLL_NOTE_TO_RECEIVER
, RT.CONSIGNED_FLAG CONSIGNED_FLAG
, DECODE(MSI.TRACKING_QUANTITY_IND
, 'PS'
, MSI.SECONDARY_DEFAULT_IND
, NULL) SECONDARY_DEFAULT_IND
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PH.ORG_ID
, 'RMA'
, OEH.ORG_ID
, NULL)
, RT.LCM_SHIPMENT_LINE_ID
, RT.UNIT_LANDED_COST
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, PO_REQUISITION_LINES PRL
, HR_LOCATIONS_ALL_TL HRL
, MTL_SYSTEM_ITEMS MSI
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, OE_TRANSACTION_TYPES_TL OETL
, OE_TRANSACTION_TYPES_ALL OET
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS PLL
, PO_RELEASES_ALL PR
, RCV_ROUTING_HEADERS RRH
, PO_VENDORS POV
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE ( ( ( ( RT.TRANSACTION_TYPE IN ('RECEIVE'
, 'TRANSFER'
, 'ACCEPT'
, 'REJECT'
, 'MATCH') ) OR ( RT.TRANSACTION_TYPE = 'UNORDERED'
AND NOT EXISTS ( SELECT 'PROCESSED MATCH ROWS'
FROM RCV_TRANSACTIONS RT2
WHERE RT2.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT2.TRANSACTION_TYPE = 'MATCH' )
AND NOT EXISTS ( SELECT 'UNPROCESSED MATCH ROWS'
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
AND RTI.TRANSACTION_TYPE = 'MATCH' ) ) )
AND EXISTS ( SELECT 'POSTIVE RCV SUPPLY'
FROM RCV_SUPPLY RS
WHERE RS.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RS.TO_ORG_PRIMARY_QUANTITY > ( SELECT NVL(SUM(RTI.PRIMARY_QUANTITY)
, 0)
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
AND RTI.TRANSACTION_STATUS_CODE = 'PENDING'
AND RTI.PROCESSING_STATUS_CODE = 'PENDING' ) ) ) OR ( RT.TRANSACTION_TYPE = 'DELIVER'
AND RT.SOURCE_DOCUMENT_CODE <> 'RMA' ) )
AND RT.SOURCE_DOCUMENT_CODE IN ('PO'
, 'RMA')
AND NOT EXISTS ( SELECT 'PURCHASE ORDER SHIPMENT CANCELLED OR FC'
FROM PO_LINE_LOCATIONS PLL
WHERE PLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
AND ( NVL(PLL.CANCEL_FLAG
, 'N') = 'Y' OR NVL(PLL.CLOSED_CODE
, 'OPEN') = 'FINALLY CLOSED' OR NVL(PLL.APPROVED_FLAG
, 'N') <> 'Y' ) )
AND NOT EXISTS ( SELECT 'REQUISITION LINE CANCELLED OR FC'
FROM PO_REQUISITION_LINES PRL
WHERE PRL.REQUISITION_LINE_ID = RT.REQUISITION_LINE_ID
AND ( NVL(PRL.CANCEL_FLAG
, 'N') = 'Y' OR NVL(PRL.CLOSED_CODE
, 'OPEN') = 'FINALLY CLOSED' ) )
AND ( MSI.INVENTORY_ITEM_ID(+) = RSL.ITEM_ID
AND NVL (MSI.ORGANIZATION_ID
, RT.ORGANIZATION_ID) = RT.ORGANIZATION_ID )
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND OEL.LINE_ID(+) = RT.OE_ORDER_LINE_ID
AND OEH.HEADER_ID(+) = RT.OE_ORDER_HEADER_ID
AND OET.TRANSACTION_TYPE_ID (+) = OEH.ORDER_TYPE_ID
AND OET.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV('LANG')
AND OET.TRANSACTION_TYPE_CODE(+) = 'ORDER'
AND (PH.PO_HEADER_ID (+) = RT.PO_HEADER_ID)
AND (PL.PO_LINE_ID (+) = RT.PO_LINE_ID)
AND (PLL.LINE_LOCATION_ID (+) = RT.PO_LINE_LOCATION_ID)
AND NVL(PLL.ORG_ID
, -99) = NVL(PH.ORG_ID
, -99)
AND (PR.PO_RELEASE_ID(+) = RT.PO_RELEASE_ID)
AND RRH.ROUTING_HEADER_ID(+) = RT.ROUTING_HEADER_ID
AND POV.VENDOR_ID(+) = RSH.VENDOR_ID
AND RT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND NVL(PLL.MATCHING_BASIS(+)
, 'QUANTITY') <> 'AMOUNT'
AND PLL.PAYMENT_TYPE IS NULL
AND PH.ORG_ID = FSP.ORG_ID (+)
AND ( RT.SOURCE_DOCUMENT_CODE!='PO' OR ( NVL(FSP.PURCH_ENCUMBRANCE_FLAG
, 'N') = 'Y'
AND NVL(PLL.ENCUMBERED_FLAG
, 'N') = 'Y' ) OR NVL(FSP.PURCH_ENCUMBRANCE_FLAG
, 'N') = 'N' OR ( NVL(FSP.PURCH_ENCUMBRANCE_FLAG
, 'N') = 'Y'
AND NVL(PLL.ENCUMBERED_FLAG
, 'N') = 'N'
AND RT.SOURCE_DOCUMENT_CODE = 'PO'
AND EXISTS ( SELECT 1
FROM PO_DISTRIBUTIONS
WHERE LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND DESTINATION_TYPE_CODE = 'SHOP FLOOR' ) ) )