[Home] [Help]
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
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
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:
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);
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)