PACKAGE BODY: APPS.MSC_X_SECURITY

Source

1 package body msc_x_security as
2 /*$Header: MSCXSECB.pls 115.4 2002/10/04 21:39:54 agoel ship $ */
3 
4    procedure set_context is
5 
6       v_company_id        NUMBER;
7       v_company_name      varchar2(2000);
8 
9    begin
10 
11       -- Reset context values to null
12 
13       dbms_session.set_context('MSC', 'COMPANY_ID', null);
14       dbms_session.set_context('MSC', 'COMPANY_NAME', null);
15 
16       -- get the company id
17 
18       begin
19 
20         select c.company_name, c.company_id
21         into v_company_name, v_company_id
22         from
23           MSC_COMPANY_USERS uc,
24           msc_companies c
25         where
26           uc.user_id = FND_GLOBAL.USER_ID
27           and c.company_id = uc.company_id;
28 
29       exception
30 
31         when others then
32 
33           v_company_id := -666;
34           v_company_name := '###UNDEFINED####';
35 
36       end;
37 
38       -- set the context values
39 
40       dbms_session.set_context('MSC', 'COMPANY_ID', to_char(v_company_id));
41       dbms_session.set_context('MSC', 'COMPANY_NAME', v_company_name);
42 
43 
44    end set_context;
45 
46   /**
47     The performace of this function really sucks....
48   */
49 
50   function get_security_access(p_transaction_id in number) return varchar2 is
51 
52     v_privilege varchar2(30) := null;
53 
54   begin
55 
56     for rec in (
57       select
58          rule.privilege
59       from
60          msc_sup_dem_entries supdem,
61          MSC_X_SECURITY_RULES rule
62       where
63          sysdate between nvl(rule.EFFECTIVE_FROM_DATE, sysdate-1) and nvl(rule.EFFECTIVE_TO_DATE, sysdate +1)
64          and nvl(rule.company_id, supdem.PUBLISHER_ID) = supdem.PUBLISHER_ID
65          and nvl(rule.order_type, supdem.publisher_order_type) = supdem.publisher_order_type
66          and nvl(rule.item_id, supdem.inventory_item_id) = supdem.inventory_item_id
67          and nvl(rule.customer_id, nvl(supdem.customer_id, -1)) = nvl(supdem.customer_id, -1)
68          and nvl(rule.supplier_id, nvl(supdem.supplier_id, -1)) = nvl(supdem.supplier_id, -1)
69          and nvl(rule.customer_site_id, nvl(supdem.customer_site_id, -1)) = nvl(supdem.customer_site_id, -1)
70          and nvl(rule.supplier_site_id, nvl(supdem.supplier_site_id, -1)) = nvl(supdem.supplier_site_id, -1)
71          and nvl(rule.org_id, supdem.PUBLISHER_SITE_ID) = supdem.PUBLISHER_SITE_ID
72          and nvl(rule.order_number, nvl(supdem.order_number, -1)) = nvl(supdem.order_number, -1)
73          and (rule.grantee_key = decode(upper(rule.grantee_type), 'USER', FND_GLOBAL.USER_ID, 'COMPANY', sys_context('MSC', 'COMPANY_ID'))
74                 or upper(rule.grantee_type) = 'DOCUMENT OWNER' and supdem.publisher_id = sys_context('MSC', 'COMPANY_ID')
75                 or upper(rule.grantee_type) = 'TRADING PARTNER' and nvl(supdem.customer_id, supdem.supplier_id) = sys_context('MSC', 'COMPANY_ID')
76                 or decode(upper(rule.grantee_type),'RESPONSIBILITY', rule.grantee_key) = fnd_global.resp_id
77                 or (upper(rule.grantee_key) = 'GLOBAL')
78               )
79          and supdem.transaction_id = transaction_id
80       ) loop
81 
82         if (v_privilege is null or v_privilege = 'VIEW') then
83           v_privilege := upper(rec.privilege);
84         end if;
85 
86       end loop;
87 
88       return v_privilege;
89 
90   end get_security_access;
91 
92 end msc_x_security;