OraFAQ Forum: SQL & PL/SQL » UNION and dependent LOV

Home » SQL & PL/SQL » SQL & PL/SQL » UNION and dependent LOV (Oracle R12)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UNION and dependent LOV [message #661949]

Fri, 07 April 2017 01:37

umamagi
Messages: 1
Registered: April 2017

Junior Member

Hi all,

I have a requirement to create a valueset which will display LOT_NUMBERS from two tables.

1. MTL_LOT_NUMBERS and
2. A custom table XX_CUTS_TABLE

Both of the above tables have column LOT_NUMBER.

From first table only the lot_numbers corresponding to the organization selected on previous value set needs to be displayed along with all the lot_numbers from second table.

How could I achive this? Please help here.

Report message to a moderator

Re: UNION and dependent LOV [message #661950 is a reply to message #661949]

Fri, 07 April 2017 02:42

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Join the table on LOT_NUMBER (if this is the common column).

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Fri, 07 April 2017 02:43]

Report message to a moderator

Re: UNION and dependent LOV [message #661951 is a reply to message #661949]

Fri, 07 April 2017 02:49

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

You do know that LOT_NUMBER is not unique in MTL_LOT_NUMBERS, not even when combined with ORGANIZATION_ID? The unique keys are these:

MTL_LOT_NUMBERS_U1	NORMAL	UNIQUE	TablespaceAPPS_TS_TX_IDX	INVENTORY_ITEM_ID 
                                                                        ORGANIZATION_ID 
                                                                        LOT_NUMBER
MTL_LOT_NUMBERS_U2	NORMAL	UNIQUE	TablespaceAPPS_TS_TX_IDX	GEN_OBJECT_ID

Report message to a moderator

Re: UNION and dependent LOV [message #661962 is a reply to message #661951]

Sat, 08 April 2017 03:20

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"LOV" and "R12" look like Forms might be involved. If that's so, when you select LOT_NUMBER from the first table (MTL_LOT_NUMBERS) and it is now in a form item, you can use it in second table's (XX_CUTS_TABLE) LOT_NUMBER List of Values by referencing MTL_LOT_NUMBERS.LOT_NUMBER in second item's LoV (i.e. its Record Group query), such as

select whatever
from xx_cuts_table
where lot_number = :mtl_lot_numbers.lot_number

Report message to a moderator

Previous Topic:	Pre Insert logic in Trigger
Next Topic:	Hierarchical Queries

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 17:22:53 CDT 2024