Notification Mailer Keeps Sending the same closed Notification

The probable reason is the incorrect receipient's incorrect email address.

The SMTP server does not handle invalid email address it should be either null of valid email address

Solution

Go to System Administrator > Workflow > Oracle Applications Manager > Workflow Manager

Click on the 'Notification Mailers' > Workflow Notification Mailer

Edit > Advanced >
    At step 6 create a new TAG with 'Pattern' name as the 'Subject of the Notification' with Action value of 'Ignore'

Ex: Undeliverable             Ignore

Showing Custom Error Message - Web ADI

There are two ways to do this.

1>  Simplest way is to change the 'API Returns' attribute to 'FND Message Code' and use raise_application_errro to raise errors

 Desktop Integration Manager > Manage Integrators
  >> Search the Integrator and go to the 'Interfaces' screen and select the Interface
     >> Change the 'API Returns' to 'FND Message Code' if it is null or 'Error Message'
 
>> Now change the exception section of the related API packaged procedure  

when ex_web_adi1 then
   raise_application_error(-20001,'Web ADI Error Message: '||lc_emp_number);

2> Change the exception section of the related API packaged procedure as below

when ex_web_adi1 then
   lc_err_msg := 'Web ADI Error Message: '||lc_emp_number;
   fnd_message.set_name('XXCMN', lc_err_msg);
   --fnd_message.set_token('ERROR_MESSAGE', lc_err_msg);
   lc_mesg := fnd_message.get;     
   raise_application_error(-20001, lc_mesg);

Deployment - Web ADI

We will use the FNDLOAD utility to deploy the Web ADI to other instances.

> We just need to deploy the 'Integrator' because it also includes the realted content,layout and mappings and then the related Form Function (R12)

>> Integrator

select * --integrator_code, application_id,user_name
  from bne_integrators_vl vl
 where user_name like 'XXAK%%'
   and integrator_code like 'XXAK%';
 
fndload apps/<apps password> 0 y download $bne_top/patch/115/import/bneintegrator.lct xxaktestadi_xintg.ldt bne_integrators integrator_asn="XXAKTEST_ADI_XINTG" integrator_code="XXAKTESTADI_XINTG"

fndload apps/<apps password> 0 y upload $bne_top/patch/115/import/bneintegrator.lct xxaktestadi_xintg.ldt

>> Form Function

select * --function_name
  from fnd_form_functions_vl
 where function_name like 'XXAK%';
 
fndload apps/<apps password> 0 y download $fnd_top/patch/115/import/afsload.lct xxaktestadi_func.ldt function function_name="XXAKTESTADI"
 
fndload apps/<apps password> 0 y upload $fnd_top/patch/115/import/afsload.lct xxaktestadi_func.ldt - warning=yes upload_mode=replace custom_mode=force

Please use below scripts if you have to deploy contents/layouts/mappings

>> Content 

select * --content_code
  from bne_content_cols_vl
 where content_code like '%XXAK%';

fndload apps/<apps password> 0 y download $bne_top/patch/115/import/bnecont.lct xxaktestadi_cnt2.ldt bne_contents content_asn="XXAK" content_code="XXAKTESTADI_CNT2"

fndload apps/<apps password> 0 y upload $bne_top/patch/115/import/bnecont.lct xxaktestadi_cnt2.ldt

>> Layout

select *--LAYOUT_CODE
  from bne_layouts_vl vl
 where integrator_code like 'XXAK%';

fndload apps/<apps password> 0 y download $bne_top/patch/115/import/bnelay.lct xxaktestadi_lay.ldt bne_layouts layout_asn="XXAK" layout_code="XXAKTESTADI"

fndload apps/<apps password> 0 y upload $bne_top/patch/115/import/bnelay.lct xxaktestadi_lay.ldt


>> Mapping

select * --mapping_code, integrator_code
  from bne_mappings_vl
 where mapping_code like 'XXAK%';

fndload apps/<apps password> 0 y download $bne_top/patch/115/import/bnemap.lct xxaktestadi_map.ldt bne_mappings mapping_asn="XXAK" mapping_code="XXAKTESTADI"

fndload apps/<apps password> 0 y upload $bne_top/patch/115/import/bnemap.lct xxaktestadi_map.ldt

Create Payment Method - HRMS APIs

--
--Possible Error: ORA-20001: HR_7348_ASSIGNMENT_INVALID
--Check the date format of effective_date
--
declare
   ln_method_id         pay_personal_payment_methods_f.personal_payment_method_id%type;
   ln_ext_acc_id        pay_external_accounts.external_account_id%type;
   ln_obj_ver_num       pay_personal_payment_methods_f.object_version_number%type;
   lc_org_pay_method    pay_org_payment_methods_f_tl.org_payment_method_name%type;
   ld_eff_date          date := to_date('01-OCT-2011','DD-MON-YYYY');
   ld_eff_start_date    date;
   ld_eff_end_date      date;
   ln_comment_id        number;
   ln_assignment_id     number;
   ln_org_pay_method_id number;  
   lc_employee_num      varchar2(20) := '223344';
   lc_territory_code    varchar2(60) := 'GB';
   lc_bank_name         varchar2(60) := 'XX';
   lc_bank_branch       varchar2(60) ;
   lc_sort_code         varchar2(60) := '900011';
   lc_account_num       varchar2(60) := '91889999';
   lc_account_name      varchar2(60) := 'Mr A Kumar' ;
   lc_account_type      varchar2(60) := '0' ;
   lc_bld_s_account_num varchar2(60) := null;
   lc_branch_loc        varchar2(60) ;
begin
   --
    select assignment_id
          ,pay.payroll_name
      into ln_assignment_id
          ,lc_org_pay_method
      from per_all_assignments_f ass
          ,per_all_people_f      per
          ,pay_all_payrolls_f    pay
     where ass.person_id = per.person_id
       and per.employee_number = lc_employee_num
       and trunc(sysdate) between ass.effective_start_date and ass.effective_end_date
       and trunc(sysdate) between per.effective_start_date and per.effective_end_date
       and pay.payroll_id = ass.payroll_id;
     
    
    select org_payment_method_id
      into ln_org_pay_method_id
      from pay_org_payment_methods_f_tl
     where org_payment_method_name = lc_org_pay_method
       and language = 'US';
     
    -- Create Employee Payment Method
    -- --------------------------------------------------
    hr_personal_pay_method_api.create_personal_pay_method
    (-- Input Parameters
          p_effective_date                                      => ld_eff_date,
          p_assignment_id                                      => ln_assignment_id,
          p_org_payment_method_id                     => ln_org_pay_method_id,
          p_priority                                                 => 1,
          p_percentage                                            => 100,
          p_territory_code                                       => lc_territory_code,
          p_segment1                                              => lc_bank_name,
          p_segment2                                              => lc_bank_branch,
          p_segment3                                              => lc_sort_code,
          p_segment4                                              => lc_account_num,
          p_segment5                                              => lc_account_name,
          p_segment6                                              => lc_account_type,
          p_segment7                                              => lc_bld_s_account_num,
          p_segment8                                              => lc_branch_loc,
          -- Output parameters
          p_personal_payment_method_id             => ln_method_id,
          p_external_account_id                             => ln_ext_acc_id,
          p_object_version_number                        => ln_obj_ver_num,
          p_effective_start_date                              => ld_eff_start_date,
          p_effective_end_date                               => ld_eff_end_date,
          p_comment_id                                         => ln_comment_id
          );
     commit;
    dbms_output.put_line('Done');      
--

exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
      rollback;
end;
--

Create/Update Person extra info - HRMS APIs

--
declare
--
  cursor cur_eit_details
  is
  select per.person_id
        ,'EIT_VALUE' eit_val
        ,to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') begin_date
        ,null end_date
    from per_all_people_f per
   where per.employee_number = '123456'
     and trunc(sysdate) between per.effective_start_date and per.effective_end_date
     ;
  lc_information_type  varchar2(20) := 'XXAK_TEST_EIT';
  lc_information_cat    varchar2(20) := 'XXAK_TEST_EIT';
  ln_per_extra_info_id number;
  ln_ovn_pei                number;
--
begin
--
   -- Please use your own business logic, this is a basic case
   for rec_eit_details in cur_eit_details loop
   --     
      begin
      --
         select ppei.person_extra_info_id
               ,ppei.object_version_number
           into ln_per_extra_info_id
               ,ln_ovn_pei
           from per_people_extra_info           ppei
          where ppei.person_id                    = rec_eit_details.person_id
            and ppei.information_type          = lc_information_type
            and ppei.pei_information_category  = lc_information_cat
          ;
         --
         begin
            -- UPDATE
            hr_person_extra_info_api.update_person_extra_info
                      ( p_validate                              =>  false
                      , p_person_extra_info_id           =>  ln_per_extra_info_id
                      -- In / Out
                      , p_object_version_number       =>  ln_ovn_pei
                      -- In
                      , p_pei_information_category   =>  lc_information_cat
                      , p_pei_information1           =>  rec_eit_details.eit_val
                      , p_pei_information2           =>  rec_eit_details.begin_date
                      , p_pei_information3           =>  rec_eit_details.end_date
                     );               
            dbms_output.put_line('EIT Updated ');
            commit;  
            --   
         exception
            when others then
               dbms_output.put_line('Update Failed '||sqlerrm);
               rollback;
         end;
      --
      exception
         when no_data_found then
         --
            begin
               -- CREATE
               ln_per_extra_info_id := null;
               ln_ovn_pei           := null;
               --
               hr_person_extra_info_api.create_person_extra_info
                      ( p_validate                               =>  false
                      , p_person_id                            =>  rec_eit_details.person_id
                      , p_information_type                =>  lc_information_type
                      , p_pei_information_category  =>  lc_information_cat
                      , p_pei_information1                =>  rec_eit_details.eit_val
                      , p_pei_information2                =>  rec_eit_details.begin_date
                      , p_pei_information3                =>  rec_eit_details.end_date
                       -- Out
                      , p_person_extra_info_id         =>  ln_per_extra_info_id
                      , p_object_version_number     =>  ln_ovn_pei
                      );
               --                                  
               dbms_output.put_line('EIT Created '||ln_per_extra_info_id);               
               commit;
               --
            exception
               when others then
                  dbms_output.put_line('Creation Failed '||sqlerrm);
                  rollback;                 
                  --
            end;
           --
        when others then
           dbms_output.put_line('Error: Selecting Person Extra Info '||sqlerrm);
      end;
      -- 
   end loop;
--
   dbms_output.put_line('Done!');
--
exception
when others    then
   dbms_output.put_line('Error: '||sqlerrm);
   rollback;
end;
--

Default Parameters - Web ADI

Sometimes you need to default parameters for end users while creating the document.

Like Integrator/Viewer/Reporting/Layout/Content etc.

There are some parameters you need to set achieve this.

Go to the respective form function

Navigation: Application Developer > Application > Function > ‘Form’ Tab > Parameters

Frequently used parameters

> &bne:validation=Y this used to be True or False

> &bne:rows=all Flagged rows : The parameters are FLAGGED or ALL it may be case sensitive

> &bne:viewer=BNE:EXCEL2010

> &bne:content

    >> Content value

select application_id || ':' || content_code content

  from bne_contents_tl

 where user_name like 'XXAK%'

   and language = 'US'

>&bne:integrator

>&bne:layout

    >> Integrator and layout Values

select integrator_app_id || ':' || integrator_code integrator

     , b.application_id || ':' || a.layout_code layouts

  from bne_layouts_tl a

      ,bne_layouts_b  b

 where user_name like 'XXAK%'

   and a.layout_code = b.layout_code

   and language = 'US'

>&bne:allow  'Yes' 'No' -- used for 'Upload Parameter' window to appear or not 

>&bne:import=N / Y : This will set 'Automatically submit Transaction import' to No or Yes when uploading transactions to the interface.

>&bne:import=Y/N : Whether you import automatically or not

>&bne:importPost=Y/N Y : Indicates  'Posting errors to suspense' should be set.

>&bne:importSummary=Y/N : Import in Summary Mode or full

>&bne:importFlex=IMPORTVALIDATION : Description Flexfield validation

Example: bne:page=BneCreateDoc&bne:viewer=BNE:EXCEL2010&bne:reporting=N&bne:integrator=20003:XXAKTESTADI_XINTG&bne:layout=20003:XXAKTESTADI&bne:content=20003:XXAKTESTADI_CNT2&bne:rows=ALL

> Once these values are defaulted correctly then set the profile option
"BNE Allow Set Parameters" at the appropriate level to enforce the default values.

> Clear the cache or stop and restart apache to ensure that the change has taken effect

Navigation: Functional Administrator > Core Services > Caching Framework > Global Configuration > Clear All Cache

Web ADI - Errors

Here, I am listing few errors which I came across

Exception Name: oracle.apps.bne.exception.BneFatalException - Error loading class: Log File Bookmark: 236030

Reason: The above error will occur when trying to access "Create Document".
    > Check if there is any space between parameters in the 'Form' tag.

For Parameter details please follow the 'Add a form function and add to specified responsibility menu'  section of this post: Web ADI (Download)

--------------

Exception Name:oracle.jbo.TooManyObjectsException: JBO-25013: Too many objects match the primary key oracle.jbo.Key

Reason: You are using the same name or you have deleted the interface from application using 'Delete' icon but it's metadata still remains in BNE table(s)

Add a new Interface with a different name

OR

Delete the Interface from backend (If it is a custom BNE) : Delete Interface

-----------------
Exception Name: CANNOT EXECUTE SQL STATEMENT - A LOW LEVEL API CALL FAILED

Reason: Field length is greater than 80 characters, amend the select statement.

----------------

For Excel related errors please follow the below post:

Web ADI - Excel Errors

Enable Trace/Debug - Web ADI

Set the following System Profile Options:

Navigation: System Administrator > Profile > System

                    > BNE%Log%

BNE Server Log Filename : Any Name (Ex: WebADI.log)

BNE Server Log Level    : ERROR

BNE Server Log Path     : Provide the full directory path as per your convenient

> Bounce the Apache Server (adstpall.sh)

>Trace/debug statements will keep increasing the file for every run, to avoid excessive growth of the log file select the appropriate 'BNE Server Log Level' options

Options are: REQUIRED | CRITICAL ERROR | ERROR | WARNING | INFORMATION | DETAIL | TRACE

-- Web ADI Profile Values

select a.profile_option_name

      ,a.user_profile_option_name

      ,a.description

      ,c.profile_option_value

  from fnd_profile_options_tl a

      ,fnd_profile_options b

      ,fnd_profile_option_values c

where b.profile_option_id   = c.profile_option_id

   and a.profile_option_name = b.profile_option_name

   and b.profile_option_name like 'BNE%_LOG%'

   and a.language            = 'US'

;

Custom Web ADI (Download/Upload)

Now we will integrate the download and upload ADIs. We will use the same custom table and packaged procedure used separately in download and upload.

Responsibility: Desktop Integration Manager

> Create Integrator

 XXAK: Test Web ADI

XXAKTESTWEBADI

Check the box for ‘Display in Create Document Page’

Add these three functions

Code:

BNE_ADI_DEFINE_MAPPING

BNE_CREATE_DOCUMENT

BNE_ADI_CREATE_DOCUMENT

Interface type here would be ‘API Procedure’

Package Name: xxak_testadi_pkg

Procedure/Function Name: load_record

API Returns : Error Message

Apply

You can click on ‘Update’ and change prompt and other details required

Create Content

Content type would be ‘SQL Query’

Query : SELECT order_number

      ,start_date

      ,end_date

      ,business_area

      ,batch_number

      ,status

  FROM XXAK_WEB_ADI_DOWNLAOD_TBL

Test Query

Apply

Create Uploader

Uploader: From Template

Select check boxes as below

Skip ‘Create Importer’

Submit

Define Layout

Navigation: Desktop Integrator -> Define Layout -> Select the Integrator

Go > Create

Change the Placement to 'Line'

Next

Apply

Create Mapping

Responsibility: Desktop Integrator

        > Define Mapping

• Mapping between ‘Interface’ and ‘Content’ data

Apply

Add a form function and add to specified responsibility menu

Responsibility: Application Developer

           > Application > Function

Type: SSWA servlet function
HTML Call: BneApplicationService

Parameter: bne:page=BneCreateDoc&bne:integrator=20003:XXAKTESTWEBADI_XINTG&bne:layout=20003:XXAKTESTWEBADI

select integrator_app_id || ':' || integrator_code integrator

     , application_id || ':' || layout_code layouts

  from bne_layouts_b

 where integrator_code like 'XXAK%'

Add this Function to the specified Menu and go to the related responsibility

Create Document