topic Re: Oracle Function into Qlikview in QlikView

Oracle Function into Qlikview

nilesh_gangurde — Tue, 31 Dec 2013 08:58:54 GMT

Hii,

how we can impement the logic mentioned below into Qlikview. Below is the oracle function which uses the Cursors to do some calculations. Need to implement the same calculations in Qlikview.

CREATE OR REPLACE FUNCTION APPS.FUNCTION_NAME (

month_frm NUMBER,

year_frm NUMBER

)

RETURN NUMBER

CURSOR cur_div

SELECT DISTINCT division_code

FROM target_header

ORDER BY 1;

CURSOR cur (p_division NUMBER)

SELECT DISTINCT division_code, ROUND (AVG (job_gp_pro), 5) avg_gp_pro,

month_no month_val, YEAR year_val

FROM target_header a, target_lines b

WHERE a.header_id = b.header_id

AND month_no = month_frm

AND YEAR = year_frm

AND division_code = p_division

GROUP BY division_code, month_no, YEAR

ORDER BY 1;

l_actual_val NUMBER := 0;

l_gp_val NUMBER := 0;

l_total_val NUMBER := 0;

l_actual_tot NUMBER := 0;

l_result_val NUMBER := 0;

BEGIN

FOR r_cur_div IN cur_div

LOOP

FOR r_cur IN cur (r_cur_div.division_code)

LOOP

BEGIN

SELECT NVL2(SUM(pail.inv_amount),SUM(pail.inv_amount),1)--NVL (SUM (pail.inv_amount), 0)

INTO l_actual_val

FROM pa_draft_invoices_v pain, pa_draft_invoice_lines_v pail

WHERE pail.project_id = pain.project_id

AND pail.draft_invoice_num = pain.draft_invoice_num

AND pain.project_organization_id = fnd_profile.VALUE ('ORG_ID')

AND TRUNC (pain.gl_date)

BETWEEN TO_DATE ( '01/'

|| LPAD (r_cur.month_val, 2, 0)

|| r_cur.year_val,

'dd/mm/rrrr'

)

AND TO_CHAR

(LAST_DAY (TO_DATE ( '01/'

|| LPAD (r_cur.month_val,

)

|| r_cur.year_val,

'dd/mm/rrrr'

)

AND pain.invoice_status_code = 'ACCEPTED'

AND pail.invoice_line_type = 'STANDARD'

AND pain.project_id IN (

SELECT ppa.project_id

FROM pa_projects_all ppa,

pa_project_classes ppc,

pa_project_players ppp

WHERE ppa.project_id = ppc.project_id

AND ppp.project_id = ppa.project_id

AND UPPER (ppc.class_category) = 'DIVISION'

AND SUBSTR (ppc.class_code, 1, 2) =

NVL (SUBSTR (r_cur.division_code, 1, 2),

SUBSTR (ppc.class_code, 1, 2)

));

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

l_actual_tot := l_actual_tot + l_actual_val;

l_total_val :=

l_total_val

+ (l_actual_val * (r_cur.avg_gp_pro / 100));

BEGIN

l_result_val := (l_total_val / NVL (l_actual_tot, 1) * 100);

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

END LOOP;

RETURN (ROUND (l_result_val, 5));

END;

Re: Oracle Function into Qlikview

Anonymous — Tue, 31 Dec 2013 09:03:49 GMT

You can easily call this function in a SQL query>>

SQL select Apps.function_name(10,13) from dual;

This will return a numeric value as returned by your function and you can store it in qvd or access it in qlikview.

Regards

Nitin

Re: Oracle Function into Qlikview

sujeetsingh — Tue, 31 Dec 2013 09:04:04 GMT

nilesh just stick up with this link .

It will help you .

http://community.qlik.com/thread/74382

Re: Oracle Function into Qlikview

nilesh_gangurde — Tue, 31 Dec 2013 09:07:52 GMT

Hii,

Yes Nitin, We can do that. but this will store only one value into Qvd. if we want it for all the months then what will be the logic.???

-Nilesh

Re: Oracle Function into Qlikview

Anonymous — Tue, 31 Dec 2013 09:11:05 GMT

Select year,month,

apps.function(month,year)

from table name;

This query will definitely provide your the desired results.

Keep a note that month and year should be numeric in your table also..

If not you can easily change it in oracle..

Regards

Nitin

Re: Oracle Function into Qlikview

nilesh_gangurde — Tue, 31 Dec 2013 09:17:31 GMT

HI,

if you can see in the above Query multiple tables are used, there is no specific table from which we can extract data.

hence we cant write the above Query.

is there any other way.

-Nilesh

Re: Oracle Function into Qlikview

Anonymous — Tue, 31 Dec 2013 09:29:02 GMT

I think I was unable to explain you correctly,

I think you need this calculation(via function) for each year month...

and you might be generating a table with year month as dimension and calcualted value as expression

For example>>

Create a calendar table which would definately contain month and year as....

Year Month Quarter

2012 11 Q4

2012 12 Q4

2013 1 Q1
2013 2 Q1

2013 3 Q1

2013 4 Q2

Etc ....................

then you can use these columns(Year Month) in the query and you will easily get the calculated value for each year and month.

_______________________________________________________________________________________________

===================================================================================

I think , you also might be loading some other table which might contain year and month, then you can use those fields for the same.

function will not effect the output of you query , it will just add a column with your desired calculated value for each year and month......

Regards

Nitin