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_RECEIPT_CONFIRMATION_TXN_V

Source

View Text - Preformatted


SELECT mp1.organization_code, mp2.organization_code, rsh.receipt_num, rt.source_document_code, Decode (rt.source_document_code, 'PO', poh.segment1, 'RMA', oeh.order_number, 'REQ', prh.segment1, 'INVENTORY', rsh.shipment_num, NULL) document_number, por.release_num, Decode (rt.source_document_code, 'PO', pol.line_num, 'RMA', oel.line_number, 'REQ', prl.line_num, 'INVENTORY', rsl.line_num, NULL) document_line_number, Decode (rt.source_document_code, 'PO', pll.shipment_num, NULL) document_shipment_line_number, Decode (rt.source_document_code, 'PO', pod.distribution_num, NULL) document_dist_line_number, rsh.shipment_num, pov.vendor_name, pvs.vendor_site_code, hzp.party_name, hzca.account_number, hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.province, hl.county, hl.postal_code, hl.state, hl.country, rsl.line_num, msibk.concatenated_segments, rsl.item_id, rsl.item_description, rsl.item_revision, rt.transaction_type, rt.transaction_date, rt.creation_date, rt.transaction_id, rt.parent_transaction_id, hre1.full_name employee_name, hre.full_name deliver_to_person_name, hrl.location_code, rt.inspection_status_code, rsl.mmt_transaction_id, rt.subinventory subinventory, rt.from_subinventory transfer_subinventory, milk1.concatenated_segments locator, milk2.concatenated_segments transfer_locator, rt.quantity transaction_quantity, tuom.unit_of_measure_tl transaction_uom, rt.primary_quantity, puom.unit_of_measure_tl primary_uom, Decode(msibk.tracking_quantity_ind, 'PS', rt.secondary_quantity, NULL) secondary_quantity, Decode(msibk.tracking_quantity_ind, 'PS', suom.unit_of_measure_tl, NULL) secondary_uom, rt.receipt_confirmation_extracted, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rt.po_distribution_id, rt.oe_order_header_id, rt.oe_order_line_id, prh.requisition_header_id, rt.requisition_line_id, rt.shipment_header_id, rt.shipment_line_id, rt.source_transaction_num, wlpn1.license_plate_number lpn, wlpn2.license_plate_number transfer_lpn, wlpn3.license_plate_number parent_lpn, rsl.attribute_category, rsl.attribute1, rsl.attribute2, rsl.attribute3, rsl.attribute4, rsl.attribute5, rsl.attribute6, rsl.attribute7, rsl.attribute8, rsl.attribute9, rsl.attribute10, rsl.attribute11, rsl.attribute12, rsl.attribute13, rsl.attribute14, rsl.attribute15, 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.amount, rt.amount_billed, rsl.asn_line_flag, rt.child_inspection_flag, rt.comments, rt.consigned_flag, rt.country_of_origin_code, rt.currency_code, rt.currency_conversion_date, rt.currency_conversion_rate, rt.currency_conversion_type, rt.destination_context, rt.destination_type_code, rt.dropship_type_code, rsl.from_organization_id, rt.group_id, rt.inspection_quality_code, rt.interface_source_code, rt.interface_source_line_id, rt.interface_transaction_id, rt.inv_transaction_id, rt.invoice_status_code, rt.last_update_date, rt.last_update_login, rt.last_updated_by, rt.lcm_shipment_line_id, rt.locator_attribute, rt.lpn_group_id, rt.match_flag, rt.match_option, rt.material_stored_amount, rt.mobile_txn, rt.mrc_currency_conversion_date, rt.mrc_currency_conversion_rate, rt.mrc_currency_conversion_type, rt.mvt_stat_status, Decode (rt.source_document_code, 'PO', Nvl(pll.org_id, poh.org_id), 'RMA', oeh.org_id, 'REQ', prl.org_id, NULL), rt.organization_id, rt.pa_addition_flag, rt.po_unit_price, Nvl(ppa.segment1, psn.project_number), rt.qc_grade, rt.quantity_billed, rt.receipt_exception_flag, rt.req_distribution_id, rt.requested_amount, rt.rma_reference, rt.routing_header_id, rt.routing_step_id, rt.source_doc_quantity, rt.source_doc_unit_of_measure, rt.source_document_code, rt.substitute_unordered_code, pt.task_number, rt.unit_landed_cost, rt.user_entered_flag, rt.vendor_lot_num, rt.xml_document_id FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions rt, mtl_parameters mp1, mtl_parameters mp2, po_headers_all poh, po_releases_all por, po_lines_all pol, po_line_locations_all pll, po_distributions_all pod, po_requisition_headers_all prh, po_requisition_lines_all prl, oe_order_headers_all oeh, oe_order_lines_all oel, hr_employees hre, hr_employees hre1, hr_locations_all hrl, mtl_system_items_b_kfv msibk, mtl_item_locations_kfv milk1, mtl_item_locations_kfv milk2, wms_license_plate_numbers wlpn1, wms_license_plate_numbers wlpn2, wms_license_plate_numbers wlpn3, mtl_units_of_measure tuom, mtl_units_of_measure puom, mtl_units_of_measure suom, mtl_material_transactions mmt, hz_parties hzp, hz_cust_accounts hzca, po_vendors pov, po_vendor_sites_all pvs, pa_projects_all ppa, pa_tasks pt, pjm_seiban_numbers psn, hz_locations hl, hz_party_sites hzps, hz_cust_acct_sites_all hzcas, hz_cust_site_uses_all hzcsu WHERE rsh.shipment_header_id = rsl.shipment_header_id AND rsh.shipment_header_id = rt.shipment_header_id AND rsl.shipment_line_id = rt.shipment_line_id AND ( rt.transaction_type IN ( 'DELIVER' ) OR ( rt.transaction_type IN ( 'CORRECT', 'RETURN TO RECEIVING' ) AND EXISTS (SELECT '1' FROM rcv_transactions rt2 WHERE rt.parent_transaction_id = rt2.transaction_id AND rt2.transaction_type = 'DELIVER') ) OR ( rt.transaction_type IN ( 'CORRECT' ) AND EXISTS (SELECT '1' FROM rcv_transactions rt3 WHERE rt.parent_transaction_id = rt3.transaction_id AND rt3.transaction_type = 'RETURN TO RECEIVING') ) ) AND rt.po_header_id = poh.po_header_id (+) AND rt.po_release_id = por.po_release_id (+) AND rt.po_line_id = pol.po_line_id (+) AND rt.po_line_location_id = pll.line_location_id (+) AND rt.po_distribution_id = pod.po_distribution_id (+) AND rt.oe_order_header_id = oeh.header_id (+) AND rt.oe_order_line_id = oel.line_id (+) AND rt.requisition_line_id = prl.requisition_line_id (+) AND prl.requisition_header_id = prh.requisition_header_id (+) AND rt.deliver_to_person_id = hre.employee_id (+) AND rt.deliver_to_location_id = hrl.location_id (+) AND rt.employee_id = hre1.employee_id (+) AND rt.locator_id = milk1.inventory_location_id (+) AND rt.organization_id = milk1.organization_id (+) AND rt.from_locator_id = milk2.inventory_location_id (+) AND rt.organization_id = milk2.organization_id (+) AND rt.lpn_id = wlpn1.lpn_id (+) AND rt.transfer_lpn_id = wlpn2.lpn_id (+) AND wlpn2.parent_lpn_id = wlpn3.lpn_id (+) AND rsl.item_id = msibk.inventory_item_id AND rt.organization_id = msibk.organization_id AND msibk.primary_uom_code = puom.uom_code (+) AND msibk.secondary_uom_code = suom.uom_code (+) AND rt.transaction_id = mmt.rcv_transaction_id (+) AND mmt.transaction_uom = tuom.uom_code (+) AND mmt.project_id = ppa.project_id (+) AND mmt.task_id = pt.task_id (+) AND mmt.project_id = psn.project_id (+) AND rt.customer_id = hzca.cust_account_id (+) AND hzca.party_id = hzp.party_id (+) AND rt.customer_site_id = hzcsu.site_use_id (+) AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id (+) AND hzcas.party_site_id = hzps.party_site_id (+) AND hzps.location_id = hl.location_id (+) AND rt.vendor_site_id = pvs.vendor_site_id (+) AND rt.vendor_id = pov.vendor_id (+) AND rt.organization_id = mp1.organization_id AND rsh.organization_id = mp2.organization_id (+)

View Text - HTML Formatted

SELECT MP1.ORGANIZATION_CODE
, MP2.ORGANIZATION_CODE
, RSH.RECEIPT_NUM
, RT.SOURCE_DOCUMENT_CODE
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, 'RMA'
, OEH.ORDER_NUMBER
, 'REQ'
, PRH.SEGMENT1
, 'INVENTORY'
, RSH.SHIPMENT_NUM
, NULL) DOCUMENT_NUMBER
, POR.RELEASE_NUM
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POL.LINE_NUM
, 'RMA'
, OEL.LINE_NUMBER
, 'REQ'
, PRL.LINE_NUM
, 'INVENTORY'
, RSL.LINE_NUM
, NULL) DOCUMENT_LINE_NUMBER
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PLL.SHIPMENT_NUM
, NULL) DOCUMENT_SHIPMENT_LINE_NUMBER
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POD.DISTRIBUTION_NUM
, NULL) DOCUMENT_DIST_LINE_NUMBER
, RSH.SHIPMENT_NUM
, POV.VENDOR_NAME
, PVS.VENDOR_SITE_CODE
, HZP.PARTY_NAME
, HZCA.ACCOUNT_NUMBER
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.PROVINCE
, HL.COUNTY
, HL.POSTAL_CODE
, HL.STATE
, HL.COUNTRY
, RSL.LINE_NUM
, MSIBK.CONCATENATED_SEGMENTS
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, RSL.ITEM_REVISION
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.CREATION_DATE
, RT.TRANSACTION_ID
, RT.PARENT_TRANSACTION_ID
, HRE1.FULL_NAME EMPLOYEE_NAME
, HRE.FULL_NAME DELIVER_TO_PERSON_NAME
, HRL.LOCATION_CODE
, RT.INSPECTION_STATUS_CODE
, RSL.MMT_TRANSACTION_ID
, RT.SUBINVENTORY SUBINVENTORY
, RT.FROM_SUBINVENTORY TRANSFER_SUBINVENTORY
, MILK1.CONCATENATED_SEGMENTS LOCATOR
, MILK2.CONCATENATED_SEGMENTS TRANSFER_LOCATOR
, RT.QUANTITY TRANSACTION_QUANTITY
, TUOM.UNIT_OF_MEASURE_TL TRANSACTION_UOM
, RT.PRIMARY_QUANTITY
, PUOM.UNIT_OF_MEASURE_TL PRIMARY_UOM
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, RT.SECONDARY_QUANTITY
, NULL) SECONDARY_QUANTITY
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, SUOM.UNIT_OF_MEASURE_TL
, NULL) SECONDARY_UOM
, RT.RECEIPT_CONFIRMATION_EXTRACTED
, RT.PO_HEADER_ID
, RT.PO_LINE_ID
, RT.PO_LINE_LOCATION_ID
, RT.PO_DISTRIBUTION_ID
, RT.OE_ORDER_HEADER_ID
, RT.OE_ORDER_LINE_ID
, PRH.REQUISITION_HEADER_ID
, RT.REQUISITION_LINE_ID
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, RT.SOURCE_TRANSACTION_NUM
, WLPN1.LICENSE_PLATE_NUMBER LPN
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LPN
, WLPN3.LICENSE_PLATE_NUMBER PARENT_LPN
, RSL.ATTRIBUTE_CATEGORY
, RSL.ATTRIBUTE1
, RSL.ATTRIBUTE2
, RSL.ATTRIBUTE3
, RSL.ATTRIBUTE4
, RSL.ATTRIBUTE5
, RSL.ATTRIBUTE6
, RSL.ATTRIBUTE7
, RSL.ATTRIBUTE8
, RSL.ATTRIBUTE9
, RSL.ATTRIBUTE10
, RSL.ATTRIBUTE11
, RSL.ATTRIBUTE12
, RSL.ATTRIBUTE13
, RSL.ATTRIBUTE14
, RSL.ATTRIBUTE15
, 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.AMOUNT
, RT.AMOUNT_BILLED
, RSL.ASN_LINE_FLAG
, RT.CHILD_INSPECTION_FLAG
, RT.COMMENTS
, RT.CONSIGNED_FLAG
, RT.COUNTRY_OF_ORIGIN_CODE
, RT.CURRENCY_CODE
, RT.CURRENCY_CONVERSION_DATE
, RT.CURRENCY_CONVERSION_RATE
, RT.CURRENCY_CONVERSION_TYPE
, RT.DESTINATION_CONTEXT
, RT.DESTINATION_TYPE_CODE
, RT.DROPSHIP_TYPE_CODE
, RSL.FROM_ORGANIZATION_ID
, RT.GROUP_ID
, RT.INSPECTION_QUALITY_CODE
, RT.INTERFACE_SOURCE_CODE
, RT.INTERFACE_SOURCE_LINE_ID
, RT.INTERFACE_TRANSACTION_ID
, RT.INV_TRANSACTION_ID
, RT.INVOICE_STATUS_CODE
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATE_LOGIN
, RT.LAST_UPDATED_BY
, RT.LCM_SHIPMENT_LINE_ID
, RT.LOCATOR_ATTRIBUTE
, RT.LPN_GROUP_ID
, RT.MATCH_FLAG
, RT.MATCH_OPTION
, RT.MATERIAL_STORED_AMOUNT
, RT.MOBILE_TXN
, RT.MRC_CURRENCY_CONVERSION_DATE
, RT.MRC_CURRENCY_CONVERSION_RATE
, RT.MRC_CURRENCY_CONVERSION_TYPE
, RT.MVT_STAT_STATUS
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, NVL(PLL.ORG_ID
, POH.ORG_ID)
, 'RMA'
, OEH.ORG_ID
, 'REQ'
, PRL.ORG_ID
, NULL)
, RT.ORGANIZATION_ID
, RT.PA_ADDITION_FLAG
, RT.PO_UNIT_PRICE
, NVL(PPA.SEGMENT1
, PSN.PROJECT_NUMBER)
, RT.QC_GRADE
, RT.QUANTITY_BILLED
, RT.RECEIPT_EXCEPTION_FLAG
, RT.REQ_DISTRIBUTION_ID
, RT.REQUESTED_AMOUNT
, RT.RMA_REFERENCE
, RT.ROUTING_HEADER_ID
, RT.ROUTING_STEP_ID
, RT.SOURCE_DOC_QUANTITY
, RT.SOURCE_DOC_UNIT_OF_MEASURE
, RT.SOURCE_DOCUMENT_CODE
, RT.SUBSTITUTE_UNORDERED_CODE
, PT.TASK_NUMBER
, RT.UNIT_LANDED_COST
, RT.USER_ENTERED_FLAG
, RT.VENDOR_LOT_NUM
, RT.XML_DOCUMENT_ID
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_DISTRIBUTIONS_ALL POD
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, OE_ORDER_HEADERS_ALL OEH
, OE_ORDER_LINES_ALL OEL
, HR_EMPLOYEES HRE
, HR_EMPLOYEES HRE1
, HR_LOCATIONS_ALL HRL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_ITEM_LOCATIONS_KFV MILK1
, MTL_ITEM_LOCATIONS_KFV MILK2
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
, MTL_UNITS_OF_MEASURE TUOM
, MTL_UNITS_OF_MEASURE PUOM
, MTL_UNITS_OF_MEASURE SUOM
, MTL_MATERIAL_TRANSACTIONS MMT
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, PA_PROJECTS_ALL PPA
, PA_TASKS PT
, PJM_SEIBAN_NUMBERS PSN
, HZ_LOCATIONS HL
, HZ_PARTY_SITES HZPS
, HZ_CUST_ACCT_SITES_ALL HZCAS
, HZ_CUST_SITE_USES_ALL HZCSU
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND ( RT.TRANSACTION_TYPE IN ( 'DELIVER' ) OR ( RT.TRANSACTION_TYPE IN ( 'CORRECT'
, 'RETURN TO RECEIVING' )
AND EXISTS (SELECT '1'
FROM RCV_TRANSACTIONS RT2
WHERE RT.PARENT_TRANSACTION_ID = RT2.TRANSACTION_ID
AND RT2.TRANSACTION_TYPE = 'DELIVER') ) OR ( RT.TRANSACTION_TYPE IN ( 'CORRECT' )
AND EXISTS (SELECT '1'
FROM RCV_TRANSACTIONS RT3
WHERE RT.PARENT_TRANSACTION_ID = RT3.TRANSACTION_ID
AND RT3.TRANSACTION_TYPE = 'RETURN TO RECEIVING') ) )
AND RT.PO_HEADER_ID = POH.PO_HEADER_ID (+)
AND RT.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND RT.PO_LINE_ID = POL.PO_LINE_ID (+)
AND RT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID (+)
AND RT.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID (+)
AND RT.OE_ORDER_HEADER_ID = OEH.HEADER_ID (+)
AND RT.OE_ORDER_LINE_ID = OEL.LINE_ID (+)
AND RT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+)
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID (+)
AND RT.DELIVER_TO_PERSON_ID = HRE.EMPLOYEE_ID (+)
AND RT.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID (+)
AND RT.EMPLOYEE_ID = HRE1.EMPLOYEE_ID (+)
AND RT.LOCATOR_ID = MILK1.INVENTORY_LOCATION_ID (+)
AND RT.ORGANIZATION_ID = MILK1.ORGANIZATION_ID (+)
AND RT.FROM_LOCATOR_ID = MILK2.INVENTORY_LOCATION_ID (+)
AND RT.ORGANIZATION_ID = MILK2.ORGANIZATION_ID (+)
AND RT.LPN_ID = WLPN1.LPN_ID (+)
AND RT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+)
AND WLPN2.PARENT_LPN_ID = WLPN3.LPN_ID (+)
AND RSL.ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND RT.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID
AND MSIBK.PRIMARY_UOM_CODE = PUOM.UOM_CODE (+)
AND MSIBK.SECONDARY_UOM_CODE = SUOM.UOM_CODE (+)
AND RT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID (+)
AND MMT.TRANSACTION_UOM = TUOM.UOM_CODE (+)
AND MMT.PROJECT_ID = PPA.PROJECT_ID (+)
AND MMT.TASK_ID = PT.TASK_ID (+)
AND MMT.PROJECT_ID = PSN.PROJECT_ID (+)
AND RT.CUSTOMER_ID = HZCA.CUST_ACCOUNT_ID (+)
AND HZCA.PARTY_ID = HZP.PARTY_ID (+)
AND RT.CUSTOMER_SITE_ID = HZCSU.SITE_USE_ID (+)
AND HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID (+)
AND HZCAS.PARTY_SITE_ID = HZPS.PARTY_SITE_ID (+)
AND HZPS.LOCATION_ID = HL.LOCATION_ID (+)
AND RT.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND RT.VENDOR_ID = POV.VENDOR_ID (+)
AND RT.ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND RSH.ORGANIZATION_ID = MP2.ORGANIZATION_ID (+)