Solved

Oracle APEX Single Record Report or Review

Posted on 2011-03-21
5
1,277 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
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:gopisera
Comment Utility


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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now