Query to get/find the Package/Procedure/Content View associated with a WebADI Integrator

 

--Query to get Package/procedure used with WebADI Interface

SELECT ba.attribute2 interface_pkg_proc
      ,bit.user_name
      ,ba.*
 FROM apps.bne_attributes ba,
      apps.bne_param_lists_b bplb,
      apps.bne_interfaces_b bib,
      apps.bne_integrators_tl bit
WHERE bib.upload_param_list_code = bplb.param_list_code
  AND bib.integrator_code = bit.integrator_code
  AND ba.attribute_code = bplb.attribute_code
  AND bit.user_name = <integrator_name>; 

 
--Query to get the Content/View Name for the WebADI
SELECT bct.*
  FROM apps.bne_contents_tl bct,
       apps.bne_contents_b bcb,
       apps.bne_integrators_tl bit
 WHERE bit.integrator_code = bcb.integrator_code
   AND bcb.content_code = bct.content_code
   AND bit.user_name = <integrator_name>; 

 

 

How to clear BNE cache for WebADI Development

A. Releases prior to 12.2.7
Using System Administrator responsibility:

1. Login to the application as a user with System Administrator Responsibility.
2. Select the System Administrator Responsibility.
3. Bring up the AdminServlet by changing the URL in the browser where you are logged into the applications keeping the appropriate hostname.domain and port number.

Release 11i:
https://hostname.domain:portnumber/oa_servlets/oracle.apps.bne.framework.BneAdminServlet
On the new web page that loads, scroll down and click the "clear-cache" link

Release 12.0 - 12.6:
https://hostname.domain:portnumber/OA_HTML/BneAdminServlet
OR
https://erp-test.xyzcompany.com/OA_HTML/BneAdminServlet
On the new web page that loads, scroll down to the Cache Name section.
Clear the cache for the following by clicking the "(clear)" link:

Cache Name
============
Default Cache
Generic SQL Statements
Web ADI Repository Objects
Web ADI Parameter Lists
Web ADI Parameter Definitions

The BNE cache is now cleared.

B. Release 12.2.7 and higher:
Access to the BneAdminServlet is now secured by the function WDF_CREATE_INTEGRATOR (Desktop Integration Manager - Create Integrator).
1. Login to the application.
2. Select the Desktop Integration Manager responsibility or a responsibility that includes the function WDF_CREATE_INTEGRATOR.
3. Proceed as in step 5 for release 12 above.

Query to find Custom Layouts for Oracle WebADI

--Query to get WebADI Custom layouts
SELECT bl.application_id
      ,(select APPLICATION_NAME
          from fnd_application_vl
         where application_id = bl.application_id) lay_app_name
      ,bl.layout_code
      ,bl.user_name layout_name
      ,bl.integrator_app_id
      ,(select APPLICATION_NAME
          from fnd_application_vl
         where application_id = bl.integrator_app_id) intg_app_name
      ,bl.integrator_code
      ,(select user_name from fnd_user where user_id = bl.created_by) lay_created_by
      ,creation_date
  FROM bne_layouts_vl bl
 WHERE bl.created_by > 2
   AND EXISTS
       (select 1
          from fnd_user
         where user_id = bl.created_by)


--Query to get all the Fields/Columns included/selected for the specific WebADI Layout Code
SELECT blc.prompt_left
      ,blc.interface_col_name
      ,blc.user_hint
      ,blc.required_flag
      ,blc.display_flag
      ,blc.read_only_flag
      ,blc.val_type
      ,blc.val_id_col
      ,blc.val_obj_name
      ,blc.lov_type
    FROM bne_layout_cols_v blc
   WHERE blc.layout_code = :P_LAYPUT_CODE --Get custom layout code from above query output
ORDER BY block_id
        ,sequence_num

WebADI - Delete Interface/Integrator from Backend

--Query to Get Integrator code
SELECT application_id
      ,integrator_code
      ,user_name
  FROM bne_integrators_vl
WHERE user_name  = '<Integrator Name>';

--Query to Get Interface Code
SELECT *
  FROM bne_interfaces_b
 WHERE integrator_code = '<Integrator_code>';

-- Query to Get interface columns
SELECT *
  FROM bne_interface_cols_b
 WHERE interface_code = '<interface_code>';

--Query get an Interface/Integrator Details
SELECT biv.application_id
      ,biv.integrator_code
      ,biv.user_name
      ,bib.interface_code
  FROM bne_integrators_vl biv
      ,bne_interfaces_b   bib
 WHERE upper(user_name) like 'XXAP%' --'XXAP Supplier Update WebADI'
   AND bib.integrator_code = biv.integrator_code;

--Script to Delete an Interface
DECLARE
   vn_number   NUMBER;
BEGIN
   vn_number := bne_integrator_utils.delete_interface
                (p_application_id => 200,
                p_interface_code  => 'XXSUP_XINTG_INTF1');
               
   DBMS_OUTPUT.put_line ('WebADI Interface has been Deleted '||vn_number);
   COMMIT;
   --
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('Error: '||sqlerrm);
      ROLLBACK;
END;
/


--Script to Delete an Integrator
DECLARE
   vn_number number:=0;
BEGIN
   vn_number:= bne_integrator_utils.delete_integrator
               (p_application_id => 200,
               p_integrator_code => 'XXSUP_XINTG');
              
   dbms_output.put_line('WebADI Integrator has been Deleted : '||vn_number);
   COMMIT;
   --
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('Error: '||sqlerrm);
      ROLLBACK;
END;
/