[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sum(TRUNC(sysdate) - ps.due_date)
/ COUNT(1)
INTO l_avg_days_late
FROM ar_payment_schedules ps, hz_cust_accounts ca
WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
AND ps.gl_date_closed > TRUNC(sysdate)
-- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'OP'
-- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.due_date < TRUNC(sysdate)
AND ps.payment_schedule_id <> -1
AND ca.cust_account_id = ps.customer_id
AND ca.party_id = p_party_id;
SELECT sum(TRUNC(sysdate) - ps.due_date)
/ COUNT(1)
INTO l_avg_days_late
FROM ar_payment_schedules ps
WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
AND ps.gl_date_closed > TRUNC(sysdate)
-- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'OP'
-- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.due_date < TRUNC(sysdate)
AND ps.payment_schedule_id <> -1
AND ps.customer_id = p_cust_account_id;
SELECT sum(TRUNC(sysdate) - ps.due_date)
/ COUNT(1)
INTO l_avg_days_late
FROM ar_payment_schedules ps
WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
AND ps.gl_date_closed > TRUNC(sysdate)
-- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'OP'
-- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.due_date < TRUNC(sysdate)
AND ps.payment_schedule_id <> -1
AND ps.customer_site_use_id = p_customer_site_use_id;
SELECT sum
(
(
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
) *
(TRUNC(sysdate) - ps.due_date)
) /
sum (
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
)
INTO l_wtd_days_late
FROM ar_payment_schedules ps, hz_cust_accounts ca
WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
-- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
--AND ps.gl_date_closed > TRUNC(sysdate)
--AND ps.due_date < TRUNC(sysdate)
-- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'OP'
-- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.payment_schedule_id <> -1
AND ps.customer_id = ca.cust_account_id
AND ca.party_id = p_party_id;
SELECT sum
(
(
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
) *
(TRUNC(sysdate) - ps.due_date)
) /
sum (
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
)
INTO l_wtd_days_late
FROM ar_payment_schedules ps
WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
-- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- AND ps.gl_date_closed > TRUNC(sysdate)
-- AND ps.due_date < TRUNC(sysdate)
-- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'OP'
-- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.payment_schedule_id <> -1
AND ps.customer_id = p_cust_account_id;
SELECT sum
(
(
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
) *
(TRUNC(sysdate) - ps.due_date)
) /
sum (
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
)
INTO l_wtd_days_late
FROM ar_payment_schedules ps
WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
-- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- AND ps.gl_date_closed > TRUNC(sysdate)
-- AND ps.due_date < TRUNC(sysdate)
-- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'OP'
-- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
AND ps.payment_schedule_id <> -1
AND ps.customer_site_use_id = p_customer_site_use_id;
SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
/ SUM(ra.amount_applied)
, 0) WEIGHTED_AVG_DAYS_PAID
INTO l_wtd_days_paid
FROM ar_receivable_applications ra,
ar_payment_schedules ps,
hz_cust_accounts ca
WHERE ps.customer_id = ca.cust_account_id
AND ca.party_id = p_party_id
AND ra.status = 'APP'
AND ps.payment_schedule_id = ra.applied_payment_schedule_id
AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
-- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- AND ps.gl_date_closed > TRUNC(sysdate)
-- AND ps.due_date < TRUNC(sysdate)
-- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'CL'
-- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
AND ps.payment_schedule_id <> -1;
SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
/ SUM(ra.amount_applied)
, 0) WEIGHTED_AVG_DAYS_PAID
INTO l_wtd_days_paid
FROM ar_receivable_applications ra,
ar_payment_schedules ps
WHERE ps.customer_id = p_cust_account_id
AND ra.status = 'APP'
AND ps.payment_schedule_id = ra.applied_payment_schedule_id
AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
-- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- AND ps.gl_date_closed > TRUNC(sysdate)
-- AND ps.due_date < TRUNC(sysdate)
-- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'CL'
-- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
AND ps.payment_schedule_id <> -1;
SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
/ SUM(ra.amount_applied)
, 0) WEIGHTED_AVG_DAYS_PAID
INTO l_wtd_days_paid
FROM ar_receivable_applications ra,
ar_payment_schedules ps
WHERE ps.customer_site_use_id = p_customer_site_use_id
AND ra.status = 'APP'
AND ps.payment_schedule_id = ra.applied_payment_schedule_id
AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
-- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- AND ps.gl_date_closed > TRUNC(sysdate)
-- AND ps.due_date < TRUNC(sysdate)
-- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'CL'
-- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
AND ps.payment_schedule_id <> -1;
SELECT sum
(
(
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
) *
(ps.due_date - ps.trx_date)
) /
sum (
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
)
INTO l_wtd_days_terms
FROM ar_payment_schedules ps, hz_cust_accounts ca
WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
-- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- AND ps.gl_date_closed > TRUNC(sysdate)
-- AND ps.due_date < TRUNC(sysdate)
-- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
AND ps.payment_schedule_id <> -1
AND ps.customer_id = ca.cust_account_id
AND ca.party_id = p_party_id;
SELECT sum
(
(
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
) *
(ps.due_date - ps.trx_date)
) /
sum (
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
)
INTO l_wtd_days_terms
FROM ar_payment_schedules ps
WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
-- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- AND ps.gl_date_closed > TRUNC(sysdate)
-- AND ps.due_date < TRUNC(sysdate)
-- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
AND ps.payment_schedule_id <> -1
AND ps.customer_id = p_cust_account_id;
SELECT sum
(
(
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
) *
(ps.due_date - ps.trx_date)
) /
sum (
GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
nvl(ps.acctd_amount_due_remaining, 0)
)
INTO l_wtd_days_terms
FROM ar_payment_schedules ps
WHERE ps.gl_date between TRUNC(add_months(sysdate, -12)) and TRUNC(sysdate)
AND ps.class in ('INV','DEP','DM','CB')
-- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
-- AND ps.gl_date_closed > TRUNC(sysdate)
-- AND ps.due_date < TRUNC(sysdate)
-- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
AND ps.payment_schedule_id <> -1
AND ps.customer_site_use_id = p_customer_site_use_id;
SELECT
ROUND(
( (SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1,
0)
* PS.ACCTD_AMOUNT_DUE_REMAINING
) * MAX(SP.CER_DSO_DAYS)
)
/ DECODE(
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0)) ,
0, 1,
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0) )
)
), 0) /* DSO */
INTO l_conv_dso
FROM ar_system_parameters sp,
hz_cust_accounts cust_acct,
ar_payment_schedules ps
WHERE ps.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id = p_party_id
-- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
AND ps.status = 'OP'
-- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';
SELECT
ROUND(
( (SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1,
0)
* PS.ACCTD_AMOUNT_DUE_REMAINING
) * MAX(SP.CER_DSO_DAYS)
)
/ DECODE(
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0)) ,
0, 1,
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0) )
)
), 0) /* DSO */
INTO l_conv_dso
FROM ar_system_parameters sp,
ar_payment_schedules ps
WHERE ps.customer_id = p_cust_account_id
-- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
AND ps.status = 'OP'
-- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';
SELECT
ROUND(
( (SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1,
0)
* PS.ACCTD_AMOUNT_DUE_REMAINING
) * MAX(SP.CER_DSO_DAYS)
)
/ DECODE(
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0)) ,
0, 1,
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0) )
)
), 0) /* DSO */
INTO l_conv_dso
FROM ar_system_parameters sp,
ar_payment_schedules ps
WHERE ps.customer_site_use_id = p_customer_site_use_id
-- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
AND ps.status = 'OP'
-- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';
SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
INTO l_nsf_stop_payment_count
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
hz_cust_accounts ca
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.status = 'REV'
-- AND cr.status = 'NSF' -- bug 5613019
AND cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
--AND cr.reversal_category = 'NSF' -- big 5613019
AND cr.reversal_category in ('NSF','REV') -- bug 5613019
AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
AND cr.pay_from_customer = ca.cust_account_id
AND ca.party_id = p_party_id;
SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
INTO l_nsf_stop_payment_count
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.status = 'REV'
-- AND cr.status = 'NSF' --bug 5613019
AND cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
--AND cr.reversal_category = 'NSF' -- bug 5613019
and cr.reversal_category in ('NSF','REV') -- bug 5613019
AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
AND cr.pay_from_customer = p_cust_account_id;
SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
INTO l_nsf_stop_payment_count
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.status = 'REV'
-- AND cr.status = 'NSF' --bug 5613019
and cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.reversal_category = 'NSF' -- bug 5613019
and cr.reversal_category in ('NSF','REV') -- bug 5613019
AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
AND cr.customer_site_use_id = p_customer_site_use_id;
SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
INTO l_nsf_stop_payment_amount
FROM ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
hz_cust_accounts ca
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.status = 'REV'
-- AND cr.status = 'NSF' -- bug 5613019
and cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.reversal_category = 'NSF' --bug 5613019
and cr.reversal_category in ('NSF','REV') -- bug 5613019
AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
AND cr.pay_from_customer = ca.cust_account_id
AND ca.party_id = p_party_id;
SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
INTO l_nsf_stop_payment_amount
FROM ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.status = 'REV'
-- AND cr.status = 'NSF' -- bug 5613019
and cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.reversal_category = 'NSF' --bug 5613019
and cr.reversal_category in ('NSF','REV') -- bug 5613019
AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
AND cr.pay_from_customer = p_cust_account_id;
SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
INTO l_nsf_stop_payment_amount
FROM ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.status = 'REV'
-- AND cr.status = 'NSF' -- bug5613019
and cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
-- AND cr.reversal_category = 'NSF' -- bug 5613019
and cr.reversal_category in ('NSF','REV') -- bug 5613019
AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
AND cr.customer_site_use_id = p_customer_site_use_id;
SELECT
sum( nvl(adj.acctd_amount,0))
INTO l_adj
FROM ar_payment_schedules ps, ar_adjustments adj, hz_cust_accounts ca
WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
AND ps.payment_schedule_id <> -1
AND ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
AND ps.customer_id = ca.cust_account_id
AND ca.party_id = p_party_id
AND adj.payment_schedule_id = ps.payment_schedule_id
AND adj.status = 'A'
AND adj.gl_date <= TRUNC(sysdate);
SELECT
sum( nvl(adj.acctd_amount,0))
INTO l_adj
FROM ar_payment_schedules ps, ar_adjustments adj
WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
AND ps.payment_schedule_id <> -1
AND ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
AND ps.customer_id = p_cust_account_id
AND adj.payment_schedule_id = ps.payment_schedule_id
AND adj.status = 'A'
AND adj.gl_date <= TRUNC(sysdate);
SELECT
sum( nvl(adj.acctd_amount,0))
INTO l_adj
FROM ar_payment_schedules ps, ar_adjustments adj
WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
AND ps.payment_schedule_id <> -1
AND ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
AND ps.customer_site_use_id = p_customer_site_use_id
AND adj.payment_schedule_id = ps.payment_schedule_id
AND adj.status = 'A'
AND adj.gl_date <= TRUNC(sysdate);
SELECT SUM(arpcurr.functional_amount(
ps.amount_due_original,
g_curr_rec.base_currency,
nvl(ps.exchange_rate,1),
g_curr_rec.base_precision,
g_curr_rec.base_min_acc_unit) +
GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
INTO l_tot_rec
FROM ar_payment_schedules ps,
hz_cust_accounts ca
WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
AND ps.payment_schedule_id <> -1
AND ps.gl_date BETWEEN l_temp_start AND p_end_date
AND ps.customer_id = ca.cust_account_id
AND ca.party_id = p_party_id;
SELECT SUM(arpcurr.functional_amount(
ps.amount_due_original,
g_curr_rec.base_currency,
nvl(ps.exchange_rate,1),
g_curr_rec.base_precision,
g_curr_rec.base_min_acc_unit) +
GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
INTO l_tot_rec
FROM ar_payment_schedules ps
WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
AND ps.payment_schedule_id <> -1
AND ps.gl_date BETWEEN l_temp_start AND p_end_date
AND ps.customer_id = p_cust_account_id;
SELECT SUM(arpcurr.functional_amount(
ps.amount_due_original,
g_curr_rec.base_currency,
nvl(ps.exchange_rate,1),
g_curr_rec.base_precision,
g_curr_rec.base_min_acc_unit) +
GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
INTO l_tot_rec
FROM ar_payment_schedules ps
WHERE ps.class in ('INV', 'DM', 'CB', 'DEP' )
AND ps.payment_schedule_id <> -1
AND ps.gl_date BETWEEN l_temp_start AND p_end_date
AND ps.customer_site_use_id = p_customer_site_use_id;
SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
nvl(ps.acctd_amount_due_remaining,0))
INTO l_rem_sales
FROM ar_payment_schedules ps,
hz_cust_accounts ca
WHERE ps.gl_date between p_start_date and p_end_date
AND ps.class in ('INV','DEP','DM','CB')
AND ps.gl_date_closed > p_end_date
AND ps.customer_id = ca.cust_account_id
-- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'OP'
-- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
AND ca.party_id = p_party_id;
SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
nvl(ps.acctd_amount_due_remaining,0))
INTO l_rem_sales
FROM ar_payment_schedules ps
WHERE ps.gl_date between p_start_date and p_end_date
AND ps.class in ('INV','DEP','DM','CB')
AND ps.gl_date_closed > p_end_date
-- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'OP'
-- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
AND ps.customer_id = p_cust_account_id;
SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
nvl(ps.acctd_amount_due_remaining,0))
INTO l_rem_sales
FROM ar_payment_schedules ps
WHERE ps.gl_date between p_start_date and p_end_date
AND ps.class in ('INV','DEP','DM','CB')
AND ps.gl_date_closed > p_end_date
-- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
AND ps.status = 'OP'
-- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
AND ps.customer_site_use_id = p_customer_site_use_id;
SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
INTO l_curr_rec
FROM ar_payment_schedules ps,
hz_cust_accounts ca
WHERE ps.gl_date between p_start_date and p_end_date
AND ps.class in ('INV','DEP','DM','CB')
AND ps.gl_date_closed > p_end_date
AND ps.status = 'OP'
AND ps.customer_id = ca.cust_account_id
AND ca.party_id = p_party_id
AND ps.due_date > p_end_date;
SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
INTO l_curr_rec
FROM ar_payment_schedules ps
WHERE ps.gl_date between p_start_date and p_end_date
AND ps.class in ('INV','DEP','DM','CB')
AND ps.gl_date_closed > p_end_date
AND ps.status = 'OP'
AND ps.customer_id = p_cust_account_id
AND ps.due_date > p_end_date;
SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
INTO l_curr_rec
FROM ar_payment_schedules ps
WHERE ps.gl_date between p_start_date and p_end_date
AND ps.class in ('INV','DEP','DM','CB')
AND ps.gl_date_closed > p_end_date
AND ps.status = 'OP'
AND ps.customer_site_use_id = p_customer_site_use_id
AND ps.due_date > p_end_date;
SELECT sum( nvl(ra.acctd_amount_applied_to,0) +
nvl(ra.acctd_earned_discount_taken,0) +
nvl(ra.acctd_unearned_discount_taken,0))
INTO l_apps_tot
FROM ar_receivable_applications ra
WHERE ra.applied_payment_schedule_id = p_payment_schedule_id
AND ra.status = 'APP'
AND nvl(ra.confirmed_flag,'Y') = 'Y'
AND ra.gl_date > p_to_date;
SELECT sum( nvl(a.acctd_amount,0))
INTO l_adj_tot
FROM ar_adjustments a
WHERE a.payment_schedule_id = p_payment_schedule_id
AND a.status = 'A'
AND a.gl_date > p_to_date;
SELECT sum( nvl(a.acctd_amount,0))
INTO l_adj_for_tot_rec
FROM ar_adjustments a
WHERE a.payment_schedule_id = p_payment_schedule_id
AND a.status = 'A'
AND a.gl_date <= p_to_date;
SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
-- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
INTO l_credit_limit
FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
ar_cmgt_setup_options cm_opt
WHERE prof.party_id = p_party_id
AND prof.site_use_id IS NULL
AND prof.status = 'A'
AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
AND prof_amt.cust_account_id = prof.cust_account_id
--Begin-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
AND prof.cust_account_id = -1
--End-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
AND prof_amt.site_use_id IS NULL;
SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
-- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
INTO l_credit_limit
FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
ar_cmgt_setup_options cm_opt
WHERE prof.cust_account_id = p_cust_account_id
AND prof.site_use_id IS NULL
AND prof.status = 'A'
AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
AND prof_amt.cust_account_id = p_cust_account_id
AND prof_amt.site_use_id IS NULL;
SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
-- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
INTO l_credit_limit
FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
ar_cmgt_setup_options cm_opt
WHERE prof.site_use_id = p_customer_site_use_id
AND prof.status = 'A'
AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
AND prof_amt.site_use_id = p_customer_site_use_id;
SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
INTO l_high_credit_ytd
FROM ar_trx_summary trx_summ,
ar_cmgt_setup_options cm_opt,
hz_cust_accounts ca
WHERE NVL(trx_summ.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND trx_summ.cust_account_id = ca.cust_account_id
AND ca.party_id = p_party_id;
SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
INTO l_high_credit_ytd
FROM ar_trx_summary trx_summ,
ar_cmgt_setup_options cm_opt
WHERE NVL(trx_summ.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND trx_summ.cust_account_id = p_cust_account_id;
SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
INTO l_high_credit_ytd
FROM ar_trx_summary trx_summ,
ar_cmgt_setup_options cm_opt
WHERE NVL(trx_summ.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND trx_summ.site_use_id = p_customer_site_use_id;
/*SELECT sob.currency_code,
c.precision,
c.minimum_accountable_unit
INTO g_curr_rec.base_currency,
g_curr_rec.base_precision,
g_curr_rec.base_min_acc_unit
FROM ar_system_parameters sysp,
gl_sets_of_books sob,
fnd_currencies c
WHERE sob.set_of_books_id = sysp.set_of_books_id
AND sob.currency_code = c.currency_code;
SELECT TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
TRUNC(sysdate) pastYearTo
INTO g_curr_rec.past_year_from,
g_curr_rec.past_year_to
FROM dual;*/