This discussion is archived
1 2 Previous Next 29 Replies Latest reply: May 23, 2013 3:01 PM by Denes Kubicek Go to original post RSS
  • 15. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    yes, I remember checking that with you. I will remove, but am curious then how the input[name='fxx' will know that fxx=column (for example DISPOSITION, PRICE, etc).  Should I also be replacing input[name='f09 with input[name=REPORTED_QUANTITY and select[name="f08" with select[name='DISPOSITION_CODE'

    I have removed the HTML Expression from DISPOSITION and changed to SELECT LIST (named LOV). 

    THe jquery selector is SELECT[name="f08"

    it does not appear that the DA is firing.  thanks

    Edited by: KarenH on May 21, 2013 10:05 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 16. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    I changed the format for jquery selector (in dynamic action) to: select[name=#DISPOSITION_CODE#],

    that changes the P113_ID, but not the P113_VALUE.

    the DA currently has the following true actions:

    1. set value P113_ID javascript--> this.triggeringElement.id
    2. set value P113_VALUE javascript --> this.triggeringElement.value
    3. set value P113_SEQ javascript --> $('#'+'f01_'+$('#P113_ID').val().substring(4)).val();
    4. pl/sql code -->
     declare
    
      v_member number;
    
    begin 
      v_member := TO_NUMBER (SUBSTR (:p113_id, 2, 2));
    
      safis_collections.update_column(:p113_seq,
                                    v_member,
                                    :p113_value);
    end;
    could you take a look at the actions....I think there must be something off on how I have set up P113_SEQ. It seems to always remain at 2.

    thanks!

    ps. the package safis_collections.update_columns seems to be working properly
    procedure update_column(v_seq in number,
                            v_attr in number,
                            v_value in varchar2)
    is
    
    begin
       apex_collection.update_member_attribute (p_collection_name=> 'SPECIES_COLLECTION',
                         p_seq => v_seq,
                         p_attr_number => v_attr,
                         p_attr_value  => v_value);
    apex_debug.message('updated column '||v_attr||' with '||v_value);
    
    
    end;
    Edited by: KarenH on May 21, 2013 11:16 AM

    Edited by: KarenH on May 21, 2013 11:18 AM

    Edited by: KarenH on May 21, 2013 12:15 PM
  • 17. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Karen,

    Take a look at what exactly? If you do it the way I explained it, it should work. Do you use firebug to determine the right array for your columns? You should leave the element names as they are (f08, f09,...). See this posting on how to use firebug:

    http://deneskubicek.blogspot.de/2012/06/apex-tabular-form-and-ora-01403-no-data.html

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 18. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    this is the set value that does not seem to be working.


    set value P113_SEQ javascript --> $('#'+'f01_'+$('#P113_ID').val().substring(4)).val();


    I have about 25 columns in the species_collection which is why I moved to using a view therefore making it simplier for future programmers of this application to debug. f08,f09,f10 may be clear to some, but not for me...which is why I am using DISPOSITION_CODE, REPORTED_QUANTITY, etc.

    if there is a reason to use f08, etc, then that makes sense...otherwise, I think it will be easier for us to use the colum names that relate to the view.

    I suspect that the reference to f01 in the above assignment is not working properly.

    Thanks for your help. I can sense you are frustrated by my questions, as am I, but do appreciate your feedback.

    Karen
  • 19. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    If you have a delete checkbox in your tabular form then the f01 is the name of that array. My example is using the SEQ_ID column as a hidden column and since I have no checkbox there, this column gets the f01 assigned automaticaly. The positioning of the columns are deciding which name the element will have (f01 for checkbox, f02 for the hidden key, ...). I don't think that using column names will work. I am not frustrated by your qestions. I am just repeating that you should read what I write and follow the instructions as they are described. Later, when you managed to make it working and understand how it works and what it does, you may experiment.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 20. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    thanks Denes.

    still trying...and I must be close. I appreciate your patience....clearly I am the one who is $%#@ frustrated by my questions.

    I now recievean error when I change the DISPOSITION_CODE (or any value). Both P113_ID and P113_VALUE are sucessfully assigned.

    ajac called returned server error ORA-20213 Member Sequence does not exist in collection SPECIES_COLLECTION.


    here is what I have as Dynamic Action - true action #3 to set SEQ_ID: $('#'+'f01_'+$('#P113_ID').val().substring(4)).val();


    and here is what the tabular form query looks like:
    select 
    null delete_link,
    "SEQ_ID",
    "LANDING_SEQ",
    "SPECIES_ITIS",
    "GRADE_CODE",
    "MARKET_CODE",
    "UNIT_MEASURE",
    "DISPOSITION_CODE",
    "GEAR_CODE",
    "REPORTED_QUANTITY",
    "PRICE",
    "DOLLARS",
    "AREA_CODE_FLAG",
    "C014",
    "ADDITIONAL_MEASURE_FLAG",
    "ADDNL_REPORTED_QUANTITY",
    "ADDNL_UNIT_QUANTITY",
    "FINS_FLAG",
    "FINSATTACHED",
    "FINSNOTATTACHED",
    "FINSUNKNOWN",
    "FINS_CODE",
    "AREA_FISHED",
    "SUB_AREA_FISHED",
    "LOCAL_AREA_CODE",
    "EXPLANATION",
    "NATURE_OF_SALE",
    "HMS_AREA_CODE",
    "SALE_PRICE",
    "HMS_FLAG"
    from "#OWNER#"."LANDINGS_COLLECTION_VIEW"
    and finally, here is the process which sets up the collection to begin with:
    declare
        found number;
        seqid number;
        totalvalue number;
        licenseval VARCHAR2(75);
        areaInformation VARCHAR2(75);
        pCount number;
        pos number;
        yes_are number;
        found_area number;
        found_unit number;
        found_hms number;
        vesspermit varchar2(30);
        portcode varchar2(30);
        port_partner varchar2(4);
        found_port    number(2);
        xclass varchar2(30); -- classification of hms species
    
    begin
      totalvalue := 0;
      apex_collection.create_or_truncate_collection('FP_COLLECTION');
      apex_collection.create_or_truncate_collection('SPECIES_COLLECTION');
      apex_collection.create_or_truncate_collection('PORT_COLLECTION');
    
      pos := 1;
      :P110_PORTSELECTED := 1;
      for rec IN (select l.*,
                        area_display_name 
                  from  landings l, 
                        areas_fished a
                  where dealer_rpt_id = :P110_DEALER_RPT_ID and 
                        l.area_fished = a.area_code(+) and 
                        l.sub_area_fished = a.sub_area_code(+) and 
                        l.local_area_code = a.local_area_code(+)
                  order by landing_seq)
     
      LOOP
    htp.p('<script language="JavaScript" type="text/javascript">portSelected = 1;rowCount++;</script>');
         yes_are := 0;
         
         seqid:=apex_collection.add_member('SPECIES_COLLECTION',
                           null,
                           null,
                           rec.landing_seq,
                           rec.species_itis,
                           rec.grade_code,
                           rec.market_code,
                           rec.unit_measure,
                           rec.disposition_code,
                           rec.gear_code,
                           rec.reported_quantity,
                           rec.price,
                           rec.dollars,
                           'N');
    
    
    
         :G_FIRST_DISPOSITION := rec.disposition_code;
         :G_FIRST_GEAR := rec.gear_code;
         totalvalue := totalvalue + rec.dollars;
    
         select port 
                into portcode 
         from   dealer_reports 
         where  dealer_rpt_id = :P110_DEALER_RPT_ID;
    
         apex_collection.add_member('PORT_COLLECTION',portcode);
    
       
         select partner_id 
                into port_partner 
         from    valid_ports
        where  port =  portcode;
        
    
        select count(*) 
               into found_port 
        from   port_options 
        where  partner_id = port_partner and 
               option_type = 'LAC';
    
        if found_port > 0 then
           select count(*) 
                  into found_area 
           from   partner_options
           where  partner_id = port_partner and 
                  substr(species_itis,1,6) = rec.species_itis and 
                  option_type = 'ARE';
        end if;
    
        if found_area > 0 then
    
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => seqid,
                                                 p_attr_number =>13,
                                                 p_attr_value => 'Y');
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => seqid,
                                                 p_attr_number =>14,
                                                 p_attr_value => rec.area_display_name);
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => seqid,
                                                 p_attr_number =>25,
                                                 p_attr_value => rec.area_fished);
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => seqid,
                                                 p_attr_number =>26,
                                                 p_attr_value => rec.sub_area_fished);
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => seqid,
                                                 p_attr_number =>27,
                                                 p_attr_value => rec.local_area_code);
         else
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => seqid,
                                                 p_attr_number =>13,
                                                 p_attr_value => 'N');
     
         end if;
    
         found_unit := 0;
    
         select count(*) 
         into   found_unit 
         from   partner_options 
         where  option_type = 'LBC' and 
                partner_id = :G_ISSUING_AGENCY and 
                species_itis = rec.species_itis;
         
         if found_unit > 0 then
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>17,
                                                    p_attr_value => 'Y');     
         else
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>17,
                                                    p_attr_value => 'N');
         end if;
       
         for rec1 in (select * 
                      from  landings_addl_measure 
                      where landing_seq = rec.landing_seq 
                      order by landing_seq)
         LOOP
            found_unit := 1;
    
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>18,
                                                    p_attr_value => rec1.reported_quantity);
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>30,
                                                    p_attr_value => rec1.unit_measure);
         
         end LOOP;
    
         found_hms := 0;
         for rec2 in (select h.landing_seq, 
                             h.fins_attached, 
                             h.explanation,
                             h.nature_of_sale, 
                             h.hms_area_code, 
                             h.sale_price, 
                             h.modified_data,
                             h.late_report, 
                             l.species_itis, 
                             l.grade_code 
                      from   landings_hms h, 
                             landings l
                      where  h.landing_seq = rec.landing_seq and
                             h.landing_seq = l.landing_seq
                      order by    h.landing_seq)
         LOOP
            found_hms := 1;
    
    /* c020 indicates that species is an HMS species */
    
            apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>20,
                                                    p_attr_value => 'Y');
    
    
    BEGIN
    select upper(e.classification)
       into   xclass
       from   edealer.species e
       where  to_number(e.itis_code) = rec2.species_itis and
              rec.grade_code <> '01';
    
    if xclass = 'SHARK' then
         apex_collection.update_member_attribute  
                         (p_collection_name=>'SPECIES_COLLECTION',
                                                  p_seq=> pos,
                                                  p_attr_number=>50,
                                                  p_attr_value=>'Y');
    else
      apex_collection.update_member_attribute  
                         (p_collection_name=>'SPECIES_COLLECTION',
                                                  p_seq=> pos,
                                                  p_attr_number=>50,
                                                  p_attr_value=>'N');
    
    end if;
    EXCEPTION
    when no_data_found then 
      apex_collection.update_member_attribute  
                         (p_collection_name=>'SPECIES_COLLECTION',
                                                  p_seq=> pos,
                                                  p_attr_number=>50,
                                                  p_attr_value=>'N');
    end;
      
            if rec2.fins_attached = 'Y' then
               apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>21,
                                                    p_attr_value => rec2.fins_attached);
               apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>24,
                                                    p_attr_value => rec2.fins_attached);
            end if;
    
            if rec2.fins_attached = 'N' then
               apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>22,
                                                    p_attr_value => rec2.fins_attached);
               apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>24,
                                                    p_attr_value => rec2.fins_attached);
            end if;
    
            if rec2.fins_attached = 'U' then
               apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>23,
                                                    p_attr_value => rec2.fins_attached);
               apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>24,
                                                    p_attr_value => rec2.fins_attached); 
            end if;
               apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>28,
                                                    p_attr_value => rec2.explanation);
               apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>29,
                                                    p_attr_value => nvl(rec2.nature_of_sale,'A'));
    
    /* keh - added 5/11/2012, hms area */
    
     
    
    
              apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>40,
                                                    p_attr_value => rec2.hms_area_code);
    
    apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                    p_seq => pos,
                                                    p_attr_number =>41,
                                                    p_attr_value => rec2.sale_price);
       end LOOP;
    
       pos := pos + 1;
      end LOOP;
      
      
      select license_nbr||'-'||license_type into licenseval 
      from   permits p, 
             dealer_reports dr 
      where  dr.dealer_rpt_id = :P110_DEALER_RPT_ID and 
             dr.cf_permit_id = p.permit_id ;
    
      if apex_collection.collection_exists('LICENSE_COLLECTION') then
         apex_collection.truncate_collection('LICENSE_COLLECTION');
      else
         apex_collection.create_collection('LICENSE_COLLECTION');
      end if;
    
      apex_collection.add_member('LICENSE_COLLECTION', licenseval);
    
      select supplier_vessel_id into licenseval 
      from   vessels p, 
             dealer_reports dr 
      where  dr.dealer_rpt_id = :P110_DEALER_RPT_ID and dr.vessel_id = p.vessel_id ;
    
      if apex_collection.collection_exists('SUPVES_COLLECTION') then
         apex_collection.truncate_collection('SUPVES_COLLECTION');
      else
         apex_collection.create_collection('SUPVES_COLLECTION');
      end if;
      apex_collection.add_member('SUPVES_COLLECTION', licenseval);
      select count(*) into pCount from vessel_permits vp, dealer_reports dr where dr.vessel_id = vp.vessel_id and dr.dealer_rpt_id = :P110_DEALER_RPT_ID;
      if pCount = 1 then
      select supplier_id into vesspermit from vessel_permits vp, dealer_reports dr where dr.vessel_id = vp.vessel_id and dr.dealer_rpt_id = :P110_DEALER_RPT_ID;
       
      apex_collection.add_member('FP_COLLECTION', vesspermit);
      end if;
    
      apex_util.set_session_state(p_name => 'P110_TOTAL',p_value => totalvalue);
      apex_util.set_session_state(p_name => 'P110_PORT_CODE',p_value => portcode);
    
    
    end;
  • 21. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    To what value is the item PX_SEQ set? Is the column SEQ_ID of type hidden or a standard report column? The error message you get is saying that the input parameter somwhere in the code which is supposed to identify the sequence number could not be found - no sequence with that number exists in the collection. I don't see where you read the value of the item PX_SEQ. I see that you identifiy the sequence number using variable pos which you use as a counter in a loop. This approach is not clear to me. The counter in a loop doesn't have to be identical to the corresponding sequence value.

    Is your code one PL/SQL Block? I don't seem to be able to format it. Is this the whole code you use for updating? Why don't you put this in a package as a procedure? It looks to me like there would be a lot of redundant code. You are repeating 'SPECIES_COLLECTION' many times instead of using my approach - have a look at my PL/SQL blocks again.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 22. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    all good questions. Again, most of this code was written under an earlier version of APEX and we were also learning it as we went..never the best scenario. I have also inherited this code, so I don't have an answer as to why it was done this way. The good news is we are in the process of reviewing (which is why I am trying to simplify using views on top of collections to makes it easier to understand columns).

    The code is in one process. It is only run when a record is being reviewed or updated...there is a different process run when a new record is being created:
    begin
       apex_collection.create_collection('SPECIES_COLLECTION');
    END;
    our application is designed for commercial fisherman and is currently used by about 5,000 fishermen up our east coast...so change is tricky, but this review is the start.

    basically, when a new report is created a fisherman selects a fish species from a tree. As I have started this review, I have moved the process of adding a row to the collection and updating the collection into a db. The one shown in my earlier post, has not yet been moved. I have not had a chance to tighten up the add row procedure yet...but that will be done
    -- Start of DDL Script for Package Body SAFIS.SAFIS_COLLECTIONS
    -- Generated 22-May-2013 17:29:25 from SAFIS@SAFISD.world
    
    CREATE OR REPLACE 
    PACKAGE BODY safis_collections
    IS
    --
    -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
    -- directory of SQL Navigator
    --
    -- Purpose: Briefly explain the functionality of the package body
    --
    -- MODIFICATION HISTORY
    -- Person      Date    Comments
    -- ---------   ------  ------------------------------------------
       -- Enter procedure, function bodies as shown below
    
    
    
    procedure update_column(v_seq in number,
                            v_attr in number,
                            v_value in varchar2)
    is
    
    begin
       apex_collection.update_member_attribute (p_collection_name=> 'SPECIES_COLLECTION',
                         p_seq => v_seq,
                         p_attr_number => v_attr,
                         p_attr_value  => v_value);
    apex_debug.message('updated column. seq='||v_seq||' attribute='||v_attr||' with '||v_value);
    
    
    end;
    
    procedure delete_row (v_seq in number)
    is
    begin
     apex_collection.delete_member (p_collection_name      => 'SPECIES_COLLECTION',
                                      p_seq                  => v_seq
                                     );
    end;
    
    procedure add_landing (v_permit_id in number,
                           v_port in number,
                           v_state_issuing_agency in varchar2,
                           v_issuing_agency in varchar2,
                           v_selected_node  in varchar2)
    
    is
    begin
    
    DECLARE
       nodedata VARCHAR2(20);
       species VARCHAR2(6);
       grade VARCHAR2(2);
       marketcategory VARCHAR2(2);
       unit VARCHAR2(2);
       l_seq_id NUMBER(10);
       pCount NUMBER;
       favdispc NUMBER;
       favdisp VARCHAR2(3);
       favgear varchar2(4);
       xclass  varchar2(20);
       found_unit number;
       found_area number;
       unitmeasure varchar2(2);
    
    
    BEGIN
       select node_data into nodedata
       from   species_tree
       where  seq_nbr = v_SELECTED_NODE;
    
      /* begin
    
          select gear_code into favgear
          from   frequent_gears
          where  permit_id = v_PERMIT_ID and
                 :G_FIRST_GEAR = '3333';
    
       EXCEPTION when others then
             if :G_FIRST_GEAR <> '3333' then
                 favgear := :G_FIRST_GEAR;
             else
                 favgear := '3333';
             end if;
       END;*/
    
       species := substr(nodedata,1,6);
       grade := substr(nodedata, 7,2);
       marketcategory := substr(nodedata, 9,2);
       unit := substr(nodedata, 11,2);
    
       select count(*) into favdispc
       from   frequent_species
       where  species_itis = species and
              unit_measure = unit and
              market_code = marketcategory and
              grade_code = grade and
              permit_id = v_PERMIT_ID;
    
       if favdispc = 1 then
          select disposition_code into favdisp
          from   frequent_species
          where  species_itis = species and
                 unit_measure = unit and
                 market_code = marketcategory and
                 grade_code = grade and
                 permit_id = v_PERMIT_ID;
       end if;
    
       l_seq_id := APEX_COLLECTION.ADD_MEMBER('SPECIES_COLLECTION',
                                   null,
                                   null,
                                   null,
                                   species,
                                   grade,
                                   marketcategory,
                                   unit,
                                  -- nvl(favdisp,nvl(:G_FIRST_DISPOSITION,0)),
                                  nvl(favdisp,0),
                                   favgear,
                                   null,
                                   null,
                                   null);
    
      apex_collection.update_member_attribute
                     (p_collection_name=>'SPECIES_COLLECTION',
                                  p_seq=>l_seq_id,
                                  p_attr_number=>13,
                                  p_attr_value=>'N');
    
    
      for rec1 IN (select *
                   from   price_board
                   where  permit_id = v_PERMIT_ID)
      LOOP
    
          if (rec1.species_itis = species and
              rec1.grade_code = grade and
              rec1.market_code = marketcategory and
              rec1.unit_of_measure = unit) then
                  apex_collection.update_member_attribute(p_collection_name =>'SPECIES_COLLECTION',
                                         p_seq=>l_seq_id,
                                         p_attr_number=>11,
                                         p_attr_value=> rec1.price);
          end if;
      end loop;
    
    /* added 10/15/2012 - not certain why it was not included previously.  Check ADDtoFavorites */
    
    -- All rows need to be checked to determine if additional info is needed based on partner_options table
    -- check if AREA will be needed
    
         select count(*) into found_area
         from   partner_options
         where  partner_id in (select x.partner_id
                               from   partners x,
                                      valid_ports v
                               where  v.port = v_PORT ) and
                option_type = 'ARE' and
                species_itis = species;
    
       -- landing row requires AREA data
       if found_area > 0 then
          apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => l_seq_id,
                                                 p_attr_number =>13,
                                                 p_attr_value => 'Y');
       -- landing row does NOT require AREA data
       else
          apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => l_seq_id,
                                                 p_attr_number =>13,
                                                 p_attr_value => 'N');
    
        end if;
    
       found_unit := 0;
    
    -- check if COUNT will be needed
    
       select count(*) into found_unit
       from   partner_options
       where  partner_id = v_STATE_ISSUING_AGENCY and
              substr(species_itis,1,6) = species and
              option_type = 'LBC' and
              nvl(inactivate_option_date, sysdate) >= sysdate;
    
       -- landing row requires UNIT data
       if found_unit > 0 then
          select unit_measure into unitmeasure
          from   partner_options
          where  partner_id = v_STATE_ISSUING_AGENCY and
                 substr(species_itis,1,6) = species and
                 option_type = 'LBC' and nvl(inactivate_option_date, sysdate) >= sysdate;
    
          apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => l_seq_id,
                                                 p_attr_number =>17,
                                                 p_attr_value => 'Y');
          apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => l_seq_id,
                                                 p_attr_number =>19,
                                                 p_attr_value => unitmeasure);
    
       --landing row does NOT require UNIT data
       else
          apex_collection.update_member_attribute(p_collection_name=>'SPECIES_COLLECTION',
                                                 p_seq => l_seq_id,
                                                 p_attr_number =>17,
                                                 p_attr_value => 'N');
    
        end if;
    
    
    
    
    
    
    
    
    
    
      if v_ISSUING_AGENCY = '0016' then
         apex_collection.update_member_attribute
                        (p_collection_name=>'SPECIES_COLLECTION',
                         p_seq=>l_seq_id,
                         p_attr_number=>20,
                         p_attr_value=>'N');
    --THIS IS THE CORRECT TABLE CALL
    /*      SELECT count(*) into pCount
          FROM edealer.Species a
          where itis_code = species ;
          */
    -- USE THIS FOR DEV ONLY
    select count(*) into pcount
    from hmsspecies
    where hmsspeciesitis = species;
    
          if pCount > 0 then
             apex_collection.update_member_attribute
                            (p_collection_name=>'SPECIES_COLLECTION',
                             p_seq=>l_seq_id,
                             p_attr_number=>20,
                             p_attr_value=>'Y');
    
    
             apex_collection.update_member_attribute
                            (p_collection_name=>'SPECIES_COLLECTION',
                             p_seq=>l_seq_id,
                             p_attr_number=>24,
                             p_attr_value=>'E');
    
             apex_collection.update_member_attribute
                            (p_collection_name=>'SPECIES_COLLECTION',
                             p_seq=>l_seq_id,
                             p_attr_number=>29,
                             p_attr_value=>'A');
    
           end if;
    
           BEGIN
    
    -- THIS IS THE CORRECT TABLE
    /*      select upper(classification)
                    into xclass
             from   edealer.species
             where  itis_code = species  and
                    GRADE != '01';
                    */
    
    
    
             if xclass = 'SHARK' then
                apex_collection.update_member_attribute(
                           p_collection_name => 'SPECIES_COLLECTION',
                           p_seq => l_seq_id,
                           p_attr_number => 50,
                           p_attr_value => 'Y');
             else
               apex_collection.update_member_attribute(
                          p_collection_name => 'SPECIES_COLLECTION',
                          p_seq => l_seq_id,
                          p_attr_number => 50,
                          p_attr_value => 'N');
             end if;
    
          EXCEPTION when no_data_found then
                      apex_collection.update_member_attribute(
                                p_collection_name => 'SPECIES_COLLECTION',
                                p_seq => l_seq_id,
                                p_attr_number => 50,
                                p_attr_value => 'N');
          END;
      end if;
      APEX_COLLECTION.RESEQUENCE_COLLECTION('SPECIES_COLLECTION');
    
    END;
    
    
    end;
    
    
       -- Enter further code below as specified in the Package spec.
    END;
    /
    
    
    
    -- End of DDL Script for Package Body SAFIS.SAFIS_COLLECTIONS
    my modest hope is to place as much of the code into packages...and make the application much cleaner thereby making it easier for the next programmer to make changes and not have to bug you. ;)

    thanks

    Edited by: KarenH on May 22, 2013 5:12 PM

    Edited by: KarenH on May 22, 2013 5:26 PM
  • 23. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    I modified dynamic action #3 to set the value of P113_SEQ using static value = 4.

    that worked.

    so...now, it is question of how to do that dynamically. I am not familiar with javascript, but was hoping something like this would work to set value:
    $('#P113_ID').val().substring(4)).val();
    but still the ora-20213 error.

    so, I changed it to SET VALUE using SQL*PLUS:
    select ltrim(substr(:p113_ID,5,4),'0') from dual
    and that ALMOST does the trick. It only works if I then move to another field. If I keep the cursor on the field just changed, and then refresh manually, the change does not stick.


    incidently, I modified the jQuery Selector for the dynamic action to be:
    select[name=#DISPOSITION_CODE#],select[name=#GEAR_CODE#],input[name=#REPORTED_QUANTITY#],input[name=#PRICE#],input[name=#DOLLARS#]
    and the references to actual column names, as opposed to 'fxx' work well and minimize confusion on such a large collection.


    one last issue (for the moment...as there will be many more I am certain!). I am unable to refresh the region after the change to any of those above columns. The last true action of my DA is REFRESH on REGION: LANDINGS_VIEW_COLLECTION. Is there something additional that I need?

    thanks.

    Edited by: KarenH on May 23, 2013 11:16 AM

    Edited by: KarenH on May 23, 2013 11:21 AM
  • 24. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    oops. be careful with the jQuery selector.

    I had hoped that this would work:
    select[name=#DISPOSITION_CODE#],select[name=#GEAR_CODE#],input[name=#REPORTED_QUANTITY#],input[name=#PRICE#],input[name=#DOLLARS#]
    and it does, provided disposition_code maps to f08 (or the 8th posistion in my tabular form) ...as it is the equivalent of c008 in the collection. I made some of the columns DISPLAY and hid them....but that changed DISPOSITION_CODE to mape to f07, which through things off. I have now made columns hidden...so the order remains as it should be.

    Edited by: KarenH on May 23, 2013 3:51 PM
  • 25. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    I resolved the issue of Dynamic Action step#3 set value of P113_ID. The javascript was not working properly. So I completely removed the variable and the true action.

    Instead the DA action has the true action:
    declare
    
      v_member number;
      v_seq number;
    
    begin 
      v_member := TO_NUMBER (SUBSTR (:p113_id, 2, 2));
      select ltrim(substr(:p113_ID,5,4),'0') into v_seq from dual;
    
      safis_collections.update_column(v_seq,
                                    v_member,
                                    :p113_value);
    end;
    by setting the v_seq in the pl/sql, I am on more familiar territory, and the value is properly refreshed.

    I am still unable to refresh the region as a last true action (nothing happens)...

    thanks again
  • 26. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    If you want to refresh a clasic report region then the Enable Partial Page Refresh has to be set to Yes in the Region Attributes.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 27. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    Denes! you are a gem. thank you.

    but, I just don't see that option in the tabular form region. drat.
  • 28. Re: how should I update a tabular form, based on collection
    KarenH Newbie
    Currently Being Moderated
    oops. found it! thank goodness! :)
  • 29. Re: how should I update a tabular form, based on collection
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    :)

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    https://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points