[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select translated_description
into l_description
from ra_customer_trx_lines
where customer_trx_line_id = p_customer_trx_line_id;
SELECT nvl( cp_site.tax_printing_option,
nvl(cp_cust.tax_printing_option,p_tax_printing_option) )
INTO l_tax_printing_option
FROM hz_customer_profiles cp_site,
hz_customer_profiles cp_cust,
hz_cust_site_uses site
WHERE cp_site.site_use_id(+) = site.site_use_id
AND site.site_use_id = p_bill_to_site_use_id
AND cp_cust.cust_account_id = p_bill_to_customer_id
AND cp_cust.site_use_id is null;
SELECT NVL(SUM(adj.amount),0)
INTO commit_adjustments
FROM ra_customer_trx trx,
ra_cust_trx_types type,
ar_adjustments adj
WHERE trx.cust_trx_type_id = type.cust_trx_type_id
AND type.type in ('INV', 'CM')
AND trx.complete_flag ='Y'
AND trx.initial_customer_trx_id = p_init_cust_trx_id
AND adj.customer_trx_id = DECODE(type.type,
'INV', trx.customer_trx_id,
'CM', trx.previous_customer_trx_id)
AND NVL(adj.subsequent_trx_id,-111) = DECODE(type.type,
'INV',-111,
'CM',trx.customer_trx_id)
AND adj.adjustment_type = 'C';
SELECT NVL(SUM(line.extended_amount),0)
INTO commit_total_activity
FROM ra_customer_trx trx,
ra_cust_trx_types type,
ra_customer_trx_lines line
WHERE trx.cust_trx_type_id = type.cust_trx_type_id
AND trx.customer_trx_id = line.customer_trx_id
AND type.type = 'CM'
AND trx.complete_flag = 'Y'
AND trx.previous_customer_trx_id = p_init_cust_trx_id;
SELECT -1 * (NVL(SUM(amount_line_items_original), 0) -
NVL(SUM(amount_line_items_remaining), 0))
INTO commit_total_activity
FROM ar_payment_schedules
WHERE customer_trx_id = p_init_cust_trx_id;
SELECT SUM(Amount)
INTO commit_this_invoice
FROM ar_adjustments
WHERE adjustment_type = 'C'
AND ( ((customer_trx_id = p_customer_trx_id )
AND (subsequent_trx_id is null))
OR subsequent_trx_id = p_customer_trx_id);
SELECT SUM(Amount)
INTO commit_this_invoice
FROM ar_adjustments
WHERE adjustment_type = 'C'
AND ( ((customer_trx_id = p_customer_trx_id )
AND (subsequent_trx_id is null))
OR subsequent_trx_id = p_customer_trx_id) ;
SELECT NVL(SUM(adj.amount),0)
INTO commit_adjustments
FROM ra_customer_trx trx,
ra_cust_trx_types type,
ar_adjustments adj
WHERE trx.cust_trx_type_id = type.cust_trx_type_id
AND type.type in ('INV', 'CM')
AND trx.complete_flag ='Y'
AND trx.initial_customer_trx_id = p_init_cust_trx_id
AND adj.customer_trx_id = DECODE(type.type,
'INV', trx.customer_trx_id,
'CM', trx.previous_customer_trx_id)
AND NVL(adj.subsequent_trx_id,-111) = DECODE(type.type,
'INV',-111,
'CM',trx.customer_trx_id)
AND adj.adjustment_type = 'C';
SELECT NVL(SUM(line.extended_amount),0)
INTO commit_total_activity
FROM ra_customer_trx trx,
ra_cust_trx_types type,
ra_customer_trx_lines line
WHERE trx.cust_trx_type_id = type.cust_trx_type_id
AND trx.customer_trx_id = line.customer_trx_id
AND type.type = 'CM'
AND trx.complete_flag = 'Y'
AND trx.previous_customer_trx_id = p_init_cust_trx_id;
SELECT -1 * (NVL(SUM(amount_line_items_original), 0) -
NVL(SUM(amount_line_items_remaining), 0))
INTO commit_total_activity
FROM ar_payment_schedules
WHERE customer_trx_id = p_init_cust_trx_id;
|| PRIVATE PROCEDURE update_customer_trx
||
|| DESCRIPTION This procedure updates the ra_customer_trx table
|| and sets the printing information.
||
|| ARGUMENTS
|| IN: p_choice
|| p_customer_trx_id
|| p_trx_type
|| p_term_count
|| p_term_sequence_number
|| p_printing_count
|| p_printing_original_date
||
|| OUT:
||
|| FUNCTION CALL
||
|| RETURN
||
|| NOTE This is a update procedure. So pragma restriction should not be
|| imposed in its declaration.
||
|| MODIFICATION HISTORY
|| 29-MAY-97 Ashim K Dey Created
=============================================================================*/
PROCEDURE update_customer_trx (
p_choice IN VARCHAR2,
p_customer_trx_id IN NUMBER,
p_trx_type IN VARCHAR2,
p_term_count IN NUMBER,
p_term_sequence_number IN NUMBER,
p_printing_count IN NUMBER,
p_printing_original_date IN DATE) IS
BEGIN
IF
p_choice <> 'ADJ'
THEN
/* 4188835 - freeze for tax if printing columns updated */
IF NVL(p_printing_count, 0) = 0
THEN
/* This is the first run for this one -- freeze it */
arp_etax_util.global_document_update(p_customer_trx_id,
null,
'PRINT');
UPDATE ra_customer_trx
SET printing_pending =
decode (p_trx_type, 'CM', 'N',
decode (p_term_count,
greatest(nvl(last_printed_sequence_num,0),
p_term_sequence_number), 'N',
NULL, 'N',
1, 'N',
0, 'N',
'Y')),
printing_count = decode(p_printing_count,
NULL, 0,
p_printing_count) + 1,
printing_last_printed = SYSDATE,
printing_original_date = decode(p_printing_count, 0, SYSDATE,
p_printing_original_date),
last_printed_sequence_num =
decode(p_term_count,NULL,NULL,
greatest(nvl(last_printed_sequence_num,0),
p_term_sequence_number))
WHERE customer_trx_id = p_customer_trx_id;
END update_customer_trx ;
SELECT 'x' from dual where exists
( SELECT 'x'
FROM ra_customer_trx_lines l
WHERE l.link_to_cust_trx_line_id = line_id
AND l.line_type = 'TAX'
AND l.extended_amount <> 0 );
SELECT meaning
INTO l_taxyn
FROM ar_lookups
WHERE lookup_type = 'YES/NO'
AND lookup_code = 'Y' ;
SELECT meaning
INTO l_taxyn
FROM ar_lookups
WHERE lookup_type = 'YES/NO'
AND lookup_code = 'N' ;
SELECT rt.address_id
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
ra_remit_tos rt
WHERE acct_site.cust_acct_site_id = rt.address_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND nvl(rt.status,'A') = 'A'
AND nvl(acct_site.status, 'A') = 'A'
AND (nvl(rt.state, inv_state)= inv_state
OR
(inv_state IS NULL AND
rt.state IS NULL))
AND ((inv_postal_code between
rt.postal_code_low and rt.postal_code_high)
OR
(rt.postal_code_high IS NULL and rt.postal_code_low IS NULL))
AND rt.country = inv_country
ORDER BY rt.postal_code_low,
rt.postal_code_high,
rt.state,
loc.address1,
loc.address2;
SELECT loc.state,
loc.country,
loc.postal_code
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses site_uses
WHERE acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_id = bill_site_use_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id;
SELECT remit_to_address_id
INTO l_remit_to_address_id
FROM ra_customer_trx
WHERE customer_trx_id = p_previous_customer_trx_id;