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.

[Home] [Help]

VIEW: APPS.OE_PO_ENTER_RECEIPTS_V

Source

View Text - Preformatted


SELECT 'N' LINE_CHKBOX ,'CUSTOMER' SOURCE_TYPE_CODE ,'CUSTOMER' RECEIPT_SOURCE_CODE ,OLT.ORDER_CATEGORY_CODE ORDER_TYPE_CODE ,T.NAME 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 ,to_number(null) PO_SHIPMENT_NUMBER ,to_number(null) PO_RELEASE_ID ,to_number(null) 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 ,to_number(null) RCV_SHIPMENT_HEADER_ID ,null RCV_SHIPMENT_NUMBER ,to_number(null) RCV_SHIPMENT_LINE_ID ,to_number(null) RCV_LINE_NUMBER ,nvl(OEL.SHIP_TO_ORG_ID,OEH.SHIP_TO_ORG_ID) FROM_ORGANIZATION_ID ,nvl(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) TO_ORGANIZATION_ID ,to_number(NULL) VENDOR_ID ,PARTY.PARTY_NAME SOURCE ,to_number(NULL) VENDOR_SITE_ID ,null OUTSIDE_OPERATION_FLAG ,OEL.INVENTORY_ITEM_ID Item_Id ,MUM1.UNIT_OF_MEASURE PRIMARY_UOM ,MUM1.UOM_CLASS PRIMARY_UOM_CLASS , NVL(MSIWAREHOUSE.ALLOWED_UNITS_LOOKUP_CODE ,2) ITEM_ALLOWED_UNITS_LOOKUP_CODE , NVL(MSIWAREHOUSE.LOCATION_CONTROL_CODE ,1) ITEM_LOCATOR_CONTROL , DECODE(MSIWAREHOUSE.RESTRICT_LOCATORS_CODE ,1 ,'Y' ,'N') RESTRICT_LOCATORS_CODE , DECODE(MSIWAREHOUSE.RESTRICT_SUBINVENTORIES_CODE ,1 ,'Y' ,'N') RESTRICT_SUBINVENTORIES_CODE , NVL(MSIWAREHOUSE.SHELF_LIFE_CODE ,1) SHELF_LIFE_CODE , NVL(MSIWAREHOUSE.SHELF_LIFE_DAYS ,0) SHELF_LIFE_DAYS , MSIWAREHOUSE.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE , MSIWAREHOUSE.LOT_CONTROL_CODE LOT_CONTROL_CODE , DECODE(MSIWAREHOUSE.REVISION_QTY_CONTROL_CODE ,1 ,'N' ,2 ,'Y' ,'N') ITEM_REV_CONTROL_FLAG_TO , NULL ITEM_REV_CONTROL_FLAG_FROM , MSI.SEGMENT1 Item_Number , OEL.ITEM_REVISION ITEM_REVISION ,MSIT.DESCRIPTION ITEM_DESCRIPTION , to_number(null) ITEM_CATEGORY_ID , null HAZARD_CLASS , null UN_NUMBER , null VENDOR_ITEM_NUMBER , to_number(null) SHIP_TO_LOCATION_ID , null SHIP_TO_LOCATION , null PACKING_SLIP , to_number(null) ROUTING_ID , null ROUTING_NAME , OEL.REQUEST_DATE NEED_BY_DATE , NVL(OEL.PROMISE_DATE, OEL.REQUEST_DATE) EXPECTED_RECEIPT_DATE , OEL.ORDERED_QUANTITY ORDERED_QTY , MUM.UNIT_OF_MEASURE ORDERED_UOM , null USSGL_TRANSACTION_CODE , null GOVERNMENT_CONTEXT , DECODE(MSIWAREHOUSE.RETURN_INSPECTION_REQUIREMENT,1,'Y','N') INSPECTION_REQUIRED_FLAG , 'Y' RECEIPT_REQUIRED_FLAG , 'N' ENFORCE_SHIP_TO_LOCATION_CODE , OEL.UNIT_SELLING_PRICE UNIT_PRICE , OEH.TRANSACTIONAL_CURR_CODE CURRENCY_CODE , OEH.CONVERSION_TYPE_CODE CURRENCY_CONVERSION_TYPE , OEH.CONVERSION_RATE_DATE CURRENCY_CONVERSION_DATE , OEH.CONVERSION_RATE CURRENCY_CONVERSION_RATE , OEL.SHIPPING_INSTRUCTIONS NOTE_TO_RECEIVER , NULL DESTINATION_TYPE_CODE , OEL.DELIVER_TO_CONTACT_ID DELIVER_TO_PERSON_ID , OEL.DELIVER_TO_ORG_ID DELIVER_TO_LOCATION_ID , NULL DESTINATION_SUBINVENTORY , OEL.CONTEXT ATTRIBUTE_CATEGORY , OEL.ATTRIBUTE1 ATTRIBUTE1 , OEL.ATTRIBUTE2 ATTRIBUTE2 , OEL.ATTRIBUTE3 ATTRIBUTE3 , OEL.ATTRIBUTE4 ATTRIBUTE4 , OEL.ATTRIBUTE5 ATTRIBUTE5 , OEL.ATTRIBUTE6 ATTRIBUTE6 , OEL.ATTRIBUTE7 ATTRIBUTE7 , OEL.ATTRIBUTE8 ATTRIBUTE8 , OEL.ATTRIBUTE9 ATTRIBUTE9 , OEL.ATTRIBUTE10 ATTRIBUTE10 , OEL.ATTRIBUTE11 ATTRIBUTE11 , OEL.ATTRIBUTE12 ATTRIBUTE12 , OEL.ATTRIBUTE13 ATTRIBUTE13 , OEL.ATTRIBUTE14 ATTRIBUTE14 , OEL.ATTRIBUTE15 ATTRIBUTE15 ,null 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 ,null RATE_TYPE_DISPLAY ,null MATCH_OPTION ,null COUNTRY_OF_ORIGIN_CODE ,OEL.HEADER_ID OE_ORDER_HEADER_ID ,OEL.LINE_ID OE_ORDER_LINE_ID ,OEH.ORDER_NUMBER OE_ORDER_NUM ,OEL.LINE_NUMBER OE_ORDER_LINE_NUM ,OEL.SHIPMENT_NUMBER OE_ORDER_SHIP_NUM ,MCI.CUSTOMER_ITEM_NUMBER CUSTOMER_ITEM_NUM ,ACCT.CUST_ACCOUNT_ID CUSTOMER_ID ,NVL(OEL.SHIP_TO_ORG_ID, OEH.SHIP_TO_ORG_ID) CUSTOMER_SITE_ID ,OEL.ORDERED_QUANTITY2 ORDERED_QUANTITY2 ,MUM2.UNIT_OF_MEASURE ORDERED_QUANTITY_UOM2 ,OEL.PREFERRED_GRADE PREFERRED_GRADE ,OEH.ORG_ID ORG_ID FROM OE_ORDER_HEADERS_ALL OEH , OE_ORDER_LINES_ALL OEL , OE_TRANSACTION_TYPES_ALL OLT , OE_TRANSACTION_TYPES_TL T , HZ_CUST_ACCOUNTS ACCT , HZ_PARTIES PARTY , MTL_SYSTEM_ITEMS MSI , MTL_SYSTEM_ITEMS_TL MSIT , MTL_SYSTEM_ITEMS MSIWAREHOUSE , MTL_CUSTOMER_ITEMS MCI , MTL_UNITS_OF_MEASURE_TL MUM , MTL_UNITS_OF_MEASURE_TL MUM2 , MTL_UNITS_OF_MEASURE_TL MUM1 WHERE OEH.HEADER_ID = OEL.HEADER_ID AND OEH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID AND OEH.OPEN_FLAG = 'Y' AND ACCT.PARTY_ID = PARTY.PARTY_ID AND OEL.LINE_CATEGORY_CODE='RETURN' AND OEL.OPEN_FLAG = 'Y' AND OEL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND OEL.INVENTORY_ITEM_ID = MSIWAREHOUSE.INVENTORY_ITEM_ID AND OE_SYS_PARAMETERS.VALUE_WNPS('MASTER_ORGANIZATION_ID', OEL.ORG_ID) = MSI.ORGANIZATION_ID AND OEL.SHIP_FROM_ORG_ID = MSIWAREHOUSE.ORGANIZATION_ID AND OEL.ORDERED_ITEM_ID = MCI.CUSTOMER_ITEM_ID (+) AND OEL.ORDER_QUANTITY_UOM = MUM.UOM_CODE AND OEL.ORDERED_QUANTITY_UOM2 = MUM2.UOM_CODE (+) AND NVL(MUM2.LANGUAGE,USERENV('LANG')) = USERENV('LANG') AND MUM.LANGUAGE = USERENV('LANG') AND MSI.PRIMARY_UOM_CODE = MUM1.UOM_CODE AND MUM1.LANGUAGE = USERENV('LANG') AND MSIWAREHOUSE.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y' AND OEL.LINE_TYPE_ID = OLT.TRANSACTION_TYPE_ID AND OLT.TRANSACTION_TYPE_CODE = 'LINE' AND OLT.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID AND T.LANGUAGE = userenv('LANG') AND OEL.BOOKED_FLAG='Y' AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY,0) AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN' AND MSIT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSIT.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MSIT.LANGUAGE = userenv('LANG')

View Text - HTML Formatted

SELECT 'N' LINE_CHKBOX
, 'CUSTOMER' SOURCE_TYPE_CODE
, 'CUSTOMER' RECEIPT_SOURCE_CODE
, OLT.ORDER_CATEGORY_CODE ORDER_TYPE_CODE
, T.NAME 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
, TO_NUMBER(NULL) PO_SHIPMENT_NUMBER
, TO_NUMBER(NULL) PO_RELEASE_ID
, TO_NUMBER(NULL) 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
, TO_NUMBER(NULL) RCV_SHIPMENT_HEADER_ID
, NULL RCV_SHIPMENT_NUMBER
, TO_NUMBER(NULL) RCV_SHIPMENT_LINE_ID
, TO_NUMBER(NULL) RCV_LINE_NUMBER
, NVL(OEL.SHIP_TO_ORG_ID
, OEH.SHIP_TO_ORG_ID) FROM_ORGANIZATION_ID
, NVL(OEL.SHIP_FROM_ORG_ID
, OEH.SHIP_FROM_ORG_ID) TO_ORGANIZATION_ID
, TO_NUMBER(NULL) VENDOR_ID
, PARTY.PARTY_NAME SOURCE
, TO_NUMBER(NULL) VENDOR_SITE_ID
, NULL OUTSIDE_OPERATION_FLAG
, OEL.INVENTORY_ITEM_ID ITEM_ID
, MUM1.UNIT_OF_MEASURE PRIMARY_UOM
, MUM1.UOM_CLASS PRIMARY_UOM_CLASS
, NVL(MSIWAREHOUSE.ALLOWED_UNITS_LOOKUP_CODE
, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE
, NVL(MSIWAREHOUSE.LOCATION_CONTROL_CODE
, 1) ITEM_LOCATOR_CONTROL
, DECODE(MSIWAREHOUSE.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'N') RESTRICT_LOCATORS_CODE
, DECODE(MSIWAREHOUSE.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N') RESTRICT_SUBINVENTORIES_CODE
, NVL(MSIWAREHOUSE.SHELF_LIFE_CODE
, 1) SHELF_LIFE_CODE
, NVL(MSIWAREHOUSE.SHELF_LIFE_DAYS
, 0) SHELF_LIFE_DAYS
, MSIWAREHOUSE.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSIWAREHOUSE.LOT_CONTROL_CODE LOT_CONTROL_CODE
, DECODE(MSIWAREHOUSE.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'N') ITEM_REV_CONTROL_FLAG_TO
, NULL ITEM_REV_CONTROL_FLAG_FROM
, MSI.SEGMENT1 ITEM_NUMBER
, OEL.ITEM_REVISION ITEM_REVISION
, MSIT.DESCRIPTION ITEM_DESCRIPTION
, TO_NUMBER(NULL) ITEM_CATEGORY_ID
, NULL HAZARD_CLASS
, NULL UN_NUMBER
, NULL VENDOR_ITEM_NUMBER
, TO_NUMBER(NULL) SHIP_TO_LOCATION_ID
, NULL SHIP_TO_LOCATION
, NULL PACKING_SLIP
, TO_NUMBER(NULL) ROUTING_ID
, NULL ROUTING_NAME
, OEL.REQUEST_DATE NEED_BY_DATE
, NVL(OEL.PROMISE_DATE
, OEL.REQUEST_DATE) EXPECTED_RECEIPT_DATE
, OEL.ORDERED_QUANTITY ORDERED_QTY
, MUM.UNIT_OF_MEASURE ORDERED_UOM
, NULL USSGL_TRANSACTION_CODE
, NULL GOVERNMENT_CONTEXT
, DECODE(MSIWAREHOUSE.RETURN_INSPECTION_REQUIREMENT
, 1
, 'Y'
, 'N') INSPECTION_REQUIRED_FLAG
, 'Y' RECEIPT_REQUIRED_FLAG
, 'N' ENFORCE_SHIP_TO_LOCATION_CODE
, OEL.UNIT_SELLING_PRICE UNIT_PRICE
, OEH.TRANSACTIONAL_CURR_CODE CURRENCY_CODE
, OEH.CONVERSION_TYPE_CODE CURRENCY_CONVERSION_TYPE
, OEH.CONVERSION_RATE_DATE CURRENCY_CONVERSION_DATE
, OEH.CONVERSION_RATE CURRENCY_CONVERSION_RATE
, OEL.SHIPPING_INSTRUCTIONS NOTE_TO_RECEIVER
, NULL DESTINATION_TYPE_CODE
, OEL.DELIVER_TO_CONTACT_ID DELIVER_TO_PERSON_ID
, OEL.DELIVER_TO_ORG_ID DELIVER_TO_LOCATION_ID
, NULL DESTINATION_SUBINVENTORY
, OEL.CONTEXT ATTRIBUTE_CATEGORY
, OEL.ATTRIBUTE1 ATTRIBUTE1
, OEL.ATTRIBUTE2 ATTRIBUTE2
, OEL.ATTRIBUTE3 ATTRIBUTE3
, OEL.ATTRIBUTE4 ATTRIBUTE4
, OEL.ATTRIBUTE5 ATTRIBUTE5
, OEL.ATTRIBUTE6 ATTRIBUTE6
, OEL.ATTRIBUTE7 ATTRIBUTE7
, OEL.ATTRIBUTE8 ATTRIBUTE8
, OEL.ATTRIBUTE9 ATTRIBUTE9
, OEL.ATTRIBUTE10 ATTRIBUTE10
, OEL.ATTRIBUTE11 ATTRIBUTE11
, OEL.ATTRIBUTE12 ATTRIBUTE12
, OEL.ATTRIBUTE13 ATTRIBUTE13
, OEL.ATTRIBUTE14 ATTRIBUTE14
, OEL.ATTRIBUTE15 ATTRIBUTE15
, NULL 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
, NULL RATE_TYPE_DISPLAY
, NULL MATCH_OPTION
, NULL COUNTRY_OF_ORIGIN_CODE
, OEL.HEADER_ID OE_ORDER_HEADER_ID
, OEL.LINE_ID OE_ORDER_LINE_ID
, OEH.ORDER_NUMBER OE_ORDER_NUM
, OEL.LINE_NUMBER OE_ORDER_LINE_NUM
, OEL.SHIPMENT_NUMBER OE_ORDER_SHIP_NUM
, MCI.CUSTOMER_ITEM_NUMBER CUSTOMER_ITEM_NUM
, ACCT.CUST_ACCOUNT_ID CUSTOMER_ID
, NVL(OEL.SHIP_TO_ORG_ID
, OEH.SHIP_TO_ORG_ID) CUSTOMER_SITE_ID
, OEL.ORDERED_QUANTITY2 ORDERED_QUANTITY2
, MUM2.UNIT_OF_MEASURE ORDERED_QUANTITY_UOM2
, OEL.PREFERRED_GRADE PREFERRED_GRADE
, OEH.ORG_ID ORG_ID
FROM OE_ORDER_HEADERS_ALL OEH
, OE_ORDER_LINES_ALL OEL
, OE_TRANSACTION_TYPES_ALL OLT
, OE_TRANSACTION_TYPES_TL T
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES PARTY
, MTL_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS_TL MSIT
, MTL_SYSTEM_ITEMS MSIWAREHOUSE
, MTL_CUSTOMER_ITEMS MCI
, MTL_UNITS_OF_MEASURE_TL MUM
, MTL_UNITS_OF_MEASURE_TL MUM2
, MTL_UNITS_OF_MEASURE_TL MUM1
WHERE OEH.HEADER_ID = OEL.HEADER_ID
AND OEH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID
AND OEH.OPEN_FLAG = 'Y'
AND ACCT.PARTY_ID = PARTY.PARTY_ID
AND OEL.LINE_CATEGORY_CODE='RETURN'
AND OEL.OPEN_FLAG = 'Y'
AND OEL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND OEL.INVENTORY_ITEM_ID = MSIWAREHOUSE.INVENTORY_ITEM_ID
AND OE_SYS_PARAMETERS.VALUE_WNPS('MASTER_ORGANIZATION_ID'
, OEL.ORG_ID) = MSI.ORGANIZATION_ID
AND OEL.SHIP_FROM_ORG_ID = MSIWAREHOUSE.ORGANIZATION_ID
AND OEL.ORDERED_ITEM_ID = MCI.CUSTOMER_ITEM_ID (+)
AND OEL.ORDER_QUANTITY_UOM = MUM.UOM_CODE
AND OEL.ORDERED_QUANTITY_UOM2 = MUM2.UOM_CODE (+)
AND NVL(MUM2.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND MUM.LANGUAGE = USERENV('LANG')
AND MSI.PRIMARY_UOM_CODE = MUM1.UOM_CODE
AND MUM1.LANGUAGE = USERENV('LANG')
AND MSIWAREHOUSE.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y'
AND OEL.LINE_TYPE_ID = OLT.TRANSACTION_TYPE_ID
AND OLT.TRANSACTION_TYPE_CODE = 'LINE'
AND OLT.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID
AND T.LANGUAGE = USERENV('LANG')
AND OEL.BOOKED_FLAG='Y'
AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY
, 0)
AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
AND MSIT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSIT.LANGUAGE = USERENV('LANG')