EBS开发_修改客户地点API

--Disable customer site
DECLARE
  lv_party_site_number      VARCHAR2(30) := '';--&p_party_site_number;
  lr_party_site_rec_type    hz_party_site_v2pub.party_site_rec_type;
  lr_cust_acct_site_rec     hz_cust_account_site_v2pub.cust_acct_site_rec_type;
  lr_cust_site_use_rec_type hz_cust_account_site_v2pub.cust_site_use_rec_type;

  lr_orig_sys_reference_rec hz_orig_system_ref_pub.orig_sys_reference_rec_type;

  ln_version_num            NUMBER;
  ln_version_num_party_site NUMBER;
  x_return_status           VARCHAR2(10);
  x_msg_count               NUMBER;
  x_msg_data                VARCHAR2(1000);

  ln_failed_count NUMBER := 0;
  api_exception EXCEPTION;

  lv_errmsg VARCHAR2(2000);
BEGIN
  dbms_output.put_line('Customer site number: ' || lv_party_site_number);

  --begin to disable customer site use
  SELECT hcas.cust_acct_site_id
        ,hcas.org_id
    INTO lr_cust_acct_site_rec.cust_acct_site_id
        ,lr_cust_acct_site_rec.org_id
    FROM hz_cust_accounts       hca
        ,hz_cust_acct_sites_all hcas
        ,hz_party_sites         hps
   WHERE hca.cust_account_id = hcas.cust_account_id
     AND hcas.party_site_id = hps.party_site_id
     and hcas.cust_acct_site_id = 10003;
     --AND hps.party_site_number = lv_party_site_number;
     
begin
  fnd_global.apps_initialize(user_id      => 12249,
                             resp_id      => 50775,
                             resp_appl_id => 435);
  mo_global.init('AR');--mo_global.init('M');
end;     
     
--  mo_global.init('AR');
  mo_global.set_policy_context('S', lr_cust_acct_site_rec.org_id);
  dbms_output.put_line('Org id : ' || lr_cust_acct_site_rec.org_id);

  --begin to disable customer site
  dbms_output.put_line('                                                                                     ');
  dbms_output.put_line('==========================Trunc customer site attribute20=======================================');

  SELECT hcas.cust_acct_site_id
        ,hcas.org_id
        ,hcas.object_version_number
    INTO lr_cust_acct_site_rec.cust_acct_site_id
        ,lr_cust_acct_site_rec.org_id
        ,ln_version_num
    FROM hz_cust_accounts       hca
        ,hz_cust_acct_sites_all hcas
        ,hz_party_sites         hps
   WHERE hca.cust_account_id = hcas.cust_account_id
     AND hcas.party_site_id = hps.party_site_id
     and hcas.cust_acct_site_id = 10003;
     --AND hps.party_site_number = lv_party_site_number;
  lr_cust_acct_site_rec.attribute20 := chr(0);--chr(0)--'hellothere'--置空不可以用'',查看源码HZ_CUST_ACCT_SITES_PKG才知道只可以用FND_API.G_MISS_CHAR即chr(0)才显示null
  

  

  --call api to disable customer site
  hz_cust_account_site_v2pub.update_cust_acct_site(p_init_msg_list         => fnd_api.g_true,
                                                   p_cust_acct_site_rec    => lr_cust_acct_site_rec,
                                                   p_object_version_number => ln_version_num,
                                                   x_return_status         => x_return_status,
                                                   x_msg_count             => x_msg_count,
                                                   x_msg_data              => x_msg_data);

commit;
  IF x_return_status = 'S' THEN
      
    --delete customer xross table
/*    DELETE FROM xxwe_mg_customer_xross xmcx
     WHERE party_site_number_de = lv_party_site_number
       AND org_id = lr_cust_acct_site_rec.org_id;*/
    dbms_output.put_line('Disable customer site successfully !');
  ELSE
    ln_failed_count := ln_failed_count + 1;
    dbms_output.put_line('Failed to disable customer site !');
    dbms_output.put_line('Error message  : ' || x_msg_data);
    RAISE api_exception;
  END IF;

/*  dbms_output.put_line('                                                                                     ');
  dbms_output.put_line('==========================Disable party site=========================================');

  --begin to disable party site
  SELECT hps.party_site_id
        ,hps.party_site_name || '_d'
        ,hps.object_version_number
    INTO lr_party_site_rec_type.party_site_id
        ,lr_party_site_rec_type.party_site_name
        ,ln_version_num_party_site
    FROM hz_cust_accounts       hca
        ,hz_cust_acct_sites_all hcas
        ,hz_party_sites         hps
   WHERE hca.cust_account_id = hcas.cust_account_id
     AND hcas.party_site_id = hps.party_site_id
     and hcas.cust_acct_site_id = 10003;
    -- AND hps.party_site_number = lv_party_site_number;

  lr_party_site_rec_type.status := 'I';
  hz_party_site_v2pub.update_party_site(p_init_msg_list         => fnd_api.g_true,
                                        p_party_site_rec        => lr_party_site_rec_type,
                                        p_object_version_number => ln_version_num_party_site,
                                        x_return_status         => x_return_status,
                                        x_msg_count             => x_msg_count,
                                        x_msg_data              => x_msg_data);

  IF x_return_status = 'S' THEN
    dbms_output.put_line('Disable party site successfully !');
  ELSE
    dbms_output.put_line('Failed to disable party site !');
    dbms_output.put_line('Error message  : ' || x_msg_data);
    RAISE api_exception;
  END IF;*/

EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('No data found!');
    ROLLBACK;
  WHEN api_exception THEN
    dbms_output.put_line('Error occured when execute oracle api!');
    ROLLBACK;
  WHEN OTHERS THEN
    dbms_output.put_line('Unexpected exception occured!');
    ROLLBACK;
END;

--查看更新结果
/*
SELECT length(hca.attribute20),hca.attribute20,hcs.site_use_id,hca.*
  FROM hz_parties             hp
      ,hz_party_sites         hps
      ,hz_cust_acct_sites_all hca
      ,hz_cust_site_uses_all  hcs
      ,hz_cust_accounts       hzc
 WHERE hp.party_id = hps.party_id
   AND hps.status = 'A' --地点状态控制
   AND hp.status = 'A' --组织状态控制
   AND hzc.status = 'A' --客户账户状态控制
   AND hca.party_site_id = hps.party_site_id
   AND hca.cust_acct_site_id = hcs.cust_acct_site_id
   AND hzc.party_id = hp.party_id
   AND hcs.site_use_code = 'BILL_TO' --收单方--不同组织存在多个收单方，会出现一个客户多条记录
  -- AND hzc.account_number in ('3090') 
   and hca.cust_acct_site_id = 10003;
   */
   
/*   SELECT hca.attribute20
         ,hca.*
     FROM hz_cust_acct_sites_all hca
    WHERE hca.attribute20 IS NOT NULL;*/