View: XNC_QUOTE_FIND_V

View Text

SELECT DISTINCT QUOTE_HEADERS.ROWID ROW_ID
, QUOTE_HEADERS.QUOTE_HEADER_ID QUOTE_HEADER_ID
, QUOTE_HEADERS.QUOTE_NAME QUOTE_NAME
, QUOTE_HEADERS.QUOTE_NUMBER QUOTE_NUMBER
, QUOTE_HEADERS.QUOTE_VERSION QUOTE_VERSION
, QUOTE_HEADERS.QUOTE_EXPIRATION_DATE QUOTE_EXPIRATION_DATE
, QUOTE_HEADERS.QUOTE_STATUS_ID QUOTE_STATUS_ID
, QUOTE_HEADERS.ORDER_ID ORDER_ID
, ORDER_HEADERS.ORDER_NUMBER ORDER_NUMBER
, QUOTE_HEADERS.ORG_ID ORG_ID
, PARTY_INFO.PARTY_NAME ORGANIZATION_NAME
, QUOTE_HEADERS.PARTY_ID PARTY_ID
, PARTY_INFO.PARTY_TYPE PARTY_TYPE
, PARTY_INFO.PERSON_FIRST_NAME CONTACT_FIRST_NAME
, PARTY_INFO.PERSON_MIDDLE_NAME CONTACT_MIDDLE_NAME
, PARTY_INFO.PERSON_LAST_NAME CONTACT_LAST_NAME
, QUOTE_HEADERS.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, ACCOUNT_INFO.ACCOUNT_NUMBER ACCOUNT_NUMBER
, QUOTE_HEADERS.ORG_CONTACT_ID ORG_CONTACT_ID
, QUOTE_HEADERS.PHONE_ID PHONE_ID
, PHONE_INFO.PHONE_AREA_CODE || PHONE_INFO.PHONE_NUMBER FULL_PHONE_NUMBER
, QUOTE_HEADERS.ORDERED_DATE ORDERED_DATE
, QUOTE_HEADERS.ORDER_TYPE_ID ORDER_TYPE_ID
, QUOTE_HEADERS.TOTAL_QUOTE_PRICE TOTAL_QUOTE_PRICE
, QUOTE_LINE_ATTRIBS.NAME LINE_ATTRIBS_NAME
, QUOTE_LINE_ATTRIBS.VALUE SUBSCRIPTION_VALUE
FROM ASO_QUOTE_HEADERS_ALL QUOTE_HEADERS
, ASO_QUOTE_LINES_ALL QUOTE_LINES
, ASO_QUOTE_LINE_ATTRIBS_EXT QUOTE_LINE_ATTRIBS
, HZ_PARTIES PARTY_INFO
, HZ_CUST_ACCOUNTS ACCOUNT_INFO
, OE_ORDER_HEADERS ORDER_HEADERS
, HZ_CONTACT_POINTS PHONE_INFO
WHERE QUOTE_HEADERS.PARTY_ID = PARTY_INFO.PARTY_ID
AND ACCOUNT_INFO.CUST_ACCOUNT_ID(+) = QUOTE_HEADERS.CUST_ACCOUNT_ID
AND QUOTE_HEADERS.QUOTE_HEADER_ID = QUOTE_LINES.QUOTE_HEADER_ID
AND QUOTE_HEADERS.ORDER_ID = ORDER_HEADERS.HEADER_ID(+)
AND QUOTE_LINES.QUOTE_LINE_ID = QUOTE_LINE_ATTRIBS.QUOTE_LINE_ID(+)
AND QUOTE_LINES.QUOTE_HEADER_ID = QUOTE_LINE_ATTRIBS.QUOTE_HEADER_ID(+)
AND QUOTE_HEADERS.PHONE_ID = PHONE_INFO.CONTACT_POINT_ID(+)
AND PARTY_INFO.PARTY_TYPE IN ('PERSON'
, 'ORGANIZATION')
AND QUOTE_HEADERS.ORG_CONTACT_ID IS NULL
AND QUOTE_LINE_ATTRIBS.NAME (+) = 'SUBSCRIPTION' UNION ALL SELECT DISTINCT QUOTE_HEADERS.ROWID ROW_ID
, QUOTE_HEADERS.QUOTE_HEADER_ID QUOTE_HEADER_ID
, QUOTE_HEADERS.QUOTE_NAME QUOTE_NAME
, QUOTE_HEADERS.QUOTE_NUMBER QUOTE_NUMBER
, QUOTE_HEADERS.QUOTE_VERSION QUOTE_VERSION
, QUOTE_HEADERS.QUOTE_EXPIRATION_DATE QUOTE_EXPIRATION_DATE
, QUOTE_HEADERS.QUOTE_STATUS_ID QUOTE_STATUS_ID
, QUOTE_HEADERS.ORDER_ID ORDER_ID
, ORDER_HEADERS.ORDER_NUMBER ORDER_NUMBER
, QUOTE_HEADERS.ORG_ID ORG_ID
, DECODE(PARTY_INFO.PARTY_TYPE
, 'ORGANIZATION'
, PARTY_INFO.PARTY_NAME
, NULL) ORGANIZATION_NAME
, QUOTE_HEADERS.PARTY_ID PARTY_ID
, PARTY_INFO.PARTY_TYPE PARTY_TYPE
, DECODE(PARTY_INFO.PARTY_TYPE
, 'ORGANIZATION'
, CONTACT_INFO.PERSON_FIRST_NAME
, PARTY_INFO.PERSON_FIRST_NAME) CONTACT_FIRST_NAME
, DECODE(PARTY_INFO.PARTY_TYPE
, 'ORGANIZATION'
, CONTACT_INFO.PERSON_MIDDLE_NAME
, PARTY_INFO.PERSON_MIDDLE_NAME) CONTACT_MIDDLE_NAME
, DECODE(PARTY_INFO.PARTY_TYPE
, 'ORGANIZATION'
, CONTACT_INFO.PERSON_LAST_NAME
, PARTY_INFO.PERSON_LAST_NAME) CONTACT_LAST_NAME
, QUOTE_HEADERS.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, ACCOUNT_INFO.ACCOUNT_NUMBER ACCOUNT_NUMBER
, QUOTE_HEADERS.ORG_CONTACT_ID ORG_CONTACT_ID
, QUOTE_HEADERS.PHONE_ID PHONE_ID
, PHONE_INFO.PHONE_AREA_CODE || PHONE_INFO.PHONE_NUMBER FULL_PHONE_NUMBER
, QUOTE_HEADERS.ORDERED_DATE ORDERED_DATE
, QUOTE_HEADERS.ORDER_TYPE_ID ORDER_TYPE_ID
, QUOTE_HEADERS.TOTAL_QUOTE_PRICE TOTAL_QUOTE_PRICE
, QUOTE_LINE_ATTRIBS.NAME LINE_ATTRIBS_NAME
, QUOTE_LINE_ATTRIBS.VALUE SUBSCRIPTION_VALUE
FROM ASO_QUOTE_HEADERS_ALL QUOTE_HEADERS
, ASO_QUOTE_LINES_ALL QUOTE_LINES
, ASO_QUOTE_LINE_ATTRIBS_EXT QUOTE_LINE_ATTRIBS
, HZ_PARTIES PARTY_INFO
, HZ_CUST_ACCOUNTS ACCOUNT_INFO
, HZ_CONTACT_POINTS PHONE_INFO
, HZ_ORG_CONTACTS ORG_CONTACT
, HZ_PARTIES CONTACT_INFO
, HZ_PARTY_RELATIONSHIPS PR
, AR_LOOKUPS LK
, OE_ORDER_HEADERS ORDER_HEADERS
WHERE QUOTE_HEADERS.PARTY_ID = PARTY_INFO.PARTY_ID
AND ACCOUNT_INFO.CUST_ACCOUNT_ID(+) = QUOTE_HEADERS.CUST_ACCOUNT_ID
AND QUOTE_HEADERS.PHONE_ID = PHONE_INFO.CONTACT_POINT_ID(+)
AND QUOTE_HEADERS.ORG_CONTACT_ID = ORG_CONTACT.ORG_CONTACT_ID(+)
AND QUOTE_HEADERS.QUOTE_HEADER_ID = QUOTE_LINES.QUOTE_HEADER_ID
AND QUOTE_HEADERS.ORDER_ID = ORDER_HEADERS.HEADER_ID(+)
AND QUOTE_LINES.QUOTE_LINE_ID = QUOTE_LINE_ATTRIBS.QUOTE_LINE_ID(+)
AND QUOTE_LINES.QUOTE_HEADER_ID = QUOTE_LINE_ATTRIBS.QUOTE_HEADER_ID(+)
AND ORG_CONTACT.PARTY_RELATIONSHIP_ID = PR.PARTY_RELATIONSHIP_ID
AND PR.PARTY_RELATIONSHIP_TYPE IN ( 'CONTACT'
, 'CONTACT_OF')
AND PR.SUBJECT_ID = CONTACT_INFO.PARTY_ID
AND LK.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND LK.LOOKUP_CODE(+) = ORG_CONTACT.TITLE
AND QUOTE_LINE_ATTRIBS.NAME (+) = 'SUBSCRIPTION'

Columns