View: OE_AS_ORDER_HEADERS_V

Product:	ONT - Order Management
Description:	View to provide details of all existing orders in the Order Management.
Implementation/DBA Data:	APPS.OE_AS_ORDER_HEADERS_V

View Text

SELECT H.HEADER_ID
, H.ORG_ID
, HR_OP_UNITS.NAME ORG_NAME
, H.ORDER_NUMBER
, H.FLOW_STATUS_CODE
, OE_LOOKUPS_FLOW_STATUS.MEANING FLOW_STATUS
, H.ORDER_TYPE_ID
, OT.NAME ORDER_TYPE
, H.ORDER_SOURCE_ID
, OS.NAME ORDER_SOURCE
, H.CREATION_DATE
, H.TRANSACTIONAL_CURR_CODE
, H.SOLD_TO_ORG_ID
, H.SOLD_TO_CONTACT_ID
, SOLD_PARTY.PERSON_LAST_NAME || DECODE(SOLD_PARTY. PERSON_FIRST_NAME
, NULL
, NULL
, '
, ' || SOLD_PARTY.PERSON_FIRST_NAME) || DECODE(SOLD_ARL.MEANING
, NULL
, NULL
, ' '||SOLD_ARL .MEANING) SOLD_TO_CONTACT
, CUST_ACCT.ACCOUNT_NUMBER
, CUST_ACCT.PARTY_ID
, JTF_EXTN.RESOURCE_NAME SALESREP_NAME
, H.SALESREP_ID
, H.SALES_CHANNEL_CODE
, OE_LOOKUPS_SALES_CHANNEL.MEANING SALES_CHANNEL
, NULL ORDER_TOTAL
, OE_OE_TOTALS_SUMMARY.PRT_ORDER_TOTAL(HEADER_ID) ORDER_TOTAL_NUMERIC
FROM OE_ORDER_HEADERS_ALL H
, OE_ORDER_SOURCES OS
, OE_TRANSACTION_TYPES_TL OT
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_ACCOUNT_ROLES SOLD_ROLES
, RA_SALESREPS_ALL SALESREPS
, JTF_RS_RESOURCE_EXTNS_VL JTF_EXTN
, HZ_PARTIES SOLD_PARTY
, HZ_RELATIONSHIPS SOLD_REL
, HZ_CUST_ACCOUNTS SOLD_CUST
, AR_LOOKUPS SOLD_ARL
, HR_OPERATING_UNITS HR_OP_UNITS
, OE_LOOKUPS OE_LOOKUPS_FLOW_STATUS
, OE_LOOKUPS OE_LOOKUPS_SALES_CHANNEL
WHERE H.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID
AND OT.LANGUAGE = USERENV('LANG')
AND H.ORDER_SOURCE_ID = OS.ORDER_SOURCE_ID(+)
AND H.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND H.SOLD_TO_CONTACT_ID = SOLD_ROLES.CUST_ACCOUNT_ROLE_ID(+)
AND SOLD_ROLES.PARTY_ID = SOLD_REL.PARTY_ID(+)
AND SOLD_ROLES.ROLE_TYPE(+) = 'CONTACT'
AND SOLD_REL.SUBJECT_ID = SOLD_PARTY.PARTY_ID(+)
AND SOLD_ROLES.CUST_ACCOUNT_ID = SOLD_CUST.CUST_ACCOUNT_ID(+)
AND NVL(SOLD_REL.OBJECT_ID
, 1) = NVL(SOLD_CUST.PARTY_ID
, 1)
AND SOLD_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND SOLD_ARL.LOOKUP_CODE(+) = SOLD_PARTY.PERSON_PRE_NAME_ADJUNCT AND H.ORG_ID = HR_OP_UNITS.ORGANIZATION_ID(+) AND H.SALESREP_ID = SALESREPS.SALESREP_ID(+)
AND NVL(H.ORG_ID
, -99) = NVL(SALESREPS.ORG_ID(+)
, -99) AND H.FLOW_STATUS_CODE = OE_LOOKUPS_FLOW_STATUS.LOOKUP_CODE(+) AND OE_LOOKUPS_FLOW_STATUS.LOOKUP_TYPE(+) = 'FLOW_STATUS' AND H.SALES_CHANNEL_CODE = OE_LOOKUPS_SALES_CHANNEL.LOOKUP_CODE(+) AND OE_LOOKUPS_SALES_CHANNEL.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND SALESREPS.RESOURCE_ID =JTF_EXTN.RESOURCE_ID (+)

Columns