[Home] [Help]
| Product: | IBE - iStore |
|---|---|
| Description: | This view is used to show the Order Summary and Order Header Detail in Post Sales Interaction with Customer. This view is based in aso_i_oe_order_headers_v to get the Order Header Details. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT OH.HEADER_ID
, OH.ORG_ID
, OH.SOURCE_DOCUMENT_TYPE_ID
, OH.CREATED_BY
, OH.LAST_UPDATE_DATE
, OH.CREATION_DATE
, OH.SOURCE_DOCUMENT_ID
, OH.ORDER_NUMBER
, TO_CHAR(ASO_ORDER_INT.TOTAL_ORDER_PRICE(OH.HEADER_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(TRANSACTIONAL_CURR_CODE
, 30)) ORDER_TOTAL
, ASO_ORDER_INT.GET_HEADER_STATUS(OH.HEADER_ID) ORDER_STATUS
, OH.ORDER_TYPE_ID
, OH.ORDERED_DATE
, OH.PRICE_LIST_ID
, OH.SOLD_TO_ORG_ID CUST_ACCOUNT_ID
, CUST.PARTY_ID
, CUST.CUSTOMER_NAME
, OH.SALESREP_ID
, TO_CHAR(OH.PAYMENT_AMOUNT
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(TRANSACTIONAL_CURR_CODE
, 30)) PAYMENT_AMOUNT
, OH.TRANSACTIONAL_CURR_CODE
, OH.AGREEMENT_ID
, OH.CUST_PO_NUMBER
, OH.PAYMENT_TYPE_CODE
, OH.CANCELLED_FLAG
, OH.OPEN_FLAG
, OH.BOOKED_FLAG
, OH.SHIP_TO_ORG_ID
, OH.INVOICE_TO_ORG_ID
, OH.INVOICE_TO_CONTACT_ID
, OH.ORDER_SOURCE_ID
, OH.RETURN_REASON_CODE
, OH.SALES_CHANNEL_CODE
, OH.CONVERSION_RATE
, OH.CONVERSION_TYPE_CODE
, OH.PAYMENT_TERM_ID
, TERM.NAME PAYMENT_TERM
, INVADDR.CITY || ' ' || INVADDR.STATE INVOICE_TO_LOCATION
, INVADDR.ADDRESS1
, INVADDR.ADDRESS2
, INVADDR.ADDRESS3
, INVADDR.ADDRESS4
, INVADDR.CITY
, INVADDR.STATE
, INVADDR.POSTAL_CODE
, INVADDR.COUNTY
, INVADDR.PROVINCE
, INVADDR.COUNTRY
, INVADDR.COUNTRY_CODE
, SHIPADDR.CITY || ' ' || SHIPADDR.STATE SHIP_TO_LOCATION
, SHIPADDR.ADDRESS1
, SHIPADDR.ADDRESS2
, SHIPADDR.ADDRESS3
, SHIPADDR.ADDRESS4
, SHIPADDR.CITY
, SHIPADDR.STATE
, SHIPADDR.POSTAL_CODE
, SHIPADDR.COUNTY
, SHIPADDR.PROVINCE
, SHIPADDR.COUNTRY
, SHIPADDR.COUNTRY_CODE
, OH.SHIPPING_METHOD_CODE
, OH.FREIGHT_CARRIER_CODE
, OH.FOB_POINT_CODE
, OH.FREIGHT_TERMS_CODE
, OLKP.MEANING FREIGHT_TERMS
, OH.SHIPMENT_PRIORITY_CODE
, OH.PARTIAL_SHIPMENTS_ALLOWED
, OH.REQUEST_DATE
, OH.TAX_EXEMPT_FLAG
, OH.TAX_EXEMPT_NUMBER
, OH.TAX_EXEMPT_REASON_CODE
, OH.ORDER_CATEGORY_CODE
FROM ASO_I_OE_ORDER_HEADERS_V OH
, RA_TERMS_VL TERM
, ASO_I_CUST_ACCOUNTS_V CUST
, ASO_I_ACCT_SITE_ADDR_V SHIPADDR
, ASO_I_ACCT_SITE_ADDR_V INVADDR
, OE_LOOKUPS OLKP
WHERE OH.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND OH.SHIP_TO_ORG_ID = SHIPADDR.SITE_USE_ID(+)
AND OH.INVOICE_TO_ORG_ID = INVADDR.SITE_USE_ID(+)
AND OH.SOLD_TO_ORG_ID = CUST.CUST_ACCOUNT_ID(+)
AND OH.FREIGHT_TERMS_CODE = OLKP.LOOKUP_CODE(+)
AND OLKP.LOOKUP_TYPE (+)= 'FREIGHT_TERMS'
AND NVL(OH.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)