eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.QPR_SR_QUOTE_LINES_V

Source

View Text - Preformatted


SELECT l.quote_header_id, l.quote_line_id, nvl( (SELECT application_id FROM fnd_application_vl WHERE application_short_name = 'ASO' AND rownum < 2), qpr_sr_util.get_null_pk) source_id, l.line_number, l.start_date_active, l.end_date_active, l.org_id, (SELECT org.name FROM hr_all_organization_units_vl org WHERE org.organization_id = l.org_id AND rownum < 2) org_name, l.inventory_item_id, itm.concatenated_segments, itm.description, decode(itm.bom_item_type, 2, decode(itm.replenish_to_order_flag, 'Y', 'ATOCLASS', 'PTOCLASS'), l.item_type_code), ldet.top_model_line_id, l.uom_code, uom.unit_of_measure_tl, uom.description, l.quantity, nvl(l.currency_code, h.currency_code), curr.name, curr.description, l.line_list_price, l.line_quote_price, l.line_adjusted_amount, l.line_adjusted_percent, nvl(lp.payment_term_id, hp.payment_term_id) payment_term, nvl2(lp.payment_term_id, lp.name, hp.name) payment_sd, nvl2(lp.payment_term_id, lp.description, hp.description) payment_ld, nvl2(ls.site_use_id, ls.site_use_id, hs.site_use_id) geo_id, nvl2(ls.site_use_id, ls.site_desc, hs.site_desc) geo_sd, nvl2(ls.site_use_id, ls.site_desc, hs.site_desc) geo_ld, 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) 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) competitor_price, NULL, NULL, nvl(ls.ship_method_code, hs.ship_method_code) ship_method_code, nvl2(ls.ship_method_code, ls.ship_mthd_desc, hs.ship_mthd_desc) ship_method_sd, nvl2(ls.ship_method_code, ls.ship_mthd_desc, hs.ship_mthd_desc) ship_method_ld, NULL FROM aso_quote_headers_all h, aso_quote_lines_all l, aso_quote_line_details ldet, mtl_system_items_vl itm, mtl_units_of_measure_vl uom, fnd_currencies_vl curr, (SELECT DISTINCT h.quote_header_id quote_header_id, hcsu.site_use_id site_use_id, p.party_name || ':' || hca.account_number || ':' || hcsu.location site_desc, asos.ship_method_code ship_method_code, shm.meaning ship_mthd_desc FROM aso_shipments asos, aso_quote_headers_all h, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, hz_parties p, hz_cust_accounts hca, oe_ship_methods_v shm WHERE asos.quote_header_id = h.quote_header_id AND asos.quote_line_id IS NULL AND hcas.party_site_id = asos.ship_to_party_site_id AND hcas.cust_account_id = asos.ship_to_cust_account_id AND hcas.org_id = h.org_id AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id AND hcas.org_id = hcsu.org_id AND hcsu.site_use_code = 'SHIP_TO' AND p.party_id = asos.ship_to_cust_party_id AND hca.cust_account_id = asos.ship_to_cust_account_id AND asos.ship_method_code = shm.lookup_code(+) AND shm.lookup_type(+) = 'SHIP_METHOD') hs, (select asos.quote_header_id, asos.quote_line_id, hcsu.site_use_id site_use_id, hcsu.org_id, p.party_name || ':' || hca.account_number || ':' || hcsu.location site_desc, asos.ship_method_code, shm.meaning ship_mthd_desc from hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, hz_parties p, hz_cust_accounts hca, aso_shipments asos, oe_ship_methods_v shm where asos.quote_line_id is not null AND (asos.ship_to_cust_account_id IS NOT NULL or asos.ship_method_code is not null) AND hcas.party_site_id(+) = asos.ship_to_party_site_id AND hcas.cust_account_id(+) = asos.ship_to_cust_account_id and hcas.cust_acct_site_id = hcsu.cust_acct_site_id(+) AND hcas.org_id = hcsu.org_id(+) AND hcsu.site_use_code(+) = 'SHIP_TO' AND p.party_id(+) = asos.ship_to_cust_party_id AND hca.cust_account_id(+) = asos.ship_to_cust_account_id AND asos.ship_method_code = shm.lookup_code(+) AND shm.lookup_type(+) = 'SHIP_METHOD') ls, (SELECT DISTINCT hp.quote_header_id, hp.payment_term_id, pth.name, pth.description FROM aso_payments hp, ra_terms_vl pth WHERE hp.quote_line_id IS NULL AND hp.payment_term_id = pth.term_id(+)) hp, (SELECT DISTINCT lp.quote_header_id, lp.quote_line_id, lp.payment_term_id, ptl.name, ptl.description FROM aso_payments lp, ra_terms_vl ptl WHERE lp.quote_line_id IS NOT NULL AND lp.payment_term_id = ptl.term_id(+)) lp WHERE l.inventory_item_id = itm.inventory_item_id AND l.organization_id = itm.organization_id AND l.quote_header_id = h.quote_header_id AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and h.cust_account_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.uom_code = uom.uom_code AND nvl(l.currency_code, h.currency_code) = curr.currency_code AND l.quote_line_id = ldet.quote_line_id(+) AND l.quote_header_id = hs.quote_header_id(+) AND l.quote_header_id = ls.quote_header_id(+) AND l.quote_line_id = ls.quote_line_id(+) and l.org_id = nvl(ls.org_id, l.org_id) AND l.quote_line_id = lp.quote_line_id(+) AND l.quote_header_id = lp.quote_header_id(+) AND l.quote_header_id = hp.quote_header_id(+) ORDER BY l.line_number, decode(ldet.service_ref_type_code, 'QUOTE', (SELECT bom_sort_order FROM aso_quote_line_details WHERE quote_line_id = ldet.service_ref_line_id), ldet.bom_sort_order), decode(ldet.service_ref_type_code, 'QUOTE', ldet.service_ref_line_id, l.quote_line_id), decode(l.serviceable_product_flag, 'Y', 0, l.quote_line_id)

View Text - HTML Formatted

SELECT L.QUOTE_HEADER_ID
, L.QUOTE_LINE_ID
, NVL( (SELECT APPLICATION_ID
FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME = 'ASO'
AND ROWNUM < 2)
, QPR_SR_UTIL.GET_NULL_PK) SOURCE_ID
, L.LINE_NUMBER
, L.START_DATE_ACTIVE
, L.END_DATE_ACTIVE
, L.ORG_ID
, (SELECT ORG.NAME
FROM HR_ALL_ORGANIZATION_UNITS_VL ORG
WHERE ORG.ORGANIZATION_ID = L.ORG_ID
AND ROWNUM < 2) ORG_NAME
, L.INVENTORY_ITEM_ID
, ITM.CONCATENATED_SEGMENTS
, ITM.DESCRIPTION
, DECODE(ITM.BOM_ITEM_TYPE
, 2
, DECODE(ITM.REPLENISH_TO_ORDER_FLAG
, 'Y'
, 'ATOCLASS'
, 'PTOCLASS')
, L.ITEM_TYPE_CODE)
, LDET.TOP_MODEL_LINE_ID
, L.UOM_CODE
, UOM.UNIT_OF_MEASURE_TL
, UOM.DESCRIPTION
, L.QUANTITY
, NVL(L.CURRENCY_CODE
, H.CURRENCY_CODE)
, CURR.NAME
, CURR.DESCRIPTION
, L.LINE_LIST_PRICE
, L.LINE_QUOTE_PRICE
, L.LINE_ADJUSTED_AMOUNT
, L.LINE_ADJUSTED_PERCENT
, NVL(LP.PAYMENT_TERM_ID
, HP.PAYMENT_TERM_ID) PAYMENT_TERM
, NVL2(LP.PAYMENT_TERM_ID
, LP.NAME
, HP.NAME) PAYMENT_SD
, NVL2(LP.PAYMENT_TERM_ID
, LP.DESCRIPTION
, HP.DESCRIPTION) PAYMENT_LD
, NVL2(LS.SITE_USE_ID
, LS.SITE_USE_ID
, HS.SITE_USE_ID) GEO_ID
, NVL2(LS.SITE_USE_ID
, LS.SITE_DESC
, HS.SITE_DESC) GEO_SD
, NVL2(LS.SITE_USE_ID
, LS.SITE_DESC
, HS.SITE_DESC) GEO_LD
, 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) 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) COMPETITOR_PRICE
, NULL
, NULL
, NVL(LS.SHIP_METHOD_CODE
, HS.SHIP_METHOD_CODE) SHIP_METHOD_CODE
, NVL2(LS.SHIP_METHOD_CODE
, LS.SHIP_MTHD_DESC
, HS.SHIP_MTHD_DESC) SHIP_METHOD_SD
, NVL2(LS.SHIP_METHOD_CODE
, LS.SHIP_MTHD_DESC
, HS.SHIP_MTHD_DESC) SHIP_METHOD_LD
, NULL
FROM ASO_QUOTE_HEADERS_ALL H
, ASO_QUOTE_LINES_ALL L
, ASO_QUOTE_LINE_DETAILS LDET
, MTL_SYSTEM_ITEMS_VL ITM
, MTL_UNITS_OF_MEASURE_VL UOM
, FND_CURRENCIES_VL CURR
, (SELECT DISTINCT H.QUOTE_HEADER_ID QUOTE_HEADER_ID
, HCSU.SITE_USE_ID SITE_USE_ID
, P.PARTY_NAME || ':' || HCA.ACCOUNT_NUMBER || ':' || HCSU.LOCATION SITE_DESC
, ASOS.SHIP_METHOD_CODE SHIP_METHOD_CODE
, SHM.MEANING SHIP_MTHD_DESC
FROM ASO_SHIPMENTS ASOS
, ASO_QUOTE_HEADERS_ALL H
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_SITE_USES_ALL HCSU
, HZ_PARTIES P
, HZ_CUST_ACCOUNTS HCA
, OE_SHIP_METHODS_V SHM
WHERE ASOS.QUOTE_HEADER_ID = H.QUOTE_HEADER_ID
AND ASOS.QUOTE_LINE_ID IS NULL
AND HCAS.PARTY_SITE_ID = ASOS.SHIP_TO_PARTY_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = ASOS.SHIP_TO_CUST_ACCOUNT_ID
AND HCAS.ORG_ID = H.ORG_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HCAS.ORG_ID = HCSU.ORG_ID
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
AND P.PARTY_ID = ASOS.SHIP_TO_CUST_PARTY_ID
AND HCA.CUST_ACCOUNT_ID = ASOS.SHIP_TO_CUST_ACCOUNT_ID
AND ASOS.SHIP_METHOD_CODE = SHM.LOOKUP_CODE(+)
AND SHM.LOOKUP_TYPE(+) = 'SHIP_METHOD') HS
, (SELECT ASOS.QUOTE_HEADER_ID
, ASOS.QUOTE_LINE_ID
, HCSU.SITE_USE_ID SITE_USE_ID
, HCSU.ORG_ID
, P.PARTY_NAME || ':' || HCA.ACCOUNT_NUMBER || ':' || HCSU.LOCATION SITE_DESC
, ASOS.SHIP_METHOD_CODE
, SHM.MEANING SHIP_MTHD_DESC
FROM HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_SITE_USES_ALL HCSU
, HZ_PARTIES P
, HZ_CUST_ACCOUNTS HCA
, ASO_SHIPMENTS ASOS
, OE_SHIP_METHODS_V SHM
WHERE ASOS.QUOTE_LINE_ID IS NOT NULL
AND (ASOS.SHIP_TO_CUST_ACCOUNT_ID IS NOT NULL OR ASOS.SHIP_METHOD_CODE IS NOT NULL)
AND HCAS.PARTY_SITE_ID(+) = ASOS.SHIP_TO_PARTY_SITE_ID
AND HCAS.CUST_ACCOUNT_ID(+) = ASOS.SHIP_TO_CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID(+)
AND HCAS.ORG_ID = HCSU.ORG_ID(+)
AND HCSU.SITE_USE_CODE(+) = 'SHIP_TO'
AND P.PARTY_ID(+) = ASOS.SHIP_TO_CUST_PARTY_ID
AND HCA.CUST_ACCOUNT_ID(+) = ASOS.SHIP_TO_CUST_ACCOUNT_ID
AND ASOS.SHIP_METHOD_CODE = SHM.LOOKUP_CODE(+)
AND SHM.LOOKUP_TYPE(+) = 'SHIP_METHOD') LS
, (SELECT DISTINCT HP.QUOTE_HEADER_ID
, HP.PAYMENT_TERM_ID
, PTH.NAME
, PTH.DESCRIPTION
FROM ASO_PAYMENTS HP
, RA_TERMS_VL PTH
WHERE HP.QUOTE_LINE_ID IS NULL
AND HP.PAYMENT_TERM_ID = PTH.TERM_ID(+)) HP
, (SELECT DISTINCT LP.QUOTE_HEADER_ID
, LP.QUOTE_LINE_ID
, LP.PAYMENT_TERM_ID
, PTL.NAME
, PTL.DESCRIPTION
FROM ASO_PAYMENTS LP
, RA_TERMS_VL PTL
WHERE LP.QUOTE_LINE_ID IS NOT NULL
AND LP.PAYMENT_TERM_ID = PTL.TERM_ID(+)) LP
WHERE L.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND L.ORGANIZATION_ID = ITM.ORGANIZATION_ID
AND L.QUOTE_HEADER_ID = H.QUOTE_HEADER_ID
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND H.CUST_ACCOUNT_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.UOM_CODE = UOM.UOM_CODE
AND NVL(L.CURRENCY_CODE
, H.CURRENCY_CODE) = CURR.CURRENCY_CODE
AND L.QUOTE_LINE_ID = LDET.QUOTE_LINE_ID(+)
AND L.QUOTE_HEADER_ID = HS.QUOTE_HEADER_ID(+)
AND L.QUOTE_HEADER_ID = LS.QUOTE_HEADER_ID(+)
AND L.QUOTE_LINE_ID = LS.QUOTE_LINE_ID(+)
AND L.ORG_ID = NVL(LS.ORG_ID
, L.ORG_ID)
AND L.QUOTE_LINE_ID = LP.QUOTE_LINE_ID(+)
AND L.QUOTE_HEADER_ID = LP.QUOTE_HEADER_ID(+)
AND L.QUOTE_HEADER_ID = HP.QUOTE_HEADER_ID(+) ORDER BY L.LINE_NUMBER
, DECODE(LDET.SERVICE_REF_TYPE_CODE
, 'QUOTE'
, (SELECT BOM_SORT_ORDER
FROM ASO_QUOTE_LINE_DETAILS
WHERE QUOTE_LINE_ID = LDET.SERVICE_REF_LINE_ID)
, LDET.BOM_SORT_ORDER)
, DECODE(LDET.SERVICE_REF_TYPE_CODE
, 'QUOTE'
, LDET.SERVICE_REF_LINE_ID
, L.QUOTE_LINE_ID)
, DECODE(L.SERVICEABLE_PRODUCT_FLAG
, 'Y'
, 0
, L.QUOTE_LINE_ID)