VIEW: APPS.ISCBV_BOOK_SUM2_FCV

Source

View Text - Preformatted

SELECT /*+ ordered */ l.line_id LINE_ID, isc.to_currency1 CURRENCY_FUNC_ID, isc.to_currency3 CURR_WH_FUNC_ID, nvl(cust_acct.party_id,-1) CUSTOMER_ID, nvl(ship_ps.party_id,-1) SHIP_TO_PARTY_ID, l.inventory_item_id INVENTORY_ITEM_ID, decode(isc.view_type, 0, nvl(l.ship_from_org_id, isc.master_org_id), nvl(pl.ship_from_org_id, isc.master_org_id)) INV_ORG_ID, isc.inv_ou_id INV_OU_ID, decode(isc.view_type, 0, l.inventory_item_id, pl.inventory_item_id) ITEM_ID, nvl(l.ship_from_org_id, isc.master_org_id) ITEM_INV_ORG_ID, l.line_category_code ORDER_CATEGORY_ID, nvl(l.source_document_type_id, nvl(l.order_source_id, -1)) ORDER_SOURCE_ID, h.order_type_id ORDER_TYPE_ID, nvl(h.org_id,-1) ORG_OU_ID, nvl(l.return_reason_code, -1) RETURN_REASON_ID, nvl(h.sales_channel_code, -1) SALES_CHANNEL_ID, l.ship_to_org_id SHIP_TO_ORG_ID, h.sold_to_org_id SOLD_TO_ORG_ID, trunc(l.actual_shipment_date) TIME_ACT_SHIP_DATE_ID, isc.time_booked_date_id TIME_BOOKED_DATE_ID, isc.time_fulfilled_date_id TIME_FULFILLED_DATE_ID, trunc(h.ordered_date) TIME_ORDERED_DATE_ID, trunc(l.promise_date) TIME_PROMISE_DATE_ID, trunc(l.request_date) TIME_REQUEST_DATE_ID, trunc(l.schedule_ship_date) TIME_SCHEDULE_DATE_ID, decode(isc.view_type, 3, nvl(trunc(l.actual_shipment_date), isc.time_fulfilled_date_id), trunc(l.actual_shipment_date)) TIME_SHIPPED_DATE_ID, pl.inventory_item_id TOP_MODEL_ITEM_ID, nvl(pl.ship_from_org_id, isc.master_org_id) TOP_MODEL_ORG_ID, l.actual_shipment_date ACTUAL_SHIPMENT_DATE, l.ato_line_id ATO_LINE_ID, isc.batch_id BATCH_ID, nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0) * nvl(isc.rate1, curr.rate1) BOOKED_AMT_F, nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0) * curr.rate3 BOOKED_AMT_F1, nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0) * decode(isc.from_currency, isc.to_currency2, 1, curr.rate3 * curr.rate2) BOOKED_AMT_G, nvl(l.unit_selling_price,0) * nvl(l.ordered_quantity,0) * decode(isc.from_currency, isc.to_currency4, 1, curr.rate3 * curr.rate4) BOOKED_AMT_G1, nvl(l.ordered_quantity,0) * isc.inv_uom_rate BOOKED_QTY_INV, nvl(l.order_firmed_date, h.booked_date) BOOKED_DATE, nvl(l.unit_list_price,0) * nvl(l.ordered_quantity,0) * nvl(isc.rate1, curr.rate1) BOOKED_LIST_AMT_F, nvl(l.unit_list_price,0) * nvl(l.ordered_quantity,0) * curr.rate3 BOOKED_LIST_AMT_F1, nvl(l.unit_list_price,0) * nvl(l.ordered_quantity,0) * decode(isc.from_currency, isc.to_currency2, 1, curr.rate3 * curr.rate2) BOOKED_LIST_AMT_G, nvl(l.unit_list_price,0) * nvl(l.ordered_quantity,0) * decode(isc.from_currency, isc.to_currency4, 1, curr.rate3 * curr.rate4) BOOKED_LIST_AMT_G1, decode(l.shippable_flag, 'Y', decode(l.shipped_quantity, null, 1, 0),0) COUNT_PDUE_LINE, decode(l.shippable_flag, 'Y', CASE WHEN l.shipped_quantity > 0 THEN 1 ELSE 0 END, 0) COUNT_SHIP_LINE, l.flow_status_code FLOW_STATUS_CODE, nvl(l.unit_selling_price,0) * nvl(l.fulfilled_quantity,0) * nvl(isc.rate1, curr.rate1) FULFILLED_AMT_F, nvl(l.unit_selling_price,0) * nvl(l.fulfilled_quantity,0) * curr.rate3 FULFILLED_AMT_F1, nvl(l.unit_selling_price,0) * nvl(l.fulfilled_quantity,0) * decode(isc.from_currency, isc.to_currency2, 1, curr.rate3 * curr.rate2) FULFILLED_AMT_G, nvl(l.unit_selling_price,0) * nvl(l.fulfilled_quantity,0) * decode(isc.from_currency, isc.to_currency4, 1, curr.rate3 * curr.rate4) FULFILLED_AMT_G1, nvl(l.fulfilled_quantity,0) * isc.inv_uom_rate FULFILLED_QTY_INV, nvl(l.actual_fulfillment_date, l.fulfillment_date) FULFILLED_DATE, nvl(l.fulfilled_flag, 'N') FULFILLED_FLAG, l.header_id HEADER_ID, h.marketing_source_code_id H_MARKETING_SOURCE_CODE_ID, nvl(l.unit_selling_price,0)* nvl(abs(l.invoiced_quantity),0) * nvl(isc.rate1, curr.rate1) INVOICED_AMT_F, nvl(l.unit_selling_price,0)* nvl(abs(l.invoiced_quantity),0) * curr.rate3 INVOICED_AMT_F1, nvl(l.unit_selling_price,0) * nvl(abs(l.invoiced_quantity),0) * decode(isc.from_currency, isc.to_currency2, 1, curr.rate3 * curr.rate2) INVOICED_AMT_G, nvl(l.unit_selling_price,0) * nvl(abs(l.invoiced_quantity),0) * decode(isc.from_currency, isc.to_currency4, 1, curr.rate3 * curr.rate4) INVOICED_AMT_G1, nvl(abs(l.invoiced_quantity),0) * isc.inv_uom_rate INVOICED_QTY_INV, l.invoice_to_org_id INVOICE_TO_ORG_ID, isc.inv_uom_code INV_UOM_CODE, l.item_type_code ITEM_TYPE_CODE, l.line_category_code LINE_CATEGORY_CODE, isc.view_type LINE_ITEM_TYPE, isc.line_number LINE_NUMBER, l.marketing_source_code_id MARKETING_SOURCE_CODE_ID, l.open_flag OPEN_FLAG, h.ordered_date ORDERED_DATE, l.ordered_quantity ORDERED_QUANTITY, h.order_date_type_code ORDER_DATE_TYPE_CODE, h.order_number ORDER_NUMBER, l.order_quantity_uom ORDER_QUANTITY_UOM, l.promise_date PROMISE_DATE, l.request_date REQUEST_DATE, l.schedule_ship_date SCHEDULE_SHIP_DATE, decode(l.service_reference_type_code, 'CUSTOMER_PRODUCT', serv.service_parent_line_id, 'ORDER', l.service_reference_line_id, NULL) SERVICE_PARENT_LINE_ID, l.shippable_flag SHIPPABLE_FLAG, nvl(l.unit_selling_price,0) * nvl(l.shipped_quantity,0) * nvl(isc.rate1, curr.rate1) SHIPPED_AMT_F, nvl(l.unit_selling_price,0) * nvl(l.shipped_quantity,0) * curr.rate3 SHIPPED_AMT_F1, nvl(l.unit_selling_price,0) * nvl(l.shipped_quantity,0) * decode(isc.from_currency, isc.to_currency2, 1, curr.rate3 * curr.rate2) SHIPPED_AMT_G, nvl(l.unit_selling_price,0) * nvl(l.shipped_quantity,0) * decode(isc.from_currency, isc.to_currency4, 1, curr.rate3 * curr.rate4) SHIPPED_AMT_G1, nvl(l.shipped_quantity,0) * isc.inv_uom_rate SHIPPED_QTY_INV, decode(isc.view_type, 3, nvl(l.actual_shipment_date, nvl(l.actual_fulfillment_date, l.fulfillment_date)), l.actual_shipment_date) SHIPPED_DATE, l.top_model_line_id TOP_MODEL_LINE_ID, l.unit_list_price UNIT_LIST_PRICE, l.unit_selling_price UNIT_SELLING_PRICE, isc.freight_charge FREIGHT_CHARGE, isc.freight_charge * nvl(isc.rate1, curr.rate1) FREIGHT_CHARGE_F, isc.freight_charge * decode(isc.from_currency, isc.to_currency2, 1, curr.rate3 * curr.rate2) FREIGHT_CHARGE_G, isc.freight_charge * decode(isc.from_currency, isc.to_currency4, 1, curr.rate3 * curr.rate4) FREIGHT_CHARGE_G1, isc.freight_cost FREIGHT_COST, isc.freight_cost * nvl(isc.rate1, curr.rate1) FREIGHT_COST_F, isc.freight_cost * decode(isc.from_currency, isc.to_currency2, 1, curr.rate3 * curr.rate2) FREIGHT_COST_G, isc.freight_cost * decode(isc.from_currency, isc.to_currency4, 1, curr.rate3 * curr.rate4) FREIGHT_COST_G1, l.charge_periodicity_code CHARGE_PERIODICITY_CODE, l.blanket_number BLANKET_NUMBER, l.blanket_line_number BLANKET_LINE_NUMBER, nvl(l.created_by,0) CREATED_BY, l.creation_date CREATION_DATE, nvl(l.last_updated_by,0) LAST_UPDATED_BY, l.last_update_date LAST_UPDATE_DATE, nvl(l.last_update_login,0) LAST_UPDATE_LOGIN FROM ISC_SERVICE_BOOK_SUM2 serv, ISC_CURR_BOOK_SUM2 curr, ISC_TMP_BOOK_SUM2 isc, OE_ORDER_LINES_ALL l, OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL pl, HZ_CUST_ACCOUNTS cust_acct, HZ_CUST_SITE_USES_ALL ship_su, HZ_CUST_ACCT_SITES_ALL ship_cas, HZ_PARTY_SITES ship_ps WHERE isc.from_currency = curr.from_currency AND isc.to_currency1 = curr.to_currency1 AND isc.to_currency3 = curr.to_currency3 AND isc.time_booked_date_id = curr.conversion_date AND isc.rate_type = curr.conversion_type AND isc.pk1 = serv.line_id(+) AND isc.pk1 = l.line_id AND l.header_id= h.header_id AND h.sold_to_org_id = cust_acct.cust_account_id(+) AND l.ship_to_org_id = ship_su.site_use_id(+) AND ship_su.cust_acct_site_id= ship_cas.cust_acct_site_id(+) AND ship_cas.party_site_id = ship_ps.party_site_id(+) AND nvl(l.top_model_line_id, l.line_id) = pl.line_id

View Text - HTML Formatted

SELECT /*+ ORDERED */ L.LINE_ID LINE_ID
, ISC.TO_CURRENCY1 CURRENCY_FUNC_ID
, ISC.TO_CURRENCY3 CURR_WH_FUNC_ID
, NVL(CUST_ACCT.PARTY_ID
, -1) CUSTOMER_ID
, NVL(SHIP_PS.PARTY_ID
, -1) SHIP_TO_PARTY_ID
, L.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, DECODE(ISC.VIEW_TYPE
, 0
, NVL(L.SHIP_FROM_ORG_ID
, ISC.MASTER_ORG_ID)
, NVL(PL.SHIP_FROM_ORG_ID
, ISC.MASTER_ORG_ID)) INV_ORG_ID
, ISC.INV_OU_ID INV_OU_ID
, DECODE(ISC.VIEW_TYPE
, 0
, L.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID) ITEM_ID
, NVL(L.SHIP_FROM_ORG_ID
, ISC.MASTER_ORG_ID) ITEM_INV_ORG_ID
, L.LINE_CATEGORY_CODE ORDER_CATEGORY_ID
, NVL(L.SOURCE_DOCUMENT_TYPE_ID
, NVL(L.ORDER_SOURCE_ID
, -1)) ORDER_SOURCE_ID
, H.ORDER_TYPE_ID ORDER_TYPE_ID
, NVL(H.ORG_ID
, -1) ORG_OU_ID
, NVL(L.RETURN_REASON_CODE
, -1) RETURN_REASON_ID
, NVL(H.SALES_CHANNEL_CODE
, -1) SALES_CHANNEL_ID
, L.SHIP_TO_ORG_ID SHIP_TO_ORG_ID
, H.SOLD_TO_ORG_ID SOLD_TO_ORG_ID
, TRUNC(L.ACTUAL_SHIPMENT_DATE) TIME_ACT_SHIP_DATE_ID
, ISC.TIME_BOOKED_DATE_ID TIME_BOOKED_DATE_ID
, ISC.TIME_FULFILLED_DATE_ID TIME_FULFILLED_DATE_ID
, TRUNC(H.ORDERED_DATE) TIME_ORDERED_DATE_ID
, TRUNC(L.PROMISE_DATE) TIME_PROMISE_DATE_ID
, TRUNC(L.REQUEST_DATE) TIME_REQUEST_DATE_ID
, TRUNC(L.SCHEDULE_SHIP_DATE) TIME_SCHEDULE_DATE_ID
, DECODE(ISC.VIEW_TYPE
, 3
, NVL(TRUNC(L.ACTUAL_SHIPMENT_DATE)
, ISC.TIME_FULFILLED_DATE_ID)
, TRUNC(L.ACTUAL_SHIPMENT_DATE)) TIME_SHIPPED_DATE_ID
, PL.INVENTORY_ITEM_ID TOP_MODEL_ITEM_ID
, NVL(PL.SHIP_FROM_ORG_ID
, ISC.MASTER_ORG_ID) TOP_MODEL_ORG_ID
, L.ACTUAL_SHIPMENT_DATE ACTUAL_SHIPMENT_DATE
, L.ATO_LINE_ID ATO_LINE_ID
, ISC.BATCH_ID BATCH_ID
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.ORDERED_QUANTITY
, 0) * NVL(ISC.RATE1
, CURR.RATE1) BOOKED_AMT_F
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.ORDERED_QUANTITY
, 0) * CURR.RATE3 BOOKED_AMT_F1
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.ORDERED_QUANTITY
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY2
, 1
, CURR.RATE3 * CURR.RATE2) BOOKED_AMT_G
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.ORDERED_QUANTITY
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY4
, 1
, CURR.RATE3 * CURR.RATE4) BOOKED_AMT_G1
, NVL(L.ORDERED_QUANTITY
, 0) * ISC.INV_UOM_RATE BOOKED_QTY_INV
, NVL(L.ORDER_FIRMED_DATE
, H.BOOKED_DATE) BOOKED_DATE
, NVL(L.UNIT_LIST_PRICE
, 0) * NVL(L.ORDERED_QUANTITY
, 0) * NVL(ISC.RATE1
, CURR.RATE1) BOOKED_LIST_AMT_F
, NVL(L.UNIT_LIST_PRICE
, 0) * NVL(L.ORDERED_QUANTITY
, 0) * CURR.RATE3 BOOKED_LIST_AMT_F1
, NVL(L.UNIT_LIST_PRICE
, 0) * NVL(L.ORDERED_QUANTITY
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY2
, 1
, CURR.RATE3 * CURR.RATE2) BOOKED_LIST_AMT_G
, NVL(L.UNIT_LIST_PRICE
, 0) * NVL(L.ORDERED_QUANTITY
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY4
, 1
, CURR.RATE3 * CURR.RATE4) BOOKED_LIST_AMT_G1
, DECODE(L.SHIPPABLE_FLAG
, 'Y'
, DECODE(L.SHIPPED_QUANTITY
, NULL
, 1
, 0)
, 0) COUNT_PDUE_LINE
, DECODE(L.SHIPPABLE_FLAG
, 'Y'
, CASE WHEN L.SHIPPED_QUANTITY > 0 THEN 1 ELSE 0 END
, 0) COUNT_SHIP_LINE
, L.FLOW_STATUS_CODE FLOW_STATUS_CODE
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.FULFILLED_QUANTITY
, 0) * NVL(ISC.RATE1
, CURR.RATE1) FULFILLED_AMT_F
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.FULFILLED_QUANTITY
, 0) * CURR.RATE3 FULFILLED_AMT_F1
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.FULFILLED_QUANTITY
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY2
, 1
, CURR.RATE3 * CURR.RATE2) FULFILLED_AMT_G
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.FULFILLED_QUANTITY
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY4
, 1
, CURR.RATE3 * CURR.RATE4) FULFILLED_AMT_G1
, NVL(L.FULFILLED_QUANTITY
, 0) * ISC.INV_UOM_RATE FULFILLED_QTY_INV
, NVL(L.ACTUAL_FULFILLMENT_DATE
, L.FULFILLMENT_DATE) FULFILLED_DATE
, NVL(L.FULFILLED_FLAG
, 'N') FULFILLED_FLAG
, L.HEADER_ID HEADER_ID
, H.MARKETING_SOURCE_CODE_ID H_MARKETING_SOURCE_CODE_ID
, NVL(L.UNIT_SELLING_PRICE
, 0)* NVL(ABS(L.INVOICED_QUANTITY)
, 0) * NVL(ISC.RATE1
, CURR.RATE1) INVOICED_AMT_F
, NVL(L.UNIT_SELLING_PRICE
, 0)* NVL(ABS(L.INVOICED_QUANTITY)
, 0) * CURR.RATE3 INVOICED_AMT_F1
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(ABS(L.INVOICED_QUANTITY)
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY2
, 1
, CURR.RATE3 * CURR.RATE2) INVOICED_AMT_G
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(ABS(L.INVOICED_QUANTITY)
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY4
, 1
, CURR.RATE3 * CURR.RATE4) INVOICED_AMT_G1
, NVL(ABS(L.INVOICED_QUANTITY)
, 0) * ISC.INV_UOM_RATE INVOICED_QTY_INV
, L.INVOICE_TO_ORG_ID INVOICE_TO_ORG_ID
, ISC.INV_UOM_CODE INV_UOM_CODE
, L.ITEM_TYPE_CODE ITEM_TYPE_CODE
, L.LINE_CATEGORY_CODE LINE_CATEGORY_CODE
, ISC.VIEW_TYPE LINE_ITEM_TYPE
, ISC.LINE_NUMBER LINE_NUMBER
, L.MARKETING_SOURCE_CODE_ID MARKETING_SOURCE_CODE_ID
, L.OPEN_FLAG OPEN_FLAG
, H.ORDERED_DATE ORDERED_DATE
, L.ORDERED_QUANTITY ORDERED_QUANTITY
, H.ORDER_DATE_TYPE_CODE ORDER_DATE_TYPE_CODE
, H.ORDER_NUMBER ORDER_NUMBER
, L.ORDER_QUANTITY_UOM ORDER_QUANTITY_UOM
, L.PROMISE_DATE PROMISE_DATE
, L.REQUEST_DATE REQUEST_DATE
, L.SCHEDULE_SHIP_DATE SCHEDULE_SHIP_DATE
, DECODE(L.SERVICE_REFERENCE_TYPE_CODE
, 'CUSTOMER_PRODUCT'
, SERV.SERVICE_PARENT_LINE_ID
, 'ORDER'
, L.SERVICE_REFERENCE_LINE_ID
, NULL) SERVICE_PARENT_LINE_ID
, L.SHIPPABLE_FLAG SHIPPABLE_FLAG
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.SHIPPED_QUANTITY
, 0) * NVL(ISC.RATE1
, CURR.RATE1) SHIPPED_AMT_F
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.SHIPPED_QUANTITY
, 0) * CURR.RATE3 SHIPPED_AMT_F1
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.SHIPPED_QUANTITY
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY2
, 1
, CURR.RATE3 * CURR.RATE2) SHIPPED_AMT_G
, NVL(L.UNIT_SELLING_PRICE
, 0) * NVL(L.SHIPPED_QUANTITY
, 0) * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY4
, 1
, CURR.RATE3 * CURR.RATE4) SHIPPED_AMT_G1
, NVL(L.SHIPPED_QUANTITY
, 0) * ISC.INV_UOM_RATE SHIPPED_QTY_INV
, DECODE(ISC.VIEW_TYPE
, 3
, NVL(L.ACTUAL_SHIPMENT_DATE
, NVL(L.ACTUAL_FULFILLMENT_DATE
, L.FULFILLMENT_DATE))
, L.ACTUAL_SHIPMENT_DATE) SHIPPED_DATE
, L.TOP_MODEL_LINE_ID TOP_MODEL_LINE_ID
, L.UNIT_LIST_PRICE UNIT_LIST_PRICE
, L.UNIT_SELLING_PRICE UNIT_SELLING_PRICE
, ISC.FREIGHT_CHARGE FREIGHT_CHARGE
, ISC.FREIGHT_CHARGE * NVL(ISC.RATE1
, CURR.RATE1) FREIGHT_CHARGE_F
, ISC.FREIGHT_CHARGE * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY2
, 1
, CURR.RATE3 * CURR.RATE2) FREIGHT_CHARGE_G
, ISC.FREIGHT_CHARGE * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY4
, 1
, CURR.RATE3 * CURR.RATE4) FREIGHT_CHARGE_G1
, ISC.FREIGHT_COST FREIGHT_COST
, ISC.FREIGHT_COST * NVL(ISC.RATE1
, CURR.RATE1) FREIGHT_COST_F
, ISC.FREIGHT_COST * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY2
, 1
, CURR.RATE3 * CURR.RATE2) FREIGHT_COST_G
, ISC.FREIGHT_COST * DECODE(ISC.FROM_CURRENCY
, ISC.TO_CURRENCY4
, 1
, CURR.RATE3 * CURR.RATE4) FREIGHT_COST_G1
, L.CHARGE_PERIODICITY_CODE CHARGE_PERIODICITY_CODE
, L.BLANKET_NUMBER BLANKET_NUMBER
, L.BLANKET_LINE_NUMBER BLANKET_LINE_NUMBER
, NVL(L.CREATED_BY
, 0) CREATED_BY
, L.CREATION_DATE CREATION_DATE
, NVL(L.LAST_UPDATED_BY
, 0) LAST_UPDATED_BY
, L.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NVL(L.LAST_UPDATE_LOGIN
, 0) LAST_UPDATE_LOGIN
FROM ISC_SERVICE_BOOK_SUM2 SERV
, ISC_CURR_BOOK_SUM2 CURR
, ISC_TMP_BOOK_SUM2 ISC
, OE_ORDER_LINES_ALL L
, OE_ORDER_HEADERS_ALL H
, OE_ORDER_LINES_ALL PL
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_SITE_USES_ALL SHIP_SU
, HZ_CUST_ACCT_SITES_ALL SHIP_CAS
, HZ_PARTY_SITES SHIP_PS
WHERE ISC.FROM_CURRENCY = CURR.FROM_CURRENCY
AND ISC.TO_CURRENCY1 = CURR.TO_CURRENCY1
AND ISC.TO_CURRENCY3 = CURR.TO_CURRENCY3
AND ISC.TIME_BOOKED_DATE_ID = CURR.CONVERSION_DATE
AND ISC.RATE_TYPE = CURR.CONVERSION_TYPE
AND ISC.PK1 = SERV.LINE_ID(+)
AND ISC.PK1 = L.LINE_ID
AND L.HEADER_ID= H.HEADER_ID
AND H.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND L.SHIP_TO_ORG_ID = SHIP_SU.SITE_USE_ID(+)
AND SHIP_SU.CUST_ACCT_SITE_ID= SHIP_CAS.CUST_ACCT_SITE_ID(+)
AND SHIP_CAS.PARTY_SITE_ID = SHIP_PS.PARTY_SITE_ID(+)
AND NVL(L.TOP_MODEL_LINE_ID
, L.LINE_ID) = PL.LINE_ID