[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Event_ID,
Event_Type_Code,
Event_Date,
Event_Number,
Event_Status_Code,
Entity_Code,
Source_ID_Int_1
FROM XLA_Events_GT
WHERE (Entity_Code = 'AP_PAYMENTS'
OR Event_Type_Code IN ('PREPAYMENT APPLIED',
'PREPAYMENT UNAPPLIED',
'PREPAYMENT APPLICATION ADJ'))
AND Event_Status_Code <> 'N'
ORDER BY Entity_id, --Bug 9784405
Event_Number; --Bug 9784405
Delete_Hist_Dists (l_curr_calling_sequence);
SELECT ASP.Base_Currency_Code, AC.Currency_Code --8288996
INTO g_base_currency_code, l_check_curr_code
FROM AP_System_Parameters_All ASP,
AP_Checks_All AC
WHERE AC.Check_ID = l_xla_event_rec.source_id_int_1
AND AC.Org_ID = ASP.Org_ID;
SELECT ASP.Base_Currency_Code
INTO g_base_currency_code
FROM AP_System_Parameters_All ASP,
AP_Invoices_All AI
WHERE AI.Invoice_ID = l_xla_event_rec.source_id_int_1
AND AI.Org_ID = ASP.Org_ID;
-- adding the code below to skip executing Update_Gain_Loss_Ind
-- for the budgetary control events
--
BEGIN
SELECT nvl(xe.budgetary_control_flag, 'N') --BUG12594203
INTO l_budgetary_control_flag
FROM xla_events xe
WHERE xe.application_id = 200
AND xe.event_id = l_xla_event_rec.event_id;
l_log_msg := 'Calling procedure AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind';
AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind
(l_xla_event_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure AP_Acctg_Prepay_Dist_Pkg.Updated_Gain_Loss_Ind executed';
SELECT DISTINCT xeg.entity_id
, xeg.ledger_id
INTO l_entity_id
, l_ledger_id
FROM xla_events_gt xeg
WHERE xeg.event_id = l_xla_event_rec.event_id
AND xeg.application_id = 200;
PROCEDURE Delete_Hist_Dists
(P_Calling_Sequence IN VARCHAR2
) IS
l_curr_calling_sequence VARCHAR2(2000);
DELETE FROM AP_Payment_Hist_Dists
WHERE Accounting_Event_ID IN
(SELECT Event_ID
FROM XLA_Events_GT
WHERE Entity_Code = 'AP_PAYMENTS');
DELETE FROM AP_Prepay_App_Dists
WHERE Accounting_Event_ID IN
(SELECT Event_ID
FROM XLA_Events_GT
WHERE Event_Type_Code IN ('PREPAYMENT APPLICATION ADJ'));
END Delete_Hist_Dists;
SELECT SUM(APHD.Amount)
INTO l_pay_sum
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
AND APH.Related_Event_ID = P_Related_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'AWT'); --bug 9495429
SELECT SUM(APHD.Invoice_Dist_Amount)
INTO l_inv_dist_sum
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
AND APH.Related_Event_ID = P_Related_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'AWT'); --bug 9495429
SELECT SUM(APHD.Bank_Curr_Amount)
INTO l_bank_curr_sum
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
AND APH.Related_Event_ID = P_Related_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'AWT'); --bug 9495429
SELECT SUM(APAD.Amount)
INTO l_prepay_sum
FROM AP_Prepay_App_Dists APAD
WHERE APAD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
AND APAD.Prepay_App_Distribution_ID = P_Prepay_App_Dist_ID
AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX', 'AWT',
'EXCHANGE RATE VARIANCE');
SELECT SUM(APAD.Amount)
INTO l_tax_diff_sum
FROM AP_Prepay_App_Dists APAD
WHERE APAD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
AND APAD.Prepay_App_Distribution_ID = P_Prepay_App_Dist_ID
AND APAD.Prepay_Dist_Lookup_Code IN ('TAX DIFF');
SELECT SUM(APHD.Amount)
INTO l_discount_sum
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
AND APH.Related_Event_ID = P_Related_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
AND Pay_Dist_Lookup_Code = 'DISCOUNT';
SELECT SUM(APHD.Invoice_Dist_Amount)
INTO l_discount_sum
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
AND APH.Related_Event_ID = P_Related_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
AND Pay_Dist_Lookup_Code = 'DISCOUNT';
SELECT SUM(APHD.Bank_Curr_Amount)
INTO l_discount_sum
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = P_Invoice_Distribution_ID
AND APHD.Invoice_Payment_ID = P_Invoice_Payment_ID
AND APH.Related_Event_ID = P_Related_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APH.Posted_Flag <> 'N' -- changed for bug 7560247
AND Pay_Dist_Lookup_Code = 'DISCOUNT';
SELECT SUM(APHD.Amount),
SUM(APHD.Invoice_Dist_Amount),
SUM(APHD.Bank_Curr_Amount)
INTO l_pay_sum,
l_inv_dist_sum,
l_bank_curr_sum
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID in ( /*bug8882706*/
select p_invoice_distribution_id from dual
union
-- awt distributions which are applied on the p_invoice_distribution_id
select distinct aid_awt.invoice_distribution_id
from ap_invoice_distributions_all aid_awt,
ap_invoice_distributions_all aid_item
where 1=1
and aid_item.invoice_distribution_id = p_invoice_distribution_id
and aid_item.line_type_lookup_code <> 'AWT'
and aid_awt.invoice_id = aid_item.invoice_id
and aid_awt.awt_related_id = aid_item.invoice_distribution_id
and aid_awt.line_type_lookup_code = 'AWT'
)
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT')
AND NVL(APH.Posted_Flag, 'N') IN ('Y', 'S') --bug 7614480, added status 'S'
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
'PAYMENT CLEARING ADJUSTED')
AND NOT EXISTS
(SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL
WHERE APH_REL.check_id = APH.check_id --bug9282163
AND NVL(APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID) =
NVL(APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID)
AND APH_REL.REV_PMT_HIST_ID IS NOT NULL)
/*Bug 13908641*/
AND NOT EXISTS
(SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL
WHERE APH_REL.check_id = APH.check_id --bug9282163
AND APH_REL.REV_PMT_HIST_ID = APH.PAYMENT_HISTORY_ID
AND APH_REL.REV_PMT_HIST_ID IS NOT NULL);
SELECT SUM(APHD.Amount),
SUM(APHD.Invoice_Dist_Amount),
SUM(APHD.Bank_Curr_Amount)
INTO l_pay_sum,
l_inv_dist_sum,
l_bank_curr_sum
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID in ( /*bug8882706*/
select p_invoice_distribution_id from dual
union
-- awt distributions which are applied on p_invoice_distribution_id
select distinct aid_awt.invoice_distribution_id
from ap_invoice_distributions_all aid_awt,
ap_invoice_distributions_all aid_item
where 1=1
and aid_item.invoice_distribution_id = p_invoice_distribution_id
and aid_item.line_type_lookup_code <> 'AWT'
and aid_awt.invoice_id = aid_item.invoice_id
and aid_awt.awt_related_id = aid_item.invoice_distribution_id
and aid_awt.line_type_lookup_code = 'AWT'
)
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT') -- bug8882706
AND NVL(APH.Posted_Flag, 'N') IN ('Y', 'S') --bug 7614480, added status 'S'
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT MATURITY', 'PAYMENT MATURITY REVERSED',
'PAYMENT MATURITY ADJUSTED')
AND NOT EXISTS
(SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL
WHERE APH_REL.check_id = APH.check_id --bug9282163
AND NVL(APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID) =
NVL(APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID)
AND APH_REL.REV_PMT_HIST_ID IS NOT NULL)
/*Bug 13908641*/
AND NOT EXISTS
(SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL
WHERE APH_REL.check_id = APH.check_id --bug9282163
AND APH_REL.REV_PMT_HIST_ID = APH.PAYMENT_HISTORY_ID
AND APH_REL.REV_PMT_HIST_ID IS NOT NULL);
SELECT SUM(APHD.Amount),
SUM(APHD.Invoice_Dist_Amount),
SUM(APHD.Bank_Curr_Amount)
INTO l_pay_sum,
l_inv_dist_sum,
l_bank_curr_sum
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID in ( /*bug 8882706*/
select p_invoice_distribution_id from dual
union
-- awt distributions which are applied on p_invoice_distribution_id
select distinct aid_awt.invoice_distribution_id
from ap_invoice_distributions_all aid_awt,
ap_invoice_distributions_all aid_item
where 1=1
and aid_item.invoice_distribution_id = p_invoice_distribution_id
and aid_item.line_type_lookup_code <> 'AWT'
and aid_awt.invoice_id = aid_item.invoice_id
and aid_awt.awt_related_id = aid_item.invoice_distribution_id and aid_awt.line_type_lookup_code = 'AWT'
)
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT') -- bug8882706
AND NVL(APH.Posted_Flag, 'N') IN ('Y', 'S') --bug 7614480, added status 'S'
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
'MANUAL PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED',
'REFUND RECORDED', 'REFUND ADJUSTED', 'REFUND CANCELLED',
'MANUAL REFUND ADJUSTED')
AND NOT EXISTS
(SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL
WHERE APH_REL.check_id = APH.check_id --bug9282163
AND NVL(APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID) =
NVL(APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID)
AND APH_REL.REV_PMT_HIST_ID IS NOT NULL)
/*Bug 13908641*/
AND NOT EXISTS
(SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL
WHERE APH_REL.check_id = APH.check_id --bug9282163
AND APH_REL.REV_PMT_HIST_ID = APH.PAYMENT_HISTORY_ID
AND APH_REL.REV_PMT_HIST_ID IS NOT NULL);
SELECT SUM( APHD.Paid_Base_Amount )
, SUM( APHD.Invoice_Dist_Base_Amount )
, SUM( APHD.Cleared_Base_Amount )
INTO l_pay_sum
, l_inv_dist_sum
, l_bank_curr_sum
FROM AP_Payment_Hist_Dists APHD
, AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID IN
(SELECT p_invoice_distribution_id
FROM dual
UNION
SELECT DISTINCT aid_awt.invoice_distribution_id
FROM ap_invoice_distributions_all aid_awt
, ap_invoice_distributions_all aid_item
WHERE 1 = 1
AND aid_item.invoice_distribution_id = p_invoice_distribution_id
AND aid_item.line_type_lookup_code <> 'AWT'
AND aid_awt.invoice_id = aid_item.invoice_id
AND aid_awt.awt_related_id =
aid_item.invoice_distribution_id
AND aid_awt.line_type_lookup_code = 'AWT'
)
AND APHD.Pay_Dist_Lookup_Code IN( 'CASH', 'DISCOUNT', 'AWT' )
AND NVL( APH.Posted_Flag, 'N' ) IN( 'Y', 'S' )
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN( 'PAYMENT CLEARING',
'PAYMENT UNCLEARING', 'PAYMENT CLEARING ADJUSTED' )
AND NOT EXISTS
(SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL
WHERE APH_REL.check_id = APH.check_id
AND NVL( APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID )
= NVL( APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID )
AND APH_REL.REV_PMT_HIST_ID IS NOT NULL
);
SELECT SUM( APHD.Paid_Base_Amount )
, SUM( APHD.Invoice_Dist_Base_Amount )
, SUM( APHD.Cleared_Base_Amount )
INTO l_pay_sum
, l_inv_dist_sum
, l_bank_curr_sum
FROM AP_Payment_Hist_Dists APHD
, AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID IN
(SELECT p_invoice_distribution_id
FROM dual
UNION
SELECT DISTINCT aid_awt.invoice_distribution_id
FROM ap_invoice_distributions_all aid_awt
, ap_invoice_distributions_all aid_item
WHERE 1 = 1
AND aid_item.invoice_distribution_id = p_invoice_distribution_id
AND aid_item.line_type_lookup_code <> 'AWT'
AND aid_awt.invoice_id = aid_item.invoice_id
AND aid_awt.awt_related_id =
aid_item.invoice_distribution_id
AND aid_awt.line_type_lookup_code = 'AWT'
)
AND APHD.Pay_Dist_Lookup_Code IN( 'CASH', 'DISCOUNT', 'AWT' )
AND NVL( APH.Posted_Flag, 'N' ) IN( 'Y', 'S' )
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN( 'PAYMENT MATURITY',
'PAYMENT MATURITY REVERSED', 'PAYMENT MATURITY ADJUSTED' )
AND NOT EXISTS
(SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL
WHERE APH_REL.check_id = APH.check_id
AND NVL( APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID )
= NVL( APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID )
AND APH_REL.REV_PMT_HIST_ID IS NOT NULL
);
SELECT SUM( APHD.Paid_Base_Amount )
, SUM( APHD.Invoice_Dist_Base_Amount )
, SUM( APHD.Cleared_Base_Amount )
INTO l_pay_sum
, l_inv_dist_sum
, l_bank_curr_sum
FROM AP_Payment_Hist_Dists APHD
, AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID IN
(SELECT p_invoice_distribution_id
FROM dual
UNION
SELECT DISTINCT aid_awt.invoice_distribution_id
FROM ap_invoice_distributions_all aid_awt
, ap_invoice_distributions_all aid_item
WHERE 1 = 1
AND aid_item.invoice_distribution_id = p_invoice_distribution_id
AND aid_item.line_type_lookup_code <> 'AWT'
AND aid_awt.invoice_id = aid_item.invoice_id
AND aid_awt.awt_related_id =
aid_item.invoice_distribution_id
AND aid_awt.line_type_lookup_code = 'AWT'
)
AND APHD.Pay_Dist_Lookup_Code IN( 'CASH', 'DISCOUNT', 'AWT' )
AND NVL( APH.Posted_Flag, 'N' ) IN( 'Y', 'S' )
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN( 'PAYMENT CREATED', 'PAYMENT CANCELLED'
, 'PAYMENT ADJUSTED', 'MANUAL PAYMENT ADJUSTED',
'UPGRADED MANUAL PMT ADJUSTED', 'REFUND RECORDED', 'REFUND ADJUSTED',
'REFUND CANCELLED', 'MANUAL REFUND ADJUSTED' )
AND NOT EXISTS
(SELECT 'Event Reversed'
FROM AP_PAYMENT_HISTORY_ALL APH_REL
WHERE APH_REL.check_id = APH.check_id
AND NVL( APH_REL.RELATED_EVENT_ID, APH_REL.ACCOUNTING_EVENT_ID )
= NVL( APH.RELATED_EVENT_ID, APH.ACCOUNTING_EVENT_ID )
AND APH_REL.REV_PMT_HIST_ID IS NOT NULL
);
SELECT SUM(APAD.Amount)
INTO l_prepay_sum
FROM AP_Prepay_App_Dists APAD,
AP_Invoice_Distributions_All AID
WHERE APAD.Invoice_Distribution_ID in ( /*bug 8882706*/
select p_invoice_distribution_id from dual
union
/* awt distributions which are applied on the p_invoice_distribution_id*/
select distinct aid_awt.invoice_distribution_id
from ap_invoice_distributions_all aid_awt,
ap_invoice_distributions_all aid_item
where 1=1
and aid_item.invoice_distribution_id = p_invoice_distribution_id
and aid_item.line_type_lookup_code <> 'AWT'
and aid_awt.invoice_id = aid_item.invoice_id
and aid_awt.awt_related_id = aid_item.invoice_distribution_id
and aid_awt.line_type_lookup_code = 'AWT'
)
AND APAD.Prepay_App_Distribution_ID = AID.Invoice_Distribution_ID
AND NVL(AID.Reversal_Flag, 'N') <> 'Y' --bug9322001
AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX', 'AWT',
'EXCHANGE RATE VARIANCE');
SELECT SUM( APAD.Base_Amount )
, SUM( APAD.Base_Amt_At_Prepay_XRate )
, SUM( APAD.Base_Amt_At_Prepay_Clr_XRate )
INTO P_Inv_Dist_Base_Sum
, P_Paid_Base_Sum
, P_Clr_Base_Curr_Sum
FROM AP_Prepay_App_Dists APAD
WHERE APAD.Invoice_Distribution_ID IN
( SELECT p_invoice_distribution_id FROM dual
UNION
SELECT DISTINCT aid_awt.invoice_distribution_id
FROM ap_invoice_distributions_all aid_awt
, ap_invoice_distributions_all aid_item
WHERE 1 = 1
AND aid_item.invoice_distribution_id = p_invoice_distribution_id
AND aid_item.line_type_lookup_code <> 'AWT'
AND aid_awt.invoice_id = aid_item.invoice_id
AND aid_awt.awt_related_id = aid_item.invoice_distribution_id
AND aid_awt.line_type_lookup_code = 'AWT'
)
AND APAD.Prepay_Dist_Lookup_Code IN( 'PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX', 'AWT', 'EXCHANGE RATE VARIANCE' );
SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
--'EXCHANGE RATE VARIANCE', -1 * APHD.Amount,
APHD.Amount)
INTO l_paid_acctd_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Invoice_Distributions_All AID,
AP_Payment_History_All APH
WHERE AID.Invoice_ID = p_inv_rec.invoice_id
AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT') --bug 9265516, removed 'AWT'
AND APH.Posted_Flag IN ('Y', 'S') --bug 7614480, added status 'S'
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT CLEARING')
AND NOT EXISTS(SELECT 'reversed event'
FROM AP_PAYMENT_HISTORY_ALL APH_REV
WHERE APH_REV.check_id = APH.check_id --bug9282163
AND nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
= nvl(aph.related_event_id, aph.accounting_event_id)
AND aph_rev.rev_pmt_hist_id IS NOT NULL); --bug 7614480, added not exists
SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
--'EXCHANGE RATE VARIANCE', -1 * APHD.Amount,
APHD.Amount)
INTO l_paid_acctd_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Invoice_Distributions_All AID,
AP_Payment_History_All APH
WHERE AID.Invoice_ID = p_inv_rec.invoice_id
AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT') --bug 9265516, removed 'AWT'
AND APH.Posted_Flag IN ('Y', 'S') --bug 7614480, added status 'S'
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT MATURITY')
AND NOT EXISTS(SELECT 'reversed event'
FROM AP_PAYMENT_HISTORY_ALL APH_REV
WHERE APH_REV.check_id = APH.check_id --bug9282163
AND nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
= nvl(aph.related_event_id, aph.accounting_event_id)
AND aph_rev.rev_pmt_hist_id IS NOT NULL); --bug 7614480, added not exists
SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
--'EXCHANGE RATE VARIANCE', -1 * APHD.Amount,
APHD.Amount)
INTO l_paid_acctd_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Invoice_Distributions_All AID,
AP_Payment_History_All APH,
AP_INVOICE_PAYMENTS_ALL AIP
WHERE AID.Invoice_ID = p_inv_rec.invoice_id
AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT') --bug 9265516, removed 'AWT'
AND APH.Posted_Flag IN ('Y', 'S') --bug 7614480, added status 'S'
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT CREATED', 'MANUAL PAYMENT ADJUSTED',
'UPGRADED MANUAL PMT ADJUSTED', 'REFUND RECORDED',
'MANUAL REFUND ADJUSTED')
AND aphd.invoice_payment_id = aip.invoice_payment_id
AND aip.invoice_id = aid.invoice_id
AND aip.check_id = aph.check_id
AND nvl(aip.reversal_flag, 'N') <> 'Y'; --bug 7614480, added not exists
SELECT SUM(APAD.Amount)
INTO l_prepaid_acctd_amt
FROM AP_Prepay_App_Dists APAD,
AP_Invoice_Distributions_All AID,
AP_PREPAY_HISTORY_ALL APPH
WHERE AID.Invoice_ID = p_inv_rec.invoice_id
AND AID.Invoice_Distribution_ID = APAD.Invoice_Distribution_ID
AND APAD.prepay_history_id = APPH.PREPAY_HISTORY_ID
AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX') --bug 9265516, removed 'AWT'
AND NOT EXISTS( SELECT 'reversed prepay application'
FROM ap_invoice_distributions_all aidp
WHERE aidp.invoice_distribution_id = APAD.prepay_app_distribution_id
AND aidp.reversal_flag = 'Y'); --bug 7614480, added not exists
SELECT NVL(SUM(AID.amount), 0)
INTO l_inv_inc_prepay_tot
FROM ap_invoice_distributions_all AID
WHERE AID.invoice_id = p_inv_rec.invoice_id
AND AID.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
AND AID.prepay_distribution_id IS NOT NULL
AND AID.invoice_includes_prepay_flag = 'Y';
SELECT nvl(sum(amount),0) into l_total_awt
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id= p_inv_rec.invoice_id
AND aid.line_type_lookup_code ='AWT';
SELECT ai.invoice_id
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai,
xla_events_gt xe,
xla_events xle,
financials_system_params_all fsp
WHERE xe.event_type_code IN('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
AND xe.event_status_code NOT IN('N', 'P')
AND aid.accounting_event_id = xe.event_id
AND aid.prepay_distribution_id IS NOT NULL
AND aid.invoice_id = ai.invoice_id
AND aid.org_id = fsp.org_id
AND EXISTS
(SELECT 1
FROM gl_period_statuses glps
WHERE glps.application_id = 200
AND glps.set_of_books_id = ai.set_of_books_id
AND nvl(glps.adjustment_period_flag, 'N') = 'N'
AND ai.gl_date BETWEEN glps.start_date
AND glps.end_date
AND glps.migration_status_code = 'U')
AND xle.application_id = 200
AND xle.event_id = xe.event_id
AND xle.upg_batch_id IS NOT NULL
AND xle.upg_batch_id <> -9999
AND ((fsp.purch_encumbrance_flag = 'Y' AND
aid.match_status_flag = 'A') OR
(fsp.purch_encumbrance_flag = 'N' AND
aid.match_status_flag IN ('A','T')))
AND nvl(aid.posted_flag, 'N') <> 'Y'
AND aid.historical_flag = 'Y';
SELECT aid.invoice_distribution_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_invoice_id
AND aid.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
AND (aid.prepay_distribution_id IS NOT NULL OR
aid.prepay_tax_parent_id IS NOT NULL)
AND NVL(aid.posted_flag, 'N') <> 'Y'
AND aid.encumbered_flag = 'Y'
AND aid.historical_flag = 'Y'
AND aid.bc_event_id IS NULL;
UPDATE ap_invoice_distributions_all AID
SET aid.encumbered_flag = 'N'
WHERE aid.invoice_id = l_invoice_id
AND aid.invoice_distribution_id = l_prepay_dist_tab(j);
l_log_msg := 'Number of records updated to not encumbered '||l_rowcount;
UPDATE ap_invoice_distributions_all AID
SET aid.encumbered_flag = 'Y'
WHERE aid.invoice_id = l_invoice_id
AND aid.invoice_distribution_id = l_prepay_dist_tab(j);
l_log_msg := 'Number of records updated back to encumbered '||l_rowcount;
SELECT DECODE(COUNT(*), 0, 'N', 'Y')
INTO l_unacctg_events_exist
FROM DUAL
WHERE EXISTS(
SELECT 'unreversed, unaccounted payment not selected for accounting'
FROM ap_payment_history_all aph
, ap_invoice_payments_all aip
, xla_event_types_b xet
, xla_event_types_b xet_rel
, xla_events xe
WHERE aip.invoice_id = p_inv_rec.invoice_id
AND aph.check_id = aip.check_id
AND xe.event_id = aph.accounting_event_id
AND xet.event_type_code = p_xla_event_rec.event_type_code
AND xet_rel.event_class_code = xet.event_class_code
AND xet_rel.event_type_code = xe.event_type_code
AND xet.application_id = 200
AND xet_rel.application_id = 200
AND xe.application_id = 200
AND aph.posted_flag = 'N'
AND NOT EXISTS(
SELECT 'reversed event'
FROM ap_payment_history_all aph_rev
WHERE nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
= nvl(aph.related_event_id, aph.accounting_event_id)
AND aph_rev.check_id = aph.check_id /* bug12909730 */
AND aph_rev.rev_pmt_hist_id IS NOT NULL)
UNION
-- prepay application
SELECT 'unreversed, unvalidated prepayment application yet to be validated'
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai --added for bug13334090
WHERE aid.invoice_id = p_inv_rec.invoice_id
--AND nvl(aid.match_status_flag, 'N') = 'N' -- commented for bug13334090
AND aid.invoice_id = ai.invoice_id
AND AP_INVOICES_UTILITY_PKG.get_approval_status(ai.invoice_id,
ai.invoice_amount,
ai.payment_status_flag,
ai.invoice_type_lookup_code)
NOT IN ('APPROVED', 'CANCELLED', 'AVAILABLE', 'FULL') -- added for bug13334090
AND aid.prepay_distribution_id IS NOT NULL
AND nvl(aid.reversal_flag, 'N') <> 'Y'
AND nvl(aid.encumbered_flag, 'N') <> 'Y' -- added for bug13334090
AND aid.posted_flag <> 'Y' -- added for bug13334090
AND p_xla_event_rec.event_id IS NOT NULL -- added for bug13334090
UNION
-- prepay application adjustment
SELECT 'unreversed, unaccounted prepay adjustment not selected for accounting'
FROM ap_prepay_history_all apph
WHERE apph.posted_flag = 'N'
AND apph.invoice_id = p_inv_rec.invoice_id
AND apph.invoice_adjustment_event_id IS NOT NULL
AND NOT EXISTS(
SELECT 'reversed event'
FROM ap_invoice_distributions_all aid_rel
WHERE aid_rel.invoice_id = apph.invoice_id
AND aid_rel.accounting_event_id = apph.related_prepay_app_event_id
AND aid_rel.reversal_flag = 'Y'));
SELECT MAX(AID.INVOICE_DISTRIBUTION_ID)
INTO l_max_prepay_app_dist_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_inv_rec.invoice_id
--bug13334090 - commented below and added conditions on posted_flag and enc flag
--AND aid.match_status_flag = 'S'
AND aid.posted_flag <> 'Y'
AND NVL(aid.encumbered_flag, 'N') <> 'Y'
AND aid.prepay_distribution_id IS NOT NULL
AND NVL(reversal_flag, 'N') <> 'Y';
SELECT MAX(accounting_event_id)
INTO l_acctg_event_id
FROM
(
-- payment
SELECT MAX(aph.accounting_event_id) accounting_event_id
FROM ap_payment_history_all aph
, ap_invoice_payments_all aip
, xla_event_types_b xet
, xla_event_types_b xet_rel
, xla_events xe
, xla_events_gt xgt
WHERE aip.invoice_id = p_inv_rec.invoice_id
AND aph.check_id = aip.check_id
AND xe.event_id = aph.accounting_event_id
AND xet.event_type_code = p_xla_event_rec.event_type_code
AND xet_rel.event_class_code = xet.event_class_code
AND xet_rel.event_type_code = xe.event_type_code
AND xgt.event_id = xe.event_id
AND xet.application_id = 200
AND xet_rel.application_id = 200
AND xe.application_id = 200
AND aph.posted_flag = 'S'
AND NOT EXISTS(
SELECT 'reversed event'
FROM ap_payment_history_all aph_rev
WHERE nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
= nvl(aph.related_event_id, aph.accounting_event_id)
AND aph_rev.check_id = aph.check_id /* bug12909730 */
AND aph_rev.rev_pmt_hist_id IS NOT NULL)
UNION
-- prepay adjustment
SELECT MAX(apph.accounting_event_id) accounting_event_id
FROM ap_prepay_history_all apph,
xla_events_gt xgt
WHERE xgt.event_id = apph.accounting_event_id
AND apph.posted_flag = 'S'
AND apph.invoice_id = p_inv_rec.invoice_id
AND apph.invoice_adjustment_event_id IS NOT NULL
AND NOT EXISTS(
SELECT 'reversed event'
FROM ap_invoice_distributions_all aid_rel
WHERE aid_rel.invoice_id = apph.invoice_id
AND aid_rel.accounting_event_id = apph.related_prepay_app_event_id
AND aid_rel.reversal_flag = 'Y'));
select count(1)
into l_upg_appl_actg_for_prep_dist
from ap_invoice_distributions_all aid,
xla_ae_headers xah
where aid.prepay_distribution_id = p_prepay_distribution_id
and nvl(aid.reversal_flag, 'N') <> 'Y'
and aid.accounting_event_id = xah.event_id
and aid.set_of_books_id = xah.ledger_id
and xah.event_type_code IN ('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
and xah.accounting_entry_status_code = 'F'
and xah.upg_batch_id is not null
and xah.upg_batch_id <> -9999
and xah.application_id = 200
and rownum = 1;
select count(1)
into l_upg_pay_actg_for_inv
from ap_payment_history_all aph,
ap_system_parameters_all asp,
xla_ae_headers xah
where aph.check_id in
(select aip.check_id
from ap_invoice_payments_all aip
where aip.invoice_id = p_inv_rec.invoice_id
and nvl(aip.reversal_flag, 'N') <> 'Y'
)
and aph.transaction_type IN ('PAYMENT CLEARING',
'PAYMENT UNCLEARING',
'PAYMENT CLEARING ADJUSTED')
and not exists (select 'event reversed'
from ap_payment_history_all aph_rel
where aph_rel.check_id = aph.check_id
and nvl(aph_rel.related_event_id, aph_rel.accounting_event_id) =
nvl(aph.related_event_id, aph.accounting_event_id)
and aph_rel.rev_pmt_hist_id is not null)
and aph.org_id = asp.org_id
and aph.accounting_event_id = xah.event_id
and xah.ledger_id = asp.set_of_books_id
and xah.accounting_entry_status_code = 'F'
and xah.upg_batch_id is not null
and xah.upg_batch_id <> -9999
and xah.application_id = 200
and rownum = 1;
select count(1)
into l_upg_pay_actg_for_inv
from ap_payment_history_all aph,
ap_system_parameters_all asp,
xla_ae_headers xah
where aph.check_id in
(select aip.check_id
from ap_invoice_payments_all aip
where aip.invoice_id = p_inv_rec.invoice_id
and nvl(aip.reversal_flag, 'N') <> 'Y'
)
and aph.transaction_type IN ('PAYMENT MATURITY',
'PAYMENT MATURITY REVERSED',
'PAYMENT MATURITY ADJUSTED')
and not exists (select 'event reversed'
from ap_payment_history_all aph_rel
where aph_rel.check_id = aph.check_id
and nvl(aph_rel.related_event_id, aph_rel.accounting_event_id) =
nvl(aph.related_event_id, aph.accounting_event_id)
and aph_rel.rev_pmt_hist_id is not null)
and aph.org_id = asp.org_id
and aph.accounting_event_id = xah.event_id
and xah.ledger_id = asp.set_of_books_id
and xah.accounting_entry_status_code = 'F'
and xah.upg_batch_id is not null
and xah.upg_batch_id <> -9999
and xah.application_id = 200
and rownum = 1;
select count(1)
into l_upg_pay_actg_for_inv
from ap_payment_history_all aph,
ap_system_parameters_all asp,
xla_ae_headers xah
where aph.check_id in
(select aip.check_id
from ap_invoice_payments_all aip
where aip.invoice_id = p_inv_rec.invoice_id
and nvl(aip.reversal_flag, 'N') <> 'Y'
)
and aph.transaction_type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
'MANUAL PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED',
'REFUND RECORDED', 'REFUND ADJUSTED', 'REFUND CANCELLED',
'MANUAL REFUND ADJUSTED')
and not exists (select 'event reversed'
from ap_payment_history_all aph_rel
where aph_rel.check_id = aph.check_id
and nvl(aph_rel.related_event_id, aph_rel.accounting_event_id) =
nvl(aph.related_event_id, aph.accounting_event_id)
and aph_rel.rev_pmt_hist_id is not null)
and aph.org_id = asp.org_id
and aph.accounting_event_id = xah.event_id
and xah.ledger_id = asp.set_of_books_id
and xah.accounting_entry_status_code = 'F'
and xah.upg_batch_id is not null
and xah.upg_batch_id <> -9999
and xah.application_id = 200
and rownum = 1;
select count(1)
into l_upg_appl_actg_for_inv
from ap_invoice_distributions_all aid,
xla_ae_headers xah
where aid.invoice_id = p_inv_rec.invoice_id
and nvl(aid.reversal_flag, 'N') <> 'Y'
and aid.accounting_event_id = xah.event_id
and aid.set_of_books_id = xah.ledger_id
and xah.event_type_code IN ('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
and xah.accounting_entry_status_code = 'F'
and xah.upg_batch_id is not null
and xah.upg_batch_id <> -9999
and xah.application_id = 200
and rownum = 1;
select count(1)
into l_upg_pay_all_actg_for_inv
from ap_payment_history_all aph,
ap_system_parameters_all asp,
xla_ae_headers xah
where aph.check_id in
(select aip.check_id
from ap_invoice_payments_all aip
where aip.invoice_id = p_invoice_payment_id
)
and aph.org_id = asp.org_id
and aph.accounting_event_id = xah.event_id
and xah.ledger_id = asp.set_of_books_id
and xah.accounting_entry_status_code = 'F'
and xah.upg_batch_id is not null
and xah.upg_batch_id <> -9999
and xah.application_id = 200
and rownum = 1;