[Home] [Help]
View: AMS_IBA_OP_PURCHASING_DETAIL_V
Product: | AMS - Marketing |
Description: | It contains the purchasing information of customers |
Implementation/DBA Data: |
Not implemented in this database
|
View Text
SELECT HEADER.HEADER_ID
, HEADER.HEADER_ID
, HEADER.REQUEST_DATE
, 1
, LINE.INVENTORY_ITEM_ID
, ITEM.ITEM_TYPE
, TO_CHAR(HZ.PARTY_ID) CUSTOMER_ID
FROM OE_ORDER_HEADERS_ALL HEADER
, OE_ORDER_LINES_ALL LINE
, HZ_CUST_ACCOUNT_ROLES ACCOUNT_ROLE
, AMS_IBA_OP_ITEM_V ITEM
, HZ_PARTY_RELATIONSHIPS HZ
, AMS_IBA_OP_CUSTOMER_V CUST
WHERE HEADER.CANCELLED_FLAG = 'N'
AND HEADER.HEADER_ID = LINE.HEADER_ID
AND HEADER.SHIP_TO_CONTACT_ID = ACCOUNT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ACCOUNT_ROLE.PARTY_ID=HZ.PARTY_ID
AND CUST.ID = HZ.PARTY_ID
AND ITEM.ID = LINE.INVENTORY_ITEM_ID
AND (LINE.ORDERED_QUANTITY-NVL(LINE.CANCELLED_QUANTITY
, 0)) > 0 UNION ALL SELECT HEADER.HEADER_ID
, HEADER.HEADER_ID
, HEADER.REQUEST_DATE
, 1
, LINE.INVENTORY_ITEM_ID
, ITEM.ITEM_TYPE
, TO_CHAR(HZ.PARTY_ID) CUSTOMER_ID
FROM OE_ORDER_HEADERS_ALL HEADER
, OE_ORDER_LINES_ALL LINE
, HZ_CUST_ACCOUNTS ACCOUNT
, AMS_IBA_OP_ITEM_V ITEM
, HZ_PARTIES HZ
, AMS_IBA_OP_CUSTOMER_V CUST
WHERE HEADER.CANCELLED_FLAG = 'N'
AND HEADER.HEADER_ID = LINE.HEADER_ID
AND HEADER.SOLD_TO_ORG_ID = ACCOUNT.CUST_ACCOUNT_ID
AND ACCOUNT.PARTY_ID=HZ.PARTY_ID
AND CUST.ID = HZ.PARTY_ID
AND ITEM.ID = LINE.INVENTORY_ITEM_ID
AND HZ.PARTY_TYPE = 'PERSON'
AND (LINE.ORDERED_QUANTITY-NVL(LINE.CANCELLED_QUANTITY
, 0)) > 0
Columns
Name |
ORDER_ID |
SESSION_ID |
TIMESTAMP |
PURCHASING_VALUE |
ITEM_ID |
ITEM_TYPE |
CUSTOMER_ID |