Saturday 6 June 2015

API to assign existing customer sites to other OUs(Operating Unit)

1. Create a custom table to hold error massages.


CREATE TABLE xxcona_cust_acct_sites_error
(
org_id  NUMBER,
cust_account_id NUMBER,
party_site_id NUMBER,
cust_acct_site_id NUMBER,
site_use_id NUMBER,
process_flag VARCHAR2(1),
error_msg  VARCHAR2(4000),
error_api  VARCHAR2(250)
);


2. Use the below procedure.



CREATE OR REPLACE PROCEDURE xxcona_assign_cust_site_to_org (
   p_cust_account_id      IN   NUMBER,
   p_assign_from_org_id   IN   NUMBER,
   p_assign_to_org_id     IN   NUMBER
)
AS
----------------------------------------------------------------------------------------------------
-- File name       : xxcona_assign_cust_site_to_org.prc
--
-- Author          : Arjun Prasad 
--
-- Created         : 06-JUN-2015
--
-- Description     : This PL/SQL procedure is used to assign customer sites to another operating units.
--
-----------------------------------------------------------------------------------------------------
--  Date          Author          Version    Reason
-------------- ---------------    -------- ----------------------------------------------------------
--06-JUN-2015    Arjun Prasad     1.0      Initial creation
-----------------------------------------------------------------------------------------------------

---For create_cust_acct_site creation--------------
   v_customer_acount_site_record   hz_cust_account_site_v2pub.cust_acct_site_rec_type;
   v_cust_acct_site_id             NUMBER                             := NULL;
   v_return_status                 VARCHAR2 (2)                       := NULL;
   v_msg_count                     NUMBER                             := NULL;
   v_msg_data                      VARCHAR2 (32000)                   := NULL;
   ---For cust_site_use_rec  creation--------------
   v_cust_site_use_rec             hz_cust_account_site_v2pub.cust_site_use_rec_type;
   v_customer_profile_rec          hz_customer_profile_v2pub.customer_profile_rec_type;
   v_site_use_id                   NUMBER                             := NULL;
   l_msg_index_out                 NUMBER;

   CURSOR cust_acct_sites
   IS
      SELECT hcas.org_id, hcas.cust_account_id, hcas.party_site_id,
             hcas.cust_acct_site_id
        FROM hz_cust_acct_sites_all hcas
       WHERE hcas.org_id = p_assign_from_org_id
         AND hcas.cust_account_id =
                                 NVL (p_cust_account_id, hcas.cust_account_id);

   CURSOR cust_acct_site_uses (
      xx_cust_acct_site_id    NUMBER,
      xx_assign_from_org_id   NUMBER
   )
   IS
      SELECT hcsu.org_id, hcsu.cust_acct_site_id, hcsu.site_use_id
        FROM hz_cust_site_uses_all hcsu
       WHERE hcsu.cust_acct_site_id = xx_cust_acct_site_id
         AND hcsu.org_id = xx_assign_from_org_id;
BEGIN
   --==============Initializa Application ======================--
   fnd_global.apps_initialize (user_id           => 1737,
                               resp_id           => 20678,
                               resp_appl_id      => 222
                              );
     MO_GLOBAL.SET_POLICY_CONTEXT('S',p_assign_to_org_id);
     MO_GLOBAL.INIT ('AR') ;                            

   FOR cur_rec IN cust_acct_sites
   LOOP
      v_customer_acount_site_record := NULL;
      --==============To get_cust_acct_site_rec =========================--
      hz_cust_account_site_v2pub.get_cust_acct_site_rec
                      (p_init_msg_list           => fnd_api.g_false,
                       p_cust_acct_site_id       => cur_rec.cust_acct_site_id,
                       x_cust_acct_site_rec      => v_customer_acount_site_record,
                       x_return_status           => v_return_status,
                       x_msg_count               => v_msg_count,
                       x_msg_data                => v_msg_data
                      );

      IF NVL (v_return_status, 'E') = 'S'
      THEN
         DBMS_OUTPUT.put_line (   'get_cust_acct_site_rec=>V_RETURN_STATUS:='
                               || v_return_status
                              );
      END IF;

--==============To create_cust_acct_site =========================--
      v_customer_acount_site_record.org_id := p_assign_to_org_id;
      v_customer_acount_site_record.cust_acct_site_id := NULL;
      v_customer_acount_site_record.orig_system_reference := NULL;
      hz_cust_account_site_v2pub.create_cust_acct_site
                                               (fnd_api.g_true,
                                                v_customer_acount_site_record,
                                                v_cust_acct_site_id,
                                                --returned
                                                v_return_status,
                                                --To indicagte success
                                                v_msg_count,                 --
                                                v_msg_data
                                               );
      DBMS_OUTPUT.put_line (   'create_cust_acct_site=>V_RETURN_STATUS:='
                            || v_return_status
                           );

      IF NVL (v_return_status, 'E') = 'S' AND v_cust_acct_site_id IS NOT NULL
      THEN
         v_return_status := NULL;

         FOR site_uses_rec IN cust_acct_site_uses (cur_rec.cust_acct_site_id,
                                                   p_assign_from_org_id
                                                  )
         LOOP
            v_cust_site_use_rec := NULL;
            v_customer_profile_rec := NULL;
            --==============To get_cust_site_use_rec =========================--
            hz_cust_account_site_v2pub.get_cust_site_use_rec
                           (p_init_msg_list             => fnd_api.g_false,
                            p_site_use_id               => site_uses_rec.site_use_id,
                            x_cust_site_use_rec         => v_cust_site_use_rec,
                            x_customer_profile_rec      => v_customer_profile_rec,
                            x_return_status             => v_return_status,
                            x_msg_count                 => v_msg_count,
                            x_msg_data                  => v_msg_data
                           );

            IF NVL (v_return_status, 'E') = 'S'
            THEN
               DBMS_OUTPUT.put_line
                               (   'get_cust_site_use_rec=>V_RETURN_STATUS:='
                                || v_return_status
                               );
            END IF;

            --==============To create_cust_site_use =========================--
            v_cust_site_use_rec.org_id := p_assign_to_org_id;
            v_cust_site_use_rec.site_use_id := NULL;
            v_cust_site_use_rec.cust_acct_site_id := v_cust_acct_site_id;
            v_cust_site_use_rec.orig_system_reference := NULL;
            v_customer_profile_rec.cust_account_profile_id := NULL;
            hz_cust_account_site_v2pub.create_cust_site_use
                            (p_init_msg_list             => fnd_api.g_true,
                             p_cust_site_use_rec         => v_cust_site_use_rec,
                             p_customer_profile_rec      => v_customer_profile_rec,
                             p_create_profile            => fnd_api.g_true,
                             p_create_profile_amt        => fnd_api.g_false,
                             x_site_use_id               => v_site_use_id,
                             x_return_status             => v_return_status,
                             x_msg_count                 => v_msg_count,
                             x_msg_data                  => v_msg_data
                            );
            DBMS_OUTPUT.put_line (   'create_cust_site_use=>V_RETURN_STATUS:='
                                  || v_return_status
                                 );

            IF NVL (v_return_status, 'E') = 'S'
            THEN
               DBMS_OUTPUT.put_line (   'ORG_ID:='
                                     || v_cust_site_use_rec.org_id
                                     || '***'
                                     || 'v_site_use_id:='
                                     || v_site_use_id
                                    );
            ELSE
               FOR i IN 1 .. v_msg_count
               LOOP
                  fnd_msg_pub.get (p_msg_index          => i,
                                   p_data               => v_msg_data,
                                   p_encoded            => fnd_api.g_false,
                                   p_msg_index_out      => l_msg_index_out
                                  );
                  v_msg_data := v_msg_data || ' *** ' || v_msg_data;
               END LOOP;

               DBMS_OUTPUT.put_line (   'create_cust_site_use=>Error: '
                                     || v_msg_data
                                    );

               INSERT INTO xxcona_cust_acct_sites_error
                           (org_id, cust_account_id,
                            party_site_id, cust_acct_site_id,
                            site_use_id, process_flag,
                            error_msg,
                            error_api
                           )
                    VALUES (cur_rec.org_id, cur_rec.cust_account_id,
                            cur_rec.party_site_id, cur_rec.cust_acct_site_id,
                            site_uses_rec.site_use_id, 'N',
                            SUBSTR (v_msg_data, 1, 4000),
                            'create_cust_site_use'
                           );
            END IF;
         END LOOP;
      ELSE
         FOR i IN 1 .. v_msg_count
         LOOP
            fnd_msg_pub.get (p_msg_index          => i,
                             p_data               => v_msg_data,
                             p_encoded            => fnd_api.g_false,
                             p_msg_index_out      => l_msg_index_out
                            );
            v_msg_data := v_msg_data || ' *** ' || v_msg_data;
         END LOOP;

         DBMS_OUTPUT.put_line ('create_cust_acct_site=>Error: ' || v_msg_data);

         INSERT INTO xxcona_cust_acct_sites_error
                     (org_id, cust_account_id,
                      party_site_id, cust_acct_site_id, site_use_id,
                      process_flag, error_msg,
                      error_api
                     )
              VALUES (cur_rec.org_id, cur_rec.cust_account_id,
                      cur_rec.party_site_id, cur_rec.cust_acct_site_id, NULL,
                      'N', SUBSTR (v_msg_data, 1, 4000),
                      'create_cust_acct_site'
                     );
      END IF;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR MESSAGE :=' || SQLERRM);
END;

3. Syntax to call above procedure.


BEGIN
   xxcona_assign_cust_site_to_org (p_cust_account_id         => 5042, 
-- Pass NULL to consider all customer sites.
                                   p_assign_from_org_id      => 81,
                                   p_assign_to_org_id        => 221
                                  );
END;

No comments:

Post a Comment