[Home] [Help]
PACKAGE BODY: APPS.GMA_PURGE_ENGINE
Source
1 PACKAGE BODY GMA_PURGE_ENGINE AS
2 /* $Header: GMAPRGEB.pls 120.1.12010000.1 2008/07/30 06:17:23 appldev ship $ */
3
4 FUNCTION archivecleanup
5 (p_purge_id sy_purg_mst.purge_id%TYPE,
6 p_tablenames_tab GMA_PURGE_DDL.g_tablename_tab_type,
7 p_tableactions_tab GMA_PURGE_DDL.g_tableaction_tab_type,
8 p_tablecount INTEGER,
9 p_indexes_tab GMA_PURGE_DDL.g_statement_tab_type,
10 p_indexcount INTEGER,
11 p_idx_tablespaces_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablespace_name_tab_type,
12 p_idx_tablespaces_count IN OUT NOCOPY INTEGER,
13 p_owner user_users.username%TYPE,
14 p_appl_short_name fnd_application.application_short_name%TYPE,
15 p_disable_constraints BOOLEAN,
16 p_debug_flag BOOLEAN)
17 RETURN BOOLEAN;
18
19 PROCEDURE report_exit (p_purge_id sy_purg_mst.purge_id%TYPE,
20 p_status INTEGER);
21
22 FUNCTION initarchive
23 (p_purge_id sy_purg_mst.purge_id%TYPE,
24 p_purge_type sy_purg_def.purge_type%TYPE,
25 p_owner user_users.username%TYPE,
26 p_appl_short_name fnd_application.application_short_name%TYPE,
27 p_arctablename user_tables.table_name%TYPE,
28 p_arctables_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablename_tab_type,
29 p_arcactions_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tableaction_tab_type,
30 p_tablecount IN OUT NOCOPY INTEGER,
31 p_indexes_tab IN OUT NOCOPY GMA_PURGE_DDL.g_statement_tab_type,
32 p_indexcount IN OUT NOCOPY INTEGER,
33 p_idx_tablespaces_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablespace_name_tab_type,
34 p_idx_tablespaces_count IN OUT NOCOPY INTEGER,
35 p_disable_constraints BOOLEAN,
36 p_sizing_flag BOOLEAN,
37 p_debug_flag BOOLEAN)
38 RETURN BOOLEAN;
39
40 PROCEDURE doarchive
41 (p_purge_id sy_purg_mst.purge_id%TYPE,
42 p_purge_type sy_purg_def.purge_type%TYPE,
43 p_owner user_users.username%TYPE,
44 p_appl_short_name fnd_application.application_short_name%TYPE,
45 p_user NUMBER,
46 p_arcrowtable user_tables.table_name%TYPE,
47 p_arctables_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablename_tab_type,
48 p_arcactions_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tableaction_tab_type,
49 p_tablecount IN OUT NOCOPY INTEGER,
50 p_totarchiverows IN OUT NOCOPY INTEGER,
51 p_totdeleterows IN OUT NOCOPY INTEGER,
52 p_sizing BOOLEAN,
53 p_commitfrequency INTEGER,
54 p_disable_constraints BOOLEAN,
55 p_debug_flag BOOLEAN);
56
57 PROCEDURE getrows
58 (p_purge_id sy_purg_mst.purge_id%TYPE,
59 p_owner user_users.username%TYPE,
60 p_appl_short_name fnd_application.application_short_name%TYPE,
61 p_sqlstatement sy_purg_def.sqlstatement%TYPE,
62 p_tablespace user_tablespaces.tablespace_name%TYPE,
63 p_arcrowtable user_tables.table_name%TYPE,
64 p_debug_flag BOOLEAN);
65
66 PROCEDURE logresults
67 (p_purge_id sy_purg_mst.purge_id%TYPE,
68 p_user NUMBER,
69 p_arctables_tab GMA_PURGE_DDL.g_tablename_tab_type,
70 p_arcactions_tab GMA_PURGE_DDL.g_tableaction_tab_type,
71 p_tablecount INTEGER,
72 p_totarchiverows IN OUT NOCOPY INTEGER,
73 p_totdeleterows IN OUT NOCOPY INTEGER);
74
75 PROCEDURE archive(p_purge_id sy_purg_mst.purge_id%TYPE,
76 p_purge_type sy_purg_def.purge_type%TYPE,
77 p_owner user_users.username%TYPE,
78 p_appl_short_name fnd_application.application_short_name%TYPE,
79 p_user NUMBER,
80 p_sqlstatement sy_purg_def.sqlstatement%TYPE,
81 p_arcrowbasename user_tables.table_name%TYPE,
82 p_arctablespace user_tablespaces.tablespace_name%TYPE,
83 p_arctables_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablename_tab_type,
84 p_arcactions_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tableaction_tab_type,
85 p_totarchiverows IN OUT NOCOPY INTEGER,
86 p_totdeleterows IN OUT NOCOPY INTEGER,
87 p_sizing BOOLEAN,
88 p_commitfrequency INTEGER,
89 p_inittime IN OUT NOCOPY DATE,
90 p_starttime IN OUT NOCOPY DATE,
91 p_disable_constraints BOOLEAN,
92 p_debug_flag BOOLEAN);
93
94 PROCEDURE purge(p_purge_id sy_purg_mst.purge_id%TYPE,
95 p_purge_type sy_purg_def.purge_type%TYPE,
96 p_owner user_users.username%TYPE,
97 p_appl_short_name fnd_application.application_short_name%TYPE,
98 p_debug_flag BOOLEAN);
99
100 /* These four GLPOSTED functions is added to check for unposted transactions in purge types,
101 The main purpose of this is not to delete any transaction which have unposted rows in it. */
102
103 /* added new TEMP TABLE logic */
104 FUNCTION GLPOSTED_OPSO
105 (P_Purge_id in sy_purg_mst.purge_id%TYPE,
106 p_purge_type sy_purg_def.purge_type%TYPE,
107 p_owner user_users.username%TYPE,
108 p_debug_flag BOOLEAN)
109 RETURN LONG;
110
111 /* added new TEMP TABLE logic */
112 FUNCTION GLPOSTED_JRNL
113 (P_Purge_id in sy_purg_mst.purge_id%TYPE,
114 p_purge_type sy_purg_def.purge_type%TYPE,
115 p_owner user_users.username%TYPE,
116 p_debug_flag BOOLEAN)
117 RETURN LONG;
118
119 /* added new TEMP TABLE logic */
120 FUNCTION GLPOSTED_PORD
121 (P_Purge_id in sy_purg_mst.purge_id%TYPE,
122 p_purge_type sy_purg_def.purge_type%TYPE,
123 p_owner user_users.username%TYPE,
124 p_debug_flag BOOLEAN)
125 RETURN LONG;
126
127 /* added new TEMP TABLE logic */
128 FUNCTION GLPOSTED_PROD
129 (P_Purge_id in sy_purg_mst.purge_id%TYPE,
130 p_purge_type sy_purg_def.purge_type%TYPE,
131 p_owner user_users.username%TYPE,
132 p_debug_flag BOOLEAN)
133 RETURN LONG;
134
135
136 -- Create temporary table for PROD and APRD KH
137 FUNCTION Tempcreate(p_purge_id sy_purg_mst.purge_id%TYPE,
138 p_purge_type sy_purg_def.purge_type%TYPE,
139 p_owner user_users.username%TYPE,
140 p_debug_flag BOOLEAN)
141 RETURN CHAR;
142
143 -- Insert rows in temporary table for PROD and APRD KH
144 PROCEDURE Tempinsert(p_purge_id sy_purg_mst.purge_id%TYPE,
145 p_purge_type sy_purg_def.purge_type%TYPE,
146 p_all_ids number,
147 p_debug_flag BOOLEAN);
148
149 -- Drop the temporary table for PROD and APRD KH
150 PROCEDURE Tempdrop(p_purge_id sy_purg_mst.purge_id%TYPE,
151 p_purge_type sy_purg_def.purge_type%TYPE,
152 p_debug_flag BOOLEAN);
153
154 -- Drop the temporary table for PROD and APRD KH
155 PROCEDURE ResetTestPurge(p_purge_id sy_purg_mst.purge_id%TYPE,
156 p_purge_type sy_purg_def.purge_type%TYPE,
157 p_debug_flag varchar2);
158
159 -- Created a FUNCTION for GSCC standard fix bug 3871659
160 --Standard: File.Sql.6 - Do NOT include any references to hardcoded schema
161 FUNCTION Get_GmaSchemaName
162 RETURN VARCHAR2;
163
164
165 /***********************************************************/
166
167 PROCEDURE doarchive(p_purge_id sy_purg_mst.purge_id%TYPE,
168 p_purge_type sy_purg_def.purge_type%TYPE,
169 p_owner user_users.username%TYPE,
170 p_appl_short_name fnd_application.application_short_name%TYPE,
171 p_user NUMBER,
172 p_arcrowtable user_tables.table_name%TYPE,
173 p_arctables_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablename_tab_type,
174 p_arcactions_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tableaction_tab_type,
175 p_tablecount IN OUT NOCOPY INTEGER,
176 p_totarchiverows IN OUT NOCOPY INTEGER,
177 p_totdeleterows IN OUT NOCOPY INTEGER,
178 p_sizing BOOLEAN,
179 p_commitfrequency INTEGER,
180 p_disable_constraints BOOLEAN,
181 p_debug_flag BOOLEAN) IS
182
183 l_indexes_tab GMA_PURGE_DDL.g_statement_tab_type;
184 l_idx_tablespaces_tab GMA_PURGE_DDL.g_tablespace_name_tab_type;
185 l_idx_tablespaces_count INTEGER;
186
187 l_indexcount BINARY_INTEGER;
188
189 l_badpurge EXCEPTION; -- purge did not complete
190 l_badsetup EXCEPTION; -- initialization failed
191 l_noacttable EXCEPTION; -- arc action table does not exist
192 l_noarctable EXCEPTION; -- arc master table does not exist
193 l_badcleanup EXCEPTION; -- archive cleanup did not complete
194
195 l_initstarttime DATE; -- time PA init started
196 l_copystarttime DATE; -- time actual copying started
197 l_cleanupstarttime DATE; -- time cleanup started
198
199 l_continue BOOLEAN;
200 BEGIN
201
202
203 -- make sure archive master table exists
204 IF (GMA_PURGE_VALIDATE.is_table(p_purge_id,p_arcrowtable) <> TRUE) THEN
205 RAISE l_noarctable;
206 END IF;
207
208 -- get set up for archive; create target archive tables, disable constraints
209 l_initstarttime := sysdate;
210 IF (GMA_PURGE_ENGINE.initarchive(p_purge_id,
211 p_purge_type,
212 p_owner,
213 p_appl_short_name,
214 p_arcrowtable,
215 p_arctables_tab,
216 p_arcactions_tab,
217 p_tablecount,
218 l_indexes_tab,
219 l_indexcount,
220 l_idx_tablespaces_tab,
221 l_idx_tablespaces_count,
222 p_disable_constraints,
223 p_sizing,
224 p_debug_flag) = FALSE) THEN
225 RAISE l_badsetup;
226 END IF;
227 -- GMA_PURGE_UTILITIES.printlong(p_purge_id,'Archive initialization completed in ' ||
228 GMA_PURGE_UTILITIES.printlong(p_purge_id,GMA_PURGE_ENGINE.PA_OPTION_NAME|| ' initialization completed in ' ||
229 to_char(trunc((sysdate - l_initstarttime) * 86400)) ||
230 ' seconds - ' ||
231 GMA_PURGE_UTILITIES.chartime);
232
233 -- well, we're ready... Do the purge
234 l_copystarttime := sysdate;
235 IF (GMA_PURGE_COPY.archiveengine(p_purge_id,
236 p_owner,
237 p_appl_short_name,
238 p_user,
239 p_arcrowtable,
240 p_tablecount,
241 p_arctables_tab,
242 p_arcactions_tab,
243 p_debug_flag,
244 p_commitfrequency) <> TRUE) THEN
245 RAISE l_badpurge;
246 END IF;
247
248 -- log copy time to master record for statistics
249 UPDATE sy_purg_mst
250 SET archive_table_count = (p_tablecount + 1)
251 , copy_elapsed_time =
252 trunc(((sysdate - l_copystarttime) * 86400),2)
253 , last_update_date = sysdate
254 , last_updated_by = p_user
255 WHERE purge_id = p_purge_id;
256
257 COMMIT;
258
259 -- GMA_PURGE_UTILITIES.printlong(p_purge_id,'Archive copy function completed in ' ||
260 GMA_PURGE_UTILITIES.printlong(p_purge_id,GMA_PURGE_ENGINE.PA_OPTION_NAME||' copy function completed in ' ||
261 to_char(trunc((sysdate - l_copystarttime) * 86400)) ||
262 ' seconds - ' ||
263 GMA_PURGE_UTILITIES.chartime);
264
265 -- clean up a few things
266 l_cleanupstarttime := SYSDATE;
267 IF (GMA_PURGE_ENGINE.archivecleanup(p_purge_id,
268 p_arctables_tab,
269 p_arcactions_tab,
270 p_tablecount,
271 l_indexes_tab,
272 l_indexcount,
273 l_idx_tablespaces_tab,
274 l_idx_tablespaces_count,
275 p_owner,
276 p_appl_short_name,
277 p_disable_constraints,
278 p_debug_flag) <> TRUE) THEN
279 RAISE l_badcleanup;
280 END IF;
281 -- GMA_PURGE_UTILITIES.printlong(p_purge_id,'Cleanup function completed in ' ||
282 GMA_PURGE_UTILITIES.printlong(p_purge_id,GMA_PURGE_ENGINE.PA_OPTION_NAME||' Cleanup function completed in ' ||
283 to_char(trunc((sysdate - l_cleanupstarttime) * 86400)) ||
284 ' seconds - ' ||
285 GMA_PURGE_UTILITIES.chartime);
286
287 -- Cool! We're done.
288 -- GMA_PURGE_UTILITIES.printlong(p_purge_id,'Archive function completed in ' ||
289 GMA_PURGE_UTILITIES.printlong(p_purge_id,GMA_PURGE_ENGINE.PA_OPTION_NAME||' function completed in ' ||
290 to_char(trunc((sysdate - l_initstarttime) * 86400)) ||
291 ' seconds - ' ||
292 GMA_PURGE_UTILITIES.chartime);
293
294 EXCEPTION
295
299 RAISE;
296 WHEN l_noacttable THEN
297 GMA_PURGE_UTILITIES.printlong(p_purge_id,
298 'Serious problem - no archive action table');
300
301 WHEN l_noarctable THEN
302 GMA_PURGE_UTILITIES.printlong(p_purge_id,
303 'Serious problem - no archive master table');
304 RAISE;
305
306 WHEN l_badpurge THEN
307 GMA_PURGE_UTILITIES.printlong(p_purge_id,
308 'Serious problem - purge did not complete.');
309 -- try to fix DB state
310 l_continue := GMA_PURGE_ENGINE.archivecleanup(p_purge_id,
311 p_arctables_tab,
312 p_arcactions_tab,
313 p_tablecount,
314 l_indexes_tab,
315 l_indexcount,
316 l_idx_tablespaces_tab,
317 l_idx_tablespaces_count,
318 p_owner,
319 p_appl_short_name,
320 p_disable_constraints,
321 p_debug_flag);
322
323 WHEN l_badsetup THEN
324 GMA_PURGE_UTILITIES.printlong(p_purge_id,
325 'Serious problem - archive master table setup');
326 -- try to fix DB state
327 l_continue := GMA_PURGE_ENGINE.archivecleanup(p_purge_id,
328 p_arctables_tab,
329 p_arcactions_tab,
330 p_tablecount,
331 l_indexes_tab,
332 l_indexcount,
333 l_idx_tablespaces_tab,
334 l_idx_tablespaces_count,
335 p_owner,
336 p_appl_short_name,
337 p_disable_constraints,
338 p_debug_flag);
339 RAISE;
340
341 WHEN OTHERS THEN
342 GMA_PURGE_UTILITIES.printlong(p_purge_id,
343 'Problem raised in GMA_PURGE_ENGINE.doarchive.');
344 GMA_PURGE_UTILITIES.printlong(p_purge_id,
345 'Unhandled EXCEPTION - ' || sqlerrm);
346 -- try to fix DB state
347 l_continue := GMA_PURGE_ENGINE.archivecleanup(p_purge_id,
348 p_arctables_tab,
349 p_arcactions_tab,
350 p_tablecount,
351 l_indexes_tab,
352 l_indexcount,
353 l_idx_tablespaces_tab,
354 l_idx_tablespaces_count,
355 p_owner,
356 p_appl_short_name,
357 p_disable_constraints,
358 p_debug_flag);
359 RAISE;
360
361 END doarchive;
362
363 /***********************************************************/
364
365 FUNCTION archivecleanup
366 (p_purge_id sy_purg_mst.purge_id%TYPE,
367 p_tablenames_tab GMA_PURGE_DDL.g_tablename_tab_type,
368 p_tableactions_tab GMA_PURGE_DDL.g_tableaction_tab_type,
369 p_tablecount INTEGER,
370 p_indexes_tab GMA_PURGE_DDL.g_statement_tab_type,
371 p_indexcount INTEGER,
372 p_idx_tablespaces_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablespace_name_tab_type,
373 p_idx_tablespaces_count IN OUT NOCOPY INTEGER,
374 p_owner user_users.username%TYPE,
375 p_appl_short_name fnd_application.application_short_name%TYPE,
376 p_disable_constraints BOOLEAN,
377 p_debug_flag BOOLEAN)
378 RETURN BOOLEAN IS
379 BEGIN
380
381 -- Re-enable the constraints if we're supposed to.
382 IF (p_disable_constraints = TRUE) THEN
383 GMA_PURGE_DDL.enableindexes(p_purge_id,
384 p_indexes_tab,
385 p_indexcount,
386 p_idx_tablespaces_tab,
387 p_idx_tablespaces_count,
388 p_owner,
389 p_appl_short_name,
390 p_debug_flag);
391 GMA_PURGE_DDL.alterconstraints(p_purge_id,
392 p_tablenames_tab,
393 p_tableactions_tab,
394 p_tablecount,
395 p_idx_tablespaces_tab,
396 p_idx_tablespaces_count,
397 p_owner,
398 p_appl_short_name,
399 'ENABLE',
400 p_debug_flag);
401 END IF;
402
403 RETURN TRUE;
404
405 EXCEPTION
406
407 WHEN OTHERS THEN
408 GMA_PURGE_UTILITIES.printlong(p_purge_id,
409 'Problem raised in GMA_PURGE_ENGINE.archivecleanup.');
413
410 GMA_PURGE_UTILITIES.printlong(p_purge_id,
411 'Unhandled EXCEPTION - ' || sqlerrm);
412 RAISE;
414 END;
415
416 /***********************************************************/
417
418 FUNCTION initarchive
419 (p_purge_id sy_purg_mst.purge_id%TYPE,
420 p_purge_type sy_purg_def.purge_type%TYPE,
421 p_owner user_users.username%TYPE,
422 p_appl_short_name fnd_application.application_short_name%TYPE,
423 p_arctablename user_tables.table_name%TYPE,
424 p_arctables_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablename_tab_type,
425 p_arcactions_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tableaction_tab_type,
426 p_tablecount IN OUT NOCOPY INTEGER,
427 p_indexes_tab IN OUT NOCOPY GMA_PURGE_DDL.g_statement_tab_type,
428 p_indexcount IN OUT NOCOPY INTEGER,
429 p_idx_tablespaces_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablespace_name_tab_type,
430 p_idx_tablespaces_count IN OUT NOCOPY INTEGER,
431 p_disable_constraints BOOLEAN,
432 p_sizing_flag BOOLEAN,
433 p_debug_flag BOOLEAN)
434 RETURN BOOLEAN IS
435 -- Check archive row table to make sure all columns are real
436 -- tables with the right row type (rowid). Put all table names
437 -- in p_arctablestab, with master table first.
438
439 CURSOR l_tablename_rows_cur (c_purge_type sy_purg_def.purge_type%TYPE,
440 c_arctablename user_tables.table_name%TYPE,
441 c_schema_name VARCHAR2) IS
442 SELECT UC.column_name arctable
443 , UC.data_type drowtype
444 , decode(nvl(SD.archive_action,'K'),'D','D','K') arcaction
445 , UU.default_tablespace arctablespace
446 FROM all_tab_columns UC
447 , dba_users UU
448 , sy_purg_def_act SD
449 , sy_purg_def SP
450 WHERE UC.owner = c_schema_name
451 AND UU.USERNAME='GMA'
452 AND SD.purge_type = SP.purge_type
453 AND SD.table_name = UC.column_name
454 AND SP.purge_type = c_purge_type
455 AND UC.table_name = c_arctablename
456 ORDER BY UC.column_id;
457 /*
458 SELECT UC.column_name arctable
459 , UC.data_type drowtype
460 , decode(nvl(SD.archive_action,'K'),'D','D','K') arcaction
461 , nvl(SD.target_tablespace,
462 nvl(SP.default_target_tablespace,
463 UU.default_tablespace
464 )
465 ) arctablespace
466 FROM user_users UU
467 , sy_purg_def_act SD
468 , sy_purg_def SP
469 , all_tab_columns UC
470 WHERE UC.owner='GMA'
471 AND SD.purge_type = SP.purge_type
472 AND SD.table_name = UC.column_name
473 AND SP.purge_type = c_purge_type
474 AND UC.table_name = c_arctablename
475 ORDER BY UC.column_id;
476 */
477
478 -- Changed by Khaja user_tab_columns TO all_tab_columns
479 l_badrowtype EXCEPTION;
480 l_tablecount INTEGER;
481 l_schema_name VARCHAR2(30); /* Bug 4344986 */
482
483 BEGIN
484
485 l_tablecount := -1;
486
487 l_schema_name := Get_GmaSchemaName; /* Bug 4344986 */
488
489 -- do setup for each table
490 FOR l_tablename_row IN l_tablename_rows_cur(p_purge_type,
491 p_arctablename,
492 l_schema_name) LOOP
493 l_tablecount := l_tablecount + 1; -- start table index at zero
494
495 -- make sure the column has the correct datatype, namely rowid
496 IF (l_tablename_row.drowtype <> 'ROWID') THEN
497 GMA_PURGE_UTILITIES.printlong(p_purge_id,
498 'Problem with ' || p_arctablename || ' - ' ||
499 l_tablename_row.arctable || 'of type ' || l_tablename_row.drowtype);
500 RAISE l_badrowtype;
501 END IF;
502
503 -- make sure the table exists
504 IF (GMA_PURGE_VALIDATE.is_table(p_purge_id,l_tablename_row.arctable) <> TRUE) THEN
505 GMA_PURGE_UTILITIES.printlong(p_purge_id,
506 'Problem with ' || p_arctablename || ' - ' ||
507 l_tablename_row.arctable || ' does not exist in ALL_TABLES.');
508 RETURN FALSE;
509 END IF;
510
511 -- create archive table
512 IF (GMA_PURGE_DDL.createarctable(p_purge_id,
513 l_tablename_row.arctable,
514 l_tablename_row.arctablespace,
515 p_owner,
516 p_appl_short_name,
517 p_sizing_flag,
518 p_arctablename,
519 p_debug_flag) <> TRUE) THEN
520 RETURN FALSE;
521 END IF;
522
523 -- Add the table to the array
524 p_arctables_tab(l_tablecount) := l_tablename_row.arctable;
525
526 -- added by khaja for TEST archive
527 IF PA_OPTION=3 THEN
528 l_tablename_row.arcaction:='K';
529 END IF;
533 p_tablecount := l_tablecount;
530 -- get the archive action and add that to the action table
531 p_arcactions_tab(l_tablecount) := l_tablename_row.arcaction;
532
534
535 END LOOP; -- each table
536
537 -- disable constraints if we're supposed to.
538 IF (p_disable_constraints = TRUE) THEN
539 GMA_PURGE_DDL.alterconstraints(p_purge_id,
540 p_arctables_tab,
541 p_arcactions_tab,
542 l_tablecount,
543 p_idx_tablespaces_tab,
544 p_idx_tablespaces_count,
545 p_owner,
546 p_appl_short_name,
547 'DISABLE',
548 p_debug_flag);
549 GMA_PURGE_DDL.disableindexes(p_purge_id,
550 p_arctables_tab,
551 p_arcactions_tab,
552 l_tablecount,
553 p_indexes_tab,
554 p_indexcount,
555 p_owner,
556 p_appl_short_name,
557 p_debug_flag);
558 END IF;
559
560 RETURN TRUE;
561
562 EXCEPTION
563
564 WHEN l_badrowtype THEN
565 GMA_PURGE_UTILITIES.printlong(p_purge_id,
566 'Serious problem - archive master table setup');
567 RAISE;
568
569 WHEN OTHERS THEN
570 GMA_PURGE_UTILITIES.printlong(p_purge_id,
571 'Problem raised in GMA_PURGE_ENGINE.initarchive.');
572 GMA_PURGE_UTILITIES.printlong(p_purge_id,
573 'Unhandled EXCEPTION - ' || sqlerrm);
574 RAISE;
575
576 END initarchive;
577
578 /***********************************************************/
579
580 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
581 retcode OUT NOCOPY VARCHAR2,
582 p_purge_id IN sy_purg_mst.purge_id%TYPE,
583 p_appl_short_name IN fnd_application.application_short_name%TYPE,
584 p_job_run IN NUMBER,
585 p_job_name IN VARCHAR2) IS
586
587
588 -- JKB Removed default above per GSCC.
589
590 -- main function for archive function
591
592 l_debug_flag_f CHAR(1);
593 l_disable_constraints_f CHAR(1);
594 l_sizing_f CHAR(1);
595 l_debug_flag BOOLEAN;
596 l_disable_constraints BOOLEAN;
597 l_sizing BOOLEAN;
598 l_commitfrequency INTEGER;
599 l_owner user_users.username%TYPE;
600 l_owner_verify user_users.username%TYPE;
601 l_aol_status BOOLEAN;
602
603 l_status sy_purg_mst.purge_status%TYPE;
604 l_testarcstatus sy_purg_mst.status%TYPE;
605 -- You know it.
606 l_orastatus sy_purg_mst.ora_status%TYPE;
607 -- Not that we're expecting trouble or anything.
608
609 -- dummy variables.
610 l_app_status VARCHAR2(50);
611 l_app_industry VARCHAR2(50);
612
613 -- funky little cheats
614 CURSOR l_arccursor_cur(c_purge_id sy_purg_mst.purge_id%TYPE) IS
615 SELECT SM.purge_type purgetype
616 , SD.sqlstatement arcsqlstatement
617 -- , NVL(SD.work_tablespace,
618 , UU.default_tablespace arctablespace
619 , SM.purge_status arcstatus
620 , SM.status testarcstatus
621 , nvl(SM.debug_flag,'F') debug_flag
622 , nvl(SM.disable_constraints_flag,'F') disable_constraints
623 , nvl(SM.calculate_storage_flag,'F') storage_flag
624 , nvl(SM.commit_frequency,750) commit_frequency
625 , nvl(SM.object_owner,'<NULL>') object_owner
626 FROM dba_users UU
627 , sy_purg_mst SM
628 , sy_purg_def SD
629 WHERE SD.purge_type (+) = SM.purge_type
630 AND UU.username = 'GMA'
631 AND SM.purge_id = c_purge_id;
632
633 -- Made BY KHAJA FROM user_users UU
634 CURSOR l_critcursor_cur(c_purge_id sy_purg_mst.purge_id%TYPE) IS
635
636 SELECT DC.crit_tag crit_tag
637 , REPLACE(NVL(DC.value_mask,'{X}'),
638 '{X}',
639 NVL(MC.crit_value,DC.default_value)) value
640 FROM sy_purg_mst_crit MC
641 , sy_purg_def_crit DC
642 , sy_purg_mst MS
643 WHERE MC.crit_tag = DC.crit_tag
644 AND MC.purge_id = MS.purge_id
645 AND DC.purge_type = MS.purge_type
646 AND MS.purge_id = c_purge_id;
647
648 CURSOR l_schema_cursor(c_schema_name all_users.username%TYPE) IS
649 SELECT username
650 FROM all_users
651 WHERE username = c_schema_name;
652
653 l_starttime DATE;
654 l_inittime DATE;
655
656 l_user NUMBER;
657 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
658 l_purge_type sy_purg_def.purge_type%TYPE;
659 l_tablespace user_tablespaces.tablespace_name%TYPE;
660
661 l_tablenames_tab GMA_PURGE_DDL.g_tablename_tab_type;
665 l_totdeleterows INTEGER;
662 l_tableactions_tab GMA_PURGE_DDL.g_tableaction_tab_type;
663
664 l_totarchiverows INTEGER;
666
667 l_elapsed NUMBER;
668
669 l_badstatement EXCEPTION;
670 glposted_badstatement EXCEPTION;
671 get_all_ids long;
672 gl_posted_flag varchar2(10);
673 pa_initiate_time varchar2(50);
674
675 l_temptable varchar2(2000);
676
677
678 BEGIN
679
680 -- fnd_file.put_line(FND_FILE.LOG,NVL(SUBSTR(p_appl_short_name,1,80),' '));
681
682 -- Let the rubes know what's shakin'...
683 GMA_PURGE_UTILITIES.printline(p_purge_id);
684
685 pa_initiate_time:=GMA_PURGE_UTILITIES.chartime;
686
687 GMA_PURGE_UTILITIES.printlong(p_purge_id,
688 -- 'PA initializing for process id ' ||
689 P_job_name||' initializing for process id ' ||
690 p_purge_id ||
691 ' - '||pa_initiate_time);
692 -- GMA_PURGE_UTILITIES.chartime);
693 pa_initiate_time:=to_char(sysdate,'DD-MM-YYYY')||' '||pa_initiate_time;
694
695 -- get process id, sql statement, user name
696 OPEN l_arccursor_cur(p_purge_id);
697 FETCH l_arccursor_cur INTO l_purge_type,l_sqlstatement,l_tablespace,l_status,l_testarcstatus,
698 l_debug_flag_f,l_disable_constraints_f,
699 l_sizing_f,l_commitfrequency,l_owner;
700
701 if l_debug_flag_f='Y' then
702 l_debug_flag_f:='T';
703 elsif l_debug_flag_f='N' then
704 l_debug_flag_f:='F';
705 end if;
706
707 if l_disable_constraints_f='Y' then
708 l_disable_constraints_f:='T';
709 elsif l_disable_constraints_f='N' then
710 l_disable_constraints_f:='F';
711 end if;
712
713 if l_sizing_f='Y' then
714 l_sizing_f:='T';
715 elsif l_sizing_f='N' then
716 l_sizing_f:='F';
717 end if;
718
719 CLOSE l_arccursor_cur;
720
721 -- get lost if purge ID isn't good
722 IF NVL(l_purge_type,'<><>') = '<><>' THEN
723 GMA_PURGE_UTILITIES.printlong(p_purge_id,
724 'Purge id ' || p_purge_id || ' does not exist.');
725 GMA_PURGE_ENGINE.report_exit(p_purge_id,l_status);
726 return;
727 END IF;
728
729 -- get lost if purge type isn't good
730 IF NVL(l_sqlstatement,'<><>') = '<><>' THEN
731 GMA_PURGE_UTILITIES.printlong(p_purge_id,
732 'Purge type ' || l_purge_type || ' does not exist.');
733 GMA_PURGE_ENGINE.report_exit(p_purge_id,l_status);
734 return;
735 END IF;
736 GMA_PURGE_UTILITIES.printlong(p_purge_id,
737 'Purge type is ' ||
738 l_purge_type || '.');
739
740 -- make sure we have a good schema name
741 l_aol_status := FND_INSTALLATION.get_app_info(p_appl_short_name,
742 l_app_status,
743 l_app_industry,
744 l_owner);
745 IF (l_aol_status = false) THEN
746 GMA_PURGE_UTILITIES.printlong(p_purge_id,
747 'Purge owner ' || l_owner || ' can''''t be determined. (FND_INSTALLATION.get_app_info');
748 GMA_PURGE_ENGINE.report_exit(p_purge_id,l_status);
749 RETURN;
750 END IF;
751 BEGIN
752 OPEN l_schema_cursor(l_owner);
753 FETCH l_schema_cursor INTO l_owner_verify;
754 CLOSE l_schema_cursor;
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757 GMA_PURGE_UTILITIES.printlong(p_purge_id,
758 'Purge owner ' || l_owner || ' can''''t be determined. (select schemaname)');
759 GMA_PURGE_ENGINE.report_exit(p_purge_id,l_status);
760 RETURN;
761 END;
762
763 -- get user ID
764 l_user := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
765
766 PA_OPTION:=P_JOB_RUN;
767 PA_OPTION_NAME:=P_JOB_NAME;
768 if P_JOB_RUN in (1,2) then
769 l_status:=l_status;
770 elsif P_JOB_RUN in(3,4,5) then
771 l_status:=l_testarcstatus;
772 end if;
773
774
775 -- status checking, updating here
776 IF (l_status <> 0 AND l_status <> 2) THEN
777 IF (l_status = 1 OR l_status = 3) THEN
778 GMA_PURGE_UTILITIES.printlong(p_purge_id,
779 'Purge ID in process with status - '
780 || to_char(l_status) ||
781 ' - exiting.');
782 ELSIF (l_status = 4) THEN
783 GMA_PURGE_UTILITIES.printlong(p_purge_id,
784 'This purge is complete (status 4) - exiting.');
785 ELSIF (l_status < 0) THEN
786 if P_JOB_RUN in(3,4,5) then
787 GMA_PURGE_UTILITIES.printlong(p_purge_id,
788 'Cleaning Error purge status - ' || to_char(l_status)||'.');
789 ResetTestPurge(p_purge_id,
790 l_purge_type,
791 l_debug_flag_f);
792
793 --l_status:=l_testarcstatus;
794 l_status:=0;
795 else
796 GMA_PURGE_UTILITIES.printlong(p_purge_id,
797 'Error purge status - ' || to_char(l_status) ||
798 ' - exiting.');
799 end if;
800 ELSE
801 GMA_PURGE_UTILITIES.printlong(p_purge_id,
805
802 'Unknown purge status - ' || to_char(l_status) ||
803 ' - exiting.');
804 END IF;
806 IF l_status<>0 THEN --added by Khaja
807 GMA_PURGE_ENGINE.report_exit(p_purge_id,l_status);
808 RETURN;
809 END IF;
810
811 END IF;
812
813 IF (l_status = 0) THEN
814 l_status := 1;
815 ELSIF (l_status = 2) THEN
816 if PA_OPTION in(2,5) then
817 l_status := 3;
818 end if;
819 END IF;
820
821 if P_JOB_RUN in (1,2) then
822
823 UPDATE sy_purg_mst
824 SET purge_status = l_status
825 , last_update_date = sysdate
826 , last_updated_by = l_user
827 , archive_start_time = decode(l_status,1,sysdate,archive_start_time)
828 , purge_start_time = decode(l_status,3,sysdate,purge_start_time)
829 WHERE purge_id = p_purge_id;
830 -- Bug #2599273 (JKB) Changed statuses above to match IF above that.
831
832 COMMIT;
833
834 elsif P_JOB_RUN in (3,4,5) then
835 -- added by KH for test type
836 UPDATE sy_purg_mst
837 SET status = l_status
838 , last_update_date = sysdate
839 , last_updated_by = l_user
840 , archive_start_time = decode(l_status,1,sysdate,archive_start_time)
841 , purge_start_time = decode(l_status,3,sysdate,purge_start_time)
842 WHERE purge_id = p_purge_id;
843 commit;
844 end if;
845
846
847 -- grab a few things before we get started...
848 l_starttime := SYSDATE;
849
850 -- check the flags
851 IF (l_debug_flag_f = 'T') THEN
852 l_debug_flag := TRUE;
853 GMA_PURGE_UTILITIES.printlong(p_purge_id,
854 'Debugging is on.');
855 ELSE
856 l_debug_flag := FALSE;
857 GMA_PURGE_UTILITIES.printlong(p_purge_id,
858 'Debugging is off.');
859 END IF;
860
861 -- are we going to size the tables before copying?
862 IF (l_sizing_f = 'T') THEN
863 l_sizing := TRUE;
864 GMA_PURGE_UTILITIES.printlong(p_purge_id,
865 'Table sizing is on.');
866 ELSE
867 l_sizing := FALSE;
868 GMA_PURGE_UTILITIES.printlong(p_purge_id,
869 'Table sizing is off.');
870 END IF;
871
872 -- are we going to disable constraints if deleting?
873 IF (l_disable_constraints_f = 'T') THEN
874 l_disable_constraints := TRUE;
875 GMA_PURGE_UTILITIES.printlong(p_purge_id,
876 'Constraint disabling is on.');
877 ELSE
878 l_disable_constraints := FALSE;
879 GMA_PURGE_UTILITIES.printlong(p_purge_id,
880 'Constraint disabling is off.');
881 END IF;
882
883 -- Tell 'em the news.
884 GMA_PURGE_UTILITIES.printlong(p_purge_id,
885 'Commit Frequency is set to ' ||
886 to_char(l_commitfrequency) ||
887 '.');
888 GMA_PURGE_UTILITIES.printlong(p_purge_id,
889 'Object owner is ' ||
890 l_owner || '.');
891
892 IF (l_status = 1) THEN
893
894
895 -- get purge criteria
896 FOR l_crit_row IN l_critcursor_cur(p_purge_id) LOOP
897 l_sqlstatement := replace(l_sqlstatement,
898 '<' || l_crit_row.crit_tag || '>',
899 l_crit_row.value);
900 END LOOP;
901
902 -- check the GL_POSTED_IND for OPSO
903 if upper(l_purge_type) in('OPSO','AOPS') then
904 get_all_ids:=GLPOSTED_OPSO(p_purge_id,
905 l_purge_type,
906 l_owner,
907 l_debug_flag);
908 gl_posted_flag:=substr(get_all_ids,1,1);
909 get_all_ids:=substr(get_all_ids,3);
910
911 if gl_posted_flag='F' then
912 RAISE glposted_badstatement;
913 else
914 --l_sqlstatement := replace(l_sqlstatement,'<GLPOSTED>',get_all_ids);
915 l_sqlstatement := replace(l_sqlstatement,'<TEMPTABLE>',get_all_ids);
916
917 end if;
918 end if;
919
920 -- check the GL_POSTED_IND for JRNL
921 if upper(l_purge_type) in ('JRNL','AJNL') then
922 get_all_ids:=GLPOSTED_JRNL(p_purge_id,
923 l_purge_type,
924 l_owner,
925 l_debug_flag);
926 gl_posted_flag:=substr(get_all_ids,1,1);
927 get_all_ids:=substr(get_all_ids,3);
928
929 if gl_posted_flag='F' then
930 RAISE glposted_badstatement;
931 else
932 -- l_sqlstatement := replace(l_sqlstatement,'<GLPOSTED>',get_all_ids);
933 l_sqlstatement := replace(l_sqlstatement,'<TEMPTABLE>',get_all_ids);
934
935 end if;
936 end if;
937
938 -- check the GL_POSTED_IND for PROD added by KH
939 if upper(l_purge_type) in('PROD','APRD') then
940
941 -- Get Posted flag and temp table name from GLPOSTED_PROD
942 get_all_ids:=GMA_PURGE_ENGINE.glposted_prod(p_purge_id,
943 l_purge_type,
944 l_owner,
945 l_debug_flag);
946 -- Take the Posted flag
947 gl_posted_flag:=substr(get_all_ids,1,1);
948 -- Take the Temp table name
952 get_all_ids:=substr(get_all_ids,3);
949 l_temptable:=substr(get_all_ids,3);
950
951 -- Prepare the temptable with owner
953
954 if gl_posted_flag='F' then
955 RAISE glposted_badstatement;
956 else
957 -- Replace the TEMPTABLE tag in main SQL for PROD and APRD
958 l_sqlstatement := replace(l_sqlstatement,'<TEMPTABLE>',get_all_ids);
959
960 end if;
961 end if;
962
963
964 -- check the GL_POSTED_IND for PORD
965 if upper(l_purge_type) in ('PORD','APOR') then
966 get_all_ids:=GLPOSTED_PORD(p_purge_id,
967 l_purge_type,
968 l_owner,
969 l_debug_flag);
970 gl_posted_flag:=substr(get_all_ids,1,1);
971 get_all_ids:=substr(get_all_ids,3);
972
973 if gl_posted_flag='F' then
974 RAISE glposted_badstatement;
975 else
976 --l_sqlstatement := replace(l_sqlstatement,'<GLPOSTED>',get_all_ids);
977 l_sqlstatement := replace(l_sqlstatement,'<TEMPTABLE>',get_all_ids);
978
979 end if;
980 end if;
981
982 -- do it up.
983 GMA_PURGE_ENGINE.archive(p_purge_id,
984 l_purge_type,
985 l_owner,
986 p_appl_short_name,
987 l_user,
988 l_sqlstatement,
989 'ARCHIVEROWS',
990 l_tablespace,
991 l_tablenames_tab,
992 l_tableactions_tab,
993 l_totarchiverows,
994 l_totdeleterows,
995 l_sizing,
996 l_commitfrequency,
997 l_inittime,
998 l_starttime,
999 l_disable_constraints,
1000 l_debug_flag);
1001
1002 l_elapsed := trunc(((SYSDATE - l_starttime) * 86400),2);
1003
1004 -- bug 3216740 ARCHIVE AND PURGE (DIVISOR IS EQUAL TO ZERO FIX (khaja)
1005 if l_elapsed<=0 then
1006 l_elapsed:=1;
1007 end if;
1008
1009 if P_JOB_RUN in (1,2) then
1010
1011 UPDATE sy_purg_mst
1012 -- SET rows_archived = decode(l_status,1,l_totarchiverows, rows_archived)
1013 SET rows_deleted = decode(l_status,1,l_totdeleterows,
1014 rows_deleted)
1015 , archive_elapsed_time =
1016 decode(l_status,1,l_elapsed,archive_elapsed_time)
1017 , rows_per_second = trunc((l_totarchiverows/
1018 decode(l_elapsed,
1019 0,1,
1020 l_elapsed))
1021 ,2)
1022 , copy_rows_per_second = trunc((l_totarchiverows/
1023 decode(copy_elapsed_time,
1024 0,1,
1025 copy_elapsed_time))
1026 ,2)
1027 , last_update_date = sysdate
1028 , last_updated_by = l_user
1029 WHERE purge_id = p_purge_id;
1030 elsif P_JOB_RUN in (3,4,5) then
1031 UPDATE sy_purg_mst
1032 SET rows_archived = decode(l_status,1,l_totarchiverows,
1033 rows_archived)
1034 -- , rows_deleted = decode(l_status,1,l_totdeleterows,
1035 -- rows_deleted)
1036 , archive_elapsed_time =
1037 decode(l_status,1,l_elapsed,archive_elapsed_time)
1038 , rows_per_second = trunc((l_totarchiverows/
1039 decode(l_elapsed,
1040 0,1,
1041 l_elapsed))
1042 ,2)
1043 , copy_rows_per_second = trunc((l_totarchiverows/
1044 decode(copy_elapsed_time,
1045 0,1,
1046 copy_elapsed_time))
1047 ,2)
1048 , last_update_date = sysdate
1049 , last_updated_by = l_user
1050 WHERE purge_id = p_purge_id;
1051 end if;
1052
1053
1054 ELSE
1055 if PA_OPTION in(2,5) then
1056 GMA_PURGE_ENGINE.purge(p_purge_id,l_purge_type,l_owner,p_appl_short_name,l_debug_flag);
1057 end if;
1058
1059 l_elapsed := trunc(((SYSDATE - l_starttime) * 86400),2);
1060
1061 -- bug 3216740 ARCHIVE AND PURGE (DIVISOR IS EQUAL TO ZERO FIX (khaja)
1062 if l_elapsed<=0 then
1063 l_elapsed:=1;
1064 end if;
1065
1066
1067 UPDATE sy_purg_mst
1068 SET purge_elapsed_time =
1069 decode(l_status,3,l_elapsed,purge_elapsed_time)
1070 , last_update_date = sysdate
1071 , last_updated_by = l_user
1072 WHERE purge_id = p_purge_id;
1073
1074 END IF;
1075
1076 COMMIT;
1077
1078 -- status checking, updating here
1079 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1080 -- 'PA completed in ' || TO_CHAR(l_elapsed)
1081 P_JOB_NAME||' completed in ' || TO_CHAR(l_elapsed)
1082 || ' seconds - '||
1083 GMA_PURGE_UTILITIES.chartime);
1087 IF (l_status = 3) THEN
1084 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1085 'R/S ' || TO_CHAR(trunc((l_totarchiverows/l_elapsed),2)));
1086
1088 l_status := 4;
1089 ELSIF (l_status = 1) THEN
1090 l_status := 2;
1091 END IF;
1092
1093 if P_JOB_RUN in (1,2) then
1094
1095 -- Added by Khaja to place the actual Archive time
1096 UPDATE sy_purg_mst
1097 SET purge_status = l_status
1098 , archive_start_time=to_date(pa_initiate_time,'DD-MM-YYYY HH24:MI:SS')
1099 , last_update_date = sysdate
1100 , last_updated_by = l_user
1101 WHERE purge_id = p_purge_id;
1102
1103 COMMIT;
1104 elsif P_JOB_RUN in(3,4,5) then
1105 UPDATE sy_purg_mst
1106 SET status = l_status
1107 , archive_start_time=to_date(pa_initiate_time,'DD-MM-YYYY HH24:MI:SS')
1108 , last_update_date = sysdate
1109 , last_updated_by = l_user
1110 WHERE purge_id = p_purge_id;
1111 commit;
1112 end if;
1113
1114 GMA_PURGE_ENGINE.report_exit(p_purge_id,l_status);
1115
1116 -- DBMS_SQL.CLOSE_CURSOR(NULL);
1117 RETURN; -- Exit program.
1118
1119 EXCEPTION
1120
1121 WHEN l_badstatement THEN
1122 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1123 'There is a problem with the purge definition.');
1124 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1125 l_sqlstatement);
1126 GMA_PURGE_ENGINE.report_exit(p_purge_id,l_status);
1127 WHEN glposted_badstatement THEN
1128 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1129 'Not Purging, No posted Rows found.');
1130 if P_JOB_RUN in (1,2) then
1131
1132 UPDATE sy_purg_mst
1133 SET purge_status = 0
1134 , last_update_date = sysdate
1135 , last_updated_by = l_user
1136 , archive_start_time = decode(l_status,1,sysdate,archive_start_time)
1137 , purge_start_time = decode(l_status,3,sysdate,purge_start_time)
1138 WHERE purge_id = p_purge_id;
1139
1140 COMMIT;
1141
1142 elsif P_JOB_RUN in (3,4,5) then
1143 -- added by KH for test type
1144 UPDATE sy_purg_mst
1145 SET status = 0
1146 , last_update_date = sysdate
1147 , last_updated_by = l_user
1148 , archive_start_time = decode(l_status,1,sysdate,archive_start_time)
1149 , purge_start_time = decode(l_status,3,sysdate,purge_start_time)
1150 WHERE purge_id = p_purge_id;
1151 commit;
1152 end if;
1153 l_status:=0;
1154
1155 GMA_PURGE_ENGINE.report_exit(p_purge_id,l_status);
1156 WHEN OTHERS THEN
1157 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1158 'Problem raised in GMA_PURGE_ENGINE.main.');
1159 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1160 '## ' || sqlerrm);
1161 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1162 'Exiting.');
1163 GMA_PURGE_ENGINE.report_exit(p_purge_id,l_status);
1164
1165 l_orastatus := sqlcode;
1166
1167 if P_JOB_RUN in (1,2) then
1168 UPDATE sy_purg_mst SY
1169 SET SY.purge_status = (SY.purge_status - (SY.purge_status * 2))
1170 , SY.ora_status = l_orastatus
1171 WHERE SY.purge_id = p_purge_id;
1172 elsif P_JOB_RUN in(3,4,5) then
1173 UPDATE sy_purg_mst SY
1174 SET SY.status = (SY.status - (SY.status * 2))
1175 , SY.ora_status = l_orastatus
1176 WHERE SY.purge_id = p_purge_id;
1177 end if;
1178
1179 COMMIT;
1180
1181 -- DBMS_SQL.CLOSE_CURSOR(NULL);
1182
1183 END main;
1184
1185 /***********************************************************/
1186
1187 PROCEDURE getrows(p_purge_id sy_purg_mst.purge_id%TYPE,
1188 p_owner user_users.username%TYPE,
1189 p_appl_short_name fnd_application.application_short_name%TYPE,
1190 p_sqlstatement sy_purg_def.sqlstatement%TYPE,
1191 p_tablespace user_tablespaces.tablespace_name%TYPE,
1192 p_arcrowtable user_tables.table_name%TYPE,
1193 p_debug_flag BOOLEAN) IS
1194
1195 -- create master rows table for archive
1196
1197 l_result INTEGER;
1198 l_rows INTEGER;
1199 l_cursor INTEGER;
1200 l_badstatement EXCEPTION;
1201 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
1202
1203 BEGIN
1204
1205 l_cursor := DBMS_SQL.OPEN_CURSOR;
1206
1207 l_sqlstatement := 'CREATE TABLE ' || p_owner || '.' ||
1208 p_arcrowtable || ' TABLESPACE ' ||
1209 p_tablespace || ' nologging AS ' ||
1210 p_sqlstatement;
1211
1212 IF (p_debug_flag = TRUE) THEN
1213 GMA_PURGE_UTILITIES.printlong(p_purge_id,l_sqlstatement);
1214 END IF;
1215 -- MADE BY KHAJA
1216 -- IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
1217 -- AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.CREATE_TABLE,
1218 -- l_sqlstatement,p_arcrowtable);
1219 -- ELSE
1220 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
1221 l_result := DBMS_SQL.EXECUTE(l_cursor);
1222 -- END IF;
1223
1224 IF l_result <> 0 THEN
1225 RAISE l_badstatement;
1226 END IF;
1227
1228 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1229
1230 RETURN;
1231
1232 EXCEPTION
1233
1234 WHEN OTHERS THEN
1235 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1239 RAISE;
1236 'Problem raised in GMA_PURGE_ENGINE.getrows.');
1237 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1238 'Unhandled EXCEPTION - ' || sqlerrm);
1240
1241 END getrows;
1242
1243 /***********************************************************/
1244
1245 PROCEDURE logresults(p_purge_id sy_purg_mst.purge_id%TYPE,
1246 p_user NUMBER,
1247 p_arctables_tab GMA_PURGE_DDL.g_tablename_tab_type,
1248 p_arcactions_tab GMA_PURGE_DDL.g_tableaction_tab_type,
1249 p_tablecount INTEGER,
1250 p_totarchiverows IN OUT NOCOPY INTEGER,
1251 p_totdeleterows IN OUT NOCOPY INTEGER) IS
1252 -- distill results into log format
1253
1254 l_result INTEGER;
1255 l_archiverows INTEGER; -- number of rows archived from this table
1256 l_deleterows INTEGER; -- number of rows deleted from this table
1257 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
1258 l_cursor INTEGER;
1259 l_tableno INTEGER;
1260
1261 BEGIN
1262
1263 -- init some values
1264 l_archiverows := 0;
1265 l_deleterows := 0;
1266 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1267 '');
1268 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1269 ' ' || rpad('Table Name',32) || ' ' ||
1270 lpad('Total Rows',10) ||' ');
1271 -- lpad('Archived',10) || ' ' || moved to next
1272 -- lpad('Deleted',10)); --commented
1273 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1274 '');
1275
1276 l_cursor := DBMS_SQL.OPEN_CURSOR;
1277
1278 FOR l_tableno IN 0 .. p_tablecount LOOP
1279
1280
1281 -- Created a FUNCTION for GSCC standard fix bug 3871659
1282 -- Standard: File.Sql.6 - Do NOT include any references to hardcoded schema
1283
1284 -- l_sqlstatement := 'SELECT COUNT(*) FROM '
1285 l_sqlstatement := 'SELECT COUNT(*) FROM ' ||Get_GmaSchemaName||'.'||
1286 GMA_PURGE_UTILITIES.makearcname(p_purge_id,
1287 p_arctables_tab(l_tableno));
1288
1289 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
1290 DBMS_SQL.DEFINE_COLUMN(l_cursor,1,l_archiverows);
1291 l_result := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor);
1292 DBMS_SQL.COLUMN_VALUE(l_cursor,1,l_archiverows);
1293
1294 -- did we delete any rows?
1295 IF p_arcactions_tab(l_tableno) = 'D' THEN
1296 l_deleterows := l_archiverows;
1297 ELSE
1298 l_deleterows := 0;
1299 END IF;
1300
1301 if PA_OPTION<>3 then
1302
1303 INSERT INTO sy_purg_log
1304 ( purge_id
1305 , table_name
1306 , rows_archived
1307 , rows_deleted
1308 , creation_date
1309 , created_by
1310 , last_update_login
1311 , last_update_date
1312 , last_updated_by)
1313 VALUES
1314 ( p_purge_id
1315 , p_arctables_tab(l_tableno)
1316 , l_archiverows
1317 , l_deleterows
1318 , sysdate
1319 , p_user
1320 ,1
1321 ,sysdate
1322 ,1);
1323 end if;
1324
1325 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1326 ' ' || rpad(p_arctables_tab(l_tableno),32) || ' ' ||
1327 lpad(to_char(l_archiverows),10) || ' ');
1328 -- lpad(to_char(l_deleterows),10)); -- commented
1329
1330 p_totarchiverows := p_totarchiverows + l_archiverows;
1331 p_totdeleterows := p_totdeleterows + l_deleterows;
1332
1333 END LOOP;
1334
1335 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1336
1337 EXCEPTION
1338
1339 WHEN OTHERS THEN
1340 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1341 'Problem raised in GMA_PURGE_ENGINE.logresults.');
1342 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1343 'Unhandled EXCEPTION - ' || sqlerrm);
1344 RAISE;
1345
1346 END logresults;
1347
1348 /***********************************************************/
1349
1350 PROCEDURE archive(p_purge_id sy_purg_mst.purge_id%TYPE,
1351 p_purge_type sy_purg_def.purge_type%TYPE,
1352 p_owner user_users.username%TYPE,
1353 p_appl_short_name fnd_application.application_short_name%TYPE,
1354 p_user NUMBER,
1355 p_sqlstatement sy_purg_def.sqlstatement%TYPE,
1356 p_arcrowbasename user_tables.table_name%TYPE,
1357 p_arctablespace user_tablespaces.tablespace_name%TYPE,
1358 p_arctables_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tablename_tab_type,
1359 p_arcactions_tab IN OUT NOCOPY GMA_PURGE_DDL.g_tableaction_tab_type,
1360 p_totarchiverows IN OUT NOCOPY INTEGER,
1361 p_totdeleterows IN OUT NOCOPY INTEGER,
1362 p_sizing BOOLEAN,
1363 p_commitfrequency INTEGER,
1364 p_inittime IN OUT NOCOPY DATE,
1365 p_starttime IN OUT NOCOPY DATE,
1366 p_disable_constraints BOOLEAN,
1367 p_debug_flag BOOLEAN) IS
1368
1369 l_arcrowtable user_tables.table_name%TYPE;
1370 l_tablecount INTEGER;
1371
1372 BEGIN
1373
1374 p_totarchiverows := 0;
1375 p_totdeleterows := 0;
1376
1380
1377 -- Figure out important process table names
1378 l_arcrowtable := GMA_PURGE_UTILITIES.makearcname(p_purge_id,
1379 p_arcrowbasename);
1381 -- get archive set, do actual archive
1382 GMA_PURGE_ENGINE.getrows(p_purge_id,
1383 p_owner,
1384 p_appl_short_name,
1385 p_sqlstatement,
1386 p_arctablespace,
1387 l_arcrowtable,
1388 p_debug_flag);
1389
1390 -- get incremental stats
1391 p_inittime := SYSDATE;
1392 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1393 GMA_PURGE_ENGINE.PA_OPTION_NAME||' rows determined in ' ||
1394 TO_CHAR(trunc((p_inittime - p_starttime) * 86400)) || ' seconds.');
1395
1396 UPDATE sy_purg_mst
1397 SET selection_elapsed_time =
1398 trunc((p_inittime - p_starttime) * 86400)
1399 , last_update_date = sysdate
1400 , last_updated_by = p_user
1401 WHERE purge_id = p_purge_id;
1402
1403 COMMIT;
1404
1405 GMA_PURGE_ENGINE.doarchive(p_purge_id,
1406 p_purge_type,
1407 p_owner,
1408 p_appl_short_name,
1409 p_user,
1410 GMA_PURGE_UTILITIES.makearcname(p_purge_id,
1411 p_arcrowbasename),
1412 p_arctables_tab,
1413 p_arcactions_tab,
1414 l_tablecount,
1415 p_totarchiverows,
1416 p_totdeleterows,
1417 p_sizing,
1418 p_commitfrequency,
1419 p_disable_constraints,
1420 p_debug_flag);
1421
1422 -- get incremental stats
1423 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1424 -- 'PA selection/archive completed in ' ||
1425 GMA_PURGE_ENGINE.PA_OPTION_NAME||' selection completed in ' ||
1426 to_char(trunc((SYSDATE - p_inittime) * 86400))
1427 || ' seconds.');
1428
1429 -- create log entries here
1430 GMA_PURGE_ENGINE.logresults(p_purge_id,
1431 p_user,
1432 p_arctables_tab,
1433 p_arcactions_tab,
1434 l_tablecount,
1435 p_totarchiverows,
1436 p_totdeleterows);
1437
1438 -- drop archive row table, archive journal tables
1439 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1440 '');
1441 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1442 ' ' || rpad('TOTAL',32) || ' ' ||
1443 lpad(to_char(p_totarchiverows),10) || ' ');
1444 -- lpad(to_char(p_totdeleterows),10)); commented
1445 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1446 '');
1447 GMA_PURGE_DDL.createarcviews(p_purge_id,p_purge_type,p_owner,p_appl_short_name,p_debug_flag);
1448
1449 GMA_PURGE_DDL.droparctable(p_purge_id,p_owner,p_appl_short_name,'ARCHIVEROWS');
1450
1451 -- Drops the temporary table for GME only. KH
1452 IF upper(p_purge_type) in('PROD','APRD','AJNL','JRNL','OPSO','AOPS','PORD','APOR') Then
1453 GMA_PURGE_ENGINE.Tempdrop(p_purge_id,p_purge_type,p_debug_flag);
1454 END IF;
1455
1456
1457 EXCEPTION
1458
1459 WHEN OTHERS THEN
1460 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1461 'Problem raised in GMA_PURGE_ENGINE.archive.');
1462 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1463 'Unhandled EXCEPTION - ' || sqlerrm);
1464 RAISE;
1465
1466 END archive;
1467
1468 /***********************************************************/
1469
1470 PROCEDURE purge(p_purge_id sy_purg_mst.purge_id%TYPE,
1471 p_purge_type sy_purg_def.purge_type%TYPE,
1472 p_owner user_users.username%TYPE,
1473 p_appl_short_name fnd_application.application_short_name%TYPE,
1474 p_debug_flag BOOLEAN) IS
1475 -- drop archive tables, reset views
1476
1477 CURSOR l_viewtables_cur(c_purge_type sy_purg_mst.purge_type%TYPE) IS
1478 SELECT table_name
1479 FROM sy_purg_def_act
1480 WHERE purge_type = c_purge_type;
1481
1482 BEGIN
1483
1484 FOR l_viewtable_row IN l_viewtables_cur(p_purge_type) LOOP
1485 GMA_PURGE_DDL.droparctable(p_purge_id,p_owner,p_appl_short_name,l_viewtable_row.table_name);
1486 END LOOP;
1487
1488 GMA_PURGE_DDL.createarcviews(p_purge_id,p_purge_type,p_owner,p_appl_short_name,p_debug_flag);
1489
1490 EXCEPTION
1491
1492 WHEN OTHERS THEN
1493 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1494 'Problem raised in GMA_PURGE_ENGINE.purge.');
1495 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1496 'Unhandled EXCEPTION - ' || sqlerrm);
1497 RAISE;
1498
1499 END purge;
1500
1501 /***********************************************************/
1502
1503 PROCEDURE report_exit (p_purge_id sy_purg_mst.purge_id%TYPE,
1504 p_status INTEGER) IS
1505 BEGIN
1506
1507 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1508 'Ending status is ' || to_char(p_status) || '.');
1509 GMA_PURGE_UTILITIES.printline(p_purge_id);
1510
1514 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1511 EXCEPTION
1512
1513 WHEN OTHERS THEN
1515 'Problem raised in GMA_PURGE_ENGINE. report_exit.');
1516 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1517 'Unhandled EXCEPTION - ' || sqlerrm);
1518 RAISE;
1519
1520 END report_exit;
1521
1522 FUNCTION GLPOSTED_OPSO(P_Purge_id in sy_purg_mst.purge_id%TYPE,
1523 p_purge_type sy_purg_def.purge_type%TYPE,
1524 p_owner user_users.username%TYPE,
1525 p_debug_flag BOOLEAN)
1526 RETURN LONG
1527 is
1528 cursor purge_crit is
1529 select crit_tag,crit_value
1530 from sy_purg_mst_crit
1531 where purge_id=P_Purge_id;
1532
1533 cursor c1(P_MINORDER op_ordr_hdr.order_no%type,
1534 P_MAXORDER op_ordr_hdr.order_no%type,
1535 P_MINORGN op_ordr_hdr.orgn_code%type,
1536 P_MAXORGN op_ordr_hdr.orgn_code%type,
1537 P_MINMDATE op_ordr_hdr.last_update_date%type,
1538 P_MAXMDATE op_ordr_hdr.last_update_date%type,
1539 P_MINCDATE op_ordr_hdr.creation_date%type,
1540 P_MAXCDATE op_ordr_hdr.creation_date%type,
1541 P_OSTATUS op_ordr_hdr.order_status%type)
1542 is
1543 select distinct order_id
1544 FROM op_ordr_hdr OH1
1545 WHERE order_no >= P_MINORDER
1546 AND order_no <= P_MAXORDER
1547 AND orgn_code >= P_MINORGN
1548 AND orgn_code <= P_MAXORGN
1549 AND last_update_date >= P_MINMDATE
1550 AND last_update_date <= P_MAXMDATE
1551 AND creation_date >= P_MINCDATE
1552 AND creation_date <= P_MAXCDATE
1553 AND order_status = P_OSTATUS
1554 AND (order_status = -1 or order_status = 25) ;
1555
1556 -- COMPLETED_IND Completed indicator. 0=Pending transaction, 1=Completed transaction.
1557 -- GL_POSTED_IND GL posted indicator. 0=Not posted to GL, 1=Posted to GL.
1558 -- DELETE_MARK Standard: 0=Active record (default); 1=Marked for (logical) deletion.
1559 -- TRANS_ID (PK) Unique key for the transaction.
1560
1561 --(in 1,2) and TRANS_ID not in (SELECT TRANS_ID from ic_tran_pnd where doc_id=pdoc_id and doc_type='OPSO' and delete_mark=1)
1562
1563 cursor c2 (pdoc_id ic_tran_pnd.doc_id%type)
1564 IS
1565 select count(*) COUNT_GL_POSTED_IND, 0 COUNT_COMPLETED_IND
1566 from ic_tran_pnd
1567 where doc_id =pdoc_id
1568 and doc_type = 'OPSO'
1569 and delete_mark=0
1570 and gl_posted_ind <>1
1571 UNION ALL
1572 select 0,count(*)
1573 from ic_tran_pnd
1574 where doc_id = pdoc_id
1575 and doc_type = 'OPSO'
1576 and delete_mark=0
1577 and completed_ind =0;
1578
1579 MINORDER op_ordr_hdr.order_no%type;
1580 MAXORDER op_ordr_hdr.order_no%type;
1581 MINORGN op_ordr_hdr.orgn_code%type;
1582 MAXORGN op_ordr_hdr.orgn_code%type;
1583 MINMDATE op_ordr_hdr.last_update_date%type;
1584 MAXMDATE op_ordr_hdr.last_update_date%type;
1585 MINCDATE op_ordr_hdr.creation_date%type;
1586 MAXCDATE op_ordr_hdr.creation_date%type;
1587 OSTATUS op_ordr_hdr.order_status%type;
1588
1589 no_of_unposted_rec number(10):=0;
1590 posted varchar2(10):='F';
1591
1592 TYPE id_tab_type IS TABLE OF number INDEX BY BINARY_INTEGER;
1593 order_id id_tab_type;
1594 all_order_id varchar2(30000):=-9999;
1595
1596 l_temptable Varchar2(2000);
1597 l_TempFlag varchar2(1):='T';
1598
1599 Begin
1600
1601 FOR pcritRec in purge_crit LOOP
1602 if (pcritRec.crit_tag='MINORDER') then
1603 MINORDER:=pcritRec.crit_value;
1604 elsif (pcritRec.crit_tag='MAXORDER') then
1605 MAXORDER:=pcritRec.crit_value;
1606 elsif (pcritRec.crit_tag='MINORGN') then
1607 MINORGN:=pcritRec.crit_value;
1608 elsif (pcritRec.crit_tag='MAXORGN') then
1609 MAXORGN:=pcritRec.crit_value;
1610 elsif (pcritRec.crit_tag='MINMDATE') then
1611 MINMDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1612 elsif (pcritRec.crit_tag='MAXMDATE') then
1613 MAXMDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1614 elsif (pcritRec.crit_tag='MINCDATE') then
1615 MINCDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1616 elsif (pcritRec.crit_tag='MAXCDATE') then
1617 MAXCDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1618 elsif (pcritRec.crit_tag='OSTATUS') then
1619 OSTATUS:=pcritRec.crit_value;
1620 end if;
1621 END LOOP;
1622
1623 FOR c1Rec in c1(MINORDER,MAXORDER,MINORGN,MAXORGN,MINMDATE,MAXMDATE,MINCDATE,MAXCDATE,OSTATUS)
1624 LOOP
1625 for c2Rec in c2(c1Rec.order_id) loop
1626
1627 if c2Rec.COUNT_GL_POSTED_IND>0 then
1628 no_of_unposted_rec:=c2Rec.COUNT_GL_POSTED_IND;
1629 end if;
1630
1631 if c2Rec.COUNT_COMPLETED_IND>0 then
1632 no_of_unposted_rec:=c2Rec.COUNT_COMPLETED_IND;
1633 end if;
1634
1635 end loop;
1636
1637 IF (no_of_unposted_rec > 0) THEN
1638 order_id(c1%rowcount):=c1Rec.order_id;
1639 --all_order_id:=all_order_id||','||order_id(c1%rowcount);
1640 ELSE
1641 posted:='T,';
1642
1643 if l_TempFlag='T' then
1644 -- proceede with Temporary table stuff,Create the Temp table
1645 l_temptable:=GMA_PURGE_ENGINE.Tempcreate(P_purge_id,
1646 p_purge_type,
1647 p_owner,
1648 p_debug_flag);
1649 l_TempFlag:='F';
1650 end if;
1651
1652
1653 --Now start inserting all_ids to Temp table
1654 GMA_PURGE_ENGINE.Tempinsert(P_purge_id,
1655 p_purge_type,
1659 END IF;
1656 c1Rec.order_id,
1657 p_debug_flag);
1658
1660 no_of_unposted_rec := 0;
1661 -- Bug #3872548 (JKB) Added =0 line above.
1662 END LOOP;
1663
1664 -- all_order_id:=posted||all_order_id;
1665 all_order_id:=posted||l_temptable;
1666 return all_order_id;
1667
1668 EXCEPTION WHEN OTHERS THEN
1669 if sqlcode=-1858 then
1670 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1671 'Problem raised in GMA_PURGE_ENGINE.GLPOSTED_OPSO.');
1672 GMA_PURGE_UTILITIES.printlong(p_purge_id,'Wrong data given for Purge and Archive');
1673 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1674 'Unhandled EXCEPTION - ' || sqlerrm);
1675 end if;
1676 END GLPOSTED_OPSO;
1677
1678 FUNCTION GLPOSTED_JRNL(P_Purge_id in sy_purg_mst.purge_id%TYPE,
1679 p_purge_type sy_purg_def.purge_type%TYPE,
1680 p_owner user_users.username%TYPE,
1681 p_debug_flag BOOLEAN)
1682 RETURN LONG
1683 is
1684 cursor purge_crit is
1685 select crit_tag,crit_value
1686 from sy_purg_mst_crit
1687 where purge_id=P_Purge_id;
1688
1689 cursor c1(P_MINJRNL ic_jrnl_mst.journal_no%type,
1690 P_MAXJRNL ic_jrnl_mst.journal_no%type,
1691 P_MINORGN ic_jrnl_mst.orgn_code%type,
1692 P_MAXORGN ic_jrnl_mst.orgn_code%type,
1693 P_MINMDATE ic_jrnl_mst.last_update_date%type,
1694 P_MAXMDATE ic_jrnl_mst.last_update_date%type,
1695 P_MINCDATE ic_jrnl_mst.creation_date%type,
1696 P_MAXCDATE ic_jrnl_mst.creation_date%type)
1697
1698 is
1699 select distinct IA1.doc_id
1700 FROM ic_adjs_jnl IA1,
1701 ic_jrnl_mst IJ1
1702 WHERE IA1.journal_id = IJ1.journal_id
1703 AND IJ1.posted_ind = 1
1704 AND IA1.completed_ind = 1
1705 AND IJ1.journal_no >= P_MINJRNL
1706 AND IJ1.journal_no <= P_MAXJRNL
1707 AND IJ1.orgn_code >= P_MINORGN
1708 AND IJ1.orgn_code <= P_MAXORGN
1709 AND IJ1.last_update_date >= P_MINMDATE
1710 AND IJ1.last_update_date <= P_MAXMDATE
1711 AND IJ1.creation_date >= P_MINCDATE
1712 AND IJ1.creation_date <= P_MAXCDATE;
1713
1714 -- Status and grade Journal type GMI transactions are not posted to the subledger and gl_posted_ind in
1715 -- ic_tran_cmp will never be set to 1. Ignoring the gl_posted_ind validation for these types GRDI GRDR STSI STSR
1716
1717 cursor c2 (pdoc_id ic_tran_cmp.doc_id%type) is
1718 select count(*)
1719 from ic_tran_cmp
1720 where doc_id = pdoc_id
1721 and doc_type in ('CREI','CRER','ADJI','ADJR','TRNI','TRNR')
1722 --excludes ('GRDI','GRDR','STSI','STSR') types per bug 2441842
1723 -- Bug #2602036 (JKB) Removed 'upper' and 'not in' above.
1724 and gl_posted_ind <> 1;
1725
1726 MINJRNL ic_jrnl_mst.journal_no%type;
1727 MAXJRNL ic_jrnl_mst.journal_no%type;
1728 MINORGN ic_jrnl_mst.orgn_code%type;
1729 MAXORGN ic_jrnl_mst.orgn_code%type;
1730 MINMDATE ic_jrnl_mst.last_update_date%type;
1731 MAXMDATE ic_jrnl_mst.last_update_date%type;
1732 MINCDATE ic_jrnl_mst.creation_date%type;
1733 MAXCDATE ic_jrnl_mst.creation_date%type;
1734
1735 no_of_unposted_rec number(10);
1736 posted varchar2(10):='F';
1737
1738 TYPE id_tab_type IS TABLE OF number INDEX BY BINARY_INTEGER;
1739 doc_id id_tab_type;
1740 all_doc_id varchar2(30000):=-9999;
1741
1742 l_temptable Varchar2(2000);
1743 l_TempFlag varchar2(1):='T';
1744
1745 Begin
1746
1747 FOR pcritRec in purge_crit LOOP
1748 if (pcritRec.crit_tag='MINJRNL') then
1749 MINJRNL:=pcritRec.crit_value;
1750 elsif (pcritRec.crit_tag='MAXJRNL') then
1751 MAXJRNL:=pcritRec.crit_value;
1752 elsif (pcritRec.crit_tag='MINORGN') then
1753 MINORGN:=pcritRec.crit_value;
1754 elsif (pcritRec.crit_tag='MAXORGN') then
1755 MAXORGN:=pcritRec.crit_value;
1756 elsif (pcritRec.crit_tag='MINMDATE') then
1757 MINMDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1758 elsif (pcritRec.crit_tag='MAXMDATE') then
1759 MAXMDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1760 elsif (pcritRec.crit_tag='MINCDATE') then
1761 MINCDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1762 elsif (pcritRec.crit_tag='MAXCDATE') then
1763 MAXCDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1764 end if;
1765 END LOOP;
1766
1767
1768 FOR c1Rec in c1(MINJRNL,MAXJRNL,MINORGN,MAXORGN,MINMDATE,MAXMDATE,MINCDATE,MAXCDATE)
1769 LOOP
1770 OPEN c2 (c1Rec.doc_id);
1771 FETCH c2 into no_of_unposted_rec;
1772 CLOSE c2;
1773
1774 IF (no_of_unposted_rec > 0) THEN
1775 doc_id(c1%rowcount):=c1Rec.doc_id;
1776 -- all_doc_id:=all_doc_id||','||doc_id(c1%rowcount);
1777 ELSE
1778 posted:='T,';
1779
1780 if l_TempFlag='T' then
1781 -- proceede with Temporary table stuff,Create the Temp table
1782 l_temptable:=GMA_PURGE_ENGINE.Tempcreate(P_purge_id,
1783 p_purge_type,
1784 p_owner,
1785 p_debug_flag);
1786 l_TempFlag:='F';
1787 end if;
1788
1789
1790 --Now start inserting all_ids to Temp table
1791 GMA_PURGE_ENGINE.Tempinsert(P_purge_id,
1792 p_purge_type,
1793 c1Rec.doc_id,
1794 p_debug_flag);
1795 END IF;
1796 END LOOP;
1797
1798 -- all_doc_id:=posted||all_doc_id;
1802
1799 all_doc_id:=posted||l_temptable;
1800
1801 return all_doc_id;
1803 EXCEPTION WHEN OTHERS THEN
1804 if sqlcode=-1858 then
1805 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1806 'Problem raised in GMA_PURGE_ENGINE.GLPOSTED_JRNL.');
1807 GMA_PURGE_UTILITIES.printlong(p_purge_id,'Wrong data given for Purge and Archive');
1808 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1809 'Unhandled EXCEPTION - ' || sqlerrm);
1810 end if;
1811 END GLPOSTED_JRNL;
1812
1813 FUNCTION GLPOSTED_PROD
1814 (P_Purge_id sy_purg_mst.purge_id%TYPE,
1815 p_purge_type sy_purg_def.purge_type%TYPE,
1816 p_owner user_users.username%TYPE,
1817 p_debug_flag BOOLEAN)
1818 RETURN LONG
1819 IS
1820
1821 --Purge all rows for BATCH_STATUS -1(cancelled) or -3(Converted FPO) because some rows never get posted in db.
1822 --Check GL_POSTED_IND only for BATCH_STATUS 4(Closed) and purge.
1823 --Ignore GL_POSTED_IND for UPDATE_INVENTORY_IND flag is set to off 'Y' and purge.
1824
1825 cursor purge_crit is
1826 select crit_tag,crit_value
1827 from sy_purg_mst_crit
1828 where purge_id=P_Purge_id;
1829
1830 cursor c1(P_MINBATCH gme_batch_header.batch_no%type,
1831 P_MAXBATCH gme_batch_header.batch_no%type,
1832 P_MINPLANT gme_batch_header.plant_code%type,
1833 P_MAXPLANT gme_batch_header.plant_code%type,
1834 P_MINMDATE gme_batch_header.last_update_date%type,
1835 P_MAXMDATE gme_batch_header.last_update_date%type,
1836 P_MINCDATE gme_batch_header.creation_date%type,
1837 P_MAXCDATE gme_batch_header.creation_date%type,
1838 P_PSTATUS gme_batch_header.batch_status%type)
1839 is
1840 select distinct BH2.batch_id
1841 FROM gme_batch_header BH2
1842 WHERE BH2.batch_no >= P_MINBATCH
1843 AND BH2.batch_no <= P_MAXBATCH
1844 AND BH2.plant_code >= P_MINPLANT
1845 AND BH2.plant_code <= P_MAXPLANT
1846 AND BH2.last_update_date >= P_MINMDATE
1847 AND BH2.last_update_date <= P_MAXMDATE
1848 AND BH2.creation_date >= P_MINCDATE
1849 AND BH2.creation_date <= P_MAXCDATE
1850 AND BH2.batch_status = P_PSTATUS
1851 AND (BH2.batch_status = -1 or BH2.batch_status = -3 or BH2.batch_status = 4);
1852
1853 cursor cur_regular_or_phantom(P_batch_id GME_BATCH_HEADER.batch_id%type) is
1854 SELECT batch_id,parentline_id
1855 FROM gme_batch_header
1856 WHERE batch_id=P_batch_id;
1857
1858 cursor cur_phantoms(p_batch_id GME_BATCH_HEADER.batch_id%type) is
1859 select batch_id,batch_status,gl_posted_ind,update_inventory_ind
1860 FROM GME_BATCH_HEADER
1861 WHERE batch_id IN (SELECT DISTINCT batch_id
1862 FROM gme_material_details
1863 START WITH batch_id=P_batch_id
1864 CONNECT BY batch_id = PRIOR phantom_id);
1865 -- AND GL_POSTED_IND<>1
1866 -- AND BATCH_STATUS not in( -1,-3);
1867
1868 MINBATCH gme_batch_header.batch_no%type;
1869 MAXBATCH gme_batch_header.batch_no%type;
1870 MINPLANT gme_batch_header.plant_code%type;
1871 MAXPLANT gme_batch_header.plant_code%type;
1872 MINMDATE gme_batch_header.last_update_date%type;
1873 MAXMDATE gme_batch_header.last_update_date%type;
1874 MINCDATE gme_batch_header.creation_date%type;
1875 MAXCDATE gme_batch_header.creation_date%type;
1876 PSTATUS gme_batch_header.batch_status%type;
1877
1878 tmp_cur_rphantom cur_regular_or_phantom%rowtype;
1879
1880 no_of_unposted_rec number(10);
1881 posted varchar2(10):='F,';
1882
1883 TYPE id_tab_type IS TABLE OF number INDEX BY BINARY_INTEGER;
1884 phantom_batch_id id_tab_type;
1885 all_phantom_batch_id id_tab_type;
1886 all_batch_id id_tab_type;
1887
1888 all_phantoms long:=-9999;
1889
1890 phantom_cnt number:=0;
1891 phantom_unposted_flag BOOLEAN:=FALSE;
1892 icnt number:=0;
1893
1894 l_temptable varchar2(2000);
1895
1896 Begin
1897
1898 FOR pcritRec in purge_crit LOOP
1899 if (pcritRec.crit_tag='MINBATCH') then
1900 MINBATCH:=pcritRec.crit_value;
1901 elsif (pcritRec.crit_tag='MAXBATCH') then
1902 MAXBATCH:=pcritRec.crit_value;
1903 elsif (pcritRec.crit_tag='MINPLANT') then
1904 MINPLANT:=pcritRec.crit_value;
1905 elsif (pcritRec.crit_tag='MAXPLANT') then
1906 MAXPLANT:=pcritRec.crit_value;
1907 elsif (pcritRec.crit_tag='MINMDATE') then
1908 MINMDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1909 elsif (pcritRec.crit_tag='MAXMDATE') then
1910 MAXMDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1911 elsif (pcritRec.crit_tag='MINCDATE') then
1912 MINCDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1913 elsif (pcritRec.crit_tag='MAXCDATE') then
1914 MAXCDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
1915 elsif (pcritRec.crit_tag='PSTATUS') then
1916 PSTATUS:=pcritRec.crit_value;
1917 end if;
1918 END LOOP;
1919
1920 FOR c1Rec in c1(MINBATCH,MAXBATCH,MINPLANT,MAXPLANT,MINMDATE,MAXMDATE,MINCDATE,MAXCDATE,PSTATUS)
1921 LOOP
1922 OPEN cur_regular_or_phantom(c1Rec.batch_id);
1923 FETCH cur_regular_or_phantom INTO tmp_cur_rphantom;
1924 CLOSE cur_regular_or_phantom;
1925
1926 if(tmp_cur_rphantom.parentline_id >0) then
1927 -- its a phantom batch so do not purge this batch id
1928 no_of_unposted_rec:=1;
1929 else
1930 -- its a regular batch and check for all phantom batches ,validate gl_posted_ind
1931 phantom_cnt:=0;
1932 phantom_unposted_flag:=FALSE;
1933
1934 FOR RecPhantom in cur_phantoms(c1Rec.batch_id) LOOP
1935
1939 --Purge all rows for BATCH_STATUS -1(cancelled) or -3(Converted FPO) because some rows never get posted in db.
1936 phantom_cnt:=phantom_cnt+1;
1937 phantom_batch_id(phantom_cnt):=RecPhantom.batch_id;
1938
1940 --Check GL_POSTED_IND only for BATCH_STATUS 4(Closed) and purge.
1941
1942 if RecPhantom.batch_status NOT IN (-1,-3) then
1943 if(RecPhantom.gl_posted_ind<>1 and RecPhantom.update_inventory_ind='Y') then
1944 phantom_unposted_flag:=TRUE;
1945 no_of_unposted_rec:=1;
1946 end if;
1947 end if;
1948 END LOOP;
1949
1950 IF NOT phantom_unposted_flag then
1951 --no_of_unposted_rec:=0;
1952 posted:='T,';
1953
1954 for ci in 1..phantom_cnt loop
1955 icnt:=icnt+1;
1956 all_phantom_batch_id(icnt):=phantom_batch_id(ci);
1957
1958 -- Commented all_phantoms ,no more required KH
1959 -- all_phantoms:=all_phantoms||','||all_phantom_batch_id(icnt);
1960
1961 end loop;
1962 END IF;
1963
1964 end if;
1965
1966
1967 END LOOP;
1968
1969 -- If posted flag is True then proceede with Temporary table stuff
1970 IF substr(posted,1,1)='T' Then
1971 --Create the Temp table
1972 l_temptable:=GMA_PURGE_ENGINE.Tempcreate(P_purge_id,
1973 p_purge_type,
1974 p_owner,
1975 p_debug_flag);
1976 --Now start inserting all_ids to Temp table
1977 For i in 1..icnt
1978 Loop
1979 GMA_PURGE_ENGINE.Tempinsert(P_purge_id,
1980 p_purge_type,
1981 all_phantom_batch_id(i),
1982 p_debug_flag);
1983 End Loop;
1984
1985 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1986 icnt||' rows inserted in '||l_temptable||' table.');
1987 END IF;
1988
1989 --all_phantoms:=posted||all_phantoms; commented by KH
1990 -- Return only Posted flag and Temp table name to Main
1991
1992 all_phantoms:=posted||l_temptable;
1993 return all_phantoms;
1994
1995 EXCEPTION WHEN OTHERS THEN
1996 if sqlcode=-1858 then
1997 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1998 'Problem raised in GMA_PURGE_ENGINE.glposted_prod.');
1999 GMA_PURGE_UTILITIES.printlong(p_purge_id,'Wrong data given for Purge and Archive');
2000 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2001 'Unhandled EXCEPTION - ' || sqlerrm);
2002 else
2003 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2004 'Problem raised in GMA_PURGE_ENGINE.glposted_prod.');
2005 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2006 'Unhandled EXCEPTION - ' || sqlerrm);
2007
2008 end if;
2009
2010
2011 END GLPOSTED_PROD;
2012
2013 FUNCTION GLPOSTED_PORD(P_Purge_id in sy_purg_mst.purge_id%TYPE,
2014 p_purge_type sy_purg_def.purge_type%TYPE,
2015 p_owner user_users.username%TYPE,
2016 p_debug_flag BOOLEAN)
2017 RETURN LONG
2018
2019 is
2020 cursor purge_crit is
2021 select crit_tag,crit_value
2022 from sy_purg_mst_crit
2023 where purge_id=P_Purge_id;
2024
2025 cursor c1(P_MINPO po_ordr_hdr.po_no%type,
2026 P_MAXPO po_ordr_hdr.po_no%type,
2027 P_MINORGN po_ordr_hdr.orgn_code%type,
2028 P_MAXORGN po_ordr_hdr.orgn_code%type,
2029 P_MINMDATE po_ordr_hdr.last_update_date%type,
2030 P_MAXMDATE po_ordr_hdr.last_update_date%type,
2031 P_MINCDATE po_ordr_hdr.creation_date%type,
2032 P_MAXCDATE po_ordr_hdr.creation_date%type)
2033 is
2034 select distinct PH2.po_id
2035 FROM po_ordr_hdr PH2
2036 WHERE PH2.po_no >= P_MINPO
2037 AND PH2.po_no <= P_MAXPO
2038 AND PH2.orgn_code >= P_MINORGN
2039 AND PH2.orgn_code <= P_MAXORGN
2040 AND PH2.last_update_date >= P_MINMDATE
2041 AND PH2.last_update_date <= P_MAXMDATE
2042 AND PH2.creation_date >= P_MINCDATE
2043 AND PH2.creation_date <= P_MAXCDATE
2044 AND (PH2.po_status = 20);
2045
2046 /*
2047 RECV_LINE_ID (PK) This column may contain a receipt or a return line number: Recpt: fk to po_recv_dtl; Rtrn: fk to po_rtrn_dtl.
2048 DELETE_MARK Standard: 0=Active record (default); 1=Marked for (logical) deletion.
2049 */
2050
2051 cursor c2 (ppo_id po_recv_hst.po_id%type) is
2052 select count(*)
2053 from po_recv_hst
2054 where po_id = ppo_id
2055 and RECV_LINE_ID not in (SELECT RECV_LINE_ID from po_recv_hst where po_id=ppo_id and delete_mark=1)
2056 and gl_posted_ind <> 1;
2057
2058 MINPO po_ordr_hdr.po_no%type;
2059 MAXPO po_ordr_hdr.po_no%type;
2060 MINORGN po_ordr_hdr.orgn_code%type;
2061 MAXORGN po_ordr_hdr.orgn_code%type;
2062 MINMDATE po_ordr_hdr.last_update_date%type;
2063 MAXMDATE po_ordr_hdr.last_update_date%type;
2064 MINCDATE po_ordr_hdr.creation_date%type;
2068 posted varchar2(10):='F';
2065 MAXCDATE po_ordr_hdr.creation_date%type;
2066
2067 no_of_unposted_rec number(10):=0;
2069
2070 TYPE id_tab_type IS TABLE OF number INDEX BY BINARY_INTEGER;
2071 po_id id_tab_type;
2072 all_po_id varchar2(30000):=-9999;
2073
2074 l_temptable Varchar2(2000);
2075 l_TempFlag varchar2(1):='T';
2076
2077 Begin
2078
2079 FOR pcritRec in purge_crit LOOP
2080 if (pcritRec.crit_tag='MINPO') then
2081 MINPO:=pcritRec.crit_value;
2082 elsif (pcritRec.crit_tag='MAXPO') then
2083 MAXPO:=pcritRec.crit_value;
2084 elsif (pcritRec.crit_tag='MINORGN') then
2085 MINORGN:=pcritRec.crit_value;
2086 elsif (pcritRec.crit_tag='MAXORGN') then
2087 MAXORGN:=pcritRec.crit_value;
2088 elsif (pcritRec.crit_tag='MINMDATE') then
2089 MINMDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
2090 elsif (pcritRec.crit_tag='MAXMDATE') then
2091 MAXMDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
2092 elsif (pcritRec.crit_tag='MINCDATE') then
2093 MINCDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
2094 elsif (pcritRec.crit_tag='MAXCDATE') then
2095 MAXCDATE:=to_date(pcritRec.crit_value,'DD-MM-YYYY HH24:MI:SS');
2096 end if;
2097 END LOOP;
2098
2099 FOR c1Rec in c1(MINPO,MAXPO,MINORGN,MAXORGN,MINMDATE,MAXMDATE,MINCDATE,MAXCDATE)
2100 LOOP
2101 OPEN c2 (c1Rec.po_id);
2102 FETCH c2 into no_of_unposted_rec;
2103 CLOSE c2;
2104
2105 IF (no_of_unposted_rec > 0) THEN
2106 po_id(c1%rowcount):=c1Rec.po_id;
2107 -- all_po_id:=all_po_id||','||po_id(c1%rowcount);
2108 ELSE
2109 posted:='T,';
2110 if l_TempFlag='T' then
2111 -- proceede with Temporary table stuff,Create the Temp table
2112 l_temptable:=GMA_PURGE_ENGINE.Tempcreate(P_purge_id,
2113 p_purge_type,
2114 p_owner,
2115 p_debug_flag);
2116 l_TempFlag:='F';
2117 end if;
2118
2119
2120 --Now start inserting all_ids to Temp table
2121 GMA_PURGE_ENGINE.Tempinsert(P_purge_id,
2122 p_purge_type,
2123 c1Rec.po_id,
2124 p_debug_flag);
2125
2126 END IF;
2127 END LOOP;
2128
2129 -- all_po_id:=posted||all_po_id;
2130 all_po_id:=posted||l_temptable;
2131 return all_po_id;
2132
2133 EXCEPTION WHEN OTHERS THEN
2134 if sqlcode=-1858 then
2135 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2136 'Problem raised in GMA_PURGE_ENGINE.GLPOSTED_PORD.');
2137 GMA_PURGE_UTILITIES.printlong(p_purge_id,'Wrong data given for Purge and Archive');
2138 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2139 'Unhandled EXCEPTION - ' || sqlerrm);
2140 end if;
2141 END GLPOSTED_PORD;
2142
2143 FUNCTION Tempcreate(p_purge_id sy_purg_mst.purge_id%TYPE,
2144 p_purge_type sy_purg_def.purge_type%TYPE,
2145 p_owner user_users.username%TYPE,
2146 p_debug_flag BOOLEAN) RETURN CHAR
2147 IS
2148
2149 -- create master rows table for archive
2150
2151 l_result INTEGER;
2152 l_rows INTEGER;
2153 l_cursor INTEGER;
2154 l_badstatement EXCEPTION;
2155 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
2156
2157 l_temptable Varchar2(2000);
2158
2159 -- start of khaja code
2160
2161 get_all_ids long;
2162
2163 BEGIN
2164
2165 -- define temporary table name of ids
2166 -- l_temptable:=p_purge_type||'_'||P_purge_id;
2167 l_temptable:=GMA_PURGE_UTILITIES.makearcname(p_purge_id,
2168 'TEMP');
2169
2170 l_cursor := DBMS_SQL.OPEN_CURSOR;
2171
2172 l_sqlstatement := 'CREATE TABLE ' || p_owner || '.' ||
2173 l_temptable|| ' (all_ids varchar2(100)) nologging';
2174
2175 -- l_temptable|| ' (all_ids number(20))';
2176
2177 IF (p_debug_flag = TRUE) THEN
2178 GMA_PURGE_UTILITIES.printlong(p_purge_id,l_sqlstatement);
2179 END IF;
2180 -- MADE BY KHAJA
2181
2182 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
2183 l_result := DBMS_SQL.EXECUTE(l_cursor);
2184
2185 IF l_result=0 then
2186
2187 l_sqlstatement := 'INSERT INTO '||p_owner ||'.'||l_temptable|| ' values(:V_bindfix)';
2188
2189 -- Created a FUNCTION for GSCC standard fix bug 3871659
2190 -- Standard: File.Sql.6 - Do NOT include any references to hardcoded schema
2191
2192 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2193 'Temporary table '||Get_GmaSchemaName||'.'||l_temptable||' created.');
2194 -- 'Temporary table '||l_temptable||' created.');
2195
2196 IF (p_debug_flag = TRUE) THEN
2197 GMA_PURGE_UTILITIES.printlong(p_purge_id,l_sqlstatement);
2198 END IF;
2199
2200 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
2201 --added by Khaja for SQL BIND VARIABLE project fix see 2935158
2202 dbms_sql.bind_variable(l_cursor, 'V_bindfix','-9999');
2203 l_result := DBMS_SQL.EXECUTE(l_cursor);
2204
2205 END IF;
2206
2207 /* IF l_result <> 0 THEN
2208 RAISE l_badstatement;
2209 END IF;
2210 */
2211
2212
2213 DBMS_SQL.CLOSE_CURSOR(l_cursor);
2214
2218 WHEN l_badstatement THEN
2215 RETURN p_owner||'.'||l_temptable;
2216
2217 EXCEPTION
2219 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2220 'Problem raised in GMA_PURGE_ENGINE.tempcreate.');
2221 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2222 'Unhandled EXCEPTION - ' || sqlerrm);
2223 RAISE;
2224
2225 WHEN OTHERS THEN
2226 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2227 'Problem raised in GMA_PURGE_ENGINE.tempcreate.');
2228 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2229 'Unhandled EXCEPTION - ' || sqlerrm);
2230 RAISE;
2231
2232 END tempcreate;
2233
2234 PROCEDURE Tempinsert(p_purge_id sy_purg_mst.purge_id%TYPE,
2235 p_purge_type sy_purg_def.purge_type%TYPE,
2236 p_all_ids number,
2237 p_debug_flag BOOLEAN)
2238 IS
2239
2240 -- create master rows table for archive
2241
2242 l_result INTEGER;
2243 l_rows INTEGER;
2244 l_cursor INTEGER;
2245 l_badstatement EXCEPTION;
2246 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
2247
2248 l_temptable Varchar2(2000);
2249
2250 -- start of khaja code
2251
2252 get_all_ids long;
2253
2254 BEGIN
2255
2256 -- l_temptable:=p_purge_type||'_'||p_purge_id;
2257 l_temptable:=GMA_PURGE_UTILITIES.makearcname(p_purge_id,
2258 'TEMP');
2259
2260 l_cursor := DBMS_SQL.OPEN_CURSOR;
2261
2262 -- l_sqlstatement := 'INSERT INTO ' || 'GMA' || '.' ||
2263 -- Created a FUNCTION for GSCC standard fix bug 3871659
2264 -- Standard: File.Sql.6 - Do NOT include any references to hardcoded schema
2265
2266 l_sqlstatement := 'INSERT INTO ' ||Get_GmaSchemaName||'.' ||
2267 l_temptable|| ' values(:all_ids)';
2268
2269 -- do not run this stmt
2270 /* IF (p_debug_flag = TRUE) THEN
2271 GMA_PURGE_UTILITIES.printlong(p_purge_id,l_sqlstatement);
2272 END IF;
2273 */
2274
2275 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
2276 dbms_sql.bind_variable(l_cursor, 'all_ids',p_all_ids);
2277 l_result := DBMS_SQL.EXECUTE(l_cursor);
2278
2279 /* IF l_result <> 0 THEN
2280 RAISE l_badstatement;
2281 END IF;
2282 */
2283
2284
2285 DBMS_SQL.CLOSE_CURSOR(l_cursor);
2286
2287 EXCEPTION
2288 WHEN l_badstatement THEN
2289 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2290 'Problem raised in GMA_PURGE_ENGINE.tempinsert.');
2291 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2292 'Unhandled EXCEPTION - ' || sqlerrm);
2293 RAISE;
2294
2295 WHEN OTHERS THEN
2296 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2297 'Problem raised in GMA_PURGE_ENGINE.tempinsert.');
2298 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2299 'Unhandled EXCEPTION - ' || sqlerrm);
2300 RAISE;
2301
2302 END tempinsert;
2303
2304 PROCEDURE Tempdrop(p_purge_id sy_purg_mst.purge_id%TYPE,
2305 p_purge_type sy_purg_def.purge_type%TYPE,
2306 p_debug_flag BOOLEAN)
2307 IS
2308
2309 -- create master rows table for archive
2310
2311 l_result INTEGER;
2312 l_rows INTEGER;
2313 l_cursor INTEGER;
2314 l_badstatement EXCEPTION;
2315 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
2316
2317 l_temptable Varchar2(2000);
2318
2319 -- start of khaja code
2320
2321 get_all_ids long;
2322
2323 BEGIN
2324
2325 -- l_temptable:=P_purge_type||'_'||p_purge_id;
2326 l_temptable:=GMA_PURGE_UTILITIES.makearcname(p_purge_id,
2327 'TEMP');
2328
2329 l_cursor := DBMS_SQL.OPEN_CURSOR;
2330
2331 -- l_sqlstatement := 'DROP TABLE ' || 'GMA' || '.' ||l_temptable;
2332 -- Created a FUNCTION for GSCC standard fix bug 3871659
2333 -- Standard: File.Sql.6 - Do NOT include any references to hardcoded schema
2334
2335 l_sqlstatement := 'DROP TABLE ' ||Get_GmaSchemaName||'.' ||l_temptable;
2336
2337 IF (p_debug_flag = TRUE) THEN
2338 GMA_PURGE_UTILITIES.printlong(p_purge_id,l_sqlstatement);
2339 END IF;
2340
2341 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
2342 l_result := DBMS_SQL.EXECUTE(l_cursor);
2343
2344 IF l_result <> 0 THEN
2345 RAISE l_badstatement;
2346 END IF;
2347
2348 -- Created a FUNCTION for GSCC standard fix bug 3871659
2349 -- Standard: File.Sql.6 - Do NOT include any references to hardcoded schema
2350
2351 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2352 'Temporary table '||Get_GmaSchemaName||'.'||l_temptable||' dropped.');
2353 -- 'Temporary table '||l_temptable||' dropped.');
2354
2355 DBMS_SQL.CLOSE_CURSOR(l_cursor);
2356
2357 EXCEPTION
2358 WHEN l_badstatement THEN
2359 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2360 'Problem raised in GMA_PURGE_ENGINE.tempdrop.');
2361 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2362 'Unhandled EXCEPTION - ' || sqlerrm);
2363 RAISE;
2364
2365 WHEN OTHERS THEN
2366 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2367 'Problem raised in GMA_PURGE_ENGINE.tempdrop.');
2368 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2372 END Tempdrop;
2369 'Unhandled EXCEPTION - ' || sqlerrm);
2370 RAISE;
2371
2373
2374 PROCEDURE ResetTestPurge(p_purge_id sy_purg_mst.purge_id%TYPE,
2375 p_purge_type sy_purg_def.purge_type%TYPE,
2376 p_debug_flag varchar2)
2377 IS
2378 --Prepare the SQL to get all table_names which needs to be renamed based on the
2379 --archive_action equal to 'K'
2380 Cursor Cur_dropTbl(ppurge_id sy_purg_mst.purge_id%TYPE,
2381 c_schema_name VARCHAR2) is
2382 SELECT owner,table_name
2383 FROM all_tables
2384 WHERE owner = c_schema_name
2385 AND
2386 table_name IN(
2387 SELECT 'T' ||LPAD(TO_CHAR(A.purge_id),5,'0')||'_'||B.table_name
2388 FROM SY_PURG_MST A, Sy_purg_def_act B
2389 WHERE A.purge_type=B.purge_type AND A.purge_id=ppurge_id
2390 union
2391 SELECT 'T'||LPAD(TO_CHAR(A.purge_id),5,'0')||'_'||'ARCHIVEROWS'
2392 FROM SY_PURG_MST A
2393 WHERE A.purge_id=ppurge_id
2394 union
2395 SELECT 'T'||LPAD(TO_CHAR(A.purge_id),5,'0')||'_'||'TEMP'
2396 FROM SY_PURG_MST A
2397 WHERE A.purge_id=ppurge_id
2398 );
2399
2400
2401 -- create master rows table for archive
2402
2403 l_result INTEGER;
2404 l_rows INTEGER:=0;
2405 l_cursor INTEGER;
2406 l_badstatement EXCEPTION;
2407 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
2408
2409 l_temptable Varchar2(2000);
2410
2411 -- start of khaja code
2412
2413 get_all_ids long;
2414 l_schema_name VARCHAR2(30); /* Bug 4344986 */
2415
2416 BEGIN
2417
2418 l_schema_name := Get_GmaSchemaName; /* Bug 4344986 */
2419
2420 -- l_temptable:=P_purge_type||'_'||p_purge_id;
2421 l_temptable:=GMA_PURGE_UTILITIES.makearcname(p_purge_id,
2422 'TEMP');
2423 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2424 'Reset Process initiated.');
2425
2426 l_cursor := DBMS_SQL.OPEN_CURSOR;
2427 FOR rec in Cur_DropTbl(P_purge_id, l_schema_name)
2428 Loop
2429 if l_rows=0 then
2430 Update sy_purg_mst set status=0 where purge_id=P_purge_id;
2431 commit;
2432 l_rows:=1;
2433 end if;
2434
2435 --Prepare the RENAME table stmt for GMA user
2436 l_sqlstatement:='DROP TABLE '||rec.owner||'.'||rec.table_name;
2437
2438
2439 IF (p_debug_flag ='T') THEN
2440 GMA_PURGE_UTILITIES.printlong(p_purge_id,l_sqlstatement);
2441 END IF;
2442
2443 --parse the RENAME stmt if table not found.
2444 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
2445 l_result := DBMS_SQL.EXECUTE(l_cursor);
2446
2447
2448 IF l_result <> 0 THEN
2449 RAISE l_badstatement;
2450 END IF;
2451
2452 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2453 rec.owner||'.'||rec.table_name||' Table Dropped.');
2454 End Loop;
2455
2456 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2457 'Reset Process completed successfully.');
2458
2459 -- Close the cursor
2460 DBMS_SQL.CLOSE_CURSOR(l_cursor);
2461
2462 EXCEPTION
2463 WHEN l_badstatement THEN
2464 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2465 'Problem raised in GMA_PURGE_ENGINE.ResetTestPurge.');
2466 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2467 'Unhandled EXCEPTION - ' || sqlerrm);
2468 RAISE;
2469
2470 WHEN OTHERS THEN
2471 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2472 'Problem raised in GMA_PURGE_ENGINE.ResetTestPurge.');
2473 GMA_PURGE_UTILITIES.printlong(p_purge_id,
2474 'Unhandled EXCEPTION - ' || sqlerrm);
2475 RAISE;
2476 END ResetTestPurge;
2477
2478 -- Created a FUNCTION for GSCC standard fix bug 3871659
2479 -- Standard: File.Sql.6 - Do NOT include any references to hardcoded schema
2480
2481 FUNCTION Get_GmaSchemaName
2482 RETURN VARCHAR2
2483 IS
2484 l_return BOOLEAN;
2485 l_status VARCHAR2(1);
2486 l_industry VARCHAR2(1);
2487 l_schema_name VARCHAR2(30);
2488 BEGIN
2489 l_return := FND_INSTALLATION.GET_APP_INFO
2490 ( 'GMA'
2491 , l_status
2492 , l_industry
2493 , l_schema_name
2494 );
2495
2496 IF NOT l_return THEN
2497 RAISE fnd_api.g_exc_unexpected_error;
2498 END IF;
2499
2500 RETURN l_schema_name;
2501
2502 END Get_GmaSchemaName;
2503
2504 END GMA_PURGE_ENGINE;