View: OE_SCH_ORDER_LINES_V

Product:	ONT - Order Management
Description:
Implementation/DBA Data:	APPS.OE_SCH_ORDER_LINES_V

View Text

SELECT L.ROWID ROW_ID
, L.LINE_ID
, L.ORG_ID
, L.HEADER_ID
, L.LINE_TYPE_ID
, L.LINE_NUMBER
, L.ORDERED_ITEM
, L.REQUEST_DATE
, L.PROMISE_DATE
, L.SCHEDULE_ARRIVAL_DATE
, L.SCHEDULE_SHIP_DATE
, L.ORDER_QUANTITY_UOM
, L.PRICING_QUANTITY
, L.PRICING_QUANTITY_UOM
, L.CANCELLED_QUANTITY
, L.SHIPPED_QUANTITY
, L.ORDERED_QUANTITY
, L.FULFILLED_QUANTITY
, L.SHIPPING_QUANTITY
, L.SHIPPING_QUANTITY_UOM
, L.DELIVERY_LEAD_TIME
, L.AUTO_SELECTED_QUANTITY
, L.TAX_EXEMPT_FLAG
, L.TAX_EXEMPT_NUMBER
, L.TAX_EXEMPT_REASON_CODE
, L.CUST_PO_NUMBER
, L.SHIP_TOLERANCE_ABOVE
, L.SHIP_TOLERANCE_BELOW
, L.DEMAND_BUCKET_TYPE_CODE
, L.RLA_SCHEDULE_TYPE_CODE
, L.CUSTOMER_DOCK_CODE
, L.CUSTOMER_JOB
, L.CUSTOMER_PRODUCTION_LINE
, L.CUST_MODEL_SERIAL_NUMBER
, L.PROJECT_ID
, L.TASK_ID
, L.END_ITEM_UNIT_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_DATE
, L.TAX_CODE
, L.TAX_RATE
, L.DEMAND_CLASS_CODE
, L.SCHEDULE_STATUS_CODE
, L.SOURCE_TYPE_CODE
, L.PRICE_LIST_ID
, L.PRICING_DATE
, L.SHIPMENT_NUMBER
, L.AGREEMENT_ID
, L.SHIPMENT_PRIORITY_CODE
, L.SHIPPING_METHOD_CODE
, L.FREIGHT_TERMS_CODE
, L.FREIGHT_CARRIER_CODE
, L.FOB_POINT_CODE
, L.TAX_POINT_CODE
, L.PAYMENT_TERM_ID
, L.INVOICING_RULE_ID
, L.ACCOUNTING_RULE_ID
, L.ACCOUNTING_RULE_DURATION
, L.SOURCE_DOCUMENT_TYPE_ID
, L.ORIG_SYS_DOCUMENT_REF
, L.SOURCE_DOCUMENT_ID
, L.ORIG_SYS_LINE_REF
, L.SOURCE_DOCUMENT_LINE_ID
, L.REFERENCE_LINE_ID
, L.REFERENCE_TYPE
, L.REFERENCE_HEADER_ID
, L.ITEM_REVISION
, L.LINE_CATEGORY_CODE
, L.CUSTOMER_TRX_LINE_ID
, L.RETURN_CONTEXT
, L.RETURN_ATTRIBUTE1
, L.RETURN_ATTRIBUTE2
, L.RETURN_ATTRIBUTE3
, L.RETURN_ATTRIBUTE4
, L.RETURN_ATTRIBUTE5
, L.RETURN_ATTRIBUTE6
, L.RETURN_ATTRIBUTE7
, L.RETURN_ATTRIBUTE8
, L.RETURN_ATTRIBUTE9
, L.RETURN_ATTRIBUTE10
, L.RETURN_ATTRIBUTE11
, L.RETURN_ATTRIBUTE12
, L.RETURN_ATTRIBUTE13
, L.RETURN_ATTRIBUTE14
, L.RETURN_ATTRIBUTE15
, L.REFERENCE_CUSTOMER_TRX_LINE_ID REFERENCE_CUSTOMER_TRX_LINE_ID
, L.UNIT_SELLING_PRICE
, L.UNIT_LIST_PRICE
, L.TAX_VALUE
, L.CONTEXT
, L.ATTRIBUTE1
, L.ATTRIBUTE2
, L.ATTRIBUTE3
, L.ATTRIBUTE4
, L.ATTRIBUTE5
, L.ATTRIBUTE6
, L.ATTRIBUTE7
, L.ATTRIBUTE8
, L.ATTRIBUTE9
, L.ATTRIBUTE10
, L.ATTRIBUTE11
, L.ATTRIBUTE12
, L.ATTRIBUTE13
, L.ATTRIBUTE14
, L.ATTRIBUTE15
, L.GLOBAL_ATTRIBUTE_CATEGORY
, L.GLOBAL_ATTRIBUTE1
, L.GLOBAL_ATTRIBUTE2
, L.GLOBAL_ATTRIBUTE3
, L.GLOBAL_ATTRIBUTE4
, L.GLOBAL_ATTRIBUTE5
, L.GLOBAL_ATTRIBUTE6
, L.GLOBAL_ATTRIBUTE7
, L.GLOBAL_ATTRIBUTE8
, L.GLOBAL_ATTRIBUTE9
, L.GLOBAL_ATTRIBUTE10
, L.GLOBAL_ATTRIBUTE11
, L.GLOBAL_ATTRIBUTE12
, L.GLOBAL_ATTRIBUTE13
, L.GLOBAL_ATTRIBUTE14
, L.GLOBAL_ATTRIBUTE15
, L.GLOBAL_ATTRIBUTE16
, L.GLOBAL_ATTRIBUTE17
, L.GLOBAL_ATTRIBUTE18
, L.GLOBAL_ATTRIBUTE19
, L.GLOBAL_ATTRIBUTE20
, L.PRICING_CONTEXT
, L.PRICING_ATTRIBUTE1
, L.PRICING_ATTRIBUTE2
, L.PRICING_ATTRIBUTE3
, L.PRICING_ATTRIBUTE4
, L.PRICING_ATTRIBUTE5
, L.PRICING_ATTRIBUTE6
, L.PRICING_ATTRIBUTE7
, L.PRICING_ATTRIBUTE8
, L.PRICING_ATTRIBUTE9
, L.PRICING_ATTRIBUTE10
, L.INDUSTRY_CONTEXT
, L.INDUSTRY_ATTRIBUTE1
, L.INDUSTRY_ATTRIBUTE2
, L.INDUSTRY_ATTRIBUTE3
, L.INDUSTRY_ATTRIBUTE4
, L.INDUSTRY_ATTRIBUTE5
, L.INDUSTRY_ATTRIBUTE6
, L.INDUSTRY_ATTRIBUTE7
, L.INDUSTRY_ATTRIBUTE8
, L.INDUSTRY_ATTRIBUTE9
, L.INDUSTRY_ATTRIBUTE10
, L.INDUSTRY_ATTRIBUTE11
, L.INDUSTRY_ATTRIBUTE13
, L.INDUSTRY_ATTRIBUTE12
, L.INDUSTRY_ATTRIBUTE14
, L.INDUSTRY_ATTRIBUTE15
, L.INDUSTRY_ATTRIBUTE16
, L.INDUSTRY_ATTRIBUTE17
, L.INDUSTRY_ATTRIBUTE18
, L.INDUSTRY_ATTRIBUTE19
, L.INDUSTRY_ATTRIBUTE20
, L.INDUSTRY_ATTRIBUTE21
, L.INDUSTRY_ATTRIBUTE22
, L.INDUSTRY_ATTRIBUTE23
, L.INDUSTRY_ATTRIBUTE24
, L.INDUSTRY_ATTRIBUTE25
, L.INDUSTRY_ATTRIBUTE26
, L.INDUSTRY_ATTRIBUTE27
, L.INDUSTRY_ATTRIBUTE28
, L.INDUSTRY_ATTRIBUTE29
, L.INDUSTRY_ATTRIBUTE30
, L.TP_CONTEXT
, L.TP_ATTRIBUTE1
, L.TP_ATTRIBUTE2
, L.TP_ATTRIBUTE3
, L.TP_ATTRIBUTE4
, L.TP_ATTRIBUTE5
, L.TP_ATTRIBUTE6
, L.TP_ATTRIBUTE7
, L.TP_ATTRIBUTE8
, L.TP_ATTRIBUTE9
, L.TP_ATTRIBUTE10
, L.TP_ATTRIBUTE11
, L.TP_ATTRIBUTE12
, L.TP_ATTRIBUTE13
, L.TP_ATTRIBUTE14
, L.TP_ATTRIBUTE15
, L.CREATION_DATE
, L.CREATED_BY
, L.LAST_UPDATE_DATE
, L.LAST_UPDATED_BY
, L.LAST_UPDATE_LOGIN
, L.PROGRAM_APPLICATION_ID
, L.PROGRAM_ID
, L.PROGRAM_UPDATE_DATE
, L.REQUEST_ID
, L.SHIP_MODEL_COMPLETE_FLAG
, L.ATO_LINE_ID
, L.CONFIGURATION_ID
, L.TOP_MODEL_LINE_ID
, L.LINK_TO_LINE_ID
, L.EXPLOSION_DATE
, L.COMPONENT_SEQUENCE_ID
, L.COMPONENT_CODE
, L.CONFIG_DISPLAY_SEQUENCE
, L.SORT_ORDER
, L.ITEM_TYPE_CODE
, L.OPTION_NUMBER
, L.COMPONENT_NUMBER
, L.MODEL_GROUP_NUMBER
, L.OPTION_FLAG
, L.DEP_PLAN_REQUIRED_FLAG
, L.VISIBLE_DEMAND_FLAG
, L.ACTUAL_ARRIVAL_DATE
, L.ACTUAL_SHIPMENT_DATE
, L.EARLIEST_ACCEPTABLE_DATE
, L.LATEST_ACCEPTABLE_DATE
, LT.NAME LINE_TYPE
, PARTY.PARTY_NAME SOLD_TO
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, SHIP_FROM_ORG.ORGANIZATION_CODE SHIP_FROM
, L.SUBINVENTORY SUBINVENTORY
, SHIP_SU.LOCATION SHIP_TO
, SHIP_SU.LOCATION SHIP_TO_LOCATION
, SHIP_LOC.ADDRESS1 SHIP_TO_ADDRESS1
, SHIP_LOC.ADDRESS2 SHIP_TO_ADDRESS2
, SHIP_LOC.ADDRESS3 SHIP_TO_ADDRESS3
, SHIP_LOC.ADDRESS4 SHIP_TO_ADDRESS4
, DECODE(SHIP_LOC.CITY
, NULL
, NULL
, SHIP_LOC.CITY || '
, ') || DECODE(SHIP_LOC.STATE
, NULL
, NULL
, SHIP_LOC.STATE || '
, ') || DECODE(SHIP_LOC.POSTAL_CODE
, NULL
, NULL
, SHIP_LOC.POSTAL_CODE || '
, ') || DECODE(SHIP_LOC.COUNTRY
, NULL
, NULL
, SHIP_LOC.COUNTRY) SHIP_TO_ADDRESS5
, SHIP_PARTY.PERSON_LAST_NAME || DECODE(SHIP_PARTY.PERSON_FIRST_NAME
, NULL
, NULL
, '
, ' || SHIP_PARTY.PERSON_FIRST_NAME) || DECODE(SHIP_ARL.MEANING
, NULL
, NULL
, ' '||SHIP_ARL.MEANING) SHIP_TO_CONTACT
, H.ORDER_NUMBER ORDER_NUMBER
, H.VERSION_NUMBER VERSION_NUMBER
, H.ORDER_TYPE_ID
, OT.NAME ORDER_TYPE
, H.ORDERED_DATE ORDERED_DATE
, H.ROWID HEADER_ROW_ID
, H.ORDER_SOURCE_ID
, L.SALESREP_ID
, L.RETURN_REASON_CODE
, L.SPLIT_FROM_LINE_ID
, L.LINE_SET_ID
, L.SPLIT_BY
, L.CUST_PRODUCTION_SEQ_NUM
, L.AUTHORIZED_TO_SHIP_FLAG
, L.VEH_CUS_ITEM_CUM_KEY_ID
, L.INVOICE_INTERFACE_STATUS_CODE
, L.OVER_SHIP_REASON_CODE
, L.OVER_SHIP_RESOLVED_FLAG
, L.SHIP_SET_ID
, L.ARRIVAL_SET_ID
, L.ORDERED_ITEM_ID
, L.ITEM_IDENTIFIER_TYPE
, L.COMMITMENT_ID
, L.SHIPPING_INTERFACED_FLAG
, L.CREDIT_INVOICE_LINE_ID
, L.FIRST_ACK_CODE
, L.FIRST_ACK_DATE
, L.LAST_ACK_CODE
, L.LAST_ACK_DATE
, L.PLANNING_PRIORITY
, L.CONFIG_HEADER_ID
, L.CONFIG_REV_NBR
, L.BOOKED_FLAG
, L.CANCELLED_FLAG
, L.OPEN_FLAG
, L.SOLD_FROM_ORG_ID
, L.FULFILLED_FLAG
, 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.UNIT_SELLING_PERCENT
, L.UNIT_LIST_PERCENT
, L.UNIT_PERCENT_BASE_PRICE
, L.SERVICE_NUMBER
, L.SERVICE_REFERENCE_TYPE_CODE
, L.SERVICE_REFERENCE_LINE_ID
, L.SERVICE_REFERENCE_SYSTEM_ID
, L.FLOW_STATUS_CODE
, L.MODEL_REMNANT_FLAG
, L.FULFILLMENT_METHOD_CODE
, L.CALCULATE_PRICE_FLAG
, L.REVENUE_AMOUNT
, L.FULFILLMENT_DATE
, L.PREFERRED_GRADE
, L.ORDERED_QUANTITY2
, L.ORDERED_QUANTITY_UOM2
, L.SHIPPED_QUANTITY2
, L.CANCELLED_QUANTITY2
, L.SHIPPING_QUANTITY2
, L.SHIPPING_QUANTITY_UOM2
, L.FULFILLED_QUANTITY2
, L.UPGRADED_FLAG
, L.LOCK_CONTROL
, L.UNIT_SELLING_PRICE_PER_PQTY
, L.UNIT_LIST_PRICE_PER_PQTY
, L.CUSTOMER_LINE_NUMBER
, L.CUSTOMER_SHIPMENT_NUMBER
, L.CUSTOMER_ITEM_NET_PRICE
, L.CUSTOMER_PAYMENT_TERM_ID
, L.ORIGINAL_INVENTORY_ITEM_ID
, L.ORIGINAL_ITEM_IDENTIFIER_TYPE
, L.ORIGINAL_ORDERED_ITEM_ID
, L.ORIGINAL_ORDERED_ITEM
, L.ITEM_SUBSTITUTION_TYPE_CODE
, L.LATE_DEMAND_PENALTY_FACTOR
, L.OVERRIDE_ATP_DATE_CODE
, L.UNIT_COST
, TO_NUMBER(DECODE(L.UNIT_COST
, NULL
, NULL
, DECODE(L.UNIT_SELLING_PRICE
, NULL
, NULL
, (L.UNIT_SELLING_PRICE - L.UNIT_COST)))) MARGIN
, TO_NUMBER(DECODE(L.UNIT_COST
, NULL
, NULL
, DECODE(L.UNIT_SELLING_PRICE
, NULL
, NULL
, DECODE(OE_SYS_PARAMETERS.VALUE_WNPS('COMPUTE_MARGIN'
, L.ORG_ID)
, 'N'
, NULL
, NULL
, NULL
, 'C'
, DECODE(L.UNIT_COST
, 0
, 0
, ROUND((L.UNIT_SELLING_PRICE - L.UNIT_COST)/L.UNIT_COST * 100
, 2))
, 'P'
, DECODE(L.UNIT_SELLING_PRICE
, 0
, 0
, ROUND((L.UNIT_SELLING_PRICE - L.UNIT_COST)/L.UNIT_SELLING_PRICE * 100
, 2)))))) MARGIN_PERCENT
, L.USER_ITEM_DESCRIPTION
, SP_LOOKUP.MEANING SHIPMENT_PRIORITY
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.CONCATENATED_SEGMENTS INVENTORY_ITEM
, OFCL.MEANING DEMAND_CLASS
, H.ORDER_DATE_TYPE_CODE
, L.ITEM_RELATIONSHIP_TYPE
, L.ATTRIBUTE16
, L.ATTRIBUTE17
, L.ATTRIBUTE18
, L.ATTRIBUTE19
, L.ATTRIBUTE20
, L.FIRM_DEMAND_FLAG
, L.EARLIEST_SHIP_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_PARAMETERS SHIP_FROM_ORG
, HZ_CUST_SITE_USES_ALL SHIP_SU
, HZ_PARTY_SITES SHIP_PS
, HZ_LOCATIONS SHIP_LOC
, HZ_CUST_ACCT_SITES_ALL SHIP_CAS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_ACCOUNT_ROLES SHIP_ROLES
, HZ_PARTIES SHIP_PARTY
, HZ_RELATIONSHIPS SHIP_REL
, HZ_CUST_ACCOUNTS SHIP_ACCT
, AR_LOOKUPS SHIP_ARL
, OE_ORDER_HEADERS H
, OE_ORDER_LINES_ALL L
, OE_TRANSACTION_TYPES_TL LT
, OE_LOOKUPS SP_LOOKUP
, MTL_SYSTEM_ITEMS_VL MSI
, OE_FND_COMMON_LOOKUPS_V OFCL
, OE_TRANSACTION_TYPES_TL OT
WHERE L.LINE_TYPE_ID = LT.TRANSACTION_TYPE_ID
AND LT.LANGUAGE = USERENV('LANG')
AND L.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND L.SHIP_FROM_ORG_ID = SHIP_FROM_ORG.ORGANIZATION_ID (+)
AND L.SHIP_TO_ORG_ID = SHIP_SU.SITE_USE_ID(+)
AND SHIP_SU.CUST_ACCT_SITE_ID= SHIP_CAS.CUST_ACCT_SITE_ID(+)
AND SHIP_CAS.PARTY_SITE_ID = SHIP_PS.PARTY_SITE_ID(+)
AND SHIP_LOC.LOCATION_ID(+) = SHIP_PS.LOCATION_ID
AND L.SHIP_TO_CONTACT_ID = SHIP_ROLES.CUST_ACCOUNT_ROLE_ID(+) AND SHIP_ROLES.PARTY_ID = SHIP_REL.PARTY_ID(+)
AND SHIP_ROLES.ROLE_TYPE(+) = 'CONTACT'
AND SHIP_REL.SUBJECT_ID = SHIP_PARTY.PARTY_ID(+)
AND SHIP_ROLES.CUST_ACCOUNT_ID = SHIP_ACCT.CUST_ACCOUNT_ID(+)
AND NVL(SHIP_REL.OBJECT_ID
, -1) = NVL(SHIP_ACCT.PARTY_ID
, -1)
AND SHIP_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SHIP_ARL.LOOKUP_CODE(+) = SHIP_PARTY.PERSON_PRE_NAME_ADJUNCT
AND L.HEADER_ID = H.HEADER_ID
AND SP_LOOKUP.LOOKUP_TYPE (+) = 'SHIPMENT_PRIORITY'
AND SP_LOOKUP.LOOKUP_CODE (+) = L.SHIPMENT_PRIORITY_CODE
AND MSI.INVENTORY_ITEM_ID(+) = L.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID (+)= OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
, L.ORG_ID)
AND OFCL.LOOKUP_TYPE (+)= 'DEMAND_CLASS'
AND OFCL.LOOKUP_CODE(+) = L.DEMAND_CLASS_CODE
AND H.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID
AND OT.LANGUAGE = USERENV('LANG')
AND SHIP_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SHIP_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'

Columns