• Oracle EBS AP 供应商地点失效


    /*
    供应商地点失效
    creation: created by jenrry 20161108 1.00
    
    */
    DECLARE
      lv_return_status          VARCHAR2(1) := NULL;
      ln_msg_count              NUMBER;
      lv_errmsg                 VARCHAR2(5000);
      lt_vendor_site_rec        ap_vendor_pub_pkg.r_vendor_site_rec_type;
      p_vendor_rec              ap_vendor_pub_pkg.r_vendor_rec_type;
      l_party_site_rec          hz_party_site_v2pub.party_site_rec_type;
      lt_location_rec           hz_location_v2pub.location_rec_type;
      ln_object_version_number  NUMBER;
      ln_object_version_number2 NUMBER;
      l_object_version_number   number;
      ln_party_site_id          NUMBER := NULL;
      lv_party_site_name        VARCHAR2(240);
      lv_party_site_number      VARCHAR2(30);
      ln_location_id            NUMBER;
      x_profile_id              number;
      l_party_rec             hz_party_v2pub.party_rec_type;
      l_organization_rec      hz_party_v2pub.organization_rec_type;
      l_status                VARCHAR2(1) := 'A';
      cursor cur_tmp is
        select pvs.vendor_site_id,
               pvs.party_site_id,
               hps.party_site_name,
               hps.object_version_number,
               hps.party_site_number,
               pvs.location_id,
               hl.object_version_number object_version_number2,
               po.VENDOR_NAME,
               pvs.VENDOR_SITE_CODE,
               po.VENDOR_ID,
               po.PARTY_ID,
               pvs.INACTIVE_DATE
        
          FROM ap_supplier_sites_all pvs,
               hz_party_sites        hps,
               hz_locations          hl,
               ap_suppliers            PO
         WHERE pvs.party_site_id = hps.party_site_id
           AND pvs.location_id = hl.location_id
           and po.VENDOR_ID = pvs.VENDOR_ID
           and po.ATTRIBUTE13 = 'S201608160069';
     
    BEGIN
        --
    
      fnd_global.apps_initialize(0, 50738, 20003);
      dbms_output.put_line('------------------Start Update-------------------');
     
    
      for cur in cur_tmp loop
      
        --
        lt_vendor_site_rec.vendor_site_id := cur.vendor_site_id;
        if l_status = 'I' then
        lt_vendor_site_rec.inactive_date := SYSDATE;
        else 
        lt_vendor_site_rec.inactive_date := fnd_api.G_NULL_DATE; --sysdate + 30;--to_date('3033-12-07','YYYY-MM-DD');
        end if;
        ap_vendor_pub_pkg.update_vendor_site(p_api_version      => 1.0,
                                             p_init_msg_list    => fnd_api.g_true,
                                             p_commit           => fnd_api.g_false,
                                             p_validation_level => fnd_api.g_valid_level_full,
                                             x_return_status    => lv_return_status,
                                             x_msg_count        => ln_msg_count,
                                             x_msg_data         => lv_errmsg,
                                             p_vendor_site_rec  => lt_vendor_site_rec,
                                             p_vendor_site_id   => cur.vendor_site_id);
        IF (nvl(lv_return_status, fnd_api.g_ret_sts_error) <>
           fnd_api.g_ret_sts_success) THEN
          ln_msg_count := fnd_msg_pub.count_msg;
          IF ln_msg_count > 0 THEN
            lv_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
                                                fnd_api.g_false),
                                1,
                                512);
          
            FOR i IN 1 .. (ln_msg_count - 1) LOOP
            
              lv_errmsg := lv_errmsg || chr(10) ||
                           substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
                                                  fnd_api.g_false),
                                  1,
                                  512);
            END LOOP;
            fnd_msg_pub.delete_msg();
          END IF;
          dbms_output.put_line('------------------Update Error-------------------');
          dbms_output.put_line('Error1 :' || lv_errmsg);
        END IF;
        IF lv_return_status = fnd_api.g_ret_sts_success THEN
          dbms_output.put_line('------------------Update vendor Success-------------------');
        
        END IF;
        
        l_party_site_rec.party_site_id := cur.party_site_id;
        l_party_site_rec.status := l_status;
        --
        begin
          select hps.OBJECT_VERSION_NUMBER
            into ln_object_version_number
            from hz_party_sites hps
           where hps.PARTY_SITE_ID = cur.party_site_id;
        end;
        hz_party_site_v2pub.update_party_site(p_init_msg_list         => fnd_api.g_false,
                                              p_party_site_rec        => l_party_site_rec,
                                              p_object_version_number => ln_object_version_number,
                                              x_return_status         => lv_return_status,
                                              x_msg_count             => ln_msg_count,
                                              x_msg_data              => lv_errmsg);
        IF (nvl(lv_return_status, fnd_api.g_ret_sts_error) <>
           fnd_api.g_ret_sts_success) THEN
          ln_msg_count := fnd_msg_pub.count_msg;
          IF ln_msg_count > 0 THEN
            lv_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
                                                fnd_api.g_false),
                                1,
                                512);
          
            FOR i IN 1 .. (ln_msg_count - 1) LOOP
            
              lv_errmsg := lv_errmsg || chr(10) ||
                           substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
                                                  fnd_api.g_false),
                                  1,
                                  512);
            END LOOP;
            fnd_msg_pub.delete_msg();
          END IF;
          dbms_output.put_line('------------------Update Error-------------------');
          dbms_output.put_line('Error2 :' || lv_errmsg);
        END IF;
        IF lv_return_status = fnd_api.g_ret_sts_success THEN
          dbms_output.put_line('------------------Update party site Success-------------------');
        
        END IF;
        --update location
        lt_location_rec.address_style := 'POSTAL_ADDR_DEF';
        lt_location_rec.location_id   := cur.location_id;
      
        hz_location_v2pub.update_location(p_init_msg_list         => fnd_api.g_true,
                                          p_location_rec          => lt_location_rec,
                                          p_object_version_number => cur.object_version_number2,
                                          x_return_status         => lv_return_status,
                                          x_msg_count             => ln_msg_count,
                                          x_msg_data              => lv_errmsg);
        IF (nvl(lv_return_status, fnd_api.g_ret_sts_error) <>
           fnd_api.g_ret_sts_success) THEN
          ln_msg_count := fnd_msg_pub.count_msg;
          IF ln_msg_count > 0 THEN
            lv_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
                                                fnd_api.g_false),
                                1,
                                512);
          
            FOR i IN 1 .. (ln_msg_count - 1) LOOP
            
              lv_errmsg := lv_errmsg || chr(10) ||
                           substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
                                                  fnd_api.g_false),
                                  1,
                                  512);
            END LOOP;
            fnd_msg_pub.delete_msg();
          END IF;
          dbms_output.put_line('------------------Update Error-------------------');
          dbms_output.put_line('Error3 :' || lv_errmsg);
        END IF;
        --
        IF lv_return_status = fnd_api.g_ret_sts_success THEN
          dbms_output.put_line('------------------Update location Success-------------------');
        
        END IF;
        --update vendor name
        p_vendor_rec.vendor_id := cur.vendor_id;
        if l_status = 'I' then
        p_vendor_rec.VENDOR_NAME := cur.VENDOR_NAME || '(作废)';
        p_vendor_rec.END_DATE_ACTIVE := sysdate;
        else
          p_vendor_rec.VENDOR_NAME := replace(cur.VENDOR_NAME, '(作废)','');
          p_vendor_rec.END_DATE_ACTIVE := fnd_api.G_NULL_DATE; --sysdate + 30; --to_date('3033-12-07','YYYY-MM-DD');
          p_vendor_rec.START_DATE_ACTIVE := sysdate;
        end if;
        ap_vendor_pub_pkg.update_vendor(p_api_version      => 1.0,
                                        p_init_msg_list    => fnd_api.g_false,
                                        p_commit           => fnd_api.g_false,
                                        p_validation_level => NULL,
                                        x_return_status    => lv_return_status,
                                        x_msg_count        => ln_msg_count,
                                        x_msg_data         => lv_errmsg,
                                        p_vendor_rec       => p_vendor_rec,
                                        p_vendor_id        => cur.vendor_id);
        IF (nvl(lv_return_status, fnd_api.g_ret_sts_error) <>
           fnd_api.g_ret_sts_success) THEN
          ln_msg_count := fnd_msg_pub.count_msg;
          IF ln_msg_count > 0 THEN
            lv_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
                                                fnd_api.g_false),
                                1,
                                512);
          
            FOR i IN 1 .. (ln_msg_count - 1) LOOP
            
              lv_errmsg := lv_errmsg || chr(10) ||
                           substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
                                                  fnd_api.g_false),
                                  1,
                                  512);
            END LOOP;
            fnd_msg_pub.delete_msg();
          END IF;
          dbms_output.put_line('------------------Update vendor name Error-------------------');
          dbms_output.put_line('Error1 :' || lv_errmsg);
        END IF;
        IF lv_return_status = fnd_api.g_ret_sts_success THEN
          dbms_output.put_line('------------------Update vendor name Success-------------------');
        
        END IF;
        
        --
        begin
        SELECT hp.object_version_number
            INTO l_object_version_number
            FROM hz_parties hp
           WHERE hp.party_id = cur.party_id
           and rownum = 1;
        end;
          l_party_rec.party_id                          := cur.party_id;
          l_organization_rec.party_rec                  := l_party_rec;
          if l_status = 'I' then
          l_organization_rec.organization_name          := cur.vendor_name || '(作废)';
          else 
            l_organization_rec.organization_name          := replace(cur.vendor_name , '(作废)','');
          end if;
          cux_ap_vendors_api.update_organization(p_init_msg_list    => fnd_api.g_false,
                                                 p_commit           => fnd_api.g_false,
                                                 p_party_id         => cur.party_id,
                                                 p_organization_rec => l_organization_rec,
                                                 x_profile_id       => x_profile_id,
                                                 x_return_status    => lv_return_status,
                                                 x_msg_count        => ln_msg_count,
                                                 x_msg_data         => lv_errmsg);
        IF (nvl(lv_return_status, fnd_api.g_ret_sts_error) <>
           fnd_api.g_ret_sts_success) THEN
          ln_msg_count := fnd_msg_pub.count_msg;
          IF ln_msg_count > 0 THEN
            lv_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
                                                fnd_api.g_false),
                                1,
                                512);
          
            FOR i IN 1 .. (ln_msg_count - 1) LOOP
            
              lv_errmsg := lv_errmsg || chr(10) ||
                           substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
                                                  fnd_api.g_false),
                                  1,
                                  512);
            END LOOP;
            fnd_msg_pub.delete_msg();
          END IF;
          dbms_output.put_line('------------------Update vendor party name Error-------------------');
          dbms_output.put_line('Error1 :' || lv_errmsg);
        END IF;
        IF lv_return_status = fnd_api.g_ret_sts_success THEN
          dbms_output.put_line('------------------Update vendor party name Success-------------------');
        
        END IF;
      END LOOP;
      dbms_output.put_line('------------------Update End-------------------');
    END;
    土豆君
  • 相关阅读:
    [Python 多线程] Concurrent (十五)
    [Python 多线程] GIL全局解释器锁 (十三)
    [Python 多线程] Semaphore、BounedeSemaphore (十二)
    自定义HandlerMethodArgumentResolver参数解析器和源码分析
    Dubbo分布式日志追踪
    kafka的分区分配策略
    深入剖析kafka架构内部原理
    kafka partition(分区)与 group
    Kafka消费组(consumer group)
    kafka存储机制以及offset
  • 原文地址:https://www.cnblogs.com/jenrry/p/10006814.html
Copyright © 2020-2023  润新知