[Home] [Help]
PACKAGE BODY: APPS.MSC_X_PURGE_SUPDEM_HISTORY_PKG
Source
1 PACKAGE BODY msc_x_purge_supdem_history_pkg AS
2 /* $Header: MSCXPHSB.pls 120.1 2005/09/22 03:46:42 vdeshmuk noship $ */
3
4 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
5 IS
6 BEGIN
7 IF fnd_global.conc_request_id > 0 THEN
8 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
9 ELSE
10 null;
11 --DBMS_OUTPUT.PUT_LINE( pBUFF);
12 END IF;
13 EXCEPTION
14 WHEN OTHERS THEN
15 RETURN;
16 END LOG_MESSAGE;
17
18 PROCEDURE purge_sup_dem_history (
19 p_errbuf out nocopy varchar2,
20 p_retcode out nocopy varchar2,
21 p_from_date in varchar2 , /*bug 4504227 */
22 p_to_date in varchar2 , /*bug 4504227 */
23 p_order_type In Number
24 ) IS
25
26
27
28 lv_sql_stmt VARCHAR2(2048);
29 lv_sql_stmt1 VARCHAR2(2048);
30 lv_task_start_time DATE;
31 lv_retval boolean;
32 lv_dummy1 varchar2(32);
33 lv_dummy2 varchar2(32);
34 lv_msc_schema varchar2(32);
35 ----------------------------------------------------------------
36 -- begin
37 -----------------------------------------------------------------
38
39 lv_from_date_offset number;
40 lv_to_date_offset number;
41
42 BEGIN
43
44 if (p_from_date is not null) then
45 log_message('p_from_date '||p_from_date) ;
46 lv_from_date_offset := sysdate-fnd_date.canonical_to_date(p_from_date); /* Bug 4504227 */
47 log_message('lv_from_date_offset '||lv_from_date_offset) ;
48 end if;
49
50 if (p_to_date is not null) then
51 log_message('p_to_date '||p_to_date) ;
52 lv_to_date_offset := sysdate-fnd_date.canonical_to_date(p_to_date); /* Bug 4504227 */
53 log_message('lv_to_date_offset '||lv_to_date_offset) ;
54
55 end if;
56
57 lv_task_start_time:= SYSDATE;
58
59 lv_retval := FND_INSTALLATION.GET_APP_INFO (
60 'MSC', lv_dummy1, lv_dummy2, lv_msc_schema);
61
62 IF (p_from_date is null)
63 and (p_to_date is null)
64 and (p_order_type is null) then
65
66 /* IF no parameters provided, then truncate the table */
67 lv_sql_stmt:= 'TRUNCATE TABLE '||lv_msc_schema||'.MSC_SUP_DEM_HISTORY';
68
69 EXECUTE IMMEDIATE lv_sql_stmt;
70 COMMIT;
71 LOG_MESSAGE('Table MSC_SUP_DEM_HISTORY truncated.');
72
73 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
74 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
75 TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
76 LOG_MESSAGE(' '||FND_MESSAGE.GET);
77
78 ELSE
79 /* create the basic delete sql for Deletes in batches of 100K */
80 lv_sql_stmt := ' DELETE /*+ PARALLEL(msdh) */ '
81 ||' MSC_SUP_DEM_HISTORY msdh '
82 ||' WHERE plan_id = -1 '
83 ||' AND ROWNUM < 100001 ';
84
85 /* If Order type is given , include order type in the sql */
86 if (p_order_type is not null) then
87 lv_sql_stmt := lv_sql_stmt || ' AND publisher_order_type = '|| p_order_type;
88 end if;
89
90 /* IF to_date is provided use TO_DATE */
91 if (p_to_date is not null) and (p_from_date is null) then
92 lv_sql_stmt1 := lv_sql_stmt;
93
94 lv_sql_stmt := lv_sql_stmt
95 ||' AND key_date_new <= sysdate-('||lv_to_date_offset||')';
96
97 lv_sql_stmt1 := lv_sql_stmt1
98 ||' AND key_date_old <= sysdate-('||lv_to_date_offset||')';
99
100 /* IF from_date is provided use from_date */
101 elsif (p_to_date is null) and (p_from_date is not null) then
102 lv_sql_stmt1 := lv_sql_stmt;
103
104 lv_sql_stmt := lv_sql_stmt
105 ||' AND key_date_new >= sysdate-('||lv_from_date_offset||')';
106
107 lv_sql_stmt1 := lv_sql_stmt1
108 ||' AND key_date_old >= sysdate-('||lv_from_date_offset||')';
109
110 /* IF from and to date are provided, use both */
111 elsif (p_to_date is not null) and (p_from_date is not null) then
112 lv_sql_stmt1 := lv_sql_stmt;
113
114 lv_sql_stmt := lv_sql_stmt
115 ||' AND key_date_new >= sysdate-('||lv_from_date_offset||')'
116 ||' AND key_date_new <= sysdate-('||lv_to_date_offset ||') ';
117
118 lv_sql_stmt1 := lv_sql_stmt1
119 ||' AND key_date_old >= sysdate-('||lv_from_date_offset||')'
120 ||' AND key_date_old <= sysdate-('||lv_to_date_offset ||') ';
121 end if;
122
123 LOG_MESSAGE(' SQL#1 executed : ' || lv_sql_stmt );
124 LOG_MESSAGE(' SQL#2 executed : ' || lv_sql_stmt1 );
125
126 LOOP
127 EXECUTE IMMEDIATE lv_sql_stmt;
128 LOG_MESSAGE('Number of records deleted in SQL#1 : ' || SQL%ROWCOUNT);
129
130 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
131 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
132 TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
133 LOG_MESSAGE(' '||FND_MESSAGE.GET);
134
135 lv_task_start_time := SYSDATE;
136
137 EXIT WHEN SQL%ROWCOUNT= 0;
138 COMMIT;
139
140 END LOOP;
141
142 IF (lv_sql_stmt1 is not null) then
143 LOOP
144 EXECUTE IMMEDIATE lv_sql_stmt1;
145 LOG_MESSAGE('Number of records deleted in SQL#2 : ' || SQL%ROWCOUNT);
146
147 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
148 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
149 TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
150 LOG_MESSAGE(' '||FND_MESSAGE.GET);
151
152 lv_task_start_time := SYSDATE;
153
154 EXIT WHEN SQL%ROWCOUNT= 0;
155 COMMIT;
156
157 END LOOP;
158 END IF;
159
160 END IF;
161
162 commit;
163
164 END purge_sup_dem_history;
165
166 END msc_x_purge_supdem_history_pkg;