This discussion is archived
2 Replies Latest reply: Aug 30, 2012 8:35 PM by Vortex13 RSS

How to update DO_NOT_MAIL_FLAG field in HZ_PARTIES.

933676 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points