[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_last_update_login IN NUMBER,
X_gl_date IN OUT NOCOPY DATE,
X_period_name IN OUT NOCOPY VARCHAR2,
X_prepay_curr_amount_apply IN OUT NOCOPY NUMBER,
X_payment_cross_rate OUT NOCOPY NUMBER,
X_amount_positive OUT NOCOPY VARCHAR2,
X_orig_amount OUT NOCOPY NUMBER,
X_dist_item_amount OUT NOCOPY NUMBER,
X_dist_tax_amount OUT NOCOPY NUMBER,
X_currency_code OUT NOCOPY VARCHAR2,
X_base_currency OUT NOCOPY VARCHAR2,
X_min_unit OUT NOCOPY NUMBER,
X_precision OUT NOCOPY NUMBER,
X_base_min_unit OUT NOCOPY NUMBER,
X_base_precision OUT NOCOPY NUMBER,
X_pay_curr_min_unit OUT NOCOPY NUMBER,
X_pay_curr_precision OUT NOCOPY NUMBER,
X_max_dist OUT NOCOPY NUMBER,
X_orig_max_dist OUT NOCOPY NUMBER,
X_max_pay_num OUT NOCOPY NUMBER,
X_max_inv_pay OUT NOCOPY NUMBER,
X_copy_inv_pay_id OUT NOCOPY NUMBER,
/* Bug 3700128. MOAC Project */
X_org_id OUT NOCOPY NUMBER,
X_calling_from IN VARCHAR2,
X_calling_sequence IN VARCHAR2);
PROCEDURE appp_update_ap_invoices(
X_invoice_id IN NUMBER,
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_prepay_curr_amount_apply IN NUMBER,
X_user_id IN NUMBER,
X_base_currency IN VARCHAR2,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2);
PROCEDURE appp_insert_invoice_dist(
X_invoice_id IN NUMBER,
X_prepay_id IN NUMBER,
X_dist_line_amount IN NUMBER,
X_payment_cross_rate IN NUMBER,
X_max_dist IN OUT NOCOPY NUMBER,
X_copy_dist_num IN NUMBER,
X_user_id IN NUMBER,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_base_min_unit IN NUMBER,
X_base_precision IN NUMBER,
X_gl_date IN DATE,
X_period_name IN VARCHAR2,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2);
PROCEDURE appp_insert_invoice_payment(
X_prepay_id IN NUMBER,
X_new_invoice_id IN NUMBER,
X_amount_apply IN NUMBER,
X_prepay_curr_amount_apply IN NUMBER,
X_payment_cross_rate IN NUMBER,
X_copy_inv_pay_id IN NUMBER,
X_max_inv_pay IN OUT NOCOPY NUMBER,
X_orig_max_dist IN NUMBER,
X_user_id IN NUMBER,
X_currency_code IN VARCHAR2,
X_base_currency IN VARCHAR2,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_pay_curr_min_unit IN NUMBER,
X_pay_curr_precision IN NUMBER,
X_base_min_unit IN NUMBER,
X_base_precision IN NUMBER,
X_gl_date IN DATE,
X_period_name IN VARCHAR2,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2);
PROCEDURE appp_insert_payment_schedule(
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_prepay_curr_amount_apply IN NUMBER,
X_max_pay_num IN OUT NOCOPY NUMBER,
X_copy_payment_num IN NUMBER,
X_user_id IN NUMBER,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_pay_curr_min_unit IN NUMBER,
X_pay_curr_precision IN NUMBER,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2);
PROCEDURE appp_update_payment_schedule(
X_invoice_id IN NUMBER,
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_prepay_curr_amount_apply IN NUMBER,
X_payment_cross_rate IN NUMBER,
X_amount_positive IN VARCHAR2,
X_copy_inv_pay_id IN NUMBER,
X_orig_max_dist IN NUMBER,
X_user_id IN NUMBER,
X_currency_code IN VARCHAR2,
X_base_currency IN VARCHAR2,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_pay_curr_min_unit IN NUMBER,
X_pay_curr_precision IN NUMBER,
X_base_min_unit IN NUMBER,
X_base_precision IN NUMBER,
X_gl_date IN DATE,
X_period_name IN VARCHAR2,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2);
PROCEDURE appp_insert_invoice_prepay(
X_invoice_id IN NUMBER,
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_user_id IN NUMBER,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_last_update_login IN NUMBER,
/* Bug 3700128. MOAC Project */
X_org_id IN NUMBER,
X_calling_sequence IN VARCHAR2);
PROCEDURE app_update_inv_distributions(
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_calling_sequence IN VARCHAR2);
X_last_update_login IN NUMBER,
X_gl_date IN DATE,
X_period_name IN VARCHAR2,
X_calling_from IN VARCHAR2,
X_calling_sequence IN VARCHAR2) IS
current_calling_sequence VARCHAR2(2000);
X_last_update_login,
P_gl_date,
P_period_name,
P_prepay_curr_amount_apply,
P_payment_cross_rate,
P_amount_positive,
P_orig_amount,
P_dist_item_amount,
P_dist_tax_amount,
P_currency_code,
P_base_currency,
P_min_unit,
P_precision,
P_base_min_unit,
P_base_precision,
P_pay_curr_min_unit,
P_pay_curr_precision,
P_max_dist,
P_orig_max_dist,
P_max_pay_num,
P_max_inv_pay,
P_copy_inv_pay_id,
P_org_id, /* Bug 3700128. MOAC Project */
X_calling_from,
Current_calling_sequence);
* -- Step 2p : case p: Prepayment: Update AP_INVOICES
* Call appp_update_ap_invoices:
* 1. Reduce the prepayment amount (invoice_amount) become
* (invoice_amount - amount_apply)
* 2. Reduce amount_paid, invoice_distribution_total, and base_amount
* as well
* (converse for Unapplication)
*
*--------------------------------------------------------------------------*/
ap_r11_prepay_pkg.appp_update_ap_invoices(
'',
X_prepay_id,
X_amount_apply,
P_prepay_curr_amount_apply,
X_user_id,
P_base_currency,
P_base_min_unit,
P_base_precision,
X_last_update_login,
Current_calling_sequence);
* -- Step 3p : case p : Prepayment: Insert AP_INVOICE_DISTRIBUTIONS (ITEM)
* Call appp_insert_invoice_dist:
* Create reversing ITEM distribution on the Prepayment, We presume dist line 1
* is item line. (converse for unapplication (amount_apply < 0) )
*--------------------------------------------------------------------------*/
ap_r11_prepay_pkg.appp_insert_invoice_dist(
X_invoice_id,
X_prepay_id,
P_dist_item_amount,
P_payment_cross_rate,
P_max_dist, /* IN/OUT parameter*/
1, /* Line 1 is item line */
X_user_id,
P_min_unit,
P_precision,
P_base_min_unit,
P_base_precision,
P_gl_date,
P_period_name,
X_last_update_login,
Current_calling_sequence);
* -- Step 4p : case p : Prepayment: Insert AP_INVOICE_DISTRIBUTIONS (TAX)
* Call appp_insert_invoice_dist:
* Create reversing TAX distribution on the Prepayment if applicable,
* we presume dist line 2 is tax line. (converse for unapplication)
*--------------------------------------------------------------------------*/
if (NVL(P_dist_tax_amount,0) <> 0) then
ap_r11_prepay_pkg.appp_insert_invoice_dist(
X_invoice_id,
X_prepay_id,
P_dist_tax_amount,
P_payment_cross_rate,
P_max_dist, /* Add 1 from above */ /* IN/OUT parameter*/
2, /* Line 2 in Tax line */
X_user_id,
P_min_unit,
P_precision,
P_base_min_unit,
P_base_precision,
P_gl_date,
P_period_name,
X_last_update_login,
Current_calling_sequence);
* -- Step 5p : case p : Prepayment: Insert AP_PAYMENT_SCHEDULES
* Call appp_insert_payment_schedule :
* Create additional paid Payment Schedule for the Prepayment.
* (converse for Unapplication)
*--------------------------------------------------------------------------*/
ap_r11_prepay_pkg.appp_insert_payment_schedule(
X_prepay_id,
X_amount_apply,
P_prepay_curr_amount_apply,
P_max_pay_num, /* IN/OUT parameter */
1, /* Line 1 will be copied into new line*/
X_user_id,
P_min_unit,
P_precision,
P_pay_curr_min_unit,
P_pay_curr_precision,
X_last_update_login,
Current_calling_sequence);
* -- Step 6p : case p : Prepayment : Insert AP_INVOICE_PAYMENTS
* Call appp_insert_invoice_payment :
* Create new positive Invoice Payments for the Prepayment (converse
* for Unapplication)
*--------------------------------------------------------------------------*/
ap_r11_prepay_pkg.appp_insert_invoice_payment(
X_invoice_id,
X_prepay_id,
X_amount_apply,
P_prepay_curr_amount_apply,
P_payment_cross_rate,
P_copy_inv_pay_id,
P_max_inv_pay, /* IN/OUT parameter*/
P_orig_max_dist,
X_user_id,
P_currency_code,
P_base_currency,
P_min_unit,
P_precision,
P_pay_curr_min_unit,
P_pay_curr_precision,
P_base_min_unit,
P_base_precision,
P_gl_date,
P_period_name,
X_last_update_login,
Current_calling_sequence);
* -- Step 7i : case i: Invoice : Update AP_INVOICES
* Call appp_update_ap_invoices:
* 1. Add the amount_apply to amount_paid for refelecting the payment
* amount change.
* 2. Update discount_amount_taken, payment_status_flag as well
* (converse for Unapplication)
* Reversed order of this and next step for Rel11 'cos calc of ROUNDING
* type payment distributions depends upon the payment_status_flag of the
* invoice. Since the next step also creates payment dists, we should
* first update the payment_status_flag on the invoice.
*--------------------------------------------------------------------------*/
ap_r11_prepay_pkg.appp_update_ap_invoices(
X_invoice_id,
'',
X_amount_apply,
P_prepay_curr_amount_apply,
X_user_id,
P_base_currency,
P_pay_curr_min_unit,
P_pay_curr_precision,
X_last_update_login,
Current_calling_sequence);
* -- Step 8i : case i : Invoice : Update AP_PAYMENT_SCHEDULES
* Call appp_update_payment_schedule :
*
* 1. Update the Payment Schedules and create new Invoice Payments on the
* Invoice to reflect the effective payment (converse for Unapplication)
* 2. Insert a new line for ap_invoice_payment to reflect the effective
* payment amount.
*--------------------------------------------------------------------------*/
ap_r11_prepay_pkg.appp_update_payment_schedule(
X_invoice_id,
X_prepay_id,
X_amount_apply,
P_prepay_curr_amount_apply,
P_payment_cross_rate,
P_amount_positive,
P_copy_inv_pay_id,
P_orig_max_dist,
X_user_id,
P_currency_code,
P_base_currency,
P_min_unit,
P_precision,
P_pay_curr_min_unit,
P_pay_curr_precision,
P_base_min_unit,
P_base_precision,
P_gl_date,
P_period_name,
X_last_update_login,
Current_calling_sequence);
* -- Step 9ip : case i and p: Invoice: prepayment : Update AP_INVOICE_PREPAYS
* Call appp_insert_invoice_prepay:
* 1. Update ap_invoice_prepays if there's a invoice_prepay line exit.
* 2. Delete record if unapply the prepayment.
* 3. Insert new line if there's no such record exist
---------------------------------------------------------------------------*/
ap_r11_prepay_pkg.appp_insert_invoice_prepay(
X_invoice_id,
X_prepay_id,
X_amount_apply,
X_user_id,
P_pay_curr_min_unit,
P_pay_curr_precision,
X_last_update_login,
/* Bug 3700128. MOAC Project */
P_org_id,
Current_calling_sequence);
app_update_inv_distributions(
X_prepay_id,
X_amount_apply,
Current_calling_sequence);
| | | Use for insert a new dist line |
+---------------------------------------------------------------------+
| X_orig_max_dist | No(*) | Because X_max_dist is updatable, |
| | | keep a very original max_dist |
| | | Use only for updating ap_payment_sche.|
+---------------------------------------------------------------------+
| X_max_pay_num | No(*) | max(payment_num) from ap_payment_sche.|
+---------------------------------------------------------------------+
| X_max_inv_pay | No(*) | max(payment_num) from ap_invoice_paym.|
+---------------------------------------------------------------------+
| X_copy_inv_pay_id | No(*) | max(invoice_payment_id), it means we |
| | | copy the last line of invoice_payment |
| | | when we create a new line |
+---------------------------------------------------------------------+
* Currupted data if NULL.
*=====================================================================*/
PROCEDURE ap_prepay_get_info(
X_prepay_id IN NUMBER,
X_invoice_id IN NUMBER,
X_amount_apply IN NUMBER,
X_user_id IN NUMBER,
X_last_update_login IN NUMBER,
X_gl_date IN OUT NOCOPY DATE,
X_period_name IN OUT NOCOPY VARCHAR2,
X_prepay_curr_amount_apply IN OUT NOCOPY NUMBER,
X_payment_cross_rate OUT NOCOPY NUMBER,
X_amount_positive OUT NOCOPY VARCHAR2,
X_orig_amount OUT NOCOPY NUMBER,
X_dist_item_amount OUT NOCOPY NUMBER,
X_dist_tax_amount OUT NOCOPY NUMBER,
X_currency_code OUT NOCOPY VARCHAR2,
X_base_currency OUT NOCOPY VARCHAR2,
X_min_unit OUT NOCOPY NUMBER,
X_precision OUT NOCOPY NUMBER,
X_base_min_unit OUT NOCOPY NUMBER,
X_base_precision OUT NOCOPY NUMBER,
X_pay_curr_min_unit OUT NOCOPY NUMBER,
X_pay_curr_precision OUT NOCOPY NUMBER,
X_max_dist OUT NOCOPY NUMBER,
X_orig_max_dist OUT NOCOPY NUMBER,
X_max_pay_num OUT NOCOPY NUMBER,
X_max_inv_pay OUT NOCOPY NUMBER,
X_copy_inv_pay_id OUT NOCOPY NUMBER,
/* Bug 3700128. MOAC Project */
X_org_id OUT NOCOPY NUMBER,
X_calling_from IN VARCHAR2,
X_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
SELECT DECODE((SIGN(X_amount_apply)), 1, 'Y', 'N')
INTO X_amount_positive
FROM sys.dual;
SELECT 'Not overapplying'
INTO DUMMY
FROM ap_payment_schedules
WHERE invoice_id = X_invoice_id
GROUP BY invoice_id
HAVING sum(nvl(amount_remaining, 0)) >= X_amount_apply;
SELECT 'Not applying more than available'
INTO DUMMY
FROM ap_invoices
WHERE invoice_id = X_prepay_id
AND invoice_amount >= X_amount_apply;
SELECT invoice_num
INTO invoice_number
FROM ap_invoices
WHERE invoice_id = X_invoice_id;
SELECT 'Not applying more than not on hold'
INTO DUMMY
FROM ap_payment_schedules
WHERE invoice_id = X_invoice_id
AND hold_flag <> 'Y'
GROUP BY invoice_id
HAVING sum(nvl(amount_remaining, 0)) >= X_amount_apply;
SELECT invoice_currency_code, payment_cross_rate,
nvl(pay_curr_invoice_amount, invoice_amount),
invoice_amount, invoice_currency_code,
payment_cross_rate,
original_prepayment_amount
INTO X_currency_code, X_payment_cross_rate,
C_pay_curr_invoice_amount,
C_invoice_amount, C_currency_code,
C_payment_cross_rate,
C_orig_prepay_amount
FROM ap_invoices
WHERE invoice_id = X_prepay_id;
Selected org_id also so that the same can be used for
insertion at later point of time */
SELECT base_currency_code,org_id
INTO X_base_currency,X_org_id
FROM ap_system_parameters;
SELECT minimum_accountable_unit, nvl(precision,0)
INTO X_min_unit, X_precision
FROM fnd_currencies
WHERE currency_code = C_currency_code;
SELECT minimum_accountable_unit, nvl(precision,0)
INTO C_min_unit, C_precision
FROM fnd_currencies
WHERE currency_code = C_currency_code;
SELECT MINIMUM_ACCOUNTABLE_UNIT, nvl(PRECISION,0)
INTO X_base_min_unit , X_base_precision
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = ( SELECT BASE_CURRENCY_CODE
FROM AP_SYSTEM_PARAMETERS);
SELECT minimum_accountable_unit, nvl(precision,0),
minimum_accountable_unit, nvl(precision,0)
INTO X_pay_curr_min_unit, X_pay_curr_precision,
C_pay_curr_min_unit, C_pay_curr_precision
FROM fnd_currencies
WHERE currency_code = ( SELECT payment_currency_code
FROM ap_invoices
WHERE invoice_id = X_prepay_id);
SELECT gross_amount
INTO C_gross_amount
FROM ap_payment_schedules
WHERE invoice_id = X_prepay_id
AND payment_num = 1;
SELECT nvl(I.original_prepayment_amount,0),
ap_utilities_pkg.ap_round_precision(
D1.amount/I.original_prepayment_amount *
X_amount_apply,
C_pay_curr_min_unit, C_pay_curr_precision),
DECODE(D2.line_type_lookup_code,
'ITEM', 0, null, 0,
(X_amount_apply -
ap_utilities_pkg.ap_round_precision(
D1.amount/I.original_prepayment_amount *
X_amount_apply,
C_pay_curr_min_unit, C_pay_curr_precision)))
INTO X_orig_amount,
X_dist_item_amount,
X_dist_tax_amount
FROM ap_invoices_all I, ap_invoice_distributions D1,
ap_invoice_distributions_all D2
WHERE I.invoice_id = X_prepay_id
AND D1.invoice_id = I.invoice_id
AND D1.distribution_line_number = 1
AND D2.invoice_id(+) = D1.invoice_id -- Perf bug 5058989 -- replace I. with D1.
AND D2.distribution_line_number(+) = 2;
SELECT max(distribution_line_number),max(distribution_line_number)
INTO X_max_dist, X_orig_max_dist
FROM ap_invoice_distributions
WHERE invoice_id = X_prepay_id;
SELECT max(payment_num)
INTO X_max_pay_num
FROM ap_payment_schedules
WHERE invoice_id = X_prepay_id;
SELECT max(payment_num),
max(decode(payment_num,1,invoice_payment_id,0))
INTO X_max_inv_pay,
X_copy_inv_pay_id
FROM ap_invoice_payments
WHERE invoice_id = X_prepay_id;
SELECT G.period_name
INTO X_period_name
FROM gl_period_statuses G, ap_system_parameters P
WHERE G.application_id = 200
AND G.set_of_books_id = P.set_of_books_id
AND DECODE(X_gl_date, '',
sysdate, X_gl_date) between G.start_date and G.end_date
AND G.closing_status in ('O', 'F')
AND NVL(G.adjustment_period_flag, 'N') = 'N';
||' Last_update_login = '||TO_CHAR(X_last_update_login)
||' gl_date = '||TO_CHAR(X_gl_date)
||' Period_name = '||X_period_name);
||' Last_update_login = '||TO_CHAR(X_last_update_login)
||' gl_date = '||TO_CHAR(X_gl_date)
||' Period_name = '||X_period_name);
* 1.2 Update discount_amount_taken, payment_status_flag as well
*
* Distingrish case for invoice or prepayment depend on Null value passing.
* (Invoice: prepay_id is NULL; Prepayment: invoice_id is NULL)
PROCEDURE appp_update_ap_invoices(
X_invoice_id IN NUMBER,
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_prepay_curr_amount_apply IN NUMBER,
X_user_id IN NUMBER,
X_base_currency IN VARCHAR2,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
current_calling_sequence := 'appp_update_ap_invoices<-'||X_calling_sequence;
if (X_invoice_id is NULL) then /* Update prepayment info */
debug_info := 'Update ap_invoice for reducing the amount';
UPDATE ap_invoices
SET invoice_amount = invoice_amount - X_prepay_curr_amount_apply,
pay_curr_invoice_amount = nvl(pay_curr_invoice_amount, invoice_amount)
- X_amount_apply,
amount_paid = amount_paid - X_amount_apply,
invoice_distribution_total = invoice_distribution_total -
X_amount_apply,
base_amount = DECODE(invoice_currency_code,
X_base_currency, base_amount,
base_amount -
ap_utilities_pkg.ap_round_precision(
exchange_rate * X_prepay_curr_amount_apply,
X_min_unit, X_precision)),
last_update_date = SYSDATE,
last_updated_by = X_user_id,
last_update_login = X_last_update_login
WHERE invoice_id = X_prepay_id;
else /* Update invoice info*/
UPDATE ap_invoices
SET amount_paid = nvl(amount_paid, 0) +
ap_utilities_pkg.ap_round_precision(
X_amount_apply, X_min_unit, X_precision),
discount_amount_taken = nvl(discount_amount_taken, 0),
payment_status_flag =
DECODE(NVL(amount_paid, 0) + NVL(discount_amount_taken, 0) +
ap_utilities_pkg.ap_round_precision(
X_amount_apply, X_min_unit, X_precision),
nvl(pay_curr_invoice_amount, invoice_amount), 'Y',
0,'N',
'P'),
last_update_date = SYSDATE,
last_updated_by = X_user_id,
last_update_login = X_last_update_login
WHERE invoice_id = X_invoice_id;
||' Last_update_login = '||TO_CHAR(X_last_update_login)
||' Base_currency = '||X_base_currency
||' Min_unit = '||TO_CHAR(X_min_unit)
||' Precision = '||TO_CHAR(X_precision));
END appp_update_ap_invoices;
* -- the parameter X_invoice is only used for insert other_invoice_id
*
==========================================================================*/
PROCEDURE appp_insert_invoice_dist(
X_invoice_id IN NUMBER,
X_prepay_id IN NUMBER,
X_dist_line_amount IN NUMBER,
X_payment_cross_rate IN NUMBER,
X_max_dist IN OUT NOCOPY NUMBER,
X_copy_dist_num IN NUMBER,
X_user_id IN NUMBER,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_base_min_unit IN NUMBER,
X_base_precision IN NUMBER,
X_gl_date IN DATE,
X_period_name IN VARCHAR2,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
current_calling_sequence := 'appp_insert_invoice_dist<-'||X_calling_sequence;
SELECT ap_invoice_distributions_s.NEXTVAL
INTO l_invoice_distribution_id
FROM sys.dual; -- added for Invoice_Distribution_Id
debug_info := 'Update ap_invoice_distributions for creating the distribution reversals';
INSERT INTO AP_INVOICE_DISTRIBUTIONS
(invoice_id,
dist_code_combination_id,
last_update_date,
last_updated_by,
accounting_date,
period_name,
set_of_books_id,
amount,
description,
type_1099,
vat_code,
posted_flag,
batch_id,
req_distribution_id,
quantity_invoiced,
unit_price,
price_adjustment_flag,
earliest_settlement_date,
assets_addition_flag,
distribution_line_number,
line_type_lookup_code,
base_amount,
exchange_rate,
exchange_rate_type,
exchange_date,
accrual_posted_flag,
cash_posted_flag,
assets_tracking_flag,
pa_addition_flag,
other_invoice_id,
last_update_login,
creation_date,
created_by,
invoice_distribution_id,
tax_code_id,
tax_code_override_flag,
tax_recovery_override_flag,
tax_recoverable_flag,
org_id ) /* Bug 3700128. MOAC Project */
SELECT invoice_id,
dist_code_combination_id,
SYSDATE,
X_user_id,
X_gl_date,
X_period_name,
set_of_books_id,
ap_utilities_pkg.ap_round_precision(
(-1) * X_dist_line_amount / X_payment_cross_rate,
X_min_unit, X_precision),
'Prepayment Application',
type_1099,
vat_code,
'N',
batch_id,
req_distribution_id,
quantity_invoiced,
unit_price,
price_adjustment_flag,
earliest_settlement_date,
'U',
new_line_num,
line_type_lookup_code,
DECODE(base_amount, null, null,
ap_utilities_pkg.ap_round_precision(
(-1) * exchange_rate * X_dist_line_amount
/ X_payment_cross_rate,
X_base_min_unit, X_base_precision)),
exchange_rate,
exchange_rate_type,
exchange_date,
'N',
'N',
assets_tracking_flag,
'E',
X_invoice_id,
DECODE(X_last_update_login, -999, null, X_last_update_login),
SYSDATE,
X_user_id,
l_invoice_distribution_id,
tax_code_id,
tax_code_override_flag,
tax_recovery_override_flag,
tax_recoverable_flag,
org_id /* Bug 3700128. MOAC Project */
FROM ap_invoice_distributions
WHERE invoice_id = X_prepay_id
AND distribution_line_number = X_copy_dist_num;
||' Last_update_login = '||TO_CHAR(X_last_update_login)
||' Base_min_unit = '||TO_CHAR(X_base_min_unit)
||' Base_precision = '||TO_CHAR(X_base_precision)
||' Min_unit = '||TO_CHAR(X_min_unit)
||' Precision = '||TO_CHAR(X_precision)
||' gl_date = '||TO_CHAR(X_gl_date)
||' Period_name = '||X_period_name);
END appp_insert_invoice_dist;
on the prepayment invoice (Insert AP_PAYMENT_SCHEDULE)
*========================================================================*/
PROCEDURE appp_insert_payment_schedule(
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_prepay_curr_amount_apply IN NUMBER,
X_max_pay_num IN OUT NOCOPY NUMBER,
X_copy_payment_num IN NUMBER,
X_user_id IN NUMBER,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_pay_curr_min_unit IN NUMBER,
X_pay_curr_precision IN NUMBER,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
current_calling_sequence := 'appp_insert_payment_schedule<-'||X_calling_sequence;
INSERT INTO AP_PAYMENT_SCHEDULES(
invoice_id,
payment_num,
last_update_date,
last_updated_by,
due_date,
discount_date,
gross_amount,
inv_curr_gross_amount,
amount_remaining,
discount_amount_remaining,
payment_priority,
payment_method_code, --4552701
hold_flag,
payment_status_flag,
batch_id,
payment_cross_rate,
future_pay_due_date,
last_update_login,
creation_date,
created_by,
org_id ) /* Bug 3700128. MOAC Project */
SELECT invoice_id,
new_line_num,
SYSDATE,
X_user_id,
SYSDATE,
SYSDATE,
ap_utilities_pkg.ap_round_precision(
(-1) * X_amount_apply, X_pay_curr_min_unit,
X_pay_curr_precision),
ap_utilities_pkg.ap_round_precision(
(-1) * X_prepay_curr_amount_apply,
X_min_unit, X_precision),
0,
0,
payment_priority,
payment_method_code, --4552701
'N',
'Y',
batch_id,
payment_cross_rate,
future_pay_due_date,
DECODE(X_last_update_login, -999, null, X_last_update_login),
SYSDATE,
X_user_id,
org_id /* Bug 3700128. MOAC Project */
FROM ap_payment_schedules
WHERE invoice_id = X_prepay_id
AND payment_num = X_copy_payment_num;
||' Last_update_login = '||TO_CHAR(X_last_update_login)
||' Min_unit = '||TO_CHAR(X_min_unit)
||' Precision = '||TO_CHAR(X_precision));
END appp_insert_payment_schedule;
* 1. Update the paid payment schedules for this invoice. *
* 2. Insert a new line for ap_invoice_payment to reflect the effective *
* payment amount. (call appp_insert_invoice_payment) *
*===========================================================================*/
PROCEDURE appp_update_payment_schedule(
X_invoice_id IN NUMBER,
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_prepay_curr_amount_apply IN NUMBER,
X_payment_cross_rate IN NUMBER,
X_amount_positive IN VARCHAR2,
X_copy_inv_pay_id IN NUMBER,
X_orig_max_dist IN NUMBER,
X_user_id IN NUMBER,
X_currency_code IN VARCHAR2,
X_base_currency IN VARCHAR2,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_pay_curr_min_unit IN NUMBER,
X_pay_curr_precision IN NUMBER,
X_base_min_unit IN NUMBER,
X_base_precision IN NUMBER,
X_gl_date IN DATE,
X_period_name IN VARCHAR2,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
SELECT payment_num,
DECODE(X_amount_positive,
'N', gross_amount - amount_remaining,
amount_remaining)
--
-- gross_amount - amount_remaining = amount_paid.<- No database column
--
FROM ap_payment_schedules
WHERE invoice_id = X_invoice_id
AND (payment_status_flag||'' = 'P'
OR payment_status_flag||'' = DECODE(X_amount_positive, 'N', 'Y', 'N'))
ORDER BY DECODE(X_amount_positive,
'N', DECODE(payment_status_flag,'P',1,'Y',2,3),
DECODE(NVL(hold_flag,'N'),'N',1,2)),
DECODE(X_amount_positive,
'N', due_date,
NULL) DESC,
DECODE(X_amount_positive,
'N', NULL,
due_date),
DECODE(X_amount_positive,
'N', DECODE(hold_flag,'N',1,'Y',2,3),
DECODE(NVL(payment_status_flag,'N'),'P',1,'N',2,3));
current_calling_sequence := 'appp_update_payment_schedule<-'||X_calling_sequence;
* Update the amount_remaining for this payment schedule line become *
* (amount_remaining - amount_apply_remaining). *
+-----------------------------------------------------------------------*/
debug_info := 'Update ap_payment_schedule for the invoice, case 1';
UPDATE ap_payment_schedules
SET amount_remaining = (amount_remaining -
ap_utilities_pkg.ap_round_precision(
C_amount_apply_remaining,
X_pay_curr_min_unit, X_pay_curr_precision)),
payment_status_flag =
DECODE(amount_remaining -
ap_utilities_pkg.ap_round_precision(
C_amount_apply_remaining,
X_pay_curr_min_unit, X_pay_curr_precision),
0,'Y',
gross_amount, 'N',
'P'),
last_update_date = SYSDATE,
last_updated_by = X_user_id,
last_update_login = X_last_update_login
WHERE invoice_id = X_invoice_id
AND payment_num = C_local_pay_num;
debug_info := 'Call appp_insert_invoice_payment , case 1';
AP_R11_PREPAY_PKG.appp_insert_invoice_payment(
X_prepay_id,
X_invoice_id,
C_amount_apply_remaining,
X_prepay_curr_amount_apply,
X_payment_cross_rate,
X_copy_inv_pay_id,
Temp_local_pay_num,
X_orig_max_dist,
X_user_id,
X_currency_code,
X_base_currency,
X_min_unit,
X_precision,
X_pay_curr_min_unit,
X_pay_curr_precision,
X_base_min_unit,
X_base_precision,
X_gl_date,
X_period_name,
X_last_update_login,
Current_calling_sequence);
* Update the amount_remaining to 0 and amount_apply_remaining become *
* (amount_apply - amount_remaining(this line)), then go to next *
* schedule line. *
*----------------------------------------------------------------------*/
debug_info := 'Update ap_payment_schedule for the invoice, case 2';
UPDATE ap_payment_schedules
SET amount_remaining = DECODE(X_amount_positive,
'Y', 0,
gross_amount),
payment_status_flag = DECODE(X_amount_positive,
'Y', 'Y',
'N'),
last_update_date = SYSDATE,
last_updated_by = X_user_id,
last_update_login = X_last_update_login
WHERE invoice_id = X_invoice_id
AND payment_num = C_local_pay_num;
AP_R11_PREPAY_PKG.appp_insert_invoice_payment(
X_prepay_id,
X_invoice_id,
C_local_amount, /* Difference from above */
X_prepay_curr_amount_apply,
X_payment_cross_rate,
X_copy_inv_pay_id,
Temp_local_pay_num, /* See notice above */
X_orig_max_dist,
X_user_id,
X_currency_code,
X_base_currency,
X_min_unit,
X_precision,
X_pay_curr_min_unit,
X_pay_curr_precision,
X_base_min_unit,
X_base_precision,
X_gl_date,
X_period_name,
X_last_update_login,
Current_calling_sequence);
AP_R11_PREPAY_PKG.appp_insert_invoice_payment(
X_prepay_id,
X_invoice_id,
(-1)*C_local_amount, /* Difference from above */
X_prepay_curr_amount_apply,
X_payment_cross_rate,
X_copy_inv_pay_id,
Temp_local_pay_num, /* See notice above */
X_orig_max_dist,
X_user_id,
X_currency_code,
X_base_currency,
X_min_unit,
X_precision,
X_pay_curr_min_unit,
X_pay_curr_precision,
X_base_min_unit,
X_base_precision,
X_gl_date,
X_period_name,
X_last_update_login,
Current_calling_sequence);
||' Last_update_login = '||TO_CHAR(X_last_update_login)
||' Currency_code = '||X_currency_code
||' Base_currency = '||X_base_currency
||' Base_min_unit = '||TO_CHAR(X_base_min_unit)
||' Base_precision = '||TO_CHAR(X_base_precision)
||' Min_unit = '||TO_CHAR(X_min_unit)
||' Precision = '||TO_CHAR(X_precision)
||' gl_date = '||TO_CHAR(X_gl_date)
||' Period_name = '||X_period_name);
END appp_update_payment_schedule;
1. Invoice type is prepayment: insert ap_payment_distribution. It separate
into 3 steps, see below for detail.
2. Invoice type is invoice: use AP_CREATE_PAY_DISTS_PKG.distribution_payment
to create payment distribution line
*==========================================================================*/
PROCEDURE appp_insert_invoice_payment(
X_prepay_id IN NUMBER,
X_new_invoice_id IN NUMBER,
X_amount_apply IN NUMBER,
X_prepay_curr_amount_apply IN NUMBER,
X_payment_cross_rate IN NUMBER,
X_copy_inv_pay_id IN NUMBER,
X_max_inv_pay IN OUT NOCOPY NUMBER,
X_orig_max_dist IN NUMBER,
X_user_id IN NUMBER,
X_currency_code IN VARCHAR2,
X_base_currency IN VARCHAR2,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_pay_curr_min_unit IN NUMBER,
X_pay_curr_precision IN NUMBER,
X_base_min_unit IN NUMBER,
X_base_precision IN NUMBER,
X_gl_date IN DATE,
X_period_name IN VARCHAR2,
X_last_update_login IN NUMBER,
X_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
current_calling_sequence := 'appp_insert_invoice_payment<-'||X_calling_sequence;
SELECT check_id
INTO C_check_id
FROM ap_invoice_payments
WHERE invoice_payment_id = X_copy_inv_pay_id;
SELECT invoice_type_lookup_code
INTO C_invoice_type
FROM ap_invoices
WHERE invoice_id = X_new_invoice_id;
SELECT payment_type_flag
INTO C_payment_type
FROM ap_checks
WHERE check_id = c_check_id;
INSERT INTO AP_INVOICE_PAYMENTS(
invoice_payment_id,
invoice_id,
payment_num,
check_id,
amount,
last_update_date,
last_updated_by,
set_of_books_id,
posted_flag,
accrual_posted_flag,
cash_posted_flag,
electronic_transfer_id,
accts_pay_code_combination_id,
accounting_date,
period_name,
exchange_rate_type,
exchange_rate,
exchange_date,
discount_lost,
invoice_base_amount,
payment_base_amount,
asset_code_combination_id,
gain_code_combination_id,
loss_code_combination_id,
bank_account_num,
bank_num,
bank_account_type,
future_pay_code_combination_id,
future_pay_posted_flag,
last_update_login,
creation_date,
created_by,
invoice_payment_type,
other_invoice_id,
org_id ) /* Bug 3700128. MOAC Project */
SELECT ap_invoice_payments_s.nextval,
X_new_invoice_id,
new_line_num,
P.check_id,
DECODE(P.invoice_id, X_new_invoice_id,
ap_utilities_pkg.ap_round_precision(
(-1) * X_amount_apply, X_pay_curr_min_unit,
X_pay_curr_precision),
ap_utilities_pkg.ap_round_precision(
X_amount_apply, X_pay_curr_min_unit,
X_pay_curr_precision)),
SYSDATE,
X_user_id,
P.set_of_books_id,
'N',
'N',
'N',
P.electronic_transfer_id,
decode(X_new_invoice_id, P.invoice_id,
P.accts_pay_code_combination_id,
I.accts_pay_code_combination_id),
X_gl_date,
X_period_name,
P.exchange_rate_type,
P.exchange_rate,
P.exchange_date,
0,
DECODE(P.invoice_id, X_new_invoice_id,
ap_utilities_pkg.ap_round_precision(
(-1) *
decode(I.invoice_currency_code,
ASP.base_currency_code,
decode(I.payment_currency_code,
ASP.base_currency_code,
I.exchange_rate, 1),
I.exchange_rate)
* X_amount_apply
/ X_payment_cross_rate,
X_base_min_unit, X_base_precision),
ap_utilities_pkg.ap_round_precision(
decode(I.invoice_currency_code,
ASP.base_currency_code,
decode(I.payment_currency_code,
ASP.base_currency_code,
I.exchange_rate, 1),
I.exchange_rate) * X_amount_apply
/ X_payment_cross_rate,
X_base_min_unit, X_base_precision)),
DECODE(P.invoice_id, X_new_invoice_id,
ap_utilities_pkg.ap_round_precision(
(-1) *
decode(I.payment_currency_code,
ASP.base_currency_code,
decode(I.invoice_currency_code,
ASP.base_currency_code,
P.exchange_rate, 1),
P.exchange_rate)
* X_amount_apply,
X_base_min_unit, X_base_precision),
ap_utilities_pkg.ap_round_precision(
decode(I.payment_currency_code,
ASP.base_currency_code,
decode(I.invoice_currency_code,
ASP.base_currency_code,
P.exchange_rate, 1),
P.exchange_rate) * X_amount_apply,
X_base_min_unit, X_base_precision)),
P.asset_code_combination_id,
P.gain_code_combination_id,
P.loss_code_combination_id,
P.bank_account_num,
P.bank_num,
P.bank_account_type,
P.future_pay_code_combination_id,
'N',
DECODE(X_last_update_login, -999, null, X_last_update_login),
sysdate,
X_user_id,
'PREPAY',
X_prepay_id,
I.org_id /* Bug 3700128. MOAC Project */
FROM ap_invoice_payments P, ap_invoices I, ap_system_parameters ASP
WHERE I.invoice_id = X_new_invoice_id
AND P.invoice_payment_id = X_copy_inv_pay_id
AND ASP.set_of_books_id = I.set_of_books_id;
||' Last_update_login = '||TO_CHAR(X_last_update_login)
||' Currency_code = '||X_currency_code
||' Base_currency = '||X_base_currency
||' Base_min_unit = '||TO_CHAR(X_base_min_unit)
||' Base_precision = '||TO_CHAR(X_base_precision)
||' Min_unit = '||TO_CHAR(X_min_unit)
||' Precision = '||TO_CHAR(X_precision)
||' gl_date = '||TO_CHAR(X_gl_date)
||' Period_name = '||X_period_name);
||' Last_update_login = '||TO_CHAR(X_last_update_login)
||' Currency_code = '||X_currency_code
||' Base_currency = '||X_base_currency
||' Base_min_unit = '||TO_CHAR(X_base_min_unit)
||' Base_precision = '||TO_CHAR(X_base_precision)
||' Min_unit = '||TO_CHAR(X_min_unit)
||' Precision = '||TO_CHAR(X_precision)
||' gl_date = '||TO_CHAR(X_gl_date)
||' Period_name = '||X_period_name);
END appp_insert_invoice_payment;
1. Update ap_invoice_prepays if there's a invoice_prepay line exit.
2. Delete record if unapply the prepayment.
3. Insert new line if there's no such record exist
===========================================================================*/
PROCEDURE appp_insert_invoice_prepay(
X_invoice_id IN NUMBER,
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_user_id IN NUMBER,
X_min_unit IN NUMBER,
X_precision IN NUMBER,
X_last_update_login IN NUMBER,
/* Bug 3700128. MOAC Project */
X_org_id IN NUMBER,
X_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
current_calling_sequence := 'appp_insert_invoice_prepay<-'||X_calling_sequence;
debug_info := 'Update ap_invoice_prepays';
UPDATE ap_invoice_prepays
SET prepayment_amount_applied = prepayment_amount_applied +
ap_utilities_pkg.ap_round_precision(
X_amount_apply, X_min_unit, X_precision),
last_update_date = SYSDATE,
last_updated_by = X_user_id,
last_update_login = X_last_update_login
WHERE prepay_id = X_prepay_id
AND invoice_id = X_invoice_id;
debug_info := 'Delete record from ap_invoice_prepays';
DELETE FROM ap_invoice_prepays
WHERE prepay_id = X_prepay_id
AND invoice_id = X_invoice_id
AND prepayment_amount_applied = 0;
debug_info := 'Insert record from ap_invoice_prepays';
INSERT INTO ap_invoice_prepays(
prepay_id,
invoice_id,
prepayment_amount_applied,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
org_id ) /* Bug 3700128. MOAC Project */
SELECT X_prepay_id,
X_invoice_id,
ap_utilities_pkg.ap_round_precision(
X_amount_apply, X_min_unit, X_precision),
SYSDATE,
X_user_id,
DECODE(X_last_update_login, -999, null, X_last_update_login),
SYSDATE,
X_user_id,
X_org_id /* Bug 3700128. MOAC Project */
FROM SYS.DUAL
WHERE NOT EXISTS (
SELECT 'Already updated existing record'
FROM ap_invoice_prepays
WHERE prepay_id = X_prepay_id
AND invoice_id = X_invoice_id);
||' Last_update_login = '||TO_CHAR(X_last_update_login)
||' Min_unit = '||TO_CHAR(X_min_unit)
||' Precision = '||TO_CHAR(X_precision));
END appp_insert_invoice_prepay;
PROCEDURE app_update_inv_distributions(
X_prepay_id IN NUMBER,
X_amount_apply IN NUMBER,
X_calling_sequence IN VARCHAR2) IS
debug_info VARCHAR2(100);
current_calling_sequence := 'appp_insert_invoice_prepay<-'||X_calling_sequence;
UPDATE ap_invoice_distributions AID
SET reversal_flag = 'Y'
WHERE AID.line_type_lookup_code = 'ITEM'
AND AID.distribution_line_number > 1
AND AID.invoice_id = X_prepay_id;
SELECT prepay_amount_remaining
INTO l_prepay_amt_remaining
FROM ap_invoice_distributions
WHERE invoice_id = X_prepay_id
AND distribution_line_number = 1
AND line_type_lookup_code = 'ITEM';
UPDATE ap_invoice_distributions AID
SET prepay_amount_remaining = (
SELECT sum(AID2.amount)
FROM ap_invoice_distributions AID2
WHERE AID.invoice_id = AID2.invoice_id
AND AID2.line_type_lookup_code = 'ITEM')
WHERE invoice_id = X_prepay_id
AND AID.distribution_line_number = 1;
UPDATE ap_invoice_distributions
SET prepay_amount_remaining = l_prepay_amt_remaining - X_amount_apply
WHERE invoice_id = X_prepay_id
AND distribution_line_number = 1;
END app_update_inv_distributions;