[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select lines.days_start,
lines.days_to,
lines.report_heading1,
lines.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)
and nvl(buckets.status,'A') = 'A'
order by lines.bucket_sequence_num
;
select sum(adr), sum(bucket0), sum(bucket1), sum(bucket2),
sum(bucket3), sum(bucket4), sum(bucket5),sum(bucket6)
from(
SELECT sum( gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
ps.amount_due_remaining)) adr,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_0,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_0,
v_bucket_days_to_0,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
ps.amount_due_remaining)) bucket0 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_1,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_1,
v_bucket_days_to_1,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
ps.amount_due_remaining)) bucket1 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_2,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_2,
v_bucket_days_to_2,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
ps.amount_due_remaining)) bucket2 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_3,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_3,
v_bucket_days_to_3,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
ps.amount_due_remaining)) bucket3 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_4,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_4,
v_bucket_days_to_4,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
ps.amount_due_remaining)) bucket4 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_5,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_5,
v_bucket_days_to_5,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
ps.amount_due_remaining)) bucket5 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_6,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_6,
v_bucket_days_to_6,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
ps.amount_due_remaining)) bucket6
FROM ar_payment_schedules_all ps
WHERE payment_schedule_id +0 > 0
AND ps.class NOT IN ('GUAR', 'PMT')
--kjoshi bug#5169416
AND nvl(sign(ps.cons_inv_id),0) = decode(p_source,'CONS_BILL',1,0) --apandit BFB changes
AND trx_date <= l_as_of_date
AND actual_date_closed > l_as_of_date
and ps.customer_id in (select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= l_as_of_date
and effective_end_date >= l_as_of_date
and hierarchy_type =
FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and pg_source_name <> 'LNS'
union
select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = pg_source_id
and participant_type_code = 'COBORROWER'
and pg_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
)
union
select p_customer_id from dual
)
and decode(p_site_use_id,
NULL, ps.customer_site_use_id,
p_site_use_id) = ps.customer_site_use_id
and ((ps.invoice_currency_code = p_currency_code
and p_source = 'CONS_BILL')
or (nvl(p_source,'x') <> 'CONS_BILL' and
ps.invoice_currency_code in
(select currency
from ar_cmgt_curr_usage_gt)))
) ;
select sum(adr), sum(bucket0), sum(bucket1), sum(bucket2),
sum(bucket3), sum(bucket4), sum(bucket5),sum(bucket6)
from(
-----All the receipt and CM applications after the as of date ---------
SELECT
sum( gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) adr,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_0,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_0,
v_bucket_days_to_0,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket0 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_1,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_1,
v_bucket_days_to_1,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket1 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_2,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_2,
v_bucket_days_to_2,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket2 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_3,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_3,
v_bucket_days_to_3,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket3 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_4,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_4,
v_bucket_days_to_4,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket4 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_5,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_5,
v_bucket_days_to_5,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket5 ,
sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_6,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_6,
v_bucket_days_to_6,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket6
FROM
ar_payment_schedules_all ps,
ar_receivable_applications_all ra
WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
--kjoshi bug#5169416
AND nvl(sign(ps.cons_inv_id),0) = decode(p_source,'CONS_BILL',1,0)
AND ps.payment_schedule_id +0 > 0
AND ra.apply_date > l_as_of_date
AND ra.status = 'APP'
AND ps.class NOT IN ('GUAR', 'PMT')
AND ps.trx_date <= l_as_of_date
AND ps.actual_date_closed > l_as_of_date
AND NVL(ra.confirmed_flag,'Y') = 'Y'
and ps.customer_id in (select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= l_as_of_date
and effective_end_date >= l_as_of_date
and hierarchy_type =
FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and pg_source_name <> 'LNS'
union
select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = pg_source_id
and participant_type_code = 'COBORROWER'
and pg_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
)
union
select p_customer_id from dual
)
and decode(p_site_use_id,
NULL, ps.customer_site_use_id,
p_site_use_id) = ps.customer_site_use_id
and ((ps.invoice_currency_code = p_currency_code
and p_source = 'CONS_BILL')
or (nvl(p_source,'x') <> 'CONS_BILL' and
ps.invoice_currency_code in
(select currency
from ar_cmgt_curr_usage_gt)))
) ;
select sum(adr), sum(bucket0), sum(bucket1), sum(bucket2),
sum(bucket3), sum(bucket4), sum(bucket5),sum(bucket6)
from(
------------All the adjustments after the as of date---------------
SELECT
-sum(gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
adj.amount)) adr,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_0,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_0,
v_bucket_days_to_0,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
adj.amount)) bucket0 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_1,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_1,
v_bucket_days_to_1,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
adj.amount)) bucket1 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_2,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_2,
v_bucket_days_to_2,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
adj.amount)) bucket2 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_3,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_3,
v_bucket_days_to_3,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
adj.amount)) bucket3 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_4,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_4,
v_bucket_days_to_4,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
adj.amount)) bucket4 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_5,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_5,
v_bucket_days_to_5,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
adj.amount)) bucket5 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_6,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_6,
v_bucket_days_to_6,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
adj.acctd_amount)) bucket6
FROM ar_adjustments_all adj,
ar_payment_schedules_all ps
WHERE adj.payment_schedule_id = ps.payment_schedule_id
--kjoshi bug#5169416
AND nvl(sign(ps.cons_inv_id),0) = decode(p_source,'CONS_BILL',1,0) --apandit BFB changes
AND adj.apply_date > l_as_of_date
AND ps.class NOT IN ('GUAR', 'PMT')
AND ps.trx_date <= l_as_of_date
AND ps.actual_date_closed > l_as_of_date
AND adj.status = 'A'
and ps.customer_id in (select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= l_as_of_date
and effective_end_date >= l_as_of_date
and hierarchy_type =
FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and pg_source_name <> 'LNS'
union
select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = pg_source_id
and participant_type_code = 'COBORROWER'
and pg_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
)
union
select p_customer_id from dual
)
and decode(p_site_use_id,
NULL, ps.customer_site_use_id,
p_site_use_id) = ps.customer_site_use_id
and ((ps.invoice_currency_code = p_currency_code
and p_source = 'CONS_BILL')
or (nvl(p_source,'x') <> 'CONS_BILL' and
ps.invoice_currency_code in
(select currency
from ar_cmgt_curr_usage_gt)))
) ;
select sum(adr), sum(bucket0), sum(bucket1), sum(bucket2),
sum(bucket3), sum(bucket4), sum(bucket5),sum(bucket6)
from(
---------all the CM applications after the as of date -----------
SELECT sum(gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied_from +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) adr,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_0,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_0,
v_bucket_days_to_0,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied_from +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket0 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_1,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_1,
v_bucket_days_to_1,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied_from +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket1 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_2,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_2,
v_bucket_days_to_2,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied_from +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket2 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_3,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_3,
v_bucket_days_to_3,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied_from +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket3 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_4,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_4,
v_bucket_days_to_4,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied_from +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket4 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_5,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_5,
v_bucket_days_to_5,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied_from +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket5 ,
-sum(arpt_sql_func_util.bucket_function(v_bucket_line_type_6,
ps.amount_in_dispute,ps.amount_adjusted_pending,v_bucket_days_from_6,
v_bucket_days_to_6,ps.due_date,v_bucket_category,l_as_of_date)
* gl_currency_api.convert_amount(
ps.invoice_currency_code,
p_currency_code,
sysdate,
p_exchange_rate_type,
(ra.amount_applied_from +
NVL(ra.earned_discount_taken,0)
+ NVL(ra.unearned_discount_taken,0) ))) bucket6
FROM ar_payment_schedules_all ps,
ar_receivable_applications_all ra
WHERE
ps.payment_schedule_id +0 > 0
AND ra.payment_schedule_id = ps.payment_schedule_id
--kjoshi bug#5169416
AND nvl(sign(ps.cons_inv_id),0) = decode(p_source,'CONS_BILL',1,0) --apandit BFB changes
AND ra.apply_date > l_as_of_date
AND ps.class NOT IN ('GUAR', 'PMT')
AND ra.status = 'APP'
and ra.application_type = 'CM'
AND ps.trx_date <= l_as_of_date
AND ps.actual_date_closed > l_as_of_date
AND NVL(ra.confirmed_flag,'Y') = 'Y'
and ps.customer_id in (select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= l_as_of_date
and effective_end_date >= l_as_of_date
and hierarchy_type =
FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and pg_source_name <> 'LNS'
union
select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = pg_source_id
and participant_type_code = 'COBORROWER'
and pg_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
)
union
select p_customer_id from dual
)
and decode(p_site_use_id,
NULL, ps.customer_site_use_id,
p_site_use_id) = ps.customer_site_use_id
and ((ps.invoice_currency_code = p_currency_code
and p_source = 'CONS_BILL')
or (nvl(p_source,'x') <> 'CONS_BILL' and
ps.invoice_currency_code in
(select currency
from ar_cmgt_curr_usage_gt)))
) ;
INSERT INTO ar_cmgt_curr_usage_gt ( credit_request_id, currency) values
( NULL, l_curr_tbl(i).usage_curr_code);
INSERT INTO ar_cmgt_curr_usage_gt(currency)
( select distinct currency from ar_trx_bal_summary);
INSERT INTO ar_cmgt_curr_usage_gt(currency)
( select distinct currency from ar_trx_bal_summary);