[Home] [Help]
| Product: | ICX - Oracle iProcurement |
|---|---|
| Description: | Project Related Sales Order View |
| Implementation/DBA Data: | Not implemented in this database |
SELECT DISTINCT L.PROJECT_ID
, L.TASK_ID
, PROJ.PROJECT_NUMBER
, TASK.TASK_NUMBER
, H.ORDER_NUMBER
, H.PURCHASE_ORDER_NUM
, SOLKP1.MEANING ORDER_CATEGORY
, TYPE.NAME ORDER_TYPE
, TERM.NAME TERMS_NAME
, CUST.CUSTOMER_NUMBER
, CUST.CUSTOMER_NAME
, H.DATE_ORDERED
, H.DATE_REQUESTED_CURRENT
, H.CANCELLED_FLAG
, TO_CHAR(OE_QUERY.ORDER_TOTAL(H.HEADER_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(H.CURRENCY_CODE
, 30)) ORDER_TOTAL
, H.CURRENCY_CODE
, H.TAX_EXEMPT_FLAG
, INVADDR.CITY|| '
, ' ||INVADDR.STATE INVOICE_TO_LOCATION
, SHIPADDR.CITY|| ' ' ||SHIPADDR.STATE SHIP_TO_LOCATION
, SOLKP3.MEANING FREIGHT_TERMS
, H.SHIP_METHOD_CODE
, SOLKP2.MEANING SHIPMENT_PRIORITY
, H.SHIP_PARTIAL_FLAG
, H.ORIGINAL_SYSTEM_REFERENCE
, H.ORIGINAL_SYSTEM_SOURCE_CODE
, H.HEADER_ID
, H.ORDERED_BY_CONTACT_ID
, H.CUSTOMER_ID
, INVADDR.ADDRESS_ID INVOICE_TO_ADDRESS_ID
, SHIPADDR.ADDRESS_ID SHIP_TO_ADDRESS_ID
FROM RA_TERMS TERM
, SO_LOOKUPS SOLKP3
, SO_LOOKUPS SOLKP2
, SO_LOOKUPS SOLKP1
, SO_ORDER_TYPES TYPE
, RA_ADDRESSES INVADDR
, RA_ADDRESSES SHIPADDR
, RA_SITE_USES INVLOC
, RA_SITE_USES SHIPLOC
, RA_CUSTOMERS CUST
, SO_HEADERS H
, SO_LINES L
, PJM_PROJECTS_V PROJ
, PA_TASKS TASK
WHERE H.HEADER_ID = L.HEADER_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 TYPE.ORDER_TYPE_ID = H.ORDER_TYPE_ID
AND SOLKP3.LOOKUP_CODE(+) = H.FREIGHT_TERMS_CODE
AND SOLKP3.LOOKUP_TYPE (+)= 'FREIGHT_TERMS'
AND SOLKP2.LOOKUP_CODE(+) = H.SHIPMENT_PRIORITY_CODE
AND SOLKP2.LOOKUP_TYPE(+) = 'SHIPMENT_PRIORITY'
AND SOLKP1.LOOKUP_TYPE(+) = 'ORDER_CATEGORY'
AND SOLKP1.LOOKUP_CODE(+) = H.ORDER_CATEGORY
AND PROJ.PROJECT_ID = L.PROJECT_ID
AND TASK.TASK_ID (+) = L.TASK_ID