Trying to Understand PLSQL Function

Posted by Rachel on Stack Overflow See other posts from Stack Overflow or by Rachel
Published on 2010-12-29T20:29:51Z Indexed on 2010/12/29 20:55 UTC
Read the original article Hit count: 136

Filed under:
|
|

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 ?

© Stack Overflow or respective owner

Related posts about sql

Related posts about Oracle