[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_reqs
( x_return_status OUT NOCOPY VARCHAR2,
p_range_size IN NUMBER,
p_req_lower_limit IN NUMBER,
p_req_upper_limit IN NUMBER
);
PROCEDURE delete_pos
( x_return_status OUT NOCOPY VARCHAR2,
p_range_size IN NUMBER,
p_po_lower_limit IN NUMBER,
p_po_upper_limit IN NUMBER
);
PROCEDURE delete_records
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
p_purge_name IN VARCHAR2,
p_purge_category IN VARCHAR2,
p_range_size IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(50) := 'delete_records';
p_error_text => 'Internal Error. delete_records must commit'
);
delete_reqs
( x_return_status => l_return_status,
p_range_size => p_range_size,
p_req_lower_limit => l_req_lower_limit,
p_req_upper_limit => l_req_upper_limit
);
delete_pos
( x_return_status => l_return_status,
p_range_size => p_range_size,
p_po_lower_limit => l_po_lower_limit,
p_po_upper_limit => l_po_upper_limit
);
RCV_AP_PURGE_PVT.delete_receipts
( x_return_status => l_return_status,
p_range_size => p_range_size,
p_po_lower_limit => l_po_lower_limit,
p_po_upper_limit => l_po_upper_limit
);
END delete_records;
PROCEDURE delete_purge_lists
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
p_purge_name IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(50) := 'delete_purge_lists';
SAVEPOINT delete_purge_lists_pvt;
DELETE
FROM po_purge_req_list;
DELETE
FROM po_purge_po_list;
ROLLBACK TO delete_purge_lists_pvt;
END delete_purge_lists;
PROCEDURE delete_history_tables
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
p_purge_name IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(50) := 'delete_history_tables';
SAVEPOINT delete_history_tables_pvt;
DELETE
FROM po_history_pos
WHERE purge_name = p_purge_name;
DELETE
FROM po_history_requisitions
WHERE purge_name = p_purge_name;
DELETE
FROM po_history_receipts
WHERE purge_name = p_purge_name;
ROLLBACK TO delete_history_tables_pvt;
END delete_history_tables;
SELECT COUNT(1)
INTO x_po_hdr_count
FROM po_headers;
SELECT COUNT(1)
INTO x_rcv_line_count
FROM rcv_shipment_lines;
SELECT COUNT(1)
INTO x_req_hdr_count
FROM po_requisition_headers;
SELECT COUNT(1)
INTO x_vendor_count
FROM po_vendors;
SELECT COUNT(1)
INTO x_asl_count
FROM po_approved_supplier_list;
SELECT COUNT(1)
INTO x_asl_attr_count
FROM po_asl_attributes;
SELECT COUNT(1)
INTO x_asl_doc_count
FROM po_asl_documents;
INSERT INTO po_purge_req_list
( requisition_header_id,
purge_name,
double_check_flag
)
SELECT PRH.requisition_header_id,
p_purge_name,
'Y'
FROM po_requisition_headers PRH
WHERE PRH.last_update_date <= p_last_activity_date
AND (PRH.closed_code = 'FINALLY CLOSED'
OR PRH.authorization_status = 'CANCELLED')
AND NOT EXISTS
(SELECT NULL
FROM po_requisition_lines PRL
WHERE PRL.requisition_header_id = PRH.requisition_header_id
AND NVL(PRL.modified_by_agent_flag, 'N') = 'N'
AND (PRL.last_update_date > p_last_activity_date
OR
PRL.line_location_id IS NOT NULL
OR
PRL.source_type_code = 'INVENTORY'
OR
EXISTS (
SELECT NULL
FROM po_price_differentials PPD
WHERE PPD.entity_type = 'REQ LINE'
AND PPD.entity_id = PRL.requisition_line_id
AND PPD.last_update_date >
p_last_activity_date)
OR
EXISTS (
SELECT NULL
FROM po_req_distributions PRD
WHERE PRD.requisition_line_id =
PRL.requisition_line_id
AND PRD.last_update_date >
p_last_activity_date)));
message => 'Inserted ' || SQL%ROWCOUNT || ' Reqs to purge list'
);
INSERT INTO po_purge_po_list
( po_header_id,
purge_name,
double_check_flag
)
SELECT PH.po_header_id,
p_purge_name,
'Y'
FROM po_headers PH
WHERE PH.type_lookup_code IN ('STANDARD', 'PLANNED',
'BLANKET', 'CONTRACT')
AND PH.last_update_date <= p_last_activity_date
AND (PH.closed_code = 'FINALLY CLOSED'
OR PH.cancel_flag = 'Y')
AND NOT EXISTS
(SELECT NULL
FROM po_releases PR
WHERE PR.po_header_id = PH.po_header_id
AND PR.last_update_date > p_last_activity_date)
AND NOT EXISTS
(SELECT NULL
FROM po_lines PL
WHERE PL.po_header_id = PH.po_header_id
AND PL.last_update_date > p_last_activity_date)
AND NOT EXISTS
(SELECT NULL
FROM po_line_locations PLL
WHERE PLL.po_header_id = PH.po_header_id
AND PLL.last_update_date > p_last_activity_date)
AND NOT EXISTS
(SELECT NULL
FROM po_distributions PD
WHERE PD.po_header_id = PH.po_header_id
AND (PD.last_update_date > p_last_activity_date
OR
EXISTS
(SELECT NULL
FROM ap_invoice_distributions AD
WHERE AD.po_distribution_id =
PD.po_distribution_id)))
AND NOT EXISTS
(SELECT NULL
FROM rcv_transactions RT
WHERE RT.po_header_id = PH.po_header_id
AND RT.last_update_date > p_last_activity_date)
AND PO_AP_PURGE_GRP.validate_purge(PH.po_header_id) = 'T';
INSERT INTO po_purge_po_list
( po_header_id,
purge_name,
double_check_flag
)
SELECT PH.po_header_id,
p_purge_name,
'Y'
FROM po_headers PH
WHERE PH.type_lookup_code IN ('STANDARD', 'PLANNED',
'BLANKET', 'CONTRACT')
AND PH.last_update_date <= p_last_activity_date
AND (PH.closed_code = 'FINALLY CLOSED'
OR PH.cancel_flag = 'Y')
AND NOT EXISTS
(SELECT NULL
FROM po_releases PR
WHERE PR.po_header_id = PH.po_header_id
AND PR.last_update_date > p_last_activity_date)
AND NOT EXISTS
(SELECT NULL
FROM po_lines PL
WHERE PL.po_header_id = PH.po_header_id
AND PL.last_update_date > p_last_activity_date)
AND NOT EXISTS
(SELECT NULL
FROM po_line_locations PLL
WHERE PLL.po_header_id = PH.po_header_id
AND PLL.last_update_date > p_last_activity_date)
AND NOT EXISTS
(SELECT NULL
FROM po_distributions PD
WHERE PD.po_header_id = PH.po_header_id
AND PD.last_update_date > p_last_activity_date)
AND NOT EXISTS
(SELECT NULL
FROM rcv_transactions RT
WHERE RT.po_header_id = PH.po_header_id
AND RT.last_update_date > p_last_activity_date)
AND PO_AP_PURGE_GRP.validate_purge(PH.po_header_id) = 'T';
message => 'inserted ' || SQL%ROWCOUNT || ' POs to purge'
|| ' list. purge_cat = ' || p_purge_category
);
INSERT INTO po_purge_req_list
( requisition_header_id,
purge_name,
double_check_flag
)
SELECT PRH.requisition_header_id,
p_purge_name,
'Y'
FROM po_requisition_headers PRH
WHERE PRH.last_update_date <= p_last_activity_date
AND (PRH.closed_code = 'FINALLY CLOSED'
OR PRH.authorization_status = 'CANCELLED')
AND NOT EXISTS
(SELECT NULL
FROM po_requisition_lines PRL
WHERE PRL.requisition_header_id =
PRH.requisition_header_id
AND NVL(PRL.modified_by_agent_flag, 'N') = 'N'
AND (PRL.last_update_date > p_last_activity_date
OR
PRL.source_type_code = 'INVENTORY'
OR
EXISTS (
SELECT NULL
FROM po_req_distributions PRD
WHERE PRD.requisition_line_id =
PRL.requisition_line_id
AND PRD.last_update_date >
p_last_activity_date)));
message => 'inserted ' || SQL%ROWCOUNT || ' REQs to purge list'
);
UPDATE po_purge_req_list PPRL
SET PPRL.double_check_flag = 'N'
WHERE PPRL.double_check_flag = 'Y'
AND EXISTS
(SELECT NULL
FROM chv_item_orders CIO
WHERE CIO.document_header_id = PPRL.requisition_header_id
AND CIO.supply_document_type = 'REQUISITION');
message => 'Check CHV. Updated rowcount = ' || SQL%ROWCOUNT
);
UPDATE po_purge_req_list PPRL
SET PPRL.double_check_flag = 'N'
WHERE PPRL.double_check_flag = 'Y'
AND EXISTS
(SELECT NULL
FROM po_req_distributions RD,
po_requisition_lines RL
WHERE RL.requisition_header_id =
PPRL.requisition_header_id
AND RD.requisition_line_id =
RL.requisition_line_id
AND RD.project_id IS NOT NULL
AND RL.destination_type_code = 'EXPENSE');
'Updated rowcount = ' || SQL%ROWCOUNT
);
UPDATE po_purge_req_list PPRL
SET PPRL.double_check_flag = 'N'
WHERE PPRL.double_check_flag = 'Y'
AND EXISTS
(SELECT NULL
FROM po_req_distributions RD,
po_requisition_lines RL
WHERE RL.requisition_header_id =
PPRL.requisition_header_id
AND RD.requisition_line_id =
RL.requisition_line_id
AND RD.project_id IS NOT NULL
AND RL.destination_type_code IN ('INVENTORY',
'SHOP FLOOR'));
'Updated rowcount = ' || SQL%ROWCOUNT
);
UPDATE po_purge_po_list PPL
SET PPL.double_check_flag = 'N'
WHERE PPL.double_check_flag = 'Y'
AND EXISTS (
SELECT NULL
FROM mtl_material_transactions MMT
WHERE MMT.transaction_source_type_id = 1
AND MMT.transaction_source_id = PPL.po_header_id);
'Updated rowcount = ' || SQL%ROWCOUNT
);
UPDATE po_purge_po_list PPL
SET PPL.double_check_flag = 'N'
WHERE PPL.double_check_flag = 'Y'
AND EXISTS (
SELECT NULL
FROM wip_transactions WT
WHERE WT.po_header_id = PPL.po_header_id);
'Updated rowcount = ' || SQL%ROWCOUNT
);
UPDATE po_purge_po_list PPL
SET PPL.double_check_flag = 'N'
WHERE PPL.double_check_flag = 'Y'
AND EXISTS (
SELECT NULL
FROM mrp_schedule_consumptions MSC
WHERE MSC.disposition_type IN (2,6)
AND MSC.disposition_id = PPL.po_header_id);
'Updated rowcount = ' || SQL%ROWCOUNT
);
UPDATE po_purge_po_list PPL
SET PPL.double_check_flag = 'N'
WHERE PPL.double_check_flag = 'Y'
AND EXISTS(
SELECT NULL
FROM chv_item_orders CIO
WHERE CIO.document_header_id = PPL.po_header_id
AND CIO.supply_document_type = 'RELEASE');
message => 'Check CHV. Updated rowcount = ' || SQL%ROWCOUNT
);
UPDATE po_purge_po_list PPL
SET PPL.double_check_flag = 'N'
WHERE PPL.double_check_flag = 'Y'
AND EXISTS (
SELECT NULL
FROM po_distributions PD
WHERE PPL.po_header_id = PD.po_header_id
AND PD.project_id IS NOT NULL
AND PD.destination_type_code = 'EXPENSE');
'Updated rowcount = ' || SQL%ROWCOUNT
);
UPDATE po_purge_po_list PPL
SET PPL.double_check_flag = 'N'
WHERE PPL.double_check_flag = 'Y'
AND EXISTS (
SELECT NULL
FROM po_distributions PD
WHERE PPL.po_header_id = PD.po_header_id
AND PD.project_id IS NOT NULL
AND PD.destination_type_code IN ('INVENTORY',
'SHOP FLOOR'));
'Updated rowcount = ' || SQL%ROWCOUNT
);
UPDATE po_purge_po_list PPL
SET PPL.double_check_flag = 'N'
WHERE PPL.double_check_flag = 'Y'
AND (EXISTS ( -- rm po if req not in purge list
SELECT NULL
FROM po_line_locations_all PLL,
po_requisition_lines_all RL
WHERE PLL.po_header_id = PPL.po_header_id
AND PLL.line_location_id = RL.line_location_id
AND NOT EXISTS (
SELECT NULL
FROM po_purge_req_list PRL
WHERE PRL.requisition_header_id =
RL.requisition_header_id
AND PRL.double_check_flag = 'Y'))
OR
EXISTS ( -- rm po if contract not in purge list
SELECT NULL
FROM po_lines_all POL
WHERE POL.po_header_id = PPL.po_header_id
AND POL.contract_id IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM po_purge_po_list PPL1
WHERE PPL1.double_check_flag = 'Y'
AND PPL1.po_header_id =
POL.contract_id))
OR
EXISTS ( -- rm po if ga not in purge list
SELECT NULL
FROM po_lines_all POL,
po_headers_all POH
WHERE PPL.po_header_id = POL.po_header_id
AND POL.from_header_id = POH.po_header_id
AND POH.type_lookup_code = 'BLANKET'
AND POH.global_agreement_flag = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM po_purge_po_list PPL1
WHERE PPL1.double_check_flag = 'Y'
AND POH.po_header_id =
PPL1.po_Header_id))
OR
EXISTS ( -- rm contract if po not in purge list
SELECT NULL
FROM po_lines_all POL
WHERE PPL.po_header_id = POL.contract_id
AND NOT EXISTS (
SELECT NULL
FROM po_purge_po_list PPL1
WHERE PPL1.double_check_flag = 'Y'
AND POL.po_header_id =
PPL1.po_header_id))
OR
EXISTS ( -- rm ga if po not in purge list
SELECT NULL
FROM po_headers_all POH,
po_lines_all POL
WHERE POH.po_header_id = PPL.po_header_id
AND POH.type_lookup_code = 'BLANKET'
AND POH.global_agreement_flag = 'Y'
AND POL.from_header_id = POH.po_header_id
AND NOT EXISTS (
SELECT NULL
FROM po_purge_po_list PPL1
WHERE PPL1.double_check_flag = 'Y'
AND POL.po_header_id =
PPL1.po_header_id)));
UPDATE po_purge_req_list PPRL
SET PPRL.double_check_flag = 'N'
WHERE PPRL.double_check_flag = 'Y'
AND EXISTS (
SELECT NULL
FROM po_requisition_lines_all RL,
po_line_locations_all PLL
WHERE RL.requisition_header_id =
PPRL.requisition_header_id
AND RL.line_location_id = PLL.line_location_id
AND NOT EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.double_check_flag = 'Y'
AND PPL.po_header_id =
PLL.po_header_id));
message => 'Updated po_count = ' || l_po_count ||
', Updated req_count = ' || l_req_count
);
UPDATE po_purge_po_list PPL
SET PPL.double_check_flag = 'N'
WHERE PPL.double_check_flag = 'Y'
AND EXISTS (
SELECT NULL
FROM po_distributions PD,
ap_invoice_distributions AD
WHERE PD.po_header_id = PPL.po_header_id
AND AD.po_distribution_id = PD.po_distribution_id
AND NOT EXISTS (
SELECT NULL
FROM ap_purge_invoice_list APL
WHERE APL.invoice_id = AD.invoice_id));
message => 'Updated po rowcount = ' || l_po_count
);
DELETE FROM po_purge_req_list
WHERE double_check_flag = 'N';
message => 'Deleted ' || SQL%ROWCOUNT || ' reqs from purge list'
);
DELETE FROM po_purge_po_list
WHERE double_check_flag = 'N';
message => 'Deleted ' || SQL%ROWCOUNT || ' pos from purge list'
);
UPDATE po_purge_req_list PPRL
SET double_check_flag = 'N'
WHERE double_check_flag = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM po_requisition_headers RH
WHERE RH.requisition_header_id =
PPRL.requisition_header_id
AND RH.last_update_date <= p_last_activity_date
AND (RH.closed_code = 'FINALLY CLOSED'
OR RH.authorization_status = 'CANCELLED')
AND NOT EXISTS (
SELECT NULL
FROM po_requisition_lines RL
WHERE RL.requisition_header_id =
RH.requisition_header_id
AND NVL(RL.modified_by_agent_flag,'N') = 'N'
AND (RL.last_update_date > p_last_activity_date
OR
RL.line_location_id IS NOT NULL
OR
RL.source_type_code = 'INVENTORY'
OR
EXISTS (
SELECT NULL
FROM po_price_differentials PPD
WHERE PPD.entity_type = 'REQ LINE'
AND PPD.entity_id =
RL.requisition_line_id
AND PPD.last_update_date >
p_last_activity_date)
OR
EXISTS (
SELECT NULL
FROM po_req_distributions RD
WHERE RD.requisition_line_id =
RL.requisition_line_id
AND RD.last_update_date >
p_last_activity_date))));
UPDATE po_purge_po_list PPL
SET double_check_flag = 'N'
WHERE double_check_flag = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM po_headers PH
WHERE PH.po_header_id = PPL.po_header_id
AND PH.type_lookup_code IN ('STANDARD', 'BLANKET',
'PLANNED', 'CONTRACT')
AND (PH.cancel_flag = 'Y'
OR PH.closed_code = 'FINALLY CLOSED')
AND PH.last_update_date <= p_last_activity_date
AND NOT EXISTS
(SELECT NULL
FROM po_releases PR
WHERE PR.po_header_id = PPL.po_header_id
AND PR.last_update_date > p_last_activity_date)
AND NOT EXISTS
(SELECT NULL
FROM po_lines PL
WHERE PL.po_header_id = PPL.po_header_id
AND (PL.last_update_date > p_last_activity_date
OR
EXISTS
(SELECT NULL
FROM po_price_differentials PPD
WHERE PPD.entity_type IN ('PO LINE',
'BLANKET LINE')
AND PPD.entity_id = PL.po_line_id
AND PPD.last_update_date >
p_last_activity_date)))
AND NOT EXISTS
(SELECT NULL
FROM po_line_locations PLL
WHERE PLL.po_header_id = PPL.po_header_id
AND (PLL.last_update_date >p_last_activity_date
OR
EXISTS
(SELECT NULL
FROM po_price_differentials PPD
WHERE PPD.entity_type = 'PRICE BREAK'
AND PPD.entity_id =
PLL.line_location_id
AND PPD.last_update_date >
p_last_activity_date)))
AND NOT EXISTS
(SELECT NULL
FROM po_distributions PD
WHERE PD.po_header_id = PPL.po_header_id
AND (PD.last_update_date > p_last_activity_date
OR
EXISTS
(SELECT NULL
FROM ap_invoice_distributions AD
WHERE AD.po_distribution_id =
PD.po_distribution_id))));
UPDATE po_purge_po_list PPL
SET double_check_flag = 'N'
WHERE double_check_flag = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM po_headers PH
WHERE PH.po_header_id = PPL.po_header_id
AND PH.type_lookup_code IN ('STANDARD', 'BLANKET',
'PLANNED', 'CONTRACT')
AND (PH.cancel_flag = 'Y'
OR PH.closed_code = 'FINALLY CLOSED')
AND PH.last_update_date <= p_last_activity_date
AND NOT EXISTS
(SELECT NULL
FROM po_releases PR
WHERE PR.po_header_id = PH.po_header_id
AND PR.last_update_date > p_last_activity_date)
AND NOT EXISTS
(SELECT NULL
FROM po_lines PL
WHERE PL.po_header_id = PH.po_header_id
AND (PL.last_update_date > p_last_activity_date
OR
EXISTS
(SELECT NULL
FROM po_price_differentials PPD
WHERE PPD.entity_type IN ('PO LINE',
'BLANKET LINE')
AND PPD.entity_id = PL.po_line_id
AND PPD.last_update_date >
p_last_activity_date)))
AND NOT EXISTS
(SELECT NULL
FROM po_line_locations PLL
WHERE PLL.po_header_id = PH.po_header_id
AND (PLL.last_update_date > p_last_activity_date
OR
EXISTS
(SELECT NULL
FROM po_price_differentials PPD
WHERE PPD.entity_type = 'PRICE BREAK'
AND PPD.entity_id =
PLL.line_location_id
AND PPD.last_update_date >
p_last_activity_date)))
AND NOT EXISTS
(SELECT NULL
FROM po_distributions PD
WHERE PD.po_header_id = PH.po_header_id
AND PD.last_update_date >
p_last_activity_date));
UPDATE po_purge_req_list PPRL
SET double_check_flag = 'N'
WHERE double_check_flag = 'Y'
AND NOT EXISTS (
SELECT NULL
FROM po_requisition_headers RH
WHERE RH.requisition_header_id =
PPRL.requisition_header_id
AND RH.last_update_date <= p_last_activity_date
AND (RH.closed_code = 'FINALLY CLOSED'
OR RH.authorization_status = 'CANCELLED')
AND NOT EXISTS (
SELECT NULL
FROM po_requisition_lines RL
WHERE RL.requisition_header_id =
RH.requisition_header_id
AND NVL(RL.modified_by_agent_flag,'N') = 'N'
AND (RL.last_update_date > p_last_activity_date
OR
RL.source_type_code = 'INVENTORY'
OR
EXISTS (
SELECT NULL
FROM po_price_differentials PPD
WHERE PPD.entity_type = 'REQ LINE'
AND PPD.entity_id =
RL.requisition_line_id
AND PPD.last_update_date >
p_last_activity_date)
OR
EXISTS (
SELECT NULL
FROM po_req_distributions RD
WHERE RD.requisition_line_id =
RL.requisition_line_id
AND RD.last_update_date >
p_last_activity_date))));
SELECT NVL ( MIN (PPRL.requisition_header_id), -1 ),
NVL ( MAX (PPRL.requisition_header_id), -1 )
INTO x_lower_limit,
x_upper_limit
FROM po_purge_req_list PPRL
WHERE PPRL.double_check_flag = 'Y';
SELECT NVL ( MIN (PPL.po_header_id), -1 ),
NVL ( MAX (PPL.po_header_id), -1 )
INTO x_lower_limit,
x_upper_limit
FROM po_purge_po_list PPL
WHERE PPL.double_check_flag = 'Y';
l_range_inserted VARCHAR2(1);
SELECT 'Y'
INTO l_range_inserted
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_requisition_headers PRH,
po_history_requisitions PHR
WHERE PRH.segment1 = PHR.segment1
AND PRH.type_lookup_code = PHR.type_lookup_code
AND PHR.purge_name = p_purge_name
AND PRH.requisition_header_id BETWEEN l_range_low
AND l_range_high);
l_range_inserted := 'N';
message => 'range inserted = ' || l_range_inserted
);
IF (l_range_inserted = 'N') THEN
l_progress := '030';
INSERT INTO po_history_requisitions
( segment1,
type_lookup_code,
creation_date,
requisition_total,
preparer_name,
purge_name,
org_id -- bug5446437
)
SELECT RH.segment1,
RH.type_lookup_code,
RH.creation_date,
SUM( DECODE (RL.amount,
NULL, RL.quantity * RL.unit_price,
RL.amount)),
PAPF.full_name,
p_purge_name,
RH.org_id -- bug5446437
FROM po_purge_req_list PRL,
per_all_people_f PAPF,
po_requisition_headers RH,
po_requisition_lines RL
WHERE PRL.requisition_header_id = RH.requisition_header_id
AND RH.requisition_header_id = RL.requisition_header_id
AND RH.preparer_id = PAPF.person_id
AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
AND PAPF.effective_end_date
AND PRL.double_check_flag = 'Y'
AND PRL.requisition_header_id BETWEEN l_range_low
AND l_range_high
GROUP BY RH.segment1,
RH.type_lookup_code,
RH.creation_date,
PAPF.full_name,
RH.org_id; -- bug5446437
END IF; -- l_range_inserted = 'N'
l_range_inserted VARCHAR2(1);
SELECT 'Y'
INTO l_range_inserted
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_headers PH,
po_history_pos PHP
WHERE PH.segment1 = PHP.segment1
AND PH.type_lookup_code = PHP.type_lookup_code
AND PHP.purge_name = p_purge_name
AND PH.po_header_id BETWEEN l_range_low
AND l_range_high);
l_range_inserted := 'N';
message => 'range inserted = ' || l_range_inserted
);
IF (l_range_inserted = 'N') THEN -- current batch is not inserted
l_progress := '030';
INSERT INTO po_history_pos
( segment1,
type_lookup_code,
vendor_id,
vendor_site_code,
po_total,
currency_code,
agent_name,
creation_date,
purge_name,
org_id -- bug5446437
)
SELECT PH.segment1,
PH.type_lookup_code,
PH.vendor_id,
VDS.vendor_site_code,
NVL (ROUND (
SUM (
DECODE (
PLL.quantity,
NULL,
PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled,
0)) *
PLL.price_override)),
2),
0),
PH.currency_code,
PAPF.full_name,
PH.creation_date,
p_purge_name,
PH.org_id -- bug5446437
FROM per_all_people_f PAPF,
po_vendor_sites VDS,
po_headers PH,
po_line_locations PLL,
po_purge_po_list PPL
WHERE PPL.po_header_id BETWEEN l_range_low AND l_range_high
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id = PH.po_header_id
AND NVL(PH.global_agreement_flag, 'N') = 'N'
AND PH.type_lookup_code IN ('STANDARD', 'PLANNED', 'BLANKET')
AND PH.po_header_id = PLL.po_header_id(+)
AND PLL.shipment_type (+) <> 'PRICE BREAK'
AND PH.agent_id = PAPF.person_id
AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
AND PAPF.effective_end_date
AND PH.vendor_site_id = VDS.vendor_site_id
GROUP BY PH.segment1,
PH.type_lookup_code,
PH.vendor_id,
VDS.vendor_site_code,
PH.currency_code,
PAPF.full_name,
PH.creation_date,
PH.org_id; -- bug5446437
message => 'Rows inserted for std/planned po/local bpa:' ||
SQL%ROWCOUNT
);
INSERT INTO po_history_pos
( segment1,
type_lookup_code,
vendor_id,
vendor_site_code,
po_total,
currency_code,
agent_name,
creation_date,
purge_name,
org_id -- bug5446437
)
SELECT PH.segment1,
PH.type_lookup_code,
PH.vendor_id,
VDS.vendor_site_code,
NVL (ROUND (
SUM (
DECODE (
PLL.quantity,
NULL,
PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled,
0)) *
PLL.price_override)),
2),
0),
PH.currency_code,
PAPF.full_name,
PH.creation_date,
p_purge_name,
PH.org_id -- bug5446437
FROM per_all_people_f PAPF,
po_vendor_sites VDS,
po_headers PH,
po_line_locations_all PLL,
po_purge_po_list PPL
WHERE PPL.po_header_id BETWEEN l_range_low AND l_range_high
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id = PH.po_header_id
AND PH.global_agreement_flag = 'Y'
AND PH.type_lookup_code = 'BLANKET'
AND PH.po_header_id = PLL.from_header_id(+)
AND PH.agent_id = PAPF.person_id
AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
AND PAPF.effective_end_date
AND PH.vendor_site_id = VDS.vendor_site_id
GROUP BY PH.segment1,
PH.type_lookup_code,
PH.vendor_id,
VDS.vendor_site_code,
PH.currency_code,
PAPF.full_name,
PH.creation_date,
PH.org_id; -- bug5446437
message => 'Rows inserted for gbpa:' || SQL%ROWCOUNT
);
INSERT INTO po_history_pos
( segment1,
type_lookup_code,
vendor_id,
vendor_site_code,
po_total,
currency_code,
agent_name,
creation_date,
purge_name,
org_id -- bug5446437
)
SELECT PH.segment1,
PH.type_lookup_code,
PH.vendor_id,
VDS.vendor_site_code,
NVL (ROUND (
SUM (
DECODE (
PLL.quantity,
NULL,
PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled,
0)) *
PLL.price_override)),
2),
0),
PH.currency_code,
PAPF.full_name,
PH.creation_date,
p_purge_name,
PH.org_id -- bug5446437
FROM per_all_people_f PAPF,
po_vendor_sites VDS,
po_headers PH,
po_lines_all POL,
po_line_locations_all PLL,
po_purge_po_list PPL
WHERE PPL.po_header_id BETWEEN l_range_low AND l_range_high
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id = PH.po_header_id
AND PH.type_lookup_code = 'CONTRACT'
AND PH.po_header_id = POL.contract_id (+)
AND POL.po_line_id = PLL.po_line_id (+)
AND PH.agent_id = PAPF.person_id
AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
AND PAPF.effective_end_date
AND PH.vendor_site_id = VDS.vendor_site_id
GROUP BY PH.segment1,
PH.type_lookup_code,
PH.vendor_id,
VDS.vendor_site_code,
PH.currency_code,
PAPF.full_name,
PH.creation_date,
PH.org_id; -- bug5446437
message => 'Rows inserted for contracts:' || SQL%ROWCOUNT
);
INSERT INTO po_history_pos
( segment1,
type_lookup_code,
vendor_id,
vendor_site_code,
release_num,
po_total,
currency_code,
agent_name,
creation_date,
purge_name,
org_id -- bug5446437
)
SELECT PH.segment1,
PH.type_lookup_code,
PH.vendor_id,
VDS.vendor_site_code,
PREL.release_num,
NVL (ROUND (
SUM (
DECODE (
PLL.quantity,
NULL,
PLL.amount - NVL(PLL.amount_cancelled, 0),
(PLL.quantity - NVL(PLL.quantity_cancelled,
0)) *
PLL.price_override)),
2),
0),
PH.currency_code,
PAPF.full_name,
PH.creation_date,
p_purge_name,
PH.org_id -- bug5446437
FROM po_vendor_sites VDS,
per_all_people_f PAPF,
po_releases PREL,
po_headers PH,
po_line_locations PLL,
po_purge_po_list PPL
WHERE PPL.po_header_id = PH.po_header_id
AND PH.po_header_id = PREL.po_header_id
AND PREL.po_release_id = PLL.po_release_id
AND PLL.shipment_type IN ('SCHEDULED', 'BLANKET')
AND PH.vendor_site_id = VDS.vendor_site_id
AND PH.agent_id = PAPF.person_id
AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date
AND PAPF.effective_end_date
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high
GROUP BY PH.segment1,
PH.type_lookup_code,
PH.vendor_id,
VDS.vendor_site_code,
PREL.release_num,
PH.currency_code,
PAPF.full_name,
PH.creation_date,
PH.org_id; -- bug5446437
message => 'Rows inserted for releases: ' || SQL%ROWCOUNT
);
END IF; -- l_range_inserted = 'N'
PROCEDURE delete_reqs
( x_return_status OUT NOCOPY VARCHAR2,
p_range_size IN NUMBER,
p_req_lower_limit IN NUMBER,
p_req_upper_limit IN NUMBER
) IS
TYPE num_tbltyp IS TABLE OF NUMBER;
l_api_name VARCHAR2(50) := 'delete_reqs';
SELECT PPRL2.req_header_id
BULK COLLECT INTO l_ids_tbl
FROM (SELECT PPRL.requisition_header_id req_header_id,
MOD(ROWNUM, p_range_size) mod_result
FROM po_purge_req_list PPRL
WHERE PPRL.double_check_flag = 'Y'
ORDER BY PPRL.requisition_header_id) PPRL2
WHERE PPRL2.mod_result = 0;
PO_AP_PURGE_UTIL_PVT.delete_req_related_records
( x_return_status => l_return_status,
p_range_low => l_range_low,
p_range_high => l_range_high
);
DELETE
FROM po_requisition_headers RH
WHERE EXISTS (
SELECT NULL
FROM po_purge_req_list PPRL
WHERE PPRL.requisition_header_id =
RH.requisition_header_id
AND PPRL.double_check_flag = 'Y'
AND PPRL.requisition_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from RH'
);
DELETE
FROM po_req_distributions RD
WHERE EXISTS (
SELECT NULL
FROM po_purge_req_list PPRL,
po_requisition_lines RL
WHERE PPRL.requisition_header_id =
RL.requisition_header_id
AND RL.requisition_line_id =
RD.requisition_line_id
AND PPRL.double_check_flag = 'Y'
AND PPRL.requisition_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from RD'
);
DELETE
FROM po_requisition_lines RL
WHERE EXISTS (
SELECT NULL
FROM po_purge_req_list PPRL
WHERE PPRL.requisition_header_id =
RL.requisition_header_id
AND PPRL.double_check_flag = 'Y'
AND PPRL.requisition_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from RL'
);
DELETE
FROM po_action_history PA
WHERE PA.object_type_code = 'REQUISITION'
AND EXISTS (
SELECT NULL
FROM po_purge_req_list PPRL
WHERE PPRL.requisition_header_id = PA.object_id
AND PPRL.double_check_flag = 'Y'
AND PPRL.requisition_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec frm act_history'
);
END delete_reqs;
PROCEDURE delete_pos
( 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_pos';
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;
PO_AP_PURGE_UTIL_PVT.delete_po_related_records
( x_return_status => l_return_status,
p_range_low => l_range_low,
p_range_high => l_range_high
);
DELETE
FROM po_headers PH
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PH.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high)
--
RETURNING po_header_id, type_lookup_code
BULK COLLECT INTO l_po_header_id_tbl, l_type_lookup_code_tbl;
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PH'
);
DELETE
FROM po_headers_archive PHA
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PHA.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PHA'
);
DELETE
FROM po_lines PL
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PL.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PL'
);
DELETE
FROM PO_ATTRIBUTE_VALUES POATR
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL,
po_lines_all POL
WHERE PPL.po_header_id = POL.po_header_id
AND POATR.po_line_id = POL.po_line_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from POATR'
);
DELETE
FROM PO_ATTRIBUTE_VALUES_TLP POTLP
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL,
po_lines_all POL
WHERE PPL.po_header_id = POL.po_header_id
AND POTLP.po_line_id = POL.po_line_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from POTLP'
);
DELETE
FROM po_lines_archive PLA
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PLA.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PLA'
);
DELETE
FROM PO_ATTR_VALUES_ARCHIVE POATRA
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL,
po_lines_all POL
WHERE PPL.po_header_id = POL.po_header_id
AND POATRA.po_line_id = POL.po_line_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from POATRA'
);
DELETE
FROM PO_ATTR_VALUES_TLP_ARCHIVE POTLPA
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL,
po_lines_all POL
WHERE PPL.po_header_id = POL.po_header_id
AND POTLPA.po_line_id = POL.po_line_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from POTLPA'
);
DELETE
FROM po_line_locations PLL
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PLL.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PLL'
);
DELETE
FROM po_line_locations_archive PLLA
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PLLA.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PLLA'
);
DELETE
FROM po_distributions PD
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PD.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PD'
);
DELETE
FROM po_distributions_archive PDA
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PDA.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PDA'
);
DELETE
FROM po_releases PR
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PR.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high)
--
RETURNING po_release_id
BULK COLLECT INTO l_po_release_id_tbl;
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PR'
);
DELETE
FROM po_releases_archive PRA
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PRA.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from PRA'
);
DELETE
FROM po_acceptances PA
WHERE EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PA.po_header_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec from po_accept'
);
DELETE
FROM po_action_history PAH
WHERE PAH.object_type_code = 'PO'
AND EXISTS (
SELECT NULL
FROM po_purge_po_list PPL
WHERE PPL.po_header_id = PAH.object_id
AND PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
message => 'Deleted ' || SQL%ROWCOUNT || ' rec frm act_history'
);
UPDATE po_requisition_lines_all PRL
SET PRL.blanket_po_header_id = NULL,
PRL.blanket_po_line_num = NULL,
PRL.last_update_date = SYSDATE,
PRL.last_updated_by = FND_GLOBAL.user_id,
PRL.last_update_login = FND_GLOBAL.login_id
WHERE PRL.blanket_po_header_id IS NOT NULL
AND PRL.blanket_po_header_id IN (
SELECT po_header_id
FROM po_purge_po_list PPL
WHERE PPL.double_check_flag = 'Y'
AND PPL.po_header_id BETWEEN l_range_low
AND l_range_high);
insert into zx_purge_transactions_gt(
application_id,
entity_code,
event_class_code,
trx_id
)
select
fnd_global.resp_appl_id(),
PO_CONSTANTS_SV.PO_ENTITY_CODE,
PO_CONSTANTS_SV.PO_EVENT_CLASS_CODE,
l_po_header_id_tbl(i)
from dual
where l_type_lookup_code_tbl(i) IN ('STANDARD', 'PLANNED');
insert into zx_purge_transactions_gt(
application_id,
entity_code,
event_class_code,
trx_id
)
select
fnd_global.resp_appl_id(),
PO_CONSTANTS_SV.REL_ENTITY_CODE,
PO_CONSTANTS_SV.REL_EVENT_CLASS_CODE,
l_po_release_id_tbl(i)
from dual;
END delete_pos;