1 PACKAGE BODY RCV_AP_PURGE_PVT AS
2 /* $Header: RCVVPUDB.pls 120.2 2011/07/06 19:36:21 vthevark ship $ */
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 SELECT RT.transaction_id, RT.interface_transaction_id
292 BULK COLLECT INTO l_rt_ids_tbl, l_rti_ids_tbl
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
302 l_progress := '050';
303
304 -- add a costing API call out to delete
305 -- from rcv_sub_ledger_details and rcv_receiving_sub_ledger
306 delete_costing_data
307 ( x_return_status => l_return_status,
308 p_rt_ids_tbl => l_rt_ids_tbl
309 );
310
311 --SQL What: Delete rcv_transactions data
312 FORALL i IN l_rt_ids_tbl.FIRST..l_rt_ids_tbl.LAST
313 DELETE
314 FROM rcv_transactions RT
315 WHERE transaction_id = l_rt_ids_tbl(i);
316
317 IF (g_fnd_debug = 'Y') THEN
318 asn_debug.put_line('Deleted ' || l_rt_ids_tbl.COUNT || ' rt records');
319 END IF;
320
321 l_progress := '060';
322
323 --SQL What: Delete rcv_lots_supply data
324 DELETE
325 FROM rcv_lots_supply RLS
326 WHERE (RLS.shipment_line_id > 0
327 AND
328 NOT EXISTS (
329 SELECT NULL
330 FROM rcv_shipment_lines RSL
331 WHERE RSL.shipment_line_id = RLS.shipment_line_id))
332 OR (RLS.transaction_id > 0
333 AND
334 NOT EXISTS (
335 SELECT NULL
336 FROM rcv_transactions RT
337 WHERE RT.transaction_id = RLS.transaction_id));
338
339 l_progress := '070';
340
341 --SQL What: Delete rcv_serials_supply data
342 DELETE
343 FROM rcv_serials_supply RSS
344 WHERE (RSS.shipment_line_id > 0
345 AND
346 NOT EXISTS (
347 SELECT NULL
348 FROM rcv_shipment_lines RSL
349 WHERE RSL.shipment_line_id = RSS.shipment_line_id))
350 OR (RSS.transaction_id > 0
351 AND
352 NOT EXISTS (
353 SELECT NULL
354 FROM rcv_transactions RT
355 WHERE RT.transaction_id = RSS.transaction_id));
356
357 l_progress := '080';
358
359 --SQL What: Delete rcv_lot_transactions data
360 DELETE
361 FROM rcv_lot_transactions RLT
362 WHERE (RLT.shipment_line_id > 0
363 AND
364 NOT EXISTS (
365 SELECT NULL
366 FROM rcv_shipment_lines RSL
367 WHERE RSL.shipment_line_id = RLT.shipment_line_id))
368 OR
369 (RLT.transaction_id > 0
370 AND
371 NOT EXISTS (
372 SELECT NULL
373 FROM rcv_transactions RT
374 WHERE RT.transaction_id = RLT.transaction_id));
375
376 l_progress := '090';
377
378 --SQL What: Delete rcv_serial_transactions data
379 DELETE
380 FROM rcv_serial_transactions RST
381 WHERE (RST.shipment_line_id > 0
382 AND
383 NOT EXISTS (
384 SELECT NULL
385 FROM rcv_shipment_lines RSL
386 WHERE RSL.shipment_line_id = RST.shipment_line_id))
387 OR
388 (RST.transaction_id > 0
389 AND
390 NOT EXISTS (
391 SELECT NULL
392 FROM rcv_transactions RT
393 WHERE RT.transaction_id = RST.transaction_id));
394
395 l_progress := '100';
396
397 delete_rcv_attachments
398 ( x_return_status => l_return_status,
399 p_rsh_ids_tbl => l_rsh_ids_tbl,
400 p_rti_ids_tbl => l_rti_ids_tbl
401 );
402
403 COMMIT;
404
405 l_range_low := l_range_high + 1;
406
407 IF (l_range_low > p_po_upper_limit) THEN
408 EXIT;
409 END IF;
410
411 END LOOP;
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 FND_MSG_PUB.add_exc_msg
417 ( p_pkg_name => g_pkg_name,
418 p_procedure_name => l_api_name || '.' || l_progress
419 );
420
421 END delete_receipts;
422
423
424
425 --*********************************************************************
426 -------------------------- Private Procedures -------------------------
427 --*********************************************************************
428
429 -----------------------------------------------------------------------
430 --Start of Comments
431 --Name: delete_costing_data
432 --Pre-reqs:
433 --Modifies:
434 --Locks:
435 -- None
436 --Function:
437 -- Call Costing API to delete records from sub ledger tables where receiving
438 -- transactions are purged
439 --Parameters:
440 --IN:
441 --p_rt_ids_tbl
442 -- list of rcv_transactions that have been purged
443 --IN OUT:
444 --OUT:
445 --x_return_status
446 -- status of the procedure
447 --Returns:
448 --Notes:
449 --Testing:
450 --End of Comments
451 ------------------------------------------------------------------------
452
453 PROCEDURE delete_costing_data
454 ( x_return_status OUT NOCOPY VARCHAR2,
455 p_rt_ids_tbl IN PO_TBL_NUMBER
456 ) IS
457
458 l_api_name VARCHAR2(50) := 'delete_costing_data';
459 l_progress VARCHAR2(3);
460
461 l_cst_purge_in_rec RCV_AccrualUtilities_GRP.purge_in_rectype;
462
463 l_msg_count NUMBER;
464 l_msg_data VARCHAR2(2000);
465 l_return_status VARCHAR2(1);
466 BEGIN
467
468 l_progress := '000';
469
470 x_return_status := FND_API.G_RET_STS_SUCCESS;
471
472 -- Copy the ids to a structure that can be passed to costing API
473
474 FOR i IN 1..p_rt_ids_tbl.COUNT LOOP
475
476 l_cst_purge_in_rec.entity_ids(i) := p_rt_ids_tbl(i);
477
478 END LOOP;
479
480 l_progress := '010';
481
482 RCV_AccrualUtilities_GRP.purge
483 ( p_api_version => 1.0,
484 p_init_msg_list => FND_API.G_TRUE,
485 p_commit => FND_API.G_FALSE,
486 x_return_status => l_return_status,
487 x_msg_count => l_msg_count,
488 x_msg_data => l_msg_data,
489 p_purge_entity_type => 'RCV_TRANSACTIONS',
490 p_purge_in_rec => l_cst_purge_in_rec
491 );
492
493 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 END IF;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
500 FND_MSG_PUB.add_exc_msg
501 ( p_pkg_name => g_pkg_name,
502 p_procedure_name => l_api_name || '.' || l_progress
503 );
504
505 END delete_costing_data;
506
507 -----------------------------------------------------------------------
508 --Start of Comments
509 --Name: delete_rcv_attachments
510 --Pre-reqs:
511 --Modifies:
512 --Locks:
513 -- None
514 --Function:
515 -- Delete attachments that are associated to the purged receiving records
516 --Parameters:
517 --IN:
518 --p_rsh_ids_tbl
519 -- list of shipment headers that have been purged
520 --p_rt_ids_tbl
521 -- list of interface_transaction_id listed in rcv_transaction records that
522 -- have been purged
523 --IN OUT:
524 --OUT:
525 --x_return_status
526 -- status of the procedure
527 --Returns:
528 --Notes:
529 --Testing:
530 --End of Comments
531 ------------------------------------------------------------------------
532
533 PROCEDURE delete_rcv_attachments
534 ( x_return_status OUT NOCOPY VARCHAR2,
535 p_rsh_ids_tbl IN PO_TBL_NUMBER,
536 p_rti_ids_tbl IN PO_TBL_NUMBER
537 ) IS
538
539 l_api_name VARCHAR2(50) := 'delete_rcv_attachments';
540 l_progress VARCHAR2(3);
541
542 BEGIN
543
544 l_progress := '000';
545
546 IF (g_fnd_debug = 'Y') THEN
547 asn_debug.put_line('deleting rcv attachments');
548 END IF;
549
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551
552 FOR i IN 1..p_rsh_ids_tbl.COUNT LOOP
553 l_progress := '010';
554
555 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
556 ( x_entity_name => 'RCV_HEADERS',
557 x_pk1_value => p_rsh_ids_tbl(i),
558 x_delete_document_flag => 'Y'
559 );
560
561 END LOOP;
562
563 l_progress := '020';
564
565 FOR i IN 1..p_rti_ids_tbl.COUNT LOOP
566 l_progress := '030';
567
568 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
569 ( x_entity_name => 'RCV_TRANSACTIONS_INTERFACE',
570 x_pk1_value => p_rti_ids_tbl(i),
571 x_delete_document_flag => 'Y'
572 );
573
574 END LOOP;
575
576 EXCEPTION
577 WHEN OTHERS THEN
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
579 FND_MSG_PUB.add_exc_msg
580 ( p_pkg_name => g_pkg_name,
581 p_procedure_name => l_api_name || '.' || l_progress
582 );
583
584 END delete_rcv_attachments;
585
586 -- <DOC PURGE FPJ END>
587
588 END RCV_AP_PURGE_PVT;
[Home] [Help]