CURSOR    c_best_contact(a_CUG IN VARCHAR2) IS
        SELECT    ac_contact_key,
                ac_title,
                ac_forename,
                ac_surname,
                ac_full_name,
                ac_kdm,
                assk_kdm_flg,
                DECODE(
                        assk_kdm_flg,
                        'Key Decision Maker', '01',
                        'Operational KDM', '02',
                        'Strategic KDM', '03',
                        'Decision Maker', '04',
                        'Influencer', '05',
                        'Non-Decision Maker', '06',
                        '09'
                        ) kdm_order
        FROM    ACE2_CONTACT,
                ACE2_SMERF_SVEN_KDM
        WHERE    ac_customer_group_key=a_CUG
        AND      assk_conk = ac_contact_key
        AND      NVL(ac_deleted_flg,'A') = 'A'
        AND         ac_kdm IS NOT NULL  /* added for avoiding additional records from ACE2_contact table dt: 17/09/07*/
        ORDER BY
                  kdm_order ;

 FOR k IN c_best_contact(CUGTable(i).cugkey)
                            LOOP
                                  lv_RecordExist := 'TRUE' ;
                               BEGIN
                                       IF lv_Best_Contact_Found = 'FALSE' THEN
                                        IF p_output IN ('CM', 'EM', 'OM') THEN
                                            BEGIN
                                                SELECT    actc_telephone_number,
                                                        actc_tps_consent_value,
                                                        actc_ctps_consent_value,
                                                        NVL(actc_dnc_consent_value, 'C')
                                                INTO    lv_telephone_number,
                                                        lv_tps_consent_value,
                                                        lv_ctps_consent_value,
                                                        lv_telephone_consent
                                                FROM    ACE2_CONTACT_TELEPHONE_CONSENT,
                                                        ACE2_CONTACT
                                                WHERE    actc_contact_key = k.ac_contact_key
                                                AND      ac_contact_key = actc_contact_key
                                                AND      NVL(actc_deleted_flg, 'N') = 'N'
                                                AND      NVL(actc_primary_flg, 'N') = 'Y'
                                                AND      NVL(actc_dnc_consent_value, 'C') = 'C'
                                                AND     ((NVL(ac_full_name, ' ') <> ' ') OR
                                                         (LENGTH(NVL(actc_telephone_number,' ')) > 8))
                                AND       ac_kdm IS NOT NULL /* added for avoiding additional records from ACE2_contact table dt: 17/09/07*/
                                                AND ROWNUM = 1 /*to be removed later added temporarily for resolving the issuewith ACE2*/ ;
                                                   IF INSTR(lv_supp,'1', 1) <> 0 THEN   -- Check for TPS
                                                    IF (NVL(lv_tps_consent_value, 'C') = 'C' AND
                                                        NVL(lv_ctps_consent_value, 'C') = 'C') THEN
                                                        lv_tps_consent := 'C' ;
                                                    ELSE
                                                        lv_tps_consent := 'D' ;
                                                    END IF ;
                                                ELSE
                                                    lv_tps_consent := 'N' ;
                                                END IF ;
                                            EXCEPTION
                                                     WHEN NO_DATA_FOUND THEN
                                                           lv_telephone_consent := 'D' ;
                                                          lv_tps_consent := 'N' ;  -- earlier it was D
                                                          lv_telephone_number := NULL ;
                                                          lv_tps_consent_value := NULL ;
                                                          lv_ctps_consent_value := NULL ;
                                            END ;
...contnd
end loop;