[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE financials_purges
SET status = P_Status
WHERE purge_name = P_Purge_Name;
/* Bug#2274656 Selecting Recon Accounting Flag also in this program unit */
SELECT DECODE(ASP.accounting_method_option, 'Accrual', 'Y',
DECODE(ASP.secondary_accounting_method,
'Accrual', 'Y', 'N')),
DECODE(ASP.accounting_method_option,'Cash','Y',
DECODE(ASP.secondary_accounting_method,
'Cash', 'Y', 'N')),
nvl(ASP.RECON_ACCOUNTING_FLAG,'N')
INTO p_using_accrual_basis,
p_using_cash_basis,
p_recon_acctg_flag
FROM ap_system_parameters ASP;
select count(1)
into invoice_count
from ap_purge_invoice_list
where double_check_flag = 'Y';
select count(1)
into po_count
from po_purge_po_list
where double_check_flag = 'Y';
select count(1)
into req_count
from po_purge_req_list
where double_check_flag = 'Y';
select count(1)
into vendor_count
from po_purge_vendor_list
where double_check_flag = 'Y';
delete from chv_purge_schedule_list cpsl
where exists (select null
from chv_cum_periods ccp,
chv_schedule_items csi,
chv_schedule_headers csh,
chv_org_options coo
where ccp.organization_id = g_organization_id
and sysdate between ccp.cum_period_start_date and
NVL(ccp.cum_period_end_date,sysdate + 1)
and coo.organization_id = ccp.organization_id
and coo.enable_cum_flag = 'Y'
and csh.schedule_id = csi.schedule_id
and csh.schedule_horizon_start >= ccp.cum_period_start_date
and csi.schedule_item_id = cpsl.schedule_item_id);
delete from chv_purge_schedule_list cpsl
where exists (select null
from chv_schedule_items csi,
ece_spso_items esi
where csi.schedule_item_id = cpsl.schedule_item_id
and csi.schedule_id = esi.schedule_id);
Because performance of delete stmt in do_dependent_inv_checks was very poor.
This function does same check with the delete stmt.
*/
TYPE tab_status_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
select pl.invoice_id
from ap_purge_invoice_list pl,
ap_invoice_payments ip
where pl.invoice_id = ip.invoice_id;
select invoice_id
from ap_purge_invoice_list
where invoice_id = l_invoice_id
and double_check_flag = 'Y';
select pil.invoice_id
from ap_invoice_payments ip,
ap_purge_invoice_list pil
where ip.check_id = l_check_id
and ip.invoice_id = pil.invoice_id (+) ;
select check_id
from ap_invoice_payments
where invoice_id = l_invoice_id ;
UPDATE ap_purge_invoice_list
SET double_check_flag = 'S'
WHERE invoice_id = p_id ;
/* if this chain is not purgeable, delete selected invoice from purge list */
p_count := tab_inv.count;
DELETE FROM ap_purge_invoice_list
WHERE invoice_id = p_id ;
/* delete unpurgeable list beforehand for performance */
p_count := tab_check.count;
DELETE FROM ap_purge_invoice_list
WHERE invoice_id in ( select invoice_id
from ap_invoice_payments
where check_id = p_id);
update ap_purge_invoice_list
set double_check_flag = 'Y'
where double_check_flag = 'S' ;
DELETE
FROM ap_purge_invoice_list PL
WHERE EXISTS (
SELECT 'payment schedule not purgeable'
FROM ap_payment_schedules PS,
ap_invoices I
WHERE PS.invoice_id = PL.invoice_id
AND PS.invoice_id = I.invoice_id
AND ((PS.payment_status_flag <> 'Y'
AND I.cancelled_date is null)
OR PS.last_update_date > g_activity_date));
DELETE
FROM ap_purge_invoice_list PL
WHERE EXISTS
(SELECT 'project-related vendor invoices'
FROM ap_invoice_distributions d
WHERE d.invoice_id = pl.invoice_id
AND d.project_id is not null) -- bug1746226
OR EXISTS
(SELECT 'project-related expense report'
FROM ap_invoices i
WHERE i.invoice_id = pl.invoice_id
AND i.source = 'Oracle Project Accounting');
DELETE
FROM ap_purge_invoice_list PL
WHERE EXISTS
(SELECT 'distributions not purgeable'
FROM ap_invoice_distributions D, ap_invoices I
WHERE I.invoice_id = D.invoice_id
AND PL.invoice_id = D.invoice_id
AND ( D.last_update_date > g_activity_date
OR D.posted_flag <> 'Y'
OR D.accrual_posted_flag =
DECODE(p_using_accrual_basis,
'Y', 'N',
'Z')
OR D.cash_posted_flag =
DECODE(p_using_cash_basis,
'Y', DECODE(D.cash_posted_flag,
'N', 'N',
'P', 'P',
'Z'),
'Z')
OR D.po_distribution_id IS NOT NULL
OR ( D.assets_addition_flag||'' =
DECODE(g_assets_status,
'Y', 'U',
'cantequalme')
AND I.cancelled_date IS NULL)));
DELETE
FROM ap_purge_invoice_list PL
WHERE EXISTS
(SELECT 'distributions not purgeable'
FROM ap_invoice_distributions D, ap_invoices I
WHERE I.invoice_id = D.invoice_id
AND PL.invoice_id = D.invoice_id
AND ( D.last_update_date > g_activity_date
OR D.posted_flag <> 'Y'
OR D.accrual_posted_flag =
DECODE(p_using_accrual_basis,
'Y', 'N',
'Z')
OR D.cash_posted_flag =
DECODE(p_using_cash_basis,
'Y', DECODE(D.cash_posted_flag,
'N', 'N',
'P', 'P',
'Z'),
'Z')
OR ( D.assets_addition_flag||'' =
DECODE(g_assets_status,
'Y', 'U',
'cantequalme')
AND I.cancelled_date IS NULL)));
DELETE
FROM ap_purge_invoice_list PL
WHERE EXISTS
(SELECT 'payment not purgeable'
FROM ap_invoice_payments_all P,
ap_checks C
WHERE P.invoice_id = PL.invoice_id
AND P.check_id = C.check_id
AND (((P.posted_flag <> 'Y'
OR
P.accrual_posted_flag =
DECODE(p_using_accrual_basis, 'Y','N','Z')
OR
P.cash_posted_flag =
DECODE(p_using_cash_basis,'Y',
DECODE(P.cash_posted_flag,'N',
'N','P','P','Z'),'Z')
OR
P.last_update_date > g_activity_date
OR
C.last_update_date > g_activity_date
OR
/*Following two conditions added for bug#2274656 to prevent
Future Dated checks being purged before they are matured */
(C.future_pay_due_date is not null
AND C.status_lookup_code ='ISSUED')
/* Code Modified by MSWAMINA.
Bug 2211285.
Payments should not be considered to in the purge list if it has any reference
information left in cash management. AP assumes that if a customer uses
CE, They have already purged the related data in CE before purging the AP
payments information.
So Added the following condition back. */
/* Fix for bug#2274656
Bug#2211285 hard codes the date in case Cleared Date and Void date are null, to an infinite
value and makes the condition always true , so the payment records would not
get purged if it has not been Cleared, but some customers may not be using
recon accounting at all. Now the recon accounting flag is selected
in get_accounting_method() and we decide based on that */
OR
decode(p_recon_acctg_flag,'Y',nvl(c.cleared_date,
nvl(c.void_date,to_date('12/31/2999','MM/DD/YYYY'))))
> g_activity_date
))
OR
EXISTS (SELECT 'Referenced by cashbook'
from ce_statement_reconcils_all SR
where C.check_id=SR.reference_id
AND SR.reference_type= 'PAYMENT'
AND SR.org_id = C.org_id )));
DELETE
FROM ap_purge_invoice_list PL
WHERE EXISTS
(SELECT 'related to prepayment'
FROM ap_invoice_prepays IP
WHERE PL.invoice_id = IP.invoice_id
OR PL.invoice_id = IP.prepay_id);
DELETE
FROM ap_purge_invoice_list PL
WHERE EXISTS
(SELECT 'X'
FROM ap_invoice_distributions ID
WHERE PL.invoice_id = ID.invoice_id
AND ID.line_type_lookup_code = 'PREPAY'
AND ID.prepay_distribution_id IS NOT NULL);
DELETE FROM ap_purge_invoice_list PL
where EXISTS(
select 'history not purgeable'
from ap_invoice_payments aip
, ap_payment_history aph
where aip.invoice_id = PL.invoice_id
and aip.check_id = aph.check_id
-- To check for posted_flag added for bug#2274656
and nvl(aph.posted_flag,'N') <> 'Y'
--Bug 1579474
--and aph.last_update_date >= g_activity_date);
and aph.last_update_date > g_activity_date);
DELETE FROM ap_purge_invoice_list PL
WHERE EXISTS (
Select 'invoice accounting not purgeable'
from xla_events xe, --Bug 4588031
xla_transaction_entities xte, --Bug 4588031
xla_ae_headers xeh, --Bug 4588031
ap_invoices_all ai,ap_system_parameters_all asp--bug5052748
where xte.entity_code = 'AP_INVOICES'
and xte.source_id_int_1 = PL.invoice_id
AND pl.invoice_id=ai.invoice_id
AND ai.org_id=asp.org_id
AND asp.set_of_books_id=xte.ledger_id
and xte.entity_id = xe.entity_id
and xe.event_id = xeh.event_id --Bug6318079
and xe.application_id = 200
and xeh.application_id = 200
and xte.application_id = 200
and (xeh.gl_transfer_status_code = 'N'
OR ( xeh.last_update_date > g_activity_date )))
OR EXISTS (
Select 'payment accounting not purgeable'
from xla_events xe, --Bug 4588031
xla_transaction_entities xte, --Bug 4588031
ap_invoice_payments aip,
ap_system_parameters_all asp,--bug5052478
xla_ae_headers xeh --Bug 4588031
where xte.entity_code = 'AP_PAYMENTS'
and xte.source_id_int_1 = aip.check_id
and xte.entity_id = xe.entity_id
AND asp.set_of_books_id=xte.ledger_id
AND aip.org_id=asp.org_id
and PL.invoice_id = aip.invoice_id
and xe.event_id = xeh.event_id
and xe.application_id = 200
and xeh.application_id = 200
and xte.application_id = 200
and (xeh.gl_transfer_status_code = 'N'
OR ( xeh.last_update_date > g_activity_date)));
DELETE FROM ap_purge_invoice_list PL
WHERE EXISTS (
select 'matched'
from ap_invoice_distributions aid, rcv_transactions rcv
where aid.invoice_id = PL.invoice_id
and aid.rcv_transaction_id = rcv.transaction_id
and rcv.last_update_date > g_activity_date);
DELETE FROM ap_purge_invoice_list PL
WHERE EXISTS
(select null
from ap_invoice_distributions ad
where ad.invoice_id = PL.invoice_id
and ad.rcv_transaction_id is not null
and exists (
select 'matching' from ap_invoice_distributions ad2
where ad2.rcv_transaction_id = ad.rcv_transaction_id
and ad2.invoice_id NOT IN (
select invoice_id
from ap_purge_invoice_list
where double_check_flag = 'Y')));
SELECT count(*) INTO l_list_count FROM ap_purge_invoice_list;
select count(1)
into invoice_count
from ap_purge_invoice_list
where double_check_flag = 'Y';
delete from ap_purge_invoice_list apl
where exists
(select null
from ap_invoice_distributions ad
where ad.invoice_id = apl.invoice_id
and ad.po_distribution_id is not null
and not exists (select null
from po_purge_po_list ppl,
po_distributions pd
where ppl.po_header_id =
pd.po_header_id
and pd.po_distribution_id =
ad.po_distribution_id));
update ap_purge_invoice_list apl
set double_check_flag = 'N'
where double_check_flag = 'Y'
and exists (select /*+NO_UNNEST*/ null
from ap_invoice_distributions ad,po_distributions pd
where ad.invoice_id = apl.invoice_id
AND pd.po_distribution_id=ad.po_distribution_id
and ad.po_distribution_id is not null
and not exists (SELECT null
FROM po_purge_po_list ppl
WHERE ppl.double_check_flag = 'Y'
AND ppl.po_header_id =pd.po_header_id));
select count(1)
into invoice_count
from ap_purge_invoice_list
where double_check_flag = 'Y';
SELECT count(*)
INTO start_list_count
FROM ap_purge_invoice_list
WHERE double_check_flag = DECODE(p_purge_status, 'INITIATING', 'Y',
double_check_flag);
DELETE
FROM ap_purge_invoice_list PL
WHERE EXISTS (
SELECT 'relational problem'
FROM ap_invoice_payments IP1,
ap_invoice_payments IP2
WHERE PL.invoice_id = IP1.invoice_id
AND IP1.check_id = IP2.check_id
AND IP2.invoice_id NOT IN (
SELECT PL2.invoice_id
FROM ap_purge_invoice_list PL2
WHERE PL2.invoice_id =
IP2.invoice_id)
);
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE PL.double_check_flag = 'Y'
AND EXISTS (
SELECT /*+NO_UNNEST*/'relational problem'
FROM ap_invoice_payments IP1, ap_invoice_payments IP2
WHERE PL.invoice_id = IP1.invoice_id
AND IP1.check_id = IP2.check_id
AND IP2.invoice_id NOT IN (
SELECT PL2.invoice_id
FROM ap_purge_invoice_list PL2
WHERE PL2.invoice_id = IP2.invoice_id
AND PL2.double_check_flag ='Y'));
SELECT count(*)
INTO list_count
FROM ap_purge_invoice_list
WHERE double_check_flag = DECODE(p_purge_status, 'INITIATING', 'Y',
double_check_flag);
insert into chv_purge_cum_list
(cum_period_id,
purge_name,
double_check_flag)
select ccp.cum_period_id,
p_purge_name,
'Y'
from chv_cum_periods ccp
where ccp.organization_id = g_organization_id
and NVL(ccp.cum_period_end_date, sysdate + 1) <= g_activity_date
and NVL(ccp.cum_period_end_date,sysdate + 1) < sysdate;
insert into chv_purge_schedule_list
(schedule_item_id,
purge_name,
double_check_flag)
select csi.schedule_item_id,
p_purge_name,
'Y'
from chv_schedule_items csi,
chv_schedule_headers csh,
chv_purge_cum_list cpcl,
chv_cum_periods ccp
where csh.schedule_id = csi.schedule_id
and csh.schedule_horizon_start between ccp.cum_period_start_date
and ccp.cum_period_end_date
and ccp.cum_period_id = cpcl.cum_period_id
and csi.organization_id = g_organization_id;
insert into chv_purge_schedule_list
(schedule_item_id,
purge_name,
double_check_flag)
select csi.schedule_item_id,
p_purge_name,
'Y'
from chv_schedule_items csi,
chv_schedule_headers csh
where csh.schedule_id = csi.schedule_id
and csh.last_update_date <= g_activity_date
and NVL(csi.item_purge_status,'N') <> 'PURGED'
and csi.organization_id = g_organization_id;
Print('(Inserting into ap_purge_invoice_list)' ||debug_info);
INSERT INTO ap_purge_invoice_list
(invoice_id, purge_name, double_check_flag)
SELECT DISTINCT I.invoice_id, p_purge_name, 'Y'
FROM ap_invoices I, ap_invoice_distributions D
WHERE I.invoice_id = D.invoice_id
AND I.payment_status_flag || '' = 'Y'
AND I.invoice_type_lookup_code <> 'PREPAYMENT'
AND D.posted_flag || '' = 'Y'
AND D.accrual_posted_flag = DECODE(p_using_accrual_basis, 'Y','Y',
D.accrual_posted_flag)
AND D.cash_posted_flag = DECODE(p_using_cash_basis, 'Y','Y',
D.cash_posted_flag)
AND D.last_update_date <= g_activity_date
AND I.last_update_date <= g_activity_date
AND I.invoice_date <= g_activity_date
UNION
SELECT I.invoice_id, p_purge_name, 'Y'
FROM ap_invoices I, ap_invoice_distributions D
WHERE I.invoice_id = D.invoice_id (+)
AND I.last_update_date <= g_activity_date
AND I.invoice_date <= g_activity_date
AND I.invoice_amount = 0
AND I.invoice_type_lookup_code <> 'PREPAYMENT'
GROUP BY I.invoice_id
HAVING SUM(NVL(D.amount, 0)) = 0;
select count(*) into temp from ap_purge_invoice_list;
Print('(Done inserting into ap_purge_invoice_list)' ||debug_info);
insert into po_purge_vendor_list
(vendor_id,
purge_name,
double_check_flag)
select vnd.vendor_id,
p_purge_name,
'Y'
from ap_suppliers vnd
where vnd.end_date_active <= g_activity_date
and not exists (select 'vnd.vendor is a parent of
another vendor'
from ap_suppliers v
where v.parent_vendor_id =
vnd.vendor_id)
--Bug 2653578
and PO_THIRD_PARTY_STOCK_GRP.validate_supplier_purge(
vnd.vendor_id) = 'TRUE';
delete from po_purge_vendor_list pvl
where exists
(select null
from fa_mass_additions fma
where fma.po_vendor_id = pvl.vendor_id)
or exists
(select null
from fa_asset_invoices fai
where fai.po_vendor_id = pvl.vendor_id);
delete from po_purge_vendor_list pvl
where exists
(select null
from ap_invoices_all ai
where ai.vendor_id = pvl.vendor_id)
or exists
(select null
from ap_selected_invoices_all asi,
ap_supplier_sites_all pvs
where asi.vendor_site_id =
pvs.vendor_site_id
and pvs.vendor_id = pvl.vendor_id)
or exists
(select null
from ap_recurring_payments_all arp
where arp.vendor_id = pvl.vendor_id);
delete from po_purge_vendor_list pvl
where exists (select null
from po_headers_all ph
where ph.vendor_id =
pvl.vendor_id)
or exists (select null
from rcv_shipment_headers
rcvsh
where rcvsh.vendor_id =
pvl.vendor_id)
or exists (select null
from po_rfq_vendors rfq
where rfq.vendor_id =
pvl.vendor_id);
delete from po_purge_vendor_list pvl
where exists (select null
from fa_mass_additions fma
where fma.po_vendor_id =
pvl.vendor_id)
or exists (select null
from fa_asset_invoices fai
where fai.po_vendor_id =
pvl.vendor_id);
delete from po_purge_vendor_list pvl
where exists (select null
from ap_invoices_all ai
where ai.vendor_id = pvl.vendor_id)
or exists (select null
from ap_selected_invoices_all asi,
ap_supplier_sites_all pvs
where asi.vendor_site_id =
pvs.vendor_site_id
and pvs.vendor_id = pvl.vendor_id)
or exists (select null
from ap_recurring_payments_all arp
where arp.vendor_id = pvl.vendor_id);
delete from po_purge_vendor_list pvl
where exists (select null
from po_headers_all ph
where ph.vendor_id = pvl.vendor_id)
or exists (select null
from rcv_shipment_headers rcvsh
where rcvsh.vendor_id = pvl.vendor_id)
or exists (select null
from po_rfq_vendors rfq
where rfq.vendor_id = pvl.vendor_id)
or exists (select null
from rcv_headers_interface rhi
where rhi.vendor_id = pvl.vendor_id)
or exists (select null
from rcv_transactions_interface rti
where rti.vendor_id = pvl.vendor_id);
delete from po_purge_vendor_list pvl
where exists (select null
from chv_schedule_headers csh
where csh.vendor_id = pvl.vendor_id);
delete from po_purge_vendor_list pvl
where exists (select null
from ece_tp_details etd,
ap_supplier_sites_all pvs
where etd.tp_header_id = pvs.tp_header_id
and pvs.vendor_id = pvl.vendor_id
and etd.last_update_date > g_activity_date);
delete from po_purge_vendor_list pvl
where exists (select null
from mrp_sr_source_org msso
where msso.vendor_id = pvl.vendor_id);
range_inserted VARCHAR2(1);
range_inserted := 'N';
select 'Y'
into range_inserted
from sys.dual
where exists (select null
from ap_history_invoices
where purge_name = p_purge_name
and invoice_id between range_low and range_high);
range_inserted := 'N';
if (range_inserted <> 'Y') then
--
debug_info := 'Summerizing sub-group from Oracle Purchasing -- Invoices';
INSERT INTO ap_history_invoices_all
(invoice_id, vendor_id, vendor_site_code, invoice_num, invoice_date,
invoice_amount, batch_name, purge_name, doc_sequence_id,
doc_sequence_value,org_id)
SELECT i.invoice_id, i.vendor_id, v.vendor_site_code, i.invoice_num,
i.invoice_date, i.invoice_amount, b.batch_name, p_purge_name,
i.doc_sequence_id, i.doc_sequence_value,i.org_id
FROM ap_invoices_all i, ap_supplier_sites_all v, ap_batches_all b
WHERE i.vendor_site_id = v.vendor_site_id
AND i.batch_id = b.batch_id (+)
AND i.invoice_id IN (SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND
range_high);
INSERT INTO ap_history_checks_all
(check_id, bank_account_id, check_number, check_date, amount,
currency_code, void_flag, purge_name, doc_sequence_id,
doc_sequence_value, payment_id,org_id)
SELECT
ac.check_id, ac.bank_account_id, ac.check_number, ac.check_date,
ac.amount, ac.currency_code, DECODE(void_date, null, null, 'Y'),
p_purge_name, ac.doc_sequence_id, ac.doc_sequence_value, ac.payment_id,
ac.org_id
FROM ap_checks_all AC,
ap_invoice_payments_all IP,
ap_purge_invoice_list PL
WHERE PL.invoice_id = IP.invoice_id
AND IP.check_id = AC.check_id
AND PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high
AND NOT EXISTS (SELECT null
FROM ap_history_checks_all hc
WHERE hc.check_id = AC.check_id)
GROUP BY ac.check_id, ac.bank_account_id, ac.check_number,
ac.check_date, ac.amount, ac.currency_code,
DECODE(void_date, null, null, 'Y'), purge_name,
ac.doc_sequence_id, ac.doc_sequence_value, ac.payment_id,
ac.org_id; --Bug 6277474 added the org_id in group by clause.
INSERT INTO ap_history_inv_payments_all
(invoice_id, check_id, amount,org_id)
SELECT
IP.invoice_id, IP.check_id, SUM(IP.amount),IP.org_id
FROM ap_invoice_payments_all IP, ap_purge_invoice_list PL
WHERE IP.invoice_id = PL.invoice_id
AND PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high
GROUP BY IP.invoice_id, IP.check_id,
IP.org_id; --Bug 6277474 added the org_id in group by clause.
range_inserted VARCHAR2(1);
range_inserted := 'N';
select 'Y'
into range_inserted
from sys.dual
where exists (select null
from po_history_vendors vnd
where vnd.purge_name = p_purge_name);
range_inserted := 'N';
if (range_inserted <> 'Y') then
--
debug_info := 'Vendors';
insert into po_history_vendors
(vendor_id,
vendor_name,
segment1,
vendor_type_lookup_code,
purge_name)
select vnd.vendor_id,
vnd.vendor_name,
vnd.segment1,
vnd.vendor_type_lookup_code,
p_purge_name
from po_purge_vendor_list pvl,
ap_suppliers vnd
where pvl.vendor_id = vnd.vendor_id
and pvl.double_check_flag = 'Y';
range_inserted VARCHAR2(1);
range_inserted := 'N';
select MAX('Y')
into range_inserted
from sys.dual
where exists (select null
from chv_schedule_items csi,
chv_schedule_headers csh,
chv_history_schedules chs
where csi.item_id = chs.item_id
and csi.schedule_id = chs.schedule_id
and csh.schedule_id = chs.schedule_id
and csh.vendor_id = chs.vendor_id
and csh.vendor_site_id = chs.vendor_site_id
and csi.organization_id = chs.organization_id
and chs.purge_name = p_purge_name
and csi.schedule_item_id between range_low
and range_high);
Print('(Range Inserted) ' || range_inserted);
if (NVL(range_inserted,'N') <> 'Y') then
--
debug_info := 'Summerizing sub-group from Oracle Supplier Scheduling';
insert into chv_history_schedules
(schedule_id,
vendor_id,
vendor_site_id,
schedule_type,
schedule_subtype,
schedule_horizon_start,
bucket_pattern_id,
creation_date,
schedule_num,
schedule_revision,
schedule_status,
item_id,
organization_id,
purge_name
)
select csh.schedule_id,
csh.vendor_id,
csh.vendor_site_id,
csh.schedule_type,
csh.schedule_subtype,
csh.schedule_horizon_start,
csh.bucket_pattern_id,
csh.creation_date,
csh.schedule_num,
csh.schedule_revision,
csh.schedule_status,
csi.item_id,
csi.organization_id,
p_purge_name
from chv_purge_schedule_list cpsl,
chv_schedule_headers csh,
chv_schedule_items csi
where cpsl.schedule_item_id = csi.schedule_item_id
and csi.schedule_id = csh.schedule_id
and cpsl.double_check_flag = 'Y'
and cpsl.schedule_item_id between range_low and range_high;
range_inserted := 'N';
select MAX('Y')
into range_inserted
from sys.dual
where exists (select null
from chv_cum_periods ccp,
chv_history_cum_periods chcp
where ccp.cum_period_id = chcp.cum_period_id
and chcp.purge_name = p_purge_name);
NVL(range_inserted,'N') <> 'Y') then
-- summarize_schedules_by_org
insert into chv_history_cum_periods
(cum_period_id,
cum_period_name,
cum_period_start_date,
cum_period_end_date,
creation_date,
purge_name
)
select ccp.cum_period_id,
ccp.cum_period_name,
ccp.cum_period_start_date,
ccp.cum_period_end_date,
ccp.creation_date,
p_purge_name
from chv_purge_cum_list cpcl,
chv_cum_periods ccp
where cpcl.cum_period_id = ccp.cum_period_id
and cpcl.double_check_flag = 'Y';
select nvl(min(invoice_id),-1),
nvl(max(invoice_id),-1)
into p_inv_lower_limit, p_inv_upper_limit
from ap_purge_invoice_list
where double_check_flag = 'Y';
select nvl(min(po_header_id),-1),
nvl(max(po_header_id),-1)
into p_po_lower_limit, p_po_upper_limit
from po_purge_po_list
where double_check_flag = 'Y';
select nvl(min(requisition_header_id),-1),
nvl(max(requisition_header_id),-1)
into p_req_lower_limit, p_req_upper_limit
from po_purge_req_list
where double_check_flag = 'Y';
select nvl(min(schedule_item_id),-1),
nvl(max(schedule_item_id),-1)
into p_chv_lower_limit, p_chv_upper_limit
from chv_purge_schedule_list
where double_check_flag = 'Y';
debug_info := 'Inserting summary records into history tables';
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE PL.double_check_flag = 'Y'
AND EXISTS(
SELECT 'invoice no longer purgeable'
FROM ap_invoices I
WHERE PL.invoice_id = I.invoice_id
AND (( I.payment_status_flag <> 'Y'
AND
I.invoice_amount <> 0)
OR I.last_update_date > g_activity_date
OR I.invoice_date > g_activity_date));
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE PL.double_check_flag = 'Y'
AND (EXISTS
(SELECT 'project-related vendor invoices'
FROM ap_invoice_distributions d
WHERE d.invoice_id = pl.invoice_id
AND d.project_id is not null -- bug1746226
)
OR EXISTS
(SELECT 'project-related expense report'
FROM ap_invoices i
WHERE i.invoice_id = pl.invoice_id
AND i.source = 'Oracle Project Accounting'
));
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE PL.double_check_flag = 'Y'
AND EXISTS (
SELECT /*+NO_UNNEST*/ 'payment schedule no longer purgeable'
FROM ap_payment_schedules PS,
ap_invoices I
WHERE PS.invoice_id = PL.invoice_id
AND PS.invoice_id = I.invoice_id
AND ((PS.payment_status_flag <> 'Y'
AND I.cancelled_date is null)
OR PS.last_update_date > g_activity_date)
);
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE PL.double_check_flag = 'Y'
AND EXISTS
(SELECT /*+NO_UNNEST*/ 'distribution no longer purgeable'
FROM ap_invoice_distributions D, ap_invoices I
WHERE I.invoice_id = D.invoice_id
AND PL.invoice_id = D.invoice_id
AND ( D.last_update_date > g_activity_date
OR D.posted_flag <> 'Y'
OR D.po_distribution_id IS NOT NULL
OR ( D.assets_addition_flag||'' =
Decode(g_Assets_Status,
'Y', 'U',
'cantequalme')
AND I.cancelled_date IS NULL)));
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE PL.double_check_flag = 'Y'
AND EXISTS
(SELECT /*+NO_UNNEST*/'distribution no longer purgeable'
FROM ap_invoice_distributions D, ap_invoices I
WHERE I.invoice_id = D.invoice_id
AND PL.invoice_id = D.invoice_id
AND ( D.last_update_date > g_activity_date
OR D.posted_flag <> 'Y'
OR ( D.assets_addition_flag||'' =
Decode(g_Assets_Status,
'Y', 'U',
'cantequalme')
AND I.cancelled_date IS NULL)));
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE PL.double_check_flag = 'Y'
AND EXISTS (
SELECT /*+NO_UNNEST*/'payment no longer purgeable'
FROM ap_invoice_payments P, ap_checks C
WHERE P.invoice_id = PL.invoice_id
AND P.check_id = C.check_id
AND (P.posted_flag <> 'Y'
OR P.last_update_date > g_activity_date
OR C.last_update_date > g_activity_date
OR (NVL(C.cleared_date, C.void_date) > g_activity_date
AND nvl(C.cleared_date, C.void_date) is not NULL)
));
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE PL.double_check_flag = 'Y'
AND EXISTS (
SELECT 'recently related to prepayment'
FROM ap_invoice_prepays IP
WHERE PL.invoice_id = IP.invoice_id
OR PL.invoice_id = IP.prepay_id);
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE EXISTS (
SELECT 'matched'
FROM ap_invoice_distributions aid
, rcv_transactions rcv
WHERE aid.invoice_id = PL.invoice_id
and aid.rcv_transaction_id = rcv.transaction_id
--Bug 1579474
and rcv.last_update_date > g_activity_date
);
UPDATE ap_purge_invoice_list PL
SET double_check_flag = 'N'
WHERE EXISTS (
SELECT null
FROM ap_invoice_distributions ad
WHERE ad.invoice_id = PL.invoice_id
and ad.rcv_transaction_id IS NOT NULL
and EXISTS (
SELECT 'matching'
FROM ap_invoice_distributions ad2
where ad2.rcv_transaction_id = ad.rcv_transaction_id
and ad2.invoice_id NOT IN (
SELECT invoice_id
FROM ap_purge_invoice_list
WHERE double_check_flag = 'Y')));
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE EXISTS (
SELECT 'invoice accounting not purgeable'
FROM xla_events xe,
xla_ae_headers xeh,
xla_transaction_entities xte,
ap_invoices_all ai,
ap_system_parameters_all asp --bug5052748
where xte.entity_code = 'AP_INVOICES'
and xte.entity_id = xe.entity_id
and xte.source_id_int_1 =PL.invoice_id
AND ai.invoice_id=pl.invoice_id
AND ai.org_id=asp.org_id
AND asp.set_of_books_id=xte.ledger_id
and xe.event_id = xeh.event_id
and xe.application_id = 200
and xeh.application_id = 200
and xte.application_id = 200
and (xeh.gl_transfer_status_code = 'N'
OR ( xeh.last_update_date > g_activity_date)))
OR EXISTS (
SELECT 'payment accounting not purgeable'
FROM xla_events xe
, ap_invoice_payments aip
, ap_checks apc
, xla_ae_headers xeh
, xla_transaction_entities xte
WHERE xte.entity_code = 'AP_CHECKS'
and xte.source_id_int_1 = apc.check_id
and PL.invoice_id = aip.invoice_id
and aip.check_id = apc.check_id
and xe.event_id = xeh.event_id
and xe.application_id = 200
and xeh.application_id = 200
and xte.application_id = 200
and xe.event_id = xeh.event_id
and (xeh.gl_transfer_status_code = 'N'
OR ( xeh.last_update_date > g_activity_date)));
Because performance of delete stmt in do_dependent_inv_checks was very poor.
This function does same check with the delete stmt.
*/
TYPE tab_status_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
select pl.invoice_id
from ap_purge_invoice_list pl,
ap_invoice_payments ip
where pl.invoice_id = ip.invoice_id;
select invoice_id
from ap_purge_invoice_list
where invoice_id = l_invoice_id
and double_check_flag = 'Y';
select pil.invoice_id
from ap_invoice_payments ip,
ap_purge_invoice_list pil
where ip.check_id = l_check_id
and ip.invoice_id = pil.invoice_id (+)
and pil.double_check_flag = 'Y';
select check_id
from ap_invoice_payments
where invoice_id = l_invoice_id ;
UPDATE ap_purge_invoice_list
SET double_check_flag = 'S'
WHERE invoice_id = p_id ;
/* if this chain is not purgeable, delete selected invoice from purge list */
p_count := tab_inv.count;
UPDATE ap_purge_invoice_list
SET double_check_flag = 'N'
WHERE invoice_id = p_id ;
/* delete unpurgeable list beforehand for performance */
p_count := tab_check.count;
UPDATE ap_purge_invoice_list
SET double_check_flag = 'N'
WHERE invoice_id in ( select invoice_id
from ap_invoice_payments
where check_id = p_id);
update ap_purge_invoice_list
set double_check_flag = 'Y'
where double_check_flag = 'S' ;
SELECT count(*)
INTO fp_check_rows
FROM ap_checks;
SELECT count(*)
INTO fp_invoice_payment_rows
FROM ap_invoice_payments;
SELECT count(*)
INTO fp_invoice_rows
FROM ap_invoices;
SELECT count(*)
INTO fp_payment_history_rows
FROM ap_payment_history;
SELECT count(*)
INTO fp_encumbrance_line_rows
FROM ap_encumbrance_lines;
SELECT count(*)
INTO fp_rcv_subledger_detail_rows
FROM rcv_sub_ledger_details;
update po_purge_vendor_list pvl
set double_check_flag = 'N'
where pvl.double_check_flag = 'Y'
and not exists (select null
from ap_suppliers vnd
where vnd.vendor_id = pvl.vendor_id
--and nvl(vnd.vendor_type_lookup_code, 'VENDOR') <> 'EMPLOYEE'
and nvl(vnd.end_date_active,sysdate) <=
g_activity_date);
update po_purge_vendor_list pvl
set double_check_flag = 'N'
where pvl.double_check_flag = 'Y'
and (exists (select null
from fa_mass_additions fma
where fma.po_vendor_id = pvl.vendor_id)
or
exists (select null
from fa_asset_invoices fai
where fai.po_vendor_id = pvl.vendor_id));
update po_purge_vendor_list pvl
set double_check_flag = 'N'
where pvl.double_check_flag = 'Y'
and (exists (select null
from ap_invoices_all ai
where ai.vendor_id = pvl.vendor_id)
or
exists (select null
from ap_selected_invoices_all asi,
ap_supplier_sites_all pvs
where asi.vendor_site_id = pvs.vendor_site_id
and pvs.vendor_id = pvl.vendor_id)
or
exists (select null
from ap_recurring_payments_all arp
where arp.vendor_id = pvl.vendor_id));
update po_purge_vendor_list pvl
set double_check_flag = 'N'
where pvl.double_check_flag = 'Y'
and (exists (select null
from po_headers_all ph
where ph.vendor_id = pvl.vendor_id)
or
exists (select null
from po_rfq_vendors rfq
where rfq.vendor_id = pvl.vendor_id)
or
exists (select null
from rcv_shipment_headers rcvsh
where rcvsh.vendor_id = pvl.vendor_id)
or
exists (select null
from rcv_headers_interface rhi
where rhi.vendor_id = pvl.vendor_id)
or
exists (select null
from rcv_transactions_interface rti
where rti.vendor_id = pvl.vendor_id));
update po_purge_vendor_list pvl
set double_check_flag = 'N'
where pvl.double_check_flag = 'Y'
and (exists (select null
from chv_schedule_headers csh
where csh.vendor_id = pvl.vendor_id));
update po_purge_vendor_list pvl
set double_check_flag = 'N'
where pvl.double_check_flag = 'Y'
and (exists (select null
from mrp_sr_source_org msso
where msso.vendor_id = pvl.vendor_id));
update po_purge_vendor_list pvl
set double_check_flag = 'N'
where pvl.double_check_flag = 'Y'
and (exists (select null
from ece_tp_details etd,
ap_supplier_sites_all pvs
where etd.tp_header_id = pvs.tp_header_id
and pvs.vendor_id = pvl.vendor_id
and etd.last_update_date > g_activity_date));
update chv_purge_schedule_list cpsl
set double_check_flag = 'N'
where cpsl.double_check_flag = 'Y'
and not exists (select 'schedule not purgeable' from chv_schedule_items csi,
chv_schedule_headers csh
where csh.schedule_id = csi.schedule_id
and csh.last_update_date <= g_activity_date
and csi.organization_id = g_organization_id
and csi.schedule_item_id = cpsl.schedule_item_id);
update chv_purge_cum_list cpcl
set double_check_flag = 'N'
where cpcl.double_check_flag = 'Y'
and not exists (select null from chv_cum_periods ccp
where ccp.organization_id = g_organization_id
and NVL(ccp.cum_period_end_date, sysdate + 1) <= g_activity_date
and NVL(ccp.cum_period_end_date,sysdate + 1) < sysdate
and ccp.cum_period_id = cpcl.cum_period_id);
update chv_purge_schedule_list cpsl
set double_check_flag = 'N'
where cpsl.double_check_flag = 'Y'
and not exists (select null from chv_schedule_items csi,
chv_schedule_headers csh,
chv_cum_periods ccp,
chv_purge_cum_list cpcl
where csh.schedule_id = csi.schedule_id
and csh.schedule_horizon_start between ccp.cum_period_start_date
and ccp.cum_period_end_date
and ccp.cum_period_id = cpcl.cum_period_id
and csi.organization_id = g_organization_id
and csi.schedule_item_id = cpsl.schedule_item_id);
update chv_purge_schedule_list cpsl
set double_check_flag = 'N'
where cpsl.double_check_flag = 'Y'
and exists (select null
from chv_cum_periods ccp,
chv_schedule_items csi,
chv_schedule_headers csh,
chv_org_options coo
where ccp.organization_id = g_organization_id
and sysdate between ccp.cum_period_start_date and
NVL(ccp.cum_period_end_date,sysdate + 1)
and coo.organization_id = ccp.organization_id
and coo.enable_cum_flag = 'Y'
and csh.schedule_id = csi.schedule_id
and csh.schedule_horizon_start >= ccp.cum_period_start_date
and csi.schedule_item_id = cpsl.schedule_item_id);
update chv_purge_schedule_list cpsl
set double_check_flag = 'N'
where cpsl.double_check_flag = 'Y'
and exists (select null
from chv_schedule_items csi,
ece_spso_items esi
where csi.schedule_item_id = cpsl.schedule_item_id
and csi.schedule_id = esi.schedule_id);
SELECT count(*)
INTO chv_auth_rows
FROM chv_authorizations;
SELECT count(*)
INTO chv_cum_adj_rows
FROM chv_cum_adjustments;
SELECT count(*)
INTO chv_cum_rows
FROM chv_cum_periods;
SELECT count(*)
INTO chv_hor_rows
FROM chv_horizontal_schedules;
SELECT count(*)
INTO chv_ord_rows
FROM chv_item_orders;
SELECT count(*)
INTO chv_head_rows
FROM chv_schedule_headers;
SELECT count(*)
INTO chv_item_rows
FROM chv_schedule_items
WHERE NVL(item_purge_status,'N') <> 'PURGED';
UPDATE financials_purges
SET
ap_checks = fp_check_rows,
ap_invoice_payments = fp_invoice_payment_rows,
ap_invoices = fp_invoice_rows,
po_headers = fp_po_header_rows ,
po_receipts = fp_receipt_line_rows,
po_requisition_headers = fp_req_header_rows,
po_vendors = fp_vendor_rows,
po_approved_supplier_list = fp_po_asl_rows,
po_asl_attributes = fp_po_asl_attr_rows,
po_asl_documents = fp_po_asl_doc_rows,
chv_authorizations = fp_chv_auth_rows,
chv_cum_adjustments = fp_chv_cum_adj_rows,
chv_cum_periods = fp_chv_cum_rows,
chv_horizontal_schedules = fp_chv_hor_rows,
chv_item_orders = fp_chv_ord_rows,
chv_schedule_headers = fp_chv_head_rows,
chv_schedule_items = fp_chv_item_rows,
ap_ae_lines = fp_ae_line_rows,
ap_ae_headers = fp_ae_header_rows,
ap_accounting_events = fp_accounting_event_rows,
ap_chrg_allocations = fp_chrg_allocation_rows,
ap_payment_history = fp_payment_history_rows,
ap_encumbrance_lines = fp_encumbrance_line_rows,
rcv_subledger_details = fp_rcv_subledger_detail_rows
WHERE purge_name = fp_purge_name ;
UPDATE ap_purge_invoice_list PL
SET PL.double_check_flag = 'N'
WHERE EXISTS (
SELECT 'history not purgeable'
FROM ap_invoice_payments aip
, ap_payment_history aph
WHERE aip.invoice_id = PL.invoice_id
and aip.check_id = aph.check_id
and aph.last_update_date > g_activity_date);
'Delete Seeded Data') <> TRUE) then
Print('purge_schedules_by_cum failed!');
'Delete Seeded Data') <> TRUE) then
Print('purge_schedules_by_cum failed!');
CURSOR overflow_select is
SELECT C.check_stock_id,C.check_number
FROM ap_invoice_payments P, ap_purge_invoice_list PL,
ap_checks C
WHERE P.invoice_id = PL.invoice_id
AND P.check_id = C.check_id
AND PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high;
to_be_deleted_check_number NUMBER;
OPEN overflow_select;
debug_info := 'Fetch overflow_select Cursor';
FETCH overflow_select into overflow_check_stock_id,
to_be_deleted_check_number;
EXIT WHEN overflow_select%NOTFOUND OR overflow_select%NOTFOUND IS NULL;
||' to_be_deleted_check_number = '
||to_char(to_be_deleted_check_number));
overflow_check_number := to_be_deleted_check_number - 1;
SELECT 'exist'
INTO overflow_exist
FROM ap_checks C
WHERE C.check_stock_id = overflow_check_stock_id
AND C.check_number = overflow_check_number
AND C.status_lookup_code = 'OVERFLOW';
debug_info := 'Delete_Overflow from ap_checks';
DELETE FROM ap_checks C
WHERE C.check_stock_id = overflow_check_stock_id
AND C.check_number = overflow_check_number
AND C.status_lookup_code = 'OVERFLOW';
SELECT 'exist'
INTO overflow_exist
FROM ap_checks C
WHERE C.check_stock_id = overflow_check_stock_id
AND C.check_number = overflow_check_number
AND C.status_lookup_code = 'OVERFLOW';
CLOSE overflow_select;
CURSOR setup_spoil_select is
SELECT distinct C.checkrun_name
FROM ap_checks C, ap_invoice_selection_criteria D
WHERE D.LAST_UPDATE_DATE <= g_activity_date
AND C.checkrun_name NOT IN
(SELECT distinct b.checkrun_name
FROM ap_checks a,
ap_invoice_selection_criteria b
WHERE a.checkrun_name = b.checkrun_name
AND a.status_lookup_code not in
('SET UP', 'SPOILED'))
AND C.checkrun_name = D.checkrun_name
AND C.last_update_date <= g_activity_date;
selected_checkrun ap_invoice_selection_criteria.checkrun_name%TYPE;
OPEN setup_spoil_select;
debug_info := 'Fetch setup_spoil_select Cursor';
FETCH setup_spoil_select into selected_checkrun;
EXIT WHEN setup_spoil_select%NOTFOUND OR setup_spoil_select%NOTFOUND IS NULL;
debug_info := 'delete_setup_spoil';
DELETE FROM ap_checks C
WHERE C.checkrun_name = selected_checkrun
AND C.status_lookup_code in ('SET UP','SPOILED')
AND C.last_update_date <= g_activity_date;
debug_info := 'delete_invoice_selection';
DELETE FROM ap_invoice_selection_criteria C
WHERE C.checkrun_name = selected_checkrun
AND C.last_update_date <= g_activity_date;
Function: Delete_AP_Tables
*==========================================================================*/
FUNCTION Delete_AP_Tables
(P_Calling_Sequence IN VARCHAR2)
RETURN BOOLEAN IS
debug_info VARCHAR2(200);
to_be_deleted_check_number NUMBER;
SELECT invoice_id
FROM ap_purge_invoice_list
WHERE double_check_flag = 'Y'
and invoice_id > low_inv_id
ORDER BY invoice_id asc;
SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN low_inv_id AND high_inv_id;
current_calling_sequence := 'Delete_AP_Tables<-'||P_Calling_Sequence;
debug_info := 'Starting Delete_AP_Tables';
Print('(Delete_AP_Tables)'||debug_info);
select nvl(min(invoice_id),-1)
, nvl(max(invoice_id),-1)
into range_low, range_high
from ap_purge_invoice_list
where double_check_flag = 'Y';
Print('(Delete_AP_Tables)'||debug_info);
delete from ap_chrg_allocations aca
where exists (
select 'allocations'
from ap_invoice_distributions aid
, ap_purge_invoice_list pil
where aca.item_dist_id = aid.invoice_distribution_id
and pil.invoice_id = aid.invoice_id
and pil.invoice_id BETWEEN range_low and range_high
and pil.double_check_flag = 'Y');
DELETE FROM ap_doc_sequence_audit AUD
WHERE (AUD.doc_sequence_id , AUD.doc_sequence_value)
IN (SELECT C.doc_sequence_id , C.doc_sequence_value
FROM ap_purge_invoice_list PL,
ap_checks C,
ap_invoice_payments IP
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high
AND PL.invoice_id = IP.invoice_id
AND IP.check_id = C.check_id ) ;
Print('(Delete_AP_Tables)'||debug_info);
'delete_ap_tables') <> TRUE) then
Print( 'Overflow failed!');
debug_info := 'delete_checks';
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_checks_all C
WHERE C.check_id IN (
SELECT P.check_id
FROM ap_invoice_payments P, ap_purge_invoice_list PL
WHERE P.invoice_id = PL.invoice_id
AND PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high);
Print('(Delete_AP_Tables)'||debug_info);
if (Setup_Spoil('delete_ap_tables') <> TRUE) then
Print('Setup_Spoil failed!');
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_payment_history aph
WHERE EXISTS (
SELECT 'history purgeable'
FROM ap_invoice_payments aip
, ap_purge_invoice_list PL
WHERE aip.invoice_id = PL.invoice_id
and aip.check_id = aph.check_id
and PL.double_check_flag = 'Y');
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_invoice_payments
WHERE invoice_id IN (
SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high);
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_payment_schedules
WHERE invoice_id IN (
SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high);
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_trial_balance
WHERE invoice_id IN (
SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high);
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_holds
WHERE invoice_id IN (
SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high);
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_inv_aprvl_hist
WHERE invoice_id IN (
SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high);
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_invoice_distributions
WHERE invoice_id IN (
SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high);
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_doc_sequence_audit AUD
WHERE (AUD.doc_sequence_id , AUD.doc_sequence_value)
IN (SELECT I.doc_sequence_id , I.doc_sequence_value
FROM ap_purge_invoice_list PL,
ap_invoices I
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high
AND PL.invoice_id = I.invoice_id);
--delete all related transactions: dists, holds, payment shedules, payments
AP_DBI_PKG.Maintain_DBI_Summary
(p_table_name => 'AP_INVOICES',
p_operation => 'D',
p_key_value1 => l_invoice_id,
p_calling_sequence => current_calling_sequence);
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_invoices
WHERE invoice_id IN (
SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND PL.invoice_id BETWEEN range_low AND range_high);
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_batches B
WHERE B.last_update_date <= g_activity_date
AND NOT EXISTS (
SELECT null
FROM ap_invoices I
WHERE I.batch_id = B.batch_id);
debug_info := 'Completed deleteing from Oracle Payables';
Print('(Delete_AP_Tables)'||debug_info);
END Delete_AP_Tables;
to_be_deleted_check_number NUMBER;
SELECT invoice_id
FROM ap_purge_invoice_list
WHERE double_check_flag = 'Y'
and invoice_id > low_inv_id
ORDER BY invoice_id asc;
SELECT nvl(min(invoice_id),-1)
, nvl(max(invoice_id),-1)
into range_low, range_high
FROM ap_purge_invoice_list
WHERE double_check_flag = 'Y';
DELETE FROM ap_liability_balance alb
WHERE EXISTS (
SELECT 'records exist'
FROM ap_purge_invoice_list pil
WHERE alb.invoice_id = pil.invoice_id
AND pil.double_check_flag = 'Y'
AND pil.invoice_id BETWEEN range_low
AND range_high)
AND journal_sequence_id IS NULL;
DELETE FROM ap_ae_lines ael
WHERE ael.ae_header_id in
( SELECT aeh.ae_header_id
FROM ap_ae_headers aeh
,ap_accounting_events aae
,ap_purge_invoice_list pil
WHERE aae.source_id = pil.invoice_id
and aae.source_table = 'AP_INVOICES'
and aae.accounting_event_id = aeh.accounting_event_id
and pil.double_check_flag = 'Y'
and pil.invoice_id BETWEEN range_low AND range_high) ;
DELETE FROM ap_ae_lines ael
WHERE ael.ae_header_id in
( SELECT aeh.ae_header_id
FROM ap_ae_headers aeh -- bug 2153117 added
,ap_accounting_events aae
,ap_invoice_payments aip
,ap_purge_invoice_list pil
WHERE aae.source_id = aip.check_id
and aae.source_table = 'AP_CHECKS'
and pil.double_check_flag = 'Y'
and aae.accounting_event_id = aeh.accounting_event_id
and aip.invoice_id = pil.invoice_id
and pil.invoice_id BETWEEN range_low AND range_high);
DELETE FROM ap_ae_headers aeh
WHERE aeh.accounting_event_id IN
( SELECT aae.accounting_event_id
FROM ap_accounting_events aae
, ap_purge_invoice_list pil
WHERE aae.source_id = pil.invoice_id
and aae.source_table = 'AP_INVOICES'
and pil.double_check_flag = 'Y'
-- Commented the below line as a fix for bug 2880690
-- and aae.accounting_event_id = aeh.accounting_event_id
and pil.invoice_id BETWEEN range_low AND range_high
) ;
DELETE FROM ap_ae_headers aeh
WHERE aeh.accounting_event_id in
( SELECT aae.accounting_event_id
FROM ap_accounting_events aae
, ap_invoice_payments aip
, ap_purge_invoice_list pil
-- bug2153117 removed
-- , ap_ae_headers aeh
WHERE aae.source_id = aip.check_id
and aae.source_table = 'AP_CHECKS'
and pil.double_check_flag = 'Y'
-- Commented the below line as a fix for bug 2880690
-- and aae.accounting_event_id = aeh.accounting_event_id
and aip.invoice_id = pil.invoice_id
and pil.invoice_id BETWEEN range_low AND range_high) ;
DELETE FROM ap_encumbrance_lines aen
WHERE EXISTS (
SELECT 'dist'
FROM ap_purge_invoice_list pil
, ap_invoice_distributions aid
WHERE aen.invoice_distribution_id = aid.invoice_distribution_id
and aid.invoice_id = pil.invoice_id
and pil.double_check_flag = 'Y'
and pil.invoice_id BETWEEN range_low AND range_high);
/* -- delete_ap_accounting_events
-- Fix for bug 2545172 , commented above delete statement and wrote
-- below 3 delete statement
DELETE FROM AP_ACCOUNTING_EVENTS AAE
WHERE aae.source_id in (SELECT PIL.INVOICE_ID
FROM AP_PURGE_INVOICE_LIST PIL
WHERE PIL.DOUBLE_CHECK_FLAG = 'Y'
AND PIL.INVOICE_ID BETWEEN range_low AND range_high )
AND AAE.SOURCE_TABLE = 'AP_INVOICES'
;
DELETE FROM AP_ACCOUNTING_EVENTS AAE WHERE
aae.source_id in ( SELECT APC.CHECK_ID
FROM AP_PURGE_INVOICE_LIST PIL,
AP_CHECKS APC,
AP_INVOICE_PAYMENTS AIP
WHERE PIL.DOUBLE_CHECK_FLAG = 'Y'
AND APC.CHECK_ID = AIP.CHECK_ID
AND AIP.INVOICE_ID = PIL.INVOICE_ID
AND PIL.INVOICE_ID BETWEEN range_low
AND range_high )
AND AAE.SOURCE_TABLE = 'AP_CHECKS' ;
DELETE FROM AP_ACCOUNTING_EVENTS AAE WHERE
AAE.source_id IN ( SELECT APH.CHECK_ID
FROM AP_PURGE_INVOICE_LIST PIL,
AP_INVOICE_PAYMENTS AIP,
AP_PAYMENT_HISTORY APH
WHERE PIL.DOUBLE_CHECK_FLAG = 'Y'
AND APH.CHECK_ID = AIP.CHECK_ID
AND AIP.INVOICE_ID = PIL.INVOICE_ID
AND PIL.INVOICE_ID BETWEEN range_low
AND range_high )
and AAE.SOURCE_TABLE = 'AP_PAYMENT_HISTORY' ;
SELECT schedule_item_id
FROM chv_purge_schedule_list
WHERE double_check_flag = 'Y'
and schedule_item_id > low_chv_id
ORDER BY schedule_item_id asc;
select nvl(min(schedule_item_id),-1)
, nvl(max(schedule_item_id),-1)
into range_low, range_high
from chv_purge_schedule_list
where double_check_flag = 'Y';
update chv_schedule_items csi
set csi.item_purge_status = 'PURGED'
where exists
(select null
from chv_purge_schedule_list cpsl
where cpsl.schedule_item_id = csi.schedule_item_id
and cpsl.double_check_flag = 'Y'
and cpsl.schedule_item_id between range_low and range_high);
delete from chv_item_orders cio
where exists
(select null
from chv_purge_schedule_list cpsl
where cpsl.schedule_item_id = cio.schedule_item_id
and cpsl.double_check_flag = 'Y'
and cpsl.schedule_item_id between range_low and range_high);
delete from chv_horizontal_Schedules chs
where exists
(select null
from chv_purge_schedule_list cpsl
where cpsl.schedule_item_id = chs.schedule_item_id
and cpsl.double_check_flag = 'Y'
and cpsl.schedule_item_id between range_low and range_high);
delete from chv_authorizations ca
where exists
(select null
from chv_purge_schedule_list cpsl
where cpsl.schedule_item_id = ca.reference_id
and ca.reference_type = 'SCHEDULE_ITEMS'
and cpsl.double_check_flag = 'Y'
and cpsl.schedule_item_id between range_low and range_high);
delete from chv_authorizations ca
where exists
(select null
from chv_purge_cum_list cpcl
where cpcl.cum_period_id = ca.reference_id
and cpcl.double_check_flag = 'Y'
and ca.reference_type = 'CUM_PERIODS');
delete from chv_cum_adjustments cca
where exists
(select null
from chv_purge_cum_list cpcl
where cpcl.cum_period_id = cca.cum_period_id
and cpcl.double_check_flag = 'Y');
delete from chv_cum_periods ccp
where exists
(select null
from chv_purge_cum_list cpcl
where cpcl.cum_period_id = ccp.cum_period_id
and cpcl.double_check_flag = 'Y');
delete from chv_schedule_items csi
where not exists (select null
from chv_schedule_items cs
where csi.schedule_id = cs.schedule_id
and nvl(cs.item_purge_status,'ACTIVE') <> 'PURGED');
delete from chv_schedule_headers csh
where not exists (select null
from chv_schedule_items csi
where csh.schedule_id = csi.schedule_id );
SELECT schedule_item_id
FROM chv_purge_schedule_list
WHERE double_check_flag = 'Y'
and schedule_item_id > low_chv_id
ORDER BY schedule_item_id asc;
select nvl(min(schedule_item_id),-1)
, nvl(max(schedule_item_id),-1)
into range_low, range_high
from chv_purge_schedule_list
where double_check_flag = 'Y';
update chv_schedule_items csi
set csi.item_purge_status = 'PURGED'
where exists
(select null
from chv_purge_schedule_list cpsl
where cpsl.schedule_item_id = csi.schedule_item_id
and cpsl.double_check_flag = 'Y'
and cpsl.schedule_item_id between range_low and range_high);
delete from chv_item_orders cio
where exists
(select null
from chv_purge_schedule_list cpsl
where cpsl.schedule_item_id = cio.schedule_item_id
and cpsl.double_check_flag = 'Y'
and cpsl.schedule_item_id between range_low and range_high);
delete from chv_horizontal_Schedules chs
where exists
(select null
from chv_purge_schedule_list cpsl
where cpsl.schedule_item_id = chs.schedule_item_id
and cpsl.double_check_flag = 'Y'
and cpsl.schedule_item_id between range_low and range_high);
delete from chv_authorizations ca
where exists
(select null
from chv_purge_schedule_list cpsl
where cpsl.schedule_item_id = ca.reference_id
and ca.reference_type = 'SCHEDULE_ITEMS'
and cpsl.double_check_flag = 'Y'
and cpsl.schedule_item_id between range_low and range_high);
delete from chv_schedule_items csi
where not exists (select null
from chv_schedule_items cs
where csi.schedule_id = cs.schedule_id
and nvl(cs.item_purge_status,'ACTIVE') <> 'PURGED');
delete from chv_schedule_headers csh
where not exists (select null
from chv_schedule_items csi
where csh.schedule_id = csi.schedule_id );
select vendor_id
from po_purge_vendor_list pvl
where pvl.double_check_flag = 'Y';
select vendor_id,
vendor_site_id
from po_vendor_sites_all
where vendor_id in (select vendor_id
from po_purge_vendor_list pvl
where pvl.double_check_flag = 'Y');
delete from ap_suppliers vnd
where exists
(select null
from po_purge_vendor_list pvl
where pvl.vendor_id = vnd.vendor_id
and pvl.double_check_flag = 'Y');
delete from ap_supplier_sites_all vnd
where exists
(select null
from po_purge_vendor_list pvl
where pvl.vendor_id = vnd.vendor_id
and pvl.double_check_flag = 'Y');
delete from ap_supplier_contacts pc
where not exists
(select null
from ap_supplier_sites_all ps
where ps.vendor_site_id = pc.vendor_site_id);
delete from ap_bank_account_uses_all abau
where exists
(select null
from po_purge_vendor_list pvl
where pvl.vendor_id = abau.vendor_id
and pvl.double_check_flag = 'Y');
delete from po_vendor_list_entries pvle
where not exists
(select null
from ap_suppliers vnd
where vnd.vendor_id = pvle.vendor_id);
delete from po_vendor_list_headers h
where not exists
(select null
from po_vendor_list_entries e
where e.vendor_list_header_id =
h.vendor_list_header_id);
delete from po_asl_documents pad where
exists (select null from po_asl_attributes paa,
po_purge_vendor_list pvl
where pvl.vendor_id = paa.vendor_id
and pvl.double_check_flag = 'Y'
and paa.using_organization_id = pad.using_organization_id
and paa.asl_id = pad.asl_id);
delete from po_asl_attributes paa
where exists
(select null
from po_purge_vendor_list pvl
where pvl.vendor_id = paa.vendor_id
and pvl.double_check_flag = 'Y');
delete from po_approved_supplier_list pasl
where exists
(select null
from po_purge_vendor_list pvl
where pvl.vendor_id = pasl.vendor_id
and pvl.double_check_flag = 'Y');
update mrp_sourcing_rules msr
set planning_active = 2
where exists (select null
from po_purge_vendor_list pvl,
mrp_sr_source_org msso,
mrp_sr_receipt_org msro
where pvl.vendor_id = msso.vendor_id
and msso.sr_receipt_id = msro.sr_receipt_id
and msro.sourcing_rule_id = msr.sourcing_rule_id
and pvl.double_check_flag = 'Y');
update mrp_recommendations mr
set source_vendor_id = null, source_vendor_site_id = null
where exists (select null
from po_purge_vendor_list pvl
where pvl.vendor_id = mr.source_vendor_id
and pvl.double_check_flag = 'Y');
delete from mrp_sr_source_org msso
where exists (select null
from po_purge_vendor_list pvl
where pvl.vendor_id = msso.vendor_id
and pvl.double_check_flag = 'Y');
delete from mrp_item_sourcing mis
where exists (select null
from po_purge_vendor_list pvl
where pvl.vendor_id = mis.vendor_id
and pvl.double_check_flag = 'Y');
Function: Delete_Seeded_Data
*==========================================================================*/
FUNCTION Delete_Seeded_Data
(P_Purge_Name IN VARCHAR2,
P_Category IN VARCHAR2,
P_activity_Date IN DATE,
P_Range_Size IN NUMBER,
P_Purchasing_Status IN VARCHAR2,
P_MRP_Status IN VARCHAR2,
P_Debug_Switch IN VARCHAR2,
P_Calling_Sequence IN VARCHAR2)
RETURN BOOLEAN IS
debug_info VARCHAR2(200);
current_calling_sequence := 'Delete_Seeded_Data<-'||P_Calling_Sequence;
debug_info := 'Starting Delete_Seeded_Data';
Print('(Delete_Seeded_Data)'||debug_info);
if (delete_ap_tables('Delete_Seeded_Data') <> TRUE) then
Print('delete_ap_tables failed!');
if (purge_accounting('Delete_Seeded_Data') <> TRUE) then
Print('purge_accounting failed!');
PO_AP_PURGE_GRP.delete_records
( p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
x_return_status => l_po_return_status,
x_msg_data => l_po_msg,
p_purge_name => p_purge_name,
p_purge_category => p_category,
p_range_size => p_range_size);
if (delete_ap_tables('Delete_Seeded_Data') <> TRUE) then
Print('delete_ap_tables failed!');
PO_AP_PURGE_GRP.delete_records
( p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
x_return_status => l_po_return_status,
x_msg_data => l_po_msg,
p_purge_name => p_purge_name,
p_purge_category => p_category,
p_range_size => p_range_size);
if (purge_accounting('Delete_Seeded_Data') <> TRUE) then
Print('purge_accounting failed!');
if (purge_vendors('Delete_Seeded_Data') <> TRUE) then
Print('purge_vendors failed!');
if (purge_schedules_by_org('Delete_Seeded_Data') <> TRUE) then
Print('purge_schedules_by_org failed!');
'Delete Seeded Data')
<> TRUE) then
Print('purge_schedules_by_org failed!');
if (purge_schedules_by_cum('Delete_Seeded_Data') <> TRUE) then
Print('purge_schedules_by_cum failed!');
'Delete Seeded Data')
<> TRUE) then
Print('purge_schedules_by_cum failed!');
UPDATE financials_purges
SET
ap_checks = nvl(ap_checks, 0) - check_rows,
ap_invoice_payments = nvl(ap_invoice_payments, 0) - invoice_payment_rows,
ap_invoices = nvl(ap_invoices, 0) - invoice_rows,
po_headers = nvl(po_headers, 0) - po_header_rows,
po_requisition_headers = nvl(po_requisition_headers, 0) - req_header_rows,
po_vendors = nvl(po_vendors, 0) - vendor_rows,
po_receipts = nvl(po_receipts, 0) - shipment_line_rows,
po_approved_supplier_list = nvl(po_approved_supplier_list,0) - po_asl_rows,
po_asl_attributes = nvl(po_asl_attributes,0) - po_asl_attr_rows,
po_asl_documents = nvl(po_asl_documents,0) - po_asl_doc_rows,
chv_authorizations = nvl(chv_authorizations,0) - chv_auth_rows,
chv_cum_adjustments = nvl(chv_cum_adjustments,0) - chv_cum_adj_rows,
chv_cum_periods = nvl(chv_cum_periods,0) - chv_cum_rows,
chv_horizontal_Schedules = nvl(chv_horizontal_schedules,0) - chv_hor_rows,
chv_item_orders = nvl(chv_item_orders,0) - chv_ord_rows,
chv_schedule_headers = nvl(chv_schedule_headers,0) - chv_head_rows,
chv_schedule_items = nvl(chv_schedule_items,0) - chv_item_rows,
ap_ae_lines = nvl(ap_ae_lines,0) - ae_line_rows,
ap_ae_headers = nvl(ap_ae_headers,0) - ae_header_rows,
ap_accounting_events = nvl(ap_accounting_events,0) - accounting_event_rows
WHERE purge_name = p_purge_name;
delete from ap_purge_invoice_list;
PO_AP_PURGE_GRP.delete_purge_lists
( p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'F',
x_return_status => l_po_return_status,
x_msg_data => l_po_msg,
p_purge_name => p_purge_name);
delete from po_purge_vendor_list;
delete from chv_purge_schedule_list;
delete from chv_purge_cum_list;
'Delete_Seeded_Data')
<> TRUE) then
Print('set_purge_status failed!');
END Delete_Seeded_Data;
delete from ap_history_checks
where purge_name = g_purge_name;
delete from chv_history_schedules
where purge_name = g_purge_name;
delete from chv_history_cum_periods
where purge_name = g_purge_name;
delete from chv_purge_schedule_list;
delete from chv_purge_cum_list;
delete from chv_history_schedules
where purge_name = g_purge_name;
delete from chv_purge_schedule_list;
delete from ap_history_invoices
where purge_name = g_purge_name;
delete from ap_purge_invoice_list;
delete from ap_history_invoice_payments ahp
where not exists (select null
from ap_history_invoices ahi
where ahi.invoice_id = ahp.invoice_id);
delete from po_history_vendors
where purge_name = g_purge_name;
delete from po_purge_vendor_list;
PO_AP_PURGE_GRP.delete_purge_lists
( p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
x_return_status => l_po_return_status,
x_msg_data => l_po_msg,
p_purge_name => p_purge_name);
PO_AP_PURGE_GRP.delete_history_tables
( p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
x_return_status => l_po_return_status,
x_msg_data => l_po_msg,
p_purge_name => p_purge_name);