[Home] [Help]
PACKAGE BODY: APPS.MSD_CS_DEFN_LOAD_DATA
Source
1 package body msd_CS_DEFN_load_data as
2 /* $Header: msdcsdfb.pls 120.0 2005/05/25 19:27:52 appldev noship $ */
3 Procedure load_row (
4 p_name in varchar2,
5 p_description in varchar2,
6 p_plan_type in varchar2,
7 p_liability_user_flag in varchar2,
8 p_cs_classification in varchar2,
9 p_cs_type in varchar2,
10 p_strict_flag in varchar2,
11 p_system_flag in varchar2,
12 p_multiple_stream_flag in varchar2,
13 p_planning_server_view_name in varchar2,
14 p_planning_server_view_name_ds in varchar2,
15 p_stripe_flag in varchar2,
16 p_source_view_name in varchar2,
17 p_collection_program_name in varchar2,
18 p_collect_addtl_where_clause in varchar2,
19 p_pull_addtl_where_clause in varchar2,
20 p_valid_flag in varchar2,
21 p_stream_editable_flag in varchar2 ,
22 p_aggregation_allowed_flag in varchar2 ,
23 p_allocation_allowed_flag in varchar2 ,
24 p_dependent_data_flag in varchar2,
25 p_dependent_demand_code in varchar2,
26 p_measurement_type in varchar2 ,
27 p_enable_flag in varchar2 ,
28 p_cs_lov_view_name in varchar2,
29 p_lowest_level_flag in varchar2,
30 p_owner in varchar2,
31 p_last_update_date in varchar2,
32 p_custom_mode in varchar2) is
33 Begin
34
35 DEFN_Update_row(
36 p_cs_definition_id => null ,
37 p_name => p_name ,
38 p_plan_type => p_plan_type ,
39 p_liability_user_flag => p_liability_user_flag ,
40 p_description => p_description ,
41 p_cs_classification => p_cs_classification ,
42 p_cs_type => p_cs_type ,
43 p_strict_flag => p_strict_flag ,
44 p_system_flag => p_system_flag ,
45 p_multiple_stream_flag => p_multiple_stream_flag ,
46 p_planning_server_view_name => p_planning_server_view_name ,
47 p_planning_server_view_name_ds => p_planning_server_view_name_ds,
48 p_stripe_flag => p_stripe_flag,
49 p_source_view_name => p_source_view_name ,
50 p_collection_program_name => p_collection_program_name ,
51 p_collect_addtl_where_clause => p_collect_addtl_where_clause ,
52 p_pull_addtl_where_clause => p_pull_addtl_where_clause ,
53 p_valid_flag => p_valid_flag ,
54 p_stream_editable_flag => p_stream_editable_flag ,
55 p_aggregation_allowed_flag => p_aggregation_allowed_flag ,
56 p_allocation_allowed_flag => p_allocation_allowed_flag ,
57 p_dependent_data_flag => p_dependent_data_flag ,
58 p_dependent_demand_code => p_dependent_demand_code ,
59 p_enable_flag => p_enable_flag ,
60 p_cs_lov_view_name => p_cs_lov_view_name ,
61 p_lowest_level_flag => p_lowest_level_flag ,
62 p_measurement_type => p_measurement_type ,
63 p_owner => p_owner ,
64 p_last_update_date => p_last_update_date ,
65 p_custom_mode => p_custom_mode );
66 Exception
67 when no_data_found then
68 DEFN_Insert_row(
69 p_name => p_name ,
70 p_description => p_description ,
71 p_plan_type =>p_plan_type ,
72 p_liability_user_flag => p_liability_user_flag ,
73 p_cs_classification => p_cs_classification ,
74 p_cs_type => p_cs_type ,
75 p_strict_flag => p_strict_flag ,
76 p_system_flag => p_system_flag ,
77 p_multiple_stream_flag => p_multiple_stream_flag ,
78 p_planning_server_view_name => p_planning_server_view_name ,
79 p_planning_server_view_name_ds => p_planning_server_view_name_ds,
80 p_stripe_flag => p_stripe_flag ,
81 p_source_view_name => p_source_view_name ,
82 p_collection_program_name => p_collection_program_name ,
83 p_collect_addtl_where_clause => p_collect_addtl_where_clause ,
84 p_pull_addtl_where_clause => p_pull_addtl_where_clause ,
85 p_valid_flag => p_valid_flag ,
86 p_stream_editable_flag => p_stream_editable_flag ,
87 p_aggregation_allowed_flag => p_aggregation_allowed_flag ,
88 p_allocation_allowed_flag => p_allocation_allowed_flag ,
89 p_dependent_data_flag => p_dependent_data_flag ,
90 p_dependent_demand_code => p_dependent_demand_code ,
91 p_enable_flag => p_enable_flag ,
92 p_cs_lov_view_name => p_cs_lov_view_name ,
93 p_lowest_level_flag => p_lowest_level_flag ,
94 p_measurement_type => p_measurement_type ,
95 p_owner => p_owner ,
96 p_last_update_date => p_last_update_date );
97
98 End;
99
100
101 Procedure DEFN_UPDATE_row (
102 p_cs_definition_id in number,
103 p_name in varchar2,
104 p_plan_type in varchar2 ,
105 p_liability_user_flag in varchar2 ,
106 p_description in varchar2,
107 p_cs_classification in varchar2,
108 p_cs_type in varchar2,
109 p_strict_flag in varchar2,
110 p_system_flag in varchar2,
111 p_multiple_stream_flag in varchar2,
112 p_planning_server_view_name in varchar2,
113 p_planning_server_view_name_ds in varchar2,
114 p_stripe_flag in varchar2,
115 p_source_view_name in varchar2,
116 p_collection_program_name in varchar2,
117 p_collect_addtl_where_clause in varchar2,
118 p_pull_addtl_where_clause in varchar2,
119 p_valid_flag in varchar2,
120 p_stream_editable_flag in varchar2,
121 p_aggregation_allowed_flag in varchar2,
122 p_allocation_allowed_flag in varchar2,
123 p_dependent_data_flag in varchar2,
124 p_dependent_demand_code in varchar2,
125 p_enable_flag in varchar2,
126 p_cs_lov_view_name in varchar2,
127 p_lowest_level_flag in varchar2,
128 p_measurement_type in varchar2,
129 p_owner in varchar2,
130 p_last_update_date in varchar2,
131 p_custom_mode in varchar2) is
132
133
134 l_user number; -- entity owner in file
135 l_definition_id number;
136
137 f_ludate date; -- entity update date in file
138 db_luby number; -- entity owner in db
139 db_ludate date; -- entity update date in db
140
141
142 Begin
143
144 if p_owner = 'SEED' then
145 l_user := 1;
146 else
147 l_user := 0;
148 end if;
149
150 -- Translate char last_update_date to date
151 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
152
153 if p_cs_definition_id is null then
154 select cs_definition_id, last_update_date, last_updated_by
155 into l_definition_id, db_ludate, db_luby
156 from msd_cs_definitions
157 where name = p_name;
158 end if;
159
160 if ((p_custom_mode = 'FORCE') or
161 ((l_user = 0) and (db_luby = 1)) or
162 ((l_user = db_luby) and (f_ludate > db_ludate))) then
163
164 update msd_cs_definitions set
165 description = p_description,
166 cs_classification = p_cs_classification,
167 plan_type = p_plan_type ,
168 liability_user_flag= p_liability_user_flag,
169 cs_type = p_cs_type,
170 strict_flag = p_strict_flag,
171 system_flag = p_system_flag,
172 multiple_stream_flag = p_multiple_stream_flag,
173 planning_server_view_name = p_planning_server_view_name,
174 planning_server_view_name_ds = p_planning_server_view_name_ds,
175 stripe_flag = p_stripe_flag,
176 source_view_name = p_source_view_name,
177 collection_program_name = p_collection_program_name,
178 collect_addtl_where_clause = p_collect_addtl_where_clause,
179 pull_addtl_where_clause = p_pull_addtl_where_clause,
180 valid_flag = p_valid_flag,
181 stream_editable_flag = p_stream_editable_flag,
182 aggregation_allowed_flag = p_aggregation_allowed_flag,
183 allocation_allowed_flag = p_allocation_allowed_flag,
184 dependent_data_flag = p_dependent_data_flag,
185 dependent_demand_code = p_dependent_demand_code,
186 measurement_type = p_measurement_type,
187 enable_flag = p_enable_flag,
188 cs_lov_view_name = p_cs_lov_view_name,
189 lowest_level_flag = p_lowest_level_flag,
190 -- creation_date = p_last_update_date,
191 -- created_by = l_user,
192 last_update_date = f_ludate,
193 last_updated_by = l_user,
194 last_update_login = fnd_global.login_id
195 where
196 cs_definition_id = l_definition_id;
197
198 update msd_cs_definitions_TL set
199 description = p_description,
200 LAST_UPDATE_DATE = f_ludate,
201 LAST_UPDATED_BY = l_user,
202 LAST_UPDATE_LOGIN = fnd_global.login_id,
203 SOURCE_LANG = userenv('LANG')
204 where
205 cs_definition_id = l_definition_id
206 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
207
208 else
209
210 update msd_cs_definitions set
211 cs_classification = p_cs_classification,
212 cs_type = p_cs_type,
213 plan_type = p_plan_type ,
214 liability_user_flag= p_liability_user_flag,
215 strict_flag = p_strict_flag,
216 system_flag = p_system_flag,
217 multiple_stream_flag = p_multiple_stream_flag,
218 planning_server_view_name = p_planning_server_view_name,
219 planning_server_view_name_ds = p_planning_server_view_name_ds,
220 stripe_flag = p_stripe_flag,
221 source_view_name = p_source_view_name,
222 collection_program_name = p_collection_program_name,
223 collect_addtl_where_clause = p_collect_addtl_where_clause,
224 pull_addtl_where_clause = p_pull_addtl_where_clause,
225 valid_flag = p_valid_flag,
226 -- stream_editable_flag = p_stream_editable_flag,
227 -- aggregation_allowed_flag = p_aggregation_allowed_flag,
228 -- allocation_allowed_flag = p_allocation_allowed_flag,
229 dependent_data_flag = p_dependent_data_flag,
230 dependent_demand_code = p_dependent_demand_code,
231 measurement_type = p_measurement_type,
232 enable_flag = p_enable_flag,
233 cs_lov_view_name = p_cs_lov_view_name,
234 -- lowest_level_flag = p_lowest_level_flag,
235 -- creation_date = f_ludate,
236 -- created_by = l_user,
237 last_update_date = f_ludate,
238 last_updated_by = l_user,
239 last_update_login = fnd_global.login_id
240 where
241 cs_definition_id = l_definition_id;
242
243 end if;
244
245
246 if (sql%notfound) then
247 raise no_data_found;
248 end if;
249
250
251 if (sql%notfound) then
252
253 select cs_definition_id into l_definition_id
254 from msd_cs_definitions where name = p_name;
255
256 insert into msd_cs_definitions_TL(
257 cs_definition_id,
258 description,
259 language,
260 source_lang,
261 created_by,
262 creation_date,
263 last_updated_by,
264 last_update_date ,
265 last_update_login
266 )
267 Select
268 l_definition_id,
269 p_description,
270 l.language_code,
271 userenv('LANG'),
272 l_user,
273 f_ludate,
274 fnd_global.user_id,
275 f_ludate,
276 fnd_global.login_id
277 from fnd_languages l
278 where l.installed_flag in ('I','B');
279 /* and not exists (select null
280 from msd_cs_definitions_TL
281 and rtl.language = l.language_code );
282 */
283 end if;
284
285 End;
286
287 Procedure DEFN_Insert_row (
288 p_name in varchar2,
289 p_description in varchar2,
290 p_plan_type in varchar2 ,
291 p_liability_user_flag in varchar2,
292 p_cs_classification in varchar2,
293 p_cs_type in varchar2,
294 p_strict_flag in varchar2,
295 p_system_flag in varchar2,
296 p_multiple_stream_flag in varchar2,
297 p_planning_server_view_name in varchar2,
298 p_planning_server_view_name_ds in varchar2,
299 p_stripe_flag in varchar2,
300 p_source_view_name in varchar2,
301 p_collection_program_name in varchar2,
302 p_collect_addtl_where_clause in varchar2,
303 p_pull_addtl_where_clause in varchar2,
304 p_valid_flag in varchar2,
305 p_stream_editable_flag in varchar2,
306 p_aggregation_allowed_flag in varchar2,
307 p_allocation_allowed_flag in varchar2,
308 p_dependent_data_flag in varchar2,
309 p_dependent_demand_code in varchar2,
310 p_enable_flag in varchar2,
311 p_cs_lov_view_name in varchar2,
312 p_lowest_level_flag in varchar2,
313 p_measurement_type in varchar2,
314 p_owner in varchar2,
315 p_last_update_date in varchar2) is
316
317
318
319 l_user number;
320 l_definition_id number;
321 f_ludate date; -- entity update date in file
322
323 Begin
324
325 if p_owner = 'SEED' then
326 l_user := 1;
327 else
328 l_user := 0;
329 end if;
330
331 -- Translate char last_update_date to date
332 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
333
334 select msd_cs_definitions_s.nextval into l_definition_id from dual;
335
336 insert into msd_cs_definitions
337 (
338 cs_definition_id,
339 name,
340 description,
341 plan_type ,
342 liability_user_flag,
343 cs_classification,
344 cs_type,
345 lowest_level_flag,
346 strict_flag,
347 system_flag,
348 local_stream_flag,
349 multiple_stream_flag,
350 planning_server_view_name,
354 collection_program_name,
351 planning_server_view_name_ds,
352 stripe_flag,
353 source_view_name,
355 collect_addtl_where_clause,
356 pull_addtl_where_clause,
357 valid_flag,
358 /* New Fields */
359 stream_editable_flag ,
360 aggregation_allowed_flag ,
361 allocation_allowed_flag ,
362 dependent_data_flag ,
363 dependent_demand_code ,
364 measurement_type ,
365 enable_flag ,
366 cs_lov_view_name ,
367 /* */
368 creation_date,
369 created_by,
370 last_update_date,
371 last_updated_by,
372 last_update_login
373 )
374 values
375 (
376 l_definition_id,
377 p_name,
378 p_description,
379 p_plan_type,
380 p_liability_user_flag,
381 p_cs_classification,
382 p_cs_type,
383
384 /** Replaced 'N' with p_lowest_level_flag for Allocation Floor, **/
385 p_lowest_level_flag,
386 p_strict_flag,
387 p_system_flag,
388 'N',
389 p_multiple_stream_flag,
390 p_planning_server_view_name,
391 p_planning_server_view_name_ds,
392 p_stripe_flag,
393 p_source_view_name,
394 p_collection_program_name,
395 p_collect_addtl_where_clause,
396 p_pull_addtl_where_clause,
397 p_valid_flag,
398 /* New Fields */
399 p_stream_editable_flag ,
400 p_aggregation_allowed_flag ,
401 p_allocation_allowed_flag ,
402 p_dependent_data_flag ,
403 p_dependent_demand_code ,
404 p_measurement_type ,
405 p_enable_flag ,
406 p_cs_lov_view_name ,
407 /* */
408 f_ludate,
409 l_user,
410 f_ludate,
411 l_user,
412 fnd_global.login_id
413 );
414
415 insert into msd_cs_definitions_TL(
416 cs_definition_id,
417 description,
418 language,
419 source_lang,
420 created_by,
421 creation_date,
422 last_updated_by,
423 last_update_date ,
424 last_update_login
425 )
426 Select
427 l_definition_id,
428 p_description,
429 l.language_code,
430 userenv('LANG'),
431 fnd_global.user_id,
432 f_ludate,
433 fnd_global.user_id,
434 f_ludate,
435 fnd_global.login_id
436 from fnd_languages l
437 where l.installed_flag in ('I','B');
438 End;
439
440 Procedure translate_row (
441 p_name in varchar2,
442 p_description in varchar2,
443 p_owner in varchar2) is
444
445 l_user number:= 1;
446 Begin
447
448 if p_owner = 'SEED' then
449 l_user := 1;
450 else
451 l_user := 0;
452 end if;
453
454 update msd_cs_definitions_TL set
455 description = p_description,
456 LAST_UPDATE_DATE = sysdate,
457 LAST_UPDATED_BY = l_user,
458 LAST_UPDATE_LOGIN = fnd_global.login_id,
459 SOURCE_LANG = userenv('LANG')
460 where
461 cs_definition_id = (select cs_definition_id from msd_cs_definitions where name = p_name)
462 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
463
464 End;
465
466 Procedure ADD_LANGUAGE
467 is
468 begin
469 delete from MSD_CS_DEFINITIONS_TL T
470 where not exists
471 (select NULL
472 from MSD_CS_DEFINITIONS B
473 where B.CS_DEFINITION_ID = T.CS_DEFINITION_ID
474 );
475
476 update MSD_CS_DEFINITIONS_TL T set (
477 DESCRIPTION
478 ) = (select
479 B.DESCRIPTION
480 from MSD_CS_DEFINITIONS_TL B
481 where B.CS_DEFINITION_ID = T.CS_DEFINITION_ID
482 and B.LANGUAGE = T.SOURCE_LANG)
483 where (
484 T.CS_DEFINITION_ID,
485 T.LANGUAGE
486 ) in (select
487 SUBT.CS_DEFINITION_ID,
488 SUBT.LANGUAGE
489 from MSD_CS_DEFINITIONS_TL SUBB, MSD_CS_DEFINITIONS_TL SUBT
490 where SUBB.CS_DEFINITION_ID = SUBT.CS_DEFINITION_ID
491 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
492 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
493 ));
494
495 insert into MSD_CS_DEFINITIONS_TL (
496 CS_DEFINITION_ID,
497 DESCRIPTION,
498 CREATION_DATE,
499 CREATED_BY,
500 LAST_UPDATE_DATE,
501 LAST_UPDATED_BY,
502 LAST_UPDATE_LOGIN,
503 REQUEST_ID,
504 PROGRAM_APPLICATION_ID,
505 PROGRAM_ID,
506 PROGRAM_UPDATE_DATE,
507 LANGUAGE,
508 SOURCE_LANG
509 ) select
510 B.CS_DEFINITION_ID,
511 B.DESCRIPTION,
512 B.CREATION_DATE,
513 B.CREATED_BY,
514 B.LAST_UPDATE_DATE,
515 B.LAST_UPDATED_BY,
516 B.LAST_UPDATE_LOGIN,
517 B.REQUEST_ID,
518 B.PROGRAM_APPLICATION_ID,
519 B.PROGRAM_ID,
520 B.PROGRAM_UPDATE_DATE,
521 L.LANGUAGE_CODE,
522 B.SOURCE_LANG
523 from MSD_CS_DEFINITIONS_TL B, FND_LANGUAGES L
524 where L.INSTALLED_FLAG in ('I', 'B')
525 and B.LANGUAGE = userenv('LANG')
526 and not exists
527 (select NULL
528 from MSD_CS_DEFINITIONS_TL T
529 where T.CS_DEFINITION_ID = B.CS_DEFINITION_ID
530 and T.LANGUAGE = L.LANGUAGE_CODE);
531 End ADD_LANGUAGE;
532
533 End;