VIEW: APPS.AST_ORDER_HEADERS_V

Source

View Text - Preformatted

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 , res.resource_name salesrep_name , h.salesrep_id , h.sales_channel_code , oe_lookups_sales_channel.meaning sales_channel , '' 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, jtf_rs_salesreps salesreps, jtf_rs_resource_extns_vl res, hz_parties sold_party, hz_relationships sold_rel, hz_cust_accounts sold_cust, hz_org_contacts sold_cont, 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_cont.party_relationship_id(+)= sold_rel.relationship_id 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_cont.title AND h.org_id = hr_op_units.organization_id(+) AND h.salesrep_id = salesreps.salesrep_id(+) AND res.resource_id = salesreps.resource_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'

View Text - HTML Formatted

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
, RES.RESOURCE_NAME SALESREP_NAME
, H.SALESREP_ID
, H.SALES_CHANNEL_CODE
, OE_LOOKUPS_SALES_CHANNEL.MEANING SALES_CHANNEL
, '' 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
, JTF_RS_SALESREPS SALESREPS
, JTF_RS_RESOURCE_EXTNS_VL RES
, HZ_PARTIES SOLD_PARTY
, HZ_RELATIONSHIPS SOLD_REL
, HZ_CUST_ACCOUNTS SOLD_CUST
, HZ_ORG_CONTACTS SOLD_CONT
, 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_CONT.PARTY_RELATIONSHIP_ID(+)= SOLD_REL.RELATIONSHIP_ID
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_CONT.TITLE
AND H.ORG_ID = HR_OP_UNITS.ORGANIZATION_ID(+)
AND H.SALESREP_ID = SALESREPS.SALESREP_ID(+)
AND RES.RESOURCE_ID = SALESREPS.RESOURCE_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'