View: OEFV_ORDER_HEADERS

Product:	ONT - Order Management
Description:
Implementation/DBA Data:	APPS.OEFV_ORDER_HEADERS

View Text

SELECT HEADER.ORDER_NUMBER
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, PARTY.PARTY_NAME CUSTOMER
, SUBSTRB(SOLDCONT2.PERSON_LAST_NAME
, 1
, 50) || DECODE(SOLDCONT2.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| SUBSTRB(SOLDCONT2.PERSON_FIRST_NAME
, 1
, 50)) || DECODE(SOLD_ARL.MEANING
, NULL
, NULL
, ' ' || SOLD_ARL.MEANING) CUSTOMER_CONTACT
, SOLDCONT6.EMAIL_ADDRESS
, DECODE(SOLDCONT5.PHONE_COUNTRY_CODE
, NULL
, NULL
, SOLDCONT5.PHONE_COUNTRY_CODE || ' ') || DECODE(SOLDCONT5.PHONE_AREA_CODE
, NULL
, NULL
, SOLDCONT5.PHONE_AREA_CODE || '-') || DECODE(SOLDCONT5.PHONE_NUMBER
, NULL
, NULL
, SOLDCONT5.PHONE_NUMBER) CUSTOMER_CONTACT_PHONE_NUMBER
, SOLDCONT5.PHONE_EXTENSION
, AGREE.NAME AGREEMENT
, PL.NAME
, SHIP_TO_ORG.LOCATION SHIP_TO_LOCATION
, SHIP_TO_ADDR2.ADDRESS1 SHIP_TO_ADDRESS1
, SHIP_TO_ADDR2.ADDRESS2 SHIP_TO_ADDRESS2
, SHIP_TO_ADDR2.ADDRESS3 SHIP_TO_ADDRESS3
, SHIP_TO_ADDR2.ADDRESS4 SHIP_TO_ADDRESS4
, DECODE(SHIP_TO_ADDR2.CITY
, NULL
, NULL
, SHIP_TO_ADDR2.CITY || '
, ') || DECODE(SHIP_TO_ADDR2.STATE
, NULL
, NULL
, SHIP_TO_ADDR2.STATE || '
, ') || DECODE(SHIP_TO_ADDR2.POSTAL_CODE
, NULL
, NULL
, SHIP_TO_ADDR2.POSTAL_CODE || '
, ') || DECODE(SHIP_TO_ADDR2.COUNTRY
, NULL
, NULL
, SHIP_TO_ADDR2.COUNTRY) SHIP_TO_CTY_ST_ZIP_CTRY
, SUBSTRB(SHIPCONT2.PERSON_LAST_NAME
, 1
, 50) || DECODE(SHIPCONT2.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| SUBSTRB(SHIPCONT2.PERSON_FIRST_NAME
, 1
, 50)) || DECODE(SHIP_ARL.MEANING
, NULL
, NULL
, ' '|| SHIP_ARL.MEANING) SHIP_TO_CONTACT
, SHIP_FROM_ORG.ORGANIZATION_CODE SHIP_FROM_ORG
, OS.NAME ORDER_SOURCE
, OT.NAME ORDER_TYPE
, OE_BIS_SALESPERSON.GET_SALESPERSON_NAME(HEADER.SALESREP_ID)
, BILLORG.LOCATION BILL_TO
, BILL_TO_ADDR2.ADDRESS1 BILL_TO_ADDRESS1
, BILL_TO_ADDR2.ADDRESS2 BILL_TO_ADDRESS2
, BILL_TO_ADDR2.ADDRESS3 BILL_TO_ADDRESS3
, BILL_TO_ADDR2.ADDRESS4 BILL_TO_ADDRESS4
, DECODE(BILL_TO_ADDR2.CITY
, NULL
, NULL
, BILL_TO_ADDR2.CITY || '
, ') || DECODE(BILL_TO_ADDR2.STATE
, NULL
, NULL
, BILL_TO_ADDR2.STATE || '
, ') || DECODE(BILL_TO_ADDR2.POSTAL_CODE
, NULL
, NULL
, BILL_TO_ADDR2.POSTAL_CODE || '
, ') || DECODE(BILL_TO_ADDR2.COUNTRY
, NULL
, NULL
, BILL_TO_ADDR2.COUNTRY) BILL_TO_CTY_ST_ZIP_CTRY
, SUBSTRB(BILLCONT2.PERSON_LAST_NAME
, 1
, 50) || DECODE(BILLCONT2.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| SUBSTRB(BILLCONT2.PERSON_FIRST_NAME
, 1
, 50)) || DECODE(BILL_ARL.MEANING
, NULL
, NULL
, ' '||BILL_ARL.MEANING) BILL_TO_CONTACT
, TERM.NAME
, INVRULE.NAME
, HEADER.ORDERED_DATE
, HEADER.CUST_PO_NUMBER
, '_LA:HEADER.SALES_CHANNEL_CODE:OE_LOOKUPS:SALES_CHANNEL:MEANING'
, '_LA:HEADER.FLOW_STATUS_CODE:OE_LOOKUPS:FLOW_STATUS:MEANING'
, '_LA:HEADER.TAX_EXEMPT_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, '_LA:HEADER.TAX_EXEMPT_REASON_CODE:AR_LOOKUPS:TAX_REASON:MEANING'
, HEADER.TAX_EXEMPT_NUMBER
, HEADER.TRANSACTIONAL_CURR_CODE
, HEADER.PAYMENT_TYPE_CODE
, '_LA:HEADER.SHIPMENT_PRIORITY_CODE:OE_LOOKUPS:SHIPMENT_PRIORITY:MEANING'
, '_LA:HEADER.FREIGHT_TERMS_CODE:OE_LOOKUPS:FREIGHT_TERMS:MEANING'
, '_LA:HEADER.BOOKED_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, '_LA:HEADER.CANCELLED_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, HEADER.ORG_ID
, HEADER.SOLD_FROM_ORG_ID
, HEADER.HEADER_ID
, HEADER.ORDER_TYPE_ID
, HEADER.SOLD_TO_CONTACT_ID
, HEADER.AGREEMENT_ID
, HEADER.PRICE_LIST_ID
, HEADER.SHIP_TO_ORG_ID
, HEADER.SHIP_TO_CONTACT_ID
, SUBSTRB(SHIP_PARTY.PARTY_NAME
, 1
, 50)
, SHIP_CUST_ACCT.CUST_ACCOUNT_ID
, HEADER.SHIP_FROM_ORG_ID
, HEADER.INVOICING_RULE_ID
, HEADER.INVOICE_TO_ORG_ID
, HEADER.INVOICE_TO_CONTACT_ID
, SUBSTRB(BILL_PARTY.PARTY_NAME
, 1
, 50)
, BILL_CUST_ACCT.CUST_ACCOUNT_ID
, HEADER.PAYMENT_TERM_ID
, HEADER.ORDER_SOURCE_ID
, HEADER.ORIG_SYS_DOCUMENT_REF
, HEADER.SALESREP_ID
, '_DF:ONT:OE_HEADER_ATTRIBUTES:HEADER'
, '_DF:ONT:OE_HEADER_GLOBAL_ATTRIBUTE:HEADER'
, '_DF:ONT:OE_HEADER_TP_ATTRIBUTES:HEADER'
, HEADER.BOOKED_DATE
, HEADER.LAST_UPDATE_DATE
, HEADER.LAST_UPDATED_BY
, USER1.USER_NAME
, HEADER.CREATION_DATE
, HEADER.CREATED_BY
, USER2.USER_NAME
, HEADER.VERSION_NUMBER
, HEADER.EXPIRATION_DATE
, HEADER.SOURCE_DOCUMENT_TYPE_ID
, HEADER.CHANGE_SEQUENCE
, HEADER.SOURCE_DOCUMENT_ID
, HEADER.REQUEST_DATE
, HEADER.PRICING_DATE
, HEADER.DEMAND_CLASS_CODE
, HEADER.PRICE_REQUEST_CODE
, HEADER.CONVERSION_RATE
, HEADER.CONVERSION_TYPE_CODE
, HEADER.CONVERSION_RATE_DATE
, '_LA:HEADER.PARTIAL_SHIPMENTS_ALLOWED:OE_LOOKUPS:YES_NO:MEANING'
, HEADER.SHIP_TOLERANCE_ABOVE
, HEADER.SHIP_TOLERANCE_BELOW
, '_LA:HEADER.TAX_POINT_CODE:OE_LOOKUPS:TAX_POINT:MEANING'
, '_LA:HEADER.OPEN_FLAG:OE_LOOKUPS:YES_NO:MEANING'
, HEADER.ACCOUNTING_RULE_ID
, HEADER.ACCOUNTING_RULE_DURATION
, '_LA:HEADER.SHIPPING_METHOD_CODE:OE_SHIP_METHODS_V:SHIP_METHOD:MEANING'
, HEADER.FREIGHT_CARRIER_CODE
, HEADER.FOB_POINT_CODE
, HEADER.SOLD_TO_ORG_ID
, HEADER.DELIVER_TO_ORG_ID
, HEADER.DELIVER_TO_CONTACT_ID
, HEADER.RETURN_REASON_CODE
, HEADER.ORDER_DATE_TYPE_CODE
, HEADER.EARLIEST_SCHEDULE_LIMIT
, HEADER.LATEST_SCHEDULE_LIMIT
, HEADER.PAYMENT_AMOUNT
, HEADER.CHECK_NUMBER
, HEADER.CREDIT_CARD_CODE
, HEADER.CREDIT_CARD_HOLDER_NAME
, HEADER.CREDIT_CARD_NUMBER
, HEADER.CREDIT_CARD_EXPIRATION_DATE
, HEADER.CREDIT_CARD_APPROVAL_CODE
, HEADER.FIRST_ACK_CODE
, HEADER.FIRST_ACK_DATE
, HEADER.LAST_ACK_CODE
, HEADER.LAST_ACK_DATE
, '_LA:HEADER.ORDER_CATEGORY_CODE:OE_LOOKUPS:ORDER_CATEGORY:MEANING'
, HEADER.SHIPPING_INSTRUCTIONS
, HEADER.PACKING_INSTRUCTIONS
, HEADER.CREDIT_CARD_APPROVAL_DATE
, HEADER.CUSTOMER_PREFERENCE_SET_CODE
, HEADER.MARKETING_SOURCE_CODE_ID
, HEADER.UPGRADED_FLAG
, HEADER.TRANSACTION_PHASE_CODE
, HEADER.ORDER_FIRMED_DATE
FROM MTL_PARAMETERS SHIP_FROM_ORG
, HZ_CUST_SITE_USES_ALL SHIP_TO_ORG
, HZ_PARTY_SITES SHIP_TO_ADDR1
, HZ_LOCATIONS SHIP_TO_ADDR2
, HZ_CUST_ACCT_SITES_ALL SHIP_TO_ADDR3
, HZ_PARTY_SITES BILL_TO_ADDR1
, HZ_LOCATIONS BILL_TO_ADDR2
, HZ_CUST_ACCT_SITES_ALL BILL_TO_ADDR3
, HZ_CUST_SITE_USES_ALL BILLORG
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, RA_TERMS TERM
, OE_ORDER_HEADERS_ALL HEADER
, HZ_CUST_ACCOUNT_ROLES SOLDCONT1
, HZ_PARTIES SOLDCONT2
, AR_LOOKUPS SOLD_ARL
, HZ_RELATIONSHIPS SOLDCONT4
, HZ_CONTACT_POINTS SOLDCONT5
, HZ_PARTIES SOLDCONT6
, HZ_CUST_ACCOUNT_ROLES SHIPCONT1
, HZ_PARTIES SHIPCONT2
, AR_LOOKUPS SHIP_ARL
, HZ_RELATIONSHIPS SHIPCONT4
, HZ_CUST_ACCOUNT_ROLES BILLCONT1
, HZ_PARTIES BILLCONT2
, AR_LOOKUPS BILL_ARL
, HZ_RELATIONSHIPS BILLCONT4
, FND_CURRENCIES FNDCUR
, OE_ORDER_SOURCES OS
, OE_TRANSACTION_TYPES_TL OT
, OE_AGREEMENTS_TL AGREE
, QP_LIST_HEADERS_TL PL
, RA_RULES INVRULE
, HZ_PARTIES SHIP_PARTY
, HZ_CUST_ACCOUNTS SHIP_CUST_ACCT
, HZ_PARTIES BILL_PARTY
, HZ_CUST_ACCOUNTS BILL_CUST_ACCT
, FND_USER USER1
, FND_USER USER2
, HZ_CUST_ACCOUNTS SOLDACCT
, HZ_CUST_ACCOUNTS SHIPACCT
, HZ_CUST_ACCOUNTS BILLACCT
WHERE NVL(HEADER.ORDER_SOURCE_ID
, HEADER.SOURCE_DOCUMENT_TYPE_ID ) = OS.ORDER_SOURCE_ID(+)
AND HEADER.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID
AND OT.LANGUAGE = USERENV('LANG')
AND HEADER.AGREEMENT_ID = AGREE.AGREEMENT_ID(+)
AND AGREE.LANGUAGE(+) = USERENV('LANG')
AND HEADER.PRICE_LIST_ID = PL.LIST_HEADER_ID(+)
AND PL.LANGUAGE(+) = USERENV('LANG')
AND HEADER.INVOICING_RULE_ID = INVRULE.RULE_ID(+)
AND HEADER.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND HEADER.TRANSACTIONAL_CURR_CODE = FNDCUR.CURRENCY_CODE
AND HEADER.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND PARTY.PARTY_ID(+) = CUST_ACCT.PARTY_ID
AND HEADER.SHIP_FROM_ORG_ID = SHIP_FROM_ORG.ORGANIZATION_ID(+)
AND HEADER.SHIP_TO_ORG_ID = SHIP_TO_ORG.SITE_USE_ID(+)
AND SHIP_TO_ORG.CUST_ACCT_SITE_ID = SHIP_TO_ADDR3.CUST_ACCT_SITE_ID(+)
AND SHIP_TO_ADDR3.PARTY_SITE_ID = SHIP_TO_ADDR1.PARTY_SITE_ID(+)
AND SHIP_TO_ADDR2.LOCATION_ID(+) = SHIP_TO_ADDR1.LOCATION_ID
AND HEADER.INVOICE_TO_ORG_ID = BILLORG.SITE_USE_ID(+)
AND BILLORG.CUST_ACCT_SITE_ID = BILL_TO_ADDR3.CUST_ACCT_SITE_ID(+)
AND BILL_TO_ADDR3.PARTY_SITE_ID = BILL_TO_ADDR1.PARTY_SITE_ID(+)
AND BILL_TO_ADDR2.LOCATION_ID(+) = BILL_TO_ADDR1.LOCATION_ID
AND HEADER.SOLD_TO_CONTACT_ID = SOLDCONT1.CUST_ACCOUNT_ROLE_ID(+)
AND SOLDCONT1.PARTY_ID = SOLDCONT4.PARTY_ID(+)
AND SOLDCONT1.ROLE_TYPE(+) = 'CONTACT'
AND SOLD_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SOLD_ARL.LOOKUP_CODE(+) = SOLDCONT2.PERSON_PRE_NAME_ADJUNCT
AND SOLDCONT1.CUST_ACCOUNT_ID = SOLDACCT.CUST_ACCOUNT_ID(+)
AND NVL(SOLDCONT4.OBJECT_ID
, -1) = NVL(SOLDACCT.PARTY_ID
, -1)
AND SOLDCONT4.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLDCONT4.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLDCONT4.SUBJECT_ID = SOLDCONT2.PARTY_ID(+)
AND SOLDCONT4.PARTY_ID = SOLDCONT6.PARTY_ID(+)
AND SOLDCONT5.OWNER_TABLE_ID(+) = SOLDCONT6.PARTY_ID
AND SOLDCONT5.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLDCONT5.CONTACT_POINT_TYPE(+) = 'PHONE'
AND SOLDCONT5.STATUS (+) = 'A'
AND SOLDCONT5.PRIMARY_FLAG(+) = 'Y'
AND HEADER.SHIP_TO_CONTACT_ID = SHIPCONT1.CUST_ACCOUNT_ROLE_ID(+)
AND SHIPCONT1.PARTY_ID = SHIPCONT4.PARTY_ID(+)
AND SHIPCONT1.ROLE_TYPE(+) = 'CONTACT'
AND SHIP_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SHIP_ARL.LOOKUP_CODE(+) = SHIPCONT2.PERSON_PRE_NAME_ADJUNCT
AND SHIPCONT1.CUST_ACCOUNT_ID = SHIPACCT.CUST_ACCOUNT_ID(+)
AND NVL(SHIPCONT4.OBJECT_ID
, -1) = NVL(SHIPACCT.PARTY_ID
, -1)
AND SHIPCONT4.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SHIPCONT4.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SHIPCONT4.SUBJECT_ID = SHIPCONT2.PARTY_ID(+)
AND HEADER.INVOICE_TO_CONTACT_ID = BILLCONT1.CUST_ACCOUNT_ROLE_ID(+)
AND BILLCONT1.PARTY_ID = BILLCONT4.PARTY_ID(+)
AND BILLCONT1.ROLE_TYPE(+) = 'CONTACT'
AND BILL_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND BILL_ARL.LOOKUP_CODE(+) = BILLCONT2.PERSON_PRE_NAME_ADJUNCT
AND BILLCONT1.CUST_ACCOUNT_ID = BILLACCT.CUST_ACCOUNT_ID(+)
AND NVL(BILLCONT4.OBJECT_ID
, -1) = NVL(BILLACCT.PARTY_ID
, -1)
AND BILLCONT4.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND BILLCONT4.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND BILLCONT4.SUBJECT_ID = BILLCONT2.PARTY_ID(+)
AND SHIP_TO_ADDR3.CUST_ACCOUNT_ID = SHIP_CUST_ACCT.CUST_ACCOUNT_ID(+)
AND SHIP_PARTY.PARTY_ID(+)= SHIP_CUST_ACCT.PARTY_ID
AND BILL_TO_ADDR3.CUST_ACCOUNT_ID = BILL_CUST_ACCT.CUST_ACCOUNT_ID(+)
AND BILL_PARTY.PARTY_ID(+)= BILL_CUST_ACCT.PARTY_ID
AND HEADER.LAST_UPDATED_BY = USER1.USER_ID(+)
AND HEADER.CREATED_BY = USER2.USER_ID (+)
AND '_SEC:HEADER.ORG_ID' IS NOT NULL WITH READ ONLY

Columns