Hi,
I had asked a question earlier in one of the threads.
The details are in thread here.
https://community.oracle.com/tech/developers/discussion/4484286/oracle-apex-modfiy-grid-query-to-fetch-previous-quarter-data-if-current-not-available#latest
Requirement was to write query for interactive grid that would return previous quarter data if current quarter was unavailable.
To resolve i created function in database returning sql which implements same logic. But i am not sure how to use it in apex application.
My current IG query is:
SELECT DEPT_ID,DEPT_QUARTER_ID,
,KPI_1,KPI_2,KPI_3,KPI_4,KPI_5
from DEPT_DATA
WHERE
DEPT_QUARTER_ID=:DEPT_QUARTER_ID
AND PROJECT = :P1_PROJECT
I need to modifiy this to use the function mentioned below retuning the sql .How can i do that?
As mentioned in thread, for project variable p_id, there is application item :PROJECT.
For year and quarter, i have app items A_YEAR AND A_QUARTER.
So function would be something like:
Q_VALUE(:A_YEAR,A_QUARTER, :PROJECT, :P1_DEPT --P1_DEPT is page item for departments
But how do i use this as source of IG?
create or replace FUNCTION Q_VALUE (P_YEAR IN NUMBER, P_QUARTER IN NUMBER,P_COLUMN IN VARCHAR2, P_ID IN NUMBER, P_DEPT IN VARCHAR2 )
RETURN VARCHAR2
IS
V_YEAR NUMBER;
V_QUARTER NUMBER;
V_PK NUMBER;
sql_stmt VARCHAR2(10000);
V_VAL VARCHAR2(1000);
BEGIN
IF P_QUARTER =1 THEN
V_YEAR :=P_YEAR-1;
V_QUARTER :=4
BEGIN SELECT QUARTER_PK
INTO V_PK
FROM DEPT_QUARTER_DATA
WHERE PROJECT = P_ID
AND QUARTER = TO_CHAR(V_QUARTER)
AND YEAR = V_YEAR;
EXCEPTION WHEN OTHERS THEN
V_PK := NULL;
END;
IF V_PK IS NOT NULL THEN
sql_stmt := ' SELECT '||P_COL|| ' FROM DEPT_DATA WHERE DEPT_QUARTER_ID= '''||V_PK||''' AND DEPT_ID='''||P_DEPT||''' ;
BEGIN
EXECUTE IMMEDIATE sql_stmt INTO V_VAL ;
EXCEPTION WHEN OTHERS THEN
V_VAL := NULL;
END;
END IF;
ELSE
V_QUARTER :=P_QUARTER-1; //fetches from prev quarter
BEGIN
SELECT QUARTER_PK
INTO V_PK
FROM DEPT_QUARTER_DATA
WHERE PROJECT = P_ID
AND QUARTER = TO_CHAR(V_QUARTER)
AND YEAR = V_YEAR;
EXCEPTION WHEN OTHERS THEN
V_PK := NULL;
END;
IF V_PK IS NOT NULL THEN sql_stmt := ' SELECT '||P_COL|| ' FROM DEPT_DATA WHERE DEPT_QUARTER_ID= '''||V_PK||''' AND DEPT_ID='''||P_DEPT||''' ;
BEGIN
EXECUTE IMMEDIATE sql_stmt INTO V_VAL ;
EXCEPTION WHEN OTHERS THEN
V_VAL := NULL;
END;
END IF;
RETURN V_VAL;
END;