Tuesday 29 March 2016

Restricting Element

Restricting Element Entries using Task flow in HRMS


Customizing Element Entry Screen form in HRMS and attaching it, or a standard form, to an HRMS Task Flow. by step instructions on setting up Task Flows. 

SETTING UP AND ATTACHING FORMS TO TASKFLOWS IN HRMS
--------------------------------------------------- 
Summary of Steps

1.Define Customization Set
2.Define Custom forms
3.Define Task Flow Node
4.Define Task Flow
5.Attach Task Flows to Functions


LIST OF CUSTOMIZABLE FORMS IN HRMS (subject to changing in different releases of HRMS)
-------------------------------------------------------------
- ORGANIZATION
- PERSON
- SPECIAL INFORMATION
- ELEMENT ENTRIES
- EVENT BOOKINGS
- LIST PEOPLE BY SPECIAL INFORMATION
- PEOPLE FOLDER
- ASSIGNMENT FOLDER
- RUN QUICKPAINT REPORT
- DEFINE USER VALUES
- LIST EMPLOYEES BY ELEMENT
- VIEW ELEMENT ENTRY HISTORY FOR EMPLOYEE
- ADJUST BALANCE (Oracle Payroll form)
- PAYROLL AND ASSIGNMENT PROCESSES (Oracle Payroll form)

All of these forms have different types of restrictions, this is detailed in the User Guide.
or  Configuring, Reporting and System Administration in Oracle HRMS. The restrictions will be listed in a drop down during the customization . 


Form Customization

1. From the Navigator -> Security->CustomForm.



2. Select the form you want to customize -- "Element Entry"

3. Enter a NAME for your customized form. This is the name that will be used
   defining the Menu Function, it will not be seen by your users.

4. Enter a STANDARD TITLE for your form. This is the window title which will
   be seen by the users who are able to use the form for data entry and
   editing.

5. Enter a QUERY TITLE for your form. 
   This is the window title which will be seen by the users who have query only
   access to the form.

6. Check the Enabled flag, if you would like the customizations to take effect,
   otherwise the form will behave normally.

SAVE
  
7. Enter the RESTRICTIONS.
      The list can be restricted by
 1.       Customization Set ( I have used in this example)

    2. Element Type
    3. Element

TASK FLOW NODES

Once we have  defined a customized form,  now need to define a
Task Flow Node.
1. From the HRMS Responsibility, navigate to Security -> Task Flow Nodes.
2. Enter the NAME of your Task Flow.
3. In UNIT, the Form is the name of the standard form that you have customized( Element Entry)
4. Select the name of your customized form in CUSTOMIZATION.

DEFINING TASK FLOWS


This section will give you details on how to define a Task Flow, which is the
next step you will have to take in this example.


1. Navigate to Security, Task Flow Definition.
2. Enter the name of your new Task Flow and SAVE the record.
3. Select the Legislation (UNITED ARAB EMIRATES)
4. In NODE you will have to enter each of the forms you want to include in your
   Task Flow.  To do this you use the LOV to pick each one in turn, and page
   down or new record to enter each of the forms in this field.
5. Find the form you want to use as the Top Node (the form that will be at the
   top of the task flow e.g. People Enter and Maintain) and check the TOP NODE
   checkbox.
6. When you have entered each of the forms, commit the record and move to the
   next zone Navigate Options to set up the task flow itself.

7. In the Navigate Options, you select the nodes in the task flow that you want
   to navigate to.
Note:  All Task Flows must begin with one of the following three forms:
- PERSON FORM
- PEOPLE FOLDER
- ASSIGNMENTS FORM



























CALLING TASK FLOWS FROM MENUS
-------------------------------------------------
Once the Task Flow set up, we will need to create a function in order
to call it from the required Menu.
1. In the System Administrator responsibility ->Application-> Function.
2. Enter the function name.
3. In Form, enter the name of the Top Node of your Task Flow.
4. In Parameters you will have to enter the following:
   WORKFLOW_NAME="taskflowname"
   (ensure that there are no spaces, the "'s are included and the taskflowname
   is the name that you have given your own Task Flow.)
5. Enter a USER FUNCTION NAME, this is the Name that you will see on the LOV on
   the menu when you attach your Task Flow to it.  SAVE
6. attach the function to a Menu
7. In System Administrator,  Application -> Menu
8. Query the Menu that you want the Task Flow attached to.
9. Add your  new function to the Menu (quickpick in the LOV).
10. Enter a Navigator Prompt, this is what you will see on the Menu when you
    want to access your Task Flow.

Tuesday 15 March 2016

Using SQL Loader with an UTF8 Character- set Database

Using SQL Loader with an UTF8 Character-set Database

You never had any troubles with character-sets using Oracle’s SQL Loader utility?
If you accept as true then enjoy the day or find another interesting tip on our site.
Situation
We had erroneous data after loading with SQL Loader.
Settings
Database created with
CHARACTER SET "UTF8"
NATIONAL CHARACTER SET "WE8ISO8859P1"
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA
Example
Create a test table with
CONNECT scott/tiger;
DROP TABLE LOADER_TEST;
CREATE TABLE LOADER_TEST (
   USR_ID          NUMBER NOT NULL ,
   USR_NAME        VARCHAR2(50) ,
   USR_LNK_NAME    VARCHAR2(50) ,
   USR_LNK_ORDER   NUMBER
);
  • Case 1
Client (Win 2000)
NLS_LANG=AMERICAN_AMERICA.UTF8
Controlfile (ctrl.txt)
LOAD DATA
CHARACTERSET UTF8
INFILE *
REPLACE INTO TABLE LOADER_TEST
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS (
   USR_ID         INTEGER EXTERNAL,
   USR_NAME       CHAR(50),
   USR_LNK_NAME   CHAR(50),
   USR_LNK_ORDER  INTEGER EXTERNAL
)
BEGINDATA
1;Santé bien;http://www.vaud-sante.ch;3
2;Santé;http://www.vaud-sante.ch;4
3;Alle à gessa;http://www.gessa.com/now;2
4;Alle à;http://www.gessa.com/now;1
5;Gägs;http://www.gaegs.ch;5
6;Gägs ä;http://www.gaegs.ch;6

Invoke sqlldr
sqlldr userid=scott/tiger control=ctrl.txt
Result
column usr_id format 99 heading "Id"
column USR_NAME format a32 heading "Name"
column USR_LNK_NAME format a26 heading "Link"
column USR_LNK_ORDER format 99 heading "Ord"
select * from loader_test;
Id Name                             Link                      Ord
-- -------------------------------- ------------------------- ---
 1 Santé bien                       http://www.vaud-sante.ch    3
 2 Santé;http://www.vaud-sante.ch   4
 3 Alle à gessa                     http://www.gessa.com/now    2
 4 Alle à;http://www.gessa.com/now  1
 5 Gägs                             http://www.gaegs.ch         5
 6 Gägs ä;http://www.gaegs.ch        6
For the records 2,4,6 where a special character is prior to the delimiter, the SQL Loader does not see the delimiter.
  • Case 2
Change the control file (ctrl.txt) to
CHARACTERSET WE8ISO8859P1
Reload the data and select the LOADER_TEST table again
Id Name                             Link                      Ord
-- -------------------------------- ------------------------- ---
 1 Santé bien                      http://www.vaud-sante.ch    3
 2 Santé                            http://www.vaud-sante.ch    4
 3 Alle à gessa                    http://www.gessa.com/now    2
 4 Alle à                          http://www.gessa.com/now    1
 5 Gägs                             http://www.gaegs.ch         5
 6 Gägs ä                         http://www.gaegs.ch         6
Now, SQL Loader recognises all delimiters, but data seems to be corrupted.
  • Case 3
Change the client environment to
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
Reselect data - now the result is now correct !
 Id Name                           Link                       Ord
--- ------------------------------ -------------------------- ---
  1 Santé bien                     http://www.vaud-sante.ch     3
  2 Santé                          http://www.vaud-sante.ch     4
  3 Alle à gessa                   http://www.gessa.com/now     2
  4 Alle à                         http://www.gessa.com/now     1
  5 Gägs                           http://www.gaegs.ch          5
  6 Gägs ä                         http://www.gaegs.ch          6

Conclusion
Running client tools (e.g. SQL Loader, SQL Plus) against a database with UTF8 character set,you must assign WE8ISO8859P1 to NLS_LANG for the client tools due to prober data representation. That means a correct characterset conversion will take place within SQL Net and Net8 respectively.

Thursday 10 March 2016

Find concurrent program defined in each concurrent manager queue

Find concurrent programs defined in each concurrent manager queue


SELECT fcr.request_id,
       DECODE(fcpt.user_concurrent_program_name,
             'Report Set',
             'Report Set:' || fcr.description,
             fcpt.user_concurrent_program_name) CONC_PROG_NAME,
       argument_text PARAMETERS,
       NVL2(fcr.resubmit_interval,
           'PERIODICALLY',NVL2(fcr.release_class_id,
                              'ON SPECIFIC DAYS',
                              'ONCE')) PROG_SCHEDULE_TYPE,
       DECODE(NVL2(fcr.resubmit_interval,
                   'PERIODICALLY',NVL2(fcr.release_class_id,
                                       'ON SPECIFIC DAYS',
                                       'ONCE')),
                   'PERIODICALLY',
                   'EVERY ' || fcr.resubmit_interval || ' ' ||fcr.resubmit_interval_unit_code || 
                   ' FROM ' || fcr.resubmit_interval_type_code || 
                   ' OF PREV RUN',
                   'ONCE',
                   'AT :' || TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
                   'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
       fu.user_name USER_NAME,
       requested_start_date START_DATE
  FROM apps.fnd_concurrent_programs_tl fcpt
,       apps.fnd_concurrent_requests fcr
,       apps.fnd_user fu
,       apps.fnd_conc_release_classes fcrc
 WHERE fcpt.application_id        = fcr.program_application_id
   AND fcpt.concurrent_program_id = fcr.concurrent_program_id
   AND fcr.requested_by           = fu.user_id
   AND fcr.phase_code             = 'P'
   AND fcr.requested_start_date   > SYSDATE
   AND fcpt.LANGUAGE              = 'US'
   AND fcrc.release_class_id(+)   = fcr.release_class_id
   AND fcrc.application_id(+)     = fcr.release_class_app_id

Query to find Form Personalization

Query to find Form Personalization


SELECT ffv.form_id          "Form ID",
       ffv.form_name        "Form Name",
       ffv.user_form_name   "User Form Name",
       ffv.description      "Form Description",
       ffcr.sequence        "Sequence",
       ffcr.description     "Personalization Rule Name"
  FROM fnd_form_vl             ffv,
       fnd_form_custom_rules   ffcr
 WHERE ffv.form_name = ffcr.form_name
 ORDER BY ffv.form_name, ffcr.sequence;

Query to find Legal Entity, Organization, Company Code

This summary is not available. Please click here to view the post.

Query to find DFF

Query to find DFF


Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information". In the following example, I am trying to get all the information for "US" context code.


























The following query will display the DFF related information. You can try changing "fdfv.title" value too see different DFF.

-----------------------------------------------------------------------------
-- Query to find DFF information
-----------------------------------------------------------------------------
SELECT
       fdf.title                             "DFF Title",
       fdf.application_table_name            "Application Table",
       fdf.context_column_name               "Context Column Name",
       --
       fdfcu.descriptive_flex_context_code   "DFF Context Code",
       fdfcu.column_seq_num                  "Sequence",
       fdfcu.end_user_column_name            "Segment Name",
       fdfcu.application_column_name         "Column Name",
       --
       ffv.flex_value_set_name               "Value Set Name"
  FROM
       fnd_descr_flex_col_usage_vl   fdfcu,
       fnd_descriptive_flexs_vl      fdf,
       fnd_flex_value_sets           ffv
 WHERE
       1 = 1
   --
   AND fdf.title = 'Further Job Information'        -- <change it>
   AND fdfcu.descriptive_flex_context_code = 'US'   -- <change it>
   AND fdfcu.enabled_flag = 'Y'
   --
   AND fdfcu.flex_value_set_id = ffv.flex_value_set_id
   AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name
   AND fdfcu.application_id = fdf.application_id
   --
 ORDER BY
       fdfcu.descriptive_flexfield_name,
       fdfcu.descriptive_flex_context_code,
       fdfcu.column_seq_num;