VIEW: APPS.QPR_SR_COST_DATA_V

Source

View Text - Preformatted

SELECT a.line_id, nvl(trunc(a.booked_date,'dd'),to_date('1000/01/01', 'yyyy/mm/dd')), 'COST', a.hdr_trans_curr_conv * nvl(a.unit_list_price, 0) / a.uom_conv_rate , a.sob_curr_conv * nvl( mtlt.unit_cost, nvl((-1 * cst_cost_api.get_item_cost(1,a.inventory_item_id, a.ship_from_org_id)), 0) ) , a.top_model_line_id, a.LINK_TO_LINE_ID, a.ato_line_id, a.item_type_code, a.inventory_item_id, a.component_code, a.uom_conv_rate * nvl(a.ordered_quantity , 0) from ( select l.line_id, l.inventory_item_id, l.ship_from_org_id, l.unit_list_price, qpr_sr_util.uom_conv(l.order_quantity_uom, l.inventory_item_id, NULL) uom_conv_rate, qpr_sr_util.convert_global_amt( (select gsob.currency_code from HR_ORGANIZATION_INFORMATION HOI2, GL_SETS_OF_BOOKS GSOB where HOI2.ORG_INFORMATION_CONTEXT ='Accounting Information' and hoi2.organization_id = l.ship_from_org_id and HOI2.ORG_INFORMATION1 = GSOB.SET_OF_BOOKS_ID) , h.booked_date) sob_curr_conv, qpr_sr_util.convert_global_amt(h.transactional_curr_code, h.booked_date) hdr_trans_curr_conv, l.ordered_quantity , l.top_model_line_id, l.LINK_TO_LINE_ID, l.ato_line_id, (case when (select replenish_to_order_flag from mtl_system_items_b itm where itm.inventory_item_id = l.inventory_item_id and itm.organization_id = l.org_id) = 'Y' then decode(l.item_type_code, 'MODEL', 'ATOMODEL', 'CLASS', 'ATOCLASS', l.item_type_code) else decode(l.item_type_code, 'MODEL', 'PTOMODEL', 'CLASS', 'PTOCLASS', l.item_type_code) end) item_type_code, l.component_code, h.booked_date, h.sold_to_org_id from oe_order_lines_all l, oe_order_headers_all h where h.booked_flag = 'Y' and l.booked_flag = 'Y' and l.header_id = h.header_id and h.order_category_code <> 'RETURN' and nvl(h.source_document_type_id,0) <> 10 and l.line_category_code <> 'RETURN' and nvl(l.order_source_id,0) <> 27 and l.retrobill_request_id is null ) a, (select sum(-1* actual_cost * primary_quantity) / sum(primary_quantity) unit_cost , trx_source_line_id from mtl_material_transactions b where b.transaction_action_id=1 and b.transaction_source_type_id = 2 and transaction_type_id = 33 group by b.trx_source_line_id) mtlt WHERE mtlt.trx_source_line_id(+) = a.line_id and a.uom_conv_rate > 0 and a.sob_curr_conv > 0 and a.hdr_trans_curr_conv > 0 AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and a.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')

View Text - HTML Formatted

SELECT A.LINE_ID
, NVL(TRUNC(A.BOOKED_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, 'COST'
, A.HDR_TRANS_CURR_CONV * NVL(A.UNIT_LIST_PRICE
, 0) / A.UOM_CONV_RATE
, A.SOB_CURR_CONV * NVL( MTLT.UNIT_COST
, NVL((-1 * CST_COST_API.GET_ITEM_COST(1
, A.INVENTORY_ITEM_ID
, A.SHIP_FROM_ORG_ID))
, 0) )
, A.TOP_MODEL_LINE_ID
, A.LINK_TO_LINE_ID
, A.ATO_LINE_ID
, A.ITEM_TYPE_CODE
, A.INVENTORY_ITEM_ID
, A.COMPONENT_CODE
, A.UOM_CONV_RATE * NVL(A.ORDERED_QUANTITY
, 0)
FROM ( SELECT L.LINE_ID
, L.INVENTORY_ITEM_ID
, L.SHIP_FROM_ORG_ID
, L.UNIT_LIST_PRICE
, QPR_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID
, NULL) UOM_CONV_RATE
, QPR_SR_UTIL.CONVERT_GLOBAL_AMT( (SELECT GSOB.CURRENCY_CODE
FROM HR_ORGANIZATION_INFORMATION HOI2
, GL_SETS_OF_BOOKS GSOB
WHERE HOI2.ORG_INFORMATION_CONTEXT ='ACCOUNTING INFORMATION'
AND HOI2.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID
AND HOI2.ORG_INFORMATION1 = GSOB.SET_OF_BOOKS_ID)
, H.BOOKED_DATE) SOB_CURR_CONV
, QPR_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE) HDR_TRANS_CURR_CONV
, L.ORDERED_QUANTITY
, L.TOP_MODEL_LINE_ID
, L.LINK_TO_LINE_ID
, L.ATO_LINE_ID
, (CASE WHEN (SELECT REPLENISH_TO_ORDER_FLAG
FROM MTL_SYSTEM_ITEMS_B ITM
WHERE ITM.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = L.ORG_ID) = 'Y' THEN DECODE(L.ITEM_TYPE_CODE
, 'MODEL'
, 'ATOMODEL'
, 'CLASS'
, 'ATOCLASS'
, L.ITEM_TYPE_CODE) ELSE DECODE(L.ITEM_TYPE_CODE
, 'MODEL'
, 'PTOMODEL'
, 'CLASS'
, 'PTOCLASS'
, L.ITEM_TYPE_CODE) END) ITEM_TYPE_CODE
, L.COMPONENT_CODE
, H.BOOKED_DATE
, H.SOLD_TO_ORG_ID
FROM OE_ORDER_LINES_ALL L
, OE_ORDER_HEADERS_ALL H
WHERE H.BOOKED_FLAG = 'Y'
AND L.BOOKED_FLAG = 'Y'
AND L.HEADER_ID = H.HEADER_ID
AND H.ORDER_CATEGORY_CODE <> 'RETURN'
AND NVL(H.SOURCE_DOCUMENT_TYPE_ID
, 0) <> 10
AND L.LINE_CATEGORY_CODE <> 'RETURN'
AND NVL(L.ORDER_SOURCE_ID
, 0) <> 27
AND L.RETROBILL_REQUEST_ID IS NULL ) A
, (SELECT SUM(-1* ACTUAL_COST * PRIMARY_QUANTITY) / SUM(PRIMARY_QUANTITY) UNIT_COST
, TRX_SOURCE_LINE_ID
FROM MTL_MATERIAL_TRANSACTIONS B
WHERE B.TRANSACTION_ACTION_ID=1
AND B.TRANSACTION_SOURCE_TYPE_ID = 2
AND TRANSACTION_TYPE_ID = 33 GROUP BY B.TRX_SOURCE_LINE_ID) MTLT
WHERE MTLT.TRX_SOURCE_LINE_ID(+) = A.LINE_ID
AND A.UOM_CONV_RATE > 0
AND A.SOB_CURR_CONV > 0
AND A.HDR_TRANS_CURR_CONV > 0
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND A.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')