View: OE_BLANKET_HEADERS_V

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

View Text

SELECT OOBHA.ROWID
, OOBHA.HEADER_ID
, OOBHA.ORG_ID
, OOBHA.ORDER_TYPE_ID
, OOBHA.ORDER_NUMBER
, OOBHA.VERSION_NUMBER
, OOBHA.EXPIRATION_DATE
, OOBHA.ORDER_SOURCE_ID
, OOBHA.SOURCE_DOCUMENT_TYPE_ID
, OOBHA.ORIG_SYS_DOCUMENT_REF
, OOBHA.SOURCE_DOCUMENT_ID
, OOBHA.ORDERED_DATE
, OOBHA.REQUEST_DATE
, OOBHA.PRICING_DATE
, OOBHA.SHIPMENT_PRIORITY_CODE
, OOBHA.DEMAND_CLASS_CODE
, OOBHA.PRICE_LIST_ID
, OOBHA.TAX_EXEMPT_FLAG
, OOBHA.TAX_EXEMPT_NUMBER
, OOBHA.TAX_EXEMPT_REASON_CODE
, OOBHA.CONVERSION_RATE
, OOBHA.CONVERSION_TYPE_CODE
, OOBHA.CONVERSION_RATE_DATE
, OOBHA.PARTIAL_SHIPMENTS_ALLOWED
, OOBHA.SHIP_TOLERANCE_ABOVE
, OOBHA.SHIP_TOLERANCE_BELOW
, OOBHA.TRANSACTIONAL_CURR_CODE
, OOBHA.AGREEMENT_ID
, OOBHA.TAX_POINT_CODE
, OOBHA.CUST_PO_NUMBER
, OOBHA.INVOICING_RULE_ID
, OOBHA.ACCOUNTING_RULE_ID
, OOBHA.PAYMENT_TERM_ID
, OOBHA.SHIPPING_METHOD_CODE
, OOBHA.FREIGHT_CARRIER_CODE
, OOBHA.FOB_POINT_CODE
, OOBHA.FREIGHT_TERMS_CODE
, OOBHA.SOLD_FROM_ORG_ID
, OOBHA.SOLD_TO_ORG_ID
, OOBHA.SHIP_FROM_ORG_ID
, OOBHA.SHIP_TO_ORG_ID
, OOBHA.INVOICE_TO_ORG_ID
, OOBHA.DELIVER_TO_ORG_ID
, OOBHA.SOLD_TO_CONTACT_ID
, OOBHA.SHIP_TO_CONTACT_ID
, OOBHA.INVOICE_TO_CONTACT_ID
, OOBHA.DELIVER_TO_CONTACT_ID
, OOBHA.CREATION_DATE
, OOBHA.CREATED_BY
, OOBHA.LAST_UPDATED_BY
, OOBHA.LAST_UPDATE_DATE
, OOBHA.LAST_UPDATE_LOGIN
, OOBHA.PROGRAM_APPLICATION_ID
, OOBHA.PROGRAM_ID
, OOBHA.PROGRAM_UPDATE_DATE
, OOBHA.REQUEST_ID
, OOBHA.CONTEXT
, OOBHA.ATTRIBUTE1
, OOBHA.ATTRIBUTE2
, OOBHA.ATTRIBUTE3
, OOBHA.ATTRIBUTE4
, OOBHA.ATTRIBUTE5
, OOBHA.ATTRIBUTE6
, OOBHA.ATTRIBUTE7
, OOBHA.ATTRIBUTE8
, OOBHA.ATTRIBUTE9
, OOBHA.ATTRIBUTE10
, OOBHA.ATTRIBUTE11
, OOBHA.ATTRIBUTE12
, OOBHA.ATTRIBUTE13
, OOBHA.ATTRIBUTE14
, OOBHA.ATTRIBUTE15
, OOBHA.ATTRIBUTE16
, OOBHA.ATTRIBUTE17
, OOBHA.ATTRIBUTE18
, OOBHA.ATTRIBUTE19
, OOBHA.ATTRIBUTE20
, OOBHA.GLOBAL_ATTRIBUTE_CATEGORY
, OOBHA.GLOBAL_ATTRIBUTE1
, OOBHA.GLOBAL_ATTRIBUTE2
, OOBHA.GLOBAL_ATTRIBUTE3
, OOBHA.GLOBAL_ATTRIBUTE4
, OOBHA.GLOBAL_ATTRIBUTE5
, OOBHA.GLOBAL_ATTRIBUTE6
, OOBHA.GLOBAL_ATTRIBUTE7
, OOBHA.GLOBAL_ATTRIBUTE8
, OOBHA.GLOBAL_ATTRIBUTE9
, OOBHA.GLOBAL_ATTRIBUTE10
, OOBHA.GLOBAL_ATTRIBUTE11
, OOBHA.GLOBAL_ATTRIBUTE12
, OOBHA.GLOBAL_ATTRIBUTE13
, OOBHA.GLOBAL_ATTRIBUTE14
, OOBHA.GLOBAL_ATTRIBUTE15
, OOBHA.GLOBAL_ATTRIBUTE16
, OOBHA.GLOBAL_ATTRIBUTE17
, OOBHA.GLOBAL_ATTRIBUTE18
, OOBHA.GLOBAL_ATTRIBUTE19
, OOBHA.GLOBAL_ATTRIBUTE20
, OOBHA.CANCELLED_FLAG
, OOBHA.OPEN_FLAG
, OOBHA.BOOKED_FLAG
, OOBHA.SALESREP_ID
, OOBHA.RETURN_REASON_CODE
, OOBHA.ORDER_DATE_TYPE_CODE
, OOBHA.EARLIEST_SCHEDULE_LIMIT
, OOBHA.LATEST_SCHEDULE_LIMIT
, OOBHA.PAYMENT_TYPE_CODE
, OOBHA.PAYMENT_AMOUNT
, OOBHA.CHECK_NUMBER
, OOBHA.CREDIT_CARD_CODE
, OOBHA.CREDIT_CARD_HOLDER_NAME
, OOBHA.CREDIT_CARD_NUMBER
, OOBHA.CREDIT_CARD_EXPIRATION_DATE
, OOBHA.CREDIT_CARD_APPROVAL_CODE
, OOBHA.SALES_CHANNEL_CODE
, OOBHA.FIRST_ACK_CODE
, OOBHA.FIRST_ACK_DATE
, OOBHA.LAST_ACK_CODE
, OOBHA.LAST_ACK_DATE
, OOBHA.ORDER_CATEGORY_CODE
, OOBHA.CHANGE_SEQUENCE
, OOBHA.DROP_SHIP_FLAG
, OOBHA.CUSTOMER_PAYMENT_TERM_ID
, OOBHA.SHIPPING_INSTRUCTIONS
, OOBHA.PACKING_INSTRUCTIONS
, OOBHA.TP_CONTEXT
, OOBHA.TP_ATTRIBUTE1
, OOBHA.TP_ATTRIBUTE2
, OOBHA.TP_ATTRIBUTE3
, OOBHA.TP_ATTRIBUTE4
, OOBHA.TP_ATTRIBUTE5
, OOBHA.TP_ATTRIBUTE6
, OOBHA.TP_ATTRIBUTE7
, OOBHA.TP_ATTRIBUTE8
, OOBHA.TP_ATTRIBUTE9
, OOBHA.TP_ATTRIBUTE10
, OOBHA.TP_ATTRIBUTE11
, OOBHA.TP_ATTRIBUTE12
, OOBHA.TP_ATTRIBUTE13
, OOBHA.TP_ATTRIBUTE14
, OOBHA.TP_ATTRIBUTE15
, OOBHA.FLOW_STATUS_CODE
, OOBHA.MARKETING_SOURCE_CODE_ID
, OOBHA.CREDIT_CARD_APPROVAL_DATE
, OOBHA.UPGRADED_FLAG
, OOBHA.CUSTOMER_PREFERENCE_SET_CODE
, OOBHA.BOOKED_DATE
, OOBHA.LOCK_CONTROL
, OOBHA.PRICE_REQUEST_CODE
, OOBHA.BATCH_ID
, OOBHA.XML_MESSAGE_ID
, OOBHA.BLANKET_NUMBER
, OOBHE.START_DATE_ACTIVE
, OOBHE.END_DATE_ACTIVE
, OOBHE.ENFORCE_PRICE_LIST_FLAG
, OOBHE.ENFORCE_SHIP_TO_FLAG
, OOBHE.ENFORCE_INVOICE_TO_FLAG
, OOBHE.ENFORCE_FREIGHT_TERM_FLAG
, OOBHE.ENFORCE_SHIPPING_METHOD_FLAG
, OOBHE.ENFORCE_PAYMENT_TERM_FLAG
, OOBHE.ENFORCE_ACCOUNTING_RULE_FLAG
, OOBHE.ENFORCE_INVOICING_RULE_FLAG
, OOBHE.RELEASED_AMOUNT
, OOBHE.REVISION_CHANGE_REASON_CODE
, LKUP.MEANING REVISION_REASON_CODE
, OOBHE.REVISION_CHANGE_COMMENTS
, OOBHE.ON_HOLD_FLAG
, OOBHE.BLANKET_MIN_AMOUNT
, OOBHE.BLANKET_MAX_AMOUNT
, OOBHE.OVERRIDE_AMOUNT_FLAG
, OOBHE.RETURNED_AMOUNT
, OOBHE.REVISION_CHANGE_DATE
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, OT.NAME ORDER_TYPE
, NULL AGREEMENT
, PL.NAME PRICE_LIST_NAME
, 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
, DEL_SU.LOCATION DELIVER_TO
, DEL_SU.LOCATION DELIVER_TO_LOCATION
, DEL_LOC.ADDRESS1 DELIVER_TO_ADDRESS1
, DEL_LOC.ADDRESS2 DELIVER_TO_ADDRESS2
, DEL_LOC.ADDRESS3 DELIVER_TO_ADDRESS3
, DEL_LOC.ADDRESS4 DELIVER_TO_ADDRESS4
, DECODE(DEL_LOC.CITY
, NULL
, NULL
, DEL_LOC.CITY || '
, ') || DECODE(DEL_LOC.STATE
, NULL
, NULL
, DEL_LOC.STATE || '
, ') || DECODE(DEL_LOC.POSTAL_CODE
, NULL
, NULL
, DEL_LOC.POSTAL_CODE || '
, ') || DECODE(DEL_LOC.COUNTRY
, NULL
, NULL
, DEL_LOC.COUNTRY) DELIVER_TO_ADDRESS5
, 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
, SREP.NAME
, OOBHE.FULFILLED_AMOUNT
, OOBHA.SALES_DOCUMENT_NAME
, OOBHA.TRANSACTION_PHASE_CODE
, OOBHA.USER_STATUS_CODE
, OOBHA.SUPPLIER_SIGNATURE
, OOBHA.SUPPLIER_SIGNATURE_DATE
, OOBHA.CUSTOMER_SIGNATURE
, OOBHA.CUSTOMER_SIGNATURE_DATE
, OOBHA.SOLD_TO_SITE_USE_ID
, OOBHA.DRAFT_SUBMITTED_FLAG
, OOBHA.SOURCE_DOCUMENT_VERSION_NUMBER
, OOBHA.QUOTE_NUMBER
, OOBHA.QUOTE_DATE
, OOBHE.NEW_PRICE_LIST_ID
, OOBHE.NEW_MODIFIER_LIST_ID
, OOBHE.DEFAULT_DISCOUNT_PERCENT
, OOBHE.DEFAULT_DISCOUNT_AMOUNT
FROM OE_BLANKET_HEADERS OOBHA
, OE_BLANKET_HEADERS_EXT OOBHE
, 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
, HZ_CUST_ACCOUNT_ROLES SOLD_ROLES
, HZ_PARTIES SOLD_PARTY
, AR_LOOKUPS SOLD_ARL
, HZ_RELATIONSHIPS SOLD_REL
, HZ_CUST_ACCOUNTS SOLD_ACCT
, FND_CURRENCIES FNDCUR
, OE_TRANSACTION_TYPES_TL OT
, QP_LIST_HEADERS_TL PL
, RA_RULES INVRULE
, RA_RULES ACCRULE
, RA_SALESREPS SREP
, OE_LOOKUPS LKUP
, HZ_CUST_SITE_USES_ALL DEL_SU
, HZ_LOCATIONS DEL_LOC
, HZ_CUST_ACCT_SITES_ALL DEL_CAS
, HZ_PARTY_SITES DEL_PS
WHERE OOBHA.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID(+)
AND OT.LANGUAGE(+) = USERENV('LANG')
AND OOBHA.PRICE_LIST_ID = PL.LIST_HEADER_ID(+)
AND PL.LANGUAGE(+) = USERENV('LANG')
AND OOBHA.INVOICING_RULE_ID = INVRULE.RULE_ID(+)
AND OOBHA.ACCOUNTING_RULE_ID = ACCRULE.RULE_ID(+)
AND OOBHA.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND TERM.LANGUAGE(+) = USERENV('LANG')
AND OOBHA.TRANSACTIONAL_CURR_CODE = FNDCUR.CURRENCY_CODE (+)
AND OOBHA.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND OOBHA.SHIP_FROM_ORG_ID = SHIP_FROM_ORG.ORGANIZATION_ID (+)
AND OOBHA.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 OOBHA.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 OOBHA.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_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SOLD_ARL.LOOKUP_CODE(+) = SOLD_PARTY.PERSON_PRE_NAME_ADJUNCT
AND SOLD_REL.SUBJECT_ID = SOLD_PARTY.PARTY_ID(+)
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 SOLD_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLD_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND OOBHA.SALESREP_ID = SREP.SALESREP_ID (+)
AND OOBHE.REVISION_CHANGE_REASON_CODE = LKUP.LOOKUP_CODE(+)
AND LKUP.LOOKUP_TYPE(+) = 'BLANKET_REV_REASON_CODE'
AND OOBHA.ORDER_NUMBER = OOBHE.ORDER_NUMBER
AND OOBHA.SALES_DOCUMENT_TYPE_CODE = 'B'
AND OOBHA.DELIVER_TO_ORG_ID = DEL_SU.SITE_USE_ID (+)
AND DEL_SU.CUST_ACCT_SITE_ID = DEL_CAS.CUST_ACCT_SITE_ID (+)
AND DEL_CAS.PARTY_SITE_ID = DEL_PS.PARTY_SITE_ID (+)
AND DEL_LOC.LOCATION_ID(+) = DEL_PS.LOCATION_ID

Columns