eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IBE_ORDER_HEADER_V

Source

View Text - Preformatted


SELECT oh.header_id ,oh.org_id ,oh.source_document_type_id ,oh.created_by ,oh.source_document_id ,oh.orig_sys_document_ref ,oh.order_number ,oh.order_category_code ,ordtype.name Order_type ,oe_totals_grp.Get_Order_Total(oh.header_id,null,'ALL') order_total ,oe_totals_grp.Get_Order_Total(oh.header_id,null,'LINES') lines_total ,oe_totals_grp.Get_Order_Total(oh.header_id,null,'TAXES') taxes_total ,oe_totals_grp.Get_Order_Total(oh.header_id,null,'CHARGES') charges_total ,OE_Sys_Parameters.VALUE('INSTALLMENT_OPTIONS', oh.org_id) installment_options ,OE_Sys_Parameters.VALUE('RECURRING_CHARGES', oh.org_id) Recurring_Charges ,oe_totals_grp.Get_PayNow_Total(oh.header_id,null,'ALL') PayNow_order_total ,oe_totals_grp.Get_PayNow_Total(oh.header_id,null,'LINES') PayNow_lines_total ,oe_totals_grp.Get_PayNow_Total(oh.header_id,null,'TAXES') PayNow_taxes_total ,oe_totals_grp.Get_PayNow_Total(oh.header_id,null,'CHARGES') PayNow_charges_total ,flow_status_code order_status ,oe_lookups_flow_status.meaning ,oh.ordered_date ,oh.booked_date , oh.last_update_date ,oh.sold_to_org_id cust_account_id ,sold_to_account.party_id ,sold_to_party.party_name ,oh.sold_to_contact_id ,sold_to_contact.party_name ,IBE_ORDER_UTIL_PVT.get_phone_email(decode(sold_to_party.party_type,'PERSON', sold_to_account.party_id, sold_to_contact.rel_party_id)) ,oh.transactional_curr_code ,oh.cust_po_number ,substr(oh.credit_card_number,(length(oh.credit_card_number)-3),4) ,oh.credit_card_code ,olkp2.meaning ,oh.payment_term_id ,term.name payment_term ,oh.payment_type_code ,olkp3.meaning ,oh.agreement_id ,oat.name PRICING_AGREEMENT_NAME ,oh.invoice_to_org_id ,inv_part.party_id ,inv_part.party_name ,oh.invoice_to_contact_id ,inv_contact.party_name ,invaddr.city || ' ' || invaddr.state bill_to_location ,invaddr.address1 ,invaddr.address2 ,invaddr.address3 ,invaddr.address4 ,invaddr.address_lines_phonetic ,invaddr.city ,invaddr.state ,invaddr.postal_code ,invaddr.county ,invaddr.province ,invaddr.country ,null ,oh.ship_to_org_id ,shp_part.party_id ,shp_part.party_name ,oh.ship_to_contact_id ,shp_contact.party_name ship_to_contact_party_name ,shipaddr.city || ' ' || shipaddr.state ship_to_location ,shipaddr.address1 ,shipaddr.address2 ,shipaddr.address3 ,shipaddr.address4 ,shipaddr.address_lines_phonetic ,shipaddr.city ,shipaddr.state ,shipaddr.postal_code ,shipaddr.county ,shipaddr.province ,shipaddr.country ,null ,oh.end_customer_id ,end_cust_party.party_name ,oh.end_customer_contact_id ,end_contact.party_name ,endaddr.city || ' ' || endaddr.state end_location ,endaddr.address1 ,endaddr.address2 ,endaddr.address3 ,endaddr.address4 ,endaddr.address_lines_phonetic ,endaddr.city ,endaddr.state ,endaddr.postal_code ,endaddr.county ,endaddr.province ,endaddr.country ,oh.shipping_method_code , olkp4.meaning shipping_method_desc ,oh.freight_terms_code ,olkp.meaning freight_terms ,oh.shipment_priority_code ,sp_lookup.meaning shipment_priority_code_mng ,oh.partial_shipments_allowed ,oh.request_date ,oh.tax_exempt_flag ,oh.shipping_instructions ,oh.packing_instructions ,oh.context ,oh.attribute1 ,oh.attribute2 ,oh.attribute3 ,oh.attribute4 ,oh.attribute5 ,oh.attribute6 ,oh.attribute7 ,oh.attribute8 ,oh.attribute9 ,oh.attribute10 ,oh.attribute11 ,oh.attribute12 ,oh.attribute13 ,oh.attribute14 ,oh.attribute15 ,oh.global_attribute_category ,oh.global_attribute1 ,oh.global_attribute2 ,oh.global_attribute3 ,oh.global_attribute4 ,oh.global_attribute5 ,oh.global_attribute6 ,oh.global_attribute7 ,oh.global_attribute8 ,oh.global_attribute9 ,oh.global_attribute10 ,oh.global_attribute11 ,oh.global_attribute12 ,oh.global_attribute13 ,oh.global_attribute14 ,oh.global_attribute15 ,oh.global_attribute16 ,oh.global_attribute17 ,oh.global_attribute18 ,oh.global_attribute19 ,oh.global_attribute20 ,inv_acct_site.cust_account_id BILL_TO_ACCOUNT_ID ,inv_site_use.cust_acct_site_id BILL_TO_ACCOUNT_SITE_ID FROM oe_order_headers_all oh, ra_terms_tl term, hz_cust_accounts sold_to_account, hz_parties sold_to_party, ibe_oe_contacts_v sold_to_contact, hz_parties shp_part, hz_cust_accounts shp_accounts, ibe_oe_contacts_v shp_contact, hz_locations shipaddr, hz_party_sites shp_party_site, hz_cust_acct_sites_all shp_acct_site, hz_cust_site_uses_all shp_site_use, hz_parties inv_part, hz_cust_accounts inv_accounts, ibe_oe_contacts_v inv_contact, hz_locations invaddr, hz_party_sites inv_party_site, hz_cust_acct_sites_all inv_acct_site, hz_cust_site_uses_all inv_site_use, hz_parties end_cust_party, hz_cust_accounts end_cust_accounts, ibe_oe_contacts_v end_contact , hz_locations endaddr, hz_party_sites end_party_site, hz_cust_acct_sites_all end_acct_site, hz_cust_site_uses_all end_site_use, oe_agreements_tl oat , oe_lookups olkp, oe_lookups olkp2, oe_lookups olkp3, oe_ship_methods_v olkp4, oe_lookups sp_lookup, oe_lookups oe_lookups_flow_status, oe_transaction_types_tl ordtype WHERE oh.payment_term_id = term.term_id(+) AND term.language (+) = userenv('LANG') AND oh.sold_to_org_id = sold_to_account.cust_account_id AND sold_to_account.party_id = sold_to_party.party_id AND oh.sold_to_contact_id = sold_to_contact.cust_account_role_id(+) AND oh.ship_to_org_id = shp_site_use.site_use_id(+) AND shp_site_use.cust_acct_site_id = shp_acct_site.cust_acct_site_id(+) AND shp_acct_site.party_site_id = shp_party_site.party_site_id(+) AND shp_party_site.location_id = shipaddr.location_id(+) AND shp_acct_site.cust_account_id = shp_accounts.cust_account_id(+) AND shp_accounts.party_id = shp_part.party_id(+) AND oh.ship_to_contact_id = shp_contact.cust_account_role_id(+) AND oh.invoice_to_org_id = inv_site_use.site_use_id(+) AND inv_site_use.cust_acct_site_id = inv_acct_site.cust_acct_site_id(+) AND inv_acct_site.party_site_id = inv_party_site.party_site_id(+) AND inv_party_site.location_id = invaddr.location_id(+) AND inv_acct_site.cust_account_id = inv_accounts.cust_account_id(+) AND inv_accounts.party_id = inv_part.party_id(+) AND oh.invoice_to_contact_id = inv_contact.cust_account_role_id(+) AND oh.end_customer_id = end_cust_accounts.cust_account_id (+) AND end_cust_accounts.party_id = end_cust_party.party_id(+) AND oh.end_customer_site_use_id = end_site_use.site_use_id(+) AND end_site_use.cust_acct_site_id = end_acct_site.cust_acct_site_id(+) AND end_acct_site.party_site_id = end_party_site.party_site_id(+) AND end_party_site.location_id = endaddr.location_id(+) AND oh.end_customer_contact_id = end_contact.cust_account_role_id(+) AND oh.freight_terms_code = olkp.lookup_code(+) AND olkp.lookup_type (+)= 'FREIGHT_TERMS' AND oh.credit_card_code = olkp2.lookup_code(+) AND olkp2.lookup_type (+)= 'CREDIT_CARD' AND oh.payment_type_code = olkp3.lookup_code(+) AND olkp3.lookup_type (+)= 'PAYMENT TYPE' AND sp_lookup.lookup_type (+) = 'SHIPMENT_PRIORITY' AND sp_lookup.lookup_code (+) = oh.shipment_priority_code AND oh.flow_status_code = oe_lookups_flow_status.lookup_code(+) AND oe_lookups_flow_status.lookup_type(+) = 'FLOW_STATUS' AND oh.agreement_id = oat.agreement_id(+) AND oat.language(+) = userenv('LANG') AND oh.shipping_method_code = olkp4.lookup_code(+) AND olkp4.lookup_type(+) = 'SHIP_METHOD' AND oh.order_type_id = ordtype.transaction_type_id AND ordtype.language = userenv('LANG')

View Text - HTML Formatted

SELECT OH.HEADER_ID
, OH.ORG_ID
, OH.SOURCE_DOCUMENT_TYPE_ID
, OH.CREATED_BY
, OH.SOURCE_DOCUMENT_ID
, OH.ORIG_SYS_DOCUMENT_REF
, OH.ORDER_NUMBER
, OH.ORDER_CATEGORY_CODE
, ORDTYPE.NAME ORDER_TYPE
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OH.HEADER_ID
, NULL
, 'ALL') ORDER_TOTAL
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OH.HEADER_ID
, NULL
, 'LINES') LINES_TOTAL
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OH.HEADER_ID
, NULL
, 'TAXES') TAXES_TOTAL
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OH.HEADER_ID
, NULL
, 'CHARGES') CHARGES_TOTAL
, OE_SYS_PARAMETERS.VALUE('INSTALLMENT_OPTIONS'
, OH.ORG_ID) INSTALLMENT_OPTIONS
, OE_SYS_PARAMETERS.VALUE('RECURRING_CHARGES'
, OH.ORG_ID) RECURRING_CHARGES
, OE_TOTALS_GRP.GET_PAYNOW_TOTAL(OH.HEADER_ID
, NULL
, 'ALL') PAYNOW_ORDER_TOTAL
, OE_TOTALS_GRP.GET_PAYNOW_TOTAL(OH.HEADER_ID
, NULL
, 'LINES') PAYNOW_LINES_TOTAL
, OE_TOTALS_GRP.GET_PAYNOW_TOTAL(OH.HEADER_ID
, NULL
, 'TAXES') PAYNOW_TAXES_TOTAL
, OE_TOTALS_GRP.GET_PAYNOW_TOTAL(OH.HEADER_ID
, NULL
, 'CHARGES') PAYNOW_CHARGES_TOTAL
, FLOW_STATUS_CODE ORDER_STATUS
, OE_LOOKUPS_FLOW_STATUS.MEANING
, OH.ORDERED_DATE
, OH.BOOKED_DATE
, OH.LAST_UPDATE_DATE
, OH.SOLD_TO_ORG_ID CUST_ACCOUNT_ID
, SOLD_TO_ACCOUNT.PARTY_ID
, SOLD_TO_PARTY.PARTY_NAME
, OH.SOLD_TO_CONTACT_ID
, SOLD_TO_CONTACT.PARTY_NAME
, IBE_ORDER_UTIL_PVT.GET_PHONE_EMAIL(DECODE(SOLD_TO_PARTY.PARTY_TYPE
, 'PERSON'
, SOLD_TO_ACCOUNT.PARTY_ID
, SOLD_TO_CONTACT.REL_PARTY_ID))
, OH.TRANSACTIONAL_CURR_CODE
, OH.CUST_PO_NUMBER
, SUBSTR(OH.CREDIT_CARD_NUMBER
, (LENGTH(OH.CREDIT_CARD_NUMBER)-3)
, 4)
, OH.CREDIT_CARD_CODE
, OLKP2.MEANING
, OH.PAYMENT_TERM_ID
, TERM.NAME PAYMENT_TERM
, OH.PAYMENT_TYPE_CODE
, OLKP3.MEANING
, OH.AGREEMENT_ID
, OAT.NAME PRICING_AGREEMENT_NAME
, OH.INVOICE_TO_ORG_ID
, INV_PART.PARTY_ID
, INV_PART.PARTY_NAME
, OH.INVOICE_TO_CONTACT_ID
, INV_CONTACT.PARTY_NAME
, INVADDR.CITY || ' ' || INVADDR.STATE BILL_TO_LOCATION
, INVADDR.ADDRESS1
, INVADDR.ADDRESS2
, INVADDR.ADDRESS3
, INVADDR.ADDRESS4
, INVADDR.ADDRESS_LINES_PHONETIC
, INVADDR.CITY
, INVADDR.STATE
, INVADDR.POSTAL_CODE
, INVADDR.COUNTY
, INVADDR.PROVINCE
, INVADDR.COUNTRY
, NULL
, OH.SHIP_TO_ORG_ID
, SHP_PART.PARTY_ID
, SHP_PART.PARTY_NAME
, OH.SHIP_TO_CONTACT_ID
, SHP_CONTACT.PARTY_NAME SHIP_TO_CONTACT_PARTY_NAME
, SHIPADDR.CITY || ' ' || SHIPADDR.STATE SHIP_TO_LOCATION
, SHIPADDR.ADDRESS1
, SHIPADDR.ADDRESS2
, SHIPADDR.ADDRESS3
, SHIPADDR.ADDRESS4
, SHIPADDR.ADDRESS_LINES_PHONETIC
, SHIPADDR.CITY
, SHIPADDR.STATE
, SHIPADDR.POSTAL_CODE
, SHIPADDR.COUNTY
, SHIPADDR.PROVINCE
, SHIPADDR.COUNTRY
, NULL
, OH.END_CUSTOMER_ID
, END_CUST_PARTY.PARTY_NAME
, OH.END_CUSTOMER_CONTACT_ID
, END_CONTACT.PARTY_NAME
, ENDADDR.CITY || ' ' || ENDADDR.STATE END_LOCATION
, ENDADDR.ADDRESS1
, ENDADDR.ADDRESS2
, ENDADDR.ADDRESS3
, ENDADDR.ADDRESS4
, ENDADDR.ADDRESS_LINES_PHONETIC
, ENDADDR.CITY
, ENDADDR.STATE
, ENDADDR.POSTAL_CODE
, ENDADDR.COUNTY
, ENDADDR.PROVINCE
, ENDADDR.COUNTRY
, OH.SHIPPING_METHOD_CODE
, OLKP4.MEANING SHIPPING_METHOD_DESC
, OH.FREIGHT_TERMS_CODE
, OLKP.MEANING FREIGHT_TERMS
, OH.SHIPMENT_PRIORITY_CODE
, SP_LOOKUP.MEANING SHIPMENT_PRIORITY_CODE_MNG
, OH.PARTIAL_SHIPMENTS_ALLOWED
, OH.REQUEST_DATE
, OH.TAX_EXEMPT_FLAG
, OH.SHIPPING_INSTRUCTIONS
, OH.PACKING_INSTRUCTIONS
, OH.CONTEXT
, OH.ATTRIBUTE1
, OH.ATTRIBUTE2
, OH.ATTRIBUTE3
, OH.ATTRIBUTE4
, OH.ATTRIBUTE5
, OH.ATTRIBUTE6
, OH.ATTRIBUTE7
, OH.ATTRIBUTE8
, OH.ATTRIBUTE9
, OH.ATTRIBUTE10
, OH.ATTRIBUTE11
, OH.ATTRIBUTE12
, OH.ATTRIBUTE13
, OH.ATTRIBUTE14
, OH.ATTRIBUTE15
, OH.GLOBAL_ATTRIBUTE_CATEGORY
, OH.GLOBAL_ATTRIBUTE1
, OH.GLOBAL_ATTRIBUTE2
, OH.GLOBAL_ATTRIBUTE3
, OH.GLOBAL_ATTRIBUTE4
, OH.GLOBAL_ATTRIBUTE5
, OH.GLOBAL_ATTRIBUTE6
, OH.GLOBAL_ATTRIBUTE7
, OH.GLOBAL_ATTRIBUTE8
, OH.GLOBAL_ATTRIBUTE9
, OH.GLOBAL_ATTRIBUTE10
, OH.GLOBAL_ATTRIBUTE11
, OH.GLOBAL_ATTRIBUTE12
, OH.GLOBAL_ATTRIBUTE13
, OH.GLOBAL_ATTRIBUTE14
, OH.GLOBAL_ATTRIBUTE15
, OH.GLOBAL_ATTRIBUTE16
, OH.GLOBAL_ATTRIBUTE17
, OH.GLOBAL_ATTRIBUTE18
, OH.GLOBAL_ATTRIBUTE19
, OH.GLOBAL_ATTRIBUTE20
, INV_ACCT_SITE.CUST_ACCOUNT_ID BILL_TO_ACCOUNT_ID
, INV_SITE_USE.CUST_ACCT_SITE_ID BILL_TO_ACCOUNT_SITE_ID
FROM OE_ORDER_HEADERS_ALL OH
, RA_TERMS_TL TERM
, HZ_CUST_ACCOUNTS SOLD_TO_ACCOUNT
, HZ_PARTIES SOLD_TO_PARTY
, IBE_OE_CONTACTS_V SOLD_TO_CONTACT
, HZ_PARTIES SHP_PART
, HZ_CUST_ACCOUNTS SHP_ACCOUNTS
, IBE_OE_CONTACTS_V SHP_CONTACT
, HZ_LOCATIONS SHIPADDR
, HZ_PARTY_SITES SHP_PARTY_SITE
, HZ_CUST_ACCT_SITES_ALL SHP_ACCT_SITE
, HZ_CUST_SITE_USES_ALL SHP_SITE_USE
, HZ_PARTIES INV_PART
, HZ_CUST_ACCOUNTS INV_ACCOUNTS
, IBE_OE_CONTACTS_V INV_CONTACT
, HZ_LOCATIONS INVADDR
, HZ_PARTY_SITES INV_PARTY_SITE
, HZ_CUST_ACCT_SITES_ALL INV_ACCT_SITE
, HZ_CUST_SITE_USES_ALL INV_SITE_USE
, HZ_PARTIES END_CUST_PARTY
, HZ_CUST_ACCOUNTS END_CUST_ACCOUNTS
, IBE_OE_CONTACTS_V END_CONTACT
, HZ_LOCATIONS ENDADDR
, HZ_PARTY_SITES END_PARTY_SITE
, HZ_CUST_ACCT_SITES_ALL END_ACCT_SITE
, HZ_CUST_SITE_USES_ALL END_SITE_USE
, OE_AGREEMENTS_TL OAT
, OE_LOOKUPS OLKP
, OE_LOOKUPS OLKP2
, OE_LOOKUPS OLKP3
, OE_SHIP_METHODS_V OLKP4
, OE_LOOKUPS SP_LOOKUP
, OE_LOOKUPS OE_LOOKUPS_FLOW_STATUS
, OE_TRANSACTION_TYPES_TL ORDTYPE
WHERE OH.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND TERM.LANGUAGE (+) = USERENV('LANG')
AND OH.SOLD_TO_ORG_ID = SOLD_TO_ACCOUNT.CUST_ACCOUNT_ID
AND SOLD_TO_ACCOUNT.PARTY_ID = SOLD_TO_PARTY.PARTY_ID
AND OH.SOLD_TO_CONTACT_ID = SOLD_TO_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OH.SHIP_TO_ORG_ID = SHP_SITE_USE.SITE_USE_ID(+)
AND SHP_SITE_USE.CUST_ACCT_SITE_ID = SHP_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND SHP_ACCT_SITE.PARTY_SITE_ID = SHP_PARTY_SITE.PARTY_SITE_ID(+)
AND SHP_PARTY_SITE.LOCATION_ID = SHIPADDR.LOCATION_ID(+)
AND SHP_ACCT_SITE.CUST_ACCOUNT_ID = SHP_ACCOUNTS.CUST_ACCOUNT_ID(+)
AND SHP_ACCOUNTS.PARTY_ID = SHP_PART.PARTY_ID(+)
AND OH.SHIP_TO_CONTACT_ID = SHP_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OH.INVOICE_TO_ORG_ID = INV_SITE_USE.SITE_USE_ID(+)
AND INV_SITE_USE.CUST_ACCT_SITE_ID = INV_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND INV_ACCT_SITE.PARTY_SITE_ID = INV_PARTY_SITE.PARTY_SITE_ID(+)
AND INV_PARTY_SITE.LOCATION_ID = INVADDR.LOCATION_ID(+)
AND INV_ACCT_SITE.CUST_ACCOUNT_ID = INV_ACCOUNTS.CUST_ACCOUNT_ID(+)
AND INV_ACCOUNTS.PARTY_ID = INV_PART.PARTY_ID(+)
AND OH.INVOICE_TO_CONTACT_ID = INV_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OH.END_CUSTOMER_ID = END_CUST_ACCOUNTS.CUST_ACCOUNT_ID (+)
AND END_CUST_ACCOUNTS.PARTY_ID = END_CUST_PARTY.PARTY_ID(+)
AND OH.END_CUSTOMER_SITE_USE_ID = END_SITE_USE.SITE_USE_ID(+)
AND END_SITE_USE.CUST_ACCT_SITE_ID = END_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND END_ACCT_SITE.PARTY_SITE_ID = END_PARTY_SITE.PARTY_SITE_ID(+)
AND END_PARTY_SITE.LOCATION_ID = ENDADDR.LOCATION_ID(+)
AND OH.END_CUSTOMER_CONTACT_ID = END_CONTACT.CUST_ACCOUNT_ROLE_ID(+)
AND OH.FREIGHT_TERMS_CODE = OLKP.LOOKUP_CODE(+)
AND OLKP.LOOKUP_TYPE (+)= 'FREIGHT_TERMS'
AND OH.CREDIT_CARD_CODE = OLKP2.LOOKUP_CODE(+)
AND OLKP2.LOOKUP_TYPE (+)= 'CREDIT_CARD'
AND OH.PAYMENT_TYPE_CODE = OLKP3.LOOKUP_CODE(+)
AND OLKP3.LOOKUP_TYPE (+)= 'PAYMENT TYPE'
AND SP_LOOKUP.LOOKUP_TYPE (+) = 'SHIPMENT_PRIORITY'
AND SP_LOOKUP.LOOKUP_CODE (+) = OH.SHIPMENT_PRIORITY_CODE
AND OH.FLOW_STATUS_CODE = OE_LOOKUPS_FLOW_STATUS.LOOKUP_CODE(+)
AND OE_LOOKUPS_FLOW_STATUS.LOOKUP_TYPE(+) = 'FLOW_STATUS'
AND OH.AGREEMENT_ID = OAT.AGREEMENT_ID(+)
AND OAT.LANGUAGE(+) = USERENV('LANG')
AND OH.SHIPPING_METHOD_CODE = OLKP4.LOOKUP_CODE(+)
AND OLKP4.LOOKUP_TYPE(+) = 'SHIP_METHOD'
AND OH.ORDER_TYPE_ID = ORDTYPE.TRANSACTION_TYPE_ID
AND ORDTYPE.LANGUAGE = USERENV('LANG')