View: OE_HEADER_ACKS_V

Product:	ONT - Order Management
Description:	Information about the acknowledgements for Order Headers.
Implementation/DBA Data:	APPS.OE_HEADER_ACKS_V

View Text

SELECT HA.HEADER_ID
, HA.ORIG_SYS_DOCUMENT_REF
, HA.ORDER_NUMBER
, HA.ORDERED_DATE
, HA.ORG_ID
, HA.CHANGE_DATE
, HA.CHANGE_SEQUENCE
, HA.ACCOUNTING_RULE_ID
, HA.ACCOUNTING_RULE
, HA.ACCOUNTING_RULE_DURATION
, HA.ACKNOWLEDGMENT_FLAG
, HA.FIRST_ACK_CODE
, HA.LAST_ACK_CODE
, HA.FIRST_ACK_DATE
, HA.LAST_ACK_DATE
, HA.BUYER_SELLER_FLAG
, HA.CREATED_BY
, HA.CREATION_DATE
, HA.LAST_UPDATE_DATE
, HA.LAST_UPDATE_LOGIN
, HA.LAST_UPDATED_BY
, HA.BOOKED_FLAG
, HA.AGREEMENT_ID
, HA.AGREEMENT
, HA.AGREEMENT_NAME
, HA.CONTEXT
, HA.CUSTOMER_PAYMENT_TERM_ID
, HA.PRICE_LIST
, HA.PRICE_LIST_ID
, HA.PRICING_DATE
, HA.SHIP_FROM_ORG_ID
, HA.SHIP_FROM_ORG
, HA.SHIP_TO_ORG_ID
, HA.SHIP_TO_ORG
, HA.DROP_SHIP_FLAG
, HA.SOLD_FROM_ORG
, HA.SOLD_FROM_ORG_ID
, HA.SOLD_TO_ORG
, HA.SOLD_TO_ORG_ID
, HA.SOLD_TO_PHONE_ID
, HA.ATTRIBUTE1
, HA.ATTRIBUTE2
, HA.ATTRIBUTE3
, HA.ATTRIBUTE4
, HA.ATTRIBUTE5
, HA.ATTRIBUTE6
, HA.ATTRIBUTE7
, HA.ATTRIBUTE8
, HA.ATTRIBUTE9
, HA.ATTRIBUTE10
, HA.ATTRIBUTE11
, HA.ATTRIBUTE12
, HA.ATTRIBUTE13
, HA.ATTRIBUTE14
, HA.ATTRIBUTE15
, HA.ATTRIBUTE16
, HA.ATTRIBUTE17
, HA.ATTRIBUTE18
, HA.ATTRIBUTE19
, HA.ATTRIBUTE20
, HA.CANCELLED_FLAG
, HA.CLOSED_FLAG
, HA.CONVERSION_RATE
, HA.CONVERSION_RATE_DATE
, HA.CONVERSION_TYPE
, HA.CONVERSION_TYPE_CODE
, HA.CUST_PO_NUMBER
, NVL(HA.CUSTOMER_ID
, CUST_ACCT.CUST_ACCOUNT_ID)
, NVL(HA.CUSTOMER_NAME
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50))
, NVL(HA.CUSTOMER_NUMBER
, CUST_ACCT.ACCOUNT_NUMBER)
, HA.DELIVER_TO_CONTACT
, HA.DELIVER_TO_CONTACT_ID
, HA.DELIVER_TO_CUSTOMER
, HA.DELIVER_TO_CUSTOMER_NUMBER
, HA.DELIVER_TO_ORG
, HA.DELIVER_TO_ORG_ID
, HA.DEMAND_CLASS
, HA.DEMAND_CLASS_CODE
, HA.EARLIEST_SCHEDULE_LIMIT
, HA.LATEST_SCHEDULE_LIMIT
, HA.ERROR_FLAG
, HA.EXPIRATION_DATE
, HA.FOB_POINT
, HA.FOB_POINT_CODE
, HA.FREIGHT_CARRIER_CODE
, HA.FREIGHT_TERMS
, HA.FREIGHT_TERMS_CODE
, HA.ORDER_CATEGORY_CODE
, HA.GLOBAL_ATTRIBUTE_CATEGORY
, HA.GLOBAL_ATTRIBUTE1
, HA.GLOBAL_ATTRIBUTE2
, HA.GLOBAL_ATTRIBUTE3
, HA.GLOBAL_ATTRIBUTE4
, HA.GLOBAL_ATTRIBUTE5
, HA.GLOBAL_ATTRIBUTE6
, HA.GLOBAL_ATTRIBUTE7
, HA.GLOBAL_ATTRIBUTE8
, HA.GLOBAL_ATTRIBUTE9
, HA.GLOBAL_ATTRIBUTE10
, HA.GLOBAL_ATTRIBUTE11
, HA.GLOBAL_ATTRIBUTE12
, HA.GLOBAL_ATTRIBUTE13
, HA.GLOBAL_ATTRIBUTE14
, HA.GLOBAL_ATTRIBUTE15
, HA.GLOBAL_ATTRIBUTE16
, HA.GLOBAL_ATTRIBUTE17
, HA.GLOBAL_ATTRIBUTE18
, HA.GLOBAL_ATTRIBUTE19
, HA.GLOBAL_ATTRIBUTE20
, HA.HEADER_PO_CONTEXT
, HA.INTERFACE_STATUS
, HA.INVOICE_ADDRESS_1
, HA.INVOICE_ADDRESS_2
, HA.INVOICE_ADDRESS_3
, HA.INVOICE_ADDRESS_4
, HA.INVOICE_CITY
, HA.INVOICE_COUNTRY
, HA.INVOICE_COUNTY
, HA.INVOICE_CUSTOMER
, HA.INVOICE_CUSTOMER_NUMBER
, HA.INVOICE_POSTAL_CODE
, HA.INVOICE_PROVINCE_INT
, HA.INVOICE_SITE
, HA.INVOICE_SITE_CODE
, HA.INVOICE_STATE
, HA.INVOICE_TO_CONTACT
, HA.INVOICE_TO_CONTACT_FIRST_NAME
, HA.INVOICE_TO_CONTACT_ID
, HA.INVOICE_TO_CONTACT_LAST_NAME
, HA.INVOICE_TO_ORG
, HA.INVOICE_TO_ORG_ID
, HA.INVOICE_TOLERANCE_ABOVE
, HA.INVOICE_TOLERANCE_BELOW
, HA.INVOICING_RULE
, HA.INVOICING_RULE_ID
, HA.OPEN_FLAG
, HA.OPERATION_CODE
, HA.ORDER_DATE_TYPE_CODE
, HA.ORDER_SOURCE
, HA.ORDER_SOURCE_ID
, HA.ORDER_TYPE
, HA.ORDER_TYPE_ID
, HA.ORDERED_BY_CONTACT_FIRST_NAME
, HA.ORDERED_BY_CONTACT_LAST_NAME
, HA.PACKING_INSTRUCTIONS
, HA.PARTIAL_SHIPMENTS_ALLOWED
, HA.PAYMENT_TERM_ID
, HA.PAYMENT_TERM
, HA.PO_ATTRIBUTE_1
, HA.PO_ATTRIBUTE_2
, HA.PO_ATTRIBUTE_3
, HA.PO_ATTRIBUTE_4
, HA.PO_ATTRIBUTE_5
, HA.PO_ATTRIBUTE_6
, HA.PO_ATTRIBUTE_7
, HA.PO_ATTRIBUTE_8
, HA.PO_ATTRIBUTE_9
, HA.PO_ATTRIBUTE_10
, HA.PO_ATTRIBUTE_11
, HA.PO_ATTRIBUTE_12
, HA.PO_ATTRIBUTE_13
, HA.PO_ATTRIBUTE_14
, HA.PO_ATTRIBUTE_15
, HA.PO_REVISION_DATE
, HA.PROGRAM
, HA.PROGRAM_APPLICATION
, HA.PROGRAM_APPLICATION_ID
, HA.PROGRAM_ID
, HA.PROGRAM_UPDATE_DATE
, HA.RELATED_PO_NUMBER
, HA.REMAINDER_ORDERS_ALLOWED
, HA.REQUEST_DATE
, HA.REQUEST_ID
, HA.RETURN_REASON_CODE
, HA.SALESREP_ID
, HA.SALESREP
, HA.SHIP_TO_ADDRESS_1
, HA.SHIP_TO_ADDRESS_2
, HA.SHIP_TO_ADDRESS_3
, HA.SHIP_TO_ADDRESS_4
, HA.SHIP_TO_CITY
, HA.SHIP_TO_CONTACT
, HA.SHIP_TO_CONTACT_FIRST_NAME
, HA.SHIP_TO_CONTACT_ID
, HA.SHIP_TO_CONTACT_LAST_NAME
, HA.SHIP_TO_COUNTRY
, HA.SHIP_TO_COUNTY
, HA.SHIP_TO_CUSTOMER
, HA.SHIP_TO_CUSTOMER_NUMBER
, HA.SHIP_TO_POSTAL_CODE
, HA.SHIP_TO_PROVINCE
, HA.SHIP_TO_SITE_INT
, HA.SHIP_TO_STATE
, HA.SHIP_TOLERANCE_ABOVE
, HA.SHIP_TOLERANCE_BELOW
, HA.SHIPMENT_PRIORITY
, HA.SHIPMENT_PRIORITY_CODE
, HA.SHIPMENT_PRIORITY_CODE_INT
, HA.SHIPPING_INSTRUCTIONS
, HA.SHIPPING_METHOD
, HA.SHIPPING_METHOD_CODE
, HA.SOLD_TO_CONTACT
, HA.SOLD_TO_CONTACT_ID
, HA.SOURCE_DOCUMENT_ID
, HA.SOURCE_DOCUMENT_TYPE_ID
, HA.SUBMISSION_DATETIME
, HA.TAX_EXEMPT_FLAG
, HA.TAX_EXEMPT_NUMBER
, HA.TAX_EXEMPT_REASON
, HA.TAX_EXEMPT_REASON_CODE
, HA.TAX_POINT
, HA.TAX_POINT_CODE
, HA.TRANSACTIONAL_CURR
, HA.TRANSACTIONAL_CURR_CODE
, HA.VERSION_NUMBER
, 'E'
, TD.TEST_FLAG
, TD.DOCUMENT_ID
, TD.TRANSLATOR_CODE
, ACCT_SITE.ECE_TP_LOCATION_CODE
, TH.TP_DESCRIPTION
, TH.TP_REFERENCE_EXT1
, TH.TP_REFERENCE_EXT2
, TD.DOCUMENT_STANDARD
, TH.ATTRIBUTE_CATEGORY
, TH.ATTRIBUTE1
, TH.ATTRIBUTE2
, TH.ATTRIBUTE3
, TH.ATTRIBUTE4
, TH.ATTRIBUTE5
, TH.ATTRIBUTE6
, TH.ATTRIBUTE7
, TH.ATTRIBUTE8
, TH.ATTRIBUTE9
, TH.ATTRIBUTE10
, TH.ATTRIBUTE11
, TH.ATTRIBUTE12
, TH.ATTRIBUTE13
, TH.ATTRIBUTE14
, TH.ATTRIBUTE15
, TD.ATTRIBUTE_CATEGORY
, TD.ATTRIBUTE1
, TD.ATTRIBUTE2
, TD.ATTRIBUTE3
, TD.ATTRIBUTE4
, TD.ATTRIBUTE5
, HA.SHIP_TO_EDI_LOCATION_CODE
, ACCT_SITE.ECE_TP_LOCATION_CODE
, HA.BILL_TO_EDI_LOCATION_CODE
, HA.CUSTOMER_PAYMENT_TERM
, HA.SOLD_TO_ADDRESS1
, HA.SOLD_TO_ADDRESS2
, HA.SOLD_TO_ADDRESS3
, HA.SOLD_TO_ADDRESS4
, HA.SOLD_TO_CITY
, HA.SOLD_TO_POSTAL_CODE
, HA.SOLD_TO_COUNTRY
, HA.SOLD_TO_STATE
, HA.SOLD_TO_COUNTY
, HA.SOLD_TO_PROVINCE
, HA.SOLD_TO_CONTACT_LAST_NAME
, HA.SOLD_TO_CONTACT_FIRST_NAME
, SYSDATE
, TD.DOCUMENT_TYPE
, 'OR'
, HA.CUST_PO_NUMBER
, SHIP_FROM_ADDRESS_1
, SHIP_FROM_ADDRESS_2
, SHIP_FROM_ADDRESS_3
, SHIP_FROM_CITY
, SHIP_FROM_POSTAL_CODE
, SHIP_FROM_COUNTRY
, SHIP_FROM_REGION1
, SHIP_FROM_REGION2
, SHIP_FROM_REGION3
, SHIP_FROM_EDI_LOCATION_CODE
, SHIP_FROM_ADDRESS_ID
, SOLD_TO_ADDRESS_ID
, SHIP_TO_ADDRESS_ID
, INVOICE_ADDRESS_ID
, SHIP_TO_ADDRESS_CODE
, HA.TP_CONTEXT
, HA.TP_ATTRIBUTE1
, HA.TP_ATTRIBUTE2
, HA.TP_ATTRIBUTE3
, HA.TP_ATTRIBUTE4
, HA.TP_ATTRIBUTE5
, HA.TP_ATTRIBUTE6
, HA.TP_ATTRIBUTE7
, HA.TP_ATTRIBUTE8
, HA.TP_ATTRIBUTE9
, HA.TP_ATTRIBUTE10
, HA.TP_ATTRIBUTE11
, HA.TP_ATTRIBUTE12
, HA.TP_ATTRIBUTE13
, HA.TP_ATTRIBUTE14
, HA.TP_ATTRIBUTE15
, HA.XML_MESSAGE_ID
, HA.BLANKET_NUMBER
, HA.BOOKED_DATE
, HA.FLOW_STATUS_CODE
, HA.CREDIT_CARD_APPROVAL_DATE
, HA.PAYMENT_TYPE_CODE
, HA.PAYMENT_AMOUNT
, HA.CHECK_NUMBER
, HA.CREDIT_CARD_CODE
, HA.CREDIT_CARD_HOLDER_NAME
, HA.CREDIT_CARD_NUMBER
, HA.CREDIT_CARD_EXPIRATION_DATE
, HA.CREDIT_CARD_APPROVAL_CODE
, HA.CUSTOMER_PREFERENCE_SET_CODE
, HA.SALES_CHANNEL_CODE
, HA.QUOTE_DATE
, HA.QUOTE_NUMBER
, HA.SALES_DOCUMENT_NAME
, HA.TRANSACTION_PHASE_CODE
, HA.USER_STATUS_CODE
, HA.SOLD_TO_SITE_USE_ID
, HA.SUPPLIER_SIGNATURE
, HA.SUPPLIER_SIGNATURE_DATE
, HA.CUSTOMER_SIGNATURE
, HA.CUSTOMER_SIGNATURE_DATE
, HA.SOLD_TO_PARTY_NUMBER
, HA.SHIP_TO_PARTY_NUMBER
, HA.INVOICE_TO_PARTY_NUMBER
, HA.DELIVER_TO_PARTY_NUMBER
, HA.END_CUSTOMER_NUMBER
, HA.END_CUSTOMER_PARTY_NUMBER
, HA.END_CUSTOMER_ID
, HA.END_CUSTOMER_CONTACT_ID
, HA.END_CUSTOMER_SITE_USE_ID
, HA.END_CUSTOMER_ADDRESS1
, HA.END_CUSTOMER_ADDRESS2
, HA.END_CUSTOMER_ADDRESS3
, HA.END_CUSTOMER_ADDRESS4
, HA.END_CUSTOMER_STATE
, HA.END_CUSTOMER_COUNTY
, HA.END_CUSTOMER_PROVINCE
, HA.END_CUSTOMER_POSTAL_CODE
, HA.END_CUSTOMER_COUNTRY
, HA.END_CUSTOMER_CONTACT
, HA.END_CUSTOMER_CONTACT_LAST_NAME
, HA.END_CUSTOMER_CONTACT_FIRST_NAM
, HA.END_CUSTOMER_NAME
, HA.IB_OWNER
, HA.IB_INSTALLED_AT_LOCATION
, HA.IB_CURRENT_LOCATION
, HA.SOLD_TO_LOCATION_ADDRESS1
, HA.SOLD_TO_LOCATION_ADDRESS2
, HA.SOLD_TO_LOCATION_ADDRESS3
, HA.SOLD_TO_LOCATION_ADDRESS4
, HA.SOLD_TO_LOCATION_CITY
, HA.SOLD_TO_LOCATION_STATE
, HA.SOLD_TO_LOCATION_POSTAL_CODE
, HA.SOLD_TO_LOCATION_COUNTRY
, HA.SOLD_TO_LOCATION_PROVINCE
, HA.SOLD_TO_LOCATION_COUNTY
, HA.ORDER_FIRMED_DATE
, HA.DELIVER_TO_ADDRESS1
, HA.DELIVER_TO_ADDRESS2
, HA.DELIVER_TO_ADDRESS3
, HA.DELIVER_TO_ADDRESS4
, HA.DELIVER_TO_CITY
, HA.DELIVER_TO_COUNTRY
, HA.DELIVER_TO_COUNTY
, HA.DELIVER_TO_POSTAL_CODE
, HA.DELIVER_TO_PROVINCE
, HA.DELIVER_TO_STATE
, HA.END_CUSTOMER_CITY
, HA.IB_OWNER_CODE
, HA.IB_CURRENT_LOCATION_CODE
, HA.IB_INSTALLED_AT_LOCATION_CODE
FROM OE_HEADER_ACKS HA
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_SITE_USES_ALL SITE
, HZ_CUST_ACCT_SITES ACCT_SITE
, ECE_TP_HEADERS TH
, ECE_TP_DETAILS TD
WHERE HA.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND PARTY.PARTY_ID = CUST_ACCT.PARTY_ID
AND SITE.SITE_USE_CODE = 'SOLD_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND SITE.PRIMARY_FLAG = 'Y'
AND ACCT_SITE.TP_HEADER_ID = TH.TP_HEADER_ID
AND TH.TP_HEADER_ID = TD.TP_HEADER_ID
AND TD.DOCUMENT_ID IN ('POAO'
, 'POCAO')
AND TD.EDI_FLAG = 'Y'
AND NVL(HA.ACKNOWLEDGMENT_FLAG
, 'N') <> 'Y'
AND SITE.ORG_ID = ACCT_SITE.ORG_ID

Columns