VIEW: APPS.AST_QUOTES_MV

Source

View Text - Preformatted

SELECT QTE.QUOTE_NAME, QTE.QUOTE_NUMBER||'-'||QTE.QUOTE_VERSION quote_number_version, QTE.CREATION_DATE, QTE.last_update_date, QTE.QUOTE_STATUS_ID, STA_t.MEANING quote_status, nvl(QTE.TOTAL_QUOTE_PRICE, 0.0) quote_total, QTE.QUOTE_EXPIRATION_DATE, ACCT.ACCOUNT_NUMBER, QTE.QUOTE_HEADER_ID, QTE.CUST_ACCOUNT_ID, ACCT.PARTY_ID account_party_id, QTE.CURRENCY_CODE, APP.PARTY_NAME sold_to_contact_name, qte.resource_id , srp.name resource_name, qte.party_id customer_id, app.party_type sold_to_contact_party_type, qte.org_id, fndlang.language_code lang FROM aso_quote_headers_all QTE, aso_quote_statuses_b STA_b, aso_quote_statuses_tl STA_t, hz_cust_accounts ACCT, hz_parties APP, HZ_RELATIONSHIPS APPR , JTF_RS_SALESREPS SRP, fnd_languages fndlang WHERE fndlang.installed_flag in ('I', 'B') AND QTE.QUOTE_STATUS_ID = STA_b.QUOTE_STATUS_ID AND sta_b.quote_status_id = sta_t.quote_status_id and sta_t.language = fndlang.language_code AND (trunc(SYSDATE) between NVL(trunc(sta_b.EFFECTIVE_START_DATE), trunc (SYSDATE)) and NVL(trunc(STA_b.EFFECTIVE_END_DATE), trunc(SYSDATE))) and QTE.CUST_ACCOUNT_ID = ACCT.CUST_ACCOUNT_ID(+) and QTE.PARTY_ID = APPR.PARTY_ID(+) and APPR.SUBJECT_ID = APP.PARTY_ID(+) and APPR.SUBJECT_TYPE(+) = 'PERSON' AND SRP.resource_id(+) = QTE.resource_id AND SRP.org_id(+) = QTE.org_id AND QTE.LAST_UPDATE_DATE >= SYSDATE-NVL((select profile_option_value from fnd_profile_option_values where level_id = 10001 and (profile_option_id, application_id) = (select profile_option_id, application_id from fnd_profile_options where profile_option_name = 'AST_MV_NUM_OF_MONTH_DATA')),3)*30

View Text - HTML Formatted

SELECT QTE.QUOTE_NAME
, QTE.QUOTE_NUMBER||'-'||QTE.QUOTE_VERSION QUOTE_NUMBER_VERSION
, QTE.CREATION_DATE
, QTE.LAST_UPDATE_DATE
, QTE.QUOTE_STATUS_ID
, STA_T.MEANING QUOTE_STATUS
, NVL(QTE.TOTAL_QUOTE_PRICE
, 0.0) QUOTE_TOTAL
, QTE.QUOTE_EXPIRATION_DATE
, ACCT.ACCOUNT_NUMBER
, QTE.QUOTE_HEADER_ID
, QTE.CUST_ACCOUNT_ID
, ACCT.PARTY_ID ACCOUNT_PARTY_ID
, QTE.CURRENCY_CODE
, APP.PARTY_NAME SOLD_TO_CONTACT_NAME
, QTE.RESOURCE_ID
, SRP.NAME RESOURCE_NAME
, QTE.PARTY_ID CUSTOMER_ID
, APP.PARTY_TYPE SOLD_TO_CONTACT_PARTY_TYPE
, QTE.ORG_ID
, FNDLANG.LANGUAGE_CODE LANG
FROM ASO_QUOTE_HEADERS_ALL QTE
, ASO_QUOTE_STATUSES_B STA_B
, ASO_QUOTE_STATUSES_TL STA_T
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES APP
, HZ_RELATIONSHIPS APPR
, JTF_RS_SALESREPS SRP
, FND_LANGUAGES FNDLANG
WHERE FNDLANG.INSTALLED_FLAG IN ('I'
, 'B')
AND QTE.QUOTE_STATUS_ID = STA_B.QUOTE_STATUS_ID
AND STA_B.QUOTE_STATUS_ID = STA_T.QUOTE_STATUS_ID
AND STA_T.LANGUAGE = FNDLANG.LANGUAGE_CODE
AND (TRUNC(SYSDATE) BETWEEN NVL(TRUNC(STA_B.EFFECTIVE_START_DATE)
, TRUNC (SYSDATE))
AND NVL(TRUNC(STA_B.EFFECTIVE_END_DATE)
, TRUNC(SYSDATE)))
AND QTE.CUST_ACCOUNT_ID = ACCT.CUST_ACCOUNT_ID(+)
AND QTE.PARTY_ID = APPR.PARTY_ID(+)
AND APPR.SUBJECT_ID = APP.PARTY_ID(+)
AND APPR.SUBJECT_TYPE(+) = 'PERSON'
AND SRP.RESOURCE_ID(+) = QTE.RESOURCE_ID
AND SRP.ORG_ID(+) = QTE.ORG_ID
AND QTE.LAST_UPDATE_DATE >= SYSDATE-NVL((SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
WHERE LEVEL_ID = 10001
AND (PROFILE_OPTION_ID
, APPLICATION_ID) = (SELECT PROFILE_OPTION_ID
, APPLICATION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME = 'AST_MV_NUM_OF_MONTH_DATA'))
, 3)*30