VIEW: APPS.QPR_SR_OM_QUOTE_LINES_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), oe_order_misc_pub.get_concat_line_number(l.line_id) line_no, l.org_id, org.name, l.inventory_item_id, itm.concatenated_segments, itm.description, decode(l.item_type_code, 'MODEL', 'MDL', 'CLASS', decode(itm.replenish_to_order_flag, 'Y', 'ATOCLASS', 'PTOCLASS'), l.item_type_code), l.top_model_line_id, l.order_quantity_uom, uom.unit_of_measure_tl, uom.description, l.ordered_quantity, h.transactional_curr_code, curr.name, curr.description, nvl(l.original_list_price, l.unit_list_price), l.unit_selling_price, (l.unit_list_price -l.unit_selling_price), decode(nvl(l.unit_list_price, 0), 0, 0, ((l.unit_list_price -l.unit_selling_price) / l.unit_list_price) * 100), nvl(l.payment_term_id, h.payment_term_id), decode(nvl(l.payment_term_id, -1), -1, pth.name, ptl.name), decode(nvl(l.payment_term_id, -1), -1, pth.description, ptl.description), nvl(l.ship_to_org_id, h.ship_to_org_id), decode(nvl(l.line_id, -1), -1, (SELECT p.party_name || ':' || hca.account_number || ':' || hcsu.location site_desc FROM hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_cust_accounts hca, hz_parties p WHERE hcsu.site_use_id = l.ship_to_org_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.cust_account_id = hca.cust_account_id AND hca.party_id = p.party_id), (SELECT p.party_name || ':' || hca.account_number || ':' || hcsu.location site_desc FROM hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_cust_accounts hca, hz_parties p WHERE hcsu.site_use_id = h.ship_to_org_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.cust_account_id = hca.cust_account_id AND hca.party_id = p.party_id) ), decode(nvl(l.line_id, -1), -1, (SELECT p.party_name || ':' || hca.account_number || ':' || hcsu.location site_desc FROM hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_cust_accounts hca, hz_parties p WHERE hcsu.site_use_id = l.ship_to_org_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.cust_account_id = hca.cust_account_id AND hca.party_id = p.party_id), (SELECT p.party_name || ':' || hca.account_number || ':' || hcsu.location site_desc FROM hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_cust_accounts hca, hz_parties p WHERE hcsu.site_use_id = h.ship_to_org_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.cust_account_id = hca.cust_account_id AND hca.party_id = p.party_id) ), decode(nvl(LOWER(qpr_sr_util.read_parameter('QPR_QT_COMP_NAME_ATTRIBUTE')), '1'), 'attribute1', l.attribute1, 'attribute2', l.attribute2, 'attribute3', l.attribute3, 'attribute4', l.attribute4, 'attribute5', l.attribute5, 'attribute6', l.attribute6, 'attribute7', l.attribute7, 'attribute8', l.attribute8, 'attribute9', l.attribute9, 'attribute10', l.attribute10, 'attribute11', l.attribute11, 'attribute12', l.attribute12, 'attribute13', l.attribute13, 'attribute14', l.attribute14, 'attribute15', l.attribute15, NULL) AS competitor_name, decode(nvl(LOWER(qpr_sr_util.read_parameter('QPR_QT_COMP_PRIC_ATTRIBUTE')), '1'), 'attribute1', l.attribute1, 'attribute2', l.attribute2, 'attribute3', l.attribute3, 'attribute4', l.attribute4, 'attribute5', l.attribute5, 'attribute6', l.attribute6, 'attribute7', l.attribute7, 'attribute8', l.attribute8, 'attribute9', l.attribute9, 'attribute10', l.attribute10, 'attribute11', l.attribute11, 'attribute12', l.attribute12, 'attribute13', l.attribute13, 'attribute14', l.attribute14, 'attribute15', l.attribute15, NULL) AS competitor_price, NULL, NULL, nvl(l.shipping_method_code, h.shipping_method_code), decode(nvl(l.shipping_method_code, '*'), '*', smh.meaning, sml.meaning), decode(nvl(l.shipping_method_code, '*'), '*', smh.description, sml.description), NULL FROM oe_order_lines_all l, oe_order_headers_all h, hr_all_organization_units_vl org, mtl_system_items_vl itm, mtl_units_of_measure_vl uom, fnd_currencies_vl curr, ra_terms_vl pth, ra_terms_vl ptl, oe_ship_methods_v smh, oe_ship_methods_v sml WHERE l.cancelled_flag = 'N' AND l.line_category_code <> 'RETURN' AND l.header_id = h.header_id AND h.cancelled_flag = 'N' AND h.order_category_code <> 'RETURN' AND nvl(h.source_document_type_id, 0) <> 10 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.retrobill_request_id IS NULL AND nvl(l.order_source_id, 0) <> 27 AND l.org_id = org.organization_id AND l.inventory_item_id = itm.inventory_item_id AND l.org_id = itm.organization_id AND l.order_quantity_uom = uom.uom_code AND curr.currency_code = h.transactional_curr_code AND l.payment_term_id = ptl.term_id(+) AND h.payment_term_id = pth.term_id(+) AND smh.lookup_type(+) = 'SHIP_METHOD' AND smh.lookup_code(+) = h.shipping_method_code AND sml.lookup_type(+) = 'SHIP_METHOD' AND sml.lookup_code(+) = l.shipping_method_code order by l.line_number, l.shipment_number, nvl(l.option_number,0), nvl(l.component_number,0), nvl(l.service_number,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)
, OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(L.LINE_ID) LINE_NO
, L.ORG_ID
, ORG.NAME
, L.INVENTORY_ITEM_ID
, ITM.CONCATENATED_SEGMENTS
, ITM.DESCRIPTION
, DECODE(L.ITEM_TYPE_CODE
, 'MODEL'
, 'MDL'
, 'CLASS'
, DECODE(ITM.REPLENISH_TO_ORDER_FLAG
, 'Y'
, 'ATOCLASS'
, 'PTOCLASS')
, L.ITEM_TYPE_CODE)
, L.TOP_MODEL_LINE_ID
, L.ORDER_QUANTITY_UOM
, UOM.UNIT_OF_MEASURE_TL
, UOM.DESCRIPTION
, L.ORDERED_QUANTITY
, H.TRANSACTIONAL_CURR_CODE
, CURR.NAME
, CURR.DESCRIPTION
, NVL(L.ORIGINAL_LIST_PRICE
, L.UNIT_LIST_PRICE)
, L.UNIT_SELLING_PRICE
, (L.UNIT_LIST_PRICE -L.UNIT_SELLING_PRICE)
, DECODE(NVL(L.UNIT_LIST_PRICE
, 0)
, 0
, 0
, ((L.UNIT_LIST_PRICE -L.UNIT_SELLING_PRICE) / L.UNIT_LIST_PRICE) * 100)
, NVL(L.PAYMENT_TERM_ID
, H.PAYMENT_TERM_ID)
, DECODE(NVL(L.PAYMENT_TERM_ID
, -1)
, -1
, PTH.NAME
, PTL.NAME)
, DECODE(NVL(L.PAYMENT_TERM_ID
, -1)
, -1
, PTH.DESCRIPTION
, PTL.DESCRIPTION)
, NVL(L.SHIP_TO_ORG_ID
, H.SHIP_TO_ORG_ID)
, DECODE(NVL(L.LINE_ID
, -1)
, -1
, (SELECT P.PARTY_NAME || ':' || HCA.ACCOUNT_NUMBER || ':' || HCSU.LOCATION SITE_DESC
FROM HZ_CUST_SITE_USES_ALL HCSU
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES P
WHERE HCSU.SITE_USE_ID = L.SHIP_TO_ORG_ID
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = P.PARTY_ID)
, (SELECT P.PARTY_NAME || ':' || HCA.ACCOUNT_NUMBER || ':' || HCSU.LOCATION SITE_DESC
FROM HZ_CUST_SITE_USES_ALL HCSU
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES P
WHERE HCSU.SITE_USE_ID = H.SHIP_TO_ORG_ID
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = P.PARTY_ID) )
, DECODE(NVL(L.LINE_ID
, -1)
, -1
, (SELECT P.PARTY_NAME || ':' || HCA.ACCOUNT_NUMBER || ':' || HCSU.LOCATION SITE_DESC
FROM HZ_CUST_SITE_USES_ALL HCSU
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES P
WHERE HCSU.SITE_USE_ID = L.SHIP_TO_ORG_ID
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = P.PARTY_ID)
, (SELECT P.PARTY_NAME || ':' || HCA.ACCOUNT_NUMBER || ':' || HCSU.LOCATION SITE_DESC
FROM HZ_CUST_SITE_USES_ALL HCSU
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES P
WHERE HCSU.SITE_USE_ID = H.SHIP_TO_ORG_ID
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = P.PARTY_ID) )
, DECODE(NVL(LOWER(QPR_SR_UTIL.READ_PARAMETER('QPR_QT_COMP_NAME_ATTRIBUTE'))
, '1')
, 'ATTRIBUTE1'
, L.ATTRIBUTE1
, 'ATTRIBUTE2'
, L.ATTRIBUTE2
, 'ATTRIBUTE3'
, L.ATTRIBUTE3
, 'ATTRIBUTE4'
, L.ATTRIBUTE4
, 'ATTRIBUTE5'
, L.ATTRIBUTE5
, 'ATTRIBUTE6'
, L.ATTRIBUTE6
, 'ATTRIBUTE7'
, L.ATTRIBUTE7
, 'ATTRIBUTE8'
, L.ATTRIBUTE8
, 'ATTRIBUTE9'
, L.ATTRIBUTE9
, 'ATTRIBUTE10'
, L.ATTRIBUTE10
, 'ATTRIBUTE11'
, L.ATTRIBUTE11
, 'ATTRIBUTE12'
, L.ATTRIBUTE12
, 'ATTRIBUTE13'
, L.ATTRIBUTE13
, 'ATTRIBUTE14'
, L.ATTRIBUTE14
, 'ATTRIBUTE15'
, L.ATTRIBUTE15
, NULL) AS COMPETITOR_NAME
, DECODE(NVL(LOWER(QPR_SR_UTIL.READ_PARAMETER('QPR_QT_COMP_PRIC_ATTRIBUTE'))
, '1')
, 'ATTRIBUTE1'
, L.ATTRIBUTE1
, 'ATTRIBUTE2'
, L.ATTRIBUTE2
, 'ATTRIBUTE3'
, L.ATTRIBUTE3
, 'ATTRIBUTE4'
, L.ATTRIBUTE4
, 'ATTRIBUTE5'
, L.ATTRIBUTE5
, 'ATTRIBUTE6'
, L.ATTRIBUTE6
, 'ATTRIBUTE7'
, L.ATTRIBUTE7
, 'ATTRIBUTE8'
, L.ATTRIBUTE8
, 'ATTRIBUTE9'
, L.ATTRIBUTE9
, 'ATTRIBUTE10'
, L.ATTRIBUTE10
, 'ATTRIBUTE11'
, L.ATTRIBUTE11
, 'ATTRIBUTE12'
, L.ATTRIBUTE12
, 'ATTRIBUTE13'
, L.ATTRIBUTE13
, 'ATTRIBUTE14'
, L.ATTRIBUTE14
, 'ATTRIBUTE15'
, L.ATTRIBUTE15
, NULL) AS COMPETITOR_PRICE
, NULL
, NULL
, NVL(L.SHIPPING_METHOD_CODE
, H.SHIPPING_METHOD_CODE)
, DECODE(NVL(L.SHIPPING_METHOD_CODE
, '*')
, '*'
, SMH.MEANING
, SML.MEANING)
, DECODE(NVL(L.SHIPPING_METHOD_CODE
, '*')
, '*'
, SMH.DESCRIPTION
, SML.DESCRIPTION)
, NULL
FROM OE_ORDER_LINES_ALL L
, OE_ORDER_HEADERS_ALL H
, HR_ALL_ORGANIZATION_UNITS_VL ORG
, MTL_SYSTEM_ITEMS_VL ITM
, MTL_UNITS_OF_MEASURE_VL UOM
, FND_CURRENCIES_VL CURR
, RA_TERMS_VL PTH
, RA_TERMS_VL PTL
, OE_SHIP_METHODS_V SMH
, OE_SHIP_METHODS_V SML
WHERE L.CANCELLED_FLAG = 'N'
AND L.LINE_CATEGORY_CODE <> 'RETURN'
AND L.HEADER_ID = H.HEADER_ID
AND H.CANCELLED_FLAG = 'N'
AND H.ORDER_CATEGORY_CODE <> 'RETURN'
AND NVL(H.SOURCE_DOCUMENT_TYPE_ID
, 0) <> 10
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.RETROBILL_REQUEST_ID IS NULL
AND NVL(L.ORDER_SOURCE_ID
, 0) <> 27
AND L.ORG_ID = ORG.ORGANIZATION_ID
AND L.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND L.ORG_ID = ITM.ORGANIZATION_ID
AND L.ORDER_QUANTITY_UOM = UOM.UOM_CODE
AND CURR.CURRENCY_CODE = H.TRANSACTIONAL_CURR_CODE
AND L.PAYMENT_TERM_ID = PTL.TERM_ID(+)
AND H.PAYMENT_TERM_ID = PTH.TERM_ID(+)
AND SMH.LOOKUP_TYPE(+) = 'SHIP_METHOD'
AND SMH.LOOKUP_CODE(+) = H.SHIPPING_METHOD_CODE
AND SML.LOOKUP_TYPE(+) = 'SHIP_METHOD'
AND SML.LOOKUP_CODE(+) = L.SHIPPING_METHOD_CODE ORDER BY L.LINE_NUMBER
, L.SHIPMENT_NUMBER
, NVL(L.OPTION_NUMBER
, 0)
, NVL(L.COMPONENT_NUMBER
, 0)
, NVL(L.SERVICE_NUMBER
, 0)