VIEW: APPS.CE_SO_FC_ORDERS_V

Source

View Text - Preformatted

SELECT H.ORG_ID, /* 1 */ H.TRANSACTIONAL_CURR_CODE, /* 2 */ DECODE( L.LINE_CATEGORY_CODE, 'ORDER', 1,'RETURN', -1, 0 ) /* 3 */ * (( (NVL( L.ORDERED_QUANTITY, 0 ) - NVL( L.INVOICED_QUANTITY, 0 ) ) * (L.UNIT_SELLING_PRICE) ) + DECODE((NVL(L.ORDERED_QUANTITY,0) - NVL(L.INVOICED_QUANTITY,0)),0,0,NVL(L.TAX_VALUE,0))), DECODE( L.LINE_CATEGORY_CODE, 'ORDER', 1,'RETURN', -1, 0 ) /* 4 */ * (( (NVL( L.ORDERED_QUANTITY, 0 ) - NVL( L.INVOICED_QUANTITY, 0 ) ) * (L.UNIT_SELLING_PRICE) ) + DECODE((NVL(L.ORDERED_QUANTITY,0) - NVL(L.INVOICED_QUANTITY,0)),0,0,NVL(L.TAX_VALUE,0))), TRUNC( NVL( H.ORDERED_DATE, SYSDATE ) ), /* 5 */ TRUNC( NVL( L.REQUEST_DATE, /* 6 */ NVL( H.REQUEST_DATE, SYSDATE ) ) ), H.BOOKED_FLAG, /* 7 */ H.CONVERSION_TYPE_CODE, /* 8 */ DECODE( H.CONVERSION_TYPE_CODE, NULL, NULL, /* 9 */ 'User', H.CONVERSION_RATE_DATE, NVL( H.REQUEST_DATE, TRUNC(SYSDATE ) ) ), 1, /* 10 */ L.COMMITMENT_ID, /* 11 */ CP_CLASS.PROFILE_CLASS_ID, /* 12 */ L.PROJECT_ID /* 13 */, H.ORDER_TYPE_ID, /* 14 */ H.ORDER_NUMBER, /* 15 */ L.LINE_NUMBER, /* 16 */ L.FLOW_STATUS_CODE, /* 17 */ CP_CLASS.NAME, /* 18 */ L.LINE_ID /* 19 */ FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS_ALL H, HZ_CUSTOMER_PROFILES CP_SITE, HZ_CUSTOMER_PROFILES CP_CUST, HZ_CUST_PROFILE_CLASSES CP_CLASS WHERE H.OPEN_FLAG = 'Y' AND L.HEADER_ID = H.HEADER_ID AND NVL( L.INVOICE_INTERFACE_STATUS_CODE, 'NO' ) IN ('NO','PARTIAL') AND CP_CLASS.PROFILE_CLASS_ID = DECODE(CP_SITE.CUST_ACCOUNT_PROFILE_ID, NULL, CP_CUST.PROFILE_CLASS_ID, CP_SITE.PROFILE_CLASS_ID) AND NVL(H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID) = CP_SITE.SITE_USE_ID(+) AND H.SOLD_TO_ORG_ID = CP_CUST.CUST_ACCOUNT_ID AND CP_CUST.SITE_USE_ID IS NULL

View Text - HTML Formatted

SELECT H.ORG_ID
, /* 1 */ H.TRANSACTIONAL_CURR_CODE
, /* 2 */ DECODE( L.LINE_CATEGORY_CODE
, 'ORDER'
, 1
, 'RETURN'
, -1
, 0 ) /* 3 */ * (( (NVL( L.ORDERED_QUANTITY
, 0 ) - NVL( L.INVOICED_QUANTITY
, 0 ) ) * (L.UNIT_SELLING_PRICE) ) + DECODE((NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0
, 0
, NVL(L.TAX_VALUE
, 0)))
, DECODE( L.LINE_CATEGORY_CODE
, 'ORDER'
, 1
, 'RETURN'
, -1
, 0 ) /* 4 */ * (( (NVL( L.ORDERED_QUANTITY
, 0 ) - NVL( L.INVOICED_QUANTITY
, 0 ) ) * (L.UNIT_SELLING_PRICE) ) + DECODE((NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0
, 0
, NVL(L.TAX_VALUE
, 0)))
, TRUNC( NVL( H.ORDERED_DATE
, SYSDATE ) )
, /* 5 */ TRUNC( NVL( L.REQUEST_DATE
, /* 6 */ NVL( H.REQUEST_DATE
, SYSDATE ) ) )
, H.BOOKED_FLAG
, /* 7 */ H.CONVERSION_TYPE_CODE
, /* 8 */ DECODE( H.CONVERSION_TYPE_CODE
, NULL
, NULL
, /* 9 */ 'USER'
, H.CONVERSION_RATE_DATE
, NVL( H.REQUEST_DATE
, TRUNC(SYSDATE ) ) )
, 1
, /* 10 */ L.COMMITMENT_ID
, /* 11 */ CP_CLASS.PROFILE_CLASS_ID
, /* 12 */ L.PROJECT_ID /* 13 */
, H.ORDER_TYPE_ID
, /* 14 */ H.ORDER_NUMBER
, /* 15 */ L.LINE_NUMBER
, /* 16 */ L.FLOW_STATUS_CODE
, /* 17 */ CP_CLASS.NAME
, /* 18 */ L.LINE_ID /* 19 */
FROM OE_ORDER_LINES_ALL L
, OE_ORDER_HEADERS_ALL H
, HZ_CUSTOMER_PROFILES CP_SITE
, HZ_CUSTOMER_PROFILES CP_CUST
, HZ_CUST_PROFILE_CLASSES CP_CLASS
WHERE H.OPEN_FLAG = 'Y'
AND L.HEADER_ID = H.HEADER_ID
AND NVL( L.INVOICE_INTERFACE_STATUS_CODE
, 'NO' ) IN ('NO'
, 'PARTIAL')
AND CP_CLASS.PROFILE_CLASS_ID = DECODE(CP_SITE.CUST_ACCOUNT_PROFILE_ID
, NULL
, CP_CUST.PROFILE_CLASS_ID
, CP_SITE.PROFILE_CLASS_ID)
AND NVL(H.INVOICE_TO_ORG_ID
, H.SHIP_TO_ORG_ID) = CP_SITE.SITE_USE_ID(+)
AND H.SOLD_TO_ORG_ID = CP_CUST.CUST_ACCOUNT_ID
AND CP_CUST.SITE_USE_ID IS NULL