6 Replies Latest reply: Jan 10, 2013 2:35 AM by Ahmed Alsaied RSS

    Master-Detail Manual Tabular updatable

    Ahmed Alsaied
      Dear Experts ,
      I have master and detail table , in detail table i want to build tabular form manual , i want to insert , delete and update in detail manual tabular .
      tables i use :
      CREATE TABLE  "DISTRB_PLAN" 
         (     "PLAN_ID" NUMBER NOT NULL ENABLE, 
           "PLAN_NAME" VARCHAR2(200), 
           "PLAN_DESC" VARCHAR2(2000), 
           "PLAN_NOTES" VARCHAR2(2000),  
            CONSTRAINT "DISTRB_PLAN_PK" PRIMARY KEY ("PLAN_ID") ENABLE
         )
      /
      CREATE TABLE  "PLAN_TARGETS" 
         (     "TARGET_ID" NUMBER NOT NULL ENABLE, 
           "PLAN_ID" NUMBER NOT NULL ENABLE, 
           "TARGET_NAME" VARCHAR2(200), 
           "TARGET_DESC" VARCHAR2(2000), 
           "TARGET_LEVEL_ID" NUMBER, 
           "TARGET_OBJECT" VARCHAR2(200), 
           "TARGET_QNTY" NUMBER, 
           "TARGET_VAL" NUMBER, 
           "TARGET_NOTES" VARCHAR2(2000), 
           "TARGET_SDATE" DATE, 
           "TARGET_EDATE" DATE, 
            CONSTRAINT "PLAN_TARGETS_PK" PRIMARY KEY ("TARGET_ID") ENABLE
         )
      /
      ALTER TABLE  "PLAN_TARGETS" ADD CONSTRAINT "PLAN_TARGETS_R01" FOREIGN KEY ("PLAN_ID")
             REFERENCES  "DISTRB_PLAN" ("PLAN_ID") ENABLE
      /
      in Detail Manual Tabular form i put this query :
      select  apex_item.checkbox (30,
                                 P.TARGET_ID,
                                 'onclick="highlight_row(this,' || P.TARGET_ID || ')"',
                                 NULL,
                                 ':',
                                 'f30_' || P.TARGET_ID
                                ) delete_checkbox ,
      P.TARGET_ID,
      "PLAN_ID",
      apex_item.text(31,TARGET_NAME,15) TARGET_NAME,
      apex_item.text(32,target_desc,20) target_desc ,
      apex_item.text(33,TARGET_QNTY ,6) TARGET_QNTY ,
      apex_item.text(34,TARGET_VAL ,6)  TARGET_VAL,
      apex_item.text(35,TARGET_NOTES,15)  TARGET_NOTES,
       APEX_ITEM.DATE_POPUP(36,rownum,TARGET_SDATE,'dd-mm-yyyy',9) TARGET_SDATE ,
       APEX_ITEM.DATE_POPUP(37,rownum,TARGET_EDATE,'dd-mm-yyyy',9) TARGET_EDATE 
       
      from  "#OWNER#"."PLAN_TARGETS" P , apex_collections C
      where "PLAN_ID" = :P2_PLAN_ID 
      and collection_name = 'PLAN_TARGET'
      AND P.TARGET_ID =C.C001
      i create collection on load before header :
      DECLARE
      v_collection_name             apex_collections.collection_name%TYPE
                                                       := 'PLAN_TARGET';
      v_reset_flag                  VARCHAR2 (1) := 'N';
      BEGIN
      -- Create collection if it does not exist or reset collection required
      IF    apex_collection.collection_exists
                                         (p_collection_name            => v_collection_name) =
                                                                           FALSE
        OR v_reset_flag = 'Y'
      THEN
       apex_collection.create_collection_from_query
         (p_collection_name            =>    'PLAN_TARGET',
          p_query                      => q'! SELECT  target_id, plan_id,  
                                              target_name,target_desc,
                                              target_qnty, target_val,TARGET_NOTES,
                                              TARGET_SDATE,
                                              TARGET_EDATE
                                              FROM plan_targets !',
          p_generate_md5               => 'YES'
         ); 
      END IF;
      END;
      my example in apex.oracle.com
      Workspace: ajyal
      user:ahmed
      pass:123
      Application : 40730 Master-Manual Tabular

      Thanks
      Abo Yhaya
        • 1. Re: Master-Detail Manual Tabular updatable
          Denes Kubicek
          You didn't mention what the actual problem is.

          Denes Kubicek
          -------------------------------------------------------------------
          http://deneskubicek.blogspot.com/
          http://www.apress.com/9781430235125
          http://apex.oracle.com/pls/apex/f?p=31517:1
          http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
          -------------------------------------------------------------------
          • 2. Re: Master-Detail Manual Tabular updatable
            Ahmed Alsaied
            Hi Denes ,
            the problem that i try to make insert , update and delete but not working , how can i do that ?

            Edited by: Abo Yahya on 08/01/2013 01:35 م
            • 3. Re: Master-Detail Manual Tabular updatable
              Denes Kubicek
              See this example for a manual tabular form:

              http://apex.oracle.com/pls/apex/f?p=31517:170

              and this on on how tu update collections:

              http://apex.oracle.com/pls/apex/f?p=31517:30

              Denes Kubicek
              -------------------------------------------------------------------
              http://deneskubicek.blogspot.com/
              http://www.apress.com/9781430235125
              http://apex.oracle.com/pls/apex/f?p=31517:1
              http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
              -------------------------------------------------------------------
              • 4. Re: Master-Detail Manual Tabular updatable
                Ahmed Alsaied
                Dears ,
                now i have master and detail table , in detail i use manual tabular form to use cascade list item , i have problem with sql query and i use union all to insert new row
                SELECT apex_item.checkbox (30,
                                           '#ROWNUM#',
                                           'onclick="highlight_row(this,' || '#ROWNUM#'|| ')"',
                                           NULL,
                                           ':',
                                           'f30_' || '#ROWNUM#'
                                          ) delete_checkbox,
                         target_id,
                         apex_item.hidden (31, target_id) 
                        ||apex_item.text(32,PLAN_ID,2) PLAN_ID ,
                  apex_item.text(33,TARGET_NAME,10) 
                ||apex_item.hidden (34, wwv_flow_item.md5 (TARGET_NAME, target_desc, target_level_id,target_object,
                 TARGET_VAL,TARGET_QNTY,TARGET_SDATE,TARGET_EDATE,TARGET_NOTES))  TARGET_NAME,
                apex_item.text(35,target_desc,10) target_desc,
                apex_item.select_list_from_query
                                               (36,
                                                target_level_id,
                                                'SELECT level_desc d, ' || 'level_id r FROM PLAN_TARGET_LEVEL',
                                                   'style="width:130px" '
                                                || ' ="f_set_casc_sel_list_item(this,'
                                                || 'f37_'
                                                ||'#ROWNUM#'
                                                || ')"',
                                                'YES',
                                                '0',
                                                '- Select level -',
                                                'f36_' ||'#ROWNUM#',
                                                NULL,
                                                'NO'
                                               ) target_level_id ,
                 apex_item.select_list_from_query (37,
                                                          target_object,
                                                            decode( target_level_id,1,'SELECT comp_name d,comp_code r FROM company ',2,
                                                            'SELECT item_name d,item_code r FROM items ',3,
                                                            'select type_desc d,type_id r from item_type ',4,
                                                            'select cust_name d ,cust_id r from customers ',5,
                                                            'select branch_name d ,branch_code r from Branches ',6,
                                                            'select office_name d,office_id r from SALES_OFFICE',7,
                                                            'select sector_name d ,SECTOR_ID r from Sectors',8,
                                                            'select state_name d,state_id r from States',9,
                                                            'select city_name d,city_id r from city'
                                                            ),
                                                         'style="width:130px"',
                                                         'YES',
                                                         '0',
                                                            '- Select ('
                                                         || decode(target_level_id,1,(SELECT COUNT (*) FROM customers)
                                                         ,2,(SELECT COUNT (*) FROM items)
                                                         ,3,(select count(*) from item_type)
                                                         ,4,(select count(*) from customers)
                                                         ,5,(select count(*) from Branches )
                                                         ,6,(select count(*) from SALES_OFFICE)
                                                         ,7,(select count(*) from Sectors)
                                                         ,8,(select count(*) from States)
                                                         ,9,(select count(*) from city))
                                                         || ') -',
                                                         'f37_' ||'#ROWNUM#',
                                                         NULL,
                                                         'NO'
                                                        ) target_object,
                --apex_item.text(37,target_object ,20)  target_object,
                 apex_item.text(38,TARGET_VAL ,3)  TARGET_VAL,
                 apex_item.text(39,TARGET_QNTY ,3) TARGET_QNTY ,
                 APEX_ITEM.DATE_POPUP(40,rownum,TARGET_SDATE,'YYYY-MM-DD',6) TARGET_SDATE ,
                 APEX_ITEM.DATE_POPUP(41,rownum,TARGET_EDATE,'YYYY-MM-DD',6) TARGET_EDATE ,
                 apex_item.text(42,TARGET_NOTES,15)  TARGET_NOTES
                
                 
                from  PLAN_TARGETS 
                where plan_id =:P100006_PLAN_ID
                UNION all
                SELECT   distinct  apex_item.checkbox
                                          (30,
                                           TO_NUMBER(9900 + LEVEL),
                                           'onclick="highlight_row(this,' || '#ROWNUM#' || ')"',
                                           NULL,
                                           ':',
                                           'f30_' || TO_NUMBER (9900 + LEVEL)
                                          ) delete_checkbox,
                           NULL,
                              apex_item.hidden (31, NULL)
                           || apex_item.text(32,null,2)  PLAN_ID,
                apex_item.text(33,null,10) 
                ||apex_item.hidden (34, NULL) TARGET_NAME,
                apex_item.text(35,null,10) target_desc,
                apex_item.select_list_from_query
                                               (36,
                                                null,
                                                'SELECT level_desc d, ' || 'level_id r FROM PLAN_TARGET_LEVEL',
                                                   'style="width:160px" '
                                                || 'onchange="f_set_casc_sel_list_item(this,'
                                                || 'f37_'
                                                ||'#ROWNUM#'
                                                || ')"',
                                                'YES',
                                                '0',
                                                '- Select level -',
                                                'f36_' ||'#ROWNUM#',
                                                NULL,
                                                'NO'
                                               ) target_level_id ,
                 apex_item.select_list_from_query (37,
                                                          null,
                                                          'SELECT item_name d, '
                                                         || 'item_code r FROM items ',
                                                         'style="width:130px"',
                                                         'YES',
                                                         '0',
                                                            '- Select items ('
                                                         || (SELECT COUNT (*)
                                                               FROM items
                                                              )
                                                         || ') -',
                                                         'f37_' ||'#ROWNUM#',
                                                         NULL,
                                                         'NO'
                                                        ) target_object,
                --apex_item.text(37,null,10) target_object,
                
                 apex_item.text(38,null ,3)  TARGET_VAL,
                 apex_item.text(39,null ,3) TARGET_QNTY ,
                  APEX_ITEM.DATE_POPUP(40,rownum,null,'YYYY-MM-DD',6) TARGET_SDATE ,
                 APEX_ITEM.DATE_POPUP(41,rownum,null,'YYYY-MM-DD',6) TARGET_EDATE ,
                 apex_item.text(42,null,15)  TARGET_NOTES
                
                      FROM dual 
                     WHERE :request = 'ADD_ROWS'
                
                CONNECT BY LEVEL <= 1
                the problem when i try to add new row can't fetch plan_id from master table in the detail because in second select i am select from dual , if i change the code to
                SELECT apex_item.checkbox (30,
                                           '#ROWNUM#',
                                           'onclick="highlight_row(this,' || '#ROWNUM#'|| ')"',
                                           NULL,
                                           ':',
                                           'f30_' || '#ROWNUM#'
                                          ) delete_checkbox,
                         target_id,
                         apex_item.hidden (31, target_id) 
                        ||apex_item.text(32,PLAN_ID,2) PLAN_ID ,
                  apex_item.text(33,TARGET_NAME,10) 
                ||apex_item.hidden (34, wwv_flow_item.md5 (TARGET_NAME, target_desc, target_level_id,target_object,
                 TARGET_VAL,TARGET_QNTY,TARGET_SDATE,TARGET_EDATE,TARGET_NOTES))  TARGET_NAME,
                apex_item.text(35,target_desc,10) target_desc,
                apex_item.select_list_from_query
                                               (36,
                                                target_level_id,
                                                'SELECT level_desc d, ' || 'level_id r FROM PLAN_TARGET_LEVEL',
                                                   'style="width:130px" '
                                                || ' ="f_set_casc_sel_list_item(this,'
                                                || 'f37_'
                                                ||'#ROWNUM#'
                                                || ')"',
                                                'YES',
                                                '0',
                                                '- Select level -',
                                                'f36_' ||'#ROWNUM#',
                                                NULL,
                                                'NO'
                                               ) target_level_id ,
                 apex_item.select_list_from_query (37,
                                                          target_object,
                                                            decode( target_level_id,1,'SELECT comp_name d,comp_code r FROM company ',2,
                                                            'SELECT item_name d,item_code r FROM items ',3,
                                                            'select type_desc d,type_id r from item_type ',4,
                                                            'select cust_name d ,cust_id r from customers ',5,
                                                            'select branch_name d ,branch_code r from Branches ',6,
                                                            'select office_name d,office_id r from SALES_OFFICE',7,
                                                            'select sector_name d ,SECTOR_ID r from Sectors',8,
                                                            'select state_name d,state_id r from States',9,
                                                            'select city_name d,city_id r from city'
                                                            ),
                                                         'style="width:130px"',
                                                         'YES',
                                                         '0',
                                                            '- Select ('
                                                         || decode(target_level_id,1,(SELECT COUNT (*) FROM customers)
                                                         ,2,(SELECT COUNT (*) FROM items)
                                                         ,3,(select count(*) from item_type)
                                                         ,4,(select count(*) from customers)
                                                         ,5,(select count(*) from Branches )
                                                         ,6,(select count(*) from SALES_OFFICE)
                                                         ,7,(select count(*) from Sectors)
                                                         ,8,(select count(*) from States)
                                                         ,9,(select count(*) from city))
                                                         || ') -',
                                                         'f37_' ||'#ROWNUM#',
                                                         NULL,
                                                         'NO'
                                                        ) target_object,
                --apex_item.text(37,target_object ,20)  target_object,
                 apex_item.text(38,TARGET_VAL ,3)  TARGET_VAL,
                 apex_item.text(39,TARGET_QNTY ,3) TARGET_QNTY ,
                 APEX_ITEM.DATE_POPUP(40,rownum,TARGET_SDATE,'YYYY-MM-DD',6) TARGET_SDATE ,
                 APEX_ITEM.DATE_POPUP(41,rownum,TARGET_EDATE,'YYYY-MM-DD',6) TARGET_EDATE ,
                 apex_item.text(42,TARGET_NOTES,15)  TARGET_NOTES
                
                 
                from  PLAN_TARGETS p
                where p.plan_id =:P100006_PLAN_ID
                UNION all
                SELECT   distinct  apex_item.checkbox
                                          (30,
                                           TO_NUMBER(9900 + LEVEL),
                                           'onclick="highlight_row(this,' || '#ROWNUM#' || ')"',
                                           NULL,
                                           ':',
                                           'f30_' || TO_NUMBER (9900 + LEVEL)
                                          ) delete_checkbox,
                           NULL,
                              apex_item.hidden (31, NULL)
                           || apex_item.text(32,null,2)  PLAN_ID,
                apex_item.text(33,null,10) 
                ||apex_item.hidden (34, NULL) TARGET_NAME,
                apex_item.text(35,null,10) target_desc,
                apex_item.select_list_from_query
                                               (36,
                                                null,
                                                'SELECT level_desc d, ' || 'level_id r FROM PLAN_TARGET_LEVEL',
                                                   'style="width:160px" '
                                                || 'onchange="f_set_casc_sel_list_item(this,'
                                                || 'f37_'
                                                ||'#ROWNUM#'
                                                || ')"',
                                                'YES',
                                                '0',
                                                '- Select level -',
                                                'f36_' ||'#ROWNUM#',
                                                NULL,
                                                'NO'
                                               ) target_level_id ,
                 apex_item.select_list_from_query (37,
                                                          null,
                                                          'SELECT item_name d, '
                                                         || 'item_code r FROM items ',
                                                         'style="width:130px"',
                                                         'YES',
                                                         '0',
                                                            '- Select items ('
                                                         || (SELECT COUNT (*)
                                                               FROM items
                                                              )
                                                         || ') -',
                                                         'f37_' ||'#ROWNUM#',
                                                         NULL,
                                                         'NO'
                                                        ) target_object,
                --apex_item.text(37,null,10) target_object,
                
                 apex_item.text(38,null ,3)  TARGET_VAL,
                 apex_item.text(39,null ,3) TARGET_QNTY ,
                  APEX_ITEM.DATE_POPUP(40,rownum,null,'YYYY-MM-DD',6) TARGET_SDATE ,
                 APEX_ITEM.DATE_POPUP(41,rownum,null,'YYYY-MM-DD',6) TARGET_EDATE ,
                 apex_item.text(42,null,15)  TARGET_NOTES
                
                      FROM PLAN_TARGETS t
                     WHERE :request = 'ADD_ROWS' and  t.plan_id =:P100006_PLAN_ID
                
                CONNECT BY LEVEL <= 1
                it's insert count of rows like in report ,
                and i have another problem , when i insert row cascading list working well , but after save can't working when i want to update .
                • 5. Re: Master-Detail Manual Tabular updatable
                  Ahmed Alsaied
                  Hello ,
                  now i can make update after save , every thing working well , only how can i insert one row only in every click on add row ? , the problem it's insert like count in the report .
                  • 6. Re: Master-Detail Manual Tabular updatable
                    Ahmed Alsaied
                    at least i solved all the issues , the thread answered
                    Thanks
                    Abo Yhaya