Skip to main content


Showing posts from February, 2014

Query to find Operating Unit, Business Group and Legal Entity Information

SELECT   DISTINCT   hrl . country ,                  hroutl_bg . name              bg ,                  hroutl_bg . organization_id ,                  lep . legal_entity_id ,                  lep . name                    legal_entity ,                  hroutl_ou . name              ou_name ,                  hroutl_ou . organization_id   org_id ,                  hrl . location_id ,                  hrl . location_code ,                  glev . flex_segment_value FROM     apps . xle_entity_profiles   lep ,         apps . xle_registrations   reg ,         apps . hr_locations_all   hrl ,         apps . hz_parties   hzp ,         apps . fnd_territories_vl   ter ,         apps . hr_operating_units   hro ,         apps . hr_all_organization_units_tl   hroutl_bg ,         apps . hr_all_organization_units_tl   hroutl_ou ,         hr_organization_units   gloperatingunitseo ,         apps . gl_legal_entities_bsvs   glev WHERE    lep . transacting_entity_flag   =   'Y'         AND   l

API to delete the values in a Value set in AOL

DECLARE    l_err_msg   VARCHAR2 (500) := NULL;    CURSOR c1    IS       SELECT ffv.flex_value_id, ffv.flex_value         FROM fnd_flex_value_sets ffvs,              fnd_flex_values ffv,              fnd_flex_values_tl ffvt        WHERE     flex_value_set_name = 'TEST_VALUESET' -- Value Set Name              AND ffv.flex_value_set_id = ffvs.flex_value_set_id              AND ffvt.flex_value_id = ffv.flex_value_id              AND ffvs.flex_value_set_id = ffv.flex_value_set_id              AND ffvt.language = 'US'              AND ffv.enabled_flag = 'Y'              AND ffv.summary_flag = 'N'; BEGIN    FOR i IN c1    LOOP       fnd_flex_values_pkg.delete_row (i.flex_value_id);       COMMIT;       DBMS_OUTPUT.put_line (i.flex_value_id || ' Deleted  Successfully !!!!');    END LOOP; EXCEPTION    WHEN OTHERS    THEN       l_err_msg := SQLERRM;       DBMS_OUTPUT.put_line ('Exception: ' || l_err_msg); END;

Query to find PO Expense Account Rules Details

SELECT   ( SELECT   name          FROM     apps . hr_operating_units          WHERE    organization_id   =   org_id )   OU_NAME ,         rule_type ,         rule_value ,         segment_num ,         segment_name ,         segment_value FROM     apps . po_rule_expense_accounts_v  

Predefined Workflow Activities

All the activities calls the PL/SQL procedure: And/Or Activities And   WF_STANDARD.ANDJOIN OR    WF_STANDARD.ORJOIN Comparison Activities Compare Date/Compare Number/Compare Text  WF_STANDARD.COMPARE Compare Execution Time Activity WF_STANDARD.COMPAREEXECUTIONTIME Wait Activity WF_STANDARD.WAIT Block Activity The Block activity lets you pause a process until some external program or manual step completes and makes a call to the CompleteActivity Workflow Engine API. WF_STANDARD.BLOCK Defer Thread Activity The Defer Thread activity defers the subsequent process thread to the background queue without requiring you to change the cost of each activity in that thread to a value above the Workflow Engine threshold. WF_STANDARD.DEFER Launch Process Activity The Launch Process activity lets you launch another workflow process from the current process.  WF_STANDARD.LAUNCHPROCESS. Loop Counter Activity: WF_STANDARD.LOOPCOUNTER Notify Act

Query to find iProcurement Smart Forms Details

SELECT   template_name ,           ( SELECT   name            FROM     apps . hr_organization_units   ood            WHERE    ood . organization_id   =   pnta . org_id )   OU_NAME ,           currency_code ,           unit_of_measure_code   FROM     apps . por_noncat_templates_all_vl   pnta   WHERE    template_name   <>   'New'  

How to End Date a Responsibility using API

We can end date a responsibility which is already assigned to all users / for a single user using the API  fnd_user_pkg.delresp DECLARE    v_user_name                   VARCHAR2 (100) := NULL;    v_application_name        VARCHAR2 (100) := NULL;    v_responsibility_key        VARCHAR2 (100) := NULL;    v_security_group              VARCHAR2 (100) := NULL; CURSOR xx_iexpenses_cursor IS SELECT             fu.user_name,        fa.application_short_name,        frv.responsibility_key,        (select security_group_key from        apps.fnd_security_groups fsg        where fsg.security_group_id = furgd.security_group_id) Security_Group FROM apps.fnd_user fu,   apps.fnd_user_resp_groups_direct furgd,   apps.fnd_responsibility_vl frv,   apps. fnd_application fa WHERE fu.user_id = furgd.user_id AND fa.application_id = frv.application_id AND furgd.responsibility_id          = frv.responsibility_id AND furgd.end_date                  IS NULL AND

How to Remove End Date on Responsibility using API

The removing of end date from a responsibility which is already assigned to a user, can be done using the API  fnd_user_resp_groups_api Sample Procedure for removing end date from Responsibilities given to Users : -------------------------------------------------------------------------------------------- DECLARE p_user_name VARCHAR2 (50) := 'XX12345'; p_resp_name VARCHAR2 (50) := 'Order Management Super User'; v_user_id NUMBER (10) := 0; v_responsibility_id NUMBER (10) := 0; v_application_id NUMBER (10) := 0; BEGIN BEGIN SELECT user_id INTO v_user_id FROM fnd_user WHERE UPPER (user_name) = UPPER (p_user_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('User not found'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error finding User.'); RAISE; END; BEGIN SELECT application_id, responsibility_id INTO v_application_id, v_responsibility_id FROM fnd_responsibility_vl WHERE UPPER (responsibility_name) = U

Query to provide you the values on all levels of a specific profile.

  SELECT     SUBSTR(e.profile_option_name,1,25) INTERNAL_NAME,     SUBSTR(pot.user_profile_option_name,1,60) NAME_IN_FORMS,     DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',     10004,'User',10005,'Server',10007,'Server+Resp',a.level_id) LEVELl,     DECODE(a.level_id,10001,'Site',10002,c.application_short_name,     10003,b.responsibility_name,10004,d.user_name,10005,n.node_name,     10007,m.node_name||' + '||b.responsibility_name,a.level_id) LEVEL_VALUE,     NVL(a.profile_option_value,'Is Null') VALUE,     to_char(a.last_update_date, 'DD-MON-YYYY HH24:MI') LAST_UPDATE_DATE,     dd.USER_NAME LAST_UPDATE_USER FROM     applsys.fnd_profile_option_values a,     applsys.fnd_responsibility_tl b,     applsys.fnd_application c,     applsys.fnd_user d,     applsys.fnd_profile_options e,     applsys.fnd_nodes n,     applsys.fnd_nodes m,     applsys.fnd_respons

Common Issues in Oracle iExpenses due to incorrect setups -

1.    Manager approves the expense report but report gets escalated to next Manager for approval:          a.    This happens if the Manger for the Employee doesn't have approval authority on                     the cost center to which employee belongs.Setup the approval limit for the Manager                on the correct cost center.          b.    Employee submits the Expense report not to his direct manager but to another                         Manager who  doesn't have approval authority. Verify the Manager at Employee                     record and Withdraw the expense report, submit to correct Manager. 2.    Employee gets the error while submitting the expense report to a Manager “Approver not found”         a.    Verify if the Manager employee record is active and its not end dated.         b.    Verify if the Manager has Oracle User with iExpenses responsibility.         c.    Verify if the Oracle User record is associated with correct Employee record

How to Submit Expense Reports without Original Employee's Approval

This can be accomplished by editing a single node in the workflow  definition for Expense Report Approval (Item Type APEXP). 1.  Open workflow builder and connect to your database. File=>Open=>Database.      Enter required database information in this form. 2.  Select the "AP Expense Report" Item Type from the resulting LOV, then use     the Arrow button to move it into workflow builder and select OK button. 3.  Set workflow builder security to allow modification of the seeded     workflow.  Select Help=>About Workflow builder. Change the Access Level     to 0 and check the box for "Allow modifications of customized objects",     then select the OK button. 4.  Open the Processes and select "Third Party Expense Report". 5.  Right click on the node "Employee Approval Required" and select Properties. 6.  Click on the Node Attribute tab for  "Employee Approval Required" .  7.  Click on the ro