[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*) + l_num_payments
INTO l_num_payments
FROM ap_invoices_ready_to_pay_v
WHERE invoice_id = l_invoice_id;
SELECT a.payment_currency_code,
b.payment_method_code, --4552701
a.vendor_id,
a.vendor_site_id,
a.party_id,
a.party_site_id,
a.org_id,
l_num_payments,
a.payment_function,
a.pay_proc_trxn_type_code,
a.legal_entity_id,
/* commented as part of bug 7688200
, -- Bug 5617689 */--Bug 7860631 Uncommented the commeted code.
b.remit_to_supplier_id, --Bug 7662240
b.remit_to_supplier_site_id,
b.remit_to_supplier_name,
b.remit_to_supplier_site,
b.relationship_id
INTO P_currency_code,
P_payment_method,
P_vendor_id,
P_vendor_site_id,
P_party_id,
P_party_site_id,
P_org_id,
P_num_payments,
P_payment_function,
P_proc_trxn_type,
p_le_id,
/* commented as part of bug 7688200
, -- Bug 5617689 */--Bug 7860631 Uncommented the commented code.
P_remit_vendor_id, -- Bug 7662240
P_remit_vendor_site_id,
p_remit_vendor_name,
p_remit_vendor_site_name,
p_relationship_id
FROM ap_invoices_all a, ap_payment_schedules_all b --Bug 7662240
WHERE a.invoice_id = l_invoice_id
and a.invoice_id = b.invoice_id
and rownum<2;
SELECT payment_method_code,
/* commented as part of bug 7688200
, --4552701 */--Bug 7860631 Uncommented the commeted code.
remit_to_supplier_id, --7662240
remit_to_supplier_site_id,
remit_to_supplier_name,
remit_to_supplier_site,
relationship_id
INTO p_payment_method,
/* commented as part of bug 7688200
,*/--Bug 7860631 Uncommented the commented code.
P_remit_vendor_id, -- Bug 7662240
P_remit_vendor_site_id,
p_remit_vendor_name,
p_remit_vendor_site_name,
p_relationship_id
FROM ap_payment_schedules
WHERE invoice_id = l_invoice_id
and payment_num = l_payment_num;
SELECT APS.party_id
INTO p_remit_party_id
FROM AP_SUPPLIERS APS
WHERE APS.vendor_id = p_remit_vendor_id;
SELECT APS.vendor_id,
APS.vendor_name,
APSS.party_site_id,
APSS.vendor_site_code
INTO p_remit_vendor_id,
p_remit_vendor_name,
p_remit_party_site_id,
p_remit_vendor_site_name
FROM AP_SUPPLIERS APS, AP_SUPPLIER_SITES APSS
WHERE APS.party_id = p_remit_party_id
AND APS.vendor_id =APSS.vendor_id
AND APSS.vendor_site_id = p_remit_vendor_site_id;
SELECT party_id, party_site_id
INTO p_remit_party_id, p_remit_party_site_id
FROM ap_invoices_all
WHERE invoice_id = l_invoice_id;
SELECT ap_payment_schedules_pkg.get_discount_available(
invoice_id,
payment_num,
P_check_date,
P_currency_code)
INTO l_discount_available
FROM ap_invoices_ready_to_pay_v
WHERE invoice_id = l_invoice_id
AND payment_num = l_payment_num;
SELECT SUM(ap_payment_schedules_pkg.get_discount_available(
invoice_id,
payment_num,
P_check_date,
P_currency_code))
INTO l_discount_available
FROM ap_invoices_ready_to_pay_v
WHERE invoice_id = l_invoice_id;
SELECT payment_num,
amount_remaining,
ap_payment_schedules_pkg.get_discount_available(
invoice_id,
payment_num,
P_check_date,
P_currency_code)
FROM ap_invoices_ready_to_pay_v
WHERE invoice_id = P_invoice_id
AND payment_num = nvl(P_payment_num, payment_num);
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER,
P_calling_sequence IN VARCHAR2,
P_sequential_numbering IN VARCHAR2,
P_accounting_event_id IN NUMBER, --Events
P_org_id IN NUMBER)
IS
l_invoice_payment_id NUMBER;
SELECT AIRP.payment_num,
AIRP.invoice_type,
AIRP.invoice_num,
AIRP.vendor_id,
AIRP.vendor_site_id,
AIRP.exclusive_payment_flag,
AIRP.accts_pay_code_combi_id,
AIRP.amount_remaining,
ap_payment_schedules_pkg.get_discount_available(
AIRP.invoice_id,
AIRP.payment_num,
P_check_date,
P_currency_code),
APS.attribute1,
APS.attribute2,
APS.attribute3,
APS.attribute4,
APS.attribute5,
APS.attribute6,
APS.attribute7,
APS.attribute8,
APS.attribute9,
APS.attribute10,
APS.attribute11,
APS.attribute12,
APS.attribute13,
APS.attribute14,
APS.attribute15,
APS.attribute_category
FROM ap_invoices_ready_to_pay_v AIRP,
ap_payment_schedules APS
WHERE AIRP.invoice_id = P_invoice_id
AND AIRP.payment_num = nvl(P_payment_num, AIRP.payment_num)
AND APS.invoice_id = AIRP.invoice_id
AND APS.payment_num = AIRP.payment_num;
SELECT ap_invoice_payments_s.nextval
INTO l_invoice_payment_id
FROM sys.dual;
SELECT nvl(awt_include_discount_amt, 'N'),
nvl(allow_awt_flag, 'N'),
create_awt_dists_type,
create_awt_invoices_type
INTO l_include_discount,
l_awt_flag,
l_create_awt_dists_type, --5745239
l_create_awt_invoices_type
FROM ap_system_parameters;
SELECT 'Y',awt_flag
INTO l_awt_invoices_exists,l_awt_applied --5745239
FROM ap_invoices AI
WHERE AI.invoice_id = p_invoice_id
AND EXISTS (SELECT 'At least 1 dist has an AWT Group'
FROM ap_invoice_distributions AID1
WHERE AID1.invoice_id = AI.invoice_id
AND ( AID1.pay_awt_group_id is not null --Bug6660355
OR AID1.awt_group_id is not null)) --Bug7685907
AND NOT EXISTS (SELECT 'Manual AWT lines exist'
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = AI.invoice_id
AND AID.line_type_lookup_code = 'AWT'
AND AID.awt_flag in ('M', 'O'));
SELECT sum(nvl(base_amount,amount))
INTO l_total_inv_amount
FROM ap_invoice_distributions
WHERE invoice_id =p_invoice_id
AND line_type_lookup_code <> 'AWT'; /*Bug 14530960*/
/* SELECT sum(nvl(aid.base_amount,aid.amount)) --query is commented for Bug:14696775
INTO l_total_awt_amount
FROM ap_invoice_distributions aid,ap_invoices ai
WHERE aid.invoice_id =p_invoice_id
AND aid.invoice_id = ai.invoice_id
AND aid.line_type_lookup_code in ('AWT')
AND aid.awt_origin_group_id = NVL(ai.awt_group_id, aid.awt_origin_group_id) --Bug7707630
AND aid.awt_invoice_payment_id IS NULL; */
SELECT sum(nvl(aid.base_amount,aid.amount))
INTO l_total_awt_amount
FROM ap_invoice_distributions aid
WHERE aid.invoice_id =p_invoice_id
AND aid.line_type_lookup_code in ('AWT')
AND aid.awt_invoice_payment_id is null;
SELECT invoice_amount,exchange_rate,nvl(payment_cross_rate,1)
INTO l_before_invoice_amount,l_inv_exchange_rate,l_pay_cross_rate
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
,P_Last_Updated_By => p_last_updated_by
,P_Last_Update_Login => p_last_update_login
,P_Program_Application_Id => null
,P_Program_Id => null
,P_Request_Id => null
,P_Awt_Success => undo_output
,P_checkrun_id => null);
,P_Last_Updated_By => p_last_updated_by
,P_Last_Update_Login => p_last_update_login
,P_Program_Application_Id => null
,P_Program_Id => null
,P_Request_Id => null
,P_Awt_Success => l_awt_success
,P_Invoice_Payment_ID => l_invoice_payment_id
,P_Check_Id => P_check_id --bug 8590059
);
SELECT nvl(sum(ap_utilities_pkg.ap_round_currency(
AID.amount * AI.payment_cross_rate,
AI.payment_currency_code)),0)
INTO l_withholding_amount
FROM ap_invoice_distributions AID,
ap_invoices AI
WHERE AID.awt_invoice_payment_id = l_invoice_payment_id
AND AID.invoice_id = AI.invoice_id;
SELECT nvl(SUM(aip.amount),0)
INTO l_pay_amt
FROM ap_invoice_payments_all aip
WHERE aip.invoice_id = p_invoice_id;
SELECT nvl(SUM(aid.amount),0)
INTO l_prepay_amt
FROM ap_invoice_distributions_all aid,ap_invoice_lines_all ail,ap_invoices_all ai
WHERE ail.invoice_id=aid.invoice_id
AND ail.invoice_id=ai.invoice_id
AND ail.line_number=aid.invoice_line_number
AND ai.invoice_id=p_invoice_id
AND aid.prepay_distribution_id is not null
AND nvl(ail.invoice_includes_prepay_flag,'N')<>'Y';
SELECT count(ai.invoice_id)
INTO l_count
FROM ap_invoices_all ai
WHERE ai.invoice_id=p_invoice_id
GROUP BY ai.invoice_id,ai.invoice_amount
,ai.discount_amount_taken, decode(ai.net_of_retainage_flag,'Y',0,
nvl(AP_INVOICES_UTILITY_PKG.GET_RETAINED_TOTAL(ai.invoice_id,ai.org_id),0))
HAVING (abs(nvl(ai.invoice_amount,0) -nvl(ai.discount_amount_taken,0)
- nvl(AP_INVOICES_UTILITY_PKG.get_amount_withheld(ai.invoice_id),0)
+ decode(ai.net_of_retainage_flag, 'Y', 0,
nvl(AP_INVOICES_UTILITY_PKG.GET_RETAINED_TOTAL(ai.invoice_id, ai.org_id),0))
)
< abs((nvl(l_pay_amt,0)-nvl(l_prepay_amt,0))
+((l_amount) + (l_discount_taken))));
,P_Last_Updated_By => p_last_updated_by
,P_Last_Update_Login => p_last_update_login
,P_Program_Application_Id => null
,P_Program_Id => null
,P_Request_Id => null
,P_Awt_Success => undo_output
,P_checkrun_id => null);
P_last_updated_by => P_last_updated_by,
P_last_update_login => P_last_update_login,
P_currency_code => P_currency_code,
P_base_currency_code => P_base_currency_code,
P_exchange_rate => P_exchange_rate,
P_exchange_rate_type => P_exchange_rate_type,
P_exchange_date => P_exchange_date,
P_ce_bank_acct_use_id => P_ce_bank_acct_use_id,
P_bank_account_num => P_bank_account_num,
P_bank_account_type => P_bank_account_type,
P_bank_num => P_bank_num,
P_future_pay_posted_flag => l_future_pay_posted_flag,
P_exclusive_payment_flag => l_exclusive_payment_flag,
P_accts_pay_ccid => l_accts_pay_ccid,
P_gain_ccid => '',
P_loss_ccid => '',
P_future_pay_ccid => P_future_pay_ccid,
P_asset_ccid => NULL,
P_payment_dists_flag => 'N',
P_payment_mode => 'PAY',
P_replace_flag => 'N',
P_attribute1 => l_attribute1,
P_attribute2 => l_attribute2,
P_attribute3 => l_attribute3,
P_attribute4 => l_attribute4,
P_attribute5 => l_attribute5,
P_attribute6 => l_attribute6,
P_attribute7 => l_attribute7,
P_attribute8 => l_attribute8,
P_attribute9 => l_attribute9,
P_attribute10 => l_attribute10,
P_attribute11 => l_attribute11,
P_attribute12 => l_attribute12,
P_attribute13 => l_attribute13,
P_attribute14 => l_attribute14,
P_attribute15 => l_attribute15,
P_attribute_category => l_attribute_category,
P_calling_sequence => l_curr_calling_sequence,
-- Events Project - 4 - Added following parameter
P_accounting_event_id => P_accounting_event_id,
P_org_id => P_org_id);
AP_ACCOUNTING_EVENTS_PKG.UPDATE_AWT_INT_DISTS
(
p_event_type => 'PAYMENT CREATED',
p_check_id => p_check_id,
p_event_id => p_accounting_event_id,
p_calling_sequence => l_curr_calling_sequence
);
SELECT ap_invoices_s.nextval
INTO l_interest_invoice_id
FROM sys.dual;
SELECT ap_invoice_payments_s.nextval
INTO l_interest_invoice_pay_id
FROM sys.dual;
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field
INTO l_nls_interest,
l_nls_days,
l_nls_percent
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3
WHERE l1.lookup_type = 'NLS TRANSLATION'
AND l1.lookup_code = 'INTEREST'
AND l2.lookup_type = 'NLS TRANSLATION'
AND l2.lookup_code = 'DAYS'
AND l3.lookup_type = 'NLS TRANSLATION'
AND l3.lookup_code = 'PERCENT';
SELECT annual_interest_rate , due_date
INTO l_rate, l_due_date
FROM ap_payment_schedules, ap_interest_periods
WHERE payment_num = l_payment_num
AND invoice_id = P_invoice_id
AND trunc(due_date+1) BETWEEN trunc(start_date) AND trunc(end_date);
P_last_updated_by => P_last_updated_by,
P_last_update_login => P_last_update_login,
P_currency_code => P_currency_code,
P_base_currency_code => P_base_currency_code,
P_exchange_rate => P_exchange_rate,
P_exchange_rate_type => P_exchange_rate_type,
P_exchange_date => P_exchange_date,
P_bank_account_id => P_ce_bank_acct_use_id,
P_bank_account_num => P_bank_account_num,
P_bank_account_type => P_bank_account_type,
P_bank_num => P_bank_num,
P_exclusive_payment_flag => l_exclusive_payment_flag,
P_accts_pay_ccid => l_accts_pay_ccid,
P_gain_ccid => '',
P_loss_ccid => '',
P_future_pay_ccid => P_future_pay_ccid,
P_asset_ccid => NULL,
P_payment_dists_flag => 'N',
P_payment_mode => 'PAY',
P_replace_flag => 'N',
P_invoice_description => l_invoice_description,
P_attribute1 => l_attribute1,
P_attribute2 => l_attribute2,
P_attribute3 => l_attribute3,
P_attribute4 => l_attribute4,
P_attribute5 => l_attribute5,
P_attribute6 => l_attribute6,
P_attribute7 => l_attribute7,
P_attribute8 => l_attribute8,
P_attribute9 => l_attribute9,
P_attribute10 => l_attribute10,
P_attribute11 => l_attribute11,
P_attribute12 => l_attribute12,
P_attribute13 => l_attribute13,
P_attribute14 => l_attribute14,
P_attribute15 => l_attribute15,
P_attribute_category => l_attribute_category,
P_calling_sequence => l_curr_calling_sequence,
P_org_id => P_org_id, /* Bug 4742671 */
P_accounting_event_id => P_accounting_event_id); --Events
' P_last_updated_by = ' || P_last_updated_by ||
' P_last_update_login = ' || P_last_update_login
);
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER,
P_calling_sequence IN VARCHAR2,
P_sequential_numbering IN VARCHAR2 DEFAULT 'N', -- 1724353
P_accounting_event_id IN NUMBER, -- Events
P_org_id IN NUMBER)
IS
l_invoice_id NUMBER;
P_last_updated_by,
P_last_update_login,
l_curr_calling_sequence,
P_sequential_numbering, -- 1724353
P_accounting_event_id, -- Events Project
P_org_id);
P_last_updated_by,
P_last_update_login,
l_curr_calling_sequence,
P_sequential_numbering,
P_accounting_event_id, -- Events Project
P_org_id);
' P_last_updated_by = ' || P_last_updated_by ||
' P_last_update_login = ' || P_last_update_login
);