VIEW: APPS.ASO_PVT_QUOTE_HEADERS_V

Source

View Text - Preformatted

SELECT ASO_QUOTES.ROWID , ASO_QUOTES.QUOTE_HEADER_ID , ASO_QUOTES.ORDER_ID , ASO_QUOTES.ORG_ID , ASO_QUOTES.LAST_UPDATE_DATE , ASO_QUOTES.LAST_UPDATED_BY , ASO_QUOTES.CREATION_DATE , ASO_QUOTES.CREATED_BY , ASO_QUOTES.LAST_UPDATE_LOGIN , ASO_QUOTES.REQUEST_ID, ASO_QUOTES.PROGRAM_APPLICATION_ID , ASO_QUOTES.PROGRAM_ID , ASO_QUOTES.PROGRAM_UPDATE_DATE , ASO_QUOTES.ORIGINAL_SYSTEM_REFERENCE , ASO_QUOTES.EMPLOYEE_PERSON_ID , ASO_QUOTES.RESOURCE_ID, null, null, DECODE(ASO_QUOTES.RESOURCE_ID, null, sales.name, sales1.name), ASO_QUOTES.PRICE_LIST_ID , I_PRICE_LISTS.NAME , ASO_QUOTES.QUOTE_STATUS_ID , ASO_STATUS_VL.STATUS_CODE , ASO_STATUS_VL.MEANING , ASO_STATUS_VL.UPDATE_ALLOWED_FLAG , ASO_STATUS_VL.AUTO_VERSION_FLAG , ASO_QUOTES.QUOTE_SOURCE_CODE , ASO_QUOTES.party_id, NULL, NULL, NULL, NULL, I_CUST_PARTIES.PARTY_ID , I_CUST_PARTIES.PARTY_NAME, I_CUST_PARTIES.PARTY_TYPE , I_ACCTS.ACCOUNT_NUMBER, ASO_QUOTES.CUST_ACCOUNT_ID, ASO_QUOTES.CUST_ACCOUNT_ID, ASO_QUOTES.ORG_CONTACT_ID , I_HEADER_CONTACT_REL.PARTY_ID, I_HEADER_CONTACT_PARTY.PARTY_NAME, ASO_QUOTES.PHONE_ID , I_PHONE.PHONE_AREA_CODE , I_PHONE.PHONE_NUMBER , I_PHONE.PHONE_AREA_CODE || DECODE(I_PHONE.PHONE_AREA_CODE , NULL, '',' ' ) || I_PHONE.PHONE_NUMBER , I_PHONE.PHONE_EXTENSION , I_PHONE.PHONE_LINE_TYPE , ASO_QUOTES.QUOTE_NAME , ASO_QUOTES.QUOTE_NUMBER , ASO_QUOTES.QUOTE_VERSION , ASO_QUOTES.QUOTE_EXPIRATION_DATE , ASO_QUOTES.QUOTE_CATEGORY_CODE , ASO_QUOTES.CURRENCY_CODE , ASO_QUOTES.EXCHANGE_RATE , ASO_QUOTES.EXCHANGE_TYPE_CODE , ASO_QUOTES.EXCHANGE_RATE_DATE , ASO_QUOTES.ORIG_MKTG_SOURCE_CODE_ID , ASO_QUOTES.MARKETING_SOURCE_CODE_ID , NULL , NULL , ASO_QUOTES.ORDERED_DATE ORDERED_DATE , ASO_QUOTES.ORDER_TYPE_ID , I_ORDER_TYPES.NAME , TAX.TAX_EXEMPT_NUMBER , TAX.TAX_EXEMPT_REASON_CODE , TAX.TAX_EXEMPT_FLAG , ASO_QUOTES.TOTAL_LIST_PRICE , ASO_QUOTES.TOTAL_ADJUSTED_AMOUNT , ASO_QUOTES.TOTAL_ADJUSTED_PERCENT , ASO_QUOTES.TOTAL_TAX , ASO_QUOTES.SURCHARGE , ASO_QUOTES.TOTAL_SHIPPING_CHARGE , ASO_QUOTES.TOTAL_QUOTE_PRICE , ASO_QUOTES.ACCOUNTING_RULE_ID , ASO_QUOTES.INVOICING_RULE_ID , ASO_QUOTES.INVOICE_TO_PARTY_SITE_ID , ASO_QUOTES.INVOICE_TO_PARTY_ID, NULL, NULL, NULL, aso_quotes.invoice_to_cust_PARTY_ID , I_ACCTS_INV_PARTY.PARTY_NAME, I_ACCTS_INV_PARTY.PARTY_TYPE, I_ACCTS_INV.account_number, aso_quotes.invoice_TO_CUST_ACCOUNT_ID, ASO_QUOTES.INVOICE_TO_PARTY_ID, I_INVOICE_REL_PARTY.PARTY_NAME, I_INVOICE_LOCATIONS.ADDRESS1 , I_INVOICE_LOCATIONS.ADDRESS2 , I_INVOICE_LOCATIONS.ADDRESS3 , I_INVOICE_LOCATIONS.ADDRESS4 , I_INVOICE_LOCATIONS.COUNTRY , I_TERRITORIES.territory_short_name, I_INVOICE_LOCATIONS.CITY , I_INVOICE_LOCATIONS.POSTAL_CODE , I_INVOICE_LOCATIONS.STATE , I_INVOICE_LOCATIONS.PROVINCE , I_INVOICE_LOCATIONS.COUNTY , ASO_QUOTES.CONTRACT_ID , ASO_QUOTES.PRICE_FROZEN_DATE , ASO_QUOTES.QUOTE_PASSWORD , ASO_QUOTES.PAYMENT_AMOUNT , ASO_QUOTES.SALES_CHANNEL_CODE , ASO_QUOTES.CONTRACT_TEMPLATE_ID, ASO_QUOTES.CONTRACT_TEMPLATE_MAJOR_VER, ASO_QUOTES.CONTRACT_REQUESTER_ID, ASO_QUOTES.CONTRACT_APPROVAL_LEVEL, ASO_QUOTES.PUBLISH_FLAG, ASO_QUOTES.RESOURCE_GRP_ID, ASO_QUOTES.SOLD_TO_PARTY_SITE_ID, ASO_QUOTES.QUOTE_TYPE, ASO_QUOTES.QUOTE_DESCRIPTION, ASO_QUOTES.MINISITE_ID, ASO_QUOTES.ATTRIBUTE_CATEGORY , ASO_QUOTES.ATTRIBUTE1 , ASO_QUOTES.ATTRIBUTE2 , ASO_QUOTES.ATTRIBUTE3 , ASO_QUOTES.ATTRIBUTE4 , ASO_QUOTES.ATTRIBUTE5 , ASO_QUOTES.ATTRIBUTE6 , ASO_QUOTES.ATTRIBUTE7 , ASO_QUOTES.ATTRIBUTE8 , ASO_QUOTES.ATTRIBUTE9 , ASO_QUOTES.ATTRIBUTE10 , ASO_QUOTES.ATTRIBUTE11 , ASO_QUOTES.ATTRIBUTE12 , ASO_QUOTES.ATTRIBUTE13 , ASO_QUOTES.ATTRIBUTE14 , ASO_QUOTES.ATTRIBUTE15 FROM hz_CONTACT_POINTS I_PHONE , ASO_I_ORDER_TYPES_V I_ORDER_TYPES, HZ_RELATIONSHIPS I_HEADER_CONTACT_REL, HZ_PARTIES I_HEADER_CONTACT_PARTY, ASO_I_PRICE_LISTS_V I_PRICE_LISTS, ASO_QUOTE_HEADERS ASO_QUOTES, ASO_QUOTE_STATUSES_VL ASO_STATUS_VL, HZ_RELATIONSHIPS I_INVOICE_REL, HZ_PARTIES I_INVOICE_REL_PARTY, HZ_PARTY_sites I_INVOICE_PARTY_SITES, HZ_LOCATIONS I_INVOICE_LOCATIONS, HZ_PARTIES I_CUST_PARTIES, HZ_CUST_ACCOUNTS I_ACCTS, HZ_CUST_ACCOUNTS I_ACCTS_INV, HZ_PARTIES I_ACCTS_INV_PARTY, JTF_RS_SALESREPS_MO_V SALES, JTF_RS_SALESREPS_MO_V SALES1, FND_TERRITORIES_VL I_TERRITORIES, (SELECT TAX_DETAIL_ID, QUOTE_HEADER_ID, QUOTE_LINE_ID, QUOTE_SHIPMENT_ID, TAX_EXEMPT_FLAG, TAX_EXEMPT_NUMBER, TAX_EXEMPT_REASON_CODE FROM ASO_TAX_DETAILS WHERE QUOTE_LINE_ID IS NULL) TAX WHERE ASO_QUOTES.QUOTE_STATUS_ID = ASO_STATUS_VL.QUOTE_STATUS_ID(+) AND ASO_QUOTES.PRICE_LIST_ID = I_PRICE_LISTS.PRICE_LIST_ID(+) AND ASO_QUOTES.PARTY_ID = I_HEADER_CONTACT_REL.PARTY_ID(+) AND I_HEADER_CONTACT_REL.SUBJECT_ID = I_HEADER_CONTACT_PARTY.PARTY_ID(+) AND I_HEADER_CONTACT_REL.SUBJECT_TYPE(+) = 'PERSON' AND I_HEADER_CONTACT_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' and i_header_contact_rel.object_id(+) = aso_quotes.cust_party_id AND ASO_QUOTES.INVOICE_TO_PARTY_SITE_ID = I_INVOICE_PARTY_SITES.PARTY_SITE_ID (+) AND I_INVOICE_PARTY_SITES.LOCATION_ID = I_INVOICE_LOCATIONS.LOCATION_ID(+) AND I_INVOICE_LOCATIONS.country = I_TERRITORIES.territory_code(+) AND ASO_QUOTES.INVOICE_TO_PARTY_ID = I_INVOICE_REL.PARTY_ID(+) AND I_INVOICE_REL.SUBJECT_ID = I_INVOICE_REL_PARTY.PARTY_ID(+) AND I_INVOICE_REL.SUBJECT_TYPE(+) = 'PERSON' AND I_INVOICE_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' and i_invoice_rel.object_id (+) = aso_quotes.invoice_to_cust_party_id AND ASO_QUOTES.ORDER_TYPE_ID = I_ORDER_TYPES.ORDER_TYPE_ID (+) AND ASO_QUOTES.ORG_ID = SALES.ORG_ID(+) AND ASO_QUOTES.EMPLOYEE_PERSON_ID = SALES.PERSON_ID (+) AND ASO_QUOTES.ORG_ID = SALES1.ORG_ID(+) AND ASO_QUOTES.RESOURCE_ID = SALES1.RESOURCE_ID (+) AND ASO_QUOTES.CUST_ACCOUNT_ID = I_ACCTS.CUST_ACCOUNT_ID(+) AND ASO_QUOTES.CUST_PARTY_ID = I_CUST_PARTIES.PARTY_ID AND ASO_QUOTES.INVOICE_TO_CUST_ACCOUNT_ID = I_ACCTS_INV.CUST_ACCOUNT_ID(+) AND ASO_QUOTES.INVOICE_TO_CUST_PARTY_ID = I_ACCTS_INV_PARTY.PARTY_ID(+) AND aso_quotes.phone_id = i_phone.contact_point_Id(+) AND ASO_QUOTES.QUOTE_HEADER_ID = TAX.QUOTE_HEADER_ID(+) order by aso_quotes.quote_number,aso_quotes.quote_version desc

View Text - HTML Formatted

SELECT ASO_QUOTES.ROWID
, ASO_QUOTES.QUOTE_HEADER_ID
, ASO_QUOTES.ORDER_ID
, ASO_QUOTES.ORG_ID
, ASO_QUOTES.LAST_UPDATE_DATE
, ASO_QUOTES.LAST_UPDATED_BY
, ASO_QUOTES.CREATION_DATE
, ASO_QUOTES.CREATED_BY
, ASO_QUOTES.LAST_UPDATE_LOGIN
, ASO_QUOTES.REQUEST_ID
, ASO_QUOTES.PROGRAM_APPLICATION_ID
, ASO_QUOTES.PROGRAM_ID
, ASO_QUOTES.PROGRAM_UPDATE_DATE
, ASO_QUOTES.ORIGINAL_SYSTEM_REFERENCE
, ASO_QUOTES.EMPLOYEE_PERSON_ID
, ASO_QUOTES.RESOURCE_ID
, NULL
, NULL
, DECODE(ASO_QUOTES.RESOURCE_ID
, NULL
, SALES.NAME
, SALES1.NAME)
, ASO_QUOTES.PRICE_LIST_ID
, I_PRICE_LISTS.NAME
, ASO_QUOTES.QUOTE_STATUS_ID
, ASO_STATUS_VL.STATUS_CODE
, ASO_STATUS_VL.MEANING
, ASO_STATUS_VL.UPDATE_ALLOWED_FLAG
, ASO_STATUS_VL.AUTO_VERSION_FLAG
, ASO_QUOTES.QUOTE_SOURCE_CODE
, ASO_QUOTES.PARTY_ID
, NULL
, NULL
, NULL
, NULL
, I_CUST_PARTIES.PARTY_ID
, I_CUST_PARTIES.PARTY_NAME
, I_CUST_PARTIES.PARTY_TYPE
, I_ACCTS.ACCOUNT_NUMBER
, ASO_QUOTES.CUST_ACCOUNT_ID
, ASO_QUOTES.CUST_ACCOUNT_ID
, ASO_QUOTES.ORG_CONTACT_ID
, I_HEADER_CONTACT_REL.PARTY_ID
, I_HEADER_CONTACT_PARTY.PARTY_NAME
, ASO_QUOTES.PHONE_ID
, I_PHONE.PHONE_AREA_CODE
, I_PHONE.PHONE_NUMBER
, I_PHONE.PHONE_AREA_CODE || DECODE(I_PHONE.PHONE_AREA_CODE
, NULL
, ''
, ' ' ) || I_PHONE.PHONE_NUMBER
, I_PHONE.PHONE_EXTENSION
, I_PHONE.PHONE_LINE_TYPE
, ASO_QUOTES.QUOTE_NAME
, ASO_QUOTES.QUOTE_NUMBER
, ASO_QUOTES.QUOTE_VERSION
, ASO_QUOTES.QUOTE_EXPIRATION_DATE
, ASO_QUOTES.QUOTE_CATEGORY_CODE
, ASO_QUOTES.CURRENCY_CODE
, ASO_QUOTES.EXCHANGE_RATE
, ASO_QUOTES.EXCHANGE_TYPE_CODE
, ASO_QUOTES.EXCHANGE_RATE_DATE
, ASO_QUOTES.ORIG_MKTG_SOURCE_CODE_ID
, ASO_QUOTES.MARKETING_SOURCE_CODE_ID
, NULL
, NULL
, ASO_QUOTES.ORDERED_DATE ORDERED_DATE
, ASO_QUOTES.ORDER_TYPE_ID
, I_ORDER_TYPES.NAME
, TAX.TAX_EXEMPT_NUMBER
, TAX.TAX_EXEMPT_REASON_CODE
, TAX.TAX_EXEMPT_FLAG
, ASO_QUOTES.TOTAL_LIST_PRICE
, ASO_QUOTES.TOTAL_ADJUSTED_AMOUNT
, ASO_QUOTES.TOTAL_ADJUSTED_PERCENT
, ASO_QUOTES.TOTAL_TAX
, ASO_QUOTES.SURCHARGE
, ASO_QUOTES.TOTAL_SHIPPING_CHARGE
, ASO_QUOTES.TOTAL_QUOTE_PRICE
, ASO_QUOTES.ACCOUNTING_RULE_ID
, ASO_QUOTES.INVOICING_RULE_ID
, ASO_QUOTES.INVOICE_TO_PARTY_SITE_ID
, ASO_QUOTES.INVOICE_TO_PARTY_ID
, NULL
, NULL
, NULL
, ASO_QUOTES.INVOICE_TO_CUST_PARTY_ID
, I_ACCTS_INV_PARTY.PARTY_NAME
, I_ACCTS_INV_PARTY.PARTY_TYPE
, I_ACCTS_INV.ACCOUNT_NUMBER
, ASO_QUOTES.INVOICE_TO_CUST_ACCOUNT_ID
, ASO_QUOTES.INVOICE_TO_PARTY_ID
, I_INVOICE_REL_PARTY.PARTY_NAME
, I_INVOICE_LOCATIONS.ADDRESS1
, I_INVOICE_LOCATIONS.ADDRESS2
, I_INVOICE_LOCATIONS.ADDRESS3
, I_INVOICE_LOCATIONS.ADDRESS4
, I_INVOICE_LOCATIONS.COUNTRY
, I_TERRITORIES.TERRITORY_SHORT_NAME
, I_INVOICE_LOCATIONS.CITY
, I_INVOICE_LOCATIONS.POSTAL_CODE
, I_INVOICE_LOCATIONS.STATE
, I_INVOICE_LOCATIONS.PROVINCE
, I_INVOICE_LOCATIONS.COUNTY
, ASO_QUOTES.CONTRACT_ID
, ASO_QUOTES.PRICE_FROZEN_DATE
, ASO_QUOTES.QUOTE_PASSWORD
, ASO_QUOTES.PAYMENT_AMOUNT
, ASO_QUOTES.SALES_CHANNEL_CODE
, ASO_QUOTES.CONTRACT_TEMPLATE_ID
, ASO_QUOTES.CONTRACT_TEMPLATE_MAJOR_VER
, ASO_QUOTES.CONTRACT_REQUESTER_ID
, ASO_QUOTES.CONTRACT_APPROVAL_LEVEL
, ASO_QUOTES.PUBLISH_FLAG
, ASO_QUOTES.RESOURCE_GRP_ID
, ASO_QUOTES.SOLD_TO_PARTY_SITE_ID
, ASO_QUOTES.QUOTE_TYPE
, ASO_QUOTES.QUOTE_DESCRIPTION
, ASO_QUOTES.MINISITE_ID
, ASO_QUOTES.ATTRIBUTE_CATEGORY
, ASO_QUOTES.ATTRIBUTE1
, ASO_QUOTES.ATTRIBUTE2
, ASO_QUOTES.ATTRIBUTE3
, ASO_QUOTES.ATTRIBUTE4
, ASO_QUOTES.ATTRIBUTE5
, ASO_QUOTES.ATTRIBUTE6
, ASO_QUOTES.ATTRIBUTE7
, ASO_QUOTES.ATTRIBUTE8
, ASO_QUOTES.ATTRIBUTE9
, ASO_QUOTES.ATTRIBUTE10
, ASO_QUOTES.ATTRIBUTE11
, ASO_QUOTES.ATTRIBUTE12
, ASO_QUOTES.ATTRIBUTE13
, ASO_QUOTES.ATTRIBUTE14
, ASO_QUOTES.ATTRIBUTE15
FROM HZ_CONTACT_POINTS I_PHONE
, ASO_I_ORDER_TYPES_V I_ORDER_TYPES
, HZ_RELATIONSHIPS I_HEADER_CONTACT_REL
, HZ_PARTIES I_HEADER_CONTACT_PARTY
, ASO_I_PRICE_LISTS_V I_PRICE_LISTS
, ASO_QUOTE_HEADERS ASO_QUOTES
, ASO_QUOTE_STATUSES_VL ASO_STATUS_VL
, HZ_RELATIONSHIPS I_INVOICE_REL
, HZ_PARTIES I_INVOICE_REL_PARTY
, HZ_PARTY_SITES I_INVOICE_PARTY_SITES
, HZ_LOCATIONS I_INVOICE_LOCATIONS
, HZ_PARTIES I_CUST_PARTIES
, HZ_CUST_ACCOUNTS I_ACCTS
, HZ_CUST_ACCOUNTS I_ACCTS_INV
, HZ_PARTIES I_ACCTS_INV_PARTY
, JTF_RS_SALESREPS_MO_V SALES
, JTF_RS_SALESREPS_MO_V SALES1
, FND_TERRITORIES_VL I_TERRITORIES
, (SELECT TAX_DETAIL_ID
, QUOTE_HEADER_ID
, QUOTE_LINE_ID
, QUOTE_SHIPMENT_ID
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
FROM ASO_TAX_DETAILS
WHERE QUOTE_LINE_ID IS NULL) TAX
WHERE ASO_QUOTES.QUOTE_STATUS_ID = ASO_STATUS_VL.QUOTE_STATUS_ID(+)
AND ASO_QUOTES.PRICE_LIST_ID = I_PRICE_LISTS.PRICE_LIST_ID(+)
AND ASO_QUOTES.PARTY_ID = I_HEADER_CONTACT_REL.PARTY_ID(+)
AND I_HEADER_CONTACT_REL.SUBJECT_ID = I_HEADER_CONTACT_PARTY.PARTY_ID(+)
AND I_HEADER_CONTACT_REL.SUBJECT_TYPE(+) = 'PERSON'
AND I_HEADER_CONTACT_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND I_HEADER_CONTACT_REL.OBJECT_ID(+) = ASO_QUOTES.CUST_PARTY_ID
AND ASO_QUOTES.INVOICE_TO_PARTY_SITE_ID = I_INVOICE_PARTY_SITES.PARTY_SITE_ID (+)
AND I_INVOICE_PARTY_SITES.LOCATION_ID = I_INVOICE_LOCATIONS.LOCATION_ID(+)
AND I_INVOICE_LOCATIONS.COUNTRY = I_TERRITORIES.TERRITORY_CODE(+)
AND ASO_QUOTES.INVOICE_TO_PARTY_ID = I_INVOICE_REL.PARTY_ID(+)
AND I_INVOICE_REL.SUBJECT_ID = I_INVOICE_REL_PARTY.PARTY_ID(+)
AND I_INVOICE_REL.SUBJECT_TYPE(+) = 'PERSON'
AND I_INVOICE_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND I_INVOICE_REL.OBJECT_ID (+) = ASO_QUOTES.INVOICE_TO_CUST_PARTY_ID
AND ASO_QUOTES.ORDER_TYPE_ID = I_ORDER_TYPES.ORDER_TYPE_ID (+)
AND ASO_QUOTES.ORG_ID = SALES.ORG_ID(+)
AND ASO_QUOTES.EMPLOYEE_PERSON_ID = SALES.PERSON_ID (+)
AND ASO_QUOTES.ORG_ID = SALES1.ORG_ID(+)
AND ASO_QUOTES.RESOURCE_ID = SALES1.RESOURCE_ID (+)
AND ASO_QUOTES.CUST_ACCOUNT_ID = I_ACCTS.CUST_ACCOUNT_ID(+)
AND ASO_QUOTES.CUST_PARTY_ID = I_CUST_PARTIES.PARTY_ID
AND ASO_QUOTES.INVOICE_TO_CUST_ACCOUNT_ID = I_ACCTS_INV.CUST_ACCOUNT_ID(+)
AND ASO_QUOTES.INVOICE_TO_CUST_PARTY_ID = I_ACCTS_INV_PARTY.PARTY_ID(+)
AND ASO_QUOTES.PHONE_ID = I_PHONE.CONTACT_POINT_ID(+)
AND ASO_QUOTES.QUOTE_HEADER_ID = TAX.QUOTE_HEADER_ID(+) ORDER BY ASO_QUOTES.QUOTE_NUMBER
, ASO_QUOTES.QUOTE_VERSION DESC