[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT term_id
INTO p_term_id
FROM ra_terms a
WHERE a.name = 'IMMEDIATE';
l_location := l_module_name||'.select_ra_terms';
l_location := l_module_name||'.select_ra_terms';
debug(l_module_name,'SELECT gl_period_statuses');
SELECT 1
INTO l_ar_period_count
FROM gl_period_statuses
WHERE closing_status ='O'
AND set_of_books_id = g_set_of_books_id
AND application_id = 222
AND p_gl_date between start_date and end_date;
l_location := l_module_name||'.select_gl_period_statuses1';
l_location := l_module_name||'.select_gl_period_statuses2';
SELECT SUM(amount_due_remaining)
INTO l_amt_due
FROM ar_payment_schedules
WHERE customer_trx_id = l_ct_id
AND class = 'INV';
l_location := l_module_name||'.select_ar_payment_schedules';
FOR error_rec IN (SELECT *
FROM ar_trx_errors_gt) LOOP
IF NOT l_header_printed THEN
log ('', '*********** ERRORS FOR TRX NUMBER '||p_trx_number||' ***************');
SELECT fv_ra_customer_trx_s.nextval
INTO l_dm_trx_number
FROM dual;
l_location := l_module_name||'.select_fv_ra_customer_trx_s';
SELECT SUM(NVL(rctl.amount_due_remaining, rctl.quantity_invoiced*rctl.unit_selling_price))
INTO l_total_line_amount
FROM ra_customer_trx_lines rctl
WHERE rctl.customer_trx_id = p_parent_invoice_id;
l_location := l_module_name||'.select_ra_customer_trx_lines';
FOR inv_dist_rec IN (SELECT *
FROM ra_cust_trx_line_gl_dist rctl
WHERE rctl.customer_trx_id = p_parent_invoice_id
AND rctl.customer_trx_line_id IS NULL) LOOP
l_dist_counter := l_dist_counter + 1;
FOR inv_lines_rec IN (SELECT *
FROM ra_customer_trx_lines rctl
WHERE rctl.customer_trx_id = p_parent_invoice_id) LOOP
IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
debug (l_module_name,'inv_lines_rec.amount_due_remaining='||inv_lines_rec.amount_due_remaining);
FOR inv_dist_rec IN (SELECT *
FROM ra_cust_trx_line_gl_dist rctl
WHERE rctl.customer_trx_id = p_parent_invoice_id
AND rctl.customer_trx_line_id = inv_lines_rec.customer_trx_line_id) LOOP
l_dist_counter := l_dist_counter + 1;
SELECT SUM(amount)
INTO l_total_line_amount
FROM ra_cust_trx_line_gl_dist rctl
WHERE rctl.customer_trx_id = p_parent_invoice_id
AND rctl.customer_trx_line_id IS NOT NULL;
FOR inv_dist_rec IN (SELECT code_combination_id,
sum (amount) amount,
ROUND(sum (amount)/l_total_line_amount, 4) percent
FROM ra_cust_trx_line_gl_dist rctl
WHERE rctl.customer_trx_id = p_parent_invoice_id
AND rctl.customer_trx_line_id IS NOT NULL
GROUP BY code_combination_id
ORDER BY code_combination_id) LOOP
l_dist_counter := l_dist_counter + 1;
UPDATE ra_customer_trx rct
SET rct.related_customer_trx_id = p_root_invoice_id,
rct.interface_header_attribute3 = p_charge_type
WHERE rct.customer_trx_id = p_dm_invoice_id;
l_location := l_module_name||'.update_ra_customer_trx';
debug(l_module_name,'Insert fv_ar_fin_chrg_invoices');
INSERT INTO fv_ar_fin_chrg_invoices
(
org_id,
customer_id,
invoice_id,
invoice_number,
invoice_date,
invoice_amount,
finance_charges,
waive_flag,
enabled_flag,
parent_invoice_id,
root_invoice_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
finance_charge_group_hdr_id,
finance_charge_group_dtl_id,
charge_id
)
VALUES
(
p_org_id,
p_customer_id,
l_dm_invoice_id,
l_dm_trx_number,
l_dm_trx_date,
p_finance_charges,
0,
'N',
'Y',
p_invoice_id,
l_root_invoice_id,
SYSDATE,
g_user_id,
g_user_id,
SYSDATE,
g_login_id,
g_request_id,
p_finance_charge_group_hdr_id,
p_finance_charge_group_dtl_id,
p_charge_id
);
l_location := l_module_name||'.insert_fv_ar_fin_chrg_invoices';
debug(l_module_name,'Insert fv_ar_fin_chrg_inv_lines');
INSERT INTO fv_ar_fin_chrg_inv_lines
(
org_id,
customer_id,
invoice_id,
line_number,
gl_date,
line_amount,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
finance_charge_group_hdr_id,
finance_charge_group_dtl_id,
charge_id
)
SELECT rctl.org_id,
p_customer_id,
rctl.customer_trx_id,
rctl.line_number,
p_gl_date,
rctl.quantity_invoiced*rctl.unit_selling_price,
SYSDATE,
g_user_id,
g_user_id,
SYSDATE,
g_login_id,
g_request_id,
p_finance_charge_group_hdr_id,
p_finance_charge_group_dtl_id,
p_charge_id
FROM ra_customer_trx_lines rctl
WHERE rctl.customer_trx_id = l_dm_invoice_id;
l_location := l_module_name||'.insert_fv_ar_fin_chrg_inv_lines';
UPDATE fv_ar_fin_chrg_invoices
SET request_id=g_request_id,
last_updated_by = g_user_id,
last_update_date = SYSDATE,
last_accrual_date = p_accrue_as_of_date,
finance_charges = NVL(finance_charges, 0) + p_finance_charges,
current_child_invoice_id = l_dm_invoice_id, --can be used in future for adjusting
last_line_number = 1 --can be used in future for adjusting
WHERE invoice_id = p_invoice_id;
UPDATE fv_ar_fin_chrg_invoices
SET last_updated_by = g_user_id,
last_update_date = SYSDATE,
finance_charges = NVL(finance_charges, 0) + p_finance_charges
WHERE invoice_id = l_root_invoice_id;
debug(l_module_name,'Updated '||SQL%ROWCOUNT||' rows.');
l_location := l_module_name||'.update_fv_ar_fin_chrg_invoices (1)';
FOR dm_rec IN (SELECT fcgd.base_charge_id,
fcgd.assessed_charge_id
FROM fv_finance_charge_grp_dtls fcgd,
fv_finance_charge_grp_hdrs fcgh
WHERE fcgh.finance_charge_group_hdr_id = p_finance_charge_group_hdr_id
AND fcgd.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
AND fcgd.base_charge_id = p_charge_id
AND fcgd.start_date <= sysdate
AND decode(fcgd.end_date,null,sysdate,fcgd.end_date) >= sysdate
) LOOP
BEGIN
INSERT INTO fv_invoice_finance_chrgs_all
(
customer_trx_id,
customer_id,
charge_id,
set_of_books_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
waive_flag,
org_id,
finance_charge_group_hdr_id,
finance_charge_group_dtl_id,
enabled_flag,
base_charge_id,
request_id
)
VALUES
(
l_dm_invoice_id,
p_customer_id,
dm_rec.assessed_charge_id,
p_set_of_books_id,
SYSDATE,
g_user_id,
g_user_id,
SYSDATE,
g_login_id,
'N',
p_org_id,
p_finance_charge_group_hdr_id,
p_finance_charge_group_dtl_id,
'Y',
dm_rec.base_charge_id,
g_request_id
);
l_location := l_module_name||'.insert_fv_invoice_finance_chrgs';
SELECT a.curr_value_of_funds_percent
INTO p_cvf_rate
FROM fv_value_of_fund_periods a
WHERE p_accrue_as_of_date BETWEEN a.effective_start_date AND NVL(a.effective_end_date, g_hi_date);
SELECT a.last_accrual_date
INTO p_last_accrual_date
FROM fv_ar_controls a
WHERE a.payment_schedule_id = p_payment_schedule_id
AND a.created_from = p_charge_type;
UPDATE fv_ar_controls a
SET last_accrual_date = p_last_accrual_date
WHERE a.payment_schedule_id = p_payment_schedule_id
AND a.created_from = p_charge_type;
INSERT INTO fv_ar_controls
(
payment_schedule_id,
created_from,
last_accrual_date,
org_id
)
VALUES
(
p_payment_schedule_id,
p_charge_type,
p_last_accrual_date,
p_org_id
);
FOR main_rec IN (SELECT aps.customer_trx_id invoice_id,
aps.amount_due_remaining,
aps.amount_due_original,
aps.payment_schedule_id,
fcc.charge_id,
fcc.charge_type,
fcc.batch_source_id,
aps.trx_number,
aps.due_date,
nvl(fch.amount, nvl(fch.rate,0)/100) rate_amount,
decode(fch.amount, NULL, 'Y', 'N') rate_flag,
fch.rate_base,
fch.rate_type,
nvl(fcc.accrue_at_invoice,'N') accrue_at_invoice,
fcc.trx_type_id,
fcc.first_accrual,
fcc.accrual_interval,
fcc.grace_period,
fcc.receivables_trx_id,
rct.bill_to_customer_id,
rct.bill_to_contact_id,
rct.invoice_currency_code,
rct.exchange_date,
rct.exchange_rate,
rct.exchange_rate_type,
aps.trx_date,
rctt.gl_id_rev,
rctt.gl_id_rec,
rsua.cust_acct_site_id bill_to_address_id,
fai.last_accrual_date,
fcc.base_date_type,
fai.root_invoice_id,
rct.org_id,
rct.term_id,
fifc.customer_id,
fifc.set_of_books_id,
fifc.finance_charge_group_hdr_id,
fifc.finance_charge_group_dtl_id,
NVL(fcc.prorate_charge, 'N') prorate_charge,
fcc.invoice_suffix
FROM ar_payment_schedules aps,
fv_invoice_finance_chrgs fifc,
fv_finance_charge_controls fcc,
fv_finance_charge_history fch,
ra_customer_trx rct,
ra_cust_trx_types rctt,
hz_cust_site_uses_all rsua,
fv_ar_fin_chrg_invoices fai
WHERE (due_date + first_accrual + grace_period) <= l_accrue_as_of_date
AND fifc.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 = g_set_of_books_id
--bug 9716140
AND (rct.related_customer_trx_id IS NULL OR
EXISTS(SELECT 'x'
FROM fv_finance_chrg_cust_classes fccc,
fv_finance_charge_grp_dtls fcgd,
hz_cust_accounts hzca
WHERE fccc.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
AND fccc.enabled_flag = 'Y'
AND fcgd.assessed_charge_id = fcc.charge_id
AND fcgd.base_charge_id <> 0
AND fccc.customer_class = hzca.customer_class_code
AND set_of_books_id = g_set_of_books_id))
-- end of bug 9716140
AND aps.customer_trx_id = fifc.customer_trx_id
AND fifc.charge_id = fcc.charge_id
AND fcc.charge_id = fch.charge_id
AND fcc.enabled_flag = 'Y'
AND aps.due_date
BETWEEN fch.start_date AND
nvl(fch.end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND rctt.cust_trx_type_id = fcc.trx_type_id
AND rsua.site_use_id = rct.bill_to_site_use_id
AND fai.invoice_id = fifc.customer_trx_id
AND EXISTS ( SELECT 'x'
FROM fv_finance_chrg_cust_classes fccc,
fv_finance_charge_grp_dtls fcgd,
hz_cust_accounts hzca
WHERE fccc.customer_class = hzca.customer_class_code
AND fccc.enabled_flag = 'Y'
AND fccc.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
AND fcgd.assessed_charge_id = fcc.charge_id
AND set_of_books_id = g_set_of_books_id)
ORDER BY rct.customer_trx_id,
fcc.charge_id,
aps.payment_schedule_id)
LOOP
IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
debug(l_module_name,'************************************************************');
SELECT SUM(aps.amount_due_original),
SUM(aps.amount_due_remaining)
INTO l_inv_amount_due_original,
l_inv_amount_due_remaining
FROM ar_payment_schedules aps
WHERE aps.customer_trx_id = main_rec.invoice_id;
l_location := l_module_name||'.select_ar_payment_schedules';
SELECT DISTINCT hzca.cust_Account_id customer_id,
hzca.customer_class_code cust_class_code
FROM hz_cust_accounts hzca,
fv_finance_charge_controls fcc
WHERE fcc.enabled_flag = 'Y'
AND fcc.set_of_books_id = c_ledger_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 = c_ledger_id)
AND NOT EXISTS (SELECT 'x'
FROM fv_finance_chrg_cust_classes fccc,
fv_finance_charge_grp_dtls fcgd
WHERE fccc.customer_class = hzca.customer_class_code
AND fccc.enabled_flag = 'Y'
AND fccc.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
AND SYSDATE between NVL(fcgd.start_date, g_lo_date) and NVL(fcgd.end_date, g_hi_date)
AND fcgd.assessed_charge_id = fcc.charge_id
AND set_of_books_id = c_ledger_id);
SELECT currency_code
INTO l_currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = g_set_of_books_id;
l_location := l_module_name||'.select_gl_ledgers_public_v';
/* 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 = g_set_of_books_id);*/
UPDATE fv_cust_finance_chrgs a
SET CUSTOMER_CLASS_CODE = customer.cust_class_code,
finance_charge_group_hdr_id = (SELECT finance_charge_group_hdr_id
FROM fv_finance_chrg_cust_classes b
WHERE customer_class= customer.cust_class_code
AND a.set_of_books_id = b.set_of_books_id
AND rownum =1)
WHERE EXISTS(SELECT 'A'
FROM fv_finance_chrg_cust_classes c
WHERE customer_class= customer.cust_class_code
AND a.set_of_books_id = c.set_of_books_id)
AND customer_id = customer.customer_id;
UPDATE fv_cust_finance_chrgs
SET waive_flag = 'Y'
WHERE customer_id = customer.customer_id
AND charge_id NOT IN (SELECT fcf.charge_id
FROM fv_cust_finance_chrgs fcf,
fv_finance_chrg_cust_classes fcfc,
hz_cust_accounts hzca,
fv_finance_charge_grp_hdrs fcgh,
fv_finance_charge_grp_dtls fcgd
WHERE hzca.cust_account_id = customer.customer_id
AND fcf.customer_id = hzca.cust_account_id
AND fcf.customer_class_code = fcfc.customer_class
AND fcf.customer_class_code = hzca.customer_class_code
AND fcgh.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
AND fcgh.finance_charge_group_hdr_id = fcf.finance_charge_group_hdr_id
AND fcgh.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
AND fcf.charge_id = fcgd.assessed_charge_id
AND fcfc.enabled_flag = 'Y'
AND fcf.set_of_books_id =g_set_of_books_id
AND fcgd.start_date <= sysdate
AND decode(fcgd.end_date,null,sysdate,fcgd.end_date) >= sysdate
AND fcgd.base_charge_id = 0);
UPDATE fv_cust_finance_chrgs
SET waive_flag = 'N'
WHERE customer_id = customer.customer_id
AND charge_id IN (SELECT fcf.charge_id
FROM fv_cust_finance_chrgs fcf,
fv_finance_chrg_cust_classes fcfc,
hz_cust_accounts hzca,
fv_finance_charge_grp_hdrs fcgh,
fv_finance_charge_grp_dtls fcgd
WHERE hzca.cust_account_id = customer.customer_id
AND fcf.customer_id = hzca.cust_account_id
AND fcf.customer_class_code = fcfc.customer_class
AND fcf.customer_class_code = hzca.customer_class_code
AND fcgh.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
AND fcgh.finance_charge_group_hdr_id = fcf.finance_charge_group_hdr_id
AND fcgh.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
AND fcf.charge_id = fcgd.assessed_charge_id
AND fcfc.enabled_flag = 'Y'
AND fcf.set_of_books_id =g_set_of_books_id
AND fcgd.start_date <= sysdate
AND decode(fcgd.end_date,null,sysdate,fcgd.end_date) >= sysdate
AND fcgd.base_charge_id = 0);
l_location := l_module_name||'.update_fv_cust_finance_chrgs';
DELETE FROM fv_cust_finance_chrgs WHERE
customer_id IN
(SELECT hzca.cust_account_id
FROM hz_cust_accounts hzca,
fv_finance_charge_controls fcc,
fv_finance_chrg_cust_classes fccc,
fv_finance_charge_grp_dtls fcgd,
fv_finance_charge_grp_hdrs fcgh
WHERE fcc.enabled_flag = 'Y'
AND fcc.set_of_books_id = g_set_of_books_id
AND hzca.status = 'A'
AND fccc.customer_class = hzca.customer_class_code
AND fccc.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
AND fcgd.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
AND fcgh.enabled_flag = 'Y'
AND fcgd.enabled_flag = 'Y'
AND fcgd.assessed_charge_id = fcc.charge_id
AND fccc.set_of_books_id = g_set_of_books_id
AND fcgh.ledger_id = g_set_of_books_id
AND fccc.enabled_flag = 'Y'
AND fcgd.base_charge_id = 0
AND EXISTS (SELECT 'x'
FROM fv_cust_finance_chrgs_all fcfc
WHERE hzca.cust_account_id = fcfc.customer_id
AND fcc.charge_id = fcfc.charge_id
AND fcgd.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
AND fcfc.customer_class_code <> fccc.customer_class
AND fcfc.set_of_books_id = g_set_of_books_id));
l_location := l_module_name||'.delete_fv_cust_finance_chrgs';
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,
enabled_flag,
customer_class_code,
finance_charge_group_hdr_id,
base_charge_id,
request_id
)
SELECT hzca.cust_account_id,
fcgd.assessed_charge_id,
'N',
g_user_id,
SYSDATE,
g_user_id,
SYSDATE,
g_set_of_books_id,
fcc.org_id,
'Y',
fccc.customer_class,
fcgh.finance_charge_group_hdr_id,
fcgd.base_charge_id,
g_request_id
FROM hz_cust_accounts hzca,
fv_finance_charge_controls fcc,
fv_finance_chrg_cust_classes fccc,
fv_finance_charge_grp_dtls fcgd,
fv_finance_charge_grp_hdrs fcgh
WHERE fcc.enabled_flag = 'Y'
AND fcc.set_of_books_id = g_set_of_books_id
AND hzca.status = 'A'
AND fccc.customer_class = hzca.customer_class_code
AND fccc.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
AND fcgd.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
AND fcgh.enabled_flag = 'Y'
AND fcgd.enabled_flag = 'Y'
AND fcgd.assessed_charge_id = fcc.charge_id
AND fccc.set_of_books_id = g_set_of_books_id
AND fcgh.ledger_id = g_set_of_books_id
AND fccc.enabled_flag = 'Y'
AND fcgd.base_charge_id = 0
AND NOT EXISTS (SELECT 'x'
FROM fv_cust_finance_chrgs fcfc
WHERE hzca.cust_account_id = fcfc.customer_id
AND fcc.charge_id = fcfc.charge_id
AND fcgd.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
and fcfc.customer_class_code = fccc.customer_class
AND fcfc.set_of_books_id = g_set_of_books_id);
l_location := l_module_name||'.insert_fv_cust_finance_chrgs';
(SELECT 'X' FROM ra_customer_trx_lines_all ral
WHERE description = 'Accrue Federal Finance Charges'
AND ral.customer_trx_id = ract.customer_trx_id )))
in both insert statements below. Assign Finance Charges report should display
finance charges assigned to invoices and debit memos not created by the Accrue
Finance charge process(AcFC) only. It should not display the charges assigned
to debit memos created using AcFC process */
INSERT INTO fv_ar_fin_chrg_invoices
(
org_id,
customer_id,
invoice_id,
invoice_number,
invoice_date,
invoice_amount,
finance_charges,
waive_flag,
enabled_flag,
parent_invoice_id,
root_invoice_id,
last_accrual_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id
)
SELECT ract.org_id,
ract.bill_to_customer_id,
ract.customer_trx_id,
ract.trx_number,
ract.trx_date,
0, --invoice amount
0, --finance charges
'N',
'Y',
0,
0,
NULL,
SYSDATE,
g_user_id,
g_user_id,
SYSDATE,
g_login_id,
g_request_id
FROM ra_customer_trx ract,
ra_cust_trx_types rctt
WHERE ract.cust_trx_type_id = rctt.cust_trx_type_id
AND ract.complete_flag = 'Y'
--AND rctt.type IN ('DM','INV')
AND( rctt.type ='INV' OR (rctt.type ='DM' AND NOT EXISTS
( SELECT 'X' FROM ra_customer_trx_lines_all ral
WHERE description = 'Accrue Federal Finance Charges'
AND ral.customer_trx_id = ract.customer_trx_id )))
/* AND NVL(ract.interface_header_attribute3,'XX') NOT IN (SELECT charge_type
FROM fv_finance_charge_controls
WHERE set_of_books_id = g_set_of_books_id)*/
AND EXISTS (SELECT 'x'
FROM fv_cust_finance_chrgs fcfc
WHERE ract.bill_to_customer_id = fcfc.customer_id
AND fcfc.enabled_flag = 'Y'
AND fcfc.waive_flag = 'N')
AND NOT EXISTS (SELECT 'x'
FROM fv_ar_fin_chrg_invoices fai
WHERE ract.customer_trx_id = fai.invoice_id
AND ract.bill_to_customer_id=fai.customer_id);
l_location := l_module_name||'.insert_fv_cust_finance_chrgs';
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,
request_id,
base_charge_id,
finance_charge_group_hdr_id,
finance_charge_group_dtl_id,
enabled_flag
)
SELECT ract.bill_to_customer_id,
ract.customer_trx_id,
fcgd.assessed_charge_id,
fcfc.waive_flag,
g_user_id,
SYSDATE,
g_user_id,
SYSDATE,
g_set_of_books_id,
fcfc.org_id,
g_request_id,
fcgd.base_charge_id,
fcgh.finance_charge_group_hdr_id,
fcgd.finance_charge_group_dtl_id,
'Y'
FROM ra_customer_trx ract,
fv_cust_finance_chrgs fcfc,
fv_finance_charge_controls fcc,
ra_cust_trx_types rctt,
fv_finance_charge_grp_dtls fcgd,
fv_finance_charge_grp_hdrs fcgh
WHERE ract.bill_to_customer_id = fcfc.customer_id
AND fcfc.set_of_books_id = g_set_of_books_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 fcgh.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
AND fcfc.enabled_flag = 'Y'
AND fcfc.waive_flag = 'N'
--AND rctt.type IN ('DM','INV')
AND( rctt.type ='INV' OR (rctt.type ='DM' AND NOT EXISTS
( SELECT 'X' FROM ra_customer_trx_lines_all ral
WHERE description = 'Accrue Federal Finance Charges'
AND ral.customer_trx_id = ract.customer_trx_id )))
AND fcgd.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
AND fcgh.enabled_flag = 'Y'
AND fcgd.enabled_flag = 'Y'
AND fcgd.assessed_charge_id = fcc.charge_id
AND fcgd.base_charge_id = 0
AND fcgh.ledger_id = g_set_of_books_id
/* AND NVL(ract.interface_header_attribute3,'XX') NOT IN (SELECT charge_type
FROM fv_finance_charge_controls
WHERE set_of_books_id = g_set_of_books_id)*/
AND NOT EXISTS (SELECT 'x'
FROM fv_invoice_finance_chrgs fifc
WHERE ract.customer_trx_id = fifc.customer_trx_id
AND ract.bill_to_customer_id=fifc.customer_id
AND fcgd.assessed_charge_id = fifc.charge_id
AND set_of_books_id = g_set_of_books_id);
l_location := l_module_name||'.insert_fv_invoice_finance_chrgs';
l_location := l_module_name||'.insert_fv_invoice_finance_chrgs';