[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_selected_id VARCHAR2(100);
SELECT c.cust_account_id
INTO l_selected_id
FROM hz_cust_accounts c,
hz_customer_profiles cp,
hz_parties party
WHERE c.cust_account_id = cp.cust_account_id (+) and
cp.site_use_id is null and
c.account_number = p_value
AND c.party_id = party.party_id;
SELECT cust_acct.cust_account_id
INTO l_selected_id
FROM hz_cust_accounts cust_acct,
hz_customer_profiles cp,
hz_parties party
WHERE cust_acct.cust_account_id = cp.cust_account_id (+)
and cust_acct.party_id = party.party_id(+)
and cp.site_use_id is null
and party.party_name = p_value;
SELECT receipt_method_id
INTO l_selected_id
FROM ar_receipt_methods
WHERE name = p_value;
SELECT cbau.bank_acct_use_id
INTO l_selected_id
FROM ce_bank_accounts cba, ce_bank_acct_uses cbau
WHERE bank_account_num = p_value
and cba.bank_account_id = cbau.bank_account_id;
SELECT cbau.bank_acct_use_id
INTO l_selected_id
FROM ce_bank_accounts cba, ce_bank_acct_uses cbau
WHERE bank_account_name = p_value
and cba.bank_account_id = cbau.bank_account_id;
SELECT currency_code
INTO l_selected_id
FROM fnd_currencies_vl
WHERE name = p_value;
SELECT conversion_type
INTO l_selected_id
FROM gl_daily_conversion_types
WHERE user_conversion_type = p_value ;
select check_id
into l_selected_id
from ap_checks
where check_number = p_value;
select isc.checkrun_id
into l_selected_id
from ap_invoice_selection_criteria isc
where isc.checkrun_name = p_value;
select cash_receipt_id
into l_selected_id
from ar_cash_receipts
where receipt_number = p_value;
select batch_id
into l_selected_id
from ar_batches
where name = p_value
and type = 'REMITTANCE' ;
select receivables_trx_id
into l_selected_id
from ar_receivables_trx
where name = p_value;
SELECT tax_rate_id
INTO l_selected_id
from zx_sco_rates
WHERE tax_rate_code = p_value
AND nvl(active_flag, 'Y') = 'Y' /* 4400063 */
AND l_date between
nvl(effective_from, l_date)
and nvl(effective_to, l_date);
SELECT ba.account_owner_org_id
INTO l_selected_id
FROM ce_bank_accounts ba, ce_bank_acct_uses bau
WHERE ba.bank_account_id = bau.bank_account_id
AND bau.bank_acct_use_id = p_value;
l_selected_id,
NVL( l_index, 0 ) + 1
);
arp_util.debug('Get_Id: ' || 'Value selected. Entity: '||
p_entity || ',' ||
' Value: ' || p_value || ',' ||
'ID: ' || l_selected_id);
RETURN( l_selected_id );
l_selected_id hz_cust_site_uses.site_use_id%type;
SELECT site_use.site_use_id
INTO l_selected_id
FROM hz_cust_site_uses site_use,
hz_cust_acct_sites acct_site
WHERE acct_site.cust_account_id = p_customer_id
AND acct_site.status = 'A'
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND (site_use.site_use_code = nvl(p_site_use_code1,
site_use.site_use_code) OR
site_use.site_use_code = nvl(p_site_use_code1,
site_use.site_use_code))
AND site_use.status = 'A'
AND site_use.location = p_location;
SELECT site_use.site_use_id
INTO l_selected_id
FROM hz_cust_site_uses site_use,
hz_cust_acct_sites acct_site
WHERE acct_site.cust_account_id = p_customer_id
AND acct_site.status = 'A'
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND (site_use.site_use_code = nvl(p_site_use_code1,
site_use.site_use_code) OR
site_use.site_use_code = nvl(p_site_use_code1,
site_use.site_use_code))
AND site_use.status = 'A'
AND site_use.primary_flag = 'Y';
l_selected_id,
NVL( l_index, 0 ) + 1
);
RETURN( l_selected_id );
RETURN( l_selected_id );
SELECT ba.bank_acct_use_id,
rc.creation_status,
rc.creation_method_code,
rma.start_date rma_start_date,
rma.end_date rma_end_date,
rm.start_date rm_start_date,
rm.end_date rm_end_date,
ba.end_Date bau_end_date,
rma.org_id rma_org_id
FROM ar_receipt_methods rm,
ce_bank_accounts cba,
ce_bank_acct_uses 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 = 'AUTOMATIC'
--and rc.remit_flag = 'Y'
-- OSTEINME 2/27/2001: removed remit_flag
-- condition for iReceivables CC functionality.
-- See bug 1659109.
-- bichatte autorecapi.
and 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')))
order by
decode(rma.primary_flag,'Y',1,'N',2,3),
decode(cba.currency_code,p_currency_code,1,2),
cba.bank_branch_id,
cba.bank_account_name,
ba.bank_acct_use_id;
SELECT rc.creation_status,
rc.creation_method_code
INTO p_state,
p_creation_method_code
FROM ar_receipt_classes rc,
ar_receipt_methods rm
WHERE rc.receipt_class_id = rm.receipt_class_id
AND rm.receipt_method_id = p_receipt_method_id;
SELECT rma.cash_ccid,art1.code_combination_id,rma.on_account_ccid,
rma.unapplied_ccid,art2.code_combination_id,rma.unidentified_ccid,
rma.bank_charges_ccid,rma.factor_ccid,rma.remittance_ccid,
rma.receipt_clearing_ccid, rma.short_term_debt_ccid
INTO l_cash_ccid,l_earned_ccid,l_on_account_ccid,
l_unapplied_ccid,l_unearned_ccid,l_unidentified_ccid,
l_bank_charges_ccid,l_factor_ccid,l_remittance_ccid,
l_receipt_clearing_ccid,l_short_term_debt_ccid
FROM ar_receipt_method_accounts rma,
ar_receivables_trx art1, ar_receivables_trx art2
WHERE remit_bank_acct_use_id = p_remittance_bank_account_id
AND receipt_method_id = p_receipt_method_id
AND art1.receivables_trx_id = rma.edisc_receivables_trx_id
AND art2.receivables_trx_id = rma.unedisc_receivables_trx_id;
SELECT name
INTO p_receipt_method_name
FROM ar_receipt_methods
WHERE receipt_method_id = p_receipt_method_id;
select distinct bb.branch_party_id "bank_branch_id",
bb.BANK_ACCOUNT_ID
into p_customer_bank_branch_id
,p_customer_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 a.currency_code = p_currency_code
and bb.ext_bank_account_id = a.instrument_id
and bb.bank_account_name = p_customer_bank_account_name;
select distinct bb.branch_party_id "bank_branch_id",
bb.BANK_ACCOUNT_ID
into p_customer_bank_branch_id,
p_customer_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 a.currency_code = p_currency_code
and bb.ext_bank_account_id = a.instrument_id
and bb.bank_account_number = p_customer_bank_account_num;
select distinct bb.branch_party_id "bank_branch_id",
bb.BANK_ACCOUNT_ID
into p_customer_bank_branch_id
,p_customer_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 a.currency_code = p_currency_code
and bb.ext_bank_account_id = a.instrument_id
and bb.bank_account_name = p_customer_bank_account_name
and bb.bank_account_number = p_customer_bank_account_num;
Select trunc(sysdate)
into p_receipt_date
from dual;
SELECT NVL(NVL(site.discount_grace_days, cust.discount_grace_days),0)
INTO l_grace_days
FROM
hz_customer_profiles cust,
hz_customer_profiles site,
hz_cust_accounts c
WHERE
c.cust_account_id = p_customer_id
AND cust.cust_account_id = c.cust_account_id
AND cust.site_use_id IS NULL
AND site.cust_account_id (+) = c.cust_account_id
AND site.site_use_id (+) = l_bill_to_site_use_id;
SELECT amount_due_remaining,
amount_due_original
INTO l_amt_due_remaining,
l_amt_due_original
FROM ar_payment_schedules_all
WHERE cash_receipt_id = p_cash_receipt_id;
SELECT SUM(ra.amount_applied),
SUM(ra.amount_applied_from)
INTO l_amt_app_prev,
l_amt_app_from_prev
FROM ar_receivable_applications_all ra,
ar_payment_schedules_all ps
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND ra.status = 'APP'
AND ra.display = 'Y'
AND ra.trans_to_receipt_rate = p_trans_to_receipt_rate
AND ps.invoice_currency_code = p_trx_currency_code
AND ra.applied_payment_schedule_id = ps.payment_schedule_id;
select * from ar_llca_trx_lines_gt
where customer_trx_id = p_cust_trx_id;
select count(*) into l_gt_count
from ar_llca_trx_lines_gt
where customer_trx_id = p_customer_trx_id
and rownum = 1;
select to_char(line.line_number) apply_to,
nvl(line.amount_due_remaining,0),
nvl(tax.amount_due_remaining,0)
into
l_line_number,
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 (+);
Select decode ( ( Nvl(l_line_amount_remaining,0)
/ ( Nvl(l_line_amount_remaining,0)
+ Nvl(l_line_tax_remaining,0)
)
),0,1,
( Nvl(l_line_amount_remaining,0)
/ ( Nvl(l_line_amount_remaining,0)
+ Nvl(l_line_tax_remaining,0)
)
)
)
into l_calc_line_per
from dual;
-- Update the GT table with calculated values
Update ar_llca_trx_lines_gt
set amount_applied = Nvl(l_calc_tot_amount_app,0),
line_amount = Nvl(l_calc_line_amount,0),
tax_amount = Nvl(l_calc_tax_amount,0)
where customer_trx_id = p_customer_trx_id
and customer_trx_line_id = sp_lines_row.customer_trx_line_id;
select count(1) into l_count_err_gt from ar_llca_trx_errors_gt
where customer_trx_id = p_customer_trx_id;
SELECT NVL(NVL(site.discount_terms, cust.discount_terms),'Y')
INTO l_allow_discount
FROM
hz_customer_profiles cust
, hz_customer_profiles site
WHERE
cust.cust_account_id = p_customer_id
AND cust.site_use_id IS NULL
AND site.cust_account_id (+) = cust.cust_account_id
AND site.site_use_id (+) = p_bill_to_site_use_id;
SELECT ctl.extended_amount *
nvl(tl.relative_amount,1)/ nvl(t.base_amount,1)
INTO l_trx_line_amount
FROM ra_customer_trx_lines ctl ,
ra_terms t ,
ra_terms_lines tl ,
ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_payment_schedule_id and
ctl.customer_trx_id = p_customer_trx_id and
ctl.line_type = 'LINE' and
tl.term_id(+) = ps.term_id and
tl.sequence_num(+) = ps.terms_sequence_number and
t.term_id(+) = tl.term_id and
ctl.customer_trx_line_id = p_customer_trx_line_id;
SELECT site_use_id
FROM hz_cust_site_uses site_use,
hz_cust_acct_sites acct_site
WHERE acct_site.cust_acct_site_id = site_use.cust_acct_site_id
AND acct_site.cust_account_id = p_customer_id
AND site_use.location = l_location
AND site_use.site_use_code IN ('BILL_TO','DRAWEE');
SELECT
ps.customer_id
, ps.cust_trx_type_id
, decode(ps.customer_id ,-1,NULL ,ps.due_date)
, ps.invoice_currency_code
, ps.exchange_rate
, ct.trx_date
, ps.gl_date
, t.calc_discount_on_lines_flag
, t.partial_discount_flag
, ctt.allow_overapplication_flag
, ctt.natural_application_only_flag
, ctt.creation_sign
, ps.payment_schedule_id
, greatest(p_cr_gl_date,ps.gl_date,
decode(pg_profile_appln_gl_date_def,
'INV_REC_SYS_DT', sysdate, 'INV_REC_DT', ps.gl_date,
ps.gl_date)) gl_date
, ps.term_id
, ps.amount_due_original
, ps.amount_line_items_original
, arp_util.CurrRound(ps.amount_due_remaining,
ps.invoice_currency_code)
, ps.discount_taken_earned
, ps.discount_taken_unearned
, ps.amount_line_items_original
, ps.amount_line_items_remaining
, ps.tax_original
, ps.tax_remaining
, ps.freight_original
, ps.freight_remaining
, ps.receivables_charges_charged
, ps.receivables_charges_remaining
, su.location
INTO
p_customer_id ,
p_cust_trx_type_id ,
p_trx_due_date ,
p_trx_currency_code,
p_trx_exchange_rate,
p_trx_date,
p_trx_gl_date ,
p_calc_discount_on_lines_flag ,
p_partial_discount_flag ,
p_allow_overappln_flag ,
p_natural_appln_only_flag ,
p_creation_sign ,
l_applied_payment_schedule_id ,
p_gl_date, --this is the application gl_date
p_term_id ,
p_amount_due_original,
p_amount_line_items_original ,
p_amount_due_remaining ,
p_discount_taken_earned,
p_discount_taken_unearned,
p_line_items_original,
p_line_items_remaining,
p_tax_original,
p_tax_remaining,
p_freight_original,
p_freight_remaining,
p_rec_charges_charged,
p_rec_charges_remaining,
l_location
FROM
ra_customer_trx ct
, ra_cust_trx_types ctt
, hz_cust_site_uses su
, ra_batch_sources bs
, ar_lookups lu
, hz_cust_accounts cust
, ra_terms t
, ar_payment_schedules ps
, ar_cons_inv ci
WHERE
ps.class in ('CB','CM','DEP','DM','INV','BR')
AND ps.selected_for_receipt_batch_id is null
AND t.term_id(+) = ps.term_id
AND ct.customer_trx_id(+) = ps.customer_trx_id
AND bs.batch_source_id (+) = ct.batch_source_id
AND ctt.cust_trx_type_id(+) = ps.cust_trx_type_id
AND cust.cust_account_id(+) = ps.customer_id
AND su.site_use_id(+) = ps.customer_site_use_id
AND ps.class = lu.lookup_code
AND ct.previous_customer_trx_id is null
AND lu.lookup_type = 'INV/CM'
AND ci.cons_inv_id(+) = ps.cons_inv_id
AND ct.customer_trx_id = p_customer_trx_id
AND ps.invoice_currency_code =
decode(nvl(pg_profile_enable_cc,'N'),
'Y',ps.invoice_currency_code,p_cr_currency_code)
AND ps.status=decode(p_show_closed_invoices,'Y',ps.status,'OP')
AND ps.terms_sequence_number = p_installment
;
SELECT
ps.customer_id
, ps.cust_trx_type_id
, decode(ps.customer_id ,-1,NULL,ps.due_date)
, ps.invoice_currency_code
, ps.exchange_rate
, ct.trx_date
, ps.gl_date
, t.calc_discount_on_lines_flag
, t.partial_discount_flag
, ctt.allow_overapplication_flag
, ctt.natural_application_only_flag
, ctt.creation_sign
, ps.payment_schedule_id
, greatest(p_cr_gl_date,ps.gl_date,
decode(pg_profile_appln_gl_date_def,
'INV_REC_SYS_DT', sysdate, 'INV_REC_DT', ps.gl_date,
ps.gl_date)) gl_date
, ps.term_id
, ps.amount_due_original
, ps.amount_line_items_original
, arp_util.CurrRound(ps.amount_due_remaining,
ps.invoice_currency_code)
, ps.discount_taken_earned
, ps.discount_taken_unearned
, ps.amount_line_items_original
, ps.amount_line_items_remaining
, ps.tax_original
, ps.tax_remaining
, ps.freight_original
, ps.freight_remaining
, ps.receivables_charges_charged
, ps.receivables_charges_remaining
, su.location
INTO
p_customer_id ,
p_cust_trx_type_id ,
p_trx_due_date ,
p_trx_currency_code,
p_trx_exchange_rate,
p_trx_date,
p_trx_gl_date ,
p_calc_discount_on_lines_flag ,
p_partial_discount_flag ,
p_allow_overappln_flag ,
p_natural_appln_only_flag ,
p_creation_sign ,
l_applied_payment_schedule_id ,
p_gl_date, --this is the application gl_date
p_term_id ,
p_amount_due_original,
p_amount_line_items_original ,
p_amount_due_remaining ,
p_discount_taken_earned,
p_discount_taken_unearned,
p_line_items_original,
p_line_items_remaining,
p_tax_original,
p_tax_remaining,
p_freight_original,
p_freight_remaining,
p_rec_charges_charged,
p_rec_charges_remaining,
l_location
FROM
ra_customer_trx ct
, ra_cust_trx_types ctt
, hz_cust_site_uses su
, ra_batch_sources bs
, ar_lookups lu
, hz_cust_accounts cst
, ra_terms t
, ar_payment_schedules ps
, ar_cons_inv ci
WHERE
ps.class in ('CB','CM','DEP','DM','INV','BR')
AND ps.selected_for_receipt_batch_id is null
AND t.term_id(+) = ps.term_id
AND ct.customer_trx_id(+) = ps.customer_trx_id
AND bs.batch_source_id (+) = ct.batch_source_id
AND ctt.cust_trx_type_id(+) = ps.cust_trx_type_id
AND cst.cust_account_id(+) = ps.customer_id
AND su.site_use_id(+) = ps.customer_site_use_id
AND ps.class = lu.lookup_code
AND ct.previous_customer_trx_id is null
AND lu.lookup_type = 'INV/CM'
AND ci.cons_inv_id(+) = ps.cons_inv_id
AND ct.customer_trx_id = p_customer_trx_id
AND ps.invoice_currency_code =
decode(nvl(pg_profile_enable_cc,'N'),
'Y',ps.invoice_currency_code,p_cr_currency_code)
AND ps.status=decode(p_show_closed_invoices,'Y',ps.status,'OP')
AND ps.terms_sequence_number = p_installment
AND ps.customer_id IN (
SELECT rcr.related_cust_account_id
FROM hz_cust_acct_relate rcr
WHERE rcr.status='A' and
rcr.cust_account_id= p_cr_customer_id
and rcr.bill_to_flag = 'Y'
UNION
SELECT p_cr_customer_id
FROM dual
UNION
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE rel.party_id = acc.party_id
AND acc.cust_account_id = p_cr_customer_id
AND p_cr_date BETWEEN effective_start_date
AND effective_end_date
);
SELECT
ot.customer_id ,
ot.cust_trx_type_id ,
ot.trx_due_date ,
ot.invoice_currency_code,
ot.trx_exchange_rate,
ot.trx_date,
ot.trx_gl_date ,
ot.calc_discount_on_lines_flag ,
ot.partial_discount_flag ,
ot.allow_overapplication_flag ,
ot.natural_application_only_flag ,
ot.creation_sign ,
ot.payment_schedule_id ,
greatest(p_cr_gl_date,ot.trx_gl_date,
decode(pg_profile_appln_gl_date_def,
'INV_REC_SYS_DT', sysdate, 'INV_REC_DT', ot.trx_gl_date,
ot.trx_gl_date)) gl_date,
ot.term_id ,
ot.amount_due_original,
ot.amount_line_items_original ,
arp_util.CurrRound(ot.balance_due_curr_unformatted,
ot.invoice_currency_code) ,
ot.discount_taken_earned,
ot.discount_taken_unearned,
ot.amount_line_items_original,
ot.amount_line_items_remaining,
ot.tax_original,
ot.tax_remaining,
ot.freight_original,
ot.freight_remaining,
Null receivables_charges_charged,
ot.receivables_charges_remaining,
ot.location
INTO
p_customer_id ,
p_cust_trx_type_id ,
p_trx_due_date ,
p_trx_currency_code,
p_trx_exchange_rate,
p_trx_date,
p_trx_gl_date ,
p_calc_discount_on_lines_flag ,
p_partial_discount_flag ,
p_allow_overappln_flag ,
p_natural_appln_only_flag ,
p_creation_sign ,
l_applied_payment_schedule_id ,
p_gl_date, --this is the application gl_date
p_term_id ,
p_amount_due_original,
p_amount_line_items_original ,
p_amount_due_remaining ,
p_discount_taken_earned,
p_discount_taken_unearned,
p_line_items_original,
p_line_items_remaining,
p_tax_original,
p_tax_remaining,
p_freight_original,
p_freight_remaining,
p_rec_charges_charged,
p_rec_charges_remaining,
l_location
FROM
ar_open_trx_v ot
WHERE
ot.customer_trx_id = p_customer_trx_id and
ot.invoice_currency_code =
DECODE(NVL(pg_profile_enable_cc,'N'),
'Y',ot.invoice_currency_code,p_cr_currency_code) and
ot.status=decode(p_show_closed_invoices,'Y',ot.status,'OP') and
ot.terms_sequence_number = p_installment;
SELECT
ot.customer_id ,
ot.cust_trx_type_id ,
ot.trx_due_date ,
ot.invoice_currency_code,
ot.trx_exchange_rate,
ot.trx_date,
ot.trx_gl_date ,
ot.calc_discount_on_lines_flag ,
ot.partial_discount_flag ,
ot.allow_overapplication_flag ,
ot.natural_application_only_flag ,
ot.creation_sign ,
ot.payment_schedule_id ,
greatest(p_cr_gl_date,ot.trx_gl_date,
decode(pg_profile_appln_gl_date_def,
'INV_REC_SYS_DT', sysdate, 'INV_REC_DT', ot.trx_gl_date,
ot.trx_gl_date)) gl_date,
ot.term_id ,
ot.amount_due_original,
ot.amount_line_items_original ,
arp_util.CurrRound(ot.balance_due_curr_unformatted,
ot.invoice_currency_code) ,
ot.discount_taken_earned,
ot.discount_taken_unearned,
ot.amount_line_items_original,
ot.amount_line_items_remaining,
ot.tax_original,
ot.tax_remaining,
ot.freight_original,
ot.freight_remaining,
Null receivables_charges_charged,
ot.receivables_charges_remaining,
ot.location
INTO
p_customer_id ,
p_cust_trx_type_id ,
p_trx_due_date ,
p_trx_currency_code,
p_trx_exchange_rate,
p_trx_date,
p_trx_gl_date ,
p_calc_discount_on_lines_flag ,
p_partial_discount_flag,
p_allow_overappln_flag,
p_natural_appln_only_flag,
p_creation_sign,
l_applied_payment_schedule_id,
p_gl_date, --this is the defaulted application gl_date
p_term_id,
p_amount_due_original,
p_amount_line_items_original,
p_amount_due_remaining,
p_discount_taken_earned,
p_discount_taken_unearned,
p_line_items_original,
p_line_items_remaining,
p_tax_original,
p_tax_remaining,
p_freight_original,
p_freight_remaining,
p_rec_charges_charged,
p_rec_charges_remaining,
l_location
FROM
ar_open_trx_v ot
WHERE
ot.customer_trx_id = p_customer_trx_id and
ot.invoice_currency_code =
DECODE(NVL(pg_profile_enable_cc,'N'),
'Y',ot.invoice_currency_code,p_cr_currency_code) and
ot.status=decode(p_show_closed_invoices,'Y',ot.status,'OP') and
ot.terms_sequence_number = p_installment and
--ot.customer_id IN (
exists
-- Fix for bug 12635079
--SELECT rcr.related_cust_account_id
(SELECT /*+ no_unnest */ 'x'
FROM hz_cust_acct_relate rcr
WHERE rcr.status='A' and
rcr.cust_account_id= p_cr_customer_id
and rcr.bill_to_flag = 'Y'
and ot.customer_id = rcr.related_cust_account_id
UNION ALL
SELECT /*+ no_unnest */ 'x'
FROM dual
WHERE ot.customer_id = p_cr_customer_id
UNION ALL
SELECT /*+ no_unnest */ 'x'
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE rel.party_id = acc.party_id
AND acc.cust_account_id = p_cr_customer_id
AND p_cr_date BETWEEN effective_start_date
AND effective_end_date
AND OT.CUSTOMER_ID = REL.RELATED_CUST_ACCOUNT_ID
);
select 'Y'
into l_found
from ar_receivable_applications rap
where rap.payment_schedule_id = p_cr_payment_schedule_id
and rap.applied_payment_schedule_id = p_applied_payment_schedule_id
and applied_customer_trx_line_id is NULL
and rap.display = 'Y'
and rap.status = 'APP';
select 'Y'
into l_found
from ar_receivable_applications rap
where rap.payment_schedule_id = p_cr_payment_schedule_id
and rap.applied_payment_schedule_id = p_applied_payment_schedule_id
and rap.applied_customer_trx_line_id = p_customer_trx_line_id
and rap.display = 'Y'
and rap.status = 'APP';
SELECT cr.pay_from_customer,
crh.gl_date,
cr.amount,
cr.customer_site_use_id,
cr.receipt_date,
cr.currency_code,
cr.exchange_rate,
ps.payment_schedule_id,
ba.bank_acct_use_id,
cr.receipt_method_id
INTO p_cr_customer_id,
p_cr_gl_date,
p_cr_amount,
p_cr_cust_site_use_id,
p_cr_date,
p_cr_currency_code,
p_cr_exchange_rate,
p_cr_payment_schedule_id,
p_remittance_bank_account_id,
p_receipt_method_id
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_payment_schedules ps,
ce_bank_acct_uses ba
WHERE cr.remit_bank_acct_use_id = ba.bank_acct_use_id and
cr.cash_receipt_id = crh.cash_receipt_id and
crh.first_posted_record_flag = 'Y' and /* bug 3333680 */
cr.cash_receipt_id = p_cash_receipt_id and
cr.cash_receipt_id = ps.cash_receipt_id and
crh.status IN ('CONFIRMED','CLEARED', 'REMITTED','APPROVED',
decode(crh.factor_flag,'Y','RISK_ELIMINATED')); /* Risk Eliminated condition added
SELECT cr.pay_from_customer,
crh.gl_date,
cr.amount,
cr.customer_site_use_id,
cr.receipt_date,
cr.currency_code,
cr.exchange_rate,
ps.payment_schedule_id,
ba.bank_acct_use_id,
cr.receipt_method_id
INTO p_cr_customer_id,
p_cr_gl_date,
p_cr_amount,
p_cr_cust_site_use_id,
p_cr_date,
p_cr_currency_code,
p_cr_exchange_rate,
p_cr_payment_schedule_id,
p_remittance_bank_account_id,
p_receipt_method_id
FROM ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_payment_schedules ps,
ce_bank_acct_uses ba
WHERE cr.remit_bank_acct_use_id = ba.bank_acct_use_id and
cr.cash_receipt_id = crh.cash_receipt_id and
crh.first_posted_record_flag = 'N' and
cr.cash_receipt_id = p_cash_receipt_id and
cr.cash_receipt_id = ps.cash_receipt_id and
crh.status = 'APPROVED';
SELECT SUM(NVL(ra.amount_applied,0))
INTO p_cr_unapp_amount
FROM ar_receivable_applications ra
WHERE ra.status = 'UNAPP'
AND ra.cash_receipt_id = p_cash_receipt_id;
SELECT customer_trx_id
INTO p_customer_trx_id
FROM ra_customer_trx
WHERE trx_number = p_trx_number;
select count(1)
into l_count
from ra_customer_trx_lines line
where line.customer_Trx_id = p_customer_trx_id
and line.line_type = 'LINE'
and line.source_data_key4 = p_group_id
and rownum = 1;
select * from ar_llca_trx_lines_gt
where customer_trx_id = p_cust_trx_id;
SELECT customer_trx_line_id
INTO p_customer_trx_line_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND line_number = p_line_number
AND line_type = 'LINE';
SELECT customer_trx_id
INTO p_customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_customer_trx_line_id
AND line_type = 'LINE';
SELECT customer_trx_line_id
INTO l_cust_trx_line_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND line_number = i.line_number
AND line_type = 'LINE';
Update ar_llca_trx_lines_gt
set customer_trx_line_id = l_cust_trx_line_id
where customer_trx_id = p_customer_trx_id
and line_number = i.line_number;
SELECT customer_trx_line_id
INTO l_cust_trx_line_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND customer_trx_line_id = i.customer_trx_line_id
AND line_type = 'LINE';
SELECT cash_receipt_id
INTO p_cash_receipt_id
FROM ar_cash_receipts
WHERE receipt_number = p_receipt_number;
SELECT customer_trx_id,terms_sequence_number
FROM ar_payment_schedules
WHERE payment_schedule_id = p_applied_payment_schedule_id and
payment_schedule_id >0 and
class in ('INV','DM','DEP','CB','CM','BR');
SELECT terms_sequence_number
FROM ar_payment_schedules
WHERE customer_trx_id = p_customer_trx_id;
SELECT terms_sequence_number
INTO p_installment
FROM ar_payment_schedules
WHERE customer_trx_id = p_customer_trx_id;
select terms_sequence_number into l_installment
from ar_payment_schedules
where customer_trx_id = p_customer_trx_id;
SELECT ps.payment_schedule_id
INTO l_trx_ps_id
FROM ra_customer_trx ct,
ar_payment_schedules ps
WHERE ct.customer_trx_id = p_customer_trx_id
AND ct.customer_trx_id = ps.customer_trx_id
AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
AND ps.terms_sequence_number = p_installment
;
SELECT ps.payment_schedule_id
INTO l_trx_ps_id
FROM ra_customer_trx ct,
ar_payment_schedules ps
WHERE ct.customer_trx_id = p_customer_trx_id
AND ct.customer_trx_id = ps.customer_trx_id
AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
;
SELECT ps.payment_schedule_id
INTO l_trx_ps_id
FROM ra_customer_trx ct,
ar_payment_schedules ps
WHERE ct.customer_trx_id = p_customer_trx_id
AND ct.customer_trx_id = ps.customer_trx_id
AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
AND ps.terms_sequence_number = p_installment
--these two conditions are to ensure that the trx(bill) is not in remit process
AND ps.reserved_type IS NULL
AND ps.reserved_value IS NULL;
SELECT ps.payment_schedule_id
INTO l_trx_ps_id
FROM ra_customer_trx ct,
ar_payment_schedules ps
WHERE ct.customer_trx_id = p_customer_trx_id
AND ct.customer_trx_id = ps.customer_trx_id
AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
--these two conditions are to ensure that the trx(bill) is not in remit process
AND ps.reserved_type IS NULL
AND ps.reserved_value IS NULL
;
SELECT receivable_application_id, gl_date
,applied_customer_trx_id
INTO p_receivable_application_id, p_apply_gl_date
,p_customer_trx_id
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND ra.applied_payment_schedule_id = p_applied_payment_schedule_id
AND ra.display = 'Y'
AND ra.status = 'APP'
AND ra.application_type = 'CASH';
SELECT ps.customer_trx_id, ps.terms_sequence_number
FROM ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_ps_id
AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
--these two conditions are to ensure that the trx(bill) is not in remit process
AND ps.reserved_type is null
AND ps.reserved_value is null;
SELECT ps.customer_trx_id, ps.terms_sequence_number
FROM ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_ps_id
AND ps.class IN ('CB','CM','DEP','DM','INV','BR');
SELECT ra.cash_receipt_id, ra.applied_payment_schedule_id, ra.gl_date /* Bug fix 3451241 */
, ra.applied_customer_trx_id
FROM ar_receivable_applications ra,
ar_payment_schedules ps
WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
AND ra.receivable_application_id = p_ra_id
AND ra.display = 'Y'
AND ra.status = 'APP'
AND ps.reserved_value IS NULL
AND ps.reserved_type IS NULL;
SELECT ra.cash_receipt_id, ra.applied_payment_schedule_id, ra.gl_date /* Bug fix 3451241 */
, ra.applied_customer_trx_id
FROM ar_receivable_applications ra
WHERE ra.receivable_application_id = p_ra_id
AND ra.display = 'Y'
AND ra.status = 'APP';
SELECT gl_date, cash_receipt_id
INTO l_apply_gl_date, p_cash_receipt_id
FROM ar_receivable_applications
WHERE receivable_application_id =
p_receivable_application_id;
SELECT SUM(NVL(ra.amount_applied,0))
INTO p_cr_unapp_amount
FROM ar_receivable_applications ra
WHERE ra.status = 'UNAPP'
AND ra.cash_receipt_id = l_cash_receipt_id;
SELECT gl_date
INTO p_receipt_gl_date
FROM ar_cash_receipt_history crh
WHERE crh.cash_receipt_id = l_cash_receipt_id
and crh.FIRST_POSTED_RECORD_FLAG = 'Y';
SELECT lookup_code
INTO p_reversal_category_code
FROM ar_lookups
WHERE lookup_type = 'REVERSAL_CATEGORY_TYPE'
AND enabled_flag = 'Y'
AND meaning = p_reversal_category_name;
SELECT lookup_code
INTO p_reversal_reason_code
FROM ar_lookups
WHERE lookup_type = 'CKAJST_REASON'
AND enabled_flag = 'Y'
AND meaning = p_reversal_reason_name;
SELECT cr.receipt_date, crh.status, cr.type
INTO l_receipt_date, p_receipt_state, p_type
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 cr.cash_receipt_id = p_cash_receipt_id;
SELECT max(crh.gl_date)
INTO p_receipt_gl_date
FROM ar_cash_receipt_history crh
WHERE crh.cash_receipt_id = p_cash_receipt_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 receivable_application_id, gl_date
INTO l_rec_appln_id , p_apply_gl_date
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cash_receipt_id
AND display = 'Y'
AND status = 'ACC';
SELECT cash_receipt_id, gl_date
INTO l_cash_receipt_id , p_apply_gl_date
FROM ar_receivable_applications
WHERE receivable_application_id = p_receivable_application_id
and display = 'Y'
and status = 'ACC';
SELECT receivable_application_id, gl_date
INTO l_rec_appln_id , p_apply_gl_date
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 cash_receipt_id, gl_date
INTO l_cash_receipt_id , p_apply_gl_date
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 SUM(NVL(ra.amount_applied,0))
INTO p_cr_unapp_amt
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND ra.status = 'UNAPP'
AND nvl(ra.confirmed_flag,'Y') = 'Y';
SELECT gl_date
INTO p_receipt_gl_date
FROM ar_cash_receipt_history crh
WHERE crh.cash_receipt_id = l_cash_receipt_id
and crh.current_record_flag = 'Y';
do a generic select. Also receipt write-off unapplication requires
that applied_payment_schedule_id should be compared with -3 */
/* Bug 3840287 - credit card refund included */
IF p_cash_receipt_id IS NOT NULL
AND p_receivable_application_id IS NULL THEN
BEGIN
SELECT receivable_application_id, gl_date
INTO l_rec_appln_id , p_apply_gl_date
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cash_receipt_id
and ((NVL(p_called_from,'RAPI') = 'BR_FACTORED_WITH_RECOURSE' AND
applied_payment_schedule_id = -2)
or applied_payment_schedule_id IN (-3,-6,-8)
or receivables_trx_id = -16)
and display = 'Y' and
status = 'ACTIVITY';
SELECT cash_receipt_id, gl_date
INTO l_cash_receipt_id , p_apply_gl_date
FROM ar_receivable_applications
WHERE receivable_application_id = p_receivable_application_id
and ((NVL(p_called_from,'RAPI') = 'BR_FACTORED_WITH_RECOURSE' AND
applied_payment_schedule_id = -2)
or applied_payment_schedule_id IN (-3,-6,-8)
or receivables_trx_id = -16)
and display = 'Y'
and status = 'ACTIVITY';
SELECT SUM(NVL(ra.amount_applied,0))
INTO p_cr_unapp_amount
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_cash_receipt_id
AND ra.status = 'UNAPP'
AND nvl(ra.confirmed_flag,'Y') = 'Y';
SELECT ps.customer_id,
ps.customer_site_use_id
FROM hz_cust_site_uses su,
hz_cust_accounts cust_acct,
ra_cust_trx_types ctt,
ar_payment_schedules ps
WHERE su.site_use_id = ps.customer_site_use_id
and cust_acct.cust_account_id = ps.customer_id
and ctt.cust_trx_type_id = ps.cust_trx_type_id
and ps.selected_for_receipt_batch_id is null
and ps.class in (''BR'',''CB'',''CM'',''DEP'',''DM'',''INV'')
and ps.invoice_currency_code = decode(nvl(:pg_profile_enable_cc, ''N''), ''Y'',
decode(ps.class, ''CM'', :p_currency_code, ps.invoice_currency_code), :p_currency_code)
and ps.status = ''OP'' ';
SELECT name
INTO p_receipt_method_name
FROM ar_receipt_methods
WHERE receipt_method_id = p_receipt_method_id;
SELECT dist_code_combination_id
FROM ar_distribution_set_lines
WHERE distribution_set_id = p_distribution_set_id;
Select trunc(sysdate)
into p_receipt_date
from dual;
SELECT vat.tax_rate_id
INTO p_vat_tax_id
FROM ar_receivables_trx rt,
ar_rec_trx_le_details details,
zx_sco_rates vat
WHERE rt.receivables_trx_id = p_receivables_trx_id
AND rt.receivables_trx_id = details.receivables_trx_id (+)
AND details.legal_entity_id (+) = l_le_id
AND rt.type in ('MISCCASH', 'BANK_ERROR')
AND nvl(rt.status, 'A') = 'A'
AND vat.tax_rate_code = decode(sign(p_amount),
1, nvl(details.asset_tax_code,
rt.asset_tax_code),
0, nvl(details.asset_tax_code,
rt.asset_tax_code),
-1, nvl(details.liability_tax_code,
rt.liability_tax_code))
AND nvl(vat.tax_class,l_tax_class) = l_tax_class
AND nvl(vat.active_flag, 'Y') = 'Y' /* 4400063 */
AND p_receipt_date between
nvl(vat.effective_from, p_receipt_date)
and nvl(vat.effective_to, p_receipt_date);
SELECT vat.tax_rate_id
INTO p_vat_tax_id
FROM ar_receivables_trx rt,
zx_sco_rates vat
WHERE rt.receivables_trx_id = p_receivables_trx_id
AND rt.type in ('MISCCASH', 'BANK_ERROR')
AND nvl(rt.status, 'A') = 'A'
AND vat.tax_rate_code(+) = decode(sign(p_amount),
1, rt.asset_tax_code,
0, rt.asset_tax_code,
-1, rt.liability_tax_code)
AND nvl(vat.tax_class,l_tax_class) = l_tax_class
AND nvl(vat.active_flag, 'Y') = 'Y'
AND p_receipt_date between
nvl(vat.effective_from, p_receipt_date)
and nvl(vat.effective_to, p_receipt_date);
select gl_account_source,default_acctg_distribution_set into
l_source_code, l_dist_set_id
from ar_receivables_trx
where receivables_trx_id = nvl(p_receivables_trx_id,-99);
select code_combination_id
into l_code_combination_id
from ar_receivables_trx
where receivables_trx_id=p_receivables_trx_id;
SELECT 'Y'
FROM fnd_descriptive_flexs
WHERE application_id = 222
and descriptive_flexfield_name = p_desc_flex_name;
SELECT attribute_category,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15
INTO p_desc_flex_rec.attribute_category,
p_desc_flex_rec.attribute1, p_desc_flex_rec.attribute2,
p_desc_flex_rec.attribute3, p_desc_flex_rec.attribute4,
p_desc_flex_rec.attribute5, p_desc_flex_rec.attribute6,
p_desc_flex_rec.attribute7, p_desc_flex_rec.attribute8,
p_desc_flex_rec.attribute9, p_desc_flex_rec.attribute10,
p_desc_flex_rec.attribute11, p_desc_flex_rec.attribute12,
p_desc_flex_rec.attribute13, p_desc_flex_rec.attribute14,
p_desc_flex_rec.attribute15
FROM ar_cash_receipts
WHERE cash_receipt_id = p_cash_receipt_id;
SELECT rt.receivables_trx_id
INTO p_receivable_trx_id
FROM ar_receivables_trx rt
WHERE nvl(rt.status,'A') = 'A'
AND trunc(sysdate) between nvl(rt.start_date_active,trunc(sysdate))
and nvl(rt.end_date_active,trunc(sysdate))
AND rt.type = p_appl_type
AND ROWNUM = 1;
SELECT cash_receipt_id INTO p_open_cash_receipt_id
FROM ar_payment_schedules
WHERE payment_schedule_id = p_applied_ps_id;
SELECT applied_payment_schedule_id,
amount_applied,
cash_receipt_id,
receivables_trx_id,
secondary_application_ref_id,
application_ref_num,
application_ref_reason,
customer_reference,
customer_reason,
status,
NVL(display,'N')
INTO x_open_applied_ps_id,
x_open_amount_applied,
p_open_cash_receipt_id ,
x_claim_rec_trx_id,
x_secondary_app_ref_id,
x_application_ref_num,
x_application_ref_reason,
x_customer_reference,
x_customer_reason,
l_status,
l_display
FROM ar_receivable_applications
WHERE receivable_application_id = p_open_rec_app_id ;
SELECT SUM(amount_applied)
INTO x_unapplied_cash
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cash_receipt_id
AND status = 'UNAPP';
SELECT SUM(amount_applied)
INTO x_open_amount_applied
FROM ar_receivable_applications
WHERE cash_receipt_id = p_open_cash_receipt_id
AND status = 'UNAPP';
SELECT app.applied_rec_app_id,
applied_app.cash_receipt_id,
app.amount_applied
INTO x_applied_rec_app_id,
x_applied_cash_receipt_id,
x_amount_applied
FROM ar_receivable_applications app,
ar_receivable_applications applied_app
WHERE app.applied_rec_app_id = applied_app.receivable_application_id
AND app.receivable_application_id = p_receivable_application_id;
SELECT legal_entity_id,
exchange_rate,
exchange_rate_type,
exchange_date,
org_id,
pay_from_customer,
customer_site_use_id
INTO x_legal_entity_id,
x_exchange_rate,
x_exchange_rate_type,
x_exchange_date,
l_org_id,
l_cust_acct_id,
l_site_use_id
FROM ar_cash_receipts_all
WHERE cash_receipt_id = p_cash_receipt_id;
SELECT legal_entity_id,
exchange_rate,
exchange_rate_type,
exchange_date,
org_id,
bill_to_customer_id,
bill_to_site_use_id
INTO x_legal_entity_id,
x_exchange_rate,
x_exchange_rate_type,
x_exchange_date,
l_org_id,
l_cust_acct_id,
l_site_use_id
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
SELECT p.party_id
,p.party_name
,p.party_number
INTO p_party_id
,x_party_name
,x_party_number
FROM hz_cust_accounts ca
,hz_parties p
WHERE p.party_id = ca.party_id
AND ca.cust_account_id = l_cust_acct_id;
SELECT cas.party_site_id
/* , arh_addr_pkg.format_address(loc.address_style,loc.address1,
loc.address2, loc.address3,
loc.address4, loc.city,
loc.county, loc.state,
loc.province, loc.postal_code,
null)*/
, loc.address1
INTO p_party_site_id
,x_party_address
FROM hz_cust_acct_sites_all cas,
hz_cust_site_uses_all csu,
hz_party_sites ps,
hz_locations loc
WHERE cas.cust_acct_site_id = csu.cust_acct_site_id
AND csu.site_use_id = l_site_use_id
AND cas.party_site_id = ps.party_site_id
AND ps.location_id = loc.location_id;
delete from ar_llca_trx_lines_gt
where customer_trx_id = p_customer_trx_id;
delete from ar_llca_trx_errors_gt
where customer_trx_id = p_customer_trx_id;
Insert into ar_llca_trx_lines_gt
( customer_trx_id,
customer_trx_line_id,
line_number,
line_amount,
tax_amount,
freight_amount,
charges_amount,
amount_applied,
amount_applied_from,
line_discount,
tax_discount,
freight_discount,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
values
(
p_customer_trx_id,
p_llca_trx_lines_tbl(i).customer_trx_line_id,
p_llca_trx_lines_tbl(i).line_number,
p_llca_trx_lines_tbl(i).line_amount,
p_llca_trx_lines_tbl(i).tax_amount,
Null,
Null,
p_llca_trx_lines_tbl(i).amount_applied,
p_llca_trx_lines_tbl(i).amount_applied_from,
p_llca_trx_lines_tbl(i).line_discount,
p_llca_trx_lines_tbl(i).tax_discount,
Null,
p_llca_trx_lines_tbl(i).attribute_category,
p_llca_trx_lines_tbl(i).attribute1,
p_llca_trx_lines_tbl(i).attribute2,
p_llca_trx_lines_tbl(i).attribute3,
p_llca_trx_lines_tbl(i).attribute4,
p_llca_trx_lines_tbl(i).attribute5,
p_llca_trx_lines_tbl(i).attribute6,
p_llca_trx_lines_tbl(i).attribute7,
p_llca_trx_lines_tbl(i).attribute8,
p_llca_trx_lines_tbl(i).attribute9,
p_llca_trx_lines_tbl(i).attribute10,
p_llca_trx_lines_tbl(i).attribute11,
p_llca_trx_lines_tbl(i).attribute12,
p_llca_trx_lines_tbl(i).attribute13,
p_llca_trx_lines_tbl(i).attribute14,
p_llca_trx_lines_tbl(i).attribute15
);
Insert into ar_llca_trx_errors_gt
( customer_trx_id,
customer_trx_line_id,
error_message,
invalid_value
)
values
( p_customer_trx_id,
p_customer_trx_line_id,
p_error_message,
p_invalid_value
);