Solved

Oracle APEX Single Record Report or Review

Posted on 2011-03-21
5
1,303 Views
Last Modified: 2012-05-11
All,

I have an application which has a form where users submit accounts for review. It is working great and populates a table in my schema, but of course we need to have these accounts reviewed by my team. I am attempting to design a button that will pull a single random account that was submitted into a report/form (not sure which to use) so that my team can review them for errors and have an 'accept' or 'deny' button.

What would be the best method for designing such a review. The button would need to execute PL/SQL that would update the 'user id' column of the person clicking the button and thus "assigning the account to them" and display that single record in this report/form so that they can easily review it for approval. I have already written the SQL that will pull a random record from the table. The Accept or Reject buttons would insert a Y or N into the accepted column.

We are using Oracle 11g and Apex 3.2. I am just not the best at PL/SQL and not sure how to get such a button to launch this form.

On a separate note I have used JQuery and have seen the posts on how to create a form via JQuery but not sure how to bring in a random loan with by executing SQL.
0
Comment
Question by:mjfigur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:gopisera
ID: 35185842


When the user submits the account.  Then the account will be in review (Say,  Y or N are not placed in the accepted column).




I can suggest you to display  the items which are to be reviewed (Say,  Y or N are not placed in the accepted column)  as a "Report" mode.


1 st method in 3.2 oracle apex

Display as a report.
Then for a column (linn on the column or create a button for every account in a column) which are to be reviewed and on clicking the column or button, redirect the page to another page for "accept" or "Reject" with displaying the full details.
After clicking the Accept or Reject button redirect again to the same page (where the display items to be reviewed)



2nd method in 4.0 oracle apex


You can user ajax in oracle apex very easily.

Display as a report.
Then create a check box which will accept Y or N /  Select box (of Reject or Accepted) on each row of the report.
On selecting the account,  You can directly update the record without reloading the entire page.




Hoping this will help.

If you need more information on linking the column in a report  to another page.
Let me know will explain in more detail.
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 35189012
I'm not sure I understand your process flow. Do you want the review team to
a) look at the entire list of accounts submitted for review and then click a "Review Random Account" button or
b) not look at the list of accounts but instead access a separate page that displays a random account?

In either case, you will need to have a hidden (and protected, if necessary) item on the second page, say :ACCOUNT_ID. If you're in the first scenario, then on the first page (containing the list of accounts) you will have an After Submit page process that populates the ACCOUNT_ID with a random selection from the report. If you're in the second scenario, you will have a Before Load page process that randomizes the ACCOUNT_ID with a value from the recently submitted reports.

0
 

Author Comment

by:mjfigur
ID: 35189555
Sorry if it was not clear the process would be the second scenario where a single person could only work one random account at a time and then when complete would move onto the next one.
0
 
LVL 20

Accepted Solution

by:
gatorvip earned 500 total points
ID: 35189737
Then what you probably want to do is what I said above.

- Create a hidden page item :ACCOUNT_ID.
- When a reviewer loads the page, populate the ACCOUNT_ID with a random value from your set, using either a Page Computation / Before Header, or a PL/SQL Page Process / On-Load Before Header. A process would probably better because you could set a "lock" on the record so that two reviewers wouldn't be able to review the same account at the same time. Now, you may want to have a fail-safe design where if the user loads an account then closes the browser, the lock on the record would be released, or the user would load the same record upon reconnecting.
- the user id is easy to set, using the application item :APP_USER . If you use a page process, you could have something like

update accounts
set reviewer_id = :APP_USER,
     review_started = sysdate,
     locked = 'Y'
where account_id = :ACCOUNT_ID;

to acquire the lock.

When the reviewer approves/rejects the account, then you have another PL/SQL Process, On-Submit/After Validations and Computations, where you do something like this:

update accounts
set lock = 'N',
     review_ended = sysdate,
     result = :REVIEW_RESULT
where account_id = :ACCOUNT_ID;

and Review_Result is Y / N

There are quite a few more things you can do here, but this should be a pretty good start. You need to have a good plan in place on how to deal with concurrency, if that is a concern for your environment.
0
 

Author Closing Comment

by:mjfigur
ID: 35192753
Thank you so much this will be a great start! We see how it works and get reviewing the PL/SQL code today.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question