View: IBE_ORDER_HEADER_V

Product:	IBE - iStore
Description:
Implementation/DBA Data:	APPS.IBE_ORDER_HEADER_V

View Text

SELECT OH.HEADER_ID
, OH.ORG_ID
, OH.SOURCE_DOCUMENT_TYPE_ID
, OH.CREATED_BY
, OH.SOURCE_DOCUMENT_ID
, OH.ORIG_SYS_DOCUMENT_REF
, OH.ORDER_NUMBER
, OH.ORDER_CATEGORY_CODE
, ORDTYPE.NAME ORDER_TYPE
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OH.HEADER_ID
, NULL
, 'ALL') ORDER_TOTAL
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OH.HEADER_ID
, NULL
, 'LINES') LINES_TOTAL
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OH.HEADER_ID
, NULL
, 'TAXES') TAXES_TOTAL
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OH.HEADER_ID
, NULL
, 'CHARGES') CHARGES_TOTAL
, OE_SYS_PARAMETERS.VALUE('INSTALLMENT_OPTIONS') INSTALLMENT_OPTIONS
, OE_SYS_PARAMETERS.VALUE('RECURRING_CHARGES') RECURRING_CHARGES
, OE_TOTALS_GRP.GET_PAYNOW_TOTAL(OH.HEADER_ID
, NULL
, 'ALL') PAYNOW_ORDER_TOTAL
, OE_TOTALS_GRP.GET_PAYNOW_TOTAL(OH.HEADER_ID
, NULL
, 'LINES') PAYNOW_LINES_TOTAL
, OE_TOTALS_GRP.GET_PAYNOW_TOTAL(OH.HEADER_ID
, NULL
, 'TAXES') PAYNOW_TAXES_TOTAL
, OE_TOTALS_GRP.GET_PAYNOW_TOTAL(OH.HEADER_ID
, NULL
, 'CHARGES') PAYNOW_CHARGES_TOTAL
, FLOW_STATUS_CODE ORDER_STATUS
, OE_LOOKUPS_FLOW_STATUS.MEANING
, OH.ORDERED_DATE
, OH.BOOKED_DATE
, OH.LAST_UPDATE_DATE
, OH.SOLD_TO_ORG_ID CUST_ACCOUNT_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))
, OH.TRANSACTIONAL_CURR_CODE
, OH.CUST_PO_NUMBER
, SUBSTR(OH.CREDIT_CARD_NUMBER
, (LENGTH(OH.CREDIT_CARD_NUMBER)-3)
, 4)
, OH.CREDIT_CARD_CODE
, OLKP2.MEANING
, OH.PAYMENT_TERM_ID
, TERM.NAME PAYMENT_TERM
, OH.PAYMENT_TYPE_CODE
, OLKP3.MEANING
, OH.AGREEMENT_ID
, OAT.NAME PRICING_AGREEMENT_NAME
, OH.INVOICE_TO_ORG_ID
, INV_PART.PARTY_ID
, INV_PART.PARTY_NAME
, OH.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
, OH.SHIP_TO_ORG_ID
, SHP_PART.PARTY_ID
, SHP_PART.PARTY_NAME
, OH.SHIP_TO_CONTACT_ID
, SHP_CONTACT.PARTY_NAME SHIP_TO_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
, OH.END_CUSTOMER_ID
, END_CUST_PARTY.PARTY_NAME
, OH.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
, OH.SHIPPING_METHOD_CODE
, OLKP4.MEANING SHIPPING_METHOD_DESC
, OH.FREIGHT_TERMS_CODE
, OLKP.MEANING FREIGHT_TERMS
, OH.SHIPMENT_PRIORITY_CODE
, SP_LOOKUP.MEANING SHIPMENT_PRIORITY_CODE_MNG
, OH.PARTIAL_SHIPMENTS_ALLOWED
, OH.REQUEST_DATE
, OH.TAX_EXEMPT_FLAG
, OH.SHIPPING_INSTRUCTIONS
, OH.PACKING_INSTRUCTIONS
, OH.CONTEXT
, OH.ATTRIBUTE1
, OH.ATTRIBUTE2
, OH.ATTRIBUTE3
, OH.ATTRIBUTE4
, OH.ATTRIBUTE5
, OH.ATTRIBUTE6
, OH.ATTRIBUTE7
, OH.ATTRIBUTE8
, OH.ATTRIBUTE9
, OH.ATTRIBUTE10
, OH.ATTRIBUTE11
, OH.ATTRIBUTE12
, OH.ATTRIBUTE13
, OH.ATTRIBUTE14
, OH.ATTRIBUTE15
, OH.GLOBAL_ATTRIBUTE_CATEGORY
, OH.GLOBAL_ATTRIBUTE1
, OH.GLOBAL_ATTRIBUTE2
, OH.GLOBAL_ATTRIBUTE3
, OH.GLOBAL_ATTRIBUTE4
, OH.GLOBAL_ATTRIBUTE5
, OH.GLOBAL_ATTRIBUTE6
, OH.GLOBAL_ATTRIBUTE7
, OH.GLOBAL_ATTRIBUTE8
, OH.GLOBAL_ATTRIBUTE9
, OH.GLOBAL_ATTRIBUTE10
, OH.GLOBAL_ATTRIBUTE11
, OH.GLOBAL_ATTRIBUTE12
, OH.GLOBAL_ATTRIBUTE13
, OH.GLOBAL_ATTRIBUTE14
, OH.GLOBAL_ATTRIBUTE15
, OH.GLOBAL_ATTRIBUTE16
, OH.GLOBAL_ATTRIBUTE17
, OH.GLOBAL_ATTRIBUTE18
, OH.GLOBAL_ATTRIBUTE19
, OH.GLOBAL_ATTRIBUTE20
, INV_ACCT_SITE.CUST_ACCOUNT_ID BILL_TO_ACCOUNT_ID
, INV_SITE_USE.CUST_ACCT_SITE_ID BILL_TO_ACCOUNT_SITE_ID
FROM OE_ORDER_HEADERS_ALL OH
, RA_TERMS_TL TERM
, HZ_CUST_ACCOUNTS SOLD_TO_ACCOUNT
, HZ_PARTIES SOLD_TO_PARTY
, IBE_OE_CONTACTS_V SOLD_TO_CONTACT
, HZ_PARTIES SHP_PART
, HZ_CUST_ACCOUNTS SHP_ACCOUNTS
, IBE_OE_CONTACTS_V SHP_CONTACT
, HZ_LOCATIONS SHIPADDR
, HZ_PARTY_SITES SHP_PARTY_SITE
, HZ_CUST_ACCT_SITES_ALL SHP_ACCT_SITE
, HZ_CUST_SITE_USES_ALL SHP_SITE_USE
, HZ_PARTIES INV_PART
, HZ_CUST_ACCOUNTS INV_ACCOUNTS
, IBE_OE_CONTACTS_V INV_CONTACT
, HZ_LOCATIONS INVADDR
, HZ_PARTY_SITES INV_PARTY_SITE
, HZ_CUST_ACCT_SITES_ALL INV_ACCT_SITE
, HZ_CUST_SITE_USES_ALL INV_SITE_USE
, 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_AGREEMENTS_TL OAT
, OE_LOOKUPS OLKP
, OE_LOOKUPS OLKP2
, OE_LOOKUPS OLKP3
, OE_SHIP_METHODS_V OLKP4
, OE_LOOKUPS SP_LOOKUP
, OE_LOOKUPS OE_LOOKUPS_FLOW_STATUS
, OE_TRANSACTION_TYPES_TL ORDTYPE
WHERE OH.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND TERM.LANGUAGE (+) = USERENV('LANG')
AND OH.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 OH.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_ACCOUNTS.CUST_ACCOUNT_ID(+)
AND SHP_ACCOUNTS.PARTY_ID = SHP_PART.PARTY_ID(+)
AND OH.SHIP_TO_CONTACT_ID = SHP_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OH.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_ACCOUNTS.CUST_ACCOUNT_ID(+)
AND INV_ACCOUNTS.PARTY_ID = INV_PART.PARTY_ID(+)
AND OH.INVOICE_TO_CONTACT_ID = INV_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OH.END_CUSTOMER_ID = END_CUST_ACCOUNTS.CUST_ACCOUNT_ID (+)
AND END_CUST_ACCOUNTS.PARTY_ID = END_CUST_PARTY.PARTY_ID(+)
AND OH.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 OH.END_CUSTOMER_CONTACT_ID = END_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OH.FREIGHT_TERMS_CODE = OLKP.LOOKUP_CODE(+)
AND OLKP.LOOKUP_TYPE (+)= 'FREIGHT_TERMS'
AND OH.CREDIT_CARD_CODE = OLKP2.LOOKUP_CODE(+)
AND OLKP2.LOOKUP_TYPE (+)= 'CREDIT_CARD'
AND OH.PAYMENT_TYPE_CODE = OLKP3.LOOKUP_CODE(+)
AND OLKP3.LOOKUP_TYPE (+)= 'PAYMENT TYPE'
AND SP_LOOKUP.LOOKUP_TYPE (+) = 'SHIPMENT_PRIORITY'
AND SP_LOOKUP.LOOKUP_CODE (+) = OH.SHIPMENT_PRIORITY_CODE
AND OH.FLOW_STATUS_CODE = OE_LOOKUPS_FLOW_STATUS.LOOKUP_CODE(+)
AND OE_LOOKUPS_FLOW_STATUS.LOOKUP_TYPE(+) = 'FLOW_STATUS'
AND OH.AGREEMENT_ID = OAT.AGREEMENT_ID(+)
AND OAT.LANGUAGE(+) = USERENV('LANG')
AND OH.SHIPPING_METHOD_CODE = OLKP4.LOOKUP_CODE(+)
AND OLKP4.LOOKUP_TYPE(+) = 'SHIP_METHOD'
AND OH.ORDER_TYPE_ID = ORDTYPE.TRANSACTION_TYPE_ID
AND ORDTYPE.LANGUAGE = USERENV('LANG')

Columns