106: ----------------------------------------------------------------------
107:
108: CURSOR c_get_mail_pref (cp_party_id NUMBER) IS
109: SELECT preference_value
110: FROM fnd_user_preferences user_prefs,
111: fnd_user users,
112: hz_parties parties
113: WHERE user_prefs.preference_name = 'MAILTYPE'
114: AND user_prefs.user_name = users.user_name
114: AND user_prefs.user_name = users.user_name
115: AND users.customer_id = parties.party_id
116: AND parties.party_id = cp_party_id;
117:
118: l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
119:
120: BEGIN
121: --retrieve mail preference for the owner
122: OPEN c_get_mail_pref(cp_party_id => p_party_id);
540: l_member_user_name FND_USER.USER_NAME%TYPE;
541: l_group_member_rel_id NUMBER;
542: --used to generate plsql document
543: l_msg_document_plsql_proc VARCHAR2(9999);
544: l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
545:
546: l_member_note VARCHAR2(2000);
547: l_admin_note VARCHAR2(2000);
548: l_temp_message VARCHAR2(2000);
1827: -- AND SYSDATE BETWEEN grp_owner.start_date AND NVL(grp_owner.end_date, SYSDATE)
1828: -- AND grp_owner.relationship_type = G_OWNER_GROUP_REL_TYPE;
1829:
1830: -- l_owner_id NUMBER;
1831: l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
1832: l_temp_message VARCHAR2(2000);
1833:
1834: BEGIN
1835:
1944:
1945: l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
1946: l_item_type VARCHAR2(30);
1947: l_item_key VARCHAR2(30);
1948: l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
1949: l_respondent HZ_PARTIES.PARTY_NAME%TYPE;
1950: l_user_name FND_USER.USER_NAME%TYPE;
1951: l_member_id NUMBER;
1952:
2027:
2028: l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
2029: l_item_type VARCHAR2(30);
2030: l_item_key VARCHAR2(30);
2031: l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2032: l_member_id NUMBER;
2033:
2034: BEGIN
2035: -- parse document_id for the ':' dividing item type name from item key value
2109: l_item_key VARCHAR2(30);
2110: l_member_id NUMBER;
2111:
2112: l_member_name HZ_PARTIES.PARTY_NAME%TYPE;
2113: l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2114: l_member_note VARCHAR2(2000);
2115: l_temp_message VARCHAR2(2000);
2116:
2117: BEGIN
2205: l_member_id NUMBER;
2206: l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
2207: l_item_type VARCHAR2(30);
2208: l_item_key VARCHAR2(30);
2209: l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2210:
2211: BEGIN
2212: -- parse document_id for the ':' dividing item type name from item key value
2213: -- document_id value will take the form
2274:
2275: l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
2276: l_item_type VARCHAR2(30);
2277: l_item_key VARCHAR2(30);
2278: l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2279: l_deletion_note VARCHAR2(999);
2280: l_temp_message VARCHAR2(2000);
2281: l_member_id NUMBER;
2282:
2359:
2360: l_group_name HZ_PARTIES.PARTY_NAME%TYPE;
2361: l_item_type VARCHAR2(30);
2362: l_item_key VARCHAR2(30);
2363: l_mail_pref FND_USER_PREFERENCES.PREFERENCE_VALUE%TYPE;
2364: l_deletion_note VARCHAR2(999);
2365: l_temp_message VARCHAR2(2000);
2366: l_member_id NUMBER;
2367: