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

Source

View Text - Preformatted


SELECT L.LINE_ID , L.ORG_ID , L.HEADER_ID , L.LINE_TYPE_ID , OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(L.LINE_ID) LINE , L.LINE_NUMBER , DECODE(L.ITEM_IDENTIFIER_TYPE, 'CUST', C.CUSTOMER_ITEM_NUMBER, 'INT', ITEMSKFV.CONCATENATED_SEGMENTS, NULL, ITEMSKFV.CONCATENATED_SEGMENTS, L.ORDERED_ITEM) ITEM_NUMBER , DECODE(L.ITEM_IDENTIFIER_TYPE, 'CUST', NVL(C.CUSTOMER_ITEM_DESC,ITEMS.DESCRIPTION), 'INT', ITEMS.DESCRIPTION, NULL, ITEMS.DESCRIPTION, NVL(REF.DESCRIPTION, ITEMS.DESCRIPTION)) ITEM_DESCRIPTION , L.REQUEST_DATE , L.PROMISE_DATE , L.SCHEDULE_ARRIVAL_DATE , L.SCHEDULE_SHIP_DATE , UOM.UNIT_OF_MEASURE_TL ORDER_QUANTITY_UOM , DECODE(L.LINE_CATEGORY_CODE, 'RETURN', (-1)*ORDERED_QUANTITY, ORDERED_QUANTITY) ORDERED_QUANTITY , L.TAX_EXEMPT_NUMBER , L.CUST_PO_NUMBER , L.SOLD_TO_ORG_ID , L.SHIP_FROM_ORG_ID , L.SHIP_TO_ORG_ID , L.DELIVER_TO_ORG_ID , L.INTMED_SHIP_TO_ORG_ID , L.INVOICE_TO_ORG_ID , L.SHIP_TO_CONTACT_ID , L.DELIVER_TO_CONTACT_ID , L.INTMED_SHIP_TO_CONTACT_ID , L.INVOICE_TO_CONTACT_ID , L.INVENTORY_ITEM_ID , L.TAX_CODE , L.TAX_RATE , TO_CHAR(L.UNIT_LIST_PRICE, FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE, 30)) UNIT_LIST_PRICE , TO_CHAR(L.UNIT_SELLING_PRICE, FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE, 30)) UNIT_SELLING_PRICE , TO_CHAR(OE_OE_TOTALS_SUMMARY.GET_DISCOUNT(L.UNIT_LIST_PRICE,L.UNIT_SELLING_PRICE), FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE, 30)) DISCOUNT , L.TAX_VALUE , L.ATO_LINE_ID , L.CONFIGURATION_ID , L.TOP_MODEL_LINE_ID , L.LINK_TO_LINE_ID , L.COMPONENT_SEQUENCE_ID , L.COMPONENT_CODE , L.ITEM_TYPE_CODE , L.SHIPMENT_NUMBER , L.OPTION_NUMBER , L.COMPONENT_NUMBER , L.OPTION_FLAG , L.SALESREP_ID , L.ORDERED_ITEM_ID , L.ITEM_IDENTIFIER_TYPE , L.CONFIG_HEADER_ID , L.CONFIG_REV_NBR , L.SHIPPING_INSTRUCTIONS , L.PACKING_INSTRUCTIONS , L.INVOICED_QUANTITY , L.SERVICE_TXN_REASON_CODE , L.SERVICE_TXN_COMMENTS , L.SERVICE_DURATION , L.SERVICE_PERIOD , L.SERVICE_START_DATE , L.SERVICE_END_DATE , L.SERVICE_COTERMINATE_FLAG , L.SERVICE_NUMBER , L.SERVICE_REFERENCE_TYPE_CODE , L.SERVICE_REFERENCE_LINE_ID , L.SERVICE_REFERENCE_SYSTEM_ID , AGR.NAME AGREEMENT_NAME , AGR.REVISION AGREEMENT_VERSION , L.BLANKET_NUMBER , L.BLANKET_LINE_NUMBER , BLANKET.VERSION_NUMBER BLANKET_VERSION_NUMBER , INVRULE.NAME INVOICING_RULE_NAME , TERM.NAME PAYMENT_TERMS , TO_CHAR(OE_TOTALS_GRP.GET_ORDER_TOTAL(H.HEADER_ID, LINE_ID, 'ALL'), FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE, 30)) LINE_TOTAL , TO_CHAR(OE_TOTALS_GRP.GET_ORDER_TOTAL(H.HEADER_ID, LINE_ID, 'LINES'), FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE, 30)) LINE_PRICE , TO_CHAR(OE_TOTALS_GRP.GET_ORDER_TOTAL(H.HEADER_ID, LINE_ID, 'CHARGES'), FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE, 30)) LINE_CHARGES , TO_CHAR(OE_TOTALS_GRP.GET_ORDER_TOTAL(H.HEADER_ID, LINE_ID, 'TAXES'), FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE, 30)) LINE_TAXES , L.END_CUSTOMER_ID , L.END_CUSTOMER_CONTACT_ID , L.END_CUSTOMER_SITE_USE_ID , L.IB_OWNER , L.IB_INSTALLED_AT_LOCATION , L.IB_CURRENT_LOCATION , L.CHARGE_PERIODICITY_CODE FROM OE_ORDER_HEADERS_ALL H , OE_ORDER_LINES_ALL L , RA_RULES INVRULE , RA_TERMS_TL TERM , OE_AGREEMENTS_TL AGR , OE_BLANKET_HEADERS_ALL BLANKET , MTL_SYSTEM_ITEMS_TL ITEMS , MTL_SYSTEM_ITEMS_KFV ITEMSKFV , MTL_CUSTOMER_ITEMS C , MTL_CROSS_REFERENCES REF , MTL_UNITS_OF_MEASURE_TL UOM , OE_TRANSACTION_TYPES_ALL LT WHERE L.ITEM_TYPE_CODE <> 'INCLUDED' AND L.HEADER_ID = H.HEADER_ID AND L.LINE_TYPE_ID = LT.TRANSACTION_TYPE_ID AND LT.TRANSACTION_TYPE_CODE = 'LINE' AND L.AGREEMENT_ID = AGR.AGREEMENT_ID (+) AND AGR.LANGUAGE(+) = userenv('LANG') AND L.BLANKET_NUMBER = BLANKET.ORDER_NUMBER (+) AND L.INVOICING_RULE_ID = INVRULE.RULE_ID (+) AND L.PAYMENT_TERM_ID = TERM.TERM_ID (+) AND TERM.LANGUAGE(+) = userenv('LANG') AND L.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID(+) AND L.INVENTORY_ITEM_ID = ITEMSKFV.INVENTORY_ITEM_ID(+) AND ITEMS.ORGANIZATION_ID = ITEMSKFV.ORGANIZATION_ID AND ITEMS.LANGUAGE = userenv('LANG') AND OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID',L.ORG_ID) = ITEMS.ORGANIZATION_ID AND L.ITEM_IDENTIFIER_TYPE = REF.CROSS_REFERENCE_TYPE(+) AND L.ORDERED_ITEM = REF.CROSS_REFERENCE(+) AND L.INVENTORY_ITEM_ID = REF.INVENTORY_ITEM_ID(+) AND L.ORDERED_ITEM_ID = C.CUSTOMER_ITEM_ID(+) AND L.ORDER_QUANTITY_UOM = UOM.UOM_CODE(+) AND UOM.LANGUAGE(+) = userenv('LANG') ORDER BY LINE_NUMBER, SHIPMENT_NUMBER, NVL(OPTION_NUMBER,-1), NVL(COMPONENT_NUMBER,-1), NVL(SERVICE_NUMBER,-1)

View Text - HTML Formatted

SELECT L.LINE_ID
, L.ORG_ID
, L.HEADER_ID
, L.LINE_TYPE_ID
, OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(L.LINE_ID) LINE
, L.LINE_NUMBER
, DECODE(L.ITEM_IDENTIFIER_TYPE
, 'CUST'
, C.CUSTOMER_ITEM_NUMBER
, 'INT'
, ITEMSKFV.CONCATENATED_SEGMENTS
, NULL
, ITEMSKFV.CONCATENATED_SEGMENTS
, L.ORDERED_ITEM) ITEM_NUMBER
, DECODE(L.ITEM_IDENTIFIER_TYPE
, 'CUST'
, NVL(C.CUSTOMER_ITEM_DESC
, ITEMS.DESCRIPTION)
, 'INT'
, ITEMS.DESCRIPTION
, NULL
, ITEMS.DESCRIPTION
, NVL(REF.DESCRIPTION
, ITEMS.DESCRIPTION)) ITEM_DESCRIPTION
, L.REQUEST_DATE
, L.PROMISE_DATE
, L.SCHEDULE_ARRIVAL_DATE
, L.SCHEDULE_SHIP_DATE
, UOM.UNIT_OF_MEASURE_TL ORDER_QUANTITY_UOM
, DECODE(L.LINE_CATEGORY_CODE
, 'RETURN'
, (-1)*ORDERED_QUANTITY
, ORDERED_QUANTITY) ORDERED_QUANTITY
, L.TAX_EXEMPT_NUMBER
, L.CUST_PO_NUMBER
, L.SOLD_TO_ORG_ID
, L.SHIP_FROM_ORG_ID
, L.SHIP_TO_ORG_ID
, L.DELIVER_TO_ORG_ID
, L.INTMED_SHIP_TO_ORG_ID
, L.INVOICE_TO_ORG_ID
, L.SHIP_TO_CONTACT_ID
, L.DELIVER_TO_CONTACT_ID
, L.INTMED_SHIP_TO_CONTACT_ID
, L.INVOICE_TO_CONTACT_ID
, L.INVENTORY_ITEM_ID
, L.TAX_CODE
, L.TAX_RATE
, TO_CHAR(L.UNIT_LIST_PRICE
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE
, 30)) UNIT_LIST_PRICE
, TO_CHAR(L.UNIT_SELLING_PRICE
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE
, 30)) UNIT_SELLING_PRICE
, TO_CHAR(OE_OE_TOTALS_SUMMARY.GET_DISCOUNT(L.UNIT_LIST_PRICE
, L.UNIT_SELLING_PRICE)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE
, 30)) DISCOUNT
, L.TAX_VALUE
, L.ATO_LINE_ID
, L.CONFIGURATION_ID
, L.TOP_MODEL_LINE_ID
, L.LINK_TO_LINE_ID
, L.COMPONENT_SEQUENCE_ID
, L.COMPONENT_CODE
, L.ITEM_TYPE_CODE
, L.SHIPMENT_NUMBER
, L.OPTION_NUMBER
, L.COMPONENT_NUMBER
, L.OPTION_FLAG
, L.SALESREP_ID
, L.ORDERED_ITEM_ID
, L.ITEM_IDENTIFIER_TYPE
, L.CONFIG_HEADER_ID
, L.CONFIG_REV_NBR
, L.SHIPPING_INSTRUCTIONS
, L.PACKING_INSTRUCTIONS
, L.INVOICED_QUANTITY
, L.SERVICE_TXN_REASON_CODE
, L.SERVICE_TXN_COMMENTS
, L.SERVICE_DURATION
, L.SERVICE_PERIOD
, L.SERVICE_START_DATE
, L.SERVICE_END_DATE
, L.SERVICE_COTERMINATE_FLAG
, L.SERVICE_NUMBER
, L.SERVICE_REFERENCE_TYPE_CODE
, L.SERVICE_REFERENCE_LINE_ID
, L.SERVICE_REFERENCE_SYSTEM_ID
, AGR.NAME AGREEMENT_NAME
, AGR.REVISION AGREEMENT_VERSION
, L.BLANKET_NUMBER
, L.BLANKET_LINE_NUMBER
, BLANKET.VERSION_NUMBER BLANKET_VERSION_NUMBER
, INVRULE.NAME INVOICING_RULE_NAME
, TERM.NAME PAYMENT_TERMS
, TO_CHAR(OE_TOTALS_GRP.GET_ORDER_TOTAL(H.HEADER_ID
, LINE_ID
, 'ALL')
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE
, 30)) LINE_TOTAL
, TO_CHAR(OE_TOTALS_GRP.GET_ORDER_TOTAL(H.HEADER_ID
, LINE_ID
, 'LINES')
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE
, 30)) LINE_PRICE
, TO_CHAR(OE_TOTALS_GRP.GET_ORDER_TOTAL(H.HEADER_ID
, LINE_ID
, 'CHARGES')
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE
, 30)) LINE_CHARGES
, TO_CHAR(OE_TOTALS_GRP.GET_ORDER_TOTAL(H.HEADER_ID
, LINE_ID
, 'TAXES')
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( H.TRANSACTIONAL_CURR_CODE
, 30)) LINE_TAXES
, L.END_CUSTOMER_ID
, L.END_CUSTOMER_CONTACT_ID
, L.END_CUSTOMER_SITE_USE_ID
, L.IB_OWNER
, L.IB_INSTALLED_AT_LOCATION
, L.IB_CURRENT_LOCATION
, L.CHARGE_PERIODICITY_CODE
FROM OE_ORDER_HEADERS_ALL H
, OE_ORDER_LINES_ALL L
, RA_RULES INVRULE
, RA_TERMS_TL TERM
, OE_AGREEMENTS_TL AGR
, OE_BLANKET_HEADERS_ALL BLANKET
, MTL_SYSTEM_ITEMS_TL ITEMS
, MTL_SYSTEM_ITEMS_KFV ITEMSKFV
, MTL_CUSTOMER_ITEMS C
, MTL_CROSS_REFERENCES REF
, MTL_UNITS_OF_MEASURE_TL UOM
, OE_TRANSACTION_TYPES_ALL LT
WHERE L.ITEM_TYPE_CODE <> 'INCLUDED'
AND L.HEADER_ID = H.HEADER_ID
AND L.LINE_TYPE_ID = LT.TRANSACTION_TYPE_ID
AND LT.TRANSACTION_TYPE_CODE = 'LINE'
AND L.AGREEMENT_ID = AGR.AGREEMENT_ID (+)
AND AGR.LANGUAGE(+) = USERENV('LANG')
AND L.BLANKET_NUMBER = BLANKET.ORDER_NUMBER (+)
AND L.INVOICING_RULE_ID = INVRULE.RULE_ID (+)
AND L.PAYMENT_TERM_ID = TERM.TERM_ID (+)
AND TERM.LANGUAGE(+) = USERENV('LANG')
AND L.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID(+)
AND L.INVENTORY_ITEM_ID = ITEMSKFV.INVENTORY_ITEM_ID(+)
AND ITEMS.ORGANIZATION_ID = ITEMSKFV.ORGANIZATION_ID
AND ITEMS.LANGUAGE = USERENV('LANG')
AND OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
, L.ORG_ID) = ITEMS.ORGANIZATION_ID
AND L.ITEM_IDENTIFIER_TYPE = REF.CROSS_REFERENCE_TYPE(+)
AND L.ORDERED_ITEM = REF.CROSS_REFERENCE(+)
AND L.INVENTORY_ITEM_ID = REF.INVENTORY_ITEM_ID(+)
AND L.ORDERED_ITEM_ID = C.CUSTOMER_ITEM_ID(+)
AND L.ORDER_QUANTITY_UOM = UOM.UOM_CODE(+)
AND UOM.LANGUAGE(+) = USERENV('LANG') ORDER BY LINE_NUMBER
, SHIPMENT_NUMBER
, NVL(OPTION_NUMBER
, -1)
, NVL(COMPONENT_NUMBER
, -1)
, NVL(SERVICE_NUMBER
, -1)