VIEW: APPS.OE_HEADER_ACKS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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