[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cp.user_concurrent_program_name
INTO l_report_name
FROM FND_CONCURRENT_PROGRAMS_VL cp,
FND_CONCURRENT_REQUESTS cr
WHERE cr.request_id = P_CONC_REQUEST_ID
AND cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id;
SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
-AMOUNT_APPLIED, 0)),
0) on_account,
nvl(max(decode(ar_receivable_applications.status, 'ACC',
decode(ar_cash_receipts.currency_code, functional_currency, ' ',
decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
' ')), ' ') account_convert,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
-amount_applied, 0)),
0) unapplied,
nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
decode(ar_cash_receipts.currency_code, functional_currency, ' ',
decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
' ')), ' ') unapp_convert
into l_aging_on_account,
l_aging_convert_on_account,
l_aging_unapplied,
l_aging_convert_unapplied
from ar_receivable_applications,
ar_cash_receipts
where ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = customer_id
and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
and ar_cash_receipts.currency_code = Currency_Bucket
and ar_receivable_applications.gl_date <= sysdate
and nvl(ar_receivable_applications.confirmed_flag,'Y') = 'Y'
;
select nvl(sum(amount_due_remaining ), 0),
nvl(max(decode(invoice_currency_code,
functional_currency, ' ',
decode(exchange_rate,
NULL, '*', ' '))), ' ')
into l_aging_credit,
l_aging_convert_credit
from ar_payment_schedules
where customer_id = customer_id
and customer_site_use_id = site_use_id
and invoice_currency_code = Currency_Bucket
and class = 'CM'
and gl_date <= sysdate
;
SELECT NVL(SUM(DECODE(IN_COLLECTION, 'Y',
AMOUNT_DUE_REMAINING, 0)),0) COLLECT,
NVL(MAX(DECODE(IN_COLLECTION, 'Y',
DECODE(INVOICE_CURRENCY_CODE, functional_currency, ' ',
DECODE(EXCHANGE_RATE, NULL, '*', ' ')), ' ')), ' ') CCONV
INTO l_aging_in_collection,
l_aging_convert_collection
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND CUSTOMER_SITE_USE_ID = site_use_id
AND INVOICE_CURRENCY_CODE = Currency_Bucket
AND STATUS = 'OP'
;
SELECT NVL(AMOUNT_DUE_ORIGINAL, 0),
DECODE(INVOICE_CURRENCY_CODE, functional_currency, ' ',
DECODE(EXCHANGE_RATE, NULL, '*', ' ')),
TRX_DATE
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_ID = CUSTOMER_ID
AND CUSTOMER_SITE_USE_ID = SITE_USE_ID
AND INVOICE_CURRENCY_CODE = Currency_Bucket
AND CLASS NOT IN ('CM', 'PMT')
ORDER BY AMOUNT_DUE_ORIGINAL DESC,
TRX_DATE DESC
;
SELECT H.CREDIT_LIMIT,
H.LAST_UPDATE_DATE
FROM AR_CREDIT_HISTORIES H
WHERE H.CUSTOMER_ID = CUSTOMER_ID_1
AND (H.SITE_USE_ID = site_use_id_1
OR
to_char(site_use_id) is null
OR
( H.SITE_USE_ID IS NULL
AND NOT EXISTS (SELECT 1
FROM AR_CREDIT_HISTORIES H2
WHERE H2.SITE_USE_ID =
site_use_id_1
AND H2.CUSTOMER_ID =
customer_id_1
)
)
)
AND H.CREDIT_LIMIT IS NOT NULL
ORDER BY H.CREDIT_LIMIT DESC,
H.LAST_UPDATE_DATE DESC
;
select overall_credit_limit,
last_update_date
from hz_cust_profile_amts
where cust_account_profile_id = c_customer_profile_id
and currency_code = CP_limit_currency;
select nvl(sum(amount_due_original),0),
max(decode(invoice_currency_code,functional_currency, ' ',
decode(exchange_rate, NULL, '*', ' '))),
count(amount_due_original),
nvl(sum(receivables_charges_charged),0),
max(decode(invoice_currency_code,functional_currency, ' ',
decode(exchange_rate, NULL, '*', ' ')))
into l_ytd_sales_amount,
l_ytd_convert_sales,
l_ytd_sales_count,
d_ytd_finance_charge_amount,
d_ytd_finance_charge_convert
from ar_payment_schedules
--where ar_payment_schedules.customer_id = customer_id
where ar_payment_schedules.customer_id = customer_id_1
and customer_site_use_id = site_use_id
and invoice_currency_code = Currency_Bucket
and ar_payment_schedules.trx_date between
add_months(sysdate, -12) and sysdate
and ar_payment_schedules.class not in ('CM', 'PMT')
;
select nvl(sum(-amount), 0) payment_amount,
nvl(max(decode(currency_code, functional_currency, ' ',
decode(exchange_rate, NULL, '*', ' '))), ' '),
nvl(count(amount),0) payment_count
into l_ytd_payment_amount,
l_ytd_convert_payment,
l_ytd_payment_count
from ar_cash_receipts
--where ar_cash_receipts.pay_from_customer = customer_id
where ar_cash_receipts.pay_from_customer = customer_id_1
and customer_site_use_id = site_use_id
and currency_code = Currency_Bucket
and ar_cash_receipts.creation_date between
add_months(sysdate, -12) and sysdate
and ar_cash_receipts.status <> 'REV'
and nvl(ar_cash_receipts.confirmed_flag,'Y') = 'Y'
;
select nvl(sum(amount_due_original ), 0),
nvl(max(decode(ar_payment_schedules.invoice_currency_code,
functional_currency, ' ',
decode(ar_payment_schedules.exchange_rate,
NULL, '*', ' '))), ' '),
count(customer_trx_id)
into l_ytd_credit_amount,
l_ytd_convert_credit,
l_ytd_credit_count
from ar_payment_schedules
where ar_payment_schedules.trx_date between add_months(sysdate,-12) and
sysdate
--and ar_payment_schedules.customer_id = customer_id
and ar_payment_schedules.customer_id = customer_id_1
and customer_site_use_id = site_use_id
and invoice_currency_code = Currency_Bucket
and ar_payment_schedules.class = 'CM'
;
select nvl(sum(amount), 0),
nvl(max(decode(ar_payment_schedules.invoice_currency_code,
functional_currency, ' ',
decode(ar_payment_schedules.exchange_rate,
NULL, '*', ' '))), ' '),
count(adjustment_id)
into l_ytd_finance_charge_amount,
l_ytd_convert_finance_charge,
l_ytd_finance_charge_count
from ar_adjustments,
ar_receivables_trx,
ar_payment_schedules
where ar_adjustments.payment_schedule_id=
ar_payment_schedules.payment_schedule_id
and ar_payment_schedules.customer_site_use_id = site_use_id
and ar_payment_schedules.invoice_currency_code = Currency_Bucket
and ar_adjustments.receivables_trx_id=ar_receivables_trx.receivables_trx_id
and ar_receivables_trx.type='FINCHRG'
and ar_adjustments.apply_date between add_months(sysdate,-12) and sysdate
and nvl(ar_adjustments.postable,'Y')='Y'
--and ar_payment_schedules.customer_id=customer_id
and ar_payment_schedules.customer_id=customer_id_1
;
select nvl(sum(ar_adjustments.amount), 0),
nvl(max(decode(ar_payment_schedules.invoice_currency_code,
functional_currency, ' ',
decode(ar_payment_schedules.exchange_rate,
NULL, '*', ' '))), ' ')
into l_ytd_writeoff_amount,
l_ytd_convert_writeoff
from ar_adjustments,
ar_lookups lk,
ar_payment_schedules
where ar_adjustments.reason_code
= lk.lookup_code(+)
and lk.lookup_code(+) = 'WRITE OFF'
and lk.lookup_type(+) = 'ADJUST_REASON'
--and ar_payment_schedules.customer_id = customer_id
and ar_payment_schedules.customer_id = customer_id_1
and ar_payment_schedules.customer_site_use_id = site_use_id
and ar_payment_schedules.invoice_currency_code = Currency_Bucket
and ar_adjustments.payment_schedule_id =
ar_payment_schedules.payment_schedule_id
and ar_adjustments.apply_date between
add_months(sysdate, -12) and sysdate
and nvl(ar_adjustments.postable, 'Y') = 'Y'
;
select nvl(sum(ar_receivable_applications.earned_discount_taken ), 0) earned,
nvl(max(decode(nvl(ar_receivable_applications.earned_discount_taken, 0),
0, ' ',
decode(ar_payment_schedules.invoice_currency_code,
functional_currency, ' ',
decode(ar_payment_schedules.exchange_rate,
NULL, '*', ' ')))), ' ') earned_cvt,
nvl(sum(ar_receivable_applications.unearned_discount_taken), 0) unearned,
nvl(max(decode(nvl(ar_receivable_applications.unearned_discount_taken, 0),
0, ' ',
decode(ar_payment_schedules.invoice_currency_code,
functional_currency, ' ',
decode(ar_payment_schedules.exchange_rate,
NULL, '*', ' ')))), ' ') unearned_cvt,
decode(count(ar_receivable_applications.apply_date), 0, 0,
round(sum(ar_receivable_applications.apply_date -
ar_payment_schedules.trx_date) /
count(ar_receivable_applications.apply_date))) avgdays,
decode(count(ar_receivable_applications.apply_date), 0, 0,
round(sum(ar_receivable_applications.apply_date -
ar_payment_schedules.due_date) /
count(ar_receivable_applications.apply_date))) avgdayslate,
nvl(sum(decode(sign(ar_receivable_applications.apply_date -
ar_payment_schedules.due_date),
1, 1, 0)), 0) newlate,
nvl(sum( decode(sign(ar_receivable_applications.apply_date -
ar_payment_schedules.due_date),
1, 0, 1)), 0) newontime
into l_ytd_earned_discount_amount,
l_ytd_convert_earned_discount,
l_ytd_unearned_discount_amount,
l_ytd_conv_unearned_discount,
l_ytd_average_payment_days,
l_ytd_average_days_late,
l_ytd_late_payments_count,
l_ytd_on_time_payments_count
from ar_receivable_applications, ar_payment_schedules
where ar_receivable_applications.applied_payment_schedule_id =
ar_payment_schedules.payment_schedule_id
and ar_payment_schedules.customer_id = customer_id
and ar_payment_schedules.customer_site_use_id = site_use_id
and ar_payment_schedules.invoice_currency_code = Currency_Bucket
and ar_receivable_applications.apply_date between
add_months(sysdate, -12) and sysdate
and ar_receivable_applications.status = 'APP'
and ar_receivable_applications.display = 'Y'
and nvl(ar_payment_schedules.receipt_confirmed_flag,'Y') = 'Y'
;
select nvl(sum(ROUND(decode(ar_cash_receipts.status,'NSF', acrh.acctd_amount, 'STOP', acrh.acctd_amount, 0)
, 2)), 0) nsf_amount,
nvl(max(decode(currency_code, functional_currency, ' ',
decode(ar_cash_receipts.exchange_rate, NULL, '*', ' '))), ' '),
nvl(sum(decode(ar_cash_receipts.status,'NSF', 1, 'STOP', 1, 0)), 0) nsf_count
into l_ytd_nsf_amount,
l_ytd_convert_nsf,
l_ytd_nsf_count
from ar_cash_receipts,
ar_cash_receipt_history acrh
--where ar_cash_receipts.pay_from_customer = customer_id
where ar_cash_receipts.pay_from_customer = customer_id_1
and ar_cash_receipts.cash_receipt_id = acrh.cash_receipt_id
and acrh.first_posted_record_flag = 'Y'
and customer_site_use_id = site_use_id
and currency_code = Currency_Bucket
and ar_cash_receipts.reversal_date between
add_months(sysdate, -12) and sysdate
;
SELECT min(name)
INTO l_collector_min
FROM ar_collectors;
SELECT max(name)
into l_collector_max
from ar_collectors;
select count(*)
into ct_prof
from hz_cust_profile_amts cpa,
hz_customer_profiles cp
where cp.cust_account_id = customer_id
-- and cp.site_use_id = site_use_id
and cp.site_use_id = site_use_id_1
and cp.cust_account_profile_id = cpa.cust_account_profile_id;
SELECT INITCAP(YES.MEANING) yes,
INITCAP(NO.MEANING) no
INTO yes,
no
FROM AR_LOOKUPS YES,
AR_LOOKUPS NO
WHERE YES.LOOKUP_TYPE = 'YES/NO' AND
YES.LOOKUP_CODE = 'Y' AND
NO.LOOKUP_TYPE = 'YES/NO' AND
NO.LOOKUP_CODE = 'N';
Select
to_char(nvl(cp.tolerance, 0), '990') || '%',
substr(nvl(cp.credit_rating, rp_na_upper),1,30),
cp.risk_code,
lk.meaning,
cp.account_status,
substr(nvl(term.name, rp_none),1,20),
decode(cp.dunning_letters, 'Y', no, yes),
coll.name,
cp.cust_account_profile_id
into
l_cred_summ_limit_tolerance,
l_cred_summ_credit_rating,
l_cred_summ_risk_code,
l_cred_summ_credit_hold,
l_cred_summ_account_status,
l_cred_summ_terms,
l_cred_summ_exempt_dun,
l_cred_summ_collector,
l_customer_profile_id
from hz_customer_profiles cp,
ar_collectors coll,
ar_lookups lk,
ra_terms term
where cp.collector_id = coll.collector_id
and cp.standard_terms = term.term_id (+)
and cp.cust_account_id = customer_id
and cp.site_use_id is null
and coll.name between nvl(p_collector_low,p_collector_min)
and nvl(p_collector_high,p_collector_max)
and nvl(cp.credit_hold,'N') = lk.lookup_code
and lk.lookup_type = 'YES/NO' ;
Select
to_char(nvl(cp.tolerance, 0), '990') || '%',
substr(nvl(cp.credit_rating,rp_na_upper),1,30),
cp.risk_code,
lk.meaning,
cp.account_status,
substr(nvl(term.name, rp_none),1,20),
decode(cp.dunning_letters, 'Y', no, yes),
coll.name,
cp.cust_account_profile_id
into
l_cred_summ_limit_tolerance,
l_cred_summ_credit_rating,
l_cred_summ_risk_code,
l_cred_summ_credit_hold,
l_cred_summ_account_status,
l_cred_summ_terms,
l_cred_summ_exempt_dun,
l_cred_summ_collector,
l_customer_profile_id
from hz_customer_profiles cp,
ar_collectors coll,
ar_lookups lk,
ra_terms term
where cp.collector_id = coll.collector_id
and cp.standard_terms = term.term_id (+)
and cp.cust_account_id = customer_id
--and cp.site_use_id = site_use_id
and cp.site_use_id = site_use_id_1
and coll.name between nvl(p_collector_low,p_collector_min)
and nvl(p_collector_high,p_collector_max)
and nvl(cp.credit_hold,'N') = lk.lookup_code
and lk.lookup_type = 'YES/NO'
;
SELECT invoice_currency_code, NVL(SUM(AMOUNT_DUE_REMAINING), 0) ammount_due,exchange_rate_type
from ar_payment_schedules ps
where ps.customer_id = qc_customer
and ps.customer_site_use_id = NVL(qc_site,ps.customer_site_use_id)
and ps.status = 'OP'
and ps.class not in ('CM', 'PMT')
group by ps.invoice_currency_code,exchange_rate_type;
SELECT
NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
AMOUNT_APPLIED, 0)),
0) on_account,
nvl(max(decode(ar_receivable_applications.status, 'ACC',
decode(ar_cash_receipts.currency_code,functional_currency, ' ',
decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
' ')), ' ') account_convert,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
amount_applied, 0)),
0) unapplied,
nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
decode(ar_cash_receipts.currency_code,functional_currency, ' ',
decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
' ')), ' ') unapp_convert
into
l_aging_on_account_profile,
l_aging_conv_on_ac_profile,
l_aging_unapplied_profile,
l_aging_conv_unap_prof
from
ar_receivable_applications,
ar_cash_receipts
where
ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = qc_customer
and ar_cash_receipts.CUSTOMER_SITE_USE_ID =
NVL(qc_site, ar_cash_receipts.customer_site_use_id)
and ar_cash_receipts.currency_code = currency_credit
and ar_receivable_applications.gl_date <= sysdate;
SELECT
NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
AMOUNT_APPLIED, 0)), 0) on_account,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
amount_applied, 0)), 0) unapplied
into
l_aging_on_account_profile,
l_aging_unapplied_profile
from
ar_receivable_applications,
ar_cash_receipts
where
ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = qc_customer
and ar_cash_receipts.CUSTOMER_SITE_USE_ID =
NVL(qc_site, ar_cash_receipts.customer_site_use_id)
and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code
and ar_receivable_applications.gl_date <= sysdate;
SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
RA_CUST_TRX_TYPES.NAME,
AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,functional_currency, ' ',
DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
RA_CUSTOMER_TRX.TRX_DATE,
TO_CHAR(ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE))
FROM RA_CUST_TRX_TYPES,
AR_PAYMENT_SCHEDULES,
RA_CUSTOMER_TRX
WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
AND AR_PAYMENT_SCHEDULES.CLASS || '' = 'INV'
ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
;
SELECT TRX1.TRX_NUMBER,
TYPES.NAME,
PS.INVOICE_CURRENCY_CODE,
TRX1.TRX_DATE,
ROUND(TRUNC(SYSDATE) - TRX1.TRX_DATE),
TRX2.CUSTOMER_TRX_ID,
TRX1.CUSTOMER_TRX_ID
FROM RA_CUST_TRX_TYPES TYPES, RA_CUSTOMER_TRX TRX1, AR_PAYMENT_SCHEDULES PS,
RA_CUSTOMER_TRX TRX2, AR_RECEIVABLE_APPLICATIONS APP
WHERE TRX1.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID
AND TRX1.BILL_TO_CUSTOMER_ID = CUSTOMER_ID_1
AND APP.APPLIED_CUSTOMER_TRX_ID = TRX2.CUSTOMER_TRX_ID (+)
AND APP.CUSTOMER_TRX_ID (+) = TRX1.CUSTOMER_TRX_ID
AND PS.CUSTOMER_TRX_ID = TRX1.CUSTOMER_TRX_ID
AND PS.CUSTOMER_SITE_USE_ID = site_use_id_1
AND PS.CLASS = 'CM'
ORDER BY TRX1.TRX_DATE DESC,
TRX1.CUSTOMER_TRX_ID DESC
;
SELECT TRX_NUMBER
INTO l_last_cm_rel_invoice
FROM RA_CUSTOMER_TRX
WHERE CUSTOMER_TRX_ID = c_last_cm_prev_trx
AND BILL_TO_SITE_USE_ID = SITE_USE_ID_1
;
SELECT NVL( SUM( P.AMOUNT_DUE_ORIGINAL) , 0),
MAX(DECODE(P.INVOICE_CURRENCY_CODE,functional_currency, ' ',
DECODE(P.EXCHANGE_RATE,NULL, '*', ' ')))
INTO l_last_cm_amount,
l_last_cm_converted
FROM AR_PAYMENT_SCHEDULES P
WHERE P.CUSTOMER_TRX_ID = c_last_cm_id
AND P.CUSTOMER_SITE_USE_ID = SITE_USE_ID_1
;
SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
RA_CUST_TRX_TYPES.NAME,
AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,functional_currency, ' ',
DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
RA_CUSTOMER_TRX.TRX_DATE,
ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE)
FROM RA_CUST_TRX_TYPES,
RA_CUSTOMER_TRX,
AR_PAYMENT_SCHEDULES
WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
AND AR_PAYMENT_SCHEDULES.CLASS = 'GUAR'
ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
;
SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
RA_CUST_TRX_TYPES.NAME,
AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,functional_currency, ' ',
DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
RA_CUSTOMER_TRX.TRX_DATE,
ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE)
FROM RA_CUST_TRX_TYPES,
RA_CUSTOMER_TRX,
AR_PAYMENT_SCHEDULES
WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
AND AR_PAYMENT_SCHEDULES.CLASS = 'DEP'
ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
;
SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
RA_CUST_TRX_TYPES.NAME,
AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,functional_currency, ' ',
DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
RA_CUSTOMER_TRX.TRX_DATE,
ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE)
FROM RA_CUST_TRX_TYPES,
RA_CUSTOMER_TRX,
AR_PAYMENT_SCHEDULES
WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
AND AR_PAYMENT_SCHEDULES.CLASS = 'DM'
ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
;
SELECT RA_CUSTOMER_TRX.TRX_NUMBER,
RA_CUST_TRX_TYPES.NAME,
AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL,
DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE, functional_currency, ' ',
DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
RA_CUSTOMER_TRX.TRX_DATE,
ROUND(TRUNC(SYSDATE) - RA_CUSTOMER_TRX.TRX_DATE)
FROM RA_CUST_TRX_TYPES,
RA_CUSTOMER_TRX,
AR_PAYMENT_SCHEDULES
WHERE AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
AND RA_CUSTOMER_TRX.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID
AND RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID = CUSTOMER_ID
AND AR_PAYMENT_SCHEDULES.Customer_site_use_id = site_use_id
AND AR_PAYMENT_SCHEDULES.CLASS = 'CB'
ORDER BY RA_CUSTOMER_TRX.TRX_DATE DESC,
RA_CUSTOMER_TRX.CUSTOMER_TRX_ID DESC
;
SELECT AR_CASH_RECEIPTS.RECEIPT_NUMBER,
AR_LOOKUPS.MEANING,
AR_CASH_RECEIPTS.CURRENCY_CODE,
AR_CASH_RECEIPTS.AMOUNT,
DECODE(AR_CASH_RECEIPTS.CURRENCY_CODE, functional_currency, ' ',
DECODE(AR_CASH_RECEIPTS.EXCHANGE_RATE, NULL, '*', ' ')),
CRH.GL_DATE,
ROUND(TRUNC(SYSDATE) - CRH.GL_DATE),
RA_CUSTOMER_TRX.TRX_NUMBER
FROM AR_LOOKUPS,
AR_CASH_RECEIPTS,
AR_CASH_RECEIPT_HISTORY CRH,
AR_RECEIVABLE_APPLICATIONS,
RA_CUSTOMER_TRX
WHERE NVL(AR_CASH_RECEIPTS.TYPE, 'CASH') = AR_LOOKUPS.LOOKUP_CODE
AND AR_LOOKUPS.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND AR_CASH_RECEIPTS.CUSTOMER_SITE_USE_ID = site_use_id
AND AR_CASH_RECEIPTS.CASH_RECEIPT_ID =
AR_RECEIVABLE_APPLICATIONS.CASH_RECEIPT_ID
AND AR_CASH_RECEIPTS.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.FIRST_POSTED_RECORD_FLAG = 'Y'
AND AR_RECEIVABLE_APPLICATIONS.APPLIED_CUSTOMER_TRX_ID =
RA_CUSTOMER_TRX.CUSTOMER_TRX_ID (+)
ORDER BY AR_CASH_RECEIPTS.CREATION_DATE DESC,
AR_CASH_RECEIPTS.CASH_RECEIPT_ID DESC,
AR_RECEIVABLE_APPLICATIONS.CREATION_DATE DESC
;
SELECT LK.MEANING,
AR_PAYMENT_SCHEDULES.TRX_NUMBER,
ar_payment_schedules.invoice_currency_code,
AR_ADJUSTMENTS.AMOUNT,
DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE, functional_currency, ' ',
DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
AR_ADJUSTMENTS.APPLY_DATE,
ROUND(TRUNC(SYSDATE) - AR_ADJUSTMENTS.APPLY_DATE)
FROM AR_ADJUSTMENTS,
AR_LOOKUPS LK,
AR_PAYMENT_SCHEDULES
WHERE AR_ADJUSTMENTS.REASON_CODE = LK.LOOKUP_CODE (+)
AND LK.LOOKUP_TYPE = 'ADJUST_REASON'
AND AR_ADJUSTMENTS.PAYMENT_SCHEDULE_ID =
AR_PAYMENT_SCHEDULES.PAYMENT_SCHEDULE_ID
AND NVL(AR_ADJUSTMENTS.POSTABLE, 'Y') = 'Y'
AND AR_PAYMENT_SCHEDULES.CUSTOMER_ID = CUSTOMER_ID
AND AR_PAYMENT_SCHEDULES.CUSTOMER_SITE_USE_ID = site_use_id
ORDER BY AR_ADJUSTMENTS.CREATION_DATE DESC,
AR_ADJUSTMENTS.ADJUSTMENT_ID DESC
;
SELECT LK.LOOKUP_CODE,
AR_PAYMENT_SCHEDULES.TRX_NUMBER,
AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE,
AR_ADJUSTMENTS.AMOUNT,
DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE, functional_currency, ' ',
DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' ')),
AR_ADJUSTMENTS.APPLY_DATE,
ROUND(TRUNC(SYSDATE) - AR_ADJUSTMENTS.APPLY_DATE)
FROM AR_ADJUSTMENTS,
AR_LOOKUPS LK,
AR_PAYMENT_SCHEDULES
WHERE AR_ADJUSTMENTS.REASON_CODE
= LK.LOOKUP_CODE(+)
AND AR_ADJUSTMENTS.CUSTOMER_TRX_ID = AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID
AND NVL(AR_ADJUSTMENTS.POSTABLE, 'Y') = 'Y'
AND LK.LOOKUP_CODE(+) = 'WRITE OFF'
AND LK.LOOKUP_TYPE(+) = 'ADJUST_REASON'
AND AR_PAYMENT_SCHEDULES.CUSTOMER_ID = CUSTOMER_ID
AND AR_PAYMENT_SCHEDULES.CUSTOMER_SITE_USE_ID = site_use_id
ORDER BY AR_ADJUSTMENTS.CREATION_DATE DESC,
AR_ADJUSTMENTS.ADJUSTMENT_ID DESC
;
SELECT AR_STATEMENT_CYCLES.NAME,
AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
TRUNC(TRUNC(SYSDATE) - AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
FROM HZ_CUSTOMER_PROFILES, AR_STATEMENT_CYCLES,
AR_STATEMENT_CYCLE_DATES
WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
AND HZ_CUSTOMER_PROFILES.site_use_id = site_use_id
ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE DESC,
AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID DESC ;
SELECT AR_STATEMENT_CYCLES.NAME,
AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
TRUNC(TRUNC(SYSDATE) - AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
FROM HZ_CUSTOMER_PROFILES, AR_STATEMENT_CYCLES,
AR_STATEMENT_CYCLE_DATES
WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
AND AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID =
AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'Y'
AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
AND HZ_CUSTOMER_PROFILES.site_use_id is null
ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE DESC,
AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID DESC
;
SELECT MIN(AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE)
INTO l_last_stmnt_next_trx_date
FROM HZ_CUSTOMER_PROFILES,
AR_STATEMENT_CYCLE_DATES
WHERE HZ_CUSTOMER_PROFILES.STATEMENT_CYCLE_ID =
AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_ID
AND AR_STATEMENT_CYCLE_DATES.PRINTED = 'N'
AND AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE > to_date(c_last_st_date,'DD-MM-YYYY')
AND HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_ID = CUSTOMER_ID
AND HZ_CUSTOMER_PROFILES.SITE_USE_ID IS NULL
ORDER BY AR_STATEMENT_CYCLE_DATES.STATEMENT_DATE,
AR_STATEMENT_CYCLE_DATES.STATEMENT_CYCLE_DATE_ID
;
SELECT MAX(creation_date),
TRUNC(SYSDATE) - MAX(creation_date)
FROM iex_dunnings
--WHERE (dunning_level = 'ACCOUNT' AND dunning_object_id = CUSTOMER_ID)
WHERE (dunning_level = 'ACCOUNT' AND dunning_object_id = CUSTOMER_ID_1)
--OR (dunning_level = 'BILL_TO' AND dunning_object_id = site_use_id);
SELECT ROUND(SUM(CPS.AMOUNT_DUE_REMAINING + CPS.AMOUNT_ACCRUE +
CPS.AMOUNT_UNACCRUE), 2),
CORR.CORRESPONDENCE_TYPE,
ps.invoice_currency_code,
MAX(CORR.CORRESPONDENCE_DATE),
TRUNC(SYSDATE) - MAX(CORR.CORRESPONDENCE_DATE)
FROM ar_payment_schedules ps,
ar_correspondence_pay_sched cps,
AR_DUNNING_LETTERS DUNN,
AR_CORRESPONDENCES CORR
WHERE CORR.REFERENCE1 = DUNN.DUNNING_LETTER_ID
AND CORR.CORRESPONDENCE_ID = CPS.CORRESPONDENCE_ID
--and CORR.site_use_id = site_use_id
and CORR.site_use_id = site_use_id_1
and cps.payment_schedule_id = ps.payment_schedule_id
AND CORR.CORRESPONDENCE_TYPE = 'DUNNING'
--AND CORR.CUSTOMER_ID = CUSTOMER_ID
AND CORR.CUSTOMER_ID = CUSTOMER_ID_1
GROUP BY ps.invoice_currency_code, CORR.CORRESPONDENCE_TYPE,
CPS.AMOUNT_DUE_REMAINING,
CPS.AMOUNT_ACCRUE, DUNN.LETTER_NAME, CORR.CORRESPONDENCE_DATE
ORDER BY CORR.CORRESPONDENCE_DATE DESC ;
SELECT AR_CASH_RECEIPTS.RECEIPT_NUMBER,
AR_CASH_RECEIPTS.STATUS,
currency_code,
AR_CASH_RECEIPTS.AMOUNT,
DECODE(AR_CASH_RECEIPTS.CURRENCY_CODE,functional_currency, ' ',
DECODE(AR_CASH_RECEIPTS.EXCHANGE_RATE, NULL, '*', ' ')),
AR_CASH_RECEIPTS.REVERSAL_DATE,
ROUND(TRUNC(SYSDATE) - AR_CASH_RECEIPTS.REVERSAL_DATE)
FROM AR_CASH_RECEIPTS
WHERE AR_CASH_RECEIPTS.STATUS IN ('NSF','STOP')
AND AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER = CUSTOMER_ID
AND AR_CASH_RECEIPTS.CUSTOMER_SITE_USE_ID = site_use_id
ORDER BY AR_CASH_RECEIPTS.REVERSAL_DATE DESC,
AR_CASH_RECEIPTS.CASH_RECEIPT_ID DESC
;
SELECT cont_point.phone_area_code||'-' ||
RTRIM(RPAD(decode(cont_point.contact_point_type,
'TLX', cont_point.telex_number,
cont_point.phone_number),15)),
AR_PAYMENT_SCHEDULES.TRX_NUMBER,
AR_PAYMENT_SCHEDULES.invoice_currency_code,
AR_CALL_ACTIONS.ACTION_AMOUNT,
DECODE(AR_CALL_ACTIONS.ACTION_AMOUNT, NULL, NULL,
DECODE(AR_PAYMENT_SCHEDULES.INVOICE_CURRENCY_CODE, functional_currency, ' ',
DECODE(AR_PAYMENT_SCHEDULES.EXCHANGE_RATE, NULL, '*', ' '))),
AR_CUSTOMER_CALL_TOPICS.CALL_DATE,
ROUND(TRUNC(SYSDATE) - AR_CUSTOMER_CALL_TOPICS.CALL_DATE)
FROM hz_contact_points cont_point,
hz_cust_account_roles car, AR_LOOKUPS LKUPS,
AR_PAYMENT_SCHEDULES, AR_CUSTOMER_CALL_TOPICS, AR_CALL_ACTIONS
--WHERE AR_CUSTOMER_CALL_TOPICS.CUSTOMER_ID = CUSTOMER_ID
WHERE AR_CUSTOMER_CALL_TOPICS.CUSTOMER_ID = CUSTOMER_ID_1
--and AR_CUSTOMER_CALL_TOPICS.site_use_id(+) = site_use_id
and AR_CUSTOMER_CALL_TOPICS.site_use_id(+) = site_use_id_1
AND AR_CUSTOMER_CALL_TOPICS.PHONE_ID = cont_point.contact_point_id
AND AR_CUSTOMER_CALL_TOPICS.CONTACT_ID = car.cust_account_role_id
AND car.party_id = cont_point.owner_table_id
AND cont_point.owner_table_name = 'HZ_PARTIES'
AND cont_point.contact_point_type not in ('EDI','EMAIL','WEB')
AND AR_CUSTOMER_CALL_TOPICS.CUSTOMER_TRX_ID =
AR_PAYMENT_SCHEDULES.CUSTOMER_TRX_ID (+)
AND AR_CUSTOMER_CALL_TOPICS.CUSTOMER_CALL_TOPIC_ID =
AR_CALL_ACTIONS.CUSTOMER_CALL_TOPIC_ID (+)
AND AR_CUSTOMER_CALL_TOPICS.FOLLOW_UP_ACTION =
LKUPS.LOOKUP_CODE (+)
ORDER BY AR_CUSTOMER_CALL_TOPICS.CALL_DATE DESC,
AR_CUSTOMER_CALL_TOPICS.CUSTOMER_CALL_ID DESC
;
SELECT AR_CREDIT_HISTORIES.CREDIT_LIMIT,
AR_CREDIT_HISTORIES.HOLD_DATE,
ROUND(TRUNC(SYSDATE) - AR_CREDIT_HISTORIES.HOLD_DATE)
FROM AR_CREDIT_HISTORIES
--WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID
--and (ar_credit_histories.site_use_id = site_use_id
WHERE AR_CREDIT_HISTORIES.CUSTOMER_ID = CUSTOMER_ID_1
and (ar_credit_histories.site_use_id = site_use_id_1
or
site_use_id_1 is null
or
( ar_credit_histories.site_use_id is null
and not exists (select 1
from ar_credit_histories h2
-- where h2.site_use_id = site_use_id
where h2.site_use_id = site_use_id_1
-- and h2.customer_id =
-- customer_id
and h2.customer_id =
customer_id_1
)
)
)
AND AR_CREDIT_HISTORIES.ON_HOLD = 'Y'
ORDER BY AR_CREDIT_HISTORIES.HOLD_DATE DESC,
AR_CREDIT_HISTORIES.CREDIT_HISTORY_ID DESC
;
select meaning
into w_meaning
from fnd_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code ;
select decode( party.person_pre_name_adjunct , null,
substrb(party.person_first_name,1,40) || ' ' ||
substrb(party.person_last_name,1,50),
ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CONTACT_TITLE',ORG_CONT.TITLE)
|| ' ' || substrb(party.person_first_name,1,40) || ' '||
substrb(party.person_last_name,1,50)),
cont_point.phone_area_code || ' ' ||
RTRIM(RPAD(decode(cont_point.contact_point_type,'TLX',
cont_point.telex_number,
cont_point.phone_number), 15))
from hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_contact_points cont_point,
hz_cust_account_roles car
where acct_role.cust_acct_site_id = Address_id
and acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and rel.directional_flag = 'F'
and acct_role.cust_account_role_id = car.cust_account_role_id(+)
and car.party_id = cont_point.owner_table_id(+)
and cont_point.owner_table_name(+) = 'HZ_PARTIES'
and cont_point.contact_point_type(+) NOT IN ('EDI','EMAIL','WEB')
and nvl(nvl(cont_point.phone_line_type,
cont_point.contact_point_type), 'GEN') = 'GEN'
and nvl( acct_role.status,'A') = 'A'
order by cont_point.primary_flag desc;
select lines.days_start,
lines.days_to,
report_heading1 || ' ' || report_heading2 ,
lines.type
from ar_aging_bucket_lines lines,
ar_aging_buckets buckets
where lines.aging_bucket_id = buckets.aging_bucket_id
and upper(buckets.bucket_name) = upper(p_bucket_name_low)
order by lines.bucket_sequence_num;
select decode (site_use_id, null, 'CUSTOMER', 'SITE'),
decode (site_use_id, null, cust_account_id, site_use_id),
cust_account_id,
site_use_id
into l_entity_type,
l_entity_id,
l_customer_id
,l_site_use_id
from hz_customer_profiles
where cust_account_profile_id = c_customer_profile_id;
Select instr( CP_related_currencies, trx_cur)
INTO present
from dual;
SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
AMOUNT_APPLIED, 0)),
0) on_account,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
amount_applied, 0)),
0) unapplied
into l_aging_on_account_profile,
l_aging_unapplied_profile
from ar_receivable_applications,
ar_cash_receipts
where ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = customer_id
and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
and ar_cash_receipts.currency_code = trx_cur
and ar_receivable_applications.gl_date <= sysdate;
Select instr( related_currencies, trx_cur2)
INTO present
from dual;
SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
AMOUNT_APPLIED, 0)),
0) on_account,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
amount_applied, 0)),
0) unapplied
into l_aging_on_account_profile,
l_aging_unapplied_profile
from ar_receivable_applications,
ar_cash_receipts
where ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = customer_id
and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
and ar_cash_receipts.currency_code = trx_cur2
and ar_receivable_applications.gl_date <= sysdate;
SELECT invoice_currency_code, NVL(SUM(AMOUNT_DUE_REMAINING), 0) ammount_due
from ar_payment_schedules ps
where ps.customer_id = customer_id
and ps.customer_site_use_id = site_use_id
and ps.status = 'OP'
and ps.class not in ('CM', 'PMT')
group by ps.invoice_currency_code;
SELECT
NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
AMOUNT_APPLIED, 0)),
0) on_account,
nvl(max(decode(ar_receivable_applications.status, 'ACC',
decode(ar_cash_receipts.currency_code,functional_currency, ' ',
decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
' ')), ' ') account_convert,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
amount_applied, 0)),
0) unapplied,
nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
decode(ar_cash_receipts.currency_code,functional_currency, ' ',
decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
' ')), ' ') unapp_convert
into
l_aging_on_account_profile,
l_aging_conv_on_ac_profile,
l_aging_unapplied_profile,
l_aging_conv_unap_prof
from ar_receivable_applications,
ar_cash_receipts
where ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = customer_id
and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
and ar_cash_receipts.currency_code = org_currency_code
and ar_receivable_applications.gl_date <= sysdate;
SELECT instr(related_currencies,trx_rec.invoice_currency_code)
INTO curr_exists
FROM DUAL;
SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
AMOUNT_APPLIED, 0)),
0) on_account,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
amount_applied, 0)),
0) unapplied
into l_aging_on_account_profile,
l_aging_unapplied_profile
from ar_receivable_applications,
ar_cash_receipts
where ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = customer_id
and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code
and ar_receivable_applications.gl_date <= sysdate;
Select instr( related_currencies, org_trx_cur)
INTO present
from dual;
SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
AMOUNT_APPLIED, 0)),
0) on_account,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
amount_applied, 0)),
0) unapplied
into l_aging_on_account_profile,
l_aging_unapplied_profile
from ar_receivable_applications,
ar_cash_receipts
where ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = customer_id
and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
and ar_cash_receipts.currency_code = org_trx_cur
and ar_receivable_applications.gl_date <= sysdate;
select count(*)
into ct_prof
from hz_credit_profile_amts cpa
where credit_profile_id IN (Select credit_profile_id
From HZ_Credit_Profiles
Where organization_id = p_reporting_entity_id
and effective_date_from <= SYSDATE
and effective_date_to >= SYSDATE);
SELECT substr(INITCAP(YES.MEANING),1,3) yes,
substr(INITCAP(NO.MEANING),1,3) no
INTO yes,
no
FROM AR_LOOKUPS YES,
AR_LOOKUPS NO
WHERE YES.LOOKUP_TYPE = 'YES/NO' AND
YES.LOOKUP_CODE = 'Y' AND
NO.LOOKUP_TYPE = 'YES/NO' AND
NO.LOOKUP_CODE = 'N';
Select
to_char(nvl(cp.tolerance, 0), '990') || '%',
substr(nvl(cp.credit_rating, rp_na_upper),1,30),
lk.meaning,
cp.credit_profile_id
into
l_cred_summ_limit_tolerance,
l_cred_summ_credit_rating,
l_cred_summ_credit_hold,
l_credit_profile_id
from HZ_credit_profiles cp,
ar_lookups lk
where
nvl(cp.credit_hold,'N') = lk.lookup_code
and lk.lookup_type = 'YES/NO'
and cp.organization_id = p_reporting_entity_id
and NVL(cp.effective_date_from, sysdate-1) > sysdate
and NVL(cp.effective_date_to, sysdate+1) < sysdate
and NVL(cp.enable_flag, 'N') = 'Y';
SELECT invoice_currency_code, NVL(SUM(AMOUNT_DUE_REMAINING), 0) ammount_due,exchange_rate_type
from ar_payment_schedules ps
where ps.customer_id = customer_id
and ps.customer_site_use_id = site_use_id
and ps.status = 'OP'
and ps.class not in ('CM', 'PMT')
group by ps.invoice_currency_code,exchange_rate_type;
SELECT
NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
AMOUNT_APPLIED, 0)),
0) on_account,
nvl(max(decode(ar_receivable_applications.status, 'ACC',
decode(ar_cash_receipts.currency_code, functional_currency, ' ',
decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
' ')), ' ') account_convert,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
amount_applied, 0)),
0) unapplied,
nvl(max(decode(ar_receivable_applications.status, 'UNAPP',
decode(ar_cash_receipts.currency_code, functional_currency, ' ',
decode(ar_cash_receipts.exchange_rate, NULL, '*', ' ')),
' ')), ' ') unapp_convert
into
l_aging_on_account_profile,
l_aging_conv_on_ac_profile,
l_aging_unapplied_profile,
l_aging_conv_unap_prof
from ar_receivable_applications,
ar_cash_receipts
where ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = customer_id
and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
and ar_cash_receipts.currency_code = currency_credit1
and ar_receivable_applications.gl_date <= sysdate;
SELECT instr(related_currencies,trx_rec.invoice_currency_code)
INTO curr_exists
FROM DUAL;
SELECT NVL(SUM(DECODE(AR_RECEIVABLE_APPLICATIONS.STATUS, 'ACC',
AMOUNT_APPLIED, 0)),
0) on_account,
nvl(sum(decode(ar_receivable_applications.status, 'UNAPP',
amount_applied, 0)),
0) unapplied
into l_aging_on_account_profile,
l_aging_unapplied_profile
from ar_receivable_applications,
ar_cash_receipts
where ar_receivable_applications.cash_receipt_id =
ar_cash_receipts.cash_receipt_id
and ar_cash_receipts.pay_from_customer = customer_id
and ar_cash_receipts.CUSTOMER_SITE_USE_ID = site_use_id
and ar_cash_receipts.currency_code = trx_rec.invoice_currency_code
and ar_receivable_applications.gl_date <= sysdate;
for i in (select distinct currency_code from
hz_cust_profile_amts where cust_account_id=p_cust_acct_id
and NVL(site_use_id,-9) = NVL(p_site_use_id,NVL(site_use_id,-9)))
Loop
l_cnt:=l_cnt+1;