VIEW: APPS.QPR_SR_QUOTE_HEADERS_V

Source

View Text - Preformatted

SELECT h.quote_header_id, h.quote_number, h.creation_date, h.quote_version, h.quote_number || ' - Ver ' || h.quote_version, h.quote_name, nvl( (SELECT application_id FROM fnd_application_vl WHERE application_short_name = 'ASO' AND rownum < 2), qpr_sr_util.get_null_pk), 'Oracle Quoting', 'Oracle Quoting', curr.currency_code, curr.name, curr.description, h.cust_account_id, cust.account_number, cust.account_name, rep.salesrep_id, rep.name, rep.salesrep_number, rep.email_address, h.sales_channel_code, sc.meaning, sc.description, h.invoice_to_party_site_id, (loc.address1 || decode(loc.city, '', '', ('-' || loc.city)) || decode(loc.postal_code, '', '', ('-' || loc.postal_code)) || decode(nvl(loc.province, loc.county), '', '', ('-' || nvl(loc.province, loc.county))) || decode(nvl(loc.province, loc.state), '', '', ('-' || nvl(loc.province, loc.state))) || decode(loc.country, '', '', '-' || loc.country)), hs.freight_terms_code, hs.meaning, hs.description, h.quote_expiration_date, decode(nvl(LOWER(qpr_sr_util.read_parameter('QPR_QT_COMMENT_ATTRIBUTE')), '1'), 'attribute1', h.attribute1, 'attribute2', h.attribute2, 'attribute3', h.attribute3, 'attribute4', h.attribute4, 'attribute5', h.attribute5, 'attribute6', h.attribute6, 'attribute7', h.attribute7, 'attribute8', h.attribute8, 'attribute9', h.attribute9, 'attribute10', h.attribute10, 'attribute11', h.attribute11, 'attribute12', h.attribute12, 'attribute13', h.attribute13, 'attribute14', h.attribute14, 'attribute15', h.attribute15, NULL), NULL FROM aso_quote_headers_all h, fnd_currencies_vl curr, hz_cust_accounts cust, ra_salesreps_all rep, so_lookups sc, hz_party_sites hps, hz_locations loc, ( select asos.quote_header_id, asos.freight_terms_code, ft.meaning, ft.description from aso_shipments asos, so_lookups ft where asos.quote_line_id IS NULL AND asos.freight_terms_code = ft.lookup_code(+) AND ft.lookup_type(+) = 'FREIGHT_TERMS' ) hs WHERE h.currency_code = curr.currency_code AND h.cust_account_id = cust.cust_account_id AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and nvl(cust.customer_type, 'C') <> 'I') or nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'Y') AND h.resource_id = rep.resource_id(+) AND h.org_id = rep.org_id(+) AND h.sales_channel_code = sc.lookup_code(+) AND sc.lookup_type(+) = 'SALES_CHANNEL' AND h.invoice_to_party_site_id = hps.party_site_id(+) AND hps.location_id = loc.location_id(+) AND h.quote_header_id = hs.quote_header_id(+)

View Text - HTML Formatted

SELECT H.QUOTE_HEADER_ID
, H.QUOTE_NUMBER
, H.CREATION_DATE
, H.QUOTE_VERSION
, H.QUOTE_NUMBER || ' - VER ' || H.QUOTE_VERSION
, H.QUOTE_NAME
, NVL( (SELECT APPLICATION_ID
FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME = 'ASO'
AND ROWNUM < 2)
, QPR_SR_UTIL.GET_NULL_PK)
, 'ORACLE QUOTING'
, 'ORACLE QUOTING'
, CURR.CURRENCY_CODE
, CURR.NAME
, CURR.DESCRIPTION
, H.CUST_ACCOUNT_ID
, CUST.ACCOUNT_NUMBER
, CUST.ACCOUNT_NAME
, REP.SALESREP_ID
, REP.NAME
, REP.SALESREP_NUMBER
, REP.EMAIL_ADDRESS
, H.SALES_CHANNEL_CODE
, SC.MEANING
, SC.DESCRIPTION
, H.INVOICE_TO_PARTY_SITE_ID
, (LOC.ADDRESS1 || DECODE(LOC.CITY
, ''
, ''
, ('-' || LOC.CITY)) || DECODE(LOC.POSTAL_CODE
, ''
, ''
, ('-' || LOC.POSTAL_CODE)) || DECODE(NVL(LOC.PROVINCE
, LOC.COUNTY)
, ''
, ''
, ('-' || NVL(LOC.PROVINCE
, LOC.COUNTY))) || DECODE(NVL(LOC.PROVINCE
, LOC.STATE)
, ''
, ''
, ('-' || NVL(LOC.PROVINCE
, LOC.STATE))) || DECODE(LOC.COUNTRY
, ''
, ''
, '-' || LOC.COUNTRY))
, HS.FREIGHT_TERMS_CODE
, HS.MEANING
, HS.DESCRIPTION
, H.QUOTE_EXPIRATION_DATE
, DECODE(NVL(LOWER(QPR_SR_UTIL.READ_PARAMETER('QPR_QT_COMMENT_ATTRIBUTE'))
, '1')
, 'ATTRIBUTE1'
, H.ATTRIBUTE1
, 'ATTRIBUTE2'
, H.ATTRIBUTE2
, 'ATTRIBUTE3'
, H.ATTRIBUTE3
, 'ATTRIBUTE4'
, H.ATTRIBUTE4
, 'ATTRIBUTE5'
, H.ATTRIBUTE5
, 'ATTRIBUTE6'
, H.ATTRIBUTE6
, 'ATTRIBUTE7'
, H.ATTRIBUTE7
, 'ATTRIBUTE8'
, H.ATTRIBUTE8
, 'ATTRIBUTE9'
, H.ATTRIBUTE9
, 'ATTRIBUTE10'
, H.ATTRIBUTE10
, 'ATTRIBUTE11'
, H.ATTRIBUTE11
, 'ATTRIBUTE12'
, H.ATTRIBUTE12
, 'ATTRIBUTE13'
, H.ATTRIBUTE13
, 'ATTRIBUTE14'
, H.ATTRIBUTE14
, 'ATTRIBUTE15'
, H.ATTRIBUTE15
, NULL)
, NULL
FROM ASO_QUOTE_HEADERS_ALL H
, FND_CURRENCIES_VL CURR
, HZ_CUST_ACCOUNTS CUST
, RA_SALESREPS_ALL REP
, SO_LOOKUPS SC
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS LOC
, ( SELECT ASOS.QUOTE_HEADER_ID
, ASOS.FREIGHT_TERMS_CODE
, FT.MEANING
, FT.DESCRIPTION
FROM ASO_SHIPMENTS ASOS
, SO_LOOKUPS FT
WHERE ASOS.QUOTE_LINE_ID IS NULL
AND ASOS.FREIGHT_TERMS_CODE = FT.LOOKUP_CODE(+)
AND FT.LOOKUP_TYPE(+) = 'FREIGHT_TERMS' ) HS
WHERE H.CURRENCY_CODE = CURR.CURRENCY_CODE
AND H.CUST_ACCOUNT_ID = CUST.CUST_ACCOUNT_ID
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND NVL(CUST.CUSTOMER_TYPE
, 'C') <> 'I') OR NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'Y')
AND H.RESOURCE_ID = REP.RESOURCE_ID(+)
AND H.ORG_ID = REP.ORG_ID(+)
AND H.SALES_CHANNEL_CODE = SC.LOOKUP_CODE(+)
AND SC.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND H.INVOICE_TO_PARTY_SITE_ID = HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID = LOC.LOCATION_ID(+)
AND H.QUOTE_HEADER_ID = HS.QUOTE_HEADER_ID(+)