1 package body PA_SIGNON as
2 /* $Header: PASIGNOB.pls 120.5.12010000.3 2009/06/24 19:16:54 cklee ship $ */
3
4 PROCEDURE INIT
5 IS
6 program_name VARCHAR2(2000); /*bug 7277303*/
7 l_module v$session.MODULE%TYPE;
8 l_session_id NUMBER;
9 l_pji_installed VARCHAR2(1);
10
11 -- 5652711 Added below local variables
12 l_application_short_name VARCHAR2(80);
13 l_init_function_name VARCHAR2(240);
14
15 BEGIN
16
17 -- 5652711 Commented below code , refer bug for more detail
18
19 /*
20 SELECT USERENV('SESSIONID')
21 INTO l_session_id
22 FROM dual;
23
24 --------------------------------------------------------------------------------
25 --Session ID will be 0 if we schedule to run the workbooks
26 --When the package is run from the DBMS_JOBS queue the userenv('SESSIONID') is
27 --set to 0
28 --------------------------------------------------------------------------------
29 IF l_session_id = 0 THEN
30
31 BEGIN
32 SELECT audsid INTO l_session_id
33 FROM v$session
34 WHERE audsid = 0
35 AND MODULE = 'Discoverer4';
36 EXCEPTION
37 WHEN NO_DATA_FOUND THEN -- not Discoverer 4i
38 RETURN;
39 END;
40
41 ELSE
42
43 --------------------------------------------------------
44 -- Find program name for the current session
45 -- Note, we only grab the text starting from the last '\'
46 ---------------------------------------------------------
47 SELECT NVL(LOWER(SUBSTR(program,INSTRB(program,'\',-1,1)+1)), 'xxx')
48 ,MODULE
49 INTO program_name
50 ,l_module
51 FROM v$session
52 WHERE audsid = USERENV('SESSIONID');
53
54 -----------------------------------------------------------------------
55 -- Check if called from Discoverer session, if not, just exit
56 -- Assumptions: 1 The icon name and/or executable
57 -- name for Discoverer User Edition startes with
58 -- the characters "dis" (not case sensitive)
59 -- 2 The total path of the icon does not exceed 48 char
60 --
61 -- Notes: 1 When running Disco from Win95, the PROGRAM field
62 -- in V$SESSION is populated with executable name without path.
63 -- In NT, path is included. See bug 1472179
64 -- 2 Is not a complete solution as string matching is not
65 -- 100% reliable. Long term, requesting Disco 4i to
66 -- populate the module field so we can definitively identify
67 -- a Disco session. Complete solution in 4i.
68
69 -- IF program name is like '%dis%' OR
70 -- module name is 'Discoverer4' THEN
71 -- we need to build the temp table
72 -- Otherwise, just exit.
73 -------------------------------------------------------------------------
74 IF ( INSTRB(program_name,'dis',-1,1) = 0 ) AND
75 ( NVL(l_module,'XXX') <> 'Discoverer4' ) THEN
76
77 RETURN;
78 END IF;
79
80 -- Check if the program name is like '%f60run%'. If so, it is forms
81 -- runtime. Just exit.
82 IF (INSTRB(program_name,'f60run',1,1) > 0) THEN
83 RETURN;
84 END IF;
85
86 -- Check if the program name is like '%rwrun60%'. If so, it is reports
87 -- runtime. Just exit.
88 IF (INSTRB(program_name,'rwrun60',1,1) > 0) THEN
89 RETURN;
90 END IF;
91
92 -- Check if the program name is like '%standard@%'. If so, it is standard
93 -- manager within concurrent manager. Just exit.
94 IF (INSTRB(program_name,'standard@',1,1) > 0) THEN
95 RETURN;
96 END IF;
97
98 END IF;--end IF l_session_id = 0
99 */
100
101 -- Added below code to do MO Initialization
102
103 l_application_short_name := fnd_global.APPLICATION_SHORT_NAME;
104
105 SELECT UPPER(init_function_name) INTO l_init_function_name
106 FROM fnd_product_initialization
107 WHERE application_short_name = UPPER(l_application_short_name);
108
109 IF ('PA_SIGNON.INIT' = l_init_function_name) THEN
110 mo_global.init(l_application_short_name);
111 END IF;
112
113 -- 5652711 end
114
115 ----------------------------------------------
116 --Populating secured list of projects and
117 --project organizations
118 --There are 2 temporary tables that are
119 --populated based on the following logic
120 -- - Projects for which logged in user is a
121 -- project manager
122 -- - Organizations on which one has project
123 -- authority
124 -- - Security 11.5.10 uptake for logged in user
125 -----------------------------------------------
126 BEGIN
127 --Inserting secured Organizations
128 INSERT INTO pa_rep_sec_porgz_tmp
129 (
130 ORGANIZATION_ID
131 )
132 SELECT INSTANCE_PK1_VALUE
133 FROM fnd_grants fg,
134 fnd_objects fob,
135 (SELECT nvl(pa_security_pvt.get_menu_id('PA_PRM_PROJ_AUTH'),-1) menu_id
136 ,nvl(PA_SECURITY_PVT.GET_GRANTEE_KEY, -1) grantee_key
137 FROM dual) prj_auth_menu
138 WHERE fg.INSTANCE_TYPE = 'INSTANCE'
139 AND fg.GRANTEE_TYPE = 'USER'
140 AND fg.OBJECT_ID = fob.OBJECT_ID
141 AND fob.OBJ_NAME = 'ORGANIZATION'
142 AND fg.MENU_ID = prj_auth_menu.MENU_ID
143 AND fg.GRANTEE_KEY = prj_auth_menu.GRANTEE_KEY -- in replacement of the following string of code
144 -- AND fg.GRANTEE_KEY = 'PER:' || fu.EMPLOYEE_ID
145 AND trunc(SYSDATE) BETWEEN trunc(fg.START_DATE)
146 AND trunc(NVL(fg.END_DATE, SYSDATE+1));
147
148 INSERT INTO pa_rep_sec_proj_tmp
149 (
150 PROJECT_ID
151 )
152 SELECT ppp.PROJECT_ID
153 FROM pa_project_parties ppp,
154 fnd_user fu
155 -- fnd_grants fg /*bug#4904076 Perf bug*/
156 WHERE fu.USER_ID = fnd_global.user_id AND
157 fu.EMPLOYEE_ID = ppp.RESOURCE_SOURCE_ID AND
158 --start rem | 24-JUN-2009 cklee fxied bug:6708625 |
159 object_type = 'PA_PROJECTS' --(since this is reading project records)
160 and resource_type_id = 101 --(since this join is grabbing project
161 --end rem | 24-JUN-2009 cklee fxied bug:6708625 |
162 and ppp.PROJECT_ROLE_ID = 1;
163 EXCEPTION
164 WHEN OTHERS THEN
165 null;
166 END;
167
168 -----------------------------------------------
169 --If PJI is installed then populate security
170 --tables for OU and Organizations
171 --Also, we assume that the signon occured
172 --from Discoverer login if code reaches this
173 --point
174 -----------------------------------------------
175 IF PA_INSTALL.Is_PJI_Installed = 'Y' THEN
176
177 --dynamic calls to pji api's
178 execute immediate 'begin PJI_PMV_ENGINE.Convert_Organization; end;';
179 execute immediate 'begin PJI_PMV_ENGINE.Convert_Operating_Unit(p_view_by => ''OU''); end;';
180
181 END IF;
182
183 END INIT;
184
185 end PA_SIGNON;