DIS Items, Folders and Formulas

Description

Categories: Enginatics
Repository: Github

Discoverer items (expressions) and folders (objects), including join conditions and formulas (calculated items)

DIS Items, Folders and Formulas and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

select
xxen_util.dis_business_area(eo.obj_id,'&eul') business_area,
eo.obj_name folder_name,
xxen_util.dis_folder_type(eo.obj_type) folder_type,
nvl2(eo.sobj_ext_table,nvl2(uv.view_name,'View','Table'),null) object_type,
eo.sobj_ext_table object_name,
&sql_text_columns
eo2.obj_name join_folder_name,
xxen_util.meaning(ee.fk_mstr_no_detail,'SYS_YES_NO',700) outer_join,
ee.exp_name item_name,
ee.key_name join_name,
xxen_util.dis_item_type(ee.exp_type) item_type,
decode(ee.exp_data_type,1,'Varchar',2,'Number',3,'Long',4,'Date',5,'Raw',6,'Large binary object',8,'Char',10,null,ee.exp_data_type) data_type,
ee.it_ext_column db_column_name,
xxen_util.dis_formula_sql(ee.exp_id,'&eul') formula,
ee.exp_formula1,
ee.exp_id,
ee.exp_type,
eo.obj_id
from
(
select
ee.*,
ekc.key_name,
ekc.key_obj_id,
ekc.fk_obj_id_remote,
ekc.fk_mstr_no_detail
from
&eul.eul5_expressions ee,
&eul.eul5_key_cons ekc
where
decode(ee.exp_type,'JP',ee.jp_key_id)=ekc.key_id(+)
) ee,
(
select
(
select
xxen_util.long_to_clob('SYS.VIEW$', 'TEXT', v.rowid) text
from
sys."_CURRENT_EDITION_OBJ" o,
sys.view$ v,
sys.user$ u
where
u.name='APPS' and
eo.sobj_ext_table=o.name and
o.obj#=v.obj# and
o.owner#=u.user#
) text,
eo.*
from
&eul.eul5_objs eo
) eo,
&eul.eul5_objs eo2,
user_views uv
where
1=1 and
decode(ee.exp_type,'FIL',ee.fil_obj_id,'CI',ee.it_obj_id,'CO',ee.it_obj_id,'JP',ee.key_obj_id)=eo.obj_id(+) and
ee.fk_obj_id_remote=eo2.obj_id(+) and
eo.sobj_ext_table=uv.view_name(+)
order by
eo.obj_name,
decode(ee.exp_type,'FIL',2,1),
ee.exp_sequence

Parameter Name

SQL text

Validation

Folder

eo.obj_name like :folder

LOV

Folder Identifier

eo.obj_developer_key=:folder_identifier

LOV

Folder Type

eo.obj_type=decode(:folder_type,'Standard','SOBJ','Complex view','COBJ','Custom SQL','CUO')

LOV

View Name

eo.sobj_ext_table=:view_name

LOV

Item Id

ee.exp_id=:item_id

Number

Object Id

eo.obj_id=:object_id

Number

Used by Workbook

eo.obj_developer_key in (
select
eex.ex_to_par_devkey
from
eul_us.eul5_documents ed,
eul_us.eul5_elem_xrefs eex
where
ed.doc_name=:workbook and
ed.doc_id=eex.ex_from_id and
eex.ex_from_type='DOC'
)

LOV

Item

ee.exp_name=:item

LOV

Item Type

ee.exp_type=decode(:item_type,'Condition','FIL','Calculated Item','CI','Item','CO','Join Predicate','JP')

LOV

Show Object SQL

xxen_util.dis_folder_sql2(eo.obj_id,'&eul') folder_sql,
case when lower(dbms_lob.substr(eo.text,15,length(eo.text)-14))=' with read only' then substr(eo.text,1,length(eo.text)-15) else eo.text end view_sql,