Forum Stats

  • 3,874,184 Users
  • 2,266,680 Discussions
  • 7,911,761 Comments

Discussions

How can I make a conditional report in Oracle APEX?

Hello, I have an interactive report, but I need a conditional that executes one of two SQL depending on the user role.


1. If the role is administrator you must use the following query:

  select 
    FQ7076E3.FEQ70NN as "Numero_Comprobante",
    FQ7076E3.FEQ70NAU2 as "Numero_Autorizacion",
    F0101.ABTAX as "RUC_Emisor",
    to_date(to_date(1900000 + FQ7076E3.FEIVD, 'yyyyddd'),'DD/MM/YYYY') as "Fecha_Emision",
    to_date(to_date(1900000 + FQ7076E3.FEUPMJ, 'yyyyddd'),'DD/MM/YYYY') as "Fecha_Autorizacion",
    FQ7076E3.FETAX as "ID_Fiscal",
    FQ7076E3.FEALPH as "Razon_Social",
    DOCUMENT_TYPE_1,
    sys.dbms_lob.getlength("DOCUMENT_TYPE_1")"Archivo_PDF",
    DOCU.DOCUMENT_FILENAME_1,
    DOCU.DOCUMENT_MIMETYPE_1,
    DOCUMENT_TYPE_2,
    sys.dbms_lob.getlength("DOCUMENT_TYPE_2")"Archivo_XML",
    DOCU.DOCUMENT_FILENAME_2,
    DOCU.DOCUMENT_MIMETYPE_2
 from F0101 F0101,
    F0010 F0010,
    FQ7076E3 FQ7076E3,
    F_ASR_DOCUMENTS DOCU
 where FEDL02='03' 
    and FEQ70DTP='2'
    and FQ7076E3.FECO=F0010.CCCO
    and F0010.CCAN8=F0101.ABAN8
    and FQ7076E3.FEQ70NN = DOCU.DOCUMENT_ID(+)

2. If the role is client it should execute the following query:

  select 
    FQ7076E3.FEQ70NN as "Numero_Comprobante",
    FQ7076E3.FEQ70NAU2 as "Numero_Autorizacion",
    F0101.ABTAX as "RUC_Emisor",
    to_date(to_date(1900000 + FQ7076E3.FEIVD, 'yyyyddd'),'DD/MM/YYYY') as "Fecha_Emision",
    to_date(to_date(1900000 + FQ7076E3.FEUPMJ, 'yyyyddd'),'DD/MM/YYYY') as "Fecha_Autorizacion",
    FQ7076E3.FETAX as "ID_Fiscal",
    FQ7076E3.FEALPH as "Razon_Social",
    DOCUMENT_TYPE_1,
    sys.dbms_lob.getlength("DOCUMENT_TYPE_1")"Archivo_PDF",
    DOCU.DOCUMENT_FILENAME_1,
    DOCU.DOCUMENT_MIMETYPE_1,
    DOCUMENT_TYPE_2,
    sys.dbms_lob.getlength("DOCUMENT_TYPE_2")"Archivo_XML",
    DOCU.DOCUMENT_FILENAME_2,
    DOCU.DOCUMENT_MIMETYPE_2
 from F0101 F0101,
    F0010 F0010,
    FQ7076E3 FQ7076E3,
    F_ASR_DOCUMENTS DOCU
 where FEDL02='03' 
    and FEQ70DTP='2'
    and FQ7076E3.FECO=F0010.CCCO
    and F0010.CCAN8=F0101.ABAN8
    and FQ7076E3.FEQ70NN = DOCU.DOCUMENT_ID(+)
    and trim(FQ7076E3.FETAX) = Trim(coalesce(sys_context('APEX$SESSION','APP_USER'),user))

At the moment I only had it for the client user which is point two, but I was told that I should adjust the application to the admin role which can see all the records. But I can not find information on how to do I thought in PL/SQL, but I think it is not possible I do not know what other alternative there is.

I hope you can help me, thank you

Tagged:

Answers

  • GaneshNimkar
    GaneshNimkar Member Posts: 1 Green Ribbon

    You can use Function body returning SQL query option in report source section.

    Where you can compare role using IF...Else and return the respective query on condition match.

    Ex-

    declare

    v_sql varchar2(4000);

    begin


    if :p3_a = 1 then

    v_sql := 'select dname d, deptno r from dept';

    else

    v_sql := 'select dname d, deptno r from eba_demo_ir_dept';

    end if;

    return v_sql;

    end;

    SmithJohn45vikrant_sri
  • User_8LEFJ
    User_8LEFJ Member Posts: 1 Green Ribbon
    edited Nov 25, 2022 6:13AM

    Hi

    i use this code and it work fine but i change it like below and i got error "data not fount "

    when i change app_user to user_name wich in table users it work fine but i nead to check by login user

    could help me please,thank you .

    declare


    v_sql varchar2(4000); r1 INT;


    begin


    select role_id into r1 from myusers where username=:app_user;

    (if i change :app_user to 'OBAID' for example it work fine .


    if r1 != 24 then


    v_sql := 'select "EXPENSEID","EXPENSETYPEID","EXPENSEDATE","BANKID" from "EXPENSESDETAILS"';


    else


    v_sql := 'select "EXPENSEID","EXPENSETYPEID","EXPENSEDATE","BANKID" from "EXPENSESDETAILS" where adduser=:app_user';


    end if;


    return v_sql;


    end;