[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ps.customer_trx_id,
ps.payment_schedule_id,
ps.due_date maturity_date,
ps.reserved_type,
ps.reserved_value,
ps.amount_due_remaining,
ps.tax_remaining,
trh.gl_date,
trh.transaction_history_id,
trh.prv_trx_history_id,
trh.status,
trh.event,
ps.org_id
from ar_transaction_history trh, ar_payment_schedules ps
where
/*-----------------------------------------------------+
| Restrict the transaction type if given as parameter |
+-----------------------------------------------------*/
ps.cust_trx_type_id = NVL(p_cust_trx_type_id,ps.cust_trx_type_id)
and ps.class = 'BR'
and ps.reserved_type in ('REMITTANCE','ADJUSTMENT')
/*---------------------------------------------------------------------------------+
| Restrict the maturity date to be earlier than effective_date given as parameter |
+---------------------------------------------------------------------------------*/
and trunc(ps.due_date) <= trunc(NVL(p_effective_date, SYSDATE))
/*--------------------------------------------------------------------------------+
| Restrict the maturity date to be within maturity date range given as parameter |
+--------------------------------------------------------------------------------*/
and trunc(ps.due_date) between trunc(NVL(p_maturity_date_low ,ps.due_date))
and trunc(NVL(p_maturity_date_high,ps.due_date))
/*--------------------------------------------------------------------------------+
| Restrict the transaction GL date to be within GL date range given as parameter |
+--------------------------------------------------------------------------------*/
and trunc(ps.gl_date) between trunc(NVL(p_trx_gl_date_low ,ps.gl_date))
and trunc(NVL(p_trx_gl_date_high,ps.gl_date))
and ps.customer_trx_id = trh.customer_trx_id
and trh.current_record_flag = 'Y'
/*-------------------------------------------------------------------------------------------------------------------+
| Restrict the BR status depending on flags given as parameter. |
| If p_include_std_remitted_BR = 'Y' then BRs with status 'REMITTED' are included |
| If p_include_factored_BR = 'Y' then BRs with statuses 'FACTORED' and 'MATURED_PEND_RISK_ELIMINATION' are included |
| If p_include_endorsed_BR = 'Y' then BRs with status 'ENDORSED' are included |
| If all or some of the flags are 'Y' then the corresponding statuses are included |
+-------------------------------------------------------------------------------------------------------------------*/
and trh.status in (decode(NVL(p_include_std_remitted_BR,'Y'),
'Y','REMITTED',NULL),
decode(NVL(p_include_factored_BR,'Y'),
'Y','FACTORED',NULL),
decode(NVL(p_include_factored_BR,'Y'),
'Y','MATURED_PEND_RISK_ELIMINATION',NULL),
decode(NVL(p_include_endorsed_BR,'Y'),
'Y','ENDORSED',NULL)
)
FOR UPDATE OF ps.reserved_type, trh.status NOWAIT;
| If no BRs were selected, write information to the log. The processing will|
| skip the loop and exit the program. TRUE is returned as value since no |
| error occurred. |
+---------------------------------------------------------------------------*/
IF matured_cur%NOTFOUND THEN
write_debug_and_log( 'No Bills Receivable transactions matching the given criteria' );
| Process the selected BR transactions |
+--------------------------------------*/
WHILE matured_cur%FOUND LOOP
/*-----------------------------------------------------------+
| Copy values from local record to a package global record, |
| so the values can be seen form the sub procedures |
+-----------------------------------------------------------*/
pg_BR_rec := l_BR_rec;
select adj.*
from ar_adjustments adj
where adj.customer_trx_id = pg_BR_rec.customer_trx_id
and adj.status = 'W'
order by adj.adjustment_id desc;
SELECT ps.invoice_currency_code,
ps.exchange_rate_type,
decode(ps.exchange_rate_type,'User',ps.exchange_rate,null) exchange_rate,
ps.exchange_date,
ps.customer_id,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.amount_due_remaining,
ct.drawee_site_use_id,
ct.override_remit_account_flag,
ct.remit_bank_acct_use_id,
ct.customer_bank_account_id,
ct.trx_number,
ct.term_due_date maturity_date,
ct.org_id
FROM ra_customer_trx ct, ar_payment_schedules ps
WHERE ct.customer_trx_id = pg_BR_rec.customer_trx_id
AND ps.customer_trx_id = ct.customer_trx_id;
| Success update the batch id on the current cash |
| receipt history record. |
+-----------------------------------------------------*/
arp_br_remit_batches.update_br_remit_batch_to_crh(l_cr_id,pg_BR_rec.reserved_value);
| Insert the transaction history record |
+----------------------------------------*/
ARP_PROC_TRANSACTION_HISTORY.insert_transaction_history(l_trh_rec,
l_transaction_history_id);
| The GL date might be updated give the |
| adjustment record as parameter to the API |
+----------------------------------------------*/
IF l_default_gl_date IS NOT NULL THEN
l_adj_rec := p_adjustment_rec;
| Insert the transaction history record |
+----------------------------------------*/
ARP_PROC_TRANSACTION_HISTORY.insert_transaction_history(l_trh_rec,
l_transaction_history_id);
select rap.receivable_application_id, rap.cash_receipt_id, rap.gl_date, rap.apply_date,
rap.org_id
from ar_receivable_applications rap
where rap.link_to_customer_trx_id = pg_BR_rec.customer_trx_id
and rap.status = 'ACTIVITY'
and rap.applied_payment_schedule_id = -2
and rap.display = 'Y'
order by rap.receivable_application_id desc;
| Insert the transaction history record |
+----------------------------------------*/
ARP_PROC_TRANSACTION_HISTORY.insert_transaction_history(l_trh_rec,
l_transaction_history_id);
ARP_PROC_TRANSACTION_HISTORY.insert_transaction_history(l_trh_rec,
l_transaction_history_id,
p_move_deferred_tax);
select th.*
from ar_transaction_history th
where (postable_flag = 'Y' or event = 'MATURITY_DATE')
connect by prior prv_trx_history_id = transaction_history_id
start with transaction_history_id = p_transaction_history_id
order by transaction_history_id desc;
SELECT NVL(rma.br_collection_days,0) collection_days,
NVL(rma.risk_elimination_days,0) risk_elimination_days,
rm.receipt_inherit_inv_num_flag,
ab.receipt_method_id,
ab.remit_bank_acct_use_id,
ab.batch_date
FROM ar_batches ab, ar_receipt_method_accounts rma, ar_receipt_methods rm
WHERE ab.batch_id = p_batch_id
and rma.remit_bank_acct_use_id = ab.remit_bank_acct_use_id
and rma.receipt_method_id = ab.receipt_method_id
and rm.receipt_method_id = ab.receipt_method_id;
SELECT NVL(rt.risk_elimination_days,0) risk_elimination_days
FROM ar_receivables_trx rt
WHERE rt.receivables_trx_id = p_receivables_trx_id;