[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT receivables_trx_id
INTO g_ccr_receivables_trx_id
FROM ar_receivables_trx
WHERE type = 'CCREFUND'
AND status = 'A';
SELECT receivables_trx_id
INTO g_nccr_receivables_trx_id
FROM ar_receivables_trx
WHERE type = 'CM_REFUND'
AND status = 'A';
SELECT
inv.customer_trx_id inv_customer_trx_id,
inv.invoice_currency_code,
inv.exchange_rate,
cmbs.receipt_handling_option,
COUNT(DISTINCT invps.payment_schedule_id) ps_count,
get_total_cm_amount(inv.customer_trx_id, cm.request_id) cm_amount,
get_total_payment_types(inv.customer_trx_id) total_pmt_types,
SUM(invps.amount_due_remaining)/
COUNT(DISTINCT NVL(adj.adjustment_id, -9.9)) inv_balance,
(SUM(NVL(invps.amount_applied, 0))+
SUM(NVL(invps.discount_taken_earned, 0)))/
COUNT(DISTINCT NVL(adj.adjustment_id, -9.9)) inv_app_amount,
NVL(SUM(DECODE(adj.adjustment_type, 'C', adj.amount, 0)), 0) /
COUNT(DISTINCT invps.payment_schedule_id) cmt_adj_amount,
NVL(SUM(DECODE(adj.adjustment_type, 'C', 0, adj.amount)), 0) /
COUNT(DISTINCT invps.payment_schedule_id) adj_amount
FROM
ra_customer_trx inv,
ar_payment_schedules invps,
ra_cust_trx_types itt,
ra_batch_sources cmbs,
ra_customer_trx cm,
ar_adjustments adj
WHERE
inv.customer_trx_id = cm.previous_customer_trx_id
AND inv.customer_trx_id = p_customer_trx_id
AND inv.customer_trx_id = invps.customer_trx_id
AND cm.batch_source_id = cmbs.batch_source_id
AND cm.request_id = arp_global.request_id
AND inv.cust_trx_type_id = itt.cust_trx_type_id
AND cmbs.receipt_handling_option IS NOT NULL
AND itt.allow_overapplication_flag = 'N'
AND inv.customer_trx_id = adj.customer_trx_id (+)
GROUP BY
cmbs.receipt_handling_option,
cm.request_id,
inv.invoice_currency_code,
inv.exchange_rate,
inv.customer_trx_id;
SELECT
inv.customer_trx_id inv_customer_trx_id,
inv.invoice_currency_code,
inv.exchange_rate,
invps.amount_due_remaining inv_balance
FROM
ra_customer_trx inv,
ar_payment_schedules invps
WHERE
invps.payment_schedule_id = p_payment_schedule_id
AND inv.customer_trx_id = invps.customer_trx_id;
select party_id
into l_party_id
from
hz_cust_accounts acc,
ra_customer_trx trx
where trx.bill_to_customer_id = acc.cust_account_id
and trx.trx_number = app_info(i).trx_number;
SELECT
ra.receivable_application_id,
ra.cash_receipt_id,
cr.amount,
cr.currency_code rec_currency_code,
inv.invoice_currency_code,
ra.applied_customer_trx_id,
ra.applied_payment_schedule_id,
inv.trx_number,
rm.payment_channel_code payment_type,
-- DECODE(p_receipt_handling_option, 'REFUND',
-- DECODE(rm.payment_channel_code,
-- 'CREDIT_CARD', 'REFUND',
-- 'ON-ACCOUNT'),
-- 'ON-ACCOUNT') rec_proc_option,
DECODE(p_receipt_handling_option, 'REFUND',
DECODE(rm.payment_channel_code,
'CREDIT_CARD', 'REFUND',
' BANK_ACCT_XFER','PAY_REFUND',
null,'PAY_REFUND',
'ON-ACCOUNT'),
'ON-ACCOUNT') rec_proc_option,
ra.amount_applied,
ra.amount_applied_from
FROM
ar_receivable_applications ra
,ar_cash_receipts cr
,ar_receipt_methods rm
,ra_customer_trx inv
WHERE
ra.applied_customer_trx_id = p_customer_trx_id
AND ra.cash_receipt_id = cr.cash_receipt_id
AND rm.receipt_method_id = cr.receipt_method_id
AND ra.display = 'Y'
AND ra.applied_customer_trx_id = inv.customer_trx_id
ORDER BY
ra.APPLY_DATE, --- This is for aging
TO_NUMBER(DECODE(p_receipt_handling_option, 'REFUND',
DECODE(rm.payment_channel_code, 'CREDIT_CARD',
2, 1) ,
ra.amount_applied)) desc,
ra.amount_applied desc;
SELECT NVL(SUM(extended_amount) , 0)
INTO l_total_cm_amount
FROM RA_CUSTOMER_TRX_LINES
WHERE previous_customer_trx_id = p_inv_customer_trx_id
AND request_id = p_request_id;
SELECT
-- count(distinct NVL(rm.payment_channel_code, 'CHECK')) ,
count(distinct NVL(rm.payment_channel_code, 'CHECK')) ,
-- sum(DECODE(rm.payment_channel_code, 'CREDIT_CARD', 1, 0))
sum(DECODE(rm.payment_channel_code, 'CREDIT_CARD', 1, 0))
INTO
l_total_payment_types,
l_total_cc_pmts
FROM AR_RECEIVABLE_APPLICATIONS ra,
ar_cash_receipts cr,
ar_receipt_methods rm
WHERE ra.applied_customer_trx_id = p_inv_customer_trx_id
AND ra.cash_receipt_id = cr.cash_receipt_id
AND cr.receipt_method_id = rm.receipt_method_id;
SELECT NVL(amount, 0), NVL(SUM(amount_applied) , 0)
INTO x_receipt_amount, x_refund_amount
FROM ar_cash_receipts cr, ar_receivable_applications ra
WHERE cr.cash_receipt_id = p_cash_receipt_id
AND cr.cash_receipt_id = ra.cash_receipt_id(+)
AND ra.applied_payment_schedule_id(+) = -6
AND ra.display(+) = 'Y'
GROUP BY amount;
SELECT NVL(amount, 0), NVL(SUM(amount_applied) , 0)
INTO x_receipt_amount, x_refund_amount
FROM ar_cash_receipts cr, ar_receivable_applications ra
WHERE cr.cash_receipt_id = p_cash_receipt_id
AND cr.cash_receipt_id = ra.cash_receipt_id(+)
AND ra.applied_payment_schedule_id(+) = -8
AND ra.display(+) = 'Y'
GROUP BY amount;
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_NOT_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 ('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 count(*)
into l_count
from ar_receivable_applications app,
ra_customer_trx oncm
where app.applied_customer_trx_id = p_customer_trx_id
and app.status = 'APP'
and app.application_type = 'CM'
and app.display = 'Y'
and app.customer_trx_id = oncm.customer_trx_id
and oncm.previous_customer_trx_id IS NULL;
select count(*)
into l_count
from ar_receivable_applications app
where app.applied_customer_trx_id = p_customer_trx_id
and app.status = 'APP'
and app.application_type = 'CASH'
and app.display = 'Y'
and app.amount_applied < 0;
select count(*)
into l_count
from ar_activity_details aad,
ra_customer_trx_lines lines
where
lines.customer_trx_id = p_customer_trx_id
and nvl(aad.CURRENT_ACTIVITY_FLAG,'Y') = 'Y'
and aad.customer_trx_line_id = lines.customer_trx_line_id;
SELECT
SUM(NVL(line_applied, 0) + NVL(line_ediscounted, 0)),
SUM(NVL(tax_applied, 0) + NVL(tax_ediscounted, 0)),
SUM(NVL(freight_applied, 0) + NVL(freight_ediscounted, 0)),
SUM(NVL(receivables_charges_applied, 0)
+ NVL(charges_ediscounted, 0)),
SUM(NVL(amount_applied, 0) + NVL(earned_discount_taken, 0))
INTO
l_line_amount,
l_tax_amount,
l_frt_amount,
l_charges_amount,
l_applied_amount
FROM
ar_receivable_applications
WHERE
applied_customer_trx_id = p_customer_trx_id
AND application_type = 'CASH' -- Consider only receipt applications
AND display = 'Y';