VIEW: APPS.CSP_RECEIVING_LINES_V

Source

View Text - Preformatted

SELECT crld.source_type, mr.reservation_id, mr.reservation_id, mr.organization_id, mr.subinventory_code, mr.inventory_item_id, msibk.concatenated_segments, msibk.description, msibk.serial_number_control_code, msibk.revision_qty_control_code, mr.serial_number, mr.revision, mr.reservation_uom_code, mr.reservation_quantity, mr.reservation_quantity, DECODE(msibk.serial_number_control_code, 1, mr.reservation_quantity, 1) AS shipped_quantity, crld.req_line_detail_id , NULL, NULL, NULL, NULL, NULL FROM mtl_reservations mr, mtl_system_items_b_kfv msibk, csp_req_line_details crld, TABLE(csp_transactions_pub.gen_numbers(DECODE(msibk.serial_number_control_code, 1, 1, mr.reservation_quantity))) WHERE msibk.organization_id = mr.organization_id AND msibk.inventory_item_id = mr.inventory_item_id AND crld.source_type = 'RES' AND crld.source_id = mr.reservation_id UNION ALL SELECT crld.source_type, mtrl.header_id, mtrl.line_id, mtrl.organization_id, mtrl.from_subinventory_code, mtrl.inventory_item_id, msibk.concatenated_segments, msibk.description, msibk.serial_number_control_code, msibk.revision_qty_control_code, mtrl.serial_number_start, mtrl.revision, mtrl.uom_code, mtrl.quantity, mtrl.quantity - mtrl.quantity_delivered, mtrl.quantity - mtrl.quantity_delivered, crld.req_line_detail_id, crpv.packlist_line_id, crpv.transaction_temp_id, NULL, NULL, NULL FROM mtl_txn_request_lines mtrl, mtl_system_items_b_kfv msibk, csp_req_line_details crld, csp_recv_parts_v crpv WHERE msibk.organization_id = mtrl.organization_id AND msibk.inventory_item_id = mtrl.inventory_item_id AND crld.source_type = 'MO' AND crld.source_id = mtrl.line_id AND crpv.line_id (+) = mtrl.line_id AND mtrl.line_status NOT IN (5,6) UNION ALL SELECT crld.source_type, oola.header_id, oola.line_id, oola.ship_from_org_id, oola.subinventory, oola.inventory_item_id, msibk.concatenated_segments, msibk.description, msibk.serial_number_control_code, msibk.revision_qty_control_code, rss.serial_num, NULL, oola.order_quantity_uom, (SELECT SUM(ordered_quantity) FROM oe_order_lines_all WHERE header_id = oola.header_id AND (line_id = oola.line_id OR split_from_line_id = oola.line_id) ) ordered_quantity, ( (SELECT SUM(ordered_quantity) FROM oe_order_lines_all WHERE header_id = oola.header_id AND (line_id = oola.line_id OR split_from_line_id = oola.line_id) ) - (SELECT SUM(NVL(quantity_received, 0)) FROM RCV_SHIPMENT_LINES WHERE requisition_line_id = oola.SOURCE_DOCUMENT_LINE_ID )) AS balance_due, DECODE(msibk.serial_number_control_code, 1, rsl.quantity_shipped, 1) shipped_quantity, crld.req_line_detail_id, NULL, NULL, rsh.shipment_num AS shipment_number, rsl.line_num AS shipment_line_num, rsl.shipment_line_id AS shipment_line_id FROM oe_order_lines_all oola, RCV_SHIPMENT_LINES rsl, rcv_serials_supply rss, mtl_system_items_b_kfv msibk, csp_req_line_details crld, rcv_shipment_headers rsh WHERE msibk.organization_id = oola.ship_from_org_id AND msibk.inventory_item_id = oola.inventory_item_id AND crld.source_type = 'IO' AND crld.source_id = oola.line_id AND oola.source_document_line_id = rsl.requisition_line_id (+) AND rsl.shipment_line_id = rss.shipment_line_id (+) AND rsl.shipment_header_id = rsh.shipment_header_id(+) AND rsl.SHIPMENT_LINE_STATUS_CODE(+) <> 'FULLY RECEIVED' UNION ALL SELECT crld.source_type, prla.requisition_header_id, prla.requisition_line_id, prla.source_organization_id, prla.source_subinventory, prla.item_id, msibk.concatenated_segments, msibk.description, msibk.serial_number_control_code, msibk.revision_qty_control_code, NULL, prla.item_revision, prla.unit_meas_lookup_code, prla.quantity, prla.quantity - prla.quantity_delivered, prla.quantity - prla.quantity_delivered, crld.req_line_detail_id, NULL, NULL, NULL, NULL, NULL FROM po_requisition_lines_all prla, mtl_system_items_b_kfv msibk, csp_req_line_details crld WHERE msibk.organization_id = fnd_profile.value('CS_INV_VALIDATION_ORG') AND msibk.inventory_item_id = prla.item_id AND crld.source_type = 'POREQ' AND crld.source_id = prla.requisition_line_id

View Text - HTML Formatted

SELECT CRLD.SOURCE_TYPE
, MR.RESERVATION_ID
, MR.RESERVATION_ID
, MR.ORGANIZATION_ID
, MR.SUBINVENTORY_CODE
, MR.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, MSIBK.SERIAL_NUMBER_CONTROL_CODE
, MSIBK.REVISION_QTY_CONTROL_CODE
, MR.SERIAL_NUMBER
, MR.REVISION
, MR.RESERVATION_UOM_CODE
, MR.RESERVATION_QUANTITY
, MR.RESERVATION_QUANTITY
, DECODE(MSIBK.SERIAL_NUMBER_CONTROL_CODE
, 1
, MR.RESERVATION_QUANTITY
, 1) AS SHIPPED_QUANTITY
, CRLD.REQ_LINE_DETAIL_ID
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_RESERVATIONS MR
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQ_LINE_DETAILS CRLD
, TABLE(CSP_TRANSACTIONS_PUB.GEN_NUMBERS(DECODE(MSIBK.SERIAL_NUMBER_CONTROL_CODE
, 1
, 1
, MR.RESERVATION_QUANTITY)))
WHERE MSIBK.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = MR.INVENTORY_ITEM_ID
AND CRLD.SOURCE_TYPE = 'RES'
AND CRLD.SOURCE_ID = MR.RESERVATION_ID UNION ALL SELECT CRLD.SOURCE_TYPE
, MTRL.HEADER_ID
, MTRL.LINE_ID
, MTRL.ORGANIZATION_ID
, MTRL.FROM_SUBINVENTORY_CODE
, MTRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, MSIBK.SERIAL_NUMBER_CONTROL_CODE
, MSIBK.REVISION_QTY_CONTROL_CODE
, MTRL.SERIAL_NUMBER_START
, MTRL.REVISION
, MTRL.UOM_CODE
, MTRL.QUANTITY
, MTRL.QUANTITY - MTRL.QUANTITY_DELIVERED
, MTRL.QUANTITY - MTRL.QUANTITY_DELIVERED
, CRLD.REQ_LINE_DETAIL_ID
, CRPV.PACKLIST_LINE_ID
, CRPV.TRANSACTION_TEMP_ID
, NULL
, NULL
, NULL
FROM MTL_TXN_REQUEST_LINES MTRL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQ_LINE_DETAILS CRLD
, CSP_RECV_PARTS_V CRPV
WHERE MSIBK.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID
AND CRLD.SOURCE_TYPE = 'MO'
AND CRLD.SOURCE_ID = MTRL.LINE_ID
AND CRPV.LINE_ID (+) = MTRL.LINE_ID
AND MTRL.LINE_STATUS NOT IN (5
, 6) UNION ALL SELECT CRLD.SOURCE_TYPE
, OOLA.HEADER_ID
, OOLA.LINE_ID
, OOLA.SHIP_FROM_ORG_ID
, OOLA.SUBINVENTORY
, OOLA.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, MSIBK.SERIAL_NUMBER_CONTROL_CODE
, MSIBK.REVISION_QTY_CONTROL_CODE
, RSS.SERIAL_NUM
, NULL
, OOLA.ORDER_QUANTITY_UOM
, (SELECT SUM(ORDERED_QUANTITY)
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = OOLA.HEADER_ID
AND (LINE_ID = OOLA.LINE_ID OR SPLIT_FROM_LINE_ID = OOLA.LINE_ID) ) ORDERED_QUANTITY
, ( (SELECT SUM(ORDERED_QUANTITY)
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = OOLA.HEADER_ID
AND (LINE_ID = OOLA.LINE_ID OR SPLIT_FROM_LINE_ID = OOLA.LINE_ID) ) - (SELECT SUM(NVL(QUANTITY_RECEIVED
, 0))
FROM RCV_SHIPMENT_LINES
WHERE REQUISITION_LINE_ID = OOLA.SOURCE_DOCUMENT_LINE_ID )) AS BALANCE_DUE
, DECODE(MSIBK.SERIAL_NUMBER_CONTROL_CODE
, 1
, RSL.QUANTITY_SHIPPED
, 1) SHIPPED_QUANTITY
, CRLD.REQ_LINE_DETAIL_ID
, NULL
, NULL
, RSH.SHIPMENT_NUM AS SHIPMENT_NUMBER
, RSL.LINE_NUM AS SHIPMENT_LINE_NUM
, RSL.SHIPMENT_LINE_ID AS SHIPMENT_LINE_ID
FROM OE_ORDER_LINES_ALL OOLA
, RCV_SHIPMENT_LINES RSL
, RCV_SERIALS_SUPPLY RSS
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQ_LINE_DETAILS CRLD
, RCV_SHIPMENT_HEADERS RSH
WHERE MSIBK.ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND MSIBK.INVENTORY_ITEM_ID = OOLA.INVENTORY_ITEM_ID
AND CRLD.SOURCE_TYPE = 'IO'
AND CRLD.SOURCE_ID = OOLA.LINE_ID
AND OOLA.SOURCE_DOCUMENT_LINE_ID = RSL.REQUISITION_LINE_ID (+)
AND RSL.SHIPMENT_LINE_ID = RSS.SHIPMENT_LINE_ID (+)
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
AND RSL.SHIPMENT_LINE_STATUS_CODE(+) <> 'FULLY RECEIVED' UNION ALL SELECT CRLD.SOURCE_TYPE
, PRLA.REQUISITION_HEADER_ID
, PRLA.REQUISITION_LINE_ID
, PRLA.SOURCE_ORGANIZATION_ID
, PRLA.SOURCE_SUBINVENTORY
, PRLA.ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, MSIBK.SERIAL_NUMBER_CONTROL_CODE
, MSIBK.REVISION_QTY_CONTROL_CODE
, NULL
, PRLA.ITEM_REVISION
, PRLA.UNIT_MEAS_LOOKUP_CODE
, PRLA.QUANTITY
, PRLA.QUANTITY - PRLA.QUANTITY_DELIVERED
, PRLA.QUANTITY - PRLA.QUANTITY_DELIVERED
, CRLD.REQ_LINE_DETAIL_ID
, NULL
, NULL
, NULL
, NULL
, NULL
FROM PO_REQUISITION_LINES_ALL PRLA
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQ_LINE_DETAILS CRLD
WHERE MSIBK.ORGANIZATION_ID = FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')
AND MSIBK.INVENTORY_ITEM_ID = PRLA.ITEM_ID
AND CRLD.SOURCE_TYPE = 'POREQ'
AND CRLD.SOURCE_ID = PRLA.REQUISITION_LINE_ID