Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle APEX 5.1 Interactive Grid - LOV contents should be different for each row

franklin.jrJan 31 2017 — edited Feb 13 2017

Dear friends, I have an interactive grid for Oracle APEX 5.1 and one of its columns has an LOV.

This LOV is based on a SQL query which invokes, among other criteria, a package.function with parameters such as : CD_ITEM1 and : CD_ITEM2 from other columns at the same interactive grid, as well as : P1_CD_PARAMETER and : P2_CD_PARAMETER which are page items.

It should pop up a LOV which displays, for example, 1, 2, 3 depending on parameters at one row, and 4, 5, 6 depending on parameters at another row.

However, this LOV does not seem to work - it does not filter any value in any column and it brings all LOV values for each row. Do you know any reason for that?

Thanks in advance.

Comments

John Snyders-Oracle

Perhaps you can share the SQL you are using and also verify that you have set Items to Submit to include all the parameters that your query uses.

Regards,

-John

franklin.jr

Please notice this SQL query 1) uses page items such as :P3_CD_CREDENCIADO; 2) uses editable interactive grid items such as :CD_SERVICO; 3) invokes a package.function with parameters. Thanks for helping. I do need lots of help on issues like this, especially because I am working alone on this project.

select hvs.ds_servico,

       hvs.cd_servico

  from hc_v_servico hvs

where (:P3_TP_SISTEMA_ATEND = 'REEMB'

        or nvl (:P3_CD_CREDENCIADO_CALCULO, :P3_CD_CREDENCIADO) is NULL

        or nvl (:P3_CD_CREDENCIADO_CALCULO, :P3_CD_CREDENCIADO) is not NULL

        and exists (select 1

                      from hc_credenciado_valid_servico hcvs

                     where hvs.cd_tabela              = hcvs.cd_tabela

                       and hcvs.dt_inicio_vigencia <= to_char (:DT_ATENDIMENTO_ITEM, 'dd/mm/yyyy')

                       and hcvs.cd_credenciado      = nvl (:P3_CD_CREDENCIADO_CALCULO, :P3_CD_CREDENCIADO)

                       and nvl (hcvs.dt_termino_vigencia, to_char (:DT_ATENDIMENTO_ITEM, 'dd/mm/yyyy')) >= to_char (:DT_ATENDIMENTO_ITEM, 'dd/mm/yyyy')

                       and PKG_HC_INTEGRACAO_ATENDIMENTO.f_verifica_lib_servico (hvs.cd_servico,

                                                                                 hcvs.cd_tabela,

                                                                                 :P3_CD_CREDENCIADO,

                                                                                 :P3_CD_CREDENCIADO_CALCULO,

                                                                                 to_char (:DT_ATENDIMENTO_ITEM, 'dd/mm/yyyy')) >= 1)

                   or (hvs.tp_sistema_atend = 'AMBOS' or hvs.tp_sistema_atend = :P3_TP_SISTEMA_ATEND))

   and hvs.fg_ult_nivel = 'S'

   and (:CD_MASCARA_SERVICO is NULL or hvs.cd_mascara like :CD_MASCARA_SERVICO || '%')

   and (:CD_SERVICO is NULL or hvs.cd_servico = :CD_SERVICO)

JurijC

Have you set the value of the attribute "Cascading LOV Parent Column(s)" of the item where you are adding the LOV to the items you are referencing?

franklin.jr

JurijC escreveu:

Have you set the value of the attribute "Cascading LOV Parent Column(s)" of the item where you are adding the LOV to the items you are referencing?

I tried doing it this morning. APEX gave me :CD_MASCARA_SERVICO, :CD_SERVICO AND : DT_ATENDIMENTO_ITEM to cascade. And, when I pressed the LOV popup button, this was the result.

ERR-1002 Unable to find item ID for item "C9544657287034861" in application "101".

franklin.jr

I found a workaround for all this situation... I thought grid parameters would be dynamically passed for each row being rendered during page load, but it didn't happen, and I don't know why. In Oracle Forms I can easily create a POST-QUERY trigger so I can use retrieved code to both validate the value and retrieve its description when applicable. But in Oracle APEX editable interactive grids? I am working alone and doing what I can to understand it.

  1. For the column on the editable interactive grid, I modified the popup LOV in this column so now it is enterable (it shows only the code, not the description).

  2. The column is based on "PL/SQL Function Body returning SQL Query", and this function receives parameters. The advantage of this approach is that it allowed me to debug the page rendering, showing what was being returned from the function - and all grid columns sent NULL values to the function which returns LOV.

  3. Since I was unable to pass "Cascading LOV Parent Column(s)" attribute (for it was causing "ERR-1002 Unable to find item ID for item" error), I had to create a dynamic action which fires on "Get Focus" for this column.

  4. When clicked column gets focus, it executes a PL/SQL which submits grid columns and returns page items. It attributes : P3_PAGE_ITEM := :GRID_COLUMN.

  5. "PL/SQL Function Body returning SQL Query" for the column on item 1 will pass only page items, instead of some grid columns, to the function.

It works for now, and I will fine tune it. But it is disappointing to see that I was unable to make it easier.

(For a grid column which is "popup LOV", this column does not raise "get focus" dynamic action when I click on its "up" arrow.)

Also, please notice the rendering imprecision on my grid below:

pastedImage_0.png

franklin.jr

Sorry people, I just want to understand if I just described above an issue/bug on Editable Interactive Grid, or if I am missing any detail which has misled me, or if the whole picture was not clear. Anyhow I simply cannot understand why I cannot have different values for each row, based on each column value, passing a function with parameters which come from both items and columns (shouldn't popup lovs and select lists have it? Sorry I just want to understand what goes on).

My case is complex to be reproduced on apex.oracle.com and my 1st attempt has failed, but I will try again. Just ask you to have your say in the meanwhile.

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 13 2017
Added on Jan 31 2017
6 comments
2,789 views