[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT extended_amount
INTO l_original_amount
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_customer_trx_line_id;
SELECT DECODE(line_type, 'LINE',
NVL(gross_extended_amount, extended_amount),
extended_amount)
INTO l_original_amount
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_customer_trx_line_id;
SELECT l_original_amount +
NVL(
SUM(
DECODE(ct.complete_flag,
'N', DECODE(ctl.customer_trx_line_id,
p_cm_customer_trx_line_id,
nvl(ctl.gross_extended_amount,ctl.extended_amount),
0 ),
nvl(ctl.gross_extended_amount,ctl.extended_amount)
)
), 0
)
INTO l_net_amount
FROM ra_customer_trx ct,
ra_customer_trx_lines ctl
WHERE ctl.previous_customer_trx_line_id = p_customer_trx_line_id
AND ctl.customer_trx_id = ct.customer_trx_id;
SELECT NVL(
SUM( extended_amount ), 0
)
INTO l_credit_amount
FROM ra_customer_trx_lines
WHERE previous_customer_trx_line_id = p_prev_customer_trx_line_id;
SELECT SUM(extended_amount)
INTO l_credit_amount
FROM ra_customer_trx_lines
WHERE previous_customer_trx_line_id = p_prev_customer_trx_line_id;
SELECT NVL(
SUM( extended_amount ), 0
)
INTO l_credit_amount
FROM ra_customer_trx_lines
WHERE previous_customer_trx_line_id = p_prev_customer_trx_line_id;
SELECT ctt.accounting_affect_flag,
ct.exchange_rate,
ctt.type
INTO l_open_receivables_flag,
l_exchange_rate,
l_trx_type
FROM ra_cust_trx_types ctt,
ra_customer_trx ct
WHERE ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.customer_trx_id = p_customer_trx_id;
SELECT ctt.type
INTO l_trx_type
FROM ra_cust_trx_types ctt,
ra_customer_trx ct
WHERE ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ct.customer_trx_id = p_customer_trx_id;
SELECT sob.currency_code,
precision,
minimum_accountable_unit
INTO l_base_curr_code,
l_base_precision,
l_base_min_acc_unit
FROM fnd_currencies fc,
gl_sets_of_books sob
WHERE sob.set_of_books_id = arp_global.set_of_books_id
AND sob.currency_code = fc.currency_code;
SELECT nvl(SUM( acctd_amount ),0)
INTO l_base_total_credits1
FROM ra_cust_trx_line_gl_dist lgd,
ra_customer_trx ct
WHERE ct.customer_trx_id = lgd.customer_trx_id
AND lgd.account_class = 'REC'
AND lgd.latest_rec_flag = 'Y'
AND ct.previous_customer_trx_id = p_customer_trx_id;
select nvl(sum(rec.acctd_amount_applied_from),0)*(-1)
into l_base_total_credits2
from ar_receivable_applications rec,
ra_customer_trx trx
where rec.applied_customer_trx_id = p_customer_trx_id
and rec.customer_trx_id = trx.customer_trx_id
and rec.status = 'APP'
and rec.application_type = 'CM'
and trx.previous_customer_trx_id is null;
SELECT SUM( NVL( amount_line_items_original, 0 ) ),
SUM( NVL( amount_line_items_remaining, 0 ) ),
SUM( NVL( tax_original, 0 ) ),
SUM( NVL( tax_remaining, 0 ) ),
SUM( NVL( freight_original, 0 ) ),
SUM( NVL( freight_remaining, 0 ) ),
SUM( NVL( receivables_charges_charged, 0 ) ),
SUM( NVL( receivables_charges_remaining, 0 ) ),
SUM( NVL( amount_due_original, 0 ) ),
SUM( NVL( amount_due_remaining, 0 ) ),
DECODE(
p_currency_mode,
'E', null,
SUM( NVL( acctd_amount_due_remaining, 0 ) )
),
DECODE(
p_mode,
'ALL', SUM( NVL( amount_applied, 0 ) ),
null
),
DECODE(
p_mode,
'ALL', SUM( NVL( amount_credited, 0 ) ),
null
)
-- DECODE(
-- p_mode,
-- 'ALL', SUM( NVL( amount_adjusted, 0 ) ),
-- null
-- )
INTO
l_line_original,
l_line_remaining,
l_tax_original,
l_tax_remaining,
l_freight_original,
l_freight_remaining,
l_charges_original,
l_charges_remaining,
l_total_original,
l_total_remaining,
l_base_total_remaining,
l_total_receipts,
l_total_credits
-- l_total_adjustments
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id;
SELECT DECODE(
p_currency_mode,
'E', null,
MAX( dist.acctd_amount_dr )
)
INTO l_base_total_original
FROM ar_transaction_history trh,
ar_distributions dist
WHERE trh.customer_trx_id = p_customer_trx_id
AND trh.first_posted_record_flag = 'Y'
AND dist.source_id = trh.transaction_history_id
AND dist.source_table = 'TH'
AND dist.source_type = 'REC'
AND dist.source_type_secondary IS NULL
AND dist.source_id_secondary IS NULL
AND dist.source_table_secondary IS NULL;
SELECT DECODE(
p_currency_mode,
'E', null,
MAX( lgd.acctd_amount )
)
INTO l_base_total_original
FROM ra_cust_trx_line_gl_dist lgd
WHERE lgd.customer_trx_id = p_customer_trx_id
AND lgd.account_class = 'REC'
AND lgd.latest_rec_flag = 'Y';
SELECT SUM(
DECODE(
ct.complete_flag,
'N', 0,
DECODE(
ctl.line_type,
'TAX', 0,
'FREIGHT', 0,
ctl.extended_amount
)
)
), -- line_credited
SUM(
DECODE(
ct.complete_flag,
'N', 0,
DECODE(
ctl.line_type,
'TAX', ctl.extended_amount,
0
)
)
), -- tax_credited
SUM(
DECODE(
ct.complete_flag,
'N', 0,
DECODE(
ctl.line_type,
'FREIGHT', ctl.extended_amount,
0
)
)
) -- freight_credited
INTO l_line_credits1,
l_tax_credits1,
l_freight_credits1
FROM ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist rec,
ra_customer_trx ct
WHERE ct.customer_trx_id = ctl.customer_trx_id
AND ct.customer_trx_id = rec.customer_trx_id
AND rec.account_class = 'REC'
AND rec.latest_rec_flag = 'Y'
AND ct.previous_customer_trx_id = p_customer_trx_id;
select nvl(sum(rec.line_applied),0)*(-1),
nvl(sum(rec.tax_applied),0)*(-1),
nvl(sum(rec.freight_applied),0)*(-1)
into l_line_credits2,
l_tax_credits2,
l_freight_credits2
from ar_receivable_applications rec,
ra_customer_trx trx
where rec.applied_customer_trx_id = p_customer_trx_id
and rec.customer_trx_id = trx.customer_trx_id
and rec.status = 'APP'
and rec.application_type = 'CM'
and trx.previous_customer_trx_id is null;
SELECT NVL(SUM(DECODE(rec.line_applied,null,rec.amount_applied,0)),0),
NVL(SUM(NVL(rec.line_applied,rec.amount_applied)),0),
NVL(SUM(rec.tax_applied),0),
NVL(SUM(rec.freight_applied),0)
into l_cm_refunds,
l_line_act_credits,
l_tax_act_credits,
l_freight_act_credits
FROM ar_receivable_applications rec
WHERE rec.customer_trx_id = p_customer_trx_id
AND rec.status = 'ACTIVITY';
SELECT SUM( NVL( line_adjusted, 0) ),
SUM( NVL( tax_adjusted, 0) ),
SUM( NVL( freight_adjusted, 0) ),
SUM( NVL( receivables_charges_adjusted, 0) ),
DECODE(
p_currency_mode,
'E', null,
SUM( acctd_amount )
),
SUM(NVL(amount,0))
/*3374248*/
,SUM(DECODE(type,'LINE',NVL(acctd_amount,0),0))
,SUM(DECODE(type,'FREIGHT',NVL(acctd_amount,0),0))
,SUM(DECODE(type,'CHARGES',NVL(acctd_amount,0),0))
,SUM(DECODE(type,'TAX',NVL(acctd_amount,0),0))
INTO l_line_adjustments,
l_tax_adjustments,
l_freight_adjustments,
l_charges_adjustments,
l_base_total_adjustments,
l_total_adjustments
/*3374248*/
,l_new_line_acctd_amt
,l_new_frt_acctd_amt
,l_new_chrg_acctd_amt
,l_new_tax_acctd_amt
FROM ar_adjustments
WHERE customer_trx_id = p_customer_trx_id
AND status = 'A'
AND receivables_trx_id <> -15;
SELECT SUM( NVL( line_adjusted, 0) ),
SUM( NVL( tax_adjusted, 0) ),
SUM( NVL( freight_adjusted, 0) ),
SUM( NVL( receivables_charges_adjusted, 0) ),
DECODE(
p_currency_mode,
'E', null,
SUM( acctd_amount )
),
SUM(NVL(amount,0))
INTO l_aline_adjustments,
l_atax_adjustments,
l_afreight_adjustments,
l_acharges_adjustments,
l_base_atotal_adjustments,
l_atotal_adjustments
FROM ar_adjustments
WHERE customer_trx_id = p_customer_trx_id
AND status = 'A'
AND receivables_trx_id = -15;
SELECT SUM( NVL( line_applied, 0 )),
SUM( NVL( tax_applied, 0 )),
SUM( NVL( freight_applied, 0 )),
SUM( NVL( receivables_charges_applied, 0 )),
SUM( NVL( amount_applied, 0 )),
SUM( NVL( line_ediscounted, 0)),
SUM( NVL( line_uediscounted, 0)),
SUM( NVL( tax_ediscounted, 0)),
SUM( NVL( tax_uediscounted, 0)),
SUM( NVL( freight_ediscounted, 0)),
SUM( NVL( freight_uediscounted, 0)),
SUM( NVL( charges_ediscounted, 0)),
SUM( NVL( charges_uediscounted, 0)),
DECODE(
p_currency_mode,
'E', null,
SUM( NVL( acctd_amount_applied_to, 0 ))
),
SUM(
NVL( earned_discount_taken, 0) +
NVL( unearned_discount_taken, 0 )
),
DECODE(
p_currency_mode,
'E', null,
SUM(
NVL( acctd_earned_discount_taken, 0) +
NVL( acctd_unearned_discount_taken, 0 )
)
)
INTO l_line_receipts,
l_tax_receipts,
l_freight_receipts,
l_charges_receipts,
l_total_receipts,
l_line_edreceipts,
l_line_uedreceipts,
l_tax_edreceipts,
l_tax_uedreceipts,
l_freight_edreceipts,
l_freight_uedreceipts,
l_charges_edreceipts,
l_charges_uedreceipts,
l_base_total_receipts,
l_total_discount,
l_base_total_discount
FROM ar_receivable_applications
WHERE applied_customer_trx_id = p_customer_trx_id
AND application_type = 'CASH'
AND NVL( confirmed_flag, 'Y' ) = 'Y';
SELECT SUM(
DECODE(
ct.complete_flag,
'N', 0,
DECODE( -- only use the original lines
ctl.customer_trx_line_id,
orig_ctl.customer_trx_line_id,
orig_ctl.extended_amount,
0
)
)
), -- total original
SUM(
DECODE(
ct.complete_flag,
'N', 0,
ctl.extended_amount
)
), -- total remaining
SUM(
DECODE( -- only use LINE, CHARGES + CB lines
ctl.line_type,
'TAX', 0,
'FREIGHT', 0,
1
) *
DECODE(
ct.complete_flag,
'N', 0,
DECODE(
ctl.customer_trx_line_id,
orig_ctl.customer_trx_line_id,
orig_ctl.extended_amount,
0
)
)
), -- line original
SUM(
DECODE(
ctl.line_type,
'TAX', 0,
'FREIGHT', 0,
1
) *
DECODE(
ct.complete_flag,
'N', 0,
ctl.extended_amount
)
), -- line remaining
SUM(
DECODE( -- only use TAX lines
ctl.line_type,
'TAX', 1,
0
) *
DECODE(
ct.complete_flag,
'N', 0,
DECODE(
ctl.customer_trx_line_id,
orig_ctl.customer_trx_line_id,
orig_ctl.extended_amount,
0
)
)
), -- tax original
SUM(
DECODE(
ctl.line_type,
'TAX', 1,
0
) *
DECODE(
ct.complete_flag,
'N', 0,
ctl.extended_amount
)
), -- tax remaining
SUM(
DECODE( -- only use FREIGHT lines
ctl.line_type,
'FREIGHT', 1,
0
) *
DECODE(
ct.complete_flag,
'N', 0,
DECODE(
ctl.customer_trx_line_id,
orig_ctl.customer_trx_line_id,
orig_ctl.extended_amount,
0
)
)
), -- freight original
SUM(
DECODE(
ctl.line_type,
'FREIGHT', 1,
0
) *
DECODE(
ct.complete_flag,
'N', 0,
ctl.extended_amount
)
), -- freight remaining
SUM(
DECODE( -- Only get credits in ALL mode
p_mode, 'ALL',
1,
null
) *
DECODE( -- only use LINE, CHARGES + CB lines
ctl.line_type,
'TAX', 0,
'FREIGHT', 0,
1
) *
DECODE(
ct.complete_flag,
'N', 0,
DECODE(
ctl.customer_trx_line_id,
orig_ctl.customer_trx_line_id,
0,
ctl.extended_amount
)
)
), -- line credits
SUM(
DECODE( -- Only get credits in ALL mode
p_mode, 'ALL',
1,
null
) *
DECODE( -- only use TAX lines
ctl.line_type,
'TAX', 1,
0
) *
DECODE(
ct.complete_flag,
'N', 0,
DECODE(
ctl.customer_trx_line_id,
orig_ctl.customer_trx_line_id,
0,
ctl.extended_amount
)
)
), -- tax credits
SUM(
DECODE( -- Only get credits in ALL mode
p_mode, 'ALL',
1,
null
) *
DECODE( -- only use FREIGHT lines
ctl.line_type,
'FREIGHT', 1,
0
) *
DECODE(
ct.complete_flag,
'N', 0,
DECODE(
ctl.customer_trx_line_id,
orig_ctl.customer_trx_line_id,
0,
ctl.extended_amount
)
)
), -- freight credits
SUM(
DECODE( -- Only get credits in ALL mode
p_mode, 'ALL',
1,
null
) *
DECODE(
ct.complete_flag,
'N', 0,
DECODE( -- only use the credit lines
ctl.customer_trx_line_id,
orig_ctl.customer_trx_line_id,
0,
ctl.extended_amount
)
)
), -- total credits
DECODE(
p_currency_mode,
'E', null,
max( lgd.acctd_amount )
) -- total base amount
INTO l_total_original,
l_total_remaining,
l_line_original,
l_line_remaining,
l_tax_original,
l_tax_remaining,
l_freight_original,
l_freight_remaining,
l_line_credits,
l_tax_credits,
l_freight_credits,
l_total_credits,
l_base_total_original
FROM ra_cust_trx_line_gl_dist lgd,
ra_customer_trx_lines orig_ctl,
ra_customer_trx_lines ctl,
ra_customer_trx ct
WHERE (
ctl.customer_trx_line_id = orig_ctl.customer_trx_line_id
OR
ctl.previous_customer_trx_line_id
= orig_ctl.customer_trx_line_id
)
AND ctl.customer_trx_id = ct.customer_trx_id
AND orig_ctl.customer_trx_id = lgd.customer_trx_id
AND lgd.account_class = 'REC'
AND lgd.latest_rec_flag = 'Y'
AND orig_ctl.customer_trx_id = p_customer_trx_id;
select nvl(PREVIOUS_CUSTOMER_TRX_ID,0) into l_previous_customer_trx_id from ra_customer_trx where customer_trx_id=p_customer_trx_id;
| the base total credits values was selected in order to determine |
| the base total balance. This value should not be returned, |
| however, since the p_mode <> 'ALL'. Null the value out NOCOPY in this case. |
+------------------------------------------------------------------------*/
IF ( p_mode <> 'ALL' )
THEN l_base_total_credits := null;
| 12-JAN-01 Michael Raymond Fixed select over ra_interface_lines
| table to properly test trx_type
| for commitment invoices. OM
| is using a little-known method where
| the trx_type is defaulted from the
| commitment trx_type.
| See bug 1580737 for details.
| 11-APR-01 Michael Raymond Implemented promised_commitment_amount
| and allocate_tax_freight logic for
| commitment-related lines in
| ra_interface_lines table.
| See bugs 1483656 and 1645425 for details.
+===========================================================================*/
FUNCTION calc_commitment_balance( p_customer_trx_id IN Number,
p_class IN Varchar2,
p_include_oe_trx_flag IN varchar2,
p_oe_installed_flag IN varchar2,
p_so_source_code IN varchar2 )
RETURN NUMBER IS
l_commitment_bal number;
SELECT lines.extended_amount,
type.type,
trx.invoice_currency_code,
type.subsequent_trx_type_id,
type.allocate_tax_freight
INTO l_commitment_bal,
l_commitment_class,
l_currency_code,
l_sub_inv_trx_type_id,
l_allocate_t_f
FROM hz_cust_accounts cust_acct,
ra_customer_trx_lines lines,
ra_customer_trx trx,
ra_cust_trx_types type
WHERE trx.customer_trx_id = p_customer_trx_id
AND trx.cust_trx_type_id = type.cust_trx_type_id
AND trx.customer_trx_id = lines.customer_trx_id
AND trx.bill_to_customer_id = cust_acct.cust_account_id
AND type.type IN ('DEP','GUAR')
ORDER BY trx.trx_number;
SELECT NVL( l_commitment_bal, 0 ) -
NVL(OE_Payments_Util.Get_Uninvoiced_Commitment_Bal(p_customer_trx_id), 0)
INTO l_commitment_bal
FROM dual;
SELECT NVL( l_commitment_bal, 0 ) -
NVL( SUM(NVL(i.promised_commitment_amount,
i.amount)), 0)
INTO l_commitment_bal
FROM ra_interface_lines i,
ra_customer_trx_lines l
WHERE NVL(interface_status,
'A') <> 'P'
AND (i.line_type = 'LINE'
OR i.line_type = DECODE(l_allocate_t_f,'Y','FREIGHT','LINE'))
AND i.reference_line_id = l.customer_trx_line_id
AND l.customer_trx_id = p_customer_trx_id
AND i.interface_line_context = p_so_source_code
AND (EXISTS
( select 'valid_trx_type'
from ra_cust_trx_types ty
where (i.cust_trx_type_name = ty.name OR
i.cust_trx_type_id = ty.cust_trx_type_id)
AND ty.type = 'INV')
OR (i.cust_trx_type_name is null AND
i.cust_trx_type_id is null AND
l_sub_inv_trx_type_id is not null));
SELECT NVL( l_commitment_bal, 0)
-
(
NVL(
SUM( ADJ.AMOUNT),
0
) * -1
)
INTO l_commitment_bal
FROM ra_customer_trx trx,
ra_cust_trx_types type,
ar_adjustments adj
WHERE trx.cust_trx_type_id = type.cust_trx_type_id
AND trx.initial_customer_trx_id = p_customer_trx_id
AND trx.complete_flag = 'Y'
AND adj.adjustment_type = 'C'
AND type.type IN ('INV', 'CM')
AND adj.customer_trx_id =
DECODE(type.type,
'INV', trx.customer_trx_id,
'CM', trx.previous_customer_trx_id)
AND NVL( adj.subsequent_trx_id, -111) =
DECODE(type.type,
'INV', -111,
'CM', trx.customer_trx_id);
SELECT NVL( l_commitment_bal, 0)
-
NVL(
SUM(
-1 * line.extended_amount
),
0
)
INTO l_commitment_bal
FROM ra_customer_trx trx,
ra_customer_trx_lines line
WHERE trx.customer_trx_id = line.customer_trx_id
AND trx.previous_customer_trx_id = p_customer_trx_id
AND trx.complete_flag = 'Y';
SELECT NVL( l_commitment_bal, 0) -
(
NVL(
SUM(
amount_line_items_original
),
0
) -
NVL(
SUM(
amount_due_remaining
),
0
)
)
INTO l_commitment_bal
FROM ar_payment_schedules
WHERE customer_trx_id = p_customer_trx_id;
SELECT NVL( l_commitment_bal, 0) -
NVL(
SUM( amount ),
0
)
INTO l_commitment_bal
FROM ar_adjustments
WHERE customer_trx_id = p_customer_trx_id
AND adjustment_type <> 'C';
SELECT NVL(SUM(amount),0),
NVL(SUM(acctd_amount),0)
INTO l_actual_amount1,
l_acctd_amount1
FROM ra_customer_trx t,
ra_cust_trx_types ty,
ar_adjustments a
WHERE t.cust_trx_type_id = ty.cust_trx_type_id
and t.customer_trx_id = a.customer_trx_id
and ty.type not in ('DEP', 'GUAR')
and a.adjustment_type = 'C'
and t.customer_trx_id = p_customer_trx_id;
SELECT NVL(SUM(amount),0),
NVL(SUM(acctd_amount),0)
INTO l_actual_amount1,
l_acctd_amount1
FROM ra_customer_trx t,
ra_cust_trx_types ty,
ar_adjustments a
WHERE t.cust_trx_type_id = ty.cust_trx_type_id
and t.customer_trx_id = a.subsequent_trx_id
and ty.type not in ('DEP', 'GUAR')
and a.adjustment_type = 'C'
and t.customer_trx_id = p_customer_trx_id;
SELECT NVL(SUM(amount),0),
NVL(SUM(acctd_amount),0)
INTO l_actual_amount1,
l_acctd_amount1
FROM ra_customer_trx t,
ra_cust_trx_types ty,
ar_adjustments a
WHERE t.cust_trx_type_id = ty.cust_trx_type_id
and a.subsequent_trx_id IN
(select cma.customer_trx_id
from ar_receivable_applications cma
where cma.applied_customer_trx_id =
t.customer_trx_id
and cma.application_type = 'CM')
and ty.type not in ('DEP', 'GUAR')
and a.adjustment_type = 'C'
and t.customer_trx_id = p_customer_trx_id
-- following was added for Bug # 3702956
AND adjustment_id NOT IN
(
SELECT adjustment_id
FROM ar_adjustments aa1
WHERE aa1.customer_trx_id = t.customer_trx_id
AND aa1.adjustment_type = 'C'
)
AND adjustment_id NOT IN
(
SELECT adjustment_id
FROM ar_adjustments aa2
WHERE aa2.subsequent_trx_id = t.customer_trx_id
AND aa2.adjustment_type = 'C'
);
Select count(customer_Trx_line_id)
INTO l_count
from ar_activity_details
WHERE customer_trx_line_id in
(SELECT customer_trx_line_id
FROM RA_CUSTOMER_TRX_LINES
WHERE customer_trx_id = p_customer_trx_id)
and nvl(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y'; -- bug 7241111
SELECT sob.currency_code,
precision,
minimum_accountable_unit
INTO l_base_curr_code,
l_base_precision,
l_base_min_acc_unit
FROM fnd_currencies fc,
gl_sets_of_books sob
WHERE sob.set_of_books_id = arp_global.set_of_books_id
AND sob.currency_code = fc.currency_code;
select customer_Trx_line_id
into l_customer_trx_line_id
from ra_customer_Trx_lines
where line_number = p_line_num
and line_type = 'LINE'
and customer_trx_id = p_customer_trx_id;
select sum(DECODE (lines.line_type,
'TAX',0,
'FREIGHT',0 , 1) *
DECODE(ct.complete_flag, 'N',
0, lines.extended_amount)), -- line_original
sum(DECODE (lines.line_type,
'TAX',1,0) *
DECODE(ct.complete_flag,
'N', 0,
lines.extended_amount )) tax_original, -- tax_original
sum(DECODE (lines.line_type,
'FREIGHT', 1,0) *
DECODE(ct.complete_flag,
'N', 0 ,
lines.extended_amount)) -- freight_original
INTO l_line_original,
l_tax_original,
l_freight_original
from ra_customer_trx ct,
ra_customer_trx_lines lines
where (lines.customer_Trx_line_id = l_customer_trx_line_id or
lines.link_to_cust_trx_line_id = l_customer_trx_line_id)
and ct.customer_Trx_id = lines.customer_trx_id
and ct.customer_trx_id = p_customer_trx_id;
SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
INTO
l_line_receipts,
l_base_line_receipts
FROM ar_distributions
WHERE source_table = 'RA'
AND source_id in (select receivable_application_id
from ar_receivable_applications
where status = 'APP' and
applied_customer_Trx_id = p_customer_trx_id and
cash_receipt_id is not null )
AND ref_customer_trx_line_id = l_customer_trx_line_id
AND activity_bucket = 'APP_LINE'
AND ref_account_class = 'REV';
SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
INTO
l_tax_receipts,
l_base_tax_receipts
FROM ar_distributions
WHERE source_table = 'RA'
AND source_id in (select receivable_application_id
from ar_receivable_applications
where status = 'APP' and
applied_customer_Trx_id = p_customer_trx_id and
cash_receipt_id is not null )
AND tax_link_id = l_customer_trx_line_id
AND activity_bucket = 'APP_TAX'
AND ref_account_class = 'TAX';
SELECT
nvl(sum(nvl(charges,0)),0),
nvl(sum(nvl(freight_discount,0)),0)
INTO
l_charges_receipts,
l_freight_discount
FROM AR_ACTIVITY_DETAILS act,
ra_customer_trx_lines line
WHERE line.customer_Trx_id = p_customer_trx_id
and line.line_number = p_line_num
and line.line_type = 'LINE'
and nvl(act.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- bug 7241111
and line.customer_Trx_line_id = act.customer_Trx_line_id;
SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
INTO
l_freight_receipts,
l_base_freight_receipts
FROM ar_distributions ard,
ra_customer_trx_lines ctl
WHERE ard.source_table = 'RA'
AND ard.source_id in (select receivable_application_id
from ar_receivable_applications
where status = 'APP' and
applied_customer_Trx_id = p_customer_trx_id and
cash_receipt_id is not null )
AND ctl.link_to_cust_trx_line_id = l_customer_trx_line_id
AND ard.ref_customer_trx_line_id = ctl.customer_trx_line_id
AND ctl.line_type = 'FREIGHT'
AND ard.activity_bucket = 'APP_FRT'
AND ard.ref_account_class = 'FREIGHT';
SELECT NVL(sum(NVL(amount_dr,0) - NVL(amount_cr,0)),0),
NVL(sum(NVL(acctd_amount_dr,0) - NVL(acctd_amount_cr,0)),0)
INTO
l_tax_discount,
l_base_tax_discount
FROM ar_distributions
WHERE source_table = 'RA'
AND source_id in (select receivable_application_id
from ar_receivable_applications
where status = 'APP' and
applied_customer_Trx_id = p_customer_trx_id and
cash_receipt_id is not null )
AND tax_link_id = l_customer_trx_line_id
AND activity_bucket IN ('ED_TAX', 'UNED_TAX')
AND ref_account_class = 'TAX';
SELECT NVL(sum(NVL(amount_dr,0) - NVL(amount_cr,0)),0),
NVL(sum(NVL(acctd_amount_dr,0) - NVL(acctd_amount_cr,0)),0)
INTO
l_line_discount,
l_base_line_discount
FROM ar_distributions
WHERE source_table = 'RA'
AND source_id in (select receivable_application_id
from ar_receivable_applications
where status = 'APP' and
applied_customer_Trx_id = p_customer_trx_id and
cash_receipt_id is not null )
AND ref_customer_trx_line_id = l_customer_trx_line_id
AND activity_bucket in ('ED_LINE', 'UNED_LINE')
AND ref_account_class = 'REV';
SELECT NVL(sum(NVL(amount_dr,0) - NVL(amount_cr,0)),0),
NVL(sum(NVL(acctd_amount_dr,0) - NVL(acctd_amount_cr,0)),0)
INTO
l_line_credits,
l_base_line_credits
from ar_receivable_applications rec,
ar_distributions dist
where rec.applied_customer_trx_id = p_customer_trx_id
and dist.ref_customer_trx_line_id = l_customer_trx_line_id
and rec.status = 'APP'
and rec.application_type = 'CM'
and dist.source_table = 'RA'
and dist.source_id = rec.receivable_application_id
and activity_bucket = 'APP_LINE'
and ref_account_class = 'REV';
SELECT NVL(sum(NVL(amount_dr,0) - NVL(amount_cr,0)),0),
NVL(sum(NVL(acctd_amount_dr,0) - NVL(acctd_amount_cr,0)),0)
INTO
l_tax_credits,
l_base_tax_credits
from ar_receivable_applications rec,
ar_distributions dist
where rec.applied_customer_trx_id = p_customer_trx_id
and dist.ref_customer_trx_line_id = l_customer_trx_line_id
and rec.status = 'APP'
and rec.application_type = 'CM'
and dist.source_table = 'RA'
and dist.source_id = rec.receivable_application_id
and activity_bucket = 'APP_TAX'
and ref_account_class = 'REV';
SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
INTO l_line_adjustments,
l_base_line_adjustments
from ar_distributions dist
where dist.ref_customer_trx_line_id = l_customer_trx_line_id
and dist.source_table = 'ADJ'
and dist.activity_bucket = 'ADJ_LINE';
SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
INTO l_tax_adjustments,
l_base_tax_adjustments
from ar_distributions dist,
ra_customer_trx_lines lines
where lines.link_to_cust_trx_line_id = l_customer_trx_line_id
and lines.line_type = 'TAX'
and dist.ref_customer_trx_line_id = lines.customer_trx_line_id
and dist.source_table = 'ADJ'
and dist.activity_bucket = 'ADJ_TAX';
SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
INTO l_freight_adjustments,
l_base_freight_adjustments
from ar_distributions dist,
ra_customer_trx_lines lines
where lines.link_to_cust_trx_line_id = l_customer_trx_line_id
and dist.ref_customer_trx_line_id = lines.link_to_cust_trx_line_id
and lines.line_type = 'FREIGHT'
and dist.source_table = 'ADJ'
and dist.activity_bucket = 'ADJ_FRT';