WebADI - Delete Integrator and Interface through API

Deleting interface through application and trying to create the same name, It will not allow to recreate the same interface name, for that we need to delete the interface through API

-- 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 '%XXAK%'

    AND bib.integrator_code = biv.integrator_code ;

--Delete an Intergrator

DECLARE

   vn_number number:=0;

BEGIN

   vn_number:= bne_integrator_utils.delete_integrator

               (p_application_id => 20003,

                p_integrator_code => 'XXAKTESTUPADI_XINTG');

               

   dbms_output.put_line(' ADI Deleted : '||vn_number);

   COMMIT;

   --

EXCEPTION  

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END

/

--Delete an Interface

DECLARE

   vn_number   NUMBER;

BEGIN

   vn_number := bne_integrator_utils.delete_interface

                (p_application_id => 20003,

        p_interface_code  => 'XXAKTESTUPADI_XINTG_INTF1');

                

   DBMS_OUTPUT.put_line ('ADI Interface Deleted '||vn_number);

   COMMIT;

   --

EXCEPTION  

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END;

/

Concurrent program attached request Group - Query

SELECT FRT.RESPONSIBILITY_NAME,

       FRG.REQUEST_GROUP_NAME,

       FRGU.REQUEST_UNIT_TYPE,

       FRGU.REQUEST_UNIT_ID,

       FCPT.USER_CONCURRENT_PROGRAM_NAME

  FROM FND_RESPONSIBILITY         FR,

       FND_RESPONSIBILITY_TL      FRT,

       FND_REQUEST_GROUPS         FRG,

       FND_REQUEST_GROUP_UNITS    FRGU,

       FND_CONCURRENT_PROGRAMS_TL FCPT

 WHERE FRT.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID

   AND FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID

   AND FRGU.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID

   AND FCPT.CONCURRENT_PROGRAM_ID = FRGU.REQUEST_UNIT_ID

   AND FRT.LANGUAGE = USERENV('LANG')

   AND FCPT.LANGUAGE = USERENV('LANG')

   AND FCPT.USER_CONCURRENT_PROGRAM_NAME =

       'PO Changes'

 ORDER BY 1, 2, 3, 4

Concurrent Program Parameters - include ValueSets Query

           SELECT DISTINCT fcpl.user_concurrent_program_name "Concurrent Program Name",

                fcp.concurrent_program_name "Short Name"                                 ,

                fat.application_name                                                     ,

                fl.meaning execution_method                                              ,

                fe.execution_file_name                                                   ,

                fcp.output_file_type                                                     ,

                fdfcuv.column_seq_num "Column Seq Number"                                ,

                fdfcuv.end_user_column_name "Parameter Name"                             ,

                fdfcuv.form_left_prompt "Prompt"                                         ,

                fdfcuv.enabled_flag " Enabled Flag"                                      ,

                fdfcuv.required_flag "Required Flag"                                     ,

                fdfcuv.display_flag "Display Flag"                                       ,

                fdfcuv.flex_value_set_id "Value Set Id"                                  ,

                ffvs.flex_value_set_name "Value Set Name"                                ,

                flv.meaning "Default Type"                                               ,

                fdfcuv.default_value "Default Value"

                 FROM fnd_concurrent_programs fcp ,

                fnd_concurrent_programs_tl fcpl   ,

                fnd_descr_flex_col_usage_vl fdfcuv,

                fnd_flex_value_sets ffvs          ,

                fnd_lookup_values flv             ,

                fnd_lookups fl                    ,

                fnd_executables fe                ,

                fnd_executables_tl fet            ,

                fnd_application_tl fat

                WHERE 1                     = 1

              AND fcp.concurrent_program_id = fcpl.concurrent_program_id

              AND fcp.enabled_flag          = 'Y'

              AND fdfcuv.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name

              AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

              AND flv.lookup_type(+)     = 'FLEX_DEFAULT_TYPE'

              AND flv.lookup_code(+)     = fdfcuv.default_type

              AND fcpl.LANGUAGE          = 'US'

              AND flv.LANGUAGE(+)        = 'US'

              AND fl.lookup_type         ='CP_EXECUTION_METHOD_CODE'

              AND fl.lookup_code         =fcp.execution_method_code

              AND fe.executable_id       = fcp.executable_id

              AND fe.executable_id       =fet.executable_id

              AND fet.LANGUAGE           = 'US'

              AND fat.application_id     =fcp.application_id

              AND fat.LANGUAGE           = 'US'

              and upper(fcpl.user_concurrent_program_name) like upper('Vertex Recon - AR to O Series Tax Reconciliation')

              --and upper(fe.execution_file_name) like 'PO_CHNG_PKG%'

              --and fcp.concurrent_program_name=''

              ORDER BY fdfcuv.column_seq_num;

Check Multiple Values in WHERE Clause SQL Query REGEXP_LIKE

-- Created on 9/29/2017 by C_CKANDA

DECLARE

  -- Local variables here

  LN                  INTEGER;

  LV_TRANS_TYPES      VARCHAR2(4000);

  LV_TRANS_TYPE_IDS   VARCHAR2(4000);

  LV_TRANS_TYPES_IDS  VARCHAR2(4000);

  LV_TRANS_TYPES_IDS1 VARCHAR2(4000);

BEGIN

  -- Test statements here

  SELECT LISTAGG(TRANSACTION_TYPE_ID, '|') WITHIN GROUP(ORDER BY 1)

    INTO LV_TRANS_TYPES_IDS

    FROM MTL_TRANSACTION_TYPES

   WHERE TRANSACTION_TYPE_NAME IN

         (SELECT MEANING

            FROM FND_LOOKUP_VALUES_VL

           WHERE LOOKUP_TYPE = 'LOOKUPNAME');

-- it will return multiple values like 2|38|45|60 

  LV_TRANS_TYPES_IDS := '^(' || LV_TRANS_TYPES_IDS || ')$';

 -- assignment  ^(2|38|45|60)$ passing this values to the below query

  SELECT COUNT(*)

    INTO LN

    FROM MTL_TRANSACTION_TYPES

   WHERE 1 = 1

     AND REGEXP_LIKE(TRANSACTION_TYPE_ID, LV_TRANS_TYPES_IDS);

  DBMS_OUTPUT.PUT_LINE(LN);

END;