Product: | ASO - Order Capture |
---|---|
Description: | view provides order related information |
Implementation/DBA Data: | APPS.ASO_I_ORDER_HEADERS_V |
SELECT OH.HEADER_ID
, OH.ORG_ID
, OH.SOURCE_DOCUMENT_TYPE_ID
, OH.SOURCE_DOCUMENT_ID
, OH.LAST_UPDATE_DATE
, OH.CREATION_DATE
, OH.ORIG_SYS_DOCUMENT_REF
, OH.ORDER_NUMBER
, OH.ORDER_TYPE_ID
, OT.NAME ORDER_TYPE_NAME
, OH.ORDERED_DATE
, OH.PRICE_LIST_ID
, OH.SOLD_TO_ORG_ID
, CUST.PARTY_NAME
, ACCT.ACCOUNT_NUMBER
, ACCT.PARTY_ID
, ROLE.PARTY_ID
, ROLE.CUST_ACCOUNT_ID
, OH.SALESREP_ID
, OH.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.ADDRESS_LINE_1
, INVADDR.ADDRESS_LINE_2
, INVADDR.ADDRESS_LINE_3
, NULL
, INVADDR.TOWN_OR_CITY
, INVADDR.STATE
, INVADDR.POSTAL_CODE
, NULL
, NULL
, INVADDR.COUNTRY
, NULL
, SHIPADDR.ADDRESS_LINE_1
, SHIPADDR.ADDRESS_LINE_2
, SHIPADDR.ADDRESS_LINE_3
, NULL
, SHIPADDR.TOWN_OR_CITY
, SHIPADDR.STATE
, SHIPADDR.POSTAL_CODE
, NULL
, NULL
, SHIPADDR.COUNTRY
, NULL
, OH.SHIPPING_METHOD_CODE
, OH.FREIGHT_CARRIER_CODE
, OH.FOB_POINT_CODE
, OH.FREIGHT_TERMS_CODE
, OH.SHIPMENT_PRIORITY_CODE
, OH.REQUEST_DATE
, OH.TAX_EXEMPT_FLAG
, OH.TAX_EXEMPT_NUMBER
, OH.TAX_EXEMPT_REASON_CODE
, OH.MARKETING_SOURCE_CODE_ID
, OH.ORDER_CATEGORY_CODE
, OH.VERSION_NUMBER
, OH.CREDIT_CARD_CODE
, OH.CREDIT_CARD_NUMBER
, OH.CREDIT_CARD_EXPIRATION_DATE
FROM OE_ORDER_HEADERS_ALL OH
, OE_TRANSACTION_TYPES_VL OT
, RA_TERMS_VL TERM
, OE_SHIP_TO_ORGS_V SHIPADDR
, OE_INVOICE_TO_ORGS_V INVADDR
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, HZ_CUST_ACCOUNT_ROLES ROLE
WHERE OH.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID
AND OT.TRANSACTION_TYPE_CODE = 'ORDER'
AND OH.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND OH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND OH.SHIP_TO_ORG_ID = SHIPADDR.ORGANIZATION_ID (+)
AND OH.INVOICE_TO_ORG_ID = INVADDR.ORGANIZATION_ID (+)
AND OH.SOLD_TO_CONTACT_ID = ROLE.CUST_ACCOUNT_ROLE_ID (+)
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)