1 PACKAGE BODY gmp_form_eff_pkg as
2 /* $Header: GMPDLEFB.pls 115.6 2003/03/21 16:52:29 sgidugu noship $ */
3
4 /* Put Global variables , type decalrations here */
5
6
7 /*=========================================================================
8 | PROCEDURE NAME |
9 | delete_eff_rows |
10 | |
11 | TYPE |
12 | public |
13 | |
14 | DESCRIPTION |
15 | Input Parameters |
16 | p_validate - Indicate whether to evaluate if the rows to be deleted |
17 | are under use in any plan in aps or not |
18 | Output Parameters |
19 | errbuf - Standard Conc prgm parameter |
20 | retcode - Standard Conc prgm parameter |
21 | HISTORY |
22 | 18-nov-2002 Abhay Satpute Created |
23 | |
24 ==========================================================================*/
25
26 PROCEDURE delete_eff_rows( errbuf OUT NOCOPY VARCHAR2,
27 retcode OUT NOCOPY NUMBER,
28 p_validate IN NUMBER) IS
29 l_date_cnt INTEGER := 0 ;
30 at_msc_dblink VARCHAR2(32) ;
31 l_instance_id NUMBER ;
32 cur_date DATE ;
33
34 Cursor cur_creation_dates IS
35 SELECT distinct creation_date
36 FROM gmp_form_eff
37 ORDER BY creation_date DESC ;
38 /* order by desc so that data for last extract is NOT deleted */
39
40 BEGIN
41
42 IF p_validate = 1 THEN
43 SELECT a2m_dblink, instance_id
44 INTO at_msc_dblink , l_instance_id
45 FROM mrp_ap_apps_instances ;
46 END IF ;
47
48 IF at_msc_dblink is NOT NULL THEN
49 at_msc_dblink := '@'||at_msc_dblink ;
50 END IF ;
51
52 OPEN cur_creation_dates ;
53 LOOP
54 FETCH cur_creation_dates INTO cur_date ;
55 EXIT WHEN cur_creation_dates%NOTFOUND ;
56 -- Give a message if there are No rows to delete
57 IF trunc(cur_date) = trunc(sysdate)
58 THEN
59 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cannot delete rows for the Current Date --> '||cur_date);
60 END IF;
61 IF l_date_cnt >= 1 THEN
62 -- let the same vars be updated by child proc
63 delete_data(errbuf, retcode ,cur_date,p_validate, at_msc_dblink , l_instance_id) ;
64 END IF ;
65 l_date_cnt := l_date_cnt + 1 ;
66 /* simply loops through without deleting the data for first record
67 By doing this way, we are NOT deleting the latest record even if
68 Validation is given 'No'
69 */
70 END LOOP;
71
72 CLOSE cur_creation_dates ;
73 retcode := 0 ;
74
75 EXCEPTION
76 WHEN Others Then
77 errbuf := sqlcode ||' - '||sqlerrm;
78 retcode := -1 ;
79
80 END delete_eff_rows;
81
82
83 /*=========================================================================
84 | PROCEDURE NAME |
85 | delete_data |
86 | |
87 | TYPE |
88 | Private |
89 | |
90 | DESCRIPTION |
91 | Input Parameters |
92 | p_cur_date - creation date for which rows are being deleted |
93 | p_validate - whether to evaluate use of these rows in aps plans |
94 | p_dblink - passed in from calling proc to be used to make qry |
95 | p_instance_id - used in qry to determine rows of the same instance |
96 | |
97 | Output Parameters |
98 | |
99 | HISTORY |
100 | 18-nov-2002 Abhay Satpute Created |
101 | |
102 ==========================================================================*/
103 PROCEDURE delete_data ( errbuf OUT NOCOPY VARCHAR2,
104 retcode OUT NOCOPY NUMBER,
105 p_cur_date IN DATE ,
106 p_validate IN NUMBER ,
107 p_dblink IN VARCHAR2,
108 p_instance_id IN NUMBER )IS
109
110 rows_deleted NUMBER := 1 ;
111 total_rows_deleted NUMBER := 0 ;
112 eff_in_use INTEGER := 0 ;
113 statement1 VARCHAR2(2000) ;
114 statement2 VARCHAR2(2000) ;
115 statement3 VARCHAR2(2000) ;
116 v_plan_name VARCHAR2(10);
117 v_plan_id NUMBER(10);
118 v_eff_id NUMBER(10);
119 excp_eff_in_use EXCEPTION ;
120
121 TYPE ref_cursor_typ IS REF CURSOR;
122 cur_in_use ref_cursor_typ ;
123 Cur_plan_name ref_cursor_typ ;
124 Cur_plan_name2 ref_cursor_typ ;
125
126 BEGIN
127
128 IF p_validate = 1 THEN /* Validate = 'Yes' */
129 statement1 := 'SELECT 1 '
130 || ' FROM dual '
131 || ' WHERE EXISTS (SELECT 1 from '
132 || ' msc_process_effectivity'||p_dblink||' mpe ,gmp_form_eff ge'
133 || ' where (ge.aps_fmeff_id*2 + 1) = mpe.bill_sequence_id '
134 || ' and mpe.sr_instance_id = :p_instance_id '
135 || ' and mpe.plan_id = -1 '
136 || ' and trunc(ge.creation_date) = :p_cur_date )' ;
137
138 OPEN cur_in_use FOR statement1 USING p_instance_id, trunc(p_cur_date) ;
139 FETCH cur_in_use INTO eff_in_use ;
140 CLOSE cur_in_use ;
141
142 IF nvl(eff_in_use,0) = 1 THEN
143 --
144 statement2 := ' SELECT distinct mp.compile_designator '
145 || ' from '
146 || ' msc_process_effectivity'||p_dblink||' mpe , '
147 || ' msc_plans'||p_dblink||' mp , '
148 || ' gmp_form_eff ge'
149 || ' where mpe.plan_id = mp.plan_id '
150 || ' and mpe.plan_id <> -1 '
151 || ' and (ge.aps_fmeff_id*2 + 1) = mpe.bill_sequence_id '
152 || ' and mpe.sr_instance_id = :p_instance_id '
153 || ' and trunc(ge.creation_date) = :p_cur_date ' ;
154
155 OPEN Cur_plan_name FOR statement2 USING p_instance_id,trunc(p_cur_date);
156 LOOP
157 FETCH Cur_plan_name INTO v_plan_name;
158 EXIT WHEN Cur_plan_name%NOTFOUND;
159 FND_FILE.PUT_LINE(FND_FILE.LOG,'Rows Exist in gmp_form_eff table, cannot delete effectivities for the following Plans --> '||v_plan_name);
160 END LOOP;
161 CLOSE Cur_plan_name ;
162
163 RAISE excp_eff_in_use ;
164
165 END IF ; /* End if for eff_in_use check */
166 --
167 ELSIF p_validate = 2
168 THEN
169 statement3 := ' SELECT distinct mp.compile_designator '
170 || ' from '
171 || ' msc_process_effectivity'||p_dblink||' mpe , '
172 || ' msc_plans'||p_dblink||' mp , '
173 || ' gmp_form_eff ge'
174 || ' where mpe.plan_id = mp.plan_id '
175 || ' and mpe.plan_id <> -1 '
176 || ' and (ge.aps_fmeff_id*2 + 1) = mpe.bill_sequence_id '
177 || ' and mpe.sr_instance_id = :p_instance_id '
178 || ' and trunc(ge.creation_date) = :p_cur_date ' ;
179
180 OPEN Cur_plan_name2 FOR statement3 USING p_instance_id,trunc(p_cur_date);
181 LOOP
182 FETCH Cur_plan_name2 INTO v_plan_name;
183 EXIT WHEN Cur_plan_name2%NOTFOUND;
184 FND_FILE.PUT_LINE(FND_FILE.LOG,'Effectivities for the following Plans have been deleted --> '||v_plan_name);
185 END LOOP;
186 CLOSE Cur_plan_name2 ;
187
188 END IF ; /* End if for Validate Flag check */
189
190 WHILE (rows_deleted >0 )
191 LOOP
192 Delete from gmp_form_eff
193 where creation_date = p_cur_date
194 and rownum < 501 ;
195
196 rows_deleted := SQL%ROWCOUNT ;
197 total_rows_deleted := total_rows_deleted + rows_deleted ;
198
199 commit ;
200 -- We can put further optimize on this
201 IF rows_deleted < 500 THEN
202 EXIT ;
203 END IF ;
204 END LOOP;
205
206 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of rows Successfully deleted from collection run on '||to_char(p_cur_date,'DD-MON-YYYY HH24:MI:SS')||'= '||total_rows_deleted);
207
208 EXCEPTION
209 WHEN excp_eff_in_use THEN
210 FND_FILE.PUT_LINE(FND_FILE.LOG,'Rows Exist in gmp_form_eff table '||v_plan_name||'-'||p_cur_date);
211 NULL ;
212 WHEN others THEN
213 errbuf := sqlcode ||' - '||sqlerrm;
214 retcode := -2 ;
215 END delete_data;
216
217 END gmp_form_eff_pkg;
218
[Home] [Help]