[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_Financials_Purges ( p_check_rows IN NUMBER DEFAULT 0,
p_invoice_payment_rows IN NUMBER DEFAULT 0,
p_invoice_rows IN NUMBER DEFAULT 0,
p_ae_line_rows IN NUMBER DEFAULT 0,
p_ae_header_rows IN NUMBER DEFAULT 0,
p_accounting_event_rows IN NUMBER DEFAULT 0,
p_invoice_lines_rows IN NUMBER DEFAULT 0, --bug 11829621
p_invoice_distributions_rows IN NUMBER DEFAULT 0, --bug 11829621
p_purge_name IN VARCHAR2 );
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 ;
SELECT MAX( invoice_id )
INTO max_inv_id
FROM ap_invoices ;
SELECT MAX( check_id )
INTO max_chk_id
FROM ap_invoice_payments ; */
SELECT last_number
INTO max_inv_id
FROM all_sequences
WHERE sequence_owner = l_sch_name
AND sequence_name = 'AP_INVOICES_S' ;
SELECT last_number
INTO max_chk_id
FROM all_sequences
WHERE sequence_owner = l_sch_name
AND sequence_name = 'AP_CHECKS_S' ;
tab_inv_vc2.DELETE ; -- Bug 8942883
tab_check_vc2.DELETE ; -- Bug 8942883
UPDATE ap_purge_invoice_list
SET double_check_flag = 'S'
WHERE invoice_id = p_id_vc2 ;
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 */
IF use_vc2 THEN -- Bug 8942883
p_count := tab_inv_vc2.count;
DELETE FROM ap_purge_invoice_list
WHERE invoice_id = p_id_vc2 ;
DELETE FROM ap_purge_invoice_list
WHERE invoice_id = p_id ;
/* delete unpurgeable list beforehand for performance */
IF use_vc2 THEN -- Bug 8942883
p_count := tab_check_vc2.count;
DELETE FROM ap_purge_invoice_list
WHERE invoice_id in ( select invoice_id
from ap_invoice_payments
where check_id = p_id_vc2);
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' ;
SELECT invoice_id
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.pa_addition_flag in ('Y','T'))
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 /*+ PARALLEL(pl) ROWID(pl) */
FROM ap_purge_invoice_list pl
WHERE pl.rowid IN
(SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(i,ps)
INDEX(i AP_INVOICES_U1) INDEX(ps AP_PAYMENT_SCHEDULES_U1) */
pl1.rowid
FROM ap_purge_invoice_list pl1,
ap_invoices i,
ap_payment_schedules ps
WHERE i.invoice_id = pl1.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 'Invoices are not transfered to PA'
FROM ap_invoices I,
ap_invoice_distributions D
WHERE I.invoice_id = PL.invoice_id
AND I.invoice_id = D.invoice_id
AND I.source = 'Oracle Project Accounting'
AND D.pa_addition_flag not in ('Y','T','E','Z') );
/* 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
WHERE invoice_id = l_invoice_id;
DELETE /*+ PARALLEL(pl) ROWID(pl) */
FROM ap_purge_invoice_list pl
WHERE pl.rowid IN
(SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(i,d)
INDEX(i AP_INVOICES_U1) INDEX(d AP_INVOICE_DISTRIBUTIONS_U1) */
pl1.rowid
FROM ap_purge_invoice_list pl1,
ap_invoices i,
ap_invoice_distributions d
WHERE i.invoice_id = pl1.invoice_id
AND i.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_tracking_flag = 'Y' /* bug 11707744 */
AND 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 /*+ no_unnest */ 'distributions not purgeable' -- 7759218
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_tracking_flag = 'Y' /* bug 11707744 */
AND D.assets_addition_flag||'' =
DECODE(g_assets_status,
'Y', 'U',
'cantequalme')
AND I.cancelled_date IS NULL)));
DELETE /*+ PARALLEL(pl) ROWID(pl) */
FROM ap_purge_invoice_list pl
WHERE pl.rowid IN
(SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(p,c)
INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(c AP_CHECKS_U1) */
pl1.rowid
FROM ap_purge_invoice_list pl1,
ap_invoice_payments p,
ap_checks c
WHERE p.invoice_id = pl1.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 (c.future_pay_due_date IS NOT NULL AND c.status_lookup_code = 'ISSUED')
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))
);
DELETE /*+ PARALLEL(pl) ROWID(pl) */
FROM ap_purge_invoice_list pl
WHERE pl.rowid IN
(SELECT /*+ ORDERED PARALLEL(pl1) FULL(pl1) USE_NL(p, sr)
INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(sr CE_STATEMENT_RECONS_N2) */
DISTINCT pl1.rowid
FROM ap_purge_invoice_list pl1,
ap_invoice_payments p,
ce_statement_reconciliations sr
WHERE p.invoice_id = pl1.invoice_id
AND p.check_id = sr.reference_id
AND sr.reference_type = 'PAYMENT'
);
DELETE
FROM ap_purge_invoice_list PL
WHERE EXISTS
(SELECT /*+ no_unnest */ 'related to prepayment' -- 7759218
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 /*+ PARALLEL(pl) ROWID(pl) */
FROM ap_purge_invoice_list PL
WHERE pl.rowid IN (
Select /*+ ORDERED PARALLEL(pl1) FULL(pl1) */
pl1.rowid -- 7759218
from ap_purge_invoice_list pl1,
ap_invoices_all ai,
xla_events xe, --Bug 4588031
xla_transaction_entities xte, --Bug 4588031
xla_ae_headers xeh, --Bug 4588031
ap_system_parameters_all asp--bug5052748
where xte.entity_code = 'AP_INVOICES'
and NVL(XTE.SOURCE_ID_INT_1,-99) = PL1.invoice_id --11059839
AND pl1.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 ))
UNION
Select /*+ ORDERED PARALLEL(pl1) FULL(pl1) */
pl1.rowid -- 7759218
from ap_purge_invoice_list pl1,
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 NVL(XTE.SOURCE_ID_INT_1,-99) = aip.check_id --11059839
and xte.entity_id = xe.entity_id
AND asp.set_of_books_id=xte.ledger_id
AND aip.org_id=asp.org_id
and PL1.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 /*+ no_unnest */ null -- 7759218
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);
SELECT NVL( approx_rows, 0 ),
NVL(pay_alone,'A')
INTO l_approx_rows,
l_pay_alone
FROM financials_purges
WHERE purge_name = p_purge_name ;
INSERT INTO ap_purge_invoice_list pl
(
invoice_id,
purge_name,
double_check_flag
)
WITH purge_inv AS
(SELECT /*+ MATERIALIZE INDEX(i AP_INVOICES_N5) */ rowid inv_rowid
FROM ap_invoices i
WHERE i.invoice_date <= g_activity_date
) ,
purge_zero_inv AS
(SELECT /*+ MATERIALIZE INDEX(i AP_INVOICES_N4)*/ rowid inv_rowid
FROM ap_invoices i
WHERE i.invoice_amount = 0
)
SELECT invoice_id, p_purge_name purge_name, 'Y' double_check_flag
FROM
(SELECT invoice_id, check_id
FROM
(SELECT /*+ ORDERED PARALLEL(PI) FULL(pi) USE_NL(i,p,c,d,l)
INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(c AP_CHECKS_U1) INDEX(d
AP_INVOICE_DISTRIBUTIONS_U1) INDEX(l AP_INVOICE_LINES_U1) */
i.invoice_id, MIN(c.check_id) check_id
FROM purge_inv pi,
ap_invoices i,
ap_invoice_payments p,
ap_checks c,
ap_invoice_distributions d,
ap_invoice_lines l
WHERE i.rowid = pi.inv_rowid
AND i.invoice_id = l.invoice_id
AND i.invoice_id = d.invoice_id
AND l.line_number = d.invoice_line_number -- bug14237038
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)
OR d.cash_posted_flag = DECODE(p_using_cash_basis, 'Y', 'Y', d.cash_posted_flag))
AND d.last_update_date <= g_activity_date
AND l.last_update_date <= g_activity_date
AND i.last_update_date <= g_activity_date
AND p.invoice_id = i.invoice_id
AND p.check_id = c.check_id
AND p.last_update_date <= g_activity_date
AND c.last_update_date <= g_activity_date
AND NVL(i.exclusive_payment_flag, 'N') = DECODE(l_pay_alone, 'N', 'N', 'A',
NVL(i.exclusive_payment_flag, 'N'), 'Y')
GROUP BY i.invoice_id
UNION
SELECT /*+ ORDERED PARALLEL(pzi) FULL(pzi) USE_NL(i,p,d)
INDEX(p AP_INVOICE_PAYMENTS_N1) INDEX(d AP_INVOICE_DISTRIBUTIONS_U1) */
i.invoice_id, NULL check_id
FROM purge_zero_inv pzi,
ap_invoices i,
ap_invoice_payments p,
ap_invoice_distributions d
WHERE i.rowid = pzi.inv_rowid
AND p.invoice_id (+) = i.invoice_id
AND i.invoice_id = d.invoice_id(+)
AND i.last_update_date <= g_activity_date
AND i.invoice_date <= g_activity_date
AND i.invoice_type_lookup_code <> 'PREPAYMENT'
AND p.check_id IS NULL
GROUP BY i.invoice_id
HAVING SUM(NVL(d.amount, 0)) = 0
)
ORDER BY DECODE(l_pay_alone, 'Y', invoice_id, 'N', check_id, check_id),
DECODE(l_pay_alone, 'Y', check_id, 'N', invoice_id, invoice_id)
)
WHERE(l_approx_rows = 0 OR rownum <= l_approx_rows) ;
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);
select nvl(pay_alone,'A')
into l_pay_alone
from financials_purges
where purge_name = P_Purge_Name ;
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';
SELECT invoice_id
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.pa_addition_flag in ('Y','T'))
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 '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 'lines no longer purgeable'
FROM ap_invoice_lines IL
WHERE PL.invoice_id = IL.invoice_id
AND IL.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 'Invoices are not transfered to PA'
FROM ap_invoices I,
ap_invoice_distributions D
WHERE I.invoice_id = PL.invoice_id
AND I.invoice_id = D.invoice_id
AND I.source <> 'Oracle Project Accounting'
AND D.pa_addition_flag not in ('Y','T','E','Z') );
/* 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 invoice_id = l_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*/ '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_tracking_flag = 'Y' /* bug 11707744 */
AND 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_tracking_flag = 'Y' /* bug 11707744 */
AND 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 NVL(XTE.SOURCE_ID_INT_1,-99) = PL.invoice_id /* Bug#12615876 */
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 (
/* Changed the subquery - bug 12955426. Now the delete stmt (in Do_independent_inv_checks())
and update stmt (in Retest_Invoice_Independents()) has same queries. */
Select 'payment accounting not purgeable' -- 7759218
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 NVL(XTE.SOURCE_ID_INT_1,-99) = aip.check_id --11059839
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)));
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 NVL(XTE.SOURCE_ID_INT_1,-99) = apc.check_id --Bug#12615876
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 ;
SELECT COUNT(*) into l_dummy
FROM ap_invoice_payments_all aip
WHERE check_id IN
(SELECT DISTINCT check_id
FROM ap_invoice_payments_all
WHERE invoice_id IN
(SELECT invoice_id
FROM ap_purge_invoice_list
WHERE double_check_flag = 'Y') )
AND NOT EXISTS
(SELECT 'CHECK'
FROM ap_purge_invoice_list pil
WHERE pil.invoice_id = aip.invoice_id)
AND ROWNUM =1;
SELECT MAX( invoice_id )
INTO max_inv_id
FROM ap_invoices ;
SELECT MAX( check_id )
INTO max_chk_id
FROM ap_invoice_payments ; */
SELECT last_number
INTO max_inv_id
FROM all_sequences
WHERE sequence_owner = l_sch_name
AND sequence_name = 'AP_INVOICES_S' ;
SELECT last_number
INTO max_chk_id
FROM all_sequences
WHERE sequence_owner = l_sch_name
AND sequence_name = 'AP_CHECKS_S' ;
tab_inv_vc2.DELETE ; -- Bug 8942883
tab_check_vc2.DELETE ; -- Bug 8942883
UPDATE ap_purge_invoice_list
SET double_check_flag = 'S'
WHERE invoice_id = p_id_vc2 ;
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 */
IF use_vc2 THEN -- Bug 8942883
p_count := tab_inv_vc2.count;
UPDATE ap_purge_invoice_list
SET double_check_flag = 'N'
WHERE invoice_id = p_id_vc2 ;
UPDATE ap_purge_invoice_list
SET double_check_flag = 'N'
WHERE invoice_id = p_id ;
/* delete unpurgeable list beforehand for performance */
IF use_vc2 THEN -- Bug 8942883
p_count := tab_check_vc2.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_vc2);
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!');
Bug 13799066 : Deleted the debug messages from the function
*==========================================================================*/
FUNCTION Overflow
(Overflow_Exist OUT NOCOPY VARCHAR2,
p_start_rowid IN ROWID , -- Bug 8913560 range_low IN NUMBER,
p_end_rowid IN ROWID , -- Bug 8913560 range_high IN NUMBER,
p_purge_name IN VARCHAR2, -- Bug 8913560
P_Calling_Sequence IN VARCHAR2)
RETURN BOOLEAN IS
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid;
to_be_deleted_check_number NUMBER;
OPEN overflow_select;
FETCH overflow_select into overflow_check_stock_id,
to_be_deleted_check_number;
EXIT WHEN overflow_select%NOTFOUND OR overflow_select%NOTFOUND IS NULL;
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';
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';
Update_Financials_Purges ( p_check_rows => l_check_rows ,
p_purge_name => p_purge_name ) ;
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,
p_start_rowid IN ROWID , -- Bug 8913560
p_end_rowid IN ROWID , -- Bug 8913560
p_purge_name IN VARCHAR2, -- Bug 8913560
P_rows_processed OUT NOCOPY NUMBER) -- Bug 9481539
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 -- Bug 8913560 PL.invoice_id BETWEEN low_inv_id AND high_inv_id
PL.rowid BETWEEN p_start_rowid AND p_end_rowid ;
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
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'
and PL.rowid BETWEEN p_start_rowid AND p_end_rowid); --9481539
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid);
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
Print('(Delete_AP_Tables)'||debug_info);
DELETE FROM ap_invoice_lines
WHERE invoice_id IN (
SELECT PL.invoice_id
FROM ap_purge_invoice_list PL
WHERE PL.double_check_flag = 'Y'
AND -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid
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 -- Bug 8913560 PL.invoice_id BETWEEN range_low AND range_high
PL.rowid BETWEEN p_start_rowid AND p_end_rowid );
Update_Financials_Purges ( p_check_rows => l_check_rows ,
p_invoice_payment_rows => l_invoice_payment_rows,
p_invoice_rows => l_invoice_rows ,
p_invoice_lines_rows => l_invoice_lines_rows , --bug 11829621
p_invoice_distributions_rows => l_invoice_distributions_rows, --bug 11829621
p_purge_name => g_purge_name ) ;
/* 9481539, moving this delete statement to parent process delete_seeded_data
debug_info := 'deleting from ap_batches';
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 /* Bug 8913560 pil.invoice_id BETWEEN range_low AND range_high */
pil.rowid BETWEEN p_start_rowid AND p_end_rowid )
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 /* Bug 8913560 pil.invoice_id BETWEEN range_low AND range_high */
pil.rowid BETWEEN p_start_rowid AND p_end_rowid);
/* -- 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_contacts pc
WHERE pc.org_party_site_id IN
( SELECT vnd.party_site_id
FROM ap_supplier_sites_all vnd
, 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);
Print('(Delete_Seeded_Data)'||debug_info);
SELECT count(*)
INTO ae_line_rows
FROM ap_ae_lines;
SELECT count(*)
INTO ae_header_rows
FROM ap_ae_headers;
SELECT count(*)
INTO accounting_event_rows
FROM ap_accounting_events;
UPDATE financials_purges
SET
ap_checks = check_rows,
ap_invoice_payments = invoice_payment_rows,
ap_invoices = invoice_rows,
ap_ae_lines = ae_line_rows,
ap_ae_headers = ae_header_rows,
ap_accounting_events = accounting_event_rows
WHERE purge_name = p_purge_name;
SELECT NVL( ap_checks, 0 ) ,
NVL( ap_invoice_payments, 0 ) ,
NVL( ap_invoices, 0 ) ,
NVL( ap_ae_lines, 0 ) ,
NVL( ap_ae_headers, 0 ) ,
NVL( ap_accounting_events, 0 )
INTO check_rows,
invoice_payment_rows,
invoice_rows,
ae_line_rows,
ae_header_rows,
accounting_event_rows
FROM financials_purges
WHERE purge_name = p_purge_name;
Print('(Delete_Seeded_Data)'||debug_info);
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!');
SELECT NVL( ap_checks, 0 ) ,
NVL( ap_invoice_payments, 0 ) ,
NVL( ap_invoices, 0 ) ,
NVL( ap_ae_lines, 0 ) ,
NVL( ap_ae_headers, 0 ) ,
NVL( ap_accounting_events, 0 )
INTO check_rows,
invoice_payment_rows,
invoice_rows,
ae_line_rows,
ae_header_rows,
accounting_event_rows
FROM financials_purges
WHERE purge_name = p_purge_name;
Print('(Delete_Seeded_Data)'||debug_info);
Print('(Delete_Seeded_Data)'||debug_info);
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_batches B
WHERE B.last_update_date <= g_activity_date
AND NOT EXISTS (
SELECT null
FROM ap_invoices_all I --bug13799066
WHERE I.batch_id = B.batch_id);
/* 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);
Purpose : This is called for Delete_Seeded_Data() and in turn, it spawns
multiple child requests to delete Payables and Accounting data
*==========================================================================*/
PROCEDURE Submit_Multiple_Requests ( p_purge_name IN VARCHAR2 ,
p_success OUT NOCOPY BOOLEAN ) IS -- Bug 9268290
l_debug_info VARCHAR2(2000);
SELECT COUNT(0)
INTO l_total_recs
FROM ap_purge_invoice_list
WHERE double_check_flag = 'Y';
purge_accounting() and delete_ap_tables() for deleting records
*==========================================================================*/
PROCEDURE Purge_acctg_and_ap_table
(errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2,
P_batch_size IN VARCHAR2,
P_Worker_Id IN NUMBER,
P_Num_Workers IN NUMBER,
P_Script_Name IN VARCHAR2,
P_Debug_Switch IN VARCHAR2 DEFAULT NULL,
p_purge_name IN VARCHAR2 DEFAULT NULL)
IS
l_debug_info VARCHAR2(2000);
l_mode VARCHAR2(30) := 'UPDATE';
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
p_script_name,
p_worker_id,
p_num_workers,
p_batch_size,
0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
p_batch_size,
TRUE);
if (delete_ap_tables('Purge_acctg_and_ap_table', l_start_rowid, l_end_rowid, p_script_name,l_rows_processed ) <> TRUE) then
Print('delete_ap_tables failed!');
Print('Invoices deleted -> '||to_char(l_rows_processed));
ad_parallel_updates_pkg.processed_rowid_range( l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
p_batch_size,
FALSE);
Print('Total invoices deleted in this worker -> '||to_char(l_final_count));
Procedure : Update_Financials_Purges
Purpose : Updates the financilas_purges table during the delete process of
accounting and ap tables that is done using multiple child requests
*==========================================================================*/
Procedure Update_Financials_Purges ( p_check_rows IN NUMBER DEFAULT 0,
p_invoice_payment_rows IN NUMBER DEFAULT 0,
p_invoice_rows IN NUMBER DEFAULT 0,
p_ae_line_rows IN NUMBER DEFAULT 0,
p_ae_header_rows IN NUMBER DEFAULT 0,
p_accounting_event_rows IN NUMBER DEFAULT 0,
p_invoice_lines_rows IN NUMBER DEFAULT 0, --bug 11829621
p_invoice_distributions_rows IN NUMBER DEFAULT 0, --bug 11829621
p_purge_name IN VARCHAR2 )
IS
PRAGMA AUTONOMOUS_TRANSACTION ;
UPDATE financials_purges
SET ap_checks = NVL(ap_checks , 0 ) + p_check_rows,
ap_invoice_payments = NVL(ap_invoice_payments , 0 ) + p_invoice_payment_rows,
ap_invoices = NVL(ap_invoices , 0 ) + p_invoice_rows,
ap_invoice_lines = NVL(ap_invoice_lines , 0 ) + p_invoice_lines_rows, --bug 11829621
ap_invoice_distributions = NVL(ap_invoice_distributions , 0 ) + p_invoice_distributions_rows, --bug 11829621
ap_ae_lines = NVL(ap_ae_lines , 0 ) + p_ae_line_rows,
ap_ae_headers = NVL(ap_ae_headers , 0 ) + p_ae_header_rows,
ap_accounting_events = NVL(ap_accounting_events, 0 ) + p_accounting_event_rows
WHERE purge_name = p_purge_name;