Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
IS
CURSOR cur_div
IS
SELECT DISTINCT division_code
FROM target_header
ORDER BY 1;
CURSOR cur (p_division NUMBER)
IS
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,
2,
0
)
|| 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;
END LOOP;
RETURN (ROUND (l_result_val, 5));
END;
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
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
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
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
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