[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE select_credit_to_apply( p_currency_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_credit_ps_id OUT NOCOPY NUMBER,
x_debit_ps_id OUT NOCOPY NUMBER);
SELECT
CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
ACCOUNT_NUMBER,
CUSTOMER_TRX_ID,
TRX_NUMBER,TRX_DATE,
TRX_CLASS,
DUE_DATE,
PAYMENT_SCHEDULE_ID,
STATUS,
PAYMENT_TERMS,
NUMBER_OF_INSTALLMENTS,
TERMS_SEQUENCE_NUMBER,
LINE_AMOUNT,
TAX_AMOUNT ,
FREIGHT_AMOUNT,
FINANCE_CHARGES,
CURRENCY_CODE ,
AMOUNT_DUE_ORIGINAL,
AMOUNT_DUE_REMAINING,
PAYMENT_AMT ,
SERVICE_CHARGE,
DISCOUNT_AMOUNT,
RECEIPT_DATE,
RECEIPT_NUMBER,
PO_NUMBER,
SO_NUMBER,
PRINTING_OPTION ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE2,
INTERFACE_HEADER_ATTRIBUTE3,
INTERFACE_HEADER_ATTRIBUTE4,
INTERFACE_HEADER_ATTRIBUTE5,
INTERFACE_HEADER_ATTRIBUTE6,
INTERFACE_HEADER_ATTRIBUTE7,
INTERFACE_HEADER_ATTRIBUTE8,
INTERFACE_HEADER_ATTRIBUTE9,
INTERFACE_HEADER_ATTRIBUTE10,
INTERFACE_HEADER_ATTRIBUTE11,
INTERFACE_HEADER_ATTRIBUTE12,
INTERFACE_HEADER_ATTRIBUTE13,
INTERFACE_HEADER_ATTRIBUTE14,
INTERFACE_HEADER_ATTRIBUTE15,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CASH_RECEIPT_ID,
PAY_FOR_CUSTOMER_ID,
PAY_FOR_CUSTOMER_SITE_ID
FROM ar_irec_payment_list_gt
WHERE CUSTOMER_ID = p_customer_id
AND CUSTOMER_SITE_USE_ID = nvl(p_customer_site_use_id,CUSTOMER_SITE_USE_ID)
AND CURRENCY_CODE = p_currency_code;
INSERT INTO ar_irec_apply_credit_gt
(
CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
ACCOUNT_NUMBER,
CUSTOMER_TRX_ID,
TRX_NUMBER,TRX_DATE,
TRX_CLASS,
DUE_DATE,
PAYMENT_SCHEDULE_ID,
STATUS,
PAYMENT_TERMS,
NUMBER_OF_INSTALLMENTS,
TERMS_SEQUENCE_NUMBER,
LINE_AMOUNT,
TAX_AMOUNT ,
FREIGHT_AMOUNT,
FINANCE_CHARGES,
CURRENCY_CODE ,
AMOUNT_DUE_ORIGINAL,
AMOUNT_DUE_REMAINING,
PAYMENT_AMT ,
SERVICE_CHARGE,
DISCOUNT_AMOUNT,
RECEIPT_DATE,
RECEIPT_NUMBER,
PO_NUMBER,
SO_NUMBER,
PRINTING_OPTION ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE2,
INTERFACE_HEADER_ATTRIBUTE3,
INTERFACE_HEADER_ATTRIBUTE4,
INTERFACE_HEADER_ATTRIBUTE5,
INTERFACE_HEADER_ATTRIBUTE6,
INTERFACE_HEADER_ATTRIBUTE7,
INTERFACE_HEADER_ATTRIBUTE8,
INTERFACE_HEADER_ATTRIBUTE9,
INTERFACE_HEADER_ATTRIBUTE10,
INTERFACE_HEADER_ATTRIBUTE11,
INTERFACE_HEADER_ATTRIBUTE12,
INTERFACE_HEADER_ATTRIBUTE13,
INTERFACE_HEADER_ATTRIBUTE14,
INTERFACE_HEADER_ATTRIBUTE15,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
APPLICATION_AMOUNT,
CASH_RECEIPT_ID,
PAY_FOR_CUSTOMER_ID,
PAY_FOR_CUSTOMER_SITE_ID )
VALUES
(
-- In create transaction list record of arirpmtb, the actual customer id of the transaction is stored in pay_for_customer_id
-- and the customer id is the login customer id.
trx.PAY_FOR_CUSTOMER_ID,
trx.PAY_FOR_CUSTOMER_SITE_ID,
trx.ACCOUNT_NUMBER,
trx.CUSTOMER_TRX_ID,
trx.TRX_NUMBER,
trx.TRX_DATE,
trx.TRX_CLASS,
trx.DUE_DATE,
trx.PAYMENT_SCHEDULE_ID,
trx.STATUS,
trx.PAYMENT_TERMS,
trx.NUMBER_OF_INSTALLMENTS,
trx.TERMS_SEQUENCE_NUMBER,
trx.LINE_AMOUNT,
trx.TAX_AMOUNT ,
trx.FREIGHT_AMOUNT,
trx.FINANCE_CHARGES,
trx.CURRENCY_CODE ,
trx.AMOUNT_DUE_ORIGINAL,
trx.AMOUNT_DUE_REMAINING,
trx.PAYMENT_AMT ,
trx.SERVICE_CHARGE,
trx.DISCOUNT_AMOUNT,
trx.RECEIPT_DATE,
trx.RECEIPT_NUMBER,
trx.PO_NUMBER,
trx.SO_NUMBER,
trx.PRINTING_OPTION ,
trx.ATTRIBUTE_CATEGORY ,
trx.ATTRIBUTE1,
trx.ATTRIBUTE2,
trx.ATTRIBUTE3,
trx.ATTRIBUTE4,
trx.ATTRIBUTE5,
trx.ATTRIBUTE6,
trx.ATTRIBUTE7,
trx.ATTRIBUTE8,
trx.ATTRIBUTE9,
trx.ATTRIBUTE10,
trx.ATTRIBUTE11,
trx.ATTRIBUTE12,
trx.ATTRIBUTE13,
trx.ATTRIBUTE14,
trx.ATTRIBUTE15,
trx.INTERFACE_HEADER_CONTEXT,
trx.INTERFACE_HEADER_ATTRIBUTE1,
trx.INTERFACE_HEADER_ATTRIBUTE2,
trx.INTERFACE_HEADER_ATTRIBUTE3,
trx.INTERFACE_HEADER_ATTRIBUTE4,
trx.INTERFACE_HEADER_ATTRIBUTE5,
trx.INTERFACE_HEADER_ATTRIBUTE6,
trx.INTERFACE_HEADER_ATTRIBUTE7,
trx.INTERFACE_HEADER_ATTRIBUTE8,
trx.INTERFACE_HEADER_ATTRIBUTE9,
trx.INTERFACE_HEADER_ATTRIBUTE10,
trx.INTERFACE_HEADER_ATTRIBUTE11,
trx.INTERFACE_HEADER_ATTRIBUTE12,
trx.INTERFACE_HEADER_ATTRIBUTE13,
trx.INTERFACE_HEADER_ATTRIBUTE14,
trx.INTERFACE_HEADER_ATTRIBUTE15,
trx.LAST_UPDATE_DATE,
trx.LAST_UPDATED_BY,
trx.CREATION_DATE,
trx.CREATED_BY,
trx.LAST_UPDATE_LOGIN,
trx.PAYMENT_AMT,
trx.CASH_RECEIPT_ID,
trx.PAY_FOR_CUSTOMER_ID,
trx.PAY_FOR_CUSTOMER_SITE_ID
);
select class, amount_due_remaining, cash_receipt_id, ct.PAYING_CUSTOMER_ID, ct.PAYING_SITE_USE_ID, ps.CUSTOMER_ID, ps.CUSTOMER_SITE_USE_ID
into l_trx_class, l_amount_due_remaining, l_cash_receipt_id, l_pay_for_cust_id, l_pay_for_cust_site_id, l_customer_id, l_customer_site_use_id
from ar_payment_schedules ps, ra_customer_trx ct
where ps.CUSTOMER_TRX_ID = ct.CUSTOMER_TRX_ID(+)
and ps.payment_schedule_id = p_payment_schedule_id;
select -sum(app.amount_applied)
into l_amount_due_remaining
from ar_receivable_applications app
where nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status = 'UNAPP'
AND app.cash_receipt_id = l_cash_receipt_id;
INSERT INTO ar_irec_apply_credit_gt
(
CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
ACCOUNT_NUMBER,
CUSTOMER_TRX_ID,
TRX_NUMBER,TRX_DATE,
TRX_CLASS,
DUE_DATE,
PAYMENT_SCHEDULE_ID,
STATUS,
PAYMENT_TERMS,
NUMBER_OF_INSTALLMENTS,
TERMS_SEQUENCE_NUMBER,
LINE_AMOUNT,
TAX_AMOUNT ,
FREIGHT_AMOUNT,
FINANCE_CHARGES,
CURRENCY_CODE ,
AMOUNT_DUE_ORIGINAL,
AMOUNT_DUE_REMAINING,
PAYMENT_AMT ,
SERVICE_CHARGE,
DISCOUNT_AMOUNT,
RECEIPT_DATE,
PO_NUMBER,
SO_NUMBER,
PRINTING_OPTION ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE2,
INTERFACE_HEADER_ATTRIBUTE3,
INTERFACE_HEADER_ATTRIBUTE4,
INTERFACE_HEADER_ATTRIBUTE5,
INTERFACE_HEADER_ATTRIBUTE6,
INTERFACE_HEADER_ATTRIBUTE7,
INTERFACE_HEADER_ATTRIBUTE8,
INTERFACE_HEADER_ATTRIBUTE9,
INTERFACE_HEADER_ATTRIBUTE10,
INTERFACE_HEADER_ATTRIBUTE11,
INTERFACE_HEADER_ATTRIBUTE12,
INTERFACE_HEADER_ATTRIBUTE13,
INTERFACE_HEADER_ATTRIBUTE14,
INTERFACE_HEADER_ATTRIBUTE15,
APPLICATION_AMOUNT,
CASH_RECEIPT_ID,
PAY_FOR_CUSTOMER_ID,
PAY_FOR_CUSTOMER_SITE_ID)
SELECT l_customer_id,
DECODE(l_customer_site_use_id,to_number(''),-1,l_customer_site_use_id),
hca.account_number,
ps.customer_trx_id,
ps.trx_number,
ps.trx_date,
ps.class,
ps.due_date,
ps.payment_schedule_id,
ps.status,
rt.name,
ARPT_SQL_FUNC_UTIL.Get_Number_Of_Due_Dates(ps.term_id) number_of_installments,
ps.terms_sequence_number,
ps.amount_line_items_original line_amount,
ps.tax_original tax_amount,
ps.freight_original freight_amount,
ps.receivables_charges_charged finance_charge,
ps.INVOICE_CURRENCY_CODE,
ps.AMOUNT_DUE_ORIGINAL,
l_amount_due_remaining,
NULL,
0,
l_discount_amount,
sysdate,
ct.PURCHASE_ORDER,
NULL,
ct.printing_option,
ps.ATTRIBUTE_CATEGORY ,
ps.ATTRIBUTE1,
ps.ATTRIBUTE2,
ps.ATTRIBUTE3,
ps.ATTRIBUTE4,
ps.ATTRIBUTE5,
ps.ATTRIBUTE6,
ps.ATTRIBUTE7,
ps.ATTRIBUTE8,
ps.ATTRIBUTE9,
ps.ATTRIBUTE10,
ps.ATTRIBUTE11,
ps.ATTRIBUTE12,
ps.ATTRIBUTE13,
ps.ATTRIBUTE14,
ps.ATTRIBUTE15,
ct.INTERFACE_HEADER_CONTEXT,
ct.INTERFACE_HEADER_ATTRIBUTE1,
ct.INTERFACE_HEADER_ATTRIBUTE2,
ct.INTERFACE_HEADER_ATTRIBUTE3,
ct.INTERFACE_HEADER_ATTRIBUTE4,
ct.INTERFACE_HEADER_ATTRIBUTE5,
ct.INTERFACE_HEADER_ATTRIBUTE6,
ct.INTERFACE_HEADER_ATTRIBUTE7,
ct.INTERFACE_HEADER_ATTRIBUTE8,
ct.INTERFACE_HEADER_ATTRIBUTE9,
ct.INTERFACE_HEADER_ATTRIBUTE10,
ct.INTERFACE_HEADER_ATTRIBUTE11,
ct.INTERFACE_HEADER_ATTRIBUTE12,
ct.INTERFACE_HEADER_ATTRIBUTE13,
ct.INTERFACE_HEADER_ATTRIBUTE14,
ct.INTERFACE_HEADER_ATTRIBUTE15,
ARI_UTILITIES.curr_round_amt(l_amount_due_remaining - l_discount_amount,ps.INVOICE_CURRENCY_CODE),
l_cash_receipt_id,
l_pay_for_cust_id,
--Bug 4062938 - Handling of transactions with no site id
decode(l_pay_for_cust_site_id, null, -1,l_pay_for_cust_site_id) as customer_site_use_id
FROM ar_payment_schedules ps, hz_cust_accounts hca, ra_terms rt, ra_customer_trx ct
WHERE ps.payment_schedule_id = p_payment_schedule_id
AND ps.customer_id = hca.cust_account_id
AND ps.term_id = rt.term_id(+)
AND ps.customer_trx_id = ct.customer_trx_id(+);
| PUBLIC procedure delete_all_debits
|
| DESCRIPTION
| Deletes all credit transactions for the active customer, site and currency from the
| Apply Credits GT
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_customer_id IN NUMBER
| p_customer_site_use_id IN NUMBER DEFAULT NULL
| p_currency_code IN VARCHAR2
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-OCT-2004 vnb Created
+============================================================*/
PROCEDURE delete_all_debits(p_customer_id IN NUMBER,
p_customer_site_use_id IN NUMBER DEFAULT NULL,
p_currency_code IN VARCHAR2
) IS
l_procedure_name VARCHAR2(50);
l_procedure_name := '.delete_all_debits';
l_debug_info := 'Delete all debit transactions from Apply Credits GT';
DELETE FROM ar_irec_apply_credit_gt
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(p_customer_site_use_id, customer_site_use_id)
AND currency_code = p_currency_code
AND ( trx_class = 'INV' OR
trx_class = 'DM' OR
trx_class = 'CB' OR
trx_class = 'DEP'
);
END delete_all_debits;
| PUBLIC procedure delete_all_credits
|
| DESCRIPTION
| Deletes all credit transactions for the active customer, site and currency from the
| Apply Credits GT
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_customer_id IN NUMBER
| p_customer_site_use_id IN NUMBER DEFAULT NULL
| p_currency_code IN VARCHAR2
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-OCT-2004 vnb Created
+============================================================*/
PROCEDURE delete_all_credits(p_customer_id IN NUMBER,
p_customer_site_use_id IN NUMBER DEFAULT NULL,
p_currency_code IN VARCHAR2
) IS
l_procedure_name VARCHAR2(50);
l_procedure_name := '.delete_all_credits';
l_debug_info := 'Delete all credit transactions from Apply Credits GT';
DELETE FROM ar_irec_apply_credit_gt
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(p_customer_site_use_id, customer_site_use_id)
AND currency_code = p_currency_code
AND ( trx_class = 'CM' OR
trx_class = 'PMT'
);
END delete_all_credits;
| PUBLIC procedure delete_apply_credits_record
|
| DESCRIPTION
| Deletes a transaction, specified by a Payment Schedule Id, from the Apply Credits GT
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_payment_schedule_id IN NUMBER
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-OCT-2004 vnb Created
+============================================================*/
PROCEDURE delete_apply_credits_record( p_payment_schedule_id IN NUMBER
) IS
l_procedure_name VARCHAR2(50);
l_procedure_name := '.delete_apply_credits_record';
l_debug_info := 'Delete the transaction from Apply Credits GT';
DELETE FROM ar_irec_apply_credit_gt
WHERE payment_schedule_id = p_payment_schedule_id;
END delete_apply_credits_record;
| PUBLIC procedure delete_all_records
|
| DESCRIPTION
| Deletes all transactions for the active customer, site and currency from the
| Apply Credits GT
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_customer_id IN NUMBER
| p_customer_site_use_id IN NUMBER DEFAULT NULL
| p_currency_code IN VARCHAR2
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-OCT-2004 vnb Created
+============================================================*/
PROCEDURE delete_all_records(p_customer_id IN NUMBER,
p_customer_site_use_id IN NUMBER DEFAULT NULL,
p_currency_code IN VARCHAR2
) IS
l_procedure_name VARCHAR2(50);
l_procedure_name := '.delete_all_records';
l_debug_info := 'Delete all transactions from Apply Credits GT';
DELETE FROM ar_irec_apply_credit_gt;
END delete_all_records;
| Applies selected credits against selected debits
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_customer_id IN NUMBER
| p_customer_site_use_id IN NUMBER DEFAULT NULL
| p_currency_code IN VARCHAR2
| p_credit_memos_only IN VARCHAR2
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-OCT-2004 vnb Created
| 06-DEC-2004 vnb Bug 4042557 - Change sign in credit application amount
| 10-JAN-2005 vnb Bug 4105891 - Check if invoice will remain open after application
| 12-Jan-2005 vnb Bug 4050280 - Added 'ORDER BY' clause in cursors for applying credits
| 25-Jan-2005 vnb Bug 4103527 - Display error icons for duplicate application
+============================================================*/
PROCEDURE apply_credits(p_customer_id IN NUMBER,
p_customer_site_use_id IN NUMBER DEFAULT NULL,
p_driving_customer_id IN NUMBER,
p_currency_code IN VARCHAR2,
p_credit_memos_only IN VARCHAR2,
x_open_invoices_status OUT NOCOPY VARCHAR2,
x_dup_appln_dbt_psid OUT NOCOPY NUMBER,
x_dup_appln_crdt_psid OUT NOCOPY NUMBER,
x_cash_receipt_id OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_procedure_name VARCHAR2(50);
DELETE FROM ar_irec_apply_credit_gt WHERE PRINTING_OPTION <> 'Y';
| Applies selected credits and selected debits against a selected payment
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_customer_id IN NUMBER
| p_customer_site_use_id IN NUMBER DEFAULT NULL
| p_currency_code IN VARCHAR2
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 02-FEB-2005 vnb Created
+============================================================*/
PROCEDURE apply_credits_on_payment(p_currency_code IN VARCHAR2,
x_open_invoices_status OUT NOCOPY VARCHAR2,
x_dup_appln_dbt_psid OUT NOCOPY NUMBER,
x_dup_appln_crdt_psid OUT NOCOPY NUMBER,
x_cash_receipt_id OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR debit_transactions(p_currency_code VARCHAR2) IS
SELECT CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
CUSTOMER_TRX_ID,
TRX_NUMBER,
TRX_DATE,
TRX_CLASS,
DUE_DATE,
PAYMENT_SCHEDULE_ID,
STATUS,
TERMS_SEQUENCE_NUMBER,
LINE_AMOUNT,
TAX_AMOUNT ,
FREIGHT_AMOUNT,
FINANCE_CHARGES,
CURRENCY_CODE ,
AMOUNT_DUE_ORIGINAL,
AMOUNT_DUE_REMAINING,
SERVICE_CHARGE,
DISCOUNT_AMOUNT,
APPLICATION_AMOUNT,
CASH_RECEIPT_ID
FROM ar_irec_apply_credit_gt
WHERE CURRENCY_CODE = p_currency_code
AND ( TRX_CLASS = 'INV' OR
TRX_CLASS = 'DM' OR
TRX_CLASS = 'CB' OR
TRX_CLASS = 'DEP'
)
ORDER BY AMOUNT_DUE_REMAINING ASC;
SELECT CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
CUSTOMER_TRX_ID,
TRX_NUMBER,
TRX_DATE,
TRX_CLASS,
DUE_DATE,
PAYMENT_SCHEDULE_ID,
STATUS,
TERMS_SEQUENCE_NUMBER,
LINE_AMOUNT,
TAX_AMOUNT ,
FREIGHT_AMOUNT,
FINANCE_CHARGES,
CURRENCY_CODE ,
AMOUNT_DUE_ORIGINAL,
AMOUNT_DUE_REMAINING,
SERVICE_CHARGE,
DISCOUNT_AMOUNT,
APPLICATION_AMOUNT,
CASH_RECEIPT_ID
FROM ar_irec_apply_credit_gt
WHERE CURRENCY_CODE = p_currency_code
AND ( TRX_CLASS = 'CM' OR
TRX_CLASS = 'PMT'
)
ORDER BY PAYMENT_SCHEDULE_ID;
select cash_receipt_id
from ar_payment_schedules
where payment_schedule_id = p_ps_id;
select_credit_to_apply( p_currency_code => p_currency_code,
x_return_status => x_return_status,
x_credit_ps_id => x_dup_appln_crdt_psid,
x_debit_ps_id => x_dup_appln_dbt_psid
);
l_debug_info := 'Apply credits against the selected payment';
l_debug_info := 'Apply credit memo against selected payment';
l_debug_info := 'Apply payment against selected payment';
l_debug_info := 'Apply invoices against the selected payment';
l_debug_info := 'Apply invoice against selected payment';
| Applies selected credits against selected debits
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_customer_id IN NUMBER
| p_customer_site_use_id IN NUMBER DEFAULT NULL
| p_currency_code IN VARCHAR2
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 02-FEB-2005 vnb Created
+============================================================*/
PROCEDURE apply_credits_on_credit_memo(p_currency_code IN VARCHAR2,
x_open_invoices_status OUT NOCOPY VARCHAR2,
x_dup_appln_dbt_psid OUT NOCOPY NUMBER,
x_dup_appln_crdt_psid OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR debit_transactions(p_currency_code VARCHAR2) IS
SELECT CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
CUSTOMER_TRX_ID,
TRX_NUMBER,
TRX_DATE,
TRX_CLASS,
DUE_DATE,
PAYMENT_SCHEDULE_ID,
STATUS,
TERMS_SEQUENCE_NUMBER,
LINE_AMOUNT,
TAX_AMOUNT ,
FREIGHT_AMOUNT,
FINANCE_CHARGES,
CURRENCY_CODE ,
AMOUNT_DUE_ORIGINAL,
AMOUNT_DUE_REMAINING,
SERVICE_CHARGE,
DISCOUNT_AMOUNT,
APPLICATION_AMOUNT,
CASH_RECEIPT_ID
FROM ar_irec_apply_credit_gt
WHERE CURRENCY_CODE = p_currency_code
AND ( TRX_CLASS = 'INV' OR
TRX_CLASS = 'DM' OR
TRX_CLASS = 'CB' OR
TRX_CLASS = 'DEP'
)
ORDER BY AMOUNT_DUE_REMAINING ASC;
SELECT CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
CUSTOMER_TRX_ID,
TRX_NUMBER,
TRX_DATE,
TRX_CLASS,
DUE_DATE,
PAYMENT_SCHEDULE_ID,
STATUS,
TERMS_SEQUENCE_NUMBER,
LINE_AMOUNT,
TAX_AMOUNT ,
FREIGHT_AMOUNT,
FINANCE_CHARGES,
CURRENCY_CODE ,
AMOUNT_DUE_ORIGINAL,
AMOUNT_DUE_REMAINING,
SERVICE_CHARGE,
DISCOUNT_AMOUNT,
APPLICATION_AMOUNT,
CASH_RECEIPT_ID
FROM ar_irec_apply_credit_gt
WHERE CURRENCY_CODE = p_currency_code
AND ( TRX_CLASS = 'CM' OR
TRX_CLASS = 'PMT'
)
ORDER BY PAYMENT_SCHEDULE_ID;
select 'Y'
into l_found
from ar_receivable_applications rap
where rap.payment_schedule_id = credit_trx_record.payment_schedule_id
and rap.applied_payment_schedule_id = debit_trx_record.payment_schedule_id
and rap.display = 'Y'
and rap.status = 'APP';
select trunc(gl_date), trunc(trx_date)
into l_gl_date, l_receipt_date
from ar_payment_schedules
where payment_schedule_id = credit_trx_record.payment_schedule_id;
select trunc(gl_date)
into l_inv_date
from ar_payment_schedules
where payment_schedule_id = debit_trx_record.payment_schedule_id;
SELECT
CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
TRX_CLASS,
PAYMENT_SCHEDULE_ID,
CURRENCY_CODE
FROM ar_irec_apply_credit_gt
WHERE PAY_FOR_CUSTOMER_ID = p_customer_id
AND ( (PAY_FOR_CUSTOMER_SITE_ID IS NULL AND p_customer_site_use_id IS NULL) OR CUSTOMER_SITE_USE_ID = nvl(p_customer_site_use_id,CUSTOMER_SITE_USE_ID))
AND CURRENCY_CODE = p_currency_code
AND ( TRX_CLASS = 'INV' OR
TRX_CLASS = 'DM' OR
TRX_CLASS = 'CB' OR
TRX_CLASS = 'DEP'
)
AND (AMOUNT_DUE_REMAINING - (nvl(DISCOUNT_AMOUNT,0) + APPLICATION_AMOUNT) > 0);
DELETE FROM AR_IREC_PAYMENT_LIST_GT
WHERE CUSTOMER_ID = p_customer_id
AND CUSTOMER_SITE_USE_ID = nvl(p_customer_site_use_id,CUSTOMER_SITE_USE_ID)
AND CURRENCY_CODE = p_currency_code;
| PUBLIC procedure select_credit_to_apply
|
| DESCRIPTION
| Select credit to apply other transactions against
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_customer_id IN NUMBER
| p_customer_site_use_id IN NUMBER DEFAULT NULL
| p_currency_code IN VARCHAR2
| x_return_status OUT VARCHAR2 Returns 'S' if successful; 'E' if duplicate application
| 07-SEP-2005 7 rrsaneve Updated the cursors credit_to_apply ,duplicate_application as bug#6311033 fix.
+============================================================*/
PROCEDURE select_credit_to_apply( p_currency_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_credit_ps_id OUT NOCOPY NUMBER,
x_debit_ps_id OUT NOCOPY NUMBER)
IS
CURSOR credit_to_apply (p_currency_code VARCHAR2) IS
select acgt.payment_schedule_id
from ar_irec_apply_credit_gt acgt
where acgt.currency_code = p_currency_code
and acgt.trx_class = 'PMT'
and (not exists
(
select arp.payment_schedule_id
from ar_receivable_applications arp, ar_irec_apply_credit_gt acgt1
where acgt1.currency_code = p_currency_code
and arp.applied_payment_schedule_id = acgt1.payment_schedule_id
and arp.payment_schedule_id = acgt.payment_schedule_id
)
or ( (
select sum(amount_applied)
from ar_receivable_applications arp, ar_irec_apply_credit_gt acgt1
where acgt1.currency_code = p_currency_code
and arp.applied_payment_schedule_id = acgt1.payment_schedule_id
and arp.payment_schedule_id = acgt.payment_schedule_id) = 0
)
)
order by acgt.trx_date asc;
select acgt.payment_schedule_id, arp.applied_payment_schedule_id
from ar_irec_apply_credit_gt acgt,ar_receivable_applications_all arp,ar_irec_apply_credit_gt acgt1
where acgt.currency_code = p_currency_code
and acgt.trx_class = 'PMT'
and acgt.payment_schedule_id = arp.payment_schedule_id
and acgt1.currency_code = p_currency_code
and acgt1.payment_schedule_id = arp.applied_payment_schedule_id;
select acgt.payment_schedule_id, arp.applied_payment_schedule_id
from ar_irec_apply_credit_gt acgt,ar_receivable_applications_all arp,ar_irec_apply_credit_gt acgt1
where acgt.currency_code = p_currency_code
and acgt.trx_class = 'PMT'
and acgt.payment_schedule_id = arp.payment_schedule_id
and acgt1.currency_code = p_currency_code
and acgt1.payment_schedule_id = arp.applied_payment_schedule_id
and (
select sum(amount_applied)
from ar_receivable_applications arp, ar_irec_apply_credit_gt acgt1
where acgt1.currency_code = p_currency_code
and arp.applied_payment_schedule_id = acgt1.payment_schedule_id
and arp.payment_schedule_id = acgt.payment_schedule_id) > 0;
l_procedure_name := '.select_credit_to_apply';
l_debug_info := 'Open the cursor to select credit to apply';
END select_credit_to_apply;