My Oracle Support Banner

Groovy That Queries Resource View Returns Error: Attempt to set a parameter name that does not occur in the SQL: vc_temp_2 (Doc ID 2687039.1)

Last updated on JULY 10, 2020

Applies to:

Oracle Fusion CX Sales Cloud Service - Version and later
Information in this document applies to any platform.


On : version, Accounts, Contacts, Households

Can't create new record on Factory Data child object of Account standard object

Under Account we have a custom child Object.
This object has a field that if we try to set it it will return an error.

Exception in expression "" trigger "testresource" : oracle.jbo.SQLStmtException SQL error during statement preparation. Statement: SELECT /*+ FIRST_ROWS(10) */ ResourceEO.RESOURCE_PROFILE_ID, ResourceEO.PARTY_ID, PartyPEO.PARTY_NAME, PartyPEO.PARTY_NUMBER, ResourceEO.START_DATE_ACTIVE, ResourceEO.END_DATE_ACTIVE, ResourceEO.CREATED_BY_MODULE, ResourceEO.REQUEST_ID, ('INDIVIDUAL') AS RESOURCETYPE, (SELECT OrganizationUnitPEO.NAME FROM jtf_rs_group_members ResourceOrganizationMemberEO, jtf_rs_role_relations ResourceRoleAssignEO, hr_all_organization_units_f_vl OrganizationUnitPEO WHERE ResourceOrganizationMemberEO.group_member_id = ResourceRoleAssignEO.role_resource_id AND ResourceOrganizationMemberEO.resource_id = ResourceEO.party_id AND ResourceOrganizationMemberEO.group_id = OrganizationUnitPEO.organization_id AND ResourceOrganizationMemberEO.delete_flag <> 'Y' AND ResourceRoleAssignEO.role_resource_type = 'RS_GROUP_MEMBER' AND TRUNC(SYSDATE) BETWEEN ResourceRoleAssignEO.start_date_active and ResourceRoleAssignEO.end_date_active AND ResourceRoleAssignEO.delete_flag <> 'Y' AND rownum = 1) AS PRIMARYORGANIZATION, (SELECT substr(RTRIM(XMLAGG(XMLELEMENT(E,ResourceRoleEO.role_name,', ').EXTRACT('//text()'))),0,(length(RTRIM(XMLAGG(XMLELEMENT(E,ResourceRoleEO.role_name,', ').EXTRACT('//text()'))))-1)) AS ResourceRoleNames FROM jtf_rs_role_relations ResourceRoleAssignEO, jtf_rs_roles_vl ResourceRoleEO WHERE ResourceRoleAssignEO.role_id = ResourceRoleEO.role_id AND ResourceRoleAssignEO.role_resource_type = 'RS_INDIVIDUAL' AND ResourceRoleAssignEO.role_resource_id = ResourceEO.party_id AND ResourceRoleAssignEO.delete_flag <> 'Y' AND TRUNC (SYSDATE) BETWEEN ResourceRoleAssignEO.start_date_active AND ResourceRoleAssignEO.end_date_active) AS ROLES, PartyPEO.PARTY_ID AS PARTY_ID1, PartyPEO.PARTY_TYPE, ResourceEO.EMAIL_ADDRESS, PartyPEO.INTERNAL_FLAG, (SELECT DECODE(reportinghierarchypeo.manager_name,NULL, ( SELECT party_name FROM hz_parties hp WHERE hp.party_id = ( SELECT DISTINCT gm.resource_id FROM jtf_rs_group_members gm,jtf_rs_role_relations rr,jtf_rs_roles_b rs WHERE gm.group_id = jrgm.group_id AND gm.delete_flag = 'N' AND gm.group_member_id = rr.role_resource_id AND rr.delete_flag = 'N' AND rr.role_id = rs.role_id AND rs.manager_flag = 'Y' AND trunc(SYSDATE) BETWEEN rr.start_date_active AND rr.end_date_active AND gm.resource_id <> resourceeo.party_id AND ROWNUM = 1 ) ),reportinghierarchypeo.manager_name) FROM jtf_rs_group_members jrgm, jtf_rs_role_relations jrrr, jtf_rs_rep_managers_v reportinghierarchypeo WHERE jrgm.resource_id = resourceeo.party_id AND jrgm.delete_flag = 'N' AND jrgm.group_member_id = jrrr.role_resource_id AND jrrr.delete_flag = 'N' AND trunc(SYSDATE) BETWEEN jrrr.start_date_active AND jrrr.end_date_active AND resourceeo.party_id = reportinghierarchypeo.resource_id (+) AND reportinghierarchypeo.reports_to_flag (+) = 'Y' AND reportinghierarchypeo.resource_id (+) = jrgm.resource_id AND ROWNUM = 1) AS MANAGER, ('HZ_PARTIES') AS AttachmentEntityName, PartyPEO.URL, PartyPEO.USER_GUID, PartyPEO.IDEN_ADDR_PARTY_SITE_ID, (select HcmUsersPEO.username from per_users HcmUsersPEO where PartyPEO.user_guid = HcmUsersPEO.user_guid and rownum = 1) AS USERNAME, ResourceEO.CURCY_CONV_RATE_TYPE, ResourceEO.CURRENCY_CODE, ResourceEO.CORP_CURRENCY_CODE, ('A') AS SEARCH_STATUS_FLAG, ('N') AS SEARCH_DELETE_FLAG, ('RS_INDIVIDUAL') AS SEARCH_ROLE_TYPE, ResourceEO.PERSON_FIRST_NAME, ResourceEO.PERSON_LAST_NAME, PartyPEO.PERSON_LAST_NAME_PREFIX, PartyPEO.PERSON_MIDDLE_NAME, PartyPEO.PERSON_NAME_SUFFIX, PartyPEO.PERSON_PRE_NAME_ADJUNCT, PartyPEO.PERSON_PREVIOUS_LAST_NAME, PartyPEO.PERSON_SECOND_LAST_NAME, PartyPEO.STATUS, (select 'ResourceOrgRoleCode' || ':' || ResourceRolePEO.ROLE_CODE || ',' || 'ResourceOrgRoleName' || ':' || ResourceRolePEO.ROLE_NAME FROM JTF_RS_GROUP_MEMBERS ResourceOrganizationMemberPE1,JTF_RS_ROLE_RELATIONS ResourceRoleAssignPEO , JTF_RS_ROLES_VL ResourceRolePEO WHERE ResourceOrganizationMemberPE1.RESOURCE_ID =PartyPEO.PARTY_ID AND ResourceOrganizationMemberPE1.GROUP_MEMBER_ID(+) = ResourceRoleAssignPEO.ROLE_RESOURCE_ID AND ( 'RS_GROUP_MEMBER' ) = ResourceRoleAssignPEO.ROLE_RESOURCE_TYPE AND ResourceRoleAssignPEO.ROLE_ID = ResourceRolePEO.ROLE_ID(+) AND TRUNC(sysdate) BETWEEN TRUNC(ResourceRoleAssignPEO.Start_date_active) AND TRUNC(ResourceRoleAssignPEO.end_date_active ) AND ResourceOrganizationMemberPE1.DELETE_FLAG != 'Y' AND ResourceRoleAssignPEO.DELETE_FLAG != 'Y' and rownum=1) AS ResOrgRoleNameAndCode FROM JTF_RS_RESOURCE_PROFILES ResourceEO, HZ_PARTIES PartyPEO WHERE ResourceEO.PARTY_ID = PartyPEO.PARTY_ID AND ( ( ( (ResourceEO.PARTY_ID = :vc_temp_1 ) AND 1 = 1 ) ) AND ( ( ( (PartyPEO.STATUS = :BindPartyStatus ) ) ) ) ) AND ( (1=1)): Attempt to set a parameter name that does not occur in the SQL: vc_temp_2
at "" trigger "testresource" line 38

The issue can be reproduced at will with the following steps:
1. Drilldown on an Account
2. Go to the custom object subtab
3. Set the field with a different value

The issue has the following business impact:
Due to this issue, users cannot update the child records..


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.