[Home] [Help]
PACKAGE BODY: APPS.PJM_INSTALL
Source
1 package body PJM_INSTALL as
2 /* $Header: PJMINSTB.pls 120.0.12010000.2 2009/10/20 22:06:14 huiwan ship $ */
3
4 ---------------------------------------------
5 -- Global variables
6 ---------------------------------------------
7 G_Installed BOOLEAN := NULL;
8 G_Implemented BOOLEAN := NULL;
9 G_Implemented_Org NUMBER := NULL;
10
11 ---------------------------------------------
12 -- Private Functions
13 ---------------------------------------------
14 function create_locator_segment (
15 p_segment_num number,
16 p_segment_name varchar2,
17 p_column_name varchar2,
18 p_value_set_name varchar2,
19 p_default_type varchar2,
20 p_default_value varchar2,
21 p_qualifier varchar2)
22 return boolean is
23
24 l_value_set_id number := -1;
25 l_seg_value_set_id number := -2;
26 l_flexfield fnd_flex_key_api.flexfield_type;
27 l_new_flexfield fnd_flex_key_api.flexfield_type;
28 l_structure fnd_flex_key_api.structure_type;
29 l_new_segment fnd_flex_key_api.segment_type;
30
31 begin
32 select flex_value_set_id
33 into l_value_set_id
34 from fnd_flex_value_sets
35 where flex_value_set_name = p_value_set_name;
36
37 begin
38 select flex_value_set_id
39 into l_seg_value_set_id
40 from fnd_id_flex_segments
41 where application_id = 401
42 and id_flex_code = 'MTLL'
43 and id_flex_num = 101
44 and application_column_name = p_column_name;
45
46 --
47 -- Fixed bug 851461
48 --
49 -- Previous logic does not handle the case when Project
50 -- Manufacturing is installed and the first Inventory Org
51 -- is not setup as Project References Enabled. In this case
52 -- the code went ahead and tried to create the segment again,
53 -- resulting in failure condition in the AOL Flex API.
54 --
55 if ( l_seg_value_set_id = l_value_set_id ) then
56 --
57 -- Segment already correctly defined
58 --
59 return(TRUE);
60 end if;
61
62 --
63 -- In previous versions of this function, the following logic
64 -- is placed after this sub-block. It is now moved to this
65 -- location which is more logical and in line with the fix
66 -- for bug 851461
67 --
68 if ( l_seg_value_set_id <> l_value_set_id ) then
69 --
70 -- Segment already in use
71 --
72 fnd_message.set_name('PJM','INST-SEGMENT IN USE');
73 fnd_message.set_token('SEGMENT',p_column_name);
74 return(FALSE);
75 end if;
76
77 exception
78 when no_data_found then
79 null;
80 when others then
81 raise;
82 end;
83
84 --
85 -- Make sure flexfield allows ID valuesets
86 --
87 fnd_flex_key_api.set_session_mode('seed_data');
88 l_flexfield := fnd_flex_key_api.find_flexfield('INV','MTLL');
89 l_new_flexfield := l_flexfield;
90 l_new_flexfield.allow_id_value_sets := 'Y';
91 fnd_flex_key_api.modify_flexfield(l_flexfield, l_new_flexfield);
92
93 --
94 -- Creating Locator Flexfield Segment
95 --
96 l_flexfield := fnd_flex_key_api.find_flexfield('INV','MTLL');
97 l_structure := fnd_flex_key_api.find_structure(l_flexfield,101);
98 l_new_segment := fnd_flex_key_api.new_segment(
99 flexfield => l_flexfield,
100 structure => l_structure,
101 segment_name => p_segment_name,
102 description => p_segment_name,
103 column_name => p_column_name,
104 segment_number => p_segment_num,
105 enabled_flag => 'Y',
106 displayed_flag => 'Y',
107 indexed_flag => 'N',
108 value_set => p_value_set_name,
109 default_type => p_default_type,
110 default_value => p_default_value,
111 required_flag => 'N',
112 security_flag => 'N',
113 range_code => NULL,
114 display_size => 25,
115 description_size => 50,
116 concat_size => 25,
117 lov_prompt => p_segment_name,
118 window_prompt => p_segment_name
119 );
120
121 fnd_flex_key_api.add_segment(
122 flexfield => l_flexfield,
123 structure => l_structure,
124 segment => l_new_segment
125 );
126
127 fnd_flex_key_api.assign_qualifier(
128 flexfield => l_flexfield,
129 structure => l_structure,
130 segment => l_new_segment,
131 flexfield_qualifier => p_qualifier,
132 enable_flag => 'Y'
133 );
134
135 --
136 -- Deleting Compiled Flexfield information
137 --
138 -- delete from fnd_compiled_id_flexs
139 -- where application_id = 401
140 -- and id_flex_code = 'MTLL';
141
142 -- delete from fnd_compiled_id_flex_structs
143 -- where application_id = 401
144 -- and id_flex_code = 'MTLL'
145 -- and id_flex_num = 101;
146
147 return(TRUE);
148
149 end create_locator_segment;
150
151
152 ---------------------------------------------
153 -- Public Functions
154 ---------------------------------------------
155 function CHECK_IMPLEMENTATION_STATUS
156 ( P_Organization_ID NUMBER
157 ) return boolean is
158
159 cursor c is
160 select organization_id
161 from pjm_org_parameters
162 where organization_id = P_organization_id;
163
164 cursor c2 is
165 select organization_id
166 from pjm_org_parameters
167 where organization_id > 0;
168
169 dummy number;
170
171 begin
172 --
173 -- PJM not implemented if not installed
174 --
175 if NOT check_install then
176 return false;
177 end if;
178
179 if ( G_Implemented is null
180 --
181 -- Make sure cached value matches current inquiry
182 --
183 OR ( G_Implemented_Org <> P_Organization_ID )
184 --
185 -- Special case: no need to recache if cache value indicates PJM is already
186 -- implemented in a specific org (NULL org means any org)
187 --
188 OR ( P_Organization_ID is null AND G_Implemented_Org <> 0 AND NOT G_Implemented )
189 ) then
190 if ( P_Organization_ID is not null ) then
191 open c;
192 fetch c into dummy;
193 G_Implemented := NOT ( c%notfound );
194 close c;
195 G_Implemented_Org := P_Organization_ID;
196 else
197 open c2;
198 fetch c2 into dummy;
199 G_Implemented := NOT ( c2%notfound );
200 close c2;
201 G_Implemented_Org := 0;
202 end if;
203 end if;
204 return G_Implemented;
205 exception
206 when others then
207 return false;
208 end CHECK_IMPLEMENTATION_STATUS;
209
210
211 function CHECK_INSTALL
212 return boolean is
213 l_status varchar2(1);
214 l_industry varchar2(1);
215 l_ora_schema varchar2(30);
216 l_return_code boolean;
217 begin
218 if ( G_Installed is NULL ) then
219 --
220 -- Call FND routine to figure out installation status
221 --
222 -- If the license status is not 'I', Project Manufacturing is
223 -- not installed.
224 --
225 l_return_code := fnd_installation.get_app_info('PJM',
226 l_status,
227 l_industry,
228 l_ora_schema);
229
230 -- this is the old code which CHECK_INSTALL always return TRUE, comment out
231 -- if (l_return_code = FALSE) then
232 -- G_Installed := FALSE;
233 -- end if;
234 -- if (l_status <> 'I') then
235 -- G_Installed := FALSE;
236 -- end if;
237 -- G_Installed := TRUE;
238 -- end if;
239 -- return G_Installed;
240
241 /* New code for fixing bug 8969284 */
242 if (l_return_code = FALSE) then
243 G_Installed := FALSE;
244 return G_Installed;
245 end if;
246
247 if (l_status = 'I') then
248 G_Installed := TRUE;
249 else
250 G_Installed := FALSE;
251 end if;
252 end if;
253 return G_Installed;
254 /* end of bug 8969284 */
255
256 end CHECK_INSTALL;
257
258 function ENABLE_INSTALL
259 return boolean is
260 l_return_code boolean;
261 l_orgcount number;
262 l_segment_name varchar2(30);
263 begin
264
265 --
266 -- If Project Manufacturing is not installed, immediately get out
267 --
268 if (not pjm_install.check_install) then
269 return TRUE;
270 end if;
271
272 select count(*)
273 into l_orgcount
274 from pjm_org_parameters
275 where project_reference_enabled = 'Y'
276 and organization_id <> FND_PROFILE.VALUE('MFG_ORGANIZATION_ID');
277
278 if (l_orgcount > 0) then
279 --
280 -- There are already project enabled orgs. We don't need
281 -- to do anything else
282 --
283 return(TRUE);
284 end if;
285
286 savepoint pre_insert;
287
288 fnd_message.set_name('PA','PA_TKN_PRJ');
289 l_segment_name := fnd_message.get;
290 l_return_code := create_locator_segment(
291 19,
292 l_segment_name,
293 'SEGMENT19',
294 'PJM_PROJECT',
295 'S',
296 'select pjm_project_locator.Proj_Seg_Default from sys.dual',
297 'PJM_PROJECT'
298 );
299 if (l_return_code = FALSE) then
300 rollback to savepoint pre_insert;
301 return(FALSE);
302 end if;
303
304 fnd_message.set_name('PA','PA_TKN_TSK');
305 l_segment_name := fnd_message.get;
306 l_return_code := create_locator_segment(
307 20,
308 l_segment_name,
309 'SEGMENT20',
310 'PJM_TASK',
311 'S',
312 'select pjm_project_locator.Task_Seg_Default from sys.dual',
313 'PJM_TASK'
314 );
315 if (l_return_code = FALSE) then
316 rollback to savepoint pre_insert;
317 return(FALSE);
318 end if;
319 return(TRUE);
320
321 end enable_install;
322
323
324 procedure maintain_locator_valuesets is
325
326 --
327 -- Making this procedure as AUTONOMOUS transaction
328 --
329 -- pragma autonomous_transaction;
330
331 cursor c is
332 select ou.oracle_username
333 from fnd_product_installations pi
334 , fnd_oracle_userid ou
335 where ou.oracle_id = pi.oracle_id
336 and application_id = 0;
337
338 cursor c2 is
339 select decode( min( seiban_number_flag ) , 1 , 'Y' , 'N' ) seiban_used
340 , decode( max( seiban_number_flag ) , 2 , 'Y' , 'N' ) project_used
341 from pjm_project_parameters;
342
343 seiban_used varchar2(1);
344 project_used varchar2(1);
345 applsys_schema varchar2(30);
346 target_name varchar2(30);
347
348 procedure create_synonym
349 ( X_synonym_name in varchar2
350 , X_table_name in varchar2
351 ) is
352
353 sqlstmt varchar2(240);
354 curr_table_name varchar2(30);
355 create_flag varchar2(1);
356
357 cursor s ( c_name varchar2 ) is
358 select table_name
359 from user_synonyms
360 where synonym_name = c_name;
361
362 begin
363 --
364 -- Check with existence of synonym first
365 --
366 open s ( X_synonym_name );
367 fetch s into curr_table_name;
368 if ( s%notfound ) then
369 --
370 -- If synonym not found, we need to create it
371 --
372 close s;
373 --
374 -- Drop the view just in case
375 --
376 ad_ddl.do_ddl( applsys_schema
377 , 'PJM'
378 , ad_ddl.drop_view
379 , 'DROP VIEW ' || X_synonym_name
380 , X_synonym_name );
381
382 create_flag := 'Y';
383 else
384 close s;
385 if ( curr_table_name <> X_table_name ) then
386 --
387 -- Synonym exists but points to a different object. We need to drop
388 -- the existing synonym first before recreating the new one
389 --
390 ad_ddl.do_ddl( applsys_schema
391 , 'PJM'
392 , ad_ddl.drop_synonym
393 , 'DROP SYNONYM ' || X_synonym_name
394 , X_synonym_name );
395 create_flag := 'Y';
396 else
397 --
398 -- Existing synonym is what we want, no need to do anything
399 --
400 create_flag := 'N';
401 end if;
402 end if;
403 if ( create_flag = 'Y' ) then
404 sqlstmt := 'CREATE SYNONYM ' || X_synonym_name || ' FOR ' || X_table_name;
405 ad_ddl.do_ddl( applsys_schema
406 , 'PJM'
407 , ad_ddl.create_synonym
408 , sqlstmt
409 , X_synonym_name );
410 end if;
411 end create_synonym;
412
413 begin
414
415 open c;
416 fetch c into applsys_schema;
417 close c;
418
419 open c2;
420 fetch c2 into seiban_used , project_used;
421 close c2;
422
423 if ( project_used = 'Y' and seiban_used = 'Y' ) then
424 target_name := 'PJM_PROJECTS_MTLL_PSV';
425 elsif ( project_used = 'Y' ) then
426 target_name := 'PJM_PROJECTS_MTLL_PV';
427 else
428 target_name := 'PJM_PROJECTS_MTLL_SV';
429 end if;
430
431 create_synonym( 'PJM_PROJECTS_MTLL_V' , target_name );
432
433 end maintain_locator_valuesets;
434
435 end PJM_INSTALL;