VIEW: APPS.OE_BLKT_HEADERS_HIST_V

Source

View Text - Preformatted

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.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, 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, 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.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_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, OE_LOOKUPS LKUP 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.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 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 sold_rel.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND sold_rel.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' union all SELECT OBHH.ROWID, OBHH.HEADER_ID, OBHH.ORG_ID, OBHH.ORDER_TYPE_ID, OBHH.ORDER_NUMBER, OBHH.VERSION_NUMBER, OBHH.EXPIRATION_DATE, OBHH.ORDER_SOURCE_ID, OBHH.SOURCE_DOCUMENT_TYPE_ID, OBHH.ORIG_SYS_DOCUMENT_REF, OBHH.SOURCE_DOCUMENT_ID, OBHH.ORDERED_DATE, OBHH.REQUEST_DATE, OBHH.PRICING_DATE, OBHH.SHIPMENT_PRIORITY_CODE, OBHH.DEMAND_CLASS_CODE, OBHH.PRICE_LIST_ID, OBHH.TAX_EXEMPT_FLAG, OBHH.TAX_EXEMPT_NUMBER, OBHH.TAX_EXEMPT_REASON_CODE, OBHH.CONVERSION_RATE, OBHH.CONVERSION_TYPE_CODE, OBHH.CONVERSION_RATE_DATE, OBHH.PARTIAL_SHIPMENTS_ALLOWED, OBHH.SHIP_TOLERANCE_ABOVE, OBHH.SHIP_TOLERANCE_BELOW, OBHH.TRANSACTIONAL_CURR_CODE, OBHH.AGREEMENT_ID, OBHH.TAX_POINT_CODE, OBHH.CUST_PO_NUMBER, OBHH.INVOICING_RULE_ID, OBHH.ACCOUNTING_RULE_ID, OBHH.PAYMENT_TERM_ID, OBHH.SHIPPING_METHOD_CODE, OBHH.FREIGHT_CARRIER_CODE, OBHH.FOB_POINT_CODE, OBHH.FREIGHT_TERMS_CODE, OBHH.SOLD_FROM_ORG_ID, OBHH.SOLD_TO_ORG_ID, OBHH.SHIP_FROM_ORG_ID, OBHH.SHIP_TO_ORG_ID, OBHH.INVOICE_TO_ORG_ID, OBHH.DELIVER_TO_ORG_ID, OBHH.SOLD_TO_CONTACT_ID, OBHH.SHIP_TO_CONTACT_ID, OBHH.INVOICE_TO_CONTACT_ID, OBHH.DELIVER_TO_CONTACT_ID, OBHH.CREATION_DATE, OBHH.CREATED_BY, OBHH.LAST_UPDATED_BY, OBHH.LAST_UPDATE_DATE, OBHH.LAST_UPDATE_LOGIN, OBHH.PROGRAM_APPLICATION_ID, OBHH.PROGRAM_ID, OBHH.PROGRAM_UPDATE_DATE, OBHH.REQUEST_ID, OBHH.CONTEXT, OBHH.ATTRIBUTE1, OBHH.ATTRIBUTE2, OBHH.ATTRIBUTE3, OBHH.ATTRIBUTE4, OBHH.ATTRIBUTE5, OBHH.ATTRIBUTE6, OBHH.ATTRIBUTE7, OBHH.ATTRIBUTE8, OBHH.ATTRIBUTE9, OBHH.ATTRIBUTE10, OBHH.ATTRIBUTE11, OBHH.ATTRIBUTE12, OBHH.ATTRIBUTE13, OBHH.ATTRIBUTE14, OBHH.ATTRIBUTE15, OBHH.ATTRIBUTE16, OBHH.ATTRIBUTE17, OBHH.ATTRIBUTE18, OBHH.ATTRIBUTE19, OBHH.ATTRIBUTE20, OBHH.GLOBAL_ATTRIBUTE_CATEGORY, OBHH.GLOBAL_ATTRIBUTE1, OBHH.GLOBAL_ATTRIBUTE2, OBHH.GLOBAL_ATTRIBUTE3, OBHH.GLOBAL_ATTRIBUTE4, OBHH.GLOBAL_ATTRIBUTE5, OBHH.GLOBAL_ATTRIBUTE6, OBHH.GLOBAL_ATTRIBUTE7, OBHH.GLOBAL_ATTRIBUTE8, OBHH.GLOBAL_ATTRIBUTE9, OBHH.GLOBAL_ATTRIBUTE10, OBHH.GLOBAL_ATTRIBUTE11, OBHH.GLOBAL_ATTRIBUTE12, OBHH.GLOBAL_ATTRIBUTE13, OBHH.GLOBAL_ATTRIBUTE14, OBHH.GLOBAL_ATTRIBUTE15, OBHH.GLOBAL_ATTRIBUTE16, OBHH.GLOBAL_ATTRIBUTE17, OBHH.GLOBAL_ATTRIBUTE18, OBHH.GLOBAL_ATTRIBUTE19, OBHH.GLOBAL_ATTRIBUTE20, OBHH.CANCELLED_FLAG, OBHH.OPEN_FLAG, OBHH.BOOKED_FLAG, OBHH.SALESREP_ID, OBHH.RETURN_REASON_CODE, OBHH.ORDER_DATE_TYPE_CODE, OBHH.EARLIEST_SCHEDULE_LIMIT, OBHH.LATEST_SCHEDULE_LIMIT, OBHH.PAYMENT_TYPE_CODE, OBHH.PAYMENT_AMOUNT, OBHH.CHECK_NUMBER, OBHH.CREDIT_CARD_CODE, OBHH.CREDIT_CARD_HOLDER_NAME, OBHH.CREDIT_CARD_NUMBER, OBHH.CREDIT_CARD_EXPIRATION_DATE, OBHH.CREDIT_CARD_APPROVAL_CODE, OBHH.SALES_CHANNEL_CODE, OBHH.FIRST_ACK_CODE, OBHH.FIRST_ACK_DATE, OBHH.LAST_ACK_CODE, OBHH.LAST_ACK_DATE, OBHH.ORDER_CATEGORY_CODE, OBHH.CHANGE_SEQUENCE, null, /*OBHH.DROP_SHIP_FLAG,*/ to_number(null), /*OBHH.CUSTOMER_PAYMENT_TERM_ID, */ OBHH.SHIPPING_INSTRUCTIONS, OBHH.PACKING_INSTRUCTIONS, OBHH.TP_CONTEXT, OBHH.TP_ATTRIBUTE1, OBHH.TP_ATTRIBUTE2, OBHH.TP_ATTRIBUTE3, OBHH.TP_ATTRIBUTE4, OBHH.TP_ATTRIBUTE5, OBHH.TP_ATTRIBUTE6, OBHH.TP_ATTRIBUTE7, OBHH.TP_ATTRIBUTE8, OBHH.TP_ATTRIBUTE9, OBHH.TP_ATTRIBUTE10, OBHH.TP_ATTRIBUTE11, OBHH.TP_ATTRIBUTE12, OBHH.TP_ATTRIBUTE13, OBHH.TP_ATTRIBUTE14, OBHH.TP_ATTRIBUTE15, OBHH.FLOW_STATUS_CODE, OBHH.MARKETING_SOURCE_CODE_ID, OBHH.CREDIT_CARD_APPROVAL_DATE, OBHH.UPGRADED_FLAG, OBHH.CUSTOMER_PREFERENCE_SET_CODE, OBHH.BOOKED_DATE, OBHH.LOCK_CONTROL, OBHH.PRICE_REQUEST_CODE, to_number(NULL), /* batch_id */ to_number(NULL), /* xml_message_id */ OBHH.BLANKET_NUMBER, OBHH.START_DATE_ACTIVE, OBHH.END_DATE_ACTIVE, OBHH.ENFORCE_PRICE_LIST_FLAG, OBHH.RELEASED_AMOUNT, OBHH.REVISION_CHANGE_REASON_CODE, LKUP.meaning revision_reason_code, OBHH.REVISION_CHANGE_COMMENTS, OBHH.ON_HOLD_FLAG, OBHH.BLANKET_MIN_AMOUNT, OBHH.BLANKET_MAX_AMOUNT, OBHH.OVERRIDE_AMOUNT_FLAG, OBHH.RETURNED_AMOUNT, OBHH.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, 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, OBHH.ENFORCE_SHIP_TO_FLAG, OBHH.ENFORCE_INVOICE_TO_FLAG, OBHH.ENFORCE_FREIGHT_TERM_FLAG, OBHH.ENFORCE_SHIPPING_METHOD_FLAG, OBHH.ENFORCE_PAYMENT_TERM_FLAG, OBHH.ENFORCE_ACCOUNTING_RULE_FLAG, OBHH.ENFORCE_INVOICING_RULE_FLAG, OBHH.FULFILLED_AMOUNT, OBHH.SALES_DOCUMENT_NAME, OBHH.TRANSACTION_PHASE_CODE, OBHH.USER_STATUS_CODE, OBHH.SUPPLIER_SIGNATURE, OBHH.SUPPLIER_SIGNATURE_DATE, OBHH.CUSTOMER_SIGNATURE, OBHH.CUSTOMER_SIGNATURE_DATE, OBHH.SOLD_TO_SITE_USE_ID, OBHH.DRAFT_SUBMITTED_FLAG, OBHH.SOURCE_DOCUMENT_VERSION_NUMBER, OBHH.QUOTE_NUMBER, OBHH.QUOTE_DATE, OBHH.NEW_PRICE_LIST_ID, OBHH.NEW_MODIFIER_LIST_ID, OBHH.DEFAULT_DISCOUNT_PERCENT, OBHH.DEFAULT_DISCOUNT_AMOUNT FROM oe_blanket_headers_hist obhh, mtl_parameters ship_from_org, 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, OE_LOOKUPS LKUP WHERE oe_blanket_form_control.include_all_revisions = 'Y' AND OBHH.order_type_id = ot.transaction_type_id(+) AND ot.language(+) = userenv('LANG') AND OBHH.price_list_id = pl.list_header_id(+) AND pl.language(+) = userenv('LANG') AND OBHH.invoicing_rule_id = invrule.rule_id(+) AND OBHH.accounting_rule_id = accrule.rule_id(+) AND OBHH.payment_term_id = term.term_id(+) AND TERM.Language(+) = userenv('LANG') AND OBHH.transactional_curr_code = fndcur.currency_code (+) AND OBHH.sold_to_org_id = cust_acct.cust_account_id(+) AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+) AND OBHH.ship_from_org_id = ship_from_org.organization_id (+) AND OBHH.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 OBHH.REVISION_CHANGE_REASON_CODE = LKUP.LOOKUP_CODE(+) AND LKUP.LOOKUP_TYPE(+) = 'BLANKET_REV_REASON_CODE' AND sold_rel.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND sold_rel.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'

View Text - HTML Formatted

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.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
, 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
, 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.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_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
, OE_LOOKUPS LKUP
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.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 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 SOLD_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLD_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' UNION ALL SELECT OBHH.ROWID
, OBHH.HEADER_ID
, OBHH.ORG_ID
, OBHH.ORDER_TYPE_ID
, OBHH.ORDER_NUMBER
, OBHH.VERSION_NUMBER
, OBHH.EXPIRATION_DATE
, OBHH.ORDER_SOURCE_ID
, OBHH.SOURCE_DOCUMENT_TYPE_ID
, OBHH.ORIG_SYS_DOCUMENT_REF
, OBHH.SOURCE_DOCUMENT_ID
, OBHH.ORDERED_DATE
, OBHH.REQUEST_DATE
, OBHH.PRICING_DATE
, OBHH.SHIPMENT_PRIORITY_CODE
, OBHH.DEMAND_CLASS_CODE
, OBHH.PRICE_LIST_ID
, OBHH.TAX_EXEMPT_FLAG
, OBHH.TAX_EXEMPT_NUMBER
, OBHH.TAX_EXEMPT_REASON_CODE
, OBHH.CONVERSION_RATE
, OBHH.CONVERSION_TYPE_CODE
, OBHH.CONVERSION_RATE_DATE
, OBHH.PARTIAL_SHIPMENTS_ALLOWED
, OBHH.SHIP_TOLERANCE_ABOVE
, OBHH.SHIP_TOLERANCE_BELOW
, OBHH.TRANSACTIONAL_CURR_CODE
, OBHH.AGREEMENT_ID
, OBHH.TAX_POINT_CODE
, OBHH.CUST_PO_NUMBER
, OBHH.INVOICING_RULE_ID
, OBHH.ACCOUNTING_RULE_ID
, OBHH.PAYMENT_TERM_ID
, OBHH.SHIPPING_METHOD_CODE
, OBHH.FREIGHT_CARRIER_CODE
, OBHH.FOB_POINT_CODE
, OBHH.FREIGHT_TERMS_CODE
, OBHH.SOLD_FROM_ORG_ID
, OBHH.SOLD_TO_ORG_ID
, OBHH.SHIP_FROM_ORG_ID
, OBHH.SHIP_TO_ORG_ID
, OBHH.INVOICE_TO_ORG_ID
, OBHH.DELIVER_TO_ORG_ID
, OBHH.SOLD_TO_CONTACT_ID
, OBHH.SHIP_TO_CONTACT_ID
, OBHH.INVOICE_TO_CONTACT_ID
, OBHH.DELIVER_TO_CONTACT_ID
, OBHH.CREATION_DATE
, OBHH.CREATED_BY
, OBHH.LAST_UPDATED_BY
, OBHH.LAST_UPDATE_DATE
, OBHH.LAST_UPDATE_LOGIN
, OBHH.PROGRAM_APPLICATION_ID
, OBHH.PROGRAM_ID
, OBHH.PROGRAM_UPDATE_DATE
, OBHH.REQUEST_ID
, OBHH.CONTEXT
, OBHH.ATTRIBUTE1
, OBHH.ATTRIBUTE2
, OBHH.ATTRIBUTE3
, OBHH.ATTRIBUTE4
, OBHH.ATTRIBUTE5
, OBHH.ATTRIBUTE6
, OBHH.ATTRIBUTE7
, OBHH.ATTRIBUTE8
, OBHH.ATTRIBUTE9
, OBHH.ATTRIBUTE10
, OBHH.ATTRIBUTE11
, OBHH.ATTRIBUTE12
, OBHH.ATTRIBUTE13
, OBHH.ATTRIBUTE14
, OBHH.ATTRIBUTE15
, OBHH.ATTRIBUTE16
, OBHH.ATTRIBUTE17
, OBHH.ATTRIBUTE18
, OBHH.ATTRIBUTE19
, OBHH.ATTRIBUTE20
, OBHH.GLOBAL_ATTRIBUTE_CATEGORY
, OBHH.GLOBAL_ATTRIBUTE1
, OBHH.GLOBAL_ATTRIBUTE2
, OBHH.GLOBAL_ATTRIBUTE3
, OBHH.GLOBAL_ATTRIBUTE4
, OBHH.GLOBAL_ATTRIBUTE5
, OBHH.GLOBAL_ATTRIBUTE6
, OBHH.GLOBAL_ATTRIBUTE7
, OBHH.GLOBAL_ATTRIBUTE8
, OBHH.GLOBAL_ATTRIBUTE9
, OBHH.GLOBAL_ATTRIBUTE10
, OBHH.GLOBAL_ATTRIBUTE11
, OBHH.GLOBAL_ATTRIBUTE12
, OBHH.GLOBAL_ATTRIBUTE13
, OBHH.GLOBAL_ATTRIBUTE14
, OBHH.GLOBAL_ATTRIBUTE15
, OBHH.GLOBAL_ATTRIBUTE16
, OBHH.GLOBAL_ATTRIBUTE17
, OBHH.GLOBAL_ATTRIBUTE18
, OBHH.GLOBAL_ATTRIBUTE19
, OBHH.GLOBAL_ATTRIBUTE20
, OBHH.CANCELLED_FLAG
, OBHH.OPEN_FLAG
, OBHH.BOOKED_FLAG
, OBHH.SALESREP_ID
, OBHH.RETURN_REASON_CODE
, OBHH.ORDER_DATE_TYPE_CODE
, OBHH.EARLIEST_SCHEDULE_LIMIT
, OBHH.LATEST_SCHEDULE_LIMIT
, OBHH.PAYMENT_TYPE_CODE
, OBHH.PAYMENT_AMOUNT
, OBHH.CHECK_NUMBER
, OBHH.CREDIT_CARD_CODE
, OBHH.CREDIT_CARD_HOLDER_NAME
, OBHH.CREDIT_CARD_NUMBER
, OBHH.CREDIT_CARD_EXPIRATION_DATE
, OBHH.CREDIT_CARD_APPROVAL_CODE
, OBHH.SALES_CHANNEL_CODE
, OBHH.FIRST_ACK_CODE
, OBHH.FIRST_ACK_DATE
, OBHH.LAST_ACK_CODE
, OBHH.LAST_ACK_DATE
, OBHH.ORDER_CATEGORY_CODE
, OBHH.CHANGE_SEQUENCE
, NULL
, /*OBHH.DROP_SHIP_FLAG
, */ TO_NUMBER(NULL)
, /*OBHH.CUSTOMER_PAYMENT_TERM_ID
, */ OBHH.SHIPPING_INSTRUCTIONS
, OBHH.PACKING_INSTRUCTIONS
, OBHH.TP_CONTEXT
, OBHH.TP_ATTRIBUTE1
, OBHH.TP_ATTRIBUTE2
, OBHH.TP_ATTRIBUTE3
, OBHH.TP_ATTRIBUTE4
, OBHH.TP_ATTRIBUTE5
, OBHH.TP_ATTRIBUTE6
, OBHH.TP_ATTRIBUTE7
, OBHH.TP_ATTRIBUTE8
, OBHH.TP_ATTRIBUTE9
, OBHH.TP_ATTRIBUTE10
, OBHH.TP_ATTRIBUTE11
, OBHH.TP_ATTRIBUTE12
, OBHH.TP_ATTRIBUTE13
, OBHH.TP_ATTRIBUTE14
, OBHH.TP_ATTRIBUTE15
, OBHH.FLOW_STATUS_CODE
, OBHH.MARKETING_SOURCE_CODE_ID
, OBHH.CREDIT_CARD_APPROVAL_DATE
, OBHH.UPGRADED_FLAG
, OBHH.CUSTOMER_PREFERENCE_SET_CODE
, OBHH.BOOKED_DATE
, OBHH.LOCK_CONTROL
, OBHH.PRICE_REQUEST_CODE
, TO_NUMBER(NULL)
, /* BATCH_ID */ TO_NUMBER(NULL)
, /* XML_MESSAGE_ID */ OBHH.BLANKET_NUMBER
, OBHH.START_DATE_ACTIVE
, OBHH.END_DATE_ACTIVE
, OBHH.ENFORCE_PRICE_LIST_FLAG
, OBHH.RELEASED_AMOUNT
, OBHH.REVISION_CHANGE_REASON_CODE
, LKUP.MEANING REVISION_REASON_CODE
, OBHH.REVISION_CHANGE_COMMENTS
, OBHH.ON_HOLD_FLAG
, OBHH.BLANKET_MIN_AMOUNT
, OBHH.BLANKET_MAX_AMOUNT
, OBHH.OVERRIDE_AMOUNT_FLAG
, OBHH.RETURNED_AMOUNT
, OBHH.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
, 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
, OBHH.ENFORCE_SHIP_TO_FLAG
, OBHH.ENFORCE_INVOICE_TO_FLAG
, OBHH.ENFORCE_FREIGHT_TERM_FLAG
, OBHH.ENFORCE_SHIPPING_METHOD_FLAG
, OBHH.ENFORCE_PAYMENT_TERM_FLAG
, OBHH.ENFORCE_ACCOUNTING_RULE_FLAG
, OBHH.ENFORCE_INVOICING_RULE_FLAG
, OBHH.FULFILLED_AMOUNT
, OBHH.SALES_DOCUMENT_NAME
, OBHH.TRANSACTION_PHASE_CODE
, OBHH.USER_STATUS_CODE
, OBHH.SUPPLIER_SIGNATURE
, OBHH.SUPPLIER_SIGNATURE_DATE
, OBHH.CUSTOMER_SIGNATURE
, OBHH.CUSTOMER_SIGNATURE_DATE
, OBHH.SOLD_TO_SITE_USE_ID
, OBHH.DRAFT_SUBMITTED_FLAG
, OBHH.SOURCE_DOCUMENT_VERSION_NUMBER
, OBHH.QUOTE_NUMBER
, OBHH.QUOTE_DATE
, OBHH.NEW_PRICE_LIST_ID
, OBHH.NEW_MODIFIER_LIST_ID
, OBHH.DEFAULT_DISCOUNT_PERCENT
, OBHH.DEFAULT_DISCOUNT_AMOUNT
FROM OE_BLANKET_HEADERS_HIST OBHH
, MTL_PARAMETERS SHIP_FROM_ORG
, 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
, OE_LOOKUPS LKUP
WHERE OE_BLANKET_FORM_CONTROL.INCLUDE_ALL_REVISIONS = 'Y'
AND OBHH.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID(+)
AND OT.LANGUAGE(+) = USERENV('LANG')
AND OBHH.PRICE_LIST_ID = PL.LIST_HEADER_ID(+)
AND PL.LANGUAGE(+) = USERENV('LANG')
AND OBHH.INVOICING_RULE_ID = INVRULE.RULE_ID(+)
AND OBHH.ACCOUNTING_RULE_ID = ACCRULE.RULE_ID(+)
AND OBHH.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND TERM.LANGUAGE(+) = USERENV('LANG')
AND OBHH.TRANSACTIONAL_CURR_CODE = FNDCUR.CURRENCY_CODE (+)
AND OBHH.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND OBHH.SHIP_FROM_ORG_ID = SHIP_FROM_ORG.ORGANIZATION_ID (+)
AND OBHH.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 OBHH.REVISION_CHANGE_REASON_CODE = LKUP.LOOKUP_CODE(+)
AND LKUP.LOOKUP_TYPE(+) = 'BLANKET_REV_REASON_CODE'
AND SOLD_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND SOLD_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'