View: OE_ORDER_HEADERS_HIST_V

Product:	ONT - Order Management
Description:	This view is used to retrieve the Order History. This is used by the history form.
Implementation/DBA Data:	APPS.OE_ORDER_HEADERS_HIST_V

View Text

SELECT H.HIST_CREATED_BY
, H.RESPONSIBILITY_ID
, H.HIST_CREATION_DATE
, H.HIST_COMMENTS
, H.HIST_TYPE_CODE
, H.REASON_CODE
, H.PRICE_REQUEST_CODE
, H.HEADER_ID
, H.ORG_ID
, H.ORDER_TYPE_ID
, H.ORDER_NUMBER
, H.VERSION_NUMBER
, H.ORDER_SOURCE_ID
, H.SOURCE_DOCUMENT_TYPE_ID
, H.ORIG_SYS_DOCUMENT_REF
, H.SOURCE_DOCUMENT_ID
, H.ORDERED_DATE
, H.REQUEST_DATE
, H.PRICING_DATE
, H.SHIPMENT_PRIORITY_CODE
, H.DEMAND_CLASS_CODE
, H.PRICE_LIST_ID
, H.TAX_EXEMPT_FLAG
, H.TAX_EXEMPT_NUMBER
, H.TAX_EXEMPT_REASON_CODE
, H.CONVERSION_RATE
, H.CONVERSION_RATE_DATE
, H.CONVERSION_TYPE_CODE
, H.PARTIAL_SHIPMENTS_ALLOWED
, H.SHIP_TOLERANCE_ABOVE
, H.SHIP_TOLERANCE_BELOW
, H.TRANSACTIONAL_CURR_CODE
, H.AGREEMENT_ID
, H.TAX_POINT_CODE
, H.CUST_PO_NUMBER
, H.INVOICING_RULE_ID
, H.ACCOUNTING_RULE_ID
, H.ACCOUNTING_RULE_DURATION
, H.PAYMENT_TERM_ID
, H.SHIPPING_METHOD_CODE
, H.FOB_POINT_CODE
, H.FREIGHT_TERMS_CODE
, H.SOLD_TO_ORG_ID
, H.SOLD_TO_PHONE_ID
, H.SHIP_FROM_ORG_ID
, H.SHIP_TO_ORG_ID
, H.INVOICE_TO_ORG_ID
, H.DELIVER_TO_ORG_ID
, H.SOLD_TO_CONTACT_ID
, H.SHIP_TO_CONTACT_ID
, H.INVOICE_TO_CONTACT_ID
, H.DELIVER_TO_CONTACT_ID
, H.CREATION_DATE
, H.CREATED_BY
, H.LAST_UPDATE_DATE
, H.LAST_UPDATED_BY
, H.LAST_UPDATE_LOGIN
, H.EXPIRATION_DATE
, H.REQUEST_ID
, H.PROGRAM_APPLICATION_ID
, H.PROGRAM_ID
, H.PROGRAM_UPDATE_DATE
, H.CONTEXT
, H.ATTRIBUTE1
, H.ATTRIBUTE2
, H.ATTRIBUTE3
, H.ATTRIBUTE4
, H.ATTRIBUTE5
, H.ATTRIBUTE6
, H.ATTRIBUTE7
, H.ATTRIBUTE8
, H.ATTRIBUTE9
, H.ATTRIBUTE10
, H.ATTRIBUTE11
, H.ATTRIBUTE12
, H.ATTRIBUTE13
, H.ATTRIBUTE14
, H.ATTRIBUTE15
, H.ATTRIBUTE16
, H.ATTRIBUTE17
, H.ATTRIBUTE18
, H.ATTRIBUTE19
, H.ATTRIBUTE20
, H.GLOBAL_ATTRIBUTE_CATEGORY
, H.GLOBAL_ATTRIBUTE1
, H.GLOBAL_ATTRIBUTE2
, H.GLOBAL_ATTRIBUTE3
, H.GLOBAL_ATTRIBUTE4
, H.GLOBAL_ATTRIBUTE5
, H.GLOBAL_ATTRIBUTE6
, H.GLOBAL_ATTRIBUTE7
, H.GLOBAL_ATTRIBUTE8
, H.GLOBAL_ATTRIBUTE9
, H.GLOBAL_ATTRIBUTE10
, H.GLOBAL_ATTRIBUTE11
, H.GLOBAL_ATTRIBUTE12
, H.GLOBAL_ATTRIBUTE13
, H.GLOBAL_ATTRIBUTE14
, H.GLOBAL_ATTRIBUTE15
, H.GLOBAL_ATTRIBUTE16
, H.GLOBAL_ATTRIBUTE17
, H.GLOBAL_ATTRIBUTE18
, H.GLOBAL_ATTRIBUTE19
, H.GLOBAL_ATTRIBUTE20
, H.TP_CONTEXT
, H.TP_ATTRIBUTE1
, H.TP_ATTRIBUTE2
, H.TP_ATTRIBUTE3
, H.TP_ATTRIBUTE4
, H.TP_ATTRIBUTE5
, H.TP_ATTRIBUTE6
, H.TP_ATTRIBUTE7
, H.TP_ATTRIBUTE8
, H.TP_ATTRIBUTE9
, H.TP_ATTRIBUTE10
, H.TP_ATTRIBUTE11
, H.TP_ATTRIBUTE12
, H.TP_ATTRIBUTE13
, H.TP_ATTRIBUTE14
, H.TP_ATTRIBUTE15
, H.FREIGHT_CARRIER_CODE
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, NULL ORDER_SOURCE
, OT.NAME ORDER_TYPE
, NULL SOURCE_DOCUMENT_TYPE
, NULL AGREEMENT
, PL.NAME PRICE_LIST
, NULL CONVERSION_TYPE
, ACCRULE.NAME ACCOUNTING_RULE
, INVRULE.NAME INVOICING_RULE
, TERM.NAME TERMS
, PARTY.PARTY_NAME SOLD_TO
, SHIP_FROM_ORG.ORGANIZATION_CODE SHIP_FROM
, NULL SHIP_FROM_LOCATION
, NULL SHIP_FROM_ADDRESS1
, NULL SHIP_FROM_ADDRESS2
, NULL SHIP_FROM_ADDRESS3
, NULL SHIP_FROM_ADDRESS4
, 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
, NULL DELIVER_TO
, NULL DELIVER_TO_LOCATION
, NULL DELIVER_TO_ADDRESS1
, NULL DELIVER_TO_ADDRESS2
, NULL DELIVER_TO_ADDRESS3
, NULL DELIVER_TO_ADDRESS4
, BILL_SU.LOCATION INVOICE_TO
, BILL_SU.LOCATION INVOICE_TO_LOCATION
, BILL_LOC.ADDRESS1 INVOICE_TO_ADDRESS1
, BILL_LOC.ADDRESS2 INVOICE_TO_ADDRESS2
, BILL_LOC.ADDRESS3 INVOICE_TO_ADDRESS3
, BILL_LOC.ADDRESS4 INVOICE_TO_ADDRESS4
, DECODE(BILL_LOC.CITY
, NULL
, NULL
, BILL_LOC.CITY || '
, ') || DECODE(BILL_LOC.STATE
, NULL
, NULL
, BILL_LOC.STATE || '
, ') || DECODE(BILL_LOC.POSTAL_CODE
, NULL
, NULL
, BILL_LOC.POSTAL_CODE || '
, ') || DECODE(BILL_LOC.COUNTRY
, NULL
, NULL
, BILL_LOC.COUNTRY) INVOICE_TO_ADDRESS5
, SOLD_PARTY.PERSON_LAST_NAME || DECODE(SOLD_PARTY.PERSON_FIRST_NAME
, NULL
, NULL
, '
, ' || SOLD_PARTY.PERSON_FIRST_NAME) || DECODE(SOLD_ARL.MEANING
, NULL
, NULL
, ' '||SOLD_ARL.MEANING) SOLD_TO_CONTACT
, 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
, INVOICE_PARTY.PERSON_LAST_NAME || DECODE(INVOICE_PARTY.PERSON_FIRST_NAME
, NULL
, NULL
, '
, ' || INVOICE_PARTY.PERSON_FIRST_NAME) || DECODE(INVOICE_ARL.MEANING
, NULL
, NULL
, ' '||INVOICE_ARL.MEANING) INVOICE_TO_CONTACT
, NULL DELIVER_TO_CONTACT
, H.SALESREP_ID
, H.RETURN_REASON_CODE
, NULL RETURN_REASON
, H.ORDER_DATE_TYPE_CODE
, H.EARLIEST_SCHEDULE_LIMIT
, H.LATEST_SCHEDULE_LIMIT
, H.PAYMENT_TYPE_CODE
, H.PAYMENT_AMOUNT
, H.CHECK_NUMBER
, H.CREDIT_CARD_CODE
, H.CREDIT_CARD_HOLDER_NAME
, H.CREDIT_CARD_NUMBER
, H.CREDIT_CARD_EXPIRATION_DATE
, H.CREDIT_CARD_APPROVAL_CODE
, H.CREDIT_CARD_APPROVAL_DATE
, H.FIRST_ACK_CODE
, H.FIRST_ACK_DATE
, H.LAST_ACK_CODE
, H.LAST_ACK_DATE
, H.BOOKED_FLAG
, H.BOOKED_DATE
, H.CANCELLED_FLAG
, H.OPEN_FLAG
, H.SOLD_FROM_ORG_ID
, H.SHIPPING_INSTRUCTIONS
, H.PACKING_INSTRUCTIONS
, H.ORDER_CATEGORY_CODE
, H.FLOW_STATUS_CODE
, NULL FREIGHT_CARRIER
, DECODE(H.CUSTOMER_PREFERENCE_SET_CODE
, 'ARRIVAL'
, 'ARRIVAL'
, 'SHIP'
, 'SHIP')
, H.SALES_CHANNEL_CODE
, H.UPGRADED_FLAG
, H.LOCK_CONTROL
, PARTY.EMAIL_ADDRESS
, NVL(PARTY.GSA_INDICATOR_FLAG
, 'N') GSA_INDICATOR
, H.BLANKET_NUMBER
, H.QUOTE_DATE
, H.QUOTE_NUMBER
, H.SALES_DOCUMENT_NAME
, H.TRANSACTION_PHASE_CODE
, H.USER_STATUS_CODE
, H.DRAFT_SUBMITTED_FLAG
, H.SOURCE_DOCUMENT_VERSION_NUMBER
, H.SOLD_TO_SITE_USE_ID
, H.SUPPLIER_SIGNATURE
, H.SUPPLIER_SIGNATURE_DATE
, H.CUSTOMER_SIGNATURE
, H.CUSTOMER_SIGNATURE_DATE
, H.REASON_ID
, H.AUDIT_FLAG
, H.VERSION_FLAG
, H.PHASE_CHANGE_FLAG
, H.MINISITE_ID
, H.END_CUSTOMER_ID
, H.END_CUSTOMER_CONTACT_ID
, H.END_CUSTOMER_SITE_USE_ID
, NULL END_CUSTOMER_NAME
, NULL END_CUSTOMER_NUMBER
, NULL END_CUSTOMER_CONTACT
, NULL END_CUSTOMER_LOCATION
, NULL END_CUSTOMER_ADDRESS1
, NULL END_CUSTOMER_ADDRESS2
, NULL END_CUSTOMER_ADDRESS3
, NULL END_CUSTOMER_ADDRESS4
, NULL END_CUSTOMER_ADDRESS5
, H.IB_OWNER
, H.IB_CURRENT_LOCATION
, H.IB_INSTALLED_AT_LOCATION
, LKUP.MEANING TRANSACTION_PHASE
, H.ORDER_FIRMED_DATE
, H.INSTRUMENT_ID
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_CUST_SITE_USES_ALL BILL_SU
, HZ_PARTY_SITES BILL_PS
, HZ_LOCATIONS BILL_LOC
, HZ_CUST_ACCT_SITES_ALL BILL_CAS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, RA_TERMS_TL TERM
, OE_ORDER_HEADER_HISTORY H
, HZ_CUST_ACCOUNT_ROLES SOLD_ROLES
, HZ_PARTIES SOLD_PARTY
, HZ_RELATIONSHIPS SOLD_REL
, HZ_CUST_ACCOUNTS SOLD_ACCT
, AR_LOOKUPS SOLD_ARL
, HZ_CUST_ACCOUNT_ROLES SHIP_ROLES
, HZ_PARTIES SHIP_PARTY
, HZ_RELATIONSHIPS SHIP_REL
, HZ_CUST_ACCOUNTS SHIP_ACCT
, AR_LOOKUPS SHIP_ARL
, HZ_CUST_ACCOUNT_ROLES INVOICE_ROLES
, HZ_PARTIES INVOICE_PARTY
, HZ_RELATIONSHIPS INVOICE_REL
, HZ_CUST_ACCOUNTS INVOICE_ACCT
, AR_LOOKUPS INVOICE_ARL
, OE_TRANSACTION_TYPES_TL OT
, QP_LIST_HEADERS_TL PL
, RA_RULES INVRULE
, RA_RULES ACCRULE
, OE_LOOKUPS LKUP
WHERE H.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID
AND OT.LANGUAGE = USERENV('LANG')
AND H.PRICE_LIST_ID = PL.LIST_HEADER_ID(+)
AND PL.LANGUAGE(+) = USERENV('LANG')
AND H.INVOICING_RULE_ID = INVRULE.RULE_ID(+)
AND H.ACCOUNTING_RULE_ID = ACCRULE.RULE_ID(+)
AND H.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND TERM.LANGUAGE(+) =USERENV('LANG')
AND H.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND H.SHIP_FROM_ORG_ID = SHIP_FROM_ORG.ORGANIZATION_ID (+)
AND H.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 H.INVOICE_TO_ORG_ID = BILL_SU.SITE_USE_ID(+)
AND BILL_SU.CUST_ACCT_SITE_ID= BILL_CAS.CUST_ACCT_SITE_ID(+)
AND BILL_CAS.PARTY_SITE_ID = BILL_PS.PARTY_SITE_ID(+)
AND BILL_LOC.LOCATION_ID(+) = BILL_PS.LOCATION_ID
AND H.SOLD_TO_CONTACT_ID = SOLD_ROLES.CUST_ACCOUNT_ROLE_ID(+) AND SOLD_ROLES.PARTY_ID = SOLD_REL.PARTY_ID(+)
AND SOLD_ROLES.ROLE_TYPE(+) = 'CONTACT'
AND SOLD_REL.SUBJECT_ID = SOLD_PARTY.PARTY_ID(+)
AND SOLD_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SOLD_ARL.LOOKUP_CODE(+) = SOLD_PARTY.PERSON_PRE_NAME_ADJUNCT
AND H.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_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SHIP_ARL.LOOKUP_CODE(+) = SHIP_PARTY.PERSON_PRE_NAME_ADJUNCT
AND H.INVOICE_TO_CONTACT_ID = INVOICE_ROLES.CUST_ACCOUNT_ROLE_ID(+) AND INVOICE_ROLES.PARTY_ID = INVOICE_REL.PARTY_ID(+)
AND INVOICE_ROLES.ROLE_TYPE(+) = 'CONTACT'
AND INVOICE_REL.SUBJECT_ID = INVOICE_PARTY.PARTY_ID(+)
AND INVOICE_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND INVOICE_ARL.LOOKUP_CODE(+) = INVOICE_PARTY.PERSON_PRE_NAME_ADJUNCT
AND H.TRANSACTION_PHASE_CODE = LKUP.LOOKUP_CODE(+)
AND LKUP.LOOKUP_TYPE(+) = 'TRANSACTION_PHASE'
AND SHIP_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND INVOICE_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND INVOICE_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SHIP_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLD_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLD_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLD_ROLES.CUST_ACCOUNT_ID = SOLD_ACCT.CUST_ACCOUNT_ID(+)
AND NVL(SOLD_REL.OBJECT_ID
, -1) = NVL(SOLD_ACCT.PARTY_ID
, -1)
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 INVOICE_ROLES.CUST_ACCOUNT_ID = INVOICE_ACCT.CUST_ACCOUNT_ID(+)
AND NVL(INVOICE_REL.OBJECT_ID
, -1) = NVL(INVOICE_ACCT.PARTY_ID
, -1)

Columns