Hi All,
I have below job running.
"BEGIN
IF '$CATEGORY' = 'SIMPLE REQUISITIONS' THEN AJ_APPO_VALIDATIONS.MAIN('$ACTIVITY_DATE',
$ORG_ID, '$PURGE_NAME','$CATEGORY');
COMMIT;
END IF;
END;"
I have package below :
create or replace PACKAGE BODY xxarc_aj_appo_validations_pkg
AS
PROCEDURE aj_ap_inst_flag_proc (
l_ap_installed_flag IN OUT VARCHAR2,
l_po_installed_flag IN OUT VARCHAR2,
l_inventory_installed_flag IN OUT VARCHAR2,
l_assets_installed_flag IN OUT VARCHAR2,
l_wip_installed_flag IN OUT VARCHAR2,
l_mrp_installed_flag IN OUT VARCHAR2,
l_pa_installed_flag IN OUT VARCHAR2,
l_chv_installed_flag IN OUT VARCHAR2,
l_edi_installed_flag IN OUT VARCHAR2,
l_pa_yes IN OUT VARCHAR2
)
IS
fnd_return BOOLEAN;
ap_flag VARCHAR2 (1);
po_flag VARCHAR2 (1);
inv_flag VARCHAR2 (1);
asset_flag VARCHAR2 (1);
wip_flag VARCHAR2 (1);
mrp_flag VARCHAR2 (1);
pa_flag VARCHAR2 (1);
chv_flag VARCHAR2 (1);
edi_flag VARCHAR2 (1);
dummy VARCHAR2 (1);
BEGIN
-- Get AP Installation Information
--
fnd_return := fnd_installation.get (200, 200, ap_flag, dummy);
IF (fnd_return = FALSE)
THEN
fnd_message.set_name ('SQLAP', 'AP_ALL_CANT_GET_INSTALL_INFO');
END IF;
IF (ap_flag = 'I')
THEN
l_ap_installed_flag := 'Y';
ELSE
l_ap_installed_flag := 'N';
END IF;
-- Get PO Installation Information
fnd_return := fnd_installation.get (201, 201, po_flag, dummy);
IF (fnd_return = FALSE)
THEN
fnd_message.set_name ('SQLAP', 'AP_ALL_CANT_GET_INSTALL_INFO');
END IF;
IF (po_flag = 'I')
THEN
l_po_installed_flag := 'Y';
ELSE
l_po_installed_flag := 'N';
END IF;
-- Get INVENTORY Installation Information
fnd_return := fnd_installation.get (401, 401, inv_flag, dummy);
IF (fnd_return = FALSE)
THEN
fnd_message.set_name ('SQLAP', 'AP_ALL_CANT_GET_INSTALL_INFO');
END IF;
IF (inv_flag = 'I')
THEN
l_inventory_installed_flag := 'Y';
ELSE
l_inventory_installed_flag := 'N';
END IF;
-- Get ASSETS Installation Information
fnd_return := fnd_installation.get (140, 140, asset_flag, dummy);
IF (fnd_return = FALSE)
THEN
fnd_message.set_name ('SQLAP', 'AP_ALL_CANT_GET_INSTALL_INFO');
END IF;
IF (asset_flag = 'I')
THEN
l_assets_installed_flag := 'Y';
ELSE
l_assets_installed_flag := 'N';
END IF;
-- Get WIP Installation Information
fnd_return := fnd_installation.get (706, 706, wip_flag, dummy);
IF (fnd_return = FALSE)
THEN
fnd_message.set_name ('SQLAP', 'AP_ALL_CANT_GET_INSTALL_INFO');
END IF;
IF (wip_flag = 'I')
THEN
l_wip_installed_flag := 'Y';
ELSE
l_wip_installed_flag := 'N';
END IF;
-- Get MRP Installation Information
fnd_return := fnd_installation.get (704, 704, mrp_flag, dummy);
IF (fnd_return = FALSE)
THEN
fnd_message.set_name ('SQLAP', 'AP_ALL_CANT_GET_INSTALL_INFO');
END IF;
IF (mrp_flag = 'I')
THEN
l_mrp_installed_flag := 'Y';
ELSE
l_mrp_installed_flag := 'N';
END IF;
-- Get PA Installation Information
fnd_return := fnd_installation.get (275, 275, pa_flag, dummy);
IF (fnd_return = FALSE)
THEN
fnd_message.set_name ('SQLAP', 'AP_ALL_CANT_GET_INSTALL_INFO');
END IF;
IF (pa_flag = 'I')
THEN
l_pa_installed_flag := 'Y';
ELSE
l_pa_installed_flag := 'N';
END IF;
-- Get CHV Installation Information
fnd_return := fnd_installation.get (202, 201, chv_flag, dummy);
IF (fnd_return = FALSE)
THEN
fnd_message.set_name ('SQLAP', 'AP_ALL_CANT_GET_INSTALL_INFO');
END IF;
IF (chv_flag = 'I')
THEN
l_chv_installed_flag := 'Y';
ELSE
l_chv_installed_flag := 'N';
END IF;
-- Get EDI Installation Information
fnd_return := fnd_installation.get (175, 175, edi_flag, dummy);
IF (fnd_return = FALSE)
THEN
fnd_message.set_name ('SQLAP', 'AP_ALL_CANT_GET_INSTALL_INFO');
END IF;
IF (chv_flag = 'I')
THEN
l_edi_installed_flag := 'Y';
ELSE
l_edi_installed_flag := 'N';
END IF;
SELECT meaning
INTO l_pa_yes
FROM fnd_lookups
WHERE lookup_type = 'YES_NO' AND lookup_code = 'Y';
END aj_ap_inst_flag_proc;
/* This program is used to populate initial list of eligible Invoices */
PROCEDURE seed_invoices (
p_purge_name IN VARCHAR2,
p_using_accrual_basis IN VARCHAR2,
p_using_cash_basis IN VARCHAR2,
p_activity_date IN DATE
)
IS
BEGIN
mo_global.set_policy_context ('S', g_org_id);
/* SELECT ALL PAID AND POSTED INVOICES INCLUDING PRE-PAYMENTS THAT FALL WITHIN THE GIVEN DATE */
INSERT INTO ap.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' /* PREPAYMENTS ARE REQUIRED TO BE ARCHIVED AT SNI */
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.accounting_date <= p_activity_date
-- AND I.invoice_date >= '01-AUG-2010' THIS HAS BEEN ADDED FOR CLAIMS DATE RANGE CHECK */
AND i.invoice_date <= p_activity_date
/* SELECT ALL ZERO AMOUNT INVOICES */
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.invoice_date <= p_activity_date
AND i.invoice_amount = 0
-- AND I.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT' /* PREPAYMENTS ARE REQUIRED TO BE ARCHIVED AT SNI */
GROUP BY i.invoice_id
HAVING SUM (NVL (d.amount, 0)) = 0;
DELETE FROM ap.ap_purge_invoice_list a
WHERE EXISTS (
SELECT 1
FROM ap.ap_invoices_all b, xxarc.xxarc_claims_tmp c
WHERE b.invoice_id = a.invoice_id
AND b.SOURCE = 'CLAIMS'
AND b.attribute8 = c.claim_id);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('The Error Message is : ' || SQLERRM);
END seed_invoices;
PROCEDURE seed_simple_req (
p_purge_name IN VARCHAR2,
p_last_activity_date IN DATE
)
IS
l_api_name CONSTANT VARCHAR2 (50) := 'SEED_SIMPLE_REQ';
BEGIN
INSERT INTO po.po_purge_req_list
(requisition_header_id, purge_name, double_check_flag)
SELECT prh.requisition_header_id, p_purge_name, 'Y'
FROM po_requisition_headers prh
WHERE prh.last_update_date <= p_last_activity_date
-- AND ( prh.closed_code IN ('FINALLY CLOSED', 'CLOSED') -- Commented by Sitha on 07-Apr-2014 as per ticket 3016 raised by SNI
-- Modified by Sitha on 07-Apr-2014 as per ticket 3016 raised by SNI
AND ( prh.closed_code = 'FINALLY CLOSED' OR prh.authorization_status = 'CANCELLED' )
AND NOT EXISTS (
SELECT NULL
FROM po_requisition_lines prl
WHERE prl.requisition_header_id =
prh.requisition_header_id
AND NVL (prl.modified_by_agent_flag, 'N') = 'N'
AND ( prl.last_update_date > p_last_activity_date
OR prl.line_location_id IS NOT NULL
OR prl.source_type_code = 'INVENTORY'
OR EXISTS (
SELECT NULL
FROM po_price_differentials ppd
WHERE ppd.entity_type = 'REQ LINE'
AND ppd.entity_id =
prl.requisition_line_id
AND ppd.last_update_date >
p_last_activity_date)
OR EXISTS (
SELECT NULL
FROM po_req_distributions prd
WHERE prd.requisition_line_id =
prl.requisition_line_id
AND prd.last_update_date >
p_last_activity_date)
));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('The Error Message is : ' || SQLERRM);
END seed_simple_req;
PROCEDURE seed_pos (
p_purge_name IN VARCHAR2,
p_category IN VARCHAR2,
p_last_activity_date IN DATE
)
IS
BEGIN
mo_global.set_policy_context ('S', g_org_id);
IF (po_code_release_grp.current_release <
po_code_release_grp.prc_11i_family_pack_j
)
THEN
IF (p_category = 'SIMPLE POS')
THEN
INSERT INTO po.po_purge_po_list
(po_header_id, purge_name, double_check_flag)
SELECT ph.po_header_id, p_purge_name, 'Y'
FROM po_headers ph
WHERE ph.type_lookup_code IN
('STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT')
AND ph.last_update_date <= p_last_activity_date
AND ( ph.closed_code IN ('CLOSED', 'FINALLY CLOSED')
OR ph.cancel_flag = 'Y'
)
AND NOT EXISTS (
SELECT NULL
FROM po_releases pr
WHERE pr.po_header_id = ph.po_header_id
AND pr.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM po_lines pl
WHERE pl.po_header_id = ph.po_header_id
AND pl.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM po_line_locations pll
WHERE pll.po_header_id = ph.po_header_id
AND pll.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM po_distributions pd
WHERE pd.po_header_id = ph.po_header_id
AND ( pd.last_update_date > p_last_activity_date
OR EXISTS (
SELECT NULL
FROM ap_invoice_distributions ad
WHERE ad.po_distribution_id =
pd.po_distribution_id)
))
AND NOT EXISTS (
SELECT NULL
FROM rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.last_update_date > p_last_activity_date)
AND po_ap_purge_grp.validate_purge (ph.po_header_id) = 'T';
ELSIF (p_category = 'MATCHED POS AND INVOICES')
THEN
INSERT INTO po.po_purge_po_list
(po_header_id, purge_name, double_check_flag)
SELECT ph.po_header_id, p_purge_name, 'Y'
FROM po_headers ph
WHERE ph.type_lookup_code IN
('STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT')
AND ph.last_update_date <= p_last_activity_date
AND ( ph.closed_code IN ('CLOSED', 'FINALLY CLOSED')
OR ph.cancel_flag = 'Y'
)
AND NOT EXISTS (
SELECT NULL
FROM po_releases pr
WHERE pr.po_header_id = ph.po_header_id
AND pr.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM po_lines pl
WHERE pl.po_header_id = ph.po_header_id
AND pl.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM po_line_locations pll
WHERE pll.po_header_id = ph.po_header_id
AND pll.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM po_distributions pd
WHERE pd.po_header_id = ph.po_header_id
AND pd.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.last_update_date > p_last_activity_date)
AND po_ap_purge_grp.validate_purge (ph.po_header_id) = 'T';
END IF; -- p_category
INSERT INTO po.po_purge_req_list
(requisition_header_id, purge_name, double_check_flag)
SELECT prh.requisition_header_id, p_purge_name, 'Y'
FROM po_requisition_headers prh
WHERE prh.last_update_date <= p_last_activity_date
/* AND (PRH.closed_code in ('CLOSED','FINALLY CLOSED')
OR PRH.authorization_status = 'CANCELLED') */
AND NOT EXISTS (
SELECT NULL
FROM po_requisition_lines prl
WHERE prl.requisition_header_id =
prh.requisition_header_id
AND NVL (prl.modified_by_agent_flag, 'N') = 'N'
AND ( prl.last_update_date > p_last_activity_date
OR prl.source_type_code = 'INVENTORY'
OR EXISTS (
SELECT NULL
FROM po_req_distributions prd
WHERE prd.requisition_line_id =
prl.requisition_line_id
AND prd.last_update_date >
p_last_activity_date)
));
ELSE
-- PO_CODE_RELEASE_GRP.Current_Release = PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J
IF p_category = 'SIMPLE POS'
THEN
INSERT INTO po.po_purge_po_list
(po_header_id, purge_name, double_check_flag)
SELECT ph.po_header_id, p_purge_name, 'Y'
FROM po_headers ph
WHERE ph.type_lookup_code IN
('STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT')
AND ph.last_update_date <= p_last_activity_date
AND ( ph.closed_code IN ('CLOSED', 'FINALLY CLOSED')
OR ph.cancel_flag = 'Y'
)
AND NOT EXISTS (
SELECT NULL
FROM po_releases pr
WHERE pr.po_header_id = ph.po_header_id
AND pr.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM po_lines pl
WHERE pl.po_header_id = ph.po_header_id
AND ( pl.last_update_date > p_last_activity_date
OR EXISTS (
SELECT NULL
FROM po_price_differentials ppd
WHERE ppd.entity_type IN
('PO LINE', 'BLANKET LINE')
AND ppd.entity_id = pl.po_line_id
AND ppd.last_update_date >
p_last_activity_date)
))
AND NOT EXISTS (
SELECT NULL
FROM po_line_locations pll
WHERE pll.po_header_id = ph.po_header_id
AND ( pll.last_update_date >
p_last_activity_date
OR EXISTS (
SELECT NULL
FROM po_price_differentials ppd
WHERE ppd.entity_type = 'PRICE BREAK'
AND ppd.entity_id =
pll.line_location_id
AND ppd.last_update_date >
p_last_activity_date)
))
AND NOT EXISTS (
SELECT NULL
FROM po_distributions pd
WHERE pd.po_header_id = ph.po_header_id
AND ( pd.last_update_date > p_last_activity_date
OR EXISTS (
SELECT NULL
FROM ap_invoice_distributions ad
WHERE ad.po_distribution_id =
pd.po_distribution_id)
))
AND NOT EXISTS (
SELECT NULL
FROM rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.last_update_date > p_last_activity_date);
ELSIF p_category = 'MATCHED POS AND INVOICES'
THEN
INSERT INTO po.po_purge_po_list
(po_header_id, purge_name, double_check_flag)
SELECT ph.po_header_id, p_purge_name, 'Y'
FROM po_headers ph
WHERE ph.type_lookup_code IN
('STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT')
AND ph.last_update_date <= p_last_activity_date
AND ( ph.closed_code IN ('CLOSED', 'FINALLY CLOSED')
OR ph.cancel_flag = 'Y'
)
AND NOT EXISTS (
SELECT NULL
FROM po_releases pr
WHERE pr.po_header_id = ph.po_header_id
AND pr.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM po_lines pl
WHERE pl.po_header_id = ph.po_header_id
AND ( pl.last_update_date > p_last_activity_date
OR EXISTS (
SELECT NULL
FROM po_price_differentials ppd
WHERE ppd.entity_type IN
('PO LINE', 'BLANKET LINE')
AND ppd.entity_id = pl.po_line_id
AND ppd.last_update_date >
p_last_activity_date)
))
AND NOT EXISTS (
SELECT NULL
FROM po_line_locations pll
WHERE pll.po_header_id = ph.po_header_id
AND ( pll.last_update_date >
p_last_activity_date
OR EXISTS (
SELECT NULL
FROM po_price_differentials ppd
WHERE ppd.entity_type = 'PRICE BREAK'
AND ppd.entity_id =
pll.line_location_id
AND ppd.last_update_date >
p_last_activity_date)
))
AND NOT EXISTS (
SELECT NULL
FROM po_distributions pd
WHERE pd.po_header_id = ph.po_header_id
AND pd.last_update_date > p_last_activity_date)
AND NOT EXISTS (
SELECT NULL
FROM rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.last_update_date > p_last_activity_date);
END IF;
INSERT INTO po.po_purge_req_list
(requisition_header_id, purge_name, double_check_flag)
SELECT prh.requisition_header_id, p_purge_name, 'Y'
FROM po_requisition_headers prh
WHERE prh.last_update_date <= p_last_activity_date
/* AND (PRH.closed_code in ('CLOSED','FINALLY CLOSED')
OR PRH.authorization_status = 'CANCELLED') */
AND NOT EXISTS (
SELECT NULL
FROM po_requisition_lines prl
WHERE prl.requisition_header_id =
prh.requisition_header_id
AND NVL (prl.modified_by_agent_flag, 'N') = 'N'
AND ( prl.last_update_date > p_last_activity_date
OR prl.source_type_code = 'INVENTORY'
OR EXISTS (
SELECT NULL
FROM po_price_differentials ppd
WHERE ppd.entity_type = 'REQ LINE'
AND ppd.entity_id =
prl.requisition_line_id
AND ppd.last_update_date >
p_last_activity_date)
OR EXISTS (
SELECT NULL
FROM po_req_distributions prd
WHERE prd.requisition_line_id =
prl.requisition_line_id
AND prd.last_update_date >
p_last_activity_date)
));
END IF;
-- PO_CODE_RELEASE_GRP.Current_Release < PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('The Error Message is : ' || SQLERRM);
END seed_pos;
/* This procedure takes the latest list of eligible invoices from the AP staging table and continues validating */
PROCEDURE do_independent_inv_checks (
p_activity_date IN DATE,
p_using_accrual_basis IN VARCHAR2,
p_using_cash_basis IN VARCHAR2,
p_recon_acctg_flag IN VARCHAR2,
p_category IN VARCHAR2,
p_pa_status IN VARCHAR2,
p_assets_status IN VARCHAR2
)
IS
BEGIN
mo_global.set_policy_context ('S', g_org_id);
--
-- Test Payment Schedules
/* Delete all Invoices whose payment schedule status is Not paid and Due date falls outside the given date */
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.due_date > p_activity_date
));
/* If Oracle Projects is installed, then the project related Invoices and expenses reports are not considered for archiving */
IF p_pa_status = 'Y'
THEN
--
-- Test PA Invoices
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');
END IF;
/* Execution block for Simple Invoices */
IF p_category = 'SIMPLE INVOICES'
THEN
-- Test Simple Invoice Distributions
--modified code on 11-04-12 Version V1 as per ap_system_parameters
/* Delete all Invoices whose distributions fall outside the given date range, not posted, Matched to POs */
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.accounting_date > p_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 (p_assets_status,
'N', 'U',
'cantequalme'
)
AND i.cancelled_date IS NULL
)
));
/* Execution block for Matched Invoices */
ELSE
-- Test All Invoice Distributions
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.accounting_date > p_activity_date
-- last_update_date being replaced by accounting_date
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 NULL
-- check added to eliminate simple invoices for Matched POs category
OR ( d.assets_addition_flag || '' =
DECODE (p_assets_status,
'N', 'U',
'cantequalme'
)
AND i.cancelled_date IS NULL
)
));
END IF;
-- Test Payments
/* Delete all Invoices whose payments are (not posted or accounting,check date fall outside the given date range, issued post dated
checks */
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 c.status_lookup_code <> 'RECONCILED'
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.accounting_date > p_activity_date
--last_update_date being replaced by accounting_date
OR c.check_date > p_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'
)
)
)
) > p_activity_date
)
)
));
/* This rule relating to Cash management has been relaxed as part of the standard code
OR
EXISTS (SELECT 'Referenced by cashbook'
from ce_statement_reconcils_all SR -- Modified from ce_statement_reconciliations SR in 11510 and added org_id join
where C.check_id=SR.reference_id
AND SR.reference_type= 'PAYMENT'
AND SR.org_id = C.org_id)));*/
-- Delete Inoivces that have applied Prepayments
-- Keep this Statement for Invoices upgrated from 11.0
/* This section is commented as Pre-payments need to be archived at SNI
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);
-- invoices with applied Prepayments
DELETE
FROM ap_purge_invoice_list PLpro
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 all Invoices whose payment history is not posted and accounting date falls outside the date range */
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
AND NVL (aph.posted_flag, 'N') <> 'Y'
AND aph.accounting_date > p_activity_date);
-- Fix for bug 2652768 made changes to below DELETE statement
-- Fix for bug 2961016 Added condition to check description is not MRC upgrade
/* Delete all Invoices whose related SubLedger Accounting Entries are not transfered to General Ledger */
DELETE FROM ap_purge_invoice_list pl
WHERE EXISTS (
SELECT 'invoice accounting not purgeable'
FROM xla.xla_events xe, --Bug 4588031
xla.xla_transaction_entities xte, --Bug 4588031
xla.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 > p_activity_date )))*/
OR (xeh.accounting_date > p_activity_date)
))
OR EXISTS (
SELECT 'payment accounting not purgeable'
FROM xla.xla_events xe, --Bug 4588031
xla.xla_transaction_entities xte, --Bug 4588031
ap_invoice_payments aip,
ap_system_parameters_all asp, --bug5052478
xla.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 > p_activity_date)));*/
OR (xeh.accounting_date > p_activity_date)
));
/* For Invoices matched to POs, last update date of Receiving transactions should be within the given date range */
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 > p_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')));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('The Error Message is : ' || SQLERRM);
END do_independent_inv_checks;
/* This is a recursive validation check for the latest eligible Invoices and checks.
The whole chain of invoices and checks should have satisfied all previous archive validations to be eligible for archiving.
After this is complete, the staging table would contain the finalized list of archive eligible Invoices
*/
PROCEDURE do_dependent_inv_checks
IS
TYPE tab_status_type IS TABLE OF VARCHAR2 (1)
INDEX BY BINARY_INTEGER;
tab_inv tab_status_type;
tab_check tab_status_type;
tab_clear tab_status_type;
CURSOR c_main
IS
SELECT pl.invoice_id
FROM ap_purge_invoice_list pl, ap_invoice_payments ip
WHERE pl.invoice_id = ip.invoice_id;
CURSOR c_main_check (l_invoice_id NUMBER)
IS
SELECT invoice_id
FROM ap_purge_invoice_list
WHERE invoice_id = l_invoice_id AND double_check_flag = 'Y';
p_count INTEGER;
p_id INTEGER;
l_cnt INTEGER;
l_invoice BOOLEAN;
l_dummy NUMBER;
FUNCTION check_check (l_invoice_id IN NUMBER)
RETURN BOOLEAN;
/* Get related invoice_id from check_id and check if the invoice_id is
in purge list. If there is, call check_check to get check_id which
is related to the invoice_id */
FUNCTION check_inv (l_check_id IN NUMBER)
RETURN BOOLEAN
IS
CURSOR c_inv
IS
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(+);
l_flag BOOLEAN := FALSE;
l_inv_id ap_purge_invoice_list.invoice_id%TYPE;
BEGIN
OPEN c_inv;
LOOP
FETCH c_inv
INTO l_inv_id;
EXIT WHEN c_inv%NOTFOUND;
/*