Calling a Concurrent Program - Web ADI

Calling a concurrent program can be achieved by defining an ‘importer’ during Integrator definition (Please follow other Web ADI posts for details).

There are three options to define an importer

 

Asynchronous concurrent request:
Here Integrator submits the specified concurrent program after completing the upload and returns the request ID to the user, but does not wait for the import program to complete. This importer type reduces the wait time by deferring the import process. However, you need to verify the concurrent program by your own. it is recommended that you define pre-import rules with this type to validate the data at the time of upload. If the pre-validation fails, then integrator returns an error message.

 Synchronous concurrent request:
Integrator submits the specified concurrent program after completing the upload, waits for the program to complete, and then returns the results to the user. This importer type gives users immediate feedback about the success of the import, but can increase the wait time.

 PL/SQL AP:
It is same as the Synchronous concurrent request, only difference is that it calls an API instead on concurrent request and partly reduces the time by bypassing the concurrent processing queue.

Here, I am using an Asynchronous Concurrent Request importer type.

“Asynchronous concurrent request” importer type allows:
-          Pre-Import Rules    - Optional                          
-          Group Definitions  - Optional
-          Document Row : Interface Attribute Mapping - Optional
-          Concurrent Program Request Submission  - Mandatory
-          Success Message Definition  - Optional
-          Cleanup - Optional

Pre-Import Rules:  These can be used to run a select statement or to run some PL/SQL.  We can define a pre-import rule in order to use a value from the spreadsheet as a parameter value in the concurrent program submission.
 
Rule can be based on :
SQL Query
PL/SQL API Function
PL/SQL API Procedure
Sequence

I am using SQL Query and taking benefit_name from the staging table.

* Please note that it always fetches the first row of the result.

Test your query and ‘Apply’

Provide the reference name so that you can refer this rule.

Group Definition:  It is needed when you want to process the records in a particular group. I am skipping it here.

Document Row : Interface Attribute Mapping: This rule type is required for asynchronous concurrent request importer and optional for other importer types. This is how we identify each row that may have a unique result. I am skipping it here.

Concurrent Program Request Submission - Specifies the concurrent program to perform the import. This rule type is required only for an asynchronous or synchronous importer type.
Select the specified concurrent program name and ‘Apply’

Please provide ‘Reference Name’ and any values you want. Here I am providing default value ‘NEW’ to p_status and the importer rule value to the benefit name.

Value source can be:
Environment Variables
Import
Import Table
Upload Parameters

‘Apply’

Success Message Definition: You can provide a message to display when the import process is submitted successfully.

Program 'XXAK: Content Parameter Test ADI Program' Submitted Successfully With Request ID: $import$.requestid
Please verify the status from concurrent request window.

‘Apply’

Cleanup - Specifies cleanup processing to perform if errors occurred during any of the previous importer rules. I am skipping it here.

‘Submit’

> Now It is time to test,  run the ADI
Insert a new row and ‘upload’

Note the request id returned

Check the request

*Parameter tag and value for import is bne:import=Yes or No
Yes: Program submitted automatically after the upload



Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues

Signing Limit - API

Unfortunately there is no any API available to enter signing limit.

Is There A Method To Enter Signing Limits Other Than Through The Form? (Doc ID 171837.1)
fix: This must be done through the form. There is no supported API or alternate method which can be used.

Through form >
Payable Manager > Employee > Signing Limit

Else,

Do the direct insert to the base table ?
           --
          insert into ap_web_signing_limits_all
                                        (document_type
                                        ,employee_id
                                        ,cost_center
                                        ,signing_limit
                                        ,last_update_date
                                        ,last_updated_by
                                        ,last_update_login
                                        ,creation_date
                                        ,created_by
                                        ,org_id
                                        )
                                  values('APEXP'
                                        ,ln_person_id
                                        ,lc_cost_center
                                        ,ln_signing_limit
                                        ,sysdate
                                        ,fnd_profile.value('USER_ID')
                                        ,fnd_profile.value('LOGIN_ID')
                                        ,sysdate
                                        ,fnd_profile.value('USER_ID')
                                        ,ln_org_id                                     
                                        );         

Ref: http://ebsanil.blogspot.co.uk/2012/09/oracle-employee-signingapproval-limits.html

ORA-20001: The Value Set value XXXXXXXX is invalid for this Value Set

Another error while calling API pay_element_entry_api.create_element_entry. I was passing the correct value but API was returning this error.

Reason: Value set used for the API column is dependent on the session values or using FND_SESSION table

Insert an entry in the fnd_session table before calling the API and then remove the same after the call.



Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues

ORA-20001: The QuickCode is invalid for XXXX_XXXXXXX

I got this error while calling the API pay_element_entry_api.create_element_entry

Reason: Incorrent value passed
Solution: Make sure you are pass the lookup code value not the meaning or description to the entry values also date values in the correct format.



Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues

Query variable is adding extra space in Unix(ksh)

> Set linesize variable to avoid unwanted spaces

Ex:
--
lc_rti_rec=`sqlplus -s $orauser_pwd <<+ENDOFSQL+
set feedback off
set echo off
set pages 0
set heading off
set verify off
set termout off
set feed off
set trimspool on
set trim on
set linesize 100
select fcr.outfile_name
       ||'#'||
       (select instance_name FROM v\\$instance)
  from fnd_concurrent_requests fcr
 where fcr.priority_request_id     = $ln_request_id;
exit
+ENDOFSQL+`

lc_out_file=`echo $lc_rti_rec | cut -f1 -d'#'`
echo "Output Directory>"
echo $lc_out_file
echo " "
lc_instance=`echo $lc_rti_rec | cut -f2 -d'#'`
echo "Instance>"
echo $lc_instance
echo " "
--



Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues

wf_notification/wf_notification_out/wf_deferred Link

--
SELECT n.begin_date,
       n.status,
       n.mail_status,
       n.recipient_role,
       de.def_enq_time,
       de.def_deq_time,
       de.def_state,
       ou.out_enq_time,
       ou.out_deq_time,
       ou.out_state
  FROM applsys.wf_notifications n,
       (SELECT d.enq_time def_enq_time,
               d.deq_time def_deq_time,
               TO_NUMBER((SELECT VALUE
                           FROM TABLE(d.user_data.parameter_list)
                          WHERE NAME = 'NOTIFICATION_ID')) d_notification_id,
               msg_state def_state
          FROM applsys.aq$wf_deferred d
         WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send') de,
       (SELECT o.deq_time out_deq_time,
               o.enq_time out_enq_time,
               TO_NUMBER((SELECT str_value
                           FROM TABLE(o.user_data.header.properties)
                          WHERE NAME = 'NOTIFICATION_ID')) o_notification_id,
               msg_state out_state
          FROM applsys.aq$wf_notification_out o) ou
 WHERE n.notification_id = &NOTIFICATION_ID
   AND n.notification_id = de.d_notification_id(+)
   AND n.notification_id = ou.o_notification_id(+)
--
 Ex:
SELECT A.*
   FROM APPLSYS.AQ$WF_NOTIFICATION_OUT A
WHERE A.user_data.get_string_property('NOTIFICATION_ID') = 22048249;

--
wf_notification  package is also very handy

Ex: to get notification body/subject etc.

SELECT wf_notification.getbody(<Notification Id>)
  FROM dual;
--
SELECT wf_notification.getsubject(<Notification Id>)
  FROM dual;

--
Ref: https://me-dba.com/2009/09/10/notification-mailer-troubleshooting-part-ii/

Grade Rate API(s)

Use hr_grade_rate_value_api API to create/update/delete grade rate values

Ex: Update grade rate
--
DECLARE
   CURSOR get_details
   IS
      select pgr.effective_start_date
            ,pgr.effective_end_date
            ,pgr.object_version_number
            ,pgr.value
            ,pgr.maximum
            ,pgr.mid_value
            ,pgr.minimum
            ,pgr.sequence
            ,pgr.grade_rule_id
            ,pgr.currency_code
        from per_grades pg,
             pay_grade_rules_f pgr
       where pg.name = 'AK General|12|Standard XX'
         and pgr.grade_or_spinal_point_id = pg.grade_id  
         and trunc(sysdate) between pgr.effective_start_date  and pgr.effective_end_date
         ;

   l_effective_start_date    DATE   := NULL;
   l_effective_end_date      DATE   := NULL;
   l_grade_rule_id           NUMBER := NULL;
   l_object_version_number   NUMBER := NULL;
   l_err_msg                 VARCHAR2 (500) := NULL;
   l_value                   NUMBER;
   l_mid_value               NUMBER;
   l_max_vlaue               NUMBER;
   l_mim_value               NUMBER;
BEGIN

      FOR i IN get_details    LOOP
         --
         l_object_version_number := i.object_version_number;        
         l_max_vlaue             := 40000;
         l_mim_value             := 30000;
         l_mid_value              := 35000;
         l_value                     := 35000;
         --
         BEGIN
            hr_grade_rate_value_api.update_grade_rate_value (
               p_validate                    => FALSE,
               p_grade_rule_id           => i.grade_rule_id,
               p_effective_date          => TO_DATE ('10-MAY-2014', 'DD-MON-YYYY'),
               p_datetrack_update_mode   => 'UPDATE',--'CORRECTION',
               p_currency_code           => i.currency_code,
               p_maximum                  => l_max_vlaue,
               p_mid_value                => l_mid_value,
               p_minimum                 => l_mim_value,
               p_value                        => l_value,
               p_sequence                  => i.sequence,
               p_object_version_number   => l_object_version_number,
               p_effective_start_date    => l_effective_start_date,
               p_effective_end_date      => l_effective_end_date
            );
            COMMIT;
            DBMS_OUTPUT.put_line ('Grate Rate has been Updated: ' || i.grade_rule_id  );
         EXCEPTION
            WHEN OTHERS THEN
               l_err_msg := SQLERRM;
               DBMS_OUTPUT.put_line ('Inner Exception: ' || l_err_msg);
         END;
      END LOOP;
--
EXCEPTION
   WHEN OTHERS THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;
--


Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues

Running AWR Report

The AWR (Automatic Workload Repository) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

> Please run your required process an hour before running the AWR report.

1. Login into the SQL Plus.

2. Run the following command to generate the report.
SQL > @$ORACLE_HOME/rdbms/admin/awrrpt.sql

3. You will be prompted to enter the report type. Enter report TYPE as HTML.

4. Next, you will be prompted for the number of days you want to see the snapshots. Enter a value of "1" to see snapshots created in today.
NOTE: By default, the Oracle Database generates snapshots once every hour and retains the statistics in the workload repository for 7 days.

5. After the list displays, you are prompted for the beginning and ending snapshot ID for
the workload repository report. Enter a starting ID that corresponds with the approximate time that your concurrent process was started. Enter the ending ID that corresponds with the approximate time that your process finished.

6. Next, accept the default report name or enter a the name as required.

7. Report will be created in the same directory :- $ORACLE_HOME/rdbms/admin




Ref: Metalink Doc ID 748642.1

Not able to view employee type supplier from supplier form

A few setup steps required:

1) Get the  menu attached to the problematic responsibility
    > System Administrator > Security >  Responsibility > Define

2) Navigate to System Administrator > Application > Menu
Query the menu attached to the responsibility

3) Include the function:
Create/Update Employee Supplier Details (POS_HT_SP_EMP_SUPPLIER) with the grant flag checked in the menu of the responsibility for full access to employee suppliers.
OR
View Employee Supplier Details (POS_HT_SP_RO_EMP_SUPPLIER) with the grant flag checked in the menu of the responsibility for read only/inquiry access to employee suppliers.

Alternatively check the grant flag for the function POS_HT_SP_EMP_SUPPLIER in the menu POS_HT_SP_FULL_ACCESS_MENU.
> However then all users that can create standard supplier will also be able to create employee supplier.

4) Bounce the Apache server OR 'Clear All Cache' from Functional Administrator responsibility, so that the Function Security related caches will be cleared and the above changes can take effect.

Ref: Metalink Doc ID 1371295.1