APPSINFODB – Information Technology Group

Finding Solutions for DBA's to smile…….!!!!!!! DBA its just not Administration, its core of customer’s data integrity

  • Blog Stats

    • 36,440 hits
  • Visitors

    free counters
  • Vistors Access

  • Calendar

    September 2014
    M T W T F S S
    « Aug   Oct »
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  
  • Archives

  • Authors

Implementing Row–LOV

Posted by appsinfo on September 18, 2014

Implementing Row–LOV

To implement a Row–LOV, create an LOV that selects the primary key of the row the user wants into a form parameter, and then copy that value into the primary key field in the results block right before executing a query.

This example uses the DEPT block, which is based on the DEPT table, and consists of the three columns DEPTNO, DNAME and LOC. This table contains a row for each department in a company.

Step 1: Create a Parameter for Your Primary Key

Create a form parameter(s) to hold the primary key(s) for the LOV. If the Row–LOV is for a detail block, you do not need a parameter for the foreign key to the master block (the join column(s)), as you should include that column in the WHERE clause of your record group in a later step. Set the datatype and length appropriately.

For example, for the DEPT block, create a parameter called DEPTNO_QF.

Step 2: Create an LOV

Create an LOV that includes the columns your user needs to identify the desired row. If the Row–LOV is for a detail block, you should include the foreign key to the master block (the join column(s)) in the WHERE clause of your record group. Return the primary key for the row into the parameter.

For our example, create an LOV, DEPT_QF, that contains the columns DEPTNO and DNAME. Set the return item for DEPTNO into parameter DEPTNO_QF. Although the user sees DNAME , it is not returned into any field.

Step 3: Create a PRE–QUERY Trigger

Create a block–level PRE–QUERY trigger (Execution Style: Before) that contains:

IF :parameter.G_query_find = ’TRUE’ THEN

<Primary Key> := :parameter.<Your parameter>;

:parameter.G_query_find := ’FALSE’;

END IF;

For multi–part keys, you need multiple assignments for the primary key.

The parameter G_query_find exists in the TEMPLATE form.

For the Dept example, your PRE–QUERY trigger contains:

IF :parameter.G_query_find = ’TRUE’ THEN

:DEPT.DEPTNO := :parameter.DEPTNO_QF

:parameter.G_query_find := ’FALSE’;

END IF;

 Step 4: Create a QUERY_FIND Trigger

Finally, create a block–level user–named trigger QUERY_FIND on the results block (Execution Style: Override) that contains:

APP_FIND.QUERY_FIND(’<Your LOV Name>’);

For DEPT:

APP_FIND.QUERY_FIND(’DEPT_QF’);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: