VIEW: APPS.QPR_SR_OM_QUOTE_HEADERS_V

Source

View Text - Preformatted

SELECT h.header_id, nvl(h.order_number, h.quote_number), nvl(h.quote_date,h.ordered_date), h.version_number, ttyp.name, nvl(h.order_number, h.quote_number) || ' - Ver ' || h.version_number, h.sales_document_name, nvl( (SELECT application_id FROM fnd_application_vl WHERE application_short_name = 'ONT' AND rownum < 2), qpr_sr_util.get_null_pk), 'Oracle Order Management', 'Oracle Order Management', h.transactional_curr_code, curr.name, curr.description, h.sold_to_org_id, cust.account_number, cust.account_name, h.salesrep_id, rep.name, rep.salesrep_number, rep.email_address, h.sales_channel_code, sc.meaning, sc.description, ps.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)), h.freight_terms_code, ft.meaning, ft.description, h.expiration_date, decode(lower(nvl(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 oe_order_headers_all h, oe_trxt_types_noorgs_vl ttyp, fnd_currencies_vl curr, ra_salesreps_all rep, so_lookups sc, so_lookups ft, hz_cust_accounts cust, hz_cust_site_uses_all isu, hz_cust_acct_sites_all cas, hz_party_sites ps, hz_locations loc WHERE h.cancelled_flag = 'N' AND h.order_category_code <> 'RETURN' and nvl(h.source_document_type_id, 0) <> 10 AND h.order_type_id = ttyp.transaction_type_id AND h.org_id = ttyp.org_id AND h.transactional_curr_code = curr.currency_code AND h.salesrep_id = rep.salesrep_id(+) AND h.org_id = rep.org_id(+) AND h.sold_to_org_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.invoice_to_org_id = isu.site_use_id(+) AND isu.site_use_code(+) = 'BILL_TO' AND isu.cust_acct_site_id = cas.cust_acct_site_id(+) AND cas.party_site_id = ps.party_site_id(+) AND ps.location_id = loc.location_id(+) AND h.sales_channel_code = sc.lookup_code(+) AND sc.lookup_type(+) = 'SALES_CHANNEL' AND h.freight_terms_code = ft.lookup_code(+) AND ft.lookup_type(+) = 'FREIGHT_TERMS'

View Text - HTML Formatted

SELECT H.HEADER_ID
, NVL(H.ORDER_NUMBER
, H.QUOTE_NUMBER)
, NVL(H.QUOTE_DATE
, H.ORDERED_DATE)
, H.VERSION_NUMBER
, TTYP.NAME
, NVL(H.ORDER_NUMBER
, H.QUOTE_NUMBER) || ' - VER ' || H.VERSION_NUMBER
, H.SALES_DOCUMENT_NAME
, NVL( (SELECT APPLICATION_ID
FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME = 'ONT'
AND ROWNUM < 2)
, QPR_SR_UTIL.GET_NULL_PK)
, 'ORACLE ORDER MANAGEMENT'
, 'ORACLE ORDER MANAGEMENT'
, H.TRANSACTIONAL_CURR_CODE
, CURR.NAME
, CURR.DESCRIPTION
, H.SOLD_TO_ORG_ID
, CUST.ACCOUNT_NUMBER
, CUST.ACCOUNT_NAME
, H.SALESREP_ID
, REP.NAME
, REP.SALESREP_NUMBER
, REP.EMAIL_ADDRESS
, H.SALES_CHANNEL_CODE
, SC.MEANING
, SC.DESCRIPTION
, PS.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))
, H.FREIGHT_TERMS_CODE
, FT.MEANING
, FT.DESCRIPTION
, H.EXPIRATION_DATE
, DECODE(LOWER(NVL(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 OE_ORDER_HEADERS_ALL H
, OE_TRXT_TYPES_NOORGS_VL TTYP
, FND_CURRENCIES_VL CURR
, RA_SALESREPS_ALL REP
, SO_LOOKUPS SC
, SO_LOOKUPS FT
, HZ_CUST_ACCOUNTS CUST
, HZ_CUST_SITE_USES_ALL ISU
, HZ_CUST_ACCT_SITES_ALL CAS
, HZ_PARTY_SITES PS
, HZ_LOCATIONS LOC
WHERE H.CANCELLED_FLAG = 'N'
AND H.ORDER_CATEGORY_CODE <> 'RETURN'
AND NVL(H.SOURCE_DOCUMENT_TYPE_ID
, 0) <> 10
AND H.ORDER_TYPE_ID = TTYP.TRANSACTION_TYPE_ID
AND H.ORG_ID = TTYP.ORG_ID
AND H.TRANSACTIONAL_CURR_CODE = CURR.CURRENCY_CODE
AND H.SALESREP_ID = REP.SALESREP_ID(+)
AND H.ORG_ID = REP.ORG_ID(+)
AND H.SOLD_TO_ORG_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.INVOICE_TO_ORG_ID = ISU.SITE_USE_ID(+)
AND ISU.SITE_USE_CODE(+) = 'BILL_TO'
AND ISU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID(+)
AND CAS.PARTY_SITE_ID = PS.PARTY_SITE_ID(+)
AND PS.LOCATION_ID = LOC.LOCATION_ID(+)
AND H.SALES_CHANNEL_CODE = SC.LOOKUP_CODE(+)
AND SC.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND H.FREIGHT_TERMS_CODE = FT.LOOKUP_CODE(+)
AND FT.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'