VIEW: APPS.CSC_ORDER_HEADERS_V

Source

View Text - Preformatted

SELECT h.header_id ,sold_roles.party_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.account_name ,cust_acct.party_id ,salesreps.name salesrep_name ,h.salesrep_id ,h.sales_channel_code ,oe_lookups_sales_channel.meaning sales_channel ,to_char(NVL(oe_oe_totals_summary.prt_order_total(header_id),0.00), fnd_currency.safe_get_format_mask(transactional_curr_code, 30)) ORDER_TOTAL ,h.open_flag ,NVL(oe_oe_totals_summary.prt_order_total(header_id),0.00) UNFORM_ORDER_TOTAL ,decode (h.transaction_phase_code, 'N', h.quote_number, 'F', h.quote_number, NULL, NULL) QUOTE_NUMBER ,h.quote_date , Decode (h.transaction_phase_code, 'F', h.order_number, NULL, h.order_number, 'N', NULL) ORDER_NUMBER_DISP ,h.ordered_date ,h.orig_sys_document_ref FROM oe_order_headers 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, hz_parties sold_party, hz_relationships sold_rel, hz_cust_accounts sold_cust, hz_org_contacts sold_cont, ar_lookups sold_arl, hr_all_organization_units_tl 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 hr_op_units.language(+) = userenv('LANG') 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'

View Text - HTML Formatted

SELECT H.HEADER_ID
, SOLD_ROLES.PARTY_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.ACCOUNT_NAME
, CUST_ACCT.PARTY_ID
, SALESREPS.NAME SALESREP_NAME
, H.SALESREP_ID
, H.SALES_CHANNEL_CODE
, OE_LOOKUPS_SALES_CHANNEL.MEANING SALES_CHANNEL
, TO_CHAR(NVL(OE_OE_TOTALS_SUMMARY.PRT_ORDER_TOTAL(HEADER_ID)
, 0.00)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(TRANSACTIONAL_CURR_CODE
, 30)) ORDER_TOTAL
, H.OPEN_FLAG
, NVL(OE_OE_TOTALS_SUMMARY.PRT_ORDER_TOTAL(HEADER_ID)
, 0.00) UNFORM_ORDER_TOTAL
, DECODE (H.TRANSACTION_PHASE_CODE
, 'N'
, H.QUOTE_NUMBER
, 'F'
, H.QUOTE_NUMBER
, NULL
, NULL) QUOTE_NUMBER
, H.QUOTE_DATE
, DECODE (H.TRANSACTION_PHASE_CODE
, 'F'
, H.ORDER_NUMBER
, NULL
, H.ORDER_NUMBER
, 'N'
, NULL) ORDER_NUMBER_DISP
, H.ORDERED_DATE
, H.ORIG_SYS_DOCUMENT_REF
FROM OE_ORDER_HEADERS 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
, HZ_PARTIES SOLD_PARTY
, HZ_RELATIONSHIPS SOLD_REL
, HZ_CUST_ACCOUNTS SOLD_CUST
, HZ_ORG_CONTACTS SOLD_CONT
, AR_LOOKUPS SOLD_ARL
, HR_ALL_ORGANIZATION_UNITS_TL 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 HR_OP_UNITS.LANGUAGE(+) = USERENV('LANG')
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'