[Home] [Help]
PACKAGE BODY: APPS.QP_LOADER_DIST_PUB
Source
1 package body qp_loader_dist_pub as
2 /* $Header: QPXPLDDB.pls 120.1 2006/06/21 09:35:22 rnayani noship $ */
3
4 function p_detect_dup_mapping_cols(p_segment_id in number,
5 p_prc_context_id in number,
6 p_segment_mapping_column in varchar2,
7 p_segment_code in varchar2,
8 p_action in varchar2) return number
9 is
10 dummy varchar2(1);
11 begin
12 select 'x'
13 into dummy
14 from qp_segments_b a, qp_prc_contexts_b b
15 where a.prc_context_id = b.prc_context_id and
16 a.prc_context_id = p_prc_context_id and
17 segment_mapping_column = p_segment_mapping_column and
18 segment_code <> p_segment_code and
19 rownum = 1;
20 --
21 insert into qp_upgrade_errors
22 (creation_date,
23 created_by,
24 last_update_date,
25 last_updated_by,
26 error_type,
27 error_desc,
28 error_module) values
29 (sysdate,
30 fnd_global.user_id,
31 sysdate,
32 fnd_global.user_id,
33 'ATTRIBUTE_MANAGER_LCT_UPLOAD',
34 substr('Seeded attribute '|| upper(p_segment_code) ||
35 ' mapped to '|| p_segment_mapping_column ||
36 ' is already used. ' || p_action||' this Attribute,'||
37 ' its PTE-Links and Mapping rules manually.'||
38 ' Refer to Pricing Implementation Guide for this Attribute''s details.',1,200),
39 'Attribute Manager');
40 --
41 return(0);
42 exception
43 when no_data_found then
44 return(-1);
45 end;
46 --
47 procedure qp_prc_contexts_translate_row (
48 x_prc_context_code in varchar2,
49 x_prc_context_type in varchar2,
50 x_seeded_flag in varchar2,
51 x_enabled_flag in varchar2,
52 x_application_id in varchar2,
53 x_seeded_prc_context_name in varchar2,
54 x_seeded_description in varchar2,
55 x_custom_mode in varchar2,
56 x_last_update_date in varchar2,
57 x_owner in varchar2
58 )
59 is
60 begin
61
62 --Bug#5237456 RAVI
63 --Seeded data does not contain application ID
64 -- if x_application_id = '661' then
65
66 update qp_prc_contexts_tl set
67 user_prc_context_name = decode(seeded_prc_context_name, user_prc_context_name, x_seeded_prc_context_name, user_prc_context_name),
68 --seeded_prc_context_name = nvl(seeded_prc_context_name, x_seeded_prc_context_name),
69 seeded_prc_context_name = nvl(x_seeded_prc_context_name, seeded_prc_context_name),
70 user_description = decode(seeded_description, user_description, x_seeded_description, user_description),
71 --seeded_description = nvl(seeded_description, x_seeded_description),
72 seeded_description = nvl(x_seeded_description, seeded_description),
73 last_update_date = nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
74 last_updated_by = decode(x_owner, 'SEED', 1, 'ORACLE', 1, 3),
75 last_update_login = 0,
76 source_lang = userenv('LANG')
77 where prc_context_id =
78 (select prc_context_id from qp_prc_contexts_b
79 where prc_context_code = x_prc_context_code
80 and prc_context_type = x_prc_context_type)
81 and userenv('LANG') in (language, source_lang);
82
83 --Bug#5237456 RAVI
84 --Seeded data does not contain application ID
85 -- end if;
86
87 end qp_prc_contexts_translate_row;
88
89 procedure qp_prc_contexts_load_row (
90 x_prc_context_code in varchar2,
91 x_prc_context_type in varchar2,
92 x_seeded_flag in varchar2,
93 x_enabled_flag in varchar2,
94 x_application_id in varchar2,
95 x_seeded_prc_context_name in varchar2,
96 x_seeded_description in varchar2,
97 x_custom_mode in varchar2,
98 x_last_update_date in varchar2,
99 x_owner in varchar2
100 )
101 is
102 l_prc_context_id number := null;
103 l_prc_context_s number;
104 l_user_id number := 3;
105
106 begin
107
108
109
110 if (x_owner in ('SEED','ORACLE')) then
111 l_user_id := 1;
112 end if;
113
114 begin
115 select prc_context_id into l_prc_context_id from qp_prc_contexts_b
116 where prc_context_code = x_prc_context_code
117 and prc_context_type = x_prc_context_type
118 and rownum = 1;
119
120 if x_application_id = '661' then
121 update qp_prc_contexts_b set
122 seeded_flag = x_seeded_flag,
123 enabled_flag = x_enabled_flag,
124 last_updated_by = l_user_id,
125 last_update_date = nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
126 last_update_login = 0
127 where prc_context_id = l_prc_context_id;
128
129 update qp_prc_contexts_tl set
130 user_prc_context_name = decode(seeded_prc_context_name, user_prc_context_name, x_seeded_prc_context_name, user_prc_context_name),
131 seeded_prc_context_name = nvl(seeded_prc_context_name, x_seeded_prc_context_name),
132 user_description = decode(seeded_description, user_description, x_seeded_description, user_description),
133 seeded_description = nvl(seeded_description, x_seeded_description),
134 last_update_date = nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
135 last_updated_by = l_user_id,
136 last_update_login = 0,
137 source_lang = userenv('LANG')
138 where prc_context_id = l_prc_context_id
139 and userenv('LANG') in (language, source_lang);
140 end if;
141
142 exception
143 when no_data_found then
144 select qp_prc_contexts_s.nextval into l_prc_context_s from dual;
145 begin
146 insert into qp_prc_contexts_b (
147 prc_context_id,
148 prc_context_code,
149 prc_context_type,
150 seeded_flag,
151 enabled_flag,
152 creation_date,
153 created_by,
154 last_update_date,
155 last_update_login,
156 last_updated_by
157 ) values (
158 l_prc_context_s,
159 x_prc_context_code,
160 x_prc_context_type,
161 x_seeded_flag,
162 x_enabled_flag,
163 sysdate,
164 l_user_id,
165 nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
166 0,
167 l_user_id
168 );
169 commit;
170
171 insert into qp_prc_contexts_tl (
172 prc_context_id,
173 seeded_prc_context_name,
174 user_prc_context_name,
175 seeded_description,
176 user_description,
177 source_lang,
178 language,
179 creation_date,
180 created_by,
181 last_update_date,
182 last_update_login,
183 last_updated_by
184 ) select
185 l_prc_context_s,
186 x_seeded_prc_context_name,
187 x_seeded_prc_context_name,
188 x_seeded_description,
189 x_seeded_description,
190 userenv('LANG'),
191 l.language_code,
192 sysdate,
193 l_user_id,
194 nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
195 0,
196 l_user_id
197 from fnd_languages l
198 where l.installed_flag in ('I', 'B')
199 and not exists
200 (select null
201 from qp_prc_contexts_tl t
202 where t.prc_context_id = l_prc_context_id
203 and t.language = l.language_code);
204 commit;
205 exception
206 when others then
207 null;
208 end;
209 when others then
210 null;
211 end;
212
213 end qp_prc_contexts_load_row;
214
215
216 procedure qp_segments_translate_row (
217 x_segment_code in varchar2,
218 x_prc_context_code in varchar2,
219 x_prc_context_type in varchar2,
220 x_availability_in_basic in varchar2,
221 x_application_id in varchar2,
222 x_segment_mapping_column in varchar2,
223 x_seeded_flag in varchar2,
224 x_seeded_precedence in varchar2,
225 x_flex_value_set_name in varchar2,
226 x_seeded_format_type in varchar2,
227 x_seeded_segment_name in varchar2,
228 x_seeded_description in varchar2,
229 x_custom_mode in varchar2,
230 x_last_update_date in varchar2,
231 x_owner in varchar2
232 )
233 is
234 l_segment_id number;
235 l_prc_context_id number;
236 begin
237 begin
238 select segment_id,
239 prc_context_id
240 into l_segment_id,
241 l_prc_context_id
242 from qp_segments_b
243 where segment_code = x_segment_code and
244 prc_context_id = (select prc_context_id from qp_prc_contexts_b
245 where prc_context_code = x_prc_context_code and
246 prc_context_type = x_prc_context_type);
247 if p_detect_dup_mapping_cols( l_segment_id,
248 l_prc_context_id,
249 x_segment_mapping_column,
250 x_segment_code,
251 'Update') = -1 then
252 update qp_segments_tl
253 set user_segment_name = decode(seeded_segment_name, user_segment_name,
254 x_seeded_segment_name, user_segment_name),
255 --seeded_segment_name = nvl(seeded_segment_name, x_seeded_segment_name),
256 seeded_segment_name = nvl(x_seeded_segment_name, seeded_segment_name),
257 user_description = decode(seeded_description, user_description,
258 x_seeded_segment_name, user_description),
259 --seeded_description = nvl(seeded_description, x_seeded_description),
260 seeded_description = nvl(x_seeded_description, seeded_description),
261 last_update_date = nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
262 last_updated_by = decode(x_owner, 'SEED', 1, 'ORACLE', 1, 3),
263 last_update_login = 0,
264 source_lang = userenv('LANG')
265 where segment_id = l_segment_id and
266 userenv('LANG') in (language, source_lang);
267 end if;
268 exception
269 when others then
270 null;
271 end;
272
273 end qp_segments_translate_row;
274
275 procedure qp_segments_load_row (
276 x_segment_code in varchar2,
277 x_prc_context_code in varchar2,
278 x_prc_context_type in varchar2,
279 x_availability_in_basic in varchar2,
280 x_application_id in varchar2,
281 x_segment_mapping_column in varchar2,
282 x_seeded_flag in varchar2,
283 x_seeded_precedence in varchar2,
284 x_flex_value_set_name in varchar2,
285 x_seeded_format_type in varchar2,
286 x_seeded_segment_name in varchar2,
287 x_seeded_description in varchar2,
288 x_required_flag in varchar2,
289 x_custom_mode in varchar2,
290 x_last_update_date in varchar2,
291 x_owner in varchar2
292 )
293 is
294 l_prc_context_id number;
295 l_segment_id number;
296 l_segment_s number;
297 l_user_id number := 3;
298 l_valueset_id number;
299 nc number;
300 begin
301 if (x_owner in ('SEED','ORACLE')) then
302 l_user_id := 1;
303 end if;
304
305 begin
306 select flex_value_set_id into l_valueset_id from fnd_flex_value_sets
307 where flex_value_set_name = x_flex_value_set_name;
308 exception
309 when others then
310 null;
311 end;
312
313 begin
314 select prc_context_id into l_prc_context_id from qp_prc_contexts_b
315 where prc_context_code = x_prc_context_code
316 and prc_context_type = x_prc_context_type
317 and rownum = 1;
318 exception
319 when others then
320 null;
321 end;
322
323 if l_prc_context_id is not null then
324
325 begin
326 select segment_id into l_segment_id from qp_segments_b
327 where segment_code = x_segment_code
328 and prc_context_id = l_prc_context_id;
329
330 if p_detect_dup_mapping_cols( l_segment_id,
331 l_prc_context_id,
332 x_segment_mapping_column,
333 x_segment_code, 'Update') = -1 then
334 update qp_segments_b set
335 availability_in_basic = x_availability_in_basic,
336 application_id = x_application_id,
337 segment_mapping_column = x_segment_mapping_column,
338 seeded_flag = x_seeded_flag,
339 user_precedence = decode(seeded_precedence, user_precedence,
340 x_seeded_precedence, user_precedence),
341 seeded_precedence = x_seeded_precedence,
342 user_valueset_id = l_valueset_id,
343 seeded_valueset_id = l_valueset_id,
344 user_format_type = decode(seeded_format_type, user_format_type,
345 x_seeded_format_type, user_format_type),
346 required_flag = decode (last_updated_by, 1, x_required_flag, required_Flag),
347 seeded_format_type = x_seeded_format_type,
348 last_updated_by = l_user_id,
349 last_update_date = nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
350 last_update_login = 0
351 where segment_id = l_segment_id;
352
353 update qp_segments_tl set
354 user_segment_name = decode(seeded_segment_name, user_segment_name,
355 x_seeded_segment_name, user_segment_name),
356 seeded_segment_name = nvl(seeded_segment_name, x_seeded_segment_name),
357 user_description = decode(seeded_description,
358 user_description,
359 x_seeded_description
360 , user_description),
361 seeded_description = nvl(seeded_description, x_seeded_description),
362 last_update_date = nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
363 last_updated_by = l_user_id,
364 last_update_login = 0,
365 source_lang = userenv('LANG')
366 where segment_id = l_segment_id
367 and userenv('LANG') in (language, source_lang);
368 end if;
369 exception
370 when no_data_found then
371 select qp_segments_s.nextval
372 into l_segment_s
373 from dual;
374 begin
375 if p_detect_dup_mapping_cols( l_segment_id,
376 l_prc_context_id,
377 x_segment_mapping_column,
378 x_segment_code,'Add') = -1 then
379 insert into qp_segments_b (
380 segment_id,
381 segment_code,
382 prc_context_id,
383 availability_in_basic,
384 application_id,
385 segment_mapping_column,
386 seeded_flag,
387 seeded_precedence,
388 user_precedence,
389 seeded_valueset_id,
390 user_valueset_id,
391 seeded_format_type,
392 user_format_type,
393 required_flag,
394 creation_date,
395 created_by,
396 last_update_date,
397 last_update_login,
398 last_updated_by
399 ) values (
400 l_segment_s,
401 x_segment_code,
402 l_prc_context_id,
403 x_availability_in_basic,
404 x_application_id,
405 x_segment_mapping_column,
406 x_seeded_flag,
407 x_seeded_precedence,
408 x_seeded_precedence,
409 l_valueset_id,
410 l_valueset_id,
411 x_seeded_format_type,
412 x_seeded_format_type,
413 x_required_flag,
414 sysdate,
415 l_user_id,
416 nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
417 0,
418 l_user_id
419 );
420 commit;
421 ---
422 insert into qp_segments_tl (
423 segment_id,
424 seeded_segment_name,
425 user_segment_name,
426 seeded_description,
427 user_description,
428 source_lang,
429 language,
430 creation_date,
431 created_by,
432 last_update_date,
433 last_update_login,
434 last_updated_by
435 ) select
436 l_segment_s,
437 x_seeded_segment_name,
438 x_seeded_segment_name,
439 x_seeded_description,
440 x_seeded_description,
441 userenv('LANG'),
442 l.language_code,
443 sysdate,
444 l_user_id,
445 nvl(to_date(x_last_update_date,'YYYY/MM/DD'),sysdate),
446 0,
447 l_user_id
448 from fnd_languages l
449 where l.installed_flag in ('I', 'B') and
450 not exists (select null
451 from qp_segments_tl t
452 where t.segment_id = l_segment_id and
453 t.language = l.language_code);
454 commit;
455 end if;
456 exception
457 when others then
458 null;
459 end;
460 when others then
461 null;
462 end;
463
464 end if;
465
466 end qp_segments_load_row;
467
468 end qp_loader_dist_pub;