Monday, November 26, 2012

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=<>;

Thursday, November 1, 2012

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;