View: IBE_ORDER_LINE_DTL_V

Product:	IBE - iStore
Description:	Stores information about details of an order line
Implementation/DBA Data:	APPS.IBE_ORDER_LINE_DTL_V

View Text

SELECT OH.HEADER_ID
, OL.LINE_ID
, OH.ORDER_NUMBER
, OL.LINE_NUMBER
, OL.TOP_MODEL_LINE_ID
, OL.LINK_TO_LINE_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.ATTRIBUTE7 PRODUCT_DESCRIPTION
, MSI.SEGMENT6 LICENSING_UNIT
, OL.PRICING_ATTRIBUTE10
, OL.ITEM_TYPE_CODE
, OL.ORDERED_QUANTITY
, OL.SHIPPED_QUANTITY
, OL.CANCELLED_QUANTITY
, OL.FULFILLED_QUANTITY
, OL.ORDER_QUANTITY_UOM
, MSIT.UNIT_OF_MEASURE_TL UOM_DESC
, OL.UNIT_LIST_PRICE
, OL.UNIT_SELLING_PRICE
, OL.REQUEST_DATE
, OL.PROMISE_DATE
, OL.SCHEDULE_SHIP_DATE
, OL.FLOW_STATUS_CODE
, OEL.MEANING LINE_STATUS
, OL.SHIPMENT_NUMBER
, OL.OPTION_NUMBER
, OL.COMPONENT_NUMBER
, OL.SERVICE_NUMBER
, OL.SHIPPING_METHOD_CODE REQ_SHIP_METHOD_CODE
, OLKP2.MEANING REQ_SHIP_METHOD_DESC
, OL.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE
, OLKP1.MEANING FREIGHT_TERMS_DESC
, OL.AGREEMENT_ID
, OAV.NAME AGREEMENT_NAME
, OAV.NAME||'
, '||OAV.AGREEMENT_NUM||DECODE(OAV.AGREEMENT_NUM
, NULL
, ''
, '
, ')||OAV.REVISION FULL_AGREEMENT_NAME
, OL.PAYMENT_TERM_ID
, TERM.NAME PAYMENT_TERMS
, OH.SOLD_TO_ORG_ID
, SOLD_TO_ACCOUNT.PARTY_ID
, SOLD_TO_PARTY.PARTY_NAME
, OH.SOLD_TO_CONTACT_ID
, SOLD_TO_CONTACT.PARTY_NAME
, IBE_ORDER_UTIL_PVT.GET_PHONE_EMAIL(DECODE(SOLD_TO_PARTY.PARTY_TYPE
, 'PERSON'
, SOLD_TO_ACCOUNT.PARTY_ID
, SOLD_TO_CONTACT.REL_PARTY_ID))
, OL.INVOICE_TO_ORG_ID
, INV_CUST_PARTY.PARTY_NAME
, INV_ACCT_SITE.CUST_ACCOUNT_ID
, OL.INVOICE_TO_CONTACT_ID
, INV_CONTACT.PARTY_NAME
, INVADDR.CITY || ' ' || INVADDR.STATE BILL_TO_LOCATION
, INVADDR.ADDRESS1
, INVADDR.ADDRESS2
, INVADDR.ADDRESS3
, INVADDR.ADDRESS4
, INVADDR.ADDRESS_LINES_PHONETIC
, INVADDR.CITY
, INVADDR.STATE
, INVADDR.POSTAL_CODE
, INVADDR.COUNTY
, INVADDR.PROVINCE
, INVADDR.COUNTRY
, NULL
, OL.SHIP_TO_ORG_ID
, SHP_CUST_PARTY.PARTY_NAME
, SHP_ACCT_SITE.CUST_ACCOUNT_ID
, OL.SHIP_TO_CONTACT_ID
, SHP_CONTACT.PARTY_NAME
, SHIPADDR.CITY || ' ' || SHIPADDR.STATE SHIP_TO_LOCATION
, SHIPADDR.ADDRESS1
, SHIPADDR.ADDRESS2
, SHIPADDR.ADDRESS3
, SHIPADDR.ADDRESS4
, SHIPADDR.ADDRESS_LINES_PHONETIC
, SHIPADDR.CITY
, SHIPADDR.STATE
, SHIPADDR.POSTAL_CODE
, SHIPADDR.COUNTY
, SHIPADDR.PROVINCE
, SHIPADDR.COUNTRY
, NULL
, OL.END_CUSTOMER_ID
, END_CUST_PARTY.PARTY_NAME
, OL.END_CUSTOMER_CONTACT_ID
, END_CONTACT.PARTY_NAME
, ENDADDR.CITY || ' ' || ENDADDR.STATE END_LOCATION
, ENDADDR.ADDRESS1
, ENDADDR.ADDRESS2
, ENDADDR.ADDRESS3
, ENDADDR.ADDRESS4
, ENDADDR.ADDRESS_LINES_PHONETIC
, ENDADDR.CITY
, ENDADDR.STATE
, ENDADDR.POSTAL_CODE
, ENDADDR.COUNTY
, ENDADDR.PROVINCE
, ENDADDR.COUNTRY
, OL.SHIPPING_INSTRUCTIONS
, OL.PACKING_INSTRUCTIONS
, OL.COMMITMENT_ID
, RCT.TRX_NUMBER COMMITMENT_NAME
, OL.CUST_PO_NUMBER
, OL.SHIPMENT_PRIORITY_CODE
, SP_LOOKUP.MEANING SHIPMENT_PRIORITY_CODE_MNG
, OL.CONTEXT
, OL.ATTRIBUTE1
, OL.ATTRIBUTE2
, OL.ATTRIBUTE3
, OL.ATTRIBUTE4
, OL.ATTRIBUTE5
, OL.ATTRIBUTE6
, OL.ATTRIBUTE7
, OL.ATTRIBUTE8
, OL.ATTRIBUTE9
, OL.ATTRIBUTE10
, OL.ATTRIBUTE11
, OL.ATTRIBUTE12
, OL.ATTRIBUTE13
, OL.ATTRIBUTE14
, OL.ATTRIBUTE15
, OH.ORG_ID ORG_ID
FROM OE_ORDER_LINES_ALL OL
, OE_ORDER_HEADERS_ALL OH
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_UNITS_OF_MEASURE_TL MSIT
, OE_LOOKUPS OEL
, OE_LOOKUPS OLKP1
, OE_SHIP_METHODS_V OLKP2
, RA_TERMS_TL TERM
, OE_AGREEMENTS_VL OAV
, RA_CUSTOMER_TRX_ALL RCT
, HZ_CUST_ACCOUNTS SOLD_TO_ACCOUNT
, HZ_PARTIES SOLD_TO_PARTY
, IBE_OE_CONTACTS_V SOLD_TO_CONTACT
, HZ_CUST_SITE_USES_ALL SHP_SITE_USE
, HZ_CUST_ACCT_SITES_ALL SHP_ACCT_SITE
, HZ_PARTY_SITES SHP_PARTY_SITE
, HZ_LOCATIONS SHIPADDR
, HZ_CUST_ACCOUNTS SHP_CUST
, HZ_PARTIES SHP_CUST_PARTY
, IBE_OE_CONTACTS_V SHP_CONTACT
, HZ_CUST_SITE_USES_ALL INV_SITE_USE
, HZ_CUST_ACCT_SITES_ALL INV_ACCT_SITE
, HZ_PARTY_SITES INV_PARTY_SITE
, HZ_LOCATIONS INVADDR
, HZ_CUST_ACCOUNTS INV_CUST
, HZ_PARTIES INV_CUST_PARTY
, IBE_OE_CONTACTS_V INV_CONTACT
, HZ_PARTIES END_CUST_PARTY
, HZ_CUST_ACCOUNTS END_CUST_ACCOUNTS
, IBE_OE_CONTACTS_V END_CONTACT
, HZ_LOCATIONS ENDADDR
, HZ_PARTY_SITES END_PARTY_SITE
, HZ_CUST_ACCT_SITES_ALL END_ACCT_SITE
, HZ_CUST_SITE_USES_ALL END_SITE_USE
, OE_LOOKUPS SP_LOOKUP
WHERE OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OE_PROFILE.VALUE('OE_ORGANIZATION_ID'
, OL.ORG_ID)
AND OL.HEADER_ID = OH.HEADER_ID
AND OL.ORDER_QUANTITY_UOM = MSIT.UOM_CODE
AND MSIT.LANGUAGE = USERENV('LANG')
AND OEL.LOOKUP_CODE(+) = OL.FLOW_STATUS_CODE
AND OEL.LOOKUP_TYPE = 'LINE_FLOW_STATUS'
AND OL.FREIGHT_TERMS_CODE = OLKP1.LOOKUP_CODE(+)
AND OLKP1.LOOKUP_TYPE (+) = 'FREIGHT_TERMS'
AND OL.SHIPPING_METHOD_CODE = OLKP2.LOOKUP_CODE(+)
AND OLKP2.LOOKUP_TYPE (+) = 'SHIP_METHOD'
AND OL.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND TERM.LANGUAGE (+) = USERENV('LANG')
AND OL.AGREEMENT_ID = OAV.AGREEMENT_ID(+)
AND OL.COMMITMENT_ID = RCT.CUSTOMER_TRX_ID(+)
AND OL.SOLD_TO_ORG_ID = SOLD_TO_ACCOUNT.CUST_ACCOUNT_ID
AND SOLD_TO_ACCOUNT.PARTY_ID = SOLD_TO_PARTY.PARTY_ID
AND OH.SOLD_TO_CONTACT_ID = SOLD_TO_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OL.SHIP_TO_ORG_ID = SHP_SITE_USE.SITE_USE_ID(+)
AND SHP_SITE_USE.CUST_ACCT_SITE_ID = SHP_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND SHP_ACCT_SITE.PARTY_SITE_ID = SHP_PARTY_SITE.PARTY_SITE_ID(+)
AND SHP_PARTY_SITE.LOCATION_ID = SHIPADDR.LOCATION_ID(+)
AND SHP_ACCT_SITE.CUST_ACCOUNT_ID = SHP_CUST.CUST_ACCOUNT_ID(+)
AND SHP_CUST.PARTY_ID = SHP_CUST_PARTY.PARTY_ID(+)
AND OL.SHIP_TO_CONTACT_ID = SHP_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OL.INVOICE_TO_ORG_ID = INV_SITE_USE.SITE_USE_ID(+)
AND INV_SITE_USE.CUST_ACCT_SITE_ID = INV_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND INV_ACCT_SITE.PARTY_SITE_ID = INV_PARTY_SITE.PARTY_SITE_ID(+)
AND INV_PARTY_SITE.LOCATION_ID = INVADDR.LOCATION_ID(+)
AND INV_ACCT_SITE.CUST_ACCOUNT_ID = INV_CUST.CUST_ACCOUNT_ID(+)
AND INV_CUST.PARTY_ID = INV_CUST_PARTY.PARTY_ID(+)
AND OL.INVOICE_TO_CONTACT_ID = INV_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OL.END_CUSTOMER_ID = END_CUST_ACCOUNTS.CUST_ACCOUNT_ID (+)
AND END_CUST_ACCOUNTS.PARTY_ID = END_CUST_PARTY.PARTY_ID(+)
AND OL.END_CUSTOMER_SITE_USE_ID = END_SITE_USE.SITE_USE_ID(+)
AND END_SITE_USE.CUST_ACCT_SITE_ID = END_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND END_ACCT_SITE.PARTY_SITE_ID = END_PARTY_SITE.PARTY_SITE_ID(+)
AND END_PARTY_SITE.LOCATION_ID = ENDADDR.LOCATION_ID(+)
AND OL.END_CUSTOMER_CONTACT_ID = END_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OL.SHIPMENT_PRIORITY_CODE = SP_LOOKUP.LOOKUP_CODE (+)
AND SP_LOOKUP.LOOKUP_TYPE (+) = 'SHIPMENT_PRIORITY'

Columns