OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » Creating items based on a pl/sql function OR referencing report columns in a pl/sql query

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Creating items based on a pl/sql function OR referencing report columns in a pl/sql query (APEX 4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #543869]

Fri, 17 February 2012 05:13

balckbandit5
Messages: 104
Registered: December 2011

Senior Member

Hello

I have the following situation which I have thought of a way to resolve (but which I can't do...):
I have a form on a page to enter the details of a new project.
For each project I need to have X rows created in table MILESTONES containing the project ID, each checkpoint ID and a DATE for each checkpoint. (Where X is the number of checkpoints.)

So for example I create example_project, ID = 987. there are 3 checkpoints A, B and C
the milestone table should look like:

PROJECT_ID     CHECKPOINT   DATE
987            A            21.02.2012
987            B            04.03.2012
987            C            15.03.2012

To enter that into the database with PL/SQL, I can do BUT the problem is that table CHECKPOINTS is editable. So the number of checkpoints can change.

My initial thought was just to create a report/tabular form displaying each checkpoint with a datepicker field. The problem is that I don't know how (or even if you can) reference a specific report field in the PL/SQL function.

If that's the best way to go about this, how do I do it? and if not, how should I go about the whole thing?

thanks Smile

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #543905 is a reply to message #543869]

Fri, 17 February 2012 09:28

c_stenersen
Messages: 255
Registered: August 2007

Senior Member

I'm not completely sure what it is you want to do and what it is you have problems with. Have you already figured out how to enter the entries into the milestones table and just want to edit these dates? Or don't you have the rows in your milestones table at all and need to insert them? And what exactly do you want to do in your pl/sql function which references these dates?

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544093 is a reply to message #543905]

Mon, 20 February 2012 02:27

balckbandit5
Messages: 104
Registered: December 2011

Senior Member

Sorry I wasn't clear, I'll try again Razz

On the page, I create the project. I then need to enter the rows into the milestone table. (So, yes, insert them not edit them)
Without the date, I think I can do it just not when I need to have them enter the date themselves.

The PL/SQL is just the insert process.

Hope that clears it up...

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544114 is a reply to message #544093]

Mon, 20 February 2012 04:11

c_stenersen
Messages: 255
Registered: August 2007

Senior Member

Are the checkpoints in another table? If you want an editable field then you can use the APEX_ITEM package. So in your region you could then have

select 
    apex_item.display_and_save(1, checkpoint) checkpoint, 
    apex_item.date_popup2(2, null, 'DD-MON-YY') milestone_date
from checkpoints
where <<whatever conditions you have>>

And in your process (assuming P1_PROJECT_ID holds the id of the recently inserted project):

begin
    for i in 1..apex_application.g_f01.count loop 
        --apex_application.g_f01 holds the values for items you've given number 1 to
        --and g_f02 holds the ones with number 2

        insert into milestones(project_id, checkpoint, date)
        values(:P1_PROJECT_ID, apex_application.g_f01(i), apex_application.g_f02(i));
    end loop;
end;

If you don't have this checkpoint table then there is still a technique for doing this, but unless you actually need it I won't confuse you with it Smile

Just let me know if this is what you needed.

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544118 is a reply to message #544114]

Mon, 20 February 2012 04:30

balckbandit5
Messages: 104
Registered: December 2011

Senior Member

Quote:

but unless you actually need it I won't confuse you with it

haha thank you for the consideration Razz

As far as I can tell this is what I was after
just one thing, my checkpoint table has the ID and the Checkpoint name, so to display the name but have the ID in the other table would this work:

select 
    apex_item.hidden(1, checkpoint_ID) Checkpoint_ID,
    apex_item.display_and_save(2, checkpoint) checkpoint_name, 
    apex_item.date_popup2(3, null, 'DD-MON-YY') milestone_date
from checkpoints

then

begin
    for i in 1..apex_application.g_f01.count loop 
                insert into milestones(project_id, checkpoint_ID, date)
        values(:P1_PROJECT_ID, apex_application.g_f01(i), apex_application.g_f03(i));
    end loop;
end;

thanks

[Updated on: Mon, 20 February 2012 04:31]

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544123 is a reply to message #544118]

Mon, 20 February 2012 04:40

c_stenersen
Messages: 255
Registered: August 2007

Senior Member

Yes it would work. But what I would rather do is:

select 
    apex_item.hidden(1, checkpoint_ID) || checkpoint checkpoint_name,
    apex_item.date_popup2(2, null, 'DD-MON-YY') milestone_date
from checkpoints

What you can see is that I removed the "display_and_save" for the text of the checkpoint name (you don't need it in your process, so there's no need to save it), and I just concatenated your hidden value with the checkpoint name. If you put the hidden field in a column of its own the report will show an empty column. By concatenating the hidden field with the display value you will see the name of the checkpoint, but the id is what's stored. Then you can just use the process from my previous post.

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544127 is a reply to message #544123]

Mon, 20 February 2012 04:43

balckbandit5
Messages: 104
Registered: December 2011

Senior Member

Ok, perfect! Thanks Smile

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544132 is a reply to message #544127]

Mon, 20 February 2012 05:17

balckbandit5
Messages: 104
Registered: December 2011

Senior Member

Hi again, got a bit of a problem,

THe MILESTONE table has a Primary Key on 'ID', so I need another hidden column with a new ID for each checkpoint...

I tried:

begin
    for i in 1..apex_application.g_f01.count loop 
        insert into PMO_MILESTONES(MILESTONE_ID, CHECKPOINT_ID, PROJECT_ID, DATE)
        values((select S_PROJECT_MILESTONE.nextval next_val from dual),
 apex_application.g_f01(i), :P4_PROJECT_ID, apex_application.g_f02(i));
    end loop;
end;

but it didn't like me doing that...(it said: sequence number not allowed here )
Can I put a default value in a hidden apex_item?
thanks

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544149 is a reply to message #544132]

Mon, 20 February 2012 06:09

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Try with

insert into PMO_MILESTONES
  (MILESTONE_ID, 
   CHECKPOINT_ID, 
   PROJECT_ID, 
   DATE
  )
   values
  (S_PROJECT_MILESTONE.nextval, 
   apex_application.g_f01(i), 
  :P4_PROJECT_ID, 
   apex_application.g_f02(i)
  );

Report message to a moderator

Re: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query [message #544159 is a reply to message #544149]

Mon, 20 February 2012 06:33

balckbandit5
Messages: 104
Registered: December 2011

Senior Member

That works, thanks

Just one last thing, I've got the format for the date as 'DD-MON-YY HH24:MI' in the APEX_ITEM. But when I select a time later than 12 o'clock it says the hour must be between 1 and 12...I'm not sure why because it doesn't have to be in the database, I've got times 16:30 and 13:15 already and there was no problem with them...

Report message to a moderator