standard oaf pages in the server

1) class files at $JAVA_TOP


2) pages in mds directory.

ex: /oracle/apps/ar/custstd/cust/CustPG.xml will be found at $AR_TOP/mds/custstd/cust/custpg.xml.

Customer and its bank relation

select hp.party_name customer_name,hp.party_name,


ieb.bank_name,ieba.bank_account_num,ieba.branch_id

--,ieba.*

from hz_parties hp,

IBY_ACCOUNT_OWNERS iao,

IBY_EXT_BANK_ACCOUNTS ieba,

IBY_EXT_BANKS_V ieb

--, hz_parties branch

where hp.party_id =22052

and iao.account_owner_party_id = hp.party_id --party_id of customer

and iao.ext_bank_account_id = ieba.ext_bank_account_id

and ieb.bank_party_id = ieba.bank_id;

Query To Fetch Customer email address

SELECT hp.person_first_name



' '



hp.person_last_name

, hoc.contact_number

, hp1.email_address

FROM hz_cust_account_roles hcar

, hz_parties hp

, hz_relationships hr

, hz_org_contacts hoc

, hz_parties hp1

, hz_cust_accounts hca

WHERE hcar.party_id = hr.party_id

AND hcar.role_type = 'CONTACT'

AND hoc.party_relationship_id = hr.relationship_id

AND hr.subject_id = hp.party_id

AND hr.party_id = hp1.party_id

AND hr.subject_table_name = 'HZ_PARTIES'

AND hr.object_table_name = 'HZ_PARTIES'

AND hcar.cust_account_id = hca.cust_account_id

AND hca.party_id = hr.object_id

-- AND hcar.cust_account_role_id = v_ship_contact_id;

and hca.account_number=<>;

Script to list All OAF personalizations in the system

SELECT PATH.PATH_DOCID PERZ_DOC_ID,


jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) PERZ_DOC_PATH

FROM JDR_PATHS PATH

WHERE PATH.PATH_DOCID IN

(SELECT DISTINCT COMP_DOCID FROM JDR_COMPONENTS

WHERE COMP_SEQ = 0 AND COMP_ELEMENT = 'customization'

AND COMP_ID IS NULL)

ORDER BY PERZ_DOC_PATH;

API for adding responsibility to user

DECLARE
CURSOR c1
IS
select responsibility_id, application_id
from fnd_responsibility_vl a where
a.responsibility_name in
(and not exists (
select 1
from FND_USER_RESP_GROUPS_DIRECT
where user_id=
and responsibility_id=a.responsibility_id);
l_number NUMBER := 0;
BEGIN
FOR c1_rec IN c1
LOOP
BEGIN

SELECT 1

INTO l_number

FROM FND_USER_RESP_GROUPS_DIRECT a

WHERE a.responsibility_id = c1_rec.responsibility_id

AND a.user_id = 7081;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

l_number := 2;

WHEN OTHERS

THEN

l_number := 0;

END;



IF l_number = 2

THEN

DBMS_OUTPUT.

put_line (

'resp id --'



c1_rec.responsibility_id



'---Application id--> '



c1_rec.application_id);

fnd_user_resp_groups_api.

Insert_Assignment (

user_id => 7081,

responsibility_id => c1_rec.responsibility_id,

responsibility_application_id => c1_rec.application_id,

start_date => SYSDATE,

end_date => NULL,

description => NULL);

END IF;

END LOOP;



COMMIT;

END;

Supplier Conversion

http://prasanthapps.blogspot.co.uk/2011/04/supplier-conversion-in-r12.html

java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: Variable

There should not be any Bind variables defined in View Object if it is not used in SQL Query.

To remove the Bind Variables from the View Object.

1.Go to Edit view Object.-->Bind Variables
2.Select any variable defined and select remove button.