VIEW: APPS.CE_SO_FC_ORDERS_NO_TERMS_V

Source

View Text - Preformatted

SELECT H.ORG_ID, H.TRANSACTIONAL_CURR_CODE, DECODE( L.LINE_CATEGORY_CODE, 'ORDER', 1,'RETURN', -1, 0 ) * (( (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 ) * (( (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 ) ), TRUNC( NVL( L.REQUEST_DATE, NVL( H.REQUEST_DATE, SYSDATE ) ) ), H.BOOKED_FLAG, H.CONVERSION_TYPE_CODE, DECODE( H.CONVERSION_TYPE_CODE, NULL, NULL, 'User', H.CONVERSION_RATE_DATE, NVL( H.REQUEST_DATE, TRUNC(SYSDATE ) ) ), 1, L.COMMITMENT_ID, CP_CLASS.PROFILE_CLASS_ID, L.PROJECT_ID, H.ORDER_TYPE_ID, H.ORDER_NUMBER, L.LINE_NUMBER, L.FLOW_STATUS_CODE, CP_CLASS.NAME, TO_CHAR(L.LINE_ID) 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 AND L.PAYMENT_TERM_ID IS NULL AND H.PAYMENT_TERM_ID IS NULL

View Text - HTML Formatted

SELECT H.ORG_ID
, H.TRANSACTIONAL_CURR_CODE
, DECODE( L.LINE_CATEGORY_CODE
, 'ORDER'
, 1
, 'RETURN'
, -1
, 0 ) * (( (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 ) * (( (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 ) )
, TRUNC( NVL( L.REQUEST_DATE
, NVL( H.REQUEST_DATE
, SYSDATE ) ) )
, H.BOOKED_FLAG
, H.CONVERSION_TYPE_CODE
, DECODE( H.CONVERSION_TYPE_CODE
, NULL
, NULL
, 'USER'
, H.CONVERSION_RATE_DATE
, NVL( H.REQUEST_DATE
, TRUNC(SYSDATE ) ) )
, 1
, L.COMMITMENT_ID
, CP_CLASS.PROFILE_CLASS_ID
, L.PROJECT_ID
, H.ORDER_TYPE_ID
, H.ORDER_NUMBER
, L.LINE_NUMBER
, L.FLOW_STATUS_CODE
, CP_CLASS.NAME
, TO_CHAR(L.LINE_ID)
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
AND L.PAYMENT_TERM_ID IS NULL
AND H.PAYMENT_TERM_ID IS NULL