WebADI Web Applications Desktop Integrator

Web Applications Desktop Integrator

Web ADI brings Oracle E-Business suite to the desktop where familiar desktop tool like Excel can be used to create spreadsheet, enter(create) and modify(Update) data in the spreadsheet and finally upload the data into the Oracle Applications with or without validation.

Prerequisite steps to ensure that Web ADI works for you:

Add Responsibility to the user

• System Admin -->Security --> User --> Define
• Assign ‘Desktop Integration Manager’ and 'Desktop Integration' responsibility to your user. This responsibility has the menu ‘Desktop Integration Manager Menu’ and 'Desktop Integration Menu' associated with it.

Set Profile Option

•  System Admin --> Profile --> System
• Set profile option  ‘BNE Allow No Security Rule’ to ‘Yes’  at site level.

Enable Macros for Excel 2007

• Enable macros for Microsoft Excel.

Macros in excel 2007 for web adi(Oracle E-Business Suite)

 Excel 2007:

• Open Start button and select Microsoft Office Excel 2007
• Click the Office Button (top-left button in the window) 
• Click the 'Excel Options' button.
• Click 'Trust Center' link from the menu on the left.
• Click 'Trust Center Settings' button.
• In the 'Trust Center' window click 'Macro Settings' link from the menu on the left.
• Under 'Macro Settings' check ‘Enable all Macros(not recommended; potentially dangerous…’).
• Under 'Developer Macro Settings' check 'Trust Access to the VBA project object model'.
• Click 'OK' button to close the 'Trust Center' window.

Query To get Run Results information

SELECT INITCAP(ppf.last_name
  ||','
  ||ppf.first_name) name,
  ety.element_name,
  to_number(rrv.result_value) commission
FROM per_all_people_f ppf,
  apps.per_assignments_f paf,
  apps.pay_assignment_actions pas,
  apps.pay_payroll_actions ppa,
  apps.pay_run_results rr,
  apps.pay_run_result_values rrv,
  apps.pay_element_types_f ety,
  apps.pay_input_values_f I
WHERE ppf.person_id          = paf.person_id
AND paf.assignment_id        = pas.assignment_id
AND pas.assignment_action_id = rr.assignment_action_id
AND ppa.payroll_action_id    = pas.payroll_action_id
AND rr.element_type_id       = ety.element_type_id
AND i.element_type_id        = ety.element_type_id
AND rrv.run_result_id        = rr.run_result_id
AND rrv.input_value_id       = i.input_value_id
AND sysdate BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND sysdate BETWEEN paf.effective_start_date AND paf.effective_end_date
AND sysdate BETWEEN ety.effective_start_date AND ety.effective_end_date
AND sysdate BETWEEN I.effective_start_date AND I.effective_end_date
AND i.name           = 'Pay Value'
AND ety.element_name = 'Salary' ;

To Reset Oracle Apps User id and Password From Backend

DECLARE
  v_user_name    VARCHAR2(30):=upper('HCM'); --HCM is username of my oracle Apps.
  v_new_password VARCHAR2(30):='welcome1';
  v_status       BOOLEAN;
BEGIN
  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, newpassword => v_new_password );
  IF v_status =true THEN
    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
  END IF;
END;
/

Query to Find Oracle Alerts

The following query finds all enabled custom alerts. You can comment out the very last two lines (enabled_flag and created_by) to display all both enabled and disabled alerts.



SELECT created_by,
  enabled_flag,
  application_id,
  alert_id,
  alert_name,
  start_date_active,
  description,
  sql_statement_text
FROM apps.alr_alerts
WHERE 1          =1
AND created_by  <> 1   -- Shows only custom alerts
AND enabled_flag = 'Y';-- Shows only enabled alerts

To find Screen Entry Value from Element and Element Link Definition

SELECT peev.screen_entry_value
FROM pay_element_types_f pet,
pay_input_values_f piv,
--pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE 1 =1
and pet.element_type_id = piv.element_type_id
--AND piv.element_type_id = pel.element_type_id
-- AND pel.element_link_id = pee.element_link_id
and pee.element_entry_id=peev.element_entry_id
and pet.element_type_id = pee.element_type_id
and piv.input_value_id=peev.input_value_id
AND pee.assignment_id = p_assignment_id
AND piv.name = p_input_name
AND pet.element_name = p_element_name
AND p_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
and p_start_date between piv.effective_start_date and piv.effective_end_date
--AND p_start_date BETWEEN pel.effective_start_date AND pel.effective_end_date
AND p_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND p_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date;

Reusable Queries

Query to find the DFF fields atached to a context_code  
------------------------------------------------------  
 
select
a.descriptive_flex_context_code context_code,  
a.end_user_column_name,  
a.application_column_name ,  
a.flex_value_set_id VS_id, b.flex_value_set_name,a.enabled_flag,  
a.required_flag,b.validation_type,a.display_flag,a.display_size  
from apps.fnd_descr_flex_col_usage_vl a, apps.fnd_flex_value_sets b  
where a.descriptive_flex_context_code='XXCHR_COMM_DRAWS'
and a.descriptive_flexfield_name='Assignment Developer DF'
and b.flex_value_set_id(+)=a.flex_value_set_id  
   
 
******************************************************************************************************************************************************  
 
Query for getting Responsibility_Key  
-------------------------------------  
 
SELECT  r.responsibility_id,  
  SUBSTR(USER_NAME,1,25) "User Name",  
  SUBSTR(APPLICATION_NAME,1,45) "Application Name",  
  responsibility_key,  
  SUBSTR(RESPONSIBILITY_NAME,1,60) "Responsibility Name",  
  SECURITY_GROUP_NAME,  
  GREATEST(U.START_DATE, UR.START_DATE, R.START_DATE) START_DATE,  
  LEAST(NVL(U.END_DATE,  NVL(UR.END_DATE, R.END_DATE)),  
  NVL(UR.END_DATE, NVL(U.END_DATE,  R.END_DATE)),  
  NVL(R.END_DATE,  NVL(U.END_DATE,  UR.END_DATE)))    END_DATE  
FROM  
  FND_USER                                              U,  
  FND_USER_RESP_GROUPS                                  UR,  
  FND_RESPONSIBILITY_VL                                 R,  
  FND_APPLICATION_VL                                    A,  
  FND_SECURITY_GROUPS_VL                                S  
WHERE  
      A.APPLICATION_ID                = R.APPLICATION_ID  
  AND U.USER_ID                       = UR.USER_ID  
  AND R.APPLICATION_ID                = UR.RESPONSIBILITY_APPLICATION_ID  
  AND R.RESPONSIBILITY_ID           = UR.RESPONSIBILITY_ID  
  AND UR.START_DATE                 <= SYSDATE  
  AND NVL(UR.END_DATE, SYSDATE + 1) >  SYSDATE  
  AND U.START_DATE                  <= SYSDATE  
  AND NVL(U.END_DATE, SYSDATE + 1)  > SYSDATE  
  AND R.START_DATE                  <= SYSDATE  
  AND NVL(R.END_DATE, SYSDATE + 1)  > SYSDATE  
  AND UR.SECURITY_GROUP_ID          = S.SECURITY_GROUP_ID  
  and user_name like 'ARMUTHUS'
ORDER BY  
  SUBSTR(RESPONSIBILITY_NAME,1,8),  
  USER_NAME  
 
******************************************************************************************************************************************************  
 
Query to get the MENU NAME
---------------------------  
 
SELECT DISTINCT fm.menu_id,fm.menu_name  
FROM
fnd_menus fm,  
FND_MENU_ENTRIES fme,  
fnd_responsibility fr,  
fnd_responsibility_tl frt  
WHERE fm.menu_id = fme.menu_id  
AND fr.menu_id = fme.menu_id  
AND fr.responsibility_id = frt.responsibility_id  
AND fr.responsibility_id  = frt.responsibility_id  
AND responsibility_name = '&Responsibillity' ;  
 
***************************************************************************************************************************************************************************  
 
Query to check the Request Group of a concurrent program  
---------------------------------------------------------  
 
select d.RESPONSIBILITY_NAME,a.REQUEST_GROUP_NAME, e.USER_CONCURRENT_PROGRAM_NAME  
from fnd_request_groups a,  
     FND_REQUEST_GROUP_UNITS b,  
     fnd_responsibility c,  
     fnd_responsibility_tl d,  
     FND_CONCURRENT_PROGRAMS_TL e  
where a.REQUEST_GROUP_ID=b.REQUEST_GROUP_ID  
and c.RESPONSIBILITY_ID=d.RESPONSIBILITY_ID  
and c.REQUEST_GROUP_ID=b.REQUEST_GROUP_ID  
and d.language='US'  
and e.language='US'
and b.request_unit_id=e.concurrent_program_id  
and e.USER_CONCURRENT_PROGRAM_NAME like 'Cisco%JP Morgan%Inbound%'
 
******************************************************************************************************************************************************  
 
Query to check the Employees having particular elements in a given pay period  
------------------------------------------------------------------------------  
 
SELECT DISTINCT papf.employee_number,  
       papf.full_name,  
       petf.element_name,  
       peef.effective_start_date,  
       pivf.name input_field,  
       peevf.screen_entry_value  input_value,  
       peef.creation_date,  
       peef.last_update_date  
  from per_all_people_f papf,  
       per_all_assignments_f paaf,  
       pay_element_entries_f peef,  
       pay_element_types_f petf,  
       pay_element_entry_values_f peevf,  
       pay_input_values_f pivf,  
       xxchr_ib_jpm_401k_stage xs  
 where papf.person_id = paaf.person_id  
   and papf.business_group_id=113  
   and papf.current_employee_flag = 'Y'
   and paaf.assignment_id = peef.assignment_id  
   and peef.element_type_id = petf.element_type_id  
   and peef.element_entry_id = peevf.element_entry_id  
   and TO_DATE('GIVEN_DATE') between peevf.effective_start_date and peevf.effective_end_date  
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date  
   and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date  
   and TO_DATE('GIVEN_DATE') between peef.effective_start_date and peef.effective_end_date  
   and trunc(sysdate) between petf.effective_start_date and petf.effective_end_date  
   AND petf.element_type_id = pivf.element_type_id  
   AND peef.element_type_id = pivf.element_type_id  
   AND pivf.input_value_id = peevf.input_value_id  
   AND trunc(sysdate) BETWEEN pivf.effective_start_date  
                               AND pivf.effective_end_date  
   AND UPPER (pivf.NAME) IN ( 'AMOUNT', 'LOAN NUMBER', 'PERCENT')  
   and  REPLACE(papf.national_identifier,'-') = TRIM (xs.ssn)  
   AND xs.status = 'P'
   AND petf.element_name IN (  
                             'Element Name1', 'Element Name2', 'Element Name3'
                            )  
   AND peevf.screen_entry_value IS NOT NULL
   AND (  
            TRUNC(peef.creation_date) = TRUNC(SYSDATE)    
            OR
            TRUNC(peef.last_update_date) = TRUNC(SYSDATE)  
       )        
ORDER BY papf.employee_number  
 
******************************************************************************************************************************************************  
Query to pick the employee # , his elements and the effective date of the element entry  
----------------------------------------------------------------------------------------  
 
SELECT papf.employee_number, papf.full_name, petf.element_name, peef.effective_start_date  
  FROM per_all_people_f papf, per_all_assignments_f paaf, pay_element_entries_f peef, pay_element_types_f petf, per_business_groups pb  
 WHERE papf.person_id = paaf.person_id  
   AND papf.business_group_id=pb.business_group_id  
   AND papf.current_employee_flag = 'Y'
   AND paaf.assignment_id = peef.assignment_id  
   AND peef.element_type_id = petf.element_type_id  
   AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date  
   AND trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date  
   AND trunc(sysdate) between peef.effective_start_date and peef.effective_end_date  
   AND trunc(sysdate) between petf.effective_start_date and petf.effective_end_date  
   AND petf.element_name IN (  
                'Element Name1', 'Element Name2', 'Element Name3'
                            )  
 
******************************************************************************************************************************************************  
 
 
Query to pull values from the User Defined Tables (UDT)  
--------------------------------------------------------  
 
         SELECT pui.VALUE,puc.user_column_name,pur.row_low_range_or_name  
           FROM pay_user_tables put,  
                pay_user_rows_f pur,  
                pay_user_columns puc,  
                pay_user_column_instances_f pui  
          WHERE put.user_table_name = '<UDT_NAME>'
            AND pur.user_table_id = put.user_table_id  
            AND pui.user_column_id = puc.user_column_id  
            AND pui.user_row_id = pur.user_row_id  
            AND SYSDATE BETWEEN pur.effective_start_date  
                            AND pur.effective_end_date  
            AND SYSDATE BETWEEN pui.effective_start_date  
                            AND pui.effective_end_date  
            AND puc.user_column_name = '<COLUMN_NAME>'
            AND puc.user_table_id = put.user_table_id