[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| validate_insert_header |
| |
| DESCRIPTION |
| Validates row that is going to be inserted into ra_customer_trx. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: l_trx_rec |
| OUT: l_status |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 13-JUL-95 Martin Johnson Created |
| 13-MAY-99 Srihari Koukuntla Modified for BugNo :860294 |
| added to i/p parameters l_trx_rec and l_status |
| to validate complete_flag |
| |
+===========================================================================*/
PROCEDURE validate_insert_header( l_trx_rec IN ra_customer_trx%rowtype,
l_status OUT NOCOPY varchar2 ) IS
BEGIN
arp_util.debug('arp_process_header.validate_insert_header()+');
arp_util.debug('arp_process_header.validate_insert_header()-');
arp_util.debug('EXCEPTION: arp_process_header.validate_insert_header()');
| validate_update_header |
| |
| DESCRIPTION |
| Validates row that is going to be updated in ra_customer_trx. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 17-JUL-95 Martin Johnson Created |
| |
+===========================================================================*/
PROCEDURE validate_update_header IS
BEGIN
arp_util.debug('arp_process_header.validate_update_header()+');
arp_util.debug('arp_process_header.validate_update_header()-');
arp_util.debug('EXCEPTION: arp_process_header.validate_update_header()');
| validate_delete_header |
| |
| DESCRIPTION |
| Validates row that is going to be delete from ra_customer_trx. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 26-JUL-95 Martin Johnson Created |
| |
+===========================================================================*/
PROCEDURE validate_delete_header IS
BEGIN
arp_util.debug('arp_process_header.validate_delete_header()+');
arp_util.debug('arp_process_header.validate_delete_header()-');
arp_util.debug('EXCEPTION: arp_process_header.validate_delete_header()');
| for SELECT that fetches gl_date
+===========================================================================*/
PROCEDURE set_flags(p_customer_trx_id IN
ra_customer_trx.customer_trx_id%type,
p_new_trx_rec IN ra_customer_trx%rowtype,
p_new_gl_date IN
ra_cust_trx_line_gl_dist.gl_date%type,
p_new_open_rec_flag IN
ra_cust_trx_types.accounting_affect_flag%type,
p_ps_dispute_amount IN
ar_payment_schedules.amount_in_dispute%type,
pd_dispute_date IN DATE,
p_ex_rate_changed_flag OUT NOCOPY boolean,
p_commitment_changed_flag OUT NOCOPY boolean,
p_gl_date_changed_flag OUT NOCOPY boolean,
p_complete_changed_flag OUT NOCOPY boolean,
p_open_rec_changed_flag OUT NOCOPY boolean,
p_dispute_changed_flag OUT NOCOPY boolean,
p_number_of_payment_schedules OUT NOCOPY NUMBER,
p_old_trx_rec OUT NOCOPY ra_customer_trx%rowtype,
p_cust_trx_type_changed_flag OUT NOCOPY boolean)
IS
l_old_trx_rec ra_customer_trx%rowtype;
select gl_date
into l_old_gl_date
from ra_cust_trx_line_gl_dist
where customer_trx_id = p_customer_trx_id
and account_class = 'REC'
and latest_rec_flag = 'Y';
select accounting_affect_flag
into l_old_open_rec_flag
from ra_cust_trx_types
where cust_trx_type_id = l_old_trx_rec.cust_trx_type_id;
SELECT SUM( NVL(ps.amount_in_dispute,0) ),
COUNT(*)
, MAX(ps.dispute_date)
INTO l_old_dispute_amount,
p_number_of_payment_schedules
, ld_old_dispute_date
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id;
SELECT distinct gld.event_id event_id
FROM ra_cust_trx_line_gl_dist gld, ra_customer_trx ra
WHERE gld.customer_trx_id = p_customer_trx_id
and gld.customer_trx_id = ra.customer_trx_id
and ra.invoicing_rule_id is NULL
and gld.gl_date <> p_gl_date
and gld.account_class = 'REC'
and gld.posting_control_id = -3
and gld.account_set_flag = 'N'
AND gld.event_id is not null
AND exists
(Select 1 from xla_events
where entity_id in (
Select entity_id from xla_transaction_entities
where entity_code = 'TRANSACTIONS'
and nvl(source_id_int_1 , -99) = ra.customer_trx_id
and ledger_id = ra.set_of_books_id
and application_id = 222 ));
-- Delete the existing event_id that has been latched to the GLD rows:
BEGIN
arp_util.debug('header_rerun_a: Deleting existing events');
xla_events_pub_pkg.delete_event
( p_event_source_info => l_event_source_info,
p_event_id => l_event_id,
p_valuation_method => NULL,
p_security_context => l_security);
arp_util.debug('EXCEPTION: header_rerun_aa : delete events'||SQLERRM);
arp_ctl_pkg.update_f_ct_id( l_line_rec,
p_customer_trx_id,
'LINE');
'no child lines to update.');
ARP_XLA_EVENTS.delete_reverse_revrec_event( p_document_id => p_customer_trx_id,
p_doc_table => 'CT');
arp_ctlgd_pkg.delete_f_ct_id(p_customer_trx_id,
'N',
NULL);
'no dists to delete.');
arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
p_customer_trx_id,
'Y',
'REC');
'no dists to update.');
| insert_header |
| |
| DESCRIPTION |
| Inserts a record into ra_customer_trx. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_form_name |
| p_form_version |
| p_trx_rec |
| p_trx_class |
| p_gl_date |
| p_term_in_use_flag |
| p_commitment_rec |
| OUT: |
| p_trx_number |
| p_customer_trx_id |
| p_status |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 11-JUL-95 Martin Johnson Created |
| |
+===========================================================================*/
PROCEDURE insert_header(
p_form_name IN varchar2,
p_form_version IN number,
p_trx_rec IN ra_customer_trx%rowtype,
p_trx_class IN ra_cust_trx_types.type%type,
p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
p_term_in_use_flag IN varchar2,
p_commitment_rec IN arp_process_commitment.commitment_rec_type,
p_trx_number OUT NOCOPY ra_customer_trx.trx_number%type,
p_customer_trx_id OUT NOCOPY ra_customer_trx.customer_trx_id%type,
p_customer_trx_line_id OUT NOCOPY ra_customer_trx_lines.customer_trx_line_id%type,
p_row_id OUT NOCOPY rowid,
p_status OUT NOCOPY varchar2,
p_receivable_ccid IN gl_code_combinations.code_combination_id%type
DEFAULT NULL,
p_run_autoacc_flag IN varchar2 DEFAULT 'Y',
p_create_default_sc_flag IN varchar2 DEFAULT 'Y' )
IS
l_customer_trx_id ra_customer_trx.customer_trx_id%type;
arp_util.debug('arp_process_header.insert_header()+');
validate_insert_header(p_trx_rec,p_status);
arp_util.debug('Failed in validate insert header');
| pre-insert logic |
+--------------------*/
/*---------------------------------------------------------------------+
| IF the remit to address is null |
| AND the transaction is not a credit memo |
| THEN try to derive the remit to address from the bill to address |
| or from the default remit to address. |
| |
| If no remit to address can be derived, the procedure raises a |
| NO_DATA_FOUND error. Ignore this error. |
+---------------------------------------------------------------------*/
IF ( l_trx_rec.remit_to_address_id IS NULL ) AND
( p_trx_class <> 'CM' )
THEN
BEGIN
arp_trx_defaults_3.get_remit_to_address(
null,
null,
null,
null,
l_trx_rec.bill_to_site_use_id,
l_trx_rec.remit_to_address_id,
l_remit_to_address_rec
);
SELECT NVL( default_printing_option, 'PRI' )
INTO l_trx_rec.printing_option
FROM ra_cust_trx_types
WHERE cust_trx_type_id = l_trx_rec.cust_trx_type_id;
THEN arp_process_commitment.header_pre_insert;
arp_ct_pkg.insert_p(l_trx_rec, p_trx_number, l_customer_trx_id);
| post-insert logic |
+---------------------*/
IF p_trx_class in ('DEP', 'GUAR')
THEN arp_process_commitment.header_post_insert(
l_customer_trx_id,
p_commitment_rec,
l_trx_rec.primary_salesrep_id,
p_gl_date,
p_customer_trx_line_id,
l_status1 );
ELSE arp_process_invoice.header_post_insert(
l_trx_rec.primary_salesrep_id,
l_customer_trx_id,
p_create_default_sc_flag);
arp_util.debug('EXCEPTION: Insert_Header');
arp_util.debug('arp_process_header.insert_header()-');
arp_util.debug('EXCEPTION: arp_process_header.insert_header()');
| update_header |
| |
| DESCRIPTION |
| Updates a record into ra_customer_trx. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 17-JUL-95 Martin Johnson Created |
| 29-NOV-95 Nigel Smith Added calls to Tax Engine. |
| 10/10/1996 Harri Kaukovuo Added parameter pd_dispute_date
| to set_flags().
| Fixed bug when updating dispute amount
| would cause the whole ar_payment_schedules
| to be updated.
+===========================================================================*/
PROCEDURE update_header(
p_form_name IN varchar2,
p_form_version IN number,
p_trx_rec IN OUT NOCOPY ra_customer_trx%rowtype,
p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
p_trx_amount IN number,
p_trx_class IN ra_cust_trx_types.type%type,
p_gl_date IN ra_cust_trx_line_gl_dist.gl_date%type,
p_initial_customer_trx_line_id IN
ra_customer_trx_lines.initial_customer_trx_line_id%type
default null,
p_commitment_rec IN arp_process_commitment.commitment_rec_type,
p_open_rec_flag IN ra_cust_trx_types.accounting_affect_flag%type,
p_term_in_use_flag IN varchar2,
p_recalc_tax_flag IN boolean,
p_rerun_autoacc_flag IN boolean,
p_ps_dispute_amount IN NUMBER DEFAULT NULL,
p_ps_dispute_date IN DATE DEFAULT NULL,
p_status OUT NOCOPY varchar2)
IS
l_rerun_autoacc_flag boolean; /* Bug-3454082 - 4019170 */
SELECT 'Y'
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_customer_trx_id
AND posting_control_id <> -3
AND account_set_flag = 'N';
arp_util.debug('arp_process_header.update_header()+');
validate_update_header;
arp_util.debug('EXCEPTION: arp_trx_util.delete_lock()');
| pre-update logic |
+--------------------*/
-- If tax user exit is going to be called in update mode (if
-- recalculate_tax_flag = 'Y'), call salestax delete. The Tax
-- Vendor Code will then be able to "backout" these old tax rows.
-- And, the subsequent call to AR SALESTAX UPDATE can be relied
-- upon to just recalculate the new data. This allows the Tax
-- Vendor Code to keep an audit trail.
l_recalc_tax := p_recalc_tax_flag;
THEN arp_process_commitment.header_pre_update;
arp_ct_pkg.update_p(p_trx_rec, p_customer_trx_id);
| Update the dispute amounts on the payment schedules if |
| the dispute amount has changed. |
|
| 10/10/1996 Harri Kaukovuo Bug fix 411031.
+---------------------------------------------------------*/
IF ( l_dispute_changed_flag = TRUE )
THEN
DECLARE
/*Adding cursor as part of bug fix 5129946*/
CURSOR get_existing_ps (p_ctrx_id IN NUMBER) IS
SELECT payment_schedule_id,
amount_in_dispute,
amount_due_remaining,
dispute_date
FROM ar_payment_schedules
WHERE customer_trx_id = p_ctrx_id;
l_last_update_login NUMBER := arp_standard.profile.last_update_login;
l_last_update_login);
UPDATE ar_payment_schedules ps
SET ps.amount_in_dispute = DECODE(p_ps_dispute_amount,
NULL, ps.amount_in_dispute,
0, 0,
DECODE(l_number_of_pay_scheds,
1, p_ps_dispute_amount,
ps.amount_due_remaining)),
ps.dispute_date = p_ps_dispute_date
WHERE ps.customer_trx_id = p_customer_trx_id;
| post-update logic |
+---------------------*/
IF p_trx_rec.exchange_rate = pg_number_dummy
THEN l_exchange_rate := nvl(l_old_trx_rec.exchange_rate, 1);
arp_process_commitment.header_post_update(
p_commitment_rec,
l_invoice_currency_code,
l_exchange_rate,
p_rerun_autoacc_flag);
Select 'X' INTO l_dummy_flag
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND rownum = 1;
arp_ctl_pkg.update_f_ct_id( l_line_rec,
p_customer_trx_id,
'LINE');
'no child lines to update.');
arp_util.debug('EXCEPTION: arp_process_header.update_header: '||
'no child lines to clear rule info.');
arp_ctlgd_pkg.delete_f_ct_id(p_customer_trx_id,
'Y',
NULL);
'no dists to delete.');
| distributions. Therefore, we don't need to update |
| the distributions if autoaccounting is rerun - they will |
| already have the correct values. |
+------------------------------------------------------------*/
IF l_rerun_autoacc_flag /* Bug-3454082 - 4019170 */
THEN
header_rerun_aa(p_customer_trx_id,
p_gl_date,
p_trx_amount,
l_status2);
arp_ctlgd_pkg.update_acctd_amount(p_customer_trx_id,
pg_base_curr_code,
l_exchange_rate,
pg_base_precision,
pg_base_min_acc_unit);
Calling reverse_revrec_effect routine to delete Non modal row from line_gl_dist table for 'CM'
Once User complete this CM, it will regenerate Non Modal row. */
IF (l_gl_date_changed_flag AND
p_trx_rec.complete_flag = 'N' AND
p_trx_rec.previous_customer_trx_id IS NOT Null AND
p_trx_class = 'CM' AND
arpt_sql_func_util.get_nonmodal_row_exist_flag(
p_trx_rec.Customer_trx_id,
p_trx_rec.invoicing_rule_id) = 'Y')
THEN
arp_util.debug('Calling reverse_revrec_effect to delete non modal row +');
arp_util.debug('Calling reverse_revrec_effect to delete non modal row -');
so that the GL_DATE of the ROUND record is updated
properly even when autoaccounting is re-run. */
IF l_gl_date_changed_flag
THEN
-- update gl_date for all gl_distributions
arp_ctlgd_pkg.set_to_dummy(l_dist_rec);
arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
p_customer_trx_id,
null,
null);
arp_util.debug('arp_process_header.update_header: '||
'no dists to update.');
'arp_process_header.update_header()');
arp_util.debug( 'Before Update TAX P'|| pg_tax_flag);
| Do not update line tax codes when the Complete Flag is changed |
| to 'Y' and the system option Enforce from Revenue Account is 'Y'.|
| Line tax codes are corrected when the checkbox is changed on the |
| client side. Calling update_tax will erroneously override them. |
+------------------------------------------------------------------*/
IF ( l_complete_changed_flag AND
p_trx_rec.complete_flag = 'Y' AND
nvl(ARP_GLOBAL.sysparam.tax_enforce_account_flag, 'N') = 'Y' ) THEN
-- Don't update line tax code, Would've been updated at the client
-- side.
null;
| Call update_tax to re-default line tax codes |
+------------------------------------------------------------------*/
arp_ct_pkg.update_tax(p_trx_rec.ship_to_site_use_id,
p_trx_rec.bill_to_site_use_id,
p_trx_rec.trx_date,
p_trx_rec.cust_trx_type_id,
p_customer_trx_id,
pg_tax_flag,
FALSE);
SELECT customer_trx_line_id
INTO l_initial_customer_trx_line_id
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = p_trx_rec.initial_customer_trx_id
AND ctl.line_type = 'LINE';
arp_ctl_pkg.update_f_ct_id( l_line_rec,
p_customer_trx_id,
'LINE');
arp_util.debug('arp_process_header.update_header: '||
'no child lines to update.');
'arp_process_header.update_header()');
arp_ctlgd_pkg.update_f_ct_id(l_dist_rec,
p_customer_trx_id,
null,
null);
select xet.legal_entity_id legal_entity_id,
ct.SET_OF_BOOKS_ID set_of_books_id,
ct.org_id org_id,
xe.event_id event_id,
xet.entity_code entity_code,
ct.customer_trx_id transaction_id,
xet.application_id,
ctt.post_to_gl,
xe.event_status_code
into
l_event_source_info.legal_entity_id,
l_event_source_info.ledger_id,
l_security.security_id_int_1,
l_event_id ,
l_event_source_info.entity_type_code,
l_event_source_info.source_id_int_1,
l_event_source_info.application_id,
l_post_to_gl,
l_event_status_code
from
ra_customer_trx ct ,
ra_cust_trx_types ctt,
xla_transaction_entities_upg xet ,
xla_events xe
where ct.customer_trx_id = p_customer_trx_id
and ctt.cust_trx_type_id = ct.cust_trx_type_id
and ct.customer_trx_id = nvl(xet.source_id_int_1,-99)
AND ct.SET_OF_BOOKS_ID = xet.LEDGER_ID
and xet.entity_code ='TRANSACTIONS'
AND xet.application_id = 222
AND xe.entity_id = xet.entity_id
AND xe.application_id = 222 ;
xla_events_pub_pkg.delete_event
( p_event_source_info => l_event_source_info,
p_event_id => l_event_id,
p_valuation_method => NULL,
p_security_context => l_security);
update ra_cust_trx_line_gl_dist set event_id=null
WHERE customer_trx_id = p_customer_trx_id
and ACCOUNT_SET_FLAG='N'
and event_id =l_event_id;
'EXCEPTION: arp_process_header.update_header()' );
arp_util.debug('arp_process_header.update_header()-');
arp_util.debug('EXCEPTION: arp_process_header.update_header()');
| delete_cont_defer_data | |
| |
| DESCRIPTION |
| deletes rows from ar_line_conts and ar_deferred_lines. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: p_customer_trx_id | |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 07-NOV-08 Ankur Agarwal Created |
| |
| |
+===========================================================================*/
PROCEDURE delete_cont_defer_data(p_customer_trx_id IN NUMBER) IS
CURSOR cont_cursor IS
select alc.customer_trx_line_id
from ra_customer_trx_lines ctl,
ar_line_conts alc
where
ctl.customer_trx_id = p_customer_trx_id
and ctl.customer_trx_line_id = alc.customer_trx_line_id
and ctl.line_type = 'LINE'
FOR UPDATE OF alc.customer_trx_line_id NOWAIT;
select customer_trx_id
from ar_deferred_lines
where customer_trx_id = p_customer_trx_id
FOR UPDATE OF customer_trx_id NOWAIT;
arp_util.debug('arp_process_header.delete_cont_defer_data+');
delete from ar_line_conts
where customer_trx_line_id = l_cont_rec.customer_trx_line_id;
delete from ar_deferred_lines
WHERE customer_trx_id = l_deferred_rec.customer_trx_id;
arp_util.debug('arp_process_header.delete_cont_defer_data-');
'arp_process_header.delete_cont_defer_data()-');
| delete_header |
| |
| DESCRIPTION |
| deletes row from ra_customer_trx. Also deletes all child rows. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 26-JUL-95 Martin Johnson Created |
| 29-NOV-95 Nigel Smith Added call to Tax Engine. |
| |
+===========================================================================*/
PROCEDURE delete_header(
p_form_name IN varchar2,
p_form_version IN number,
p_customer_trx_id IN number,
p_trx_class IN varchar2,
p_status OUT NOCOPY varchar2)
IS
l_new_tax_amount NUMBER;
arp_util.debug('arp_process_header.delete_header()+');
validate_delete_header;
| pre-delete logic |
+--------------------*/
IF p_trx_class in ('DEP', 'GUAR')
THEN arp_process_commitment.header_pre_delete;
ARP_ETAX_UTIL.GLOBAL_DOCUMENT_UPDATE(p_customer_trx_id,
NULL,'DELETE');
ARP_XLA_EVENTS.delete_event( p_document_id => p_customer_trx_id,
p_doc_table => 'CT');
delete_cont_defer_data(p_customer_trx_id);
| delete the transaction |
+-------------------------*/
arp_trx_util.delete_transaction(p_form_name,
p_form_version,
p_customer_trx_id);
arp_util.debug('arp_process_header.delete_header()-');
arp_util.debug('EXCEPTION: arp_process_header.delete_header()');
| insertion or update of a transaction has occurred. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| arp_maintain_ps.maintain_payment_schedules |
| |
| ARGUMENTS : IN: |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-AUG-95 Charlie Tomberg Created |
| 19-FEB-96 Oliver Steinmeier Changed logic in post-commit to |
| make sure the payment schedule |
| gets called for debit memos |
| |
+===========================================================================*/
PROCEDURE post_commit( p_form_name IN varchar2,
p_form_version IN number,
p_customer_trx_id IN
ra_customer_trx.customer_trx_id%type,
p_previous_customer_trx_id IN
ra_customer_trx.previous_customer_trx_id%type,
p_complete_flag IN
ra_customer_trx.complete_flag%type,
p_trx_open_receivables_flag IN
ra_cust_trx_types.accounting_affect_flag%type,
p_prev_open_receivables_flag IN
ra_cust_trx_types.accounting_affect_flag%type,
p_creation_sign IN
ra_cust_trx_types.creation_sign%type,
p_allow_overapplication_flag IN
ra_cust_trx_types.allow_overapplication_flag%type,
p_natural_application_flag IN
ra_cust_trx_types.natural_application_only_flag%type,
p_cash_receipt_id IN
ar_cash_receipts.cash_receipt_id%type DEFAULT NULL
) IS
BEGIN
arp_process_header_post_commit.
post_commit( p_form_name,
p_form_version,
p_customer_trx_id,
p_previous_customer_trx_id,
p_complete_flag,
p_trx_open_receivables_flag,
p_prev_open_receivables_flag,
p_creation_sign,
p_allow_overapplication_flag,
p_natural_application_flag,
p_cash_receipt_id,
'STANDARD'
);
| update_header_freight_cover |
| |
| DESCRIPTION |
| Converts column parameters to a header transaction record and |
| updates the freight columns on the transaction header |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_form_name |
| p_form_version |
| p_trx_class |
| p_open_rec_flag |
| p_ship_via |
| p_ship_date_actual |
| p_waybill_number |
| p_fob_point |
| OUT: |
| p_status |
| IN/ OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 10-OCT-95 Subash Chadalavada Created |
| |
+===========================================================================*/
PROCEDURE update_header_freight_cover(
p_form_name IN varchar2,
p_form_version IN number,
p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
p_trx_class IN ra_cust_trx_types.type%type,
p_open_rec_flag IN ra_cust_trx_types.accounting_affect_flag%type,
p_ship_via IN ra_customer_trx.ship_via%type,
p_ship_date_actual IN ra_customer_trx.ship_date_actual%type,
p_waybill_number IN ra_customer_trx.waybill_number%type,
p_fob_point IN ra_customer_trx.fob_point%type,
p_status OUT NOCOPY varchar2)
IS
l_trx_rec ra_customer_trx%rowtype;
arp_util.debug('arp_process_header.update_header_freight_cover()+');
update_header(
p_form_name,
p_form_version,
l_trx_rec,
p_customer_trx_id,
null,
p_trx_class,
pg_date_dummy,
null,
l_commit_rec,
null,
null,
null,
null,
null,
null,
p_status);
arp_util.debug('arp_process_header.update_header_freight_cover()-');
'arp_process_header.update_header_freight_cover()-');
arp_util.debug('------- parameters for update_header_freight_cover ----');
| Selects additional data from the database in the following cases: |
| - The transaction is the child of a commitment |
| - The transaction is a commitment |
| - The transaction is a credit memo against a specific transaction |
| (not on account). |
| |
| This procedure was created so that the ra_customer_trx_v view could |
| be simplified by removing koins to support these special cases. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_ct_rowid |
| p_customer_trx_id |
| p_initial_customer_trx_id |
| p_previous_customer_trx_id |
| p_class |
| OUT: |
| p_ct_commitment_trx_date |
| p_ct_commitment_number |
| p_gd_commitment_gl_date |
| p_ctl_commit_cust_trx_line_id |
| p_ctl_commitment_amount |
| p_ctl_commitment_text |
| p_ctl_commitment_inv_item_id |
| p_interface_line_context |
| p_interface_line_attribute1 |
| p_interface_line_attribute2 |
| p_interface_line_attribute3 |
| p_interface_line_attribute4 |
| p_interface_line_attribute5 |
| p_interface_line_attribute6 |
| p_interface_line_attribute7 |
| p_interface_line_attribute8 |
| p_interface_line_attribute9 |
| p_interface_line_attribute10 |
| p_interface_line_attribute11 |
| p_interface_line_attribute12 |
| p_interface_line_attribute13 |
| p_interface_line_attribute14 |
| p_interface_line_attribute15 |
| p_attribute_category |
| p_attribute1 |
| p_attribute2 |
| p_attribute3 |
| p_attribute4 |
| p_attribute5 |
| p_attribute6 |
| p_attribute7 |
| p_attribute8 |
| p_attribute9 |
| p_attribute10 |
| p_attribute11 |
| p_attribute12 |
| p_attribute13 |
| p_attribute14 |
| p_attribute15 |
| p_ct_prev_trx_number |
| p_ct_prev_trx_reference |
| p_ct_prev_inv_currency_code |
| p_ct_prev_trx_date |
| p_ct_prev_bill_to_customer_id |
| p_ct_prev_ship_to_customer_id |
| p_ct_prev_sold_to_customer_id |
| p_ct_prev_paying_customer_id |
| p_ct_prev_bill_to_site_use_id |
| p_ct_prev_ship_to_site_use_id |
| p_ct_prev_paying_site_use_id |
| p_ct_prev_bill_to_contact_id |
| p_ct_prev_ship_to_contact_id |
| p_ct_prev_initial_cust_trx_id |
| p_ct_prev_primary_salesrep_id |
| p_ct_prev_invoicing_rule_id |
| p_gd_prev_gl_date |
| p_prev_trx_original |
| p_prev_trx_balance |
| p_rac_prev_bill_to_cust_name |
| p_rac_prev_bill_to_cust_num |
| p_bs_prev_source_name |
| p_ctt_prev_class |
| p_ctt_prev_allow_overapp_flag |
| p_ctt_prev_natural_app_only |
| p_al_cm_reason_meaning |
| IN/ OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 05-APR-96 Charlie Tomberg Created |
| 08-MAY-96 Martin Johnson BugNo:345208. Return p_ctl_commitment_|
| inv_item_id for Child Of A Commitment |
| case |
| 20-Oct-04 Surendra Rajan Bug-3954193 : Added two parameters ct_ |
| prev_open_receviables and ct_prev_post_|
| to_gl_flag in the procedure post_query.|
| |
| |
+===========================================================================*/
PROCEDURE post_query(
p_ct_rowid IN varchar2,
p_customer_trx_id IN NUMBER,
p_initial_customer_trx_id IN NUMBER,
p_previous_customer_trx_id IN NUMBER,
p_class IN varchar2,
p_ct_commitment_trx_date OUT NOCOPY date,
p_ct_commitment_number OUT NOCOPY varchar2,
p_gd_commitment_gl_date OUT NOCOPY date,
p_ctl_commit_cust_trx_line_id OUT NOCOPY number,
p_ctl_commitment_amount OUT NOCOPY number,
p_ctl_commitment_text OUT NOCOPY varchar2,
p_ctl_commitment_inv_item_id OUT NOCOPY number,
p_interface_line_context OUT NOCOPY varchar2,
p_interface_line_attribute1 OUT NOCOPY varchar2,
p_interface_line_attribute2 OUT NOCOPY varchar2,
p_interface_line_attribute3 OUT NOCOPY varchar2,
p_interface_line_attribute4 OUT NOCOPY varchar2,
p_interface_line_attribute5 OUT NOCOPY varchar2,
p_interface_line_attribute6 OUT NOCOPY varchar2,
p_interface_line_attribute7 OUT NOCOPY varchar2,
p_interface_line_attribute8 OUT NOCOPY varchar2,
p_interface_line_attribute9 OUT NOCOPY varchar2,
p_interface_line_attribute10 OUT NOCOPY varchar2,
p_interface_line_attribute11 OUT NOCOPY varchar2,
p_interface_line_attribute12 OUT NOCOPY varchar2,
p_interface_line_attribute13 OUT NOCOPY varchar2,
p_interface_line_attribute14 OUT NOCOPY varchar2,
p_interface_line_attribute15 OUT NOCOPY varchar2,
p_attribute_category OUT NOCOPY varchar2,
p_attribute1 OUT NOCOPY varchar2,
p_attribute2 OUT NOCOPY varchar2,
p_attribute3 OUT NOCOPY varchar2,
p_attribute4 OUT NOCOPY varchar2,
p_attribute5 OUT NOCOPY varchar2,
p_attribute6 OUT NOCOPY varchar2,
p_attribute7 OUT NOCOPY varchar2,
p_attribute8 OUT NOCOPY varchar2,
p_attribute9 OUT NOCOPY varchar2,
p_attribute10 OUT NOCOPY varchar2,
p_attribute11 OUT NOCOPY varchar2,
p_attribute12 OUT NOCOPY varchar2,
p_attribute13 OUT NOCOPY varchar2,
p_attribute14 OUT NOCOPY varchar2,
p_attribute15 OUT NOCOPY varchar2,
p_default_ussgl_trx_code OUT NOCOPY varchar2,
p_ct_prev_trx_number OUT NOCOPY varchar2,
p_ct_prev_trx_reference OUT NOCOPY varchar2,
p_ct_prev_inv_currency_code OUT NOCOPY varchar2,
p_ct_prev_trx_date OUT NOCOPY date,
p_ct_prev_bill_to_customer_id OUT NOCOPY number,
p_ct_prev_ship_to_customer_id OUT NOCOPY number,
p_ct_prev_sold_to_customer_id OUT NOCOPY number,
p_ct_prev_paying_customer_id OUT NOCOPY number,
p_ct_prev_bill_to_site_use_id OUT NOCOPY number,
p_ct_prev_ship_to_site_use_id OUT NOCOPY number,
p_ct_prev_paying_site_use_id OUT NOCOPY number,
p_ct_prev_bill_to_contact_id OUT NOCOPY number,
p_ct_prev_ship_to_contact_id OUT NOCOPY number,
p_ct_prev_initial_cust_trx_id OUT NOCOPY number,
p_ct_prev_primary_salesrep_id OUT NOCOPY number,
p_ct_prev_invoicing_rule_id OUT NOCOPY number,
p_gd_prev_gl_date OUT NOCOPY date,
p_prev_trx_original OUT NOCOPY number,
p_prev_trx_balance OUT NOCOPY number,
p_rac_prev_bill_to_cust_name OUT NOCOPY varchar2,
p_rac_prev_bill_to_cust_num OUT NOCOPY varchar2,
p_bs_prev_source_name OUT NOCOPY varchar2,
p_ctt_prev_class OUT NOCOPY varchar2,
p_ctt_prev_allow_overapp_flag OUT NOCOPY varchar2,
p_ctt_prev_natural_app_only OUT NOCOPY varchar2,
p_ct_prev_open_receivables OUT NOCOPY varchar2, /* Bug-3954193 */
p_ct_prev_post_to_gl_flag OUT NOCOPY varchar2, /* Bug-3954193 */
p_al_cm_reason_meaning OUT NOCOPY varchar2,
p_commit_memo_line_id OUT NOCOPY number,
p_commit_memo_line_desc OUT NOCOPY varchar2
) IS
l_ct_commitment_trx_date ra_customer_trx.trx_date%type;
SELECT ct_commit.trx_date,
ct_commit.trx_number,
gd_commit.gl_date,
ctl_commit.inventory_item_id
INTO l_ct_commitment_trx_date,
l_ct_commitment_number,
l_gd_commitment_gl_date,
l_ctl_commitment_inv_item_id
FROM ra_customer_trx ct_commit,
ra_cust_trx_line_gl_dist gd_commit,
ra_customer_trx_lines ctl_commit
WHERE ct_commit.customer_trx_id = p_initial_customer_trx_id
AND ct_commit.customer_trx_id = ctl_commit.customer_trx_id
AND ct_commit.customer_trx_id = gd_commit.customer_trx_id
AND 'REC' = gd_commit.account_class(+)
AND 'Y' = gd_commit.latest_rec_flag(+);
SELECT ctl_commit.customer_trx_line_id,
ctl_commit.extended_amount,
ctl_commit.description,
ctl_commit.inventory_item_id,
ctl_commit.interface_line_context,
ctl_commit.interface_line_attribute1,
ctl_commit.interface_line_attribute2,
ctl_commit.interface_line_attribute3,
ctl_commit.interface_line_attribute4,
ctl_commit.interface_line_attribute5,
ctl_commit.interface_line_attribute6,
ctl_commit.interface_line_attribute7,
ctl_commit.interface_line_attribute8,
ctl_commit.interface_line_attribute9,
ctl_commit.interface_line_attribute10,
ctl_commit.interface_line_attribute11,
ctl_commit.interface_line_attribute12,
ctl_commit.interface_line_attribute13,
ctl_commit.interface_line_attribute14,
ctl_commit.interface_line_attribute15,
ctl_commit.attribute_category,
ctl_commit.attribute1,
ctl_commit.attribute2,
ctl_commit.attribute3,
ctl_commit.attribute4,
ctl_commit.attribute5,
ctl_commit.attribute6,
ctl_commit.attribute7,
ctl_commit.attribute8,
ctl_commit.attribute9,
ctl_commit.attribute10,
ctl_commit.attribute11,
ctl_commit.attribute12,
ctl_commit.attribute13,
ctl_commit.attribute14,
ctl_commit.attribute15,
ctl_commit.default_ussgl_transaction_code,
ctl_commit.memo_line_id
INTO l_ctl_commit_cust_trx_line_id,
l_ctl_commitment_amount,
l_ctl_commitment_text,
l_ctl_commitment_inv_item_id,
l_interface_line_context,
l_interface_line_attribute1,
l_interface_line_attribute2,
l_interface_line_attribute3,
l_interface_line_attribute4,
l_interface_line_attribute5,
l_interface_line_attribute6,
l_interface_line_attribute7,
l_interface_line_attribute8,
l_interface_line_attribute9,
l_interface_line_attribute10,
l_interface_line_attribute11,
l_interface_line_attribute12,
l_interface_line_attribute13,
l_interface_line_attribute14,
l_interface_line_attribute15,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_default_ussgl_trx_code,
l_commit_memo_line_id
FROM ra_customer_trx_lines ctl_commit
WHERE ctl_commit.customer_trx_id = p_customer_trx_id
AND 1 = ctl_commit.line_number
AND 'LINE' = ctl_commit.line_type;
SELECT description
INTO l_commit_memo_line_desc
FROM ar_memo_lines
WHERE memo_line_id = l_commit_memo_line_id;
SELECT ct_prev.rowid,
ct_prev.trx_number,
ct_prev.invoice_currency_code,
ct_prev.trx_date,
ct_prev.bill_to_customer_id,
ct_prev.ship_to_customer_id,
ct_prev.sold_to_customer_id,
ct_prev.paying_customer_id,
ct_prev.bill_to_site_use_id,
ct_prev.ship_to_site_use_id,
ct_prev.paying_site_use_id,
ct_prev.bill_to_contact_id,
ct_prev.ship_to_contact_id,
ct_prev.initial_customer_trx_id,
ct_prev.primary_salesrep_id,
ct_prev.invoicing_rule_id,
gd_prev.gl_date,
gd_prev.amount,
substrb(party.party_name,1,50),
rac_prev.account_number,
bs_prev.name,
ctt_prev.type,
ctt_prev.allow_overapplication_flag,
ctt_prev.natural_application_only_flag,
ctt_prev.accounting_affect_flag, /* Bug-3954193 */
ctt_prev.post_to_gl, /* Bug-3954193 */
al_cm_reason.meaning
INTO l_ct_prev_rowid,
l_ct_prev_trx_number,
l_ct_prev_inv_currency_code,
l_ct_prev_trx_date,
l_ct_prev_bill_to_customer_id,
l_ct_prev_ship_to_customer_id,
l_ct_prev_sold_to_customer_id,
l_ct_prev_paying_customer_id,
l_ct_prev_bill_to_site_use_id,
l_ct_prev_ship_to_site_use_id,
l_ct_prev_paying_site_use_id,
l_ct_prev_bill_to_contact_id,
l_ct_prev_ship_to_contact_id,
l_ct_prev_initial_cust_trx_id,
l_ct_prev_primary_salesrep_id,
l_ct_prev_invoicing_rule_id,
l_gd_prev_gl_date,
l_prev_trx_original,
l_rac_prev_bill_to_cust_name,
l_rac_prev_bill_to_cust_num,
l_bs_prev_source_name,
l_ctt_prev_class,
l_ctt_prev_allow_overapp_flag,
l_ctt_prev_natural_app_only,
l_ct_prev_open_receivables, /* Bug-3954193 */
l_ct_prev_post_to_gl_flag, /* Bug-3954193 */
l_al_cm_reason_meaning
FROM ra_customer_trx ct,
ra_customer_trx ct_prev,
ra_cust_trx_line_gl_dist gd_prev,
hz_cust_accounts rac_prev,
hz_parties party,
ra_batch_sources bs_prev,
ra_cust_trx_types ctt_prev,
ar_lookups al_cm_reason
WHERE ct.rowid = p_ct_rowid
and ct.previous_customer_trx_id = ct_prev.customer_trx_id
and ct_prev.batch_source_id = bs_prev.batch_source_id
and ct_prev.cust_trx_type_id = ctt_prev.cust_trx_type_id
and ct_prev.customer_trx_id = gd_prev.customer_trx_id
and 'REC' = gd_prev.account_class
and 'Y' = gd_prev.latest_rec_flag
and ct_prev.bill_to_customer_id = rac_prev.cust_account_id
and rac_prev.party_id = party.party_id
and 'CREDIT_MEMO_REASON' = al_cm_reason.lookup_type(+)
and ct.reason_code = al_cm_reason.lookup_code(+);