CZ - List published models in Configurator Repository

select (Select vl.application_name
from fnd_application_vl vl
where vl.application_id = ma.fnd_application_id) app_name,
ma.fnd_application_id,
ma.publication_id,
mp.remote_publication_id,
p.Name Model_Name,
p.Desc_text,
ma.publication_mode,
ma.product_key,
(Select mtl.segment1
from mtl_system_items mtl where mtl.inventory_item_id =
ma.inventory_item_id and mtl.organization_id = ma.bom_explosion_org_id) ITEM,
ma.start_date,
ma.disable_date,
ma.server_id,
ma.ui_def_id,
you.Name Usage
--, ma.*
from
cz_model_applicabilities_v ma,
cz_model_publications mp,
cz_model_usages you,
cz_devl_projects p
where
you.model_usage_id = ma.usage_id
and ma.publication_id = mp.publication_id
and p.devl_project_id = ma.model_id
and mp.deleted_flag = 0
and mp.disabled_flag = 0
and ma.inventory_item_id is not null
and fnd_application_id = 880
and language = 'US'
and mp.source_target_flag = 'T'
and Decode (mp.UI_STYLE, 0, 'DHTML', 3, 'APPLET', 7, 'JRAD', 'NA') = 'JRAD'
and upper(mp.publication_mode) =
(
select upper(
COALESCE
(
(select v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = FND_GLOBAL.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id)
)
) PUB_MODE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_MODE'
)
and you.name =
(
select
COALESCE
(
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = fnd_global.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id
)
) USAGE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_USAGE'
)

Order by model_name

-----------------------------------------
--debugging scripts :
SELECT * FROM cz.cz_db_logs WHERE message_id='353320' --publication id
SELECT model_id,publication_id from cz_model_publications where export_status = 'ERR'
SELECT intl_text_id, persistent_intl_text_id,text_str,ui_def_id FROM cz_intl_texts where persistent_intl_Text_id = 33125520; --persistent id
SELECT model_id,ui_def_id,intl_text_id,creation_date from cz_intl_texts where persistent_intl_text_id = 33125520 --persistent id
SELECT intl_text_id, text_str, persistent_intl_text_id, seeded_flag, ui_def_id, model_id FROM cz_intl_texts where ui_def_id = 23080 AND deleted_flag = 0;
SELECT * from cz_model_publications WHERE object_type = 'UIT' ;

select ui_def_id from cz_ui_defs c
where c.DEVL_PROJECT_ID=(select max(DEVL_PROJECT_ID)
from cz_devl_projects dp , mtl_system_items_b msi
where dp.inventory_item_id=msi.INVENTORY_ITEM_ID
and segment1= 'AT2200-10H')