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_RECEIVE_LINES_V

Source

View Text - Preformatted


SELECT flv.MEANING DOC_TYPE , flv.LOOKUP_CODE DOC_TYPE_CODE, 'INTERNAL' SOURCE_TYPE_CODE, DECODE(RSL.SOURCE_DOCUMENT_CODE,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER') RECEIPT_SOURCE_CODE, RSL.SOURCE_DOCUMENT_CODE ORDER_TYPE_CODE, '' ORDER_TYPE, TO_NUMBER(NULL) PO_HEADER_ID, NULL PO_NUMBER, TO_NUMBER(NULL) PO_LINE_ID, TO_NUMBER(NULL) PO_LINE_NUMBER, TO_NUMBER(NULL) PO_LINE_LOCATION_ID, RSL.LINE_NUM PO_SHIPMENT_NUMBER , TO_NUMBER(NULL) PO_RELESE_ID, TO_NUMBER(NULL) PO_RELEASE_NUMBER, PRH.REQUISITION_HEADER_ID REQ_HEADER_ID, PRH.SEGMENT1 REQ_NUMBER, PRL.REQUISITION_LINE_ID REQ_LINE_ID, PRL.LINE_NUM REQ_LINE, RSL.REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_ID, RSL.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID , RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER, RSL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID, RSL.LINE_NUM RCV_LINE_NUMBER, RSL.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID, PRL.DESTINATION_ORGANIZATION_ID TO_ORGANIZATION_ID, TO_NUMBER(NULL) VENDOR_ID, TO_NUMBER(NULL) VENDOR_SITE_ID, 'N' OUTSIDE_OPERATION_FLAG, RSL.ITEM_ID ITEM_ID, NULL uom_code, RSL.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM, MUOM.UOM_CLASS PRIMARY_UOM_CLASS, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE, NVL(MSI.LOCATION_CONTROL_CODE,1) ITEM_LOCATOR_CONTROL, DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y','N') RESTRICT_LOCATORS_CODE, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_CODE, NVL(MSI.SHELF_LIFE_CODE,1) SHELF_LIFE_CODE, NVL(MSI.SHELF_LIFE_DAYS,0) SHELF_LIFE_DAYS, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE, DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO, DECODE(MSI1.REVISION_QTY_CONTROL_CODE, 1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_FROM, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER, RSL.ITEM_REVISION ITEM_REVISION, RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION, RSL.CATEGORY_ID ITEM_CATEGORY_ID, '' HAZARD_CLASS, '' UN_NUMBER, RSL.VENDOR_ITEM_NUM VENDOR_ITEM_NUMBER, RSH.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID, '' SHIP_TO_LOCATION, RSH.PACKING_SLIP PACKING_SLIP, RSL.ROUTING_HEADER_ID ROUTING_ID, '' ROUTING_NAME, PRL.NEED_BY_DATE NEED_BY_DATE, RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE, RSL.QUANTITY_SHIPPED ORDERED_QTY, RSL.UNIT_OF_MEASURE ORDERED_UOM, RSH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, RSH.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT, NULL INSPECTION_REQUIRED_FLAG, NULL RECEIPT_REQUIRED_FLAG, NULL ENFORCE_SHIP_TO_LOCATION_CODE, TO_NUMBER(NULL) UNIT_PRICE, NULL CURRENCY_CODE, NULL CURRENCY_CONVERSION_TYPE, TO_DATE(NULL) CURRENCY_CONVERSION_DATE, TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE, NULL note_to_receiver, RSL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE, RSL.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID, RSL.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID, PRL.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY, RSL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, RSL.ATTRIBUTE1 ATTRIBUTE1, RSL.ATTRIBUTE2 ATTRIBUTE2, RSL.ATTRIBUTE3 ATTRIBUTE3, RSL.ATTRIBUTE4 ATTRIBUTE4, RSL.ATTRIBUTE5 ATTRIBUTE5, RSL.ATTRIBUTE6 ATTRIBUTE6, RSL.ATTRIBUTE7 ATTRIBUTE7, RSL.ATTRIBUTE8 ATTRIBUTE8, RSL.ATTRIBUTE9 ATTRIBUTE9, RSL.ATTRIBUTE10 ATTRIBUTE10, RSL.ATTRIBUTE11 ATTRIBUTE11, RSL.ATTRIBUTE12 ATTRIBUTE12, RSL.ATTRIBUTE13 ATTRIBUTE13, RSL.ATTRIBUTE14 ATTRIBUTE14, RSL.ATTRIBUTE15 ATTRIBUTE15, 'OPEN' CLOSED_CODE, NULL ASN_TYPE, RSH.BILL_OF_LADING BILL_OF_LADING, RSH.SHIPPED_DATE SHIPPED_DATE, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE, RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM, RSH.FREIGHT_BILL_NUMBER FREIGHT_BILL_NUM, RSL.VENDOR_LOT_NUM VENDOR_LOT_NUM, RSL.CONTAINER_NUM CONTAINER_NUM, RSL.TRUCK_NUM TRUCK_NUM, RSL.BAR_CODE_LABEL BAR_CODE_LABEL, NULL RATE_TYPE_DISPLAY, 'P' MATCH_OPTION, NULL COUNTRY_OF_ORIGIN_CODE , OH.HEADER_ID OE_ORDER_HEADER_ID, OH.ORDER_NUMBER OE_ORDER_NUM ,TO_NUMBER(NULL) OE_ORDER_LINE_ID, TO_NUMBER(NULL) OE_ORDER_LINE_NUM, TO_NUMBER(NULL) CUSTOMER_ID, TO_NUMBER(NULL) CUSTOMER_SITE_ID, NULL CUSTOMER_ITEM_NUM, NULL pll_note_to_receiver, TO_NUMBER(NULL) PO_DISTRIBUTION_ID, nvl(rsl.quantity_shipped,0) - nvl(rsl.quantity_received,0) TRANSACTION_QTY, TO_NUMBER(NULL) WIP_ENTITY_ID, TO_NUMBER(NULL) WIP_OPERATION_SEQ_NUM, TO_NUMBER(NULL) WIP_RESOURCE_SEQ_NUM, TO_NUMBER(NULL) WIP_REPETITIVE_SCHEDULE_ID, TO_NUMBER(NULL) WIP_LINE_ID, TO_NUMBER(NULL) BOM_RESOURCE_ID, '' DESINATION_TYPE, '' LOCATION, TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE_POD, TO_DATE(NULL) CURRENCY_CONVERSION_DATE_POD, TO_NUMBER(NULL) PROJECT_ID, TO_NUMBER(NULL) TASK_ID, TO_NUMBER(NULL) SET_OF_BOOKS_ID_SOB, NULL CURRENCY_CODE_SOB, RSL.COST_GROUP_ID COST_GROUP_ID, nvl(rss1.serial_num,RSS.SERIAL_NUM) SERIAL_NUM, RLS.LOT_NUM,RLS.QUANTITY LOT_QUANTITY, RLS.PRIMARY_QUANTITY LOT_PRIMARY_QUANTITY from mtl_supply ms, fnd_lookup_values_vl flv, rcv_shipment_lines rsl, PO_REQUISITION_HEADERS_ALL PRH, po_requisition_lines_all prl, rcv_shipment_headers rsh, mtl_units_of_measure muom, mtl_system_items_b_kfv msi, mtl_system_items msi1, rcv_serials_supply rss, rcv_serials_supply rss1, rcv_lots_supply rls, OE_ORDER_HEADERS_ALL OH where flv.lookup_code = 'INTSHIP' AND flv.lookup_type = 'DOC_TYPE' AND nvl(flv.start_date_active, sysdate)<=sysdate AND nvl(flv.end_date_active,sysdate)>=sysdate AND flv.enabled_flag = 'Y' and rsl.shipment_line_id = ms.shipment_line_id and prl.requisition_line_id = ms.req_line_id and prh.requisition_header_id = ms.req_header_id and rsh.shipment_header_id = ms.shipment_header_id AND MUOM.UNIT_OF_MEASURE = RSL.PRIMARY_UNIT_OF_MEASURE and msi.organization_id = ms.to_organization_id and msi.inventory_item_id = ms.item_id and msi1.organization_id = ms.from_organization_id and msi1.inventory_item_id = ms.item_id and rss1.shipment_line_id(+) = ms.shipment_line_id and rss1.lot_num(+) is null and rls.shipment_line_id(+) = ms.shipment_line_id AND RSS.SHIPMENT_LINE_ID (+) = RLS.SHIPMENT_LINE_ID AND RSS.LOT_NUM (+) = RLS.LOT_NUM and OH.orig_sys_document_ref = PRH.segment1 and oh.source_document_id = prh.requisition_header_id union all SELECT flv.MEANING DOC_TYPE , flv.LOOKUP_CODE DOC_TYPE_CODE, 'VENDOR' SOURCE_TYPE_CODE, 'VENDOR' RECEIPT_SOURCE_CODE, 'PO' ORDER_TYPE_CODE, '' ORDER_TYPE, ph.PO_HEADER_ID PO_HEADER_ID, ph.SEGMENT1 PO_NUMBER, pll.PO_LINE_ID PO_LINE_ID, pl.LINE_NUM PO_LINE_NUMBER, pll.LINE_LOCATION_ID PO_LINE_LOCATION_ID, pll.SHIPMENT_NUM PO_SHIPMENT_NUMBER, pll.PO_RELEASE_ID PO_RELEASE_ID, PR.RELEASE_NUM PO_RELEASE_NUMBER, TO_NUMBER(NULL) REQ_HEADER_ID, NULL REQ_NUMBER, TO_NUMBER(NULL) REQ_LINE_ID, TO_NUMBER(NULL) REQ_LINE, TO_NUMBER(NULL) REQ_DISTRIBUTION_ID, ph.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID, ph.SEGMENT1 RCV_SHIPMENT_NUMBER, pl.PO_LINE_ID RCV_SHIPMENT_LINE_ID, pl.LINE_NUM RCV_LINE_NUMBER, TO_NUMBER(NULL) FROM_ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID, ph.VENDOR_ID VENDOR_ID, ph.VENDOR_SITE_ID VENDOR_SITE_ID, '' OUTSIDE_OPERATION_FLAG, pl.ITEM_ID ITEM_ID, NULL uom_code, pl.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM, MUOM.UOM_CLASS PRIMARY_UOM_CLASS, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE, NVL(MSI.LOCATION_CONTROL_CODE,1) ITEM_LOCATOR_CONTROL, DECODE(MSI.RESTRICT_LOCATORS_CODE,1,'Y','N') RESTRICT_LOCATORS_CODE, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE,1,'Y','N') RESTRICT_SUBINVENTORIES_CODE, NVL(MSI.SHELF_LIFE_CODE,1) SHELF_LIFE_CODE, NVL(MSI.SHELF_LIFE_DAYS,0) SHELF_LIFE_DAYS, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE, DECODE(MSI.REVISION_QTY_CONTROL_CODE,1,'N',2,'Y','N') ITEM_REV_CONTROL_FLAG_TO, NULL ITEM_REV_CONTROL_FLAG_FROM, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER, pl.ITEM_REVISION ITEM_REVISION, pl.ITEM_DESCRIPTION ITEM_DESCRIPTION, pl.CATEGORY_ID ITEM_CATEGORY_ID, '' HAZARD_CLASS, '' UN_NUMBER, pl.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER, pll.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID, '' SHIP_TO_LOCATION, NULL PACKING_SLIP, pll.RECEIVING_ROUTING_ID ROUTING_ID, '' ROUTING_NAME, pll.NEED_BY_DATE NEED_BY_DATE, NVL(pll.PROMISED_DATE,pll.NEED_BY_DATE) EXPECTED_RECEIPT_DATE, pll.QUANTITY ORDERED_QTY, pl.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM, NULL USSGL_TRANSACTION_CODE, pll.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT, pll.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG, pll.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG, pll.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE, NVL(pll.PRICE_OVERRIDE,pl.UNIT_PRICE) UNIT_PRICE, ph.CURRENCY_CODE CURRENCY_CODE, ph.RATE_TYPE CURRENCY_CONVERSION_TYPE, ph.RATE_DATE CURRENCY_CONVERSION_DATE, ph.RATE CURRENCY_CONVERSION_RATE, ph.NOTE_TO_RECEIVER NOTE_TO_RECEIVER, pod.destination_type_code DESTINATION_TYPE_CODE, pod.deliver_to_person_id DELIVER_TO_PERSON_ID, pod.deliver_to_location_id DELIVER_TO_LOCATION_ID, pod.destination_subinventory DESTINATION_SUBINVENTORY, pll.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, pll.ATTRIBUTE1 ATTRIBUTE1, pll.ATTRIBUTE2 ATTRIBUTE2, pll.ATTRIBUTE3 ATTRIBUTE3, pll.ATTRIBUTE4 ATTRIBUTE4, pll.ATTRIBUTE5 ATTRIBUTE5, pll.ATTRIBUTE6 ATTRIBUTE6, pll.ATTRIBUTE7 ATTRIBUTE7, pll.ATTRIBUTE8 ATTRIBUTE8, pll.ATTRIBUTE9 ATTRIBUTE9, pll.ATTRIBUTE10 ATTRIBUTE10, pll.ATTRIBUTE11 ATTRIBUTE11, pll.ATTRIBUTE12 ATTRIBUTE12, pll.ATTRIBUTE13 ATTRIBUTE13, pll.ATTRIBUTE14 ATTRIBUTE14, pll.ATTRIBUTE15 ATTRIBUTE15, pll.CLOSED_CODE CLOSED_CODE, NULL ASN_TYPE, NULL BILL_OF_LADING, TO_DATE(NULL) SHIPPED_DATE, NULL FREIGHT_CARRIER_CODE, NULL WAYBILL_AIRBILL_NUM, NULL FREIGHT_BILL_NUM, NULL VENDOR_LOT_NUM, NULL CONTAINER_NUM, NULL TRUCK_NUM, NULL BAR_CODE_LABEL, '' RATE_TYPE_DISPLAY, pll.MATCH_OPTION MATCH_OPTION, pll.COUNTRY_OF_ORIGIN_CODE COUNTRY_OF_ORIGIN_CODE, TO_NUMBER(NULL) OE_ORDER_HEADER_ID, TO_NUMBER(NULL) OE_ORDER_NUM, TO_NUMBER(NULL) OE_ORDER_LINE_ID, TO_NUMBER(NULL) OE_ORDER_LINE_NUM, TO_NUMBER(NULL) CUSTOMER_ID, TO_NUMBER(NULL) CUSTOMER_SITE_ID, NULL CUSTOMER_ITEM_NUM, NULL pll_note_to_receiver, pod.po_distribution_id, pod.quantity_ordered - pod.quantity_delivered transaction_qty, pod.wip_entity_id, pod.wip_operation_seq_num, pod.wip_resource_seq_num, pod.wip_repetitive_schedule_id, pod.wip_line_id, pod.bom_resource_id, '' DESTINATION_TYPE, '' LOCATION, pod.rate currency_conversion_rate_pod, pod.rate_date currency_conversion_date_pod, pod.project_id project_id, pod.task_id task_id, sob.set_of_books_id set_of_books_id_sob,sob.currency_code currency_code_sob, TO_NUMBER(NULL) COST_GROUP_ID,NULL SERIAL_NUM,NULL LOT_NUM, TO_NUMBER(NULL) LOT_QUANTITY,TO_NUMBER(NULL) LOT_PRIMARY_QUANTITY FROM MTL_SUPPLY ms, PO_HEADERS_ALL PH, PO_LINE_LOCATIONS_ALL PLL, PO_LINES_ALL PL, PO_RELEASES_ALL PR, MTL_SYSTEM_ITEMS_B_KFV MSI, MTL_UNITS_OF_MEASURE MUOM, PO_DISTRIBUTIONS_ALL POD, FND_LOOKUP_VALUES_VL FLV, ORG_ORGANIZATION_DEFINITIONS OOD, GL_SETS_OF_BOOKS SOB WHERE FLV.LOOKUP_CODE = 'PO' AND FLV.LOOKUP_TYPE = 'DOC_TYPE' AND nvl(flv.start_date_active, sysdate)<=sysdate AND nvl(flv.end_date_active,sysdate)>=sysdate AND FLV.ENABLED_FLAG = 'Y' and ph.po_header_id = ms.po_header_id AND PH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT') AND NVL(PH.CANCEL_FLAG, 'N') IN ('N', 'I') AND NVL(PH.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED') AND pll.line_location_id = ms.po_line_location_id AND Nvl(PLL.APPROVED_FLAG,'N') = 'Y' AND Nvl(PLL.CANCEL_FLAG,'N') = 'N' AND Nvl(PLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING') AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND pll.RECEIVING_ROUTING_ID = 3 AND PL.PO_LINE_ID = ms.PO_LINE_ID AND pr.PO_RELEASE_ID(+) = ms.PO_RELEASE_ID AND MUOM.UNIT_OF_MEASURE = ms.unit_of_measure AND MSI.ORGANIZATION_ID = ms.TO_ORGANIZATION_ID and msi.inventory_item_id = ms.item_id and pod.po_distribution_id = ms.po_distribution_id AND OOD.ORGANIZATION_ID = ms.TO_ORGANIZATION_ID AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID

View Text - HTML Formatted

SELECT FLV.MEANING DOC_TYPE
, FLV.LOOKUP_CODE DOC_TYPE_CODE
, 'INTERNAL' SOURCE_TYPE_CODE
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INVENTORY'
, 'INVENTORY'
, 'REQ'
, 'INTERNAL ORDER') RECEIPT_SOURCE_CODE
, RSL.SOURCE_DOCUMENT_CODE ORDER_TYPE_CODE
, '' ORDER_TYPE
, TO_NUMBER(NULL) PO_HEADER_ID
, NULL PO_NUMBER
, TO_NUMBER(NULL) PO_LINE_ID
, TO_NUMBER(NULL) PO_LINE_NUMBER
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, RSL.LINE_NUM PO_SHIPMENT_NUMBER
, TO_NUMBER(NULL) PO_RELESE_ID
, TO_NUMBER(NULL) PO_RELEASE_NUMBER
, PRH.REQUISITION_HEADER_ID REQ_HEADER_ID
, PRH.SEGMENT1 REQ_NUMBER
, PRL.REQUISITION_LINE_ID REQ_LINE_ID
, PRL.LINE_NUM REQ_LINE
, RSL.REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_ID
, RSL.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER
, RSL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID
, RSL.LINE_NUM RCV_LINE_NUMBER
, RSL.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID
, PRL.DESTINATION_ORGANIZATION_ID TO_ORGANIZATION_ID
, TO_NUMBER(NULL) VENDOR_ID
, TO_NUMBER(NULL) VENDOR_SITE_ID
, 'N' OUTSIDE_OPERATION_FLAG
, RSL.ITEM_ID ITEM_ID
, NULL UOM_CODE
, RSL.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM
, MUOM.UOM_CLASS PRIMARY_UOM_CLASS
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE
, NVL(MSI.LOCATION_CONTROL_CODE
, 1) ITEM_LOCATOR_CONTROL
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'N') RESTRICT_LOCATORS_CODE
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N') RESTRICT_SUBINVENTORIES_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE_CODE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_LIFE_DAYS
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, DECODE(MSI.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'N') ITEM_REV_CONTROL_FLAG_TO
, DECODE(MSI1.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'N') ITEM_REV_CONTROL_FLAG_FROM
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, RSL.ITEM_REVISION ITEM_REVISION
, RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, RSL.CATEGORY_ID ITEM_CATEGORY_ID
, '' HAZARD_CLASS
, '' UN_NUMBER
, RSL.VENDOR_ITEM_NUM VENDOR_ITEM_NUMBER
, RSH.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, '' SHIP_TO_LOCATION
, RSH.PACKING_SLIP PACKING_SLIP
, RSL.ROUTING_HEADER_ID ROUTING_ID
, '' ROUTING_NAME
, PRL.NEED_BY_DATE NEED_BY_DATE
, RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE
, RSL.QUANTITY_SHIPPED ORDERED_QTY
, RSL.UNIT_OF_MEASURE ORDERED_UOM
, RSH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, RSH.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT
, NULL INSPECTION_REQUIRED_FLAG
, NULL RECEIPT_REQUIRED_FLAG
, NULL ENFORCE_SHIP_TO_LOCATION_CODE
, TO_NUMBER(NULL) UNIT_PRICE
, NULL CURRENCY_CODE
, NULL CURRENCY_CONVERSION_TYPE
, TO_DATE(NULL) CURRENCY_CONVERSION_DATE
, TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE
, NULL NOTE_TO_RECEIVER
, RSL.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE
, RSL.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID
, RSL.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID
, PRL.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY
, RSL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, RSL.ATTRIBUTE1 ATTRIBUTE1
, RSL.ATTRIBUTE2 ATTRIBUTE2
, RSL.ATTRIBUTE3 ATTRIBUTE3
, RSL.ATTRIBUTE4 ATTRIBUTE4
, RSL.ATTRIBUTE5 ATTRIBUTE5
, RSL.ATTRIBUTE6 ATTRIBUTE6
, RSL.ATTRIBUTE7 ATTRIBUTE7
, RSL.ATTRIBUTE8 ATTRIBUTE8
, RSL.ATTRIBUTE9 ATTRIBUTE9
, RSL.ATTRIBUTE10 ATTRIBUTE10
, RSL.ATTRIBUTE11 ATTRIBUTE11
, RSL.ATTRIBUTE12 ATTRIBUTE12
, RSL.ATTRIBUTE13 ATTRIBUTE13
, RSL.ATTRIBUTE14 ATTRIBUTE14
, RSL.ATTRIBUTE15 ATTRIBUTE15
, 'OPEN' CLOSED_CODE
, NULL ASN_TYPE
, RSH.BILL_OF_LADING BILL_OF_LADING
, RSH.SHIPPED_DATE SHIPPED_DATE
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE
, RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM
, RSH.FREIGHT_BILL_NUMBER FREIGHT_BILL_NUM
, RSL.VENDOR_LOT_NUM VENDOR_LOT_NUM
, RSL.CONTAINER_NUM CONTAINER_NUM
, RSL.TRUCK_NUM TRUCK_NUM
, RSL.BAR_CODE_LABEL BAR_CODE_LABEL
, NULL RATE_TYPE_DISPLAY
, 'P' MATCH_OPTION
, NULL COUNTRY_OF_ORIGIN_CODE
, OH.HEADER_ID OE_ORDER_HEADER_ID
, OH.ORDER_NUMBER OE_ORDER_NUM
, TO_NUMBER(NULL) OE_ORDER_LINE_ID
, TO_NUMBER(NULL) OE_ORDER_LINE_NUM
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_NUMBER(NULL) CUSTOMER_SITE_ID
, NULL CUSTOMER_ITEM_NUM
, NULL PLL_NOTE_TO_RECEIVER
, TO_NUMBER(NULL) PO_DISTRIBUTION_ID
, NVL(RSL.QUANTITY_SHIPPED
, 0) - NVL(RSL.QUANTITY_RECEIVED
, 0) TRANSACTION_QTY
, TO_NUMBER(NULL) WIP_ENTITY_ID
, TO_NUMBER(NULL) WIP_OPERATION_SEQ_NUM
, TO_NUMBER(NULL) WIP_RESOURCE_SEQ_NUM
, TO_NUMBER(NULL) WIP_REPETITIVE_SCHEDULE_ID
, TO_NUMBER(NULL) WIP_LINE_ID
, TO_NUMBER(NULL) BOM_RESOURCE_ID
, '' DESINATION_TYPE
, '' LOCATION
, TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE_POD
, TO_DATE(NULL) CURRENCY_CONVERSION_DATE_POD
, TO_NUMBER(NULL) PROJECT_ID
, TO_NUMBER(NULL) TASK_ID
, TO_NUMBER(NULL) SET_OF_BOOKS_ID_SOB
, NULL CURRENCY_CODE_SOB
, RSL.COST_GROUP_ID COST_GROUP_ID
, NVL(RSS1.SERIAL_NUM
, RSS.SERIAL_NUM) SERIAL_NUM
, RLS.LOT_NUM
, RLS.QUANTITY LOT_QUANTITY
, RLS.PRIMARY_QUANTITY LOT_PRIMARY_QUANTITY
FROM MTL_SUPPLY MS
, FND_LOOKUP_VALUES_VL FLV
, RCV_SHIPMENT_LINES RSL
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, RCV_SHIPMENT_HEADERS RSH
, MTL_UNITS_OF_MEASURE MUOM
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_SYSTEM_ITEMS MSI1
, RCV_SERIALS_SUPPLY RSS
, RCV_SERIALS_SUPPLY RSS1
, RCV_LOTS_SUPPLY RLS
, OE_ORDER_HEADERS_ALL OH
WHERE FLV.LOOKUP_CODE = 'INTSHIP'
AND FLV.LOOKUP_TYPE = 'DOC_TYPE'
AND NVL(FLV.START_DATE_ACTIVE
, SYSDATE)<=SYSDATE
AND NVL(FLV.END_DATE_ACTIVE
, SYSDATE)>=SYSDATE
AND FLV.ENABLED_FLAG = 'Y'
AND RSL.SHIPMENT_LINE_ID = MS.SHIPMENT_LINE_ID
AND PRL.REQUISITION_LINE_ID = MS.REQ_LINE_ID
AND PRH.REQUISITION_HEADER_ID = MS.REQ_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID = MS.SHIPMENT_HEADER_ID
AND MUOM.UNIT_OF_MEASURE = RSL.PRIMARY_UNIT_OF_MEASURE
AND MSI.ORGANIZATION_ID = MS.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MS.ITEM_ID
AND MSI1.ORGANIZATION_ID = MS.FROM_ORGANIZATION_ID
AND MSI1.INVENTORY_ITEM_ID = MS.ITEM_ID
AND RSS1.SHIPMENT_LINE_ID(+) = MS.SHIPMENT_LINE_ID
AND RSS1.LOT_NUM(+) IS NULL
AND RLS.SHIPMENT_LINE_ID(+) = MS.SHIPMENT_LINE_ID
AND RSS.SHIPMENT_LINE_ID (+) = RLS.SHIPMENT_LINE_ID
AND RSS.LOT_NUM (+) = RLS.LOT_NUM
AND OH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1
AND OH.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID UNION ALL SELECT FLV.MEANING DOC_TYPE
, FLV.LOOKUP_CODE DOC_TYPE_CODE
, 'VENDOR' SOURCE_TYPE_CODE
, 'VENDOR' RECEIPT_SOURCE_CODE
, 'PO' ORDER_TYPE_CODE
, '' ORDER_TYPE
, PH.PO_HEADER_ID PO_HEADER_ID
, PH.SEGMENT1 PO_NUMBER
, PLL.PO_LINE_ID PO_LINE_ID
, PL.LINE_NUM PO_LINE_NUMBER
, PLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, PLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, PLL.PO_RELEASE_ID PO_RELEASE_ID
, PR.RELEASE_NUM PO_RELEASE_NUMBER
, TO_NUMBER(NULL) REQ_HEADER_ID
, NULL REQ_NUMBER
, TO_NUMBER(NULL) REQ_LINE_ID
, TO_NUMBER(NULL) REQ_LINE
, TO_NUMBER(NULL) REQ_DISTRIBUTION_ID
, PH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID
, PH.SEGMENT1 RCV_SHIPMENT_NUMBER
, PL.PO_LINE_ID RCV_SHIPMENT_LINE_ID
, PL.LINE_NUM RCV_LINE_NUMBER
, TO_NUMBER(NULL) FROM_ORGANIZATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID TO_ORGANIZATION_ID
, PH.VENDOR_ID VENDOR_ID
, PH.VENDOR_SITE_ID VENDOR_SITE_ID
, '' OUTSIDE_OPERATION_FLAG
, PL.ITEM_ID ITEM_ID
, NULL UOM_CODE
, PL.UNIT_MEAS_LOOKUP_CODE PRIMARY_UOM
, MUOM.UOM_CLASS PRIMARY_UOM_CLASS
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE
, NVL(MSI.LOCATION_CONTROL_CODE
, 1) ITEM_LOCATOR_CONTROL
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'N') RESTRICT_LOCATORS_CODE
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N') RESTRICT_SUBINVENTORIES_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE_CODE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_LIFE_DAYS
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, DECODE(MSI.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'N') ITEM_REV_CONTROL_FLAG_TO
, NULL ITEM_REV_CONTROL_FLAG_FROM
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, PL.ITEM_REVISION ITEM_REVISION
, PL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, PL.CATEGORY_ID ITEM_CATEGORY_ID
, '' HAZARD_CLASS
, '' UN_NUMBER
, PL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER
, PLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, '' SHIP_TO_LOCATION
, NULL PACKING_SLIP
, PLL.RECEIVING_ROUTING_ID ROUTING_ID
, '' ROUTING_NAME
, PLL.NEED_BY_DATE NEED_BY_DATE
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, PLL.QUANTITY ORDERED_QTY
, PL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM
, NULL USSGL_TRANSACTION_CODE
, PLL.GOVERNMENT_CONTEXT GOVERNMENT_CONTEXT
, PLL.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, PLL.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE ENFORCE_SHIP_TO_LOCATION_CODE
, NVL(PLL.PRICE_OVERRIDE
, PL.UNIT_PRICE) UNIT_PRICE
, PH.CURRENCY_CODE CURRENCY_CODE
, PH.RATE_TYPE CURRENCY_CONVERSION_TYPE
, PH.RATE_DATE CURRENCY_CONVERSION_DATE
, PH.RATE CURRENCY_CONVERSION_RATE
, PH.NOTE_TO_RECEIVER NOTE_TO_RECEIVER
, POD.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE
, POD.DELIVER_TO_PERSON_ID DELIVER_TO_PERSON_ID
, POD.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID
, POD.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY
, PLL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1 ATTRIBUTE1
, PLL.ATTRIBUTE2 ATTRIBUTE2
, PLL.ATTRIBUTE3 ATTRIBUTE3
, PLL.ATTRIBUTE4 ATTRIBUTE4
, PLL.ATTRIBUTE5 ATTRIBUTE5
, PLL.ATTRIBUTE6 ATTRIBUTE6
, PLL.ATTRIBUTE7 ATTRIBUTE7
, PLL.ATTRIBUTE8 ATTRIBUTE8
, PLL.ATTRIBUTE9 ATTRIBUTE9
, PLL.ATTRIBUTE10 ATTRIBUTE10
, PLL.ATTRIBUTE11 ATTRIBUTE11
, PLL.ATTRIBUTE12 ATTRIBUTE12
, PLL.ATTRIBUTE13 ATTRIBUTE13
, PLL.ATTRIBUTE14 ATTRIBUTE14
, PLL.ATTRIBUTE15 ATTRIBUTE15
, PLL.CLOSED_CODE CLOSED_CODE
, NULL ASN_TYPE
, NULL BILL_OF_LADING
, TO_DATE(NULL) SHIPPED_DATE
, NULL FREIGHT_CARRIER_CODE
, NULL WAYBILL_AIRBILL_NUM
, NULL FREIGHT_BILL_NUM
, NULL VENDOR_LOT_NUM
, NULL CONTAINER_NUM
, NULL TRUCK_NUM
, NULL BAR_CODE_LABEL
, '' RATE_TYPE_DISPLAY
, PLL.MATCH_OPTION MATCH_OPTION
, PLL.COUNTRY_OF_ORIGIN_CODE COUNTRY_OF_ORIGIN_CODE
, TO_NUMBER(NULL) OE_ORDER_HEADER_ID
, TO_NUMBER(NULL) OE_ORDER_NUM
, TO_NUMBER(NULL) OE_ORDER_LINE_ID
, TO_NUMBER(NULL) OE_ORDER_LINE_NUM
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_NUMBER(NULL) CUSTOMER_SITE_ID
, NULL CUSTOMER_ITEM_NUM
, NULL PLL_NOTE_TO_RECEIVER
, POD.PO_DISTRIBUTION_ID
, POD.QUANTITY_ORDERED - POD.QUANTITY_DELIVERED TRANSACTION_QTY
, POD.WIP_ENTITY_ID
, POD.WIP_OPERATION_SEQ_NUM
, POD.WIP_RESOURCE_SEQ_NUM
, POD.WIP_REPETITIVE_SCHEDULE_ID
, POD.WIP_LINE_ID
, POD.BOM_RESOURCE_ID
, '' DESTINATION_TYPE
, '' LOCATION
, POD.RATE CURRENCY_CONVERSION_RATE_POD
, POD.RATE_DATE CURRENCY_CONVERSION_DATE_POD
, POD.PROJECT_ID PROJECT_ID
, POD.TASK_ID TASK_ID
, SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID_SOB
, SOB.CURRENCY_CODE CURRENCY_CODE_SOB
, TO_NUMBER(NULL) COST_GROUP_ID
, NULL SERIAL_NUM
, NULL LOT_NUM
, TO_NUMBER(NULL) LOT_QUANTITY
, TO_NUMBER(NULL) LOT_PRIMARY_QUANTITY
FROM MTL_SUPPLY MS
, PO_HEADERS_ALL PH
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL PL
, PO_RELEASES_ALL PR
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_UNITS_OF_MEASURE MUOM
, PO_DISTRIBUTIONS_ALL POD
, FND_LOOKUP_VALUES_VL FLV
, ORG_ORGANIZATION_DEFINITIONS OOD
, GL_SETS_OF_BOOKS SOB
WHERE FLV.LOOKUP_CODE = 'PO'
AND FLV.LOOKUP_TYPE = 'DOC_TYPE'
AND NVL(FLV.START_DATE_ACTIVE
, SYSDATE)<=SYSDATE
AND NVL(FLV.END_DATE_ACTIVE
, SYSDATE)>=SYSDATE
AND FLV.ENABLED_FLAG = 'Y'
AND PH.PO_HEADER_ID = MS.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE IN ('STANDARD'
, 'PLANNED'
, 'BLANKET'
, 'CONTRACT')
AND NVL(PH.CANCEL_FLAG
, 'N') IN ('N'
, 'I')
AND NVL(PH.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED FOR RECEIVING'
, 'CLOSED')
AND PLL.LINE_LOCATION_ID = MS.PO_LINE_LOCATION_ID
AND NVL(PLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED FOR RECEIVING')
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND PLL.RECEIVING_ROUTING_ID = 3
AND PL.PO_LINE_ID = MS.PO_LINE_ID
AND PR.PO_RELEASE_ID(+) = MS.PO_RELEASE_ID
AND MUOM.UNIT_OF_MEASURE = MS.UNIT_OF_MEASURE
AND MSI.ORGANIZATION_ID = MS.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MS.ITEM_ID
AND POD.PO_DISTRIBUTION_ID = MS.PO_DISTRIBUTION_ID
AND OOD.ORGANIZATION_ID = MS.TO_ORGANIZATION_ID
AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID