Oracle EBS AR 更新客户账户层

最新推荐文章于 2024-02-01 14:45:36 发布

weixin_30265171

最新推荐文章于 2024-02-01 14:45:36 发布

阅读量143

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/jenrry/p/10006829.html

版权

本文详细解析了一个用于在Oracle数据库中更新客户账户信息的PL/SQL过程。该过程通过游标遍历客户账户站点、位置、账户详情等表，获取账户ID、账户编号、状态等信息，然后调用HZ_CUST_ACCOUNT_V2PUB包的UPDATE_CUST_ACCOUNT过程进行账户信息的更新。过程中包含了错误处理和输出消息的部分。

摘要由CSDN通过智能技术生成

declare
  x_return_status         VARCHAR2(150);
  x_msg_count             NUMBER;
  x_msg_data              VARCHAR2(2000);
  x_profile_id            NUMBER;
  l_location_id           NUMBER;
  l_object_version_number NUMBER;
  l_party_rec             hz_cust_account_v2pub.cust_account_rec_type;
  cursor cur_DT is
    SELECT loc.location_id,
           loc.object_version_number,
           addr.PARTY_SITE_ID,
           hc.ACCOUNT_NUMBER,
           hc.CUST_ACCOUNT_ID,
           party_site.PARTY_SITE_NAME,
           hcp.CUST_ACCOUNT_PROFILE_ID,
           hcp.CREDIT_HOLD,
           hc.CREATION_DATE,
           hc.CREATED_BY,
           hc.ORIG_SYSTEM_REFERENCE,
           hc.PARTY_ID,
           hc.STATUS
      FROM hz_cust_acct_sites_all addr,
           hz_party_sites         party_site,
           hz_locations           loc,
           HZ_CUSTOMER_PROFILES   hcp,
           HZ_CUST_ACCOUNTS       hc
     WHERE 1 = 1
       AND addr.party_site_id = party_site.party_site_id
       AND loc.location_id = party_site.location_id
       and hc.PARTY_ID = party_site.PARTY_ID
       and hc.CUST_ACCOUNT_ID = hcp.CUST_ACCOUNT_ID
       and party_site.PARTY_ID =
           (select hz.PARTY_ID
              from hz_parties hz
             where hz.PARTY_ID = party_site.PARTY_ID
               and hz.PARTY_NUMBER = '10984');

begin
  fnd_global.apps_initialize(0, 50738, 20003);
  mo_global.init('AR');
  FOR rec IN CUR_DT LOOP
    begin
      select hc.OBJECT_VERSION_NUMBER
        into l_object_version_number
        from HZ_CUST_ACCOUNTS hc
       where hc.CUST_ACCOUNT_ID = rec.CUST_ACCOUNT_ID;
    end;
    l_party_rec.CUST_ACCOUNT_ID       := rec.CUST_ACCOUNT_ID;
    l_party_rec.ACCOUNT_NUMBER        := rec.ACCOUNT_NUMBER;
    l_party_rec.status                := 'I';
    l_party_rec.orig_system_reference := rec.orig_system_reference;
  
    hz_cust_account_v2pub.update_cust_account(p_init_msg_list         => fnd_api.g_false,
                                              p_cust_account_rec      => l_party_rec,
                                              p_object_version_number => l_object_version_number,
                                              x_return_status         => x_return_status,
                                              x_msg_count             => x_msg_count,
                                              x_msg_data              => x_msg_data);
  
    IF x_return_status = fnd_api.g_ret_sts_error THEN
      x_msg_count := fnd_msg_pub.count_msg;
      x_msg_data  := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
                                            fnd_api.g_false),
                            1,
                            512);
      dbms_output.put_line(x_msg_data);
      fnd_msg_pub.delete_msg();
      RAISE fnd_api.g_exc_error;
    ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
      x_msg_count := fnd_msg_pub.count_msg;
      x_msg_data  := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
                                            fnd_api.g_false),
                            1,
                            512);
      dbms_output.put_line(x_msg_data);
    
      fnd_msg_pub.delete_msg();
      RAISE fnd_api.g_exc_unexpected_error;
    END IF;
    IF x_return_status = fnd_api.g_ret_sts_success THEN
      dbms_output.put_line('------------------Update Success-------------------');
    
    END IF;
  END LOOP;
END;