VIEW: APPS.IBE_RETURN_SUM_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')