OBIA 11.1.1.7.1 BIACM Querys

OBIA 11.1.1.7.1 BIACM 

BR100 Querys

When Creating a Document on Domain values that have been mapped to target values etc in BIACM we can use the BIACOMP schema to derive various pieces of information.

Below are some querys that can be used to get information on mappings etc.

Offerings enabled

select offering_code,offering_name
from c_bia_offering
where installed_flag = 1;

Functional Areas in the Offerings

select offering_key,funcarea_key
from c_bia_offering_funcarea_rel where offering_key in
('FIN_AN_OFRNG',
'PROC_SPEND_AN_OFRNG',
'SCOM_AN_OFRNG')
order by 1,2

Data Load Parameters

select c_param.param_category_key,c_param.param_code,
c_param.param_key,c_param.param_name
,c_param.param_descr,c_param.param_type,c_param.param_value_type,param_value_varchar2
,param_value_date,param_value_number,c_param_dw_val.datasource_num_id
from c_param
,c_param_dw_val
where c_param.param_key = c_param_dw_val.param_key
and product_line_version_key = 'EBS_12_1_3'
--and c_param.param_code = 'HR_ACCRUAL_OTHER_AMT'
and datasource_num_id > 0
order by datasource_num_id,c_param.param_category_key,c_param.param_code;

 Top level mapping

select distinct dom_map.src_domain_key,
c_domain.domain_code source_code,src_dom.domain_name src_domain_name
,dom_map.trg_domain_key
,trg.domain_code target_code,trg_dom.domain_name trg_domain_name
from c_domain_map dom_map
,c_domain
,c_domain_tl src_dom
,c_domain trg
,c_domain_tl trg_dom
where  
dom_map.src_domain_key = c_domain.domain_key
and dom_map.src_domain_key   = src_dom.domain_key
and src_dom.src_language_code = 'US'
and dom_map.trg_domain_key    = trg.domain_key
and dom_map.trg_domain_key   = trg_dom.domain_key
and trg_dom.src_language_code = 'US'
--and trg.ref_product_line_key = 'EBS';

 All Mappings and Member Domain Values etc 

select q1.*,dom_map.src_domain_member_code,dom_map.trg_domain_member_code,dom_map.datasource_num_id
,source_member.domain_member_name source_member_name
,target_member.domain_member_name target_member_name
 from c_domain_member_map dom_map
,c_domain_member dom_member
,(select distinct dom_map.src_domain_key,
c_domain.domain_code source_code,src_dom.domain_name src_domain_name
,dom_map.trg_domain_key
,trg.domain_code target_code,trg_dom.domain_name trg_domain_name
from c_domain_map dom_map
,c_domain
,c_domain_tl src_dom
,c_domain trg
,c_domain_tl trg_dom
where
dom_map.src_domain_key = c_domain.domain_key
and dom_map.src_domain_key   = src_dom.domain_key
and src_dom.src_language_code = 'US'
and dom_map.trg_domain_key    = trg.domain_key
and dom_map.trg_domain_key   = trg_dom.domain_key
and trg_dom.src_language_code = 'US'
and trg_dom.domain_name in ('Conformed Currency Rate Type')
) q1
,(select * from c_src_domain_member_tl where language_code = 'US') source_member
,(select * from c_domain_member_tl where language_code = 'US') target_member
where dom_map.src_domain_key = q1.src_domain_key
and dom_member.domain_key = q1.trg_domain_key
and   dom_map.src_domain_member_code = dom_member.domain_member_code
and dom_map.domain_plv_key = source_member.domain_plv_key
and dom_map.src_domain_member_code = source_member.domain_member_code
and q1.trg_domain_key = target_member.domain_key
and dom_map.trg_domain_member_code = target_member.domain_member_code
order by source_code,target_code,src_domain_member_code;

Cheers

Krishna Mohan & Shahed M