1 Package Body pqh_refresh_data AS
2 /* $Header: pqrefdat.pkb 120.0 2005/05/29 02:26:24 appldev noship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 g_package varchar2(33) := ' pqh_refresh_data.'; -- Global package name
8 --
9 -- ----------------------------------------------------------------------------
10 -- | Private Variables and functions for string parsing
11 -- ----------------------------------------------------------------------------
12 --
13
14 /* Package variables used repeatedly throughout the body. */
15 len_string NUMBER;
16 start_loc NUMBER;
17 next_loc NUMBER;
18 a_blank CONSTANT VARCHAR2(3) := '';
19
20 /*--------------------- Private Modules ---------------------------
21 || The following functions are available only to other modules in
22 || package.
23 ------------------------------------------------------------------*/
24
25 /*------------------------------------- ---------------------------
26 || Function : a_delimiter
27 ||
28 ------------------------------------------------------------------*/
29
30
31 FUNCTION a_delimiter
32 (character_in IN VARCHAR2,
33 delimiters_in IN VARCHAR2 := std_delimiters)
34 RETURN BOOLEAN
35 /*
36 || Returns TRUE if the character passsed into the function is found
37 || in the list of delimiters.
38 */
39 IS
40 BEGIN
41 RETURN INSTR (delimiters_in, character_in) > 0;
42 END;
43
44
45 /*------------------------------------- ---------------------------
46 || Function : string_length
47 ||
48 ------------------------------------------------------------------*/
49
50
51 FUNCTION string_length (string_in IN VARCHAR2)
52 RETURN INTEGER
53 IS
54 BEGIN
55 RETURN LENGTH (LTRIM (RTRIM (string_in)));
56 END;
57
58 /*------------------------------------- ---------------------------
59 || Function : get_legislation_code
60 ||
61 ------------------------------------------------------------------*/
62
63
64 FUNCTION get_legislation_code (p_business_group_id IN number)
65 RETURN VARCHAR2
66 IS
67 l_leg_code varchar2(30);
68 cursor c1 is
69 select legislation_code
70 from per_business_groups
71 where business_group_id = p_business_group_id;
72 BEGIN
73 open c1;
74 fetch c1 into l_leg_code;
75 close c1;
76 return l_leg_code;
77 END;
78
79 /*------------------------------------- ---------------------------
80 || Function : next_atom_loc
81 ||
82 ------------------------------------------------------------------*/
83
84
85 FUNCTION next_atom_loc
86 (string_in IN VARCHAR2,
87 start_loc_in IN NUMBER,
88 scan_increment_in IN NUMBER := +1)
89 /*
90 || The next_atom_loc function returns the location
91 || in the string of the starting point of the next atomic (from the
92 || start location). The function scans forward if scan_increment_in is
93 || +1, otherwise it scans backwards through the string. Here is the
94 || logic to determine when the next atomic starts:
95 ||
96 || 1. If current atomic is a delimiter (if, that is, the character
97 || at the start_loc_in of the string is a delimiter), then the
98 || the next character starts the next atomic since all
99 || delimiters are a single character in length.
100 ||
101 || 2. If current atomic is a word (if, that is, the character
102 || at the start_loc_in of the string is a delimiter), then the
103 || next atomic starts at the next delimiter. Any letters or
104 || numbers in between are part of the current atomic.
105 ||
106 || So I loop through the string a character at a time and apply these
107 || tests. I also have to check for end of string. If I scan forward
108 || the end of string comes when the SUBSTR which pulls out the next
109 || character returns NULL. If I scan backward, then the end of the
110 || string comes when the location is less than 0.
111 */
112 RETURN NUMBER
113 IS
114 /* Boolean variable which uses private function to determine
115 || if the current character is a delimiter or not.
116 */
117 was_a_delimiter BOOLEAN :=
118 a_delimiter (SUBSTR (string_in, start_loc_in, 1));
119
120 /* If not a delimiter, then it was a word. */
121 was_a_word BOOLEAN := NOT was_a_delimiter;
122
123 /* The next character scanned in the string */
124 next_char VARCHAR2(1);
125 /*
126 || The value returned by the function. This location is the start
127 || of the next atomic found. Initialize it to next character,
128 || forward or backward depending on increment.
129 */
130 return_value NUMBER := start_loc_in + scan_increment_in;
131 BEGIN
132 LOOP
133 -- Extract the next character.
134 next_char := SUBSTR (string_in, return_value, 1);
135
136 -- Exit the loop if:
137 EXIT WHEN
138 /* On a delimiter, since that is always an atomic */
139 a_delimiter (next_char)
140 OR
141 /* Was a delimiter, but am now in a word. */
142 (was_a_delimiter AND NOT a_delimiter (next_char))
143 OR
144 /* Reached end of string scanning forward. */
145 next_char IS NULL
146 OR
147 /* Reached beginning of string scanning backward. */
148 return_value < 0;
149
150 /* Shift return_value to move the next character. */
151 return_value := return_value + scan_increment_in;
152 END LOOP;
153
154 -- If the return_value is negative, return 0, else the return_value
155 RETURN GREATEST (return_value, 0);
156 END;
157
158
159 /*----------------------------------------------------------------
160 || PROCEDURE : increment_counter
161 ||
162 ------------------------------------------------------------------*/
163
164
165 PROCEDURE increment_counter
166 (counter_inout IN OUT NOCOPY NUMBER,
167 count_type_in IN VARCHAR2,
168 atomic_in IN CHAR)
169 /*
170 || The increment_counter procedure is used by nth_atomic and
171 || number_of_atomics to add to the count of of atomics. Since you
172 || can request a count by ALL atomics, just the WORD atomics or
173 || just the DELIMITER atomics. I use the a_delimiter function to
174 || decide whether I should add to the counter. This is not a terribly
175 || complex procedure. I bury this logic into a separate module,
179 IS
176 || however, to make it easier to read and debug the main body of
177 || the programs.
178 */
180 l_counter_inout number := counter_inout;
181 BEGIN
182 IF count_type_in = 'ALL' OR
183 (count_type_in = 'WORD' AND NOT a_delimiter (atomic_in)) OR
184 (count_type_in = 'DELIMITER' AND a_delimiter (atomic_in))
185 THEN
186 counter_inout := counter_inout + 1;
187 END IF;
188 exception when others then
189 counter_inout := l_counter_inout;
190 raise;
191 END increment_counter;
192
193
194
195 --
196 -- End of string parsing private functions
197 --
198
199
200 /*----------------------------------------------------------------
201 ||
202 || PROCEDURE : refresh_data
203 || This is the MAIN procedure which calls the others
204 ------------------------------------------------------------------*/
205
206
207
208 PROCEDURE refresh_data
209 ( p_txn_category_id IN pqh_transaction_categories.transaction_category_id%TYPE,
210 p_txn_id IN number,
211 p_refresh_criteria IN varchar2,
212 p_items_changed OUT NOCOPY varchar2
213 )
214 IS
215 -- local variables
216 --
217 l_proc varchar2(72) := g_package||'refresh_data';
218 l_txn_id number;
219 l_txn_tab_id pqh_table_route.table_route_id%TYPE;
220 l_shd_tab_id pqh_table_route.table_route_id%TYPE;
221 l_mas_tab_id pqh_table_route.table_route_id%TYPE;
222 l_column_name pqh_attributes.column_name%TYPE;
223 l_attribute_name pqh_attributes.attribute_name%TYPE;
224 l_column_prompt varchar2(100);
225 l_refresh_flag pqh_txn_category_attributes.refresh_flag%TYPE;
226 l_column_type pqh_attributes.column_type%TYPE;
227 l_from_clause_txn pqh_table_route.from_clause%TYPE;
228 l_where_clause_txn pqh_table_route.where_clause%TYPE;
229 l_rep_where_clause_txn pqh_table_route.where_clause%TYPE;
230 l_from_clause_shd pqh_table_route.from_clause%TYPE;
231 l_where_clause_shd pqh_table_route.where_clause%TYPE;
232 l_rep_where_clause_shd pqh_table_route.where_clause%TYPE;
233 l_from_clause_main pqh_table_route.from_clause%TYPE;
234 l_where_clause_main pqh_table_route.where_clause%TYPE;
235 l_rep_where_clause_main pqh_table_route.where_clause%TYPE;
236 l_select_stmt t_where_clause_typ;
237 l_tot_txn_columns NUMBER;
238 l_tot_txn_rows NUMBER;
239 l_all_txn_rows_array DBMS_SQL.VARCHAR2_TABLE;
240 l_txn_row_cnt NUMBER := 0;
241 l_ordered_txn_row DBMS_SQL.VARCHAR2_TABLE;
242 l_tot_shd_columns NUMBER;
243 l_tot_shd_rows NUMBER;
244 l_all_shd_rows_array DBMS_SQL.VARCHAR2_TABLE;
245 l_tot_main_columns NUMBER;
246 l_tot_main_rows NUMBER;
247 l_all_main_rows_array DBMS_SQL.VARCHAR2_TABLE;
248 l_legislation_code varchar2(30)
249 := get_legislation_code(fnd_profile.value('PER_BUSINESS_GROUP_ID'));
250 type t_string is table of pqh_attributes_tl.attribute_name%type
251 index by binary_integer;
252 l_change_items t_string;
253 l_chg_items_index integer := 0;
254 l_found boolean := false;
255 --
256 -- BINARY_INTEGER
257 i BINARY_INTEGER:= 1; -- for column_name and refresh_flag cursor
258
259
260 -- This cursor will get the list of all tables to be refreshed
261 CURSOR c1_table_lists IS
262 SELECT DISTINCT tca.transaction_table_route_id,
263 tr.shadow_table_route_id,
264 att.master_table_route_id
265 FROM pqh_attributes att, pqh_table_route tr,
266 pqh_txn_category_attributes tca
267 WHERE tca.transaction_table_route_id = tr.table_route_id
268 AND att.attribute_id = tca.attribute_id
269 AND tca.transaction_category_id = p_txn_category_id
270 AND tr.shadow_table_route_id IS NOT NULL
271 AND tr.table_alias = DECODE(p_refresh_criteria, 'A', tr.table_alias,
272 p_refresh_criteria);
273
274 -- This cursor will get the list of all column_names for the current txn
275 -- and master tables
276 CURSOR c2_column_names ( p_txn_tab_id IN pqh_table_route.table_route_id%TYPE,
277 p_mas_tab_id IN pqh_table_route.table_route_id%TYPE) IS
278 SELECT att.refresh_col_name, nvl(tca.refresh_flag,'N') ,
279 att.column_type,
280 nvl(at2.attribute_name, att.attribute_name) attribute_name
281 FROM pqh_attributes_vl att, pqh_txn_category_attributes tca,
282 pqh_attributes_vl at2
283 WHERE att.attribute_id = tca.attribute_id
284 AND att.column_name = at2.column_name(+)
285 AND att.master_table_route_id = at2.master_table_route_id(+)
286 AND at2.legislation_code(+) = l_legislation_code
287 AND tca.transaction_category_id = p_txn_category_id
288 AND tca.transaction_table_route_id = p_txn_tab_id
289 AND att.master_table_route_id = p_mas_tab_id
290 ORDER BY tca.refresh_flag DESC, att.attribute_name;
291
292 -- This cursor will get the FROM and WHERE columns from pqh_table_route
293 CURSOR c3_from_where ( p_tab_id IN pqh_table_route.table_route_id%TYPE ) IS
294 SELECT tr.from_clause, tr.where_clause
295 FROM pqh_table_route tr
296 WHERE tr.table_route_id = p_tab_id ;
297
298 BEGIN
299
300 -- hr_utility.trace_on;
301 hr_utility.set_location('Entering: '||l_proc, 5);
302
303 -- populate the local variable l_txn_id and global g_txn_category_id with IN param value
304 -- These variables are passed to some procedures
305
306 l_txn_id := p_txn_id;
307 g_txn_category_id := p_txn_category_id;
308
309 hr_utility.set_location('Transaction Id: '||l_txn_id, 6);
310 hr_utility.set_location('Transaction Category Id: '||g_txn_category_id, 7);
311 hr_utility.set_location('Refresh Criteria: '||p_refresh_criteria, 8);
312
313
314 OPEN c1_table_lists;
315 LOOP
316 -- This is the MAIN LOOP which has table lists to be Refreshed
317 hr_utility.set_location('Inside MAIN LOOP of table list c1_table_lists', 9);
318
319 -- Initialize all the variables for each table set
320 i := 1; -- for column_name, type and refresh_flag cursor
321 l_select_stmt := '';
322 l_tot_txn_columns := 0;
323 l_tot_txn_rows := 0;
324 l_txn_row_cnt := 0;
325 l_tot_shd_columns := 0;
326 l_tot_shd_rows := 0;
327 l_tot_main_columns := 0;
328 l_tot_main_rows := 0;
329
330 -- initialize all the PL/SQL tables
331 l_all_txn_rows_array.DELETE;
332 l_ordered_txn_row.DELETE;
333 l_all_shd_rows_array.DELETE;
334 l_all_main_rows_array.DELETE;
335 g_refresh_tab.DELETE;
336 g_refresh_tab_all.DELETE;
337
338 FETCH c1_table_lists INTO l_txn_tab_id, l_shd_tab_id, l_mas_tab_id ;
339 EXIT WHEN c1_table_lists%NOTFOUND;
340
341 hr_utility.set_location('Txn Tab Id:'||l_txn_tab_id, 10);
342 hr_utility.set_location('Shd Tab Id:'||l_shd_tab_id, 11);
343 hr_utility.set_location('Main Tab Id:'||l_mas_tab_id, 12);
344
345 -- open the column_name cursor and populate the arrys columns 1 , 2 and 3
346 OPEN c2_column_names ( l_txn_tab_id , l_mas_tab_id );
347 LOOP
348 -- This loop will fetch all the column_name and refresh_flag into array
349
350 FETCH c2_column_names INTO l_column_name , l_refresh_flag , l_column_type, l_attribute_name;
351 EXIT WHEN c2_column_names%NOTFOUND;
352
353 hr_utility.set_location('Col Name'||l_column_name, 13);
354 hr_utility.set_location('Refresh Flag'||l_refresh_flag, 14);
355 hr_utility.set_location('Col Type'||l_column_type, 15);
356 hr_utility.set_location('Attr Name'||l_attribute_name, 15);
357
358 -- populate the arrys col1 , col2 and col3
359 g_refresh_tab(i).column_name := l_column_name;
360 g_refresh_tab(i).refresh_flag := l_refresh_flag;
361 g_refresh_tab(i).column_type := l_column_type;
362 g_refresh_tab(i).attribute_name := l_attribute_name;
363 i := i + 1;
364 END LOOP; -- for column_name , type and refresh flags
365 CLOSE c2_column_names;
366
367
368 -- array g_refresh_tab has all the column_names of txn table in col1
369 --
370 -- TRANSACTION TABLE
371 --
372 -- Build a dynamic select statement for the TRANSACTION TABLE
373
374 build_dynamic_select
375 ( p_flag => 'A',
376 p_select_stmt => l_select_stmt,
377 p_tot_columns => l_tot_txn_columns );
378
379
380 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,1,50), 16);
381 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,51,50), 16);
382 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,101,50), 16);
383 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,151,50), 16);
384 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,201,50), 16);
385 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,251,50), 16);
386 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,301,50), 16);
387 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,351,50), 16);
388 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,401,50), 16);
389 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,451,50), 16);
390 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,501,50), 16);
391 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,551,50), 16);
392 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,601,50), 16);
393 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,651,50), 16);
394 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,701,50), 16);
395 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,751,50), 16);
396 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,801,50), 16);
397 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,851,50), 16);
398 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,901,50), 16);
399 hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,951,50), 16);
400 hr_utility.set_location('Txn Total Columns : '||l_tot_txn_columns, 17);
401
402
403 -- get the FROM and WHERE clause from pqh_table_route for TRANSACTION TABLE
404
405 OPEN c3_from_where ( l_txn_tab_id ) ;
406 LOOP
407 -- this gets the from and where clause , one row only
408 FETCH c3_from_where INTO l_from_clause_txn, l_where_clause_txn;
409 EXIT WHEN c3_from_where%NOTFOUND;
410 END LOOP;
411 CLOSE c3_from_where ;
412
413 hr_utility.set_location('Txn From : '||l_from_clause_txn, 18);
414 hr_utility.set_location('Txn Where : '||l_where_clause_txn, 19);
415
416 -- replace the WHERE clause txn_id parameter with the actual value
417 -- for TRANSACTION TABLE
418 replace_where_params
419 ( p_where_clause_in => l_where_clause_txn,
420 p_txn_tab_flag => 'Y',
421 p_txn_id => l_txn_id,
422 p_where_clause_out => l_rep_where_clause_txn );
423
424 hr_utility.set_location('Txn Replaced Where : '||l_rep_where_clause_txn, 20);
425
426
427 -- get ALL ROWS for the TRANSACTION TABLE
428 get_all_rows
429 (p_select_stmt => l_select_stmt,
430 p_from_clause => l_from_clause_txn,
431 p_where_clause => l_rep_where_clause_txn,
432 p_total_columns => l_tot_txn_columns,
433 p_total_rows => l_tot_txn_rows,
434 p_all_txn_rows => l_all_txn_rows_array );
435
436 /*
437 we now have all the txn rows in the l_all_txn_rows_array
438 the array is populated in the following way
439 eg: TXN table has 3 rows and 3 columns then array has following value
440 r1.c1, r2.c1, r3.c1, r2.c1, r2.c2, r2.c3, r3.c1, r3.c2, r3.c3
441 We will get each row for this random array and process the shadow and main
442 tables
443
444 THIS IS THE LOOP FOR EACH ROW IN TRANSACTION TABLE
445
446 */
447
448 -- LOOP FOR EACH ROW IN TRANSACTION TABLE
449 FOR row_no in 1..NVL(l_tot_txn_rows,-1)
450 LOOP
451 l_txn_row_cnt := row_no;
452 -- since the array is random, we build a ordered row
453 FOR col_no in 1..l_tot_txn_columns
454 LOOP
455 l_ordered_txn_row(col_no) := NVL(l_all_txn_rows_array(l_txn_row_cnt),'');
456 l_txn_row_cnt := l_txn_row_cnt + l_tot_txn_rows;
457 END LOOP; -- for all txn columns
458
462 FOR k in NVL(l_ordered_txn_row.FIRST,0)..NVL(l_ordered_txn_row.LAST,-1)
459 -- the above loop gives ordered value for each txn row in the l_ordered_txn_row
460 -- array
461 -- populate the g_refresh_tab.txn_val column with these values
463 LOOP
464 g_refresh_tab(k).txn_val := l_ordered_txn_row(k);
465 -- dbms_output.put_line('Record : '||row_no||' Values i '||i||' '||a(i) );
466 END LOOP;
467
468 -- g_refresh_tab array now has values of txn record in the txn_val column
469
470
471 /*
472 SHADOW TABLE
473 */
474
475
476 -- Build a dynamic select statement for the SHADOW TABLE
477
478 build_dynamic_select
479 ( p_flag => 'R',
480 p_select_stmt => l_select_stmt,
481 p_tot_columns => l_tot_shd_columns );
482
483
484 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,1,50), 16);
485 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,51,50), 16);
486 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,101,50), 16);
487 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,151,50), 16);
488 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,201,50), 16);
489 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,251,50), 16);
490 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,301,50), 16);
491 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,351,50), 16);
492 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,401,50), 16);
493 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,451,50), 16);
494 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,501,50), 16);
495 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,551,50), 16);
496 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,601,50), 16);
497 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,651,50), 16);
498 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,701,50), 16);
499 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,751,50), 16);
500 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,801,50), 16);
501 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,851,50), 16);
502 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,901,50), 16);
503 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,951,50), 16);
504 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,1001,50), 16);
505 hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,1051,50), 16);
506
507 -- get the FROM and WHERE clause from pqh_table_route for SHADOW TABLE
508
509 OPEN c3_from_where ( l_shd_tab_id ) ;
510 LOOP
511 -- this gets the from and where clause , one row only
512 FETCH c3_from_where INTO l_from_clause_shd, l_where_clause_shd;
513 EXIT WHEN c3_from_where%NOTFOUND;
514 END LOOP;
515 CLOSE c3_from_where ;
516
517 hr_utility.set_location('Shd From : '||l_from_clause_shd, 18);
518 hr_utility.set_location('Shd Where : '||l_where_clause_shd, 19);
519
520 -- replace the WHERE clause parameters with the actual value
521 -- for SHADOW TABLE
522 replace_where_params
523 ( p_where_clause_in => l_where_clause_shd,
524 p_txn_tab_flag => 'N',
525 p_txn_id => l_txn_id,
526 p_where_clause_out => l_rep_where_clause_shd );
527
528 hr_utility.set_location('Shd Replaced Where : '||l_rep_where_clause_shd, 20);
529
530 -- get ALL ROWS for the SHADOW TABLE
531 -- THERE WILL BE ONLY ONW ROW RETURNED for shadow and main tables as
532 -- the where clause has primary key columns
533
534 get_all_rows
535 (p_select_stmt => l_select_stmt,
536 p_from_clause => l_from_clause_shd,
537 p_where_clause => l_rep_where_clause_shd,
538 p_total_columns => l_tot_shd_columns,
539 p_total_rows => l_tot_shd_rows,
540 p_all_txn_rows => l_all_shd_rows_array );
541
542 -- the l_all_shd_rows_array has ONLY ONE ROW
543 -- populate the g_refresh_tab.shadow_val column with these values
544 FOR k in NVL(l_all_shd_rows_array.FIRST,0)..NVL(l_all_shd_rows_array.LAST,-1)
545 LOOP
546 g_refresh_tab(k).shadow_val := l_all_shd_rows_array(k);
547 -- dbms_output.put_line('Record : '||row_no||' Values i '||i||' '||a(i) );
548 END LOOP;
549
550 -- g_refresh_tab array now has values of txn record in the shadow_val column
551
552
553
554 /*
555 MAIN TABLE
556 */
557
558
559 -- Build a dynamic select statement for the MAIN TABLE
560
561 build_dynamic_select
562 ( p_flag => 'R',
563 p_select_stmt => l_select_stmt,
564 p_tot_columns => l_tot_main_columns );
565
566
567
568 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,1,50), 16);
569 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,51,50), 16);
570 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,101,50), 16);
571 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,151,50), 16);
572 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,201,50), 16);
573 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,251,50), 16);
574 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,301,50), 16);
575 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,351,50), 16);
576 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,401,50), 16);
577 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,451,50), 16);
578 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,501,50), 16);
579 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,551,50), 16);
580 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,601,50), 16);
581 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,651,50), 16);
582 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,701,50), 16);
583 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,751,50), 16);
584 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,801,50), 16);
585 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,851,50), 16);
586 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,901,50), 16);
587 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,951,50), 16);
588 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,1001,50), 16);
589 hr_utility.set_location('Main Sel : '||substr(l_select_stmt,1051,50), 16);
590
591 -- get the FROM and WHERE clause from pqh_table_route for MAIN TABLE
592
593 OPEN c3_from_where ( l_mas_tab_id ) ;
594 LOOP
595 -- this gets the from and where clause , one row only
596 FETCH c3_from_where INTO l_from_clause_main, l_where_clause_main;
597 EXIT WHEN c3_from_where%NOTFOUND;
598 END LOOP;
599 CLOSE c3_from_where ;
600
601 hr_utility.set_location('Main From : '||l_from_clause_main, 18);
602 hr_utility.set_location('Main Where : '||substr(l_where_clause_main,1,50), 19);
603 hr_utility.set_location('Main Where : '||substr(l_where_clause_main,51,50), 19);
604 hr_utility.set_location('Main Where : '||substr(l_where_clause_main,101,50), 19);
605 hr_utility.set_location('Main Where : '||substr(l_where_clause_main,151,50), 19);
606 hr_utility.set_location('Main Where : '||substr(l_where_clause_main,201,50), 19);
607 hr_utility.set_location('Main Where : '||substr(l_where_clause_main,251,50), 19);
608
609 -- replace the WHERE clause parameters with the actual value
610 -- for MAIN TABLE
611 replace_where_params
612 ( p_where_clause_in => l_where_clause_main,
613 p_txn_tab_flag => 'N',
614 p_txn_id => l_txn_id,
615 p_where_clause_out => l_rep_where_clause_main );
616
617
618 hr_utility.set_location('Man Rep where : '||substr(l_rep_where_clause_main,1,50),30);
619
620
621 -- get ALL ROWS for the MAIN TABLE
622 -- THERE WILL BE ONLY ONW ROW RETURNED for shadow and main tables as
623 -- the where clause has primary key columns
624
625 get_all_rows
626 (p_select_stmt => l_select_stmt,
627 p_from_clause => l_from_clause_main,
628 p_where_clause => l_rep_where_clause_main,
629 p_total_columns => l_tot_main_columns,
630 p_total_rows => l_tot_main_rows,
631 p_all_txn_rows => l_all_main_rows_array );
632
633 -- the l_all_main_rows_array has ONLY ONE ROW
634 -- populate the g_refresh_tab.main_val column with these values
635 FOR k in NVL(l_all_main_rows_array.FIRST,0)..NVL(l_all_main_rows_array.LAST,-1)
636 LOOP
637 g_refresh_tab(k).main_val := l_all_main_rows_array(k);
638 -- dbms_output.put_line('Record : '||row_no||' Values i '||i||' '||a(i) );
639 END LOOP;
640
641 -- g_refresh_tab array now has values of txn record in the main_val column
642
643
644 /*
645 Call the compute_updt_flag procedure which will loop thru the g_refresh_tab
646 and populate the updt_flag.
647
648 */
649 compute_updt_flag;
650
651 /*
652 LOOP thru the g_refresh_tab and update the necessary columns with new values
653 */
654
655 FOR k IN NVL(g_refresh_tab.FIRST,0)..NVL(g_refresh_tab.LAST,-1)
656 LOOP
657 IF g_refresh_tab(k).updt_flag = 'Y' THEN
658 -- call the update_table procedure to updt tables
659 update_tables
660 (p_column_name => g_refresh_tab(k).column_name,
661 p_column_type => g_refresh_tab(k).column_type,
662 p_column_val => g_refresh_tab(k).main_val,
663 p_from_clause_txn => l_from_clause_txn,
664 p_from_clause_shd => l_from_clause_shd,
665 p_rep_where_clause_shd => l_rep_where_clause_shd);
666 --
667 l_column_prompt := g_refresh_tab(k).attribute_name;
668 l_found := false;
669 for i in 1 .. l_chg_items_index loop
670 if l_change_items(i) = l_column_prompt then
671 l_found := true;
672 end if;
673 exit when l_found;
674 end loop;
675 if not l_found then
676 l_chg_items_index := l_chg_items_index +1;
677 l_change_items(l_chg_items_index) := l_column_prompt;
678 if p_items_changed is null then
679 p_items_changed := l_column_prompt;
680 else
681 p_items_changed :=
682 p_items_changed || fnd_global.local_chr(10) || l_column_prompt;
683 end if;
684 end if;
685 --
686 END IF;
687 END LOOP; -- thru the table
688
689 END LOOP; -- For ALL ROWS in TRANSACTION TABLE
690
691 /*
692 for forms purpose we will take a backup of g_refresh_tab into g_refresh_bak
693 g_refresh_bak will be used by form
694 */
695
696 FOR m IN NVL(g_refresh_tab.FIRST,0)..NVL(g_refresh_tab.LAST,-1)
697 LOOP
698 g_refresh_bak(m).column_name := g_refresh_tab(m).column_name;
699 g_refresh_bak(m).column_type := g_refresh_tab(m).column_type;
700 g_refresh_bak(m).refresh_flag := g_refresh_tab(m).refresh_flag;
701 g_refresh_bak(m).txn_val := g_refresh_tab(m).txn_val;
702 g_refresh_bak(m).shadow_val := g_refresh_tab(m).shadow_val;
703 g_refresh_bak(m).main_val := g_refresh_tab(m).main_val;
704 g_refresh_bak(m).updt_flag := g_refresh_tab(m).updt_flag;
705
706 END LOOP; -- for copy to backup
707
708 END LOOP; -- main loop of tables to be refreshed
709 CLOSE c1_table_lists;
710
711 -- commit the work;
712 -- commit;
713 hr_utility.set_location('Leaving:'||l_proc, 1000);
714 -- hr_utility.trace_off;
715
716
717 EXCEPTION
718 WHEN OTHERS THEN
719 p_items_changed := null;
720 -- hr_utility.trace_off;
721 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
722 hr_utility.set_message_token('ROUTINE', l_proc);
723 hr_utility.set_message_token('REASON', SQLERRM);
724 hr_utility.raise_error;
725 END refresh_data;
726
727
728 /*----------------------------------------------------------------
729 || PROCEDURE : build_dynamic_select
730 ||
731 ------------------------------------------------------------------*/
732
733 PROCEDURE build_dynamic_select
734 ( p_flag IN VARCHAR2,
735 p_select_stmt OUT NOCOPY t_where_clause_typ,
736 p_tot_columns OUT NOCOPY NUMBER ) IS
737
738 /*
739 p_flag has 2 values
740 'A' means select all columns from the array , this is for txn table select
741 'R' means select ONLY those columns where refresh_flag i.e column 2 in array
742 is 'Y' , this is for shadow and master table select
743 Depending on the column_type we will format the front and back packing string
744 */
745
746 --
747 -- local variables
748 --
749 l_proc varchar2(72) := g_package||'build_dynamic_select';
750 l_front VARCHAR2(100);
751 l_back VARCHAR2(100);
752
753
754
755 BEGIN
756 hr_utility.set_location('Entering:'||l_proc, 5);
757
758 -- intitalize the out variable
759 p_select_stmt := 'SELECT ';
760 p_tot_columns := 0;
761
762 -- loop thru the array and keep appending column 1 into string g_refresh_tab(i).column_name
763 FOR i IN NVL(g_refresh_tab.FIRST,0)..NVL(g_refresh_tab.LAST,-1)
764 LOOP
765 -- form the front and back packing string
766 IF g_refresh_tab(i).column_type = 'D' THEN
767 l_front := 'TO_CHAR(';
768 l_back := ',''RRRRMMDD HH24MISS'')';
769 ELSIF g_refresh_tab(i).column_type = 'N' THEN
770 l_front := 'TO_CHAR(';
771 l_back := ')';
772 ELSE
773 l_front := ' ';
774 l_back := ' ';
775 END IF;
776
777 IF p_flag = 'A' THEN
778 -- append all columns
779 p_select_stmt := p_select_stmt||
780 l_front||g_refresh_tab(i).column_name||l_back||' ';
781 -- increment the total no of columns
782 p_tot_columns := p_tot_columns + 1;
783 -- if this is not the last column_name append a comma at end
784 -- IF i <> g_refresh_tab.LAST THEN
785 p_select_stmt := p_select_stmt||' ,';
786 -- END IF;
787 ELSE
788 -- append only if refresh flag is 'Y'
789 IF g_refresh_tab(i).refresh_flag = 'Y' THEN
790 p_select_stmt := p_select_stmt||
791 l_front||g_refresh_tab(i).column_name||l_back||' ';
792 -- increment the total no of columns
793 p_tot_columns := p_tot_columns + 1;
794 -- if this is not the last column_name append a comma at end
795 -- IF i <> g_refresh_tab.LAST THEN
796 p_select_stmt := p_select_stmt||' ,';
797 -- END IF; -- for last row check
798 END IF; -- for refresh_flag is Y
799 END IF; -- for p_flag
800 END LOOP;
801
802 -- remove the last comma
803 p_select_stmt := rtrim(p_select_stmt,',');
804
805 hr_utility.set_location('Leaving:'||l_proc, 1000);
806
807 EXCEPTION
808 WHEN OTHERS THEN
809 p_select_stmt := null;
810 p_tot_columns := null;
811 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
812 hr_utility.set_message_token('ROUTINE', l_proc);
813 hr_utility.set_message_token('REASON', SQLERRM);
814 hr_utility.raise_error;
815 END build_dynamic_select;
816
817 /*----------------------------------------------------------------
818 || FUNCTION : ret_value_from_glb_table
819 ||
820 ------------------------------------------------------------------*/
821 FUNCTION ret_value_from_glb_table(p_index in number)
822 RETURN VARCHAR2 IS
823 --
824 l_proc varchar2(72) := g_package||'ret_value_from_glb_table';
825 BEGIN
826 --
827 hr_utility.set_location('Entering:'||l_proc, 5);
828 --
829 return pqh_refresh_data.g_refresh_tab_all(p_index).txn_val;
830 --
831 hr_utility.set_location('Entering:'||l_proc, 5);
832 --
833 exception when others then
834 return null;
835 --
836 END;
837 --
838 /*----------------------------------------------------------------
839 || FUNCTION : get_value_from_array
840 ||
841 ------------------------------------------------------------------*/
842 --
843 FUNCTION get_value_from_array ( p_column_name IN pqh_attributes.column_name%TYPE )
844 RETURN VARCHAR2 IS
845
846 -- local variables
847 --
848 l_proc varchar2(72) := g_package||'get_value_from_array';
849 l_col_val VARCHAR2(8000);
850 l_col_type VARCHAR2(1);
851 l_front VARCHAR2(100);
852 l_back VARCHAR2(100);
853
854
855 BEGIN
856 hr_utility.set_location('Entering:'||l_proc, 5);
857
858 hr_utility.set_location('Col Name : '||p_column_name, 6);
859
860 IF NVL(g_refresh_tab_all.COUNT,0) <> 0 THEN
861 -- loop thru the array and get the value in column 3 corresponding to col name
862 FOR i IN NVL(g_refresh_tab_all.FIRST,0)..NVL(g_refresh_tab_all.LAST,-1)
863 LOOP
864 hr_utility.set_location('Searching g_refresh_tab_all:'|| UPPER(g_refresh_tab_all(i).column_name)||','|| UPPER(p_column_name),7);
865 IF UPPER(g_refresh_tab_all(i).column_name) = UPPER(p_column_name) THEN
866 hr_utility.set_location('Found match in g_refresh_tab_all',8);
867 l_col_val := 'pqh_refresh_data.ret_value_from_glb_table('
868 || to_char(i)
869 || ')';
870 l_col_type := g_refresh_tab_all(i).column_type;
871 EXIT; -- exit the loop as the column is found
872 END IF;
873 END LOOP;
874 END IF;
875
876 -- form the front and back packing string
877 IF l_col_type = 'D' THEN
878 l_front := 'TO_DATE(';
879 l_back := ',''RRRRMMDD HH24MISS'')';
880 ELSIF l_col_type = 'V' THEN
881 l_front := ' ';
882 l_back := ' ';
883 ELSE
884 l_front := ' ';
885 l_back := ' ';
886 END IF;
887 /**
888 IF NVL(g_refresh_tab.COUNT,0) <> 0 THEN
889 -- loop thru the array and get the value in column 3 corresponding to col name
890 FOR i IN NVL(g_refresh_tab.FIRST,0)..NVL(g_refresh_tab.LAST,-1)
891 LOOP
892 IF UPPER(g_refresh_tab(i).column_name) = UPPER(p_column_name) THEN
893 l_col_val := g_refresh_tab(i).txn_val;
894 l_col_type := g_refresh_tab(i).column_type;
895 EXIT; -- exit the loop as the column is found
896 END IF;
897 END LOOP;
898 END IF;
899
900
901 -- form the front and back packing string
902 IF l_col_type = 'D' THEN
903 l_front := 'TO_DATE(''';
904 l_back := ''',''RRRRMMDD HH24MISS'')';
905 ELSIF l_col_type = 'V' THEN
906 l_front := '''';
907 l_back := '''';
908 ELSE
909 l_front := ' ';
910 l_back := ' ';
911 END IF;
912 **/
913
914 l_col_val := l_front||l_col_val||l_back;
915
916 hr_utility.set_location('Col Val : '||l_col_val, 10);
917 hr_utility.set_location('Leaving:'||l_proc, 1000);
918
919 return l_col_val;
920
921
922 END get_value_from_array;
923
924 /*----------------------------------------------------------------
925 || FUNCTION : get_value_from_array_purge
926 ||
927 ------------------------------------------------------------------*/
928 FUNCTION get_value_from_array_purge ( p_column_name IN pqh_attributes.column_name%TYPE )
929 RETURN VARCHAR2 IS
930
931 -- local variables
932 --
933 l_proc varchar2(72) := g_package||'get_value_from_array_purge';
934 l_col_val VARCHAR2(8000);
935 l_col_type VARCHAR2(1);
936 l_front VARCHAR2(100);
937 l_back VARCHAR2(100);
938
939
940 BEGIN
941 hr_utility.set_location('Entering:'||l_proc, 5);
942
943 hr_utility.set_location('Col Name : '||p_column_name, 6);
944
945 IF NVL(g_refresh_tab.COUNT,0) <> 0 THEN
946 -- loop thru the array and get the value in column 3 corresponding to col name
947 FOR i IN NVL(g_refresh_tab.FIRST,0)..NVL(g_refresh_tab.LAST,-1)
948 LOOP
949 IF UPPER(g_refresh_tab(i).column_name) = UPPER(p_column_name) THEN
950 l_col_val := g_refresh_tab(i).txn_val;
951 l_col_type := g_refresh_tab(i).column_type;
952 EXIT; -- exit the loop as the column is found
953 END IF;
954 END LOOP;
955 END IF;
956
957
958 -- form the front and back packing string
959 IF l_col_type = 'D' THEN
960 l_front := 'TO_DATE(''';
961 l_back := ''',''YYYYMMDD HH24MISS'')';
962 ELSIF l_col_type = 'V' THEN
963 l_front := '''';
964 l_back := '''';
965 ELSE
966 l_front := ' ';
967 l_back := ' ';
968 END IF;
969
970 l_col_val := l_front||l_col_val||l_back;
971
972 hr_utility.set_location('Col Val : '||l_col_val, 10);
973 hr_utility.set_location('Leaving:'||l_proc, 1000);
974
975 return l_col_val;
976
977
978 END get_value_from_array_purge;
979
980 /*----------------------------------------------------------------
981 || PROCEDURE : replace_where_params
982 ||
983 ------------------------------------------------------------------*/
984
985 PROCEDURE replace_where_params
986 ( p_where_clause_in IN pqh_table_route.where_clause%TYPE,
987 p_txn_tab_flag IN VARCHAR2,
988 p_txn_id IN number,
989 p_where_clause_out OUT NOCOPY pqh_table_route.where_clause%TYPE ) IS
990
991 /*
992 This procedure will replace all the parameters in the where_clause with their actual values.
993 p_txn_tab_flag will be 'Y' for the txn table. In the case of txn table we replace the txn_id
994 value with the IN param value to the program. In the case of shadow and main table, we get
995 param values from g_refresh_dtata array
996 */
997
998 --
999 -- local variables
1000 --
1001 l_proc varchar2(72) := g_package||'replace_where_params';
1002 l_atoms_tab atoms_tabtype; -- to hold the where_clause atoms
1003 l_no_atoms number;
1004 l_key_column pqh_attributes.column_name%TYPE;
1005 l_key_val VARCHAR2(8000);
1006 l_where_out pqh_table_route.where_clause%TYPE;
1007 l_key_col_null VARCHAR2(8000);
1008 --
1009 l_found boolean := false;
1010 l_next number(10) := 0;
1011 i number(10) := 0;
1012 j number(10) := 0;
1013 --
1014 BEGIN
1015 hr_utility.set_location('Entering:'||l_proc, 5);
1016
1017 -- parse the where_clause and populate the PL/SQL table
1018 parse_string
1019 ( p_string_in => p_where_clause_in,
1020 p_atomics_list_out => l_atoms_tab,
1021 p_num_atomics_out => l_no_atoms
1022 );
1023
1024 IF NVL(g_refresh_tab.COUNT,0) <> 0 THEN
1025 -- loop thru the array g_refresh_tab and add the values in g_refresh_tab_all
1026 FOR i IN NVL(g_refresh_tab.FIRST,0)..NVL(g_refresh_tab.LAST,-1) LOOP
1027 l_found := false;
1028 IF NVL(g_refresh_tab_all.COUNT,0) <> 0 THEN
1029 FOR j IN NVL(g_refresh_tab_all.FIRST,0)..NVL(g_refresh_tab_all.LAST,-1) loop
1030 IF UPPER(g_refresh_tab(i).column_name) = UPPER(g_refresh_tab_all(j).column_name) THEN
1031 g_refresh_tab_all(j).txn_val := g_refresh_tab(i).txn_val;
1032 g_refresh_tab_all(j).column_type := g_refresh_tab(i).column_type;
1033 l_found := true;
1034 EXIT; -- exit the loop as the column is found
1035 END IF;
1036 END LOOP;
1037 END IF;
1038 If not l_found then
1039 l_next := nvl(g_refresh_tab_all.COUNT,0);
1040 hr_utility.set_location('Adding row:'||to_char(l_next),10);
1041 g_refresh_tab_all(l_next).column_name := g_refresh_tab(i).column_name;
1042 g_refresh_tab_all(l_next).txn_val := g_refresh_tab(i).txn_val;
1043 g_refresh_tab_all(l_next).column_type := g_refresh_tab(i).column_type;
1044 End if;
1045 --
1046 END LOOP;
1047 END IF;
1048
1049
1050 -- loop thru the PL/SQL table and replace params
1051
1052 FOR table_row IN NVL(l_atoms_tab.FIRST,0)..NVL(l_atoms_tab.LAST,-1)
1053 LOOP
1054 IF substr(NVL (l_atoms_tab(table_row), 'NULL') ,1,1) = '<' THEN
1055 hr_utility.set_location('Parameter:'||l_atoms_tab(table_row),11);
1056 l_key_column := substr(l_atoms_tab(table_row),2,(LENGTH(LTRIM(RTRIM(l_atoms_tab(table_row)))) - 2)) ;
1057 l_key_col_null := l_atoms_tab(table_row -4);
1058
1062 l_key_val := p_txn_id;
1059 -- depending on the flag get the param value
1060 IF p_txn_tab_flag = 'Y' THEN
1061 -- this is txn table , so param value is IN parameter to the refresh_data procedure
1063 ELSE
1064 -- this is shadow OR main table
1065 -- for the above key_column name get the value from the array
1066 l_key_val := get_value_from_array(p_column_name => l_key_column);
1067 hr_utility.set_location(l_key_column||' = '||l_key_val,15);
1068 -- if value is null pass the column name
1069 IF RTRIM(l_key_val) IS NULL THEN
1070 l_key_val := l_key_col_null;
1071 END IF;
1072 END IF;
1073
1074 -- replace the param with the actual value
1075 l_atoms_tab(table_row) := l_key_val;
1076 END IF;
1077
1078 END LOOP;
1079
1080 -- build the where clause again
1081 l_where_out := ''; -- initialize variable
1082 FOR table_row IN NVL(l_atoms_tab.FIRST,0)..NVL(l_atoms_tab.LAST,-1)
1083 LOOP
1084 l_where_out := l_where_out||nvl(l_atoms_tab(table_row),' ');
1085 END LOOP;
1086
1087 -- assign the out parameter the final where string
1088 p_where_clause_out := l_where_out;
1089 hr_utility.set_location('Leaving:'||l_proc, 1000);
1090
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 p_where_clause_out := null;
1094 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1095 hr_utility.set_message_token('ROUTINE', l_proc);
1096 hr_utility.set_message_token('REASON', SQLERRM);
1097 hr_utility.raise_error;
1098 END replace_where_params;
1099
1100 /*----------------------------------------------------------------
1101 || PROCEDURE : replace_where_params_purge
1102 ||
1103 ------------------------------------------------------------------*/
1104 PROCEDURE replace_where_params_purge
1105 ( p_where_clause_in IN pqh_table_route.where_clause%TYPE,
1106 p_txn_tab_flag IN VARCHAR2,
1107 p_txn_id IN number,
1108 p_where_clause_out OUT NOCOPY pqh_table_route.where_clause%TYPE ) IS
1109
1110 /*
1111 This procedure will replace all the parameters in the where_clause with their
1112 actual values.
1113 p_txn_tab_flag will be 'Y' for the txn table. In the case of txn table we replace the txn_id
1114 value with the IN param value to the program. In the case of shadow and main table, we get
1115 param values from g_refresh_dtata array
1116 */
1117
1118 --
1119 -- local variables
1120 --
1121 l_proc varchar2(72) := g_package||'replace_where_params_purge';
1122 l_atoms_tab atoms_tabtype; -- to hold the where_clause atoms
1123 l_no_atoms number;
1124 l_key_column pqh_attributes.column_name%TYPE;
1125 l_key_val VARCHAR2(8000);
1126 l_where_out pqh_table_route.where_clause%TYPE;
1127 l_key_col_null VARCHAR2(8000);
1128
1129
1130 BEGIN
1131 hr_utility.set_location('Entering:'||l_proc, 5);
1132
1133 -- parse the where_clause and populate the PL/SQL table
1134 parse_string
1135 ( p_string_in => p_where_clause_in,
1136 p_atomics_list_out => l_atoms_tab,
1137 p_num_atomics_out => l_no_atoms
1138 );
1139
1140
1141 -- loop thru the PL/SQL table and replace params
1142
1143 FOR table_row IN NVL(l_atoms_tab.FIRST,0)..NVL(l_atoms_tab.LAST,-1)
1144 LOOP
1145 IF substr(NVL (l_atoms_tab(table_row), 'NULL') ,1,1) = '<' THEN
1146 l_key_column := substr(l_atoms_tab(table_row),2,(LENGTH(LTRIM(RTRIM(l_atoms_tab(table_row)))) - 2)) ;
1147 l_key_col_null := l_atoms_tab(table_row -4);
1148
1149 -- depending on the flag get the param value
1150 IF p_txn_tab_flag = 'Y' THEN
1151 -- this is txn table , so param value is IN parameter to the refresh_data procedure
1152 l_key_val := p_txn_id;
1153 ELSE
1154 -- this is shadow OR main table
1155 -- for the above key_column name get the value from the array
1156 l_key_val := get_value_from_array_purge(p_column_name => l_key_column);
1157 -- if value is null pass the column name
1158 IF RTRIM(l_key_val) IS NULL THEN
1159 l_key_val := l_key_col_null;
1160 END IF;
1161 END IF;
1162
1163 -- replace the param with the actual value
1164 l_atoms_tab(table_row) := l_key_val;
1165 END IF;
1166
1167 END LOOP;
1168
1169 -- build the where clause again
1170 l_where_out := ''; -- initialize variable
1171 FOR table_row IN NVL(l_atoms_tab.FIRST,0)..NVL(l_atoms_tab.LAST,-1)
1172 LOOP
1173 l_where_out := l_where_out||nvl(l_atoms_tab(table_row),' ');
1174 END LOOP;
1175
1176 -- assign the out parameter the final where string
1177 p_where_clause_out := l_where_out;
1178 hr_utility.set_location('Leaving:'||l_proc, 1000);
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 p_where_clause_out := null;
1183 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1184 hr_utility.set_message_token('ROUTINE', l_proc);
1185 hr_utility.set_message_token('REASON', SQLERRM);
1186 hr_utility.raise_error;
1187 END replace_where_params_purge;
1188
1189 /*----------------------------------------------------------------
1190 || PROCEDURE : get_all_rows
1191 ||
1192 ------------------------------------------------------------------*/
1193 PROCEDURE get_all_rows
1194 (p_select_stmt IN t_where_clause_typ,
1195 p_from_clause IN pqh_table_route.from_clause%TYPE,
1199 p_all_txn_rows OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE )
1196 p_where_clause IN pqh_table_route.where_clause%TYPE,
1197 p_total_columns IN NUMBER,
1198 p_total_rows OUT NOCOPY NUMBER,
1200 IS
1201 /*
1202 This procedure will get all rows of the table and populate the array
1203 The OUT array p_all_txn_rows will have the following data .
1204 eg: table has 3 rows and 3 columns then array has following value
1205 r1.c1, r2.c1, r3.c1, r2.c1, r2.c2, r2.c3, r3.c1, r3.c2, r3.c3
1206
1207 */
1208
1209 --
1210 -- local variables
1211 --
1212 l_proc varchar2(72) := g_package||'get_all_rows';
1213 c number; -- cursor handle
1214 d number; -- no of rows fetched by cursor
1215 v_tab dbMS_SQL.VARCHAR2_TABLE; -- temp array to hold elements
1216 l_qry_string VARCHAR2(32000); -- to cinstruct the qry string
1217 indx number := 1; -- start index of the array populated
1218 l_tot_rows_fetch number := 0; -- total of all array elements
1219 l_tot_cnt number :=1; -- index of the OUT array p_all_txn_rows
1220
1221
1222 BEGIN
1223 hr_utility.set_location('Entering:'||l_proc, 5);
1224
1225 -- construct the query string
1226 l_qry_string := p_select_stmt||' FROM '||
1227 p_from_clause||' WHERE '||
1228 p_where_clause ;
1229
1230 hr_utility.set_location('Qry Str : ',5);
1231
1232 hr_utility.set_location(substr(l_qry_string,1,50), 10);
1233 hr_utility.set_location(substr(l_qry_string,51,50), 10);
1234 hr_utility.set_location(substr(l_qry_string,101,50), 10);
1235 hr_utility.set_location(substr(l_qry_string,151,50), 10);
1236 hr_utility.set_location(substr(l_qry_string,201,50), 10);
1237 hr_utility.set_location(substr(l_qry_string,251,50), 10);
1238 hr_utility.set_location(substr(l_qry_string,301,50), 10);
1239 hr_utility.set_location(substr(l_qry_string,351,50), 10);
1240 hr_utility.set_location(substr(l_qry_string,401,50), 10);
1241 hr_utility.set_location(substr(l_qry_string,451,50), 10);
1242 hr_utility.set_location(substr(l_qry_string,501,50), 10);
1243 hr_utility.set_location(substr(l_qry_string,551,50), 10);
1244 hr_utility.set_location(substr(l_qry_string,601,50), 10);
1245 hr_utility.set_location(substr(l_qry_string,651,50), 10);
1246 hr_utility.set_location(substr(l_qry_string,701,50), 10);
1247 hr_utility.set_location(substr(l_qry_string,751,50), 10);
1248 hr_utility.set_location(substr(l_qry_string,801,50), 10);
1249 hr_utility.set_location(substr(l_qry_string,851,50), 10);
1250 hr_utility.set_location(substr(l_qry_string,901,50), 10);
1251 hr_utility.set_location(substr(l_qry_string,951,50), 10);
1252 hr_utility.set_location(substr(l_qry_string,1001,50), 10);
1253 hr_utility.set_location(substr(l_qry_string,1051,50), 10);
1254 hr_utility.set_location(substr(l_qry_string,1101,50), 10);
1255 hr_utility.set_location(substr(l_qry_string,1151,50), 10);
1256 hr_utility.set_location(substr(l_qry_string,1201,50), 10);
1257 hr_utility.set_location(substr(l_qry_string,1251,50), 10);
1258 hr_utility.set_location(substr(l_qry_string,1301,50), 10);
1259 hr_utility.set_location(substr(l_qry_string,1351,50), 10);
1260 hr_utility.set_location(substr(l_qry_string,1401,50), 10);
1261 hr_utility.set_location(substr(l_qry_string,1451,50), 10);
1262 hr_utility.set_location(substr(l_qry_string,1501,50), 10);
1263 hr_utility.set_location(substr(l_qry_string,1551,50), 10);
1264 hr_utility.set_location(substr(l_qry_string,1601,50), 10);
1265 hr_utility.set_location(substr(l_qry_string,1651,50), 10);
1266 hr_utility.set_location(substr(l_qry_string,1701,50), 10);
1267 hr_utility.set_location(substr(l_qry_string,1751,50), 10);
1268 hr_utility.set_location(substr(l_qry_string,1801,50), 10);
1269 hr_utility.set_location(substr(l_qry_string,1851,50), 10);
1270 hr_utility.set_location(substr(l_qry_string,1901,50), 10);
1271 hr_utility.set_location(substr(l_qry_string,1951,50), 10);
1272 hr_utility.set_location(substr(l_qry_string,2001,50), 10);
1273 hr_utility.set_location(substr(l_qry_string,2051,50), 10);
1274
1275
1276 -- open the cursor
1277 c := dbms_sql.open_cursor;
1278
1279 hr_utility.set_location('Opened Cursor : '||c, 10);
1280
1281
1282 -- parse the select stmt for errors
1283 dbms_sql.parse(c,l_qry_string, dbms_sql.native);
1284
1285 hr_utility.set_location('Parsed Query String :', 15);
1286
1287 -- for ALL COLUMNS we will LOOP one column at a time and build the
1288 -- total array
1289
1290 FOR j in 1..p_total_columns
1291 LOOP
1292
1293 -- hr_utility.set_location('Inside first Loop : ', 16);
1294
1295 -- define v_tab array to hold all the column values
1296 dbms_sql.define_array(c,j,v_tab,1, indx);
1297
1298 -- hr_utility.set_location('Defining Array : ', 18);
1299
1300 -- execute the dynamic select for all rows
1301 -- this will fetch the j th column values in the v_tab array
1302 d := dbms_sql.execute(c);
1303
1304 -- hr_utility.set_location('Sql Execute : '||d, 20);
1305
1306 LOOP
1307 d := dbms_sql.fetch_rows(c);
1308
1309 -- hr_utility.set_location('Fetched rows : '||d, 25);
1310
1311 EXIT WHEN d <> 1;
1312 l_tot_rows_fetch := l_tot_rows_fetch + 1;
1313 -- associate the fetch value with v_tab
1314 dbms_sql.column_value(c, j, v_tab);
1315 END LOOP;
1316
1317 -- hr_utility.set_location('After exec Loop : ', 30);
1318
1319 -- populate the OUT array with the v_tab values for each column
1320 -- we do this as the v_tab array index resets for each column
1321 -- eg : if we have 5 rows and 10 columns the the index resets fro 1 to 5
1322 -- for each column
1323
1324 FOR i in NVL(v_tab.FIRST,0)..NVL(v_tab.LAST,-1)
1328 -- hr_utility.set_location('Table Val :'||v_tab(i), 32);
1325 LOOP
1326
1327 -- hr_utility.set_location('Inside v_tab loop : ', 31);
1329 -- hr_utility.set_location('Inside v_tab loop : ', 33);
1330
1331 -- dbms_output.put_line('tab Value :'||i||' '||v_tab(i) );
1332
1333 p_all_txn_rows(l_tot_cnt) := v_tab(i);
1334 l_tot_cnt := l_tot_cnt + 1;
1335 END LOOP;
1336
1337 -- hr_utility.set_location('After second Loop : ', 35);
1338
1339 END LOOP; -- for all columns
1340
1341
1342 -- compute the total rows OUT variable
1343 p_total_rows := l_tot_rows_fetch/p_total_columns ;
1344
1345 -- dbms_output.put_line('Total Rows : '||tot_rows);
1346 hr_utility.set_location('Total Rows :'||p_total_rows, 50);
1347
1348 -- close the cursor
1349 dbms_sql.close_cursor(c);
1350 hr_utility.set_location('Leaving:'||l_proc, 1000);
1351
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1355 hr_utility.set_message_token('ROUTINE', l_proc);
1356 hr_utility.set_message_token('REASON', SQLERRM);
1357 hr_utility.raise_error;
1358 END get_all_rows;
1359
1360
1361
1362
1363 /*----------------------------------------------------------------
1364 || PROCEDURE : compute_updt_flag
1365 ||
1366 ------------------------------------------------------------------*/
1367
1368
1369 PROCEDURE compute_updt_flag
1370 IS
1371 /*
1372 This procedure would be called from the refresh_data. This procedure would loop thru the
1373 g_refresh_tab table and set the updt_flag column.
1374 Y => update column
1375 N => don't update column as NO change
1376 C => don't update column as the USER HAS CHANGED THE COLUMN WHICH CAN BE REFRESHED
1377 we will use 'C' to set visual attribute of item in the TXN form
1378 as we plan to give provision to user to refresh a refreshable column with right mouse
1379 click. This new visual attribute will wrn the user that he or someone who routed this txn to him
1380 has intentionally changed this value, so be cautious before you refresh this column
1381 */
1382
1383 --
1384 -- local variables
1385 --
1386 l_proc varchar2(72) := g_package||'compute_updt_flag';
1387
1388
1389 BEGIN
1390 hr_utility.set_location('Entering:'||l_proc, 5);
1391
1392 -- loop thru the g_refresh_tab array
1393 FOR i IN NVL(g_refresh_tab.FIRST,0)..NVL(g_refresh_tab.LAST,-1)
1394 LOOP
1395 IF NVL(g_refresh_tab(i).refresh_flag,'N') = 'Y' THEN
1396 -- as this column can be refreshed check for changed
1397 IF NVL(g_refresh_tab(i).shadow_val,'$$$') = NVL(g_refresh_tab(i).txn_val,'$$$') THEN
1398 -- value was not changed by user in txn form
1399 -- compare with the main table value to see if some has updated it
1400 IF NVL(g_refresh_tab(i).shadow_val,'$$$') = NVL(g_refresh_tab(i).main_val,'$$$') THEN
1401 -- value is unchanged
1402 g_refresh_tab(i).updt_flag := 'N';
1403 ELSE
1404 -- main table was updated
1405 -- so update the txn and shadow tables
1406 g_refresh_tab(i).updt_flag := 'Y';
1407 END IF;
1408 ELSE
1409 -- as shadow_val and txn_val are different
1410 -- the user has changed the value of this column in the txn form
1411 -- so don update this column
1412 g_refresh_tab(i).updt_flag := 'C';
1413
1414 END IF;
1415
1416 ELSE
1417 -- this column is not to be refreshed
1418 g_refresh_tab(i).updt_flag := 'N';
1419 END IF;
1420
1421 /*
1422 hr_utility.set_location('Col Name : '||g_refresh_tab(i).column_name,100);
1423 hr_utility.set_location('Col Type : '||g_refresh_tab(i).column_type,100);
1424 hr_utility.set_location('Refresh Flag : '||g_refresh_tab(i).refresh_flag,100);
1425 hr_utility.set_location('Txn Val : '||g_refresh_tab(i).txn_val,100);
1426 hr_utility.set_location('Shadow Val : '||g_refresh_tab(i).shadow_val,100);
1427 hr_utility.set_location('Pos Val : '||g_refresh_tab(i).main_val,100);
1428 hr_utility.set_location('Updt Flag : '||g_refresh_tab(i).updt_flag,100);
1429 */
1430
1431 END LOOP;
1432 hr_utility.set_location('Leaving:'||l_proc, 1000);
1433
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1437 hr_utility.set_message_token('ROUTINE', l_proc);
1438 hr_utility.set_message_token('REASON', SQLERRM);
1439 hr_utility.raise_error;
1440 END compute_updt_flag;
1441
1442
1443
1444 /*----------------------------------------------------------------
1445 || PROCEDURE : update_tables
1446 ||
1447 ------------------------------------------------------------------*/
1448
1449
1450 PROCEDURE update_tables
1451 (p_column_name IN pqh_attributes.column_name%TYPE,
1452 p_column_type IN pqh_attributes.column_type%TYPE,
1453 p_column_val IN VARCHAR2,
1454 p_from_clause_txn IN pqh_table_route.from_clause%TYPE,
1455 p_from_clause_shd IN pqh_table_route.from_clause%TYPE,
1456 p_rep_where_clause_shd IN pqh_table_route.where_clause%TYPE )
1457 IS
1458 /*
1459 This procedure will update the txn and shadow tables with the new value.
1460 As the shadow and txn tables are identical, we use the wwhere clause of shadow
1461 which uniquely identifies only ONE row
1462 */
1463 --
1464 -- local variables
1465 --
1466 l_proc varchar2(72) := g_package||'update_tables';
1470 l_front VARCHAR2(100);
1467 l_stmt_str VARCHAR2(8000);
1468 l_where_clause VARCHAR2(8000);
1469 l_col_val VARCHAR2(8000);
1471 l_back VARCHAR2(100);
1472
1473 BEGIN
1474 hr_utility.set_location('Entering:'||l_proc, 5);
1475 hr_utility.set_location('p_column_name : '||p_column_name, 5);
1476 hr_utility.set_location('p_column_type : '||p_column_type, 5);
1477 hr_utility.set_location('p_column_val : '||p_column_val, 5);
1478 hr_utility.set_location('from_txn : '||p_from_clause_txn, 5);
1479 hr_utility.set_location('from_shd : '||p_from_clause_shd, 5);
1480 hr_utility.set_location('rep__shd : '||p_rep_where_clause_shd, 5);
1481
1482 l_col_val := p_column_val;
1483
1484 -- form the front and back packing string
1485 IF p_column_type = 'D' THEN
1486 l_front := 'TO_DATE(';
1487 l_back := ',''RRRRMMDD HH24MISS'')';
1488 ELSE
1489 l_front := ' ';
1490 l_back := ' ';
1491 END IF;
1492
1493
1494 -- construct where clause which is same for BOTH tables
1495 l_where_clause := ' SET '||p_column_name||' = '||
1496 l_front||':p_col_val '||l_back||
1497 ' WHERE '||p_rep_where_clause_shd ;
1498
1499 /*
1500 update TRANSACTION TABLE
1501 */
1502 -- construct the updt stmt
1503 l_stmt_str := ''; -- initialize string
1504 l_stmt_str := 'UPDATE '||p_from_clause_txn||l_where_clause ;
1505
1506 hr_utility.set_location('Update Statement ',10);
1507
1508 hr_utility.set_location(substr(l_stmt_str,1,50), 10);
1509 hr_utility.set_location(substr(l_stmt_str,51,50), 10);
1510 hr_utility.set_location(substr(l_stmt_str,101,50), 10);
1511 hr_utility.set_location(substr(l_stmt_str,151,50), 10);
1512 hr_utility.set_location(substr(l_stmt_str,201,50), 10);
1513 hr_utility.set_location(substr(l_stmt_str,251,50), 10);
1514 hr_utility.set_location(substr(l_stmt_str,301,50), 10);
1515 hr_utility.set_location(substr(l_stmt_str,351,50), 10);
1516 hr_utility.set_location(substr(l_stmt_str,401,50), 10);
1517 hr_utility.set_location(substr(l_stmt_str,451,50), 10);
1518 hr_utility.set_location(substr(l_stmt_str,501,50), 10);
1519 hr_utility.set_location(substr(l_stmt_str,551,50), 10);
1520 hr_utility.set_location(substr(l_stmt_str,601,50), 10);
1521 hr_utility.set_location(substr(l_stmt_str,651,50), 10);
1522 hr_utility.set_location(substr(l_stmt_str,701,50), 10);
1523 hr_utility.set_location(substr(l_stmt_str,751,50), 10);
1524 hr_utility.set_location(substr(l_stmt_str,801,50), 10);
1525 hr_utility.set_location(substr(l_stmt_str,851,50), 10);
1526 hr_utility.set_location(substr(l_stmt_str,901,50), 10);
1527 hr_utility.set_location(substr(l_stmt_str,951,50), 10);
1528 hr_utility.set_location(substr(l_stmt_str,1001,50), 10);
1529 hr_utility.set_location(substr(l_stmt_str,1051,50), 10);
1530 hr_utility.set_location(substr(l_stmt_str,1101,50), 10);
1531 hr_utility.set_location(substr(l_stmt_str,1151,50), 10);
1532 hr_utility.set_location(substr(l_stmt_str,1201,50), 10);
1533 hr_utility.set_location(substr(l_stmt_str,1251,50), 10);
1534 hr_utility.set_location(substr(l_stmt_str,1301,50), 10);
1535 hr_utility.set_location(substr(l_stmt_str,1351,50), 10);
1536 hr_utility.set_location(substr(l_stmt_str,1401,50), 10);
1537 hr_utility.set_location(substr(l_stmt_str,1451,50), 10);
1538 hr_utility.set_location(substr(l_stmt_str,1501,50), 10);
1539 hr_utility.set_location(substr(l_stmt_str,1551,50), 10);
1540 hr_utility.set_location(substr(l_stmt_str,1601,50), 10);
1541 hr_utility.set_location(substr(l_stmt_str,1651,50), 10);
1542 hr_utility.set_location(substr(l_stmt_str,1701,50), 10);
1543 hr_utility.set_location(substr(l_stmt_str,1751,50), 10);
1544 hr_utility.set_location(substr(l_stmt_str,1801,50), 10);
1545 hr_utility.set_location(substr(l_stmt_str,1851,50), 10);
1546 hr_utility.set_location(substr(l_stmt_str,1901,50), 10);
1547 hr_utility.set_location(substr(l_stmt_str,1951,50), 10);
1548 hr_utility.set_location(substr(l_stmt_str,2001,50), 10);
1549 hr_utility.set_location(substr(l_stmt_str,2051,50), 10);
1550
1551
1552 -- execute the updt stmt
1553 EXECUTE IMMEDIATE l_stmt_str
1554 USING l_col_val ;
1555
1556 /*
1557 update SHADOW TABLE
1558 */
1559 -- construct the updt stmt
1560 l_stmt_str := ''; -- initialize string
1561 l_stmt_str := 'UPDATE '||p_from_clause_shd||l_where_clause ;
1562
1563 -- execute the updt stmt
1564 EXECUTE IMMEDIATE l_stmt_str
1565 USING l_col_val ;
1566
1567
1568 hr_utility.set_location('Leaving:'||l_proc, 1000);
1569
1570 EXCEPTION
1571 WHEN OTHERS THEN
1572 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1573 hr_utility.set_message_token('ROUTINE', l_proc);
1574 hr_utility.set_message_token('REASON', SQLERRM);
1575 hr_utility.raise_error;
1576 END update_tables;
1577
1578 -- Procedures for PQHPCTXN form Visual Attribute Implementation
1579
1580 /*----------------------------------------------------------------
1581 || PROCEDURE : count_changed
1582 ||
1583 ------------------------------------------------------------------*/
1584
1585 PROCEDURE count_changed
1586 (p_count OUT NOCOPY number )
1587 IS
1588 /*
1589 This procedure will loop thru the g_refresh_data array and populate the
1590 prvcalc array. This is written for PQHPCTXN for for the visual attributes
1591 implementation part.
1592 */
1593
1594
1595 l_proc varchar2(72) := g_package||'count_changed';
1596 l_form_column_name pqh_txn_category_attributes.form_column_name%TYPE;
1597 l_mode_flag varchar2(1) := 'E';
1598 l_reqd_flag varchar2(1) := 'C';
1602 CURSOR c1(p_column_name pqh_attributes.column_name%TYPE ) IS
1599 l_cnt binary_integer := 0;
1600
1601
1603 SELECT tca.form_column_name
1604 FROM pqh_attributes att, pqh_txn_category_attributes tca
1605 WHERE att.attribute_id = tca.attribute_id
1606 AND tca.transaction_category_id = g_txn_category_id
1607 AND att.refresh_col_name = p_column_name;
1608
1609 BEGIN
1610
1611 hr_utility.set_location('Entering:'||l_proc, 5);
1612
1613 IF NVL(g_refresh_bak.COUNT,0) <> 0 THEN
1614
1615 FOR i IN NVL(g_refresh_bak.FIRST,0)..NVL(g_refresh_bak.LAST,-1)
1616 LOOP
1617
1618 IF g_refresh_bak(i).updt_flag = 'C' THEN
1619 -- get the form_column_name for the column_name and populate the prvcalc table
1620
1621 OPEN c1(pqh_refresh_data.g_refresh_bak(i).column_name);
1622 FETCH c1 INTO l_form_column_name;
1623 CLOSE c1;
1624
1625 -- populate the global prv_tab
1626 l_cnt := l_cnt + 1;
1627
1628 g_attrib_prv_tab(l_cnt).form_column_name := l_form_column_name;
1629 g_attrib_prv_tab(l_cnt).mode_flag := l_mode_flag;
1630 g_attrib_prv_tab(l_cnt).reqd_flag := l_reqd_flag;
1631
1632 END IF;
1633
1634 END LOOP;
1635
1636 -- populate the OUT variable
1637 p_count := g_attrib_prv_tab.COUNT;
1638
1639 ELSE
1640 -- g_refresh_bak is empty
1641 p_count := 0;
1642
1643 END IF;
1644
1645
1646 hr_utility.set_location('Total Items Changed : '||p_count,9);
1647
1648 hr_utility.set_location('Leaving:'||l_proc, 10);
1649
1650
1651 EXCEPTION
1652 WHEN OTHERS THEN
1653 p_count := null;
1654 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1655 hr_utility.set_message_token('ROUTINE', l_proc);
1656 hr_utility.set_message_token('REASON', SQLERRM);
1657 hr_utility.raise_error;
1658 END count_changed;
1659
1660
1661
1662 /*----------------------------------------------------------------
1663 || PROCEDURE : get_row_prv_calc
1664 ||
1665 ------------------------------------------------------------------*/
1666
1667 PROCEDURE get_row_prv_calc
1668 ( p_row IN number,
1669 p_form_column_name OUT NOCOPY pqh_txn_category_attributes.form_column_name%TYPE,
1670 p_mode_flag OUT NOCOPY varchar2,
1671 p_reqd_flag OUT NOCOPY varchar2
1672 ) IS
1673
1674 /*
1675 This procedure will return the row in the prvcalc table corresponding to p_row
1676 */
1677
1678 l_proc varchar2(72) := g_package||'get_row_prv_calc';
1679
1680 BEGIN
1681
1682 hr_utility.set_location('Entering:'||l_proc, 5);
1683
1684 p_form_column_name := g_attrib_prv_tab(p_row).form_column_name;
1685 p_mode_flag := 'E';
1686 p_reqd_flag := 'C';
1687
1688
1689 hr_utility.set_location('Leaving:'||l_proc, 10);
1690
1691 EXCEPTION
1692 WHEN OTHERS THEN
1693 p_form_column_name := null;
1694 p_mode_flag := null;
1695 p_reqd_flag := null;
1696 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1697 hr_utility.set_message_token('ROUTINE', l_proc);
1698 hr_utility.set_message_token('REASON', SQLERRM);
1699 hr_utility.raise_error;
1700 END get_row_prv_calc;
1701
1702 -- Procedure to Parse string into atoms
1703
1704 /*----------------------------------------------------------------
1705 || PROCEDURE : parse_string
1706 ||
1707 ------------------------------------------------------------------*/
1708
1709
1710 PROCEDURE parse_string
1711 (p_string_in IN pqh_table_route.where_clause%TYPE,
1712 p_atomics_list_out OUT NOCOPY atoms_tabtype,
1713 p_num_atomics_out IN OUT NOCOPY NUMBER,
1714 p_delimiters_in IN VARCHAR2 := std_delimiters)
1715
1716 /*
1717 || Version of parse_string which stores the list of atomics
1718 || in a PL/SQL table.
1719 ||
1720 || Parameters:
1721 || p_string_in - the string to be parsed.
1722 || p_atomics_list_out - the table of atomics.
1723 || p_num_atomics_out - the number of atomics found.
1724 || p_delimiters_in - the set of delimiters used in parse.
1725 */
1726
1727 IS
1728 BEGIN
1729 /* Initialize variables. */
1730 p_num_atomics_out := 0;
1731 len_string := string_length (p_string_in);
1732
1733 IF len_string IS NOT NULL
1734 THEN
1735 /*
1736 || Only scan the string if made of something more than blanks.
1737 || Start at first non-blank character. Remember: INSTR returns 0
1738 || if a space is not found. Stop scanning if at end of string.
1739 */
1740 start_loc := LEAST (1, INSTR (p_string_in, ' ') + 1);
1741 WHILE start_loc <= len_string
1742 LOOP
1743 /*
1744 || Find the starting point of the NEXT atomic. Go ahead and
1745 || increment counter for the number of atomics. Then have to
1746 || actually pull out the atomic. Two cases to consider:
1747 || 1. Last atomic goes to end of string.
1748 || 2. The atomic is a single blank. Use special constant.
1749 || 3. Anything else.
1750 */
1751 next_loc := next_atom_loc (p_string_in, start_loc);
1752 p_num_atomics_out := p_num_atomics_out + 1;
1753 IF next_loc > len_string
1754 THEN
1755 -- Atomic is all characters right to the end of the string.
1756 p_atomics_list_out (p_num_atomics_out) :=
1757 SUBSTR (p_string_in, start_loc);
1758 ELSE
1759 /*
1760 || Internal atomic. If RTRIMs to NULL, have a blank
1761 || Use special-case string to stuff a " " in the table.
1762 */
1763 p_atomics_list_out (p_num_atomics_out) :=
1764 NVL (RTRIM (SUBSTR (p_string_in,
1765 start_loc, next_loc-start_loc)),
1766 a_blank);
1767 END IF;
1768
1769 -- Move starting point of scan for next atomic.
1770 start_loc := next_loc;
1771 END LOOP;
1772 END IF;
1773 END parse_string;
1774
1775 END; -- Package Body PQH_REFRESH_DATA
[Home] [Help]