View: XNC_QUOTE_HEADERS_V

View Text

SELECT ASO_QUOTES.ROWID ROW_ID
, ASO_QUOTES.QUOTE_HEADER_ID
, ASO_QUOTES.ORDER_ID
, ASO_QUOTES.ORG_ID
, ASO_QUOTES.LAST_UPDATE_DATE
, ASO_QUOTES.LAST_UPDATED_BY
, ASO_QUOTES.CREATION_DATE
, ASO_QUOTES.CREATED_BY
, ASO_QUOTES.LAST_UPDATE_LOGIN
, ASO_QUOTES.REQUEST_ID
, ASO_QUOTES.PROGRAM_APPLICATION_ID
, ASO_QUOTES.PROGRAM_ID
, ASO_QUOTES.PROGRAM_UPDATE_DATE
, ASO_QUOTES.ORIGINAL_SYSTEM_REFERENCE
, ASO_QUOTES.EMPLOYEE_PERSON_ID
, I_PPF.FIRST_NAME SALESREP_FIRST_NAME
, I_PPF.LAST_NAME SALESREP_LAST_NAME
, RSA.NAME SALESREP_NAME
, ASO_QUOTES.PRICE_LIST_ID
, I_PRICE_LISTS.NAME PRICE_LIST_NAME
, ASO_QUOTES.QUOTE_STATUS_ID
, ASO_STATUS_VL.STATUS_CODE QUOTE_STATUS_CODE
, ASO_STATUS_VL.MEANING QUOTE_STATUS
, ASO_STATUS_VL.UPDATE_ALLOWED_FLAG
, ASO_STATUS_VL.AUTO_VERSION_FLAG
, ASO_QUOTES.QUOTE_SOURCE_CODE
, ASO_QUOTES.PARTY_ID
, DECODE(I_PARTIES.PARTY_TYPE
, 'ORGANIZATION'
, I_PARTIES.PARTY_NAME
, NULL) PARTY_NAME
, I_PARTIES.PARTY_TYPE
, DECODE(I_PARTIES.PARTY_TYPE
, 'ORGANIZATION'
, I_PEOPLE.PERSON_FIRST_NAME
, I_PARTIES.PERSON_FIRST_NAME) PERSON_FIRST_NAME
, DECODE(I_PARTIES.PARTY_TYPE
, 'ORGANIZATION'
, I_PEOPLE.PERSON_MIDDLE_NAME
, I_PARTIES.PERSON_MIDDLE_NAME) PERSON_MIDDLE_NAME
, DECODE(I_PARTIES.PARTY_TYPE
, 'ORGANIZATION'
, I_PEOPLE.PERSON_LAST_NAME
, I_PARTIES.PERSON_LAST_NAME) PERSON_LAST_NAME
, DECODE(I_PARTIES.PARTY_TYPE
, 'ORGANIZATION'
, I_PEOPLE.PERSON_FIRST_NAME
, I_PARTIES.PERSON_FIRST_NAME) || ' ' || DECODE(I_PARTIES.PARTY_TYPE
, 'ORGANIZATION'
, I_PEOPLE.PERSON_LAST_NAME
, I_PARTIES.PERSON_LAST_NAME) PERSON_NAME
, ASO_QUOTES.CUST_ACCOUNT_ID
, I_ACCTS.ACCOUNT_NUMBER
, ASO_QUOTES.ORG_CONTACT_ID
, ASO_QUOTES.PHONE_ID
, I_PHONE.PHONE_AREA_CODE
, I_PHONE.PHONE_NUMBER
, I_PHONE.PHONE_EXTENSION
, I_PHONE.PHONE_LINE_TYPE
, ASO_QUOTES.QUOTE_NAME
, ASO_QUOTES.QUOTE_NUMBER
, ASO_QUOTES.QUOTE_VERSION
, ASO_QUOTES.QUOTE_EXPIRATION_DATE
, ASO_QUOTES.QUOTE_CATEGORY_CODE
, ASO_QUOTES.CURRENCY_CODE
, ASO_QUOTES.EXCHANGE_RATE
, ASO_QUOTES.EXCHANGE_TYPE_CODE
, ASO_QUOTES.EXCHANGE_RATE_DATE
, ASO_QUOTES.ORIG_MKTG_SOURCE_CODE_ID
, ASO_QUOTES.MARKETING_SOURCE_CODE_ID
, I_MKTG_SRC.NAME
, I_MKTG_SRC.SOURCE_CODE
, ASO_QUOTES.ORDERED_DATE
, ASO_QUOTES.ORDER_TYPE_ID
, I_ORDER_TYPES.NAME ORDER_TYPE_NAME
, ASO_TAX.TAX_EXEMPT_NUMBER
, ASO_TAX.TAX_EXEMPT_REASON_CODE
, ASO_TAX.TAX_EXEMPT_FLAG
, ASO_QUOTES.TOTAL_LIST_PRICE
, ASO_QUOTES.TOTAL_ADJUSTED_AMOUNT
, ASO_QUOTES.TOTAL_ADJUSTED_PERCENT
, ASO_QUOTES.TOTAL_TAX
, ASO_QUOTES.SURCHARGE
, ASO_QUOTES.TOTAL_SHIPPING_CHARGE
, ASO_QUOTES.TOTAL_QUOTE_PRICE
, ASO_QUOTES.ACCOUNTING_RULE_ID
, ASO_QUOTES.INVOICING_RULE_ID
, ASO_QUOTES.INVOICE_TO_PARTY_ID
, ASO_QUOTES.INVOICE_TO_PARTY_SITE_ID
, I_INVOICE_PARTIES.PARTY_NAME INVOICE_TO_PARTY_NAME
, I_INVOICE_PARTIES.PERSON_FIRST_NAME INVOICE_TO_CONTACT_FIRST_NAME
, I_INVOICE_PARTIES.PERSON_MIDDLE_NAME INVOICE_TO_CONTACT_MIDDLE_NAME
, I_INVOICE_PARTIES.PERSON_LAST_NAME INVOICE_TO_CONTACT_LAST_NAME
, I_INVOICE_LOCATIONS.ADDRESS1 ORIG_INVOICE_TO_ADDRESS1
, I_INVOICE_LOCATIONS.ADDRESS2 ORIG_INVOICE_TO_ADDRESS2
, I_INVOICE_LOCATIONS.ADDRESS3 ORIG_INVOICE_TO_ADDRESS3
, I_INVOICE_LOCATIONS.ADDRESS4 ORIG_INVOICE_TO_ADDRESS4
, I_INVOICE_LOCATIONS.COUNTRY INVOICE_TO_COUNTRY_CODE
, I_COUNTRY.TERRITORY_SHORT_NAME INVOICE_TO_COUNTRY
, I_INVOICE_LOCATIONS.CITY INVOICE_TO_CITY
, I_INVOICE_LOCATIONS.POSTAL_CODE INVOICE_TO_POSTAL_CODE
, I_INVOICE_LOCATIONS.STATE INVOICE_TO_STATE
, I_INVOICE_LOCATIONS.PROVINCE INVOICE_TO_PROVINCE
, I_INVOICE_LOCATIONS.COUNTY INVOICE_TO_COUNTY
, I_INVOICE_PARTIES.PERSON_FIRST_NAME||' '|| I_INVOICE_PARTIES.PERSON_MIDDLE_NAME||' '|| I_INVOICE_PARTIES.PERSON_LAST_NAME INVOICE_TO_CONTACT_NAME
, I_INVOICE_LOCATIONS.ADDRESS1 INVOICE_TO_ADDRESS1
, I_INVOICE_LOCATIONS.ADDRESS2||' '|| I_INVOICE_LOCATIONS.ADDRESS3||' '|| I_INVOICE_LOCATIONS.ADDRESS4 INVOICE_TO_ADDRESS2
, I_INVOICE_LOCATIONS.CITY|| DECODE(I_INVOICE_LOCATIONS.COUNTY
, NULL
, NULL
, '
, ')|| I_INVOICE_LOCATIONS.COUNTY|| DECODE(I_INVOICE_LOCATIONS.STATE
, NULL
, NULL
, '
, ')|| I_INVOICE_LOCATIONS.STATE|| DECODE(I_INVOICE_LOCATIONS.PROVINCE
, NULL
, NULL
, '
, ')|| I_INVOICE_LOCATIONS.PROVINCE|| DECODE(I_INVOICE_LOCATIONS.POSTAL_CODE
, NULL
, NULL
, '
, ')|| I_INVOICE_LOCATIONS.POSTAL_CODE INVOICE_TO_ADDRESS3
, I_INVOICE_LOCATIONS.COUNTRY INVOICE_TO_ADDRESS4
, ASO_QUOTES.CONTRACT_ID
, ASO_QUOTES.PRICE_FROZEN_DATE
, ASO_QUOTES.QUOTE_PASSWORD
, ASO_QUOTES.PAYMENT_AMOUNT
, ASO_QUOTES.SALES_CHANNEL_CODE
, ASO_QUOTES.ATTRIBUTE_CATEGORY
, ASO_QUOTES.ATTRIBUTE1
, ASO_QUOTES.ATTRIBUTE2
, ASO_QUOTES.ATTRIBUTE3
, ASO_QUOTES.ATTRIBUTE4
, ASO_QUOTES.ATTRIBUTE5
, ASO_QUOTES.ATTRIBUTE6
, ASO_QUOTES.ATTRIBUTE7
, ASO_QUOTES.ATTRIBUTE8
, ASO_QUOTES.ATTRIBUTE9
, ASO_QUOTES.ATTRIBUTE10
, ASO_QUOTES.ATTRIBUTE11
, ASO_QUOTES.ATTRIBUTE12
, ASO_QUOTES.ATTRIBUTE13
, ASO_QUOTES.ATTRIBUTE14
, ASO_QUOTES.ATTRIBUTE15
, I_SHIP_ADDR.SHIPMENT_ID
, I_SHIP_ADDR.SHIP_TO_PARTY_ID
, I_SHIP_ADDR.SHIP_TO_PARTY_SITE_ID
, I_SHIP_ADDR.SHIP_TO_PARTY_NAME
, I_SHIP_ADDR.SHIP_TO_CONTACT_FIRST_NAME
, I_SHIP_ADDR.SHIP_TO_CONTACT_MIDDLE_NAME
, I_SHIP_ADDR.SHIP_TO_CONTACT_LAST_NAME
, I_SHIP_ADDR.SHIP_TO_ADDRESS1 ORIG_SHIP_TO_ADDRESS1
, I_SHIP_ADDR.SHIP_TO_ADDRESS2 ORIG_SHIP_TO_ADDRESS2
, I_SHIP_ADDR.SHIP_TO_ADDRESS3 ORIG_SHIP_TO_ADDRESS3
, I_SHIP_ADDR.SHIP_TO_ADDRESS4 ORIG_SHIP_TO_ADDRESS4
, I_SHIP_ADDR.SHIP_TO_COUNTRY_CODE
, I_SHIP_ADDR.SHIP_TO_COUNTRY
, I_SHIP_ADDR.SHIP_TO_CITY
, I_SHIP_ADDR.SHIP_TO_POSTAL_CODE
, I_SHIP_ADDR.SHIP_TO_STATE
, I_SHIP_ADDR.SHIP_TO_PROVINCE
, I_SHIP_ADDR.SHIP_TO_COUNTY
, I_SHIP_ADDR.SHIP_TO_CONTACT_FIRST_NAME||' '|| I_SHIP_ADDR.SHIP_TO_CONTACT_MIDDLE_NAME||' '|| I_SHIP_ADDR.SHIP_TO_CONTACT_LAST_NAME SHIP_TO_CONTACT_NAME
, I_SHIP_ADDR.SHIP_TO_ADDRESS1 SHIP_TO_ADDRESS1
, I_SHIP_ADDR.SHIP_TO_ADDRESS2||' '|| I_SHIP_ADDR.SHIP_TO_ADDRESS3||' '|| I_SHIP_ADDR.SHIP_TO_ADDRESS4 SHIP_TO_ADDRESS2
, I_SHIP_ADDR.SHIP_TO_CITY|| DECODE(I_SHIP_ADDR.SHIP_TO_COUNTY
, NULL
, NULL
, '
, ')|| I_SHIP_ADDR.SHIP_TO_COUNTY|| DECODE(I_SHIP_ADDR.SHIP_TO_STATE
, NULL
, NULL
, '
, ')|| I_SHIP_ADDR.SHIP_TO_STATE|| DECODE(I_SHIP_ADDR.SHIP_TO_PROVINCE
, NULL
, NULL
, '
, ')|| I_SHIP_ADDR.SHIP_TO_PROVINCE|| DECODE(I_SHIP_ADDR.SHIP_TO_POSTAL_CODE
, NULL
, NULL
, '
, ')|| I_SHIP_ADDR.SHIP_TO_POSTAL_CODE SHIP_TO_ADDRESS3
, I_SHIP_ADDR.SHIP_TO_COUNTRY SHIP_TO_ADDRESS4
, PAYMENT.PAYMENT_ID
, PAYMENT.PAYMENT_TYPE_CODE
, PAYMENT.PAYMENT_TYPE_MEANING
, PAYMENT.PAYMENT_OPTION
, PAYMENT.PAYMENT_OPTION_MEANING
, PAYMENT.PAYMENT_REF_NUMBER
, PAYMENT.PAYMENT_TERM_ID
, PAYMENT.PAYMENT_TERM_NAME
, PAYMENT.CREDIT_CARD_CODE
, PAYMENT.CREDIT_CARD_HOLDER_NAME
, PAYMENT.CREDIT_CARD_EXPIRATION_DATE
, PAYMENT.CREDIT_CARD_APPROVAL_CODE
, PAYMENT.CREDIT_CARD_APPROVAL_DATE
FROM ASO_QUOTE_HEADERS_ALL ASO_QUOTES
, ASO_QUOTE_STATUSES_VL ASO_STATUS_VL
, ASO_TAX_DETAILS ASO_TAX
, ASO_I_CURRENCIES_V I_CURRENCY
, ASO_I_ORDER_TYPES_V I_ORDER_TYPES
, ASO_I_PEOPLE_V I_PEOPLE
, ASO_I_HR_PEOPLE_V I_PPF
, RA_SALESREPS_ALL RSA
, ASO_I_PRICE_LISTS_V I_PRICE_LISTS
, ASO_I_MKTG_SRC_CODES_V I_MKTG_SRC
, ASO_I_PARTIES_V I_INVOICE_PARTIES
, ASO_I_PARTY_ADDRESSES_V I_INVOICE_LOCATIONS
, ASO_I_COUNTRIES_V I_COUNTRY
, ASO_I_PARTIES_V I_PARTIES
, ASO_I_CUST_ACCOUNTS_V I_ACCTS
, ASO_I_CONTACT_POINTS_V I_PHONE
, ASO_SHIPMENTS_V I_SHIP_ADDR
, ASO_PAYMENTS_V PAYMENT
WHERE NVL(ASO_QUOTES.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND ASO_QUOTES.QUOTE_STATUS_ID = ASO_STATUS_VL.QUOTE_STATUS_ID(+)
AND ASO_QUOTES.PRICE_LIST_ID = I_PRICE_LISTS.PRICE_LIST_ID(+)
AND ASO_QUOTES.PARTY_ID = I_PARTIES.PARTY_ID(+)
AND ASO_QUOTES.ORG_CONTACT_ID = I_PEOPLE.PARTY_ID(+)
AND ASO_QUOTES.CURRENCY_CODE = I_CURRENCY.CURRENCY_CODE(+)
AND ASO_QUOTES.MARKETING_SOURCE_CODE_ID = I_MKTG_SRC.SOURCE_CODE_ID (+)
AND ASO_QUOTES.INVOICE_TO_PARTY_SITE_ID = I_INVOICE_LOCATIONS.PARTY_SITE_ID (+)
AND ASO_QUOTES.INVOICE_TO_PARTY_ID = I_INVOICE_PARTIES.PARTY_ID (+)
AND ASO_QUOTES.ORDER_TYPE_ID = I_ORDER_TYPES.ORDER_TYPE_ID (+)
AND ASO_QUOTES.CUST_ACCOUNT_ID = I_ACCTS.CUST_ACCOUNT_ID(+)
AND ASO_QUOTES.PHONE_ID = I_PHONE.CONTACT_POINT_ID(+)
AND ASO_QUOTES.EMPLOYEE_PERSON_ID = I_PPF.PERSON_ID (+)
AND ASO_QUOTES.EMPLOYEE_PERSON_ID = RSA.PERSON_ID (+)
AND I_INVOICE_LOCATIONS.COUNTRY_CODE = I_COUNTRY.TERRITORY_CODE(+)
AND ASO_QUOTES.QUOTE_HEADER_ID = ASO_TAX.QUOTE_HEADER_ID(+)
AND ASO_TAX.QUOTE_LINE_ID(+) IS NULL
AND ASO_QUOTES.QUOTE_HEADER_ID = I_SHIP_ADDR.QUOTE_HEADER_ID(+)
AND I_SHIP_ADDR.QUOTE_LINE_ID(+) IS NULL
AND ASO_QUOTES.QUOTE_HEADER_ID = PAYMENT.QUOTE_HEADER_ID(+)
AND PAYMENT.QUOTE_LINE_ID(+) IS NULL -- END OF DDL SCRIPT FOR XNC_QUOTE_HEADERS_V

Columns