910: l_org_unit_addr c_org_unit_addr%ROWTYPE;
911:
912: -- Cursor to obtain the primary email address of the primary address of an Orgn Unit
913: CURSOR c_org_unit_email(cp_id_flag hz_party_sites.IDENTIFYING_ADDRESS_FLAG%TYPE,
914: cp_owner_tbl hz_contact_points.OWNER_TABLE_NAME%TYPE,
915: cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
916: cp_prim_flag hz_contact_points.PRIMARY_FLAG%TYPE,
917: cp_party_id HZ_PARTY_SITES.PARTY_ID%TYPE) IS
918: SELECT
911:
912: -- Cursor to obtain the primary email address of the primary address of an Orgn Unit
913: CURSOR c_org_unit_email(cp_id_flag hz_party_sites.IDENTIFYING_ADDRESS_FLAG%TYPE,
914: cp_owner_tbl hz_contact_points.OWNER_TABLE_NAME%TYPE,
915: cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
916: cp_prim_flag hz_contact_points.PRIMARY_FLAG%TYPE,
917: cp_party_id HZ_PARTY_SITES.PARTY_ID%TYPE) IS
918: SELECT
919: CPE.EMAIL_ADDRESS
912: -- Cursor to obtain the primary email address of the primary address of an Orgn Unit
913: CURSOR c_org_unit_email(cp_id_flag hz_party_sites.IDENTIFYING_ADDRESS_FLAG%TYPE,
914: cp_owner_tbl hz_contact_points.OWNER_TABLE_NAME%TYPE,
915: cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
916: cp_prim_flag hz_contact_points.PRIMARY_FLAG%TYPE,
917: cp_party_id HZ_PARTY_SITES.PARTY_ID%TYPE) IS
918: SELECT
919: CPE.EMAIL_ADDRESS
920: FROM
918: SELECT
919: CPE.EMAIL_ADDRESS
920: FROM
921: HZ_PARTY_SITES PS,
922: HZ_CONTACT_POINTS CPE
923: WHERE
924: PS.PARTY_ID = cp_party_id AND
925: PS.IDENTIFYING_ADDRESS_FLAG = cp_id_flag AND
926: CPE.OWNER_TABLE_NAME = cp_owner_tbl AND
931: l_org_unit_email c_org_unit_email%ROWTYPE;
932:
933: -- Cursor to obtain the Phone/Fax of an Organization Unit
934: CURSOR c_org_unit_phone (cp_id_flag hz_party_sites.IDENTIFYING_ADDRESS_FLAG%TYPE,
935: cp_owner_tbl hz_contact_points.OWNER_TABLE_NAME%TYPE,
936: cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
937: cp_status hz_contact_points.STATUS%TYPE,
938: cp_party_id hz_party_sites.PARTY_ID%TYPE,
939: cp_line_type1 hz_contact_points.PHONE_LINE_TYPE%TYPE,
932:
933: -- Cursor to obtain the Phone/Fax of an Organization Unit
934: CURSOR c_org_unit_phone (cp_id_flag hz_party_sites.IDENTIFYING_ADDRESS_FLAG%TYPE,
935: cp_owner_tbl hz_contact_points.OWNER_TABLE_NAME%TYPE,
936: cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
937: cp_status hz_contact_points.STATUS%TYPE,
938: cp_party_id hz_party_sites.PARTY_ID%TYPE,
939: cp_line_type1 hz_contact_points.PHONE_LINE_TYPE%TYPE,
940: cp_line_type2 hz_contact_points.PHONE_LINE_TYPE%TYPE) IS
933: -- Cursor to obtain the Phone/Fax of an Organization Unit
934: CURSOR c_org_unit_phone (cp_id_flag hz_party_sites.IDENTIFYING_ADDRESS_FLAG%TYPE,
935: cp_owner_tbl hz_contact_points.OWNER_TABLE_NAME%TYPE,
936: cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
937: cp_status hz_contact_points.STATUS%TYPE,
938: cp_party_id hz_party_sites.PARTY_ID%TYPE,
939: cp_line_type1 hz_contact_points.PHONE_LINE_TYPE%TYPE,
940: cp_line_type2 hz_contact_points.PHONE_LINE_TYPE%TYPE) IS
941: SELECT
935: cp_owner_tbl hz_contact_points.OWNER_TABLE_NAME%TYPE,
936: cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
937: cp_status hz_contact_points.STATUS%TYPE,
938: cp_party_id hz_party_sites.PARTY_ID%TYPE,
939: cp_line_type1 hz_contact_points.PHONE_LINE_TYPE%TYPE,
940: cp_line_type2 hz_contact_points.PHONE_LINE_TYPE%TYPE) IS
941: SELECT
942: NVL (CPP.PHONE_AREA_CODE,'*') PHONE_AREA_CODE,
943: NVL (CPP.PHONE_COUNTRY_CODE,'*') PHONE_COUNTRY_CODE,
936: cp_cnt_type hz_contact_points.CONTACT_POINT_TYPE%TYPE,
937: cp_status hz_contact_points.STATUS%TYPE,
938: cp_party_id hz_party_sites.PARTY_ID%TYPE,
939: cp_line_type1 hz_contact_points.PHONE_LINE_TYPE%TYPE,
940: cp_line_type2 hz_contact_points.PHONE_LINE_TYPE%TYPE) IS
941: SELECT
942: NVL (CPP.PHONE_AREA_CODE,'*') PHONE_AREA_CODE,
943: NVL (CPP.PHONE_COUNTRY_CODE,'*') PHONE_COUNTRY_CODE,
944: NVL (CPP.PHONE_NUMBER,'*') PHONE_NUMBER,
945: NVL (CPP.PHONE_EXTENSION,'*') PHONE_EXTENSION,
946: CPP.PHONE_LINE_TYPE
947: FROM
948: HZ_PARTY_SITES PS,
949: HZ_CONTACT_POINTS CPP
950: WHERE
951: PS.PARTY_ID = cp_party_id AND
952: PS.IDENTIFYING_ADDRESS_FLAG = cp_id_flag AND
953: CPP.OWNER_TABLE_NAME = cp_owner_tbl AND