[Home] [Help]
PACKAGE BODY: APPS.PQH_GEN_FORM
Source
1 Package Body PQH_GEN_FORM as
2 /* $Header: pqgnfnf.pkb 120.3.12020000.2 2012/07/05 12:25:58 amnaraya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |-----------------------------< PQH_GEN_FORM >---------------------------------|
6 -- ----------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Created by : Sanej Nair (SCNair)
10 --
11 -- Description:
12 -- This handles internal Generic form support functionalities.
13 --
14 -- Access Status:
15 -- Internal Use Only.
16 --
17 -- {End Of Comments}
18 -- ----------------------------------------------------------------------------
19 -- Created by : Sanej Nair (SCNair)
20 -- Version Date Author Comment
21 -- -------+-----------+--------------+----------------------------------------
22 -- 115.0 27-Feb-2000 Sanej Nair Initial Version
23 -- ==========================================================================
24
25 g_package varchar2(80) := ' PQH_GEN_FORM.';
26 g_code varchar2(30) ;
27 g_meaning varchar2(240) ;
28 -- forllowing variables are for caching BG (context bg function usage)
29 g_private_bg_id number(15);
30 g_private_txn_id number(15);
31 --
32 -- Procedure to create source records / retrive number of records likey to be retrived
33 --
34 procedure create_source ( p_copy_entity_txn_id number
35 , p_delimiter varchar2
36 , p_count out nocopy number
37 , p_msg out nocopy varchar2
38 , p_copies number default 1
39 , p_row_count boolean ) is
40
41 -- local Variables
42 l_ins varchar2(32000);
43 l_sel varchar2(32000);
44 l_where varchar2(32000);
45 l_session_id fnd_sessions.session_id%type;
46 l_effective_date date;
47 --
48 -- Primary Cursor fetching txn related details
49 --
50 cursor c_txn is
51 select e.copy_entity_txn_id
52 , e.transaction_category_id
53 , e.txn_category_attribute_id
54 , e.context_business_group_id
55 , e.src_effective_date
56 , e.context -- application_id
57 , a.information_category
58 , replace(a.information1, '''','''''') information1
59 , replace(a.information2, '''','''''') information2
60 , replace(a.information3, '''','''''') information3
61 , replace(a.information4, '''','''''') information4
62 , replace(a.information5, '''','''''') information5
63 , replace(a.information6, '''','''''') information6
64 , replace(a.information7, '''','''''') information7
65 , replace(a.information8, '''','''''') information8
66 , replace(a.information9, '''','''''') information9
67 , replace(a.information10, '''','''''') information10
68 , replace(a.information11, '''','''''') information11
69 , replace(a.information12, '''','''''') information12
70 , replace(a.information13, '''','''''') information13
71 , replace(a.information14, '''','''''') information14
72 , replace(a.information15, '''','''''') information15
73 , replace(a.information16, '''','''''') information16
74 , replace(a.information17, '''','''''') information17
75 , replace(a.information18, '''','''''') information18
76 , replace(a.information19, '''','''''') information19
77 , replace(a.information20, '''','''''') information20
78 , replace(a.information21, '''','''''') information21
79 , replace(a.information22, '''','''''') information22
80 , replace(a.information23, '''','''''') information23
81 , replace(a.information24, '''','''''') information24
82 , replace(a.information25, '''','''''') information25
83 , replace(a.information26, '''','''''') information26
84 , replace(a.information27, '''','''''') information27
85 , replace(a.information28, '''','''''') information28
86 , replace(a.information29, '''','''''') information29
87 , replace(a.information30, '''','''''') information30
88 , t.from_clause
89 , t.where_clause
90 , c.master_table_route_id
91 from pqh_copy_entity_txns e
92 , pqh_copy_entity_attribs a
93 , pqh_transaction_categories c
94 , pqh_table_route t
95 where e.copy_entity_txn_id = a.copy_entity_txn_id
96 and a.row_type_cd = 'CRITERIA'
97 and c.transaction_category_id = e.transaction_category_id
98 and c.master_table_route_id = t.table_route_id
99 and e.copy_entity_txn_id = p_copy_entity_txn_id ;
100
101 --
102 -- Cursor to decode information based on inner cursor
103 --
104 cursor dec (column_name varchar2
105 ,ddf_column_name varchar2
106 ,information1 varchar2
107 ,information2 varchar2
108 ,information3 varchar2
109 ,information4 varchar2
110 ,information5 varchar2
111 ,information6 varchar2
112 ,information7 varchar2
113 ,information8 varchar2
114 ,information9 varchar2
115 ,information10 varchar2
116 ,information11 varchar2
117 ,information12 varchar2
118 ,information13 varchar2
119 ,information14 varchar2
120 ,information15 varchar2
121 ,information16 varchar2
122 ,information17 varchar2
123 ,information18 varchar2
124 ,information19 varchar2
125 ,information20 varchar2
126 ,information21 varchar2
127 ,information22 varchar2
128 ,information23 varchar2
129 ,information24 varchar2
130 ,information25 varchar2
131 ,information26 varchar2
132 ,information27 varchar2
133 ,information28 varchar2
134 ,information29 varchar2
135 ,information30 varchar2
136 ,p_delimiter varchar2 )
137 is
138 select decode(upper(ddf_column_name)
139 ,'INFORMATION1', decode(information1,null, column_name, ''''||information1 ||'''')
140 ,'INFORMATION2', decode(information2,null, column_name, ''''||information2 ||'''')
141 ,'INFORMATION3', decode(information3,null, column_name, ''''||information3 ||'''')
142 ,'INFORMATION4', decode(information4,null, column_name, ''''||information4 ||'''')
143 ,'INFORMATION5', decode(information5,null, column_name, ''''||information5 ||'''')
144 ,'INFORMATION6', decode(information6,null, column_name, ''''||information6 ||'''')
145 ,'INFORMATION7', decode(information7,null, column_name, ''''||information7 ||'''')
146 ,'INFORMATION8', decode(information8,null, column_name, ''''||information8 ||'''')
147 ,'INFORMATION9', decode(information9,null, column_name, ''''||information9 ||'''')
148 ,'INFORMATION10',decode(information10,null,column_name, ''''||information10||'''')
149 ,'INFORMATION11',decode(information11,null,column_name, ''''||information11||'''')
150 ,'INFORMATION12',decode(information12,null,column_name, ''''||information12||'''')
151 ,'INFORMATION13',decode(information13,null,column_name, ''''||information13||'''')
152 ,'INFORMATION14',decode(information14,null,column_name, ''''||information14||'''')
153 ,'INFORMATION15',decode(information15,null,column_name, ''''||information15||'''')
154 ,'INFORMATION16',decode(information16,null,column_name, ''''||information16||'''')
155 ,'INFORMATION17',decode(information17,null,column_name, ''''||information17||'''')
156 ,'INFORMATION18',decode(information18,null,column_name, ''''||information18||'''')
157 ,'INFORMATION19',decode(information19,null,column_name, ''''||information19||'''')
158 ,'INFORMATION20',decode(information20,null,column_name, ''''||information20||'''')
159 ,'INFORMATION21',decode(information21,null,column_name, ''''||information21||'''')
160 ,'INFORMATION22',decode(information22,null,column_name, ''''||information22||'''')
161 ,'INFORMATION23',decode(information23,null,column_name, ''''||information23||'''')
162 ,'INFORMATION24',decode(information24,null,column_name, ''''||information24||'''')
163 ,'INFORMATION25',decode(information25,null,column_name, ''''||information25||'''')
164 ,'INFORMATION26',decode(information26,null,column_name, ''''||information26||'''')
165 ,'INFORMATION27',decode(information27,null,column_name, ''''||information27||'''')
166 ,'INFORMATION28',decode(information28,null,column_name, ''''||information28||'''')
167 ,'INFORMATION29',decode(information29,null,column_name, ''''||information29||'''')
168 ,'INFORMATION30',decode(information30,null,column_name, ''''||information30||'''')
169 , column_name) info
170 from dual where rownum < 2;
171
172 --
173 -- cursor to fetch details on what is stored where and what goes where for the dynamic sql
174 --
175 cursor c_sat (p_attribute_type_cd varchar2
176 ,p_transaction_category_id number) is
177 select upper(a.column_name ) column_name
178 , a.column_type
179 , pqh_generic.get_alias(a.column_name) alias
180 , decode(column_type,'D', 'fnd_date.date_to_canonical('|| substr(a.column_name,1,instr(a.column_name||' ', ' ',1)-1) ||')'||' '||pqh_generic.get_alias(column_name)
181 , column_name ) col_dt_name
182 , a.width
183 , upper(s.ddf_column_name ) ddf_column_name
184 , upper(s.ddf_value_column_name ) ddf_value_column_name
185 , c.value_set_id
186 , s.context -- application_id a
187 from pqh_special_attributes s
188 , pqh_txn_category_attributes c
189 , pqh_attributes a
190 where s.txn_category_attribute_id = c.txn_category_attribute_id
191 and c.attribute_id = a.attribute_id
192 and c.transaction_category_id = p_transaction_category_id
193 and s.attribute_type_cd = p_attribute_type_cd
194 and s.context = pqh_gen_form.g_gbl_context
195 and c.select_flag = 'Y'
196 and a.enable_flag = 'Y' ;
197 --
198 cursor c_session is
199 select session_id, effective_date
200 from fnd_sessions
201 where session_id = userenv('sessionid')
202 for update of effective_date;
203 --
204 --internal function
205 --
206 function find_like_or_equal(p_string in varchar2) return varchar2 is
207 l_like varchar2(10) := ' like ';
208 l_equal varchar2(10) := '=';
209 begin
210 if instr(p_string, '%') > 0 or instr(p_string, '_') > 0 then
211 return l_like;
212 end if;
213 --
214 return l_equal;
215 exception when others then
216 return l_like ;
217 end find_like_or_equal;
218 --
219 begin
220 --
221 hr_utility.set_location(g_package||'create_source: Entering',1);
222 --
223 -- The primary cursor, this ideally would have just row with information pretaining to the txn
224 --
225 for rec in c_txn loop
226 -- set session_date as per user request
227 open c_session ;
228 fetch c_session into l_session_id, l_effective_date;
229 --
230 if c_session%notfound then
231 insert into fnd_sessions (session_id, effective_date)
232 values ( userenv('sessionid'), nvl(rec.src_effective_date, trunc(sysdate)));
233 else
234 update fnd_sessions
235 set effective_date = nvl(rec.src_effective_date,trunc(sysdate))
236 where current of c_session;
237 end if;
238 close c_session;
239 --
240 pqh_gen_form.populate_context(rec.copy_entity_txn_id);
241 --
242 if not(p_row_count) then
243
244 hr_utility.set_location(g_package||'create_source: inside c_txn',2);
245 --
246 -- l_ins would hold the string call to create source records on the results table
247 --
248 l_ins := 'pqh_copy_entity_results_api.create_copy_entity_result '||
249 '( '||
250 'p_copy_entity_result_id => l_var '||
251 ',p_object_version_number => l_var '||
252 ',p_result_type_cd => ''SOURCE'' '||
253 ',p_status => ''SRC_P'' '||
254 ',p_number_of_copies => '|| nvl(p_copies,1) ||
255 ',p_copy_entity_txn_id => '||p_copy_entity_txn_id ||
256 ',p_effective_date => trunc(sysdate)' ;
257
258 --
259 -- l_sel would hold the string call to select from the txn entity (eg position table, job table etc)
260 --
261 l_sel := 'Select 1 pqh_$$_unique' ;
262 --
263 -- cursor to pick the names of diplayable items on the form for given txn, responsibility etc.
264 --
265 for e_rec in c_sat('DISPLAY', rec.transaction_category_id) loop
266 hr_utility.set_location(g_package||'create_source: inside c_sat D',3);
267 if e_rec.context = pqh_gen_form.g_gbl_context then
268 if not( instr(l_ins, e_rec.ddf_column_name||'=>') <> 0 ) then
269 l_ins := l_ins ||
270 ',P_' ||e_rec.ddf_column_name||'=>i.'|| e_rec.alias;
271 end if; --instr(l_ins, e_rec.ddf_column_name) = 0
272 --
273 if e_rec.ddf_value_column_name is not null and e_rec.column_type = 'D' then
274 if not( instr(l_ins, e_rec.ddf_value_column_name||'=>') <> 0 ) then
275 l_ins := l_ins ||
276 ',P_' ||e_rec.ddf_value_column_name||'=>i.'||e_rec.alias ;
277 end if; --instr(l_ins, e_rec.ddf_column_name) = 0
278 end if; -- ddf_value_column_name + date
279 --
280 -- problem if a date column would have value set on it...may have to change the check below
281 --
282 if e_rec.ddf_value_column_name is not null and e_rec.column_type <> 'D' then
283 if not( instr(l_ins, e_rec.ddf_value_column_name||'=>') <> 0 ) then
284 l_ins := l_ins ||
285 ',P_' ||e_rec.ddf_value_column_name
286 ||' => rtrim(pqh_gen_form.get_value_from_id( i.'|| e_rec.alias
287 ||','''||e_rec.value_set_id||'''))';
288 end if; --instr(l_ins, e_rec.ddf_column_name) = 0
289 end if; -- ddf_value_column_name
290 --
291 if not( instr(l_sel, ' '||e_rec.column_name||' ') <> 0 ) then
292 l_sel := l_sel ||', '||e_rec.col_dt_name||' ' ;
293 end if; -- instr(l_ins, e_rec.col_dt_name) = 0
294 end if; -- Display
295 end loop ; -- c_sat display
296 --
297 -- cursor to pick the names of primary key items for given txn.
298 --
299 for e_rec in c_sat('PRIMARY_KEY', rec.transaction_category_id) loop
300 hr_utility.set_location(g_package||'create_source: inside c_sat P',3);
301 if e_rec.context = pqh_gen_form.g_gbl_context then
302 if not( instr(l_ins, e_rec.ddf_column_name||'=>') <> 0 ) then
303 l_ins := l_ins ||
304 ',P_' ||e_rec.ddf_column_name||'=>i.'|| e_rec.alias;
305 end if; --instr(l_ins, e_rec.ddf_column_name) = 0
306
307 if not( instr(l_sel, ' '||e_rec.col_dt_name||' ') <> 0 ) then
308 l_sel := l_sel ||', '||e_rec.col_dt_name||' ' ;
309 end if; -- instr(l_ins, e_rec.col_dt_name) = 0
310 end if; -- Primary Key
311 end loop ; -- c_sat primary_key
312 -- HIDDEN column addition
313 for e_rec in c_sat('HIDDEN', rec.transaction_category_id) loop
314 hr_utility.set_location(g_package||'create_source: inside c_sat H',31);
315 if e_rec.context = pqh_gen_form.g_gbl_context then
316 if not( instr(l_ins, e_rec.ddf_column_name||'=>') <> 0 ) then
317 l_ins := l_ins ||
318 ',P_' ||e_rec.ddf_column_name||'=>i.'|| e_rec.alias;
319 end if; --instr(l_ins, e_rec.ddf_column_name) = 0
320
321 if not( instr(l_sel, ' '||e_rec.col_dt_name||' ') <> 0 ) then
322 l_sel := l_sel ||', '||e_rec.col_dt_name||' ' ;
323 end if; -- instr(l_ins, e_rec.col_dt_name) = 0
324 end if; --
325 end loop ; -- c_sat HIDDEN column
326 -- SEGMENTs
327 for e_rec in c_sat('SEGMENT', rec.transaction_category_id) loop
328 hr_utility.set_location(g_package||'create_source: inside c_sat S',32);
329 if e_rec.context = pqh_gen_form.g_gbl_context then
330 if not( instr(l_ins, e_rec.ddf_column_name||'=>') <> 0 ) then
331 l_ins := l_ins ||
332 ',P_' ||e_rec.ddf_column_name||'=>i.'|| e_rec.alias;
333 end if; --instr(l_ins, e_rec.ddf_column_name) = 0
334
335 if not( instr(l_sel, ' '||e_rec.col_dt_name||' ') <> 0 ) then
336 l_sel := l_sel ||', '||e_rec.col_dt_name||' ' ;
337 end if; -- instr(l_ins, e_rec.col_dt_name) = 0
338 end if; -- Segments
339 end loop ; -- c_sat segments
340
341 end if; -- p_row_count
342 --
343 -- Building where condition with information entered by the user as criteria, l_where hold where string
344 --
345 pqh_refresh_data.g_refresh_tab.delete;
346
347 if rec.context_business_group_id is not null then
348 pqh_refresh_data.g_refresh_tab(1).column_name := 'BUSINESS_GROUP_ID';
349 pqh_refresh_data.g_refresh_tab(1).column_type := 'N';
350 pqh_refresh_data.g_refresh_tab(1).txn_val := rec.context_business_group_id ;
351 pqh_refresh_data.g_refresh_tab(1).shadow_val := rec.context_business_group_id ;
352 pqh_refresh_data.g_refresh_tab(1).main_val := rec.context_business_group_id ;
353 pqh_refresh_data.g_refresh_tab(1).refresh_flag := 'N';
354 pqh_refresh_data.g_refresh_tab(1).updt_flag := 'N';
355 end if;
356
357 pqh_refresh_data.replace_where_params(rec.where_clause,'N','',l_where);
358 --;
359 l_where := ' Where '||nvl(l_where, '1=1')||' ';
360 --
361 for e_rec in c_sat('CRITERIA', rec.transaction_category_id) loop
362 if e_rec.context = pqh_gen_form.g_gbl_context then
363 --
364 hr_utility.set_location(g_package||'create_source: inside c_sat 2',4);
365 for i in dec (e_rec.column_name, e_rec.ddf_column_name, rec.information1
366 ,rec.information2, rec.information3, rec.information4
367 ,rec.information5, rec.information6, rec.information7
368 ,rec.information8, rec.information9, rec.information10
369 ,rec.information11, rec.information12, rec.information13
370 ,rec.information14, rec.information15, rec.information16
371 ,rec.information17, rec.information18, rec.information19
372 ,rec.information20, rec.information21, rec.information22
373 ,rec.information23, rec.information24, rec.information25
374 ,rec.information26, rec.information27, rec.information28
375 ,rec.information29, rec.information30, p_delimiter) loop
376 hr_utility.set_location(g_package||'create_source: inside c_dec ',5);
377 --
378 -- COLUMN_NAME like COLUMN_NAME fails if the value is null and hence decided to form where
379 -- on if there is a value (SCNair 05/25/00 UK)
380 --
381 if e_rec.column_name <> i.info then
382 if e_rec.column_type = 'D' then
383 l_where := l_where || ' and ' || e_rec.column_name ||
384 find_like_or_equal(i.info) ||'fnd_date.canonical_to_date('||i.info||')' ;
385 else
386 l_where := l_where || ' and ' || e_rec.column_name ||find_like_or_equal(i.info)||i.info ;
387 end if;
388 end if;
389 end loop ; --dec
390 --
391 end if; -- for global context of txn_short_name
392 end loop ; -- c_sat changeable
393 hr_utility.set_location(g_package||'create_source: after c_dec ',6);
394 hr_utility.trace('BEGIN l_where PQH_GEN_FORM');
395 hr_utility.trace(substr(l_where,1,2000));
396 hr_utility.trace(substr(l_where,2001,2000));
397 hr_utility.trace(substr(l_where,4001,2000));
398 hr_utility.trace(substr(l_where,6001,2000));
399 hr_utility.trace(substr(l_where,8001,2000));
400 hr_utility.trace(substr(l_where,10001,2000));
401 hr_utility.trace(substr(l_where,12001,2000));
402 hr_utility.trace(substr(l_where,14001,2000));
403 hr_utility.trace(substr(l_where,16001,2000));
404 hr_utility.trace(substr(l_where,18001,2000));
405 hr_utility.trace(substr(l_where,20001,2000));
406 hr_utility.trace(substr(l_where,22001,2000));
407 hr_utility.trace(substr(l_where,24001,2000));
408 hr_utility.trace(substr(l_where,26001,2000));
409 hr_utility.trace(substr(l_where,28001,2000));
410 hr_utility.trace(substr(l_where,30001,2000));
411 hr_utility.trace('END l_where PQH_GEN_FORM');
412 --
413 -- Replace where clause
414 --
415 if p_row_count then
416 g_count := '';
417 g_msg := '';
418 hr_utility.set_location(g_package||'create_source: before count ',7);
419 execute immediate 'begin select count(*) into pqh_gen_form.g_count from '||rec.from_clause ||l_where ||';'||
420 ' exception when no_data_found then pqh_gen_form.g_count := 0;'||
421 ' when others then pqh_gen_form.g_msg := sqlerrm; end; ';
422 p_count := g_count;
423 p_msg := g_msg ;
424 else
425 l_ins := l_ins || ');';
426 hr_utility.set_location(g_package||'create_source: after l_ins ',7);
427 l_sel := l_sel || ' from ' || rec.from_clause ||l_where||';' ;
428 hr_utility.set_location(g_package||'create_source: after l_sel ',8);
429 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
430 hr_utility.trace('BEGIN l_SEL PQH_GEN_FORM');
431 hr_utility.trace(substr(l_sel,1,2000));
432 hr_utility.trace(substr(l_sel,2001,2000));
433 hr_utility.trace(substr(l_sel,4001,2000));
434 hr_utility.trace(substr(l_sel,6001,2000));
435 hr_utility.trace(substr(l_sel,8001,2000));
436 hr_utility.trace(substr(l_sel,10001,2000));
437 hr_utility.trace(substr(l_sel,12001,2000));
438 hr_utility.trace(substr(l_sel,14001,2000));
439 hr_utility.trace(substr(l_sel,16001,2000));
440 hr_utility.trace(substr(l_sel,18001,2000));
441 hr_utility.trace(substr(l_sel,20001,2000));
442 hr_utility.trace(substr(l_sel,22001,2000));
443 hr_utility.trace(substr(l_sel,24001,2000));
444 hr_utility.trace(substr(l_sel,26001,2000));
445 hr_utility.trace(substr(l_sel,28001,2000));
446 hr_utility.trace(substr(l_sel,30001,2000));
447 hr_utility.trace(substr(l_sel,32001,2000));
448 hr_utility.trace('END l_SEL PQH_GEN_FORM');
449 --
450 hr_utility.trace('BEGIN l_INS PQH_GEN_FORM');
451 hr_utility.trace(substr(l_ins,1,2000));
452 hr_utility.trace(substr(l_ins,2001,2000));
453 hr_utility.trace(substr(l_ins,4001,2000));
454 hr_utility.trace(substr(l_ins,6001,2000));
455 hr_utility.trace(substr(l_ins,8001,2000));
456 hr_utility.trace(substr(l_ins,10001,2000));
457 hr_utility.trace(substr(l_ins,12001,2000));
458 hr_utility.trace(substr(l_ins,14001,2000));
459 hr_utility.trace(substr(l_ins,16001,2000));
460 hr_utility.trace(substr(l_ins,18001,2000));
461 hr_utility.trace(substr(l_ins,20001,2000));
462 hr_utility.trace(substr(l_ins,22001,2000));
463 hr_utility.trace(substr(l_ins,24001,2000));
464 hr_utility.trace(substr(l_ins,26001,2000));
465 hr_utility.trace(substr(l_ins,28001,2000));
466 hr_utility.trace(substr(l_ins,30001,2000));
467 hr_utility.trace(substr(l_ins,32001,2000));
468 hr_utility.trace('END l_INS PQH_GEN_FORM');
469 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
470
471 execute immediate 'declare l_var number; cursor c_a is '|| l_sel||' begin for i in c_a loop '
472 ||l_ins||' end loop; end;';
473
474 hr_utility.set_location(g_package||'create_source: after execute of ins api',9);
475 end if; -- p_row_count
476 end loop ; -- c_txn
477
478 hr_utility.set_location(g_package||'create_source: Leaving',1);
479 exception when others then
480 p_count := null;
481 p_msg := null;
482 raise;
483 end create_source;
484 --
485 -- Over load ; to be called to perform source create
486 --
487 procedure create_source ( p_copy_entity_txn_id number
488 , p_delimiter varchar2
489 , p_copies number default 1
490 , p_msg out nocopy varchar2) is
491 --
492 -- local variable
493 --
494 l_count number;
495 l_row_count boolean := false;
496 begin
497 create_source ( p_copy_entity_txn_id => p_copy_entity_txn_id
498 , p_delimiter => p_delimiter
499 , p_count => l_count
500 , p_msg => p_msg
501 , p_copies => p_copies
502 , p_row_count => l_row_count );
503 end;
504 --
505 -- To be called to perform source count
506 --
507 procedure count_source ( p_copy_entity_txn_id number
508 , p_delimiter varchar2
509 , p_count out nocopy number
510 , p_msg out nocopy varchar2 ) is
511 --
512 -- local variable
513 --
514 l_count number;
515 l_row_count boolean := true;
516 begin
517 create_source ( p_copy_entity_txn_id => p_copy_entity_txn_id
518 , p_delimiter => p_delimiter
519 , p_count => p_count
520 , p_msg => p_msg
521 , p_row_count => l_row_count );
522 end;
523 --
524 procedure recount_source ( p_copy_entity_txn_id number
525 , p_delimiter varchar2
526 , p_count out nocopy number
527 , p_msg out nocopy varchar2 ) is
528 --
529 -- local variable
530 --
531 l_count number;
532 l_row_count boolean := true;
533 begin
534 --
535 delete from pqh_copy_entity_results
536 where copy_entity_txn_id = p_copy_entity_txn_id;
537 --
538 create_source ( p_copy_entity_txn_id => p_copy_entity_txn_id
539 , p_delimiter => p_delimiter
540 , p_count => p_count
541 , p_msg => p_msg
542 , p_row_count => l_row_count );
543 end;
544 --
545 procedure update_source (p_copy_entity_result_id number
546 , p_count number
547 , p_object_version_number in out nocopy number ) is
548
549 begin
550 pqh_copy_entity_results_api.update_copy_entity_result
551 ( p_validate => false
552 , p_copy_entity_result_id => p_copy_entity_result_id
553 , p_number_of_copies => p_count
554 , p_object_version_number => p_object_version_number
555 , p_effective_date => trunc(sysdate)
556 , p_long_attribute1 => null
557 ) ;
558
559 end update_source;
560 --
561 Procedure create_target ( p_copy_entity_txn_id number
562 , p_ld1 varchar2 --(ld => flex delimter)
563 , p_lf1 varchar2 default null --(lf => flex code)
564 , p_ln1 varchar2 default null --(ln => flex numb)
565 , p_ld2 varchar2 default null
566 , p_lf2 varchar2 default null
567 , p_ln2 varchar2 default null
568 , p_batch_status out nocopy varchar2 ) is
569 --
570 l_txn_category_attribute_id pqh_special_attributes.txn_category_attribute_id%type;
571 l_replacement_type_cd pqh_copy_entity_txns.replacement_type_cd%type ;
572 l_start_with pqh_copy_entity_txns.start_with%type;
573 l_increment_by pqh_copy_entity_txns.increment_by%type;
574 l_mast_trt_id pqh_transaction_categories.master_table_route_id%type;
575 l_transaction_id pqh_transaction_categories.transaction_category_id%type;
576 l_warn number(15);
577 l_copies number(15);
578 l_copy_entity_result_id number(15);
579 l_ovn number(15);
580 --
581 cursor c_txn is
582 select cet.txn_category_attribute_id
583 , cet.replacement_type_cd
584 , cet.start_with
585 , cet.increment_by
586 , cet.transaction_category_id
587 , tct.master_table_route_id
588 , cet.context context -- application_id
589 from pqh_copy_entity_txns cet
590 , pqh_transaction_categories tct
591 where copy_entity_txn_id = p_copy_entity_txn_id
592 and tct.transaction_category_id = cet.transaction_category_id;
593 --
594 cursor c_change is
595 select copy_entity_attrib_id
596 , information1
597 , information2
598 , information3
599 , information4
600 , information5
601 , information6
602 , information7
603 , information8
604 , information9
605 , information10
606 , information11
607 , information12
608 , information13
609 , information14
610 , information15
611 , information16
612 , information17
613 , information18
614 , information19
615 , information20
616 , information21
617 , information22
618 , information23
619 , information24
620 , information25
621 , information26
622 , information27
623 , information28
624 , information29
625 , information30
626 from pqh_copy_entity_attribs
627 where copy_entity_txn_id = p_copy_entity_txn_id
628 and row_type_cd = 'CHANGEABLE' ;
629 --
630 cursor c_ch_kf (p_flex in varchar2) is
631 select copy_entity_attrib_id
632 , row_type_cd
633 , information_category
634 , information1
635 , information2
636 , information3
637 , information4
638 , information5
639 , information6
640 , information7
641 , information8
642 , information9
643 , information10
644 , information11
645 , information12
646 , information13
647 , information14
648 , information15
649 , information16
650 , information17
651 , information18
652 , information19
653 , information20
654 , information21
655 , information22
656 , information23
657 , information24
658 , information25
659 , information26
660 , information27
661 , information28
662 , information29
663 , information30
664 from pqh_copy_entity_attribs
665 where copy_entity_txn_id = p_copy_entity_txn_id
666 and information_category = p_flex;
667 --
668 cursor c_candidate is
669 select copy_entity_result_id
670 , number_of_copies copies
671 , information1
672 , information2
673 , information3
674 , information4
675 , information5
676 , information6
677 , information7
678 , information8
679 , information9
680 , information10
681 , information11
682 , information12
683 , information13
684 , information14
685 , information15
686 , information16
687 , information17
688 , information18
689 , information19
690 , information20
691 , information21
692 , information22
693 , information23
694 , information24
695 , information25
696 , information26
697 , information27
698 , information28
699 , information29
700 , information30
701 , information31
702 , information32
703 , information33
704 , information34
705 , information35
706 , information36
707 , information37
708 , information38
709 , information39
710 , information40
711 , information41
712 , information42
713 , information43
714 , information44
715 , information45
716 , information46
717 , information47
718 , information48
719 , information49
720 , information50
721 , information51
722 , information52
723 , information53
724 , information54
725 , information55
726 , information56
727 , information57
728 , information58
729 , information59
730 , information60
731 , information61
732 , information62
733 , information63
734 , information64
735 , information65
736 , information66
737 , information67
738 , information68
739 , information69
740 , information70
741 , information71
742 , information72
743 , information73
744 , information74
745 , information75
746 , information76
747 , information77
748 , information78
749 , information79
750 , information80
751 , information81
752 , information82
753 , information83
754 , information84
755 , information85
756 , information86
757 , information87
758 , information88
759 , information89
760 , information90,
761 information91 ,
762 information92 ,
763 information93 ,
764 information94 ,
765 information95 ,
766 information96 ,
767 information97 ,
768 information98 ,
769 information99 ,
770 information100 ,
771 information101 ,
772 information102 ,
773 information103 ,
774 information104 ,
775 information105 ,
776 information106 ,
777 information107 ,
778 information108 ,
779 information109 ,
780 information110 ,
781 information111 ,
782 information112 ,
783 information113 ,
784 information114 ,
785 information115 ,
786 information116 ,
787 information117 ,
788 information118 ,
789 information119 ,
790 information120 ,
791 information121 ,
792 information122 ,
793 information123 ,
794 information124 ,
795 information125 ,
796 information126 ,
797 information127 ,
798 information128 ,
799 information129 ,
800 information130 ,
801 information131 ,
802 information132 ,
803 information133 ,
804 information134 ,
805 information135 ,
806 information136 ,
807 information137 ,
808 information138 ,
809 information139 ,
810 information140 ,
811 information141 ,
812 information142 ,
813 information143 ,
814 information144 ,
815 information145 ,
816 information146 ,
817 information147 ,
818 information148 ,
819 information149 ,
820 information150 ,
821 information151 ,
822 information152 ,
823 information153 ,
824 information154 ,
825 information155 ,
826 information156 ,
827 information157 ,
828 information158 ,
829 information159 ,
830 information160 ,
831 information161 ,
832 information162 ,
833 information163 ,
834 information164 ,
835 information165 ,
836 information166 ,
837 information167 ,
838 information168 ,
839 information169 ,
840 information170 ,
841 information171 ,
842 information172 ,
843 information173 ,
844 information174 ,
845 information175 ,
846 information176 ,
847 information177 ,
848 information178 ,
849 information179 ,
850 information180
851 , object_version_number
852 from pqh_copy_entity_results
853 where copy_entity_txn_id = p_copy_entity_txn_id
854 and number_of_copies <> 0
855 and result_type_cd = 'SOURCE'
856 for update of copy_entity_result_id;
857 --
858 cursor c_attrib(v_table_route_id in number) is
859 select upper(pqh_generic.get_alias(att.column_name)) column_alias,
860 upper(sat.ddf_column_name) ddf_column_name,
861 upper(sat1.ddf_column_name) changeable_column_name,
862 sat.context context, -- application_id
863 sat1.context context_s1 -- application_id
864 from pqh_special_attributes sat,
865 pqh_txn_category_attributes tca,
866 pqh_transaction_categories tct,
867 pqh_attributes att,
868 pqh_special_attributes sat1
869 where sat.txn_category_attribute_id = tca.txn_category_attribute_id
870 and tca.attribute_id = att.attribute_id
871 and tct.master_table_route_id = v_table_route_id
872 and tct.transaction_category_id = tca.transaction_category_id
873 and sat.txn_category_attribute_id = sat1.txn_category_attribute_id
874 and sat.context = sat1.context
875 and sat.context = pqh_gen_form.g_gbl_context
876 and tca.select_flag = 'Y'
877 and att.enable_flag = 'Y'
878 and sat.attribute_type_cd in ('DISPLAY')
879 and sat1.attribute_type_cd = 'CHANGEABLE'
880 and sat.txn_category_attribute_id not in
881 (select txn_category_attribute_id from pqh_special_attributes
882 where context = pqh_gen_form.g_gbl_context
883 and attribute_type_cd = 'KEY_FLEX');
884 --
885 cursor c_id (p_transaction_category_id in number) is
886 select upper(s.ddf_column_name ) ddf_column_name
887 , upper(s.ddf_value_column_name ) ddf_value_column_name
888 , c.value_set_id
889 , s.context context -- application_id
890 from pqh_special_attributes s
891 , pqh_txn_category_attributes c
892 where s.txn_category_attribute_id = c.txn_category_attribute_id
893 and c.transaction_category_id = p_transaction_category_id
894 and s.ddf_column_name <> 'INFORMATION1'
895 and s.attribute_type_cd = 'DISPLAY'
896 and s.context = pqh_gen_form.g_gbl_context
897 and c.value_set_id is not null
898 and s.ddf_value_column_name is not null ;
899 --
900 procedure apply_changes
901 is
902 l_changeable_column_name varchar2(80);
903 begin
904 hr_utility.set_location(g_package||'Create_target>>apply_changes: Entering',1);
905 hr_utility.set_location('Infor 1 '||pqh_gen_form.g_information1,1);
906 hr_utility.set_location('Infor 3 '||pqh_gen_form.g_information3,1);
907 hr_utility.set_location('Infor 4 '||pqh_gen_form.g_information4,1);
908 hr_utility.set_location('C Infor 2 '||pqh_gen_form.c_information2,1);
909 for k in 1..180 loop
910 for j in nvl(PQH_GEN_FORM.g_attrib_tab.first,0)..nvl(PQH_GEN_FORM.g_attrib_tab.last,-1) loop
911 begin
912 if pqh_gen_form.g_attrib_tab(j).ddf_column_name = 'INFORMATION'||to_char(k) then
913 l_changeable_column_name := pqh_gen_form.g_attrib_tab(j).changeable_column_name ;
914 hr_utility.set_location(l_changeable_column_name||'->'||pqh_gen_form.g_attrib_tab(j).ddf_column_name,111);
915 --
916 -- Bug 5249887: dynamic select into has been failing for some unknown reason; alternative assignment seems to work.
917 -- execute immediate 'begin select nvl(pqh_gen_form.c_'||l_changeable_column_name
918 -- ||',pqh_gen_form.g_'||pqh_gen_form.g_attrib_tab(j).ddf_column_name ||') into '
919 -- || 'pqh_gen_form.g_'||pqh_gen_form.g_attrib_tab(j).ddf_column_name
920 -- || ' from fnd_dual where rownum < 2; end;';
921 execute immediate 'begin pqh_gen_form.g_'||pqh_gen_form.g_attrib_tab(j).ddf_column_name
922 ||':= nvl(pqh_gen_form.c_'||l_changeable_column_name
923 ||',pqh_gen_form.g_'||pqh_gen_form.g_attrib_tab(j).ddf_column_name ||'); end;';
924
925 hr_utility.set_location( nvl(pqh_gen_form.c_information1,'Info 1')||'='
926 ||pqh_gen_form.g_attrib_tab(j).ddf_column_name ,112);
927 exit;
928 end if;
929 exception when no_data_found then
930 hr_utility.set_location('no data on-'||j,113);
931 hr_utility.trace(substr(sqlerrm,1,255));
932 hr_utility.trace( 'begin pqh_gen_form.g_'||pqh_gen_form.g_attrib_tab(j).ddf_column_name
933 ||':= nvl(pqh_gen_form.c_'||l_changeable_column_name
934 ||',pqh_gen_form.g_'||pqh_gen_form.g_attrib_tab(j).ddf_column_name ||'); end;');
935 end;
936 end loop; -- g_attrib_tab scan
937 end loop; -- source information scan
938 hr_utility.set_location('Infor 1 '||pqh_gen_form.g_information1,1);
939 hr_utility.set_location('Infor 3 '||pqh_gen_form.g_information3,1);
940 hr_utility.set_location('Infor 4 '||pqh_gen_form.g_information4,1);
941 hr_utility.set_location('C Infor 2 '||pqh_gen_form.c_information2,1);
942 --l_changeable_column_name := pqh_gen_form.g_attrib_tab(1000).changeable_column_name ;
943 hr_utility.set_location(g_package||'Create_target>>apply_changes: Leaving',1);
944 end apply_changes ;
945 --
946 procedure apply_kf (p_flex in varchar2, p_ite in varchar2)
947 is
948 l_str varchar2(4000);
949 --
950 cursor c_sat is
951 select sat.ddf_column_name ddf_column_name,
952 to_number(substr(column_name,instr(upper(column_name),'SEGMENT')+7,2)) nos
953 from pqh_special_attributes sat,
954 pqh_txn_category_attributes tca,
955 pqh_attributes att
956 where sat.txn_category_attribute_id = tca.txn_category_attribute_id
957 and tca.attribute_id = att.attribute_id
958 and tca.select_flag = 'Y'
959 and att.enable_flag = 'Y'
960 and sat.attribute_type_cd = 'SEGMENT'
961 and sat.flex_code = p_flex
962 and sat.context = pqh_gen_form.g_gbl_context
963 order by to_number(substr(column_name,instr(column_name,'SEGMENT')+7,2)) ;
964 begin
965 hr_utility.set_location(g_package||'create_target>>apply_kf: Entering',1);
966 --
967 for i in c_sat loop
968 l_str := l_str ||'pqh_gen_form.g_'||i.ddf_column_name ||' := nvl(pqh_gen_form.k'||p_ite||'_information'||i.nos
969 ||',pqh_gen_form.g_'||i.ddf_column_name ||');';
970 end loop;
971
972 if l_str is not null then
973 l_str := 'Begin '||l_str||' end;';
974 hr_utility.trace(substr(l_str,1,255));
975 hr_utility.trace(substr(l_str,256,255));
976 hr_utility.trace(substr(l_str,511,255));
977 hr_utility.trace(substr(l_str,766,255));
978 hr_utility.trace(substr(l_str,1021,255));
979 hr_utility.trace(substr(l_str,1276,255));
980 hr_utility.trace(substr(l_str,1531,255));
981 hr_utility.trace(substr(l_str,1786,255));
982 hr_utility.trace(substr(l_str,2041,255));
983 hr_utility.trace(substr(l_str,2296,255));
984 hr_utility.trace(substr(l_str,2551,255));
985 hr_utility.trace(substr(l_str,2806,255));
986 hr_utility.trace(substr(l_str,3061,255));
987 hr_utility.trace(substr(l_str,3316,255));
988 hr_utility.trace(substr(l_str,3571,255));
989 hr_utility.trace(substr(l_str,3826,255));
990 execute immediate l_str ;
991 end if;
992
993 hr_utility.set_location(g_package||'create_target>>apply_kf: Leaving',1);
994 end apply_kf;
995 --
996 procedure apply_special_attrib ( p_txn_category_attribute_id in number
997 , p_index in number
998 , p_replacement_type_cd in varchar2
999 , p_start_with in varchar2
1000 , p_increment_by in varchar2) is
1001
1002 -- , p_lf1 in varchar2
1003 -- , p_lf2 in varchar2
1004 -- , p_ln1 in varchar2
1005 -- , p_ln2 in varchar2) is
1006
1007 --
1008 l_val varchar2(2000);
1009 l_cat_value varchar2(2000);
1010 l_str varchar2(32000);
1011 l_id_from_value varchar2(200);
1012 l_fd varchar2(10);
1013 l_fn varchar2(10);
1014 l_appl_id number(10);
1015 l_col varchar2(100);
1016 l_number number(10);
1017 l_start_with varchar2(1000) := replace(p_start_with,'''','''''');
1018
1019 cursor c_sat is
1020 select upper(st.ddf_column_name) ddf_column_name
1021 , nvl(upper(st.ddf_value_column_name), upper(st.ddf_column_name)) col_name
1022 , tc.value_set_id
1023 , upper(st.ddf_value_column_name) ddf_value_column_name
1024 , st.attribute_type_cd, st.flex_code
1025 , pqh_gen_form.get_segment(ata.column_name) segment
1026 from pqh_special_attributes st,
1027 pqh_txn_category_attributes tc,
1028 pqh_attributes ata
1029 where st.txn_category_attribute_id = tc.txn_category_attribute_id
1030 and ata.attribute_id = tc.attribute_id
1031 and st.attribute_type_cd in ('SEGMENT','DISPLAY')
1032 and st.context = pqh_gen_form.g_gbl_context
1033 and tc.select_flag = 'Y'
1034 and ata.enable_flag = 'Y'
1035 and st.txn_category_attribute_id in
1036 (select sat.txn_category_attribute_id
1037 from pqh_special_attributes sat
1038 where sat.context = pqh_gen_form.g_gbl_context
1039 and sat.attribute_type_cd = 'CHANGEABLE'
1040 and sat.txn_category_attribute_id = p_txn_category_attribute_id ); -- special_attribute
1041 --
1042 cursor c_kf_seg (v_id_fc in varchar2, v_id_fn in varchar2, v_appl_id in number)is
1043 select application_column_name, segment_num, seg_no, rownum from (
1044 select application_column_name,
1045 segment_num,
1046 to_number(substr(application_column_name, instr(upper(application_column_name),'SEGMENT')+7)) seg_no
1047 from fnd_id_flex_segments
1048 where id_flex_code = v_id_fc
1049 and id_flex_num = v_id_fn
1050 and application_id = v_appl_id
1051 and enabled_flag = 'Y'
1052 and display_flag = 'Y'
1053 order by segment_num);
1054 --
1055 cursor c_col (p_flex in varchar2) is
1056 select ddf_column_name from pqh_special_attributes
1057 where flex_code = p_flex
1058 and context = pqh_gen_form.g_gbl_context
1059 and attribute_type_cd = 'KEY_FLEX' ;
1060 begin
1061 hr_utility.set_location(g_package||'create_target>>apply_special_attribs: Entering',1);
1062 for rec in c_sat loop
1063 if p_index = 1 then
1064 execute immediate 'begin pqh_gen_form.g_initial_value := replace(pqh_gen_form.g_'||rec.col_name|| ','''''''','''''''''''') ; end;';
1065 end if;
1066 --
1067 begin
1068 if to_number(l_start_with) = 0 or true then
1069 l_val := nvl(l_start_with, 0) +(p_index-1)* nvl(p_increment_by,0) ;
1070 end if;
1071 exception
1072 when value_error then
1073 l_val := l_start_with ||to_char((p_index-1)* p_increment_by) ;
1074 end ;
1075 --
1076 if p_replacement_type_cd = 'REPLACE' then
1077 l_cat_value := nvl(l_val, l_start_with);
1078 elsif p_replacement_type_cd = 'PREFIX' then
1079 l_cat_value := l_val || g_initial_value ;
1080 elsif p_replacement_type_cd = 'SUFFIX' then
1081 l_cat_value := g_initial_value || l_val ;
1082 else
1083 return; -- there is nothing to be done
1084 end if;
1085 --
1086 if rec.ddf_value_column_name is not null and
1087 rec.value_set_id is not null then
1088 l_id_from_value := pqh_gen_form.get_id_from_value(p_value => l_cat_value
1089 ,p_vset_id => rec.value_set_id);
1090 if l_cat_value is not null and
1091 l_id_from_value is not null then
1092 execute immediate 'begin pqh_gen_form.g_'||rec.ddf_column_name ||' :='''|| l_id_from_value||''';'||
1093 'pqh_gen_form.g_'||rec.ddf_value_column_name ||' :='''|| l_cat_value||'''; end;';
1094 else
1095 execute immediate 'begin pqh_gen_form.g_'||rec.ddf_value_column_name||' :='''||g_initial_value||'''; end;';
1096 l_warn := '0';
1097 end if;
1098 elsif rec.ddf_value_column_name is null and
1099 l_cat_value is not null then
1100 execute immediate 'begin pqh_gen_form.g_'||rec.ddf_column_name ||' :='''|| l_cat_value||'''; end;';
1101 end if;
1102 --
1103 if rec.attribute_type_cd = 'SEGMENT' then
1104 hr_utility.set_location(g_package||'create_target>>apply_special_attribs: Kf segment',5);
1105
1106 if rec.flex_code = p_lf1 then
1107 l_fn := p_ln1;
1108 l_fd := p_ld1;
1109 elsif rec.flex_code = p_lf2 then
1110 l_fn := p_ln2;
1111 l_fd := p_ld2;
1112 end if;
1113 if rec.flex_code in ('COST', 'GRP') then
1114 l_appl_id := 801 ;
1115 else
1116 l_appl_id := 800 ;
1117 end if;
1118 --
1119 for a in c_kf_seg(rec.flex_code, l_fn, l_appl_id) loop
1120 if a.application_column_name = rec.segment then
1121 l_number := a.rownum ;
1122 exit;
1123 end if;
1124 end loop;
1125 --
1126 for a in c_col(rec.flex_code) loop
1127 l_col := a.ddf_column_name ;
1128 end loop;
1129 --
1130
1131 l_str:= 'a := fnd_flex_ext.breakup_segments (pqh_gen_form.g_'||l_col||','''||l_fd||''', l_ac_seg); '
1132 ||'for i in 1..greatest(a,'||to_char(l_number)||') loop '
1133 ||'l_ch_seg(i) := null; end loop;'
1134 ||'for i in 1..a loop l_ch_seg(i) := l_ac_seg(i); end loop;'
1135 ||' hr_utility.set_location(''dyn ac:''|| pqh_gen_form.g_'||l_col||',2001);'
1136 ||'l_ch_seg('||to_char(l_number)||') := '''||l_cat_value||'''; '
1137 ||'l_val := fnd_flex_ext.concatenate_segments(greatest(a,'||to_char(l_number)
1138 ||'), l_ch_seg,'''||l_fd||'''); '
1139 ||'pqh_gen_form.g_'||l_col||' := l_val; ';
1140 --
1141 l_str:= 'declare j number := 0; l_a varchar2(300); a number; l_val varchar2(2000);'
1142 ||' l_ac_seg FND_FLEX_EXT.SegmentArray ; l_ch_seg FND_FLEX_EXT.SegmentArray; begin '||l_str||' end;';
1143 hr_utility.trace(substr(l_str,1,255));
1144 hr_utility.trace(substr(l_str,256,255));
1145 hr_utility.trace(substr(l_str,511,255));
1146 hr_utility.trace(substr(l_str,766,255));
1147 hr_utility.trace(substr(l_str,1021,255));
1148 hr_utility.trace(substr(l_str,1276,255));
1149 hr_utility.trace(substr(l_str,1531,255));
1150 hr_utility.trace(substr(l_str,1786,255));
1151 hr_utility.trace(substr(l_str,2041,255));
1152 hr_utility.trace(substr(l_str,2296,255));
1153 hr_utility.trace(substr(l_str,2551,255));
1154 hr_utility.trace(substr(l_str,2806,255));
1155 hr_utility.trace(substr(l_str,3061,255));
1156 hr_utility.trace(substr(l_str,3316,255));
1157 hr_utility.trace(substr(l_str,3571,255));
1158 hr_utility.trace(substr(l_str,3826,255));
1159
1160 execute immediate l_str;
1161 --
1162 end if ; --attribute is a SEGMENT
1163 end loop; -- c_sat
1164 hr_utility.set_location(g_package||'create_target>>apply_special_attrib: Leaving',1);
1165 end apply_special_attrib;
1166 --
1167 procedure concat_segs (p_trt_id in number, p_flex in varchar2, p_kf_num in varchar2, p_kd in varchar2)
1168 is
1169 l_str varchar2(32000);
1170 l_appl_id number(15) ;
1171 l_ch_seg FND_FLEX_EXT.SegmentArray ;
1172 l_ac_seg FND_FLEX_EXT.SegmentArray ;
1173
1174 cursor c_sat(v_table_route_id number) is
1175 select sat.ddf_column_name ddf_column_name,
1176 substr(pqh_generic.get_alias(column_name)
1177 ,instr(upper(pqh_generic.get_alias(column_name)),'SEGMENT')+7) seg_no
1178 from pqh_special_attributes sat,
1179 pqh_txn_category_attributes tca,
1180 pqh_attributes att
1181 where sat.txn_category_attribute_id = tca.txn_category_attribute_id
1182 and tca.attribute_id = att.attribute_id
1183 and att.master_table_route_id = v_table_route_id
1184 and tca.select_flag = 'Y'
1185 and att.enable_flag = 'Y'
1186 and sat.attribute_type_cd = 'SEGMENT'
1187 and sat.flex_code = p_flex
1188 and sat.context = pqh_gen_form.g_gbl_context
1189 order by to_number(substr(pqh_generic.get_alias(column_name)
1190 ,instr(upper(pqh_generic.get_alias(column_name)),'SEGMENT')+7)) ;
1191
1192 cursor c_flex is
1193 select ddf_column_name
1194 from pqh_special_attributes sat,
1195 pqh_txn_category_attributes tca,
1196 pqh_attributes att
1197 where sat.txn_category_attribute_id = tca.txn_category_attribute_id
1198 and tca.attribute_id = att.attribute_id
1199 and tca.select_flag = 'Y'
1200 and att.enable_flag = 'Y'
1201 and sat.attribute_type_cd = 'KEY_FLEX'
1202 and sat.flex_code = p_flex
1203 and sat.context = pqh_gen_form.g_gbl_context ;
1204
1205 cursor c_kf_seg (v_id_fc in varchar2, v_id_fn in varchar2, v_appl_id in number)is
1206 select application_column_name,
1207 segment_num,
1208 to_number(substr(application_column_name, instr(upper(application_column_name),'SEGMENT')+7)) seg_no
1209 from fnd_id_flex_segments
1210 where id_flex_code = v_id_fc
1211 and id_flex_num = v_id_fn
1212 and application_id = v_appl_id
1213 and enabled_flag = 'Y'
1214 and display_flag = 'Y'
1215 order by segment_num;
1216
1217 cursor c_change is
1218 select sat2.ddf_column_name ch_col, sat.ddf_column_name ac_col
1219 from pqh_special_attributes sat,
1220 pqh_special_attributes sat2,
1221 pqh_txn_category_attributes tca,
1222 pqh_attributes att
1223 where sat.txn_category_attribute_id = tca.txn_category_attribute_id
1224 and sat.txn_category_attribute_id = sat2.txn_category_attribute_id
1225 and tca.attribute_id = att.attribute_id
1226 and tca.select_flag = 'Y'
1227 and att.enable_flag = 'Y'
1228 and sat.attribute_type_cd = 'KEY_FLEX'
1229 and sat2.attribute_type_cd = 'CHANGEABLE'
1230 and sat.flex_code = p_flex
1231 and sat2.context = pqh_gen_form.g_gbl_context
1232 and sat.context = pqh_gen_form.g_gbl_context ;
1233
1234 begin
1235 hr_utility.set_location(g_package||'create_target>>concat_segs: Entering',1);
1236
1237 for l in c_change loop
1238
1239 l_str:= l_str||'c := fnd_flex_ext.breakup_segments (nvl(pqh_gen_form.c_'||l.ch_col
1240 ||',pqh_gen_form.g_'||l.ac_col||'),'''||p_kd||''', l_ch_seg); '
1241 ||'a := fnd_flex_ext.breakup_segments (nvl(pqh_gen_form.g_'||l.ac_col
1242 ||',pqh_gen_form.c_'||l.ch_col||'),'''||p_kd||''', l_ac_seg); '
1243 ||' hr_utility.set_location(''dyn ch:''|| pqh_gen_form.c_'||l.ch_col||',1001);'
1244 ||' hr_utility.set_location(''dyn ac:''|| pqh_gen_form.g_'||l.ac_col||',1002);'
1245 ||'for z in 1..c loop begin '
1246 ||'l_c := nvl(l_ch_seg(z),''$PQH_unq$''); '
1247 ||'exception when no_data_found then '
1248 ||'l_c := ''$PQH_unq$''; end ; begin '
1249 ||'l_a := nvl(l_ac_seg(z),''$PQH_unq$''); '
1250 ||'exception when no_data_found then '
1251 ||'l_a := ''$PQH_unq$''; end ; '
1252 ||'if l_c <> l_a then if l_c <> ''$PQH_unq$'' then '
1253 ||'l_ac_seg(z) := l_c; elsif l_a <> ''$PQH_unq$'' then l_ac_seg(z) := l_a; '
1254 ||'else l_ac_seg(z) := null; end if; end if; end loop; '
1255 ||'l_val := fnd_flex_ext.concatenate_segments(c, l_ac_seg,'''||p_kd||'''); '
1256 ||'pqh_gen_form.g_'||l.ac_col||' := l_val; ';
1257 end loop;
1258 --
1259 if l_str is not null then
1260 l_str:= 'declare j number := 0; l_a varchar2(300); l_c varchar2(300); a number; c number; l_val varchar2(2000);'
1261 ||' l_ch_seg FND_FLEX_EXT.SegmentArray ; l_ac_seg FND_FLEX_EXT.SegmentArray ; begin '||l_str||' end;';
1262 hr_utility.trace(substr(l_str,1,255));
1263 hr_utility.trace(substr(l_str,256,255));
1264 hr_utility.trace(substr(l_str,511,255));
1265 hr_utility.trace(substr(l_str,766,255));
1266 hr_utility.trace(substr(l_str,1021,255));
1267 hr_utility.trace(substr(l_str,1276,255));
1268 hr_utility.trace(substr(l_str,1531,255));
1269 hr_utility.trace(substr(l_str,1786,255));
1270 hr_utility.trace(substr(l_str,2041,255));
1271 hr_utility.trace(substr(l_str,2296,255));
1272 hr_utility.trace(substr(l_str,2551,255));
1273 hr_utility.trace(substr(l_str,2806,255));
1274 hr_utility.trace(substr(l_str,3061,255));
1275 hr_utility.trace(substr(l_str,3316,255));
1276 hr_utility.trace(substr(l_str,3571,255));
1277 hr_utility.trace(substr(l_str,3826,255));
1278
1279 execute immediate l_str;
1280 end if;
1281 /*
1282 This part of code is commented out nocopy as this results is id on kf concat.
1283 The reason why it still remain:
1284 For reconstructing the rule breaker.
1285
1286 if p_flex in ('COST', 'GRP') then
1287 l_appl_id := 801 ;
1288 else
1289 l_appl_id := 800 ;
1290 end if;
1291 --
1292 if p_kd is not null then
1293 l_str := 'declare j number := 0; l_val varchar2(2000); all_segs FND_FLEX_EXT.SegmentArray ; begin ';
1294 for k in c_kf_seg (p_flex, p_kf_num, l_appl_id) loop
1295 for i in c_sat(p_trt_id) loop
1296 if i.seg_no = k.seg_no then
1297 l_str := l_str ||' j:= j+1; all_segs(j):= pqh_gen_form.g_'||i.ddf_column_name||';';
1298 end if;
1299 end loop;-- c_sat
1300 end loop;
1301 --
1302 for j in c_flex loop
1303 l_str := l_str||' l_val := fnd_flex_ext.concatenate_segments(j, all_segs,'''||p_kd||''');'
1304 ||' pqh_gen_form.g_'||j.ddf_column_name||' := l_val; end; ';
1305 end loop;
1306 --
1307 hr_utility.trace(substr(l_str,1,2000));
1308 execute immediate l_str;
1309 end if; --p_kd
1310 */
1311 hr_utility.set_location(g_package||'create_target>>concat_segs: Leaving',1);
1312 end concat_segs;
1313 --
1314 begin
1315 --
1316 hr_utility.set_location(g_package||'create_target: Entering',1);
1317 --
1318 delete from pqh_copy_entity_results
1319 where result_type_cd = 'TARGET'
1320 and copy_entity_txn_id = p_copy_entity_txn_id;
1321 --
1322 for rec in c_txn loop
1323 l_txn_category_attribute_id := rec.txn_category_attribute_id ;
1324 l_replacement_type_cd := rec.replacement_type_cd ;
1325 l_start_with := rec.start_with ;
1326 l_increment_by := rec.increment_by ;
1327 l_mast_trt_id := rec.master_table_route_id ;
1328 l_transaction_id := rec.transaction_category_id ;
1329 pqh_gen_form.populate_context(p_copy_entity_txn_id);
1330 exit ; -- should ideally loop only once
1331 end loop ; -- c_txn
1332 hr_utility.set_location(g_package||'create_target: Clear Table',10);
1333 --
1334 PQH_GEN_FORM.g_attrib_tab.delete; -- clear table
1335 --
1336 hr_utility.set_location(g_package||'create_target: c_attrib',1);
1337 hr_utility.set_location(' context: '||pqh_gen_form.g_context,11);
1338 for rec in c_attrib(l_mast_trt_id) loop
1339 pqh_gen_form.g_attrib_tab(c_attrib%rowcount).column_alias := rec.column_alias ;
1340 pqh_gen_form.g_attrib_tab(c_attrib%rowcount).ddf_column_name := rec.ddf_column_name ;
1341 pqh_gen_form.g_attrib_tab(c_attrib%rowcount).changeable_column_name := rec.changeable_column_name ;
1342 end loop; -- c_attrib
1343 --
1344 hr_utility.set_location(g_package||'create_target: c_change',11);
1345 for rec in c_change loop
1346 c_information1 := rec.information1 ;
1347 c_information2 := rec.information2 ;
1348 c_information3 := rec.information3 ;
1349 c_information4 := rec.information4 ;
1350 c_information5 := rec.information5 ;
1351 c_information6 := rec.information6 ;
1352 c_information7 := rec.information7 ;
1353 c_information8 := rec.information8 ;
1354 c_information9 := rec.information9 ;
1355 c_information10 := rec.information10 ;
1356 c_information11 := rec.information11 ;
1357 c_information12 := rec.information12 ;
1358 c_information13 := rec.information13 ;
1359 c_information14 := rec.information14 ;
1360 c_information15 := rec.information15 ;
1361 c_information16 := rec.information16 ;
1362 c_information17 := rec.information17 ;
1363 c_information18 := rec.information18 ;
1364 c_information19 := rec.information19 ;
1365 c_information20 := rec.information20 ;
1366 c_information21 := rec.information21 ;
1367 c_information22 := rec.information22 ;
1368 c_information23 := rec.information23 ;
1369 c_information24 := rec.information24 ;
1370 c_information25 := rec.information25 ;
1371 c_information26 := rec.information26 ;
1372 c_information27 := rec.information27 ;
1373 c_information28 := rec.information28 ;
1374 c_information29 := rec.information29 ;
1375 c_information30 := rec.information30 ;
1376 --
1377 exit ; -- should ideally have only one record.
1378 --
1379 end loop ; -- rec in c_change
1380 --for i in 1..30 loop
1381 -- execute immediate 'begin pqh_gen_form.c_information20 := pqh_gen_form.c_information'||i||'; end;';
1382 -- hr_utility.set_location('attrib table: c_information'||i||' '||pqh_gen_form.c_information20,12);
1383 --end loop;
1384 --
1385 if p_lf1 is not null then
1386 for rec in c_ch_kf (p_lf1) loop
1387 k1_information1 := rec.information1 ;
1388 k1_information2 := rec.information2 ;
1389 k1_information3 := rec.information3 ;
1390 k1_information4 := rec.information4 ;
1391 k1_information5 := rec.information5 ;
1392 k1_information6 := rec.information6 ;
1393 k1_information7 := rec.information7 ;
1394 k1_information8 := rec.information8 ;
1395 k1_information9 := rec.information9 ;
1396 k1_information10 := rec.information10 ;
1397 k1_information11 := rec.information11 ;
1398 k1_information12 := rec.information12 ;
1399 k1_information13 := rec.information13 ;
1400 k1_information14 := rec.information14 ;
1401 k1_information15 := rec.information15 ;
1402 k1_information16 := rec.information16 ;
1403 k1_information17 := rec.information17 ;
1404 k1_information18 := rec.information18 ;
1405 k1_information19 := rec.information19 ;
1406 k1_information20 := rec.information20 ;
1407 k1_information21 := rec.information21 ;
1408 k1_information22 := rec.information22 ;
1409 k1_information23 := rec.information23 ;
1410 k1_information24 := rec.information24 ;
1411 k1_information25 := rec.information25 ;
1412 k1_information26 := rec.information26 ;
1413 k1_information27 := rec.information27 ;
1414 k1_information28 := rec.information28 ;
1415 k1_information29 := rec.information29 ;
1416 k1_information30 := rec.information30 ;
1417 --
1418 exit ; -- should ideally have only one record.
1419 --
1420 end loop ; -- rec in c_ch_kf1
1421 end if;
1422 --
1423 if p_lf2 is not null then
1424 for rec in c_ch_kf (p_lf2) loop
1425 k2_information1 := rec.information1 ;
1426 k2_information2 := rec.information2 ;
1427 k2_information3 := rec.information3 ;
1428 k2_information4 := rec.information4 ;
1429 k2_information5 := rec.information5 ;
1430 k2_information6 := rec.information6 ;
1431 k2_information7 := rec.information7 ;
1432 k2_information8 := rec.information8 ;
1433 k2_information9 := rec.information9 ;
1434 k2_information10 := rec.information10 ;
1435 k2_information11 := rec.information11 ;
1436 k2_information12 := rec.information12 ;
1437 k2_information13 := rec.information13 ;
1438 k2_information14 := rec.information14 ;
1439 k2_information15 := rec.information15 ;
1440 k2_information16 := rec.information16 ;
1441 k2_information17 := rec.information17 ;
1442 k2_information18 := rec.information18 ;
1443 k2_information19 := rec.information19 ;
1444 k2_information20 := rec.information20 ;
1445 k2_information21 := rec.information21 ;
1446 k2_information22 := rec.information22 ;
1447 k2_information23 := rec.information23 ;
1448 k2_information24 := rec.information24 ;
1449 k2_information25 := rec.information25 ;
1450 k2_information26 := rec.information26 ;
1451 k2_information27 := rec.information27 ;
1452 k2_information28 := rec.information28 ;
1453 k2_information29 := rec.information29 ;
1454 k2_information30 := rec.information30 ;
1455 --
1456 exit ; -- should ideally have only one record.
1457 --
1458 end loop ; -- rec in c_ch_kf2
1459 end if;
1460 --
1461 hr_utility.set_location(g_package||'create_target: c_candidate',12);
1462 for rec in c_candidate loop
1463 g_information1 := rec.information1 ;
1464 g_information2 := rec.information2 ;
1465 g_information3 := rec.information3 ;
1466 g_information4 := rec.information4 ;
1467 g_information5 := rec.information5 ;
1468 g_information6 := rec.information6 ;
1469 g_information7 := rec.information7 ;
1470 g_information8 := rec.information8 ;
1471 g_information9 := rec.information9 ;
1472 g_information10 := rec.information10 ;
1473 g_information11 := rec.information11 ;
1474 g_information12 := rec.information12 ;
1475 g_information13 := rec.information13 ;
1476 g_information14 := rec.information14 ;
1477 g_information15 := rec.information15 ;
1478 g_information16 := rec.information16 ;
1479 g_information17 := rec.information17 ;
1480 g_information18 := rec.information18 ;
1481 g_information19 := rec.information19 ;
1482 g_information20 := rec.information20 ;
1483 g_information21 := rec.information21 ;
1484 g_information22 := rec.information22 ;
1485 g_information23 := rec.information23 ;
1486 g_information24 := rec.information24 ;
1487 g_information25 := rec.information25 ;
1488 g_information26 := rec.information26 ;
1489 g_information27 := rec.information27 ;
1490 g_information28 := rec.information28 ;
1491 g_information29 := rec.information29 ;
1492 g_information30 := rec.information30 ;
1493 g_information31 := rec.information31 ;
1494 g_information32 := rec.information32 ;
1495 g_information33 := rec.information33 ;
1496 g_information34 := rec.information34 ;
1497 g_information35 := rec.information35 ;
1498 g_information36 := rec.information36 ;
1499 g_information37 := rec.information37 ;
1500 g_information38 := rec.information38 ;
1501 g_information39 := rec.information39 ;
1502 g_information40 := rec.information40 ;
1503 g_information41 := rec.information41 ;
1504 g_information42 := rec.information42 ;
1505 g_information43 := rec.information43 ;
1506 g_information44 := rec.information44 ;
1507 g_information45 := rec.information45 ;
1508 g_information46 := rec.information46 ;
1509 g_information47 := rec.information47 ;
1510 g_information48 := rec.information48 ;
1511 g_information49 := rec.information49 ;
1512 g_information50 := rec.information50 ;
1513 g_information51 := rec.information51 ;
1514 g_information52 := rec.information52 ;
1515 g_information53 := rec.information53 ;
1516 g_information54 := rec.information54 ;
1517 g_information55 := rec.information55 ;
1518 g_information56 := rec.information56 ;
1519 g_information57 := rec.information57 ;
1520 g_information58 := rec.information58 ;
1521 g_information59 := rec.information59 ;
1522 g_information60 := rec.information60 ;
1523 g_information61 := rec.information61 ;
1524 g_information62 := rec.information62 ;
1525 g_information63 := rec.information63 ;
1526 g_information64 := rec.information64 ;
1527 g_information65 := rec.information65 ;
1528 g_information66 := rec.information66 ;
1529 g_information67 := rec.information67 ;
1530 g_information68 := rec.information68 ;
1531 g_information69 := rec.information69 ;
1532 g_information70 := rec.information70 ;
1533 g_information71 := rec.information71 ;
1534 g_information72 := rec.information72 ;
1535 g_information73 := rec.information73 ;
1536 g_information74 := rec.information74 ;
1537 g_information75 := rec.information75 ;
1538 g_information76 := rec.information76 ;
1539 g_information77 := rec.information77 ;
1540 g_information78 := rec.information78 ;
1541 g_information79 := rec.information79 ;
1542 g_information80 := rec.information80 ;
1543 g_information81 := rec.information81 ;
1544 g_information82 := rec.information82 ;
1545 g_information83 := rec.information83 ;
1546 g_information84 := rec.information84 ;
1547 g_information85 := rec.information85 ;
1548 g_information86 := rec.information86 ;
1549 g_information87 := rec.information87 ;
1550 g_information88 := rec.information88 ;
1551 g_information89 := rec.information89 ;
1552 g_information90 := rec.information90 ;
1553 g_information91 := rec.information91 ;
1554 g_information92 := rec.information92 ;
1555 g_information93 := rec.information93 ;
1556 g_information94 := rec.information94 ;
1557 g_information95 := rec.information95 ;
1558 g_information96 := rec.information96 ;
1559 g_information97 := rec.information97 ;
1560 g_information98 := rec.information98 ;
1561 g_information99 := rec.information99 ;
1562 g_information100 := rec.information100;
1563 g_information101 := rec.information101;
1564 g_information102 := rec.information102;
1565 g_information103 := rec.information103;
1566 g_information104 := rec.information104;
1567 g_information105 := rec.information105;
1568 g_information106 := rec.information106;
1569 g_information107 := rec.information107;
1570 g_information108 := rec.information108;
1571 g_information109 := rec.information109;
1572 g_information110 := rec.information110;
1573 g_information111 := rec.information111;
1574 g_information112 := rec.information112;
1575 g_information113 := rec.information113;
1576 g_information114 := rec.information114;
1577 g_information115 := rec.information115;
1578 g_information116 := rec.information116;
1579 g_information117 := rec.information117;
1580 g_information118 := rec.information118;
1581 g_information119 := rec.information119;
1582 g_information120 := rec.information120;
1583 g_information121 := rec.information121;
1584 g_information122 := rec.information122;
1585 g_information123 := rec.information123;
1586 g_information124 := rec.information124;
1587 g_information125 := rec.information125;
1588 g_information126 := rec.information126;
1589 g_information127 := rec.information127;
1590 g_information128 := rec.information128;
1591 g_information129 := rec.information129;
1592 g_information130 := rec.information130;
1593 g_information131 := rec.information131;
1594 g_information132 := rec.information132;
1595 g_information133 := rec.information133;
1596 g_information134 := rec.information134;
1597 g_information135 := rec.information135;
1598 g_information136 := rec.information136;
1599 g_information137 := rec.information137;
1600 g_information138 := rec.information138;
1601 g_information139 := rec.information139;
1602 g_information140 := rec.information140;
1603 g_information141 := rec.information141;
1604 g_information142 := rec.information142;
1605 g_information143 := rec.information143;
1606 g_information144 := rec.information144;
1607 g_information145 := rec.information145;
1608 g_information146 := rec.information146;
1609 g_information147 := rec.information147;
1610 g_information148 := rec.information148;
1611 g_information149 := rec.information149;
1612 g_information150 := rec.information150;
1613 g_information151 := rec.information151;
1614 g_information152 := rec.information152;
1615 g_information153 := rec.information153;
1616 g_information154 := rec.information154;
1617 g_information155 := rec.information155;
1618 g_information156 := rec.information156;
1619 g_information157 := rec.information157;
1620 g_information158 := rec.information158;
1621 g_information159 := rec.information159;
1622 g_information160 := rec.information160;
1623 g_information161 := rec.information161;
1624 g_information162 := rec.information162;
1625 g_information163 := rec.information163;
1626 g_information164 := rec.information164;
1627 g_information165 := rec.information165;
1628 g_information166 := rec.information166;
1629 g_information167 := rec.information167;
1630 g_information168 := rec.information168;
1631 g_information169 := rec.information169;
1632 g_information170 := rec.information170;
1633 g_information171 := rec.information171;
1634 g_information172 := rec.information172;
1635 g_information173 := rec.information173;
1636 g_information174 := rec.information174;
1637 g_information175 := rec.information175;
1638 g_information176 := rec.information176;
1639 g_information177 := rec.information177;
1640 g_information178 := rec.information178;
1641 g_information179 := rec.information179;
1642 g_information180 := rec.information180;
1643 --
1644 l_copies := rec.copies;
1645 --
1646 apply_changes ; -- changes on the ddf
1647 --
1648 if p_lf1 is not null then
1649 apply_kf(p_lf1, '1');
1650 concat_segs (l_mast_trt_id,p_lf1, p_ln1, p_ld1) ;
1651 end if;
1652 --
1653 if p_lf2 is not null then
1654 apply_kf(p_lf2, '2');
1655 concat_segs (l_mast_trt_id,p_lf2, p_ln2, p_ld2) ;
1656 end if;
1657 --
1658 hr_utility.set_location(g_package||'create_target: l_copies',13);
1659 for e_rec in c_id(l_transaction_id) loop
1660 execute immediate 'begin pqh_gen_form.g_'||e_rec.ddf_value_column_name ||':= rtrim('
1661 ||'pqh_gen_form.get_value_from_id(pqh_gen_form.g_'||e_rec.ddf_column_name ||','''
1662 ||e_rec.value_set_id||''')); end;';
1663 hr_utility.trace('Tgt Loop : '||e_rec.ddf_value_column_name ||' - '||e_rec.ddf_column_name);
1664 end loop; --c_id
1665 --
1666 hr_utility.set_location('Infor 1 '||pqh_gen_form.g_information1,1);
1667 hr_utility.set_location('Infor 3 '||pqh_gen_form.g_information3,1);
1668 hr_utility.set_location('Infor 4 '||pqh_gen_form.g_information4,1);
1669 hr_utility.set_location('C Infor 2 '||pqh_gen_form.c_information2,1);
1670 for i in 1..l_copies loop
1671
1672 hr_utility.set_location('Infor 1 '||pqh_gen_form.g_information1,1);
1673 hr_utility.set_location('Infor 3 '||pqh_gen_form.g_information3,1);
1674 hr_utility.set_location('Infor 4 '||pqh_gen_form.g_information4,1);
1675 hr_utility.set_location('C Infor 2 '||pqh_gen_form.c_information2,1);
1676 apply_special_attrib ( p_txn_category_attribute_id => l_txn_category_attribute_id
1677 , p_index => i
1678 , p_replacement_type_cd => l_replacement_type_cd
1679 , p_start_with => l_start_with
1680 , p_increment_by => l_increment_by);
1681 -- , p_lf1 => p_lf1
1682 -- , p_lf2 => p_lf2
1683 -- , p_ln1 => p_ln1
1684 -- , p_ln2 => p_ln2);
1685 --
1686 pqh_copy_entity_results_api.create_copy_entity_result
1687 (
1688 p_validate => FALSE
1689 ,p_copy_entity_result_id => l_copy_entity_result_id
1690 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1691 ,p_result_type_cd => 'TARGET'
1692 ,p_number_of_copies => nvl(l_warn, '1') -- l_warn not null => warnings
1693 ,p_status => 'TGT_P'
1694 ,p_src_copy_entity_result_id => rec.copy_entity_result_id
1695 ,p_information_category => ''
1696 ,p_information1 => g_information1
1697 ,p_information2 => g_information2
1698 ,p_information3 => g_information3
1699 ,p_information4 => g_information4
1700 ,p_information5 => g_information5
1701 ,p_information6 => g_information6
1702 ,p_information7 => g_information7
1703 ,p_information8 => g_information8
1704 ,p_information9 => g_information9
1705 ,p_information10 => g_information10
1706 ,p_information11 => g_information11
1707 ,p_information12 => g_information12
1708 ,p_information13 => g_information13
1709 ,p_information14 => g_information14
1710 ,p_information15 => g_information15
1711 ,p_information16 => g_information16
1712 ,p_information17 => g_information17
1713 ,p_information18 => g_information18
1714 ,p_information19 => g_information19
1715 ,p_information20 => g_information20
1716 ,p_information21 => g_information21
1717 ,p_information22 => g_information22
1718 ,p_information23 => g_information23
1719 ,p_information24 => g_information24
1720 ,p_information25 => g_information25
1721 ,p_information26 => g_information26
1722 ,p_information27 => g_information27
1723 ,p_information28 => g_information28
1724 ,p_information29 => g_information29
1725 ,p_information30 => g_information30
1726 ,p_information31 => g_information31
1727 ,p_information32 => g_information32
1728 ,p_information33 => g_information33
1729 ,p_information34 => g_information34
1730 ,p_information35 => g_information35
1731 ,p_information36 => g_information36
1732 ,p_information37 => g_information37
1733 ,p_information38 => g_information38
1734 ,p_information39 => g_information39
1735 ,p_information40 => g_information40
1736 ,p_information41 => g_information41
1737 ,p_information42 => g_information42
1738 ,p_information43 => g_information43
1739 ,p_information44 => g_information44
1740 ,p_information45 => g_information45
1741 ,p_information46 => g_information46
1742 ,p_information47 => g_information47
1743 ,p_information48 => g_information48
1744 ,p_information49 => g_information49
1745 ,p_information50 => g_information50
1746 ,p_information51 => g_information51
1747 ,p_information52 => g_information52
1748 ,p_information53 => g_information53
1749 ,p_information54 => g_information54
1750 ,p_information55 => g_information55
1751 ,p_information56 => g_information56
1752 ,p_information57 => g_information57
1753 ,p_information58 => g_information58
1754 ,p_information59 => g_information59
1755 ,p_information60 => g_information60
1756 ,p_information61 => g_information61
1757 ,p_information62 => g_information62
1758 ,p_information63 => g_information63
1759 ,p_information64 => g_information64
1760 ,p_information65 => g_information65
1761 ,p_information66 => g_information66
1762 ,p_information67 => g_information67
1763 ,p_information68 => g_information68
1764 ,p_information69 => g_information69
1765 ,p_information70 => g_information70
1766 ,p_information71 => g_information71
1767 ,p_information72 => g_information72
1768 ,p_information73 => g_information73
1769 ,p_information74 => g_information74
1770 ,p_information75 => g_information75
1771 ,p_information76 => g_information76
1772 ,p_information77 => g_information77
1773 ,p_information78 => g_information78
1774 ,p_information79 => g_information79
1775 ,p_information80 => g_information80
1776 ,p_information81 => g_information81
1777 ,p_information82 => g_information82
1778 ,p_information83 => g_information83
1779 ,p_information84 => g_information84
1780 ,p_information85 => g_information85
1781 ,p_information86 => g_information86
1782 ,p_information87 => g_information87
1783 ,p_information88 => g_information88
1784 ,p_information89 => g_information89
1785 ,p_information90 => g_information90
1786 ,p_information91 => g_information91
1787 ,p_information92 => g_information92
1788 ,p_information93 => g_information93
1789 ,p_information94 => g_information94
1790 ,p_information95 => g_information95
1791 ,p_information96 => g_information96
1792 ,p_information97 => g_information97
1793 ,p_information98 => g_information98
1794 ,p_information99 => g_information99
1795 ,p_information100 => g_information100
1796 ,p_information101 => g_information101
1797 ,p_information102 => g_information102
1798 ,p_information103 => g_information103
1799 ,p_information104 => g_information104
1800 ,p_information105 => g_information105
1801 ,p_information106 => g_information106
1802 ,p_information107 => g_information107
1803 ,p_information108 => g_information108
1804 ,p_information109 => g_information109
1805 ,p_information110 => g_information110
1806 ,p_information111 => g_information111
1807 ,p_information112 => g_information112
1808 ,p_information113 => g_information113
1809 ,p_information114 => g_information114
1810 ,p_information115 => g_information115
1811 ,p_information116 => g_information116
1812 ,p_information117 => g_information117
1813 ,p_information118 => g_information118
1814 ,p_information119 => g_information119
1815 ,p_information120 => g_information120
1816 ,p_information121 => g_information121
1817 ,p_information122 => g_information122
1818 ,p_information123 => g_information123
1819 ,p_information124 => g_information124
1820 ,p_information125 => g_information125
1821 ,p_information126 => g_information126
1822 ,p_information127 => g_information127
1823 ,p_information128 => g_information128
1824 ,p_information129 => g_information129
1825 ,p_information130 => g_information130
1826 ,p_information131 => g_information131
1827 ,p_information132 => g_information132
1828 ,p_information133 => g_information133
1829 ,p_information134 => g_information134
1830 ,p_information135 => g_information135
1831 ,p_information136 => g_information136
1832 ,p_information137 => g_information137
1833 ,p_information138 => g_information138
1834 ,p_information139 => g_information139
1835 ,p_information140 => g_information140
1836 ,p_information141 => g_information141
1837 ,p_information142 => g_information142
1838 ,p_information143 => g_information143
1839 ,p_information144 => g_information144
1840 ,p_information145 => g_information145
1841 ,p_information146 => g_information146
1842 ,p_information147 => g_information147
1843 ,p_information148 => g_information148
1844 ,p_information149 => g_information149
1845 ,p_information150 => g_information150
1846 ,p_information151 => g_information151
1847 ,p_information152 => g_information152
1848 ,p_information153 => g_information153
1849 ,p_information154 => g_information154
1850 ,p_information155 => g_information155
1851 ,p_information156 => g_information156
1852 ,p_information157 => g_information157
1853 ,p_information158 => g_information158
1854 ,p_information159 => g_information159
1855 ,p_information160 => g_information160
1856 ,p_information161 => g_information161
1857 ,p_information162 => g_information162
1858 ,p_information163 => g_information163
1859 ,p_information164 => g_information164
1860 ,p_information165 => g_information165
1861 ,p_information166 => g_information166
1862 ,p_information167 => g_information167
1863 ,p_information168 => g_information168
1864 ,p_information169 => g_information169
1865 ,p_information170 => g_information170
1866 ,p_information171 => g_information171
1867 ,p_information172 => g_information172
1868 ,p_information173 => g_information173
1869 ,p_information174 => g_information174
1870 ,p_information175 => g_information175
1871 ,p_information176 => g_information176
1872 ,p_information177 => g_information177
1873 ,p_information178 => g_information178
1874 ,p_information179 => g_information179
1875 ,p_information180 => g_information180
1876 ,p_object_version_number => l_ovn
1877 ,p_effective_date => trunc(sysdate)
1878 );
1879
1880 end loop; -- 1..rec.number_of_copies
1881 update pqh_copy_entity_results
1882 set status = 'TGT_P'
1883 where copy_entity_result_id = rec.copy_entity_result_id;
1884 --
1885 if l_warn is not null and p_batch_status is null then
1886 p_batch_status := 'WARN';
1887 end if;
1888 --
1889 l_warn := ''; -- reset to no warnings
1890 --
1891 end loop; -- c_candidate
1892 hr_utility.set_location(g_package||'create_target: Leaving',1);
1893 exception when others then
1894 p_batch_status := null;
1895 raise;
1896 end create_target;
1897 --
1898 function get_sql_from_vset_id(p_vset_id in number,
1899 p_add_where_clause in varchar2 default null,
1900 p_where_on_id in boolean default false )
1901 return varchar2 is
1902 l_v_r fnd_vset.valueset_r;
1903 l_v_dr fnd_vset.valueset_dr;
1904 l_str varchar2(4000);
1905 l_whr varchar2(4000);
1906 l_ord varchar2(4000);
1907 l_col varchar2(4000);
1908 begin
1909 --
1910 fnd_vset.get_valueset(valueset_id => p_vset_id ,
1911 valueset => l_v_r,
1912 format => l_v_dr);
1913 --
1914 if l_v_r.table_info.table_name is null then
1915 return ('');
1916 end if;
1917 --
1918 if l_v_r.table_info.id_column_name is null then
1919 return ('');
1920 end if;
1921 --
1922 if l_v_r.table_info.value_column_name is null then
1923 return ('');
1924 end if;
1925 --
1926 if p_add_where_clause is not null then
1927 if p_where_on_id then
1928 l_col := substr(l_v_r.table_info.id_column_name,1,instr(l_v_r.table_info.id_column_name||' ', ' '));
1929 else
1930 l_col := substr(l_v_r.table_info.value_column_name,1,instr(l_v_r.table_info.value_column_name||' ', ' '));
1931 end if;
1932 --
1933 l_whr := substr(l_v_r.table_info.where_clause||' ',1,
1934 instr(upper(l_v_r.table_info.where_clause||' ORDER BY'),'ORDER BY')-1);
1935 l_ord := substr(l_v_r.table_info.where_clause,
1936 instr(upper(l_v_r.table_info.where_clause||'ORDER BY'),'ORDER BY'));
1937
1938 if instr(upper(l_whr),'WHERE') > 0 then
1939 l_whr := l_whr||' and '||l_col || p_add_where_clause||' and rownum < 2 ';
1940 else
1941 l_whr := 'Where '||l_col || p_add_where_clause||' and rownum < 2 ' ;
1942 end if;
1943 l_str := 'select '||l_v_r.table_info.id_column_name||','
1944 ||l_v_r.table_info.value_column_name
1945 ||' into pqh_gen_form.g_v_id, pqh_gen_form.g_v_value '
1946 ||' from '
1947 ||l_v_r.table_info.table_name||' '||l_whr;
1948 --
1949 else
1950 l_whr := l_v_r.table_info.where_clause ;
1951 l_str := 'select '||substr(l_v_r.table_info.id_column_name,1,instr(l_v_r.table_info.id_column_name||' ',' '))||','
1952 ||substr(l_v_r.table_info.value_column_name,1,instr(l_v_r.table_info.value_column_name||' ',' '))
1953 ||' from '
1954 ||l_v_r.table_info.table_name||' '||l_whr;
1955 --
1956 end if;
1957 --
1958 return (l_str);
1959 end get_sql_from_vset_id;
1960 --
1961 function get_value_from_id(p_id in varchar2,
1962 p_vset_id in varchar2) return varchar2 is
1963 l_sql varchar2(4000);
1964 l_cnt number;
1965 begin
1966 pqh_gen_form.g_v_value := '';
1967 if p_id is null then
1968 return('');
1969 end if;
1970 --
1971 if p_vset_id is null then
1972 return(p_id);
1973 end if;
1974 --
1975 begin
1976 l_cnt := pqh_gen_form.g_vset_tab.count ;
1977 for j in nvl(PQH_GEN_FORM.g_vset_tab.first,0)..nvl(PQH_GEN_FORM.g_vset_tab.last,-1) loop
1978 if pqh_gen_form.g_vset_tab(j).vset_id = p_vset_id then
1979 if pqh_gen_form.g_vset_tab(j).code = p_id then
1980 return(pqh_gen_form.g_vset_tab(j).meaning);
1981 end if;
1982 end if;
1983 end loop;
1984 exception when no_data_found then null; -- in case the table is yet to be populated
1985 end;
1986 --
1987 l_sql := get_sql_from_vset_id(p_vset_id => p_vset_id,
1988 p_add_where_clause => ' = '''||p_id||'''' ,
1989 p_where_on_id => true );
1990 --
1991 execute immediate 'Begin '||l_sql ||'; end;';
1992 --
1993 pqh_gen_form.g_vset_tab(nvl(l_cnt,0)+1).vset_id := p_vset_id;
1994 pqh_gen_form.g_vset_tab(nvl(l_cnt,0)+1).code := p_id;
1995 pqh_gen_form.g_vset_tab(nvl(l_cnt,0)+1).meaning := pqh_gen_form.g_v_value;
1996 --
1997 return(pqh_gen_form.g_v_value);
1998 --
1999 exception
2000 when no_data_found then
2001 pqh_gen_form.g_v_id := '';
2002 pqh_gen_form.g_v_value := '';
2003 return(''); --Modified for bug 7411098
2004 when others then
2005 pqh_gen_form.g_v_id := '';
2006 pqh_gen_form.g_v_value := '';
2007 return(p_id||'-'||substr(sqlerrm,1,125));
2008 --raise ;
2009 end get_value_from_id;
2010 --
2011 function get_id_from_value(p_value in varchar2,
2012 p_vset_id in varchar2) return varchar2 is
2013 l_sql varchar2(4000);
2014 begin
2015 if p_value is null then
2016 return('');
2017 end if;
2018 --
2019 if p_vset_id is null then
2020 return(p_value);
2021 end if;
2022 --
2023 l_sql := get_sql_from_vset_id(p_vset_id => p_vset_id,
2024 p_add_where_clause => ' = '''||p_value ||'''' ,
2025 p_where_on_id => false );
2026 --
2027 execute immediate 'Begin '||l_sql ||'; end;';
2028 --
2029 return(pqh_gen_form.g_v_id);
2030 --
2031 exception
2032 when no_data_found then
2033 pqh_gen_form.g_v_id := '';
2034 pqh_gen_form.g_v_value := '';
2035 return('');
2036 when others then
2037 pqh_gen_form.g_v_id := '';
2038 pqh_gen_form.g_v_value := '';
2039 return('');
2040 --raise ;
2041 end get_id_from_value;
2042 --
2043 function populate_prefs(p_copy_entity_txn_id in number
2044 , p_transaction_category_id in number ) return boolean
2045 is
2046 l_ovn number;
2047 --
2048 cursor c_tab is
2049 select distinct tr.table_route_id, tr.display_name
2050 from pqh_table_route tr
2051 , pqh_attributes_vl a
2052 , pqh_txn_category_attributes c
2053 , pqh_transaction_categories cat
2054 where a.master_table_route_id = tr.table_route_id
2055 and a.attribute_id = c.attribute_id
2056 and tr.table_route_id <> cat.master_table_route_id
2057 and cat.transaction_category_id = c.transaction_category_id
2058 and c.select_flag = 'Y'
2059 and a.enable_flag = 'Y'
2060 and c.transaction_category_id = nvl(p_transaction_category_id,-99);
2061
2062 --
2063 cursor c_check is
2064 select null from pqh_copy_entity_prefs where copy_entity_txn_id = nvl(p_copy_entity_txn_id,-99);
2065 --
2066 begin
2067 --
2068 for i in c_check loop
2069 return true;
2070 end loop;
2071
2072 for rec in c_tab loop
2073 pqh_copy_entity_prefs_api.create_copy_entity_pref (
2074 p_validate => false
2075 ,p_copy_entity_pref_id => l_ovn
2076 ,p_table_route_id => rec.table_route_id
2077 ,p_copy_entity_txn_id => p_copy_entity_txn_id
2078 ,p_select_flag => 'Y'
2079 ,p_object_version_number => l_ovn
2080 ,p_effective_date => trunc(sysdate) );
2081
2082 end loop; -- rec in tab
2083 --
2084 if l_ovn is null then
2085 return(false);
2086 else
2087 return(true);
2088 end if;
2089 --
2090 end populate_prefs;
2091 --
2092 function get_legislation_code (p_business_group_id in varchar2) return varchar2
2093 is
2094 cursor c_leg is
2095 select legislation_code from per_business_groups
2096 where business_group_id = nvl(p_business_group_id, -99) ;
2097 begin
2098 for i in c_leg loop
2099 return(i.legislation_code);
2100 end loop;
2101 --
2102 return('NULL');
2103 --
2104 end get_legislation_code;
2105 --
2106 function get_alr(p_application_id in number
2107 ,p_responsibility_id in number
2108 ,p_business_group_id in varchar2
2109 ,p_transaction_short_name in varchar2
2110 ,p_application_short_name out nocopy varchar2
2111 ,p_legislation_code out nocopy varchar2
2112 ,p_responsibility_key out nocopy varchar2
2113 ,p_gbl_context out nocopy varchar2 ) return varchar2
2114 is
2115
2116 cursor c_one is
2117 select context
2118 ,application_short_name
2119 ,legislation_code
2120 ,responsibility_key
2121 from pqh_copy_entity_contexts
2122 where transaction_short_name = p_transaction_short_name ;
2123
2124 cursor c_gbl is
2125 select context
2126 from pqh_copy_entity_contexts
2127 where transaction_short_name = p_transaction_short_name -- +++ ENSURE index on txn_short_name +++
2128 and nvl(upper(application_short_name), 'NULL') = 'NULL'
2129 and nvl(upper(legislation_code) , 'NULL') = 'NULL'
2130 and nvl(upper(responsibility_key) , 'NULL') = 'NULL';
2131
2132 cursor c_appl is
2133 select application_short_name
2134 from fnd_application
2135 where application_id = p_application_id ;
2136
2137 cursor c_resp is
2138 select responsibility_key
2139 from fnd_responsibility
2140 where responsibility_id = p_responsibility_id ;
2141 begin
2142 hr_utility.set_location('Entering alr : ',100);
2143 --
2144 -- get application short name to determine context
2145 --
2146 for i in c_appl loop
2147 p_application_short_name := i.application_short_name ;
2148 end loop;
2149 hr_utility.set_location(' a : '||p_application_short_name ,100);
2150 --
2151 -- get legislation_code to help determine context
2152 --
2153 p_legislation_code := pqh_gen_form.get_legislation_code(p_business_group_id);
2154 hr_utility.set_location(' l : '||p_legislation_code ,100);
2155 --
2156 -- read responsibility short name to facilitate deriving context
2157 --
2158 for i in c_resp loop
2159 p_responsibility_key := i.responsibility_key ;
2160 end loop;
2161 hr_utility.set_location(' r : '||p_responsibility_key ,100);
2162 --
2163 for rec in c_gbl loop
2164 p_gbl_context := rec.context ;
2165 pqh_gen_form.g_txn_name := p_transaction_short_name ;
2166 pqh_gen_form.g_gbl_context := rec.context ;
2167 end loop; --c_gbl
2168 --
2169 for i in c_one loop
2170 --
2171 if nvl(i.application_short_name, 'NULL') = p_application_short_name and
2172 nvl(i.legislation_code , 'NULL') = p_legislation_code and
2173 nvl(i.responsibility_key , 'NULL') = p_responsibility_key then
2174 p_application_short_name := i.application_short_name ;
2175 p_legislation_code := i.legislation_code ;
2176 p_responsibility_key := i.responsibility_key ;
2177 pqh_gen_form.g_context := i.context;
2178 return (i.context);
2179 end if ; -- alr
2180 end loop; --c_one
2181 --
2182 for i in c_one loop
2183 if nvl(i.application_short_name , 'NULL') = p_application_short_name and
2184 nvl(upper(i.legislation_code) , 'NULL') = 'NULL' and
2185 nvl(i.responsibility_key , 'NULL') = p_responsibility_key then
2186 p_application_short_name := i.application_short_name ;
2187 p_legislation_code := 'NULL' ;
2188 p_responsibility_key := i.responsibility_key ;
2189 pqh_gen_form.g_context := i.context;
2190 return (i.context);
2191 end if ; -- a r
2192 end loop; --c_one
2193 --
2194 for i in c_one loop
2195 if nvl(upper(i.application_short_name), 'NULL') = 'NULL' and
2196 nvl(i.legislation_code , 'NULL') = p_legislation_code and
2197 nvl(i.responsibility_key , 'NULL') = p_responsibility_key then
2198 p_application_short_name := 'NULL';
2199 p_legislation_code := i.legislation_code ;
2200 p_responsibility_key := i.responsibility_key ;
2201 pqh_gen_form.g_context := i.context;
2202 return (i.context);
2203 end if ; -- lr
2204 end loop; --c_one
2205 --
2206 for i in c_one loop
2207 if nvl(i.application_short_name , 'NULL') = p_application_short_name and
2208 nvl(i.legislation_code , 'NULL') = p_legislation_code and
2209 nvl(upper(i.responsibility_key) , 'NULL') = 'NULL' then
2210 p_application_short_name := i.application_short_name ;
2211 p_legislation_code := i.legislation_code ;
2212 p_responsibility_key := 'NULL' ;
2213 pqh_gen_form.g_context := i.context;
2214 return (i.context);
2215 end if ; -- al
2216 end loop; --c_one
2217 --
2218 for i in c_one loop
2219 if nvl(upper(i.application_short_name), 'NULL') = 'NULL' and
2220 nvl(upper(i.legislation_code) , 'NULL') = 'NULL' and
2221 nvl(i.responsibility_key , 'NULL') = p_responsibility_key then
2222 p_application_short_name := 'NULL' ;
2223 p_legislation_code := 'NULL' ;
2224 p_responsibility_key := i.responsibility_key ;
2225 pqh_gen_form.g_context := i.context;
2226 return (i.context);
2227 end if ; -- r
2228 end loop; --c_one
2229 --
2230 for i in c_one loop
2231 if nvl(upper(i.application_short_name), 'NULL') = 'NULL' and
2232 nvl(i.legislation_code , 'NULL') = p_legislation_code and
2233 nvl(upper(i.responsibility_key) , 'NULL') = 'NULL' then
2234 p_application_short_name := 'NULL' ;
2235 p_legislation_code := i.legislation_code ;
2236 p_responsibility_key := 'NULL' ;
2237 pqh_gen_form.g_context := i.context;
2238 return (i.context);
2239 end if ; -- l
2240 end loop; --c_one
2241 --
2242 for i in c_one loop
2243 if nvl(i.application_short_name , 'NULL') = p_application_short_name and
2244 nvl(upper(i.legislation_code) , 'NULL') = 'NULL' and
2245 nvl(upper(i.responsibility_key) , 'NULL') = 'NULL' then
2246 p_application_short_name := i.application_short_name ;
2247 p_legislation_code := 'NULL' ;
2248 p_responsibility_key := 'NULL' ;
2249 pqh_gen_form.g_context := i.context;
2250 return (i.context);
2251 end if ; -- a
2252 end loop; --c_one
2253 --
2254 for i in c_one loop
2255 if nvl(upper(i.application_short_name), 'NULL') = 'NULL' and
2256 nvl(upper(i.legislation_code) , 'NULL') = 'NULL' and
2257 nvl(upper(i.responsibility_key) , 'NULL') = 'NULL' then
2258 p_application_short_name := 'NULL' ;
2259 p_legislation_code := 'NULL' ;
2260 p_responsibility_key := 'NULL' ;
2261 pqh_gen_form.g_context := i.context;
2262 return (i.context);
2263 end if ; -- null (global)
2264
2265 end loop; --c_one
2266 --
2267 hr_utility.set_location('Leaving alr : ',100);
2268 exception when others then
2269 p_application_short_name := null;
2270 p_legislation_code := null;
2271 p_responsibility_key := null;
2272 p_gbl_context := null;
2273 raise;
2274 end get_alr;
2275 --
2276 procedure populate_context(p_copy_entity_txn_id in number)
2277 is
2278 cursor c_txn is
2279 select short_name, context
2280 from pqh_copy_entity_txns cet,
2281 pqh_transaction_categories tct
2282 where cet.transaction_category_id = tct.transaction_category_id
2283 and cet.copy_entity_txn_id = p_copy_entity_txn_id ;
2284 --
2285 cursor c_gbl(v_context in varchar2) is
2286 select ce1.context
2287 from pqh_copy_entity_contexts ce1, pqh_copy_entity_contexts ce2
2288 where ce1.transaction_short_name = ce2.transaction_short_name
2289 and ce2.context = v_context
2290 and nvl(upper(ce1.application_short_name), 'NULL') = 'NULL'
2291 and nvl(upper(ce1.legislation_code) , 'NULL') = 'NULL'
2292 and nvl(upper(ce1.responsibility_key) , 'NULL') = 'NULL';
2293 begin
2294 if pqh_gen_form.g_context is null then
2295 for i in c_txn loop
2296 pqh_gen_form.g_context := i.context ;
2297 end loop;
2298 end if;
2299 --
2300 if pqh_gen_form.g_gbl_context is null then
2301 for i in c_gbl(pqh_gen_form.g_context) loop
2302 pqh_gen_form.g_gbl_context := i.context ;
2303 end loop;
2304 end if;
2305 end;
2306 --
2307 procedure chk_transaction_category (p_short_name in out nocopy varchar2,
2308 p_transaction_category_id in out nocopy varchar2,
2309 p_transaction_id in varchar2,
2310 p_member_cd out nocopy varchar2,
2311 p_name out nocopy varchar2)
2312 is
2313 cursor c_short_name is
2314 select transaction_category_id,
2315 member_cd,
2316 name
2317 from pqh_transaction_categories_vl
2318 where short_name = p_short_name
2319 and business_group_id is null ;
2320 --
2321 cursor c_txn_cat_id is
2322 select short_name,
2323 member_cd,
2324 name
2325 from pqh_transaction_categories_vl
2326 where transaction_category_id = p_transaction_category_id;
2327 --
2328 cursor c_txn_id is
2329 select transaction_category_id
2330 from pqh_copy_entity_txns
2331 where copy_entity_txn_id = p_transaction_id ;
2332
2333 l_short_name varchar2(30) := p_short_name;
2334 l_transaction_category_id number := p_transaction_category_id;
2335 begin
2336 if p_transaction_id is not null then
2337 for i in c_txn_id loop
2338 p_transaction_category_id := i.transaction_category_id ;
2339 end loop;
2340 end if;
2341 --
2342 if p_short_name is not null then
2343 for i in c_short_name loop
2344 p_transaction_category_id := i.transaction_category_id ;
2345 p_member_cd := i.member_cd ;
2346 p_name := i.name ;
2347 end loop;
2348 elsif p_transaction_category_id is not null then
2349 for i in c_txn_cat_id loop
2350 p_short_name := i.short_name ;
2351 p_member_cd := i.member_cd ;
2352 p_name := i.name ;
2353 end loop;
2354 end if;
2355 exception when others then
2356 p_short_name := l_short_name;
2357 p_transaction_category_id := l_transaction_category_id;
2358 p_member_cd := null;
2359 p_name := null;
2360 raise;
2361 end chk_transaction_category;
2362 --
2363 function get_transaction_type (p_transaction_category_id in number
2364 , p_context in varchar2)
2365 return varchar2
2366 is
2367 cursor c_tran is
2368 select function_type_cd
2369 from pqh_transaction_categories tct
2370 ,pqh_copy_entity_functions cef
2371 where tct.master_table_route_id = cef.table_route_id
2372 and cef.context = p_context
2373 and tct.transaction_category_id = p_transaction_category_id ;
2374 begin
2375 for i in c_tran loop
2376 return i.function_type_cd;
2377 end loop;
2378 return ('NULL');
2379 end get_transaction_type;
2380 --
2381 function my_con return varchar2 is
2382 begin
2383 --
2384 return (pqh_gen_form.g_context);
2385 --
2386 end;
2387 --
2388 function get_look (p_code in varchar2) return varchar2
2389 is
2390 --
2391 cursor c_look is
2392 select meaning from hr_lookups
2393 where lookup_type = 'PQH_GEN_LOV'
2394 and lookup_code = nvl(p_code,'NULL');
2395 --
2396 begin
2397 if p_code = g_code then
2398 return g_meaning ;
2399 end if;
2400 --
2401 for i in c_look loop
2402 g_code := p_code ;
2403 g_meaning := i.meaning ;
2404 return (i.meaning);
2405 end loop;
2406 --
2407 return('');
2408 end;
2409 --
2410 function kf(p_string in varchar2 ,
2411 p_delimiter in varchar2 ) return varchar2 is
2412 --
2413 l_xname varchar2(240);
2414 l_name varchar2(240);
2415 l_start number;
2416 l_end number;
2417 l_delimiter varchar2(1) := p_delimiter ;
2418 l_2delimiter varchar2(2);
2419 --
2420 begin
2421 if l_delimiter is not null then
2422 l_xname := p_string;
2423
2424 l_2delimiter := l_delimiter || l_delimiter ;
2425 if substr( l_xname, 1, 1) = l_delimiter then
2426 l_xname := '%' || l_xname;
2427 end if;
2428 while true
2429 loop
2430 if instr(l_xname, l_2delimiter) <> 0 then
2431 l_name := substr(l_xname, 1, to_number(instr(l_xname, l_2delimiter)) );
2432 l_name := l_name || '%' ;
2433 l_start := instr(l_xname, l_2delimiter) + 1;
2434 l_end := length(l_xname) - instr(l_xname, l_2delimiter);
2435 l_name := l_name || substr( l_xname, l_start, l_end);
2436 l_xname := l_name;
2437 else
2438 exit;
2439 end if;
2440 end loop;
2441 if substr( l_xname, length(l_xname), 1) = l_delimiter then
2442 l_xname := l_xname || '%';
2443 end if;
2444 return(l_xname);
2445 end if;
2446 --
2447 return(p_string);
2448 end kf;
2449 --
2450 procedure delete_source (p_validate in boolean
2451 , p_copy_entity_result_id in number
2452 , p_object_version_number in number
2453 , p_effective_date in date)
2454 is
2455 l_ovn number := p_object_version_number;
2456 cursor c_tgt is
2457 select copy_entity_result_id, object_version_number
2458 from pqh_copy_entity_results
2459 where src_copy_entity_result_id = p_copy_entity_result_id ;
2460 begin
2461 for i in c_tgt loop
2462 pqh_copy_entity_results_api.delete_copy_entity_result
2463 (p_validate => FALSE
2464 ,p_copy_entity_result_id => i.copy_entity_result_id
2465 ,p_object_version_number => i.object_version_number
2466 ,p_effective_date => p_effective_date );
2467 end loop;
2468 --
2469 pqh_copy_entity_results_api.delete_copy_entity_result
2470 (p_validate => p_validate
2471 ,p_copy_entity_result_id => p_copy_entity_result_id
2472 ,p_object_version_number => l_ovn
2473 ,p_effective_date => p_effective_date );
2474
2475 end delete_source;
2476 --
2477 procedure flip_selection (p_mode in varchar2,
2478 p_copy_entity_txn_id in number ,
2479 p_copy_entity_result_id in number default null ,
2480 p_block in varchar2 default 'SOURCE',
2481 p_select_value in varchar2 )
2482 is
2483 begin
2484 --
2485 if p_mode = 'INVERT' then
2486 update pqh_copy_entity_results
2487 set number_of_copies = decode(number_of_copies,0, nvl(p_select_value,1), 0)
2488 where copy_entity_txn_id = p_copy_entity_txn_id
2489 --and src_copy_entity_result_id = nvl(p_copy_entity_result_id, -99)
2490 and nvl(src_copy_entity_result_id,-99) = nvl(p_copy_entity_result_id, nvl(src_copy_entity_result_id,-99))
2491 and status not in ('COMPLETED', 'DPT_ERR')
2492 and result_type_cd = p_block ;
2493 elsif p_mode = 'NONE' then
2494 update pqh_copy_entity_results
2495 set number_of_copies = 0
2496 where copy_entity_txn_id = p_copy_entity_txn_id
2497 and nvl(src_copy_entity_result_id,-99) = nvl(p_copy_entity_result_id, nvl(src_copy_entity_result_id,-99))
2498 and status not in ('COMPLETED', 'DPT_ERR')
2499 and result_type_cd = p_block ;
2500 elsif p_mode = 'ALL' then
2501 update pqh_copy_entity_results
2502 set number_of_copies = nvl(p_select_value,1)
2503 where copy_entity_txn_id = p_copy_entity_txn_id
2504 and nvl(src_copy_entity_result_id,-99) = nvl(p_copy_entity_result_id, nvl(src_copy_entity_result_id,-99))
2505 and status not in ('COMPLETED', 'DPT_ERR')
2506 and result_type_cd = p_block ;
2507 end if;
2508 --
2509 end flip_selection;
2510 --
2511 function check_id_flex_struct ( p_id_flex_code varchar2,
2512 p_id_flex_num number ) return boolean is
2513 --
2514 -- declare cursor
2515 --
2516 cursor get_flex_struct IS
2517 select
2518 'Y'
2519 from fnd_compiled_id_flex_structs fcf,
2520 fnd_id_flex_structures fs
2521 where fcf.id_flex_code = p_id_flex_code
2522 and fcf.id_flex_num = p_id_flex_num
2523 and fs.id_flex_code = fcf.id_flex_code
2524 and fs.id_flex_num = fcf.id_flex_num
2525 and fs.dynamic_inserts_allowed_flag = 'Y';
2526 --
2527 l_struct_exists varchar2(1) := 'N';
2528 --
2529 BEGIN
2530 --
2531 -- get flex struct
2532 --
2533 open get_flex_struct;
2534 fetch get_flex_struct into l_struct_exists;
2535 close get_flex_struct;
2536 --
2537 -- check flex struct
2538 --
2539 if (l_struct_exists = 'Y') then
2540 return TRUE;
2541 else
2542 return FALSE;
2543 end if;
2544 --
2545 end check_id_flex_struct;
2546 --
2547 function context_bg return varchar2
2548 is
2549 --
2550 cursor c_bg is
2551 select context_business_group_id
2552 from pqh_copy_entity_txns
2553 where copy_entity_txn_id = pqh_gen_form.g_txn_id;
2554 begin
2555
2556 if pqh_gen_form.g_txn_id is not null then
2557 if g_private_txn_id is not null and
2558 g_private_bg_id is not null and
2559 g_private_txn_id = pqh_gen_form.g_txn_id then
2560
2561 return (g_private_bg_id) ;
2562 end if;
2563 --
2564 for i in c_bg loop
2565 g_private_bg_id := i.context_business_group_id;
2566 g_private_txn_id := pqh_gen_form.g_txn_id;
2567 return (i.context_business_group_id);
2568 end loop;
2569 /*Added the following else block as a part of bug#13707305*/
2570 else
2571 return fnd_profile.value('PER_BUSINESS_GROUP_ID');
2572 end if;
2573
2574 end context_bg;
2575 --
2576 procedure set_txn_id (p_txn_id in number) is
2577 begin
2578 pqh_gen_form.g_txn_id := p_txn_id ;
2579
2580 end ;
2581 --
2582 procedure set_dt (p_dt_mode in varchar2, p_dt_desc in varchar2) is
2583 l_upd boolean := true;
2584 l_updt boolean := true;
2585 cursor c_dt (p_type in varchar2) is
2586 select ddf_column_name, ddf_value_column_name
2587 from Pqh_special_attributes s
2588 ,pqh_txn_category_attributes c
2589 ,pqh_attributes a
2590 where a.attribute_id = c.attribute_id
2591 and c.txn_category_attribute_id = s.txn_category_attribute_id
2592 and a.enable_flag = 'Y'
2593 and c.select_flag = 'Y'
2594 and s.context = pqh_gen_form.g_gbl_context
2595 and s.attribute_type_cd = p_type
2596 and a.column_name like 'DATETRACK_MODE'
2597 and s.ddf_column_name is not null;
2598 --
2599 begin
2600 for i in c_dt('DISPLAY') loop
2601 if i.ddf_value_column_name is not null then
2602 execute immediate 'update pqh_copy_entity_results set '||i.ddf_value_column_name||' = '''||p_dt_desc
2603 ||''' where copy_entity_txn_id = '||to_char(pqh_gen_form.g_txn_id)
2604 ||' and result_type_cd = ''TARGET'''
2605 ||' and number_of_copies = 1 and status in (''TGT_P'',''TGT_ERR'')' ;
2606 end if;
2607 execute immediate 'update pqh_copy_entity_results set '||i.ddf_column_name||' = '''||p_dt_mode
2608 ||''' where copy_entity_txn_id = '||to_char(pqh_gen_form.g_txn_id)
2609 ||' and result_type_cd = ''TARGET'''
2610 ||' and number_of_copies = 1 and status in (''TGT_P'',''TGT_ERR'')' ;
2611
2612 l_upd := FALSE;
2613 end loop; --c_dt
2614 --
2615 if l_upd then
2616 for i in c_dt('SELECT') loop
2617 if i.ddf_value_column_name is not null then
2618 execute immediate 'update pqh_copy_entity_results set '||i.ddf_value_column_name||' = '''||p_dt_desc
2619 ||''' where copy_entity_txn_id = '||to_char(pqh_gen_form.g_txn_id)
2620 ||' and result_type_cd = ''TARGET'''
2621 ||' and number_of_copies = 1 and status in (''TGT_P'',''TGT_ERR'')' ;
2622 end if;
2623 execute immediate 'update pqh_copy_entity_results set '||i.ddf_column_name||' = '''||p_dt_mode
2624 ||''' where copy_entity_txn_id = '||to_char(pqh_gen_form.g_txn_id)
2625 ||' and result_type_cd = ''TARGET'''
2626 ||' and number_of_copies = 1 and status in (''TGT_P'',''TGT_ERR'')' ;
2627
2628 l_upd := FALSE;
2629 end loop; --c_dt
2630 end if;
2631 --
2632 if l_upd then
2633 for i in c_dt('PARAMETER') loop
2634 if i.ddf_value_column_name is not null then
2635 execute immediate 'update pqh_copy_entity_results set '||i.ddf_value_column_name||' = '''||p_dt_desc
2636 ||''' where copy_entity_txn_id = '||to_char(pqh_gen_form.g_txn_id)
2637 ||' and result_type_cd = ''TARGET'''
2638 ||' and number_of_copies = 1 and status in (''TGT_P'',''TGT_ERR'')' ;
2639 end if;
2640 execute immediate 'update pqh_copy_entity_results set '||i.ddf_column_name||' = '''||p_dt_mode
2641 ||''' where copy_entity_txn_id = '||to_char(pqh_gen_form.g_txn_id)
2642 ||' and result_type_cd = ''TARGET'''
2643 ||' and number_of_copies = 1 and status in (''TGT_P'',''TGT_ERR'')' ;
2644
2645 l_upd := FALSE;
2646 end loop; --c_dt
2647 end if;
2648 --
2649 end set_dt;
2650 --
2651 function check_valueset_type (p_valueset_id in varchar2) return varchar2 is
2652
2653 cursor c_vset is
2654 select validation_type
2655 from fnd_flex_value_sets
2656 where flex_value_set_id = p_valueset_id;
2657 begin
2658
2659 --return if valueset id is null
2660 if p_valueset_id is null then
2661 return 'Y' ;
2662 end if;
2663
2664 hr_utility.set_location('value of valueset_id : '||p_valueset_id, 11);
2665 for i in c_vset loop
2666 if i.validation_type in ('N') then -- , 'I') then (commented I..can be taken up for enhancement)
2667 return 'Y';
2668 end if;
2669 end loop;
2670
2671 return 'N';
2672
2673 end check_valueset_type;
2674 --
2675 function get_segment(p_col in varchar2) return varchar2 is
2676 l_col varchar2(200) ;
2677 begin
2678
2679 l_col := upper(p_col);
2680 l_col := rtrim(substr(l_col,instr(l_col,'SEGMENT'),instr(substr(l_col, instr(l_col, 'SEGMENT'))||' ',' ')));
2681 return (substr(l_col,1,instr(l_col||'_','_')-1));
2682
2683 end get_segment;
2684 --
2685 END PQH_GEN_FORM;