This discussion is archived
4 Replies Latest reply: Apr 9, 2013 2:11 AM by 683256 RSS

Copy Data From One Table Into My Block

683256 Newbie
Currently Being Moderated
dear experts
i have the following case :
i have 2 oracle users : A & B
each one containing same tables : Master_Trans & detail_Trans
i have a form "Fmb" Main block /Detail block based on 2 the tables of user B
the cycle of work will be like this :
when i fill the item ID on the main block of "Fmb" , my code will search if the same ID existing on user A or not
if not , i will continue filling data manually
if yes , i will get same data of the 2 the tables from user A , display it on the form "Fmb" (main & details)
then after reviewing the data if it is ok i will save it .
what i did is :
i success to display the data of Master_Trans on the main of my form , but i fail to get the data of the detail_Trans to its details blocks
any idea to do it ?
  • 1. Re: Copy Data From One Table Into My Block
    Amatu Allah Guru
    Currently Being Moderated
    Hi Rudwan
    pls consider using relations between master Details block & share us the code u used
    Amatu Allah
  • 2. Re: Copy Data From One Table Into My Block
    CraigB Guru
    Currently Being Moderated
    ...I have 2 oracle users : A & B
    each one containing same tables : Master_Trans & detail_Trans>
    If you want your users to share data, then you need to use a single table and give users A and B permissions to write to this single table.
    If you must* keep the tables in their respective schema's then you can reference the seperate schema tables through explicite reference. In other words, you have to specify the schema.table_name. EG: A.MASTER_TRANS or B.MASTER_TRANS. Of course, the A user will need permissions to select from user B's tables and vis-versa; User B will need permissions to select from user A's tables. This is not a very efficient design however. What if your system had 1000 users? The better solution would be to use a single table and grant user's permissions to insert, update, and delete from the table.
    when i fill the item ID on the main block of "Fmb" , my code will search if the same ID existing on user A or not
    if not , i will continue filling data manually
    if yes , i will get same data of the 2 the tables from user A , display it on the form "Fmb" (main & details)>
    This will only work for records that are commited to the database. You can't see any uncommited records.

    Craig...
  • 3. Re: Copy Data From One Table Into My Block
    683256 Newbie
    Currently Being Moderated
    thanx for both
    may i didn't explain my problem
    i don't have problem with the permissions of the other oracle user , it is ok
    my problem is : how to display the data of the details table in my block details
    if it is one record only it will be so easier
    but i think i have to insert it all details records of each ID
    maybe
    but any one have a solution to display it before inserting ?
    for example , is there a code to set the cursur to the first record in the block ( the details ) and i will use cursor to display the 1st record of detail_table
    then go next record in the detail block , and display the 2nd record of the table
    then go next record ,, etc ?
  • 4. Re: Copy Data From One Table Into My Block
    683256 Newbie
    Currently Being Moderated
    HI
    now i changed my sycle , i tried to insert the data from both tables of user A , but i got a problem in the 1st step while inserting data from main table of user A

    i create non_database item "TR" , and used key_next_item with the following trigger


    DECLARE
         

    Cursor c_ent IS
    select * From master (master is a synonym of main_tran on user A)
    Where tran_ID=:M_TR.tr and (doc_date between st_month and en_month);
    vk c_ent%rowtype;
    tr_id integer;
    lm INTEGER;
    kl integer;
    BEGIN
    -- 1 - check if record exist in current table of user B then display it
    SELECT count(*)
    INTO lm
    FROM M_TR
    WHERE year=curr_y
    and tran_ID = :M_TR.tR ;

    if lm > 0 then
         Clear_Block(NO_VALIDATE); (not work)
    Enter_Query; (not work)

    end if;


    -- 2 check if record exist in table of user A , then upload it
    SELECT count(*)
    INTO kl
    FROM master
    WHERE
    year=curr_y
    AND tran_ID = :M_TR.TR
    and doc_date between st_month and en_month;

    if kl > 0 then

    select nvl(max(tran_no),0)+1 into tr_id from M_TR where year=curr_y; (to create new ID)

    OPEN c_ent;

    loop

    fetch c_ent into vk;

    exit when c_ent%notfound;



    INSERT INTO M_TR (YEAR, DOC_DATE, HEAD_DESC, CHK_NO, tran_ID, tran_no,PERIOD_NO, TR_CODE, tr_code_orcl,CREATED_BY, CREATED_DATE, CREATE_ALPHA_USER, APPROVE_FLG, JOUR_BAL_FLG )
    values( curr_y, vk.doc_date, vk.HEAD_DESC, vk.CHK_NO, vk.tran_ID , tr_id ,vk.PERIOD_NO , 20 , vk.tr_code,'GL_INT' , sysdate, 'Me' , 1 , 1 );

    commit; ( here it returnes to the 1st checking , then it send me error message that commit not allowed under pre_text_item)
    end loop;
    CLOSE c_ent;


    else
    -- DO CONTINUE MANUAL POSTING
    end if;




    EXCEPTION
    WHEN NO_DATA_FOUND THEN
         MESSAGE('This Entry Doesnt Exist');
         MESSAGE('This Entry Doesnt Exist');
    END;


    after that i execute_trigger('key_next_item') through POST_ITEM to be far a way from the error of commit , but still
    now i always have error while committing

Legend

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