VIEW: APPS.QPR_SR_OM_QUOTE_COST_V

Source

View Text - Preformatted

SELECT l.header_id, l.line_id, nvl( (SELECT application_id FROM fnd_application_vl WHERE application_short_name = 'ONT' AND rownum < 2), qpr_sr_util.get_null_pk), 'COST', 'COST', (select meaning from qpr_lookups where lookup_type = 'QPR_DEAL_EROSIONS' and lookup_code = 'COST' and rownum < 2) , nvl2(sob.currency_code, (qpr_sr_util.convert_global_amt(sob.currency_code,nvl(h.quote_date, h.ordered_date),'Y',h.transactional_curr_code) * nvl(cst_cost_api.get_item_cost(1, l.inventory_item_id, l.ship_from_org_id), 0) / qpr_sr_util.uom_conv(qpr_sr_util.get_base_uom(l.inventory_item_id), l.inventory_item_id, l.order_quantity_uom)), 0) unit_cost, nvl2(sob.currency_code, (qpr_sr_util.convert_global_amt(sob.currency_code,nvl(h.quote_date, h.ordered_date),'Y',h.transactional_curr_code) * nvl(cst_cost_api.get_item_cost(1, l.inventory_item_id, l.ship_from_org_id), 0) * l.ordered_quantity), 0) total_erosion FROM oe_order_headers_all h, oe_order_lines_all l, (SELECT gsob.currency_code, hoi2.organization_id FROM hr_organization_information hoi2, gl_sets_of_books gsob WHERE hoi2.org_information_context = 'Accounting Information' AND hoi2.org_information1 = gsob.set_of_books_id) sob WHERE l.header_id = h.header_id AND l.cancelled_flag = 'N' AND l.line_category_code <> 'RETURN' AND h.cancelled_flag = 'N' AND h.order_category_code <> 'RETURN' AND nvl(h.source_document_type_id, 0) <> 10 AND l.retrobill_request_id IS NULL AND nvl(l.order_source_id, 0) <> 27 AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and h.sold_to_org_id not in ( select cust_account_id from hz_cust_accounts where nvl(customer_type, 'C') = 'I')) or nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'Y') AND l.ship_from_org_id = sob.organization_id (+) AND qpr_sr_util.convert_global_amt(nvl(sob.currency_code, h.transactional_curr_code), nvl(h.quote_date, h.ordered_date), 'Y', h.transactional_curr_code) > 0 AND qpr_sr_util.uom_conv(qpr_sr_util.get_base_uom(l.inventory_item_id), l.inventory_item_id, l.order_quantity_uom) > 0

View Text - HTML Formatted

SELECT L.HEADER_ID
, L.LINE_ID
, NVL( (SELECT APPLICATION_ID
FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME = 'ONT'
AND ROWNUM < 2)
, QPR_SR_UTIL.GET_NULL_PK)
, 'COST'
, 'COST'
, (SELECT MEANING
FROM QPR_LOOKUPS
WHERE LOOKUP_TYPE = 'QPR_DEAL_EROSIONS'
AND LOOKUP_CODE = 'COST'
AND ROWNUM < 2)
, NVL2(SOB.CURRENCY_CODE
, (QPR_SR_UTIL.CONVERT_GLOBAL_AMT(SOB.CURRENCY_CODE
, NVL(H.QUOTE_DATE
, H.ORDERED_DATE)
, 'Y'
, H.TRANSACTIONAL_CURR_CODE) * NVL(CST_COST_API.GET_ITEM_COST(1
, L.INVENTORY_ITEM_ID
, L.SHIP_FROM_ORG_ID)
, 0) / QPR_SR_UTIL.UOM_CONV(QPR_SR_UTIL.GET_BASE_UOM(L.INVENTORY_ITEM_ID)
, L.INVENTORY_ITEM_ID
, L.ORDER_QUANTITY_UOM))
, 0) UNIT_COST
, NVL2(SOB.CURRENCY_CODE
, (QPR_SR_UTIL.CONVERT_GLOBAL_AMT(SOB.CURRENCY_CODE
, NVL(H.QUOTE_DATE
, H.ORDERED_DATE)
, 'Y'
, H.TRANSACTIONAL_CURR_CODE) * NVL(CST_COST_API.GET_ITEM_COST(1
, L.INVENTORY_ITEM_ID
, L.SHIP_FROM_ORG_ID)
, 0) * L.ORDERED_QUANTITY)
, 0) TOTAL_EROSION
FROM OE_ORDER_HEADERS_ALL H
, OE_ORDER_LINES_ALL L
, (SELECT GSOB.CURRENCY_CODE
, HOI2.ORGANIZATION_ID
FROM HR_ORGANIZATION_INFORMATION HOI2
, GL_SETS_OF_BOOKS GSOB
WHERE HOI2.ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND HOI2.ORG_INFORMATION1 = GSOB.SET_OF_BOOKS_ID) SOB
WHERE L.HEADER_ID = H.HEADER_ID
AND L.CANCELLED_FLAG = 'N'
AND L.LINE_CATEGORY_CODE <> 'RETURN'
AND H.CANCELLED_FLAG = 'N'
AND H.ORDER_CATEGORY_CODE <> 'RETURN'
AND NVL(H.SOURCE_DOCUMENT_TYPE_ID
, 0) <> 10
AND L.RETROBILL_REQUEST_ID IS NULL
AND NVL(L.ORDER_SOURCE_ID
, 0) <> 27
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND H.SOLD_TO_ORG_ID NOT IN ( SELECT CUST_ACCOUNT_ID
FROM HZ_CUST_ACCOUNTS
WHERE NVL(CUSTOMER_TYPE
, 'C') = 'I')) OR NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'Y')
AND L.SHIP_FROM_ORG_ID = SOB.ORGANIZATION_ID (+)
AND QPR_SR_UTIL.CONVERT_GLOBAL_AMT(NVL(SOB.CURRENCY_CODE
, H.TRANSACTIONAL_CURR_CODE)
, NVL(H.QUOTE_DATE
, H.ORDERED_DATE)
, 'Y'
, H.TRANSACTIONAL_CURR_CODE) > 0
AND QPR_SR_UTIL.UOM_CONV(QPR_SR_UTIL.GET_BASE_UOM(L.INVENTORY_ITEM_ID)
, L.INVENTORY_ITEM_ID
, L.ORDER_QUANTITY_UOM) > 0