Trying to Understand PLSQL Function
- by Rachel
I am new to PLSQL and I have this huge plsql function which am trying to understand and am having hard time understanding the flow and so I would really appreciate if anyone can run me through the big pieces so that I can understand the flow. Guidance would be highly appreciated.
FUNCTION monthly_analysis(
REGION_ID_P VARCHAR2,
COUNTRY_ID_P VARCHAR2 ,
SUB_REGION_ID_P VARCHAR2 ,
CUSTOMER_TYPE_ID_P VARCHAR2 ,
RECEIVED_FROM_DATE_P VARCHAR2 ,
RECEIVED_TO_DATE_P VARCHAR2,
CUSTOMER_ID_P VARCHAR2 ,
PRIORITY_ID_P VARCHAR2,
WORK_GROUP_ID_P VARCHAR2,
CITY_ID_P VARCHAR2,
USER_ID_P VARCHAR2
) RETURN AP_ANALYSIS_REPORT_TAB_TYPE pipelined
IS
with_sql LONG;
e_sql LONG;
where_sql LONG;
group_by_sql LONG;
curent_date Date;
v_row AP_ANALYSIS_REPORT_ROW_TYPE := AP_ANALYSIS_REPORT_ROW_TYPE(
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
TYPE rectyp IS REF CURSOR; -- define weak REF CURSOR type
rrc_rectyp rectyp;
TYPE recordvar IS RECORD(
MONTHS VARCHAR2(100),
ORDERBY_MONTHS VARCHAR2(100),
REQ_RECEIVED NUMBER(9,2),
REQ_STILL_OPEN NUMBER(9,2),
REQ_AWAIT_ACCEPTANCE NUMBER(9,2),
REQ_WITH_ATT NUMBER(9,2),
REQ_CLOSED NUMBER(9,2),
REQ_CANCELLED NUMBER(9,2)
);
res_rec recordvar;
BEGIN
select sysdate +substr(to_char(systimestamp, 'tzr'),3,1)/24 into curent_date from dual;
where_sql := ' AND 1=1 ';
IF COUNTRY_ID_P IS NOT NULL THEN
where_sql := where_sql ||' AND x.country_id ='|| COUNTRY_ID_P;
END IF;
IF SUB_REGION_ID_P IS NOT NULL THEN
where_sql := where_sql ||' AND x.SUB_REGION_ID ='|| SUB_REGION_ID_P;
END IF;
IF CUSTOMER_TYPE_ID_P IS NOT NULL THEN
where_sql := where_sql ||' AND x.CUSTOMER_TYPE_ID ='|| CUSTOMER_TYPE_ID_P;
END IF;
IF RECEIVED_FROM_DATE_P IS NOT NULL THEN
where_sql := where_sql||' AND convert_time(received_date, ''GMT'', ''GMT'') >= convert_time(trunc(to_date('''||RECEIVED_FROM_DATE_P||''',''dd/mm/yyyy HH24:MI:SS'')), ''Europe/Paris'', ''GMT'')';
END IF;
IF RECEIVED_TO_DATE_P IS NOT NULL THEN
where_sql := where_sql||' AND convert_time(received_date, ''GMT'', ''GMT'') <= convert_time(trunc(to_date('''||RECEIVED_TO_DATE_P||''',''dd/mm/yyyy HH24:MI:SS'')), ''Europe/Paris'', ''GMT'')';
END IF;
IF CUSTOMER_ID_P IS NOT NULL THEN
where_sql := where_sql||' AND x.CUSTOMER_ID in(select CUSTOMER_ID from lk_customer where upper(CUSTOMER_NAME) like upper('''||CUSTOMER_ID_P||'%''))';
END IF;
IF PRIORITY_ID_P IS NOT NULL THEN
where_sql := where_sql ||' AND x.PRIORITY_ID ='|| PRIORITY_ID_P;
END IF;
IF WORK_GROUP_ID_P IS NOT NULL THEN
where_sql := where_sql ||' AND x.WORKGROUP_ID ='|| WORK_GROUP_ID_P;
END IF;
IF CITY_ID_P IS NOT NULL THEN
where_sql := where_sql ||' AND x.CITY_ID = ' || CITY_ID_P;
END IF;
group_by_sql := ' group by to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''mm/YYYY''),to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''yyyy/mm'')';
with_sql := 'with
b AS (select cep_work_item_no from ap_main where req_accept_date is null and ecep_ap_utils.f_business_days(received_date,'''||curent_date||''')>30),
e AS (select cep_work_item_no from ap_main where status_id=1 and req_accept_date is not null and stage_ID != 10 and stage_Id !=4 and ecep_ap_utils.f_business_days(received_date,'''||curent_date||''')>30),
--f AS (select cep_work_item_no from ap_main where received_date is not null),
m AS (select cep_work_item_no from ap_main where received_date is not null and status_id=1),
n AS (select cep_work_item_no from ap_main where status_id=2),
o AS (select cep_work_item_no from ap_main where status_id=3)';
--e_sql := ' SELECT MONTHS, REQ_RECEIVED,REQ_STILL_OPEN, REQ_AWAIT_ACCEPTANCE, REQ_WITH_ATT from (';
--e_sql := with_sql;
e_sql := with_sql||' select to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''mm/YYYY'') MONTHS, to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''yyyy/mm'') ORDERBY_MONTHS,
count(x.cep_work_item_no) REQ_RECEIVED,
count(m.cep_work_item_no) REQ_STILL_OPEN,count(b.cep_work_item_no) REQ_AWAIT_ACCEPTANCE,count(e.cep_work_item_no) REQ_WITH_ATT,
count(n.cep_work_item_no) REQ_CLOSED, count(o.cep_work_item_no) REQ_CANCELLED
from emea_main x,m,b,e,n,o where x.cep_work_item_no=m.cep_work_item_no(+)
and x.cep_work_item_no = b.cep_work_item_no(+) and x.cep_work_item_no=e.cep_work_item_no(+) and
x.cep_work_item_no=n.cep_work_item_no(+) and x.cep_work_item_no=o.cep_work_item_no(+)
and x.received_date is not null';
e_sql := e_sql|| where_sql||group_by_sql;
OPEN rrc_rectyp FOR e_sql;
LOOP
FETCH rrc_rectyp INTO res_rec;
EXIT WHEN rrc_rectyp%NOTFOUND;
v_row.MONTHS := res_rec.MONTHS ;
v_row.ORDERBY_MONTHS := res_rec.ORDERBY_MONTHS ;
v_row.REQ_RECEIVED := res_rec.REQ_RECEIVED;
v_row.REQ_STILL_OPEN := res_rec.REQ_STILL_OPEN;
v_row.REQ_AWAIT_ACCEPTANCE := res_rec.REQ_AWAIT_ACCEPTANCE;
v_row.REQ_WITH_ATT := res_rec.REQ_WITH_ATT;
v_row.REQ_CLOSED := res_rec.REQ_CLOSED;
v_row.REQ_CANCELLED := res_rec.REQ_CANCELLED;
pipe ROW(v_row);
END LOOP;
RETURN;
END monthly_analysis;
And would also appreciate if someone can let me know as to what are the important plsql concepts used here so that I can go ahead and understand them in a better way and some small explanation would go long way.
As suggested by dcp, i am trying to use debugger, again I have not used it before and so pardon me, here is what am getting:
DECLARE
REGION_ID_P VARCHAR2(200);
COUNTRY_ID_P VARCHAR2(200);
SUB_REGION_ID_P VARCHAR2(200);
CUSTOMER_TYPE_ID_P VARCHAR2(200);
RECEIVED_FROM_DATE_P VARCHAR2(200);
RECEIVED_TO_DATE_P VARCHAR2(200);
CUSTOMER_ID_P VARCHAR2(200);
PRIORITY_ID_P VARCHAR2(200);
WORK_GROUP_ID_P VARCHAR2(200);
CITY_ID_P VARCHAR2(200);
USER_ID_P VARCHAR2(200);
v_Return GECEPDEV.AP_ANALYSIS_REPORT_TAB_TYPE;
BEGIN
REGION_ID_P := NULL;
COUNTRY_ID_P := NULL;
SUB_REGION_ID_P := NULL;
CUSTOMER_TYPE_ID_P := NULL;
RECEIVED_FROM_DATE_P := NULL;
RECEIVED_TO_DATE_P := NULL;
CUSTOMER_ID_P := NULL;
PRIORITY_ID_P := NULL;
WORK_GROUP_ID_P := NULL;
CITY_ID_P := NULL;
USER_ID_P := NULL;
v_Return := ECEP_AP_REPORTS.MONTHLY_ANALYSIS(
REGION_ID_P => REGION_ID_P,
COUNTRY_ID_P => COUNTRY_ID_P,
SUB_REGION_ID_P => SUB_REGION_ID_P,
CUSTOMER_TYPE_ID_P => CUSTOMER_TYPE_ID_P,
RECEIVED_FROM_DATE_P => RECEIVED_FROM_DATE_P,
RECEIVED_TO_DATE_P => RECEIVED_TO_DATE_P,
CUSTOMER_ID_P => CUSTOMER_ID_P,
PRIORITY_ID_P => PRIORITY_ID_P,
WORK_GROUP_ID_P => WORK_GROUP_ID_P,
CITY_ID_P => CITY_ID_P,
USER_ID_P => USER_ID_P
);
-- Modify the code to output the variable
-- DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
Can anyone guide me through this query and its goal ?
