[Home] [Help]
PACKAGE BODY: APPS.CN_PURGE_PKG
Source
1 PACKAGE BODY cn_purge_pkg AS
2 -- $Header: cnpurgeb.pls 115.6 2002/11/21 21:06:50 hlchen ship $
3
4 /*
5
6 Package Body Name
7 cn_purge_pkg
8 Purpose
9
10 History
11 -------- ------------- -------------------------------------------------------+
12 04/16/96 Xinyang Fan Created
13 04/26/00 Vijay Pendyala Updated
14 Changes as 11.5.1 schema has been changed
15 12/03/01 ymao bug 2129772
16 */
17
18
19 PROCEDURE purge(errbuf OUT NOCOPY VARCHAR2,
20 retcode OUT NOCOPY NUMBER,
21 x_start_period IN varchar2,
22 x_end_period IN varchar2,
23 x_salesrep_id IN number)
24 IS
25 l_start_period_id number(15);
26 l_end_period_id number(15);
27 l_start_date DATE;
28 l_end_date DATE;
29 BEGIN
30 SELECT period_id, start_date
31 INTO l_start_period_id, l_start_date
32 FROM cn_periods
33 WHERE period_name like x_start_period;
34
35 SELECT period_id, end_date
36 INTO l_end_period_id, l_end_date
37 FROM cn_periods
38 WHERE period_name like x_end_period;
39
40 -- Delete process batches from CN_PROCESS_BATCHES with status_code = 'VOID'
41 DELETE FROM CN_PROCESS_BATCHES WHERE status_code = 'VOID';
42
43 -- Delete process audit lines from CN_PROCESS_AUDIT_LINES and CN_PROCESS_AUDITS
44 -- Commented out as this process is taking a very long time for deleting as
45 -- there can be lot of records in these tables as part of auditing.
46 --DELETE FROM CN_PROCESS_AUDITS;
47 --DELETE FROM CN_PROCESS_AUDIT_LINES;
48
49 IF (x_salesrep_id IS NOT NULL) THEN
50 -- Delete transactions from CN_COMMISSION_HEADERS
51 DELETE FROM CN_COMMISSION_HEADERS
52 WHERE direct_salesrep_id = x_salesrep_id
53 AND processed_date between l_start_date and l_end_date;
54 COMMIT;
55
56 -- Delete transactions from CN_COMMISSION_LINES
57 DELETE FROM CN_COMMISSION_LINES
58 WHERE credited_salesrep_id = x_salesrep_id
59 AND processed_period_id between l_start_period_id and l_end_period_id;
60 COMMIT;
61
62 -- Update CN_SRP_PERIODS
66 balance1_bbd = 0,
63 UPDATE cn_srp_periods SET
64 balance1_dtd = 0,
65 balance1_ctd = 0,
67 balance1_bbc = 0,
68 balance2_dtd = 0,
69 balance2_ctd = 0,
70 balance2_bbd = 0,
71 balance2_bbc = 0,
72 balance3_dtd = 0,
73 balance3_ctd = 0,
74 balance3_bbd = 0,
75 balance3_bbc = 0,
76 balance4_dtd = 0,
77 balance4_ctd = 0,
78 balance4_bbd = 0,
79 balance4_bbc = 0,
80 balance5_dtd = 0,
81 balance5_ctd = 0,
82 balance5_bbd = 0,
83 balance5_bbc = 0,
84 balance6_dtd = 0,
85 balance6_ctd = 0,
86 balance6_bbd = 0,
87 balance6_bbc = 0,
88 balance7_dtd = 0,
89 balance7_ctd = 0,
90 balance7_bbd = 0,
91 balance7_bbc = 0,
92 balance8_dtd = 0,
93 balance8_ctd = 0,
94 balance8_bbd = 0,
95 balance8_bbc = 0,
96 balance9_dtd = 0,
97 balance9_ctd = 0,
98 balance9_bbd = 0,
99 balance9_bbc = 0,
100 balance10_dtd = 0,
101 balance10_ctd = 0,
102 balance10_bbd = 0,
103 balance10_bbc = 0,
104 balance11_dtd = 0,
105 balance11_ctd = 0,
106 balance11_bbd = 0,
107 balance11_bbc = 0,
108 balance12_dtd = 0,
109 balance12_ctd = 0,
110 balance12_bbd = 0,
111 balance12_bbc = 0,
112 balance13_dtd = 0,
113 balance13_ctd = 0,
114 balance13_bbd = 0,
115 balance13_bbc = 0,
116 balance14_dtd = 0,
117 balance14_ctd = 0,
118 balance14_bbd = 0,
119 balance14_bbc = 0,
120 balance15_dtd = 0,
121 balance15_ctd = 0,
122 balance15_bbd = 0,
123 balance15_bbc = 0,
124 balance16_dtd = 0,
125 balance16_ctd = 0,
126 balance16_bbd = 0,
127 balance16_bbc = 0,
128 balance17_dtd = 0,
129 balance17_ctd = 0,
130 balance17_bbd = 0,
131 balance17_bbc = 0,
132 balance18_dtd = 0,
133 balance18_ctd = 0,
134 balance18_bbd = 0,
135 balance18_bbc = 0,
136 balance19_dtd = 0,
137 balance19_ctd = 0,
138 balance19_bbd = 0,
139 balance19_bbc = 0,
140 balance20_dtd = 0,
141 balance20_ctd = 0,
142 balance20_bbd = 0,
143 balance20_bbc = 0,
144 balance21_dtd = 0,
145 balance21_ctd = 0,
146 balance21_bbd = 0,
147 balance21_bbc = 0,
148 balance22_dtd = 0,
149 balance22_ctd = 0,
150 balance22_bbd = 0,
151 balance22_bbc = 0,
152 balance23_dtd = 0,
153 balance23_ctd = 0,
154 balance23_bbd = 0,
155 balance23_bbc = 0,
156 balance24_dtd = 0,
157 balance24_ctd = 0,
158 balance24_bbd = 0,
159 balance24_bbc = 0,
160 balance25_dtd = 0,
161 balance25_ctd = 0,
162 balance25_bbd = 0,
163 balance25_bbc = 0,
164 balance26_dtd = 0,
165 balance26_ctd = 0,
166 balance26_bbd = 0,
167 balance26_bbc = 0,
168 balance27_dtd = 0,
169 balance27_ctd = 0,
170 balance27_bbd = 0,
171 balance27_bbc = 0,
172 balance28_dtd = 0,
173 balance28_ctd = 0,
174 balance28_bbd = 0,
175 balance28_bbc = 0,
176 balance29_dtd = 0,
177 balance29_ctd = 0,
178 balance29_bbd = 0,
179 balance29_bbc = 0,
180 balance30_dtd = 0,
181 balance30_ctd = 0,
182 balance30_bbd = 0,
183 balance30_bbc = 0,
184 balance31_dtd = 0,
185 balance31_ctd = 0,
186 balance31_bbd = 0,
187 balance31_bbc = 0,
188 balance32_dtd = 0,
189 balance32_ctd = 0,
190 balance32_bbd = 0,
191 balance32_bbc = 0,
192 balance33_dtd = 0,
193 balance33_ctd = 0,
194 balance33_bbd = 0,
195 balance33_bbc = 0
196 WHERE salesrep_id = x_salesrep_id
197 AND period_id between l_start_period_id and l_end_period_id;
198 COMMIT;
199
200 -- Update CN_SRP_PERIOD_QUOTAS
201 UPDATE cn_srp_period_quotas SET
202 commission_payed_itd = (commission_payed_itd - nvl(commission_payed_ptd,0)),
203 commission_payed_ptd = 0,
204 perf_achieved_itd = (perf_achieved_itd - nvl(perf_achieved_ptd,0)),
205 perf_achieved_ptd = 0,
206 advance_recovered_itd = (advance_recovered_itd - nvl(advance_recovered_ptd,0)),
207 advance_recovered_ptd = 0,
208 advance_to_rec_itd = (advance_to_rec_itd - nvl(advance_to_rec_ptd,0)),
209 advance_to_rec_ptd = 0,
210 comm_pend_itd = (comm_pend_itd - nvl(comm_pend_ptd,0)),
211 comm_pend_ptd = 0,
212 recovery_amount_itd = (recovery_amount_itd - nvl(recovery_amount_ptd,0)),
213 recovery_amount_ptd = 0,
214 performance_goal_itd = (performance_goal_itd - nvl(performance_goal_ptd,0)),
215 performance_goal_ptd = 0
216 WHERE salesrep_id = x_salesrep_id
217 AND period_id between l_start_period_id and l_end_period_id;
218
219 COMMIT;
220
221 -- CN_SRP_PER_QUOTA_RC
222 UPDATE cn_srp_per_quota_rc SET
223 year_to_date = 0,
224 quarter_to_date = 0,
225 period_to_date = 0
226 WHERE salesrep_id = x_salesrep_id
227 AND period_id between l_start_period_id and l_end_period_id;
228 COMMIT;
229
230 -- Delete Payment from CN_PAYMENT_WORKSHEETS, CN_PAYMENT_API
231 DELETE FROM CN_PAYMENT_WORKSHEETS
232 WHERE salesrep_id = x_salesrep_id
233 AND payrun_id IN (SELECT payrun_id FROM CN_PAYRUNS
234 WHERE accounting_period_id between l_start_period_id and l_end_period_id);
235 COMMIT;
236
237 DELETE FROM CN_PAYMENT_API
238 WHERE salesrep_id = x_salesrep_id
239 AND period_id between l_start_period_id and l_end_period_id
240 AND payrun_id IN (SELECT payrun_id FROM CN_PAYRUNS
241 WHERE accounting_period_id between l_start_period_id and l_end_period_id);
242 COMMIT;
243
247 WHERE salesrep_id = x_salesrep_id
244 -- Delete journal entries from CN_LEDGER_JOURNAL_ENTRIES
245 DELETE FROM CN_LEDGER_JOURNAL_ENTRIES
246 WHERE srp_period_id IN (SELECT srp_period_id FROM CN_SRP_PERIODS
248 AND period_id between l_start_period_id and l_end_period_id);
249 COMMIT;
250
251 -- Delete transactions from CN_COMM_LINES_API, CN_NOT_TRX, CN_TRX, CN_TRX_LINES, CN_TRX_SALES_LINES
252 DELETE FROM CN_COMM_LINES_API
253 WHERE (employee_number, type) = (SELECT employee_number, type FROM cn_salesreps WHERE salesrep_id = x_salesrep_id)
254 AND processed_date between l_start_date and l_end_date;
255 COMMIT;
256
257 DELETE FROM cn_not_trx
258 WHERE source_trx_id IN (SELECT source_trx_id
259 FROM cn_trx
260 WHERE trx_id IN (SELECT trx_id
261 FROM cn_trx_sales_lines
262 WHERE salesrep_id = x_salesrep_id
263 AND processed_period_id BETWEEN l_start_period_id AND l_end_period_id));
264 COMMIT;
265
266 DELETE FROM cn_trx
267 WHERE trx_id IN (SELECT trx_id
268 FROM cn_trx_sales_lines
269 WHERE salesrep_id = x_salesrep_id
270 AND processed_period_id BETWEEN l_start_period_id AND l_end_period_id);
271 COMMIT;
272
273 DELETE FROM cn_trx_lines
274 WHERE trx_line_id IN (SELECT trx_line_id
275 FROM cn_trx_sales_lines
276 WHERE salesrep_id = x_salesrep_id
277 AND processed_period_id BETWEEN l_start_period_id AND l_end_period_id);
278 COMMIT;
279
280 DELETE FROM CN_TRX_SALES_LINES
281 WHERE salesrep_id = x_salesrep_id
282 AND processed_period_id between l_start_period_id and l_end_period_id;
283 COMMIT;
284 ELSE
285 -- Delete transactions from CN_COMMISSION_HEADERS
286 DELETE FROM CN_COMMISSION_HEADERS
287 WHERE direct_salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
288 AND processed_date between l_start_date and l_end_date;
289 COMMIT;
290
291 -- Delete transactions from CN_COMMISSION_LINES
292 DELETE FROM CN_COMMISSION_LINES
293 WHERE processed_period_id between l_start_period_id and l_end_period_id;
294 COMMIT;
295
296 -- Update CN_SRP_PERIODS
297 UPDATE cn_srp_periods SET
298 balance1_dtd = 0,
299 balance1_ctd = 0,
300 balance1_bbd = 0,
301 balance1_bbc = 0,
302 balance2_dtd = 0,
303 balance2_ctd = 0,
304 balance2_bbd = 0,
305 balance2_bbc = 0,
306 balance3_dtd = 0,
307 balance3_ctd = 0,
308 balance3_bbd = 0,
309 balance3_bbc = 0,
310 balance4_dtd = 0,
311 balance4_ctd = 0,
312 balance4_bbd = 0,
313 balance4_bbc = 0,
314 balance5_dtd = 0,
315 balance5_ctd = 0,
316 balance5_bbd = 0,
317 balance5_bbc = 0,
318 balance6_dtd = 0,
319 balance6_ctd = 0,
320 balance6_bbd = 0,
321 balance6_bbc = 0,
322 balance7_dtd = 0,
323 balance7_ctd = 0,
324 balance7_bbd = 0,
325 balance7_bbc = 0,
326 balance8_dtd = 0,
327 balance8_ctd = 0,
328 balance8_bbd = 0,
329 balance8_bbc = 0,
330 balance9_dtd = 0,
331 balance9_ctd = 0,
332 balance9_bbd = 0,
333 balance9_bbc = 0,
334 balance10_dtd = 0,
335 balance10_ctd = 0,
336 balance10_bbd = 0,
337 balance10_bbc = 0,
338 balance11_dtd = 0,
339 balance11_ctd = 0,
340 balance11_bbd = 0,
341 balance11_bbc = 0,
342 balance12_dtd = 0,
343 balance12_ctd = 0,
344 balance12_bbd = 0,
345 balance12_bbc = 0,
346 balance13_dtd = 0,
347 balance13_ctd = 0,
348 balance13_bbd = 0,
349 balance13_bbc = 0,
350 balance14_dtd = 0,
351 balance14_ctd = 0,
352 balance14_bbd = 0,
353 balance14_bbc = 0,
354 balance15_dtd = 0,
355 balance15_ctd = 0,
356 balance15_bbd = 0,
357 balance15_bbc = 0,
358 balance16_dtd = 0,
359 balance16_ctd = 0,
360 balance16_bbd = 0,
361 balance16_bbc = 0,
362 balance17_dtd = 0,
363 balance17_ctd = 0,
364 balance17_bbd = 0,
365 balance17_bbc = 0,
366 balance18_dtd = 0,
367 balance18_ctd = 0,
368 balance18_bbd = 0,
369 balance18_bbc = 0,
370 balance19_dtd = 0,
371 balance19_ctd = 0,
372 balance19_bbd = 0,
373 balance19_bbc = 0,
374 balance20_dtd = 0,
375 balance20_ctd = 0,
376 balance20_bbd = 0,
377 balance20_bbc = 0,
378 balance21_dtd = 0,
379 balance21_ctd = 0,
380 balance21_bbd = 0,
381 balance21_bbc = 0,
382 balance22_dtd = 0,
383 balance22_ctd = 0,
384 balance22_bbd = 0,
385 balance22_bbc = 0,
386 balance23_dtd = 0,
387 balance23_ctd = 0,
388 balance23_bbd = 0,
389 balance23_bbc = 0,
390 balance24_dtd = 0,
391 balance24_ctd = 0,
392 balance24_bbd = 0,
393 balance24_bbc = 0,
394 balance25_dtd = 0,
395 balance25_ctd = 0,
396 balance25_bbd = 0,
397 balance25_bbc = 0,
398 balance26_dtd = 0,
399 balance26_ctd = 0,
400 balance26_bbd = 0,
401 balance26_bbc = 0,
402 balance27_dtd = 0,
403 balance27_ctd = 0,
404 balance27_bbd = 0,
405 balance27_bbc = 0,
406 balance28_dtd = 0,
407 balance28_ctd = 0,
408 balance28_bbd = 0,
409 balance28_bbc = 0,
410 balance29_dtd = 0,
411 balance29_ctd = 0,
412 balance29_bbd = 0,
413 balance29_bbc = 0,
414 balance30_dtd = 0,
415 balance30_ctd = 0,
416 balance30_bbd = 0,
417 balance30_bbc = 0,
418 balance31_dtd = 0,
419 balance31_ctd = 0,
423 balance32_ctd = 0,
420 balance31_bbd = 0,
421 balance31_bbc = 0,
422 balance32_dtd = 0,
424 balance32_bbd = 0,
425 balance32_bbc = 0,
426 balance33_dtd = 0,
427 balance33_ctd = 0,
428 balance33_bbd = 0,
429 balance33_bbc = 0
430 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
431 AND period_id between l_start_period_id and l_end_period_id;
432 COMMIT;
433
434 -- Update CN_SRP_PERIOD_QUOTAS
435 UPDATE cn_srp_period_quotas SET
436 commission_payed_itd = (commission_payed_itd - nvl(commission_payed_ptd,0)),
437 commission_payed_ptd = 0,
438 perf_achieved_itd = (perf_achieved_itd - nvl(perf_achieved_ptd,0)),
439 perf_achieved_ptd = 0,
440 advance_recovered_itd = (advance_recovered_itd - nvl(advance_recovered_ptd,0)),
441 advance_recovered_ptd = 0,
442 advance_to_rec_itd = (advance_to_rec_itd - nvl(advance_to_rec_ptd,0)),
443 advance_to_rec_ptd = 0,
444 comm_pend_itd = (comm_pend_itd - nvl(comm_pend_ptd,0)),
445 comm_pend_ptd = 0,
446 recovery_amount_itd = (recovery_amount_itd - nvl(recovery_amount_ptd,0)),
447 recovery_amount_ptd = 0,
448 performance_goal_itd = (performance_goal_itd - nvl(performance_goal_ptd,0)),
449 performance_goal_ptd = 0
450 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
451 AND period_id between l_start_period_id and l_end_period_id;
452
453 COMMIT;
454
455 -- CN_SRP_PER_QUOTA_RC
456 UPDATE cn_srp_per_quota_rc SET
457 year_to_date = 0,
458 quarter_to_date = 0,
459 period_to_date = 0
460 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
461 AND period_id between l_start_period_id and l_end_period_id;
462 COMMIT;
463
464 -- Delete Payment from CN_PAYMENT_WORKSHEETS, CN_PAYMENT_API
465 DELETE FROM CN_PAYMENT_WORKSHEETS
466 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
467 AND payrun_id IN (SELECT payrun_id FROM CN_PAYRUNS
468 WHERE accounting_period_id between l_start_period_id and l_end_period_id);
469 COMMIT;
470
471 DELETE FROM CN_PAYMENT_API
472 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
473 AND period_id between l_start_period_id and l_end_period_id
474 AND payrun_id IN (SELECT payrun_id FROM CN_PAYRUNS
475 WHERE accounting_period_id between l_start_period_id and l_end_period_id);
476 COMMIT;
477
478 -- Delete journal entries from CN_LEDGER_JOURNAL_ENTRIES
479 DELETE FROM CN_LEDGER_JOURNAL_ENTRIES
480 WHERE srp_period_id IN (SELECT srp_period_id FROM CN_SRP_PERIODS
481 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
482 AND period_id between l_start_period_id and l_end_period_id);
483 COMMIT;
484
485 -- Delete transactions from CN_COMM_LINES_API, CN_NOT_TRX, CN_TRX, CN_TRX_LINES, CN_TRX_SALES_LINES
486 DELETE FROM CN_COMM_LINES_API
487 WHERE (employee_number, TYPE) IN (SELECT employee_number, TYPE FROM cn_salesreps)
488 AND processed_date between l_start_date and l_end_date;
489 COMMIT;
490
491 DELETE FROM cn_not_trx
492 WHERE source_trx_id IN (SELECT source_trx_id
493 FROM cn_trx
494 WHERE trx_id IN (SELECT trx_id
495 FROM cn_trx_sales_lines
496 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
497 AND processed_period_id BETWEEN l_start_period_id AND l_end_period_id));
498 COMMIT;
499
500 DELETE FROM cn_trx
501 WHERE trx_id IN (SELECT trx_id
502 FROM cn_trx_sales_lines
503 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
504 AND processed_period_id BETWEEN l_start_period_id AND l_end_period_id);
505 COMMIT;
506
507 DELETE FROM cn_trx_lines
508 WHERE trx_line_id IN (SELECT trx_line_id
509 FROM cn_trx_sales_lines
510 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
511 AND processed_period_id BETWEEN l_start_period_id AND l_end_period_id);
512 COMMIT;
513
514 DELETE FROM CN_TRX_SALES_LINES
515 WHERE salesrep_id IN (SELECT salesrep_id FROM cn_salesreps)
516 AND processed_period_id between l_start_period_id and l_end_period_id;
517 COMMIT;
518 END IF;
519
520 EXCEPTION
521 WHEN NO_DATA_FOUND THEN
522 rollback;
523 cn_message_pkg.debug('Invalid period name');
524 WHEN OTHERS THEN
525 rollback;
526 cn_message_pkg.debug('Other error occurred during purge');
527 END purge;
528
529 END cn_purge_pkg;