VIEW: APPS.ISCBV_EDW_BACKLOG_SUM1_B_FCV

Source

View Text - Preformatted

SELECT h.header_id||'-'||inst.instance_code BACKLOG_SUM1_PK, nvl( to_char(h.sold_to_org_id), 'NA_EDW') CUSTOMER_ID, nvl( gl.currency_code, 'NA_EDW') FUNCTIONAL_CURRENCY, nvl( inst.instance_code,'NA_EDW') INSTANCE_CODE, nvl( to_char(h.org_id), 'NA_EDW') OPERATING_UNIT_ID, nvl( to_char(gl.set_of_books_id), 'NA_EDW') SET_OF_BOOKS_ID, h.booked_date DATE_BOOKED, sysdate DATE_OF_SNAPSHOT, (sysdate - h.booked_date) * decode( (l.ordered_quantity - nvl(l.fulfilled_quantity,0)),0,0,1) * decode( l.item_type_code, 'SERVICE',0,1) * decode( l.item_type_code, 'CONFIG',0,1) * decode( l.item_type_code, 'INCLUDED',0,1) DAYS_OPEN, nvl(l.unit_selling_price,0) * (l.ordered_quantity - nvl(l.shipped_quantity,0)) * nvl(decode( upper(h.conversion_type_code), 'USER', h.conversion_rate, decode( h.transactional_curr_code, gl.currency_code, 1, GL_CURRENCY_API.get_rate_sql ( h.transactional_curr_code, gl.currency_code, h.booked_date, nvl(h.conversion_type_code, edw_param.rate_type)))),0) * decode( sign(nvl(l.schedule_ship_date,sysdate) - sysdate),-1,1,0) * decode( l.top_model_line_id, NULL, decode( nvl(l.shippable_flag,'N'), 'N',0,1), 1) * decode( l.item_type_code, 'SERVICE',0,1) * decode( l.item_type_code, 'CONFIG',0,1) * decode( l.item_type_code, 'INCLUDED',0,1) DLQT_BKLG_AMT_B, nvl(l.unit_selling_price,0) * (l.ordered_quantity - nvl(l.shipped_quantity,0)) * nvl(decode( upper(h.conversion_type_code), 'USER', h.conversion_rate, decode( h.transactional_curr_code, gl.currency_code, 1, GL_CURRENCY_API.get_rate_sql ( h.transactional_curr_code, gl.currency_code, h.booked_date, nvl(h.conversion_type_code, edw_param.rate_type)))),0) * EDW_CURRENCY.Get_Rate( gl.currency_code, h.booked_date) * decode( sign(nvl(l.schedule_ship_date,sysdate) - sysdate),-1,1,0) * decode( l.top_model_line_id, NULL, decode( nvl(l.shippable_flag,'N'), 'N', 0, 1), 1) * decode( l.item_type_code, 'SERVICE',0,1) * decode( l.item_type_code, 'CONFIG',0,1) * decode( l.item_type_code, 'INCLUDED',0,1) DLQT_BKLG_AMT_G, decode( (l.ordered_quantity - nvl(l.shipped_quantity,0)), 0, 0, 1) * decode( sign(nvl(l.schedule_ship_date,sysdate) - sysdate), -1, 1, 0) * decode( l.top_model_line_id, NULL, decode( nvl(l.shippable_flag,'N'), 'N',0,1), 1) * decode( l.item_type_code, 'SERVICE',0,1) * decode( l.item_type_code, 'CONFIG',0,1) * decode( l.item_type_code, 'INCLUDED',0,1) DLQT_BKLG_LINE_COUNT, to_char(h.header_id) HEADER_ID, (sysdate - nvl(l.schedule_ship_date,sysdate)) * decode( (l.ordered_quantity - nvl(l.shipped_quantity,0)),0,0,1) * decode( sign(nvl(l.schedule_ship_date,sysdate) - sysdate),-1,1,0) * decode( l.top_model_line_id, NULL, decode( nvl(l.shippable_flag,'N'),'N',0,1), 1) * decode( l.item_type_code, 'SERVICE',0,1) * decode( l.item_type_code, 'CONFIG',0,1) * decode( l.item_type_code, 'INCLUDED',0,1) MAX_DAYS_LATE, to_char(h.order_number) ORDER_NUMBER, nvl(l.unit_selling_price * (l.ordered_quantity - nvl(l.fulfilled_quantity,0)),0) * nvl(decode( upper(h.conversion_type_code), 'USER', h.conversion_rate, decode( h.transactional_curr_code, gl.currency_code, 1, GL_CURRENCY_API.get_rate_sql ( h.transactional_curr_code, gl.currency_code, h.booked_date, nvl(h.conversion_type_code, edw_param.rate_type)))),0) * decode( l.item_type_code, 'SERVICE',0,1) * decode(l.item_type_code, 'CONFIG',0,1) * decode( l.item_type_code, 'INCLUDED',0,1) SHIP_BKLG_AMT_B, nvl(l.unit_selling_price * (l.ordered_quantity - nvl(l.fulfilled_quantity,0)),0) * nvl(decode( upper(h.conversion_type_code), 'USER', h.conversion_rate, decode( h.transactional_curr_code, gl.currency_code, 1, GL_CURRENCY_API.get_rate_sql ( h.transactional_curr_code, gl.currency_code, h.booked_date, nvl(h.conversion_type_code, edw_param.rate_type)))),0) * EDW_CURRENCY.Get_Rate( gl.currency_code, h.booked_date) * decode( l.item_type_code, 'SERVICE',0,1) * decode( l.item_type_code, 'CONFIG',0,1) * decode( l.item_type_code, 'INCLUDED',0,1) SHIP_BKLG_AMT_G, decode( (l.ordered_quantity - nvl(l.fulfilled_quantity,0)),0,0,1) * decode( l.item_type_code, 'SERVICE',0,1) * decode( l.item_type_code, 'CONFIG',0,1) * decode( l.item_type_code, 'INCLUDED',0,1) SHIP_BKLG_LINE_COUNT FROM edw_local_instance inst, edw_local_system_parameters edw_param, oe_order_headers_all h, oe_order_lines_all l, financials_system_params_all fspa, gl_sets_of_books gl WHERE l.header_id = h.header_id AND h.org_id = fspa.org_id AND fspa.set_of_books_id = gl.set_of_books_id AND h.booked_flag = 'Y' AND h.booked_date IS NOT NULL AND h.open_flag = 'Y' AND nvl(l.ordered_quantity,0) > 0 AND nvl(l.source_document_type_id,0) <> 10 AND l.line_category_code = ('ORDER') AND l.order_source_id <> 27 AND l.charge_periodicity_code is NULL

View Text - HTML Formatted

SELECT H.HEADER_ID||'-'||INST.INSTANCE_CODE BACKLOG_SUM1_PK
, NVL( TO_CHAR(H.SOLD_TO_ORG_ID)
, 'NA_EDW') CUSTOMER_ID
, NVL( GL.CURRENCY_CODE
, 'NA_EDW') FUNCTIONAL_CURRENCY
, NVL( INST.INSTANCE_CODE
, 'NA_EDW') INSTANCE_CODE
, NVL( TO_CHAR(H.ORG_ID)
, 'NA_EDW') OPERATING_UNIT_ID
, NVL( TO_CHAR(GL.SET_OF_BOOKS_ID)
, 'NA_EDW') SET_OF_BOOKS_ID
, H.BOOKED_DATE DATE_BOOKED
, SYSDATE DATE_OF_SNAPSHOT
, (SYSDATE - H.BOOKED_DATE) * DECODE( (L.ORDERED_QUANTITY - NVL(L.FULFILLED_QUANTITY
, 0))
, 0
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) DAYS_OPEN
, NVL(L.UNIT_SELLING_PRICE
, 0) * (L.ORDERED_QUANTITY - NVL(L.SHIPPED_QUANTITY
, 0)) * NVL(DECODE( UPPER(H.CONVERSION_TYPE_CODE)
, 'USER'
, H.CONVERSION_RATE
, DECODE( H.TRANSACTIONAL_CURR_CODE
, GL.CURRENCY_CODE
, 1
, GL_CURRENCY_API.GET_RATE_SQL ( H.TRANSACTIONAL_CURR_CODE
, GL.CURRENCY_CODE
, H.BOOKED_DATE
, NVL(H.CONVERSION_TYPE_CODE
, EDW_PARAM.RATE_TYPE))))
, 0) * DECODE( SIGN(NVL(L.SCHEDULE_SHIP_DATE
, SYSDATE) - SYSDATE)
, -1
, 1
, 0) * DECODE( L.TOP_MODEL_LINE_ID
, NULL
, DECODE( NVL(L.SHIPPABLE_FLAG
, 'N')
, 'N'
, 0
, 1)
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) DLQT_BKLG_AMT_B
, NVL(L.UNIT_SELLING_PRICE
, 0) * (L.ORDERED_QUANTITY - NVL(L.SHIPPED_QUANTITY
, 0)) * NVL(DECODE( UPPER(H.CONVERSION_TYPE_CODE)
, 'USER'
, H.CONVERSION_RATE
, DECODE( H.TRANSACTIONAL_CURR_CODE
, GL.CURRENCY_CODE
, 1
, GL_CURRENCY_API.GET_RATE_SQL ( H.TRANSACTIONAL_CURR_CODE
, GL.CURRENCY_CODE
, H.BOOKED_DATE
, NVL(H.CONVERSION_TYPE_CODE
, EDW_PARAM.RATE_TYPE))))
, 0) * EDW_CURRENCY.GET_RATE( GL.CURRENCY_CODE
, H.BOOKED_DATE) * DECODE( SIGN(NVL(L.SCHEDULE_SHIP_DATE
, SYSDATE) - SYSDATE)
, -1
, 1
, 0) * DECODE( L.TOP_MODEL_LINE_ID
, NULL
, DECODE( NVL(L.SHIPPABLE_FLAG
, 'N')
, 'N'
, 0
, 1)
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) DLQT_BKLG_AMT_G
, DECODE( (L.ORDERED_QUANTITY - NVL(L.SHIPPED_QUANTITY
, 0))
, 0
, 0
, 1) * DECODE( SIGN(NVL(L.SCHEDULE_SHIP_DATE
, SYSDATE) - SYSDATE)
, -1
, 1
, 0) * DECODE( L.TOP_MODEL_LINE_ID
, NULL
, DECODE( NVL(L.SHIPPABLE_FLAG
, 'N')
, 'N'
, 0
, 1)
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) DLQT_BKLG_LINE_COUNT
, TO_CHAR(H.HEADER_ID) HEADER_ID
, (SYSDATE - NVL(L.SCHEDULE_SHIP_DATE
, SYSDATE)) * DECODE( (L.ORDERED_QUANTITY - NVL(L.SHIPPED_QUANTITY
, 0))
, 0
, 0
, 1) * DECODE( SIGN(NVL(L.SCHEDULE_SHIP_DATE
, SYSDATE) - SYSDATE)
, -1
, 1
, 0) * DECODE( L.TOP_MODEL_LINE_ID
, NULL
, DECODE( NVL(L.SHIPPABLE_FLAG
, 'N')
, 'N'
, 0
, 1)
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) MAX_DAYS_LATE
, TO_CHAR(H.ORDER_NUMBER) ORDER_NUMBER
, NVL(L.UNIT_SELLING_PRICE * (L.ORDERED_QUANTITY - NVL(L.FULFILLED_QUANTITY
, 0))
, 0) * NVL(DECODE( UPPER(H.CONVERSION_TYPE_CODE)
, 'USER'
, H.CONVERSION_RATE
, DECODE( H.TRANSACTIONAL_CURR_CODE
, GL.CURRENCY_CODE
, 1
, GL_CURRENCY_API.GET_RATE_SQL ( H.TRANSACTIONAL_CURR_CODE
, GL.CURRENCY_CODE
, H.BOOKED_DATE
, NVL(H.CONVERSION_TYPE_CODE
, EDW_PARAM.RATE_TYPE))))
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, 1) * DECODE(L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) SHIP_BKLG_AMT_B
, NVL(L.UNIT_SELLING_PRICE * (L.ORDERED_QUANTITY - NVL(L.FULFILLED_QUANTITY
, 0))
, 0) * NVL(DECODE( UPPER(H.CONVERSION_TYPE_CODE)
, 'USER'
, H.CONVERSION_RATE
, DECODE( H.TRANSACTIONAL_CURR_CODE
, GL.CURRENCY_CODE
, 1
, GL_CURRENCY_API.GET_RATE_SQL ( H.TRANSACTIONAL_CURR_CODE
, GL.CURRENCY_CODE
, H.BOOKED_DATE
, NVL(H.CONVERSION_TYPE_CODE
, EDW_PARAM.RATE_TYPE))))
, 0) * EDW_CURRENCY.GET_RATE( GL.CURRENCY_CODE
, H.BOOKED_DATE) * DECODE( L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) SHIP_BKLG_AMT_G
, DECODE( (L.ORDERED_QUANTITY - NVL(L.FULFILLED_QUANTITY
, 0))
, 0
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) SHIP_BKLG_LINE_COUNT
FROM EDW_LOCAL_INSTANCE INST
, EDW_LOCAL_SYSTEM_PARAMETERS EDW_PARAM
, OE_ORDER_HEADERS_ALL H
, OE_ORDER_LINES_ALL L
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, GL_SETS_OF_BOOKS GL
WHERE L.HEADER_ID = H.HEADER_ID
AND H.ORG_ID = FSPA.ORG_ID
AND FSPA.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
AND H.BOOKED_FLAG = 'Y'
AND H.BOOKED_DATE IS NOT NULL
AND H.OPEN_FLAG = 'Y'
AND NVL(L.ORDERED_QUANTITY
, 0) > 0
AND NVL(L.SOURCE_DOCUMENT_TYPE_ID
, 0) <> 10
AND L.LINE_CATEGORY_CODE = ('ORDER')
AND L.ORDER_SOURCE_ID <> 27
AND L.CHARGE_PERIODICITY_CODE IS NULL