Form personalization - Number

Numeric validation in form personalization

 

Trigegr event: WHEN-VALIDATE-RECORD

 

Trigger object : ACCOUNTS

 

DECODE(TRANSLATE(:ACCOUNTS.BANK_ACCOUNT_NUM,'1234567890','0000000000'),LPAD('0',LENGTH(:ACCOUNTS.BANK_ACCOUNT_NUM),'0'),'NUMBER','NOT NUMBER')='NOT NUMBER'

 

ex: if 33123-33 is entered, then decode convers the code as 00000-00 and LPAD function returns 0000000 , hense mismatchin and not Number.

 

 

Creating softlink and registering Host concurrent program

1. Create a host file in required TOP

 

Ex: $XXGL_TOP/bin/XXGL_EMAIL.prog (the extension can be any thing)

 

2. Create a softlink using the below command (execute this command in the same path $XXGL_TOP/bin)

 

ln -s $FND_TOP/bin/fndcpesr  XXGL_EMAIL.prog

 

3. Then use the below command

 

XXGL_EMAIL XXGL_EMAIL.prog

 

4. Register the executable with XXGL_EMAIL and concurrent program and run.

 

 

How to find Oracle Apps password

Oralce apps password is stored in a file called wdbsvr.app on application server

 

in the below path

 

$IAS_ORACLE_HOME/Apache/modplsql/cfg

 

Generally this is not accessible by with your unix access. Only administrator can access.

Position Based Approval Hierarchy in Purchasing

Position Based Approval Hierarchy in Purchasing explained in detail

 

http://erpschools.com/Apps/oracle-applications/articles/financials/Purchasing/Position-Based-Approval-Hierarchy-in-Purchasing/index.aspx

 

 

Query to get HR Orgnization Grant Parent

Here is the query to get grant parent of organization

 

 

SELECT  OSE.organization_id_child,OU.organization_id,OU.name, OU.type
      FROM   (SELECT  POSE.organization_id_child,
                      POSE.organization_id_parent
             FROM  per_org_structure_elements POSE
             WHERE POSE.org_structure_version_id  = (SELECT OSV.org_structure_version_id
                                                     FROM   per_org_structure_versions OSV,
                                                        per_organization_structures OS
                                                     WHERE SYSDATE BETWEEN OSV.date_from AND NVL (OSV.date_to,SYSDATE + 1)
                                                     AND OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID
                                                     AND UPPER(OS.name)               = 'XX You Company'  -------------------------------------------------------------
                                                     AND OS.primary_structure_flag    = 'Y' ))OSE,
                   hr_all_organization_units  OU
      WHERE   OSE.organization_id_parent      =  OU.organization_id
    --  AND     UPPER(OU.name)                  = 'GROUP 2'
      AND     OU.type = 'AGP'
      CONNECT BY PRIOR organization_id_parent = organization_id_child
      START WITH organization_id_child =  (select paa.organization_id
      from
                   per_all_people_F pea
                   ,per_all_assignments_f paa
where pea.person_id=paa.person_id
      and pea.person_id=:p_person_id
      and sysdate between pea.effective_start_date and NVL(pea.effective_end_date,sysdate+1)
      and sysdate between paa.effective_start_date and NVL(paa.effective_end_date,sysdate+1)
      and paa.assignment_type='E')

SQL Query to get Oracle HR Organization Hierarchy

SQL Query to get Oracle HR Organization Hierarchy

 

 

SELECT
LPAD(' ',10*(LEVEL-1)) || org.name hierarchy,
org.organization_id
FROM
hr_all_organization_units org,
per_org_structure_elements pose
WHERE 1=1
AND porg.organization_id = pose.organization_id_child
AND pose.org_structure_version_id = 61
--and org.name  like '201.Financiale Services'
START WITH
pose.organization_id_parent = 115   -- Orgnization of parent id -- provide the id from which level the downward hierarchy should be displaed
CONNECT BY PRIOR
pose.organization_id_child = pose.organization_id_parent
ORDER SIBLINGS BY
org.location_id,
pose.organization_id_child

SQL Query to get Oracle HR Organization Hierarchy

SQL Query to get Oracle HR Organization Hierarchy

 

 

SELECT
LPAD(' ',10*(LEVEL-1)) || org.name hierarchy,
org.organization_id
FROM
hr_all_organization_units org,
per_org_structure_elements pose
WHERE 1=1
AND porg.organization_id = pose.organization_id_child
AND pose.org_structure_version_id = 61
--and org.name  like '201.Financiale Services'
START WITH
pose.organization_id_parent = 115   -- Orgnization of parent id -- provide the id from which level the downward hierarchy should be displaed
CONNECT BY PRIOR
pose.organization_id_child = pose.organization_id_parent
ORDER SIBLINGS BY
org.location_id,
pose.organization_id_child

Another work around for ieframe.dll error

If you are unable to uncheck the below option
Enable memory protection to help mitigate online attacks

goto c:\Program Files\Internet Explorer

right click on iexplore and select run as and administrator

then you should be able to uncheck Enable memory protection to help mitigate online attacks

if this does not work:
========================
Open internet browser
Goto tools --> Click Manage addons

Disable addons under Sun Microsystems

and restart the internet explorer and see

If that does not work

diable all the addons and try

PRC: Distribute Labor Costs erros with Signal 11

When you are extending the "PRC: Distribute Labor Costs" program and it fails with - Program was terminated by signal 11 error. Please check the following.

 

Go to Project

Setup -> System -> Implementation Options

 

Go to Expnd/Costing Tab

Uncheck - "Enable Overtime Calculations"  check box and save.

 

The "Program was terminated by signal 11 error" might occur for different reasons but while extending prc distribute labor costs program this is only the fix as per oracle support.

 

We also abserved that this program did not error when it was ran with ONLY Batch name / Project Number as parameters. We had faced the error when Batch name, project number and employee name are provided as parameters at same time.

Oracle Apps Sample/Simple Resume Structure

 

Name                                      :               <Name in Full>

Contact No.                           :               +(country Code) (Mobile)\

Mail id                                    :               xxxxx@xxxx.com

 

Experience Summary:

 

• X years of experience in <Technology> in the Development, Design, Testing, Coding
• Worked extensively on Oracle Manufacturing , System Administration modules
• Tasks executed as Computer Analyst, Programmer, Leading technical team/deliveries, documenting, handled onsite/offshore deliveries
• Extensively worked on RICE (Reports, Interfaces, Conversions and Extensions), Oracle Apps Framework development/extensions, customization, rollouts, migrations and upgrades in Oracle Applications
• Can work independently in understanding requirements, writing technical documents & application development using software development life cycle

 

Employment
Organization	Designation	Duration

<Present Company name>                                                  Oracle Consultant                         Jun 2009 - till date

<Previous Employer>                                                          <Position>                                       Oct 2008 - Jun 2009

 

Qualifications

• Bachelor of Science (Computer Science) – Completed in 1993 - from  XXXX University, India

 

 

Skills:

 

Oracle E-Business Suite	R11
Oracle Apps Modules	·         Inventory (INV) ·         Oracle Projects
Tools associated to Oracle Apps	Oracle Developer/2000, Forms6i, Reports6i, SQL*Plus, SQL*Loader
Methodology	Oracle Application Implementation Methodology (AIM)
Databases	Oracle 9i,10g, Ms-Access
Other Tools and Utilities	Documentum, Ms-VSS
Operating Systems	HP-UX, IBM/AIX
Programming Languages	SQL, PL/SQL

 

 

Work Expericne:

 

 

 

<Company>., <Location Optional ><Country Optional>

Duration: <From date> to <To date / Till date>

 

1)

Project                   :               Ex: Oracle Applications Support and Development project

Client                     :               <Client name>

Duration                :               From date to todate/till date

Roles & Responsibilities:

• Responsibility 1 Ex: Writing data conversion scripts
• Responsibility 2 Ex: Designing the technical document
• Responsibility 3 Ex: Responsible for raising TARs etc
• Responsibility 4 Ex: Developing Reports / Reviewing technical docs such as MD070 and MD120

 

 

Tools and Technologies:   

Oracle R12, Oracle Financials (GL, AR, AP, FA), Workflow Builder, Oracle 10g, SQL, PLSQL, VSS and UNIX 

 

               

2)

Project                   :               Ex: Oracle Applications Upgrade

Client                     :               <Client name>

Duration                :               From date to todate/till date

Roles & Responsibilities:

• Responsibility 1 Ex: Developing off scripts
• Responsibility 2 Ex: Designing the technical document
• Responsibility 3 Ex: Customizing workflows
• Responsibility 4 Ex: Developing Reports / Reviewing technical docs such as MD070 and MD120

 

 

Tools and Technologies:   

Oracle R12, Oracle Financials (GL, AR, AP, FA), Jdeveloper, XML Publisher, TOAD, Workflow Builder, Oracle 10g, SQL, PLSQL, VSS and UNIX

 

 

 

……List of project in Chronological order with the organization that you worked for

Oracle Apps Sample and Simple Resume Structure

Download DFF using FNDLOAD for particular Context code

Metalink advises to user p_context_code but it looks like that is not working.

 

Use the option DESCRIPTIVE_FLEX_CONTEXT_CODE = <context code> this works fine for sure.

 

FNDLOAD apps/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXPO_MY_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="FND_COMMON_LOOKUPS" DESCRIPTIVE_FLEX_CONTEXT_CODE="XXPO_LOCS"

 

 

Script to copy xml and class files from standard, custom and mds to the Jdeveloper

Automating the copying of class and xml files from respective tops and mds folders

copy all the files from $JAVA_TOP/per/irc/*.* to c:\dump\java\top\irc\ (create this folder path)

copy all the files from $PER_TOP/mds/irc/*.* to c:\dump\java\mds\irc\ (create this folder path)

Make sure the same path upto irc is created in myclasses and myprojects
ex: for irc you should create two more folders in your jdev
by default oracle/apps/fnd will be there. You just need to create per and irc under oracle/apps
so the path shoudl like like
myclasses\oracle\apps\per\irc
myprojects\oracle\apps\per\irc

Use the windows xcopy  command

start-> run -> cmd and execute the commands

#all classes and xml to myclasses
xcopy c:\dump\java\top\irc\*.* C:\jdeveloper\jdevhome\jdev\myclasses\oracle\apps\per\irc\ /S

#all xml to myprojects (only xml no class files)
xcopy c:\dump\java\top\irc\*.xml C:\jdeveloper\jdevhome\jdev\myprojects\oracle\apps\per\irc\ /S

# all xml from mds to respective top of myprojects
xcopy c:\dump\java\mds\irc\*.xml C:\jdeveloper\jdevhome\jdev\myprojects\oracle\apps\per\irc\ /S

# all xml from mds to respective top of myclasses (mds will only have xml)
xcopy c:\dump\java\mds\irc\*.xml C:\jdeveloper\jdevhome\jdev\myclasses\oracle\apps\per\irc\ /S

######################################
#
# Copying custom java and xml objects
#
#
####################################

Create separate folders for custom objects

create a folder structure xxclient\oracle\apps\per\
under
c:\dump\custom_top\   and   c:\dump\custom_mds\

copy all files and folders from  irc folder under Custom java top ex: $XXBOL_TOP\java\xxclient\oracle\apps\per\ to c:\dump\custom_top\

copy all files and folders from  irc folder under $XXPER_TOP/mds/oracle/apps/per/ to the local c:\dump\custom_mds\

and run the below commands.

#copy all classes and xml from custom top to myclasses           <cust>
# this is optional if you copy the
xcopy C:\dump\custom_top\*.* C:\jdeveloper\jdevhome\jdev\myclasses\ /S

#all xml to myprojects (only xml no class files)
xcopy c:\dump\custom_top\*.xml C:\jdeveloper\jdevhome\jdev\myprojects\ /S

# all xml from mds to respective top of myprojects
xcopy C:\dump\custom_mds\*.xml C:\jdeveloper\jdevhome\jdev\myprojects\ /S

# all xml from mds to respective top of myclasses (mds will only have xml)
xcopy c:\dump\custom_mds\*.xml C:\jdeveloper\jdevhome\jdev\myclasses\ /S

Script to copy the class and xml files to JDEV

Automating the copying of class and xml files from respective tops and mds folders

 

When you are required to perform OA framework extentions especially controllers, follow the below steps to set your jdeveloper

 

copy all the files from $JAVA_TOP/per/irc/*.* to c:\dump\java\top\irc\ (create this folder path)

Ex: If you want to copy IRC (irecruitment files)

copy all the files from $PER_TOP/mds/irc/*.* to c:\dump\java\mds\irc\ (create this folder path)

Make sure the same path upto irc is created in myclasses and myprojects
ex: for irc you should create two more folders in your jdev
by default oracle/apps/fnd will be there. You just need to create per and irc under oracle/apps
so the path shoudl like like
myclasses\oracle\apps\per\irc
myprojects\oracle\apps\per\irc

Use the windows xcopy  command

start-> run -> cmd and execute the commands

# in the below command /S will exclude the empty folders

 

#all classes and xml to myclasses
xcopy c:\dump\java\top\irc\*.* C:\jdeveloper\jdevhome\jdev\myclasses\oracle\apps\per\irc\ /S

#all xml to myprojects (only xml no class files)
xcopy c:\dump\java\top\irc\*.* C:\jdeveloper\jdevhome\jdev\myprojects\oracle\apps\per\irc\ /S

# all xml from mds to respective top of myprojects
xcopy c:\dump\java\mds\irc\*.xml C:\jdeveloper\jdevhome\jdev\myprojects\oracle\apps\per\irc\ /S

# all xml from mds to respective top of myclasses (mds will only have xml)
xcopy c:\dump\java\mds\irc\*.xml C:\jdeveloper\jdevhome\jdev\myclasses\oracle\apps\per\irc\ /S

 

3205: 'hr@mycompany.com' is not a valid role or user name

This is a uncommon error in HR workflow when the workflow is not customized properly 

 

 

HRSSA workflow by defauls holds the value for HR Department E-mail ID (Internal name HR_DEPT_EMAIL)  as "hr@mycompany.com "

   

 

And if you see at Access levels of that, it shows Customization - 20

Access 90 (this might be different because what ever the access level your set on your workflow builder at help -> About Oralce workflow builder <version no>

Protection 1000

 

And Option check boxes for Preserve customizations and Lock at this Access level are unchecked.

 

This means the email id can be changed. This can be done with below steps.

 

1. Open the workflow, on select help -> About Oralce workflow builder <version no>

2. Provide access level as 100 because Oracle patches will not be above access level 100

3. Change the email id from hr@mycompany.com  as something like  xyz@yourclient.com

4. At access tab select Preserve customizations check box

 

And save the workflow.

 

 

WFLOAD apps/$APPS_PASSWORD 0 Y <UPGRADE_MODE> XXHRSSA.wft

How to enable the DFF for the Lookup?

If you want to enable the DFF and enter some additional entries in Lookups you just need to follow the below steps

 

Create a DFF

 

Login to System Administrator

Application -> Flexfield -> Descriptive -> Segments

Search for Application = Application Object Library

Title = Common Lookups

 

You can see referenced field as "LOOKUP_TYPE"

 

Now if you want to enable your lookup, you need to uncheck "Freeze Flexfield Definitions" checkbox on the top left the DFF Segments screen.

this enables you to enter new DFF entries.

 

Now, enter create a line under Context Field Values

 

Under "Code" enter your lookup ex: XXHR_DEDUCT_ELEMENTS

 

Click on Segments

 

 

Enter the number, name, prompt, column and value set(if you have one)

 

save and compile the DFF.

 

Now, switch the responsibility to Application Developer to see the DFFs enabled in lookup

 

Open the common lookups screen and query for the lookup name you have given under "Code" of DFF setup.

 

and find the DFF enabled.

 

 

Oracle HRMS security profiles restrictions and important points

When you are working on security profiles especially with custom security, you must be careful to see whether another security profile is setup. Because, that may override your custom security profile with those restriction.

 

Ex: in custom security you might have written a piece of code to restrict the users to view applications across all the organizations, and you also set the organization security to restrict applicants at organization level.

 

In this case, your restriction at organization level will override and it will restrict the user to view the applicants in another organization and there wont be any impact of the code written in custom security tab.

Also make sure you have set the restriction such as view applicant, view contract etc.

Run the program :   "Security List Maintenance" to see the effect of the security profile. If you do not run this, you can not see the change you made.

How to find the process in Workflow using a known function name or Activity name

Using this query we can find the workflow process name where the given function name or activity is being called. This is one of the important workflow queries used during development of WFs

 

 

 

select
wat1.display_name "process name",wat.display_name "activity name",wa.function "function"
from wf_process_activities wpa
,wf_activities_tl wat
,wf_activities_tl wat1
,wf_activities wa
where wpa.activity_item_type = wa.item_type
and wpa.instance_label = wa.name
and wat.item_type = wa.item_type
and wat.version = wa.version
and wat.language = 'US'
and wat.name = wa.name
and wat1.name = wpa.process_name
and wat1.item_type = wat.item_type
and wat1.version = wat.version
and wat1.language = wat.language
and wat1.version = wpa.process_version
and (upper(wa.function) like '%'||upper('&func_name')||'%'  or (wa.FUNCTION IS NULL AND '&func_name'='%'))
and (upper(wat.display_name) like '%'||upper('&act_name')||'%'  or (wat.display_name IS NULL AND '&act_name'='%'))

How to fix the ieframe.dll error - this may occur when you are trying to download any software

When you are downloading any software, if you encounter any error like below.

res://ieframe.dll/acr_error.htm

is shown on the address bar

Step 1)
Go to internet
tools, internet options, security , Custom Level

Enable -> ActiveX Controls and Plug ins

Step 2)
Internet Options
Advanced tab Settings
Security->
Uncheck “Enable memory protection to help mitigate online attacks*”

Step 3) restart internet explorer