[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX( ct.trx_number )
INTO l_inv_trx_number
FROM ra_customer_trx ct,
ar_adjustments_all aa --anuj
WHERE aa.chargeback_customer_trx_id = p_customer_trx_id
and ct.org_id = aa.org_id
AND aa.customer_trx_id = ct.customer_trx_id;
SELECT SUM( NVL( ps.AMOUNT_IN_DISPUTE, 0) )
INTO l_amount_in_dispute
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id;
SELECT MAX(ps.dispute_date )
INTO l_dispute_date
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id
AND ps.dispute_date IS NOT NULL;
SELECT MAX(h.start_date)
INTO l_dispute_date
FROM ar_dispute_history h,
ar_payment_schedules ps
WHERE h.payment_schedule_id = ps.payment_schedule_id
AND ps.customer_trx_id = p_customer_trx_id
AND end_date IS NULL;
| SELECT to determine whether Revenue Recognition is |
| run is also modified.The old code is commented out |
| below the new one. |
| |
+===========================================================================*/
FUNCTION get_revenue_recog_run_flag( p_customer_trx_id IN number,
p_invoicing_rule_id IN number)
RETURN VARCHAR2 IS
l_rule_flag varchar2(1) := 'N';
Select decode(max(DUMMY), null , 'N','Y')
Into l_rule_flag
From dual
Where Exists ( Select 'Revenue recognition has been run'
From ra_cust_trx_line_gl_dist d
Where d.customer_trx_id = p_customer_trx_id
and d.account_class = 'REC'
and d.account_set_flag = 'N');
SELECT decode(previous_customer_trx_id,NULL,'N','Y')
INTO l_cm_flag
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
SELECT decode( max(d.customer_trx_id),
null, 'N',
'Y')
INTO l_rule_flag
FROM ra_customer_trx trx,
ra_cust_trx_line_gl_dist d
WHERE trx.customer_trx_id = p_customer_trx_id
and trx.previous_customer_trx_id = d.customer_trx_id
and d.account_class in ('UNEARN', 'UNBILL')
and d.account_set_flag='N'; Added for bug 559954
SELECT DECODE( MAX(DUMMY),
NULL, 'N',
'Y')
INTO l_temp_flag
FROM DUAL
WHERE EXISTS (
SELECT 'Revenue recognition has been run'
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND autorule_duration_processed <> 0
);
SELECT DECODE( MAX(DUMMY),
NULL, 'N',
'Y')
INTO l_temp_flag
FROM DUAL
WHERE EXISTS (
SELECT 'transaction has been posted'
FROM ar_transaction_history
WHERE customer_trx_id = p_customer_trx_id
AND gl_posted_date IS NOT NULL
);
SELECT DECODE( MAX(DUMMY),
NULL, 'N',
'Y')
INTO l_temp_flag
FROM DUAL
WHERE EXISTS (
SELECT 'transaction has been posted'
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_customer_trx_id
AND gl_posted_date IS NOT NULL
);
FUNCTION get_selected_for_payment_flag( p_customer_trx_id IN number,
p_open_receivables_flag IN varchar2,
p_complete_flag IN varchar2)
RETURN VARCHAR2 IS
l_auto_rec_count integer;
| been selected for automatic receipt. |
+-------------------------------------------*/
SELECT COUNT(*)
INTO l_auto_rec_count
FROM ar_payment_schedules
WHERE customer_trx_id = p_customer_trx_id
AND selected_for_receipt_batch_id IS NOT NULL;
| If no payment schedules have been selected |
| for automatic receipt, return 'N'. |
+-----------------------------------------------*/
IF ( l_auto_rec_count = 0 )
THEN RETURN( 'N' );
| Find out how many of the payment schedules selected |
| for automatic receipt have been approved. |
+-------------------------------------------------------*/
SELECT COUNT(DISTINCT ps.payment_schedule_id)
INTO l_auto_rec_approved_count
FROM ar_payment_schedules ps,
ar_receivable_applications ra,
ar_cash_receipt_history crh
WHERE ps.customer_trx_id = p_customer_trx_id
AND ra.applied_payment_schedule_id = ps.payment_schedule_id
AND ra.cash_receipt_id = crh.cash_receipt_id
AND ps.selected_for_receipt_batch_id = crh.batch_id
AND crh.batch_id = ps.selected_for_receipt_batch_id;
| If all of the payment schedules selected for automatic |
| receipt have been approved, then return 'N'. |
| Otherwise, return 'Y'. |
+---------------------------------------------------------*/
IF ( l_auto_rec_count = l_auto_rec_approved_count )
THEN RETURN('N');
SELECT 'Y' FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_customer_trx_id
AND account_set_flag = 'N'
AND posting_control_id > 0;
SELECT 'Y' FROM iex_delinquencies
WHERE transaction_id = p_customer_trx_id;
SELECT DECODE(ctt.type,
'GUAR', 'Y',
'N')
INTO l_activity_flag
FROM ra_customer_trx ct,
ra_cust_trx_types ctt
WHERE ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.customer_trx_id = p_initial_customer_trx_id
--begin anuj
/* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
SELECT DECODE( MAX(ct.customer_trx_id),
NULL, 'N',
'Y')
INTO l_activity_flag
FROM ra_customer_trx ct
WHERE ct.initial_customer_trx_id = p_customer_trx_id;
SELECT DECODE( MAX(ps.payment_schedule_id),
NULL, 'N',
'Y')
INTO l_activity_flag
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id
AND (
ps.amount_due_original <> ps.amount_due_remaining
OR NVL(ps.amount_applied,0) <> 0
OR NVL(ps.amount_credited,0) <> 0
OR NVL(ps.amount_adjusted,0) <> 0
OR NVL(ps.amount_in_dispute,0) <> 0
OR ps.selected_for_receipt_batch_id IS NOT NULL
OR exists
(
SELECT 'dunned'
FROM ar_correspondence_pay_sched cps
WHERE cps.payment_schedule_id =
ps.payment_schedule_id
)
);
SELECT DECODE( MAX( receivable_application_id ),
NULL, 'N',
'Y' )
INTO l_activity_flag
FROM ar_receivable_applications app
WHERE app.customer_trx_id = p_customer_trx_id
OR app.applied_customer_trx_id = p_customer_trx_id;
SELECT DECODE( MAX( payment_schedule_id ),
NULL, 'N',
'Y' )
INTO l_activity_flag
FROM ar_payment_schedules ps
WHERE (
(
NVL(ps.amount_credited, 0) <> 0
OR NVL(ps.amount_adjusted, 0) <> 0
OR NVL(ps.amount_in_dispute, 0) <> 0
)
OR
ps.selected_for_receipt_batch_id IS NOT NULL
)
AND ps.customer_trx_id = p_customer_trx_id;
SELECT DECODE( MAX( other_ps.payment_schedule_id),
NULL, 'N',
'Y')
INTO l_activity_flag
FROM ar_payment_schedules this_ps,
ar_payment_schedules other_ps,
ra_customer_trx other_ct
WHERE this_ps.customer_trx_id = p_customer_trx_id
AND other_ct.previous_customer_trx_id =
p_previous_customer_trx_id
AND other_ct.customer_trx_id = other_ps.customer_trx_id
AND other_ps.creation_date > this_ps.creation_date;
SELECT DECODE( MAX(adjustment_id),
NULL, 'N',
'Y')
INTO l_activity_flag
FROM ar_adjustments
WHERE customer_trx_id = p_customer_trx_id;
SELECT DECODE( MAX( customer_trx_id ),
NULL, 'N',
'Y')
INTO l_activity_flag
FROM ar_interim_cash_receipts
WHERE customer_trx_id = p_customer_trx_id;
SELECT DECODE( MAX( customer_trx_id ),
NULL, 'N',
'Y')
INTO l_activity_flag
FROM ar_interim_cash_receipt_lines
WHERE customer_trx_id = p_customer_trx_id;
SELECT DECODE( MAX( cons_inv_id ),
NULL, 'N',
'Y')
INTO l_activity_flag
FROM ar_payment_schedules
WHERE customer_trx_id = p_customer_trx_id;
SELECT DECODE( MAX(customer_trx_line_id),
NULL, 'N',
'Y')
INTO l_activity_flag
FROM ra_customer_trx_lines ctl
WHERE br_ref_customer_trx_id = p_customer_trx_id;
SELECT DECODE( MAX( gl_posted_date ), NULL, 'N', 'Y')
INTO l_activity_flag
FROM ar_transaction_history
WHERE customer_trx_id = p_customer_trx_id;
SELECT DECODE( MAX( gl_posted_date ), NULL, 'N', 'Y')
INTO l_activity_flag
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_customer_trx_id;
SELECT decode (MAX(previous_customer_trx_id), NULL, 'N', 'Y')
INTO l_activity_flag
FROM ra_customer_trx
WHERE previous_customer_trx_id = p_customer_trx_id;
SELECT DECODE(DEFAULT_REFERENCE,
'1', ct.interface_header_attribute1,
'2', ct.interface_header_attribute2,
'3', ct.interface_header_attribute3,
'4', ct.interface_header_attribute4,
'5', ct.interface_header_attribute5,
'6', ct.interface_header_attribute6,
'7', ct.interface_header_attribute7,
'8', ct.interface_header_attribute8,
'9', ct.interface_header_attribute9,
'10', ct.interface_header_attribute10,
'11', ct.interface_header_attribute11,
'12', ct.interface_header_attribute12,
'13', ct.interface_header_attribute13,
'14', ct.interface_header_attribute14,
'15', ct.interface_header_attribute15,
NULL )
INTO l_temp
FROM ra_customer_trx ct,
ra_batch_sources bs
WHERE ct.rowid = p_trx_rowid and
bs.batch_source_id = ct.batch_source_id;
SELECT DECODE(DEFAULT_REFERENCE,
'1', ctl.interface_line_attribute1,
'2', ctl.interface_line_attribute2,
'3', ctl.interface_line_attribute3,
'4', ctl.interface_line_attribute4,
'5', ctl.interface_line_attribute5,
'6', ctl.interface_line_attribute6,
'7', ctl.interface_line_attribute7,
'8', ctl.interface_line_attribute8,
'9', ctl.interface_line_attribute9,
'10', ctl.interface_line_attribute10,
'11', ctl.interface_line_attribute11,
'12', ctl.interface_line_attribute12,
'13', ctl.interface_line_attribute13,
'14', ctl.interface_line_attribute14,
'15', ctl.interface_line_attribute15,
NULL )
INTO l_line_temp
FROM ra_customer_trx_lines ctl,
ra_customer_trx ct,
ra_batch_sources bs
WHERE bs.batch_source_id = ct.batch_source_id and
ctl.customer_trx_id = ct.customer_trx_id and
ctl.rowid = p_line_trx_rowid ;
SELECT
DECODE( tl.due_days,
NULL, NVL( tl.due_date,
DECODE ( LEAST(
TO_NUMBER(
TO_CHAR(p_trx_date,
'DD') ),
NVL(t.due_cutoff_day, 32)
),
t.due_cutoff_day,
LAST_DAY(
ADD_MONTHS(
p_trx_date,
tl.due_months_forward
) )
+ LEAST(tl.due_day_of_month,
TO_NUMBER(
TO_CHAR(
LAST_DAY(
ADD_MONTHS(p_trx_date,
tl.due_months_forward +
1 )
), 'DD'
) ) ),
/*BUG 1702687 --ADDED decode(tl.due....)*/
/* BUG 2019477 -- ADDED the decode(sign(trunc(t */
LAST_DAY( ADD_MONTHS(p_trx_date,
(tl.due_months_forward +decode(tl.due_months_forward-trunc(tl.due_months_forward),0,-1,0)))+
decode(sign(trunc(tl.due_months_forward)-tl.due_months_forward),-1,
decode(sign(((TO_NUMBER(TO_CHAR(p_trx_date,'DD')))+
(tl.due_months_forward-trunc(tl.due_months_forward))*30)-t.due_cutoff_day),-1,-30,0),0)
/*BUG 1702687 ends */
) +
LEAST( tl.due_day_of_month,
TO_NUMBER(
TO_CHAR(
LAST_DAY(
ADD_MONTHS(p_trx_date,
tl.due_months_forward)
), 'DD'
) )
)
)
),
p_trx_date + tl.due_days
)
INTO l_term_due_date
FROM ra_terms_lines tl,
ra_terms t
WHERE tl.term_id = p_term_id
AND t.term_id = tl.term_id
AND tl.sequence_num = (
SELECT MIN(sequence_num)
FROM ra_terms_lines
WHERE term_id = p_term_id
);
SELECT NVL(MIN(ps.due_date),
arpt_sql_func_util.get_first_due_date(p_term_id, nvl(ct.billing_date, p_trx_date)))
INTO l_term_due_date
FROM ar_payment_schedules ps,
ra_customer_trx ct
WHERE ct.customer_trx_id=ps.customer_trx_id(+)
AND ct.customer_trx_id = p_customer_trx_id
group by ct.billing_date;
SELECT COUNT(*)
INTO l_count
FROM ra_terms_lines
WHERE term_id = p_term_id;
select /*+use_nl(sp,gps) index(gps gl_period_statuses_u1)*/
gps.period_name
from gl_period_statuses gps,
ar_system_parameters sp
where gps.application_id = 222
and gps.adjustment_period_flag = 'N'
and gps.set_of_books_id = sp.set_of_books_id
and p_gl_date between gps.start_date and gps.end_date;
select ft.territory_short_name
from fnd_territories_vl ft,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc
where loc.country = ft.territory_code
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and acct_site.cust_acct_site_id = p_address_id;
select ft.rowid
from fnd_territories_vl ft,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc
where loc.country = ft.territory_code
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and acct_site.cust_acct_site_id = p_address_id;
SELECT sp.code_combination_id_gain,
sb.currency_code
INTO l_code_combination_id_gain,
l_base_currency
FROM ar_system_parameters sp,
gl_sets_of_books sb
WHERE sp.set_of_books_id = sb.set_of_books_id;
SELECT MAX(initial_customer_trx_id)
INTO l_ct_prev_initial_cust_trx_id
FROM ra_customer_trx
WHERE customeR_trx_id = p_previous_customer_trx_id;
SELECT DECODE( MAX(dummy),
NULL, 'N',
'Y' )
INTO l_commitments_exist_flag
FROM DUAL
WHERE EXISTS
(
SELECT 'commitments_exist'
FROM hz_cust_accounts cust_acct,
so_agreements soa,
hz_cust_acct_sites acct_site,
ra_cust_trx_types inv_type,
ra_cust_trx_types type,
ra_customer_trx trx
WHERE trx.cust_trx_type_id = type.cust_trx_type_id
AND trx.bill_to_customer_id = cust_acct.cust_account_id
AND trx.remit_to_address_id = acct_site.cust_acct_site_id(+)
AND 'A' = acct_site.status(+)
AND trx.agreement_id = soa.agreement_id(+)
AND type.subsequent_trx_type_id = inv_type.cust_trx_type_id(+)
AND 'A' = inv_type.status(+)
AND type.type in ('DEP','GUAR')
AND trx.complete_flag = 'Y'
AND trx.bill_to_customer_id
in (
select distinct cr.cust_account_id
from hz_cust_acct_relate cr
where cr.related_cust_account_id = p_bill_to_customer_id
AND status = 'A'
union
select to_number(p_bill_to_customer_id)
from dual
UNION
SELECT acc.cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE rel.party_id = acc.party_id
AND rel.related_cust_account_id = p_bill_to_customer_id
AND l_trx_date BETWEEN effective_start_date
AND effective_end_date
)
AND trx.invoice_currency_code =
DECODE(l_code_combination_id_gain,
NULL, l_base_currency,
p_invoice_currency_code
) /* non-on account credit memos must have the same
commitment as the transactions that they are
crediting. */
AND (
p_previous_customer_trx_id is NULL
or trx.customer_trx_id = l_ct_prev_initial_cust_trx_id
)
/* check effectivity dates */
AND l_trx_date
BETWEEN NVL(trx.start_date_commitment, l_trx_date)
AND NVL(trx.end_date_commitment, l_trx_date)
AND l_trx_date
BETWEEN NVL( soa.start_date_active(+), l_trx_date)
AND NVL( soa.end_date_active(+), l_trx_date)
AND l_trx_date
BETWEEN NVL( inv_type.start_date(+), l_trx_date)
AND NVL( inv_type.end_date(+), l_trx_date)
);
SELECT DECODE( MAX(dummy),
NULL, 'N',
'Y' )
INTO l_agreements_exist_flag
FROM DUAL
WHERE EXISTS
(
SELECT 'agreements_exist'
FROM so_agreements a
WHERE a.customer_id IN
(
SELECT cr.cust_account_id
FROM hz_cust_acct_relate cr
WHERE cr.related_cust_account_id = p_bill_to_customer_id
AND cr.status = 'A'
UNION ALL
SELECT to_number(p_bill_to_customer_id)
FROM dual
UNION ALL
SELECT -1 /* no customer case */
FROM dual
)
AND p_trx_date
BETWEEN NVL( TRUNC( a.start_date_active ), p_trx_date )
AND NVL( TRUNC( a.end_date_active ), p_trx_date )
);
SELECT NVL(site.override_terms, cust.override_terms)
INTO l_override_terms
FROM hz_customer_profiles cust,
hz_customer_profiles site
WHERE cust.cust_account_id = p_customer_id
AND cust.site_use_id IS NULL
AND site.cust_account_id (+) = cust.cust_account_id
AND site.site_use_id (+) = NVL(p_site_use_id,-44444);
select bs.name
into l_bs_name
from ra_customer_trx ct,
ra_batch_sources bs
where ct.batch_source_id = bs.batch_source_id
and ct.org_id = bs.org_id --anuj
and ct.customer_trx_id = (select max(ctt.customer_trx_id)
from ra_customer_trx ctt,
ar_adjustments aa
where aa.chargeback_customer_trx_id = p_customer_trx_id
and aa.org_id = ctt.org_id --anuj
and aa.customer_trx_id = ctt.customer_trx_id
);
select MAX(arc.correspondence_date)
into l_dunning_date_last
from ar_correspondences arc,
ar_correspondence_pay_sched arcps
where arcps.payment_schedule_id = p_payment_schedule_id
and arc.correspondence_id = arcps.correspondence_id
and nvl(arc.preliminary_flag,'N') = 'N';
SELECT meaning
INTO l_meaning
FROM ar_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code ;
SELECT name, salesrep_number
INTO l_salesrep_name, l_salesrep_number
FROM ra_salesreps
WHERE salesrep_id = p_salesrep_id and
--begin anuj
/* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
SELECT loc.ADDRESS1, loc.ADDRESS2, loc.ADDRESS3, loc.ADDRESS4,
loc.CITY, loc.STATE, loc.PROVINCE, loc.POSTAL_CODE, loc.COUNTRY,
acct_site.STATUS
INTO l_add1, l_add2, l_add3, l_add4,
l_city, l_state,l_province, l_postal_code, l_country,
l_status
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc
WHERE acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND acct_site.cust_acct_site_id = p_address_id;
SELECT territory_short_name
INTO l_territory_short_name
FROM fnd_territories_vl
WHERE territory_code = l_country;
SELECT decode(cont_point.contact_point_type, 'TLX',
cont_point.telex_number, cont_point.phone_number),
cont_point.phone_area_code,
cont_point.phone_extension
INTO l_ph_num, l_area_code, l_ext
FROM hz_contact_points cont_point
WHERE cont_point.contact_point_id = p_phone_id;
select max(rowid)
into l_max_rowid
from gl_import_references
where (je_header_id, je_batch_id, je_line_num,
reference_2, reference_3,
reference_8, reference_9) = (select je_header_id, je_batch_id,je_line_num,
reference_2, reference_3,
reference_8, reference_9
from gl_import_references
where rowid = p_rowid);
SELECT name,calc_discount_on_lines_flag,partial_discount_flag
INTO l_name, l_calc_disc_on_lines_flag, l_partial_discount_flag
FROM ra_terms
WHERE term_id = p_term_id;
SELECT name,start_date_active, end_date_active
INTO l_name, l_start_date_active, l_end_date_active
FROM so_agreements
WHERE agreement_id = p_agreement_id ;
SELECT name,start_date_active, end_date_active
INTO l_name, l_start_date_active, l_end_date_active
FROM so_agreements
WHERE agreement_id = p_agreement_id ;
SELECT name, type, subsequent_trx_type_id ,
allow_overapplication_flag ,
natural_application_only_flag,
creation_sign, post_to_gl
INTO l_name, l_type, l_subseq_trx_type_id,
l_allow_overapplication_flag ,
l_natural_application_flag,
l_creation_sign, l_post_to_gl
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = p_trx_type_id
AND org_id = p_org_id;
SELECT name, type, subsequent_trx_type_id ,
allow_overapplication_flag ,
natural_application_only_flag,
creation_sign, post_to_gl
INTO l_name, l_type, l_subseq_trx_type_id,
l_allow_overapplication_flag ,
l_natural_application_flag,
l_creation_sign, l_post_to_gl
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_trx_type_id;
SELECT name, type, subsequent_trx_type_id ,
allow_overapplication_flag ,
natural_application_only_flag,
creation_sign, post_to_gl
INTO l_name, l_type, l_subseq_trx_type_id,
l_allow_overapplication_flag ,
l_natural_application_flag,
l_creation_sign, l_post_to_gl
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = p_trx_type_id;
SELECT gl_date
INTO l_orig_gl_date
FROM ar_transaction_history h
WHERE h.customer_trx_id = p_customer_trx_id
AND h.event in ('COMPLETED','ACCEPTED')
AND h.transaction_history_id =
(SELECT max(transaction_history_id)
FROM ar_transaction_history h2
WHERE h2.customer_trx_id = p_customer_trx_id
AND h2.event IN ('COMPLETED','ACCEPTED'));
SELECT gl_date
INTO l_orig_gl_date
FROM ar_transaction_history h
WHERE h.customer_trx_id = p_customer_trx_id
AND h.current_record_flag = 'Y';
SELECT NVL(SUM(extended_amount),0)
INTO l_total
FROM ra_customer_trx_lines
WHERE line_type = p_line_type
AND customer_trx_id = p_customer_trx_id;
SELECT nvl(sum(nvl(amount_applied,0) + nvl(earned_discount_taken,0) + nvl(unearned_discount_taken,0)), 0)
INTO p_amount_applied
FROM ar_receivable_applications
WHERE applied_payment_schedule_id = p_applied_payment_schedule_id
AND status = 'APP'
AND nvl(confirmed_flag,'Y') = 'Y'
AND apply_date <= p_as_of_date;
SELECT nvl(sum(amount_applied),0)
INTO p_cm_amount_applied
FROM ar_receivable_applications
WHERE payment_schedule_id = p_applied_payment_schedule_id
AND apply_date <= p_as_of_date;
SELECT nvl(sum(amount),0)
INTO p_adj_amount_applied
FROM ar_adjustments
WHERE payment_schedule_id = p_applied_payment_schedule_id
AND status = 'A'
AND apply_date <= p_as_of_date;
SELECT amount_due_original
INTO p_amt_due_original
FROM ar_payment_schedules
WHERE payment_schedule_id = p_applied_payment_schedule_id;
select decode(p_buck_line_typ,
'DISPUTE_ONLY',decode(nvl(p_amt_in_disp,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(p_amt_adj_pen,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(p_amt_in_disp,0),
0,decode(nvl(p_amt_adj_pen,0),0,0,1),
1),
decode( greatest(p_days_from,
ceil(p_as_of-p_due_date)),
least(p_days_to,
ceil(p_as_of-p_due_date)),1,
0)
* decode(nvl(p_amt_in_disp,0), 0, 1,
decode(p_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(p_amt_adj_pen,0), 0, 1,
decode(p_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1)))
into bucket_amount
from dual;
only one row will ever be returned by the select.
*/
FUNCTION get_bill_id(p_site_use_id IN NUMBER)
RETURN NUMBER IS
l_site_with_profile NUMBER;
SELECT distinct site_use_id
INTO l_site_with_profile
FROM hz_customer_profiles
WHERE site_use_id = p_site_use_id;
select site_use_id
into l_site_with_profile
from hz_customer_profiles
where site_use_id in ( select site_use_id
from hz_cust_site_uses
where cust_acct_site_id =
( SELECT cust_acct_site_id
FROM hz_cust_site_uses
WHERE site_use_id = p_site_use_id)
and status = 'A'
and site_use_code in ('BILL_TO','DUN','STMTS'));
select site_use_id
into l_site_with_profile
from hz_customer_profiles
where site_use_id in ( select site_use_id
from hz_cust_site_uses
where cust_acct_site_id =
( SELECT cust_acct_site_id
FROM hz_cust_site_uses
WHERE site_use_id = p_site_use_id)
and status = 'A'
and site_use_code = 'BILL_TO');
SELECT statement_cycle_id
INTO l_cycle_id
FROM hz_customer_profiles
WHERE site_use_id = arpt_sql_func_util.get_bill_id(p_site_use_id);
SELECT nvl(send_statements ,'N')
INTO l_send
FROM hz_customer_profiles
WHERE site_use_id = arpt_sql_func_util.get_bill_id(p_site_use_id);
SELECT nvl(credit_balance_statements ,'N')
INTO l_cred_bal
FROM hz_customer_profiles
WHERE site_use_id = arpt_sql_func_util.get_bill_id(p_site_use_id);
SELECT name
INTO l_trx_name
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = p_trx_type_id
AND NVL(org_id,-99) = NVL(p_org_id,-99);
select type, name
into l_type, l_name
from ar_receivables_trx
where receivables_trx_id = p_rec_trx_id;
select type, name
into l_type, l_name
from ar_receivables_trx_all
where receivables_trx_id = p_rec_trx_id;
select decode(rc.creation_method_code,'AUTOMATIC',nvl(rc.bill_of_exchange_flag,'N'),'N')
into boe_flag
from ar_receipt_classes rc,
ar_receipt_methods rm
where rm.receipt_method_id = p_receipt_method_id
and rm.receipt_class_id = rc.receipt_class_id;
Select decode(p_application_type, 'CASH',
decode(p_status,'APP',
decode(substr(p_ard_source_type,1,5),
'EXCH_',decode(p_cr_currency_code,
arp_global.functional_currency,p_inv_currency_code,
p_cr_currency_code),
'CURR_',decode(p_cr_currency_code,
arp_global.functional_currency,p_inv_currency_Code,
p_cr_currency_code),
p_inv_currency_code),
p_cr_currency_code),
'CM',p_inv_currency_code)
into l_curr from dual;