The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_costing_data
( x_return_status OUT NOCOPY VARCHAR2,
p_rt_ids_tbl IN PO_TBL_NUMBER
);
PROCEDURE delete_rcv_attachments
( x_return_status OUT NOCOPY VARCHAR2,
p_rsh_ids_tbl IN PO_TBL_NUMBER,
p_rti_ids_tbl IN PO_TBL_NUMBER
);
asn_debug.put_line('Before insert into phr. low = ' || l_range_low || ' high = ' || l_range_high);
INSERT INTO po_history_receipts
( receipt_num,
shipment_num,
transaction_date,
vendor_id,
receiver_name,
item_description,
purge_name
)
SELECT NVL(RSH.receipt_num, -1),
RSH.shipment_num,
RSH.shipped_date,
RSH.vendor_id,
PAPF.full_name,
RSL.item_description,
p_purge_name
FROM per_all_people_f PAPF,
rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
po_purge_po_list PPL
WHERE PPL.double_check_flag = 'Y'
AND PPL.po_header_id = RSL.po_header_id
AND PPL.po_header_id BETWEEN l_range_low AND l_range_high
AND RSL.shipment_header_id = RSH.shipment_header_id
AND RSL.employee_id = PAPF.person_id (+)
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF.effective_start_date,
TRUNC(SYSDATE))
AND NVL(PAPF.effective_end_date,
TRUNC(SYSDATE));
PROCEDURE delete_receipts
( x_return_status OUT NOCOPY VARCHAR2,
p_range_size IN NUMBER,
p_po_lower_limit IN NUMBER,
p_po_upper_limit IN NUMBER
) IS
TYPE num_tbltyp IS TABLE OF NUMBER;
l_api_name VARCHAR2(50) := 'delete_receipts';
SELECT PPL2.po_header_id
BULK COLLECT INTO l_ids_tbl
FROM (SELECT PPL.po_header_id po_header_id,
MOD(ROWNUM, p_range_size) mod_result
FROM po_purge_po_list PPL
WHERE PPL.double_check_flag = 'Y'
ORDER BY PPL.po_header_id) PPL2
WHERE PPL2.mod_result = 0;
DELETE
FROM rcv_shipment_lines RSL
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = RSL.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
DELETE
FROM rcv_shipment_headers RSH
WHERE NOT EXISTS (
SELECT NULL
FROM rcv_shipment_lines RSL
WHERE RSL.shipment_header_id =
RSH.shipment_header_id)
AND NOT EXISTS (
SELECT NULL
FROM rcv_transactions_interface RTI
WHERE RTI.shipment_header_id = RSH.shipment_header_id
AND RTI.processing_status_code <> 'COMPLETED')
RETURNING RSH.shipment_header_id
BULK COLLECT INTO l_rsh_ids_tbl;
asn_debug.put_line('Deleted ' || l_rsh_ids_tbl.COUNT || ' rsh records');
DELETE
FROM rcv_transactions RT
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = RT.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high)
RETURNING RT.transaction_id, RT.interface_transaction_id
BULK COLLECT INTO l_rt_ids_tbl, l_rti_ids_tbl;
asn_debug.put_line('Deleted ' || l_rt_ids_tbl.COUNT || ' rt records');
delete_costing_data
( x_return_status => l_return_status,
p_rt_ids_tbl => l_rt_ids_tbl
);
DELETE
FROM rcv_lots_supply RLS
WHERE (RLS.shipment_line_id > 0
AND
NOT EXISTS (
SELECT NULL
FROM rcv_shipment_lines RSL
WHERE RSL.shipment_line_id = RLS.shipment_line_id))
OR (RLS.transaction_id > 0
AND
NOT EXISTS (
SELECT NULL
FROM rcv_transactions RT
WHERE RT.transaction_id = RLS.transaction_id));
DELETE
FROM rcv_serials_supply RSS
WHERE (RSS.shipment_line_id > 0
AND
NOT EXISTS (
SELECT NULL
FROM rcv_shipment_lines RSL
WHERE RSL.shipment_line_id = RSS.shipment_line_id))
OR (RSS.transaction_id > 0
AND
NOT EXISTS (
SELECT NULL
FROM rcv_transactions RT
WHERE RT.transaction_id = RSS.transaction_id));
DELETE
FROM rcv_lot_transactions RLT
WHERE (RLT.shipment_line_id > 0
AND
NOT EXISTS (
SELECT NULL
FROM rcv_shipment_lines RSL
WHERE RSL.shipment_line_id = RLT.shipment_line_id))
OR
(RLT.transaction_id > 0
AND
NOT EXISTS (
SELECT NULL
FROM rcv_transactions RT
WHERE RT.transaction_id = RLT.transaction_id));
DELETE
FROM rcv_serial_transactions RST
WHERE (RST.shipment_line_id > 0
AND
NOT EXISTS (
SELECT NULL
FROM rcv_shipment_lines RSL
WHERE RSL.shipment_line_id = RST.shipment_line_id))
OR
(RST.transaction_id > 0
AND
NOT EXISTS (
SELECT NULL
FROM rcv_transactions RT
WHERE RT.transaction_id = RST.transaction_id));
delete_rcv_attachments
( x_return_status => l_return_status,
p_rsh_ids_tbl => l_rsh_ids_tbl,
p_rti_ids_tbl => l_rti_ids_tbl
);
END delete_receipts;
PROCEDURE delete_costing_data
( x_return_status OUT NOCOPY VARCHAR2,
p_rt_ids_tbl IN PO_TBL_NUMBER
) IS
l_api_name VARCHAR2(50) := 'delete_costing_data';
END delete_costing_data;
PROCEDURE delete_rcv_attachments
( x_return_status OUT NOCOPY VARCHAR2,
p_rsh_ids_tbl IN PO_TBL_NUMBER,
p_rti_ids_tbl IN PO_TBL_NUMBER
) IS
l_api_name VARCHAR2(50) := 'delete_rcv_attachments';
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
( x_entity_name => 'RCV_HEADERS',
x_pk1_value => p_rsh_ids_tbl(i),
x_delete_document_flag => 'Y'
);
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
( x_entity_name => 'RCV_TRANSACTIONS_INTERFACE',
x_pk1_value => p_rti_ids_tbl(i),
x_delete_document_flag => 'Y'
);
END delete_rcv_attachments;