View: XNC_QUOTE_HEADERS_RG_V

View Text

SELECT AQH.ROWID ROW_ID
, AQH.QUOTE_HEADER_ID
, AQH.ORG_ID
, AQH.QUOTE_NUMBER
, AQH.QUOTE_VERSION
, AQH.QUOTE_SOURCE_CODE
, AQS.MEANING QUOTE_STATUS
, (SELECT MIN(CREATION_DATE)
FROM ASO_QUOTE_HEADERS_ALL AQHB
WHERE AQH.QUOTE_NUMBER = AQHB.QUOTE_NUMBER) CREATION_DATE
, AQH.PRICE_FROZEN_DATE
, AQH.QUOTE_EXPIRATION_DATE
, OOH.ORDER_NUMBER
, OOT.NAME ORDER_TYPE_NAME
, AHP.FIRST_NAME||' '||AHP.LAST_NAME SALESPERSON_NAME
, AQH.SALES_CHANNEL_CODE
, XQC.RECURRING_CHARGE
, AQH.CURRENCY_CODE RECURRING_CURRENCY
, XQC.NON_RECURRING_CHARGE
, AQH.CURRENCY_CODE NON_RECURRING_CURRENCY
, HPI.PARTY_NAME INVOICE_TO_CONTACT_NAME
, APADI.ADDRESS1 INVOICE_TO_ADDRESS1
, APADI.ADDRESS2||' '||APADI.ADDRESS3||' '||APADI.ADDRESS4 INVOICE_TO_ADDRESS2
, APADI.CITY|| DECODE(APADI.COUNTY
, NULL
, NULL
, '
, ')||APADI.COUNTY|| DECODE(APADI.STATE
, NULL
, NULL
, '
, ')||APADI.STATE|| DECODE(APADI.PROVINCE
, NULL
, NULL
, '
, ')||APADI.PROVINCE|| DECODE(APADI.POSTAL_CODE
, NULL
, NULL
, '
, ')||APADI.POSTAL_CODE INVOICE_TO_ADDRESS3
, APADI.COUNTRY INVOICE_TO_ADDRESS4
, ASH.SHIP_TO_CONTACT_FIRST_NAME||' '|| ASH.SHIP_TO_CONTACT_MIDDLE_NAME||' '|| ASH.SHIP_TO_CONTACT_LAST_NAME SHIP_TO_CONTACT_NAME
, ASH.SHIP_TO_ADDRESS1 SHIP_TO_ADDRESS1
, ASH.SHIP_TO_ADDRESS2||' '||ASH.SHIP_TO_ADDRESS3||' '||ASH.SHIP_TO_ADDRESS4 SHIP_TO_ADDRESS2
, ASH.SHIP_TO_CITY|| DECODE(ASH.SHIP_TO_COUNTY
, NULL
, NULL
, '
, ')||ASH.SHIP_TO_COUNTY|| DECODE(ASH.SHIP_TO_STATE
, NULL
, NULL
, '
, ')||ASH.SHIP_TO_STATE|| DECODE(ASH.SHIP_TO_PROVINCE
, NULL
, NULL
, '
, ')||ASH.SHIP_TO_PROVINCE|| DECODE(ASH.SHIP_TO_POSTAL_CODE
, NULL
, NULL
, '
, ')||ASH.SHIP_TO_POSTAL_CODE SHIP_TO_ADDRESS3
, ASH.SHIP_TO_COUNTRY SHIP_TO_ADDRESS4
, APA.PAYMENT_TERM_NAME
, APA.PAYMENT_TYPE_MEANING
, APA.PAYMENT_OPTION
, APA.PAYMENT_OPTION_MEANING
, APA.PAYMENT_REF_NUMBER
, APA.CREDIT_CARD_CODE
, APA.CREDIT_CARD_HOLDER_NAME
, APA.CREDIT_CARD_EXPIRATION_DATE
, APA.CREDIT_CARD_APPROVAL_CODE
, APA.CREDIT_CARD_APPROVAL_DATE
, ATD.TAX_CODE
, ATD.TAX_EXEMPT_FLAG
, ATD.TAX_EXEMPT_NUMBER
, ATD.TAX_EXEMPT_REASON_CODE
FROM ASO_I_PARTY_ADDRESSES_V APADI
, JTF_HZ_PARTIES_V HPI
, JTF_HZ_PARTIES_V HPP
, ASO_I_HR_PEOPLE_V AHP
, ASO_QUOTE_STATUSES_VL AQS
, OE_ORDER_TYPES_V OOT
, OE_ORDER_HEADERS_ALL OOH
, ASO_TAX_DETAILS_V ATD
, ASO_PAYMENTS_V APA
, ASO_SHIPMENTS_V ASH
, XNC_QUOTE_CHARGES_V XQC
, ASO_QUOTE_HEADERS_ALL_V AQH
WHERE NVL(AQH.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 AQH.QUOTE_HEADER_ID = XQC.QUOTE_HEADER_ID(+)
AND AQH.QUOTE_HEADER_ID = ASH.QUOTE_HEADER_ID(+)
AND ASH.QUOTE_LINE_ID(+) IS NULL
AND AQH.QUOTE_HEADER_ID = APA.QUOTE_HEADER_ID(+)
AND APA.QUOTE_LINE_ID(+) IS NULL
AND AQH.QUOTE_HEADER_ID = ATD.QUOTE_HEADER_ID(+)
AND ATD.QUOTE_LINE_ID(+) IS NULL
AND ATD.QUOTE_SHIPMENT_ID(+) IS NULL
AND AQH.ORDER_ID = OOH.HEADER_ID(+)
AND AQH.ORDER_TYPE_ID = OOT.ORDER_TYPE_ID(+)
AND AQH.QUOTE_STATUS_ID = AQS.QUOTE_STATUS_ID(+)
AND AQH.EMPLOYEE_PERSON_ID = AHP.PERSON_ID(+)
AND (SYSDATE BETWEEN NVL(AHP.EFFECTIVE_START_DATE
, SYSDATE)
AND NVL(AHP.EFFECTIVE_END_DATE
, SYSDATE))
AND AQH.PARTY_ID = HPP.PARTY_ID(+)
AND AQH.INVOICE_TO_PARTY_ID = HPI.PARTY_ID(+)
AND AQH.INVOICE_TO_PARTY_SITE_ID = APADI.PARTY_SITE_ID(+)

Columns