SELECT distinct
2 level_id,
pl.meaning Lev_show,
pl.meaning Lev,
' ' ou,
pv_site.profile_option_value site,
' ' Application,
' ' Resposibility,
' ' Us
,po.profile_option_name po_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,pv_site.profile_option_value) CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_site
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_site.application_id =222
AND pv_site.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_SHOW_BILLING_NUMBER'
AND pv_site.level_id=10001
AND pv_site.level_value_application_id is null
AND pv_site.level_value=r.application_id - r.application_id
AND pv.rowid=pv_site.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT
distinct 3 level_id,
pl.meaning Lev_show,
pl.meaning,
' ' ,
' ' ,
pv_app.profile_option_value Application,
' ' ,
' '
,po.profile_option_name
,fpo_tl.user_profile_option_name
,AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,pv_app.profile_option_value) CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_app
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_app.application_id=222
AND pv_app.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_SHOW_BILLING_NUMBER'
AND pv_app.level_id=10002
AND pv_app.level_value_application_id is null
AND pv_app.level_value=r.application_id
AND pv.rowid=pv_app.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
Union all
SELECT 5,
pl.meaning Lev_show,
r.responsibility_name lev,
HR.name operating_unit_name ,
' ' ,
' ' ,
pv.profile_option_value,
' ',
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,pv.profile_option_value) CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl ou_r
,fnd_profile_option_values ou_pv_site
,fnd_profile_option_values ou_pv_app
,fnd_profile_option_values ou_pv_resp
,fnd_profile_option_values ou_pv
,HR_OPERATING_UNITS HR
,fnd_responsibility_vl r
,fnd_profile_option_values pv_resp
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE ou_r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND HR.ORGANIZATION_ID=ou_pv.profile_option_value
AND ou_pv_site.application_id (+)=0
AND ou_pv_resp.profile_option_id(+)=:L_PO_ID_V
and ou_pv_app.profile_option_id(+)=:L_PO_ID_V
and ou_pv_site.profile_option_id (+) =:L_PO_ID_V
AND ou_pv_site.level_id (+)=10001
AND ou_pv_site.level_value_application_id is null
AND ou_pv_site.level_value (+)=ou_r.application_id - ou_r.application_id
AND ou_pv_app.application_id (+)=0
AND ou_pv_app.level_id (+)=10002
AND ou_pv_app.level_value_application_id is null
AND ou_pv_app.level_value (+)=ou_r.application_id
AND ou_pv_resp.application_id (+)=0
AND ou_pv_resp.level_id (+)=10003
AND ou_pv_resp.level_value_application_id (+)=ou_r.application_id
AND ou_pv_resp.level_value (+)=ou_r.responsibility_id
AND ou_pv.rowid=nvl(ou_pv_resp.rowid,nvl(ou_pv_app.rowid,ou_pv_site.rowid))
AND ou_r.start_date <= sysdate
AND nvl(ou_r.end_date,sysdate) >= sysdate
AND r.responsibility_id=ou_r.responsibility_id
AND r.application_id=222
AND pv_resp.application_id (+)=222
AND pv_resp.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_SHOW_BILLING_NUMBER'
AND pv_resp.level_id (+)=10003
AND pv_resp.level_value_application_id (+)=r.application_id
AND pv_resp.level_value (+)=r.responsibility_id
AND pv.rowid=pv_resp.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT distinct 7,
pl.meaning Lev_show,
fo.user_name lev,
' ' ,
' ' ,
' ' ,
' ',
pv.profile_option_value,
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,pv.profile_option_value) CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_users
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_user fo
,fnd_user_resp_groups fg
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
and fg.responsibility_application_id =222
and fg.responsibility_id=r.responsibility_id
and fg.user_id=fo.user_id
AND pv_users.application_id (+)=222
AND pv_users.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_SHOW_BILLING_NUMBER'
AND pv_users.level_id=10004
-- AND pv_users.level_value_application_id (+)=r.application_id
AND pv_users.level_value (+)=fo.user_id
AND pv.rowid=pv_users.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT distinct
2 level_id,
pl.meaning Lev_show,
pl.meaning Lev,
' ' ou,
pv_site.profile_option_value site,
' ' Application,
' ' Resposibility,
' ' Us
,po.profile_option_name po_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,pv_site.profile_option_value) CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_site
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_site.application_id =222
AND pv_site.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_CROSS_CURRENCY_RATE_TYPE'
AND pv_site.level_id=10001
AND pv_site.level_value_application_id is null
AND pv_site.level_value=r.application_id - r.application_id
AND pv.rowid=pv_site.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT
distinct 3,
pl.meaning Lev_show,
pl.meaning,
' ' ,
' ' ,
pv_app.profile_option_value Application,
' ' ,
' '
,po.profile_option_name
,fpo_tl.user_profile_option_name
,AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,pv_app.profile_option_value) CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_app
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_app.application_id=222
AND pv_app.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_CROSS_CURRENCY_RATE_TYPE'
AND pv_app.level_id=10002
AND pv_app.level_value_application_id is null
AND pv_app.level_value=r.application_id
AND pv.rowid=pv_app.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
Union all
SELECT 5,
pl.meaning Lev_show,
r.responsibility_name lev,
HR.name operating_unit_name ,
' ' ,
' ' ,
pv.profile_option_value,
' ',
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,pv.profile_option_value) CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl ou_r
,fnd_profile_option_values ou_pv_site
,fnd_profile_option_values ou_pv_app
,fnd_profile_option_values ou_pv_resp
,fnd_profile_option_values ou_pv
,HR_OPERATING_UNITS HR
,fnd_responsibility_vl r
,fnd_profile_option_values pv_resp
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE ou_r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND HR.ORGANIZATION_ID=ou_pv.profile_option_value
AND ou_pv_site.application_id (+)=0
AND ou_pv_resp.profile_option_id(+)=:L_PO_ID_V
and ou_pv_app.profile_option_id(+)=:L_PO_ID_V
and ou_pv_site.profile_option_id (+) =:L_PO_ID_V
AND ou_pv_site.level_id (+)=10001
AND ou_pv_site.level_value_application_id is null
AND ou_pv_site.level_value (+)=ou_r.application_id - ou_r.application_id
AND ou_pv_app.application_id (+)=0
AND ou_pv_app.level_id (+)=10002
AND ou_pv_app.level_value_application_id is null
AND ou_pv_app.level_value (+)=ou_r.application_id
AND ou_pv_resp.application_id (+)=0
AND ou_pv_resp.level_id (+)=10003
AND ou_pv_resp.level_value_application_id (+)=ou_r.application_id
AND ou_pv_resp.level_value (+)=ou_r.responsibility_id
AND ou_pv.rowid=nvl(ou_pv_resp.rowid,nvl(ou_pv_app.rowid,ou_pv_site.rowid))
AND ou_r.start_date <= sysdate
AND nvl(ou_r.end_date,sysdate) >= sysdate
AND r.responsibility_id=ou_r.responsibility_id
AND r.application_id=222
AND pv_resp.application_id (+)=222
AND pv_resp.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_CROSS_CURRENCY_RATE_TYPE'
AND pv_resp.level_id (+)=10003
AND pv_resp.level_value_application_id (+)=r.application_id
AND pv_resp.level_value (+)=r.responsibility_id
AND pv.rowid=pv_resp.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT distinct 7,
pl.meaning Lev_show,
fo.user_name lev,
' ' ,
' ' ,
' ' ,
' ',
pv.profile_option_value,
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,pv.profile_option_value) CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_users
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_user fo
,fnd_user_resp_groups fg
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
and fg.responsibility_application_id =222
and fg.responsibility_id=r.responsibility_id
and fg.user_id=fo.user_id
AND pv_users.application_id (+)=222
AND pv_users.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_CROSS_CURRENCY_RATE_TYPE'
AND pv_users.level_id=10004
-- AND pv_users.level_value_application_id (+)=r.application_id
AND pv_users.level_value (+)=fo.user_id
AND pv.rowid=pv_users.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT distinct
2 level_id,
pl.meaning Lev_show,
pl.meaning Lev,
' ' ou,
pv_site.profile_option_value site,
' ' Application,
' ' Resposibility,
' ' Us
,po.profile_option_name po_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,pv_site.profile_option_value) CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_site
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_site.application_id =222
AND pv_site.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_DOC_SEQ_GEN_LEVEL'
AND pv_site.level_id=10001
AND pv_site.level_value_application_id is null
AND pv_site.level_value=r.application_id - r.application_id
AND pv.rowid=pv_site.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT
distinct 3,
pl.meaning Lev_show,
pl.meaning,
' ' ,
' ' ,
pv_app.profile_option_value Application,
' ' ,
' '
,po.profile_option_name
,fpo_tl.user_profile_option_name
,AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,pv_app.profile_option_value) CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_app
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_app.application_id=222
AND pv_app.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_DOC_SEQ_GEN_LEVEL'
AND pv_app.level_id=10002
AND pv_app.level_value_application_id is null
AND pv_app.level_value=r.application_id
AND pv.rowid=pv_app.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
Union all
SELECT 5,
pl.meaning Lev_show,
r.responsibility_name lev,
HR.name operating_unit_name ,
' ' ,
' ' ,
pv.profile_option_value,
' ',
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,pv.profile_option_value) CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl ou_r
,fnd_profile_option_values ou_pv_site
,fnd_profile_option_values ou_pv_app
,fnd_profile_option_values ou_pv_resp
,fnd_profile_option_values ou_pv
,HR_OPERATING_UNITS HR
,fnd_responsibility_vl r
,fnd_profile_option_values pv_resp
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE ou_r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND HR.ORGANIZATION_ID=ou_pv.profile_option_value
AND ou_pv_site.application_id (+)=0
AND ou_pv_resp.profile_option_id(+)=:L_PO_ID_V
and ou_pv_app.profile_option_id(+)=:L_PO_ID_V
and ou_pv_site.profile_option_id (+) =:L_PO_ID_V
AND ou_pv_site.level_id (+)=10001
AND ou_pv_site.level_value_application_id is null
AND ou_pv_site.level_value (+)=ou_r.application_id - ou_r.application_id
AND ou_pv_app.application_id (+)=0
AND ou_pv_app.level_id (+)=10002
AND ou_pv_app.level_value_application_id is null
AND ou_pv_app.level_value (+)=ou_r.application_id
AND ou_pv_resp.application_id (+)=0
AND ou_pv_resp.level_id (+)=10003
AND ou_pv_resp.level_value_application_id (+)=ou_r.application_id
AND ou_pv_resp.level_value (+)=ou_r.responsibility_id
AND ou_pv.rowid=nvl(ou_pv_resp.rowid,nvl(ou_pv_app.rowid,ou_pv_site.rowid))
AND ou_r.start_date <= sysdate
AND nvl(ou_r.end_date,sysdate) >= sysdate
AND r.responsibility_id=ou_r.responsibility_id
AND r.application_id=222
AND pv_resp.application_id (+)=222
AND pv_resp.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_DOC_SEQ_GEN_LEVEL'
AND pv_resp.level_id (+)=10003
AND pv_resp.level_value_application_id (+)=r.application_id
AND pv_resp.level_value (+)=r.responsibility_id
AND pv.rowid=pv_resp.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT distinct 7,
pl.meaning Lev_show,
fo.user_name lev,
' ' ,
' ' ,
' ' ,
' ',
pv.profile_option_value,
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,pv.profile_option_value) CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_users
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_user fo
,fnd_user_resp_groups fg
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
and fg.responsibility_application_id =222
and fg.responsibility_id=r.responsibility_id
and fg.user_id=fo.user_id
AND pv_users.application_id (+)=222
AND pv_users.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_DOC_SEQ_GEN_LEVEL'
AND pv_users.level_id=10004
-- AND pv_users.level_value_application_id (+)=r.application_id
AND pv_users.level_value (+)=fo.user_id
AND pv.rowid=pv_users.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT distinct
2 level_id,
pl.meaning Lev_show,
pl.meaning Lev,
' ' ou,
pv_site.profile_option_value site,
' ' Application,
' ' Resposibility,
' ' Us
,po.profile_option_name po_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,pv_site.profile_option_value) CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_site
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_site.application_id =222
AND pv_site.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_ZENGIN_CHAR_SET'
AND pv_site.level_id=10001
AND pv_site.level_value_application_id is null
AND pv_site.level_value=r.application_id - r.application_id
AND pv.rowid=pv_site.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT
distinct 3,
pl.meaning Lev_show,
pl.meaning,
' ' ,
' ' ,
pv_app.profile_option_value Application,
' ' ,
' '
,po.profile_option_name
,fpo_tl.user_profile_option_name
,AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,pv_app.profile_option_value) CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_app
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_app.application_id=222
AND pv_app.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_ZENGIN_CHAR_SET'
AND pv_app.level_id=10002
AND pv_app.level_value_application_id is null
AND pv_app.level_value=r.application_id
AND pv.rowid=pv_app.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
Union all
SELECT 5,
pl.meaning Lev_show,
r.responsibility_name lev,
HR.name operating_unit_name ,
' ' ,
' ' ,
pv.profile_option_value,
' ',
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,pv.profile_option_value) CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl ou_r
,fnd_profile_option_values ou_pv_site
,fnd_profile_option_values ou_pv_app
,fnd_profile_option_values ou_pv_resp
,fnd_profile_option_values ou_pv
,HR_OPERATING_UNITS HR
,fnd_responsibility_vl r
,fnd_profile_option_values pv_resp
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE ou_r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND HR.ORGANIZATION_ID=ou_pv.profile_option_value
AND ou_pv_site.application_id (+)=0
AND ou_pv_resp.profile_option_id(+)=:L_PO_ID_V
and ou_pv_app.profile_option_id(+)=:L_PO_ID_V
and ou_pv_site.profile_option_id (+) =:L_PO_ID_V
AND ou_pv_site.level_id (+)=10001
AND ou_pv_site.level_value_application_id is null
AND ou_pv_site.level_value (+)=ou_r.application_id - ou_r.application_id
AND ou_pv_app.application_id (+)=0
AND ou_pv_app.level_id (+)=10002
AND ou_pv_app.level_value_application_id is null
AND ou_pv_app.level_value (+)=ou_r.application_id
AND ou_pv_resp.application_id (+)=0
AND ou_pv_resp.level_id (+)=10003
AND ou_pv_resp.level_value_application_id (+)=ou_r.application_id
AND ou_pv_resp.level_value (+)=ou_r.responsibility_id
AND ou_pv.rowid=nvl(ou_pv_resp.rowid,nvl(ou_pv_app.rowid,ou_pv_site.rowid))
AND ou_r.start_date <= sysdate
AND nvl(ou_r.end_date,sysdate) >= sysdate
AND r.responsibility_id=ou_r.responsibility_id
AND r.application_id=222
AND pv_resp.application_id (+)=222
AND pv_resp.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_ZENGIN_CHAR_SET'
AND pv_resp.level_id (+)=10003
AND pv_resp.level_value_application_id (+)=r.application_id
AND pv_resp.level_value (+)=r.responsibility_id
AND pv.rowid=pv_resp.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT distinct 7,
pl.meaning Lev_show,
fo.user_name lev,
' ' ,
' ' ,
' ' ,
' ',
pv.profile_option_value,
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,pv.profile_option_value) CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_users
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_user fo
,fnd_user_resp_groups fg
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
and fg.responsibility_application_id =222
and fg.responsibility_id=r.responsibility_id
and fg.user_id=fo.user_id
AND pv_users.application_id (+)=222
AND pv_users.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_ZENGIN_CHAR_SET'
AND pv_users.level_id=10004
-- AND pv_users.level_value_application_id (+)=r.application_id
AND pv_users.level_value (+)=fo.user_id
AND pv.rowid=pv_users.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT distinct
2 level_id,
pl.meaning Lev_show,
pl.meaning Lev,
' ' ou,
DECODE(pv_site.profile_option_value,'UNDEFINED',pv_site.profile_option_value,
to_char(pv_site.profile_option_value) ) site,
' ' Application,
' ' Resposibility,
' ' Us
,po.profile_option_name po_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,DECODE(pv_site.profile_option_value,'UNDEFINED',pv_site.profile_option_value,
to_char(pv_site.profile_option_value) )) CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_site
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_site.application_id =222
AND pv_site.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_PA_CODE'
AND pv_site.level_id=10001
AND pv_site.level_value_application_id is null
AND pv_site.level_value=r.application_id - r.application_id
AND pv.rowid=pv_site.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT
distinct 3,
pl.meaning Lev_show,
pl.meaning,
' ' ,
' ' ,
DECODE(pv_app.profile_option_value,'UNDEFINED',pv_app.profile_option_value,
to_char(pv_app.profile_option_value) ) Application,
' ' ,
' '
,po.profile_option_name
,fpo_tl.user_profile_option_name
,AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,DECODE(pv_app.profile_option_value,'UNDEFINED',pv_app.profile_option_value,
to_char(pv_app.profile_option_value) ) ) CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_app
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND pv_app.application_id=222
AND pv_app.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_PA_CODE'
AND pv_app.level_id=10002
AND pv_app.level_value_application_id is null
AND pv_app.level_value=r.application_id
AND pv.rowid=pv_app.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
Union all
SELECT 5,
pl.meaning Lev_show,
r.responsibility_name lev,
HR.name operating_unit_name ,
' ' ,
' ' ,
DECODE(pv.profile_option_value,'UNDEFINED',pv.profile_option_value,
to_char(pv.profile_option_value) ) ,
' ',
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,DECODE(pv.profile_option_value,'UNDEFINED',pv.profile_option_value,
to_char(pv.profile_option_value) )) CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,' ') CF_user
FROM fnd_responsibility_vl ou_r
,fnd_profile_option_values ou_pv_site
,fnd_profile_option_values ou_pv_app
,fnd_profile_option_values ou_pv_resp
,fnd_profile_option_values ou_pv
,HR_OPERATING_UNITS HR
,fnd_responsibility_vl r
,fnd_profile_option_values pv_resp
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_profile_options_tl fpo_tl
WHERE ou_r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
AND HR.ORGANIZATION_ID=ou_pv.profile_option_value
AND ou_pv_site.application_id (+)=0
AND ou_pv_resp.profile_option_id(+)=:L_PO_ID_V
and ou_pv_app.profile_option_id(+)=:L_PO_ID_V
and ou_pv_site.profile_option_id (+) =:L_PO_ID_V
AND ou_pv_site.level_id (+)=10001
AND ou_pv_site.level_value_application_id is null
AND ou_pv_site.level_value (+)=ou_r.application_id - ou_r.application_id
AND ou_pv_app.application_id (+)=0
AND ou_pv_app.level_id (+)=10002
AND ou_pv_app.level_value_application_id is null
AND ou_pv_app.level_value (+)=ou_r.application_id
AND ou_pv_resp.application_id (+)=0
AND ou_pv_resp.level_id (+)=10003
AND ou_pv_resp.level_value_application_id (+)=ou_r.application_id
AND ou_pv_resp.level_value (+)=ou_r.responsibility_id
AND ou_pv.rowid=nvl(ou_pv_resp.rowid,nvl(ou_pv_app.rowid,ou_pv_site.rowid))
AND ou_r.start_date <= sysdate
AND nvl(ou_r.end_date,sysdate) >= sysdate
AND r.responsibility_id=ou_r.responsibility_id
AND r.application_id=222
AND pv_resp.application_id (+)=222
AND pv_resp.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_PA_CODE'
AND pv_resp.level_id (+)=10003
AND pv_resp.level_value_application_id (+)=r.application_id
AND pv_resp.level_value (+)=r.responsibility_id
AND pv.rowid=pv_resp.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
union all
SELECT distinct 7,
pl.meaning Lev_show,
fo.user_name lev,
' ' ,
' ' ,
' ' ,
' ',
DECODE(pv.profile_option_value,'UNDEFINED',pv.profile_option_value,
to_char(pv.profile_option_value) ),
po.profile_option_name
,fpo_tl.user_profile_option_name,
AR_ARXPOMR_XMLP_PKG.cf_1formula(po.profile_option_name,' ') CF_resp,
AR_ARXPOMR_XMLP_PKG.cf_appformula(po.profile_option_name,' ') CF_app,
AR_ARXPOMR_XMLP_PKG.cf_siteformula(po.profile_option_name,' ') CF_site,
AR_ARXPOMR_XMLP_PKG.cf_userformula(po.profile_option_name,DECODE(pv.profile_option_value,'UNDEFINED',pv.profile_option_value,
to_char(pv.profile_option_value) )) CF_user
FROM fnd_responsibility_vl r
,fnd_profile_option_values pv_users
,fnd_profile_option_values pv
,fnd_lookups pl
,fnd_profile_options po
,fnd_user fo
,fnd_user_resp_groups fg
,fnd_profile_options_tl fpo_tl
WHERE r.application_id=222
AND po.profile_option_name=fpo_tl.profile_option_name
AND fpo_tl.language=USERENV('LANG')
and fg.responsibility_application_id =222
and fg.responsibility_id=r.responsibility_id
and fg.user_id=fo.user_id
AND pv_users.application_id (+)=222
AND pv_users.profile_option_id=po.profile_option_id
AND po.profile_option_name='AR_PA_CODE'
AND pv_users.level_id=10004
-- AND pv_users.level_value_application_id (+)=r.application_id
AND pv_users.level_value (+)=fo.user_id
AND pv.rowid=pv_users.rowid
AND pl.lookup_type='FND_PROFILE_LEVELS'
AND pl.lookup_code=DECODE(pv.level_id,
10001,'SITE',
10002,'APPLICATION',
10003,'RESPONSIBILITY',
10004,'USER')
AND r.start_date <= sysdate
AND nvl(r.end_date,sysdate) >= sysdate
--order by 9,1
ORDER BY 10 ASC,
9 ASC,
1 ASC,
2 ASC,
9,
1 |
