ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

We use below script to create new supplier site.

-- Create Supplier

ap_vendor_pub_pkg.create_vendor_site

(p_api_version        => 1.0,

 p_init_msg_list      => fnd_api.g_true,

 p_commit             => fnd_api.g_false,

 p_validation_level   => fnd_api.g_valid_level_full,

 p_vendor_site_rec    => l_v_site_rec_type,

 x_vendor_site_id     => l_vendor_site_id,

 x_party_site_id      => l_party_site_id,

 x_location_id        => l_location_id,

 x_return_status      => l_return_status,--OUT 

 x_msg_count          => l_msg_count,    --OUT

 x_msg_data           => l_msg_data      --OUT);

We got below error.

ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

Developer dropped and recreated SYNONYM AP_SYSTEM_PARAMETERS as suggested by some of the Oracle forums.

After this we were able to create the supplier sites but AP forms stopped working because AP_SYSTEM_PARAMETERS started working as a normal synonym instead of Global Temporary Table. 

DO NOT drop AP_SYSTEM_PARAMETERS because it will drop MOAC security record from v$sqlarea ,v$vpd_policy tables.

Use below query to check security details which helps to work this synonym as a Global Temporary Table. 

 select substr(sql_text,1,200) sql_text,
 predicate,
 policy,
 object_name
 from v$sqlarea ,v$vpd_policy
 where hash_value = sql_hash
 and object_name = 'AP_SYSTEM_PARAMETERS';

Query Output

------------

SQL_TEXT= Select /*bug10155938 sql*/ org_id from ap_system_parameters
PREDICATE   = org_id = sys_context('multi_org2','current_org_id')
POLICY      = ORG_SEC
OBJECT_NAME = AP_SYSTEM_PARAMETERS

There must be some bug in the Oracle program because of which we get below error.
ORA-01403: no data found in Package AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site

If you drop AP_SYSTEM_PARAMETERS by mistake then you need to run below scripts to get it back.

Get below script from Oracle and Run the below scripts as apps user

SQL>@apmoconv.sql MOSYN AP APPS
SQL>@apmoconv.sql MOVPD AP APPS

NOTE: The first statement will recreate the synonym and the second statement will apply the policy. The binds of the script are in the same order in which it is fed to the script.
bind#1 -> MOSYN/MOVPDbind#2 -> APbind#3 -> APPS

DO NOT Drop AP_SYSTEM_PARAMETERS.