1 PACKAGE BODY RCV_AP_PURGE_PVT AS
2 /* $Header: RCVVPUDB.pls 120.1 2005/06/29 00:57:01 pjiang noship $ */
3
4 -- <DOC PURGE FPJ START>
5
6 g_pkg_name CONSTANT VARCHAR2(30) := 'RCV_AP_PURGE_PVT';
7 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8 G_MODULE_PREFIX CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
9
10 --*********************************************************************
11 ----------------- Private Procedure Prototypes-------------------------
12 --*********************************************************************
13
14 PROCEDURE delete_costing_data
15 ( x_return_status OUT NOCOPY VARCHAR2,
16 p_rt_ids_tbl IN PO_TBL_NUMBER
17 );
18
19 PROCEDURE delete_rcv_attachments
20 ( x_return_status OUT NOCOPY VARCHAR2,
21 p_rsh_ids_tbl IN PO_TBL_NUMBER,
22 p_rti_ids_tbl IN PO_TBL_NUMBER
23 );
24
25 --*********************************************************************
26 -------------------------- Public Procedures --------------------------
27 --*********************************************************************
28
29
30 -----------------------------------------------------------------------
31 --Start of Comments
32 --Name: summarize_receipts
33 --Pre-reqs:
34 --Modifies: po_history_receipts
35 --Locks:
36 -- None
37 --Function:
38 -- Record necessary information for receipt lines that are about to be purged
39 -- To purge a receipt line, it has to be linked to a PO that is in the purge
40 -- list
41 --Parameters:
42 --IN:
43 --p_purge_name
44 -- Name of this purge process
45 --p_range_size
46 -- The id range size of the documents being inserted into history tables
47 -- per commit cycle
48 --p_req_lower_limit
49 -- min id among all pos to be purged
50 --p_req_upper_limit
51 -- max id among all pos to be purged
52 --IN OUT:
53 --OUT:
54 --x_return_status
55 -- status of the procedure
56 --Returns:
57 --Notes:
58 --Testing:
59 --End of Comments
60 ------------------------------------------------------------------------
61
62 PROCEDURE summarize_receipts
63 ( x_return_status OUT NOCOPY VARCHAR2,
64 p_purge_name IN VARCHAR2,
65 p_range_size IN NUMBER,
66 p_po_lower_limit IN NUMBER,
67 p_po_upper_limit IN NUMBER
68 ) IS
69
70 l_api_name VARCHAR2(50) := 'summarize_receipts';
71 l_progress VARCHAR2(3);
72 l_range_low NUMBER;
73 l_range_high NUMBER;
74
75 BEGIN
76
77 l_progress := '000';
78
79
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 IF (p_po_lower_limit = -1) THEN
83 RETURN;
84 END IF;
85
86 l_range_low := p_po_lower_limit;
87 l_range_high := p_po_lower_limit + p_range_size;
88
89 LOOP
90 l_progress := '010';
91
92 IF (g_fnd_debug = 'Y') THEN
93 asn_debug.put_line('Before insert into phr. low = ' || l_range_low || ' high = ' || l_range_high);
94 END IF;
95
96 --bug3256316
97 --Added NVL() around effective date check.
98
99 -- SQL What: Insert rcv shipment line information in history
100 -- table if the po line it associates with is in the
101 -- purge list
102 -- SQL Why: Need to record data in history table before actual
103 -- purge happens
104 INSERT INTO po_history_receipts
105 ( receipt_num,
106 shipment_num,
107 transaction_date,
108 vendor_id,
109 receiver_name,
110 item_description,
111 purge_name
112 )
113 SELECT NVL(RSH.receipt_num, -1),
114 RSH.shipment_num,
115 RSH.shipped_date,
116 RSH.vendor_id,
117 PAPF.full_name,
118 RSL.item_description,
119 p_purge_name
120 FROM per_all_people_f PAPF,
121 rcv_shipment_lines RSL,
122 rcv_shipment_headers RSH,
123 po_purge_po_list PPL
124 WHERE PPL.double_check_flag = 'Y'
125 AND PPL.po_header_id = RSL.po_header_id
126 AND PPL.po_header_id BETWEEN l_range_low AND l_range_high
127 AND RSL.shipment_header_id = RSH.shipment_header_id
128 AND RSL.employee_id = PAPF.person_id (+)
129 AND TRUNC(SYSDATE) BETWEEN NVL(PAPF.effective_start_date,
130 TRUNC(SYSDATE))
131 AND NVL(PAPF.effective_end_date,
132 TRUNC(SYSDATE));
133
134 COMMIT;
135
136 l_range_low := l_range_high + 1;
137 l_range_high := l_range_low + p_range_size;
138
139 IF (l_range_low > p_po_upper_limit) THEN
140 l_progress := '020';
141 EXIT;
142 END IF;
143
144 END LOOP;
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
149 FND_MSG_PUB.add_exc_msg
150 ( p_pkg_name => g_pkg_name,
151 p_procedure_name => l_api_name || '.' || l_progress
152 );
153
154 END summarize_receipts;
155
156
157 -----------------------------------------------------------------------
158 --Start of Comments
159 --Name: delete_receipts
160 --Pre-reqs:
161 --Modifies: Various RCV transaction tables
162 --Locks:
163 -- None
164 --Function:
165 -- Delete receiving data when PO are getting purged
166 --Parameters:
167 --IN:
168 --p_range_size
169 -- Number of documents to be purged per commit cycle
170 --p_po_lower_limit
171 -- min id among all pos to be purged
172 --p_po_upper_limit
173 -- max id among all pos to be purged
174 --IN OUT:
175 --OUT:
176 --x_return_status
177 -- status of the procedure
178 --Returns:
179 --Notes:
180 --Testing:
181 --End of Comments
182 ------------------------------------------------------------------------
183
184 PROCEDURE delete_receipts
185 ( x_return_status OUT NOCOPY VARCHAR2,
186 p_range_size IN NUMBER,
187 p_po_lower_limit IN NUMBER,
188 p_po_upper_limit IN NUMBER
189 ) IS
190
191
192 TYPE num_tbltyp IS TABLE OF NUMBER;
193 l_ids_tbl num_tbltyp;
194
195
196 l_api_name VARCHAR2(50) := 'delete_receipts';
197 l_progress VARCHAR2(3);
198
199 l_range_low NUMBER;
200 l_range_high NUMBER;
201
202 l_return_status VARCHAR2(1);
203
204 l_rsh_ids_tbl PO_TBL_NUMBER;
205 l_rt_ids_tbl PO_TBL_NUMBER;
206 l_rti_ids_tbl PO_TBL_NUMBER;
207 BEGIN
208
209 l_progress := '000';
210
211 x_return_status := FND_API.G_RET_STS_SUCCESS;
212
213 IF (p_po_lower_limit = -1) THEN
214 RETURN;
215 END IF;
216
217 --SQL What: This cursor will divide po_purge_po_list into groups of
218 -- records with size p_range_size. Each fetch will return the
219 -- highest req id of that group
220 --SQL Why: We want to delete data in smaller groups to avoid running
221 -- out of rollback segments
222
223 SELECT PPL2.po_header_id
224 BULK COLLECT INTO l_ids_tbl
225 FROM (SELECT PPL.po_header_id po_header_id,
226 MOD(ROWNUM, p_range_size) mod_result
227 FROM po_purge_po_list PPL
228 WHERE PPL.double_check_flag = 'Y'
229 ORDER BY PPL.po_header_id) PPL2
230 WHERE PPL2.mod_result = 0;
231
232 l_progress := '010';
233
234 l_range_low := p_po_lower_limit;
235
236 FOR i IN 0..l_ids_tbl.COUNT LOOP
237
238 IF i = l_ids_tbl.COUNT THEN
239 l_range_high := p_po_upper_limit;
240 ELSE
241 l_range_high := l_ids_tbl(i+1);
242 END IF;
243
244 l_progress := '020';
245
246 IF (g_fnd_debug = 'Y') THEN
247 asn_debug.put_line('Begin deleting rcv. low = ' || l_range_low ||' high = ' || l_range_high);
248 END IF;
249
250 --SQL What: Delete records from rcv_shipment_lines if the pos
251 -- they associate with are in the purge list
252 DELETE
253 FROM rcv_shipment_lines RSL
254 WHERE EXISTS (
255 SELECT NULL
256 FROM po_purge_po_list PPL
257 WHERE PPL.po_header_id = RSL.po_header_id
258 AND PPL.double_check_flag = 'Y'
259 AND PPL.po_header_id BETWEEN l_range_low
260 AND l_range_high);
261
262 l_progress := '030';
263
264 --SQL What: Delete records from rcv_shipment_headers if all the
265 -- shipment lines for these headers have been deleted, and
266 -- there is not pending transaction for these headers. The
267 -- returning clause will collect all shipment headers that
268 -- have been deleted so that we can notify the same to MRC
269 -- in an MRC call out later in this procedure
270 DELETE
271 FROM rcv_shipment_headers RSH
272 WHERE NOT EXISTS (
273 SELECT NULL
274 FROM rcv_shipment_lines RSL
275 WHERE RSL.shipment_header_id =
276 RSH.shipment_header_id)
277 AND NOT EXISTS (
278 SELECT NULL
279 FROM rcv_transactions_interface RTI
280 WHERE RTI.shipment_header_id = RSH.shipment_header_id
281 AND RTI.processing_status_code <> 'COMPLETED')
282 RETURNING RSH.shipment_header_id
283 BULK COLLECT INTO l_rsh_ids_tbl;
284
285 IF (g_fnd_debug = 'Y') THEN
286 asn_debug.put_line('Deleted ' || l_rsh_ids_tbl.COUNT || ' rsh records');
287 END IF;
288
289 l_progress := '040';
290
291 --SQL What: Delete rcv_transactions data
292 DELETE
293 FROM rcv_transactions RT
294 WHERE EXISTS (
295 SELECT NULL
296 FROM po_purge_po_list PPL
297 WHERE PPL.po_header_id = RT.po_header_id
298 AND PPL.double_check_flag = 'Y'
299 AND PPL.po_header_id BETWEEN l_range_low
300 AND l_range_high)
301 RETURNING RT.transaction_id, RT.interface_transaction_id
302 BULK COLLECT INTO l_rt_ids_tbl, l_rti_ids_tbl;
303
304 IF (g_fnd_debug = 'Y') THEN
305 asn_debug.put_line('Deleted ' || l_rt_ids_tbl.COUNT || ' rt records');
306 END IF;
307
308 l_progress := '050';
309
310 -- add a costing API call out to delete
311 -- from rcv_sub_ledger_details and rcv_receiving_sub_ledger
312 delete_costing_data
313 ( x_return_status => l_return_status,
314 p_rt_ids_tbl => l_rt_ids_tbl
315 );
316
317 l_progress := '060';
318
319 --SQL What: Delete rcv_lots_supply data
320 DELETE
321 FROM rcv_lots_supply RLS
322 WHERE (RLS.shipment_line_id > 0
323 AND
324 NOT EXISTS (
325 SELECT NULL
326 FROM rcv_shipment_lines RSL
327 WHERE RSL.shipment_line_id = RLS.shipment_line_id))
328 OR (RLS.transaction_id > 0
329 AND
330 NOT EXISTS (
331 SELECT NULL
332 FROM rcv_transactions RT
333 WHERE RT.transaction_id = RLS.transaction_id));
334
335 l_progress := '070';
336
337 --SQL What: Delete rcv_serials_supply data
338 DELETE
339 FROM rcv_serials_supply RSS
340 WHERE (RSS.shipment_line_id > 0
341 AND
342 NOT EXISTS (
343 SELECT NULL
344 FROM rcv_shipment_lines RSL
345 WHERE RSL.shipment_line_id = RSS.shipment_line_id))
346 OR (RSS.transaction_id > 0
347 AND
348 NOT EXISTS (
349 SELECT NULL
350 FROM rcv_transactions RT
351 WHERE RT.transaction_id = RSS.transaction_id));
352
353 l_progress := '080';
354
355 --SQL What: Delete rcv_lot_transactions data
356 DELETE
357 FROM rcv_lot_transactions RLT
358 WHERE (RLT.shipment_line_id > 0
359 AND
360 NOT EXISTS (
361 SELECT NULL
362 FROM rcv_shipment_lines RSL
363 WHERE RSL.shipment_line_id = RLT.shipment_line_id))
364 OR
365 (RLT.transaction_id > 0
366 AND
367 NOT EXISTS (
368 SELECT NULL
369 FROM rcv_transactions RT
370 WHERE RT.transaction_id = RLT.transaction_id));
371
372 l_progress := '090';
373
374 --SQL What: Delete rcv_serial_transactions data
375 DELETE
376 FROM rcv_serial_transactions RST
377 WHERE (RST.shipment_line_id > 0
378 AND
379 NOT EXISTS (
380 SELECT NULL
381 FROM rcv_shipment_lines RSL
382 WHERE RSL.shipment_line_id = RST.shipment_line_id))
383 OR
384 (RST.transaction_id > 0
385 AND
386 NOT EXISTS (
387 SELECT NULL
388 FROM rcv_transactions RT
389 WHERE RT.transaction_id = RST.transaction_id));
390
391 l_progress := '100';
392
393 delete_rcv_attachments
394 ( x_return_status => l_return_status,
395 p_rsh_ids_tbl => l_rsh_ids_tbl,
396 p_rti_ids_tbl => l_rti_ids_tbl
397 );
398
399 COMMIT;
400
401 l_range_low := l_range_high + 1;
402
403 IF (l_range_low > p_po_upper_limit) THEN
404 EXIT;
405 END IF;
406
407 END LOOP;
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412 FND_MSG_PUB.add_exc_msg
413 ( p_pkg_name => g_pkg_name,
414 p_procedure_name => l_api_name || '.' || l_progress
415 );
416
417 END delete_receipts;
418
419
420
421 --*********************************************************************
422 -------------------------- Private Procedures -------------------------
423 --*********************************************************************
424
425 -----------------------------------------------------------------------
426 --Start of Comments
427 --Name: delete_costing_data
428 --Pre-reqs:
429 --Modifies:
430 --Locks:
431 -- None
432 --Function:
433 -- Call Costing API to delete records from sub ledger tables where receiving
434 -- transactions are purged
435 --Parameters:
436 --IN:
437 --p_rt_ids_tbl
438 -- list of rcv_transactions that have been purged
439 --IN OUT:
440 --OUT:
441 --x_return_status
442 -- status of the procedure
443 --Returns:
444 --Notes:
445 --Testing:
446 --End of Comments
447 ------------------------------------------------------------------------
448
449 PROCEDURE delete_costing_data
450 ( x_return_status OUT NOCOPY VARCHAR2,
451 p_rt_ids_tbl IN PO_TBL_NUMBER
452 ) IS
453
454 l_api_name VARCHAR2(50) := 'delete_costing_data';
455 l_progress VARCHAR2(3);
456
457 l_cst_purge_in_rec RCV_AccrualUtilities_GRP.purge_in_rectype;
458
459 l_msg_count NUMBER;
460 l_msg_data VARCHAR2(2000);
461 l_return_status VARCHAR2(1);
462 BEGIN
463
464 l_progress := '000';
465
466 x_return_status := FND_API.G_RET_STS_SUCCESS;
467
468 -- Copy the ids to a structure that can be passed to costing API
469
470 FOR i IN 1..p_rt_ids_tbl.COUNT LOOP
471
472 l_cst_purge_in_rec.entity_ids(i) := p_rt_ids_tbl(i);
473
474 END LOOP;
475
476 l_progress := '010';
477
478 RCV_AccrualUtilities_GRP.purge
479 ( p_api_version => 1.0,
480 p_init_msg_list => FND_API.G_TRUE,
481 p_commit => FND_API.G_FALSE,
482 x_return_status => l_return_status,
483 x_msg_count => l_msg_count,
484 x_msg_data => l_msg_data,
485 p_purge_entity_type => 'RCV_TRANSACTIONS',
486 p_purge_in_rec => l_cst_purge_in_rec
487 );
488
489 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491 END IF;
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 FND_MSG_PUB.add_exc_msg
497 ( p_pkg_name => g_pkg_name,
498 p_procedure_name => l_api_name || '.' || l_progress
499 );
500
501 END delete_costing_data;
502
503 -----------------------------------------------------------------------
504 --Start of Comments
505 --Name: delete_rcv_attachments
506 --Pre-reqs:
507 --Modifies:
508 --Locks:
509 -- None
510 --Function:
511 -- Delete attachments that are associated to the purged receiving records
512 --Parameters:
513 --IN:
514 --p_rsh_ids_tbl
515 -- list of shipment headers that have been purged
516 --p_rt_ids_tbl
517 -- list of interface_transaction_id listed in rcv_transaction records that
518 -- have been purged
519 --IN OUT:
520 --OUT:
521 --x_return_status
522 -- status of the procedure
523 --Returns:
524 --Notes:
525 --Testing:
526 --End of Comments
527 ------------------------------------------------------------------------
528
529 PROCEDURE delete_rcv_attachments
530 ( x_return_status OUT NOCOPY VARCHAR2,
531 p_rsh_ids_tbl IN PO_TBL_NUMBER,
532 p_rti_ids_tbl IN PO_TBL_NUMBER
533 ) IS
534
535 l_api_name VARCHAR2(50) := 'delete_rcv_attachments';
536 l_progress VARCHAR2(3);
537
538 BEGIN
539
540 l_progress := '000';
541
542 IF (g_fnd_debug = 'Y') THEN
543 asn_debug.put_line('deleting rcv attachments');
544 END IF;
545
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547
548 FOR i IN 1..p_rsh_ids_tbl.COUNT LOOP
549 l_progress := '010';
550
551 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
552 ( x_entity_name => 'RCV_HEADERS',
553 x_pk1_value => p_rsh_ids_tbl(i),
554 x_delete_document_flag => 'Y'
555 );
556
557 END LOOP;
558
559 l_progress := '020';
560
561 FOR i IN 1..p_rti_ids_tbl.COUNT LOOP
562 l_progress := '030';
563
564 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
565 ( x_entity_name => 'RCV_TRANSACTIONS_INTERFACE',
566 x_pk1_value => p_rti_ids_tbl(i),
567 x_delete_document_flag => 'Y'
568 );
569
570 END LOOP;
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 FND_MSG_PUB.add_exc_msg
576 ( p_pkg_name => g_pkg_name,
577 p_procedure_name => l_api_name || '.' || l_progress
578 );
579
580 END delete_rcv_attachments;
581
582 -- <DOC PURGE FPJ END>
583
584 END RCV_AP_PURGE_PVT;
[Home] [Help]