[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_insert_flag varchar2(1):='N';
cursor main_select(cv_sob_id ra_customer_trx.set_of_books_id%TYPE) is
select aps.customer_trx_id,
aps.amount_due_original,
aps.amount_due_remaining,
aps.payment_schedule_id,
aps.customer_site_use_id,
rct.set_of_books_id,
fcc.charge_id,
fcc.charge_type,
fcc.description,
fcc.batch_source_id,
aps.trx_number,
aps.due_date,
nvl(amount, nvl(rate,0)/100),
substr(fch.accrual_type,1,1),
substr(fch.frequency,1,1),
decode(amount, NULL, 'Y', 'N'),
nvl(fcc.accrue_at_invoice,'N'),
fcc.trx_type_id ,
fcc.first_accrual ,
fcc.accrual_interval ,
fcc.grace_period ,
fcc.invoice_suffix,
-- TC Obsoletion
-- fcc.ussgl_transaction_code,
fcc.receivables_trx_id, --Added as part of Enahncement #1957297
rct.sold_to_customer_id,
rct.sold_to_site_use_id,
rct.bill_to_customer_id,
rct.bill_to_site_use_id,
rct.ship_to_customer_id,
rct.ship_to_site_use_id,
rct.remit_to_address_id,
/* ----- changed
rct.bill_to_address_id,
*/
rct.bill_to_site_use_id,
rct.ship_to_address_id,
rct.bill_to_contact_id,
rct.invoice_currency_code,
rct.exchange_date,
rct.exchange_rate,
rct.exchange_rate_type ,
aps.trx_date --Added for Bug 3018578
-- Added extra column to get the Trx_date(invoice_data) March 30, 1999 BG
-- rct.trx_date
-- bug 992498 commented out rct.trx_date
from ar_payment_schedules aps,
fv_invoice_finance_chrgs fifc,
fv_finance_charge_controls fcc,
fv_finance_charge_history fch,
ra_customer_trx rct,
hz_cust_accounts hzca
where trunc(sysdate) > (due_date + first_accrual + grace_period + 1)
and waive_flag = 'N'
and aps.amount_due_remaining > 0
and aps.status <> 'CL'
and aps.customer_trx_id = rct.customer_trx_id
and nvl(rct.interface_header_attribute3,'XX') not in
(select charge_type from fv_finance_charge_controls)
and rct.set_of_books_id = cv_sob_id
and aps.customer_trx_id = fifc.customer_trx_id
and fifc.charge_id = fcc.charge_id
and fcc.charge_id = fch.charge_id
and aps.trx_date between start_date and nvl(end_date,
to_date('31-12-4712','DD-MM-YYYY'))
and fcc.enabled_flag = 'Y'
and hzca.cust_account_id = rct.bill_to_customer_id
and exists (select 'x' from fv_finance_chrg_cust_classes
where customer_class = hzca.customer_class_code
and enabled_flag = 'Y'
and charge_id = fcc.charge_id
and set_of_books_id = cv_sob_id)
order by aps.payment_schedule_id;
select distinct last_accrual_date
from fv_ar_controls
where last_accrual_date = trunc(sysdate);
select fac.last_accrual_date
from ar_payment_schedules aps,
fv_ar_controls fac,
ra_customer_trx rct
where aps.payment_schedule_id = fac.payment_schedule_id
and rct.customer_trx_id = aps.customer_trx_id
and rct.set_of_books_id = cv_sob_id
and fac.last_accrual_date = trunc(sysdate);
select batch_source_id, name
from ra_batch_sources
where name in (select distinct batch_source_name
from ra_interface_lines
where interface_line_context = 'Accrue Finance Charges'
and interface_line_id not in (select interface_line_id
from ra_interface_errors));
select sum(amount_due_remaining)
into amt_due
from ar_payment_schedules
where customer_trx_id = ct_id
and class = 'INV';
select distinct trunc(last_accrual_date)
into accrual_date
from fv_ar_controls
where payment_schedule_id = schedule_id
and created_from = charge_type;
select customer_trx_id
into existing_trx_id
from ra_customer_trx
where related_customer_trx_id = invoice_id
-- and created_from = charge_type; CHANGED
select count(*)
into v_line_count
from ra_interface_lines_all
where interface_line_attribute3 = charge_type
and related_customer_trx_id = invoice_id;
/* This procedure selects data to be used in later SQL statements */
procedure get_global_info is
l_module_name VARCHAR2(200);
select sysdate
into start_time
from dual;
select term_id
into term_id
from ra_terms
where name = 'IMMEDIATE';
select receivables_trx_id
into rec_trx_id
from fv_operating_units
where set_of_books_id = sob_id;
/* Select Revenue and Receivables Code Combination
ID's for use in Debit Memo Creation */
procedure get_additional_invoice_info is
l_module_name VARCHAR2(200) ;
select gl_id_rev, gl_id_rec
into rev_ccid, rec_ccid
from ra_cust_trx_types
where cust_trx_type_id = trx_type_id;
SELECT cust_acct_site_id
INTO v_billto_adr_id
FROM hz_cust_site_uses_all
WHERE site_use_id = p_billto_site_use_id;
INSERT INTO fv_finchrg_debitmemo_temp
(Trx_number,
Charge_type ,
amount,
line_number,
create_adjust_flag,
set_of_books_id,
org_id)
VALUES
(fc_dm_rec.TRX_NUMBER,
fc_dm_rec.INTERFACE_LINE_ATTRIBUTE3,
fc_dm_rec.AMOUNT,
fc_dm_rec.INTERFACE_LINE_ATTRIBUTE2,
create_adjust,
fc_dm_rec.SET_OF_BOOKS_ID,
v_org_id);
INSERT INTO fv_finchrg_debitmemo_temp
(trx_number ,
charge_type ,
amount ,
create_adjust_flag ,
set_of_books_id,
org_id )
VALUES
(substr(trx_number,1,17)||suffix,
charge_type ,
adjust_amount ,
create_adjust ,
set_of_books_id,
v_org_id) ;
select name
into v_batch_source_name
from ra_batch_sources
where batch_source_id = fin_charge_batch_source_id;
select interface_line_attribute1, trx_date
into v_autoinv_seq_nbr, v_split_trx_date
from ra_interface_lines_all
where related_customer_trx_id = invoice_id
and interface_line_attribute3 = charge_type
and interface_line_attribute2 = '1';
select fv_dc_autoinv_header_s.nextval
into v_autoinv_seq_nbr
from dual;
select count(*)
into v_pay_schedule_cnt
from ar_payment_schedules
where customer_trx_id = v_customer_trx_id;
select term_id
into fc_dm_rec.term_id
from ra_customer_trx
where customer_trx_id = v_customer_trx_id;
fc_dm_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
fc_dm_rec.LAST_UPDATE_DATE := trunc(sysdate);
fc_dm_rec.LAST_UPDATE_LOGIN := NULL;
PROCEDURE insert_debit_memo_line
IS
l_module_name VARCHAR2(200) ;
l_module_name := g_module_name || 'insert_debit_memo_line';
INSERT INTO ra_interface_lines_all(
INTERFACE_LINE_ID ,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2 ,
INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4,
HEADER_ATTRIBUTE_CATEGORY,
HEADER_ATTRIBUTE1,
HEADER_ATTRIBUTE3,
BATCH_SOURCE_NAME,
SET_OF_BOOKS_ID,
LINE_TYPE,
DESCRIPTION ,
CURRENCY_CODE,
AMOUNT,
-- CUST_TRX_TYPE_NAME,
CUST_TRX_TYPE_ID,
-- TERM_NAME ,
TERM_ID ,
ORIG_SYSTEM_BATCH_NAME,
ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_CUSTOMER_ID,
ORIG_SYSTEM_BILL_ADDRESS_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID,
-- ORIG_SYSTEM_BILL_CONTACT_REF,
-- ORIG_SYSTEM_BILL_CONTACT_ID,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE,
CUSTOMER_TRX_ID ,
TRX_DATE,
GL_DATE,
DOCUMENT_NUMBER ,
TRX_NUMBER ,
RELATED_CUSTOMER_TRX_ID,
LINE_NUMBER,
QUANTITY,
QUANTITY_ORDERED,
UNIT_SELLING_PRICE ,
UNIT_STANDARD_PRICE ,
CREDIT_METHOD_FOR_ACCT_RULE,
CREDIT_METHOD_FOR_INSTALLMENTS,
REASON_CODE,
REFERENCE_LINE_ID ,
REFERENCE_LINE_CONTEXT ,
REFERENCE_LINE_ATTRIBUTE1,
REFERENCE_LINE_ATTRIBUTE2 ,
COMMENTS,
-- TC Obsoletion
-- USSGL_TRANSACTION_CODE_CONTEXT,
-- USSGL_TRANSACTION_CODE,
UOM_CODE,
UOM_NAME,
CREATED_BY ,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN ,
ORG_ID ,
AMOUNT_INCLUDES_TAX_FLAG)
VALUES(
fc_dm_rec.INTERFACE_LINE_ID,
fc_dm_rec.INTERFACE_LINE_CONTEXT,
fc_dm_rec.INTERFACE_LINE_ATTRIBUTE1,
fc_dm_rec.INTERFACE_LINE_ATTRIBUTE2,
fc_dm_rec.INTERFACE_LINE_ATTRIBUTE3,
fc_dm_rec.INTERFACE_LINE_ATTRIBUTE4,
fc_dm_rec.HEADER_ATTRIBUTE_CATEGORY,
fc_dm_rec.HEADER_ATTRIBUTE1,
fc_dm_rec.HEADER_ATTRIBUTE3,
fc_dm_rec.BATCH_SOURCE_NAME,
fc_dm_rec.SET_OF_BOOKS_ID,
fc_dm_rec.LINE_TYPE,
fc_dm_rec.DESCRIPTION,
fc_dm_rec.CURRENCY_CODE,
fc_dm_rec.AMOUNT,
-- fc_dm_rec.CUST_TRX_TYPE_NAME,
fc_dm_rec.CUST_TRX_TYPE_ID,
-- fc_dm_rec.TERM_NAME,
fc_dm_rec.TERM_ID,
fc_dm_rec.ORIG_SYSTEM_BATCH_NAME,
fc_dm_rec.ORIG_SYSTEM_BILL_CUSTOMER_REF,
fc_dm_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID,
fc_dm_rec.ORIG_SYSTEM_BILL_ADDRESS_REF,
fc_dm_rec.ORIG_SYSTEM_BILL_ADDRESS_ID,
-- fc_dm_rec.ORIG_SYSTEM_BILL_CONTACT_REF,
-- fc_dm_rec.ORIG_SYSTEM_BILL_CONTACT_ID,
fc_dm_rec.CONVERSION_TYPE,
fc_dm_rec.CONVERSION_DATE,
fc_dm_rec.CONVERSION_RATE,
fc_dm_rec.CUSTOMER_TRX_ID,
fc_dm_rec.TRX_DATE,
fc_dm_rec.GL_DATE,
fc_dm_rec.DOCUMENT_NUMBER,
fc_dm_rec.TRX_NUMBER,
fc_dm_rec.RELATED_CUSTOMER_TRX_ID,
fc_dm_rec.LINE_NUMBER,
fc_dm_rec.QUANTITY,
fc_dm_rec.QUANTITY_ORDERED,
fc_dm_rec.UNIT_SELLING_PRICE,
fc_dm_rec.UNIT_STANDARD_PRICE,
fc_dm_rec.CREDIT_METHOD_FOR_ACCT_RULE,
fc_dm_rec.CREDIT_METHOD_FOR_INSTALLMENTS,
fc_dm_rec.REASON_CODE,
fc_dm_rec.REFERENCE_LINE_ID,
fc_dm_rec.REFERENCE_LINE_CONTEXT,
fc_dm_rec.REFERENCE_LINE_ATTRIBUTE1,
fc_dm_rec.REFERENCE_LINE_ATTRIBUTE2,
fc_dm_rec.COMMENTS,
-- TC Obsoletion
-- fc_dm_rec.USSGL_TRANSACTION_CODE_CONTEXT,
-- fc_dm_rec.USSGL_TRANSACTION_CODE,
fc_dm_rec.UOM_CODE,
fc_dm_rec.UOM_NAME,
fc_dm_rec.CREATED_BY,
fc_dm_rec.CREATION_DATE,
fc_dm_rec.LAST_UPDATED_BY,
fc_dm_rec.LAST_UPDATE_DATE,
fc_dm_rec.LAST_UPDATE_LOGIN,
fc_dm_rec.ORG_ID,
fc_dm_rec.AMOUNT_INCLUDES_TAX_FLAG);
END insert_debit_memo_line;
select (decode(v_account_class,'REC',rec_ccid,'REV',rev_ccid))
into
fc_dm_dist_rec.code_combination_id
from dual;
fc_dm_dist_rec.last_updated_by := FND_GLOBAL.USER_ID;
fc_dm_dist_rec.last_update_date := trunc(sysdate);
PROCEDURE update_debit_memo_dist
IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'update_debit_memo_dist';
UPDATE ra_interface_distributions_all rd
set rd.amount = rd.amount + fc_dm_dist_rec.amount,
rd.last_updated_by = fc_dm_dist_rec.last_updated_by,
rd.last_update_date = fc_dm_dist_rec.last_update_date
WHERE
rd.INTERFACE_LINE_CONTEXT = fc_dm_dist_rec.INTERFACE_LINE_CONTEXT
AND rd.INTERFACE_LINE_ATTRIBUTE1 = fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE1
AND rd.INTERFACE_LINE_ATTRIBUTE2 = '1'
AND rd.INTERFACE_LINE_ATTRIBUTE3 = fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE3
AND rd.ACCOUNT_CLASS = fc_dm_dist_rec.ACCOUNT_CLASS
AND rd.org_id = fc_dm_dist_rec.ORG_ID;
END update_debit_memo_dist;
PROCEDURE insert_debit_memo_dist
IS
l_module_name VARCHAR2(200) ;
l_module_name := g_module_name || 'insert_debit_memo_dist';
INSERT into ra_interface_distributions_all(
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4,
ACCOUNT_CLASS,
PERCENT,
AMOUNT,
CODE_COMBINATION_ID,
ORG_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES(
fc_dm_dist_rec.INTERFACE_LINE_CONTEXT,
fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE1,
fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE2,
fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE3,
fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE4,
fc_dm_dist_rec.ACCOUNT_CLASS,
fc_dm_dist_rec.PERCENT,
fc_dm_dist_rec.AMOUNT,
fc_dm_dist_rec.CODE_COMBINATION_ID,
fc_dm_dist_rec.ORG_ID,
fc_dm_dist_rec.CREATED_BY,
fc_dm_dist_rec.CREATION_DATE,
fc_dm_dist_rec.LAST_UPDATED_BY,
fc_dm_dist_rec.LAST_UPDATE_DATE);
END insert_debit_memo_dist;
insert_debit_memo_line;
which we update
*/
update_debit_memo_dist;
insert_debit_memo_dist;
insert_debit_memo_dist;
select distinct last_accrual_date
into date_exists
from fv_ar_controls
where payment_schedule_id = schedule_id
and created_from = charge_type;
insert into fv_ar_controls
(payment_schedule_id, last_accrual_date, created_from, org_id)
values(schedule_id, trunc(sysdate), charge_type, v_org_id);
adjust_amount:=dollars ; --Store the dollars value to insert in temp table
select code_combination_id
into adjust_ccid
from ra_cust_trx_line_gl_dist
where account_class='REC'
and customer_trx_id = existing_trx_id;
select code_combination_id
into adjust_ccid
from ar_receivables_trx
where receivables_trx_id = rec_trx_id
and set_of_books_id = sob_id;
select payment_schedule_id
into existing_pay_schedule_id
from ar_payment_schedules
where customer_trx_id = existing_trx_id
and class = 'DM';
/* Insert into ar_adjustments to capture the accrual of penalty for
the existing debit memo. */
statement_no := 'B';
select ar_adjustments_s.nextval
into v_adjustment_id
from dual;
select concurrent_program_id
into v_conc_program_id
from fnd_concurrent_programs
where concurrent_program_name = 'FVXDCACC'
and application_id = 8901;
select ar_adjustment_number_s.nextval
into v_adjustment_number
from dual;
'Calling INSERT_ADJUST_COVER to adjust'||
' payment_schedule_id'||existing_pay_schedule_id);
ARP_INSERT_ADJ_COVER.INSERT_ADJUST_COVER
( p_form_name => 'FVXDCACC',
p_form_version => 0,
p_acctd_amount => dollars,
p_adjustment_id => v_adjustment_id,
-- p_adjustment_number => ar_adjustment_number_s.nextval,
p_adjustment_number => v_adjustment_number,
p_adjustment_type => 'M',
-- Adjustment type, defaulted to M in the core form
p_amount => dollars,
p_apply_date => trunc(SYSDATE),
p_approved_by => v_user_id,
p_associated_application_id => null,
p_associated_cash_receipt_id => null,
p_attribute1 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute_category => null,
p_automatically_generated =>null,
p_batch_id => null,
p_chargeback_customer_trx_id => null,
p_code_combination_id => adjust_ccid,
p_comments => null, -- Comments
p_created_by => v_user_id,
p_created_from =>'FVXDCACC',
p_creation_date => SYSDATE,
p_customer_trx_id => existing_trx_id,
p_customer_trx_line_id => null,
p_distribution_set_id => null,
p_doc_sequence_id => null,
p_doc_sequence_value => null,
p_freight_adjusted => null,
p_gl_date => trunc(sysdate),
p_gl_posted_date => null,
p_last_updated_by => v_user_id,
p_last_update_date => SYSDATE,
p_last_update_login => v_login_id,
p_line_adjusted => null,
p_org_id => v_org_id,
p_payment_schedule_id => existing_pay_schedule_id,
p_postable => 'Y',
p_posting_control_id => -3, -- This value is been defaulted
-- to -3 in the core form
p_program_application_id => -1,
p_program_id => v_conc_program_id,
p_program_update_date => sysdate,
p_reason_code => null,
p_receivables_charges_adjusted => dollars,
p_receivables_trx_id => rec_trx_id,
p_request_id => v_request_id,
p_set_of_books_id => set_of_books_id,
p_status => 'A',
p_subsequent_trx_id => null,
p_tax_adjusted => null,
p_type => 'CHARGES',
-- p_ussgl_transaction_code => ussgl_transaction_code,
p_ussgl_transaction_code => null,
p_ussgl_transaction_code_conte => null,
p_adjustment_number_o => v_adjustment_number_o,-- Out variable
p_adjustment_id_o => v_adjustment_id_o -- Out variable
);
update fv_ar_controls
set last_accrual_date = trunc(sysdate)
where payment_schedule_id = schedule_id
and created_from = charge_type;
insert into fv_ar_controls(last_accrual_date, payment_schedule_id,
created_from, org_id)
values(trunc(sysdate), schedule_id, charge_type, v_org_id);
select interface_line_attribute4, header_attribute3, interface_line_id
from ra_interface_lines
where interface_line_context = 'Accrue Finance Charges'
and batch_source_name = x_batch_source_name -- added line
and interface_line_id not in (select interface_line_id
from ra_interface_errors);
delete from fv_ar_controls
where payment_schedule_id = error_records_rec.interface_line_attribute4
and created_from = error_records_rec.header_attribute3;
delete from ra_interface_distributions
where interface_line_id = error_records_rec.interface_line_id;
delete from ra_interface_lines
where interface_line_context = 'Accrue Finance Charges'
and batch_source_name = var_batch_source_name -- added line
and interface_line_id not in (select interface_line_id
from ra_interface_errors);
DELETE FROM fv_finchrg_debitmemo_temp -- Purge from temp table 2157100
WHERE set_of_books_id=SOB_ID
AND NVL(org_id,-99)=NVL(v_org_id,-99) ;
SELECT 1 FROM ra_interface_errors_all
WHERE INTERFACE_LINE_ID IN
(SELECT INTERFACE_LINE_ID
FROM ra_interface_lines_all
WHERE INTERFACE_LINE_CONTEXT='Accrue Finance Charges' AND
set_of_books_id=p_sob
AND org_id = p_org);
SELECT 1
FROM gl_period_statuses WHERE closing_status ='O'
AND set_of_books_id = sob_id
AND application_id = 222
AND p_date between start_date and end_date;
DELETE FROM fv_finchrg_debitmemo_temp -- Purge from temp table
WHERE set_of_books_id=SOB_ID
AND NVL(org_id,-99)=NVL(v_org_id,-99) ;
open main_select(sob_id);
fetch main_select into
customer_trx_id,
amount_due_original,
amount_due_remaining,
schedule_id,
customer_site_use_id,
set_of_books_id,
charge_id,
charge_type,
description,
fin_charge_batch_source_id,
trx_number,
due_date,
rate_amount,
accrual_type ,
frequency ,
rate_flag ,
v_invoice_level,
trx_type_id,
first ,
interval ,
grace ,
suffix,
-- TC Obsoletion
-- ussgl_transaction_code,
rec_trx_id, --Added as part of Enhancement #1957297
sold_to_customer_id,
sold_to_site_use_id,
bill_to_customer_id,
bill_to_site_use_id,
ship_to_customer_id,
ship_to_site_use_id,
remit_to_address_id,
bill_to_address_id,
ship_to_address_id,
--Added to fetch Invoice date, March 30, 99 BG
--bug change 992498, commented OUT NOCOPY v_invoice_date
--v_invoice_date;
v_insert_flag:='N';
v_insert_flag:='Y';
v_insert_flag:='N';
exit when main_select%notfound;
IF rate_amount <> 0 AND v_insert_flag='N' AND v_invoice_level='Y' THEN
get_additional_invoice_info;
IF v_insert_flag='N' AND v_invoice_level='Y' THEN
get_additional_invoice_info;
IF v_insert_flag='N' AND v_invoice_level='Y' THEN
-- adjust existing dm
adjust_existing_dm(fixed_amount);
IF v_insert_flag='N' AND v_invoice_level='Y' THEN
adjust_existing_dm(fixed_amount);
select count(*) into l_error_cnt
from fnd_concurrent_requests
where parent_request_id = v_auto_id and status_code = 'E';
if main_select%rowcount = 0 then
null;
close main_select;
if main_select%ISOPEN then
close main_select;
if main_select%ISOPEN then
close main_select;
(select distinct hzca.cust_Account_id customer_id
from hz_cust_accounts hzca, fv_finance_charge_controls fcc
where fcc.enabled_flag = 'Y'
and fcc.set_of_books_id = sob_id
and hzca.status = 'A'
and exists (select 'x' from fv_cust_finance_chrgs
where hzca.cust_account_id = customer_id
and fcc.charge_id = charge_id
and set_of_books_id = sob_id)
and not exists (select 'x' from fv_finance_chrg_cust_classes
where customer_class = hzca.customer_class_code
and enabled_flag = 'Y'
and charge_id = fcc.charge_id
and set_of_books_id = sob_id));
update fv_cust_finance_chrgs
set waive_flag = 'Y'
where customer_id = customer.customer_id
and charge_id not in
(select charge_id from fv_finance_chrg_cust_classes, hz_cust_Accounts hzca
where hzca.cust_Account_id = customer.customer_id
and customer_class = hzca.customer_class_code
and enabled_flag = 'Y'
and set_of_books_id = sob_id);
insert into fv_cust_finance_chrgs (customer_id, charge_id, waive_flag,
created_by, creation_date, last_updated_by, last_update_date,
set_of_books_id,org_id)
select hzca.cust_Account_id, fcc.charge_id, 'N', 0, assign_date, 0, sysdate, sob_id,
fcc.org_id
from hz_cust_accounts hzca, fv_finance_charge_controls fcc
where fcc.enabled_flag = 'Y'
and fcc.set_of_books_id = sob_id
and hzca.status = 'A'
and not exists (select 'x' from fv_cust_finance_chrgs
where hzca.cust_account_id = customer_id
and fcc.charge_id = charge_id
and set_of_books_id = sob_id)
and exists (select 'x' from fv_finance_chrg_cust_classes
where customer_class = hzca.customer_class_code
and enabled_flag = 'Y'
and charge_id = fcc.charge_id
and set_of_books_id = sob_id);
insert into fv_invoice_finance_chrgs
(customer_id, customer_trx_id, charge_id,waive_flag, created_by,
creation_date,last_updated_by, last_update_date, set_of_books_id,org_id)
select ract.bill_to_customer_id, ract.customer_trx_id, fcfc.charge_id,
fcfc.waive_flag, 0 , assign_date, 0 , sysdate, sob_id, v_org_id
from ra_customer_trx ract,
fv_cust_finance_chrgs fcfc,
fv_finance_charge_controls fcc,
ra_cust_trx_types rctt
where ract.bill_to_customer_id = fcfc.customer_id
and fcfc.set_of_books_id = sob_id
and ract.cust_trx_type_id = rctt.cust_trx_type_id
and ract.complete_flag = 'Y'
and fcc.enabled_flag = 'Y'
and fcc.charge_id = fcfc.charge_id
and rctt.type in ('DM','INV')
and nvl(ract.interface_header_attribute3,'XX') not in (select charge_type
from fv_finance_charge_controls
where set_of_books_id = sob_id)
and not exists (select 'x'
from fv_invoice_finance_chrgs
where ract.customer_trx_id = customer_trx_id
and ract.bill_to_customer_id=customer_id
and fcfc.charge_id = charge_id
and set_of_books_id = sob_id);
and ract.trx_date >= (select nvl(last_assignment_date,trunc(sysdate))
from fv_ar_controls)
*/
statement_no := 'Control';
/* select currency_code
into v_currency_code
from gl_sets_of_books
where set_of_books_id = sob_id; */
SELECT currency_code
INTO v_currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = sob_id;