1 PACKAGE BODY cto_auto_purge_pk AS
2 /*$Header: CTODCFGB.pls 120.4 2008/01/18 16:54:03 abhissri ship $ */
3 /*============================================================================+
4 | Copyright (c) 1999 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8 | |
9 | FILE NAME : CTODCFGB.pls |
10 | DESCRIPTION : Purge Configurations from bom_ato_configurations table |
11 | HISTORY : |
12 | 26-Nov-2002 : Kundan Sarkar Initial Version
13 |
14 |
15 | 03-MAY-2004 KKONADA added delte from BCMO
16 | 3557190
17 | 21-Jun-2005 Renga Kannan Added nocopy hint for all out parameters.
18 | |
19 =============================================================================*/
20
21 g_pkg_name CONSTANT VARCHAR2(30) := 'CTO_AUTO_PURGE_PK';
22
23
24
25 /**************************************************************************
26 Procedure: AUTO_PURGE
27 Parameters: p_base_model NUMBER -- Base model Id
28 p_config_item NUMBER -- Config Item Id
29 p_created_days_ago NUMBER -- Number of days since creation
30 p_last_ref_days_ago NUMBER -- Number of days since last referenced
31 Description: This procedure is called from the concurrent program Purge
32 Configuration Items.
33 *****************************************************************************/
34 PROCEDURE auto_purge (
35 errbuf OUT NOCOPY VARCHAR2,
36 retcode OUT NOCOPY VARCHAR2,
37 p_created_days_ago NUMBER,
38 p_last_ref_days_ago NUMBER,
39 dummy VARCHAR2,
40 p_config_item NUMBER,
41 dummy2 VARCHAR2,
42 p_base_model NUMBER,
43 p_option_item NUMBER default null ) AS
44
45 -- local variables
46
47 l_stmt_num NUMBER;
48 l_rec_count NUMBER := 0;
49 conc_status BOOLEAN ;
50 current_error_code VARCHAR2(240) := NULL;
51 x_return_status VARCHAR2(1);
52 l_batch_id NUMBER;
53 l_request_id NUMBER;
54 l_program_id NUMBER;
55 llineid NUMBER; --Bugfix 6241681
56 lpatolineid NUMBER; --Bugfix 6241681
57
58 -- begin the main procedure.
59 --start bugfix 3557190
60 Type number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
61
62 cfg_item_id_tbl number_tbl_type;
63 l_grp_ref_id_tbl number_tbl_type;
64 --lline_tbl number_tbl_type; --Bugfix 6241681
65 --lpatoline_tbl number_tbl_type; --Bugfix 6241681
66 lbcolline_tbl number_tbl_type; --Bugfix 6241681
67
68 dist_cfg_idx_by_id_tbl number_tbl_type;
69
70 k number;
71
72 l_config_item_id number;
73
74 CURSOR c_grp_ref_id(l_config_item_id NUMBER) IS
75 SELECT group_reference_id
76 FROM bom_cto_src_orgs_b
77 WHERE config_item_id = l_config_item_id
78 AND group_reference_id is not null;
79 --end bugfix 3557190
80
81 BEGIN
82
83 -- initialize the program_id and the request_id from the concurrent request.
84 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
85 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
86
87 -- set the return status.
88 x_return_status := FND_API.G_RET_STS_SUCCESS ;
89
90 -- Set the return code to success
91 retcode := 0;
92
93 -- set the batch_id to the request_id
94 l_batch_id := FND_GLOBAL.CONC_REQUEST_ID;
95
96 -- Log all the input parameters
97 l_stmt_num := 10;
98
99 -- Given parameters.
100 FND_FILE.PUT_LINE(FND_FILE.LOG,'Base Model Id : '||to_char(p_base_model) );
101 FND_FILE.PUT_LINE(FND_FILE.LOG,'Option Item Id : '||to_char(p_option_item) );
102 FND_FILE.PUT_LINE(FND_FILE.LOG,'Config Item Id : '||to_char(p_config_item) );
103 FND_FILE.PUT_LINE(FND_FILE.LOG,'Created Days Ago : '||to_char(p_created_days_ago) );
104 FND_FILE.PUT_LINE(FND_FILE.LOG,'Last Referenced Days Ago : '||to_char(p_last_ref_days_ago));
105
106 -- Handle parameter dependency
107
108 l_stmt_num := 20;
109
110 if
111 ( p_base_model is NULL and p_config_item is NULL and p_created_days_ago is NULL and p_last_ref_days_ago is NULL )
112 then
113 l_stmt_num := 30;
114 FND_FILE.PUT_LINE(FND_FILE.LOG,'No parameters supplied. Exiting ... ' );
115 return ;
116 end if ;
117
118 -- Process deletion of rows
119 delete from bom_ato_configurations
120 where ( p_base_model is null
121 or ( p_base_model is not null and p_option_item is null and base_model_id = p_base_model)
122 or ( p_base_model is not null and p_option_item is not null and config_item_id in
123 ( select config_item_id from bom_ato_configurations bac
124 where bac.base_model_id = p_base_model
125 and bac.component_item_id = p_option_item )
126 )
127 )
128 and ( p_config_item is null or
129 ( p_config_item is not null and config_item_id = p_config_item))
130 and ( p_created_days_ago is null or
131 ( p_created_days_ago is not null and TRUNC(creation_date) <= TRUNC(SYSDATE) - p_created_days_ago))
132 and ( p_last_ref_days_ago is null or
133 ( p_last_ref_days_ago is not null and TRUNC(last_referenced_date) <= TRUNC(SYSDATE)- p_last_ref_days_ago))
134 --bugfix 3557190
135 RETURNING config_item_id BULK COLLECT INTO cfg_item_id_tbl;
136
137 -- Count number of rows deleted.
138
139 l_rec_count := SQL%ROWCOUNT;
140 FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleted from BAC : '||to_char(l_rec_count)||' records.');
141
142 l_stmt_num := 40;
143
144 --removing duplicate config_item_ids
145 IF cfg_item_id_tbl.count > 0 THEN --Bugfix 6241681
146 FOR k IN cfg_item_id_tbl.first..cfg_item_id_tbl.last
147 LOOP
148 IF dist_cfg_idx_by_id_tbl.exists( cfg_item_id_tbl(k) ) THEN
149 null;
150 ELSE
151 dist_cfg_idx_by_id_tbl(cfg_item_id_tbl(k)) := cfg_item_id_tbl(k);
152 END IF;
153 END LOOP;
154 END IF;
155
156 --getting the group reference_id
157 FND_FILE.PUT_LINE(FND_FILE.LOG,'de-activated disctint config item ids ');
158 k := dist_cfg_idx_by_id_tbl.first;
159
160 l_stmt_num := 50;
161
162 WHILE k is not null
163 LOOP
164 oe_debug_pub.add(dist_cfg_idx_by_id_tbl(k),5);
165
166 l_config_item_id := dist_cfg_idx_by_id_tbl(k);
167
168 FOR grp IN c_grp_ref_id(l_config_item_id)
169 LOOP
170
171 l_grp_ref_id_tbl(l_grp_ref_id_tbl.count+1) := grp.group_reference_id;
172
173 END LOOP;
174 k := dist_cfg_idx_by_id_tbl.next(k);
175
176 END LOOP;--while
177
178 FND_FILE.PUT_LINE(FND_FILE.LOG,' grp ref ids '|| to_char(l_grp_ref_id_tbl.count) );
179
180 l_stmt_num := 60;
181
182 -- rkaza. 12/29/2005. bug 4108792.
183 k := l_grp_ref_id_tbl.first;
184 while k is not null
185 loop
186 FND_FILE.PUT_LINE(FND_FILE.LOG, l_grp_ref_id_tbl(k));
187 k := l_grp_ref_id_tbl.next(k);
188 end loop;
189
190 l_stmt_num := 70;
191
192 if l_grp_ref_id_tbl.count > 0 then
193 FORALL k IN l_grp_ref_id_tbl.first..l_grp_ref_id_tbl.last
194 DELETE from bom_cto_model_orgs
195 WHERE group_reference_id = l_grp_ref_id_tbl(k);
196 end if;
197
198 FND_FILE.PUT_LINE(FND_FILE.LOG,'Rows deleted from bcmo:'|| sql%rowcount); --end bugfix 3557190
199
200 --Begin Bugfix 6241681: Removing the reference of purged configs from bom_cto_order_lines
201 l_stmt_num := 80;
202 FND_FILE.PUT_LINE(FND_FILE.LOG,'Removing the references of purged configs from bom_cto_order_lines');
203 k := dist_cfg_idx_by_id_tbl.first;
204
205 WHILE k is not null
206 LOOP
207 l_config_item_id := dist_cfg_idx_by_id_tbl(k);
208 FND_FILE.PUT_LINE(FND_FILE.LOG,'Removing the reference for config id=>'||to_char(l_config_item_id));
209 BEGIN
210 FND_FILE.PUT_LINE(FND_FILE.LOG,'Going to check if this config is linked to some SO=>'||to_char(l_config_item_id));
211
212 SELECT ato_line_id
213 BULK COLLECT INTO lbcolline_tbl --Bulk Collecting as a config may be linked to more than one line
214 FROM bom_cto_order_lines
215 WHERE config_item_id = l_config_item_id;
216
217 FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of lines this config is linked to=>'||to_char(lbcolline_tbl.count));
218
219 IF lbcolline_tbl.count > 0 THEN
220 FOR j IN 1..lbcolline_tbl.count LOOP
221
222 if (CTO_WORKFLOW.config_line_exists(lbcolline_tbl(j))) then
223
224 FND_FILE.PUT_LINE(FND_FILE.LOG,'Config item exists for this model line =>'||to_char(lbcolline_tbl(j)));
225 FND_FILE.PUT_LINE(FND_FILE.LOG,'It needs to be delinked before it can be purged');
226
227 else
228
229 l_stmt_num := 90;
230 SELECT parent_ato_line_id, line_id
231 INTO lpatolineid, llineid
232 FROM bom_cto_order_lines
233 WHERE config_item_id = l_config_item_id
234 and ato_line_id = lbcolline_tbl(j);
235
236 FND_FILE.PUT_LINE(FND_FILE.LOG,'Looping to remove the references of all the parents for config id=>'||to_char(l_config_item_id));
237
238 --FOR j IN 1..lline_tbl.Count LOOP
239 --llineid := lline_tbl(j);
240 --lpatolineid := lpatoline_tbl(j);
241
242 WHILE llineid <> lpatolineid LOOP
243 UPDATE bom_cto_order_lines SET config_item_id = NULL WHERE line_id = llineid;
244
245 llineid := lpatolineid;
246
247 SELECT parent_ato_line_id
248 INTO lpatolineid
249 FROM bom_cto_order_lines
250 WHERE line_id = llineid;
251
252 END LOOP; --while loop ends
253
254 UPDATE bom_cto_order_lines SET config_item_id = NULL WHERE line_id = llineid;
255 END IF; --if config line exists
256 END LOOP; --for loop ends
257 ELSE
258 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cant find reference of config id=>'||to_char(l_config_item_id)||' in bcol');
259 END IF; --lbcolline_tbl.count > 0
260 EXCEPTION
261 WHEN no_data_found then
262 FND_FILE.PUT_LINE(FND_FILE.LOG,'Reference of config id=>'||to_char(l_config_item_id)||' has already been removed');
263
264 END;
265
266 k:= dist_cfg_idx_by_id_tbl.next(k);
267
268 END LOOP; --while loop ends
269 --Bugfix 6241681: Removing the reference of purged configs from bom_cto_order_lines
270
271 FND_FILE.PUT_LINE(FND_FILE.LOG,'Batch ID: '|| to_char(l_batch_id));
272
273 commit ;
274
275 EXCEPTION
276 WHEN FND_API.G_EXC_ERROR THEN
277 oe_debug_pub.add('AUTO_PURGE_CONFIG::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
278 rollback;
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 retcode := 2;
281 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
282
283 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
284 oe_debug_pub.add('AUTO_PURGE_CONFIG::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
285 rollback;
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287 retcode := 2;
288 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
289
290 WHEN OTHERS THEN
291 oe_debug_pub.add('AUTO_PURGE_CONFIG::others error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
292 rollback;
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
294 retcode := 2;
295 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
296 END auto_purge;
297
298
299 END cto_auto_purge_pk;
[Home] [Help]