form basados en store proc

8/2/2019 Form Basados en Store Proc

1/22

Doc ID :Note:66887.1Content Type: TEXT/PLAINSubject:Basing a Block on a Stored Procedure - Sample CodeCreation Date: 02-DEC-1998Type:BULLETINLast Revision Date: 30-OCT-2002Status: PUBLISHEDPURPOSE=======

This bulletin gives sample coding on using a stored procedure with Forms.

SCOPE & APPLICATION===================

This bulletin is intended for the user who has a good knowledge on usingdatabase packages, procedures and functions. The user should also have a goodknowledge on using the Forms Debugger.

Tested with Forms version 5.0.x with Oracle 7.3.4, however the concepts in thisdocument apply to Forms versions 6.0.X and and the 8.X versions of the database.These techniques will not work for forms versions prior to 5.0.X.

BLOCK BASED ON A STORED PROCEDURE=================================

Introduction:------------

Why base a block on a stored procedure?---------------------------------------

Basing a block on a stored procedure is an advanced operation to do thefollowing:

1. Reduce network traffic through array processing as the sql statements areprocessed by the pl/sql engine on the server side.

2. Perform complex computations

3. Update and query multiple tables4. Perform validation and DML on server-side.

What is a REF Cursor?--------------------

REF cursors hold cursors in the same way that VARCHAR2 variables hold strings.This is an added feature that comes with PL/SQL v2.2. A REF cursor allows acursor to be opened on the server and passed to the client as a unit ratherthan one row at a time. One can use a Ref cursor as a target of assignmentsand can be passed as parameters to the Program Units. Ref cursors are openedwith an OPEN FOR statement and in all other ways, they are the same as regular

cursors.

What is a table of records?--------------------------

A table of records is a new feature added in PL/SQL v2.3. It is the equivalentof a database table in memory. If you structure the PL/SQL table of recordswith a primary key (an index) you can have array-like access to the rows.Table of records differ from arrays in that they are not bound by a fixed loweror higher limit. Nor do they require consecutive index numbers as arrays do.


2/22


3/22

TYPE b_cursor IS REF CURSOR RETURN bonus_rec;

-- Statement below needed if block is based on Table of RecordsTYPE bontab IS TABLE OF bonus_rec INDEX BY BINARY_INTEGER;

-- Statement below needed if using Ref CursorPROCEDURE bonus_refcur(bonus_data IN OUT b_cursor);

-- Statement below needed if using Table of RecordsPROCEDURE bonus_query(bonus_data IN OUT bontab);

--Statements below needed for both Ref Cursor and Table of RecordsPROCEDURE bonus_insert(r IN bonus_rec);PROCEDURE bonus_lock(s IN bonus.empno%TYPE);PROCEDURE bonus_update(t IN bonus_rec);PROCEDURE bonus_delete(t IN bonus_rec);

-- If this last function is not included you cannot use the-- Query -> count hits from the default menu of the forms and-- will get error frm-41003 Function cannot be performed here.

FUNCTION count_query_ RETURN number;

END bonus_pkg;

Step 3. Create the package body--------------------------------

PACKAGE BODY bonus_pkg IS

PROCEDURE bonus_query(bonus_data IN OUT bontab) ISii NUMBER;CURSOR bonselect IS

SELECT empno, ename, job, sal, comm FROM bonus;BEGINOPEN bonselect;ii := 1;LOOPFETCH bonselect INTObonus_data( ii ).empno,bonus_data( ii ).ename,bonus_data( ii ).job,bonus_data( ii ).sal,bonus_data( ii ).comm;

EXIT WHEN bonselect%NOTFOUND;ii := ii + 1;

END LOOP;

END bonus_query;

PROCEDURE bonus_refcur(bonus_data IN OUT b_cursor) ISBEGINOPEN bonus_data FOR SELECT empno, ename, job, sal, comm FROM bonus;

END bonus_refcur;

PROCEDURE bonus_insert(r IN bonus_rec) ISBEGININSERT INTO bonus VALUES(r.empno, r.ename, r.job, r.sal, r.comm);

END bonus_insert;


4/22


5/22

or "bonus_data.b_cursor" for a ref cursor.

* Set Mode to "IN/OUT" as the data is flowing between the client and serverand viceversa.

* Set Value (optional)

If you skip to set typename, you will hit an error. The possible compilation

error will be PL/SQL error 103 in the QUERY-PROCEDURE TRIGGER.

When you use the "table of records" as the source of query, Forms automaticallycreates a trigger like Query-Procedure to populate the values that are sentfrom the database through the stored procedure.

* Set the DML target type as "Transactional triggers" under the AdvancedDatabase section. This step is important. You must specify"transactional triggers" to avoid getting an error:FRM-40743: THIS OPERATION WITH NO BASE TABLE REQUIRES THE %S TRIGGER.

at runtime.Leave all other properties under the Advanced Database section blank.Note: You must use transactional triggers to perform all DML processing

as your block is based on stored procedures and not a table or view.If you do not provide these triggers (see code in Step 5) you willreceive runtime error: Frm-40401 No Changes To Save when afterperforming DML operations like insert, delete or update.

One more general example of setting the Query Data Source Arguments in theblock property palette could be,

ARGUMENTNAME TYPE TYPENAME MODE VALUE------------- ---- -------- ---- -----bonus_data REFCURSOR bonus_pkg.b_cursor IN OUT (leave blank)

Or bonus_data.b_cursorOR

bonus_data TABLE bonus_pkg.bontab IN OUT (leave blank)

Step 5. Create Transactional Triggers--------------------------------------Transactional triggers must be created a the block level as follows:

* On-insert trigger

DECLAREr bonus_pkg.bonus_rec;

BEGINr.empno := :bonus.empno;r.ename :=:bonus.ename;

r.job := :bonus.job;r.sal := :bonus.sal;r.comm := :bonus.comm;

bonus_pkg.bonus_insert(r);END;

* On_lock trigger

bonus_pkg.bonus_lock(:bonus.empno);


6/22


7/22

Step1: verification--------------------BEFORE ATTEMPTING TO DO THIS, VERIFY THE PRESENCE OF INTEGRITY CONSTRAINTSFOR THE TABLES INVOLVED IN THIS OPERATION.Verify if the tables have Primary key and Foreign Key relationship.Your Physical Database design is very important. Otherwise, you will be

getting errors likeORA-6502 Pl/sql: Numeric value error orORA-4098 Trigger 'X' is invalid and failed revalidation.ORA-4068 If any column name is not properly defined in the select

statement of the stored procedure.see note:2007482.6 for an explanation on how to do this.

Step2: Create a package spec at the database level---------------------------------------------------

PACKAGE emp_pkg ISTYPE emprec is RECORD(

empno emp.empno%type,ename emp.ename%type,job emp.job%type,mgr emp.mgr%type,hiredate emp.hiredate%type,sal emp.sal%type,comm emp.comm%type,deptno emp.deptno%type);

TYPE empcur IS REF CURSOR RETURN emprec;TYPE emptab IS TABLE OF emprec INDEX BY BINARY_INTEGER;

-- This procedure uses refcursor for query operation, it sends the data-- from the stored procedure to the client.

PROCEDURE empquery_refcur(emp_data IN OUT empcur,v_dno IN NUMBER);

-- This procedure uses table of records for query operation.-- One can use either ref cursor or table of records for query operation.PROCEDURE empquery(emp_data IN OUT emptab,

v_dno IN NUMBER);

-- This procedures inserts the data passed as a record from the emp block-- to the emp table.PROCEDURE emp_insert(r IN emprec);

-- Empno is getting passed from emp block to the stored procedure, this-- procedure is to lock that specific row that has that empno.

PROCEDURE emp_lock(s IN emp.empno%TYPE);

PROCEDURE emp_update(t IN emprec);

PROCEDURE emp_delete(t IN emprec);

-- If this last function is not included you cannot use the-- Query -> count hits from the default menu of the forms and-- will get error frm-41003 Function cannot be performed here.FUNCTION count_query_ RETURN NUMBER;


8/22

END emp_pkg;

Step 3. Create the package body--------------------------------

PACKAGE BODY emp_pkg IS

PROCEDURE empquery(emp_data IN OUT emptab,v_dno IN NUMBER) IS

ii NUMBER;CURSOR empselect ISSELECT empno, ename, job, mgr, hiredate, sal, comm, deptnoFROM empWHERE deptno = nvl(v_dno, deptno);

BEGINOPEN empselect;ii := 1;LOOPFETCH empselect INTOemp_data(ii).empno,

emp_data(ii).ename,emp_data(ii).job,emp_data(ii).mgr,emp_data(ii).hiredate,emp_data(ii).sal,emp_data(ii).comm,emp_data(ii).deptno;EXIT WHEN empselect%NOTFOUND;ii := ii + 1;

END LOOP;END empquery;

PROCEDURE empquery_refcur(emp_data IN OUT empcur,v_dno IN NUMBER) ASBEGINOPEN emp_data FORSELECT empno, ename, job, mgr, hiredate, sal, comm, deptnoFROM empWHERE deptno = nvl(v_dno, deptno);

END empquery_refcur;

!

PROCEDURE emp_insert(r IN emprec) ISBEGININSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)VALUES (r.empno, r.ename, r.job, r.mgr, r.hiredate, r.sal,


9/22

r.comm, r.deptno);COMMIT;

END emp_insert;

PROCEDURE emp_lock(s IN emp.empno%TYPE) ISv_rownum NUMBER;

BEGINSELECT empno

INTO v_rownumFROM empWHERE empno = sFOR UPDATE OF ename;

END emp_lock;

PROCEDURE emp_update(t IN emprec) ISBEGINUPDATE emp

SET ename = t.ename,job = t.job,mgr = t.mgr,hiredate = t.hiredate,

sal = t.sal,comm = t.comm,deptno = t.deptno

WHERE empno = t.empno;END emp_update;

PROCEDURE emp_delete(t IN emprec) ISBEGINDELETEFROM empWHERE empno = t.empno;

END emp_delete;

FUNCTION count_query_ RETURN NUMBER ISr NUMBER;BEGINSELECT count(*)INTO rFROM emp;

RETURN r;END count_query_;

END emp_pkg;

Step 4. Create the Form Block------------------------------

Build the block with datablock type as "table or view" for both the master(dept) and detail block (emp). Later the block property sheet will be adjustedfor the detail block to be based on stored procedure.The blocks can be built manually also, but, using wizard is preferrable. Here,you have the choice of having tab canvas.

Create the relationship between these blocks either explicitly or at the timeof creating the block using wizard, refer the later part of this note for moreinformation on this.


10/22

In the detail block propertysheet, (emp block)* set the Query Data Source Type as Procedure.* set the Query Data Source Name with the appropriate name of the storedprocedure.In this case, One can use either the Ref Cursor or Table of Records toperform this query operation. So, in this example, either use the procedureemp_pkg.empquery

or

emp_pkg.empquery_refcur

Because you began creating the block with data block as Table or view, you donot need to set anything in the Query Data Source Columns as the Forms doesthat job.

* set the Query Data Source Arguments with the appropriate argument name forthat query.In this case, emp_data is the argument name for both ref cursor and tableof records

* set the Type to table or refcursor, depending on the procedure you havechosen. This example uses the "Table".

* set the Type Name to the appropriate type, this will be emp_pkg.emptab

If you choose the ref cursor, it would have been emp_pkg.empcur oremp_data.refcur

* set Mode to "IN/OUT" as the data is flowing between the client and serverand viceversa.

* set Value (optional)

* repeat above steps for the other argument of the procedure, deptno.The appropriate values can be found in the table below

Yet another general example could be,

ARGUMENTNAME TYPE TYPENAME MODE VALUE------------ ---- -------- ---- -----

emp_data REFCURSOR emp_pkg.empcur IN OUT (leave it blank)ORemp_data TABLE emp_pkg.emptab IN OUT

AND

v_dno NUMBER dept.deptno%type IN :dept.deptno

If you skip to set typename, you will hit an error. The possible compilationerror will be:PL/SQL error 103 in the QUERY-PROCEDURE TRIGGER.

When you use the "table of records" as the source of query, Forms automaticallycreates a trigger like Query-Procedure to populate the values that are sent

from the database through the stored procedure.

* Set the DML target type as "Transactional triggers" under the AdvancedDatabase section. You must specify "transactional triggers" to avoidgetting error:FRM-40743: THIS OPERATION WITH NO BASE TABLE REQUIRES THE %S TRIGGER.

at runtime. Leave all other properties under the Advanced Database sectionblank.Note: You must use transactional triggers to perform all DML processing as

your block is based on stored procedures and not a table or view.If you do not provide these triggers (see code in Step 5) you will


11/22

receive runtime error:Frm-40401 No Changes To Save

after performing DML operations like insert, delete or update.

Step 5. Create Transactional Triggers--------------------------------------Then, from the forms at detail block level (emp), you have to create the

following triggers:

* On-insert trigger

DECLAREr emp_pkg.emprec;

BEGINr.empno := :emp.empno;r.ename :=:emp.ename;r.job := :emp.job;r.mgr := :emp.mgr;r.hiredate := :emp.hiredate;r.sal := :emp.sal;

r.comm := :emp.comm;r.deptno := :emp.deptno;

emp_pkg.emp_insert(r);END;

* On_lock trigger

emp_pkg.emp_lock(:emp.empno);

* On-update trigger

DECLAREt emp_pkg.emprec;

BEGINt.empno := :emp.empno;t.ename := :emp.ename;t.job := :emp.job;t.mgr := :emp.mgr;t.hiredate := :emp.hiredate;t.sal := :emp.sal;t.comm := :emp.comm;t.deptno := :emp.deptno;

emp_pkg.emp_update(t);

END;

* On-delete trigger

DECLAREt emp_pkg.emprec;

BEGINt.empno := :emp.empno;t.ename := :emp.ename;t.job := :emp.job;


12/22

t.mgr := :emp.mgr;t.hiredate := :emp.hiredate;t.sal := :emp.sal;t.comm := :emp.comm;t.deptno := :emp.deptno;

emp_pkg.emp_delete(t);END;

* On-count trigger

DECLARErecs NUMBER;

BEGINrecs := emp_pkg.count_query_;set_block_property('emp', query_hits, recs);

END;

-- This On-Count trigger is needed. The forms default-- processing will not return the query hits as you have

-- based the block on a stored procedure.

Step 6. Change the delete record behavoiur-------------------------------------------

Make sure the "delete record behaviour" property of the relation is set toisolated (non-isolated is the default in Developer 6).

You now have completed the process for basing a block on a stored procedure forMaster-Detail operations.

===============================================================================Questions:

1. What will happen if you change the "delete record behaviour" from isolatedto non-isolated or cascading?

If you change the "Delete record behaviour" from isolated to non-isolated,the On-Check-Delete-Master trigger will be created by forms in the masterblock. This will not understand the stored procedure you have used as aquery data source for the detail block. As a result, you will get aCompilation error in the On-Check-Delete-Master trigger that will be like:Pls-201 procedure name must be declared.

Similarly, if you change the "Delete record behaviour" from isolated tocascading, the forms generates the Pre-Delete trigger in the master blockwhich will give a compilation error as well, as the master block will notunderstand the procedure for the query data source on which the detail blockis based on.It will be costly to have integrity constraint at form level also as thepre-delete trigger repeats the same job as the constraints declared atdatabase side.So, do not try to change the "Delete Record Behaviour".See also bug:761722


13/22

If you have the proper integrity constraints added to your tables, it willbe automatically taken care of at the time of committing the record.For example, when you delete a master record while child records are there,at the time of saving this change, the form will provide an error message:Frm-40510: Oracle error: Unable to delete record.

If you do not have a foreign key constriant at all, and attempt to set the"delete behaviour" to cascading, it will give you:

Frm-30409 Delete record behaviour is invalid.

2. Why use Primary key and not rowid ?

The On_Lock trigger replaces the default forms locking, as a side effectprevents Forms from obtaining the ROWID for the row. In consequence you mustdefine a Primary Key for the block and use this PK to be passed as aparameter to the stored procedure.And also, your block must have a PK, otherwise, you will hit:Frm-30100 Block must have atleast one Primary Key item.

3. How to set the query criteria for detail block passing a value from themaster block?

If you have chosen the tab canvas as the canvas type and if you want to setthe query criteria in the detail block the same as master block, you cancreate a Key-Exeqry trigger at block level for the master block and callexecute_query from there;Also, you can create a When-Tab-Page-Changed trigger at form level to passthe query criteria from the master block to the detail and type thefollowing:

DECLAREpage_name varchar2(10);

tab_id tab_page;tab_id2 tab_page;BEGINpage_name := get_canvas_property('CANVAS8', TOPMOST_TAB_PAGE);IF page_name = 'PAGE11' THENgo_item('dept.deptno');

ELSEgo_item('empno');execute_query;

END IF;END;

You can set the "Copy value from item" of the item's property sheet in thedetail block of the item that has relationship with the master.

Here, in this example you can set the "copy value from item" property of thedeptno in the emp block to "dept.deptno".

See the note:1078147.6 for more explanation.

4. What to remember if you build the block manually?

In case of Master-Detail blocks, if you are passing a column to the storedprocedure that inturn returns data to the detail block and if the detailblock also has the same column present, you have to set the Column Name


14/22

Property of that specific text item to null or blank. Otherwise, you will begetting:Frm-40350 Query Caused no records to be retrieved.

In this case, if you build the block manually for the detail block, theColumn Name Property of the deptno in the dept block must be blank as it istaken care of by the stored procedure that uses a table of records or a refcursor.

5. Getting the error "Wrong no. of arguments to populate_block inquery_procedure". What should I do?

If you attempt to change the block properties manually to use refcursor fromtable of records, the Query data source name property and Query data sourcearguments should be set properly. If you feel you set it right and are stillgetting this compilation error, drop that procedure and let the formsbuilder generate a new one for you.

6. My query is performing extremely slow?

If your query matches or retrieves only small amount of records or if youare using where clause to filter the query results, then use a refcursorrather than using a table of records. As a ref cursor returns only therecords that match the query condition. But, a table of records tries tofetch all the records. So, performance will be slower with table of records.

Tips----

Provide an exceptional handler for all the possible ora errors likeORA-4098 and ORA-4068.

If you get:ORA-6502 Pl/sql: Numeric or Value erroryour table definition of column lengths do not go with the datatype or lengthof the text items in the forms.Use the debugger to see what values are getting passed from client to serverand viceversa.

Additional References for using the debugger:Bulletin 107982.466, Note 61692.1, and GSX Problem 1022763.6

Additional reference on basing a block on stored Procedure:Note: 52778.

Displayed below are the messages of the selected thread.Thread Status: ClosedFrom: ARCHANA K. DHINGRA 03-Dec-99 21:02Subject: Building a form to query from details as well as master


15/22

Building a form to query from details as well as master

Hi,I am trying to build a form with one master and four detail blocks. Two detail blocks have onerecord each in the tables for the master but other two have multiple records.

1.Is it possible to query and update from the two single record detail blocks for the entireform including the master?(No delete or insert is to be done in these detail records formaintaining one-one relationship between master and detail/s)

2. Is it possible to do update,delete and insert (no query) in the multi record detail blockswhen querying and populating from master and the two single-record details?

I tried to simulate with emp dept tables by building an external relation in emp block where empis the master and dept is the detail but at runtime the process just hangs. I would reallyappreciate your help on this problem.

Thanks in advance for your help!

Archana

From: Oracle, Abhijith Unnikannan 06-Dec-99 07:01Subject: Re : Building a form to query from details as well as master

Hello,

It is possible to build a form which queries the detail block andpopulates the master block. I am sending a small test form based onthe emp and dept tables which you can test and build upon it to meetyour requirement. This will require a bit of coding in your casebut I guess it can be done.

Have a nice day,Abhijith

From: Marco Mohrmann 08-Dec-99 07:21Subject: Re : Re : Building a form to query from details as well as master

Hi,

please, can I have a copy of the test form too (email: [email protected])?

Thanks

Marco

From: John Chang 09-Dec-99 16:35


16/22

Subject: Re : Building a form to query from details as well as master

Hi,

Please email me a copy of this test form. My email is [email protected] you

From: ARCHANA K. DHINGRA 09-Dec-99 22:25Subject: Re : Building a form to query from details as well as master

Hi Abhijith,I've sent you some queries per se to your e-mail address.Could you reply further!Thanks,Archana K. Dhingra


Hi Archana,

Replied just now!

Abhijith

John : Sent you the test file

From: HEMANT RANGOLE 13-Dec-99 19:11Subject: Re : Building a form to query from details as well as master

CAN I GET A COPY OF THE FORM FOE QUERYING A MASTER DETAIL BLOCK?

THANKSHEMANT [email protected]

From: Dana Alcivare 20-Dec-99 17:37Subject: Re : Building a form to query from details as well as master

Hi,May I have a copy as well?Thank you,Dana Alcivare


17/22

From: Dinis Paes 06-Jan-00 15:20Subject: Re : Building a form to query from details as well as master

Hello,

Can I have a copy of that example?

Thanks,

Dinis [email protected]

From: Ernesto Cruz 09-Feb-00 21:18Subject: Re : Building a form to query from details as well as master

Hi,Can I have a copy o the test form?

Thanks

Ernesto

From: colin phillips 20-Apr-00 13:31Subject: Re : Building a form to query from details as well as master

Would you please send me a copy of the test form.

to: [email protected]

Thanks

From: Krishnan Swaminathan 25-Jun-00 14:41Subject: Re : Building a form to query from details as well as master

Please send me a copy of the test form too (email: [email protected])

Thanks

N. Hammouda

From: Eirik Ellingsen 10-Jul-00 10:35Subject: Re : Building a form to query from details as well as master

Could I get a copy as well?


18/22

Best Regards,Eirik Ellingsen

From: Kris Brandt 13-Jul-00 19:51Subject: Re : Building a form to query from details as well as master

Please send me a copy too at [email protected]. Thanks.

From:john feng 11-Sep-00 15:23Subject: Re : Building a form to query from details as well as master

Please send me a copy too at [email protected], thanks.

John Feng

From: Oracle, Abhijith Unnikannan 12-Sep-00 04:14Subject: Re : Building a form to query from details as well as master

It is on the way !

Abhijith

From: Mark Whalley 13-Sep-00 15:42Subject: Re : Building a form to query from details as well as master

Could I get a copy of this. Thanks.

From: Oracle, Abhijith Unnikannan 14-Sep-00 04:02Subject: Re : Building a form to query from details as well as master

Another one sent !!

AbhijithOracle

From: Svetlana Nikic 13-Nov-00 19:47Subject: Re : Building a form to query from details as well as master

I need help figuring this out as well. I sent you an e-mail, but wanted to post this reply as well.

Thanks for your help.


19/22

Svetlana NikicSvetlana NikicThe Orvis Company, Inc.

From: Oracle, Abhijith Unnikannan 14-Nov-00 04:48Subject: Re : Building a form to query from details as well as master

I have sent the sample Form.

Abhijith

From: Chitra Tondwalkar 15-Nov-00 16:37Subject: Re : Building a form to query from details as well as master

Abhijit,

Can I have a copy of the form please ?email [email protected]

ThanksChitra


Sure !

Abhijith

From: Ramesh Thangarajan 20-Nov-00 20:43Subject: Re : Building a form to query from details as well as master

Hi,Can I also have the demo forms.My mail-id is [email protected].

Thanks

T.Ramesh


Yes


20/22

From: chandrasekhar jagarlamudi 10-Jan-01 21:37Subject: Re : Building a form to query from details as well as master

Please email me a copy of this test form.My email is [email protected]

Thank you

From: Oracle, Abhijith Unnikannan 11-Jan-01 05:14Subject: Re : Building a form to query from details as well as master

I have sent it. Meanwhile here is the note on how to createthis Form :

How to query a Master record from a Detail Block Abhijith Unnikannan

Oracle Developer Analysthttp://technet.oracle.com

From: Mitra Park 10-Dec-01 01:50Subject: Re : Building a form to query from details as well as master

Please send me a copy too at [email protected], thanks very much !!!

From: Oracle, Abhijith Unnikannan 10-Dec-01 10:20

Subject: Re : Building a form to query from details as well as master

Sent it.

From: Mukherjee Dona 14-Dec-01 15:51Subject: Re : Building a form to query from details as well as master

Hi Abhijit,

I have done quering the master record from detail block in my own way. Can you send me the onewhich you talked about.I would like to see how it can be solved differently.

My address is: [email protected]

Thanks a bunch,Dona



21/22

Have a look at the link that I gave on 11-Jan-01 05:14

From: Issa Elkhoury 19-Dec-01 06:52Subject: Re : Building a form to query from details as well as master

can i have a copy of test form

From: Rukmani Parameswaran 18-Jan-02 14:54Subject: Re : Building a form to query from details as well as master

Could you please send me a copy of the form to query both master and detail blocks in the same Screen.

Me email id is [email protected]

Thanks,Rukmani

From: Oracle, Abhijith Unnikannan 19-Jan-02 07:04Subject: Re : Building a form to query from details as well as master

I have sent it. Meanwhile here is the note on how to createthis Form :

How to query a Master record from a Detail Block

From: Connie Adams 29-May-02 01:35Subject: Re : Building a form to query from details as well as master

Would your test form also apply to the following example? If so, please send me a copy of the form.

BUILDING(Bldg_id,Div_id,Bldg_name)DIVISION(Div_id,Div_name)EMPLOYEE(Emp_id,Emp_name,Job_id,Div_id)JOBS(Job_id,Job_title)

Based on the Bldg_name, I would like to return the following detail record.

Div_name,Emp_name,Job_Title

---------------------------Thanks in advance,

Connie


22/22

From: Oracle, Abhijith Unnikannan 29-May-02 12:50Subject: Re : Building a form to query from details as well as master

Connie,

This sample is based on the EMP?DEPT master detail relation ship. It should also work for otherMaster/Detail relations as well. Please look into the sample that I have sent as well as the note that Ihave provided above as a link, please follow the steps mentioned there to create a form for yourapplication.

Abhijith

From: IFS Sverige . 11-Jun-02 12:27Subject: Re : Building a form to query from details as well as master

HelloPl send me also the copy of the test form .Thanks

[email protected]

From: Mercedes Miguel 18-Jul-02 10:48Subject: Re : Building a form to query from details as well as master

May I get a copy too? ([email protected])Thank You

M. Miguel

From: Samuel John 14-Nov-02 15:14Subject: Re : Building a form to query from details as well as master

Hi Abhijith,I have a form which has two levels of Master Detail relation. I want to query on a field in the third

level . In our example we have a form which tracks the request for Equipments. So we have 1)RequestHeader 2) Request lineOn each Request line based on the quantity of equipment type requested we have 3) Equipment Usage.Now if we have to find out who is using a specific Equipment (This information will be in the 3)Equipment Usage), then we have to query the third level.

If possible please send a test caseThanksSam

form basados en store proc

Documents