[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Dummy constants for use in update and lock operations |
+--------------------------------------------------------*/
AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
PROCEDURE insert_BR_ps(
p_trh_rec IN ar_transaction_history%ROWTYPE,
p_ps_id OUT NOCOPY ar_payment_schedules.payment_schedule_id%TYPE);
| insert_transaction_history |
| |
| DESCRIPTION |
| Inserts a record into ar_transaction_history for bills receivable |
| transaction. If this is not the first history record the previous |
| record flags are updated. If this is the completion record |
| payment schedule is created for the BR. MRC information is created |
| if MRC functionality is enabled |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: p_trh_rec |
| OUT: p_transaction_history_id |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| MODIFICATION HISTORY |
| 28-MAR-2000 Jani Rautiainen Created |
| |
+===========================================================================*/
PROCEDURE insert_transaction_history(p_trh_rec IN OUT NOCOPY ar_transaction_history%rowtype,
p_transaction_history_id OUT NOCOPY ar_transaction_history.transaction_history_id%type,
p_move_deferred_tax IN VARCHAR2 DEFAULT 'N') IS
l_ae_doc_rec ae_doc_rec_type;
cursor update_event (p_trx_id ra_customer_trx.customer_trx_id%TYPE)
is
select distinct trh.event_id
from ar_transaction_history_all trh
where trh.customer_trx_id = p_trx_id and
trh.event_id is not null and /* This condition is to make sure that null events are not selected for update. Null events get inserted in TRH when fields like maturity date are updated */
not exists
( select 'Y'
from ar_transaction_history_all trh_sub
where trh_sub.customer_trx_id = p_trx_id and
trh_sub.postable_flag ='Y' and
trh_sub.event_id = trh.event_id
);
arp_util.debug( 'arp_proc_transaction_history.insert_transaction_history()+');
| If this is not the first history record, update |
| the previous records current flags |
+--------------------------------------------------*/
IF l_old_trh_rec.transaction_history_id IS NOT NULL THEN
/*-----------------------------------------------------------------+
| set the prv_trx_history_id to point to the previous record |
+-----------------------------------------------------------------*/
p_trh_rec.prv_trx_history_id := l_old_trh_rec.transaction_history_id;
| If the new record created is postable update the |
| current_accounted_flag of previous record to 'N' |
+--------------------------------------------------*/
IF NVL(p_trh_rec.postable_flag,'N') = 'Y' THEN
/*--------------------------------------------------------------------+
| Check whether the previous record was the current accounted record |
+--------------------------------------------------------------------*/
IF NVL(l_old_trh_rec.current_accounted_flag,'N') = 'N' THEN
/*--------------------------------------------------------------------------+
| Previous record was not the accounted record, so find the correct record |
+--------------------------------------------------------------------------*/
l_prev_acctd_id := find_prev_accounted_id(l_old_trh_rec.customer_trx_id);
| Update previous accounted history record |
| if one exists. If one does not exist this|
| we are inserting the first accounted row |
+------------------------------------------*/
IF l_prev_acctd_id is not null THEN
/*-----------------------------------------+
| Initialize the record with dummy values |
+-----------------------------------------*/
ARP_TRANSACTION_HISTORY_PKG.set_to_dummy(l_old_acctd_trh_rec);
| Set the flag to be updated |
+----------------------------*/
l_old_acctd_trh_rec.current_accounted_flag := 'N';
| Update the previous accounted record |
+--------------------------------------*/
ARP_PROC_TRANSACTION_HISTORY.update_transaction_history(l_old_acctd_trh_rec,
l_prev_acctd_id);
| Previous record was the accounted record, so set the flag to update it |
+------------------------------------------------------------------------*/
l_old_trh_rec.current_accounted_flag := 'N';
| Update the current record indicator of the previous record |
+------------------------------------------------------------*/
l_old_trh_rec.current_record_flag := 'N';
| Update the previous record |
+-----------------------------*/
ARP_PROC_TRANSACTION_HISTORY.update_transaction_history(l_old_trh_rec,
l_old_trh_rec.transaction_history_id);
arp_proc_transaction_history.insert_BR_ps(p_trh_rec,l_ps_id);
arp_transaction_history_pkg.insert_p(p_trh_rec, p_transaction_history_id);
| Need to call ARP XLA to create or update |
| the life cycle of a TH |
| This routine is called by all the TH WB |
| And it is a central place for TH lifecycle|
+--------------------------------------------*/
arp_standard.debug('p_transaction_history_id :'||p_transaction_history_id);
| If the Bills was incompleted we need to delete |
| the accounting for previous PENDING_REMITTANCE |
| record since This scenario is only possible if |
| the BR does not have any activities or reclassifications |
| on it. The validation is done in the calling |
| functionality |
+----------------------------------------------------------*/
/*----------------------------------------------------------+
| Since the previous history is not necessarily postable |
| accounting needs to be reversed on the previout accounted|
| transaction history record |
+----------------------------------------------------------*/
l_prev_acctd_id := find_prev_accounted_id(l_old_trh_rec.customer_trx_id);
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
| If the Bills was incompleted we need to update also the |
| the other flags from the previous PENDING_REMITTANCE |
| record since the accounting engine will delete the |
| accounting rows for it. This scenario is only possible if|
| the BR does not have any activities or reclassifications |
| on it. The validation is done in the calling |
| functionality |
+----------------------------------------------------------*/
/*-----------------------------------------+
| Initialize the record with dummy values |
+-----------------------------------------*/
ARP_TRANSACTION_HISTORY_PKG.set_to_dummy(l_old_acctd_trh_rec);
| Set the flag to be updated |
+----------------------------*/
l_old_acctd_trh_rec.current_accounted_flag := 'N';
| Update the previous accounted record |
+--------------------------------------*/
ARP_PROC_TRANSACTION_HISTORY.update_transaction_history(l_old_acctd_trh_rec,
l_prev_acctd_id);
/* Identify and update the events only when the status changes from INCOMPLETE TO PENDING_REMITTANCE */
IF (p_trh_rec.status = 'PENDING_REMITTANCE' AND l_old_trh_rec.status = 'INCOMPLETE') THEN
select count(distinct trh.event_id)
into l_event_count
from ar_transaction_history_all trh
where trh.customer_trx_id = p_trh_rec.customer_trx_id and
not exists
( select 'Y'
from ar_transaction_history_all trh_sub
where trh_sub.customer_trx_id = p_trh_rec.customer_trx_id and
trh_sub.postable_flag ='Y' and
trh_sub.event_id = trh.event_id
);
/* Values selected to populate the IN parameters of the xla procedure */
select xet.legal_entity_id legal_entity_id,
trx.SET_OF_BOOKS_ID set_of_books_id,
xet.entity_code entity_code
into
l_event_source_info.legal_entity_id,
l_event_source_info.ledger_id,
l_event_source_info.entity_type_code
from
ra_customer_trx trx ,
xla_transaction_entities_upg xet
where trx.customer_trx_id = p_trh_rec.customer_trx_id
and trx.customer_trx_id = xet.source_id_int_1
and xet.entity_code ='BILLS_RECEIVABLE'
AND xet.application_id = 222
AND trx.SET_OF_BOOKS_ID = xet.LEDGER_ID;
/* Open the cursor containing the events to be updated and call the xla procedure for every event */
open update_event (p_trh_rec.customer_trx_id);
fetch update_event into l_event_id;
exit when update_event%NOTFOUND;
xla_events_pub_pkg.update_event
( p_event_source_info => l_event_source_info,
p_event_id => l_event_id,
p_event_status_code => 'N',
p_valuation_method => null,
p_security_context => l_security );
arp_util.debug( 'arp_proc_transaction_history.insert_transaction_history()-');
arp_util.debug( 'EXCEPTION: arp_proc_transaction_history.insert_transaction_history()');
END insert_transaction_history;
| update_transaction_history |
| |
| DESCRIPTION |
| Updates a record into ar_transaction_history for bills receivable |
| transaction. MRC information is updated if MRC functionality is enabled|
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: p_trx_rec |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-MAR-2000 Jani Rautiainen Created |
| |
+===========================================================================*/
PROCEDURE update_transaction_history(p_trh_rec IN OUT NOCOPY ar_transaction_history%rowtype,
p_transaction_history_id IN ar_transaction_history.transaction_history_id%TYPE) IS
CURSOR trh_customer_trx_cur IS
SELECT customer_trx_id
FROM ar_transaction_history
WHERE transaction_history_id = p_transaction_history_id;
arp_util.debug( 'arp_proc_transaction_history.update_transaction_history()+');
arp_transaction_history_pkg.update_p(p_trh_rec, p_transaction_history_id);
| None of MRC columns can be updated |
+-------------------------------------*/
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug( 'arp_proc_transaction_history.update_transaction_history()-');
arp_util.debug( 'EXCEPTION: arp_proc_transaction_history.update_transaction_history()');
END update_transaction_history;
| delete_transaction_history |
| |
| DESCRIPTION |
| Deletes row from ar_transaction_history for Bills Receivable |
| Transaction. MRC information is deleted if MRC functionality is enabled|
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: p_transaction_history_id |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-MAR-2000 Jani Rautiainen Created |
| |
+===========================================================================*/
PROCEDURE delete_transaction_history(p_transaction_history_id IN ar_transaction_history.transaction_history_id%TYPE) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug( 'arp_proc_transaction_history.delete_transaction_history()+');
| delete the history record |
+-------------------------------*/
arp_transaction_history_pkg.delete_p(p_trh_id => p_transaction_history_id);
| Delete all accounting related to the history row |
+---------------------------------------------------*/
arp_proc_transaction_history.delete_transaction_hist_dist(p_transaction_history_id);
| Call MRC logic to delete MRC rows |
+------------------------------------------*/
--{BUG#4301323
-- ARP_PROC_TRANSACTION_HISTORY.delete_mrc_transaction_hist(p_transaction_history_id);
arp_xla_events.delete_event( p_document_id => p_transaction_history_id,
p_doc_table => 'TRH');
arp_util.debug( 'arp_proc_transaction_history.delete_transaction_history()-');
arp_util.debug( 'EXCEPTION: arp_proc_transaction_history.delete_transaction_history()');
END delete_transaction_history;
| delete_transaction_history |
| |
| DESCRIPTION |
| Deletes distribution rows from ar_distributions for given transaction |
| history record. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: p_transaction_history_id |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-MAR-2000 Jani Rautiainen Created |
| 10-MAY-2000 Debbie Jancis Added call to delete from |
| ar_distributions after call to delete |
| using table handler since that will |
| not be modified to handle MRC ar |
| distributions deletes. Added call to |
| ar_mrc_engine for processing. |
+===========================================================================*/
PROCEDURE delete_transaction_hist_dist(p_transaction_history_id IN ar_transaction_history.transaction_history_id%TYPE) IS
/*----------------------------------------+
| Cursor for distribution records |
| related to the transaction history |
+----------------------------------------*/
/* Start FP Bug 5741803 chng the defination to check for source table */
CURSOR distribution_cur IS
select dist.line_id
from ar_distributions dist
where source_id = p_transaction_history_id
and source_table = 'TH';
arp_util.debug( 'arp_proc_transaction_history.delete_transaction_hist_dist()+');
| Delete distribution record |
+-----------------------------*/
arp_distributions_pkg.delete_p(distribution_rec.line_id);
arp_util.debug( 'arp_proc_transaction_history.delete_transaction_hist_dist()-');
arp_util.debug( 'EXCEPTION: arp_proc_transaction_history.delete_transaction_hist_dist()');
END delete_transaction_hist_dist;
select transaction_history_id
from ar_transaction_history
where current_accounted_flag = 'Y'
and customer_trx_id = p_customer_trx_id;
| insert_BR_ps |
| |
| DESCRIPTION |
| Inserts an payment schedule for Bills Receivable |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: p_trh_rec - Record containing the transaction history |
| recor being created. |
| OUT: p_ps_id - PS id of the record created |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-MAR-2000 Jani Rautiainen Created |
| 25-MAY-2005 V Crisostomo SSA-R12 : add org_id |
| |
+===========================================================================*/
PROCEDURE insert_BR_ps(
p_trh_rec IN ar_transaction_history%ROWTYPE,
p_ps_id OUT NOCOPY ar_payment_schedules.payment_schedule_id%TYPE) IS
l_trx_rec ra_customer_trx%ROWTYPE;
arp_standard.debug('arp_proc_transaction_history.insert_BR_ps()+');
| insert record into payment schedule table |
+--------------------------------------------*/
arp_ps_pkg.insert_p(l_ps_rec, l_ps_id);
arp_standard.debug('arp_proc_transaction_history.insert_BR_ps()-');
arp_standard.debug('EXCEPTION: arp_proc_transaction_history.insert_BR_ps()');
END insert_BR_ps;
SELECT sum(nvl(amount,0)) total_amount,
sum(nvl(acctd_amount,0)) total_acctd_amount,
sum(nvl(line_adjusted,0)) total_line,
sum(nvl(freight_adjusted,0)) total_freight,
sum(nvl(tax_adjusted,0)) total_tax,
sum(nvl(receivables_charges_adjusted,0)) total_charges
FROM ra_customer_trx_lines ctl,
ar_adjustments adj
WHERE ctl.customer_trx_id = p_customer_trx_id
AND adj.adjustment_id = ctl.br_adjustment_id;
SELECT 'exists'
FROM ar_transaction_history
WHERE customer_trx_id = p_customer_trx_id
AND current_record_flag = 'Y';
| Fetch the updated image of the payment schedule |
+--------------------------------------------------*/
arp_ps_pkg.fetch_p( p_old_ps_rec.payment_schedule_id, l_new_ps_rec );
| Cannot insert NULL into trx_date |
+------------------------------------*/
IF l_trh_rec.trx_date IS NULL THEN
l_trh_rec.trx_date := SYSDATE;
ARP_PROC_TRANSACTION_HISTORY.insert_transaction_history(l_trh_rec, l_transaction_history_id);