Product: | ICX - Oracle iProcurement |
---|---|
Description: | Order Entry Header View |
Implementation/DBA Data: | Not implemented in this database |
SELECT H.HEADER_ID
, H.CREATION_DATE
, H.CREATED_BY
, H.LAST_UPDATE_DATE
, H.LAST_UPDATED_BY
, H.LAST_UPDATE_LOGIN
, H.ORDER_NUMBER
, H.ORDER_CATEGORY
, H.ORDER_CATEGORY ORDER_CATEGORY_CODE
, H.ORIGINAL_SYSTEM_SOURCE_CODE
, H.ORIGINAL_SYSTEM_REFERENCE
, H.SOURCE_HEADER_ID
, H.ORDER_TYPE_ID
, H.CYCLE_ID
, H.DATE_ORDERED
, H.OPEN_FLAG
, H.CANCELLED_FLAG
, H.CUSTOMER_ID
, H.ORDERED_BY_CONTACT_ID
, H.SHIP_TO_SITE_USE_ID
, H.SHIP_TO_CONTACT_ID
, H.INVOICE_TO_SITE_USE_ID
, H.INVOICE_TO_CONTACT_ID
, H.PURCHASE_ORDER_NUM
, H.DATE_REQUESTED_CURRENT
, H.SHIPMENT_PRIORITY_CODE
, H.SHIP_METHOD_CODE
, H.FREIGHT_TERMS_CODE
, H.FOB_CODE
, H.SHIP_PARTIAL_FLAG
, H.WAREHOUSE_ID
, H.DEMAND_CLASS_CODE
, H.SHIPPING_INSTRUCTIONS
, H.PACKING_INSTRUCTIONS
, H.AGREEMENT_ID
, H.SALESREP_ID
, H.SALES_CHANNEL_CODE
, H.PRICE_LIST_ID
, H.CURRENCY_CODE
, H.CONVERSION_TYPE_CODE
, H.CONVERSION_RATE
, H.CONVERSION_DATE
, H.TERMS_ID
, H.INVOICING_RULE_ID
, H.ACCOUNTING_RULE_ID
, H.TAX_EXEMPT_NUM
, H.TAX_EXEMPT_REASON_CODE
, H.PAYMENT_TYPE_CODE
, H.PAYMENT_AMOUNT
, H.CHECK_NUMBER
, H.CREDIT_CARD_CODE
, H.CREDIT_CARD_HOLDER_NAME
, H.CREDIT_CARD_NUMBER
, H.CREDIT_CARD_EXPIRATION_DATE
, H.CREDIT_CARD_APPROVAL_CODE
, H.CONTEXT
, H.ATTRIBUTE1
, H.ATTRIBUTE2
, H.ATTRIBUTE3
, H.ATTRIBUTE4
, H.ATTRIBUTE5
, H.ATTRIBUTE6
, H.ATTRIBUTE7
, H.ATTRIBUTE8
, H.ATTRIBUTE9
, H.ATTRIBUTE10
, H.ATTRIBUTE11
, H.ATTRIBUTE12
, H.ATTRIBUTE13
, H.ATTRIBUTE14
, H.ATTRIBUTE15
, ICX_CUSTOMER_PANEL.GET_AR_CODE('TAX_CONTROL_FLAG'
, H.TAX_EXEMPT_FLAG)
, TO_CHAR(OE_QUERY.ORDER_TOTAL(H.HEADER_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(H.CURRENCY_CODE
, 30)) ORDER_TOTAL
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, NVL(CUST.GSA_INDICATOR
, 'N') GSA_FLAG
, SHIPADDR.CITY || ' ' || SHIPADDR.STATE SHIP_TO_LOCATION
, SHIPLOC.GSA_INDICATOR SHIP_TO_GSA_INDICATOR
, SHIPADDR.ADDRESS1 SHIP_TO_ADDRESS1
, SHIPADDR.ADDRESS2 SHIP_TO_ADDRESS2
, SHIPADDR.ADDRESS3 SHIP_TO_ADDRESS3
, SHIPADDR.ADDRESS4 SHIP_TO_ADDRESS4
, DECODE(SHIPADDR.CITY
, NULL
, NULL
, SHIPADDR.CITY || '
, ') || SHIPADDR.STATE || ' ' || SHIPADDR.POSTAL_CODE SHIP_TO_ADDRESS5
, SHIPADDR.ADDRESS_ID SHIP_TO_ADDRESS_ID
, INVADDR.CITY || '
, ' || INVADDR.STATE INVOICE_TO_LOCATION
, INVADDR.ADDRESS1 INVOICE_TO_ADDRESS1
, INVADDR.ADDRESS2 INVOICE_TO_ADDRESS2
, INVADDR.ADDRESS3 INVOICE_TO_ADDRESS3
, INVADDR.ADDRESS4 INVOICE_TO_ADDRESS4
, DECODE(INVADDR.CITY
, NULL
, NULL
, INVADDR.CITY || '
, ') || INVADDR.STATE || ' ' || INVADDR.POSTAL_CODE INVOICE_TO_ADDRESS5
, INVADDR.ADDRESS_ID INVOICE_TO_ADDRESS_ID
, SOLKP2.MEANING SHIPMENT_PRIORITY
, SOLKP3.MEANING FREIGHT_TERMS
, DECODE(H.TAX_EXEMPT_FLAG
, 'S'
, OE_QUERY.STD_TAX_EXEMPT_NUMBER(H.SHIP_TO_SITE_USE_ID
, H.INVOICE_TO_SITE_USE_ID
, H.DATE_ORDERED)) STD_TAX_EXEMPT_NUM
, DECODE(H.TAX_EXEMPT_FLAG
, 'S'
, OE_QUERY.STD_TAX_EXEMPT_REASON(H.SHIP_TO_SITE_USE_ID
, H.INVOICE_TO_SITE_USE_ID
, H.DATE_ORDERED)) STD_TAX_EXEMPT_REASON
, MTLPAR.ORGANIZATION_CODE WAREHOUSE
, MTLPAR.ORGANIZATION_CODE WAREHOUSE_CODE
, TERM.NAME TERMS_NAME
FROM RA_TERMS TERM
, MTL_PARAMETERS MTLPAR
, SO_LOOKUPS SOLKP3
, SO_LOOKUPS SOLKP2
, RA_ADDRESSES INVADDR
, RA_ADDRESSES SHIPADDR
, RA_SITE_USES INVLOC
, RA_SITE_USES SHIPLOC
, RA_CUSTOMERS CUST
, SO_HEADERS H
WHERE H.WAREHOUSE_ID = MTLPAR.ORGANIZATION_ID(+)
AND H.TERMS_ID = TERM.TERM_ID(+)
AND H.INVOICE_TO_SITE_USE_ID = INVLOC.SITE_USE_ID (+)
AND INVLOC.ADDRESS_ID = INVADDR.ADDRESS_ID (+)
AND H.SHIP_TO_SITE_USE_ID = SHIPLOC.SITE_USE_ID (+)
AND SHIPLOC.ADDRESS_ID = SHIPADDR.ADDRESS_ID(+)
AND H.CUSTOMER_ID = CUST.CUSTOMER_ID (+)
AND H.FREIGHT_TERMS_CODE = SOLKP3.LOOKUP_CODE(+)
AND SOLKP3.LOOKUP_TYPE (+)= 'FREIGHT_TERMS'
AND H.SHIPMENT_PRIORITY_CODE = SOLKP2.LOOKUP_CODE(+)
AND SOLKP2.LOOKUP_TYPE(+) = 'SHIPMENT_PRIORITY'