Hello,
I have a couple of tables like:
Table REGION_AREA with columns:
REGION_ID
REGION_Name
...
Table REGION_TERRITORY with columns:
TERRITORY_ID
TERRITORY_NAME
TOP_TERRITORY_ID
...
In REGION_TERRITORY , if TOP_TERRITORY_ID is not null, it means this row is actually a sub-territory. Such as:
TERRITORY_ID TERRITORY_NAME TOP_TERRITORY_ID REGION_ID
3 TopTerrA 9
4 TopTerrB 9
5 SubTerrC 3 9
In Table Rep, I have territory_ID assigned to each rep row.
Then I need to build a query something like this:
select region, REGION_TERRITORY.region_id, TERRITORY_NAME, LAST_NAME||', '||FIRST_NAME as RepName
from REGION_AREA,REGION_TERRITORY,REP
where REGION_AREA.region_id = REGION_TERRITORY.region_id
and Rep.TERRITORY_ID=REGION_TERRITORY.TERRITORY_ID
The question is how I can insert one more column called Parent_Territory_Name when territory of the row is a sub-territory. Say, for SubTerrC, I want to list its top_territory_ID's territory name. Can I build a interactive report by this?
Meanwhile, is there any good way to display this territory/sub-territory relationship in APEX besides report? Is it possible to use a list view, like windows explore that allow user to expand each territory? Thanks!