[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cust.cust_account_id
INTO l_dummy_cust.customer_id
FROM hz_cust_accounts cust,
hz_customer_profiles cp,
hz_parties party
WHERE cust.cust_account_id = cp.cust_account_id (+) and
cp.site_use_id is null and
cust.cust_account_id = p_customer_id and
cust.party_id = party.party_id;
SELECT site_uses.site_use_id
INTO l_dummy_cust.site_use_id
FROM hz_cust_site_uses_all site_uses,
hz_cust_acct_sites acct_site
WHERE acct_site.cust_account_id = p_customer_id
/*AND acct_site.status = 'A' Bug 4317815*/
AND acct_site.cust_acct_site_id =
site_uses.cust_acct_site_id
AND site_uses.site_use_code IN ('BILL_TO','DRAWEE')
/*AND site_uses.status = 'A' Bug 4317815*/
AND site_uses.site_use_id = p_customer_site_use_id;
/* SELECT ba.bank_account_id
INTO l_dummy_cust.bank_account_id
FROM ap_bank_accounts ba,
ap_bank_account_uses bau
WHERE ba.bank_account_id = bau.external_bank_account_id
and bau.customer_id = p_customer_id
and (bau.customer_site_use_id is null
or bau.customer_site_use_id = p_customer_site_use_id)
and (ba.currency_code = p_currency_code or
ba.bank_branch_id = 1)
-- OSTEINME 2/27/2001: change for iReceivables:
-- for credit card bank accounts the currency is
-- irrelevant. See bug 1659130
and p_receipt_date
between nvl(bau.start_date,p_receipt_date)
and nvl(bau.end_date,p_receipt_date)
and nvl(ba.inactive_date,p_receipt_date) >=
p_receipt_date
and ba.bank_account_id = p_customer_bank_account_id; */
select bb.bank_account_id
into l_dummy_cust.bank_account_id
from iby_fndcpt_payer_assgn_instr_v a,
iby_ext_bank_accounts_v bb
where a.cust_account_id = p_customer_id
and a.instrument_type = 'BANKACCOUNT'
and ( a.acct_site_use_id = p_customer_site_use_id or a.acct_site_use_id is null)
and p_receipt_date between nvl(trunc(bb.start_date),p_receipt_date)
and nvl(trunc(bb.end_date),p_receipt_date)
and a.currency_code = p_currency_code
and bb.ext_bank_account_id = a.instrument_id
and bb.bank_account_id = p_customer_bank_account_id;
SELECT rm.receipt_method_id,
ba.bank_acct_use_id,
rc.creation_method_code,
rc.remit_flag,
cba.currency_code
INTO l_dummy_method.method_id,
l_dummy_method.bank_account_id,
l_dummy_method.state,
l_dummy_method.remit_flag,
l_dummy_method.currency
FROM ar_receipt_methods rm,
ce_bank_accounts cba,
ce_bank_acct_uses_ou ba,
ar_receipt_method_accounts rma,
ar_receipt_classes rc
WHERE rm.receipt_method_id = p_receipt_method_id
and (p_receipt_date between rm.start_date and nvl(rm.end_date, p_receipt_date))
and ((rc.creation_method_code = DECODE(p_called_from,'BR_REMITTED','BR_REMIT',
'BR_FACTORED_WITH_RECOURSE','BR_REMIT',
'BR_FACTORED_WITHOUT_RECOURSE','BR_REMIT','@*%?&')) or
(rc.creation_method_code = 'MANUAL') or
(rc.creation_method_code = 'NETTING') or
(rc.creation_method_code = 'AUTOMATIC' and
-- rc.remit_flag = 'Y' and
-- OSTEINME 2/27/2001: removed remit_flag
-- condition for iReceivables CC functionality.
-- See bug 1659109.
rc.confirm_flag = decode(p_called_from, 'AUTORECAPI',rc.confirm_flag,'N')))
and cba.account_classification = 'INTERNAL'
and nvl(ba.end_date, p_receipt_date +1) > p_receipt_date
and p_receipt_date between rma.start_date and
nvl(rma.end_date, p_receipt_date)
and cba.currency_code = decode(cba.receipt_multi_currency_flag, 'Y',
cba.currency_code, p_currency_code)
and rc.receipt_class_id = rm.receipt_class_id
and rm.receipt_method_id = rma.receipt_method_id
and rma.remit_bank_acct_use_id = ba.bank_acct_use_id
and ba.bank_account_id = cba.bank_account_id
--APANDIT: changes made for the misc receipt creation api.
and ((nvl(p_called_from,'*&#$') <> 'MISC')
or
(rm.receipt_class_id not in (
SELECT arc.receipt_class_id
FROM ar_receipt_classes arc
WHERE arc.notes_receivable='Y'
or arc.bill_of_exchange_flag='Y')));
SELECT rm.receipt_method_id,
ba.bank_acct_use_id,
rc.creation_method_code,
rc.remit_flag,
cba.currency_code
INTO l_dummy_method.method_id,
l_dummy_method.bank_account_id,
l_dummy_method.state,
l_dummy_method.remit_flag,
l_dummy_method.currency
FROM ar_receipt_methods rm,
ce_bank_accounts cba,
ce_bank_acct_uses_ou ba,
ar_receipt_method_accounts rma,
ar_receipt_classes rc
WHERE rm.receipt_method_id = p_receipt_method_id
and rma.remit_bank_acct_use_id = p_remittance_bank_account_id
and (p_receipt_date between rm.start_date and nvl(rm.end_date, p_receipt_date))
and ((rc.creation_method_code = DECODE(p_called_from,'BR_REMITTED','BR_REMIT',
'BR_FACTORED_WITH_RECOURSE','BR_REMIT',
'BR_FACTORED_WITHOUT_RECOURSE','BR_REMIT','@*%?&')) or
(rc.creation_method_code = 'MANUAL') or
(rc.creation_method_code = 'NETTING') or
(rc.creation_method_code = 'AUTOMATIC' and
-- rc.remit_flag = 'Y' and
-- OSTEINME 2/27/2001: removed remit_flag
-- condition for iReceivables CC functionality.
-- See bug 1659109.
-- bichatte autorecapi.
rc.confirm_flag = decode(p_called_from, 'AUTORECAPI',rc.confirm_flag,'N')))
and cba.account_classification = 'INTERNAL'
and nvl(ba.end_date, p_receipt_date +1) > p_receipt_date
and p_receipt_date between rma.start_date and
nvl(rma.end_date, p_receipt_date)
and cba.currency_code = decode(cba.receipt_multi_currency_flag, 'Y',
cba.currency_code, p_currency_code)
and rc.receipt_class_id = rm.receipt_class_id
and rm.receipt_method_id = rma.receipt_method_id
and rma.remit_bank_acct_use_id = ba.bank_acct_use_id
and ba.bank_account_id = cba.bank_account_id
--APANDIT: changes made for the misc receipt creation api.
and ((nvl(p_called_from,'*&#$') <> 'MISC')
or
(rm.receipt_class_id not in (
SELECT arc.receipt_class_id
FROM ar_receipt_classes arc
WHERE arc.notes_receivable='Y'
OR arc.bill_of_exchange_flag='Y')));
SELECT 'Y'
INTO l_exchange_rate_valid
FROM gl_daily_conversion_types
WHERE conversion_type = p_exchange_rate_type;
SELECT 'Y'
INTO l_currency_valid
FROM fnd_currencies
WHERE p_currency_code = currency_code;
SELECT 'Y'
FROM ar_cash_receipts cr
WHERE cr.receipt_number = p_receipt_number
AND cr.receipt_date = p_receipt_date
AND cr.amount = p_amount
AND NVL(cr.pay_from_customer, -99999) = NVL(p_customer_id, -99999)
AND cr.type = p_type
AND cr.status NOT IN (
SELECT arl.lookup_code FROM ar_lookups arl
WHERE arl.lookup_type = 'REVERSAL_CATEGORY_TYPE');
SELECT amount_applied,
amount_applied_from INTO l_amount_applied,l_amount_applied_from
FROM ar_receivable_applications
WHERE receivable_application_id = p_receivable_application_id;
Select max(apply_date) , max(gl_date)
into l_apply_date , l_gl_date
from ar_receivable_applications
where cash_receipt_id = p_cash_receipt_id;
SELECT count(*)
INTO l_valid
FROM AR_RECEIVABLE_APPLICATIONS ra
WHERE ra.receivable_application_id = p_receivable_application_id
and ra.display = 'Y'
and ra.status = p_application_type
and ra.application_type = 'CASH';
SELECT COUNT(payment_schedule_id)
INTO l_dummy
FROM ar_payment_schedules ps,
ra_cust_trx_line_gl_dist rctlg
WHERE ps.associated_cash_receipt_id = p_cash_receipt_id
AND ps.class = 'CB'
AND ps.customer_trx_id = rctlg.customer_trx_id
AND ( nvl(ps.amount_applied, 0) <> 0
OR nvl(ps.amount_credited, 0) <> 0
OR 0 <> ( SELECT sum(adj.amount)
FROM ar_adjustments adj
WHERE adj.payment_schedule_id =
ps.payment_schedule_id
AND adj.receivables_trx_id <> -12
)
);
SELECT 'Y'
INTO l_std_appln
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND ra.status = 'ACTIVITY'
AND ra.applied_payment_schedule_id = -2
AND display = 'Y'
AND p_called_from NOT IN ('BR_REMITTED',
'BR_FACTORED_WITH_RECOURSE',
'BR_FACTORED_WITHOUT_RECOURSE'); --fixed bug 1450460
SELECT 'Y'
INTO l_reserved
FROM ar_payment_schedules ps,
ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND ra.applied_payment_schedule_id = ps.payment_schedule_id
AND ps.reserved_type IS NOT NULL
AND ps.reserved_value IS NOT NULL
AND ra.status = 'APP'
AND ra.display = 'Y';
SELECT count(*)
INTO l_valid
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh
WHERE cr.cash_receipt_id = p_cash_receipt_id
and cr.cash_receipt_id = crh.cash_receipt_id
and crh.current_record_flag = 'Y'
and crh.status
IN (p_status1,p_status2,p_status3,p_status4,p_status5);
SELECT count(*)
INTO l_valid
FROM ar_lookups
WHERE lookup_type = 'REVERSAL_CATEGORY_TYPE'
and enabled_flag = 'Y'
and lookup_code = p_reversal_category_code;
SELECT count(*)
INTO l_valid
FROM ar_lookups
WHERE lookup_type = 'CKAJST_REASON'
and enabled_flag = 'Y'
and lookup_code = p_reversal_reason_code;
SELECT NVL(arm.payment_channel_code,'NONE')
INTO l_payment_type
FROM ar_cash_receipts cr,
ar_receipt_methods arm
WHERE cr.receipt_method_id = arm.receipt_method_id
AND cr.cash_receipt_id=p_cash_receipt_id;
SELECT status INTO l_status
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id
AND current_record_flag = 'Y';
select type
from ar_receivables_trx rt
where receivables_trx_id = p_receivables_trx_id;
SELECT /*+ index(ra AR_RECEIVABLE_APPLICATIONS_N1) */SUM(AMOUNT_APPLIED)
--sum(amount_applied)
INTO l_existing_wo_amount
FROM ar_receivable_applications ra
WHERE applied_payment_schedule_id = -3
AND status = 'ACTIVITY'
AND NVL(confirmed_flag,'Y') = 'Y'
AND cash_receipt_id = p_cash_receipt_id;
SELECT nvl(exchange_rate,1)
INTO l_exchange_rate
FROM ar_cash_receipts
WHERE cash_receipt_id = p_cash_receipt_id;
SELECT NVL(amount_from,0),
NVL(amount_to,0)
INTO l_amount_from,
l_amount_to
FROM ar_approval_user_limits
where currency_code = p_cr_currency_code
and user_id = l_user_id
and document_type ='WRTOFF';
SELECT MAX_WRTOFF_AMOUNT,
MIN_WRTOFF_AMOUNT,
sob.currency_code
INTO l_max_wrt_off_amount,
l_min_wrt_off_amount,
l_functional_currency
FROM AR_SYSTEM_PARAMETERS sys,gl_sets_of_books sob
WHERE sys.set_of_books_id = sob.set_of_books_id;
SELECT 'Y'
INTO l_valid
FROM ar_payment_schedules
WHERE customer_trx_id=p_link_to_customer_trx_id
AND class='CB';
SELECT 'Y'
INTO l_valid
FROM ar_transaction_history
WHERE status IN ('FACTORED', 'MATURED_PEND_RISK_ELIMINATION',
'PENDING_REMITTANCE','CLOSED')
AND customer_trx_id = p_link_to_customer_trx_id
AND current_record_flag = 'Y';
SELECT 'Y'
INTO l_valid
FROM ar_lookups
WHERE lookup_type = DECODE(p_applied_ps_id,-4,'APPLICATION_REF_TYPE',
-5,'CHARGEBACK',
-6, 'MISC_RECEIPT',
-7,'AR_PREPAYMENT_TYPE',
'NONE')
AND enabled_flag = 'Y'
AND lookup_code = p_application_ref_type;
' select reason_code_id from ozf_reason_codes_vl '||
' where reason_code_id = :application_ref_reason '||
' and sysdate between nvl(start_date_active,sysdate) '||
' and nvl(end_date_active,sysdate) ';
' select claim_id from ozf_ar_deductions_v ';
SELECT rt.default_acctg_distribution_set
INTO p_distribution_set_id
FROM ar_receivables_trx rt
WHERE rt.receivables_trx_id = p_receivables_trx_id
AND rt.type in
('MISCCASH', 'BANK_ERROR', 'CCREFUND', 'CM_REFUND','CC_CHARGEBACK')
AND nvl(rt.status, 'A') = 'A'
AND p_receipt_date >= nvl(rt.start_date_active, p_receipt_date)
AND p_receipt_date <= nvl(rt.end_date_active, p_receipt_date);
SELECT percentage_rate
INTO l_tax_rate
FROM zx_sco_rates vt
WHERE p_receipt_date between
nvl(vt.effective_from, p_receipt_date)
and nvl(vt.effective_to, p_receipt_date)
AND (vt.tax_class = decode(sign(p_amount), 1, 'OUTPUT',
0, 'OUTPUT',-1, 'INPUT') OR vt.tax_class IS NULL) -- Added condition --> vt.tax_class IS NULL to handle (bug 8648248)
AND vt.tax_rate_id = p_vat_tax_id; -- the Miscellaneous Receipt creation through Standard API
SELECT tax_rate, validate_flag
INTO l_tax_rate, l_tax_validate_flag
FROM ar_vat_tax vt
WHERE p_receipt_date between
nvl(vt.start_date, p_receipt_date)
and nvl(vt.end_date, p_receipt_date)
AND vt.set_of_books_id = arp_global.set_of_books_id
AND vt.tax_class = decode(sign(p_amount), 1, 'O', 0, 'O', -1, 'I')
AND vt.enabled_flag='Y'
AND vt.tax_type <> 'TAX_GROUP'
AND vt.tax_type <> 'LOCATION'
AND vt.tax_type <> 'SALES_TAX'
AND vt.displayed_flag='Y'
AND vt.vat_tax_id = p_vat_tax_id; */
IF fnd_profile.value('ZX_ALLOW_TAX_UPDATE') = 'N'
THEN
l_tax_rate_return_status := FND_API.G_RET_STS_ERROR;
select 'y'
into l_reference_valid
from ap_checks
where check_id = p_reference_id /* Bug fix 2982212 */
and ce_bank_acct_use_id = p_remittance_bank_account_id;/*bug8449826*/
select 'y'
into l_reference_valid
from ap_invoice_selection_criteria isc
where isc.checkrun_id = p_reference_id /* Bug fix 2982212 */
and bank_account_id = p_remittance_bank_account_id;
select 'y'
into l_reference_valid
from ar_cash_receipts
where cash_receipt_id = p_reference_id
and remit_bank_acct_use_id = p_remittance_bank_account_id;
select 'y'
into l_reference_valid
from ar_batches
where batch_id = p_reference_id /* Bug fix 2982212 */
and type = 'REMITTANCE'
and remit_bank_acct_use_id = p_remittance_bank_account_id;
select 'y'
into l_reference_valid
from ra_customer_trx
where customer_trx_id = p_reference_id;
SELECT sum(nvl(amount_applied,0))
INTO l_prepay_amount
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cash_receipt_id
AND applied_payment_schedule_id = p_applied_ps_id
AND display = 'Y'
AND status = 'OTHER ACC';
SELECT sum(nvl(amount_applied,0))
INTO l_prepay_amount
FROM ar_receivable_applications
WHERE receivable_application_id = p_receivable_application_id
AND display = 'Y'
AND applied_payment_schedule_id = p_applied_ps_id
AND status = 'OTHER ACC';
SELECT NVL(payment_channel_code,'CASH')
INTO l_payment_type_code
FROM ar_receipt_methods arm,
ar_cash_receipts cr
WHERE cr.receipt_method_id = arm.receipt_method_id
AND cr.cash_receipt_id=l_cash_receipt_id;
SELECT NVL(payment_channel_code,'CASH')
INTO l_payment_type_code
FROM ar_receipt_methods arm,
ar_cash_receipts cr,
ar_receivable_applications app
WHERE cr.receipt_method_id = arm.receipt_method_id
AND app.cash_receipt_id=cr.cash_receipt_id
AND app.receivable_application_id = p_receivable_application_id;
arp_process_application.update_claim(
p_claim_id => l_secondary_app_ref_id
, p_invoice_ps_id => p_invoice_ps_id
, p_customer_trx_id => p_customer_trx_id
, p_amount => 0
, p_amount_applied => p_amount_applied
, p_apply_date => SYSDATE
, p_cash_receipt_id => p_cash_receipt_id
, p_receipt_number => p_receipt_number
, p_action_type => 'U'
, x_claim_reason_code_id => l_claim_reason_code_id
, x_claim_reason_name => l_claim_reason_name
, x_claim_number => l_claim_number
, x_return_status => p_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
SELECT name, code_combination_id
INTO l_activity_name, l_ccid
FROM ar_receivables_trx
WHERE receivables_trx_id = -16;
SELECT sob.currency_code
INTO l_func_currency
FROM ar_system_parameters sp,
gl_sets_of_books sob
WHERE sp.set_of_books_id = sob.set_of_books_id;
SELECT amount
INTO l_cr_amount
FROM ar_cash_receipts
WHERE cash_receipt_id = p_applied_cash_receipt_id;
SELECT NVL(SUM(amount_applied),0)
INTO l_amount_applied
FROM ar_receivable_applications
WHERE cash_receipt_id = p_applied_cash_receipt_id
AND display = 'Y';
PROCEDURE validate_llca_insert_ad(
p_cash_receipt_id IN NUMBER
,p_customer_trx_id IN NUMBER
,p_customer_trx_line_id IN NUMBER
,p_cr_unapp_amount IN NUMBER
,p_llca_type IN VARCHAR2
,p_group_id IN VARCHAR2
,p_line_amount IN NUMBER
,p_tax_amount IN NUMBER
,p_freight_amount IN NUMBER
,p_charges_amount IN NUMBER
,p_line_discount IN NUMBER
,p_tax_discount IN NUMBER
,p_freight_discount IN NUMBER
,p_amount_applied IN NUMBER
,p_amount_applied_from IN NUMBER
,p_trans_to_receipt_rate IN NUMBER
,p_invoice_currency_code IN VARCHAR2
,p_receipt_currency_code IN VARCHAR2
,p_earned_discount IN NUMBER
,p_unearned_discount IN NUMBER
,p_max_discount IN NUMBER
,p_line_items_original IN NUMBER
,p_line_items_remaining IN NUMBER
,p_tax_original IN NUMBER
,p_tax_remaining IN NUMBER
,p_freight_original IN NUMBER
,p_freight_remaining IN NUMBER
,p_rec_charges_charged IN NUMBER
,p_rec_charges_remaining IN NUMBER
,p_attribute_category IN VARCHAR2
,p_attribute1 IN VARCHAR2
,p_attribute2 IN VARCHAR2
,p_attribute3 IN VARCHAR2
,p_attribute4 IN VARCHAR2
,p_attribute5 IN VARCHAR2
,p_attribute6 IN VARCHAR2
,p_attribute7 IN VARCHAR2
,p_attribute8 IN VARCHAR2
,p_attribute9 IN VARCHAR2
,p_attribute10 IN VARCHAR2
,p_attribute11 IN VARCHAR2
,p_attribute12 IN VARCHAR2
,p_attribute13 IN VARCHAR2
,p_attribute14 IN VARCHAR2
,p_attribute15 IN VARCHAR2
,p_comments IN VARCHAR2
,p_return_status OUT NOCOPY VARCHAR2
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
) IS
cursor all_lines_in_grp (p_cust_trx_id in number,
p_grp_id in number) is
select to_char(line.line_number) apply_to,
line.customer_trx_line_id LINE_ID,
nvl(line.source_data_key4,0) GROUP_ID ,
nvl(line.amount_due_remaining,0) line_to_apply,
nvl(tax.amount_due_remaining,0) tax_to_apply
from ra_customer_trx_lines line,
(select link_to_cust_trx_line_id,
line_type,
sum(nvl(amount_due_original,0)) amount_due_original,
sum(nvl(amount_due_remaining,0)) amount_due_remaining
from ra_customer_trx_lines
where customer_trx_id = p_cust_trx_id -- Bug 7241703 Added condition
and nvl(line_type,'TAX') = 'TAX'
group by link_to_cust_trx_line_id,line_type
) tax
where line.customer_Trx_id = p_cust_trx_id
and line.line_type = 'LINE'
and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+)
and line.source_data_key4 = p_grp_id;
select to_char(line.line_number) apply_to,
line.customer_trx_line_id line_id,
nvl(line.source_data_key4,0) group_id ,
nvl(line.amount_due_remaining,0) line_to_apply,
nvl(tax.amount_due_remaining,0) tax_to_apply
from ra_customer_trx_lines line,
(select link_to_cust_trx_line_id,
line_type,
sum(nvl(amount_due_original,0)) amount_due_original,
sum(nvl(amount_due_remaining,0)) amount_due_remaining
from ra_customer_trx_lines
where customer_trx_id = p_cust_trx_id -- Bug 7241703 Added condition
and nvl(line_type,'TAX') = 'TAX'
group by link_to_cust_trx_line_id,line_type
) tax
where line.customer_Trx_id = p_cust_trx_id
and line.line_type = 'LINE'
and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+)
order by line_number;
select * from ar_llca_trx_lines_gt
where customer_trx_id = p_cust_trx_id;
arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_ad()+');
ar_ll_rcv_summary_pkg.insert_row(
x_cash_receipt_id => p_cash_receipt_id,
x_customer_trx_id => p_customer_trx_id,
x_lin => p_line_amount,
x_tax => p_tax_amount,
x_frt => p_freight_amount,
x_chg => p_charges_amount,
x_lin_dsc => p_line_discount,
x_tax_dsc => p_tax_discount,
x_frt_dsc => p_freight_discount,
x_created_by_module => 'RAPI'
,x_inv_curr_code => p_invoice_currency_code
,x_inv_to_rct_rate => p_trans_to_receipt_rate
,x_rct_curr_code => p_receipt_currency_code
,x_attribute_category => p_attribute_category
,x_attribute1 => p_attribute1
,x_attribute2 => p_attribute2
,x_attribute3 => p_attribute3
,x_attribute4 => p_attribute4
,x_attribute5 => p_attribute5
,x_attribute6 => p_attribute6
,x_attribute7 => p_attribute7
,x_attribute8 => p_attribute8
,x_attribute9 => p_attribute9
,x_attribute10 => p_attribute10
,x_attribute11 => p_attribute11
,x_attribute12 => p_attribute12
,x_attribute13 => p_attribute13
,x_attribute14 => p_attribute14
,x_attribute15 => p_attribute15
);
Select decode ( ( Nvl(line_grp.line_to_apply,0)
/ (Nvl(line_grp.line_to_apply,0)
+ Nvl(line_grp.tax_to_apply,0)
)
),0,1,
( Nvl(line_grp.line_to_apply,0)
/ (Nvl(line_grp.line_to_apply,0)
+ Nvl(line_grp.tax_to_apply,0)
)
)
)
into l_calc_line_per
from dual;
ar_activity_details_pkg.insert_row (
x_rowid => l_rowid,
x_cash_receipt_id => p_cash_receipt_id,
x_customer_trx_line_id => line_grp.line_id,
x_allocated_receipt_amount => Nvl(l_calc_tot_amount_app,0),
x_amount => Nvl(l_calc_line_amount,0),
x_tax => Nvl(l_calc_tax_amount,0),
x_line_discount => '',
x_tax_discount => '',
x_line_balance => line_grp.line_to_apply,
x_tax_balance => Nvl(line_grp.tax_to_apply,0),
x_apply_to => line_grp.apply_to,
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15,
x_comments => p_comments,
x_group_id => line_grp.group_id,
x_object_version_number => 1,
x_created_by_module => 'RAPI',
x_reference1 => '',
x_reference2 => '',
x_reference3 => '',
x_reference4 => '',
x_reference5 => ''
);
select count(*)
into l_gt_count
from ar_llca_trx_lines_gt
where customer_trx_id = p_customer_trx_id
and rownum = 1;
Select decode ( ( Nvl(All_lines_row.line_to_apply,0)
/ (Nvl(All_lines_row.line_to_apply,0)
+ Nvl(All_lines_row.tax_to_apply,0)
)
),0,1,
( Nvl(All_lines_row.line_to_apply,0)
/ (Nvl(All_lines_row.line_to_apply,0)
+ Nvl(All_lines_row.tax_to_apply,0)
)
)
)
into l_calc_line_per
from dual;
ar_activity_details_pkg.insert_row (
x_rowid => l_rowid,
x_cash_receipt_id => p_cash_receipt_id,
x_customer_trx_line_id => All_lines_row.line_id,
x_allocated_receipt_amount => Nvl(l_calc_amount_app_from,0),
x_amount => Nvl(l_calc_line_amount,0),
x_tax => Nvl(l_calc_tax_amount,0),
x_line_discount => '',
x_tax_discount => '',
x_line_balance => All_lines_row.line_to_apply,
x_tax_balance => Nvl(All_lines_row.tax_to_apply,0),
x_apply_to => All_lines_row.apply_to,
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15,
x_comments => p_comments,
x_group_id => All_lines_row.group_id,
x_object_version_number => 1,
x_created_by_module => 'RAPI',
x_reference1 => '',
x_reference2 => '',
x_reference3 => '',
x_reference4 => '',
x_reference5 => ''
);
select nvl(line.source_data_key4,0) group_id,
nvl(line.amount_due_remaining,0),
nvl(tax.amount_due_remaining,0)
into
l_group_id,
l_line_amount_remaining,
l_line_tax_remaining
from ra_customer_trx_lines line,
(select link_to_cust_trx_line_id,
line_type,
sum(nvl(amount_due_original,0)) amount_due_original,
sum(nvl(amount_due_remaining,0)) amount_due_remaining
from ra_customer_trx_lines
where customer_trx_id = sp_lines_row.customer_trx_id -- Bug 7241703 Added condition
and nvl(line_type,'TAX') = 'TAX'
group by link_to_cust_trx_line_id,line_type
) tax
where line.customer_Trx_id = sp_lines_row.customer_trx_id
and line.customer_trx_line_id = sp_lines_row.customer_trx_line_id
and line.line_type = 'LINE'
and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+);
arp_util.debug('' || 'EXCEPTION: validate_llac_insert_ad()');
ar_activity_details_pkg.insert_row(
x_rowid => l_rowid,
x_cash_receipt_id => p_cash_receipt_id,
x_customer_trx_line_id => sp_lines_row.customer_trx_line_id,
x_allocated_receipt_amount => Nvl(l_calc_amount_app_from,0),
x_amount => Nvl(sp_lines_row.line_amount,0),
x_tax => Nvl(sp_lines_row.tax_amount,0),
x_line_discount => Nvl(sp_lines_row.line_discount,0),
x_tax_discount => Nvl(sp_lines_row.tax_discount,0),
x_line_balance => l_line_amount_remaining,
x_tax_balance => l_line_tax_remaining,
x_apply_to => sp_lines_row.line_number,
x_attribute_category => sp_lines_row.attribute_category,
x_attribute1 => sp_lines_row.attribute1,
x_attribute2 => sp_lines_row.attribute2,
x_attribute3 => sp_lines_row.attribute3,
x_attribute4 => sp_lines_row.attribute4,
x_attribute5 => sp_lines_row.attribute5,
x_attribute6 => sp_lines_row.attribute6,
x_attribute7 => sp_lines_row.attribute7,
x_attribute8 => sp_lines_row.attribute8,
x_attribute9 => sp_lines_row.attribute9,
x_attribute10 => sp_lines_row.attribute10,
x_attribute11 => sp_lines_row.attribute11,
x_attribute12 => sp_lines_row.attribute12,
x_attribute13 => sp_lines_row.attribute13,
x_attribute14 => sp_lines_row.attribute14,
x_attribute15 => sp_lines_row.attribute15,
x_comments => p_comments,
x_group_id => l_group_id,
x_object_version_number => 1,
x_created_by_module => 'RAPI',
x_reference1 => '',
x_reference2 => '',
x_reference3 => '',
x_reference4 => '',
x_reference5 => ''
);
ar_ll_rcv_summary_pkg.insert_frt_rows(
x_cash_receipt_id => p_cash_receipt_id,
x_customer_trx_id => p_customer_trx_id,
x_frt => p_freight_amount,
x_frt_dsc => p_freight_discount,
x_created_by_module => 'RAPI'
,x_inv_curr_code => p_invoice_currency_code
,x_inv_to_rct_rate => p_trans_to_receipt_rate
,x_rct_curr_code => p_receipt_currency_code
,x_comments => NULL
);
arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_ad()-');
arp_util.debug('EXCEPTION: ar_receipt_val_pvt.validate_llac_insert_ad()');
END validate_llca_insert_ad;
PROCEDURE validate_llca_insert_app(
p_cash_receipt_id IN NUMBER
,p_customer_trx_id IN NUMBER
,p_disc_earn_allowed IN NUMBER
,p_disc_max_allowed IN NUMBER
,p_return_status OUT NOCOPY VARCHAR2
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
) IS
cursor rcv_lines_cur (p_cust_trx_id in number, p_cash_rec_id in number) is
select
trx_lines.line_type,
trx_lines.source_data_key1 sdk1,
trx_lines.source_data_key2 sdk2,
trx_lines.source_data_key3 sdk3,
trx_lines.source_data_key4 sdk4,
trx_lines.source_data_key5 sdk5,
trx_lines.customer_Trx_line_id ctl_id,
--
rcv_lines.amount lin,
rcv_lines.tax tax,
rcv_lines.freight frt,
rcv_lines.charges chg,
--
--
rcv_lines.line_discount lin_disc,
rcv_lines.tax_discount tax_disc,
rcv_lines.freight_discount frt_disc,
0 chg_disc,
--
rcv_lines.allocated_receipt_amount
from ar_activity_details rcv_lines,
ra_customer_trx_lines trx_lines
where trx_lines.customer_trx_id = p_cust_trx_id
and rcv_lines.cash_receipt_id = p_cash_rec_id
and nvl(rcv_lines.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- Bug 7241111
and trx_lines.line_type = 'LINE'
and rcv_lines.customer_trx_line_id = trx_lines.customer_trx_line_id;
select trx_lines.line_type,
sum(Nvl(rcv_lines.amount,0)) lin,
sum(Nvl(rcv_lines.tax,0)) tax,
sum(Nvl(rcv_lines.freight,0)) frt,
sum(Nvl(rcv_lines.charges,0)) chg,
sum(Nvl(rcv_lines.line_discount,0)) lin_disc,
sum(Nvl(rcv_lines.tax_discount,0)) tax_disc,
sum(NVl(rcv_lines.freight_discount,0)) frt_disc,
sum(Nvl(rcv_lines.allocated_receipt_amount,0)) allocated
from ar_Activity_details rcv_lines,
ra_customer_trx_lines_all trx_lines
where trx_lines.customer_trx_id = pf_ct_id
and rcv_lines.cash_receipt_id = pf_cr_id
and nvl(rcv_lines.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- Bug 7241111
and trx_lines.line_type in ('FREIGHT','CHARGES')
and rcv_lines.customer_trx_line_id = trx_lines.customer_trx_line_id
group by trx_lines.line_type;
arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_app()+');
arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_app(Line)+');
arp_util.debug('ar_receipt_val_pvt.validate_llac_insert_app()-');
arp_util.debug('EXCEPTION: ar_receipt_val_pvt.validate_llac_insert_app()');
END validate_llca_insert_app;