IGI Subledger Security: User Allocation Status - draft

SELECT  g.sls_group,
               g.sls_group || ' - '|| g.description sls_name_desc,
	IGI_IGISLSUA_XMLP_PKG.cf_1formula(g.sls_group) CF_1
FROM     igi_sls_groups g
WHERE   g.sls_group_type = 'S'
AND        g.sls_group like :P_GROUP_NAME_T
AND       g.sls_group != 'CEN'
AND        NVL(:P_USER_NAME_T, '%') = '%'
UNION
SELECT  g.sls_group,
         g.sls_group || ' - '|| g.description sls_name_desc,
	IGI_IGISLSUA_XMLP_PKG.cf_1formula(g.sls_group) CF_1
FROM     igi_sls_groups g,
	fnd_profile_options            pro,
	fnd_profile_option_values prv,
	fnd_responsibility              res,
	fnd_user_resp_groups_direct urg,
	fnd_user             usr
WHERE   g.sls_group_type = 'S'
AND     g.sls_group != 'CEN'
AND     g.sls_group like :P_GROUP_NAME_T
AND     pro.profile_option_name = 'IGI_SLS_SECURITY_GROUP'
AND     prv.profile_option_value = g.sls_group
AND     pro.profile_option_id       = prv.profile_option_id
AND     prv.level_value        = res.responsibility_id
AND     urg.user_id = usr.user_id
AND     urg.responsibility_id = res.responsibility_id
AND     NVL(:P_USER_NAME_T, '%') != '%'
AND     usr.user_name = :P_USER_NAME_T
AND     NVL(usr.end_date,sysdate) >= SYSDATE
ORDER BY sls_group