[Home] [Help]
PACKAGE BODY: APPS.PJM_INSTALL
Source
1 package body PJM_INSTALL as
2 /* $Header: PJMINSTB.pls 120.0 2005/05/24 18:06:24 appldev noship $ */
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 if (l_return_code = FALSE) then
231 G_Installed := FALSE;
232 end if;
233 if (l_status <> 'I') then
234 G_Installed := FALSE;
235 end if;
236 G_Installed := TRUE;
237 end if;
238 return G_Installed;
239 end CHECK_INSTALL;
240
241 function ENABLE_INSTALL
242 return boolean is
243 l_return_code boolean;
244 l_orgcount number;
245 l_segment_name varchar2(30);
246 begin
247
248 --
249 -- If Project Manufacturing is not installed, immediately get out
250 --
251 if (not pjm_install.check_install) then
252 return TRUE;
253 end if;
254
255 select count(*)
256 into l_orgcount
257 from pjm_org_parameters
258 where project_reference_enabled = 'Y'
259 and organization_id <> FND_PROFILE.VALUE('MFG_ORGANIZATION_ID');
260
261 if (l_orgcount > 0) then
262 --
263 -- There are already project enabled orgs. We don't need
264 -- to do anything else
265 --
266 return(TRUE);
267 end if;
268
269 savepoint pre_insert;
270
271 fnd_message.set_name('PA','PA_TKN_PRJ');
272 l_segment_name := fnd_message.get;
273 l_return_code := create_locator_segment(
274 19,
275 l_segment_name,
276 'SEGMENT19',
277 'PJM_PROJECT',
278 'S',
279 'select pjm_project_locator.Proj_Seg_Default from sys.dual',
280 'PJM_PROJECT'
281 );
282 if (l_return_code = FALSE) then
283 rollback to savepoint pre_insert;
284 return(FALSE);
285 end if;
286
287 fnd_message.set_name('PA','PA_TKN_TSK');
288 l_segment_name := fnd_message.get;
289 l_return_code := create_locator_segment(
290 20,
291 l_segment_name,
292 'SEGMENT20',
293 'PJM_TASK',
294 'S',
295 'select pjm_project_locator.Task_Seg_Default from sys.dual',
296 'PJM_TASK'
297 );
298 if (l_return_code = FALSE) then
299 rollback to savepoint pre_insert;
300 return(FALSE);
301 end if;
302 return(TRUE);
303
304 end enable_install;
305
306
307 procedure maintain_locator_valuesets is
308
309 --
310 -- Making this procedure as AUTONOMOUS transaction
311 --
312 -- pragma autonomous_transaction;
313
314 cursor c is
315 select ou.oracle_username
316 from fnd_product_installations pi
317 , fnd_oracle_userid ou
318 where ou.oracle_id = pi.oracle_id
319 and application_id = 0;
320
321 cursor c2 is
322 select decode( min( seiban_number_flag ) , 1 , 'Y' , 'N' ) seiban_used
323 , decode( max( seiban_number_flag ) , 2 , 'Y' , 'N' ) project_used
324 from pjm_project_parameters;
325
326 seiban_used varchar2(1);
327 project_used varchar2(1);
328 applsys_schema varchar2(30);
329 target_name varchar2(30);
330
331 procedure create_synonym
332 ( X_synonym_name in varchar2
333 , X_table_name in varchar2
334 ) is
335
336 sqlstmt varchar2(240);
337 curr_table_name varchar2(30);
338 create_flag varchar2(1);
339
340 cursor s ( c_name varchar2 ) is
341 select table_name
342 from user_synonyms
343 where synonym_name = c_name;
344
345 begin
346 --
347 -- Check with existence of synonym first
348 --
349 open s ( X_synonym_name );
350 fetch s into curr_table_name;
351 if ( s%notfound ) then
352 --
353 -- If synonym not found, we need to create it
354 --
355 close s;
356 --
357 -- Drop the view just in case
358 --
359 ad_ddl.do_ddl( applsys_schema
360 , 'PJM'
361 , ad_ddl.drop_view
362 , 'DROP VIEW ' || X_synonym_name
363 , X_synonym_name );
364
365 create_flag := 'Y';
366 else
367 close s;
368 if ( curr_table_name <> X_table_name ) then
369 --
370 -- Synonym exists but points to a different object. We need to drop
371 -- the existing synonym first before recreating the new one
372 --
373 ad_ddl.do_ddl( applsys_schema
374 , 'PJM'
375 , ad_ddl.drop_synonym
376 , 'DROP SYNONYM ' || X_synonym_name
377 , X_synonym_name );
378 create_flag := 'Y';
379 else
380 --
381 -- Existing synonym is what we want, no need to do anything
382 --
383 create_flag := 'N';
384 end if;
385 end if;
386 if ( create_flag = 'Y' ) then
387 sqlstmt := 'CREATE SYNONYM ' || X_synonym_name || ' FOR ' || X_table_name;
388 ad_ddl.do_ddl( applsys_schema
389 , 'PJM'
390 , ad_ddl.create_synonym
391 , sqlstmt
392 , X_synonym_name );
393 end if;
394 end create_synonym;
395
396 begin
397
398 open c;
399 fetch c into applsys_schema;
400 close c;
401
402 open c2;
403 fetch c2 into seiban_used , project_used;
404 close c2;
405
406 if ( project_used = 'Y' and seiban_used = 'Y' ) then
407 target_name := 'PJM_PROJECTS_MTLL_PSV';
408 elsif ( project_used = 'Y' ) then
409 target_name := 'PJM_PROJECTS_MTLL_PV';
410 else
411 target_name := 'PJM_PROJECTS_MTLL_SV';
412 end if;
413
414 create_synonym( 'PJM_PROJECTS_MTLL_V' , target_name );
415
416 end maintain_locator_valuesets;
417
418 end PJM_INSTALL;