Need to mask SSN in LOV of seeded form. Have used Form Personalization to do similar tasks several times.
The record group needing modification contains a bind variable for customer_id. I have created a ‘LOCAL_VARIABLE’ using ‘Forms Personalization’ and modified the SQL used to create the desired record group using the local variable. The new record group does not get created. There is an undetermined syntax error. The sql used to create the new record group is below.
Please help me resolve the syntax error or suggest another method.
First a local variable was created and then populated with, customer_id.
TRIGGER_EVENT: WHEN-NEW-ITEM_INSTANCE
TRIGGER_OBJECT: CUST.CUSTOMER_NAME_MIR
CONDITION: FND_GLOBAL.RESP_NAME NOT LIKE ‘
R; Customer Setup’
ACTION:
Property, LOCAL_VARIABLE: V_LOCAL_CUSTOMER_ID
PROPERTY_NAME: VALUE
VALUE: ${item.cust.customer_id.value}
Then the local variable needs to be used to define a record group.
(Create Record Group From Query)
RECORD_GROUP SQL:
SELECT /*+ LEADING(e) */ su.site_use_id,
su.location,
substrb(party.party_name,1,50) customer_name,
cust.account_number customer_number,
translate(party.jgzz_fiscal_code, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz !@#$%^&*()-=[]\;'',./_+{}|:"<>?`~',
'***********************************************************************************************') taxpayer_id,
translate(party.tax_reference,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz !@#$%^&*()-=[]\;'',./_+{}|:"<>?`~',
'***********************************************************************************************') tax_reference,
ARH_ADDR_PKG.FORMAT_ADDRESS(
addr.address_style, addr.address1, addr.address2, addr.address3,
addr.address4, addr.city, addr.country, addr.state, addr.province,
addr.postal_code, terr.territory_short_name )
concatenated_address,
cust.account_name account_description
FROM
hz_cust_accounts cust,
hz_cust_acct_sites site,
hz_cust_site_uses su,
hz_parties party,
hz_party_sites ps,
hz_locations addr,
fnd_territories_vl terr,
( SELECT to_number( ${item.cust.customer_id.value} ) cust_account_id
FROM dual
UNION
SELECT cust_account_id
FROM hz_cust_acct_relate_all
WHERE related_cust_account_id = ( ${item.cust.customer_id.value} ) and
status = 'A' and
bill_to_flag = 'Y') e
WHERE
su.site_use_code='BILL_TO' and
su.status = 'A' and
su.cust_acct_site_id = site.cust_acct_site_id and
site.status = 'A' and
site.cust_account_id =e.cust_account_id and
site.cust_account_id = cust.cust_account_id and
site.party_site_id = ps.party_site_id and
ps.party_id = party.party_id and
ps.location_id = addr.location_id and
addr.country = terr.territory_code
ORDER BY party.party_name, cust.account_number, su.location