Home » Developer & Programmer » Application Express & MOD_PLSQL » How to REJECT or APPROVE the request in APEX4.1 (Apex4.1)
How to REJECT or APPROVE the request in APEX4.1 [message #553860] Tue, 08 May 2012 23:08 Go to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hello everyone,

if the users applying the leave by filling the form,
it has the following fields,
DESCRIBE leave_transaction

Column

LEAVE_ID
EMP_ID
EMP_NAME
GENDER
LEAVE_TYPE_NAME
LEAVE_TYPE_CODE
DESIGNATION
FROM_DATE
TO_DATE
NO_OF_DAYS
REASON
REMAINING_DAYS
Status

when they apply ,it will be shown as report in my superior page(i.e 5th page) He has to REJECT or APPROVE the leave applied and the "Status" column should be updated as REJECT or APPROVE based on superior action,
How it is possible in APEX 4.1?
Can you please Help me.
Thank you.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553861 is a reply to message #553860] Tue, 08 May 2012 23:09 Go to previous messageGo to next message
BlackSwan
Messages: 22691
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553862 is a reply to message #553861] Tue, 08 May 2012 23:17 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Sorry for posting here.Should I post in Apex forum or it will be moved to apex forum?
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553863 is a reply to message #553862] Tue, 08 May 2012 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is already moved to this forum.

Regards
Michel
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553867 is a reply to message #553863] Wed, 09 May 2012 00:14 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A simple option is to create two buttons: APPROVE and REJECT.
Create two processes (one for updating a status to APPROVE, another for REJECT) which will fire when a BUTTON is pressed. The process would do the smart part:
-- Fire when APPROVE button is pressed
update leave_transaction set
  status = 'APPROVE'                  -- another process would set it to REJECT
  where leave_id = :P5_LEAVE_ID;


Another option is to create radio buttons, checkbox, text item or any other option you find appropriate.

Maybe, once you make it work (with these two buttons), you might try to make it fancy (i.e. do the same using what I suggested next. It'll teach you how to create lists of values and stuff).
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553871 is a reply to message #553867] Wed, 09 May 2012 01:07 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Littlefoot,
Thank you for your response,
actually where to create the buttons with particular leave_application report?
for example when the report page is opened if it as 10 reports(for leave approval)
where to create the button? on report page or for all report(for leave approval),
can you please explain?
Thank you.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553873 is a reply to message #553871] Wed, 09 May 2012 01:17 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, the report! Didn't read carefully enough your initial post, sorry.

My suggestion was based on a FORM, not a report. However, it is easy to create a LINK on a report column which would pass LEAVE_ID (or anything else you need) to another (FORM) page, which would do what I described. I suppose that supervisors wouldn't like it much (as they would need to navigate forth and back for every leave requirement).

Therefore, perhaps you should switch from a report to a tabular form (updateable report). In there, fetch all columns you fetch now, plus additional APPROVE checkbox. Supervisor would check these checkboxes (for people whose leave requests are approved), and it would require a single SUBMIT button whose process would loop through all records in a tabular form, check whether the checkbox is checked and UPDATE a record in a table.

If you didn't do that yet, here's how the loop looks like (excerpt from one of my tabular forms):
  for i in 1 .. apex_application.g_f01.count loop

    if apex_application.g_f02(i) = 1 then       -- g_f02 is a tabular form checkbox
       select p.naziv
         into l_naziv
         from dg_slike_popis p
         where p.id = apex_application.g_f01(i);

       utl_file.putf(file_handler, l_naziv || '\n');
    end if;

  end loop;

For more information about tabular forms items, see the documentation.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553880 is a reply to message #553873] Wed, 09 May 2012 01:37 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Littlefoot,
as you told earlier, I created 2 buttons APPROVE and REJECT,
If the report have 2 rows,If I click the APPROVE then its setting status as APPROVE for both,
If I click the REJECT then its setting status as REJECT for both, Sad
Is it possible ,
when I press the APPROVE or REJECT button it has to ask Leave_id,
so that we can enter the leave_id from the report and the status has to be set based on the button and leave_id given.
Thank you. Smile
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553881 is a reply to message #553880] Wed, 09 May 2012 01:38 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Re-read what I said previously.

As far as I know (which is not much, unfortunately), REPORT is not the way to do that. TABULAR FORM is.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553885 is a reply to message #553881] Wed, 09 May 2012 01:51 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Littlefoot,
am started to do what you said,
but in report I specified the sql code which will show only the leave applied today(sysdate)
but here it fetching all rows from the table,
is this possible to write the sql code fetch leave applied today ?
Thank you.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553889 is a reply to message #553880] Wed, 09 May 2012 01:58 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Litttlefoot,
Now it showing in tabular form ,
what i have to do when the check box is selected(clicked) and pressed submit button,
the status has to be set Approved for the selected leave_id,
initially there was 4 buttons , I deleted 2 buttons now only CANCEL and SUBMIT buttons are here,can I use the same process which you mentioned before?
Thank you.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553890 is a reply to message #553889] Wed, 09 May 2012 02:03 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As of SYSDATE question: SYSDATE function returns both date and TIME, so you probably need to TRUNC(SYSDATE) in your WHERE clause.

As of the second question: code I posted is just an example. You need to adjust it to your situation. Hint: apex_application.g_f0x items reference only EDITABLE items in a tabular form (the ones you chose to be editable, and now have a mark on the Report Attributes page). As far as I know, this property can't be set after the region is created so - choose them wisely (or you might need to do everything once again).
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553892 is a reply to message #553890] Wed, 09 May 2012 02:21 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Littlefoot,
Can you explain me what your above code does,
and what is the meaning for this line,
if apex_application.g_f02(i) = 1 then 

what value the chck box have when the chekbox is not clicked and when it is clicked(checked)
here now I want to update when the checkbox is clicked then in Leave_transaction table status=approved and if not status=rejected,
Thank you,
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553897 is a reply to message #553892] Wed, 09 May 2012 02:54 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is my region source query:
select 
  id,
  id id_display,
  naziv,
  cb_delete
from dg_slike_popis
order by 1

CB_DELETE is NUMBER(1).
It is displayed as a "Simple checkbox".
Its List of values definition is "1,0" (put 1,0 into that field, without quotes) (1 is checked value, 0 is unchecked).

(For you, it might be CB_APPROVE instead. I'm deleting records, so I named the column CB_DELETE).

This is how my report attributes look like. Pay attention to "Edit" check mark.
./fa/10140/0/

It means that
- apex_application.g_f01 = ID => will be LEAVE_ID for you
- apex_application.g_f02 = CB_DELETE => will be CB_APPROVE for you

Process loops through all records in a tabular form ("for i in 1 .. apex_application.g_f01.count") and does *something* (I'm selecting, you'll be UPDATE-ing) IF a checkbox is checked ("if apex_application.g_f02(i) = 1" - as we said, g_f02 = CB_DELETE).

You'd do something like this:
update your_table set
  status = case when apex_application.g_f02(i) = 1 then 'APPROVED'
                else 'REJECTED'
           end
  where leave_id = apex_application.g_f01(i);

  • Attachment: edit.png
    (Size: 5.49KB, Downloaded 251 times)
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553902 is a reply to message #553897] Wed, 09 May 2012 04:02 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Littlefoot,
Quote:



This is how my report attributes look like. Pay attention to "Edit" check mark.

in your above image,
ID and CB_DELETE are checked,
I tried to click the same but its cannot be check so where to check and also by default
before the first column there is check box is available,In my condition what are the fields I have to check and where?
Thank you.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553906 is a reply to message #553902] Wed, 09 May 2012 04:42 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I told you (see message #553890): as far as I know, you MUST set editable items when you are creating a tabular form. This ("edit") property can't be modified later. In other words, don't just blindly click <Next> in Wizard - read what it says.

Which items should be editable? The ones you plan to edit, PLUS the ones you plan to reference later (using apex_application.g_fxx). I was editing CB_DELETE, and I was referencing ID. It is up to you to decide that.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553908 is a reply to message #553906] Wed, 09 May 2012 05:06 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Littlefoot,
Where to put the above code, whether I have to create process?
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553910 is a reply to message #553908] Wed, 09 May 2012 05:10 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553920 is a reply to message #553910] Wed, 09 May 2012 05:44 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Littlefoot,
I put the following code in process,
DECLARE
   v_rowno NUMBER;
BEGIN
  for i in 1 .. apex_application.g_f01.count 
loop
v_rowno := apex_application.g_f01(i);
 if apex_application.g_f02(i) = 1 then       
update leave_transaction set status='Approved' where
leave_id=v_rowno;
else
update leave_transaction set status='Rejected' where
leave_id=v_rowno;
end if;
end loop;
end;

If i checked the status checkbox its updating status=1 if not checked its set to 0.
instead of reject or approve.
Thank you.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553925 is a reply to message #553920] Wed, 09 May 2012 05:51 Go to previous messageGo to next message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Littlefoot,
Instead of 1,0 I given Approved,Rejected in List of values field,
when I checked for one row its updated as Approved
and another one I didnt checked but it not set as Rejected instead it showing -.
Thank you.
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553926 is a reply to message #553925] Wed, 09 May 2012 06:00 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I told you what I did. I told you how to set up list of values. I provided sample code.

You decided to change it and use your own options and code.

That's OK, as far as I'm concerned. But, why do you expect me (us) to do your job from A to Z? Why would I debug your code? When do you plan to do something by yourself?
Re: How to REJECT or APPROVE the request in APEX4.1 [message #553927 is a reply to message #553926] Wed, 09 May 2012 06:04 Go to previous message
gurujothi
Messages: 79
Registered: January 2012
Location: Banglore
Member
Hi Littlefoot,
Thank you,I will try to debug myself and I will made it to run.
Previous Topic: What is the best way to back up oracle application?
Next Topic: VALIDATION IN APEX
Goto Forum:
  


Current Time: Sat Aug 23 01:24:55 CDT 2014

Total time taken to generate the page: 0.09560 seconds