1 2 Previous Next 29 Replies Latest reply: May 23, 2013 5:01 PM by Denes Kubicek Go to original post RSS
      • 15. Re: how should I update a tabular form, based on collection
        KarenH
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  oops. found it! thank goodness! :)
                                  • 29. Re: how should I update a tabular form, based on collection
                                    Denes Kubicek
                                    :)

                                    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