VIEW: APPS.MSC_SUP_DEM_UPDATE_SECURITY_V

Source

View Text - Preformatted

SELECT supdem.transaction_id, supdem.customer_id , supdem.customer_site_id , supdem.supplier_id , supdem.supplier_site_id , supdem.publisher_order_type, supdem.inventory_item_id from msc_sup_dem_entries supdem WHERE exists (select 1 from MSC_X_SECURITY_RULES rule where rule.PRIVILEGE = 'UPDATE' and sysdate between nvl(rule.EFFECTIVE_FROM_DATE, sysdate-1) and nvl(rule.EFFECTIVE_TO_DATE, sysdate +1) and nvl(rule.company_id, supdem.PUBLISHER_ID) = supdem.publisher_id and nvl(rule.order_type, supdem.publisher_order_type) = supdem.publisher_order_type and nvl(rule.item_id, supdem.inventory_item_id) = supdem.inventory_item_id and nvl(rule.customer_id, nvl(supdem.customer_id, -1)) = nvl(supdem.customer_id, -1) and nvl(rule.supplier_id, nvl(supdem.supplier_id, -1)) = nvl(supdem.supplier_id, -1) and nvl(rule.customer_site_id, nvl(supdem.customer_site_id, -1)) = nvl(supdem.customer_site_id, -1) and nvl(rule.supplier_site_id, nvl(supdem.supplier_site_id, -1)) = nvl(supdem.supplier_site_id, -1) and nvl(rule.org_id, supdem.PUBLISHER_SITE_ID) = supdem.PUBLISHER_SITE_ID and nvl(rule.order_number, nvl(supdem.order_number, -1)) = nvl(supdem.order_number, -1) and (rule.grantee_key = decode(upper(rule.grantee_type), 'USER', FND_GLOBAL.USER_ID, 'COMPANY', sys_context('MSC', 'COMPANY_ID')) or upper(rule.grantee_type) = 'DOCUMENT OWNER' and supdem.publisher_id = sys_context('MSC', 'COMPANY_ID') or upper(rule.grantee_type) = 'TRADING PARTNER' and supdem.customer_id = sys_context('MSC', 'COMPANY_ID') or upper(rule.grantee_type) ='TRADING PARTNER' and supdem.supplier_id = sys_context('MSC', 'COMPANY_ID') or decode(upper(rule.grantee_type),'RESPONSIBILITY', rule.grantee_key) = fnd_global.resp_id or (upper(rule.grantee_type) = 'GLOBAL') or (upper(rule.grantee_type) = 'GROUP' and rule.grantee_key in (SELECT group_id FROM msc_group_companies WHERE company_id = sys_context('MSC','COMPANY_ID') AND sysdate BETWEEN effective_date and nvl(disable_date,sysdate+1) ) ) ))

View Text - HTML Formatted

SELECT SUPDEM.TRANSACTION_ID
, SUPDEM.CUSTOMER_ID
, SUPDEM.CUSTOMER_SITE_ID
, SUPDEM.SUPPLIER_ID
, SUPDEM.SUPPLIER_SITE_ID
, SUPDEM.PUBLISHER_ORDER_TYPE
, SUPDEM.INVENTORY_ITEM_ID
FROM MSC_SUP_DEM_ENTRIES SUPDEM
WHERE EXISTS (SELECT 1
FROM MSC_X_SECURITY_RULES RULE
WHERE RULE.PRIVILEGE = 'UPDATE'
AND SYSDATE BETWEEN NVL(RULE.EFFECTIVE_FROM_DATE
, SYSDATE-1)
AND NVL(RULE.EFFECTIVE_TO_DATE
, SYSDATE +1)
AND NVL(RULE.COMPANY_ID
, SUPDEM.PUBLISHER_ID) = SUPDEM.PUBLISHER_ID
AND NVL(RULE.ORDER_TYPE
, SUPDEM.PUBLISHER_ORDER_TYPE) = SUPDEM.PUBLISHER_ORDER_TYPE
AND NVL(RULE.ITEM_ID
, SUPDEM.INVENTORY_ITEM_ID) = SUPDEM.INVENTORY_ITEM_ID
AND NVL(RULE.CUSTOMER_ID
, NVL(SUPDEM.CUSTOMER_ID
, -1)) = NVL(SUPDEM.CUSTOMER_ID
, -1)
AND NVL(RULE.SUPPLIER_ID
, NVL(SUPDEM.SUPPLIER_ID
, -1)) = NVL(SUPDEM.SUPPLIER_ID
, -1)
AND NVL(RULE.CUSTOMER_SITE_ID
, NVL(SUPDEM.CUSTOMER_SITE_ID
, -1)) = NVL(SUPDEM.CUSTOMER_SITE_ID
, -1)
AND NVL(RULE.SUPPLIER_SITE_ID
, NVL(SUPDEM.SUPPLIER_SITE_ID
, -1)) = NVL(SUPDEM.SUPPLIER_SITE_ID
, -1)
AND NVL(RULE.ORG_ID
, SUPDEM.PUBLISHER_SITE_ID) = SUPDEM.PUBLISHER_SITE_ID
AND NVL(RULE.ORDER_NUMBER
, NVL(SUPDEM.ORDER_NUMBER
, -1)) = NVL(SUPDEM.ORDER_NUMBER
, -1)
AND (RULE.GRANTEE_KEY = DECODE(UPPER(RULE.GRANTEE_TYPE)
, 'USER'
, FND_GLOBAL.USER_ID
, 'COMPANY'
, SYS_CONTEXT('MSC'
, 'COMPANY_ID')) OR UPPER(RULE.GRANTEE_TYPE) = 'DOCUMENT OWNER'
AND SUPDEM.PUBLISHER_ID = SYS_CONTEXT('MSC'
, 'COMPANY_ID') OR UPPER(RULE.GRANTEE_TYPE) = 'TRADING PARTNER'
AND SUPDEM.CUSTOMER_ID = SYS_CONTEXT('MSC'
, 'COMPANY_ID') OR UPPER(RULE.GRANTEE_TYPE) ='TRADING PARTNER'
AND SUPDEM.SUPPLIER_ID = SYS_CONTEXT('MSC'
, 'COMPANY_ID') OR DECODE(UPPER(RULE.GRANTEE_TYPE)
, 'RESPONSIBILITY'
, RULE.GRANTEE_KEY) = FND_GLOBAL.RESP_ID OR (UPPER(RULE.GRANTEE_TYPE) = 'GLOBAL') OR (UPPER(RULE.GRANTEE_TYPE) = 'GROUP'
AND RULE.GRANTEE_KEY IN (SELECT GROUP_ID
FROM MSC_GROUP_COMPANIES
WHERE COMPANY_ID = SYS_CONTEXT('MSC'
, 'COMPANY_ID')
AND SYSDATE BETWEEN EFFECTIVE_DATE
AND NVL(DISABLE_DATE
, SYSDATE+1) ) ) ))