[Home] [Help]
1048: l_booking_status_type_id ota_booking_status_types.booking_status_type_id%TYPE;
1049: l_booking_priority ota_delegate_bookings.booking_priority%TYPE := NULL;
1050: l_delegate_contact_phone varchar2(80);
1051: l_delegate_contact_fax varchar2(80);
1052: l_delegate_contact_email HZ_PARTIES.email_address%TYPE; ---changed
1053: l_contact_address_id HZ_CUST_ACCT_SITES.cust_acct_site_id%TYPE := NULL; --changed
1054: l_correspondent varchar2(1) := 'C';
1055: l_student_address_id varchar2(1) := NULL;
1056: l_control_contact_address_id ota_customer_contacts_v.address_id%TYPE := NULL;
1068: fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
1069: --Third party info --
1070: l_third_contact_phone varchar2(80);
1071: l_third_contact_fax varchar2(80);
1072: l_third_contact_email HZ_PARTIES.email_address%TYPE; --changed
1073: l_third_address_id HZ_CUST_ACCT_SITES.cust_acct_site_id%TYPE := NULL; --changed
1074: l_control_third_address_id ota_customer_contacts_v.address_id%TYPE := NULL;
1075: l_third_contact_id number;
1076:
1092: select con.phone_country_code||' '|| con.phone_area_code||' '||decode(con.contact_point_type,'TLX',con.telex_number,con.phone_number) from hz_contact_points con, hz_cust_account_roles acct_role
1093: where con.owner_table_id=acct_role.party_id
1094: and con.Primary_Flag='Y'
1095: and acct_role.cust_account_role_id=l_stud_cont
1096: And con.owner_table_name = 'HZ_PARTIES'
1097: And con.contact_point_type not in ('EDI', 'EMAIL', 'WEB');
1098:
1099: --
1100: -- Bug 2599144 Country code added and a space added in between numbers ---
1104: phone_number
1105: From HZ_CONTACT_POINTS con,
1106: HZ_CUST_ACCOUNT_ROLES car
1107: where car.party_id = con.owner_table_id
1108: and con.owner_table_name = 'HZ_PARTIES'
1109: and con.contact_point_type not in ('EDI','EMAIL','WEB')
1110: and car.cust_account_role_id = l_stud_cont
1111: and nvl(con.phone_line_type,con.contact_point_type) = 'FAX';
1112: --
1112: --
1113: Cursor get_email IS
1114: Select rel_party.email_address
1115: From HZ_CUST_ACCOUNT_ROLES acct_role,
1116: HZ_PARTIES rel_party,
1117: HZ_RELATIONSHIPS rel,
1118: HZ_CUST_ACCOUNTS role_acct
1119: where acct_role.party_id = rel.party_id
1120: and acct_role.role_type = 'CONTACT'
1118: HZ_CUST_ACCOUNTS role_acct
1119: where acct_role.party_id = rel.party_id
1120: and acct_role.role_type = 'CONTACT'
1121: and rel.party_id = rel_party.party_id
1122: and rel.subject_table_name = 'HZ_PARTIES'
1123: and rel.object_table_name = 'HZ_PARTIES'
1124: and acct_role.cust_account_id = role_acct.cust_account_id
1125: and role_acct.party_id = rel.object_id
1126: and acct_role.cust_account_role_id = l_stud_cont;
1119: where acct_role.party_id = rel.party_id
1120: and acct_role.role_type = 'CONTACT'
1121: and rel.party_id = rel_party.party_id
1122: and rel.subject_table_name = 'HZ_PARTIES'
1123: and rel.object_table_name = 'HZ_PARTIES'
1124: and acct_role.cust_account_id = role_acct.cust_account_id
1125: and role_acct.party_id = rel.object_id
1126: and acct_role.cust_account_role_id = l_stud_cont;
1127: --