Practice Notes: Query for DAC metadata

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

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.

Practice Notes: UTL_FILE mess

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

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

1. There should be only one INPUT for the formula named ENTRY_VALUE of text data type i.e. INPUTS are entry_value(text)
2. entry_value input can be converted to number types using to_num conversion functions for any validation
3. 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

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.