Let me explain how it works,
Create a page item P1_MSG;
Lets say the user will key in values for F02 report text box
Create a page process
Process point: Before computations and validations
v_msg VARCHAR2(32000) := NULL;
--apex_application.g_f02 is your input parameter name in tabular form, check gfrom browser
FOR i IN 1..apex_application.g_f02.COUNT
SELECT B2 INTO v_val FROM TABLE2 WHERE A2 = apex_application.g_f01(i);
IF v_val > apex_application.g_f02(i) THEN
v_msg := v_msg||'The value you entered cannot exceed amount<br/>';
IF v_msg IS NOT NULL THEN
:P1_MSG := v_msg;
Now create a validation on page level.
Select pl/sql expression returning error message
v_msg VARCHAR2(32000) := :P1_MSG;
IF :P1_MSG IS NOT NULL THEN
Honestly, I'm not sure why you would need to go through the hassle of creating a page item just to be a temporary container between 2 processes, when you're just returning it as a message in a validation. Why wouldn't you simply write your required validation code in the validation?
It also doesn't really help with what I seem to understand is the problem at hand. When OP reacted to my last post he mentioned he got the validation working without the item hoop?
@OP: is this still the case? Your issue seems more to be about looping over the appropriate arrays and dealing with their data?
You can go about this in several ways: use plsql tables, use a temp table (possible if contained within this one process) or a collection - all to store your data temporarily. If you want to do actual sorting you might want to use a collection so you can use some sql. But you'll first have to push the data from the apex_application.g_f arrays to one of those places then.
Does order even matter then? Can you not just loop over each record and increment the total amount each time, once you hit the limit you can then throw an error.
By the way, the error message can contain html, so you could very well create, say, an unordered list (UL element) with list items in them, refering to rows by using the index of the loop iteration.
Tell me if I'm wrong on any count.
You are correct that I got the validation working fine for a row at a time, but I need to sort the data in the tabular form. Here is an example below of my tabular form:
Task Name Invoice Value
Task A 50000
Task B 100000
Task A 800
Task C 50000
Task B 3000
Another table holds the maximum amounts for Task A, Task B, Task C, etc... I need to make sure that all invoices in the tabular form for Task A, B, C, etc... do not exceed the maximum amount for their corresponding task in the other table. (So, the sum of invoices in the tabular form for Task A can not exceed the max value for Task A in the other table, and so on.) I need a good way to sort the tabular form data by Task Name. So, since the order does matter, it sounds like a collection might be appropriate? Do you have any examples or any advice for the best way to approach this? I hope this makes sense....
Ah, I wasn't totally with you on that count then. My validation works globally but it adds up the amount for each row disregarding task. Okay - that wasn't totally my solution but more a show of the technique you can use to validate your tabular form in that way.
You could solve that issue with a collection sure, or with a plsql table where you add an entry for each task name and keep the amount stored in there.
Either way it'd be best for you to spend a bit of time on reading up on plsql collections and/or apex collections!
Mind you, this is a sample and you'll need to take stock of it. The best solutions are those where you need to experiment 8)
DECLARE TYPE tt_taskamount IS TABLE OF NUMBER INDEX BY VARCHAR2(20); t_taskamount tt_taskamount; l_task VARCHAR2(20); l_amount NUMBER; l_max NUMBER; BEGIN -- F02 is the taskname -- F03 is the amount -- adapt to your situation! FOR i IN 1..apex_application.g_f02.count LOOP l_task := apex_application.g_f02(i); l_amount := apex_application.g_f03(i); IF NOT t_taskamount.exists(l_task) THEN t_taskamount(l_task) := l_amount; ELSE t_taskamount(l_task) := t_taskamount(l_task) + l_amount; END IF; -- it's not the best idea to perform a select each time and could be -- done better but for the sake of the example it's fine -- not too bad either if the rowcount is small. SELECT maxamount INTO l_max FROM table2 WHERE taskname = l_task; IF t_taskamount(l_task) > l_max THEN -- returning will stop further processing and throw an error message as soon as a budget has been overrun -- it might or might not be required behaviour but that's up to you RETURN 'The max amount ('||l_max||') for task ' || l_task ||' has been exceeded. Reduce the total amount on associated tasks!'; END IF; END LOOP; END;
Thank you, Tom!!! This is EXACTLY what I needed! I read the documentation, and I was able to adapt the code to my situation. It works like a charm. I really appreciate your help!!!!!