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_CORRECTIONS_V

Source

View Text - Preformatted


SELECT rt.transaction_id, rt.creation_date, rt.transaction_type, rt.transaction_date, rt.quantity, rt.unit_of_measure, rt.shipment_header_id, rt.shipment_line_id, rt.source_document_code, rt.destination_type_code, rt.primary_unit_of_measure, 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, prl.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, rt.deliver_to_person_id, rt.deliver_to_location_id, rt.vendor_id, rt.vendor_site_id, rt.organization_id, rt.subinventory, rt.locator_id, rt.location_id, rt.receipt_exception_flag, rt.inspection_status_code, rt.vendor_lot_num, 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.reason_id, rt.destination_context, rt.locator_attribute, rt.source_doc_unit_of_measure, rsl.government_context, rsl.ussgl_transaction_code, prl.requisition_header_id, rsh.receipt_num, rsh.shipment_num, rsl.line_num, rsh.packing_slip, rsl.item_id, rsl.item_description, rsl.category_id, rsl.item_revision, rsh.receipt_source_code, msi.allowed_units_lookup_code, msi.serial_number_control_code serial_number_cc, 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, 'PO', ph.segment1, 'RMA', NULL, rsh.shipment_num ) po_number, DECODE (rt.source_document_code, 'RMA', NULL, pl.line_num) po_line_number, DECODE (rt.source_document_code, 'RMA', OEL.SHIPMENT_NUMBER, pll.shipment_num) po_shipment_number, 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, DECODE (rt.source_document_code, 'PO', pov.vendor_name, 'RMA', NULL, ood.NAME ) vendor, DECODE (rt.source_document_code, 'RMA', oetl.NAME, null ) order_type, DECODE (rt.source_document_code, 'RMA', DECODE (oeot.order_category_code, 'RETURN', oeh.shipping_instructions, NULL ), ph.note_to_receiver ) note_to_receiver, rsl.comments inv_req_note_to_receiver, hrl.location_code, rsh.bill_of_lading, rsh.freight_carrier_code freight_carrier, rsh.waybill_airbill_num, DECODE (rt.source_document_code, 'RMA', NULL, pl.vendor_product_num) vendor_item_number, rrh.routing_name, DECODE (rt.source_document_code, 'PO', pll.quantity, 'RMA', oel.ordered_quantity, rsl.quantity_shipped ) ordered_qty, DECODE (rt.source_document_code, 'PO', pl.unit_meas_lookup_code, 'RMA', oel.order_quantity_uom, rsl.unit_of_measure ) ordered_uom, DECODE (rt.source_document_code, 'RMA', oel.schedule_arrival_date, NVL (pll.need_by_date, pll.promised_date) ) due_date_vendor, rsh.expected_receipt_date due_date_internal, mtr.reason_name reason_code, hre.full_name, rt.wip_entity_id, rt.wip_line_id, rt.wip_repetitive_schedule_id, rt.wip_operation_seq_num, rt.wip_resource_seq_num, rt.department_code, rt.bom_resource_id, DECODE (rt.source_document_code, 'RMA', NULL, pll.qty_rcv_exception_code ) qty_rcv_exception_code, rt.movement_id, rt.inspection_quality_code, rt.substitute_unordered_code, dct.user_conversion_type, rt.oe_order_header_id, DECODE (rt.source_document_code, 'RMA', oeh.order_number, NULL) oe_order_num, rt.oe_order_line_id, DECODE (rt.source_document_code, 'RMA', oel.line_number, NULL) oe_order_line_num, rt.customer_id, DECODE (rt.source_document_code, 'RMA', SUBSTR (hzp.party_name, 1, 255), NULL ) customer, rt.lpn_id lpn_id, rt.transfer_lpn_id transfer_lpn_id, rt.secondary_quantity, rt.secondary_unit_of_measure, DECODE (rt.source_document_code, 'PO', pll.secondary_quantity, 'RMA', oel.ordered_quantity2, rsl.secondary_quantity_shipped ) secondary_ordered_qty, DECODE (rt.source_document_code, 'PO', pll.secondary_unit_of_measure, 'RMA', oel.ordered_quantity_uom2, rsl.secondary_unit_of_measure ) secondary_ordered_uom, rt.qc_grade qc_grade, DECODE (rt.source_document_code, 'RMA', oel.shipping_instructions, pll.note_to_receiver ) pll_note_to_receiver, NVL (rsl.employee_id, rt.employee_id), DECODE(MSI.TRACKING_QUANTITY_IND,'PS',MSI.SECONDARY_DEFAULT_IND,NULL), DECODE(RT.SOURCE_DOCUMENT_CODE,'PO',PH.ORG_ID, 'REQ',PRL.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, mtl_system_items msi, oe_order_headers_all oeh, oe_order_lines_all oel, hz_cust_accounts hzca, hz_parties hzp, oe_transaction_types_tl oetl, oe_transaction_types_all oeot, po_headers_all ph, po_lines_all pl, po_line_locations pll, po_releases_all pr, po_vendors pov, hr_locations_all_tl hrl, mtl_transaction_reasons mtr, rcv_routing_headers rrh, per_people_f hre, hr_all_organization_units_tl ood, gl_daily_conversion_types dct, FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE ( rt.transaction_type IN ('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH') OR ( rt.transaction_type IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER') AND NOT EXISTS ( SELECT 'rtv to processed matched unordered receipt' FROM rcv_transactions rt2, rcv_transactions rt3 WHERE rt2.transaction_id = rt.parent_transaction_id AND rt2.transaction_type = 'UNORDERED' AND rt3.transaction_type = 'MATCH' AND rt3.parent_transaction_id = rt2.transaction_id) AND NOT EXISTS ( SELECT 'rtv to unprocessed matched unordered receipt' FROM rcv_transactions rt2, rcv_transactions_interface rti WHERE rt2.transaction_id = rt.parent_transaction_id AND rt2.transaction_type = 'UNORDERED' AND rti.transaction_type = 'MATCH' AND rti.parent_transaction_id = rt2.transaction_id) ) OR ( rt.transaction_type = 'DELIVER' AND NOT ( rt.destination_type_code = 'INVENTORY' AND rt.source_document_code IN ('REQ', 'INVENTORY', 'RMA') ) ) OR ( rt.transaction_type = 'UNORDERED' AND NOT EXISTS ( SELECT 'processed matched rows' FROM rcv_transactions rt2 WHERE rt2.parent_transaction_id = rt.transaction_id AND rt2.transaction_type = 'MATCH') AND NOT EXISTS ( SELECT 'unprocessed matched rows' FROM rcv_transactions_interface rti WHERE rti.parent_transaction_id = rt.transaction_id AND rti.transaction_type = 'MATCH') ) ) 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 (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 ( msi.inventory_item_id(+) = rsl.item_id AND NVL (msi.organization_id, rt.organization_id) = rt.organization_id ) AND (oeh.header_id(+) = rt.oe_order_header_id) AND (oel.line_id(+) = rt.oe_order_line_id) AND (oeot.transaction_type_id(+) = oeh.order_type_id) AND oeot.transaction_type_id = oetl.transaction_type_id(+) AND oetl.LANGUAGE(+) = USERENV ('LANG') AND oeot.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 pov.vendor_id(+) = rsh.vendor_id AND hzca.cust_account_id(+) = rsh.customer_id AND hzca.party_id = hzp.party_id(+) AND hrl.location_id(+) = NVL (rt.deliver_to_location_id, rt.location_id) AND hrl.LANGUAGE(+) = USERENV ('LANG') AND rrh.routing_header_id(+) = rt.routing_header_id AND mtr.reason_id(+) = rt.reason_id AND hre.person_id(+) = rt.deliver_to_person_id AND ( ( TRUNC (SYSDATE) BETWEEN hre.effective_start_date AND hre.effective_end_date AND hre.person_id IS NOT NULL ) OR hre.person_id IS NULL ) AND ood.organization_id(+) = rsl.from_organization_id AND ood.LANGUAGE(+) = USERENV ('LANG') AND dct.conversion_type(+) = rt.currency_conversion_type AND NVL (pll.matching_basis(+), 'QUANTITY') != 'AMOUNT' AND (pll.drop_ship_flag <> 'Y' OR pll.drop_ship_flag IS NULL) 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.CREATION_DATE
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.QUANTITY
, RT.UNIT_OF_MEASURE
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, RT.SOURCE_DOCUMENT_CODE
, RT.DESTINATION_TYPE_CODE
, RT.PRIMARY_UNIT_OF_MEASURE
, 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
, PRL.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
, RT.DELIVER_TO_PERSON_ID
, RT.DELIVER_TO_LOCATION_ID
, RT.VENDOR_ID
, RT.VENDOR_SITE_ID
, RT.ORGANIZATION_ID
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, RT.LOCATION_ID
, RT.RECEIPT_EXCEPTION_FLAG
, RT.INSPECTION_STATUS_CODE
, RT.VENDOR_LOT_NUM
, 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.REASON_ID
, RT.DESTINATION_CONTEXT
, RT.LOCATOR_ATTRIBUTE
, RT.SOURCE_DOC_UNIT_OF_MEASURE
, RSL.GOVERNMENT_CONTEXT
, RSL.USSGL_TRANSACTION_CODE
, PRL.REQUISITION_HEADER_ID
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RSL.LINE_NUM
, RSH.PACKING_SLIP
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, RSL.CATEGORY_ID
, RSL.ITEM_REVISION
, RSH.RECEIPT_SOURCE_CODE
, MSI.ALLOWED_UNITS_LOOKUP_CODE
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CC
, 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
, 'PO'
, PH.SEGMENT1
, 'RMA'
, NULL
, RSH.SHIPMENT_NUM ) PO_NUMBER
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.LINE_NUM) PO_LINE_NUMBER
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPMENT_NUMBER
, PLL.SHIPMENT_NUM) PO_SHIPMENT_NUMBER
, 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
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POV.VENDOR_NAME
, 'RMA'
, NULL
, OOD.NAME ) VENDOR
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OETL.NAME
, NULL ) ORDER_TYPE
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, DECODE (OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL )
, PH.NOTE_TO_RECEIVER ) NOTE_TO_RECEIVER
, RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER
, HRL.LOCATION_CODE
, RSH.BILL_OF_LADING
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER
, RSH.WAYBILL_AIRBILL_NUM
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.VENDOR_PRODUCT_NUM) VENDOR_ITEM_NUMBER
, RRH.ROUTING_NAME
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PLL.QUANTITY
, 'RMA'
, OEL.ORDERED_QUANTITY
, RSL.QUANTITY_SHIPPED ) ORDERED_QTY
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PL.UNIT_MEAS_LOOKUP_CODE
, 'RMA'
, OEL.ORDER_QUANTITY_UOM
, RSL.UNIT_OF_MEASURE ) ORDERED_UOM
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SCHEDULE_ARRIVAL_DATE
, NVL (PLL.NEED_BY_DATE
, PLL.PROMISED_DATE) ) DUE_DATE_VENDOR
, RSH.EXPECTED_RECEIPT_DATE DUE_DATE_INTERNAL
, MTR.REASON_NAME REASON_CODE
, HRE.FULL_NAME
, RT.WIP_ENTITY_ID
, RT.WIP_LINE_ID
, RT.WIP_REPETITIVE_SCHEDULE_ID
, RT.WIP_OPERATION_SEQ_NUM
, RT.WIP_RESOURCE_SEQ_NUM
, RT.DEPARTMENT_CODE
, RT.BOM_RESOURCE_ID
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PLL.QTY_RCV_EXCEPTION_CODE ) QTY_RCV_EXCEPTION_CODE
, RT.MOVEMENT_ID
, RT.INSPECTION_QUALITY_CODE
, RT.SUBSTITUTE_UNORDERED_CODE
, DCT.USER_CONVERSION_TYPE
, RT.OE_ORDER_HEADER_ID
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEH.ORDER_NUMBER
, NULL) OE_ORDER_NUM
, RT.OE_ORDER_LINE_ID
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.LINE_NUMBER
, NULL) OE_ORDER_LINE_NUM
, RT.CUSTOMER_ID
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, SUBSTR (HZP.PARTY_NAME
, 1
, 255)
, NULL ) CUSTOMER
, RT.LPN_ID LPN_ID
, RT.TRANSFER_LPN_ID TRANSFER_LPN_ID
, RT.SECONDARY_QUANTITY
, RT.SECONDARY_UNIT_OF_MEASURE
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PLL.SECONDARY_QUANTITY
, 'RMA'
, OEL.ORDERED_QUANTITY2
, RSL.SECONDARY_QUANTITY_SHIPPED ) SECONDARY_ORDERED_QTY
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PLL.SECONDARY_UNIT_OF_MEASURE
, 'RMA'
, OEL.ORDERED_QUANTITY_UOM2
, RSL.SECONDARY_UNIT_OF_MEASURE ) SECONDARY_ORDERED_UOM
, RT.QC_GRADE QC_GRADE
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPPING_INSTRUCTIONS
, PLL.NOTE_TO_RECEIVER ) PLL_NOTE_TO_RECEIVER
, NVL (RSL.EMPLOYEE_ID
, RT.EMPLOYEE_ID)
, DECODE(MSI.TRACKING_QUANTITY_IND
, 'PS'
, MSI.SECONDARY_DEFAULT_IND
, NULL)
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PH.ORG_ID
, 'REQ'
, PRL.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
, MTL_SYSTEM_ITEMS MSI
, OE_ORDER_HEADERS_ALL OEH
, OE_ORDER_LINES_ALL OEL
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES HZP
, OE_TRANSACTION_TYPES_TL OETL
, OE_TRANSACTION_TYPES_ALL OEOT
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS PLL
, PO_RELEASES_ALL PR
, PO_VENDORS POV
, HR_LOCATIONS_ALL_TL HRL
, MTL_TRANSACTION_REASONS MTR
, RCV_ROUTING_HEADERS RRH
, PER_PEOPLE_F HRE
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, GL_DAILY_CONVERSION_TYPES DCT
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE ( RT.TRANSACTION_TYPE IN ('RECEIVE'
, 'TRANSFER'
, 'ACCEPT'
, 'REJECT'
, 'MATCH') OR ( RT.TRANSACTION_TYPE IN ('RETURN TO VENDOR'
, 'RETURN TO CUSTOMER')
AND NOT EXISTS ( SELECT 'RTV TO PROCESSED MATCHED UNORDERED RECEIPT'
FROM RCV_TRANSACTIONS RT2
, RCV_TRANSACTIONS RT3
WHERE RT2.TRANSACTION_ID = RT.PARENT_TRANSACTION_ID
AND RT2.TRANSACTION_TYPE = 'UNORDERED'
AND RT3.TRANSACTION_TYPE = 'MATCH'
AND RT3.PARENT_TRANSACTION_ID = RT2.TRANSACTION_ID)
AND NOT EXISTS ( SELECT 'RTV TO UNPROCESSED MATCHED UNORDERED RECEIPT'
FROM RCV_TRANSACTIONS RT2
, RCV_TRANSACTIONS_INTERFACE RTI
WHERE RT2.TRANSACTION_ID = RT.PARENT_TRANSACTION_ID
AND RT2.TRANSACTION_TYPE = 'UNORDERED'
AND RTI.TRANSACTION_TYPE = 'MATCH'
AND RTI.PARENT_TRANSACTION_ID = RT2.TRANSACTION_ID) ) OR ( RT.TRANSACTION_TYPE = 'DELIVER'
AND NOT ( RT.DESTINATION_TYPE_CODE = 'INVENTORY'
AND RT.SOURCE_DOCUMENT_CODE IN ('REQ'
, 'INVENTORY'
, 'RMA') ) ) OR ( RT.TRANSACTION_TYPE = 'UNORDERED'
AND NOT EXISTS ( SELECT 'PROCESSED MATCHED ROWS'
FROM RCV_TRANSACTIONS RT2
WHERE RT2.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT2.TRANSACTION_TYPE = 'MATCH')
AND NOT EXISTS ( SELECT 'UNPROCESSED MATCHED ROWS'
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
AND RTI.TRANSACTION_TYPE = 'MATCH') ) )
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 (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 ( MSI.INVENTORY_ITEM_ID(+) = RSL.ITEM_ID
AND NVL (MSI.ORGANIZATION_ID
, RT.ORGANIZATION_ID) = RT.ORGANIZATION_ID )
AND (OEH.HEADER_ID(+) = RT.OE_ORDER_HEADER_ID)
AND (OEL.LINE_ID(+) = RT.OE_ORDER_LINE_ID)
AND (OEOT.TRANSACTION_TYPE_ID(+) = OEH.ORDER_TYPE_ID)
AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV ('LANG')
AND OEOT.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 POV.VENDOR_ID(+) = RSH.VENDOR_ID
AND HZCA.CUST_ACCOUNT_ID(+) = RSH.CUSTOMER_ID
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
AND HRL.LOCATION_ID(+) = NVL (RT.DELIVER_TO_LOCATION_ID
, RT.LOCATION_ID)
AND HRL.LANGUAGE(+) = USERENV ('LANG')
AND RRH.ROUTING_HEADER_ID(+) = RT.ROUTING_HEADER_ID
AND MTR.REASON_ID(+) = RT.REASON_ID
AND HRE.PERSON_ID(+) = RT.DELIVER_TO_PERSON_ID
AND ( ( TRUNC (SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND HRE.PERSON_ID IS NOT NULL ) OR HRE.PERSON_ID IS NULL )
AND OOD.ORGANIZATION_ID(+) = RSL.FROM_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV ('LANG')
AND DCT.CONVERSION_TYPE(+) = RT.CURRENCY_CONVERSION_TYPE
AND NVL (PLL.MATCHING_BASIS(+)
, 'QUANTITY') != 'AMOUNT'
AND (PLL.DROP_SHIP_FLAG <> 'Y' OR PLL.DROP_SHIP_FLAG IS NULL)
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' ) ) )