Skip to Main Content

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

Comments
Post Details
Added on Dec 10 2021
2 comments
379 views