[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_event IN varchar2 default 'UPDATE',
X_update_base IN varchar2 default 'N',
X_vendor_changed_flag IN varchar2 default 'N',
X_calling_sequence IN varchar2)
IS
current_calling_sequence VARCHAR2(2000);
select AI.invoice_amount,
AI.base_amount,
AI.invoice_currency_code,
VS.invoice_amount_limit,
SP.base_currency_code,
nvl(VS.hold_future_payments_flag,'N')
from ap_invoices_all AI,
ap_batches_all AB,
ap_system_parameters_all SP,
po_vendor_sites VS
where AI.invoice_id = X_invoice_id
and AI.batch_id = AB.batch_id (+)
and AI.vendor_site_id = VS.vendor_site_id
and sp.org_id = ai.org_id
and sp.set_of_books_id = ai.set_of_books_id;
if (X_event = 'INSERT' or
(X_update_base = 'Y' or
X_vendor_changed_flag = 'Y')) then
ap_holds_pkg.insert_single_hold(
X_invoice_id,
'AMOUNT',
'INVOICE HOLD REASON',
'',
5,
current_calling_sequence);
if (X_event = 'INSERT' or
X_vendor_changed_flag = 'Y') then
ap_holds_pkg.insert_single_hold(
X_invoice_id,
'VENDOR',
'INVOICE HOLD REASON',
'',
5,
current_calling_sequence);
'VENDOR UPDATED',
5,
current_calling_sequence);
||'X_update_base = '||X_update_base
||'X_vendor_changed_flag = '||X_vendor_changed_flag
);
procedure insert_children (
X_invoice_id IN NUMBER,
X_Payment_Priority IN NUMBER,
X_Hold_count IN OUT NOCOPY NUMBER,
X_Line_count IN OUT NOCOPY NUMBER,
X_Line_Total IN OUT NOCOPY NUMBER,
X_calling_sequence IN VARCHAR2,
X_Sched_Hold_count IN OUT NOCOPY NUMBER) -- bug 5334577
IS
current_calling_sequence VARCHAR2(2000);
l_Last_Updated_By AP_INVOICES.Last_Updated_By%TYPE;
select AI.terms_id,
AI.last_updated_by,
AI.created_by,
AI.batch_id,
AI.terms_date,
AI.invoice_amount,
nvl(AI.pay_curr_invoice_amount, invoice_amount),
AI.payment_cross_rate,
AI.amount_applicable_to_discount,
AI.payment_method_code,
AI.invoice_currency_code,
AI.payment_currency_code,
AI.invoice_type_lookup_code,
AI.vendor_id,
AI.vendor_site_id,
AB.hold_lookup_code,
AB.hold_reason,
AI.invoice_date
from ap_invoices AI,
ap_batches_all AB --Bug8409056
where AI.invoice_id = X_invoice_id
and AI.batch_id = AB.batch_id (+);
'AP_INVOICES_POST_PROCESS_PKG.insert_children<-'
||X_calling_sequence;
l_last_updated_by,
l_created_by,
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_amt_applicable_to_discount,
l_payment_method_code,
l_invoice_currency_code,
l_payment_currency_code,
l_invoice_type_lookup_code,
l_vendor_id,
l_vendor_site_id,
l_batch_hold_lookup_code,
l_batch_hold_reason,
l_invoice_date;
l_last_updated_by,
l_created_by,
X_payment_priority,
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_amt_applicable_to_discount,
l_payment_method_code,
l_invoice_currency_code,
l_payment_currency_code,
current_calling_sequence);
ap_holds_pkg.insert_single_hold(
X_invoice_id,
l_batch_hold_lookup_code,
'',
l_batch_hold_reason,
'',
current_calling_sequence);
debug_info := 'Select counts and sum of amounts from lines and holds';
select count(*)
into X_Hold_count
from ap_holds
where invoice_id = X_invoice_id
and release_lookup_code is null;
Select count(*)
into X_Sched_Hold_count
from ap_payment_schedules_all
where invoice_id = X_invoice_id
and hold_flag = 'Y';
select count(*)
into X_Line_count
from ap_invoice_lines
where invoice_id = X_invoice_id;
select sum(amount)
into X_Line_total
from ap_invoice_lines
where invoice_id = X_invoice_id;
END insert_children;
procedure invoice_pre_update (
X_invoice_id IN number,
X_invoice_amount IN number,
X_payment_status_flag IN OUT NOCOPY varchar2,
X_invoice_type_lookup_code IN varchar2,
X_last_updated_by IN number,
X_accts_pay_ccid IN number,
X_terms_id IN number,
X_terms_date IN date,
X_discount_amount IN number,
X_exchange_rate_type IN varchar2,
X_exchange_date IN date,
X_exchange_rate IN number,
X_vendor_id IN number,
X_payment_method_code IN varchar2,
X_message1 IN OUT NOCOPY varchar2,
X_message2 IN OUT NOCOPY varchar2,
X_reset_match_status IN OUT NOCOPY varchar2,
X_vendor_changed_flag IN OUT NOCOPY varchar2,
X_recalc_pay_sched IN OUT NOCOPY varchar2,
X_liability_adjusted_flag IN OUT NOCOPY varchar2,
X_external_bank_account_id IN NUMBER, --bug 7714053
X_payment_currency_code IN VARCHAR2, --Bug9294551
X_calling_sequence IN varchar2,
X_revalidate_ps IN OUT NOCOPY varchar2)
IS
current_calling_sequence VARCHAR2(2000);
SELECT 'Y'
FROM ap_invoices AI,
financials_system_parameters FSP
WHERE invoice_id = X_invoice_id
AND (AI.accts_pay_code_combination_id <> X_accts_pay_ccid OR
--
-- The following have been added in order to
-- completely externalize the tests for match status
-- reset on the server. We want to reset the match
-- status flag if
-- Encumbrance is not on *AND*
-- One of the following columns' values has changed
--
-- (1) invoice_amount
-- (2) exchange_rate_type
-- (3) exchange_date
-- (4) exchange_rate
--
invoice_amount <> X_invoice_amount OR
nvl(AI.exchange_rate_type,'dummy') <>
nvl(X_exchange_rate_type,'dummy') OR
nvl(AI.exchange_date,sysdate-9000) <>
nvl(X_exchange_date,sysdate-9000) OR
nvl(AI.exchange_rate,-1) <> nvl(X_exchange_rate,-1))
AND FSP.purch_encumbrance_flag <> 'Y';
SELECT 'Y'
FROM ap_invoices
WHERE vendor_id <> X_vendor_id
AND invoice_id = X_invoice_id;
SELECT 'Y'
FROM ap_invoices AI
WHERE invoice_id = X_invoice_id
AND (AI.invoice_amount <> X_invoice_amount OR
AI.terms_id <> X_terms_id OR
AI.terms_date <> X_terms_date OR
AI.payment_method_code <> X_payment_method_code OR
AI.payment_currency_code <> X_payment_currency_code OR -- Bug9294551
AI.amount_applicable_to_discount <> X_discount_amount /*OR --bug 7714053
AI.external_bank_account_id <> X_external_bank_account_id*/) --bug 7714053
-- commented above code as part of bug 8208495
AND (( X_payment_status_flag = 'N') OR
(X_payment_status_flag <> 'N' AND (-1*l_recoup_amt) = ai.amount_paid ));
'AP_INVOICES_POST_PROCESS_PKG.invoice_pre_update<-'||X_calling_sequence;
X_last_updated_by,
X_message1,
X_message2,
X_reset_match_status,
X_liability_adjusted_flag,
current_calling_sequence,
'APXINWKB',
X_revalidate_ps);
||', X_last_updated_by = ' ||X_last_updated_by
||', X_accts_pay_ccid = ' ||X_accts_pay_ccid
||', X_terms_id = ' ||X_terms_id
||', X_terms_date = ' ||X_terms_date
||', X_discount_amount = ' ||X_discount_amount
||', X_message1 = ' ||X_message1
||', X_message2 = ' ||X_message2
||', X_reset_match_status = '||X_reset_match_status
||', X_recalc_pay_sched = ' ||X_recalc_pay_sched
||', X_liability_adjusted_flag = ' ||X_liability_adjusted_flag
);
END invoice_pre_update;
procedure invoice_post_update (
X_invoice_id IN number,
X_payment_priority IN number,
X_recalc_pay_sched IN OUT NOCOPY varchar2,
X_Hold_count IN OUT NOCOPY number,
X_update_base IN varchar2,
X_vendor_changed_flag IN varchar2,
X_calling_sequence IN varchar2,
X_Sched_Hold_count IN OUT NOCOPY number) -- bug 5334577
IS
current_calling_sequence VARCHAR2(2000);
l_Last_Updated_By AP_INVOICES.Last_Updated_By%TYPE;
select AI.terms_id,
AI.last_updated_by,
AI.created_by,
AI.batch_id,
AI.terms_date,
AI.invoice_amount,
nvl(AI.pay_curr_invoice_amount, AI.invoice_amount),
AI.payment_cross_rate,
AI.amount_applicable_to_discount,
AI.payment_method_code,
AI.invoice_currency_code,
AI.payment_currency_code
from ap_invoices AI
where AI.invoice_id = X_invoice_id;
'AP_INVOICES_POST_PROCESS_PKG.invoice_post_update<-'
||X_calling_sequence;
l_last_updated_by,
l_created_by,
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_amt_applicable_to_discount,
l_payment_method_code,
l_invoice_currency_code,
l_payment_currency_code;
debug_info := 'Select count from AP_HOLDS';
select count(*)
into X_Hold_count
from ap_holds
where invoice_id = X_invoice_id
and release_lookup_code is null;
l_last_updated_by,
l_created_by,
X_payment_priority,
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_amt_applicable_to_discount,
l_payment_method_code,
l_invoice_currency_code,
l_payment_currency_code,
current_calling_sequence);
SELECT sum( nvl(amount, 0) )
INTO l_awt_amount
FROM ap_invoice_lines -- bug 9255550
WHERE invoice_id = X_invoice_id
AND line_type_lookup_code = 'AWT';
SELECT sum(nvl(amount_remaining,0)), sum(nvl(gross_amount,0))
INTO l_inv_amt_remaining, l_gross_amount
FROM ap_payment_schedules
WHERE invoice_id = X_invoice_id;
Select count(*)
into X_Sched_Hold_count
from ap_payment_schedules_all
where invoice_id = X_invoice_id
and hold_flag = 'Y';
UPDATE ap_payment_schedules
SET amount_remaining = (amount_remaining +
ap_utilities_pkg.ap_round_currency(
(amount_remaining * (l_awt_amount/l_inv_amt_remaining)
* l_payment_cross_rate), l_payment_currency_code ) )
WHERE invoice_id = X_invoice_id;
UPDATE ap_payment_schedules
SET amount_remaining = (amount_remaining +
ap_utilities_pkg.ap_round_currency(
(gross_amount * (l_awt_amount/l_gross_amount)
* l_payment_cross_rate), l_payment_currency_code) ),
payment_status_flag = DECODE(payment_status_flag,
'Y','P',payment_status_flag)
WHERE invoice_id = X_invoice_id;
UPDATE ap_invoices
SET payment_status_flag = DECODE(payment_status_flag,
'Y','P',payment_status_flag)
WHERE invoice_id = X_invoice_id;
UPDATE ap_payment_schedules
SET amount_remaining = (amount_remaining +
ap_utilities_pkg.ap_round_currency(l_recouped_amount,
l_payment_currency_code) ),
payment_status_flag = DECODE(amount_remaining +
ap_utilities_pkg.ap_round_currency( l_recouped_amount,
l_payment_currency_code),
0,'Y',
gross_amount, 'N',
'P')
WHERE invoice_id = X_invoice_id;
END invoice_post_update;
X_update_base IN OUT NOCOPY varchar2,
X_reset_match_status IN OUT NOCOPY varchar2,
X_update_occurred IN OUT NOCOPY varchar2,
X_approval_status_lookup_code IN OUT NOCOPY varchar2,
X_holds_count IN OUT NOCOPY number,
X_posting_flag IN OUT NOCOPY varchar2,
X_amount_paid IN OUT NOCOPY number,
X_highest_line_num IN OUT NOCOPY number,
X_line_total IN OUT NOCOPY number,
X_actual_invoice_count IN OUT NOCOPY number,
X_actual_invoice_total IN OUT NOCOPY number,
X_calling_sequence IN varchar2,
X_sched_holds_count IN OUT NOCOPY number) -- bug 5334577
IS
current_calling_sequence VARCHAR2(2000);
select
AP_INVOICES_PKG.GET_APPROVAL_STATUS(
AI.INVOICE_ID,
AI.INVOICE_AMOUNT,
AI.PAYMENT_STATUS_FLAG,
AI.INVOICE_TYPE_LOOKUP_CODE),
AP_INVOICES_PKG.GET_HOLDS_COUNT(
AI.INVOICE_ID),
AP_INVOICES_PKG.GET_SCHED_HOLDS_COUNT( --bug 5334577
AI.INVOICE_ID),
AP_INVOICES_PKG.GET_POSTING_STATUS(
AI.INVOICE_ID),
AI.AMOUNT_PAID,
AP_INVOICES_PKG.GET_MAX_LINE_NUMBER(
AI.INVOICE_ID) + 1,
AP_INVOICES_UTILITY_PKG.GET_LINE_TOTAL(
AI.INVOICE_ID),
decode(AB.BATCH_ID,
'',null,
AP_BATCHES_PKG.GET_ACTUAL_INV_COUNT(
AB.BATCH_ID)),
decode(AB.BATCH_ID,
'',null,
AP_BATCHES_PKG.GET_ACTUAL_INV_AMOUNT(
AB.BATCH_ID))
from ap_invoices AI,
ap_batches_all AB --Bug: 6668692 : Added _all to table name
where AI.invoice_id = X_invoice_id
and AI.batch_id = AB.batch_id (+);
if (nvl(X_update_base,'N') = 'Y' or
nvl(X_reset_match_status,'N') = 'Y') then
ap_invoice_distributions_pkg.update_distributions
(X_invoice_id,
X_line_number,
X_type_1099,
X_income_tax_region,
X_vendor_changed_flag,
X_update_base,
X_reset_match_status,
X_update_occurred,
current_calling_sequence);
debug_info := 'Select invoice statuses from AP_INVOICES';
||', X_update_base = ' ||X_update_base
||', X_reset_match_status = ' ||X_reset_match_status
||', X_update_occurred = ' ||X_update_occurred
||', X_approval_status_lookup_code = '||
X_approval_status_lookup_code
||', X_holds_count = ' ||X_holds_count
||', X_posting_flag = ' ||X_posting_flag
||', X_amount_paid = ' ||X_amount_paid
||', X_highest_line_num = ' ||X_highest_line_num
||', X_actual_invoice_count = ' ||X_actual_invoice_count
||', X_actual_invoice_total = ' ||X_actual_invoice_total
||', X_line_total = ' ||X_Line_total );
PROCEDURE Select_Summary(X_Batch_ID IN NUMBER,
X_Total IN OUT NOCOPY NUMBER,
X_Total_Rtot_DB IN OUT NOCOPY NUMBER,
X_Calling_Sequence IN VARCHAR2)
IS
current_calling_sequence VARCHAR2(2000);
'AP_INVOICES_POST_PROCESS_PKG.Select_Summary<-'||X_Calling_Sequence;
debug_info := 'Select from AP_INVOICES';
select sum(nvl(invoice_amount,0))
into X_Total
from ap_invoices
where Batch_ID = X_Batch_ID;
END Select_Summary;