VIEW: APPS.ISCBV_EDW_BOOK_SUM1_BASE_FCV

Source

View Text - Preformatted

SELECT isc.seq_id SEQ_ID, h.header_id ||'-'||inst.instance_code BOOKINGS_PK, decode( h.invoice_to_org_id, NULL, 'NA_EDW', h.invoice_to_org_id ||'-'||inst.instance_code ||'-CUST_SITE_USE') BILL_TO_CUST_FK, nvl(gl.currency_code,'NA_EDW') CURRENCY_BASE_FK, decode( h.booked_date, NULL, 'NA_EDW', to_char(h.booked_date,'dd-mm-yyyy') ||'-'||gl.period_set_name ||'-'||gl.accounted_period_type ||'-'||inst.instance_code ||'-CD') DATE_BOOKED_FK, nvl(inst.instance_code,'NA_EDW') INSTANCE_FK, EDW_ORGANIZATION_PKG.Operating_Unit_FK( h.org_id, inst.instance_code) OPERATING_UNIT_FK, decode( fspa.set_of_books_id, NULL, 'NA_EDW', to_char(fspa.set_of_books_id) ||'-'||substr(inst.instance_code,1,40)) SET_OF_BOOKS_FK, nvl(l.ordered_quantity,0) * decode( l.line_category_code, 'RETURN', -1, 1) * nvl(l.unit_selling_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) BOOKED_AMT_B, nvl(l.ordered_quantity,0) * decode( l.line_category_code, 'RETURN', -1, 1) * nvl(l.unit_selling_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) * EDW_CURRENCY.Get_Rate( gl.currency_code, h.booked_date) BOOKED_AMT_G, nvl(l.ordered_quantity,0) * decode( l.line_category_code, 'RETURN', -1, 1) * nvl(l.unit_list_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) BOOKED_LIST_AMT_B, nvl(l.ordered_quantity,0) * decode( l.line_category_code, 'RETURN', -1, 1) * nvl(l.unit_list_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) * EDW_CURRENCY.Get_Rate( gl.currency_code, h.booked_date) BOOKED_LIST_AMT_G, nvl(l.fulfilled_quantity,0) * decode( l.line_category_code, 'RETURN', -1, 1) * nvl(l.unit_selling_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) FULFILLED_AMT_B, nvl(l.fulfilled_quantity,0) * decode( l.line_category_code, 'RETURN', -1, 1) * nvl(l.unit_selling_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) * EDW_CURRENCY.Get_Rate( gl.currency_code, h.booked_date) FULFILLED_AMT_G, (nvl(abs(l.invoiced_quantity),0) - decode( l.line_category_code, 'RETURN', l.ordered_quantity, 0)) * nvl(l.unit_selling_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) INVOICED_AMT_B, (nvl(abs(l.invoiced_quantity),0) - decode( l.line_category_code, 'RETURN', l.ordered_quantity, 0)) * nvl(l.unit_selling_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) * EDW_CURRENCY.Get_Rate( gl.currency_code, h.booked_date) INVOICED_AMT_G, (nvl(l.shipped_quantity,0) * decode( l.line_category_code,'ORDER', 1, 0) - nvl(l.ordered_quantity,0) * decode( l.line_category_code, 'RETURN', 1, 0)) * nvl(l.unit_selling_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) * EDW_CURRENCY.Get_Rate( gl.currency_code, h.booked_date) SHIPPED_AMT_B, (nvl(l.shipped_quantity,0) * decode( l.line_category_code,'ORDER', 1, 0) - nvl(l.ordered_quantity,0) * decode( l.line_category_code, 'RETURN', 1, 0)) * nvl(l.unit_selling_price,0) * decode( l.item_type_code, 'CONFIG', 0, 1) * decode( l.item_type_code, 'INCLUDED', 0, 1) * 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)))) SHIPPED_AMT_G, trunc(h.booked_date) DATE_BOOKED, trunc(l.fulfillment_date) DATE_LATEST_FULFILLED, trunc(l.actual_shipment_date) DATE_LATEST_SHIP, trunc(h.ordered_date) DATE_ORDERED, h.header_id HEADER_ID , substr(inst.instance_code,1,40) INSTANCE, h.order_number ORDER_NUMBER, NULL USER_ATTRIBUTE1, NULL USER_ATTRIBUTE2, NULL USER_ATTRIBUTE3, NULL USER_ATTRIBUTE4, NULL USER_ATTRIBUTE5, NULL USER_ATTRIBUTE6, NULL USER_ATTRIBUTE7, NULL USER_ATTRIBUTE8, NULL USER_ATTRIBUTE9, NULL USER_ATTRIBUTE10, NULL USER_ATTRIBUTE11, NULL USER_ATTRIBUTE12, NULL USER_ATTRIBUTE13, NULL USER_ATTRIBUTE14, NULL USER_ATTRIBUTE15, NULL USER_ATTRIBUTE16, NULL USER_ATTRIBUTE17, NULL USER_ATTRIBUTE18, NULL USER_ATTRIBUTE19, NULL USER_ATTRIBUTE20, NULL USER_ATTRIBUTE21, NULL USER_ATTRIBUTE22, NULL USER_ATTRIBUTE23, NULL USER_ATTRIBUTE24, NULL USER_ATTRIBUTE25, 'NA_EDW' USER_FK1, 'NA_EDW' USER_FK2, 'NA_EDW' USER_FK3, 'NA_EDW' USER_FK4, 'NA_EDW' USER_FK5, to_number(NULL) USER_MEASURE1, to_number(NULL) USER_MEASURE2, to_number(NULL) USER_MEASURE3, to_number(NULL) USER_MEASURE4, to_number(NULL) USER_MEASURE5 FROM isc_tmp_book_sum1 isc, oe_order_lines_all l, oe_order_headers_all h, edw_local_instance inst, edw_local_system_parameters edw_param, gl_sets_of_books gl, financials_system_params_all fspa WHERE l.header_id = h.header_id AND isc.pk1 = h.header_id AND l.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 l.order_source_id <> 27 AND l.charge_periodicity_code is NULL

View Text - HTML Formatted

SELECT ISC.SEQ_ID SEQ_ID
, H.HEADER_ID ||'-'||INST.INSTANCE_CODE BOOKINGS_PK
, DECODE( H.INVOICE_TO_ORG_ID
, NULL
, 'NA_EDW'
, H.INVOICE_TO_ORG_ID ||'-'||INST.INSTANCE_CODE ||'-CUST_SITE_USE') BILL_TO_CUST_FK
, NVL(GL.CURRENCY_CODE
, 'NA_EDW') CURRENCY_BASE_FK
, DECODE( H.BOOKED_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(H.BOOKED_DATE
, 'DD-MM-YYYY') ||'-'||GL.PERIOD_SET_NAME ||'-'||GL.ACCOUNTED_PERIOD_TYPE ||'-'||INST.INSTANCE_CODE ||'-CD') DATE_BOOKED_FK
, NVL(INST.INSTANCE_CODE
, 'NA_EDW') INSTANCE_FK
, EDW_ORGANIZATION_PKG.OPERATING_UNIT_FK( H.ORG_ID
, INST.INSTANCE_CODE) OPERATING_UNIT_FK
, DECODE( FSPA.SET_OF_BOOKS_ID
, NULL
, 'NA_EDW'
, TO_CHAR(FSPA.SET_OF_BOOKS_ID) ||'-'||SUBSTR(INST.INSTANCE_CODE
, 1
, 40)) SET_OF_BOOKS_FK
, NVL(L.ORDERED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, -1
, 1) * NVL(L.UNIT_SELLING_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) BOOKED_AMT_B
, NVL(L.ORDERED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, -1
, 1) * NVL(L.UNIT_SELLING_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) * EDW_CURRENCY.GET_RATE( GL.CURRENCY_CODE
, H.BOOKED_DATE) BOOKED_AMT_G
, NVL(L.ORDERED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, -1
, 1) * NVL(L.UNIT_LIST_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) BOOKED_LIST_AMT_B
, NVL(L.ORDERED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, -1
, 1) * NVL(L.UNIT_LIST_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) * EDW_CURRENCY.GET_RATE( GL.CURRENCY_CODE
, H.BOOKED_DATE) BOOKED_LIST_AMT_G
, NVL(L.FULFILLED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, -1
, 1) * NVL(L.UNIT_SELLING_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) FULFILLED_AMT_B
, NVL(L.FULFILLED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, -1
, 1) * NVL(L.UNIT_SELLING_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) * EDW_CURRENCY.GET_RATE( GL.CURRENCY_CODE
, H.BOOKED_DATE) FULFILLED_AMT_G
, (NVL(ABS(L.INVOICED_QUANTITY)
, 0) - DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, L.ORDERED_QUANTITY
, 0)) * NVL(L.UNIT_SELLING_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) INVOICED_AMT_B
, (NVL(ABS(L.INVOICED_QUANTITY)
, 0) - DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, L.ORDERED_QUANTITY
, 0)) * NVL(L.UNIT_SELLING_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) * EDW_CURRENCY.GET_RATE( GL.CURRENCY_CODE
, H.BOOKED_DATE) INVOICED_AMT_G
, (NVL(L.SHIPPED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'ORDER'
, 1
, 0) - NVL(L.ORDERED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, 1
, 0)) * NVL(L.UNIT_SELLING_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) * EDW_CURRENCY.GET_RATE( GL.CURRENCY_CODE
, H.BOOKED_DATE) SHIPPED_AMT_B
, (NVL(L.SHIPPED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'ORDER'
, 1
, 0) - NVL(L.ORDERED_QUANTITY
, 0) * DECODE( L.LINE_CATEGORY_CODE
, 'RETURN'
, 1
, 0)) * NVL(L.UNIT_SELLING_PRICE
, 0) * DECODE( L.ITEM_TYPE_CODE
, 'CONFIG'
, 0
, 1) * DECODE( L.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, 1) * 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)))) SHIPPED_AMT_G
, TRUNC(H.BOOKED_DATE) DATE_BOOKED
, TRUNC(L.FULFILLMENT_DATE) DATE_LATEST_FULFILLED
, TRUNC(L.ACTUAL_SHIPMENT_DATE) DATE_LATEST_SHIP
, TRUNC(H.ORDERED_DATE) DATE_ORDERED
, H.HEADER_ID HEADER_ID
, SUBSTR(INST.INSTANCE_CODE
, 1
, 40) INSTANCE
, H.ORDER_NUMBER ORDER_NUMBER
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, NULL USER_ATTRIBUTE6
, NULL USER_ATTRIBUTE7
, NULL USER_ATTRIBUTE8
, NULL USER_ATTRIBUTE9
, NULL USER_ATTRIBUTE10
, NULL USER_ATTRIBUTE11
, NULL USER_ATTRIBUTE12
, NULL USER_ATTRIBUTE13
, NULL USER_ATTRIBUTE14
, NULL USER_ATTRIBUTE15
, NULL USER_ATTRIBUTE16
, NULL USER_ATTRIBUTE17
, NULL USER_ATTRIBUTE18
, NULL USER_ATTRIBUTE19
, NULL USER_ATTRIBUTE20
, NULL USER_ATTRIBUTE21
, NULL USER_ATTRIBUTE22
, NULL USER_ATTRIBUTE23
, NULL USER_ATTRIBUTE24
, NULL USER_ATTRIBUTE25
, 'NA_EDW' USER_FK1
, 'NA_EDW' USER_FK2
, 'NA_EDW' USER_FK3
, 'NA_EDW' USER_FK4
, 'NA_EDW' USER_FK5
, TO_NUMBER(NULL) USER_MEASURE1
, TO_NUMBER(NULL) USER_MEASURE2
, TO_NUMBER(NULL) USER_MEASURE3
, TO_NUMBER(NULL) USER_MEASURE4
, TO_NUMBER(NULL) USER_MEASURE5
FROM ISC_TMP_BOOK_SUM1 ISC
, OE_ORDER_LINES_ALL L
, OE_ORDER_HEADERS_ALL H
, EDW_LOCAL_INSTANCE INST
, EDW_LOCAL_SYSTEM_PARAMETERS EDW_PARAM
, GL_SETS_OF_BOOKS GL
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
WHERE L.HEADER_ID = H.HEADER_ID
AND ISC.PK1 = H.HEADER_ID
AND L.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 L.ORDER_SOURCE_ID <> 27
AND L.CHARGE_PERIODICITY_CODE IS NULL