VIEW: APPS.ASO_I_ORDER_HEADERS_V

Source

View Text - Preformatted

SELECT oh.header_id ,oh.org_id ,oh.source_document_type_id ,oh.source_document_id ,oh.last_update_date ,oh.creation_date ,oh.orig_sys_document_ref ,oh.order_number ,oh.ORDER_TYPE_ID ,ot.name ORDER_TYPE_NAME ,oh.ORDERED_DATE ,oh.PRICE_LIST_ID ,oh.SOLD_TO_ORG_ID ,cust.party_name ,acct.account_number ,acct.party_id ,role.party_id ,role.cust_account_id ,oh.SALESREP_ID ,oh.PAYMENT_AMOUNT ,oh.TRANSACTIONAL_CURR_CODE ,oh.AGREEMENT_ID ,oh.CUST_PO_NUMBER ,oh.PAYMENT_TYPE_CODE ,oh.CANCELLED_FLAG ,oh.OPEN_FLAG ,oh.BOOKED_FLAG ,oh.SHIP_TO_ORG_ID ,oh.INVOICE_TO_ORG_ID ,oh.INVOICE_TO_CONTACT_ID ,oh.ORDER_SOURCE_ID ,oh.RETURN_REASON_CODE ,oh.SALES_CHANNEL_CODE ,oh.CONVERSION_RATE ,oh.CONVERSION_TYPE_CODE ,oh.payment_term_id ,term.name payment_term ,invaddr.address_line_1 ,invaddr.address_line_2 ,invaddr.address_line_3 ,null ,invaddr.town_or_city ,invaddr.state ,invaddr.postal_code , null , null ,invaddr.country , null ,shipaddr.address_line_1 ,shipaddr.address_line_2 ,shipaddr.address_line_3 , null ,shipaddr.town_or_city ,shipaddr.state ,shipaddr.postal_code , null , null ,shipaddr.country , null ,oh.shipping_method_code ,oh.freight_carrier_code ,oh.fob_point_code ,oh.freight_terms_code ,oh.shipment_priority_code ,oh.request_date ,oh.tax_exempt_flag ,oh.tax_exempt_number ,oh.tax_exempt_reason_code ,oh.marketing_source_code_id ,oh.order_category_code ,oh.VERSION_NUMBER ,oh.CREDIT_CARD_CODE ,oh.CREDIT_CARD_NUMBER ,oh.CREDIT_CARD_EXPIRATION_DATE FROM OE_ORDER_HEADERS OH, OE_TRANSACTION_TYPES_VL OT, RA_TERMS_VL TERM, OE_SHIP_TO_ORGS_V SHIPADDR, OE_INVOICE_TO_ORGS_V INVADDR, HZ_CUST_ACCOUNTS ACCT, HZ_PARTIES CUST, HZ_CUST_ACCOUNT_ROLES ROLE WHERE oh.order_type_id = ot.transaction_type_id AND ot.transaction_type_code = 'ORDER' AND oh.payment_term_id = term.term_id(+) AND oh.sold_to_org_id = acct.cust_account_id AND acct.party_id = cust.party_id AND oh.ship_to_org_id = shipaddr.organization_id (+) AND oh.invoice_to_org_id = invaddr.organization_id (+) AND oh.sold_to_contact_id = role.cust_account_role_id (+)

View Text - HTML Formatted

SELECT OH.HEADER_ID
, OH.ORG_ID
, OH.SOURCE_DOCUMENT_TYPE_ID
, OH.SOURCE_DOCUMENT_ID
, OH.LAST_UPDATE_DATE
, OH.CREATION_DATE
, OH.ORIG_SYS_DOCUMENT_REF
, OH.ORDER_NUMBER
, OH.ORDER_TYPE_ID
, OT.NAME ORDER_TYPE_NAME
, OH.ORDERED_DATE
, OH.PRICE_LIST_ID
, OH.SOLD_TO_ORG_ID
, CUST.PARTY_NAME
, ACCT.ACCOUNT_NUMBER
, ACCT.PARTY_ID
, ROLE.PARTY_ID
, ROLE.CUST_ACCOUNT_ID
, OH.SALESREP_ID
, OH.PAYMENT_AMOUNT
, OH.TRANSACTIONAL_CURR_CODE
, OH.AGREEMENT_ID
, OH.CUST_PO_NUMBER
, OH.PAYMENT_TYPE_CODE
, OH.CANCELLED_FLAG
, OH.OPEN_FLAG
, OH.BOOKED_FLAG
, OH.SHIP_TO_ORG_ID
, OH.INVOICE_TO_ORG_ID
, OH.INVOICE_TO_CONTACT_ID
, OH.ORDER_SOURCE_ID
, OH.RETURN_REASON_CODE
, OH.SALES_CHANNEL_CODE
, OH.CONVERSION_RATE
, OH.CONVERSION_TYPE_CODE
, OH.PAYMENT_TERM_ID
, TERM.NAME PAYMENT_TERM
, INVADDR.ADDRESS_LINE_1
, INVADDR.ADDRESS_LINE_2
, INVADDR.ADDRESS_LINE_3
, NULL
, INVADDR.TOWN_OR_CITY
, INVADDR.STATE
, INVADDR.POSTAL_CODE
, NULL
, NULL
, INVADDR.COUNTRY
, NULL
, SHIPADDR.ADDRESS_LINE_1
, SHIPADDR.ADDRESS_LINE_2
, SHIPADDR.ADDRESS_LINE_3
, NULL
, SHIPADDR.TOWN_OR_CITY
, SHIPADDR.STATE
, SHIPADDR.POSTAL_CODE
, NULL
, NULL
, SHIPADDR.COUNTRY
, NULL
, OH.SHIPPING_METHOD_CODE
, OH.FREIGHT_CARRIER_CODE
, OH.FOB_POINT_CODE
, OH.FREIGHT_TERMS_CODE
, OH.SHIPMENT_PRIORITY_CODE
, OH.REQUEST_DATE
, OH.TAX_EXEMPT_FLAG
, OH.TAX_EXEMPT_NUMBER
, OH.TAX_EXEMPT_REASON_CODE
, OH.MARKETING_SOURCE_CODE_ID
, OH.ORDER_CATEGORY_CODE
, OH.VERSION_NUMBER
, OH.CREDIT_CARD_CODE
, OH.CREDIT_CARD_NUMBER
, OH.CREDIT_CARD_EXPIRATION_DATE
FROM OE_ORDER_HEADERS OH
, OE_TRANSACTION_TYPES_VL OT
, RA_TERMS_VL TERM
, OE_SHIP_TO_ORGS_V SHIPADDR
, OE_INVOICE_TO_ORGS_V INVADDR
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, HZ_CUST_ACCOUNT_ROLES ROLE
WHERE OH.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID
AND OT.TRANSACTION_TYPE_CODE = 'ORDER'
AND OH.PAYMENT_TERM_ID = TERM.TERM_ID(+)
AND OH.SOLD_TO_ORG_ID = ACCT.CUST_ACCOUNT_ID
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND OH.SHIP_TO_ORG_ID = SHIPADDR.ORGANIZATION_ID (+)
AND OH.INVOICE_TO_ORG_ID = INVADDR.ORGANIZATION_ID (+)
AND OH.SOLD_TO_CONTACT_ID = ROLE.CUST_ACCOUNT_ROLE_ID (+)