2 Replies Latest reply: Aug 30, 2012 10:35 PM by Vortex13 RSS

    How to update DO_NOT_MAIL_FLAG field in HZ_PARTIES.

    933676
      I need to update DO_NOT_MAIL_FLAG field in HZ_PARTIES. Is that coming in any APIs? Or do I need to update manually by writing queries? Because I want to know what checks/validations that I need to care about. I don't know much about this.

      Thanks in advance.

      Edited by: 930673 on Aug 15, 2012 9:55 PM
        • 1. Re: How to update DO_NOT_MAIL_FLAG field in HZ_PARTIES.
          Deborahcrm-Oracle
          Hello,

          The DO_NOT_MAIL_FLAG column in the table HZ_PARTIES is no longer used, therefore, no HZ API's will be updating it.

          Please see the table details in the Oracle eBusiness Suite Electronic Technical Reference Manual - eTRM for Release 11.5.10

          http://etrm.oracle.com/pls/trm11510/


          Thank you,
          Deborah Bourgeois
          Oracle Customer Support
          • 2. Re: How to update DO_NOT_MAIL_FLAG field in HZ_PARTIES.
            Vortex13
            I believe what you want is an API to create records in hz_contact_preferences.
            PROCEDURE opt_out (p_party_id     IN VARCHAR2
                              ,p_contact_type IN VARCHAR2 DEFAULT 'EMAIL' )
            IS 
            --
            CURSOR c1
            IS
            SELECT contact_preference_id, object_version_number
              FROM hz_contact_preferences
             WHERE contact_level_table_id = p_party_id
               AND contact_level_table = 'HZ_PARTIES'
               AND contact_type = p_contact_type;
            --
              l_return_status  VARCHAR2(1); 
              l_msg_count      NUMBER; 
              l_msg_data       VARCHAR2(1000);
              l_failure        EXCEPTION;
            --
              l_contact_preference_rec hz_contact_preference_v2pub.contact_preference_rec_type;
              l_ovn                    NUMBER;
              l_cpid                   NUMBER := 0; 
            --
            BEGIN
              FOR v1 IN c1 LOOP
                l_cpid := v1.contact_preference_id;
                l_ovn := v1.object_version_number;
              END LOOP;
              IF l_cpid = 0 THEN
                --INSERT
                l_contact_preference_rec.contact_level_table_id := p_party_id;
                l_contact_preference_rec.contact_level_table := 'HZ_PARTIES';
                l_contact_preference_rec.contact_type := p_contact_type;
                l_contact_preference_rec.preference_code := 'DO_NOT';
                l_contact_preference_rec.requested_by := 'INTERNAL';
                l_contact_preference_rec.created_by_module := 'XX_IES';
                hz_contact_preference_v2pub.create_contact_preference(
                  p_init_msg_list                         => FND_API.G_TRUE,
                  p_contact_preference_rec                => l_contact_preference_rec,
                  x_contact_preference_id                 => l_cpid,
                  x_return_status                         => l_return_status,
                  x_msg_count                             => l_msg_count,
                  x_msg_data                              => l_msg_data
                );
                IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
                  RAISE l_failure;
                END IF;
              ELSE
                --UPDATE
                hz_contact_preference_v2pub.get_contact_preference_rec(
                  p_init_msg_list                         => FND_API.G_TRUE,
                  p_contact_preference_id                 => l_cpid,
                  x_contact_preference_rec                => l_contact_preference_rec,
                  x_return_status                         => l_return_status,
                  x_msg_count                             => l_msg_count,
                  x_msg_data                              => l_msg_data
                );
                IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
                  RAISE l_failure;
                END IF;
                l_contact_preference_rec.preference_code := 'DO_NOT';
                hz_contact_preference_v2pub.update_contact_preference(
                  p_contact_preference_rec                => l_contact_preference_rec,
                  p_object_version_number                 => l_ovn,
                  x_return_status                         => l_return_status,
                  x_msg_count                             => l_msg_count,
                  x_msg_data                              => l_msg_data
                );
                IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
                  RAISE l_failure;
                END IF;
              END IF;
            EXCEPTION 
              WHEN l_failure THEN 
                IF l_msg_count > 0 THEN
                 RAISE_APPLICATION_ERROR(-20040, Fnd_Msg_Pub.get(1,'F') );
                END IF; 
              WHEN OTHERS THEN
                RAISE_APPLICATION_ERROR(-20050, 'Critical error inserting contact preferences.' );
            END opt_out;
            HTH,
            --Johnnie