Siebel SQLs/Error Messages >  Find all List Applet Fields with SQL

Find all the fields in a List Applet with SQL:

WITH ITEMS AS
(SELECT lcl.NAME control_name,
a.NAME applet_name,
bc.NAME buscomp_name,
fld.NAME field_name,
fld.COL_NAME field_col_name,
fld.JOIN_NAME field_join_name,
fld.multivalINK_NAME field_multivalink_name,
fld.BUSCOMP_ID,
fld.PICKLIST_NAME field_picklist_name,
(
CASE
WHEN (fld.multivalINK_NAME IS NOT NULL)
THEN ('MVG')
WHEN (fld.CALCULATED = 'Y')
THEN ('Calculated')
WHEN (fld.JOIN_NAME IS NOT NULL)
THEN (
CASE
WHEN (bcu.VALUE IS NOT NULL)
THEN (
CASE
WHEN (fld.JOIN_NAME <> bcu.VALUE)
THEN ('Joined Field')
ELSE (
CASE
WHEN (fld.PICKLIST_NAME IS NOT NULL)
THEN ('Picklist')
WHEN (fld.TYPE = 'DTYPE_BOOL')
THEN ('Boolean')
ELSE ('Field')
END)
END)
ELSE ('Joined Field')
END)
ELSE (
CASE
WHEN (fld.PICKLIST_NAME IS NOT NULL)
THEN ('Picklist')
WHEN (fld.TYPE = 'DTYPE_BOOL')
THEN ('Boolean')
ELSE ('Field')
END)
END) field_type,
(DECODE(fld.JOIN_NAME, NULL, NULL,
(SELECT j.DEST_TBL_NAME
FROM siebel.s_join j
WHERE fld.JOIN_NAME = j.NAME
AND j.BUSCOMP_ID = fld.BUSCOMP_ID
AND j.INACTIVE_FLG = 'N'
AND j.REPOSITORY_ID = fld.REPOSITORY_ID
))) join_table_name,
(DECODE(fld.JOIN_NAME, NULL, NULL,
(SELECT MAX(fj.COL_NAME)
FROM siebel.s_field fj
WHERE fj.BUSCOMP_ID = fld.BUSCOMP_ID
AND fj.INACTIVE_FLG = 'N'
AND fj.REPOSITORY_ID = fld.REPOSITORY_ID
AND fj.NAME IN
(SELECT sjoin.SRC_FLD_NAME
FROM siebel.s_join_spec sjoin
WHERE sjoin.JOIN_ID IN
(SELECT SIEBEL.S_JOIN.ROW_ID
FROM SIEBEL.S_JOIN
WHERE SIEBEL.S_JOIN.BUSCOMP_ID = fld.BUSCOMP_ID
AND SIEBEL.S_JOIN.NAME IN
(SELECT ji.NAME
FROM siebel.s_join ji
WHERE fld.JOIN_NAME = ji.NAME
AND ji.BUSCOMP_ID = fld.BUSCOMP_ID
AND ji.INACTIVE_FLG = 'N'
AND ji.REPOSITORY_ID = fld.REPOSITORY_ID
)
)
)
))) AS join_src_column,
(DECODE(fld.multivalINK_NAME, NULL, NULL, NVL(
(SELECT lnk.INTER_TBL_NAME
FROM siebel.s_multivalink multival
INNER JOIN siebel.s_link lnk
ON multival.DEST_LINK_NAME = lnk.NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
WHERE multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
),
(SELECT DECODE(bcu2.VALUE, NULL, buscomp2.TABLE_NAME, bcu2.VALUE)
FROM siebel.s_multivalink multival
INNER JOIN siebel.s_link lnk
ON multival.DEST_LINK_NAME = lnk.NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
INNER JOIN siebel.s_buscomp buscomp2
ON lnk.CHILD_BC_NAME = buscomp2.NAME
AND buscomp2.REPOSITORY_ID = lnk.REPOSITORY_ID
LEFT OUTER JOIN siebel.s_buscomp_uprop bcu2
ON bcu2.BUSCOMP_ID = buscomp2.ROW_ID
AND bcu2.REPOSITORY_ID = buscomp2.REPOSITORY_ID
AND bcu2.NAME = 'Inner Join Extension Table 1'
WHERE multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
AND buscomp2.INACTIVE_FLG = 'N'
)))) mvg_dest_table,
(DECODE(fld.multivalINK_NAME, NULL, NULL, NVL(
(SELECT lnk.IPARENT_COL_NAME
FROM siebel.s_multivalink multival
INNER JOIN siebel.s_link lnk
ON multival.DEST_LINK_NAME = lnk.NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
WHERE multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
), (
CASE
WHEN (EXISTS
(SELECT NVL(fld2.COL_NAME, 'ROW_ID')
FROM siebel.s_multivalink multival,
siebel.s_link lnk,
siebel.s_buscomp buscomp2,
siebel.s_field fld2
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.CHILD_BC_NAME = buscomp2.NAME
AND fld2.BUSCOMP_ID = buscomp2.ROW_ID
AND fld2.NAME = lnk.DST_FLD_NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND buscomp2.REPOSITORY_ID = lnk.REPOSITORY_ID
AND fld2.REPOSITORY_ID = buscomp2.REPOSITORY_ID
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
AND buscomp2.INACTIVE_FLG = 'N'
AND fld2.INACTIVE_FLG = 'N'
))
THEN
(SELECT NVL(fld2.COL_NAME, 'ROW_ID')
FROM siebel.s_multivalink multival,
siebel.s_link lnk,
siebel.s_buscomp buscomp2,
siebel.s_field fld2
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.CHILD_BC_NAME = buscomp2.NAME
AND fld2.BUSCOMP_ID = buscomp2.ROW_ID
AND fld2.NAME = lnk.DST_FLD_NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND buscomp2.REPOSITORY_ID = lnk.REPOSITORY_ID
AND fld2.REPOSITORY_ID = buscomp2.REPOSITORY_ID
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
AND buscomp2.INACTIVE_FLG = 'N'
AND fld2.INACTIVE_FLG = 'N'
)
ELSE
(SELECT DECODE(lnk.DST_FLD_NAME, 'Id', 'ROW_ID', 'Created', 'CREATED', 'Created By', 'CREATED_BY', 'Updated', 'LAST_UPD', 'Updated By', 'LAST_UPD_BY', 'Mod Id', 'MODIFICATION_NUM', 'Conflict Id', 'CONFLICT_ID', NULL, 'ROW_ID')
FROM siebel.s_multivalink multival,
siebel.s_link lnk,
siebel.s_buscomp buscomp2
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.CHILD_BC_NAME = buscomp2.NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND buscomp2.REPOSITORY_ID = lnk.REPOSITORY_ID
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
AND buscomp2.INACTIVE_FLG = 'N'
)
END)))) mvg_dest_column,
(DECODE(fld.multivalINK_NAME, NULL, NULL,
(SELECT NVL(multival.PRIMEID_FLD_NAME, lnk.PR_ID_FLD_NAME)
FROM siebel.s_multivalink multival,
siebel.s_link lnk
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
))) mvg_primary_fld,
(DECODE(fld.multivalINK_NAME, NULL, NULL, (
CASE
WHEN (EXISTS
(SELECT 1
FROM siebel.s_multivalink multival,
siebel.s_link lnk
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
AND (multival.PRIMEID_FLD_NAME IS NOT NULL
OR lnk.PR_ID_FLD_NAME IS NOT NULL)
))
THEN (NULL)
ELSE ('Y')
END))) mvg_no_primary_flg,
(DECODE(fld.multivalINK_NAME, NULL, NULL,
CASE
WHEN (EXISTS
(SELECT lnk.INTER_TBL_NAME
FROM siebel.s_multivalink multival,
siebel.s_link lnk
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND lnk.INTER_TBL_NAME IS NOT NULL
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
))
THEN ('Y')
ELSE (NULL)
END)) mvg_mm_flg,
(DECODE(fld.multivalINK_NAME, NULL, NULL,
CASE
WHEN (EXISTS
(SELECT 1
FROM siebel.s_multivalink multival,
siebel.s_link lnk
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND lnk.IPARENT_COL_NAME IS NOT NULL
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
))
THEN ('ROW_ID')
ELSE (
CASE
WHEN (EXISTS
(SELECT 1
FROM siebel.s_multivalink multival,
siebel.s_link lnk,
siebel.s_buscomp buscomp2,
siebel.s_field fld2
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.CHILD_BC_NAME = buscomp2.NAME
AND fld2.BUSCOMP_ID = buscomp2.ROW_ID
AND fld2.NAME = lnk.SRC_FLD_NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND buscomp2.REPOSITORY_ID = lnk.REPOSITORY_ID
AND fld2.REPOSITORY_ID = buscomp2.REPOSITORY_ID
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
AND buscomp2.INACTIVE_FLG = 'N'
AND fld2.INACTIVE_FLG = 'N'
))
THEN
(SELECT NVL(fld2.COL_NAME, 'ROW_ID')
FROM siebel.s_multivalink multival,
siebel.s_link lnk,
siebel.s_buscomp buscomp2,
siebel.s_field fld2
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.PARENT_BC_NAME = buscomp2.NAME
AND fld2.BUSCOMP_ID = buscomp2.ROW_ID
AND fld2.NAME = lnk.SRC_FLD_NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND buscomp2.REPOSITORY_ID = lnk.REPOSITORY_ID
AND fld2.REPOSITORY_ID = buscomp2.REPOSITORY_ID
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
AND buscomp2.INACTIVE_FLG = 'N'
AND fld2.INACTIVE_FLG = 'N'
)
ELSE
(SELECT DECODE(lnk.SRC_FLD_NAME, 'Id', 'ROW_ID', 'Created', 'CREATED', 'Created By', 'CREATED_BY', 'Updated', 'LAST_UPD', 'Updated By', 'LAST_UPD_BY', 'Mod Id', 'MODIFICATION_NUM', 'Conflict Id', 'CONFLICT_ID', NULL, 'ROW_ID')
FROM siebel.s_multivalink multival,
siebel.s_link lnk,
siebel.s_buscomp buscomp2
WHERE multival.DEST_LINK_NAME = lnk.NAME
AND lnk.PARENT_BC_NAME = buscomp2.NAME
AND lnk.REPOSITORY_ID = multival.REPOSITORY_ID
AND buscomp2.REPOSITORY_ID = lnk.REPOSITORY_ID
AND multival.BUSCOMP_ID = fld.BUSCOMP_ID
AND multival.NAME = fld.multivalINK_NAME
AND multival.INACTIVE_FLG = 'N'
AND multival.REPOSITORY_ID = fld.REPOSITORY_ID
AND lnk.INACTIVE_FLG = 'N'
AND buscomp2.INACTIVE_FLG = 'N'
)
END)
END)) mvg_src_column,
wti.NAME web_template_item_name,
t.NAME tbl_name,
lclint.DISPLAY_NAME disp_name_override,
refexact.STRING_VALUE disp_name_ref_exact,
NVL(lclint.DISPLAY_NAME, refexact.STRING_VALUE) caption,
bcu.VALUE bc_inner_join_table,
fld.JOIN_NAME,
NVL(bcu.INACTIVE_FLG, 'N') bc_inner_join_inactive,
ROW_NUMBER() Over (Partition BY bc.NAME
|| fld.NAME Order By NULL) rid,
a.LAST_UPD
FROM siebel.s_applet a,
siebel.s_buscomp bc,
siebel.s_field fld,
siebel.s_appl_wtmpl_it wti,
siebel.s_appl_web_tmpl wt,
siebel.s_table t,
siebel.s_list lst,
siebel.s_list_column lcl,
siebel.s_list_col_intl lclint,
siebel.s_sym_str_intl refexact,
siebel.s_buscomp_uprop bcu
WHERE a.BUSCOMP_NAME = bc.NAME
AND bc.ROW_ID = fld.BUSCOMP_ID
AND bcu.BUSCOMP_ID(+) = bc.ROW_ID
AND lcl.FIELD_NAME = fld.NAME
AND wti.APPL_WEB_TMPL_ID = wt.ROW_ID
AND wt.APPLET_ID = a.ROW_ID
AND lst.APPLET_ID = a.ROW_ID
AND lcl.LIST_ID = lst.ROW_ID
AND lclint.LIST_COLUMN_ID(+) = lcl.ROW_ID
AND refexact.SYM_STR_KEY(+) = lcl.DISPLAY_NAME_REF
AND wti.CTRL_NAME = lcl.NAME
AND bc.TABLE_NAME = t.NAME
AND a.REPOSITORY_ID = fld.REPOSITORY_ID
AND bc.REPOSITORY_ID = a.REPOSITORY_ID
AND wt.REPOSITORY_ID = a.REPOSITORY_ID
AND wti.REPOSITORY_ID = wt.REPOSITORY_ID
AND t.REPOSITORY_ID = fld.REPOSITORY_ID
AND lst.REPOSITORY_ID = a.REPOSITORY_ID
AND lcl.REPOSITORY_ID = lst.REPOSITORY_ID
AND lclint.REPOSITORY_ID(+) = lcl.REPOSITORY_ID
AND refexact.REPOSITORY_ID(+) = lcl.REPOSITORY_ID
AND bcu.REPOSITORY_ID(+) = bc.REPOSITORY_ID
AND a.NAME LIKE :pi_vc_appletname
|| '%'
AND fld.REPOSITORY_ID IN
(SELECT SIEBEL.S_REPOSITORY.ROW_ID
FROM SIEBEL.S_REPOSITORY
WHERE SIEBEL.S_REPOSITORY.NAME = 'Siebel Repository'
)
AND a.INACTIVE_FLG = 'N'
AND bc.INACTIVE_FLG = 'N'
AND fld.INACTIVE_FLG = 'N'
AND wt.INACTIVE_FLG = 'N'
AND wti.INACTIVE_FLG = 'N'
AND t.INACTIVE_FLG = 'N'
AND lst.INACTIVE_FLG = 'N'
AND lcl.INACTIVE_FLG = 'N'
AND lclint.INACTIVE_FLG(+) = 'N'
AND refexact.INACTIVE_FLG(+) = 'N'
AND bcu.NAME(+) = 'Inner Join Extension Table 1'
AND wti.REPOS_TYPE = 'List Item'
)
SELECT ITEMS.control_name,
ITEMS.applet_name,
ITEMS.BUSCOMP_ID,
ITEMS.buscomp_name,
ITEMS.field_name,
ITEMS.field_type,
ITEMS.caption,
ITEMS.tbl_name,
ITEMS.field_col_name,
ITEMS.join_table_name,
ITEMS.join_src_column,
ITEMS.mvg_src_column,
ITEMS.mvg_dest_table,
ITEMS.mvg_dest_column,
ITEMS.mvg_primary_fld,
ITEMS.mvg_no_primary_flg,
ITEMS.mvg_mm_flg,
ITEMS.field_join_name,
ITEMS.bc_inner_join_table,
ITEMS.JOIN_NAME,
ITEMS.bc_inner_join_inactive,
ITEMS.field_multivalink_name,
(SELECT f.COL_NAME FROM siebel.s_field f WHERE f.NAME = ITEMS.mvg_primary_fld AND f.BUSCOMP_ID = ITEMS.BUSCOMP_ID) AS field_multivalink_column,
ITEMS.field_picklist_name,
ITEMS.web_template_item_name,
ITEMS.disp_name_override,
ITEMS.disp_name_ref_exact,
ITEMS.rid,
ITEMS.LAST_UPD
FROM ITEMS;