Saturday, December 2, 2017

Script to update the Supplier Sites "auto_tax_calc_flag" (Options : Y,N and NULL)

DECLARE
CURSOR CUR_SITES
IS
  select
    asa.vendor_site_id
  from
    ap_suppliers aps
    ,ap_supplier_sites_all asa
  where 1=1
    and aps.vendor_id = asa.vendor_id
    and NVL(vendor_type_lookup_code,0) != 'EMPLOYEE'
    and asa.inactive_date is null
    and asa.org_id = 204
    and aps.segment1 in ('5689')
  order by asa.auto_tax_calc_flag,aps.VENDOR_NAME,asa.vendor_site_code
  ;
 
  l_vendor_site_id           NUMBER;
  l_SITES_msg_count          NUMBER;
  l_SITES_msg_data           VARCHAR2(4000);
  l_SITES_return_status      VARCHAR2(10);
  l_vendor_site_rec          AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;

BEGIN
  FND_GLOBAL.apps_initialize(1230,50239,200);

FOR SITES_REC IN CUR_SITES
 LOOP
 l_vendor_site_id                       := SITES_REC.VENDOR_SITE_ID;
 l_vendor_site_rec.AUTO_TAX_CALC_FLAG   := 'N';

 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_TRUE,
  p_validation_level => FND_API.G_VALID_LEVEL_FULL,
  p_vendor_site_id   => l_vendor_site_id,
  p_vendor_site_rec  => l_vendor_site_rec,
  x_return_status    => l_SITES_return_status,
  x_msg_count        => l_SITES_msg_count,
  x_msg_data         => l_SITES_msg_data
  );

  IF l_SITES_return_status = 'S'
  THEN
    COMMIT;
    dbms_output.put_line('Vendor Site ID : '||l_vendor_site_id||' Status is : '||l_SITES_return_status); 
  ELSE
    ROLLBACK;
    dbms_output.put_line('Vendor Site ID : '||l_vendor_site_id||' Status is : '||l_SITES_return_status);   
  END IF;

  FOR I IN 1..l_SITES_msg_count
  LOOP
    l_SITES_msg_data := SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);
    dbms_output.put_line(l_SITES_msg_data);
  END LOOP ;
 END LOOP ;

END;


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect