Oracle E-Business Suite

January 25, 2010

How to Create Position Hierarchy by Using APIs

January 17, 2010

Define Valid Grades for Position in Oracle HRMS

Define Valid Grades for Positions
Oracle HRMS lets you define Valid Grades for Positions. These definitions will be used to provide warning messages in the Assignment window when you enter Position and Grade information.
Navigate to Position Definitation Form
Query the position and click on the Valid Grades button
Enter and save the valid grades for each position. You can enter a single grade, or a set of grades.

Define Valid Grades for Positions By Using API
/* Formatted on 2010/01/17 08:29 (Formatter Plus v4.8.8) */
create table grade_raw_data(slno number,
position_name varchar2(1000),
grade varchar2(1000))

Upload Data into grade_raw_data table either by using SQLLDR or Some other tool

ALTER TABLE APPS_APPLMGR.GRADE_RAW_DATA
ADD (Status VARCHAR2(3 BYTE),
error_message varchar2(1000)
);

/* Formatted on 2010/01/17 08:40 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE xx_position_valid_grade
IS
CURSOR cur
IS
SELECT g.slno, g.position_name, g.grade, g.status, g.error_message
FROM grade_raw_data g
WHERE status = ‘E’ or Status is Null;

x_rowid VARCHAR2 (200);
x_valid_grade_id NUMBER;
x_business_group_id NUMBER;
x_grade_id NUMBER;
x_date_from DATE;
x_comments VARCHAR2 (200);
x_date_to DATE;
x_job_id NUMBER;
x_position_id NUMBER;
x_attribute_category VARCHAR2 (200);
x_attribute1 VARCHAR2 (200);
v_msg VARCHAR2 (1000) := NULL;
x_attribute2 VARCHAR2 (200);
x_attribute3 VARCHAR2 (200);
x_attribute4 VARCHAR2 (200);
x_attribute5 VARCHAR2 (200);
x_attribute6 VARCHAR2 (200);
x_attribute7 VARCHAR2 (200);
x_attribute8 VARCHAR2 (200);
x_attribute9 VARCHAR2 (200);
x_attribute10 VARCHAR2 (200);
x_attribute11 VARCHAR2 (200);
x_attribute12 VARCHAR2 (200);
x_attribute13 VARCHAR2 (200);
x_attribute14 VARCHAR2 (200);
x_attribute15 VARCHAR2 (200);
x_attribute16 VARCHAR2 (200);
x_attribute17 VARCHAR2 (200);
x_attribute18 VARCHAR2 (200);
x_attribute19 VARCHAR2 (200);
x_attribute20 VARCHAR2 (200);
x_end_of_time DATE;
x_pst1_date_end DATE;
x_pst1_date_effective DATE := TO_DATE (‘03-01-2010′, ‘DD-MM-YYYY’);
BEGIN
FOR pos IN cur
LOOP
BEGIN
x_rowid := NULL;
x_valid_grade_id := dbms_random.random;
x_business_group_id := 0;

SELECT grade_id
INTO x_grade_id
FROM per_grades
WHERE UPPER (NAME) = UPPER (pos.grade);

x_date_from := ‘03-jan-2010′;

SELECT position_id
INTO x_position_id
FROM hr_all_positions_f
WHERE UPPER (NAME) = UPPER (pos.position_name);
per_valid_grades_pkg2.insert_row (x_rowid,
x_valid_grade_id,
x_business_group_id,
x_grade_id,
x_date_from,
x_comments,
x_date_to,
x_job_id,
x_position_id,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_attribute16,
x_attribute17,
x_attribute18,
x_attribute19,
x_attribute20,
x_end_of_time,
x_pst1_date_end,
x_pst1_date_effective
);

UPDATE grade_raw_data p
SET p.status = ‘I’
WHERE slno = pos.slno;
x_valid_grade_id := NULL;
X_GRADE_ID := null;
x_position_id := null;
EXCEPTION
WHEN OTHERS
THEN
v_msg := SQLERRM;
UPDATE grade_raw_data p
SET p.status = ‘E’,
p.error_message = v_msg
WHERE slno = pos.slno;
COMMIT;
END;
END LOOP;
END;
/

Next Post will be about Position Hierarchy Upload by Using API .

January 3, 2010

Upload Lookup Values by Using Script

October 19, 2009

Extract Employee and Supervisor Based on Position Hierarchy

This SQL will give you employee and Supervisor as per position hierarchy .

/* Formatted on 2009/10/19 15:01 (Formatter Plus v4.8.8) */
SELECT LPAD (‘ ‘, 5 * LEVEL) || has.NAME hierarchy, LEVEL,
hap.NAME parent_name, pse.parent_position_id, has.NAME child_name,
pse.subordinate_position_id
FROM (SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV (‘LANG’)) hap,
(SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV (‘LANG’)) has,
per_pos_structure_elements pse
WHERE pse.business_group_id = 81
AND hap.position_id = pse.parent_position_id
AND has.position_id = pse.subordinate_position_id
start with pse.parent_position_id = –40979 Base Position Id (like CEO etc)
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id
ORDER BY 4

October 7, 2009

Oracle Procurement Contract (a step by step user guide)

Filed under: General, Oracle Purchasing, R12 — Tags: — Ahmad Bilal @ 1:56 pm

August 20, 2009

Oracle Business Intelligence Applications 7.9.6 step by step installation and Configuration with screen shots.

Visit ERPstuff.com to download Installation and configuration Guide Version 1.0

Link

Note:- you have to be a registered member and should have access to Documents (Tutorials) Section in Forums

August 19, 2009

SQL Query to Get Diffrence in Values for Profile Options assigned on USER Level

I got this Query from Gareth Roberts BLOG …Very Very Useful during implementation specially if there are some custom profile options .

I used to work for a home appliances Manufacturing Organization . we used some custom profile options to setup level of security for Sales Manage, Salesman and Warehouse User. Some Time we faced issues and the and discovered that it’s wrong profile option assignment.

/* Formatted on 2009/08/19 08:41 (Formatter Plus v4.8.8) */
SELECT *
FROM (WITH prof_di AS
(SELECT ‘USER’ level_name, fu.user_name level_value,
fpo.profile_option_id, fpot.user_profile_option_name,
fpo.profile_option_name, fpov.profile_option_value
FROM fnd_user fu,
fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot
WHERE fu.user_id = fpov.level_value
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpot.LANGUAGE = ‘US’
AND fpov.level_id = 10004
AND fu.user_name = ‘SYSADMIN’), /*User Name Working Fine*/
prof_gr AS
(SELECT ‘USER’ level_name, fu.user_name level_value,
fpo.profile_option_id, fpot.user_profile_option_name,
fpo.profile_option_name, fpov.profile_option_value
FROM fnd_user fu,
fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot
WHERE fu.user_id = fpov.level_value
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpot.LANGUAGE = ‘US’
AND fpov.level_id = 10004
AND fu.user_name = ‘AHMADBILAL’) /*User Name Where we are facing some issue*/
SELECT pd.profile_option_id, pd.user_profile_option_name,
pd.profile_option_name, pd.profile_option_value d_value,
pg.profile_option_value g_value,
DECODE (pd.profile_option_value,
pg.profile_option_value, ‘EQUAL’,
‘DIFF’
) status
FROM prof_di pd, prof_gr pg
WHERE pd.profile_option_name = pg.profile_option_name(+)
UNION
SELECT pg.profile_option_id, pg.user_profile_option_name,
pg.profile_option_name, pd.profile_option_value d_value,
pg.profile_option_value g_value,
DECODE (pg.profile_option_value,
pd.profile_option_value, ‘EQUAL’,
‘DIFF’
) status
FROM prof_di pd, prof_gr pg
WHERE pg.profile_option_name = pd.profile_option_name(+))
WHERE status != ‘EQUAL’;

August 10, 2009

How to Migrate Forms Personalization from One Instance to Other Instance.

It’s very normal during implementation that we need to move all personalization from TEST / DEVELOPMENT to PRODUCTION there are two way (best of my Knowledge)

Method 1
Open TEST instance navigate to form where you did personalization, simultaneously open PRODUCTION instance start copy pasting….. Sounds pathetic (at least to me)
Method 2
Use FNDLOAD (purely a DBA task)
Download for a specific form
FNDLOAD / 0 Y DOWNLOAD
$FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES form_name=

Download all personalization
FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES

Upload
FNDLOAD / 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct

For more information you can visit this LINK

http://oracle.anilpassi.com/oracle-fndload-script-examples.html

Daily Business Intelligence Features

Filed under: Daily Business Intelligence, General — Tags: — Ahmad Bilal @ 4:23 pm

Few days back for Pre sales meetings with some clients i was looking for some slides / information about Daily Business Intelligence and Oracle Business Intelligence Application and difference in both.I got some stuff related to Daily Business Intelligence believe it’s worth sharing.

Click to Download

July 16, 2009

OBIA Data Load by DAC causing Account Lock

I was implementing Oracle Business Intelligence Application 7.9.6. on one client site in Middle East . Everything was fine when my data warehouse was on Oracle Database 10.1.X but on DBA suggestion they changed it to 10.2.0.1.0 . once they changed Database version. it was disaster for me whenever i ran the task from DAC it was showing me Account is Locked…..duhhhhh.

Now DBA was no taking the responsibility at all, by saying this it’s some issue with DAC or TASK. but case was not that at all but i was not able to prove it :(

After some research i got to know in Oracle 10.2 they setup the default option for “FAILED_LOGIN_ATTEMPTS” as 10 so after 10 unsuccessful attempts DAC is Locking user Name i just used following SQL Statement to rectify this issue :)

Alter Profile “DEFAULT” LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

July 8, 2009

Julian to Gregorian Calender Switch impact on Oracle

Filed under: General, Oracle General Ledger — Ahmad Bilal @ 12:06 pm

Do you see something abnormal in following image?

oct-1582

Yes …….what’s this ……?

And same thing you will see on some other ERP solutions / software once you select September 1752.

Answer

The calendar snippet shows that there are no days between October 4 and October 15. This discontinuity is called the “cutover”.

What is Cutover

“This is a hybrid Julian/Gregorian calendar with a cutover on October 4/15 1582.” Any dates before a cutover are Julian, any dates during a cutover are nonexistent, and any dates after a cutover are Gregorian.

July 2, 2009

Mass Feature Available in Oracle Purchasing R12

June 25, 2009

How to Clear Application Cache by Using Funcational Administrator Responsiblity

1- Navigate to Functional Administrator Responsibility
2- Choose the “ Core Services” Tab
3- Choose “Caching Framework”
4- Click on “Global Configuration”
5- Click on “Clear All Cache”
6- The Click on yes on the display which will appear.

June 11, 2009

Meaningless Details (a story about project management)

Filed under: General — Ahmad Bilal @ 5:54 pm

I got this story from internet and I believe it’s worth sharing for Project Managers because most of us faced same situation during project management

This is a true story, although the details have been changed to protect the guilty parties.

I was hired as an outside consultant to manage a project for an international corporation. They were producing a new project – a multi-million dollar project that was supposed to go to market within a specific timeframe.

On the initial call with the new product team, I was introduced to the various people including the sponsor, the team leader, and the team. The sponsor turned the meeting over to the team leader and she introduced herself and then introduced the project.

“The codename for this project is ‘Ice’ because we’ll freeze out the competition when this product hits the market”. Okay, fair enough. I was the project manager of Project Ice.

We got started.

Less than a week later, we received a blanket email from the team leader. “We’ve changed the codename to ‘Project Snowstorm’ because we’re going to totally cover the market with this product.” Great! Now I was the project manager for Project Snowstorm.

We continued with the work.

Then, on our weekly team call, the team leader opened the call by saying “I’ve forgotten to tell most of you, but we’ve changed the codename to ‘Avalanche’ because we felt that it more accurately describes how we wanted to take our market by storm with this project.” So now I was the project manager for Project Avalanche.

We continued on the project.

Two days later, we got an email from the team lead: “The Senior V.P.’s don’t like the negative connotations of the term ‘avalanche’ so we’re going to give the codename a more positive spin, ‘Project Fire’ because we’re going to burn up the market!” So now I was the project manager for Project Fire.

And I continued to manage the project’s progress.

Then another email: “We’ve decided to change the codename again. ‘Project Fire’ was too general and didn’t seem like we had any focus, so we’re going to call it ‘Project Laser’.” So now I was the project manager for Project Laser.

… and on it went. The codename of the project changed 14 times. No, that is not an exaggeration or hyperbole. The project really did change codenames that many times.

If you’ve ever managed a project for another company, you know that it can be difficult to flex your project management muscle and say “enough is enough”. In the case of this project, I could have stepped in earlier but there were some political issues at play here and I needed to pick my battles (another lesson for another time).

In the end, the codenames didn’t extend the project timeline too much. But they did create a number of inefficiencies that could not be ignored. The timeline was impacted a little, and people were never sure who to talk to or where to file information or how to refer to the project because it was never clear.

To me, this is a perfect example of how a corporation can get caught up in a meaningless detail (a codename, of all things, and it had absolutely nothing to do with the final product name!) and forget the bigger picture: Completing a project successfully, on time and on budget.

June 1, 2009

Hyperion Startuppp (Appetizer for Hyperion Newbies)

Filed under: General, Hyperion — Tags: , , , , , , — Ahmad Bilal @ 1:39 pm

February 10, 2009

How to Get Approval Group assigned to specific Job and Position in Oracle Purchasing

January 7, 2009

Remittance Advice by Email to Supplier by Using UTL_SMTP

November 28, 2008

November 24, 2008

Strength and Weakness of Approval Hierarchies available in Oracle Purchasing

Filed under: Oracle Purchasing, R12 — Ahmad Bilal @ 6:19 pm

Strengths and Weaknesses of Approvals Using the Employee/Supervisor Relationship

Strengths

Allows flexibility of establishing approval groups around either jobs or positions.

Assuming Oracle Human Resources (HR) is installed and the data is being maintained, Purchasing uses the supervisor name on the employee record which should be entered and maintained for HR purposes already.

Weaknesses

Documents may not travel by the employee/supervisor relationship for approval purposes, but take a different path in part or entirely.

Documents can get hung up more frequently due to the volume of personnel changes that typically occur in companies.

Higher volumes of documents need to be re-routed using the Forward-To settings. Recall that the first name alphabetically appears as the default.

Strengths and Weaknesses of Approvals Using Position Approval Hierarchies

Strengths

Allows approval paths to be set up that follow the actual flow of documents for approval

Assuming Human Resources is installed and the data is being maintained, Purchasing can make an employee representation of the position approval hierarchy by simply running the Fill Employee Hierarchy process. This will allow changes occurring in personnel assignments to be updated in the position approval hierarchy.

Documents can get hung up less frequently due to the lower volume of structural hierarchy changes compared to the higher volume of personnel changes that typically occur in companies.

Lower volumes of documents need to be re-routed using the Forward To settings, especially when there are multiple holders in a particular job or position. Recall that the first name alphabetically appears as the default when there are multiple holders.

Weaknesses

Cannot be used with jobs, but must be used with positions.

November 23, 2008

Some Doubts………..In Oracle Purchasing

Filed under: General, Oracle Purchasing, R12 — Tags: — Ahmad Bilal @ 10:12 am

What if there is more than one holder in a job or position?

Multiple holders in the same job or position can only occur if Oracle Human Resources is installed. If only Oracle Purchasing is installed, you can assign only one employee to each job or position. If multiple holders exist when using position approval hierarchies, Oracle Workflow will route the document to the holder based on alphabetical order. You can see who the system will route the document to by clicking the Forward radio button, overriding the defaulted name if needed.

What if there are changes to the position approval hierarchy?

Personnel changes are updated in the position approval hierarchy by running the Fill Employee Hierarchy process which reviews the employee record to determine the current position assignment. You will probably want to schedule this process to run on a frequent basis to ensure smooth processing of all approval requests. Structural changes to the position approval hierarchy require the hierarchy to be rebuilt from the point where the change occurs to the bottom of that branch. Documents are routed according to the hierarchy in effect at the time they are submitted for approval.

What if Human Resources were already implemented with jobs set up, but no positions?

Unless the decision to set up jobs only and no positions can be revisited, you will have to route all documents by the employee/supervisor relationship and maintain this data on the employee records.

What if the approver is unavailable for an extended period?

Oracle Purchasing has the ability to automatically forward documents when users do not respond to notifications. This tool should be used when possible to prevent documents from holding up business productivity. Oracle Workflow manages this functionality, typically set up to send a first and second reminder after pre-determined time periods before forwarding the notification to the next approver.

What if the document has a status of Pre-Approved?

The status of Pre-Approved is the outcome of a person forwarding a document for approval even though the forwarding person has the necessary authority to approve it. The document may have been forwarded by mistake or for business reasons. Once the person it was forwarded to approves the document, the status will be changed to Approved and subsequent actions such as receiving and invoicing can be completed.

What if there is no account range on an approval group assigned to a job or position?

By default, all accounts are excluded from an approval group and therefore documents governed by this approval group cannot be approved. You avoid this situation by ensuring that there is at least one Account Range Include rule on every Approval Group.

What if Workflow can’t find a supervisor to approve the document?

If this occurs, the person submitting the request for approval must forward the document to a different person in the list of values for Forward To. If this isn’t done, the document will be returned to an Incomplete status and a notification will be sent stating No Approver Found – Please Select a Forward To Employee. If using employee/supervisor relationship to determine approval paths, the list of values will include all active employees. If using position approval hierarchies to determine approval paths, the list of values will include all employees in the hierarchy selected.

What if a job or position has different authority levels for different document types?

You may have jobs or positions that can approve one document up to a specified dollar amount, while they can approve another document at a lower dollar amount. If this occurs, simply set up multiple approval groups with the rules properly defined for the differences and ensure that the right approval group is assigned to the correct document type.

What if documents flow employee/supervisor, but approval rules are needed?

Approval Groups can be used whether approval paths are determined by employee/supervisor relationships or by position approval hierarchies.

Older Posts »

Blog at WordPress.com.