[Home] [Help]
PACKAGE BODY: APPS.PQH_GENERIC_PURGE
Source
1 PACKAGE BODY pqh_generic_purge AS
2 /* $Header: pqgenpur.pkb 120.0 2005/05/29 01:55:57 appldev noship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 g_package varchar2(33) := 'pqh_generic_purge.'; -- Global Package Name
8 g_purge_txn_catg_id NUMBER; --transaction category id for PURGE
9 g_short_name varchar2(50); --short name for use in deleting Process Log entries
10 g_error_flag BOOLEAN DEFAULT FALSE;
11 g_effective_date DATE;
12 g_wf_txn_catg_id NUMBER;
13 g_master_alias varchar2(30);
14 --
15 /* -------------Variable Declarations --------------------------------------
16 l_master_tab_route_id pqh_table_route.table_route_id%TYPE;
17 l_master_table_alias pqh_table_route.table_alias%TYPE;
18 l_master_pk_value NUMBER(15);
19 pk_column_name pqh_attributes.column_name%TYPE;
20 g_error_flag BOOLEAN DEFAULT FALSE;
21 retcode NUMBER;
22 l_effective_date DATE;
23 */
24
25 /* function added by kgowripe */
26 FUNCTION get_transaction_category_id(p_short_name IN varchar2) RETURN Number IS
27
28 CURSOR csr_local_txn_catg IS
29 SELECT transaction_category_id
30 FROM pqh_transaction_categories
31 WHERE short_name = p_short_name;
32 l_txn_catg_id NUMBER;
33 l_proc varchar2(80) := g_package||'get_transaction_category_id';
34 BEGIN
35 hr_utility.set_location('Entering '||l_proc,10);
36 OPEN csr_local_txn_catg;
37 FETCH csr_local_txn_catg INTO l_txn_catg_id;
38 CLOSE csr_local_txn_catg;
39 hr_utility.set_location('Leaving '||l_proc,20);
40 RETURN l_txn_catg_id;
41 END get_transaction_category_id;
42
43 PROCEDURE table_route_details(p_table_alias IN varchar2,
44 p_primary_key_flag IN Varchar2 DEFAULT NULL,
45 p_table_route_id OUT NOCOPY NUMBER,
46 p_from_clause OUT NOCOPY VARCHAR2,
47 p_where_clause OUT NOCOPY VARCHAR2,
48 p_primary_key_col OUT NOCOPY VARCHAR2) IS
49 CURSOR csr_table_route(p_alias IN pqh_table_route.TABLE_alias%TYPE) is
50 SELECT table_route_id,
51 from_clause,
52 where_clause
53 FROM pqh_table_route
54 WHERE table_alias = p_alias;
55
56 CURSOR csr_col_name(p_table_route_id IN NUMBER,
57 p_col_type_cd IN VARCHAR2) IS
58 SELECT upper(att.column_name) column_name
59 FROM pqh_attributes att
60 , pqh_special_attributes sat
61 , pqh_txn_category_attributes tca
62 WHERE att.attribute_id = tca.attribute_id
63 AND att.master_table_route_id = p_table_route_id
64 AND tca.transaction_category_id = g_purge_txn_catg_id
65 AND tca.txn_category_attribute_id = sat.txn_category_attribute_id
66 AND sat.attribute_type_cd = p_col_type_cd; --'PRIMARY_KEY';
67
68 l_proc varchar2(80) := g_package||'table_route_details';
69
70 BEGIN
71 hr_utility.set_location('Entering '||l_proc,10);
72 OPEN csr_table_route(p_table_alias);
73 FETCH csr_table_route INTO p_table_route_id, p_from_clause, p_where_clause;
74 CLOSE csr_table_route;
75
76 IF NVL(p_primary_key_flag,'N') = 'Y' THEN
77 OPEN csr_col_name(p_table_route_id,'PRIMARY_KEY');
78 FETCH csr_col_name INTO p_primary_key_col;
79 CLOSE csr_col_name;
80 END IF;
81 hr_utility.set_location('leaving '||l_proc,20);
82 END table_route_details;
83
84 PROCEDURE delete_wf_data(p_pk_value IN NUMBER) IS
85 l_proc varchar2(80) := g_package||'delete_wf_data';
86 l_select varchar2(2000);
87 l_item_key varchar2(80);
88 l_item_type Varchar2(30) := 'PQHGEN'; --Item type for PQH workflow
89 BEGIN
90 --
91 hr_utility.set_location('Entering '||l_proc,10);
92 --
93 hr_utility.set_location('Item Type PQHGEN',11);
94 hr_utility.set_location('Item Key '||g_wf_txn_catg_id||'-'||p_pk_value,12);
95 -- EXECUTE IMMEDIATE l_select INTO l_wf_txn_catg_id USING p_pk_value;
96
97 IF g_wf_txn_catg_id IS NOT NULL THEN
98 --
99 hr_utility.set_location('Deleting WF data '||l_proc,15);
100 --
101 l_item_key := g_wf_txn_catg_id||'-'||p_pk_value;
102 wf_engine.abortprocess(itemtype => l_item_type,
103 itemkey => l_item_key);
104 wf_purge.total(itemtype => l_item_type
105 ,itemkey => l_item_key);
106
107
108 END IF;
109 hr_utility.set_location('Leaving '||l_proc,20);
110 EXCEPTION
111 When No_data_found THEN
112 NULL;
113 WHEN Others THEN
114 hr_utility.set_location('Error '||sqlErrm,16);
115 hr_utility.set_location('Leaving '||L_proc,18);
116 END;
117
118 PROCEDURE delete_process_log_data(p_pk_value IN Number ) IS
119 CURSOR csr_process_log_id(p_txn_value IN NUMBER,
120 p_short_name IN VARCHAR2) IS
121 SELECT process_log_id,object_version_number
122 FROM pqh_process_log
123 WHERE module_cd = UPPER(p_short_name)
124 START WITH process_log_id = (SELECT process_log_id
125 FROM pqh_process_log
126 WHERE module_cd = UPPER(p_short_name)
127 AND master_process_log_id IS NULL
128 AND txn_id = p_txn_value)
129 CONNECT BY master_process_log_id = PRIOR process_log_id
130 ORDER BY level DESC;
131 l_proc varchar2(80) := g_package||'delete_process_log_data';
132 BEGIN
133 if g_short_name = 'BUDGET_WORKSHEET' THEN
134 g_short_name := 'APPROVE_WORKSHEET';
135 end if;
136 hr_utility.set_location('Entering '||l_proc,10);
137 FOR i IN csr_process_log_id(p_pk_value,g_short_name)
138 LOOP
139 pqh_process_log_api.delete_process_log
140 (p_validate => false
141 ,p_process_log_id => i.process_log_id
142 ,p_object_version_number => i.object_version_number
143 ,p_effective_date => SYSDATE
144 );
145 END LOOP;
146 hr_utility.set_location('Leaving '||l_proc,10);
147 END;
148
149 ----------------------------------------------------------------------------------------------------
150 -- PQH_GEN_PURGE TO CALL ALL OTHER PROCEDURES
151 -------------------------------------------------------------------------------------------------
152 PROCEDURE pqh_gen_purge
153 (errbuf OUT NOCOPY VARCHAR2,
154 retcode OUT NOCOPY NUMBER,
155 p_alias IN pqh_table_route.table_alias%TYPE,
156 paramname1 IN pqh_attributes.column_name%TYPE ,
157 paramvalue1 IN VARCHAR2 ,
158 paramname2 IN pqh_attributes.column_name%TYPE,
159 paramvalue2 IN VARCHAR2 ,
160 paramname3 IN pqh_attributes.column_name%TYPE ,
161 paramvalue3 IN VARCHAR2 ,
162 paramname4 IN pqh_attributes.column_name%TYPE ,
163 paramvalue4 IN VARCHAR2 ,
164 paramname5 IN pqh_attributes.column_name%TYPE ,
165 paramvalue5 IN VARCHAR2 ,
166 p_effective_date IN DATE )
167 IS
168 l_proc varchar2(72) := g_package||'gen_purge';
169 l_master_alias pqh_table_route.table_alias%TYPE;
170 l_master_tab_route_id pqh_table_route.table_route_id%TYPE;
171 pk_col_name pqh_attributes.column_name%TYPE;
172 l_select_stmt VARCHAR2(8000);
173 l_from_clause_txn pqh_table_route.from_clause%TYPE;
174 l_where_clause_in_txn pqh_table_route.where_clause%TYPE;
175 l_where_clause_out_txn pqh_table_route.where_clause%TYPE;
176 l_all_txn_rows_array dbms_sql.varchar2_table;
177 l_tot_txn_rows NUMBER;
178 l_parent_pk_value NUMBER;
179 i NUMBER default 1 ;
180 l_select varchar2(2000);
181
182 BEGIN
183
184 hr_utility.set_location('entering: ' ||l_proc,1000);
185 l_master_alias := p_alias;
186 g_master_alias := p_alias;
187 g_effective_date := p_effective_date;
188 --Added by kgowripe
189 g_purge_txn_catg_id := get_transaction_category_id('PURGE');
190 l_all_txn_rows_array.DELETE;
191 --get table route information for master table
192 table_route_details(p_table_alias => l_master_alias
193 ,p_primary_key_flag => 'Y'
194 ,p_table_route_id => l_master_tab_route_id
195 ,p_from_clause => l_from_clause_txn
196 ,p_where_clause => l_where_clause_in_txn
197 ,p_primary_key_col => pk_col_name);
198
199 l_select_stmt :='select ' || ' ' || ' TO_CHAR(' || pk_col_name || ')';
200 hr_utility.set_location('select stme:' || l_select_stmt,1010);
201 --
202 populate_pltable
203 (l_master_tab_route_id => l_master_tab_route_id,
204 paramname1 => paramname1,
205 paramvalue1 => paramvalue1,
206 paramname2 => paramname2,
207 paramvalue2 => paramvalue2,
208 paramname3 => paramname3,
209 paramvalue3 => paramvalue3,
210 paramname4 => paramname4,
211 paramvalue4 => paramvalue4,
212 paramname5 => paramname5,
213 paramvalue5 => paramvalue5);
214 --
215 hr_utility.set_location('where_in:'||substr(l_where_clause_in_txn,1,100),1020);
216 hr_utility.set_location('where_in:'||substr(l_where_clause_in_txn,100,100),1021);
217 --
218 pqh_refresh_data.replace_where_params_purge
219 (p_where_clause_in => l_where_clause_in_txn,
220 p_txn_tab_flag => 'N',
221 p_txn_id => '',
222 p_where_clause_out => l_where_clause_out_txn );
223 --
224 hr_utility.set_location('where_out:'||substr(l_where_clause_out_txn,1,75),1023);
225 hr_utility.set_location('where_out:'||substr(l_where_clause_out_txn,75,75),1024);
226 --
227 pqh_refresh_data.get_all_rows
228 (p_select_stmt => l_select_stmt,
229 p_from_clause => l_from_clause_txn,
230 p_where_clause => l_where_clause_out_txn,
231 p_total_columns => 1,--Since we are selecting only the primary key only
232 p_total_rows => l_tot_txn_rows,
233 p_all_txn_rows => l_all_txn_rows_array );
234 --
235
236 --Now all the pk values satisffying the criteria is in the table..l_all_txn_rows_array
237 FOR i in NVL(l_all_txn_rows_array.FIRST,0)..NVL(l_all_txn_rows_array.LAST,-1)
238 LOOP
239 l_parent_pk_value := l_all_txn_rows_array(i);
240 savepoint s1;
241 l_select := 'SELECT wf_transaction_category_id '||' FROM '||l_from_clause_txn||' WHERE '||pk_col_name||' = :1';
242
243 EXECUTE IMMEDIATE l_select INTO g_wf_txn_catg_id USING l_parent_pk_value;
244 hr_utility.set_location('wf_txn_catg_id '||g_wf_txn_catg_id,12);
245 --
246 del_child_records(l_master_alias ,l_parent_pk_value );
247 --
248 enter_conc_log(p_pk_value => l_parent_pk_value,
249 tab_rou_id => l_master_tab_route_id,
250 p_from_clause_txn => l_from_clause_txn,
251 p_pk_col_name => pk_col_name);
252 --
253 call_delete_api(p_tab_route_id => l_master_tab_route_id,
254 p_pk_value => l_parent_pk_value,
255 p_from_clause_txn => l_from_clause_txn,
256 p_pk_col_name => pk_col_name);
257 --
258
259 if g_error_flag = TRUE then
260 fnd_message.set_name('PQH','PQH_PURGE_TXN_FAIL');
261 g_error_flag := FALSE;
262 else
263 fnd_message.set_name('PQH','PQH_PURGE_TXN_SUCC');
264 end if;
265 fnd_file.put(fnd_file.log,fnd_message.get);
266 fnd_file.put_line(fnd_file.log,' ');
267 commit;
268 END LOOP;
269 hr_utility.set_location('leaving: ' ||l_proc,1100);
270
271 END pqh_gen_purge;
272 -----------------------------------------------------------------------------------------
273 -- FUNCTION GET_COL_TYPE
274 -----------------------------------------------------------------------------------------
275 FUNCTION get_col_type(p_column_name IN pqh_attributes.column_name%TYPE,
276 l_master_table_route_id in pqh_table_route.table_route_id%TYPE )
277 RETURN VARCHAR2
278 IS
279 CURSOR
280 csr_get_tr_type (p_column_name IN pqh_attributes.column_name%TYPE,
281 l_master_table_route_id IN pqh_table_route.table_route_id%TYPE ) IS
282 SELECT column_type
283 FROM pqh_attributes
284 WHERE column_name = UPPER(p_column_name)
285 AND master_table_route_id = l_master_table_route_id;
286
287 CURSOR csr_get_type (p_column_name IN pqh_attributes.column_name%TYPE) IS
288 SELECT column_type
289 FROM pqh_attributes
290 WHERE column_name = UPPER(p_column_name)
291 AND master_table_route_id is null ;
292 l_dummy varchar2(1);
293 BEGIN
294 if l_master_table_route_id is not null then
295 OPEN csr_get_tr_type(p_column_name,l_master_table_route_id);
296 FETCH csr_get_tr_type INTO l_dummy;
297 CLOSE csr_get_tr_type;
298 else
299 OPEN csr_get_type(p_column_name);
300 FETCH csr_get_type INTO l_dummy;
301 CLOSE csr_get_type;
302 end if;
303 RETURN l_dummy;
304
305 END get_col_type;
306 -------------------------------------------------------------------------------------------------
307 -- POPULATE_PLTABLE
308 -------------------------------------------------------------------------------------------------
309 PROCEDURE populate_pltable
310 (l_master_tab_route_id IN PQH_TABLE_ROUTE.TABLE_ROUTE_ID%TYPE,
311 paramname1 IN pqh_attributes.column_name%TYPE,
312 paramvalue1 IN VARCHAR2,
313 paramname2 IN pqh_attributes.column_name%TYPE,
314 paramvalue2 IN VARCHAR2,
315 paramname3 IN pqh_attributes.column_name%TYPE,
316 paramvalue3 IN VARCHAR2,
317 paramname4 IN pqh_attributes.column_name%TYPE,
318 paramvalue4 IN VARCHAR2,
319 paramname5 IN pqh_attributes.column_name%TYPE,
320 paramvalue5 IN VARCHAR2) IS
321 l_proc varchar2(72) := g_package||'populate_pltable';
322 i number := 1;
323 BEGIN
324 hr_utility.set_location('entering: ' ||l_proc,1200);
325 pqh_refresh_data.g_refresh_tab.DELETE;
326 --populate the g_refresh_tab to be used in replace_where_params_purge
327 pqh_refresh_data.g_refresh_tab(1).column_name := paramname1;
328 hr_utility.set_location(paramname1||'-'||get_col_type(paramname1,l_master_tab_route_id)||'-'||l_master_tab_route_id,1201);
329 if get_col_type(paramname1,l_master_tab_route_id) = 'D' then
330 pqh_refresh_data.g_refresh_tab(1).txn_val := ' fnd_date.canonical_to_date('''||paramvalue1||''')';
331 pqh_refresh_data.g_refresh_tab(1).column_type := 'N';
332 else
333 pqh_refresh_data.g_refresh_tab(1).txn_val := paramvalue1;
334 pqh_refresh_data.g_refresh_tab(1).column_type := get_col_type(paramname1,l_master_tab_route_id);
335 end if;
336 pqh_refresh_data.g_refresh_tab(2).column_name := paramname2;
337 hr_utility.set_location(paramname2||'-'||get_col_type(paramname2,l_master_tab_route_id)||'-'||l_master_tab_route_id,1202);
338 if get_col_type(paramname2,l_master_tab_route_id) = 'D' then
339 pqh_refresh_data.g_refresh_tab(2).txn_val := ' fnd_date.canonical_to_date('''||paramvalue2||''')';
340 pqh_refresh_data.g_refresh_tab(2).column_type := 'N';
341 else
342 pqh_refresh_data.g_refresh_tab(2).txn_val := paramvalue2;
343 pqh_refresh_data.g_refresh_tab(2).column_type := get_col_type(paramname2,l_master_tab_route_id);
344 end if;
345 pqh_refresh_data.g_refresh_tab(3).column_name := paramname3;
346 hr_utility.set_location(paramname3||'-'||get_col_type(paramname3,l_master_tab_route_id)||'-'||l_master_tab_route_id,1203);
347 if get_col_type(paramname3,l_master_tab_route_id) = 'D' then
348 pqh_refresh_data.g_refresh_tab(3).txn_val := ' fnd_date.canonical_to_date('''||paramvalue3||''')';
349 pqh_refresh_data.g_refresh_tab(3).column_type := 'N';
350 else
351 pqh_refresh_data.g_refresh_tab(3).txn_val := paramvalue3;
352 pqh_refresh_data.g_refresh_tab(3).column_type := get_col_type(paramname3,l_master_tab_route_id);
353 end if;
354 pqh_refresh_data.g_refresh_tab(4).column_name := paramname4;
355 hr_utility.set_location(paramname4||'-'||get_col_type(paramname4,l_master_tab_route_id)||'-'||l_master_tab_route_id,1204);
356 if get_col_type(paramname4,l_master_tab_route_id) = 'D' then
357 pqh_refresh_data.g_refresh_tab(4).txn_val := ' fnd_date.canonical_to_date('''||paramvalue4||''')';
358 pqh_refresh_data.g_refresh_tab(4).column_type := 'N';
359 else
360 pqh_refresh_data.g_refresh_tab(4).txn_val := paramvalue4;
361 pqh_refresh_data.g_refresh_tab(4).column_type := get_col_type(paramname4,l_master_tab_route_id);
362 end if;
363 pqh_refresh_data.g_refresh_tab(5).column_name := paramname5;
364 hr_utility.set_location(paramname5||'-'||get_col_type(paramname5,l_master_tab_route_id)||'-'||l_master_tab_route_id,1205);
365 if get_col_type(paramname5,l_master_tab_route_id) = 'D' then
366 pqh_refresh_data.g_refresh_tab(5).txn_val := ' fnd_date.canonical_to_date('''||paramvalue5||''')';
367 pqh_refresh_data.g_refresh_tab(5).column_type := 'N';
368 else
369 pqh_refresh_data.g_refresh_tab(5).txn_val := paramvalue5;
370 pqh_refresh_data.g_refresh_tab(5).column_type := get_col_type(paramname5,l_master_tab_route_id);
371 end if;
372 -- display values of g_refresh_tab array
373
374 FOR i IN 1..5 LOOP
375 If pqh_refresh_data.g_refresh_tab(i).column_name = 'SHORT_NAME' THEN
376 g_short_name:= pqh_refresh_data.g_refresh_tab(i).txn_val;
377 END IF;
378 hr_utility.set_location(pqh_refresh_data.g_refresh_tab(i).column_name,10);
379 hr_utility.set_location(pqh_refresh_data.g_refresh_tab(i).column_type,11);
380 hr_utility.set_location(pqh_refresh_data.g_refresh_tab(i).txn_val,12);
381 END LOOP;
382 hr_utility.set_location('leaving: ' ||l_proc,1300);
383 END populate_pltable;
384 ----------------------------------------------------------------------------------------------
385 -- DEL_CHILD_RECORDS
386 ----------------------------------------------------------------------------------------------
387 PROCEDURE del_child_records
388 (p_alias_name IN pqh_table_route.table_alias%TYPE,
389 p_parent_pk_value IN NUMBER) IS
390 l_proc varchar2(72) := g_package||'del_child_records';
391 l_alias_name pqh_table_route.table_alias%TYPE default null;
392 l_tab_route_id pqh_table_route.table_route_id%TYPE;
393 c_pk_col_name pqh_attributes.column_name%TYPE;
394 l_select_stmt varchar2(8000) DEFAULT null;
395 l_parent_pk_value number;
396 --
397 l_all_child_rows_array dbms_sql.varchar2_table;
398 l_child_alias pqh_table_route.table_alias%TYPE;
399 l_from_clause_txn pqh_table_route.from_clause%type;
400 l_where_clause_in_txn pqh_table_route.where_clause%type;
401 l_where_clause_out_txn pqh_table_route.where_clause%type;
402 l_tot_txn_rows NUMBER;
403 l_error_flag BOOLEAN;
404 i NUMBER :=1 ;
405 /* Re-writing the cursor for Purging all the child/grand-child tables
406 CURSOR
407 get_child_alias(p_alias_name in pqh_table_route.table_alias%type) IS
408 SELECT
409 child_node_type
410 FROM
411 per_gen_hier_node_types
412 WHERE
413 parent_node_type = UPPER(p_alias_name) AND hierarchy_type = 'GENERIC_PURGE';
414 */
415 CURSOR csr_child_alias(p_alias_name IN pqh_table_route.table_alias%TYPE) IS
416 SELECT child_node_type
417 FROM per_gen_hier_node_types
418 WHERE hierarchy_type = 'GENERIC_PURGE'
419 START WITH parent_node_type = UPPER(p_alias_name)
420 CONNECT BY parent_node_type = PRIOR child_node_type;
421
422 BEGIN
423 hr_utility.set_location('entering: ' ||l_proc,1500);
424 l_parent_pk_value := p_parent_pk_value;
425 l_alias_name := p_alias_name;
426 OPEN csr_child_alias(l_alias_name);
427 LOOP
428 FETCH csr_child_alias INTO l_child_alias;
429 EXIT WHEN csr_child_alias%NOTFOUND;
430 /*
431
432 OPEN get_table_id(l_child_alias);
433 LOOP
434 FETCH get_table_id INTO l_tab_route_id;
435 EXIT WHEN get_table_id%NOTFOUND;
436 END LOOP;
437 CLOSE get_table_id;
438 --get the PK column name
439 OPEN get_pk_col_name(l_tab_route_id);
440 LOOP
441 FETCH get_pk_col_name INTO c_pk_col_name;
442 EXIT WHEN get_pk_col_name%NOTFOUND;
443 END LOOP;
444 CLOSE get_pk_col_name;
445 OPEN c3_from_where ( l_tab_route_id ) ;
446 LOOP
447 -- this gets the from and where clause , one row only
448 FETCH c3_from_where INTO l_from_clause_txn, l_where_clause_in_txn;
449 EXIT WHEN c3_from_where%NOTFOUND;
450 END LOOP;
451 CLOSE c3_from_where ;
452 */
453 l_all_child_rows_array.DELETE;
454 table_route_details(p_table_alias => l_child_alias
455 ,p_primary_key_flag => 'Y'
456 ,p_table_route_id => l_tab_route_id
457 ,p_from_clause => l_from_clause_txn
458 ,p_where_clause => l_where_clause_in_txn
459 ,p_primary_key_col => c_pk_col_name);
460
461 l_select_stmt :='select '|| c_pk_col_name ;
462 hr_utility.set_location('Parent key value '||l_parent_pk_value,20);
463 pqh_refresh_data.replace_where_params_purge
464 ( p_where_clause_in => l_where_clause_in_txn,
465 p_txn_tab_flag => 'Y',
466 p_txn_id => l_parent_pk_value,
467 p_where_clause_out => l_where_clause_out_txn );
468
469 pqh_refresh_data.get_all_rows
470 (p_select_stmt => l_select_stmt,
471 p_from_clause => l_from_clause_txn,
472 p_where_clause => l_where_clause_out_txn,
473 p_total_columns => 1,
474 p_total_rows => l_tot_txn_rows,
475 p_all_txn_rows => l_all_child_rows_array );
476 FOR i in NVL(l_all_child_rows_array.FIRST,0)..NVL(l_all_child_rows_array.LAST,-1)
477 LOOP
478 hr_utility.set_location('Child alias '||l_child_alias,20);
479 hr_utility.set_location('child pk '||l_all_child_rows_array(i),25);
480 del_child_records(l_child_alias,l_all_child_rows_array(i));
481 hr_utility.set_location('Deleting Child alias '||l_child_alias,20);
482 hr_utility.set_location('Deleting child pk '||l_all_child_rows_array(i),25);
483 call_delete_api
484 (p_tab_route_id => l_tab_route_id,
485 p_pk_value => l_all_child_rows_array(i),
486 p_from_clause_txn => l_from_clause_txn,
487 p_pk_col_name => c_pk_col_name);
488
489
490 END LOOP;
491 END LOOP;
492 CLOSE csr_child_alias;
493 hr_utility.set_location('leaving: ' ||l_proc,1600);
494 END del_child_records;
495 -----------------------------------------------------------------------------------------
496 -- CALL_DELETE_API
497 ------------------------------------------------------------------------------------------
498 Procedure call_delete_api
499 (p_tab_route_id IN pqh_table_route.table_route_id%TYPE,
500 p_pk_value IN NUMBER,
501 p_from_clause_txn IN pqh_table_route.from_clause%TYPE,
502 p_pk_col_name IN pqh_attributes.column_name%TYPE) IS
503 -- p_errror_flag OUT BOOLEAN ) IS
504 --This Cursor will get the delete api to be called for the respective tables.
505 ---------------------Cursor and Variable Declarations-------------------------------------
506 CURSOR
507 csr_delete_api_name( p_table_route_id IN pqh_table_route.table_route_id%TYPE) IS
508 SELECT copy_function_name
509 FROM pqh_copy_entity_functions
510 WHERE table_route_id = p_table_route_id;
511 /*
512 CURSOR
513 get_process_log_id(p_txn_value IN NUMBER,
514 l_short_name IN VARCHAR2) IS
515 SELECT
516 process_log_id from pqh_process_log
517 WHERE txn_id = p_txn_value AND module_cd = UPPER(l_short_name);
518
519 CURSOR get_plog_ovn(l_process_log_id IN NUMBER) IS
520 SELECT
521 object_version_number from pqh_process_log
522 WHERE
523 process_log_id = l_process_log_id;
524 */
525 --
526 l_proc varchar2(72) := g_package||'call_delete_api';
527 l_dummy_in varchar2(4000);
528 l_dummy_out varchar2(4000);
529 l_select_stmt varchar2(8000);
530 -- l_ovn_value dbms_sql.varchar2_table;
531 -- l_plog_value dbms_sql.varchar2_table;
532 l_from_clause_txn pqh_table_route.from_clause%type;
533 l_where_clause_in_txn pqh_table_route.where_clause%type;
534 l_where_clause_out_txn pqh_table_route.where_clause%type;
535 l_ovn_rows number;
536 l_pk_value Number;
537 l_ovn NUMBER;
538 l_dummy_out1 varchar2(4000);
539 l_dummy_out2 varchar2(4000);
540 l_pk_col_name PQH_ATTRIBUTES.COLUMN_NAME%TYPE;
541 l_process_log_id NUMBER;
542 -----------------------------------------------------------------------------------------
543 BEGIN
544 hr_utility.set_location('entering: ' ||l_proc,1700);
545 l_pk_value := p_pk_value;
546 l_pk_col_name := p_pk_col_name;
547 -- l_plog_value.DELETE;
548 -- l_ovn_value.DELETE;
549 l_from_clause_txn := p_from_clause_txn;
550
551
552 /*
553 l_select_stmt := ' select object_version_number ' ;
554 l_where_clause_in_txn := ' c_pk_col_name = <l_pk_value> ' ;
555 l_where_clause_in_txn := replace(l_where_clause_in_txn , 'c_pk_col_name' , l_pk_col_name);
556 --dbms_output.put_line('after ovn where replace' || l_where_clause_in_txn);
557 pqh_refresh_data.replace_where_params_purge
558 ( p_where_clause_in => l_where_clause_in_txn,
559 p_txn_tab_flag => 'Y',
560 p_txn_id => l_pk_value,
561 p_where_clause_out => l_where_clause_out_txn );
562 --
563 pqh_refresh_data.get_all_rows
564 (p_select_stmt => l_select_stmt,
565 p_from_clause => l_from_clause_txn,
566 p_where_clause => l_where_clause_out_txn,
567 p_total_columns => 1,
568 p_total_rows => l_ovn_rows,
569 p_all_txn_rows => l_ovn_value );
570 l_ovn := l_ovn_value(1);
571 */
572 l_select_stmt := 'SELECT object_version_number FROM '||l_from_clause_txn||' WHERE '||l_pk_col_name||' = :1';
573 EXECUTE IMMEDIATE l_select_stmt INTO l_ovn USING l_pk_value ;
574 --
575 OPEN csr_delete_api_name(p_tab_route_id);
576 FETCH csr_delete_api_name INTO l_dummy_in;
577 CLOSE csr_delete_api_name;
578 hr_utility.set_location('l_dummy_in'||substr(l_dummy_in,1,50),1710);
579 hr_utility.set_location('l_dummy_in'||substr(l_dummy_in,51,50),1720);
580 -- l_dummy_in := replace(l_dummy_in, 'R_OBJECT_VERSION_NUMBER' , l_ovn_value(1));
581
582 l_dummy_in := replace(l_dummy_in, '<p_effective_date>', g_effective_date);
583 pqh_refresh_data.replace_where_params_purge
584 ( p_where_clause_in => l_dummy_in,
585 p_txn_tab_flag => 'Y',
586 p_txn_id => l_pk_value,
587 p_where_clause_out => l_dummy_out);
588 --
589 hr_utility.set_location('l_dummy_out'||substr(l_dummy_out,1,50),1750);
590 hr_utility.set_location('l_dummy_out'||substr(l_dummy_out,51,50),1760);
591 hr_utility.set_location('l_dummy_out'||substr(l_dummy_out,101,50),1770);
592 hr_utility.set_location('l_dummy_out'||substr(l_dummy_out,151,50),1780);
593 hr_utility.set_location('l_dummy_out'||substr(l_dummy_out,201,50),1790);
594 hr_utility.set_location('l_dummy_out'||substr(l_dummy_out,251,50),1800);
595 hr_utility.set_location('l_dummy_out'||substr(l_dummy_out,301,50),1810);
596 hr_utility.set_location('l_dummy_out'||substr(l_dummy_out,351,50),1820);
597 --Execute the procedure to delete the record with passed PK value.
598 l_dummy_out1 := substr(l_dummy_out,1,instr(l_dummy_out,'R_OBJECT_VERSION_NUMBER')-1 );
599 l_dummy_out2 := substr(l_dummy_out,instr(l_dummy_out,'R_OBJECT_VERSION_NUMBER') +length('R_OBJECT_VERSION_NUMBER'),length(l_dummy_out));
600
601 --code added for deleting any workflow notifications sent for the Transaction
602 IF g_master_alias IN ('PPTX','PPWS','PBPR') THEN
603 delete_wf_data(p_pk_value => l_pk_value);
604 END IF;
605 --
606 --added by kgowripe for deleting the Process log data corresponding to a transaction
607 delete_process_log_data(p_pk_value => l_pk_value);
608 --
609 EXECUTE IMMEDIATE 'DECLARE ' ||
610 ' p_ovn NUMBER;' ||
611 'BEGIN ' ||
612 'p_ovn :=' || l_ovn || ';' ||
613 l_dummy_out1 ||
614 'p_ovn' ||
615 l_dummy_out2|| ';'||
616 'END;' ;
617
618 hr_utility.set_location('leaving: ' ||l_proc,1900);
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 hr_utility.set_location(SQLERRM,15);
623 -- retcode:=2;
624 rollback to s1;
625 g_error_flag := TRUE;
626 --
627 END call_delete_api;
628 ----------------------------------------------------------------------------------------------
629 -- ENTER_CONC_LOG
630 ----------------------------------------------------------------------------------------------
631 PROCEDURE enter_conc_log(p_pk_value IN NUMBER,
632 TAB_ROU_ID IN NUMBER,
633 p_from_clause_txn IN pqh_table_route.from_clause%TYPE,
634 p_pk_col_name IN pqh_attributes.column_name%TYPE)
635 -- p_error_flag IN boolean)
636 IS
637 ------------------------DECLARATIONS----------------------------------------------------------
638 --
639 CURSOR
640 csr_err_col_name(p_table_route_id IN pqh_table_route.table_route_id%TYPE) IS
641 SELECT upper(att.column_name) column_name
642 FROM pqh_attributes att
643 , pqh_special_attributes sat
644 , pqh_txn_category_attributes tca
645 WHERE att.attribute_id = tca.attribute_id
646 and att.master_table_route_id = p_table_route_id
647 and tca.transaction_category_id = g_purge_txn_catg_id
648 and tca.txn_category_attribute_id = sat.txn_category_attribute_id
649 and sat.attribute_type_cd = 'ERROR_KEY';
650 lcol varchar2(50);
651 l_select varchar2(8000);
652 L_txn_value DBMS_SQL.VARCHAR2_TABLE;
653 l_where_clause varchar2(8000);
654 l_tot_columns number :=0;
655 l_tot_rows number;
656 L_from_clause varchar2(4000);
657 i number;
658 -- l_error_flag boolean;
659 --
660 BEGIN
661 -- l_error_flag := g_error_flag;
662 l_select := 'select ';
663 OPEN csr_ERR_col_name(tab_rou_id);
664 LOOP
665 FETCH csr_err_col_name INTO LCOL;
666 EXIT WHEN csr_err_col_name%NOTFOUND;
667 l_select := l_select || 'TO_CHAR('||lcol ||'),';
668 l_tot_columns := l_tot_columns +1;
669 END LOOP;
670 L_SELECT := RTRIM(L_SELECT,',');
671 CLOSE csr_err_col_name;
672 L_WHERE_CLAUSE := p_pk_col_name || '=' || p_pk_value;
673
674 l_from_clause := p_from_clause_txn;
675 pqh_refresh_data.get_all_rows
676 (p_select_stmt => l_select,
677 p_from_clause => l_from_clause,
678 p_where_clause => l_where_clause,
679 p_total_columns => l_tot_columns,
680 p_total_rows => l_tot_rows,
681 p_all_txn_rows => l_txn_value );
682 --dbms_output.put_line('totrows:' || l_tot_rows);
683 --
684 FOR i IN NVL(l_txn_value.first,0)..NVL(l_txn_value.last,-1)
685 LOOP
686 fnd_file.put(fnd_file.log,l_txn_value(i)|| ' ');
687 END LOOP;
688
689 END enter_conc_log;
690 END PQH_GENERIC_PURGE;