Product: | IBE - iStore |
---|---|
Description: | |
Implementation/DBA Data: | APPS.IBE_RETURN_SUM_V |
SELECT OH.HEADER_ID
, OH.CREATED_BY
, OH.SOURCE_DOCUMENT_ID
, OH.ORIG_SYS_DOCUMENT_REF
, OH.ORDER_NUMBER
, OH.ORDERED_DATE
, OH.FLOW_STATUS_CODE ORDER_STATUS
, OH.SOLD_TO_ORG_ID CUST_ACCOUNT_ID
, CUST.PARTY_ID
, CUST.PARTY_NAME CUSTOMER_NAME
, OH.CUST_PO_NUMBER
, SUBSTR(OH.CREDIT_CARD_NUMBER
, (LENGTH(OH.CREDIT_CARD_NUMBER)-3)
, 4)
, OH.ORDER_CATEGORY_CODE
, OH.BOOKED_DATE
, OH.SOLD_TO_CONTACT_ID
, OH.CANCELLED_FLAG
, OH.OPEN_FLAG
, OH.BOOKED_FLAG
, OH.SOURCE_DOCUMENT_ID
, OH.SOURCE_DOCUMENT_TYPE_ID
, OEL.MEANING
, OH.ORG_ID ORG_ID
FROM OE_ORDER_HEADERS_ALL OH
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, OE_LOOKUPS OEL
WHERE OH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND OEL.LOOKUP_CODE = OH.FLOW_STATUS_CODE
AND OEL.LOOKUP_TYPE = 'FLOW_STATUS'
AND OH.ORDER_CATEGORY_CODE IN ('MIXED'
, 'RETURN')
AND EXISTS (SELECT 'X'
FROM OE_ORDER_LINES_ALL OL
WHERE OH.HEADER_ID = OL.HEADER_ID
AND OL.LINE_CATEGORY_CODE = 'RETURN')