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.CSP_RECEIVING_HEADERS_V

Source

View Text - Preformatted


SELECT crh.resource_type, crh.resource_id, mr.creation_date, TO_CHAR(mr.reservation_id) , mr.reservation_id, crld.source_type, jtb.task_number, mr.organization_id, mp1.organization_code ||' - ' ||mr.subinventory_code, mr.subinventory_code, crh.destination_organization_id, mp2.organization_code ||' - ' ||crh.destination_subinventory, crh.destination_subinventory, sysdate expected_delivery_date, flvv.meaning FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, mtl_reservations mr, mtl_parameters mp1, mtl_parameters mp2, jtf_tasks_b jtb, fnd_lookup_values_vl flvv WHERE crl.requirement_header_id = crh.requirement_header_id AND crld.requirement_line_id = crl.requirement_line_id AND mr.reservation_id = crld.source_id AND jtb.task_id(+) = crh.task_id AND mp1.organization_id = mr.organization_id AND mp2.organization_id = crh.destination_organization_id AND crld.source_type = 'RES' AND flvv.lookup_type = 'CSP_ORDER_LINE_STATUS_NO_OE' AND flvv.lookup_code = 'RESERVED' AND mp1.organization_code ||' - ' ||mr.subinventory_code <> mp2.organization_code ||' - ' ||crh.destination_subinventory UNION ALL SELECT DISTINCT crh.resource_type, crh.resource_id, mtrh.creation_date, mtrh.request_number, mtrh.header_id, crld.source_type, jtb.task_number, mtrl.organization_id source_organization_id, mp1.organization_code ||' - ' ||mtrl.from_subinventory_code source, mtrl.from_subinventory_code source_subinventory, mtrl.organization_id destination_organization_id, mp1.organization_code ||' - ' ||mtrl.to_subinventory_code destination, mtrl.to_subinventory_code, to_date(NULL), ml.meaning FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl, mtl_parameters mp1, jtf_tasks_b jtb, mfg_lookups ml WHERE crl.requirement_header_id = crh.requirement_header_id AND crld.requirement_line_id = crl.requirement_line_id AND mtrl.line_id = crld.source_id AND mtrh.header_id = mtrl.header_id AND jtb.task_id(+) = crh.task_id AND mp1.organization_id = mtrh.organization_id AND mtrl.quantity - NVL(mtrl.quantity_delivered,0) > 0 AND ml.lookup_type = 'MTL_TXN_REQUEST_STATUS' AND ml.lookup_code = mtrh.header_status AND crld.source_type = 'MO' AND mtrh.header_status <> 5 UNION ALL SELECT DISTINCT crh.resource_type, crh.resource_id, ooha.creation_date, TO_CHAR(ooha.order_number), ooha.header_id, crld.source_type, jtb.task_number, oola.ship_from_org_id source_organization_id, mp1.organization_code ||' - ' ||oola.subinventory source, oola.subinventory source_subinventory, crh.destination_organization_id destination_organization_id, mp2.organization_code ||' - ' ||crh.destination_subinventory destination, crh.destination_subinventory, to_date(NULL), ooha.flow_status_code FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, oe_order_headers_all ooha, oe_order_lines_all oola, mtl_parameters mp1, mtl_parameters mp2, jtf_tasks_b jtb WHERE crl.requirement_header_id = crh.requirement_header_id AND crld.requirement_line_id = crl.requirement_line_id AND oola.line_id = crld.source_id AND ooha.header_id = oola.header_id AND jtb.task_id(+) = crh.task_id AND mp1.organization_id = oola.ship_from_org_id AND mp2.organization_id = crh.destination_organization_id AND oola.flow_status_code <> 'CANCELLED' AND (EXISTS (SELECT requisition_line_id FROM RCV_SHIPMENT_LINES WHERE requisition_line_id = oola.SOURCE_DOCUMENT_LINE_ID AND ROUTING_HEADER_ID = 3 AND SHIPMENT_LINE_STATUS_CODE IN ('EXPECTED','PARTIALLY RECEIVED') ) OR NOT EXISTS (SELECT requisition_line_id FROM RCV_SHIPMENT_LINES WHERE requisition_line_id = oola.SOURCE_DOCUMENT_LINE_ID )) AND crld.source_type = 'IO' UNION ALL SELECT DISTINCT crh.resource_type, crh.resource_id, prha.creation_date, prha.segment1, prha.requisition_header_id, crld.source_type, jtb.task_number, NULL, prla.suggested_vendor_name source, NULL, crh.destination_organization_id destination_organization_id, mp1.organization_code ||' - ' ||crh.destination_subinventory destination, crh.destination_subinventory, to_date(NULL), prha.authorization_status FROM csp_requirement_headers crh, csp_requirement_lines crl, csp_req_line_details crld, po_requisition_headers_all prha, po_requisition_lines_all prla, mtl_parameters mp1, jtf_tasks_b jtb WHERE crl.requirement_header_id = crh.requirement_header_id AND crld.requirement_line_id = crl.requirement_line_id AND prla.requisition_line_id = crld.source_id AND prha.requisition_header_id = prla.requisition_header_id AND prla.source_type_code = 'VENDOR' AND jtb.task_id(+) = crh.task_id AND mp1.organization_id = crh.destination_organization_id AND prla.quantity - NVL(prla.quantity_delivered,0) > 0 AND crld.source_type = 'POREQ'

View Text - HTML Formatted

SELECT CRH.RESOURCE_TYPE
, CRH.RESOURCE_ID
, MR.CREATION_DATE
, TO_CHAR(MR.RESERVATION_ID)
, MR.RESERVATION_ID
, CRLD.SOURCE_TYPE
, JTB.TASK_NUMBER
, MR.ORGANIZATION_ID
, MP1.ORGANIZATION_CODE ||' - ' ||MR.SUBINVENTORY_CODE
, MR.SUBINVENTORY_CODE
, CRH.DESTINATION_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE ||' - ' ||CRH.DESTINATION_SUBINVENTORY
, CRH.DESTINATION_SUBINVENTORY
, SYSDATE EXPECTED_DELIVERY_DATE
, FLVV.MEANING
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, MTL_RESERVATIONS MR
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, JTF_TASKS_B JTB
, FND_LOOKUP_VALUES_VL FLVV
WHERE CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND MR.RESERVATION_ID = CRLD.SOURCE_ID
AND JTB.TASK_ID(+) = CRH.TASK_ID
AND MP1.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND MP2.ORGANIZATION_ID = CRH.DESTINATION_ORGANIZATION_ID
AND CRLD.SOURCE_TYPE = 'RES'
AND FLVV.LOOKUP_TYPE = 'CSP_ORDER_LINE_STATUS_NO_OE'
AND FLVV.LOOKUP_CODE = 'RESERVED'
AND MP1.ORGANIZATION_CODE ||' - ' ||MR.SUBINVENTORY_CODE <> MP2.ORGANIZATION_CODE ||' - ' ||CRH.DESTINATION_SUBINVENTORY UNION ALL SELECT DISTINCT CRH.RESOURCE_TYPE
, CRH.RESOURCE_ID
, MTRH.CREATION_DATE
, MTRH.REQUEST_NUMBER
, MTRH.HEADER_ID
, CRLD.SOURCE_TYPE
, JTB.TASK_NUMBER
, MTRL.ORGANIZATION_ID SOURCE_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE ||' - ' ||MTRL.FROM_SUBINVENTORY_CODE SOURCE
, MTRL.FROM_SUBINVENTORY_CODE SOURCE_SUBINVENTORY
, MTRL.ORGANIZATION_ID DESTINATION_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE ||' - ' ||MTRL.TO_SUBINVENTORY_CODE DESTINATION
, MTRL.TO_SUBINVENTORY_CODE
, TO_DATE(NULL)
, ML.MEANING
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, MTL_TXN_REQUEST_HEADERS MTRH
, MTL_TXN_REQUEST_LINES MTRL
, MTL_PARAMETERS MP1
, JTF_TASKS_B JTB
, MFG_LOOKUPS ML
WHERE CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND MTRL.LINE_ID = CRLD.SOURCE_ID
AND MTRH.HEADER_ID = MTRL.HEADER_ID
AND JTB.TASK_ID(+) = CRH.TASK_ID
AND MP1.ORGANIZATION_ID = MTRH.ORGANIZATION_ID
AND MTRL.QUANTITY - NVL(MTRL.QUANTITY_DELIVERED
, 0) > 0
AND ML.LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
AND ML.LOOKUP_CODE = MTRH.HEADER_STATUS
AND CRLD.SOURCE_TYPE = 'MO'
AND MTRH.HEADER_STATUS <> 5 UNION ALL SELECT DISTINCT CRH.RESOURCE_TYPE
, CRH.RESOURCE_ID
, OOHA.CREATION_DATE
, TO_CHAR(OOHA.ORDER_NUMBER)
, OOHA.HEADER_ID
, CRLD.SOURCE_TYPE
, JTB.TASK_NUMBER
, OOLA.SHIP_FROM_ORG_ID SOURCE_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE ||' - ' ||OOLA.SUBINVENTORY SOURCE
, OOLA.SUBINVENTORY SOURCE_SUBINVENTORY
, CRH.DESTINATION_ORGANIZATION_ID DESTINATION_ORGANIZATION_ID
, MP2.ORGANIZATION_CODE ||' - ' ||CRH.DESTINATION_SUBINVENTORY DESTINATION
, CRH.DESTINATION_SUBINVENTORY
, TO_DATE(NULL)
, OOHA.FLOW_STATUS_CODE
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, OE_ORDER_HEADERS_ALL OOHA
, OE_ORDER_LINES_ALL OOLA
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, JTF_TASKS_B JTB
WHERE CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND OOLA.LINE_ID = CRLD.SOURCE_ID
AND OOHA.HEADER_ID = OOLA.HEADER_ID
AND JTB.TASK_ID(+) = CRH.TASK_ID
AND MP1.ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND MP2.ORGANIZATION_ID = CRH.DESTINATION_ORGANIZATION_ID
AND OOLA.FLOW_STATUS_CODE <> 'CANCELLED'
AND (EXISTS (SELECT REQUISITION_LINE_ID
FROM RCV_SHIPMENT_LINES
WHERE REQUISITION_LINE_ID = OOLA.SOURCE_DOCUMENT_LINE_ID
AND ROUTING_HEADER_ID = 3
AND SHIPMENT_LINE_STATUS_CODE IN ('EXPECTED'
, 'PARTIALLY RECEIVED') ) OR NOT EXISTS (SELECT REQUISITION_LINE_ID
FROM RCV_SHIPMENT_LINES
WHERE REQUISITION_LINE_ID = OOLA.SOURCE_DOCUMENT_LINE_ID ))
AND CRLD.SOURCE_TYPE = 'IO' UNION ALL SELECT DISTINCT CRH.RESOURCE_TYPE
, CRH.RESOURCE_ID
, PRHA.CREATION_DATE
, PRHA.SEGMENT1
, PRHA.REQUISITION_HEADER_ID
, CRLD.SOURCE_TYPE
, JTB.TASK_NUMBER
, NULL
, PRLA.SUGGESTED_VENDOR_NAME SOURCE
, NULL
, CRH.DESTINATION_ORGANIZATION_ID DESTINATION_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE ||' - ' ||CRH.DESTINATION_SUBINVENTORY DESTINATION
, CRH.DESTINATION_SUBINVENTORY
, TO_DATE(NULL)
, PRHA.AUTHORIZATION_STATUS
FROM CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, PO_REQUISITION_HEADERS_ALL PRHA
, PO_REQUISITION_LINES_ALL PRLA
, MTL_PARAMETERS MP1
, JTF_TASKS_B JTB
WHERE CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND PRLA.REQUISITION_LINE_ID = CRLD.SOURCE_ID
AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND PRLA.SOURCE_TYPE_CODE = 'VENDOR'
AND JTB.TASK_ID(+) = CRH.TASK_ID
AND MP1.ORGANIZATION_ID = CRH.DESTINATION_ORGANIZATION_ID
AND PRLA.QUANTITY - NVL(PRLA.QUANTITY_DELIVERED
, 0) > 0
AND CRLD.SOURCE_TYPE = 'POREQ'