Oracle Apps Diary

Practice Notes: Query for DAC metadata

2011-10-20T13:39:00.003+05:30

Time and Again there may a situation where one may need find the DAC tasks which populate particular target table be it a temporary table or staging table or the warehouse table. This information could easily be found from the DAC interface. But if we need to get this data from a TOAD/SQL developer, the following query would do the work.

select step.name as "DAC TASK",

tbl.name as "TARGET TABLE",

step.app_wid as "ETL LAYER",

step.priority

from w_etl_step_tbl step_tbl,

w_etl_step step,

w_etl_table tbl

where tbl.row_wid = step_tbl.table_wid

and step.row_wid = step_tbl.step_wid

and tbl.name = 'W_EMPLOYEE_D'

and step_tbl.type_cd = 'Target'

order by step.priority, step.name

Practice Notes: PER_ALL_ASSINGMENTS_F ASSINGMENT_TYPE

2011-09-30T12:26:00.004+05:30

Probably not worth blogging. But don't have mind to just leave it after having searched for quite sometime.

The lookup type for the PER_ALL_ASSINGMENTS_F.ASSINGMENT_TYPE is ALL_ASGN_TYPE

So next time i can just look at my diary.

OBIEE : Beware of Session Variables

2011-08-23T15:52:00.006+05:30

Blogging after a long time. It may be interesting to note some interesting outcomes in the usage of server variables. You may refer to the syntax of using different OBIEE variables here. We may note that the syntax for referring a session variables is

VALUEOF(NQ_SESSION.sess_var_name) -------- (1)

To refer a repository variable,

VALUEOF("rep_var_name") --------------- (2)

But what if you refer a session variable using syntax (2)?

I have taken two such session variables CURRENT_FSCL_YEAR and CURRENT_FSCL_PERIOD from the BI Apps RPD.

The incorrect way of using a session variable CURRENT_FSCL_YEAR:

The incorrect way of using a session variable CURRENT_FSCL_PERIOD:

The result is :

As everyone can see, it has fetched the default value of the session variable for the incorrect representations. Internally repository or session, they are server variables hence this behavior. Hence it may not be good idea to mix the syntax of both the variable types.

OBIEE - Script for creating Time Dimension

2010-05-25T19:54:00.002+05:30

A basic plsql script for creating time dimension table and populating it with data for years together.

CREATE TABLE mh_time_periods

(

time_period_id NUMBER NOT NULL

,YEAR NUMBER(4) NOT NULL

,quarter NUMBER(1) NOT NULL

,MONTH NUMBER(2) NOT NULL

,DAY NUMBER(2) NOT NULL

,month_end_flag char(1) DEFAULT 'N'

,year_end_flag char(1) default 'N'

,quarter_end_flag CHAR(1) DEFAULT 'N'

,period_date DATE NOT NULL

,CONSTRAINT mh_time_periods_pk PRIMARY KEY(time_period_id)

,CONSTRAINT mh_time_periods_chk1 CHECK(month_end_flag IN ('N','Y'))

,constraint mh_time_periods_chk2 check(year_end_flag in ('N','Y'))

,CONSTRAINT mh_time_periods_chk3 CHECK(quarter_end_flag IN ('N','Y'))

);

/

CREATE SEQUENCE mh_time_period_seq START WITH 1 INCREMENT BY 1;

/

Currently the time dimension table has basic settings. More columns can be added as and when required.

CREATE OR REPLACE PROCEDURE mh_fill_time_data(

p_end_date DATE

)

IS

l_year NUMBER(4) := 2005;

l_month NUMBER(2) := 1;

l_day NUMBER(2) := 1;

l_quarter NUMBER(1) := 0;

--l_flag BOOLEAN := FALSE;

l_month_end_flag CHAR(1) := 'N';

l_year_end_flag char(1) := 'N';

l_quarter_end_flag CHAR(1) := 'N';

l_loop_day_end NUMBER(2);

l_end_year NUMBER(4) := TO_NUMBER(TO_CHAR(p_end_date,'yyyy'));

l_end_month NUMBER(2) := TO_NUMBER(TO_CHAR(p_end_date,'mm'));

l_end_day NUMBER(2) := TO_NUMBER(TO_CHAR(p_end_date,'dd'));

BEGIN

SAVEPOINT start_insert;

LOOP

IF l_month IN (1,2,3) THEN

l_quarter := 1;

ELSIF l_month IN (4,5,6) THEN

l_quarter := 2;

ELSIF l_month IN (7,8,9) THEN

l_quarter := 3;

ELSE

l_quarter := 4;

END IF;

IF l_month IN (1,3,5,7,8,10,12) THEN

l_loop_day_end := 31;

ELSIF l_month IN (4,6,9,11) THEN

l_loop_day_end := 30;

ELSE

l_loop_day_end := get_feb_day(l_year);

end if;

IF l_year = l_end_year AND l_month = l_end_month THEN

l_loop_day_end := l_end_day;

END IF;

LOOP

IF l_day = l_loop_day_end THEN

l_month_end_flag := 'Y';

IF l_month = 12 THEN

l_year_end_flag := 'Y';

END IF;

end if;

if l_month_end_flag = 'Y' then

if l_month IN (3,6,9,12) then

l_quarter_end_flag := 'Y';

END IF;

INSERT INTO mh_time_periods(

time_period_id

,YEAR

,quarter

,MONTH

,DAY

,month_end_flag

,year_end_flag

,quarter_end_flag

,period_date

)

VALUES(

mh_time_period_seq.NEXTVAL

,l_year

,l_quarter

,l_month

,l_day

,l_month_end_flag

,l_year_end_flag

,l_quarter_end_flag

,TO_DATE(TO_CHAR(l_day)||'-'||TO_CHAR(l_month)||'-'||TO_CHAR(l_year),'DD-MM-YYYY')

);

l_day := l_day + 1;

EXIT WHEN l_day > l_loop_day_end;

END LOOP; -- end of day loop

l_quarter_end_flag := 'N';

l_month_end_flag := 'N';

l_day := 1;

l_month := l_month + 1;

EXIT WHEN l_month > 12 OR (l_year = l_end_year AND l_month > l_end_month);

END LOOP; -- End of month loop

l_year_end_flag := 'N';

l_month := 1;

l_year := l_year + 1;

EXIT WHEN l_year > l_end_year;

END LOOP; -- End of year loop

COMMIT;

dbms_output.put_line('Done');

EXCEPTION

WHEN others THEN

dbms_output.put_line(SQLERRM);

ROLLBACK TO start_insert;

END mh_fill_time_data;

/

CREATE OR REPLACE FUNCTION get_feb_day (nYr IN NUMBER) RETURN NUMBER IS

v_day varchar2(2);

begin

select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;

return to_number(v_day);

END;

/

The above plsql populates from 01-JAN-2005. But can be changed to any starting date.

Note:

The code is open for scrutiny

OBIEE Datetime Functions

2010-05-19T11:51:00.002+05:30

To get the start date of the last 4 quarters in OBIEE Answers

TIMESTAMPADD(SQL_TSI_QUARTER, -3, 
 TIMESTAMPADD(
 SQL_TSI_DAY,-1*(DAY_OF_QUARTER(CURRENT_DATE)-1), CURRENT_DATE
 )       
     
)

Practice Notes: UTL_FILE mess

2010-05-07T11:11:00.007+05:30

It is most common for apps developer to create outbound interfaces in text files and the usual requirement will of delivering 40 - 60 columns of data. Typical choice for a developer is to use UTL_FILE package.

Crux of the mess is we have to deliver above said number of column data on the average. Usually beginners would start like this

l_utl_file UTL_FILE.FILE_TYPE;
l_utl_file := UTL_FILE.FOPEN(p_dir, p_file_name,'w');

Then goes on to write to this file. All things set, compiles successfully and when run, produces following error:

ORA-29285: file write error

What happened here? we do have permission to write to that directory but still we get this error. It happens because, on average utl file with 40 - 60 columns takes at least 1500 - 2000 characters each line with proper formatting while default for an utl file line size is around 1000. The following is the procedure declaration for FOPEN.

function fopen returns record
(
 location in varchar2,
 filename in varchar2,
 open_mode in varchar2,
 max_linesize in binary_integer default
); --courtesy from http://www.adp-gmbh.ch/ora/plsql/utl_file.html

Most references we find for FOPEN miss the fourth default parameter set to 32K of characters. Hence for a typical interface flat file, it is necessary to open the file with required line size to not to see the write error.

Practice Notes: Payroll Fast Formula

2010-04-28T14:08:00.010+05:30

Fast formulas exist not only for payroll calculation but also for many other purposes. One of them being input value validation i.e. using these type of formulas we will be able to validate the screen entry values of element's input values.

We can add these validations against particular elements in

Form Functions(ctrl + L) -> element entries

Here search for the person and select the element's entry value button and in the popup form, horizontal scroll to formula text box and from the LOV select the formula which validates the screen entries against this input value.

Such custom formula can be created in

Form Functions(ctrl + L) -> write formula

Create the formula with formula type as Element Input Validation. A very useful article on element input validation formulas can be found in oracle manual here.

Two basic rules for writing an input value validation formula are

There should be only one INPUT for the formula named ENTRY_VALUE of text data type i.e. INPUTS are entry_value(text)
entry_value input can be converted to number types using to_num conversion functions for any validation
There should be a mandatory return parameter called formula_result that can take either ‘S’ (success) or ‘E’ (error)

Let us consider an example of validating an entry value which must be atleast 10 i.e. consider an HRA element where we will be using this validation to enforce a rule that the employee gets a minimum of 10% HRA. A simple formula validation may be as follows

  INPUTS are Entry_Value(text)
  FORMULA_RESULT = ‘S’
  IF TO_NUM(Entry_Value) < 10 THEN
  (
  FORMULA_RESULT = ‘E’
  FORMULA_MESSAGE = ‘Entry Value must be minimum 10 and above’
)
RETURN FORMULA_RESULT, FORMULA_MESSAGE

After attaching this above formula against hra_pct of test_hra and when used for an employee, the above validation is fired whenever we enter some screen entry value for the hra_pct. Notice that when hra_pct value entered is less than 10, application throws error from the formula_message but eventually saves the transaction.

The formula validation we try to do will merely give warning to the user and no way prevent one from overriding the formula validation.

Practice Notes: Payroll QuickPay

2010-04-27T14:07:00.005+05:30

Try running quick pay for an employee with an effective date in the past. The quick-pay results in error even though there is no payroll was run for this employee. Let situation be evaluated with a simple example.

On 27th Apr 2010, we create following objects as of 01-Jan-2000:

Create two recurring elements called test_salary and test_hra. The input values for test_salary are Pay Value and Basic Salary. Similarly for test_hra, Pay Value and hra_pct are the input values
Create a salary basis test_salary_basis including test_salary as its element
Write a formula test_formula which reads basic salary of test_salary and hra_pct of test_hra and computes the total salary which is equal to (monthly_salary + monthly_salary*hra_pct/100)
Attach this formula to test_salary and return the total_salary to Pay Value of test_salary using formula results form
Link both the element to a payroll test_payroll
Create a new employee 'Mahindrasingh Dhoni', create address and attach the test_salary_basis to his assignment. Add test_payroll to his payroll
Create a new salary for this employee effective from 01-Jan-2000. say $10000 basic per month
From Element entries form, add test_hra element to this employee
Run the quick pay for this employee as of 31-jan-2000

Above process typically results in error. The above process works perfectly when the same steps are repeated for effective date of today and for future periods whereas it does not work backwards even though we have not run any payroll for this employee later than 31-Jan-2000

Inference:

It is best to do all setup works in the past and start running the payroll from current date.

Short Writeup: Fast Formula - Overview

2010-04-23T11:02:00.016+05:30

Oracle Fast Formula can be written using formula form found in

Navigation -> Total Compensation -> Basic -> Fast Formula -> Write Formula

Once written, usually Fast formulas are attached to elements of Payroll for any computation. But FF can be of any type including payroll, accruals etc.

Different Components of Fast Formulas:

Input Statement
Assignment Statements
Return Statements

Data Types:
There are three types of data available in fast formula. They are text, numeric and date types. By default a variable is of numeric type. If the data type is not numeric then it must be specified like

INPUTS are x,y (date),z (text)
Fast Formula Variables:
Fast formula variables are of three types. Local, Global and database variables.

Variables usually get their values when values are input to them during a formula call or through database objects.

Formula Structure:
A simple formula may look like this with input, assignment and return statements.


INPUTS are x,y

x = y + 1

RETURN x

Note:

RETURN statement is used to return the specific value to outside usually a payroll run
There can be many RETURN statement in a formula but only one INPUTS statement is allowed in a formula
Values of input variables can not be changed
When the element input value is passed to the formula, the input name must be same as that of the input values of the element and the multiple words must be joined by an underscore.

Contexts:
Contexts are similar to environment variables. Since fast formulas are attached to elements, the contexts change for each assignment. Typical context info include

Business group
Element and element links
Employee and his assignments

Order of processing:
Calculation begins from inner most braces starting left to right.

Conditional Statements:

We can use If-else statement like we use it in any other languages.

IF  THEN
(

  statements
)
ELSE
( 
  statements
)

Here we can use the normal conditional operators and logical operators(AND,OR,NOT). There is one more clause called 'WAS DEFAULTED' which when used in if condition, results in true condition if the particular value passed is null.

For eg.


DEFAULT for x is 10

IF x WAS DEFAULTED THEN
(   
  statements
)
ELSE
(   
  statements
)

When no value/null value is passed to x then the condition evaluates to TRUE oterwise FALSE.

Comments:
The commenting is similar to that of pl/sql and C++
/* Comments */

Aliases:
The database items are often longer and referencing them in formulas becomes tedious without aliases.
ALIAS database_name AS alias_name

Fast Formula Functions:
FF Functions can be of following types.

Text Functions
Numeric Functions
Date Functions
Data conversion Functions
NULL Functions

Database items:
Database items are the procedures that exist in database. These items can be referenced in fast formula to arrive at the result values. There are two types of database items.

Static DB Items: are predefined and include standard type of information. For example, date of birth of an employee.
Dynamic DB Items: are generated by the definitions of elements, balances, absence types, flex field segments etc. For example, for each of the input value entry of an element one DB Item is created with preceding element name.

Short Writeup: Oracle Payroll Balances

2010-04-22T16:19:00.010+05:30

Balances in oracle payroll is nothing but addition/subtraction of 'run result values'. Considering a lay man example of showing year to date salary payment made for particular employee, it may be computed through summing up run result values generated after each payroll through specified time period or one could use Balances.

We could define our own balance or use one of the seeded balance to take particular element feeds.

Navigation -> Total Compensation -> Basic -> Banlances

Again, balances can be fed by individual elements or through element classification(at the larger level). We should note that both are mutually exclusive. Basic balance information is stored in PAY_BALANCE_TYPES table in R12 .

Dimension:
Dimensions talk about period context of balance i.e. in our previous example, 'one financial year' forms the dimension. similarly dimension can be quarter to date, month to date types. Dimension details are stored in PAY_BALANCE_DIMENSIONS.

Balance Feeds:
As we saw previously, balance feed is nothing but how element types are used to feed balances. The input values of element type is linked to balance type via PAY_BALANCE_FEEDS_F table.

Defined Balances:
Balances by itself does not hold the accrued balance values. Because the context in which balance is going to be used may change depending on the dimension with which we query it. Hence PAY_DEFINED_BALANCES links the valid combination of balance types and dimension types. This table has defined_balance_id as the primary key which will be extensively referred in latest balances.

Balance Dimension Types:
Usually balances are associated at the assignment level. But there are five different types of dimensions based on which the balances may be used differs.

N type - Not Fed, Not stored
F type - Fed, but not stored
A type - stored at assignment level
P type - stored at person level
R type - Run level balances

The balances for N and F types are not stored in the database tables whereas it is stored for other types.

Latest Balance:
In R12, the latest balances, be it in assignment level or at person level are stored commonly in a table called PAY_LATEST_BALANCES. The latest balances at assignment level are stored in PAY_ASSIGNMENT_LATEST_BALANCES and linked to PAY_LATEST_BALANCES through latest_balance_id. This table also holds assignment_id and assignment_action_id also. Similarly PAY_PERSON_LATEST_BALANCES is also linked to PAY_LATEST_BALANCES.

Retrieving Code Combination ID for any key flex fields

2010-04-20T14:42:00.006+05:30

It may required to validate the segments of key flex fields and retrieve the code combination id so that such ids may be used in APIs. Typical example includes validating soft coding key flex fields(in update_emp_asg), job,position, cost allocation key flex etc in element links API.

During conversion/interfacing type of projects we may be required to validate the kff descriptions and get back the code combination id. In such cases the following procedure may be useful. It internally uses hr_kflex_utility.ins_or_sel_keyflex_comb API
--- --- Name : validate_keyflex --- Description : takes the kff desciptions concatenated by ~ --- and returns the code combination id. Other --- mandatory parameters to be supplied are --- flex code and flex num --- Note : p_error_msg_io is just varchar2 type to take back --- generated error messages. --- PROCEDURE validate_keyflex( p_concat_des_i IN VARCHAR2 ,p_flex_code_i IN fnd_id_flex_segments.id_flex_code%TYPE ,p_flex_num_i IN fnd_id_flex_segments.id_flex_num%TYPE ,p_error_msg_io IN OUT VARCHAR2 ,p_cost_allocation_keyflex_id_o OUT pay_element_links_f.cost_allocation_keyflex_id%TYPE ,p_concat_segments_o OUT VARCHAR2 ) IS l_id_concat_segments pay_cost_allocation_keyflex.concatenated_segments%TYPE; l_segment_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE; l_application_shortname fnd_application.application_short_name%TYPE; TYPE flex_des_table_type IS TABLE OF fnd_flex_values_vl.description%TYPE INDEX BY PLS_INTEGER; TYPE flex_value_table_type IS TABLE OF fnd_flex_values.flex_value%TYPE INDEX BY PLS_INTEGER; TYPE flex_value_set_table_type IS TABLE OF fnd_flex_values_vl.flex_value_set_id%TYPE INDEX BY PLS_INTEGER; CURSOR value_set_cur IS SELECT flex_value_set_id FROM fnd_id_flex_segments WHERE id_flex_code=p_flex_code_i AND id_flex_num = p_flex_num_i ORDER BY segment_num; flex_des_table flex_des_table_type; flex_value_table flex_value_table_type; flex_value_set_table flex_value_set_table_type; l_index_des PLS_INTEGER; l_index_val PLS_INTEGER; l_count NUMBER := 1; l_loc NUMBER := 1; l_position NUMBER; l_des VARCHAR2(40); BEGIN ----------- Get the segment delimiter for the structure ---------------- SELECT concatenated_segment_delimiter INTO l_segment_delimiter FROM fnd_id_flex_structures WHERE id_flex_code = p_flex_code_i AND id_flex_num = p_flex_num_i; --DBMS_OUTPUT.PUT_LINE('Find Delimiter: Passed'); ------------ Get all value set id for the flex structure used ---------- OPEN value_set_cur; FETCH value_set_cur BULK COLLECT INTO flex_value_set_table; CLOSE value_set_cur; --DBMS_OUTPUT.PUT_LINE('Get value sets: Passed'); ------------ Populate Description table ---------------------------------- l_index_des := 1; LOOP l_position := instr(p_concat_des_i, '~', 1, l_loc); EXIT WHEN l_position = 0; l_des := substr(p_concat_des_i, l_count, l_position-l_count); flex_des_table(l_index_des) := l_des; l_count := l_position+1; l_loc := l_loc +1; l_index_des := l_index_des + 1; END LOOP; l_des := substr(p_concat_des_i, l_count, length(p_concat_des_i)-l_count+1); flex_des_table(l_index_des) := l_des; DBMS_OUTPUT.PUT_LINE('Get flex des table: Passed'); ---------- Populate value table for corresponding description --------- ---------- and create value concat segments ---------------------------- l_index_des := NULL; l_index_val := flex_des_table.FIRST; l_index_des := flex_value_set_table.FIRST; WHILE l_index_val IS NOT NULL AND l_index_des IS NOT NULL LOOP SELECT flex_value INTO flex_value_table(l_index_val) FROM fnd_flex_values_vl WHERE flex_value_set_id = flex_value_set_table(l_index_des) AND description = flex_des_table(l_index_val); ---Create concat segments with delimiter at the begining l_id_concat_segments := l_id_concat_segments || l_segment_delimiter||flex_value_table(l_index_val); l_index_val := flex_des_table.NEXT(l_index_val); l_index_des := flex_value_set_table.NEXT(l_index_des); END LOOP; -- Remove the preceeding delimiter l_id_concat_segments := substr(l_id_concat_segments, 2, length(l_id_concat_segments)); --DBMS_OUTPUT.PUT_LINE('Get concatenated flex ids: Passed'); ----------- Get the Application Short Name --------------------------- SELECT application_short_name INTO l_application_shortname FROM fnd_application WHERE application_id = ( SELECT application_id FROM fnd_id_flex_structures WHERE id_flex_code = p_flex_code_i AND id_flex_num = p_flex_num_i ); --DBMS_OUTPUT.PUT_LINE('Get Application Short Name: Passed'); --DBMS_OUTPUT.PUT_LINE('Des Segments: '|| p_concat_des_i ); --DBMS_OUTPUT.PUT_LINE('ID Segments: '|| l_id_concat_segments); ----------- Call Kflex utility to get the code combination id --------- apps.hr_kflex_utility.ins_or_sel_keyflex_comb( p_appl_short_name => l_application_shortname ,p_flex_code => p_flex_code_i ,p_flex_num => p_flex_num_i ,p_concat_segments_in => l_id_concat_segments ,p_ccid => p_cost_allocation_keyflex_id_o ,p_concat_segments_out => p_concat_segments_o ); DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Passed'); EXCEPTION WHEN TOO_MANY_ROWS THEN p_error_msg_io := p_error_msg_io ||'KeyFlex:Ambiguous Values~'; DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Failed'); p_cost_allocation_keyflex_id_o := NULL; p_concat_segments_o := NULL; WHEN NO_DATA_FOUND THEN p_error_msg_io := p_error_msg_io ||'KeyFlex:Invalid parameter passed~'; DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Failed'); p_cost_allocation_keyflex_id_o := NULL; p_concat_segments_o := NULL; WHEN others THEN p_error_msg_io := p_error_msg_io ||' '|| SQLERRM|| '~'; DBMS_OUTPUT.PUT_LINE('Key Flex Validation: Failed'); p_cost_allocation_keyflex_id_o := NULL; p_concat_segments_o := NULL; END validate_keyflex;
The above code simply assumes that the validation sets used by the kffs are of either independent/dependent types. And the code is open for scrutiny.