[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Num_Cancelled OUT NOCOPY NUMBER,
P_Num_Not_Cancelled OUT NOCOPY NUMBER,
P_Calling_Module IN VARCHAR2 Default 'SQLAP',
P_Calling_Sequence IN VARCHAR2,
X_return_status OUT NOCOPY VARCHAR2,
X_msg_count OUT NOCOPY NUMBER,
X_msg_data OUT NOCOPY VARCHAR2)
IS
-- Cursor to insert reversing invoice payments. We swap gain and
-- loss ccids. This tricks posting into making the reversal to
-- the gain/loss account used for the original payment.
CURSOR c_new_payments IS
SELECT AIP.invoice_payment_id invoice_payment_id,
ap_invoice_payments_s.nextval new_invoice_payment_id,
AIP.invoice_id invoice_id,
AIP.payment_num payment_num,
AIP.check_id check_id,
0-NVL(AIP.amount,0) amount,
AIP.set_of_books_id set_of_books_id,
DECODE(AIP.discount_taken
,'','',
0-NVL(AIP.discount_taken,0)) discount_taken,
DECODE(AIP.discount_lost
,'','',
0-NVL(AIP.discount_lost,0)) discount_lost,
AIP.exchange_rate_type exchange_rate_type,
AIP.exchange_rate exchange_rate,
AIP.exchange_date exchange_date,
DECODE(AIP.invoice_base_amount
,'','',0-NVL(AIP.invoice_base_amount,0))
invoice_base_amount,
DECODE(AIP.payment_base_amount
,'','',
0-NVL(AIP.payment_base_amount,0))
payment_base_amount,
AIP.gain_code_combination_id gain_code_combination_id,
AIP.loss_code_combination_id loss_code_combination_id,
AIP.accts_pay_code_combination_id accts_pay_code_combination_id,
AIP.future_pay_code_combination_id future_pay_code_combination_id,
AI.vendor_id vendor_id,
AIP.assets_addition_flag assets_addition_flag,
AIP.attribute1,
AIP.attribute2,
AIP.attribute3,
AIP.attribute4,
AIP.attribute5,
AIP.attribute6,
AIP.attribute7,
AIP.attribute8,
AIP.attribute9,
AIP.attribute10,
AIP.attribute11,
AIP.attribute12,
AIP.attribute13,
AIP.attribute14,
AIP.attribute15,
AIP.attribute_category,
AIP.global_attribute1,
AIP.global_attribute2,
AIP.global_attribute3,
AIP.global_attribute4,
AIP.global_attribute5,
AIP.global_attribute6,
AIP.global_attribute7,
AIP.global_attribute8,
AIP.global_attribute9,
AIP.global_attribute10,
AIP.global_attribute11,
AIP.global_attribute12,
AIP.global_attribute13,
AIP.global_attribute14,
AIP.global_attribute15,
AIP.global_attribute16,
AIP.global_attribute17,
AIP.global_attribute18,
AIP.global_attribute19,
AIP.global_attribute20,
AIP.global_attribute_category,
AIP.org_id /* Bug 4759178, added org_id */
FROM ap_invoice_payments AIP,
ap_invoices AI
WHERE AIP.check_id = P_Check_Id
AND AIP.invoice_id = AI.invoice_id
AND nvl(AIP.reversal_flag, 'N') <> 'Y';
SELECT invoice_id
FROM ap_invoice_payments
WHERE check_id = P_Check_Id
AND nvl(reversal_flag, 'N') <> 'Y'
GROUP BY invoice_id;
SELECT invoice_id,
payment_num
FROM ap_invoice_payments
WHERE check_id = P_Check_Id
AND nvl(reversal_flag, 'N') <> 'Y'
GROUP BY invoice_id, payment_num;
SELECT aid.invoice_id invoice_id,
aid.dist_code_combination_id dist_code_combination_id,
ap_invoice_distributions_s.NEXTVAL invoice_distribution_id,
aid.invoice_line_number invoice_line_number, /* bug 5169128 */
aid.invoice_distribution_id parent_reversal_id, -- 2806074
aid.set_of_books_id set_of_books_id,
aid.amount * -1 amount,
aid.line_type_lookup_code line_type_lookup_code,
aid.base_amount * -1 base_amount,
alc.displayed_field || ' '|| aid.description description,
DECODE(gl.account_type, 'A', 'Y', 'N') assets_tracking_flag,
aid.accts_pay_code_combination_id accts_pay_code_combination_id,
-- Bug 4277744 - Removed references to USSGL
-- aid.ussgl_transaction_code ussgl_transaction_code,
aid.org_id org_id,
aid.type_1099 type_1099,
aid.income_tax_region income_tax_region
FROM ap_invoice_distributions aid,
gl_code_combinations gl,
ap_invoice_payments aip,
ap_invoice_relationships air,
ap_lookup_codes alc
WHERE air.related_invoice_id = aid.invoice_id
AND gl.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id = aip.invoice_id
AND aip.check_id = P_Check_Id
AND aip.amount > 0
AND alc.lookup_type = 'NLS TRANSLATION'
AND alc.lookup_code = 'VOID'
AND NVL(aip.reversal_flag, 'N') <> 'Y';
SELECT DISTINCT AIP.invoice_id
, AIP.org_id /* Bug 3700128. MOAC PRoject */
FROM ap_invoice_payments AIP
WHERE AIP.check_id = P_check_id
AND nvl(AIP.reversal_flag, 'N') <> 'Y'
AND NOT EXISTS
(SELECT 'Invoice already has this hold'
FROM ap_holds AH
WHERE AH.invoice_id = AIP.invoice_id
AND AH.hold_lookup_code = P_Hold_Code
AND AH.release_lookup_code IS NULL)
AND NOT EXISTS (SELECT 'Invoice is an Interest Invoice' -- 3240962
FROM ap_invoices AI
WHERE AI.invoice_id = AIP.invoice_id
AND AI.invoice_type_lookup_code = 'INTEREST');
SELECT set_of_books_id
INTO l_set_of_books_id
FROM ap_invoice_payments
WHERE check_id = P_check_id
AND ROWNUM < 2;
SELECT payment_type_flag
INTO l_netting_type
FROM ap_checks
WHERE check_id = p_check_id;
AP_PAY_INVOICE_PKG.AP_PAY_UPDATE_PAYMENT_SCHEDULE(
l_invoice_id,
l_payment_num,
P_Check_Id,
NULL,
NULL,
'Y',
'REV',
P_Replace_Flag,
P_Last_Updated_By,
SYSDATE,
l_curr_calling_sequence);
AP_PAY_INVOICE_PKG.AP_PAY_UPDATE_AP_INVOICES (
l_invoice_id,
P_Check_Id,
NULL,
NULL,
'Y',
'REV',
P_Replace_Flag,
SYSDATE,
P_Last_Updated_By,
l_curr_calling_sequence);
l_debug_info := 'Update ap_payment_schedules';
UPDATE ap_payment_schedules_all aps
SET aps.last_updated_by = P_Last_Updated_By,
aps.gross_amount = 0,
aps.last_update_date = SYSDATE,
aps.amount_remaining = 0
WHERE aps.invoice_id IN (SELECT related_invoice_id
FROM ap_invoice_relationships air,
ap_invoice_payments_all aip
WHERE aip.check_id = P_Check_Id
AND air.related_invoice_id = aip.invoice_id
AND nvl(aip.reversal_flag, 'N') <> 'Y')
RETURNING aps.invoice_id
BULK COLLECT INTO l_dbi_key_value_list2;
l_debug_info := 'Update ap_invoices for Interest invoice';
UPDATE ap_invoices_all AI
SET AI.description = 'VOID '||AI.description,
AI.invoice_amount = 0,
AI.amount_paid = 0,
AI.invoice_distribution_total = 0,
AI.cancelled_date = sysdate, --bug5631957
AI.pay_curr_invoice_amount = 0 --bug5631957
WHERE AI.invoice_id IN
(SELECT AIR.related_invoice_id
FROM ap_invoice_relationships AIR,
ap_invoice_payments_all AIP
WHERE AIP.invoice_id = AIR.related_invoice_id
AND AIP.check_id = P_Check_Id
AND NVL(aip.reversal_flag, 'N') <> 'Y')
RETURNING invoice_id
BULK COLLECT INTO l_dbi_key_value_list1;
l_debug_info := 'Update ap_invoice_lines for Interest invoice';
UPDATE ap_invoice_lines_all AIL
SET AIL.description = 'VOID '||AIL.description,
AIL.amount = 0,
AIL.base_amount = 0
WHERE AIL.invoice_id IN
(SELECT AIR.related_invoice_id
FROM ap_invoice_relationships AIR,
ap_invoice_payments_all AIP
WHERE AIP.invoice_id = AIR.related_invoice_id
AND AIP.check_id = P_Check_Id
AND NVL(aip.reversal_flag, 'N') <> 'Y');
l_debug_info := 'INSERT ap_invoice_distributions for Interest Invoice';
SELECT MAX(aid.distribution_line_number)
INTO l_max_dist_line_num
FROM ap_invoice_distributions aid,
gl_code_combinations gl,
ap_invoice_payments aip,
ap_invoice_relationships air,
ap_lookup_codes alc
WHERE air.related_invoice_id = aid.invoice_id
AND gl.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id = aip.invoice_id
AND aip.check_id = P_Check_Id
AND aip.amount > 0
AND alc.lookup_type = 'NLS TRANSLATION'
AND alc.lookup_code = 'VOID'
AND NVL(aip.reversal_flag, 'N') <> 'Y';
INSERT INTO ap_invoice_distributions_all
(INVOICE_ID,
DIST_CODE_COMBINATION_ID,
INVOICE_DISTRIBUTION_ID,
INVOICE_LINE_NUMBER, /* bug 5169128 */
LAST_UPDATED_BY,
ASSETS_ADDITION_FLAG,
ACCOUNTING_DATE,
PERIOD_NAME,
SET_OF_BOOKS_ID,
AMOUNT,
POSTED_FLAG,
CASH_POSTED_FLAG,
ACCRUAL_POSTED_FLAG,
MATCH_STATUS_FLAG,
DISTRIBUTION_LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
BASE_AMOUNT,
LAST_UPDATE_DATE,
DESCRIPTION,
PA_ADDITION_FLAG,
CREATED_BY,
CREATION_DATE,
ASSETS_TRACKING_FLAG,
ACCTS_PAY_CODE_COMBINATION_ID,
-- USSGL_TRANSACTION_CODE, - Bug 4277744
ORG_ID,
DIST_MATCH_TYPE,
DISTRIBUTION_CLASS,
AMOUNT_TO_POST,
BASE_AMOUNT_TO_POST,
POSTED_AMOUNT,
POSTED_BASE_AMOUNT,
UPGRADE_POSTED_AMT,
UPGRADE_BASE_POSTED_AMT,
ROUNDING_AMT,
ACCOUNTING_EVENT_ID,
ENCUMBERED_FLAG,
PACKET_ID,
-- USSGL_TRX_CODE_CONTEXT, - Bug 4277744
REVERSAL_FLAG,
PARENT_REVERSAL_ID,
CANCELLATION_FLAG,
ASSET_BOOK_TYPE_CODE,
ASSET_CATEGORY_ID,
LAST_UPDATE_LOGIN,
--Freight and Special Charges
RCV_CHARGE_ADDITION_FLAG,
TYPE_1099,
INCOME_TAX_REGION)
VALUES
(Interest_Inv_Cur.invoice_id,
Interest_Inv_Cur.dist_code_combination_id,
Interest_Inv_Cur.invoice_distribution_id,
Interest_Inv_Cur.invoice_line_number, /* bug 5169128 */
P_Last_Updated_By,
'U',
P_reversal_Date,
P_reversal_Period_Name,
Interest_Inv_Cur.set_of_books_id,
Interest_Inv_Cur.amount,
'N',
'N',
'N',
'A',
l_max_dist_line_num,
Interest_Inv_Cur.line_type_lookup_code,
Interest_Inv_Cur.base_amount,
SYSDATE,
Interest_Inv_Cur.description,
'E',
P_Last_Updated_By,
SYSDATE,
Interest_Inv_Cur.assets_tracking_flag,
Interest_Inv_Cur.accts_pay_code_combination_id,
-- Interest_Inv_Cur.ussgl_transaction_code, - Bug 4277744
Interest_Inv_Cur.org_id,
'MATCH_STATUS',
'PERMANENT',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
NULL,
-- NULL, - Bug 4277744
NULL,
Interest_Inv_Cur.parent_reversal_id, --2806074
NULL,
NULL,
NULL,
P_last_update_login,
'N',
Interest_Inv_Cur.type_1099,
Interest_Inv_Cur.income_tax_region);
SELECT
payment_type_flag,
amount,
currency_code,
exchange_rate_type,
exchange_date,
exchange_rate,
base_amount,
org_id
INTO
l_payment_type_flag,
l_amount,
l_currency_code,
l_exchange_rate_type,
l_exchange_date,
l_exchange_rate,
l_base_amount,
l_org_id
FROM
ap_checks
WHERE
check_id = p_check_id;
P_Last_Updated_By,
P_Last_Update_Login,
P_Last_Updated_By,
SYSDATE,
NULL,
NULL,
NULL,
NULL,
l_curr_calling_sequence);
SELECT max(accounting_event_id)
INTO l_old_accounting_event_id
FROM AP_INVOICE_PAYMENTS AIP
WHERE check_id = P_check_id
AND posted_flag = 'N';
SELECT MAX(Payment_History_ID)
INTO l_rev_pmt_hist_id
FROM AP_Payment_History APH
WHERE APH.Check_ID = P_Check_ID
AND APH.Transaction_Type = 'PAYMENT CREATED';
SELECT MAX(Payment_History_ID)
INTO l_rev_pmt_hist_id
FROM AP_Payment_History APH
WHERE APH.Check_ID = P_Check_ID
AND APH.Transaction_Type = 'PAYMENT CREATED';
SELECT MAX(Payment_History_ID)
INTO l_rev_pmt_hist_id
FROM AP_Payment_History APH
WHERE APH.Check_ID = P_Check_ID
AND APH.Transaction_Type = 'REFUND RECORDED';
AP_RECONCILIATION_PKG.insert_payment_history
(
x_check_id => p_check_id,
x_transaction_type => l_transaction_type,
x_accounting_date => p_reversal_date,
x_trx_bank_amount => NULL,
x_errors_bank_amount => NULL,
x_charges_bank_amount => NULL,
x_bank_currency_code => NULL,
x_bank_to_base_xrate_type => NULL,
x_bank_to_base_xrate_date => NULL,
x_bank_to_base_xrate => NULL,
x_trx_pmt_amount => l_amount,
x_errors_pmt_amount => NULL,
x_charges_pmt_amount => NULL,
x_pmt_currency_code => l_currency_code,
x_pmt_to_base_xrate_type => l_exchange_rate_type,
x_pmt_to_base_xrate_date => l_exchange_date,
x_pmt_to_base_xrate => l_exchange_rate,
x_trx_base_amount => l_base_amount,
x_errors_base_amount => NULL,
x_charges_base_amount => NULL,
x_matched_flag => NULL,
x_rev_pmt_hist_id => l_rev_pmt_hist_id,
x_org_id => l_org_id, -- 4578865
x_creation_date => SYSDATE,
x_created_by => p_last_updated_by,
x_last_update_date => SYSDATE,
x_last_updated_by => p_last_updated_by,
x_last_update_login => p_last_update_login,
x_program_update_date => NULL,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL,
x_calling_sequence => l_curr_calling_sequence,
x_accounting_event_id => l_accounting_event_id
);
SELECT DISTINCT AIP.invoice_id
BULK COLLECT INTO l_dbi_key_value_list3
FROM ap_invoice_payments AIP
WHERE AIP.check_id = P_check_id
AND nvl(AIP.reversal_flag, 'N') <> 'Y'
AND NOT EXISTS
(SELECT 'Invoice already has this hold'
FROM ap_holds AH
WHERE AH.invoice_id = AIP.invoice_id
AND AH.hold_lookup_code = P_Hold_Code
AND AH.release_lookup_code IS NULL)
AND NOT EXISTS (SELECT 'Invoice is an Interest Invoice'
FROM ap_invoices AI
WHERE AI.invoice_id = AIP.invoice_id
AND AI.invoice_type_lookup_code = 'INTEREST');
Select AP_HOLDS_S.nextval
INTO l_hold_tab(l_invoice_id_hold).hold_id
From DUAL;
INSERT INTO ap_holds_all
(invoice_id
,hold_lookup_code
,last_update_date
,last_updated_by
,held_by
,hold_date
,hold_reason
,created_by
,creation_date
,org_id /* Bug 3700128. MOAC Project */
,hold_id)
Values
(l_hold_tab(i).invoice_id
,P_Hold_Code
,sysdate
,P_Last_Updated_By
,P_Last_Updated_By
,sysdate
,P_Hold_Reason
,P_Last_Updated_By
,sysdate
,l_hold_tab(i).org_id /* Bug 3700128. MOAC PRoject */
,l_hold_tab(i).hold_id);
SELECT postable_flag,
user_releaseable_flag, /* bug 5143826 */
initiate_workflow_flag
INTO l_postable_flag,
l_user_releaseable_flag,
l_initiate_workflow_flag
FROM AP_HOLD_CODES AHC
WHERE AHC.hold_lookup_code = P_Hold_code;
AP_ACCOUNTING_EVENTS_PKG.UPDATE_PAYMENT_EVENTS_STATUS -- Bug3343314
(
p_check_id => p_check_id,
p_calling_sequence => l_curr_calling_sequence -- Bug3343314
);
P_Last_Updated_By,
P_Last_Update_Login,
-- Base Line ARU
-- l_set_of_books_id,
P_Reversal_Date,
-- Base Line ARU
-- P_Reversal_Period_Name,
P_Num_Cancelled,
P_Num_Not_Cancelled,
l_curr_calling_sequence);
,P_Last_Updated_By
,P_Last_Update_Login
,NULL
,NULL
,rec_new_payments.exchange_rate
,rec_new_payments.exchange_rate_type
,rec_new_payments.exchange_date
,NULL
,NULL
,NULL
,NULL
,'N'
,NULL
,rec_new_payments.accts_pay_code_combination_id
,rec_new_payments.gain_code_combination_id
,rec_new_payments.loss_code_combination_id
,rec_new_payments.future_pay_code_combination_id
,NULL
,'Y'
,'REV'
,P_Replace_Flag
,rec_new_payments.attribute1
,rec_new_payments.attribute2
,rec_new_payments.attribute3
,rec_new_payments.attribute4
,rec_new_payments.attribute5
,rec_new_payments.attribute6
,rec_new_payments.attribute7
,rec_new_payments.attribute8
,rec_new_payments.attribute9
,rec_new_payments.attribute10
,rec_new_payments.attribute11
,rec_new_payments.attribute12
,rec_new_payments.attribute13
,rec_new_payments.attribute14
,rec_new_payments.attribute15
,rec_new_payments.attribute_category
,rec_new_payments.global_attribute1
,rec_new_payments.global_attribute2
,rec_new_payments.global_attribute3
,rec_new_payments.global_attribute4
,rec_new_payments.global_attribute5
,rec_new_payments.global_attribute6
,rec_new_payments.global_attribute7
,rec_new_payments.global_attribute8
,rec_new_payments.global_attribute9
,rec_new_payments.global_attribute10
,rec_new_payments.global_attribute11
,rec_new_payments.global_attribute12
,rec_new_payments.global_attribute13
,rec_new_payments.global_attribute14
,rec_new_payments.global_attribute15
,rec_new_payments.global_attribute16
,rec_new_payments.global_attribute17
,rec_new_payments.global_attribute18
,rec_new_payments.global_attribute19
,rec_new_payments.global_attribute20
,rec_new_payments.global_attribute_category
,l_curr_calling_sequence
,l_accounting_event_id -- Events Project - 6
,rec_new_payments.org_id /* Bug 4759178, passed org_id */
);
UPDATE ap_invoice_payments
SET assets_addition_flag = 'U'
WHERE invoice_payment_id =
rec_new_payments.new_invoice_payment_id;
,P_Last_Updated_By
,P_Last_Update_Login
,NULL
,NULL
,NULL
,l_success
);
P_Last_Updated_By,
P_Last_Update_Login,
/* Base Line ARU */
-- l_set_of_books_id,
P_Reversal_Date,
/* Base Line ARU */
-- P_Reversal_Period_Name,
P_Num_Cancelled,
P_Num_Not_Cancelled,
l_curr_calling_sequence);
AP_ACCOUNTING_EVENTS_PKG.UPDATE_AWT_INT_DISTS
(
p_event_type => 'PAYMENT CANCELLED',
p_check_id => p_check_id,
p_event_id => l_accounting_event_id,
p_calling_sequence => l_curr_calling_sequence
);
l_debug_info := 'Delete from ap_selected_invoices';
DELETE FROM ap_selected_invoices
WHERE checkrun_name = P_Checkrun_Name;
UPDATE ap_checks_all
SET status_lookup_code = 'VOIDED',
void_date = P_reversal_date
WHERE check_id = p_check_id;
l_debug_info := 'Selecting the IBY payment id from ap_checks_all';
SELECT payment_id
INTO l_payment_id
FROM AP_CHECKS_ALL
WHERE check_id = p_check_id;
p_voided_by => p_last_updated_by,
p_void_date => p_reversal_date,
p_void_reason => 'Oracle Payables',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
||', LAST_UPDATED_BY = ' || TO_CHAR(P_Last_Updated_By)
||', LAST_UPDATED_LOGIN = '|| TO_CHAR(P_Last_Update_Login));
SELECT check_id,
checkrun_id,
org_id
INTO l_check_id,
l_checkrun_name,
l_org_id
FROM AP_CHECKS_ALL
WHERE payment_id = p_payment_id;
P_Last_Updated_By => l_user_id,
P_Last_Update_Login => l_login_id,
P_Num_Cancelled => l_num_cancelled,
P_Num_Not_Cancelled => l_num_not_cancelled,
P_Calling_Module => 'IBY',
P_Calling_Sequence => 'AP_VOID_PKG.Iby_Void_Check',
x_return_status => x_return_status,
X_msg_count => X_msg_count,
X_msg_data => X_msg_data);
UPDATE AP_CHECKS_ALL
SET status_lookup_code = 'VOIDED'
,void_date = l_reversal_date
WHERE check_id = l_check_id;