[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_last_updated_by IN number,
X_message1 IN OUT NOCOPY varchar2,
X_message2 IN OUT NOCOPY varchar2,
X_reset_match_status IN OUT NOCOPY varchar2,
X_liability_adjusted_flag IN OUT NOCOPY varchar2,
X_calling_sequence IN varchar2,
X_calling_mode IN varchar2,
X_revalidate_ps IN OUT NOCOPY varchar2)
IS
current_calling_sequence VARCHAR2(2000);
SELECT 'AP_PAY_WARN_DISC_UPDATE'
FROM ap_payment_schedules
WHERE invoice_id = X_invoice_id
AND payment_num = l_current_payment_num
AND (NVL(discount_amount_available, 0) <> 0
OR NVL(second_disc_amt_available, 0) <> 0
OR NVL(third_disc_amt_available, 0) <> 0);
select AI.invoice_amount,
SP.allow_paid_invoice_adjust,
AI.invoice_currency_code,
AI.payment_currency_code,
AI.payment_cross_rate,
nvl(AI.pay_curr_invoice_amount, AI.invoice_amount)
from ap_invoices AI,
ap_system_parameters SP
where invoice_id = X_invoice_id;
SELECT payment_num,
amount_remaining,
payment_status_flag
FROM ap_payment_schedules
WHERE invoice_id = X_invoice_id
AND (l_add_new_payment_schedule='Y' OR
payment_status_flag <> 'Y')
ORDER BY due_date desc, payment_num desc;
SELECT nvl((MAX(payment_num)+1),1)
FROM ap_payment_schedules
WHERE invoice_id = X_invoice_id;
SELECT SUM(nvl(inv_curr_gross_amount, gross_amount))
FROM ap_payment_schedules
WHERE invoice_id = X_Invoice_Id;
elsif (ap_invoices_pkg.selected_for_payment_flag(
X_invoice_id) = 'Y') then
-- Cannot change the amount as it is selected for payment
fnd_message.set_name('SQLAP','AP_INV_SELECTED_INVOICE');
fnd_message.set_name('SQLAP','AP_DIST_NO_UPDATE_PAID');
debug_info := 'Delete AP_PAYMENT_SCHEDULES payment_num '||
l_current_payment_num;
delete from ap_payment_schedules
where invoice_id = X_invoice_id
and payment_num = l_current_payment_num;
debug_info := 'Update AP_PAYMENT_SCHEDULES payment_num '||
l_current_payment_num;
UPDATE ap_payment_schedules
SET gross_amount = NVL(gross_amount, 0)+l_current_amount_to_adjust,
inv_curr_gross_amount = (
SELECT DECODE(F.minimum_accountable_unit,NULL,
ROUND( ((NVL(gross_amount, 0)+
l_current_amount_to_adjust)/
l_payment_cross_rate)
, F.precision),
ROUND( ((NVL(gross_amount, 0)+
l_current_amount_to_adjust)/
l_payment_cross_rate)
/ F.minimum_accountable_unit)
* F.minimum_accountable_unit)
FROM fnd_currencies_vl F
WHERE F.currency_code = l_invoice_currency_code),
amount_remaining = NVL(amount_remaining, 0)
+ l_current_amount_to_adjust,
payment_status_flag =
DECODE(NVL(amount_remaining, 0) +
l_current_amount_to_adjust,
NVL(gross_amount, 0) +
l_current_amount_to_adjust, 'N',
0, DECODE(X_invoice_amount,
0,'N',
'Y'),
'P')
WHERE invoice_id = X_invoice_id
AND payment_num = l_current_payment_num;
debug_info := 'Select from AP_PAYMENT_SCHEDULES';
debug_info:= 'Update ap_payment_schedules - set inv_curr_gross_amount';
UPDATE AP_PAYMENT_SCHEDULES
SET inv_curr_gross_amount = inv_curr_gross_amount
+ X_Invoice_Amount
- l_inv_curr_sched_total
WHERE invoice_id = X_Invoice_Id
AND payment_num = (SELECT MAX(payment_num)
FROM ap_payment_schedules
WHERE invoice_id = X_Invoice_Id);
debug_info := 'Insert into AP_PAYMENT_SCHEDULES';
INSERT INTO ap_payment_schedules(
invoice_id, payment_num, due_date,
last_update_date, last_updated_by,
last_update_login, creation_date, created_by,
payment_cross_rate,
gross_amount,inv_curr_gross_amount,amount_remaining,
payment_priority, hold_flag,
payment_status_flag, batch_id, payment_method_code,
external_bank_account_id,
org_id, --MOAC project
remittance_message1,
remittance_message2,
remittance_message3,
--third party payments
remit_to_supplier_name,
remit_to_supplier_id,
remit_to_supplier_site,
remit_to_supplier_site_id,
relationship_id
)
SELECT X_invoice_id, l_payment_num_to_add, P.due_date,
SYSDATE, X_last_updated_by,
null, SYSDATE, X_last_updated_by,
P.payment_cross_rate,
l_pay_curr_net_amt_to_adj,
l_net_amount_to_adjust,
l_pay_curr_net_amt_to_adj,
P.payment_priority, P.hold_flag, 'N', P.batch_id,
P.payment_method_code,
P.external_bank_account_id,
P.org_id, --MOAC project
p.remittance_message1,
p.remittance_message2,
p.remittance_message3,
--third party payments
p.remit_to_supplier_name,
p.remit_to_supplier_id,
p.remit_to_supplier_site,
p.remit_to_supplier_site_id,
p.relationship_id
FROM ap_payment_schedules P
WHERE P.invoice_id = X_invoice_id
AND P.payment_num = l_current_payment_num;
X_Message2 := 'AP_PAY_WARN_SCHED_UPDATE';
SELECT sum(amount_remaining)
INTO l_sum_ps_amount_remaining
FROM ap_payment_schedules
WHERE invoice_id = X_invoice_id;
||', X_last_updated_by = ' ||X_last_updated_by
||', X_message1 = ' ||X_message1
||', X_message2 = ' ||X_message2
||', X_reset_match_status = '||X_reset_match_status
||', X_liability_adjusted_flag = '||X_liability_adjusted_flag
);
select nvl(ap_utilities_pkg.ap_round_currency(
ap_invoices_pkg.get_amount_withheld(ai.invoice_id)*
ai.payment_cross_rate,X_currency_code),0)*
X_gross_amount/decode(ai.pay_curr_invoice_amount, 0, 1,
nvl(ai.pay_curr_invoice_amount, 1))
into l_wt_amt_to_subtract
from ap_invoices ai
where ai.invoice_id=X_invoice_id;
select nvl(ap_utilities_pkg.ap_round_currency(
ap_invoices_pkg.get_amount_withheld(ai.invoice_id)*
ai.payment_cross_rate,X_currency_code),0)*
aps.gross_amount/decode(ai.pay_curr_invoice_amount, 0, 1,
nvl(ai.pay_curr_invoice_amount, 1)),
aps.gross_amount -- BUG 3741934
into l_wt_amt_to_subtract, l_gross_amount
from ap_invoices ai,ap_payment_schedules aps
where ai.invoice_id=aps.invoice_id
and aps.payment_num=X_payment_num
and ai.invoice_id=X_invoice_id;
SELECT NVL(ap_utilities_pkg.ap_round_currency(
DECODE(gross_amount, 0, 0,
DECODE(air.always_take_disc_flag, 'Y', discount_amount_available, --Bug7717053, added the table alias
GREATEST(
DECODE(SIGN(X_check_date -
NVL(discount_date, sysdate-9000)),
1, 0, NVL(ABS(discount_amount_available), 0)),
DECODE(SIGN(X_check_date -
NVL(second_discount_date, sysdate-9000)),
1, 0, NVL(ABS(second_disc_amt_available), 0)),
DECODE(SIGN(X_check_date -
NVL(third_discount_date, sysdate-9000)),
1, 0, NVL(ABS(third_disc_amt_available),0))) * DECODE(SIGN(gross_amount),-1,-1,1) )
* (amount_remaining/DECODE(gross_amount, 0, 1, gross_amount-decode(asp.create_awt_dists_type,
'APPROVAL',
l_wt_amt_to_subtract,
0)))),
X_currency_code),0) --Bug7717053, added the decode
INTO l_discount_available
FROM ap_invoices_ready_to_pay_v air, ap_system_parameters asp
WHERE invoice_id = X_invoice_id
AND payment_num = X_payment_num;
SELECT DECODE(always_take_disc_flag, 'Y', due_date,
DECODE(SIGN(X_check_date - NVL(discount_date,sysdate-9000)-1),
-1, discount_date,
DECODE(SIGN(X_check_date - NVL(second_discount_date,sysdate-9000)-1),
-1, second_discount_date,
DECODE(SIGN(X_check_date - NVL(third_discount_date,sysdate-9000)-1),
-1, third_discount_date, due_date))))
INTO l_discount_date
FROM ap_invoices_ready_to_pay_v
WHERE invoice_id = X_invoice_id
AND payment_num = X_payment_num;
X_Last_Updated_By NUMBER,
X_Last_Update_Date DATE,
X_Payment_Cross_Rate NUMBER,
X_Payment_Num NUMBER,
X_Amount_Remaining NUMBER,
X_Created_By NUMBER,
X_Creation_Date DATE,
X_Discount_Date DATE,
X_Due_Date DATE,
X_Future_Pay_Due_Date DATE,
X_Gross_Amount NUMBER,
X_Hold_Flag VARCHAR2,
X_Last_Update_Login NUMBER,
X_Payment_Method_Lookup_Code VARCHAR2 default null,
X_payment_method_code varchar2,
X_Payment_Priority NUMBER,
X_Payment_Status_Flag VARCHAR2,
X_Second_Discount_Date DATE,
X_Third_Discount_Date DATE,
X_Batch_Id NUMBER,
X_Discount_Amount_Available NUMBER,
X_Second_Disc_Amt_Available NUMBER,
X_Third_Disc_Amt_Available NUMBER,
X_Attribute1 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Discount_Amount_Remaining NUMBER,
X_Global_Attribute_Category VARCHAR2,
X_Global_Attribute1 VARCHAR2,
X_Global_Attribute2 VARCHAR2,
X_Global_Attribute3 VARCHAR2,
X_Global_Attribute4 VARCHAR2,
X_Global_Attribute5 VARCHAR2,
X_Global_Attribute6 VARCHAR2,
X_Global_Attribute7 VARCHAR2,
X_Global_Attribute8 VARCHAR2,
X_Global_Attribute9 VARCHAR2,
X_Global_Attribute10 VARCHAR2,
X_Global_Attribute11 VARCHAR2,
X_Global_Attribute12 VARCHAR2,
X_Global_Attribute13 VARCHAR2,
X_Global_Attribute14 VARCHAR2,
X_Global_Attribute15 VARCHAR2,
X_Global_Attribute16 VARCHAR2,
X_Global_Attribute17 VARCHAR2,
X_Global_Attribute18 VARCHAR2,
X_Global_Attribute19 VARCHAR2,
X_Global_Attribute20 VARCHAR2,
X_External_Bank_Account_Id NUMBER,
X_Inv_Curr_Gross_Amount NUMBER,
X_Org_Id NUMBER,
X_Calling_Sequence IN VARCHAR2,
--Third Party Payments
X_Remit_To_Supplier_Name VARCHAR2,
X_Remit_To_Supplier_Id NUMBER,
X_Remit_To_Supplier_Site VARCHAR2,
X_Remit_To_Supplier_Site_Id NUMBER,
X_Relationship_Id NUMBER
) IS
CURSOR C IS
SELECT *
FROM ap_payment_schedules
WHERE invoice_id = X_Invoice_Id
AND payment_num = X_Payment_Num
FOR UPDATE of invoice_id NOWAIT;
AND ((Recinfo.Last_Updated_By = X_Last_Updated_By) OR
((Recinfo.Last_Updated_By IS NULL)
AND (X_Last_Updated_By IS NULL)))
-- Bug 2909797 AND ((Recinfo.Last_Update_Date = X_Last_Update_Date) OR
-- ((Recinfo.Last_Update_Date IS NULL)
-- AND (X_Last_Update_Date IS NULL)))
AND ((Recinfo.Payment_Cross_Rate = X_Payment_Cross_Rate) OR
((Recinfo.Payment_Cross_Rate IS NULL)
AND (X_Payment_Cross_Rate IS NULL)))
AND ((Recinfo.Payment_Num = X_Payment_Num) OR
((Recinfo.Payment_Num IS NULL)
AND (X_Payment_Num IS NULL)))
AND ((Recinfo.Amount_Remaining = X_Amount_Remaining) OR
((Recinfo.Amount_Remaining IS NULL)
AND (X_Amount_Remaining IS NULL)))
AND ((Recinfo.Created_By = X_Created_By) OR
((Recinfo.Created_By IS NULL)
AND (X_Created_By IS NULL)))
-- Bug 2909797 AND ((Recinfo.Creation_Date = X_Creation_Date) OR
-- ((Recinfo.Creation_Date IS NULL)
-- AND (X_Creation_Date IS NULL)))
AND ((Recinfo.Discount_Date = X_Discount_Date) OR
((Recinfo.Discount_Date IS NULL)
AND (X_Discount_Date IS NULL)))
AND ((Recinfo.Due_Date = X_Due_Date) OR
((Recinfo.Due_Date IS NULL)
AND (X_Due_Date IS NULL)))
AND ((Recinfo.Future_Pay_Due_Date = X_Future_Pay_Due_Date) OR
((Recinfo.Future_Pay_Due_Date IS NULL)
AND (X_Future_Pay_Due_Date IS NULL)))
AND ((Recinfo.Gross_Amount = X_Gross_Amount) OR
((Recinfo.Gross_Amount IS NULL)
AND (X_Gross_Amount IS NULL)))
AND ((Recinfo.Hold_Flag = X_Hold_Flag) OR
((Recinfo.Hold_Flag IS NULL)
AND (X_Hold_Flag IS NULL)))
AND ((Recinfo.Last_Update_Login = X_Last_Update_Login) OR
((Recinfo.Last_Update_Login IS NULL)
AND (X_Last_Update_Login IS NULL)))
AND ((Recinfo.Payment_Method_Code = X_Payment_Method_Code) OR
((Recinfo.Payment_Method_Code IS NULL)
AND (X_Payment_Method_Code IS NULL)))
AND ((Recinfo.Payment_Priority = X_Payment_Priority) OR
((Recinfo.Payment_Priority IS NULL)
AND (X_Payment_Priority IS NULL)))
AND ((Recinfo.Payment_Status_Flag = X_Payment_Status_Flag) OR
((Recinfo.Payment_Status_Flag IS NULL)
AND (X_Payment_Status_Flag IS NULL)))
AND ((Recinfo.Second_Discount_Date = X_Second_Discount_Date) OR
((Recinfo.Second_Discount_Date IS NULL)
AND (X_Second_Discount_Date IS NULL)))
AND ((Recinfo.Third_Discount_Date = X_Third_Discount_Date) OR
((Recinfo.Third_Discount_Date IS NULL)
AND (X_Third_Discount_Date IS NULL)))
AND ((Recinfo.Batch_Id = X_Batch_Id) OR
((Recinfo.Batch_Id IS NULL)
AND (X_Batch_Id IS NULL)))
AND ((Recinfo.Discount_Amount_Available = X_Discount_Amount_Available) OR
((Recinfo.Discount_Amount_Available IS NULL)
AND (X_Discount_Amount_Available IS NULL)))
AND ((Recinfo.Second_Disc_Amt_Available = X_Second_Disc_Amt_Available) OR
((Recinfo.Second_Disc_Amt_Available IS NULL)
AND (X_Second_Disc_Amt_Available IS NULL)))
AND ((Recinfo.Third_Disc_Amt_Available = X_Third_Disc_Amt_Available) OR
((Recinfo.Third_Disc_Amt_Available IS NULL)
AND (X_Third_Disc_Amt_Available IS NULL)))
AND ((Recinfo.Attribute1 = X_Attribute1) OR
((Recinfo.Attribute1 IS NULL)
AND (X_Attribute1 IS NULL)))
AND ((Recinfo.Attribute10 = X_Attribute10) OR
((Recinfo.Attribute10 IS NULL)
AND (X_Attribute10 IS NULL)))
AND ((Recinfo.Attribute11 = X_Attribute11) OR
((Recinfo.Attribute11 IS NULL)
AND (X_Attribute11 IS NULL)))
AND ((Recinfo.Attribute12 = X_Attribute12) OR
((Recinfo.Attribute12 IS NULL)
AND (X_Attribute12 IS NULL)))
AND ((Recinfo.Attribute13 = X_Attribute13) OR
((Recinfo.Attribute13 IS NULL)
AND (X_Attribute13 IS NULL)))
AND ((Recinfo.Attribute14 = X_Attribute14) OR
((Recinfo.Attribute14 IS NULL)
AND (X_Attribute14 IS NULL)))
AND ((Recinfo.Attribute15 = X_Attribute15) OR
((Recinfo.Attribute15 IS NULL)
AND (X_Attribute15 IS NULL)))
AND ((Recinfo.Attribute2 = X_Attribute2) OR
((Recinfo.Attribute2 IS NULL)
AND (X_Attribute2 IS NULL)))
AND ((Recinfo.Attribute3 = X_Attribute3) OR
((Recinfo.Attribute3 IS NULL)
AND (X_Attribute3 IS NULL)))
AND ((Recinfo.Attribute4 = X_Attribute4) OR
((Recinfo.Attribute4 IS NULL)
AND (X_Attribute4 IS NULL)))
AND ((Recinfo.Attribute5 = X_Attribute5) OR
((Recinfo.Attribute5 IS NULL)
AND (X_Attribute5 IS NULL)))
AND ((Recinfo.Attribute6 = X_Attribute6) OR
((Recinfo.Attribute6 IS NULL)
AND (X_Attribute6 IS NULL)))
AND ((Recinfo.Attribute7 = X_Attribute7) OR
((Recinfo.Attribute7 IS NULL)
AND (X_Attribute7 IS NULL)))
AND ((Recinfo.Attribute8 = X_Attribute8) OR
((Recinfo.Attribute8 IS NULL)
AND (X_Attribute8 IS NULL)))
AND ((Recinfo.Attribute9 = X_Attribute9) OR
((Recinfo.Attribute9 IS NULL)
AND (X_Attribute9 IS NULL)))
AND ((Recinfo.Attribute_Category = X_Attribute_Category) OR
((Recinfo.Attribute_Category IS NULL)
AND (X_Attribute_Category IS NULL)))
AND ((Recinfo.Discount_Amount_Remaining = X_Discount_Amount_Remaining) OR
((Recinfo.Discount_Amount_Remaining IS NULL)
AND (X_Discount_Amount_Remaining IS NULL)))
-- Third party payments
AND ((Recinfo.Remit_To_Supplier_Name = X_Remit_To_Supplier_Name) OR
((Recinfo.Remit_To_Supplier_Name IS NULL)
AND (X_Remit_To_Supplier_Name IS NULL)))
AND ((Recinfo.Remit_To_Supplier_Id = X_Remit_To_Supplier_Id) OR
((Recinfo.Remit_To_Supplier_Id IS NULL)
AND (X_Remit_To_Supplier_Id IS NULL)))
AND ((Recinfo.Remit_To_Supplier_Site = X_Remit_To_Supplier_Site) OR
((Recinfo.Remit_To_Supplier_Site IS NULL)
AND (X_Remit_To_Supplier_Site IS NULL)))
AND ((Recinfo.Remit_To_Supplier_Site_Id = X_Remit_To_Supplier_Site_Id) OR
((Recinfo.Remit_To_Supplier_Site_Id IS NULL)
AND (X_Remit_To_Supplier_Site_Id IS NULL)))
AND ((Recinfo.Relationship_Id = X_Relationship_Id) OR
((Recinfo.Relationship_Id IS NULL)
AND (X_Relationship_Id IS NULL)));