This discussion is archived
6 Replies Latest reply: Jan 10, 2013 12:35 AM by Ahmed Alsaied RSS

Master-Detail Manual Tabular updatable

Ahmed Alsaied Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    at least i solved all the issues , the thread answered
    Thanks
    Abo Yhaya

Legend

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