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;