VIEW: APPS.CE_SO_FC_ORDERS_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, TERM_L.DUE_DATE TERM_DUE_DATE, TERM_L.DUE_DAYS TERM_DUE_DAYS, TERM_L.DUE_DAY_OF_MONTH TERM_DUE_DAY_OF_MONTH, TERM_L.DUE_MONTHS_FORWARD TERM_DUE_MONTHS_FORWARD, TERM_L.RELATIVE_AMOUNT, NVL(L.PAYMENT_TERM_ID, H.PAYMENT_TERM_ID), H.ORDER_NUMBER, L.LINE_NUMBER, L.FLOW_STATUS_CODE, CP_CLASS.NAME, (L.LINE_ID || 'X' || TERM_L.TERM_ID || 'X' || TERM_L.SEQUENCE_NUM) 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, RA_TERMS_LINES TERM_L 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 TERM_L.TERM_ID = NVL(L.PAYMENT_TERM_ID, H.PAYMENT_TERM_ID)

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
, TERM_L.DUE_DATE TERM_DUE_DATE
, TERM_L.DUE_DAYS TERM_DUE_DAYS
, TERM_L.DUE_DAY_OF_MONTH TERM_DUE_DAY_OF_MONTH
, TERM_L.DUE_MONTHS_FORWARD TERM_DUE_MONTHS_FORWARD
, TERM_L.RELATIVE_AMOUNT
, NVL(L.PAYMENT_TERM_ID
, H.PAYMENT_TERM_ID)
, H.ORDER_NUMBER
, L.LINE_NUMBER
, L.FLOW_STATUS_CODE
, CP_CLASS.NAME
, (L.LINE_ID || 'X' || TERM_L.TERM_ID || 'X' || TERM_L.SEQUENCE_NUM)
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
, RA_TERMS_LINES TERM_L
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 TERM_L.TERM_ID = NVL(L.PAYMENT_TERM_ID
, H.PAYMENT_TERM_ID)