Sales Leads Personalized Views Error When Sorting on Created Date

(Doc ID 808083.1)

Last updated on SEPTEMBER 07, 2016

Applies to:

Oracle Sales - Version 12.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 28-Aug-2016***

Symptoms

Personalized views for leads error if creation date is used in the order by criteria

Navigation:  Responsibility: Sales User > Sales Dashboard

In the Leads Region use the Personalize button to Personalize Leads Views.

If the sort settings include the 'Created date' column the following error will occur.


Error Page
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during
statement preparation. Statement: SELECT * FROM (SELECT LeadEO.sales_lead_id ,
LeadEO.lead_number , SUBSTRB(LeadEO.description, 1,240) as Description, LeadEO.customer_id ,
LeadEO.primary_contact_party_id , LeadEO.currency_code , LeadEO.creation_date as CreationDate,
LeadEO.budget_amount , LeadEO.last_update_date , CEIL(SYSDATE - LeadEO.creation_date) as Age,
DECODE (LeadEO.status_code, 'CONVERTED_TO_OPPORTUNITY' , 'ASNCnvToOpptyDsbld',
'ASNCnvToOpptyEnbld') as ASNLeadLstCnvToOppty, null as CurrencyName, null as ResourceName, null
as Methodology, null as Stage, null as SalesChannel, null as ResponseChannel, null as
CloseReason, null as LeadCreatedBy, null as LeadUpdatedBy, aslrt.meaning as RankName,
ast.meaning as LeadStatus, null as SourceName, null as ContactRole, hpobj.party_name as
CustomerName, cont.party_name as ContactName, cont.party_id as PersonId, hr.relationship_id as
RelationshipId, null as JobTitle, null as PhoneCountryCode, null as PhoneAreaCode, null as
PhoneNumber, null as PhoneExtension, to_number(null) as ContactPointId, null as FormattedPhone,
ht.standard_time_short_code as ContactLocalTime, null as ContactLocalTimeZone, null as City,
null as Province, null as State, null as Country, null as PostalCode, to_number(null) as
ContactLocationId,null as Address, null as EmailAddress, to_number(null) as
CustomerLocationId, null as CustomerAddress, null as CustomerCity, null as CustomerState, null
as CustomerProvince, null as CustomerCountry, null as CustomerPostalCode, null as
SourceSystem, null as PrimaryPhoneCountryCode, null as PrimaryPhoneAreaCode, null as
PrimaryPhoneNumber, null as PrimaryPhoneExtension, to_number(null) as CustomerContactPointId,
null as CustomerFormattedPhone, null as CustomerEmail, null as SourceCode,
ht.standard_time_short_code as ContactLocalTimeZoneCd FROM as_sales_leads LeadEO, as_statuses_tl
ast , hz_parties hpobj , as_sales_lead_ranks_tl aslrt , hz_parties cont, hz_relationships hr,
hz_contact_points hcplt, hz_timezones ht WHERE leadEO.sales_lead_id IN ( SELECT distinct
aaa.sales_lead_id FROM as_accesses_all aaa WHERE aaa.sales_lead_id IS NOT NULL AND
aaa.lead_id IS NULL AND aaa.open_flag = 'Y' AND aaa.salesforce_id = :0) AND
LeadEO.status_code = ast.status_code AND ast.language = USERENV('LANG') AND LeadEO.customer_id =
hpobj.party_id AND LeadEO.lead_rank_id = aslrt.rank_id (+) AND aslrt.language (+) =
USERENV('LANG') AND LeadEO.primary_contact_party_id = hr.party_id(+) AND hr.subject_id =
cont.party_id (+) AND hr.subject_table_name (+) = 'HZ_PARTIES' AND hr.object_id(+) =
LeadEO.customer_id AND hr.object_table_name (+) = 'HZ_PARTIES' AND
LeadEO.primary_contact_party_id = hcplt.owner_table_id(+) AND hcplt.owner_table_name (+) =
'HZ_PARTIES' AND hcplt.primary_flag (+) = 'Y' AND hcplt.contact_point_type (+) = 'PHONE' AND
hcplt.timezone_id = ht.timezone_id (+) AND LeadEO.status_code = :1 AND
TRUNC(LeadEO.creation_date) <= :2 AND TRUNC(LeadEO.creation_date) >= :3 AND CEIL(SYSDATE -
LeadEO.creation_date) BETWEEN :4 AND :5 ) QRSLT ORDER BY CREATION_DATE DESC
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:896)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:870)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:993)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at
oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:7
20)
at oracle.apps.asn.lead.webui.ASNLeadLstCO.executeLeadsQuery(ASNLeadLstCO.java:536)
at oracle.apps.asn.lead.webui.ASNLeadLstCO.processFormRequest(ASNLeadLstCO.java:180)
..
..
## Detail 0 ##
java.sql.SQLException: ORA-00904: "CREATION_DATE": invalid identifier

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms