VIEW: APPS.MSC_AVAILABLE_ORGS_V

Source

View Text - Preformatted

SELECT ltrim(rtrim(pt.organization_code)), to_char(pt.sr_tp_id) || ':' || to_char(pt.sr_instance_id), resp.responsibility_id from msc_trading_partners pt, msc_apps_instances ai, fnd_responsibility resp WHERE pt.sr_instance_id = ai.instance_id and ai.enable_flag = '1' and pt.partner_type= 3 and not exists (select 'x' from msc_org_access acc where acc.organization_id = pt.sr_tp_id and acc.sr_instance_id = pt.sr_instance_id and acc.responsibility_id= resp.responsibility_id and acc.resp_application_id = resp.application_id and sysdate >= nvl(acc.effective_from_date,sysdate) and sysdate <= nvl(acc.effective_to_date,sysdate) ) order by ltrim(rtrim(pt.organization_code))

View Text - HTML Formatted

SELECT LTRIM(RTRIM(PT.ORGANIZATION_CODE))
, TO_CHAR(PT.SR_TP_ID) || ':' || TO_CHAR(PT.SR_INSTANCE_ID)
, RESP.RESPONSIBILITY_ID
FROM MSC_TRADING_PARTNERS PT
, MSC_APPS_INSTANCES AI
, FND_RESPONSIBILITY RESP
WHERE PT.SR_INSTANCE_ID = AI.INSTANCE_ID
AND AI.ENABLE_FLAG = '1'
AND PT.PARTNER_TYPE= 3
AND NOT EXISTS (SELECT 'X'
FROM MSC_ORG_ACCESS ACC
WHERE ACC.ORGANIZATION_ID = PT.SR_TP_ID
AND ACC.SR_INSTANCE_ID = PT.SR_INSTANCE_ID
AND ACC.RESPONSIBILITY_ID= RESP.RESPONSIBILITY_ID
AND ACC.RESP_APPLICATION_ID = RESP.APPLICATION_ID
AND SYSDATE >= NVL(ACC.EFFECTIVE_FROM_DATE
, SYSDATE)
AND SYSDATE <= NVL(ACC.EFFECTIVE_TO_DATE
, SYSDATE) ) ORDER BY LTRIM(RTRIM(PT.ORGANIZATION_CODE))