[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y', arp_standard.fnd_message('AR_RID_NOT_REMITTED_OR_CLEARED')
INTO x_rec_in_doubt, x_rid_reason
FROM dual
WHERE
(
NOT EXISTS
(
SELECT 1
FROM AR_CASH_RECEIPT_HISTORY crh
WHERE crh.cash_receipt_id = p_cash_receipt_id
AND crh.status IN ('REMITTED', 'CLEARED')
)
);
SELECT 'Y', arp_standard.fnd_message('AR_RID_CLAIM_OR_CB_APP_EXISTS')
INTO x_rec_in_doubt, x_rid_reason
FROM dual
WHERE
EXISTS
(
SELECT 1
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND applied_payment_schedule_id IN (-4, -5)
AND display = 'Y'
);
SELECT 'Y', arp_standard.fnd_message('AR_RID_RECEIPT_REVERSED')
INTO x_rec_in_doubt, x_rid_reason
FROM dual
WHERE
EXISTS
(
SELECT 1
FROM ar_cash_receipts cr1
WHERE cr1.cash_receipt_id = p_cash_receipt_id
AND cr1.reversal_date is not null
);
SELECT ra.cash_receipt_id,
SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
-7,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) prepayment_amount
FROM ar_receivable_applications ra , ar_cash_receipts cr
WHERE ra.payment_set_id= c_payment_set_id
AND cr.cash_receipt_id = ra.cash_receipt_id
AND ra.display = 'Y'
AND decode(c_receipt_method_id, null,'1',cr.receipt_method_id ) =
nvl(c_receipt_method_id,'1')
AND decode(c_bank_account_id,null,1,cr.customer_bank_account_id ) =
nvl(c_bank_account_id,1)
GROUP by ra.cash_receipt_id
order by prepayment_amount desc;
select nvl(rm.payment_channel_code, 'CHECK')
into l_payment_type
from ar_receipt_methods rm , ar_cash_receipts cr
WHERE cr.cash_receipt_id = l_cash_receipt_id
AND cr.receipt_method_id = rm.receipt_method_id;
SELECT
SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
-7,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) max_refund_amt
into l_max_refund_amt
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = l_cash_receipt_id
AND ra.display = 'Y';
select count(*) into l_dummy
from ar_cash_receipts_all cr, ar_receivable_applications ra
where ra.payment_set_id= l_payment_set_id
AND cr.cash_receipt_id = ra.cash_receipt_id;
select count(*) into l_dummy
from ar_cash_receipts_all cr, ar_receivable_applications ra
where ra.payment_set_id= l_payment_set_id
AND cr.cash_receipt_id = ra.cash_receipt_id
and cr.customer_bank_account_id = t_bank_account_id;
select count(*) into l_dummy
from ar_cash_receipts_all cr, ar_receivable_applications ra
where ra.payment_set_id= l_payment_set_id
AND cr.cash_receipt_id = ra.cash_receipt_id
and cr.receipt_method_id = t_receipt_method_id;
SELECT
SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
-7,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) max_refund_amt
into l_max_refund_amt
FROM ar_receivable_applications ra , ar_cash_receipts cr
WHERE ra.payment_set_id= l_payment_set_id
AND cr.cash_receipt_id = ra.cash_receipt_id
AND ra.display = 'Y'
AND decode(t_receipt_method_id, null,'1',cr.receipt_method_id ) =
nvl(t_receipt_method_id,'1')
AND decode(t_bank_account_id,null,1,cr.customer_bank_account_id ) =
nvl(t_bank_account_id,1);
SELECT count(distinct NVL(rm.payment_channel_code, 'CHECK'))
as pmt_type_count,
sum(DECODE(rm.payment_channel_code, 'CREDIT_CARD', 1, 0))
as credit_pmt_type_count
INTO l_total_pmt_types, l_credit_pmt_type_count
FROM ar_receivable_applications ra,
ar_cash_receipts cr,
ar_receipt_methods rm
WHERE ra.payment_set_id = l_payment_set_id
AND ra.cash_receipt_id = cr.cash_receipt_id
AND cr.receipt_method_id = rm.receipt_method_id
AND decode(t_bank_account_id,null,1,cr.customer_bank_account_id ) =
nvl(t_bank_account_id,1)
AND decode(t_receipt_method_id,null,1,cr.receipt_method_id ) =
nvl(t_receipt_method_id,1);
SELECT distinct ctl.payment_set_id
FROM ra_customer_trx_lines ctl
WHERE ctl.payment_set_id is not null
AND ctl.customer_trx_id= c_customer_trx_id;
SELECT *
FROM ar_receivable_applications
WHERE display ='Y'
AND applied_payment_schedule_id = -7
AND payment_set_id = c_payment_set_id
order by amount_applied;
SELECT 'Y'
INTO l_prepayment_exist_flag
FROM ar_payment_schedules ps,
ra_customer_trx ct
WHERE ps.customer_trx_id =ct.customer_trx_id
AND NVL(ct.prepayment_flag,'N') = 'Y'
AND ps.payment_schedule_id=l_payment_schedule_id;
select sequence_num as installment_number,
arp_util.CurrRound( (relative_amount/base_amount ) * l_amount ,
l_currency_code) as installment_amount
from ra_terms t , ra_terms_lines tl
where t.term_id = tl.term_id
and t.term_id = l_term_id;
SELECT 1 into l_dummy
FROM RA_TERMS_B
WHERE term_id = p_term_id;
SELECT 1 into l_dummy
FROM fnd_currencies
WHERE currency_code = p_currency_code;
| rule_select_prepayments |
| |
| DESCRIPTION |
| Subscription to the business event AutoInvoice
| |
| PARAMETERS |
| |
| MODIFIES |
| |
| RETURNS |
|
| |
+-------------------------------------------------------------------------*/
FUNCTION rule_select_prepayments(
p_subscription_guid in raw,
p_event in out NOCOPY wf_event_t)RETURN VARCHAR2 IS
l_request_id NUMBER := null;
SAVEPOINT Select_Prepay_Event;
arp_util.debug('The rule_select_prepayments Subscription to AutoInvoice ''');
ROLLBACK TO Select_Prepay_Event;
WF_CORE.CONTEXT('AR_PREPAYMENTS', 'RULE_SELECT_PREPAYMENTS', p_event.getEventName(), p_subscription_guid);
END rule_select_prepayments;
| PUBLIC Procedure Select_Prepayments
|
| DESCRIPTION
| Called from Concurrent program 'Prepayments Matching Program' to
| match the prepaid receipts to their invoices
| -----------------------------------------------------------------
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_batch_source : 'All Invoices' for matching all the invoices
| 'AutoInvoice Batch' for matching invoices in a
| particular AutoInvoice Batch
| p_request_id : Populated only if p_batch_source = 'AutoInvoice Batch'
| Request ID for the AutoInvoice Batch
|
|
| RETURNS
| nothing
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 10-JUL-2003 Jyoti Pandey o Created
|
*=======================================================================*/
PROCEDURE Select_Prepayments ( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_batch_source IN VARCHAR2,
p_request_id IN NUMBER )
IS
l_retcode NUMBER := 0;
SELECT
ps.payment_schedule_id,
ps.amount_due_remaining
FROM
ra_customer_trx ct,
ar_payment_schedules ps
WHERE ps.status = 'OP'
AND ps.amount_due_remaining > 0
AND ps.gl_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD')
--- Bug : 917451 in order to force the use of AR_PAYMENT_SCHEDULES_N9 --
AND ps.selected_for_receipt_batch_id IS NULL
---AND ps.due_date +0 <= TO_DATE(SYSDATE) + TO_NUMBER(rm.lead_days)
AND nvl(ct.prepayment_flag, 'N') = 'Y'
AND ps.customer_trx_id = ct.customer_trx_id
AND decode(p_batch_src,'All Invoices', '1', ct.request_id) =
decode(p_batch_src,'All Invoices', '1' , p_req_id)
ORDER BY ps.due_date;
FND_MSG_PUB.Delete_Msg;
END select_prepayments;