View: XNC_ORDER_SUMMARY_V

View Text

SELECT AIOH.HEADER_ID
, AIOH.ORDER_NUMBER
, AIOH.FLOW_STATUS_CODE ORDER_STATUS
, AIOH.ORDERED_DATE
, AQH.QUOTE_NUMBER
, AIOH.ORDER_TYPE ORDER_TYPE_NAME
, RAS.NAME SALESPERSON
, NULL SALES_CHANNEL
, XITC.RECURRING TOTAL_RECURRING_CHARGES
, XITC.ONE_TIME TOTAL_ONE_TIME_CHARGES
, AIOH.TRANSACTIONAL_CURR_CODE CURR_RECURRING
, AIOH.TRANSACTIONAL_CURR_CODE CURR_ONETIME
, BILLCONT.FIRST_NAME ||' '||BILLCONT.TITLE||' '||BILLCONT.LAST_NAME BILL_TO_CONTACT_NAME
, INVADDR.ADDRESS1||' '||INVADDR.ADDRESS2 BILL_TO_ADDRESS1
, INVADDR.ADDRESS3||' '||INVADDR.ADDRESS4 BILL_TO_ADDRESS2
, DECODE(INVADDR.CITY
, NULL
, NULL
, INVADDR.CITY ||'
, ')||DECODE(INVADDR.COUNTY
, NULL
, NULL
, INVADDR.COUNTY|| '
, ') ||DECODE(INVADDR.PROVINCE
, NULL
, NULL
, INVADDR.PROVINCE || '
, ') ||DECODE(INVADDR.STATE
, NULL
, NULL
, INVADDR.STATE|| ' ') ||DECODE(INVADDR.POSTAL_CODE
, NULL
, NULL
, INVADDR.POSTAL_CODE) BILL_TO_POSTAL_INFO
, INVADDR.COUNTRY BILL_TO_COUNTRY_CODE
, SHIPCONT.FIRST_NAME||' '||SHIPCONT.TITLE||' '||SHIPCONT.LAST_NAME SHIP_TO_CONTACT_NAME
, SHIPADDR.ADDRESS1||' '||SHIPADDR.ADDRESS2 SHIP_TO_ADDRESS1
, SHIPADDR.ADDRESS3||' '||SHIPADDR.ADDRESS4 SHIP_TO_ADDRESS2
, DECODE(SHIPADDR.CITY
, NULL
, NULL
, SHIPADDR.CITY || '
, ')||DECODE(SHIPADDR.COUNTY
, NULL
, NULL
, SHIPADDR.COUNTY|| '
, ') ||DECODE(SHIPADDR.PROVINCE
, NULL
, NULL
, SHIPADDR.PROVINCE || '
, ') ||DECODE(SHIPADDR.STATE
, NULL
, NULL
, SHIPADDR.STATE|| ' ') ||DECODE(SHIPADDR.POSTAL_CODE
, NULL
, NULL
, INVADDR.POSTAL_CODE) SHIP_TO_POSTAL_INFO
, SHIPADDR.COUNTRY SHIP_TO_COUNTRY_CODE
, AIOH.REQUEST_DATE
, AIOH.TAX_EXEMPT_NUMBER
, AIOH.TAX_EXEMPT_REASON_CODE
, AIOH.PAYMENT_TYPE_CODE PAYMENT_TYPE
, RATR.NAME PAYMENT_TERM
, AIOH.CREDIT_CARD_APPROVAL_CODE AUTHORIZATION_CODE
, AIC.CREDIT_CARD CREDIT_CARD
, AIOH.CREDIT_CARD_NUMBER CARD_NUMBER
, AIOH.CREDIT_CARD_EXPIRATION_DATE CARD_EXPIRATION_DATE
, AIOH.CREDIT_CARD_HOLDER_NAME CARD_HOLDER_NAME
FROM OE_ORDER_HEADERS_V AIOH
, RA_SALESREPS RAS
, ASO_QUOTE_HEADERS_V AQH
, XNC_ITEM_TYPE_CHARGES_V XITC
, RA_TERMS RATR
, ASO_I_CREDIT_CARDS_V AIC
, MTL_PARAMETERS SHIP_FROM_ORG
, RA_SITE_USES_ALL SHIP_TO_ORG
, RA_ADDRESSES SHIPADDR
, RA_SITE_USES_ALL BILLORG
, RA_ADDRESSES INVADDR
, OE_SOLD_TO_ORGS_V SOLD_TO_ORG
, RA_CONTACTS SOLDCONT
, RA_CONTACTS SHIPCONT
, RA_CONTACTS BILLCONT
WHERE AIOH.HEADER_ID = AQH.ORDER_ID (+)
AND AIOH.SALESREP_ID =RAS.SALESREP_ID (+)
AND AIOH.HEADER_ID = XITC.HEADER_ID (+)
AND AIOH.PAYMENT_TERM_ID = RATR.TERM_ID (+)
AND AIOH.CREDIT_CARD_CODE = AIC.CREDIT_CARD_CODE (+)
AND AIOH.SOLD_TO_ORG_ID = SOLD_TO_ORG.ORGANIZATION_ID(+)
AND AIOH.SHIP_FROM_ORG_ID = SHIP_FROM_ORG.ORGANIZATION_ID (+)
AND AIOH.SHIP_TO_ORG_ID = SHIP_TO_ORG.SITE_USE_ID (+)
AND SHIP_TO_ORG.ADDRESS_ID = SHIPADDR.ADDRESS_ID (+)
AND AIOH.INVOICE_TO_ORG_ID = BILLORG.SITE_USE_ID (+)
AND BILLORG.ADDRESS_ID = INVADDR.ADDRESS_ID (+)
AND AIOH.SOLD_TO_CONTACT_ID = SOLDCONT.CONTACT_ID (+)
AND AIOH.SHIP_TO_CONTACT_ID = SHIPCONT.CONTACT_ID (+)
AND AIOH.INVOICE_TO_CONTACT_ID = BILLCONT.CONTACT_ID (+)
AND NVL(AIOH.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)

Columns