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;
